CREATE OR REPLACE PROCEDURE ATUALIZA_CUSTO_COMPRA(
   P_FILIAL       IN FILIAL.CDFILIAL%TYPE,
   P_PRODINI      IN PRODUTO.CDARVPROD%TYPE,
   P_PRODFIM      IN PRODUTO.CDARVPROD%TYPE,
   P_CDALMOXARIFE IN ALMOXARIFADO.CDALMOXARIFE%TYPE,
   P_CDLOCALESTOQ IN LOCALESTOQUE.CDLOCALESTOQ%TYPE,
   P_TALMOX       IN VARCHAR2,
   P_TLOCAL       IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE)
AS
   V_DATA DATE;
   V_FATOCONV      NUMBER;
   V_VRULTCMPLIQ   NUMBER;
   V_VRULTCMPBRU   NUMBER;
   V_VRULTCMPLM1   NUMBER;
   V_VRULTCMPLM2   NUMBER;
   V_VRULTCMPBM1   NUMBER;
   V_VRULTCMPBM2   NUMBER;
   V_VRCUSTLCMPM1  NUMBER;
   V_VRCUSTLCMPM2  NUMBER;
   V_VRSEGULCMPM1  NUMBER;
   V_VRSEGULCMPM2  NUMBER;
   V_VRINDICE1     NUMBER;
   V_VRINDICE2     NUMBER;
   V_NRSEQUITEM   VARCHAR2(4);
   V_CDPRODUTO    VARCHAR2(10);
   V_VRLANCTOEST  NUMBER;
   V_VRLANCTOBRUT NUMBER;
   V_VRFATOCONV   NUMBER;
   V_NRLANCTONF   VARCHAR2(10);
   V_QTTOTLANCTO  FLOAT;
   V_VRTOTLANCTO  NUMBER;
   V_VRUNILANCTO  NUMBER;
   V_CDFILIMOVI   VARCHAR2(4);
   V_IDTPLANCTO   VARCHAR2(1);
   V_NRLANCESTQ   VARCHAR2(10);
   V_CDFILIAL     VARCHAR2(4);
   V_VRCUSTLOGLAN NUMBER;
   V_VRSEGULOGLAN NUMBER;
   V_IDPERVALOR   VARCHAR2(01);
   V_IDPERVALOR2  VARCHAR2(01);
   V_CDINDICEMF   VARCHAR2(04);
   V_CDINDICEMF2  VARCHAR2(04);
   V_CDTPINDICE   VARCHAR2(01);
   V_CDTPINDICE2   VARCHAR2(01);

BEGIN
   BEGIN
      SELECT NVL(CDTPINDICE,'1'),  NVL(IDPERVALOR,'2'),  CDINDICEMF,
                NVL(CDTPINDICE2,'1'), NVL(IDPERVALOR2,'2'), CDINDICEMF2
         INTO V_CDTPINDICE,  V_IDPERVALOR,  V_CDINDICEMF,
              V_CDTPINDICE2, V_IDPERVALOR2, V_CDINDICEMF2
         FROM PARAMGERAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDTPINDICE  := Null;  V_IDPERVALOR  := Null;  V_CDINDICEMF   := Null;
            V_CDTPINDICE2 := Null;  V_IDPERVALOR2 := Null;   V_CDINDICEMF2 := NULL;
         END;
   END;

   FOR C_PRODUTO IN(SELECT DISTINCT PF.CDPRODUTO
                   FROM PRODUTO P, PRODFILI PF
                  WHERE P.CDARVPROD BETWEEN P_PRODINI AND P_PRODFIM
                    AND CDFILIAL = P_FILIAL
                    AND NVL(PF.IDCNTRESTOQ,'S') = 'S'
                    AND NVL(P.IDPRODATIVPR,'S') = 'S'
                    AND P.CDPRODESTO = PF.CDPRODUTO) LOOP

      DELETE FROM CUSTOCMPPROD
       WHERE CDFILIAL     = P_FILIAL
         AND ((CDALMOXARIFE = P_CDALMOXARIFE) OR (P_TALMOX = 'T'))
         AND ((CDLOCALESTOQ = P_CDLOCALESTOQ) OR (P_TLOCAL = 'T'))
         AND CDPRODUTO    = C_PRODUTO.CDPRODUTO
         AND DTCUSTCMPPRO BETWEEN P_DTINICIAL AND P_DTFINAL;

      V_DATA := P_DTINICIAL;
      WHILE(V_DATA<=P_DTFINAL) LOOP
         FOR C_INSERE IN(SELECT NRLANCESTQ, NRLOTEESTQ, NRSUBLOTE, MAX(NRSEQUITEM) NRSEQUITEM,
                                CDALMOXARIFE, CDLOCALESTOQ
                           FROM ITLANCTOEST I
                          WHERE I.CDFILIMOVI = P_FILIAL
                            AND I.NRLANCESTQ = (SELECT MAX(NRLANCESTQ)
                                                 FROM ITLANCTOEST I
                                                WHERE CDFILIMOVI     = P_FILIAL
                                                  AND ((CDALMOXARIFE = P_CDALMOXARIFE) OR (P_TALMOX = 'T'))
                                                  AND ((CDLOCALESTOQ = P_CDLOCALESTOQ) OR (P_TLOCAL = 'T'))
                                                  AND CDPRODMOVI     = C_PRODUTO.CDPRODUTO
                                                  AND DTLANCMOVI     = V_DATA
                                                  AND IDTIPOMOVI IN ('0','2','E')
                                                  AND IDEVOLESTOQ = 'S')
                            AND I.CDPRODMOVI = C_PRODUTO.CDPRODUTO
                          GROUP BY NRLANCESTQ, NRLOTEESTQ, NRSUBLOTE,
                                   CDALMOXARIFE, CDLOCALESTOQ) LOOP

            BEGIN
               SELECT NVL(A.VRLANCTOEST,0) AS VRLANCTOEST, NVL(A.VRLANCTOBRUT,0) AS VRLANCTOBRUT,
                      B.VRFATOCONV,  QTTOTLANCTO,   A.VRTOTLANCTO,  A.VRUNILANCTO,
                      A.CDFILIMOVI,  D.IDTPLANCTO,  D.CDFILIAL,
                      DECODE(QTTOTLANCTO, 0, 0, (VRCUSTLOGLAN/QTTOTLANCTO)) AS VRCUSTLOGLAN,
                      DECODE(QTTOTLANCTO, 0, 0, (VRSEGULOGLAN /QTTOTLANCTO)) AS VRSEGULOGLAN
                 INTO V_VRLANCTOEST, V_VRLANCTOBRUT, V_VRFATOCONV, V_QTTOTLANCTO, V_VRTOTLANCTO,
                      V_VRUNILANCTO, V_CDFILIMOVI,  V_IDTPLANCTO, V_CDFILIAL, V_VRCUSTLOGLAN,
                      V_VRSEGULOGLAN
                 FROM ITLANCTOEST A, PRODUTO B, PRODFILI C, LANCTOESTOQ D
                WHERE (A.CDFILIMOVI   = P_FILIAL)            AND
                      (A.NRLANCESTQ = C_INSERE.NRLANCESTQ) AND
                      (B.CDPRODUTO  = A.CDPRODUTO)         AND
                      (C.CDFILIAL   = A.CDFILIMOVI)        AND
                      (C.CDPRODUTO  = A.CDPRODMOVI)        AND
                      (D.CDFILIAL   = A.CDFILIAL)          AND
                      (D.NRLANCESTQ = A.NRLANCESTQ)        AND
                      (D.IDTPLANCTO IN ('0','2', 'E'))     AND
                      (A.NRSEQUITEM = C_INSERE.NRSEQUITEM);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN BEGIN
                  V_VRLANCTOEST  := NULL;
                  V_VRLANCTOBRUT := NULL;
                  V_VRFATOCONV   := NULL;
                  V_QTTOTLANCTO  := NULL;
                  V_VRTOTLANCTO  := NULL;
                  V_VRUNILANCTO  := NULL;
                  V_CDFILIMOVI   := NULL;
                  V_IDTPLANCTO   := NULL;
                  V_CDFILIAL     := NULL;
                  V_VRCUSTLOGLAN := NULL;
                  V_VRSEGULOGLAN := NULL;
               END;
            END;
            IF (V_CDFILIAL IS NOT NULL) THEN
               V_VRULTCMPLIQ := 0;
               V_VRULTCMPBRU := 0;
               V_VRULTCMPLM1 := 0;
               V_VRULTCMPLM2 := 0;
               V_VRULTCMPBM1 := 0;
               V_VRULTCMPBM2 := 0;

               IF (V_CDINDICEMF IS NOT NULL) THEN
                  BEGIN
                     SELECT VRINDICE
                       INTO V_VRINDICE1
                       FROM INDICE I, MOVINDICE M
                      WHERE I.CDTPINDICE = V_CDTPINDICE            AND
                            I.IDPERVALOR = V_IDPERVALOR            AND
                            I.CDINDICE   = V_CDINDICEMF            AND
                            M.CDTPINDICE = I.CDTPINDICE            AND
                            M.IDPERVALOR = I.IDPERVALOR            AND
                            M.CDINDICE   = I.CDINDICE              AND
                            TO_CHAR(M.DTREFIND,'DD/MM/YYYY')   = TO_CHAR(V_DATA,'DD/MM/YYYY');
                     EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                           V_VRINDICE1 := 0;
                     END;

                     IF (V_VRINDICE1 = 0) THEN
                        raise_application_error(-20501, ' Indice Financeiro Parametrizado na Personalizacao do Sistema, nao Possui valor Cadastrado para a Data '||  TO_CHAR(V_DATA,'DD/MM/YYYY')  , True);
                     END IF;
               END IF;

               IF (V_CDINDICEMF2 IS NOT NULL) THEN
                  BEGIN
                     SELECT VRINDICE
                     INTO V_VRINDICE2
                     FROM  INDICE I, MOVINDICE M
                     WHERE I.CDTPINDICE = V_CDTPINDICE2    AND
                           I.IDPERVALOR = V_IDPERVALOR2    AND
                           I.CDINDICE   = V_CDINDICEMF2    AND
                           M.CDTPINDICE = I.CDTPINDICE     AND
                           M.IDPERVALOR = I.IDPERVALOR     AND
                           M.CDINDICE   = I.CDINDICE       AND
                           TO_CHAR(M.DTREFIND,'DD/MM/YYYY') = TO_CHAR(V_DATA,'DD/MM/YYYY');
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_VRINDICE2 := 0;
                  END;

                  IF (V_VRINDICE2 = 0) THEN
                     raise_application_error(-20501, 'Indice Financeiro Parametrizado na Personalizacao do Sistema, nao Possui valor Cadastrado para a Data '||TO_CHAR(V_DATA,'DD/MM/YYYY'), True);
                  END IF;
               END IF;

               IF (V_VRFATOCONV IS NULL) OR (V_VRFATOCONV = 0) THEN
                  V_FATOCONV := 1;
               ELSE
                  V_FATOCONV := V_VRFATOCONV;
               END IF;

               IF (V_IDTPLANCTO <> '0') THEN
                  V_VRULTCMPLIQ := V_VRLANCTOEST;
                  V_VRULTCMPBRU := V_VRLANCTOBRUT;
               ELSE
                  IF (V_QTTOTLANCTO > 0) THEN
                     V_VRULTCMPLIQ := ((V_VRLANCTOEST/V_QTTOTLANCTO)/V_FATOCONV);
                     V_VRULTCMPBRU := ((V_VRLANCTOBRUT/V_QTTOTLANCTO)/V_FATOCONV);
                  END IF;
               END IF;

               IF (V_CDINDICEMF IS NOT NULL) THEN
                  IF (V_IDTPLANCTO <> '0') THEN
                     V_VRULTCMPLM1  := V_VRLANCTOEST  * V_VRINDICE1;
                     V_VRULTCMPBM1  := V_VRLANCTOBRUT * V_VRINDICE1;
                     V_VRCUSTLCMPM1 := V_VRCUSTLOGLAN * V_VRINDICE1;
                     V_VRSEGULCMPM1 := V_VRSEGULOGLAN * V_VRINDICE1;
                  ELSE
                     IF (V_QTTOTLANCTO > 0) THEN
                        V_VRULTCMPLM1 := (((V_VRLANCTOEST/V_QTTOTLANCTO)/V_FATOCONV))  * V_VRINDICE1;
                        V_VRULTCMPBM1 := (((V_VRLANCTOBRUT/V_QTTOTLANCTO)/V_FATOCONV)) * V_VRINDICE1;
                     END IF;
                     V_VRCUSTLCMPM1 := V_VRCUSTLOGLAN * V_VRINDICE1;
                     V_VRSEGULCMPM1 := V_VRSEGULOGLAN * V_VRINDICE1;
                  END IF;
               END IF;

               IF (V_CDINDICEMF2 IS NOT NULL) THEN
                  IF (V_IDTPLANCTO <> '0') THEN
                     V_VRULTCMPLM2  := V_VRLANCTOEST  * V_VRINDICE2;
                     V_VRULTCMPBM2  := V_VRLANCTOBRUT * V_VRINDICE2;
                     V_VRCUSTLCMPM2 := V_VRCUSTLOGLAN * V_VRINDICE2;
                     V_VRSEGULCMPM2 := V_VRSEGULOGLAN * V_VRINDICE2;
                  ELSE
                     IF (V_QTTOTLANCTO > 0) THEN
                        V_VRULTCMPLM2 := (((V_VRLANCTOEST/V_QTTOTLANCTO)/V_FATOCONV))  * V_VRINDICE2;
                        V_VRULTCMPBM2 := (((V_VRLANCTOEST/V_QTTOTLANCTO)/V_FATOCONV))  * V_VRINDICE2;
                     END IF;
                     V_VRCUSTLCMPM2 := V_VRCUSTLOGLAN * V_VRINDICE2;
                     V_VRSEGULCMPM2 := V_VRSEGULOGLAN * V_VRINDICE2;
                  END IF;
               END IF;

               IF (NVL(V_VRULTCMPLIQ,0)  <> 0) OR (NVL(V_VRULTCMPBRU,0)  <> 0) OR (NVL(V_VRULTCMPLM1,0) <> 0) OR (NVL(V_VRULTCMPBM1,0) <> 0) OR
                  (NVL(V_VRCUSTLCMPM1,0) <> 0) OR (NVL(V_VRSEGULCMPM1,0) <> 0) OR (NVL(V_VRULTCMPLM2,0) <> 0) OR (NVL(V_VRULTCMPBM2,0) <> 0) OR
                  (NVL(V_VRCUSTLCMPM2,0) <> 0) OR (NVL(V_VRSEGULCMPM2,0) <> 0) THEN

                  INSERT INTO CUSTOCMPPROD
                     (CDFILIAL,     DTCUSTCMPPRO,   CDFILLANCEST,   NRLANCESTQ,   CDPRODUTO,
                      CDALMOXARIFE, CDLOCALESTOQ,   NRLOTEESTQ,     NRSUBLOTE,    VRLIQCMPPROD,
                      VRBRUCMPPROD, VRLIQCMPPRM1,   VRLIQCMPPRM2,   VRBRUCMPPRM1, VRBRUCMPPRM2,
                      VRCUSTLOGCMP, VRCUSTLCMPM1,   VRCUSTLCMPM2,   VRSEGULOGCMP, VRSEGULCMPM1,
                      VRSEGULCMPM2)
                  VALUES
                     (V_CDFILIMOVI         , V_DATA               , V_CDFILIAL           , C_INSERE.NRLANCESTQ  ,
                      C_PRODUTO.CDPRODUTO  , C_INSERE.CDALMOXARIFE, C_INSERE.CDLOCALESTOQ, C_INSERE.NRLOTEESTQ  ,
                      C_INSERE.NRSUBLOTE   , NVL(V_VRULTCMPLIQ,0) , NVL(V_VRULTCMPBRU,0) , NVL(V_VRULTCMPLM1,0) ,
                      NVL(V_VRULTCMPLM2,0) , NVL(V_VRULTCMPBM1,0) , NVL(V_VRULTCMPBM2,0) , NVL(V_VRCUSTLOGLAN,0),
                      NVL(V_VRCUSTLCMPM1,0), NVL(V_VRCUSTLCMPM2,0), NVL(V_VRSEGULOGLAN,0), NVL(V_VRSEGULCMPM1,0),
                      NVL(V_VRSEGULCMPM2,0));

               END IF;
            END IF;
         END LOOP;
         V_DATA := V_DATA + 1;
      END LOOP;
   END LOOP;
END;
/