[Home] [Help]
PACKAGE BODY: APPS.FLM_PURGE
Source
1 PACKAGE BODY FLM_PURGE AS
2 /* $Header: FLMCPPGB.pls 120.3 2006/09/20 21:21:45 ksuleman noship $ */
3
4
5 PROCEDURE VERIFY_FOREIGN_KEYS(
6 arg_wip_entity_id in number,
7 arg_org_id in number,
8 arg_item_id in number,
9 arg_table_name out NOCOPY varchar2,
10 arg_return_value out NOCOPY number ,
11 errbuf out NOCOPY varchar2
12 )
13 IS
14 l_records_found NUMBER := G_ZERO;
15 l_stmt_num NUMBER := G_ZERO;
16 l_flag BOOLEAN := TRUE;
17 Begin
18 l_stmt_num := 310;
19
20 SELECT COUNT(*)
21 into l_records_found
22 FROM DUAL
23 WHERE EXISTS
24 (SELECT 1
25 FROM MTL_MATERIAL_TRANSACTIONS
26 WHERE ORGANIZATION_ID = arg_org_id
27 AND INVENTORY_ITEM_ID = arg_item_id
28 AND TRANSACTION_SOURCE_TYPE_ID + 0 = 5
29 AND TRANSACTION_SOURCE_ID = arg_wip_entity_id);
30
31
32 if (l_records_found <> 0) then
33 arg_table_name := arg_table_name || ' MTL_MATERIAL_TRANSACTIONS *';
34 l_flag := FALSE;
35 end if;
36
37 l_stmt_num := 320;
38
39 SELECT COUNT(*)
40 into l_records_found
41 FROM DUAL
42 WHERE EXISTS
43 (SELECT 1
44 FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT
45 WHERE MMT.ORGANIZATION_ID = arg_org_id
46 AND MMT.TRANSACTION_SOURCE_ID = arg_wip_entity_id
47 AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
48 AND MMT.TRANSACTION_SOURCE_TYPE_ID +0 = 5 );
49
50 if (l_records_found <> 0) then
51 arg_table_name := arg_table_name||' MTL_TRANSACTIONS_ACCOUNTS *';
52 l_flag := FALSE ;
53 end if;
54
55 l_stmt_num := 330;
56
57 SELECT COUNT(*)
58 into l_records_found
59 FROM DUAL
60 WHERE EXISTS
61 (SELECT 1
62 FROM MTL_TRANSACTION_LOT_NUMBERS
63 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
64 AND TRANSACTION_SOURCE_ID = arg_wip_entity_id );
65
66 if (l_records_found <> 0) then
67 arg_table_name := arg_table_name||' MTL_TRANSACTION_LOT_NUMBERS *';
68 l_flag := FALSE;
69 end if;
70
71 l_stmt_num := 340;
72
73 SELECT COUNT(*)
74 into l_records_found
75 FROM DUAL
76 WHERE EXISTS
77 (SELECT 1
78 FROM MTL_UNIT_TRANSACTIONS
79 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
80 AND TRANSACTION_SOURCE_ID = arg_wip_entity_id );
81
82 if (l_records_found <> 0) then
83 arg_table_name := arg_table_name||' MTL_UNIT_TRANSACTIONS *';
84 l_flag := FALSE;
85 end if;
86
87 l_stmt_num := 350;
88
89 SELECT COUNT(*)
90 into l_records_found
91 FROM DUAL
92 WHERE EXISTS
93 (SELECT 1
94 FROM MTL_DEMAND MD, WIP_ENTITIES WE
95 WHERE WE.WIP_ENTITY_ID = arg_wip_entity_id
96 AND MD.SUPPLY_SOURCE_TYPE = 5
97 AND MD.SUPPLY_SOURCE_HEADER_ID = WE.WIP_ENTITY_ID
98 AND MD.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
99 AND MD.ORGANIZATION_ID = arg_org_id);
100
101 if (l_records_found <> 0) then
102 arg_table_name := arg_table_name||' MTL_DEMAND *';
103 l_flag := FALSE;
104 end if;
105
106 l_stmt_num := 360;
107
108 SELECT COUNT(*)
109 into l_records_found
110 FROM DUAL
111 WHERE EXISTS
112 (SELECT 1
113 FROM MTL_USER_SUPPLY
114 WHERE SOURCE_TYPE_ID = 4
115 AND SOURCE_ID = arg_wip_entity_id
116 AND ORGANIZATION_ID = arg_org_id);
117
118 if (l_records_found <> 0) then
119 arg_table_name := arg_table_name||' MTL_USER_SUPPLY *';
120 l_flag := FALSE;
121 end if;
122
123 l_stmt_num := 370;
124
125 SELECT COUNT(*)
126 into l_records_found
127 FROM DUAL
128 WHERE EXISTS
129 (SELECT 1
130 FROM MTL_USER_DEMAND
131 WHERE SOURCE_TYPE_ID = 4
132 AND SOURCE_ID = arg_wip_entity_id
133 AND ORGANIZATION_ID = arg_org_id);
134
135 if (l_records_found <> 0) then
136 arg_table_name := arg_table_name||' MTL_USER_DEMAND *';
137 l_flag := FALSE;
138 end if;
139
140 l_stmt_num := 380;
141
142 SELECT COUNT(*)
143 into l_records_found
144 FROM DUAL
145 WHERE EXISTS
146 (SELECT 1
147 FROM MTL_SERIAL_NUMBERS
148 WHERE INVENTORY_ITEM_ID = arg_item_id
149 AND ORIGINAL_WIP_ENTITY_ID = arg_wip_entity_id);
150
151 if (l_records_found <> 0) then
152 arg_table_name := arg_table_name||' MTL_SERIAL_NUMBERS *';
153 l_flag := FALSE;
154 end if;
155
156
157 if (l_flag) then
158 arg_return_value := G_SUCCESS;
159 else
160 arg_return_value := G_WARNING;
161 end if;
162
163 EXCEPTION WHEN OTHERS THEN
164 arg_return_value := G_ERROR;
165 errbuf := substr(SQLERRM,1,500);
166 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Verify_Foreign_Key');
167 END VERIFY_FOREIGN_KEYS;
168
169
170 PROCEDURE DELETE_EXE_TABLES(arg_wip_entity_id in number,
171 arg_org_id in number) IS
172 BEGIN
173
174 delete from flm_exe_serial_numbers
175 where wip_entity_id = arg_wip_entity_id;
176
177 delete from flm_exe_lot_numbers
178 where wip_entity_id = arg_wip_entity_id;
179
180 delete from flm_exe_req_operations
181 where wip_entity_id = arg_wip_entity_id;
182
183 END DELETE_EXE_TABLES;
184
185
186 Procedure DELETE_TABLES(
187 arg_wip_entity_id in number,
188 arg_org_id in number,
189 arg_auto_replenish in varchar2, /* Added for Enhancement #2829204 */
190 arg_return_value out NOCOPY number,
191 errbuf out NOCOPY varchar2
192 )
193 IS
194 l_stmt_num NUMBER := G_ZERO;
195
196 CURSOR card_activity_csr IS
197 SELECT kanban_activity_id
198 FROM mtl_kanban_card_activity
199 WHERE source_wip_entity_id = arg_wip_entity_id;
200
201 Begin
202 l_stmt_num := 410;
203
204 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
205 WHERE TRANSACTION_SOURCE_TYPE_ID +0 = 5
206 AND TRANSACTION_SOURCE_ID = arg_wip_entity_id
207 AND ORGANIZATION_ID = arg_org_id;
208
209 l_stmt_num := 420;
210
211 DELETE FROM MTL_TRANSACTIONS_INTERFACE
212 WHERE TRANSACTION_SOURCE_ID = arg_wip_entity_id
213 AND ORGANIZATION_ID = arg_org_id;
214
215 l_stmt_num := 430;
216
217 DELETE FROM MRP_RELIEF_INTERFACE
218 WHERE DISPOSITION_TYPE = 1
219 AND DISPOSITION_ID = arg_wip_entity_id;
220
221 l_stmt_num := 440;
222
223 DELETE FROM WIP_REQ_OPERATION_COST_DETAILS
224 WHERE WIP_ENTITY_ID = arg_wip_entity_id ;
225
226 l_stmt_num := 450;
227
228 DELETE FROM WIP_OPERATION_OVERHEADS
229 WHERE WIP_ENTITY_ID = arg_wip_entity_id ;
230
231 l_stmt_num := 460;
232
233 DELETE FROM WIP_TRANSACTIONS
234 WHERE WIP_ENTITY_ID = arg_wip_entity_id;
235
236 l_stmt_num := 470;
237
238 DELETE FROM WIP_TRANSACTION_ACCOUNTS
239 WHERE WIP_ENTITY_ID = arg_wip_entity_id;
240
241 l_stmt_num := 475;
242 DELETE FROM WIP_PERIOD_BALANCES
243 WHERE WIP_ENTITY_ID = arg_wip_entity_id
244 AND ORGANIZATION_ID = arg_org_id;
245
246 l_stmt_num := 480;
247
248 /* Added for Enhancement # 3321626
249 * To Delete data from flm_exe_operations table also.
250 */
251
252 DELETE FROM FLM_EXE_OPERATIONS
253 WHERE WIP_ENTITY_ID = arg_wip_entity_id;
254
255 l_stmt_num := 481 ;
256 delete_exe_tables (arg_wip_entity_id, arg_org_id);
257
258 l_stmt_num := 485;
259
260 DELETE FROM WIP_FLOW_SCHEDULES
261 WHERE WIP_ENTITY_ID = arg_wip_entity_id;
262
263 l_stmt_num := 490;
264
265 /*
266 Added for Enhancement #2829204
267 If for the flow schedule, auto_replenish flag was set to 'Y', this
268 indicates, this flow schedule is being referenced by a Kanban Card.
269 So, we need to delink that Kanban Card Activity which was linked to
270 this flow schedule.
271 */
272
273 IF (nvl(arg_auto_replenish, 'N') = 'Y') THEN
274
275 FOR l_card_activity_csr IN card_activity_csr
276 LOOP
277 UPDATE mtl_kanban_card_activity
278 SET source_wip_entity_id = NULL
279 WHERE kanban_activity_id = l_card_activity_csr.kanban_activity_id;
280 END LOOP;
281
282 END IF;
283
284 l_stmt_num := 495;
285
286 DELETE FROM WIP_ENTITIES
287 WHERE WIP_ENTITY_ID = arg_wip_entity_id;
288
289 arg_return_value := G_SUCCESS;
290
291 EXCEPTION WHEN OTHERS THEN
292 arg_return_value := G_ERROR;
293 errbuf := substr(SQLERRM,1,500);
294 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Delete_Tables');
295 END DELETE_TABLES;
296
297 PROCEDURE PURGE_SCHEDULES(
298 errbuf out NOCOPY varchar2,
299 retcode out NOCOPY number,
300 arg_org_id in number,
301 arg_cutoff_date in varchar2,
302 arg_line in VARCHAR2,
303 arg_assembly in VARCHAR2,
304 arg_purge_option in number)
305 IS
306 CURSOR Purge(p_cutoff_date DATE) IS --fix bug#3170105
307 SELECT wfs.wip_entity_id ,
308 wfs.schedule_number,
309 wfs.status,
310 wfs.primary_item_id ,
311 wfs.line_id line_id,
312 wfs.scheduled_completion_date ,
313 wfs.date_closed,
314 wfs.organization_id,
315 wfs.auto_replenish /* Added for Enhancement #2829204 */
316 FROM wip_flow_schedules wfs
317 WHERE wfs.organization_id = arg_org_id
318 AND wfs.scheduled_completion_date <= p_cutoff_date
319 AND (arg_line is null or wfs.line_id = to_number(arg_line) )
320 AND wfs.primary_item_id = nvl(arg_assembly,wfs.primary_item_id);
321
322 l_wip_entity_id WIP_FLOW_SCHEDULES.WIP_ENTITY_ID%TYPE;
323 l_schedule_number WIP_FLOW_SCHEDULES.SCHEDULE_NUMBER%TYPE;
324 l_status WIP_FLOW_SCHEDULES.STATUS%TYPE;
325 l_primary_item_id WIP_FLOW_SCHEDULES.PRIMARY_ITEM_ID%TYPE;
326 l_close_date WIP_FLOW_SCHEDULES.DATE_CLOSED%TYPE;
327 l_completion_date WIP_FLOW_SCHEDULES.SCHEDULED_COMPLETION_DATE%TYPE;
328 l_account_close_date DATE;
329 l_organization_id WIP_FLOW_SCHEDULES.ORGANIZATION_ID%TYPE;
330 l_table_name VARCHAR2(500);
331 l_tot_rec_purge NUMBER := G_ZERO;
332 l_records_deleted NUMBER := G_ZERO;
333 l_flag BOOLEAN ;
334 l_return_value NUMBER := G_ZERO;
335 l_stmt_num NUMBER := G_ZERO;
336 l_auto_replenish WIP_FLOW_SCHEDULES.AUTO_REPLENISH%TYPE;
337 l_cutoff_date DATE;
338 l_line_code VARCHAR2(10);
339
340 Begin
341
342
343 l_stmt_num := 100;
344
345 MRP_UTIL.MRP_LOG(' The Value of Parameters are : ');
346 MRP_UTIL.MRP_LOG(' Organization ---> '||to_char(arg_org_id));
347
348 --fix bug#3170105
349 l_cutoff_date := flm_timezone.client_to_server(
350 fnd_date.canonical_to_date(arg_cutoff_date))+1-1/(24*60*60);
351 MRP_UTIL.MRP_LOG(' Cut-Off Date ---> '||to_char(l_cutoff_date));
352 --end of fix bug#3170105
353
354 -- Bug 5353590
355 -- find the line_code name with the (line id, org id) unique key
356 select line_code into l_line_code
357 from wip_lines
358 where line_id = arg_line and organization_id = arg_org_id;
359
360 MRP_UTIL.MRP_LOG(' Line ---> '||arg_line||' (line name: '||l_line_code||')');
361 MRP_UTIL.MRP_LOG(' Assembly ---> '||arg_assembly);
362
363 /*
364 When the "Purge Option" is given as "All",
365 arg_purge_option will have a value of 1
366 When the "Purge Option" is given as "Resource Transactions Only",
367 arg_purge_option will have a value of 2
368 */
369
370 if ( arg_purge_option = 1 ) then
371 MRP_UTIL.MRP_LOG(' Purge Option ---> '||'All');
372 elsif ( arg_purge_option = 2 ) then
373 MRP_UTIL.MRP_LOG(' Purge Option ---> '||'Resource Transactions Only');
374 elsif ( arg_purge_option = 3 ) then /* Added for deleting execution history data */
375 MRP_UTIL.MRP_LOG(' Purge Option ---> '||'Execution History Only');
376 end if;
377
378 select max(period_close_date) --fix bug#3170105
379 into l_account_close_date
380 from org_acct_periods
381 where organization_id = arg_org_id
382 and schedule_close_date
383 <= l_cutoff_date
384 and open_flag = 'N'
385 and period_close_date IS NOT NULL;
386
390 return;
387 if (l_account_close_date is null) then
388 fnd_message.set_name('FLM','FLM_SCHED_NO_ACCT_CLOSE_PERIOD');
389 MRP_UTIL.MRP_LOG(fnd_message.get);
391 end if;
392
393 FOR Purge_Rec IN Purge(l_cutoff_date) LOOP --fix bug#3170105
394
395 l_flag := TRUE;
396 l_table_name := NULL;
397 l_wip_entity_id := Purge_Rec.Wip_entity_id;
398 l_schedule_number := Purge_rec.Schedule_Number;
399 l_status := Purge_rec.Status;
400 l_primary_item_id := Purge_rec.Primary_item_id;
401 l_completion_date := Purge_rec.Scheduled_completion_date;
402 l_close_date := Purge_rec.Date_closed;
403 l_organization_id := Purge_rec.Organization_id;
404 l_auto_replenish := Purge_rec.Auto_Replenish;
405
406 if (l_completion_date > l_account_close_date) then
407 fnd_message.set_name('FLM','FLM_SCHED_CLOSED_PERIOD');
408 fnd_message.set_token('SCHEDULE',l_schedule_number);
409 MRP_UTIL.MRP_LOG(fnd_message.get);
410 l_flag := FALSE;
411 end if;
412
413 l_stmt_num := 200;
414
415 if (l_flag) then
416 if ((l_status <> G_CLOSED_STATUS)
417 or (l_close_date IS NULL)) then
418 fnd_message.set_name('FLM','FLM_SCHED_NOT_CLOSED');
419 fnd_message.set_token('SCHEDULE',l_schedule_number);
420 MRP_UTIL.MRP_LOG(fnd_message.get);
421 l_flag := FALSE;
422 end if;
423 end if;
424
425 l_stmt_num := 300;
426
427 /* start of arg_purge_option if condition */
428 if (arg_purge_option = 2) and (l_flag) then
429
430 DELETE FROM WIP_TRANSACTIONS
431 WHERE WIP_ENTITY_ID = l_wip_entity_id;
432
433 l_stmt_num := 310;
434
435 DELETE FROM WIP_TRANSACTION_ACCOUNTS
436 WHERE WIP_ENTITY_ID = l_wip_entity_id;
437
438 l_stmt_num := 320;
439
440 l_records_deleted := l_records_deleted + 1;
441 l_tot_rec_purge := l_tot_rec_purge + 1;
442
443 if (l_records_deleted >= G_BATCH ) then
444 COMMIT;
445 l_records_deleted := G_ZERO;
446 end if;
447
448 /* Added for Enhancement # 3321626
449 * To Delete data from flm_exe_operations table if purge_option = 3
450 */
451 elsif (arg_purge_option = 3) and (l_flag) then
452
453 DELETE FROM FLM_EXE_OPERATIONS
454 WHERE WIP_ENTITY_ID = l_wip_entity_id;
455
456 l_stmt_num := 325;
457
458 delete_exe_tables (l_wip_entity_id, l_organization_id);
459
460 l_stmt_num := 326;
461
462 l_records_deleted := l_records_deleted + 1;
463 l_tot_rec_purge := l_tot_rec_purge + 1;
464
465 if (l_records_deleted >= G_BATCH ) then
466 COMMIT;
467 l_records_deleted := G_ZERO;
468 end if;
469
470 else
471 if (l_flag) then
472 Verify_Foreign_Keys(l_wip_entity_id,
473 l_organization_id,
474 l_primary_item_id,
475 l_table_name,
476 l_return_value,
477 errbuf
478 );
479 end if;
480
481
482 if (l_return_value = G_WARNING) and (l_flag) then
483 fnd_message.set_name('FLM','FLM_SCHEDULE_FKEY_REFERENCE');
484 fnd_message.set_token('SCHEDULE',l_schedule_number);
485 fnd_message.set_token('TABLES',l_table_name);
486 MRP_UTIL.MRP_LOG(fnd_message.get);
487 l_flag := FALSE;
488 elsif (l_return_value = G_ERROR) then
489 APP_EXCEPTION.RAISE_EXCEPTION;
490 end if;
491
492
493 l_stmt_num := 400;
494
495 if (l_flag) then
496 Delete_Tables(l_wip_entity_id,
497 l_organization_id,
498 l_auto_replenish, /* Added for Enhancement #2829204 */
499 l_return_value,
500 errbuf
501 );
502 end if;
503
504 l_stmt_num := 500;
505
506 if ((l_flag) and (l_return_value = G_SUCCESS)) then
507 l_records_deleted := l_records_deleted + 1;
508 l_tot_rec_purge := l_tot_rec_purge + 1;
509 if (l_records_deleted >= G_BATCH ) then
510 COMMIT;
511 l_records_deleted := G_ZERO;
512 end if;
513 elsif (l_return_value = G_ERROR) then
514 retcode := l_return_value;
515 APP_EXCEPTION.RAISE_EXCEPTION;
516 end if;
517
518 end if; /* end of arg_purge_option if condition */
519
520 END LOOP;
521
522 if (l_records_deleted > 0) then
523 COMMIT;
524 end if;
525
526 if (l_tot_rec_purge = G_ZERO) then
527 fnd_message.set_name('FLM','FLM_SCHEDULE_NOT_FOUND');
528 MRP_UTIL.MRP_LOG(fnd_message.get);
529 else
530 /* Added for Enhancement # 3321626
531 * Modified the message to be shown in the log file, depending upon the Purge Option
532 */
533 if (arg_purge_option = 2) then
534 fnd_message.set_name('FLM','FLM_PURGE_RESOURCE_TXNS');
535 fnd_message.set_token('NUMBER',l_tot_rec_purge);
536 MRP_UTIL.MRP_LOG(fnd_message.get);
537
538 elsif (arg_purge_option = 3) then
539 fnd_message.set_name('FLM','FLM_PURGE_EXECUTION_HISTORY');
540 fnd_message.set_token('NUMBER',l_tot_rec_purge);
541 MRP_UTIL.MRP_LOG(fnd_message.get);
542
543 else
544 fnd_message.set_name('FLM','FLM_SCHEDULES_PURGED');
545 fnd_message.set_token('NUMBER',l_tot_rec_purge);
546 MRP_UTIL.MRP_LOG(fnd_message.get);
547 end if;
548 end if;
549
550 EXCEPTION WHEN OTHERS THEN
551 retcode := G_ERROR;
552 if (errbuf is NULL) then
553 errbuf := SUBSTR(SQLERRM, 1, 500);
554 end if;
555 ROLLBACK;
556 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' Purge_Schedules');
557 MRP_UTIL.MRP_LOG('Error due to '|| errbuf );
558 END PURGE_SCHEDULES;
559
560
561 END FLM_PURGE;