DROP TYPE EQUALIZACAO_FORNECEDOR_TABLE
/

DROP TYPE EQUALIZACAO_FORNECEDOR_TYPE
/

CREATE TYPE EQUALIZACAO_FORNECEDOR_TYPE AS
OBJECT( CDREGIAO   VARCHAR2(3), 
        CDPRODUTO  VARCHAR2(10), 
        CDARVPROD  VARCHAR2(20), 
        NMPRODUTO  VARCHAR2(50), 
        SGUNIDADE  VARCHAR2(10),
        CDMARCA    VARCHAR2(5),
        NMMARCA    VARCHAR2(20), 
        CDFORNECED VARCHAR2(21), 
        NMFANTFORN VARCHAR2(250), 
        QTACOMPRAR    FLOAT,
        VRULTPRECO    FLOAT,
        VRPRECUNIITM  FLOAT,
        VRVARIACAO    FLOAT,
        VRPRECOT      FLOAT)
/

CREATE TYPE EQUALIZACAO_FORNECEDOR_TABLE AS TABLE OF EQUALIZACAO_FORNECEDOR_TYPE
/

CREATE OR REPLACE FUNCTION EQUALIZACAO_FORNECEDOR(P_NRORG        IN VARCHAR2 DEFAULT '1',
                                                  P_CDFILIAL     IN VARCHAR2,
                                                  P_DTINIVALITM  IN DATE,
                                                  P_DTFINVALITM  IN DATE,
                                                  P_CDPRODINI    IN VARCHAR2,
                                                  P_CDPRODFIN    IN VARCHAR2,
                                                  P_CDMARCA      IN VARCHAR2,
                                                  P_TMARCA       IN VARCHAR2,
                                                  P_CDREGIAO     IN VARCHAR2,
                                                  P_TREGIAO      IN VARCHAR2,
                                                  P_CDFORNECED   IN VARCHAR2,
                                                  P_TFORNECEDOR  IN VARCHAR2,
                                                  P_CDCOMPRADOR  IN VARCHAR2,
                                                  P_TCOMPRADOR   IN VARCHAR2,
                                                  P_DTINIVALANT  IN DATE,
                                                  P_DTFINVALANT  IN DATE,                                                  
                                                  P_MELHORPRECO  IN VARCHAR2,
                                                  P_TOTALIZADOR  IN VARCHAR2,
                                                  P_EXBQTCOMP    IN VARCHAR2,
                                                  P_EXBVARIACAO  IN VARCHAR2,
                                                  P_EXBVRANTFORN IN VARCHAR2) RETURN EQUALIZACAO_FORNECEDOR_TABLE PIPELINED
IS
  V_QTACOMPRAR    NUMERIC;
  V_VRULTPRECO    NUMERIC;
  V_AUXVRUNIT     NUMERIC;
  OUT_RECORD EQUALIZACAO_FORNECEDOR_TYPE := EQUALIZACAO_FORNECEDOR_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
                                                                         NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN        
  FOR C_EQUALIZA IN ( SELECT ITEMCOLE.CDREGIAO, PRODUTO.CDPRODUTO, PRODUTO.CDPRODESTO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, MARCA.CDMARCA, 
                             MARCA.NMMARCA, FORNECEDOR.CDFORNECED, FORNECEDOR.NMFANTFORN, PRODUTO.SGUNIDADE, 0 VRULTPRECO,
                             MIN(ITEMCOLE.VRPRECUNIITM) VRPRECUNIITM
                        FROM ITEMCOLE, COLETAPR, PRODUTO, MARCA, FORNECEDOR
                       WHERE (ITEMCOLE.CDFILIAL      = COLETAPR.CDFILIAL)
                         AND (ITEMCOLE.NRCOLEPRECO   = COLETAPR.NRCOLEPRECO)
                         AND (ITEMCOLE.CDPRODUTO     = PRODUTO.CDPRODUTO)
                         AND (ITEMCOLE.CDMARCA       = MARCA.CDMARCA)
                         AND (ITEMCOLE.CDFORNECED    = FORNECEDOR.CDFORNECED)
                         AND (ITEMCOLE.CDFILIAL      = P_CDFILIAL)
                         AND (ITEMCOLE.DTINIVALITM >= P_DTINIVALITM AND ITEMCOLE.DTFINVALITM <= P_DTFINVALITM)
                         AND (PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN)
                         AND ((INSTR(P_CDMARCA,     ITEMCOLE.CDMARCA   ) <> 0) OR (P_TMARCA = 'T'      ))
                         AND ((INSTR(P_CDREGIAO,    ITEMCOLE.CDREGIAO  ) <> 0) OR (P_TREGIAO = 'T'     ))
                         AND ((INSTR(P_CDFORNECED,  ITEMCOLE.CDFORNECED) <> 0) OR (P_TFORNECEDOR = 'T' )) 
                         AND ((INSTR(P_CDCOMPRADOR, COLETAPR.CDOPERADOR) <> 0) OR (P_TCOMPRADOR = 'T'  ))
                         AND ITEMCOLE.NRORG   = P_NRORG
                         AND COLETAPR.NRORG   = P_NRORG
                         AND PRODUTO.NRORG    = P_NRORG
                         AND MARCA.NRORG      = P_NRORG
                         AND FORNECEDOR.NRORG = P_NRORG 
                        GROUP BY ITEMCOLE.CDREGIAO, PRODUTO.CDPRODUTO, PRODUTO.CDPRODESTO, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO, MARCA.CDMARCA, 
                                 MARCA.NMMARCA, FORNECEDOR.CDFORNECED, FORNECEDOR.NMFANTFORN, PRODUTO.SGUNIDADE, 0)
  LOOP 
    OUT_RECORD.CDREGIAO     := C_EQUALIZA.CDREGIAO;
    OUT_RECORD.CDPRODUTO    := C_EQUALIZA.CDPRODUTO;
    OUT_RECORD.CDARVPROD    := C_EQUALIZA.CDARVPROD;
    OUT_RECORD.SGUNIDADE    := C_EQUALIZA.SGUNIDADE;
    OUT_RECORD.NMPRODUTO    := C_EQUALIZA.NMPRODUTO;
    OUT_RECORD.CDMARCA      := C_EQUALIZA.CDMARCA;
    OUT_RECORD.NMMARCA      := C_EQUALIZA.NMMARCA;
    OUT_RECORD.CDFORNECED   := C_EQUALIZA.CDFORNECED;
    OUT_RECORD.NMFANTFORN   := C_EQUALIZA.NMFANTFORN;
    OUT_RECORD.VRPRECOT     := C_EQUALIZA.VRPRECUNIITM;

    /*Melhor preco anterior*/
    V_VRULTPRECO := 0;
    IF(P_MELHORPRECO = '1') THEN
      BEGIN
        SELECT MIN(ITEMCOLE.VRPRECUNIITM) VRPRECUNIITM
          INTO V_VRULTPRECO
          FROM COLETAPR, ITEMCOLE
         WHERE COLETAPR.CDFILIAL    = P_CDFILIAL          
           AND ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
           AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
           AND ITEMCOLE.CDREGIAO    = C_EQUALIZA.CDREGIAO
           AND ITEMCOLE.CDPRODUTO   = C_EQUALIZA.CDPRODUTO
           AND ITEMCOLE.CDFORNECED  = C_EQUALIZA.CDFORNECED
           AND ITEMCOLE.VRPRECUNIITM > 0
           AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
           AND ((INSTR(P_CDCOMPRADOR, COLETAPR.CDOPERADOR) <> 0) OR P_TCOMPRADOR = 'T')
           AND (ITEMCOLE.DTINIVALITM >= P_DTINIVALITM AND ITEMCOLE.DTFINVALITM <= P_DTFINVALITM)
           AND COLETAPR.NRORG = P_NRORG
           AND ITEMCOLE.NRORG = P_NRORG;

        /*Menor preco do periodo*/   
        OUT_RECORD.VRULTPRECO := V_VRULTPRECO;     
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          OUT_RECORD.VRULTPRECO := 0;
      END;
    ELSE 
      IF(P_MELHORPRECO = '2') THEN
        BEGIN
          SELECT MIN(ITEMCOLE.VRPRECUNIITM) VRPRECUNIITM
            INTO V_VRULTPRECO
            FROM ITEMCOLE
           WHERE ITEMCOLE.CDFILIAL    = P_CDFILIAL
             AND ITEMCOLE.CDREGIAO    = C_EQUALIZA.CDREGIAO
             AND ITEMCOLE.CDPRODUTO   = C_EQUALIZA.CDPRODUTO
             AND ITEMCOLE.CDFORNECED  = C_EQUALIZA.CDFORNECED
             AND ITEMCOLE.VRPRECUNIITM > 0
             AND ITEMCOLE.NRORG       = P_NRORG
             AND ITEMCOLE.NRCOLEPRECO = ( SELECT MAX(ITEMCOLE.NRCOLEPRECO) NRCOLEPRECO
                                            FROM COLETAPR, ITEMCOLE
                                           WHERE COLETAPR.CDFILIAL    = P_CDFILIAL          
                                             AND ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
                                             AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                                             AND ITEMCOLE.CDREGIAO    = C_EQUALIZA.CDREGIAO
                                             AND ITEMCOLE.CDPRODUTO   = C_EQUALIZA.CDPRODUTO
                                             AND ITEMCOLE.CDFORNECED  = C_EQUALIZA.CDFORNECED
                                             AND ITEMCOLE.VRPRECUNIITM > 0
                                             AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
                                             AND ((INSTR(P_CDCOMPRADOR, COLETAPR.CDOPERADOR) <> 0) OR P_TCOMPRADOR = 'T')
                                             AND (ITEMCOLE.DTINIVALITM >= P_DTINIVALITM AND ITEMCOLE.DTFINVALITM <= P_DTFINVALITM)
                                             AND COLETAPR.NRORG = P_NRORG
                                             AND ITEMCOLE.NRORG = P_NRORG);
          
          /*Menor preco do periodo, considerando o ultimo lancamento*/
          OUT_RECORD.VRULTPRECO := V_VRULTPRECO;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            OUT_RECORD.VRULTPRECO := 0;
        END;
      END IF;
    END IF;

    
    /*Quantidade a comprar*/
    V_QTACOMPRAR := 0;
    IF(P_EXBQTCOMP = 'S') THEN
      BEGIN
        SELECT NVL(ITEMSOLI.QTSOLICOMP, 0) - NVL(ITEMAUTF.QTPROGENTAF, 0) QTACOMPRAR 
          INTO V_QTACOMPRAR
          FROM (SELECT SUM(ITEMSOLI.QTSOLIPROD*PRODUTO.VRFATOCONV/ESTOQUE.VRFATOCONV) QTSOLICOMP
                  FROM PRODUTO, PRODUTO ESTOQUE, ITEMSOLI
                 WHERE PRODUTO.CDPRODUTO  = C_EQUALIZA.CDPRODUTO
                   AND PRODUTO.CDPRODESTO = ESTOQUE.CDPRODUTO
                   AND PRODUTO.CDPRODUTO  = ITEMSOLI.CDPRODUTO
                   AND ITEMSOLI.IDITMCONS = 'N'
                   AND NVL(ITEMSOLI.IDSOLIAPROV,'S') = 'S'
                   AND (ITEMSOLI.DTPROGENTR BETWEEN P_DTINIVALITM AND P_DTFINVALITM)
                   AND PRODUTO.NRORG  = P_NRORG
                   AND ESTOQUE.NRORG  = P_NRORG
                   AND ITEMSOLI.NRORG = P_NRORG
                   AND NOT EXISTS (SELECT 1
                                     FROM REFORFIL
                                    WHERE REFORFIL.CDFORNECED = C_EQUALIZA.CDFORNECED
                                      AND REFORFIL.CDFILIAL   = ITEMSOLI.CDFILSOLI
                                      AND REFORFIL.CDPERIENTR IS NULL
                                      AND REFORFIL.NRORG = P_NRORG)) ITEMSOLI,
              (SELECT SUM(ITEMAUTF.QTPROGENTAF*PRODUTO.VRFATOCONV) QTPROGENTAF
                 FROM ITEMAUTF, FILIAL, AUTOFORNE, PRODUTO
                WHERE AUTOFORNE.CDFILDEST  = ITEMAUTF.CDFILDEST
                  AND AUTOFORNE.CDFILLANC  = ITEMAUTF.CDFILLANC
                  AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
                  AND ITEMAUTF.CDFILDEST   = FILIAL.CDFILIAL
                  AND ITEMAUTF.CDPRODUTO   = PRODUTO.CDPRODUTO
                  AND ITEMAUTF.CDFILLANC   = P_CDFILIAL
                  AND FILIAL.CDREGIAO      = C_EQUALIZA.CDREGIAO
                  AND ITEMAUTF.CDPRODUTO   = C_EQUALIZA.CDPRODUTO
                  --AND ITEMAUTF.CDMARCA     = C_EQUALIZA.CDMARCA
                  AND AUTOFORNE.IDSITUATUAAF IS NULL
                  AND AUTOFORNE.IDAFFORALIM  = 'N'
                  AND AUTOFORNE.IDAUTOFORNEX = 'N'
                  AND NVL(AUTOFORNE.IDBAIXAAF, '0') <> '5'
                  AND ITEMAUTF.NRORG  = P_NRORG
                  AND FILIAL.NRORG    = P_NRORG
                  AND AUTOFORNE.NRORG = P_NRORG
                  AND PRODUTO.NRORG   = P_NRORG) ITEMAUTF;        

        IF(V_QTACOMPRAR > 0) THEN
          OUT_RECORD.QTACOMPRAR := V_QTACOMPRAR;
        ELSE
          OUT_RECORD.QTACOMPRAR := 0;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          OUT_RECORD.QTACOMPRAR := 0; 
      END;
    END IF;

    V_AUXVRUNIT := C_EQUALIZA.VRPRECUNIITM; 
    IF (C_EQUALIZA.VRPRECUNIITM = 0) THEN
      IF (P_TOTALIZADOR = '4') THEN
        V_AUXVRUNIT := V_VRULTPRECO;        
      END IF;
    END IF;
    OUT_RECORD.VRPRECUNIITM := V_AUXVRUNIT;
    
    IF ((P_EXBVARIACAO = 'S') AND (P_EXBVRANTFORN = 'S')) THEN
      IF ((V_AUXVRUNIT = 0) OR (V_VRULTPRECO = 0) OR (V_VRULTPRECO = V_AUXVRUNIT)) THEN
        IF (V_VRULTPRECO = V_AUXVRUNIT) THEN
          OUT_RECORD.VRVARIACAO := 0;
        ELSE
          IF (V_AUXVRUNIT = 0) THEN
            OUT_RECORD.VRVARIACAO := -100;
          ELSE
            IF (V_VRULTPRECO = 0) THEN
              OUT_RECORD.VRVARIACAO := 100;
            END IF;
          END IF;
        END IF;
      ELSE
        OUT_RECORD.VRVARIACAO   := ((V_AUXVRUNIT * 100) / V_VRULTPRECO) - 100;
      END IF;
    END IF;
    
    PIPE ROW (OUT_RECORD);   
  END LOOP;
END;
/