CREATE OR REPLACE
PROCEDURE REGERA_RATEIONF(P_CDFILIAL IN FILIAL.CDFILIAL%TYPE,
                          P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE)
AS
-- Variaveis
V_CDTPCTPAGAR   VARCHAR2(06);
V_CDFILIRATE    VARCHAR2(04);
V_CDTIPOOPER    VARCHAR2(02);
V_IDENTRSAIDOP  VARCHAR2(01);
V_CDFILIRECE    VARCHAR2(04);
V_CDEMPRESA     VARCHAR2(02);
V_CDFILIAL      VARCHAR2(04);
V_IDCONSCTBRAT  VARCHAR2(1);
V_RATPROD       VARCHAR2(1);
V_VRPARCELA     NUMBER;
V_VRACUMULADO   NUMBER;
V_COUNT         NUMBER;
V_CDCENTCUST VARCHAR2(4);
BEGIN
  SELECT MAX(CDTPCTPAGAR) AS CDTPCTPAGAR
    INTO V_CDTPCTPAGAR
    FROM CONTAPAGAR
   WHERE CDFILIAL   = P_CDFILIAL
     AND NRLANCTONF = P_NRLANCTONF;
  BEGIN
    SELECT COUNT(*)
      INTO V_COUNT
      FROM RATEIONF
     WHERE CDFILIAL   = P_CDFILIAL
       AND NRLANCTONF = P_NRLANCTONF;
  EXCEPTION WHEN NO_DATA_FOUND THEN
     V_COUNT := 0;
  END;
-- Se a conta pagar nao possui conta relacionada
IF V_CDTPCTPAGAR IS NULL THEN
   SELECT CDTIPOOPER, IDENTRSAIDOP, CDFILIRECE, CDEMPRESA
     INTO V_CDTIPOOPER, V_IDENTRSAIDOP, V_CDFILIRECE, V_CDEMPRESA
     FROM NOTAFISCAL
    WHERE CDFILIAL   = P_CDFILIAL
      AND NRLANCTONF = P_NRLANCTONF;
   -- Se for nf de entrada
   IF (V_IDENTRSAIDOP = 'E' AND V_COUNT = 0) THEN
      SELECT NVL(IDCONSCTBRAT, 'N') AS IDCONSCTBRAT
        INTO V_IDCONSCTBRAT
        FROM TIPOOPERAC
       WHERE IDENTRSAIDOP = V_IDENTRSAIDOP
         AND CDTIPOOPER   = V_CDTIPOOPER;
      V_RATPROD := 'N';
      /*BUSCA OS PRODUTOS DA NOTA FISCAL*/
     DELETE RATEIONF
      WHERE CDFILIAL   = P_CDFILIAL
        AND NRLANCTONF = P_NRLANCTONF;
      V_VRACUMULADO := 0;
      V_VRPARCELA   := 0;
      FOR C_PROD_CONTA IN (SELECT P3.CDPRODUTO, P4.CDARVPROD, P4.CDRATEIO,
                                  ROUND(I.VRCONTITEM,2) AS VRCONTITEM,
                                  I.NRSEQITPROD
                             FROM ITEMPRODUTO I, PRODUTO P3, PRODUTO P4
                            WHERE I.CDFILIAL   = P_CDFILIAL
                              AND I.NRLANCTONF = P_NRLANCTONF
                              AND I.CDPRODUTO = P3.CDPRODUTO
                              AND (INSTR(P3.CDARVPROD,NVL(RTRIM(P4.CDARVPROD),P3.CDARVPROD)) = 1)
                              AND P4.CDARVPROD = (SELECT MAX(P1.CDARVPROD)
                                                    FROM ITEMPRODUTO IT,  PRODUTO P, PRODUTO P1
                                                   WHERE IT.CDFILIAL = I.CDFILIAL
                                                     AND IT.NRLANCTONF = I.NRLANCTONF
                                                     AND IT.CDPRODUTO = I.CDPRODUTO
                                                     AND IT.CDPRODUTO = P.CDPRODUTO
                                                     AND (INSTR(P.CDARVPROD,NVL(RTRIM(P1.CDARVPROD),P.CDARVPROD)) = 1))) LOOP
         IF C_PROD_CONTA.CDRATEIO IS NOT NULL THEN
            /*BUSCA PADRAO DE RATEIO DO PRODUTO*/
            V_VRACUMULADO :=  V_VRACUMULADO + C_PROD_CONTA.VRCONTITEM;
            FOR C_PADRAT IN (SELECT CDFILIAL AS CDFILICCUST, CDCENTCUST, (VRRATEIO) AS VRPERATTPCTP
                               FROM ITEMRATEIO
                              WHERE CDRATEIO = C_PROD_CONTA.CDRATEIO) LOOP
              IF not C_PADRAT.CDFILICCUST IS NULL THEN
                 V_CDFILIRATE := C_PADRAT.CDFILICCUST;
              ELSE
                 V_CDFILIRATE := V_CDFILIRECE;
              END IF;
               V_RATPROD := 'S';
               BEGIN
                 SELECT CDFILIAL
                   INTO V_CDFILIAL
                   FROM RATEIONF
                  WHERE CDFILIAL    = P_CDFILIAL
                    AND NRLANCTONF  = P_NRLANCTONF
                    AND NRSEQITPROD = C_PROD_CONTA.NRSEQITPROD
                    AND CDFILICCUST = V_CDFILIRATE
                    AND CDCENTCUSNF = C_PADRAT.CDCENTCUST;
               EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDFILIAL := NULL;
               END;
               IF V_CDFILIAL IS NULL THEN
                  INSERT INTO RATEIONF
                     (CDFILIAL,    NRLANCTONF, NRSEQITPROD,
                      CDFILICCUST, VRRATENF,   CDCENTCUSNF, VRPERRAT)
                  VALUES
                  (P_CDFILIAL,  P_NRLANCTONF, C_PROD_CONTA.NRSEQITPROD,
                   V_CDFILIRATE, (C_PROD_CONTA.VRCONTITEM * (C_PADRAT.VRPERATTPCTP/100)), C_PADRAT.CDCENTCUST, C_PADRAT.VRPERATTPCTP);
               ELSE
                  UPDATE RATEIONF
                     SET VRRATENF = VRRATENF + (C_PROD_CONTA.VRCONTITEM * (C_PADRAT.VRPERATTPCTP/100)),
                         VRPERRAT = VRPERRAT + C_PADRAT.VRPERATTPCTP
                   WHERE CDFILIAL     = P_CDFILIAL
                     AND NRLANCTONF   = P_NRLANCTONF
                     AND NRSEQITPROD  = C_PROD_CONTA.NRSEQITPROD
                     AND CDFILICCUST  = V_CDFILIRATE
                     AND CDCENTCUSNF  = C_PADRAT.CDCENTCUST;
               END IF;
            END LOOP;
         ELSE
           /*BUSCA O CENTRO DE CUSTO DA FILIAL. SE NAO PARAMETRIZADO, PEGA O DA EMPRESA*/
            BEGIN
               SELECT MAX(CDFILIAL) AS CDFILIAL, MAX(CDCENTCUST) AS CDCENTCUST
                 INTO V_CDFILIRATE, V_CDCENTCUST
                 FROM
                 (SELECT F.CDFILIAL, NVL(F.CDCENTCUST, E.CDCENTCUST) AS CDCENTCUST
                   FROM FILIAL F, EMPRESA E
                  WHERE F.CDFILIAL   = V_CDFILIRECE AND
                        E.CDEMPRESA  = V_CDEMPRESA  AND
                        F.CDEMPRESA  = E.CDEMPRESA
                  UNION
                 SELECT F.CDFILIAL, NVL(F.CDCENTCUST, E.CDCENTCUST) AS CDCENTCUST
                   FROM FILIAL F, EMPRESA E, EMPRCNTRFIL EC
                  WHERE EC.CDFILIAL   = V_CDFILIRECE AND
                        EC.CDEMPRESA  = V_CDEMPRESA  AND
                        E.CDEMPRESA   = EC.CDEMPRESA AND
                        F.CDFILIAL = EC.CDFILIAL);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CDFILIRATE := NULL;
                  V_CDCENTCUST := NULL;
            END;
            V_VRACUMULADO := 0;
            V_VRPARCELA   := 0;
            V_VRPARCELA   := C_PROD_CONTA.VRCONTITEM;
            V_VRACUMULADO := V_VRACUMULADO + C_PROD_CONTA.VRCONTITEM;
         IF (V_VRPARCELA > 0) THEN
            IF NOT (V_CDFILIRATE IS NULL) AND NOT (V_CDCENTCUST IS NULL) THEN
               BEGIN
                 SELECT CDFILIAL
                   INTO V_CDFILIAL
                   FROM RATEIONF
                  WHERE CDFILIAL     = P_CDFILIAL
                    AND NRLANCTONF   = P_NRLANCTONF
                    AND NRSEQITPROD = C_PROD_CONTA.NRSEQITPROD
                    AND CDFILICCUST = V_CDFILIRATE
                    AND CDCENTCUSNF = V_CDCENTCUST;
               EXCEPTION WHEN NO_DATA_FOUND THEN
                   V_CDFILIAL := NULL;

               END;
               IF V_CDFILIAL IS NULL THEN
                 INSERT INTO RATEIONF
                     (CDFILIAL,    NRLANCTONF, NRSEQITPROD,
                      CDFILICCUST, VRRATENF,   CDCENTCUSNF, VRPERRAT)
                 VALUES
                     (P_CDFILIAL,  P_NRLANCTONF, C_PROD_CONTA.NRSEQITPROD,
                      V_CDFILIRATE, V_VRPARCELA, V_CDCENTCUST, ((V_VRPARCELA*100)/V_VRACUMULADO));
               ELSE
                 UPDATE RATEIONF
                    SET VRRATENF = VRRATENF + V_VRPARCELA,
                        VRPERRAT = VRPERRAT + ((V_VRPARCELA*100)/V_VRACUMULADO)
                  WHERE CDFILIAL     = P_CDFILIAL
                    AND NRLANCTONF   = P_NRLANCTONF
                    AND NRSEQITPROD  = C_PROD_CONTA.NRSEQITPROD
                    AND CDFILICCUST  = V_CDFILIRATE
                    AND CDCENTCUSNF  = V_CDCENTCUST;
               END IF;
            END IF;
         END IF;
      END IF;
      END LOOP;
     -- Se considera tipo de conta para geracao de rateio contabil
      IF (V_IDCONSCTBRAT = 'S' AND V_RATPROD = 'N' )THEN
         -- verifica o tipo de conta a pagar
         BEGIN
            SELECT TR.CDTPCTPAGAR
              INTO V_CDTPCTPAGAR
              FROM (SELECT MAX(CDARVPROD) AS CDARVPROD
                      FROM ITEMPRODUTO I, PRODUTO P
                     WHERE CDFILIAL    = P_CDFILIAL AND
                           NRLANCTONF  = P_NRLANCTONF AND
                           I.CDPRODUTO = P.CDPRODUTO
                     GROUP BY VRTOTAITEM
                    HAVING VRTOTAITEM = (SELECT MAX(VRTOTAITEM)
                                           FROM ITEMPRODUTO
                                          WHERE CDFILIAL    = P_CDFILIAL AND
                                                NRLANCTONF  = P_NRLANCTONF)) IT,
                   PRODUTO PR , TIPOCTPAGAR TR
             WHERE TR.CDTPCTPAGAR = PR.CDTPCTPAGAR AND
                   PR.CDARVPROD = (SELECT MAX(P.CDARVPROD)
                                     FROM TIPOCTRECEPROD C, PRODUTO P
                                    WHERE C.CDPRODUTO = P.CDPRODUTO AND
                                     (INSTR(IT.CDARVPROD, RTRIM(P.CDARVPROD)) = 1));
         EXCEPTION WHEN NO_DATA_FOUND THEN
             V_CDTPCTPAGAR := NULL;
         END;
         -- Se possuir conta parametrizada
         IF NOT V_CDTPCTPAGAR IS NULL THEN
            DELETE RATEIONF
             WHERE CDFILIAL   = P_CDFILIAL
               AND NRLANCTONF = P_NRLANCTONF;
            FOR C_PROD IN (SELECT CDFILIAL, NRLANCTONF, NRSEQITPROD, VRCONTITEM, DSPRODEMIS
                             FROM ITEMPRODUTO
                            WHERE CDFILIAL   = P_CDFILIAL
                              AND NRLANCTONF = P_NRLANCTONF)
            LOOP
               FOR C_RAT IN (SELECT CDCENTCUST, VRPERATTPCTP, CDFILIAL
                               FROM RATTPCTP
                              WHERE CDTPCTPAGAR  = V_CDTPCTPAGAR)
               LOOP
                  IF NOT C_RAT.CDFILIAL IS NULL THEN
                     V_CDFILIRATE := C_RAT.CDFILIAL;
                  ELSE
                     V_CDFILIRATE := V_CDFILIRECE;
                  END IF;
                   
               BEGIN
                  SELECT CDFILIAL
                    INTO V_CDFILIAL
                    FROM RATEIONF
                   WHERE CDFILIAL    = P_CDFILIAL
                     AND NRLANCTONF  = P_NRLANCTONF
                     AND NRSEQITPROD = C_PROD.NRSEQITPROD
                     AND CDFILICCUST = V_CDFILIRATE
                     AND CDCENTCUSNF = C_RAT.CDCENTCUST;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_CDFILIAL := NULL;
               END;
                     
                     
                  IF V_CDFILIAL IS NULL THEN
                     INSERT INTO RATEIONF
                        (CDFILIAL,    NRLANCTONF, NRSEQITPROD,
                         CDFILICCUST, VRRATENF,   CDCENTCUSNF, VRPERRAT)
                     VALUES
                     (P_CDFILIAL,  P_NRLANCTONF, C_PROD.NRSEQITPROD,
                      V_CDFILIRATE, (C_PROD.VRCONTITEM * (C_RAT.VRPERATTPCTP/100)), C_RAT.CDCENTCUST, C_RAT.VRPERATTPCTP);
                  ELSE
                     UPDATE RATEIONF
                        SET VRRATENF = VRRATENF + (C_PROD.VRCONTITEM * (C_RAT.VRPERATTPCTP/100)),
                            VRPERRAT = VRPERRAT + C_RAT.VRPERATTPCTP
                      WHERE CDFILIAL     = P_CDFILIAL
                        AND NRLANCTONF   = P_NRLANCTONF
                        AND NRSEQITPROD  = C_PROD.NRSEQITPROD
                        AND CDFILICCUST  = V_CDFILIRATE
                        AND CDCENTCUSNF  = C_RAT.CDCENTCUST;
                  END IF;
               END LOOP;
            END LOOP;
         END IF;
      END IF;
   END IF;
END IF;
END;
/