/*
  @C:\Web_635\SQL\END_CONV\ORACLE\400A_ESTTRGO_ATUALIZA_DELETE_LOGMOVIEST.SQL

  raise_application_error(-20501, '' , True);

*/
CREATE OR REPLACE TRIGGER ATUALIZA_DELETE_LOGMOVIEST 
BEFORE DELETE ON ITLANCTOEST
FOR EACH ROW
DECLARE
   DTATUAL         DATE;
   V_IDCTRLESTQLG  VARCHAR2(1);
   V_IDESONLINELG  VARCHAR2(1);
   V_IDPESTNEGALG  VARCHAR2(1);

   V_COUNT         NUMBER;

   V_VRINDICE1     NUMBER;
   V_VRINDICE2     NUMBER;
   V_CDINDICEMF    VARCHAR2(04);
   V_CDINDICEMF2   VARCHAR2(04);
   V_CDTPINDICE    VARCHAR2(01);
   V_IDPERVALOR    VARCHAR2(01);
   V_CDTPINDICE2   VARCHAR2(01);
   V_IDPERVALOR2   VARCHAR2(01);

   V_VRULTCMPLIQ   NUMBER;
   V_VRULTCMPBRU   NUMBER;
   V_VRULTCMPLM1   NUMBER;
   V_VRULTCMPLM2   NUMBER;
   V_VRULTCMPBM1   NUMBER;
   V_VRULTCMPBM2   NUMBER;

   V_VRFATOCONV    NUMBER;
   V_DIFERENCA     NUMBER;
   V_FATOCONV      NUMBER;
   V_LANCTOEST     NUMBER;
   V_VRLIQUIDO     NUMBER;
   V_VRLANCTOBRUT  NUMBER;
   V_QTTOTLANCTO   NUMBER;
   V_VRNOTAFISC    NUMBER;
   V_QUANTIDADE    NUMBER;
   V_VRTOTLANCTO   NUMBER;
   V_VRUNILANCTO   NUMBER;
   V_VRLANCTOEST   NUMBER;

   V_CDFILIMOVI    VARCHAR2(04);
   V_DTLANCMOVI    DATE;
   V_CDFILIAL      VARCHAR2(04);
   V_NRLANCESTQ    VARCHAR2(10);
   V_CDPRODUTO     VARCHAR2(10);
   V_CDALMOXARIFE  VARCHAR2(06);
   V_CDLOCALESTOQ  VARCHAR2(06);
   V_NRLOTEESTQ    VARCHAR2(20);
   V_NRSUBLOTE     VARCHAR2(04);
   V_NRLANCTONF    VARCHAR2(10);
   V_VRPESOBRUTPR  NUMBER;
   V_VRPECUSTOLOG  NUMBER;
   V_VRVRCUSTOLOG  NUMBER;
   V_VRPESEGURLOG  NUMBER;
   V_CDFORNECED    VARCHAR2(21);
   V_VRTOTAITEM    NUMBER;
   V_VRCUSTLCMPM1  NUMBER;
   V_VRSEGULCMPM1  NUMBER;
   V_VRCUSTLCMPM2  NUMBER;
   V_VRSEGULCMPM2  NUMBER;
   V_VRCUSTOLOG    NUMBER;
   V_VRCUSTOSEG    NUMBER;
   V_VRDESCITEM    NUMBER;
   V_RESDIVVRQTD   NUMBER;

BEGIN
   DTATUAL := TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY HH24MISS'),'DD/MM/YYYY HH24MISS');

   SELECT IDCTRLESTQ, IDESTONLINE, IDPERMESTNEG
     INTO V_IDCTRLESTQLG, V_IDESONLINELG, V_IDPESTNEGALG
     FROM PARAMFILIAL
    WHERE CDFILIAL = :OLD.CDFILIMOVI;

   SELECT COUNT(*)
     INTO V_COUNT
     FROM MOVILOTE
    WHERE NRMOVILOTE = :OLD.CDFILIAL || :OLD.NRLANCESTQ 
      AND CDFILIAL = :OLD.CDFILIMOVI
      AND CDALMOXARIFE = :OLD.CDALMOXARIFE
      AND CDLOCALESTOQ = :OLD.CDLOCALESTOQ
      AND NRLOTEESTQ = :OLD.NRLOTEESTQ
      AND NRSUBLOTE = :OLD.NRSUBLOTE
      AND CDPRODUTO = :OLD.CDPRODMOVI;

   IF V_COUNT = 0 THEN
      IF (:OLD.IDTIPOMOVI IN ('0', '1')) OR (:OLD.QTTOTLANCTO > 0) THEN
         INSERT INTO MOVILOTE
            (NRMOVILOTE, CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ, CDPRODUTO,
             NRLOTEESTQ, NRSUBLOTE, NRLANCTOORIG, DTMOVILOTE, DTMOVILOTEANT, CDSITUTRIBMOV)
         VALUES
            (:OLD.CDFILIAL || :OLD.NRLANCESTQ, :OLD.CDFILIMOVI, :OLD.CDALMOXARIFE, :OLD.CDLOCALESTOQ, :OLD.CDPRODMOVI,
             :OLD.NRLOTEESTQ, :OLD.NRSUBLOTE, :OLD.NRLANCORIGRP, :OLD.DTLANCMOVI, :OLD.DTLANCMOVI, :OLD.CDSITUTRIBEST);
      END IF;
   END IF;

   SELECT CDTPINDICE, IDPERVALOR, CDINDICEMF,
          CDTPINDICE2, IDPERVALOR2, CDINDICEMF2
     INTO V_CDTPINDICE, V_IDPERVALOR, V_CDINDICEMF,
          V_CDTPINDICE2, V_IDPERVALOR2, V_CDINDICEMF2
     FROM PARAMGERAL;

   SELECT COUNT(*)
     INTO V_COUNT
     FROM CUSTOCMPPROD
    WHERE CDFILLANCEST = :OLD.CDFILIAL
      AND NRLANCESTQ = :OLD.NRLANCESTQ
      AND CDFILIAL = :OLD.CDFILIMOVI
      AND CDALMOXARIFE = :OLD.CDALMOXARIFE
      AND CDLOCALESTOQ = :OLD.CDLOCALESTOQ
      AND NRLOTEESTQ = :OLD.NRLOTEESTQ
      AND NRSUBLOTE = :OLD.NRSUBLOTE
      AND CDPRODUTO = :OLD.CDPRODMOVI
      AND DTCUSTCMPPRO = :OLD.DTLANCMOVI;

   IF (V_COUNT > 0) THEN
      V_VRPESOBRUTPR  := 0;
      V_VRPECUSTOLOG  := 0;
      V_VRVRCUSTOLOG  := 0;
      V_VRPESEGURLOG  := 0;
      V_VRTOTAITEM    := 0;
      V_VRCUSTLCMPM1  := 0;
      V_VRSEGULCMPM1  := 0;
      V_VRCUSTLCMPM1  := 0;
      V_VRSEGULCMPM1  := 0;
      V_VRCUSTOLOG    := 0;
      V_VRCUSTOSEG    := 0;

      DELETE FROM CUSTOCMPPROD
       WHERE CDFILLANCEST = :OLD.CDFILIAL
         AND NRLANCESTQ = :OLD.NRLANCESTQ
         AND CDALMOXARIFE = :OLD.CDALMOXARIFE
         AND CDLOCALESTOQ = :OLD.CDLOCALESTOQ
         AND NRLOTEESTQ = :OLD.NRLOTEESTQ
         AND NRSUBLOTE = :OLD.NRSUBLOTE
         AND CDPRODUTO = :OLD.CDPRODMOVI
         AND DTCUSTCMPPRO = :OLD.DTLANCMOVI;

      SELECT MAX(N.DTENTRSAID), MAX(A.CDPRODMOVI), MAX(A.VRCONTITEM), MAX(B.VRFATOCONV), MAX(QTITEMPROD),
             MAX(A.CDALMOXARIFE), MAX(A.CDLOCALESTOQ), MAX(A.NRLOTEESTQ), MAX(A.NRSUBLOTE), MAX(D.NRLANCESTQ),
             MAX(D.NRLANCTONF), MAX(N.CDFORNECED), MAX(VRTOTAITEM), MAX(VRDESCITEM)
        INTO V_DTLANCMOVI, V_CDPRODUTO, V_VRLANCTOBRUT, V_VRFATOCONV, V_QTTOTLANCTO,
             V_CDALMOXARIFE, V_CDLOCALESTOQ, V_NRLOTEESTQ, V_NRSUBLOTE, V_NRLANCESTQ,
             V_NRLANCTONF, V_CDFORNECED, V_VRTOTAITEM, V_VRDESCITEM
        FROM ITEMPRODUTO A, NOTAFISCAL N, PRODUTO B, PRODFILI C, LANCTOESTOQ D
       WHERE (A.CDFILIAL = N.CDFILIAL)
         AND (A.NRLANCTONF = N.NRLANCTONF)
         AND (N.NRLANCTONF = D.NRLANCTONF)
         AND (N.CDFILIAL = D.CDFILIAL)
         AND (D.IDTPLANCTO IN ('0','2','E'))
         AND (A.CDPRODMOVI = :OLD.CDPRODMOVI)
         AND (A.CDFILIMOVI = :OLD.CDFILIMOVI)
         AND (A.CDALMOXARIFE = :OLD.CDALMOXARIFE)
         AND (A.CDLOCALESTOQ = :OLD.CDLOCALESTOQ)
         AND (A.NRLOTEESTQ = :OLD.NRLOTEESTQ)
         AND (A.NRSUBLOTE = :OLD.NRSUBLOTE)
         AND (N.DTENTRSAID = :OLD.DTLANCMOVI)
         AND (B.CDPRODUTO = A.CDPRODUTO)
         AND (C.CDFILIAL = N.CDFILIRECE)
         AND (C.CDPRODUTO = B.CDPRODESTO)
         AND (D.CDFILIAL||D.NRLANCESTQ <> :OLD.CDFILIAL||:OLD.NRLANCESTQ);

      IF NOT (V_VRLANCTOEST IS NULL) THEN
         SELECT CDFILIAL
           INTO V_CDFILIAL
           FROM LANCTOESTOQ
          WHERE NRLANCESTQ  = V_NRLANCESTQ
            AND CDFILIMOVI  = :OLD.CDFILIMOVI;

         V_VRTOTLANCTO := V_VRTOTAITEM - V_VRDESCITEM;

         IF V_QTTOTLANCTO <> 0 THEN
            V_RESDIVVRQTD := V_VRLANCTOBRUT / V_QTTOTLANCTO;
            V_VRUNILANCTO := V_VRTOTLANCTO / V_QTTOTLANCTO;
         END IF;

         IF V_VRFATOCONV <> 0 THEN
            V_VRLANCTOEST := V_RESDIVVRQTD / V_VRFATOCONV;
         END IF;

         SELECT MAX(P.VRPESOBRUTPR), NVL(MAX(VRPECUSTOLOG),0), NVL(MAX(VRVRCUSTOLOG),0), NVL(MAX(VRPESEGURLOG),0)
           INTO V_VRPESOBRUTPR, V_VRPECUSTOLOG, V_VRVRCUSTOLOG, V_VRPESEGURLOG
           FROM CUSTOLOGIST C, PRODUTO P
          WHERE C.CDPRODUTO = :OLD.CDPRODUTO
            AND P.CDPRODUTO = C.CDPRODUTO
            AND C.CDFILIAL = :OLD.CDFILIMOVI
            AND C.CDFORNECED = V_CDFORNECED
            AND C.DTINIVIGCLOG = (SELECT MAX(DTINIVIGCLOG)
                                    FROM CUSTOLOGIST A
                                   WHERE A.CDFILIAL = C.CDFILIAL
                                     AND A.CDFORNECED = C.CDFORNECED
                                     AND A.CDPRODUTO = :OLD.CDPRODUTO
                                     AND A.DTINIVIGCLOG <= :OLD.DTLANCMOVI);

         IF (V_VRVRCUSTOLOG > 0) AND (V_VRPESOBRUTPR > 0) THEN
            V_VRCUSTOLOG := ((V_QTTOTLANCTO *V_VRPESOBRUTPR)*V_VRVRCUSTOLOG)/V_QTTOTLANCTO;
         END IF;

         IF (V_VRPECUSTOLOG > 0) THEN
            V_VRCUSTOLOG := ((V_VRTOTAITEM * V_VRPECUSTOLOG)/100)/V_QTTOTLANCTO;
         END IF;

         IF (V_VRPESEGURLOG > 0) THEN
            V_VRCUSTOSEG := ((V_VRTOTAITEM * V_VRPESEGURLOG)/100)/V_QTTOTLANCTO;
         END IF;

         SELECT SUM(VRTOTAITEM - VRDESCITEM) AS VRLIQUIDO
           INTO V_VRLIQUIDO
           FROM ITEMPRODUTO
          WHERE CDFILIAL = V_CDFILIAL
            AND NRLANCTONF = V_NRLANCTONF;

         SELECT VRNOTAFISC
           INTO V_VRNOTAFISC
           FROM NOTAFISCAL
          WHERE CDFILIAL = V_CDFILIAL
            AND NRLANCTONF = V_NRLANCTONF;

         V_DIFERENCA := V_VRNOTAFISC - V_VRLIQUIDO;

         IF V_QTTOTLANCTO = 0 THEN
            V_QUANTIDADE := 1;
         ELSE
            V_QUANTIDADE := V_QTTOTLANCTO;
         END IF;

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

         V_LANCTOEST := 0;

         IF NOT (V_VRLIQUIDO = 0) THEN
            IF NOT(V_DIFERENCA IS NULL) THEN
               V_DIFERENCA  := ((V_DIFERENCA / V_VRLIQUIDO * V_VRTOTLANCTO / V_QUANTIDADE) / (V_FATOCONV));
            END IF;

            IF V_VRLANCTOBRUT = 0 THEN
               V_LANCTOEST := 0;
            ELSE
               V_LANCTOEST := V_DIFERENCA;
            END IF;
         END IF;

         V_VRULTCMPLIQ := V_VRLANCTOEST  + V_LANCTOEST;
         V_VRULTCMPBRU := V_VRLANCTOBRUT + V_LANCTOEST;

         IF (V_CDINDICEMF IS NOT NULL) THEN
            SELECT MAX(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 I.CDTPINDICE = M.CDTPINDICE
               AND I.CDTPINDICE = M.IDPERVALOR
               AND I.CDTPINDICE = M.CDINDICE
               AND V_DTLANCMOVI = M.DTREFIND;

            V_VRULTCMPLM1 := (V_VRLANCTOEST + V_LANCTOEST) * V_VRINDICE1;
            V_VRULTCMPBM1 := (V_VRLANCTOBRUT + V_LANCTOEST) * V_VRINDICE1;

            V_VRCUSTLCMPM1 := V_VRCUSTOLOG * V_VRINDICE1;
            V_VRSEGULCMPM1 := V_VRCUSTOSEG * V_VRINDICE1;

         END IF;
         IF (V_CDINDICEMF2 IS NOT NULL) THEN
            V_VRULTCMPLM2 := (V_VRLANCTOEST +  V_LANCTOEST) * V_VRINDICE2;
            V_VRULTCMPBM2 := (V_VRLANCTOBRUT + V_LANCTOEST) * V_VRINDICE2;

            V_VRCUSTLCMPM1 := V_VRCUSTOLOG * V_VRINDICE2;
            V_VRSEGULCMPM1 := V_VRCUSTOSEG * V_VRINDICE2;
         END IF;

         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
            (:OLD.CDFILIMOVI, :OLD.DTLANCMOVI, V_CDFILIAL, V_NRLANCESTQ, :OLD.CDPRODMOVI, :OLD.CDALMOXARIFE, :OLD.CDLOCALESTOQ,
             :OLD.NRLOTEESTQ, :OLD.NRSUBLOTE, V_VRULTCMPLIQ, V_VRULTCMPBRU, V_VRULTCMPLM1, V_VRULTCMPLM2, V_VRULTCMPBM1,
             V_VRULTCMPBM2, V_VRCUSTOLOG, V_VRCUSTLCMPM1, V_VRCUSTLCMPM2, V_VRCUSTOSEG, V_VRSEGULCMPM1, V_VRSEGULCMPM2);

      END IF;
   END IF;
END;
/
