alterando procedure

Scripts Diversos para o Oracle SQL*Plus. (Relacionado a ferramenta Oracle SQL*Plus, para questões de SQL, usar o forum SQL)
Responder
wilke
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Sex, 23 Nov 2007 9:26 am
Localização: porto velho -ro

ola pessoal , eu comercei a trabalhar com procedure , e estou tendo dificuldade em poder filtrar alfuns resultado voces poderia dar essa ajuda. :cry:
essa e minha procedure e nela eu teria que filtra bloquear uns valores ,que não são corretos.

Selecionar tudo

CREATE or replace PROCEDURE PR_DESEMBOLSO_GRUPO ( FL_LIQUIDADO_ABERTO IN VARCHAR2,
                                                                                                                                                    DATA1 IN VARCHAR2,
                                                  DATA2 IN VARCHAR2,
                                                  ID_SESSION OUT NUMBER ) AS
-- Geral em aberto
CURSOR CR_GERAL_A IS
   SELECT TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH') AS NM_mês,
   GC.CD_SUB_GRUPO_CONTABIL, GC.NM_SUB_GRUPO_CONTABIL,
   SUM(VL_FORNECEDOR1)  AS VL_LOJA1,
   SUM(VL_FORNECEDOR2)  AS VL_LOJA2,
   SUM(VL_FORNECEDOR3)  AS VL_LOJA3,
   SUM(VL_FORNECEDOR4)  AS VL_LOJA4,
   SUM(VL_FORNECEDOR5)  AS VL_LOJA5,
   SUM(VL_FORNECEDOR6)  AS VL_LOJA6,
   SUM(VL_FORNECEDOR7)  AS VL_LOJA7,
   SUM(VL_FORNECEDOR9)  AS VL_LOJA8,
   SUM(VL_FORNECEDOR10) AS VL_LOJA9,
   SUM(VL_FORNECEDOR11) AS VL_LOJA10,
   SUM(VL_FORNECEDOR12) AS VL_LOJA11,
   SUM(VL_FORNECEDOR13) AS VL_LOJA12,
   SUM(VL_FORNECEDOR14) AS VL_LOJA13,
   SUM(VL_FORNECEDOR15) AS VL_LOJA14,
   SUM(VL_FORNECEDOR8)  AS VL_LOJA15
   FROM (SELECT MP.DT_VENCIMENTO, MP.CD_CONTA_CONTABIL,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR9,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR10,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR11,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR12,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR13,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR14,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR15,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_FORNECEDOR8
         FROM TB_MOV_PAGAR MP
         WHERE MP.DT_VENCIMENTO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
         AND   MP.DT_VENCIMENTO IS NOT NULL
         AND   MP.FL_POSICAO = 'O'
         AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
         GROUP BY MP.DT_VENCIMENTO, MP.CD_CONTA_CONTABIL) MP,
   TB_SUB_GRUPO_CONTABIL GC, TB_PLANO_CONTA PN
   WHERE GC.NU_PLANO = '1'
   AND   GC.CD_SUB_GRUPO_CONTABIL NOT IN ('1.112','2.111')
   AND   GC.CD_SUB_GRUPO_CONTABIL = PN.CD_SUB_GRUPO_CONTABIL
   AND   PN.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
   GROUP BY TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH'),
         GC.CD_SUB_GRUPO_CONTABIL, GC.NM_SUB_GRUPO_CONTABIL
   ORDER BY GC.NM_SUB_GRUPO_CONTABIL;
ST_GERAL_A CR_GERAL_A%ROWTYPE;
-- Geral Liquidado
CURSOR CR_GERAL_L IS
   SELECT TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH') AS NM_mês,
   GC.CD_SUB_GRUPO_CONTABIL, GC.NM_SUB_GRUPO_CONTABIL,
   SUM(VL_FORNECEDOR1)  AS VL_LOJA1,
   SUM(VL_FORNECEDOR2)  AS VL_LOJA2,
   SUM(VL_FORNECEDOR3)  AS VL_LOJA3,
   SUM(VL_FORNECEDOR4)  AS VL_LOJA4,
   SUM(VL_FORNECEDOR5)  AS VL_LOJA5,
   SUM(VL_FORNECEDOR6)  AS VL_LOJA6,
   SUM(VL_FORNECEDOR7)  AS VL_LOJA7,
   SUM(VL_FORNECEDOR9)  AS VL_LOJA8,
   SUM(VL_FORNECEDOR10) AS VL_LOJA9,
   SUM(VL_FORNECEDOR11) AS VL_LOJA10,
   SUM(VL_FORNECEDOR12) AS VL_LOJA11,
   SUM(VL_FORNECEDOR13) AS VL_LOJA12,
   SUM(VL_FORNECEDOR14) AS VL_LOJA13,
   SUM(VL_FORNECEDOR15) AS VL_LOJA14,
   SUM(VL_FORNECEDOR8)  AS VL_LOJA15
   FROM (SELECT MP.DT_LIQUIDACAO, MP.CD_CONTA_CONTABIL,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR9,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR10,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR11,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR12,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR13,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR14,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_FORNECEDOR15,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_FORNECEDOR8
         FROM TB_MOV_PAGAR MP
         WHERE MP.DT_LIQUIDACAO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
         AND   MP.DT_LIQUIDACAO IS NOT NULL
         AND   MP.FL_POSICAO = 'L'
         --AND ((pg.fl_posicao <> 'C' and pg.fl_posicao <> 'T') or  pg.fl_posicao IS NULL)
         AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
         GROUP BY MP.DT_LIQUIDACAO, MP.CD_CONTA_CONTABIL) MP,
   TB_SUB_GRUPO_CONTABIL GC, TB_PLANO_CONTA PN
   WHERE GC.NU_PLANO = '1'
   AND   GC.CD_SUB_GRUPO_CONTABIL NOT IN ('1.112','2.111')
   AND   GC.CD_SUB_GRUPO_CONTABIL = PN.CD_SUB_GRUPO_CONTABIL
   AND   PN.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
   GROUP BY TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH'),
         GC.CD_SUB_GRUPO_CONTABIL, GC.NM_SUB_GRUPO_CONTABIL
   ORDER BY GC.NM_SUB_GRUPO_CONTABIL;
ST_GERAL_L CR_GERAL_L%ROWTYPE;
-- fornecedor em aberto
CURSOR CR_FORN_A IS
   SELECT -- /* + INDEX( F IE_FORNECEDOR2 ) */
   TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH') AS NM_mês,
   NM_GRUPO,
   SUM(VL_LOJA1) AS VL_LOJA1,
   SUM(VL_LOJA2) AS VL_LOJA2,
   SUM(VL_LOJA3) AS VL_LOJA3,
   SUM(VL_LOJA4) AS VL_LOJA4,
   SUM(VL_LOJA5) AS VL_LOJA5,
   SUM(VL_LOJA6) AS VL_LOJA6,
   SUM(VL_LOJA7) AS VL_LOJA7,
   SUM(VL_LOJA8) AS VL_LOJA8,
   SUM(VL_LOJA9) AS VL_LOJA9,
   SUM(VL_LOJA10) AS VL_LOJA10,
   SUM(VL_LOJA11) AS VL_LOJA11,
   SUM(VL_LOJA12) AS VL_LOJA12,
   SUM(VL_LOJA13) AS VL_LOJA13,
   SUM(VL_LOJA14) AS VL_LOJA14,
   SUM(VL_LOJA15) AS VL_LOJA15
   FROM (SELECT MP.CD_PAGAR,
                fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1') as dt_vencimento,
         DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES') AS NM_GRUPO,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
         FROM TB_MOV_PAGAR MP --TB_FORNECEDOR F
         WHERE -- F.CD_CONTABIL = MP.CD_CONTA_CONTABIL
              (MP.CD_CONTA_CONTABIL <> '25.10000' OR MP.CD_CONTA_CONTABIL IS NULL) AND
               MP.FL_POSICAO = 'O'
         AND -- MP.DT_VENCIMENTO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2) AND
             (((mp.dt_vencimento_boleta is null and mp.dt_prorrogacao is null and mp.dt_vencimento between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2))
              or mp.dt_prorrogacao between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2)
              and mp.dt_prorrogacao > decode(mp.dt_vencimento_boleta,null,FN_CONV_DATA('01/01/1920'),mp.dt_vencimento_boleta))
              or mp.dt_vencimento_boleta between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2)
              and mp.dt_vencimento_boleta > decode(mp.dt_prorrogacao,null,FN_CONV_DATA('01/01/1920'),mp.dt_prorrogacao))
              AND MP.DT_LIQUIDACAO IS NULL
              AND ((MP.FL_POSICAO <> 'C' AND MP.FL_POSICAO <> 'L') OR MP.FL_POSICAO IS NULL) -- AND
              AND ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
              --NOT EXISTS ( SELECT CD_CONTA_CONTABIL
              --             FROM TB_TRANSPORTADOR T
              --             WHERE T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL)
         GROUP BY fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1'),
                  MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES')) MP,
   TB_PAGAR PG
   WHERE  PG.CD_PAGAR = MP.CD_PAGAR AND
          (PG.CD_GRUPO_CAIXA = '15005' OR PG.CD_GRUPO_CAIXA = '56005') AND
           PG.CD_PESSOA <> '34629005' AND --Ariquemes
           
           ((PG.FL_POSICAO <> 'T') OR PG.FL_POSICAO IS NULL)
   GROUP BY TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH'), NM_GRUPO;
ST_FORN_A CR_FORN_A%ROWTYPE;
-- fornecedor liquidado
CURSOR CR_FORN_L IS
   SELECT -- /* + INDEX( F IE_FORNECEDOR2 ) */
   TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH') AS NM_mês,
   NM_GRUPO,
   SUM(VL_LOJA1) AS VL_LOJA1,
   SUM(VL_LOJA2) AS VL_LOJA2,
   SUM(VL_LOJA3) AS VL_LOJA3,
   SUM(VL_LOJA4) AS VL_LOJA4,
   SUM(VL_LOJA5) AS VL_LOJA5,
   SUM(VL_LOJA6) AS VL_LOJA6,
   SUM(VL_LOJA7) AS VL_LOJA7,
   SUM(VL_LOJA8) AS VL_LOJA8,
   SUM(VL_LOJA9) AS VL_LOJA9,
   SUM(VL_LOJA10) AS VL_LOJA10,
   SUM(VL_LOJA11) AS VL_LOJA11,
   SUM(VL_LOJA12) AS VL_LOJA12,
   SUM(VL_LOJA13) AS VL_LOJA13,
   SUM(VL_LOJA14) AS VL_LOJA14,
   SUM(VL_LOJA15) AS VL_LOJA15
   FROM (SELECT MP.DT_LIQUIDACAO, MP.CD_PAGAR,
         DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES') AS NM_GRUPO,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
         FROM TB_MOV_PAGAR MP --, TB_FORNECEDOR F
         WHERE -- F.CD_CONTABIL = MP.CD_CONTA_CONTABIL
               (MP.CD_CONTA_CONTABIL <> '25.10000' OR MP.CD_CONTA_CONTABIL IS NULL)
         AND   MP.FL_POSICAO = 'L'
         AND   MP.DT_LIQUIDACAO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2) -- AND
         AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
               -- NOT EXISTS ( SELECT CD_CONTA_CONTABIL
               --             FROM TB_TRANSPORTADOR T
               --             WHERE T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL)
         GROUP BY DT_LIQUIDACAO, MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES')) MP,
   TB_PAGAR PG
   WHERE  PG.CD_PAGAR = MP.CD_PAGAR AND
          PG.CD_PESSOA <> '34629005' AND  --Ariquemes
          (PG.CD_GRUPO_CAIXA = '15005' OR PG.CD_GRUPO_CAIXA = '56005')
   GROUP BY TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH'), NM_GRUPO;
ST_FORN_L CR_FORN_L%ROWTYPE;
-- frete em aberto
CURSOR CR_FRETE_A IS
   SELECT -- /* + INDEX( T IE_TRANSPORTADOR2 ) */
   TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH') AS NM_mês,
   NM_GRUPO,
   SUM(VL_LOJA1) AS VL_LOJA1,
   SUM(VL_LOJA2) AS VL_LOJA2,
   SUM(VL_LOJA3) AS VL_LOJA3,
   SUM(VL_LOJA4) AS VL_LOJA4,
   SUM(VL_LOJA5) AS VL_LOJA5,
   SUM(VL_LOJA6) AS VL_LOJA6,
   SUM(VL_LOJA7) AS VL_LOJA7,
   SUM(VL_LOJA8) AS VL_LOJA8,
   SUM(VL_LOJA9) AS VL_LOJA9,
   SUM(VL_LOJA10) AS VL_LOJA10,
   SUM(VL_LOJA11) AS VL_LOJA11,
   SUM(VL_LOJA12) AS VL_LOJA12,
   SUM(VL_LOJA13) AS VL_LOJA13,
   SUM(VL_LOJA14) AS VL_LOJA14,
   SUM(VL_LOJA15) AS VL_LOJA15
   FROM (SELECT MP.DT_VENCIMENTO, MP.CD_PAGAR,
         DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES') AS NM_GRUPO,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
         FROM TB_MOV_PAGAR MP, TB_PAGAR PG --, TB_TRANSPORTADOR T
         WHERE --T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
               -- PG.CD_GRUPO_CAIXA <> '25005' -- Vendas cd p/ Ariquemes
               (PG.CD_GRUPO_CAIXA = '16005' OR PG.CD_FISCAL IN ('2.3532','2.3531')) -- Fretes
         AND   PG.CD_PAGAR = MP.CD_PAGAR
         AND   MP.FL_POSICAO = 'O'
         AND   MP.DT_VENCIMENTO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
         AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
         --AND   EXISTS ( SELECT CD_CONTA_CONTABIL
         --               FROM TB_TRANSPORTADOR T
         --               WHERE T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL)
         GROUP BY DT_VENCIMENTO, MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES')) MP
   GROUP BY TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH'), NM_GRUPO;
ST_FRETE_A CR_FRETE_A%ROWTYPE;
-- frete liquidado
CURSOR CR_FRETE_L IS
   SELECT -- /* + INDEX( T IE_TRANSPORTADOR2 ) */
   TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH') AS NM_mês,
   NM_GRUPO,
   SUM(VL_LOJA1) AS VL_LOJA1,
   SUM(VL_LOJA2) AS VL_LOJA2,
   SUM(VL_LOJA3) AS VL_LOJA3,
   SUM(VL_LOJA4) AS VL_LOJA4,
   SUM(VL_LOJA5) AS VL_LOJA5,
   SUM(VL_LOJA6) AS VL_LOJA6,
   SUM(VL_LOJA7) AS VL_LOJA7,
   SUM(VL_LOJA8) AS VL_LOJA8,
   SUM(VL_LOJA9) AS VL_LOJA9,
   SUM(VL_LOJA10) AS VL_LOJA10,
   SUM(VL_LOJA11) AS VL_LOJA11,
   SUM(VL_LOJA12) AS VL_LOJA12,
   SUM(VL_LOJA13) AS VL_LOJA13,
   SUM(VL_LOJA14) AS VL_LOJA14,
   SUM(VL_LOJA15) AS VL_LOJA15
   FROM (SELECT MP.DT_LIQUIDACAO, MP.CD_PAGAR,
         DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES') AS NM_GRUPO,
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
         FROM TB_MOV_PAGAR MP, TB_PAGAR PG -- TB_TRANSPORTADOR T
         WHERE --T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
               -- PG.CD_GRUPO_CAIXA <> '25005' -- Vendas cd p/ Ariquemes
               (PG.CD_GRUPO_CAIXA = '16005' OR PG.CD_FISCAL IN ('2.3532','2.3531')) -- Fretes
         AND   PG.CD_PAGAR = MP.CD_PAGAR
         AND   MP.FL_POSICAO = 'L'
         AND   MP.DT_LIQUIDACAO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
         AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
         -- AND   EXISTS ( SELECT CD_CONTA_CONTABIL
         --                FROM TB_TRANSPORTADOR T
         --                WHERE T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL)
         GROUP BY DT_LIQUIDACAO, MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES')) MP
   GROUP BY TO_CHAR(DT_LIQUIDACAO,'MM YYYY'),TO_CHAR(DT_LIQUIDACAO,'MONTH'), NM_GRUPO;
ST_FRETE_L CR_FRETE_L%ROWTYPE;
-- Pedidos Para Revenda
CURSOR CR_PED_REV IS
  SELECT
      CD_ORDEM,
      PG.CD_GRUPO_CAIXA, NM_GRUPO_CAIXA,
      TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH') AS NM_mês,
      SUM(VL_LOJA1)  AS VL_LOJA1,
      SUM(VL_LOJA2)  AS VL_LOJA2,
      SUM(VL_LOJA3)  AS VL_LOJA3,
      SUM(VL_LOJA4)  AS VL_LOJA4,
      SUM(VL_LOJA5)  AS VL_LOJA5,
      SUM(VL_LOJA6)  AS VL_LOJA6,
      SUM(VL_LOJA7)  AS VL_LOJA7,
      SUM(VL_LOJA8)  AS VL_LOJA8,
      SUM(VL_LOJA9)  AS VL_LOJA9,
      SUM(VL_LOJA10) AS VL_LOJA10,
      SUM(VL_LOJA11) AS VL_LOJA11,
      SUM(VL_LOJA12) AS VL_LOJA12,
      SUM(VL_LOJA13) AS VL_LOJA13,
      SUM(VL_LOJA14) AS VL_LOJA14,
      SUM(VL_LOJA15) AS VL_LOJA15
  FROM(SELECT
  MP.CD_PAGAR,
  fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1') as dt_vencimento,
  SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
  SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
  SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
  SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
  SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
  SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
  SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
  SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
  SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
  SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
  SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
  SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
  SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
  SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
  SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
  FROM TB_MOV_PAGAR MP
  WHERE
   (((mp.dt_vencimento_boleta is null
  and    mp.dt_prorrogacao is null
  and    mp.dt_vencimento between  fn_conv_data(data1) and fn_conv_data(data2))
  or     mp.dt_prorrogacao between fn_conv_data(data1) and fn_conv_data(data2)
  and    mp.dt_prorrogacao > decode(mp.dt_vencimento_boleta,null,fn_conv_data('01/01/1920'), mp.dt_vencimento_boleta))
  or     mp.dt_vencimento_boleta between fn_conv_data(data1) and fn_conv_data(data2)
  and    mp.dt_vencimento_boleta > decode(mp.dt_prorrogacao,null,fn_conv_data('01/01/1920'),mp.dt_prorrogacao))
  AND   MP.DT_LIQUIDACAO IS NULL
  AND   MP.FL_POSICAO = 'P'
  AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
  GROUP BY MP.CD_PAGAR,
           fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1')
  ) MP,
  TB_GRUPO_CAIXA GC,TB_PAGAR PG
  WHERE  PG.CD_PAGAR = MP.CD_PAGAR
  AND   PG.FL_POSICAO = 'P'
  AND   GC.FL_OPERACAO NOT IN ('A','C')
  AND   GC.CD_GRUPO_CAIXA = PG.CD_GRUPO_CAIXA
  GROUP BY CD_ORDEM, PG.CD_GRUPO_CAIXA, NM_GRUPO_CAIXA,
           TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH');
ST_PED_REV CR_PED_REV%ROWTYPE;
SESSIONID NUMBER := USERENV('sessionid');
vMes   Varchar2(30);
LOJA1  NUMBER := 0;
LOJA2  NUMBER := 0;
LOJA3  NUMBER := 0;
LOJA4  NUMBER := 0;
LOJA5  NUMBER := 0;
LOJA6  NUMBER := 0;
LOJA7  NUMBER := 0;
LOJA8  NUMBER := 0;
LOJA9  NUMBER := 0;
LOJA10 NUMBER := 0;
LOJA11 NUMBER := 0;
LOJA12 NUMBER := 0;
LOJA13 NUMBER := 0;
LOJA14  NUMBER := 0;
LOJA15  NUMBER := 0;
BEGIN
  ID_SESSION := SESSIONID;
  IF FL_LIQUIDADO_ABERTO = 'A' THEN
     -- Fretes: CD_CONTA_CONTABIL = '4.30015'
     SELECT
        SUM(VL_LOJA1) AS VL_LOJA1,
        SUM(VL_LOJA2) AS VL_LOJA2,
        SUM(VL_LOJA3) AS VL_LOJA3,
        SUM(VL_LOJA4) AS VL_LOJA4,
        SUM(VL_LOJA5) AS VL_LOJA5,
        SUM(VL_LOJA6) AS VL_LOJA6,
        SUM(VL_LOJA7) AS VL_LOJA7,
        SUM(VL_LOJA8) AS VL_LOJA8,
        SUM(VL_LOJA9) AS VL_LOJA9,
        SUM(VL_LOJA10) AS VL_LOJA10,
        SUM(VL_LOJA11) AS VL_LOJA11,
        SUM(VL_LOJA12) AS VL_LOJA12,
        SUM(VL_LOJA13) AS VL_LOJA13,
        SUM(VL_LOJA14) AS VL_LOJA14,
        SUM(VL_LOJA15) AS VL_LOJA15
     INTO
        LOJA1, LOJA2, LOJA3, LOJA4, LOJA5, LOJA6, LOJA7, LOJA8, LOJA9, LOJA10, LOJA11, LOJA12, LOJA13, LOJA14, LOJA15
     FROM (
        SELECT MP.CD_PAGAR,
        SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
        SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
        SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
        SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
        SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
        SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
        SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
        SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
        SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
        SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
        SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
        SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
        SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
        SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
        SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
        FROM TB_MOV_PAGAR MP
        WHERE MP.CD_CONTA_CONTABIL = '4.30015'
        AND   MP.FL_POSICAO = 'O'
        AND   MP.DT_VENCIMENTO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
        AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
        GROUP BY MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES')) MP;
     --
     FOR ST_GERAL_A IN CR_GERAL_A LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_GERAL_A.NM_mês, ST_GERAL_A.NM_SUB_GRUPO_CONTABIL, ST_GERAL_A.VL_LOJA1, ST_GERAL_A.VL_LOJA2, ST_GERAL_A.VL_LOJA3,
           ST_GERAL_A.VL_LOJA4, ST_GERAL_A.VL_LOJA5, ST_GERAL_A.VL_LOJA6, ST_GERAL_A.VL_LOJA7,
           ST_GERAL_A.VL_LOJA8, ST_GERAL_A.VL_LOJA9, ST_GERAL_A.VL_LOJA10, ST_GERAL_A.VL_LOJA11,
           ST_GERAL_A.VL_LOJA12, ST_GERAL_A.VL_LOJA13, ST_GERAL_A.VL_LOJA14, ST_GERAL_A.VL_LOJA15);
        commit;
     END LOOP;
     FOR ST_FORN_A IN CR_FORN_A LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_FORN_A.NM_mês, ST_FORN_A.NM_GRUPO, ST_FORN_A.VL_LOJA1, ST_FORN_A.VL_LOJA2, ST_FORN_A.VL_LOJA3,
           ST_FORN_A.VL_LOJA4, ST_FORN_A.VL_LOJA5, ST_FORN_A.VL_LOJA6, ST_FORN_A.VL_LOJA7,
           ST_FORN_A.VL_LOJA8, ST_FORN_A.VL_LOJA9, ST_FORN_A.VL_LOJA10, ST_FORN_A.VL_LOJA11,
           ST_FORN_A.VL_LOJA12, ST_FORN_A.VL_LOJA13, ST_FORN_A.VL_LOJA14, ST_FORN_A.VL_LOJA15);
        commit;
     END LOOP;
     FOR ST_FRETE_A IN CR_FRETE_A LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_FRETE_A.NM_mês, ST_FRETE_A.NM_GRUPO, ST_FRETE_A.VL_LOJA1+LOJA1, ST_FRETE_A.VL_LOJA2+LOJA2, ST_FRETE_A.VL_LOJA3+LOJA3,
           ST_FRETE_A.VL_LOJA4+LOJA4, ST_FRETE_A.VL_LOJA5+LOJA5, ST_FRETE_A.VL_LOJA6+LOJA6, ST_FRETE_A.VL_LOJA7+LOJA7,
           ST_FRETE_A.VL_LOJA8+LOJA8, ST_FRETE_A.VL_LOJA9+LOJA9, ST_FRETE_A.VL_LOJA10+LOJA10, ST_FRETE_A.VL_LOJA11+LOJA11,
           ST_FRETE_A.VL_LOJA12+LOJA12, ST_FRETE_A.VL_LOJA13+LOJA13, ST_FRETE_A.VL_LOJA14+LOJA14, ST_FRETE_A.VL_LOJA15+LOJA15);
        commit;
     END LOOP;
     FOR ST_PED_REV IN CR_PED_REV LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_PED_REV.NM_mês, ST_PED_REV.NM_GRUPO_CAIXA, ST_PED_REV.VL_LOJA1, ST_PED_REV.VL_LOJA2, ST_PED_REV.VL_LOJA3,
           ST_PED_REV.VL_LOJA4, ST_PED_REV.VL_LOJA5, ST_PED_REV.VL_LOJA6, ST_PED_REV.VL_LOJA7,
           ST_PED_REV.VL_LOJA8, ST_PED_REV.VL_LOJA9, ST_PED_REV.VL_LOJA10, ST_PED_REV.VL_LOJA11,
           ST_PED_REV.VL_LOJA12, ST_PED_REV.VL_LOJA13, ST_PED_REV.VL_LOJA14, ST_PED_REV.VL_LOJA15);
        commit;
     END LOOP;
  ELSE -- FL_LIQUIDADO_ABERTO = 'L'
     BEGIN
        SELECT
           TO_CHAR(DT_LIQUIDACAO,'MONTH') AS NM_mês,
           SUM(VL_LOJA1) AS VL_LOJA1,
           SUM(VL_LOJA2) AS VL_LOJA2,
           SUM(VL_LOJA3) AS VL_LOJA3,
           SUM(VL_LOJA4) AS VL_LOJA4,
           SUM(VL_LOJA5) AS VL_LOJA5,
           SUM(VL_LOJA6) AS VL_LOJA6,
           SUM(VL_LOJA7) AS VL_LOJA7,
           SUM(VL_LOJA8) AS VL_LOJA8,
           SUM(VL_LOJA9) AS VL_LOJA9,
           SUM(VL_LOJA10) AS VL_LOJA10,
           SUM(VL_LOJA11) AS VL_LOJA11,
           SUM(VL_LOJA12) AS VL_LOJA12,
           SUM(VL_LOJA13) AS VL_LOJA13,
           SUM(VL_LOJA14) AS VL_LOJA14,
           SUM(VL_LOJA15) AS VL_LOJA15
        INTO vMes, LOJA1, LOJA2, LOJA3, LOJA4, LOJA5, LOJA6, LOJA7, LOJA8, LOJA9, LOJA10, LOJA11, LOJA12, LOJA13, LOJA14, LOJA15
        FROM (
           SELECT MP.DT_LIQUIDACAO, MP.CD_PAGAR,
              SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1,
              SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2,
              SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3,
              SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4,
              SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5,
              SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6,
              SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7,
              SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8,
              SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9,
              SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10,
              SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11,
              SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12,
              SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13,
              SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14,
              SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15
           FROM TB_MOV_PAGAR MP
           WHERE MP.CD_CONTA_CONTABIL = '4.30015'
              AND   MP.FL_POSICAO = 'L'
              AND   MP.DT_LIQUIDACAO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2)
              AND   ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL )
        GROUP BY MP.DT_LIQUIDACAO, MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FRETES','FRETES')) MP
        GROUP BY TO_CHAR(DT_LIQUIDACAO,'MONTH');
         
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, vMes, 'FRETES', LOJA1, LOJA2, LOJA3,
           LOJA4, LOJA5, LOJA6, LOJA7,
           LOJA8, LOJA9, LOJA10, LOJA11,
           LOJA12, LOJA13, LOJA14, LOJA15);
        commit;

        EXCEPTION WHEN OTHERS THEN NULL;
     END;   

     FOR ST_GERAL_L IN CR_GERAL_L LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_GERAL_L.NM_mês, ST_GERAL_L.NM_SUB_GRUPO_CONTABIL, ST_GERAL_L.VL_LOJA1, ST_GERAL_L.VL_LOJA2, ST_GERAL_L.VL_LOJA3,
           ST_GERAL_L.VL_LOJA4, ST_GERAL_L.VL_LOJA5, ST_GERAL_L.VL_LOJA6, ST_GERAL_L.VL_LOJA7,
           ST_GERAL_L.VL_LOJA8, ST_GERAL_L.VL_LOJA9, ST_GERAL_L.VL_LOJA10, ST_GERAL_L.VL_LOJA11,
           ST_GERAL_L.VL_LOJA12, ST_GERAL_L.VL_LOJA13, ST_GERAL_L.VL_LOJA14, ST_GERAL_L.VL_LOJA15);
        commit;
     END LOOP;
     FOR ST_FORN_L IN CR_FORN_L LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_FORN_L.NM_mês, ST_FORN_L.NM_GRUPO, ST_FORN_L.VL_LOJA1, ST_FORN_L.VL_LOJA2, ST_FORN_L.VL_LOJA3,
           ST_FORN_L.VL_LOJA4, ST_FORN_L.VL_LOJA5, ST_FORN_L.VL_LOJA6, ST_FORN_L.VL_LOJA7,
           ST_FORN_L.VL_LOJA8, ST_FORN_L.VL_LOJA9, ST_FORN_L.VL_LOJA10, ST_FORN_L.VL_LOJA11,
           ST_FORN_L.VL_LOJA12, ST_FORN_L.VL_LOJA13, ST_FORN_L.VL_LOJA14, ST_FORN_L.VL_LOJA15);
        commit;
     END LOOP;
     /*
     FOR ST_FRETE_L IN CR_FRETE_L LOOP
        insert into TT_DESEMBOLSO_GRUPO (
           CD_SESSION, NM_mês, NM_GRUPO, VL_LOJA1, VL_LOJA2, VL_LOJA3, VL_LOJA4, VL_LOJA5, VL_LOJA6,
           VL_LOJA7, VL_LOJA8, VL_LOJA9, VL_LOJA10, VL_LOJA11, VL_LOJA12, VL_LOJA13, VL_LOJA14, VL_LOJA15)
        values (
           SESSIONID, ST_FRETE_L.NM_mês, ST_FRETE_L.NM_GRUPO, ST_FRETE_L.VL_LOJA1+LOJA1, ST_FRETE_L.VL_LOJA2+LOJA2, ST_FRETE_L.VL_LOJA3+LOJA3,
           ST_FRETE_L.VL_LOJA4+LOJA4, ST_FRETE_L.VL_LOJA5+LOJA5, ST_FRETE_L.VL_LOJA6+LOJA6, ST_FRETE_L.VL_LOJA7+LOJA7,
           ST_FRETE_L.VL_LOJA8+LOJA8, ST_FRETE_L.VL_LOJA9+LOJA9, ST_FRETE_L.VL_LOJA10+LOJA10, ST_FRETE_L.VL_LOJA11+LOJA11,
           ST_FRETE_L.VL_LOJA12+LOJA12, ST_FRETE_L.VL_LOJA13+LOJA13, ST_FRETE_L.VL_LOJA14+LOJA14, ST_FRETE_L.VL_LOJA15+LOJA15);
        commit;
     END LOOP;
     */
  END IF;
END;
Voltar ao topo
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Contato dr_gori
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Fica difícil alguém te ajudar, pois está muito amplo!
Tenta definir exatamente qual é o problema, qual select, o que você está tentando fazer.
Voltar ao topo
wilke
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Sex, 23 Nov 2007 9:26 am
Localização: porto velho -ro

amigo e essa select ,

Selecionar tudo

-- fornecedor em aberto 
CURSOR CR_FORN_A IS 
   SELECT -- /* + INDEX( F IE_FORNECEDOR2 ) */ 
   TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH') AS NM_mês, 
   NM_GRUPO, 
   SUM(VL_LOJA1) AS VL_LOJA1, 
   SUM(VL_LOJA2) AS VL_LOJA2, 
   SUM(VL_LOJA3) AS VL_LOJA3, 
   SUM(VL_LOJA4) AS VL_LOJA4, 
   SUM(VL_LOJA5) AS VL_LOJA5, 
   SUM(VL_LOJA6) AS VL_LOJA6, 
   SUM(VL_LOJA7) AS VL_LOJA7, 
   SUM(VL_LOJA8) AS VL_LOJA8, 
   SUM(VL_LOJA9) AS VL_LOJA9, 
   SUM(VL_LOJA10) AS VL_LOJA10, 
   SUM(VL_LOJA11) AS VL_LOJA11, 
   SUM(VL_LOJA12) AS VL_LOJA12, 
   SUM(VL_LOJA13) AS VL_LOJA13, 
   SUM(VL_LOJA14) AS VL_LOJA14, 
   SUM(VL_LOJA15) AS VL_LOJA15 
   FROM (SELECT MP.CD_PAGAR, 
                fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1') as dt_vencimento, 
         DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES') AS NM_GRUPO, 
         SUM(DECODE(MP.CD_LOJA,'020',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '005',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA1, 
         SUM(DECODE(MP.CD_LOJA,'023',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '004',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA2, 
         SUM(DECODE(MP.CD_LOJA,'001',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA3, 
         SUM(DECODE(MP.CD_LOJA,'022',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '003',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA4, 
         SUM(DECODE(MP.CD_LOJA,'026',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '009',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA5, 
         SUM(DECODE(MP.CD_LOJA,'027',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '010',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA6, 
         SUM(DECODE(MP.CD_LOJA,'031',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '006',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA7, 
         SUM(DECODE(MP.CD_LOJA,'028',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '011',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA8, 
         SUM(DECODE(MP.CD_LOJA,'025',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00), '008',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA9, 
         SUM(DECODE(MP.CD_LOJA,'030',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA10, 
         SUM(DECODE(MP.CD_LOJA,'029',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA11, 
         SUM(DECODE(MP.CD_LOJA,'032',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA12, 
         SUM(DECODE(MP.CD_LOJA,'033',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA13, 
         SUM(DECODE(MP.CD_LOJA,'034',NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00),0)) AS VL_LOJA14, 
         SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_LOJA15 
         FROM TB_MOV_PAGAR MP --TB_FORNECEDOR F 
         WHERE -- F.CD_CONTABIL = MP.CD_CONTA_CONTABIL 
              (MP.CD_CONTA_CONTABIL <> '25.10000' OR MP.CD_CONTA_CONTABIL IS NULL) AND 
               MP.FL_POSICAO = 'O' 
         AND -- MP.DT_VENCIMENTO BETWEEN FN_CONV_DATA(DATA1) AND FN_CONV_DATA(DATA2) AND 
             (((mp.dt_vencimento_boleta is null and mp.dt_prorrogacao is null and mp.dt_vencimento between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2)) 
              or mp.dt_prorrogacao between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2) 
              and mp.dt_prorrogacao > decode(mp.dt_vencimento_boleta,null,FN_CONV_DATA('01/01/1920'),mp.dt_vencimento_boleta)) 
              or mp.dt_vencimento_boleta between FN_CONV_DATA(DATA1) and FN_CONV_DATA(DATA2) 
              and mp.dt_vencimento_boleta > decode(mp.dt_prorrogacao,null,FN_CONV_DATA('01/01/1920'),mp.dt_prorrogacao)) 
              AND MP.DT_LIQUIDACAO IS NULL 
              AND ((MP.FL_POSICAO <> 'C' AND MP.FL_POSICAO <> 'L') OR MP.FL_POSICAO IS NULL) -- AND 
              AND ( MP.DS_OBS NOT LIKE 'TRANSFERENCIA INTERNA%' OR DS_OBS IS NULL ) 
              --NOT EXISTS ( SELECT CD_CONTA_CONTABIL 
              --             FROM TB_TRANSPORTADOR T 
              --             WHERE T.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL) 
         GROUP BY fn_venc_data(TO_DATE(data1,'DD MM YYYY'),TO_DATE(data2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'1'), 
                  MP.CD_PAGAR, DECODE(MP.CD_LOJA,MP.CD_LOJA,'FORNECEDORES','FORNECEDORES')) MP, 
   TB_PAGAR PG 
   WHERE  PG.CD_PAGAR = MP.CD_PAGAR AND 
          (PG.CD_GRUPO_CAIXA = '15005' OR PG.CD_GRUPO_CAIXA = '56005') AND 
           PG.CD_PESSOA <> '34629005' AND --Ariquemes 
            
           ((PG.FL_POSICAO <> 'T') OR PG.FL_POSICAO IS NULL) 
   GROUP BY TO_CHAR(DT_VENCIMENTO,'MM YYYY'),TO_CHAR(DT_VENCIMENTO,'MONTH'), NM_GRUPO; 
ST_FORN_A CR_FORN_A%ROWTYPE;
Voltar ao topo
wilke
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Sex, 23 Nov 2007 9:26 am
Localização: porto velho -ro

amigo e que na verdade eu não conhece nada de procedure , eu to alterando um relatorio e vi que o resultado do relatorio vem de uma select , que pucha dessa procedure. aproveitando ,se você tiver alguma coisa que explique bem sobre procedure, eu te agradeço.
Voltar ao topo
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

E ai Wilke, beleza??

cara, pra ajudar a montar o select com esse filtro agente precisa saber qual o criterio que você precisa ignorar..

[]'s
Voltar ao topo
wilke
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Sex, 23 Nov 2007 9:26 am
Localização: porto velho -ro

eu tenho um relatorio chamado fluxo de desembolso em aberto, e nesse relatorio sai um valor que não condiz .numa determinda loja.
Voltar ao topo
wilke
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Sex, 23 Nov 2007 9:26 am
Localização: porto velho -ro

resolvido amigo obrigado pela atenção :wink:
Voltar ao topo
Responder

Voltar para “SQL*Plus”

  • Informação

  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes