[Home] [Help]
PACKAGE BODY: APPS.WIP_WICTPG
Source
1 package body wip_wictpg as
2 /* $Header: wiptpgb.pls 120.12.12000000.3 2007/07/18 01:28:17 rrajkule ship $ */
3
4 -- procedure to insert a line into the report
5 procedure append_report(
6 p_rec in purge_report_type,
7 p_option in number) is
8 begin
9 if ( p_option IN (REPORT_ONLY, PURGE_AND_REPORT) ) then
10 insert into Wip_temp_reports(
11 key1, /* Group ID */
12 program_source,
13 last_updated_by,
14 organization_id,
15 wip_entity_id,
16 inventory_item_id,
17 key2, /* line ID */
18 key3, /* Repetitive schedule ID */
19 description, /* Table Name */
20 key6, /* Info Type */
21 attribute1 /* Info */,
22 date1, /* Start Date */
23 date2, /* Completion Date */
24 date3, /* Close Date */
25 attribute2, /*Job Name*/
26 attribute3 /*Line Code*/
27 ) values (
28 p_rec.group_id,
29 'WICTPG', /* program_source */
30 -1, /* Last Updated By */
31 p_rec.org_id,
32 p_rec.wip_entity_id,
33 p_rec.primary_item_id,
34 p_rec.line_id,
35 p_rec.schedule_id,
36 p_rec.table_name,
37 p_rec.info_type,
38 p_rec.info,
39 p_rec.start_date,
40 p_rec.complete_date,
41 p_rec.close_date,
42 p_rec.entity_name,
43 p_rec.line_code
44 );
45 end if ;
46
47 end append_report;
48
49
50 -- Procedure to delete the records entered in the WIP_PURGE_TEMP table
51 procedure delete_purge_temp_table(
52 p_group_id in number ) is
53
54 begin
55
56 delete from Wip_Purge_Temp where group_id = p_group_id ;
57 commit ;
58
59 end delete_purge_temp_table ;
60
61
62 procedure construct_report_content(p_option in number,
63 p_num_rows in number,
64 p_purge_rec in out nocopy purge_report_type) is
65
66 begin
67 if ( p_num_rows > 0 ) then
68 fnd_message.set_name('WIP', 'WIP_PURGE_ROWS');
69 fnd_message.set_token('NUMBER', to_char(p_num_rows));
70 p_purge_rec.info := fnd_message.get;
71 append_report(p_purge_rec, p_option);
72 end if;
73 end construct_report_content;
74
75
76 /*
77 -- procedure to delete from a table and get a count of records
78 procedure delete_from_table(
79 p_option in number,
80 p_purge_rec in purge_report_type,
81 p_delete_statement in varchar2) is
82
83 x_cursor_id number;
84 x_num_rows number := 0;
85 x_message varchar2(255);
86 x_purge_rec purge_report_type;
87 begin
88 -- issue savepoint to restore deletes later
89 savepoint wictpg_sp;
90
91 -- delete from table using dynamic SQL
92 x_cursor_id := dbms_sql.open_cursor;
93 dbms_sql.parse(x_cursor_id, p_delete_statement, dbms_sql.v7);
94 x_num_rows := dbms_sql.execute(x_cursor_id);
95 dbms_sql.close_cursor(x_cursor_id);
96
97 if (p_option = REPORT_ONLY) then
98 -- rollback deletes
99 rollback to wictpg_sp;
100 end if;
101
102 if (x_num_rows > 0) then
103 fnd_message.set_name('WIP', 'WIP_PURGE_ROWS');
104 fnd_message.set_token('NUMBER', to_char(x_num_rows));
105 x_message := fnd_message.get;
106 x_purge_rec := p_purge_rec;
107 x_purge_rec.info := x_message;
108 append_report(x_purge_rec,p_option);
109 end if;
110
111 end delete_from_table;
112 */
113
114 /* Bug# 1280455
115 ** New procedure created to do the same work as done by
116 ** delete_from_table except the dynamic sql execution.
117 */
118 procedure before_append_report(
119 p_option in number,
120 p_purge_rec in purge_report_type,
121 num_rows in number) is
122 x_purge_rec purge_report_type;
123 begin
124
125 fnd_message.set_name('WIP', 'WIP_PURGE_ROWS');
126 fnd_message.set_token('NUMBER', to_char(num_rows));
127 x_purge_rec := p_purge_rec;
128 x_purge_rec.info := fnd_message.get;
129 append_report(x_purge_rec,p_option);
130
131 end before_append_report;
132
133
134 -- procedure to delete the details of a job
135 procedure delete_job_details(
136 p_option in number,
137 p_group_id in number,
138 p_purge_request in get_purge_requests%rowtype) is
139
140 x_num_rows number := 0;
141 x_purge_rec purge_report_type;
142 l_op_count number := 0; /*Bug 6056455: (FP of 5224338) Added variable l_op_count to store operation count*/
143 begin
144
145 x_purge_rec.group_id := p_group_id;
146 x_purge_rec.org_id := p_purge_request.organization_id;
147 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
148 x_purge_rec.schedule_id := NULL;
149 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
150 x_purge_rec.line_id := NULL;
151 x_purge_rec.start_date := p_purge_request.start_date;
152 x_purge_rec.complete_date := p_purge_request.complete_date;
153 x_purge_rec.close_date := p_purge_request.close_date;
154 x_purge_rec.info_type := ROWS_AFFECTED;
155 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
156 x_purge_rec.line_code := p_purge_request.line_code;
157
158 x_purge_rec.table_name := 'WIP_OPERATIONS';
159 if (p_option = REPORT_ONLY) then
160 select count(*) into x_num_rows from WIP_OPERATIONS where
161 WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
162 else
163 DELETE FROM WIP_OPERATIONS
164 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
165 x_num_rows := SQL%ROWCOUNT;
166 end if;
167
168 if x_num_rows > 0 then
169 before_append_report(
170 p_option => p_option,
171 p_purge_rec => x_purge_rec,
172 num_rows => x_num_rows);
173 end if;
174 x_purge_rec.table_name := 'WIP_REQUIREMENT_OPERATIONS';
175 if (p_option = REPORT_ONLY) then
176 select count(*) into x_num_rows from WIP_REQUIREMENT_OPERATIONS where
177 WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
178 else
179 DELETE FROM WIP_REQUIREMENT_OPERATIONS
180 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
181 x_num_rows := SQL%ROWCOUNT;
182 end if;
183
184 l_op_count := x_num_rows; /*Bug 6056455: (FP of 5224338) Store operation count in variable l_op_count*/
185
186 if x_num_rows > 0 then
187 before_append_report(
188 p_option => p_option,
189 p_purge_rec => x_purge_rec,
190 num_rows => x_num_rows);
191 end if;
192
193 x_purge_rec.table_name := 'WIP_OPERATION_RESOURCES';
194 if (p_option = REPORT_ONLY) then
195 select count(*) into x_num_rows from WIP_OPERATION_RESOURCES where
196 WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
197 else
198 DELETE FROM WIP_OPERATION_RESOURCES
199 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
200 x_num_rows := SQL%ROWCOUNT;
201 end if;
202
203 if x_num_rows > 0 then
204 before_append_report(
205 p_option => p_option,
206 p_purge_rec => x_purge_rec,
207 num_rows => x_num_rows);
208 end if;
209
210 /*fix for bug no 4774572*/
211 x_purge_rec.table_name := 'WIP_OP_RESOURCE_INSTANCES';
212 if (p_option = REPORT_ONLY) then
213 select count(*) into x_num_rows from WIP_OP_RESOURCE_INSTANCES where
214 WIP_ENTITY_ID= x_purge_rec.wip_entity_id
215 AND ORGANIZATION_ID = x_purge_rec.org_id;
216 else
217 DELETE FROM WIP_OP_RESOURCE_INSTANCES
218 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
219 AND ORGANIZATION_ID = x_purge_rec.org_id;
220 x_num_rows := SQL%ROWCOUNT;
221 end if;
222
223 if x_num_rows > 0 then
224 before_append_report(
225 p_option => p_option,
226 p_purge_rec => x_purge_rec,
227 num_rows => x_num_rows);
228 end if;
229 /* end fix for bug#4774572*/
230
231 x_purge_rec.table_name := 'WIP_SHOP_FLOOR_STATUSES';
232 if (p_option = REPORT_ONLY) then
233 select count(*) into x_num_rows from WIP_SHOP_FLOOR_STATUSES where
234 WIP_ENTITY_ID= x_purge_rec.wip_entity_id;
235 else
236 DELETE FROM WIP_SHOP_FLOOR_STATUSES
237 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
238 x_num_rows := SQL%ROWCOUNT;
239 end if;
240
241 if x_num_rows > 0 then
242 before_append_report(
243 p_option => p_option,
244 p_purge_rec => x_purge_rec,
245 num_rows => x_num_rows);
246 end if;
247
248 /* Fix for bug 2146528*/
249 x_purge_rec.table_name := 'WIP_OPERATION_OVERHEADS';
250 if (p_option = REPORT_ONLY) then
251 select count(*) into x_num_rows from WIP_OPERATION_OVERHEADS
252 where WIP_ENTITY_ID=to_char(x_purge_rec.wip_entity_id);
253 else
254 DELETE FROM WIP_OPERATION_OVERHEADS
255 WHERE WIP_ENTITY_ID = to_char(x_purge_rec.wip_entity_id);
256 x_num_rows := SQL%ROWCOUNT;
257 end if;
258
259 if x_num_rows > 0 then
260 before_append_report(
261 p_option => p_option,
262 p_purge_rec => x_purge_rec,
263 num_rows => x_num_rows);
264 end if;
265
266 x_purge_rec.table_name := 'WIP_REQ_OPERATION_COST_DETAILS';
267
268 if (p_option = REPORT_ONLY) then
269 select count(*) into x_num_rows from WIP_REQ_OPERATION_COST_DETAILS
270 where WIP_ENTITY_ID=to_char(x_purge_rec.wip_entity_id);
271 else
272 DELETE FROM WIP_REQ_OPERATION_COST_DETAILS
273 WHERE WIP_ENTITY_ID = to_char(x_purge_rec.wip_entity_id);
274 x_num_rows := SQL%ROWCOUNT;
275 end if;
276
277 if x_num_rows > 0 then
278 before_append_report(
279 p_option => p_option,
280 p_purge_rec => x_purge_rec,
281 num_rows => x_num_rows);
282 end if;
283
284 if ( p_option IN (REPORT_ONLY, PURGE_AND_REPORT) ) then
285 -- This procedure is used for counting only, later it will be deleted
286 x_purge_rec.table_name := 'FND_ATTACHED_DOCUMENTS';
287
288 /* Removed commented code */
289 /* Bug 2943615 - modified wip_entity_id, org_id on the R.H.S to char
290 to avoid ORA-1722 error */
291 select count(*)
292 into x_num_rows
293 from FND_ATTACHED_DOCUMENTS
294 WHERE PK1_VALUE = to_char(x_purge_rec.wip_entity_id)
295 AND (( PK2_VALUE = to_char(x_purge_rec.org_id)
296 AND ENTITY_NAME = 'WIP_DISCRETE_JOBS'
297 )
298 OR ( PK3_VALUE = to_char(x_purge_rec.org_id)
299 AND ENTITY_NAME = 'WIP_DISCRETE_OPERATIONS'
300 )
301 );
302
303 if x_num_rows > 0 then
304 before_append_report(
305 p_option => p_option,
306 p_purge_rec => x_purge_rec,
307 num_rows => x_num_rows);
308 end if;
309 end if;
310
311 -- If the action type option is to purge then call the API supplied by dlane
312 if (p_Option <> REPORT_ONLY) then
313
314 /* Bug 6056455: (FP of 5224338) Added following if condition before calling FND API to delete attachments*/
315 if ( l_op_count > 0) then
316 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
317 X_entity_name => 'WIP_DISCRETE_OPERATIONS',
318 X_pk1_value => to_char(x_purge_rec.wip_entity_id),
319 X_pk3_value => to_char(x_purge_rec.org_id),
320 X_delete_document_flag => 'Y' );
321 end if;
322
323 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
324 X_entity_name => 'WIP_DISCRETE_JOBS',
325 X_pk1_value => to_char(x_purge_rec.wip_entity_id),
326 X_pk2_value => to_char(x_purge_rec.org_id),
327 X_delete_document_flag => 'Y' );
328 end if ;
329
330 end delete_job_details;
331
332 -- procedure to delete the details of a schedule
333 procedure delete_sched_details(
334 p_option in number,
335 p_group_id in number,
336 p_purge_request in get_purge_requests%rowtype) is
337
338 x_purge_rec purge_report_type;
339 x_num_rows number := 0;
340 begin
341
342 x_purge_rec.group_id := p_group_id;
343 x_purge_rec.org_id := p_purge_request.organization_id;
344 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
345 x_purge_rec.schedule_id := p_purge_request.repetitive_schedule_id;
346 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
347 x_purge_rec.line_id := p_purge_request.line_id;
348 x_purge_rec.start_date := p_purge_request.start_date;
349 x_purge_rec.complete_date := p_purge_request.complete_date;
350 x_purge_rec.close_date := p_purge_request.close_date;
351 x_purge_rec.info_type := ROWS_AFFECTED;
352 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
353 x_purge_rec.line_code := p_purge_request.line_code;
354
355 x_purge_rec.table_name := 'WIP_OPERATIONS';
356 if (p_option = REPORT_ONLY) then
357 select count(*) into x_num_rows from WIP_OPERATIONS
358 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
359 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
360 AND ORGANIZATION_ID = x_purge_rec.org_id;
361 else
362 DELETE FROM WIP_OPERATIONS
363 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
364 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
365 AND ORGANIZATION_ID = x_purge_rec.org_id;
366 x_num_rows := SQL%ROWCOUNT;
367 end if;
368
369 if x_num_rows > 0 then
370 before_append_report(
371 p_option => p_option,
372 p_purge_rec => x_purge_rec,
373 num_rows => x_num_rows);
374 end if;
375
376
377 x_purge_rec.table_name := 'WIP_REQUIREMENT_OPERATIONS';
378 if (p_option = REPORT_ONLY) then
379 select count(*) into x_num_rows from WIP_REQUIREMENT_OPERATIONS
380 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
381 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
382 else
383 DELETE FROM WIP_REQUIREMENT_OPERATIONS
384 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
385 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
386 x_num_rows := SQL%ROWCOUNT;
387 end if;
388
389 if x_num_rows > 0 then
390 before_append_report(
391 p_option => p_option,
392 p_purge_rec => x_purge_rec,
393 num_rows => x_num_rows);
394 end if;
395
396 x_purge_rec.table_name := 'WIP_OPERATION_RESOURCES';
397 if (p_option = REPORT_ONLY) then
398 select count(*) into x_num_rows from WIP_OPERATION_RESOURCES
399 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
400 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
401 else
402 DELETE FROM WIP_OPERATION_RESOURCES
403 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
404 AND REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
405 x_num_rows := SQL%ROWCOUNT;
406 end if;
407
408 if x_num_rows > 0 then
409 before_append_report(
410 p_option => p_option,
411 p_purge_rec => x_purge_rec,
412 num_rows => x_num_rows);
413 end if;
414
415 /*fix for bug no 4774572*/
416 x_purge_rec.table_name := 'WIP_OP_RESOURCE_INSTANCES';
417 if (p_option = REPORT_ONLY) then
418 select count(*) into x_num_rows from WIP_OP_RESOURCE_INSTANCES
419 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
420 AND ORGANIZATION_ID = x_purge_rec.org_id;
421 else
422 DELETE FROM WIP_OP_RESOURCE_INSTANCES
423 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
424 AND ORGANIZATION_ID = x_purge_rec.org_id;
425 x_num_rows := SQL%ROWCOUNT;
426 end if;
427
428 if x_num_rows > 0 then
429 before_append_report(
430 p_option => p_option,
431 p_purge_rec => x_purge_rec,
432 num_rows => x_num_rows);
433 end if;
434 /* end fix for bug#4774572*/
435
436
437
438 if ( p_option IN (REPORT_ONLY, PURGE_AND_REPORT) ) then
439 -- This procedure is used for counting only, later it will be deleted
440 x_purge_rec.table_name := 'FND_ATTACHED_DOCUMENTS';
441
442 /* Removed commented code */
443 /* Bug 2943615 - modified wip_entity_id, org_id, schedule_id on the R.H.S to char
444 to avoid ORA-1722 error */
445 select count(*)
446 into x_num_rows
447 from FND_ATTACHED_DOCUMENTS
448 WHERE PK1_VALUE = to_char(x_purge_rec.wip_entity_id)
449 AND PK3_VALUE = to_char(x_purge_rec.org_id)
450 AND ( ( PK2_VALUE = to_char(x_purge_rec.schedule_id)
451 AND ENTITY_NAME = 'WIP_REPETITIVE_SCHEDULES'
452 )
453 OR ( PK4_VALUE = to_char(x_purge_rec.schedule_id)
454 AND ENTITY_NAME = 'WIP_REPETITIVE_OPERATIONS'
455 )
456 ) ;
457
458 if x_num_rows > 0 then
459 before_append_report(
460 p_option => p_option,
461 p_purge_rec => x_purge_rec,
462 num_rows => x_num_rows);
463 end if;
464
465
466 end if;
467
468
469 -- If the action type option is to purge then call the API supplied by dlane
470 if (p_Option <> REPORT_ONLY) then
471 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
472 X_entity_name => 'WIP_REPETITIVE_OPERATIONS',
473 X_pk1_value => to_char(x_purge_rec.wip_entity_id),
474 X_pk3_value => to_char(x_purge_rec.org_id),
475 X_pk4_value => to_char(x_purge_rec.schedule_id),
476 X_delete_document_flag => 'Y' );
477
478 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
479 X_entity_name => 'WIP_REPETITIVE_SCHEDULES',
480 X_pk1_value => to_char(x_purge_rec.wip_entity_id),
481 X_pk2_value => to_char(x_purge_rec.schedule_id),
482 X_pk3_value => to_char(x_purge_rec.org_id),
483 X_delete_document_flag => 'Y' );
484 end if ;
485
486 end delete_sched_details;
487
488 -- procedure to delete job move transactions
489 procedure delete_job_move_trx(
490 p_option in number,
491 p_group_id in number,
492 p_purge_request in get_purge_requests%rowtype) is
493
494 x_purge_rec purge_report_type;
495 x_num_rows number := 0;
496 begin
497
498 x_purge_rec.group_id := p_group_id;
499 x_purge_rec.org_id := p_purge_request.organization_id;
500 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
501 x_purge_rec.schedule_id := NULL;
502 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
503 x_purge_rec.line_id := NULL;
504 x_purge_rec.start_date := p_purge_request.start_date;
505 x_purge_rec.complete_date := p_purge_request.complete_date;
506 x_purge_rec.close_date := p_purge_request.close_date;
507 x_purge_rec.info_type := ROWS_AFFECTED;
508 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
509 x_purge_rec.line_code := p_purge_request.line_code;
510
511 x_purge_rec.table_name := 'WIP_SERIAL_MOVE_TRANSACTIONS';
512 if ( p_option = REPORT_ONLY ) then
513 select count(*) into x_num_rows
514 from wip_serial_move_transactions wsmt
515 where wsmt.transaction_id in (select wmt.transaction_id
516 from wip_move_transactions wmt
517 where wmt.wip_entity_id = x_purge_rec.wip_entity_id);
518 else
519 delete from wip_serial_move_transactions
520 where transaction_id in (select wmt.transaction_id
521 from wip_move_transactions wmt
522 where wmt.wip_entity_id = x_purge_rec.wip_entity_id);
523 x_num_rows := SQL%ROWCOUNT;
524 end if;
525 if x_num_rows > 0 then
526 before_append_report(
527 p_option => p_option,
528 p_purge_rec => x_purge_rec,
529 num_rows => x_num_rows);
530 end if;
531
532 x_num_rows := 0;
533 x_purge_rec.table_name := 'WIP_MOVE_TRANSACTIONS';
534
535 if (p_option = REPORT_ONLY) then
536 select count(*) into x_num_rows from WIP_MOVE_TRANSACTIONS
537 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
538 else
539 DELETE FROM WIP_MOVE_TRANSACTIONS
540 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
541 x_num_rows := SQL%ROWCOUNT;
542 end if;
543
544 if x_num_rows > 0 then
545 before_append_report(
546 p_option => p_option,
547 p_purge_rec => x_purge_rec,
548 num_rows => x_num_rows);
549 end if;
550
551 end delete_job_move_trx;
552
553
554
555 /*
556 -- procedure to check if open scheules exists for a transaction
557 procedure verify_open_schedules(
558 p_option in number,
559 p_purge_rec in purge_report_type,
560 p_sql in varchar2,
561 p_rec_exists in out nocopy boolean) is
562
563 x_cursor_id number;
564 x_count number := 0;
565 x_purge_rec purge_report_type;
566 x_ret number := 0 ;
567 begin
568
569
570 x_cursor_id := dbms_sql.open_cursor;
571 dbms_sql.parse(x_cursor_id, p_sql, dbms_sql.v7);
572 dbms_sql.define_column(x_cursor_id, 1, x_count);
573 x_ret := dbms_sql.execute(x_cursor_id);
574 x_ret := dbms_sql.fetch_rows(x_cursor_id);
575 dbms_sql.column_value(x_cursor_id, 1, x_count);
576 dbms_sql.close_cursor(x_cursor_id);
577
578 if (x_count > 0) then
579 p_rec_exists := TRUE ;
580 append_report(p_purge_rec,p_option);
581 end if;
582
583
584 end verify_Open_Schedules;
585 */
586
587
588
589
590
591 -- procedure to delete schedule move transactions
592 -- this still needs work
593 procedure delete_sched_move_trx(
594 p_option in number,
595 p_group_id in number,
596 p_purge_request in get_purge_requests%rowtype,
597 p_cutoff_date in date,
598 p_sched_move_txn_flag in out nocopy boolean) is
599
600 x_purge_rec purge_report_type;
601 x_records_found boolean := FALSE ;
602 l_num_rows number := 0;
603 begin
604
605 x_purge_rec.group_id := p_group_id;
606 x_purge_rec.org_id := p_purge_request.organization_id;
607 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
608 x_purge_rec.schedule_id := p_purge_request.repetitive_schedule_id;
609 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
610 x_purge_rec.line_id := p_purge_request.line_id;
611 x_purge_rec.start_date := p_purge_request.start_date;
612 x_purge_rec.complete_date := p_purge_request.complete_date;
613 x_purge_rec.close_date := p_purge_request.close_date;
614 x_purge_rec.info_type := EXCEPTIONS;
615 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
616 x_purge_rec.line_code := p_purge_request.line_code;
617
618
619 -- verify that no other schedules exists
620 fnd_message.set_name('WIP', 'WIP_TRANSACTIONS_PURGE_ERROR');
621 x_purge_rec.info := fnd_message.get;
622 x_purge_rec.table_name := 'WIP_MOVE_TXN_ALLOCATIONS';
623 SELECT COUNT(*) into l_num_rows
624 FROM DUAL WHERE EXISTS (
625 SELECT out_wmta.transaction_id
626 FROM wip_move_txn_allocations out_wmta
627 WHERE out_wmta.organization_id = p_purge_request.organization_id
628 AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
629 AND transaction_id IN (
630 SELECT transaction_id
631 FROM wip_repetitive_schedules wrs,
632 wip_move_txn_allocations wmta
633 WHERE wmta.transaction_id = out_wmta.transaction_id
634 AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
635 AND nvl(wrs.date_closed, p_cutoff_date+1) > p_cutoff_date
636 AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
637 AND wmta.organization_id = p_purge_request.organization_id
638 AND wrs.organization_id = p_purge_request.organization_id));
639 if ( l_num_rows > 0 ) then
640 x_records_found := true;
641 append_report(x_purge_rec, p_option);
642 end if;
643 l_num_rows := 0;
644
645 -- If records whre found then it means that there records still sitting
646 -- in the WIP_MOVE_TRANSACTIONS table.
647
648 if x_records_found then
649 p_sched_move_txn_flag := TRUE ;
650 x_purge_rec.table_name := 'WIP_MOVE_TRANSACTIONS';
651 append_report(x_purge_rec,p_option);
652 end if ;
653
654
655 x_purge_rec.info_type := ROWS_AFFECTED;
656 x_purge_rec.table_name := 'WIP_MOVE_TRANSACTIONS';
657
658 savepoint wictpg_sp01;
659 DELETE FROM WIP_MOVE_TRANSACTIONS
660 WHERE TRANSACTION_ID IN (
661 SELECT out_wmta.transaction_id
662 FROM wip_move_txn_allocations out_wmta
663 WHERE out_wmta.organization_id = p_purge_request.organization_id
664 AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
665 AND out_wmta.transaction_id IN (
666 SELECT transaction_id
667 FROM wip_repetitive_schedules wrs,
668 wip_move_txn_allocations wmta
669 WHERE wmta.transaction_id = out_wmta.transaction_id
670 AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
671 AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
672 AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
673 AND wmta.organization_id = p_purge_request.organization_id
674 AND wrs.organization_id = p_purge_request.organization_id));
675 l_num_rows := sql%rowcount;
676 if ( p_option = REPORT_ONLY) then
677 -- rollback deletes
678 rollback to wictpg_sp01;
679 end if;
680 construct_report_content(p_option => p_option,
681 p_num_rows => l_num_rows,
682 p_purge_rec => x_purge_rec);
683 l_num_rows := 0;
684
685
686 x_purge_rec.table_name := 'WIP_MOVE_TXN_ALLOCATIONS';
687
688 savepoint wictpg_sp02;
689 DELETE FROM WIP_MOVE_TXN_ALLOCATIONS
690 WHERE TRANSACTION_ID IN (
691 SELECT out_wmta.transaction_id
692 FROM wip_move_txn_allocations out_wmta
693 WHERE out_wmta.organization_id = p_purge_request.organization_id
694 AND out_wmta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
695 AND out_wmta.transaction_id IN (
696 SELECT transaction_id
697 FROM wip_repetitive_schedules wrs,
698 wip_move_txn_allocations wmta
699 WHERE wmta.transaction_id = out_wmta.transaction_id
700 AND wmta.repetitive_schedule_id <> out_wmta.repetitive_schedule_id
701 AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
702 AND wrs.repetitive_schedule_id = wmta.repetitive_schedule_id
703 AND wmta.organization_id = p_purge_request.organization_id
704 AND wrs.organization_id = p_purge_request.organization_id));
705 l_num_rows := sql%rowcount;
706 if ( p_option = REPORT_ONLY) then
707 -- rollback deletes
708 rollback to wictpg_sp02;
709 end if;
710 construct_report_content(p_option => p_option,
711 p_num_rows => l_num_rows,
712 p_purge_rec => x_purge_rec);
713 l_num_rows := 0;
714
715 end delete_sched_move_trx;
716
717
718
719 -- procedure to delete job resource transactions
720 procedure delete_job_cost_trx(
721 p_option in number,
722 p_group_id in number,
723 p_purge_request in get_purge_requests%rowtype,
724 p_cut_off_date in date) is /*bug 4082908*/
725 x_purge_rec purge_report_type;
726 x_num_rows number := 0;
727 x_num_rows_non_lot number := 0;
728 x_num_rows_lot number := 0;
729 begin
730
731 x_purge_rec.group_id := p_group_id;
732 x_purge_rec.org_id := p_purge_request.organization_id;
733 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
734 x_purge_rec.schedule_id := NULL;
735 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
736 x_purge_rec.line_id := NULL;
737 x_purge_rec.start_date := p_purge_request.start_date;
738 x_purge_rec.complete_date := p_purge_request.complete_date;
739 x_purge_rec.close_date := p_purge_request.close_date;
740 x_purge_rec.info_type := ROWS_AFFECTED;
741 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
742 x_purge_rec.line_code := p_purge_request.line_code;
743
744
745 x_purge_rec.table_name := 'WIP_TRANSACTION_ACCOUNTS';
746 /* Bug 4082908 -> Changed following deletion statement to consider
747 lot-merge transactions, so that they are purged as a unit */
748 if (p_option = REPORT_ONLY) then
749 SELECT count(*) into x_num_rows_non_lot
750 FROM WIP_TRANSACTION_ACCOUNTS WTA
751 WHERE WTA.TRANSACTION_ID IN
752 ( SELECT WT.TRANSACTION_ID
753 FROM WIP_TRANSACTIONS WT
754 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
755 AND WT.TRANSACTION_TYPE NOT IN (11,12));
756 SELECT count(*) into x_num_rows_lot
757 FROM WIP_TRANSACTION_ACCOUNTS WTA
758 WHERE WTA.TRANSACTION_ID IN
759 ( SELECT WT.TRANSACTION_ID
760 FROM WIP_TRANSACTIONS WT
761 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
762 AND WT.TRANSACTION_TYPE IN (11,12)
763 AND NOT EXISTS (SELECT 1 FROM
764 WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
765 WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
766 AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
767 AND NVL(WDJ.DATE_CLOSED,SYSDATE) >= p_cut_off_date)
768 UNION
769 SELECT WT.TRANSACTION_ID
770 FROM WIP_TRANSACTION_ACCOUNTS WT
771 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
772 AND NOT EXISTS
773 ( SELECT 1 FROM
774 WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
775 WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
776 AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
777 AND NVL(WDJ.DATE_CLOSED,SYSDATE) > p_cut_off_date)
778 );
779 else
780 DELETE FROM WIP_TRANSACTION_ACCOUNTS WTA
781 WHERE WTA.TRANSACTION_ID IN
782 ( SELECT WT.TRANSACTION_ID
783 FROM WIP_TRANSACTIONS WT
784 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
785 AND WT.TRANSACTION_TYPE NOT IN (11,12));
786
787 x_num_rows_non_lot := nvl(SQL%ROWCOUNT,0);
788
789 DELETE FROM WIP_TRANSACTION_ACCOUNTS WTA
790 WHERE WTA.TRANSACTION_ID IN
791 ( SELECT WT.TRANSACTION_ID
792 FROM WIP_TRANSACTIONS WT
793 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
794 AND WT.TRANSACTION_TYPE IN (11,12)
795 AND NOT EXISTS (SELECT 1 FROM
796 WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
797 WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
798 AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
799 AND NVL(WDJ.DATE_CLOSED,SYSDATE) >= p_cut_off_date)
800 UNION
801 SELECT WT.TRANSACTION_ID
802 FROM WIP_TRANSACTION_ACCOUNTS WT
803 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
804 AND NOT EXISTS
805 ( SELECT 1 FROM
806 WIP_TRANSACTION_ACCOUNTS WTA1, WIP_DISCRETE_JOBS WDJ
807 WHERE WTA1.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
808 AND WTA1.TRANSACTION_ID=WT.TRANSACTION_ID
809 AND NVL(WDJ.DATE_CLOSED,SYSDATE) > p_cut_off_date)
810 );
811
812 x_num_rows_lot := nvl(SQL%ROWCOUNT,0);
813 end if;
814
815 if (x_num_rows_non_lot + x_num_rows_lot) > 0 then
816 before_append_report(
817 p_option => p_option,
818 p_purge_rec => x_purge_rec,
819 num_rows => x_num_rows_non_lot + x_num_rows_lot);
820 end if;
821
822 x_purge_rec.table_name := 'WIP_TRANSACTIONS';
823 if (p_option = REPORT_ONLY) then
824 select count(*) into x_num_rows from WIP_TRANSACTIONS
825 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
826 else
827 DELETE FROM WIP_TRANSACTIONS
828 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
829 x_num_rows := SQL%ROWCOUNT;
830 end if;
831
832 if x_num_rows > 0 then
833 before_append_report(
834 p_option => p_option,
835 p_purge_rec => x_purge_rec,
836 num_rows => x_num_rows);
837 end if;
838
839 end delete_job_cost_trx;
840
841
842
843 -- procedure to delete schedule resource transactions
844 -- This need work also
845 procedure delete_sched_cost_trx(
846 p_option in number,
847 p_group_id in number,
848 p_purge_request in get_purge_requests%rowtype,
849 p_cutoff_date in date,
850 p_sched_txn_flag in out nocopy boolean ) is
851 x_purge_rec purge_report_type;
852 x_records_found boolean := FALSE ;
853 l_num_rows number :=0;
854 begin
855
856 x_purge_rec.group_id := p_group_id;
857 x_purge_rec.org_id := p_purge_request.organization_id;
858 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
859 x_purge_rec.schedule_id := p_purge_request.repetitive_schedule_id;
860 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
861 x_purge_rec.line_id := p_purge_request.line_id;
862 x_purge_rec.start_date := p_purge_request.start_date;
863 x_purge_rec.complete_date := p_purge_request.complete_date;
864 x_purge_rec.close_date := p_purge_request.close_date;
865 x_purge_rec.info_type := EXCEPTIONS;
866 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
867 x_purge_rec.line_code := p_purge_request.line_code;
868
869
870 -- verify that no other schedules exists
871 fnd_message.set_name('WIP', 'WIP_TRANSACTIONS_PURGE_ERROR');
872 x_purge_rec.info := fnd_message.get;
873 x_purge_rec.table_name := 'WIP_TXN_ALLOCATIONS';
874
875 SELECT COUNT(*) into l_num_rows
876 FROM DUAL WHERE EXISTS (
877 SELECT out_wta.transaction_id
878 FROM wip_txn_allocations out_wta
879 WHERE out_wta.organization_id = p_purge_request.organization_id
880 AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
881 AND out_wta.transaction_id IN (
882 SELECT transaction_id
883 FROM wip_repetitive_schedules wrs,
884 wip_txn_allocations wta
885 WHERE wta.transaction_id = out_wta.transaction_id
886 AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
887 AND nvl(wrs.date_closed, p_cutoff_date+1) > p_cutoff_date
888 AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
889 AND wta.organization_id = p_purge_request.organization_id
890 AND wrs.organization_id = p_purge_request.organization_id));
891 if ( l_num_rows > 0 ) then
892 x_records_found := true;
893 append_report(x_purge_rec, p_option);
894 end if;
895 l_num_rows := 0;
896
897 -- If records whre found then it means that there records still sitting
898 -- in the WIP_TRANSACTIONS table.
899 if x_records_found then
900 p_sched_txn_flag := TRUE ;
901 x_purge_rec.table_name := 'WIP_TRANSACTIONS';
902 append_report(x_purge_rec, p_option);
903 x_purge_rec.table_name := 'WIP_TRANSACTION_ACCOUNTS';
904 append_report(x_purge_rec, p_option);
905 end if ;
906
907
908 x_purge_rec.info_type := ROWS_AFFECTED;
909 x_purge_rec.table_name := 'WIP_TRANSACTIONS';
910
911 savepoint sched_cost01;
912 DELETE FROM WIP_TRANSACTIONS
913 WHERE TRANSACTION_ID IN (
914 SELECT out_wta.transaction_id
915 FROM wip_txn_allocations out_wta
916 WHERE out_wta.organization_id = p_purge_request.organization_id
917 AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
918 AND NOT EXISTS (
919 SELECT transaction_id
920 FROM wip_repetitive_schedules wrs,
921 wip_txn_allocations wta
922 WHERE wta.transaction_id = out_wta.transaction_id
923 AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
924 AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
925 AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
926 AND wta.organization_id = p_purge_request.organization_id
927 AND wrs.organization_id = p_purge_request.organization_id));
928 l_num_rows := sql%rowcount;
929 if ( p_option = REPORT_ONLY) then
930 -- rollback deletes
931 rollback to sched_cost01;
932 end if;
933 construct_report_content(p_option => p_option,
934 p_num_rows => l_num_rows,
935 p_purge_rec => x_purge_rec);
936 l_num_rows := 0;
937
938
939 x_purge_rec.table_name := 'WIP_TRANSACTION_ACCOUNTS';
940
941 savepoint sched_cost02;
942 DELETE FROM WIP_TRANSACTION_ACCOUNTS
943 WHERE TRANSACTION_ID IN (
944 SELECT out_wta.transaction_id
945 FROM wip_txn_allocations out_wta
946 WHERE out_wta.organization_id = p_purge_request.organization_id
947 AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
948 AND NOT EXISTS (
949 SELECT transaction_id
950 FROM wip_repetitive_schedules wrs,
951 wip_txn_allocations wta
952 WHERE wta.transaction_id = out_wta.transaction_id
953 AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
954 AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
955 AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
956 AND wta.organization_id = p_purge_request.organization_id
957 AND wrs.organization_id = p_purge_request.organization_id));
958 l_num_rows := sql%rowcount;
959 if ( p_option = REPORT_ONLY) then
960 -- rollback deletes
961 rollback to sched_cost02;
962 end if;
963 construct_report_content(p_option => p_option,
964 p_num_rows => l_num_rows,
965 p_purge_rec => x_purge_rec);
966 l_num_rows := 0;
967
968
969 x_purge_rec.table_name := 'WIP_TXN_ALLOCATIONS';
970
971 savepoint sched_cost03;
972 DELETE FROM WIP_TXN_ALLOCATIONS
973 WHERE TRANSACTION_ID IN (
974 SELECT out_wta.transaction_id
975 FROM wip_txn_allocations out_wta
976 WHERE out_wta.organization_id = p_purge_request.organization_id
977 AND out_wta.repetitive_schedule_id = p_purge_request.repetitive_schedule_id
978 AND NOT EXISTS (
979 SELECT transaction_id
980 FROM wip_repetitive_schedules wrs,
981 wip_txn_allocations wta
982 WHERE wta.transaction_id = out_wta.transaction_id
983 AND wta.repetitive_schedule_id <> out_wta.repetitive_schedule_id
984 AND nvl(wrs.date_closed, p_cutoff_date+1) <= p_cutoff_date
985 AND wrs.repetitive_schedule_id = wta.repetitive_schedule_id
986 AND wta.organization_id = p_purge_request.organization_id
987 AND wrs.organization_id = p_purge_request.organization_id));
988
989 l_num_rows := sql%rowcount;
990 if ( p_option = REPORT_ONLY) then
991 -- rollback deletes
992 rollback to sched_cost03;
993 end if;
994 construct_report_content(p_option => p_option,
995 p_num_rows => l_num_rows,
996 p_purge_rec => x_purge_rec);
997 l_num_rows := 0;
998
999 end delete_sched_cost_trx;
1000
1001
1002
1003 /*
1004 -- procedure to check if record exists
1005 procedure verify_foreign_key(
1006 p_option in number,
1007 p_purge_rec in purge_report_type,
1008 p_sql in varchar2,
1009 p_rec_exists in out nocopy boolean) is
1010
1011 x_cursor_id number;
1012 x_count number := 0;
1013 x_purge_rec purge_report_type;
1014 x_ret number := 0 ;
1015 begin
1016
1017
1018 x_cursor_id := dbms_sql.open_cursor;
1019 dbms_sql.parse(x_cursor_id, p_sql, dbms_sql.v7);
1020 dbms_sql.define_column(x_cursor_id, 1, x_count);
1021 x_ret := dbms_sql.execute(x_cursor_id);
1022 x_ret := dbms_sql.fetch_rows(x_cursor_id);
1023 dbms_sql.column_value(x_cursor_id, 1, x_count);
1024 dbms_sql.close_cursor(x_cursor_id);
1025
1026 -- set count of rows
1027 p_rec_exists := p_rec_exists OR (x_count > 0);
1028
1029 if (x_count > 0) then
1030 append_report(p_purge_rec, p_option);
1031 end if;
1032
1033 end verify_foreign_key;
1034 */
1035
1036 -- procedure to delete the job header record
1037 procedure delete_job_header(
1038 p_option in number,
1039 p_group_id in number,
1040 p_purge_request in get_purge_requests%rowtype) is
1041
1042 x_purge_rec purge_report_type;
1043 x_records_found boolean := FALSE;
1044 x_records_returned number := 0;
1045 x_Temp_Where_Clause Varchar2(150);
1046 x_num_rows number := 0;
1047 begin
1048
1049
1050
1051 -- initialize
1052 -- G_Continue_Purging := TRUE ;
1053 x_purge_rec.group_id := p_group_id;
1054 x_purge_rec.org_id := p_purge_request.organization_id;
1055 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
1056 x_purge_rec.schedule_id := NULL;
1057 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
1058 x_purge_rec.line_id := NULL;
1059 x_purge_rec.start_date := p_purge_request.start_date;
1060 x_purge_rec.complete_date := p_purge_request.complete_date;
1061 x_purge_rec.close_date := p_purge_request.close_date;
1062 x_purge_rec.info_type := EXCEPTIONS;
1063 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
1064 x_purge_rec.line_code := p_purge_request.line_code;
1065
1066
1067 -- verify no period balance activity
1068 fnd_message.set_name('WIP', 'WIP_PERIOD_BALANCES_EXIST');
1069 x_purge_rec.info := fnd_message.get;
1070 x_purge_rec.table_name := 'WIP_PERIOD_BALANCES';
1071
1072 /* Fixing bug 935919. The following sql is introduced to replace the earlier one because
1073 the checking should be done to see whether the sum of (IN - OUT NOCOPY - VAR) of each cost
1074 component is zero over all accounting periods in WIP_PERIOD_BALANCES, not each of the
1075 individula cost columns in WIP_PERIOD_BALANCES is zero as it was in the earlier sql.
1076 */
1077
1078 select count(*)
1079 into x_records_returned
1080 from dual
1081 where (0,0,0,0,0,0,0,0,0,0) <>
1082 (select sum(NVL(TL_RESOURCE_IN, 0) - NVL(TL_RESOURCE_OUT, 0) - NVL(TL_RESOURCE_VAR,0)),
1083 sum(NVL(TL_OVERHEAD_IN, 0) - NVL(TL_OVERHEAD_OUT, 0) - NVL(TL_OVERHEAD_VAR,0)),
1084 sum(NVL(TL_OUTSIDE_PROCESSING_IN,0) - NVL(TL_OUTSIDE_PROCESSING_OUT, 0) - NVL(TL_OUTSIDE_PROCESSING_VAR,0)),
1085 sum(0 - NVL(TL_MATERIAL_OUT, 0) - NVL(TL_MATERIAL_VAR,0)),
1086 sum(0 - NVL(TL_MATERIAL_OVERHEAD_OUT, 0) - NVL(TL_MATERIAL_OVERHEAD_VAR,0)),
1087 sum(NVL(PL_MATERIAL_IN, 0) - NVL(PL_MATERIAL_OUT, 0) - NVL(PL_MATERIAL_VAR,0)),
1088 sum(NVL(PL_MATERIAL_OVERHEAD_IN, 0) - NVL(PL_MATERIAL_OVERHEAD_OUT, 0) - NVL(PL_MATERIAL_OVERHEAD_VAR,0)),
1089 sum(NVL(PL_RESOURCE_IN, 0) - NVL(PL_RESOURCE_OUT, 0) - NVL(PL_RESOURCE_VAR,0)),
1090 sum(NVL(PL_OVERHEAD_IN, 0) - NVL(PL_OVERHEAD_OUT, 0) - NVL(PL_OVERHEAD_VAR,0)),
1091 sum(NVL(PL_OUTSIDE_PROCESSING_IN, 0) - NVL(PL_OUTSIDE_PROCESSING_OUT, 0) - NVL(PL_OUTSIDE_PROCESSING_VAR,0))
1092 from WIP_PERIOD_BALANCES
1093 where WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
1094
1095 x_records_found := x_records_found OR (x_records_returned > 0);
1096
1097 if (x_records_returned > 0) then
1098 append_report(x_purge_rec, p_option);
1099 end if;
1100
1101
1102 -- check for foreign key references
1103 fnd_message.set_name('WIP', 'WIP_PURGE_FOREIGN_KEY');
1104 fnd_message.set_token('TABLE', 'WIP_DISCRETE_JOBS', TRUE);
1105 x_purge_rec.info := fnd_message.get;
1106
1107 x_purge_rec.table_name := 'CST_STD_COST_ADJ_VALUES';
1108
1109 SELECT COUNT(*)
1110 into x_records_returned
1111 FROM DUAL
1112 WHERE EXISTS
1113 (SELECT 1
1114 FROM CST_STD_COST_ADJ_VALUES
1115 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
1116
1117 x_records_found := x_records_found OR (x_records_returned > 0);
1118
1119 if (x_records_returned > 0) then
1120 append_report(x_purge_rec, p_option);
1121 end if;
1122
1123
1124 x_purge_rec.table_name := 'PO_DISTRIBUTIONS_ALL';
1125
1126
1127 SELECT COUNT(*)
1128 into x_records_returned
1129 FROM DUAL
1130 WHERE EXISTS
1131 (SELECT 1
1132 FROM PO_DISTRIBUTIONS_ALL
1133 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1134 /* Fixed bug 3115844 */
1135 AND po_line_id IS NOT NULL
1136 AND line_location_id IS NOT NULL);
1137
1138 x_records_found := x_records_found OR (x_records_returned > 0);
1139
1140 if (x_records_returned > 0) then
1141 append_report(x_purge_rec, p_option);
1142 end if;
1143
1144
1145 x_purge_rec.table_name := 'PO_DISTRIBUTIONS_ARCHIVE_ALL';
1146
1147 SELECT COUNT(*)
1148 into x_records_returned
1149 FROM DUAL
1150 WHERE EXISTS
1151 (SELECT 1
1152 FROM PO_DISTRIBUTIONS_ARCHIVE_ALL
1153 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
1154
1155 x_records_found := x_records_found OR (x_records_returned > 0);
1156
1157 if (x_records_returned > 0) then
1158 append_report(x_purge_rec, p_option);
1159 end if;
1160
1161
1162
1163 x_purge_rec.table_name := 'PO_REQUISITION_LINES_ALL';
1164
1165 SELECT COUNT(*)
1166 into x_records_returned
1167 FROM DUAL
1168 WHERE EXISTS
1169 (SELECT 1
1170 FROM PO_REQUISITION_LINES_ALL
1171 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id );
1172
1173 x_records_found := x_records_found OR (x_records_returned > 0);
1174
1175 if (x_records_returned > 0) then
1176 append_report(x_purge_rec, p_option);
1177 end if;
1178
1179
1180
1181 x_purge_rec.table_name := 'RCV_TRANSACTIONS';
1182
1183 SELECT COUNT(*)
1184 into x_records_returned
1185 FROM DUAL
1186 WHERE EXISTS
1187 (SELECT 1
1188 FROM RCV_TRANSACTIONS
1189 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id );
1190
1191 x_records_found := x_records_found OR (x_records_returned > 0);
1192
1193 if (x_records_returned > 0) then
1194 append_report(x_purge_rec, p_option);
1195 end if;
1196
1197
1198 -- This is included inorder to use the existing index on inventory_item_id in
1199 -- the MTL tables
1200
1201 /* Fix for bug#4902938 - Comment this piece of code since it is NOT used anywhere.*/
1202 /*
1203 if (x_purge_rec.primary_item_id IS NULL) then
1204 x_Temp_Where_Clause := ' AND INVENTORY_ITEM_ID IS NULL ' ;
1205 else
1206 x_Temp_Where_Clause := ' AND INVENTORY_ITEM_ID = ' || to_char(x_purge_rec.primary_item_id) ;
1207 end if ;
1208 */
1209 /* END - Fix for bug#4902938 */
1210
1211 x_purge_rec.table_name := 'MTL_DEMAND';
1212
1213 if (x_purge_rec.primary_item_id IS NULL) then
1214
1215 -- Bug 4880984
1216 -- Removed this SQL as check is redundand. This is because
1217 -- inventory_item_id is mandatory column in MTL_DEMAND table.
1218 -- Assigning value of Zero to x_records_returned instead.
1219 /*
1220 SELECT COUNT(*)
1221 into x_records_returned
1222 FROM DUAL
1223 WHERE EXISTS
1224 (SELECT 1
1225 FROM MTL_DEMAND
1226 WHERE SUPPLY_SOURCE_TYPE = 5
1227 AND INVENTORY_ITEM_ID IS NULL
1228 AND SUPPLY_SOURCE_HEADER_ID = x_purge_rec.wip_entity_id
1229 AND ORGANIZATION_ID = x_purge_rec.org_id);
1230 */
1231
1232 x_records_returned := 0;
1233
1234 else
1235
1236 SELECT COUNT(*)
1237 into x_records_returned
1238 FROM DUAL
1239 WHERE EXISTS
1240 (SELECT 1
1241 FROM MTL_DEMAND
1242 WHERE SUPPLY_SOURCE_TYPE = 5
1243 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
1244 AND SUPPLY_SOURCE_HEADER_ID = x_purge_rec.wip_entity_id
1245 AND ORGANIZATION_ID = x_purge_rec.org_id);
1246
1247 end if ;
1248
1249 x_records_found := x_records_found OR (x_records_returned > 0);
1250
1251 if (x_records_returned > 0) then
1252 append_report(x_purge_rec, p_option);
1253 end if;
1254
1255
1256
1257 x_purge_rec.table_name := 'MTL_USER_SUPPLY';
1258
1259
1260 if (x_purge_rec.primary_item_id IS NULL) then
1261
1262 SELECT COUNT(*)
1263 into x_records_returned
1264 FROM DUAL
1265 WHERE EXISTS
1266 (SELECT 1
1267 FROM MTL_USER_SUPPLY
1268 WHERE SOURCE_TYPE_ID = 4
1269 AND SOURCE_ID = x_purge_rec.wip_entity_id
1270 AND INVENTORY_ITEM_ID IS NULL );
1271
1272 else
1273
1274 SELECT COUNT(*)
1275 into x_records_returned
1276 FROM DUAL
1277 WHERE EXISTS
1278 (SELECT 1
1279 FROM MTL_USER_SUPPLY
1280 WHERE SOURCE_TYPE_ID = 4
1281 AND SOURCE_ID = x_purge_rec.wip_entity_id
1282 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
1283 end if;
1284
1285 x_records_found := x_records_found OR (x_records_returned > 0);
1286
1287 if (x_records_returned > 0) then
1288 append_report(x_purge_rec, p_option);
1289 end if;
1290
1291
1292 x_purge_rec.table_name := 'MTL_USER_DEMAND';
1293
1294
1295 if (x_purge_rec.primary_item_id IS NULL) then
1296
1297 SELECT COUNT(*)
1298 into x_records_returned
1299 FROM DUAL
1300 WHERE EXISTS
1301 (SELECT 1
1302 FROM MTL_USER_DEMAND
1303 WHERE SOURCE_TYPE_ID = 4
1304 AND SOURCE_ID = x_purge_rec.wip_entity_id
1305 AND INVENTORY_ITEM_ID IS NULL );
1306 else
1307 SELECT COUNT(*)
1308 into x_records_returned
1309 FROM DUAL
1310 WHERE EXISTS
1311 (SELECT 1
1312 FROM MTL_USER_DEMAND
1313 WHERE SOURCE_TYPE_ID = 4
1314 AND SOURCE_ID = x_purge_rec.wip_entity_id
1315 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
1316
1317 end if;
1318
1319 x_records_found := x_records_found OR (x_records_returned > 0);
1320
1321 if (x_records_returned > 0) then
1322 append_report(x_purge_rec, p_option);
1323 end if;
1324
1325
1326 x_purge_rec.table_name := 'MTL_SERIAL_NUMBERS';
1327
1328 if (x_purge_rec.primary_item_id IS NULL) then
1329
1330 SELECT COUNT(*)
1331 into x_records_returned
1332 FROM DUAL
1333 WHERE EXISTS
1334 (SELECT 1
1335 FROM MTL_SERIAL_NUMBERS
1336 WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1337 AND INVENTORY_ITEM_ID IS NULL );
1338 else
1339
1340 SELECT COUNT(*)
1341 into x_records_returned
1342 FROM DUAL
1343 WHERE EXISTS
1344 (SELECT 1
1345 FROM MTL_SERIAL_NUMBERS
1346 WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1347 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id );
1348
1349 end if;
1350
1351 /* Bug 5935560. Need to update original_wip_entity_id in MSN to -999
1352 and purge if there is a associated serial number. */
1353
1354 /* x_records_found := x_records_found OR (x_records_returned > 0); */
1355
1356 if (x_records_returned > 0) then
1357 /* append_report(x_purge_rec, p_option); */
1358 fnd_file.put_line(FND_FILE.LOG,'Note: job/schedule '||x_purge_rec.wip_entity_id||' has Serial Number reference');
1359 end if;
1360
1361 x_purge_rec.table_name := 'MTL_MATERIAL_TRANSACTIONS';
1362
1363 SELECT COUNT(*)
1364 into x_records_returned
1365 FROM DUAL
1366 WHERE EXISTS
1367 (SELECT 1
1368 FROM MTL_MATERIAL_TRANSACTIONS
1369 WHERE TRANSACTION_SOURCE_TYPE_ID + 0 = 5
1370 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
1371 AND ORGANIZATION_ID = x_purge_rec.org_id );
1372
1373 x_records_found := x_records_found OR (x_records_returned > 0);
1374
1375 if (x_records_returned > 0) then
1376 append_report(x_purge_rec, p_option);
1377 end if;
1378
1379
1380
1381 x_purge_rec.table_name := 'MTL_TRANSACTION_ACCOUNTS';
1382
1383 SELECT COUNT(*)
1384 into x_records_returned
1385 FROM DUAL
1386 WHERE EXISTS
1387 (SELECT 1
1388 FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT
1389 WHERE MMT.TRANSACTION_SOURCE_TYPE_ID + 0 = 5
1390 AND MMT.TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
1391 AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
1392 AND MMT.ORGANIZATION_ID = x_purge_rec.org_id );
1393
1394 x_records_found := x_records_found OR (x_records_returned > 0);
1395
1396 if (x_records_returned > 0) then
1397 append_report(x_purge_rec, p_option);
1398 end if;
1399
1400
1401 x_purge_rec.table_name := 'MTL_TRANSACTION_LOT_NUMBERS';
1402
1403 SELECT COUNT(*)
1404 into x_records_returned
1405 FROM DUAL
1406 WHERE EXISTS
1407 (SELECT 1
1408 FROM MTL_TRANSACTION_LOT_NUMBERS
1409 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1410 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id );
1411
1412 x_records_found := x_records_found OR (x_records_returned > 0);
1413
1414 if (x_records_returned > 0) then
1415 append_report(x_purge_rec, p_option);
1416 end if;
1417
1418
1419
1420 x_purge_rec.table_name := 'MTL_UNIT_TRANSACTIONS';
1421
1422 SELECT COUNT(*)
1423 into x_records_returned
1424 FROM DUAL
1425 WHERE EXISTS
1426 (SELECT 1
1427 FROM MTL_UNIT_TRANSACTIONS
1428 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1429 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id );
1430
1431 x_records_found := x_records_found OR (x_records_returned > 0);
1432
1433 if (x_records_returned > 0) then
1434 append_report(x_purge_rec, p_option);
1435 end if;
1436
1437
1438
1439 -- PASSED VALIDATIONS SO DELETE HEADER AND INTERFACE RECORDS
1440
1441 -- if (G_Continue_Purging = TRUE) then
1442
1443 if x_records_found = FALSE then
1444
1445 x_purge_rec.info_type := EXCEPTIONS;
1446
1447 if (p_option = REPORT_ONLY) then
1448 select count(*) into x_num_rows
1449 from MTL_SERIAL_NUMBERS
1450 WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id ;
1451 else
1452 update MTL_SERIAL_NUMBERS
1453 set original_wip_entity_id = -999
1454 WHERE ORIGINAL_WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1455
1456 x_num_rows := SQL%ROWCOUNT ;
1457 end if;
1458
1459 if x_num_rows > 0 then
1460 x_purge_rec.table_name := 'MTL_SERIAL_NUMBERS';
1461 fnd_message.set_name('WIP', 'WIP_SERIAL_FOREIGN_KEY');
1462 x_purge_rec.info := fnd_message.get;
1463 append_report(x_purge_rec, p_option);
1464 end if;
1465
1466 x_purge_rec.info_type := ROWS_AFFECTED;
1467
1468 x_purge_rec.table_name := 'CST_PERIOD_VALUE_TEMP';
1469
1470
1471 if (p_option = REPORT_ONLY) then
1472 SELECT COUNT(*) INTO x_num_rows
1473 FROM CST_PERIOD_VALUE_TEMP
1474 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1475 AND ORGANIZATION_ID = x_purge_rec.org_id;
1476 else
1477 DELETE FROM CST_PERIOD_VALUE_TEMP
1478 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1479 AND ORGANIZATION_ID = x_purge_rec.org_id;
1480
1481 x_num_rows := SQL%ROWCOUNT ;
1482
1483 end if ;
1484
1485 if x_num_rows > 0 then
1486 before_append_report(
1487 p_option => p_option,
1488 p_purge_rec => x_purge_rec,
1489 num_rows => x_num_rows);
1490 end if;
1491
1492 /* Fix for Bug#3125050. Changed CST_STD_COST_ADJ_TEMP TO
1493 CST_STD_COST_ADJ_DEBUG
1494 */
1495 x_purge_rec.table_name := 'CST_STD_COST_ADJ_DEBUG';
1496
1497
1498 if (p_option = REPORT_ONLY) then
1499 SELECT COUNT(*) INTO x_num_rows
1500 FROM CST_STD_COST_ADJ_DEBUG
1501 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1502 AND ORGANIZATION_ID = x_purge_rec.org_id;
1503 else
1504 DELETE FROM CST_STD_COST_ADJ_DEBUG
1505 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1506 AND ORGANIZATION_ID = x_purge_rec.org_id;
1507
1508 x_num_rows := SQL%ROWCOUNT ;
1509
1510 end if ;
1511
1512 if x_num_rows > 0 then
1513 before_append_report(
1514 p_option => p_option,
1515 p_purge_rec => x_purge_rec,
1516 num_rows => x_num_rows);
1517 end if;
1518
1519
1520 x_purge_rec.table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
1521
1522 if (p_option = REPORT_ONLY) then
1523 SELECT COUNT(*) INTO x_num_rows
1524 FROM PO_REQUISITIONS_INTERFACE_ALL
1525 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1526 else
1527 DELETE FROM PO_REQUISITIONS_INTERFACE_ALL
1528 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1529
1530 x_num_rows := SQL%ROWCOUNT ;
1531
1532 end if ;
1533
1534 if x_num_rows > 0 then
1535 before_append_report(
1536 p_option => p_option,
1537 p_purge_rec => x_purge_rec,
1538 num_rows => x_num_rows);
1539 end if;
1540
1541
1542 x_purge_rec.table_name := 'RCV_TRANSACTIONS_INTERFACE';
1543
1544 if (p_option = REPORT_ONLY) then
1545 SELECT COUNT(*) INTO x_num_rows
1546 FROM RCV_TRANSACTIONS_INTERFACE
1547 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1548 else
1549 DELETE FROM RCV_TRANSACTIONS_INTERFACE
1550 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1551
1552 x_num_rows := SQL%ROWCOUNT ;
1553
1554 end if ;
1555
1556 if x_num_rows > 0 then
1557 before_append_report(
1558 p_option => p_option,
1559 p_purge_rec => x_purge_rec,
1560 num_rows => x_num_rows);
1561 end if;
1562
1563
1564 x_purge_rec.table_name := 'MRP_RELIEF_INTERFACE';
1565
1566 if (p_option = REPORT_ONLY) then
1567 SELECT COUNT(*) INTO x_num_rows
1568 FROM MRP_RELIEF_INTERFACE
1569 WHERE DISPOSITION_TYPE = 1
1570 AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
1571 else
1572 DELETE FROM MRP_RELIEF_INTERFACE
1573 WHERE DISPOSITION_TYPE = 1
1574 AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
1575
1576 x_num_rows := SQL%ROWCOUNT ;
1577
1578 end if ;
1579
1580 if x_num_rows > 0 then
1581 before_append_report(
1582 p_option => p_option,
1583 p_purge_rec => x_purge_rec,
1584 num_rows => x_num_rows);
1585 end if;
1586
1587 x_purge_rec.table_name := 'MTL_DEMAND_INTERFACE';
1588
1589 if (p_option = REPORT_ONLY) then
1590 SELECT COUNT(*) INTO x_num_rows
1591 FROM MTL_DEMAND_INTERFACE
1592 WHERE ORGANIZATION_ID = x_purge_rec.org_id
1593 AND SUPPLY_SOURCE_TYPE = 5
1594 AND SUPPLY_HEADER_ID = x_purge_rec.wip_entity_id;
1595 else
1596 DELETE FROM MTL_DEMAND_INTERFACE
1597 WHERE ORGANIZATION_ID = x_purge_rec.org_id
1598 AND SUPPLY_SOURCE_TYPE = 5
1599 AND SUPPLY_HEADER_ID = x_purge_rec.wip_entity_id;
1600
1601 x_num_rows := SQL%ROWCOUNT ;
1602
1603 end if ;
1604
1605 if x_num_rows > 0 then
1606 before_append_report(
1607 p_option => p_option,
1608 p_purge_rec => x_purge_rec,
1609 num_rows => x_num_rows);
1610 end if;
1611
1612
1613 x_purge_rec.table_name := 'MTL_TRANSACTIONS_INTERFACE';
1614
1615 if (p_option = REPORT_ONLY) then
1616 SELECT COUNT(*) INTO x_num_rows
1617 FROM MTL_TRANSACTIONS_INTERFACE
1618 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1619 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1620 else
1621 DELETE FROM MTL_TRANSACTIONS_INTERFACE
1622 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1623 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1624 x_num_rows := SQL%ROWCOUNT ;
1625 end if ;
1626
1627 if x_num_rows > 0 then
1628 before_append_report(
1629 p_option => p_option,
1630 p_purge_rec => x_purge_rec,
1631 num_rows => x_num_rows);
1632 end if;
1633
1634
1635 x_purge_rec.table_name := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
1636
1637 if (x_purge_rec.primary_item_id IS NULL) then
1638 if (p_option = REPORT_ONLY) then
1639 SELECT COUNT(*)
1640 INTO x_num_rows
1641 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1642 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1643 AND INVENTORY_ITEM_ID IS NULL
1644 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1645 else
1646 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1647 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1648 AND INVENTORY_ITEM_ID IS NULL
1649 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1650
1651 x_num_rows := SQL%ROWCOUNT ;
1652 end if ;
1653
1654 else
1655
1656 if (p_option = REPORT_ONLY) then
1657 SELECT COUNT(*)
1658 INTO x_num_rows
1659 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1660 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1661 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
1662 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1663 else
1664 DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1665 WHERE TRANSACTION_SOURCE_TYPE_ID = 5
1666 AND INVENTORY_ITEM_ID = x_purge_rec.primary_item_id
1667 AND TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id;
1668
1669 x_num_rows := SQL%ROWCOUNT ;
1670 end if ;
1671
1672 end if;
1673
1674 if x_num_rows > 0 then
1675 before_append_report(
1676 p_option => p_option,
1677 p_purge_rec => x_purge_rec,
1678 num_rows => x_num_rows);
1679 end if;
1680
1681
1682
1683 --
1684 --Bug#4715338 START - Purge Time Entry records
1685 --Purge records from WIP_RESOURCE_ACTUAL_TIMES
1686 --
1687 x_purge_rec.table_name := 'WIP_RESOURCE_ACTUAL_TIMES';
1688
1689 if (p_option = REPORT_ONLY) then
1690 SELECT COUNT(*) INTO X_NUM_ROWS
1691 FROM WIP_RESOURCE_ACTUAL_TIMES
1692 WHERE ORGANIZATION_ID = X_PURGE_REC.ORG_ID AND
1693 WIP_ENTITY_ID = X_PURGE_REC.WIP_ENTITY_ID;
1694 else
1695 DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
1696 WHERE ORGANIZATION_ID = X_PURGE_REC.ORG_ID AND
1697 WIP_ENTITY_ID = X_PURGE_REC.WIP_ENTITY_ID;
1698 x_num_rows := SQL%ROWCOUNT ;
1699 end if;
1700
1701 if x_num_rows > 0 then
1702 before_append_report(
1703 p_option => p_option,
1704 p_purge_rec => x_purge_rec,
1705 num_rows => x_num_rows);
1706 end if;
1707 --
1708 --Bug#4715338 ENDS
1709 --
1710
1711
1712 --
1713 --Bug#4716115 START
1714 --Purge records from WIP_LPN_COMPLETIONS, WIP_LPN_COMPLETIONS_LOTS, WIP_LPN_COMPLETIONS_SERIALS
1715 --
1716
1717 -- delete from wip_lpn_completions_lots
1718 x_purge_rec.table_name := 'WIP_LPN_COMPLETIONS_LOTS';
1719
1720 if (p_option = REPORT_ONLY) then
1721 SELECT COUNT(*) INTO X_NUM_ROWS
1722 FROM WIP_LPN_COMPLETIONS_LOTS WLCL
1723 WHERE WLCL.HEADER_ID IN
1724 (
1725 SELECT WLC.HEADER_ID
1726 FROM WIP_LPN_COMPLETIONS WLC
1727 WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
1728 );
1729 else
1730 DELETE FROM WIP_LPN_COMPLETIONS_LOTS WLCL
1731 WHERE WLCL.HEADER_ID IN
1732 (
1733 SELECT WLC.HEADER_ID
1734 FROM WIP_LPN_COMPLETIONS WLC
1735 WHERE WLC.TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
1736 );
1737 x_num_rows := SQL%ROWCOUNT ;
1738 end if;
1739
1740 if x_num_rows > 0 then
1741 before_append_report(
1742 p_option => p_option,
1743 p_purge_rec => x_purge_rec,
1744 num_rows => x_num_rows);
1745 end if;
1746
1747 -- delete from wip_lpn_completions_serials
1748 x_purge_rec.table_name := 'WIP_LPN_COMPLETIONS_SERIALS';
1749
1750 if (p_option = REPORT_ONLY) then
1751 SELECT COUNT(*) INTO X_NUM_ROWS
1752 FROM WIP_LPN_COMPLETIONS_SERIALS WLCS
1753 WHERE WLCS.HEADER_ID IN
1754 (
1755 SELECT WLC.HEADER_ID
1756 FROM WIP_LPN_COMPLETIONS WLC
1757 WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
1758 );
1759 else
1760 DELETE FROM WIP_LPN_COMPLETIONS_SERIALS WLCS
1761 WHERE WLCS.HEADER_ID IN
1762 (
1763 SELECT WLC.HEADER_ID
1764 FROM WIP_LPN_COMPLETIONS WLC
1765 WHERE WLC.TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID
1766 );
1767 x_num_rows := SQL%ROWCOUNT ;
1768 end if;
1769
1770 if x_num_rows > 0 then
1771 before_append_report(
1772 p_option => p_option,
1773 p_purge_rec => x_purge_rec,
1774 num_rows => x_num_rows);
1775 end if;
1776
1777
1778 -- delete from wip_lpn_completions
1779 x_purge_rec.table_name := 'WIP_LPN_COMPLETIONS';
1780
1781 if (p_option = REPORT_ONLY) then
1782 SELECT COUNT(*) INTO X_NUM_ROWS
1783 FROM WIP_LPN_COMPLETIONS
1784 WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID;
1785 else
1786 DELETE FROM WIP_LPN_COMPLETIONS
1787 WHERE TRANSACTION_SOURCE_ID = X_PURGE_REC.WIP_ENTITY_ID;
1788 x_num_rows := SQL%ROWCOUNT ;
1789 end if;
1790
1791 if x_num_rows > 0 then
1792 before_append_report(
1793 p_option => p_option,
1794 p_purge_rec => x_purge_rec,
1795 num_rows => x_num_rows);
1796 end if;
1797
1798 --
1799 --Fix for Bug#4716115 ENDS
1800 --
1801
1802
1803 x_purge_rec.table_name := 'MTL_SUPPLY_DEMAND_TEMP';
1804
1805 if (p_option = REPORT_ONLY) then
1806 SELECT COUNT(*) INTO x_num_rows
1807 FROM MTL_SUPPLY_DEMAND_TEMP
1808 WHERE DISPOSITION_TYPE = 5
1809 AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
1810 else
1811 DELETE FROM MTL_SUPPLY_DEMAND_TEMP
1812 WHERE DISPOSITION_TYPE = 5
1813 AND DISPOSITION_ID = x_purge_rec.wip_entity_id;
1814 x_num_rows := SQL%ROWCOUNT ;
1815 end if;
1816
1817 if x_num_rows > 0 then
1818 before_append_report(
1819 p_option => p_option,
1820 p_purge_rec => x_purge_rec,
1821 num_rows => x_num_rows);
1822 end if;
1823
1824
1825
1826 /* Moved WIP_SO_ALLOCATIONS deletion codes to this section so that it will go
1827 through the foreign key verifications to ensure that WIP_SO_ALLOCATIONS will
1828 NOT be purged if foreign key references exist in the MTL_DEMAND table.
1829 Bug # 622330 */
1830
1831 if (OE_INSTALL.Get_Active_Product = 'OE') then
1832 x_purge_rec.table_name := 'WIP_SO_ALLOCATIONS';
1833
1834 if (p_option = REPORT_ONLY) then
1835 SELECT COUNT(*) INTO x_num_rows
1836 FROM WIP_SO_ALLOCATIONS
1837 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1838 else
1839 DELETE FROM WIP_SO_ALLOCATIONS
1840 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1841 x_num_rows := SQL%ROWCOUNT ;
1842 end if ;
1843
1844 if x_num_rows > 0 then
1845 before_append_report(
1846 p_option => p_option,
1847 p_purge_rec => x_purge_rec,
1848 num_rows => x_num_rows);
1849 end if;
1850
1851 end if;
1852
1853
1854
1855 x_purge_rec.table_name := 'WIP_SCHEDULING_EXCEPTIONS';
1856
1857 if (p_option = REPORT_ONLY) then
1858 SELECT COUNT(*)
1859 INTO x_num_rows
1860 FROM WIP_SCHEDULING_EXCEPTIONS
1861 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1862 else
1863 DELETE FROM WIP_SCHEDULING_EXCEPTIONS
1864 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1865 x_num_rows := SQL%ROWCOUNT ;
1866 end if ;
1867
1868 if x_num_rows > 0 then
1869 before_append_report(
1870 p_option => p_option,
1871 p_purge_rec => x_purge_rec,
1872 num_rows => x_num_rows);
1873 end if;
1874
1875 /* Bug#4675116 */
1876 x_purge_rec.table_name := 'WIP_EXCEPTIONS';
1877
1878 if (p_option = REPORT_ONLY) then
1879 SELECT COUNT(*)
1880 INTO x_num_rows
1881 FROM WIP_EXCEPTIONS
1882 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1883 AND ORGANIZATION_ID = x_purge_rec.org_id;
1884 else
1885 DELETE FROM WIP_EXCEPTIONS
1886 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id
1887 AND ORGANIZATION_ID = x_purge_rec.org_id;
1888 x_num_rows := SQL%ROWCOUNT ;
1889 end if ;
1890
1891 if x_num_rows > 0 then
1892 before_append_report(
1893 p_option => p_option,
1894 p_purge_rec => x_purge_rec,
1895 num_rows => x_num_rows);
1896 end if;
1897
1898
1899
1900 x_purge_rec.table_name := 'WIP_PERIOD_BALANCES';
1901
1902 if (p_option = REPORT_ONLY) then
1903 SELECT COUNT(*) INTO x_num_rows
1904 FROM WIP_PERIOD_BALANCES
1905 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1906 else
1907 DELETE FROM WIP_PERIOD_BALANCES
1908 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1909 x_num_rows := SQL%ROWCOUNT ;
1910 end if ;
1911
1912 if x_num_rows > 0 then
1913 before_append_report(
1914 p_option => p_option,
1915 p_purge_rec => x_purge_rec,
1916 num_rows => x_num_rows);
1917 end if;
1918
1919
1920 x_purge_rec.table_name := 'WIP_DISCRETE_JOBS';
1921
1922 if (p_option = REPORT_ONLY) then
1923 SELECT COUNT(*) INTO x_num_rows
1924 FROM WIP_DISCRETE_JOBS
1925 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1926 else
1927 DELETE FROM WIP_DISCRETE_JOBS
1928 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1929 x_num_rows := SQL%ROWCOUNT ;
1930 end if ;
1931
1932 if x_num_rows > 0 then
1933 before_append_report(
1934 p_option => p_option,
1935 p_purge_rec => x_purge_rec,
1936 num_rows => x_num_rows);
1937 end if;
1938
1939
1940
1941 x_purge_rec.table_name := 'WIP_ENTITIES';
1942
1943 if (p_option = REPORT_ONLY) then
1944 SELECT COUNT(*) INTO x_num_rows
1945 FROM WIP_ENTITIES
1946 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1947 else
1948 DELETE FROM WIP_ENTITIES
1949 WHERE WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
1950 x_num_rows := SQL%ROWCOUNT ;
1951 end if ;
1952
1953 if x_num_rows > 0 then
1954 before_append_report(
1955 p_option => p_option,
1956 p_purge_rec => x_purge_rec,
1957 num_rows => x_num_rows);
1958 end if;
1959
1960
1961 end if;
1962
1963 end delete_job_header;
1964
1965
1966
1967 -- procedure to delete schedule headers
1968 procedure delete_sched_header (
1969 p_option in number,
1970 p_group_id in number,
1971 p_purge_request in get_purge_requests%rowtype,
1972 p_sched_move_txn_flag in out nocopy boolean,
1973 p_sched_txn_flag in out nocopy boolean ) is
1974
1975 x_purge_rec purge_report_type;
1976 x_records_found boolean := FALSE;
1977 x_num_rows number := 0;
1978 l_num_rows number := 0;
1979 begin
1980 -- initialize
1981 x_purge_rec.group_id := p_group_id;
1982 x_purge_rec.org_id := p_purge_request.organization_id;
1983 x_purge_rec.wip_entity_id := p_purge_request.wip_entity_id;
1984 x_purge_rec.schedule_id := p_purge_request.repetitive_schedule_id;
1985 x_purge_rec.primary_item_id := p_purge_request.primary_item_id;
1986 x_purge_rec.line_id := p_purge_request.line_id;
1987 x_purge_rec.start_date := p_purge_request.start_date;
1988 x_purge_rec.complete_date := p_purge_request.complete_date;
1989 x_purge_rec.close_date := p_purge_request.close_date;
1990 x_purge_rec.info_type := EXCEPTIONS;
1991 x_purge_rec.entity_name := p_purge_request.wip_entity_name;
1992 x_purge_rec.line_code := p_purge_request.line_code;
1993
1994 -- set message; verify no period balance activity
1995 fnd_message.set_name('WIP', 'WIP_PERIOD_BALANCES_EXIST');
1996 x_purge_rec.info := fnd_message.get;
1997
1998 x_purge_rec.table_name := 'WIP_PERIOD_BALANCES';
1999
2000 /* Fixing bug 935919. The following sql is introduced to replace the earlier one because
2001 the checking should be done to see whether the sum of (IN - OUT NOCOPY - VAR) of each cost
2002 component is zero over all accounting periods in WIP_PERIOD_BALANCES, not each of the
2003 individula cost columns in WIP_PERIOD_BALANCES is zero as it was in the earlier sql.
2004 */
2005
2006 select count(*) into l_num_rows
2007 from sys.dual
2008 where (0,0,0,0,0,0,0,0,0,0) <>
2009 (select sum(NVL(TL_RESOURCE_IN, 0) - NVL(TL_RESOURCE_OUT, 0)
2010 - NVL(TL_RESOURCE_VAR,0)),
2011 sum(NVL(TL_OVERHEAD_IN, 0) - NVL(TL_OVERHEAD_OUT, 0)
2012 - NVL(TL_OVERHEAD_VAR,0)),
2013 sum(NVL(TL_OUTSIDE_PROCESSING_IN,0) - NVL(TL_OUTSIDE_PROCESSING_OUT, 0)
2014 - NVL(TL_OUTSIDE_PROCESSING_VAR,0)),
2015 sum(0 - NVL(TL_MATERIAL_OUT, 0) - NVL(TL_MATERIAL_VAR,0)),
2016 sum(0 - NVL(TL_MATERIAL_OVERHEAD_OUT, 0)
2017 - NVL(TL_MATERIAL_OVERHEAD_VAR,0)),
2018 sum(NVL(PL_MATERIAL_IN, 0) - NVL(PL_MATERIAL_OUT, 0)
2019 - NVL(PL_MATERIAL_VAR,0)),
2020 sum(NVL(PL_MATERIAL_OVERHEAD_IN, 0) - NVL(PL_MATERIAL_OVERHEAD_OUT, 0)
2021 - NVL(PL_MATERIAL_OVERHEAD_VAR,0)),
2022 sum(NVL(PL_RESOURCE_IN, 0) - NVL(PL_RESOURCE_OUT, 0)
2023 - NVL(PL_RESOURCE_VAR,0)),
2024 sum(NVL(PL_OVERHEAD_IN, 0) - NVL(PL_OVERHEAD_OUT, 0)
2025 - NVL(PL_OVERHEAD_VAR,0)),
2026 sum(NVL(PL_OUTSIDE_PROCESSING_IN, 0) - NVL(PL_OUTSIDE_PROCESSING_OUT, 0)
2027 - NVL(PL_OUTSIDE_PROCESSING_VAR,0))
2028 from WIP_PERIOD_BALANCES
2029 where WIP_ENTITY_ID = x_purge_rec.wip_entity_id
2030 and REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id);
2031 x_records_found := x_records_found OR (l_num_rows > 0);
2032 if ( l_num_rows > 0 ) then
2033 append_report(x_purge_rec, p_option);
2034 end if;
2035 l_num_rows := 0;
2036
2037 -- reset message; check for foreign key references
2038 fnd_message.set_name('WIP', 'WIP_PURGE_FOREIGN_KEY');
2039 fnd_message.set_token('TABLE', 'WIP_REPETITIVE_SCHEDULES', TRUE);
2040 x_purge_rec.info := fnd_message.get;
2041
2042 x_purge_rec.table_name := 'PO_DISTRIBUTIONS_ALL';
2043 SELECT COUNT(*) into l_num_rows
2044 FROM DUAL
2045 WHERE EXISTS (SELECT 1
2046 FROM PO_DISTRIBUTIONS_ALL
2047 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2048 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id
2049 /* Fixed bug 3115844 */
2050 AND po_line_id IS NOT NULL
2051 AND line_location_id IS NOT NULL);
2052 x_records_found := x_records_found OR (l_num_rows > 0);
2053 if ( l_num_rows > 0 ) then
2054 append_report(x_purge_rec, p_option);
2055 end if;
2056 l_num_rows := 0;
2057
2058 x_purge_rec.table_name := 'PO_DISTRIBUTIONS_ARCHIVE_ALL';
2059 SELECT COUNT(*) into l_num_rows
2060 FROM DUAL
2061 WHERE EXISTS (SELECT 1
2062 FROM PO_DISTRIBUTIONS_ARCHIVE_ALL
2063 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2064 and WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
2065 x_records_found := x_records_found OR (l_num_rows > 0);
2066 if ( l_num_rows > 0 ) then
2067 append_report(x_purge_rec, p_option);
2068 end if;
2069 l_num_rows := 0;
2070
2071 x_purge_rec.table_name := 'PO_REQUISITION_LINES_ALL';
2072 SELECT COUNT(*) into l_num_rows
2073 FROM DUAL
2074 WHERE EXISTS (SELECT 1
2075 FROM PO_REQUISITION_LINES_ALL
2076 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2077 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
2078 x_records_found := x_records_found OR (l_num_rows > 0);
2079 if ( l_num_rows > 0 ) then
2080 append_report(x_purge_rec, p_option);
2081 end if;
2082 l_num_rows := 0;
2083
2084
2085 x_purge_rec.table_name := 'RCV_TRANSACTIONS';
2086 SELECT COUNT(*) into l_num_rows
2087 FROM DUAL
2088 WHERE EXISTS (SELECT 1
2089 FROM RCV_TRANSACTIONS
2090 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2091 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id);
2092 x_records_found := x_records_found OR (l_num_rows > 0);
2093 if ( l_num_rows > 0 ) then
2094 append_report(x_purge_rec, p_option);
2095 end if;
2096 l_num_rows := 0;
2097
2098 x_purge_rec.table_name := 'MTL_TRANSACTION_ACCOUNTS';
2099 SELECT COUNT(*) into l_num_rows
2100 FROM DUAL
2101 WHERE EXISTS (SELECT 1
2102 FROM MTL_TRANSACTION_ACCOUNTS MTA,
2103 MTL_MATERIAL_TRANSACTIONS MMT
2104 WHERE MMT.TRANSACTION_SOURCE_TYPE_ID + 0 = 5
2105 AND MMT.TRANSACTION_SOURCE_ID = x_purge_rec.wip_entity_id
2106 AND MMT.REPETITIVE_LINE_ID = x_purge_rec.line_id
2107 AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
2108 AND MMT.ORGANIZATION_ID = x_purge_rec.org_id
2109 AND MTA.REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id);
2110 x_records_found := x_records_found OR (l_num_rows > 0);
2111 if ( l_num_rows > 0 ) then
2112 append_report(x_purge_rec, p_option);
2113 end if;
2114 l_num_rows := 0;
2115
2116 -- reset message; check for allocations
2117 fnd_message.set_name('WIP', 'WIP_ALLOCATIONS_EXIST');
2118 x_purge_rec.info := fnd_message.get;
2119
2120 x_purge_rec.table_name := 'MTL_MATERIAL_TXN_ALLOCATIONS';
2121 SELECT COUNT(*) into l_num_rows
2122 FROM DUAL
2123 WHERE EXISTS (SELECT 1
2124 FROM MTL_MATERIAL_TXN_ALLOCATIONS
2125 WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2126 AND ORGANIZATION_ID = x_purge_rec.org_id);
2127 x_records_found := x_records_found OR (l_num_rows > 0);
2128 if ( l_num_rows > 0 ) then
2129 append_report(x_purge_rec, p_option);
2130 end if;
2131 l_num_rows := 0;
2132
2133
2134 -- PASSED VALIDATIONS SO DELETE HEADER AND INTERFACE RECORDS
2135
2136 if (x_records_found = FALSE and p_sched_txn_flag = FALSE
2137 and p_sched_move_txn_flag = FALSE) then
2138
2139 x_purge_rec.info_type := ROWS_AFFECTED ;
2140 x_purge_rec.table_name := 'PO_DISTRIBUTIONS_INTERFACE';
2141
2142 savepoint sched_header01;
2143 DELETE FROM PO_DISTRIBUTIONS_INTERFACE
2144 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
2145 l_num_rows := sql%rowcount;
2146 if ( p_option = REPORT_ONLY) then
2147 -- rollback deletes
2148 rollback to sched_header01;
2149 end if;
2150 construct_report_content(p_option => p_option,
2151 p_num_rows => l_num_rows,
2152 p_purge_rec => x_purge_rec);
2153 l_num_rows := 0;
2154
2155
2156 x_purge_rec.table_name := 'PO_REQUISITIONS_INTERFACE_ALL';
2157
2158 savepoint sched_header02;
2159
2160 -- Bug 4880984
2161 -- Added wip_entity_id filter to this SQL to improve performance
2162 -- since an index based on wip_entity_id is available for
2163 -- table PO_REQUISITIONS_INTERFACE_ALL.
2164 DELETE FROM PO_REQUISITIONS_INTERFACE_ALL
2165 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2166 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
2167 l_num_rows := sql%rowcount;
2168 if ( p_option = REPORT_ONLY) then
2169 -- rollback deletes
2170 rollback to sched_header02;
2171 end if;
2172 construct_report_content(p_option => p_option,
2173 p_num_rows => l_num_rows,
2174 p_purge_rec => x_purge_rec);
2175 l_num_rows := 0;
2176
2177
2178 x_purge_rec.table_name := 'RCV_TRANSACTIONS_INTERFACE';
2179
2180 -- Bug 4880984
2181 -- Added wip_entity_id filter to these SQLs to improve performance
2182 -- since an index based on wip_entity_id is available for
2183 -- table rcv_transactions_interface
2184 if (p_option = REPORT_ONLY) then
2185 SELECT COUNT(*) INTO x_num_rows
2186 FROM RCV_TRANSACTIONS_INTERFACE
2187 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2188 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
2189 else
2190 DELETE FROM RCV_TRANSACTIONS_INTERFACE
2191 WHERE WIP_REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2192 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
2193 x_num_rows := SQL%ROWCOUNT ;
2194 end if ;
2195
2196 if x_num_rows > 0 then
2197 before_append_report(
2198 p_option => p_option,
2199 p_purge_rec => x_purge_rec,
2200 num_rows => x_num_rows);
2201 end if;
2202
2203
2204
2205 /* -- The following tables need not be checked - decided after the
2206 dicussion with mikec and djoffe - dsoosai 01/06/1996 --
2207
2208 x_purge_rec.table_name := 'WIP_MOVE_TXN_INTERFACE';
2209 delete_from_table(
2210 p_option => p_option,
2211 p_purge_rec => x_purge_rec,
2212 p_delete_statement =>
2213 'DELETE FROM ' || x_purge_rec.table_name ||
2214 ' WHERE REPETITIVE_SCHEDULE_ID = ' || to_char(x_purge_rec.schedule_id));
2215
2216 x_purge_rec.table_name := 'WIP_COST_TXN_INTERFACE';
2217 delete_from_table(
2218 p_option => p_option,
2219 p_purge_rec => x_purge_rec,
2220 p_delete_statement =>
2221 'DELETE FROM ' || x_purge_rec.table_name ||
2222 ' WHERE REPETITIVE_SCHEDULE_ID = ' || to_char(x_purge_rec.schedule_id));
2223
2224 ----------------------------------------------------------------------------*/
2225
2226 x_purge_rec.table_name := 'WIP_PERIOD_BALANCES';
2227
2228 if (p_option = REPORT_ONLY) then
2229 SELECT COUNT(*) INTO x_num_rows
2230 FROM WIP_PERIOD_BALANCES
2231 WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2232 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
2233 else
2234 DELETE FROM WIP_PERIOD_BALANCES
2235 WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id
2236 AND WIP_ENTITY_ID = x_purge_rec.wip_entity_id;
2237 x_num_rows := SQL%ROWCOUNT ;
2238 end if ;
2239
2240 if x_num_rows > 0 then
2241 before_append_report(
2242 p_option => p_option,
2243 p_purge_rec => x_purge_rec,
2244 num_rows => x_num_rows);
2245 end if;
2246
2247 x_purge_rec.table_name := 'WIP_REPETITIVE_SCHEDULES';
2248 if (p_option = REPORT_ONLY) then
2249 SELECT COUNT(*) INTO x_num_rows
2250 FROM WIP_REPETITIVE_SCHEDULES
2251 WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
2252 else
2253 DELETE FROM WIP_REPETITIVE_SCHEDULES
2254 WHERE REPETITIVE_SCHEDULE_ID = x_purge_rec.schedule_id;
2255 x_num_rows := SQL%ROWCOUNT ;
2256 end if ;
2257
2258 if x_num_rows > 0 then
2259 before_append_report(
2260 p_option => p_option,
2261 p_purge_rec => x_purge_rec,
2262 num_rows => x_num_rows);
2263 end if;
2264
2265 end if;
2266 end delete_sched_header ;
2267
2268 -- Bug 5129924
2269 -- Added the parameter p_days_before_cutoff
2270 -- ntungare Wed May 31 00:23:41 PDT 2006
2271 --
2272 function find(
2273 p_purge_type in number,
2274 p_conf_flag in boolean,
2275 p_org_id in number,
2276 p_cutoff_date in date,
2277 p_days_before_cutoff in number,
2278 p_from_job in varchar2,
2279 p_to_job in varchar2,
2280 p_primary_item_id in number,
2281 p_line_id in number,
2282 p_err_num in out nocopy number,
2283 p_error_text in out nocopy varchar2 ) return number is
2284
2285 x_group_id number;
2286 x_count number;
2287 x_group number;
2288 x_sql_stm1 varchar2(10000);
2289 x_sql_stm2 varchar2(10000);
2290 x_cursor_id integer;
2291 x_num_rows integer;
2292
2293 x_from_date date;
2294
2295 begin
2296 -- generate a group ID
2297 select Wip_purge_temp_s.nextval into x_group_id from dual;
2298
2299 -- find jobs
2300 if (p_purge_type in (PURGE_JOBS, PURGE_LOTBASED, PURGE_ALL)) then
2301
2302 x_sql_stm1 := ' insert into wip_purge_temp '||
2303 ' (group_id, ' ||
2304 ' wip_entity_id, ' ||
2305 ' repetitive_schedule_id, ' ||
2306 ' primary_item_id, ' ||
2307 ' line_id, ' ||
2308 ' start_date, ' ||
2309 ' complete_date, ' ||
2310 ' close_date, ' ||
2311 ' status_type, ' ||
2312 ' organization_id) ' ||
2313 ' select ' ||
2314 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2315 ' :l_group_id , ' ||
2316 ' wdj.wip_entity_id, ' ||
2317 ' NULL, ' ||
2318 ' wdj.primary_item_id, ' ||
2319 ' NULL, ' ||
2320 ' wdj.scheduled_start_date, ' ||
2321 ' wdj.scheduled_completion_date, ' ||
2322 ' wdj.date_closed, ' ||
2323 ' wdj.status_type, ' ||
2324 ' wdj.organization_id ' ||
2325 ' from wip_discrete_jobs wdj, ' ||
2326 ' wip_entities we ' ||
2327 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2328 ' where we.organization_id = :l_organization_id' ||
2329 ' and we.organization_id = wdj.organization_id ' ||
2330 ' and wdj.status_type = 12 ' || -- WIP_CONSTANTS.CLOSED
2331 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2332 /* Fix for bug#5137027 - remove '' for date bind variables */
2333 -- bug 5129924
2334 -- Commented out this condition as its handled
2335 -- separately below
2336 -- ntungare Wed May 31 00:25:28 PDT 2006
2337 --
2338 -- ' and wdj.date_closed <= :l_cutoff_date '||
2339 ' and we.wip_entity_id = wdj.wip_entity_id ' ;
2340 -- bug 5129924
2341 -- Added following if statement and removed
2342 -- wdj.date_closed condition from above sql
2343 -- ntungare Thu May 25 11:48:33 PDT 2006
2344 --
2345 if (p_days_before_cutoff is null ) then
2346 x_sql_stm1 := x_sql_stm1 || ' and wdj.date_closed <= :l_cutoff_date ' ;
2347 else
2348 select p_cutoff_date - nvl(p_days_before_cutoff, 0)
2349 into x_from_date
2350 from dual ;
2351
2352 x_sql_stm1 := x_sql_stm1 ||
2353 ' and wdj.date_closed between :l_from_date and :l_cutoff_date ' ;
2354 end if ;
2355
2356 if (p_purge_type = PURGE_JOBS) then
2357 x_sql_stm1 := x_sql_stm1 || ' and we.entity_type = 3 ' ; -- WIP_CONSTANTS.CLOSED_DISCRETE_JOBS
2358 elsif (p_purge_type = PURGE_LOTBASED) then
2359 x_sql_stm1 := x_sql_stm1 || ' and we.entity_type = 8 ' ; -- WIP_CONSTANTS.CLOSED_OSFM
2360 elsif (p_purge_type = PURGE_ALL) then
2361 x_sql_stm1 := x_sql_stm1 || ' and we.entity_type in (3, 8) ' ;
2362 end if ;
2363
2364 if (p_primary_item_id is not null) then
2365 x_sql_stm1 := x_sql_stm1 || ' and wdj.primary_item_id = :l_primary_item_id ' ;
2366 end if;
2367
2368 if (p_from_job is not null) then
2369 x_sql_stm1 := x_sql_stm1 || ' and we.wip_entity_name >= :l_from_job ' ;
2370 end if ;
2371
2372 if (p_to_job is not null) then
2373 x_sql_stm1 := x_sql_stm1 || ' and we.wip_entity_name <= :l_to_job ';
2374 end if;
2375
2376 if (not p_conf_flag) then
2377 x_sql_stm1 := x_sql_stm1 || ' and (wdj.primary_item_id is null ' ||
2378 ' or ' ||
2379 ' exists ' ||
2380 ' (select msi.inventory_item_id ' ||
2381 ' from mtl_system_items msi ' ||
2382 ' where msi.inventory_item_id = wdj.primary_item_id ' ||
2383 ' and msi.organization_id = wdj.organization_id ' ||
2384 ' and msi.base_item_id is null ' ||
2385 ' and msi.bom_item_type = 4 /*standard*/)) ';
2386 end if;
2387
2388 x_cursor_id := dbms_sql.open_cursor;
2389 dbms_sql.parse(x_cursor_id, x_sql_stm1, dbms_sql.v7);
2390
2391 /* Fix for bug#4902938 */
2392 if ( x_group_id is not null ) then
2393 dbms_sql.bind_variable(x_cursor_id, ':l_group_id', x_group_id);
2394 end if;
2395 if ( to_char(p_org_id) is not null ) then
2396 dbms_sql.bind_variable(x_cursor_id, ':l_organization_id', to_char(p_org_id));
2397 end if;
2398
2399 -- Bug 5129924
2400 -- Binding the values
2401 -- ntungare
2402 --
2403 if (p_days_before_cutoff is not null ) then
2404 dbms_sql.bind_variable(x_cursor_id, ':l_from_date', x_from_date);
2405 end if;
2406
2407 if ( p_cutoff_date is not null ) then
2408 dbms_sql.bind_variable(x_cursor_id, ':l_cutoff_date', p_cutoff_date);
2409 end if;
2410
2411 /* END Fix for bug#4902938 */
2412
2413 if ( p_primary_item_id is not null ) then
2414 dbms_sql.bind_variable(x_cursor_id, ':l_primary_item_id', p_primary_item_id);
2415 end if;
2416 if ( p_from_job is not null ) then
2417 dbms_sql.bind_variable(x_cursor_id, ':l_from_job', p_from_job);
2418 end if;
2419 if ( p_to_job is not null ) then
2420 dbms_sql.bind_variable(x_cursor_id, ':l_to_job', p_to_job);
2421 end if;
2422 x_num_rows := dbms_sql.execute(x_cursor_id);
2423 dbms_sql.close_cursor(x_cursor_id);
2424
2425 end if;
2426
2427 -- find schedules
2428 if (p_purge_type in (PURGE_SCHEDS, PURGE_ALL)) then
2429
2430
2431 x_sql_stm2 := ' insert into wip_purge_temp '||
2432 ' (group_id, ' ||
2433 ' wip_entity_id, ' ||
2434 ' repetitive_schedule_id, ' ||
2435 ' primary_item_id, ' ||
2436 ' line_id, ' ||
2437 ' start_date, ' ||
2438 ' complete_date, ' ||
2439 ' close_date, ' ||
2440 ' status_type, ' ||
2441 ' organization_id) ' ||
2442 ' select distinct ' ||
2443 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2444 ' :l_group_id , ' ||
2445 ' wrs.wip_entity_id, ' ||
2446 ' wrs.repetitive_schedule_id, ' ||
2447 ' wri.primary_item_id, ' ||
2448 ' wri.line_id, ' ||
2449 ' wrs.first_unit_start_date, ' ||
2450 ' wrs.last_unit_completion_date, ' ||
2451 ' wrs.date_closed, ' ||
2452 ' wrs.status_type, ' ||
2453 ' wrs.organization_id ' ||
2454 ' from wip_repetitive_schedules wrs , ' ||
2455 ' wip_repetitive_items wri ' ||
2456 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2457 ' where wri.organization_id = :l_organization_id' ||
2458 ' and wrs.organization_id = wri.organization_id ' ||
2459 ' and wrs.wip_entity_id = wri.wip_entity_id ' ||
2460 ' and wrs.line_id = wri.line_id ' ||
2461 /* Fix for bug#4902938 - Convert Literal to Bind variable*/
2462 /* Fix for bug#5137027 - remove '' for date bind variables */
2463 -- bug 5129924
2464 -- Commented this where clause as its handled below
2465 -- ntungare
2466 -- ' and wrs.date_closed <= :l_cutoff_date '||
2467 ' and wrs.status_type in ( 7,5 ) ' ; -- WIP_CONSTANTS.COMP_NOCHRG, CANCELLED
2468
2469 -- bug 5129924
2470 -- Added following if statement and
2471 -- removed wrs.date_closed condition in
2472 -- above sql
2473 -- ntungare
2474 --
2475 if (p_days_before_cutoff is null ) then
2476 x_sql_stm2 := x_sql_stm2 || ' and wrs.date_closed <= :l_cutoff_date ' ;
2477 else
2478 select p_cutoff_date - nvl(p_days_before_cutoff, 0)
2479 into x_from_date
2480 from dual ;
2481
2482 x_sql_stm2 := x_sql_stm2 ||
2483 ' and wrs.date_closed between :l_from_date and :l_cutoff_date ' ;
2484 end if ;
2485
2486 if (p_line_id is not null) then
2487 x_sql_stm2 := x_sql_stm2 || ' and wrs.line_id = :l_line_id ';
2488 end if;
2489
2490 if (p_primary_item_id is not null) then
2491 x_sql_stm2 := x_sql_stm2 || ' and wri.primary_item_id = :l_primary_item_id ';
2492 end if;
2493
2494
2495 x_cursor_id := dbms_sql.open_cursor;
2496 dbms_sql.parse(x_cursor_id, x_sql_stm2, dbms_sql.v7);
2497
2498 /* Fix for bug#4902938 */
2499 if ( x_group_id is not null ) then
2500 dbms_sql.bind_variable(x_cursor_id, ':l_group_id', x_group_id);
2501 end if;
2502 if ( to_char(p_org_id) is not null ) then
2503 dbms_sql.bind_variable(x_cursor_id, ':l_organization_id', to_char(p_org_id));
2504 end if;
2505
2506 -- Bug 5129924
2507 -- Binding the values
2508 -- ntungare
2509 --
2510 if ( p_days_before_cutoff is not null ) then
2511 dbms_sql.bind_variable(x_cursor_id, ':l_from_date', x_from_date);
2512 end if;
2513
2514 if ( p_cutoff_date is not null ) then
2515 dbms_sql.bind_variable(x_cursor_id, ':l_cutoff_date', p_cutoff_date);
2516 end if;
2517 /* END Fix for bug#4902938 */
2518
2519 if ( p_line_id is not null ) then
2520 dbms_sql.bind_variable(x_cursor_id, ':l_line_id', p_line_id);
2521 end if;
2522 if ( p_primary_item_id is not null ) then
2523 dbms_sql.bind_variable(x_cursor_id, ':l_primary_item_id', p_primary_item_id);
2524 end if;
2525
2526 x_num_rows := dbms_sql.execute(x_cursor_id);
2527 dbms_sql.close_cursor(x_cursor_id);
2528
2529
2530 end if;
2531
2532 -- write changes
2533 commit;
2534
2535 -- get count
2536 select count(group_id)
2537 into x_count
2538 from wip_purge_temp
2539 where group_id = x_group_id;
2540
2541 -- if nothing was found then return a 0
2542 if (x_count = 0) then
2543 x_group := x_count;
2544 else
2545 x_group := x_group_id ;
2546 end if;
2547
2548 return(x_group);
2549
2550 exception
2551 when others then
2552 p_err_num := SQLCODE;
2553 p_error_text := SUBSTR(SQLERRM, 1, 500);
2554 rollback;
2555 return(-1);
2556 end find;
2557
2558 -- Bug 5129924
2559 -- Added a new parameter
2560 -- p_days_before_cutoff
2561 -- ntungare
2562 --
2563 function purge(
2564 p_purge_type in number,
2565 p_group_id in number,
2566 p_org_id in number,
2567 p_cutoff_date in date,
2568 p_days_before_cutoff in number,
2569 p_from_job in varchar2,
2570 p_to_job in varchar2,
2571 p_primary_item_id in number,
2572 p_line_id in number,
2573 p_option in number default NULL,
2574 p_conf_flag in boolean default NULL,
2575 p_header_flag in boolean default NULL,
2576 p_detail_flag in boolean default NULL,
2577 p_move_trx_flag in boolean default NULL,
2578 p_cost_trx_flag in boolean default NULL,
2579 p_err_num in out NOCOPY number,
2580 p_error_text in out NOCOPY varchar2
2581 ) return number is
2582
2583 x_dummy number;
2584 x_group_id number;
2585 x_found boolean;
2586 x_sched_move_txn_flag boolean := FALSE;
2587 x_sched_txn_flag boolean := FALSE;
2588 x_purge_request get_purge_requests%rowtype;
2589
2590 x_ret_success varchar2(1) := FND_API.G_RET_STS_SUCCESS ;
2591 x_commit_count number := 0 ;
2592 begin
2593 -- this procedure performs periodic commits to prevent rollback segments
2594 -- from filling up
2595 -- Bug 2413526 -- introduced a batch commit counter so that commit
2596 -- occurs every 100 th record.This reduces rollback segment error
2597
2598 -- use passed in group ID or generate one after finding
2599 -- Bug 5129924
2600 -- Passing the parameter p_days_before_cutoff
2601 -- to the find function
2602 -- ntungare
2603 --
2604 if (p_group_id is NULL) then
2605 x_group_id := find(p_purge_type => p_purge_type,
2606 p_conf_flag => nvl(p_conf_flag,FALSE),
2607 p_org_id => p_org_id,
2608 p_cutoff_date => p_cutoff_date,
2609 p_days_before_cutoff => p_days_before_cutoff,
2610 p_from_job => p_from_job,
2611 p_to_job => p_to_job,
2612 p_primary_item_id => p_primary_item_id,
2613 p_line_id => p_line_id,
2614 p_err_num => p_err_num,
2615 p_error_text => p_error_text );
2616
2617 -- return 0 if no data found
2618 if (x_group_id <= 0) then
2619 return(x_group_id);
2620 end if ;
2621
2622 else
2623 x_group_id := p_group_id;
2624 end if;
2625
2626 open get_purge_requests(
2627 c_purge_type => p_purge_type,
2628 c_group_id => x_group_id);
2629 x_commit_count := 0;
2630 loop
2631 fetch get_purge_requests into x_purge_request;
2632
2633 x_found := get_purge_requests%FOUND;
2634
2635 -- break out if no more jobs
2636 exit when (not x_found);
2637 x_commit_count := x_commit_count + 1;
2638 if ((p_purge_type = PURGE_LOTBASED) OR (p_purge_type = PURGE_ALL)) then
2639 -- Call OSFM API To Delete OSFM specific tables
2640 WSM_JobPurge_GRP.delete_osfm_tables(
2641 p_option => p_option,
2642 p_group_id => x_group_id,
2643 p_purge_request => x_purge_request,
2644 --Bug#4918553 - Passing detail_flag to OSFM
2645 p_detail_flag => nvl(p_detail_flag, false),
2646 p_return_status => x_ret_success
2647 );
2648 end if ;
2649
2650 if (x_ret_success = FND_API.G_RET_STS_SUCCESS) then
2651 /* OSFM API returns success status to continue .
2652 For other purge_type it is defaulted to success
2653 */
2654
2655 -- delete the job details
2656 if (nvl(p_detail_flag,FALSE)) then
2657 -- For Jobs
2658 if ( (p_purge_type in (PURGE_JOBS, PURGE_LOTBASED)) or
2659 ( (p_purge_type = PURGE_ALL) and (x_purge_request.line_id is NULL) ) ) then
2660 delete_job_details(
2661 p_option => nvl(p_option,REPORT_ONLY),
2662 p_group_id => x_group_id,
2663 p_purge_request => x_purge_request);
2664 else
2665 -- For Repetitive Schedules
2666 delete_sched_details(
2667 p_option => nvl(p_option,REPORT_ONLY),
2668 p_group_id => x_group_id,
2669 p_purge_request => x_purge_request);
2670
2671 end if;
2672
2673 end if;
2674
2675
2676 -- delete the move transactions
2677 if (nvl(p_move_trx_flag,FALSE)) then
2678 -- For Jobs
2679 if ( (p_purge_type in (PURGE_JOBS, PURGE_LOTBASED)) or
2680 ( (p_purge_type = PURGE_ALL) and (x_purge_request.line_id is NULL) ) ) then
2681 delete_job_move_trx(
2682 p_option => nvl(p_option,REPORT_ONLY),
2683 p_group_id => x_group_id,
2684 p_purge_request => x_purge_request);
2685 else
2686 -- For Repetitive Schedules
2687 delete_sched_move_trx(
2688 p_option => nvl(p_option,REPORT_ONLY),
2689 p_group_id => x_group_id,
2690 p_purge_request => x_purge_request,
2691 p_cutoff_date => p_cutoff_date,
2692 p_sched_move_txn_flag => x_sched_move_txn_flag );
2693 end if;
2694
2695 end if;
2696
2697 -- delete the resource transactions
2698 if (nvl(p_cost_trx_flag,FALSE)) then
2699 -- For Jobs
2700 if ( (p_purge_type in (PURGE_JOBS, PURGE_LOTBASED)) or
2701 ( (p_purge_type = PURGE_ALL) and (x_purge_request.line_id is NULL) ) ) then
2702 delete_job_cost_trx(
2703 p_option => nvl(p_option,REPORT_ONLY),
2704 p_group_id => x_group_id,
2705 p_purge_request => x_purge_request,
2706 p_cut_off_date => p_cutoff_date);
2707 else
2708 -- For Repetitive Schedules
2709 delete_sched_cost_trx(
2710 p_option => nvl(p_option,REPORT_ONLY),
2711 p_group_id => x_group_id,
2712 p_purge_request => x_purge_request,
2713 p_cutoff_date => p_cutoff_date,
2714 p_sched_txn_flag => x_sched_txn_flag );
2715 end if;
2716
2717 end if;
2718
2719 -- delete the job header
2720 if (nvl(p_header_flag,FALSE)) then
2721 -- For Jobs
2722 if ( (p_purge_type = PURGE_JOBS) or
2723 ( (p_purge_type = PURGE_ALL) and (x_purge_request.line_id is NULL)
2724 and (x_purge_request.entity_type <> WIP_CONSTANTS.CLOSED_OSFM)) ) then
2725 delete_job_header(
2726 p_option => nvl(p_option,REPORT_ONLY),
2727 p_group_id => x_group_id,
2728 p_purge_request => x_purge_request);
2729 elsif (p_purge_type = PURGE_SCHEDS) then
2730 -- For Repetitive Schedules
2731 delete_sched_header(
2732 p_option => nvl(p_option,REPORT_ONLY),
2733 p_group_id => x_group_id,
2734 p_purge_request => x_purge_request,
2735 p_sched_move_txn_flag => x_sched_move_txn_flag,
2736 p_sched_txn_flag => x_sched_txn_flag );
2737
2738 end if;
2739
2740 end if;
2741
2742 end if ; /* If l_ret_sucess */
2743 if (x_commit_count = 100 ) then
2744 commit ;
2745 x_commit_count := 0 ;
2746 end if ;
2747 end loop;
2748 commit;
2749 close get_purge_requests;
2750
2751 -- delete the records sitting in the WIP_PURGE_TEMP table
2752 delete_purge_temp_table(x_group_id);
2753 return (x_group_id);
2754
2755 exception
2756 when others then
2757 p_err_num := SQLCODE;
2758 p_error_text := SUBSTR(SQLERRM, 1, 500);
2759
2760 if get_purge_requests%ISOPEN then
2761 close get_purge_requests ;
2762 end if ;
2763
2764 rollback;
2765 return(-1);
2766
2767
2768 end purge;
2769
2770 end wip_wictpg;