Descuento en la factura

-- Agregamos el campo monto_descuento a la tabla factura

alter table c_invoiceline add monto_descuento numeric default 0;

-- Creamos otro disparador para actualizar el monto en la factura

CREATE OR REPLACE FUNCTION sp_actualizar_factura()
RETURNS TRIGGER AS $$
  BEGIN
 
      drop table if exists tmp_detalle_descuento;
 
      -- Buscamos la orden de compra desde la recepción de materiales
    create table tmp_detalle_descuento as
    select
        /*f.created,
        uc.name,
        f.updated,
        um.name,*/
        o.documentno num_orden,
        coalesce(ole.c_order_id, ol.c_order_id) c_order_id,
        coalesce(ole.c_orderline_id, ol.c_orderline_id) c_orderline_id,
        f.documentno num_factura,
        f.c_invoice_id,
        df.c_invoiceline_id,
        ol.monto_descuento orden_descuento,
        df.monto_descuento factura_descuento
    from c_invoice f
        join c_invoiceline df
        on f.c_invoice_id = df.c_invoice_id
        left outer join m_inoutline de
        on df.m_inoutline_id = de.m_inoutline_id
        left outer join c_orderline ole
        on de.c_orderline_id = ole.c_orderline_id
        left outer join c_orderline ol
        on df.c_orderline_id = ol.c_orderline_id
        left outer join c_order o
        on coalesce(ole.c_order_id, ol.c_order_id) = o.c_order_id
        join ad_user uc
        on f.createdby = uc.ad_user_id
        join ad_user um
        on f.updatedby = um.ad_user_id
    where f.ad_client_id = 1000000
        and f.docstatus not in ('VO', 'RE')
        and f.c_invoice_id = new.c_invoice_id;
   
    update c_invoiceline
        set monto_descuento =
    (select t.orden_descuento from tmp_detalle_descuento t
    where t.c_invoiceline_id = c_invoiceline.c_invoiceline_id)
    where c_invoiceline_id in
    (select t.c_invoiceline_id from tmp_detalle_descuento t);
     
      -- Aplicamos el monto de descuento para cada subtotal de la orden de compra 
    update c_invoiceline set linenetamt =
    case
        when c_invoiceline.qtyinvoiced > 0 then
            (c_invoiceline.qtyinvoiced * c_invoiceline.priceactual) - monto_descuento
        else
            (c_invoiceline.qtyinvoiced * c_invoiceline.priceactual)
    end
    where c_invoice_id = new.c_invoice_id;
   
    -- Actualizamos el monto total de la orden de compra
    NEW.totallines = (select coalesce(sum(df.linenetamt), 0) from c_invoiceline df where df.c_invoice_id = NEW.c_invoice_id);
    NEW.grandtotal = (select coalesce(sum(df.linenetamt), 0) from c_invoiceline df where df.c_invoice_id = NEW.c_invoice_id);
   
      return new;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualizar_factura
BEFORE UPDATE ON c_invoice
FOR EACH ROW EXECUTE PROCEDURE sp_actualizar_factura();