Generar Cierre Contable en ADempiere

-- Function: adempiere.sp_generar_cierre_contable(numeric, numeric)

-- DROP FUNCTION adempiere.sp_generar_cierre_contable(numeric, numeric);

CREATE OR REPLACE FUNCTION adempiere.sp_generar_cierre_contable(
    id_compania numeric,
    id_periodo numeric)
  RETURNS boolean AS
$BODY$
declare
    num_periodo numeric(10,0);
    periodo varchar(60);
    fecha_inicio date;
    fecha_fin date;

    ingresos_debito numeric(10,2);
    ingresos_credito numeric(10,2);

    egresos_debito numeric(10,2);
    egresos_credito numeric(10,2);

    id_lote numeric(10,0);
    id_nota numeric(10,0);

    secuencia char;

    saldo_total numeric(10,2);
begin
    select
        periodno,
        p.name,
        p.startdate,
        p.enddate
    into num_periodo, periodo, fecha_inicio, fecha_fin
    from c_period p
    where p.ad_client_id = id_compania
    and p.c_period_id = id_periodo;

    drop table if exists tmp_saldos_previos;

    create table tmp_saldos_previos as
    select
        sf.id_cuenta,
        ev.value as cod_cuenta,
        ev.name as cuenta_contable,
        sf.tipo_cuenta,

        case sf.naturaleza
            when 'D' then
                sum(coalesce(sf.debito_final, 0.00)) - sum(coalesce(sf.credito_final, 0.00))
            when 'C' then
                0.00
        end as debito,

        case sf.naturaleza
            when 'D' then
                0.00
            when 'C' then
                sum(coalesce(sf.credito_final, 0.00)) - sum(coalesce(sf.debito_final, 0.00))
        end as credito
       
    from nic_saldos_finales sf
        join c_elementvalue ev
        on sf.id_cuenta = ev.c_elementvalue_id
    where sf.tipo_cuenta in ('R', 'E') -- Ingresos y Gastos (Estado de Resultado)
        and sf.id_periodo = id_periodo
    group by
        sf.id_cuenta,
        ev.value,
        ev.name,
        sf.tipo_cuenta,
        sf.naturaleza

    union all

    select
        sf.id_cuenta,
        ev.value as cod_cuenta,
        ev.name as cuenta_contable,
        sf.tipo_cuenta,

        case sf.naturaleza
            when 'D' then
                sum(coalesce(sf.debito_final, 0.00)) - sum(coalesce(sf.credito_final, 0.00))
            when 'C' then
                0.00
        end as debito,

        case sf.naturaleza
            when 'D' then
                0.00
            when 'C' then
                sum(coalesce(sf.credito_final, 0.00)) - sum(coalesce(sf.debito_final, 0.00))
        end as credito
       
    from nic_saldos_finales sf
        join c_elementvalue ev
        on sf.id_cuenta = ev.c_elementvalue_id
    where sf.tipo_cuenta in ('R', 'E') -- Ingresos y Gastos (Estado de Resultado)
        and sf.num_periodo <= num_periodo
    group by
        sf.id_cuenta,
        ev.value,
        ev.name,
        sf.tipo_cuenta,
        sf.naturaleza;

    saldo_total = (select sum(sp.credito - debito) from tmp_saldos_previos sp);

    drop table if exists tmp_saldos_cuenta;

    create temporary table tmp_saldos_cuenta as
    select
        sp.id_cuenta,
        sp.cod_cuenta,
        sp.cuenta_contable,
        sp.tipo_cuenta,
        coalesce(sp.debito, 0.00) as debito,
        coalesce(sp.credito, 0.00) as credito
    from tmp_saldos_previos sp
    where (sp.debito <> 0 or sp.credito <> 0)
   
    union all
   
    select
        ev.c_elementvalue_id as id_cuenta,
        ev.value as cod_cuenta,
        ev.name as cuenta_contable,
        ev.accounttype as tipo_cuenta,

        coalesce(case
            when saldo_total > 0 then -- Ganancia
                saldo_total
            else -- Perdida
                0.00
        end, 0.00) as debito,

        coalesce(case
            when saldo_total > 0 then -- Ganancia
                0.00
            else -- Perdida
                saldo_total
        end, 0.00) as credito
       
    from c_elementvalue ev
    where ev.c_elementvalue_id = 1000881 -- 90000002 Pérdida y Ganancia (Credito)
    and saldo_total <> 0;
   
    insert into gl_journalbatch
    (
        gl_journalbatch_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        documentno,
        description,
        postingtype,
        gl_category_id,
        datedoc,
        dateacct,
        c_period_id,
        c_currency_id,
        totaldr,
        totalcr,
        controlamt,
        processing,
        processed,
        copyfrom,
        c_doctype_id,
        docstatus,
        docaction,
        isapproved
    )
    select
        ((select max(gb.gl_journalbatch_id) from gl_journalbatch gb) + 1) as gl_journalbatch_id,
        id_compania as ad_client_id,
        1000001 as ad_org_id,
        cast('Y' as char) as isactive,
        now() as created,
        100 as createdby,
        now() as updated,
        100 as updatedby,
        periodo::varchar(30) as documentno,
        cast('Cierre contable al ' || fecha_fin as varchar(255)) as description,
        cast('A' as char) as postingtype,
        cast(1000000 as numeric(10,0)) as gl_category_id,
        fecha_fin as datedoc,
        fecha_fin as dateacct,
        id_periodo as c_period_id,
        cast(209 as numeric(10,0)) as c_currency_id,
        coalesce(sum(sc.credito), 0.00)::numeric(10,2) as totaldr,
        coalesce(sum(sc.debito), 0.00)::numeric(10,2) as totalcr,
        0::numeric as controlamt,
        'N'::char as processing,
        'N'::char as processed,
        'N'::char as copyfrom,
        1000000::numeric(10,0) as c_doctype_id,
        'DR'::char(2) as docstatus,
        'CO'::char(2) as docaction,
        'N'::char(1) as isapproved
    from tmp_saldos_cuenta sc;

    id_lote =
    (
        select
            max(gb.gl_journalbatch_id)
        from gl_journalbatch gb
    );

    insert into gl_journal
    (
        gl_journal_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        c_acctschema_id,
        c_doctype_id,
        documentno,
        docstatus,
        docaction,
        isapproved,
        isprinted,
        description,
        postingtype,
        gl_category_id,
        datedoc,
        dateacct,
        c_period_id,
        c_currency_id,
        currencyrate,
        gl_journalbatch_id,
        totaldr,
        totalcr,
        controlamt,
        processing,
        processed,
        posted,
        c_conversiontype_id
    )
    select
        ((select max(g.gl_journal_id) from gl_journal g) + 1) as gl_journal_id,
        id_compania as ad_client_id,
        1000001 as ad_org_id,
        cast('Y' as char) as isactive,
        now() as created,
        100 as createdby,
        now() as updated,
        100 as updatedby,
        1000001::numeric(10,0) as c_acctschema_id,
        1000000::numeric(10,0) as c_doctype_id,
        periodo::varchar(30) as documentno,
        'DR'::char(2) as docstatus,
        'CO'::char(2) as docaction,
        'Y'::char as isapproved,
        'N'::char as isprinted,
        cast('Cierre contable al ' || fecha_fin as varchar(255)) as description,
        'A'::char as postingtype,
        1000000::numeric(10,0) as gl_category_id,
        fecha_fin as datedoc,
        fecha_fin as dateacct,
        id_periodo as c_period_id,
        cast(209 as numeric(10,0)) as c_currency_id,
        1::numeric as currencyrate,
        id_lote as gl_journalbatch_id,
        coalesce(sum(sc.credito), 0.00)::numeric(10,2) as totaldr,
        coalesce(sum(sc.debito), 0.00)::numeric(10,2) as totalcr,
        0::numeric as controlamt,
        'N'::char as processing,
        'N'::char as processed,
        'N'::char as posted,
        114::numeric(10,0) as c_conversiontype_id
    from tmp_saldos_cuenta sc;

    id_nota =
    (
        select
            max(g.gl_journal_id)
        from gl_journal g
        where g.gl_journalbatch_id = id_lote
    );

    insert into c_validcombination
    (
        c_validcombination_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        combination,
        description,
        isfullyqualified,
        c_acctschema_id,
        account_id
    )
    with cte_combinaciones_contables as
    (
        select
            id_compania as ad_client_id,
            1000001 as ad_org_id,
            cast('Y' as char) as isactive,
            now() as created,
            100 as createdby,
            now() as updated,
            100 as updatedby,
            'Enterprise-' || sc.cod_cuenta || '-_-_'::varchar(60) as combination,
            'Enterprise-' || sc.cuenta_contable || '-_-_'::varchar(255) as description,
            'Y'::char as isfullyqualified,
            1000001::numeric(10,0) as c_acctschema_id,
            sc.id_cuenta as account_id,
            sc.tipo_cuenta,
            sc.cod_cuenta
        from tmp_saldos_cuenta sc
        where not exists
        (
            select 1 from c_validcombination vc
            where vc.ad_client_id = id_compania
            and vc.combination = 'Enterprise-' || sc.cod_cuenta || '-_-_'
            and vc.description = 'Enterprise-' || sc.cuenta_contable || '-_-_'
        )
    )
    select
        (select max(vc.c_validcombination_id) from c_validcombination vc) +
        row_number() over(order by cc.tipo_cuenta, cc.cod_cuenta) as c_validcombination_id,
        cc.ad_client_id,
        cc.ad_org_id,
        cc.isactive,
        cc.created,
        cc.createdby,
        cc.updated,
        cc.updatedby,
        cc.combination,
        cc.description,
        cc.isfullyqualified,
        cc.c_acctschema_id,
        cc.account_id
    from cte_combinaciones_contables cc;

    insert into gl_journalline
    (
        gl_journalline_id,
        ad_client_id,
        ad_org_id,
        isactive,
        created,
        createdby,
        updated,
        updatedby,
        gl_journal_id,
        line,
        isgenerated,
        description,
        amtsourcedr,
        amtsourcecr,
        c_currency_id,
        currencyrate,
        dateacct,
        amtacctdr,
        amtacctcr,
        c_uom_id,
        qty,
        c_validcombination_id,
        c_conversiontype_id,
        processed,
        a_createasset,
        a_processed
    )
    select
        ((select max(g.gl_journalline_id) from gl_journalline g) +
        row_number() over(order by sc.tipo_cuenta, sc.cod_cuenta)) as gl_journalline_id,
        id_compania as ad_client_id,
        1000001 as ad_org_id,
        cast('Y' as char) as isactive,
        now() as created,
        100 as createdby,
        now() as updated,
        100 as updatedby,
        id_nota as gl_journal_id,
        (row_number() over(order by sc.tipo_cuenta, sc.cod_cuenta) * 10)::numeric(10,0) as line,
        'N'::char as isgenerated,
        sc.cuenta_contable as description,
        sc.credito::numeric(10,2) as amtsourcedr,
        sc.debito::numeric(10,2) as amtsourcecr,
        209::numeric(10,0) as c_currency_id,
        1::numeric as currencyrate,
        fecha_fin as dateacct,
        sc.credito::numeric(10,2) as amtacctdr,
        sc.debito::numeric(10,2) as amtacctcr,
        1000001::numeric(10,0) as c_uom_id,
        0::numeric as qty,
        (select max(vc.c_validcombination_id) from c_validcombination vc
        where vc.ad_client_id = 1000001
        and vc.combination = 'Enterprise-' || sc.cod_cuenta || '-_-_'
        and vc.description = 'Enterprise-' || sc.cuenta_contable || '-_-_') as c_validcombination_id,
        114::numeric(10,0) as c_conversiontype_id,
        'N'::char as processed,
        'N'::char as a_createasset,
        'N'::char as a_processed
    from tmp_saldos_cuenta sc;

    secuencia =
    (
        select update_sequences()
    );

    return true;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;