DROP TYPE EQUALIZA_MIX_FORN_TABLE
/

DROP TYPE EQUALIZA_MIX_FORN_TYPE
/

CREATE TYPE EQUALIZA_MIX_FORN_TYPE AS
OBJECT(CDREGIAO         VARCHAR2(3),
       NMREGIAO         VARCHAR2(30),
       CDOPERADOR       VARCHAR2(12),
       NMOPERADOR       VARCHAR2(40),
       CDPRODUTO        VARCHAR2(10),
       CDARVPROD        VARCHAR2(20),
       NMPRODUTO        VARCHAR2(50),
       SGUNIDADE        VARCHAR2(10),
       CDMARCA          VARCHAR2(5),
       NMMARCA          VARCHAR2(20),
       NRINSJURFORNULT  VARCHAR2(20),
       IDTPIJURFORNULT  VARCHAR2(1),
       NMFANTFORNULT    VARCHAR2(250),

       NRINSJURFORN1    VARCHAR2(20),
       NRINSJURFORN2    VARCHAR2(20),
       NRINSJURFORN3    VARCHAR2(20),
       NRINSJURFORN4    VARCHAR2(20),
       NRINSJURFORN5    VARCHAR2(20),

       IDTPIJURFORN1    VARCHAR2(1),
       IDTPIJURFORN2    VARCHAR2(1),
       IDTPIJURFORN3    VARCHAR2(1),
       IDTPIJURFORN4    VARCHAR2(1),
       IDTPIJURFORN5    VARCHAR2(1),

       NMFANTFORN1      VARCHAR2(250),
       NMFANTFORN2      VARCHAR2(250),
       NMFANTFORN3      VARCHAR2(250),
       NMFANTFORN4      VARCHAR2(250),
       NMFANTFORN5      VARCHAR2(250),

       QTACOMPRAR       FLOAT,
       VRULTPRECO       FLOAT,

       VRPRECUNIITM1    FLOAT,
       VRPRECUNIITM2    FLOAT,
       VRPRECUNIITM3    FLOAT,
       VRPRECUNIITM4    FLOAT,
       VRPRECUNIITM5    FLOAT,

       VRVARIACAO1      FLOAT,
       VRVARIACAO2      FLOAT,
       VRVARIACAO3      FLOAT,
       VRVARIACAO4      FLOAT,
       VRVARIACAO5      FLOAT,
       DTINI            DATE,
       DTFIN            DATE)
/

CREATE TYPE EQUALIZA_MIX_FORN_TABLE AS TABLE OF EQUALIZA_MIX_FORN_TYPE
/

CREATE OR REPLACE FUNCTION EQUALIZA_MIX_FORN (P_CDREGIAO     IN VARCHAR2,
                                              P_CDPRODINI    IN VARCHAR2,
                                              P_CDPRODFIN    IN VARCHAR2,
                                              P_TPROD        IN VARCHAR2,
                                              P_DTINI        IN DATE,
                                              P_DTFIN        IN DATE,
                                              P_CDFILIAL     IN VARCHAR2,
                                              P_CDCOMPRADOR  IN VARCHAR2,
                                              P_CDFORNECED   IN VARCHAR2,
                                              P_MAPCOTACAO   IN VARCHAR2 DEFAULT 'N')
 RETURN EQUALIZA_MIX_FORN_TABLE PIPELINED
IS
  V_IDTIPOAPUR        VARCHAR2(1);
  V_CONTAFORNECEDOR   NUMBER;
  V_NRINSJURFORNULT   VARCHAR2(20);
  V_IDTPIJURFORNULT   VARCHAR2(1);
  V_NMFANTFORNULT     FORNECEDOR.NMFANTFORN%TYPE;
  V_QTACOMPRAR        FLOAT;
  V_VRULTPRECO        FLOAT;
  V_CONTADOR          NUMBER := 0;
OUT_RECORD EQUALIZA_MIX_FORN_TYPE := EQUALIZA_MIX_FORN_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, 0, 0, 0, 0,
                                                             0, 0, 0, 0, 0, 0,
                                                             0, 0, NULL, NULL);
BEGIN
   SELECT NVL(MAX(IDTIPOAPUR), '0')
     INTO V_IDTIPOAPUR
     FROM PARASUPRI P
    WHERE P.CDFILIAL = P_CDFILIAL;

   FOR C_BASE IN (SELECT DISTINCT PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE,
                         PRODUTO.CDPRODESTO, MARCA.CDMARCA, MARCA.NMMARCA, OPERADOR.CDOPERADOR,
                         OPERADOR.NMOPERADOR, REGIAO.CDREGIAO, REGIAO.NMREGIAO
                    FROM MARCA, PRODUTO, COLETAPR, ITEMCOLE, OPERADOR, REGIAO
                   WHERE ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
                     AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                     AND ITEMCOLE.CDMARCA     = MARCA.CDMARCA
                     AND ITEMCOLE.CDREGIAO    = REGIAO.CDREGIAO
                     AND ITEMCOLE.CDPRODUTO   = PRODUTO.CDPRODUTO
                     AND COLETAPR.CDOPERADOR  = OPERADOR.CDOPERADOR
                     AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND RPAD(P_CDPRODFIN,20,'Z')) OR (P_TPROD = 'T'))
                     AND ((INSTR(P_CDCOMPRADOR, COLETAPR.CDOPERADOR) > 0) OR (P_CDCOMPRADOR = 'T'))
                     AND ((INSTR(P_CDREGIAO,    ITEMCOLE.CDREGIAO  ) > 0) OR (P_CDREGIAO    = 'T'))
                     AND ITEMCOLE.DTINIVALITM BETWEEN CASE
                                                         WHEN ITEMCOLE.DTINIVALITM < P_DTINI
                                                         THEN ITEMCOLE.DTINIVALITM
                                                         ELSE P_DTINI
                                                      END AND P_DTFIN
                     AND ITEMCOLE.DTFINVALITM BETWEEN P_DTINI AND CASE
                                                                     WHEN ITEMCOLE.DTFINVALITM > P_DTFIN
                                                                     THEN ITEMCOLE.DTFINVALITM
                                                                     ELSE P_DTFIN
                                                                  END
                     AND ((INSTR(P_CDFORNECED,  ITEMCOLE.CDFORNECED ) > 0) OR (P_CDFORNECED  = 'T'))
                     AND (NVL(ITEMCOLE.VRPRECUNIITM, 0)  > 0)
                   ORDER BY PRODUTO.CDPRODESTO, PRODUTO.CDARVPROD) LOOP

       SELECT NVL(MAX(F.NMFANTFORN), ' '),  NVL(MAX(F.IDTPIJURFORN), ' '), NVL(MAX(F.NRINSJURFORN), ' '),
              NVL(MIN(I.VRPREUNIAF), 0), NVL(SUM(I.QTPROGENTAF), 0)
         INTO V_NMFANTFORNULT, V_IDTPIJURFORNULT, V_NRINSJURFORNULT, V_VRULTPRECO, V_QTACOMPRAR
         FROM ITEMAUTF I, AUTOFORNE A, FILIAL FI, FORNECEDOR F
        WHERE A.CDFILDEST   = I.CDFILDEST
          AND A.CDFILLANC   = I.CDFILLANC
          AND A.NRAUTOFORN  = I.NRAUTOFORN
          AND I.CDFILDEST   = FI.CDFILIAL
          AND A.CDFORNECED  = F.CDFORNECED
          AND NVL(I.VRPREUNIAF, 0)  > 0
          AND NVL(I.QTPROGENTAF, 0)  > 0
          AND FI.CDREGIAO   = C_BASE.CDREGIAO
          AND A.CDOPERADOR  = C_BASE.CDOPERADOR
          AND I.CDPRODUTO   = C_BASE.CDPRODUTO
          AND I.CDMARCA     = C_BASE.CDMARCA
          AND I.VRPREUNIAF = (SELECT MIN(ITEMAUTF.VRPREUNIAF)
                                FROM ITEMAUTF
                               WHERE ITEMAUTF.CDMARCA     = I.CDMARCA
                                 AND ITEMAUTF.CDPRODUTO   = I.CDPRODUTO
                                 AND ITEMAUTF.CDFILDEST   = I.CDFILDEST
                                 AND ITEMAUTF.NRAUTOFORN  = I.NRAUTOFORN
                                 AND NVL(ITEMAUTF.VRPREUNIAF, 0)  > 0
                                 AND NVL(ITEMAUTF.QTPROGENTAF, 0) > 0
                                 AND ITEMAUTF.DTPROGENTAF < P_DTINI)
          AND NOT EXISTS(SELECT 1
                           FROM REFORFIL
                          WHERE REFORFIL.CDFORNECED = F.CDFORNECED
                            AND REFORFIL.CDFILIAL   = A.CDFILDEST
                            AND REFORFIL.CDPERIENTR IS NULL);
      OUT_RECORD.NRINSJURFORNULT  := V_NRINSJURFORNULT;
      OUT_RECORD.IDTPIJURFORNULT  := V_IDTPIJURFORNULT;
      OUT_RECORD.NMFANTFORNULT    := V_NMFANTFORNULT;
      OUT_RECORD.QTACOMPRAR       := V_QTACOMPRAR;
      OUT_RECORD.VRULTPRECO       := V_VRULTPRECO;
      OUT_RECORD.CDREGIAO         := C_BASE.CDREGIAO;
      OUT_RECORD.NMREGIAO         := C_BASE.NMREGIAO;
      OUT_RECORD.CDOPERADOR       := C_BASE.CDOPERADOR;
      OUT_RECORD.NMOPERADOR       := C_BASE.NMOPERADOR;
      OUT_RECORD.SGUNIDADE        := C_BASE.SGUNIDADE;
      OUT_RECORD.CDARVPROD        := C_BASE.CDARVPROD;
      OUT_RECORD.NMPRODUTO        := C_BASE.NMPRODUTO;
      OUT_RECORD.CDPRODUTO        := C_BASE.CDPRODUTO;
      OUT_RECORD.CDMARCA          := C_BASE.CDMARCA;
      OUT_RECORD.NMMARCA          := C_BASE.NMMARCA;
      
      OUT_RECORD.DTINI          := P_DTINI;
      OUT_RECORD.DTFIN          := P_DTFIN;

      OUT_RECORD.NRINSJURFORN1    :=  '';
      OUT_RECORD.IDTPIJURFORN1    :=  '';
      OUT_RECORD.NMFANTFORN1      :=  '';
      OUT_RECORD.VRPRECUNIITM1    :=  0;
      OUT_RECORD.VRVARIACAO1      :=  0;
      OUT_RECORD.NRINSJURFORN2    :=  '';
      OUT_RECORD.IDTPIJURFORN2    :=  '';
      OUT_RECORD.NMFANTFORN2      :=  '';
      OUT_RECORD.VRPRECUNIITM2    :=  0;
      OUT_RECORD.VRVARIACAO2      :=  0;
      OUT_RECORD.NRINSJURFORN3    :=  '';
      OUT_RECORD.IDTPIJURFORN3    :=  '';
      OUT_RECORD.NMFANTFORN3      :=  '';
      OUT_RECORD.VRPRECUNIITM3    :=  0;
      OUT_RECORD.VRVARIACAO3      :=  0;
      OUT_RECORD.NRINSJURFORN4    :=  '';
      OUT_RECORD.IDTPIJURFORN4    :=  '';
      OUT_RECORD.NMFANTFORN4      :=  '';
      OUT_RECORD.VRPRECUNIITM4    :=  0;
      OUT_RECORD.VRVARIACAO4      :=  0;
      OUT_RECORD.NRINSJURFORN5    :=  '';
      OUT_RECORD.IDTPIJURFORN5    :=  '';
      OUT_RECORD.NMFANTFORN5      :=  '';
      OUT_RECORD.VRPRECUNIITM5    :=  0;
      OUT_RECORD.VRVARIACAO5      :=  0;
      V_CONTAFORNECEDOR := 1;
      V_CONTADOR        := 0;
      FOR C_ITEM IN(SELECT NRINSJURFORN, NMFANTFORN, IDTPIJURFORN,
                           MIN(VRPRECUNIITM) VRPRECUNIITM
                      FROM (SELECT FORNECEDOR.NRINSJURFORN, FORNECEDOR.NMFANTFORN, FORNECEDOR.IDTPIJURFORN,
                                   CASE V_IDTIPOAPUR WHEN '0' THEN ITEMCOLE.VRPRECUNIITM
                                                     WHEN '1' THEN ITEMCOLE.VRPRECECOITM
                                                     WHEN '2' THEN ITEMCOLE.VRPRECOITMPI
                                   END VRPRECUNIITM
                              FROM COLETAPR, ITEMCOLE, FORNECEDOR
                             WHERE ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
                               AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                               AND COLETAPR.CDOPERADOR  = C_BASE.CDOPERADOR
                               AND ITEMCOLE.CDFILIAL    = P_CDFILIAL
                               AND ITEMCOLE.CDMARCA     = C_BASE.CDMARCA
                               AND ITEMCOLE.CDREGIAO    = C_BASE.CDREGIAO
                               AND ITEMCOLE.CDFORNECED  = FORNECEDOR.CDFORNECED
                               AND ITEMCOLE.CDPRODUTO   = C_BASE.CDPRODUTO
                               AND ITEMCOLE.DTINIVALITM BETWEEN CASE
                                                                   WHEN ITEMCOLE.DTINIVALITM < P_DTINI
                                                                   THEN ITEMCOLE.DTINIVALITM
                                                                   ELSE P_DTINI
                                                                END AND P_DTFIN
                               AND ITEMCOLE.DTFINVALITM BETWEEN P_DTINI AND CASE
                                                                               WHEN ITEMCOLE.DTFINVALITM > P_DTFIN
                                                                               THEN ITEMCOLE.DTFINVALITM
                                                                               ELSE P_DTFIN
                                                                            END
                               AND ((INSTR(P_CDFORNECED,  ITEMCOLE.CDFORNECED ) > 0) OR (P_CDFORNECED  = 'T'))
                               AND (NVL(ITEMCOLE.VRPRECUNIITM, 0)  > 0))
                     GROUP BY NRINSJURFORN, NMFANTFORN, IDTPIJURFORN
                     ORDER BY VRPRECUNIITM) LOOP
         V_CONTADOR := V_CONTADOR +1 ;
         IF P_MAPCOTACAO    = 'S' THEN
            OUT_RECORD.NRINSJURFORN1  :=  C_ITEM.NRINSJURFORN;
            OUT_RECORD.IDTPIJURFORN1  :=  C_ITEM.IDTPIJURFORN;
            OUT_RECORD.NMFANTFORN1    :=  C_ITEM.NMFANTFORN;
            OUT_RECORD.VRPRECUNIITM1  :=  C_ITEM.VRPRECUNIITM;
            IF V_VRULTPRECO > 0 THEN
               OUT_RECORD.VRVARIACAO1 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
            ELSE
               OUT_RECORD.VRVARIACAO1 :=  0;
            END IF;
            PIPE ROW (OUT_RECORD);
         ELSE
            IF V_CONTAFORNECEDOR = 1 THEN
               OUT_RECORD.NRINSJURFORN1  :=  C_ITEM.NRINSJURFORN;
               OUT_RECORD.IDTPIJURFORN1  :=  C_ITEM.IDTPIJURFORN;
               OUT_RECORD.NMFANTFORN1    :=  C_ITEM.NMFANTFORN;
               OUT_RECORD.VRPRECUNIITM1  :=  C_ITEM.VRPRECUNIITM;
               IF V_VRULTPRECO > 0 THEN
                  OUT_RECORD.VRVARIACAO1 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
               ELSE
                  OUT_RECORD.VRVARIACAO1 :=  0;
               END IF;
            ELSIF V_CONTAFORNECEDOR = 2 THEN
               OUT_RECORD.NRINSJURFORN2  :=  C_ITEM.NRINSJURFORN;
               OUT_RECORD.IDTPIJURFORN2  :=  C_ITEM.IDTPIJURFORN;
               OUT_RECORD.NMFANTFORN2    :=  C_ITEM.NMFANTFORN;
               OUT_RECORD.VRPRECUNIITM2  :=  C_ITEM.VRPRECUNIITM;
               IF V_VRULTPRECO > 0 THEN
                  OUT_RECORD.VRVARIACAO2 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
               ELSE
                  OUT_RECORD.VRVARIACAO2 :=  0;
               END IF;
            ELSIF V_CONTAFORNECEDOR = 3 THEN
               OUT_RECORD.NRINSJURFORN3  :=  C_ITEM.NRINSJURFORN;
               OUT_RECORD.IDTPIJURFORN3  :=  C_ITEM.IDTPIJURFORN;
               OUT_RECORD.NMFANTFORN3    :=  C_ITEM.NMFANTFORN;
               OUT_RECORD.VRPRECUNIITM3  :=  C_ITEM.VRPRECUNIITM;
               IF V_VRULTPRECO > 0 THEN
                  OUT_RECORD.VRVARIACAO3 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
               ELSE
                  OUT_RECORD.VRVARIACAO3 :=  0;
               END IF;
            ELSIF V_CONTAFORNECEDOR = 4 THEN
               OUT_RECORD.NRINSJURFORN4  :=  C_ITEM.NRINSJURFORN;
               OUT_RECORD.IDTPIJURFORN4  :=  C_ITEM.IDTPIJURFORN;
               OUT_RECORD.NMFANTFORN4    :=  C_ITEM.NMFANTFORN;
               OUT_RECORD.VRPRECUNIITM4  :=  C_ITEM.VRPRECUNIITM;
               IF V_VRULTPRECO > 0 THEN
                  OUT_RECORD.VRVARIACAO4 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
               ELSE
                  OUT_RECORD.VRVARIACAO4 :=  0;
               END IF;
            ELSIF V_CONTAFORNECEDOR = 5 THEN
               OUT_RECORD.NRINSJURFORN5  :=  C_ITEM.NRINSJURFORN;
               OUT_RECORD.IDTPIJURFORN5  :=  C_ITEM.IDTPIJURFORN;
               OUT_RECORD.NMFANTFORN5    :=  C_ITEM.NMFANTFORN;
               OUT_RECORD.VRPRECUNIITM5  :=  C_ITEM.VRPRECUNIITM;
               IF V_VRULTPRECO > 0 THEN
                  OUT_RECORD.VRVARIACAO5 := (((C_ITEM.VRPRECUNIITM/V_VRULTPRECO)-1)*100);
               ELSE
                  OUT_RECORD.VRVARIACAO5 :=  0;
               END IF;
               EXIT;
            END IF;
            V_CONTAFORNECEDOR := V_CONTAFORNECEDOR+1;
         END IF;
      END LOOP;
      IF (V_CONTADOR > 0) AND (P_MAPCOTACAO <> 'S')THEN
         PIPE ROW (OUT_RECORD);
      END IF;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EQUALIZA_MIX_FORN_TYPE','6.19.XXX'); END;
/

