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,
6 x_msg_count OUT NOCOPY NUMBER,
7 x_msg_data OUT NOCOPY VARCHAR2)
8 IS
9
10 CURSOR app_id IS select distinct(dict_application_id) from hz_merge_dictionary
11 where parent_entity_name='HZ_PARTIES'
12 and nvl(validate_purge_flag,'Y') <> 'N'; --5125968
13 --and entity_name in (select table_name from fnd_tables);
14
15 cursor x1(app_id number) is --4500011
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,
17 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
18 AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
19 from hz_merge_dictionary where parent_entity_name like 'HZ_%' and dict_application_id = app_id
20 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')
21 and nvl(validate_purge_flag,'Y') <> 'N'; --5125968
22
23 cursor x4(app_id number) is --4500011
24 select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
25 decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
26 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
27 AND directional_flag = ''F''', join_clause) join_clause,
28 parent_entity_name, fk_data_type
29 from hz_merge_dictionary where parent_entity_name like 'HZ_%' and dict_application_id = app_id
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):= '';
39 appid number(15);
40 e1 varchar2(50);
41 fk1 varchar2(50);
42 pk1 varchar2(50);
43 j1 varchar2(1000);
44 pe1 varchar2(50);
45 fk_data_typ1 varchar2(100);
46 s1 varchar2(32000);
47 s2 varchar2(31000);
48 id number :=1;
49 partyid varchar2(100);
50 column_indexed boolean := true;
51 s3 varchar2(3000);
52 cnt number :=0;
53 xxx varchar2(10);
54 app_name VARCHAR2(100);
55
56 BEGIN
57
58 SAVEPOINT generate_body;
59
60 -- initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean(p_init_msg_list) THEN
62 FND_MSG_PUB.initialize;
63 END IF;
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,
73 x_msg_data OUT NOCOPY VARCHAR2,
74 check_flag boolean, con_prg boolean, regid_proc boolean) IS');
75 HZ_GEN_PLSQL.add_line('appid number;');
76 HZ_GEN_PLSQL.add_line('sql_count number;');
77 HZ_GEN_PLSQL.add_line('total_parties number;');
78 HZ_GEN_PLSQL.add_line('parties_count1 number;');
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;');
88
89 -- initialize message list if p_init_msg_list is set to TRUE.
90 HZ_GEN_PLSQL.add_line('IF FND_API.to_Boolean(p_init_msg_list) THEN');
91 HZ_GEN_PLSQL.add_line('FND_MSG_PUB.initialize;');
92 HZ_GEN_PLSQL.add_line('END IF;');
93
94 -- initialize API return status to success.
95 HZ_GEN_PLSQL.add_line('x_return_status := FND_API.G_RET_STS_SUCCESS;');
96 HZ_GEN_PLSQL.add_line('delete from hz_application_trans_gt; ');
97 HZ_GEN_PLSQL.add_line('open repopulate;');
98 HZ_GEN_PLSQL.add_line('fetch repopulate into single_party;');
99 HZ_GEN_PLSQL.add_line('close repopulate;');
100 populate_fk_datatype;
101 -- open cursor to get each application id
102 OPEN app_id;
103 LOOP
104 FETCH app_id into appid;
105 exit when app_id%NOTFOUND;
106 cnt := 1;
107 if appid =222 then
108 app_name :=null;
109 app_name := get_app_name(appid);
110 if(app_name IS NOT NULL) then
111 -- open cursor to get the tca tables, app_id=222 where check is needed
112 OPEN x4(appid);
113 loop
114 FETCH x4 into e1,fk1,pk1,j1,pe1,fk_data_typ1;
115 exit when x4%NOTFOUND;
116 column_indexed := has_index(e1,fk1,app_name,j1);
117 if(column_indexed = true) then
118 delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1, 'TRUE', s2, cnt);
119 if(s2 is not null) then
120 s1 := s1||fnd_global.local_chr(10)||s2;
121 cnt := cnt+1;
122 end if;
123 end if;
124 end loop;
125 close x4;
126 end if;
127 else
128 app_name :=null;
129 app_name := get_app_name(appid);
130 if(app_name IS NOT NULL) then
131 OPEN x1(appid);
132 loop
133 FETCH x1 into e1,fk1,pk1,j1,pe1,fk_data_typ1;
134 exit when x1%NOTFOUND;
135 column_indexed := has_index(e1,fk1,app_name,j1);
136 if(column_indexed = true) then
137 delete_template(e1, fk1, pk1, j1, pe1, fk_data_typ1,'TRUE', s2,cnt);
138 if(s2 is not null) then
139 s1 := s1||fnd_global.local_chr(10)||s2;
140 cnt := cnt+1;
141 end if;
142 end if;
143 end loop;
144 close x1;
145 end if;
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||';');
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;
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;
170 LOOP
171 FETCH app_id into appid;
172 exit when app_id%NOTFOUND;
173 app_name:=null;
174 app_name := get_app_name(appid);
175 if(app_name IS NOT NULL) then
176 OPEN x1(appid);
177 loop
178 FETCH x1 into e1,fk1,pk1,j1,pe1,fk_data_typ1;
179 exit when x1%NOTFOUND;
180 column_indexed := has_index(e1,fk1,app_name,j1);
181 if(column_indexed = false) then
182 --dbms_output.put_line('non indexed entity='||e1||',column='||fk1||'parent='||pe1);
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 ');
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||';');
195 HZ_GEN_PLSQL.ADD_LINE('end if;');
196
197 end if;
198 end if;
199 end loop;
200 close x1;
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');
210 HZ_GEN_PLSQL.add_line('ROLLBACK to identify_candidates;');
211 HZ_GEN_PLSQL.add_line('x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;');
212 HZ_GEN_PLSQL.add_line('FND_MESSAGE.SET_NAME( ''AR'', ''HZ_API_OTHERS_EXCEP'' );');
213 HZ_GEN_PLSQL.add_line('FND_MESSAGE.SET_TOKEN( ''ERROR'' ,SQLERRM );');
214 HZ_GEN_PLSQL.add_line('FND_MSG_PUB.ADD;');
215
216 HZ_GEN_PLSQL.add_line('FND_MSG_PUB.Count_And_Get(
217 p_encoded => FND_API.G_FALSE,
218 p_count => x_msg_count,
219 p_data => x_msg_data );');
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.
229 FND_MSG_PUB.Count_And_Get(
230 p_encoded => FND_API.G_FALSE,
231 p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 EXCEPTION
235 WHEN FND_API.G_EXC_ERROR THEN
236 ROLLBACK ;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 FND_MSG_PUB.Count_And_Get(
239 p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count,
241 p_data => x_msg_data);
242 RAISE FND_API.G_EXC_ERROR;
243
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 ROLLBACK ;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 FND_MSG_PUB.Count_And_Get(
248 p_encoded => FND_API.G_FALSE,
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 WHEN OTHERS THEN
253 ROLLBACK;
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255
256 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
257 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
258 FND_MSG_PUB.ADD;
259
260 FND_MSG_PUB.Count_And_Get(
261 p_encoded => FND_API.G_FALSE,
262 p_count => x_msg_count,
263 p_data => x_msg_data );
264 RAISE FND_API.G_EXC_ERROR;
265
266 END;
267
268
269 -- Procedure called by the concurrent program
270
271 PROCEDURE IDENTIFY_PURGE_PARTIES(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, batchid varchar2, con_prg VARCHAR2, regid_proc VARCHAR2 DEFAULT 'F') IS
272 x_return_status varchar2(5);
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);
282 candpartyid number;
283 num_parties number;
284 p_init_msg_list VARCHAR2(10);
285 conc_prg boolean;
286 partyid number;
287 partyname VARCHAR2(360);
288 regid_flag boolean;
289 attrib_flag VARCHAR2(10);
290 app_id number;
291 p_id number;
292 pid number;
293 x_sysdate date :=sysdate;
294 i number := 0;
295 /*l_bool BOOLEAN;
296 l_status VARCHAR2(255);
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';
306
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
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
325 if con_prg is not null and con_prg='Y' then
326 conc_prg := true;
327 else
328 conc_prg := false;
329 end if;
330
331 log('NEWLINE',conc_prg);
332 log('--Program to identify the purge candidates',conc_prg);
333 --l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
334 open time_stmp;
335 fetch time_stmp into time_stamp;
336 close time_stmp;
337
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
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;
352 fetch b1 into where_clause,attrib_flag;
353 close b1;
354
355 if where_clause is not null then
356 insert_stmt := insert_stmt||' and '||where_clause;
357 end if;
358 execute immediate delete_stmt;
359 execute immediate insert_stmt;
360
361 /* Procedure to identify the purge candidates with no transactions */
362 if(attrib_flag IS NOT NULL) then
363 if(attrib_flag='Y') then
364 regid_flag := false;
365 else
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)
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
384 cp.primary_flag(+)='Y';
385
386 num_parties:=SQL%ROWCOUNT;*/
387
388 open purge_parties;
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',
398 sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
399 from hz_parties a , hz_contact_points cp where
400 a.party_id = pid and
401 cp.owner_table_id(+)= a.party_id and
402 cp.contact_point_type(+)='PHONE' and
403 cp.owner_table_name(+)='HZ_PARTIES' and
404 cp.primary_flag(+)='Y';
405 end loop;
406 close purge_parties;
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;
416 exit when appl_trans%NOTFOUND;
417 x_sysdate :=x_sysdate+0.00001;
418 --dbms_output.put_line('sysdate='||x_sysdate);
419 insert into hz_non_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,APPL_ID,
420 PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,CREATION_DATE,
421 LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
422 select to_number(batchid), p_id, app_id, substr(a.party_name,1,250), a.party_number,
423 null, null, null,
424 x_sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
425 from hz_parties a where
426 a.party_id = p_id;
427 end loop;
428 close appl_trans;
429 end if;
430 log('The following Parties have been identified as purge candidates',conc_prg);
431 log('***************************************************************',conc_prg);
432 open printparties;
433 loop
434 fetch printparties into partyid,partyname;
435 exit when printparties%NOTFOUND;
436 log(partyname||'(party_id ='||partyid||')',conc_prg);
437 end loop;
438 close printparties;
439
440 EXCEPTION
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;
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;
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
460 /*PROCEDURE check_single_party_trans
461 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2,
465 partyid number,
466 allow_purge OUT NOCOPY VARCHAR2) IS
467
468 party_count number;
469 phone_number varchar2(75);
470
471 cursor party is
472 select count(*) from hz_application_trans_gt;
473
474 begin
475 null;
476 /*
477 SAVEPOINT check_single_party_trans;
478
479 -- initialize message list if p_init_msg_list is set to TRUE.
480 IF FND_API.to_Boolean(p_init_msg_list) THEN
481 FND_MSG_PUB.initialize;
482 END IF;
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;
492 if party_count>0 then
493 allow_purge:='false';
494 else
495 allow_purge:='true';
496 end if;
497 close party;
498
499 -- standard call to get message count and if count is 1, get message info.
500 FND_MSG_PUB.Count_And_Get(
501 p_encoded => FND_API.G_FALSE,
502 p_count => x_msg_count,
503 p_data => x_msg_data);
504
505 EXCEPTION
506 WHEN FND_API.G_EXC_ERROR THEN
507 ROLLBACK TO check_single_party_trans;
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 FND_MSG_PUB.Count_And_Get(
510 p_encoded => FND_API.G_FALSE,
511 p_count => x_msg_count,
512 p_data => x_msg_data);
513 RAISE FND_API.G_EXC_ERROR;
514
515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
516 ROLLBACK TO check_single_party_trans;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 FND_MSG_PUB.Count_And_Get(
519 p_encoded => FND_API.G_FALSE,
520 p_count => x_msg_count,
521 p_data => x_msg_data);
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523
524 WHEN OTHERS THEN
525 ROLLBACK to check_single_party_trans;
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527
528 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
529 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
530 FND_MSG_PUB.ADD;
531
532 FND_MSG_PUB.Count_And_Get(
533 p_encoded => FND_API.G_FALSE,
534 p_count => x_msg_count,
535 p_data => x_msg_data );
536 RAISE FND_API.G_EXC_ERROR;
537
538 end;*/
539
540 /* Procedure to purge parties. This is called by the concurrent program */
541
542 PROCEDURE PURGE_PARTIES(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, batchid number, con_prg VARCHAR2) IS
543
544 candidate_id number;
545 conc_prg boolean;
546 partyid number;
547 partyname VARCHAR2(360);
548 l_bool BOOLEAN;
549 l_status VARCHAR2(255);
550 l_schema VARCHAR2(255);
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
560 if con_prg is not null and con_prg='Y' then
561 conc_prg := true;
562 else
563 conc_prg := false;
564 end if;
565
566 log('NEWLINE',conc_prg);
567 log('The following Parties will be purged from the TCA tables',conc_prg);
568 log('***************************************************************',conc_prg);
569 open printparties(batchid);
570 loop
571 fetch printparties into partyid,partyname;
572 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED','BO_EVENTS_ENABLED')) THEN
573
574 HZ_BES_BO_UTIL_PKG.del_obj_hierarchy(partyid);
575
576 END IF;
577 exit when printparties%NOTFOUND;
578 log(partyname||'(ID= '||partyid||')',conc_prg);
579 end loop;
580 close printparties;
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');
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
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
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;
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 (
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
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;
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
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
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
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
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;
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
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
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
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
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');
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);
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'));
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);
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');
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);
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);
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'')
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
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;
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
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
778 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
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'
788 from wsh_location_owners wlo1
789 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
790
791 -- delete wsh again to catch the ones are not deletled from first delete and not updated from second update
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
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)
808 and not exists (Select 'x'
809 from zx_exemptions ex
810 where ptp.party_tax_profile_id = ex.party_tax_profile_id)
811 and not exists (Select 'x'
812 from ZX_REPORT_CODES_ASSOC assoc
813 where assoc.entity_code = 'ZX_PARTY_TAX_PROFILE'
814 and assoc.ENTITY_ID = ptp.party_tax_profile_id)
815 and not exists (Select 'x'
816 from hz_code_assignments HCA
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
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
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
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'))
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;
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
867 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_parties_t1');
868 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_party_sites_t1');
869 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_contact_t1');
870 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_cpt_t1');
871 exception
872 when others then
873 null;
874 end;
875
876 hz_common_pub.enable_cont_source_security;
877 EXCEPTION
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;
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;
892 update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
893
894 END;
895
896 /* Purge Single Party */
897
898 PROCEDURE PURGE_PARTY
899 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
900 x_return_status OUT NOCOPY VARCHAR2,
901 x_msg_count OUT NOCOPY NUMBER,
902 x_msg_data OUT NOCOPY VARCHAR2,
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;
912
913 -- initialize message list if p_init_msg_list is set to TRUE.
914 IF FND_API.to_Boolean(p_init_msg_list) THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917
918 -- initialize API return status to success.
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',
928 sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
929 from hz_parties a , hz_contact_points cp where a.party_id = p_party_id and
930 cp.owner_table_id(+)=a.party_id and cp.contact_point_type(+)='PHONE' and cp.primary_flag(+)='Y' and
931 cp.owner_table_name(+)='HZ_PARTIES';
932
933 hz_common_pub.disable_cont_source_security;
934
935 DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID = p_party_id;
936
937 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
938 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
939 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
940 and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
941
942 begin
943 execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
944 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
945 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using p_party_id;
946 exception
947 when others then
948 null;
949 end;
950
951 DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
952 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
953
954 DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
955 ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
956 SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id));
957
958 begin
959 execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
960 ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
961 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1))' using p_party_id;
962 exception
963 when others then
964 null;
965 end;
966
967 DELETE from HZ_ORG_CONTACTS where PARTY_SITE_ID in
968 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id);
969
970 DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
971 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id);
972
973 execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
974 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using p_party_id;
975
976 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
977 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using p_party_id;
978
979 begin
980 execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
981 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)' using p_party_id;
982 exception
983 when others then
984 null;
985 end;
986
987 DELETE from HZ_PARTY_SITES where PARTY_ID = p_party_id;
988
989 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
990 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')
991 and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
992
993 begin
994 execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
995 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')' using p_party_id;
996 exception
997 when others then
998 null;
999 end;
1000
1001 DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID = p_party_id and OWNER_TABLE_NAME='HZ_PARTIES';
1002
1003 DELETE from HZ_PERSON_PROFILES where PARTY_ID = p_party_id;
1004
1005 DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID = p_party_id;
1006
1007 DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID = p_party_id;
1008
1009 DELETE from HZ_CERTIFICATIONS where PARTY_ID = p_party_id;
1010
1011 DELETE from HZ_CREDIT_RATINGS where PARTY_ID = p_party_id;
1012
1013 DELETE from HZ_SECURITY_ISSUED where PARTY_ID = p_party_id;
1014
1015 DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
1016 ( select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID = p_party_id);
1017
1018 DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID = p_party_id;
1019
1020 DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID = p_party_id;
1021
1022 DELETE from HZ_PERSON_INTEREST where PARTY_ID = p_party_id;
1023
1024 DELETE from HZ_CITIZENSHIP where PARTY_ID = p_party_id;
1025
1026 DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
1027 (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID = p_party_id);
1028
1029 DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID = p_party_id;
1030
1031 DELETE from HZ_PERSON_LANGUAGE where PARTY_ID = p_party_id;
1032
1033 DELETE from HZ_EDUCATION where PARTY_ID = p_party_id;
1034
1035 DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID = p_party_id;
1036
1037 execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES''' using p_party_id;
1038
1039 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID = :1 and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using p_party_id;
1040
1041 execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id = :1' using p_party_id;
1042
1043 begin
1044 execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID = :1' using p_party_id;
1045 exception
1046 when others then
1047 null;
1048 end;
1049 DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID = p_party_id;
1050
1051 delete from wsh_location_owners wlo
1052 where wlo.owner_party_id = p_party_id
1053 and exists (
1054 select 'x'
1055 from wsh_location_owners wlo1
1056 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
1057
1058 update wsh_location_owners wlo
1059 set wlo.owner_party_id = -1
1060 where wlo.owner_party_id = p_party_id
1061 and not exists (
1062 select 'x'
1063 from wsh_location_owners wlo1
1064 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
1065
1066 Delete from zx_party_tax_profile PTP
1067 where ptp.party_type_code = 'THIRD_PARTY'
1068 and ptp.party_id = p_party_id
1069 and not exists (Select 'x'
1070 from zx_registrations reg
1071 where ptp.party_tax_profile_id = reg.party_tax_profile_id)
1072 and not exists (Select 'x'
1073 from zx_exemptions ex
1074 where ptp.party_tax_profile_id = ex.party_tax_profile_id)
1075 and not exists (Select 'x'
1076 from ZX_REPORT_CODES_ASSOC assoc
1077 where assoc.entity_code = 'ZX_PARTY_TAX_PROFILE'
1078 and assoc.ENTITY_ID = ptp.party_tax_profile_id)
1079 and not exists (Select 'x'
1080 from hz_code_assignments HCA
1081 where HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
1082 AND HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);
1083
1084 DELETE from HZ_PARTIES where party_id = p_party_id;
1085
1086 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED','BO_EVENTS_ENABLED')) THEN
1087
1088 HZ_BES_BO_UTIL_PKG.del_obj_hierarchy(p_party_id);
1089
1090 END IF;
1091
1092 /* if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null */
1093 execute immediate 'update hz_relationships set party_id=null where party_id=:1 and party_id in
1094 (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;
1095
1096 delete from hz_parties where party_id in (select party_id from hz_relationships
1097 where (subject_id = p_party_id or object_id = p_party_id))
1098 and status = 'M';
1099
1100 -- bug 4947069
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;
1110 -- standard call to get message count and if count is 1, get message info.
1111 FND_MSG_PUB.Count_And_Get(
1112 p_encoded => FND_API.G_FALSE,
1113 p_count => x_msg_count,
1114 p_data => x_msg_data);
1115
1116
1117 EXCEPTION
1118 WHEN FND_API.G_EXC_ERROR THEN
1119 ROLLBACK TO PURGE_PARTY;
1120 x_return_status := FND_API.G_RET_STS_ERROR;
1121 FND_MSG_PUB.Count_And_Get(
1122 p_encoded => FND_API.G_FALSE,
1123 p_count => x_msg_count,
1124 p_data => x_msg_data);
1125 RAISE FND_API.G_EXC_ERROR;
1126
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 ROLLBACK TO PURGE_PARTY;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130 FND_MSG_PUB.Count_And_Get(
1131 p_encoded => FND_API.G_FALSE,
1132 p_count => x_msg_count,
1133 p_data => x_msg_data);
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135
1136 WHEN OTHERS THEN
1137 ROLLBACK to PURGE_PARTY;
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139
1140 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
1141 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1142 FND_MSG_PUB.ADD;
1143
1144 FND_MSG_PUB.Count_And_Get(
1145 p_encoded => FND_API.G_FALSE,
1146 p_count => x_msg_count,
1147 p_data => x_msg_data );
1148 RAISE FND_API.G_EXC_ERROR;
1149
1150 END purge_party;
1151
1152 PROCEDURE post_app_logic(appid NUMBER, single_party VARCHAR2, check_flag boolean) IS
1153 parties_count1 NUMBER;
1154 appid_cnt NUMBER;
1155
1156 cursor appid_count(appid NUMBER) IS
1157 select count(*) from hz_application_trans_gt where app_id=appid;
1158
1159 BEGIN
1160
1161 OPEN appid_count(appid);
1162 FETCH appid_count into appid_cnt;
1163 CLOSE appid_count;
1164 If(appid_cnt=0) then
1165 parties_count1:=SQL%ROWCOUNT;
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
1175 END post_app_logic;
1176
1177 FUNCTION logerror RETURN VARCHAR2 IS
1178 l_msg_data VARCHAR2(2000);
1179 BEGIN
1180 FND_MSG_PUB.Reset;
1181
1182 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1183 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1184 END LOOP;
1185 log(l_msg_data,true);
1186 RETURN l_msg_data;
1187 END logerror;
1188
1189 PROCEDURE log(
1190 message IN VARCHAR2,
1191 con_prg IN boolean,
1192 newline IN BOOLEAN DEFAULT TRUE) IS
1193 BEGIN
1194 IF message = 'NEWLINE' THEN
1195 if con_prg is not null and con_prg=true then
1196 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1197 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1198 end if;
1199 ELSE
1200 FND_FILE.put_line(fnd_file.log,message);
1201 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, message);
1202 END IF;
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 NULL;
1206 END log;
1207
1208 FUNCTION get_col_type(
1209 p_table VARCHAR2,
1210 p_column VARCHAR2,
1211 p_app_name VARCHAR2)
1212 RETURN VARCHAR2 IS
1213
1214 CURSOR data_type(schema1 VARCHAR2) IS
1215 SELECT DATA_TYPE FROM sys.all_tab_columns
1216 WHERE table_name = p_table
1217 AND COLUMN_NAME = p_column
1218 AND owner = schema1;
1219
1220 l_data_type VARCHAR2(106);
1221 ret_data_type VARCHAR2(106);
1222 l_bool BOOLEAN;
1223 l_status VARCHAR2(255);
1224 l_schema VARCHAR2(255);
1225 l_tmp VARCHAR2(2000);
1226
1227 BEGIN
1228 /*l_bool := fnd_installation.GET_APP_INFO(p_app_name,l_status,l_tmp,l_schema);
1229 OPEN data_type(l_schema);
1230 FETCH data_type INTO l_data_type;
1231 CLOSE data_type;
1232 if (l_data_type is null) then
1233 ret_data_type := 'NONE';
1234 else
1235 ret_data_type := l_data_type;
1236 end if;
1237
1238 RETURN ret_data_type;
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1242 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1243 FND_MSG_PUB.ADD;
1244 RAISE FND_API.G_EXC_ERROR;*/
1245 null;
1246 END get_col_type;
1247
1248 FUNCTION has_context(proc VARCHAR2) RETURN BOOLEAN IS
1249 l_entity VARCHAR2(255);
1250 l_procedure VARCHAR2(255);
1251 l_attribute VARCHAR2(255);
1252 c NUMBER;
1253 n NUMBER;
1254 l_custom BOOLEAN;
1255 BEGIN
1256 c := dbms_sql.open_cursor;
1257 dbms_sql.parse(c,proc,2);
1258 dbms_sql.close_cursor(c);
1259 RETURN TRUE;
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 dbms_sql.close_cursor(c);
1263 RETURN FALSE;
1264 END;
1265
1266 PROCEDURE populate_fk_datatype IS
1267 cursor c_dict_no_fktype is
1268 select entity_name, fk_column_name, merge_dict_id, dict_application_id
1269 from hz_merge_dictionary
1270 where fk_data_type is null;
1271
1272 l_sub_entity_name VARCHAR2(150);
1273 l_sub_fk_column_name VARCHAR2(150);
1274 l_merge_dict_id NUMBER;
1275 l_app_id NUMBER;
1276 l_data_type VARCHAR2(100);
1277 l_app_name VARCHAR2(100);
1278
1279 BEGIN
1280 open c_dict_no_fktype;
1281 loop
1282 fetch c_dict_no_fktype into l_sub_entity_name, l_sub_fk_column_name,
1283 l_merge_dict_id, l_app_id;
1284 EXIT WHEN c_dict_no_fktype%NOTFOUND;
1285 l_app_name := get_app_name(l_app_id);
1286 l_data_type:=hz_party_merge.get_col_type(l_sub_entity_name,l_sub_fk_column_name,l_app_name);
1287
1288 update hz_merge_dictionary
1289 set fk_data_type = l_data_type
1290 where merge_dict_id = l_merge_dict_id;
1291
1292 end loop;
1293 close c_dict_no_fktype;
1294
1295 END;
1296
1297 FUNCTION get_app_name(appid NUMBER) RETURN VARCHAR2 IS
1298 appname VARCHAR2(100);
1299 cursor app_name(app_id NUMBER) IS
1300 Select application_short_name from fnd_application where application_id=app_id;
1301 BEGIN
1302 open app_name(appid);
1303 fetch app_name into appname;
1304 close app_name;
1305 return appname;
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 null;
1309 END;
1310
1311
1312 FUNCTION has_index(entity_name VARCHAR2, column_name VARCHAR2, app_name VARCHAR2, join_clause VARCHAR2) RETURN BOOLEAN IS
1313 l_bool BOOLEAN;
1314 l_status VARCHAR2(255);
1315 l_schema VARCHAR2(255);
1316 l_tmp VARCHAR2(2000);
1317 col_pos NUMBER;
1318 col_name VARCHAR2(100);
1319 entity_occur NUMBER;
1320 check_flag VARCHAR2(10);
1321 col_position NUMBER;
1322 indexname VARCHAR2(100);
1323 upper_join_clause VARCHAR2(2000);
1324
1325 cursor column_position(ent_name varchar2, ent_col_name varchar2, schema1 varchar2) is
1326 select min(column_position) from dba_ind_columns where table_name = ent_name
1327 and column_name = ent_col_name
1328 and index_owner = schema1 and table_owner = schema1;
1329
1330 cursor col_indexes(ent_name varchar2, colmn_name varchar2, schema1 varchar2) is
1331 select index_name, column_position from dba_ind_columns
1332 where table_name = ent_name and column_name = colmn_name
1333 and index_owner = schema1 and table_owner = schema1;
1334
1335 cursor preceeding_columns(ent_name varchar2, ind_name varchar2, col_position NUMBER, schema1 varchar2) is
1336 select column_name from dba_ind_columns where table_name = ent_name
1337 and index_name = ind_name
1338 and column_position<col_position
1339 and index_owner = schema1 and table_owner = schema1;
1340
1341 cursor indexed_views(ent_name varchar2, colmn_name varchar2) is
1342 select 'Y' from dual where
1343 (ent_name,colmn_name)
1344 in (('AS_ACCESSES_ALL', 'CUSTOMER_ID'),('AS_ACCESSES_ALL', 'ADDRESS_ID'),('AS_ACCESSES_ALL', 'PARTNER_CUSTOMER_ID'),
1345 ('AS_ACCESSES_ALL', 'PARTNER_CONT_PARTY_ID'),('AS_ACCESSES_ALL', 'PARTNER_ADDRESS_ID'),('ASG_PARTY_ACC_V', 'PARTY_ID'),
1346 ('OKE_K_FUNDING_SOURCES_PM_HV', 'K_PARTY_ID'),('IGW_PROP_PERSONS_TCA_V', 'PERSON_PARTY_ID'),
1347 ('MIS_HZ_MERGE_VETO_PARTIES', 'PARTY_ID'),('MIS_HZ_MERGE_VETO_PARTY_SITES', 'PARTY_SITE_ID'),('JTF_PERZ_QUERY_PARAM','PARAMETER_VALUE'));
1348
1349 cursor string_column(join_clause varchar2, col_name varchar2) is
1350 select instr(join_clause,col_name) from dual;
1351 i NUMBER :=0;
1352 cols NUMBER:=0;
1353 isView VARCHAR2(1) := null;
1354 BEGIN
1355 open indexed_views(entity_name, column_name);
1356 fetch indexed_views into isView;
1357 close indexed_views;
1358 if(isView='Y') then
1359 check_flag := 'Y';
1360 else
1361 l_bool := fnd_installation.GET_APP_INFO(app_name,l_status,l_tmp,l_schema);
1362 open column_position(entity_name, column_name, l_schema);
1363 fetch column_position into col_pos;
1364 close column_position;
1365
1366 if (col_pos is not null AND col_pos=1) then
1367 check_flag :='Y';
1368 --dbms_output.put_line('col position is 1');
1369 elsif(col_pos is not null AND col_pos>1) then
1370
1371 open col_indexes(entity_name, column_name, l_schema);
1372 loop
1373 fetch col_indexes into indexname, col_position;
1374 --dbms_output.put_line('index name ='||indexname||' and col_position ='||col_position);
1375 exit when col_indexes%NOTFOUND;
1376 if join_clause is not null then
1377 check_flag := 'Y';
1378 open preceeding_columns(entity_name, indexname, col_position, l_schema);
1379 loop
1380 fetch preceeding_columns into col_name;
1381 exit when preceeding_columns%NOTFOUND;
1382 --cols := preceeding_columns%ROWCOUNT;
1383 upper_join_clause := upper(join_clause);
1384 --dbms_output.put_line('nvl(instrb(upper_join_clause,col_name),0)'||nvl(instrb(upper_join_clause,col_name),0));
1385 IF nvl(instrb(upper_join_clause,col_name),0)<=0 then
1386 check_flag := 'N';
1387 exit;
1388 end if;
1389 end loop;
1390 close preceeding_columns;
1391 else
1392 check_flag := 'N';
1393 end if;
1394 if check_flag = 'Y' then
1395 --dbms_output.put_line('all columns exist in join clause=');
1396 exit;
1397 end if;
1398 end loop;
1399 close col_indexes;
1400 else
1401 check_flag:='N';
1402 --dbms_output.put_line('entity_occur=null');
1403 end if;
1404 end if;
1405
1406 if (check_flag='Y') then
1407 RETURN TRUE;
1408 else
1409 RETURN FALSE;
1410 end if;
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 RETURN FALSE;
1414 END;
1415
1416
1417 PROCEDURE delete_template
1418 (e1 VARCHAR2, fk1 VARCHAR2,pk1 VARCHAR2,j1 VARCHAR2, pe1 VARCHAR2, fk_data_typ1 VARCHAR2,
1419 first VARCHAR2, concat_string OUT NOCOPY VARCHAR2, cnt NUMBER) IS
1420
1421 e2 varchar2(50);
1422 fk2 varchar2(50);
1423 pk2 varchar2(50);
1424 j2 varchar2(1000);
1425 pe2 varchar2(50);
1426 e3 varchar2(50);
1427 fk3 varchar2(50);
1428 pk3 varchar2(50);
1429 j3 varchar2(1000);
1430 pe3 varchar2(50);
1431 fk_data_typ2 varchar2(100);
1432 fk_data_typ3 varchar2(100);
1433 s1 varchar2(31000);
1434 s2 varchar2(300);
1435 p1 varchar2(1000);
1436 p2 varchar2(500);
1437 p3 varchar2(500);
1438 p4 varchar2(500);
1439 p5 varchar2(500);
1440 p6 varchar2(500);
1441 p7 varchar2(500);
1442 p8 varchar2(500);
1443 p9 varchar2(500);
1444 p10 varchar2(500);
1445 p11 varchar2(500);
1446 p12 varchar2(10);
1447 fkcolumn_type varchar2(50);
1448 pkcolumn_type varchar2(50);
1449 column_type varchar2(50);
1450 partyid varchar2(100);
1451 valid_stmt boolean := true;
1452 l_sql VARCHAR2(32000);
1453 cnt2 NUMBER := 0;
1454 cnt3 NUMBER := 0;
1455
1456 cursor x2(parent varchar2) is --4500011
1457 select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1458 decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1459 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1460 AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
1461 from hz_merge_dictionary where entity_name = parent;
1462
1463 cursor x3(parent2 varchar2) is --4500011
1464 select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1465 decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1466 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1467 AND directional_flag = ''F''', join_clause) join_clause,
1468 parent_entity_name,fk_data_type
1469 from hz_merge_dictionary where entity_name = parent2;
1470
1471 BEGIN
1472 if pe1='HZ_PARTIES' then
1473 partyid :=' temp.party_id ';
1474 if (fk_data_typ1<>'NUMBER' AND fk_data_typ1 IS NOT NULL) then
1475 partyid := ' to_char(temp.party_id) ';
1476 end if;
1477
1478 if j1 is not null then
1479 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1480 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1481 else
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||')';
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||')';
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||')';
1505 end if;
1506 end if;
1507
1508
1509 p4:= ')';
1510
1511 s1:= s1||fnd_global.local_chr(10)||p1||
1512 p2||fnd_global.local_chr(10)||p3||p4;
1513 else
1514 -- open cursor to get the second level tables, which have/not have HZ_PARTIES as parent_entity
1515 open x2(pe1);
1516 loop
1517 fetch x2 into e2, fk2, pk2, j2, pe2,fk_data_typ2;
1518 exit when x2%NOTFOUND;
1519 cnt2:=cnt2+1;
1520 if pe2 = 'HZ_PARTIES' then
1521 partyid :=' temp.party_id ';
1522 if (fk_data_typ2<>'NUMBER' AND fk_data_typ2 IS NOT NULL) then
1523 partyid := ' to_char(temp.party_id) ';
1524 end if;
1525
1526 if j2 is not null then
1527 --select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1528 select decode(instr(j2,'group'),0,j2,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1529 else
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;
1539 end if;
1540 p2:= ' xx where xx.'||fk2||' = '||partyid;
1541 if j2 is not null then
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||')';
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;
1559 end if;
1560 p2:= ' xx ';
1561 p4:= ' xx.'||pk2||' ';
1562 if(fk_data_typ1<>fk_data_typ2) then
1563 if fk_data_typ1='VARCHAR2' then
1564 p4:= ' to_char(xx.'||pk2||')';
1565 end if;
1566 end if;
1567 if j2 is not null then
1568 p3 := ' where '||'('||p3||')';
1569 p4 := ' and '||p4;
1570 else
1571 p4 := ' where '||p4;
1572 end if;
1573 end if;
1574
1575 if j1 is not null then
1576 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p7 from dual;
1577 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p7 from dual;
1578 else
1579 p7:='';
1580 end if;
1581
1582 if(first='FALSE') then
1583 p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk1||' from '||e1;
1584 p6:= ' yy ';
1585 if j1 is not null then
1586 p7:= ' where '||p7;
1587 end if;
1588 else
1589 p5 := '(select ''Y'' from '||e1;
1590 p6:= ' yy where yy.'||fk1||'=xx.'||pk2||'';
1591 if(fk_data_typ1<>fk_data_typ2) then
1592 if fk_data_typ1='VARCHAR2' then
1593 p6:= ' yy where yy.'||fk1||'=to_char(xx.'||pk2||')';
1594 end if;
1595 end if;
1596 if j1 is not null then
1597 p7:= ' and '||p7;
1598 end if;
1599 end if;
1600
1601 p8:= '))';
1602 s1:=s1||fnd_global.local_chr(10)||p1||fnd_global.local_chr(10)||p2||fnd_global.local_chr(10)||p3||fnd_global.local_chr(10)||p4||
1603 fnd_global.local_chr(10)||p5||fnd_global.local_chr(10)||p6||fnd_global.local_chr(10)||p7||p8;
1604
1605 else
1606
1607 if(cnt2>1) then
1608 cnt3:=1;
1609 else
1610 cnt3:=0;
1611 end if;
1612
1613 -- open cursor to get the third level tables, which have HZ_PARTIES as parent_entity
1614 open x3(pe2);
1615 loop
1616 fetch x3 into e3, fk3, pk3, j3, pe3,fk_data_typ3;
1617 exit when x3%NOTFOUND;
1618 cnt3:=cnt3+1;
1619 if pe3 = 'HZ_PARTIES' then
1620 partyid :=' temp.party_id ';
1621
1622 if (fk_data_typ3<>'NUMBER' AND fk_data_typ3 IS NOT NULL) then
1623 partyid := ' to_char(temp.party_id) ';
1624 end if;
1625
1626 if j3 is not null then
1627 --select decode(instr(j3,'group'),0,' and '||j3,' and '||substr(j3,1,instr(j3,'group')-1)) into p3 from dual;
1628 select decode(instr(j3,'group'),0,j3,substr(j3,1,instr(j3,'group')-1)) into p3 from dual;
1629 else
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;
1639 end if;
1640 p2:= ' xx where xx.'||fk3||' = '||partyid ;
1641 if j3 is not null then
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||')';
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;
1660 p2:= ' xx ' ;
1661 if(fk_data_typ2<>fk_data_typ3) then
1662 if fk_data_typ3='VARCHAR2' then
1663 p4:= ' to_char(xx.'||pk3||')';
1664 end if;
1665 end if;
1666 if j3 is not null then
1667 p3:=' where '||'('||p3||')';
1668 p4:= ' and '||p4||' ';
1669 else
1670 p4:= ' where '||p4||' ';
1671 end if;
1672 end if;
1673
1674
1675 if j2 is not null then
1676 --select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j2,1,instr(j2,'group')-1)) into p7 from dual;
1677 select decode(instr(j2,'group'),0,j2,substr(j2,1,instr(j2,'group')-1)) into p7 from dual;
1678 else
1679 p7:='';
1680 end if;
1681
1682 if(first='FALSE') then
1683 p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk2||' from '||e2;
1684 p6:= ' yy ';
1685 if(fk_data_typ1<>fk_data_typ2) then
1686 if fk_data_typ1='VARCHAR2' then
1687 p8:= ' and to_char(yy.'||pk2||')';
1688 end if;
1689 end if;
1690 if j2 is not null then
1691 p7:= ' where '||p7;
1692 p8:= ' and '||p8||'';
1693 else
1694 p8:= ' where '||p8||'';
1695 end if;
1696 else
1697 p5:= ' (select ''Y'' from '||e2;
1698 p6:= ' yy where yy.'||fk2||'=xx.'||pk3||' ';
1699 if(fk_data_typ2<>fk_data_typ3) then
1700 if fk_data_typ3='VARCHAR2' then
1701 p6:= ' yy where yy.'||fk2||'=to_char(xx.'||pk3||')';
1702 end if;
1703 end if;
1704 if j2 is not null then
1705 p7:= ' and '||p7;
1706 end if;
1707 p8:= ' and exists ';
1708 end if;
1709
1710 if j1 is not null then
1711 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p11 from dual;
1712 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p11 from dual;
1713 else
1714 p11:=' ';
1715 end if;
1716
1717 if (first='FALSE') then
1718 p9:= ' in (select /*+ parallel(zz)*/ yy.'||pk2||' from '||e1;
1719 p10:= ' zz ';
1720 if j1 is not null then
1721 p11:= ' where '||p11;
1722 end if;
1723 else
1724 p9:= ' (select ''Y'' from '||e1;
1725 p10:= ' zz where zz.'||fk1||' = yy.'||pk2;
1726 if(fk_data_typ1<>fk_data_typ2) then
1727 if fk_data_typ1='VARCHAR2' then
1728 p10:= ' zz where zz.'||fk1||'=to_char(yy.'||pk2||')';
1729 end if;
1730 end if;
1731 if j1 is not null then
1732 p11:= ' and '||p11;
1733 end if;
1734 end if;
1735
1736 p12:= ')))';
1737 s1:=s1||fnd_global.local_chr(10)||p1||fnd_global.local_chr(10)||p2||fnd_global.local_chr(10)||p3||fnd_global.local_chr(10)||p4||fnd_global.local_chr(10)||p5||
1738 fnd_global.local_chr(10)||p6||fnd_global.local_chr(10)||p7||fnd_global.local_chr(10)||p8||fnd_global.local_chr(10)||p9||p10||
1739 p11||p12;
1740 end if;
1741
1742 end loop;
1743 close x3;
1744 end if;
1745
1746 end loop;
1747 close x2;
1748
1749 end if;
1750 l_sql := l_sql||fnd_global.local_chr(10)||s1;
1751 valid_stmt:= has_context(l_sql);
1752 --valid_stmt:=true;
1753 if (valid_stmt=true) then
1754 --dbms_output.put_line('cnt='||cnt||',e1='||e1);
1755 concat_string := s1;
1756 else
1757 concat_string := null;
1758 --dbms_output.put_line('notvalid cnt='||l_sql);
1759 end if;
1760
1761 EXCEPTION
1762 WHEN OTHERS THEN
1763 null;
1764 END;
1765
1766 END HZ_PURGE;
[Home] [Help]