1 PACKAGE BODY inv_mo_purge AS
2 /* $Header: INVMOPGB.pls 120.5.12010000.2 2008/09/09 11:11:01 appldev 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
28 -- Bug 4237808
29 -- Rewriting the cursor below to pick up an index (instead of
30 -- full table scan) to improve the SQL performance
31 CURSOR mo_lines IS
32 SELECT mtrh.header_id
33 , mtrl.line_id
34 , mtrh.move_order_type
35 , mtrl.line_status
36 , mtrl.quantity quantity
37 , NVL(mtrl.quantity_detailed, 0) quantity_detailed
38 , NVL(mtrl.quantity_delivered, 0) quantity_delivered
39 , NVL(required_quantity, 0) required_quantity
40 , txn_source_line_id
41 FROM mtl_txn_request_headers mtrh
42 , mtl_txn_request_lines mtrl
43 WHERE mtrh.header_id = mtrl.header_id
44 AND mtrl.line_status IN (5, 6, 9)
45 AND ( p_organization_id IS NULL
46 OR mtrh.organization_id = p_organization_id )
47 AND ( p_organization_id IS NULL
48 OR mtrl.organization_id = p_organization_id )
49 AND ( p_mo_type_id IS NULL
50 OR mtrh.move_order_type = p_mo_type_id )
51 AND ( l_date_from IS NULL
52 OR TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
53 >= TO_DATE ( TO_CHAR ( l_date_from , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' ) )
54 AND TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
55 <= TO_DATE ( TO_CHAR ( l_date_to , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
56 ORDER BY mtrh.header_id;
57
58 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
59 BEGIN
60 IF (l_debug = 1) THEN
61 inv_trx_util_pub.TRACE('p_organization_id = '|| p_organization_id);
62 inv_trx_util_pub.TRACE('p_mo_type_id = '|| p_mo_type_id);
63 inv_trx_util_pub.TRACE('p_purge_name ='|| p_purge_name);
64 inv_trx_util_pub.TRACE('p_date_from='|| p_date_from);
65 inv_trx_util_pub.TRACE('p_date_to='|| p_date_to);
66 inv_trx_util_pub.TRACE('p_mol_pc='|| p_mol_pc);
67 END IF;
68
69 OPEN mo_lines;
70
71 LOOP
72 FETCH mo_lines INTO mo_line;
73 can_delete := 0;
74 l_total_loop := l_total_loop + 1;
75 EXIT WHEN mo_lines%NOTFOUND;
76
77 IF (mo_line.mo_type IN (1, 2, 6) AND mo_line.line_status IN (5, 6)) THEN
78 can_delete := 1;
79 END IF;
80
81 -- Pick Wave Move Order
82 IF mo_line.mo_type = 3 THEN
83 IF mo_line.line_status IN (5, 6) THEN
84 /* Delete MTRL if inventory interface has completed successfully and
85 order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y')
86 or is cancelled (CANCELLED_FLAG = 'Y')*/
87 wdd_exists := 'Y'; -- Added for bug # 6469970
88 oel_exists := 'Y'; -- Added for bug # 6469970
89 BEGIN
90 SELECT 1
91 INTO can_delete
92 FROM wsh_delivery_details wsd
93 WHERE wsd.move_order_line_id = mo_line.line_id
94 AND wsd.inv_interfaced_flag = 'Y'
95 AND ROWNUM < 2;
96
97 IF (can_delete = 1) THEN
98 SELECT 1
99 INTO can_delete
100 FROM oe_order_lines_all oola
101 WHERE oola.line_id = mo_line.txn_source_line_id
102 AND (oola.open_flag <> 'Y'
103 OR oola.cancelled_flag = 'Y'
104 )
105 AND ROWNUM < 2;
106 END IF;
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 can_delete := 0;
110 END;
111
112 --Bug #4864356
113 --Delete the move order lines where the order line has been cancelled
114 IF (can_delete = 0) THEN
115 BEGIN
116 SELECT 1
117 INTO can_delete
118 FROM oe_order_lines_all oola
119 WHERE oola.line_id = mo_line.txn_source_line_id
120 AND ( (NVL(open_flag, 'Y') = 'N') OR cancelled_flag = 'Y')
121 AND NOT EXISTS (
122 SELECT 1
123 FROM wsh_delivery_details wdd
124 WHERE wdd.source_line_id = oola.line_id
125 AND wdd.inv_interfaced_flag IN ('N', 'P')
126 AND wdd.released_status <> 'D')
127 AND ROWNUM =1;
128 EXCEPTION
129 WHEN OTHERS THEN
130 can_delete := 0;
131 END;
132 END IF; --END IF delete canceled SO lines
133
134 --Bug #4864356
135 --Delete the move order lines where the corresponding delivery detail
136 --has been completely backordered
137 --Bug #7249224, Added the outer paranthesis in the below IF clause.
138 IF (can_delete = 0 AND mo_line.line_status = 5 AND
139 ((mo_line.quantity = mo_line.quantity_delivered
140 AND
141 mo_line.quantity_detailed = mo_line.quantity_delivered)
142 OR
143 (mo_line.quantity > 0 AND mo_line.quantity_delivered = 0))) THEN
144 BEGIN
145 SELECT 1
146 INTO can_delete
147 FROM oe_order_lines_all oola
148 WHERE oola.line_id = mo_line.txn_source_line_id
149 AND (NVL(open_flag,'Y') ='Y' and cancelled_flag='N')
150 AND NOT EXISTS (
151 SELECT 1
152 FROM mtl_material_transactions_temp mmtt
153 WHERE mmtt.move_order_line_id = mo_line.line_id)
154 AND NOT EXISTS (
155 SELECT 1
156 FROM wsh_delivery_details wdd
157 WHERE wdd.source_line_id = oola.line_id
158 AND wdd.source_code = 'OE'
159 AND wdd.move_order_line_id = mo_line.line_id)
160 AND ROWNUM = 1;
161 EXCEPTION
162 WHEN OTHERS THEN
163 can_delete := 0;
164 END;
165 END IF; --END IF delete completely backordered lines
166
167 -- Start of Bug #6469970
168 IF (can_delete = 0) THEN
169 BEGIN
170 SELECT distinct wdd.inv_interfaced_flag into inv_int_flag
171 FROM wsh_delivery_details wdd
172 WHERE wdd.move_order_line_id = mo_line.line_id;
173
174 IF inv_int_flag = 'Y' THEN
175 can_delete := 1;
176 ELSE
177 can_delete := 0;
178 END IF;
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 can_delete := 1; -- No corresponding wdd record
182 wdd_exists := 'N';
183 WHEN TOO_MANY_ROWS THEN
184 can_delete := 0; -- At least one inv_int_flag is set to 'N' or 'P' in wdd
185 END;
186
187 IF (can_delete = 1 and wdd_exists = 'N') THEN
188 BEGIN
189 SELECT 0
190 INTO can_delete
191 FROM oe_order_lines_all oola
192 WHERE oola.line_id = mo_line.txn_source_line_id
193 AND (oola.open_flag = 'Y' AND NVL(oola.CANCELLED_FLAG,'N')='N');
194 -- If row found; can't delete
195 can_delete := 0;
196 EXCEPTION WHEN NO_DATA_FOUND THEN
197 can_delete := 1;
198 END;
199 END IF;
200 END IF;
201
202 -- The below If to check there is no corresponding order lines record.
203 -- Order information is purged before the move order purge program.
204 IF (can_delete = 0) THEN
205 BEGIN
206 SELECT 'Y'
207 INTO oel_exists
208 FROM oe_order_lines_all oola
209 WHERE oola.line_id = mo_line.txn_source_line_id
210 and rownum < 2;
211
212 IF oel_exists = 'Y' THEN
213 can_delete := 0;
214 ELSE
215 can_delete := 1;
216 END IF;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 can_delete := 1; -- there is no oel record
220 END;
221 END IF;
222 --End of changes for Bug #6469970
223
224 ELSE -- line status is 9
225 /* Delete MTRL if allocations doesn't exist
226 (MMTT should not exist for this line id, MMTT.move_order_line_id = MTRL.line_id)
227 and order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y') */
228 BEGIN
229 SELECT 1
230 INTO can_delete
231 FROM mtl_material_transactions_temp mmtt
232 WHERE mmtt.move_order_line_id = mo_line.line_id
233 AND ROWNUM < 2;
234
235 can_delete := 0;
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 BEGIN
239 SELECT 1
240 INTO can_delete
241 FROM oe_order_lines_all oola
242 WHERE oola.line_id = mo_line.txn_source_line_id
243 AND (oola.open_flag <> 'Y'
244 OR oola.cancelled_flag = 'Y'
245 )
246 AND ROWNUM < 2;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 can_delete := 0;
250 END;
251 END;
252 END IF;
253 END IF;
254
255 -- WIP Move Order
256 -- Bug 2666620: BackFlush MO Type Removed.
257 -- Since logic for both WIP Issue, WIP SubXfer is same, combining both
258 IF mo_line.mo_type = 5 THEN
259 IF mo_line.line_status IN (5, 6) THEN
260 --Delete MTRL for this line_id
261 can_delete := 1;
262 ELSE -- line status is 9
263 --Delete MTRL if allocations doesn't exist for this MO line
264 BEGIN
265 SELECT 1
266 INTO can_delete
267 FROM mtl_material_transactions_temp mmtt
268 WHERE mmtt.move_order_line_id = mo_line.line_id
269 AND ROWNUM < 2;
270
271 can_delete := 0;
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 can_delete := 1;
275 END;
276 END IF;
277 END IF;
278
279 IF (can_delete = 1) THEN
280 -- inv_trx_util_pub.TRACE('DELETED lines---mo_line.line_id= ' ||mo_line.line_id ,'INVMOPG',9);
281 DELETE FROM mtl_txn_request_lines
282 WHERE line_id = mo_line.line_id;
283
284 can_delete := 0;
285 l_count := l_count + 1;
286 END IF;
287
288 -- For deleting headers
289 IF (l_prev_header_id <> mo_line.header_id) THEN
290 DELETE FROM mtl_txn_request_headers
291 WHERE header_id = l_prev_header_id
292 AND NOT EXISTS( SELECT 1
293 FROM mtl_txn_request_lines
294 WHERE header_id = l_prev_header_id);
295
296 IF (SQL%FOUND) THEN
297 l_count := l_count + 1;
298 can_delete := 0;
299 --inv_trx_util_pub.TRACE('DELETED Headers mo_line.header_id= ' ||l_prev_header_id ,'INVMOPG',9);
300 END IF;
301 END IF;
302
303 IF (l_count >= p_mol_pc) THEN
304 IF (MOD(l_count, p_mol_pc) = 0) THEN
305 COMMIT;
306 END IF;
307 END IF;
308
309 l_prev_header_id := mo_line.header_id;
310 END LOOP;
311
312 DELETE FROM mtl_txn_request_headers
313 WHERE header_id = mo_line.header_id
314 AND NOT EXISTS( SELECT 1
315 FROM mtl_txn_request_lines
316 WHERE header_id = mo_line.header_id);
317
318 IF (SQL%FOUND) THEN
319 l_count := l_count + 1;
320 can_delete := 0;
321 --inv_trx_util_pub.TRACE('DELETED Headers mo_line.header_id= ' || mo_line.header_id ,'INVMOPG',9);
322 END IF;
323
324 IF (l_debug = 1) THEN
325 inv_trx_util_pub.TRACE(l_count || 'Rows Purged ', 'INVMOPG', 9);
326 END IF;
327
328 INSERT INTO mtl_purge_header
329 (
330 purge_id
331 , last_update_date
332 , last_updated_by
333 , last_update_login
334 , creation_date
335 , created_by
336 , purge_date
337 , move_order_type
338 , archive_flag
339 , purge_name
340 , organization_id
341 , creation_date_from
342 , creation_date_to
343 , lines_purged
344 )
345 VALUES (
346 mtl_material_transactions_s.NEXTVAL
347 , SYSDATE
348 , fnd_global.user_id
349 , fnd_global.user_id
350 , SYSDATE
351 , fnd_global.user_id
352 , SYSDATE
353 , p_mo_type_id
354 , NULL
355 , p_purge_name
356 , p_organization_id
357 , l_date_from
358 , l_date_to
359 , l_count
360 );
361
362 COMMIT;
363 --return sucess
364 l_ret_msg := fnd_concurrent.set_completion_status('NORMAL', 'NORMAL');
365 x_retcode := retcode_success;
366 x_errbuf := NULL;
367 EXCEPTION
368 WHEN fnd_api.g_exc_error THEN
369 error := SQLERRM;
370
371 IF (l_debug = 1) THEN
372 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
373 END IF;
374
375 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
376 x_retcode := retcode_error;
377 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
378 WHEN fnd_api.g_exc_unexpected_error THEN
379 error := SQLERRM;
380
381 IF (l_debug = 1) THEN
382 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
383 END IF;
384
385 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
386 x_retcode := retcode_error;
387 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
388 WHEN OTHERS THEN
389 error := SQLERRM;
390
391 IF (l_debug = 1) THEN
392 inv_trx_util_pub.TRACE('The Error Is '|| error, 'INVMOPG', 9);
393 END IF;
394
395 l_ret_msg := fnd_concurrent.set_completion_status('ERROR', 'ERROR');
396 x_retcode := retcode_error;
397 x_errbuf := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
398
399 IF mo_lines%ISOPEN THEN
400 CLOSE mo_lines;
401 --return failure
402 END IF;
403 END purge_lines;
404 END inv_mo_purge;
[Home] [Help]