1 PACKAGE BODY MSD_PURGE_LEG AS
2 /* $Header: MSDPURB.pls 120.3 2011/12/07 12:28:25 mpmurali ship $ */
3
4
5 v_batch_size NUMBER ;
6 v_debug BOOLEAN := TRUE;
7 v_applsys_schema VARCHAR2(32);
8 v_program_status NUMBER := G_SUCCESS;
9
10
11
12 /*========================================================================================+
13 | DESCRIPTION : This procedure is called to delete or truncate all the tables |
14 | under lookup type MSC_X_SETUP_ENTITY_CODE |
15 +========================================================================================*/
16
17 PROCEDURE delete_records ( p_instance_code IN VARCHAR2 DEFAULT NULL, p_instance_id IN NUMBER ,p_del_rej_rec IN NUMBER ,p_trunc_flag IN NUMBER )
18 AS
19
20 lv_instance_code VARCHAR2(5);
21 lv_instance_id NUMBER;
22 lv_p_del_rej_rec NUMBER;
23 lv_truncation_flag NUMBER;
24
25 lv_retval BOOLEAN;
26 lv_dummy1 VARCHAR2(32);
27 lv_dummy2 VARCHAR2(32);
28
29
30 lv_table_name FND_LOOKUP_VALUES.attribute1%Type;
31 lv_errtxt VARCHAR2(300);
32
33 lv_total number :=0; -- total number of rows deleted
34
35 CURSOR table_names IS
36 SELECT DISTINCT (LV.ATTRIBUTE1) TABLE_NAME
37 FROM FND_LOOKUP_VALUES LV
38 WHERE LV.ENABLED_FLAG = 'Y'
39 AND LV.VIEW_APPLICATION_ID = 700
40 AND SUBSTR (LV.ATTRIBUTE1, 1, 3) = 'MSD'
41 AND nvl(LV.ATTRIBUTE4,2)=2
42 AND LV.LOOKUP_TYPE = 'MSC_X_SETUP_ENTITY_CODE';
43
44 lv_sql_stmt VARCHAR2(1000);
45
46
47 table_not_found EXCEPTION;
48 PRAGMA EXCEPTION_INIT (table_not_found,-00942);
49
50 synonym_translation_invalid EXCEPTION;
51 PRAGMA EXCEPTION_INIT (synonym_translation_invalid,-00980);
52
53 BEGIN
54
55 OPEN table_names;
56 FETCH table_names INTO lv_table_name;
57 IF ( table_names%ROWCOUNT = 0 ) THEN
58 FND_MESSAGE.SET_NAME('MSC','MSC_PS_INVALID_LOOKUP');
59 lv_errtxt:= FND_MESSAGE.GET;
60 msc_st_util.log_message (lv_errtxt);
61 v_program_status := G_ERROR;
62
63 CLOSE table_names;
64 ELSE -- IF ( table_names%ROWCOUNT = 0 ) THEN
65 CLOSE table_names;
66
67 lv_truncation_flag := p_trunc_flag;
68
69
70 IF (lv_truncation_flag = SYS_YES) THEN
71
72 IF v_debug THEN
73 msc_st_util.log_message ('Truncation flag is YES. Entering in truncation LOOP');
74 END IF;
75
76 UPDATE msc_apps_instances
77 SET st_status= G_ST_PURGING;
78 COMMIT;
79
80 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSD', lv_dummy1, lv_dummy2, v_applsys_schema);
81
82 OPEN table_names;
83
84
85 LOOP
86 FETCH table_names INTO lv_table_name;
87 EXIT WHEN table_names%NOTFOUND;
88 IF v_debug THEN
89 msc_st_util.log_message (lv_table_name);
90 END IF;
91
92
93 BEGIN
94 lv_sql_stmt := 'TRUNCATE TABLE '||v_applsys_schema||'.'||lv_table_name|| '';
95
96 IF v_debug THEN
97 msc_st_util.log_message ('Sql statements to be executed-'||lv_sql_stmt);
98 END IF;
99
100
101
102 EXECUTE IMMEDIATE lv_sql_stmt;
103
104 EXCEPTION
105
106 WHEN table_not_found THEN
107 lv_errtxt := substr(SQLERRM,1,240) ;
108 msc_st_util.log_message(lv_errtxt);
109
110
111 WHEN OTHERS THEN
112 lv_errtxt := substr(SQLERRM,1,240) ;
113 msc_st_util.log_message(lv_errtxt);
114
115
116 END;
117
118 END LOOP;
119 CLOSE table_names;
120
121 UPDATE msc_apps_instances
122 SET st_status= G_ST_EMPTY;
123 COMMIT;
124
125 ELSE --IF (lv_truncation_flag = SYS_YES) THEN
126
127
128
129
130 lv_instance_code := p_instance_code;
131 lv_instance_id := p_instance_id;
132 lv_p_del_rej_rec := p_del_rej_rec;
133 v_batch_size := TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
134
135
136 OPEN table_names;
137 LOOP
138 lv_total := 0;
139 FETCH table_names INTO lv_table_name;
140 EXIT WHEN table_names%NOTFOUND;
141
142 loop
143
144
145 IF ( lv_table_name = 'MSD_ST_CS_DATA' ) THEN
146
147 IF ( lv_p_del_rej_rec = SYS_YES ) THEN
148 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''' ) OR ( ATTRIBUTE_1 = '''||lv_instance_id||''' )) AND ROWNUM <= '||v_batch_size
149 ||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||' ,'|| G_PROPAGATION||' )';
150 ELSE
151 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''' ) OR ( ATTRIBUTE_1 = '''||lv_instance_id||''' )) AND ROWNUM <= '||v_batch_size ;
152 END IF;
153
154 IF v_debug THEN
155 msc_st_util.log_message ('Sql statements executed-'||lv_sql_stmt);
156 END IF;
157 /* Bug 4038215 */
158 ELSIF ( lv_table_name = 'MSD_ST_ITEM_RELATIONSHIPS' ) THEN
159
160 IF ( lv_p_del_rej_rec = SYS_YES ) THEN
161 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE_ID= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size
162 ||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';
163 ELSE
164 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE_ID= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size||'';
165 END IF;
166
167 IF v_debug THEN
168 msc_st_util.log_message ('Sql statements executed-'||lv_sql_stmt);
169 END IF;
170
171
172 ELSE
173
174 IF ( lv_p_del_rej_rec = SYS_YES ) THEN
175 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size
176 ||' AND PROCESS_FLAG IN ( '||G_ERROR_FLG||','||G_PROPAGATION||')';
177 ELSE
178 lv_sql_stmt := 'DELETE FROM '||lv_table_name||' WHERE (( SR_INSTANCE_CODE= '''||lv_instance_code||''') OR ( INSTANCE= '||lv_instance_id||')) AND ROWNUM <= '||v_batch_size||'';
179 END IF;
180
181 IF v_debug THEN
182 msc_st_util.log_message ('Sql statements executed-'||lv_sql_stmt);
183 END IF;
184
185
186 END IF;
187
188
189 BEGIN
190
191 EXECUTE IMMEDIATE lv_sql_stmt;
192
193
194 lv_total := lv_total+SQL%ROWCOUNT ;
195
196
197 EXCEPTION
198
199 WHEN table_not_found THEN
200 lv_errtxt := substr(SQLERRM,1,240) ;
201 MSC_ST_UTIL.LOG_MESSAGE(lv_errtxt);
202 exit;
203
204 WHEN synonym_translation_invalid THEN
205 lv_errtxt := substr(SQLERRM,1,240) ;
206 MSC_ST_UTIL.LOG_MESSAGE(lv_errtxt);
207 exit;
208
209 WHEN OTHERS THEN
210 lv_errtxt := substr(SQLERRM,1,240) ;
211 msc_st_util.log_message(lv_errtxt);
212 exit;
213
214 END;
215
216 EXIT WHEN SQL%NOTFOUND;
217
218 COMMIT;
219 end loop;
220
221 IF v_debug THEN
222 msc_st_util.log_message ('No. of rows deleted from '|| lv_table_name ||' - '||lv_total);
223 END IF;
224
225
226
227 END LOOP;
228 CLOSE table_names;
229
230 END IF; --IF (lv_truncation_flag = SYS_YES) THEN
231
232 END IF; -- IF ( table_names%ROWCOUNT = 0 ) THEN
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237
238 lv_errtxt := substr(SQLERRM,1,240) ;
239 msc_st_util.log_message(lv_errtxt);
240
241 END delete_records;
242
243
244 /*========================================================================================+
245 | DESCRIPTION : This fuction is called to check whether the st_status for a particular |
246 | instance is not in PULLING , LOADING and PRE-PROCESSING |
247 +========================================================================================*/
248
249
250 FUNCTION is_purge_possible ( ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY NUMBER, pINSTANCE_CODE IN VARCHAR2 , pINSTANCE_ID IN NUMBER )
251 RETURN BOOLEAN
252 AS
253 lv_staging_table_status NUMBER;
254
255 BEGIN
256
257 SELECT ST_STATUS INTO lv_staging_table_status
258 FROM msc_apps_instances
259 WHERE INSTANCE_CODE= pINSTANCE_CODE
260 FOR UPDATE;
261
262 IF v_debug THEN
263 msc_st_util.log_message ('Entered to check whether purge possible for the instance-'||pINSTANCE_CODE);
264 END IF;
265
266
267
268 IF lv_staging_table_status= G_ST_PULLING THEN
269 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PULLING');
270 ERRBUF:= FND_MESSAGE.GET;
271
272 IF v_debug THEN
273 msc_st_util.log_message (ERRBUF);
274 END IF;
275
276 IF ( pINSTANCE_ID <> -1 )
277 THEN
278 v_program_status :=G_ERROR;
279
280 ELSE
281 v_program_status :=G_WARNING;
282
283 END IF;
284
285 RETURN FALSE;
286
287
288 ELSIF lv_staging_table_status= G_ST_COLLECTING THEN
289 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_LOADING');
290 ERRBUF:= FND_MESSAGE.GET;
291
292 IF v_debug THEN
293 msc_st_util.log_message (ERRBUF);
294 END IF;
295
296 IF ( pINSTANCE_ID <> -1 )
297 THEN
298 v_program_status :=G_ERROR;
299
300 ELSE
301 v_program_status :=G_WARNING;
302
303 END IF;
304
305 RETURN FALSE;
306
307 ELSIF lv_staging_table_status= G_ST_PRE_PROCESSING THEN
308 FND_MESSAGE.SET_NAME('MSC', 'MSC_ST_ERROR_PRE_PROCESSING');
309 ERRBUF:= FND_MESSAGE.GET;
310
311 IF v_debug THEN
312 msc_st_util.log_message (ERRBUF);
313 END IF;
314
315 IF ( pINSTANCE_ID <> -1 )
316 THEN
317 v_program_status :=G_ERROR;
318
319 ELSE
320 v_program_status :=G_WARNING;
321
322 END IF;
323
324 RETURN FALSE;
325
326
327 ELSE
328 UPDATE msc_apps_instances
329 SET st_status=G_ST_PURGING
330 WHERE INSTANCE_CODE= pINSTANCE_CODE;
331 COMMIT;
332
333 RETURN TRUE;
334
335 END IF;
336
337
341 | DESCRIPTION : This is the main program that deletes the records from the MSD staging |
338 END is_purge_possible;
339
340 /*=============================================================================================+
342 | tables.It takes instance_code as a parameter and deletes records for the |
343 | instance only when st_status for this instance is not in G_ST_PULLING, |
344 | G_ST_COLLECTING and G_ST_PRE_PROCESSING .If the instance_code is null |
345 | then it will delete records from all instances after checking the st_status. |
346 | It also takes a parameter , whether to delete only errored out records or |
347 | all legacy data (st_status check before deletion will only take place |
348 | when 'delete only rejected records' parameter is set to NO). |
349 +=============================================================================================*/
350
351
352 PROCEDURE LAUNCH_PROCEDURE ( ERRBUF OUT NOCOPY VARCHAR2,
353 RETCODE OUT NOCOPY NUMBER,
354 p_instance_id IN NUMBER,
355 p_del_rej_rec IN NUMBER)
356
357
358
359 AS
360
361
362 CURSOR instance_codes ( cp_instance_id NUMBER ) IS
363 SELECT instance_code,instance_type,instance_id,st_status
364 FROM msc_apps_instances
365 WHERE ( cp_instance_id = -1 or instance_id=cp_instance_id );
366
367
368 -- Cursor P is for update to lock the records before checking for the st_status.
369
370 CURSOR p (cp_instance_id NUMBER ) IS
371 SELECT instance_code
372 FROM msc_apps_instances
373 WHERE st_status NOT IN (G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING)
374 AND ( cp_instance_id= -1 or instance_id=cp_instance_id )
375 FOR UPDATE;
376
377 CURSOR total_instances IS
378 SELECT count(*)
379 FROM msc_apps_instances;
380
381 CURSOR staging_status (cp_instance_id NUMBER ) IS
382 SELECT count(*)
383 FROM msc_apps_instances
384 WHERE st_status IN ( G_ST_PULLING,G_ST_COLLECTING,G_ST_PRE_PROCESSING )
385 AND ((instance_id=cp_instance_id) OR (cp_instance_id=-1));
386
387
388 -- input variables of procedure
389 lv_p_del_rej_rec NUMBER;
390 lv_p_instance_id NUMBER ;
391
392
393 -- variable for cursor instance_codes
394 lv_instance_code MSC_APPS_INSTANCES.INSTANCE_CODE%TYPE;
395 lv_st_status MSC_APPS_INSTANCES.ST_STATUS%TYPE;
396 lv_instance_type MSC_APPS_INSTANCES.INSTANCE_TYPE%TYPE;
397 lv_instance_id MSC_APPS_INSTANCES.INSTANCE_ID%TYPE;
398
399 lv_inst_flag NUMBER := 0 ;
400 lv_st_status_flag NUMBER:= 0;
401 lv_trunc_profile BOOLEAN:=FALSE;
402
403 lv_trunc_flag NUMBER := SYS_NO;
404
405 lv_sql_stmt VARCHAR2(500);
406 lv_errtxt VARCHAR2(300);
407
408
409
410 table_not_found EXCEPTION;
411 PRAGMA EXCEPTION_INIT (table_not_found,-00942);
412
413
414 BEGIN
415
416
417 lv_p_instance_id := nvl( p_instance_id ,-1);
418 lv_p_del_rej_rec :=p_del_rej_rec;
419
420
421
422
423 v_debug := nvl(FND_PROFILE.VALUE('MRP_DEBUG'),'N') = 'Y';
424 lv_trunc_profile := nvl(FND_PROFILE.VALUE('MSC_PURGE_ST_CONTROL'),'N') = 'Y';
425
426
427 IF ( lv_trunc_profile AND lv_p_del_rej_rec=SYS_NO ) THEN
428 OPEN total_instances;
429 FETCH total_instances into lv_inst_flag;
430 CLOSE total_instances;
431
432 -- locking the records in msc_apps_instances before checking the st_status
433
434 open p(lv_p_instance_id);
435 close p;
436
437 -- Counting number of instances for which st_status is G_ST_PULLING , G_ST_COLLECTING and G_ST_PRE_PROCESSING
438 OPEN staging_status(lv_p_instance_id);
439 FETCH staging_status into lv_st_status_flag;
440 CLOSE staging_status;
441
442 IF v_debug THEN
443 msc_st_util.log_message ('Value of lv_st_status_flag-'||lv_st_status_flag);
444 END IF;
445 END IF;
446
447 -- Setting the truncation flag
448
449 IF ( (lv_p_del_rej_rec = SYS_NO ) AND ( lv_trunc_profile ) AND ((lv_p_instance_id = -1) OR (lv_inst_flag = 1)) AND (lv_st_status_flag = 0) )
450 THEN
451 lv_trunc_flag :=SYS_YES;
452 ELSE
453 lv_trunc_flag :=SYS_NO;
454 END IF;
455
456
457 IF ( lv_trunc_flag = SYS_YES ) THEN
458
459 delete_records ( p_instance_id => lv_p_instance_id,
460 p_del_rej_rec => lv_p_del_rej_rec,
461 p_trunc_flag => lv_trunc_flag );
462
463 ELSE
464
465
466
467 commit; -- To break the lock on the records, acquired while opening the cursor p or q
468
469 IF v_debug THEN
470 msc_st_util.log_message ('Truncation flag is NO. Entered in DELETION LOOP');
471 END IF;
472
473 OPEN instance_codes(lv_p_instance_id);
474 LOOP
475 FETCH instance_codes INTO lv_instance_code,lv_instance_type,lv_instance_id,lv_st_status;
476 EXIT WHEN instance_codes%NOTFOUND;
477
478 IF v_debug THEN
479 msc_st_util.log_message(lv_instance_code);
480 END IF;
481
482 IF (lv_p_del_rej_rec=SYS_YES) THEN
483
484 IF v_debug THEN
485 msc_st_util.log_message ('Deleting without checking the ST_STATUS');
486 END IF;
487
488
492 ELSE --IF (lv_p_del_rej_rec=SYS_YES) THEN
489
490 delete_records( lv_instance_code,lv_instance_id,lv_p_del_rej_rec,lv_trunc_flag);
491
493
494
495 IF ( is_purge_possible( ERRBUF,RETCODE,lv_instance_code,lv_p_instance_id) ) THEN
496
497
498 IF v_debug THEN
499 msc_st_util.log_message ('Deleting after checking the ST_STATUS');
500 END IF;
501
502 delete_records(lv_instance_code,lv_instance_id,lv_p_del_rej_rec,lv_trunc_flag);
503
504
505 IF v_debug THEN
506 msc_st_util.log_message ('Setting the st_status to empty');
507 END IF;
508
509 UPDATE msc_apps_instances
510 SET st_status=G_ST_EMPTY
511 WHERE instance_code=lv_instance_code;
512 COMMIT;
513
514
515
516
517 END IF;
518 END IF;
519
520 END LOOP;
521 CLOSE instance_codes;
522
523
524 END IF;
525
526
527
528 IF v_program_status=G_WARNING THEN
529 RETCODE := G_WARNING;
530 ELSIF v_program_status=G_ERROR THEN
531 RETCODE := -1;
532 END IF;
533
534
535 EXCEPTION
536
537 WHEN OTHERS THEN
538 ERRBUF := SQLERRM;
539 RETCODE := -1;
540
541 lv_errtxt := substr(SQLERRM,1,240) ;
542 msc_st_util.log_message (lv_errtxt);
543
544 END LAUNCH_PROCEDURE;
545
546 END MSD_PURGE_LEG ;
[Home] [Help]