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