eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_IMP_LOAD_WRAPPER SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30


P_LAST_UPDATE_LOGIN IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_PROGRAM_APPLICATION_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2);

Line: 139


  ,P_LAST_UPDATE_LOGIN	       IN       NUMBER
  ,P_PROGRAM_APP_ID	       IN       NUMBER
  ,P_PROGRAM_ID		       IN       NUMBER
) IS

l_int_row_id T_ROWID;

Line: 151


 select int_row_id, table_name, hz_imp_errors_s.nextval
   from (
       select /*+ ordered  parallel(r) use_hash(r) */
       		table_name, int_row_id, lead(sd,1) over (partition by
       		decode(t.direction_code, 'C', obj_id, sub_id),
       		decode(t.direction_code, 'C', sub_id, obj_id),
       		decode(t.direction_code, 'C', t.backward_rel_code, rc),
                t.subject_type, t.object_type  /* Fix 3931139 */
       		order by sd, ed) lsd, sub_id, obj_id, rc, sd, ed,
       		t.subject_type, t.object_type
       from hz_relationship_types t,
       (
         	select /*+ parallel(s) */ sub_id, obj_id, int_row_id,
       		       nvl(start_date, sysdate) sd, nvl(end_date,
       		       to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
       		       ed, relationship_code rc, relationship_type rt,
       		       'HZ_IMP_RELSHIPS_INT' table_name
       		from hz_imp_relships_sg s
        	where batch_id = p_batch_id
          	and batch_mode_flag = p_batch_mode_flag
          	and action_flag = 'I'
       		union all
       		select /*+ parallel(s) */ sub_id, obj_id, int_row_id,
              		nvl(start_date, sysdate) sd, nvl(end_date,
              		to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
              		ed, relationship_code rc, relationship_type rt,
              		'HZ_IMP_CONTACTS_INT' table_name
         	from hz_imp_contacts_sg s
        	where batch_id = p_batch_id
         	and batch_mode_flag = p_batch_mode_flag
          	and action_flag = 'I'
       ) r
       where t.forward_rel_code = r.rc
       and t.relationship_type = r.rt)
   where lsd < ed
   and exists (
       select /*+ index(p, HZ_PARTIES_U1) */ 1
         from hz_parties p
        where subject_type = p.party_type
          and sub_id = p.party_id)
   and exists (
       select /*+ index(p, HZ_PARTIES_U1) */ 1
         from hz_parties p
        where object_type = p.party_type
          and obj_id = p.party_id);

Line: 201


  select int_row_id, hz_imp_errors_s.NextVal from (
    select /*+ parallel(s) */ party_site_use_id suid, party_site_id sid,
      site_use_type sut, int_row_id, lead(party_site_id, 1) over(
      partition by site_use_type order by primary_flag desc) n_sid
      from hz_imp_addressuses_sg s
      where s.batch_id = p_batch_id
      and s.batch_mode_flag = p_batch_mode_flag
      and s.action_flag = 'I'
  ) r
  where sid = n_sid;

Line: 217


  select int_row_id, decode(allow_multi_assign_flag, 'N', 'M', 'E'), hz_imp_errors_s.NextVal from (
       select /*+ parallel(s) */
              s.int_row_id, s.party_id, s.party_orig_system_reference,
              s.primary_flag, s.start_date_active, s.end_date_active,
              lead(s.start_date_active, 1) over(partition by s.party_id,
              s.class_category, s.class_code order by s.start_date_active,
              s.end_date_active nulls last, s.primary_flag desc) lsd1,
              lead(s.start_date_active, 1) over(partition by s.party_id,
              s.class_category order by s.start_date_active,
              s.end_date_active nulls last, s.primary_flag desc) lsd2,
              c_cat.allow_multi_assign_flag
         from hz_imp_classifics_sg s,
              hz_class_categories c_cat,
 	      hz_imp_classifics_int c_int
        where s.batch_id = p_batch_id
          and s.batch_mode_flag = p_batch_mode_flag
          and s.action_flag = 'I'
          and c_cat.class_category = s.class_category
          and ( c_int.INTERFACE_STATUS is null  OR
 	        (c_int.INTERFACE_STATUS is not null AND c_int.INTERFACE_STATUS <> 'D')
 	      )
 	  and c_int.rowid = s.int_row_id )
 where decode(allow_multi_assign_flag, 'N', lsd2, lsd1) < nvl(end_date_active,
       to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'));

Line: 257


    insert into hz_imp_tmp_errors
    (
	request_id,
	batch_id,
	int_row_id,
	interface_table_name,
	error_id,
	creation_date,
	created_by,
	last_update_date,
	last_updated_by,
	last_update_login,
	program_application_id,
	program_id,
	program_update_date,
	e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,
	e6_flag,e7_flag,e8_flag,e9_flag,e10_flag,
	e11_flag,e12_flag,e13_flag,e14_flag,e15_flag,e16_flag,
	ACTION_MISMATCH_FLAG,
	e38_flag  -- HZ_IMP_DUP_REL_IN_INT_ERROR
    )
    values
    (
    	P_REQUEST_ID,
	P_BATCH_ID,
	l_int_row_id(j),
	l_table_name(j),
	l_err_id(j),
	P_SYSDATE,
	P_USER_ID,
	P_SYSDATE,
	P_USER_ID,
	P_LAST_UPDATE_LOGIN,
	P_PROGRAM_APP_ID,
	P_PROGRAM_ID,
	P_SYSDATE,
	'Y','Y','Y','Y','Y',
	'Y','Y','Y','Y','Y',
	'Y','Y','Y','Y','Y','Y',
	'Y',
	'E'
    );

Line: 302


     So update both tables. Update interface_status to 'E' so that
     these records will not be picked up during V+DML. Error_id column
     in interface table will be updated later when records are
     copied from tmp error table to error table. */
  ForAll j in 1..l_int_row_id.count
    update HZ_IMP_RELSHIPS_INT
    set interface_status = 'E', error_id = l_err_id(j)
    where rowid = l_int_row_id(j)
    and l_table_name(j) = 'HZ_IMP_RELSHIPS_INT';

Line: 313


    update HZ_IMP_CONTACTS_INT
    set interface_status = 'E', error_id = l_err_id(j)
    where rowid = l_int_row_id(j)
    and l_table_name(j) = 'HZ_IMP_CONTACTS_INT';

Line: 326


    insert into hz_imp_tmp_errors
    (
	request_id,
	batch_id,
	int_row_id,
	interface_table_name,
	error_id,
	creation_date,
	created_by,
	last_update_date,
	last_updated_by,
	last_update_login,
	program_application_id,
	program_id,
	program_update_date,
	e1_flag,e2_flag,e3_flag,ACTION_MISMATCH_FLAG,MISSING_PARENT_FLAG,
	e38_flag  -- HZ_IMP_DUP_ADDRUSE_IN_INT
    )
    values
    (
    	P_REQUEST_ID,
	P_BATCH_ID,
	l_int_row_id(j),
	'HZ_IMP_ADDRESSUSES_INT',
	l_err_id(j),
	P_SYSDATE,
	P_USER_ID,
	P_SYSDATE,
	P_USER_ID,
	P_LAST_UPDATE_LOGIN,
	P_PROGRAM_APP_ID,
	P_PROGRAM_ID,
	P_SYSDATE,
	'Y','Y','Y','Y','Y',
	'E'
    );

Line: 364


    update HZ_IMP_ADDRESSUSES_INT
    set interface_status = 'E', error_id = l_err_id(j)
    where rowid = l_int_row_id(j);

Line: 375


    insert into hz_imp_tmp_errors
    (
	request_id,
	batch_id,
	int_row_id,
	interface_table_name,
	error_id,
	creation_date,
	created_by,
	last_update_date,
	last_updated_by,
	last_update_login,
	program_application_id,
	program_id,
	program_update_date,
	e2_flag,e3_flag,e4_flag,e5_flag,
	e6_flag,e7_flag,e8_flag,e9_flag,ACTION_MISMATCH_FLAG,
	e38_flag  -- HZ_IMP_DUP_CLASSIFIC_IN_INT
    )
    values
    (
    	P_REQUEST_ID,
	P_BATCH_ID,
	l_int_row_id(j),
	'HZ_IMP_CLASSIFICS_INT',
	l_err_id(j),
	P_SYSDATE,
	P_USER_ID,
	P_SYSDATE,
	P_USER_ID,
	P_LAST_UPDATE_LOGIN,
	P_PROGRAM_APP_ID,
	P_PROGRAM_ID,
	P_SYSDATE,
	'Y','Y','Y','Y',
	'Y','Y','Y','Y','Y',
	l_err(j)
    );

Line: 415


    update HZ_IMP_CLASSIFICS_INT
    set interface_status = 'E', error_id = l_err_id(j)
    where rowid = l_int_row_id(j);

Line: 423


    insert into hz_imp_tmp_rel_end_date(batch_id, sub_orig_system_reference, relationship_id, directional_flag, int_row_id,
    					creation_date, created_by, last_update_date, last_updated_by)
  SELECT     /*+ parallel(rs) parallel(r) full(rs) leading(rs) use_nl(r) */
           P_BATCH_ID, rs.sub_orig_system_reference, r.relationship_id,
           r.directional_flag,  rs.int_row_id int_row_id,
           P_SYSDATE, P_USER_ID, P_SYSDATE, P_USER_ID
  FROM hz_imp_relships_sg rs,
       hz_relationships r
  WHERE rs.batch_id=P_BATCH_ID
  AND rs.sub_orig_system = P_OS
  AND rs.batch_mode_flag = P_BATCH_MODE_FLAG
  AND rs.action_flag = 'I'
  AND rs.relationship_type in ('HEADQUARTERS/DIVISION', 'PARENT/SUBSIDIARY','DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
  AND r.relationship_type in ('HEADQUARTERS/DIVISION', 'PARENT/SUBSIDIARY','DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
  AND rs.obj_id=r.object_id
  AND decode(r.relationship_type,'PARENT/SUBSIDIARY','HEADQUARTERS/DIVISION',r.relationship_type)
     =decode(rs.relationship_type,'PARENT/SUBSIDIARY','HEADQUARTERS/DIVISION',rs.relationship_type)
  AND decode(r.relationship_code,'PARENT_OF','HEADQUARTERS_OF',r.relationship_code)
     =decode(rs.relationship_code,'PARENT_OF','HEADQUARTERS_OF',rs.relationship_code)
  AND r.subject_table_name='HZ_PARTIES'
  AND r.object_table_name='HZ_PARTIES'
  AND r.object_type='ORGANIZATION'
  AND r.status='A'
  AND P_SYSDATE > r.start_date
  AND P_SYSDATE <= nvl(r.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
  AND r.actual_content_source='DNB';

Line: 450


  insert into hz_imp_tmp_rel_end_date(batch_id, sub_orig_system_reference, relationship_id, directional_flag, int_row_id,
                        creation_date, created_by, last_update_date, last_updated_by)
  SELECT batch_id, sub_orig_system_reference, relationship_id, decode(directional_flag,'B','F','F','B'), int_row_id,
         creation_date, created_by, last_update_date, last_updated_by
  FROM hz_imp_tmp_rel_end_date WHERE batch_id=P_BATCH_ID;

Line: 458


     copy from tmp error to permanent error table. Insert into tmp errors table
     for keeping error count. */
  insert into hz_imp_errors (
    error_id, batch_id, request_id, interface_table_name, message_name
  )
  select error_id, batch_id, request_id, interface_table_name,
    decode(interface_table_name, 'HZ_IMP_RELSHIPS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR',
    'HZ_IMP_CONTACTS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR',
    'HZ_IMP_ADDRESSUSES_INT', 'HZ_IMP_DUP_ADDRUSE_IN_INT',
    'HZ_IMP_CLASSIFICS_INT', decode(e38_flag, 'M', 'HZ_API_ALLOW_MUL_ASSIGN_FG',
    'HZ_IMP_DUP_CLASSIFIC_IN_INT')
    )
  from hz_imp_tmp_errors
  where batch_id = P_BATCH_ID
  and request_id = P_REQUEST_ID
  and interface_table_name in ('HZ_IMP_RELSHIPS_INT', 'HZ_IMP_CONTACTS_INT',
    'HZ_IMP_ADDRESSUSES_INT', 'HZ_IMP_CLASSIFICS_INT');

Line: 486


PROCEDURE update_import_status(
  P_BATCH_ID	IN NUMBER,
  P_STATUS	IN VARCHAR2
) IS
  l_detail_import_status VARCHAR2(30);

Line: 503


  update hz_imp_batch_summary
  set import_status = l_batch_import_status,
      import_req_id = hz_utility_v2pub.request_id
  where batch_id = P_BATCH_ID;

Line: 508


  update hz_imp_batch_details
  set import_status = l_detail_import_status,
      import_req_id = hz_utility_v2pub.request_id
  where batch_id = P_BATCH_ID
  and run_number = (select max(run_number)
   	            from hz_imp_batch_details
		    where batch_id = P_BATCH_ID);

Line: 518


END update_import_status;

Line: 527


  select 'Y' from fnd_conc_req_summary_v
  where program_short_name like 'ARHLWRPB'
  and phase_code <> 'C'
  and rownum = 1;

Line: 549


    /* Increase increment for muti-table insert for parties */
    execute immediate 'alter sequence ' || l_schema || '.hz_organization_profiles_s increment by 3';

Line: 628


/* Clean up staging. Delete for online, truncate for batch */
/* Also chean up the following tables:  */
/*     hz_imp_osr_change                */
/*     HZ_IMP_INT_DEDUP_RESULTS         */
/*     HZ_IMP_TMP_REL_END_DATE          */
PROCEDURE CLEANUP_STAGING(
  P_BATCH_ID         IN NUMBER,
  P_BATCH_MODE_FLAG  IN VARCHAR2
) IS
l_bool BOOLEAN;

Line: 666


    DELETE HZ_IMP_PARTIES_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 669


    DELETE HZ_IMP_ADDRESSES_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 672


    DELETE HZ_IMP_CONTACTPTS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 675


    DELETE HZ_IMP_CREDITRTNGS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 678


    DELETE HZ_IMP_CLASSIFICS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 681


    DELETE HZ_IMP_FINREPORTS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 684


    DELETE HZ_IMP_FINNUMBERS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 687


    DELETE HZ_IMP_RELSHIPS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 690


    DELETE HZ_IMP_CONTACTS_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 693


    DELETE HZ_IMP_CONTACTROLES_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 696


    DELETE HZ_IMP_ADDRESSUSES_SG
     WHERE batch_id = P_BATCH_ID
     AND batch_mode_flag = P_BATCH_MODE_FLAG;

Line: 702


  DELETE hz_imp_osr_change WHERE batch_id = P_BATCH_ID;

Line: 704


  DELETE HZ_IMP_TMP_REL_END_DATE WHERE batch_id = P_BATCH_ID;

Line: 719


   for existence of OS+OSR in SSM table to decide if we'll insert new records,
   if it's new DNB purchase for two existing parties, duplicate SSM records
   can be created */
PROCEDURE CLEANUP_SSM(
  P_CONTENT_SRC_TYPE  IN VARCHAR2,
  P_BATCH_ID          IN NUMBER,
  P_BATCH_MODE_FLAG   IN VARCHAR2,
  P_ORIG_SYSTEM       IN VARCHAR2,
  P_REQUEST_ID        IN NUMBER
) IS

    l_cleanup_ssm_site_qry VARCHAR2(4000) :=
    ' SELECT psosr.rowid,posr.owner_table_id
      FROM HZ_IMP_ADDRESSES_SG  site_sg,
           HZ_ORIG_SYS_REFERENCES posr,
           HZ_ORIG_SYS_REFERENCES psosr
      WHERE site_sg.batch_id = :CP_BATCH_ID
      AND site_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
      AND site_sg.party_orig_system = :CP_OS
      AND posr.orig_system  = site_sg.party_orig_system
      AND posr.orig_system_reference = site_sg.party_orig_system_reference
      AND posr.status  = ''A''
      AND posr.owner_table_name  = ''HZ_PARTIES''
      AND psosr.orig_system  = site_sg.site_orig_system
      AND psosr.orig_system_reference = site_sg.site_orig_system_reference
      AND psosr.status  = ''A''
      AND psosr.owner_table_name  = ''HZ_PARTY_SITES''
      AND psosr.owner_table_id = site_sg.party_site_id
      AND psosr.party_id <> posr.owner_table_id
      AND NOT EXISTS
          (SELECT 1 FROM HZ_IMP_TMP_ERRORS err
           WHERE err.batch_id = :CP_BATCH_ID
           AND err.request_id = :REQ_ID
           AND err.interface_table_name = ''HZ_IMP_ADDRESSES_INT''
           AND err.int_row_id = site_sg.int_row_id)
      ';

Line: 757


    ' SELECT cposr.rowid,posr.owner_table_id
      FROM HZ_IMP_CONTACTPTS_SG  cpt_sg,HZ_IMP_CONTACTPTS_INT cpi,
           HZ_ORIG_SYS_REFERENCES posr,
           HZ_ORIG_SYS_REFERENCES cposr
      WHERE cpt_sg.batch_id = :CP_BATCH_ID
      AND cpt_sg.batch_mode_flag = :CP_BATCH_MODE_FLAG
      AND cpt_sg.party_orig_system = :CP_OS
      AND posr.orig_system  = cpt_sg.party_orig_system
      AND posr.orig_system_reference = cpt_sg.party_orig_system_reference
      AND posr.status  = ''A''
      AND posr.owner_table_name  = ''HZ_PARTIES''
      AND cposr.orig_system  = cpi.cp_orig_system
      AND cposr.orig_system_reference = cpi.cp_orig_system_reference
      AND cposr.status  = ''A''
      AND cposr.owner_table_name  = ''HZ_CONTACT_POINTS''
      AND cposr.party_id <> posr.owner_table_id
      AND cpi.rowid = cpt_sg.int_row_id
      AND NOT EXISTS
          (SELECT 1 FROM HZ_IMP_TMP_ERRORS err
           WHERE err.batch_id = :CP_BATCH_ID
           AND err.request_id = :REQ_ID
           AND err.interface_table_name = ''HZ_IMP_CONTACTPTS_INT''
           AND err.int_row_id = cpt_sg.int_row_id)
      ';

Line: 784


     ' select hos.rowid, hps_old.identifying_Address_flag, hps_old.rowid,hps_new.rowid
       from hz_imp_addresses_sg has, hz_orig_sys_references hos, hz_party_sites hps_new,hz_party_sites hps_old
       where has.site_orig_system=''DNB''
       and has.old_site_orig_system_ref=hos.orig_system_reference
       and hos.orig_system=has.site_orig_system
       and hos.owner_table_name=''HZ_PARTY_SITES''
       and hos.status=''A''
       and has.old_site_orig_system_ref is not null
       and has.old_site_orig_system_ref<> has.site_orig_system_reference
       and has.site_orig_system_reference=hps_new.orig_system_reference
       and hps_old.party_site_id = hos.owner_table_id
       and has.party_site_id=hps_new.party_site_id
       and has.action_flag=''I''
       and hps_old.status=''A''
       and hps_new.status=''A''
       and has.party_id=hps_old.party_id
       and hps_old.party_id=hps_new.party_id
       and has.batch_id = :CP_BATCH_ID
       and has.batch_mode_flag=:CP_BATCH_MODE_FLAG
     ';

Line: 806


     ' select hos.rowid, hcp_old.primary_flag, hcp_old.rowid,hcp_new.rowid
       from hz_imp_contactpts_sg hcs, hz_orig_sys_references hos, hz_contact_points hcp_new,hz_contact_points hcp_old
       where hcs.party_orig_system=''DNB''
       and hcs.old_cp_orig_system_ref=hos.orig_system_reference
       and hos.orig_system=hcs.party_orig_system
       and hos.owner_table_name=''HZ_CONTACT_POINTS''
       and hos.status=''A''
       and hcs.old_cp_orig_system_ref is not null
       and hcp_old.contact_point_id = hos.owner_table_id
       and hcs.contact_point_id=hcp_new.contact_point_id
       and hcs.action_flag=''I''
       and hcp_old.status=''A''
       and hcp_new.status=''A''
       and (hcs.party_id=hcp_old.owner_table_id
	    or hcs.party_site_id = hcp_old.owner_table_id)
       and hcp_old.owner_table_id=hcp_new.owner_table_id
       and hcp_old.owner_table_name=hcp_new.owner_table_name
       and hcp_old.contact_point_id <> hcp_new.contact_point_id
       and hcp_old.contact_point_type = hcp_new.contact_point_type
       and hcp_old.contact_point_type = hcs.contact_point_type
       and hcs.batch_id = :CP_BATCH_ID
       and hcs.batch_mode_flag=:CP_BATCH_MODE_FLAG
     ';

Line: 833


    update hz_orig_sys_references set status = 'I', end_date_active = sysdate
    where rowid in (
    select row_id from (
      select /*+ parallel(osr) */ rowid row_id, orig_system_ref_id osrid, orig_system_reference osr,
      owner_table_name, rank() over
      (partition by orig_system_reference, owner_table_name order by last_update_date desc, orig_system_ref_id desc) rn
      from hz_orig_sys_references osr
      where osr.orig_system = P_CONTENT_SRC_TYPE
      and osr.status = 'A'
      and osr.end_date_active is null
    ) r
    where rn > 1);

Line: 862


          UPDATE HZ_ORIG_SYS_REFERENCES
            SET party_id = l_party_owner_table_id(j)
            WHERE rowid = l_row_id(j);

Line: 888


          UPDATE HZ_ORIG_SYS_REFERENCES
           SET party_id = l_party_owner_table_id(j)
           WHERE rowid = l_row_id(j);

Line: 915


	   UPDATE HZ_ORIG_SYS_REFERENCES
	    SET status='I',end_date_active = sysdate
	    WHERE rowid = l_row_id(j);

Line: 920


	   UPDATE HZ_PARTY_SITES
	    SET status='I', identifying_address_flag = 'N'
	    WHERE rowid = l_row_id_old(j);

Line: 925


	   UPDATE HZ_PARTY_SITES
	    SET identifying_address_flag = l_primary_flag(j)
	    WHERE rowid = l_row_id_new(j);

Line: 952


	   UPDATE HZ_ORIG_SYS_REFERENCES
	    SET status='I',end_date_active = sysdate
	    WHERE rowid = l_row_id(j);

Line: 957


	   UPDATE HZ_CONTACT_POINTS
	    SET status='I', primary_flag = 'N'
	    WHERE rowid = l_row_id_old(j);

Line: 962


	   UPDATE HZ_CONTACT_POINTS
	    SET primary_flag = l_primary_flag(j)
	    WHERE rowid = l_row_id_new(j);

Line: 1000


 SELECT ROW_ID,party_id,party_os, party_osr
 FROM
 (  SELECT int_row_id row_id,party_id,PARTY_ORIG_SYSTEM party_os,PARTY_ORIG_SYSTEM_REFERENCE party_osr
    ,row_number() over (partition by party_id order by int_row_id) rn
    from hz_imp_parties_sg
    where batch_id=P_BATCH_ID
    and batch_mode_flag=P_BATCH_MODE_FLAG
    and party_orig_system = P_PARTY_OS
    and action_flag='U'
 )
 WHERE rn>1;

Line: 1013


 SELECT ROW_ID,site_os, site_osr
 FROM
 (  SELECT int_row_id row_id,SITE_ORIG_SYSTEM site_os,SITE_ORIG_SYSTEM_REFERENCE site_osr
    ,row_number() over (partition by party_site_id order by int_row_id) rn
    from hz_imp_addresses_sg
    where batch_id=P_BATCH_ID
    and party_orig_system = P_PARTY_OS
    and action_flag = 'U'
 )
 WHERE rn>1;

Line: 1025


  SELECT pint.party_orig_system, pint.party_orig_system_reference,psg.party_id
  FROM hz_imp_parties_int pint, hz_imp_parties_sg psg
  WHERE pint.batch_id=P_BATCH_ID
  AND pint.party_orig_system=P_PARTY_OS
  AND pint.interface_status='D'
  AND Pint.rowid=psg.int_row_id
  AND pint.party_orig_system=psg.party_orig_system
  AND pint.party_orig_system_reference=psg.party_orig_system_reference
  AND psg.batch_mode_flag=P_BATCH_MODE_FLAG
  AND psg.action_flag='U'  ;

Line: 1058


       UPDATE HZ_IMP_PARTIES_INT party
       SET INTERFACE_STATUS = 'D'
       WHERE rowid=l_rowid(i);

Line: 1086


       UPDATE HZ_IMP_ADDRESSES_INT party
       SET INTERFACE_STATUS = 'D'
       WHERE rowid=l_rowid(i);

Line: 1091


 	--Update site uses
 	FORALL i in l_psosr.FIRST..l_psosr.LAST
 	UPDATE HZ_IMP_ADDRESSUSES_INT
 	SET interface_status = 'D'
 	WHERE batch_id = p_batch_id
 	AND site_orig_system = l_psos(i)
 	AND site_orig_system_reference = l_psosr(i);

Line: 1123


               UPDATE HZ_IMP_CONTACTS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id
               AND ((sub_orig_system = l_pos(i)
                    AND sub_orig_system_reference = l_posr(i))
                    OR
                    (obj_orig_system = l_pos(i)
                     AND obj_orig_system_reference = l_posr(i))
                   );

Line: 1134


              UPDATE HZ_IMP_CONTACTROLES_INT
              SET interface_status = 'D'
              WHERE batch_id = p_batch_id
              AND sub_orig_system = l_pos(i)
              AND sub_orig_system_reference = l_posr(i);

Line: 1142


               UPDATE HZ_IMP_CONTACTPTS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
               AND party_orig_system = l_pos(i)
               AND party_orig_system_reference = l_posr(i);

Line: 1150


               UPDATE HZ_IMP_RELSHIPS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id
               AND ((sub_orig_system = l_pos(i)
                    AND sub_orig_system_reference = l_posr(i))
                    OR
                    (obj_orig_system = l_pos(i)
                     AND obj_orig_system_reference = l_posr(i)
                         AND Nvl(obj_id,l_pid(i)) = l_pid(i))
                   );

Line: 1163


               UPDATE HZ_IMP_CLASSIFICS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
               AND party_orig_system = l_pos(i)
               AND party_orig_system_reference = l_posr(i);

Line: 1171


               UPDATE HZ_IMP_CREDITRTNGS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
               AND party_orig_system = l_pos(i)
               AND party_orig_system_reference = l_posr(i);

Line: 1179


               UPDATE HZ_IMP_FINNUMBERS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
               AND party_orig_system = l_pos(i)
               AND party_orig_system_reference = l_posr(i);

Line: 1187


               UPDATE HZ_IMP_FINREPORTS_INT
               SET interface_status = 'D'
               WHERE batch_id = p_batch_id AND Nvl(party_id,l_pid(i)) = l_pid(i)
               AND party_orig_system = l_pos(i)
               AND party_orig_system_reference = l_posr(i);

Line: 1201


PROCEDURE UPDATE_DISPLAYED_DUNS_PID(
  P_BATCH_ID                      IN NUMBER,
  P_BATCH_MODE_FLAG               IN VARCHAR2
) IS
l_party_id T_ENTITY_ID;

Line: 1209


  select /*+ parallel(pi) leading (pi) use_nl(ps) use_nl(osr) */
        ps.party_id, osr.owner_table_id displayed_duns_party_id
  from hz_orig_sys_references osr,
        hz_imp_parties_int pi,
        hz_imp_parties_sg ps
  where osr.owner_table_name = 'HZ_PARTIES'
    and osr.orig_system = 'DNB'
    and osr.orig_system_reference = pi.displayed_duns
    and pi.batch_id = p_batch_id
    and pi.party_orig_system = 'DNB'
    and pi.rowid = ps.int_row_id
    and ps.batch_mode_flag = p_batch_mode_flag
    and pi.batch_id = ps.batch_id
    and pi.party_orig_system = ps.party_orig_system
    and pi.party_orig_system_reference = ps.party_orig_system_reference
    and pi.party_orig_system_reference <> pi.displayed_duns;

Line: 1230


	hz_utility_v2pub.debug(p_message=>'WRP:UPDATE_DISPLAYED_DUNS_PID()+',
	                       p_prefix=>'',
			       p_msg_level=>fnd_log.level_procedure);

Line: 1242


    update hz_organization_profiles
    set displayed_duns_party_id = l_displayed_duns_party_id(j)
    where party_id = l_party_id(j)
    and effective_end_date is null
    and actual_content_source = 'DNB';

Line: 1251


	hz_utility_v2pub.debug(p_message=>'WRP:UPDATE_DISPLAYED_DUNS_PID()-',
	                       p_prefix=>'',
			       p_msg_level=>fnd_log.level_procedure);

Line: 1256


END UPDATE_DISPLAYED_DUNS_PID;

Line: 1268


    select int_row_id, interface_table_name, error_id
    from hz_imp_tmp_errors
    where batch_id = p_batch_id
    and request_id = p_request_id;

Line: 1299


      update HZ_IMP_PARTIES_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1306


      update HZ_IMP_ADDRESSES_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1313


      update HZ_IMP_CONTACTPTS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1320


      update HZ_IMP_CREDITRTNGS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1327


      update HZ_IMP_CLASSIFICS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1334


      update HZ_IMP_FINREPORTS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1341


      update HZ_IMP_FINNUMBERS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1348


      update HZ_IMP_RELSHIPS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1355


      update HZ_IMP_CONTACTS_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1362


      update HZ_IMP_CONTACTROLES_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and SUB_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1369


      update HZ_IMP_ADDRESSUSES_INT
      set error_id = null,
          interface_status = null
      where BATCH_ID = P_BATCH_ID
      and PARTY_ORIG_SYSTEM = P_ORIG_SYSTEM
      and INTERFACE_STATUS = 'C';

Line: 1382


      update HZ_IMP_PARTIES_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage in (2, 3)
        and status = 'C');

Line: 1396


      update HZ_IMP_ADDRESSES_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1410


      update HZ_IMP_CONTACTPTS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1424


      update HZ_IMP_CREDITRTNGS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1438


      update HZ_IMP_CLASSIFICS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1452


      update HZ_IMP_FINREPORTS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1466


      update HZ_IMP_FINNUMBERS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1480


      update HZ_IMP_RELSHIPS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and sub_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where sub_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1494


      update HZ_IMP_CONTACTS_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and sub_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where sub_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1508


      update HZ_IMP_CONTACTROLES_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and sub_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where sub_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1522


      update HZ_IMP_ADDRESSUSES_INT
      set error_id = null, interface_status = null
      where batch_id = P_BATCH_ID
      and party_orig_system = P_ORIG_SYSTEM
      and interface_status = 'C'
      and exists (
        select 1 from
        hz_imp_work_units
        where party_orig_system_reference
        between from_orig_system_ref and to_orig_system_ref
        and batch_id = P_BATCH_ID
        and stage = 3
        and status = 'C');

Line: 1541


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_INVALD_ADDR_ASSIGN')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'ADDRESS1')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'COUNTRY')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_API_INVALID_FK', 'FK', 'LANGUAGE', 'COLUMN', 'LANGUAGE_CODE',
       'TABLE', 'FND_LANGUAGES')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_API_INVALID_FK', 'FK', 'TIMEZONE_CODE', 'COLUMN',
       'TIMEZONE_CODE', 'TABLE', 'FND_TIMEZONES_B')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'AR_RAPI_DESC_FLEX_INVALID', 'DEF_NAME', 'HZ_PARTY_SITES')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_ADDR_NO_CORRECTION')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_DSS_SECURITY_FAIL', 'USER_NAME', USER_NAME,
       'OPER_NAME', 'UPDATE', 'OBJECT_NAME', 'HZ_PARTY_SITES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name,
token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITES_U1', 'ENTITY',
       'HZ_PARTY_SITES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITES_U2', 'ENTITY',
       'HZ_PARTY_SITES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'C') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_LOCATIONS_U1', 'ENTITY', 'HZ_LOCATIONS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
 when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_NOTALLOW_UPDATE_THIRD_PARTY')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
       E11_FLAG, DUP_VAL_IDX_EXCEP_FLAG,
       ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG, FND_GLOBAL.USER_NAME
  from hz_imp_tmp_errors e
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.interface_table_name = 'HZ_IMP_ADDRESSES_INT';

Line: 1667


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'REPORT_END_DATE', 'DATE1',
       'REPORT_START_DATE')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'AUDIT_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONSOLIDATED_IND',
       'LOOKUP_TYPE', 'YES/NO')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'ESTIMATED_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINAL_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FISCAL_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FORECAST_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'OPENING_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PROFORMA_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'QUALIFIED_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RESTATED_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E12_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SIGNED_BY_PRINCIPALS_IND',
       'LOOKUP_TYPE', 'YES/NO')
  when (E13_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TRIAL_BALANCE_IND',
       'LOOKUP_TYPE', 'YES/NO')

  when (E14_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'UNBALANCED_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E15_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_COMBINATION2', 'COLUMN1', 'issued_period', 'COLUMN2',
       'report_start_date')
  when (E16_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_COMBINATION3', 'COLUMN1', 'report_start_date', 'COLUMN2',
       'report_end_date')
  when (E17_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_REPORTS_U1', 'ENTITY',
       'HZ_FINANCIAL_REPORTS')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_REPORTS_U2', 'ENTITY',
       'HZ_FINANCIAL_REPORTS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINREPORTS_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
 e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
       E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG, E16_FLAG,
       E17_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG,
       INT.REPORT_START_DATE, INT.REPORT_END_DATE
  from hz_imp_tmp_errors e,
       HZ_IMP_FINREPORTS_INT int
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.int_row_id = int.rowid
   and e.interface_table_name = 'HZ_IMP_FINREPORTS_INT';

Line: 1844


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINANCIAL_NUMBER_NAME',
       'LOOKUP_TYPE', 'FIN_NUM_NAME')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_NUMBERS_U1', 'ENTITY',
       'HZ_FINANCIAL_NUMBERS')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_FINANCIAL_NUMBERS_U2', 'ENTITY',
       'HZ_FINANCIAL_NUMBERS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_FINNUMBERS_INT',
       'HZ_IMP_FINREPORT_NOT_FOUND')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
  from hz_imp_tmp_errors e
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.interface_table_name = 'HZ_IMP_FINNUMBERS_INT';

Line: 1896


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FISCAL_YEAREND_MONTH',
       'LOOKUP_TYPE', 'MONTH')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LEGAL_STATUS', 'LOOKUP_TYPE',
       'LEGAL_STATUS')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LOCAL_BUS_IDEN_TYPE',
       'LOOKUP_TYPE', 'LOCAL_BUS_IDEN_TYPE')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'REGISTRATION_TYPE',
       'LOOKUP_TYPE', 'REGISTRATION_TYPE')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HQ_BRANCH_IND', 'LOOKUP_TYPE',
       'HQ_BRANCH_IND')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MINORITY_OWNED_IND',
       'LOOKUP_TYPE', 'YES/NO')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GSA_INDICATOR_FLAG', 'LOOKUP_TYPE',
       'YES/NO')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'IMPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EXPORT_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'BRANCH_FLAG', 'LOOKUP_TYPE',
       'YES/NO')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DISADV_8A_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E12_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LABOR_SURPLUS_IND',
       'LOOKUP_TYPE', 'YES/NO')
  when (E13_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'OOB_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E14_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PARENT_SUB_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E15_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PUBLIC_PRIVATE_OWNERSHIP_FLAG',
       'LOOKUP_TYPE', 'YES/NO')
  when (E16_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SMALL_BUS_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E17_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_EST_IND',
       'LOOKUP_TYPE', 'TOTAL_EMP_EST_IND')
  when (E18_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMP_MIN_IND',
       'LOOKUP_TYPE', 'TOTAL_EMP_MIN_IND')
  when (E19_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TOTAL_EMPLOYEES_IND', 'LOOKUP_TYPE',
       'TOTAL_EMPLOYEES_INDICATOR')
  when (E20_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'WOMAN_OWNED_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E21_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_EST_IND',
       'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_EST_IND')
  when (E22_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMP_AT_PRIMARY_ADR_MIN_IND',
       'LOOKUP_TYPE', 'EMP_AT_PRIMARY_ADR_MIN_IND')
  when (E23_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'MARITAL_STATUS', 'LOOKUP_TYPE',
       'MARITAL_STATUS')
  when (E24_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_PRE_NAME_ADJUNCT',
       'LOOKUP_TYPE', 'CONTACT_TITLE')
  when (E25_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_DECEASED_FLAG_ERROR')
  when (E26_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'HEAD_OF_HOUSEHOLD_FLAG',
       'LOOKUP_TYPE', 'YES/NO')
  when (E27_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_BIRTH')
  when (E28_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'SYSDATE', 'DATE1', 'DATE_OF_DEATH')
  when (E29_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'DATE_OF_DEATH', 'DATE1', 'DATE_OF_BIRTH')
  when (E30_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_PARTY_TYPE_ERROR')
  when (E31_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RENT_OWN_IND', 'LOOKUP_TYPE',
       'OWN_RENT_IND')
  when (E32_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_PARTIES')
  when (E33_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_PARTY_NAME_ERROR')
  when (E34_FLAG='P') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_person_err)
  when (E34_FLAG='O') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_org_err)
  when (E34_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_others_err)
  when (E35_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_NO_RECORD', 'RECORD', 'PARTY', 'VALUE', ' ')
  -- Bug 4310257
  when (E36_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'GENDER', 'LOOKUP_TYPE',
       'HZ_GENDER')
  when (E37_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PERSON_IDEN_TYPE', 'LOOKUP_TYPE',
       'HZ_PERSON_IDEN_TYPE')
  when (E38_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (E39_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_NO_CHANGE_PARTY_NAME')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U1', 'ENTITY', 'HZ_PARTIES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTIES_U2', 'ENTITY', 'HZ_PARTIES')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_ACTION_MISMATCH')
select  /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
       e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
       E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG,
       E16_FLAG, E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG, E21_FLAG,
       E22_FLAG, E23_FLAG, E24_FLAG, E25_FLAG, E26_FLAG, E27_FLAG,
       E28_FLAG, E29_FLAG, E30_FLAG, E31_FLAG, E32_FLAG, E33_FLAG,
       E34_FLAG, E35_FLAG, E36_FLAG, E37_FLAG, E38_FLAG, E39_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
       INT.DATE_OF_BIRTH, INT.DATE_OF_DEATH, FND_GLOBAL.USER_NAME
  from hz_imp_tmp_errors e,
       HZ_IMP_PARTIES_INT int
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.int_row_id = int.rowid
   and e.interface_table_name = 'HZ_IMP_PARTIES_INT';

Line: 2244


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'SUBJECT')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'OBJECT')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_REL_TYPE_ERROR')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RELATIONSHIP_CODE',
       'LOOKUP_TYPE', 'PARTY_RELATIONS_TYPE')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE', 'DATE1', 'START_DATE')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_HIERARCHICAL_FLAG_ERROR')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_API_SUBJECT_OBJECT_IDS')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_RELATIONSHIP_DATE_OVERLAP')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_RELATIONSHIPS')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_rel_err)
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_RELATIONSHIPS_U1', 'ENTITY',
       'HZ_RELATIONSHIPS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_RELSHIPS_INT',
       'HZ_IMP_ACTION_MISMATCH')
select  /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
  e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
       E10_FLAG, E11_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
       FND_GLOBAL.USER_NAME, INT.START_DATE, INT.END_DATE
  from hz_imp_tmp_errors e,
       HZ_IMP_RELSHIPS_INT int
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.int_row_id = int.rowid
   and e.interface_table_name = 'HZ_IMP_RELSHIPS_INT';

Line: 2346


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'SUBJECT')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_IMP_REL_SUBJ_OBJ_ERROR', 'SUB_OR_OBJ', 'OBJECT')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_PARTY_NOT_PERSON', 'TABLE_NAME', 'HZ_IMP_CONTACTS_INT',
       'PARTY_ID_COL', SUB_ORIG_SYSTEM_REFERENCE)
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_PARTY_NOT_ORG', 'TABLE_NAME', 'HZ_IMP_CONTACTS_INT',
       'PARTY_ID_COL', OBJ_ORIG_SYSTEM_REFERENCE)
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'RELATIONSHIP_CODE',
       'LOOKUP_TYPE', 'PARTY_RELATIONS_TYPE')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DEPARTMENT_CODE', 'LOOKUP_TYPE',
       'DEPARTMENT_CODE')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'TITLE', 'LOOKUP_TYPE',
       'CONTACT_TITLE')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'JOB_TITLE_CODE', 'LOOKUP_TYPE',
       'RESPONSIBILITY')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DECISION_MAKER_FLAG',
       'LOOKUP_TYPE', 'YES/NO')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'REFERENCE_USE_FLAG',
       'LOOKUP_TYPE', 'YES/NO')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE', 'DATE1', 'START_DATE')
  when (E12_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_ORG_CONTACTS')
  when (E13_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_IMP_HIERARCHICAL_FLAG_ERROR')
  when (E14_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT', 'HZ_IMP_DUP_REL_IN_INT_ERROR')
  when (E15_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_rel_err)
  when (E16_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_NONUPDATEABLE_TO_NULL', 'COLUMN', 'START_DATE')
 -- Bug 4156586
 when (E17_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT', 'HZ_API_SUBJECT_OBJECT_IDS')
  when (E18_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACTS_U1', 'ENTITY',
       'HZ_ORG_CONTACTS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTS_INT',
       'HZ_IMP_ACTION_MISMATCH')
select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
       e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
       E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG,
       E16_FLAG, E17_FLAG , /* Bug 4156586 */
       E18_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG,
       FND_GLOBAL.USER_NAME, INT.SUB_ORIG_SYSTEM_REFERENCE,
       INT.OBJ_ORIG_SYSTEM_REFERENCE, INT.END_DATE, INT.START_DATE
  from hz_imp_tmp_errors e,
       HZ_IMP_CONTACTS_INT int
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.int_row_id = int.rowid
   and e.interface_table_name = 'HZ_IMP_CONTACTS_INT';

Line: 2507


insert all
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_INVALID_FK', 'FK', 'CLASS_CATEGORY', 'COLUMN',
       'CLASS_CATEGORY', 'TABLE', 'HZ_CLASS_CATEGORIES')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CLASS_CODE', 'LOOKUP_TYPE',
       'HZ_IMP_CLASSIFICS_INT.CLASS_CATEGORY')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_DATE_GREATER', 'DATE2', 'END_DATE_ACTIVE', 'DATE1',
       'START_DATE_ACTIVE')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_IMP_CODE_ASSG_DATE_OVERLAP')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_ALLOW_MUL_ASSIGN_FG')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_LEAFNODE_FLAG')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_NONUPDATEABLE_TO_NULL', 'COLUMN', 'START_DATE')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_ca_err)
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CODE_ASSIGNMENTS_U1', 'ENTITY',
       'HZ_CODE_ASSIGNMENTS')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CODE_ASSIGNMENTS_U2', 'ENTITY',
       'HZ_CODE_ASSIGNMENTS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then -- Bug 4403736
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CLASSIFICS_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
select /*+ leading(e) use_nl(int) rowid(int) */ e.creation_date, e.created_by, e.last_update_date, e.last_updated_by, e.last_update_login, e.program_application_id,
       e.program_id, e.program_update_date, e.error_id, e.batch_id, e.request_id, e.interface_table_name, E2_FLAG, E3_FLAG,
       E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG, FND_GLOBAL.USER_NAME,
       INT.END_DATE_ACTIVE, INT.START_DATE_ACTIVE
  from hz_imp_tmp_errors e,
       HZ_IMP_CLASSIFICS_INT int
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.int_row_id = int.rowid
   and e.interface_table_name = 'HZ_IMP_CLASSIFICS_INT';

Line: 2613


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'BANKRUPTCY_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SUIT_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'DEBARMENT_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name,
token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FINCL_EMBT_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'NO_TRADE_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'JUDGEMENT_IND', 'LOOKUP_TYPE',
       'YES/NO')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'LIEN_IND', 'LOOKUP_TYPE', 'YES/NO')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_OVERRIDE_CODE',
       'LOOKUP_TYPE', 'FAILURE_SCORE_OVERRIDE_CODE')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY2',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E12_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY3',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E13_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY4',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E14_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY5',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E15_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY6',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E16_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY7',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E17_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY8',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E18_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY9',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E19_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_COMMENTARY10',
       'LOOKUP_TYPE', 'FAILURE_SCORE_COMMENTARY')
  when (E20_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'FAILURE_SCORE_OVERRIDE_CODE',
       'LOOKUP_TYPE', 'FAILURE_SCORE_OVERRIDE_CODE')
  when (E21_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E22_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY2',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E23_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY3',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E24_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY4',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E25_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY5',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E26_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY6',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E27_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY7',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E28_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY8',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E29_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY9',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E30_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREDIT_SCORE_COMMENTARY10',
       'LOOKUP_TYPE', 'CREDIT_SCORE_COMMENTARY')
  when (E31_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'PRNT_HQ_BKCY_IND',
       'LOOKUP_TYPE', 'PRNT_HQ_IND')
  when (E32_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_FK', 'FK', 'CURRENCY_CODE', 'COLUMN',
       'MAXIMUM_CREDIT_CURRENCY_CODE', 'TABLE', 'FND_CURRENCIES')
  when (E33_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CREDIT_RATINGS_U1', 'ENTITY',
       'HZ_CREDIT_RATINGS')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CREDIT_RATINGS_U2', 'ENTITY',
       'HZ_CREDIT_RATINGS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CREDITRTNGS_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,

       E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG, E10_FLAG,
       E11_FLAG, E12_FLAG, E13_FLAG, E14_FLAG, E15_FLAG, E16_FLAG,
       E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG, E21_FLAG, E22_FLAG,
       E23_FLAG, E24_FLAG, E25_FLAG, E26_FLAG, E27_FLAG, E28_FLAG,
       E29_FLAG, E30_FLAG, E31_FLAG, E32_FLAG, E33_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG,
       ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
  from hz_imp_tmp_errors e
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.interface_table_name = 'HZ_IMP_CREDITRTNGS_INT';

Line: 2913


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'SITE_USE_TYPE', 'LOOKUP_TYPE',
       'PARTY_SITE_USE_CODE')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_API_UNIQUE_SITE_USE_TYPE')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_IMP_ADDRUSE_OSR_MISMATCH')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_PARTY_SITE_USES_U1', 'ENTITY',
       'HZ_PARTY_SITE_USES')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_ADDRESSUSES_INT',
       'HZ_IMP_ADDR_NOT_FOUND')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG, E3_FLAG, E4_FLAG,
       DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
  from hz_imp_tmp_errors e
 where e.batch_id = P_BATCH_ID
   and e.request_id = P_REQUEST_ID
   and e.interface_table_name = 'HZ_IMP_ADDRESSUSES_INT';

Line: 2970


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'ROLE_TYPE', 'LOOKUP_TYPE',
       'CONTACT_ROLE_TYPE')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_IMP_CONTROLE_OSR_MISMATCH')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACT_ROLES_U1', 'ENTITY',
       'HZ_ORG_CONTACT_ROLES')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'B') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_ORG_CONTACT_ROLES_U2', 'ENTITY',
       'HZ_ORG_CONTACT_ROLES')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTROLES_INT',
       'HZ_IMP_CONTACT_NOT_FOUND')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, DUP_VAL_IDX_EXCEP_FLAG, ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
  from hz_imp_tmp_errors
 where batch_id = P_BATCH_ID
   and request_id = P_REQUEST_ID
   and interface_table_name = 'HZ_IMP_CONTACTROLES_INT';

Line: 3030


insert all
  when (E1_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_TYPE',
       'LOOKUP_TYPE', 'CONTACT_POINT_TYPE')
  when (E2_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_PURPOSE',
       'LOOKUP_TYPE', 'CONTACT_POINT_PURPOSE')
  when (E3_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'EDI_ID_NUMBER')
  when (E4_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'EMAIL_ADDRESS')
  when (E5_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'EMAIL_FORMAT', 'LOOKUP_TYPE',
       'EMAIL_FORMAT')
  when (E6_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_FK', 'FK', 'PHONE_COUNTRY_CODE', 'COLUMN',
       'PHONE_COUNTRY_CODE', 'TABLE', 'HZ_PHONE_COUNTRY_CODES')
  when (E7_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'PHONE_LINE_TYPE', 'LOOKUP_TYPE',
       'PHONE_LINE_TYPE')
  when (E8_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_INVALID_PHONE_PARAMETER')
  when (E9_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'TELEX_NUMBER')
  when (E10_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value, token3_name,
       token3_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_FK', 'FK', 'TIMEZONE_CODE', 'COLUMN',
       'TIMEZONE_CODE', 'TABLE', 'FND_TIMEZONES_B')
  when (E11_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'URL')
  when (E12_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'WEB_TYPE')
  when (E13_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'AR_RAPI_DESC_FLEX_INVALID', 'DFF_NAME', 'HZ_CONTACT_POINTS')
  when (E15_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_MISSING_COLUMN', 'COLUMN', 'CONTACT_POINT_TYPE')
  when (E16_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_IMP_CPT_ADDR_OSR_MISMATCH')
  when (E17_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_NONUPDATEABLE_COLUMN', 'COLUMN', 'CONTACT_POINT_TYPE')
  when (E18_FLAG='P') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_cp_err)
  when (E18_FLAG='S') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_DSS_NO_UPDATE_PRIVILEGE', 'ENTITY_NAME', l_dss_ps_err)
  when (E19_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CONTACT_POINT_PURPOSE',
       'LOOKUP_TYPE', 'CONTACT_POINT_PURPOSE_WEB')
  when (DUP_VAL_IDX_EXCEP_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_IMP_DUP_VAL', 'INDEX', 'HZ_CONTACT_POINTS_U1', 'ENTITY',
       'HZ_CONTACT_POINTS')
  when (ACTION_MISMATCH_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_IMP_ACTION_MISMATCH')
  when (MISSING_PARENT_FLAG = 'P') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_IMP_PARENT_PARTY_NOT_FOUND')
  when (MISSING_PARENT_FLAG = 'A') then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_IMP_PARENT_ADDR_NOT_FOUND')
  when (E20_FLAG IS NULL) then /* Bug 4079902 */
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_NOTALLOW_UPDATE_THIRD_PARTY')
  when (E21_FLAG is null) then
  into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value, token2_name, token2_value)
values (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, 'HZ_IMP_CONTACTPTS_INT',
       'HZ_API_INVALID_LOOKUP', 'COLUMN', 'CREATED_BY_MODULE', 'LOOKUP_TYPE',
       'HZ_CREATED_BY_MODULES')
select creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, E1_FLAG, E2_FLAG,
       E3_FLAG, E4_FLAG, E5_FLAG, E6_FLAG, E7_FLAG, E8_FLAG, E9_FLAG,
       E10_FLAG, E11_FLAG, E12_FLAG, E13_FLAG,  E15_FLAG,
       E16_FLAG, E17_FLAG, E18_FLAG, E19_FLAG, E20_FLAG /* Bug 4079902 */,
       E21_FLAG, DUP_VAL_IDX_EXCEP_FLAG,
       ACTION_MISMATCH_FLAG, MISSING_PARENT_FLAG
  from hz_imp_tmp_errors e
 where e.batch_id = P_BATCH_ID
   and request_id = P_REQUEST_ID
   and e.interface_table_name = 'HZ_IMP_CONTACTPTS_INT';

Line: 3222


  /* Update interface status of errored records to 'E' */
  OPEN c_err(P_BATCH_ID, P_REQUEST_ID);

Line: 3228


  update hz_imp_parties_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_PARTIES_INT';

Line: 3235


  update hz_imp_addresses_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_ADDRESSES_INT';

Line: 3242


  update hz_imp_contactpts_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_CONTACTPTS_INT';

Line: 3249


  update hz_imp_contacts_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_CONTACTS_INT';

Line: 3256


  update hz_imp_contactroles_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_CONTACTROLES_INT';

Line: 3263


  update hz_imp_addressuses_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_ADDRESSUSES_INT';

Line: 3270


  update hz_imp_finreports_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_FINREPORTS_INT';

Line: 3277


  update hz_imp_finnumbers_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_FINNUMBERS_INT';

Line: 3284


  update hz_imp_creditrtngs_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_CREDITRTNGS_INT';

Line: 3291


  update hz_imp_classifics_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_CLASSIFICS_INT';

Line: 3298


  update hz_imp_relships_int
     set interface_status = 'E',
         error_id = l_error_id(i)
   where rowid = l_row_id(i)
     and l_table_name(i) = 'HZ_IMP_RELSHIPS_INT';

Line: 3327


    select bs.import_status, r.phase_code
    from hz_imp_batch_details bs, FND_CONCURRENT_REQUESTS r
    where bs.batch_id = p_batch_id
    and bs.import_req_id = r.request_id(+)
    and bs.run_number = (select max(run_number)-1
                      from hz_imp_batch_details
    	              where batch_id = p_batch_id);

Line: 3336


    SELECT 'Y'
    FROM hz_imp_work_units
    WHERE batch_id = p_batch_id
    AND rownum = 1;

Line: 3392


      delete hz_imp_work_units
      where batch_id = P_BATCH_ID;

Line: 3415


  select 'Y' from hz_imp_work_units
  where batch_id = p_batch_id
  and (postprocess_status is null or postprocess_status <> 'C')
  and rownum = 1;

Line: 3433


       update postprocess_status to NULL so that PP will be
       done for the current request_id */
    update hz_imp_work_units
    set postprocess_status = NULL
    where batch_id = P_BATCH_ID;

Line: 3440


       at all in previous run. Update postprocess_status to 'U'
       so that PP will be done for the current request_id and all
       previous request_ids

       For WUs with 'U' postprocess_status, they will be left as is
       because they were not processed in some previous runs and
       also had errors in the last run. PP will be done for the
       current request_id and all previous request_ids*/
    update hz_imp_work_units
    set postprocess_status = 'U'
    where batch_id = P_BATCH_ID
    and (postprocess_status is NULL or postprocess_status <> 'C');

Line: 3454


       successfully in previous run. Update postprocess_status to NULL
       so that PP will be done for the current request_id */
    update hz_imp_work_units
    set postprocess_status = NULL
    where batch_id = P_BATCH_ID
    and postprocess_status = 'C';

Line: 3549


    SELECT 'Y'
    FROM HZ_IMP_WORK_UNITS
    WHERE batch_id=p_batch_id
    AND
       ((stage>=2
         AND status='C')
        OR
        (stage=3
         AND status='P')
       )
    AND rownum=1;

Line: 3610


  l_last_update_login NUMBER;

Line: 3611


  l_program_update_date DATE;

Line: 3619


  l_update_str_addr_prof VARCHAR2(1);

Line: 3633


    SELECT 'Y'
    FROM hz_imp_parties_int
    WHERE batch_id = p_batch_id
    AND party_orig_system = 'DEFAULT'
    AND interface_status is null
    AND party_orig_system_reference like 'PER%'
    AND rownum = 1;

Line: 3642


    SELECT 'Y'
    FROM hz_imp_tmp_errors
    WHERE batch_id = p_batch_id
    and request_id = p_main_req_id
    AND rownum = 1;

Line: 3649


    SELECT decode(nvl(total_errors, 0), 0, 'N', 'Y')
    FROM HZ_IMP_BATCH_SUMMARY
    WHERE batch_id = p_batch_id;

Line: 3654


    SELECT 'Y'
    FROM hz_imp_parties_sg
    WHERE batch_id = p_batch_id
    AND rownum = 1;

Line: 3660


    SELECT 'Y'
    FROM hz_imp_work_units
    WHERE batch_id = p_batch_id
    AND rownum = 1;

Line: 3696


  select bs.import_status, bs.what_if_flag, r.phase_code, bs.validate_flexfield_flag
  into l_batch_status, l_what_if_flag, l_phase_code , l_flex_validation_prof
  from hz_imp_batch_summary bs, FND_CONCURRENT_REQUESTS r
  where bs.batch_id = P_BATCH_ID
  and bs.import_req_id = r.request_id(+);

Line: 3710


  SELECT SST_FLAG into l_sst_flag
  FROM HZ_ORIG_SYSTEMS_B
  WHERE ORIG_SYSTEM=P_ORIG_SYSTEM
  AND STATUS='A';

Line: 3723


    /* delete duplicate record in sg with same int_row_id. This would happen
       when party loading for 1 worker finishes before the relationship matching
       another worker. Therefore when the latter worker is matching the object party
       it finds multiple active entries in the SSM table. Therefore due to the outer
       joins which are used in SSM matching, it results in muliple duplicate rows
       in relationship staging table.
    */

    delete /*+ ROWID(SG) push_subq */ from hz_imp_relships_sg SG
    where batch_id = P_BATCH_ID
    and rowid in (
      select /*+ no_merge  */ rowid
      from (
        select /*+ parallel(SG) */ rowid,
               rank() over (partition by int_row_id order by relationship_id desc) rn
        from hz_imp_relships_sg SG
        where batch_id = P_BATCH_ID
        )
      where rn > 1
      );

Line: 3771


    delete /*+ ROWID(SG) push_subq */ from hz_imp_relships_sg SG
    where batch_id = P_BATCH_ID
    and sub_orig_system = 'DNB' /* hardcode DNB as it only happens to DNB data */
    and rowid in (
      select /*+ no_merge  */ rowid
      from (
        select /*+ parallel(SG) */ rowid,
               rank() over (partition by relationship_type, relationship_code, sub_orig_system_reference, obj_id
                      order by sub_id desc) rn
        from hz_imp_relships_sg SG
        where batch_id = P_BATCH_ID
        and sub_orig_system = 'DNB'
        )
      where rn > 1
      );

Line: 3844


  l_last_update_login := hz_utility_v2pub.last_update_login;

Line: 3852


  l_update_str_addr_prof := NVL(FND_PROFILE.value('HZ_UPDATE_STD_ADDRESS'), 'N');

Line: 3897


          update_import_status(P_BATCH_ID, 'ERROR');

Line: 3920


    /* Update batch summary status to PROCESSING */
    update_import_status(P_BATCH_ID, 'PROCESSING');

Line: 3926


    CHECK_INVALID_PARTY(P_BATCH_ID,P_REQUEST_ID,l_user_id,l_last_update_login,P_PROGRAM_ID,
                        P_PROGRAM_APPLICATION_ID,l_return_status);

Line: 3932


      update_import_status(P_BATCH_ID, 'ERROR');

Line: 3961


      UPDATE hz_imp_work_units
      SET status = 'C', stage = stage - 1
      WHERE batch_id = P_BATCH_ID
      and status = 'P';

Line: 3970


      UPDATE hz_imp_work_units
      SET hwm_stage = case when nvl(hwm_stage, 0) > stage
	    then hwm_stage else stage end
      WHERE batch_id = P_BATCH_ID;

Line: 3977


      UPDATE hz_imp_work_units
      SET stage = 0
      WHERE batch_id = P_BATCH_ID;

Line: 3991


         we'll insert the exact same row into staging tables. */
      CLEANUP_STAGING(P_BATCH_ID, P_BATCH_MODE_FLAG);

Line: 4002


        UPDATE hz_imp_work_units
        SET stage = 1, status = 'C'
        WHERE batch_id = P_BATCH_ID;

Line: 4009


        UPDATE hz_imp_work_units
        SET stage = 0, status = 'C'
        WHERE batch_id = P_BATCH_ID;

Line: 4064


    SELECT hz_imp_errors_s.NEXTVAL INTO l_start_error_id FROM dual;

Line: 4069


    			 D_SYSDATE, l_user_id, l_last_update_login,
    			 P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID);

Line: 4072


    SELECT hz_imp_errors_s.CURRVAL INTO l_current_error_id FROM dual;

Line: 4081


      update hz_imp_batch_summary
      set IMPORT_STATUS = 'COMPL_ERROR_LIMIT'
      where BATCH_ID = P_BATCH_ID;

Line: 4085


      UPDATE hz_imp_batch_details
      SET import_status = 'COMPL_ERROR_LIMIT'
      WHERE batch_id = P_BATCH_ID
      AND run_number = (SELECT max(run_number)
    		        FROM hz_imp_batch_details
    		        WHERE batch_id = P_BATCH_ID);

Line: 4116


    /* Update displayed duns party id. Originally done in PP-Wait */
    IF P_ORIG_SYSTEM = 'DNB' THEN
      UPDATE_DISPLAYED_DUNS_PID(P_BATCH_ID, P_BATCH_MODE_FLAG);

Line: 4132


    /* Delete Work Unit */
    /* delete hz_imp_work_units where batch_id = P_BATCH_ID;

Line: 4136


	   hz_utility_v2pub.debug(p_message=>'WRP:work united deleted',
			          p_prefix =>'',
			          p_msg_level=>fnd_log.level_statement);

Line: 4141


    /* Update counts in batch summary */

    IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
	hz_utility_v2pub.debug(p_message=>'WRP:post_import_counts()+',
	                       p_prefix=>'',
			       p_msg_level=>fnd_log.level_procedure);

Line: 4158


    /* Update import status */
    /* Check if any error in current run. If so, update import_status in
       both batch summary and details to COMPL_ERRORS. */
    OPEN c_error(P_BATCH_ID, P_REQUEST_ID);

Line: 4165


      update_import_status(P_BATCH_ID, 'COMPL_ERRORS');

Line: 4177


        update_import_status(P_BATCH_ID, 'COMPL_ERRORS2');

Line: 4179


        update_import_status(P_BATCH_ID, 'COMPLETED');

Line: 4208


      select nvl(total_records_imported, 0)
      into l_total_records_imported
      from hz_imp_batch_summary
      where batch_id = P_BATCH_ID;

Line: 4257


			l_last_update_login,
			P_PROGRAM_ID,
			P_PROGRAM_APPLICATION_ID,
			P_REQUEST_ID,
                    	l_resp_appl_id,
                    	l_g_miss_char,
                    	l_g_miss_num,
                    	l_g_miss_date,
			l_flex_validation_prof,
 			l_dss_security_prof,
 			l_allow_disabled_lookup_prof,
 			l_profile_version_prof,
 			P_WHAT_IF_ANALYSIS,
 			P_REGISTRY_DEDUP,
 			P_REGISTRY_DEDUP_MATCH_RULE_ID
                      );

Line: 4291


			l_last_update_login,
			P_PROGRAM_ID,
			P_PROGRAM_APPLICATION_ID,
			P_REQUEST_ID,
                    	l_resp_appl_id,
                    	l_g_miss_char,
                    	l_g_miss_num,
                    	l_g_miss_date,
			l_flex_validation_prof,
 			l_dss_security_prof,
 			l_allow_disabled_lookup_prof,
 			l_profile_version_prof,
 			l_update_str_addr_prof,
 			l_maintain_loc_hist_prof,
 			l_allow_addr_corr_prof
                      );

Line: 4363


  UPDATE HZ_IMP_WORK_UNITS
    SET STATUS = 'P',
        STAGE = P_STAGE
  WHERE STATUS = 'C'
    AND BATCH_ID = P_BATCH_ID
    AND STAGE = P_STAGE - 1
    AND ROWNUM = 1
  RETURNING ORIG_SYSTEM,
            FROM_ORIG_SYSTEM_REF,
            TO_ORIG_SYSTEM_REF,
            HWM_STAGE,
            POSTPROCESS_STATUS
  INTO P_OS, P_FROM_OSR, P_TO_OSR, P_HWM_STAGE, P_PP_STATUS;

Line: 4389


  UPDATE HZ_IMP_WORK_UNITS
    SET POSTPROCESS_STATUS = 'P'
  WHERE STATUS = 'C'
    AND BATCH_ID = P_BATCH_ID
    AND STAGE IN (2, 3)
    AND ROWNUM = 1
    AND NVL(POSTPROCESS_STATUS, 'X') = NVL(P_PP_STATUS, 'X')
  RETURNING ORIG_SYSTEM,
            FROM_ORIG_SYSTEM_REF,
            TO_ORIG_SYSTEM_REF
  INTO P_OS, P_FROM_OSR, P_TO_OSR;

Line: 4409


 'begin INSERT into hz_imp_work_units
  ( batch_id,
    orig_system,
    from_orig_system_ref,
    to_orig_system_ref,
    status,
    stage
  )
  ( select :1,
           :2,
           min(party_orig_system_reference),
           max(party_orig_system_reference),
           ''C'',
           0
    from
    ( select party_orig_system_reference,
             floor(sum(count(*)) over
               ( order by
                   party_orig_system_reference
                 rows unbounded preceding
               )/:3
             ) wu
      from
      ( -- Party
        select /*+ index_ffs(a,hz_imp_parties_int_u1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_parties_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Party Site
       select /*+ index_ffs(a,hz_imp_addresses_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_addresses_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Contact Points
       select /*+ index_ffs(a,hz_imp_contactpts_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_contactpts_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Credit Ratings
       select /*+ index_ffs(a,hz_imp_creditrtngs_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_creditrtngs_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Financial Reports
       select /*+ index_ffs(a,hz_imp_finreports_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_finreports_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Financial Numbers
       select /*+ index_ffs(a,hz_imp_finnumbers_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_finnumbers_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Code Assignments
       select /*+ index_ffs(a,hz_imp_classifics_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_classifics_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Relationships
       select /*+ index_ffs(a,hz_imp_relships_int_n1) parallel_index(a)*/
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_relships_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Contacts
       select /*+ index_ffs(a,hz_imp_contacts_int_n1) parallel_index(a)*/
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_contacts_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Contact Roles
       select /*+ index_ffs(a,hz_imp_contactroles_int_n1)
parallel_index(a)*/
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_contactroles_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Address Uses
       select /*+ index_ffs(a,hz_imp_addressuses_int_n1) parallel_index(a)*/
              party_orig_system_reference
         from hz_imp_addressuses_int a
        where party_orig_system = :2
          and batch_id=:1
      )
      group by party_orig_system_reference
    )
    group by wu
  ); end;';

Line: 4506


 'begin INSERT into hz_imp_work_units
  ( batch_id,
    orig_system,
    from_orig_system_ref,
    to_orig_system_ref,
    status,
    stage
  )
  ( select :1,
           :2,
           min(party_orig_system_reference),
           max(party_orig_system_reference),
           ''C'',
           0
    from
    ( select party_orig_system_reference,
             floor(sum(count(*)) over
               ( order by
                   party_orig_system_reference
                 rows unbounded preceding
               )/:3
             ) wu
      from
      ( -- Party
        select /*+ index(a,hz_imp_parties_int_u1) */
              party_orig_system_reference
         from hz_imp_parties_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Party Site
       select /*+ index(a,hz_imp_addresses_int_n1) */
              party_orig_system_reference
         from hz_imp_addresses_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Contact Points
       select /*+ index(a,hz_imp_contactpts_int_n1) */
              party_orig_system_reference
         from hz_imp_contactpts_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Credit Ratings
       select /*+ index(a,hz_imp_creditrtngs_int_n1) */
              party_orig_system_reference
         from hz_imp_creditrtngs_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Financial Reports
       select /*+ index(a,hz_imp_finreports_int_n1) */
              party_orig_system_reference
         from hz_imp_finreports_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Financial Numbers
       select /*+ index(a,hz_imp_finnumbers_int_n1) */
              party_orig_system_reference
         from hz_imp_finnumbers_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Code Assignments
       select /*+ index(a,hz_imp_classifics_int_n1) */
              party_orig_system_reference
         from hz_imp_classifics_int a
        where party_orig_system = :2
          and batch_id=:1
        union all -- Relationships
       select /*+ index(a,hz_imp_relships_int_n1) */
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_relships_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Contacts
       select /*+ index(a,hz_imp_contacts_int_n1) */
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_contacts_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Contact Roles
       select /*+ index(a,hz_imp_contactroles_int_n1) */
              sub_orig_system_reference party_orig_system_reference
         from hz_imp_contactroles_int a
        where sub_orig_system = :2
          and batch_id=:1
        union all -- Address Uses
       select /*+ index(a,hz_imp_addressuses_int_n1) */
              party_orig_system_reference
         from hz_imp_addressuses_int a
        where party_orig_system = :2
          and batch_id=:1
      )
      group by party_orig_system_reference
    )
    group by wu
  ); end;';

Line: 4627


    select est_no_of_records into est_count
    from HZ_IMP_BATCH_SUMMARY where batch_id = P_BATCH_ID;

Line: 4636


  select num_rows into party_count
  from sys.dba_tables where upper(table_name) = 'HZ_IMP_PARTIES_INT'
  and owner = l_schema;

Line: 4666


    SELECT 'Y'
    FROM dual
    WHERE EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_PARTIES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_ADDRESSES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTPTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CREDITRTNGS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_FINREPORTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_FINNUMBERS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CLASSIFICS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_RELSHIPS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTROLES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_ADDRESSUSES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1);

Line: 4737


    SELECT 'Y'
    FROM dual
    WHERE EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_ADDRESSES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTPTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CREDITRTNGS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_FINREPORTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_FINNUMBERS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CLASSIFICS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_RELSHIPS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTROLES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_CONTACTS_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1)
    OR EXISTS (
    SELECT 'Y'
    FROM HZ_IMP_ADDRESSUSES_SG
    WHERE batch_id = p_batch_id
    AND batch_mode_flag = p_batch_mode_flag
    AND rownum = 1);

Line: 4821


P_LAST_UPDATE_LOGIN IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_PROGRAM_APPLICATION_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2)
IS


l_row_id T_ROWID;

Line: 4834


SELECT rowid,party_id
FROM hz_imp_parties_int hip
WHERE
batch_id=L_BATCH_ID
AND hip.party_id is not null
AND not exists
(select 1
 from hz_parties hp
 where hp.party_id=hip.party_id
);

Line: 4857


       INSERT into hz_imp_errors (
       creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id,
       program_id, program_update_date, error_id, batch_id, request_id, interface_table_name, message_name,
       token1_name, token1_value)
  values (
       l_sysdate, P_USER_ID, l_sysdate, P_USER_ID, P_LAST_UPDATE_LOGIN, P_PROGRAM_APPLICATION_ID,
       P_PROGRAM_ID, l_sysdate, HZ_IMP_ERRORS_S.NextVal, P_BATCH_ID, P_REQUEST_ID, 'HZ_IMP_PARTIES_INT',
       'HZ_IMP_INVALID_PARTY_ID','PARTY_ID',l_party_id(i))
  RETURNING error_id BULK COLLECT INTO l_error_id;

Line: 4869


       UPDATE HZ_IMP_PARTIES_INT
       SET INTERFACE_STATUS='E',
       ERROR_ID=l_error_id(i)
       WHERE ROWID=l_row_id(i);