[Home] [Help]
PACKAGE BODY: APPS.EGO_PUB_HIST_PURGE_PKG
Source
1 PACKAGE BODY ego_pub_hist_purge_pkg AS
2 /* $Header: EGOPPHPB.pls 120.6.12020000.4 2013/04/19 03:23:01 yingyang ship $ */
3 --============ Purge_Publish_History API===============
4 /* This procedure is called by the concurrent program, will be used to delete
5 record from status table in order to purge publish history */
6 PROCEDURE Purge_Publish_History(err_buff IN OUT NOCOPY VARCHAR2,
7 ret_code OUT NOCOPY NUMBER,
8 p_batch_id IN NUMBER,
9 p_target_system_code IN VARCHAR2,
10 p_from_date IN VARCHAR2,
11 p_to_date IN VARCHAR2,
12 p_status_code IN VARCHAR2,
13 p_published_by IN NUMBER,
14 p_entity_type IN VARCHAR2)
15
16 IS
17
18 TYPE l_batch_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE l_status_sys_tab IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
20 l_dynamic_sql VARCHAR2(2000) := NULL;
21 l_batch_sql VARCHAR2(2000) := NULL;
22 l_where_clause VARCHAR2(2000) := NULL;
23 l_count_bind_param NUMBER := 1;
24 l_using_clause VARCHAR2(500) := NULL;
25
26 l_from_date VARCHAR2(1000) := NULL;
27 l_to_date VARCHAR2(1000) := NULL;
28
29 l_batch_tab l_batch_table;
30 l_batch_tab1 l_batch_table;
31 l_batch_tab2 l_batch_table;
32 l_batch_tab3 l_batch_table;
33 l_status_syss l_status_sys_tab;
34 l_delete_batch_hdr VARCHAR2(1000) := NULL;
35 l_delete_batch_param VARCHAR2(1000) := NULL;
36 l_delete_entity_obj VARCHAR2(1000) := NULL;
37 l_delete_batch_status VARCHAR2(1000) := NULL;
38 l_delete_batch_system VARCHAR2(1000) := NULL;
39 l_delete_batch_system1 VARCHAR2(1000) := NULL;
40 l_delete_batch_status1 VARCHAR2(1000) := NULL;
41 l_delete_batch_status2 VARCHAR2(1000) := NULL;
42 l_delete_batch_status3 VARCHAR2(1000) := NULL;
43 l_batch_id NUMBER := NULL;
44 l_pub_dt VARCHAR2(100) := NULL;
45 l_pub_dt_to VARCHAR2(100) := NULL;
46 l_system_flag BOOLEAN := false;
47 l_status_flag BOOLEAN := false;
48 l_batch_sql1 VARCHAR2(2000) := NULL;
49 l_batch_sql2 VARCHAR2(2000) := NULL;
50 l_batch_sql3 VARCHAR2(2000) := NULL;
51 l_where_clause1 VARCHAR2(2000) := NULL;
52 l_where_clause2 VARCHAR2(2000) := NULL;
53 l_where_clause3 VARCHAR2(2000) := NULL;
54 l_batch_param BOOLEAN := FALSE;
55 l_status_param BOOLEAN := FALSE;
56 l_system_param BOOLEAN := FALSE;
57 l_status_sel_sql VARCHAR2(1000) := NULL;
58 l_system_sel_sql VARCHAR2(1000) := NULL;
59 l_status_sel_count NUMBER := 0;
60 l_system_sel_count NUMBER := 0;
61 l_exec_status BOOLEAN := TRUE;
62 l_exec_sys BOOLEAN := TRUE;
63 l_status_sys_seq VARCHAR2(1000) := NULL;
64 l_sys_frm_status VARCHAR2(1000) := NULL;
65
66 BEGIN
67
68 /* Change into Date format for passed in Date Range*/
69 l_from_date := FND_DATE.canonical_to_date(p_from_date);
70 l_to_date := FND_DATE.canonical_to_date(p_to_date);
71
72 l_batch_sql := ' SELECT DISTINCT hdr.Batch_id
73 FROM EGO_PUB_BAT_HDR_B hdr, EGO_PUB_BAT_STATUS_B status
74 WHERE hdr.batch_id= status.batch_id ';
75
76 l_batch_sql1 := ' select distinct Batch_id from ego_pub_bat_hdr_b where ';
77 l_batch_sql2 := ' select distinct BATCH_ID from EGO_PUB_BAT_STATUS_B where ';
78 l_batch_sql3 := ' select distinct BATCH_ID from EGO_PUB_BAT_SYSTEMS_B where ';
79 l_status_sys_seq := ' select SYSTEM_CODE from EGO_PUB_BAT_STATUS_B where ';
80
81 l_status_sel_sql := ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = :1 ';
82 l_system_sel_sql := ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = :1 ';
83
84 l_delete_batch_hdr := 'Delete FROM ego_pub_bat_hdr_b WHERE batch_id = :1 ';
85 l_delete_batch_param := 'Delete FROM Ego_Pub_Bat_Params_B WHERE type = 1 and type_id = :1 '; --Type is 1 for Batch and 2 for System
86 l_delete_entity_obj := 'Delete FROM Ego_Pub_Bat_Ent_Objs_B WHERE batch_id = :1 ';
87 l_delete_batch_status := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 ';
88 l_delete_batch_status1 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and STATUS_CODE = :2 ';
89 l_delete_batch_system := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 ';
90 l_delete_batch_system1 := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 ';
91 l_delete_batch_status2 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 and STATUS_CODE = :3 ';
92 l_delete_batch_status3 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 ';
93
94 fnd_file.put_line(fnd_file.Log,
95 ' Processing data to delete record based on input ');
96
97 /*Case when no Input has been passed, No action will be taken by this API*/
98 IF (p_batch_id IS NULL AND p_target_system_code IS NULL AND
99 p_from_date IS NULL AND p_to_date IS NULL AND p_status_code IS NULL AND
100 p_published_by IS NULL AND p_entity_type IS NULL) THEN
101 -- If all parameters contains null value then send useful message to the log file.
102 fnd_file.put_line(fnd_file.Log,
103 'No record has been deleted because all the input parameters contains null value. User have to enter value for atleast one of the input paramters to delete data');
104 RETURN;
105 END IF;
106
107 /* Validating From date & To Date as both are required */
108 IF ((p_from_date IS NOT NULL AND p_to_date IS NULL) OR
109 (p_from_date IS NULL AND p_to_date IS NOT NULL)) THEN
110 fnd_file.put_line(fnd_file.Log,
111 ' Date range is not provided to delete record. ');
112 RETURN;
113 END IF;
114
115 /*Case when batch_id is passed as input parameter to delete publish history*/
116 IF (p_batch_id IS NOT NULL) THEN
117 fnd_file.put_line(fnd_file.Log,
118 ' Control entrered into Batch ID varification block with : ' ||
119 p_batch_id);
120 l_where_clause := l_where_clause || ' AND hdr.batch_id = ' ||
121 p_batch_id;
122
123 IF l_batch_param THEN
124 l_where_clause1 := l_where_clause1 || ' AND batch_id = ' ||
125 p_batch_id;
126 l_where_clause2 := l_where_clause2 || ' AND batch_id = ' ||
127 p_batch_id;
128 l_where_clause3 := l_where_clause3 || ' AND batch_id = ' ||
129 p_batch_id;
130 ELSE
131 l_where_clause1 := l_where_clause1 || ' batch_id = ' || p_batch_id;
132 l_where_clause2 := l_where_clause2 || ' batch_id = ' || p_batch_id;
133 l_where_clause3 := l_where_clause3 || ' batch_id = ' || p_batch_id;
134 l_batch_param := TRUE;
135 l_status_param := TRUE;
136 l_system_param := TRUE;
137 END IF;
138
139 END IF;
140
141 /*Case when target_system_code is passed as input parameter to delete publish history*/
142 IF (p_target_system_code IS NOT NULL) THEN
143
144 fnd_file.put_line(fnd_file.Log,
145 ' Control entrered into Target System varification block with : ' ||
146 p_target_system_code);
147 l_where_clause := l_where_clause || ' AND status.system_code = ''' ||
148 p_target_system_code || '''';
149
150 IF l_status_param THEN
151 l_where_clause2 := l_where_clause2 || ' AND system_code = ''' ||
152 p_target_system_code || '''';
153 l_where_clause3 := l_where_clause3 || ' AND system_code = ''' ||
154 p_target_system_code || '''';
155 ELSE
156 l_where_clause2 := l_where_clause2 || ' system_code = ''' ||
157 p_target_system_code || '''';
158 l_where_clause3 := l_where_clause3 || ' system_code = ''' ||
159 p_target_system_code || '''';
160 l_status_param := TRUE;
161 l_system_param := TRUE;
162 END IF;
163
164 l_system_flag := TRUE;
165 END IF;
166
167 /* Taking the range of dates for purging */
168 IF (p_from_date IS NOT NULL AND p_to_date IS NOT NULL) THEN
169
170 fnd_file.put_line(fnd_file.Log,
171 ' Control entrered into Date range varification block from : ' ||
172 l_from_date || ' to ' || l_to_date);
173 SELECT To_Char(To_Date(l_from_date, 'dd-mm-yy hh24:mi:ss'),
174 'dd-mon-yy hh24:mi:ss')
175 INTO l_pub_dt
176 FROM dual;
177 SELECT To_Char(To_Date(l_to_date, 'dd-mm-yy hh24:mi:ss'),
178 'dd-mon-yy hh24:mi:ss')
179 INTO l_pub_dt_to
180 FROM dual;
181 l_where_clause := l_where_clause ||
182 ' AND hdr.batch_creation_date >= To_Date( ''' ||
183 l_pub_dt || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
184 l_where_clause := l_where_clause ||
185 ' AND hdr.batch_creation_date <= To_Date( ''' ||
186 l_pub_dt_to || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
187
188 IF l_batch_param THEN
189 l_where_clause1 := l_where_clause1 ||
190 ' AND batch_creation_date >= To_Date( ''' ||
191 l_pub_dt || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
192 l_where_clause1 := l_where_clause1 ||
193 ' AND batch_creation_date <= To_Date( ''' ||
194 l_pub_dt_to || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
195 ELSE
196 l_where_clause1 := l_where_clause1 ||
197 ' batch_creation_date >= To_Date( ''' ||
198 l_pub_dt || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
199 l_where_clause1 := l_where_clause1 ||
200 ' AND batch_creation_date <= To_Date( ''' ||
201 l_pub_dt_to || ''' ,''dd-mon-yy hh24:mi:ss''' || ' )';
202 l_batch_param := TRUE;
203 END IF;
204
205 END IF;
206
207 /*Case when publish status is passed as input parameter to delete publish history*/
208 IF (p_status_code IS NOT NULL) THEN
209 fnd_file.put_line(fnd_file.Log,
210 ' Control entrered into status varification block with : ' ||
211 p_status_code);
212 l_where_clause := l_where_clause || ' AND status.status_code = ''' ||
213 p_status_code || '''';
214
215 IF l_status_param THEN
216 l_where_clause2 := l_where_clause2 || ' AND status_code = ''' ||
217 p_status_code || '''';
218 ELSE
219 l_where_clause2 := l_where_clause2 || ' status_code = ''' ||
220 p_status_code || '''';
221 l_status_param := TRUE;
222 END IF;
223
224 l_status_flag := TRUE;
225 END IF;
226
227 /*Case when publisher is passed as input parameter to delete publish history*/
228 IF (p_published_by IS NOT NULL) THEN
229 fnd_file.put_line(fnd_file.Log,
230 ' Control entrered into Publiched by varification block with : ' ||
231 p_published_by);
232 l_where_clause := l_where_clause || ' AND hdr.PUBLISHED_BY = ' ||
233 p_published_by;
234
235 IF l_batch_param THEN
236 l_where_clause1 := l_where_clause1 || ' AND PUBLISHED_BY = ' ||
237 p_published_by;
238 ELSE
239 l_where_clause1 := l_where_clause1 || ' PUBLISHED_BY = ' ||
240 p_published_by;
241 l_batch_param := TRUE;
242 END IF;
243
244 END IF;
245
246 IF (p_entity_type IS NOT NULL) THEN
247 fnd_file.put_line(fnd_file.Log,
248 ' Control entrered into Entity type by varification block with : ' ||
249 p_entity_type);
250 l_where_clause := l_where_clause || ' AND hdr.batch_type = ' ||
251 p_entity_type;
252
253 IF l_batch_param THEN
254 l_where_clause1 := l_where_clause1 || ' AND batch_type = ' ||
255 p_entity_type;
256 ELSE
257 l_where_clause1 := l_where_clause1 || ' batch_type = ' ||
258 p_entity_type;
259 l_batch_param := TRUE;
260 END IF;
261
262 END IF;
263
264 IF (l_where_clause1 IS NOT NULL) THEN
265 l_batch_sql1 := l_batch_sql1 || l_where_clause1;
266 fnd_file.put_line(fnd_file.Log, ' l_batch_sql1 = ' || l_batch_sql1);
267 EXECUTE IMMEDIATE l_batch_sql1 BULK COLLECT
268 INTO l_batch_tab1;
269 END IF;
270
271 IF (l_where_clause2 IS NOT NULL) THEN
272 l_batch_sql2 := l_batch_sql2 || l_where_clause2;
273 fnd_file.put_line(fnd_file.Log, ' l_batch_sql2 = ' || l_batch_sql2);
274 EXECUTE IMMEDIATE l_batch_sql2 BULK COLLECT
275 INTO l_batch_tab2;
276 END IF;
277
278 IF (l_where_clause3 IS NOT NULL) THEN
279 l_batch_sql3 := l_batch_sql3 || l_where_clause3;
280 fnd_file.put_line(fnd_file.Log, ' l_batch_sql3 = ' || l_batch_sql3);
281 EXECUTE IMMEDIATE l_batch_sql3 BULK COLLECT
282 INTO l_batch_tab3;
283 END IF;
284
285 IF (l_batch_tab3.count > l_batch_tab2.count) THEN
286 fnd_file.put_line(fnd_file.Log,
287 'getting the batch id from Systems table');
288 EXECUTE IMMEDIATE l_batch_sql3 BULK COLLECT
289 INTO l_batch_tab;
290 ELSIF (l_batch_tab3.count = 0 AND l_batch_tab2.count = 0 AND
291 l_batch_tab1.Count <> 0) THEN
292 fnd_file.put_line(fnd_file.Log,
293 'getting the batch id from Batch header table');
294 EXECUTE IMMEDIATE l_batch_sql1 BULK COLLECT
295 INTO l_batch_tab;
296 ELSE
297 l_batch_sql := l_batch_sql || l_where_clause;
298 fnd_file.put_line(fnd_file.Log, ' l_batch_sql =' || l_batch_sql);
299 EXECUTE IMMEDIATE l_batch_sql BULK COLLECT
300 INTO l_batch_tab;
301 END IF;
302
303 fnd_file.put_line(fnd_file.Log,
304 ' before final execution of deleting the data from the tables');
308 'Total Count of batch ID : ' || l_batch_tab.Count);
305 -- Once we get batch_id for passed in parameter, we will delete data from all tables.
306
307 fnd_file.put_line(fnd_file.Log,
309
310 IF l_batch_tab.Count > 0 THEN
311 FOR i IN l_batch_tab.FIRST .. l_batch_tab.LAST LOOP
312 fnd_file.put_line(fnd_file.Log,
313 ' Enetered FOR loop for ' || i || ' time');
314 l_batch_id := l_batch_tab(i);
315
316 EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = ' ||
317 l_batch_id
318 INTO l_status_sel_count;
319 EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = ' ||
320 l_batch_id
321 INTO l_system_sel_count;
322
323 fnd_file.put_line(fnd_file.Log,
324 'ststus table count is ' || l_status_sel_count);
325 fnd_file.put_line(fnd_file.Log,
326 'system table count is ' || l_system_sel_count);
327
328 IF (l_where_clause2 IS NOT NULL) THEN
329 fnd_file.put_line(fnd_file.Log,
330 ' Getting systems based on given status for deleting systems table');
331 l_where_clause2 := l_where_clause2 || ' AND BATCH_ID = ' ||
332 l_batch_id;
333 l_status_sys_seq := l_status_sys_seq || l_where_clause2;
334 fnd_file.put_line(fnd_file.Log,
335 'l_status_sys_seq = ' || l_status_sys_seq);
336 EXECUTE IMMEDIATE l_status_sys_seq BULK COLLECT
337 INTO l_status_syss;
338 fnd_file.put_line(fnd_file.Log,
339 'l_status_syss count = ' || l_status_syss.count);
340
341 IF l_status_syss.Count > 0 THEN
342 FOR j IN l_status_syss.FIRST .. l_status_syss.LAST LOOP
343 l_sys_frm_status := l_status_syss(j);
344 fnd_file.put_line(fnd_file.Log,
345 'deleting system ' || l_sys_frm_status ||
346 ' from systems table for the batch id = ' ||
347 l_batch_id);
348 EXECUTE IMMEDIATE l_delete_batch_system1
349 USING l_batch_id, l_sys_frm_status;
350 END LOOP;
351 END IF;
352 END IF;
353
354 IF (l_status_sel_count >= 1 AND l_status_flag AND l_system_flag) THEN
355 fnd_file.put_line(fnd_file.Log,
356 'Given status code and systems. There are other records along with the given input, so deleting only the provided input from status table');
357 EXECUTE IMMEDIATE l_delete_batch_status2
358 USING l_batch_id, p_target_system_code, p_status_code;
359 l_exec_status := FALSE;
360 ELSIF (l_status_sel_count >= 1 AND l_status_flag) THEN
361 fnd_file.put_line(fnd_file.Log,
362 'There are other records along with the given status code, so deleting only the provided input from status table ');
363 EXECUTE IMMEDIATE l_delete_batch_status1
364 USING l_batch_id, p_status_code;
365 l_exec_status := FALSE;
366 ELSIF (l_status_sel_count >= 1 AND l_system_flag) THEN
367 fnd_file.put_line(fnd_file.Log,
368 'There are other records along with the given system code, so deleting only the provided input from status table');
369 EXECUTE IMMEDIATE l_delete_batch_status3
370 USING l_batch_id, p_target_system_code;
371 l_exec_status := FALSE;
372 END IF;
373
374 IF (l_system_sel_count >= 1 AND l_system_flag) THEN
375 fnd_file.put_line(fnd_file.Log,
376 'There are other records along with the given system code, so deleting only the provided input from systems table');
377 EXECUTE IMMEDIATE l_delete_batch_system1
378 USING l_batch_id, p_target_system_code;
379 l_exec_sys := FALSE;
380 END IF;
381
382 IF (l_exec_status AND l_exec_sys) THEN
383 fnd_file.put_line(fnd_file.Log,
384 'deleting compltely based on Batch ID as there are no other data in the status and systems table');
385 EXECUTE IMMEDIATE l_delete_batch_hdr
386 USING l_batch_id;
387 EXECUTE IMMEDIATE l_delete_batch_param
388 USING l_batch_id;
389 EXECUTE IMMEDIATE l_delete_entity_obj
390 USING l_batch_id;
391 EXECUTE IMMEDIATE l_delete_batch_status
392 USING l_batch_id;
393 EXECUTE IMMEDIATE l_delete_batch_system
394 USING l_batch_id;
395
396 ELSE
397 EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = ' ||
398 l_batch_id
399 INTO l_status_sel_count;
400 EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = ' ||
401 l_batch_id
402 INTO l_system_sel_count;
403
404 IF (l_status_sel_count = 0 AND l_system_sel_count = 0) THEN
405 EXECUTE IMMEDIATE l_delete_batch_hdr
406 USING l_batch_id;
407 EXECUTE IMMEDIATE l_delete_batch_param
408 USING l_batch_id;
409 EXECUTE IMMEDIATE l_delete_entity_obj
410 USING l_batch_id;
411 END IF;
412 END IF;
413
414 fnd_file.put_line(fnd_file.Log,
415 'records are deleted based on the given parameters ');
416 END LOOP;
417 ELSE
418 fnd_file.put_line(fnd_file.Log,
419 'No records are deleted as the count of batch ID is 0');
420
421 END IF;
422
426
423 --After deletion of publish history sending message to log file
424
425 Return;
427 EXCEPTION
428 WHEN OTHERS THEN
429 err_buff := SQLERRM;
430 --Sending message to log file in case of runtime exception occurs.
431 fnd_file.put_line(fnd_file.Log,
432 'Records are not deleted due to runtime exception ' ||
433 SQLERRM);
434 END Purge_Publish_History;
435 /* End Purge_Publish_History API*/
436
437 FUNCTION SPLIT(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2) RETURN ARRAY IS
438 J NUMBER := 0;
439 I NUMBER := 1;
440 LEN NUMBER := 0;
441 LEN1 NUMBER := 0;
442 STR VARCHAR2(4000);
443 STR_SPLIT ARRAY := ARRAY();
444 BEGIN
445 LEN := LENGTH(P_STR);
446 LEN1 := LENGTH(P_DELIMITER);
447
448 WHILE J < LEN LOOP
449 J := INSTR(P_STR, P_DELIMITER, I);
450
451 IF J = 0 THEN
452 J := LEN;
453 STR := SUBSTR(P_STR, I);
454 STR_SPLIT.EXTEND;
455 STR_SPLIT(STR_SPLIT.COUNT) := STR;
456
457 IF I >= LEN THEN
458 EXIT;
459 END IF;
460 ELSE
461 STR := SUBSTR(P_STR, I, J - I);
462 I := J + LEN1;
463 STR_SPLIT.EXTEND;
464 STR_SPLIT(STR_SPLIT.COUNT) := STR;
465 END IF;
466 END LOOP;
467
468 RETURN STR_SPLIT;
469 END SPLIT;
470 PROCEDURE Purge_Publish_History_By_Batch(ERRBUF IN OUT NOCOPY VARCHAR2,
471 RETCODE OUT NOCOPY VARCHAR2,
472 p_batch_id_list varchar2,
473 p_purge_days IN NUMBER,
474 p_batch_name1 varchar2,
475 p_batch_name2 varchar2,
476 p_batch_name3 varchar2,
477 p_batch_name4 varchar2,
478 p_batch_name5 varchar2
479 )
480
481 IS
482 l_batch_id_list VARCHAR2(2000) := p_batch_id_list;
483 l_batch_id_list_batchname VARCHAR2(2000) :=NULL;
484 Processing_Error EXCEPTION;
485 Batch_ID_NOT_EXISTS_Error EXCEPTION;
486 TYPE number_tab_tp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
487 l_batch_id NUMBER := NULL;
488 l_batch_id_temp NUMBER := NULL;
489 l_session_id NUMBER := NULL;
490 l_batch_count NUMBER := 0;
491 STR_SPLIT ARRAY := ARRAY();
492 --fnd_profile.value('EGO_ENABLE_P4T')
493 l_set_is_purging VARCHAR2(2000) := NULL;
494 l_error VARCHAR2(2000) := NULL;
495 l_error_code NUMBER := NULL;
496 l_getsessionid_batch VARCHAR2(2000) := NULL;
497 l_getsessionid_list_count VARCHAR2(2000) := NULL;
498 l_getsessionid_list VARCHAR2(2000) := NULL;
499 l_batch_query_status VARCHAR2(2000) := NULL;
500 l_update_batch_status VARCHAR2(2000) := NULL;
501 l_getsessionid_batch_count VARCHAR2(2000) := NULL;
502 l_batchid_from_batchname VARCHAR2(2000) := NULL;
503 l_batchid_from_batchname_count VARCHAR2(2000) := NULL;
504 l_from_date VARCHAR2(1000) := NULL;
505 l_to_date VARCHAR2(1000) := NULL;
506 l_where_clause VARCHAR2(2000) := NULL;
507 l_pub_dt VARCHAR2(100) := NULL;
508 l_pub_dt_to VARCHAR2(100) := NULL;
509 l_delete_batch_hdr VARCHAR2(1000) := NULL;
510 l_delete_batch_param VARCHAR2(1000) := NULL;
511 l_delete_entity_obj VARCHAR2(1000) := NULL;
512 l_delete_batch_status VARCHAR2(1000) := NULL;
513 l_delete_batch_system VARCHAR2(1000) := NULL;
514 l_delete_payload1 VARCHAR2(1000) := NULL;
515 l_delete_payload2 VARCHAR2(1000) := NULL;
516 l_delete_payload3 VARCHAR2(1000) := NULL;
517 l_delete_payload4 VARCHAR2(1000) := NULL;
518 l_delete_payload5 VARCHAR2(1000) := NULL;
519 l_delete_payload6 VARCHAR2(1000) := NULL;
520 l_delete_payload7 VARCHAR2(1000) := NULL;
521 l_wflag Boolean := false;
522 l_sessionid_tab_list number_tab_tp;
523 l_batch_list_table ARRAY2;
524 l_publish_status_table ARRAY2;
525 l_batch_status_temp VARCHAR2(2000) := NULL;
526 l_batch_status_update_temp VARCHAR2(2000) := NULL;
527 l_batch_from_purgedays VARCHAR2(2000) := NULL;
528 l_batchcount_from_purgedays VARCHAR2(2000) := NULL;
529 BEGIN
530 l_batch_id_list := p_batch_id_list;
531
532 l_batchcount_from_purgedays := 'select count(1) from ego_pub_bat_hdr_b where creation_date +' ||
533 p_purge_days || ' <= sysdate';
534 l_batch_from_purgedays := 'select batch_id from ego_pub_bat_hdr_b where creation_date +' ||
535 p_purge_days || ' <= sysdate';
536 l_set_is_purging := 'update EGO_PUB_BAT_HDR_B set Is_PURGING=''Y'' where Batch_id= :1';
537 l_getsessionid_batch := ' SELECT distinct session_id FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME=''BATCHID'' AND NUMERIC_VALUE = :1';
538 l_getsessionid_list := ' SELECT distinct session_id FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME!=''BATCHID'' AND creation_date +' ||
539 p_purge_days || ' <= sysdate';
540 l_getsessionid_batch_count := ' SELECT count(1) FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME=''BATCHID'' AND NUMERIC_VALUE = :1 ';
541 l_getsessionid_list_count := ' SELECT count(1) FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME!=''BATCHID'' AND creation_date +' ||
545 l_batch_query_status := 'select status_code from EGO_PUB_BAT_STATUS_B where rownum=1';
542 p_purge_days || ' <= sysdate';
543 l_batchid_from_batchname :='select batch_id from ego_pub_bat_hdr_b where batch_name= :1';
544 l_batchid_from_batchname_count :='select count(1) from ego_pub_bat_hdr_b where batch_name= :1';
546 l_update_batch_status := 'update EGO_PUB_BAT_STATUS_B set status_code= :1 where Batch_id= :2';
547 l_delete_batch_hdr := 'Delete FROM ego_pub_bat_hdr_b WHERE batch_id = :1 ';
548 l_delete_batch_param := 'Delete FROM Ego_Pub_Bat_Params_B WHERE type = 1 and type_id = :1 '; --Type is 1 for Batch and 2 for System
549 l_delete_entity_obj := 'Delete FROM Ego_Pub_Bat_Ent_Objs_B WHERE batch_id = :1 ';
550 l_delete_batch_status := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 ';
551 l_delete_batch_system := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 ';
552 l_delete_payload1 := 'Delete FROM EGO_PUB_WS_ERRORS WHERE session_id = :1 ';
553 l_delete_payload2 := 'Delete FROM EGO_PUB_WS_FLAT_RECS WHERE session_id = :1 ';
554 l_delete_payload3 := 'Delete FROM EGO_PUB_WS_INPUT_IDENTIFIERS WHERE session_id = :1 ';
555 l_delete_payload4 := 'Delete FROM EGO_PUB_WS_OUTPUT WHERE session_id = :1 ';
556 l_delete_payload5 := 'Delete FROM EGO_PUB_WS_PARAMS WHERE session_id = :1 ';
557 l_delete_payload6 := 'Delete FROM EGO_ODI_WS_ENTITIES WHERE session_id = :1 ';
558 l_delete_payload7 := 'Delete FROM EGO_PUB_WS_CONFIG WHERE session_id = :1 ';
559 --if l_batch_id_list is not null, ignore p_purge_days,else get batch id list from p_purge_days
560
561 if (l_batch_id_list is NULL) then
562 if (p_purge_days is not NULL) then
563 EXECUTE IMMEDIATE l_batchcount_from_purgedays
564 INTO l_batch_count;
565
566 if l_batch_count > 0 then
567 EXECUTE IMMEDIATE l_batch_from_purgedays BULK COLLECT
568 INTO l_batch_list_table;
569 FOR i IN l_batch_list_table.FIRST .. l_batch_list_table.LAST LOOP
570 if (l_batch_id_list is NULL) then
571 l_batch_id_list := l_batch_list_table(i);
572 else
573 l_batch_id_list := l_batch_id_list || ',' ||
574 l_batch_list_table(i);
575
576 end if;
577 END LOOP;
578 END if;
579 else
580 --get batch id list from batch name
581 EXECUTE IMMEDIATE l_batchid_from_batchname_count
582 INTO l_batch_count using p_batch_name1;
583
584 if l_batch_count > 0 then
585 EXECUTE IMMEDIATE l_batchid_from_batchname
586 INTO l_batch_id_temp using p_batch_name1;
587
588 if (l_batch_id_list_batchname is NULL) then
589 l_batch_id_list_batchname :=l_batch_id_temp;
590 else
591 l_batch_id_list_batchname := l_batch_id_list_batchname || ',' ||
592 l_batch_id_temp;
593 l_wflag := true;
594 end if;
595 else
596 ERRBUF := ERRBUF || ' ' || ' Batch Name1:' || p_batch_name1 ||
597 ' does not exist!';
598 RETCODE := '1';
599
600 end if;
601
602 EXECUTE IMMEDIATE l_batchid_from_batchname_count
603 INTO l_batch_count using p_batch_name2;
604
605 if l_batch_count > 0 then
606 EXECUTE IMMEDIATE l_batchid_from_batchname
607 INTO l_batch_id_temp using p_batch_name2;
608
609 if (l_batch_id_list_batchname is NULL) then
610 l_batch_id_list_batchname :=l_batch_id_temp;
611 else
612 l_batch_id_list_batchname := l_batch_id_list_batchname || ',' ||
613 l_batch_id_temp;
614
615 end if;
616 l_wflag := true;
617 else
618 ERRBUF := ERRBUF || ' ' || ' Batch Name2:' || p_batch_name2 ||
619 ' does not exist!';
620 RETCODE := '1';
621
622 end if;
623
624 EXECUTE IMMEDIATE l_batchid_from_batchname_count
625 INTO l_batch_count using p_batch_name3;
626
627 if l_batch_count > 0 then
628 EXECUTE IMMEDIATE l_batchid_from_batchname
629 INTO l_batch_id_temp using p_batch_name3;
630
631 if (l_batch_id_list_batchname is NULL) then
632 l_batch_id_list_batchname :=l_batch_id_temp;
633 else
634 l_batch_id_list_batchname := l_batch_id_list_batchname || ',' ||
635 l_batch_id_temp;
636
637 end if;
638 l_wflag := true;
639 else
640 ERRBUF := ERRBUF || ' ' || ' Batch Name3:' || p_batch_name3 ||
641 ' does not exist!';
642 RETCODE := '1';
643
644 end if;
645
646 EXECUTE IMMEDIATE l_batchid_from_batchname_count
647 INTO l_batch_count using p_batch_name4;
648
649 if l_batch_count > 0 then
650 EXECUTE IMMEDIATE l_batchid_from_batchname
651 INTO l_batch_id_temp using p_batch_name4;
652
653 if (l_batch_id_list_batchname is NULL) then
654 l_batch_id_list_batchname :=l_batch_id_temp;
655 else
656 l_batch_id_list_batchname := l_batch_id_list_batchname || ',' ||
657 l_batch_id_temp;
658
659 end if;
660 l_wflag := true;
661 else
662 ERRBUF := ERRBUF || ' ' || ' Batch Name4:' || p_batch_name4 ||
663 ' does not exist!';
664 RETCODE := '1';
665
666 end if;
667
668 EXECUTE IMMEDIATE l_batchid_from_batchname_count
669 INTO l_batch_count using p_batch_name5;
670
671 if l_batch_count > 0 then
672 EXECUTE IMMEDIATE l_batchid_from_batchname
676 l_batch_id_list_batchname :=l_batch_id_temp;
673 INTO l_batch_id_temp using p_batch_name5;
674
675 if (l_batch_id_list_batchname is NULL) then
677 else
678 l_batch_id_list_batchname := l_batch_id_list_batchname || ',' ||
679 l_batch_id_temp;
680
681 end if;
682 l_wflag := true;
683 else
684 ERRBUF := ERRBUF || ' ' || ' Batch Name5:' || p_batch_name5 ||
685 ' does not exist!';
686 RETCODE := '1';
687
688 END if;
689 if(l_wflag = true) then
690 ERRBUF := NULL;
691 RETCODE := '0';
692 end if;
693 end if;
694
695 end if;
696
697 if(l_batch_id_list is NULL AND p_purge_days is NULL ) then
698 l_batch_id_list:=l_batch_id_list_batchname;
699 end if;
700
701 --end if;
702 STR_SPLIT := SPLIT(l_batch_id_list, ',');
703
704
705 FOR I IN 1 .. STR_SPLIT.COUNT LOOP
706
707 begin
708
709 l_batch_id := to_number(STR_SPLIT(I));
710 l_batch_list_table(I) := l_batch_id;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 ERRBUF := ERRBUF || ' ' || ' ' || STR_SPLIT(I) ||
715 ' is Not Number!';
716 RETCODE := '1';
717 continue;
718 end;
719
720 l_batch_id := STR_SPLIT(I);
721
722
723 --need to verify if batch is is valid , if not warning.
724 EXECUTE IMMEDIATE 'select count(1) from ego_pub_bat_hdr_b where batch_id=' ||
725 l_batch_id
726 INTO l_batch_count;
727
728 if (l_batch_count = 0) then
729 -- raise Batch_ID_NOT_EXISTS_Error;
730
731 ERRBUF := ERRBUF || ' ' || ' Batch ID:' || l_batch_id ||
732 ' does not exist!';
733 RETCODE := '1';
734 continue;
735 else
736
737 --not delete when testing
738 EXECUTE IMMEDIATE l_delete_batch_hdr
739 USING l_batch_id;
740 EXECUTE IMMEDIATE l_delete_batch_param
741 USING l_batch_id;
742 EXECUTE IMMEDIATE l_delete_entity_obj
743 USING l_batch_id;
744 EXECUTE IMMEDIATE l_delete_batch_status
745 USING l_batch_id;
746 EXECUTE IMMEDIATE l_delete_batch_system
747 USING l_batch_id;
748
749
750 end if;
751
752 --delete batch mode
753 if (l_batch_id_list is not null) then
754 --if it has batch id list
755 EXECUTE IMMEDIATE l_getsessionid_batch_count
756 INTO l_batch_count
757 USING l_batch_id;
758
759 if l_batch_count > 0 then
760 EXECUTE IMMEDIATE l_getsessionid_batch BULK COLLECT
761 INTO l_sessionid_tab_list
762 USING l_batch_id;
763
764 if (l_sessionid_tab_list.COUNT > 0) then
765 FOR i IN l_sessionid_tab_list.FIRST .. l_sessionid_tab_list.LAST LOOP
766 l_session_id := l_sessionid_tab_list(i);
767
768 EXECUTE IMMEDIATE l_delete_payload1
769 USING l_session_id;
770 EXECUTE IMMEDIATE l_delete_payload2
771 USING l_session_id;
772 EXECUTE IMMEDIATE l_delete_payload3
773 USING l_session_id;
774 EXECUTE IMMEDIATE l_delete_payload4
775 USING l_session_id;
776 EXECUTE IMMEDIATE l_delete_payload5
777 USING l_session_id;
778 EXECUTE IMMEDIATE l_delete_payload6
779 USING l_session_id;
780 EXECUTE IMMEDIATE l_delete_payload7
781 USING l_session_id;
782
783 END LOOP;
784 end if;
785 END if;
786 end if; --if it has batch id list
787 --end delete batch mode
788 --LIST MODE
789
790 if (l_batch_id_list is null AND p_purge_days is not NULL) then
791 EXECUTE IMMEDIATE l_getsessionid_list_count
792 INTO l_batch_count;
793
794 -- if l_batch_count > 0 then
795 EXECUTE IMMEDIATE l_getsessionid_list BULK COLLECT
796 INTO l_sessionid_tab_list;
797 if (l_sessionid_tab_list.COUNT > 0) then
798 FOR i IN l_sessionid_tab_list.FIRST .. l_sessionid_tab_list.LAST LOOP
799 l_session_id := l_sessionid_tab_list(i);
800
801 EXECUTE IMMEDIATE l_delete_payload1
802 USING l_session_id;
803 EXECUTE IMMEDIATE l_delete_payload2
804 USING l_session_id;
805 EXECUTE IMMEDIATE l_delete_payload3
806 USING l_session_id;
807 EXECUTE IMMEDIATE l_delete_payload4
808 USING l_session_id;
809 EXECUTE IMMEDIATE l_delete_payload5
810 USING l_session_id;
811 EXECUTE IMMEDIATE l_delete_payload6
812 USING l_session_id;
813 EXECUTE IMMEDIATE l_delete_payload7
814 USING l_session_id;
815
816 END LOOP;
817 end if;
818 end if;
819
820 --end delete list batch mode
821
822 END LOOP;
823
824
825 COMMIT;
826 EXCEPTION
827
828 WHEN OTHERS THEN
829 ERRBUF := SQLERRM;
830 RETCODE := FND_API.G_RET_STS_ERROR;
831 --Sending message to log file in case of runtime exception occurs.
832
833
834 END Purge_Publish_History_By_Batch;
835 END ego_pub_hist_purge_pkg;