1 package body wms_parameter_ext as
2 /* $Header: WMSGPHLB.pls 120.0.12020000.2 2012/07/04 06:34:19 abasheer ship $ */
3
4 -- ---------------------------------------------------------------------------------------
5 -- |---------------------< trace >--------------------------------------------------------|
6 -- ---------------------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- Wrapper around the tracing utility.
11 --
12 -- Prerequisites:
13 -- None
14 --
15 -- In Parameters:
16 -- Name Reqd Type Description
17 -- --------- ---- -------- ---------------------------------------
18 -- p_message Yes varchar2 Message to be displayed in the log file.
19 -- p_prompt Yes varchar2 Prompt.
20 -- p_level No number Level.
21 --
22 -- Post Success:
23 -- None.
24 --
25 -- Post Failure:
26 -- None
27 --
28 -- Access Status:
29 -- Internal Development Use Only.
30 --
31 -- {End Of Comments}
32 --
33 --
34 Procedure trace(
35 p_message in varchar2
36 , p_level in number
37 ) is
38 begin
39 INV_LOG_UTIL.trace(p_message, 'WMS_PARAMETER_EXT', p_level);
40 end trace;
41 --
42 -- ---------------------------------------------------------------------------------------
43 -- |---------------------< GetPOHeaderLineIDWrap >----------------------------------------|
44 -- ---------------------------------------------------------------------------------------
45 -- {Start Of Comments}
46 --
47 -- Description:
48 -- Wrapper around the wms_parameter_pvt.GetSOHeaderLineID(). This is a wrapper around the
49 -- wms_parameter_pvt.GetSOHeaderLineID().
50 --
51 -- Prerequisites:
52 -- None
53 --
54 -- In Parameters:
55 -- Name Reqd Type Description
56 -- -------------------- ---- -------- ---------------------------------------
57 -- p_transaction_id Yes number Task ID(MMTT.transaction_temp_id)
58 -- p_header_flag No varchar2 Flag to indicate that the call to this
59 -- function is to derive the SO Header ID
60 -- p_line_flag No varchar2 Flag to indicate that the call to this
61 -- function is to derive the SO Line ID
62 -- location ID.
63 -- Post Success:
64 -- None.
65 --
66 -- Post Failure:
67 -- None
68 --
69 -- Access Status:
70 -- Internal Development Use Only.
71 --
72 -- {End Of Comments}
73 --
74 Function GetSOHeaderLineIDWrap(
75 p_transaction_id in number
76 , p_header_flag in varchar2 default 'N'
77 , p_line_flag in varchar2 default 'N'
78 ) return number
79 is
80
81 l_proc varchar2(72) := 'GetSOHeaderLineIDWrap :';
82 l_so_header_id number := -1;
83 l_so_line_id number := -1;
84 l_return_val_wrap number := -1;
85
86 l_reference varchar2(50);
87 l_reference_id number;
88 l_move_order_line_id number;
89 l_transaction_source_type_id number;
90 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
91
92 cursor get_mo_line_info is
93 select mtrl.reference, mtrl.reference_id, mtrl.line_id, mmtt.transaction_source_type_id
94 from mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
95 where mtrl.line_id = mmtt.move_order_line_id
96 and mmtt.transaction_temp_id = p_transaction_id;
97
98 begin
99 -- ### Initialize API return status to success
100 --x_return_status := fnd_api.g_ret_sts_success;
101
102 if (l_debug = 1) then
103 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
104 trace(l_proc || ' p_transaction_id => ' || nvl(p_transaction_id, -99));
105 trace(l_proc || ' p_header_flag => ' || p_header_flag);
106 trace(l_proc || ' p_line_flag => ' || p_line_flag);
107 end if;
108
109 -- Validate input parameters
110 --if p_transaction_id is null then
111 -- if (l_debug = 1) then
112 -- trace(l_proc || ' Missing Required Input Parameter values, refer to trace messages above...');
113 -- end if;
114 -- return null;
115 --end if;
116
117 -- ### Derive Move Order Line and related task Information details to start with.
118 open get_mo_line_info;
119 fetch get_mo_line_info
120 into l_reference, l_reference_id, l_move_order_line_id, l_transaction_source_type_id;
121
122 if get_mo_line_info%NOTFOUND then
123 fnd_message.set_name('WMS', 'WMS_INVALID_TASK_ID');
124 fnd_msg_pub.ADD;
125 raise fnd_api.g_exc_error;
126 else
127 -- ### Print values derived from the cursor into the log file.
128 if (l_debug = 1) then
129 trace(l_proc || ' Printing Move Order Line and related task Information details...');
130 trace(l_proc || ' l_reference : '|| nvl(l_reference, '@@@'));
131 trace(l_proc || ' l_reference_id : '|| nvl(l_reference_id, -99));
132 trace(l_proc || ' l_move_order_line_id : '|| nvl(l_move_order_line_id, -99));
133 trace(l_proc || ' l_transaction_source_type_id : '|| nvl(l_transaction_source_type_id, -99));
134 end if;
135
136 if (p_header_flag = 'Y' or p_line_flag = 'Y') then
137 if (l_debug = 1) then
138 trace(l_proc || ' Within if (p_header_flag = Y or p_line_flag = Y) condition');
139 end if;
140
141 l_return_val_wrap := wms_parameter_pvt.getsoheaderlineid(
142 p_line_id => l_move_order_line_id
143 , p_transaction_source_type_id => l_transaction_source_type_id
144 , p_reference => l_reference
145 , p_reference_id => l_reference_id
146 , p_header_flag => p_header_flag
147 , p_line_flag => p_line_flag
148 );
149 end if;
150
151 if p_header_flag = 'Y' then
152 if (l_debug = 1) then
153 trace(l_proc || ' SO Header ID Derived : '|| nvl(l_return_val_wrap, -99));
154 end if;
155 elsif p_line_flag = 'Y' then
156 if (l_debug = 1) then
157 trace(l_proc || ' SO Line ID Derived : '|| nvl(l_return_val_wrap, -99));
158 end if;
159 end if;
160
161 end if;
162 -- ### Close the cursor.
163 close get_mo_line_info;
164
165 return l_return_val_wrap;
166
167 exception
168 when fnd_api.g_exc_error then
169 --x_return_status := fnd_api.g_ret_sts_error;
170 null;
171
172 when others then
173 --x_return_status := fnd_api.g_ret_sts_error;
174 if (l_debug = 1) then
175 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
176 end if;
177 if get_mo_line_info%ISOPEN then
178 close get_mo_line_info;
179 end if;
180
181 end GetSOHeaderLineIDWrap;
182
183 -- ---------------------------------------------------------------------------------------
184 -- |---------------------< GetPOHeaderLineIDWrap >----------------------------------------|
185 -- ---------------------------------------------------------------------------------------
186 -- {Start Of Comments}
187 --
188 -- Description:
189 -- Wrapper around the wms_parameter_pvt.GetPOHeaderLineID(). If the p_line_location_flag is
190 -- 'Y', then the po_line_location_id is derved within this function. In all other casess
191 -- the wms_parameter_pvt.GetPOHeaderLineID() is called to derive th appropraite values.
192 --
193 -- Prerequisites:
194 -- None
195 --
196 -- In Parameters:
197 -- Name Reqd Type Description
198 -- -------------------- ---- -------- ---------------------------------------
199 -- p_transaction_id Yes number Task ID(MMTT.transaction_temp_id)
200 -- p_header_flag No varchar2 Flag to indicate that the call to this
201 -- function is to derive the PO Header ID
202 -- p_line_flag No varchar2 Flag to indicate that the call to this
203 -- function is to derive the PO Line ID
204 -- p_line_location_flag No varchar2 Flag to indicate that the call to this
205 -- function is to derive the PO Line
206 -- location ID.
207 -- Post Success:
208 -- None.
209 --
210 -- Post Failure:
211 -- None
212 --
213 -- Access Status:
214 -- Internal Development Use Only.
215 --
216 -- {End Of Comments}
217 --
218 Function GetPOHeaderLineIDWrap(
219 p_transaction_id in number
220 , p_header_flag in varchar2
221 , p_line_flag in varchar2
222 , p_line_location_flag in varchar2
223 ) return number
224 is
225
226 l_proc varchar2(72) := 'GetPOHeaderLineIDWrap :';
227 l_po_header_id number := -1;
228 l_po_line_id number := -1;
229 l_po_line_location_id number := -1;
230 l_return_val_wrap number := -1;
231
232 l_reference varchar2(50);
233 l_reference_id number;
234 l_move_order_line_id number;
235 l_transaction_source_type_id number;
236 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
237
238 cursor get_mo_line_info is
239 select mtrl.reference, mtrl.reference_id, mtrl.line_id, mmtt.transaction_source_type_id
240 from mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
241 where mtrl.line_id = mmtt.move_order_line_id
242 and mmtt.transaction_temp_id = p_transaction_id;
243
244 begin
245 -- ### Initialize API return status to success
246 --x_return_status := fnd_api.g_ret_sts_success;
247
248 if (l_debug = 1) then
249 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
250 trace(l_proc || ' p_transaction_id => ' || nvl(p_transaction_id, -99));
251 trace(l_proc || ' p_header_flag => ' || p_header_flag);
252 trace(l_proc || ' p_line_flag => ' || p_line_flag);
253 trace(l_proc || ' p_line_location_flag => ' || p_line_location_flag);
254 end if;
255
256 -- Validate input parameters
257 --if p_transaction_id is null then
258 -- if (l_debug = 1) then
259 -- trace(l_proc || ' Missing Required Input Parameter values, refer to trace messages above...');
260 -- end if;
261 -- return null;
262 --end if;
263
264 -- ### Derive Move Order Line and related task Information details to start with.
265 open get_mo_line_info;
266 fetch get_mo_line_info
267 into l_reference, l_reference_id, l_move_order_line_id, l_transaction_source_type_id;
268
269 if get_mo_line_info%NOTFOUND then
270 fnd_message.set_name('WMS', 'WMS_INVALID_TASK_ID');
271 fnd_msg_pub.ADD;
272 raise fnd_api.g_exc_error;
273 else
274 -- ### Print values derived from the cursor into the log file.
275 if (l_debug = 1) then
276 trace(l_proc || ' Printing Move Order Line and related task Information details...');
277 trace(l_proc || ' l_reference : '|| nvl(l_reference, '@@@'));
278 trace(l_proc || ' l_reference_id : '|| nvl(l_reference_id, -99));
279 trace(l_proc || ' l_move_order_line_id : '|| nvl(l_move_order_line_id, -99));
280 trace(l_proc || ' l_transaction_source_type_id : '|| nvl(l_transaction_source_type_id, -99));
281 end if;
282
283 if (p_line_location_flag = 'Y') then
284 if (l_reference = 'PO_LINE_LOCATION_ID') then
285 if (l_debug = 1) then
286 trace(l_proc || ' Within if (p_line_location_flag = Y) condition');
287 trace(l_proc || ' po_line_location_id derived : '|| nvl(l_reference_id, -99));
288 end if;
289 l_return_val_wrap := l_reference_id;
290 end if;
291 elsif (p_header_flag = 'Y' or p_line_flag = 'Y') then
292 if (l_debug = 1) then
293 trace(l_proc || ' Within if (p_header_flag = Y or p_line_flag = Y) condition');
294 end if;
295
296 l_return_val_wrap := wms_parameter_pvt.getpoheaderlineid(
297 p_transaction_source_type_id => l_transaction_source_type_id
298 , p_reference => l_reference
299 , p_reference_id => l_reference_id
300 , p_header_flag => p_header_flag
301 , p_line_flag => p_line_flag
302 );
303 end if;
304
305 if p_header_flag = 'Y' then
306 if (l_debug = 1) then
307 trace(l_proc || ' PO Header ID Derived : '|| nvl(l_return_val_wrap, -99));
308 end if;
309 elsif p_line_flag = 'Y' then
310 if (l_debug = 1) then
311 trace(l_proc || ' PO Line ID Derived : '|| nvl(l_return_val_wrap, -99));
312 end if;
313 elsif p_line_location_flag = 'Y' then
314 if (l_debug = 1) then
315 trace(l_proc || ' PO Line Location ID Derived : '|| nvl(l_return_val_wrap, -99));
316 end if;
317 end if;
318
319 end if;
320 -- ### Close the cursor.
321 close get_mo_line_info;
322
323 return l_return_val_wrap;
324
325 exception
326 when fnd_api.g_exc_error then
327 --x_return_status := fnd_api.g_ret_sts_error;
328 null;
329
330 when others then
331 --x_return_status := fnd_api.g_ret_sts_error;
332 if (l_debug = 1) then
333 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
334 end if;
335 if get_mo_line_info%ISOPEN then
336 close get_mo_line_info;
337 end if;
338
339 end GetPOHeaderLineIDWrap;
340
341 -- ---------------------------------------------------------------------------------------
342 -- |---------------------< GetItemOnhandWrap >----------------------------------------|
343 -- ---------------------------------------------------------------------------------------
344 -- {Start Of Comments}
345 --
346 -- Description:
347 -- Wrapper around the wms_parameter_pvt.GetItemOnhand().
348 --
349 -- Prerequisites:
350 -- None
351 --
352 -- In Parameters:
353 -- Name Reqd Type Description
354 -- -------------------- ---- -------- ---------------------------------------
355 -- p_organization_id Yes number Organization ID(MMTT.organization_id)
356 -- p_inventory_item_id Yes number Item ID(MMTT.inventory_item_id)
357 -- p_subinventory_code No varchar2 Subcode(nvl(MMTT.transfer_subinventory, MMTT.subinventory_code))
358 -- p_locator_id No number Location ID(MMTT.location_id)
359 -- p_transaction_uom No varchar2 Transaction UOM (MMTT.transaction_uom)
360 -- Post Success:
361 -- None.
362 --
363 -- Post Failure:
364 -- None
365 --
366 -- Access Status:
367 -- Internal Development Use Only.
368 --
369 -- {End Of Comments}
370 --
371 Function GetItemOnhandWrap(
372 p_organization_id in number default g_miss_num
373 , p_inventory_item_id in number default g_miss_num
374 , p_subinventory_code in varchar2 default g_miss_char
375 , p_locator_id in number default g_miss_num
376 , p_transaction_uom in varchar2 default g_miss_char
377 )return number
378 is
379 l_primary_uom_code varchar2(10);
380 l_proc varchar2(72) := 'GetItemOnhandWrap :';
381 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
382 l_return_val_wrap number := -1;
383
384 cursor get_item_primary_uom is
385 select primary_uom_code
386 from mtl_system_items msi
387 where msi.inventory_item_id = p_inventory_item_id
388 and msi.organization_id = p_organization_id;
389
390 begin
391 if (l_debug = 1) then
392 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
393 trace(l_proc || ' p_organization_id => ' || nvl(p_organization_id, -99));
394 trace(l_proc || ' p_inventory_item_id => ' || nvl(p_inventory_item_id, -99));
395 trace(l_proc || ' p_subinventory_code => ' || nvl(p_subinventory_code, '@@@'));
396 trace(l_proc || ' p_locator_id => ' || nvl(p_locator_id, -99));
397 trace(l_proc || ' p_transaction_uom => ' || nvl(p_transaction_uom, '@@@'));
398 end if;
399
400 --check for missing org, item.
401 if p_organization_id = g_miss_num or p_inventory_item_id = g_miss_num then
402 return null;
403 elsif p_organization_id is null or p_inventory_item_id is null then
404 return null;
405 end if;
406
407 -- ### Derive Move Order Line and related task Information details to start with.
408 open get_item_primary_uom;
409 fetch get_item_primary_uom
410 into l_primary_uom_code;
411
412 if get_item_primary_uom%NOTFOUND then
413 close get_item_primary_uom;
414 return null;
415 else
416 l_return_val_wrap := wms_parameter_pvt.getitemonhand(
417 p_organization_id => p_organization_id
418 , p_inventory_item_id => p_inventory_item_id
419 , p_subinventory_code => p_subinventory_code
420 , p_locator_id => p_locator_id
421 , p_primary_uom => l_primary_uom_code
422 , p_transaction_uom => p_transaction_uom
423 );
424 end if;
425 -- ### Close the cursor.
426 close get_item_primary_uom;
427
428 return l_return_val_wrap;
429 exception
430 when fnd_api.g_exc_error then
431 --x_return_status := fnd_api.g_ret_sts_error;
432 null;
433
434 when others then
435 --x_return_status := fnd_api.g_ret_sts_error;
436 if (l_debug = 1) then
437 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
438 end if;
439 if get_item_primary_uom%ISOPEN then
440 close get_item_primary_uom;
441 end if;
442
443 end GetItemOnhandWrap;
444 --
445 -- ---------------------------------------------------------------------------------------
446 -- |---------------------< GetNumOtherLotsWrap >----------------------------------------|
447 -- ---------------------------------------------------------------------------------------
448 -- {Start Of Comments}
449 --
450 -- Description:
451 -- Wrapper around the wms_parameter_pvt.GetNumOtherLots().
452 --
453 -- Prerequisites:
454 -- None
455 --
456 -- In Parameters:
457 -- Name Reqd Type Description
458 -- -------------------- ---- -------- ---------------------------------------
459 -- p_tranmsaction_id Yes number Task ID(MMTT.transaction_temp_id)
460 -- Post Success:
461 -- None.
462 --
463 -- Post Failure:
464 -- None
465 --
466 -- Access Status:
467 -- Internal Development Use Only.
468 --
469 -- {End Of Comments}
470 --
471 Function GetNumOtherLotsWrap(
472 p_transaction_id in number
473 ) return number
474 is
475 l_proc varchar2(72) := 'GetNumOtherLotsWrap :';
476 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
477 l_return_val_wrap number := -1;
478
479 l_organization_id number := -1;
480 l_inventory_item_id number := -1;
481 l_subinventory_code varchar2(100) := null;
482 l_locator_id number := -1;
483 l_lot_number varchar2(100) := null;
484
485 cursor get_lot_other_info is
486 select mmtt.organization_id, mmtt.inventory_item_id,
487 nvl(mmtt.transfer_subinventory, mmtt.subinventory_code),
488 nvl(mmtt.transfer_to_location, mmtt.locator_id),
489 mtlt.lot_number
490 from mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
491 where mmtt.transaction_temp_id = mtlt.transaction_temp_id
492 and mmtt.transaction_temp_id = p_transaction_id;
493
494 begin
495 if (l_debug = 1) then
496 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
497 trace(l_proc || ' p_transaction_id => ' || nvl(p_transaction_id, -99));
498 end if;
499
500 -- ### Derive Move Order Line and related task Information details to start with.
501 open get_lot_other_info;
502 fetch get_lot_other_info
503 into l_organization_id,l_inventory_item_id,l_subinventory_code,l_locator_id,l_lot_number ;
504
505 if get_lot_other_info%NOTFOUND then
506 close get_lot_other_info;
507 return null;
508 else
509 l_return_val_wrap := wms_parameter_pvt.GetNumOtherLots(
510 p_organization_id => l_organization_id
511 , p_inventory_item_id => l_inventory_item_id
512 , p_subinventory_code => l_subinventory_code
513 , p_locator_id => l_locator_id
514 , p_lot_number => l_lot_number
515 );
516 end if;
517 -- ### Close the cursor.
518 close get_lot_other_info;
519
520 return l_return_val_wrap;
521 exception
522 when fnd_api.g_exc_error then
523 --x_return_status := fnd_api.g_ret_sts_error;
524 null;
525
526 when others then
527 --x_return_status := fnd_api.g_ret_sts_error;
528 if (l_debug = 1) then
529 trace(' Error Code, Error Message...' || sqlerrm(sqlcode), 1);
530 end if;
531 if get_lot_other_info%ISOPEN then
532 close get_lot_other_info;
533 end if;
534
535 end GetNumOtherLotsWrap;
536
537 -- API name : GetLpnQuantityRevLot
538 -- Type : Private
539 -- Function : Returns quantity of the given item, revision, and lot in the given LPN
540 --
541 -- Data in WLC is generally in primary UOM. However this is because INV-TM
542 -- always converts to primary UOM before calling pack/unpack API. packUnpack
543 -- by itself does not make any assumption that material will always be in pri-uom.
544 -- packunpack API could be called directly (as is done in ASN import, due to which
545 -- we have this bug) by an API and a different UOM can be specified. So
546 -- you cannot make the assumption that WLC will always be in pri-uom.
547 -- Hence not in all cases, can we safely assume that the UOM CODE on the WLC record
548 -- is in the primary UOM of the item. So it is always best to convert if the
549 -- MMTT.TRANSACTION_UOM and the WLC.UOM_CODE doesn't match. .
550 function getlpnquantityrevlot(
551 p_lpn_id in number
552 , p_inventory_item_id in number
553 , p_revision in varchar2 default null
554 , p_lot_number in varchar2 default null
555 , p_organization_id in number)
556 return number
557 is
558 l_proc varchar2(72) := 'GetLpnQuantityRevLot :';
559 l_return_value number:= 0;
560 l_primary_uom varchar2(3);
561 l_total_quantity number:= 0;
562 l_loop_counter number:=0;
563 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
564
565
566 -- @@@ Get the primary uom for the Item in question.
567 cursor c_get_prim_uom_code is
568 select primary_uom_code
569 from mtl_system_items mtl
570 where mtl.inventory_item_id = p_inventory_item_id
571 and mtl.organization_id = p_organization_id;
572
573 -- @@@ Get the records in WLC in the prim uom of the item.
574 cursor c_get_wlc_quantity is
575 select sum(wlc.quantity) summed_quantity, uom_code
576 from wms_lpn_contents wlc
577 where wlc.parent_lpn_id = p_lpn_id
578 and wlc.inventory_item_id = p_inventory_item_id
579 and wlc.organization_id = p_organization_id
580 and nvl(wlc.revision, '@@@') = nvl(p_revision, nvl(wlc.revision, '@@@'))
581 and nvl(wlc.lot_number, '@@@@') = nvl(p_lot_number, nvl(wlc.lot_number, '@@@@'))
582 group by uom_code;
583
584 begin
585 if (l_debug = 1) then
586 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
587 trace(l_proc || ' p_lpn_id => ' || p_lpn_id);
588 trace(l_proc || ' p_inventory_item_id => ' || p_inventory_item_id);
589 trace(l_proc || ' p_organization_id => ' || p_organization_id);
590 trace(l_proc || ' p_revision => ' || p_revision);
591 trace(l_proc || ' p_lot_number => ' || p_lot_number);
592 end if;
593
594 if p_lpn_id is null or p_inventory_item_id is null then
595 return -1;
596 end if;
597
598 -- @@@ Open cursor to fetch primary UOM code for the item in question.
599 open c_get_prim_uom_code;
600 fetch c_get_prim_uom_code
601 into l_primary_uom;
602
603
604 if c_get_prim_uom_code%NOTFOUND then
605 close c_get_prim_uom_code;
606 return null;
607 else
608 trace(l_proc || 'l_primary_uom : ' || nvl(l_primary_uom, '@@@'));
609 for v_get_wlc_quantity in c_get_wlc_quantity
610 loop
611 l_loop_counter := l_loop_counter + 1;
612 trace(l_proc || 'l_loop_counter : ' || nvl(l_loop_counter, -99));
613 if v_get_wlc_quantity.uom_code <> l_primary_uom then
614 trace(l_proc || 'summed_quantity : ' || nvl(v_get_wlc_quantity.summed_quantity, -99));
615 trace(l_proc || 'uom_code : ' || nvl(v_get_wlc_quantity.uom_code, '@@@'));
616 l_return_value := inv_convert.inv_um_convert(
617 item_id => p_inventory_item_id
618 , lot_number => p_lot_number --BUG#8542061
619 , organization_id => p_organization_id --BUG#8542061
620 , precision => null
621 , from_quantity => v_get_wlc_quantity.summed_quantity
622 , from_unit => v_get_wlc_quantity.uom_code
623 , to_unit => l_primary_uom
624 , from_name => null
625 , to_name => null
626 );
627 -- @@@ The above call(INVUNCMB,pls) is code such that when the "When Others" exception occurs,
628 -- @@@ it sets the value of l_return_value to -9999. When this happens inside the loop,
629 -- @@@ we have to make sure that the value of l_total_quantity is unchanged. Hence the check around this.
630 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
631 if (l_return_value = -99999) then
632 l_total_quantity := 0;
633 trace(l_proc || 'l_total_quantity just before exit : ' || nvl(l_total_quantity, -99));
634 exit;
635 else
636 l_total_quantity := l_total_quantity + l_return_value;
637 trace(l_proc || 'l_total_quantity in the else of the l_return_value check : ' || nvl(l_total_quantity, -99));
638 end if;
639 else
640 l_total_quantity := l_total_quantity + v_get_wlc_quantity.summed_quantity;
641 trace(l_proc || 'l_total_quantity in the else : ' || nvl(l_return_value, -99));
642 end if;-- Marker for Check v_get_wlc_quantity.uom_code <> l_primary_uom
643 end loop;
644 -- @@@ Close the cursor since the loop has been exited prematurely.
645 if (l_return_value = -99999) then
646 if c_get_wlc_quantity%ISOPEN then
647 trace(l_proc || 'c_get_wlc_quantity is open and hence being closed ');
648 close c_get_wlc_quantity;
649 end if;
650 end if;
651 close c_get_prim_uom_code;
652 end if;-- Marker for c_get_prim_uom_code FOUND/NOTFOUND
653 return l_total_quantity;
654 exception
655 when others then
656 if c_get_prim_uom_code%ISOPEN then
657 close c_get_prim_uom_code;
658 end if;
659
660 if c_get_wlc_quantity%ISOPEN then
661 close c_get_wlc_quantity;
662 end if;
663 return 0;
664 end getlpnquantityrevlot;
665 --
666 --
667 -- API name : GetLpnTotalQuantity
668 -- Type : Private
669 -- Function : Returns quantity of the given item in the given LPN for the given item.
670 --
671 -- Data in WLC is generally in primary UOM. However this is because INV-TM
672 -- always converts to primary UOM before calling pack/unpack API. packUnpack
673 -- by itself does not make any assumption that material will always be in pri-uom.
674 -- packunpack API could be called directly (as is done in ASN import, due to which
675 -- we have this bug) by an API and a different UOM can be specified. So
676 -- you cannot make the assumption that WLC will always be in pri-uom.
677 -- Hence not in all cases, can we safely assume that the UOM CODE on the WLC record
678 -- is in the primary UOM of the item. So it is always best to convert if the
679 -- primary uom for the item and the WLC.UOM_CODE doesn't match. .
680 function getlpntotalquantity(
681 p_lpn_id in number
682 , p_inventory_item_id in number
683 , p_organization_id in number)
684 return number
685 is
686 l_proc varchar2(72) := 'GetLpnTotalQuantity :';
687 l_return_value number:= 0;
688 l_primary_uom varchar2(3);
689 l_total_quantity number:= 0;
690 l_loop_counter number := 0;
691 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
692
693 -- @@@ Get the primary uom for the Item in question.
694 cursor c_get_prim_uom_code is
695 select primary_uom_code
696 from mtl_system_items mtl
697 where mtl.inventory_item_id = p_inventory_item_id
698 and mtl.organization_id = p_organization_id;
699
700 -- @@@ Check to see if the record in WLC is in the prim uom of the item.
701 cursor c_get_wlc_quantity is
702 select sum(wlc.quantity) summed_quantity, uom_code
703 from wms_lpn_contents wlc
704 where wlc.parent_lpn_id = p_lpn_id
705 and wlc.inventory_item_id = p_inventory_item_id
706 and wlc.organization_id = p_organization_id
707 group by uom_code;
708
709 begin
710
711 if (l_debug = 1) then
712 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
713 trace(l_proc || ' p_lpn_id => ' || p_lpn_id);
714 trace(l_proc || ' p_inventory_item_id => ' || p_inventory_item_id);
715 trace(l_proc || ' p_organization_id => ' || p_organization_id);
716 end if;
717
718 -- @@@ Open cursor to fetch primary UOM code for the item in question.
719 open c_get_prim_uom_code;
720 fetch c_get_prim_uom_code
721 into l_primary_uom;
722
723
724 if c_get_prim_uom_code%NOTFOUND then
725 close c_get_prim_uom_code;
726 return null;
727 else
728 trace(l_proc || 'l_primary_uom : ' || nvl(l_primary_uom, '@@@'));
729 for v_get_wlc_quantity in c_get_wlc_quantity
730 loop
731 l_loop_counter := l_loop_counter + 1;
732 trace(l_proc || 'l_loop_counter : ' || nvl(l_loop_counter, -99));
733 if v_get_wlc_quantity.uom_code <> l_primary_uom then
734 trace(l_proc || 'summed_quantity : ' || nvl(v_get_wlc_quantity.summed_quantity, -99));
735 trace(l_proc || 'uom_code : ' || nvl(v_get_wlc_quantity.uom_code, '@@@'));
736 l_return_value := inv_convert.inv_um_convert(
737 item_id => p_inventory_item_id
738 , precision => null
739 , from_quantity => v_get_wlc_quantity.summed_quantity
740 , from_unit => v_get_wlc_quantity.uom_code
741 , to_unit => l_primary_uom
742 , from_name => null
743 , to_name => null
744 );
745 -- @@@ The above call(INVUNCMB,pls) is code such that when the "When Others" exception occurs,
746 -- @@@ it sets the value of l_return_value to -9999. When this happens inside the loop,
747 -- @@@ we have to make sure that the loop is exited and the l_total_quantity is 0.
748 -- @@@ Hence a check around this.
749 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
750 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
751 if (l_return_value = -99999) then
752 l_total_quantity := 0;
753 trace(l_proc || 'l_total_quantity just before exit : ' || nvl(l_total_quantity, -99));
754 exit;
755 else
756 l_total_quantity := l_total_quantity + l_return_value;
757 trace(l_proc || 'l_total_quantity in the else of the l_return_value check : ' || nvl(l_total_quantity, -99));
758 end if;
759 else
760 l_total_quantity := l_total_quantity + v_get_wlc_quantity.summed_quantity;
761 trace(l_proc || 'l_total_quantity in the else : ' || nvl(l_return_value, -99));
762 end if;
763 end loop;
764 -- @@@ Close the cursor since the loop has been exited prematurely.
765 if (l_return_value = -99999) then
766 if c_get_wlc_quantity%ISOPEN then
767 trace(l_proc || 'c_get_wlc_quantity is open and hence being closed ');
768 close c_get_wlc_quantity;
769 end if;
770 end if;
771 close c_get_prim_uom_code;
772 end if;
773 return l_total_quantity;
774 exception
775 when others then
776 if c_get_prim_uom_code%ISOPEN then
777 close c_get_prim_uom_code;
778 end if;
779
780 if c_get_wlc_quantity%ISOPEN then
781 close c_get_wlc_quantity;
782 end if;
783 return 0;
784 end getlpntotalquantity;
785 --
786 --
787 -- API name : GetLpnNumOfItems
788 -- Type : Private
789 -- Function : Returns number of items - 1 in the the given LPN.
790 -- This function considers the current item as well as all the other items.
791 function getlpnnumofitems(
792 p_lpn_id in number
793 , p_organization_id in number)
794 return number
795 is
796 l_return_value number;
797
798 begin
799 if p_lpn_id is null then
800 return -1;
801 end if;
802
803 select count(distinct(wlc.inventory_item_id))
804 into l_return_value
805 from wms_lpn_contents wlc
806 where wlc.parent_lpn_id = p_lpn_id
807 and wlc.organization_id = p_organization_id;
808
809 return l_return_value;
810 exception
811 when others then
812 return -1;
813 end getlpnnumofitems;
814
815 -- API name : GetLpnNumOtherRevs
816 -- Type : Private
817 -- Function : Returns number of revisions of this item in the given LPN
818 function getlpnnumofrevs(
819 p_lpn_id in number
820 , p_inventory_item_id in number
821 , p_organization_id in number)
822 return number
823 is
824 l_return_value NUMBER;
825
826 begin
827 if p_lpn_id is null or p_inventory_item_id is null then
828 return -1;
829 end if;
830
831 select count(distinct(wlc.revision))
832 into l_return_value
833 from wms_lpn_contents wlc
834 where wlc.parent_lpn_id = p_lpn_id
835 and wlc.inventory_item_id = p_inventory_item_id
836 and wlc.organization_id = p_organization_id;
837
838 return l_return_value;
839 exception
840 when others then
841 return -1;
842 end getlpnnumofrevs;
843
844 -- API name : GetLpnNumOtherLots
845 -- Type : Private
846 -- Function : Returns number of lots of this item - 1
847 -- in the the given LPN
848 function getlpnnumoflots(
849 p_lpn_id in number
850 , p_inventory_item_id in number
851 , p_organization_id in number)
852 return number
853 is
854 l_return_value number;
855
856 begin
857 if p_lpn_id is null or p_inventory_item_id is null then
858 return -1;
859 end if;
860
861 select count(distinct(wlc.lot_number))
862 into l_return_value
863 from wms_lpn_contents wlc
864 where wlc.parent_lpn_id = p_lpn_id
865 and wlc.inventory_item_id = p_inventory_item_id
866 and wlc.organization_id = p_organization_id;
867
868
869 return l_return_value;
870 exception
871 when others then
872 return -1;
873 end getlpnnumoflots;
874
875 end wms_parameter_ext;
[Home] [Help]