Financial Statement Of An Invoice For iDempiere

/*

-- select * from fnt_get_financial_statement_invoice(1000113);

-- select * from fnt_get_financial_statement_invoice(1017396);

-- select * from fnt_get_financial_statement_invoice(1045352);

-- select * from fnt_get_financial_statement_invoice(1044658);

*/

create or replace function fnt_get_financial_statement_invoice(p_invoice_id numeric(10,0))
returns varchar
as $$
declare
    v_invoice_amt numeric(10,2) = 0.00;
    v_payment_amt numeric(10,2) = 0.00;
  
    v_result numeric(10,2) = 0.00;
    v_pending varchar = 'PENDIENTE';
    v_paid varchar = 'CANCELADO';
    v_financial_statement varchar = v_pending;
  
    v_journal_amt numeric(10,2) = 0.00;
begin
    /************************************************/
  
    v_invoice_amt =
    coalesce((
        select
            case f.c_currency_id
                when 209 then
                    f.grandtotal
                else
                    f.grandtotal * adempiere.fnc_hm_gettasacambio(f.dateacct)
            end
        from c_invoice f
        where f.c_invoice_id = p_invoice_id
    ), 0.00);
  
    /************************************************/
  
    v_payment_amt =
    coalesce((
        with cte_abonos_factura as
        (
            select
                case
                    when p.c_currency_id = 209 then
                        (pa.amount + pa.discountamt + pa.writeoffamt)
                    else
                        (pa.amount * adempiere.fnc_hm_gettasacambio(p.dateacct)) +
                        (pa.discountamt * adempiere.fnc_hm_gettasacambio(p.dateacct)) +
                        (pa.writeoffamt * adempiere.fnc_hm_gettasacambio(p.dateacct))
                end::numeric(20,2) as monto_pago
            from c_paymentallocate pa
                join c_payment p
                on pa.c_payment_id = p.c_payment_id
            where p.docstatus = 'CO'
                and pa.c_invoice_id = p_invoice_id
        )
        select
            sum(af.monto_pago)
        from cte_abonos_factura af
    ), 0.00);
  
    /************************************************/
  
    v_result = v_invoice_amt - v_payment_amt;
  
    /************************************************/
  
    if(v_result > 0.00)then
        if(exists(select 1 from nic_relacion_diario_factura rf
        where rf.c_invoice_id = p_invoice_id))then
            v_journal_amt =
            coalesce((
                select
                    gl.amtacctdr
                from nic_relacion_diario_factura rf
                    join gl_journalline gl
                    on rf.gl_journalline_id = gl.gl_journalline_id
                where rf.c_invoice_id = p_invoice_id
            ), 0.00);
          
            -- raise notice 'invoice amt=%', v_invoice_amt;
            -- raise notice 'payment amt=%', v_payment_amt;
            -- raise notice 'journal amt=%', v_journal_amt;

            v_result = v_invoice_amt - v_payment_amt - v_journal_amt;
      
            if(v_result > 0.00)then
                v_financial_statement = v_pending;
            else
                v_financial_statement = v_paid;
            end if;
        else
            v_financial_statement = v_pending;
        end if;
    else
        v_financial_statement = v_paid;
    end if;
  
    /************************************************/
  
    return v_financial_statement;
end;
$$ language plpgsql;