DROP TYPE EQUALIZACAO_FORN_TABLE
/

DROP TYPE EQUALIZACAO_FORN_TYPE
/

CREATE TYPE EQUALIZACAO_FORN_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,
        VRPRECOT      FLOAT,
        VRVARIACAO    FLOAT,
        TOTALFORN     FLOAT,
        TOTALCOT      FLOAT)
/

CREATE TYPE EQUALIZACAO_FORN_TABLE AS TABLE OF EQUALIZACAO_FORN_TYPE
/

CREATE OR REPLACE FUNCTION EQUALIZACAO_FORN(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_FORN_TABLE PIPELINED
IS
  OUT_RECORD EQUALIZACAO_FORN_TYPE := EQUALIZACAO_FORN_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
                                                                         NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN        
  FOR C_EQUALIZA IN ( SELECT PR.CDREGIAO,   PR.CDPRODUTO,     PR.CDARVPROD,  PR.NMPRODUTO,  PR.SGUNIDADE,    PR.CDMARCA,  PR.NMMARCA, 
                             PR.CDFORNECED, PR.NMFANTFORN,    UT.VRULTPRECO, PR.VRPRECUNIITM, PR.VRPRECOT, 
                             PR.VRVARIACAO, PR.QTACOMPRAR,    PR.TOTALFORN,  PR.TOTALCOT 
                        FROM (SELECT PR.CDREGIAO,   PR.CDPRODUTO,     PR.CDARVPROD,  PR.NMPRODUTO,  PR.SGUNIDADE,    PR.CDMARCA,  PR.NMMARCA, 
                                     PR.CDFORNECED, PR.NMFANTFORN,    PR.VRULTPRECO, PR.VRPRECUNIITM, PR.VRPRECOT, 
                                     PR.VRVARIACAO, QT.QTACOMPRAR,    TF.TOTALFORN,     TF.TOTALCOT     
                                FROM (SELECT CDREGIAO,   CDPRODUTO,    CDARVPROD, NMPRODUTO, SGUNIDADE, CDMARCA, NMMARCA, CDFORNECED, NMFANTFORN, QTACOMPRAR, 
                                             VRULTPRECO, VRPRECUNIITM, VRPRECOT,  VRVARIACAO
                                        FROM TABLE(EQUALIZACAO_FORNECEDOR(P_NRORG,       P_CDFILIAL,    P_DTINIVALITM, P_DTFINVALITM, P_CDPRODINI,   
                                                                          P_CDPRODFIN,   P_CDMARCA,     P_TMARCA,      P_CDREGIAO,    P_TREGIAO,     
                                                                          P_CDFORNECED,  P_TFORNECEDOR, P_CDCOMPRADOR, P_TCOMPRADOR,  P_DTINIVALANT, 
                                                                          P_DTFINVALANT, P_MELHORPRECO, P_TOTALIZADOR, P_EXBQTCOMP,   P_EXBVARIACAO, P_EXBVRANTFORN))) PR,
                                     (SELECT CDREGIAO, CDPRODUTO, SUM(QTACOMPRAR) QTACOMPRAR
                                        FROM TABLE(EQUALIZACAO_FORNECEDOR(P_NRORG,       P_CDFILIAL,    P_DTINIVALITM, P_DTFINVALITM, P_CDPRODINI,   
                                                                          P_CDPRODFIN,   P_CDMARCA,     P_TMARCA,      P_CDREGIAO,    P_TREGIAO,     
                                                                          P_CDFORNECED,  P_TFORNECEDOR, P_CDCOMPRADOR, P_TCOMPRADOR,  P_DTINIVALANT, 
                                                                          P_DTFINVALANT, P_MELHORPRECO, P_TOTALIZADOR, P_EXBQTCOMP,   P_EXBVARIACAO, P_EXBVRANTFORN))
                                       GROUP BY CDREGIAO, CDPRODUTO) QT,
                                     (SELECT S.CDREGIAO, S.CDPRODUTO, TOTALFORN, TOTALCOT
                                        FROM ( SELECT CDREGIAO, CDPRODUTO, COUNT(1) TOTALCOT
                                                 FROM TABLE(EQUALIZACAO_FORNECEDOR(P_NRORG,       P_CDFILIAL,    P_DTINIVALITM, P_DTFINVALITM, P_CDPRODINI,   
                                                                                   P_CDPRODFIN,   P_CDMARCA,     P_TMARCA,      P_CDREGIAO,    P_TREGIAO,     
                                                                                   P_CDFORNECED,  P_TFORNECEDOR, P_CDCOMPRADOR, P_TCOMPRADOR,  P_DTINIVALANT, 
                                                                                   P_DTFINVALANT, P_MELHORPRECO, P_TOTALIZADOR, P_EXBQTCOMP,   P_EXBVARIACAO, P_EXBVRANTFORN))
                                                WHERE VRPRECOT > 0 
                                                GROUP BY CDREGIAO, CDPRODUTO, CDMARCA) P,  
                                              (SELECT CDREGIAO, CDPRODUTO, COUNT(1) TOTALFORN
                                                 FROM TABLE(EQUALIZACAO_FORNECEDOR(P_NRORG,       P_CDFILIAL,    P_DTINIVALITM, P_DTFINVALITM, P_CDPRODINI,   
                                                                                   P_CDPRODFIN,   P_CDMARCA,     P_TMARCA,      P_CDREGIAO,    P_TREGIAO,     
                                                                                   P_CDFORNECED,  P_TFORNECEDOR, P_CDCOMPRADOR, P_TCOMPRADOR,  P_DTINIVALANT, 
                                                                                   P_DTFINVALANT, P_MELHORPRECO, P_TOTALIZADOR, P_EXBQTCOMP,   P_EXBVARIACAO, P_EXBVRANTFORN))
                                                GROUP BY CDREGIAO, CDPRODUTO, CDMARCA) S
                                       WHERE P.CDREGIAO  = S.CDREGIAO
                                         AND P.CDPRODUTO = S.CDPRODUTO) TF  
                               WHERE PR.CDREGIAO  = QT.CDREGIAO
                                 AND PR.CDPRODUTO = QT.CDPRODUTO 
                                 AND PR.CDREGIAO  = TF.CDREGIAO
                                 AND PR.CDPRODUTO = TF.CDPRODUTO) PR,
                             (SELECT CDREGIAO, CDPRODUTO, MIN(VRULTPRECO)VRULTPRECO
                                        FROM TABLE(EQUALIZACAO_FORNECEDOR(P_NRORG,       P_CDFILIAL,    P_DTINIVALITM, P_DTFINVALITM, P_CDPRODINI,   
                                                                          P_CDPRODFIN,   P_CDMARCA,     P_TMARCA,      P_CDREGIAO,    P_TREGIAO,     
                                                                          P_CDFORNECED,  P_TFORNECEDOR, P_CDCOMPRADOR, P_TCOMPRADOR,  P_DTINIVALANT, 
                                                                          P_DTFINVALANT, P_MELHORPRECO, P_TOTALIZADOR, P_EXBQTCOMP,   P_EXBVARIACAO, P_EXBVRANTFORN))
                               GROUP BY CDREGIAO, CDPRODUTO) UT       
                       WHERE PR.CDREGIAO  = UT.CDREGIAO
                         AND PR.CDPRODUTO = UT.CDPRODUTO  
                       ORDER BY PR.CDREGIAO, PR.CDARVPROD )
  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.VRPRECOT;
    OUT_RECORD.VRULTPRECO   := C_EQUALIZA.VRULTPRECO;     
    OUT_RECORD.QTACOMPRAR   := C_EQUALIZA.QTACOMPRAR;
    OUT_RECORD.VRPRECUNIITM := C_EQUALIZA.VRPRECUNIITM;
    OUT_RECORD.VRVARIACAO   := C_EQUALIZA.VRVARIACAO;
    OUT_RECORD.TOTALFORN    := C_EQUALIZA.TOTALFORN;
    OUT_RECORD.TOTALCOT     := C_EQUALIZA.TOTALCOT;
    
    PIPE ROW (OUT_RECORD);   
  END LOOP;
END;
/


