- Cierre Fiscal: cuando se abarca los períodos (normalmente son 12 meses o períodos) de un año a otro. Por ejemplo: Del 01 Enero 2012 al 31 Diciembre 2013.
- Cierre Transitorio: cuando se abarca los períodos por semestres, trimestres, etc... Por ejemplo: Del 01 Julio 2012 al 31 Diciembre 2013.
En Adempiere, no existe un proceso o pantalla especifica para realizar el cierre contable, porque el sistema maneja por defecto una Contabilidad Perpetua, esto quiere decir que las cuentas de ingresos y gastos nunca se hacen cero y trabajan con saldos acumulados, los períodos contables nunca se cierran. Es por tal motivo que se ha publicado este blog, para mostrar una solución temporal y para crear un nuevo proceso de cierre contable siguiendo determinados pasos.
1) Registrar el período de cierre como un período de ajuste
2) Crear la siguiente funcion PostgreSQL
-- select * from sp_generar_cierre_contable(1000001, 1000073);
create or replace function sp_generar_cierre_contable(id_compania numeric(10,0), id_periodo numeric(10,0))
returns boolean as $$
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;
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_cuenta;
create temporary table tmp_saldos_cuenta as
select
ev.c_elementvalue_id as id_cuenta,
ev.value as cod_cuenta,
ev.name as cuenta_contable,
ev.accounttype as tipo_cuenta,
case ev.accountsign
when 'D' then
sum(coalesce(amtacctdr, 0.00) - coalesce(amtacctcr, 0.00))
when 'C' then
0.00
else
0.00
end as debito,
case ev.accountsign
when 'D' then
0.00
when 'C' then
sum(coalesce(amtacctcr, 0.00) - coalesce(amtacctdr, 0.00))
else
0.00
end as credito
from c_elementvalue ev
join fact_acct f
on f.account_id = ev.c_elementvalue_id
join c_period p
on f.c_period_id = p.c_period_id
where ev.ad_client_id = id_compania
and ev.accounttype in ('R', 'E')
and p.periodno < num_periodo
group by
ev.c_elementvalue_id,
ev.value,
ev.name,
ev.accountsign,
ev.accounttype;
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, -- Centro de Costo
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,
sum(sc.credito) as totaldr,
sum(sc.debito) 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, -- Centro de Costo
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,
sum(sc.credito) as totaldr,
sum(sc.debito) 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, -- Centro de Costo
cast('Y' as char) as isactive,
now() as created,
100 as createdby,
now() as updated,
100 as updatedby,
'Centro de Costo-' || sc.cod_cuenta || '-_-_'::varchar(60) as combination,
'Centro de Costo-' || 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 = 'Centro de Costo-' || sc.cod_cuenta || '-_-_'
and vc.description = 'Centro de Costo-' || 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, -- Centro de Costo
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 as amtsourcedr,
sc.debito as amtsourcecr,
209::numeric(10,0) as c_currency_id,
1::numeric as currencyrate,
fecha_fin as dateacct,
sc.credito as amtacctdr,
sc.debito 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 = 'Centro de Costo-' || sc.cod_cuenta || '-_-_'
and vc.description = 'Centro de Costo-' || 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;
$$ language plpgsql;
3) Crear una nueva clase Java y agregar el customization al proyecto.
package org.compiere.process;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//import java.sql.SQLException;
import java.util.logging.Level;
import org.compiere.util.DB;
import org.compiere.util.Env;
public class GenerarCierreContable extends SvrProcess
{
private int p_C_Period_ID = 0;
protected void prepare()
{
ProcessInfoParameter[] para = getParameter();
for(int k = 0; k < para.length; k++)
{
String nombre = para[k].getParameterName();
if(para[k].getParameter() == null)
;
else if (nombre.equals("C_Period_ID"))
this.p_C_Period_ID = ((BigDecimal)para[k].getParameter()).intValue();
else
log.log(Level.SEVERE, "Parámetro desconocido: " + nombre);
}
}
protected String doIt() throws Exception
{
this.GenerarCierre();
return "";
}
public void GenerarCierre()
{
@SuppressWarnings("unused")
boolean b = false;
String id_compania = String.valueOf(Env.getAD_Client_ID(getCtx()));
String id_usuario = String.valueOf(Env.getAD_User_ID(getCtx()));
String id_periodo = String.valueOf(p_C_Period_ID);
String sql = "select * from sp_generar_cierre_contable(" +
id_compania + "," + id_periodo + ");";
PreparedStatement ps = null;
ResultSet rs = null;
try
{
ps = DB.prepareStatement(sql, get_TrxName());
rs = ps.executeQuery();
while(rs.next())
{
b = rs.getBoolean(1);
}
//int no = DB.executeUpdateEx(sql.toString(), null);
//addLog(1, null, BigDecimal.valueOf(no), "Registros Insertados");
}
catch(Exception e)
{
log.log(Level.SEVERE, sql, e);
}
finally
{
DB.close(rs, ps);
rs = null;
ps = null;
}
}
}
4) Listo, ahora puedes comprobar la información en el reporte de Balanza de Comprobacion
No hay comentarios:
Publicar un comentario