APPS.HZ_PURGE dependencies on HZ_PURGE

Line 1: PACKAGE BODY HZ_PURGE AS

1: PACKAGE BODY HZ_PURGE AS
2: /* $Header: ARHPURGB.pls 120.48 2011/03/23 04:54:24 awu ship $ */
3: PROCEDURE GENERATE_BODY
4: (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5: x_return_status OUT NOCOPY VARCHAR2,

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

30: and entity_name in('HZ_CUST_ACCOUNTS','HZ_CUST_ACCT_SITES_ALL','HZ_CUSTOMER_PROFILES') OR
31: (entity_name ='HZ_PARTY_RELATIONSHIPS' and fk_column_name<>'PARTY_ID') OR
32: (entity_name ='HZ_ORGANIZATION_PROFILES' and fk_column_name ='DISPLAYED_DUNS_PARTY_ID');
33:
34: stmt1 varchar2(31000):= 'delete from hz_purge_gt temp where ';
35: stmt2 varchar2(31000):= 'delete /*+ parallel(temp) */ from hz_purge_gt temp where ';
36: stmt3 varchar2(31000):= '';
37: stmt4 varchar2(31000):= '';
38: stmt5 varchar2(31000):= '';

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

31: (entity_name ='HZ_PARTY_RELATIONSHIPS' and fk_column_name<>'PARTY_ID') OR
32: (entity_name ='HZ_ORGANIZATION_PROFILES' and fk_column_name ='DISPLAYED_DUNS_PARTY_ID');
33:
34: stmt1 varchar2(31000):= 'delete from hz_purge_gt temp where ';
35: stmt2 varchar2(31000):= 'delete /*+ parallel(temp) */ from hz_purge_gt temp where ';
36: stmt3 varchar2(31000):= '';
37: stmt4 varchar2(31000):= '';
38: stmt5 varchar2(31000):= '';
39: appid number(15);

Line 68: HZ_GEN_PLSQL.new('HZ_PURGE_GEN', 'PACKAGE BODY');

64:
65: -- initialize API return status to success.
66: x_return_status := FND_API.G_RET_STS_SUCCESS;
67: /* Beginnig of the dynamic package generation*/
68: HZ_GEN_PLSQL.new('HZ_PURGE_GEN', 'PACKAGE BODY');
69: HZ_GEN_PLSQL.add_line('CREATE OR REPLACE PACKAGE BODY HZ_PURGE_GEN AS');
70: HZ_GEN_PLSQL.add_line('PROCEDURE IDENTIFY_CANDIDATES(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
71: x_return_status OUT NOCOPY VARCHAR2,
72: x_msg_count OUT NOCOPY NUMBER,

Line 69: HZ_GEN_PLSQL.add_line('CREATE OR REPLACE PACKAGE BODY HZ_PURGE_GEN AS');

65: -- initialize API return status to success.
66: x_return_status := FND_API.G_RET_STS_SUCCESS;
67: /* Beginnig of the dynamic package generation*/
68: HZ_GEN_PLSQL.new('HZ_PURGE_GEN', 'PACKAGE BODY');
69: HZ_GEN_PLSQL.add_line('CREATE OR REPLACE PACKAGE BODY HZ_PURGE_GEN AS');
70: HZ_GEN_PLSQL.add_line('PROCEDURE IDENTIFY_CANDIDATES(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
71: x_return_status OUT NOCOPY VARCHAR2,
72: x_msg_count OUT NOCOPY NUMBER,
73: x_msg_data OUT NOCOPY VARCHAR2,

Line 83: HZ_GEN_PLSQL.ADD_LINE('select party_id from hz_purge_gt;');

79: HZ_GEN_PLSQL.add_line('parties_count2 number;');
80: HZ_GEN_PLSQL.add_line('single_party number;');
81: HZ_GEN_PLSQL.add_line(fnd_global.local_chr(10));
82: HZ_GEN_PLSQL.ADD_LINE('cursor repopulate is');
83: HZ_GEN_PLSQL.ADD_LINE('select party_id from hz_purge_gt;');
84: HZ_GEN_PLSQL.add_line(fnd_global.local_chr(10));
85: HZ_GEN_PLSQL.add_line('BEGIN');
86: HZ_GEN_PLSQL.add_line(fnd_global.local_chr(10));
87: HZ_GEN_PLSQL.add_line('SAVEPOINT identify_candidates;');

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

146: end if;
147:
148: if (s1 is not null) then
149: stmt1:= stmt1||fnd_global.local_chr(10)||s1;
150: HZ_GEN_PLSQL.ADD_LINE('--delete and insert records into hz_purge_gt for an application');
151: HZ_GEN_PLSQL.ADD_LINE('appid:='||appid||';');
152: stmt3:= 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';
153: HZ_GEN_PLSQL.ADD_LINE(' if(regid_proc = true) then ');
154: HZ_GEN_PLSQL.ADD_LINE(stmt3||fnd_global.local_chr(10)||s1||';');

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

148: if (s1 is not null) then
149: stmt1:= stmt1||fnd_global.local_chr(10)||s1;
150: HZ_GEN_PLSQL.ADD_LINE('--delete and insert records into hz_purge_gt for an application');
151: HZ_GEN_PLSQL.ADD_LINE('appid:='||appid||';');
152: stmt3:= 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';
153: HZ_GEN_PLSQL.ADD_LINE(' if(regid_proc = true) then ');
154: HZ_GEN_PLSQL.ADD_LINE(stmt3||fnd_global.local_chr(10)||s1||';');
155: HZ_GEN_PLSQL.ADD_LINE(' else ');
156: HZ_GEN_PLSQL.ADD_LINE(stmt1||';');

Line 159: --HZ_GEN_PLSQL.ADD_LINE('HZ_PURGE.post_app_logic(appid,single_party,check_flag);');

155: HZ_GEN_PLSQL.ADD_LINE(' else ');
156: HZ_GEN_PLSQL.ADD_LINE(stmt1||';');
157: HZ_GEN_PLSQL.ADD_LINE('end if;');
158:
159: --HZ_GEN_PLSQL.ADD_LINE('HZ_PURGE.post_app_logic(appid,single_party,check_flag);');
160: HZ_GEN_PLSQL.ADD_LINE(fnd_global.local_chr(10));
161: end if;
162: id := 1;
163: s1 := null;

Line 164: stmt1 := 'delete from hz_purge_gt temp where ';

160: HZ_GEN_PLSQL.ADD_LINE(fnd_global.local_chr(10));
161: end if;
162: id := 1;
163: s1 := null;
164: stmt1 := 'delete from hz_purge_gt temp where ';
165: stmt3 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';
166: END LOOP;
167: CLOSE app_id;
168:

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

161: end if;
162: id := 1;
163: s1 := null;
164: stmt1 := 'delete from hz_purge_gt temp where ';
165: stmt3 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp where ';
166: END LOOP;
167: CLOSE app_id;
168:
169: OPEN app_id;

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

183: cnt :=1;
184: delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1,'FALSE', s2,cnt);
185: if(s2 is not NULL) then
186: HZ_GEN_PLSQL.add_line('--'||e1||';'||fk1);
187: stmt4 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp ';
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 ';
189: HZ_GEN_PLSQL.ADD_LINE('appid:='||appid||';');
190: --HZ_GEN_PLSQL.ADD_LINE('HZ_PURGE.post_app_logic(appid,single_party,check_flag);');
191: HZ_GEN_PLSQL.ADD_LINE(' if(regid_proc = true) then ');

Line 190: --HZ_GEN_PLSQL.ADD_LINE('HZ_PURGE.post_app_logic(appid,single_party,check_flag);');

186: HZ_GEN_PLSQL.add_line('--'||e1||';'||fk1);
187: stmt4 := 'insert into hz_application_trans_gt(app_id,party_id) select '||appid||', temp.party_id from hz_purge_gt temp ';
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 ';
189: HZ_GEN_PLSQL.ADD_LINE('appid:='||appid||';');
190: --HZ_GEN_PLSQL.ADD_LINE('HZ_PURGE.post_app_logic(appid,single_party,check_flag);');
191: HZ_GEN_PLSQL.ADD_LINE(' if(regid_proc = true) then ');
192: HZ_GEN_PLSQL.ADD_LINE(stmt4||stmt5||fnd_global.local_chr(10)||s2||';');
193: HZ_GEN_PLSQL.ADD_LINE(' else ');
194: HZ_GEN_PLSQL.add_line(stmt2||s2||';');

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) ;');

201: end if;
202: END LOOP;
203: CLOSE app_id;
204: HZ_GEN_PLSQL.ADD_LINE(' if(regid_proc = true) then ');
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) ;');
206: HZ_GEN_PLSQL.ADD_LINE('end if;');
207: HZ_GEN_PLSQL.ADD_LINE(fnd_global.local_chr(10));
208: HZ_GEN_PLSQL.add_line('EXCEPTION');
209: HZ_GEN_PLSQL.add_line('WHEN OTHERS THEN');

Line 224: HZ_GEN_PLSQL.add_line('END HZ_PURGE_GEN;');

220: HZ_GEN_PLSQL.add_line('RAISE FND_API.G_EXC_ERROR;');
221:
222: HZ_GEN_PLSQL.add_line('END IDENTIFY_CANDIDATES;');
223:
224: HZ_GEN_PLSQL.add_line('END HZ_PURGE_GEN;');
225: HZ_GEN_PLSQL.compile_code;
226:
227:
228: -- standard call to get message count and if count is 1, get message info.

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

273: x_msg_count number;
274: x_msg_data varchar2(100);
275: request_id number;
276: where_clause varchar2(5000);
277: insert_stmt varchar2(5000):= 'insert into hz_purge_gt(party_id) select party_id from hz_parties where party_type<>''NULL'' ';
278: delete_stmt varchar2(5000):= 'delete from hz_purge_gt ';
279: time_stamp date;
280: mergedict_update_date date;
281: phone_number varchar2(100);

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

274: x_msg_data varchar2(100);
275: request_id number;
276: where_clause varchar2(5000);
277: insert_stmt varchar2(5000):= 'insert into hz_purge_gt(party_id) select party_id from hz_parties where party_type<>''NULL'' ';
278: delete_stmt varchar2(5000):= 'delete from hz_purge_gt ';
279: time_stamp date;
280: mergedict_update_date date;
281: phone_number varchar2(100);
282: candpartyid number;

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

297: l_schema VARCHAR2(255);
298: l_tmp VARCHAR2(2000);*/
299:
300: cursor printparties is
301: select h.party_id, p.party_name from hz_purge_gt h, hz_parties p where h.party_id=p.party_id;
302:
303: cursor time_stmp is
304: select to_date(timestamp,'YYYY-MM-DD:HH24:MI:SS') from sys.user_objects
305: where object_type='PACKAGE BODY' and status='VALID'and object_name='HZ_PURGE_GEN';

Line 305: where object_type='PACKAGE BODY' and status='VALID'and object_name='HZ_PURGE_GEN';

301: select h.party_id, p.party_name from hz_purge_gt h, hz_parties p where h.party_id=p.party_id;
302:
303: cursor time_stmp is
304: select to_date(timestamp,'YYYY-MM-DD:HH24:MI:SS') from sys.user_objects
305: where object_type='PACKAGE BODY' and status='VALID'and object_name='HZ_PURGE_GEN';
306:
307: cursor dict_update_date is
308: select max(last_update_date) from hz_merge_dictionary;
309:

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

307: cursor dict_update_date is
308: select max(last_update_date) from hz_merge_dictionary;
309:
310: cursor b1 is
311: select subset_sql, attributes_flag from hz_purge_batches where batch_id = to_number(batchid);
312:
313: cursor numparties is
314: select count(*) from hz_purge_gt;
315:

Line 314: select count(*) from hz_purge_gt;

310: cursor b1 is
311: select subset_sql, attributes_flag from hz_purge_batches where batch_id = to_number(batchid);
312:
313: cursor numparties is
314: select count(*) from hz_purge_gt;
315:
316: cursor appl_trans is
317: select distinct(app_id), party_id from hz_application_trans_gt;
318:

Line 320: select distinct(party_id) from hz_purge_gt;

316: cursor appl_trans is
317: select distinct(app_id), party_id from hz_application_trans_gt;
318:
319: cursor purge_parties is
320: select distinct(party_id) from hz_purge_gt;
321:
322: BEGIN
323: retcode:=0;
324:

Line 342: /* Generate the body of the Package HZ_PURGE_GEN if last_update_date of hz_merge_dictionary

338: open dict_update_date;
339: fetch dict_update_date into mergedict_update_date;
340: close dict_update_date;
341:
342: /* Generate the body of the Package HZ_PURGE_GEN if last_update_date of hz_merge_dictionary
343: is greater than the package generation date*/
344:
345:
346: if (mergedict_update_date is null or time_stamp is null or mergedict_update_date>time_stamp) then

Line 347: hz_purge.generate_body(p_init_msg_list, x_return_status, x_msg_count, x_msg_data);

343: is greater than the package generation date*/
344:
345:
346: if (mergedict_update_date is null or time_stamp is null or mergedict_update_date>time_stamp) then
347: hz_purge.generate_body(p_init_msg_list, x_return_status, x_msg_count, x_msg_data);
348: log('Start Time ='||sysdate,conc_prg);
349: end if;
350:
351: open b1;

Line 370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);

366: regid_flag := true;
367: end if;
368: end if;
369:
370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);
371:
372: /* Insert into the hz_purge_candidates table */
373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
374: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)

Line 372: /* Insert into the hz_purge_candidates table */

368: end if;
369:
370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);
371:
372: /* Insert into the hz_purge_candidates table */
373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
374: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
375: select to_number(batchid), a.party_id, substr(a.party_name,1,250), a.party_number,
376: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,

Line 373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

369:
370: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, false, conc_prg, regid_flag);
371:
372: /* Insert into the hz_purge_candidates table */
373: /* insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
374: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
375: select to_number(batchid), a.party_id, substr(a.party_name,1,250), a.party_number,
376: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
377: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',

Line 379: from hz_parties a , hz_purge_gt temp, hz_contact_points cp where

375: select to_number(batchid), a.party_id, substr(a.party_name,1,250), a.party_number,
376: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
377: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
378: sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
379: from hz_parties a , hz_purge_gt temp, hz_contact_points cp where
380: temp.party_id = a.party_id and
381: cp.owner_table_id(+)=temp.party_id and
382: cp.contact_point_type(+)='PHONE' and
383: cp.owner_table_name(+)='HZ_PARTIES' and

Line 393: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

389: loop
390: FETCH purge_parties into pid;
391: exit when purge_parties%NOTFOUND;
392: i:=i+1;
393: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
394: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
395: select to_number(batchid), pid, substr(a.party_name,1,250), a.party_number,
396: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
397: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',

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

407:
408: num_parties:= i;
409: --dbms_output.put_line('num_parties='||num_parties);
410:
411: update hz_purge_batches set num_candidates=num_parties, num_marked=num_parties, status='IDENTIFICATION_COMPLETE' where batch_id=to_number(batchid);
412: if(regid_flag=true) then
413: open appl_trans;
414: loop
415: FETCH appl_trans into app_id, p_id;

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

441: WHEN FND_API.G_EXC_ERROR THEN
442: retcode := 2;
443: errbuf := errbuf || logerror||SQLERRM;
444: FND_FILE.close;
445: update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);
446: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447: retcode := 2;
448: errbuf := errbuf || logerror||SQLERRM;
449: FND_FILE.close;

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

446: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447: retcode := 2;
448: errbuf := errbuf || logerror||SQLERRM;
449: FND_FILE.close;
450: update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);
451: WHEN OTHERS THEN
452: retcode := 2;
453: errbuf := errbuf || logerror||SQLERRM;
454: FND_FILE.close;

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

451: WHEN OTHERS THEN
452: retcode := 2;
453: errbuf := errbuf || logerror||SQLERRM;
454: FND_FILE.close;
455: update hz_purge_batches set status='IDENTIFICATION_ERROR' where batch_id=to_number(batchid);
456: END;
457:
458: /* To check if a single party has any transactions */
459:

Line 487: delete from hz_purge_gt;

483:
484: -- initialize API return status to success.
485: x_return_status := FND_API.G_RET_STS_SUCCESS;
486:
487: delete from hz_purge_gt;
488: insert into hz_purge_gt(party_id) select party_id from hz_parties where party_id=partyid;
489: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, true, false);
490: open party;
491: fetch party into party_count;

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

484: -- initialize API return status to success.
485: x_return_status := FND_API.G_RET_STS_SUCCESS;
486:
487: delete from hz_purge_gt;
488: insert into hz_purge_gt(party_id) select party_id from hz_parties where party_id=partyid;
489: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, true, false);
490: open party;
491: fetch party into party_count;
492: if party_count>0 then

Line 489: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, true, false);

485: x_return_status := FND_API.G_RET_STS_SUCCESS;
486:
487: delete from hz_purge_gt;
488: insert into hz_purge_gt(party_id) select party_id from hz_parties where party_id=partyid;
489: hz_purge_gen.identify_candidates(p_init_msg_list, x_return_status, x_msg_count, x_msg_data, true, false);
490: open party;
491: fetch party into party_count;
492: if party_count>0 then
493: allow_purge:='false';

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

551: l_tmp VARCHAR2(2000);
552:
553:
554: cursor printparties(batchId number) is
555: select candidate_party_id, party_name from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED';
556:
557: BEGIN
558: retcode:=0;
559:

Line 585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

581:
582: hz_common_pub.disable_cont_source_security;
583: --4307686
584: DELETE from HZ_PARTY_USG_ASSIGNMENTS where PARTY_ID in
585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
586: log(' HZ_PARTY_USG_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
587:
588: DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID in
589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

585: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
586: log(' HZ_PARTY_USG_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
587:
588: DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID in
589: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
590: log(' HZ_ORGANIZATION_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
591:
592: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
593: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in

Line 595: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')

591:
592: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
593: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
594: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
595: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
596: and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
597: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
598: begin
599: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in

Line 602: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using batchid;

598: begin
599: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
600: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
601: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
602: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using batchid;
603: log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows', conc_prg);
604: EXCEPTION
605: WHEN OTHERS THEN
606: null;

Line 611: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';

607: END;
608:
609: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
610: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
611: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
612: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
613:
614: DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
615: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (

Line 617: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')));

613:
614: DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
615: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
616: SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
617: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')));
618: log(' HZ_ORG_CONTACT_ROLES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
619:
620: begin
621: execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in

Line 624: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')))' using batchid;

620: begin
621: execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
622: ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
623: (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
624: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')))' using batchid;
625: log(' HZ_STAGED_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
626: exception
627: when others then
628: null;

Line 633: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

629: end;
630:
631: DELETE from HZ_ORG_CONTACTS where PARTY_SITE_ID in
632: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
633: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
634: log(' HZ_ORG_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
635:
636: DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
637: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in

Line 638: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

634: log(' HZ_ORG_CONTACTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
635:
636: DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
637: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
638: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
639: log(' HZ_PARTY_SITE_USES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
640:
641: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
642: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in

Line 643: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))

639: log(' HZ_PARTY_SITE_USES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
640:
641: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
642: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
643: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
644: and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using batchid;
645: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
646:
647: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in

Line 649: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))

645: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
646:
647: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
648: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
649: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))
650: and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using batchid;
651: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
652:
653: begin

Line 656: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))' using batchid;

652:
653: begin
654: execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
655: ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID in
656: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED''))' using batchid;
657: log(' HZ_STAGED_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
658: exception
659: when others then
660: null;

Line 664: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

660: null;
661: end;
662:
663: DELETE from HZ_PARTY_SITES where PARTY_ID in
664: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
665: log(' HZ_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
666:
667: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
668: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in

Line 669: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

665: log(' HZ_PARTY_SITES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
666:
667: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
668: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
669: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
670: and OWNER_TABLE_NAME=''HZ_PARTIES'') and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using batchid;
671: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
672:
673: begin

Line 676: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

672:
673: begin
674: execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
675: ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
676: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
677: and OWNER_TABLE_NAME=''HZ_PARTIES'')' using batchid;
678: log(' HZ_STAGED_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
679: exception
680: when others then

Line 685: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')

681: null;
682: end;
683:
684: DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
685: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED')
686: and OWNER_TABLE_NAME='HZ_PARTIES';
687: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
688:
689: DELETE from HZ_PERSON_PROFILES where PARTY_ID in

Line 690: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

686: and OWNER_TABLE_NAME='HZ_PARTIES';
687: log(' HZ_CONTACT_POINTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
688:
689: DELETE from HZ_PERSON_PROFILES where PARTY_ID in
690: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
691: log(' HZ_PERSON_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
692:
693: DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID in
694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

690: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
691: log(' HZ_PERSON_PROFILES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
692:
693: DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID in
694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
695: log(' HZ_FINANCIAL_PROFILE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
696:
697: DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID in
698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

694: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
695: log(' HZ_FINANCIAL_PROFILE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
696:
697: DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID in
698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
699: log(' HZ_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
700:
701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

698: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
699: log(' HZ_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
700:
701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
703: log(' HZ_CERTIFICATIONS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

701: DELETE from HZ_CERTIFICATIONS where PARTY_ID in
702: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
703: log(' HZ_CERTIFICATIONS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
707: DELETE from HZ_SECURITY_ISSUED where PARTY_ID in
708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

704: DELETE from HZ_CREDIT_RATINGS where PARTY_ID in
705: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
706: log(' HZ_CREDIT_RATINGS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
707: DELETE from HZ_SECURITY_ISSUED where PARTY_ID in
708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
710: DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

Line 712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

708: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
709: log(' HZ_SECURITY_ISSUED : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
710: DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
713: log(' HZ_FINANCIAL_NUMBERS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

711: (select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID in
712: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
713: log(' HZ_FINANCIAL_NUMBERS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
717: DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID in
718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

714: DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID in
715: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
716: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
717: DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID in
718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
720:
721: DELETE from HZ_PERSON_INTEREST where PARTY_ID in
722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

718: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
719: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
720:
721: DELETE from HZ_PERSON_INTEREST where PARTY_ID in
722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
723: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
724:
725: DELETE from HZ_CITIZENSHIP where PARTY_ID in
726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

Line 726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

722: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
723: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
724:
725: DELETE from HZ_CITIZENSHIP where PARTY_ID in
726: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
727: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
728:
729: DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in

Line 731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));

727: log(' HZ_FINANCIAL_REPORTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
728:
729: DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in
731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
732: log(' HZ_WORK_CLASS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

730: (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID in
731: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED'));
732: log(' HZ_WORK_CLASS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

733: DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID in
734: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
735: log(' HZ_EMPLOYMENT_HISTORY : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

736: DELETE from HZ_PERSON_LANGUAGE where PARTY_ID in
737: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
738: log(' HZ_PERSON_LANGUAGE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);

Line 743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

739: DELETE from HZ_EDUCATION where PARTY_ID in
740: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
741: log(' HZ_EDUCATION : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
745: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;

Line 746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

742: DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID in
743: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
744: log(' HZ_INDUSTRIAL_REFERENCE : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
745: execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;
748: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
749: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

Line 750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')

746: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
747: and OWNER_TABLE_NAME=''HZ_PARTIES''' using batchid;
748: log(' HZ_CODE_ASSIGNMENTS : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
749: execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
751: and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using batchid;
752: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
753: execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id in
754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;

Line 754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;

750: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')
751: and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using batchid;
752: log(' HZ_CONTACT_PREFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
753: execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id in
754: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'') ' using batchid;
755: log(' HZ_ORIG_SYS_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
756:
757: begin
758: execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID in

Line 759: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')' using batchid;

755: log(' HZ_ORIG_SYS_REFERENCES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
756:
757: begin
758: execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID in
759: (select candidate_party_id from hz_purge_candidates where batch_id=:1 and status=''IDENTIFIED'')' using batchid;
760: log(' HZ_STAGED_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
761: exception
762: when others then
763: null;

Line 767: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

763: null;
764: end;
765:
766: DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID in
767: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
768: log(' AS_CHANGED_ACCOUNTS_ALL : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
769:
770:
771: delete from wsh_location_owners wlo

Line 773: from hz_purge_candidates

769:
770:
771: delete from wsh_location_owners wlo
772: where wlo.owner_party_id in (select candidate_party_id
773: from hz_purge_candidates
774: where batch_id=batchid and status='IDENTIFIED')
775: and exists (
776: select 'x'
777: from wsh_location_owners wlo1

Line 783: from hz_purge_candidates

779:
780: update wsh_location_owners wlo
781: set wlo.owner_party_id = -1
782: where wlo.owner_party_id in (select candidate_party_id
783: from hz_purge_candidates
784: where batch_id=batchid and status='IDENTIFIED'
785: and rownum = 1 )-- if more than one party has same location, only update one.
786: and not exists (
787: select 'x'

Line 796: from hz_purge_candidates

792: -- make sure to keep the ones owner_party_id = -1
793:
794: delete from wsh_location_owners wlo
795: where wlo.owner_party_id in (select candidate_party_id
796: from hz_purge_candidates
797: where batch_id=batchid and status='IDENTIFIED')
798: and wlo.owner_party_id <> -1;
799:
800: Delete from zx_party_tax_profile PTP

Line 803: from hz_purge_candidates

799:
800: Delete from zx_party_tax_profile PTP
801: where ptp.party_type_code = 'THIRD_PARTY'
802: and ptp.party_id in (select candidate_party_id
803: from hz_purge_candidates
804: where batch_id=batchid and status='IDENTIFIED')
805: and not exists (Select 'x'
806: from zx_registrations reg
807: where ptp.party_tax_profile_id = reg.party_tax_profile_id)

Line 821: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');

817: where HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
818: AND HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);
819:
820: DELETE from HZ_PARTIES where party_id in
821: (select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='IDENTIFIED');
822: log(' HZ_PARTIES : Deleted '||SQL%ROWCOUNT||' rows',conc_prg);
823: fnd_file.close;
824:
825: --if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null

Line 828: (select pur_cand.candidate_party_id from hz_purge_candidates pur_cand, hz_parties parties where pur_cand.batch_id=:1 and

824:
825: --if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null
826:
827: execute immediate 'update hz_relationships set party_id=null where party_id in
828: (select pur_cand.candidate_party_id from hz_purge_candidates pur_cand, hz_parties parties where pur_cand.batch_id=:1 and
829: pur_cand.candidate_party_id = parties.party_id and parties.party_type=''PARTY_RELATIONSHIP'' )' using batchid;
830:
831:
832: delete from hz_parties where party_id in (select party_id from hz_relationships

Line 834: from hz_purge_candidates

830:
831:
832: delete from hz_parties where party_id in (select party_id from hz_relationships
833: where (subject_id in (select candidate_party_id
834: from hz_purge_candidates
835: where batch_id=batchid and status='IDENTIFIED')
836: or object_id in (select candidate_party_id
837: from hz_purge_candidates
838: where batch_id=batchid and status='IDENTIFIED')))

Line 837: from hz_purge_candidates

833: where (subject_id in (select candidate_party_id
834: from hz_purge_candidates
835: where batch_id=batchid and status='IDENTIFIED')
836: or object_id in (select candidate_party_id
837: from hz_purge_candidates
838: where batch_id=batchid and status='IDENTIFIED')))
839: and status = 'M';
840:
841:

Line 846: from hz_purge_candidates

842:
843: -- bug 4947069
844:
845: delete from hz_relationships where (subject_id in (select candidate_party_id
846: from hz_purge_candidates
847: where batch_id=batchid and status='IDENTIFIED')
848: or object_id in (select candidate_party_id
849: from hz_purge_candidates
850: where batch_id=batchid and status='IDENTIFIED'))

Line 849: from hz_purge_candidates

845: delete from hz_relationships where (subject_id in (select candidate_party_id
846: from hz_purge_candidates
847: where batch_id=batchid and status='IDENTIFIED')
848: or object_id in (select candidate_party_id
849: from hz_purge_candidates
850: where batch_id=batchid and status='IDENTIFIED'))
851: and status = 'M';
852:
853:

Line 855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'

851: and status = 'M';
852:
853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;

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

852:
853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:

Line 857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (

853:
854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */

Line 858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')

854:
855: --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
856: update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
862: update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;

Line 861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */

857: update hz_purge_candidates set status='PURGED' where candidate_party_id in (
858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
862: update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;
863:
864: l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
865:

Line 862: update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;

858: select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
859: and batch_id<>batchid;
860:
861: /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
862: update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;
863:
864: l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
865:
866: begin

Line 882: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

878: WHEN FND_API.G_EXC_ERROR THEN
879: retcode := 2;
880: errbuf := errbuf || logerror||SQLERRM;
881: FND_FILE.close;
882: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
883: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884: retcode := 2;
885: errbuf := errbuf || logerror||SQLERRM;
886: FND_FILE.close;

Line 887: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

883: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884: retcode := 2;
885: errbuf := errbuf || logerror||SQLERRM;
886: FND_FILE.close;
887: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
888: WHEN OTHERS THEN
889: retcode := 2;
890: errbuf := errbuf || logerror||SQLERRM;
891: FND_FILE.close;

Line 892: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;

888: WHEN OTHERS THEN
889: retcode := 2;
890: errbuf := errbuf || logerror||SQLERRM;
891: FND_FILE.close;
892: update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
893:
894: END;
895:
896: /* Purge Single Party */

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

903: p_party_id NUMBER) IS
904:
905: insertrows number;
906: cursor existing_id(p_id number) is
907: select count(*) from hz_purge_candidates where candidate_party_id=p_id and status<>'PURGED';
908:
909: BEGIN
910:
911: SAVEPOINT PURGE_PARTY;

Line 923: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,

919: x_return_status := FND_API.G_RET_STS_SUCCESS;
920: ---please enter the directory as the third parameter to which the file needs to be copied.
921: --fnd_file.put_names('delparty.log',null,'/sqlcom/outbound');
922:
923: insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
924: LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
925: select to_number('-1'), a.party_id, substr(a.party_name,1,250), a.party_number,
926: a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
927: cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',

Line 1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */

1101:
1102: delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1103:
1104:
1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1107:
1108: --fnd_file.close;
1109: hz_common_pub.enable_cont_source_security;

Line 1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;

1102: delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1103:
1104:
1105: /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1106: update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1107:
1108: --fnd_file.close;
1109: hz_common_pub.enable_cont_source_security;
1110: -- standard call to get message count and if count is 1, get message info.

Line 1170: insert into hz_purge_gt(party_id) values(single_party);

1166: if parties_count1>0 then
1167: insert into hz_application_trans_gt(app_id) values(appid);
1168: if check_flag=true then
1169: --dbms_output.put_line('insert single party into table'||appid);
1170: insert into hz_purge_gt(party_id) values(single_party);
1171: end if;
1172: end if;
1173: end if;
1174:

Line 1486: l_sql := 'delete from hz_purge_gt temp where ';

1482: p3:=' ';
1483: end if;
1484:
1485: if(first = 'TRUE' AND cnt=1) then
1486: l_sql := 'delete from hz_purge_gt temp where ';
1487: p1:= ' exists (select ''Y'' from '||e1;
1488: p2:= ' xx where xx.'||fk1||' = '||partyid;
1489: if j1 is not null then
1490: p3:= ' and '||'('||p3||')';

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

1489: if j1 is not null then
1490: p3:= ' and '||'('||p3||')';
1491: end if;
1492: elsif (first = 'TRUE' AND cnt>1) then
1493: l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1494: p1:= ' or exists (select ''Y'' from '||e1;
1495: p2:= ' xx where xx.'||fk1||' = '||partyid;
1496: if j1 is not null then
1497: p3:= ' and '||'('||p3||')';

Line 1500: l_sql := 'delete from hz_purge_gt temp where ';

1496: if j1 is not null then
1497: p3:= ' and '||'('||p3||')';
1498: end if;
1499: elsif(first = 'FALSE' AND cnt=1) then
1500: l_sql := 'delete from hz_purge_gt temp where ';
1501: p1:= partyid||' in (select /*+ parallel(xx)*/ xx.'||fk1||' from '||e1;
1502: p2:= ' xx ';
1503: if j1 is not null then
1504: p3:= ' where '||'('||p3||')';

Line 1534: l_sql := 'delete from hz_purge_gt temp where ';

1530: p3:=' ';
1531: end if;
1532:
1533: if(first = 'TRUE' AND cnt=1) then
1534: l_sql := 'delete from hz_purge_gt temp where ';
1535: if(cnt2=1) then
1536: p1:= ' exists (select ''Y'' from '||e2;
1537: else
1538: p1:= ' or exists (select ''Y'' from '||e2;

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

1542: p3 := ' and '||'('||p3||')';
1543: end if;
1544: p4:= ' and exists' ;
1545: elsif (first = 'TRUE' AND cnt>1) then
1546: l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1547: p1:= ' or exists (select ''Y'' from '||e2;
1548: p2:= ' xx where xx.'||fk2||' = '||partyid;
1549: if j2 is not null then
1550: p3 := ' and '||'('||p3||')';

Line 1554: l_sql := 'delete from hz_purge_gt temp where ';

1550: p3 := ' and '||'('||p3||')';
1551: end if;
1552: p4:= ' and exists' ;
1553: elsif(first = 'FALSE' AND cnt=1) then
1554: l_sql := 'delete from hz_purge_gt temp where ';
1555: if(cnt2=1) then
1556: p1:= partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;
1557: else
1558: p1:= ' or '||partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;

Line 1634: l_sql := 'delete from hz_purge_gt temp where ';

1630: p3:='';
1631: end if;
1632:
1633: if(first = 'TRUE' AND cnt=1) then
1634: l_sql := 'delete from hz_purge_gt temp where ';
1635: if(cnt3=1) then
1636: p1:= ' exists (select ''Y'' from '||e3;
1637: else
1638: p1:= ' or exists (select ''Y'' from '||e3;

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

1642: p3:=' and '||'('||p3||')';
1643: end if;
1644: p4:= ' and exists ';
1645: elsif (first = 'TRUE' AND cnt>1) then
1646: l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1647: p1:= ' or exists (select ''Y'' from '||e3;
1648: p2:= ' xx where xx.'||fk3||' = '||partyid ;
1649: if j3 is not null then
1650: p3:=' and '||'('||p3||')';

Line 1655: l_sql := 'delete from hz_purge_gt temp where ';

1651: end if;
1652: p4:= ' and exists ';
1653: elsif(first = 'FALSE' AND cnt=1) then
1654: if(cnt3=1) then
1655: l_sql := 'delete from hz_purge_gt temp where ';
1656: p1:= partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;
1657: else
1658: p1:= ' or '||partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;
1659: end if;

Line 1766: END HZ_PURGE;

1762: WHEN OTHERS THEN
1763: null;
1764: END;
1765:
1766: END HZ_PURGE;