DROP TYPE COMPARATIVO_CMP_COT_TABLE
/

DROP TYPE COMPARATIVO_CMP_COT_TYPE
/

CREATE TYPE COMPARATIVO_CMP_COT_TYPE AS
OBJECT( CDREGIAO      VARCHAR2(3),
        CDPRODUTO     VARCHAR2(10),
        CDFORNECED    VARCHAR2(21),
        QUANTIDADE    FLOAT,
        VRPRECUNIITM  FLOAT,
        CDOPERADOR    VARCHAR2(12),
        MES           DATE,
        VRTOTALPROD   FLOAT,
        VARIACAO      FLOAT)
/

CREATE TYPE COMPARATIVO_CMP_COT_TABLE AS TABLE OF COMPARATIVO_CMP_COT_TYPE
/

CREATE OR REPLACE FUNCTION COMPARATIVO_CMP_COT  (P_CDREGIAO     IN VARCHAR2,
                                                 P_CDCOMPRADOR  IN VARCHAR2,
                                                 P_DTINICIAL    IN DATE,
                                                 P_DTFINAL      IN DATE,
                                                 P_PRODINI      IN VARCHAR2,
                                                 P_PRODFIN      IN VARCHAR2,
                                                 P_NRARVOREPROD IN VARCHAR2,
                                                 P_IDTPCOMPARA  IN VARCHAR2 DEFAULT 'C',
                                                 P_NRORG        IN VARCHAR2 DEFAULT '1') RETURN COMPARATIVO_CMP_COT_TABLE PIPELINED
IS
  V_DATAMES    DATE;
  V_QUANTIDADE FLOAT;
  V_PRECO      FLOAT;
  V_PRECOANT   FLOAT;
  V_VARIACAO   FLOAT;

  OUT_RECORD COMPARATIVO_CMP_COT_TYPE := COMPARATIVO_CMP_COT_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN

   FOR C_COTACAO IN (SELECT P.CDOPERADOR,
                            ITEMCOLE.CDREGIAO,
                            ITEMCOLE.CDFORNECED,
                            ITEMCOLE.CDPRODUTO,
                            PROD.CDPRODESTO,
                            PROD.VRFATOCONV,
                            ITEMCOLE.CDMARCA,
                            MIN(ITEMCOLE.VRPRECUNIITM) PRECO,
                            TO_CHAR(DATA.DTDATA, 'MM') MES,
                            TO_CHAR(DATA.DTDATA, 'YY') ANO,
                            TO_CHAR(DATA.DTDATA, 'MM')||'/'||TO_CHAR(DATA.DTDATA, 'YYYY') DATA_REL
                      FROM ITEMCOLE, DATA, COLETAPR P, OPERADOR O, PRODUTO PROD
                     WHERE ((INSTR(P_CDREGIAO, ITEMCOLE.CDREGIAO) > 0) OR (P_CDREGIAO = 'T'))
                       AND (ITEMCOLE.DTINIVALITM <= P_DTFINAL)
                       AND (ITEMCOLE.DTFINVALITM >= P_DTINICIAL)
                       AND (ITEMCOLE.CDFILIAL = P.CDFILIAL)
                       AND (ITEMCOLE.NRCOLEPRECO = P.NRCOLEPRECO)
                       AND ((INSTR(P_CDCOMPRADOR, P.CDOPERADOR) > 0) OR (P_CDCOMPRADOR = 'T'))
                       AND P.CDOPERADOR = O.CDOPERADOR
                       AND ITEMCOLE.CDPRODUTO = PROD.CDPRODUTO
                       AND ITEMCOLE.VRPRECUNIITM > 0
                       AND DATA.DTDATA BETWEEN DTINIVALITM AND DTFINVALITM
                       AND PROD.CDPRODUTO IN (
                         SELECT
                             CDPRODUTO
                         FROM
                             PRODUTO
                         WHERE
                             CDARVPROD BETWEEN P_PRODINI AND P_PRODFIN

                         UNION

                         SELECT
                             CDPRODUTO
                         FROM
                             PRODARVOREPROD
                         WHERE
                             (INSTR(P_NRARVOREPROD,PRODARVOREPROD.NRARVOREPROD) > 0
                                 OR   PRODARVOREPROD.NRARVOREPROD IN (
                                     SELECT DISTINCT
                                         ESTRUARVOREPROD.NRARVOREPROD
                                     FROM
                                         ESTRUARVOREPROD
                                     CONNECT BY
                                         PRIOR ESTRUARVOREPROD.NRARVOREPROD = ESTRUARVOREPROD.NRARVOREPRODPAI
                                     START WITH INSTR(P_NRARVOREPROD,ESTRUARVOREPROD.NRARVOREPRODPAI) > 0
                                 )
                             )
                         )
                     GROUP BY P.CDOPERADOR,
                              ITEMCOLE.CDREGIAO,
                              ITEMCOLE.CDFORNECED,
                              ITEMCOLE.CDPRODUTO,
                              PROD.CDPRODESTO,
                              PROD.VRFATOCONV,
                              ITEMCOLE.CDMARCA,
                              TO_CHAR(DATA.DTDATA, 'YY'),
                              TO_CHAR(DATA.DTDATA, 'MM'),
                              TO_CHAR(DATA.DTDATA, 'MM')||'/'||TO_CHAR(DATA.DTDATA, 'YYYY')
                     ORDER BY TO_CHAR(DATA.DTDATA, 'YY'), TO_CHAR(DATA.DTDATA, 'MM'))
   LOOP
      OUT_RECORD.MES          := TO_DATE('01/'||C_COTACAO.DATA_REL, 'DD/MM/YYYY');
      V_DATAMES               := TO_DATE('01/'||C_COTACAO.DATA_REL, 'DD/MM/YYYY');
      OUT_RECORD.CDOPERADOR   := C_COTACAO.CDOPERADOR;
      OUT_RECORD.CDREGIAO     := C_COTACAO.CDREGIAO;
      OUT_RECORD.CDFORNECED   := C_COTACAO.CDFORNECED;
      OUT_RECORD.CDPRODUTO    := C_COTACAO.CDPRODUTO;
      OUT_RECORD.VRPRECUNIITM := C_COTACAO.PRECO;
      V_PRECO := C_COTACAO.PRECO;
      BEGIN
         SELECT  MIN(ITEMCOLE.VRPRECUNIITM)
           INTO V_PRECOANT
           FROM ITEMCOLE
          WHERE ITEMCOLE.CDREGIAO   = C_COTACAO.CDREGIAO
            AND ITEMCOLE.CDFORNECED = C_COTACAO.CDFORNECED
            AND (ITEMCOLE.CDPRODUTO = C_COTACAO.CDPRODUTO)
            AND (ITEMCOLE.DTINIVALITM <= LAST_DAY(ADD_MONTHS(V_DATAMES, -1)))
            AND (ITEMCOLE.DTFINVALITM >= FIRST_DAY(ADD_MONTHS(V_DATAMES, -1)))
            AND ITEMCOLE.VRPRECUNIITM > 0;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_PRECOANT := V_PRECO;
      END;

      BEGIN
         SELECT SUM(IP.QTITEMPROD)
           INTO V_QUANTIDADE
           FROM NOTAFISCAL N, ITEMPRODUTO IP, PRODUTO P, FILIAL F, RELACPRODOL R
          WHERE N.CDFILIAL = IP.CDFILIAL
            AND N.NRLANCTONF = IP.NRLANCTONF
            AND R.CDPRARVORIG = P.CDPRODUTO
            AND N.IDENTRSAIDOP = 'E'
            AND N.CDTIPOOPER IN ('01','08')
            AND N.CDFILIRECE = F.CDFILIAL
            AND N.DTENTRSAID BETWEEN FIRST_DAY(V_DATAMES) AND LAST_DAY(V_DATAMES)
            AND P.CDPRODUTO = C_COTACAO.CDPRODUTO
            AND IP.CDPRODUTO = R.CDPRODARVOL(+)
            AND F.CDREGIAO = C_COTACAO.CDREGIAO
            AND N.CDFORNECED = C_COTACAO.CDFORNECED
          GROUP BY F.CDREGIAO, N.CDFORNECED, IP.CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_QUANTIDADE := 0;
      END;

      IF P_IDTPCOMPARA = 'C' THEN
         OUT_RECORD.VRPRECUNIITM := C_COTACAO.PRECO;
         OUT_RECORD.QUANTIDADE   := V_QUANTIDADE;
         V_PRECO                 := C_COTACAO.PRECO;
      ELSE
         IF P_IDTPCOMPARA = 'E' THEN
            OUT_RECORD.VRPRECUNIITM := C_COTACAO.PRECO/C_COTACAO.VRFATOCONV;
            OUT_RECORD.QUANTIDADE   := V_QUANTIDADE*C_COTACAO.VRFATOCONV;
            V_PRECO                 := C_COTACAO.PRECO/C_COTACAO.VRFATOCONV;
            V_PRECOANT              := V_PRECOANT/C_COTACAO.VRFATOCONV;
            V_QUANTIDADE            := V_QUANTIDADE*C_COTACAO.VRFATOCONV;
         END IF;
      END IF;

      V_VARIACAO := ((V_PRECO * 100)/V_PRECOANT)-100;
      OUT_RECORD.VRTOTALPROD := V_PRECO*V_QUANTIDADE;
      OUT_RECORD.VARIACAO := V_VARIACAO;

      PIPE ROW (OUT_RECORD);
   END LOOP;
END;
/