/* Tabela CTRLAPROVCARD: Controle de liberao e aprovao do cardapio */
/* Descrio: A trigger insere registros na tabela LOGOPERA a partir de alteraes na tabela CTRLAPROVCARD */
/* Objetivo: Gerar um log que vai registrar as alteraes na tabela de controle de liberao e aprovao do cardapio CTRLAPROVCARD,
             utilizando a tabela Geral de Logs de Operaes */
create or replace TRIGGER LOG_PLA_CTRLAPROVCARD
AFTER DELETE OR INSERT OR UPDATE 
ON CTRLAPROVCARD FOR EACH ROW 
DECLARE 
  V_USUARIO    VARCHAR2(40); 
  V_TIPO       VARCHAR2(1); 
  V_ITPESQ     VARCHAR2(40); 
  V_DESC       VARCHAR2(500); 
  V_DTOPER     DATE; 
  V_DSIP       VARCHAR2(50); 
  V_DSUSUARIO  VARCHAR2(50); 
  V_DSRDM      VARCHAR2(50); 
  V_DSMETODO   VARCHAR2(250); 
  V_DSOPERADOR VARCHAR2(12); 
BEGIN 
   V_TIPO      := '0'; 
   V_DESC      := ''; 
   WEBTUNNING(V_DSIP, 
              V_DSUSUARIO, 
              V_DSRDM, 
              V_DSMETODO, 
              V_DSOPERADOR); 
   IF DELETING THEN 
      V_DESC := V_DESC || 'Fili='  || :OLD.CDFILIAL; 
      V_DESC := V_DESC || ' Serv=' || :OLD.CDSERVFILI; 
      V_DESC := V_DESC || ' Mes=' || :OLD.DTMESCARDAPROV; 
      V_DESC := V_DESC || ' Rdm='      || V_DSRDM; 
      V_DESC := V_DESC || ' Mtodo='   || V_DSMETODO; 
      V_DESC := V_DESC || ' Ip='       || V_DSIP; 
      V_DESC := V_DESC || ' Usurio='  || V_DSUSUARIO; 
      V_DESC := V_DESC || ' Operador=' || V_DSOPERADOR; 
      V_TIPO := '3'; 
      V_ITPESQ := :OLD.CDFILIAL||:OLD.CDSERVFILI||TO_CHAR(:OLD.DTMESCARDAPROV, 'YYYYMMDD'); 
   ELSE 
      IF UPDATING THEN 
         IF (:OLD.CDFILIAL       <> :NEW.CDFILIAL  ) OR 
            (:OLD.CDSERVFILI     <> :NEW.CDSERVFILI) OR 
            (TO_CHAR(:OLD.DTMESCARDAPROV) <> TO_CHAR(:NEW.DTMESCARDAPROV)) OR 
            (NVL(:OLD.VRPUCTRLAPRCARD,-1)     <> NVL(:NEW.VRPUCTRLAPRCARD,-1)) OR      
            (NVL(:OLD.VRCUSTOLIB,-1)          <> NVL(:NEW.VRCUSTOLIB,-1)) OR 
            (NVL(:OLD.VRPUCTRLAPRCARDMATR,-1) <> NVL(:NEW.VRPUCTRLAPRCARDMATR,-1)) OR 
            (NVL(:OLD.CDOPERLIBECARDMATR,'') <> NVL(:NEW.CDOPERLIBECARDMATR,'')) OR 
            (NVL(TO_CHAR(:OLD.DTLIBECARDMATR),'')     <> NVL(TO_CHAR(:NEW.DTLIBECARDMATR),'')) OR 
            (NVL(:OLD.CDOPERAPROCARDFILI,'') <> NVL(:NEW.CDOPERAPROCARDFILI,'')) OR 
            (NVL(TO_CHAR(:OLD.DTAPROCARDFILI),'')     <> NVL(TO_CHAR(:NEW.DTAPROCARDFILI),'')) OR 
            (NVL(:OLD.DSOBSAPROCARDFILI,'')  <> NVL(:NEW.DSOBSAPROCARDFILI,'')) OR 
            (NVL(:OLD.CDOPERAPROCARDMATR,'') <> NVL(:NEW.CDOPERAPROCARDMATR,'')) OR
            (NVL(TO_CHAR(:OLD.DTAPROCARDMATR),'')     <> NVL(TO_CHAR(:NEW.DTAPROCARDMATR),'')) OR
            (NVL(:OLD.DSOBSAPROCARDMATR,'')  <> NVL(:NEW.DSOBSAPROCARDMATR,'')) OR
            (NVL(:OLD.CDTIPOCUSTO,'')        <> NVL(:NEW.CDTIPOCUSTO,'')) OR                       
            (NVL(:OLD.CDTIPOCUSTOMATR,'')    <> NVL(:NEW.CDTIPOCUSTOMATR,'')) THEN 
            V_TIPO   := '2'; 
            V_ITPESQ := :OLD.CDFILIAL||:OLD.CDSERVFILI||TO_CHAR(:OLD.DTMESCARDAPROV, 'YYYYMMDD'); 
         END IF;  
      ELSE 
         V_TIPO   := '1'; 
         V_ITPESQ := :NEW.CDFILIAL||:NEW.CDSERVFILI||TO_CHAR(:NEW.DTMESCARDAPROV, 'YYYYMMDD'); 
      END IF; 
      IF UPDATING THEN 
         IF (:OLD.CDFILIAL <> :NEW.CDFILIAL) THEN 
            V_DESC := V_DESC || 'Fili=' || :OLD.CDFILIAL || '|' || :NEW.CDFILIAL; 
         ELSE 
            V_DESC := V_DESC || 'Fili=' || :OLD.CDFILIAL; 
         END IF; 
      ELSE 
         V_DESC := V_DESC || 'Fili=' || :NEW.CDFILIAL; 
      END IF;  
      IF UPDATING THEN 
         IF (:OLD.CDSERVFILI <> :NEW.CDSERVFILI) THEN 
            V_DESC := V_DESC || ' Serv=' || :OLD.CDSERVFILI || '|' || :NEW.CDSERVFILI; 
         ELSE 
            V_DESC := V_DESC || ' Serv=' || :OLD.CDSERVFILI; 
         END IF; 
      ELSE 
         V_DESC := V_DESC || ' Serv=' || :NEW.CDSERVFILI; 
      END IF; 
      IF UPDATING THEN 
         IF (TO_CHAR(:OLD.DTMESCARDAPROV) <> TO_CHAR(:NEW.DTMESCARDAPROV)) THEN 
            V_DESC := V_DESC || ' Mes=' || TO_CHAR(:OLD.DTMESCARDAPROV) || '|' || TO_CHAR(:NEW.DTMESCARDAPROV); 
         ELSE 
            V_DESC := V_DESC || ' Mes=' || TO_CHAR(:OLD.DTMESCARDAPROV); 
         END IF; 
      ELSE 
         V_DESC := V_DESC || ' Mes=' || TO_CHAR(:NEW.DTMESCARDAPROV); 
      END IF; 
      IF (NVL(:NEW.CDOPERLIBECARDMATR, '') <> '') OR (UPDATING AND (NVL(:NEW.CDOPERLIBECARDMATR, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.CDOPERLIBECARDMATR, '') <> NVL(:NEW.CDOPERLIBECARDMATR, '')) THEN 
               V_DESC := V_DESC || ' OpLibM=' || :OLD.CDOPERLIBECARDMATR || '|' || :NEW.CDOPERLIBECARDMATR; 
            END IF;
         ELSE 
            V_DESC := V_DESC || ' OpLibM=' || :NEW.CDOPERLIBECARDMATR;
         END IF; 
      END IF;
      IF (NVL(TO_CHAR(:NEW.DTLIBECARDMATR), '') <> '') OR (UPDATING AND (NVL(TO_CHAR(:NEW.DTLIBECARDMATR), '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(TO_CHAR(:OLD.DTLIBECARDMATR), '') <> NVL(TO_CHAR(:NEW.DTLIBECARDMATR), '')) THEN 
               V_DESC := V_DESC || ' DtLibM=' || TO_CHAR(:OLD.DTLIBECARDMATR) || '|' || TO_CHAR(:NEW.DTLIBECARDMATR); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' DtLibM=' || TO_CHAR(:NEW.DTLIBECARDMATR); 
         END IF; 
      END IF;
      IF (NVL(:NEW.CDOPERAPROCARDFILI, '') <> '') OR (UPDATING AND (NVL(:NEW.CDOPERAPROCARDFILI, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.CDOPERAPROCARDFILI, '') <> NVL(:NEW.CDOPERAPROCARDFILI, '')) THEN 
               V_DESC := V_DESC || ' OpApF=' || :OLD.CDOPERAPROCARDFILI || '|' || :NEW.CDOPERAPROCARDFILI; 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' OpApF=' || :NEW.CDOPERAPROCARDFILI; 
         END IF; 
      END IF;
      IF (NVL(TO_CHAR(:NEW.DTAPROCARDFILI), '') <> '') OR (UPDATING AND (NVL(TO_CHAR(:NEW.DTAPROCARDFILI), '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(TO_CHAR(:OLD.DTAPROCARDFILI), '') <> NVL(TO_CHAR(:NEW.DTAPROCARDFILI), '')) THEN 
               V_DESC := V_DESC || ' DtApF=' || TO_CHAR(:OLD.DTAPROCARDFILI) || '|' || TO_CHAR(:NEW.DTAPROCARDFILI); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' DtApF=' || TO_CHAR(:NEW.DTAPROCARDFILI); 
         END IF; 
      END IF;
      IF (NVL(:NEW.DSOBSAPROCARDFILI, '') <> '') OR (UPDATING AND (NVL(:NEW.DSOBSAPROCARDFILI, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.DSOBSAPROCARDFILI, '') <> NVL(:NEW.DSOBSAPROCARDFILI, '')) THEN 
               V_DESC := V_DESC || ' ObsApF=' || SUBSTR(:OLD.DSOBSAPROCARDFILI,1,30) || '|' || SUBSTR(:NEW.DSOBSAPROCARDFILI,1,30); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' ObsApF=' || :NEW.DSOBSAPROCARDFILI; 
         END IF; 
      END IF;
      IF (NVL(:NEW.CDOPERAPROCARDMATR, '') <> '') OR (UPDATING AND (NVL(:NEW.CDOPERAPROCARDMATR, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.CDOPERAPROCARDMATR, '') <> NVL(:NEW.CDOPERAPROCARDMATR, '')) THEN 
               V_DESC := V_DESC || ' OpApM=' || :OLD.CDOPERAPROCARDMATR || '|' || :NEW.CDOPERAPROCARDMATR; 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' OpApM=' || :NEW.CDOPERAPROCARDMATR; 
         END IF; 
      END IF;
      IF (NVL(TO_CHAR(:NEW.DTAPROCARDMATR), '') <> '') OR (UPDATING AND (NVL(TO_CHAR(:NEW.DTAPROCARDMATR), '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(TO_CHAR(:OLD.DTAPROCARDMATR), '') <> NVL(TO_CHAR(:NEW.DTAPROCARDMATR), '')) THEN 
               V_DESC := V_DESC || ' DtApM=' || TO_CHAR(:OLD.DTAPROCARDMATR) || '|' || TO_CHAR(:NEW.DTAPROCARDMATR); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' DtApM=' || TO_CHAR(:NEW.DTAPROCARDMATR); 
         END IF; 
      END IF;
      IF (NVL(:NEW.DSOBSAPROCARDMATR, '') <> '') OR (UPDATING AND (NVL(:NEW.DSOBSAPROCARDMATR, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.DSOBSAPROCARDMATR, '') <> NVL(:NEW.DSOBSAPROCARDMATR, '')) THEN 
               V_DESC := V_DESC || ' ObsApM=' || SUBSTR(:OLD.DSOBSAPROCARDMATR,1,30) || '|' || SUBSTR(:NEW.DSOBSAPROCARDMATR,1,30);  
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' ObsApM=' || :NEW.DSOBSAPROCARDMATR; 
         END IF; 
      END IF;      
      IF (NVL(:NEW.VRPUCTRLAPRCARD, -1) <> -1) OR (UPDATING AND (NVL(:NEW.VRPUCTRLAPRCARD, -1) = -1)) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.VRPUCTRLAPRCARD, -1) <> NVL(:NEW.VRPUCTRLAPRCARD, -1)) THEN 
               V_DESC := V_DESC || ' PuApF' || ROUND(:OLD.VRPUCTRLAPRCARD,5) || '|' || ROUND(:NEW.VRPUCTRLAPRCARD,5); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' PuApF:' || ROUND(:NEW.VRPUCTRLAPRCARD,5); 
         END IF; 
      END IF;
      IF (NVL(:NEW.CDTIPOCUSTO, '') <> '') OR (UPDATING AND (NVL(:NEW.CDTIPOCUSTO, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.CDTIPOCUSTO, '') <> NVL(:NEW.CDTIPOCUSTO, '')) THEN 
               V_DESC := V_DESC || ' TpCusF=' || :OLD.CDTIPOCUSTO || '|' || :NEW.CDTIPOCUSTO; 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' TpCusF=' || :NEW.CDTIPOCUSTO; 
         END IF; 
      END IF;      
      IF (NVL(:NEW.VRCUSTOLIB, -1) <> -1) OR (UPDATING AND (NVL(:NEW.VRCUSTOLIB, -1) = -1)) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.VRCUSTOLIB, -1) <> NVL(:NEW.VRCUSTOLIB, -1)) THEN 
               V_DESC := V_DESC || ' CusLib=' || ROUND(:OLD.VRCUSTOLIB,5) || '|' || ROUND(:NEW.VRCUSTOLIB,5); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' CusLib=' || ROUND(:NEW.VRCUSTOLIB,5); 
         END IF; 
      END IF; 
      IF (NVL(:NEW.VRPUCTRLAPRCARDMATR, -1) <> -1) OR (UPDATING AND (NVL(:NEW.VRPUCTRLAPRCARDMATR, -1) = -1)) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.VRPUCTRLAPRCARDMATR, -1) <> NVL(:NEW.VRPUCTRLAPRCARDMATR, -1)) THEN 
               V_DESC := V_DESC || ' PuApM=' || ROUND(:OLD.VRPUCTRLAPRCARDMATR,5) || '|' || ROUND(:NEW.VRPUCTRLAPRCARDMATR,5); 
            END IF; 
         ELSE 
            V_DESC := V_DESC || ' PuApM=' || ROUND(:NEW.VRPUCTRLAPRCARDMATR,5); 
         END IF; 
      END IF; 
      IF (NVL(:NEW.CDTIPOCUSTOMATR, '') <> '') OR (UPDATING AND (NVL(:NEW.CDTIPOCUSTOMATR, '') = '')) THEN 
         IF UPDATING THEN 
            IF (NVL(:OLD.CDTIPOCUSTOMATR, '') <> NVL(:NEW.CDTIPOCUSTOMATR, '')) THEN 
               V_DESC := V_DESC || ' TpCusF=' || :OLD.CDTIPOCUSTOMATR || '|' || :NEW.CDTIPOCUSTOMATR; 
            END IF;   
         ELSE 
            V_DESC := V_DESC || ' TpCusF=' || :NEW.CDTIPOCUSTOMATR; 
         END IF; 
      END IF;
      V_DESC := V_DESC || ' Rdm='      || V_DSRDM; 
      V_DESC := V_DESC || ' Mtodo='   || V_DSMETODO; 
      V_DESC := V_DESC || ' Ip='       || V_DSIP; 
      V_DESC := V_DESC || ' Usurio='  || V_DSUSUARIO; 
      V_DESC := V_DESC || ' Operador=' || V_DSOPERADOR; 
   END IF; 
   IF V_TIPO <> '0' THEN 
      NMUSUARIO(V_USUARIO); 
      IF NOT V_DESC IS NULL THEN 
         IF NOT V_USUARIO IS NULL THEN 
            V_DTOPER := SYSDATE; 
 
            INSERT INTO LOGOPERA 
               (NMTABELA,   DTOPERACAO, NMRESPOPER, IDTIPOOPER, CDPESQITEM, DSOPERACAO, 
                DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER, DSMETUSUOPER, CDORESPOPER, CDFILLOGOPER) 
            VALUES 
               ('CTRLAPROVCARD', V_DTOPER, V_USUARIO, V_TIPO, V_ITPESQ, SUBSTR(V_DESC, 1, 300), 
                V_DSRDM, V_DSIP, V_DSUSUARIO, V_DSMETODO, V_DSOPERADOR, NVL(:NEW.CDFILIAL, :OLD.CDFILIAL)); 
         END IF; 
      END IF; 
   END IF; 
END; 
/