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),
       NRINSJURFILI    VARCHAR2(30),
       CDPRODUTO       VARCHAR2(10),
       CDARVPROD       VARCHAR2(20),
       NMPRODUTO       VARCHAR2(50),
       PRODFORNE       VARCHAR2(200),
       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
                                              ) 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_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(200);
   V_CDMARCA         VARCHAR2(5);
   V_CDOPERADOR      VARCHAR2(12);
   V_VRPREUNIAF      FLOAT;
   V_QTPROGENTAF     FLOAT;
   V_TOTALITEM       FLOAT;
   V_PRECOANTERIOR   FLOAT;
   V_TOTALANTERIOR   FLOAT;
   V_DIFERENCA       FLOAT;
   V_NRORG           NUMBER;

  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);
BEGIN
   OPEN C_AF FOR
        SELECT FORNECEDOR.CDFORNECED, FORMATMASK(FORNECEDOR.IDTPIJURFORN, FORNECEDOR.NRINSJURFORN) NRINSJURFORN, FORNECEDOR.NMFANTFORN, FORNECEDOR.NMRAZSOCFORN,
               ESTADO.NMESTADO, ESTADO.NMREGIAOGEO,
               AUTOFORNE.CDFILDEST,AUTOFORNE.CDFILLANC, FILIAL.NMFILIAL, FILIAL.CDREGIAO, FORMATMASK(FILIAL.IDTPIJURFILI, FILIAL.NRINSJURFILI) NRINSJURFILI,
               ITEMAUTF.CDPRODUTO, FORMATPRODMASK(PRODUTO.CDARVPROD) CDARVPROD , PRODUTO.NMPRODUTO, PRODFORNXML.CDPRODUTOXML||' - '||PRODFORNXML.NMPRODUTOXML AS PRODFORNE,
               ITEMAUTF.CDMARCA, AUTOFORNE.CDOPERADOR, AUTOFORNE.NRORG,
               ITEMAUTF.VRPREUNIAF, SUM(ITEMAUTF.QTPROGENTAF) QTPROGENTAF, SUM(ITEMAUTF.VRPREUNIAF * ITEMAUTF.QTPROGENTAF) TOTALITEM
          FROM AUTOFORNE, CATEGORIASUPPLY, SUBCATEGSUPPLY, FILIAL, FORNECEDOR, PRODSUPPLY, ITEMAUTF, ENDEFILI, ESTADO, PRODUTO, PRODFORNXML
         WHERE (INSTR(P_CDFILIAL, AUTOFORNE.CDFILDEST) > 0 OR P_CDFILIAL = 'T')
           AND (INSTR(P_CDFORNECED, AUTOFORNE.CDFORNECED ) > 0 OR P_CDFORNECED  = 'T')
           AND (INSTR(P_CDCATEGORIA, CATEGORIASUPPLY.CDCATEGORIA) > 0 OR P_CDCATEGORIA = 'T')
           AND (INSTR(P_CDSUBCATEGORIA, SUBCATEGSUPPLY.CDSUBCATEGORIA) > 0 OR P_CDSUBCATEGORIA = 'T')
           AND AUTOFORNE.IDAFCATALOGO = 'S'
           AND AUTOFORNE.IDSITAFALCADA = 'APROVADA'
           AND AUTOFORNE.CDFILDEST = FILIAL.CDFILIAL
           AND AUTOFORNE.CDFORNECED = FORNECEDOR.CDFORNECED
           AND CATEGORIASUPPLY.CDCATEGORIA = SUBCATEGSUPPLY.CDCATEGORIA
           AND PRODSUPPLY.CDCATEGORIA = CATEGORIASUPPLY.CDCATEGORIA
           AND PRODSUPPLY.CDSUBCATEGORIA = SUBCATEGSUPPLY.CDSUBCATEGORIA
           AND PRODSUPPLY.CDFORNECED = AUTOFORNE.CDFORNECED
           AND ITEMAUTF.CDFILDEST = AUTOFORNE.CDFILDEST
           AND ITEMAUTF.CDFILLANC = AUTOFORNE.CDFILLANC
           AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
           AND PRODSUPPLY.CDPRODUTO = ITEMAUTF.CDPRODUTO
           AND ITEMAUTF.CDPRODUTO = PRODUTO.CDPRODUTO
           AND PRODSUPPLY.CDFORNECED = PRODFORNXML.CDFORNECED(+)
           AND PRODSUPPLY.CDPRODUTO = PRODFORNXML.CDPRODUTO(+)
           AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINI AND P_DTFIN
           AND AUTOFORNE.CDFILDEST = ENDEFILI.CDFILIAL
           AND ENDEFILI.IDTPENDEFILI = 'P'
           AND ENDEFILI.SGESTADO = ESTADO.SGESTADO
           AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TCDPRODUTO = 'T'))
        GROUP BY FORNECEDOR.CDFORNECED, FORMATMASK(FORNECEDOR.IDTPIJURFORN, FORNECEDOR.NRINSJURFORN), FORNECEDOR.NMFANTFORN, FORNECEDOR.NMRAZSOCFORN,
               ESTADO.NMESTADO, ESTADO.NMREGIAOGEO,
               AUTOFORNE.CDFILDEST, AUTOFORNE.CDFILLANC, FILIAL.NMFILIAL, FILIAL.CDREGIAO, FORMATMASK(FILIAL.IDTPIJURFILI, FILIAL.NRINSJURFILI),
               ITEMAUTF.CDPRODUTO, FORMATPRODMASK(PRODUTO.CDARVPROD), PRODUTO.NMPRODUTO, PRODFORNXML.CDPRODUTOXML||' - '||PRODFORNXML.NMPRODUTOXML,
               ITEMAUTF.CDMARCA, AUTOFORNE.CDOPERADOR, AUTOFORNE.NRORG, ITEMAUTF.VRPREUNIAF;
   LOOP
      FETCH C_AF INTO V_CDFORNECED, V_NRINSJURFORN, V_NMFANTFORN, V_NMRAZSOCFORN, 
                      V_NMESTADO, V_NMREGIAOGEO, V_CDFILDEST, V_CDFILLANC, V_NMFILIAL, V_CDREGIAO,
                      V_NRINSJURFILI, V_CDPRODUTO, V_CDARVPROD, V_NMPRODUTO, V_CDPRODUTOXML,
                      V_CDMARCA, V_CDOPERADOR, V_NRORG, V_VRPREUNIAF, V_QTPROGENTAF, V_TOTALITEM;                
                      
      EXIT WHEN C_AF%NOTFOUND;

      OUT_RECORD.CDFORNECED    := V_CDFORNECED;
      OUT_RECORD.NRINSJURFORN  := V_NRINSJURFORN;
      OUT_RECORD.NMRAZSOCFORN  := V_NMRAZSOCFORN;   
      OUT_RECORD.NMFANTFORN    := V_NMFANTFORN;
      OUT_RECORD.NMESTADO      := V_NMESTADO;
      OUT_RECORD.NMREGIAOGEO   := V_NMREGIAOGEO; 
      OUT_RECORD.CDFILDEST     := V_CDFILDEST;   
      OUT_RECORD.NMFILIAL      := V_NMFILIAL;
      OUT_RECORD.NRINSJURFILI  := V_NRINSJURFILI;
      OUT_RECORD.CDPRODUTO     := V_CDPRODUTO;
      OUT_RECORD.CDARVPROD     := V_CDARVPROD;
      OUT_RECORD.NMPRODUTO     := V_NMPRODUTO;
      OUT_RECORD.PRODFORNE     := V_CDPRODUTOXML;
      OUT_RECORD.VRPREUNIAF    := V_VRPREUNIAF;
      OUT_RECORD.QTPROGENTAF   := V_QTPROGENTAF;
      OUT_RECORD.TOTALITEM     := V_TOTALITEM;
      OUT_RECORD.NRORG         := V_NRORG;
              
      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;

      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;
/