1 PACKAGE BODY inv_mo_purge AS
2 /* $Header: INVMOPGB.pls 120.9 2011/07/22 11:45:42 pdong ship $ */
3
4
5 PROCEDURE purge_lines(
6 x_errbuf OUT NOCOPY VARCHAR2
7 , x_retcode OUT NOCOPY NUMBER
8 , p_organization_id IN NUMBER := NULL
9 , p_mo_type_id IN NUMBER := NULL
10 , p_purge_name IN VARCHAR2 := NULL
11 , p_date_from IN VARCHAR2 := NULL
12 , p_date_to IN VARCHAR2 := NULL
13 , p_mol_pc IN NUMBER := NULL
14 ) IS
15 l_count NUMBER := 0;
16 mo_line mol_rec;
17 can_delete NUMBER := 0;
18 l_ret_msg BOOLEAN;
19 error VARCHAR2(400);
20 inv_int_flag VARCHAR2(1); -- Added for bug # 6469970
21 wdd_exists VARCHAR2(1); -- Added for bug # 6469970
22 oel_exists VARCHAR2(1); -- Added for bug # 6469970
23 l_total_loop NUMBER := 0;
24 l_date_from DATE := fnd_date.canonical_to_date(p_date_from);
25 l_date_to DATE := fnd_date.canonical_to_date(p_date_to);
26 l_prev_header_id mtl_txn_request_headers.header_id%TYPE;
27 -- Bug 7421347 Added l_entity_type
28 l_entity_type NUMBER;
29
30 -- Bug 4237808
31 -- Rewriting the cursor below to pick up an index (instead of
32 -- full table scan) to improve the SQL performance
33 CURSOR mo_lines IS
34 SELECT mtrh.header_id
35 , mtrl.line_id
36 , mtrh.move_order_type
37 , mtrl.line_status
38 , mtrl.quantity quantity
39 , NVL(mtrl.quantity_detailed, 0) quantity_detailed
40 , NVL(mtrl.quantity_delivered, 0) quantity_delivered
41 , NVL(required_quantity, 0) required_quantity
42 , mtrl.txn_source_line_id
43 , mtrl.txn_source_id
44 , mtrl.organization_id
45 FROM mtl_txn_request_headers mtrh
46 , mtl_txn_request_lines mtrl
47 WHERE mtrh.header_id = mtrl.header_id
48 AND mtrl.line_status IN (5, 6, 9)
49 AND ( p_organization_id IS NULL
50 OR mtrh.organization_id = p_organization_id )
51 AND ( p_organization_id IS NULL
52 OR mtrl.organization_id = p_organization_id )
53 AND ( p_mo_type_id IS NULL
54 OR mtrh.move_order_type = p_mo_type_id )
55 AND ( l_date_from IS NULL
56 OR TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
57 >= TO_DATE ( TO_CHAR ( l_date_from , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' ) )
58 AND TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
59 <= TO_DATE ( TO_CHAR ( l_date_to , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
60 ORDER BY mtrh.header_id;
61
62 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
63 BEGIN
64 IF (l_debug = 1) THEN
65 inv_trx_util_pub.TRACE('p_organization_id = '|| p_organization_id);
66 inv_trx_util_pub.TRACE('p_mo_type_id = '|| p_mo_type_id);
67 inv_trx_util_pub.TRACE('p_purge_name ='|| p_purge_name);
68 inv_trx_util_pub.TRACE('p_date_from='|| p_date_from);
69 inv_trx_util_pub.TRACE('p_date_to='|| p_date_to);
70 inv_trx_util_pub.TRACE('p_mol_pc='|| p_mol_pc);
71 END IF;
72
73 OPEN mo_lines;
74
75 LOOP
76 FETCH mo_lines INTO mo_line;
77 can_delete := 0;
78 l_total_loop := l_total_loop + 1;
79 EXIT WHEN mo_lines%NOTFOUND;
80
81 IF (mo_line.mo_type IN (1, 2, 6) AND mo_line.line_status IN (5, 6)) THEN
82 can_delete := 1;
83 END IF;
84
85 -- Pick Wave Move Order
86 IF mo_line.mo_type = 3 THEN
87 IF mo_line.line_status IN (5, 6) THEN
88 /* Delete MTRL if inventory interface has completed successfully and
89 order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y')
90 or is cancelled (CANCELLED_FLAG = 'Y')*/
91 wdd_exists := 'Y'; -- Added for bug # 6469970
92 oel_exists := 'Y'; -- Added for bug # 6469970
93 BEGIN
94 SELECT 1
95 INTO can_delete
96 FROM wsh_delivery_details wsd
97 WHERE wsd.move_order_line_id = mo_line.line_id
98 AND wsd.inv_interfaced_flag = 'Y'
99 AND ROWNUM < 2;
100
101 IF (can_delete = 1) THEN
102 SELECT 1
103 INTO can_delete
104 FROM oe_order_lines_all oola
105 WHERE oola.line_id = mo_line.txn_source_line_id
106 AND (oola.open_flag <> 'Y'
107 OR oola.cancelled_flag = 'Y'
108 )
109 AND ROWNUM < 2;
110 END IF;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 can_delete := 0;
114 END;
115
116 --Bug #4864356
117 --Delete the move order lines where the order line has been cancelled
118 IF (can_delete = 0) THEN
119 BEGIN
120 SELECT 1
121 INTO can_delete
122 FROM oe_order_lines_all oola
123 WHERE oola.line_id = mo_line.txn_source_line_id
124 AND ( (NVL(open_flag, 'Y') = 'N') OR cancelled_flag = 'Y')
125 AND NOT EXISTS (
126 SELECT 1
127 FROM wsh_delivery_details wdd
128 WHERE wdd.source_line_id = oola.line_id
129 AND wdd.inv_interfaced_flag IN ('N', 'P')
130 AND wdd.released_status <> 'D')
131 AND ROWNUM =1;
132 EXCEPTION
133 WHEN OTHERS THEN
134 can_delete := 0;
135 END;
136 END IF; --END IF delete canceled SO lines
137
138 --Bug #4864356
139 --Delete the move order lines where the corresponding delivery detail
140 --has been completely backordered
141 --Bug #7249224, Added the outer paranthesis in the below IF clause.
142 IF (can_delete = 0 AND mo_line.line_status = 5 AND
143 ((mo_line.quantity = mo_line.quantity_delivered
144 AND
145 mo_line.quantity_detailed = mo_line.quantity_delivered)
146 OR
147 (mo_line.quantity > 0 AND mo_line.quantity_delivered = 0))) THEN
148 BEGIN
149 SELECT 1
150 INTO can_delete
151 FROM oe_order_lines_all oola
152 WHERE oola.line_id = mo_line.txn_source_line_id
153 AND (NVL(open_flag,'Y') ='Y' and cancelled_flag='N')
154 AND NOT EXISTS (
155 SELECT 1
156 FROM mtl_material_transactions_temp mmtt
157 WHERE mmtt.move_order_line_id = mo_line.line_id)
158 AND NOT EXISTS (
159 SELECT 1
160 FROM wsh_delivery_details wdd
161 WHERE wdd.source_line_id = oola.line_id
162 AND wdd.source_code = 'OE'
163 AND wdd.move_order_line_id = mo_line.line_id)
164 AND ROWNUM = 1;
165 EXCEPTION
166 WHEN OTHERS THEN
167 can_delete := 0;
168 END;
169 END IF; --END IF delete completely backordered lines
170
171 -- Start of Bug #6469970
172 IF (can_delete = 0) THEN
173 BEGIN
174 SELECT distinct wdd.inv_interfaced_flag into inv_int_flag
175 FROM wsh_delivery_details wdd
176 WHERE wdd.move_order_line_id = mo_line.line_id;
177
178 IF inv_int_flag = 'Y' THEN
179 can_delete := 1;
180 ELSE
181 can_delete := 0;
182 END IF;
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 can_delete := 1; -- No corresponding wdd record
186 wdd_exists := 'N';
187 WHEN TOO_MANY_ROWS THEN
188 can_delete := 0; -- At least one inv_int_flag is set to 'N' or 'P' in wdd
189 END;
190
191 IF (can_delete = 1 and wdd_exists = 'N') THEN
192 BEGIN
193 SELECT 0
194 INTO can_delete
195 FROM oe_order_lines_all oola
196 WHERE oola.line_id = mo_line.txn_source_line_id
197 AND (oola.open_flag = 'Y' AND NVL(oola.CANCELLED_FLAG,'N')='N');
198 -- If row found; can't delete
199 can_delete := 0;
200 EXCEPTION WHEN NO_DATA_FOUND THEN
201 can_delete := 1;
202 END;
203 END IF;
204 END IF;
205
206 -- The below If to check there is no corresponding order lines record.
207 -- Order information is purged before the move order purge program.
208 IF (can_delete = 0) THEN
209 BEGIN
210 SELECT 'Y'
211 INTO oel_exists
212 FROM oe_order_lines_all oola
213 WHERE oola.line_id = mo_line.txn_source_line_id
214 and rownum < 2;
215
216 IF oel_exists = 'Y' THEN
217 can_delete := 0;
218 ELSE
219 can_delete := 1;
220 END IF;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 can_delete := 1; -- there is no oel record
224 END;
225 END IF;
226 --End of changes for Bug #6469970
227
228 ELSE -- line status is 9
229 /* Delete MTRL if allocations doesn't exist
230 (MMTT should not exist for this line id, MMTT.move_order_line_id = MTRL.line_id)
231 and order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y') */
232 BEGIN
233 SELECT 1
234 INTO can_delete
235 FROM mtl_material_transactions_temp mmtt
236 WHERE mmtt.move_order_line_id = mo_line.line_id
237 AND ROWNUM < 2;
238
239 can_delete := 0;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 BEGIN
243 SELECT 1
244 INTO can_delete
245 FROM oe_order_lines_all oola
246 WHERE oola.line_id = mo_line.txn_source_line_id
247 AND (oola.open_flag <> 'Y'
248 OR oola.cancelled_flag = 'Y'
249 )
250 AND ROWNUM < 2;
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 can_delete := 0;
254 END;
255 END;
256 END IF;
257 END IF;
258
259 -- WIP Move Order
260 -- Bug 2666620: BackFlush MO Type Removed.
261 -- Since logic for both WIP Issue, WIP SubXfer is same, combining both
262 IF mo_line.mo_type = 5 THEN
263 IF mo_line.line_status IN (5, 6) THEN
264 -- Delete MTRL for this line_id
265 -- can_delete := 1;
266 -- Bug 7421347
267 -- We have to purge move orders for which the job status is closed.
268 -- We now look at the different entity types like Discrete, lot based, Flow and Repetitive Schedules,
269 If (l_debug = 1) then
270 inv_trx_util_pub.TRACE('txn_source_id : '|| mo_line.txn_source_id);
271 inv_trx_util_pub.TRACE('organization_id : '|| mo_line.organization_id);
272 end if;
273
274 Begin
275 select entity_type
276 into l_entity_type
277 from wip_entities
278 where wip_entity_id = mo_line.txn_source_id
279 and organization_id = mo_line.organization_id;
280 Exception
281 when others then
282 if (l_debug = 1) then
283 inv_trx_util_pub.TRACE('other exc.when getting entity_type setting can_delete as 0 '|| sqlerrm);
284 end if;
285 l_entity_type := 0;
286 can_delete := 1;
287 End;
288
289 if (l_debug = 1) then
290 inv_trx_util_pub.TRACE('WIP Entity Type = '|| l_entity_type);
291 end if;
292
293 IF (l_entity_type in (3,7,8)) THEN
294 Begin
295 select 0
296 into can_delete
297 from wip_discrete_jobs
298 where wip_entity_id = mo_line.txn_source_id
299 and organization_id = mo_line.organization_id
300 and status_type <> 12;
301 Exception
302 when others then
303 if (l_debug = 1) then
304 inv_trx_util_pub.TRACE('other exc. when l_entity_type in (3,7,8) setting can_delete as 0 '|| sqlerrm);
305 end if;
306 can_delete := 1;
307 End;
308
309 ELSIF (l_entity_type = 2) THEN
310 -- Bug# 8209102
311 -- Changed the status_type to 5, as for Repetetive Schedules, MOs can be purged when the
312 -- schedule status is Complete - No Charges
313 Begin
314 select 0
315 into can_delete
316 from wip_repetitive_schedules
317 where wip_entity_id = mo_line.txn_source_id
318 and organization_id = mo_line.organization_id
319 and status_type <> 5;
320 Exception
321 when others then
322 if (l_debug = 1) then
323 inv_trx_util_pub.TRACE('other exc. when l_entity_type = 2 setting can_delete as 0 '|| sqlerrm);
324 end if;
325 can_delete := 1;
326 End;
327
328 ELSIF (l_entity_type = 4) THEN
329 Begin
330 select 0
331 into can_delete
332 from wip_flow_schedules
333 where wip_entity_id = mo_line.txn_source_id
334 and organization_id = mo_line.organization_id
335 and status <> 2;
336 Exception
337 when others then
338 if (l_debug = 1) then
339 inv_trx_util_pub.TRACE('other exc. when l_entity_type = 4 setting can_delete as 0 '|| sqlerrm);
340 end if;
341 can_delete := 1;
342 End;
343 ELSIF (l_entity_type = 6) THEN --bug12767095 purge EAM work order
344 BEGIN
345 SELECT 0
346 INTO can_delete
347 FROM wip_discrete_jobs
348 WHERE wip_entity_id = mo_line.txn_source_id
349 AND organization_id = mo_line.organization_id
350 AND status_type not in (12,7);--closed or cancelled
351 Exception
352 when others then
353 if (l_debug = 1) then
354 inv_trx_util_pub.TRACE('other exc. when l_entity_type = 6 setting can_delete as 0 '|| sqlerrm);
355 end if;
356 can_delete := 1;
357 End;--end bug12767095
358
359 END IF; -- l_entity_type
360
364 SELECT 1
361 ELSE -- line status is 9
362 --Delete MTRL if allocations doesn't exist for this MO line
363 BEGIN
365 INTO can_delete
366 FROM mtl_material_transactions_temp mmtt
367 WHERE mmtt.move_order_line_id = mo_line.line_id
368 AND ROWNUM < 2;
369
370 can_delete := 0;
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 can_delete := 1;
374 END;
375 END IF;
376 END IF;
377
378 IF (can_delete = 1) THEN
379 -- inv_trx_util_pub.TRACE('DELETED lines---mo_line.line_id= ' ||mo_line.line_id ,'INVMOPG',9);
380 DELETE FROM mtl_txn_request_lines
381 WHERE line_id = mo_line.line_id;
382
383 can_delete := 0;
384 l_count := l_count + 1;
385 END IF;
386
387 -- For deleting headers
388 IF (l_prev_header_id <> mo_line.header_id) THEN
389 DELETE FROM mtl_txn_request_headers
390 WHERE header_id = l_prev_header_id
391 AND NOT EXISTS( SELECT 1
392 FROM mtl_txn_request_lines
393 WHERE header_id = l_prev_header_id);
394
395 IF (SQL%FOUND) THEN
396 l_count := l_count + 1;
397 can_delete := 0;
398 --inv_trx_util_pub.TRACE('DELETED Headers mo_line.header_id= ' ||l_prev_header_id ,'INVMOPG',9);
399 END IF;
400 END IF;
401
402 IF (l_count >= p_mol_pc) THEN
403 IF (MOD(l_count, p_mol_pc) = 0) THEN
404 COMMIT;
405 END IF;
406 END IF;
407
408 l_prev_header_id := mo_line.header_id;
409 END LOOP;
410
411 DELETE FROM mtl_txn_request_headers
412 WHERE header_id = mo_line.header_id
413 AND NOT EXISTS( SELECT 1
414 FROM mtl_txn_request_lines
415 WHERE header_id = mo_line.header_id);
416
417 IF (SQL%FOUND) THEN
418 -- Bug 7421347 l_count to be used to count the number of lines deleted, not for header
419 --l_count := l_count + 1;
420 can_delete := 0;
421 --inv_trx_util_pub.TRACE('DELETED Headers mo_line.header_id= ' || mo_line.header_id ,'INVMOPG',9);
422 END IF;
423
424 IF (l_debug = 1) THEN
425 inv_trx_util_pub.TRACE(l_count || 'Rows Purged ', 'INVMOPG', 9);
426 END IF;
427
428 INSERT INTO mtl_purge_header
429 (
430 purge_id
431 , last_update_date
432 , last_updated_by
433 , last_update_login
434 , creation_date
435 , created_by
436 , purge_date
437 , move_order_type
438 , archive_flag
439 , purge_name
440 , organization_id
441 , creation_date_from
442 , creation_date_to
443 , lines_purged
444 )
445 VALUES (
446 mtl_material_transactions_s.NEXTVAL
447 , SYSDATE
448 , fnd_global.user_id
449 , fnd_global.user_id
450 , SYSDATE
451 , fnd_global.user_id
452 , SYSDATE
453 , p_mo_type_id
454 , NULL
455 , p_purge_name
456 , p_organization_id
457 , l_date_from
458 , l_date_to
459 , l_count
460 );
461
462 COMMIT;
463 --return sucess
464 l_ret_msg := fnd_concurrent.set_completion_status('NORMAL', 'NORMAL');
465 x_retcode := retcode_success;
466 x_errbuf := NULL;
467 EXCEPTION
468 WHEN fnd_api.g_exc_error THEN
469 error := SQLERRM;
470
471 IF (l_debug = 1) THEN
472 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
473 END IF;
474
475 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
476 x_retcode := retcode_error;
477 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
478 WHEN fnd_api.g_exc_unexpected_error THEN
479 error := SQLERRM;
480
481 IF (l_debug = 1) THEN
482 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
483 END IF;
484
485 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
486 x_retcode := retcode_error;
487 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
488 WHEN OTHERS THEN
489 error := SQLERRM;
490
491 IF (l_debug = 1) THEN
492 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
493 END IF;
494
495 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
496 x_retcode := retcode_error;
497 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
498
499 IF mo_lines%ISOPEN THEN
500 CLOSE mo_lines;
501 --return failure
502 END IF;
503 END purge_lines;
504 END inv_mo_purge;
[Home] [Help]