DROP FUNCTION BLOCOK_PROD_REAL_PAI
/

DROP TYPE TABELA_BLOCOK_PROD_REAL_PAI
/

DROP TYPE LINHA_BLOCOK_PROD_REAL_PAI
/

CREATE TYPE LINHA_BLOCOK_PROD_REAL_PAI  AS OBJECT
   (CDPRODACABADO VARCHAR2(10),
    CDARVPRODACAB VARCHAR2(20),
    QTEFETIVOS NUMBER,
    NRPRODUCAO VARCHAR2(14),
    DTRETIRADA DATE,
    CDFILIAL VARCHAR2(04),
    CDSERVFILI VARCHAR2(05),
    CDFILANCESTQ VARCHAR2(04),
    NRLANCESTQ VARCHAR2(10)
    )
/

CREATE TYPE TABELA_BLOCOK_PROD_REAL_PAI AS TABLE OF LINHA_BLOCOK_PROD_REAL_PAI
/

CREATE OR REPLACE FUNCTION BLOCOK_PROD_REAL_PAI
   (P_CDINSCESTA IN VARCHAR2,
    P_CDEMPRESA IN VARCHAR2,
    P_DTINICIAL IN DATE,
    P_DTFINAL IN DATE,
    P_LIVROMAG IN VARCHAR2
   ) RETURN TABELA_BLOCOK_PROD_REAL_PAI PIPELINED

   IS

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

BEGIN

   FOR C_PROD IN(SELECT SE.CDFILIAL, SE.CDSERVFILI, SE.CDPRODUTO AS CDPRODACABADO, PR.CDARVPROD AS CDARVPRODACAB,
                        RE.DTRETIRADA, CA.QTCLIEFETCAR, RE.CDFILIAL||LA.NRLANCESTQ AS NRPRODUCAO, RE.CDFILANCESTQ, LA.NRLANCESTQ
                   FROM SERVICO SE, FILIAL FI, CARDADIA CA, RETIRADA RE, LANCTOESTOQ LA, PRODUTO PR, RELACPRODITGEN R, TIPOITEMBLOCOK T
                  WHERE FI.CDINSCESTA   = P_CDINSCESTA
                    AND FI.CDEMPRESA    = P_CDEMPRESA
                    AND FI.CDFILIAL     = SE.CDFILIAL
                    AND SE.IDSERVATIV   = 'S'
                    AND SE.CDFILIAL     = CA.CDFILIAL
                    AND SE.CDSERVFILI   = CA.CDSERVFILI
                    AND CA.DTCARDAPIO   BETWEEN P_DTINICIAL AND P_DTFINAL
                    AND RE.CDFILIAL     = CA.CDFILIAL
                    AND RE.NRCARDAPIO   = CA.NRCARDAPIO
                    AND LA.IDTPLANCTO   = '3'
                    AND RE.CDFILIAL     = LA.CDFILIMOVI
                    AND RE.CDFILANCESTQ = LA.CDFILIAL
                    AND RE.NRLANCESTQ   = LA.NRLANCESTQ
                    AND SE.CDPRODUTO    = PR.CDPRODUTO
                    AND SE.CDPRODUTO    = R.CDPRODUTO
                    AND R.CDLIVROMAG    = P_LIVROMAG
                    AND R.CDTPITEMPROD  = T.CDTPITEMPROD
                    AND T.IDITEMPRODUZIDO = 'S'
                  ORDER BY 1,2,3, 4) LOOP

      V_OUT_RECORD.CDPRODACABADO := C_PROD.CDPRODACABADO;
      V_OUT_RECORD.CDARVPRODACAB := C_PROD.CDARVPRODACAB;
      V_OUT_RECORD.QTEFETIVOS    := C_PROD.QTCLIEFETCAR;
      V_OUT_RECORD.NRPRODUCAO    := C_PROD.NRPRODUCAO;
      V_OUT_RECORD.DTRETIRADA    := C_PROD.DTRETIRADA;

      V_OUT_RECORD.CDFILIAL      := C_PROD.CDFILIAL;
      V_OUT_RECORD.CDSERVFILI    := C_PROD.CDSERVFILI;
      V_OUT_RECORD.CDFILANCESTQ  := C_PROD.CDFILANCESTQ;
      V_OUT_RECORD.NRLANCESTQ    := C_PROD.NRLANCESTQ;

      PIPE ROW (V_OUT_RECORD);
   END LOOP;

   RETURN;
END;
/