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