DROP TYPE CURVA_ABC_TOT_REGIAO_TABLE
/

DROP TYPE CURVA_ABC_TOT_REGIAO_TYPE
/

CREATE TYPE CURVA_ABC_TOT_REGIAO_TYPE AS OBJECT (CDPRODUTO VARCHAR2(10), 
                                                 CDARVPROD VARCHAR2(20), 
                                                 NMPRODUTO VARCHAR2(50), 
                                                 SGUNIDADE VARCHAR2(10), 
                                                 QTTOT FLOAT, 
                                                 VRMED FLOAT, 
                                                 VRTOT FLOAT, 
                                                 DSCOMPLNOME VARCHAR2(300), 
                                                 CDREGIAO VARCHAR2(3), 
                                                 NMREGIAO VARCHAR2(30), 
                                                 CURVA VARCHAR2(10), 
                                                 PERCENTUAL FLOAT, 
                                                 PERCACUMULADO FLOAT )
/
CREATE TYPE CURVA_ABC_TOT_REGIAO_TABLE AS TABLE OF CURVA_ABC_TOT_REGIAO_TYPE
/

CREATE OR REPLACE FUNCTION CURVA_ABC_TOT_REGIAO(
      P_CDFILIAL     IN VARCHAR2,
      P_T_CDFILIAL   IN VARCHAR2,
      P_DTINICIAL    IN DATE,
      P_DTFINAL      IN DATE,
      P_CDPRODINIC   IN VARCHAR2,
      P_CDPRODFIN    IN VARCHAR2,
      P_CDREGIAO     IN VARCHAR2,
      P_T_CDREGIAO   IN VARCHAR2,
      P_NIVEL        IN VARCHAR2,
      P_CDGRUPPROD   IN VARCHAR2,
      P_LISTASUBGRUP IN VARCHAR2,
      P_SUBGRUPO     IN VARCHAR2,
      P_CDAGRUPFILI  IN VARCHAR2,
      P_IDCNTRRESC   IN VARCHAR2 )
   RETURN CURVA_ABC_TOT_REGIAO_TABLE PIPELINED
IS
   V_VRPECURVAA FLOAT;
   V_VRPECURVAB FLOAT;
   V_VRPECURVAC FLOAT;
   V_CURVA VARCHAR2(10);
   V_TOTALPERCGERAL FLOAT;
   V_TOTALPERCCURVA FLOAT;
   OUT_RECORD CURVA_ABC_TOT_REGIAO_TYPE := CURVA_ABC_TOT_REGIAO_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
   V_CURVA          := 'CURVA A';
   V_TOTALPERCGERAL := 0;
   V_TOTALPERCCURVA := 0;
    SELECT VRPECURVAA,
      VRPECURVAB     ,
      VRPECURVAC
      INTO V_VRPECURVAA,
      V_VRPECURVAB     ,
      V_VRPECURVAC
      FROM PARAMGERAL;
   FOR C_PRODUTOS IN
   (WITH PRINCIPAL ( POSICAO, CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE, QTTOT, VRMED, VRTOT, TOTAL_PRODUTOS, DSCOMPLNOME, CDREGIAO, NMREGIAO ) AS
   (SELECT ROWNUM POSICAO ,
      PRODUTO.CDPRODUTO   ,
      PRODUTO.CDARVPROD   ,
      PRODUTO.NMPRODUTO   ,
      TOT.SGUNIDADE       ,
      TOT.QTTOT           ,
      TOT.VRMED VRMED     ,
      --ROUND(TOT.VRMED,7)VRMED                                      ,
      TOT.VRTOT                                             ,
      SUM(TOT.VRTOT) OVER (PARTITION BY 1) AS TOTAL_PRODUTOS,
      PRODUTO.DSCOMPLNOME                                   ,
      TOT.CDREGIAO                                          ,
      TOT.NMREGIAO
      FROM PRODUTO                                               ,
      (SELECT SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL )                                                                                                AS CDARVPROD,
         SUM(DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), ITEMPRODUTO.QTITEMPROD, ITEMPRODUTO.QTITEMPROD * PRODUTO.VRFATOCONV)) AS QTTOT    ,
         AVG(DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), ITEMPRODUTO.VRUNITITEM, ITEMPRODUTO.VRUNITITEM /PRODUTO.VRFATOCONV))  AS VRMED    ,
         DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), PRODUTO.SGUNIDADE, PRODESTO.SGUNIDADE)                                    AS SGUNIDADE,
         SUM(ITEMPRODUTO.QTITEMPROD * ITEMPRODUTO.VRUNITITEM)                                                                                       AS VRTOT    ,
         REGIAO.CDREGIAO                                                                                                                                        ,
         REGIAO.NMREGIAO
         FROM ITEMPRODUTO,
         TIPOOPERAC      ,
         NOTAFISCAL      ,
         PRODUTO         ,
         PRODUTO PRODESTO,
         REGIAO          ,
         FILIAL          ,
         (SELECT FILIAL.CDFILIAL
            FROM FILIAL
           WHERE ((INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0)
         OR (P_T_CDFILIAL                             = 'T'))
         AND (P_CDAGRUPFILI                          IS NULL)
       UNION ALL
          SELECT FILGRUPFILI.CDFILIAL
            FROM FILGRUPFILI
           WHERE ((INSTR(P_CDFILIAL, FILGRUPFILI.CDFILIAL) > 0)
         OR (P_T_CDFILIAL                                  = 'T'))
         AND (FILGRUPFILI.CDGRUPFILI                       = P_CDAGRUPFILI)
         ) FIL
        WHERE (P_SUBGRUPO         = 'N')
      AND (FIL.CDFILIAL           = NOTAFISCAL.CDFILIRECE)
      AND NOTAFISCAL.IDENTRSAIDOP = 'E'
      AND NOTAFISCAL.CDTIPOOPER   = TIPOOPERAC.CDTIPOOPER
      AND TIPOOPERAC.IDENTRSAIDOP = NOTAFISCAL.IDENTRSAIDOP
      AND TIPOOPERAC.IDGERANFCMP  = 'S'
      AND ITEMPRODUTO.CDFILIAL    = NOTAFISCAL.CDFILIAL
      AND ITEMPRODUTO.CDPRODUTO   = PRODUTO.CDPRODUTO
      AND (PRODUTO.CDPRODESTO     = PRODESTO.CDPRODUTO)
      AND NOTAFISCAL.CDFORNECED  IS NOT NULL
      AND ITEMPRODUTO.NRLANCTONF  = NOTAFISCAL.NRLANCTONF
      AND (PRODUTO.CDARVPROD BETWEEN P_CDPRODINIC AND P_CDPRODFIN)
      AND (NOTAFISCAL.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL)
      AND (ITEMPRODUTO.VRUNITITEM               > 0)
      AND (FILIAL.CDFILIAL                      = NOTAFISCAL.CDFILIRECE)
      AND ((FILIAL.IDCNTRRESC                   = P_IDCNTRRESC)
      OR (P_IDCNTRRESC                          = 'T'))
      AND (FILIAL.CDREGIAO                      = REGIAO.CDREGIAO)
      AND ((INSTR(P_CDREGIAO, REGIAO.CDREGIAO) <> 0)
      OR (P_T_CDREGIAO                          = 'T'))
     GROUP BY SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL)                                                               ,
         DECODE (PRODUTO.CDARVPROD, SUBSTR (PRODUTO.CDARVPROD, 1, P_NIVEL), PRODUTO.SGUNIDADE, PRODESTO.SGUNIDADE),
         REGIAO.CDREGIAO                                                                                          ,
         REGIAO.NMREGIAO
      )TOT
     WHERE PRODUTO.CDARVPROD = TOT.CDARVPROD
 UNION ALL
    SELECT ROWNUM POSICAO                                   ,
      PRODUTO.CDPRODUTO                                     ,
      PRODUTO.CDARVPROD                                     ,
      PRODUTO.NMPRODUTO                                     ,
      TOT.SGUNIDADE                                         ,
      TOT.QTTOT                                             ,
      TOT.VRMED VRMED                                       ,
      TOT.VRTOT                                             ,
      SUM(TOT.VRTOT) OVER (PARTITION BY 1) AS TOTAL_PRODUTOS,
      PRODUTO.DSCOMPLNOME                                   ,
      TOT.CDREGIAO                                          ,
      TOT.NMREGIAO
      FROM PRODUTO                                              ,
      (SELECT SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL)                                                                                                 AS CDARVPROD,
         SUM(DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), ITEMPRODUTO.QTITEMPROD, ITEMPRODUTO.QTITEMPROD * PRODUTO.VRFATOCONV)) AS QTTOT    ,
         AVG(DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), ITEMPRODUTO.VRUNITITEM, ITEMPRODUTO.VRUNITITEM /PRODUTO.VRFATOCONV))  AS VRMED    ,
         DECODE(PRODUTO.CDARVPROD, SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL), PRODUTO.SGUNIDADE, PRODESTO.SGUNIDADE)                                    AS SGUNIDADE,
         SUM(ITEMPRODUTO.QTITEMPROD * ITEMPRODUTO.VRUNITITEM)                                                                                       AS VRTOT    ,
         REGIAO.CDREGIAO                                                                                                                                        ,
         REGIAO.NMREGIAO
         FROM ITEMPRODUTO,
         TIPOOPERAC      ,
         NOTAFISCAL      ,
         PRODUTO         ,
         PRODUTO PRODESTO,
         REGIAO          ,
         FILIAL          ,
         (SELECT FILIAL.CDFILIAL
            FROM FILIAL
           WHERE ((INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0)
         OR (P_T_CDFILIAL                             = 'T'))
         AND (P_CDAGRUPFILI                          IS NULL)
       UNION ALL
          SELECT FILGRUPFILI.CDFILIAL
            FROM FILGRUPFILI
           WHERE ((INSTR(P_CDFILIAL, FILGRUPFILI.CDFILIAL) > 0)
         OR (P_T_CDFILIAL                                  = 'T'))
         AND (FILGRUPFILI.CDGRUPFILI                       = P_CDAGRUPFILI)
         ) FIL
        WHERE (P_SUBGRUPO = 'S')
      AND EXISTS
         (SELECT CDGRUPPROD
            FROM PRSUBGRPROD
           WHERE (PRSUBGRPROD.CDGRUPPROD                      = P_CDGRUPPROD)
         AND ((PRSUBGRPROD.CDFILISUBGR                        = 'P')
         OR (PRSUBGRPROD.CDFILISUBGR                          = NOTAFISCAL.CDFILIRECE))
         AND (PRSUBGRPROD.CDPRODUTO                           = PRODUTO.CDPRODUTO)
         AND (INSTR(P_LISTASUBGRUP, PRSUBGRPROD.CDSUBGRPROD) <> 0)
         )
      AND (FIL.CDFILIAL           = NOTAFISCAL.CDFILIRECE)
      AND NOTAFISCAL.IDENTRSAIDOP = 'E'
      AND NOTAFISCAL.CDTIPOOPER   = TIPOOPERAC.CDTIPOOPER
      AND TIPOOPERAC.IDENTRSAIDOP = NOTAFISCAL.IDENTRSAIDOP
      AND TIPOOPERAC.IDGERANFCMP  = 'S'
      AND ITEMPRODUTO.CDFILIAL    = NOTAFISCAL.CDFILIAL
      AND ITEMPRODUTO.CDPRODUTO   = PRODUTO.CDPRODUTO
      AND (PRODUTO.CDPRODESTO     = PRODESTO.CDPRODUTO)
      AND ITEMPRODUTO.NRLANCTONF  = NOTAFISCAL.NRLANCTONF
      AND NOTAFISCAL.CDFORNECED  IS NOT NULL
      AND (PRODUTO.CDARVPROD BETWEEN P_CDPRODINIC AND P_CDPRODFIN)
      AND (NOTAFISCAL.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL)
      AND (ITEMPRODUTO.VRUNITITEM              > 0)
      AND (FILIAL.CDFILIAL                     = NOTAFISCAL.CDFILIRECE)
      AND ((FILIAL.IDCNTRRESC                  = P_IDCNTRRESC)
      OR (P_IDCNTRRESC                         = 'T'))
      AND (FILIAL.CDREGIAO                     = REGIAO.CDREGIAO)
      AND ((INSTR(P_CDREGIAO,REGIAO.CDREGIAO) <> 0)
      OR (P_T_CDREGIAO                         = 'T'))
     GROUP BY SUBSTR(PRODUTO.CDARVPROD, 1, P_NIVEL)                                                               ,
         DECODE (PRODUTO.CDARVPROD, SUBSTR (PRODUTO.CDARVPROD, 1, P_NIVEL), PRODUTO.SGUNIDADE, PRODESTO.SGUNIDADE),
         REGIAO.CDREGIAO                                                                                          ,
         REGIAO.NMREGIAO
      )TOT
     WHERE PRODUTO.CDARVPROD = TOT.CDARVPROD
  ORDER BY 8 DESC
   ),
   PERCENTUAL ( POSICAO, CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE, QTTOT, VRMED, VRTOT, TOTAL_PRODUTOS, PERC, DSCOMPLNOME, CDREGIAO, NMREGIAO ) AS
   (SELECT POSICAO                     ,
      CDPRODUTO                        ,
      CDARVPROD                        ,
      NMPRODUTO                        ,
      SGUNIDADE                        ,
      QTTOT                            ,
      VRMED                            ,
      VRTOT                            ,
      TOTAL_PRODUTOS                   ,
      (VRTOT / TOTAL_PRODUTOS) AS PERC ,
      DSCOMPLNOME                      ,
      CDREGIAO                         ,
      NMREGIAO
      FROM PRINCIPAL
   ) ,
   ACUMULADO ( POSICAO, CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE, QTTOT, VRMED, VRTOT, TOTAL_PRODUTOS, PERC, PERCACUMULADO, DSCOMPLNOME, CDREGIAO, NMREGIAO ) AS
   (SELECT POSICAO                                                                                                         ,
      CDPRODUTO                                                                                                            ,
      CDARVPROD                                                                                                            ,
      NMPRODUTO                                                                                                            ,
      SGUNIDADE                                                                                                            ,
      QTTOT                                                                                                                ,
      VRMED                                                                                                                ,
      VRTOT                                                                                                                ,
      TOTAL_PRODUTOS                                                                                                       ,
      PERC                                                                                                                 ,
      SUM(PERC) OVER (PARTITION BY 1 ORDER BY VRTOT DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PERCACUMULADO,
      DSCOMPLNOME                                                                                                          ,
      CDREGIAO                                                                                                             ,
      NMREGIAO
      FROM PERCENTUAL
  ORDER BY 8 DESC
   )
 SELECT POSICAO  ,
   CDPRODUTO     ,
   CDARVPROD     ,
   NMPRODUTO     ,
   SGUNIDADE     ,
   QTTOT         ,
   VRMED         ,
   VRTOT         ,
   TOTAL_PRODUTOS,
   PERC          ,
   PERCACUMULADO ,
   DSCOMPLNOME   ,
   CDREGIAO      ,
   NMREGIAO
   FROM ACUMULADO
   )
   LOOP
      OUT_RECORD.CDPRODUTO     := C_PRODUTOS.CDPRODUTO;
      OUT_RECORD.CDARVPROD     := C_PRODUTOS.CDARVPROD;
      OUT_RECORD.NMPRODUTO     := C_PRODUTOS.NMPRODUTO;
      OUT_RECORD.SGUNIDADE     := C_PRODUTOS.SGUNIDADE;
      OUT_RECORD.QTTOT         := C_PRODUTOS.QTTOT;
      OUT_RECORD.VRMED         := C_PRODUTOS.VRMED;
      OUT_RECORD.VRTOT         := C_PRODUTOS.VRTOT;
      OUT_RECORD.DSCOMPLNOME   := C_PRODUTOS.DSCOMPLNOME;
      OUT_RECORD.CDREGIAO      := C_PRODUTOS.CDREGIAO;
      OUT_RECORD.NMREGIAO      := C_PRODUTOS.NMREGIAO;
      OUT_RECORD.PERCENTUAL    := C_PRODUTOS.PERC;
      OUT_RECORD.PERCACUMULADO := C_PRODUTOS.PERCACUMULADO;
      --Recebe o percentual total geral, independente da curva
      V_TOTALPERCGERAL := C_PRODUTOS.PERCACUMULADO*100;
      --Usada como flag para indicar quando deve começar a nova curva
      V_TOTALPERCCURVA                 := C_PRODUTOS.PERC + V_TOTALPERCCURVA;
      OUT_RECORD.CURVA                 := V_CURVA;
      IF (V_CURVA                       = 'CURVA A') THEN
         IF (V_TOTALPERCGERAL           < 99.9999999) THEN
            IF ((V_TOTALPERCCURVA*100) >= V_VRPECURVAA) THEN
               V_CURVA                 := 'CURVA B';
               V_TOTALPERCCURVA        := 0;
            END IF;
         ELSE
            V_CURVA := 'CURVA A';
         END IF;
      ELSIF (V_CURVA                    = 'CURVA B') THEN
         IF (V_TOTALPERCGERAL           < 99.9999999) THEN
            IF ((V_TOTALPERCCURVA*100) >= V_VRPECURVAB) THEN
               V_CURVA                 := 'CURVA C';
               V_TOTALPERCCURVA        := 0;
            END IF;
         ELSE
            V_CURVA := 'CURVA A';
         END IF;
      ELSIF (V_CURVA           = 'CURVA C') THEN
         IF (V_TOTALPERCGERAL >= 99.9999999) THEN
            V_CURVA           := 'CURVA A';
            V_TOTALPERCCURVA  := 0;
         END IF;
      END IF;
      PIPE ROW (OUT_RECORD);
   END LOOP;
END;
/