CREATE OR REPLACE PROCEDURE GERA_RELCMV(P_DTINICIAL  DATE, 
                      P_CDFILIAL   VARCHAR2) 
IS 
V_AJUSTREST  FLOAT; 
V_AJUSTREST1 FLOAT; 
V_AJUSTRESTFINAL FLOAT; 
 
BEGIN 
   V_AJUSTREST      := 0; 
   V_AJUSTREST1     := 0; 
   V_AJUSTRESTFINAL := 0; 
    
    
 
   FOR C_FILIAL IN (SELECT CDFILIAL, NMFILIAL 
                      FROM FILIAL 
                     WHERE ((P_CDFILIAL= CDFILIAL) OR ('T' = P_CDFILIAL))) LOOP 
       DELETE FROM USR_MESCMVREL 
        WHERE CDFILIAL = P_CDFILIAL 
          AND LAST_DAY(DTMESCMVREL) = LAST_DAY(P_DTINICIAL); 
 
       
      FOR C_GRUPO IN(SELECT CDFILIAL, CDGRUPOESTR 
                       FROM LAV_GRUPOMERCESTR 
      	             WHERE CDFILIAL = C_FILIAL.CDFILIAL) LOOP 
 
          
         FOR C_ESTRAT IN (SELECT LC.DSCUSTOETR, LC.NRSEQCUSTO, ROUND(LE.VRCUSTESTRCMV,2) AS VRCUSTESTRCMV, 
                                 LC.IDTPCUSTO, LC.CDGRUPOESTR, LC.IDAPURCLIPRITER 
                            FROM LAV_ETLMERCESTR LE, LAV_CUSTOMERESTR LC 
                           WHERE LE.CDFILIAL  = C_FILIAL.CDFILIAL 
                             AND LE.CDGRUPOESTR = C_GRUPO.CDGRUPOESTR 
                             AND LE.CDFILIAL = LC.CDFILIAL 
                             AND LE.CDGRUPOESTR = LC.CDGRUPOESTR 
                             AND LE.NRSEQCUSTO = LC.NRSEQCUSTO 
                             AND LE.CDANOBI  = TO_CHAR(P_DTINICIAL,'YYYY') 
                             AND LE.CDMESBI  = SUBSTR(P_DTINICIAL,4,2) 
                           ORDER BY LC.NRSEQCUSTO)LOOP 
 
	     
            IF (((C_FILIAL.CDFILIAL = '9999') and (C_GRUPO.CDGRUPOESTR = '0000000001') AND (C_ESTRAT.IDTPCUSTO = 'AJ')) or 
               ((C_FILIAL.CDFILIAL = '9998') and (C_GRUPO.CDGRUPOESTR = '0000000006') AND (C_ESTRAT.IDTPCUSTO = 'AJ')) or
               ((C_FILIAL.CDFILIAL = '9996') and (C_GRUPO.CDGRUPOESTR = '0000000004') AND (C_ESTRAT.IDTPCUSTO = 'AJ'))) THEN 
               BEGIN 
                  SELECT SUM(LE.VRCUSTESTRCMV)  AS VRCUSTESTRCMV 
                    INTO V_AJUSTREST 
                    FROM (SELECT SUM(ROUND(LE.VRCUSTESTRCMV,2)) AS VRCUSTESTRCMV 
                            FROM LAV_ETLMERCESTR LE, LAV_CUSTOMERESTR LC 
                           WHERE (Le.CDFILIAL = C_FILIAL.CDFILIAL) 
                             AND LE.CDANOBI    = TO_CHAR(P_DTINICIAL,'YYYY') 
                             AND LE.CDMESBI    = SUBSTR(P_DTINICIAL,4,2) 
                             AND LE.CDGRUPOESTR = LC.CDGRUPOESTR 
                             AND LE.NRSEQCUSTO = LC.NRSEQCUSTO 
                             AND LE.CDFILIAL   = LC.CDFILIAL 
                             AND LC.IDTPCUSTO <> 'AJ'
                             
                             
                           UNION 
                          SELECT SUM(ROUND(-LE.VRCUSTESTRCMV,2)) AS VRCUSTESTRCMV 
                            FROM LAV_ETLMERCESTR LE, LAV_CUSTOMERESTR LC 
                           WHERE (Le.CDFILIAL = C_FILIAL.CDFILIAL) 
                             AND LE.CDANOBI       = TO_CHAR(P_DTINICIAL,'YYYY') 
                             AND LE.CDMESBI       = SUBSTR(P_DTINICIAL,4,2) 
                             AND LE.CDGRUPOESTR = LC.CDGRUPOESTR 
                             AND LE.NRSEQCUSTO    = LC.NRSEQCUSTO 
                             AND LE.CDFILIAL      = LC.CDFILIAL 
                             AND LC.IDTPCUSTO     = 'AJ' 
                             AND LE.VRCUSTESTRCMV > 0 
                           UNION 
                          SELECT SUM(ROUND(LE.VRCUSTESTRCMV*-1,2)) AS VRCUSTESTRCMV 
                            FROM LAV_ETLMERCESTR LE, LAV_CUSTOMERESTR LC 
                           WHERE (Le.CDFILIAL = C_FILIAL.CDFILIAL) 
                             AND LE.CDANOBI       = TO_CHAR(P_DTINICIAL,'YYYY') 
                             AND LE.CDMESBI       = SUBSTR(P_DTINICIAL,4,2) 
                             AND LE.CDGRUPOESTR = LC.CDGRUPOESTR 
                             AND LE.NRSEQCUSTO    = LC.NRSEQCUSTO 
                             AND LE.CDFILIAL      = LC.CDFILIAL 
                             AND LC.IDTPCUSTO     = 'AJ' 
                             AND LE.VRCUSTESTRCMV < 0) LE; 
               EXCEPTION 
                  WHEN NO_DATA_FOUND THEN 
                     V_AJUSTREST := 0; 
               END; 
 
               BEGIN 
                  SELECT ROUND(VRCUSTOCMV,2) AS VRCUSTOCMV 
                    INTO V_AJUSTREST1 
                    FROM LAV_ETLCMV, FILIAL F 
                   WHERE CDANOBI = TO_CHAR(P_DTINICIAL,'YYYY') 
                     AND CDMESBI = SUBSTR(TO_CHAR(P_DTINICIAL),4,2) 
                     AND LAV_ETLCMV.CDFILIAL = C_FILIAL.CDFILIAL 
                     AND F.CDFILIAL = LAV_ETLCMV.CDFILIAL; 
               EXCEPTION 
                  WHEN NO_DATA_FOUND THEN 
                     V_AJUSTREST1 := 0; 
               END; 
 
               V_AJUSTRESTFINAL :=  V_AJUSTREST1 - V_AJUSTREST; 
 
               INSERT INTO USR_MESCMVREL 
               (CDFILIAL, CDGRUPOESTR, ORDMESCMV, VRCMVREL, DTMESCMVREL) 
               VALUES 
                 (C_FILIAL.CDFILIAL, C_GRUPO.CDGRUPOESTR, C_ESTRAT.NRSEQCUSTO, (ROUND(C_ESTRAT.VRCUSTESTRCMV,2) + (V_AJUSTRESTFINAL*-1)), P_DTINICIAL); 
  
            ELSE 
               INSERT INTO USR_MESCMVREL 
               (CDFILIAL, CDGRUPOESTR, ORDMESCMV, VRCMVREL, DTMESCMVREL) 
               VALUES 
               (C_FILIAL.CDFILIAL, C_GRUPO.CDGRUPOESTR, C_ESTRAT.NRSEQCUSTO, ROUND(C_ESTRAT.VRCUSTESTRCMV,2), P_DTINICIAL); 
            END IF; 
         END LOOP; 
      END LOOP; 
   END LOOP; 
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('GERA_RELCMV','5.65.xxx'); END;
/