DROP TYPE QUERY_PONTO_REPOSICAO_TABLE
/

DROP TYPE QUERY_PONTO_REPOSICAO_TYPE
/

CREATE TYPE QUERY_PONTO_REPOSICAO_TYPE AS
OBJECT(CDFILIAL          VARCHAR2(4),
       NMFILIAL          VARCHAR2(30),
       CDARVPROD         VARCHAR2(20),
       NMPRODUTO         VARCHAR2(50),
       CDPRODUTO         VARCHAR2(10),
       SGUNIDADE         VARCHAR2(10),
       DTOPERACAO        DATE,
       QTDIAESTOQUE      FLOAT,
       QTDIASPRAZOEXPED  FLOAT,
       QTPONTOREPOSICAO  FLOAT,
       QTMEDIA           FLOAT,
       QTMEDIATOTAL      FLOAT,
       QTGERAL           FLOAT,
       QTESTOQUE         FLOAT,
       QTENTRADAS        FLOAT,
       QTSAIDAS          FLOAT,
       QTACOMPRAR        FLOAT);
/

CREATE TYPE QUERY_PONTO_REPOSICAO_TABLE AS TABLE OF QUERY_PONTO_REPOSICAO_TYPE
/

CREATE OR REPLACE FUNCTION QUERY_PONTO_REPOSICAO (P_CDFILIAL       IN VARCHAR2,   /*Unidade filtrada*/
                                                  P_DTINI          IN VARCHAR2,       /*Data de Periodo inicial*/
                                                  P_DTFIN          IN VARCHAR2,       /*Data de Periodo Final*/
                                                  P_CDGRUPPROD     IN VARCHAR2,   /*Grupo de produto parametrizado no CMP*/
                                                  P_CDSUBGRPROD    IN VARCHAR2,   /*SubGrupo de produtos selecionado*/
                                                  P_CDPRODINI      IN VARCHAR2,   /*Codigo inicial do produto da arvore*/
                                                  P_CDPRODFIN      IN VARCHAR2,   /*Codigo final do produto da arvore*/
                                                  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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
   V_DTPOSIESTQ         DATE;
   V_QTESTOQDIA         FLOAT;
   V_CONTA_FORN         FLOAT;
   V_QTDIASPRAZOEXPED   FLOAT;
   V_QTENTRADAS         FLOAT;
   V_DTPROCESS          DATE;
   V_QTSAIDAS           FLOAT;
   V_QTACOMPRAR         FLOAT;
   V_QTPONTOREPOSICAO   FLOAT;
   V_MAX_CDARVPROD      PRODUTO.CDARVPROD%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;
   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
                           FROM ITLANCTOEST, PRODUTO, FILIAL
                          WHERE ITLANCTOEST.CDPRODMOVI = PRODUTO.CDPRODUTO
                            AND ITLANCTOEST.CDFILIMOVI = FILIAL.CDFILIAL
                            AND ITLANCTOEST.CDFILIMOVI = P_CDFILIAL
                            AND ITLANCTOEST.IDTIPOMOVI = '3'
                            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
      SELECT NVL(MAX(POSESTDIA.DTPOSIESTQ), SYSDATE+1)
        INTO V_DTPOSIESTQ
        FROM POSESTDIA
       WHERE POSESTDIA.CDFILIAL      = C_ITLANCTOEST.CDFILIMOVI
         AND POSESTDIA.CDALMOXARIFE  = ' '
         AND POSESTDIA.CDLOCALESTOQ  = ' '
         AND POSESTDIA.CDPRODUTO     = C_ITLANCTOEST.CDPRODUTO
         AND POSESTDIA.NRLOTEESTQ    = ' '
         AND POSESTDIA.NRSUBLOTE     = ' ';
      SELECT NVL(CALC_ESTOQ_CONS(C_ITLANCTOEST.CDFILIMOVI, C_ITLANCTOEST.CDPRODUTO, NVL(SYSDATE, V_DTPOSIESTQ)), 0)
        INTO V_QTESTOQDIA
        FROM DUAL;

      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_FORN       := 0;
      IF V_MAX_CDARVPROD <> 'WWW' THEN
         BEGIN
            SELECT COUNT(PRAZOFORNEXPEDFILPROD.CDFORNECED), NVL(MAX(PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED), 0)
              INTO V_CONTA_FORN, V_QTDIASPRAZOEXPED
              FROM PRAZOFORNEXPEDFILPROD, PRODUTO
             WHERE PRAZOFORNEXPEDFILPROD.CDFILIAL  = C_ITLANCTOEST.CDFILIMOVI
               AND PRAZOFORNEXPEDFILPROD.CDPRODUTO = PRODUTO.CDPRODUTO
               AND PRODUTO.CDARVPROD               = V_MAX_CDARVPROD
               AND PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED >= 1
            HAVING COUNT(CDFORNECED)  > 1;
         EXCEPTION
            WHEN OTHERS THEN BEGIN
               V_CONTA_FORN       := 0;
               V_QTDIASPRAZOEXPED := 0;
            END;
         END;
         BEGIN
            IF V_CONTA_FORN > 0 THEN
               SELECT NVL(MAX(PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED), 0)
                 INTO V_QTDIASPRAZOEXPED
                 FROM PRAZOFORNEXPEDFILPROD, PARTFILI, PRODUTO, PRODUTO P
                WHERE PRAZOFORNEXPEDFILPROD.CDFILIAL  = C_ITLANCTOEST.CDFILIMOVI
                  AND PRAZOFORNEXPEDFILPROD.CDPRODUTO = PRODUTO.CDPRODUTO
                  AND PRODUTO.CDARVPROD               = V_MAX_CDARVPROD
                  AND PRAZOFORNEXPEDFILPROD.CDFILIAL  = PARTFILI.CDFILIAL
                  AND P.CDPRODUTO                     = PARTFILI.CDPRODUTO
                  AND P.CDARVPROD LIKE PRODUTO.CDARVPROD||'%'
                  AND PARTFILI.CDTIPPART IN ('4', '5', '7','8')
                  AND PRAZOFORNEXPEDFILPROD.QTDIASPRAZOEXPED >= 1;
            END IF;
         EXCEPTION
            WHEN OTHERS THEN BEGIN
               V_QTDIASPRAZOEXPED := 0;
            END;
         END;
      END IF;
      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';

      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;
      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)*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.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;
      OUT_RECORD.QTMEDIATOTAL         := (C_ITLANCTOEST.QTLANCTOEST / C_ITLANCTOEST.QTMESES)*V_QTPONTOREPOSICAO;
      OUT_RECORD.QTENTRADAS           := V_QTENTRADAS;
      OUT_RECORD.QTSAIDAS             := V_QTSAIDAS;
      OUT_RECORD.QTACOMPRAR           := V_QTACOMPRAR;
      PIPE ROW (OUT_RECORD);
   END LOOP;
END;
/