DROP TYPE COMPRAS_SUPPLYTEK_TABLE
/

DROP TYPE COMPRAS_SUPPLYTEK_TYPE
/

CREATE TYPE COMPRAS_SUPPLYTEK_TYPE AS
OBJECT(CDFORNECED      VARCHAR2(21),
       NRINSJURFORN    VARCHAR2(30),
       NMRAZSOCFORN    VARCHAR2(100),
       NMFANTFORN      VARCHAR2(40),
       NMESTADO        VARCHAR2(30),
       NMREGIAOGEO     VARCHAR2(20),
       CDFILDEST       VARCHAR2(4),
       NMFILIAL        VARCHAR2(30),
       CDOPERLANC      VARCHAR2(12),
       NMOPERLANC      VARCHAR2(40),
       NRINSJURFILI    VARCHAR2(30),
       CDPRODUTO       VARCHAR2(10),
       CDARVPROD       VARCHAR2(20),
       NMPRODUTO       VARCHAR2(50),
       CDMARCA         VARCHAR2(5),
       NMMARCA         VARCHAR2(20),
       PRODFORNE       VARCHAR2(2000),
       VRPREUNIAF      FLOAT,
       QTPROGENTAF     FLOAT,
       TOTALITEM       FLOAT,
       PRECOANTERIOR   FLOAT,
       TOTALANTERIOR   FLOAT,
       DIFERENCA       FLOAT,
       NRORG           NUMBER)
/

CREATE TYPE COMPRAS_SUPPLYTEK_TABLE AS TABLE OF COMPRAS_SUPPLYTEK_TYPE
/

CREATE OR REPLACE FUNCTION COMPRAS_SUPPLYTEK(P_CDFORNECED      IN VARCHAR2,
                                             P_CDFILIAL        IN VARCHAR2,
                                             P_CDCATEGORIA     IN VARCHAR2,
                                             P_CDSUBCATEGORIA  IN VARCHAR2, 
                                             P_CDPRODINI       IN VARCHAR2,
                                             P_CDPRODFIN       IN VARCHAR2,
                                             P_TCDPRODUTO      IN VARCHAR2,
                                             P_DTINI           IN DATE,
                                             P_DTFIN           IN DATE,
                                             P_DTINIVALANT     IN DATE,
                                             P_DTFINVALANT     IN DATE, 
                                             P_CONSIDERA       IN VARCHAR2 DEFAULT 'A' /*CONSIDERA AF (A) OU CARRINHO DE COMPRAS(C)*/
                                              ) RETURN COMPRAS_SUPPLYTEK_TABLE PIPELINED
IS

   TYPE CURTYPE      IS REF CURSOR;
   C_AF              CURTYPE;
   C_PRECOANTER      CURTYPE;

   V_CDFORNECED      VARCHAR2(21);
   V_NRINSJURFORN    VARCHAR2(30);
   V_NMRAZSOCFORN    VARCHAR2(100);
   V_NMFANTFORN      VARCHAR2(40);
   V_NMESTADO        VARCHAR2(30);
   V_NMREGIAOGEO     VARCHAR2(20);
   V_CDFILDEST       VARCHAR2(4);
   V_CDFILLANC       VARCHAR2(4);
   V_NRAUTOFORN      VARCHAR2(10);
   V_NMFILIAL        VARCHAR2(30);
   V_CDREGIAO        VARCHAR2(3);
   V_NRINSJURFILI    VARCHAR2(30);
   V_CDPRODUTO       VARCHAR2(10);
   V_CDARVPROD       VARCHAR2(20);
   V_NMPRODUTO       VARCHAR2(50);
   V_CDPRODUTOXML    VARCHAR2(2000);
   V_CDMARCA         VARCHAR2(5);
   V_CDOPERADOR      VARCHAR2(12);
   V_NMMARCA         VARCHAR2(20);
   V_VRPREUNIAF      FLOAT;
   V_QTPROGENTAF     FLOAT;
   V_TOTALITEM       FLOAT;
   V_PRECOANTERIOR   FLOAT;
   V_TOTALANTERIOR   FLOAT;
   V_DIFERENCA       FLOAT;
   V_NRORG           NUMBER;
   V_CDOPERLANC      VARCHAR2(12);
   V_NMOPERLANC      VARCHAR2(40);

  OUT_RECORD COMPRAS_SUPPLYTEK_TYPE := COMPRAS_SUPPLYTEK_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);
BEGIN
   IF P_CONSIDERA = 'A' THEN
      OPEN C_AF FOR
         SELECT AUTOFORNE.CDFILDEST, AUTOFORNE.CDFILLANC, AUTOFORNE.NRAUTOFORN, AUTOFORNE.CDOPERADOR, AUTOFORNE.CDFORNECED, AUTOFORNE.NRORG, AUTOFORNE.CDOPERLANCAF,
                ITEMAUTF.CDPRODUTO, ITEMAUTF.CDMARCA, ITEMAUTF.VRPREUNIAF, FORMATPRODMASK(PRODUTO.CDARVPROD) CDARVPROD, PRODUTO.NMPRODUTO,
                SUM(ITEMAUTF.QTPROGENTAF) QTPROGENTAF, 
                SUM(ITEMAUTF.VRPREUNIAF * ITEMAUTF.QTPROGENTAF) TOTALITEM
           FROM AUTOFORNE, ITEMAUTF, PRODUTO
          WHERE (INSTR(P_CDFILIAL, AUTOFORNE.CDFILDEST) > 0 OR P_CDFILIAL = 'T')
            AND (INSTR(P_CDFORNECED, AUTOFORNE.CDFORNECED ) > 0 OR P_CDFORNECED  = 'T')
            AND AUTOFORNE.IDAFCATALOGO = 'S'
            AND AUTOFORNE.IDSITAFALCADA = 'APROVADA'
            AND ITEMAUTF.CDFILDEST  = AUTOFORNE.CDFILDEST
            AND ITEMAUTF.CDFILLANC  = AUTOFORNE.CDFILLANC
            AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
            AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINI AND P_DTFIN
            AND ITEMAUTF.CDPRODUTO = PRODUTO.CDPRODUTO
            AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TCDPRODUTO = 'T'))
            AND EXISTS (SELECT 1
                          FROM PRODCART
                         WHERE PRODCART.NRCARTSUPPLY = AUTOFORNE.NRCARTSUPPLY
                           AND PRODCART.CDPRODUTO = ITEMAUTF.CDPRODUTO
                           AND PRODCART.CDMARCA = ITEMAUTF.CDMARCA
                           AND (INSTR(P_CDCATEGORIA, PRODCART.CDCATEGORIA) > 0 OR P_CDCATEGORIA = 'T')
                           AND (INSTR(P_CDSUBCATEGORIA, PRODCART.CDSUBCATEGORIA) > 0 OR P_CDSUBCATEGORIA = 'T'))
          GROUP BY AUTOFORNE.CDFILDEST, AUTOFORNE.CDFILLANC, AUTOFORNE.NRAUTOFORN, AUTOFORNE.CDOPERADOR, AUTOFORNE.CDFORNECED, AUTOFORNE.NRORG, AUTOFORNE.CDOPERLANCAF, 
                   ITEMAUTF.CDPRODUTO, ITEMAUTF.CDMARCA, ITEMAUTF.VRPREUNIAF, FORMATPRODMASK(PRODUTO.CDARVPROD), PRODUTO.NMPRODUTO;
      ELSE
         OPEN C_AF FOR
            SELECT CARTSUPPLY.CDFILIAL AS CDFILDEST, CARTSUPPLY.CDFILIAL AS CDFILLANC, CARTSUPPLY.NRCARTSUPPLY AS NRAUTOFORN, 
                   CARTSUPPLY.CDOPERADOR, CARTSUPPLY.CDFORNECED, CARTSUPPLY.NRORG, CARTSUPPLY.CDOPERADOR,
                   PRODCART.CDPRODUTO, PRODCART.CDMARCA, PRODCART.VRPRECPROD AS VRPREUNIAF, FORMATPRODMASK(PRODUTO.CDARVPROD) CDARVPROD, PRODUTO.NMPRODUTO,
                   SUM(PRODCART.QTPRODUTO) QTPROGENTAF, 
                   SUM(PRODCART.VRPRECPROD * PRODCART.QTPRODUTO) TOTALITEM
              FROM CARTSUPPLY, PRODCART, PRODUTO
             WHERE (INSTR(P_CDFILIAL, CARTSUPPLY.CDFILIAL) > 0 OR P_CDFILIAL = 'T')
               AND (INSTR(P_CDFORNECED, CARTSUPPLY.CDFORNECED ) > 0 OR P_CDFORNECED  = 'T')
               AND CARTSUPPLY.IDSITUCOMPRA = 'A'
               AND CARTSUPPLY.NRCARTSUPPLY  = PRODCART.NRCARTSUPPLY
               AND PRODCART.DTINCLUSAO BETWEEN P_DTINI AND P_DTFIN
               AND PRODCART.CDPRODUTO = PRODUTO.CDPRODUTO
               AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TCDPRODUTO = 'T'))
               AND (INSTR(P_CDCATEGORIA, PRODCART.CDCATEGORIA) > 0 OR P_CDCATEGORIA = 'T')
               AND (INSTR(P_CDSUBCATEGORIA, PRODCART.CDSUBCATEGORIA) > 0 OR P_CDSUBCATEGORIA = 'T')
             GROUP BY CARTSUPPLY.CDFILIAL, CARTSUPPLY.CDFILIAL, CARTSUPPLY.NRCARTSUPPLY, 
                      CARTSUPPLY.CDOPERADOR, CARTSUPPLY.CDFORNECED, CARTSUPPLY.NRORG, CARTSUPPLY.CDOPERADOR,
                      PRODCART.CDPRODUTO, PRODCART.CDMARCA, PRODCART.VRPRECPROD, FORMATPRODMASK(PRODUTO.CDARVPROD), PRODUTO.NMPRODUTO;
      END IF;
   LOOP
      FETCH C_AF INTO V_CDFILDEST, V_CDFILLANC, V_NRAUTOFORN, V_CDOPERADOR, V_CDFORNECED, V_NRORG, V_CDOPERLANC,
                      V_CDPRODUTO, V_CDMARCA, V_VRPREUNIAF, V_CDARVPROD, V_NMPRODUTO, V_QTPROGENTAF, V_TOTALITEM;             
                      
      EXIT WHEN C_AF%NOTFOUND;

      OUT_RECORD.CDFILDEST     := V_CDFILDEST;  
      OUT_RECORD.CDFORNECED    := V_CDFORNECED;
      OUT_RECORD.NRORG         := V_NRORG;
      OUT_RECORD.CDPRODUTO     := V_CDPRODUTO;
      OUT_RECORD.CDMARCA       := V_CDMARCA;
      OUT_RECORD.VRPREUNIAF    := V_VRPREUNIAF;
      OUT_RECORD.QTPROGENTAF   := V_QTPROGENTAF;
      OUT_RECORD.TOTALITEM     := V_TOTALITEM;
      OUT_RECORD.CDARVPROD     := V_CDARVPROD;
      OUT_RECORD.NMPRODUTO     := V_NMPRODUTO;

      /*DADOS FORENCEDOR*/
      BEGIN
         SELECT FORMATMASK(IDTPIJURFORN, NRINSJURFORN) NRINSJURFORN, NMFANTFORN, NMRAZSOCFORN
           INTO V_NRINSJURFORN, V_NMFANTFORN, V_NMRAZSOCFORN
		   FROM FORNECEDOR
		  WHERE CDFORNECED = V_CDFORNECED;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRINSJURFORN := '';
            V_NMFANTFORN   := '';
            V_NMRAZSOCFORN := '';
      END;
      OUT_RECORD.NRINSJURFORN  := V_NRINSJURFORN;
      OUT_RECORD.NMRAZSOCFORN  := V_NMRAZSOCFORN;   
      OUT_RECORD.NMFANTFORN    := V_NMFANTFORN;

      /*DADOS FILIAL*/
      BEGIN
         SELECT FORMATMASK(FILIAL.IDTPIJURFILI, FILIAL.NRINSJURFILI) NRINSJURFILI,FILIAL.NMFILIAL, ESTADO.NMESTADO, ESTADO.NMREGIAOGEO
           INTO V_NRINSJURFILI, V_NMFILIAL, V_NMESTADO, V_NMREGIAOGEO
		   FROM FILIAL, ENDEFILI, ESTADO
		  WHERE FILIAL.CDFILIAL = V_CDFILDEST
		    AND FILIAL.CDFILIAL = ENDEFILI.CDFILIAL
			AND ENDEFILI.IDTPENDEFILI = 'P'
			AND ENDEFILI.SGESTADO = ESTADO.SGESTADO
			AND ENDEFILI.CDPAIS = ESTADO.CDPAIS;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRINSJURFILI := '';
            V_NMFILIAL     := '';
            V_NMESTADO     := '';
            V_NMREGIAOGEO  := '';
      END;
      OUT_RECORD.NRINSJURFILI  := V_NRINSJURFILI;
      OUT_RECORD.NMFILIAL      := V_NMFILIAL;
      OUT_RECORD.NMESTADO      := V_NMESTADO;
      OUT_RECORD.NMREGIAOGEO   := V_NMREGIAOGEO; 

      /*DADOS MARCA*/
      BEGIN
         SELECT NMMARCA
           INTO V_NMMARCA
		   FROM MARCA
		  WHERE CDMARCA  = V_CDMARCA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NMMARCA := '';
      END;
      OUT_RECORD.NMMARCA  := V_NMMARCA;


      /*DADOS OPERADOR LANCAMENTO*/
      BEGIN
         SELECT NMOPERADOR
           INTO V_NMOPERLANC
		   FROM OPERADOR
		  WHERE CDOPERADOR = V_CDOPERLANC;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NMOPERLANC := '';
      END;
      OUT_RECORD.CDOPERLANC  := V_CDOPERLANC;
      OUT_RECORD.NMOPERLANC  := V_NMOPERLANC;

      /*DADOS PPRODUTO POR FORNECEDOR*/
      BEGIN
         SELECT LISTAGG(CDPRODUTOXML||' - '||NMPRODUTOXML||' ; ') WITHIN GROUP (ORDER BY CDPRODUTOXML) AS PRODFORNE
           INTO V_CDPRODUTOXML
		   FROM PRODFORNXML
		  WHERE CDPRODUTO  = V_CDPRODUTO
		    AND CDFORNECED = V_CDFORNECED;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDPRODUTOXML := '';
      END;
      OUT_RECORD.PRODFORNE  := V_CDPRODUTOXML;
      
      IF P_CONSIDERA = 'A' THEN
         /*PREÇO ANTERIOR DENTRO DE UM PERIODO INFORMADO*/
         BEGIN
            SELECT NVL(MIN(ITEMCOLE.VRPRECUNIITM), 0) VRPRECUNIITM
              INTO V_PRECOANTERIOR
              FROM COLETAPR, ITEMCOLE, FILIAL
             WHERE FILIAL.CDFILIAL      = V_CDFILLANC
               AND COLETAPR.CDFILIAL    = NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)
               AND ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
               AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
               AND ITEMCOLE.CDREGIAO    = V_CDREGIAO
               AND ITEMCOLE.CDPRODUTO   = V_CDPRODUTO
               AND ITEMCOLE.CDMARCA     = V_CDMARCA
               AND ITEMCOLE.CDFORNECED  = V_CDFORNECED
               AND ITEMCOLE.VRPRECUNIITM > 0
               AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
               AND COLETAPR.CDOPERADOR =  V_CDOPERADOR
               AND (ITEMCOLE.DTINIVALITM <= P_DTFINVALANT)
               AND (ITEMCOLE.DTFINVALITM >= P_DTINIVALANT);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_PRECOANTERIOR := 0;
         END;
          -- RAISE_APPLICATION_ERROR(-20501,  V_CDFILLANC||' - '||V_CDREGIAO||' - '||V_CDPRODUTO||' - '||V_CDMARCA||' - '||V_CDFORNECED||' - '||V_CDOPERADOR, TRUE);
          
         /* CALCULOS DO SAVING*/
         V_TOTALANTERIOR := V_PRECOANTERIOR * V_QTPROGENTAF;
            
         IF (V_TOTALANTERIOR > 0) THEN
            V_DIFERENCA    := (V_TOTALITEM/V_TOTALANTERIOR-1)*100;
         ELSE
            V_DIFERENCA    := 0;
         END IF;
      ELSE
         V_PRECOANTERIOR := 0;
         V_TOTALANTERIOR := 0;
         V_DIFERENCA     := 0;
      END IF;
      
      OUT_RECORD.PRECOANTERIOR := V_PRECOANTERIOR;
      OUT_RECORD.TOTALANTERIOR := V_TOTALANTERIOR;
      OUT_RECORD.DIFERENCA     := V_DIFERENCA;

      PIPE ROW (OUT_RECORD);
   END LOOP;
   CLOSE C_AF;
END;