DROP TYPE QUERY_ITEM_GER15900_TABLE
/

DROP TYPE QUERY_ITEM_GER15900_TYPE
/

CREATE TYPE QUERY_ITEM_GER15900_TYPE AS
OBJECT(CDFILSOLI         VARCHAR2(100),
       CDPRODUTO         VARCHAR2(10),
       NRSOLICMP         VARCHAR2(20),
       CDALMOXARIFE      VARCHAR2(6),
       CDLOCALESTOQ      VARCHAR2(6),
       CDARVPROD         VARCHAR2(20),
       NMPRODUTO         VARCHAR2(50),
       NMFILIAL          VARCHAR2(30),
       SGUNIDADE         VARCHAR2(10),
       CDFILSOLCONS      VARCHAR2(10),
       NRLOTEESTQ        VARCHAR2(40),
       IDASTERISCO       VARCHAR2(1),
       NRSUBLOTE         VARCHAR2(4),
       NMFILIALCONS      VARCHAR2(30),
       CDPRODINI         VARCHAR2(20),
       CDPRODFIN         VARCHAR2(20),
       DTINI             DATE,
       DTFIN             DATE,
       DTPROGENTR        DATE,
       DTVALILOTE        DATE,
       QTSEPARAR         FLOAT,
       QTESTOQDIA        FLOAT,
       QTSOLIPROD        FLOAT,
       QTESTRETIRAR      FLOAT,
       NRORG             NUMERIC);
/

CREATE TYPE QUERY_ITEM_GER15900_TABLE AS TABLE OF QUERY_ITEM_GER15900_TYPE
/

CREATE OR REPLACE FUNCTION QUERY_ITEM_GER15900(P_CDFILIAL        IN VARCHAR2,   /*P_CDFILIAL*/
                                               P_LISTASOLIC      IN VARCHAR2,   /*P_LISTASOLIC*/
                                               P_TODASSOLIC      IN VARCHAR2,   /*P_TODASSOLIC*/
                                               P_CDARVPRODINI    IN VARCHAR2,   /*P_CDARVPRODINI*/
                                               P_CDARVPRODFIN    IN VARCHAR2,   /*P_CDARVPRODFIN*/
                                               P_TCDARVPROD      IN VARCHAR2,   /*P_TCDARVPROD*/
                                               P_DTINI           IN DATE,       /*P_DTINI*/
                                               P_DTFIN           IN DATE,       /*P_DTINI*/
                                               P_CDFILSOLCONS    IN VARCHAR2 DEFAULT NULL, /*P_CDFILSOLCONS*/
                                               P_CDOPERADOR      IN VARCHAR2 DEFAULT NULL /*P_CDOPERADOR*/
                                               ) RETURN QUERY_ITEM_GER15900_TABLE PIPELINED
IS
   OUT_RECORD QUERY_ITEM_GER15900_TYPE := QUERY_ITEM_GER15900_TYPE (NULL, NULL, NULL, NULL, NULL,
                                                                    NULL, NULL, NULL, NULL, NULL,
                                                                    NULL, NULL, NULL, NULL, NULL,
                                                                    NULL, NULL, NULL, NULL, NULL,
                                                                    0, 0, 0, 0, 0);
   V_CHAVE        VARCHAR2(20) := '_';
   V_QTSEPARAR    FLOAT;
   V_QTESTOQDIA   FLOAT;
   V_QTESTRETIRAR FLOAT;
   V_DTPOSIESTQ   DATE;
   V_IDRESTRFILIOPER    PARAMGERAL.IDRESTRFILIOPER%TYPE;
BEGIN
   SELECT NVL(MAX(PARAMGERAL.IDRESTRFILIOPER), 'N')
     INTO V_IDRESTRFILIOPER
     FROM PARAMGERAL;
   FOR C_ITEM IN (SELECT IT.CDFILSOLI|| ' | '||FL.NMFILIAL CDFILSOLI, IT.CDPRODUTO, IT.NRSOLICMP, IT.DTPROGENTR,
                         PR.CDARVPROD, PR.NMPRODUTO, P_DTINI DTINI, P_DTFIN DTFIN,
                         FL.NMFILIAL, PR.SGUNIDADE, IT.QTSOLIPROD, IT.CDFILSOLCONS,
                         FC.NMFILIAL NMFILIALCONS, PR.CDPRODESTO, PC.NMPRODUTO NMPRODESTO,
                         PC.CDARVPROD CDARVPRODEST, PC.SGUNIDADE  SGUNIDEST, PR.VRFATOCONV,
                         PR.NRORG
                    FROM ITEMSOLI IT, PRODUTO PR, FILIAL FL, FILIAL FC, PRODUTO PC
                   WHERE (IT.CDPRODUTO  = PR.CDPRODUTO  )
                     AND (IT.CDFILSOLI  = FL.CDFILIAL   )
                     AND (PR.CDPRODESTO = PC.CDPRODUTO  )
                     AND ((INSTR('_'||P_CDFILIAL||'_', IT.CDFILSOLI) > 0) OR (P_CDFILIAL = 'T'))
                     AND (IT.IDITMCONS  = 'S'           )
                     AND (IT.CDFILSOLCONS = FC.CDFILIAL)
                     AND (DTPROGENTR  BETWEEN P_DTINI AND P_DTFIN)
                     AND (IT.CDFILSOLCONS =   NVL(P_CDFILSOLCONS, IT.CDFILSOLCONS))
                     AND (INSTR(P_LISTASOLIC, '_'||CDFILLAN||CDFILSOLI||NRSOLICMP||'_' )>0 OR
                          'T'= P_TODASSOLIC)
                     AND ((PC.CDARVPROD BETWEEN P_CDARVPRODINI AND RPAD(P_CDARVPRODFIN, 20, 'Z') )
                           OR (P_TCDARVPROD = 'T'))
                     AND (((V_IDRESTRFILIOPER = 'N' ) OR (P_CDOPERADOR IS NULL))
                      OR EXISTS (SELECT 1
                                    FROM FILIOPER
                                   WHERE FILIOPER.CDOPERADOR = P_CDOPERADOR
                                     AND FILIOPER.CDFILIAL = FL.CDFILIAL))
                  ORDER BY IT.CDFILSOLCONS, PR.CDPRODESTO, IT.DTPROGENTR, IT.CDFILSOLI)
   LOOP
      IF V_CHAVE <> C_ITEM.CDFILSOLCONS||'-'||C_ITEM.CDPRODESTO THEN
         V_QTESTOQDIA   := 0;
         V_QTSEPARAR    := 0;
         V_QTESTRETIRAR := 0;
         V_CHAVE := C_ITEM.CDFILSOLCONS||'-'||C_ITEM.CDPRODESTO;
         SELECT NVL(MAX(POSESTCONS.DTPOSESTCON), SYSDATE+1)
           INTO V_DTPOSIESTQ
           FROM POSESTCONS
          WHERE POSESTCONS.CDFILIAL      = C_ITEM.CDFILSOLCONS
            AND POSESTCONS.CDPRODUTO     = C_ITEM.CDPRODESTO;
         SELECT NVL(CALC_ESTOQ_CONS(C_ITEM.CDFILSOLCONS, C_ITEM.CDPRODESTO, V_DTPOSIESTQ), 0)
           INTO V_QTESTOQDIA
           FROM DUAL;
         V_QTESTRETIRAR := V_QTESTOQDIA;
      END IF;
      IF V_QTESTOQDIA <= 0 THEN
         V_QTSEPARAR     := 0;
         V_QTESTRETIRAR  := 0;
      ELSE
         IF (V_QTESTRETIRAR > (C_ITEM.QTSOLIPROD*C_ITEM.VRFATOCONV)) THEN
            V_QTESTRETIRAR := (V_QTESTRETIRAR - (C_ITEM.QTSOLIPROD*C_ITEM.VRFATOCONV));
            V_QTSEPARAR    := C_ITEM.QTSOLIPROD*C_ITEM.VRFATOCONV;
         ELSE
            V_QTSEPARAR := 0;
         END IF;
      END IF;
      OUT_RECORD.CDFILSOLI      := C_ITEM.CDFILSOLI;
      OUT_RECORD.CDPRODUTO      := C_ITEM.CDPRODESTO;
      OUT_RECORD.NRSOLICMP      := C_ITEM.NRSOLICMP;
      OUT_RECORD.CDALMOXARIFE   := NULL;
      OUT_RECORD.CDLOCALESTOQ   := NULL;
      OUT_RECORD.CDARVPROD      := C_ITEM.CDARVPRODEST;
      OUT_RECORD.NMPRODUTO      := C_ITEM.NMPRODESTO;
      OUT_RECORD.NMFILIAL       := C_ITEM.NMFILIAL;
      OUT_RECORD.SGUNIDADE      := C_ITEM.SGUNIDEST;
      OUT_RECORD.CDFILSOLCONS   := C_ITEM.CDFILSOLCONS;
      OUT_RECORD.NRLOTEESTQ     := NULL;
      OUT_RECORD.IDASTERISCO    := NULL;
      OUT_RECORD.NRSUBLOTE      := NULL;
      OUT_RECORD.NMFILIALCONS   := C_ITEM.NMFILIALCONS;
      OUT_RECORD.CDPRODINI      := P_CDARVPRODINI;
      OUT_RECORD.CDPRODFIN      := P_CDARVPRODFIN;
      OUT_RECORD.DTINI          := C_ITEM.DTINI;
      OUT_RECORD.DTFIN          := C_ITEM.DTFIN;
      OUT_RECORD.DTPROGENTR     := C_ITEM.DTPROGENTR;
      OUT_RECORD.DTVALILOTE     := NULL;
      OUT_RECORD.QTESTOQDIA     := V_QTESTOQDIA;
      OUT_RECORD.QTESTRETIRAR      := 0;
      OUT_RECORD.QTSEPARAR      := V_QTSEPARAR;
      OUT_RECORD.QTSOLIPROD     := C_ITEM.QTSOLIPROD*C_ITEM.VRFATOCONV;
      OUT_RECORD.NRORG          := C_ITEM.NRORG;
      IF V_QTSEPARAR = 0 THEN
         OUT_RECORD.QTESTRETIRAR   := 0;
      ELSE
         OUT_RECORD.QTESTRETIRAR   := V_QTESTRETIRAR;
      END IF;
      PIPE ROW (OUT_RECORD);
   END LOOP;
END;
/