DROP FUNCTION BLOCOK_PROD_REAL_FILHO
/

DROP TYPE TABELA_BLOCOK_PROD_REAL_FILHO
/

DROP TYPE LINHA_BLOCOK_PROD_REAL_FILHO
/

CREATE TYPE LINHA_BLOCOK_PROD_REAL_FILHO AS OBJECT
   (CDARVPROD VARCHAR2(20),
    CDPRODUTO VARCHAR2(10),
    QTPREVISRETI NUMBER,
    QTEXTRARETI NUMBER,
    QTDEVOLVRETI NUMBER,
    QTRETIRADA NUMBER,
    QTTOTLANCTO NUMBER
    )
/

CREATE TYPE TABELA_BLOCOK_PROD_REAL_FILHO AS TABLE OF LINHA_BLOCOK_PROD_REAL_FILHO
/

CREATE OR REPLACE FUNCTION BLOCOK_PROD_REAL_FILHO
   (P_CDINSCESTA IN VARCHAR2,
    P_CDEMPRESA IN VARCHAR2,
    P_DTINICIAL IN DATE,
    P_DTFINAL IN DATE,
    P_DTRETIRADA IN DATE,
    P_CDFILIAL IN VARCHAR2,
    P_CDSERVFILI IN VARCHAR2,
    P_CDFILANCESTQ IN VARCHAR2,
    P_NRLANCESTQ IN VARCHAR2,
    P_LIVROMAG IN VARCHAR2
   ) RETURN TABELA_BLOCOK_PROD_REAL_FILHO PIPELINED

   IS

   V_OUT_RECORD LINHA_BLOCOK_PROD_REAL_FILHO := LINHA_BLOCOK_PROD_REAL_FILHO (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

BEGIN

   FOR C_ITEM IN(SELECT IR.CDPRODUTO, PR.CDARVPROD,
                        SUM(IR.QTPREVISRETI) AS QTPREVISRETI,
                        SUM(IR.QTEXTRARETI)  AS QTEXTRARETI,
                        SUM(IR.QTDEVOLVRETI) AS QTDEVOLVRETI,
                        SUM(IR.QTRETIRADA)   AS QTRETIRADA,
                        SUM(IT.QTTOTLANCTO)  AS QTTOTLANCTO
                   FROM ITLANCTOEST IT, ITEMRETI IR, RETIRADA RE, CARDADIA CA, PRODUTO PR, RELACPRODITGEN R, TIPOITEMBLOCOK T
                  WHERE CA.CDFILIAL       = P_CDFILIAL
                    AND CA.CDSERVFILI     = P_CDSERVFILI
                    AND RE.DTRETIRADA     = P_DTRETIRADA
                    AND RE.CDFILANCESTQ   = P_CDFILANCESTQ
                    AND RE.NRLANCESTQ     = P_NRLANCESTQ
                    AND RE.CDFILIAL       = CA.CDFILIAL
                    AND RE.NRCARDAPIO     = CA.NRCARDAPIO
                    AND IR.CDFILIAL       = RE.CDFILIAL
                    AND IR.NRCARDAPIO     = RE.NRCARDAPIO
                    AND IR.NRRETIRADA     = RE.NRRETIRADA
                    AND IR.CDFIITLANEST   = IT.CDFILIAL
                    AND IR.NRLANCESTQ     = IT.NRLANCESTQ
                    AND IR.NRSEQUITEM     = IT.NRSEQUITEM
                    AND IT.DTLANCMOVI     = RE.DTRETIRADA
                    AND IT.IDEVOLESTOQ    = 'S'
                    AND PR.CDPRODUTO      = IR.CDPRODUTO
                    AND IR.CDPRODUTO      = R.CDPRODUTO
                    AND R.CDLIVROMAG      = P_LIVROMAG
                    AND R.CDTPITEMPROD    = T.CDTPITEMPROD
                    AND T.IDITEMCONSUMIDO = 'S'
                  GROUP BY IR.CDPRODUTO, PR.CDARVPROD) LOOP

      V_OUT_RECORD.CDARVPROD    := C_ITEM.CDARVPROD;
      V_OUT_RECORD.CDPRODUTO    := C_ITEM.CDPRODUTO;
      V_OUT_RECORD.QTPREVISRETI := C_ITEM.QTPREVISRETI;
      V_OUT_RECORD.QTEXTRARETI  := C_ITEM.QTEXTRARETI;
      V_OUT_RECORD.QTDEVOLVRETI := C_ITEM.QTDEVOLVRETI;
      V_OUT_RECORD.QTRETIRADA   := C_ITEM.QTRETIRADA;
      V_OUT_RECORD.QTTOTLANCTO  := C_ITEM.QTTOTLANCTO;

      PIPE ROW (V_OUT_RECORD);

   END LOOP;

   RETURN;
END;
/