-- Function: reportes.fn_rec_ing_may(integer, integer, integer, integer, integer)
-- DROP FUNCTION reportes.fn_rec_ing_may(integer, integer, integer, integer, integer);
CREATE OR REPLACE FUNCTION reportes.fn_rec_ing_may(pmes_ini integer, pmes_fin integer, pano integer, pide_caj integer, pide_s_p integer)
RETURNS SETOF reportes.tp_rec_ing_may AS
$BODY$
declare
qrow "reportes"."tp_rec_ing_may" ;
begin
set search_path = logistica, ppto, siam_siar, tesoreria,reportes, pg_catalog,public ;
FOR qrow IN
select nom_per(p.pat_per,p.mat_per, p.nom_per)::character(150) as nom_caj,
((((((pp.cod_par)::text || ('-'::bpchar)::text) || (sp.cod_s_p)::text) ||('-'::bpchar)::text) || (sp.des_s_p)::text))::character(100) as sub_par,
(fte.cod_fte)||' - '||trim(fte.des_fte) as nom_fte,
sum(imp_par) as imp_par,sum(imp_ene) as imp_ene,sum(imp_feb) as imp_feb,
sum(imp_mar)as imp_mar,sum(imp_abr)as imp_abr,sum(imp_may)as imp_may,sum(imp_jun)as imp_jun,sum(imp_jul)as imp_jul,
sum(imp_ago)as imp_ago,sum(imp_set)as imp_set,sum(imp_oct)as im_oct,
sum(imp_nov)as imp_nov,sum(imp_dic)as imp_dic
from
(select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,det.imp_par as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini>= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 1 and
pmes_ini <= 1 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,det.imp_par as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 1 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 2 and
pmes_ini <= 2 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,det.imp_par as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 2 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 3 and
pmes_ini <= 3 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,det.imp_par as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 3 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 4 and
pmes_ini <= 4 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
det.imp_par as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 4 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) =5 and
pmes_ini <= 5 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,det.imp_par as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 5 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 6 and
pmes_ini <= 6 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,det.imp_par as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 6 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) =7 and
pmes_ini <= 7 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,det.imp_par as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 7 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 8 and
pmes_ini <= 8 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,det.imp_par as imp_set,
0 as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 8 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 9 AND
pmes_ini <= 9 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
det.imp_par as imp_oct,0 as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 9 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 10 and
pmes_ini <= 10 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,det.imp_par as imp_nov,0 as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 10 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 11 and
pmes_ini <= 11 and
extract(year from cab.fch_rei) = pano
union all
select cab.ide_caj,
cab.fch_rei as fch_rei,cab.flg_anu as flg_anu,
det.ide_s_p,
imp_par,0 as imp_ene,0 as imp_feb,0 as imp_mar,0 as imp_abr,
0 as imp_may,0 as imp_jun,0 as imp_jul,0 as imp_ago,0 as imp_set,
0 as imp_oct,0 as imp_nov,det.imp_par as imp_dic
FROM tesoreria.ca_re_in AS cab
LEFT JOIN tesoreria.de_re_in as det
ON cab.ide_rei = det.ide_rei
WHERE (pide_caj=0 or cab.ide_caj = pide_caj) and
(pide_s_p=0 or det.ide_s_p = pide_s_p) and
cab.flg_anu = 0 and
pmes_ini + 11 >= extract(month from cab.fch_rei) and
extract(month from cab.fch_rei) <= pmes_fin and
extract(month from cab.fch_rei) = 12 and
pmes_ini <= 12 and
extract(year from cab.fch_rei) = pano ) as tempo01
left join tesoreria.cajeros c
on tempo01.ide_caj = c.ide_caj
left join siam_siar.personas p
on c.ide_tra = p.ide_per
left join tesoreria.sub_par_ing sp
on tempo01.ide_s_p = sp.ide_s_p
left join ppto.par_ing pp
on sp.ide_par = pp.ide_par
left join ppto.fte_fto fte
on pp.ide_fte = fte.ide_fte
group by sub_par,nom_fte,nom_caj
order by nom_fte,sub_par
loop
return next qrow;
end loop ;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION reportes.fn_rec_ing_may(integer, integer, integer, integer, integer) OWNER TO postgres;
Função!
Pela lógica essa função ta correta, mas ela não ta funfando.. antes quando era mais simples pegava normal ! tem algum erro ?
Obrigado !
1 Resposta
E
Deixe do jeito que ela era (simples), e vá complicando a função aos poucos.
Criado 2 de agosto de 2010
Ultima resposta 3 de ago. de 2010
Respostas 1
Participantes 2