Error Handling in ETL, using SQL LOADER and PL/SQL

reate or replace PROCEDURE                   SP_LOAD_Financial_Consultants (in_version IN NUMBER,   in_source_id IN VARCHAR2) AS
 
/*
  Functions :
    1. Check for deltas using Checksum
    2. Insert into Advisor History tables all the new advisors and changes.
    3. Insert into event table changed record id, along with old and new versions
 
  Pending issues
  -- Include Exception handling
*/
 
  v_fc_prefix constant VARCHAR2(20) := mas.advisor_prefix;
  v_fc_version_old NUMBER;
  v_fc_version_new NUMBER := in_version;
  v_fc_source VARCHAR2(20) := in_source_id;
BEGIN
 
/* LOGIC:
    Outer join Staging and Materialized tables to retrive 
    all the records that mathed External IDs but did not match check sums 
    along with their META IDs and also the records which did not match the
    External IDS with META IDs Null, and generate META IDs at the time of insertion.
    Records with Meta ID value Null are considered New records and the rest are changed ones. 
*/
  INSERT FIRST
    WHEN meta_fc_id IS NULL THEN
-- conditional insert if the advior is a new advisor also generate new meta ID
      INTO financial_consultant(version,   source_id,   meta_fc_id,   ext_fc_id,   fc_number,   fc_name,   fc_firm_name,   fc_branch_code,   fc_status,   phone_no,   addr1,   addr2,   addr3,   addr4,   city,   state,   country,   zip_code,   email,   fax_no,   checksum)
      VALUES(v_fc_version_new,   v_fc_source, v_fc_prefix||advisor_seq.nextval,   ext_fc_id,   fc_num,   fc_name,   firm_name,   branch_code,   fc_status,   phone_no,   addr1,   addr2,   addr3,   addr4,   city,   state,   country,   zip_code,   email,   fax_no,   stg_cksum)
 
    WHEN meta_fc_id IS NOT NULL THEN
-- conditional insert for changed advisors into advsor and event table.
      INTO financial_consultant (version,   source_id,   meta_fc_id,   ext_fc_id,   fc_id,   fc_name,   fc_firm_name,   fc_branch_code,   fc_status,   phone_no,   addr1,   addr2,   addr3,   addr4,   city,   state,   country,   zip_code,   email,   fax_no,   checksum)
      VALUES(v_fc_version_new,   v_fc_source, meta_fc_id,   ext_fc_id,   fc_num,   fc_name,   firm_name,   branch_code,   fc_status,   phone_no,   addr1,   addr2,   addr3,   addr4,   city,   state,   country,   zip_code,   email,   fax_no,   stg_cksum)    
      
      INTO fc_event(old_version,   new_version,   meta_fc_id,   entity)
      VALUES(old_version,  v_fc_version_new,   meta_fc_id,   'FIN_CONSULTANT') 
-- query to fetch the deltas required to insert (only the changed and new ones from staging)
      WITH fc AS
        (
          SELECT mv.version old_version, mv.meta_fc_id, stg.ext_fc_id, stg.fc_status, stg.firm_name,
             stg.fc_name, stg.phone_no, stg.addr1, stg.addr2,   stg.addr3,
             stg.addr4, stg.city, stg.state, stg.country,  stg.zip_code,
             stg.branch_code, stg.fc_num, stg.email, stg.fax_no,
             checksum(stg.ext_fc_id || stg.fc_status || stg.firm_name || stg.fc_name || stg.phone_no || stg.addr1 || stg.addr2 || stg.addr3 || stg.addr4 || stg.city || stg.state || stg.country || stg.zip_code || stg.branch_code || stg.fc_num || stg.email || stg.fax_no) stg_cksum,
             nvl(mv.checksum,0) fd_cksum
           FROM fc_stg stg,
             financial_consultant_mv mv
           WHERE stg.ext_fc_id = mv.ext_fc_id(+)
         )
      SELECT *
      FROM FC
      WHERE fc.stg_cksum <> fc.fd_cksum;
-- inline FA table calculates the staging check sums, also retrives MV checkums into its columns.
EXCEPTION
WHEN no_data_found THEN
NULL;
 
END sp_load_financial_consultants;

                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60: