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_PURGE SQL Statements

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

Line: 10


CURSOR app_id IS select distinct(dict_application_id) from hz_merge_dictionary
where parent_entity_name='HZ_PARTIES'
and  nvl(validate_purge_flag,'Y') <> 'N'; --5125968

Line: 16


select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name)  entity_name, fk_column_name, decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name)  pk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES''  AND object_table_name = ''HZ_PARTIES''
AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
from hz_merge_dictionary where parent_entity_name like 'HZ_%' and dict_application_id = app_id
and fk_column_name IS NOT NULL and entity_name not in ('AS_CHANGED_ACCOUNTS_ALL','POS_PARTIES_V','POS_PARTY_SITES_V','WSH_LOCATION_OWNERS','ZX_PARTY_TAX_PROFILE','CE_BANKS_MERGE_V','CE_BANK_BRANCHES_MERGE_V','WSH_SUPPLIER_SF_SITES_V')
and nvl(validate_purge_flag,'Y') <> 'N'; --5125968

Line: 24


select  decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES''  AND object_table_name = ''HZ_PARTIES''
AND directional_flag = ''F''', join_clause) join_clause,
parent_entity_name, fk_data_type
from hz_merge_dictionary where parent_entity_name like 'HZ_%' and dict_application_id = app_id
and entity_name in('HZ_CUST_ACCOUNTS','HZ_CUST_ACCT_SITES_ALL','HZ_CUSTOMER_PROFILES') OR
(entity_name ='HZ_PARTY_RELATIONSHIPS' and fk_column_name<>'PARTY_ID') OR
(entity_name ='HZ_ORGANIZATION_PROFILES' and fk_column_name ='DISPLAYED_DUNS_PARTY_ID');

Line: 34


stmt1 varchar2(31000):= 'delete from hz_purge_gt temp where ';

Line: 35


stmt2 varchar2(31000):= 'delete /*+ parallel(temp) */ from hz_purge_gt temp where ';

Line: 83


   HZ_GEN_PLSQL.ADD_LINE('select party_id from hz_purge_gt;');

Line: 96


    HZ_GEN_PLSQL.add_line('delete from hz_application_trans_gt; ');

Line: 118


       delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1, 'TRUE', s2, cnt);

Line: 137


       delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1,'TRUE', s2,cnt);

Line: 150


      HZ_GEN_PLSQL.ADD_LINE('--delete and insert records into hz_purge_gt for an application');

Line: 152


	  stmt3:= 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';

Line: 164


    stmt1 := 'delete from hz_purge_gt temp where ';

Line: 165


	stmt3 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';

Line: 184


       delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1,'FALSE', s2,cnt);

Line: 187


   	    stmt4 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp ';

Line: 188


        stmt5 := ' where not exists(select ''Y'' from hz_application_trans_gt appl where appl.app_id = '||appid||' and appl.party_id=temp.party_id) and ';

Line: 205


        HZ_GEN_PLSQL.ADD_LINE('delete from hz_purge_gt temp where temp.party_id in (select appl.party_id from hz_application_trans_gt appl) ;');

Line: 277


insert_stmt varchar2(5000):= 'insert into hz_purge_gt(party_id) select party_id from hz_parties where party_type<>''NULL'' ';

Line: 278


delete_stmt varchar2(5000):= 'delete from hz_purge_gt ';

Line: 280


mergedict_update_date date;

Line: 301


select h.party_id, p.party_name from hz_purge_gt h, hz_parties p where h.party_id=p.party_id;

Line: 304


select to_date(timestamp,'YYYY-MM-DD:HH24:MI:SS') from sys.user_objects
where object_type='PACKAGE BODY' and status='VALID'and object_name='HZ_PURGE_GEN';

Line: 307


cursor dict_update_date is
select max(last_update_date) from hz_merge_dictionary;

Line: 311


select subset_sql, attributes_flag from hz_purge_batches where batch_id = to_number(batchid);

Line: 314


select count(*) from hz_purge_gt;

Line: 317


select distinct(app_id), party_id from hz_application_trans_gt;

Line: 320


select distinct(party_id) from hz_purge_gt;

Line: 338


open dict_update_date;

Line: 339


 fetch dict_update_date into mergedict_update_date;

Line: 340


close dict_update_date;

Line: 342


/* Generate the body of the Package HZ_PURGE_GEN if last_update_date of hz_merge_dictionary
   is greater than the package generation date*/


 if (mergedict_update_date is null or time_stamp is null or mergedict_update_date>time_stamp)  then
  hz_purge.generate_body(p_init_msg_list, x_return_status, x_msg_count, x_msg_data);

Line: 356


   insert_stmt := insert_stmt||' and '||where_clause;

Line: 358


   execute immediate delete_stmt;

Line: 359


   execute immediate insert_stmt;

Line: 372


   /* Insert into the hz_purge_candidates table */
 /*  insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
   LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
   select to_number(batchid), a.party_id, substr(a.party_name,1,250), a.party_number,
   a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
   cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
   sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
   from hz_parties a , hz_purge_gt temp, hz_contact_points cp where
   temp.party_id = a.party_id and
   cp.owner_table_id(+)=temp.party_id and
   cp.contact_point_type(+)='PHONE' and
   cp.owner_table_name(+)='HZ_PARTIES' and
   cp.primary_flag(+)='Y';

Line: 393


     insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
     LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
     	 select to_number(batchid), pid, substr(a.party_name,1,250), a.party_number,
   		 a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
  		 cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
  		 sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
  		 from hz_parties a , hz_contact_points cp where
  		 a.party_id = pid and
  		 cp.owner_table_id(+)= a.party_id and
  		 cp.contact_point_type(+)='PHONE' and
  		 cp.owner_table_name(+)='HZ_PARTIES' and
   		 cp.primary_flag(+)='Y';

Line: 411


   update hz_purge_batches set num_candidates=num_parties, num_marked=num_parties, status='IDENTIFICATION_COMPLETE' where batch_id=to_number(batchid);

Line: 419


   		insert into hz_non_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,APPL_ID,
   		PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,CREATION_DATE,
   		LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
   		select to_number(batchid), p_id, app_id, substr(a.party_name,1,250), a.party_number,
   		null, null, null,
   		x_sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
   		from hz_parties a where
   		a.party_id = p_id;

Line: 445


    update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);

Line: 450


    update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);

Line: 455


    update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);

Line: 472


select count(*) from hz_application_trans_gt;

Line: 487


delete from hz_purge_gt;

Line: 488


insert into hz_purge_gt(party_id) select party_id from hz_parties where party_id=partyid;

Line: 555


select candidate_party_id, party_name from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED';

Line: 584


DELETE from HZ_PARTY_USG_ASSIGNMENTS where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 586


log(' HZ_PARTY_USG_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 588


DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 590


log(' HZ_ORGANIZATION_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 592


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;

Line: 597


log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 599


execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using batchid;

Line: 603


 log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows', conc_prg);

Line: 609


DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';

Line: 612


log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 614


DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')));

Line: 618


log(' HZ_ORG_CONTACT_ROLES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 621


execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')))' using batchid;

Line: 625


log(' HZ_STAGED_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 631


DELETE from HZ_ORG_CONTACTS where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line: 634


log(' HZ_ORG_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 636


DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line: 639


log(' HZ_PARTY_SITE_USES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 641


execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using batchid;

Line: 645


log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 647


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using batchid;

Line: 651


log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 654


execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))' using batchid;

Line: 657


log(' HZ_STAGED_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 663


DELETE from HZ_PARTY_SITES where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 665


log(' HZ_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 667


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
and OWNER_TABLE_NAME=''HZ_PARTIES'') and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;

Line: 671


log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 674


execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
and OWNER_TABLE_NAME=''HZ_PARTIES'')' using batchid;

Line: 678


log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 684


DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')
and OWNER_TABLE_NAME='HZ_PARTIES';

Line: 687


log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 689


DELETE from HZ_PERSON_PROFILES where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 691


log(' HZ_PERSON_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 693


DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 695


log(' HZ_FINANCIAL_PROFILE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 697


DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 699


log(' HZ_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 701


DELETE from HZ_CERTIFICATIONS where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 703


log(' HZ_CERTIFICATIONS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 704


DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 706


log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 707


DELETE from HZ_SECURITY_ISSUED where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 709


log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 710


DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
(select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line: 713


 log(' HZ_FINANCIAL_NUMBERS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 714


DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 716


log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 717


DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 719


log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 721


DELETE from HZ_PERSON_INTEREST where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 723


log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 725


DELETE from HZ_CITIZENSHIP where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 727


log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 729


DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
(select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line: 732


log(' HZ_WORK_CLASS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 733


DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 735


log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 736


DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 738


log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 739


DELETE from HZ_EDUCATION where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 741


log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 742


DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 744


log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 745


execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;

Line: 748


log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 749


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using batchid;

Line: 752


log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 753


execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;

Line: 755


log(' HZ_ORIG_SYS_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 758


execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')' using batchid;

Line: 760


log(' HZ_STAGED_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 766


DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 768


log(' AS_CHANGED_ACCOUNTS_ALL : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 771


delete from wsh_location_owners wlo
where  wlo.owner_party_id in (select candidate_party_id
			  from hz_purge_candidates
		          where batch_id=batchid and status='IDENTIFIED')
and exists (
             select 'x'
	     from wsh_location_owners wlo1
	     where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);

Line: 780


update wsh_location_owners wlo
set       wlo.owner_party_id = -1
where  wlo.owner_party_id in (select candidate_party_id
			  from hz_purge_candidates
		          where batch_id=batchid and status='IDENTIFIED'
			  and rownum = 1 )-- if more than one party has same location, only update one.
and  not exists (
               select 'x'
	       from wsh_location_owners wlo1
               where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);

Line: 794


delete from wsh_location_owners wlo
where  wlo.owner_party_id in (select candidate_party_id
			  from hz_purge_candidates
		          where batch_id=batchid and status='IDENTIFIED')
and wlo.owner_party_id <> -1;

Line: 800


Delete from zx_party_tax_profile  PTP
where  ptp.party_type_code = 'THIRD_PARTY'
and    ptp.party_id in (select candidate_party_id
			from hz_purge_candidates
			where batch_id=batchid and status='IDENTIFIED')
and not exists (Select 'x'
                from   zx_registrations reg
                where  ptp.party_tax_profile_id = reg.party_tax_profile_id)
and not exists (Select 'x'
                from   zx_exemptions ex
                where  ptp.party_tax_profile_id = ex.party_tax_profile_id)
and not exists (Select 'x'
                from   ZX_REPORT_CODES_ASSOC assoc
                where  assoc.entity_code = 'ZX_PARTY_TAX_PROFILE'
                and    assoc.ENTITY_ID = ptp.party_tax_profile_id)
and not exists (Select 'x'
                from   hz_code_assignments HCA
                where  HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
                AND    HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);

Line: 820


DELETE from HZ_PARTIES where party_id in
(select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line: 822


log(' HZ_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line: 827


  execute immediate 'update hz_relationships set party_id=null where party_id in
   (select pur_cand.candidate_party_id from hz_purge_candidates pur_cand, hz_parties parties where pur_cand.batch_id=:1 and
      pur_cand.candidate_party_id = parties.party_id and parties.party_type=''PARTY_RELATIONSHIP'' )' using batchid;

Line: 832


   delete from hz_parties where party_id in (select party_id from hz_relationships
					     where (subject_id  in (select candidate_party_id
			  						from hz_purge_candidates
		          						where batch_id=batchid and status='IDENTIFIED')
			  			    or object_id  in (select candidate_party_id
			  						from hz_purge_candidates
		          						where batch_id=batchid and status='IDENTIFIED')))
				and status = 'M';

Line: 845


   delete from hz_relationships where (subject_id  in (select candidate_party_id
			  from hz_purge_candidates
		          where batch_id=batchid and status='IDENTIFIED')
			  or object_id  in (select candidate_party_id
			  from hz_purge_candidates
		          where batch_id=batchid and status='IDENTIFIED'))
			  and status = 'M';

Line: 856


  update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';

Line: 857


  update hz_purge_candidates set status='PURGED' where candidate_party_id in (
    select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
  and batch_id<>batchid;

Line: 861


  /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
  update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;

Line: 882


    update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

Line: 887


   update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

Line: 892


    update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

Line: 905


 insertrows number;

Line: 907


 select count(*) from hz_purge_candidates where candidate_party_id=p_id and status<>'PURGED';

Line: 923


   insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
   LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
   select to_number('-1'), a.party_id, substr(a.party_name,1,250), a.party_number,
   a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
   cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
   sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
   from hz_parties a , hz_contact_points cp where a.party_id = p_party_id and
   cp.owner_table_id(+)=a.party_id and cp.contact_point_type(+)='PHONE' and cp.primary_flag(+)='Y' and
   cp.owner_table_name(+)='HZ_PARTIES';

Line: 935


DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID = p_party_id;

Line: 937


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;

Line: 943


execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using p_party_id;

Line: 951


DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id) and OWNER_TABLE_NAME='HZ_PARTY_SITES';

Line: 954


DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id));

Line: 959


execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
(SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1))' using p_party_id;

Line: 967


DELETE from HZ_ORG_CONTACTS where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id);

Line: 970


DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id);

Line: 973


execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using p_party_id;

Line: 976


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using p_party_id;

Line: 980


execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)' using p_party_id;

Line: 987


DELETE from HZ_PARTY_SITES where PARTY_ID = p_party_id;

Line: 989


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')
and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;

Line: 994


execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')' using p_party_id;

Line: 1001


DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID = p_party_id and OWNER_TABLE_NAME='HZ_PARTIES';

Line: 1003


DELETE from HZ_PERSON_PROFILES where PARTY_ID = p_party_id;

Line: 1005


DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID = p_party_id;

Line: 1007


DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID = p_party_id;

Line: 1009


DELETE from HZ_CERTIFICATIONS where PARTY_ID = p_party_id;

Line: 1011


DELETE from HZ_CREDIT_RATINGS where PARTY_ID = p_party_id;

Line: 1013


DELETE from HZ_SECURITY_ISSUED where PARTY_ID = p_party_id;

Line: 1015


DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
 ( select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID = p_party_id);

Line: 1018


DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID = p_party_id;

Line: 1020


DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID = p_party_id;

Line: 1022


DELETE from HZ_PERSON_INTEREST where PARTY_ID = p_party_id;

Line: 1024


DELETE from HZ_CITIZENSHIP where PARTY_ID = p_party_id;

Line: 1026


DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
(select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID = p_party_id);

Line: 1029


DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID = p_party_id;

Line: 1031


DELETE from HZ_PERSON_LANGUAGE where PARTY_ID = p_party_id;

Line: 1033


DELETE from HZ_EDUCATION where PARTY_ID = p_party_id;

Line: 1035


DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID = p_party_id;

Line: 1037


execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES''' using p_party_id;

Line: 1039


execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID = :1 and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using p_party_id;

Line: 1041


execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id = :1' using p_party_id;

Line: 1044


execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID = :1' using p_party_id;

Line: 1049


DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID = p_party_id;

Line: 1051


delete from wsh_location_owners wlo
where  wlo.owner_party_id = p_party_id
and exists (
             select 'x'
	     from wsh_location_owners wlo1
	     where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);

Line: 1058


update wsh_location_owners wlo
set       wlo.owner_party_id = -1
where  wlo.owner_party_id = p_party_id
and  not exists (
               select 'x'
	       from wsh_location_owners wlo1
               where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);

Line: 1066


Delete from zx_party_tax_profile  PTP
where  ptp.party_type_code = 'THIRD_PARTY'
and    ptp.party_id = p_party_id
and not exists (Select 'x'
                from   zx_registrations reg
                where  ptp.party_tax_profile_id = reg.party_tax_profile_id)
and not exists (Select 'x'
                from   zx_exemptions ex
                where  ptp.party_tax_profile_id = ex.party_tax_profile_id)
and not exists (Select 'x'
                from   ZX_REPORT_CODES_ASSOC assoc
                where  assoc.entity_code = 'ZX_PARTY_TAX_PROFILE'
                and    assoc.ENTITY_ID = ptp.party_tax_profile_id)
and not exists (Select 'x'
                from   hz_code_assignments HCA
                where  HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
                AND    HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);

Line: 1084


DELETE from HZ_PARTIES where party_id = p_party_id;

Line: 1093


   execute immediate 'update hz_relationships set party_id=null where party_id=:1 and party_id in
   (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;

Line: 1096


   delete from hz_parties where party_id in (select party_id from hz_relationships
                                             where (subject_id = p_party_id or object_id = p_party_id))
					     and status = 'M';

Line: 1102


   delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';

Line: 1105


 /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
 update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;

Line: 1157


select count(*) from hz_application_trans_gt where app_id=appid;

Line: 1167


       insert into hz_application_trans_gt(app_id) values(appid);

Line: 1170


       insert into hz_purge_gt(party_id) values(single_party);

Line: 1215


   SELECT DATA_TYPE FROM sys.all_tab_columns
   WHERE table_name = p_table
   AND COLUMN_NAME = p_column
   AND owner = schema1;

Line: 1268


	 select entity_name, fk_column_name, merge_dict_id, dict_application_id
	 from hz_merge_dictionary
	 where fk_data_type is null;

Line: 1288


        update hz_merge_dictionary
        set fk_data_type = l_data_type
        where merge_dict_id = l_merge_dict_id;

Line: 1300


Select application_short_name from fnd_application where application_id=app_id;

Line: 1326


   select min(column_position) from dba_ind_columns where table_name = ent_name
   and column_name = ent_col_name
   and index_owner = schema1 and table_owner = schema1;

Line: 1331


   select index_name, column_position from dba_ind_columns
   where table_name = ent_name and column_name = colmn_name
   and index_owner = schema1 and table_owner = schema1;

Line: 1336


   select column_name from dba_ind_columns where table_name = ent_name
   and index_name = ind_name
   and column_position


Line: 1342

select 'Y' from dual where
(ent_name,colmn_name)
in (('AS_ACCESSES_ALL', 'CUSTOMER_ID'),('AS_ACCESSES_ALL', 'ADDRESS_ID'),('AS_ACCESSES_ALL', 'PARTNER_CUSTOMER_ID'),
('AS_ACCESSES_ALL', 'PARTNER_CONT_PARTY_ID'),('AS_ACCESSES_ALL', 'PARTNER_ADDRESS_ID'),('ASG_PARTY_ACC_V', 'PARTY_ID'),
('OKE_K_FUNDING_SOURCES_PM_HV', 'K_PARTY_ID'),('IGW_PROP_PERSONS_TCA_V', 'PERSON_PARTY_ID'),
('MIS_HZ_MERGE_VETO_PARTIES', 'PARTY_ID'),('MIS_HZ_MERGE_VETO_PARTY_SITES', 'PARTY_SITE_ID'),('JTF_PERZ_QUERY_PARAM','PARAMETER_VALUE'));

Line: 1350

select instr(join_clause,col_name) from dual;

Line: 1417

PROCEDURE delete_template
(e1 VARCHAR2, fk1 VARCHAR2,pk1 VARCHAR2,j1 VARCHAR2, pe1 VARCHAR2,  fk_data_typ1 VARCHAR2,
 first VARCHAR2, concat_string OUT NOCOPY VARCHAR2, cnt NUMBER) IS

e2 varchar2(50);

Line: 1457

select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES''  AND object_table_name = ''HZ_PARTIES''
AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
from hz_merge_dictionary where entity_name = parent;

Line: 1464

select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES''  AND object_table_name = ''HZ_PARTIES''
AND directional_flag = ''F''', join_clause) join_clause,
parent_entity_name,fk_data_type
from hz_merge_dictionary where entity_name = parent2;

Line: 1480

       	select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;

Line: 1486

        l_sql := 'delete from hz_purge_gt temp where ';

Line: 1487

        p1:= ' exists (select ''Y'' from '||e1;

Line: 1493

        l_sql := 'delete from hz_purge_gt temp where 1<>1 ';

Line: 1494

        p1:= ' or exists (select ''Y'' from '||e1;

Line: 1500

        l_sql := 'delete from hz_purge_gt temp where ';

Line: 1501

        p1:= partyid||' in (select /*+ parallel(xx)*/ xx.'||fk1||' from '||e1;

Line: 1527

         	--select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j1,1,instr(j1,'group')-1)) into p3 from dual;

Line: 1528

          	select decode(instr(j2,'group'),0,j2,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;

Line: 1534

        l_sql := 'delete from hz_purge_gt temp where ';

Line: 1536

         p1:= ' exists (select ''Y'' from '||e2;

Line: 1538

         p1:= ' or exists (select ''Y'' from '||e2;

Line: 1546

        l_sql := 'delete from hz_purge_gt temp where 1<>1 ';

Line: 1547

        p1:= ' or exists (select ''Y'' from '||e2;

Line: 1554

        l_sql := 'delete from hz_purge_gt temp where ';

Line: 1556

         p1:= partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;

Line: 1558

         p1:= ' or '||partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;

Line: 1576

          	--select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p7 from dual;

Line: 1577

          	select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p7 from dual;

Line: 1583

           p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk1||' from '||e1;

Line: 1589

          	p5 := '(select ''Y'' from '||e1;

Line: 1628

           	select decode(instr(j3,'group'),0,j3,substr(j3,1,instr(j3,'group')-1)) into p3 from dual;

Line: 1634

           l_sql := 'delete from hz_purge_gt temp where ';

Line: 1636

            p1:= ' exists (select ''Y'' from '||e3;

Line: 1638

            p1:= ' or exists (select ''Y'' from '||e3;

Line: 1646

           l_sql := 'delete from hz_purge_gt temp where 1<>1 ';

Line: 1647

           p1:= ' or exists (select ''Y'' from '||e3;

Line: 1655

            l_sql := 'delete from hz_purge_gt temp where ';

Line: 1656

            p1:= partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;

Line: 1658

            p1:= ' or '||partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;

Line: 1676

          	--select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j2,1,instr(j2,'group')-1)) into p7 from dual;

Line: 1677

           	select decode(instr(j2,'group'),0,j2,substr(j2,1,instr(j2,'group')-1)) into p7 from dual;

Line: 1683

           p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk2||' from '||e2;

Line: 1697

           p5:= ' (select ''Y'' from '||e2;

Line: 1712

           	select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p11 from dual;

Line: 1718

           p9:= ' in (select /*+ parallel(zz)*/ yy.'||pk2||' from '||e1;

Line: 1724

           p9:= ' (select ''Y'' from '||e1;