-- select rc.* from vw_recepciones_contabilizadas rc;
-- drop view vw_recepciones_contabilizadas;
create view vw_recepciones_contabilizadas as
(
select
e.m_inout_id cod_recepcion,
e.documentno no_recepcion,
e.description descripcion,
cast('C$' as character varying) moneda,
o.ad_org_id cod_organizacion,
o.name organizacion,
ev.value cod_cuenta,
evt.name cuenta_contable,
f.amtacctdr debito,
f.amtacctcr credito,
coalesce(p.m_product_id, 0) id_producto,
coalesce(p.value, '') cod_producto,
coalesce(pt.name, '') producto,
f.dateacct fecha_contable,
pp.c_period_id cod_periodo,
pp.name periodo,
cast('Actual' as character varying) tipo_aplicacion -- ad_ref_list
from fact_acct f
join ad_org o
on f.ad_org_id = o.ad_org_id
join c_elementvalue ev
on f.account_id = ev.c_elementvalue_id
join c_elementvalue_trl evt
on (ev.c_elementvalue_id = evt.c_elementvalue_id
and evt.ad_language = 'es_CO')
left outer join m_product p
on f.m_product_id = p.m_product_id
left outer join m_product_trl pt
on (p.m_product_id = pt.m_product_id
and pt.ad_language = 'es_CO')
join c_period pp
on f.c_period_id = pp.c_period_id
join m_inout e
on f.record_id = e.m_inout_id
where f.ad_client_id = 1000000
and f.postingtype = 'A'
and f.c_acctschema_id = 1000000
and f.ad_table_id = 319 -- Shipment / Receipt
--and f.record_id = 1000061
and e.isactive = 'Y'
and e.processed = 'Y'
and e.posted = 'Y'
and f.fact_acct_id not in
(select
pa.record_id
from ad_private_access pa
where pa.ad_table_id = 270 -- GLJournal
and pa.ad_user_id <> 0
and pa.isactive = 'Y')
order by
f.fact_acct_id desc
);
/*
drop type recepciones_contabilizadas;
create type recepciones_contabilizadas as
(
cod_recepcion numeric(10,0),
no_recepcion character varying,
descripcion character varying,
moneda character varying,
cod_organizacion numeric(10,0),
organizacion character varying(60),
cod_cuenta character varying(40),
cuenta_contable character varying(60),
debito numeric,
credito numeric,
id_producto numeric,
cod_producto character varying,
producto character varying,
fecha_contable timestamp without time zone,
cod_periodo numeric(10,0),
periodo character varying(60),
tipo_aplicacion character varying
);
*/
-- drop function rep_recepciones_contabilizadas(finicial date, ffinal date, num_recepcion character varying);
-- select * from rep_recepciones_contabilizadas(0, 0);
-- select * from rep_recepciones_contabilizadas('2011-11-01', '2011-12-30', '0');
create or replace function rep_recepciones_contabilizadas(finicial date, ffinal date, num_recepcion numeric)
returns setof recepciones_contabilizadas as $$
declare
resultado recepciones_contabilizadas%rowtype;
begin
drop table if exists tmp_recepciones_contabilizadas;
create table tmp_recepciones_contabilizadas as
select
rc.*
from vw_recepciones_contabilizadas rc
where rc.fecha_contable between finicial and ffinal
and rc.cod_recepcion = case when num_recepcion = 0 then rc.cod_recepcion else num_recepcion end;
for resultado in execute
'select
t.*
from tmp_recepciones_contabilizadas t;'
loop
return next resultado;
end loop;
end;
$$ language plpgsql;
No hay comentarios:
Publicar un comentario