CREATE OR REPLACE PROCEDURE GERA_RAZAOGER_SAP(
   P_CDEMPRESA  IN VARCHAR2,
   P_DTINICIAL   IN CONTOPECTBL.DTFINALANC%TYPE,
   P_DTFINAL    IN CONTOPECTBL.DTFINALANC%TYPE,
   P_NRCTBTEMP  IN CTBTEMP.NRCTBTEMP%TYPE,
   P_CDAREAGERE IN VARCHAR2,
   P_CDFILIAL   IN VARCHAR2,
   P_CDCENTCUST IN VARCHAR2,
   P_CDCONTINI  IN SALDCCUS.CDCONTCTBL%TYPE,
   P_CDCONTFIN  IN SALDCCUS.CDCONTCTBL%TYPE)
IS
   V_DTSOMA DATE;
   V_NRSEQ INTEGER;
   V_CONT INTEGER;
   V_RAZAOSOCIAL VARCHAR2(100);
   V_VRSALDO FLOAT;
   V_SOMALANC FLOAT;
   V_COUNT NUMBER;
BEGIN
   V_COUNT := 0;

   V_NRSEQ  := 1;
   FOR C_RAZAO IN (SELECT B.NRORG,
                          B.CDFILIAL ,
                          B.CDCENTCUST ,
                          B.CDCONTCTBL,
                          B.DTLANCCONT,
                          B.CDEMPRESA,
                          B.DSHISTLANC,
                          B.NRLANCCONT AS NRLANCCONT,
                          B.CDDOCUCONT  ||' ' AS CDDOCUCONT,
                          B.NMCONTCTBL,
                          0 AS VRSALDCCUS,
                          B.VRLANCCUST,
                          B.DTCOMPCUSTO,
                          NMCENTCUST,
                          NMFILIAL,
                          B.NMEMPRESA,
                          'COMUM' TIPO,
                          1 CARACTER
                     FROM CENTCUST, FILIAL, EMPRESA, (SELECT LANCCONT.NRORG,
                                                             LANCCONT.CDEMPRESA,
                                                             LANCCONT.DTLANCCONT,
                                                             LANCCONT.CDCONTCTBL,
                                                             LANCCONT.DSHISTLANC,
                                                             LANCCONT.NRLANCCONT,
                                                             LANCCONT.CDDOCUCONT,
                                                             LANCCONT.CDCONTCOME,
                                                             CONTCTBL.NMCONTCTBL,
                                                             LANCCCUSTO.CDFILIAL,
                                                             LANCCCUSTO.CDCENTCUST,
                                                             LANCCCUSTO.VRLANCCUST,
                                                             LANCCCUSTO.DTCOMPCUSTO,
                                                             EMPRESA.NMEMPRESA
                                                        FROM CONTCTBL, LANCCONT, LANCCCUSTO, CENTCUST, EMPRESA, (SELECT CDFILIAL
                                                                                                                   FROM FILIAL
                                                                                                                  WHERE ((INSTR(P_CDAREAGERE, FILIAL.CDAREAGERE) <> 0)
                                                                                                                     OR (P_CDAREAGERE = 'T'))
                                                                                                                    AND (( INSTR(P_CDFILIAL, FILIAL.CDFILIAL) <> 0)
                                                                                                                     OR (P_CDFILIAL = 'T'))) FILIAL
                                                       WHERE ((INSTR(P_CDEMPRESA, LANCCONT.CDEMPRESA ) <> 0)
                                                          OR (P_CDEMPRESA = 'T'))
                                                         AND ((INSTR(P_CDCENTCUST, LANCCCUSTO.CDCENTCUST) <> 0)
                                                          OR P_CDCENTCUST = 'T')
                                                         AND ( LANCCCUSTO.DTCOMPCUSTO BETWEEN P_DTINICIAL AND P_DTFINAL )
                                                         AND ( LANCCONT.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN || 'ZZZZZZZZZZZZZZZZZZZZ' )
                                                         AND ( LANCCCUSTO.NRLANCCONT = LANCCONT.NRLANCCONT )
                                                         AND ( LANCCCUSTO.CDEMPRESA  = LANCCONT.CDEMPRESA )
                                                         AND ( LANCCCUSTO.NRSEQLANC  = LANCCONT.NRSEQLANC )
                                                         AND ( LANCCCUSTO.CDFILIAL   = FILIAL.CDFILIAL )
                                                         AND ( LANCCCUSTO.CDCENTCUST = CENTCUST.CDCENTCUST )
                                                         AND ( LANCCONT.CDCONTCTBL   = CONTCTBL.CDCONTCTBL )
                                                         AND (EMPRESA.CDEMPRESA      = LANCCONT.CDEMPRESA)
                                                         ) B
                  WHERE ((INSTR(P_CDEMPRESA, EMPRESA.CDEMPRESA) <> 0)
                     OR P_CDEMPRESA = 'T')
                    AND (B.CDFILIAL = FILIAL.CDFILIAL )
                    AND (B.CDCENTCUST = CENTCUST.CDCENTCUST)
                    AND B.CDEMPRESA = EMPRESA.CDEMPRESA
                  ORDER BY 1, 2, 3, 4)LOOP

            BEGIN
               SELECT MAX(NVL(CL.NMRAZSOCCLIE, FO.NMRAZSOCFORN))
                 INTO V_RAZAOSOCIAL
                 FROM NOTAFISCAL NF, FORNECEDOR FO, CLIENTE CL
                WHERE (NF.CDEMPRESA    = C_RAZAO.CDEMPRESA)
                  AND (NF.NRLANCTBDOC  = C_RAZAO.NRLANCCONT)
                  AND (FO.CDFORNECED  (+) = NF.CDFORNECED)
                  AND (CL.CDCLIENTE   (+)  = NF.CDCLIENTE);
            EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                 V_RAZAOSOCIAL := NULL;
            END;
            INSERT INTO CTBTEMP
                (NRCTBTEMP,   CDCONTCTBLT, CDFILIALT,  CDCENTCUSTT,
                 CDCONTCOMET, DTLANCCONTT, NRLANCCONTT,VRLANCAMENTT,
                 CDDOCUCONTT, DSHISTLANCT, NRSEQCTB,   IDCTBTEMP, CDEMPRESA, NMRAZSOCLANC)

            VALUES(P_NRCTBTEMP, C_RAZAO.CDCONTCTBL, C_RAZAO.CDFILIAL, C_RAZAO.CDCENTCUST,
                   NULL, C_RAZAO.DTCOMPCUSTO, C_RAZAO.NRLANCCONT, C_RAZAO.VRLANCCUST,
                   C_RAZAO.CDDOCUCONT, C_RAZAO.DSHISTLANC, V_NRSEQ, '1', C_RAZAO.CDEMPRESA, V_RAZAOSOCIAL);

            V_NRSEQ := V_NRSEQ + 1;
            V_COUNT := V_COUNT + 1;
            IF V_COUNT = 5000 THEN
               V_COUNT := 0;
               COMMIT;

            END IF;

   END LOOP;
   COMMIT;
END;
/