CREATE OR REPLACE TRIGGER bi_data_load_inbox
BEFORE INSERT ON data_load_inbox
FOR EACH ROW
DECLARE
  -- If this is the first record of a data load then
  --   Determine a job ID for the data load
  --   Save the job ID in a Common Load Package variable
  --   Log the start of a data load
  -- End if
  -- Assign the Common Load Package Job ID to the JOB_ID field

  v_load_rec   data_loads%ROWTYPE;
  v_successful BOOLEAN;
  v_err_msg    VARCHAR2(2000);
BEGIN
  IF (:new.interface_name = di_load.interface_name) THEN
    IF (NOT cl.load_running) THEN
      -- Set MODULE column of V$SESSION
      DBMS_APPLICATION_INFO.SET_MODULE(:new.interface_name,NULL);
      cl.load_running := TRUE;
      -- Determine Job ID
      cl.job_id := TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS');
      -- Log the start of the load
      v_load_rec.interface_name      := di_load.interface_name;
      v_load_rec.job_id              := cl.job_id;
      v_load_rec.status              := 'IN PROCESS';
      v_load_rec.lines_in_file       := 1;
      v_load_rec.lines_with_messages := 0;
      v_load_rec.message_count       := 0;
      v_load_rec.lines_with_errors   := 0;
      v_load_rec.error_count         := 0;
      v_load_rec.load_started        := SYSDATE;
      v_load_rec.load_finished       := NULL;
      cl.log_load_event(v_load_rec, 'S', v_successful, v_err_msg);
      IF (NOT v_successful) THEN
        RAISE_APPLICATION_ERROR(-20000, v_err_msg);
      END IF;
    END IF;
    :new.job_id := cl.job_id;
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'Unknown data interface: '||:new.interface_name);
  END IF;
END;
/
SHOW ERRORS TRIGGER bi_data_load_inbox;


CREATE OR REPLACE TRIGGER ai_data_load_inbox
AFTER INSERT ON data_load_inbox
DECLARE
  -- Mark all newly loaded records as in process
  -- For each data record in DATA_LOAD_INBOX
  --   Call the appropriate data load DB procedure
  -- Delete all records processed

  CURSOR data_records IS
    SELECT *
      FROM data_load_inbox
     WHERE status = 'IN PROCESS'
     ORDER BY interface_name, job_id, line_no;
  CURSOR get_load_prog(p_int_name VARCHAR2) IS
    SELECT load_program
      FROM data_load_interfaces
     WHERE interface_name = p_int_name;
  v_load_prog data_load_interfaces.load_program%TYPE;
  v_rows_processed NUMBER;
BEGIN
  -- Mark all newly loaded records as in process
  UPDATE data_load_inbox
     SET status = 'IN PROCESS'
   WHERE status = 'NEW';
  FOR load_rec IN data_records LOOP
    OPEN get_load_prog(load_rec.interface_name);
    FETCH get_load_prog INTO v_load_prog;
    IF (get_load_prog%FOUND) THEN
      v_rows_processed := cl.Execute_Statement('BEGIN '||
                                                v_load_prog||'('||
                                                  load_rec.job_id||', '||
                                                  load_rec.line_no||', '''||
                                                  REPLACE(load_rec.line_text,'''','''''')||''');'||
                                                'END;');
    ELSE
      CLOSE get_load_prog;
      RAISE_APPLICATION_ERROR(-20000, 'Undefined interface '''||load_rec.interface_name||'''');
    END IF;
    CLOSE get_load_prog;
  END LOOP;
  -- Delete records processed
  DELETE FROM data_load_inbox
   WHERE status = 'IN PROCESS';
  EXCEPTION
     WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END;
/

SHOW ERRORS TRIGGER ai_data_load_inbox;