Scheduled Payments in Adempiere

-- select * from rep_pagos_programados('130815', 0);

drop function if exists rep_pagos_programados(fecha_corte date, id_tercero numeric(10,0));

create or replace function rep_pagos_programados(fecha_corte date, id_tercero numeric(10,0))
returns table
(
    id_factura numeric(10,0),
    num_factura varchar,
    fecha_factura date,
    fecha_vencimiento date,
    id_proveedor numeric(10,0),
    cod_proveedor varchar,
    nombre_proveedor varchar,
    monto_extranjero numeric(10,2),
    monto_nacional numeric(10,2),
    concepto_factura varchar
) as $$
begin
    return query
    (
        with cte_pagos_programados as
        (
            select
                f.c_invoice_id as id_factura_x,
                f.documentno as num_factura_x,
                cast(f.dateinvoiced as date) as fecha_factura_x,
                (f.dateinvoiced + pt.netdays) as fecha_vencimiento_x,
                bp.c_bpartner_id id_proveedor_x,
                bp.value cod_proveedor_x,
                cast(upper(bp.name) as varchar) nombre_proveedor_x,
                cast(case f.c_currency_id
                    when 100 then -- monto extranjero
                        f.grandtotal
                    else
                        0.00
                end as numeric(10,2)) as monto_extranjero_x,
                cast(case f.c_currency_id
                    when 100 then -- monto extranjero (convertir a nacional)
                        f.grandtotal *
                        coalesce((select cr.multiplyrate from c_conversion_rate cr
                        where cr.validfrom = f.dateinvoiced
                        and cr.validto = f.dateinvoiced), 0.00)
                    else
                        f.grandtotal -- monto_nacional
                end as numeric(10,2)) as monto_nacional_x,
                f.description as concepto_factura_x
            from c_invoice f
                join c_paymentterm pt
                on f.c_paymentterm_id = pt.c_paymentterm_id
                join c_bpartner bp
                on f.c_bpartner_id = bp.c_bpartner_id
            where f.ad_client_id = 1000001
                and f.issotrx = 'N' -- no son ventas
                and f.c_doctypetarget_id in (1000005) -- AP Invoice
                and f.docstatus = 'CO' -- completada
                and not exists
                (
                    select 1 from c_payment p
                    where p.ad_client_id = 1000001
                    and p.c_invoice_id = f.c_invoice_id
                )
                and not exists
                (
                    select 1 from c_paymentallocate p
                    where p.ad_client_id = 1000001
                    and p.c_invoice_id = f.c_invoice_id
                )
        )
        select
            c.id_factura_x,
            c.num_factura_x,
            c.fecha_factura_x,
            c.fecha_vencimiento_x,
            c.id_proveedor_x,
            c.cod_proveedor_x,
            c.nombre_proveedor_x,
            c.monto_extranjero_x,
            c.monto_nacional_x,
            c.concepto_factura_x
        from cte_pagos_programados c
        where c.fecha_factura_x <= fecha_corte
            and c.id_proveedor_x = case when id_tercero = 0 then c.id_proveedor_x else id_tercero end
        order by
            c.id_proveedor_x,
            c.fecha_vencimiento_x
    );

end;
$$ language plpgsql;

Notas descuadradas en Adempiere

-- Type: notas_descuadradas

-- DROP TYPE notas_descuadradas;

CREATE TYPE notas_descuadradas AS
   (id_lote numeric(10),
    num_lote character varying,
    id_nota numeric(10),
    num_nota character varying,
    debito numeric(10,2),
    credito numeric(10,2),
    diferencia numeric(10,2));

-- Function: rep_notas_descuadradas(numeric, numeric)

-- DROP FUNCTION rep_notas_descuadradas(numeric, numeric);

CREATE OR REPLACE FUNCTION rep_notas_descuadradas(id_periodo numeric, id_comprobante numeric)
  RETURNS SETOF notas_descuadradas AS
$BODY$
declare
 resultado notas_descuadradas%rowtype;
begin

 drop table if exists tmp_notas_descuadradas;

 create table tmp_notas_descuadradas as
 select
 k.id_lote,
 k.num_lote,
 k.id_nota,
 k.num_nota,
 k.debito,
 k.credito,
 (k.debito - k.credito) diferencia
 from
 (
 select
 gb.gl_journalbatch_id id_lote,
 gb.documentno num_lote,
 g.gl_journal_id id_nota,
 g.documentno num_nota,
 sum(gl.amtacctdr) debito,
 sum(gl.amtacctcr) credito
 from gl_journalbatch gb
 join gl_journal g
 on gb.gl_journalbatch_id = g.gl_journalbatch_id
 join gl_journalline gl
 on g.gl_journal_id = gl.gl_journal_id
 where gb.ad_client_id = 1000001
 --and gb.docstatus = 'CO'
 and gb.c_period_id = case when id_periodo = 0 then gb.c_period_id else id_periodo end
 and gb.gl_journalbatch_id = case when id_comprobante = 0 then gb.gl_journalbatch_id else id_comprobante end
 group by
 gb.gl_journalbatch_id,
 gb.documentno,
 g.gl_journal_id,
 g.documentno
 ) k
 where k.debito <> k.credito;

 for resultado in execute
 'select
        t.id_lote,
        t.num_lote,
        t.id_nota,
        t.num_nota,
        t.debito,
        t.credito,
        t.diferencia
    from tmp_notas_descuadradas t;'
 loop
 return next resultado;
 end loop;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Balanza de Comprobacion en Adempiere

-- Descargar JRXML aqui

-- select * from rep_balanza_comprobacion(1000001, '131201', '131231', 'N');

-- Function: rep_balanza_comprobacion(numeric, date, date, character)

-- DROP FUNCTION rep_balanza_comprobacion(numeric, date, date, character);

CREATE OR REPLACE FUNCTION rep_balanza_comprobacion(IN id_compania numeric, IN finicial date, IN ffinal date,
IN es_cierre_contable character)
  RETURNS TABLE(nombre_compania character varying, c_elementvalue_id numeric, codigocuenta character varying,
  nat character varying, nombrecuenta character varying, ctamayor_id numeric, ctmayor_value numeric,
  ctmayor_name character varying, parent_id numeric, parent_value numeric, parent_name character varying,
  inicialdr numeric, inicialcr numeric, debitos numeric, creditos numeric, finaldr numeric, finalcr numeric,
  fecha_ini date, fecha_fin date) AS
$BODY$
declare
    id_periodo numeric(10,0);
    num_periodo numeric(10,0);

    -- Fecha inicial del ultimo mes
     finicial_ultimo_mes date;
begin
    id_periodo =
    (
        select p.c_period_id from c_period p
        where p.ad_client_id = id_compania
        and p.startdate = finicial
        and p.enddate = ffinal
        and p.periodtype = case when es_cierre_contable = 'Y' then 'A' else 'S' end
    );

    num_periodo =
    (
        select p.periodno from c_period p
        where p.c_period_id = id_periodo
    );

    -- Otener rangos de fechas para el ultimo mes contabilizado
    -- y rango de fechas para el cierre contable

    finicial_ultimo_mes =
    (
        select
            p.startdate
        from fact_acct f
            join c_period p
            on f.c_period_id = p.c_period_id
        where f.dateacct = ffinal
        limit 1
    );

    return query
    (
        with cte_cuentas_contables as
        (
            select
                u.accounttype as tipo_cuenta_x,
                u.c_elementvalue_id as c_elementvalue_id_x,
                u.value as codigocuenta_x,
                cast(u.nat as varchar) as nat_x,
                cast(replace(u.name, ',', '') as varchar) as nombrecuenta_x,
                cast(u.ctamayor_id as numeric(10,0)) as ctamayor_id_x,
                cast(u.ctmayor_value as numeric(10,0)) as ctmayor_value_x,
                u.ctmayor_name as ctmayor_name_x,
                cast(u.parent_id as numeric(10,0)) as parent_id_x,
                cast(u.parent_value as numeric(10,0)) as parent_value_x,
                u.parent_name as parent_name_x
            from usr_v_vcc u
        ),
        cte_saldos_contables as
        (
            -- Movimientos ordinarios de la balanza de comprobacion
            select
                f.account_id as id_cuenta_x,
                cast(f.dateacct as date) as fecha_contable_x,
                coalesce(f.amtacctdr, 0.00) debito_x,
                coalesce(f.amtacctcr, 0.00) credito_x,
                (coalesce(f.amtacctdr, 0.00) - coalesce(f.amtacctcr, 0.00)) as dc,
                (coalesce(f.amtacctcr, 0.00) - coalesce(f.amtacctdr, 0.00)) as cd,
                p.es_cierre_contable as es_cierre_conta
            from fact_acct f
                join c_period p
                on f.c_period_id = p.c_period_id
            where f.ad_table_id = 224
                and f.line_id > 0
                and f.ad_client_id = id_compania
                and p.periodtype IN ('S', 'A')
                and p.es_cierre_contable = 'N'
            union all
            -- Movimientos de los cierres contables de la balanza de comprobacion
            select
                f.account_id as id_cuenta_x,
                cast(f.dateacct as date) as fecha_contable_x,
                coalesce(f.amtacctdr, 0.00) debito_x,
                coalesce(f.amtacctcr, 0.00) credito_x,
                (coalesce(f.amtacctdr, 0.00) - coalesce(f.amtacctcr, 0.00)) as dc,
                (coalesce(f.amtacctcr, 0.00) - coalesce(f.amtacctdr, 0.00)) as cd,
                p.es_cierre_contable as es_cierre_conta
            from fact_acct f
                join c_period p
                on f.c_period_id = p.c_period_id
            where f.ad_table_id = 224
                and f.line_id > 0
                and f.ad_client_id = id_compania
                and p.periodtype IN ('S', 'A')
                and p.es_cierre_contable = 'Y'
        ),
        cte_balanza_comprobacion as
        (
            select
                cc.tipo_cuenta_x,
                cc.c_elementvalue_id_x,
                cc.codigocuenta_x,
                cc.nat_x,
                cc.nombrecuenta_x,
                cc.ctamayor_id_x,
                cc.ctmayor_value_x,
                cc.ctmayor_name_x,
                cc.parent_id_x,
                cc.parent_value_x,
                cc.parent_name_x,

                case cc.nat_x
                    when 'D' then
                        case
                            when es_cierre_contable = 'Y' then
                              
                                -- Si el usuario selecciono cierre contable, significa que el saldo inicial
                                -- es el saldo final del ultimo periodo contable
                                coalesce((select
                                    sum(sc.dc)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00)
                            else
                                -- Saldos iniciales del periodo contable
                                coalesce((select
                                    sum(sc.dc)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x < finicial
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00)
                        end +
                        -- Cierres contables de los periodos contables anteriores
                        coalesce((select
                            sum(sc.dc)
                        from cte_saldos_contables sc
                        where sc.fecha_contable_x < finicial_ultimo_mes
                            and sc.id_cuenta_x = cc.c_elementvalue_id_x
                            and sc.es_cierre_conta = 'Y'), 0.00)
                    else
                        0.00
                end as inicialdr_x,
              
                case cc.nat_x
                    when 'C' then
                        case
                            -- Si el usuario selecciono cierre contable, significa que el saldo inicial
                            -- es el saldo final del ultimo period contable
                            when es_cierre_contable = 'Y' then
                                coalesce((select
                                    sum(sc.cd)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00)
                            else
                                coalesce((select
                                    sum(sc.cd)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x < finicial
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00)
                        end +
                        -- Cierres contables de los periodos contables anteriores
                        coalesce((select
                            sum(sc.cd)
                        from cte_saldos_contables sc
                        where sc.fecha_contable_x < finicial_ultimo_mes
                            and sc.id_cuenta_x = cc.c_elementvalue_id_x
                            and sc.es_cierre_conta = 'Y'), 0.00)
                    else
                        0.00
                end as inicialcr_x,

                coalesce((select
                    sum(sc.debito_x)
                from cte_saldos_contables sc
                where sc.fecha_contable_x between finicial_ultimo_mes and ffinal
                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                    and sc.es_cierre_conta = es_cierre_contable), 0.00) as debitos_x,

                coalesce((select
                    sum(sc.credito_x)
                from cte_saldos_contables sc
                where sc.fecha_contable_x between finicial_ultimo_mes and ffinal
                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                    and sc.es_cierre_conta = es_cierre_contable), 0.00) as creditos_x,              

                case cc.nat_x
                    when 'D' then
                        case
                            when es_cierre_contable = 'Y' then
                                -- Si el usuario selecciono cierre contable, significa que el saldo final
                                -- es el saldo final del ultimo periodo contable
                                coalesce((select
                                    sum(sc.dc)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x), 0.00)
                            else
                                -- Saldos finales del periodo contable
                                coalesce((select
                                    sum(sc.dc)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00) +
                                -- Cierres contables de los periodos contables anteriores
                                coalesce((select
                                    sum(sc.dc)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x < finicial_ultimo_mes
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'Y'), 0.00)                      
                        end
                    else
                        0.00
                end as finaldr_x,

                case cc.nat_x
                    when 'C' then
                        case
                            when es_cierre_contable = 'Y' then
                                -- Si el usuario selecciono cierre contable, significa que el saldo inicial
                                -- es el saldo final del ultimo period contable
                                coalesce((select
                                    sum(sc.cd)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x), 0.00)
                            else
                                -- Saldos finales del periodo contable
                                coalesce((select
                                    sum(sc.cd)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x <= ffinal
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'N'), 0.00) +
                                -- Cierres contables de los periodos contables anteriores
                                coalesce((select
                                    sum(sc.cd)
                                from cte_saldos_contables sc
                                where sc.fecha_contable_x < finicial_ultimo_mes
                                    and sc.id_cuenta_x = cc.c_elementvalue_id_x
                                    and sc.es_cierre_conta = 'Y'), 0.00)
                        end
                    else
                        0.00
                end as finalcr_x
            from cte_cuentas_contables cc
        ),
        cte_compania as
        (
            select
                c.name as nombre_compania_x
            from ad_client c
            where c.ad_client_id = id_compania
        )
        select
            (select c.nombre_compania_x from cte_compania c) as nombre_compania_x,
            bc.c_elementvalue_id_x,
            bc.codigocuenta_x,
            bc.nat_x,
            bc.nombrecuenta_x,
            bc.ctamayor_id_x,
            bc.ctmayor_value_x,
            bc.ctmayor_name_x,
            bc.parent_id_x,
            bc.parent_value_x,
            bc.parent_name_x,
            bc.inicialdr_x,
            bc.inicialcr_x,
            bc.debitos_x,
            bc.creditos_x,
            bc.finaldr_x,
            bc.finalcr_x,
            finicial as fecha_ini_x,
            case when es_cierre_contable = 'Y' then (extract(year from ffinal)::varchar || '1231')::date else ffinal end as fecha_fin_x
        from cte_balanza_comprobacion bc
        where (bc.inicialdr_x <> 0.00
            or bc.inicialcr_x <> 0.00
            or bc.debitos_x <> 0.00
            or bc.creditos_x <> 0.00
            or bc.finaldr_x <> 0.00
            or bc.finalcr_x <> 0.00)
        order by
            case bc.tipo_cuenta_x
                when 'A' then -- Activo
                    1
                when 'L' then -- Pasivo
                    2
                when 'O' then -- Patrimonio
                    3
                when 'R' then -- Ingreso
                    4
                when 'E' then -- Gasto
                    5
                when 'M' then -- Memo
                    6
                else
                    7
            end,
            bc.ctmayor_value_x,
            bc.codigocuenta_x
    );

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Solicitud de Compra de Materia Prima

-- drop type solicitud_compra_materia;

create type solicitud_compra_materia as
(
id_producto numeric(10,0),
cod_producto varchar,
producto varchar,
unidad_medida varchar,
consumo numeric(10,2),
consumo_promedio numeric(10,2),
existencia numeric(10,2),
existencia_meses numeric(10,2),
cantidad_sugerida numeric(10,2)
);


-- drop function rep_solicitud_compra_materia(finicial date, ffinal date);

create or replace function rep_solicitud_compra_materia(finicial date, ffinal date)
returns setof solicitud_compra_materia as $$
declare
resultado solicitud_compra_materia%rowtype;

cantidad_meses numeric(10,2);
begin

cantidad_meses = (cast(ffinal - finicial as numeric(10,2)) + 1) / 30;

RAISE NOTICE 'Cantidad de meses = % ', cantidad_meses;

-- EXISTENCIAS DE LOS PRODUCTOS

drop table if exists tmp_existencia_total;

create temporary table tmp_existencia_total as
select
   s.m_product_id as id_producto,
   sum(s.qtyonhand) as existencia_total
from m_storage s
join m_locator l
on s.m_locator_id = l.m_locator_id
join m_warehouse w
on l.m_warehouse_id = w.m_warehouse_id
where s.ad_client_id = 1000001
and w.m_warehouse_id = 1000001 -- Bodega Materia Prima
group by
   s.m_product_id;

-- CONSUMO DE LOS PRODUCTOS (SALIDAS, VENTAS Y TRASLADOS)

drop table if exists tmp_consumo_total;

create temporary table tmp_consumo_total as
select
   t.m_product_id as id_producto,
   abs(sum(movementqty)) as consumo_total
from m_transaction t
join m_locator l
on t.m_locator_id = l.m_locator_id
join m_warehouse w
on l.m_warehouse_id = w.m_warehouse_id
where t.movementtype in ('I-', 'C-', 'M-')
and t.ad_client_id = 1000001
and w.m_warehouse_id = 1000001 -- Bodega Materia Prima
and t.movementdate between finicial and ffinal
group by
   t.m_product_id;

-- CALCULO DEL CONSUMO PROMEDIO Y LA CANTIDAD SUGERIDA

drop table if exists tmp_solicitud_compra_materia;

create temporary table tmp_solicitud_compra_materia as
select
   p.m_product_id as id_producto,
   p.value as cod_producto,
   p.name as producto,
   u.name as unidad_medida,
   cast(ct.consumo_total as numeric(10,2)) as consumo,
   cast((ct.consumo_total / cantidad_meses) as numeric(10,2)) as consumo_promedio,
   cast(et.existencia_total as numeric(10,2)) as existencia,
   cast(case
when round(ct.consumo_total, 0) = 0 then 0 else et.existencia_total / round(ct.consumo_total, 0)
   end as numeric(10,2)) as existencia_meses,
   case
when round(ct.consumo_total, 0) = 0 then 0
   else
case
when et.existencia_total / round(ct.consumo_total, 0) > 1.5 then 0
else
(1.5 * round(ct.consumo_total, 0)) - et.existencia_total
end
   end as cantidad_sugerida
from m_product p
   join c_uom u
   on p.c_uom_id = u.c_uom_id
   left outer join tmp_existencia_total et
   on p.m_product_id = et.id_producto
   left outer join tmp_consumo_total ct
   on p.m_product_id = ct.id_producto
where p.ad_client_id = 1000001;

-- MOSTRAR RESULTADO

for resultado in execute
'select
t.id_producto,
t.cod_producto,
t.producto,
t.unidad_medida,
coalesce(t.consumo, 0) as consumo,
coalesce(t.consumo_promedio, 0) as consumo_promedio,
coalesce(t.existencia, 0) as existencia,
coalesce(t.existencia_meses, 0) as existencia_meses,
coalesce(t.cantidad_sugerida, 0) as cantidad_sugerida
from tmp_solicitud_compra_materia t
where t.existencia is not null
and t.existencia <> 0
order by
coalesce(t.cantidad_sugerida, 0) desc,
t.id_producto;'
loop
return next resultado;
end loop;

drop table if exists tmp_existencia_total;

drop table if exists tmp_consumo_total;

drop table if exists tmp_solicitud_compra_materia;
end;
$$ language plpgsql;