1 PACKAGE BODY HZ_PURGE AS
2 /* $Header: ARHPURGB.pls 120.47 2006/06/28 22:50:46 awu noship $ */
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<>''PARTY_RELATIONSHIP'' ';
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 -- bug 4947069
832
833 delete from hz_relationships 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 --update the status of purged parties in table 'HZ_PURGE_CANDIDATES to 'PURGED'
844 update hz_purge_candidates set status='PURGED' where batch_id=batchid and status='IDENTIFIED';
845 update hz_purge_candidates set status='PURGED' where candidate_party_id in (
846 select candidate_party_id from hz_purge_candidates where batch_id=batchid and status='PURGED')
847 and batch_id<>batchid;
848
849 /* update the status of purged parties in table 'HZ_PURGE_BATCHES to 'PURGE_COMPLETED' */
850 update hz_purge_batches set status='PURGE_COMPLETE',purge_date=sysdate where batch_id=batchid;
851
852 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
853
854 begin
855 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_parties_t1');
856 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_party_sites_t1');
857 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_contact_t1');
858 ad_ctx_ddl.sync_index(l_schema||'.hz_stage_cpt_t1');
859 exception
860 when others then
861 null;
862 end;
863
864 hz_common_pub.enable_cont_source_security;
865 EXCEPTION
866 WHEN FND_API.G_EXC_ERROR THEN
867 retcode := 2;
868 errbuf := errbuf || logerror||SQLERRM;
869 FND_FILE.close;
870 update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 retcode := 2;
873 errbuf := errbuf || logerror||SQLERRM;
874 FND_FILE.close;
875 update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
876 WHEN OTHERS THEN
877 retcode := 2;
878 errbuf := errbuf || logerror||SQLERRM;
879 FND_FILE.close;
880 update hz_purge_batches set status='PURGE_ERROR' where batch_id=batchid;
881
882 END;
883
884 /* Purge Single Party */
885
886 PROCEDURE PURGE_PARTY
887 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2,
891 p_party_id NUMBER) IS
892
893 insertrows number;
894 cursor existing_id(p_id number) is
895 select count(*) from hz_purge_candidates where candidate_party_id=p_id and status<>'PURGED';
896
897 BEGIN
898
899 SAVEPOINT PURGE_PARTY;
900
901 -- initialize message list if p_init_msg_list is set to TRUE.
902 IF FND_API.to_Boolean(p_init_msg_list) THEN
903 FND_MSG_PUB.initialize;
904 END IF;
905
906 -- initialize API return status to success.
907 x_return_status := FND_API.G_RET_STS_SUCCESS;
908 ---please enter the directory as the third parameter to which the file needs to be copied.
909 --fnd_file.put_names('delparty.log',null,'/sqlcom/outbound');
910
911 insert into hz_purge_candidates(BATCH_ID,CANDIDATE_PARTY_ID,PARTY_NAME,PARTY_NUMBER,ADDRESSES,PHONE_NUMBERS,COUNTRY,STATUS,CREATION_DATE,
912 LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY)
913 select to_number('-1'), a.party_id, substr(a.party_name,1,250), a.party_number,
914 a.address1||','||a.city||','||a.state||','||a.country||' '||a.postal_code,
915 cp.PHONE_AREA_CODE||'-'||cp.PHONE_COUNTRY_CODE||'-'||cp.PHONE_NUMBER, a.country, 'IDENTIFIED',
916 sysdate, fnd_global.login_id, sysdate, fnd_global.user_id, fnd_global.user_id
917 from hz_parties a , hz_contact_points cp where a.party_id = p_party_id and
918 cp.owner_table_id(+)=a.party_id and cp.contact_point_type(+)='PHONE' and cp.primary_flag(+)='Y' and
919 cp.owner_table_name(+)='HZ_PARTIES';
920
921 hz_common_pub.disable_cont_source_security;
922
923 DELETE from HZ_ORGANIZATION_PROFILES where PARTY_ID = p_party_id;
924
925 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
926 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
927 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')
928 and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
929
930 begin
931 execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
932 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID in
933 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES'')' using p_party_id;
934 exception
935 when others then
936 null;
937 end;
938
939 DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID in
940 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id) and OWNER_TABLE_NAME='HZ_PARTY_SITES';
941
942 DELETE from HZ_ORG_CONTACT_ROLES where ORG_CONTACT_ID in
943 ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in (
944 SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id));
945
946 begin
947 execute immediate 'DELETE from HZ_STAGED_CONTACTS where ORG_CONTACT_ID in
948 ( select ORG_CONTACT_ID FROM HZ_ORG_CONTACTS WHERE PARTY_SITE_ID in
949 (SELECT PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1))' using p_party_id;
950 exception
951 when others then
952 null;
953 end;
954
955 DELETE 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 DELETE from HZ_PARTY_SITE_USES where PARTY_SITE_ID in
959 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = p_party_id);
960
961 execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID in
962 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and OWNER_TABLE_NAME=''HZ_PARTY_SITES''' using p_party_id;
963
964 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
965 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1) and CONTACT_LEVEL_TABLE=''HZ_PARTY_SITES''' using p_party_id;
966
967 begin
968 execute immediate 'DELETE from HZ_STAGED_PARTY_SITES where PARTY_SITE_ID in
969 ( select PARTY_SITE_ID FROM HZ_PARTY_SITES WHERE PARTY_ID = :1)' using p_party_id;
970 exception
971 when others then
972 null;
973 end;
974
975 DELETE from HZ_PARTY_SITES where PARTY_ID = p_party_id;
976
977 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID in
978 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')
979 and CONTACT_LEVEL_TABLE=''HZ_CONTACT_POINTS''' using p_party_id;
980
981 begin
982 execute immediate 'DELETE from HZ_STAGED_CONTACT_POINTS where CONTACT_POINT_ID in
983 ( select CONTACT_POINT_ID FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES'')' using p_party_id;
984 exception
985 when others then
986 null;
987 end;
988
989 DELETE from HZ_CONTACT_POINTS where OWNER_TABLE_ID = p_party_id and OWNER_TABLE_NAME='HZ_PARTIES';
990
991 DELETE from HZ_PERSON_PROFILES where PARTY_ID = p_party_id;
992
993 DELETE from HZ_FINANCIAL_PROFILE where PARTY_ID = p_party_id;
994
995 DELETE from HZ_REFERENCES where REFERENCED_PARTY_ID = p_party_id;
996
997 DELETE from HZ_CERTIFICATIONS where PARTY_ID = p_party_id;
998
999 DELETE from HZ_CREDIT_RATINGS where PARTY_ID = p_party_id;
1000
1001 DELETE from HZ_SECURITY_ISSUED where PARTY_ID = p_party_id;
1002
1003 DELETE from HZ_FINANCIAL_NUMBERS where FINANCIAL_REPORT_ID in
1004 ( select FINANCIAL_REPORT_ID FROM HZ_FINANCIAL_REPORTS WHERE PARTY_ID = p_party_id);
1005
1006 DELETE from HZ_FINANCIAL_REPORTS where PARTY_ID = p_party_id;
1007
1008 DELETE from HZ_ORGANIZATION_INDICATORS where PARTY_ID = p_party_id;
1009
1010 DELETE from HZ_PERSON_INTEREST where PARTY_ID = p_party_id;
1011
1012 DELETE from HZ_CITIZENSHIP where PARTY_ID = p_party_id;
1013
1014 DELETE from HZ_WORK_CLASS where EMPLOYMENT_HISTORY_ID in
1015 (select EMPLOYMENT_HISTORY_ID FROM HZ_EMPLOYMENT_HISTORY WHERE PARTY_ID = p_party_id);
1016
1017 DELETE from HZ_EMPLOYMENT_HISTORY where PARTY_ID = p_party_id;
1018
1019 DELETE from HZ_PERSON_LANGUAGE where PARTY_ID = p_party_id;
1020
1021 DELETE from HZ_EDUCATION where PARTY_ID = p_party_id;
1022
1023 DELETE from HZ_INDUSTRIAL_REFERENCE where PARTY_ID = p_party_id;
1024
1025 execute immediate 'DELETE from HZ_CODE_ASSIGNMENTS where OWNER_TABLE_ID = :1 and OWNER_TABLE_NAME=''HZ_PARTIES''' using p_party_id;
1026
1027 execute immediate 'DELETE from HZ_CONTACT_PREFERENCES where CONTACT_LEVEL_TABLE_ID = :1 and CONTACT_LEVEL_TABLE=''HZ_PARTIES''' using p_party_id;
1028
1029 execute immediate 'DELETE from HZ_ORIG_SYS_REFERENCES where party_id = :1' using p_party_id;
1030
1031 begin
1032 execute immediate 'DELETE from HZ_STAGED_PARTIES where PARTY_ID = :1' using p_party_id;
1033 exception
1034 when others then
1035 null;
1036 end;
1037 DELETE from AS_CHANGED_ACCOUNTS_ALL where CUSTOMER_ID = p_party_id;
1038
1039 delete from wsh_location_owners wlo
1040 where wlo.owner_party_id = p_party_id
1041 and exists (
1042 select 'x'
1043 from wsh_location_owners wlo1
1044 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
1045
1046 update wsh_location_owners wlo
1047 set wlo.owner_party_id = -1
1048 where wlo.owner_party_id = p_party_id
1049 and not exists (
1050 select 'x'
1051 from wsh_location_owners wlo1
1052 where wlo1.wsh_location_id = wlo.wsh_location_id and wlo1.owner_party_id = -1);
1053
1054 Delete from zx_party_tax_profile PTP
1055 where ptp.party_type_code = 'THIRD_PARTY'
1056 and ptp.party_id = p_party_id
1057 and not exists (Select 'x'
1058 from zx_registrations reg
1059 where ptp.party_tax_profile_id = reg.party_tax_profile_id)
1060 and not exists (Select 'x'
1061 from zx_exemptions ex
1062 where ptp.party_tax_profile_id = ex.party_tax_profile_id)
1063 and not exists (Select 'x'
1064 from ZX_REPORT_CODES_ASSOC assoc
1065 where assoc.entity_code = 'ZX_PARTY_TAX_PROFILE'
1066 and assoc.ENTITY_ID = ptp.party_tax_profile_id)
1067 and not exists (Select 'x'
1068 from hz_code_assignments HCA
1069 where HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
1070 AND HCA.OWNER_TABLE_ID = PTP.PARTY_TAX_PROFILE_ID);
1071
1072 DELETE from HZ_PARTIES where party_id = p_party_id;
1073
1074 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED','BO_EVENTS_ENABLED')) THEN
1075
1076 HZ_BES_BO_UTIL_PKG.del_obj_hierarchy(p_party_id);
1077
1078 END IF;
1079
1080 /* if the purged party is of type 'RELATIONSHIP' then set the corresponding value in the hz_relationships to null */
1081 execute immediate 'update hz_relationships set party_id=null where party_id=:1 and party_id in
1082 (select party_id from hz_parties where party_type = ''PARTY_RELATIONSHIP'')' using p_party_id;
1083
1084 -- bug 4947069
1085
1086 delete from hz_relationships where (subject_id = p_party_id or object_id = p_party_id) and status = 'M';
1087
1088 /* update status to 'PURGED' in hz_purge_candidates for the purged parties */
1089 update hz_purge_candidates set status='PURGED' where candidate_party_id=p_party_id;
1090
1091 --fnd_file.close;
1092 hz_common_pub.enable_cont_source_security;
1093 -- standard call to get message count and if count is 1, get message info.
1094 FND_MSG_PUB.Count_And_Get(
1095 p_encoded => FND_API.G_FALSE,
1096 p_count => x_msg_count,
1097 p_data => x_msg_data);
1098
1099
1100 EXCEPTION
1101 WHEN FND_API.G_EXC_ERROR THEN
1102 ROLLBACK TO PURGE_PARTY;
1103 x_return_status := FND_API.G_RET_STS_ERROR;
1104 FND_MSG_PUB.Count_And_Get(
1105 p_encoded => FND_API.G_FALSE,
1106 p_count => x_msg_count,
1107 p_data => x_msg_data);
1108 RAISE FND_API.G_EXC_ERROR;
1109
1110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1111 ROLLBACK TO PURGE_PARTY;
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 FND_MSG_PUB.Count_And_Get(
1114 p_encoded => FND_API.G_FALSE,
1115 p_count => x_msg_count,
1116 p_data => x_msg_data);
1117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1118
1119 WHEN OTHERS THEN
1120 ROLLBACK to PURGE_PARTY;
1121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1122
1123 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
1124 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1125 FND_MSG_PUB.ADD;
1126
1127 FND_MSG_PUB.Count_And_Get(
1128 p_encoded => FND_API.G_FALSE,
1129 p_count => x_msg_count,
1130 p_data => x_msg_data );
1131 RAISE FND_API.G_EXC_ERROR;
1132
1133 END purge_party;
1134
1135 PROCEDURE post_app_logic(appid NUMBER, single_party VARCHAR2, check_flag boolean) IS
1136 parties_count1 NUMBER;
1137 appid_cnt NUMBER;
1138
1139 cursor appid_count(appid NUMBER) IS
1140 select count(*) from hz_application_trans_gt where app_id=appid;
1141
1142 BEGIN
1143
1144 OPEN appid_count(appid);
1145 FETCH appid_count into appid_cnt;
1146 CLOSE appid_count;
1147 If(appid_cnt=0) then
1148 parties_count1:=SQL%ROWCOUNT;
1149 if parties_count1>0 then
1150 insert into hz_application_trans_gt(app_id) values(appid);
1151 if check_flag=true then
1152 --dbms_output.put_line('insert single party into table'||appid);
1153 insert into hz_purge_gt(party_id) values(single_party);
1154 end if;
1155 end if;
1156 end if;
1157
1158 END post_app_logic;
1159
1160 FUNCTION logerror RETURN VARCHAR2 IS
1161 l_msg_data VARCHAR2(2000);
1162 BEGIN
1163 FND_MSG_PUB.Reset;
1164
1165 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1166 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1167 END LOOP;
1168 log(l_msg_data,true);
1169 RETURN l_msg_data;
1170 END logerror;
1171
1172 PROCEDURE log(
1173 message IN VARCHAR2,
1174 con_prg IN boolean,
1175 newline IN BOOLEAN DEFAULT TRUE) IS
1176 BEGIN
1177 IF message = 'NEWLINE' THEN
1178 if con_prg is not null and con_prg=true then
1179 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1180 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1181 end if;
1182 ELSE
1183 FND_FILE.put_line(fnd_file.log,message);
1184 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, message);
1185 END IF;
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 NULL;
1189 END log;
1190
1191 FUNCTION get_col_type(
1192 p_table VARCHAR2,
1193 p_column VARCHAR2,
1194 p_app_name VARCHAR2)
1195 RETURN VARCHAR2 IS
1196
1197 CURSOR data_type(schema1 VARCHAR2) IS
1198 SELECT DATA_TYPE FROM sys.all_tab_columns
1199 WHERE table_name = p_table
1200 AND COLUMN_NAME = p_column
1201 AND owner = schema1;
1202
1203 l_data_type VARCHAR2(106);
1204 ret_data_type VARCHAR2(106);
1205 l_bool BOOLEAN;
1206 l_status VARCHAR2(255);
1207 l_schema VARCHAR2(255);
1208 l_tmp VARCHAR2(2000);
1209
1210 BEGIN
1211 /*l_bool := fnd_installation.GET_APP_INFO(p_app_name,l_status,l_tmp,l_schema);
1212 OPEN data_type(l_schema);
1213 FETCH data_type INTO l_data_type;
1214 CLOSE data_type;
1215 if (l_data_type is null) then
1216 ret_data_type := 'NONE';
1217 else
1218 ret_data_type := l_data_type;
1219 end if;
1220
1221 RETURN ret_data_type;
1222 EXCEPTION
1223 WHEN OTHERS THEN
1224 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1225 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1226 FND_MSG_PUB.ADD;
1227 RAISE FND_API.G_EXC_ERROR;*/
1228 null;
1229 END get_col_type;
1230
1231 FUNCTION has_context(proc VARCHAR2) RETURN BOOLEAN IS
1232 l_entity VARCHAR2(255);
1233 l_procedure VARCHAR2(255);
1234 l_attribute VARCHAR2(255);
1235 c NUMBER;
1236 n NUMBER;
1237 l_custom BOOLEAN;
1238 BEGIN
1239 c := dbms_sql.open_cursor;
1240 dbms_sql.parse(c,proc,2);
1241 dbms_sql.close_cursor(c);
1242 RETURN TRUE;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 dbms_sql.close_cursor(c);
1246 RETURN FALSE;
1247 END;
1248
1249 PROCEDURE populate_fk_datatype IS
1250 cursor c_dict_no_fktype is
1251 select entity_name, fk_column_name, merge_dict_id, dict_application_id
1252 from hz_merge_dictionary
1253 where fk_data_type is null;
1254
1255 l_sub_entity_name VARCHAR2(150);
1256 l_sub_fk_column_name VARCHAR2(150);
1257 l_merge_dict_id NUMBER;
1258 l_app_id NUMBER;
1259 l_data_type VARCHAR2(100);
1260 l_app_name VARCHAR2(100);
1261
1262 BEGIN
1263 open c_dict_no_fktype;
1264 loop
1265 fetch c_dict_no_fktype into l_sub_entity_name, l_sub_fk_column_name,
1266 l_merge_dict_id, l_app_id;
1267 EXIT WHEN c_dict_no_fktype%NOTFOUND;
1268 l_app_name := get_app_name(l_app_id);
1269 l_data_type:=hz_party_merge.get_col_type(l_sub_entity_name,l_sub_fk_column_name,l_app_name);
1270
1271 update hz_merge_dictionary
1272 set fk_data_type = l_data_type
1273 where merge_dict_id = l_merge_dict_id;
1274
1275 end loop;
1276 close c_dict_no_fktype;
1277
1278 END;
1279
1280 FUNCTION get_app_name(appid NUMBER) RETURN VARCHAR2 IS
1281 appname VARCHAR2(100);
1282 cursor app_name(app_id NUMBER) IS
1283 Select application_short_name from fnd_application where application_id=app_id;
1284 BEGIN
1285 open app_name(appid);
1286 fetch app_name into appname;
1287 close app_name;
1288 return appname;
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 null;
1292 END;
1293
1294
1295 FUNCTION has_index(entity_name VARCHAR2, column_name VARCHAR2, app_name VARCHAR2, join_clause VARCHAR2) RETURN BOOLEAN IS
1296 l_bool BOOLEAN;
1297 l_status VARCHAR2(255);
1298 l_schema VARCHAR2(255);
1299 l_tmp VARCHAR2(2000);
1300 col_pos NUMBER;
1301 col_name VARCHAR2(100);
1302 entity_occur NUMBER;
1303 check_flag VARCHAR2(10);
1304 col_position NUMBER;
1305 indexname VARCHAR2(100);
1306 upper_join_clause VARCHAR2(2000);
1307
1308 cursor column_position(ent_name varchar2, ent_col_name varchar2, schema1 varchar2) is
1309 select min(column_position) from dba_ind_columns where table_name = ent_name
1310 and column_name = ent_col_name
1311 and index_owner = schema1 and table_owner = schema1;
1312
1313 cursor col_indexes(ent_name varchar2, colmn_name varchar2, schema1 varchar2) is
1314 select index_name, column_position from dba_ind_columns
1315 where table_name = ent_name and column_name = colmn_name
1316 and index_owner = schema1 and table_owner = schema1;
1317
1318 cursor preceeding_columns(ent_name varchar2, ind_name varchar2, col_position NUMBER, schema1 varchar2) is
1319 select column_name from dba_ind_columns where table_name = ent_name
1320 and index_name = ind_name
1321 and column_position<col_position
1322 and index_owner = schema1 and table_owner = schema1;
1323
1324 cursor indexed_views(ent_name varchar2, colmn_name varchar2) is
1325 select 'Y' from dual where
1326 (ent_name,colmn_name)
1327 in (('AS_ACCESSES_ALL', 'CUSTOMER_ID'),('AS_ACCESSES_ALL', 'ADDRESS_ID'),('AS_ACCESSES_ALL', 'PARTNER_CUSTOMER_ID'),
1328 ('AS_ACCESSES_ALL', 'PARTNER_CONT_PARTY_ID'),('AS_ACCESSES_ALL', 'PARTNER_ADDRESS_ID'),('ASG_PARTY_ACC_V', 'PARTY_ID'),
1329 ('OKE_K_FUNDING_SOURCES_PM_HV', 'K_PARTY_ID'),('IGW_PROP_PERSONS_TCA_V', 'PERSON_PARTY_ID'),
1330 ('MIS_HZ_MERGE_VETO_PARTIES', 'PARTY_ID'),('MIS_HZ_MERGE_VETO_PARTY_SITES', 'PARTY_SITE_ID'),('JTF_PERZ_QUERY_PARAM','PARAMETER_VALUE'));
1331
1332 cursor string_column(join_clause varchar2, col_name varchar2) is
1333 select instr(join_clause,col_name) from dual;
1334 i NUMBER :=0;
1335 cols NUMBER:=0;
1336 isView VARCHAR2(1) := null;
1337 BEGIN
1338 open indexed_views(entity_name, column_name);
1339 fetch indexed_views into isView;
1340 close indexed_views;
1341 if(isView='Y') then
1342 check_flag := 'Y';
1343 else
1344 l_bool := fnd_installation.GET_APP_INFO(app_name,l_status,l_tmp,l_schema);
1345 open column_position(entity_name, column_name, l_schema);
1346 fetch column_position into col_pos;
1347 close column_position;
1348
1349 if (col_pos is not null AND col_pos=1) then
1350 check_flag :='Y';
1351 --dbms_output.put_line('col position is 1');
1352 elsif(col_pos is not null AND col_pos>1) then
1353
1354 open col_indexes(entity_name, column_name, l_schema);
1355 loop
1356 fetch col_indexes into indexname, col_position;
1357 --dbms_output.put_line('index name ='||indexname||' and col_position ='||col_position);
1358 exit when col_indexes%NOTFOUND;
1359 if join_clause is not null then
1360 check_flag := 'Y';
1361 open preceeding_columns(entity_name, indexname, col_position, l_schema);
1362 loop
1363 fetch preceeding_columns into col_name;
1364 exit when preceeding_columns%NOTFOUND;
1365 --cols := preceeding_columns%ROWCOUNT;
1366 upper_join_clause := upper(join_clause);
1367 --dbms_output.put_line('nvl(instrb(upper_join_clause,col_name),0)'||nvl(instrb(upper_join_clause,col_name),0));
1368 IF nvl(instrb(upper_join_clause,col_name),0)<=0 then
1369 check_flag := 'N';
1370 exit;
1371 end if;
1372 end loop;
1373 close preceeding_columns;
1374 else
1375 check_flag := 'N';
1376 end if;
1377 if check_flag = 'Y' then
1378 --dbms_output.put_line('all columns exist in join clause=');
1379 exit;
1380 end if;
1381 end loop;
1382 close col_indexes;
1383 else
1384 check_flag:='N';
1385 --dbms_output.put_line('entity_occur=null');
1386 end if;
1387 end if;
1388
1389 if (check_flag='Y') then
1390 RETURN TRUE;
1391 else
1392 RETURN FALSE;
1393 end if;
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 RETURN FALSE;
1397 END;
1398
1399
1400 PROCEDURE delete_template
1401 (e1 VARCHAR2, fk1 VARCHAR2,pk1 VARCHAR2,j1 VARCHAR2, pe1 VARCHAR2, fk_data_typ1 VARCHAR2,
1402 first VARCHAR2, concat_string OUT NOCOPY VARCHAR2, cnt NUMBER) IS
1403
1404 e2 varchar2(50);
1405 fk2 varchar2(50);
1406 pk2 varchar2(50);
1407 j2 varchar2(1000);
1408 pe2 varchar2(50);
1409 e3 varchar2(50);
1410 fk3 varchar2(50);
1411 pk3 varchar2(50);
1412 j3 varchar2(1000);
1413 pe3 varchar2(50);
1414 fk_data_typ2 varchar2(100);
1415 fk_data_typ3 varchar2(100);
1416 s1 varchar2(31000);
1417 s2 varchar2(300);
1418 p1 varchar2(1000);
1419 p2 varchar2(500);
1420 p3 varchar2(500);
1421 p4 varchar2(500);
1422 p5 varchar2(500);
1423 p6 varchar2(500);
1424 p7 varchar2(500);
1425 p8 varchar2(500);
1426 p9 varchar2(500);
1427 p10 varchar2(500);
1428 p11 varchar2(500);
1429 p12 varchar2(10);
1430 fkcolumn_type varchar2(50);
1431 pkcolumn_type varchar2(50);
1432 column_type varchar2(50);
1433 partyid varchar2(100);
1434 valid_stmt boolean := true;
1435 l_sql VARCHAR2(32000);
1436 cnt2 NUMBER := 0;
1437 cnt3 NUMBER := 0;
1438
1439 cursor x2(parent varchar2) is --4500011
1440 select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1441 decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1442 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1443 AND directional_flag = ''F''', join_clause) join_clause, parent_entity_name,fk_data_type
1444 from hz_merge_dictionary where entity_name = parent;
1445
1446 cursor x3(parent2 varchar2) is --4500011
1447 select decode(entity_name,'HZ_PARTY_RELATIONSHIPS','HZ_RELATIONSHIPS',entity_name) entity_name, fk_column_name,
1448 decode(entity_name,'HZ_PARTY_RELATIONSHIPS','RELATIONSHIP_ID',pk_column_name) pk_column_name,
1449 decode(entity_name,'HZ_PARTY_RELATIONSHIPS', join_clause || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES''
1450 AND directional_flag = ''F''', join_clause) join_clause,
1451 parent_entity_name,fk_data_type
1452 from hz_merge_dictionary where entity_name = parent2;
1453
1454 BEGIN
1455 if pe1='HZ_PARTIES' then
1456 partyid :=' temp.party_id ';
1457 if (fk_data_typ1<>'NUMBER' AND fk_data_typ1 IS NOT NULL) then
1458 partyid := ' to_char(temp.party_id) ';
1459 end if;
1460
1461 if j1 is not null then
1462 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1463 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1464 else
1465 p3:=' ';
1466 end if;
1467
1468 if(first = 'TRUE' AND cnt=1) then
1469 l_sql := 'delete from hz_purge_gt temp where ';
1470 p1:= ' exists (select ''Y'' from '||e1;
1471 p2:= ' xx where xx.'||fk1||' = '||partyid;
1472 if j1 is not null then
1473 p3:= ' and '||'('||p3||')';
1474 end if;
1475 elsif (first = 'TRUE' AND cnt>1) then
1476 l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1477 p1:= ' or exists (select ''Y'' from '||e1;
1478 p2:= ' xx where xx.'||fk1||' = '||partyid;
1479 if j1 is not null then
1480 p3:= ' and '||'('||p3||')';
1481 end if;
1482 elsif(first = 'FALSE' AND cnt=1) then
1483 l_sql := 'delete from hz_purge_gt temp where ';
1484 p1:= partyid||' in (select /*+ parallel(xx)*/ xx.'||fk1||' from '||e1;
1485 p2:= ' xx ';
1486 if j1 is not null then
1487 p3:= ' where '||'('||p3||')';
1488 end if;
1489 end if;
1490
1491
1492 p4:= ')';
1493
1494 s1:= s1||fnd_global.local_chr(10)||p1||
1495 p2||fnd_global.local_chr(10)||p3||p4;
1496 else
1497 -- open cursor to get the second level tables, which have/not have HZ_PARTIES as parent_entity
1498 open x2(pe1);
1499 loop
1500 fetch x2 into e2, fk2, pk2, j2, pe2,fk_data_typ2;
1501 exit when x2%NOTFOUND;
1502 cnt2:=cnt2+1;
1503 if pe2 = 'HZ_PARTIES' then
1504 partyid :=' temp.party_id ';
1505 if (fk_data_typ2<>'NUMBER' AND fk_data_typ2 IS NOT NULL) then
1506 partyid := ' to_char(temp.party_id) ';
1507 end if;
1508
1509 if j2 is not null then
1510 --select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1511 select decode(instr(j2,'group'),0,j2,substr(j1,1,instr(j1,'group')-1)) into p3 from dual;
1512 else
1513 p3:=' ';
1514 end if;
1515
1516 if(first = 'TRUE' AND cnt=1) then
1517 l_sql := 'delete from hz_purge_gt temp where ';
1518 if(cnt2=1) then
1519 p1:= ' exists (select ''Y'' from '||e2;
1520 else
1521 p1:= ' or exists (select ''Y'' from '||e2;
1522 end if;
1523 p2:= ' xx where xx.'||fk2||' = '||partyid;
1524 if j2 is not null then
1525 p3 := ' and '||'('||p3||')';
1526 end if;
1527 p4:= ' and exists' ;
1528 elsif (first = 'TRUE' AND cnt>1) then
1529 l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1530 p1:= ' or exists (select ''Y'' from '||e2;
1531 p2:= ' xx where xx.'||fk2||' = '||partyid;
1532 if j2 is not null then
1533 p3 := ' and '||'('||p3||')';
1534 end if;
1535 p4:= ' and exists' ;
1536 elsif(first = 'FALSE' AND cnt=1) then
1537 l_sql := 'delete from hz_purge_gt temp where ';
1538 if(cnt2=1) then
1539 p1:= partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;
1540 else
1541 p1:= ' or '||partyid||' in (select /*+ parallel (xx)*/ xx.'||fk2||' from '||e2;
1542 end if;
1543 p2:= ' xx ';
1544 p4:= ' xx.'||pk2||' ';
1545 if(fk_data_typ1<>fk_data_typ2) then
1546 if fk_data_typ1='VARCHAR2' then
1547 p4:= ' to_char(xx.'||pk2||')';
1548 end if;
1549 end if;
1550 if j2 is not null then
1551 p3 := ' where '||'('||p3||')';
1552 p4 := ' and '||p4;
1553 else
1554 p4 := ' where '||p4;
1555 end if;
1556 end if;
1557
1558 if j1 is not null then
1559 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p7 from dual;
1560 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p7 from dual;
1561 else
1562 p7:='';
1563 end if;
1564
1565 if(first='FALSE') then
1566 p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk1||' from '||e1;
1567 p6:= ' yy ';
1568 if j1 is not null then
1569 p7:= ' where '||p7;
1570 end if;
1571 else
1572 p5 := '(select ''Y'' from '||e1;
1573 p6:= ' yy where yy.'||fk1||'=xx.'||pk2||'';
1574 if(fk_data_typ1<>fk_data_typ2) then
1575 if fk_data_typ1='VARCHAR2' then
1576 p6:= ' yy where yy.'||fk1||'=to_char(xx.'||pk2||')';
1577 end if;
1578 end if;
1579 if j1 is not null then
1580 p7:= ' and '||p7;
1581 end if;
1582 end if;
1583
1584 p8:= '))';
1585 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||
1586 fnd_global.local_chr(10)||p5||fnd_global.local_chr(10)||p6||fnd_global.local_chr(10)||p7||p8;
1587
1588 else
1589
1590 if(cnt2>1) then
1591 cnt3:=1;
1592 else
1593 cnt3:=0;
1594 end if;
1595
1596 -- open cursor to get the third level tables, which have HZ_PARTIES as parent_entity
1597 open x3(pe2);
1598 loop
1599 fetch x3 into e3, fk3, pk3, j3, pe3,fk_data_typ3;
1600 exit when x3%NOTFOUND;
1601 cnt3:=cnt3+1;
1602 if pe3 = 'HZ_PARTIES' then
1603 partyid :=' temp.party_id ';
1604
1605 if (fk_data_typ3<>'NUMBER' AND fk_data_typ3 IS NOT NULL) then
1606 partyid := ' to_char(temp.party_id) ';
1607 end if;
1608
1609 if j3 is not null then
1610 --select decode(instr(j3,'group'),0,' and '||j3,' and '||substr(j3,1,instr(j3,'group')-1)) into p3 from dual;
1611 select decode(instr(j3,'group'),0,j3,substr(j3,1,instr(j3,'group')-1)) into p3 from dual;
1612 else
1613 p3:='';
1614 end if;
1615
1616 if(first = 'TRUE' AND cnt=1) then
1617 l_sql := 'delete from hz_purge_gt temp where ';
1618 if(cnt3=1) then
1619 p1:= ' exists (select ''Y'' from '||e3;
1620 else
1621 p1:= ' or exists (select ''Y'' from '||e3;
1622 end if;
1623 p2:= ' xx where xx.'||fk3||' = '||partyid ;
1624 if j3 is not null then
1625 p3:=' and '||'('||p3||')';
1626 end if;
1627 p4:= ' and exists ';
1628 elsif (first = 'TRUE' AND cnt>1) then
1629 l_sql := 'delete from hz_purge_gt temp where 1<>1 ';
1630 p1:= ' or exists (select ''Y'' from '||e3;
1631 p2:= ' xx where xx.'||fk3||' = '||partyid ;
1632 if j3 is not null then
1633 p3:=' and '||'('||p3||')';
1634 end if;
1635 p4:= ' and exists ';
1636 elsif(first = 'FALSE' AND cnt=1) then
1637 if(cnt3=1) then
1638 l_sql := 'delete from hz_purge_gt temp where ';
1639 p1:= partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;
1640 else
1641 p1:= ' or '||partyid||' in (select /*+ parallel(xx)*/ ''Y'' from '||e3;
1642 end if;
1643 p2:= ' xx ' ;
1644 if(fk_data_typ2<>fk_data_typ3) then
1645 if fk_data_typ3='VARCHAR2' then
1646 p4:= ' to_char(xx.'||pk3||')';
1647 end if;
1648 end if;
1649 if j3 is not null then
1650 p3:=' where '||'('||p3||')';
1651 p4:= ' and '||p4||' ';
1652 else
1653 p4:= ' where '||p4||' ';
1654 end if;
1655 end if;
1656
1657
1658 if j2 is not null then
1659 --select decode(instr(j2,'group'),0,' and '||j2,' and '||substr(j2,1,instr(j2,'group')-1)) into p7 from dual;
1660 select decode(instr(j2,'group'),0,j2,substr(j2,1,instr(j2,'group')-1)) into p7 from dual;
1661 else
1662 p7:='';
1663 end if;
1664
1665 if(first='FALSE') then
1666 p5:= ' in (select /*+ parallel(yy)*/ yy.'||fk2||' from '||e2;
1667 p6:= ' yy ';
1668 if(fk_data_typ1<>fk_data_typ2) then
1669 if fk_data_typ1='VARCHAR2' then
1670 p8:= ' and to_char(yy.'||pk2||')';
1671 end if;
1672 end if;
1673 if j2 is not null then
1674 p7:= ' where '||p7;
1675 p8:= ' and '||p8||'';
1676 else
1677 p8:= ' where '||p8||'';
1678 end if;
1679 else
1680 p5:= ' (select ''Y'' from '||e2;
1681 p6:= ' yy where yy.'||fk2||'=xx.'||pk3||' ';
1682 if(fk_data_typ2<>fk_data_typ3) then
1683 if fk_data_typ3='VARCHAR2' then
1684 p6:= ' yy where yy.'||fk2||'=to_char(xx.'||pk3||')';
1685 end if;
1686 end if;
1687 if j2 is not null then
1688 p7:= ' and '||p7;
1689 end if;
1690 p8:= ' and exists ';
1691 end if;
1692
1693 if j1 is not null then
1694 --select decode(instr(j1,'group'),0,' and '||j1,' and '||substr(j1,1,instr(j1,'group')-1)) into p11 from dual;
1695 select decode(instr(j1,'group'),0,j1,substr(j1,1,instr(j1,'group')-1)) into p11 from dual;
1696 else
1697 p11:=' ';
1698 end if;
1699
1700 if (first='FALSE') then
1701 p9:= ' in (select /*+ parallel(zz)*/ yy.'||pk2||' from '||e1;
1702 p10:= ' zz ';
1703 if j1 is not null then
1704 p11:= ' where '||p11;
1705 end if;
1706 else
1707 p9:= ' (select ''Y'' from '||e1;
1708 p10:= ' zz where zz.'||fk1||' = yy.'||pk2;
1709 if(fk_data_typ1<>fk_data_typ2) then
1710 if fk_data_typ1='VARCHAR2' then
1711 p10:= ' zz where zz.'||fk1||'=to_char(yy.'||pk2||')';
1712 end if;
1713 end if;
1714 if j1 is not null then
1715 p11:= ' and '||p11;
1716 end if;
1717 end if;
1718
1719 p12:= ')))';
1720 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||
1721 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||
1722 p11||p12;
1723 end if;
1724
1725 end loop;
1726 close x3;
1727 end if;
1728
1729 end loop;
1730 close x2;
1731
1732 end if;
1733 l_sql := l_sql||fnd_global.local_chr(10)||s1;
1734 valid_stmt:= has_context(l_sql);
1735 --valid_stmt:=true;
1736 if (valid_stmt=true) then
1737 --dbms_output.put_line('cnt='||cnt||',e1='||e1);
1738 concat_string := s1;
1739 else
1740 concat_string := null;
1741 --dbms_output.put_line('notvalid cnt='||l_sql);
1742 end if;
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 null;
1747 END;
1748
1749 END HZ_PURGE;
[Home] [Help]