CREATE OR REPLACE
PROCEDURE PERIODICIDADE_NAO_CALCULADA(
   P_CDFILIAL    IN VARCHAR2,
   P_TCDFILIAL   IN VARCHAR2,
   P_CDGRUPFILI  IN GRUPFILI.CDGRUPFILI%TYPE,
   P_CDPRODINI   IN PRODUTO.CDARVPROD%TYPE,
   P_CDPRODFIN   IN PRODUTO.CDARVPROD%TYPE,
   P_CDOPERADOR  IN OPERADOR.CDOPERADOR%TYPE DEFAULT NULL) IS

   TYPE CURTYPE      IS REF CURSOR;
   CUR_FILIAL        CURTYPE;

   V_IDSISDOS        CHAR(1);
   V_IDUTILDTPROC    CHAR(1);
   V_UTILIZAPRODPREF CHAR(1);
   V_IDESTOQUE       CHAR(1);

   V_DTPROCESS       DATE;

   V_CDPRODINI       VARCHAR2(40);
   V_CDPRODFIN       VARCHAR2(40);
   V_SQLFILIAL       VARCHAR2(2000);
   V_SQLF_GFILIAL    VARCHAR2(1000);
   V_SQLW_GFILIAL    VARCHAR2(1000);
   V_SQLFILTER       VARCHAR2(2000);
   V_FILIAL_CORRENTE VARCHAR2(4);
   V_FILIAL_UNICA    VARCHAR2(4);

   V_FILINECE        INT;

   W_S               CHAR(1):= 'S';
   W_N               CHAR(1):= 'N';
   W_1               CHAR(1):= '1';
   V_CDPERIENTR      CALECOMP.CDPERIENTR%TYPE;
   V_IDRESTRFILIOPER PARAMGERAL.IDRESTRFILIOPER%TYPE;

BEGIN
   BEGIN
     SELECT NVL(IDSISDOS, W_1), NVL(IDUTILDTPROC, W_N), NVL(IDRESTRFILIOPER, W_N)
     INTO V_IDSISDOS, V_IDUTILDTPROC, V_IDRESTRFILIOPER
     FROM PARAMGERAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN BEGIN
       V_IDSISDOS:= W_1;
       V_IDUTILDTPROC:= W_N;
     END;
   END;

   IF P_CDPRODINI > P_CDPRODFIN THEN
      V_CDPRODINI := P_CDPRODINI || 'ZZZZZZZZZ';
      V_CDPRODFIN := P_CDPRODFIN;
   ELSE
      V_CDPRODFIN := P_CDPRODFIN || 'ZZZZZZZZZ';
      V_CDPRODINI := P_CDPRODINI;
   END IF;

   /* MONTAGEM DA QUERY PARA BUSCAR FILIAIS */
   IF (((P_TCDFILIAL <> 'T') OR (P_TCDFILIAL IS NULL)) AND (P_CDFILIAL IS NOT NULL)) THEN
     IF (LENGTH(P_CDFILIAL) > 6) THEN
       V_SQLFILTER:= ' WHERE INSTR(''' || P_CDFILIAL || ''', CDFILIAL) > 0 ';
     ELSE
       IF INSTR(P_CDFILIAL, '_') > 0 THEN
          V_FILIAL_UNICA:= SUBSTR(P_CDFILIAL, 2, LENGTH(P_CDFILIAL)-2);
       ELSE
          V_FILIAL_UNICA:= P_CDFILIAL;
       END IF;
       V_SQLFILTER:= ' WHERE  CDFILIAL = ''' || V_FILIAL_UNICA || '''';
     END IF;
   END IF;

   IF P_CDGRUPFILI IS NOT NULL THEN
     V_SQLF_GFILIAL:= '(SELECT CDFILIAL '     ||
                      '  FROM FILGRUPFILI '   ||
                      '  WHERE CDGRUPFILI = ''' || P_CDGRUPFILI || ''') G, ';
     V_SQLW_GFILIAL:= ' G.CDFILIAL = F.CDFILIAL AND ';
   END IF;

   V_SQLFILIAL:= 'SELECT F.CDFILIAL AS COD_FILIAL_CORRENTE, NVL(PCMP.IDUTPRODPREF, ''' || W_N || '''),' ||
                 'NVL(PCMP.IDSCONESTNEC, ''' || W_N || '''), ' ||
                 'DECODE('''||V_IDUTILDTPROC||''',  ''' || W_S || ''', NVL(PF.DTPROCESSA, TRUNC(SYSDATE)), TRUNC(SYSDATE))' ||
                 'FROM (SELECT CDFILIAL, NVL(CDFILCENTCMP, CDFILIAL) AS CDFILCENTCMP ' ||
                 '      FROM FILIAL ' || V_SQLFILTER   || ' ) F, '  || V_SQLF_GFILIAL  ||
                 '      PARCMPSUP PCMP, PARASUPRI P, PARAMFILIAL PF '  ||
                 'WHERE ' || V_SQLW_GFILIAL            ||
                 '      P.CDFILIAL     = F.CDFILCENTCMP ' ||
                 '  AND F.CDFILIAL     = PCMP.CDFILIAL '  ||
                 '  AND PF.CDFILIAL(+) = F.CDFILIAL    '||
                 '  AND ((''' || V_IDRESTRFILIOPER || ''' = ''' || W_N || ''')     '||
                 '  OR (NVL('''||P_CDOPERADOR||''', '''|| W_N || ''') = '''|| W_N || ''')  '||
                 '  OR EXISTS (SELECT 1                '||
						           '               FROM FILIOPER' ||
						           '              WHERE FILIOPER.CDFILIAL   = F.CDFILIAL'||
						           '                AND FILIOPER.CDOPERADOR = '''|| P_CDOPERADOR ||'''))';

   /* FIM -- MONTAGEM DA QUERY PARA BUSCAR FILIAIS */
   OPEN CUR_FILIAL FOR V_SQLFILIAL;
   LOOP
     FETCH CUR_FILIAL INTO V_FILIAL_CORRENTE, V_UTILIZAPRODPREF, V_IDESTOQUE, V_DTPROCESS;
     EXIT WHEN CUR_FILIAL%NOTFOUND;
        /* Calcula calend?rio de compras para as periodicidades que ainda n?o possuem */
        FOR C_PERIODNCALC IN
           (SELECT DISTINCT
               PERFILPROD.CDPERIENTR,
               TO_DATE('01/'||TO_CHAR(PREVCONGER.DTPREVCONS,'MM/YYYY'), 'DD/MM/YYYY') AS DTPREVCONS
            FROM
              (SELECT
                 PREVCONS.CDFILIAL,
                 TRUNC(PREVCONS.DTPREVCONS) AS DTPREVCONS,
                 PROD_PREV.CDPRODESTO AS CDPRODUTO,
                 PROD_PREV.CDARVPROD
               FROM PRODUTO PROD_PREV, V_PREVISAO_COMPRA PREVCONS
               WHERE PREVCONS.CDFILIAL           = V_FILIAL_CORRENTE
                 AND TRUNC(PREVCONS.DTPREVCONS) >= V_DTPROCESS
                 AND PREVCONS.CDPRODUTO = PROD_PREV.CDPRODUTO
                 AND PROD_PREV.CDARVPROD BETWEEN V_CDPRODINI AND V_CDPRODFIN

               UNION ALL

               SELECT
                 PREVCONS.CDFILIAL,
                 TRUNC(PREVCONS.DTPREVCONS) AS DTPREVCONS,
                 PROD_PREV.CDPRODESTO AS CDPRODUTO,
                 PROD_PREV.CDARVPROD
               FROM PRODUTO PROD_PREV, V_PREVISAO_COMPRA_FILIAL PREVCONS
               WHERE PREVCONS.CDFILIAL           = V_FILIAL_CORRENTE
                 AND TRUNC(PREVCONS.DTPREVCONS) >= V_DTPROCESS
                 AND PREVCONS.CDPRODUTO = PROD_PREV.CDPRODUTO
                 AND PROD_PREV.CDARVPROD BETWEEN V_CDPRODINI AND V_CDPRODFIN) PREVCONGER,
               PERFILPROD,
               PRODUTO PROD_PER
            WHERE
              (PERFILPROD.CDPRODUTO = PROD_PER.CDPRODUTO) AND
              (PERFILPROD.CDFILIAL  = V_FILIAL_CORRENTE) AND
              (PROD_PER.CDARVPROD   = (SELECT
                                          MAX(PRODUTO.CDARVPROD)
                                       FROM
                                          PERFILPROD A,
                                          PRODUTO
                                       WHERE
                                          (A.CDPRODUTO = PRODUTO.CDPRODUTO) AND
                                          (A.CDFILIAL  = V_FILIAL_CORRENTE) AND
                                          (INSTR(PREVCONGER.CDARVPROD, PRODUTO.CDARVPROD) = 1))) AND
              (PERFILPROD.CDPERIENTR NOT IN (SELECT
                                                CDPERIENTR
                                             FROM
                                                CALECOMP
                                             WHERE
                                                (CALECOMP.CDFILIAL = V_FILIAL_CORRENTE)     AND
                                                (TO_CHAR(CALECOMP.DTENTREGA, 'MM/YYYY') =
                                                 TO_CHAR(PREVCONGER.DTPREVCONS, 'MM/YYYY')) )))
        LOOP
           CALC_DATA_ENTR_MES(V_FILIAL_CORRENTE,
                              C_PERIODNCALC.CDPERIENTR,
                              C_PERIODNCALC.DTPREVCONS,
                              1,
                              V_DTPROCESS);
           SELECT COUNT(CDPERIENTR)
             INTO V_CDPERIENTR
             FROM CALECOMP
            WHERE CDFILIAL   = V_FILIAL_CORRENTE
              AND CDPERIENTR = C_PERIODNCALC.CDPERIENTR
              AND DTENTREGA BETWEEN ADD_MONTHS(C_PERIODNCALC.DTPREVCONS, -1) AND C_PERIODNCALC.DTPREVCONS-1;
           IF V_CDPERIENTR = 0 THEN
              CALC_DATA_ENTR_MES(V_FILIAL_CORRENTE,
                                 C_PERIODNCALC.CDPERIENTR,
                                 ADD_MONTHS(C_PERIODNCALC.DTPREVCONS, -1),
                                 1,
                                 V_DTPROCESS);
           END IF;
           COMMIT;
        END LOOP;
   END LOOP; -- LOOP DA FILIAL
   CLOSE CUR_FILIAL;
END;
/