DROP FUNCTION BLOCOK_ESTOQUE
/

DROP TYPE TABELA_BLOCOK_ESTOQUE
/

DROP TYPE LINHA_BLOCOK_ESTOQUE
/

CREATE TYPE LINHA_BLOCOK_ESTOQUE  AS OBJECT
   (CDPRODUTO      VARCHAR2(10),
    CDARVPROD      VARCHAR2(20),
    NRINSJUR       VARCHAR2(20),
    IDPARTICIPANTE VARCHAR2(01),
    PARTICIPANTE   VARCHAR2(21),
    QTD            NUMBER,
    IND_EST        VARCHAR2(01)
    )
/

CREATE TYPE TABELA_BLOCOK_ESTOQUE AS TABLE OF LINHA_BLOCOK_ESTOQUE
/

CREATE OR REPLACE FUNCTION BLOCOK_ESTOQUE
   (P_CDEMPRESA  IN VARCHAR2,
    P_CDINSCESTA IN VARCHAR2,
    P_DTPOSICAO  IN DATE,
    P_CDLIVROMAG IN VARCHAR2
   ) RETURN TABELA_BLOCOK_ESTOQUE PIPELINED

IS

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

BEGIN

   FOR C_PROD IN(SELECT B.CDPRODUTO, A.CDARVPROD, FC.NRINSJUR, FC.PARTICIPANTE, FC.IDPARTICIPANTE,
                     SUM(B.QTESTOQDIA) AS QTD, CASE WHEN NVL(FC.CDALMOXINFO,  'X') <> 'X' THEN '1'
                                                    WHEN NVL(FC.CDALMOXTERC,  'X') <> 'X' THEN '2'
                                                    WHEN NVL(FC.CDALMOXARIFE, 'X') <> 'X' THEN '2'
                                                    ELSE '0' END IND_EST
                FROM ALMOXARIFADO AL, FILIAL F, LOCALESTOQUE LE, RELACPRODITGEN R, TIPOITEMBLOCOK T, PRODUTO A,
                     POSESTDIA B LEFT JOIN (SELECT A.CDFILIAL, A.CDCLIENTE AS PARTICIPANTE, 'C' AS IDPARTICIPANTE, A.CDALMOXARIFE,
                                                   A.CDLOCALESTOQ, C.NRINSJURCLIE AS NRINSJUR,
                                                   A.CDALMOXTERCCLI CDALMOXTERC, A.CDLOCALTERCCLI CDLOCALTERC,
                                                   A.CDALMOXINFOCLI CDALMOXINFO, A.CDLOCALINFOCLI CDLOCALINFO
                                              FROM AGENTETERCECLI A, CLIENTE C
                                             WHERE A.CDCLIENTE  = C.CDCLIENTE
                                             UNION
                                            SELECT A.CDFILIAL, A.CDFORNECED AS PARTICIPANTE, 'F' AS IDPARTICIPANTE, A.CDALMOXARIFE,
                                                   A.CDLOCALESTOQ, C.NRINSJURFORN AS NRINSJUR,
                                                   A.CDALMOXTERCFOR CDALMOXTERC, A.CDLOCALTERCFOR CDLOCALTERC,
                                                   A.CDALMOXINFOFOR CDALMOXINFO, A.CDLOCALINFOFOR CDLOCALINFO
                                              FROM AGENTETERCEFOR A, FORNECEDOR C
                                             WHERE A.CDFORNECED = C.CDFORNECED) FC ON (FC.CDFILIAL       = B.CDFILIAL AND
                                                                                       ((FC.CDALMOXARIFE = B.CDALMOXARIFE AND
                                                                                         FC.CDLOCALESTOQ = B.CDLOCALESTOQ) OR
                                                                                        (FC.CDALMOXTERC  = B.CDALMOXARIFE AND
                                                                                         FC.CDLOCALTERC  = B.CDLOCALESTOQ) OR
                                                                                        (FC.CDALMOXINFO  = B.CDALMOXARIFE AND
                                                                                         FC.CDLOCALINFO  = B.CDLOCALESTOQ)))
               WHERE ABS(B.QTESTOQDIA) > 0.00001
                 AND B.CDFILIAL      = F.CDFILIAL
                 AND F.CDEMPRESA     = P_CDEMPRESA
                 AND F.CDINSCESTA    = P_CDINSCESTA
                 AND B.CDFILIAL      = AL.CDFILIAL
                 AND B.CDALMOXARIFE  = AL.CDALMOXARIFE
                 AND LE.CDFILIAL     = B.CDFILIAL
                 AND LE.CDALMOXARIFE = B.CDALMOXARIFE
                 AND LE.CDLOCALESTOQ = B.CDLOCALESTOQ
                 AND B.CDPRODUTO     = A.CDPRODUTO
                 AND A.CDPRODUTO     = A.CDPRODESTO
                 AND A.CDPRODUTO     = R.CDPRODUTO
                 AND R.CDLIVROMAG    = P_CDLIVROMAG
                 AND R.CDTPITEMPROD  = T.CDTPITEMPROD
                 AND T.IDITEMESTOQUE = 'S'
                 AND B.DTPOSIESTQ    = (SELECT MAX(P.DTPOSIESTQ)
                                          FROM POSESTDIA P
                                         WHERE P.CDFILIAL     = B.CDFILIAL
                                           AND P.CDPRODUTO    = B.CDPRODUTO
                                           AND P.CDALMOXARIFE = B.CDALMOXARIFE
                                           AND P.CDLOCALESTOQ = B.CDLOCALESTOQ
                                           AND P.NRLOTEESTQ   = B.NRLOTEESTQ
                                           AND P.NRSUBLOTE    = B.NRSUBLOTE
                                           AND P.DTPOSIESTQ   <= P_DTPOSICAO+1)
               GROUP BY A.CDARVPROD, B.CDPRODUTO, FC.PARTICIPANTE, FC.IDPARTICIPANTE,
                     CASE WHEN NVL(FC.CDALMOXINFO,  'X') <> 'X' THEN '1'
                          WHEN NVL(FC.CDALMOXTERC,  'X') <> 'X' THEN '2'
                          WHEN NVL(FC.CDALMOXARIFE, 'X') <> 'X' THEN '2'
                          ELSE '0' END, FC.NRINSJUR) LOOP
      
      V_OUT_RECORD.CDPRODUTO      := C_PROD.CDPRODUTO;
      V_OUT_RECORD.CDARVPROD      := C_PROD.CDARVPROD;
      V_OUT_RECORD.NRINSJUR       := C_PROD.NRINSJUR;
      V_OUT_RECORD.IDPARTICIPANTE := C_PROD.IDPARTICIPANTE;
      V_OUT_RECORD.PARTICIPANTE   := C_PROD.PARTICIPANTE;
      V_OUT_RECORD.QTD            := C_PROD.QTD;
      V_OUT_RECORD.IND_EST        := C_PROD.IND_EST;
      PIPE ROW (V_OUT_RECORD);
   END LOOP;
   RETURN;
END;