[Home] [Help]
PACKAGE BODY: APPS.WMS_OPP_CYC_COUNT
Source
1 PACKAGE BODY WMS_OPP_CYC_COUNT AS
2 /* $Header: WMSOPCCB.pls 120.1.12010000.13 2010/05/03 14:03:14 abasheer noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_OPP_CYC_COUNT';
5 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSOPCCB.pls 120.1.12010000.13 2010/05/03 14:03:14 abasheer noship $';
6
7 -- Various debug levels
8 g_error CONSTANT NUMBER := 1;
9 g_info CONSTANT NUMBER := 5;
10 g_message CONSTANT NUMBER := 9;
11
12 g_org_level CONSTANT NUMBER := 1;
13 g_sub_level CONSTANT NUMBER := 2;
14
15 g_cycle_count_header_id NUMBER;
16
17
18 PROCEDURE mdebug
19 (msg IN VARCHAR2,
20 LEVEL NUMBER := g_message)
21 IS
22 BEGIN
23 -- DBMS_OUTPUT.put_line(msg);
24 inv_trx_util_pub.Trace(msg,g_pkg_name,LEVEL);
25 END mdebug;
26
27 /*
28
29 This function will return the total primary qty of an item
30 for the SKU passed after discarding the loaded qty.
31
32 */
33 FUNCTION Get_total_item_qty
34 (p_organization_id IN NUMBER,
35 p_subinventory_code IN VARCHAR2,
36 p_loc_id IN NUMBER,
37 p_inventory_item_id IN NUMBER)
38 RETURN NUMBER
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Get_total_item_qty';
41 l_api_version CONSTANT NUMBER := 1.0;
45 l_loaded_sys_qty NUMBER;
42 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'),0);
43
44 l_tot_qty NUMBER;
46 l_serial_number_control_code NUMBER;
47
48
49 BEGIN
50
51 IF ( l_debug = 1 ) THEN
52 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
53 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
54 Mdebug ( l_api_name||' : p_subinventory_code = ' || p_subinventory_code , g_message);
55 Mdebug ( l_api_name||' : p_loc_id = ' || p_loc_id , g_message);
56 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
57 END IF;
58
59
60 l_serial_number_control_code := inv_cache.item_rec.serial_number_control_code;
61
62 IF ( l_serial_number_control_code IN ( 1, 6 ) ) THEN
63 IF ( l_debug = 1 ) THEN
64 Mdebug ( l_api_name||' : Non serial controlled item' , g_message);
65 END IF;
66
67 BEGIN
68
69 SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
70 INTO l_tot_qty
71 FROM MTL_ONHAND_QUANTITIES_DETAIL
72 WHERE inventory_item_id = p_inventory_item_id
73 AND organization_id = p_organization_id
74 AND subinventory_code = p_subinventory_code
75 AND locator_id = p_loc_id;
76
77 EXCEPTION
78 WHEN no_data_found THEN
79 IF ( l_debug = 1 ) THEN
80 Mdebug ( l_api_name||' : No data found exception.. So l_tot_qty = 0 ' , g_message);
81 END IF;
82 l_tot_qty := 0;
83 END;
84
85 IF ( l_debug = 1 ) THEN
86 Mdebug ( l_api_name||' : MOQD qty is ' || l_tot_qty , g_message);
87 END IF;
88
89 BEGIN
90
91 SELECT NVL ( SUM ( quantity ), 0 )
92 INTO l_loaded_sys_qty
93 FROM WMS_LOADED_QUANTITIES_V
94 WHERE inventory_item_id = p_inventory_item_id
95 AND organization_id = p_organization_id
96 AND subinventory_code = p_subinventory_code
97 AND locator_id = p_loc_id
98 AND qty_type = 'LOADED';
99
100 EXCEPTION
101 WHEN no_data_found THEN
102 IF ( l_debug = 1 ) THEN
103 Mdebug ( l_api_name||' : No data found exception.. So l_loaded_sys_qty = 0 ' , g_message);
104 END IF;
105 l_loaded_sys_qty := 0;
106 END;
107
108
109 IF ( l_debug = 1 ) THEN
110 Mdebug ( l_api_name||' : Loaded qty is ' || l_loaded_sys_qty , g_message);
111 END IF;
112
113 ELSIF ( l_serial_number_control_code IN ( 2, 5 )) THEN
114 IF ( l_debug = 1 ) THEN
115 Mdebug ( l_api_name||' : Serial controlled item' , g_message);
116 END IF;
117
118 BEGIN
119
120 SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
121 INTO l_tot_qty
122 FROM mtl_serial_numbers
123 WHERE inventory_item_id = p_inventory_item_id
124 AND current_organization_id = p_organization_id
125 AND current_subinventory_code = p_subinventory_code
126 AND current_locator_id = p_loc_id;
127
128 EXCEPTION
129 WHEN no_data_found THEN
130 IF ( l_debug = 1 ) THEN
131 Mdebug ( l_api_name||' : No data found exception.. So l_tot_qty = 0 ' , g_message);
132 END IF;
133 l_tot_qty := 0;
134 END;
135
136 IF ( l_debug = 1 ) THEN
137 Mdebug ( l_api_name||' : MOQD qty is ' || l_tot_qty , g_message);
138 END IF;
139
140 BEGIN
141
142 SELECT Count(DISTINCT msn.serial_number)
143 INTO l_loaded_sys_qty
144 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
145 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
146 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id and
147 mtlt.lot_number is null) or
148 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
149 and mtlt.lot_number is not null))
150 AND mmtt.inventory_item_id = p_inventory_item_id
151 AND mmtt.organization_id = p_organization_id
152 AND mmtt.subinventory_code = p_subinventory_code
153 AND mmtt.locator_id = p_loc_id
154 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
155 AND msn.inventory_item_id = mmtt.inventory_item_id
156 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
157 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
158 AND wdt.task_type <> 2
159 AND wdt.status = 4;
160
161 EXCEPTION
162 WHEN no_data_found THEN
163 IF ( l_debug = 1 ) THEN
164 Mdebug ( l_api_name||' : No data found exception.. So l_loaded_sys_qty = 0 ' , g_message);
165 END IF;
166 l_loaded_sys_qty := 0;
167 END;
168
169 IF ( l_debug = 1 ) THEN
170 Mdebug ( l_api_name||' : Loaded qty is ' || l_loaded_sys_qty , g_message);
171 END IF;
172
173 END IF;
174
175 IF l_loaded_sys_qty > 0 THEN
176 l_tot_qty := l_tot_qty - l_loaded_sys_qty;
177 END IF;
181 Mdebug ( l_api_name||' : Total sys qty is ' || l_tot_qty , g_message);
178
179
180 IF ( l_debug = 1 ) THEN
182 END IF;
183
184 RETURN l_tot_qty;
185
186 END get_total_item_qty;
187
188
189 /*
190
191 This function will return the no of days since there was a cycle counting
192 performed for this item for the passed SKU.
193
194 */
195
196 FUNCTION Get_latest_cc_days
197 (p_organization_id IN NUMBER,
198 p_subinventory_code IN VARCHAR2,
199 p_loc_id IN NUMBER,
200 p_inventory_item_id IN NUMBER)
201 RETURN NUMBER
202 IS
203 l_api_name CONSTANT VARCHAR2(30) := 'Get_latest_cc_days';
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'),0);
206
207 l_no_of_days NUMBER;
208 l_opp_cyc_count_days NUMBER:=0;
209
210 BEGIN
211
212 IF ( l_debug = 1 ) THEN
213 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
214 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
215 Mdebug ( l_api_name||' : p_subinventory_code = ' || p_subinventory_code , g_message);
216 Mdebug ( l_api_name||' : p_loc_id = ' || p_loc_id , g_message);
217 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
218 END IF;
219
220 IF (inv_cache.set_fromsub_rec(p_organization_id,p_subinventory_code)) THEN
221 IF (inv_cache.fromsub_rec.enable_opp_cyc_count = 'Y') THEN
222 l_opp_cyc_count_days := inv_cache.fromsub_rec.opp_cyc_count_days;
223 END IF;
224 ELSE
225 IF (l_debug = 1) THEN
226 Mdebug(l_api_name||' : '||p_subinventory_code||' is an invalid subinv',g_error);
227 END IF;
228 fnd_message.Set_name('WMS','WMS_CONT_INVALID_SUB');
229 fnd_msg_pub.ADD;
230 RAISE fnd_api.g_exc_error;
231 END IF;
232
233 SELECT NVL((Trunc(SYSDATE) - Trunc(Max(count_date_current))),l_opp_cyc_count_days)
234 INTO l_no_of_days
235 FROM mtl_cycle_count_entries
236 WHERE subinventory = p_subinventory_code
237 AND inventory_item_id = p_inventory_item_id
238 AND organization_id = p_organization_id
239 AND locator_id = p_loc_id
240 AND ENTRY_STATUS_CODE NOT IN (1,3);
241
242 IF ( l_debug = 1 ) THEN
243 Mdebug ( l_api_name||' : l_no_of_days is ' || l_no_of_days , g_message);
244 END IF;
245
246 RETURN l_no_of_days;
247
248 EXCEPTION
249 WHEN no_data_found THEN
250 IF ( l_debug = 1 ) THEN
251 Mdebug ( l_api_name||' : No data found exception.. So returning l_opp_cyc_count_days = '||l_opp_cyc_count_days , g_message);
252 END IF;
253 RETURN l_opp_cyc_count_days;
254 END get_latest_cc_days;
255
256 /*
257
258 This function will return whether opportunistic cycle counting is required for
259 this item in the passed SKU.
260
261 This will return the default cycle count header id.
262
263 */
264 FUNCTION Is_cyc_count_enabled
265 (p_organization_id IN NUMBER,
266 p_subinventory_code IN VARCHAR2,
267 p_loc_id IN NUMBER,
268 p_inventory_item_id IN NUMBER)
269 RETURN NUMBER
270 IS
271 l_api_name CONSTANT VARCHAR2(30) := 'Is_cyc_count_enabled';
272 l_api_version CONSTANT NUMBER := 1.0;
273 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'),0);
274 l_progress VARCHAR2(500) := 'Entered API';
275
276 l_tot_qty NUMBER;
277 l_sub_tol_qty NUMBER;
278 l_no_of_days NUMBER;
279 l_cyc_count_header_id NUMBER:=-1;
280 l_item_exists VARCHAR2(1) := 'N'; -- Added for bug 9676695
281
282 BEGIN
283
284 IF ( l_debug = 1 ) THEN
285 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
286 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
287 Mdebug ( l_api_name||' : p_subinventory_code = ' || p_subinventory_code , g_message);
288 Mdebug ( l_api_name||' : p_loc_id = ' || p_loc_id , g_message);
289 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
290 END IF;
291
292 l_progress := 'Validate Item';
293
294 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
295
296 IF (l_debug = 1) THEN
297 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
298 END IF;
299
300 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
301 fnd_msg_pub.ADD;
302 RAISE fnd_api.g_exc_error;
303
304 END IF;
305
306 l_progress := 'After Validating Item';
307
308 -- Added for bug 9676695
309 IF (inv_cache.item_rec.cycle_count_enabled_flag<>'Y') THEN
310 RETURN l_cyc_count_header_id;
311 END IF;
312
313
314 l_progress := 'Validate Subinventory';
315
316 IF (NOT inv_cache.Set_fromsub_rec(p_subinventory_code => p_subinventory_code,
317 p_organization_id => p_organization_id)) THEN
318
319 IF (l_debug = 1) THEN
320 Mdebug(l_api_name||' : '||p_subinventory_code||' is an invalid subinv',g_error);
321 END IF;
322
323 fnd_message.Set_name('WMS','WMS_CONT_INVALID_SUB');
324 fnd_msg_pub.ADD;
325 RAISE fnd_api.g_exc_error;
326
327 END IF;
328
329 l_progress := 'After validating Subinventory';
330
331 IF (l_debug = 1) THEN
332 Mdebug(l_api_name||' : Opp cyc count @ Subinv level ',g_message);
333 END IF;
334
335 IF ( l_debug = 1 ) THEN
336 Mdebug ( l_api_name||' : inv_cache.fromsub_rec.enable_opp_cyc_count = ' || inv_cache.fromsub_rec.enable_opp_cyc_count , g_message);
337 Mdebug ( l_api_name||' : inv_cache.fromsub_rec.opp_cyc_count_header_id = ' || inv_cache.fromsub_rec.opp_cyc_count_header_id , g_message);
338 Mdebug ( l_api_name||' : inv_cache.fromsub_rec.pick_uom_code = ' || inv_cache.fromsub_rec.pick_uom_code , g_message);
339 Mdebug ( l_api_name||' : inv_cache.fromsub_rec.opp_cyc_count_quantity = ' || inv_cache.fromsub_rec.opp_cyc_count_quantity , g_message);
340 Mdebug ( l_api_name||' : inv_cache.fromsub_rec.opp_cyc_count_days = ' || inv_cache.fromsub_rec.opp_cyc_count_days , g_message);
341 Mdebug ( l_api_name||' : inv_cache.item_rec.primary_uom_code = ' || inv_cache.item_rec.primary_uom_code , g_message);
342 END IF;
343
344
345
346 IF (inv_cache.fromsub_rec.enable_opp_cyc_count = 'Y'
347 AND Nvl(inv_cache.fromsub_rec.opp_cyc_count_header_id,-1) > 0) THEN
348
349 -- Added for bug 9676695
350
351 BEGIN
352
353 SELECT 'Y'
354 INTO l_item_exists
355 FROM mtl_cycle_count_items
356 WHERE cycle_count_header_id = inv_cache.fromsub_rec.opp_cyc_count_header_id
357 AND inventory_item_id = p_inventory_item_id;
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 l_item_exists := 'N';
362
363 END;
364
365 IF (l_item_exists<>'Y') THEN
366 RETURN l_cyc_count_header_id;
367 END IF;
368
369 l_progress := 'Calling Get_total_item_qty';
370
371 l_tot_qty := Get_total_item_qty(p_organization_id,p_subinventory_code,p_loc_id,
372 p_inventory_item_id);
373
374 IF (l_debug = 1) THEN
375 Mdebug(l_api_name||' : l_tot_qty = '||l_tot_qty,g_message);
376 END IF;
377
378 l_progress := 'Calling Get_latest_cc_days';
379
380 l_no_of_days := Get_latest_cc_days(p_organization_id,p_subinventory_code,p_loc_id,
381 p_inventory_item_id);
382
383 IF (l_debug = 1) THEN
384 Mdebug(l_api_name||' : l_no_of_days = '||l_no_of_days,g_message);
385 END IF;
386
387 l_progress := 'Calling inv_convert.inv_um_convert';
388
389 IF (inv_cache.fromsub_rec.pick_uom_code IS NOT NULL) THEN
390
391 l_sub_tol_qty :=
392 inv_convert.inv_um_convert ( p_inventory_item_id,
393 5,
394 inv_cache.fromsub_rec.opp_cyc_count_quantity,
395 inv_cache.fromsub_rec.pick_uom_code,
396 inv_cache.item_rec.primary_uom_code,
397 NULL,
398 NULL
399 );
400 ELSE
401 l_sub_tol_qty := inv_cache.fromsub_rec.opp_cyc_count_quantity;
402 END IF;
403
404 IF (l_debug = 1) THEN
405 Mdebug(l_api_name||' : l_sub_tol_qty = '||l_sub_tol_qty,g_message);
406 END IF;
407
408 IF (l_tot_qty <= l_sub_tol_qty
409 AND l_no_of_days >= inv_cache.fromsub_rec.opp_cyc_count_days) THEN
410
411 l_cyc_count_header_id := inv_cache.fromsub_rec.opp_cyc_count_header_id;
412
413 END IF;
414
415 IF (l_debug = 1) THEN
416 Mdebug(l_api_name||' : l_cyc_count_header_id = '||l_cyc_count_header_id,g_message);
417 END IF;
418
419
420 END IF;
421
422 RETURN l_cyc_count_header_id;
423 EXCEPTION
424 WHEN fnd_api.g_exc_error THEN
425 IF (l_debug = 1) THEN
426 Mdebug(l_api_name||' : l_progress is ' || l_progress);
427 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_error: ' || SQLERRM, g_error);
428 END IF;
429 WHEN OTHERS THEN
430 IF (l_debug = 1) THEN
431 Mdebug(l_api_name||' : l_progress is ' || l_progress);
432 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_unexpected_error: ' || SQLERRM, g_error);
433 END IF;
434
435 END is_cyc_count_enabled;
436
437 /*
438
439 This procedure will return the existing uncounted cycle count tasks for this item for this SKU.
440
441 */
442 PROCEDURE delete_existing_cyc_count
443 (p_organization_id IN NUMBER ,
444 p_subinventory IN VARCHAR2 ,
445 p_locator_id IN NUMBER ,
446 p_inventory_item_id IN NUMBER
447 )
448 IS
449 l_api_name CONSTANT VARCHAR2(30) := 'delete_existing_cyc_count';
450 l_api_version CONSTANT NUMBER := 1.0;
451 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
452 BEGIN
453
454 IF ( l_debug = 1 ) THEN
455 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
456 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
457 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
458 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
459 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
460 END IF;
461 -- Delete WDT
462
463 DELETE FROM wms_dispatched_tasks
464 WHERE transaction_temp_id IN (SELECT CYCLE_COUNT_ENTRY_ID
465 FROM mtl_cycle_count_entries
466 WHERE ENTRY_STATUS_CODE IN (1,3)
467 AND ORGANIZATION_ID=p_organization_id
468 AND SUBINVENTORY=p_subinventory
469 AND LOCATOR_ID=p_locator_id
470 AND INVENTORY_ITEM_ID=p_inventory_item_id)
471 AND ORGANIZATION_ID=p_organization_id;
472
473 IF ( l_debug = 1 ) THEN
474 Mdebug ( l_api_name||' : *** Deleted '||sql%rowcount||' WDT records' , g_message);
475 END IF;
476
477 -- delete MCCE
478
479 DELETE FROM mtl_cycle_count_entries
480 WHERE ENTRY_STATUS_CODE IN (1, 3)
481 AND ORGANIZATION_ID=p_organization_id
482 AND SUBINVENTORY=p_subinventory
483 AND LOCATOR_ID=p_locator_id
484 AND INVENTORY_ITEM_ID=p_inventory_item_id;
485
486 IF ( l_debug = 1 ) THEN
487 Mdebug ( l_api_name||' : *** Deleted '||sql%rowcount||' MCCE records' , g_message);
488 END IF;
489
490 END delete_existing_cyc_count;
491
492 /*
493
494 This function will return the total primary qty of an item
495 for the parameters passed after discarding the loaded qty.
496
497 */
498 PROCEDURE get_system_qty
499 (p_organization_id IN NUMBER,
500 p_subinventory_code IN VARCHAR2,
501 p_loc_id IN NUMBER,
502 p_parent_lpn_id IN NUMBER DEFAULT NULL,
503 p_inventory_item_id IN NUMBER,
504 p_revision IN VARCHAR2 DEFAULT NULL,
505 p_lot_number IN VARCHAR2 DEFAULT NULL,
506 p_from_Serial_number IN VARCHAR2 DEFAULT NULL,
507 p_to_Serial_number IN VARCHAR2 DEFAULT NULL,
508 p_uom_code IN VARCHAR2,
509 x_system_quantity OUT NOCOPY NUMBER)
510 IS
511 l_api_name CONSTANT VARCHAR2(30) := 'get_system_qty';
512 l_api_version CONSTANT NUMBER := 1.0;
513 l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'),0);
514
515 l_tot_qty NUMBER;
516 l_cnt_qty NUMBER:=0;
517 l_loaded_sys_qty NUMBER;
518 l_serial_number_control_code NUMBER;
519
520
521 BEGIN
522
523 IF ( l_debug = 1 ) THEN
524 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
525 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
526 Mdebug ( l_api_name||' : p_subinventory_code = ' || p_subinventory_code , g_message);
527 Mdebug ( l_api_name||' : p_loc_id = ' || p_loc_id , g_message);
528 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
529 Mdebug ( l_api_name||' : p_parent_lpn_id = ' || p_parent_lpn_id , g_message);
530 Mdebug ( l_api_name||' : p_revision = ' || p_revision , g_message);
531 Mdebug ( l_api_name||' : p_lot_number = ' || p_lot_number , g_message);
532 Mdebug ( l_api_name||' : p_from_Serial_number = ' || p_from_Serial_number , g_message);
533 Mdebug ( l_api_name||' : p_to_Serial_number = ' || p_to_Serial_number , g_message);
534 Mdebug ( l_api_name||' : p_uom_code = ' || p_uom_code , g_message);
535 END IF;
536
537
538 l_serial_number_control_code := inv_cache.item_rec.serial_number_control_code;
539
540 IF ( l_serial_number_control_code IN ( 1, 6 ) ) THEN
541 IF ( l_debug = 1 ) THEN
542 Mdebug ( l_api_name||' : Non serial controlled item' , g_message);
543 END IF;
544
545 BEGIN
546
547 SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
548 INTO l_tot_qty
549 FROM MTL_ONHAND_QUANTITIES_DETAIL
550 WHERE inventory_item_id = p_inventory_item_id
551 AND organization_id = p_organization_id
552 AND subinventory_code = p_subinventory_code
553 AND locator_id = p_loc_id
554 AND ( (p_parent_lpn_id IS NOT NULL
555 AND NVL ( containerized_flag, 2 ) = 1)
556 AND lpn_id = p_parent_lpn_id
557 OR (p_parent_lpn_id IS NULL
558 AND NVL ( containerized_flag, 2 ) = 2)
559 )
560 AND ( lot_number = p_lot_number
561 OR p_lot_number IS NULL
562 )
563 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
564
565 EXCEPTION
566 WHEN no_data_found THEN
567 IF ( l_debug = 1 ) THEN
568 Mdebug ( l_api_name||' : No data found exception.. So l_tot_qty = 0 ' , g_message);
569 END IF;
570 l_tot_qty := 0;
571 END;
572
573 IF ( l_debug = 1 ) THEN
574 Mdebug ( l_api_name||' : MOQD qty is ' || l_tot_qty , g_message);
575 END IF;
576
577 BEGIN
578
579 SELECT NVL ( SUM ( quantity ), 0 )
580 INTO l_loaded_sys_qty
581 FROM WMS_LOADED_QUANTITIES_V
582 WHERE inventory_item_id = p_inventory_item_id
586 AND qty_type = 'LOADED'
583 AND organization_id = p_organization_id
584 AND subinventory_code = p_subinventory_code
585 AND locator_id = p_loc_id
587 AND ( (p_parent_lpn_id IS NOT NULL
588 AND NVL ( containerized_flag, 2 ) = 1)
589 AND NVL ( lpn_id, NVL ( content_lpn_id, -1 ) ) = p_parent_lpn_id
590 OR (p_parent_lpn_id IS NULL
591 AND NVL ( containerized_flag, 2 ) = 2)
592 )
593 AND ( lot_number = p_lot_number
594 OR p_lot_number IS NULL
595 )
596 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
597
598 EXCEPTION
599 WHEN no_data_found THEN
600 IF ( l_debug = 1 ) THEN
601 Mdebug ( l_api_name||' : No data found exception.. So l_loaded_sys_qty = 0 ' , g_message);
602 END IF;
603 l_loaded_sys_qty := 0;
604 END;
605
606
607 IF ( l_debug = 1 ) THEN
608 Mdebug ( l_api_name||' : Loaded qty is ' || l_loaded_sys_qty , g_message);
609 END IF;
610
611 ELSIF ( l_serial_number_control_code IN ( 2, 5 )) THEN
612 IF ( l_debug = 1 ) THEN
613 Mdebug ( l_api_name||' : Serial controlled item' , g_message);
614 END IF;
615
616 BEGIN
617
618 SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
619 INTO l_tot_qty
620 FROM mtl_serial_numbers
621 WHERE inventory_item_id = p_inventory_item_id
622 AND current_organization_id = p_organization_id
623 AND current_subinventory_code = p_subinventory_code
624 AND current_locator_id = p_loc_id
625 AND ( (p_parent_lpn_id IS NOT NULL
626 AND lpn_id = p_parent_lpn_id)
627 OR (p_parent_lpn_id IS NULL
628 AND lpn_id IS NULL)
629 )
630 AND ( lot_number = p_lot_number
631 OR p_lot_number IS NULL
632 )
633 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
634 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number);
635
636 EXCEPTION
637 WHEN no_data_found THEN
638 IF ( l_debug = 1 ) THEN
639 Mdebug ( l_api_name||' : No data found exception.. So l_tot_qty = 0 ' , g_message);
640 END IF;
641 l_tot_qty := 0;
642 END;
643
644 IF ( l_debug = 1 ) THEN
645 Mdebug ( l_api_name||' : MOQD qty is ' || l_tot_qty , g_message);
646 END IF;
647
648 BEGIN
649
650 SELECT Count(DISTINCT msn.serial_number)
651 INTO l_loaded_sys_qty
652 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
653 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
654 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id and
655 mtlt.lot_number is null) or
656 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
657 and mtlt.lot_number is not null))
658 AND mmtt.inventory_item_id = p_inventory_item_id
659 AND mmtt.organization_id = p_organization_id
660 AND mmtt.subinventory_code = p_subinventory_code
661 AND mmtt.locator_id = p_loc_id
662 AND ( (p_parent_lpn_id IS NOT NULL
663 AND NVL ( mmtt.lpn_id, NVL ( content_lpn_id, -1 ) ) = p_parent_lpn_id)
664 OR (p_parent_lpn_id IS NULL
665 AND NVL ( mmtt.lpn_id, NVL ( content_lpn_id, -1 ) ) = -1)
666 )
667 AND ( mtlt.lot_number = p_lot_number
668 OR p_lot_number IS NULL
669 )
670 AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
671 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR msn.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
672 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
673 AND msn.revision = mmtt.revision
674 AND msn.inventory_item_id = mmtt.inventory_item_id
675 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
676 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
677 AND wdt.task_type <> 2
678 AND wdt.status = 4;
679
680 EXCEPTION
681 WHEN no_data_found THEN
682 IF ( l_debug = 1 ) THEN
683 Mdebug ( l_api_name||' : No data found exception.. So l_loaded_sys_qty = 0 ' , g_message);
684 END IF;
685 l_loaded_sys_qty := 0;
686 END;
687
688 IF ( l_debug = 1 ) THEN
689 Mdebug ( l_api_name||' : Loaded qty is ' || l_loaded_sys_qty , g_message);
690 END IF;
691
692 BEGIN
693
694 SELECT Nvl(Sum(Decode(count_uom_current,
695 inv_cache.item_rec.primary_uom_code,
696 adj_cnt_qty,
697 (inv_convert.inv_um_convert(p_inventory_item_id,
698 5,
699 adj_cnt_qty,
700 count_uom_current,
701 inv_cache.item_rec.primary_uom_code,
702 NULL,
703 NULL
704 )
705 )
706 )
707 ),0)
711 WHERE inventory_item_id = p_inventory_item_id
708 INTO l_cnt_qty
709 FROM (SELECT count_uom_current, (system_quantity_current - count_quantity_current) adj_cnt_qty
710 FROM mtl_cycle_count_entries
712 AND organization_id = p_organization_id
713 AND subinventory = p_subinventory_code
714 AND locator_id = p_loc_id
715 AND cycle_count_header_id = g_cycle_count_header_id
716 AND entry_status_code = 2
717 AND ( (p_parent_lpn_id IS NOT NULL
718 AND parent_lpn_id = p_parent_lpn_id)
719 OR (p_parent_lpn_id IS NULL
720 AND parent_lpn_id IS NULL)
721 )
722 AND ( lot_number = p_lot_number
723 OR p_lot_number IS NULL
724 )
725 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
726 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
727 AND system_quantity_current <> count_quantity_current
728 UNION ALL
729 SELECT mcce.count_uom_current, (mcce.count_quantity_current - mcce.system_quantity_current) adj_cnt_qty
730 FROM mtl_cycle_count_entries mcce, mtl_serial_numbers msn
731 WHERE mcce.inventory_item_id = p_inventory_item_id
732 AND mcce.organization_id = p_organization_id
733 AND mcce.cycle_count_header_id = g_cycle_count_header_id
734 AND mcce.entry_status_code = 2
735 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR mcce.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
736 AND mcce.serial_number = msn.serial_number
737 AND msn.inventory_item_id = mcce.inventory_item_id
738 AND msn.CURRENT_ORGANIZATION_ID=mcce.organization_id
739 AND msn.current_subinventory_code = p_subinventory_code
740 AND msn.current_locator_id = p_loc_id
741 AND ( ( msn.lot_number = p_lot_number
742 AND msn.lot_number = mcce.lot_number
743 )
744 OR p_lot_number IS NULL
745 )
746 AND NVL ( msn.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
747 AND NVL ( mcce.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
748 AND ( mcce.subinventory <> msn.current_subinventory_code
749 OR mcce.locator_id <> msn.current_locator_id
750 )
751 AND mcce.system_quantity_current <> mcce.count_quantity_current);
752
753 EXCEPTION
754 WHEN no_data_found THEN
755 IF ( l_debug = 1 ) THEN
756 Mdebug ( l_api_name||' : No data found exception.. So l_cnt_qty = 0 ' , g_message);
757 END IF;
758 l_cnt_qty := 0;
759 END;
760
761 IF ( l_debug = 1 ) THEN
762 Mdebug ( l_api_name||' : Pending cycle count qty is ' || l_cnt_qty , g_message);
763 END IF;
764
765
766 END IF;
767
768 IF l_loaded_sys_qty > 0 THEN
769 l_tot_qty := l_tot_qty - l_loaded_sys_qty-l_cnt_qty;
770 ELSE
771 l_tot_qty := l_tot_qty - l_cnt_qty;
772 END IF;
773
774
775 IF ( l_debug = 1 ) THEN
776 Mdebug ( l_api_name||' : Total sys qty is ' || l_tot_qty , g_message);
777 END IF;
778
779 x_system_quantity := l_tot_qty;
780
781 END get_system_qty;
782
783 /*
784
785 This procedure will get the detailed allocated pending qty at the locator level for the item.
786
787 x_det_alloc_cur - A cursor which will give the pending allocations for the parameters passed.
788 x_allocated_qty - Total allocated pending qty for the parameters passed.
789 */
790
791 PROCEDURE get_locator_quantity
792 (p_organization_id IN NUMBER,
793 p_subinventory IN VARCHAR2,
794 p_locator_id IN NUMBER,
795 p_inventory_item_id IN NUMBER,
796 p_revision IN VARCHAR2,
797 p_lot_number IN VARCHAR2,
798 p_from_serial_number IN VARCHAR2 DEFAULT NULL,
799 p_to_serial_number IN VARCHAR2 DEFAULT NULL,
800 x_system_quantity OUT NOCOPY NUMBER
801 )
802 IS
803 l_api_name CONSTANT VARCHAR2(30) := 'get_locator_quantity';
804 l_api_version CONSTANT NUMBER := 1.0;
805 l_serial_number_control_code NUMBER;
806 l_progress VARCHAR2 ( 10 );
807 l_loaded_sys_qty NUMBER;
808 l_cnt_qty NUMBER:=0;
809 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
810
811 BEGIN
812
813 IF ( l_debug = 1 ) THEN
814 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
815 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
816 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
817 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
818 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
819 Mdebug ( l_api_name||' : p_revision = ' || p_revision , g_message);
820 Mdebug ( l_api_name||' : p_lot_number = ' || p_lot_number , g_message);
821 Mdebug ( l_api_name||' : p_from_serial_number = ' || p_from_serial_number , g_message);
822 Mdebug ( l_api_name||' : p_to_serial_number = ' || p_to_serial_number , g_message);
823 END IF;
824
825 -- Initialize the output variable
826 x_system_quantity := 0;
827 l_progress := '10';
828
829 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
833 END IF;
830
831 IF (l_debug = 1) THEN
832 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
834
835 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
836 fnd_msg_pub.ADD;
837 RAISE fnd_api.g_exc_error;
838
839 END IF;
840
841 l_serial_number_control_code := inv_cache.item_rec.serial_number_control_code;
842
843 l_progress := '20';
844
845 IF ( l_serial_number_control_code IN ( 1, 6 ) ) THEN
846 IF ( l_debug = 1 ) THEN
847 mdebug ( 'Non serial controlled item' );
848 END IF;
849
850 l_progress := '30';
851
852 SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
853 INTO x_system_quantity
854 FROM MTL_ONHAND_QUANTITIES_DETAIL
855 WHERE inventory_item_id = p_inventory_item_id
856 AND organization_id = p_organization_id
857 AND subinventory_code = p_subinventory
858 AND locator_id = p_locator_id
859 AND ( lot_number = p_lot_number
860 OR p_lot_number IS NULL
861 )
862 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
863
864 SELECT NVL ( SUM ( quantity ), 0 )
865 INTO l_loaded_sys_qty
866 FROM WMS_LOADED_QUANTITIES_V
867 WHERE inventory_item_id = p_inventory_item_id
868 AND organization_id = p_organization_id
869 AND subinventory_code = p_subinventory
870 AND locator_id = p_locator_id
871 AND ( lot_number = p_lot_number
872 OR p_lot_number IS NULL
873 )
874 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
875 AND qty_type = 'LOADED';
876
877 IF ( l_debug = 1 ) THEN
878 mdebug ( 'Loaded qty is ' || l_loaded_sys_qty );
879 END IF;
880
881 IF l_loaded_sys_qty > 0 THEN
882 x_system_quantity := x_system_quantity - l_loaded_sys_qty;
883 END IF;
884
885 l_progress := '40';
886
887 ELSIF ( l_serial_number_control_code IN ( 2, 5 ) ) THEN
888 IF ( l_debug = 1 ) THEN
889 mdebug ( 'Serial controlled item' );
890 END IF;
891
892 l_progress := '50';
893
894 SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
895 INTO x_system_quantity
896 FROM mtl_serial_numbers
897 WHERE inventory_item_id = p_inventory_item_id
898 AND current_organization_id = p_organization_id
899 AND current_subinventory_code = p_subinventory
900 AND current_locator_id = p_locator_id
901 AND ( lot_number = p_lot_number
902 OR p_lot_number IS NULL
903 )
904 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
905
906 SELECT Count(DISTINCT msn.serial_number)
907 INTO l_loaded_sys_qty
908 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
909 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
910 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
911 mtlt.lot_number is null) OR
912 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
913 AND mtlt.lot_number = p_lot_number)
914 )
915 AND mmtt.inventory_item_id = p_inventory_item_id
916 AND mmtt.organization_id = p_organization_id
917 AND mmtt.subinventory_code = p_subinventory
918 AND mmtt.locator_id = p_locator_id
919 AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
920 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
921 AND msn.inventory_item_id = mmtt.inventory_item_id
922 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
923 AND mmtt.transaction_temp_id=wdt.transaction_temp_id
924 AND NVL(wdt.status, 1) = 4;
925
926 BEGIN
927
928 SELECT Nvl(Sum(Decode(count_uom_current,
929 inv_cache.item_rec.primary_uom_code,
930 adj_cnt_qty,
931 (inv_convert.inv_um_convert(p_inventory_item_id,
932 5,
933 adj_cnt_qty,
934 count_uom_current,
935 inv_cache.item_rec.primary_uom_code,
936 NULL,
937 NULL
938 )
939 )
940 )
941 ),0)
942 INTO l_cnt_qty
943 FROM (SELECT count_uom_current, (system_quantity_current - count_quantity_current) adj_cnt_qty
944 FROM mtl_cycle_count_entries
945 WHERE inventory_item_id = p_inventory_item_id
946 AND organization_id = p_organization_id
947 AND subinventory = p_subinventory
948 AND locator_id = p_locator_id
949 AND cycle_count_header_id = g_cycle_count_header_id
950 AND entry_status_code = 2
951 AND ( lot_number = p_lot_number
952 OR p_lot_number IS NULL
953 )
954 AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
955 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
956 AND system_quantity_current <> count_quantity_current
960 WHERE mcce.inventory_item_id = p_inventory_item_id
957 UNION ALL
958 SELECT mcce.count_uom_current, (mcce.count_quantity_current - mcce.system_quantity_current) adj_cnt_qty
959 FROM mtl_cycle_count_entries mcce, mtl_serial_numbers msn
961 AND mcce.organization_id = p_organization_id
962 AND mcce.cycle_count_header_id = g_cycle_count_header_id
963 AND mcce.entry_status_code = 2
964 AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR mcce.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
965 AND mcce.serial_number = msn.serial_number
966 AND msn.inventory_item_id = mcce.inventory_item_id
967 AND msn.CURRENT_ORGANIZATION_ID=mcce.organization_id
968 AND msn.current_subinventory_code = p_subinventory
969 AND msn.current_locator_id = p_locator_id
970 AND ( ( msn.lot_number = p_lot_number
971 AND msn.lot_number = mcce.lot_number
972 )
973 OR p_lot_number IS NULL
974 )
975 AND NVL ( msn.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
976 AND NVL ( mcce.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
977 AND ( mcce.subinventory <> msn.current_subinventory_code
978 OR mcce.locator_id <> msn.current_locator_id
979 )
980 AND mcce.system_quantity_current <> mcce.count_quantity_current);
981
982 EXCEPTION
983 WHEN no_data_found THEN
984 IF ( l_debug = 1 ) THEN
985 Mdebug ( l_api_name||' : No data found exception.. So l_cnt_qty = 0 ' , g_message);
986 END IF;
987 l_cnt_qty := 0;
988 END;
989
990 IF ( l_debug = 1 ) THEN
991 Mdebug ( l_api_name||' : Pending cycle count qty is ' || l_cnt_qty , g_message);
992 END IF;
993
994
995
996
997 IF l_loaded_sys_qty > 0 THEN
998 x_system_quantity := x_system_quantity - l_loaded_sys_qty - l_cnt_qty;
999 ELSE
1000 x_system_quantity := x_system_quantity - l_cnt_qty;
1001 END IF;
1002
1003 l_progress := '60';
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 IF ( l_debug = 1 ) THEN
1009 mdebug ( 'Exiting get_locator_quantity - other exceptions:'
1010 || l_progress
1011 || ' '
1012 || TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
1013 );
1014 END IF;
1015 END get_locator_quantity;
1016
1017 /*
1018
1019 This procedure will get the total allocated pending qty for the selected item and SKU.
1020
1021 x_alloc_cur - A cursor which will give the pending allocations for the selected item and SKU ordered by priority.
1022 x_allocated_qty - Total allocated pending qty for the selected item and SKU ordered by priority.
1023
1024
1025 */
1026 PROCEDURE get_allocated_qty
1027 (p_organization_id IN NUMBER ,
1028 p_subinventory IN VARCHAR2 ,
1029 p_locator_id IN NUMBER := NULL ,
1030 p_parent_lpn_id IN NUMBER := NULL ,
1031 p_inventory_item_id IN NUMBER ,
1032 p_revision IN VARCHAR2 := NULL ,
1033 p_lot_number IN VARCHAR2 := NULL ,
1034 p_from_serial_number IN VARCHAR2 := NULL ,
1035 p_to_serial_number IN VARCHAR2 := NULL ,
1036 x_alloc_cur OUT NOCOPY t_genref,
1037 x_allocated_qty OUT NOCOPY NUMBER
1038 )
1039 IS
1040 l_api_name CONSTANT VARCHAR2(30) := 'get_allocated_qty';
1041 l_api_version CONSTANT NUMBER := 1.0;
1042 l_serial_number_control_code NUMBER;
1043 l_progress VARCHAR2 ( 10 );
1044 l_allocated_pri_qty NUMBER;
1045 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
1046
1047 BEGIN
1048
1049 IF ( l_debug = 1 ) THEN
1050 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
1051 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
1052 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
1053 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
1054 Mdebug ( l_api_name||' : p_parent_lpn_id = ' || p_parent_lpn_id , g_message);
1055 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
1056 Mdebug ( l_api_name||' : p_revision = ' || p_revision , g_message);
1057 Mdebug ( l_api_name||' : p_lot_number = ' || p_lot_number , g_message);
1058 Mdebug ( l_api_name||' : p_from_serial_number = ' || p_from_serial_number , g_message);
1059 Mdebug ( l_api_name||' : p_to_serial_number = ' || p_to_serial_number , g_message);
1060 END IF;
1061
1062
1063 -- Initialize the output variable
1064
1065 l_progress := '10';
1066
1067 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
1068
1069 IF (l_debug = 1) THEN
1070 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
1071 END IF;
1072
1073 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
1074 fnd_msg_pub.ADD;
1075 RAISE fnd_api.g_exc_error;
1076
1077 END IF;
1078
1079 IF (NOT inv_cache.Set_org_rec(p_organization_id => p_organization_id)) THEN
1080 IF (l_debug = 1) THEN
1084 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ORG');
1081 mdebug(l_api_name||' : '||p_organization_id||' is an invalid organization id',g_error);
1082 END IF;
1083
1085 fnd_msg_pub.ADD;
1086 RAISE fnd_api.g_exc_error;
1087
1088 END IF;
1089
1090
1091 l_progress := '20';
1092
1093 l_serial_number_control_code := inv_cache.item_rec.serial_number_control_code;
1094
1095 IF ( l_debug = 1 ) THEN
1096 Mdebug ( l_api_name||' : l_serial_number_control_code = ' || l_serial_number_control_code , g_message);
1097 END IF;
1098
1099
1100 l_progress := '30';
1101
1102 IF (( l_serial_number_control_code IN ( 1, 6 ) ) OR ( l_serial_number_control_code IN ( 2, 5 ) AND Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='N')) THEN
1103
1104 IF ( l_debug = 1 ) THEN
1105 Mdebug ( l_api_name||' : Non serial controlled item / serial item with no serial allocation' , g_message);
1106 END IF;
1107
1108 l_progress := '40';
1109
1110 BEGIN
1111 SELECT NVL ( SUM ( Nvl(mtlt.primary_quantity, mmtt.primary_quantity) ), 0 )
1112 INTO l_allocated_pri_qty
1113 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_transaction_lots_temp mtlt
1114 WHERE mmtt.inventory_item_id = p_inventory_item_id
1115 AND mmtt.organization_id = p_organization_id
1116 AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
1117 AND mmtt.subinventory_code = p_subinventory
1118 AND mmtt.locator_id = p_locator_id
1119 AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
1120 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1121 AND NVL ( mtlt.lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
1122 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1123 AND NVL(wdt.status, 1) <> 4;
1124
1125
1126 OPEN x_alloc_cur FOR
1127 SELECT mmtt.TRANSACTION_TEMP_ID, Nvl(Nvl(mtlt.primary_quantity, mmtt.primary_quantity), 0) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
1128 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_transaction_lots_temp mtlt
1129 WHERE mmtt.inventory_item_id = p_inventory_item_id
1130 AND mmtt.organization_id = p_organization_id
1131 AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
1132 AND mmtt.subinventory_code = p_subinventory
1133 AND mmtt.locator_id = p_locator_id
1134 AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
1135 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1136 AND NVL ( mtlt.lot_number, 'XX' ) = NVL ( p_lot_number, 'XX')
1137 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1138 AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
1139 ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
1140
1141
1142 EXCEPTION
1143 WHEN NO_DATA_FOUND THEN
1144 l_allocated_pri_qty := 0;
1145 END;
1146
1147 l_progress := '50';
1148
1149 ELSIF ( l_serial_number_control_code IN ( 2, 5 ) AND Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='Y') THEN
1150
1151 IF ( l_debug = 1 ) THEN
1152 Mdebug ( l_api_name||' : Serial controlled item with serial allocation' , g_message);
1153 END IF;
1154
1155 l_progress := '60';
1156
1157 SELECT Count(DISTINCT msn.serial_number)
1158 INTO l_allocated_pri_qty
1159 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
1160 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1161 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
1162 mtlt.lot_number IS NULL) OR
1163 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
1164 AND mtlt.lot_number IS NOT NULL)
1165 )
1166 AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
1167 AND mmtt.inventory_item_id = p_inventory_item_id
1168 AND mmtt.organization_id = p_organization_id
1169 AND mmtt.subinventory_code = p_subinventory
1170 AND mmtt.locator_id = p_locator_id
1171 AND nvl(mtlt.lot_number,'@@@') = nvl(p_lot_number,'@@@')
1172 AND nvl(mmtt.revision,'##') = nvl(p_revision,'##')
1173 AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
1174 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
1175 AND msn.inventory_item_id = mmtt.inventory_item_id
1176 AND (p_parent_lpn_id IS NULL OR NVL(msn.lpn_id, -1) = p_parent_lpn_id)
1177 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
1178 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1179 AND NVL(wdt.status, 1) <> 4;
1180
1181
1182 OPEN x_alloc_cur FOR
1183 SELECT DISTINCT mmtt.TRANSACTION_TEMP_ID, Count(DISTINCT msn.serial_number) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
1184 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
1185 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1186 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
1187 mtlt.lot_number is null) OR
1188 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
1189 AND mtlt.lot_number is not null)
1190 )
1194 AND mmtt.subinventory_code = p_subinventory
1191 AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
1192 AND mmtt.inventory_item_id = p_inventory_item_id
1193 AND mmtt.organization_id = p_organization_id
1195 AND mmtt.locator_id = p_locator_id
1196 AND nvl(mtlt.lot_number,'@@@') = nvl(p_lot_number,'@@@')
1197 AND nvl(mmtt.revision,'##') = nvl(p_revision,'##')
1198 AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
1199 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
1200 AND msn.inventory_item_id = mmtt.inventory_item_id
1201 AND (p_parent_lpn_id IS NULL OR NVL(msn.lpn_id, -1) = p_parent_lpn_id)
1202 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
1203 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1204 AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
1205 GROUP BY mmtt.TRANSACTION_TEMP_ID, mmtt.TASK_PRIORITY
1206 ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
1207
1208 l_progress := '70';
1209
1210 END IF;
1211
1212 IF ( l_debug = 1 ) THEN
1213 Mdebug ( l_api_name||' : Allocated primary qty is ' || l_allocated_pri_qty , g_message);
1214 END IF;
1215
1216
1217 l_progress := '80';
1218
1219 x_allocated_qty:= l_allocated_pri_qty;
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 IF ( l_debug = 1 ) THEN
1224 Mdebug(l_api_name||' : l_progress is ' || l_progress);
1225 Mdebug ( l_api_name||' : Exiting get_allocated_qty - other exceptions: '
1226 || SQLERRM, g_error
1227 );
1228 END IF;
1229
1230 x_allocated_qty:= 0;
1231
1232 RAISE fnd_api.g_exc_unexpected_error;
1233
1234 END get_allocated_qty;
1235
1236 /*
1237
1238 This procedure will get the total allocated pending serial qty for the selected item and SKU.
1239
1240 x_alloc_cur - A cursor which will give the pending allocations for the selected item and SKU ordered by priority.
1241 x_allocated_qty - Total allocated pending qty for the selected item and SKU ordered by priority.
1242
1243
1244 */
1245
1246 PROCEDURE get_serial_allocated_qty
1247 (p_organization_id IN NUMBER ,
1248 p_inventory_item_id IN NUMBER ,
1249 p_from_serial_number IN VARCHAR2 := NULL ,
1250 p_to_serial_number IN VARCHAR2 := NULL ,
1251 x_det_alloc_cur OUT NOCOPY t_genref,
1252 x_det_allocated_qty OUT NOCOPY NUMBER
1253 )
1254 IS
1255 l_api_name CONSTANT VARCHAR2(30) := 'get_serial_allocated_qty';
1256 l_api_version CONSTANT NUMBER := 1.0;
1257 l_serial_number_control_code NUMBER;
1258 l_progress VARCHAR2 ( 10 );
1259 l_allocated_pri_qty NUMBER;
1260 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
1261 BEGIN
1262
1263 IF ( l_debug = 1 ) THEN
1264 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
1265 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
1266 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
1267 Mdebug ( l_api_name||' : p_from_serial_number = ' || p_from_serial_number , g_message);
1268 Mdebug ( l_api_name||' : p_to_serial_number = ' || p_to_serial_number , g_message);
1269 END IF;
1270
1271
1272 -- Initialize the output variable
1273
1274 l_progress := '10';
1275
1276 SELECT Count(DISTINCT msn.serial_number)
1277 INTO l_allocated_pri_qty
1278 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
1279 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1280 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
1281 mtlt.lot_number is null) OR
1282 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
1283 AND mtlt.lot_number is not null)
1284 )
1285 AND mmtt.inventory_item_id = p_inventory_item_id
1286 AND mmtt.organization_id = p_organization_id
1287 AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
1288 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
1289 AND msn.inventory_item_id = mmtt.inventory_item_id
1290 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
1291 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1292 AND NVL(wdt.status, 1) <> 4;
1293
1294
1295 OPEN x_det_alloc_cur FOR
1296 SELECT DISTINCT mmtt.TRANSACTION_TEMP_ID, Count(DISTINCT msn.serial_number) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
1297 FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
1298 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
1299 AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
1300 mtlt.lot_number is null) OR
1301 (msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
1302 AND mtlt.lot_number is not null)
1303 )
1304 AND mmtt.inventory_item_id = p_inventory_item_id
1305 AND mmtt.organization_id = p_organization_id
1306 AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
1307 AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
1311 AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
1308 AND msn.inventory_item_id = mmtt.inventory_item_id
1309 AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
1310 AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
1312 GROUP BY mmtt.TRANSACTION_TEMP_ID, mmtt.TASK_PRIORITY
1313 ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
1314
1315 l_progress := '20';
1316
1317
1318 IF ( l_debug = 1 ) THEN
1319 Mdebug ( l_api_name||' : Allocated primary qty is ' || l_allocated_pri_qty , g_message);
1320 END IF;
1321
1322
1323 l_progress := '130';
1324
1325 x_det_allocated_qty:= l_allocated_pri_qty;
1326
1327
1328
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331 IF ( l_debug = 1 ) THEN
1332 Mdebug(l_api_name||' : l_progress is ' || l_progress);
1333 Mdebug ( l_api_name||' : Exiting get_serial_allocated_qty - other exceptions: '
1334 || SQLERRM, g_error
1335 );
1336 END IF;
1337
1338 x_det_allocated_qty:= 0;
1339
1340 RAISE fnd_api.g_exc_unexpected_error;
1341
1342 END get_serial_allocated_qty;
1343
1344
1345 /*
1346
1347 This procedure will backordering the tasks based on the cursor, and quantities passed.
1348
1349 x_det_alloc_cur - A cursor which will give the pending allocations for the parameters passed.
1350 x_allocated_qty - Total allocated pending qty for the parameters passed.
1351
1352
1353 */
1354 PROCEDURE process_backorder
1355 (p_count_qty IN NUMBER ,
1356 p_alloc_cur IN t_genref,
1357 p_user_id IN VARCHAR2,
1358 p_allocated_qty IN NUMBER,
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_count OUT NOCOPY NUMBER,
1361 x_msg_data OUT NOCOPY VARCHAR2
1362 )
1363 IS
1364 l_api_name CONSTANT VARCHAR2(30) := 'process_backorder';
1365 l_api_version CONSTANT NUMBER := 1.0;
1366 l_progress VARCHAR2 ( 10 );
1367 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
1368
1369 l_min_backorder_qty NUMBER;
1370 l_tot_backorder_qty NUMBER:=0;
1371 l_trx_tmp_id NUMBER;
1372 l_pri_qty NUMBER;
1373 l_priority NUMBER;
1374
1375 BEGIN
1376
1377 IF ( l_debug = 1 ) THEN
1378 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
1379 Mdebug ( l_api_name||' : p_count_qty = ' || p_count_qty , g_message);
1380 Mdebug ( l_api_name||' : p_allocated_qty = ' || p_allocated_qty , g_message);
1381 END IF;
1382
1383 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1384
1385 l_progress := '10';
1386
1387
1388 IF (p_allocated_qty>p_count_qty) THEN
1389
1390 l_min_backorder_qty := p_allocated_qty - p_count_qty;
1391
1392 IF ( l_debug = 1 ) THEN
1393 Mdebug ( l_api_name||' : ***l_min_backorder_qty*** '||l_min_backorder_qty , g_message);
1394 END IF;
1395
1396 l_progress := '20';
1397
1398 LOOP
1399 FETCH p_alloc_cur INTO l_trx_tmp_id, l_pri_qty, l_priority;
1400 EXIT WHEN p_alloc_cur%NOTFOUND;
1401
1402 l_progress := '30';
1403
1404 wms_txnrsn_actions_pub.cleanup_task
1405 ( p_temp_id => l_trx_tmp_id
1406 , p_qty_rsn_id => 0
1407 , p_user_id => p_user_id
1408 , p_employee_id => -1
1409 , p_envoke_workflow => 'N'
1410 , x_return_status => x_return_status
1411 , x_msg_count => x_msg_count
1412 , x_msg_data => x_msg_data);
1413
1414 IF (l_debug = 1) THEN
1415 mdebug (l_api_name||' : x_return_status for wms_txnrsn_actions_pub.cleanup_task : ' || x_return_status , g_message);
1416 END IF;
1417
1418 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1419 RAISE fnd_api.g_exc_unexpected_error;
1420 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1421 RAISE fnd_api.g_exc_error;
1422 END IF;
1423
1424 l_tot_backorder_qty:= l_tot_backorder_qty+l_pri_qty;
1425
1426 IF ( l_debug = 1 ) THEN
1427 Mdebug ( l_api_name||' : ***l_tot_backorder_qty*** '||l_tot_backorder_qty , g_message);
1428 END IF;
1429
1430 l_progress := '40';
1431
1432 EXIT WHEN l_tot_backorder_qty>= l_min_backorder_qty;
1433
1434 END LOOP;
1435
1436 l_progress := '50';
1437
1438 IF (p_alloc_cur%ISOPEN) THEN
1439 CLOSE p_alloc_cur;
1440 END IF;
1441
1442 END IF;
1443
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 IF ( l_debug = 1 ) THEN
1447 Mdebug(l_api_name||' : l_progress is ' || l_progress);
1448 Mdebug ( l_api_name||' : Exiting process_backorder - other exceptions: '
1449 || SQLERRM, g_error
1450 );
1451 END IF;
1452
1453 x_msg_data := SQLERRM;
1454
1455 RAISE fnd_api.g_exc_unexpected_error;
1456
1457 END process_backorder;
1458
1459
1460
1461 /*
1462
1463 This procedure will backorder the pending tasks for this item for the passed SKU based on the priority
1464 if the counted qty is less than the allocated qty.
1465
1466 Allocations can be of three types.
1470
1467 1. Allocation at the serial level (if serial allocation is set as Yes).
1468 2. Allocation at the lpn level (Allocate lpn mode in rules).
1469 3. Allocation at the locator level.
1471 Backordering needs to take care of the following conditions.
1472
1473 1. Counted lpn is in a diff location.
1474 a. Delete all the lpn level allocations for this lpn.
1475 b. Delete all the serial level allocations for the serial item inside this lpn, if serial allocation is set as Yes.
1476 c. Check for the total remaining qty at the locator level for each item inside the lpn, and delete the required allocations.
1477
1478 2. Counted serial is in a diff location.
1479 a. Delete all the serial level allocations for the serials, if serial allocation is set as Yes.
1480 b. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
1481
1482 3. Counted lpn in the same location.
1483 a. Check for the total lpn level allocations for this lpn, and backorder the allocations till the allocated qty is <= the count qty.
1484 b. If the counted item is serial controlled and if serial allocation is set as Yes, delete the required allocations if the system qty is less than count qty.
1485 c. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
1486
1487 4. Counted serials in the same location.
1488 a. If the counted item is serial controlled and if serial allocation is set as Yes, delete the required allocations if the system qty is less than count qty.
1489 b. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
1490
1491 5. Counted for non serial loose qties in the same location.
1492 a. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
1493
1494 */
1495 PROCEDURE backorder_pending_tasks
1496 (p_organization_id IN NUMBER ,
1497 p_subinventory IN VARCHAR2 ,
1498 p_locator_id IN NUMBER := NULL ,
1499 p_parent_lpn_id IN NUMBER := NULL ,
1500 p_inventory_item_id IN NUMBER ,
1501 p_revision IN VARCHAR2 := NULL ,
1502 p_lot_number IN VARCHAR2 := NULL ,
1503 p_from_serial_number IN VARCHAR2 := NULL ,
1504 p_to_serial_number IN VARCHAR2 := NULL ,
1505 p_count_quantity IN NUMBER ,
1506 p_count_uom IN VARCHAR2 ,
1507 p_user_id IN NUMBER,
1508 p_cost_group_id IN NUMBER := NULL,
1509 p_secondary_uom IN VARCHAR2 := NULL,
1510 p_secondary_qty IN NUMBER := NULL,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2
1514 )
1515 IS
1516
1517 l_api_name CONSTANT VARCHAR2(30) := 'backorder_pending_tasks';
1518 l_api_version CONSTANT NUMBER := 1.0;
1519 l_progress VARCHAR2 ( 10 );
1520 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
1521
1522 l_count_primary_qty NUMBER;
1523 l_count_qty NUMBER;
1524 l_alloc_qty NUMBER;
1525 l_alloc_cur t_genref;
1526 l_det_alloc_qty NUMBER;
1527 l_det_alloc_cur t_genref;
1528 l_serial_number_control_code NUMBER;
1529 l_sys_det_qty NUMBER:=0;
1530 l_sys_ser_qty NUMBER:=0;
1531 l_sys_tot_qty NUMBER:=0;
1532 l_lpn_subinv VARCHAR2(80);
1533 l_lpn_locator_id NUMBER;
1534 l_lpn_context NUMBER;
1535 BEGIN
1536
1537 IF ( l_debug = 1 ) THEN
1538 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
1539 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
1540 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
1541 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
1542 Mdebug ( l_api_name||' : p_parent_lpn_id = ' || p_parent_lpn_id , g_message);
1543 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
1544 Mdebug ( l_api_name||' : p_revision = ' || p_revision , g_message);
1545 Mdebug ( l_api_name||' : p_lot_number = ' || p_lot_number , g_message);
1546 Mdebug ( l_api_name||' : p_from_serial_number = ' || p_from_serial_number , g_message);
1547 Mdebug ( l_api_name||' : p_to_serial_number = ' || p_to_serial_number , g_message);
1548 Mdebug ( l_api_name||' : p_count_quantity = ' || p_count_quantity , g_message);
1549 Mdebug ( l_api_name||' : p_count_uom = ' || p_count_uom , g_message);
1550 Mdebug ( l_api_name||' : p_user_id = ' || p_user_id , g_message);
1551 Mdebug ( l_api_name||' : p_cost_group_id = ' || p_cost_group_id , g_message);
1552 Mdebug ( l_api_name||' : p_secondary_uom = ' || p_secondary_uom , g_message);
1553 Mdebug ( l_api_name||' : p_secondary_qty = ' || p_secondary_qty , g_message);
1554 END IF;
1555
1556 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1557 -- backorder pending tasks if needed.
1558
1559 l_progress := '10';
1560
1561 IF (NOT inv_cache.Set_org_rec(p_organization_id => p_organization_id)) THEN
1562
1563 IF (l_debug = 1) THEN
1564 mdebug(l_api_name||' : '||p_organization_id||' is an invalid organization id',g_error);
1565 END IF;
1566
1567 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ORG');
1568 fnd_msg_pub.ADD;
1569 RAISE fnd_api.g_exc_error;
1570
1571 END IF;
1572
1573
1574 IF ( l_debug = 1 ) THEN
1575 Mdebug ( l_api_name||' : ***backorder_pending_tasks***' , g_message);
1579
1576 END IF;
1577
1578 l_serial_number_control_code := inv_cache.item_rec.serial_number_control_code;
1580 l_count_primary_qty :=
1581 inv_convert.inv_um_convert ( p_inventory_item_id,
1582 5,
1583 p_count_quantity,
1584 p_count_uom,
1585 inv_cache.item_rec.primary_uom_code,
1586 NULL,
1587 NULL
1588 );
1589
1590 l_progress := '15';
1591
1592 get_system_qty
1593 ( p_organization_id => p_organization_id
1594 , p_subinventory_code => p_subinventory
1595 , p_loc_id => p_locator_id
1596 , p_parent_lpn_id => p_parent_lpn_id
1597 , p_inventory_item_id => p_inventory_item_id
1598 , p_revision => p_revision
1599 , p_lot_number => p_lot_number
1600 , p_uom_code => inv_cache.item_rec.primary_uom_code
1601 , x_system_quantity => l_sys_det_qty
1602 );
1603
1604 IF ( l_debug = 1 ) THEN
1605 Mdebug ( l_api_name||' : l_sys_det_qty = ' || l_sys_det_qty , g_message);
1606 END IF;
1607
1608 IF (l_serial_number_control_code IN ( 2, 5 )) THEN
1609
1610 l_progress := '17';
1611
1612 get_system_qty
1613 ( p_organization_id => p_organization_id
1614 , p_subinventory_code => p_subinventory
1615 , p_loc_id => p_locator_id
1616 , p_parent_lpn_id => p_parent_lpn_id
1617 , p_inventory_item_id => p_inventory_item_id
1618 , p_revision => p_revision
1619 , p_lot_number => p_lot_number
1620 , p_from_serial_number => p_from_serial_number
1621 , p_to_serial_number => p_to_serial_number
1622 , p_uom_code => inv_cache.item_rec.primary_uom_code
1623 , x_system_quantity => l_sys_ser_qty
1624 );
1625
1626 IF ( l_debug = 1 ) THEN
1627 Mdebug ( l_api_name||' : l_sys_ser_qty = ' || l_sys_ser_qty , g_message);
1628 END IF;
1629
1630 END IF;
1631
1632 IF (p_from_serial_number IS NOT NULL AND p_to_serial_number IS NOT NULL) THEN
1633
1634 FOR ser_cur IN (SELECT current_subinventory_code, current_locator_id, serial_number
1635 FROM mtl_serial_numbers
1636 WHERE inventory_item_id = p_inventory_item_id
1637 AND current_organization_id = p_organization_id
1638 AND serial_number BETWEEN p_from_serial_number AND p_to_serial_number
1639 AND (current_subinventory_code<>p_subinventory
1640 OR current_locator_id<>p_locator_id)) LOOP
1641
1642 l_progress := '18';
1643
1644 get_serial_allocated_qty
1645 ( p_organization_id => p_organization_id
1646 , p_inventory_item_id => p_inventory_item_id
1647 , p_from_serial_number => ser_cur.serial_number
1648 , p_to_serial_number => ser_cur.serial_number
1649 , x_det_allocated_qty => l_det_alloc_qty
1650 , x_det_alloc_cur => l_det_alloc_cur
1651 );
1652
1653 IF ( l_debug = 1 ) THEN
1654 Mdebug ( l_api_name||' : ***l_ser_alloc_qty*** '||l_det_alloc_qty , g_message);
1655 END IF;
1656
1657 l_progress := '19';
1658
1659 process_backorder
1660 ( p_count_qty => 0
1661 , p_alloc_cur => l_det_alloc_cur
1662 , p_user_id => p_user_id
1663 , p_allocated_qty => l_det_alloc_qty
1664 , x_return_status => x_return_status
1665 , x_msg_count => x_msg_count
1666 , x_msg_data => x_msg_data
1667 );
1668
1669 IF (l_debug = 1) THEN
1670 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1671 END IF;
1672
1673 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1674 RAISE fnd_api.g_exc_unexpected_error;
1675 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1676 RAISE fnd_api.g_exc_error;
1677 END IF;
1678
1679 END LOOP;
1680
1681 IF (l_sys_ser_qty>l_count_primary_qty) THEN
1682
1683 l_progress := '20';
1684
1685 get_serial_allocated_qty
1686 ( p_organization_id => p_organization_id
1687 , p_inventory_item_id => p_inventory_item_id
1688 , p_from_serial_number => p_from_serial_number
1689 , p_to_serial_number => p_to_serial_number
1690 , x_det_allocated_qty => l_det_alloc_qty
1691 , x_det_alloc_cur => l_det_alloc_cur
1692 );
1693
1694 IF ( l_debug = 1 ) THEN
1695 Mdebug ( l_api_name||' : ***l_ser_alloc_qty*** '||l_det_alloc_qty , g_message);
1696 END IF;
1697
1698 l_progress := '21';
1699
1700 process_backorder
1701 ( p_count_qty => l_count_primary_qty
1702 , p_alloc_cur => l_det_alloc_cur
1703 , p_user_id => p_user_id
1704 , p_allocated_qty => l_det_alloc_qty
1705 , x_return_status => x_return_status
1706 , x_msg_count => x_msg_count
1707 , x_msg_data => x_msg_data
1708 );
1709
1710 IF (l_debug = 1) THEN
1711 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1712 END IF;
1713
1714 l_progress := '22';
1715
1719 RAISE fnd_api.g_exc_error;
1716 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1717 RAISE fnd_api.g_exc_unexpected_error;
1718 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1720 END IF;
1721
1722 END IF;
1723
1724 END IF;
1725
1726
1727 l_progress := '25';
1728
1729 IF ( p_parent_lpn_id IS NOT NULL ) THEN
1730
1731 SELECT NVL ( subinventory_code, '###' ),
1732 NVL ( locator_id, -99 ),
1733 lpn_context
1734 INTO l_lpn_subinv,
1735 l_lpn_locator_id,
1736 l_lpn_context
1737 FROM WMS_LICENSE_PLATE_NUMBERS
1738 WHERE lpn_id = p_parent_lpn_id ;
1739
1740 l_progress := '30';
1741
1742 IF ( l_debug = 1 ) THEN
1743 Mdebug ( l_api_name||' : l_lpn_subinv: ===> ' || l_lpn_subinv , g_message);
1744 Mdebug ( l_api_name||' : l_lpn_locator_id: => ' || l_lpn_locator_id , g_message);
1745 Mdebug ( l_api_name||' : l_lpn_context: => ' || l_lpn_context , g_message);
1746 END IF;
1747
1748 IF (l_lpn_context = 8 or l_lpn_context = 9 or l_lpn_context = 4 or l_lpn_context = 6) THEN
1749 IF ( l_debug = 1 ) THEN
1750 Mdebug ( l_api_name||' : Returning as lpn is not in inventory' , g_message);
1751 END IF;
1752
1753 RETURN;
1754 ELSIF (p_subinventory=l_lpn_subinv AND p_locator_id=l_lpn_locator_id) THEN
1755
1756 IF ( l_debug = 1 ) THEN
1757 Mdebug ( l_api_name||' : LPN is already in the count subinv. So backorder only required allocations.' , g_message);
1758 END IF;
1759
1760 l_progress := '40';
1761
1762 IF ((l_serial_number_control_code IN ( 1, 6 ) AND l_sys_det_qty>l_count_primary_qty)
1763 OR (l_serial_number_control_code IN ( 2,5 ) AND l_sys_ser_qty>l_count_primary_qty AND Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='N')) THEN
1764
1765 get_allocated_qty
1766 ( p_organization_id => p_organization_id
1767 , p_subinventory => p_subinventory
1768 , p_locator_id => p_locator_id
1769 , p_parent_lpn_id => p_parent_lpn_id
1770 , p_inventory_item_id => p_inventory_item_id
1771 , p_revision => p_revision
1772 , p_lot_number => p_lot_number
1773 , x_allocated_qty => l_det_alloc_qty
1774 , x_alloc_cur => l_det_alloc_cur
1775 );
1776
1777 IF ( l_debug = 1 ) THEN
1778 Mdebug ( l_api_name||' : ***l_det_alloc_qty*** '||l_det_alloc_qty , g_message);
1779 END IF;
1780
1781 l_progress := '50';
1782
1783 IF (l_serial_number_control_code IN ( 1, 6 )) THEN
1784 l_count_qty := l_count_primary_qty;
1785 ELSE
1786 l_count_qty := (l_sys_det_qty-(l_sys_ser_qty-l_count_primary_qty));
1787 END IF;
1788
1789 process_backorder
1790 ( p_count_qty => l_count_qty
1791 , p_alloc_cur => l_det_alloc_cur
1792 , p_user_id => p_user_id
1793 , p_allocated_qty => l_det_alloc_qty
1794 , x_return_status => x_return_status
1795 , x_msg_count => x_msg_count
1796 , x_msg_data => x_msg_data
1797 );
1798
1799
1800 l_progress := '60';
1801
1802 IF (l_debug = 1) THEN
1803 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1804 END IF;
1805
1806 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1807 RAISE fnd_api.g_exc_unexpected_error;
1808 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1809 RAISE fnd_api.g_exc_error;
1810 END IF;
1811
1812 END IF;
1813
1814 ELSE
1815
1816 IF ( l_debug = 1 ) THEN
1817 Mdebug ( l_api_name||' : LPN is in different subinv. So backorder all lpn allocations.' , g_message);
1818 END IF;
1819
1820 l_progress := '70';
1821
1822 FOR lpn_contents_cur IN (SELECT DISTINCT wlc.inventory_item_id, wlc.lot_number, wlc.revision, DECODE(NVL(msn.serial_number, 'XXXX'), 'XXXX', wlc.primary_quantity, 1) primary_quantity, msn.serial_number
1823 FROM wms_lpn_contents wlc, mtl_serial_numbers msn
1824 WHERE wlc.parent_lpn_id=p_parent_lpn_id
1825 AND wlc.inventory_item_id = msn.inventory_item_id (+)
1826 AND ( msn.inventory_item_id IS NULL
1827 OR (msn.current_organization_id = p_organization_id
1828 AND msn.lpn_id=wlc.parent_lpn_id)))
1829 LOOP
1830
1831 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
1832
1833 IF (l_debug = 1) THEN
1834 mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
1835 END IF;
1836
1837 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
1838 fnd_msg_pub.ADD;
1839 RAISE fnd_api.g_exc_error;
1840
1841 END IF;
1842
1843 l_progress := '80';
1844
1845 get_allocated_qty
1846 ( p_organization_id => p_organization_id
1847 , p_subinventory => l_lpn_subinv
1848 , p_locator_id => l_lpn_locator_id
1849 , p_parent_lpn_id => p_parent_lpn_id
1850 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
1851 , p_revision => lpn_contents_cur.revision
1852 , p_lot_number => lpn_contents_cur.lot_number
1853 , p_from_serial_number => lpn_contents_cur.serial_number
1854 , p_to_serial_number => lpn_contents_cur.serial_number
1855 , x_allocated_qty => l_det_alloc_qty
1856 , x_alloc_cur => l_det_alloc_cur
1857 );
1858
1859 IF ( l_debug = 1 ) THEN
1860 Mdebug ( l_api_name||' : ***l_det_alloc_qty*** '||l_det_alloc_qty , g_message);
1861 END IF;
1862
1863 l_progress := '100';
1864
1865 process_backorder
1866 ( p_count_qty => 0
1867 , p_alloc_cur => l_det_alloc_cur
1868 , p_user_id => p_user_id
1869 , p_allocated_qty => l_det_alloc_qty
1870 , x_return_status => x_return_status
1871 , x_msg_count => x_msg_count
1872 , x_msg_data => x_msg_data
1873 );
1874
1875 IF (l_debug = 1) THEN
1876 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1877 END IF;
1878
1879 l_progress := '110';
1880
1881 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1882 RAISE fnd_api.g_exc_unexpected_error;
1883 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1884 RAISE fnd_api.g_exc_error;
1885 END IF;
1886
1887
1888 IF ( Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='Y' AND lpn_contents_cur.serial_number IS NOT NULL) THEN
1889
1890 l_progress := '120';
1891
1892 get_serial_allocated_qty
1893 ( p_organization_id => p_organization_id
1894 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
1895 , p_from_serial_number => lpn_contents_cur.serial_number
1896 , p_to_serial_number => lpn_contents_cur.serial_number
1897 , x_det_allocated_qty => l_det_alloc_qty
1898 , x_det_alloc_cur => l_det_alloc_cur
1899 );
1900
1901 IF ( l_debug = 1 ) THEN
1902 Mdebug ( l_api_name||' : ***l_ser_alloc_qty*** '||l_det_alloc_qty , g_message);
1903 END IF;
1904
1905 l_progress := '130';
1906
1907 process_backorder
1908 ( p_count_qty => 0
1909 , p_alloc_cur => l_det_alloc_cur
1910 , p_user_id => p_user_id
1911 , p_allocated_qty => l_det_alloc_qty
1912 , x_return_status => x_return_status
1913 , x_msg_count => x_msg_count
1914 , x_msg_data => x_msg_data
1915 );
1916
1917 IF (l_debug = 1) THEN
1918 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1919 END IF;
1920
1921 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1922 RAISE fnd_api.g_exc_unexpected_error;
1923 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1924 RAISE fnd_api.g_exc_error;
1925 END IF;
1926
1927 END IF;
1928
1929 END LOOP;
1930
1931 FOR lpn_contents_cur IN (SELECT wlc.inventory_item_id, wlc.lot_number, wlc.revision, Sum(wlc.primary_quantity) primary_quantity
1932 FROM wms_lpn_contents wlc
1933 WHERE wlc.parent_lpn_id=p_parent_lpn_id
1934 GROUP BY wlc.inventory_item_id, wlc.lot_number, wlc.revision)
1935 LOOP
1936
1937 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
1938
1939 IF (l_debug = 1) THEN
1940 mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
1941 END IF;
1942
1943 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
1944 fnd_msg_pub.ADD;
1945 RAISE fnd_api.g_exc_error;
1946
1947 END IF;
1948
1949 l_progress := '131';
1950
1951 get_locator_quantity
1952 ( p_organization_id => p_organization_id
1953 , p_subinventory => l_lpn_subinv
1954 , p_locator_id => l_lpn_locator_id
1955 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
1956 , p_revision => lpn_contents_cur.revision
1957 , p_lot_number => lpn_contents_cur.lot_number
1958 , x_system_quantity => l_sys_tot_qty
1959 );
1960
1961 IF ( l_debug = 1 ) THEN
1962 Mdebug ( l_api_name||' : System qty at the locator = ' || l_sys_tot_qty , g_message);
1963 END IF;
1964
1965 l_progress := '140';
1966
1967 get_allocated_qty
1968 ( p_organization_id => p_organization_id
1969 , p_subinventory => l_lpn_subinv
1970 , p_locator_id => l_lpn_locator_id
1971 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
1972 , p_revision => lpn_contents_cur.revision
1973 , p_lot_number => lpn_contents_cur.lot_number
1974 , x_allocated_qty => l_alloc_qty
1975 , x_alloc_cur => l_alloc_cur
1976 );
1977
1978 IF ( l_debug = 1 ) THEN
1979 Mdebug ( l_api_name||' : ***l_alloc_qty*** '||l_alloc_qty , g_message);
1980 END IF;
1981
1982 IF (l_alloc_qty>(l_sys_tot_qty-(lpn_contents_cur.primary_quantity))) THEN
1983
1984 l_progress := '160';
1985
1986 process_backorder
1987 ( p_count_qty => (l_sys_tot_qty-(lpn_contents_cur.primary_quantity))
1988 , p_alloc_cur => l_alloc_cur
1989 , p_user_id => p_user_id
1990 , p_allocated_qty => l_alloc_qty
1991 , x_return_status => x_return_status
1992 , x_msg_count => x_msg_count
1993 , x_msg_data => x_msg_data
1994 );
1995
1996 IF (l_debug = 1) THEN
1997 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
1998 END IF;
1999
2000 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2001 RAISE fnd_api.g_exc_unexpected_error;
2002 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2003 RAISE fnd_api.g_exc_error;
2004 END IF;
2005
2006 END IF;
2007 END LOOP;
2008
2009 l_progress := '170';
2010
2011 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
2012
2013 IF (l_debug = 1) THEN
2014 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
2015 END IF;
2016
2017 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2018 fnd_msg_pub.ADD;
2019 RAISE fnd_api.g_exc_error;
2020
2021 END IF;
2022
2023 l_progress := '180';
2024
2025 l_count_primary_qty :=
2026 inv_convert.inv_um_convert ( p_inventory_item_id,
2027 5,
2028 p_count_quantity,
2029 p_count_uom,
2030 inv_cache.item_rec.primary_uom_code,
2031 NULL,
2032 NULL
2033 );
2034
2035 l_progress := '190';
2036
2037 END IF;
2038
2039 END IF;
2040
2041 IF (p_from_serial_number IS NOT NULL AND p_to_serial_number IS NOT NULL) THEN
2042
2043 FOR ser_cur IN (SELECT current_subinventory_code, current_locator_id, Count(DISTINCT serial_number) ser_cnt
2044 FROM mtl_serial_numbers
2045 WHERE inventory_item_id = p_inventory_item_id
2046 AND current_organization_id = p_organization_id
2047 AND serial_number BETWEEN p_from_serial_number AND p_to_serial_number
2048 AND (current_subinventory_code<>p_subinventory
2049 OR current_locator_id<>p_locator_id)
2050 GROUP BY current_subinventory_code, current_locator_id) LOOP
2051
2052 l_progress := '193';
2053
2054 get_locator_quantity
2055 ( p_organization_id => p_organization_id
2056 , p_subinventory => ser_cur.current_subinventory_code
2057 , p_locator_id => ser_cur.current_locator_id
2058 , p_inventory_item_id => p_inventory_item_id
2059 , p_revision => p_revision
2060 , p_lot_number => p_lot_number
2061 , x_system_quantity => l_sys_tot_qty
2062 );
2063
2064 IF ( l_debug = 1 ) THEN
2065 Mdebug ( l_api_name||' : System qty at the locator = ' || l_sys_tot_qty , g_message);
2066 END IF;
2067
2068 l_progress := '194';
2069
2070 get_allocated_qty
2071 ( p_organization_id => p_organization_id
2072 , p_subinventory => ser_cur.current_subinventory_code
2073 , p_locator_id => ser_cur.current_locator_id
2074 , p_inventory_item_id => p_inventory_item_id
2075 , p_revision => p_revision
2076 , p_lot_number => p_lot_number
2077 , x_allocated_qty => l_alloc_qty
2078 , x_alloc_cur => l_alloc_cur
2079 );
2080
2081 IF ( l_debug = 1 ) THEN
2082 Mdebug ( l_api_name||' : ***l_alloc_qty*** '||l_alloc_qty , g_message);
2083 END IF;
2084
2085 IF (l_alloc_qty>(l_sys_tot_qty-(ser_cur.ser_cnt))) THEN
2086
2087 l_progress := '195';
2088
2089 process_backorder
2090 ( p_count_qty => (l_sys_tot_qty-(ser_cur.ser_cnt))
2091 , p_alloc_cur => l_alloc_cur
2092 , p_user_id => p_user_id
2093 , p_allocated_qty => l_alloc_qty
2094 , x_return_status => x_return_status
2095 , x_msg_count => x_msg_count
2096 , x_msg_data => x_msg_data
2097 );
2098
2099 IF (l_debug = 1) THEN
2100 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
2101 END IF;
2102
2103 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2104 RAISE fnd_api.g_exc_unexpected_error;
2105 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2106 RAISE fnd_api.g_exc_error;
2107 END IF;
2108
2109 END IF;
2110
2111 END LOOP;
2112
2113 END IF;
2114
2115 IF ( l_debug = 1 ) THEN
2116 Mdebug ( l_api_name||' : l_serial_number_control_code = ' || l_serial_number_control_code , g_message);
2117 END IF;
2118
2119 IF ((l_serial_number_control_code IN ( 1, 6 ) AND l_sys_det_qty>l_count_primary_qty)
2120 OR (l_serial_number_control_code IN ( 2,5 ) AND l_sys_ser_qty>l_count_primary_qty AND Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='N')) THEN
2121
2122 l_progress := '210';
2123
2124 get_locator_quantity
2125 ( p_organization_id => p_organization_id
2126 , p_subinventory => p_subinventory
2127 , p_locator_id => p_locator_id
2128 , p_inventory_item_id => p_inventory_item_id
2129 , p_revision => p_revision
2130 , p_lot_number => p_lot_number
2131 , x_system_quantity => l_sys_tot_qty
2132 );
2133
2134 IF ( l_debug = 1 ) THEN
2135 Mdebug ( l_api_name||' : System qty at the locator = ' || l_sys_tot_qty , g_message);
2136 END IF;
2137
2138 l_progress := '260';
2139
2140 get_allocated_qty
2141 ( p_organization_id => p_organization_id
2142 , p_subinventory => p_subinventory
2143 , p_locator_id => p_locator_id
2144 , p_inventory_item_id => p_inventory_item_id
2145 , p_revision => p_revision
2146 , p_lot_number => p_lot_number
2147 , x_allocated_qty => l_alloc_qty
2148 , x_alloc_cur => l_alloc_cur
2149 );
2150
2151 IF ( l_debug = 1 ) THEN
2152 Mdebug ( l_api_name||' : ***l_alloc_qty*** '||l_alloc_qty , g_message);
2153 END IF;
2154
2155 l_progress := '270';
2156
2157 IF (l_serial_number_control_code IN ( 1, 6 )) THEN
2158 l_count_qty := (l_sys_tot_qty-(l_sys_det_qty-l_count_primary_qty));
2159 ELSE
2160 l_count_qty := (l_sys_tot_qty-(l_sys_ser_qty-l_count_primary_qty));
2161 END IF;
2162
2163 IF (l_alloc_qty>l_count_qty) THEN
2164
2165 process_backorder
2166 ( p_count_qty => l_count_qty
2167 , p_alloc_cur => l_alloc_cur
2168 , p_user_id => p_user_id
2169 , p_allocated_qty => l_alloc_qty
2170 , x_return_status => x_return_status
2171 , x_msg_count => x_msg_count
2172 , x_msg_data => x_msg_data
2173 );
2174
2175 IF (l_debug = 1) THEN
2176 mdebug (l_api_name||' : x_return_status for process_backorder : ' || x_return_status , g_message);
2177 END IF;
2178
2179 l_progress := '280';
2180
2181 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2182 RAISE fnd_api.g_exc_unexpected_error;
2183 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2184 RAISE fnd_api.g_exc_error;
2185 END IF;
2186
2187 END IF;
2188
2189 END IF;
2190
2191 l_progress := '290';
2192
2193 EXCEPTION
2194 WHEN fnd_api.g_exc_error THEN
2195 x_return_status := fnd_api.g_ret_sts_error;
2196 IF (x_msg_count IS NULL AND x_msg_data IS NULL) THEN
2197 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2198 END IF;
2199 IF (l_debug = 1) THEN
2200 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2201 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_error: ' || SQLERRM);
2202 END IF;
2203 WHEN OTHERS THEN
2204 x_return_status := fnd_api.g_ret_sts_unexp_error;
2205 IF (x_msg_count IS NULL AND x_msg_data IS NULL) THEN
2206 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2207 END IF;
2208 IF (l_debug = 1) THEN
2209 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2210 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_unexpected_error: ' || SQLERRM);
2211 END IF;
2212
2213 END backorder_pending_tasks;
2214
2215 /*
2216
2217 This procedure will be processing the cyc count request.
2218
2219 */
2220 PROCEDURE process_entry
2221 (p_cycle_count_header_id IN NUMBER ,
2222 p_organization_id IN NUMBER ,
2223 p_subinventory IN VARCHAR2 ,
2224 p_locator_id IN NUMBER := NULL ,
2225 p_parent_lpn_id IN NUMBER := NULL ,
2226 p_inventory_item_id IN NUMBER ,
2227 p_revision IN VARCHAR2 := NULL ,
2228 p_lot_number IN VARCHAR2 := NULL ,
2229 p_from_serial_number IN VARCHAR2 := NULL ,
2230 p_to_serial_number IN VARCHAR2 := NULL ,
2231 p_sys_quantity IN NUMBER ,
2232 p_count_quantity IN NUMBER ,
2233 p_count_uom IN VARCHAR2 ,
2234 p_unscheduled_count_entry IN NUMBER ,
2235 p_user_id IN NUMBER ,
2236 p_cost_group_id IN NUMBER := NULL ,
2237 p_secondary_uom IN VARCHAR2 := NULL ,
2238 p_secondary_qty IN NUMBER := NULL
2239 )
2240 IS
2241 l_api_name CONSTANT VARCHAR2(30) := 'process_entry';
2242 l_api_version CONSTANT NUMBER := 1.0;
2243 l_sys_quantity NUMBER := p_sys_quantity;
2244 l_return_status VARCHAR2(1):= fnd_api.G_RET_STS_SUCCESS;
2245 l_progress VARCHAR2(500) := 'Entered API';
2246 l_msg_count NUMBER;
2247 l_msg_data VARCHAR2(4000);
2248 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
2249
2250 BEGIN
2251
2252 IF ( l_debug = 1 ) THEN
2253 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
2254 Mdebug ( l_api_name||' : p_cycle_count_header_id = ' || p_cycle_count_header_id , g_message);
2255 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
2256 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
2257 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
2258 Mdebug ( l_api_name||' : p_parent_lpn_id = ' || p_parent_lpn_id , g_message);
2262 Mdebug ( l_api_name||' : p_from_serial_number = ' || p_from_serial_number , g_message);
2259 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
2260 Mdebug ( l_api_name||' : p_revision = ' || p_revision , g_message);
2261 Mdebug ( l_api_name||' : p_lot_number = ' || p_lot_number , g_message);
2263 Mdebug ( l_api_name||' : p_to_serial_number = ' || p_to_serial_number , g_message);
2264 Mdebug ( l_api_name||' : p_sys_quantity = ' || p_sys_quantity , g_message);
2265 Mdebug ( l_api_name||' : p_count_quantity = ' || p_count_quantity , g_message);
2266 Mdebug ( l_api_name||' : p_count_uom = ' || p_count_uom , g_message);
2267 Mdebug ( l_api_name||' : p_user_id = ' || p_user_id , g_message);
2268 Mdebug ( l_api_name||' : p_cost_group_id = ' || p_cost_group_id , g_message);
2269 Mdebug ( l_api_name||' : p_secondary_uom = ' || p_secondary_uom , g_message);
2270 Mdebug ( l_api_name||' : p_secondary_qty = ' || p_secondary_qty , g_message);
2271 END IF;
2272
2273 g_cycle_count_header_id := p_cycle_count_header_id;
2274
2275 IF (l_debug = 1) THEN
2276 mdebug (l_api_name||' : g_cycle_count_header_id : ' || g_cycle_count_header_id , g_message);
2277 END IF;
2278
2279 l_progress := 'Validate Organization';
2280
2281 IF (NOT inv_cache.Set_org_rec(p_organization_id => p_organization_id)) THEN
2282
2283 IF (l_debug = 1) THEN
2284 mdebug(l_api_name||' : '||p_organization_id||' is an invalid organization id',g_error);
2285 END IF;
2286
2287 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ORG');
2288 fnd_msg_pub.ADD;
2289 RAISE fnd_api.g_exc_error;
2290
2291 END IF;
2292
2293 l_progress := 'After Validating Organization';
2294
2295 l_progress := 'Validate Item';
2296
2297 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
2298
2299 IF (l_debug = 1) THEN
2300 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
2301 END IF;
2302
2303 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2304 fnd_msg_pub.ADD;
2305 RAISE fnd_api.g_exc_error;
2306
2307 END IF;
2308
2309 -- backorder pending tasks if needed.
2310
2311 backorder_pending_tasks
2312 ( p_organization_id => p_organization_id
2313 , p_subinventory => p_subinventory
2314 , p_locator_id => p_locator_id
2315 , p_parent_lpn_id => p_parent_lpn_id
2316 , p_inventory_item_id => p_inventory_item_id
2317 , p_revision => p_revision
2318 , p_lot_number => p_lot_number
2319 , p_from_serial_number => p_from_serial_number
2320 , p_to_serial_number => p_to_serial_number
2321 , p_count_quantity => p_count_quantity
2322 , p_count_uom => p_count_uom
2323 , p_user_id => p_user_id
2324 , p_cost_group_id => p_cost_group_id
2325 , p_secondary_uom => p_secondary_uom
2326 , p_secondary_qty => p_secondary_qty
2327 , x_return_status => l_return_status
2328 , x_msg_count => l_msg_count
2329 , x_msg_data => l_msg_data
2330 );
2331
2332 l_progress := 'After backorder_pending_tasks';
2333
2334 IF (l_debug = 1) THEN
2335 mdebug (l_api_name||' : x_return_status of backorder_pending_tasks : ' || l_return_status , g_message);
2336 END IF;
2337
2338 IF l_return_status <> fnd_api.g_ret_sts_unexp_error AND l_return_status <> fnd_api.g_ret_sts_error THEN
2339
2340 l_progress := 'INV_CYC_LOVS.process_entry';
2341
2342 INV_CYC_LOVS.process_entry
2343 ( p_cycle_count_header_id => p_cycle_count_header_id
2344 , p_organization_id => p_organization_id
2345 , p_subinventory => p_subinventory
2346 , p_locator_id => p_locator_id
2347 , p_parent_lpn_id => p_parent_lpn_id
2348 , p_inventory_item_id => p_inventory_item_id
2349 , p_revision => p_revision
2350 , p_lot_number => p_lot_number
2351 , p_from_serial_number => p_from_serial_number
2352 , p_to_serial_number => p_to_serial_number
2353 , p_count_quantity => p_count_quantity
2354 , p_count_uom => p_count_uom
2355 , p_unscheduled_count_entry => p_unscheduled_count_entry
2356 , p_user_id => p_user_id
2357 , p_cost_group_id => p_cost_group_id
2358 , p_secondary_uom => p_secondary_uom
2359 , p_secondary_qty => p_secondary_qty
2360 );
2361
2362 l_progress := 'delete_existing_cyc_count';
2363
2364
2365 -- Delete existing cyc count tasks.
2366
2367 delete_existing_cyc_count
2368 (p_organization_id => p_organization_id
2369 , p_subinventory => p_subinventory
2370 , p_locator_id => p_locator_id
2371 , p_inventory_item_id => p_inventory_item_id);
2372
2373 l_progress := 'After delete_existing_cyc_count';
2374
2375
2376 END IF;
2377
2378 EXCEPTION
2379 WHEN fnd_api.g_exc_error THEN
2380 IF (l_debug = 1) THEN
2381 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2385 IF (l_debug = 1) THEN
2382 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_error: ' || SQLERRM);
2383 END IF;
2384 WHEN OTHERS THEN
2386 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2387 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_unexpected_error: ' || SQLERRM);
2388 END IF;
2389
2390 END process_entry;
2391
2392 /*
2393
2394 This procedure will be processing the summary cyc count request .
2395
2396 */
2397 PROCEDURE process_summary
2398 (p_cycle_count_header_id IN NUMBER ,
2399 p_organization_id IN NUMBER ,
2400 p_subinventory IN VARCHAR2 ,
2401 p_locator_id IN NUMBER := NULL ,
2402 p_parent_lpn_id IN NUMBER := NULL ,
2403 p_inventory_item_id IN NUMBER ,
2404 p_unscheduled_count_entry IN NUMBER ,
2405 p_user_id IN NUMBER
2406 )
2407 IS
2408 l_api_name CONSTANT VARCHAR2(30) := 'process_summary';
2409 l_api_version CONSTANT NUMBER := 1.0;
2410 l_return_status VARCHAR2(1):= fnd_api.G_RET_STS_SUCCESS;
2411 l_progress VARCHAR2(500) := 'Entered API';
2412 l_msg_count NUMBER;
2413 l_msg_data VARCHAR2(4000);
2414 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
2415 l_alloc_qty NUMBER;
2416 l_alloc_cur t_genref;
2417 l_det_alloc_qty NUMBER;
2418 l_det_alloc_cur t_genref;
2419 l_sys_tot_qty NUMBER:=0;
2420 l_lpn_subinv VARCHAR2(80);
2421 l_lpn_locator_id NUMBER;
2422 l_lpn_context NUMBER;
2423
2424 BEGIN
2425
2426 IF ( l_debug = 1 ) THEN
2427 Mdebug ( l_api_name||' : Entered api ' || l_api_name , g_message);
2428 Mdebug ( l_api_name||' : p_cycle_count_header_id = ' || p_cycle_count_header_id , g_message);
2429 Mdebug ( l_api_name||' : p_organization_id = ' || p_organization_id , g_message);
2430 Mdebug ( l_api_name||' : p_subinventory = ' || p_subinventory , g_message);
2431 Mdebug ( l_api_name||' : p_locator_id = ' || p_locator_id , g_message);
2432 Mdebug ( l_api_name||' : p_parent_lpn_id = ' || p_parent_lpn_id , g_message);
2433 Mdebug ( l_api_name||' : p_inventory_item_id = ' || p_inventory_item_id , g_message);
2434 Mdebug ( l_api_name||' : p_user_id = ' || p_user_id , g_message);
2435 Mdebug ( l_api_name||' : p_unscheduled_count_entry = ' || p_unscheduled_count_entry , g_message);
2436 END IF;
2437
2438 l_progress := 'Validate Organization';
2439
2440 IF (NOT inv_cache.Set_org_rec(p_organization_id => p_organization_id)) THEN
2441
2442 IF (l_debug = 1) THEN
2443 mdebug(l_api_name||' : '||p_organization_id||' is an invalid organization id',g_error);
2444 END IF;
2445
2446 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ORG');
2447 fnd_msg_pub.ADD;
2448 RAISE fnd_api.g_exc_error;
2449
2450 END IF;
2451
2452 l_progress := 'After Validating Organization';
2453
2454 l_progress := 'Validate Item';
2455
2456 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
2457
2458 IF (l_debug = 1) THEN
2459 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
2460 END IF;
2461
2462 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2463 fnd_msg_pub.ADD;
2464 RAISE fnd_api.g_exc_error;
2465
2466 END IF;
2467
2468 IF ( p_parent_lpn_id IS NOT NULL ) THEN
2469
2470 SELECT NVL ( subinventory_code, '###' ),
2471 NVL ( locator_id, -99 ),
2472 lpn_context
2473 INTO l_lpn_subinv,
2474 l_lpn_locator_id,
2475 l_lpn_context
2476 FROM WMS_LICENSE_PLATE_NUMBERS
2477 WHERE lpn_id = p_parent_lpn_id ;
2478
2479 l_progress := '30';
2480
2481 IF ( l_debug = 1 ) THEN
2482 Mdebug ( l_api_name||' : l_lpn_subinv: ===> ' || l_lpn_subinv , g_message);
2483 Mdebug ( l_api_name||' : l_lpn_locator_id: => ' || l_lpn_locator_id , g_message);
2484 Mdebug ( l_api_name||' : l_lpn_context: => ' || l_lpn_context , g_message);
2485 END IF;
2486
2487 IF (l_lpn_context = 8 or l_lpn_context = 9 or l_lpn_context = 4 or l_lpn_context = 6) THEN
2488 IF ( l_debug = 1 ) THEN
2489 Mdebug ( l_api_name||' : Returning as lpn is not in inventory' , g_message);
2490 END IF;
2491
2492 RETURN;
2493 ELSIF (p_subinventory<>l_lpn_subinv OR p_locator_id<>l_lpn_locator_id) THEN
2494
2495 IF ( l_debug = 1 ) THEN
2496 Mdebug ( l_api_name||' : LPN is in different subinv. So backorder all lpn allocations.' , g_message);
2497 END IF;
2498
2499 l_progress := '70';
2500
2501 FOR lpn_contents_cur IN (SELECT DISTINCT wlc.inventory_item_id, wlc.lot_number, wlc.revision, DECODE(NVL(msn.serial_number, 'XXXX'), 'XXXX', wlc.primary_quantity, 1) primary_quantity, msn.serial_number
2502 FROM wms_lpn_contents wlc, mtl_serial_numbers msn
2503 WHERE wlc.parent_lpn_id=p_parent_lpn_id
2504 AND wlc.inventory_item_id = msn.inventory_item_id (+)
2505 AND ( msn.inventory_item_id IS NULL
2506 OR (msn.current_organization_id = p_organization_id
2507 AND msn.lpn_id=wlc.parent_lpn_id)))
2508 LOOP
2509
2510 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
2511
2512 IF (l_debug = 1) THEN
2513 mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
2514 END IF;
2515
2516 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2517 fnd_msg_pub.ADD;
2518 RAISE fnd_api.g_exc_error;
2519
2520 END IF;
2521
2522 l_progress := '80';
2523
2524 get_allocated_qty
2525 ( p_organization_id => p_organization_id
2526 , p_subinventory => l_lpn_subinv
2527 , p_locator_id => l_lpn_locator_id
2528 , p_parent_lpn_id => p_parent_lpn_id
2529 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
2530 , p_revision => lpn_contents_cur.revision
2531 , p_lot_number => lpn_contents_cur.lot_number
2532 , p_from_serial_number => lpn_contents_cur.serial_number
2533 , p_to_serial_number => lpn_contents_cur.serial_number
2534 , x_allocated_qty => l_det_alloc_qty
2535 , x_alloc_cur => l_det_alloc_cur
2536 );
2537
2538 IF ( l_debug = 1 ) THEN
2539 Mdebug ( l_api_name||' : ***l_det_alloc_qty*** '||l_det_alloc_qty , g_message);
2540 END IF;
2541
2542 l_progress := '100';
2543
2544 process_backorder
2545 ( p_count_qty => 0
2546 , p_alloc_cur => l_det_alloc_cur
2547 , p_user_id => p_user_id
2548 , p_allocated_qty => l_det_alloc_qty
2549 , x_return_status => l_return_status
2550 , x_msg_count => l_msg_count
2551 , x_msg_data => l_msg_data
2552 );
2553
2554 IF (l_debug = 1) THEN
2555 mdebug (l_api_name||' : x_return_status for process_backorder : ' || l_return_status , g_message);
2556 END IF;
2557
2558 l_progress := '110';
2559
2560 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2561 RAISE fnd_api.g_exc_unexpected_error;
2562 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2563 RAISE fnd_api.g_exc_error;
2564 END IF;
2565
2566
2567 IF ( Nvl(inv_cache.org_rec.ALLOCATE_SERIAL_FLAG, 'N')='Y' AND lpn_contents_cur.serial_number IS NOT NULL) THEN
2568
2569 l_progress := '120';
2570
2571 get_serial_allocated_qty
2572 ( p_organization_id => p_organization_id
2573 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
2574 , p_from_serial_number => lpn_contents_cur.serial_number
2575 , p_to_serial_number => lpn_contents_cur.serial_number
2576 , x_det_allocated_qty => l_det_alloc_qty
2577 , x_det_alloc_cur => l_det_alloc_cur
2578 );
2579
2580 IF ( l_debug = 1 ) THEN
2581 Mdebug ( l_api_name||' : ***l_ser_alloc_qty*** '||l_det_alloc_qty , g_message);
2582 END IF;
2583
2584 l_progress := '130';
2585
2586 process_backorder
2587 ( p_count_qty => 0
2588 , p_alloc_cur => l_det_alloc_cur
2589 , p_user_id => p_user_id
2590 , p_allocated_qty => l_det_alloc_qty
2591 , x_return_status => l_return_status
2592 , x_msg_count => l_msg_count
2593 , x_msg_data => l_msg_data
2594 );
2595
2596 IF (l_debug = 1) THEN
2597 mdebug (l_api_name||' : l_return_status for process_backorder : ' || l_return_status , g_message);
2598 END IF;
2599
2600 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2601 RAISE fnd_api.g_exc_unexpected_error;
2602 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2603 RAISE fnd_api.g_exc_error;
2604 END IF;
2605
2606 END IF;
2607
2608 END LOOP;
2609
2610 FOR lpn_contents_cur IN (SELECT wlc.inventory_item_id, wlc.lot_number, wlc.revision, Sum(wlc.primary_quantity) primary_quantity
2611 FROM wms_lpn_contents wlc
2612 WHERE wlc.parent_lpn_id=p_parent_lpn_id
2613 GROUP BY wlc.inventory_item_id, wlc.lot_number, wlc.revision)
2614 LOOP
2615
2616 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
2617
2618 IF (l_debug = 1) THEN
2619 mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
2620 END IF;
2621
2622 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2623 fnd_msg_pub.ADD;
2624 RAISE fnd_api.g_exc_error;
2625
2626 END IF;
2627
2628 l_progress := '131';
2629
2630 get_locator_quantity
2631 ( p_organization_id => p_organization_id
2632 , p_subinventory => l_lpn_subinv
2633 , p_locator_id => l_lpn_locator_id
2634 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
2635 , p_revision => lpn_contents_cur.revision
2636 , p_lot_number => lpn_contents_cur.lot_number
2637 , x_system_quantity => l_sys_tot_qty
2638 );
2639
2640 IF ( l_debug = 1 ) THEN
2641 Mdebug ( l_api_name||' : System qty at the locator = ' || l_sys_tot_qty , g_message);
2642 END IF;
2643
2644 l_progress := '140';
2645
2646 get_allocated_qty
2647 ( p_organization_id => p_organization_id
2648 , p_subinventory => l_lpn_subinv
2649 , p_locator_id => l_lpn_locator_id
2650 , p_inventory_item_id => lpn_contents_cur.inventory_item_id
2651 , p_revision => lpn_contents_cur.revision
2652 , p_lot_number => lpn_contents_cur.lot_number
2653 , x_allocated_qty => l_alloc_qty
2654 , x_alloc_cur => l_alloc_cur
2655 );
2656
2657 IF ( l_debug = 1 ) THEN
2658 Mdebug ( l_api_name||' : ***l_alloc_qty*** '||l_alloc_qty , g_message);
2659 END IF;
2660
2661 IF (l_alloc_qty>(l_sys_tot_qty-(lpn_contents_cur.primary_quantity))) THEN
2662
2663 l_progress := '160';
2664
2665 process_backorder
2666 ( p_count_qty => (l_sys_tot_qty-(lpn_contents_cur.primary_quantity))
2667 , p_alloc_cur => l_alloc_cur
2668 , p_user_id => p_user_id
2669 , p_allocated_qty => l_alloc_qty
2670 , x_return_status => l_return_status
2671 , x_msg_count => l_msg_count
2672 , x_msg_data => l_msg_data
2673 );
2674
2675 IF (l_debug = 1) THEN
2676 mdebug (l_api_name||' : l_return_status for process_backorder : ' || l_return_status , g_message);
2677 END IF;
2678
2679 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2680 RAISE fnd_api.g_exc_unexpected_error;
2681 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2682 RAISE fnd_api.g_exc_error;
2683 END IF;
2684
2685 END IF;
2686 END LOOP;
2687
2688 l_progress := '170';
2689
2690 IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => p_inventory_item_id)) THEN
2691
2692 IF (l_debug = 1) THEN
2693 mdebug(l_api_name||' : '||p_inventory_item_id||' is an invalid item',g_error);
2694 END IF;
2695
2696 fnd_message.Set_name('WMS','WMS_CONT_INVALID_ITEM');
2697 fnd_msg_pub.ADD;
2698 RAISE fnd_api.g_exc_error;
2699
2700 END IF;
2701
2702 l_progress := '180';
2703
2704 END IF;
2705
2706 END IF;
2707
2708 l_progress := 'INV_CYC_LOVS.process_summary';
2709
2710 INV_CYC_LOVS.process_summary
2711 ( p_cycle_count_header_id => p_cycle_count_header_id
2712 , p_organization_id => p_organization_id
2713 , p_subinventory => p_subinventory
2714 , p_locator_id => p_locator_id
2715 , p_parent_lpn_id => p_parent_lpn_id
2716 , p_unscheduled_count_entry => p_unscheduled_count_entry
2717 , p_user_id => p_user_id
2718 );
2719
2720 l_progress := 'delete_existing_cyc_count';
2721
2722
2723 -- Delete existing cyc count tasks.
2724
2725 delete_existing_cyc_count
2726 (p_organization_id => p_organization_id
2727 , p_subinventory => p_subinventory
2728 , p_locator_id => p_locator_id
2729 , p_inventory_item_id => p_inventory_item_id);
2730
2731 l_progress := 'After delete_existing_cyc_count';
2732
2733
2734 EXCEPTION
2735 WHEN fnd_api.g_exc_error THEN
2736 IF (l_debug = 1) THEN
2737 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2738 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_error: ' || SQLERRM);
2739 END IF;
2740 WHEN OTHERS THEN
2741 IF (l_debug = 1) THEN
2742 Mdebug(l_api_name||' : l_progress is ' || l_progress);
2743 Mdebug(l_api_name||' : RAISE fnd_api.g_exc_unexpected_error: ' || SQLERRM);
2744 END IF;
2745
2746 END process_summary;
2747
2748 END wms_opp_cyc_count;