DECLARE rd_scoplani estado_cta_cte%rowtype; begin FOR rd_scoplani IN SELECT '' AS idccodpl, det.steccoen AS idccodso, to_date(eg.stedfech, 'YYYY/MM/DD'::text) AS sccdfech, 'EGR' AS scctipo, SUM(CASE WHEN (det.idccodcu ~~ '5011%'::text) THEN CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0.00 END) AS sccoapor, SUM(CASE WHEN ((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~ '1412%'::text) OR (det.idccodcu ~~ '166201'::text) OR (det.idccodcu ~~ '141501'::text)) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccopram, SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccoprin, SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccoprmo, SUM(CASE WHEN (((((det.idccodcu ~~ '1661%'::text) OR (det.idccodcu ~~ '7551%'::text)) AND (det.idccodcu <> '166106'::character varying)) AND (det.idccodcu <> '166107'::character varying)) AND (det.idccodcu <> '166108'::character varying) AND (det.idccodcu <> '166111'::character varying)) THEN det.stenmont ELSE 0::numeric END) AS sccoacti, SUM(CASE WHEN ((det.idccodcu = '166106'::character varying) OR (det.idccodcu = '751101'::character varying)) THEN CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccomant, SUM(CASE WHEN ((det.idccodcu = '166107'::character varying) OR (det.idccodcu = '757101'::character varying)) THEN CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccofose, SUM(CASE WHEN ((det.idccodcu = '166108'::character varying) OR (det.idccodcu = '166111'::character varying) OR (det.idccodcu = '754101'::character varying)) THEN det.stenmont ELSE 0::numeric END) AS sccomult, det.idcegres AS idcodocu, det.stecsueg AS idcsucur, SUM(CASE WHEN (det.idccodcu = '141401'::character varying) THEN (0-det.stenmont) ELSE 0::numeric END) AS sccocred, SUM(CASE WHEN (((det.idccodcu = '465102'::character varying) OR (det.idccodcu = '465101'::character varying)) OR (det.idccodcu = '464101'::character varying)) THEN (0-det.stenmont) ELSE 0::numeric END) AS sccootro, '' AS sccoefco, '' AS scoperio FROM stecabeg eg, stedeteg det WHERE (eg.idcegres = det.idcegres) AND (eg.stecsueg = det.stecsueg) AND (eg.stectien = det.stectien) AND eg.stectien = 'SOCIO'::character varying AND det.stectien = 'SOCIO'::character varying AND det.steccoen = pc_idccodso AND eg.anulado = 0::numeric AND eg.eliminar = 0::numeric GROUP BY det.steccoen, eg.stedfech, det.idcegres, det.stecsueg UNION ALL SELECT '' AS idccodpl, det.steccoen AS idccodso, to_date(ing.stedfech, 'YYYY/MM/DD'::text) AS sccdfech, 'ING' AS scctipo, SUM(CASE WHEN (det.idccodcu ~~ '5011%'::text) THEN det.stenmont ELSE 0.00 END) AS sccoapor, SUM(CASE WHEN (((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~ '1412%'::text) OR (det.idccodcu ~~ '1415%'::text))) THEN (0-det.stenmont) ELSE 0::numeric END) AS sccopram, SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccoprin, SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccoprmo, SUM(CASE WHEN (((det.idccodcu IN (SELECT ta.idccodcu FROM scrtipac ta WHERE idtipact NOT IN ('00006','00013'))) OR (det.idccodcu ~~ '7551%'::text)) AND (det.idccodcu NOT IN ('755106'))) THEN CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccoacti, SUM(CASE WHEN ((det.idccodcu = '166106'::character varying) OR (det.idccodcu = '751101'::character varying)) THEN CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE det.stenmont END ELSE 0::numeric END) AS sccomant, SUM(CASE WHEN ((det.idccodcu = '166107'::character varying) OR (det.idccodcu = '757101'::character varying)) THEN det.stenmont ELSE 0::numeric END) AS sccofose, SUM(CASE WHEN ((det.idccodcu = '166108'::character varying) OR (det.idccodcu = '754101'::character varying) OR (det.idccodcu = '166111'::character varying)) THEN det.stenmont ELSE 0::numeric END) AS sccomult, det.idcingre AS idcodocu, ing.idcsucur, SUM(CASE WHEN ((det.idccodcu ~~ '1413%'::text) OR (det.idccodcu ~~ '1414%'::text)) THEN (0-det.stenmont) ELSE 0::numeric END) AS sccocred, SUM(CASE WHEN (((det.idccodcu = '465102'::character varying) OR (det.idccodcu = '465101'::character varying)) OR (det.idccodcu = '464101'::character varying) OR (det.idccodcu ~~ '751201%'::text)) THEN det.stenmont ELSE 0::numeric END) AS sccootro, '' AS sccoefco, '' AS scoperio FROM stecabin ing, stedetin det WHERE (ing.idcingre = det.idcingre) AND (ing.stectien = det.stectien) AND (ing.idcsucur = det.idcsucur) AND ing.stectien = 'SOCIO'::character varying AND det.stectien = 'SOCIO'::character varying AND det.steccoen = pc_idccodso AND ing.eliminar = 0::numeric AND ing.anulado = 0::numeric GROUP BY det.steccoen, ing.stedfech, det.idcingre, ing.idcsucur UNION ALL SELECT '' AS idccodpl, det.sccccoen AS idccodso, asi.sccdfech, 'TRAN' AS scctipo, SUM(CASE WHEN (det.idccodcu ~~ '5011%'::text) THEN CASE WHEN det.sccnafec = 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0.00 END) AS sccoapor, SUM(CASE WHEN ((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~ '1412%'::text) OR (det.idccodcu ~~ '1415%'::text) OR (det.idccodcu ~~ '166201%'::text)) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccopram, SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccoprin, SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccoprmo, SUM(CASE WHEN (((det.idccodcu IN (SELECT ta.idccodcu FROM scrtipac ta WHERE idtipact NOT IN ('00006','00013'))) OR (det.idccodcu ~~ '7551%'::text)) AND (det.idccodcu NOT IN ('755106'))) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccoacti, SUM(CASE WHEN ((det.idccodcu = '166106'::character varying) OR (det.idccodcu = '751101'::character varying)) THEN CASE WHEN det.sccnafec = 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccomant, SUM(CASE WHEN ((det.idccodcu = '166107'::character varying) OR (det.idccodcu = '757101'::character varying)) THEN CASE WHEN det.sccnafec = 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccofose, SUM(CASE WHEN ((det.idccodcu = '166108'::character varying) OR (det.idccodcu = '754101'::character varying)) THEN det.sccnmont ELSE 0::numeric END) AS sccomult, asi.idctrans || '*' || det.idccodas AS idcodocu, asi.idcsucur, SUM(CASE WHEN ((det.idccodcu ~~ '1413%'::text) OR (det.idccodcu ~~ '1414%'::text)) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccocred, SUM(CASE WHEN (((det.idccodcu = '465102'::character varying) OR (det.idccodcu = '465101'::character varying)) OR (det.idccodcu = '464101'::character varying)) THEN det.sccnmont ELSE 0::numeric END) AS sccootro, '' AS sccoefco, '' AS scoperio FROM (scccabas asi JOIN sccdetas det ON ((asi.idccodas = det.idccodas))) WHERE upper((asi.sccctiop)::text) = 'AT'::text AND det.idccodtd = '038'::character varying AND det.sccccoen = pc_idccodso AND asi.eliminar = 0::numeric AND asi.sccnanul = 0::numeric GROUP BY det.sccccoen, asi.sccdfech, det.idccodas, asi.idcsucur, asi.idctrans UNION ALL SELECT pl.idccodpl, pl.idccodso, pl.sccdfech, pl.sccctipo AS scctipo, pl.sccoapor, (0-pl.sccopram) AS sccopram, (0-pl.sccoprin) AS sccoprin, ((0-pl.sccoprmo)+(0-pl.sccomocr)) AS sccoprmo, (0-pl.sccoacti) AS sccoacti, pl.sccomant, pl.sccofose, pl.sccomult, '' AS idcodocu, '' AS idcsucur, (0-pl.sccocred) AS sccocred, coalesce(pl.scondein,0.00) AS sccootro, pl.sccoefco, pl.scoperio FROM scoplani pl WHERE pl.idccodso = pc_idccodso AND coalesce(pl.eliminar,0) = 0 UNION ALL SELECT '' AS idccodpl, ord.idccodso, ord.scrdfech AS sccdfech, 'BOLE' AS scctipo, 0 AS sccoapor, 0 AS sccopram, 0 AS sccoprin, 0 AS sccoprmo, 0 AS sccoacti, 0 AS sccomant, 0 AS sccofose, 0 AS sccomult, bo.scrcseri || '*' || bo.scrcnume || '*' || bo.idccodbo AS idcodocu, ord.idcsucur, bo.scrntota AS sccocred, 0 AS sccootro, '' AS sccoefco, '' AS scoperio FROM scrordcr ord INNER JOIN scrcabbo bo ON bo.idccodbo = ord.idccodbo WHERE ord.scrctipo = 'OCTO2' AND ord.scrnesta = 1 AND ord.idccodso = pc_idccodso AND ord.eliminar = 0 AND ord.anulado = 0 AND bo.scrnanul = 0 AND bo.eliminar = 0 UNION ALL SELECT '' AS idccodpl, ord.idccodso, ord.scrdfech AS sccdfech, 'CRED' AS scctipo, 0 AS sccoapor, 0 AS sccopram, 0 AS sccoprin, 0 AS sccoprmo, 0 AS sccoacti, 0 AS sccomant, 0 AS sccofose, 0 AS sccomult, ord.scrcseri || '*' || ord.scrcnume || '*' || ord.idccodoc AS idcodocu, ord.idcsucur, ord.scrnmoau AS sccocred, 0 AS sccootro, '' AS sccoefco, '' AS scoperio FROM scrordcd ord WHERE ord.idccodso = pc_idccodso AND ord.scrnesta = 0 AND ord.eliminar = 0 AND ord.anulado = 0 UNION ALL SELECT '' AS idccodpl, det.sccccoen AS idccodso, asi.sccdfech, 'TRAN' AS scctipo, 0::numeric AS sccoapor, 0::numeric AS sccopram, 0::numeric AS sccoprin, 0::numeric AS sccoprmo, 0::numeric AS sccoacti, 0::numeric AS sccomant, 0::numeric AS sccofose, 0::numeric AS sccomult, det.idccodas AS idcodocu, asi.idcsucur, SUM(CASE WHEN ((det.idccodcu ~~ '1413%'::text) OR (det.idccodcu ~~ '1414%'::text)) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccocred, 0::numeric AS sccootro, '' AS sccoefco, '' AS scoperio FROM (scccabas asi JOIN sccdetas det ON ((asi.idccodas = det.idccodas))) WHERE UPPER((asi.sccctiop)::text) = 'AD'::text AND det.idccodtd = '041'::character varying AND asi.idccodor = '00006' AND det.sccccoen = pc_idccodso AND asi.eliminar = 0::numeric AND asi.sccnanul = 0::numeric GROUP BY det.sccccoen, asi.sccdfech, det.idccodas, asi.idcsucur UNION ALL SELECT '' AS idccodpl, det.sccccoen AS idccodso, asi.sccdfech, 'REFI' AS scctipo, 0::numeric AS sccoapor, SUM(CASE WHEN ((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~ '1412%'::text) OR (det.idccodcu ~~ '166201'::text) OR (det.idccodcu ~~ '1415%'::text)) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccopram, SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccoprin, SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN CASE WHEN det.sccnafec != 1 THEN (0-det.sccnmont) ELSE det.sccnmont END ELSE 0::numeric END) AS sccoprmo, 0::numeric AS sccoacti, 0::numeric AS sccomant, 0::numeric AS sccofose, 0::numeric AS sccomult, det.idccodas AS idcodocu, asi.idcsucur, 0::numeric AS sccocred, 0::numeric AS sccootro, '' AS sccoefco, '' AS scoperio FROM (scccabas asi JOIN sccdetas det ON ((asi.idccodas = det.idccodas))) WHERE UPPER((asi.sccctiop)::text) = 'AD' AND det.idccodtd = '037' AND asi.idccodor = '00008' AND det.sccccoen = pc_idccodso AND asi.eliminar = 0 AND asi.sccnanul = 0 GROUP BY det.sccccoen, asi.sccdfech, det.idccodas, asi.idcsucur UNION ALL SELECT '' AS idccodpl, acti.idccodso, to_date(acti.scrdfech, 'YYYY/MM/DD'::text) AS sccdfech, 'ACT' AS scctipo, 0 AS sccoapor, 0 AS sccopram, 0 AS sccoprin, 0 AS sccoprmo, acti.scrnmont AS sccoacti, 0 AS sccomant, 0 AS sccofose, 0 AS sccomult, acti.scrcsoli AS idcodocu, acti.idccodsu AS idcsucur, 0 AS sccocred, 0 AS sccootro, '' AS sccoefco, '' AS scoperio FROM scrpresta acti WHERE acti.scrctipo = 'activ' AND acti.idccodso = pc_idccodso AND acti.idccodpr NOT IN ('00006') AND acti.eliminar = 0 AND acti.scrindac IN ('so','ad','ct') LOOP RETURN NEXT rd_scoplani; END LOOP; RETURN; END