CREATE OR REPLACE PROCEDURE JOB_APURACAO_DRF
AS
   V_PARAMPADRAO VARCHAR2(01);
   V_PARAMOK VARCHAR2(01);
   V_EXISTEPARAMFILI VARCHAR2(01);
   V_PROXIMOLISTA VARCHAR2(50);
BEGIN
   BEGIN
      SELECT NVL(IDUTPADRROP, 'S')
        INTO V_PARAMPADRAO
        FROM PARAMGERAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_PARAMPADRAO := 'N';
   END;

   V_PARAMOK := 'S';
   NOVO_CODIGO_NT('LISTA', V_PROXIMOLISTA);
   V_PROXIMOLISTA := SUBSTR(V_PROXIMOLISTA, 11, 10);

   /* TRAZ AS FILIAIS ASSIM COMO NA TELA DE APURACAO DO DRF.
      VERIFICA O PARAMETRO "APURA DRF AUTOMATICAMENTE", CONTIDO NA TELA DE PARAMETROS DE VENDAS, ABA FILIAL. */
   FOR C_FILIAL IN
      (SELECT F.CDFILIAL, F.NMFILIAL, F.CDFILCENTPME,
              DECODE(PG.IDUTILDTPROC, 'S', PF.DTPROCESSA, TRUNC(SYSDATE))  DTPROCESSA,
              (DECODE(PG.IDUTILDTPROC, 'S', PF.DTPROCESSA, TRUNC(SYSDATE)) - D.DTINIAPURACROP) DTDIFERENTE, PG.IDUTILDTPROC,
              D.DTINIAPURACROP, D.DTFINAPURACROP,
              (SELECT COUNT(*)
                 FROM PARFILIALROP
                WHERE CDFILIAL = TIPOPARAM_DRO(F.CDFILIAL)) AS EXISTE_PARAM,
              DECODE(NVL(IDLANCDTMENFIL,'N'), 'S', A.NRDIAS , B.NRDIASGERAL) AS NRDIAS,PF.IDPERPADRAPUR
         FROM APURACAOROP D, FILIAL F, PARAMFILIAL PF, PARAMGERAL PG,
              (SELECT MAX(NRDIAS) AS NRDIASGERAL
                 FROM (SELECT NRDIASENTR NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASSAID NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASAJUS NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S'))B,
              (SELECT MAX(NRDIAS) AS NRDIAS, CDFILIAL
                 FROM (SELECT NRDIASENTRFIL NRDIAS, CDFILIAL
                         FROM PARAMFILIAL
                        WHERE NVL(IDLANCDTMENFIL,'N') = 'S'
                    UNION ALL
                       SELECT NRDIASSAIDFIL NRDIAS, CDFILIAL
                         FROM PARAMFILIAL
                        WHERE  NVL(IDLANCDTMENFIL,'N') = 'S'
                    UNION ALL
                       SELECT NRDIASAJUSFIL NRDIAS, CDFILIAL
                        FROM PARAMFILIAL
                       WHERE NVL(IDLANCDTMENFIL,'N') = 'S')
               GROUP BY CDFILIAL) A, PARAVEND PV
        WHERE (D.CDFILIAL(+)  = F.CDFILIAL)
          AND (PF.CDFILIAL(+) = F.CDFILIAL)
          AND (A.CDFILIAL (+) = F.CDFILIAL)
          AND (F.IDCNTRRESC     <> 'S')
          AND F.CDFILIAL = PV.CDFILIAL
          AND PV.IDAPUDRFAUT = 'S'
          AND F.CDFILIAL NOT IN (SELECT DISTINCT F.CDFILIAL
                                   FROM FILIAL F, PARFILIALROP P, PARAMGERAL
                                  WHERE CDFILCENTPME IS NOT NULL
                                    AND CDFILCENTPME = P.CDFILIAL
                                    AND NVL(IDFILIALAGRROP,'N') = 'S'
                                    AND IDUTPADRROP = 'N'

                                  UNION ALL
                                 SELECT F.CDFILIAL
                                   FROM FILIAL F, PARFILIALROP P, PARAMGERAL
                                  WHERE CDFILCENTPME IS NOT NULL
                                    AND P.CDFILIAL = 'P'
                                    AND NVL(IDFILIALAGRROP,'N') = 'S'
                                    AND IDUTPADRROP = 'S'
                                  UNION ALL
                                 SELECT F.CDFILIAL
                                   FROM FILIAL F, PARFILIALROP P, PARFILIALROP A
                                  WHERE CDFILCENTPME IS NOT NULL
                                    AND P.IDUTPADRROPFIL = 'S'
                                    AND NVL(A.IDFILIALAGRROP,'N') = 'S'
                                    AND A.CDFILIAL = 'P'
                                  UNION ALL
                                 SELECT DISTINCT F.CDFILIAL
                                   FROM FILIAL F, PARFILIALROP P, PARAMGERAL
                                  WHERE CDFILCENTPME IS NOT NULL
                                    AND CDFILCENTPME = P.CDFILIAL
                                    AND NVL(IDFILIALAGRROP,'N') = 'S'
                                    AND IDUTPADRROP <> 'S')
        ORDER BY F.CDFILIAL) LOOP
      IF V_PARAMOK = 'S' THEN
         V_EXISTEPARAMFILI := 'N';
         IF V_PARAMPADRAO = 'N' THEN
            BEGIN
               SELECT NVL(IDUTPADRROPFIL, 0)
                 INTO V_EXISTEPARAMFILI
                 FROM PARFILIALROP
                WHERE CDFILIAL = C_FILIAL.CDFILIAL;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_EXISTEPARAMFILI := 'N';
            END;

            IF V_EXISTEPARAMFILI = 'S' THEN
               V_EXISTEPARAMFILI := 'N';
               BEGIN
                  SELECT NVL(IDUTPADRROPFIL, 'N')
                    INTO V_EXISTEPARAMFILI
                    FROM PARFILIALROP
                   WHERE CDFILIAL = 'P';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_EXISTEPARAMFILI := 'N';
               END;
               IF V_EXISTEPARAMFILI = 'N' THEN
                  V_PARAMOK := 'N';
                  EXIT;
               END IF;
            ELSE
               V_PARAMOK := 'N';
               EXIT;
            END IF;
         END IF;

         IF V_PARAMOK = 'S' THEN
            INSERT INTO LISTA
               (NRLISTA, CDLISTA)
            VALUES
               (V_PROXIMOLISTA, C_FILIAL.CDFILIAL);
         END IF;
      END IF;
   END LOOP;

   IF V_PARAMOK = 'S' THEN
      PERIODO_APURAC_RESOPERFILI(V_PROXIMOLISTA);
   END IF;
   DELETE
     FROM LISTA
    WHERE NRLISTA = V_PROXIMOLISTA;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('JOB_APURACAO_DRF','6.10.000'); END;
/
