[Home] [Help]
PACKAGE BODY: APPS.WMS_PURGE_PVT
Source
1 PACKAGE BODY WMS_PURGE_PVT AS
2 /* $Header: WMSPURGB.pls 120.9 2006/08/10 11:27:09 bradha ship $*/
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_PURGE_PVT';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSPURGB.pls 120.9 2006/08/10 11:27:09 bradha ship $';
7
8
9 DEVICE CONSTANT NUMBER :=1;
10 LPN CONSTANT NUMBER :=2;
11 TASK CONSTANT NUMBER :=3;
12 LABEL CONSTANT NUMBER :=4;
13 EPC CONSTANT NUMBER :=5;
14
15 PROCEDURE Check_Purge_LPNs (
16 p_api_version IN NUMBER
17 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
18 , p_commit IN VARCHAR2 := fnd_api.g_false
19 , x_return_status OUT NOCOPY VARCHAR2
20 , x_msg_count OUT NOCOPY NUMBER
21 , x_msg_data OUT NOCOPY VARCHAR2
22 , p_caller IN VARCHAR2
23 , p_lock_flag IN VARCHAR2
24 , p_lpn_id_table IN OUT NOCOPY WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType
25 ) IS
26 l_api_name CONSTANT VARCHAR2(30) := 'Check_Purge_LPNs';
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 l_progress VARCHAR2(500) := 'Entered API';
30
31 l_valid_lpns WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
32
33 BEGIN
34 IF (l_debug = 1) THEN
35 inv_trx_util_pub.trace(l_api_name || ' Entered ' || g_pkg_version, l_api_name, 4);
36 inv_trx_util_pub.trace('ver='||p_api_version||' initmsg='||p_init_msg_list||' commit='||p_commit||' caller='||p_caller||' tabfst='||p_lpn_id_table.first||' tablst='||p_lpn_id_table.last, l_api_name, 4);
37 END IF;
38
39 -- Standard Start of API savepoint
40 SAVEPOINT Check_Purge_LPN_PVT;
41
42 -- Standard call to check for call compatibility.
43 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
44 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
45 fnd_msg_pub.ADD;
46 RAISE fnd_api.g_exc_unexpected_error;
47 END IF;
48
49 -- Initialize message list if p_init_msg_list is set to TRUE.
50 IF fnd_api.to_boolean(p_init_msg_list) THEN
51 fnd_msg_pub.initialize;
52 END IF;
53
54 -- Initialize API return status to success
55 x_return_status := fnd_api.g_ret_sts_success;
56
57 l_progress := 'Bulk insert LPNs into temp table for validation';
58 FORALL i IN p_lpn_id_table.first .. p_lpn_id_table.last
59 INSERT INTO WMS_TXN_CONTEXT_TEMP ( line_id, txn_source_name )
60 VALUES ( p_lpn_id_table(i), 'WMS_LPN_PURGE' );
61
62 l_progress := 'Filter out LPNs not valid for purge';
63 SELECT lpn_id BULK COLLECT
64 INTO l_valid_lpns
65 FROM WMS_LICENSE_PLATE_NUMBERS wlpn, WMS_TXN_CONTEXT_TEMP wtct
66 WHERE wtct.txn_source_name = 'WMS_LPN_PURGE'
67 AND wlpn.lpn_id = wtct.line_id
68 AND wlpn.lpn_context IN (4, 5)
69 AND NOT EXISTS ( SELECT 1 FROM MTL_TRANSACTIONS_INTERFACE
70 WHERE lpn_id = wlpn.lpn_id
71 OR content_lpn_id = wlpn.lpn_id
72 OR transfer_lpn_id = wlpn.lpn_id )
73 AND NOT EXISTS ( SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
74 WHERE lpn_id = wlpn.lpn_id
75 OR content_lpn_id = wlpn.lpn_id
76 OR transfer_lpn_id = wlpn.lpn_id )
77 AND NOT EXISTS ( SELECT 1 FROM MTL_CYCLE_COUNT_ENTRIES
78 WHERE wlpn.lpn_context = 5
79 AND parent_lpn_id = wlpn.lpn_id
80 AND entry_status_code = 2 )
81 AND NOT EXISTS ( SELECT 1 FROM MTL_ONHAND_QUANTITIES_DETAIL
82 WHERE lpn_id = wlpn.lpn_id )
83 AND NOT EXISTS ( SELECT 1 FROM WMS_LICENSE_PLATE_NUMBERS
84 WHERE outermost_lpn_id = wlpn.outermost_lpn_id
85 AND lpn_context <> wlpn.lpn_context)
86 FOR UPDATE;
87
88 l_progress := 'Remove records from wms_txn_context_temp';
89 DELETE FROM WMS_TXN_CONTEXT_TEMP
90 WHERE txn_source_name = 'WMS_LPN_PURGE';
91
92 --bug 5150284 if any LPNs did not pass validation, add message to stack
93 IF ( p_lpn_id_table.count > l_valid_lpns.count ) THEN
94 fnd_message.set_name('WMS', 'WMS_LPN_INELIGIBLE_FOR_PURGE');
95 fnd_msg_pub.ADD;
96 END IF;
97
98 l_progress := 'Replace LPNS in rec with only valid lpn_ids';
99 p_lpn_id_table := l_valid_lpns;
100
101 IF (l_debug = 1) THEN
102 inv_trx_util_pub.trace(l_api_name || ' Exit tblcnt='||p_lpn_id_table.last, l_api_name, 4);
103 END IF;
104
105 IF ( p_lock_flag <> 'Y' ) THEN
106 -- Undo select for update
107 ROLLBACK TO Check_Purge_LPN_PVT;
108 END IF;
109
110 -- Standard call to get message count and data
111 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
112 EXCEPTION
113 WHEN OTHERS THEN
114 x_return_status := fnd_api.g_ret_sts_unexp_error;
115 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
116 IF (l_debug = 1) THEN
117 inv_trx_util_pub.trace(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), l_api_name, 1);
118 END IF;
119 ROLLBACK TO Check_Purge_LPN_PVT;
120 END Check_Purge_LPNs;
121
122 PROCEDURE Purge_LPNs (
123 p_api_version IN NUMBER
124 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
125 , p_commit IN VARCHAR2 := fnd_api.g_false
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_msg_count OUT NOCOPY NUMBER
128 , x_msg_data OUT NOCOPY VARCHAR2
129 , p_caller IN VARCHAR2
130 , p_lpn_id_table IN WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType
131 , p_purge_count IN OUT NOCOPY WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType
132 ) IS
133 l_api_name CONSTANT VARCHAR2(30) := 'Purge_LPNs';
134 l_api_version CONSTANT NUMBER := 1.0;
135 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
136 l_progress VARCHAR2(500) := 'Entered API';
137
138 BEGIN
139 IF (l_debug = 1) THEN
140 inv_trx_util_pub.trace(l_api_name || ' Entered ' || g_pkg_version, l_api_name, 4);
141 inv_trx_util_pub.trace('ver='||p_api_version||' initmsg='||p_init_msg_list||' commit='||p_commit||' caller='||p_caller||' tabfst='||p_lpn_id_table.first||' tablst='||p_lpn_id_table.last, l_api_name, 4);
142 END IF;
143
144 -- Standard Start of API savepoint
145 SAVEPOINT PURGE_LPNS_PVT;
146
147 -- Standard call to check for call compatibility.
148 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
149 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
150 fnd_msg_pub.ADD;
151 RAISE fnd_api.g_exc_unexpected_error;
152 END IF;
153
154 -- Initialize message list if p_init_msg_list is set to TRUE.
155 IF fnd_api.to_boolean(p_init_msg_list) THEN
156 fnd_msg_pub.initialize;
157 END IF;
158
159 -- Initialize API return status to success
160 x_return_status := fnd_api.g_ret_sts_success;
161
162 l_progress := 'Delete from packaging history of all content packages';
163 FORALL i IN p_lpn_id_table.first..p_lpn_id_table.last
164 DELETE FROM WMS_PACKAGING_HIST
165 WHERE rowid in ( SELECT rowid FROM WMS_PACKAGING_HIST
166 START WITH parent_lpn_id = p_lpn_id_table(i)
167 CONNECT BY parent_package_id = PRIOR package_id );
168
169 IF ( p_purge_count.exists(1) ) THEN
170 p_purge_count(1) := NVL(p_purge_count(1), 0) + SQL%ROWCOUNT;
171 ELSE
172 p_purge_count(1) := SQL%ROWCOUNT;
173 END IF;
174
175 l_progress := 'Delete all history records for that LPN';
176 FORALL i IN p_lpn_id_table.first..p_lpn_id_table.last
177 DELETE FROM WMS_LPN_HISTORIES
178 WHERE parent_lpn_id = p_lpn_id_table(i)
179 OR lpn_id = p_lpn_id_table(i);
180
181 IF ( p_purge_count.exists(2) ) THEN
182 p_purge_count(2) := NVL(p_purge_count(2), 0) + SQL%ROWCOUNT;
183 ELSE
184 p_purge_count(2) := SQL%ROWCOUNT;
185 END IF;
186
187 l_progress := 'Delete all contents for that LPN';
188 FORALL i IN p_lpn_id_table.first..p_lpn_id_table.last
189 DELETE FROM WMS_LPN_CONTENTS
190 WHERE parent_lpn_id = p_lpn_id_table(i);
191
192 IF ( p_purge_count.exists(3) ) THEN
193 p_purge_count(3) := NVL(p_purge_count(3), 0) + SQL%ROWCOUNT;
194 ELSE
195 p_purge_count(3) := SQL%ROWCOUNT;
196 END IF;
197
198 l_progress := 'Delete the LPN itself';
199 FORALL i IN p_lpn_id_table.first..p_lpn_id_table.last
200 DELETE FROM WMS_LICENSE_PLATE_NUMBERS
201 WHERE lpn_id = p_lpn_id_table(i);
202
203 IF ( p_purge_count.exists(4) ) THEN
204 p_purge_count(4) := NVL(p_purge_count(4), 0) + SQL%ROWCOUNT;
205 ELSE
206 p_purge_count(4) := SQL%ROWCOUNT;
207 END IF;
208
209 l_progress := 'Delete the LPN-EPC cross reference';
210 FORALL i IN p_lpn_id_table.first..p_lpn_id_table.last
211 DELETE FROM WMS_EPC
212 WHERE lpn_id = p_lpn_id_table(i)
213 AND cross_ref_type = 1;
214
215 IF ( p_purge_count.exists(5) ) THEN
216 p_purge_count(5) := NVL(p_purge_count(5), 0) + SQL%ROWCOUNT;
217 ELSE
218 p_purge_count(5) := SQL%ROWCOUNT;
219 END IF;
220
221
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 x_return_status := fnd_api.g_ret_sts_unexp_error;
226 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
227 IF (l_debug = 1) THEN
228 inv_trx_util_pub.trace(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), l_api_name, 1);
229 END IF;
230 ROLLBACK TO PURGE_LPNS_PVT;
231 END Purge_LPNs;
232
233 PROCEDURE Purge_WMS (
234 x_errbuf OUT NOCOPY VARCHAR2
235 , x_retcode OUT NOCOPY NUMBER
236 , p_purge_date IN VARCHAR2
237 , p_orgid IN NUMBER
238 , p_purge_name IN VARCHAR2
239 , p_purge_age IN NUMBER
240 , p_purge_type IN NUMBER
241 ) IS
242 l_api_name CONSTANT VARCHAR2(30) := 'Purge_WMS';
243 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
244 l_ret BOOLEAN;
245 l_date DATE;
246
247 p_level CONSTANT NUMBER := 9;
248 p_module CONSTANT VARCHAR2(25) := 'WMS_PURGE_PVT.PURGE_WMS';
249 p_message VARCHAR2(2000);
250
251 l_return_status VARCHAR2(50);
252 x_msg_count NUMBER;
253 x_msg_data VARCHAR2(2000);
254
255 l_max_batch_size CONSTANT NUMBER := 1000;
256 l_purge_count WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
257 l_tmp_purge_count WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
258 l_lpn_tbl WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
259 l_wsh_lpn_rec WSH_GLBL_VAR_STRCT_GRP.purgeInOutRecType;
260
261 CURSOR LPNS_FOR_PURGE IS
262 SELECT lpn_id
263 FROM WMS_LICENSE_PLATE_NUMBERS wlpn
264 WHERE organization_id = p_orgid
265 AND lpn_context IN (4, 5)
266 AND last_update_date < l_date;
267
268 BEGIN
269 -- Org Id AND (purge date OR purge age) are required for purging.
270 IF ((p_purge_date IS NOT NULL OR p_purge_age is not null) AND p_orgid IS NOT NULL) THEN
271 IF ( p_purge_age IS NOT NULL AND p_purge_date IS NULL ) THEN
272 l_date := sysdate -p_purge_age;
273 ELSIF ( p_purge_age IS NULL AND p_purge_date IS NOT NULL ) THEN
274 l_date := FND_DATE.canonical_to_date(p_purge_date);
275 ELSIF ( p_purge_age IS NOT NULL AND p_purge_date IS NOT NULL ) THEN
276 l_date := least(FND_DATE.canonical_to_date(p_purge_date),trunc(sysdate)-p_purge_age);
277 END IF;
278
279 FND_MSG_PUB.DELETE_msg;
280 IF ( p_purge_type = DEVICE OR p_purge_type IS NULL ) THEN
281
282 delete from wms_device_requests_hist
283 where creation_date < l_date and organization_id = p_orgid;
284 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
285 FND_MESSAGE.SET_TOKEN('ROWS',SQL%ROWCOUNT);
286 FND_MESSAGE.SET_TOKEN('TABLE','WMS_DEVICE_REQUESTS_HIST');
287 FND_MSG_PUB.ADD;
288 END IF; --Added bug#4415994
289 IF ( p_purge_type = LPN OR p_purge_type IS NULL ) THEN
290 -- initialize table delete count variables
291 l_purge_count(1) := 0; -- WMS_PACKAGING_HIST
292 l_purge_count(2) := 0; -- WMS_LPN_HISTORIES
293 l_purge_count(3) := 0; -- WMS_LPN_CONTENTS
294 l_purge_count(4) := 0; -- WMS_LICENSE_PLATE_NUMBERS
295 l_purge_count(5) := 0; -- WMS_EPC
296
297 OPEN LPNS_FOR_PURGE;
298
299 -- Since we limit the number of LPNs to be purged at once, need to loop
300 -- until the cursor no longer retrieves any records
301 LOOP
302 l_lpn_tbl.delete;
303 FETCH LPNS_FOR_PURGE BULK COLLECT
304 INTO l_lpn_tbl
305 LIMIT l_max_batch_size;
306
307 IF ( l_debug = 1 ) THEN
308 inv_trx_util_pub.trace('Fecthed LPNs based on date and context first='||l_lpn_tbl.first||' last='||l_lpn_tbl.last, l_api_name, 4);
309 END IF;
310
311 -- If no more LPNs to purge, exit loop, otherwise if there were LPNs deleted
312 -- in the previous iteration, commit those
313 IF ( NOT l_lpn_tbl.exists(1) ) THEN
314 EXIT;
315 ELSIF ( l_purge_count(4) > 0 ) THEN
316 COMMIT;
317 END IF;
318
319 Check_Purge_LPNs (
320 p_api_version => 1.0
321 , p_init_msg_list => fnd_api.g_false
322 , p_commit => fnd_api.g_false
323 , x_return_status => l_return_status
324 , x_msg_count => x_msg_count
325 , x_msg_data => x_msg_data
326 , p_caller => 'Purge_WMS'
327 , p_lock_flag => 'Y'
328 , p_lpn_id_table => l_lpn_tbl );
329
330 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
331 FND_MESSAGE.SET_NAME('INV','INV_API_PURGE_ERROR');
332 FND_MESSAGE.SET_TOKEN('API', 'Check_Purge_LPNs');
333 FND_MSG_PUB.ADD;
334 EXIT;
335 ELSIF ( l_debug = 1 ) THEN
336 inv_trx_util_pub.trace('WMS validation done first='||l_lpn_tbl.first||' last='||l_lpn_tbl.last, l_api_name, 4);
337 END IF;
338
339 IF ( l_lpn_tbl.exists(1) AND NVL(l_lpn_tbl.last, 0) > 0 ) THEN
340 -- Call shipping to check if ther LPNs are elegible for purge
341 l_wsh_lpn_rec.lpn_ids.delete;
342
343 FOR i IN l_lpn_tbl.first .. l_lpn_tbl.last LOOP
344 l_wsh_lpn_rec.lpn_ids(i) := l_lpn_tbl(i);
345 END LOOP;
346
347 WSH_WMS_LPN_GRP.Check_purge (
348 p_api_version_number => 1.0
349 , p_init_msg_list => fnd_api.g_false
350 , p_commit => fnd_api.g_false
351 , x_return_status => l_return_status
352 , x_msg_count => x_msg_count
353 , x_msg_data => x_msg_data
354 , p_lpn_rec => l_wsh_lpn_rec );
355
356 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
357 FND_MESSAGE.SET_NAME('INV','INV_API_PURGE_ERROR');
358 FND_MESSAGE.SET_TOKEN('API', 'WSH_WMS_LPN_GRP.Check_purge');
359 FND_MSG_PUB.ADD;
360 EXIT;
361 ELSIF ( l_debug = 1 ) THEN
362 inv_trx_util_pub.trace('WSH validation done first='||l_wsh_lpn_rec.lpn_ids.first||' last='||l_wsh_lpn_rec.lpn_ids.last, l_api_name, 4);
363 END IF;
364
365 -- Put WSH approved lpn id list back in to lpn_rec type
366 l_lpn_tbl.delete;
367
368 IF ( l_wsh_lpn_rec.lpn_ids.exists(1) AND NVL(l_wsh_lpn_rec.lpn_ids.last, 0) > 0 ) THEN
369 FOR i IN l_wsh_lpn_rec.lpn_ids.first .. l_wsh_lpn_rec.lpn_ids.last LOOP
370 l_lpn_tbl(i) := l_wsh_lpn_rec.lpn_ids(i);
371 END LOOP;
372 END IF;
373 END IF;
374
375 IF ( l_lpn_tbl.exists(1) AND NVL(l_lpn_tbl.last, 0) > 0 ) THEN
376 --Store the purge count in case or partial purge failure during delete
377 l_tmp_purge_count := l_purge_count;
378
379 Purge_LPNs (
380 p_api_version => 1.0
381 , p_init_msg_list => fnd_api.g_false
382 , p_commit => fnd_api.g_false
383 , x_return_status => l_return_status
384 , x_msg_count => x_msg_count
385 , x_msg_data => x_msg_data
386 , p_caller => 'Purge_WMS'
387 , p_lpn_id_table => l_lpn_tbl
388 , p_purge_count => l_purge_count );
389
390 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
391 l_purge_count := l_tmp_purge_count;
392 FND_MESSAGE.SET_NAME('INV','INV_API_PURGE_ERROR');
393 FND_MESSAGE.SET_TOKEN('API', 'Purge_LPNs');
394 FND_MSG_PUB.ADD;
395 EXIT;
396 END IF;
397 END IF;
398 END LOOP;
399
400 CLOSE LPNS_FOR_PURGE;
401
402 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
403 FND_MESSAGE.SET_TOKEN('ROWS', l_purge_count(1));
404 FND_MESSAGE.SET_TOKEN('TABLE','WMS_PACKAGING_HIST');
405 FND_MSG_PUB.ADD;
406
407 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
408 FND_MESSAGE.SET_TOKEN('ROWS', l_purge_count(2));
409 FND_MESSAGE.SET_TOKEN('TABLE','WMS_LPN_HISTORIES');
410 FND_MSG_PUB.ADD;
411
412 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
413 FND_MESSAGE.SET_TOKEN('ROWS', l_purge_count(3));
414 FND_MESSAGE.SET_TOKEN('TABLE','WMS_LPN_CONTENTS');
415 FND_MSG_PUB.ADD;
416
417 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
418 FND_MESSAGE.SET_TOKEN('ROWS', l_purge_count(4));
419 FND_MESSAGE.SET_TOKEN('TABLE','WMS_LICENSE_PLATE_NUMBERS');
420 FND_MSG_PUB.ADD;
421
422 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
423 FND_MESSAGE.SET_TOKEN('ROWS', l_purge_count(5));
424 FND_MESSAGE.SET_TOKEN('TABLE','WMS_EPC');
425 FND_MSG_PUB.ADD;
426
427 END IF; --Added bug#4415994
428 IF ( p_purge_type = TASK OR p_purge_type IS NULL ) THEN
429
430 delete from wms_dispatched_tasks_history
431 where last_update_date < l_date and organization_id = p_orgid ;
432 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
433 FND_MESSAGE.SET_TOKEN('ROWS',SQL%ROWCOUNT);
434 FND_MESSAGE.SET_TOKEN('TABLE','WMS_DISPATCHED_TASKS_HISTORY');
435 FND_MSG_PUB.ADD;
436
437 delete from wms_exceptions
438 where creation_date < l_date and organization_id = p_orgid ;
439 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
440 FND_MESSAGE.SET_TOKEN('ROWS',SQL%ROWCOUNT);
441 FND_MESSAGE.SET_TOKEN('TABLE','WMS_EXCEPTIONS');
442 FND_MSG_PUB.ADD;
443 END IF; --Added bug#4415994
444 IF ( p_purge_type = LABEL OR p_purge_type IS NULL ) THEN
445
446 delete WMS_LABEL_REQUESTS_HIST
447 where creation_date < l_date and organization_id = p_orgid;
448 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
449 FND_MESSAGE.SET_TOKEN('ROWS',SQL%ROWCOUNT);
450 FND_MESSAGE.SET_TOKEN('TABLE','WMS_LABEL_REQUESTS_HIST');
451 FND_MSG_PUB.ADD;
452
453 END IF;
454
455 IF ( p_purge_type = EPC OR p_purge_type IS NULL ) THEN
456
457 delete wms_epc we
458 where creation_date < sysdate --No organization is here
459 AND EXISTS ( SELECT 1 FROM wms_license_plate_numbers wlpn
460 WHERE wlpn.lpn_id = we.lpn_id
461 AND we.cross_ref_type = 1 --LPN-EPC
462 AND wlpn.lpn_context = 4 )
463 OR EXISTS ( SELECT 1 FROM mtl_serial_numbers msn
464 WHERE msn.inventory_item_id = we.inventory_item_id
465 AND msn.serial_number = we.serial_number
466 AND we.cross_ref_type = 2 --Serial-EPC
467 AND msn.current_status = 4 ); --Issue out of store
468
469 FND_MESSAGE.SET_NAME('INV','INV_TOTAL_ROWS');
470 FND_MESSAGE.SET_TOKEN('ROWS',SQL%ROWCOUNT);
471 FND_MESSAGE.SET_TOKEN('TABLE','WMS_EPC');
472 FND_MSG_PUB.ADD;
473
474 END IF;
475
476
477 INSERT INTO mtl_purge_header (
478 purge_id
479 , last_update_date
480 , last_updated_by
481 , last_update_login
482 , creation_date
483 , created_by
484 , purge_date
485 , archive_flag
486 , purge_name
487 , organization_id )
488 VALUES (
489 mtl_material_transactions_s.NEXTVAL
490 , SYSDATE
491 , FND_GLOBAL.user_id
492 , fnd_global.user_id
493 , SYSDATE
494 , FND_GLOBAL.user_id
495 , l_date
496 , NULL
497 , p_purge_name
498 , p_orgid );
499
500 FND_MSG_PUB.COUNT_AND_GET (
501 p_count => x_msg_count
502 , p_data => x_msg_data );
503
504 FOR i in 1..x_msg_count LOOP
505 p_message := fnd_msg_pub.get(i,'F');
506 IF (l_debug = 1) THEN
507 INV_TRX_UTIL_PUB.TRACE (
508 p_mesg => p_message
509 , p_mod => p_module
510 , p_level => p_level );
511 END IF;
512 END LOOP;
513
514 fnd_msg_pub.delete_msg;
515
516 COMMIT;
517
518 l_ret := fnd_concurrent.set_completion_status('NORMAL', 'WMS_PURGE_SUCCESS');
519 x_retcode := 0;
520 ELSE
521 l_ret := fnd_concurrent.set_completion_status('ERROR', 'WMS_MISS_REQ_PARAMETER');
522 x_retcode := 2;
523 x_errbuf := 'ERROR';
524 END IF;
525 END Purge_WMS;
526
527 END WMS_PURGE_PVT;