1 package body wms_parameter_ext as
2 /* $Header: WMSGPHLB.pls 115.5 2004/03/25 00:49:19 joabraha noship $ */
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));
398 end if;
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, '@@@'));
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
541 -- Data in WLC is generally in primary UOM. However this is because INV-TM
538 -- Type : Private
539 -- Function : Returns quantity of the given item, revision, and lot in the given LPN
540 --
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 , precision => null
619 , from_quantity => v_get_wlc_quantity.summed_quantity
620 , from_unit => v_get_wlc_quantity.uom_code
621 , to_unit => l_primary_uom
622 , from_name => null
623 , to_name => null
624 );
625 -- @@@ The above call(INVUNCMB,pls) is code such that when the "When Others" exception occurs,
626 -- @@@ it sets the value of l_return_value to -9999. When this happens inside the loop,
627 -- @@@ we have to make sure that the value of l_total_quantity is unchanged. Hence the check around this.
628 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
629 if (l_return_value = -99999) then
630 l_total_quantity := 0;
631 trace(l_proc || 'l_total_quantity just before exit : ' || nvl(l_total_quantity, -99));
632 exit;
633 else
634 l_total_quantity := l_total_quantity + l_return_value;
635 trace(l_proc || 'l_total_quantity in the else of the l_return_value check : ' || nvl(l_total_quantity, -99));
636 end if;
637 else
638 l_total_quantity := l_total_quantity + v_get_wlc_quantity.summed_quantity;
639 trace(l_proc || 'l_total_quantity in the else : ' || nvl(l_return_value, -99));
640 end if;-- Marker for Check v_get_wlc_quantity.uom_code <> l_primary_uom
641 end loop;
642 -- @@@ Close the cursor since the loop has been exited prematurely.
643 if (l_return_value = -99999) then
644 if c_get_wlc_quantity%ISOPEN then
645 trace(l_proc || 'c_get_wlc_quantity is open and hence being closed ');
646 close c_get_wlc_quantity;
647 end if;
648 end if;
649 close c_get_prim_uom_code;
650 end if;-- Marker for c_get_prim_uom_code FOUND/NOTFOUND
654 if c_get_prim_uom_code%ISOPEN then
651 return l_total_quantity;
652 exception
653 when others then
655 close c_get_prim_uom_code;
656 end if;
657
658 if c_get_wlc_quantity%ISOPEN then
659 close c_get_wlc_quantity;
660 end if;
661 return 0;
662 end getlpnquantityrevlot;
663 --
664 --
665 -- API name : GetLpnTotalQuantity
666 -- Type : Private
667 -- Function : Returns quantity of the given item in the given LPN for the given item.
668 --
669 -- Data in WLC is generally in primary UOM. However this is because INV-TM
670 -- always converts to primary UOM before calling pack/unpack API. packUnpack
671 -- by itself does not make any assumption that material will always be in pri-uom.
672 -- packunpack API could be called directly (as is done in ASN import, due to which
673 -- we have this bug) by an API and a different UOM can be specified. So
674 -- you cannot make the assumption that WLC will always be in pri-uom.
675 -- Hence not in all cases, can we safely assume that the UOM CODE on the WLC record
676 -- is in the primary UOM of the item. So it is always best to convert if the
677 -- primary uom for the item and the WLC.UOM_CODE doesn't match. .
678 function getlpntotalquantity(
679 p_lpn_id in number
680 , p_inventory_item_id in number
681 , p_organization_id in number)
682 return number
683 is
684 l_proc varchar2(72) := 'GetLpnTotalQuantity :';
685 l_return_value number:= 0;
686 l_primary_uom varchar2(3);
687 l_total_quantity number:= 0;
688 l_loop_counter number := 0;
689 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
690
691 -- @@@ Get the primary uom for the Item in question.
692 cursor c_get_prim_uom_code is
693 select primary_uom_code
694 from mtl_system_items mtl
695 where mtl.inventory_item_id = p_inventory_item_id
696 and mtl.organization_id = p_organization_id;
697
698 -- @@@ Check to see if the record in WLC is in the prim uom of the item.
699 cursor c_get_wlc_quantity is
700 select sum(wlc.quantity) summed_quantity, uom_code
701 from wms_lpn_contents wlc
702 where wlc.parent_lpn_id = p_lpn_id
703 and wlc.inventory_item_id = p_inventory_item_id
704 and wlc.organization_id = p_organization_id
705 group by uom_code;
706
707 begin
708
709 if (l_debug = 1) then
710 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
711 trace(l_proc || ' p_lpn_id => ' || p_lpn_id);
712 trace(l_proc || ' p_inventory_item_id => ' || p_inventory_item_id);
713 trace(l_proc || ' p_organization_id => ' || p_organization_id);
714 end if;
715
716 -- @@@ Open cursor to fetch primary UOM code for the item in question.
717 open c_get_prim_uom_code;
718 fetch c_get_prim_uom_code
719 into l_primary_uom;
720
721
722 if c_get_prim_uom_code%NOTFOUND then
723 close c_get_prim_uom_code;
724 return null;
725 else
726 trace(l_proc || 'l_primary_uom : ' || nvl(l_primary_uom, '@@@'));
727 for v_get_wlc_quantity in c_get_wlc_quantity
728 loop
729 l_loop_counter := l_loop_counter + 1;
730 trace(l_proc || 'l_loop_counter : ' || nvl(l_loop_counter, -99));
731 if v_get_wlc_quantity.uom_code <> l_primary_uom then
732 trace(l_proc || 'summed_quantity : ' || nvl(v_get_wlc_quantity.summed_quantity, -99));
733 trace(l_proc || 'uom_code : ' || nvl(v_get_wlc_quantity.uom_code, '@@@'));
734 l_return_value := inv_convert.inv_um_convert(
735 item_id => p_inventory_item_id
736 , precision => null
737 , from_quantity => v_get_wlc_quantity.summed_quantity
738 , from_unit => v_get_wlc_quantity.uom_code
739 , to_unit => l_primary_uom
740 , from_name => null
741 , to_name => null
742 );
743 -- @@@ The above call(INVUNCMB,pls) is code such that when the "When Others" exception occurs,
744 -- @@@ it sets the value of l_return_value to -9999. When this happens inside the loop,
745 -- @@@ we have to make sure that the loop is exited and the l_total_quantity is 0.
746 -- @@@ Hence a check around this.
747 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
748 trace(l_proc || 'l_return_value : ' || nvl(l_return_value, -99));
749 if (l_return_value = -99999) then
750 l_total_quantity := 0;
751 trace(l_proc || 'l_total_quantity just before exit : ' || nvl(l_total_quantity, -99));
752 exit;
753 else
754 l_total_quantity := l_total_quantity + l_return_value;
755 trace(l_proc || 'l_total_quantity in the else of the l_return_value check : ' || nvl(l_total_quantity, -99));
756 end if;
757 else
758 l_total_quantity := l_total_quantity + v_get_wlc_quantity.summed_quantity;
759 trace(l_proc || 'l_total_quantity in the else : ' || nvl(l_return_value, -99));
760 end if;
761 end loop;
762 -- @@@ Close the cursor since the loop has been exited prematurely.
763 if (l_return_value = -99999) then
764 if c_get_wlc_quantity%ISOPEN then
765 trace(l_proc || 'c_get_wlc_quantity is open and hence being closed ');
766 close c_get_wlc_quantity;
767 end if;
768 end if;
769 close c_get_prim_uom_code;
770 end if;
771 return l_total_quantity;
772 exception
776 end if;
773 when others then
774 if c_get_prim_uom_code%ISOPEN then
775 close c_get_prim_uom_code;
777
778 if c_get_wlc_quantity%ISOPEN then
779 close c_get_wlc_quantity;
780 end if;
781 return 0;
782 end getlpntotalquantity;
783 --
784 --
785 -- API name : GetLpnNumOfItems
786 -- Type : Private
787 -- Function : Returns number of items - 1 in the the given LPN.
788 -- This function considers the current item as well as all the other items.
789 function getlpnnumofitems(
790 p_lpn_id in number
791 , p_organization_id in number)
792 return number
793 is
794 l_return_value number;
795
796 begin
797 if p_lpn_id is null then
798 return -1;
799 end if;
800
801 select count(distinct(wlc.inventory_item_id))
802 into l_return_value
803 from wms_lpn_contents wlc
804 where wlc.parent_lpn_id = p_lpn_id
805 and wlc.organization_id = p_organization_id;
806
807 return l_return_value;
808 exception
809 when others then
810 return -1;
811 end getlpnnumofitems;
812
813 -- API name : GetLpnNumOtherRevs
814 -- Type : Private
815 -- Function : Returns number of revisions of this item in the given LPN
816 function getlpnnumofrevs(
817 p_lpn_id in number
818 , p_inventory_item_id in number
819 , p_organization_id in number)
820 return number
821 is
822 l_return_value NUMBER;
823
824 begin
825 if p_lpn_id is null or p_inventory_item_id is null then
826 return -1;
827 end if;
828
829 select count(distinct(wlc.revision))
830 into l_return_value
831 from wms_lpn_contents wlc
832 where wlc.parent_lpn_id = p_lpn_id
833 and wlc.inventory_item_id = p_inventory_item_id
834 and wlc.organization_id = p_organization_id;
835
836 return l_return_value;
837 exception
838 when others then
839 return -1;
840 end getlpnnumofrevs;
841
842 -- API name : GetLpnNumOtherLots
843 -- Type : Private
844 -- Function : Returns number of lots of this item - 1
845 -- in the the given LPN
846 function getlpnnumoflots(
847 p_lpn_id in number
848 , p_inventory_item_id in number
849 , p_organization_id in number)
850 return number
851 is
852 l_return_value number;
853
854 begin
855 if p_lpn_id is null or p_inventory_item_id is null then
856 return -1;
857 end if;
858
859 select count(distinct(wlc.lot_number))
860 into l_return_value
861 from wms_lpn_contents wlc
862 where wlc.parent_lpn_id = p_lpn_id
863 and wlc.inventory_item_id = p_inventory_item_id
864 and wlc.organization_id = p_organization_id;
865
866
867 return l_return_value;
868 exception
869 when others then
870 return -1;
871 end getlpnnumoflots;
872
873 end wms_parameter_ext;
[Home] [Help]