CREATE OR REPLACE FUNCTION CONSULTA_PRODNACOMP_WEB
(P_CDFILIOPELOG      IN VARCHAR2,
 P_NRSEQRUPTURAOL    IN VARCHAR2,
 P_CDFILIPROC        IN VARCHAR2)
 RETURN PRODNACOMP_TABLE PIPELINED
IS
   V_CDPICTPROD PARAMGERAL.CDPICTPROD%TYPE;
   OUT_RECORD PRODNACOMP_TYPE := PRODNACOMP_TYPE (NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL,
                                               NULL, NULL, NULL, NULL);
BEGIN
   SELECT MAX(CDPICTPROD)
     INTO V_CDPICTPROD
     FROM PARAMGERAL;
   FOR C_PRINCIPAL IN(SELECT MAX(MAX_CDARVPROD) MAX_CDARVPROD, MIN(MIN_CDARVPROD) MIN_CDARVPROD, MIN(MIN_DTPROGENTAF) - 30 AS MIN_DTPROGENTAF,
                             MAX(MAX_DTPROGENTAF) + 30 AS MAX_DTPROGENTAF, MAX(LISTA_CDOPERADOR) LISTA_CDOPERADOR, MAX(LISTA_CDFILDEST) LISTA_CDFILDEST,
                             MAX(TABELA) TABELA
                FROM (SELECT PRODFIN MAX_CDARVPROD, PRODINI MIN_CDARVPROD, DATAFIN MAX_DTPROGENTAF, DATAINI MIN_DTPROGENTAF,
                             LISTCDOPERADOR LISTA_CDOPERADOR, LISTCDFILDEST LISTA_CDFILDEST, 'R' TABELA
                        FROM RUPTURAOL
                       WHERE CDFILIAL = P_CDFILIOPELOG
                         AND INSTR('_'||P_NRSEQRUPTURAOL||'_', NRSEQRUPTURAOL) > 0
                       UNION
                      SELECT CDPRODFINAAPUR MAX_CDARVPROD, CDPRODINICAPUR MIN_CDARVPROD, DTFINAPUPREC MAX_DTPROGENTAF, DTINIAPUPREC MIN_DTPROGENTAF,
                             '_' || CDCOMPRADOR || '_' LISTA_CDOPERADOR, 'T' LISTA_CDFILDEST, 'A' TABELA
                        FROM APURPRECO
                       WHERE APURPRECO.CDFILCENTCMP = P_CDFILIOPELOG
                         AND INSTR('_'||P_NRSEQRUPTURAOL||'_', APURPRECO.NRAPURPREC) > 0))
   LOOP
      FOR C_ITEM IN(SELECT CDREGIAO, NMREGIAO, CDOPERADOR, NMOPERADOR, CDFILIAL, NMFILIAL, DTPROGENTR, CDPRODESTO, CDARVPROD,
                           NMPRODUTO, QTACOMPRAR, SGUNIDADE, DATAINI, DATAFIN, IDAFINCONS, IDPARTFILI, IDQUALIFMARCA, IDPRODNCOMP,
                           IDREFORFIL, IDQUALIFFORN, IDPRODCMPFILI, IDCOTACAO, IDREFOREG, IDQFOPRODFORN, SISTEMA, ATENDIDA, IDPRODPREF,
                           IDSTATCOT, IDSEMQTDEDISP, IDFUNCAOFILI, IDOPERADOROL, NRCOMPRA, DSPRODPREF, DSIDCDTIPPART, DSPARTFILIMARCA,
                           DSPARTIFILINRINSFORN, DSPARTIFILINMFORN, DSIDFORN, LISTA_NRSOLICMP, IDRELACPRODOL, IDACORDO, IDPRCMPATIVO,
                           CDMARCA, NMMARCA, IDITMCONS, LISTA_NRAUTOFORN, CDARVPRODAF, NMPRODUTOAF
                      FROM TABLE(CONSULTA_PRODNACOMP('T', C_PRINCIPAL.MIN_DTPROGENTAF, C_PRINCIPAL.MAX_DTPROGENTAF,
                                                     C_PRINCIPAL.MIN_CDARVPROD, C_PRINCIPAL.MAX_CDARVPROD, C_PRINCIPAL.LISTA_CDOPERADOR, 'SUP', '7',
                                                     C_PRINCIPAL.LISTA_CDFILDEST, P_CDFILIPROC, NULL, 'N', NULL, 'T', 'S', NULL, NULL, 'P', 'N', P_NRSEQRUPTURAOL)) PNC
                     ORDER BY PNC.CDREGIAO, PNC.CDOPERADOR, PNC.CDFILIAL, PNC.CDARVPROD, PNC.DTPROGENTR)
      LOOP
         OUT_RECORD.CDREGIAO             := C_ITEM.CDREGIAO;
         OUT_RECORD.NMREGIAO             := C_ITEM.NMREGIAO;
         OUT_RECORD.CDOPERADOR           := C_ITEM.CDOPERADOR;
         OUT_RECORD.NMOPERADOR           := C_ITEM.NMOPERADOR;
         OUT_RECORD.CDFILIAL             := C_ITEM.CDFILIAL;
         OUT_RECORD.NMFILIAL             := C_ITEM.NMFILIAL;
         OUT_RECORD.DTPROGENTR           := C_ITEM.DTPROGENTR;
         OUT_RECORD.CDPRODESTO           := C_ITEM.CDPRODESTO;
         OUT_RECORD.CDARVPROD            := C_ITEM.CDARVPROD;
         OUT_RECORD.NMPRODUTO            := C_ITEM.NMPRODUTO;
         OUT_RECORD.QTACOMPRAR           := C_ITEM.QTACOMPRAR;
         OUT_RECORD.SGUNIDADE            := C_ITEM.SGUNIDADE;
         OUT_RECORD.DATAINI              := C_ITEM.DATAINI;
         OUT_RECORD.DATAFIN              := C_ITEM.DATAFIN;
         OUT_RECORD.IDAFINCONS           := C_ITEM.IDAFINCONS;
         OUT_RECORD.IDPARTFILI           := C_ITEM.IDPARTFILI;
         OUT_RECORD.IDQUALIFMARCA        := C_ITEM.IDQUALIFMARCA;
         OUT_RECORD.IDPRODNCOMP          := C_ITEM.IDPRODNCOMP;
         OUT_RECORD.IDREFORFIL           := C_ITEM.IDREFORFIL;
         OUT_RECORD.IDQUALIFFORN         := C_ITEM.IDQUALIFFORN;
         OUT_RECORD.IDPRODCMPFILI        := C_ITEM.IDPRODCMPFILI;
         OUT_RECORD.IDCOTACAO            := C_ITEM.IDCOTACAO;
         OUT_RECORD.IDREFOREG            := C_ITEM.IDREFOREG;
         OUT_RECORD.IDQFOPRODFORN        := C_ITEM.IDQFOPRODFORN;
         OUT_RECORD.SISTEMA              := C_ITEM.SISTEMA;
         OUT_RECORD.ATENDIDA             := C_ITEM.ATENDIDA;
         OUT_RECORD.IDPRODPREF           := C_ITEM.IDPRODPREF;
         OUT_RECORD.IDSTATCOT            := C_ITEM.IDSTATCOT;
         OUT_RECORD.IDSEMQTDEDISP        := C_ITEM.IDSEMQTDEDISP;
         OUT_RECORD.IDFUNCAOFILI         := C_ITEM.IDFUNCAOFILI;
         OUT_RECORD.IDOPERADOROL         := C_ITEM.IDOPERADOROL;
         OUT_RECORD.NRCOMPRA             := C_ITEM.NRCOMPRA;
         OUT_RECORD.DSPRODPREF           := C_ITEM.DSPRODPREF;
         OUT_RECORD.DSIDCDTIPPART        := C_ITEM.DSIDCDTIPPART;
         OUT_RECORD.DSPARTFILIMARCA      := C_ITEM.DSPARTFILIMARCA;
         OUT_RECORD.DSPARTIFILINRINSFORN := C_ITEM.DSPARTIFILINRINSFORN;
         OUT_RECORD.DSPARTIFILINMFORN    := C_ITEM.DSPARTIFILINMFORN;
         OUT_RECORD.DSIDFORN             := C_ITEM.DSIDFORN;
         OUT_RECORD.LISTA_NRSOLICMP      := C_ITEM.LISTA_NRSOLICMP;
         OUT_RECORD.IDRELACPRODOL        := C_ITEM.IDRELACPRODOL;
         OUT_RECORD.IDACORDO             := C_ITEM.IDACORDO;
         OUT_RECORD.IDPRCMPATIVO         := C_ITEM.IDPRCMPATIVO;
         OUT_RECORD.CDMARCA              := C_ITEM.CDMARCA;
         OUT_RECORD.NMMARCA              := C_ITEM.NMMARCA;
         OUT_RECORD.IDITMCONS            := C_ITEM.IDITMCONS;
         OUT_RECORD.LISTA_NRAUTOFORN     := C_ITEM.LISTA_NRAUTOFORN;
         OUT_RECORD.CDARVPRODAF          := C_ITEM.CDARVPRODAF;
         OUT_RECORD.NMPRODUTOAF          := C_ITEM.NMPRODUTOAF;
         PIPE ROW (OUT_RECORD);
      END LOOP;
   END LOOP;
END;
/