1 PACKAGE BODY JTF_IH_PURGE AS
2 /* $Header: JTFIHPRB.pls 120.5 2006/01/24 21:03:28 nchouras ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_PURGE';
4
5 -- Program History
6 -- 09-OCT-2002 Igor Aleshin Created.
7 -- 26-NOV-2002 Igor Aleshin Added parameters O/R/R for Interactions and
8 -- Activities.
9 -- 13-JAN-2003 Igor Aleshin Added parameter p_Active
10 -- 07-MAY-2003 Igor Aleshin Fixed bug# 2945880 - TST1159_DROP11: WHEN THE
11 -- PURGE TYPE = ALL GETTING ORA-01006 BIND
12 -- VARIABLE NOT E
13 -- 09-MAY-2003 Igor Aleshin TST1159: THE PURGE PARAMETERS ARE CASE
14 -- SENSITIVE AND DOES NOT COVERT THE LOWERCA
15 -- 24-OCT-2003 Igor Aleshin Fixed bug# 3216639 - JTH.R: CLEAN-UP DROP C
16 -- ISSUES IN THE ACTIVITIES AND INTERACTIONS
17 -- VIEWERS
18 -- 07-MAY-2004 Igor Aleshin Fixed File.sql.35 issue
19 -- 25-MAY-2004 Igor Aleshin Fixed bug# 3647806 - JTH.R: NEED TO CORRECT
20 -- PERF. ISSUE WITH PARTY_TYPE VALIDATION IN
21 -- PURGE PROGRAM
22 -- 04-NOV-2004 Venkatesh K Bug fix 3999018 - Media Item Check for G_MISS
23 -- 29-DEC-2004 Neha Chourasia Bug 4063673 fix for date range purge not working for Active set to Y
24 -- 25-FEB-2005 Neha Chourasia ER 4007013 Added API PURGE_BY_OBJECT to purge interactions and
25 -- activities related to the object passed.
26 -- 25-JAN-2006 Neha Chourasia Bug 4965592 fix for hints for sqls as suggested
27 -- by batch perf team
28
29 PROCEDURE PURGE(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY VARCHAR2,
30 p_Party_IDs VARCHAR2,
31 p_Party_Type VARCHAR2,
32 p_Active VARCHAR2, -- Bug# 4063673 - changed position from last param to 3rd
33 p_Start_Date DATE,
34 p_End_Date DATE,
35 p_SafeMode VARCHAR2,
36 p_Purge_Type VARCHAR2,
37 -- Added on 26-NOV-2002
38 p_ActivityOutcome VARCHAR2,
39 p_ActivityResult VARCHAR2,
40 p_ActivityReason VARCHAR2,
41 p_InterOutcome VARCHAR2,
42 p_InterResult VARCHAR2,
43 p_InterReason VARCHAR2
44 --Added on 13-JAN-2003
45 --p_Active VARCHAR2
46 ) IS
47
48 nCountInteraction NUMBER; -- Count of Interactions for Purge
49 nCountActivities NUMBER; -- Count of Activities for Purge;
50 nCntWrngInteraction NUMBER; -- Count of Interactions for Purge which have an errors
51 nCountMediaItems NUMBER; -- Count of Related of Media Items
52 nCountMediaItemLC NUMBER; -- Count of Related of Media Item Lifecycle
53 nCount NUMBER;
54 nCountMedia NUMBER;
55 sSql VARCHAR2(2000);
56 nRowTrn NUMBER;
57 nInteraction_Id JTF_IH_INTERACTIONS.INTERACTION_ID%TYPE;
58 ErrNoPartyType EXCEPTION;
59 ErrInteraction EXCEPTION;
60 ErrStartEndDate EXCEPTION;
61 ErrEndDateNull EXCEPTION; -- Added for Bug# 4063673
62 NoCriteriaSpecified EXCEPTION;
63 ErrPatyIdsDescription EXCEPTION;
64 ErrInvPartyType EXCEPTION;
65 n_CursorId NUMBER;
66 n_Res NUMBER;
67 n_Media_Id NUMBER;
68 l_msg_data VARCHAR2(2000);
69 n_NLS_Format VARCHAR2(30);
70 i_Dummy NUMBER;
71 l_SafeMode VARCHAR2(10);
72 l_Active VARCHAR2(1);
73
74 BEGIN
75 l_SafeMode := NVL(p_SafeMode,'TRUE');
76 l_Active := NVL(p_Active,'N');
77 p_commit := NVL(p_commit,FND_API.G_TRUE);
78 TranRows := NVL(TranRows,1000);
79 -- Check range for purge. If you are going to purge all interactions
80 -- then you should specify the p_Purge_Type = 'ALL'
81 IF p_Start_Date IS NULL AND p_End_Date IS NULL
82 AND (p_Purge_Type IS NULL OR UPPER(p_Purge_Type) <> 'ALL')
83 AND p_Party_IDs IS NULL AND p_Party_Type IS NULL
84 -- Added on 26-NOV-2002
85 AND p_ActivityOutcome IS NULL AND p_ActivityResult IS NULL
86 AND p_ActivityReason IS NULL AND p_InterOutcome IS NULL
87 AND p_InterResult IS NULL AND p_InterReason IS NULL THEN
88
89 RAISE NoCriteriaSpecified;
90 END IF;
91
92 IF UPPER(p_Purge_Type) <> 'ALL' OR p_Purge_Type IS NULL THEN
93
94 -- Check p_Party_Type value, it it required.
95 IF p_Party_Type IS NOT NULL AND UPPER(p_Party_Type) NOT IN('PERSON','ORGANIZATION','PARTY_RELATIONSHIP') THEN
96 RAISE ErrInvPartyType;
97 END IF;
98
99 -- Now I'm going to build a dynamic sql statement based on input parameters.
100 --
101 IF p_ActivityOutcome IS NOT NULL OR p_ActivityResult IS NOT NULL OR p_ActivityReason IS NOT NULL THEN
102 sSql := 'SELECT DISTINCT JTFI.INTERACTION_ID FROM JTF_IH_INTERACTIONS JTFI, JTF_IH_ACTIVITIES ACT ';
103 -- If you've set up some value to p_Party_Type parameter, then include HZ_PARTIES table
104 -- to major sql statement, based on JTF_IH_INTERACTION.Party_ID
105 IF p_Party_Type IS NOT NULL THEN
106 sSql := sSql || ', HZ_PARTIES HZ ';
107 END IF;
108 --sSql := sSql ||'WHERE JTFI.ACTIVE = '''||p_Active||''' AND ACT.INTERACTION_ID = JTFI.INTERACTION_ID ';
109 sSql := sSql ||'WHERE JTFI.ACTIVE = :active AND ACT.INTERACTION_ID = JTFI.INTERACTION_ID ';
110
111 IF p_Party_Type IS NOT NULL THEN
112 sSql := sSql ||' AND JTFI.PARTY_ID=HZ.PARTY_ID ';
113 END IF;
114
115 ELSE
116
117 sSql := 'SELECT INTERACTION_ID FROM JTF_IH_INTERACTIONS JTFI ';
118 IF p_Party_Type IS NOT NULL THEN
119 sSql := sSql ||', HZ_PARTIES HZ ';
120 END IF;
121 --sSql := sSql || 'WHERE JTFI.ACTIVE = '''||p_Active||'''';
122 sSql := sSql || 'WHERE JTFI.ACTIVE = :active ';
123 IF p_Party_Type IS NOT NULL THEN
124 sSql := sSql || ' AND JTFI.PARTY_ID=HZ.PARTY_ID ';
125 END IF;
126 END IF;
127
128 IF p_Party_Type IS NOT NULL THEN
129 --sSql := sSql ||' AND PARTY_TYPE = UPPER('''||p_Party_Type||''') ';
130 sSql := sSql ||' AND PARTY_TYPE = UPPER(:party_type) ';
131 END IF;
132
133 -- Compare p_End_Date and p_Start_Date. p_End_Date shouldn't be less then p_Start_Date
134 -- This comparation I'll make if p_Active flag is 'N', otherwise let pass it.
135 IF p_Start_Date IS NOT NULL AND p_End_Date IS NOT NULL AND l_Active = 'N' THEN
136 BEGIN
137 SELECT p_End_Date-p_Start_Date INTO n_Res FROM DUAL;
138 IF n_Res < 0 THEN
139 RAISE ErrStartEndDate;
140 END IF;
141 EXCEPTION
142 WHEN OTHERS THEN
143 RAISE ErrStartEndDate;
144 END;
145 END IF;
146
147 -- Add Party_IDs to major select statement if they are setup.
148 IF p_Party_Ids IS NOT NULL THEN
149 -- Make data validation for PartyIds
150 BEGIN
151 SELECT TO_NUMBER(REPLACE(REPLACE(p_Party_IDs,',',''),' ','')) INTO n_Res FROM DUAL;
152 EXCEPTION
153 WHEN OTHERS THEN
154 RAISE ErrPatyIdsDescription;
155 END;
156 sSql := sSql ||' AND JTFI.PARTY_ID IN ('||p_Party_IDs||') ';
157 END IF;
158
159 -- Fix for Bug# 4063673 - purge based only on START_DATE
160 IF p_START_DATE IS NOT NULL THEN
161 --IF l_Active = 'N' THEN
162 sSql := sSql || ' AND TO_DATE(TO_CHAR(JTFI.START_DATE_TIME,''MM/DD/RRRR''),''MM/DD/RRRR'') >= TO_DATE(TO_CHAR(:start_date,''MM/DD/RRRR''),''MM/DD/RRRR'') ';
163 --ELSE
164 -- sSql := sSql || ' AND TO_DATE(TO_CHAR(JTFI.START_DATE_TIME,''MM/DD/RRRR''),''MM/DD/RRRR'') <= TO_DATE(TO_CHAR(:start_date,''MM/DD/RRRR''),''MM/DD/RRRR'') ';
165 --END IF;
166 END IF;
167
168 -- If Interaction is Active and Start Date is not null then end date cannot be null
169 -- else all active records after Start Date upto current date are deleted
170 -- Fix for Bug# 4063673
171 IF p_END_DATE IS NULL AND l_Active = 'Y' THEN
172 RAISE ErrEndDateNull;
173 END IF;
174
175 -- Fix for Bug# 4063673 - purge based only on START_DATE
176 --IF p_END_DATE IS NOT NULL AND l_Active = 'N' THEN
177 IF p_END_DATE IS NOT NULL THEN
178 sSql := sSql || ' AND TO_DATE(TO_CHAR(JTFI.START_DATE_TIME,''MM/DD/RRRR''),''MM/DD/RRRR'') <= TO_DATE(TO_CHAR(:end_date,''MM/DD/RRRR''),''MM/DD/RRRR'') ';
179 END IF;
180
181 -- Added on 26-NOV-2002
182 --
183 IF p_InterOutcome IS NOT NULL THEN
184 sSql := sSql || ' AND JTFI.OUTCOME_ID IN ('||p_InterOutcome||') ';
185 END IF;
186
187 IF p_InterResult IS NOT NULL THEN
188 sSql := sSql || ' AND JTFI.RESULT_ID IN ('||p_InterResult||') ';
189 END IF;
190
191 IF p_InterReason IS NOT NULL THEN
192 sSql := sSql || ' AND JTFI.REASON_ID IN ('||p_InterReason||') ';
193 END IF;
194
195 IF p_ActivityOutcome IS NOT NULL THEN
196 sSql := sSql || ' AND ACT.OUTCOME_ID IN ('||p_ActivityOutcome||') ';
197 END IF;
198
199 IF p_ActivityResult IS NOT NULL THEN
200 sSql := sSql || ' AND ACT.RESULT_ID IN ('||p_ActivityResult||') ';
201 END IF;
202
203 IF p_ActivityReason IS NOT NULL THEN
204 sSql := sSql || ' AND ACT.REASON_ID IN ('||p_ActivityReason||') ';
205 END IF;
206
207 ELSE
208 sSql := 'SELECT INTERACTION_ID FROM JTF_IH_INTERACTIONS WHERE ACTIVE = :active ORDER BY INTERACTION_ID ';
209 END IF;
210
211 -- This piece of code need for debugging a sql statement
212 /*
213 if length(sSql) > 80 then
214 dbms_output.put_line(substr(sSql,1,80));
215 for i_Dummy in 1..round(length(sSql)/80) loop
216 dbms_output.put_line(substr(sSql,(i_Dummy*80)+1,80));
217 end loop;
218 else
219 dbms_output.put_line(sSql);
220 end if;*/
221
222 n_CursorId := DBMS_SQL.OPEN_CURSOR;
223
224 DBMS_SQL.PARSE(n_CursorId, sSql, DBMS_SQL.NATIVE);
225 --dbms_output.put_line('');
226 DBMS_SQL.BIND_VARIABLE(n_CursorId,':active',l_Active);
227 IF UPPER(p_Purge_Type) <> 'ALL' OR p_Purge_Type IS NULL THEN
228 IF (p_Party_Type IS NOT NULL) THEN
229 DBMS_SQL.BIND_VARIABLE(n_CursorId,':party_type',p_Party_Type);
230 END IF;
231
232 -- Bug# 2945880 - added expression for p_Purge_Type
233 --
234
235 -- Fix for Bug# 4063673
236 --IF p_END_DATE IS NOT NULL AND l_Active = 'N' AND (RTRIM(p_Purge_Type) = '' OR p_Purge_Type IS NULL) THEN
237 IF p_END_DATE IS NOT NULL AND (RTRIM(p_Purge_Type) = '' OR p_Purge_Type IS NULL) THEN
238 DBMS_SQL.BIND_VARIABLE(n_CursorId,':end_date',p_End_Date);
239 END IF;
240 -- Bug# 2945880 - added expression for p_Purge_Type
241 --
242 IF p_START_DATE IS NOT NULL AND (RTRIM(p_Purge_Type) = '' OR p_Purge_Type IS NULL) THEN
243 DBMS_SQL.BIND_VARIABLE(n_CursorId,':start_date',p_Start_Date);
244 END IF;
245 END IF;
246 DBMS_SQL.DEFINE_COLUMN(n_CursorId, 1, nInteraction_Id);
247
248 --FND_FILE.NEW_LINE(FND_FILE.LOG,1);
249 --FND_FILE.PUT_LINE(FND_FILE.LOG, sSql);
250
251 n_Res := DBMS_SQL.EXECUTE(n_CursorId);
252
253 nCount := 0;
254 nRowTrn := 0;
255 nCountInteraction := 0;
256 nCountActivities := 0;
257 nCountMediaItems := 0;
258 nCountMediaItemLC := 0;
259
260 LOOP
261 IF DBMS_SQL.FETCH_ROWS(n_CursorId) = 0 THEN
262 EXIT;
263 END IF;
264 DBMS_SQL.COLUMN_VALUE(n_CursorId,1,nInteraction_Id);
265 -- If current interaction has any errors then skip it
266 BEGIN
267 IF l_SafeMode = 'FALSE' THEN
268 BEGIN
269 SAVEPOINT Activities;
270 -- Clean Up an Activities
271 FOR curActivity IN (SELECT ACTIVITY_ID FROM JTF_IH_ACTIVITIES
272 WHERE INTERACTION_ID = nInteraction_Id) LOOP
273 DELETE FROM JTF_IH_ACTIVITIES
274 WHERE ACTIVITY_ID = curActivity.ACTIVITY_ID
275 RETURNING Media_ID INTO n_Media_Id;
276
277 nCountActivities := nCountActivities + 1;
278 IF ( (n_Media_Id IS NOT NULL) AND (n_Media_Id <> fnd_api.g_miss_num) ) THEN
279 SELECT Count(*) INTO nCountMedia
280 FROM JTF_IH_ACTIVITIES WHERE MEDIA_ID = n_Media_Id;
281 --
282 -- Delete Media Item If they aren't
283 -- related to other Activities.
284 --
285 IF nCountMedia = 0 THEN
286 DELETE FROM JTF_IH_MEDIA_ITEMS WHERE MEDIA_ID = n_Media_Id;
287 nCountMediaItems := nCountMediaItems + 1;
288 -- And Related to MediaID Media Item LifeCycle.
289 BEGIN
290 DELETE FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE MEDIA_ID = n_Media_Id;
291 nCountMediaItemLC := nCountMediaItemLC + SQL%ROWCOUNT;
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 NULL;
295 END;
296 END IF;
297 END IF;
298 END LOOP;
299 -- Clean Up Parent and All Child Interactions in the JTF_IH_INTERACTION_INTERS.
300 DELETE FROM JTF_IH_INTERACTION_INTERS WHERE
301 (INTERACT_INTERACTION_IDRELATES = nInteraction_Id)
302 OR (INTERACT_INTERACTION_ID = nInteraction_Id);
303 -- Clean Up current Intraction.
304 DELETE FROM JTF_IH_INTERACTIONS WHERE INTERACTION_ID = nInteraction_Id;
305 EXCEPTION
306 WHEN OTHERS THEN
307 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Interaction_ID = '||nInteraction_Id||' has an error:');
308 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: '||SQLERRM);
309 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: -----------------------------------------');
310 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_INTERACTION_ERROR');
311 FND_MESSAGE.SET_TOKEN('INTERACTION', to_char(nInteraction_Id));
312 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
313 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
314 ROLLBACK TO SAVEPOINT Activities;
315 RAISE ErrInteraction;
316 END;
317 ELSE
318 SELECT nCountActivities + Count(*) INTO nCountActivities
319 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = nInteraction_Id;
320
321 SELECT nCountMediaItems + Count(*) INTO nCountMediaItems
322 FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = nInteraction_Id AND
323 Media_ID IS NOT NULL;
324
325 END IF;
326 nCountInteraction := nCountInteraction + 1;
327 nRowTrn := nRowTrn + 1;
328 -- Make a commit for transactions only if SafeMode is False.
329 IF nRowTrn = TranRows AND l_SafeMode = 'FALSE' THEN
330 IF p_commit = FND_API.G_TRUE THEN
331 COMMIT;
332 nRowTrn := 0;
333 END IF;
334 END IF;
335 EXCEPTION
336 WHEN ErrInteraction THEN
337 nCntWrngInteraction := nCntWrngInteraction + 1;
338 END;
339 END LOOP;
340
341 IF p_commit = FND_API.G_TRUE THEN
342 COMMIT;
343 NULL;
344 END IF;
345
346 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Done :');
347 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_DONE');
348 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
349
350 IF nCountInteraction = 0 THEN
351 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Interactions not found');
352 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_NO_INTERACTIONS');
353 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
354 ELSE
355 IF l_SafeMode = 'TRUE' THEN
356 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: You are going to delete :');
357 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Interactions....'||to_char(nCountInteraction));
358 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Activities......'||to_char(nCountActivities));
359 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Media Items.....'||to_char(nCountMediaItems));
360 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: -----------------------------------------');
361 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_SAFEMODE_REPORT');
362 FND_MESSAGE.SET_TOKEN('INTERACTIONS', to_char(nCountInteraction));
363 FND_MESSAGE.SET_TOKEN('ACTIVITIES', to_char(nCountActivities));
364 FND_MESSAGE.SET_TOKEN('MEDIAITEMS', to_char(nCountMediaItems));
365 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
366 ELSE
367 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Deleted :');
368 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Interactions....'||to_char(nCountInteraction));
369 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Activities......'||to_char(nCountActivities));
370 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Media Items.....'||to_char(nCountMediaItems));
371 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: Media Item LC...'||to_char(nCountMediaItemLC));
372 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: -----------------------------------------');
373 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_REPORT');
374 FND_MESSAGE.SET_TOKEN('INTERACTIONS', to_char(nCountInteraction));
375 FND_MESSAGE.SET_TOKEN('ACTIVITIES', to_char(nCountActivities));
376 FND_MESSAGE.SET_TOKEN('MEDIAITEMS', to_char(nCountMediaItems));
377 FND_MESSAGE.SET_TOKEN('MEDIAITEMLC', to_char(nCountMediaItemLC));
378 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
379 END IF;
380 END IF;
381 DBMS_SQL.CLOSE_CURSOR(n_CursorId);
382 EXCEPTION
383 WHEN ErrInvPartyType THEN
384 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_INV_PARTY_TYPE');
385 FND_MESSAGE.SET_TOKEN('PARTYTYPE', p_Party_Type);
386 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
387 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
388 WHEN ErrNoPartyType THEN
389 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_NO_PARTY_TYPE');
390 FND_MESSAGE.SET_TOKEN('PARTYTYPE', p_Party_Type);
391 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
392 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
393 WHEN ErrStartEndDate THEN
394 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_INVALID_DATE');
395 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
396 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
397 -- Added for Bug# 4063673
398 WHEN ErrEndDateNull THEN
399 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_DATE_TO_NOTNULL');
400 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
401 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
402 WHEN NoCriteriaSpecified THEN
403 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_NO_CRITERIA');
404 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
405 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
406 WHEN ErrPatyIdsDescription THEN
407 FND_MESSAGE.SET_NAME('JTF','JTF_IH_PURGE_WRONG_PARTY_IDS');
408 FND_MESSAGE.SET_TOKEN('PARTYIDS', p_Party_IDs);
409 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
410 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
411 WHEN OTHERS THEN
412 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
413 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
414 --DBMS_OUTPUT.PUT_LINE('JTF_IH_PURGE: '||SQLERRM);
415 END PURGE;
416
417 PROCEDURE P_DELETE_INTERACTIONS (
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2,
420 p_commit IN VARCHAR2,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_data OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 p_processing_set_id IN NUMBER,
425 p_object_type IN VARCHAR2
426 )IS
427 l_api_name CONSTANT VARCHAR2(30) := 'P_DELETE_INTERACTIONS';
428 l_api_version CONSTANT NUMBER := 1.1;
429 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
430 l_return_status VARCHAR2(1);
431 l_msg_count NUMBER;
432 l_msg_data VARCHAR2(2000);
433 l_fnd_log_msg VARCHAR2(2000);
434
435 -- cursor on stage global temp table
436 -- to select object ids corresp. to which
437 -- interactions and activities are to be purged
438
439 CURSOR c_jtf_obj_purge_temp_success IS
440 SELECT distinct object_id, null
441 FROM JTF_OBJECT_PURGE_PARAM_TMP
442 WHERE nvl(purge_status, 'S') <> 'E'
443 AND OBJECT_TYPE = p_object_type
444 AND PROCESSING_SET_ID = p_processing_set_id;
445
446 TYPE table_incidents_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
447 TYPE table_media_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
448 TYPE table_int_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
449
450 l_inter_int_ids table_incidents_ids;
451 l_activity_id table_incidents_ids;
452 l_incident_ids table_incidents_ids;
453 l_dummy_col table_incidents_ids;
454 l_media_ids table_media_ids;
455 lc_media_ids table_media_ids;
456 l_int_ids table_int_ids;
457
458 l_processing_set_id NUMBER;
459 l_obj_type_lookup VARCHAR2(50);
460 l_jtf_activity VARCHAR2(50);
461 l_jtf_interaction VARCHAR2(50);
462
463 ErrNoRecFound EXCEPTION;
464
465 BEGIN
466
467
468 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
469 l_fnd_log_msg := 'P_DELETE_INTERACTIONS In parameters :'||
470 'p_api_version = '|| p_api_version ||
471 'p_init_msg_list = '|| p_init_msg_list ||
472 'p_commit = '|| p_commit||
473 'p_processing_set_id = '|| p_processing_set_id ||
474 'p_object_type = '|| p_object_type;
475 --dbms_output.put_line(l_fnd_log_msg);
476 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
477 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin', l_fnd_log_msg);
478 END IF;
479
480 l_obj_type_lookup := CONCAT(p_object_type,'-%');
481
482 -- Standard start of API savepoint
483 SAVEPOINT delete_interactions_p;
484
485 -- Standard call to check for call compatibility
486 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
487 l_api_name, g_pkg_name) THEN
488 RAISE fnd_api.g_exc_unexpected_error;
489 END IF;
490
491 -- Initialize message list if p_init_msg_list is set to TRUE
492 IF fnd_api.to_boolean(p_init_msg_list) THEN
493 fnd_msg_pub.initialize;
494 END IF;
495
496 -- Initialize API return status to success
497 x_return_status := fnd_api.g_ret_sts_success;
498 l_return_status := x_return_status;
499
500 OPEN c_jtf_obj_purge_temp_success;
501 FETCH c_jtf_obj_purge_temp_success bulk collect
502 INTO l_incident_ids, l_dummy_col;
503 CLOSE c_jtf_obj_purge_temp_success;
504
505 --Select set of Activities related to object_type
506 --to be deleted if the action item can be deleted
507 --for this object type
508 IF l_incident_ids.COUNT < 1 THEN
509 RAISE ErrNoRecFound;
510 END IF;
511
512 FORALL i IN l_incident_ids.FIRST..l_incident_ids.LAST
513 DELETE
514 FROM JTF_IH_ACTIVITIES
515 WHERE doc_id = l_incident_ids(i)
516 AND doc_ref = p_object_type
517 AND action_item_id
518 IN (SELECT meaning
519 FROM FND_LOOKUP_VALUES
520 WHERE lookup_type = 'JTF_IH_PURGE_OBJ_AI_MAP'
521 AND lookup_code like l_obj_type_lookup
522 AND view_application_id = 0
523 AND security_group_id = 0
524 )
525 RETURNING media_id,interaction_id,activity_id BULK COLLECT
526 INTO l_media_ids,l_int_ids,l_activity_id;
527
528
529 --Logging after deleting activities
530 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
531 l_fnd_log_msg := 'No. of Activities purged = '||SQL%ROWCOUNT;
532 --dbms_output.put_line(l_fnd_log_msg);
533
534 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
535 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_activity',l_fnd_log_msg);
536 END IF;
537
538
539 -- Inserting the deleted activity ID into temp table
540 -- with new Proc Set ID for Notes dependent object processing
541
542 IF l_activity_id.COUNT > 0 THEN
543
544 l_jtf_activity := 'JTF_ACTIVITY';
545
546 SELECT JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
547 INTO l_processing_set_id
548 FROM dual
549 WHERE rownum = 1;
550
551
552 FORALL i IN l_activity_id.FIRST..l_activity_id.LAST
553 INSERT INTO JTF_OBJECT_PURGE_PARAM_TMP
554 (
555 object_id,
556 object_type,
557 processing_set_id,
558 purge_status,
559 purge_error_message
560 )
561 values
562 (
563 l_activity_id(i),
564 l_jtf_activity,
565 l_processing_set_id,
566 null,
567 null
568 );
569
570
571 --Logging after inserting activities
572 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
573 l_fnd_log_msg := 'No. of Activities Inserted into temp table = '||SQL%ROWCOUNT;
574 --dbms_output.put_line(l_fnd_log_msg);
575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
576 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.activity_tmp',l_fnd_log_msg);
577 END IF;
578
579 --Logging before calling Notes Purge API
580 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
581 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
582 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin.purge_notes', 'Calling Notes Purge API for Activities');
583 END IF;
584
585 --Delete Notes related to the activities being purged
586 CAC_NOTE_PURGE_PUB.PURGE_NOTES
587 ( p_api_version => 1.0,
588 p_init_msg_list => p_init_msg_list,
589 p_commit => p_commit,
590 x_return_status => l_return_status,
591 x_msg_data => l_msg_data,
592 x_msg_count => l_msg_count,
593 p_processing_set_id => l_processing_set_id,
594 p_object_type => 'JTF_ACTIVITY'
595 );
596
597 --Logging on return from Notes Purge API
598 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
599 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
600 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end.purge_notes', 'After call to Notes Purge API for Activities');
601 END IF;
602
603 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
604 RAISE FND_API.G_EXC_ERROR;
605 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607 END IF;
608
609 END IF;
610
611 --Delinking activities from SR
612 FORALL i IN l_incident_ids.FIRST..l_incident_ids.LAST
613 UPDATE JTF_IH_ACTIVITIES
614 SET doc_id = l_dummy_col(i),
615 doc_ref = l_dummy_col(i)
616 WHERE doc_id = l_incident_ids(i)
617 AND doc_ref = p_object_type;
618
619
620
621 --Logging after delinking activities
622 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
623 l_fnd_log_msg := 'No. of Activities Delinked = '||SQL%ROWCOUNT;
624 --dbms_output.put_line(l_fnd_log_msg);
625 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
626 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delink_act',l_fnd_log_msg);
627 END IF;
628
629 --Deleting the associated Interactions
630
631 IF l_int_ids.COUNT > 0 THEN
632 FORALL j IN l_int_ids.FIRST..l_int_ids.LAST
633 DELETE
634 FROM JTF_IH_INTERACTIONS
635 WHERE interaction_id = l_int_ids(j)
636 AND
637 NOT EXISTS (SELECT 1
638 FROM JTF_IH_ACTIVITIES
639 WHERE interaction_id = l_int_ids(j))
640 RETURNING interaction_id BULK COLLECT INTO l_inter_int_ids;
641 END IF;
642
643
644 --Logging after deleting interactions
645 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
649 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.del_inter',l_fnd_log_msg);
646 l_fnd_log_msg := 'No. of Interactions purged = '||SQL%ROWCOUNT;
647 --dbms_output.put_line(l_fnd_log_msg);
648 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
650 END IF;
651
652 -- Clean Up Parent and All Child Interactions
653 --in the JTF_IH_INTERACTION_INTERS.
654 IF l_inter_int_ids.COUNT > 0 THEN
655 FORALL j IN l_inter_int_ids.FIRST..l_inter_int_ids.LAST
656 DELETE
657 FROM JTF_IH_INTERACTION_INTERS
658 WHERE (INTERACT_INTERACTION_IDRELATES = l_inter_int_ids(j))
659 OR (INTERACT_INTERACTION_ID = l_inter_int_ids(j));
660 END IF;
661
662
663
664 --Logging after cleaning Interaction Relationships
665 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
666 l_fnd_log_msg := 'No. of Interaction Relationships purged = '||SQL%ROWCOUNT;
667 --dbms_output.put_line(l_fnd_log_msg);
668 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
669 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.del_inter_inter',l_fnd_log_msg);
670 END IF;
671
672 -- Inserting the deleted interaction ID into temp table
673 -- with new Proc Set ID for Notes dependent object processing
674
675 IF l_inter_int_ids.COUNT > 0 THEN
676
677 l_jtf_interaction := 'JTF_INTERACTION';
678
679 SELECT JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
680 INTO l_processing_set_id
681 FROM dual
682 WHERE rownum = 1;
683
684
685 FORALL i IN l_inter_int_ids.FIRST..l_inter_int_ids.LAST
686 INSERT INTO JTF_OBJECT_PURGE_PARAM_TMP
687 (
688 object_id,
689 object_type,
690 processing_set_id,
691 purge_status,
692 purge_error_message
693 )
694 values
695 (
696 l_inter_int_ids(i),
697 l_jtf_interaction,
698 l_processing_set_id,
699 null,
700 null
701 );
702
703
704
705 --Logging after inserting interactions into temp table
706 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
707 l_fnd_log_msg := 'No. of Interactions inserted into temp table = '||SQL%ROWCOUNT;
708 --dbms_output.put_line(l_fnd_log_msg);
709 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
710 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.inter_tmp',l_fnd_log_msg);
711 END IF;
712
713 --Logging before calling Notes Purge API
714 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
715 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
716 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin.purge_notes', 'Calling Notes Purge API for Interactions');
717 END IF;
718
719 --Delete Notes related to the interactions being purged
720 CAC_NOTE_PURGE_PUB.PURGE_NOTES
721 ( p_api_version => 1.0,
722 p_init_msg_list => p_init_msg_list,
723 p_commit => p_commit,
724 x_return_status => l_return_status,
725 x_msg_data => l_msg_data,
726 x_msg_count => l_msg_count,
727 p_processing_set_id => l_processing_set_id,
728 p_object_type => 'JTF_INTERACTION'
729 );
730 --Logging on return from Notes Purge API
731 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
732 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
733 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end.purge_notes', 'After call to Notes Purge API for Activities');
734 END IF;
735 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
736 RAISE FND_API.G_EXC_ERROR;
737 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
739 END IF;
740
741 END IF;
742
743 --
744 -- Delete Media Item If they aren't
745 -- related to other Activities.
746 --
747 IF l_media_ids.COUNT > 0 THEN
748 FORALL j IN l_media_ids.FIRST..l_media_ids.LAST
749 DELETE
750 FROM JTF_IH_MEDIA_ITEMS
751 WHERE media_id IS NOT NULL
752 AND media_id <> fnd_api.g_miss_num
753 AND media_id = l_media_ids(j)
754 AND
755 NOT EXISTS (SELECT 1
756 FROM JTF_IH_ACTIVITIES
757 WHERE MEDIA_ID = l_media_ids(j))
758 RETURNING media_id BULK COLLECT INTO lc_media_ids;
759 END IF;
760
761
762
763
764 --Logging after deleting media items
765 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
766 l_fnd_log_msg := 'No. of Media Items purged = '||SQL%ROWCOUNT;
767 --dbms_output.put_line(l_fnd_log_msg);
768 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
769 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_MI',l_fnd_log_msg);
770 END IF;
771
772 -- And Related to MediaID Media Item LifeCycle.
773 IF lc_media_ids.COUNT > 0 THEN
774 FORALL j IN lc_media_ids.FIRST..lc_media_ids.LAST
775 DELETE
776 FROM JTF_IH_MEDIA_ITEM_LC_SEGS
777 WHERE MEDIA_ID = lc_media_ids(j);
778 END IF;
779
780 IF p_commit = FND_API.G_TRUE THEN
781 COMMIT;
782 END IF;
783
784 --Logging after deleting media life cycle segments
785 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
786 l_fnd_log_msg := 'No. of Media Life-cycle segments purged = '||SQL%ROWCOUNT;
787 --dbms_output.put_line(l_fnd_log_msg);
788 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
792
789 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_MLCS',l_fnd_log_msg);
790 END IF;
791
793
794 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
795 l_fnd_log_msg := 'P_DELETE_INTERACTIONS Out parameters:' ||
796 'x_return_status = '|| x_return_status ||
797 'x_msg_data = '||x_msg_data||
798 'x_msg_count ='||x_msg_count;
799
800 --dbms_output.put_line(l_fnd_log_msg);
801 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
802 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end', l_fnd_log_msg);
803 END IF;
804
805 EXCEPTION
806 WHEN fnd_api.g_exc_error THEN
807 ROLLBACK TO delete_interactions_p;
808 --dbms_output.put_line('FAILURE EXPECTED');
809 x_return_status := fnd_api.g_ret_sts_error;
810 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
811 fnd_msg_pub.count_and_get
812 ( p_count => x_msg_count,
813 p_data => x_msg_data );
814 x_msg_data:=FND_MSG_PUB.Get(p_msg_index => x_msg_count, p_encoded=>'F');
815 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
816 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
817 END IF;
818
819 WHEN fnd_api.g_exc_unexpected_error THEN
820 ROLLBACK TO delete_interactions_p;
821 --dbms_output.put_line('FAILURE UNEXPECTED');
822 x_return_status := fnd_api.g_ret_sts_unexp_error;
823 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
824 fnd_msg_pub.count_and_get
825 ( p_count => x_msg_count,
826 p_data => x_msg_data );
827 x_msg_data := FND_MSG_PUB.Get(p_msg_index=>x_msg_count, p_encoded=>'F');
828 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
829 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
830 END IF;
831
832 WHEN ErrNoRecFound THEN
833 ROLLBACK TO delete_interactions_p;
834 x_return_status := fnd_api.g_ret_sts_success;
835 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
836 fnd_msg_pub.count_and_get
837 ( p_count => x_msg_count,
838 p_data => x_msg_data );
839 x_msg_data:=FND_MSG_PUB.Get(p_msg_index => x_msg_count, p_encoded=>'F');
840 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
841 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
842 END IF;
843
844 WHEN OTHERS THEN
845 ROLLBACK TO delete_interactions_p;
846 --dbms_output.put_line('FAILURE OTHERS');
847 x_return_status := fnd_api.g_ret_sts_unexp_error;
848 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
849 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
850 END IF;
851 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
852 fnd_msg_pub.count_and_get
853 ( p_count => x_msg_count,
854 p_data => x_msg_data );
855 x_msg_data:=FND_MSG_PUB.Get(p_msg_index => x_msg_count, p_encoded=>'F');
856 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
857 'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
858 END IF;
859
860 END P_DELETE_INTERACTIONS;
861
862 END JTF_IH_PURGE;
[Home] [Help]