[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_DETAILS_INV
Source
1 PACKAGE BODY WSH_DELIVERY_DETAILS_INV as
2 /* $Header: WSHDDICB.pls 120.10 2008/01/14 14:54:52 skanduku ship $ */
3
4 g_org INV_VALIDATE.Org;
5 g_item INV_VALIDATE.Item;
6 g_sub INV_VALIDATE.Sub;
7 g_loc INV_VALIDATE.Locator;
8 g_lot INV_VALIDATE.Lot;
9 g_serial INV_VALIDATE.Serial;
10 g_to_sub INV_VALIDATE.Sub;
11
12
13 -- bug 5264874
14
15 TYPE inventory_control_rec IS RECORD(
16 LOC_CONTROL_FLAG VARCHAR2(3),
17 LOT_CONTROL_FLAG VARCHAR2(3),
18 REV_CONTROL_FLAG VARCHAR2(3),
19 SERIAL_CONTROL_FLAG VARCHAR2(3),
20 RESTRICT_LOCATORS_CODE MTL_SYSTEM_ITEMS.RESTRICT_LOCATORS_CODE%TYPE,
21 RESTRICT_SUBINVENTORIES_CODE MTL_SYSTEM_ITEMS.RESTRICT_SUBINVENTORIES_CODE%TYPE,
22 SERIAL_NUMBER_CONTROL_CODE MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE%TYPE,
23 LOCATION_CONTROL_CODE MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE%TYPE,
24 RESERVABLE_TYPE MTL_SYSTEM_ITEMS.RESERVABLE_TYPE%TYPE,
25 MTL_TRANSACTIONS_ENABLED_FLAG MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG%TYPE);
26
27
28 TYPE inventory_control_tab IS TABLE OF inventory_control_rec INDEX BY VARCHAR2(90);
29 g_inventory_control_tab inventory_control_tab;
30
31
32 CURSOR c_item_info (v_organization_id NUMBER, v_inventory_item_id IN NUMBER) IS
33 SELECT organization_id, inventory_item_id,
34 primary_uom_code, secondary_uom_code, secondary_default_ind,
35 lot_control_code, tracking_quantity_ind, dual_uom_deviation_low,
36 dual_uom_deviation_high, enabled_flag, shippable_item_flag,
37 inventory_item_flag, lot_divisible_flag, container_item_flag,
38 reservable_type, mtl_transactions_enabled_flag, 'Y' valid_flag
39 FROM MTL_SYSTEM_ITEMS
40 WHERE organization_id = v_organization_id
41 AND inventory_item_id = v_inventory_item_id;
42
43 CURSOR c_org_param_info (v_organization_id NUMBER) IS
44 SELECT STOCK_LOCATOR_CONTROL_CODE,
45 NEGATIVE_INV_RECEIPT_CODE,
46 SERIAL_NUMBER_TYPE
47 FROM MTL_PARAMETERS
48 WHERE organization_id = v_organization_id;
49
50 CURSOR c_sec_inv_info (v_organization_id NUMBER, v_subinventory VARCHAR2) IS
51 SELECT locator_type
52 FROM MTL_SUBINVENTORIES_TRK_VAL_V
53 WHERE organization_id = v_organization_id
54 AND secondary_inventory_name = v_subinventory;
55
56 TYPE Item_Cache_Tab_Typ IS TABLE OF c_item_info%ROWTYPE INDEX BY BINARY_INTEGER;
57 TYPE Param_Cache_Tab_Typ IS TABLE OF c_org_param_info%ROWTYPE INDEX BY VARCHAR2(60);
58 TYPE Sec_inv_Tab_Typ IS TABLE OF c_sec_inv_info%ROWTYPE INDEX BY VARCHAR2(60);
59 -- HW OPMCONV New table to hold item information
60
61 g_item_tab Item_Cache_Tab_Typ;
62 g_param_tab Param_Cache_Tab_Typ;
63 g_sec_inv_tab Sec_inv_Tab_Typ;
64 g_lpad_char VARCHAR2(1) := '0';
65 g_lpad_length NUMBER := 25;
66 g_session_id NUMBER;
67
68 -- bug 5264874 end
69 /*
70 ** bug 1583800: enable support for nontransactable items
71 ** Internal procedure: Inventory_Item
72 ** This is a copy of function INV_VALIDATE.inventory_item in INVVSVATB.pls
73 ** The code has been modified not to check MTL_TRANSACTIONS_ENABLED_FLAG
74 ** and to qualify the package INV_VALIDATE constants T and F.
75 ** The code is simplified, as we set only p_item.inventory_item_id.
76 **/
77 --
78 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_INV';
79 --
80
81 -- HW OPMCONV New procedure to cach item info
82
83 --========================================================================
84 -- PROCEDURE : get_item_table_index
85 --
86 -- COMMENT : Validate using Hash (internal API)
87 -- uses Hash and avoids linear scans while using PL/SQL tables
88 -- Currently available for 4 parameters (VARCHAR2 datatype)
89 -- PARAMETERS:
90 -- p_validate_rec -- Input Key to be validated
91 -- x_generic_tab -- populated for existing cached records
92 -- x_index -- New index which can be used for x_flag = U
93 -- x_return_status -- S,E,U,W
94 -- x_flag -- U to use this index,D to indicate valid record
95 --
96 -- HISTORY : Bug 3821688
97 -- NOTE : For performance reasons, no debug calls are added
98 --========================================================================
99 PROCEDURE get_item_table_index
100 (p_validate_rec IN c_item_info%ROWTYPE,
101 p_item_tab IN Item_Cache_Tab_Typ,
102 x_index OUT NOCOPY NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_flag OUT NOCOPY VARCHAR2
105 )IS
106
107 c_hash_base CONSTANT NUMBER := 1;
108 c_hash_size CONSTANT NUMBER := power(2, 25);
109
110 l_hash_string VARCHAR2(4000) := NULL;
111 l_index NUMBER;
112 l_hash_exists BOOLEAN := FALSE;
113
114 l_flag VARCHAR2(1);
115
116
117 BEGIN
118
119 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
120 l_hash_exists := FALSE;
121 -- need to hash this index
122 -- Key (for hash) : Organization_id || Inventory_item_id
123
124 l_hash_string := to_char(p_validate_rec.organization_id)||
125 to_char(p_validate_rec.inventory_item_id);
126
127 -- Hash returns a common index if l_hash_string is identical
128 l_index := dbms_utility.get_hash_value (
129 name => l_hash_string,
130 base => c_hash_base,
131 hash_size => c_hash_size);
132 WHILE NOT l_hash_exists LOOP
133 IF p_item_tab.EXISTS(l_index) THEN
134 -- Check for all attributes match
135
136 IF (
137 p_item_tab(l_index).organization_id =p_validate_rec.organization_id
138 AND
139 p_item_tab(l_index).inventory_item_id=p_validate_rec.inventory_item_id
140 ) THEN
141 -- exact match found at this index
142 l_flag := 'D';
143 EXIT;
144 ELSE
145
146 -- Index exists but key does not match this table element
147 -- Bump l_index till key matches or table element does not exist
148 l_index := l_index + 1;
149 END IF;
150 ELSE
151 -- Index is not used in the table, can be used to create a new record
152 l_hash_exists := TRUE; -- to exit from the loop
153 l_flag := 'U';
154 END IF;
155 END LOOP;
156
157 x_index := l_index;
158 x_flag := l_flag;
159
160 END get_item_table_index;
161 --
162
163 FUNCTION Inventory_Item (p_item IN OUT nocopy INV_VALIDATE.item,
164 p_org IN INV_VALIDATE.org)
165 RETURN NUMBER
166 IS
167 l_appl_short_name VARCHAR2(3) := 'INV';
168 l_key_flex_code VARCHAR2(4) := 'MSTK';
169 l_structure_number NUMBER := 101;
170 l_conc_segments VARCHAR2(2000);
171 l_keystat_val BOOLEAN;
172 l_id NUMBER;
173 l_validation_mode VARCHAR2(25) := INV_VALIDATE.EXISTS_ONLY;
174
175 --
176 l_debug_on BOOLEAN;
177 --
178 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INVENTORY_ITEM';
179 --
180 BEGIN
181
182 --
183 -- Debug Statements
184 --
185 --
186 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
187 --
188 IF l_debug_on IS NULL
189 THEN
190 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
191 END IF;
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.push(l_module_name);
195 END IF;
196 --
197 SELECT *
198 INTO p_item
199 FROM MTL_SYSTEM_ITEMS
200 WHERE ORGANIZATION_ID = p_org.organization_id
201 AND INVENTORY_ITEM_ID = p_item.inventory_item_id;
202
203 --
204 -- Debug Statements
205 --
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.pop(l_module_name);
208 END IF;
209 --
210 RETURN INV_VALIDATE.T;
211
212 EXCEPTION
213
214 WHEN NO_DATA_FOUND THEN
215
216 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
217 THEN
218
219 FND_MESSAGE.SET_NAME('INV','INV_INT_ITMCODE');
220 FND_MSG_PUB.Add;
221
222 END IF;
223
224 --
225 -- Debug Statements
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
229 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
230 END IF;
231 --
232 RETURN INV_VALIDATE.F;
233
234 WHEN OTHERS THEN
235
236 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
237 THEN
238 FND_MSG_PUB.Add_Exc_Msg
239 ( 'WSH_DELIVERY_DETAILS_INV' -- Shipping package
240 , 'Inventory_Item'
241 );
242 END IF;
243
244 --
245 -- Debug Statements
246 --
247 IF l_debug_on THEN
248 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
249 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
250 END IF;
251 --
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253
254 END Inventory_Item;
255
256 -- bug 5264874
257
258 /*
259 -----------------------------------------------------------------------------
260 PROCEDURE : Get_Org_Param_information
261 PARAMETERS : p_organization_id - organization id
262 x_mtl_org_param_rec - Record to hold parameters informatiom
263 x_return_status - success if able to look up item information
264 error if cannot find item information
265
266 DESCRIPTION : This API takes the organization id
267 and checks if parameters information is already cached, if
268 not, it loads the new parameters information for a specific
269 organization
270 -----------------------------------------------------------------------------
271 */
272
273 PROCEDURE Get_Org_Param_information (
274 p_organization_id IN NUMBER
275 , x_mtl_org_param_rec OUT NOCOPY WSH_DELIVERY_DETAILS_INV.mtl_org_param_rec
276 , x_return_status OUT NOCOPY VARCHAR2
277 )IS
278
279
280 l_debug_on BOOLEAN;
281 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Org_Param_information';
282 TYPE org_param_cache_tab IS TABLE OF c_org_param_info%ROWTYPE;
283
284 l_cache_rec c_org_param_info%ROWTYPE;
285
286 BEGIN
287
288 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
289 IF l_debug_on IS NULL
290 THEN
291 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
292 END IF;
293 IF l_debug_on THEN
294 WSH_DEBUG_SV.push(l_module_name);
295 END IF;
296
297 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
298
299
300 IF p_organization_id IS NOT NULL THEN
301
302 IF g_param_tab.EXISTS(p_organization_id) THEN
303 l_cache_rec := g_param_tab(p_organization_id);
304 ELSE
305 OPEN c_org_param_info (p_organization_id);
306 FETCH c_org_param_info INTO l_cache_rec;
307 IF c_org_param_info%NOTFOUND THEN
308 l_cache_rec.negative_inv_receipt_code := 1;
309 l_cache_rec.serial_number_type := -99;
310 l_cache_rec.stock_locator_control_code := 4;
311 END IF;
312 CLOSE c_org_param_info;
313
314 IF l_debug_on THEN
315 WSH_DEBUG_SV.log(l_module_name,'l_cache_rec.stock_locator_control_code',l_cache_rec.stock_locator_control_code);
316 WSH_DEBUG_SV.log(l_module_name,'l_cache_rec.negative_inv_receipt_code',l_cache_rec.negative_inv_receipt_code);
317 WSH_DEBUG_SV.log(l_module_name,'l_cache_rec.serial_number_type',l_cache_rec.serial_number_type);
318 END IF;
319 -- add record to cache
320 g_param_tab(p_organization_id) := l_cache_rec;
321 END IF;
322
323 x_mtl_org_param_rec.stock_locator_control_code := l_cache_rec.stock_locator_control_code;
324 x_mtl_org_param_rec.negative_inv_receipt_code := l_cache_rec.negative_inv_receipt_code;
325 x_mtl_org_param_rec.serial_number_type := l_cache_rec.serial_number_type;
326
327 ELSE -- Org is is null
328 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
329 IF l_debug_on THEN
330 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
331 END IF;
332 --
333 RETURN;
334 END IF;
335
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
338 END IF;
339
340 EXCEPTION
341
342 WHEN others THEN
343 wsh_util_core.default_handler ('WSH_DELIVERY_DETAILS_INV.Get_Org_Param_information');
344 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
345
346 IF l_debug_on THEN
347 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
348 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
349 END IF;
350
351 IF c_org_param_info%isopen THEN
352 close c_org_param_info;
353 END IF;
354
355 END Get_Org_Param_information;
356
357
358 /*
359 -----------------------------------------------------------------------------
360 PROCEDURE : Get_Sec_Inv_information
361 PARAMETERS : p_organization_id - organization id
362 p_inventory_item_id - inventory_item_id
363 x_mtl_sec_inv_rec - Record to hold sec inventoy informatiom
364 x_return_status - success if able to look up sec inventoy information
365 error if cannot find information
366
367 DESCRIPTION : This API takes the organization and inventory item
368 and checks if sec inventoy information is already cached, if
369 not, it loads the new sec inventoy information for a specific
370 organization
371 -----------------------------------------------------------------------------
372 */
373
374 PROCEDURE Get_Sec_Inv_information (
375 p_organization_id IN NUMBER
376 , p_subinventory_name IN VARCHAR2
377 , x_mtl_sec_inv_rec OUT NOCOPY WSH_DELIVERY_DETAILS_INV.mtl_sec_inv_rec
378 , x_return_status OUT NOCOPY VARCHAR2
379 )IS
380
381
382
383 l_debug_on BOOLEAN;
384 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Sec_Inv_information';
385
386 l_cache_rec c_sec_inv_info%ROWTYPE;
387 l_key VARCHAR2(60);
388
389 BEGIN
390
391 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
392 IF l_debug_on IS NULL
393 THEN
394 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
395 END IF;
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.push(l_module_name);
398 END IF;
399
400 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
401
402
403 IF p_organization_id IS NOT NULL THEN
404
405 l_key := LPAD(p_organization_id, g_lpad_length, g_lpad_char) ||'-'|| LPAD(p_subinventory_name, g_lpad_length, g_lpad_char);
406
407 IF g_sec_inv_tab.EXISTS(l_key) THEN
408 l_cache_rec := g_sec_inv_tab(l_key);
409 ELSE
410 OPEN c_sec_inv_info (p_organization_id, p_subinventory_name);
411 FETCH c_sec_inv_info INTO l_cache_rec;
412 IF c_sec_inv_info%NOTFOUND THEN
413 l_cache_rec.locator_type := 1;
414 END IF;
415 CLOSE c_sec_inv_info;
416
417 -- add record to cache
418 g_sec_inv_tab(l_key) := l_cache_rec;
419 IF l_debug_on THEN
420 WSH_DEBUG_SV.log(l_module_name,'x_mtl_sec_inv_rec.locator_type',x_mtl_sec_inv_rec.locator_type);
421 END IF;
422
423 END IF;
424
425 x_mtl_sec_inv_rec.locator_type := l_cache_rec.locator_type;
426
427 ELSE -- Org is is null
428 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.pop(l_module_name, x_return_status);
431 END IF;
432 --
433 RETURN;
434 END IF;
435
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
438 END IF;
439
440 EXCEPTION
441
442 WHEN others THEN
443 wsh_util_core.default_handler ('WSH_DELIVERY_DETAILS_INV.Get_Sec_Inv_information');
444 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
445
446 IF l_debug_on THEN
447 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
448 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
449 END IF;
450
451 IF c_sec_inv_info%isopen THEN
452 close c_sec_inv_info;
453 END IF;
454
455 END Get_Sec_Inv_information;
456
457 -- bug 5264874 end
458
459 /*
460 -----------------------------------------------------------------------------
461 PROCEDURE : Fetch_Inv_Controls
462 PARAMETERS : p_delivery_detail_id - delivery detail id.
463 p_inventory_item_id - inventory_item_id on line for which
464 inventory controls need to be determined.
465 p_organization_id - organization_id to which inventory_item
466 belongs.
467 p_subinventory - subinventory to which the item belongs
468 x_inv_controls_rec - output record of
469 WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec type containing
470 all inv control flags for the item and organization.
471 x_return_status - return status of the API.
472 DESCRIPTION : This procedure takes a delivery detail id and optionally
473 inventory item id and organization id and determines whether
474 the item is under any of the inventory controls. The API
475 fetches the control codes/flags from mtl_system_items for the
476 given inventory item and organization and decodes them and
477 returns a record of inv controls with a 'Y' or a 'N' for each
478 of the inv controls.
479
480 ------------------------------------------------------------------------------
481 */
482
483 PROCEDURE Fetch_Inv_Controls (
484 p_delivery_detail_id IN NUMBER DEFAULT NULL,
485 p_inventory_item_id IN NUMBER,
486 p_organization_id IN NUMBER,
487 p_subinventory IN VARCHAR2,
488 x_inv_controls_rec OUT NOCOPY WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec,
489 x_return_status OUT NOCOPY VARCHAR2) IS
490
491 -- OPM change 1711019
492
493
494 l_pickable_flag VARCHAR2(1);-- added for Bug 3584278
495
496
497 -- HW OPM BUG#:3011758 Added container_item_flag
498 -- Bug 3584278 : Remove join to wsh_delivery_details table(in the cursor) since the fields required
499 -- from wdd are already available in the local variables
500 CURSOR Get_Inv_Controls (v_inventory_item_id NUMBER,
501 v_organization_id NUMBER,
502 v_pickable_flag VARCHAR2) IS
503 SELECT DECODE(msi.location_control_code, 1, 'N',
504 'Y') loc_control_flag,
505 DECODE(msi.lot_control_code, 2, DECODE(v_pickable_flag, 'N', 'O',
506 'Y'),
507 3, 'N',
508 'N') lot_control_flag,
509 DECODE(msi.revision_qty_control_code, 2, DECODE(v_pickable_flag, 'N', 'O',
510 'Y'),
511 'N') rev_control_flag,
512 DECODE(msi.serial_number_control_code,2, 'Y',
513 5, 'Y',
514 6, 'D',
515 'N') serial_control_flag,
516 msi.restrict_locators_code,
517 msi.restrict_subinventories_code,
518 msi.serial_number_control_code,
519 msi.location_control_code,
520 -- HW OPMCONV. No need for item_no
521 -- msi.segment1 ,
522 -- msi.container_item_flag,
523 msi.reservable_type,
524 msi.MTL_TRANSACTIONS_ENABLED_FLAG -- Bug 3599363
525 FROM MTL_SYSTEM_ITEMS msi
526 WHERE msi.inventory_item_id = v_inventory_item_id
527 AND msi.organization_id = v_organization_id;
528
529 CURSOR Get_Detail_Item IS
530 SELECT inventory_item_id, organization_id, subinventory,
531 container_flag,pickable_flag
532 FROM WSH_DELIVERY_DETAILS
533 WHERE delivery_detail_id = p_delivery_detail_id;
534
535
536 l_rev_flag VARCHAR2(3);
537 l_loc_flag VARCHAR2(3);
538 l_lot_flag VARCHAR2(3);
539 l_ser_flag VARCHAR2(3);
540 l_sub_flag VARCHAR2(3) := 'Y';
541 l_serial_number_code NUMBER;
542 l_location_control_code NUMBER;
543
544 l_restrict_loc_code NUMBER;
545 l_restrict_sub_code NUMBER;
546
547 l_inv_item_id NUMBER;
548 l_org_id NUMBER;
549 l_cont_flag VARCHAR2(1);
550
551 l_org_loc_code NUMBER;
552 l_sub_loc_code NUMBER;
553
554 l_subinv VARCHAR2(30);
555 l_dft_subinv VARCHAR2(30);
556 l_txn_enabled_flag VARCHAR2(1);
557 l_loc_ctl_code NUMBER;
558
559 -- HW OPM BUG:3011758 Added l_container_item_flag
560 -- HW OPMCONV. No need for container_item_flag variable
561 --l_container_item_flag VARCHAR2(1);
562 --
563 l_debug_on BOOLEAN;
564
565 l_reservable_type NUMBER ;
566 l_inventory_control_rec inventory_control_rec;
567 l_key VARCHAR2(90);
568 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FETCH_INV_CONTROLS';
569 --
570
571 BEGIN
572
573 --
574 -- Debug Statements
575 --
576 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
577 --
578 IF l_debug_on IS NULL
579 THEN
580 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
581 END IF;
582 --
583 IF l_debug_on THEN
584 WSH_DEBUG_SV.push(l_module_name);
585 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
586 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
587 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
588 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
589 END IF;
590 --
591 IF p_delivery_detail_id IS NULL THEN
592 --{
593 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
594 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
595 WSH_UTIL_CORE.Add_Message(x_return_status);
596 --
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.pop(l_module_name);
599 END IF;
600 --
601 return;
602 --}
603 END IF;
604 --
605 -- Bug 3584278 , the cursor is required always, so moving the cursor before
606 -- If validation.This is required to identify if the delivery line is a container
607 OPEN Get_Detail_Item;
608 FETCH Get_Detail_Item INTO l_inv_item_id, l_org_id, l_subinv,
609 l_cont_flag,l_pickable_flag;
610 --
611 IF Get_Detail_Item%NOTFOUND THEN
612 --{
613 CLOSE Get_Detail_Item;
614 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
615 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
616 WSH_UTIL_CORE.Add_Message(x_return_status);
617 --
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.pop(l_module_name);
620 END IF;
621 --
622 return;
623 --}
624 END IF;
625 --
626 IF Get_Detail_Item%ISOPEN THEN
627 CLOSE Get_Detail_Item;
628 END IF;
629 -- End of Bug 3584278
630 --
631 -- Note that the Input variables are being assigned to Local variables
632 -- This overrides the values returned from above cursor in local variables
633 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
634 l_inv_item_id := p_inventory_item_id;
635 l_org_id := p_organization_id;
636 l_subinv := p_subinventory;
637 END IF;
638 --
639 -- Use the Inventory Item Id and organization to fetch the inv controls..
640 -- Bug 3584278, Container item is not under inventory controls
641 IF (l_inv_item_id IS NULL OR
642 l_cont_flag = 'Y'
643 )THEN
644 --{
645 -- bug 1661590: LPNs/containers in WMS can have NULL inventory_item_id
646 -- For them, we should not send a message "No Data Found."
647 -- bug 2177410, delivery detail line in WMS can have NULL
648 -- inventory_item_id as well
649 x_inv_controls_rec.rev_flag := 'N';
650 x_inv_controls_rec.rev_flag := 'N';
651 x_inv_controls_rec.lot_flag := 'N';
652 x_inv_controls_rec.sub_flag := 'N';
653 x_inv_controls_rec.loc_flag := 'N';
654 x_inv_controls_rec.ser_flag := 'N';
655 x_inv_controls_rec.restrict_loc := NULL;
656 x_inv_controls_rec.restrict_sub := NULL;
657 x_inv_controls_rec.location_control_code := NULL;
658 x_inv_controls_rec.serial_code := NULL;
659 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
660 --
661 IF l_debug_on THEN
662 WSH_DEBUG_SV.logmsg(l_module_name,'Case where l_inv_item_id IS NULL or line is Container');
663 WSH_DEBUG_SV.pop(l_module_name);
664 END IF;
665 --
666 return;
667 --}
668 END IF;
669 -- bug 5264874
670
671
672 l_key := lpad(l_inv_item_id,g_lpad_length, g_lpad_char) || '-' ||
673 lpad(l_org_id, g_lpad_length, g_lpad_char)||'-'||
674 lpad(l_pickable_flag,g_lpad_length, g_lpad_char);
675
676 IF g_inventory_control_tab.EXISTS(l_key) THEN
677
678 l_inventory_control_rec := g_inventory_control_tab(l_key);
679
680 l_loc_flag := l_inventory_control_rec.LOC_CONTROL_FLAG;
681 l_lot_flag := l_inventory_control_rec.LOT_CONTROL_FLAG;
682 l_rev_flag := l_inventory_control_rec.REV_CONTROL_FLAG;
683 l_ser_flag := l_inventory_control_rec.SERIAL_CONTROL_FLAG;
684 l_restrict_loc_code := l_inventory_control_rec.RESTRICT_LOCATORS_CODE;
685 l_restrict_sub_code := l_inventory_control_rec.RESTRICT_SUBINVENTORIES_CODE;
686 l_serial_number_code := l_inventory_control_rec.SERIAL_NUMBER_CONTROL_CODE;
687 l_location_control_code := l_inventory_control_rec.LOCATION_CONTROL_CODE;
688 l_reservable_type := l_inventory_control_rec.RESERVABLE_TYPE;
689 l_txn_enabled_flag := l_inventory_control_rec.MTL_TRANSACTIONS_ENABLED_FLAG;
690 -- bug 5264874 end
691 ELSE
692
693 --
694 -- Bug 3584278, input parameters added for cursor get_inv_controls
695 OPEN Get_Inv_Controls (l_inv_item_id,l_org_id,l_pickable_flag);
696 --
697 -- HW OPM BUG#:3011758 Added l_container_item_flag
698 -- HW OPMCONV. No need to retreieve item_no, l_container_item_flag
699 FETCH Get_Inv_Controls INTO
700 l_loc_flag,
701 l_lot_flag,
702 l_rev_flag,
703 l_ser_flag,
704 l_restrict_loc_code,
705 l_restrict_sub_code,
706 l_serial_number_code,
707 l_location_control_code,
708 -- l_item_no ,
709 -- l_container_item_flag,
710 l_reservable_type,
711 l_txn_enabled_flag ; -- Bug 3599363
712 --
713 IF Get_Inv_Controls%NOTFOUND THEN
714 --{
715 CLOSE Get_Inv_Controls;
716 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
717 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
718 WSH_UTIL_CORE.Add_Message(x_return_status);
719 --
720 IF l_debug_on THEN
721 WSH_DEBUG_SV.pop(l_module_name);
722 END IF;
723 --
724 return;
725 --}
726 END IF;
727 --
728 IF Get_Inv_Controls%ISOPEN THEN
729 CLOSE Get_Inv_Controls;
730 END IF;
731 -- bug 526487
732 l_inventory_control_rec.LOC_CONTROL_FLAG := l_loc_flag;
733 l_inventory_control_rec.LOT_CONTROL_FLAG := l_lot_flag;
734 l_inventory_control_rec.REV_CONTROL_FLAG := l_rev_flag;
735 l_inventory_control_rec.SERIAL_CONTROL_FLAG := l_ser_flag;
736 l_inventory_control_rec.RESTRICT_LOCATORS_CODE := l_restrict_loc_code;
737 l_inventory_control_rec.RESTRICT_SUBINVENTORIES_CODE := l_restrict_sub_code;
738 l_inventory_control_rec.SERIAL_NUMBER_CONTROL_CODE := l_serial_number_code;
739 l_inventory_control_rec.LOCATION_CONTROL_CODE := l_location_control_code;
740 l_inventory_control_rec.RESERVABLE_TYPE := l_reservable_type;
741 l_inventory_control_rec.MTL_TRANSACTIONS_ENABLED_FLAG := l_txn_enabled_flag;
742
743 g_inventory_control_tab(l_key) := l_inventory_control_rec;
744 -- bug 5264874 end
745 END IF;
746 --
747 -- OPM B1711019
748 --
749 -- HW OPMCONV. No need to fork code
750
751 x_inv_controls_rec.rev_flag := l_rev_flag;
752 x_inv_controls_rec.lot_flag := l_lot_flag;
753 x_inv_controls_rec.sub_flag := 'Y';
754 x_inv_controls_rec.reservable_type := l_reservable_type ;
755 x_inv_controls_rec.transactable_flag := l_txn_enabled_flag;
756 --
757 -- Bug 3599363 : Call default_subinventory() only if the
758 -- sub on the line is NULL and the item is transactable
759 --
760 IF (l_subinv IS NULL AND
761 NVL(l_txn_enabled_flag, 'N') = 'Y') THEN
762 --{
763 IF l_debug_on THEN
764 wsh_debug_sv.log(l_module_name, 'Txn Enabled Flag', l_txn_enabled_flag);
765 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.DEFAULT_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
766 END IF;
767 --
768 WSH_DELIVERY_DETAILS_INV.Default_Subinventory (
769 l_org_id,
770 l_inv_item_id,
771 l_dft_subinv,
772 x_return_status);
773 --
774 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
775 l_dft_subinv := NULL;
776 END IF;
777 --}
778 END IF;
779 --
780 IF l_debug_on THEN
781 wsh_debug_sv.log(l_module_name, 'Default Sub', l_dft_subinv);
782 wsh_debug_sv.log(l_module_name, 'Input Default Sub', l_subinv);
783 END IF;
784 --
785 IF (nvl(l_subinv, l_dft_subinv) IS NOT NULL) THEN
786 --{
787 IF l_debug_on THEN
788 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_ORG_LOC',WSH_DEBUG_SV.C_PROC_LEVEL);
789 END IF;
790 --
791 l_org_loc_code := WSH_DELIVERY_DETAILS_INV.Get_Org_Loc (l_org_id);
792 --
793 IF l_debug_on THEN
794 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.SUB_LOC_CTL',WSH_DEBUG_SV.C_PROC_LEVEL);
795 END IF;
796 --
797 l_sub_loc_code := WSH_DELIVERY_DETAILS_INV.Sub_Loc_Ctl (
798 nvl(l_subinv,l_dft_subinv),
799 l_org_id);
800
801 --
802 IF l_debug_on THEN
803 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.LOCATOR_CTL_CODE',WSH_DEBUG_SV.C_PROC_LEVEL);
804 END IF;
805 --
806 l_loc_ctl_code := WSH_DELIVERY_DETAILS_INV.Locator_Ctl_Code(
807 l_org_id,
808 l_restrict_loc_code,
809 l_org_loc_code,
810 l_sub_loc_code,
811 l_location_control_code);
812 --
813 IF l_loc_ctl_code = 1 THEN
814 l_loc_flag := 'N';
815 ELSE
816 l_loc_flag := 'Y';
817 END IF;
818 --
819 -- Hverddin 12-SEP-2000 OPM start Of Changes --
820 --
821 -- HW OPMCONV. No need to check for OPM orgs
822
823 END IF;
824 --
825 x_inv_controls_rec.loc_flag := l_loc_flag;
826 x_inv_controls_rec.ser_flag := l_ser_flag;
827 x_inv_controls_rec.restrict_loc := l_restrict_loc_code;
828 x_inv_controls_rec.restrict_sub := l_restrict_sub_code;
829 x_inv_controls_rec.serial_code := l_serial_number_code;
830 x_inv_controls_rec.location_control_code := l_loc_ctl_code;
831 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
832 --
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.LOC_FLAG',x_inv_controls_rec.loc_flag);
835 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.SER_FLAG',x_inv_controls_rec.ser_flag);
836 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.RESTRICT_LOC',x_inv_controls_rec.restrict_loc);
837 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.RESTRICT_SUB',x_inv_controls_rec.restrict_sub);
838 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.SERIAL_CODE',x_inv_controls_rec.serial_code);
839 WSH_DEBUG_SV.log(l_module_name,'X_INV_CONTROLS_REC.LOCATION_CONTROL_CODE',x_inv_controls_rec.location_control_code);
840 WSH_DEBUG_SV.log(l_module_name, 'X_INV_CONTROLS_REC.TRANSACTABLE_FLAG',
841 x_inv_controls_rec.transactable_flag);
842 WSH_DEBUG_SV.pop(l_module_name);
843 END IF;
844 --
845 EXCEPTION
846
847 WHEN Others THEN
848 IF Get_Detail_Item%ISOPEN THEN
849 CLOSE Get_Detail_Item;
850 END IF;
851
852 IF Get_Inv_Controls%ISOPEN THEN
853 CLOSE Get_Inv_Controls;
854 END IF;
855
856 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls');
857 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
858
859 --
860 -- Debug Statements
861 --
862 IF l_debug_on THEN
863 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
864 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
865 END IF;
866 --
867 END Fetch_Inv_Controls;
868
869 /*
870 -----------------------------------------------------------------------------
871 FUNCTION : Details_Required
872 PARAMETERS : p_line_inv_rec - WSH_DELIVERY_DETAILS_INV.line_inv_info type
873 that contains information about all the inventory control
874 values on the form for the delivery detail id.
875 p_set_default - boolean variable that indicates whether
876 to retrieve the default values for controls if the
877 attributes are missing.
878 x_line_inv_rec - WSH_DELIVERY_DETAILS_INV.line_inv_info type
879 containing default values in the case where set_default is TRUE
880 DESCRIPTION : This function takes a WSH_DELIVERY_DETAILS_INV.line_inv_info
881 type with inventory control attributes for the delivery detail
882 id from the form and determines whether additional inventory
883 control information needs to be entered or not. If additional
884 control information is needed then the functions returns a
885 TRUE or else it is returns FALSE.
886 Alternatively, if the p_set_default value is set to TRUE, then
887 it retrieves any default control attributes for the inventory
888 item on the line and returns the information as x_line_inv_rec
889
890 ------------------------------------------------------------------------------
891 */
892
893
894 PROCEDURE Details_Required (
895 p_line_inv_rec IN WSH_DELIVERY_DETAILS_INV.line_inv_info,
896 p_set_default IN BOOLEAN DEFAULT FALSE,
897 x_line_inv_rec OUT NOCOPY WSH_DELIVERY_DETAILS_INV.line_inv_info,
898 x_details_required OUT NOCOPY BOOLEAN,
899 x_return_status OUT NOCOPY VARCHAR2) IS
900
901 dft_subinv VARCHAR2(12);
902 subinv VARCHAR2(12);
903 loc_restricted_flag VARCHAR2(1);
904 dft_loc_id NUMBER;
905 org_loc_ctl NUMBER;
906 sub_loc_ctl NUMBER;
907 item_loc_ctl NUMBER;
908 loc_ctl_code NUMBER;
909 default_loc VARCHAR2(2000);
910
911 l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
912
913 l_ser_qty NUMBER;
914 l_container_flag VARCHAR2(1) := 'N';
915
916 CURSOR get_line_info_cur (p_delivery_detail_id in number ) IS
917 SELECT container_flag
918 FROM WSH_DELIVERY_DETAILS
919 WHERE delivery_detail_id = p_delivery_detail_id;
920 --
921 l_debug_on BOOLEAN;
922 --
923 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DETAILS_REQUIRED';
924 --
925 BEGIN
926
927 /*
928 IF (p_line_inv_rec IS NULL) THEN
929 FND_MESSAGE.SET_NAME('WSH','WSH_DETAIL_INVALID');
930 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
931 x_details_required := FALSE;
932 return;
933 END IF;
934 */
935
936 --
937 -- Debug Statements
938 --
939 --
940 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
941 --
942 IF l_debug_on IS NULL
943 THEN
944 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
945 END IF;
946 --
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.push(l_module_name);
949 WSH_DEBUG_SV.log(l_module_name,'P_SET_DEFAULT',P_SET_DEFAULT);
950 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.DELIVERY_DETAIL_ID',P_LINE_INV_REC.delivery_detail_id);
951 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.INVENTTORY_ITEM_ID',P_LINE_INV_REC.inventory_item_id);
952 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.SHP_QTY',P_LINE_INV_REC.shp_qty);
953 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.REQ_QTY',P_LINE_INV_REC.req_qty);
954 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.SER_QTY',P_LINE_INV_REC.ser_qty);
955 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.REVISION',P_LINE_INV_REC.revision);
956 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.SUBINVENTORY',P_LINE_INV_REC.subinventory);
957 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.LOT_NUMBER',P_LINE_INV_REC.lot_number);
958 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.LOCATOR_ID',P_LINE_INV_REC.locator_id);
959 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.LOCATOR_CONTROL_CODE',P_LINE_INV_REC.locator_control_code);
960 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.SERIAL_NUMBER_CONTROL_CODE',P_LINE_INV_REC.serial_number_control_code);
961 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.TRANSACTION_TEMP_ID',P_LINE_INV_REC.transaction_temp_id);
962 WSH_DEBUG_SV.log(l_module_name,'P_LINE_INV_REC.ORGANIZATION_ID',P_LINE_INV_REC.organization_id);
963 END IF;
964 --
965 --
966 -- Debug Statements
967 --
968 IF l_debug_on THEN
969 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
970 END IF;
971 --
972 WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls (
973 p_line_inv_rec.delivery_detail_id,
974 p_line_inv_rec.inventory_item_id,
975 p_line_inv_rec.organization_id,
976 p_line_inv_rec.subinventory,
977 l_inv_controls_rec,
978 x_return_status);
979
980 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
981 x_details_required := FALSE;
982 --
983 -- Debug Statements
984 --
985 IF l_debug_on THEN
986 WSH_DEBUG_SV.pop(l_module_name);
987 END IF;
988 --
989 return;
990 END IF;
991
992 -- bug 1661590: if no inventory controls, details are not required
993 IF l_inv_controls_rec.rev_flag = 'N'
994 AND l_inv_controls_rec.rev_flag = 'N'
995 AND l_inv_controls_rec.lot_flag = 'N'
996 AND l_inv_controls_rec.sub_flag = 'N'
997 AND l_inv_controls_rec.loc_flag = 'N'
998 AND l_inv_controls_rec.ser_flag = 'N'
999 AND l_inv_controls_rec.restrict_loc IS NULL
1000 AND l_inv_controls_rec.restrict_sub IS NULL
1001 AND l_inv_controls_rec.location_control_code IS NULL
1002 AND l_inv_controls_rec.serial_code IS NULL THEN
1003 x_details_required := FALSE;
1004 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1005 --
1006 -- Debug Statements
1007 --
1008 IF l_debug_on THEN
1009 WSH_DEBUG_SV.pop(l_module_name);
1010 END IF;
1011 --
1012 return;
1013 END IF;
1014
1015 -- changing the check to remove null shp qty as details not required.
1016 -- changing nvl(shp_qty,0) to nvl(shp_qty,-99) so that if shp qty is null
1017 -- details required will be governed by the item attributes.
1018
1019 IF (nvl(p_line_inv_rec.shp_qty,-99) = 0 ) THEN
1020 x_details_required := FALSE;
1021 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1022 --
1023 -- Debug Statements
1024 --
1025 IF l_debug_on THEN
1026 WSH_DEBUG_SV.pop(l_module_name);
1027 END IF;
1028 --
1029 return;
1030 END IF;
1031
1032 IF (p_line_inv_rec.revision IS NULL) AND (l_inv_controls_rec.rev_flag = 'Y') THEN
1033 x_details_required := TRUE;
1034 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1035 --
1036 -- Debug Statements
1037 --
1038 IF l_debug_on THEN
1039 WSH_DEBUG_SV.pop(l_module_name);
1040 END IF;
1041 --
1042 RETURN;
1043 END IF;
1044
1045 IF (p_line_inv_rec.lot_number IS NULL) AND (l_inv_controls_rec.lot_flag = 'Y' ) THEN
1046 x_details_required := TRUE;
1047 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1048 --
1049 -- Debug Statements
1050 --
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.pop(l_module_name);
1053 END IF;
1054 --
1055 RETURN;
1056 END IF;
1057 --
1058 subinv := p_line_inv_rec.subinventory;
1059 --
1060 -- Bug 3599363 : Call default_subinventory() only if the
1061 -- sub on the line is NULL and the item is transactable
1062 --
1063 IF (subinv IS NULL AND
1064 NVL(l_inv_controls_rec.transactable_flag, 'N') = 'Y') THEN
1065
1066 l_container_flag := 'N';
1067 --
1068 FOR get_line_info_rec IN get_line_info_cur(p_line_inv_rec.delivery_detail_id)
1069 LOOP
1070 l_container_flag := NVL(get_line_info_rec.container_flag,'N');
1071 END LOOP;
1072
1073 IF l_container_flag = 'N'
1074 THEN
1075 --{
1076 --
1077 -- Debug Statements
1078 --
1079 IF l_debug_on THEN
1080 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.DEFAULT_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
1081 END IF;
1082 --
1083 WSH_DELIVERY_DETAILS_INV.Default_Subinventory (
1084 p_line_inv_rec.organization_id,
1085 p_line_inv_rec.inventory_item_id,
1086 dft_subinv,
1087 x_return_status);
1088
1089 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1090 x_details_required := FALSE;
1091 --
1092 -- Debug Statements
1093 --
1094 IF l_debug_on THEN
1095 WSH_DEBUG_SV.pop(l_module_name);
1096 END IF;
1097 --
1098 RETURN;
1099 END IF;
1100
1101 IF ( dft_subinv IS NULL ) THEN
1102 x_details_required := TRUE;
1103 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1104 --
1105 -- Debug Statements
1106 --
1107 IF l_debug_on THEN
1108 WSH_DEBUG_SV.pop(l_module_name);
1109 END IF;
1110 --
1111 RETURN;
1112 END IF;
1113 --}
1114 END IF;
1115
1116 END IF;
1117
1118 IF (p_line_inv_rec.locator_id IS NULL) THEN
1119
1120 --
1121 -- Debug Statements
1122 --
1123 IF l_debug_on THEN
1124 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_ORG_LOC',WSH_DEBUG_SV.C_PROC_LEVEL);
1125 END IF;
1126 --
1127 org_loc_ctl := WSH_DELIVERY_DETAILS_INV.Get_Org_Loc (p_line_inv_rec.organization_id);
1128
1129 --
1130 -- Debug Statements
1131 --
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.SUB_LOC_CTL',WSH_DEBUG_SV.C_PROC_LEVEL);
1134 END IF;
1135 --
1136 sub_loc_ctl := WSH_DELIVERY_DETAILS_INV.Sub_Loc_Ctl (
1137 nvl(subinv,dft_subinv),
1138 p_line_inv_rec.organization_id);
1139
1140 item_loc_ctl := l_inv_controls_rec.location_control_code;
1141
1142
1143 --
1144 -- Debug Statements
1145 --
1146 IF l_debug_on THEN
1147 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.LOCATOR_CTL_CODE',WSH_DEBUG_SV.C_PROC_LEVEL);
1148 END IF;
1149 --
1150 loc_ctl_code := WSH_DELIVERY_DETAILS_INV.Locator_Ctl_Code(
1151 p_line_inv_rec.organization_id,
1152 l_inv_controls_rec.restrict_loc,
1153 org_loc_ctl,
1154 sub_loc_ctl,
1155 item_loc_ctl);
1156
1157 IF ( loc_ctl_code <> 1 ) THEN
1158 IF ( l_inv_controls_rec.restrict_loc = 1) THEN
1159 loc_restricted_flag := 'Y';
1160 ELSE
1161 loc_restricted_flag := 'N';
1162 END IF;
1163
1164 --
1165 -- Debug Statements
1166 --
1167 IF l_debug_on THEN
1168 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.DEFAULT_LOCATOR',WSH_DEBUG_SV.C_PROC_LEVEL);
1169 END IF;
1170 --
1171 dft_loc_id := WSH_DELIVERY_DETAILS_INV.Default_Locator (
1172 p_line_inv_rec.organization_id,
1173 p_line_inv_rec.inventory_item_id,
1174 NVL(subinv, dft_subinv),
1175 loc_restricted_flag);
1176
1177 IF ( dft_loc_id IS NULL ) THEN
1178 x_details_required := TRUE;
1179 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1180 --
1181 -- Debug Statements
1182 --
1183 IF l_debug_on THEN
1184 WSH_DEBUG_SV.pop(l_module_name);
1185 END IF;
1186 --
1187 RETURN;
1188 END IF;
1189 END IF;
1190 END IF;
1191
1192 -- We count on the fact that autodetail will reserve all
1193 -- inventory control except serial number control
1194
1195 IF (p_line_inv_rec.serial_number_control_code <> 1) THEN
1196
1197 -- if serial qty is not passed by the form and is null, then call API
1198 -- to estimate the serial qty before checking if serial numbers need to
1199 -- be entered.
1200
1201
1202 IF p_line_inv_rec.ser_qty IS NULL THEN
1203
1204 --
1205 -- Debug Statements
1206 --
1207 IF l_debug_on THEN
1208 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.GET_SERIAL_QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
1209 END IF;
1210 --
1211 l_ser_qty := WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty (
1212 p_line_inv_rec.organization_id,
1213 p_line_inv_rec.delivery_detail_id);
1214
1215 IF nvl(l_ser_qty,-99) = -99 THEN
1216 x_details_required := TRUE;
1217 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1218 --
1219 -- Debug Statements
1220 --
1221 IF l_debug_on THEN
1222 WSH_DEBUG_SV.pop(l_module_name);
1223 END IF;
1224 --
1225 return;
1226 END IF;
1227 ELSE
1228 l_ser_qty := p_line_inv_rec.ser_qty;
1229 END IF;
1230
1231 IF (nvl(p_line_inv_rec.shp_qty,0)) > NVL(l_ser_qty, 0) THEN
1232 x_details_required := TRUE;
1233 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1234 --
1235 -- Debug Statements
1236 --
1237 IF l_debug_on THEN
1238 WSH_DEBUG_SV.pop(l_module_name);
1239 END IF;
1240 --
1241 RETURN;
1242 END IF;
1243 IF (p_line_inv_rec.shp_qty IS NULL AND p_line_inv_rec.serial_number_control_code <> 1 ) THEN
1244 x_details_required := TRUE;
1245 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1246 --
1247 -- Debug Statements
1248 --
1249 IF l_debug_on THEN
1250 WSH_DEBUG_SV.pop(l_module_name);
1251 END IF;
1252 --
1253 RETURN;
1254 END IF;
1255 END IF;
1256
1257 -- x_details_required := FALSE;
1258 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1259
1260 --
1261 -- Debug Statements
1262 --
1263 IF l_debug_on THEN
1264 WSH_DEBUG_SV.pop(l_module_name);
1265 END IF;
1266 --
1267 EXCEPTION
1268
1269 WHEN Others THEN
1270 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.Details_Required');
1271 x_details_required := FALSE;
1272 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1273
1274 --
1275 -- Debug Statements
1276 --
1277 IF l_debug_on THEN
1278 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1279 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1280 END IF;
1281 --
1282 END details_required;
1283
1284
1285 /*
1286 -----------------------------------------------------------------------------
1287 FUNCTION : Sub_Loc_Ctl
1288 PARAMETERS : p_subinventory - subinventory
1289 x_sub_loc_ctl - locator control code of subinventory
1290 x_return_status - return status of API..
1291 DESCRIPTION : This API takes the subinventory and determines whether the
1292 subinventory is under locator control and returns the locator
1293 control code for the subinventory.
1294 -----------------------------------------------------------------------------
1295 */
1296
1297 FUNCTION Sub_Loc_Ctl (
1298 p_subinventory IN VARCHAR2,
1299 p_organization_id IN NUMBER ) RETURN NUMBER IS
1300
1301
1302 l_sub_loc_ctl NUMBER;
1303 l_mtl_sec_inv_rec WSH_DELIVERY_DETAILS_INV.mtl_sec_inv_rec;
1304 l_return_status VARCHAR2(1);
1305 --
1306 l_debug_on BOOLEAN;
1307 --
1308 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUB_LOC_CTL';
1309 --
1310 BEGIN
1311
1312 --
1313 -- Debug Statements
1314 --
1315 --
1316 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1317 --
1318 IF l_debug_on IS NULL
1319 THEN
1320 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1321 END IF;
1322 --
1323 IF l_debug_on THEN
1324 WSH_DEBUG_SV.push(l_module_name);
1325 --
1326 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
1327 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1328 END IF;
1329 --
1330
1331 -- bug 5264874
1332
1333 Get_Sec_Inv_information(p_organization_id => p_organization_id
1334 , p_subinventory_name => p_subinventory
1335 , x_mtl_sec_inv_rec => l_mtl_sec_inv_rec
1336 , x_return_status => l_return_status);
1337 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF;
1340
1341 l_sub_loc_ctl := l_mtl_sec_inv_rec.locator_type;
1342
1343 IF l_debug_on THEN
1344 WSH_DEBUG_SV.pop(l_module_name);
1345 END IF;
1346 --
1347 RETURN l_sub_loc_ctl;
1348 EXCEPTION
1349 WHEN FND_API.G_EXC_ERROR THEN
1350 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1351 WSH_UTIL_CORE.Add_Message(l_return_status);
1352 IF l_debug_on THEN
1353 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1354 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1355 END IF;
1356 END Sub_Loc_Ctl;
1357
1358
1359 /*
1360 -----------------------------------------------------------------------------
1361 FUNCTION : Get_Org_Loc
1362 PARAMETERS : p_organization_id - organization id of line
1363 DESCRIPTION : This API takes the organization determines whether the
1364 organization is under locator control and returns the locator
1365 control code for the organization.
1366 -----------------------------------------------------------------------------
1367 */
1368
1369
1370 FUNCTION Get_Org_Loc (
1371 p_organization_id IN NUMBER) RETURN NUMBER IS
1372
1373 l_mtl_org_param_rec WSH_DELIVERY_DETAILS_INV.mtl_org_param_rec;
1374 l_org_loc_ctl NUMBER;
1375 l_return_status VARCHAR2(1);
1376 --
1377 l_debug_on BOOLEAN;
1378 --
1379 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ORG_LOC';
1380 --
1381 BEGIN
1382
1383 --
1384 -- Debug Statements
1385 --
1386 --
1387 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1388 --
1389 IF l_debug_on IS NULL
1390 THEN
1391 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1392 END IF;
1393 --
1394 IF l_debug_on THEN
1395 WSH_DEBUG_SV.push(l_module_name);
1396 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1397 END IF;
1398 --
1399
1400 -- bug 5264874
1401
1402 Get_Org_Param_information (
1403 p_organization_id => p_organization_id
1404 , x_mtl_org_param_rec => l_mtl_org_param_rec
1405 , x_return_status => l_return_status);
1406
1407 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1408 RAISE FND_API.G_EXC_ERROR;
1409 END IF;
1410
1411 l_org_loc_ctl := l_mtl_org_param_rec.stock_locator_control_code;
1412
1413 IF l_debug_on THEN
1414 WSH_DEBUG_SV.log(l_module_name, 'l_org_loc_ctl',l_org_loc_ctl);
1415 WSH_DEBUG_SV.pop(l_module_name);
1416 END IF;
1417 --
1418 RETURN l_org_loc_ctl;
1419
1420 EXCEPTION
1421 WHEN FND_API.G_EXC_ERROR THEN
1422 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1423 WSH_UTIL_CORE.Add_Message(l_return_status);
1424 IF l_debug_on THEN
1425 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1426 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1427 END IF;
1428
1429 END Get_Org_Loc;
1430
1431
1432 /*
1433 -----------------------------------------------------------------------------
1434 PROCEDURE : Default_Subinventory
1435 PARAMETERS : p_org_id - organization_id
1436 p_inv_item_id - inventory_item_id on the line
1437 x_default_sub - default subinventory for the item/org
1438 x_return_status - return status of the API
1439 DESCRIPTION : Get Default Sub for this item/org if it is defined else it
1440 returns null.
1441 -----------------------------------------------------------------------------
1442 */
1443
1444 PROCEDURE Default_Subinventory (
1445 p_org_id IN NUMBER,
1446 p_inv_item_id IN NUMBER,
1447 x_default_sub OUT NOCOPY VARCHAR2,
1448 x_return_status OUT NOCOPY VARCHAR2) IS
1449
1450 CURSOR Default_Sub IS
1451 SELECT mtlsub.secondary_inventory_name
1452 FROM mtl_item_sub_defaults mtlisd,
1453 mtl_secondary_inventories mtlsub
1454 WHERE mtlisd.inventory_item_id = p_inv_item_id
1455 AND mtlisd.organization_id = p_org_id
1456 AND mtlisd.default_type = 1
1457 AND mtlsub.organization_id = mtlisd.organization_id
1458 AND mtlsub.secondary_inventory_name = mtlisd.subinventory_code
1459 AND mtlsub.quantity_tracked = 1
1460 AND trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate));
1461
1462 l_dflt_sub VARCHAR2(30);
1463
1464 --
1465 l_debug_on BOOLEAN;
1466 --
1467 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_SUBINVENTORY';
1468 --
1469 BEGIN
1470
1471 --
1472 -- Debug Statements
1473 --
1474 --
1475 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1476 --
1477 IF l_debug_on IS NULL
1478 THEN
1479 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1480 END IF;
1481 --
1482 IF l_debug_on THEN
1483 WSH_DEBUG_SV.push(l_module_name);
1484 --
1485 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
1486 WSH_DEBUG_SV.log(l_module_name,'P_INV_ITEM_ID',P_INV_ITEM_ID);
1487 END IF;
1488 --
1489 OPEN Default_Sub;
1490 FETCH Default_Sub into l_dflt_sub;
1491
1492 IF Default_Sub%NOTFOUND THEN
1493 CLOSE Default_Sub;
1494 x_default_sub := NULL;
1495 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1496 --
1497 -- Debug Statements
1498 --
1499 IF l_debug_on THEN
1500 WSH_DEBUG_SV.pop(l_module_name);
1501 END IF;
1502 --
1503 return;
1504 END IF;
1505
1506 IF Default_Sub%ISOPEN THEN
1507 CLOSE Default_Sub;
1508 END IF;
1509
1510 x_default_sub := l_dflt_sub;
1511 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1512
1513 --
1514 -- Debug Statements
1515 --
1516 IF l_debug_on THEN
1517 WSH_DEBUG_SV.pop(l_module_name);
1518 END IF;
1519 --
1520 EXCEPTION
1521
1522 WHEN Others THEN
1523 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.Default_Subinventory');
1524 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1525
1526 --
1527 -- Debug Statements
1528 --
1529 IF l_debug_on THEN
1530 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1531 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1532 END IF;
1533 --
1534 END Default_Subinventory;
1535
1536
1537 /*
1538 -----------------------------------------------------------------------------
1539 FUNCTION : DEFAULT_LOCATOR
1540 PARAMETERS : p_organization_id - input org id
1541 p_inv_item_id - input item_id
1542 p_subinventory - input sub id
1543 p_loc_restricted_flag - Y or N. If Y will ensure location is
1544 in predefined list
1545 x_locator_id - output default locator id.
1546 x_return_status - return status of API.
1547 DESCRIPTION : Retrieves default locator. If none exists then it returns null.
1548 -----------------------------------------------------------------------------
1549 */
1550
1551
1552 FUNCTION DEFAULT_LOCATOR
1553 (p_organization_id IN NUMBER,
1554 p_inv_item_id IN NUMBER,
1555 p_subinventory IN VARCHAR2,
1556 p_loc_restricted_flag IN VARCHAR2) RETURN NUMBER IS
1557
1558 CURSOR Default_Locator IS
1559 SELECT mtldl.locator_id
1560 FROM mtl_item_loc_defaults mtldl
1561 WHERE mtldl.inventory_item_id = p_inv_item_id
1562 and mtldl.organization_id = p_organization_id
1563 and mtldl.default_type = 1
1564 and mtldl.subinventory_code = p_subinventory
1565 and ( nvl(p_loc_restricted_flag, 'N') = 'N'
1566 OR
1567 (nvl(p_loc_restricted_flag, 'N') = 'Y'
1568 and nvl(mtldl.locator_id, -1) in
1569 (select mtlsls.secondary_locator
1570 from mtl_secondary_locators mtlsls
1571 where mtlsls.organization_id = p_organization_id
1572 and mtlsls.inventory_item_id = p_inv_item_id
1573 and mtlsls.subinventory_code = p_subinventory)));
1574
1575 dflt_locator_id NUMBER;
1576
1577 --
1578 l_debug_on BOOLEAN;
1579 --
1580 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_LOCATOR';
1581 --
1582 BEGIN
1583
1584 --
1585 -- Debug Statements
1586 --
1587 --
1588 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1589 --
1590 IF l_debug_on IS NULL
1591 THEN
1592 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1593 END IF;
1594 --
1595 IF l_debug_on THEN
1596 WSH_DEBUG_SV.push(l_module_name);
1597 --
1598 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1599 WSH_DEBUG_SV.log(l_module_name,'P_INV_ITEM_ID',P_INV_ITEM_ID);
1600 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
1601 WSH_DEBUG_SV.log(l_module_name,'P_LOC_RESTRICTED_FLAG',P_LOC_RESTRICTED_FLAG);
1602 END IF;
1603 --
1604 OPEN Default_Locator;
1605 FETCH Default_Locator INTO dflt_locator_id;
1606
1607 IF Default_Locator%NOTFOUND THEN
1608 CLOSE Default_Locator;
1609 --
1610 -- Debug Statements
1611 --
1612 IF l_debug_on THEN
1613 WSH_DEBUG_SV.pop(l_module_name);
1614 END IF;
1615 --
1616 RETURN NULL;
1617 END IF;
1618
1619 IF Default_Locator%ISOPEN THEN
1620 CLOSE Default_Locator;
1621 END IF;
1622
1623 --
1624 -- Debug Statements
1625 --
1626 IF l_debug_on THEN
1627 WSH_DEBUG_SV.pop(l_module_name);
1628 END IF;
1629 --
1630 RETURN (dflt_locator_id);
1631
1632 END default_locator;
1633
1634
1635 /*
1636 -----------------------------------------------------------------------------
1637 FUNCTION : Locator_Ctl_Code
1638 PARAMETERS : p_organization_id - input org id
1639 p_restrict_loc - restrict locators code
1640 p_org_loc_code - loc control code for org
1641 p_sub_loc_code - loc control code for sub
1642 p_item_loc_code - loc control code for item
1643 DESCRIPTION : Determines the locator control code based on the three loc
1644 control codes and returns the governing loc control code.
1645 -----------------------------------------------------------------------------
1646 */
1647
1648
1649 FUNCTION Locator_Ctl_Code (
1650 p_org_id IN NUMBER,
1651 p_restrict_loc IN NUMBER,
1652 p_org_loc_code IN NUMBER,
1653 p_sub_loc_code IN NUMBER,
1654 p_item_loc_code IN NUMBER ) RETURN NUMBER IS
1655
1656
1657 prespecified CONSTANT NUMBER := 2;
1658 dynamic CONSTANT NUMBER := 3;
1659
1660 l_neg_inv_code NUMBER;
1661 l_mtl_org_param_rec WSH_DELIVERY_DETAILS_INV.mtl_org_param_rec;
1662 l_return_status VARCHAR2(1);
1663 --
1664 l_debug_on BOOLEAN;
1665 --
1666 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCATOR_CTL_CODE';
1667 --
1668 BEGIN
1669
1670
1671 -- Hverddin 12-SEP-2000 OPM Start Of Changes
1672
1673 --
1674 -- Debug Statements
1675 --
1676 --
1677 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1678 --
1679 IF l_debug_on IS NULL
1680 THEN
1681 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1682 END IF;
1683 --
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.push(l_module_name);
1686 --
1687 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
1688 WSH_DEBUG_SV.log(l_module_name,'P_RESTRICT_LOC',P_RESTRICT_LOC);
1689 WSH_DEBUG_SV.log(l_module_name,'P_ORG_LOC_CODE',P_ORG_LOC_CODE);
1690 WSH_DEBUG_SV.log(l_module_name,'P_SUB_LOC_CODE',P_SUB_LOC_CODE);
1691 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_LOC_CODE',P_ITEM_LOC_CODE);
1692 END IF;
1693 --
1694 --
1695 -- Debug Statements
1696 --
1697 -- HW OPMCONV. Removed code forking
1698
1699 -- bug 5264874
1700 Get_Org_Param_information (
1701 p_organization_id => p_org_id
1702 , x_mtl_org_param_rec => l_mtl_org_param_rec
1703 , x_return_status => l_return_status);
1704
1705 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1706 RAISE FND_API.G_EXC_ERROR;
1707 END IF;
1708
1709 l_neg_inv_code := l_mtl_org_param_rec.negative_inv_receipt_code;
1710
1711 IF ( NVL(p_org_loc_code, 4) <> 4 ) THEN
1712 -- honor ORG level locator control
1713 IF ( p_org_loc_code <> 3 ) THEN
1714 --
1715 -- Debug Statements
1716 --
1717 IF l_debug_on THEN
1718 WSH_DEBUG_SV.pop(l_module_name);
1719 END IF;
1720 --
1721 RETURN p_org_loc_code;
1722 ELSE
1723 -- p_org_loc_code = 3 : Dynamic entry locator control
1724
1725 IF (p_restrict_loc = 1) THEN
1726 -- restrictive to pre-defined list then return Prespecified
1727 --
1728 -- Debug Statements
1729 --
1730 IF l_debug_on THEN
1731 WSH_DEBUG_SV.pop(l_module_name);
1732 END IF;
1733 --
1734 RETURN prespecified;
1735 ELSIF (l_neg_inv_code = 1) THEN
1736 --
1737 -- Debug Statements
1738 --
1739 IF l_debug_on THEN
1740 WSH_DEBUG_SV.pop(l_module_name);
1741 END IF;
1742 --
1743 RETURN dynamic;
1744 ELSE
1745 -- not allow negative balance in this organization therefore
1746 -- can not create locator dynamically for issue
1747 --
1748 -- Debug Statements
1749 --
1750 IF l_debug_on THEN
1751 WSH_DEBUG_SV.pop(l_module_name);
1752 END IF;
1753 --
1754 RETURN prespecified;
1755 END IF;
1756 END IF;
1757 ELSIF ( NVL(p_sub_loc_code, 5) <> 5 ) THEN
1758 -- honor SUB level locator control code
1759
1760 IF ( p_sub_loc_code <> 3 ) THEN
1761 --
1762 -- Debug Statements
1763 --
1764 IF l_debug_on THEN
1765 WSH_DEBUG_SV.pop(l_module_name);
1766 END IF;
1767 --
1768 RETURN p_sub_loc_code;
1769 ELSE
1770 -- p_sub_loc_code = 3 : Dynamic entry locator control
1771 IF ( p_restrict_loc = 1) THEN
1772 -- restrictive to pre-defined list then return Prespecified
1773 --
1774 -- Debug Statements
1775 --
1776 IF l_debug_on THEN
1777 WSH_DEBUG_SV.pop(l_module_name);
1778 END IF;
1779 --
1780 RETURN prespecified;
1781 ELSIF (l_neg_inv_code = 1) THEN
1782 --
1783 -- Debug Statements
1784 --
1785 IF l_debug_on THEN
1786 WSH_DEBUG_SV.pop(l_module_name);
1787 END IF;
1788 --
1789 RETURN dynamic;
1790 ELSE
1791 -- not allow negative balance in this organization therefore
1792 -- can not create locator dynamically for issue
1793 --
1794 -- Debug Statements
1795 --
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.pop(l_module_name);
1798 END IF;
1799 --
1800 RETURN prespecified;
1801 END IF;
1802 END IF;
1803 ELSE
1804 -- use item level locator control code
1805
1806 IF ( NVL(p_item_loc_code, 1) <> 3 ) THEN
1807 --
1808 -- Debug Statements
1809 --
1810 IF l_debug_on THEN
1811 WSH_DEBUG_SV.pop(l_module_name);
1812 END IF;
1813 --
1814 RETURN p_item_loc_code;
1815 ELSE
1816 -- p_item_loc_code = 3 : Dynamic entry locator control
1817 IF ( p_restrict_loc = 1) THEN
1818 -- restrictive to pre-defined list then return Prespecified
1819 --
1820 -- Debug Statements
1821 --
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.pop(l_module_name);
1824 END IF;
1825 --
1826 RETURN prespecified;
1827 ELSIF (l_neg_inv_code = 1) THEN
1828 --
1829 -- Debug Statements
1830 --
1831 IF l_debug_on THEN
1832 WSH_DEBUG_SV.pop(l_module_name);
1833 END IF;
1834 --
1835 RETURN dynamic;
1836 ELSE
1837 -- not allow negative balance in this organization therefore
1838 -- can not create locator dynamically for issue
1839 --
1840 -- Debug Statements
1841 --
1842 IF l_debug_on THEN
1843 WSH_DEBUG_SV.pop(l_module_name);
1844 END IF;
1845 --
1846 RETURN prespecified;
1847 END IF;
1848 END IF;
1849 END IF;
1850
1851 EXCEPTION
1852
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1855 WSH_UTIL_CORE.Add_Message(l_return_status);
1856 IF l_debug_on THEN
1857 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1858 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1859 END IF;
1860
1861 WHEN others THEN
1862 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Locator_Ctl_Code');
1863
1864 --
1865 -- Debug Statements
1866 --
1867 IF l_debug_on THEN
1868 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1870 END IF;
1871 --
1872 END Locator_Ctl_Code;
1873
1874
1875
1876 /*
1877 -----------------------------------------------------------------------------
1878 PROCEDURE : Mark_Serial_Number
1879 PARAMETERS : p_delivery_detail_id - delivery detail id or container id
1880 p_serial_number - serial number in case of single quantity
1881 p_transaction_temp_id - transaction temp id for multiple
1882 quantity of serial numbers.
1883 x_return_status - return status of the API
1884 DESCRIPTION : Call Inventory's serial number mark API.
1885 - frontport bug 5028993 : change from here
1886 - before fix : Uses the delivery
1887 detail id as the group mark id, temp lot id and temp id to
1888 identify the serial numbers in mtl serial numbers.
1889 - after fix : When serial_number is
1890 not null, use the transaction_temp_id created by using the
1891 sequence mtl_material_transactions_s as group_mark_id.
1892 - frontport bug 5028993 : change to here
1893 If the qty
1894 is greater than 1, then it uses the transaction temp id to
1895 fetch all the serial number ranges and then calls the mark API
1896 for each of the ranges.
1897 -----------------------------------------------------------------------------
1898 */
1899
1900 PROCEDURE Mark_Serial_Number (
1901 p_delivery_detail_id IN NUMBER,
1902 p_serial_number IN VARCHAR2,
1903 p_transaction_temp_id IN NUMBER,
1904 x_return_status OUT NOCOPY VARCHAR2) IS
1905
1906 CURSOR Fetch_Detail_Info IS
1907 SELECT Inventory_Item_Id, Organization_Id
1908 FROM WSH_DELIVERY_DETAILS
1909 WHERE delivery_detail_id = p_delivery_detail_id;
1910
1911 CURSOR Fetch_Serial_Ranges IS
1912 SELECT Fm_Serial_Number, To_Serial_Number,
1913 Serial_Prefix
1914 FROM MTL_SERIAL_NUMBERS_TEMP
1915 WHERE transaction_temp_id = p_transaction_temp_id;
1916
1917 -- Bug 5028993: needs to pass transaction_temp_id
1918 CURSOR c_temp_id IS
1919 SELECT mtl_material_transactions_s.nextval
1920 FROM dual;
1921
1922 l_inv_item_id NUMBER;
1923 l_org_id NUMBER;
1924 l_transaction_temp_id NUMBER;
1925
1926 l_success NUMBER;
1927 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1928
1929
1930 --
1931 l_debug_on BOOLEAN;
1932 --
1933 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MARK_SERIAL_NUMBER';
1934 --
1935 BEGIN
1936
1937 --
1938 -- Debug Statements
1939 --
1940 --
1941 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1942 --
1943 IF l_debug_on IS NULL
1944 THEN
1945 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1946 END IF;
1947 --
1948 IF l_debug_on THEN
1949 WSH_DEBUG_SV.push(l_module_name);
1950 --
1951 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1952 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER',P_SERIAL_NUMBER);
1953 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TEMP_ID',P_TRANSACTION_TEMP_ID);
1954 END IF;
1955 --
1956 OPEN Fetch_Detail_Info;
1957
1958 FETCH Fetch_Detail_Info INTO l_inv_item_id, l_org_id;
1959
1960 IF Fetch_Detail_Info%NOTFOUND THEN
1961 CLOSE Fetch_Detail_Info;
1962 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_ID');
1963 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1964 WSH_UTIL_CORE.Add_Message(x_return_status);
1965 --
1966 -- Debug Statements
1967 --
1968 IF l_debug_on THEN
1969 WSH_DEBUG_SV.pop(l_module_name);
1970 END IF;
1971 --
1972 return;
1973 END IF;
1974
1975 IF Fetch_Detail_Info%ISOPEN THEN
1976 CLOSE Fetch_Detail_Info;
1977 END IF;
1978
1979 IF p_serial_number IS NOT NULL AND p_transaction_temp_id IS NOT NULL THEN
1980 FND_MESSAGE.SET_NAME('WSH','WSH_DET_TRX_SERIAL_INVALID');
1981 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1982 WSH_UTIL_CORE.Add_Message(x_return_status);
1983 --
1984 -- Debug Statements
1985 --
1986 IF l_debug_on THEN
1987 WSH_DEBUG_SV.pop(l_module_name);
1988 END IF;
1989 --
1990 return;
1991 END IF;
1992
1993 IF p_serial_number IS NOT NULL THEN
1994
1995 -- bug 5028993: needs to pass transaction_temp_id
1996 OPEN c_temp_id;
1997 FETCH c_temp_id INTO l_transaction_temp_id;
1998 IF c_temp_id%NOTFOUND THEN
1999 --{
2000 CLOSE c_temp_id;
2001 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2002 --
2003 IF l_debug_on THEN
2004 WSH_DEBUG_SV.log(l_module_name,'Return Status is error',x_return_status);
2005 WSH_DEBUG_SV.pop(l_module_name);
2006 END IF;
2007 --
2008 return;
2009 --}
2010 END IF;
2011 CLOSE c_temp_id;
2012 --
2013 -- bug 5028993: End
2014
2015 --
2016 -- Debug Statements
2017 --
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.log(l_module_name,'transaction_temp_id',l_transaction_temp_id);
2020 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_MARK_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
2021 END IF;
2022 --
2023 -- bug 5028993
2024 -- if serial_number is not null, while calling mark_serial,
2025 -- need to use l_transaction_temp_id and not the delivery_detail_id
2026 Serial_Check.Inv_Mark_Serial(
2027 p_serial_number,
2028 p_serial_number,
2029 l_inv_item_id,
2030 l_org_id,
2031 l_transaction_temp_id,
2032 NULL,
2033 NULL,
2034 l_success);
2035
2036
2037 IF l_success < 0 THEN
2038 FND_MESSAGE.SET_NAME('WSH','WSH_SERIAL_MARK_ERROR');
2039 FND_MESSAGE.SET_TOKEN('SERIAL_NUM',p_serial_number);
2040 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2041 WSH_UTIL_CORE.Add_Message(x_return_status);
2042 --
2043 -- Debug Statements
2044 --
2045 IF l_debug_on THEN
2046 WSH_DEBUG_SV.pop(l_module_name);
2047 END IF;
2048 --
2049 return;
2050 END IF;
2051
2052 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2053 --
2054 -- Debug Statements
2055 --
2056 IF l_debug_on THEN
2057 WSH_DEBUG_SV.pop(l_module_name);
2058 END IF;
2059 --
2060 return;
2061
2062 END IF;
2063
2064 IF p_transaction_temp_id IS NOT NULL THEN
2065
2066 FOR c IN Fetch_Serial_Ranges LOOP
2067 EXIT WHEN Fetch_Serial_Ranges%NOTFOUND;
2068
2069 --
2070 -- Debug Statements
2071 --
2072 IF l_debug_on THEN
2073 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_MARK_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
2074 END IF;
2075 --
2076 -- Fix for bug 2762219.
2077 -- if transaction_Temp_id is not null, while calling mark_serial,
2078 -- need to Use p_transaction_Temp_id and not the delivery_detail_id
2079 Serial_Check.Inv_Mark_Serial(
2080 c.fm_serial_number,
2081 c.to_serial_number,
2082 l_inv_item_id,
2083 l_org_id,
2084 p_transaction_temp_id, -- Fix for Bug 2762219.
2085 p_transaction_temp_id,
2086 p_transaction_temp_id,
2087 l_success);
2088
2089 IF l_success < 0 THEN
2090 FND_MESSAGE.SET_NAME('WSH','WSH_SER_RANGE_MK_ERROR');
2091 FND_MESSAGE.SET_TOKEN('FM_SERIAL',c.fm_serial_number);
2092 FND_MESSAGE.SET_TOKEN('TO_SERIAL',c.to_serial_number);
2093 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2094 WSH_UTIL_CORE.Add_Message(l_return_status);
2095 END IF;
2096
2097 END LOOP;
2098
2099 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2100 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2101 --
2102 -- Debug Statements
2103 --
2104 IF l_debug_on THEN
2105 WSH_DEBUG_SV.pop(l_module_name);
2106 END IF;
2107 --
2108 return;
2109 END IF;
2110
2111 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2112
2113 END IF;
2114
2115 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2116
2117 --
2118 -- Debug Statements
2119 --
2120 IF l_debug_on THEN
2121 WSH_DEBUG_SV.pop(l_module_name);
2122 END IF;
2123 --
2124 EXCEPTION
2125
2126 WHEN OTHERS THEN
2127 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Mark_Serial_Number');
2128 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2129
2130 --
2131 -- Debug Statements
2132 --
2133 IF l_debug_on THEN
2134 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2135 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2136 END IF;
2137 --
2138 END Mark_Serial_Number;
2139
2140
2141 /*
2142 -----------------------------------------------------------------------------
2143 PROCEDURE : Unmark_Serial_Number
2144 PARAMETERS : p_delivery_detail_id - delivery detail id or container id
2145 p_serial_number_code - serial number code for the inventory
2146 item on the line.
2147 p_serial_number - serial number in case of single quantity
2148 p_transaction_temp_id - transaction temp id for multiple
2149 quantity of serial numbers.
2150 x_return_status - return status of the API
2151 p_inventory_item_id - inventory item
2152 DESCRIPTION : Call Inventory's serial number unmark API.
2153 Inventory needs only the serial number (From and To) and
2154 the inventory_item_id to unmark the Serial Number. All other
2155 parameters are passed as Null to the api.
2156 If the qty is greater than 1, then it uses the transaction
2157 temp id to fetch all the serial number ranges and then
2158 calls the ummark API for each of the ranges.
2159 -----------------------------------------------------------------------------
2160 */
2161
2162 PROCEDURE Unmark_Serial_Number (
2163 p_delivery_detail_id IN NUMBER,
2164 p_serial_number_code IN NUMBER,
2165 p_serial_number IN VARCHAR2,
2166 p_transaction_temp_id IN NUMBER,
2167 x_return_status OUT NOCOPY VARCHAR2,
2168 p_inventory_item_id IN NUMBER DEFAULT NULL) IS
2169
2170 CURSOR Fetch_Serial_Ranges IS
2171 SELECT Fm_Serial_Number, To_Serial_Number,
2172 Serial_Prefix
2173 FROM MTL_SERIAL_NUMBERS_TEMP
2174 WHERE transaction_temp_id = p_transaction_temp_id
2175 FOR UPDATE OF fm_serial_number NOWAIT;
2176
2177 CURSOR Fetch_Item IS
2178 SELECT inventory_item_id
2179 FROM wsh_delivery_details
2180 WHERE delivery_detail_id = p_delivery_detail_id;
2181
2182 l_success NUMBER;
2183 l_inventory_item_id NUMBER;
2184
2185 --
2186 l_debug_on BOOLEAN;
2187 --
2188 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNMARK_SERIAL_NUMBER';
2189 --
2190 BEGIN
2191
2192 --
2193 -- Debug Statements
2194 --
2195 --
2196 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2197 --
2198 IF l_debug_on IS NULL
2199 THEN
2200 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2201 END IF;
2202 --
2203 IF l_debug_on THEN
2204 WSH_DEBUG_SV.push(l_module_name);
2205 --
2206 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2207 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER_CODE',P_SERIAL_NUMBER_CODE);
2208 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER',P_SERIAL_NUMBER);
2209 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TEMP_ID',P_TRANSACTION_TEMP_ID);
2210 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2211 END IF;
2212 --
2213 IF p_serial_number_code IS NULL OR p_serial_number_code = 1 THEN
2214 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_SER_CODE_UNMARK');
2215 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2216 WSH_UTIL_CORE.Add_Message(x_return_status);
2217 --
2218 -- Debug Statements
2219 --
2220 IF l_debug_on THEN
2221 WSH_DEBUG_SV.pop(l_module_name);
2222 END IF;
2223 --
2224 return;
2225 END IF;
2226
2227 IF p_serial_number IS NOT NULL AND p_transaction_temp_id IS NOT NULL THEN
2228 FND_MESSAGE.SET_NAME('WSH','WSH_DET_TRX_SERIAL_INVALID');
2229 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2230 WSH_UTIL_CORE.Add_Message(x_return_status);
2231 --
2232 -- Debug Statements
2233 --
2234 IF l_debug_on THEN
2235 WSH_DEBUG_SV.pop(l_module_name);
2236 END IF;
2237 --
2238 return;
2239 END IF;
2240
2241 IF p_inventory_item_id IS NULL THEN
2242 OPEN Fetch_Item;
2243 FETCH Fetch_Item INTO l_inventory_item_id;
2244 CLOSE Fetch_Item;
2245 ELSE
2246 l_inventory_item_id := p_inventory_item_id;
2247 END IF;
2248
2249 IF p_serial_number IS NOT NULL THEN
2250
2251 --
2252 -- Debug Statements
2253 --
2254 IF l_debug_on THEN
2255 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_UNMARK_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
2256 END IF;
2257 --
2258 Serial_Check.Inv_Unmark_Serial(
2259 p_serial_number,
2260 p_serial_number,
2261 NULL,
2262 NULL,
2263 NULL,
2264 NULL,
2265 l_inventory_item_id);
2266
2267 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2268 --
2269 -- Debug Statements
2270 --
2271 IF l_debug_on THEN
2272 WSH_DEBUG_SV.pop(l_module_name);
2273 END IF;
2274 --
2275 return;
2276
2277 END IF;
2278
2279 IF p_transaction_temp_id IS NOT NULL THEN
2280
2281 FOR c IN Fetch_Serial_Ranges LOOP
2282 EXIT WHEN Fetch_Serial_Ranges%NOTFOUND;
2283
2284 -- if transaction temp id is not null it means that the number of
2285 -- serial numbers entered is greater than 1. this also implies that
2286 -- serial numbers were entered using the serial entry window
2287 -- it seems like the serial entry window uses the transaction temp id
2288 -- to mark the serial numbers as compared to the delivery detail id
2289 -- used when the mark is called with a single serial number. so use
2290 -- the transaction temp id to unmark.
2291
2292 --
2293 -- Debug Statements
2294 --
2295 IF l_debug_on THEN
2296 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_UNMARK_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
2297 END IF;
2298 --
2299 Serial_Check.Inv_Unmark_Serial(
2300 c.fm_Serial_number ,
2301 c.to_Serial_number ,
2302 NULL,
2303 NULL,
2304 NULL,
2305 NULL,
2306 l_inventory_item_id);
2307
2308 END LOOP;
2309
2310 DELETE FROM MTL_SERIAL_NUMBERS_TEMP
2311 WHERE transaction_temp_id = p_transaction_temp_id;
2312
2313 IF SQLCODE <> 0 THEN
2314 FND_MESSAGE.SET_NAME('WSH','WSH_SER_TEMP_CLEAR_ERROR');
2315 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2316 WSH_UTIL_CORE.Add_Message(x_return_status);
2317 --
2318 -- Debug Statements
2319 --
2320 IF l_debug_on THEN
2321 WSH_DEBUG_SV.pop(l_module_name);
2322 END IF;
2323 --
2324 return;
2325 END IF;
2326
2327 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2328 --
2329 -- Debug Statements
2330 --
2331 IF l_debug_on THEN
2332 WSH_DEBUG_SV.pop(l_module_name);
2333 END IF;
2334 --
2335 return;
2336
2337 END IF;
2338
2339 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2340
2341 --
2342 -- Debug Statements
2343 --
2344 IF l_debug_on THEN
2345 WSH_DEBUG_SV.pop(l_module_name);
2346 END IF;
2347 --
2348 EXCEPTION
2349
2350 WHEN OTHERS THEN
2351 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number');
2352 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2353
2354 --
2355 -- Debug Statements
2356 --
2357 IF l_debug_on THEN
2358 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2359 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2360 END IF;
2361 --
2362 END Unmark_Serial_Number;
2363
2364
2365
2366 /*
2367 -----------------------------------------------------------------------------
2368
2369 Procedure : validate_locator
2370 Parameters : p_locator_id
2371 p_inventory_item
2372 p_sub
2373 p_transaction_type_id
2374 p_object_type
2375 Description : This function returns a boolean value to
2376 indicate if the locator is valid in the context of inventory
2377 and subinventory
2378
2379 -----------------------------------------------------------------------------
2380 */
2381
2382
2383 PROCEDURE Validate_Locator(
2384 p_locator_id IN NUMBER,
2385 p_inventory_item_id IN NUMBER,
2386 p_organization_id IN NUMBER,
2387 p_subinventory IN VARCHAR2,
2388 p_transaction_type_id IN NUMBER DEFAULT NULL,
2389 p_object_type IN VARCHAR2 DEFAULT NULL,
2390 x_return_status OUT NOCOPY VARCHAR2,
2391 x_result OUT NOCOPY BOOLEAN) IS
2392
2393 l_locator INV_VALIDATE.Locator;
2394 l_result NUMBER;
2395
2396 --
2397 l_debug_on BOOLEAN;
2398 --
2399 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOCATOR';
2400 --
2401 BEGIN
2402 --
2403 -- Debug Statements
2404 --
2405 --
2406 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2407 --
2408 IF l_debug_on IS NULL
2409 THEN
2410 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2411 END IF;
2412 --
2413 IF l_debug_on THEN
2414 WSH_DEBUG_SV.push(l_module_name);
2415 --
2416 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
2417 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2418 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2419 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
2420 WSH_DEBUG_SV.log(l_module_name,'p_transaction_type_id',p_transaction_type_id);
2421 WSH_DEBUG_SV.log(l_module_name,'p_object_type',p_object_type);
2422 END IF;
2423 --
2424 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2425
2426 g_org.organization_id := p_organization_id;
2427 --
2428 -- Debug Statements
2429 --
2430 IF l_debug_on THEN
2431 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
2432 END IF;
2433 --
2434 l_result := INV_VALIDATE.organization(g_org);
2435 IF (l_result <> INV_VALIDATE.T) THEN
2436 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2437 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
2438 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2439 WSH_UTIL_CORE.Add_Message(x_return_status);
2440 END IF;
2441 g_item.inventory_item_id := p_inventory_item_id;
2442 --
2443 -- Debug Statements
2444 --
2445 IF l_debug_on THEN
2446 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
2447 END IF;
2448 --
2449 l_result := wsh_delivery_details_inv.inventory_item(
2450 p_item => g_item,
2451 p_org => g_org);
2452 IF (l_result <> INV_VALIDATE.T) THEN
2453 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2454 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
2455 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2456 WSH_UTIL_CORE.Add_Message(x_return_status);
2457 END IF;
2458
2459 g_sub.secondary_inventory_name := p_subinventory;
2460 --
2461 -- Debug Statements
2462 --
2463 IF l_debug_on THEN
2464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.FROM_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2465 END IF;
2466 --
2467 l_result := INV_VALIDATE.From_subinventory(
2468 p_sub => g_sub,
2469 p_item => g_item,
2470 p_org => g_org,
2471 p_trx_type_id => p_transaction_type_id,
2472 p_object_type => p_object_type ,
2473 p_acct_txn => 0);
2474 IF (l_result <> INV_VALIDATE.T) THEN
2475 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2476 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Subinventory');
2477 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2478 WSH_UTIL_CORE.Add_Message(x_return_status);
2479 END IF;
2480 l_locator.inventory_location_id := p_locator_id;
2481
2482 --
2483 -- Debug Statements
2484 --
2485 IF l_debug_on THEN
2486 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATELOCATOR',WSH_DEBUG_SV.C_PROC_LEVEL);
2487 END IF;
2488 --
2489 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
2490 l_result := INV_VALIDATE.ValidateLocator(
2491 p_locator => l_locator,
2492 p_org => g_org,
2493 p_sub => g_sub,
2494 p_item => g_item,
2495 p_trx_type_id => p_transaction_type_id,
2496 p_object_type => p_object_type);
2497 ELSE
2498 l_result := INV_VALIDATE.ValidateLocator(
2499 p_locator => l_locator,
2500 p_org => g_org,
2501 p_sub => g_sub,
2502 p_item => g_item);
2503 END IF;
2504 IF (l_result = INV_VALIDATE.T) THEN
2505 x_result := TRUE;
2506 ELSE
2507 x_result := FALSE;
2508 END IF;
2509
2510 --
2511 -- Debug Statements
2512 --
2513 IF l_debug_on THEN
2514 WSH_DEBUG_SV.pop(l_module_name);
2515 END IF;
2516 --
2517 EXCEPTION
2518
2519 WHEN others THEN
2520 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2521 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_locator');
2522
2523 --
2524 -- Debug Statements
2525 --
2526 IF l_debug_on THEN
2527 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2528 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2529 END IF;
2530 --
2531 END Validate_Locator;
2532
2533
2534 /*
2535 -----------------------------------------------------------------------------
2536
2537 Procedure : Validate_Revision
2538 Parameters : p_revision
2539 p_organization_id
2540 p_inventory_item_id
2541 x_return_status
2542 Description : Validate item in context of organization_id
2543 Return TRUE if validate item successfully
2544 FALSE otherwise
2545 -----------------------------------------------------------------------------
2546 */
2547
2548
2549 PROCEDURE Validate_Revision(
2550 p_revision IN VARCHAR2,
2551 p_organization_id IN NUMBER,
2552 p_inventory_item_id IN NUMBER,
2553 x_return_status OUT NOCOPY VARCHAR2,
2554 x_result OUT NOCOPY BOOLEAN ) IS
2555
2556 l_result NUMBER;
2557
2558 --
2559 l_debug_on BOOLEAN;
2560 --
2561 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_REVISION';
2562 --
2563 BEGIN
2564 --
2565 -- Debug Statements
2566 --
2567 --
2568 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2569 --
2570 IF l_debug_on IS NULL
2571 THEN
2572 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2573 END IF;
2574 --
2575 IF l_debug_on THEN
2576 WSH_DEBUG_SV.push(l_module_name);
2577 --
2578 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
2579 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2580 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2581 END IF;
2582 --
2583 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2584 g_org.organization_id := p_organization_id;
2585 --
2586 -- Debug Statements
2587 --
2588 IF l_debug_on THEN
2589 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
2590 END IF;
2591 --
2592 l_result := INV_VALIDATE.organization(g_org);
2593 IF (l_result <> INV_VALIDATE.T) THEN
2594 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2595 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
2596 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2597 WSH_UTIL_CORE.Add_Message(x_return_status);
2598 END IF;
2599 g_item.inventory_item_id := p_inventory_item_id;
2600 --
2601 -- Debug Statements
2602 --
2603 IF l_debug_on THEN
2604 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
2605 END IF;
2606 --
2607 l_result := wsh_delivery_details_inv.inventory_item(
2608 p_item => g_item,
2609 p_org => g_org);
2610 IF (l_result <> INV_VALIDATE.T) THEN
2611 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2612 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
2613 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2614 WSH_UTIL_CORE.Add_Message(x_return_status);
2615 END IF;
2616
2617 --
2618 -- Debug Statements
2619 --
2620 IF l_debug_on THEN
2621 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.REVISION',WSH_DEBUG_SV.C_PROC_LEVEL);
2622 END IF;
2623 --
2624 l_result := INV_VALIDATE.Revision(
2625 p_revision => p_revision,
2626 p_org => g_org,
2627 p_item => g_item);
2628
2629 IF (l_result = INV_VALIDATE.T) THEN
2630 x_result := TRUE;
2631 ELSE
2632 x_result := FALSE;
2633 END IF;
2634
2635
2636 --
2637 -- Debug Statements
2638 --
2639 IF l_debug_on THEN
2640 WSH_DEBUG_SV.pop(l_module_name);
2641 END IF;
2642 --
2643 EXCEPTION
2644 WHEN others THEN
2645 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2646 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_revision');
2647
2648 --
2649 -- Debug Statements
2650 --
2651 IF l_debug_on THEN
2652 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2653 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2654 END IF;
2655 --
2656 END Validate_Revision;
2657
2658
2659 /*
2660 -----------------------------------------------------------------------------
2661
2662 Procedure : Validate_Subinventory
2663 Parameters : p_subinventory
2664 p_organization_id
2665 p_inventory_item_id
2666 p_transaction_type_id
2667 p_object_type
2668 x_return_status
2669 p_to_subinventory
2670 Description : Validate item in context of organization_id
2671 Return TRUE if validate item successfully
2672 FALSE otherwise
2673 p_to_subinventory is defaulted to NULL, if it is NULL
2674 p_subinventory will be validated as from_subinventory.
2675 Else, p_to_subinventory will be validated as to_subinvnetory.
2676
2677 -----------------------------------------------------------------------------
2678 */
2679
2680
2681 PROCEDURE Validate_Subinventory(
2682 p_subinventory IN VARCHAR2,
2683 p_organization_id IN NUMBER,
2684 p_inventory_item_id IN NUMBER,
2685 p_transaction_type_id IN NUMBER DEFAULT NULL,
2686 p_object_type IN VARCHAR2 DEFAULT NULL,
2687 x_return_status OUT NOCOPY VARCHAR2,
2688 x_result OUT NOCOPY BOOLEAN,
2689 p_to_subinventory IN VARCHAR2 DEFAULT NULL) IS
2690
2691 l_result NUMBER;
2692
2693 --
2694 l_debug_on BOOLEAN;
2695 --
2696 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SUBINVENTORY';
2697 --
2698 BEGIN
2699 --
2700 -- Debug Statements
2701 --
2702 --
2703 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2704 --
2705 IF l_debug_on IS NULL
2706 THEN
2707 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2708 END IF;
2709 --
2710 IF l_debug_on THEN
2711 WSH_DEBUG_SV.push(l_module_name);
2712 --
2713 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
2714 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2715 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2716 WSH_DEBUG_SV.log(l_module_name,'P_TO_SUBINVENTORY',P_TO_SUBINVENTORY);
2717 WSH_DEBUG_SV.log(l_module_name,'p_transaction_type_id',p_transaction_type_id);
2718 WSH_DEBUG_SV.log(l_module_name,'p_object_type',p_object_type);
2719 END IF;
2720 --
2721 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2722 g_org.organization_id := p_organization_id;
2723 --
2724 -- Debug Statements
2725 --
2726 IF l_debug_on THEN
2727 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
2728 END IF;
2729 --
2730 l_result := INV_VALIDATE.organization(g_org);
2731 IF (l_result <> INV_VALIDATE.T) THEN
2732 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2733 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
2734 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2735 WSH_UTIL_CORE.Add_Message(x_return_status);
2736 END IF;
2737 g_item.inventory_item_id := p_inventory_item_id;
2738 --
2739 -- Debug Statements
2740 --
2741 IF l_debug_on THEN
2742 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
2743 END IF;
2744 --
2745 l_result := wsh_delivery_details_inv.inventory_item(
2746 p_item => g_item,
2747 p_org => g_org);
2748 IF (l_result <> INV_VALIDATE.T) THEN
2749 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2750 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
2751 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2752 WSH_UTIL_CORE.Add_Message(x_return_status);
2753 END IF;
2754 g_sub.secondary_inventory_name := p_subinventory;
2755
2756 IF p_to_subinventory IS NULL THEN
2757
2758 --
2759 -- Debug Statements
2760 --
2761 IF l_debug_on THEN
2762 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.FROM_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2763 END IF;
2764 --
2765 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
2766 l_result := INV_VALIDATE.from_subinventory(
2767 p_sub => g_sub,
2768 p_org => g_org,
2769 p_item => g_item,
2770 p_trx_type_id => p_transaction_type_id,
2771 p_object_type => p_object_type,
2772 p_acct_txn => 0);
2773 ELSE
2774 l_result := INV_VALIDATE.from_subinventory(
2775 p_sub => g_sub,
2776 p_org => g_org,
2777 p_item => g_item,
2778 p_acct_txn => 0);
2779 END IF;
2780 IF (l_result = INV_VALIDATE.T) THEN
2781 x_result := TRUE;
2782 ELSE
2783 x_result := FALSE;
2784 END IF;
2785
2786 ELSE
2787
2788 g_to_sub.secondary_inventory_name := p_to_subinventory;
2789
2790 --
2791 -- Debug Statements
2792 --
2793 IF l_debug_on THEN
2794 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.TO_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2795 END IF;
2796 --
2797 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
2798 l_result := INV_VALIDATE.to_subinventory(
2799 p_sub => g_to_sub,
2800 p_org => g_org,
2801 p_item => g_item,
2802 p_from_sub => g_sub,
2803 p_trx_type_id => p_transaction_type_id,
2804 p_object_type => p_object_type,
2805 p_acct_txn => 0);
2806 ELSE
2807 l_result := INV_VALIDATE.to_subinventory(
2808 p_sub => g_to_sub,
2809 p_org => g_org,
2810 p_item => g_item,
2811 p_from_sub => g_sub,
2812 p_acct_txn => 0);
2813 END IF;
2814
2815 IF (l_result = INV_VALIDATE.T) THEN
2816 x_result := TRUE;
2817 ELSE
2818 x_result := FALSE;
2819 END IF;
2820
2821 END IF;
2822
2823
2824
2825 --
2826 -- Debug Statements
2827 --
2828 IF l_debug_on THEN
2829 WSH_DEBUG_SV.pop(l_module_name);
2830 END IF;
2831 --
2832 EXCEPTION
2833
2834 WHEN others THEN
2835 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2836 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_subinventory');
2837
2838 --
2839 -- Debug Statements
2840 --
2841 IF l_debug_on THEN
2842 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2843 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2844 END IF;
2845 --
2846 END Validate_Subinventory;
2847
2848
2849 /*
2850 -----------------------------------------------------------------------------
2851
2852 Procedure : Validate_Lot_Number
2853 Parameters : p_lot_number
2854 p_organization_id
2855 p_inventory_item_id
2856 p_subinventory
2857 p_revision
2858 p_locator_id
2859 p_transaction_type_id
2860 p_object_type
2861 x_return_status
2862 Description : Validate item in context of organization_id
2863 Return TRUE if validate item successfully
2864 FALSE otherwise
2865 -----------------------------------------------------------------------------
2866 */
2867
2868
2869 PROCEDURE Validate_Lot_Number(
2870 p_lot_number IN VARCHAR2,
2871 p_organization_id IN NUMBER,
2872 p_inventory_item_id IN NUMBER,
2873 p_subinventory IN VARCHAR2,
2874 p_revision IN VARCHAR2,
2875 p_locator_id IN NUMBER,
2876 p_transaction_type_id IN NUMBER DEFAULT NULL,
2877 p_object_type IN VARCHAR2 DEFAULT NULL,
2878 x_return_status OUT NOCOPY VARCHAR2,
2879 x_result OUT NOCOPY BOOLEAN) IS
2880
2881 l_result NUMBER;
2882
2883 --
2884 l_debug_on BOOLEAN;
2885 --
2886 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOT_NUMBER';
2887 --
2888 BEGIN
2889 --
2890 -- Debug Statements
2891 --
2892 --
2893 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2894 --
2895 IF l_debug_on IS NULL
2896 THEN
2897 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2898 END IF;
2899 --
2900 IF l_debug_on THEN
2901 WSH_DEBUG_SV.push(l_module_name);
2902 --
2903 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
2904 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2905 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2906 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
2907 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
2908 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
2909 WSH_DEBUG_SV.log(l_module_name,'p_transaction_type_id',p_transaction_type_id);
2910 WSH_DEBUG_SV.log(l_module_name,'p_object_type',p_object_type);
2911 END IF;
2912 --
2913 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2914 g_org.organization_id := p_organization_id;
2915 g_item.inventory_item_id := p_inventory_item_id;
2916 g_loc.inventory_location_id := p_locator_id;
2917 g_lot.lot_number := p_lot_number;
2918 g_sub.secondary_inventory_name := p_subinventory;
2919
2920 --
2921 -- Debug Statements
2922 --
2923 IF l_debug_on THEN
2924 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
2925 END IF;
2926 --
2927 l_result := INV_VALIDATE.organization(g_org);
2928 IF (l_result <> INV_VALIDATE.T) THEN
2929 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2930 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
2931 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2932 WSH_UTIL_CORE.Add_Message(x_return_status);
2933 END IF;
2934
2935 g_item.inventory_item_id := p_inventory_item_id;
2936 --
2937 -- Debug Statements
2938 --
2939 IF l_debug_on THEN
2940 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
2941 END IF;
2942 --
2943 l_result := wsh_delivery_details_inv.inventory_item(
2944 p_item => g_item,
2945 p_org => g_org);
2946 IF (l_result <> INV_VALIDATE.T) THEN
2947 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2948 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
2949 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2950 WSH_UTIL_CORE.Add_Message(x_return_status);
2951 END IF;
2952
2953 g_sub.secondary_inventory_name := p_subinventory;
2954 --
2955 -- Debug Statements
2956 --
2957 IF l_debug_on THEN
2958 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.FROM_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
2959 END IF;
2960 --
2961 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
2962 l_result := INV_VALIDATE.From_Subinventory(
2963 p_org => g_org,
2964 p_sub => g_sub,
2965 p_item => g_item,
2966 p_trx_type_id => p_transaction_type_id,
2967 p_object_type => p_object_type,
2968 p_acct_txn => 1);
2969 ELSE
2970 l_result := INV_VALIDATE.From_Subinventory(
2971 p_org => g_org,
2972 p_sub => g_sub,
2973 p_item => g_item,
2974 p_acct_txn => 1);
2975 END IF;
2976 IF (l_result <> INV_VALIDATE.T) THEN
2977 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
2978 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
2979 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2980 WSH_UTIL_CORE.Add_Message(x_return_status);
2981 --
2982 -- Debug Statements
2983 --
2984 IF l_debug_on THEN
2985 WSH_DEBUG_SV.pop(l_module_name);
2986 END IF;
2987 --
2988 RETURN;
2989 END IF;
2990
2991 g_loc.inventory_location_id := p_locator_id;
2992 IF (p_locator_id IS NOT NULL) THEN
2993 --
2994 -- Debug Statements
2995 --
2996 IF l_debug_on THEN
2997 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATELOCATOR',WSH_DEBUG_SV.C_PROC_LEVEL);
2998 END IF;
2999 --
3000 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3001 l_result := INV_VALIDATE.validateLocator(
3002 p_locator => g_loc,
3003 p_org => g_org,
3004 p_sub => g_sub,
3005 p_trx_type_id => p_transaction_type_id,
3006 p_object_type => p_object_type,
3007 p_item => g_item);
3008 ELSE
3009 l_result := INV_VALIDATE.validateLocator(
3010 p_locator => g_loc,
3011 p_org => g_org,
3012 p_sub => g_sub,
3013 p_item => g_item);
3014 END IF;
3015 IF (l_result = INV_VALIDATE.F) THEN
3016 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3017 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
3018 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3019 WSH_UTIL_CORE.Add_Message(x_return_status);
3020 --
3021 -- Debug Statements
3022 --
3023 IF l_debug_on THEN
3024 WSH_DEBUG_SV.pop(l_module_name);
3025 END IF;
3026 --
3027 RETURN;
3028 END IF;
3029 END IF;
3030
3031 --
3032 -- Debug Statements
3033 --
3034 IF l_debug_on THEN
3035 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.LOT_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
3036 END IF;
3037 --
3038 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3039 l_result := INV_VALIDATE.lot_number(
3040 p_lot => g_lot,
3041 p_org => g_org,
3042 p_item => g_item,
3043 p_from_sub => g_sub,
3044 p_loc => g_loc,
3045 p_trx_type_id => p_transaction_type_id,
3046 p_object_type => p_object_type,
3047 p_revision => p_revision);
3048 ELSE
3049 l_result := INV_VALIDATE.lot_number(
3050 p_lot => g_lot,
3051 p_org => g_org,
3052 p_item => g_item,
3053 p_from_sub => g_sub,
3054 p_loc => g_loc,
3055 p_revision => p_revision);
3056 END IF;
3057
3058 IF (l_result = INV_VALIDATE.T) THEN
3059 x_result := TRUE;
3060 ELSE
3061 x_result := FALSE;
3062 END IF;
3063
3064
3065 --
3066 -- Debug Statements
3067 --
3068 IF l_debug_on THEN
3069 WSH_DEBUG_SV.pop(l_module_name);
3070 END IF;
3071 --
3072 EXCEPTION
3073 WHEN others THEN
3074 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3075 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_lot_number');
3076
3077 --
3078 -- Debug Statements
3079 --
3080 IF l_debug_on THEN
3081 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3082 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3083 END IF;
3084 --
3085 END Validate_lot_number;
3086
3087
3088 /*
3089 -----------------------------------------------------------------------------
3090
3091 Procedure : Validate_Serial
3092 Parameters : p_serial_number
3093 p_lot_number
3094 p_organization_id
3095 p_inventory_item_id
3096 p_subinventory
3097 p_revision
3098 p_locator_id
3099 p_transaction_type_id
3100 p_object_type
3101 x_return_status
3102 Description : Validate serial in context of organization_id
3103 Return TRUE if validate item successfully
3104 FALSE otherwise
3105 -----------------------------------------------------------------------------
3106 */
3107 PROCEDURE Validate_Serial(
3108 p_serial_number IN VARCHAR2,
3109 p_lot_number IN VARCHAR2,
3110 p_organization_id IN NUMBER,
3111 p_inventory_item_id IN NUMBER,
3112 p_subinventory IN VARCHAR2,
3113 p_revision IN VARCHAR2,
3114 p_locator_id IN NUMBER,
3115 p_transaction_type_id IN NUMBER DEFAULT NULL,
3116 p_object_type IN VARCHAR2 DEFAULT NULL,
3117 x_return_status OUT NOCOPY VARCHAR2,
3118 x_result OUT NOCOPY BOOLEAN) IS
3119
3120 l_result NUMBER;
3121
3122 --
3123 l_debug_on BOOLEAN;
3124 --
3125 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SERIAL';
3126 --
3127 BEGIN
3128 --
3129 -- Debug Statements
3130 --
3131 --
3132 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3133 --
3134 IF l_debug_on IS NULL
3135 THEN
3136 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3137 END IF;
3138 --
3139 IF l_debug_on THEN
3140 WSH_DEBUG_SV.push(l_module_name);
3141 --
3142 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER',P_SERIAL_NUMBER);
3143 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
3144 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
3145 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
3146 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
3147 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
3148 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
3149 WSH_DEBUG_SV.log(l_module_name,'p_transaction_type_id',p_transaction_type_id);
3150 WSH_DEBUG_SV.log(l_module_name,'p_object_type',p_object_type);
3151 END IF;
3152 --
3153 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3154 g_org.organization_id := p_organization_id;
3155 --
3156 -- Debug Statements
3157 --
3158 IF l_debug_on THEN
3159 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
3160 END IF;
3161 --
3162 l_result := INV_VALIDATE.organization(g_org);
3163 IF (l_result <> INV_VALIDATE.T) THEN
3164 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3165 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
3166 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3167 WSH_UTIL_CORE.Add_Message(x_return_status);
3168 --
3169 -- Debug Statements
3170 --
3171 IF l_debug_on THEN
3172 WSH_DEBUG_SV.pop(l_module_name);
3173 END IF;
3174 --
3175 RETURN;
3176 END IF;
3177 g_item.inventory_item_id := p_inventory_item_id;
3178 --
3179 -- Debug Statements
3180 --
3181 IF l_debug_on THEN
3182 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
3183 END IF;
3184 --
3185 l_result := wsh_delivery_details_inv.inventory_item(
3186 p_item => g_item,
3187 p_org => g_org);
3188 IF (l_result <> INV_VALIDATE.T) THEN
3189 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3190 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
3191 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3192 WSH_UTIL_CORE.Add_Message(x_return_status);
3193 --
3194 -- Debug Statements
3195 --
3196 IF l_debug_on THEN
3197 WSH_DEBUG_SV.pop(l_module_name);
3198 END IF;
3199 --
3200 RETURN;
3201 END IF;
3202
3203 g_sub.secondary_inventory_name := p_subinventory;
3204 --
3205 -- Debug Statements
3206 --
3207 IF l_debug_on THEN
3208 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.FROM_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
3209 END IF;
3210 --
3211 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3212 l_result := INV_VALIDATE.From_Subinventory(
3213 p_org => g_org,
3214 p_sub => g_sub,
3215 p_item => g_item,
3216 p_trx_type_id => p_transaction_type_id,
3217 p_object_type => p_object_type,
3218 p_acct_txn => 1);
3219 ELSE
3220 l_result := INV_VALIDATE.From_Subinventory(
3221 p_org => g_org,
3222 p_sub => g_sub,
3223 p_item => g_item,
3224 p_acct_txn => 1);
3225 END IF;
3226 IF (l_result <> INV_VALIDATE.T) THEN
3227 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3228 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
3229 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3230 WSH_UTIL_CORE.Add_Message(x_return_status);
3231 --
3232 -- Debug Statements
3233 --
3234 IF l_debug_on THEN
3235 WSH_DEBUG_SV.pop(l_module_name);
3236 END IF;
3237 --
3238 RETURN;
3239 END IF;
3240
3241 g_loc.inventory_location_id := p_locator_id;
3242 -- Need to check if locator is NULL cause inventory validation API will
3243 -- validate on flex field and return false when locator is NULL
3244 IF (p_locator_id IS NOT NULL) THEN
3245 --
3246 -- Debug Statements
3247 --
3248 IF l_debug_on THEN
3249 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATELOCATOR',WSH_DEBUG_SV.C_PROC_LEVEL);
3250 END IF;
3251 --
3252 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3253 l_result := INV_VALIDATE.validateLocator(
3254 p_locator => g_loc,
3255 p_org => g_org,
3256 p_sub => g_sub,
3257 p_trx_type_id => p_transaction_type_id,
3258 p_object_type => p_object_type,
3259 p_item => g_item);
3260 ELSE
3261 l_result := INV_VALIDATE.validateLocator(
3262 p_locator => g_loc,
3263 p_org => g_org,
3264 p_sub => g_sub,
3265 p_item => g_item);
3266 END IF;
3267 IF (l_result = INV_VALIDATE.F) THEN
3268 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3269 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
3270 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3271 WSH_UTIL_CORE.Add_Message(x_return_status);
3272 --
3273 -- Debug Statements
3274 --
3275 IF l_debug_on THEN
3276 WSH_DEBUG_SV.pop(l_module_name);
3277 END IF;
3278 --
3279 RETURN;
3280 END IF;
3281 END IF;
3282
3283 g_lot.lot_number := p_lot_number;
3284 IF (p_lot_number IS NOT NULL) THEN
3285 --
3286 -- Debug Statements
3287 --
3288 IF l_debug_on THEN
3289 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.LOT_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
3290 END IF;
3291 --
3292 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3293 l_result := INV_VALIDATE.Lot_Number(
3294 p_lot => g_lot,
3295 p_org => g_org,
3296 p_item => g_item,
3297 p_from_sub => g_sub,
3298 p_trx_type_id => p_transaction_type_id,
3299 p_object_type => p_object_type,
3300 p_loc => g_loc,
3301 p_revision => p_revision);
3302 ELSE
3303 l_result := INV_VALIDATE.Lot_Number(
3304 p_lot => g_lot,
3305 p_org => g_org,
3306 p_item => g_item,
3307 p_from_sub => g_sub,
3308 p_loc => g_loc,
3309 p_revision => p_revision);
3310 END IF;
3311 IF (l_result <> INV_VALIDATE.T) THEN
3312 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
3313 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
3314 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3315 WSH_UTIL_CORE.Add_Message(x_return_status);
3316 --
3317 -- Debug Statements
3318 --
3319 IF l_debug_on THEN
3320 WSH_DEBUG_SV.pop(l_module_name);
3321 END IF;
3322 --
3323 RETURN;
3324 END IF;
3325 END IF;
3326 g_serial.serial_number := p_serial_number;
3327
3328 --
3329 -- Debug Statements
3330 --
3331 IF l_debug_on THEN
3332 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATE_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
3333 END IF;
3334 --
3335 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
3336 l_result := INV_VALIDATE.Validate_serial(
3337 p_serial => g_serial,
3338 p_lot => g_lot,
3339 p_org => g_org,
3340 p_item => g_item,
3341 p_from_sub => g_sub,
3342 p_loc => g_loc,
3343 p_trx_type_id => p_transaction_type_id,
3344 p_object_type => p_object_type,
3345 p_revision => p_revision);
3346 ELSE
3347 l_result := INV_VALIDATE.Validate_serial(
3348 p_serial => g_serial,
3349 p_lot => g_lot,
3350 p_org => g_org,
3351 p_item => g_item,
3352 p_from_sub => g_sub,
3353 p_loc => g_loc,
3354 p_revision => p_revision);
3355 END IF;
3356
3357 IF (l_result = INV_VALIDATE.T) THEN
3358 x_result := TRUE;
3359 ELSE
3360 x_result := FALSE;
3361 END IF;
3362
3363 --
3364 -- Debug Statements
3365 --
3366 IF l_debug_on THEN
3367 WSH_DEBUG_SV.pop(l_module_name);
3368 END IF;
3369 --
3370 EXCEPTION
3371
3372 WHEN others THEN
3373 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3374 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_Serial');
3375
3376 --
3377 -- Debug Statements
3378 --
3379 IF l_debug_on THEN
3380 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3382 END IF;
3383 --
3384 END Validate_Serial;
3385
3386
3387 /*
3388 -----------------------------------------------------------------------------
3389 PROCEDURE : Update_Locator_Subinv
3390 PARAMETERS : p_organization_id - organization id for the delivery detail
3391 p_locator_id - locator id for the delivery detail
3392 -1 if dynamic insert and 1 if pre-defined.
3393 p_subinventory - subinventory for the delivery detail
3394 x_return_status - return status of the API
3395 DESCRIPTION : This procedure takes in the inventory location id (locator id),
3396 subinventory and org for the delivery detail and validates if
3397 the locator id exists for the given organization and location.
3398 If it can find it then it raises a duplicate locator exception,
3399 else it updates the mtl item locations table with the
3400 input subinventory for the given locator id and organization.
3401 -----------------------------------------------------------------------------
3402 */
3403
3404 PROCEDURE Update_Locator_Subinv (
3405 p_organization_id IN NUMBER,
3406 p_locator_id IN NUMBER,
3407 p_subinventory IN VARCHAR2,
3408 x_return_status OUT NOCOPY VARCHAR2) IS
3409
3410
3411 CURSOR Check_Dup_Loc IS
3412 SELECT 'Exist'
3413 FROM Mtl_Item_Locations
3414 WHERE organization_id = p_organization_id
3415 AND inventory_location_id = p_locator_id
3416 AND subinventory_code IS NOT NULL
3417 AND subinventory_code <> p_subinventory;
3418
3419 l_temp VARCHAR2(240);
3420 l_org_id NUMBER;
3421 l_loc_id NUMBER;
3422
3423 --
3424 l_debug_on BOOLEAN;
3425 --
3426 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_LOCATOR_SUBINV';
3427 --
3428 BEGIN
3429
3430 --
3431 -- Debug Statements
3432 --
3433 --
3434 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3435 --
3436 IF l_debug_on IS NULL
3437 THEN
3438 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3439 END IF;
3440 --
3441 IF l_debug_on THEN
3442 WSH_DEBUG_SV.push(l_module_name);
3443 --
3444 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
3445 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
3446 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
3447 END IF;
3448 --
3449 OPEN Check_Dup_Loc;
3450 FETCH Check_Dup_Loc INTO l_temp;
3451
3452 IF ( Check_Dup_Loc%FOUND) THEN
3453
3454 IF (Check_Dup_Loc%ISOPEN) THEN
3455 CLOSE Check_Dup_Loc;
3456 END IF;
3457
3458 FND_MESSAGE.SET_NAME('WSH','WSH_INV_DUP_LOCATOR');
3459 FND_MESSAGE.SET_TOKEN('SUBINV',p_subinventory);
3460 --
3461 -- Debug Statements
3462 --
3463 IF l_debug_on THEN
3464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
3465 END IF;
3466 --
3467 l_temp := WSH_UTIL_CORE.Get_Org_Name(p_organization_id);
3468 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_temp);
3469 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3470 WSH_UTIL_CORE.Add_Message(x_return_status);
3471
3472 --
3473 -- Debug Statements
3474 --
3475 IF l_debug_on THEN
3476 WSH_DEBUG_SV.pop(l_module_name);
3477 END IF;
3478 --
3479 return;
3480
3481 END IF;
3482
3483 IF (Check_Dup_Loc%ISOPEN) THEN
3484 CLOSE Check_Dup_Loc;
3485 END IF;
3486
3487 UPDATE Mtl_Item_Locations
3488 SET subinventory_code = p_subinventory
3489 WHERE organization_id = p_organization_id
3490 AND inventory_location_id = p_locator_id;
3491
3492 IF SQL%NOTFOUND THEN
3493 FND_MESSAGE.SET_NAME('FND','SQLERRM');
3494 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3495 WSH_UTIL_CORE.Add_Message(x_return_status);
3496 --
3497 -- Debug Statements
3498 --
3499 IF l_debug_on THEN
3500 WSH_DEBUG_SV.pop(l_module_name);
3501 END IF;
3502 --
3503 return;
3504 END IF;
3505
3506 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3507
3508 --
3509 -- Debug Statements
3510 --
3511 IF l_debug_on THEN
3512 WSH_DEBUG_SV.pop(l_module_name);
3513 END IF;
3514 --
3515 EXCEPTION
3516
3517 WHEN OTHERS THEN
3518 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Update_Locator_Subinv');
3519 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3520
3521 --
3522 -- Debug Statements
3523 --
3524 IF l_debug_on THEN
3525 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3527 END IF;
3528 --
3529 END Update_Locator_Subinv;
3530
3531
3532 /*
3533 -----------------------------------------------------------------------------
3534 FUNCTION : Get_Serial_Qty
3535 PARAMETERS : p_organization_id - organization id of line
3536 p_delivery_detail_id - delivery detail id for the line
3537 DESCRIPTION : This API takes the organization and delivery detail id for
3538 the line and calculates the serial quantity for the line
3539 based on the transaction temp id/serial number that is
3540 entered for the line. If the item is not under serial control
3541 then it returns a 0. If it is an invalid delivery detail id
3542 then it returns a -99.
3543 -----------------------------------------------------------------------------
3544 */
3545
3546 FUNCTION Get_Serial_Qty (
3547 p_organization_id IN NUMBER,
3548 p_delivery_detail_id IN NUMBER) RETURN NUMBER IS
3549
3550 CURSOR Get_Ser_Qty (v_trx_temp_id NUMBER) IS
3551 SELECT sum (serial_prefix)
3552 FROM MTL_SERIAL_NUMBERS_TEMP
3553 WHERE transaction_temp_id = nvl(v_trx_temp_id,transaction_temp_id);
3554
3555 CURSOR Get_Detail_Info IS
3556 SELECT transaction_temp_id, inventory_item_id, shipped_quantity,
3557 organization_id, serial_number
3558 FROM WSH_DELIVERY_DETAILS
3559 WHERE delivery_detail_id = p_delivery_detail_id;
3560
3561 l_trx_id NUMBER;
3562 l_ser_num VARCHAR2(30);
3563 l_org_id NUMBER;
3564 l_inv_item_id NUMBER;
3565
3566 l_shp_qty NUMBER;
3567
3568 l_ser_qty NUMBER := 0;
3569
3570
3571 --
3572 l_debug_on BOOLEAN;
3573 --
3574 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SERIAL_QTY';
3575 --
3576 BEGIN
3577
3578 --
3579 -- Debug Statements
3580 --
3581 --
3582 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3583 --
3584 IF l_debug_on IS NULL
3585 THEN
3586 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3587 END IF;
3588 --
3589 IF l_debug_on THEN
3590 WSH_DEBUG_SV.push(l_module_name);
3591 --
3592 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
3593 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
3594 END IF;
3595 --
3596 IF p_delivery_detail_id IS NULL THEN
3597 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL');
3598 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3599 l_ser_qty := -99;
3600 --
3601 -- Debug Statements
3602 --
3603 IF l_debug_on THEN
3604 WSH_DEBUG_SV.pop(l_module_name);
3605 END IF;
3606 --
3607 return l_ser_qty;
3608 END IF;
3609
3610 OPEN Get_Detail_Info;
3611
3612 FETCH Get_Detail_Info INTO
3613 l_trx_id,
3614 l_inv_item_id,
3615 l_shp_qty,
3616 l_org_id,
3617 l_ser_num;
3618
3619 IF Get_Detail_Info%NOTFOUND THEN
3620 CLOSE Get_Detail_Info;
3621 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL');
3622 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3623 l_ser_qty := -99;
3624 --
3625 -- Debug Statements
3626 --
3627 IF l_debug_on THEN
3628 WSH_DEBUG_SV.pop(l_module_name);
3629 END IF;
3630 --
3631 return l_ser_qty;
3632 END IF;
3633
3634 IF Get_Detail_Info%ISOPEN THEN
3635 CLOSE Get_Detail_Info;
3636 END IF;
3637
3638 IF l_ser_num IS NOT NULL AND l_shp_qty = 1 THEN
3639 l_ser_qty := 1;
3640 --
3641 -- Debug Statements
3642 --
3643 IF l_debug_on THEN
3644 WSH_DEBUG_SV.pop(l_module_name);
3645 END IF;
3646 --
3647 return l_ser_qty;
3648 END IF;
3649
3650 IF l_trx_id IS NOT NULL AND l_ser_num IS NOT NULL THEN
3651 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRX_ID');
3652 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3653 l_ser_qty := -99;
3654 --
3655 -- Debug Statements
3656 --
3657 IF l_debug_on THEN
3658 WSH_DEBUG_SV.pop(l_module_name);
3659 END IF;
3660 --
3661 return l_ser_qty;
3662 END IF;
3663
3664 IF l_trx_id IS NOT NULL AND nvl(l_shp_qty,0) < 1 THEN
3665 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRX_ID');
3666 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3667 l_ser_qty := -99;
3668 --
3669 -- Debug Statements
3670 --
3671 IF l_debug_on THEN
3672 WSH_DEBUG_SV.pop(l_module_name);
3673 END IF;
3674 --
3675 return l_ser_qty;
3676 END IF;
3677
3678 IF l_trx_id IS NOT NULL THEN
3679
3680 OPEN Get_Ser_Qty(l_trx_id);
3681
3682 FETCH Get_Ser_Qty INTO l_ser_qty;
3683
3684 IF Get_Ser_Qty%NOTFOUND THEN
3685 CLOSE Get_Ser_Qty;
3686 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRX_ID');
3687 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3688 l_ser_qty := -99;
3689 --
3690 -- Debug Statements
3691 --
3692 IF l_debug_on THEN
3693 WSH_DEBUG_SV.pop(l_module_name);
3694 END IF;
3695 --
3696 return l_ser_qty;
3697 END IF;
3698
3699 IF Get_Ser_Qty%ISOPEN THEN
3700 CLOSE Get_Ser_Qty;
3701 END IF;
3702
3703 --
3704 -- Debug Statements
3705 --
3706 IF l_debug_on THEN
3707 WSH_DEBUG_SV.pop(l_module_name);
3708 END IF;
3709 --
3710 return nvl(l_ser_qty,0);
3711
3712 END IF;
3713
3714 -- nvl the shp qty to 2 in this case because the shp qty cannot be null and
3715 -- have a valid serial number populated. this is data corruption.
3716
3717 IF l_ser_num IS NOT NULL AND nvl(l_shp_qty,2) > 1 THEN
3718 FND_MESSAGE.SET_NAME('WSH','WSH_DETAIL_INVALID_SERIAL');
3719 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR);
3720 l_ser_qty := -99;
3721 --
3722 -- Debug Statements
3723 --
3724 IF l_debug_on THEN
3725 WSH_DEBUG_SV.pop(l_module_name);
3726 END IF;
3727 --
3728 return l_ser_qty;
3729 END IF;
3730
3731 --
3732 -- Debug Statements
3733 --
3734 IF l_debug_on THEN
3735 WSH_DEBUG_SV.pop(l_module_name);
3736 END IF;
3737 --
3738 return nvl(l_ser_qty,0);
3739
3740 EXCEPTION
3741
3742 WHEN OTHERS THEN
3743 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.Get_Serial_Qty');
3744 l_ser_qty := -99;
3745 --
3746 --
3747 -- Debug Statements
3748 --
3749 IF l_debug_on THEN
3750 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3751 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3752 END IF;
3753 --
3754 return l_ser_qty;
3755
3756 END Get_Serial_Qty;
3757
3758
3759 FUNCTION get_reservable_flag(x_item_id IN NUMBER,
3760 x_organization_id IN NUMBER,
3761 x_pickable_flag IN VARCHAR2) RETURN
3762 VARCHAR2 IS
3763 -- bug 1583800: pickable_flag <> 'Y' overrides the reservable_flag
3764 -- also, check if the item is transactable.
3765
3766 l_type MTL_SYSTEM_ITEMS.RESERVABLE_TYPE%TYPE;
3767 l_trx_flag MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG%TYPE;
3768 l_flag VARCHAR2(1) := 'Y';
3769 l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
3770 --
3771 l_debug_on BOOLEAN;
3772 l_return_status VARCHAR2(1);
3773 --
3774 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_RESERVABLE_FLAG';
3775 --
3776 BEGIN
3777
3778 -- HW OPMCONV. Removed code forking
3779
3780 --
3781 -- Debug Statements
3782 --
3783 --
3784 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3785 --
3786 IF l_debug_on IS NULL
3787 THEN
3788 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3789 END IF;
3790 --
3791 IF l_debug_on THEN
3792 WSH_DEBUG_SV.push(l_module_name);
3793 --
3794 WSH_DEBUG_SV.log(l_module_name,'X_ITEM_ID',X_ITEM_ID);
3795 WSH_DEBUG_SV.log(l_module_name,'X_ORGANIZATION_ID',X_ORGANIZATION_ID);
3796 WSH_DEBUG_SV.log(l_module_name,'X_PICKABLE_FLAG',X_PICKABLE_FLAG);
3797 END IF;
3798 --
3799
3800 --Bug 5352779
3801 IF (x_item_id IS NULL) OR (x_organization_id IS NULL) THEN
3802 --
3803 IF l_debug_on THEN
3804 WSH_DEBUG_SV.pop(l_module_name);
3805 END IF;
3806 --
3807 return 'Y';
3808 END IF;
3809
3810 l_type := NULL;
3811
3812 -- bug 1583800: assume NULL pickable_flag means 'Y'
3813 IF NVL(x_pickable_flag, 'Y') = 'N' THEN
3814 l_flag := 'N';
3815 ELSE
3816 Get_item_information( p_organization_id => x_organization_id
3817 , p_inventory_item_id => x_item_id
3818 , x_mtl_system_items_rec => l_item_info
3819 , x_return_status => l_return_status);
3820
3821 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3822 RAISE FND_API.G_EXC_ERROR;
3823 END IF;
3824
3825 l_type := l_item_info.reservable_type;
3826 l_trx_flag := l_item_info.mtl_transactions_enabled_flag;
3827
3828 IF l_type = 2 THEN -- 2 = non-reservable
3829 l_flag := 'N';
3830 ELSE -- 1 = reservable
3831 -- bug 1583800: if item is also transactable, it will have
3832 --reservations.
3833 l_flag := l_trx_flag;
3834 END IF;
3835 END IF;
3836
3837 --
3838 -- Debug Statements
3839 --
3840 IF l_debug_on THEN
3841 WSH_DEBUG_SV.pop(l_module_name);
3842 END IF;
3843 --
3844 return l_flag;
3845
3846 EXCEPTION
3847
3848 WHEN FND_API.G_EXC_ERROR THEN
3849 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3850 WSH_UTIL_CORE.Add_Message(l_return_status);
3851 IF l_debug_on THEN
3852 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3854 END IF;
3855 --
3856 return 'Y';
3857
3858 WHEN OTHERS THEN
3859 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.get_reservable_flag');
3860 --
3861 -- Debug Statements
3862 --
3863 IF l_debug_on THEN
3864 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3865 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3866 END IF;
3867 --
3868 RETURN 'Y';
3869
3870 END get_reservable_flag;
3871
3872 /*
3873 -----------------------------------------------------------------------------
3874 FUNCTION : Line_Reserved
3875 PARAMETERS : p_detail_id - delivery_detail_id
3876 p_source_code - source system code
3877 p_released_status - released status
3878 p_pickable_flag - pickable flag
3879 p_organization_id - organization id of item
3880 p_inventory_item_id - item id
3881 x_return_status - success if able to look up reservation status
3882 error if cannot look up
3883 DESCRIPTION : This API takes the organization and inventory item
3884 and determines whether the lines item is reserved.
3885 It returns Y if it is reserved, N otherwise.
3886 -----------------------------------------------------------------------------
3887 */
3888
3889 FUNCTION Line_Reserved(
3890 p_detail_id IN NUMBER,
3891 p_source_code IN VARCHAR2,
3892 p_released_status IN VARCHAR2,
3893 p_pickable_flag IN VARCHAR2,
3894 p_organization_id IN NUMBER,
3895 p_inventory_item_id IN NUMBER,
3896 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
3897 IS
3898 g_cache_item_id NUMBER := NULL;
3899 g_cache_organization_id NUMBER := NULL;
3900 g_cache_reservable_flag VARCHAR(1) := NULL;
3901
3902
3903 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3904 l_reservable_type NUMBER;
3905 l_mtl_txns_enabled_flag VARCHAR2(1);
3906 l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
3907 l_debug_on BOOLEAN;
3908 --
3909 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LINE_RESERVED';
3910 --
3911 BEGIN
3912
3913 --
3914 -- Debug Statements
3915 --
3916 --
3917 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3918 --
3919 IF l_debug_on IS NULL
3920 THEN
3921 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3922 END IF;
3923 --
3924 IF l_debug_on THEN
3925 WSH_DEBUG_SV.push(l_module_name);
3926 --
3927 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_ID',P_DETAIL_ID);
3928 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
3929 WSH_DEBUG_SV.log(l_module_name,'P_RELEASED_STATUS',P_RELEASED_STATUS);
3930 WSH_DEBUG_SV.log(l_module_name,'P_PICKABLE_FLAG',P_PICKABLE_FLAG);
3931 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
3932 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
3933 END IF;
3934 --
3935 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3936
3937 IF p_pickable_flag = 'N'
3938 OR p_source_code IN ('OKE', 'WSH')
3939 OR p_released_status IN ('N', 'R', 'S', 'X') THEN
3940 --
3941 -- Debug Statements
3942 --
3943 IF l_debug_on THEN
3944 WSH_DEBUG_SV.pop(l_module_name);
3945 END IF;
3946 --
3947 return 'N' ;
3948 END IF;
3949
3950 IF g_cache_organization_id = p_organization_id
3951 AND g_cache_item_id = p_inventory_item_id
3952 AND g_cache_reservable_flag IS NOT NULL THEN
3953 --
3954 -- Debug Statements
3955 --
3956 IF l_debug_on THEN
3957 WSH_DEBUG_SV.pop(l_module_name);
3958 END IF;
3959 --
3960 RETURN g_cache_reservable_flag;
3961 END IF;
3962
3963 Get_item_information( p_organization_id => p_organization_id
3964 , p_inventory_item_id => p_inventory_item_id
3965 , x_mtl_system_items_rec => l_item_info
3966 , x_return_status => l_return_status);
3967
3968 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3969 RAISE FND_API.G_EXC_ERROR;
3970 END IF;
3971
3972 l_reservable_type := l_item_info.reservable_type;
3973
3974 g_cache_reservable_flag := NULL;
3975 g_cache_item_id := p_inventory_item_id;
3976 g_cache_organization_id := p_organization_id;
3977
3978 IF l_reservable_type = 1 /* reservable = 1 */ THEN
3979 g_cache_reservable_flag := 'Y';
3980 ELSE
3981 g_cache_reservable_flag := 'N' ;
3982 END IF;
3983
3984 --
3985 -- Debug Statements
3986 --
3987 IF l_debug_on THEN
3988 WSH_DEBUG_SV.pop(l_module_name);
3989 END IF;
3990 --
3991 RETURN g_cache_reservable_flag;
3992
3993 EXCEPTION
3994
3995 WHEN FND_API.G_EXC_ERROR THEN
3996 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3997 WSH_UTIL_CORE.Add_Message(x_return_status);
3998 IF l_debug_on THEN
3999 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4000 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4001 END IF;
4002
4003 WHEN OTHERS THEN
4004 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_DETAILS_INV.Line_Reserved');
4005 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4006 --
4007 -- Debug Statements
4008 --
4009 IF l_debug_on THEN
4010 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4011 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4012 END IF;
4013 --
4014 RETURN NULL;
4015
4016 END Line_Reserved;
4017
4018 PROCEDURE Create_Dynamic_Serial(
4019 p_from_number IN VARCHAR2,
4020 p_to_number IN VARCHAR2,
4021 p_source_line_id IN NUMBER,
4022 p_delivery_detail_id IN NUMBER,
4023 p_inventory_item_id IN NUMBER,
4024 p_organization_id IN NUMBER,
4025 p_revision IN VARCHAR2,
4026 p_lot_number IN VARCHAR2,
4027 p_subinventory IN VARCHAR2,
4028 p_locator_id IN NUMBER,
4029 x_return_status OUT NOCOPY VARCHAR2,
4030 p_serial_number_type_id IN NUMBER DEFAULT NULL,
4031 p_source_document_type_id IN NUMBER DEFAULT NULL)
4032 IS
4033
4034 cursor c_header_info(c_delivery_detail_id number) is
4035 select nvl(dd.source_document_type_id, -9999) source_document_type_id
4036 from wsh_delivery_details dd
4037 where dd.delivery_detail_id = c_delivery_detail_id;
4038
4039 l_header_info c_header_info%ROWTYPE;
4040 l_return_status VARCHAR2(300);
4041 l_msg_count NUMBER;
4042 l_msg_data VARCHAR2(300);
4043 l_error_code NUMBER;
4044 l_quantity NUMBER;
4045 l_prefix VARCHAR2(240);
4046 serial_number_type NUMBER;
4047 trx_action_id NUMBER;
4048 trx_source_type VARCHAR2(1);
4049 l_to_number VARCHAR2(100);
4050 l_return NUMBER;
4051 l_mtl_org_param_rec WSH_DELIVERY_DETAILS_INV.mtl_org_param_rec;
4052 WSH_INVALID_SER_NUM EXCEPTION;
4053
4054 --
4055 l_debug_on BOOLEAN;
4056 --
4057 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DYNAMIC_SERIAL';
4058 --
4059 begin
4060 --
4061 -- Debug Statements
4062 --
4063 --
4064 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4065 --
4066 IF l_debug_on IS NULL
4067 THEN
4068 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4069 END IF;
4070 --
4071 IF l_debug_on THEN
4072 WSH_DEBUG_SV.push(l_module_name);
4073 --
4074 WSH_DEBUG_SV.log(l_module_name,'P_FROM_NUMBER',P_FROM_NUMBER);
4075 WSH_DEBUG_SV.log(l_module_name,'P_TO_NUMBER',P_TO_NUMBER);
4076 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
4077 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
4078 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
4079 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
4080 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
4081 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
4082 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
4083 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
4084 WSH_DEBUG_SV.log(l_module_name,'p_serial_number_type_id',p_serial_number_type_id);
4085 WSH_DEBUG_SV.log(l_module_name,'p_source_document_type_id',p_source_document_type_id);
4086
4087 END IF;
4088 --
4089 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4090
4091 IF p_serial_number_type_id IS NULL THEN
4092
4093 -- bug 5264874
4094 Get_Org_Param_information (
4095 p_organization_id => p_organization_id
4096 , x_mtl_org_param_rec => l_mtl_org_param_rec
4097 , x_return_status => l_return_status);
4098
4099 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4100 RAISE FND_API.G_EXC_ERROR;
4101 END IF;
4102
4103 serial_number_type := l_mtl_org_param_rec.serial_number_type;
4104 IF serial_number_type = -99 THEN
4105 raise WSH_INVALID_SER_NUM;
4106 END IF;
4107 -- bug 5264874 end
4108 ELSE
4109
4110 serial_number_type := p_serial_number_type_id;
4111
4112 END IF;
4113
4114 IF p_source_document_type_id IS NULL THEN
4115
4116 open c_header_info(p_delivery_detail_id);
4117 fetch c_header_info into l_header_info;
4118 if c_header_info%NOTFOUND then
4119 l_header_info.source_document_type_id := -9999;
4120 end if;
4121 close c_header_info;
4122
4123 ELSE
4124
4125 l_header_info.source_document_type_id := p_source_document_type_id;
4126
4127 END IF;
4128
4129 l_to_number := p_to_number;
4130
4131
4132 --
4133 -- Debug Statements
4134 --
4135 IF l_debug_on THEN
4136 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.TRX_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
4137 END IF;
4138 --
4139 trx_action_id := WSH_DETAILS_VALIDATIONS.Trx_Id('TRX_ACTION_ID',
4140 p_source_line_id,
4141 l_header_info.source_document_type_id);
4142
4143
4144 IF l_header_info.source_document_type_id = 10 THEN
4145 trx_source_type := '8';
4146 ELSIF l_header_info.source_document_type_id <> 10 THEN
4147 trx_source_type := '2';
4148 END IF;
4149
4150
4151 --
4152 -- Debug Statements
4153 --
4154 IF l_debug_on THEN
4155 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_SERIAL_NUMBER_PUB.VALIDATE_SERIALS',WSH_DEBUG_SV.C_PROC_LEVEL);
4156 END IF;
4157 --
4158
4159
4160
4161 l_return := INV_SERIAL_NUMBER_PUB.VALIDATE_SERIALS(
4162 p_org_id => p_organization_id,
4163 p_item_id => p_inventory_item_id,
4164 p_qty => l_quantity,
4165 p_rev => p_revision,
4166 p_lot => p_lot_number,
4167 p_start_ser => p_from_number,
4168 p_trx_src_id => trx_source_type,
4169 p_trx_action_id => trx_action_id,
4170 p_subinventory_code =>p_subinventory,
4171 p_locator_id =>p_locator_id,
4172 p_group_mark_id => NULL,
4173 p_issue_receipt => 'I',
4174 x_end_ser => l_to_number,
4175 x_proc_msg => l_msg_data,
4176 p_check_for_grp_mark_id => 'Y' --Bug# 2656316
4177 );
4178
4179
4180 IF l_return = 1 THEN
4181
4182 RAISE WSH_INVALID_SER_NUM;
4183
4184 END IF;
4185
4186
4187 --
4188 -- Debug Statements
4189 --
4190 IF l_debug_on THEN
4191 WSH_DEBUG_SV.pop(l_module_name);
4192 END IF;
4193 --
4194 EXCEPTION
4195
4196 WHEN FND_API.G_EXC_ERROR THEN
4197 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4198 WSH_UTIL_CORE.Add_Message(x_return_status);
4199 IF l_debug_on THEN
4200 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4201 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4202 END IF;
4203
4204 WHEN WSH_INVALID_SER_NUM THEN
4205 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4206 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_SER_NUM');
4207 WSH_UTIL_CORE.Add_Message(x_return_status);
4208 IF l_debug_on THEN
4209 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4210 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_SER_NUM');
4211 END IF;
4212
4213 WHEN others THEN
4214 IF c_header_info%ISOPEN THEN
4215 close c_header_info;
4216 END IF;
4217 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4218 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Create_Dynamic_Serial');
4219
4220 --
4221 -- Debug Statements
4222 --
4223 IF l_debug_on THEN
4224 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4225 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4226 END IF;
4227
4228 --
4229 END Create_Dynamic_Serial;
4230
4231 /*
4232 -----------------------------------------------------------------------------
4233 PROCEDURE : Validate_Serial_Range
4234 PARAMETERS : p_from_serial_number - The start serial number
4235 p_to_serial_number - The end serial number
4236 p_lot_number - lot id for the delivery detail
4237 p_organization_id - organization id for the delivery detail
4238 p_inventory_item_id - Item id for the delivery detail
4239 p_revision - revision of the delivery detail
4240 p_locator_id - locator id for the delivery detail
4241 -1 if dynamic insert and 1 if pre-defined.
4242 p_subinventory - subinventory for the delivery detail
4243 p_quantity - Amount of quantitiy to be shipped
4244 p_transaction_type_id
4245 p_object_type
4246 x_prefix - The prefix of serial number
4247 x_return_status - return status of the API
4248 x_result - The result of the API
4249
4250 DESCRIPTION : This procedure takes in the from_serial number and to_serial number
4251 and validates if the serial numbers fall in Range and range is equal
4252 to the given quantity.It also checks if the serial numbers falling
4253 in the range are predefined for the item.
4254 -----------------------------------------------------------------------------
4255 */
4256
4257 PROCEDURE Validate_Serial_Range(
4258 p_from_serial_number IN VARCHAR2,
4259 p_to_serial_number IN VARCHAR2,
4260 p_lot_number IN VARCHAR2,
4261 p_organization_id IN NUMBER,
4262 p_inventory_item_id IN NUMBER,
4263 p_subinventory IN VARCHAR2,
4264 p_revision IN VARCHAR2,
4265 p_locator_id IN NUMBER,
4266 p_quantity IN NUMBER,
4267 p_transaction_type_id IN NUMBER DEFAULT NULL,
4268 p_object_type IN VARCHAR2 DEFAULT NULL,
4269 x_prefix OUT NOCOPY VARCHAR2,
4270 x_return_status OUT NOCOPY VARCHAR2,
4271 x_result OUT NOCOPY BOOLEAN)
4272
4273 IS
4274
4275 l_result NUMBER;
4276
4277 l_number_part NUMBER := 0;
4278 l_counter NUMBER := 0;
4279 l_from_number VARCHAR2(30);
4280 l_to_number VARCHAR2(30);
4281 l_length NUMBER;
4282 l_padded_length NUMBER;
4283 p_prefix VARCHAR(30);
4284 x_quantity NUMBER;
4285 x_errorcode NUMBER;
4286 l_fm_serial INV_VALIDATE.SERIAL_NUMBER_TBL;
4287 l_to_serial INV_VALIDATE.SERIAL_NUMBER_TBL;
4288 x_errored_serials INV_VALIDATE.SERIAL_NUMBER_TBL;
4289 --
4290 l_debug_on BOOLEAN;
4291 --
4292 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SERIAL_RANGE';
4293
4294 BEGIN
4295 --
4296 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4297 --
4298 IF l_debug_on IS NULL
4299 THEN
4300 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4301 END IF;
4302 --
4303 IF l_debug_on THEN
4304 WSH_DEBUG_SV.push(l_module_name);
4305 WSH_DEBUG_SV.log(l_module_name, 'From Serial Num',p_from_serial_number);
4306 WSH_DEBUG_SV.log(l_module_name, 'To Serial Num', p_to_serial_number);
4307 WSH_DEBUG_SV.log(l_module_name, 'Lot Number', p_lot_number);
4308 WSH_DEBUG_SV.log(l_module_name, 'Organization Id', p_organization_id);
4309 WSH_DEBUG_SV.log(l_module_name, 'Inventory Item id', p_inventory_item_id);
4310 WSH_DEBUG_SV.log(l_module_name, 'Subinventory', p_subinventory);
4311 WSH_DEBUG_SV.log(l_module_name, 'Revision', p_revision);
4312 WSH_DEBUG_SV.log(l_module_name, 'Locator Id', p_locator_id);
4313 WSH_DEBUG_SV.log(l_module_name, 'Quantity', p_quantity);
4314 WSH_DEBUG_SV.log(l_module_name, 'p_transaction_type_id', p_transaction_type_id);
4315 WSH_DEBUG_SV.log(l_module_name, 'p_object_type', p_object_type);
4316 END IF;
4317 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4318 g_org.organization_id := p_organization_id;
4319
4320 IF l_debug_on THEN
4321 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
4322 END IF;
4323
4324 l_result := INV_VALIDATE.organization(g_org);
4325
4326 IF(l_debug_on) THEN
4327 wsh_debug_sv.log(l_module_name, 'Org Result', l_result);
4328 END IF;
4329
4330 IF (l_result <> INV_VALIDATE.T) THEN
4331 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
4332 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Organization');
4333 raise FND_API.G_EXC_ERROR;
4334 END IF;
4335 g_item.inventory_item_id := p_inventory_item_id;
4336
4337 IF l_debug_on THEN
4338 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.INVENTORY_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
4339 END IF;
4340 l_result := wsh_delivery_details_inv.inventory_item(
4341 p_item => g_item,
4342 p_org => g_org);
4343
4344 IF(l_debug_on) THEN
4345 wsh_debug_sv.log(l_module_name, 'Inv Item Result', l_result);
4346 END IF;
4347
4348 IF (l_result <> INV_VALIDATE.T) THEN
4349 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
4350 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Item');
4351 raise FND_API.G_EXC_ERROR;
4352 END IF;
4353
4354 g_sub.secondary_inventory_name := p_subinventory;
4355
4356 IF l_debug_on THEN
4357 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.FROM_SUBINVENTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
4358 END IF;
4359 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
4360 l_result := INV_VALIDATE.From_Subinventory(
4361 p_org => g_org,
4362 p_sub => g_sub,
4363 p_item => g_item,
4364 p_trx_type_id => p_transaction_type_id,
4365 p_object_type => p_object_type,
4366 p_acct_txn => 1);
4367 ELSE
4368 l_result := INV_VALIDATE.From_Subinventory(
4369 p_org => g_org,
4370 p_sub => g_sub,
4371 p_item => g_item,
4372 p_acct_txn => 1);
4373 END IF;
4374 IF(l_debug_on) THEN
4375 wsh_debug_sv.log(l_module_name, 'Subinv Result', l_result);
4376 END IF;
4377
4378 IF (l_result <> INV_VALIDATE.T) THEN
4379 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
4380 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
4381 raise FND_API.G_EXC_ERROR;
4382 END IF;
4383
4384 g_loc.inventory_location_id := p_locator_id;
4385 -- Need to check if locator is NULL cause inventory validation API will
4386 -- validate on flex field and return false when locator is NULL
4387 IF (p_locator_id IS NOT NULL) THEN
4388 IF l_debug_on THEN
4389 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATELOCATOR',WSH_DEBUG_SV.C_PROC_LEVEL);
4390 END IF;
4391 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
4392 l_result := INV_VALIDATE.validateLocator(
4393 p_locator => g_loc,
4394 p_org => g_org,
4395 p_sub => g_sub,
4396 p_trx_type_id => p_transaction_type_id,
4397 p_object_type => p_object_type,
4398 p_item => g_item);
4399 ELSE
4400 l_result := INV_VALIDATE.validateLocator(
4401 p_locator => g_loc,
4402 p_org => g_org,
4403 p_sub => g_sub,
4404 p_item => g_item);
4405 END IF;
4406 IF(l_debug_on) THEN
4407 wsh_debug_sv.log(l_module_name, 'Locator Result', l_result);
4408 END IF;
4409
4410 IF (l_result = INV_VALIDATE.F) THEN
4411 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
4412 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Locator');
4413 raise FND_API.G_EXC_ERROR;
4414 END IF;
4415 END IF;
4416
4417 g_lot.lot_number := p_lot_number;
4418 IF (p_lot_number IS NOT NULL) THEN
4419 IF l_debug_on THEN
4420 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.LOT_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
4421 END IF;
4422 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
4423 l_result := INV_VALIDATE.Lot_Number(
4424 p_lot => g_lot,
4425 p_org => g_org,
4426 p_item => g_item,
4427 p_trx_type_id => p_transaction_type_id,
4428 p_object_type => p_object_type,
4429 p_from_sub => g_sub,
4430 p_loc => g_loc,
4431 p_revision => p_revision);
4432 ELSE
4433 l_result := INV_VALIDATE.Lot_Number(
4434 p_lot => g_lot,
4435 p_org => g_org,
4436 p_item => g_item,
4437 p_from_sub => g_sub,
4438 p_loc => g_loc,
4439 p_revision => p_revision);
4440 END IF;
4441 IF(l_debug_on) THEN
4442 wsh_debug_sv.log(l_module_name, 'Lot Number Result', l_result);
4443 END IF;
4444
4445 IF (l_result <> INV_VALIDATE.T) THEN
4446 FND_MESSAGE.SET_NAME('WSH','WSH_INV_INVALID');
4447 FND_MESSAGE.SET_TOKEN('INV_ATTRIBUTE', 'Lot');--Material Status Impact
4448 raise FND_API.G_EXC_ERROR;
4449 END IF;
4450 END IF;
4451
4452 IF l_debug_on THEN
4453 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit MTL_SERIAL_CHECK.INV_SERIAL_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
4454 END IF;
4455
4456 IF NOT MTL_SERIAL_CHECK.INV_SERIAL_INFO(p_from_serial_number => p_from_serial_number ,
4457 p_to_serial_number => p_to_serial_number ,
4458 x_prefix => x_prefix,
4459 x_quantity => x_quantity,
4460 x_from_number => l_from_number,
4461 x_to_number => l_to_number,
4462 x_errorcode => x_errorcode)
4463 THEN
4464 x_result := FALSE;
4465 IF l_debug_on THEN
4466 WSH_DEBUG_SV.pop(l_module_name);
4467 END IF;
4468 RETURN;
4469 END IF;
4470 IF (x_quantity <> p_quantity) THEN
4471 x_result := false;
4472 IF l_debug_on THEN
4473 WSH_DEBUG_SV.pop(l_module_name);
4474 END IF;
4475 RETURN;
4476 END IF;
4477 l_number_part := TO_NUMBER(l_FROM_NUMBER);
4478 l_counter := 1;
4479 -- Get the length of the serial number
4480 l_length := length(p_from_serial_number);
4481
4482 IF(l_debug_on) THEN
4483 wsh_debug_sv.log(l_module_name, 'Length ', l_length);
4484 END IF;
4485
4486 IF p_transaction_type_id IS NOT NULL AND p_object_type IS NOT NULL THEN
4487 l_fm_serial(1) := p_from_serial_number;
4488 l_to_serial(1) := p_to_serial_number;
4489 l_result := INV_VALIDATE.validate_serial_range(
4490 p_fm_serial => l_fm_serial,
4491 p_to_serial => l_to_serial,
4492 p_org => g_org,
4493 p_item => g_item ,
4494 p_from_sub => g_sub ,
4495 p_lot => g_lot,
4496 p_loc => g_loc,
4497 p_revision => p_revision,
4498 p_trx_type_id => p_transaction_type_id,
4499 p_object_type => p_object_type,
4500 x_errored_serials => x_errored_serials);
4501 IF (l_result = INV_VALIDATE.T) THEN
4502 x_result := TRUE;
4503 ELSE
4504 x_result := FALSE;
4505 FOR i in 1..x_errored_serials.count LOOP
4506 IF l_debug_on THEN
4507 wsh_debug_sv.log(l_module_name, 'errored serial_number'||to_char(i), x_errored_serials(i));
4508 END IF;
4509 END LOOP;
4510 END IF;
4511 ELSE
4512 WHILE (l_counter <= x_quantity) LOOP
4513
4514 -- The padded length will be the length of the serial number minus
4515 -- the length of the number part
4516 -- Fix by etam
4517 l_padded_length := l_length - length(l_number_part);
4518 g_serial.serial_number := RPAD(nvl(x_Prefix,'0'), l_padded_length, '0') ||l_number_part;
4519
4520 IF l_debug_on THEN
4521 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_VALIDATE.VALIDATE_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
4522 END IF;
4523 l_result := INV_VALIDATE.Validate_serial(
4524 p_serial => g_serial,
4525 p_lot => g_lot,
4526 p_org => g_org,
4527 p_item => g_item,
4528 p_from_sub => g_sub,
4529 p_loc => g_loc,
4530 p_revision => p_revision);
4531
4532 IF l_debug_on THEN
4533 wsh_debug_sv.log(l_module_name, 'Serial Result', l_result);
4534 END IF;
4535
4536 IF (l_result = INV_VALIDATE.T) THEN
4537 x_result := TRUE;
4538 ELSE
4539 x_result := FALSE;
4540 EXIT;
4541 END IF;
4542 l_number_part := l_number_part + 1;
4543 l_counter := l_counter + 1;
4544 END LOOP;
4545 IF (l_result = INV_VALIDATE.T) THEN
4546 x_result := TRUE;
4547 ELSE
4548 x_result := FALSE;
4549 END IF;
4550 END IF;
4551 IF l_debug_on THEN
4552 WSH_DEBUG_SV.pop(l_module_name);
4553 END IF;
4554
4555 EXCEPTION
4556 WHEN FND_API.G_EXC_ERROR THEN
4557 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4558 WSH_UTIL_CORE.Add_Message(x_return_status);
4559 IF l_debug_on THEN
4560 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4561 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4562 END IF;
4563
4564 WHEN others THEN
4565 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4566 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Validate_Serial_Range');
4567 IF l_debug_on THEN
4568 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4569 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4570 END IF;
4571
4572 END Validate_Serial_Range;
4573
4574 PROCEDURE Create_Dynamic_Serial_Range(
4575 p_from_number IN VARCHAR2,
4576 p_to_number IN VARCHAR2,
4577 p_source_line_id IN NUMBER,
4578 p_delivery_detail_id IN NUMBER,
4579 p_inventory_item_id IN NUMBER,
4580 p_organization_id IN NUMBER,
4581 p_revision IN VARCHAR2,
4582 p_lot_number IN VARCHAR2,
4583 p_subinventory IN VARCHAR2,
4584 p_locator_id IN NUMBER,
4585 p_quantity IN NUMBER,
4586 x_prefix OUT NOCOPY VARCHAR2,
4587 x_return_status OUT NOCOPY VARCHAR2)
4588 IS
4589
4590 cursor c_header_info(c_delivery_detail_id number) is
4591 select nvl(dd.source_document_type_id, -9999) source_document_type_id
4592 from wsh_delivery_details dd
4593 where dd.delivery_detail_id = c_delivery_detail_id;
4594
4595 l_header_info c_header_info%ROWTYPE;
4596 l_return_status VARCHAR2(300);
4597 l_msg_count NUMBER;
4598 l_msg_data VARCHAR2(300);
4599 l_error_code NUMBER;
4600 l_quantity NUMBER;
4601 l_prefix VARCHAR2(240);
4602 l_serial_number_type NUMBER;
4603 l_trx_action_id NUMBER;
4604 l_trx_source_type VARCHAR2(1);
4605 l_to_number VARCHAR2(100);
4606 l_return NUMBER;
4607 l_mtl_org_param_rec WSH_DELIVERY_DETAILS_INV.mtl_org_param_rec;
4608 --
4609 l_debug_on BOOLEAN;
4610 --
4611 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DYNAMIC_SERIAL_RANGE';
4612
4613 BEGIN
4614 --
4615 --
4616 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4617 --
4618 IF l_debug_on IS NULL
4619 THEN
4620 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4621 END IF;
4622 --
4623 IF l_debug_on THEN
4624 WSH_DEBUG_SV.push(l_module_name);
4625 WSH_DEBUG_SV.log(l_module_name,'P_FROM_NUMBER',P_FROM_NUMBER);
4626 WSH_DEBUG_SV.log(l_module_name,'P_TO_NUMBER',P_TO_NUMBER);
4627 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
4628 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
4629 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
4630 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
4631 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
4632 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
4633 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
4634 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
4635 WSH_DEBUG_SV.log(l_module_name, 'P_QUANTITY',p_quantity);
4636 END IF;
4637
4638 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4639
4640 -- bug 5264874
4641 Get_Org_Param_information (p_organization_id => p_organization_id
4642 , x_mtl_org_param_rec => l_mtl_org_param_rec
4643 , x_return_status => l_return_status);
4644
4645 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4646 RAISE FND_API.G_EXC_ERROR;
4647 END IF;
4648
4649 l_serial_number_type := l_mtl_org_param_rec.serial_number_type;
4650 IF l_serial_number_type = -99 THEN
4651 raise FND_API.G_EXC_ERROR;
4652 END IF;
4653 -- bug 5264874 end
4654
4655 open c_header_info(p_delivery_detail_id);
4656 fetch c_header_info into l_header_info;
4657 if c_header_info%NOTFOUND then
4658 l_header_info.source_document_type_id := -9999;
4659 end if;
4660 close c_header_info;
4661
4662 IF l_debug_on THEN
4663 WSH_DEBUG_SV.log(l_module_name, 'Serial Number Type', l_serial_number_type);
4664 END IF;
4665
4666 l_to_number := p_to_number;
4667
4668 IF l_debug_on THEN
4669 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.TRX_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
4670 END IF;
4671 l_trx_action_id := WSH_DETAILS_VALIDATIONS.Trx_Id('TRX_ACTION_ID',
4672 p_source_line_id,
4673 l_header_info.source_document_type_id);
4674
4675 IF l_header_info.source_document_type_id = 10 THEN
4676 l_trx_source_type := '8';
4677 ELSIF l_header_info.source_document_type_id <> 10 THEN
4678 l_trx_source_type := '2';
4679 END IF;
4680
4681 IF l_debug_on THEN
4682 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_SERIAL_NUMBER_PUB.VALIDATE_SERIALS',WSH_DEBUG_SV.C_PROC_LEVEL);
4683 END IF;
4684
4685 l_return := INV_SERIAL_NUMBER_PUB.VALIDATE_SERIALS(
4686 p_org_id => p_organization_id,
4687 p_item_id => p_inventory_item_id,
4688 p_qty => l_quantity,
4689 p_rev => p_revision,
4690 p_lot => p_lot_number,
4691 p_start_ser => p_from_number,
4692 p_trx_src_id => l_trx_source_type,
4693 p_trx_action_id => l_trx_action_id,
4694 p_subinventory_code =>p_subinventory,
4695 p_locator_id =>p_locator_id,
4696 p_group_mark_id => NULL,
4697 p_issue_receipt => 'I',
4698 x_end_ser => l_to_number,
4699 x_proc_msg => l_msg_data,
4700 p_check_for_grp_mark_id => 'Y' --Bug# 2656316
4701 );
4702
4703 IF l_return = 1 THEN
4704 RAISE FND_API.G_EXC_ERROR;
4705 END IF;
4706
4707
4708
4709 IF l_debug_on THEN
4710 wsh_debug_sv.log(l_module_name, 'Quantity', l_quantity);
4711 wsh_debug_sv.log(l_module_name, 'Prefix', l_prefix);
4712 END IF;
4713 IF (l_quantity <> p_quantity) THEN
4714 fnd_message.set_name('WSH', 'WSH_SERIAL_NUM_WRG_RANGE');
4715 RAISE FND_API.G_EXC_ERROR;
4716 END IF;
4717
4718 x_prefix := l_prefix;
4719
4720 IF l_debug_on THEN
4721 WSH_DEBUG_SV.pop(l_module_name);
4722 END IF;
4723
4724 EXCEPTION
4725 WHEN FND_API.G_EXC_ERROR THEN
4726 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4727 WSH_UTIL_CORE.Add_Message(x_return_status);
4728 IF l_debug_on THEN
4729 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4730 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4731 END IF;
4732 WHEN others THEN
4733 /*IF c_serial_type%ISOPEN THEN
4734 close c_serial_type;
4735 END IF;*/
4736 IF c_header_info%ISOPEN THEN
4737 close c_header_info;
4738 END IF;
4739 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4740 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Create_Dynamic_Serial_Range');
4741
4742 IF l_debug_on THEN
4743 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4745 END IF;
4746
4747 END Create_Dynamic_Serial_Range;
4748
4749
4750 -- Pack J - Catch Weights
4751 -- This procedure checks if the catch weight can be
4752 -- defaulted in a wms organization and, if possible, defaults it.
4753 -- If the catch weight is required and cannot be defaulted,
4754 -- raises an error
4755
4756 PROCEDURE Check_Default_Catch_Weights(p_line_inv_rec IN WSH_DELIVERY_DETAILS_INV.line_inv_info,
4757 x_return_status OUT NOCOPY VARCHAR2) IS
4758
4759 l_msg_count NUMBER;
4760 l_msg_data VARCHAR2(20000);
4761 l_wms_table WMS_SHIPPING_INTERFACE_GRP.g_delivery_detail_tbl;
4762
4763 --
4764 l_debug_on BOOLEAN;
4765 --
4766 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Default_Catch_Weights';
4767
4768 BEGIN
4769
4770
4771 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4772 --
4773 IF l_debug_on IS NULL THEN
4774 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4775 END IF;
4776
4777 IF l_debug_on THEN
4778 WSH_DEBUG_SV.push(l_module_name);
4779 WSH_DEBUG_SV.log(l_module_name,'p_line_inv_rec.delivery_detail_id', p_line_inv_rec.delivery_detail_id);
4780 END IF;
4781
4782 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4783
4784 IF l_debug_on THEN
4785 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_SHIPPING_INTERFACE_GRP.process_delivery_details',WSH_DEBUG_SV.C_PROC_LEVEL);
4786 END IF;
4787
4788 -- Call to WMS API to check/default catch weights
4789
4790 l_wms_table(1).delivery_detail_id := p_line_inv_rec.delivery_detail_id;
4791 l_wms_table(1).inventory_item_id := p_line_inv_rec.inventory_item_id;
4792 l_wms_table(1).organization_id := p_line_inv_rec.organization_id;
4793 l_wms_table(1).picked_quantity := p_line_inv_rec.picked_quantity;
4794 l_wms_table(1).picked_quantity2 := p_line_inv_rec.picked_quantity2;
4795 l_wms_table(1).requested_quantity_uom := p_line_inv_rec.requested_quantity_uom;
4796 l_wms_table(1).requested_quantity_uom2 := p_line_inv_rec.requested_quantity_uom2;
4797 l_wms_table(1).source_line_id := p_line_inv_rec.source_line_id;
4798 l_wms_table(1).line_direction := p_line_inv_rec.line_direction;
4799
4800
4801 WMS_SHIPPING_INTERFACE_GRP.process_delivery_details (
4802 p_api_version => 1.0,
4803 p_action => WMS_SHIPPING_INTERFACE_GRP.g_action_validate_sec_qty,
4804 p_delivery_detail_tbl => l_wms_table,
4805 x_return_status => x_return_status,
4806 x_msg_count => l_msg_count,
4807 x_msg_data => l_msg_data);
4808
4809 IF l_debug_on THEN
4810 WSH_DEBUG_SV.log(l_module_name,'return status after calling WMS_SHIPPING_INTERFACE_GRP.process_delivery_details', x_return_status);
4811 WSH_DEBUG_SV.log(l_module_name,'status of dd after calling WMS_SHIPPING_INTERFACE_GRP.process_delivery_details', l_wms_table(1).return_status);
4812 END IF;
4813
4814 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) OR
4815 (l_wms_table(1).return_status = 'E')
4816 THEN
4817 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4818 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CATCHWEIGHT');
4819 FND_MESSAGE.SET_TOKEN('DEL_DET', p_line_inv_rec.delivery_detail_id);
4820 WSH_UTIL_CORE.Add_Message(x_return_status);
4821 END IF;
4822
4823 IF l_debug_on THEN
4824 WSH_DEBUG_SV.pop(l_module_name);
4825 END IF;
4826
4827 EXCEPTION
4828
4829 WHEN others THEN
4830 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4831 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Check_Default_Catch_Weights');
4832
4833 IF l_debug_on THEN
4834 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4836 END IF;
4837
4838 END Check_Default_Catch_Weights;
4839
4840 -- HW OPMCONV - New procedure to get item information
4841 /*
4842 -----------------------------------------------------------------------------
4843 PROCEDURE : Get_item_information
4844 PARAMETERS : p_organization_id - organization id
4845 p_inventory_item_id - source system code
4846 x_mtl_system_items_rec - Record to hold item informatiom
4847 x_return_status - success if able to look up item information
4848 error if cannot find item information
4849
4850 DESCRIPTION : This API takes the organization and inventory item
4851 and checks if item information is already cached, if
4852 not, it loads the new item information for a specific
4853 organization
4854 -----------------------------------------------------------------------------
4855 */
4856
4857 PROCEDURE Get_item_information (
4858 p_organization_id IN NUMBER
4859 , p_inventory_item_id IN NUMBER
4860 , x_mtl_system_items_rec OUT NOCOPY WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec
4861 , x_return_status OUT NOCOPY VARCHAR2
4862 )IS
4863
4864
4865
4866 l_debug_on BOOLEAN;
4867 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_item_information';
4868 TYPE item_info_cache_tab IS TABLE OF c_item_info%ROWTYPE;
4869
4870 l_index NUMBER;
4871 l_flag VARCHAR2(1);
4872 l_return_status VARCHAR2(1);
4873 l_cache_rec c_item_info%ROWTYPE;
4874 -- 2nd cursor
4875 l_item_index NUMBER;
4876 l_item_flag VARCHAR2(1);
4877 l_item_return_status VARCHAR2(1);
4878
4879 BEGIN
4880
4881 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4882 IF l_debug_on IS NULL
4883 THEN
4884 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4885 END IF;
4886 IF l_debug_on THEN
4887 WSH_DEBUG_SV.push(l_module_name);
4888 WSH_DEBUG_SV.log(l_module_name,'p_organization_id',p_organization_id);
4889 WSH_DEBUG_SV.log(l_module_name,'p_inventory_item_id',p_inventory_item_id);
4890 END IF;
4891
4892 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4893
4894
4895 IF (p_organization_id IS NOT NULL AND p_inventory_item_id IS NOT NULL) THEN
4896 l_cache_rec.organization_id := (p_organization_id);
4897 l_cache_rec.inventory_item_id := (p_inventory_item_id);
4898
4899 get_item_table_index
4900 (p_validate_rec => l_cache_rec,
4901 p_item_tab => g_item_tab,
4902 x_index => l_index,
4903 x_return_status => l_return_status,
4904 x_flag => l_flag
4905 );
4906
4907 IF l_flag = 'U' AND l_index IS NOT NULL THEN
4908 IF l_debug_on THEN
4909 WSH_DEBUG_SV.log(l_module_name,'Org is not cached yet: ',p_organization_id);
4910 WSH_DEBUG_SV.log(l_module_name,'Item_id is not cached yet: ',p_inventory_item_id);
4911 END IF ;
4912 OPEN c_item_info (p_organization_id,p_inventory_item_id);
4913
4914 FETCH c_item_info INTO l_cache_rec;
4915 IF (c_item_info%NOTFOUND) THEN
4916
4917 l_cache_rec.organization_id := p_organization_id;
4918 l_cache_rec.inventory_item_id := p_inventory_item_id;
4919 l_cache_rec.valid_flag := 'N';
4920 END IF;
4921 CLOSE c_item_info;
4922 -- add record to cache
4923 g_item_tab(l_index) := l_cache_rec;
4924 ELSE
4925 IF l_debug_on THEN
4926 WSH_DEBUG_SV.log(l_module_name,'Org is already chached: ',p_organization_id);
4927 WSH_DEBUG_SV.log(l_module_name,'Inv_item_id is already chached',p_inventory_item_id);
4928 END IF;
4929 -- retrieve record from cache
4930 l_cache_rec := g_item_tab(l_index);
4931 END IF;
4932
4933 -- At this point, l_cache_rec has the cache information.
4934 -- you can alternately use g_item_tab(l_index).
4935
4936 IF l_cache_rec.valid_flag = 'N' THEN
4937 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ORG');
4938 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4939 wsh_util_core.add_message(x_return_status,l_module_name);
4940
4941 IF l_debug_on THEN
4942 WSH_DEBUG_SV.pop(l_module_name);
4943 END IF;
4944
4945 RETURN;
4946 END IF;
4947 -- Always populate the values
4948
4949 x_mtl_system_items_rec.primary_uom_code:= l_cache_rec.primary_uom_code;
4950 /* Lgao, bug 5137114, the secondary_default_ind only has meanings when the
4951 * tracking_quantity_ind is for both primary and secondary in inventory.
4952 */
4953 if l_cache_rec.tracking_quantity_ind = 'PS' then
4954 x_mtl_system_items_rec.secondary_default_ind:=l_cache_rec.secondary_default_ind;
4955 x_mtl_system_items_rec.secondary_uom_code:=l_cache_rec.secondary_uom_code;
4956 else
4957 x_mtl_system_items_rec.secondary_default_ind:='';
4958 x_mtl_system_items_rec.secondary_uom_code:= '';
4959 end if;
4960 x_mtl_system_items_rec.lot_control_code:=l_cache_rec.lot_control_code;
4961
4962 x_mtl_system_items_rec.tracking_quantity_ind:=l_cache_rec.tracking_quantity_ind;
4963 x_mtl_system_items_rec.dual_uom_deviation_low:=l_cache_rec.dual_uom_deviation_low;
4964 x_mtl_system_items_rec.dual_uom_deviation_high:=l_cache_rec.dual_uom_deviation_high;
4965 x_mtl_system_items_rec.enabled_flag:=l_cache_rec.enabled_flag;
4966 x_mtl_system_items_rec.shippable_item_flag:=l_cache_rec.shippable_item_flag;
4967 x_mtl_system_items_rec.inventory_item_flag:=l_cache_rec.inventory_item_flag;
4968 x_mtl_system_items_rec.lot_divisible_flag:=l_cache_rec.lot_divisible_flag;
4969 x_mtl_system_items_rec.container_item_flag:=l_cache_rec.container_item_flag;
4970 x_mtl_system_items_rec.reservable_type:=l_cache_rec.reservable_type;
4971 x_mtl_system_items_rec.mtl_transactions_enabled_flag:=l_cache_rec.mtl_transactions_enabled_flag;
4972
4973 ELSE -- Both or one is NULL (Org and Inv_item)
4974 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4975 IF l_debug_on THEN
4976 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
4977 END IF;
4978 --
4979 RETURN;
4980 END IF;
4981
4982 IF l_debug_on THEN
4983 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.primary_uom_code',x_mtl_system_items_rec.primary_uom_code);
4984 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.secondary_uom_code',x_mtl_system_items_rec.secondary_uom_code);
4985 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.secondary_default_ind',x_mtl_system_items_rec.secondary_default_ind);
4986 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.lot_control_code',x_mtl_system_items_rec.lot_control_code);
4987 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.tracking_quantity_ind',x_mtl_system_items_rec.tracking_quantity_ind);
4988 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.dual_uom_deviation_low',x_mtl_system_items_rec.dual_uom_deviation_low);
4989 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.dual_uom_deviation_high',x_mtl_system_items_rec.dual_uom_deviation_high);
4990 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.enabled_flag',x_mtl_system_items_rec.enabled_flag);
4991 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.shippable_item_flag',x_mtl_system_items_rec.shippable_item_flag);
4992 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.inventory_item_flag',x_mtl_system_items_rec.inventory_item_flag);
4993 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.lot_divisible_flag',x_mtl_system_items_rec.lot_divisible_flag);
4994 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.container_item_flag',x_mtl_system_items_rec.container_item_flag);
4995 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.reservable_type',x_mtl_system_items_rec.reservable_type);
4996 WSH_DEBUG_SV.log(l_module_name,'x_mtl_system_items_rec.mtl_transactions_enabled_flag',x_mtl_system_items_rec.mtl_transactions_enabled_flag);
4997 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
4998 END IF;
4999
5000 EXCEPTION
5001
5002 WHEN others THEN
5003 wsh_util_core.default_handler ('WSH_DELIVERY_DETAILS_INV .Get_item_information');
5004 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5005
5006 IF l_debug_on THEN
5007 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5008 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5009 END IF;
5010
5011 IF c_item_info%isopen THEN
5012 close c_item_info;
5013 END IF;
5014
5015 END Get_item_information;
5016
5017
5018 /*
5019 -----------------------------------------------------------------------------
5020 PROCEDURE : Update_Marked_Serial
5021 PARAMETERS : p_from_serial_number - serial number to be marked with new
5022 transaction_temp_id
5023 p_to_serial_number - to serial number
5024 p_inventory_item_id - inventory item
5025 p_organization_id - organization_id
5026 p_transaction_temp_id - newly generated transaction temp id
5027 for serial number
5028 x_return_status - return status of the API
5029 DESCRIPTION : Call Inventory's update_marked_serial API which will take
5030 serial number and new transaction_temp_id as input and
5031 mark the serial number with the new transaction_temp_id
5032 -----------------------------------------------------------------------------
5033 */
5034 PROCEDURE Update_Marked_Serial (
5035 p_from_serial_number IN VARCHAR2,
5036 p_to_serial_number IN VARCHAR2 DEFAULT NULL,
5037 p_inventory_item_id IN NUMBER,
5038 p_organization_id IN NUMBER,
5039 p_transaction_temp_id IN NUMBER,
5040 x_return_status OUT NOCOPY VARCHAR2)
5041 IS
5042 --
5043 l_success BOOLEAN;
5044 l_debug_on BOOLEAN;
5045 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_MARKED_SERIAL';
5046 --
5047 BEGIN
5048 --
5049 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5050 --
5051 IF l_debug_on IS NULL THEN
5052 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5053 END IF;
5054 --
5055 IF l_debug_on THEN
5056 WSH_DEBUG_SV.push(l_module_name);
5057 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SERIAL_NUMBER',P_FROM_SERIAL_NUMBER);
5058 WSH_DEBUG_SV.log(l_module_name,'P_TO_SERIAL_NUMBER',P_TO_SERIAL_NUMBER);
5059 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
5060 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
5061 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TEMP_ID',
5062 P_TRANSACTION_TEMP_ID);
5063 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit SERIAL_CHECK.INV_UPDATE_MARKED_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
5064 END IF;
5065 --
5066 Serial_Check.Inv_Update_Marked_Serial (
5067 from_serial_number => p_from_serial_number,
5068 to_serial_number => p_to_serial_number, -- this should be NULL for single serial
5069 item_id => p_inventory_item_id,
5070 org_id => p_organization_id,
5071 temp_id => p_transaction_temp_id,
5072 hdr_id => NULL,
5073 lot_temp_id => NULL,
5074 success => l_success);
5075 --
5076 IF NOT l_success THEN
5077 --
5078 FND_MESSAGE.SET_NAME('WSH','WSH_SERIAL_MARK_ERROR');
5079 FND_MESSAGE.SET_TOKEN('SERIAL_NUM',p_from_serial_number);
5080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5081 WSH_UTIL_CORE.Add_Message(x_return_status);
5082 --
5083 IF l_debug_on THEN
5084 WSH_DEBUG_SV.log(l_module_name, 'Return status after INV API',
5085 x_return_status);
5086 WSH_DEBUG_SV.pop(l_module_name);
5087 END IF;
5088 --
5089 RETURN;
5090 --
5091 END IF;
5092 --
5093 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5094 --
5095 IF l_debug_on THEN
5096 WSH_DEBUG_SV.pop(l_module_name);
5097 END IF;
5098 --
5099 EXCEPTION
5100 --
5101 WHEN OTHERS THEN
5102 --
5103 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.Update_Marked_Serial');
5104 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5105 --
5106 IF l_debug_on THEN
5107 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5108 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5109 END IF;
5110 --
5111 END Update_Marked_Serial;
5112
5113
5114 PROCEDURE get_trx_type_id(
5115 p_source_line_id IN NUMBER,
5116 p_source_code IN VARCHAR2,
5117 x_transaction_type_id OUT NOCOPY NUMBER,
5118 x_return_status OUT NOCOPY VARCHAR2) IS
5119
5120 CURSOR c_order_line_info(c_order_line_id number) IS
5121 SELECT source_document_type_id, source_document_id, source_document_line_id
5122 FROM oe_order_lines_all
5123 WHERE line_id = c_order_line_id;
5124
5125 l_order_line_info c_order_line_info%ROWTYPE;
5126
5127 CURSOR c_po_info(c_po_line_id number, c_source_document_id number) IS
5128 SELECT destination_type_code,
5129 destination_subinventory,
5130 source_organization_id,
5131 destination_organization_id,
5132 deliver_to_location_id,
5133 pl.requisition_line_id,
5134 pd.distribution_id,
5135 pl.unit_price,
5136 nvl(pd.budget_account_id,-1) budget_account_id,
5137 decode(nvl(pd.prevent_encumbrance_flag,'N'),'N',nvl(pd.encumbered_flag,'N'),'N') encumbered_flag
5138 FROM po_requisition_lines_all pl,
5139 po_req_distributions_all pd
5140 WHERE pl.requisition_line_id = c_po_line_id
5141 AND pl.requisition_header_id = c_source_document_id
5142 AND pl.requisition_line_id = pd.requisition_line_id;
5143
5144 l_po_info c_po_info%ROWTYPE;
5145
5146 CURSOR c_mtl_interorg_parameters (c_from_organization_id NUMBER , c_to_organization_id NUMBER) IS
5147 SELECT intransit_type
5148 FROM mtl_interorg_parameters
5149 WHERE from_organization_id = c_from_organization_id AND
5150 to_organization_id = c_to_organization_id;
5151 l_intransit_type NUMBER;
5152
5153 l_debug_on BOOLEAN;
5154 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRX_TYPE_ID';
5155
5156 BEGIN
5157 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5158 --
5159 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5160 --
5161 IF l_debug_on IS NULL THEN
5162 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5163 END IF;
5164 --
5165 IF l_debug_on THEN
5166 WSH_DEBUG_SV.push(l_module_name);
5167 WSH_DEBUG_SV.log(l_module_name,'p_source_line_id',p_source_line_id);
5168 WSH_DEBUG_SV.log(l_module_name,'p_source_code',p_source_code);
5169 END IF;
5170
5171 IF p_source_code ='OE' THEN --{
5172
5173 OPEN c_order_line_info(p_source_line_id);
5174 FETCH c_order_line_info into l_order_line_info;
5175 IF c_order_line_info%NOTFOUND THEN
5176 CLOSE c_order_line_info;
5177 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5178 IF l_debug_on THEN
5179 WSH_DEBUG_SV.log(l_module_name, 'No data found for order line',p_source_line_id);
5180 WSH_DEBUG_SV.pop(l_module_name);
5181 END IF;
5182 return;
5183 END IF;
5184 CLOSE c_order_line_info;
5185
5186 IF (l_order_line_info.source_document_type_id = 10) THEN --Internal Sales order
5187
5188 OPEN c_po_info(l_order_line_info.source_document_line_id, l_order_line_info.source_document_id);
5189 FETCH c_po_info into l_po_info;
5190 IF c_po_info%NOTFOUND THEN
5191 CLOSE c_po_info;
5192 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5193 IF l_debug_on THEN
5194 WSH_DEBUG_SV.logmsg(l_module_name, 'No data found for PO '||l_order_line_info.source_document_line_id||' ,'||l_order_line_info.source_document_id);
5195 WSH_DEBUG_SV.pop(l_module_name);
5196 END IF;
5197 return;
5198 END IF;
5199 CLOSE c_po_info;
5200 IF (l_po_info.destination_type_code = 'EXPENSE') THEN
5201 x_transaction_type_id := 34 /* Store_issue */;
5202 ELSIF (l_po_info.destination_type_code = 'INVENTORY') AND
5203 (l_po_info.source_organization_id = l_po_info.destination_organization_id) THEN
5204 x_transaction_type_id := 50 /* Subinv_xfer */;
5205 ELSIF (l_po_info.destination_organization_id <> l_po_info.source_organization_id) THEN
5206
5207 OPEN c_mtl_interorg_parameters( l_po_info.source_organization_id,
5208 l_po_info.destination_organization_id);
5209 FETCH c_mtl_interorg_parameters INTO l_intransit_type;
5210
5211 IF c_mtl_interorg_parameters%NOTFOUND THEN
5212 /* default to intransit */
5213 x_transaction_type_id := 62; /* intransit_shpmnt */
5214 ELSE
5215 IF l_intransit_type = 1 THEN
5216 x_transaction_type_id := 54; /* direct shipment */
5217 ELSE
5218 x_transaction_type_id := 62; /* intransit_shpmnt */
5219 END IF;
5220 END IF;
5221 CLOSE c_mtl_interorg_parameters;
5222 END IF;
5223 ELSE
5224 x_transaction_type_id := 33;
5225 END IF;
5226 --}
5227 ELSIF p_source_code ='OKE' THEN
5228 x_transaction_type_id := 77;
5229 ELSE
5230 x_transaction_type_id := 32; -- miscellaneous issue
5231 END IF;
5232
5233 IF l_debug_on THEN
5234 WSH_DEBUG_SV.log(l_module_name, 'x_transaction_type_id',x_transaction_type_id);
5235 WSH_DEBUG_SV.log(l_module_name, 'x_return_status',x_return_status);
5236 WSH_DEBUG_SV.pop(l_module_name);
5237 END IF;
5238
5239 EXCEPTION
5240
5241 WHEN OTHERS THEN
5242 --
5243 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_INV.get_trx_type_id');
5244 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5245 --
5246 IF c_order_line_info%isopen THEN
5247 CLOSE c_order_line_info;
5248 END IF;
5249 IF c_po_info%isopen THEN
5250 CLOSE c_po_info;
5251 END IF;
5252 IF c_mtl_interorg_parameters%isopen THEN
5253 CLOSE c_mtl_interorg_parameters;
5254 END IF;
5255 IF l_debug_on THEN
5256 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5257 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5258 END IF;
5259
5260 END get_trx_type_id;
5261
5262
5263 END WSH_DELIVERY_DETAILS_INV;
5264