create or replace PROCEDURE CAD_COMISSAO(P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
                                         P_CDPRECOBASE  IN PRECOBASE.CDPRECOBASE%TYPE,
                                         P_CDCOMISSAO   VARCHAR2,
                                         P_CDARVPRODINI IN PRODUTO.CDARVPROD%TYPE,
                                         P_CDARVPRODFIN IN PRODUTO.CDARVPROD%TYPE) IS

   V_CDPRECOBASE   VARCHAR2(5);     -- Código do Preço Base
   V_SGFATORPRECO  VARCHAR2(3);     -- SIGLA DO FATOR DE PREÇO
   V_VROPCIOPRBASE FLOAT;           -- Valor do Preço Opcional
   V_PRIMEIRA_VEZ  BOOLEAN;         -- Controla o primeiro registro encontrado de preço opcional

   V_VRINICIAL     FLOAT;           -- VALOR INICIAL DA COMISSÃO
   V_VRFINAL       FLOAT;           -- VALOR FINAL DA COMISSÃO
   V_VRCOMISSAO    FLOAT;           -- VALOR DA COMISSÃO

   V_PRECO_PROX_FAIXA FLOAT;         -- VALOR DO PREÇO DA PRÓXIMA FAIXA
   V_FORMULAUX        VARCHAR2(255); -- Variável Auxiliar
   AUX_PRECO_FINAL    FLOAT;         -- Recebe o resultado da função
   AUX_PRECO_INICIAL  FLOAT;         -- Recebe o resultado da função
   AUX_VALOR_COMISSAO FLOAT;         -- Recebe o resultado da função
   V_VRFAIXA_ANTERIOR FLOAT;         -- Guarda o valor da utima faixa
   V_COMISS_ANTERIOR  FLOAT;         -- Guarda o valor da utima comissão
   V_QTDE_FAIXAS      INT;           -- Quantidade de registros de faixas
   V_QTPRECO_OPCIO    INT;           -- Quantidade de preços opcionais cadastrados

   V_PRECO_OPCIO_ATUAL FLOAT;        -- DEFINE QUAL O PREÇO QUE ESTAMOS USANDO

   V_NRFAIXA      INT;
   V_DSVRINICIAL  VARCHAR2(50);
   V_DSVRFINAL    VARCHAR2(50);
   V_DSVRCOMISSAO VARCHAR2(50);
   V_IDTROCAPRECO VARCHAR2(1);

   -- CURSOR DAS FAIXAS INFORMADAS 
   CURSOR C_FAIXAS IS
      SELECT NRFAIXA, DSVRINICIAL, DSVRFINAL, DSVRCOMISSAO, IDTROCAPRECO
        FROM CADCOMISSAO
       ORDER BY NRFAIXA;


BEGIN
   -- SELECIONA O PREÇO BASE QUE OBRIGATORIAMENTE UTILIZA PREÇOS OPCIONAIS (IDULTPRECPROMO = 'N').
   BEGIN
      SELECT CDPRECOBASE
        INTO V_CDPRECOBASE
        FROM PRECOBASE
       WHERE CDPRECOBASE    = P_CDPRECOBASE
         AND IDULTPRECPROMO = 'N';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDPRECOBASE := NULL;
   END;

   IF (V_CDPRECOBASE IS NOT NULL)  THEN
      -- Deleta todos os itens da tabela de comissão para recalcular os valores e percentuais
      DELETE FROM ITCOMISSAO
        WHERE ((INSTR(NVL(P_CDCOMISSAO,CDCOMISSAO),CDCOMISSAO)<>0) OR (P_CDCOMISSAO = 'T')) AND
        (CDPRODUTO IN (SELECT CDPRODUTO FROM PRODUTO WHERE CDARVPROD BETWEEN P_CDARVPRODINI AND P_CDARVPRODFIN)) AND
        (CDFILIAL   = P_CDFILIAL);

      -- Seleciona os produtos
      FOR C_ITENS IN
         (SELECT P.CDPRODUTO, P.CDARVPROD, P.NMPRODUTO, C.CDCOMISSAO
            FROM PRODUTO P, COMISSAO C, ITEMPREBASE IT 
            WHERE (CDARVPROD BETWEEN P_CDARVPRODINI AND P_CDARVPRODFIN)
             AND (IT.CDPRODUTO = P.CDPRODUTO) 
             AND (CDPRECOBASE = P_CDPRECOBASE )
             AND ((INSTR (NVL(P_CDCOMISSAO,CDCOMISSAO) ,CDCOMISSAO) <> 0) OR (P_CDCOMISSAO = 'T'))
           ORDER BY C.CDCOMISSAO, P.CDARVPROD
         ) LOOP

         -- Verifica se existe preços opcionais cadastrado para os produtos
         BEGIN
            SELECT COUNT(O.VROPCIOPRBASE)
               INTO V_QTPRECO_OPCIO
               FROM FATORPRECO F, OPCIOPRBASE O, ITEMPREBASE  P
              WHERE O.CDFATPRECO    = F.CDFATPRECO      AND
                    O.CDPRODUTO     = P.CDPRODUTO       AND
                    O.CDPRECOBASE   = P.CDPRECOBASE     AND
                    P.CDPRODUTO     = C_ITENS.CDPRODUTO AND
                    P.CDPRECOBASE   = P_CDPRECOBASE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTPRECO_OPCIO := 0;
         END;

         IF V_QTPRECO_OPCIO > 0 THEN

            V_PRIMEIRA_VEZ := TRUE;

            BEGIN
               SELECT COUNT(*) QTDE_FAIXAS
                 INTO V_QTDE_FAIXAS
                 FROM CADCOMISSAO;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_QTDE_FAIXAS := 0;
            END;

            OPEN C_FAIXAS;
            LOOP
               FETCH C_FAIXAS INTO V_NRFAIXA, V_DSVRINICIAL, V_DSVRFINAL, V_DSVRCOMISSAO, V_IDTROCAPRECO;
               EXIT WHEN C_FAIXAS%NOTFOUND;

               -- Se for a primeira faixa
               IF (V_PRIMEIRA_VEZ) THEN
                  V_VRINICIAL  := 0.01;
                  V_VRFINAL    := 0;
                  V_VRCOMISSAO := 0;

                  -- BUSCA O PREÇO OPCIONAL
                  SELECT MIN(O.VROPCIOPRBASE)
                    INTO V_VROPCIOPRBASE
                    FROM FATORPRECO F, OPCIOPRBASE O, ITEMPREBASE  P
                   WHERE O.CDFATPRECO    = F.CDFATPRECO   AND
                         O.CDPRODUTO     = P.CDPRODUTO    AND
                         O.CDPRECOBASE   = P.CDPRECOBASE  AND
                         P.CDPRODUTO     = C_ITENS.CDPRODUTO    AND
                         P.CDPRECOBASE   = P_CDPRECOBASE
                  ORDER BY VROPCIOPRBASE;

                  V_PRECO_OPCIO_ATUAL := V_VROPCIOPRBASE;

                  -- BUSCA O PREÇO OPCIONAL DA PRÓXIMA FAIXA
                  BEGIN
                     SELECT MIN(O.VROPCIOPRBASE)
                       INTO V_PRECO_PROX_FAIXA
                       FROM FATORPRECO F, OPCIOPRBASE O, ITEMPREBASE  P
                      WHERE O.CDFATPRECO    = F.CDFATPRECO      AND
                            O.CDPRODUTO     = P.CDPRODUTO       AND
                            O.CDPRECOBASE   = P.CDPRECOBASE     AND
                            P.CDPRODUTO     = C_ITENS.CDPRODUTO AND
                            P.CDPRECOBASE   = V_CDPRECOBASE     AND
                            O.VROPCIOPRBASE > V_VROPCIOPRBASE;
                  EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                       V_PRECO_PROX_FAIXA := 0;
                  END;

                  -- Decodifica o valor final da FAIXA INICIAL
                  V_FORMULAUX := REPLACE(V_DSVRFINAL, ',', '.');
                  V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                  V_FORMULAUX := REPLACE(V_FORMULAUX, 'NP', REPLACE(V_PRECO_PROX_FAIXA, ',', '.'));

                  V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                  BEGIN
                     EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_PRECO_FINAL;
                  EXCEPTION
                    WHEN OTHERS THEN
                       AUX_PRECO_FINAL := 0;
                  END;

                  V_VRFINAL := AUX_PRECO_FINAL;

                  -- Decodifica o valor de comissão da FAIXA INICIAL
                  V_FORMULAUX := REPLACE(V_DSVRCOMISSAO, ',', '.');
                  V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                  V_FORMULAUX := REPLACE(V_FORMULAUX, 'NP', REPLACE(V_PRECO_PROX_FAIXA, ',', '.'));

                  V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                  BEGIN
                     EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_VALOR_COMISSAO;
                  EXCEPTION
                    WHEN OTHERS THEN
                       AUX_VALOR_COMISSAO := 0;
                  END;

                  V_VRCOMISSAO := AUX_VALOR_COMISSAO;

                  IF (V_VRFINAL > 0) AND (V_VRCOMISSAO > 0) THEN
                     V_VRFINAL := AUX_PRECO_FINAL;
                     INSERT INTO ITCOMISSAO
                       (CDFILIAL, CDCOMISSAO, CDPRODUTO, VRINICVENDA, VRFINAVENDA, VRPECOMIVEND)
                     VALUES
                       (P_CDFILIAL, C_ITENS.CDCOMISSAO, C_ITENS.CDPRODUTO, ROUND(V_VRINICIAL,2), ROUND(V_VRFINAL,2), ROUND(V_VRCOMISSAO,2));
                  END IF;

                  V_PRIMEIRA_VEZ := FALSE;

                  V_VRFAIXA_ANTERIOR := V_VRFINAL;
                  V_COMISS_ANTERIOR  := V_VRCOMISSAO;

               ELSE
                  -- SE FOR A ÚLTIMA FAIXA (Se existir dois preços opcionais, o segundo entra aqui também)
                  IF (C_FAIXAS%ROWCOUNT = V_QTDE_FAIXAS) THEN

                     V_VRINICIAL  := 0;
                     V_VRFINAL    := 99999.99;
                     V_VRCOMISSAO := 0;

                     -- Decodifica o valor inicial da FAIXA FINAL
                     V_FORMULAUX := REPLACE(V_DSVRINICIAL, ',', '.');
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UP', REPLACE(V_VRFAIXA_ANTERIOR, ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UC', REPLACE(V_COMISS_ANTERIOR,  ',', '.'));

                     V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                     BEGIN
                        EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_PRECO_INICIAL;
                     EXCEPTION
                       WHEN OTHERS THEN
                          AUX_PRECO_INICIAL := 0;
                     END;

                     V_VRINICIAL := AUX_PRECO_INICIAL;

                     -- Decodifica o valor de comissão da FAIXA FINAL
                     V_FORMULAUX := REPLACE(V_DSVRCOMISSAO, ',', '.');
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UP', REPLACE(V_VRFAIXA_ANTERIOR, ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UC', REPLACE(V_COMISS_ANTERIOR,  ',', '.'));

                     V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                     BEGIN
                        EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_VALOR_COMISSAO;
                     EXCEPTION
                       WHEN OTHERS THEN
                          AUX_VALOR_COMISSAO := 0;
                     END;

                     V_VRCOMISSAO := AUX_VALOR_COMISSAO;

                     IF (V_VRINICIAL > 0) AND (V_VRFINAL > 0) AND (V_VRCOMISSAO > 0) THEN
                        INSERT INTO ITCOMISSAO
                          (CDFILIAL, CDCOMISSAO, CDPRODUTO, VRINICVENDA, VRFINAVENDA, VRPECOMIVEND)
                        VALUES
                          (P_CDFILIAL, C_ITENS.CDCOMISSAO, C_ITENS.CDPRODUTO, ROUND(V_VRINICIAL,2), ROUND(V_VRFINAL,2), ROUND(V_VRCOMISSAO,2));
                     END IF;

                  ELSE
                     -- SE FOR FAIXAS INTERMEDIÁRIAS (onde existem 3 ou mais preços opcionais).
                     V_VRINICIAL  := 0;
                     V_VRFINAL    := 0;
                     V_VRCOMISSAO := 0;

                     -- SE TIVER QUE TROCAR O PREÇO OPCIONAL, PEGA O PROXIMO DA LISTA, SENÃO CONTINUA USANDO O PREÇO ANTIGO
                     IF V_IDTROCAPRECO = 'N' THEN
                        V_VROPCIOPRBASE := V_PRECO_OPCIO_ATUAL;
                     ELSE
                        BEGIN
                           SELECT MIN(O.VROPCIOPRBASE)
                             INTO V_VROPCIOPRBASE
                             FROM FATORPRECO F, OPCIOPRBASE O, ITEMPREBASE  P
                            WHERE O.CDFATPRECO    = F.CDFATPRECO      AND
                                  O.CDPRODUTO     = P.CDPRODUTO       AND
                                  O.CDPRECOBASE   = P.CDPRECOBASE     AND
                                  P.CDPRODUTO     = C_ITENS.CDPRODUTO AND
                                  P.CDPRECOBASE   = V_CDPRECOBASE     AND
                                  O.VROPCIOPRBASE > V_PRECO_OPCIO_ATUAL;
                        EXCEPTION
                          WHEN NO_DATA_FOUND THEN
                             V_PRECO_PROX_FAIXA := 0;
                        END;

                     END IF;

                     -- BUSCA O PREÇO OPCIONAL DA PRÓXIMA FAIXA
                     BEGIN
                        SELECT MIN(O.VROPCIOPRBASE)
                          INTO V_PRECO_PROX_FAIXA
                          FROM FATORPRECO F, OPCIOPRBASE O, ITEMPREBASE  P
                         WHERE O.CDFATPRECO    = F.CDFATPRECO      AND
                               O.CDPRODUTO     = P.CDPRODUTO       AND
                               O.CDPRECOBASE   = P.CDPRECOBASE     AND
                               P.CDPRODUTO     = C_ITENS.CDPRODUTO AND
                               P.CDPRECOBASE   = V_CDPRECOBASE     AND
                               O.VROPCIOPRBASE > V_VROPCIOPRBASE;
                     EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                          V_PRECO_PROX_FAIXA := 0;
                     END;

                     -- Decodifica o valor inicial da FAIXA INTERMEDIÁRIA
                     V_FORMULAUX := REPLACE(V_DSVRINICIAL, ',', '.');
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UP', REPLACE(V_VRFAIXA_ANTERIOR, ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UC', REPLACE(V_COMISS_ANTERIOR,  ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'NP', REPLACE(V_PRECO_PROX_FAIXA, ',', '.'));

                     V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                     BEGIN
                        EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_PRECO_INICIAL;
                     EXCEPTION
                       WHEN OTHERS THEN
                          AUX_PRECO_INICIAL := 0;
                     END;

                     V_VRINICIAL := AUX_PRECO_INICIAL;

                     -- Decodifica o valor final da FAIXA INTERMEDIÁRIA

                     V_FORMULAUX := REPLACE(V_DSVRFINAL, ',', '.');
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UP', REPLACE(V_VRFAIXA_ANTERIOR, ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UC', REPLACE(V_COMISS_ANTERIOR,  ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'NP', REPLACE(V_PRECO_PROX_FAIXA, ',', '.'));

                     V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                     BEGIN
                        EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_PRECO_FINAL;
                     EXCEPTION
                       WHEN OTHERS THEN
                          AUX_PRECO_FINAL := 0;
                     END;

                     V_VRFINAL := AUX_PRECO_FINAL;

                     -- Decodifica o valor de comissão da FAIXA INTERMEDIÁRIA
                     V_FORMULAUX := REPLACE(V_DSVRCOMISSAO, ',', '.');
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'PA', REPLACE(V_VROPCIOPRBASE,    ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UP', REPLACE(V_VRFAIXA_ANTERIOR, ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'UC', REPLACE(V_COMISS_ANTERIOR,  ',', '.'));
                     V_FORMULAUX := REPLACE(V_FORMULAUX, 'NP', REPLACE(V_PRECO_PROX_FAIXA, ',', '.'));

                     V_FORMULAUX := 'SELECT ' ||V_FORMULAUX|| ' FROM DUAL';

                     BEGIN
                        EXECUTE IMMEDIATE V_FORMULAUX INTO AUX_VALOR_COMISSAO;
                     EXCEPTION
                       WHEN OTHERS THEN
                          AUX_VALOR_COMISSAO := 0;
                     END;

                     V_VRCOMISSAO := AUX_VALOR_COMISSAO;

                     IF (V_VRINICIAL > 0) AND (V_VRFINAL > 0) AND (V_VRCOMISSAO > 0) THEN
                        V_VRFINAL := AUX_PRECO_FINAL;
                        INSERT INTO ITCOMISSAO
                          (CDFILIAL, CDCOMISSAO, CDPRODUTO, VRINICVENDA, VRFINAVENDA, VRPECOMIVEND)
                        VALUES
                          (P_CDFILIAL, C_ITENS.CDCOMISSAO, C_ITENS.CDPRODUTO, ROUND(V_VRINICIAL,2), ROUND(V_VRFINAL,2), ROUND(V_VRCOMISSAO,2));
                     END IF;

                     V_VRFAIXA_ANTERIOR := V_VRFINAL;
                     V_COMISS_ANTERIOR  := V_VRCOMISSAO;
                     V_PRECO_OPCIO_ATUAL := V_VROPCIOPRBASE;

                  END IF;

               END IF;
            END LOOP;
            CLOSE C_FAIXAS;

         END IF;

      END LOOP;

   END IF;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      IF C_FAIXAS%ISOPEN THEN
         CLOSE C_FAIXAS;
      END IF;
      ROLLBACK;
END;