CREATE OR REPLACE
PROCEDURE ETL_DREGERENCIAL
                     (P_DTINI  IN DATE,
                      P_DTFINA IN DATE)
IS
  V_DTFIM           DATE;
  V_DTINI           DATE;
  V_DTMES           VARCHAR2(07);
  V_DTANO           VARCHAR2(04);
  V_INIC            VARCHAR2(2) NOT NULL := '01';
  V_CDOPSUPER       VARCHAR2(12);
  V_CDOPGER         VARCHAR2(12);
  V_CDSEGM          VARCHAR2(5);
  V_CDAREAGERE      VARCHAR2(3);
  V_RESULTADO       FLOAT := 0;
  V_SALDO           FLOAT; 
  V_TIPODEMO        VARCHAR2(4);
  V_VRPARCELA1      FLOAT;
  V_VRPARCELA2      FLOAT;
  V_VRTXADMCENT     FLOAT;
  V_RECBRUTA        FLOAT(126):= 0;
  V_IMPOSTO         FLOAT(126):= 0;
  V_COMPGERAIS01    FLOAT(126):= 0;
  V_DESPOPERA       FLOAT(126):= 0;
  V_MAODEOBRA01     FLOAT(126):= 0;
  V_RECLIQUIDA      FLOAT(126):= 0;
  V_TAR             FLOAT(126):= 0;
  V_TAC             FLOAT(126):= 0;
  V_RESULBRUTO      FLOAT(126):= 0;
  V_FINANCEIRAS     FLOAT(126):= 0;
  V_CDTIPODEMO      VARCHAR2(2);
  V_CDITEMSTRU      VARCHAR2(4);
BEGIN

    V_DTINI  := TO_DATE(V_INIC||TO_CHAR(P_DTINI, 'MMYYYY'), 'DDMMYYYY');

    V_DTMES  :=  TO_CHAR(V_DTINI,'MM');
    V_DTANO  :=  TO_CHAR(V_DTINI,'YYYY');


    DELETE FROM ETLDREGER
    WHERE DTINICIOETLDRE >= V_DTINI;
    
    DELETE FROM ETLDREGERTMP;


    WHILE (V_DTINI  <=  P_DTFINA) LOOP

       V_DTFIM := LAST_DAY( V_DTINI);
       
       
FOR F IN
(
 
             SELECT L.CDEMPRESA, LC.CDFILIAL, LC.CDCENTCUST
              FROM LANCCONT L, LANCCCUSTO LC, ITEMAPUCTB I
             WHERE L.CDEMPRESA = LC.CDEMPRESA
               AND L.NRLANCCONT = LC.NRLANCCONT
               AND L.NRSEQLANC = LC.NRSEQLANC
               AND LC.DTCOMPCUSTO BETWEEN V_DTINI AND V_DTFIM
               AND L.CDCONTCTBL LIKE I.CDORIGDEMO ||'%'
               AND I.IDTIPOORIG = 'S'
             GROUP BY L.CDEMPRESA, LC.CDFILIAL, LC.CDCENTCUST 
             ORDER BY CDFILIAL
             
)

LOOP


              SELECT CDSUPERVISOR, CDGOPERAC, CDSEGMER, CDAREAGERE
                INTO V_CDOPSUPER, V_CDOPGER, V_CDSEGM, V_CDAREAGERE
                FROM FILIAL
              WHERE CDFILIAL = F.CDFILIAL
                AND CDEMPRESA = F.CDEMPRESA;
       
FOR V IN (
            SELECT I.CDITEMSTRU, SUM(VRLANCCUST) AS VRSALDCONT, I.CDTIPODEMO
              FROM LANCCONT L, LANCCCUSTO LC, ITEMAPUCTB I
             WHERE L.CDEMPRESA = LC.CDEMPRESA
               AND L.CDEMPRESA = F.CDEMPRESA
               AND L.NRLANCCONT = LC.NRLANCCONT
               AND L.NRSEQLANC = LC.NRSEQLANC
               AND LC.CDCENTCUST = F.CDCENTCUST
               AND LC.CDFILIAL = F.CDFILIAL
               AND LC.DTCOMPCUSTO BETWEEN V_DTINI AND V_DTFIM
               AND L.CDCONTCTBL LIKE I.CDORIGDEMO ||'%'
               AND I.IDTIPOORIG = 'S'
               AND I.CDTIPODEMO = '01'
             GROUP BY I.CDTIPODEMO,I.CDITEMSTRU
             ORDER BY I.CDTIPODEMO
         ) 
         LOOP
         
               INSERT INTO ETLDREGERTMP
                  (
                   CDEMPRESA, CDFILIAL, CDTIPODEMO, CDCENTCUST,
                   CDITEMSTRU, CDMESBI, CDANOBI, VRSALDCONT, DTINICIODRETMP
                  )
               VALUES
                  (
                   F.CDEMPRESA, F.CDFILIAL, V.CDTIPODEMO, F.CDCENTCUST,
                   V.CDITEMSTRU, V_DTMES, V_DTANO, V.VRSALDCONT, V_DTINI
                  );
                  
                COMMIT;
                
         END LOOP;
                  
         
            FOR COD IN 
            (
           
                SELECT  DISTINCT (IT.NMITEMSTRU), I.CDITEMSTRU, I.CDTIPODEMO
                  FROM ITEMDEMOF IT, ITEMAPUCTB I
                WHERE I.CDTIPODEMO = '01'
                  AND IT.CDTIPODEMO = '01'
                  AND I.CDITEMSTRU = IT.CDITEMSTRU
                  AND I.IDTIPOORIG = 'V'
                  AND IT.IDOPERACAO <> 'D'
                ORDER BY I.CDTIPODEMO, I.CDITEMSTRU
            )    
            LOOP
                      FOR D IN
                      (
                           SELECT  I.CDORIGDEMO, IT.IDOPERACAO
                             FROM ITEMDEMOF IT, ITEMAPUCTB I
                           WHERE I.CDTIPODEMO = IT.CDTIPODEMO
                             AND I.CDITEMSTRU = IT.CDITEMSTRU
                             AND I.CDTIPODEMO = COD.CDTIPODEMO
                             AND I.CDITEMSTRU = COD.CDITEMSTRU
                           ORDER BY I.CDTIPODEMO,I.CDITEMSTRU, I.CDSEGUAPUR
                      )
                      LOOP
                          
                        
                         BEGIN 
                           SELECT VRSALDCONT
                             INTO V_SALDO
                            FROM ETLDREGERTMP E
                           WHERE E.CDITEMSTRU = SUBSTR(D.CDORIGDEMO,3,4)
                             AND E.CDTIPODEMO = SUBSTR(D.CDORIGDEMO,1,2)
                             AND E.CDFILIAL = F.CDFILIAL
                             AND E.CDEMPRESA = F.CDEMPRESA
                             AND E.CDCENTCUST = F.CDCENTCUST
                             AND E.CDMESBI = V_DTMES;
                             
                             EXCEPTION
                             
                              WHEN OTHERS THEN
                               V_SALDO := 0;
                         END;
                             
                             
                                 IF D.IDOPERACAO = 'S' THEN
                                    V_RESULTADO := V_RESULTADO + V_SALDO;
                                  IF D.IDOPERACAO = 'U' THEN
                                     V_RESULTADO := V_SALDO - V_RESULTADO;
                                  END IF;
                                 END IF; 
                                 IF D.IDOPERACAO = 'M' THEN
                                    V_VRPARCELA1 := V_SALDO;
                                    V_VRPARCELA2 := 0;
                                 END IF;
                                 
                      
                      END LOOP;
                      
                      
                                     IF COD.NMITEMSTRU = 'TAR' THEN
                                         SELECT NVL(MAX(VRTXDESPREGE),0)
                                           INTO V_VRPARCELA2
                                           FROM TXDESPREGE
                                         WHERE CDAREAGERE = V_CDAREAGERE
                                           AND DTTXDESPREGE = V_DTINI;
                                           IF V_VRPARCELA2 = 0 THEN                                                                                                                                                                                                             
                                             SELECT NVL(MAX(VRTXDESPREGE),0)                                                                                                                                                                                                   
                                               INTO V_VRPARCELA2                                                                                                                                                                                                               
                                             FROM TXDESPREGE                                                                                                                                                                                                                 
                                             WHERE CDAREAGERE = V_CDAREAGERE                                                                                                                                                                                                  
                                             AND DTTXDESPREGE = ADD_MONTHS(V_DTINI, -1);                                                                                                                                                                                    
                                           END IF; 
                                           IF (V_VRPARCELA1 <> 0) AND (V_VRPARCELA2 <> 0) THEN
                                              V_RESULTADO := (V_VRPARCELA1 * (V_VRPARCELA2/100)) * (-1);
                                           ELSE
                                              V_RESULTADO := 0;
                                           END IF;
                                     END IF;
                                         
                                       IF COD.NMITEMSTRU = 'TAC' THEN
                                             IF (V_VRPARCELA1 <> 0) THEN
                                               SELECT NVL(MAX(VRTXDESPREGE),0)
                                                INTO V_VRTXADMCENT
                                                FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                                               WHERE T.CDAREAGERE = FL.CDAREAGERE
                                                AND FL.CDSUPERVISOR = O.CDOPERADOR
                                                AND O.NMOPERADOR = 'TAC'
                                                AND DTTXDESPREGE = V_DTINI;
                                              IF V_VRTXADMCENT = 0 THEN
                                               SELECT NVL(MAX(VRTXDESPREGE),0)
                                                 INTO V_VRTXADMCENT
                                                 FROM TXDESPREGE T, FILIAL FL, OPERADOR O
                                               WHERE T.CDAREAGERE = FL.CDAREAGERE
                                                 AND FL.CDSUPERVISOR = O.CDOPERADOR
                                                 AND O.NMOPERADOR = 'TAC'
                                                 AND DTTXDESPREGE = ADD_MONTHS(V_DTINI, -1);
                                              END IF;
                                                 V_RESULTADO := (V_VRPARCELA1 * (V_VRTXADMCENT/100)) * (-1);
                                             ELSE
                                                 V_RESULTADO := 0; 
                                             END IF;
                                       END IF;
                      
                                INSERT INTO ETLDREGERTMP
                                (
                                 CDEMPRESA    , CDFILIAL    , CDTIPODEMO      , 
                                 CDCENTCUST   , CDITEMSTRU  , CDMESBI         , 
                                 CDANOBI      , VRSALDCONT  , DTINICIODRETMP
                                ) 
                                VALUES 
                                (
                                  F.CDEMPRESA   , F.CDFILIAL      , COD.CDTIPODEMO  , 
                                  F.CDCENTCUST  , COD.CDITEMSTRU  , V_DTMES         , 
                                  V_DTANO       , V_RESULTADO     , V_DTINI 
                                );
                                
                                COMMIT;
                                
                                V_RESULTADO := 0;

         
            END LOOP;
              
          FOR H IN
          (
            SELECT DISTINCT(CDFILIAL), CDTIPODEMO, CDEMPRESA, CDCENTCUST
              FROM ETLDREGERTMP
            WHERE CDFILIAL = F.CDFILIAL
              AND CDEMPRESA = F.CDEMPRESA
              AND CDCENTCUST = F.CDCENTCUST
              AND DTINICIODRETMP = V_DTINI
          )
          LOOP   
             FOR P IN
                 (
                   SELECT CDTIPODEMO, CDFILIAL, CDEMPRESA, NVL(VRSALDCONT,0) AS VRSALDCONT, CDITEMSTRU
                    FROM ETLDREGERTMP
                   WHERE CDFILIAL = H.CDFILIAL
                     AND CDEMPRESA = H.CDEMPRESA
                     AND CDTIPODEMO = H.CDTIPODEMO
                     AND CDCENTCUST = H.CDCENTCUST
                     AND DTINICIODRETMP = V_DTINI
                 )
                 LOOP
                   
                   V_CDTIPODEMO := H.CDTIPODEMO;
                 
                  IF P.CDITEMSTRU = '0010' AND P.CDTIPODEMO = '01' THEN
                     V_RECBRUTA := P.VRSALDCONT;
                  END IF;
                   IF P.CDITEMSTRU = '0020' AND P.CDTIPODEMO = '01' THEN
                      V_IMPOSTO := P.VRSALDCONT;
                   END IF;
                    IF P.CDITEMSTRU = '0040' AND P.CDTIPODEMO = '01' THEN
                       V_COMPGERAIS01 := P.VRSALDCONT;
                    END IF;
                     IF P.CDITEMSTRU = '0050' AND P.CDTIPODEMO = '01' THEN
                        V_MAODEOBRA01 := P.VRSALDCONT;
                     END IF;
                      IF P.CDITEMSTRU = '0060' AND P.CDTIPODEMO = '01' THEN
                         V_DESPOPERA := P.VRSALDCONT;
                      END IF;
                       IF P.CDITEMSTRU = '0030' AND P.CDTIPODEMO = '01' THEN
                          V_RECLIQUIDA := P.VRSALDCONT;
                       END IF;
                        IF P.CDITEMSTRU = '0065' AND P.CDTIPODEMO = '01' THEN
                           V_TAR := P.VRSALDCONT;
                        END IF;
                         IF P.CDITEMSTRU = '0067' AND P.CDTIPODEMO = '01' THEN
                            V_TAC := P.VRSALDCONT;
                         END IF;
                          IF P.CDITEMSTRU = '0070' AND P.CDTIPODEMO = '01' THEN
                             V_RESULBRUTO := P.VRSALDCONT;
                          END IF;
                           IF P.CDITEMSTRU = '0062' AND P.CDTIPODEMO = '01' THEN
                              V_FINANCEIRAS := P.VRSALDCONT;
                           END IF;
                                   
                                 
                 END LOOP;
                 
                 V_RESULBRUTO:= V_RECBRUTA+V_IMPOSTO+V_DESPOPERA+V_MAODEOBRA01+V_TAC+V_TAR+V_FINANCEIRAS+V_COMPGERAIS01;
                 
                INSERT INTO ETLDREGER
                (
                  CDEMPRESA      ,  CDFILIAL        ,  CDTIPODEMO       , 
                  CDCENTCUST     ,  CDOPSUPERVISOR  ,  CDOPGERREG       , 
                  CDSEGMER       ,  CDMESBI         ,  CDANOBI          , 
                  VRRECBRUTADRE  ,  VRIMPOSTODRE    ,  VRCOMPGERAISDRE  , 
                  VRDESPOPERADRE ,  VRMAOOBRADRE    ,  VRRECLIQUIDADRE  , 
                  VRTAR          ,  VRTAC           ,  VRRESULBRUTODRE  , 
                  VRFINACDRE     ,  CDAREAGERE      ,  DTINICIOETLDRE     
                )
                VALUES
                (
                  H.CDEMPRESA    ,  H.CDFILIAL      ,  V_CDTIPODEMO     , 
                  H.CDCENTCUST   ,  V_CDOPSUPER     ,  V_CDOPGER        , 
                  V_CDSEGM       ,  V_DTMES         ,  V_DTANO          , 
                  V_RECBRUTA     ,  V_IMPOSTO       ,  V_COMPGERAIS01   , 
                  V_DESPOPERA    ,  V_MAODEOBRA01   ,  V_RECLIQUIDA     ,
                  V_TAR          ,  V_TAC           ,  V_RESULBRUTO     , 
                  V_FINANCEIRAS  ,  V_CDAREAGERE    ,  V_DTINI
   
                );
                
                
                V_RECBRUTA        := 0;
                V_IMPOSTO         := 0;
                V_COMPGERAIS01    := 0;
                V_DESPOPERA       := 0;
                V_MAODEOBRA01     := 0;
                V_RECLIQUIDA      := 0;
                V_TAR             := 0;
                V_TAC             := 0;
                V_RESULBRUTO      := 0;
                V_FINANCEIRAS     := 0;
                
                COMMIT;
                    
          
          END LOOP;

    END LOOP;
    
     V_DTINI := ADD_MONTHS(V_DTINI,1);
     V_DTMES  :=  TO_CHAR(V_DTINI,'MM');
    
END LOOP;                
END;
