DROP TYPE CALC_ITEMAUTF_OL_TABLE
/

DROP TYPE CALC_ITEMAUTF_OL_TYPE
/

CREATE TYPE CALC_ITEMAUTF_OL_TYPE AS OBJECT
  (CDFILIALOL     VARCHAR2(4),
   NMFILIALOL     VARCHAR2(30),
   CDREGIAO       VARCHAR2(3),
   NMREGIAO       VARCHAR2(30),
   CDOPERADOR     VARCHAR2(12),
   NMOPERADOR     VARCHAR2(40),
   CDARVPRODOL    VARCHAR2(20),
   NMPRODUTOOL    VARCHAR2(50),
   SGUNIDADEOL    VARCHAR2(10),
   CDPRARVORIG    VARCHAR2(10),
   CDMARCA        VARCHAR2(5),
   CDPRODARVOL    VARCHAR2(10),
   CDARVPROD      VARCHAR2(20),
   CDFILDEST      VARCHAR2(4),
   NMFILDEST      VARCHAR2(30),
   NRAUTOFORN     VARCHAR2(35),
   CDPRODUTO      VARCHAR2(10),
   IDEXISTEAF     VARCHAR2(1),
   DTPROGENTAF    DATE,
   DTUTILIZA      DATE,
   NRLIMISEGU     FLOAT,
   QTPROGENTCONS  FLOAT,
   QTPROGENTAF    FLOAT
  )
/

CREATE TYPE CALC_ITEMAUTF_OL_TABLE AS TABLE OF CALC_ITEMAUTF_OL_TYPE
/

CREATE OR REPLACE FUNCTION CALC_ITEMAUTF_OL
                          (P_CDFILIOPELOG    IN ITEMAUTF.CDFILDEST%TYPE,
                           P_DTINICIALAF     IN DATE,
                           P_DTFINALAF       IN DATE,
                           P_CDOPERADOR      IN VARCHAR2,
                           P_CDREGIAO        IN VARCHAR2,
                           P_CDPRODINIC      IN PRODUTO.CDARVPROD%TYPE,
                           P_CDPRODFIN       IN PRODUTO.CDARVPROD%TYPE,
                           P_GERACMP         IN VARCHAR2,
                           P_TIPOCONSULTA    IN VARCHAR2)
                          RETURN CALC_ITEMAUTF_OL_TABLE PIPELINED IS
   OUT_RECORD CALC_ITEMAUTF_OL_TYPE := CALC_ITEMAUTF_OL_TYPE (NULL, NULL, NULL, NULL, NULL,
                                                              NULL, NULL, NULL, NULL, NULL,
                                                              NULL, NULL, NULL, NULL, NULL,
                                                              NULL, NULL, NULL, NULL, NULL,
                                                               0, 0, 0);

   V_CDPRODINIC       VARCHAR2(20);
   V_CDPRODFIN        VARCHAR2(20);
   V_CHAVE            VARCHAR2(30) := '_';
   V_NRLIMISEGU       FLOAT;
BEGIN
   IF P_TIPOCONSULTA IN ('1', '3') THEN
      SELECT NVL(MIN(PRODUTO.CDARVPROD), '0')
        INTO V_CDPRODINIC
        FROM RELACPRODOL, PRODUTO PRODUTOOL, PRODUTO
       WHERE RELACPRODOL.CDPRODARVOL = PRODUTOOL.CDPRODUTO
         AND RELACPRODOL.CDPRARVORIG = PRODUTO.CDPRODUTO
         AND PRODUTOOL.CDARVPROD BETWEEN P_CDPRODINIC AND P_CDPRODFIN;

      SELECT NVL(MAX(PRODUTO.CDARVPROD), 'Z')
        INTO V_CDPRODFIN
        FROM RELACPRODOL, PRODUTO PRODUTOOL, PRODUTO
       WHERE RELACPRODOL.CDPRODARVOL = PRODUTOOL.CDPRODUTO
         AND RELACPRODOL.CDPRARVORIG = PRODUTO.CDPRODUTO
         AND PRODUTOOL.CDARVPROD BETWEEN P_CDPRODINIC AND P_CDPRODFIN;

      FOR C_FILIAL_CAB IN(SELECT CDFILIAL
                               FROM FILIAL
                              WHERE (INSTR(P_CDREGIAO, FILIAL.CDREGIAO) > 0) OR (P_CDREGIAO = 'T'))
      LOOP

         FOR C_DADOS_CAB IN (SELECT FILIALOL.CDFILIAL CDFILIALOL, FILIALOL.NMFILIAL NMFILIALOL,
                                    --V_DTINICIAL DTINICIAL, V_DTFINAL DTFINAL, V_DTINICIALAF DTINICIALAF, V_DTFINALAF DTFINALAF,
                                    REGIAO.CDREGIAO, REGIAO.NMREGIAO, OPERADOR.CDOPERADOR, OPERADOR.NMOPERADOR,
                                    PRODUTOOL.CDARVPROD CDARVPRODOL, PRODUTOOL.NMPRODUTO NMPRODUTOOL, PRODUTOOL.SGUNIDADE SGUNIDADEOL,
                                    0 QTESTOQ,
                                    (ITEMAUTF.QTPROGENTAF-NVL(QTEFETENTRAF, 0)) QTPROGENTCONS,
                                    0 QTSOLIPROD, RELACPRODOL.CDPRARVORIG, RELACPRODOL.CDMARCA, RELACPRODOL.CDPRODARVOL, PRODUTO.CDARVPROD,
                                    ITEMAUTF.CDFILDEST, FILIAL.NMFILIAL NMFILDEST, ITEMAUTF.NRAUTOFORN, ITEMAUTF.CDPRODUTO,
                                    (ITEMAUTF.QTPROGENTAF-NVL(QTEFETENTRAF, 0)) QTPROGENTAF, ITEMAUTF.DTPROGENTAF,
                                    (SELECT MIN(I.DTUTILIZA)
                                       FROM ITEMSOLI I, PRODUTO P
                                      WHERE I.CDFILSOLI  = ITEMAUTF.CDFILDEST
                                        AND I.CDPRODUTO  = P.CDPRODUTO
                                        AND P.CDPRODESTO = PRODUTO.CDPRODESTO
                                        AND NVL(I.IDITMCONS,   'N') = 'N'
                                        AND NVL(I.IDSOLIAPROV, 'S') = 'S'
                                        AND I.CDFILIOPELOG IS NULL
                                        AND I.DTPROGENTR = CASE AUTOFORNE.IDTIPOLANCAF
                                                              WHEN 'M'
                                                              THEN ITEMAUTF.DTPROGENTAF
                                                              ELSE NVL(ITEMAUTF.DTENTRITMSOL, ITEMAUTF.DTPROGENTAF)
                                                           END) AS DTUTILIZA,
                                    0 NRLIMISEGU,
                                    'S' IDEXISTEAF,
                                    (SELECT MAX(CDARVPROD)
                                      FROM LIMISEGPROD A, PRODUTO B
                                     WHERE (A.CDPRODUTO = B.CDPRODUTO)
                                       AND (A.CDFILIAL  = P_CDFILIOPELOG)
                                       AND (INSTR(PRODUTOOL.CDARVPROD, B.CDARVPROD) = 1)) LIMCDARVPROD
                               FROM FILIAL FILIALOL, REGIAO, OPERADOR, RELACPRODOL,
                                    PRODUTO PRODUTOOL, ITEMAUTF, AUTOFORNE, FILIAL, PRODUTO
                               WHERE ITEMAUTF.CDFILDEST               = AUTOFORNE.CDFILDEST
                                AND ITEMAUTF.CDFILLANC               = AUTOFORNE.CDFILLANC
                                AND ITEMAUTF.NRAUTOFORN              = AUTOFORNE.NRAUTOFORN
                                AND ITEMAUTF.CDFILDEST               = FILIAL.CDFILIAL
                                AND AUTOFORNE.CDFILIOPELOG           = FILIALOL.CDFILIAL
                                AND FILIALOL.CDREGIAO                = REGIAO.CDREGIAO
                                AND AUTOFORNE.CDOPERADOR             = OPERADOR.CDOPERADOR
                                AND ITEMAUTF.CDPRODUTO               = RELACPRODOL.CDPRARVORIG
                                AND ITEMAUTF.CDMARCA                 = RELACPRODOL.CDMARCA
                                AND RELACPRODOL.CDPRARVORIG          = PRODUTO.CDPRODUTO
                                AND RELACPRODOL.CDPRODARVOL          = PRODUTOOL.CDPRODUTO
                                AND NVL(AUTOFORNE.CDFILIOPELOG, '1') = P_CDFILIOPELOG
                                AND NVL(ITEMAUTF.IDBAIXITMAF, '1')   = '1'
                                AND NVL(AUTOFORNE.IDBAIXAAF, '1')    IN ('1', '7')
                                AND NVL(AUTOFORNE.IDAFEXPIRADA, 'N') = 'N'
                                AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINICIALAF AND P_DTFINALAF
                                AND AUTOFORNE.CDOPERADOR = P_CDOPERADOR
                                --AND ((INSTR(P_CDOPERADOR, AUTOFORNE.CDOPERADOR) > 0) OR (P_CDOPERADOR = 'T'))
                                --AND ((INSTR(P_CDREGIAO, FILIAL.CDREGIAO) > 0) OR (P_CDREGIAO = 'T'))
                                AND FILIAL.CDFILIAL = C_FILIAL_CAB.CDFILIAL
                                AND PRODUTOOL.CDARVPROD BETWEEN P_CDPRODINIC AND P_CDPRODFIN
                             UNION
                               --APENAS PARA EXIBIR ITENS SEM RELACIOMAMENTO
                             SELECT FILIALOL.CDFILIAL CDFILIALOL, FILIALOL.NMFILIAL NMFILIALOL,
                                    --V_DTINICIAL DTINICIAL, V_DTFINAL DTFINAL, V_DTINICIALAF DTINICIALAF, V_DTFINALAF DTFINALAF,
                                    REGIAO.CDREGIAO, REGIAO.NMREGIAO, OPERADOR.CDOPERADOR, OPERADOR.NMOPERADOR,
                                    PRODUTO.CDARVPROD CDARVPRODOL, PRODUTO.NMPRODUTO NMPRODUTO, PRODUTO.SGUNIDADE SGUNIDADEOL,
                                    0 QTESTOQ,
                                    0 QTPROGENTCONS,
                                    0 QTSOLIPROD, PRODUTO.CDPRODUTO CDPRARVORIG, ITEMAUTF.CDMARCA, PRODUTO.CDPRODUTO CDPRODARVOL,
                                    PRODUTO.CDARVPROD,
                                    ITEMAUTF.CDFILDEST, FILIAL.NMFILIAL NMFILDEST, ITEMAUTF.NRAUTOFORN, ITEMAUTF.CDPRODUTO,
                                    (ITEMAUTF.QTPROGENTAF-NVL(QTEFETENTRAF, 0)) QTPROGENTAF, ITEMAUTF.DTPROGENTAF,
                                    SYSDATE  DTUTILIZA,
                                    0 NRLIMISEGU,
                                    '' IDEXISTEAF,
                                    (SELECT MAX(CDARVPROD)
                                      FROM LIMISEGPROD A, PRODUTO B
                                     WHERE (A.CDPRODUTO = B.CDPRODUTO)
                                       AND (A.CDFILIAL  = P_CDFILIOPELOG)
                                       AND (INSTR(PRODUTO.CDARVPROD, B.CDARVPROD) = 1)) LIMCDARVPROD
                               FROM FILIAL FILIALOL, REGIAO, OPERADOR,
                                    ITEMAUTF, AUTOFORNE, FILIAL, PRODUTO, PARASUPRI
                               WHERE ITEMAUTF.CDFILDEST               = AUTOFORNE.CDFILDEST
                                AND ITEMAUTF.CDFILLANC               = AUTOFORNE.CDFILLANC
                                AND ITEMAUTF.NRAUTOFORN              = AUTOFORNE.NRAUTOFORN
                                AND ITEMAUTF.CDFILDEST               = FILIAL.CDFILIAL
                                AND AUTOFORNE.CDFILIOPELOG           = FILIALOL.CDFILIAL
                                AND FILIALOL.CDREGIAO                = REGIAO.CDREGIAO
                                AND AUTOFORNE.CDOPERADOR             = OPERADOR.CDOPERADOR
                                AND ITEMAUTF.CDPRODUTO               = PRODUTO.CDPRODUTO
                                AND NVL(AUTOFORNE.CDFILIOPELOG, '1') = P_CDFILIOPELOG
                                AND NVL(ITEMAUTF.IDBAIXITMAF, '1')   = '1'
                                AND NVL(AUTOFORNE.IDBAIXAAF, '1')    IN ('1', '7')
                                AND NVL(AUTOFORNE.IDAFEXPIRADA, 'N') = 'N'
                                AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINICIALAF AND P_DTFINALAF
                                AND AUTOFORNE.CDOPERADOR = P_CDOPERADOR
                                --AND ((INSTR(P_CDOPERADOR, AUTOFORNE.CDOPERADOR) > 0) OR (P_CDOPERADOR = 'T'))
                                --AND ((INSTR(P_CDREGIAO, FILIAL.CDREGIAO) > 0) OR (P_CDREGIAO = 'T'))
                                AND FILIAL.CDFILIAL = C_FILIAL_CAB.CDFILIAL
                                AND PRODUTO.CDARVPROD BETWEEN V_CDPRODINIC AND V_CDPRODFIN
                                AND PARASUPRI.CDFILIAL                = NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)
                                AND P_GERACMP = 'S'
                                AND NOT EXISTS(SELECT 1
                                                 FROM RELACPRODOL
                                                WHERE RELACPRODOL.CDPRARVORIG   = ITEMAUTF.CDPRODUTO
                                                  AND ((RELACPRODOL.CDMARCA     = ITEMAUTF.CDPRODUTO) OR (RELACPRODOL.CDMARCA = PARASUPRI.CDMARCA)))
                              ORDER BY LIMCDARVPROD, DTPROGENTAF
                          )
         LOOP
            IF V_CHAVE <> C_DADOS_CAB.LIMCDARVPROD||TO_CHAR(C_DADOS_CAB.DTPROGENTAF, 'DD/MM/YYYY') THEN
               SELECT NVL(MAX(CARREGA_LIMISEGU_OL(P_CDFILIOPELOG, C_DADOS_CAB.CDARVPRODOL, C_DADOS_CAB.DTPROGENTAF)), 0)
                 INTO V_NRLIMISEGU
                 FROM DUAL;
               V_CHAVE :=  C_DADOS_CAB.LIMCDARVPROD||TO_CHAR(C_DADOS_CAB.DTPROGENTAF, 'DD/MM/YYYY');
            END IF;
            OUT_RECORD.CDFILIALOL    := C_DADOS_CAB.CDFILIALOL;
            OUT_RECORD.NMFILIALOL    := C_DADOS_CAB.NMFILIALOL;
            OUT_RECORD.CDREGIAO      := C_DADOS_CAB.CDREGIAO;
            OUT_RECORD.NMREGIAO      := C_DADOS_CAB.NMREGIAO;
            OUT_RECORD.CDOPERADOR    := C_DADOS_CAB.CDOPERADOR;
            OUT_RECORD.NMOPERADOR    := C_DADOS_CAB.NMOPERADOR;
            OUT_RECORD.CDARVPRODOL   := C_DADOS_CAB.CDARVPRODOL;
            OUT_RECORD.NMPRODUTOOL   := C_DADOS_CAB.NMPRODUTOOL;
            OUT_RECORD.SGUNIDADEOL   := C_DADOS_CAB.SGUNIDADEOL;
            OUT_RECORD.CDPRARVORIG   := C_DADOS_CAB.CDPRARVORIG;
            OUT_RECORD.CDMARCA       := C_DADOS_CAB.CDMARCA;
            OUT_RECORD.CDPRODARVOL   := C_DADOS_CAB.CDPRODARVOL;
            OUT_RECORD.CDARVPROD     := C_DADOS_CAB.CDARVPROD;
            OUT_RECORD.CDFILDEST     := C_DADOS_CAB.CDFILDEST;
            OUT_RECORD.NMFILDEST     := C_DADOS_CAB.NMFILDEST;
            OUT_RECORD.NRAUTOFORN    := C_DADOS_CAB.NRAUTOFORN;
            OUT_RECORD.CDPRODUTO     := C_DADOS_CAB.CDPRODUTO;
            OUT_RECORD.IDEXISTEAF    := C_DADOS_CAB.IDEXISTEAF;
            OUT_RECORD.DTPROGENTAF   := C_DADOS_CAB.DTPROGENTAF;
            OUT_RECORD.DTUTILIZA     := C_DADOS_CAB.DTUTILIZA;
            OUT_RECORD.NRLIMISEGU    := V_NRLIMISEGU;
            OUT_RECORD.QTPROGENTCONS := C_DADOS_CAB.QTPROGENTCONS;
            OUT_RECORD.QTPROGENTAF   := C_DADOS_CAB.QTPROGENTAF;
            PIPE ROW (OUT_RECORD);
         END LOOP;
      END LOOP;
   END IF;   
   RETURN;
END;
/