-- 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;
No hay comentarios:
Publicar un comentario