CREATE OR REPLACE FUNCTION QUERY_PONTO_REPOSICAO (P_CDFILIAL       IN VARCHAR2,   /*Unidade filtrada*/
                                                  P_DTINI          IN VARCHAR2,       /*Data de Per?odo inicial*/
                                                  P_DTFIN          IN VARCHAR2,       /*Data de Per?odo Final*/
                                                  P_CDGRUPPROD     IN VARCHAR2,   /*Grupo de produto parametrizado no CMP*/
                                                  P_CDSUBGRPROD    IN VARCHAR2,   /*SubGrupo de produtos selecionado*/
                                                  P_CDPRODINI      IN VARCHAR2,   /*C?digo inicial do produto da ?rvore*/
                                                  P_CDPRODFIN      IN VARCHAR2,   /*C?digo final do produto da ?rvore*/
                                                  P_QTDIAESTOQUE   IN NUMERIC)   /*Quantidade de dias de Estoque*/
                                                  RETURN QUERY_PONTO_REPOSICAO_TABLE PIPELINED
IS
   OUT_RECORD QUERY_PONTO_REPOSICAO_TYPE := QUERY_PONTO_REPOSICAO_TYPE (NULL, NULL, NULL, NULL, NULL, NULL,
                                                                        NULL, NULL, NULL, 0, 0, 0, 0, 0, 0,
                                                                        0, 0, 0, 0, 0);
   TYPE CURTYPE         IS REF CURSOR;
   C_ITENS              CURTYPE;
   V_DTPOSIESTQ         DATE;
   V_QTESTOQDIA         FLOAT;
   V_CONTA_ITEM         NUMERIC;
   V_QTDIASPRAZOEXPED   FLOAT;
   V_QTENTRADAS         FLOAT;
   V_DTPROCESS          DATE;
   V_QTSAIDAS           FLOAT;
   V_QTACOMPRAR         FLOAT;
   V_QTPONTOREPOSICAO   FLOAT;
   V_MAX_CDARVPROD      PRODUTO.CDARVPROD%TYPE;
   V_DIASMES            FLOAT;
   V_NMFANTFORN         FORNECEDOR.NMFANTFORN%TYPE;
   V_CDFORNECED         FORNECEDOR.CDFORNECED%TYPE;
   V_CONTADOR           NUMERIC;
   V_TABELA             VARCHAR2(1);
   V_CONTAPART          NUMERIC;
   V_CDFORNECEDPART     FORNECEDOR.CDFORNECED%TYPE;
BEGIN
   SELECT NVL(MAX(CASE
                     WHEN NVL(PARAMGERAL.IDUTILDTPROC, 'N') = 'N'
                     THEN TO_DATE(TRUNC(SYSDATE))
                     ELSE NVL(PARAMFILIAL.DTPROCESSA, TO_DATE(TRUNC(SYSDATE)))
                  END), TO_DATE(TRUNC(SYSDATE)))
     INTO V_DTPROCESS
     FROM PARAMGERAL, PARAMFILIAL
    WHERE PARAMFILIAL.CDFILIAL = P_CDFILIAL;
   SELECT FLOOR(COUNT(1)/COUNT(DISTINCT TO_CHAR(DTDATA, 'MM/YYYY')))
     INTO V_DIASMES
     FROM DATA
    WHERE DTDATA BETWEEN TO_DATE('01/'||P_DTINI, 'DD/MM/YYYY') AND LAST_DAY(TO_DATE('01/'||P_DTFIN, 'DD/MM/YYYY'));
   FOR C_ITLANCTOEST IN (SELECT COUNT(DISTINCT TO_CHAR(ITLANCTOEST.DTLANCMOVI, 'MM/YYYY')) QTMESES, SUM(ITLANCTOEST.QTTOTLANCTO) QTLANCTOEST, ITLANCTOEST.CDPRODMOVI, ITLANCTOEST.CDFILIMOVI,
                                FILIAL.NMFILIAL, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, PRODUTO.CDPRODUTO,
                                PRODUTO.SGUNIDADE, V_DIASMES DIASMES
                           FROM ITLANCTOEST, PRODUTO, FILIAL
                          WHERE ITLANCTOEST.CDPRODMOVI = PRODUTO.CDPRODUTO
                            AND ITLANCTOEST.CDFILIMOVI = FILIAL.CDFILIAL
                            AND ITLANCTOEST.CDFILIMOVI = P_CDFILIAL
                            AND ITLANCTOEST.IDTIPOMOVI IN ('3', '4')
                            AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND RPAD(P_CDPRODFIN, 20, 'Z')
                            AND ITLANCTOEST.DTLANCMOVI BETWEEN TO_DATE('01/'||P_DTINI, 'DD/MM/YYYY') AND LAST_DAY(TO_DATE('01/'||P_DTFIN, 'DD/MM/YYYY'))
                            AND ((NVL(P_CDSUBGRPROD, 'W') =  'W')
                             OR (EXISTS (SELECT 1
                                           FROM PRSUBGRPROD
                                          WHERE PRSUBGRPROD.CDGRUPPROD  = P_CDGRUPPROD
                                            AND PRSUBGRPROD.CDGRUPPROD  = P_CDSUBGRPROD
                                            AND PRSUBGRPROD.CDPRODUTO   = ITLANCTOEST.CDPRODMOVI
                                            AND PRSUBGRPROD.CDFILISUBGR = 'P')))
                          GROUP BY ITLANCTOEST.CDPRODMOVI, ITLANCTOEST.CDFILIMOVI,
                                FILIAL.NMFILIAL, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, PRODUTO.CDPRODUTO,
                                PRODUTO.SGUNIDADE
                          ORDER BY PRODUTO.CDARVPROD) LOOP
      /*Posi?ao de Estoque*/
      SELECT NVL(CALC_ESTOQ_CONS(C_ITLANCTOEST.CDFILIMOVI, C_ITLANCTOEST.CDPRODMOVI, TO_DATE(TRUNC(SYSDATE + 1))), 0)
        INTO V_QTESTOQDIA
        FROM DUAL;

      /*AF?s em Aberto*/
      SELECT NVL(SUM((QTPROGENTAF - NVL(QTEFETENTRAF,0) ) * PRODUTO.VRFATOCONV), 0)
        INTO V_QTENTRADAS
        FROM AUTOFORNE, ITEMAUTF, PRODUTO
       WHERE ITEMAUTF.CDFILDEST  = AUTOFORNE.CDFILDEST
         AND ITEMAUTF.CDFILLANC  = AUTOFORNE.CDFILLANC
         AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
         AND ITEMAUTF.CDPRODUTO  = PRODUTO.CDPRODUTO
         AND ITEMAUTF.CDFILDEST  = C_ITLANCTOEST.CDFILIMOVI
         AND PRODUTO.CDPRODESTO  = C_ITLANCTOEST.CDPRODUTO
         AND NVL(ITEMAUTF.IDBAIXITMAF, '1') = '1'
         AND QTPROGENTAF - NVL(QTEFETENTRAF, 0) > 0
         AND NVL(AUTOFORNE.IDBAIXAAF, '0') NOT IN ('4', '5', '6')
         AND AUTOFORNE.IDAFFORALIM = 'N'
         AND AUTOFORNE.IDSITUATUAAF IS NULL
         AND ITEMAUTF.DTPROGENTAF BETWEEN V_DTPROCESS AND V_DTPROCESS + 999999
         AND NVL(AUTOFORNE.IDAFEXPIRADA, 'N') = 'N';
      /*Pedidos de Expedi??o em Aberto*/
      SELECT NVL(SUM(NVL(ITEMPEDIDO.QTITEMPED, 0) - NVL(ITEMPEDIDO.QTEXPITEMPED, 0)*PRODUTO.VRFATOCONV), 0)
        INTO V_QTSAIDAS
        FROM PEDIDO, ITEMPEDIDO, PRODUTO
       WHERE PEDIDO.CDFILIAL = ITEMPEDIDO.CDFILIAL
         AND PEDIDO.NRPEDIDO = ITEMPEDIDO.NRPEDIDO
         AND PEDIDO.CDFILIAL = C_ITLANCTOEST.CDFILIMOVI
         AND PEDIDO.DTENTPED BETWEEN V_DTPROCESS AND V_DTPROCESS + 999999
         AND NVL(ITEMPEDIDO.IDSITITEMPED, '1') IN ('1', '2', '3', '7')
         AND NVL(PEDIDO.IDSITPEDIDO, '1') IN ('1', '2', '6')
         AND ITEMPEDIDO.CDPRODUTO = PRODUTO.CDPRODUTO
         AND PRODUTO.CDPRODESTO   = C_ITLANCTOEST.CDPRODUTO;
      /*Verifica particularida do fornecedor*/
      BEGIN
         SELECT COUNT(1), MAX(PARTFILI.CDFORNECED)
           INTO V_CONTAPART, V_CDFORNECEDPART
           FROM PARTFILI
          WHERE PARTFILI.CDFILIAL  = C_ITLANCTOEST.CDFILIMOVI
            AND PARTFILI.CDPRODUTO = C_ITLANCTOEST.CDPRODUTO
            AND PARTFILI.CDTIPPART IN ('4', '7');
      EXCEPTION
         WHEN OTHERS THEN BEGIN
            V_CONTAPART       := 0;
            V_CDFORNECEDPART  := NULL;
         END;
      END;
      /*Grava registro independente de houver dados nas tabelas PRAZOFORNEXPEDFIL e PRAZOFORNEXPEDFILPROD*/
      V_QTPONTOREPOSICAO  := P_QTDIAESTOQUE;
      IF NVL(V_QTPONTOREPOSICAO, 0) = 0 THEN
         V_QTPONTOREPOSICAO  := 1;
      END IF;
      V_QTACOMPRAR := ((V_QTENTRADAS+V_QTESTOQDIA) -
                      ((((C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES)*V_QTPONTOREPOSICAO) + V_QTSAIDAS));
      IF V_QTACOMPRAR >= 0 THEN
         V_QTACOMPRAR := 0;
      ELSE
         V_QTACOMPRAR := ABS(V_QTACOMPRAR);
      END IF;
      OUT_RECORD.CDFILIAL             := C_ITLANCTOEST.CDFILIMOVI;
      OUT_RECORD.NMFILIAL             := C_ITLANCTOEST.NMFILIAL;
      OUT_RECORD.CDARVPROD            := C_ITLANCTOEST.CDARVPROD;
      OUT_RECORD.NMPRODUTO            := C_ITLANCTOEST.NMPRODUTO;
      OUT_RECORD.CDPRODUTO            := C_ITLANCTOEST.CDPRODUTO;
      OUT_RECORD.SGUNIDADE            := C_ITLANCTOEST.SGUNIDADE;
      OUT_RECORD.NMFANTFORN           := NULL;
      OUT_RECORD.DTOPERACAO           := TO_DATE(TRUNC(SYSDATE));
      OUT_RECORD.QTESTOQUE            := V_QTESTOQDIA;
      OUT_RECORD.QTDIAESTOQUE         := P_QTDIAESTOQUE;
      OUT_RECORD.QTDIASPRAZOEXPED     := 0;
      OUT_RECORD.QTPONTOREPOSICAO     := V_QTPONTOREPOSICAO;
      OUT_RECORD.QTGERAL              := C_ITLANCTOEST.QTLANCTOEST;
      OUT_RECORD.QTMEDIA              := (C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES;
      OUT_RECORD.QTMEDIATOTAL         := ((C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES)*V_QTPONTOREPOSICAO;
      OUT_RECORD.QTENTRADAS           := V_QTENTRADAS;
      OUT_RECORD.QTSAIDAS             := V_QTSAIDAS;
      OUT_RECORD.QTACOMPRAR           := V_QTACOMPRAR;
      OUT_RECORD.CONTAFORNECEDOR      := 0;
      OUT_RECORD.IDTIPPART            := 'N';
      /*Fim da grava??o de registro independente de houver dados nas tabelas PRAZOFORNEXPEDFIL e PRAZOFORNEXPEDFILPROD*/
      BEGIN
         SELECT NVL(MAX(PRODUTO.CDARVPROD), 'WWW')
           INTO V_MAX_CDARVPROD
           FROM PRODUTO, PRAZOFORNEXPEDFILPROD
          WHERE PRODUTO.CDPRODUTO = PRAZOFORNEXPEDFILPROD.CDPRODUTO
            AND PRAZOFORNEXPEDFILPROD.CDFILIAL = C_ITLANCTOEST.CDFILIMOVI
            AND (INSTR(C_ITLANCTOEST.CDARVPROD, PRODUTO.CDARVPROD) = 1);
      EXCEPTION
         WHEN OTHERS THEN BEGIN
            V_MAX_CDARVPROD := 'WWW';
         END;
      END;
      V_QTDIASPRAZOEXPED := 0;
      V_CONTA_ITEM       := 0;
      OPEN C_ITENS FOR
         SELECT CDFORNECED, NMFANTFORN, QTDIASPRAZOEXPED, COUNT(CDFORNECED) OVER (PARTITION BY CDFORNECED) CONTADOR, TABELA
           FROM (SELECT FORNECEDOR.CDFORNECED, FORNECEDOR.NMFANTFORN, PRAZOFORNEXPEDFIL.QTDIASPRAZOEXPED, 'F' TABELA
                   FROM PRAZOFORNEXPEDFIL,PRODFORNCOMP, FORNECEDOR
                  WHERE PRAZOFORNEXPEDFIL.CDFORNECED = FORNECEDOR.CDFORNECED
                    AND PRAZOFORNEXPEDFIL.CDFILIAL   = C_ITLANCTOEST.CDFILIMOVI
                    AND NVL(PRAZOFORNEXPEDFIL.QTDIASPRAZOEXPED, 0) > 0
                    AND FORNECEDOR.CDFORNECED        = PRODFORNCOMP.CDFORNECED
                    AND PRODFORNCOMP.CDPRODCOMPL     = C_ITLANCTOEST.CDARVPROD
                  UNION
                 SELECT FORNECEDOR.CDFORNECED, FORNECEDOR.NMFANTFORN, PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED, 'P' TABELA
                   FROM PRAZOFORNEXPEDFILPROD, PRODUTO, FORNECEDOR
                  WHERE PRAZOFORNEXPEDFILPROD.CDFORNECED = FORNECEDOR.CDFORNECED
                    AND PRAZOFORNEXPEDFILPROD.CDFILIAL   = C_ITLANCTOEST.CDFILIMOVI
                    AND PRAZOFORNEXPEDFILPROD.CDPRODUTO  = PRODUTO.CDPRODUTO
                    AND NVL(PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED, 0) > 0
                    AND PRODUTO.CDARVPROD                = V_MAX_CDARVPROD
          ORDER BY NMFANTFORN);
      LOOP
      FETCH C_ITENS INTO V_CDFORNECED, V_NMFANTFORN, V_QTDIASPRAZOEXPED, V_CONTADOR, V_TABELA;
      EXIT WHEN C_ITENS%NOTFOUND;
         IF ((V_CONTADOR = 1) OR (V_CONTADOR = 2 AND  V_TABELA = 'P')) THEN
            V_CONTA_ITEM := V_CONTA_ITEM + 1;
            IF P_QTDIAESTOQUE > V_QTDIASPRAZOEXPED THEN
               V_QTPONTOREPOSICAO  := P_QTDIAESTOQUE;
            ELSE
               V_QTPONTOREPOSICAO  := V_QTDIASPRAZOEXPED;
            END IF;
            IF NVL(V_QTPONTOREPOSICAO, 0) = 0 THEN
               V_QTPONTOREPOSICAO  := 1;
            END IF;
            V_QTACOMPRAR := ((V_QTENTRADAS+V_QTESTOQDIA) -
                            ((((C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES)*V_QTPONTOREPOSICAO) + V_QTSAIDAS));
            IF V_QTACOMPRAR >= 0 THEN
               V_QTACOMPRAR := 0;
            ELSE
               V_QTACOMPRAR := ABS(V_QTACOMPRAR);
            END IF;
            OUT_RECORD.CDFILIAL             := C_ITLANCTOEST.CDFILIMOVI;
            OUT_RECORD.NMFILIAL             := C_ITLANCTOEST.NMFILIAL;
            OUT_RECORD.CDARVPROD            := C_ITLANCTOEST.CDARVPROD;
            OUT_RECORD.NMPRODUTO            := C_ITLANCTOEST.NMPRODUTO;
            OUT_RECORD.CDPRODUTO            := C_ITLANCTOEST.CDPRODUTO;
            OUT_RECORD.SGUNIDADE            := C_ITLANCTOEST.SGUNIDADE;
            OUT_RECORD.NMFANTFORN           := V_NMFANTFORN;
            OUT_RECORD.DTOPERACAO           := TO_DATE(TRUNC(SYSDATE));
            OUT_RECORD.QTESTOQUE            := V_QTESTOQDIA;
            OUT_RECORD.QTDIAESTOQUE         := P_QTDIAESTOQUE;
            OUT_RECORD.QTDIASPRAZOEXPED     := V_QTDIASPRAZOEXPED;
            OUT_RECORD.QTPONTOREPOSICAO     := V_QTPONTOREPOSICAO;
            OUT_RECORD.QTGERAL              := C_ITLANCTOEST.QTLANCTOEST;
            OUT_RECORD.QTMEDIA              := (C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES;
            OUT_RECORD.QTMEDIATOTAL         := ((C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)/C_ITLANCTOEST.DIASMES)*V_QTPONTOREPOSICAO;
            OUT_RECORD.QTENTRADAS           := V_QTENTRADAS;
            OUT_RECORD.QTSAIDAS             := V_QTSAIDAS;
            OUT_RECORD.QTACOMPRAR           := V_QTACOMPRAR;
            OUT_RECORD.CONTAFORNECEDOR      := V_CONTADOR;
            IF (V_CONTAPART > 0 AND  V_CDFORNECEDPART = V_CDFORNECED) THEN
               OUT_RECORD.IDTIPPART         := 'S';
            ELSE
               OUT_RECORD.IDTIPPART         := 'N';
            END IF;
            PIPE ROW (OUT_RECORD);
         END IF;
      END LOOP;
      IF V_CONTA_ITEM = 0 THEN
         PIPE ROW (OUT_RECORD);
      END IF;
   END LOOP;
END;
/