CREATE OR REPLACE PROCEDURE MOVIMENTACA_BANCARIA(
   P_CDEMPRESA    IN VARCHAR2,
   P_CDREDUCONT   IN VARCHAR2,
   P_SALDOINICIAL IN VARCHAR2,
   P_DTPERIODOINI IN BAIXARECEBIDA.DTBAIXAREC%TYPE,
   P_DTPERIODOFIN IN BAIXAPAGA.DTBAIXAPAG%TYPE,
   P_RESULT OUT VARCHAR2)IS
   V_NRSEQITMOV  INTEGER;
   V_VRSALDOFIN FLOAT;
   V_VRSALDOINI FLOAT;
   V_NRSEQMOVBANC VARCHAR2(20);
   V_CDREDUCONT VARCHAR2(04);
   V_COMANDO    VARCHAR2(500);   
BEGIN
   NOVO_CODIGO_NT('NRSEQMOVBANC', V_NRSEQMOVBANC);
   V_COMANDO := 'TRUNCATE TABLE MOVBANC_TEMP';
   EXECUTE IMMEDIATE V_COMANDO;
   V_NRSEQITMOV := 0;
   V_CDREDUCONT := 'TTTT';
   FOR C_SALDANT IN (SELECT TIPO, TPLANCAMENTO, DESCRICAO, DEBITO,
                            CREDITO, SALDOTELA, DEBCRED, CDREDUCONT,
                            NRMOVBANC, NRCHEQUE, TO_CHAR(DTMOVBANC,'DD/MM/YYYY') DTMOVBANC,
                            IDMOVBANC, NRDOCMBANC, DSTIPOMOV, DSHISMBANC, VRMOVBANC, NUMEROBAIXA,
                            IDORIGMBANC, VRSDOCCORR, VRSDOIMPLA, SALDO, CDBANCO, CDEMPRESA,
                            NMEMPRESA, NRORG, CDAGENCIA, CDCONTCORR, DSCONTCORR,
                            CDPICTCONT, NMBANCO, DSMOVIMENTO, DSDEBICRED,VRLIMITCREDCCOR,
                            SDOANT, DTPERIODOINI, DTPERIODOFIN, P_SALDOINICIAL
                       FROM
                       (SELECT TIPO, TPLANCAMENTO, DECODE(DESCRICAO,'TROCAR',BB.NMRAZAO,DESCRICAO) AS DESCRICAO,
                               DEBITO, CREDITO,
                               ABS(DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR',SALDOTELA,SUM(CREDITO - DEBITO) OVER( PARTITION BY CDEMPRESA,CDBANCO,CDREDUCONT ORDER BY CDEMPRESA,CDBANCO,CDREDUCONT,TIPO,TO_DATE(DTMOVBANC),NRCHEQUE,NRMOVBANC ) + DECODE(P_SALDOINICIAL,1,VRSDOCCORR,2,VRLIMITCREDCCOR + VRSDOCCORR,0) ) ) AS SALDOTELA,
                               CASE
                                 WHEN DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR',SALDOTELA,SUM(CREDITO - DEBITO) OVER( PARTITION BY CDEMPRESA,CDBANCO,CDREDUCONT ORDER BY CDEMPRESA,CDBANCO,CDREDUCONT,TIPO,DTMOVBANC,NRCHEQUE,NRMOVBANC ) + DECODE(P_SALDOINICIAL,1,VRSDOCCORR,2,VRLIMITCREDCCOR + VRSDOCCORR,0) ) < 0
                                 THEN 'DB'
                                 ELSE 'CR'
                               END AS DEBCRED,
                               CDREDUCONT, NRMOVBANC, NRCHEQUE, DTMOVBANC, IDMOVBANC,NRDOCMBANC,
                               DSTIPOMOV, DSHISMBANC, VRMOVBANC, NUMEROBAIXA, IDORIGMBANC,
                               VRSDOCCORR, VRSDOIMPLA, SALDO, CDBANCO, CDEMPRESA,
                               NMEMPRESA, NRORG, CDAGENCIA, CDCONTCORR, DSCONTCORR,
                               CDPICTCONT, NMBANCO, DSMOVIMENTO, DSDEBICRED,
                               VRLIMITCREDCCOR, SDOANT, DTPERIODOINI, DTPERIODOFIN, P_SALDOINICIAL
                          FROM
                         (SELECT TIPO,
                                 DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR','SALDO ANTERIOR','TECNISA_IMPL_SALDO','IMPLANTACAO DE SALDO',DECODE(DSTIPOMOV ,'_BXPAGAR','BX. DE CONTAS PAGAS','_BXRECEBER','BX. DE CONTAS RECEBIDAS','_MOVICRED',DSDEBICRED,DECODE(DSTIPOMOV,'_MOVIDEBI' ,DSDEBICRED,DECODE(DSTIPOMOV,'_TRANSF','TRANSF. ENTRE CONTAS',' ') ) ) ) AS TPLANCAMENTO,
                                 DECODE(DSTIPOMOV,'TECNISA_DSTIPOMOV',' ',DECODE(DSTIPOMOV,'_BXPAGAR','TROCAR','_BXRECEBER','TROCAR','_MOVICRED',DSMOVIMENTO ,'_MOVIDEBI',DSMOVIMENTO,'_TRANSF',DSHISMBANC,' ') )                                                                                                                                               AS DESCRICAO,
                                 DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR',0,DECODE(TIPO,'A',VRMOVBANC,DECODE(DSHISMBANC,'TECNISA_IMPL_SALDO',0,DECODE (IDMOVBANC,'D',VRMOVBANC,'0') ) ) )                                                                                                                                                                     AS DEBITO,
                                 DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR',0,DECODE(TIPO,'A',VRMOVBANC,DECODE(DSHISMBANC,'TECNISA_IMPL_SALDO',0,DECODE (IDMOVBANC,'D','0',DECODE(VRMOVBANC,0,0,VRMOVBANC) ) ) ) )                                                                                                                                              AS CREDITO,
                                 DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR',DECODE(P_SALDOINICIAL,1,VRSDOCCORR,2,NVL(VRLIMITCREDCCOR,0) + VRSDOCCORR, 0),DECODE(DSHISMBANC,'TECNISA_IMPL_SALDO',VRSDOIMPLA,0) )                                                                                                                                                 AS SALDOTELA,
                                 CDREDUCONT, NRMOVBANC, DECODE(NRCHEQUE,'TECNISA_NRCHEQUE','',NRCHEQUE) AS NRCHEQUE,
                                 DECODE(DSHISMBANC,'TECNISA_SALDO_ANTERIOR', (TO_DATE(P_DTPERIODOINI) - 1),DTMOVBANC) AS DTMOVBANC,
                                 IDMOVBANC, DECODE(NRDOCMBANC,'TECNISA_NRDOCU','',NRDOCMBANC) AS NRDOCMBANC,
                                 DSTIPOMOV, DSHISMBANC, VRMOVBANC, NUMEROBAIXA, IDORIGMBANC, VRSDOCCORR, VRSDOIMPLA,
                                 SALDO, CDBANCO, CDEMPRESA, NMEMPRESA, NRORG, CDAGENCIA, CDCONTCORR, DSCONTCORR,
                                 CDPICTCONT, NMBANCO, DSMOVIMENTO, DSDEBICRED, NVL(VRLIMITCREDCCOR,0) AS VRLIMITCREDCCOR,
                                 SDOANT, RPAD(P_DTPERIODOINI,10,' ') AS DTPERIODOINI, RPAD(P_DTPERIODOFIN,10,' ') AS DTPERIODOFIN,
                                 P_SALDOINICIAL,(SELECT (CASE WHEN COUNT(*) > 1
                                                              THEN 'VARIOS BOLETOS'
                                                              ELSE MAX(NMRAZAO)
                                                          END ) AS NMRAZAO
                                                   FROM (SELECT DISTINCT CL.NMRAZSOCCLIE AS NMRAZAO,
                                                                BE.NRMOVBANC,
                                                                BE.CDREDUCONT,
                                                                '_BXRECEBER' AS DSTIPOMOV
                                                           FROM BOLETRECEBER BR,
                                                                BAIXARECEBIDA BE,
                                                                CLIENTE CL
                                                          WHERE ( BR.CDCLIENTE  = CL.CDCLIENTE )
                                                            AND ( BR.CDFILIBAIREC = BE.CDFILIAL )
                                                            AND ( BR.NRBAIXAREC   = BE.NRBAIXAREC )
                                                          UNION
                                                         SELECT DISTINCT FO.NMRAZSOCFORN AS NMRAZAO,
                                                                BA.NRMOVBANC,
                                                                BA.CDREDUCONT,
                                                                '_BXPAGAR' AS DSTIPOMOV
                                                           FROM BOLETPAGAR BP,
                                                                BAIXAPAGA BA,
                                                                FORNECEDOR FO
                                                          WHERE ( BP.CDFORNECED = FO.CDFORNECED )
                                                            AND ( BP.CDFILIBAIPAG = BA.CDFILIAL )
                                                            AND ( BP.NRBAIXAPAG   = BA.NRBAIXAPAG )) BA
                                                  WHERE BA.NRMOVBANC = AA.NRMOVBANC
                                                    AND BA.CDREDUCONT  = AA.CDREDUCONT
                                                    AND BA.DSTIPOMOV   = AA.DSTIPOMOV) AS NMRAZAO
                            FROM (SELECT 'A'                                           AS TIPO,
                                         CC.CDREDUCONT                                       AS CDREDUCONT,
                                         'TECNISA_'                                          AS NRMOVBANC,
                                         'TECNISA_NRCHEQUE'                                  AS NRCHEQUE,
                                         TO_DATE(P_DTPERIODOINI) - 1                          AS DTMOVBANC,
                                         'TECNISA_'                                          AS IDMOVBANC,
                                         'TECNISA_NRDOCU'                                    AS NRDOCMBANC,
                                         'TECNISA_DSTIPOMOV'                                 AS DSTIPOMOV,
                                         'TECNISA_SALDO_ANTERIOR'                            AS DSHISMBANC,
                                         NVL(SALDO_CONTACORR(SD.CDREDUCONT,P_DTPERIODOINI),0) AS VRMOVBANC,
                                         NULL                                                AS NUMEROBAIXA,
                                         NULL                                                AS IDORIGMBANC,
                                         NVL(SALDO_CONTACORR(SD.CDREDUCONT,P_DTPERIODOINI),0) AS VRSDOCCORR,
                                         0                                                   AS VRSDOIMPLA,
                                         NVL(SALDO_CONTACORR(SD.CDREDUCONT,P_DTPERIODOINI),0) AS SALDO,
                                         CC.CDBANCO,
                                         E.CDEMPRESA,
                                         E.NMEMPRESA,
                                         E.NRORG,
                                         CC.CDAGENCIA,
                                         CC.CDCONTCORR,
                                         CC.DSCONTCORR,
                                         CC.VRLIMITCREDCCOR,
                                         B.CDPICTCONT,
                                         B.NMBANCO,
                                         ' '             AS DSMOVIMENTO,
                                         ' '             AS DSDEBICRED,
                                         'SDOANT'        AS SDOANT,
                                         P_SALDOINICIAL AS P_SALDOINICIAL
                                    FROM CONTCORR CC,
                                         EMPRESA E,
                                         BANCO B,
                                         (SELECT CDREDUCONT
                                            FROM SDOCCORR
                                           WHERE ( INSTR(P_CDREDUCONT,CDREDUCONT) <> 0
                                              OR P_CDREDUCONT = 'T' )
                                           GROUP BY CDREDUCONT) SD
                                   WHERE (( INSTR(P_CDEMPRESA,CC.CDEMPRESA) > 0 )
                                      OR P_CDEMPRESA = 'T' )
                                     AND (SD.CDREDUCONT = CC.CDREDUCONT )
                                     AND (E.CDEMPRESA   = CC.CDEMPRESA )
                                     AND (B.CDBANCO     = CC.CDBANCO )
                                   UNION ALL
                                  SELECT 'M' AS TIPO,
                                          M.CDREDUCONT,
                                          M.NRMOVBANC,
                                          DECODE(M.IDORIGMBANC,'T',IT.NRCHEQUE,'P',BP.NRCHEQUE,'TECNISA_NRCHEQUE') AS NRCHEQUE,
                                          M.DTMOVBANC,
                                          M.IDMOVBANC,
                                          M.NRDOCMBANC,
                                          DECODE(M.IDORIGMBANC,'C','_MOVICRED','D','_MOVIDEBI','T','_TRANSF','R','_BXRECEBER','P','_BXPAGAR') AS DSTIPOMOV ,
                                          M.DSHISMBANC,
                                          M.VRMOVBANC,
                                          DECODE(M.IDORIGMBANC,'R',BR.NRBAIXAREC,'P',BP.NRBAIXAPAG) AS NUMEROBAIXA,
                                          M.IDORIGMBANC,
                                          NVL(SALDO_CONTACORR(M.CDREDUCONT,M.DTMOVBANC),0)     AS VRSDOCCORR,
                                          NVL(SALDO_CONTACORR(M.CDREDUCONT,M.DTMOVBANC,'I'),0) AS VRSDOIMPLA,
                                          0                                                    AS SALDO,
                                          B.CDBANCO,
                                          E.CDEMPRESA,
                                          E.NMEMPRESA,
                                          E.NRORG,
                                          C.CDAGENCIA,
                                          C.CDCONTCORR,
                                          C.DSCONTCORR,
                                          C.VRLIMITCREDCCOR,
                                          B.CDPICTCONT,
                                          B.NMBANCO,
                                          DECODE(M.IDORIGMBANC,'C',MC.DSMOVICRED,'D',MD.DSMOVIDEBI,' ') AS DSMOVIMENTO,
                                          DECODE(M.IDORIGMBANC,'C',
                                          (SELECT TC.DSTPCREDBANC
                                             FROM TPCREDBANC TC
                                            WHERE (MC.CDTPCREDBANC = TC.CDTPCREDBANC)),'D',
                                          (SELECT TD.DSTPDEBIBANC
                                             FROM TPDEBIBANC TD
                                            WHERE MD.CDTPDEBIBANC = TD.CDTPDEBIBANC),' ')  AS DSDEBICRED,
                                           'SDOANT'        AS SDOANT,
                                           P_SALDOINICIAL AS P_SALDOINICIAL
                                      FROM MOVBANCARIO M,
                                           CONTCORR C,
                                           BANCO B,
                                           EMPRESA E,
                                           MOVCREDBANC MC,
                                           MOVDEBBANC MD,
                                           ITTRANSFBANC IT,
                                           BAIXARECEBIDA BR,
                                           BAIXAPAGA BP
                           WHERE ((INSTR(P_CDREDUCONT,M.CDREDUCONT) > 0 )
                              OR P_CDREDUCONT = 'T' )
                             AND ((INSTR(P_CDEMPRESA,C.CDEMPRESA) > 0 )
                              OR P_CDEMPRESA = 'T' )
                             AND ( M.DTMOVBANC BETWEEN P_DTPERIODOINI AND P_DTPERIODOFIN )
                             AND ( M.CDREDUCONT = C.CDREDUCONT )
                             AND ( C.CDBANCO    = B.CDBANCO )
                             AND ( C.CDEMPRESA  = E.CDEMPRESA )
                             AND ( M.CDREDUCONT = MC.CDREDUCONT (+) )
                             AND ( M.NRMOVBANC  = MC.NRMOVBANC (+) )
                             AND ( M.CDREDUCONT = MD.CDREDUCONT (+) )
                             AND ( M.NRMOVBANC  = MD.NRMOVBANC (+) )
                             AND ( M.CDREDUCONT = IT.CDREDUCONT (+) )
                             AND ( M.NRMOVBANC  = IT.NRMOVBANC (+) )
                             AND ( M.CDREDUCONT = BR.CDREDUCONT (+) )
                             AND ( M.NRMOVBANC  = BR.NRMOVBANC (+) )
                             AND ( M.CDREDUCONT = BP.CDREDUCONT (+) )
                             AND ( M.NRMOVBANC  = BP.NRMOVBANC (+) )
                           UNION ALL
                          SELECT 'I' AS TIPO,
                                  S.CDREDUCONT           AS CDREDUCONT,
                                  'TECNISA_'             AS NRMOVBANC,
                                  'TECNISA_NRCHEQUE'     AS NRCHEQUE,
                                  DTSDOCCORR             AS DTMOVBANC,
                                  'X'                    AS IDMOVBANC,
                                  'TECNISA_NRDOCU'       AS NRDOCMBANC,
                                  'TECNISA_DSTIPOMOV'    AS DSTIPOMOV,
                                  'TECNISA_IMPL_SALDO'   AS DSHISMBANC,
                                  VRSDOIMPLA             AS VRMOVBANC,
                                  NULL                   AS NUMEROBAIXA,
                                  NULL                   AS IDORIGMBANC,
                                  0                      AS VRSDOCCORR,
                                  VRSDOIMPLA             AS VRSDOIMPLA,
                                  0                      AS SALDO,
                                  B.CDBANCO,
                                  E.CDEMPRESA,
                                  E.NMEMPRESA,
                                  E.NRORG,
                                  C.CDAGENCIA,
                                  C.CDCONTCORR,
                                  C.DSCONTCORR,
                                  C.VRLIMITCREDCCOR,
                                  B.CDPICTCONT,
                                  B.NMBANCO,
                                  ' '             AS DSMOVIMENTO,
                                  ' '             AS DSDEBICRED,
                                  'XT'            AS SDOANT,
                                  P_SALDOINICIAL AS P_SALDOINICIAL
                            FROM SDOCCORR S,
                                 EMPRESA E,
                                 CONTCORR C,
                                 BANCO B
                           WHERE (( INSTR(P_CDREDUCONT,C.CDREDUCONT)     > 0 )
                              OR P_CDREDUCONT = 'T' )
                             AND ( ( INSTR(P_CDEMPRESA,E.CDEMPRESA) > 0 )
                              OR P_CDEMPRESA                         = 'T' )
                             AND ( C.CDEMPRESA   = E.CDEMPRESA )
                             AND ( S.CDREDUCONT  = C.CDREDUCONT )
                             AND ( B.CDBANCO     = C.CDBANCO )
                             AND ( DTSDOCCORR BETWEEN P_DTPERIODOINI AND P_DTPERIODOFIN )
                             AND ( NOT VRSDOIMPLA IS NULL )) AA
                         ORDER BY CDEMPRESA,
                                  CDBANCO,
                                  CDREDUCONT,
                                  DTMOVBANC,
                                  TIPO,
                                  NRCHEQUE) BB
                  ORDER BY CDEMPRESA, CDBANCO, CDREDUCONT,  DTMOVBANC, TIPO, NRCHEQUE, NRMOVBANC, VRMOVBANC DESC) RELATORIO)LOOP
          V_NRSEQITMOV := V_NRSEQITMOV + 1;
          INSERT
            INTO MOVBANC_TEMP
               (NRSEQMOVBANC, NRSEQITMOV, TIPO, TPLANCAMENTO, DESCRICAO,
                 DEBITO, CREDITO, SALDOTELA, CDREDUCONT, NRMOVBANC,
                NRCHEQUE, DTMOVBANC, IDMOVBANC, NRDOCMBANC, DSTIPOMOV,
                DSHISMBANC, VRMOVBANC, NUMEROBAIXA, IDORIGMBANC, VRSDOCCORR,
                VRSDOIMPLA, SALDO, CDBANCO, CDEMPRESA,  NMEMPRESA,  CDAGENCIA,
                CDCONTCORR, DSCONTCORR, CDPICTCONT, NMBANCO, DSMOVIMENTO,
                DSDEBICRED, VRLIMITCREDCCOR, SDOANT,
                DTPERIODOINI, DTPERIODOFIN, SALDOINICIAL)
           VALUES
               (TO_NUMBER(V_NRSEQMOVBANC), V_NRSEQITMOV,C_SALDANT.TIPO, C_SALDANT.TPLANCAMENTO, C_SALDANT.DESCRICAO,
                C_SALDANT.DEBITO, C_SALDANT.CREDITO, C_SALDANT.SALDO, C_SALDANT.CDREDUCONT, C_SALDANT.NRMOVBANC,
                C_SALDANT.NRCHEQUE, C_SALDANT.DTMOVBANC, C_SALDANT.IDMOVBANC, C_SALDANT.NRDOCMBANC, C_SALDANT.DSTIPOMOV,
                C_SALDANT.DSHISMBANC, C_SALDANT.VRMOVBANC, C_SALDANT.NUMEROBAIXA, C_SALDANT.IDORIGMBANC, C_SALDANT.VRSDOCCORR,
                C_SALDANT.VRSDOIMPLA, C_SALDANT.SALDO, C_SALDANT.CDBANCO, C_SALDANT.CDEMPRESA,  C_SALDANT.NMEMPRESA, C_SALDANT.CDAGENCIA,
                C_SALDANT.CDCONTCORR, C_SALDANT.DSCONTCORR, C_SALDANT.CDPICTCONT, C_SALDANT.NMBANCO, C_SALDANT.DSMOVIMENTO,
                C_SALDANT.DSDEBICRED, C_SALDANT.VRLIMITCREDCCOR, C_SALDANT.SDOANT,
                P_DTPERIODOINI, P_DTPERIODOFIN, C_SALDANT.P_SALDOINICIAL);
     IF (V_CDREDUCONT <> C_SALDANT.CDREDUCONT) AND (C_SALDANT.TIPO = 'A') THEN
         BEGIN
             SELECT NVL(SALDO_CONTACORR(C_SALDANT.CDREDUCONT,(TO_DATE(C_SALDANT.DTMOVBANC)+1)),0) AS VRSDOIMPLA
               INTO V_VRSALDOINI
               FROM DUAL;
          EXCEPTION
               WHEN NO_DATA_FOUND THEN
              V_VRSALDOINI := 0;
          END;
          UPDATE MOVBANC_TEMP
             SET SALDOTELA = ABS(V_VRSALDOINI),
                 VRSDOCCORR = ABS(V_VRSALDOINI),
                 DEBCRED    = DECODE(V_VRSALDOINI,ABS(V_VRSALDOINI),'CR','DB')
           WHERE CDREDUCONT = C_SALDANT.CDREDUCONT
             AND NRSEQMOVBANC = TO_NUMBER(V_NRSEQMOVBANC)
             AND NRSEQITMOV = V_NRSEQITMOV;
         V_VRSALDOFIN := NVL(V_VRSALDOINI,0);
     ELSIF (C_SALDANT.TIPO = 'I') THEN
        UPDATE MOVBANC_TEMP
          SET SALDOTELA  = ABS(C_SALDANT.VRSDOIMPLA),
              DEBCRED    = DECODE(C_SALDANT.VRSDOIMPLA,ABS(C_SALDANT.VRSDOIMPLA),'CR','DB')
        WHERE CDREDUCONT = C_SALDANT.CDREDUCONT
          AND NRSEQMOVBANC = TO_NUMBER(V_NRSEQMOVBANC)
          AND NRSEQITMOV = V_NRSEQITMOV;
          V_VRSALDOFIN := NVL(C_SALDANT.VRSDOIMPLA,0);

     ELSE
       V_VRSALDOFIN :=  NVL(V_VRSALDOFIN,0) + (C_SALDANT.DEBITO*-1) + C_SALDANT.CREDITO;
       UPDATE MOVBANC_TEMP
          SET SALDOTELA  = ABS(V_VRSALDOFIN),
              VRSDOCCORR = ABS(V_VRSALDOFIN),
              DEBCRED    = DECODE(V_VRSALDOFIN,ABS(V_VRSALDOFIN),'CR','DB')
        WHERE CDREDUCONT = C_SALDANT.CDREDUCONT
          AND NRSEQMOVBANC = TO_NUMBER(V_NRSEQMOVBANC)
          AND NRSEQITMOV = V_NRSEQITMOV;

     END IF;
     V_CDREDUCONT := C_SALDANT.CDREDUCONT;
   END LOOP;
   P_RESULT := V_NRSEQMOVBANC;
END;
/