DROP TYPE QUERY_DETALHE_CMP99000_TABLE
/

DROP TYPE QUERY_DETALHE_CMP99000_TYPE
/

CREATE TYPE QUERY_DETALHE_CMP99000_TYPE AS
OBJECT(NRSEQUENCIAL      VARCHAR2(30),
       INSCRICAODEST     VARCHAR2(100),
       DSDESTINATARIO    VARCHAR2(500),
       CDARVPROD         VARCHAR2(20),
       NMPRODUTO         VARCHAR2(50),
       CDMARCA           VARCHAR2(5),
       DSMARCA           VARCHAR2(40),
       STATUS            VARCHAR2(10),
       DTENTREGA         DATE,
       QUANTIDADE        FLOAT,
       PRECO             FLOAT);
/

CREATE TYPE QUERY_DETALHE_CMP99000_TABLE AS TABLE OF QUERY_DETALHE_CMP99000_TYPE
/

CREATE OR REPLACE FUNCTION QUERY_DETALHE_CMP99000(P_CDFILIAL        IN VARCHAR2,   /*FILIAL DESTINO*/
                                                  P_CDARVPROD       IN VARCHAR2,   /*´CÓDIGO DE ARVORE DO PRODUTO*/
                                                  P_DTINICIAL       IN DATE,   /*PERÍODO INICIAL*/
                                                  P_DTFINAL         IN DATE,   /*PERÍDOD FINAL*/
                                                  P_TIPO            IN VARCHAR2,   /* "AF" OU  NF OU PE*/
                                                  P_CDMARCA         IN VARCHAR2 DEFAULT NULL /* CÓDIGO DA MARCA*/
                                                 ) RETURN QUERY_DETALHE_CMP99000_TABLE PIPELINED
IS
   OUT_RECORD QUERY_DETALHE_CMP99000_TYPE := QUERY_DETALHE_CMP99000_TYPE (NULL, NULL, NULL,
                                                                          NULL, NULL, NULL,
                                                                          NULL, NULL, NULL,
                                                                          0,0);
   TYPE CURTYPE        IS REF CURSOR;
   C_ITEM              CURTYPE;
   V_NRSEQUENCIAL      VARCHAR2(30);
   V_INSCRICAODEST     VARCHAR2(100);
   V_DSDESTINATARIO    VARCHAR2(500);
   V_CDARVPROD         VARCHAR2(20);
   V_NMPRODUTO         VARCHAR2(50);
   V_STATUS            VARCHAR2(10);
   V_DTENTREGA         DATE;
   V_QUANTIDADE        FLOAT;
   V_PRECO             FLOAT;
   V_CDPRODESTO        PRODUTO.CDPRODESTO%TYPE;
   V_CDMARCA           MARCA.CDMARCA%TYPE;
   V_DSMARCA           VARCHAR2(40);
   V_IDFUNCAOFILI      FILIAL.IDFUNCAOFILI%TYPE;
BEGIN
   SELECT NVL(MAX(IDFUNCAOFILI), 'F')
     INTO V_IDFUNCAOFILI
     FROM FILIAL
    WHERE CDFILIAL =  P_CDFILIAL;
   IF V_IDFUNCAOFILI <> 'O' THEN
      SELECT MAX(CDPRODESTO)
        INTO V_CDPRODESTO
        FROM PRODUTO
       WHERE CDARVPROD = P_CDARVPROD;
   ELSE
      SELECT MAX(PRODUTOOL.CDPRODESTO)
        INTO V_CDPRODESTO
        FROM PRODUTO, RELACPRODOL, PRODUTO PRODUTOOL
       WHERE RELACPRODOL.CDPRODARVOL = PRODUTOOL.CDPRODUTO
         AND RELACPRODOL.CDPRARVORIG = PRODUTO.CDPRODUTO
         AND RELACPRODOL.CDMARCA     = NVL(P_CDMARCA, RELACPRODOL.CDMARCA)
         AND PRODUTO.CDARVPROD       = P_CDARVPROD;
   END IF;
   IF P_TIPO = 'AF' THEN
      OPEN C_ITEM FOR
         SELECT AUTOFORNE.CDFILLANC||'/'||ITEMAUTF.NRAUTOFORN NRAUTOFORN, FORNECEDOR.NRINSJURFORN INSCRICAODEST,
                FORNECEDOR.NMFANTFORN DSDESTINATARIO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, ITEMAUTF.IDBAIXITMAF,
                ITEMAUTF.DTPROGENTAF, ITEMAUTF.QTPROGENTAF, ITEMAUTF.VRPREUNIAF, MARCA.CDMARCA, MARCA.CDMARCA|| ' - '||MARCA.NMMARCA
           FROM AUTOFORNE, ITEMAUTF, PRODUTO, FORNECEDOR, MARCA
          WHERE ITEMAUTF.CDFILDEST  = AUTOFORNE.CDFILDEST
            AND ITEMAUTF.CDFILLANC  = AUTOFORNE.CDFILLANC
            AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
            AND AUTOFORNE.CDFORNECED = FORNECEDOR.CDFORNECED
            AND ITEMAUTF.CDPRODUTO  = PRODUTO.CDPRODUTO
            AND ITEMAUTF.CDMARCA    = MARCA.CDMARCA
            AND ITEMAUTF.CDFILDEST  = P_CDFILIAL
            AND PRODUTO.CDPRODESTO  = V_CDPRODESTO
            AND NVL(AUTOFORNE.IDBAIXAAF, '0') NOT IN ('4', '5', '6')
            AND AUTOFORNE.IDAFFORALIM = 'N'
            AND AUTOFORNE.IDSITUATUAAF IS NULL
            AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINICIAL AND P_DTFINAL
            AND NVL(AUTOFORNE.IDAFEXPIRADA, 'N') = 'N';
   ELSIF P_TIPO = 'NF' THEN
      OPEN C_ITEM FOR
         SELECT NOTAFISCAL.CDFILIAL||'/'||ITEMPRODUTO.NRLANCTONF NRLANCTONF, FORNECEDOR.NRINSJURFORN INSCRICAODEST,
                FORNECEDOR.NMFANTFORN DSDESTINATARIO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, NOTAFISCAL.IDSTATUS,
                ITEMPRODUTO.DTPROGENTAF, ITEMPRODUTO.QTITEMPROD, ITEMPRODUTO.VRUNITITEM, '', ''
           FROM NOTAFISCAL, ITEMPRODUTO, PRODUTO, FORNECEDOR
          WHERE ITEMPRODUTO.CDFILIAL  = NOTAFISCAL.CDFILIAL
            AND ITEMPRODUTO.NRLANCTONF = NOTAFISCAL.NRLANCTONF
            AND NOTAFISCAL.CDFORNECED = FORNECEDOR.CDFORNECED
            AND ITEMPRODUTO.CDPRODUTO  = PRODUTO.CDPRODUTO
            AND NOTAFISCAL.IDENTRSAIDOP = 'E'
            AND NVL(NOTAFISCAL.IDSTATUS, 'S') <> 'C'
            AND NOTAFISCAL.CDFILIRECE  = P_CDFILIAL
            AND PRODUTO.CDPRODESTO  = V_CDPRODESTO
            AND NOTAFISCAL.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL;
   ELSIF P_TIPO = 'RE' THEN
      OPEN C_ITEM FOR
         SELECT ITLANCTOEST.CDFILIAL||'/'||ITLANCTOEST.NRLANCESTQ NRAUTOFORN, ITLANCTOEST.CDFILIMOVI INSCRICAODEST,
                FILIAL.NMFILIAL DSDESTINATARIO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, ITLANCTOEST.IDTIPOMOVI IDBAIXITMAF,
                ITLANCTOEST.DTLANCMOVI DTPROGENTAF, ITLANCTOEST.QTLANCTOEST QTPROGENTAF, ITLANCTOEST.VRUNILANCTO VRPREUNIAF,
                '' CDMARCA, '' NMMARCA
           FROM ITLANCTOEST, PRODUTO, FILIAL, PRODFILI
          WHERE ITLANCTOEST.CDPRODMOVI = PRODUTO.CDPRODUTO
            AND ITLANCTOEST.CDFILIMOVI = FILIAL.CDFILIAL
            AND ITLANCTOEST.CDPRODMOVI = PRODFILI.CDPRODUTO
            AND ITLANCTOEST.CDFILIMOVI = PRODFILI.CDFILIAL
            AND NVL(FILIAL.IDCNTRRESC, 'S') = 'N'
            AND NVL(PRODFILI.IDPRODATIV, 'I') = 'A'
            AND NVL(PRODUTO.IDPRODATIVPR, 'N')  = 'S'
            AND ITLANCTOEST.CDFILIMOVI = P_CDFILIAL
            AND ITLANCTOEST.IDTIPOMOVI IN ('3', '4')
            AND PRODUTO.CDPRODESTO  = V_CDPRODESTO
            AND ITLANCTOEST.DTLANCMOVI BETWEEN P_DTINICIAL AND P_DTFINAL;
   ELSE
      OPEN C_ITEM FOR
         SELECT PEDIDO.CDFILIAL||'/'||PEDIDO.NRPEDIDO NRAUTOFORN, CLIENTE.NRINSJURCLIE INSCRICAODEST,
                CLIENTE.NMFANTCLIE DSDESTINATARIO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, ITEMPEDIDO.IDSITITEMPED,
                PEDIDO.DTENTPED, ITEMPEDIDO.QTITEMPED, ITEMPEDIDO.VRPREUNITPED, '', ''
           FROM PEDIDO, ITEMPEDIDO, PRODUTO, CLIENTE
          WHERE PEDIDO.CDFILIAL = ITEMPEDIDO.CDFILIAL
            AND PEDIDO.NRPEDIDO = ITEMPEDIDO.NRPEDIDO
            AND PEDIDO.CDCLIENTE = CLIENTE.CDCLIENTE
            AND PEDIDO.CDFILIAL = P_CDFILIAL
            AND PEDIDO.DTENTPED BETWEEN P_DTINICIAL AND P_DTFINAL
            AND NVL(ITEMPEDIDO.IDSITITEMPED, '1') NOT IN ('5')
            AND NVL(PEDIDO.IDSITPEDIDO, '1') NOT IN ('4')
            AND ITEMPEDIDO.CDPRODUTO = PRODUTO.CDPRODUTO
            AND PRODUTO.CDPRODESTO   = V_CDPRODESTO;
   END IF;
   LOOP
   FETCH C_ITEM INTO V_NRSEQUENCIAL, V_INSCRICAODEST, V_DSDESTINATARIO, V_CDARVPROD,
                     V_NMPRODUTO, V_STATUS, V_DTENTREGA, V_QUANTIDADE, V_PRECO,
                     V_CDMARCA, V_DSMARCA;
   EXIT WHEN C_ITEM%NOTFOUND;
      OUT_RECORD.NRSEQUENCIAL    := V_NRSEQUENCIAL;
      OUT_RECORD.INSCRICAODEST   := V_INSCRICAODEST;
      OUT_RECORD.DSDESTINATARIO  := V_DSDESTINATARIO;
      OUT_RECORD.CDARVPROD       := V_CDARVPROD;
      OUT_RECORD.NMPRODUTO       := V_NMPRODUTO;
      OUT_RECORD.STATUS          := V_STATUS;
      OUT_RECORD.DTENTREGA       := V_DTENTREGA;
      OUT_RECORD.QUANTIDADE      := V_QUANTIDADE;
      OUT_RECORD.PRECO           := V_PRECO;
      OUT_RECORD.CDMARCA         := V_CDMARCA;
      OUT_RECORD.DSMARCA         := V_DSMARCA;
      PIPE ROW (OUT_RECORD);
   END LOOP;
   CLOSE C_ITEM;
END;
/