CREATE OR REPLACE PROCEDURE Sp_acancel
IS
  cn_seqn_filler_v           NUMBER(9,0);
  cn_seqn_filler_p           NUMBER(9,0);
  cn_seqn_filler_n           VARCHAR2(50) := 'CN_SEQN_FILLER';
  cn_code_v                  VARCHAR2(6);
  cn_code_p                  VARCHAR2(6);
  cn_code_n                  VARCHAR2(50) := 'CN_CODE';
  cn_desc_v                  VARCHAR2(60);
  cn_desc_p                  VARCHAR2(60);
  cn_desc_n                  VARCHAR2(50) := 'CN_DESC';
  cn_log_date_v              DATE;
  cn_log_date_p              DATE;
  cn_log_date_n              VARCHAR2(50) := 'CN_LOG_DATE';
  cn_log_details_filler_v    VARCHAR2(1);
  cn_log_details_filler_p    VARCHAR2(1);
  cn_log_details_filler_n    VARCHAR2(50) := 'CN_LOG_DETAILS_FILLER';
  cn_sequ_v                  NUMBER(9,0);
  cn_sequ_p                  NUMBER(9,0);
  cn_sequ_n                  VARCHAR2(50) := 'CN_SEQU';
  cn_cg_sequ_v               NUMBER(9,0);
  cn_cg_sequ_p               NUMBER(9,0);
  cn_cg_sequ_n               VARCHAR2(50) := 'CN_CG_SEQU';
  cn_inactive_v              NUMBER(3,0);
  cn_inactive_p              NUMBER(3,0);
  cn_inactive_n              VARCHAR2(50) := 'CN_INACTIVE';
  cn_des_sequ_v              NUMBER(9,0);
  cn_des_sequ_p              NUMBER(9,0);
  cn_des_sequ_n              VARCHAR2(50) := 'CN_DES_SEQU';
  cn_ca_sequ_v               NUMBER(9,0);
  cn_ca_sequ_p               NUMBER(9,0);
  cn_ca_sequ_n               VARCHAR2(50) := 'CN_CA_SEQU';
  cn_start_date_v            DATE;
  cn_start_date_p            DATE;
  cn_start_date_n            VARCHAR2(50) := 'CN_START_DATE';
  cn_end_date_v              DATE;
  cn_end_date_p              DATE;
  cn_end_date_n              VARCHAR2(50) := 'CN_END_DATE';
  lv_counter                 NUMBER(18,0);
  lv_counter_p               NUMBER(18,0);
  lv_sid_spid                NUMBER(18,0);
  lv_d_action                VARCHAR2(1);
  lv_insert_date_time        DATE;
  lv_machinename             VARCHAR2(50);
  lv_app_user                VARCHAR2(20);
  lv_program_name            VARCHAR2(50);
  lv_nt_domain               VARCHAR2(50);
  lv_nt_username             VARCHAR2(50);
  lv_last_process_sequ       NUMBER(9,0);
  lv_audit_table             VARCHAR2(50);
  lv_audit_primary_key_name  VARCHAR2(50);
  lv_audit_text              VARCHAR2(8000);
  lv_audt_rec_sequ           VARCHAR2(100);
  CURSOR c_acancel IS 
    SELECT cn_seqn_filler,
           cn_code,
           cn_desc,
           cn_log_date,
           cn_log_details_filler,
           cn_sequ,
           cn_cg_sequ,
           cn_inactive,
           cn_des_sequ,
           cn_ca_sequ,
           cn_start_date,
           cn_end_date,
           sid_spid,
           d_action,
           insert_date_time,
           machinename,
           program_name,
           nt_domain,
           nt_username,
           counter
    FROM   acancel
    WHERE  counter > (SELECT sq_count
                      FROM   f_sequ
                      WHERE  sq_table = 'ACANCEL');
BEGIN
  OPEN c_acancel;
  
  FETCH c_acancel INTO cn_seqn_filler_v,cn_code_v,cn_desc_v,cn_log_date_v,
  cn_log_details_filler_v,cn_sequ_v,cn_cg_sequ_v,cn_inactive_v,
  cn_des_sequ_v,cn_ca_sequ_v,cn_start_date_v,cn_end_date_v,
  lv_sid_spid,lv_d_action,lv_insert_date_time,lv_machinename,
  lv_program_name,lv_nt_domain,lv_nt_username,lv_counter;
  
  WHILE c_acancel%FOUND LOOP
    lv_audit_text := '';
    
    lv_audit_table := 'FCANCEL';
    
    lv_audit_primary_key_name := 'FCANCEL'
                                 ||'/'
                                 ||To_char(cn_sequ_v);
    
    lv_audt_rec_sequ := To_char(cn_sequ_v);
    
    BEGIN
      SELECT aud_app_us_name
      INTO   lv_app_user
      FROM   f_audit_logon_off
      WHERE  ((lv_insert_date_time >= aud_date_time_start
               AND lv_insert_date_time <= aud_date_time_exit)
               OR (lv_insert_date_time >= aud_date_time_start
                   AND aud_date_time_exit IS NULL))
             AND aud_spid = lv_sid_spid;
    EXCEPTION
      WHEN no_data_found THEN
        lv_app_user := '';
      WHEN too_many_rows THEN
        SELECT aud_app_us_name
        INTO   lv_app_user
        FROM   f_audit_logon_off
        WHERE  aud_spid = lv_sid_spid
               AND ROWNUM = 1;
    END;
    
    BEGIN
      SELECT Max(counter)
      INTO   lv_counter_p
      FROM   acancel
      WHERE  cn_sequ = cn_sequ_v
             AND counter < lv_counter;
    EXCEPTION
      WHEN no_data_found THEN
        lv_counter_p := NULL;
    END;
    
    IF lv_d_action = 'I'
        OR lv_d_action = 'D'
        OR (lv_d_action = 'U'
            AND (lv_counter_p IS NULL)) THEN
      lv_audit_text := cn_seqn_filler_n
                       ||Chr(164)
                       ||To_char(cn_seqn_filler_v);
      
      IF cn_code_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_code_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_code_n
                         ||Chr(164)
                         ||cn_code_v;
      END IF;
      
      IF cn_desc_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_desc_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_desc_n
                         ||Chr(164)
                         ||cn_desc_v;
      END IF;
      
      IF cn_log_date_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_log_date_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_log_date_n
                         ||Chr(164)
                         ||To_char(cn_log_date_v,'DD MON YYYY HH24:MI:SS');
      END IF;
      
      IF cn_log_details_filler_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_log_details_filler_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_log_details_filler_n
                         ||Chr(164)
                         ||cn_log_details_filler_v;
      END IF;
      
      lv_audit_text := lv_audit_text
                       ||Chr(164)
                       ||cn_sequ_n
                       ||Chr(164)
                       ||To_char(cn_sequ_v);
      
      lv_audit_text := lv_audit_text
                       ||Chr(164)
                       ||cn_cg_sequ_n
                       ||Chr(164)
                       ||To_char(cn_cg_sequ_v);
      
      lv_audit_text := lv_audit_text
                       ||Chr(164)
                       ||cn_inactive_n
                       ||Chr(164)
                       ||To_char(cn_inactive_v);
      
      lv_audit_text := lv_audit_text
                       ||Chr(164)
                       ||cn_des_sequ_n
                       ||Chr(164)
                       ||To_char(cn_des_sequ_v);
      
      lv_audit_text := lv_audit_text
                       ||Chr(164)
                       ||cn_ca_sequ_n
                       ||Chr(164)
                       ||To_char(cn_ca_sequ_v);
      
      IF cn_start_date_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_start_date_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_start_date_n
                         ||Chr(164)
                         ||To_char(cn_start_date_v,'DD MON YYYY HH24:MI:SS');
      END IF;
      
      IF cn_end_date_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_end_date_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_end_date_n
                         ||Chr(164)
                         ||To_char(cn_end_date_v,'DD MON YYYY HH24:MI:SS');
      END IF;
      
      lv_audit_text := lv_audit_text
                       ||Chr(164);
    ELSE
      IF (lv_d_action = 'U'
          AND (lv_counter_p IS NOT NULL)) THEN
        SELECT cn_seqn_filler,
               cn_code,
               cn_desc,
               cn_log_date,
               cn_log_details_filler,
               cn_sequ,
               cn_cg_sequ,
               cn_inactive,
               cn_des_sequ,
               cn_ca_sequ,
               cn_start_date,
               cn_end_date
        INTO   cn_seqn_filler_p,cn_code_p,cn_desc_p,cn_log_date_p,
               cn_log_details_filler_p,cn_sequ_p,cn_cg_sequ_p,cn_inactive_p,
               cn_des_sequ_p,cn_ca_sequ_p,cn_start_date_p,cn_end_date_p
        FROM   acancel
        WHERE  counter = lv_counter_p;
        
        IF cn_seqn_filler_p <> cn_seqn_filler_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_seqn_filler_n
                           ||Chr(164)
                           ||To_char(cn_seqn_filler_v);
        END IF;
        
        IF (cn_code_p IS NOT NULL 
            AND cn_code_v IS NULL)
            OR (cn_code_p IS NULL 
                AND cn_code_v IS NOT NULL)
            OR (cn_code_p <> cn_code_v) THEN
          IF cn_code_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_code_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_code_n
                             ||Chr(164)
                             ||cn_code_v;
          END IF;
        END IF;
        
        IF (cn_desc_p IS NOT NULL 
            AND cn_desc_v IS NULL)
            OR (cn_desc_p IS NULL 
                AND cn_desc_v IS NOT NULL)
            OR (cn_desc_p <> cn_desc_v) THEN
          IF cn_desc_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_desc_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_desc_n
                             ||Chr(164)
                             ||cn_desc_v;
          END IF;
        END IF;
        
        IF (cn_log_date_p IS NOT NULL 
            AND cn_log_date_v IS NULL)
            OR (cn_log_date_p IS NULL 
                AND cn_log_date_v IS NOT NULL)
            OR (cn_log_date_p <> cn_log_date_v) THEN
          IF cn_log_date_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_log_date_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_log_date_n
                             ||Chr(164)
                             ||To_char(cn_log_date_v,'DD MON YYYY HH24:MI:SS');
          END IF;
        END IF;
        
        IF (cn_log_details_filler_p IS NOT NULL 
            AND cn_log_details_filler_v IS NULL)
            OR (cn_log_details_filler_p IS NULL 
                AND cn_log_details_filler_v IS NOT NULL)
            OR (cn_log_details_filler_p <> cn_log_details_filler_v) THEN
          IF cn_log_details_filler_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_log_details_filler_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_log_details_filler_n
                             ||Chr(164)
                             ||cn_log_details_filler_v;
          END IF;
        END IF;
        
        IF cn_sequ_p <> cn_sequ_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_sequ_n
                           ||Chr(164)
                           ||To_char(cn_sequ_v);
        END IF;
        
        IF cn_cg_sequ_p <> cn_cg_sequ_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_cg_sequ_n
                           ||Chr(164)
                           ||To_char(cn_cg_sequ_v);
        END IF;
        
        IF cn_inactive_p <> cn_inactive_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_inactive_n
                           ||Chr(164)
                           ||To_char(cn_inactive_v);
        END IF;
        
        IF cn_des_sequ_p <> cn_des_sequ_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_des_sequ_n
                           ||Chr(164)
                           ||To_char(cn_des_sequ_v);
        END IF;
        
        IF cn_ca_sequ_p <> cn_ca_sequ_v THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164)
                           ||cn_ca_sequ_n
                           ||Chr(164)
                           ||To_char(cn_ca_sequ_v);
        END IF;
        
        IF (cn_start_date_p IS NOT NULL 
            AND cn_start_date_v IS NULL)
            OR (cn_start_date_p IS NULL 
                AND cn_start_date_v IS NOT NULL)
            OR (cn_start_date_p <> cn_start_date_v) THEN
          IF cn_start_date_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_start_date_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_start_date_n
                             ||Chr(164)
                             ||To_char(cn_start_date_v,'DD MON YYYY HH24:MI:SS');
          END IF;
        END IF;
        
        IF (cn_end_date_p IS NOT NULL 
            AND cn_end_date_v IS NULL)
            OR (cn_end_date_p IS NULL 
                AND cn_end_date_v IS NOT NULL)
            OR (cn_end_date_p <> cn_end_date_v) THEN
          IF cn_end_date_v IS NULL THEN
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_end_date_n
                             ||Chr(164);
          ELSE
            lv_audit_text := lv_audit_text
                             ||Chr(164)
                             ||cn_end_date_n
                             ||Chr(164)
                             ||To_char(cn_end_date_v,'DD MON YYYY HH24:MI:SS');
          END IF;
        END IF;
        
        IF Length(lv_audit_text) > 0 THEN
          lv_audit_text := lv_audit_text
                           ||Chr(164);
          
          lv_audit_text := Substr(lv_audit_text,2,Length(lv_audit_text));
        ELSE
          lv_audit_text := 'NO CHANGE';
        END IF;
      END IF;
    END IF;
    
    INSERT INTO f_audit_trail_enh
               (audt_spid,
                audt_app_user_name,
                audt_user_action,
                audt_program,
                audt_nt_machine_name,
                audt_nt_user_name,
                audt_rec_sequ,
                audt_table,
                audt_primary_key_name,
                audt_audit_char,
                audt_date_time)
    VALUES     (lv_sid_spid,
                lv_app_user,
                lv_d_action,
                lv_program_name,
                lv_machinename,
                lv_nt_username,
                lv_audt_rec_sequ,
                lv_audit_table,
                lv_audit_primary_key_name,
                lv_audit_text,
                lv_insert_date_time);
    
    UPDATE f_sequ
    SET    sq_count = lv_counter
    WHERE  sq_table = 'ACANCEL';
    
    FETCH c_acancel INTO cn_seqn_filler_v,cn_code_v,cn_desc_v,cn_log_date_v,
    cn_log_details_filler_v,cn_sequ_v,cn_cg_sequ_v,cn_inactive_v,
    cn_des_sequ_v,cn_ca_sequ_v,cn_start_date_v,cn_end_date_v,
    lv_sid_spid,lv_d_action,lv_insert_date_time,lv_machinename,
    lv_program_name,lv_nt_domain,lv_nt_username,lv_counter;
  END LOOP;
  
  CLOSE c_acancel;
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    
    dbms_output.Put_line('NO DATA FOUND');
END;

/