create or replace TRIGGER LOG_GOP_CONCPREORCAGOP
AFTER UPDATE OR INSERT
ON CONCPREORCAGOP FOR EACH ROW
DECLARE
  V_TIPO         VARCHAR2(1);
  V_DSREMOTEADDR VARCHAR2(15);
  V_DSREMOTEHOST VARCHAR2(15);
  V_DSSERVERRDM  VARCHAR2(40);
  V_DSSERVMETHOD VARCHAR2(4000);
  V_DSREMOTEOPER VARCHAR2(12);
  V_DESC         VARCHAR2(500);
  V_CDFILLOGOPER VARCHAR2(12);
  V_ITPESQ       VARCHAR2(40);
  V_NMOPERADOR   VARCHAR2(40);
  V_CDOPERADOR   VARCHAR2(12);
  V_NRANOORCAGOP VARCHAR(4);
  V_IDPERIODOORCA VARCHAR2(20);
  V_NRPERIODOORCA NUMBER;
  
   PROCEDURE PERIODO(P_NRCALENPREORCAGOP IN VARCHAR2, P_NRORCAMENTOGOP IN VARCHAR2, 
                     P_NRANOORCAGOP OUT VARCHAR2, P_NRPERIODOORCA OUT NUMBER, P_IDPERIODOORCA OUT VARCHAR2) AS
      BEGIN
         P_NRPERIODOORCA := NULL;
         P_IDPERIODOORCA := NULL; 
     P_IDPERIODOORCA := NULL;
         P_IDPERIODOORCA := NULL; 
         BEGIN 
            SELECT MAX(IDPERIODOORCA), MAX(NRPERIODOORCA) INTO P_IDPERIODOORCA, P_NRPERIODOORCA
              FROM (SELECT IDPERIODOORCA, NRPERIODOORCA 
                      FROM CALENPREORCAGOP
                     WHERE NRCALENPREORCAGOP = P_NRCALENPREORCAGOP
                     UNION
                    SELECT IDPERIODOORCA, NRPERIODOORCA
                      FROM ORCAMENTOGOP
                     WHERE NRORCAMENTOGOP = P_NRORCAMENTOGOP) PERIODO;
                     
            SELECT MAX(NRANOORCAGOP) INTO P_NRANOORCAGOP 
              FROM (SELECT NRANOORCAGOP 
                      FROM CALENPREORCAGOP 
                     WHERE NRCALENPREORCAGOP = P_NRCALENPREORCAGOP 
                     UNION 
                    SELECT NRANOORCAGOP 
                      FROM ORCAMENTOGOP 
                     WHERE NRORCAMENTOGOP = P_NRORCAMENTOGOP) ANO;
         END; 
     END; 
BEGIN
/* IDTIPOOPER - 1 - Inclusao, 2 - Alteracao*/
   V_TIPO         := '0';
   V_DESC         := '';
   V_ITPESQ       := '';
   V_NMOPERADOR   := '';
   V_CDOPERADOR   := :NEW.CDOPERULTATU;
   V_CDFILLOGOPER := '';
   V_NRANOORCAGOP := '';
   V_IDPERIODOORCA := '';
   V_NRPERIODOORCA := 0;
   V_DSSERVERRDM := 'ger03000_conclusao_prep_orca';
   NMOPERADOR(V_CDOPERADOR, V_NMOPERADOR);
   WEBTUNNING(V_DSREMOTEADDR,
              V_DSREMOTEHOST,
              V_DSSERVERRDM,
              V_DSSERVMETHOD,
              V_DSREMOTEOPER);
   PERIODO(:NEW.NRCALENPREORCAGOP, :NEW.NRORCAMENTOGOP, V_NRANOORCAGOP, V_NRPERIODOORCA, V_IDPERIODOORCA);
   IF INSERTING THEN   
      V_TIPO         := '1';
      V_ITPESQ       := TO_CHAR(:NEW.NRCONCPREORCAGOP);
      IF NVL(:NEW.NRCONCPREORCAGOP, 0) <> NVL(:OLD.NRCONCPREORCAGOP,0) THEN
         V_DESC := V_DESC || 'Conclusao de Preparacao do Orcamento' || chr(10)
                          || 'Tipo do Orcamento: ' || V_IDPERIODOORCA || chr(10)
                          || 'Ano: ' || V_NRANOORCAGOP || chr(10)
                          || 'Periodo: ' || STRPERIODO(V_NRPERIODOORCA, V_IDPERIODOORCA) || chr(10);
         IF NVL(:NEW.NRCALENPREORCAGOP, 0) <> 0 THEN
            IF NVL(TO_CHAR(:NEW.DTINICIALPREP),'!') <> NVL(TO_CHAR(:OLD.DTINICIALPREP),'!') THEN
            V_DESC := V_DESC || 'Data Inicial: '  || :NEW.DTINICIALPREP;
               END IF;
            IF NVL(TO_CHAR(:NEW.DTFINALPREP),'!') <> NVL(TO_CHAR(:OLD.DTFINALPREP),'!') THEN
               V_DESC := V_DESC || ' Data Final: '  || :NEW.DTFINALPREP || chr(10);
               END IF;
            END IF;
         IF NVL(:NEW.IDSTATUS,'!') <> NVL(:OLD.IDSTATUS,'!') THEN
            V_DESC   := V_DESC||'Status: '  || :NEW.IDSTATUS;
         END IF;
      END IF;            
   ELSIF UPDATING THEN   
      V_TIPO         := '2';
      V_ITPESQ       := TO_CHAR(:OLD.NRCONCPREORCAGOP);
      IF (NVL(:NEW.IDSTATUS,'!') <> NVL(:OLD.IDSTATUS,'!')) THEN         
         V_DESC := V_DESC || 'Conclusao de Preparacao do Orcamento' || chr(10)
                   || 'Tipo do Orcamento: ' || V_IDPERIODOORCA || chr(10)
                   || 'Ano: ' || V_NRANOORCAGOP || chr(10)
                   || 'Periodo: ' || STRPERIODO(V_NRPERIODOORCA, V_IDPERIODOORCA) || chr(10) || chr(10);
         IF NVL(:NEW.NRCALENPREORCAGOP, 0) <> 0 THEN
            IF NVL(TO_CHAR(:NEW.DTINICIALPREP),'!') <> NVL(TO_CHAR(:OLD.DTINICIALPREP),'!') THEN
               V_DESC := V_DESC || 'Data Inicial: '  || :NEW.DTINICIALPREP;
               END IF;
            IF NVL(TO_CHAR(:NEW.DTFINALPREP),'!') <> NVL(TO_CHAR(:OLD.DTFINALPREP),'!') THEN
               V_DESC := V_DESC || ' Data Final: '  || :NEW.DTFINALPREP || chr(10);
               END IF;
            END IF;
         V_DESC := V_DESC || 'Status alterado de ' || :OLD.IDSTATUS || ' para ' || :NEW.IDSTATUS;
      END IF;
   END IF;
   V_DESC := SUBSTR(V_DESC, 1, 300);
   IF NOT V_DESC IS NULL THEN
     INSERT INTO LOGOPERAGES
                 (NMTABELA,     DTOPERACAO,   NMRESPOPER,  IDTIPOOPER,   DSOPERACAO,  CDPESQITEM,
                  DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER, DSMETUSUOPER, CDORESPOPER, CDFILLOGOPER)
            VALUES
                 ('CONCPREORCAGOP', SYSDATE, NVL(V_NMOPERADOR,' '), V_TIPO, V_DESC, V_ITPESQ, NVL(V_DSSERVERRDM, ' '), 
                  NVL(V_DSREMOTEADDR, ' '), NVL(V_DSREMOTEHOST, ' '), NVL(V_DSSERVMETHOD, ' '), NVL(V_CDOPERADOR, ' '),
                  NVL(V_CDFILLOGOPER, ' '));
   END IF;
END;