[Home] [Help]
PACKAGE BODY: APPS.WSM_JOBPURGE_GRP
Source
1 PACKAGE BODY WSM_JobPurge_GRP AS
2 /* $Header: WSMPLBJB.pls 120.1 2005/12/30 05:37:57 sthangad noship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: delete_osfm_tables
7
8 ===========================================================================*/
9
10
11 procedure append_report(
12 p_rec in wip_wictpg.purge_report_type,
13 p_option in number
14 )
15 is
16 begin
17 if ( p_option IN (REPORT_ONLY, PURGE_AND_REPORT) ) then
18 insert into Wip_temp_reports(
19 key1, /* Group ID */
20 program_source,
21 last_updated_by,
22 organization_id,
23 wip_entity_id,
24 inventory_item_id,
25 key2, /* line ID */
26 key3, /* Repetitive schedule ID */
27 description, /* Table Name */
28 key6, /* Info Type */
29 attribute1 /* Info */,
30 date1, /* Start Date */
31 date2, /* Completion Date */
32 date3, /* Close Date */
33 attribute2, /*Job Name*/
34 attribute3 /*Line Code*/
35 ) values (
36 p_rec.group_id,
37 'WICTPG', /* program_source */
38 -1, /* Last Updated By */
39 p_rec.org_id,
40 p_rec.wip_entity_id,
41 p_rec.primary_item_id,
42 p_rec.line_id,
43 p_rec.schedule_id,
44 p_rec.table_name,
45 p_rec.info_type,
46 p_rec.info,
47 p_rec.start_date,
48 p_rec.complete_date,
49 p_rec.close_date,
50 p_rec.entity_name,
51 p_rec.line_code
52 );
53 end if ;
54
55 end append_report;
56
57 procedure before_append_report(
58 p_option in number,
59 p_purge_rec in wip_wictpg.purge_report_type,
60 num_rows in number
61 )
62 is
63 x_purge_rec wip_wictpg.purge_report_type;
64 begin
65
66 fnd_message.set_name('WIP', 'WIP_PURGE_ROWS');
67 fnd_message.set_token('NUMBER', to_char(num_rows));
68 x_purge_rec := p_purge_rec;
69 x_purge_rec.info := fnd_message.get;
70 append_report(x_purge_rec,p_option);
71
72 end before_append_report;
73
74 -- delete WIE which is referenced by WSM interface table on header_id
75 procedure delete_wie (
76 p_option in number,
77 p_header_id in number,
78 p_request_id in number,
79 p_wie_num_rows out NOCOPY number,
80 p_err_num in out NOCOPY number,
81 p_err_buf in out NOCOPY varchar2
82 )
83 is
84 begin
85 if (p_option = REPORT_ONLY) then
86 select count(*)
87 into p_wie_num_rows
88 from WSM_INTERFACE_ERRORS
89 where header_id = p_header_id
90 and request_id = nvl(p_request_id, -1);
91 else
92 DELETE FROM WSM_INTERFACE_ERRORS
93 WHERE header_id = p_header_id
94 and request_id = nvl(p_request_id, -1);
95 p_wie_num_rows := SQL%ROWCOUNT;
96 end if;
97
98 exception
99 when others then
100 p_err_num := SQLCODE;
101 p_err_buf:= SUBSTR(SQLERRM, 1, 500);
102
103 end delete_wie;
104
105
106 procedure delete_osfm_tables(
107 p_option in number,
108 p_group_id in number,
109 p_purge_request in wip_wictpg.get_purge_requests%rowtype,
110 -- ST Fix for bug 4918553 (Added the parameter p_detail_flag)
111 p_detail_flag IN BOOLEAN DEFAULT TRUE,
112 p_return_status out NOCOPY varchar2
113 )
114 is
115 x_num_rows number := 0;
116 x_tmp_num_rows number := 0;
117 x_wie_rows number := 0;
118 x_wsmti_rows number := 0;
119 x_wsji_rows number := 0;
120 x_wrji_rows number := 0;
121 x_purge_rec wip_wictpg.purge_report_type;
122 x_wip_entity_name VARCHAR2(240);
123 x_entity_type number;
124 x_header_id NUMBER;
125 x_request_id NUMBER;
126 p_err_num number;
127 p_err_buf varchar2(500);
128 e_delete_wie_exception EXCEPTION;
129
130 -- Bug 4722718 : Purge R12 table information as well..
131 l_serial_intf_rows NUMBER := 0;
132
133 cursor get_purge_wtxnis (pEntityName VARCHAR2, pWipEntityId NUMBER, pOrgId NUMBER) is
134 select wtxni.header_id, wtxni.request_id
135 from wsm_split_merge_txn_interface wtxni
136 where wtxni.organization_id = pOrgId
137 and wtxni.header_id in (
138 select sj.header_id
139 from wsm_starting_jobs_interface sj
140 where sj.wip_entity_id = pWipEntityId
141 union
142 select rj.header_id
143 from wsm_resulting_jobs_interface rj
144 where rj.wip_entity_name = pEntityName
145 );
146
147 -- get header_ids of purge job in wlji
148
149 cursor get_purge_wlji (pEntityName VARCHAR2, pOrgId NUMBER) is
150 select wlji.header_id, wlji.request_id
151 from WSM_LOT_JOB_INTERFACE wlji
152 where wlji.job_name = pEntityName
153 and wlji.organization_id = pOrgId;
154
155
156 cursor get_purge_wlmti (pWipEntityId NUMBER, pOrgId NUMBER) is
157 select wlmti.header_id, wlmti.request_id
158 from WSM_LOT_MOVE_TXN_INTERFACE wlmti
159 where wlmti.wip_entity_id = pWipEntityId
160 and wlmti.organization_id = pOrgId;
161
162 begin
163
164 SAVEPOINT osfm_tables;
165
166 x_purge_rec.group_id := p_group_id;
167 x_purge_rec.org_id := p_purge_request.organization_id;
168 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
169 x_purge_rec.schedule_id := NULL;
170 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
171 x_purge_rec.line_id := NULL;
172 x_purge_rec.start_date := p_purge_request.start_date;
173 x_purge_rec.complete_date := p_purge_request.complete_date;
174 x_purge_rec.close_date := p_purge_request.close_date;
175 x_purge_rec.info_type := ROWS_AFFECTED;
176 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
177 x_purge_rec.line_code := p_purge_request.line_code;
178 x_entity_type := p_purge_request.entity_type;
179
180 if x_entity_type not in (5,8) then
181 p_return_status := FND_API.G_RET_STS_SUCCESS;
182 return;
183 end if;
184
185
186 open get_purge_wtxnis (x_purge_rec.entity_name, x_purge_rec.wip_entity_id, x_purge_rec.org_id);
187
188 loop
189 FETCH get_purge_wtxnis into x_header_id, x_request_id;
190 EXIT when get_purge_wtxnis%NOTFOUND;
191
192 delete_wie(
193 p_option => p_option,
194 p_header_id => x_header_id,
195 p_request_id => x_request_id,
196 p_wie_num_rows => x_tmp_num_rows,
197 p_err_num => p_err_num,
198 p_err_buf => p_err_buf
199 );
200 if p_err_num <> 0 then
201 x_purge_rec.info := p_err_buf;
202 raise e_delete_wie_exception;
203 end if;
204
205 x_wie_rows := x_tmp_num_rows + x_wie_rows;
206
207 if (p_option = REPORT_ONLY) then
208 select count(*)
209 into x_tmp_num_rows
210 from WSM_STARTING_JOBS_INTERFACE
211 where header_id = x_header_id;
212 else
213
214 DELETE FROM WSM_STARTING_JOBS_INTERFACE
215 WHERE header_id = x_header_id;
216 x_tmp_num_rows := SQL%ROWCOUNT;
217
218 end if;
219
220 x_wsji_rows := x_tmp_num_rows + x_wsji_rows;
221
222 if (p_option = REPORT_ONLY) then
223 select count(*)
224 into x_tmp_num_rows
225 from WSM_RESULTING_JOBS_INTERFACE
226 where header_id = x_header_id;
227 else
228
229 DELETE FROM WSM_RESULTING_JOBS_INTERFACE
230 WHERE header_id = x_header_id;
231 x_tmp_num_rows := SQL%ROWCOUNT;
232
233 end if;
234
235 x_wrji_rows := x_tmp_num_rows + x_wrji_rows;
236
237 if (p_option = REPORT_ONLY) then
238 select count(*)
239 into x_tmp_num_rows
240 from WSM_SPLIT_MERGE_TXN_INTERFACE
241 where header_id = x_header_id;
242 else
243
244 DELETE FROM WSM_SPLIT_MERGE_TXN_INTERFACE
245 WHERE header_id = x_header_id;
246 x_tmp_num_rows := SQL%ROWCOUNT;
247
248 end if;
249
250 x_wsmti_rows := x_tmp_num_rows + x_wsmti_rows;
251
252 -- Bug 4722718 : Purge the Serial txn interface rows as well...
253 IF (p_option = REPORT_ONLY) THEN
254 SELECT count(*)
255 INTO x_tmp_num_rows
256 FROM WSM_SERIAL_TXN_INTERFACE
257 WHERE header_id = x_header_id
258 AND transaction_type_id = 3;
259 ELSE
260 DELETE FROM WSM_SERIAL_TXN_INTERFACE
261 WHERE header_id = x_header_id
262 AND transaction_type_id = 3;
263 x_tmp_num_rows := SQL%ROWCOUNT;
264 END IF;
265 l_serial_intf_rows := l_serial_intf_rows + x_tmp_num_rows;
266 -- Bug 4722718 : End
267
268 end loop;
269
270 close get_purge_wtxnis;
271
272 if x_wie_rows > 0 then
273
274 x_purge_rec.table_name := 'WSM_INTERFACE_ERRORS';
275
276 before_append_report(
277 p_option => p_option,
278 p_purge_rec => x_purge_rec,
279 num_rows => x_wie_rows);
280 x_wie_rows := 0;
281 end if;
282
283 if x_wsji_rows > 0 then
284
285 x_purge_rec.table_name := 'WSM_STARTING_JOBS_INTERFACE';
286
287 before_append_report(
288 p_option => p_option,
289 p_purge_rec => x_purge_rec,
290 num_rows => x_wsji_rows);
291 x_wsji_rows := 0;
292 end if;
293
294 if x_wrji_rows > 0 then
295
296 x_purge_rec.table_name := 'WSM_RESULTING_JOBS_INTERFACE';
297
298 before_append_report(
299 p_option => p_option,
300 p_purge_rec => x_purge_rec,
301 num_rows => x_wrji_rows);
302 x_wrji_rows := 0;
303 end if;
304
305 if x_wsmti_rows > 0 then
306
307 x_purge_rec.table_name := 'WSM_SPLIT_MERGE_TXN_INTERFACE';
308
309 before_append_report(
310 p_option => p_option,
311 p_purge_rec => x_purge_rec,
312 num_rows => x_wsmti_rows);
313 x_wsmti_rows := 0;
314 end if;
315
316 -- WIE ref WSM_LOT_JOB_INTERFACE
317 -- before delete wlji, purge WIE first
318 open get_purge_wlji (x_purge_rec.entity_name, x_purge_rec.org_id);
319
320 loop
321 FETCH get_purge_wlji into x_header_id, x_request_id;
322 EXIT when get_purge_wlji%NOTFOUND;
323
324 x_purge_rec.table_name := 'WSM_INTERFACE_ERRORS';
325
326 delete_wie(
327 p_option => p_option,
328 p_header_id => x_header_id,
329 p_request_id => x_request_id,
330 p_wie_num_rows => x_tmp_num_rows,
331 p_err_num => p_err_num,
332 p_err_buf => p_err_buf
333 );
334 if p_err_num <> 0 then
335 x_purge_rec.info := p_err_buf;
336 raise e_delete_wie_exception;
337 end if;
338
339 x_wie_rows := x_tmp_num_rows + x_wie_rows;
340
341 -- Bug 4722718 : Purge the Serial txn interface rows as well...
342 IF (p_option = REPORT_ONLY) THEN
343 SELECT count(*)
344 INTO x_tmp_num_rows
345 FROM WSM_SERIAL_TXN_INTERFACE
346 WHERE header_id = x_header_id
347 AND transaction_type_id = 1;
348 ELSE
349 DELETE FROM WSM_SERIAL_TXN_INTERFACE
350 WHERE header_id = x_header_id
351 AND transaction_type_id = 1;
352 x_tmp_num_rows := SQL%ROWCOUNT;
353 END IF;
354 l_serial_intf_rows := l_serial_intf_rows + x_tmp_num_rows;
355 -- Bug 4722718 : End
356
357 end loop;
358 close get_purge_wlji;
359
360 if x_wie_rows > 0 then
361
362 before_append_report(
363 p_option => p_option,
364 p_purge_rec => x_purge_rec,
365 num_rows => x_wie_rows);
366 x_wie_rows := 0;
367 end if;
368
369 x_purge_rec.table_name := 'WSM_LOT_JOB_INTERFACE';
370
371 if (p_option = REPORT_ONLY) then
372 select count(*)
373 into x_num_rows
374 from WSM_LOT_JOB_INTERFACE
375 where job_name = x_purge_rec.entity_name
376 and organization_id = x_purge_rec.org_id;
377 else
378
379 DELETE FROM WSM_LOT_JOB_INTERFACE
380 WHERE job_name = x_purge_rec.entity_name
381 AND organization_id = x_purge_rec.org_id;
382 x_num_rows := SQL%ROWCOUNT;
383
384 end if;
385
386 if x_num_rows > 0 then
387 before_append_report(
388 p_option => p_option,
389 p_purge_rec => x_purge_rec,
390 num_rows => x_num_rows);
391 end if;
392
393 -- WIE ref WSM_LOT_MOVE_TXN_INTERFACE
394
395 x_wie_rows := 0;
396
397 open get_purge_wlmti(x_purge_rec.wip_entity_id, x_purge_rec.org_id);
398
399 loop
400 FETCH get_purge_wlmti into x_header_id, x_request_id;
401 EXIT when get_purge_wlmti%NOTFOUND;
402
403 x_purge_rec.table_name := 'WSM_INTERFACE_ERRORS';
404
405 delete_wie(
406 p_option => p_option,
407 p_header_id => x_header_id,
408 p_request_id => x_request_id,
409 p_wie_num_rows => x_tmp_num_rows,
410 p_err_num => p_err_num,
411 p_err_buf => p_err_buf
412 );
413 if p_err_num <> 0 then
414 x_purge_rec.info := p_err_buf;
415 raise e_delete_wie_exception;
416 end if;
417
418 x_wie_rows := x_tmp_num_rows + x_wie_rows;
419
420 -- Bug 4722718 : Purge the Serial txn interface rows as well...
421 IF (p_option = REPORT_ONLY) THEN
422 SELECT count(*)
423 INTO x_tmp_num_rows
424 FROM WSM_SERIAL_TXN_INTERFACE
425 WHERE header_id = x_header_id
426 AND transaction_type_id = 2;
427 ELSE
428 DELETE FROM WSM_SERIAL_TXN_INTERFACE
429 WHERE header_id = x_header_id
430 AND transaction_type_id = 2;
431
432 x_tmp_num_rows := SQL%ROWCOUNT;
433 END IF;
434 l_serial_intf_rows := l_serial_intf_rows + x_tmp_num_rows;
435 -- Bug 4722718 : End
436
437 end loop;
438 close get_purge_wlmti;
439
440 if x_wie_rows > 0 then
441
442 before_append_report(
443 p_option => p_option,
444 p_purge_rec => x_purge_rec,
445 num_rows => x_wie_rows);
446 x_wie_rows := 0;
447 end if;
448
449 x_purge_rec.table_name := 'WSM_LOT_MOVE_TXN_INTERFACE';
450
451 if (p_option = REPORT_ONLY) then
452 select count(*)
453 into x_num_rows
454 from WSM_LOT_MOVE_TXN_INTERFACE
455 where wip_entity_id = x_purge_rec.wip_entity_id;
456
457 else
458
459 DELETE FROM WSM_LOT_MOVE_TXN_INTERFACE
460 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
461 x_num_rows := SQL%ROWCOUNT;
462
463 end if;
464
465 if x_num_rows > 0 then
466 before_append_report(
467 p_option => p_option,
468 p_purge_rec => x_purge_rec,
469 num_rows => x_num_rows);
470 end if;
471
472 -- Bug 4722718 : Start
473 x_purge_rec.table_name := 'WSM_SERIAL_TXN_INTERFACE';
474 IF l_serial_intf_rows > 0 THEN
475 before_append_report( p_option => p_option,
476 p_purge_rec => x_purge_rec,
477 num_rows => l_serial_intf_rows);
478 END IF;
479
480 x_purge_rec.table_name := 'WSM_RESERVATIONS';
481 IF (p_option = REPORT_ONLY) THEN
482 SELECT COUNT(*)
483 INTO x_num_rows
484 FROM WSM_RESERVATIONS
485 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
486 ELSE
487 DELETE FROM WSM_RESERVATIONS
488 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
489 x_num_rows := SQL%ROWCOUNT;
490 END IF;
491
492 IF x_num_rows > 0 THEN
493 before_append_report( p_option => p_option,
494 p_purge_rec => x_purge_rec,
495 num_rows => x_num_rows
496 );
497 END IF;
498
499 -- IF Clause added for Bug 4918553
500 -- Delete the data from the below tables only if the detail flag is set..
501 IF p_detail_flag THEN
502 x_purge_rec.table_name := 'WSM_OP_REASON_CODES';
503 IF (p_option = REPORT_ONLY) THEN
504 SELECT COUNT(*)
505 INTO x_num_rows
506 FROM WSM_OP_REASON_CODES
507 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
508 ELSE
509 DELETE FROM WSM_OP_REASON_CODES
510 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
511 x_num_rows := SQL%ROWCOUNT;
512 END IF;
513
514 IF x_num_rows > 0 THEN
515 before_append_report( p_option => p_option,
516 p_purge_rec => x_purge_rec,
517 num_rows => x_num_rows
518 );
519 END IF;
520
521 x_purge_rec.table_name := 'WSM_OP_SECONDARY_QUANTITIES';
522 IF (p_option = REPORT_ONLY) THEN
523 SELECT COUNT(*)
524 INTO x_num_rows
525 FROM WSM_OP_SECONDARY_QUANTITIES
526 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
527 ELSE
528 DELETE FROM WSM_OP_SECONDARY_QUANTITIES
529 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
530 x_num_rows := SQL%ROWCOUNT;
531 END IF;
532
533 IF x_num_rows > 0 THEN
534 before_append_report( p_option => p_option,
535 p_purge_rec => x_purge_rec,
536 num_rows => x_num_rows
537 );
538 END IF;
539
540 x_purge_rec.table_name := 'WIP_RESOURCE_ACTUAL_TIMES';
541 IF (p_option = REPORT_ONLY) THEN
542 SELECT COUNT(*)
543 INTO x_num_rows
544 FROM WIP_RESOURCE_ACTUAL_TIMES
545 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
546 ELSE
547 DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
548 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
549 x_num_rows := SQL%ROWCOUNT;
550 END IF;
551
552 IF x_num_rows > 0 THEN
553 before_append_report( p_option => p_option,
554 p_purge_rec => x_purge_rec,
555 num_rows => x_num_rows
556 );
557 END IF;
558
559 x_purge_rec.table_name := 'WSM_JOB_SECONDARY_QUANTITIES';
560 IF (p_option = REPORT_ONLY) THEN
561 SELECT COUNT(*)
562 INTO x_num_rows
563 FROM WSM_JOB_SECONDARY_QUANTITIES
564 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
565 ELSE
566 DELETE FROM WSM_JOB_SECONDARY_QUANTITIES
567 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
568 x_num_rows := SQL%ROWCOUNT;
569 END IF;
570
571 IF x_num_rows > 0 THEN
572 before_append_report( p_option => p_option,
573 p_purge_rec => x_purge_rec,
574 num_rows => x_num_rows
575 );
576 END IF;
577 -- Bug 4722718 : End
578
579 x_purge_rec.table_name := 'WIP_OPERATION_YIELDS';
580
581 if (p_option = REPORT_ONLY) then
582 select count(*)
583 into x_num_rows
584 from WIP_OPERATION_YIELDS
585 where wip_entity_id = x_purge_rec.wip_entity_id;
586
587 else
588
589 DELETE FROM WIP_OPERATION_YIELDS
590 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
591 x_num_rows := SQL%ROWCOUNT;
592
593 end if;
594
595 if x_num_rows > 0 then
596 before_append_report(
597 p_option => p_option,
598 p_purge_rec => x_purge_rec,
599 num_rows => x_num_rows);
600 end if;
601 END IF;
602 -- IF Clause added for Bug 4918553
603
604 x_purge_rec.table_name := 'WSM_COPY_OPERATIONS';
605
606 if (p_option = REPORT_ONLY) then
607 select count(*)
608 into x_num_rows
609 from WSM_COPY_OPERATIONS
610 where wip_entity_id = x_purge_rec.wip_entity_id;
611
612 else
613
614 DELETE FROM WSM_COPY_OPERATIONS
615 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
616 x_num_rows := SQL%ROWCOUNT;
617
618 end if;
619
620 if x_num_rows > 0 then
621 before_append_report(
622 p_option => p_option,
623 p_purge_rec => x_purge_rec,
624 num_rows => x_num_rows);
625 end if;
626
627 x_purge_rec.table_name := 'WSM_COPY_OP_NETWORKS';
628
629 if (p_option = REPORT_ONLY) then
630 select count(*)
631 into x_num_rows
632 from WSM_COPY_OP_NETWORKS
633 where wip_entity_id = x_purge_rec.wip_entity_id;
634
635 else
636
637 DELETE FROM WSM_COPY_OP_NETWORKS
638 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
639 x_num_rows := SQL%ROWCOUNT;
640
641 end if;
642
643 if x_num_rows > 0 then
644 before_append_report(
645 p_option => p_option,
646 p_purge_rec => x_purge_rec,
647 num_rows => x_num_rows);
648 end if;
649
650 x_purge_rec.table_name := 'WSM_COPY_OP_RESOURCES';
651
652 if (p_option = REPORT_ONLY) then
653 select count(*)
654 into x_num_rows
655 from WSM_COPY_OP_RESOURCES
656 where wip_entity_id = x_purge_rec.wip_entity_id;
657
658 else
659
660 DELETE FROM WSM_COPY_OP_RESOURCES
661 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
662 x_num_rows := SQL%ROWCOUNT;
663
664 end if;
665
666 if x_num_rows > 0 then
667 before_append_report(
668 p_option => p_option,
669 p_purge_rec => x_purge_rec,
670 num_rows => x_num_rows);
671 end if;
672
673 x_purge_rec.table_name := 'WSM_COPY_OP_RESOURCE_INSTANCES';
674
675 if (p_option = REPORT_ONLY) then
676 select count(*)
677 into x_num_rows
678 from WSM_COPY_OP_RESOURCE_INSTANCES
679 where wip_entity_id = x_purge_rec.wip_entity_id;
680
681 else
682
683 DELETE FROM WSM_COPY_OP_RESOURCE_INSTANCES
684 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
685 x_num_rows := SQL%ROWCOUNT;
686
687 end if;
688
689 if x_num_rows > 0 then
690 before_append_report(
691 p_option => p_option,
692 p_purge_rec => x_purge_rec,
693 num_rows => x_num_rows);
694 end if;
695
696
697 x_purge_rec.table_name := 'WSM_COPY_OP_RESOURCE_USAGE';
698
699 if (p_option = REPORT_ONLY) then
700 select count(*)
701 into x_num_rows
702 from WSM_COPY_OP_RESOURCE_USAGE
703 where wip_entity_id = x_purge_rec.wip_entity_id;
704
705 else
706
707 DELETE FROM WSM_COPY_OP_RESOURCE_USAGE
708 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
709 x_num_rows := SQL%ROWCOUNT;
710
711 end if;
712
713 if x_num_rows > 0 then
714 before_append_report(
715 p_option => p_option,
716 p_purge_rec => x_purge_rec,
717 num_rows => x_num_rows);
718 end if;
719
720
721 x_purge_rec.table_name := 'WSM_COPY_REQUIREMENT_OPS';
722
723 if (p_option = REPORT_ONLY) then
724 select count(*)
725 into x_num_rows
726 from WSM_COPY_REQUIREMENT_OPS
727 where wip_entity_id = x_purge_rec.wip_entity_id;
728
729 else
730
731 DELETE FROM WSM_COPY_REQUIREMENT_OPS
732 WHERE wip_entity_id = x_purge_rec.wip_entity_id;
733 x_num_rows := SQL%ROWCOUNT;
734
735 end if;
736
737 if x_num_rows > 0 then
738 before_append_report(
739 p_option => p_option,
740 p_purge_rec => x_purge_rec,
741 num_rows => x_num_rows);
742 end if;
743
744
745 -- WSM_LOT_BASED_OPERATIONS attachment
746 if (p_Option <> REPORT_ONLY) then
747 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
748 X_entity_name => 'WSM_LOT_BASED_OPERATIONS',
749 X_pk1_value => to_char(x_purge_rec.wip_entity_id),
750 X_pk2_value => to_char(x_purge_rec.org_id),
751 X_delete_document_flag => 'Y' );
752 end if ;
753 p_return_status := FND_API.G_RET_STS_SUCCESS;
754 exception
755 when e_delete_wie_exception then
756 append_report(x_purge_rec, p_option);
757 rollback to osfm_tables;
758 p_return_status := FND_API.G_RET_STS_ERROR;
759 when others then
760 p_err_num := SQLCODE;
761 x_purge_rec.info := SUBSTR(SQLERRM, 1, 500);
762 append_report(x_purge_rec, p_option);
763 rollback to osfm_tables;
764 p_return_status := FND_API.G_RET_STS_ERROR;
765
766 end delete_osfm_tables;
767
768 END WSM_JobPurge_GRP;