CREATE OR REPLACE PROCEDURE APURA_AJUSTECONS 
 (P_CDFILIAL  IN VARCHAR2, 
  P_DATAINI   IN DATE, 
  P_DATAFIN   IN DATE, 
  P_ORIGETL   IN VARCHAR2 DEFAULT 'S') 
IS 
  V_CDFILIAL         VARCHAR2(04); 
  V_VRRETIRADA       FLOAT; 
  V_VRAJUSTE         NUMBER; 
  V_NRMOVIESTQ       VARCHAR2(20); 
  V_PRODINI          VARCHAR2(20); 
  V_PRODFIM          VARCHAR2(20); 
  V_NRETLAJUSTECONS  NUMBER; 
  V_ORDENACAO        VARCHAR2(10); 
  V_QTDEPERIOD       NUMBER; 
BEGIN 
   V_ORDENACAO := 'BINARY'; 
    
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = '||V_ORDENACAO; 
    
   V_QTDEPERIOD := CASE WHEN P_ORIGETL = 'S' 
                        THEN 4 
                        ELSE 1 
                   END; 
    
   FOR C_FILIAL IN (SELECT F.CDFILIAL, F.NMFILIAL, F.NRORG, PF.CDTIPCUSTEST,  
                           CASE WHEN P.IDUTILDTPROC = 'S' 
                                THEN NVL(PF.DTPROCESSA,SYSDATE) 
                                ELSE SYSDATE 
                           END DATAPROC 
                      FROM FILIAL F INNER JOIN PARAMFILIAL PF ON F.CDFILIAL = PF.CDFILIAL 
                                    CROSS JOIN PARAMGERAL P 
                     WHERE ((F.CDFILIAL = P_CDFILIAL) OR (P_CDFILIAL = 'T')) 
                       AND F.CDGOPERAC IS NOT NULL) LOOP 
       
      FOR C_DATA IN (SELECT CASE WHEN (P_ORIGETL = 'S' AND D.CONT <> 0) 
                                 THEN FIRST_DAY(ADD_MONTHS(SYSDATE,-D.CONT)) 
                                 WHEN (P_ORIGETL = 'S' AND D.CONT = 0) 
                                 THEN FIRST_DAY(SYSDATE) 
                                 ELSE FIRST_DAY(NVL(P_DATAINI,SYSDATE)) 
                            END DTINICIAL, 
                            CASE WHEN (P_ORIGETL = 'S' AND D.CONT = 0) 
                                 THEN SYSDATE 
                                 WHEN (P_ORIGETL = 'S' AND D.CONT <> 0) 
                                 THEN LAST_DAY(ADD_MONTHS(SYSDATE,-D.CONT)) 
                                 ELSE LAST_DAY(NVL(P_DATAFIN,SYSDATE)) 
                            END DTFINAL 
                       FROM (SELECT ROWNUM-1 CONT FROM DUAL CONNECT BY LEVEL <= V_QTDEPERIOD ORDER BY 1 DESC) D) LOOP 
                      
         SELECT SUM(I.VRTOTLANCTO) 
           INTO V_VRRETIRADA 
           FROM ITLANCTOEST I 
          WHERE I.IDTIPOMOVI = '3' 
            AND I.CDFILIMOVI = C_FILIAL.CDFILIAL 
            AND I.DTLANCMOVI BETWEEN C_DATA.DTINICIAL AND C_DATA.DTFINAL; 
            
         SELECT MAX(CDARVPROD) AS CDPRODFIN, MIN(CDARVPROD) AS CDPRODINI    
           INTO V_PRODFIM, V_PRODINI 
           FROM PRODUTO P, ITLANCTOEST I    
          WHERE CDFILIMOVI = C_FILIAL.CDFILIAL 
            AND DTLANCMOVI BETWEEN C_DATA.DTINICIAL AND C_DATA.DTFINAL 
            AND I.CDPRODMOVI = P.CDPRODUTO 
            AND IDTIPOMOVI = '1'; 
   
         SELECT LPAD(ABS(ROUND(DBMS_RANDOM.VALUE(9999,9999999999))),10,'0') 
           INTO V_NRMOVIESTQ 
           FROM DUAL; 
   
         MOVIMENTACAO_ESTOQUE(C_FILIAL.CDFILIAL, NULL, NULL, NULL, 1, C_DATA.DTINICIAL, C_DATA.DTFINAL, V_PRODINI, V_PRODFIM, 3,NVL(C_FILIAL.CDTIPCUSTEST,3), V_NRMOVIESTQ, 'S');          
         
         SELECT SUM(DECODE(I.QTLANCTOEST, 0, (ROUND(R.QTTOTALANT,3)* ROUND(R.VRCUSTMEDI,5))*(-1) ,(ROUND((I.QTLANCTOEST -R.QTTOTALANT),3)* ROUND(R.VRCUSTMEDI,5)))) 
           INTO V_VRAJUSTE 
           FROM RELAT_MOVIEST R, ITLANCTOEST I 
          WHERE R.CDFILIMOVIEST = C_FILIAL.CDFILIAL 
            AND R.NRRELATMOVI = V_NRMOVIESTQ 
            AND R.DTLANCMOVIEST BETWEEN C_DATA.DTINICIAL AND C_DATA.DTFINAL 
            AND I.CDFILIAL = R.CDFILLANCMEST 
            AND I.CDFILIMOVI   = R.CDFILIMOVIEST 
            AND I.NRLANCESTQ   = R.NRLANCMOVIEST 
            AND I.NRSEQUITEM   = R.NRSEQUMOVIEST 
            AND I.CDALMOXARIFE = R.CDALMOXARIRM 
            AND I.CDLOCALESTOQ = R.CDLOCALESTRM 
            AND I.IDTIPOMOVI   = R.IDTIPOMOVIEST 
            AND I.IDTIPOMOVI   = '1'; 
               
         DELETE FROM ETLAJUSTECONS  
          WHERE CDFILIAL = C_FILIAL.CDFILIAL  
            AND TO_DATE(DTINICIAL,'DD/MM/YYYY') = TO_DATE(C_DATA.DTINICIAL,'DD/MM/YYYY'); 
         
         NOVO_CODIGO('ETLAJUSTECONS', V_NRETLAJUSTECONS); 
         
         INSERT INTO ETLAJUSTECONS 
            (NRETLAJUSTECONS, CDFILIAL, NMFILIAL, VRRETIRADA, VRAJUSTE, DTINICIAL, DTFINAL, DTULTAPUR, NRORG) 
         VALUES 
            (V_NRETLAJUSTECONS, C_FILIAL.CDFILIAL, C_FILIAL.NMFILIAL, V_VRRETIRADA, V_VRAJUSTE, C_DATA.DTINICIAL, C_DATA.DTFINAL, SYSDATE, C_FILIAL.NRORG); 
   
         DELETE FROM RELAT_MOVIEST 
               WHERE CDFILIMOVIEST = C_FILIAL.CDFILIAL 
                 AND NRRELATMOVI = V_NRMOVIESTQ; 
      END LOOP; 
   END LOOP; 
 
   DELETE FROM ETLAJUSTECONS  
    WHERE DTFINAL < ADD_MONTHS(SYSDATE,-12); 
     
   COMMIT; 
END;
/