[Home] [Help]
PACKAGE BODY: APPS.WSH_UTIL_VALIDATE
Source
1 PACKAGE BODY WSH_UTIL_VALIDATE as
2 /* $Header: WSHUTVLB.pls 120.16 2011/01/27 00:56:25 rvarghes ship $ */
3 --===================
4 -- PROCEDURES
5 --===================
6
7 --
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_UTIL_VALIDATE';
9 G_ITEM_INFO_TAB Item_Info_Tab_Type;
10 G_DEF_CONT_INF_TAB cont_def_info_tab_type;
11 G_DEF_CONT_INFO_EXT_TAB cont_def_info_tab_type;
12 G_IGNORE_PLAN_TAB ignore_plan_tab_type;
13
14 -- LPN CONV. rv
15 g_orgn_id_tbl WSH_UTIL_CORE.char500_tab_type;
16 g_orgn_id_ext_tbl WSH_UTIL_CORE.char500_tab_type;
17 g_organization_id NUMBER;
18 g_is_wms_org VARCHAR2(1);
19
20 -- LPN CONV. rv
21
22
23 -- Bug 3821688
24 -- The parameters can be populated as required, to make it generic
25 -- Output Variables store the results
26 -- Valid_Flag indicates the combination is valid or not with values = Y/N
27 TYPE Generic_Cache_Rec_Typ IS RECORD(
28 INPUT_PARAM1 VARCHAR2(500),
29 INPUT_PARAM2 VARCHAR2(500),
30 INPUT_PARAM3 VARCHAR2(500),
31 INPUT_PARAM4 VARCHAR2(500),
32 OUTPUT_PARAM1 VARCHAR2(500),
33 OUTPUT_PARAM2 VARCHAR2(500),
34 OUTPUT_PARAM3 VARCHAR2(500),
35 OUTPUT_PARAM4 VARCHAR2(500),
36 VALID_FLAG VARCHAR2(1)
37 );
38
39 TYPE Generic_Cache_Tab_Typ IS TABLE OF Generic_Cache_Rec_Typ INDEX BY BINARY_INTEGER;
40
41 -- Parameters will be Ship Method Code and Ship Method Name
42 g_ship_method_tab Generic_Cache_Tab_Typ;
43
44 -- Parameters will be Lookup Type,Lookup Code, Meaning
45 g_lookup_tab Generic_Cache_Tab_Typ;
46
47 -- Parameters will be organization_id,weight_uom class,volume_uom class
48 g_org_uom_class_tab Generic_Cache_Tab_Typ;
49
50 -- Parameters will be UOM Code, UOM description,type,class
51 g_uom_tab Generic_Cache_Tab_Typ;
52
53 --Standalone Project -- Start
54 --Input Parameter : Organization Id
55 --Output Parameters : WMS Enabled Flag, OPM Enabled Flag
56 g_sr_org_tab Generic_Cache_Tab_Typ;
57
58 --Input Parameter : Locator Code, Organization Id
59 --Output Parameters : Locator Id
60 g_locator_code_tab Generic_Cache_Tab_Typ;
61
62 --Input Parameter : Item Number, Organization Id
63 --Output Parameters : Inventory Item Id
64 g_inventory_item_tab Generic_Cache_Tab_Typ;
65
66 --Input Parameter : Item Number, Customer Id, Address Id
67 --Output Parameters : Customer Item Id
68 g_customer_item_tab Generic_Cache_Tab_Typ;
69
70 --Input Parameter : Carrier Code, Service Level, Mode of Transport, Organization Id
71 --Output Parameters : Ship Method Code
72 g_shipping_method_tab Generic_Cache_Tab_Typ;
73 --Standalone Project -- End
74
75 -- Forward Declaration of an Internal procedure
76 -- OTM R12, glog project
77 PROCEDURE Validate_Lookup_Upper
78 (p_lookup_type IN VARCHAR2,
79 p_lookup_code IN OUT NOCOPY VARCHAR2,
80 p_meaning IN VARCHAR2,
81 x_return_status OUT NOCOPY VARCHAR2);
82 -- End of Forward Declaration
83
84
85
86
87 -- Bug 3821688
88 --========================================================================
89 -- PROCEDURE : get_table_index
90 --
91 -- COMMENT : Validate using Hash (internal API)
92 -- uses Hash and avoids linear scans while using PL/SQL tables
93 -- Currently available for 4 parameters (VARCHAR2 datatype)
94 -- PARAMETERS:
95 -- p_validate_rec -- Input Key to be validated
96 -- x_generic_tab -- populated for existing cached records
97 -- x_index -- New index which can be used for x_flag = U
98 -- x_return_status -- S,E,U,W
99 -- x_flag -- U to use this index,D to indicate valid record
100 --
101 -- HISTORY : Bug 3821688
102 -- NOTE : For performance reasons, no debug calls are added
103 --========================================================================
104 PROCEDURE get_table_index
105 (p_validate_rec IN Generic_Cache_Rec_Typ,
106 p_generic_tab IN Generic_Cache_Tab_Typ,
107 x_index OUT NOCOPY NUMBER,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_flag OUT NOCOPY VARCHAR2
110 )IS
111
112 c_hash_base CONSTANT NUMBER := 1;
113 c_hash_size CONSTANT NUMBER := power(2, 25);
114
115 l_hash_string VARCHAR2(4000) := NULL;
116 l_index NUMBER;
117 l_hash_exists BOOLEAN := FALSE;
118
119 l_flag VARCHAR2(1);
120 l_generic_tab Generic_Cache_Tab_Typ;
121
122 BEGIN
123
124 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
125 l_hash_exists := FALSE;
126 l_generic_tab := p_generic_tab;
127 -- need to hash this index
128 -- Key (for hash) : param1+param2+param3
129 l_hash_string := p_validate_rec.input_param1||p_validate_rec.input_param2||p_validate_rec.input_param3||p_validate_rec.input_param4;
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 l_generic_tab.EXISTS(l_index) THEN
138 -- Check for all attributes match
139 -- Check for Input Stored(Table) vs Input given(Record)
140 -- There can be cases like
141 -- Param1 Param2 Key
142 -- AB XY ABXY
143 -- A BXY ABXY
144 -- Null ABXY ABXY
145 -- ABXY Null ABXY
146 IF (
147 ((l_generic_tab(l_index).input_param1 = p_validate_rec.input_param1)
148 OR
149 (l_generic_tab(l_index).input_param1 IS NULL AND
150 p_validate_rec.input_param1 IS NULL)
151 ) AND
152 ((l_generic_tab(l_index).input_param2 = p_validate_rec.input_param2)
153 OR
154 (l_generic_tab(l_index).input_param2 IS NULL AND
155 p_validate_rec.input_param2 IS NULL)
156 ) AND
157 ((l_generic_tab(l_index).input_param3 = p_validate_rec.input_param3)
158 OR
159 (l_generic_tab(l_index).input_param3 IS NULL AND
160 p_validate_rec.input_param3 IS NULL)
161 ) AND
162 ((l_generic_tab(l_index).input_param4 = p_validate_rec.input_param4)
163 OR
164 (l_generic_tab(l_index).input_param4 IS NULL AND
165 p_validate_rec.input_param4 IS NULL)
166 )
167 ) THEN
168 -- exact match found at this index
169 l_flag := 'D';
170 EXIT;
171 ELSE
172
173 -- Index exists but key does not match this table element
174 -- Bump l_index till key matches or table element does not exist
175 l_index := l_index + 1;
176 END IF;
177 ELSE
178 -- Index is not used in the table, can be used to create a new record
179 l_hash_exists := TRUE; -- to exit from the loop
180 l_flag := 'U';
181 END IF;
182 END LOOP;
183
184 x_index := l_index;
185 x_flag := l_flag;
186
187 END get_table_index;
188 --
189 -- End of Bug 3821688
190
191 --========================================================================
192 -- PROCEDURE : Validate_Org
193 --
194 -- COMMENT : Validates Organization_id and Organization_code against view
195 -- org_organization_definitions. If both values are
196 -- specified then only Org_Id is used
197 --
198 -- HISTORY : Bug# 1924574, hr_locations changes(8/15/01)
199 --========================================================================
200 PROCEDURE Validate_Org
201 (p_org_id IN OUT NOCOPY NUMBER,
202 p_org_code IN VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2) IS
204
205 -- BUG 4329611.
206 CURSOR check_org_id IS
207 SELECT mp.organization_id
208 FROM hr_organization_units hou,
209 mtl_parameters mp
210 WHERE hou.organization_id = mp.organization_id
211 AND mp.organization_id = p_org_id
212 AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
213
214 CURSOR check_org_code IS
215 SELECT mp.organization_id
216 FROM hr_organization_units hou, mtl_parameters mp
217 WHERE hou.organization_id = mp.organization_id
218 and mp.organization_code = p_org_code
219 AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
220
221 --
222 l_debug_on BOOLEAN;
223 --
224 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ORG';
225 --
226 l_failed BOOLEAN;
227 BEGIN
228 --
229 --
230 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
231 --
232 IF l_debug_on IS NULL
233 THEN
234 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
235 END IF;
236 --
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.push(l_module_name);
239 --
240 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
241 WSH_DEBUG_SV.log(l_module_name,'P_ORG_CODE',P_ORG_CODE);
242 END IF;
243 --
244 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
245 l_failed := FALSE;
246
247 IF p_org_id <> FND_API.G_MISS_NUM THEN
248 OPEN check_org_id;
249 FETCH check_org_id INTO p_org_id;
250 l_failed := check_org_id%NOTFOUND;
251 CLOSE check_org_id;
252 ELSIF p_org_code <> FND_API.G_MISS_CHAR THEN
253 OPEN check_org_code;
254 FETCH check_org_code INTO p_org_id;
255 l_failed := check_org_code%NOTFOUND;
256 CLOSE check_org_code;
257 END IF;
258 --
259 IF l_failed THEN
260 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
261 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ORG');
262 wsh_util_core.add_message(x_return_status,l_module_name);
263 END IF;
264 --
265 IF l_debug_on THEN
266 WSH_DEBUG_SV.log(l_module_name,'p_org_id',p_org_id);
267 WSH_DEBUG_SV.pop(l_module_name);
268 END IF;
269 --
270 EXCEPTION
271 WHEN OTHERS THEN
272 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
273 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_ORG');
274 IF check_org_id%ISOPEN THEN
275 CLOSE check_org_id;
276 END IF;
277 IF check_org_code%ISOPEN THEN
278 CLOSE check_org_code;
279 END IF;
280 --
281 IF l_debug_on THEN
282 WSH_DEBUG_SV.log(l_module_name,'ERROR:',SUBSTR(SQLERRM,1,200));
283 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
284 END IF;
285
286 END Validate_Org;
287
288
289 --========================================================================
290 -- PROCEDURE : Validate_Location
291 --
292 -- COMMENT : Validates Location_id and Location_code against view
293 -- hr_locations. If both values are specified then only
294 -- Location_id is used
295 --========================================================================
296 -- vms default p_Location_code as NULL in the spec.
297 PROCEDURE Validate_Location
298 (p_location_id IN OUT NOCOPY NUMBER,
299 p_location_code IN VARCHAR2 ,
300 x_return_status OUT NOCOPY VARCHAR2,
301 p_isWshLocation IN BOOLEAN DEFAULT FALSE,
302 p_caller IN VARCHAR2 DEFAULT NULL) IS
303
304
305 l_source_loc_type VARCHAR2(10);
306 l_return_status VARCHAR2(1);
307 l_loc_rec WSH_MAP_LOCATION_REGION_PKG.loc_rec_type;
308 l_sysdate DATE DEFAULT SYSDATE;
309 l_num_errors NUMBER;
310 l_num_warnings NUMBER;
311 l_location_id NUMBER;
312 l_location_id2 NUMBER;
313
314 CURSOR check_location IS
315 SELECT hrtl.location_id
316 FROM hr_locations_all_tl hrtl,
317 hr_locations_all hr
318 WHERE hrtl.location_code = p_location_code
319 AND hrtl.language = USERENV('LANG')
320 AND hrtl.location_id = hr.location_id
321 AND trunc(sysdate) <= nvl( hr.inactive_date, trunc(sysdate) ) ;
322
323
324
325 --
326 l_debug_on BOOLEAN;
327 --
328 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOCATION';
329 --
330 BEGIN
331 --
332 --
333 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
334 --
335 IF l_debug_on IS NULL
336 THEN
337 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
338 END IF;
339 --
340 IF l_debug_on THEN
341 WSH_DEBUG_SV.push(l_module_name);
342 --
343 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
344 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_CODE',P_LOCATION_CODE);
345 WSH_DEBUG_SV.log(l_module_name,'p_isWshLocation',p_isWshLocation);
346 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
347 END IF;
348 --
349 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
350 IF p_isWshLocation THEN
351 l_source_loc_type := 'WSH';
352 ELSE
353 l_source_loc_type := 'HR_HZ';
354 END IF;
355
356 IF (p_location_id = FND_API.G_MISS_NUM ) THEN
357 p_location_id := NULL;
358 END IF;
359
360 IF p_location_id IS NOT NULL THEN
361 IF l_debug_on THEN
362 WSH_DEBUG_SV.log(l_module_name,'Calling Transfer_Location');
363 END IF;
364
365 WSH_MAP_LOCATION_REGION_PKG.Transfer_Location (
366 p_source_type => l_source_loc_type,
367 p_source_location_id => p_location_id,
368 p_transfer_location => TRUE,
369 p_caller => p_caller,
370 p_online_region_mapping => FALSE,
371 x_loc_rec => l_loc_rec,
372 x_return_status => l_return_status);
373
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
376 END IF;
377
378 wsh_util_core.api_post_call(
379 p_return_status => l_return_status,
380 x_num_warnings => l_num_warnings,
381 x_num_errors => l_num_errors);
382 IF l_loc_rec.wsh_location_id IS NULL THEN
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385
386 IF TRUNC(l_sysdate) <= NVL(l_loc_rec.inactive_date,TRUNC(l_sysdate))
387 THEN
388 p_location_id := l_loc_rec.wsh_location_id;
389 ELSE
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392
393 IF l_debug_on THEN
394 WSH_DEBUG_SV.log(l_module_name,'inactive_date',
395 l_loc_rec.inactive_date);
396 WSH_DEBUG_SV.log(l_module_name,'l_sysdate',l_sysdate);
397 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
398 WSH_DEBUG_SV.pop(l_module_name);
399 END IF;
400 RETURN;
401 ELSIF NVL(p_location_code,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
402 AND p_location_id IS NULL
403 THEN
404 BEGIN
405 SELECT wsh_location_id
406 INTO l_location_id
407 FROM wsh_locations
408 WHERE location_code = p_location_code
409 AND TRUNC(l_sysdate)
410 <= NVL(inactive_date ,TRUNC(l_sysdate));
411 IF l_debug_on THEN
412 WSH_DEBUG_SV.log(l_module_name,'l_location_id',l_location_id);
413 END IF;
414 --
415 p_location_id := l_location_id;
416 IF l_debug_on THEN
417 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
418 WSH_DEBUG_SV.pop(l_module_name);
419 END IF;
420 RETURN;
421
422 EXCEPTION
423 WHEN TOO_MANY_ROWS THEN
424 RAISE FND_API.G_EXC_ERROR;
425 WHEN NO_DATA_FOUND THEN
426 OPEN check_location;
427 FETCH check_location
428 INTO l_location_id;
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.log(l_module_name,'cursor l_location_id',
431 l_location_id);
432 END IF;
433 IF check_location%NOTFOUND THEN
434 RAISE FND_API.G_EXC_ERROR;
435 ELSE
436 FETCH check_location
437 INTO l_location_id2;
438 IF l_location_id2 IS NOT NULL THEN
439 IF l_debug_on THEN
440 WSH_DEBUG_SV.log(l_module_name,'Too Many ROws',
441 l_location_id2);
442 END IF;
443 RAISE FND_API.G_EXC_ERROR;
444 END IF;
445 END IF;
446 END;
447
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.log(l_module_name,'Calling Transfer_Location');
450 END IF;
451
452 WSH_MAP_LOCATION_REGION_PKG.Transfer_Location (
453 p_source_type => 'HR',
454 p_source_location_id => l_location_id,
455 p_caller => p_caller,
456 p_transfer_location => TRUE,
457 p_online_region_mapping => FALSE,
458 x_loc_rec => l_loc_rec,
459 x_return_status => l_return_status);
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
462 END IF;
463
464 wsh_util_core.api_post_call(
465 p_return_status => l_return_status,
466 x_num_warnings => l_num_warnings,
467 x_num_errors => l_num_errors);
468
469 IF l_loc_rec.wsh_location_id IS NULL THEN
470 RAISE FND_API.G_EXC_ERROR;
471 END IF;
472 p_location_id := l_loc_rec.wsh_location_id;
473
474 ELSE
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.log(l_module_name,'Both parameters are null');
477 END IF;
478 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
479 --bug 2648157
480 END IF;
481
482 --
483 IF l_debug_on THEN
484 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
485 WSH_DEBUG_SV.pop(l_module_name);
486 END IF;
487 --
488 EXCEPTION
489
490 WHEN FND_API.G_EXC_ERROR THEN
491 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
492 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
493 wsh_util_core.add_message(x_return_status,l_module_name);
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION G_EXC_ERROR');
496 END IF;
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
499 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
500 wsh_util_core.add_message(x_return_status,l_module_name);
501 IF l_debug_on THEN
502 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION G_EXC_UNEXPECTED_ERROR');
503 END IF;
504 WHEN OTHERS THEN
505 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
506 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_LOCATION');
507 --
508 IF l_debug_on THEN
509 WSH_DEBUG_SV.log(l_module_name,'ERROR:',SUBSTR(SQLERRM,1,200));
510 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
511 END IF;
512
513 END Validate_Location;
514
515 --========================================================================
516 -- PROCEDURE : Validate_Lookup
517 --
518 -- COMMENT : Validates Lookup_code and Meaning against view fnd_lookups.
519 -- If both values are specified then only Lookup_code is used
520 --========================================================================
521
522 PROCEDURE Validate_Lookup
523 (p_lookup_type IN VARCHAR2,
524 p_lookup_code IN OUT NOCOPY VARCHAR2,
525 p_meaning IN VARCHAR2,
526 x_return_status OUT NOCOPY VARCHAR2) IS
527
528 -- Bug 3821688 Split Cursor
529 CURSOR check_lookup_code IS
530 SELECT lookup_code
531 FROM fnd_lookup_values_vl
532 WHERE lookup_code = p_lookup_code AND
533 lookup_type = p_lookup_type AND
534 nvl(start_date_active,sysdate)<=sysdate AND nvl(end_date_active,sysdate)>=sysdate AND
535 enabled_flag = 'Y';
536
537 CURSOR check_lookup_meaning IS
538 SELECT lookup_code
539 FROM fnd_lookup_values_vl
540 WHERE meaning = p_meaning AND
541 lookup_type = p_lookup_type AND
542 nvl(start_date_active,sysdate)<=sysdate AND nvl(end_date_active,sysdate)>=sysdate AND
543 enabled_flag = 'Y';
544
545 -- Bug 3821688
546 l_index NUMBER;
547 l_flag VARCHAR2(1);
548 l_return_status VARCHAR2(1);
549 l_cache_rec Generic_Cache_Rec_Typ;
550
551
552 --
553 l_debug_on BOOLEAN;
554 --
555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOOKUP';
556 --
557 BEGIN
558 --
559 --
560 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
561 --
562 IF l_debug_on IS NULL
563 THEN
564 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
565 END IF;
566 --
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.push(l_module_name);
569 --
570 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_TYPE',P_LOOKUP_TYPE);
571 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_CODE',P_LOOKUP_CODE);
572 WSH_DEBUG_SV.log(l_module_name,'P_MEANING',P_MEANING);
573 END IF;
574 --
575 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
576
577 IF (p_lookup_code IS NOT NULL) OR (p_meaning IS NOT NULL) THEN
578 -- Bug 3821688
579 l_cache_rec.input_param1 := p_lookup_code;
580 l_cache_rec.input_param2 := p_meaning;
581 l_cache_rec.input_param3 := p_lookup_type;
582 -- Always Call get_table_index to check if value exists in cache
583 -- If no record exists,then we can insert new record with the output index
584 get_table_index
585 (p_validate_rec => l_cache_rec,
586 p_generic_tab => g_lookup_tab,
587 x_index => l_index,
588 x_return_status => l_return_status,
589 x_flag => l_flag
590 );
591 -- l_flag = U means use this index value to insert record in table
592 IF l_flag = 'U' AND l_index IS NOT NULL THEN
593 IF p_lookup_code IS NOT NULL THEN
594 OPEN check_lookup_code;
595 FETCH check_lookup_code INTO l_cache_rec.output_param1;
596 IF (check_lookup_code%NOTFOUND) THEN
597 l_cache_rec.valid_flag := 'N';
598 ELSE
599 l_cache_rec.valid_flag := 'Y';
600 END IF;
601 CLOSE check_lookup_code;
602 ELSIF p_meaning IS NOT NULL THEN
603 OPEN check_lookup_meaning;
604 FETCH check_lookup_meaning INTO l_cache_rec.output_param1;
605 IF (check_lookup_meaning%NOTFOUND) THEN
606 l_cache_rec.valid_flag := 'N';
607 ELSE
608 l_cache_rec.valid_flag := 'Y';
609 END IF;
610 CLOSE check_lookup_meaning;
611 END IF;
612
613 g_lookup_tab(l_index) := l_cache_rec;
614 END IF;
615 -- Always check if input is valid or not
616 IF g_lookup_tab(l_index).valid_flag = 'N' THEN
617 --OTM R12 bug fix. Added the if condition for service level and
618 --mode of transport to display more specific message to user.
619 IF p_lookup_type = 'WSH_SERVICE_LEVELS' THEN
620 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_SERVICE_LEVEL');
621 ELSIF p_lookup_type = 'WSH_MODE_OF_TRANSPORT' THEN
622 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_MODE_OF_TRANSPORT');
623 ELSE
624 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOOKUP');
625 END IF;
626 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
627 wsh_util_core.add_message(x_return_status,l_module_name);
628 END IF;
629 -- Always Populate return variables
630 p_lookup_code := g_lookup_tab(l_index).output_param1;
631 -- End of Bug 3821688
632
633 END IF;
634 --
635 IF l_debug_on THEN
636 WSH_DEBUG_SV.log(l_module_name,'p_lookup_code',p_lookup_code);
637 WSH_DEBUG_SV.pop(l_module_name);
638 END IF;
639 --
640 END Validate_Lookup;
641
642 --========================================================================
643 -- PROCEDURE : Validate_Customer
644 --
645 -- COMMENT : Validates Customer_id/Customer_number against
646 -- hz_cust_accounts. If both values are specified then only
647 -- Customer_Id is used
648 --========================================================================
649
650 PROCEDURE Validate_Customer
651 (p_customer_id IN OUT NOCOPY NUMBER,
652 p_customer_number IN VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2) IS
654
655 CURSOR check_customer IS --Removal of TCA View Starts
656 SELECT cust_account_id /*customer_id */
657 FROM hz_cust_accounts
658 WHERE cust_account_id = p_customer_id AND
659 status = 'A' AND
660 NVL(p_customer_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
661 UNION
662 SELECT Cust_account_id/*customer_id */
663 FROM hz_cust_accounts
664 WHERE account_number /* customer number */ = p_customer_number AND
665 status = 'A' AND
666 NVL(p_customer_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM;
667 --Removal of TCA View Ends
668
669
670 --
671 l_debug_on BOOLEAN;
672 --
673 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CUSTOMER';
674 --
675 BEGIN
676 --
677 --
678 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
679 --
680 IF l_debug_on IS NULL
681 THEN
682 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
683 END IF;
684 --
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.push(l_module_name);
687 --
688 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
689 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_NUMBER',P_CUSTOMER_NUMBER);
690 END IF;
691 --
692 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
693
694 IF nvl(p_customer_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
695 OR nvl(p_customer_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
696 THEN
697
698 OPEN check_customer;
699 FETCH check_customer INTO p_customer_id;
700
701 IF (check_customer%NOTFOUND) THEN
702
703 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_CUSTOMER');
704 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
705 wsh_util_core.add_message(x_return_status,l_module_name);
706
707 END IF;
708
709 CLOSE check_customer;
710
711 END IF;
712 --
713 IF l_debug_on THEN
714 WSH_DEBUG_SV.log(l_module_name,'p_customer_id',p_customer_id);
715 WSH_DEBUG_SV.pop(l_module_name);
716 END IF;
717 --
718 END Validate_Customer;
719
720
721 --========================================================================
722 -- PROCEDURE : Validate_Contact
723 --
724 -- COMMENT : Validates Contact_id against hz_cust_account_roles
725 --========================================================================
726
727 PROCEDURE Validate_Contact
728 (p_contact_id IN OUT NOCOPY NUMBER,
729 x_return_status OUT NOCOPY VARCHAR2) IS
730
731 CURSOR check_contact IS --TCA View removal Starts
732 SELECT Cust_account_role_id /*contact id*/
733 FROM hz_cust_account_roles
734 WHERE cust_account_role_id = p_contact_id AND
735 p_contact_id IS NOT NULL; --TCA View removal Ends
736
737
738 --
739 l_debug_on BOOLEAN;
740 --
741 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CONTACT';
742 --
743 BEGIN
744 --
745 --
746 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
747 --
748 IF l_debug_on IS NULL
749 THEN
750 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
751 END IF;
752 --
753 IF l_debug_on THEN
754 WSH_DEBUG_SV.push(l_module_name);
755 --
756 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_ID',P_CONTACT_ID);
757 END IF;
758 --
759 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
760
761 IF (p_contact_id IS NOT NULL) THEN
762
763 OPEN check_contact;
764 FETCH check_contact INTO p_contact_id;
765
766 IF (check_contact%NOTFOUND) THEN
767
768 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_CONTACT');
769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
770 wsh_util_core.add_message(x_return_status,l_module_name);
771
772 END IF;
773
774 CLOSE check_contact;
775
776 END IF;
777 --
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.log(l_module_name,'p_contact_id',p_contact_id);
780 WSH_DEBUG_SV.pop(l_module_name);
781 END IF;
782 --
783 END Validate_Contact;
784
785
786 --========================================================================
787 --========================================================================
788 -- PROCEDURE : Validate_Quantity
789 --
790 -- COMMENT : Validates if quantity is non-negative and an integer.
791 --========================================================================
792
793 PROCEDURE Validate_Quantity
794 (p_quantity IN NUMBER ,
795 x_return_status OUT NOCOPY VARCHAR2 ) IS
796
797 --
798 l_debug_on BOOLEAN;
799 --
800 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_QUANTITY';
801 --
802 BEGIN
803 --
804 --
805 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
806 --
807 IF l_debug_on IS NULL
808 THEN
809 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
810 END IF;
811 --
812 IF l_debug_on THEN
813 WSH_DEBUG_SV.push(l_module_name);
814 --
815 WSH_DEBUG_SV.log(l_module_name,'P_QUANTITY',P_QUANTITY);
816 END IF;
817 --
818 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
819
820 IF (p_quantity IS NOT NULL) THEN
821
822 IF (p_quantity < 0) OR (p_quantity <> trunc(p_quantity)) THEN
823
824 FND_MESSAGE.SET_NAME('WSH','WSH_QUANTITY_NOT_WHOLE');
825 FND_MESSAGE.SET_TOKEN('QTY',p_quantity);
826 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
827 wsh_util_core.add_message(x_return_status,l_module_name);
828
829 END IF;
830
831 END IF;
832 --
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.pop(l_module_name);
835 END IF;
836 --
837 END Validate_Quantity;
838
839 --========================================================================
840 -- PROCEDURE : Validate_Negative
841 --
842 -- COMMENT : Validates if value is non-negative
843 --========================================================================
844 /*
845 -- Overloaded the procedure with extra parameter
846 PROCEDURE Validate_Negative
847 (p_value IN NUMBER,
848 x_return_status OUT NOCOPY VARCHAR2) IS
849
850 --
851 l_debug_on BOOLEAN;
852 --
853 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_NEGATIVE';
854 --
855 BEGIN
856 --
857 --
858 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
859 --
860 IF l_debug_on IS NULL
861 THEN
862 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
863 END IF;
864 --
865 IF l_debug_on THEN
866 WSH_DEBUG_SV.push(l_module_name);
867 --
868 WSH_DEBUG_SV.log(l_module_name,'P_VALUE',P_VALUE);
869 END IF;
870 --
871 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
872
873 IF (p_value IS NOT NULL) THEN
874
875 IF (p_value < 0) THEN
876
877 FND_MESSAGE.SET_NAME('WSH','WSH_VALUE_NEGATIVE');
878 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
879 wsh_util_core.add_message(x_return_status,l_module_name);
880
881 END IF;
882
883 END IF;
884 --
885 IF l_debug_on THEN
886 WSH_DEBUG_SV.pop(l_module_name);
887 END IF;
888 --
889 END Validate_Negative;
890 */
891
892 --overloaded procedure added for Bug # 3266333
893 --========================================================================
894 -- PROCEDURE : Validate_Negative
895 --
896 -- COMMENT : Validates if value is non-negative and shows a message
897 -- along with the attribute/field name which has a negative value.
898 --========================================================================
899
900 PROCEDURE Validate_Negative
901 (p_value IN NUMBER,
902 p_field_name IN VARCHAR2 DEFAULT NULL,
903 x_return_status OUT NOCOPY VARCHAR2) IS
904
905 --
906 l_debug_on BOOLEAN;
907 --
908 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_NEGATIVE';
909 --
910 BEGIN
911 --
912 --
913 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
914 --
915 IF l_debug_on IS NULL
916 THEN
917 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
918 END IF;
919 --
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.push(l_module_name);
922 --
923 WSH_DEBUG_SV.log(l_module_name,'P_VALUE',P_VALUE);
924 END IF;
925 --
926 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
927
928 IF (p_value IS NOT NULL) THEN
929
930 IF (p_value < 0) THEN
931
932 FND_MESSAGE.SET_NAME('WSH','WSH_VALUE_NEGATIVE');
933 FND_MESSAGE.SET_TOKEN('FIELD_NAME',p_field_name);
934 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
935 wsh_util_core.add_message(x_return_status,l_module_name);
936
937 END IF;
938
939 END IF;
940 --
941 IF l_debug_on THEN
942 WSH_DEBUG_SV.pop(l_module_name);
943 END IF;
944 --
945 END Validate_Negative;
946
947
948
949 --========================================================================
950 -- PROCEDURE : Validate_Currency
951 --
952 -- COMMENT : Validates Currency_code and Currency_Name against
953 -- table fnd_currencies_vl. If both values are specified then
954 -- only Currency_code is used. p_amount if specified is
955 -- checked for correct precision
956 -- If p_otm_enabled is 'Y', rounds p_amount using FND precision
957 -- for the input currency
958 --========================================================================
959
960 PROCEDURE Validate_Currency
961 (p_currency_code IN OUT NOCOPY VARCHAR2,
962 p_currency_name IN VARCHAR2,
963 p_amount IN NUMBER,
964 p_otm_enabled IN VARCHAR2 DEFAULT NULL, -- OTM R12
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_adjusted_amount OUT NOCOPY NUMBER) IS -- OTM R12
967
968 CURSOR currency_cursor(c_currency_code IN VARCHAR2, c_currency_name IN VARCHAR2) IS
969 SELECT currency_code,
970 NVL(precision,0),
971 DECODE(INSTR(TO_CHAR(NVL(p_amount,0)),'.'),0,0,
972 LENGTH(TO_CHAR(NVL(p_amount,0)))-
973 INSTR(TO_CHAR(NVL(p_amount,0)),'.'))
974 FROM fnd_currencies_vl
975 WHERE enabled_flag = 'Y'
976 AND name = decode( c_currency_code, null, c_currency_name, name)
977 AND currency_code = nvl( c_currency_code, currency_code)
978 AND trunc(sysdate) between nvl( start_date_active, trunc(sysdate) )
979 and nvl( end_date_active, trunc(sysdate) );
980
981 l_precision number;
982 l_in_precision number;
983 error_code VARCHAR2(50);
984
985 l_invalid_currency EXCEPTION; -- OTM R12
986
987 --
988 l_debug_on BOOLEAN;
989 --
990 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CURRENCY';
991 --
992 BEGIN
993 --
994 --
995 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
996 --
997 IF l_debug_on IS NULL
998 THEN
999 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1000 END IF;
1001 --
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.push(l_module_name);
1004 --
1005 WSH_DEBUG_SV.log(l_module_name,'P_CURRENCY_CODE',P_CURRENCY_CODE);
1006 WSH_DEBUG_SV.log(l_module_name,'P_CURRENCY_NAME',P_CURRENCY_NAME);
1007 WSH_DEBUG_SV.log(l_module_name,'P_AMOUNT',P_AMOUNT);
1008 WSH_DEBUG_SV.log(l_module_name,'P_OTM_ENABLED',P_OTM_ENABLED); -- OTM R12
1009 END IF;
1010 --
1011 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1012
1013 IF nvl(p_currency_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1014 OR nvl(p_currency_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1015 THEN
1016 -- OTM R12
1017 OPEN currency_cursor(p_currency_code,p_currency_name);
1018 -- OTM R12
1019 FETCH currency_cursor INTO p_currency_code, l_precision, l_in_precision;
1020
1021 IF (currency_cursor%NOTFOUND) THEN
1022
1023 IF p_currency_code IS NOT NULL THEN
1024 error_code := 'CURR-Invalid code';
1025 FND_MESSAGE.SET_NAME('FND', error_code);
1026 FND_MESSAGE.SET_TOKEN('CODE',p_currency_code);
1027 ELSE
1028 error_code := 'CURR-Invalid currency value';
1029 FND_MESSAGE.set_name('FND', error_code);
1030 END IF;
1031
1032 -- OTM R12
1033 CLOSE currency_cursor;
1034 RAISE l_invalid_currency;
1035
1036 END IF;
1037
1038 CLOSE currency_cursor;
1039
1040 IF l_debug_on THEN
1041 WSH_DEBUG_SV.log(l_module_name,'l_in_precision',l_in_precision);
1042 WSH_DEBUG_SV.log(l_module_name,'l_precision',l_precision);
1043 END IF;
1044
1045 x_adjusted_amount := p_amount;
1046
1047 IF l_in_precision > l_precision THEN
1048 --IF WSH_UTIL_CORE.GC3_IS_INSTALLED = 'Y' THEN
1049 -- OTM R12
1050 IF p_otm_enabled = 'Y' THEN
1051 -- truncate the amount to the precision specified in setup
1052 x_adjusted_amount := ROUND(p_amount,l_precision);
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.log(l_module_name,'Adjusted amount:',x_adjusted_amount);
1055 END IF;
1056 ELSE
1057 -- existing code
1058 error_code := 'CURR-Precision';
1059 FND_MESSAGE.SET_NAME('FND', error_code);
1060 FND_MESSAGE.SET_TOKEN('PRECISON',TO_CHAR(l_precision));
1061 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1062 wsh_util_core.add_message(x_return_status,l_module_name);
1063 END IF;
1064 END IF;
1065
1066 -- OTM R12
1067 ELSE
1068 RAISE l_invalid_currency;
1069 END IF;
1070 --
1071 IF l_debug_on THEN
1072 -- OTM R12
1073 WSH_DEBUG_SV.log(l_module_name,'x_adjusted_amount ',x_adjusted_amount);
1074 WSH_DEBUG_SV.pop(l_module_name);
1075 END IF;
1076 --
1077 EXCEPTION
1078 -- OTM R12
1079 WHEN l_invalid_currency THEN
1080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1081 WSH_UTIL_CORE.add_message(x_return_status,l_module_name);
1082 IF l_debug_on THEN
1083 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid or NULL currency exception ');
1084 WSH_DEBUG_SV.pop(l_module_name);
1085 END IF;
1086 WHEN OTHERS THEN
1087 IF currency_cursor%ISOPEN THEN
1088 CLOSE currency_cursor;
1089 END IF;
1090 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1091 WSH_UTIL_CORE.default_handler('WSH_UTIL_VALIDATE.Validate_Currency');
1092 IF l_debug_on THEN
1093 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1094 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1095 END IF;
1096
1097 END Validate_Currency;
1098
1099 --========================================================================
1100 -- PROCEDURE : Validate_Uom
1101 --
1102 -- COMMENT : Validates UOM_Code and UOM Description against table
1103 -- mtl_units_of_measure. If both values are specified then
1104 -- only UOM_Code is used. Type and Organization are required
1105 -- p_type = 'WEIGHT', 'VOLUME'
1106 --========================================================================
1107
1108 PROCEDURE Validate_Uom
1109 (p_type IN VARCHAR2,
1110 p_organization_id IN NUMBER,
1111 p_uom_code IN OUT NOCOPY VARCHAR2,
1112 p_uom_desc IN VARCHAR2,
1113 x_return_status OUT NOCOPY VARCHAR2) IS
1114
1115 CURSOR get_classes IS
1116 SELECT weight_uom_class, volume_uom_class
1117 FROM wsh_shipping_parameters
1118 WHERE organization_id = p_organization_id;
1119
1120 -- Bug 3821688 Split Cursor
1121 CURSOR check_uom_code (l_class VARCHAR2) IS
1122 SELECT uom_code
1123 FROM mtl_units_of_measure
1124 WHERE uom_code = p_uom_code AND
1125 uom_class = NVL(l_class, uom_class) AND
1126 nvl(disable_date, sysdate) >= sysdate;
1127
1128 CURSOR check_uom_desc (l_class VARCHAR2) IS
1129 SELECT uom_code
1130 FROM mtl_units_of_measure
1131 WHERE unit_of_measure = p_uom_desc AND
1132 uom_class = NVL(l_class, uom_class) AND
1133 nvl(disable_date, sysdate) >= sysdate;
1134
1135 l_weight_uom_class VARCHAR2(10);
1136 l_volume_uom_class VARCHAR2(10);
1137 l_input_class VARCHAR2(10);
1138
1139 -- Bug 3821688
1140 -- 1st cursor
1141 l_index NUMBER;
1142 l_flag VARCHAR2(1);
1143 l_return_status VARCHAR2(1);
1144 l_cache_rec Generic_Cache_Rec_Typ;
1145 -- 2nd cursor
1146 l_uom_index NUMBER;
1147 l_uom_flag VARCHAR2(1);
1148 l_uom_return_status VARCHAR2(1);
1149 l_cache_uom_rec Generic_Cache_Rec_Typ;
1150
1151 --
1152 l_debug_on BOOLEAN;
1153 --
1154 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_UOM';
1155 --
1156 BEGIN
1157 --
1158 --
1159 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1160 --
1161 IF l_debug_on IS NULL
1162 THEN
1163 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1164 END IF;
1165 --
1166 IF l_debug_on THEN
1167 WSH_DEBUG_SV.push(l_module_name);
1168 --
1169 WSH_DEBUG_SV.log(l_module_name,'P_TYPE',P_TYPE);
1170 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1171 WSH_DEBUG_SV.log(l_module_name,'P_UOM_CODE',P_UOM_CODE);
1172 WSH_DEBUG_SV.log(l_module_name,'P_UOM_DESC',P_UOM_DESC);
1173 END IF;
1174 --
1175 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1176
1177 IF nvl(p_uom_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
1178 AND nvl(p_uom_desc, fnd_api.g_miss_char) = fnd_api.g_miss_char
1179 THEN
1180 RETURN;
1181 END IF;
1182 IF (p_uom_code IS NOT NULL) OR (p_uom_desc IS NOT NULL) THEN
1183 IF (p_organization_id IS NOT NULL) THEN
1184 -- Bug 3821688
1185 l_cache_rec.input_param1 := to_char(p_organization_id);
1186 -- Always Call get_table_index to check if value exists in cache
1187 -- If no record exists,then we can insert new record with the output index
1188 get_table_index
1189 (p_validate_rec => l_cache_rec,
1190 p_generic_tab => g_org_uom_class_tab,
1191 x_index => l_index,
1192 x_return_status => l_return_status,
1193 x_flag => l_flag
1194 );
1195 -- l_flag = U means use this index value to insert record in table
1196 IF l_flag = 'U' AND l_index IS NOT NULL THEN
1197 OPEN get_classes;
1198 -- Fetching into param2 and param3 because corresponsing param1 is used
1199 -- for organization id(this will keep logic clear)
1200 FETCH get_classes INTO l_cache_rec.output_param2,l_cache_rec.output_param3;
1201 IF (get_classes%NOTFOUND) THEN
1202 l_cache_rec.valid_flag := 'N';
1203 ELSE
1204 l_cache_rec.valid_flag := 'Y';
1205 END IF;
1206 CLOSE get_classes;
1207 g_org_uom_class_tab(l_index) := l_cache_rec;
1208 END IF;
1209 -- Always check if input is valid or not
1210 IF g_org_uom_class_tab(l_index).valid_flag = 'N' THEN
1211 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ORG');
1212 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1213 wsh_util_core.add_message(x_return_status,l_module_name);
1214 RETURN;
1215 END IF;
1216 -- Always Populate return variables
1217 l_weight_uom_class := g_org_uom_class_tab(l_index).output_param2;
1218 l_volume_uom_class := g_org_uom_class_tab(l_index).output_param3;
1219 -- End of Bug 3821688
1220 END IF;
1221
1222 -- Bug 3821688 for 2nd Cursor
1223 IF p_type IN ('WEIGHT','VOLUME') THEN
1224 IF p_type = 'WEIGHT' THEN
1225 l_input_class := l_weight_uom_class;
1226 ELSIF p_type = 'VOLUME' THEN
1227 l_input_class := l_volume_uom_class;
1228 END IF;
1229 -- Bug 3821688
1230 l_cache_uom_rec.input_param1 := p_type;
1231 l_cache_uom_rec.input_param2 := p_uom_code;
1232 l_cache_uom_rec.input_param3 := p_uom_desc;
1233 l_cache_uom_rec.input_param4 := l_input_class;
1234
1235 -- Always Call get_table_index to check if value exists in cache
1236 -- If no record exists,then we can insert new record with the output index
1237 get_table_index
1238 (p_validate_rec => l_cache_uom_rec,
1239 p_generic_tab => g_uom_tab,
1240 x_index => l_uom_index,
1241 x_return_status => l_uom_return_status,
1242 x_flag => l_uom_flag
1243 );
1244 -- flag = U means use this index value to insert record in table
1245 IF l_uom_flag = 'U' AND l_uom_index IS NOT NULL THEN
1246 IF NVL(p_uom_code, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
1247 OPEN check_uom_code (l_input_class);
1248 FETCH check_uom_code INTO l_cache_uom_rec.output_param2;
1249 IF (check_uom_code%NOTFOUND) THEN
1250 l_cache_uom_rec.valid_flag := 'N';
1251 ELSE
1252 l_cache_uom_rec.valid_flag := 'Y';
1253 END IF;
1254 CLOSE check_uom_code;
1255 ELSIF NVL(p_uom_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
1256 OPEN check_uom_desc (l_input_class);
1257 FETCH check_uom_desc INTO l_cache_uom_rec.output_param2;
1258 IF (check_uom_desc%NOTFOUND) THEN
1259 l_cache_uom_rec.valid_flag := 'N';
1260 ELSE
1261 l_cache_uom_rec.valid_flag := 'Y';
1262 END IF;
1263 CLOSE check_uom_desc;
1264
1265 END IF;
1266 g_uom_tab(l_uom_index) := l_cache_uom_rec;
1267 END IF;
1268 -- Always check if input is valid or not
1269 IF g_uom_tab(l_uom_index).valid_flag = 'N' THEN
1270 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_UOM');
1271 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1272 wsh_util_core.add_message(x_return_status,l_module_name);
1273 END IF;
1274 -- Always Populate return variables
1275 p_uom_code := g_uom_tab(l_uom_index).output_param2;
1276
1277 ELSE
1278 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1279 --
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.pop(l_module_name,x_return_status);
1282 END IF;
1283 --
1284 RETURN;
1285 END IF;
1286 -- End of Bug 3821688 for 2nd Cursor
1287 END IF;
1288 --
1289 IF l_debug_on THEN
1290 WSH_DEBUG_SV.log(l_module_name,'p_uom_code',p_uom_code);
1291 WSH_DEBUG_SV.pop(l_module_name);
1292 END IF;
1293 --
1294 END Validate_Uom;
1295
1296 --========================================================================
1297 -- PROCEDURE : Validate_User
1298 --
1299 -- COMMENT : Validates User_id and User_name against table fnd_user
1300 -- If both values are specified then only User_id is used
1301 --========================================================================
1302
1303 PROCEDURE Validate_User
1304 (p_user_id IN OUT NOCOPY NUMBER,
1305 p_user_name IN VARCHAR2,
1306 x_return_status OUT NOCOPY VARCHAR2) IS
1307
1308 CURSOR check_user IS
1309 SELECT user_id
1310 FROM fnd_user
1311 WHERE p_user_id IS NOT NULL AND
1312 user_id = p_user_id AND
1313 trunc(sysdate) between nvl( start_date, trunc(sysdate) )
1314 and nvl( end_date, trunc(sysdate) )
1315 UNION ALL
1316 SELECT user_id
1317 FROM fnd_user
1318 WHERE p_user_id IS NULL AND
1319 user_name = p_user_name AND
1320 trunc(sysdate) between nvl( start_date, trunc(sysdate) )
1321 and nvl( end_date, trunc(sysdate) );
1322
1323 --
1324 l_debug_on BOOLEAN;
1325 --
1326 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_USER';
1327 --
1328 BEGIN
1329 --
1330 --
1331 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1332 --
1333 IF l_debug_on IS NULL
1334 THEN
1335 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1336 END IF;
1337 --
1338 IF l_debug_on THEN
1339 WSH_DEBUG_SV.push(l_module_name);
1340 --
1341 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
1342 WSH_DEBUG_SV.log(l_module_name,'P_USER_NAME',P_USER_NAME);
1343 END IF;
1344 --
1345 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1346
1347 IF (p_user_id IS NOT NULL) OR (p_user_name IS NOT NULL) THEN
1348
1349 OPEN check_user;
1350 FETCH check_user INTO p_user_id;
1351
1352 IF (check_user%NOTFOUND) THEN
1353
1354 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_USER');
1355 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1356 wsh_util_core.add_message(x_return_status,l_module_name);
1357
1358 END IF;
1359
1360 CLOSE check_user;
1361
1362 END IF;
1363 --
1364 IF l_debug_on THEN
1365 WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
1366 WSH_DEBUG_SV.pop(l_module_name);
1367 END IF;
1368 --
1369 END Validate_User;
1370
1371 --========================================================================
1372 -- PROCEDURE : Validate_Ship_Method
1373 --
1374 -- COMMENT : Validates Ship_Method_Code/Name against fnd_lookup_values_vl.
1375 -- If both values are specified then only Ship_Method_Code is used
1376 --========================================================================
1377
1378 PROCEDURE Validate_Ship_Method
1379 (p_ship_method_code IN OUT NOCOPY VARCHAR2,
1380 p_ship_method_name IN OUT NOCOPY VARCHAR2,
1381 x_return_status OUT NOCOPY VARCHAR2) IS
1382
1383 -- Bug 3821688 Split Cursor
1384 CURSOR check_ship_method_code IS
1385 SELECT lookup_code, meaning
1386 FROM fnd_lookup_values_vl
1387 WHERE lookup_code = p_ship_method_code AND
1388 lookup_type = 'SHIP_METHOD' AND
1389 view_application_id = 3;
1390
1391 CURSOR check_ship_method_name IS
1392 SELECT lookup_code, meaning
1393 FROM fnd_lookup_values_vl
1394 WHERE meaning = p_ship_method_name AND
1395 lookup_type = 'SHIP_METHOD' AND
1396 view_application_id = 3;
1397
1398 -- Bug 3821688
1399 l_index NUMBER;
1400 l_flag VARCHAR2(1);
1401 l_return_status VARCHAR2(1);
1402 l_cache_rec Generic_Cache_Rec_Typ;
1403
1404 --
1405 l_debug_on BOOLEAN;
1406 --
1407 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SHIP_METHOD';
1408 --
1409 BEGIN
1410 --
1411 --
1412 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1413 --
1414 IF l_debug_on IS NULL
1415 THEN
1416 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1417 END IF;
1418 --
1419 IF l_debug_on THEN
1420 WSH_DEBUG_SV.push(l_module_name);
1421 --
1422 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
1423 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_NAME',P_SHIP_METHOD_NAME);
1424 END IF;
1425 --
1426 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1427 --
1428 IF nvl(p_ship_method_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1429 OR nvl(p_ship_method_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1430 THEN
1431 --
1432
1433 -- Bug 3821688
1434 l_cache_rec.input_param1 := p_ship_method_code;
1435 l_cache_rec.input_param2 := p_ship_method_name;
1436
1437 -- Always Call get_table_index to check if value exists in cache
1438 -- If no record exists,then we can insert new record with the output index
1439 get_table_index
1440 (p_validate_rec => l_cache_rec,
1441 p_generic_tab => g_ship_method_tab,
1442 x_index => l_index,
1443 x_return_status => l_return_status,
1444 x_flag => l_flag
1445 );
1446 -- l_flag = U means use this index value to insert record in table
1447 -- l_flag = D means valid record found
1448 IF l_flag = 'U' AND l_index IS NOT NULL THEN
1449 IF p_ship_method_code <> fnd_api.g_miss_char THEN
1450 OPEN check_ship_method_code;
1451 FETCH check_ship_method_code
1452 INTO l_cache_rec.output_param1,l_cache_rec.output_param2;
1453 --
1454 IF (check_ship_method_code%NOTFOUND) THEN
1455 l_cache_rec.valid_flag := 'N';
1456 ELSE
1457 l_cache_rec.valid_flag := 'Y';
1458 END IF;
1459 --
1460 CLOSE check_ship_method_code;
1461 ELSIF p_ship_method_name <> fnd_api.g_miss_char THEN
1462 OPEN check_ship_method_name;
1463 FETCH check_ship_method_name
1464 INTO l_cache_rec.output_param1,l_cache_rec.output_param2;
1465 --
1466 IF (check_ship_method_name%NOTFOUND) THEN
1467 l_cache_rec.valid_flag := 'N';
1468 ELSE
1469 l_cache_rec.valid_flag := 'Y';
1470 END IF;
1471 --
1472 CLOSE check_ship_method_name;
1473 END IF;
1474 g_ship_method_tab(l_index) := l_cache_rec;
1475
1476 END IF;
1477
1478 -- Always check if input is valid or not
1479 IF g_ship_method_tab(l_index).valid_flag = 'N' THEN
1480 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_SHIP_METHOD');
1481 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1482 wsh_util_core.add_message(x_return_status,l_module_name);
1483 END IF;
1484
1485 -- Always Populate return variables
1486 p_ship_method_code := g_ship_method_tab(l_index).output_param1;
1487 p_ship_method_name := g_ship_method_tab(l_index).output_param2;
1488 -- End of Bug 3821688
1489 --
1490 END IF;
1491 --
1492 IF l_debug_on THEN
1493 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_code',p_ship_method_code);
1494 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_name',p_ship_method_name);
1495 WSH_DEBUG_SV.pop(l_module_name);
1496 END IF;
1497 --
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1501 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.Validate_Ship_Method');
1502 IF l_debug_on THEN
1503 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1504 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1505 END IF;
1506 END Validate_Ship_Method;
1507
1508 --========================================================================
1509 -- PROCEDURE : Validate_Freight_Terms
1510 --
1511 -- COMMENT : Validates Freight_Terms_Code by calling the
1512 -- Validate_Lookup_Code procedure.
1513 --========================================================================
1514
1515 PROCEDURE Validate_Freight_Terms
1516 (p_freight_terms_code IN OUT NOCOPY VARCHAR2 ,
1517 p_freight_terms_name IN VARCHAR2,
1518 x_return_status OUT NOCOPY VARCHAR2 ) IS
1519
1520 --
1521 l_debug_on BOOLEAN;
1522 --
1523 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FREIGHT_TERMS';
1524 --
1525 BEGIN
1526 --
1527 --
1528 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1529 --
1530 IF l_debug_on IS NULL
1531 THEN
1532 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1533 END IF;
1534 --
1535 IF l_debug_on THEN
1536 WSH_DEBUG_SV.push(l_module_name);
1537 --
1538 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_TERMS_CODE',P_FREIGHT_TERMS_CODE);
1539 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_TERMS_NAME',P_FREIGHT_TERMS_NAME);
1540 END IF;
1541 --
1542 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1543
1544
1545 IF nvl(p_freight_terms_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1546 OR nvl(p_freight_terms_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1547 THEN
1548 -- OTM R12, glog project changes
1549 validate_lookup_UPPER(
1550 p_lookup_type => 'FREIGHT_TERMS',
1551 p_lookup_code => p_freight_terms_code,
1552 p_meaning => p_freight_terms_name,
1553 x_return_status => x_return_status);
1554 END IF;
1555
1556 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1557
1558 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_FREIGHT_TERMS');
1559 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1560 wsh_util_core.add_message(x_return_status,l_module_name);
1561
1562 END IF;
1563 --
1564 IF l_debug_on THEN
1565 WSH_DEBUG_SV.pop(l_module_name);
1566 END IF;
1567 --
1568 END Validate_Freight_Terms;
1569
1570 --========================================================================
1571 -- PROCEDURE : Validate_FOB
1572 --
1573 -- COMMENT : Validates FOB_Code by calling Validate_Lookup_Code
1574 --========================================================================
1575
1576 PROCEDURE Validate_FOB
1577 (p_fob_code IN OUT NOCOPY VARCHAR2,
1578 p_fob_name IN VARCHAR2,
1579 x_return_status OUT NOCOPY VARCHAR2 ) IS
1580
1581 --
1582 l_debug_on BOOLEAN;
1583 --
1584 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FOB';
1585 --
1586 BEGIN
1587 --
1588 --
1589 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1590 --
1591 IF l_debug_on IS NULL
1592 THEN
1593 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1594 END IF;
1595 --
1596 IF l_debug_on THEN
1597 WSH_DEBUG_SV.push(l_module_name);
1598 --
1599 WSH_DEBUG_SV.log(l_module_name,'P_FOB_CODE',P_FOB_CODE);
1600 WSH_DEBUG_SV.log(l_module_name,'P_FOB_NAME',P_FOB_NAME);
1601 END IF;
1602 --
1603 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1604
1605 IF nvl(p_fob_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1606 OR nvl(p_fob_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1607 THEN
1608 validate_lookup(
1609 p_lookup_type => 'FOB',
1610 p_lookup_code => p_fob_code,
1611 p_meaning => p_fob_name,
1612 x_return_status => x_return_status);
1613 END IF;
1614
1615 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1616
1617 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_FOB');
1618 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1619 wsh_util_core.add_message(x_return_status,l_module_name);
1620
1621 END IF;
1622 --
1623 IF l_debug_on THEN
1624 WSH_DEBUG_SV.pop(l_module_name);
1625 END IF;
1626 --
1627 END Validate_FOB;
1628
1629 --========================================================================
1630 -- PROCEDURE : Validate_Flexfields
1631 --
1632 -- COMMENT : Validates Flexfield id and concatenated segments
1633 -- Logic used :
1634 -- if id is not null
1635 -- validate id
1636 -- else
1637 -- if id is null
1638 -- begin
1639 -- get delimeter
1640 -- concatenate segments
1641 -- validate concatenated segments
1642 -- exception
1643 -- handle exception
1644 -- end
1645 -- if item is not null
1646 -- validate item
1647 -- end if;
1648 -- end if;
1649 -- end if;
1650 --========================================================================
1651
1652 PROCEDURE Validate_Flexfields(
1653 p_id IN OUT NOCOPY NUMBER,
1654 p_concat_segs IN VARCHAR2,
1655 p_app_short_name IN VARCHAR2,
1656 p_key_flx_code IN VARCHAR2,
1657 p_struct_number IN NUMBER,
1658 p_org_id IN NUMBER,
1659 p_seg_array IN FND_FLEX_EXT.SegmentArray,
1660 p_val_or_ids IN VARCHAR2,
1661 p_wh_clause IN VARCHAR2 DEFAULT NULL,
1662 x_flag OUT NOCOPY BOOLEAN) IS
1663
1664 valid_flag boolean := NULL;
1665
1666 delimiter varchar2(1);
1667 concat_string varchar2(2000);
1668 error_flag boolean := NULL;
1669
1670 delimiter_null exception;
1671 wrong_combination exception;
1672
1673 ffield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1674 fstruct FND_FLEX_KEY_API.STRUCTURE_TYPE;
1675 slist FND_FLEX_KEY_API.SEGMENT_LIST;
1676 fsegment FND_FLEX_KEY_API.SEGMENT_TYPE;
1677
1678 nsegs NUMBER;
1679 charcol VARCHAR2(70);
1680 numbcol NUMBER;
1681
1682 --
1683 l_debug_on BOOLEAN;
1684 --
1685 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FLEXFIELDS';
1686 --
1687 BEGIN
1688 --
1689 --
1690 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1691 --
1692 IF l_debug_on IS NULL
1693 THEN
1694 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1695 END IF;
1696 --
1697 IF l_debug_on THEN
1698 WSH_DEBUG_SV.push(l_module_name);
1699 --
1700 WSH_DEBUG_SV.log(l_module_name,'P_ID',P_ID);
1701 WSH_DEBUG_SV.log(l_module_name,'P_CONCAT_SEGS',P_CONCAT_SEGS);
1702 WSH_DEBUG_SV.log(l_module_name,'P_APP_SHORT_NAME',P_APP_SHORT_NAME);
1703 WSH_DEBUG_SV.log(l_module_name,'P_KEY_FLX_CODE',P_KEY_FLX_CODE);
1704 WSH_DEBUG_SV.log(l_module_name,'P_STRUCT_NUMBER',P_STRUCT_NUMBER);
1705 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
1706 WSH_DEBUG_SV.log(l_module_name,'P_VAL_OR_IDS',P_VAL_OR_IDS);
1707 WSH_DEBUG_SV.log(l_module_name,'P_WH_CLAUSE',P_WH_CLAUSE);
1708 END IF;
1709 --
1710 --IF p_id IS NOT NULL THEN
1711 IF NVL(p_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1712
1713 x_flag := fnd_flex_keyval.validate_ccid(
1714 appl_short_name => p_app_short_name,
1715 key_flex_code => p_key_flx_code,
1716 structure_number=> p_struct_number,
1717 combination_id => p_id,
1718 data_set => p_org_id);
1719
1720 p_id := fnd_flex_keyval.combination_id;
1721 IF l_debug_on THEN
1722 WSH_DEBUG_SV.log(l_module_name,'x_flag',x_flag);
1723 WSH_DEBUG_SV.log(l_module_name,'p_id',p_id);
1724 END IF;
1725 ELSIF p_id IS NULL OR p_id = FND_API.G_MISS_NUM THEN
1726 If l_debug_on THEN
1727 wsh_debug_sv.logmsg(l_module_name, 'Calling set session mode');
1728 END IF;
1729
1730 fnd_flex_key_api.set_session_mode(session_mode =>'seed_data');
1731
1732 If l_debug_on THEN
1733 wsh_debug_sv.logmsg(l_module_name, 'Calling find flexfield');
1734 END IF;
1735
1736
1737 ffield := fnd_flex_key_api.find_flexfield(p_app_short_name, p_key_flx_code);
1738 fstruct := fnd_flex_key_api.find_structure(ffield, p_struct_number);
1739 fnd_flex_key_api.get_segments(ffield, fstruct, TRUE, nsegs, slist);
1740
1741 delimiter := fnd_flex_ext.get_delimiter(
1742 application_short_name => p_app_short_name,
1743 key_flex_code => p_key_flx_code,
1744 structure_number => p_struct_number);
1745
1746 If l_debug_on THEN
1747 wsh_debug_sv.log(l_module_name, 'delimiter', delimiter);
1748 wsh_debug_sv.log(l_module_name, 'nsegs', nsegs);
1749 END IF;
1750
1751 IF (delimiter IS NOT NULL) THEN
1752 concat_string := '';
1753
1754 FOR i IN 1..nsegs LOOP
1755 fsegment := fnd_flex_key_api.find_segment(ffield, fstruct, slist(i));
1756 charcol := fsegment.column_name;
1757 if l_debug_on then
1758 wsh_debug_sv.log(l_module_name, 'charcol', charcol);
1759 end if;
1760 charcol := substr(charcol, 8, length(fsegment.column_name));
1761 numbcol := to_number(charcol);
1762 if l_debug_on then
1763 wsh_debug_sv.log(l_module_name, 'numcol', numbcol);
1764 end if;
1765 concat_string := concat_string||p_seg_array(numbcol);
1766 END LOOP;
1767
1768 If l_debug_on THEN
1769 wsh_debug_sv.logmsg(l_module_name, 'Calling validate_segs');
1770 END IF;
1771
1772 x_flag := fnd_flex_keyval.validate_segs(
1773 operation =>'CHECK_COMBINATION',
1774 appl_short_name => p_app_short_name,
1775 key_flex_code => p_key_flx_code,
1776 structure_number=> p_struct_number,
1777 concat_segments => concat_string,
1778 data_set => p_org_id,
1779 values_or_ids => p_val_or_ids,
1780 where_clause => p_wh_clause );
1781
1782 If l_debug_on THEN
1783 wsh_debug_sv.log(l_module_name, 'x_flag', x_flag);
1784 END IF;
1785
1786 p_id := fnd_flex_keyval.combination_id;
1787
1788 IF x_flag = FALSE THEN
1789 RAISE wrong_combination;
1790 END IF;
1791 ELSE
1792 x_flag := FALSE;
1793 RAISE delimiter_null;
1794 end if;
1795
1796 IF p_concat_segs IS NOT NULL THEN
1797 x_flag:= fnd_flex_keyval.validate_segs(
1798 operation => 'CHECK_COMBINATION',
1799 appl_short_name => p_app_short_name,
1800 key_flex_code => p_key_flx_code,
1801 structure_number=> p_struct_number,
1802 concat_segments => p_concat_segs,
1803 data_set => p_org_id);
1804 END IF;
1805
1806 END IF; -- id is null
1807 --
1808 IF l_debug_on THEN
1809 WSH_DEBUG_SV.pop(l_module_name);
1810 END IF;
1811 --
1812 EXCEPTION
1813 WHEN delimiter_null THEN
1814 FND_MESSAGE.SET_NAME('WSH','WSH_OI_FLEX_DELIMITER_NULL');
1815 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1816 --
1817 IF l_debug_on THEN
1818 WSH_DEBUG_SV.logmsg(l_module_name,'DELIMITER_NULL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1819 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIMITER_NULL');
1820 END IF;
1821 --
1822 WHEN wrong_combination THEN
1823 FND_MESSAGE.SET_NAME('WSH','WSH_OI_FLEX_INV_COMB_ERROR');
1824 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1825 --
1826 IF l_debug_on THEN
1827 WSH_DEBUG_SV.logmsg(l_module_name,'WRONG_COMBINATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1828 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WRONG_COMBINATION');
1829 END IF;
1830 --
1831 WHEN others THEN
1832 IF l_debug_on THEN
1833 WSH_DEBUG_SV.log(l_module_name, 'Exception:', fnd_flex_key_api.message);
1834 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1836 END IF;
1837 END Validate_Flexfields;
1838
1839 --========================================================================
1840 -- PROCEDURE : Validate_Item
1841 --
1842 -- COMMENT : Validates Inventory_Item_id/Concatenated name/Segment array
1843 -- using FND APIs. Item id takes precedence over the other validations.
1844 --========================================================================
1845
1846 PROCEDURE Validate_Item(
1847 p_inventory_item_id IN OUT NOCOPY NUMBER,
1848 p_inventory_item IN VARCHAR2,
1849 p_organization_id IN NUMBER,
1850 p_seg_array IN FND_FLEX_EXT.SegmentArray,
1851 x_return_status OUT NOCOPY VARCHAR2,
1852 p_item_type IN VARCHAR2 --Defaulted to Null in Spec
1853 )
1854 IS
1855
1856 valid_flag boolean := NULL;
1857 -- Patchset I Harmonization project KVENKATE
1858 l_item_type VARCHAR2(100);
1859 l_return_status VARCHAR2(100);
1860
1861 --
1862 l_debug_on BOOLEAN;
1863 --
1864 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ITEM';
1865 --
1866 BEGIN
1867 --
1868 --
1869 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1870 --
1871 IF l_debug_on IS NULL
1872 THEN
1873 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1874 END IF;
1875 --
1876 IF l_debug_on THEN
1877 WSH_DEBUG_SV.push(l_module_name);
1878 --
1879 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
1880 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM',P_INVENTORY_ITEM);
1881 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1882 END IF;
1883 --
1884 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1885
1886 IF nvl(p_inventory_item_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1887 AND nvl(p_inventory_item, fnd_api.g_miss_char) = fnd_api.g_miss_char
1888 AND p_seg_array.count = 0
1889 THEN
1890
1891 IF l_debug_on THEN
1892 WSH_DEBUG_SV.pop(l_module_name);
1893 END IF;
1894 RETURN;
1895 END IF;
1896 --
1897 validate_flexfields(
1898 p_id => p_inventory_item_id,
1899 p_concat_segs => p_inventory_item,
1900 p_app_short_name => 'INV',
1901 p_key_flx_code => 'MSTK',
1902 p_struct_number => 101,
1903 p_org_id => p_organization_id,
1904 p_seg_array => p_seg_array,
1905 p_val_or_ids => 'I',
1906 x_flag => valid_flag);
1907
1908 IF valid_flag = FALSE THEN
1909
1910 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ITEM');
1911 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1912 wsh_util_core.add_message(x_return_status,l_module_name);
1913 IF l_debug_on THEN
1914 WSH_DEBUG_SV.pop(l_module_name);
1915 END IF;
1916 RETURN;
1917
1918 END IF;
1919 --
1920 -- harmonization project begin KVENKATE
1921 IF(p_item_type IN ('VEH_ITEM', 'CONT_ITEM')) THEN
1922 Find_Item_Type(
1923 p_inventory_item_id => p_inventory_item_id,
1924 p_organization_id => p_organization_id,
1925 x_item_type => l_item_type,
1926 x_return_status => l_return_status);
1927
1928 IF nvl(l_item_type, FND_API.G_MISS_CHAR) <> p_item_type THEN
1929 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ITEM');
1930 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1931 wsh_util_core.add_message(x_return_status,l_module_name);
1932 END IF;
1933 END IF;
1934 -- harmonization project end KVENKATE
1935 IF l_debug_on THEN
1936 WSH_DEBUG_SV.pop(l_module_name);
1937 END IF;
1938 --
1939 END Validate_Item;
1940
1941 -- LINE SPECIFIC VALIDATIONS BELOW --
1942 PROCEDURE Validate_Boolean(
1943 p_flag IN VARCHAR2,
1944 x_return_status OUT NOCOPY VARCHAR2)
1945 IS
1946 --
1947 l_debug_on BOOLEAN;
1948 --
1949 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_BOOLEAN';
1950 --
1951 BEGIN
1952 --
1953 --
1954 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1955 --
1956 IF l_debug_on IS NULL
1957 THEN
1958 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1959 END IF;
1960 --
1961 IF l_debug_on THEN
1962 WSH_DEBUG_SV.push(l_module_name);
1963 --
1964 WSH_DEBUG_SV.log(l_module_name,'P_FLAG',P_FLAG);
1965 END IF;
1966 --
1967 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1968
1969 IF (NVL(p_flag, 'N') NOT IN ('Y', 'N')) THEN
1970 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1971 END IF;
1972 --
1973 IF l_debug_on THEN
1974 WSH_DEBUG_SV.pop(l_module_name);
1975 END IF;
1976 --
1977 END;
1978
1979 PROCEDURE Validate_Released_Status(
1980 p_released_status IN VARCHAR2,
1981 x_return_status OUT NOCOPY VARCHAR2)
1982 IS
1983 --
1984 l_debug_on BOOLEAN;
1985 --
1986 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_RELEASED_STATUS';
1987 --
1988 BEGIN
1989 --
1990 --
1991 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1992 --
1993 IF l_debug_on IS NULL
1994 THEN
1995 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1996 END IF;
1997 --
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.push(l_module_name);
2000 --
2001 WSH_DEBUG_SV.log(l_module_name,'P_RELEASED_STATUS',P_RELEASED_STATUS);
2002 END IF;
2003 --
2004 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2005
2006 IF (NVL(p_released_status, 'N') NOT IN ('N', 'Y', 'R', 'X')) THEN
2007 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2008 END IF;
2009 --
2010 IF l_debug_on THEN
2011 WSH_DEBUG_SV.pop(l_module_name);
2012 END IF;
2013 --
2014 END;
2015
2016 PROCEDURE Validate_Order_uom(
2017 p_organization_id IN NUMBER,
2018 p_inventory_item_id IN NUMBER,
2019 p_unit_of_measure IN VARCHAR2,
2020 x_uom_code IN OUT NOCOPY VARCHAR2,
2021 x_return_status OUT NOCOPY VARCHAR2)
2022 IS
2023 CURSOR check_order_uom IS
2024 SELECT uom_code
2025 FROM mtl_item_uoms_view
2026 WHERE organization_id = p_organization_id AND
2027 inventory_item_id = p_inventory_item_id AND
2028 uom_code = x_uom_code
2029 UNION ALL
2030 SELECT uom_code
2031 FROM mtl_item_uoms_view
2032 WHERE organization_id = p_organization_id AND
2033 inventory_item_id = p_inventory_item_id AND
2034 unit_of_measure = p_unit_of_measure;
2035 --
2036 l_debug_on BOOLEAN;
2037 --
2038 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ORDER_UOM';
2039 --
2040 BEGIN
2041 --
2042 --
2043 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2044 --
2045 IF l_debug_on IS NULL
2046 THEN
2047 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2048 END IF;
2049 --
2050 IF l_debug_on THEN
2051 WSH_DEBUG_SV.push(l_module_name);
2052 --
2053 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2054 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2055 WSH_DEBUG_SV.log(l_module_name,'P_UNIT_OF_MEASURE',P_UNIT_OF_MEASURE);
2056 WSH_DEBUG_SV.log(l_module_name,'X_UOM_CODE',X_UOM_CODE);
2057 END IF;
2058 --
2059 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2060
2061 IF (p_unit_of_measure IS NOT NULL OR x_uom_code IS NOT NULL) THEN
2062 OPEN check_order_uom;
2063 FETCH check_order_uom INTO x_uom_code;
2064
2065 IF (check_order_uom%NOTFOUND) THEN
2066
2067 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_UOM');
2068 FND_MESSAGE.SET_TOKEN('UOM_TYPE','ordered quantity uom');
2069 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2070 wsh_util_core.add_message(x_return_status,l_module_name);
2071
2072 END IF;
2073 CLOSE check_order_uom;
2074
2075 END IF;
2076 --
2077 IF l_debug_on THEN
2078 WSH_DEBUG_SV.log(l_module_name,'x_uom_code',x_uom_code);
2079 WSH_DEBUG_SV.pop(l_module_name);
2080 END IF;
2081 --
2082 END;
2083
2084 -- CONTAINER SPECIFIC VALIDATIONS BELOW --
2085
2086 -- DELIVERY SPECIFIC VALIDATIONS BELOW --
2087
2088 --========================================================================
2089 -- PROCEDURE : Validate_Delivery_Name
2090 --
2091 -- COMMENT : Validates Delivery_id/Delivery_Name against table
2092 -- wsh_new_deliveries. If both values are specified then only
2093 -- delivery_id is used
2094 --========================================================================
2095
2096 PROCEDURE Validate_Delivery_Name
2097 (p_delivery_id IN OUT NOCOPY NUMBER ,
2098 p_delivery_name IN VARCHAR2 ,
2099 x_return_status OUT NOCOPY VARCHAR2 ) IS
2100
2101 CURSOR check_delivery_name IS
2102 SELECT delivery_id
2103 FROM wsh_new_deliveries
2104 WHERE NVL(p_delivery_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM AND
2105 delivery_id = p_delivery_id
2106 UNION ALL
2107 SELECT delivery_id
2108 FROM wsh_new_deliveries
2109 WHERE NVL(p_delivery_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND
2110 name = p_delivery_name;
2111
2112 --
2113 l_debug_on BOOLEAN;
2114 --
2115 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DELIVERY_NAME';
2116 --
2117 BEGIN
2118 --
2119 --
2120 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2121 --
2122 IF l_debug_on IS NULL
2123 THEN
2124 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2125 END IF;
2126 --
2127 IF l_debug_on THEN
2128 WSH_DEBUG_SV.push(l_module_name);
2129 --
2130 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2131 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
2132 END IF;
2133 --
2134 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2135
2136 IF (p_delivery_id IS NOT NULL) OR (p_delivery_name IS NOT NULL) THEN
2137
2138 OPEN check_delivery_name;
2139 FETCH check_delivery_name INTO p_delivery_id;
2140
2141 IF (check_delivery_name%NOTFOUND) THEN
2142
2143 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_DEL_NAME');
2144 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2145 wsh_util_core.add_message(x_return_status,l_module_name);
2146
2147 END IF;
2148
2149 CLOSE check_delivery_name;
2150
2151 END IF;
2152 --
2153 IF l_debug_on THEN
2154 WSH_DEBUG_SV.log(l_module_name,'p_delivery_name',p_delivery_name);
2155 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id',p_delivery_id);
2156 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2157 WSH_DEBUG_SV.pop(l_module_name);
2158 END IF;
2159 --
2160 END Validate_Delivery_Name;
2161
2162
2163 --========================================================================
2164 -- PROCEDURE : Validate_Report_Set
2165 --
2166 -- COMMENT : Validates Report_set_id/Report_set name against table
2167 -- wsh_report_sets. If both values are specified then only
2168 -- report_set_id is used
2169 --========================================================================
2170
2171 PROCEDURE Validate_Report_Set
2172 (p_report_set_id IN OUT NOCOPY NUMBER ,
2173 p_report_set_name IN VARCHAR2 ,
2174 x_return_status OUT NOCOPY VARCHAR2 ) IS
2175
2176 CURSOR check_report_set IS
2177 SELECT report_set_id
2178 FROM wsh_report_sets
2179 WHERE p_report_set_id IS NOT NULL AND
2180 report_set_id = p_report_set_id AND
2181 start_date_active <= sysdate AND
2182 nvl(end_date_active,sysdate) >= sysdate
2183 UNION ALL
2184 SELECT report_set_id
2185 FROM wsh_report_sets
2186 WHERE p_report_set_id IS NULL AND
2187 name = p_report_set_name AND
2188 start_date_active <= sysdate AND
2189 nvl(end_date_active,sysdate) >= sysdate;
2190
2191 --
2192 l_debug_on BOOLEAN;
2193 --
2194 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_REPORT_SET';
2195 --
2196 BEGIN
2197 --
2198 --
2199 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2200 --
2201 IF l_debug_on IS NULL
2202 THEN
2203 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2204 END IF;
2205 --
2206 IF l_debug_on THEN
2207 WSH_DEBUG_SV.push(l_module_name);
2208 --
2209 WSH_DEBUG_SV.log(l_module_name,'P_REPORT_SET_ID',P_REPORT_SET_ID);
2210 WSH_DEBUG_SV.log(l_module_name,'P_REPORT_SET_NAME',P_REPORT_SET_NAME);
2211 END IF;
2212 --
2213 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2214
2215 IF (p_report_set_id IS NOT NULL) OR (p_report_set_name IS NOT NULL) THEN
2216
2217 OPEN check_report_set;
2218 FETCH check_report_set INTO p_report_set_id;
2219
2220 IF (check_report_set%NOTFOUND) THEN
2221
2222 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_REPORT_SET');
2223 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2224 wsh_util_core.add_message(x_return_status,l_module_name);
2225
2226 END IF;
2227
2228 CLOSE check_report_set;
2229
2230 END IF;
2231 --
2232 IF l_debug_on THEN
2233 WSH_DEBUG_SV.log(l_module_name,'p_report_set_id',p_report_set_id);
2234 WSH_DEBUG_SV.pop(l_module_name);
2235 END IF;
2236 --
2237 END Validate_Report_Set;
2238
2239 --========================================================================
2240 -- PROCEDURE : Validate_Loading_Order
2241 --
2242 -- COMMENT : Validates Loading_Order_Flag/Loading_order_desc by
2243 -- calling Validate_lookup_code. If both values are
2244 -- specified then only Loading_order_desc is used
2245 --========================================================================
2246
2247 PROCEDURE Validate_Loading_Order(
2248 p_loading_order_flag IN OUT NOCOPY VARCHAR2 ,
2249 p_loading_order_desc IN VARCHAR2 ,
2250 x_return_status OUT NOCOPY VARCHAR2 )
2251 IS
2252 --
2253 l_debug_on BOOLEAN;
2254 --
2255 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOADING_ORDER';
2256 --
2257 BEGIN
2258 --
2259 --
2260 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2261 --
2262 IF l_debug_on IS NULL
2263 THEN
2264 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2265 END IF;
2266 --
2267 IF l_debug_on THEN
2268 WSH_DEBUG_SV.push(l_module_name);
2269 --
2270 WSH_DEBUG_SV.log(l_module_name,'P_LOADING_ORDER_FLAG',P_LOADING_ORDER_FLAG);
2271 WSH_DEBUG_SV.log(l_module_name,'P_LOADING_ORDER_DESC',P_LOADING_ORDER_DESC);
2272 END IF;
2273 --
2274 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2275 IF nvl(p_loading_order_flag,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
2276 OR nvl(p_loading_order_desc,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
2277 THEN
2278 --
2279 IF nvl(p_loading_order_flag,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
2280 --
2281 IF upper(p_loading_order_desc) ='FORWARD' THEN
2282 p_loading_order_flag := 'F';
2283 ELSIF upper(p_loading_order_desc) ='FORWARD INVERTED'THEN
2284 p_loading_order_flag := 'FI';
2285 ELSIF upper(p_loading_order_desc) ='REVERSE' THEN
2286 p_loading_order_flag := 'R';
2287 ELSIF upper(p_loading_order_desc) ='REVERSE INVERTED' THEN
2288 p_loading_order_flag := 'RI';
2289 ELSE
2290 --
2291 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOADING_ORDER');
2292 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2293 wsh_util_core.add_message(x_return_status,l_module_name);
2294 --
2295 END IF;
2296 --
2297 ELSIF p_loading_order_flag NOT IN ('F','FI', 'R','RI') THEN
2298 --
2299 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOADING_ORDER');
2300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2301 wsh_util_core.add_message(x_return_status,l_module_name);
2302 --
2303 END IF;
2304 --
2305 END IF;
2306 --
2307 IF l_debug_on THEN
2308 WSH_DEBUG_SV.pop(l_module_name);
2309 END IF;
2310 --
2311 EXCEPTION
2312 WHEN OTHERS THEN
2313 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2314 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_LOADING_ORDER');
2315 --
2316 IF l_debug_on THEN
2317 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2318 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2319 END IF;
2320 --
2321 END Validate_Loading_Order;
2322
2323 -- STOP SPECIFIC VALIDATIONS BELOW --
2324
2325 --========================================================================
2326 -- PROCEDURE : Validate_Stop_Name
2327 --
2328 -- COMMENT : Validates Stop_id OR
2329 -- Trip_id+Stop_Location_id+Planned_Departure_date against table
2330 -- wsh_trips. If both validations are possible then only
2331 -- stop_id is validated
2332 --========================================================================
2333
2334 PROCEDURE Validate_Stop_Name
2335 (p_stop_id IN OUT NOCOPY NUMBER ,
2336 p_trip_id IN NUMBER ,
2337 p_stop_location_id IN NUMBER ,
2338 p_planned_dep_date IN DATE,
2339 x_return_status OUT NOCOPY VARCHAR2 ) IS
2340
2341 CURSOR check_stop_name IS
2342 SELECT stop_id
2343 FROM wsh_trip_stops
2344 WHERE NVL(p_stop_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM AND
2345 stop_id = p_stop_id
2346 UNION ALL
2347 SELECT stop_id
2348 FROM wsh_trip_stops
2349 WHERE NVL(p_stop_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND
2350 trip_id = p_trip_id AND
2351 stop_location_id = p_stop_location_id AND
2352 planned_departure_date = nvl(p_planned_dep_date, planned_departure_date)
2353 ;
2354
2355 --
2356 l_debug_on BOOLEAN;
2357 --
2358 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_STOP_NAME';
2359 --
2360 BEGIN
2361 --
2362 --
2363 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2364 --
2365 IF l_debug_on IS NULL
2366 THEN
2367 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2368 END IF;
2369 --
2370 IF l_debug_on THEN
2371 WSH_DEBUG_SV.push(l_module_name);
2372 --
2373 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2374 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2375 WSH_DEBUG_SV.log(l_module_name,'P_STOP_LOCATION_ID',P_STOP_LOCATION_ID);
2376 WSH_DEBUG_SV.log(l_module_name,'P_PLANNED_DEP_DATE',P_PLANNED_DEP_DATE);
2377 END IF;
2378 --
2379 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2380
2381 IF (p_stop_id IS NOT NULL) OR (p_trip_id IS NOT NULL) THEN
2382
2383 OPEN check_stop_name;
2384 FETCH check_stop_name INTO p_stop_id;
2385
2386 IF (check_stop_name%NOTFOUND) THEN
2387
2388 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_STOP_NAME');
2389 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2390 wsh_util_core.add_message(x_return_status,l_module_name);
2391
2392 END IF;
2393
2394 CLOSE check_stop_name;
2395
2396 END IF;
2397 --
2398 IF l_debug_on THEN
2399 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
2400 WSH_DEBUG_SV.pop(l_module_name);
2401 END IF;
2402 --
2403 END Validate_Stop_Name;
2404
2405
2406 -- TRIP SPECIFIC VALIDATIONS BELOW --
2407
2408 --========================================================================
2409 -- PROCEDURE : Validate_Trip_Name
2410 --
2411 -- COMMENT : Validates Trip_id/Trip_Name against table
2412 -- wsh_trips. If both values are specified then only
2413 -- trip_id is used
2414 --========================================================================
2415
2416 PROCEDURE Validate_Trip_Name
2417 (p_trip_id IN OUT NOCOPY NUMBER ,
2418 p_trip_name IN VARCHAR2 ,
2419 x_return_status OUT NOCOPY VARCHAR2 ) IS
2420
2421 CURSOR check_trip_name IS
2422 SELECT trip_id
2423 FROM wsh_trips
2424 WHERE NVL(p_trip_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM AND
2425 trip_id = p_trip_id
2426 UNION ALL
2427 SELECT trip_id
2428 FROM wsh_trips
2429 WHERE NVL(p_trip_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND
2430 name = p_trip_name;
2431
2432 --
2433 l_debug_on BOOLEAN;
2434 --
2435 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIP_NAME';
2436 --
2437 BEGIN
2438 --
2439 --
2440 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2441 --
2442 IF l_debug_on IS NULL
2443 THEN
2444 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2445 END IF;
2446 --
2447 IF l_debug_on THEN
2448 WSH_DEBUG_SV.push(l_module_name);
2449 --
2450 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2451 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_NAME',P_TRIP_NAME);
2452 END IF;
2453 --
2454 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2455
2456 IF (p_trip_id IS NOT NULL) OR (p_trip_name IS NOT NULL) THEN
2457
2458 OPEN check_trip_name;
2459 FETCH check_trip_name INTO p_trip_id;
2460
2461 IF (check_trip_name%NOTFOUND) THEN
2462
2463 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_TRIP_NAME');
2464 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2465 wsh_util_core.add_message(x_return_status,l_module_name);
2466
2467 END IF;
2468
2469 CLOSE check_trip_name;
2470
2471 END IF;
2472 --
2473 IF l_debug_on THEN
2474 WSH_DEBUG_SV.pop(l_module_name);
2475 END IF;
2476 --
2477 END Validate_Trip_Name;
2478
2479 --========================================================================
2480 -- PROCEDURE : Validate_Trip_MultiStops
2481 -- 4106444 -skattama
2482 -- COMMENT : Validates for Trip_id if mode is other then
2483 -- 'TRUCK', the number of stops should not be more
2484 -- than 2.
2485 --========================================================================
2486
2487 PROCEDURE Validate_Trip_MultiStops
2488 (p_trip_id IN NUMBER ,
2489 p_mode_of_transport IN VARCHAR2 ,
2490 x_return_status OUT NOCOPY VARCHAR2 ) IS
2491
2492 CURSOR check_stop_count is
2493 select count(*)
2494 from wsh_trip_stops s
2495 where s.trip_id = p_trip_id
2496 and s.physical_stop_id is null
2497 and rownum < 4;
2498
2499 l_stop_count NUMBER;
2500 l_trip_name VARCHAR2(100);
2501 --
2502 l_debug_on BOOLEAN;
2503 --
2504 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Trip_MultiStops';
2505 --
2506 BEGIN
2507 --
2508 --
2509 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2510 --
2511 IF l_debug_on IS NULL
2512 THEN
2513 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2514 END IF;
2515 --
2516 IF l_debug_on THEN
2517 WSH_DEBUG_SV.push(l_module_name);
2518 --
2519 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2520 END IF;
2521 --
2522 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2523
2524 IF ((p_trip_id IS NOT NULL) AND (p_mode_of_transport <> 'TRUCK')) THEN
2525
2526 OPEN check_stop_count;
2527 FETCH check_stop_count INTO l_stop_count;
2528
2529 IF l_stop_count = 3 THEN
2530
2531 l_trip_name := WSH_TRIPS_PVT.Get_Name(p_trip_id);
2532 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_MOT_STOP_COUNT');
2533 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
2534 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2535 wsh_util_core.add_message(x_return_status,l_module_name);
2536
2537 END IF;
2538
2539 CLOSE check_stop_count;
2540
2541 END IF;
2542 --
2543 IF l_debug_on THEN
2544 WSH_DEBUG_SV.pop(l_module_name);
2545 END IF;
2546 --
2547 END Validate_Trip_MultiStops;
2548
2549
2550 --========================================================================
2551 -- FUNCTION : Check_Wms_Org
2552 --
2553 -- COMMENT : Check if the Organization is WMS enabled.
2554 -- If Yes, Return 'Y'. Otherwise 'N'
2555 --========================================================================
2556
2557 FUNCTION Check_Wms_Org
2558 (p_organization_id IN NUMBER) RETURN VARCHAR2 IS
2559
2560 l_return_status VARCHAR2(1) := 'N';
2561 l_proc_status VARCHAR2(1000) ;
2562 l_msg_count NUMBER;
2563 l_msg_data VARCHAR2(2000);
2564
2565 -- LPN CONV. rv
2566 l_return_value boolean;
2567
2568 --
2569 l_debug_on BOOLEAN;
2570 --
2571 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_WMS_ORG';
2572 --
2573 BEGIN
2574 --
2575 --
2576 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2577 --
2578 IF l_debug_on IS NULL
2579 THEN
2580 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2581 END IF;
2582 --
2583 IF l_debug_on THEN
2584 WSH_DEBUG_SV.push(l_module_name);
2585 --
2586 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2587 END IF;
2588
2589 -- bug 5161801, if p_organization_id is NULL return 'N';
2590 IF (p_organization_id is NULL) THEN
2591 --{
2592 l_return_status := 'N';
2593 --}
2594 -- LPN CONV. rv
2595 ELSIF (p_organization_id = g_organization_id) THEN
2596 --{
2597 l_return_status := g_is_wms_org;
2598 --}
2599 ELSE
2600 --{
2601 --
2602 --
2603 IF l_debug_on THEN
2604 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE-x_dlvyTbl',WSH_DEBUG_SV.C_PROC_LEVEL);
2605 END IF;
2606 --
2607 wsh_util_core.get_cached_value
2608 (
2609 p_cache_tbl => g_orgn_id_tbl,
2610 p_cache_ext_tbl => g_orgn_id_ext_tbl,
2611 p_value => l_return_status,
2612 p_key => p_organization_id,
2613 p_action => 'GET',
2614 x_return_status => l_proc_status
2615 );
2616 --
2617 --
2618 IF l_proc_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2619 THEN
2620 RAISE FND_API.G_EXC_ERROR;
2621 ELSIF l_proc_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2622 THEN
2623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2624 ELSIF l_proc_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2625 THEN
2626 --{
2627
2628 l_return_value := wms_install.check_install(
2629 p_organization_id => p_organization_id,
2630 x_return_status => l_proc_status,
2631 x_msg_count => l_msg_count,
2632 x_msg_data => l_msg_data);
2633
2634
2635 IF l_proc_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2636 THEN
2637 RAISE FND_API.G_EXC_ERROR;
2638 ELSIF l_proc_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2639 THEN
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641 END IF;
2642
2643 IF l_return_value THEN
2644 l_return_status := 'Y';
2645 ELSE
2646 l_return_status := 'N';
2647 END IF;
2648
2649 IF l_debug_on THEN
2650 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE-x_dlvyTbl',WSH_DEBUG_SV.C_PROC_LEVEL);
2651 END IF;
2652 --
2653 wsh_util_core.get_cached_value
2654 (
2655 p_cache_tbl => g_orgn_id_tbl,
2656 p_cache_ext_tbl => g_orgn_id_ext_tbl,
2657 p_value => l_return_status,
2658 p_key => p_organization_id,
2659 p_action => 'PUT',
2660 x_return_status => l_proc_status
2661 );
2662 --
2663 --
2664 IF l_proc_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2665 THEN
2666 RAISE FND_API.G_EXC_ERROR;
2667 ELSIF l_proc_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2668 THEN
2669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2670 END IF;
2671 --
2672 --}
2673 END IF;
2674 --
2675 g_organization_id := p_organization_id;
2676 g_is_wms_org := l_return_status;
2677 --
2678 --}
2679 END IF;
2680 -- LPN CONV. rv
2681
2682
2683
2684 IF l_debug_on THEN
2685 WSH_DEBUG_SV.log(l_module_name,'Returns ',l_return_status);
2686 WSH_DEBUG_SV.pop(l_module_name);
2687 END IF;
2688 --
2689 RETURN l_return_status ;
2690
2691 EXCEPTION
2692 WHEN FND_API.G_EXC_ERROR THEN
2693 --
2694 IF l_debug_on THEN
2695 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2696 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2697 END IF;
2698 --
2699 -- bug 5161801, return 'N' when it encounters any exception
2700 l_return_status := 'N';
2701 RETURN l_return_status ;
2702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2703 --
2704 IF l_debug_on THEN
2705 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2706 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2707 END IF;
2708 --
2709 -- bug 5161801, return 'N' when it encounters any exception
2710 l_return_status := 'N';
2711 RETURN l_return_status ;
2712 WHEN OTHERS THEN
2713 --
2714 IF l_debug_on THEN
2715 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2716 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2717 END IF;
2718 --
2719 -- bug 5161801, return 'N' when it encounters any exception
2720 l_return_status := 'N';
2721 RETURN l_return_status ;
2722 END Check_Wms_Org ;
2723
2724 --Harmonizing Project I --heali
2725 PROCEDURE validate_from_to_dates (
2726 p_from_date IN DATE,
2727 p_to_date IN DATE,
2728 x_return_status OUT NOCOPY VARCHAR2) IS
2729
2730 l_debug_on BOOLEAN;
2731 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FROM_TO_DATES';
2732
2733 BEGIN
2734 --
2735 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2736 --
2737 IF l_debug_on IS NULL
2738 THEN
2739 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2740 END IF;
2741 --
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.push(l_module_name);
2744 WSH_DEBUG_SV.log(l_module_name,'p_from_date',p_from_date);
2745 WSH_DEBUG_SV.log(l_module_name,'p_to_date',p_to_date);
2746 END IF;
2747 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2748
2749 IF nvl(p_from_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date
2750 OR nvl(p_to_date, fnd_api.g_miss_date) <> fnd_api.g_miss_date
2751 THEN
2752 IF (p_from_date > p_to_date) THEN
2753 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2754 FND_MESSAGE.SET_NAME('WSH','WSH_XC_INVALID_DATE_RANGE');
2755 wsh_util_core.add_message(x_return_status,l_module_name);
2756 END IF;
2757 END IF;
2758
2759
2760 IF l_debug_on THEN
2761 WSH_DEBUG_SV.pop(l_module_name);
2762 END IF;
2763
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2767 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.validate_from_to_dates');
2768 IF l_debug_on THEN
2769 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2770 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2771 END IF;
2772 END validate_from_to_dates;
2773
2774
2775 PROCEDURE Validate_Trip_status (
2776 p_trip_id IN NUMBER,
2777 p_action IN VARCHAR2,
2778 x_return_status OUT NOCOPY VARCHAR2) IS
2779
2780 /* J TP Release : If assigning del to trip doesn't introduce new stops, ok to assign to planned trips */
2781 CURSOR get_trip_status_1(l_trip_id NUMBER) IS
2782 select 'X', name
2783 from wsh_trips
2784 where trip_id = p_trip_id
2785 and
2786 (
2787 ( status_code IN ( 'OP','IT' )
2788 and nvl(shipments_type_flag,'O') = 'O' -- J-IB-NPARIKH
2789 )
2790 OR nvl(shipments_type_flag,'O') <> 'O' -- J-IB-NPARIKH
2791 )
2792 and nvl(planned_flag,'N') ='N'
2793 and rownum = 1;
2794 CURSOR get_trip_status_2(l_trip_id NUMBER) IS
2795 select 'X', name
2796 from wsh_trips
2797 where trip_id = p_trip_id
2798 and
2799 (
2800 ( status_code IN ( 'OP' )
2801 and nvl(shipments_type_flag,'O') = 'O' -- J-IB-NPARIKH
2802 )
2803 OR nvl(shipments_type_flag,'O') <> 'O' -- J-IB-NPARIKH
2804 )
2805 and nvl(planned_flag,'N') IN ('N', 'Y')
2806 and rownum = 1;
2807
2808 l_valid_trip VARCHAR2(30);
2809 l_trip_name VARCHAR2(100);
2810 l_debug_on BOOLEAN;
2811 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIP_STATUS';
2812 BEGIN
2813 --
2814 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2815 --
2816 IF l_debug_on IS NULL
2817 THEN
2818 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2819 END IF;
2820 --
2821 IF l_debug_on THEN
2822 WSH_DEBUG_SV.push(l_module_name);
2823 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
2824 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
2825 END IF;
2826
2827 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2828
2829 IF (p_trip_id IS NOT NULL) THEN
2830 IF p_action = 'CREATE' THEN
2831 OPEN get_trip_status_1(p_trip_id);
2832 FETCH get_trip_status_1 INTO l_valid_trip,l_trip_name;
2833 CLOSE get_trip_status_1;
2834 ELSE
2835 OPEN get_trip_status_2(p_trip_id);
2836 FETCH get_trip_status_2 INTO l_valid_trip,l_trip_name;
2837 CLOSE get_trip_status_2;
2838 END IF;
2839
2840 IF ( nvl(l_valid_trip, FND_API.G_MISS_CHAR) <> 'X' ) THEN
2841 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2842 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_STATUS_NO_ACTION');
2843 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
2844 wsh_util_core.add_message(x_return_status,l_module_name);
2845 END IF;
2846 END IF;
2847
2848 IF l_debug_on THEN
2849 WSH_DEBUG_SV.pop(l_module_name);
2850 END IF;
2851
2852 EXCEPTION
2853 WHEN OTHERS THEN
2854 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2855 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.validate_trip_status');
2856 IF l_debug_on THEN
2857 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2858 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2859 END IF;
2860 END validate_trip_status;
2861
2862 -- I Harmonization: rvishnuv *******
2863
2864 --========================================================================
2865 -- PROCEDURE : Validate_Carrier
2866 --
2867 -- COMMENT : Check if the Carrier is a valid carrier or not.
2868 --========================================================================
2869 PROCEDURE Validate_Carrier(
2870 p_carrier_name IN VARCHAR2,
2871 x_carrier_id IN OUT NOCOPY NUMBER,
2872 x_return_status OUT NOCOPY VARCHAR2)
2873 IS
2874 --
2875 l_debug_on BOOLEAN;
2876 --
2877 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CARRIER';
2878 --
2879 cursor l_carrier_csr is
2880 select carrier_id
2881 from wsh_carriers_v
2882 where nvl(x_carrier_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
2883 and carrier_id = x_carrier_id
2884 and nvl(generic_flag, 'N') = 'N'
2885 and active = 'A'
2886 union all
2887 select carrier_id
2888 from wsh_carriers_v
2889 where nvl(x_carrier_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
2890 and carrier_name = p_carrier_name
2891 and nvl(generic_flag, 'N') = 'N'
2892 and active = 'A';
2893
2894
2895 BEGIN
2896 --
2897 --
2898 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2899 --
2900 IF l_debug_on IS NULL
2901 THEN
2902 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2903 END IF;
2904 --
2905 IF l_debug_on THEN
2906 --
2907 WSH_DEBUG_SV.push(l_module_name);
2908 --
2909 WSH_DEBUG_SV.log(l_module_name,'p_carrier_name', p_carrier_name);
2910 WSH_DEBUG_SV.log(l_module_name,'x_carrier_id', x_carrier_id);
2911 --
2912 END IF;
2913 --
2914 x_return_status := wsh_util_core.g_ret_sts_success;
2915 --
2916 IF nvl(x_carrier_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2917 OR nvl(p_carrier_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
2918 THEN
2919 --
2920 open l_carrier_csr;
2921 fetch l_carrier_csr into x_carrier_id;
2922 --
2923 IF l_carrier_csr%NOTFOUND THEN
2924 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NOT_FOUND');
2925 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2926 wsh_util_core.add_message(x_return_status,l_module_name);
2927 END IF;
2928 close l_carrier_csr;
2929 --
2930 END IF;
2931 --
2932 IF l_debug_on THEN
2933 WSH_DEBUG_SV.pop(l_module_name);
2934 END IF;
2935 --
2936 EXCEPTION
2937 --
2938 WHEN OTHERS THEN
2939 --
2940 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2941 --
2942 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_CARRIER');
2943 IF l_debug_on THEN
2944 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2945 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2946 END IF;
2947 --
2948 END Validate_Carrier;
2949
2950 --========================================================================
2951 -- PROCEDURE : Validate_Freight_Carrier
2952 --
2953 -- COMMENT : This API checks if the inputs ship method, carrier, and service level
2954 -- are valid values.
2955 -- Also if ship method is a valid input, then, based on the organization,
2956 -- it derives the carrier, service level and mode of transport.
2957 -- Also, if the ship method is null and if all the remaining three
2958 -- components are defined, then based on the organization, it derives
2959 -- the ship method.
2960 -- Organization_id is a mandatory parameter if the entity is DLVY.
2961 -- p_entity_type can have values of 'TRIP' or 'DLVY'.
2962 -- p_entity_id should contain either trip_id or delivery_id
2963 -- depending on the p_entity_type.
2964 --========================================================================
2965 PROCEDURE Validate_Freight_Carrier(
2966 p_ship_method_name IN OUT NOCOPY VARCHAR2,
2967 x_ship_method_code IN OUT NOCOPY VARCHAR2,
2968 p_carrier_name IN VARCHAR2,
2969 x_carrier_id IN OUT NOCOPY NUMBER,
2970 x_service_level IN OUT NOCOPY VARCHAR2,
2971 x_mode_of_transport IN OUT NOCOPY VARCHAR2,
2972 p_entity_type IN VARCHAR2,
2973 p_entity_id IN NUMBER,
2974 p_organization_id IN NUMBER, -- defaulted to NULL
2975 x_return_status OUT NOCOPY VARCHAR2,
2976 p_caller IN VARCHAR2)
2977 IS
2978 --
2979 l_debug_on BOOLEAN;
2980 --
2981 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FREIGHT_CARRIER';
2982 --
2983 l_return_status VARCHAR2(1);
2984 l_dummy_meaning VARCHAR2(4000);
2985 l_num_errors NUMBER;
2986 l_num_warnings NUMBER;
2987 l_sm_is_null BOOLEAN := FALSE;
2988 --
2989 l_new_carrier VARCHAR2(1);
2990 l_ship_method_code_backup wsh_new_deliveries.ship_method_code%TYPE;
2991
2992 -- Below cursors are used if the entity is a delivery.
2993 --TP does not honor WSH's org assignment for ship method, so if caller is TP,
2994 --p_organization_id need not be considered
2995
2996 cursor l_dlvy_ship_method_code_csr is
2997 -- Bug 2757672
2998 select wcs.carrier_id,
2999 wcs.service_level,
3000 wcs.mode_of_transport
3001 from wsh_carrier_services wcs,
3002 wsh_org_carrier_services wocs,
3003 wsh_carriers_v wcv
3004 where wcs.carrier_service_id = wocs.carrier_service_id
3005 and wcs.carrier_id = wcv.carrier_id
3006 and wcv.active = 'A'
3007 and NVL(wcv.generic_flag, 'N') = 'N'
3008 and nvl(wcs.enabled_flag, 'N') = 'Y'
3009 and nvl(wocs.enabled_flag, 'N')= 'Y'
3010 and wcs.ship_method_code = x_ship_method_code
3011 and wocs.organization_id = p_organization_id;
3012
3013 --if caller is TP use below cursor (no org check)
3014 cursor l_dlvy_ship_method_code_csr_tp is
3015 -- Bug 2757672
3016 select wcs.carrier_id,
3017 wcs.service_level,
3018 wcs.mode_of_transport
3019 from wsh_carrier_services wcs,
3020 wsh_org_carrier_services wocs,
3021 wsh_carriers_v wcv
3022 where wcs.carrier_service_id = wocs.carrier_service_id
3023 and wcs.carrier_id = wcv.carrier_id
3024 and wcv.active = 'A'
3025 and NVL(wcv.generic_flag, 'N') = 'N'
3026 and nvl(wcs.enabled_flag, 'N') = 'Y'
3027 and nvl(wocs.enabled_flag, 'N')= 'Y'
3028 and wcs.ship_method_code = x_ship_method_code
3029 and rownum=1;
3030
3031 --
3032 cursor l_dlvy_carrier_services_csr is
3033 -- Bug 2757672
3034 select wcs.ship_method_code
3035 from wsh_carrier_services wcs,
3036 wsh_org_carrier_services wocs,
3037 wsh_carriers_v wcv
3038 where wcs.carrier_service_id = wocs.carrier_service_id
3039 and wcs.carrier_id = wcv.carrier_id
3040 and wcv.active = 'A'
3041 and NVL(wcv.generic_flag, 'N') = 'N'
3042 and nvl(wcs.enabled_flag, 'N') = 'Y'
3043 and nvl(wocs.enabled_flag, 'N')= 'Y'
3044 and wcs.carrier_id = x_carrier_id
3045 and ( (wcs.service_level is null
3046 and x_service_level is null )
3047 or
3048 ( wcs.service_level is not null
3049 and wcs.service_level = x_service_level )
3050 )
3051 and ( (wcs.mode_of_transport is null
3052 and x_mode_of_transport is null )
3053 or
3054 ( wcs.mode_of_transport is not null
3055 and wcs.mode_of_transport = x_mode_of_transport )
3056 )
3057 and wocs.organization_id = p_organization_id;
3058
3059 --if caller is TP use below cursor (no org check)
3060 cursor l_dlvy_carrier_services_csr_tp is
3061 -- Bug 2757672
3062 select wcs.ship_method_code
3063 from wsh_carrier_services wcs,
3064 wsh_org_carrier_services wocs,
3065 wsh_carriers_v wcv
3066 where wcs.carrier_service_id = wocs.carrier_service_id
3067 and wcs.carrier_id = wcv.carrier_id
3068 and wcv.active = 'A'
3069 and NVL(wcv.generic_flag, 'N') = 'N'
3070 and nvl(wcs.enabled_flag, 'N') = 'Y'
3071 and nvl(wocs.enabled_flag, 'N')= 'Y'
3072 and wcs.carrier_id = x_carrier_id
3073 and ( (wcs.service_level is null
3074 and x_service_level is null )
3075 or
3076 ( wcs.service_level is not null
3077 and wcs.service_level = x_service_level )
3078 )
3079 and ( (wcs.mode_of_transport is null
3080 and x_mode_of_transport is null )
3081 or
3082 ( wcs.mode_of_transport is not null
3083 and wcs.mode_of_transport = x_mode_of_transport )
3084 )
3085 and rownum=1;
3086
3087 --
3088 cursor l_dlvy_car_shp_mthd_csr(p_delivery_id IN NUMBER) is
3089 select carrier_id,
3090 service_level,
3091 mode_of_transport,
3092 ship_method_code
3093 from wsh_new_deliveries
3094 where delivery_id = p_delivery_id;
3095 --
3096 -- Below cursors are used if the entity is a trip.
3097 --
3098 cursor l_trip_ship_method_code_csr is
3099 -- Bug 2757672
3100 select wcs.carrier_id,
3101 wcs.service_level,
3102 wcs.mode_of_transport
3103 from wsh_carrier_services wcs,
3104 wsh_carriers_v wcv
3105 where wcs.ship_method_code = x_ship_method_code
3106 and wcs.carrier_id = wcv.carrier_id
3107 and nvl(wcs.enabled_flag, 'N') = 'Y'
3108 and wcv.active = 'A'
3109 and NVL(wcv.generic_flag, 'N') = 'N';
3110 --
3111 cursor l_trip_carrier_services_csr is
3112 -- Bug 2757672
3113 select distinct wcs.ship_method_code
3114 from wsh_carrier_services wcs,
3115 wsh_carriers_v wcv
3116 where wcs.carrier_id = x_carrier_id
3117 and wcs.carrier_id = wcv.carrier_id
3118 and nvl(wcs.enabled_flag, 'N') = 'Y'
3119 and wcv.active = 'A'
3120 and NVL(wcv.generic_flag, 'N') = 'N'
3121 and ( (wcs.service_level is null
3122 and x_service_level is null )
3123 or
3124 ( wcs.service_level is not null
3125 and wcs.service_level = x_service_level )
3126 )
3127 and ( (wcs.mode_of_transport is null
3128 and x_mode_of_transport is null )
3129 or
3130 ( wcs.mode_of_transport is not null
3131 and wcs.mode_of_transport = x_mode_of_transport )
3132 );
3133 --
3134 cursor l_trip_car_shp_mthd_csr(p_trip_id IN NUMBER) is
3135 select carrier_id,
3136 service_level,
3137 mode_of_transport,
3138 ship_method_code
3139 from wsh_trips
3140 where trip_id = p_trip_id;
3141
3142
3143 -- Bug 4086855
3144 -- If the trip's mode is PARCEL or LTL, the trip can have at most two
3145 -- stops (linked stops are counted as one stop in this case).
3146
3147 cursor l_trip_stop_count(p_trip_id in number) is
3148 select count(*)
3149 from wsh_trip_stops s
3150 where s.trip_id = p_trip_id
3151 and s.physical_stop_id is null
3152 and rownum < 4;
3153
3154 --
3155 l_carrier_id NUMBER;
3156 l_service_level VARCHAR2(32767);
3157 l_mode_of_transport VARCHAR2(32767);
3158 l_ship_method_code VARCHAR2(32767);
3159 l_stop_count NUMBER;
3160 l_trip_name VARCHAR2(30);
3161 --
3162 BEGIN
3163 --
3164 --
3165 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3166 --
3167 IF l_debug_on IS NULL
3168 THEN
3169 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3170 END IF;
3171 --
3172 IF l_debug_on THEN
3173 --
3174 WSH_DEBUG_SV.push(l_module_name);
3175 --
3176 WSH_DEBUG_SV.log(l_module_name,'p_caller', p_caller);
3177 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_name', p_ship_method_name);
3178 WSH_DEBUG_SV.log(l_module_name,'x_ship_method_code', x_ship_method_code);
3179 WSH_DEBUG_SV.log(l_module_name,'p_carrier_name', p_carrier_name);
3180 WSH_DEBUG_SV.log(l_module_name,'x_carrier_id', x_carrier_id);
3181 WSH_DEBUG_SV.log(l_module_name,'x_service_level', x_service_level);
3182 WSH_DEBUG_SV.log(l_module_name,'x_mode_of_transport', x_mode_of_transport);
3183 WSH_DEBUG_SV.log(l_module_name,'p_organization_id', p_organization_id);
3184 WSH_DEBUG_SV.log(l_module_name,'p_entity_id', p_entity_id);
3185 WSH_DEBUG_SV.log(l_module_name,'p_entity_type', p_entity_type);
3186 --
3187 END IF;
3188 --
3189 IF p_entity_type IS NULL
3190 OR p_entity_type NOT IN ( 'TRIP','DLVY')
3191 THEN
3192 --
3193 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_ENTITY_TYPE');
3194 FND_MESSAGE.SET_TOKEN('ENT_TYPE', p_entity_type);
3195 x_return_status := wsh_util_core.g_ret_sts_error;
3196 wsh_util_core.add_message(x_return_status,l_module_name);
3197 RAISE FND_API.G_EXC_ERROR;
3198 --
3199 END IF;
3200 --
3201 --
3202 --bug 3616738 if caller is TP Release look it up
3203 -- 10374068: checking for other values also, since other attributes when NUll can make the return value indeterminate
3204 IF (p_caller IN ('WSH_FSTRX', 'WSH_TP_RELEASE')) AND (p_ship_method_name IS NULL)
3205 AND (x_carrier_id is not null and x_service_level is not null and x_mode_of_transport is not null) THEN
3206 IF p_entity_type = 'DLVY' THEN
3207 OPEN l_dlvy_carrier_services_csr;
3208 FETCH l_dlvy_carrier_services_csr INTO x_ship_method_code;
3209 CLOSE l_dlvy_carrier_services_csr;
3210 ELSE
3211 OPEN l_trip_carrier_services_csr;
3212 FETCH l_trip_carrier_services_csr INTO x_ship_method_code;
3213 CLOSE l_trip_carrier_services_csr;
3214 END IF;
3215 END IF;
3216
3217 IF p_entity_type = 'DLVY' THEN
3218 --
3219 open l_dlvy_car_shp_mthd_csr(p_entity_id);
3220 fetch l_dlvy_car_shp_mthd_csr into l_carrier_id,
3221 l_service_level,
3222 l_mode_of_transport,
3223 l_ship_method_code;
3224 close l_dlvy_car_shp_mthd_csr;
3225 --
3226 ELSE -- p_entity_type = 'TRIP' THEN
3227 --
3228 open l_trip_car_shp_mthd_csr(p_entity_id);
3229 fetch l_trip_car_shp_mthd_csr into l_carrier_id,
3230 l_service_level,
3231 l_mode_of_transport,
3232 l_ship_method_code;
3233 close l_trip_car_shp_mthd_csr;
3234 --
3235 END IF;
3236 --
3237 IF l_debug_on THEN
3238 --
3239 WSH_DEBUG_SV.log(l_module_name,'l_carrier_id', l_carrier_id);
3240 WSH_DEBUG_SV.log(l_module_name,'l_service_level', l_service_level);
3241 WSH_DEBUG_SV.log(l_module_name,'l_mode_of_transport', l_mode_of_transport);
3242 WSH_DEBUG_SV.log(l_module_name,'l_ship_method_code', l_ship_method_code);
3243 --
3244 END IF;
3245 --
3246 validate_ship_method(
3247 p_ship_method_code => x_ship_method_code,
3248 p_ship_method_name => p_ship_method_name,
3249 x_return_status => l_return_status);
3250 --
3251 IF (x_ship_method_code IS NULL ) THEN
3252 l_sm_is_null := TRUE;
3253 END IF;
3254 l_ship_method_code_backup := x_ship_method_code;
3255 --
3256 IF l_debug_on THEN
3257 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_ship_method',l_return_status);
3258 END IF;
3259 --
3260 WSH_UTIL_CORE.api_post_call(
3261 p_return_status => l_return_status,
3262 x_num_warnings => l_num_warnings,
3263 x_num_errors => l_num_errors);
3264 --
3265 validate_carrier(
3266 p_carrier_name => p_carrier_name,
3267 x_carrier_id => x_carrier_id,
3268 x_return_status => l_return_status);
3269 --
3270 IF l_debug_on THEN
3271 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_carrier',l_return_status);
3272 END IF;
3273 --
3274 WSH_UTIL_CORE.api_post_call(
3275 p_return_status => l_return_status,
3276 x_num_warnings => l_num_warnings,
3277 x_num_errors => l_num_errors);
3278 --
3279 -- If the either one of carrier, service level or mode of transport is changed
3280 -- we need to nullify the Ship Method to keep the Pubic API behaviour
3281 -- in SYNC with the STF.
3282 -- 10374068 : do not Nullify if these are New Values for entered ShipMethod attributes
3283 IF nvl(l_carrier_id,fnd_api.g_miss_num) <> nvl(x_carrier_id,fnd_api.g_miss_num)
3284 or nvl(l_service_level,fnd_api.g_miss_char) <> nvl(x_service_level,fnd_api.g_miss_char)
3285 or nvl(l_mode_of_transport,fnd_api.g_miss_char) <> nvl(x_mode_of_transport,fnd_api.g_miss_char)
3286 THEN
3287 -- 10374068
3288 IF ( p_caller = 'WSH_FSTRX' and (x_service_level is NULL or x_mode_of_transport is NULL))
3289 THEN
3290 IF l_debug_on THEN
3291 WSH_DEBUG_SV.logmsg(l_module_name,'Ship Method is not getting Nullified since Call From WSH_FSTRX ');
3292 END IF;
3293 ELSE
3294 IF l_debug_on THEN
3295 WSH_DEBUG_SV.logmsg(l_module_name,'Ship Method is being Nullified 1');
3296 END IF;
3297 x_ship_method_code := NULL;
3298 p_ship_method_name := NULL;
3299 END IF;
3300 END IF;
3301
3302 IF nvl(x_service_level, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
3303 --
3304 validate_lookup(
3305 p_lookup_type => 'WSH_SERVICE_LEVELS',
3306 p_lookup_code => x_service_level,
3307 p_meaning => l_dummy_meaning,
3308 x_return_status => l_return_status);
3309 --
3310 IF l_debug_on THEN
3311 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_lookup for service_level',l_return_status);
3312 END IF;
3313 --
3314 WSH_UTIL_CORE.api_post_call(
3315 p_return_status => l_return_status,
3316 x_num_warnings => l_num_warnings,
3317 x_num_errors => l_num_errors);
3318 --
3319 --
3320 END IF;
3321 --
3322 --
3323 IF nvl(x_mode_of_transport, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
3324 --
3325 validate_lookup(
3326 p_lookup_type => 'WSH_MODE_OF_TRANSPORT',
3327 p_lookup_code => x_mode_of_transport,
3328 p_meaning => l_dummy_meaning,
3329 x_return_status => l_return_status);
3330 --
3331 IF l_debug_on THEN
3332 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_lookup for mode_of_transport',l_return_status);
3333 END IF;
3334 --
3335 WSH_UTIL_CORE.api_post_call(
3336 p_return_status => l_return_status,
3337 x_num_warnings => l_num_warnings,
3338 x_num_errors => l_num_errors);
3339 --
3340 --
3341 END IF;
3342 --
3343 --
3344 IF l_debug_on THEN
3345 --
3346 WSH_DEBUG_SV.log(l_module_name,'x_ship_method_code', x_ship_method_code);
3347 WSH_DEBUG_SV.log(l_module_name,'x_carrier_id', x_carrier_id);
3348 WSH_DEBUG_SV.log(l_module_name,'x_service_level', x_service_level);
3349 WSH_DEBUG_SV.log(l_module_name,'x_mode_of_transport', x_mode_of_transport);
3350 --
3351 END IF;
3352 --
3353 IF l_sm_is_null = TRUE
3354 AND NVL(l_carrier_id,fnd_api.G_MISS_NUM) =
3355 NVL(x_carrier_id,fnd_api.G_MISS_NUM)
3356 AND NVL(l_service_level,fnd_api.G_MISS_CHAR) =
3357 NVL(x_service_level,fnd_api.G_MISS_CHAR)
3358 AND NVL(l_mode_of_transport,fnd_api.G_MISS_CHAR) =
3359 NVL(x_mode_of_transport,fnd_api.G_MISS_CHAR)
3360 THEN
3361 IF l_ship_method_code IS NOT NULL THEN
3362 p_ship_method_name := NULL;
3363 x_ship_method_code := NULL;
3364 x_carrier_id := NULL;
3365 x_service_level := NULL;
3366 x_mode_of_transport := NULL;
3367 END IF;
3368 -- Following Code is Commented for Bug 4000931
3369 -- Start of Comment for Bug 4000931
3370 /******
3371 ELSIF l_sm_is_null = FALSE
3372 AND NVL(l_carrier_id,fnd_api.G_MISS_NUM) =
3373 NVL(x_carrier_id,fnd_api.G_MISS_NUM)
3374 AND NVL(l_service_level,fnd_api.G_MISS_CHAR) =
3375 NVL(x_service_level,fnd_api.G_MISS_CHAR)
3376 AND NVL(l_mode_of_transport,fnd_api.G_MISS_CHAR) =
3377 NVL(x_mode_of_transport,fnd_api.G_MISS_CHAR)
3378 AND (WSH_UTIL_CORE.FTE_IS_INSTALLED <> 'Y') THEN
3379 --
3380 x_return_status := wsh_util_core.g_ret_sts_success;
3381 IF l_debug_on THEN
3382 --
3383 WSH_DEBUG_SV.logmsg(l_module_name,'FTE not installed' );
3384 WSH_DEBUG_SV.pop(l_module_name);
3385 END IF;
3386 RETURN;
3387 ******/
3388 -- End of Comment for Bug 4000931
3389 ELSIF nvl(x_ship_method_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char
3390 AND nvl(l_ship_method_code,fnd_api.g_miss_char) <>
3391 nvl(x_ship_method_code,fnd_api.g_miss_char)
3392 THEN
3393 --
3394 IF p_entity_type = 'DLVY' THEN
3395 --
3396 IF p_caller='WSH_TP_RELEASE' THEN
3397 open l_dlvy_ship_method_code_csr_tp;
3398 fetch l_dlvy_ship_method_code_csr_tp into x_carrier_id,
3399 x_service_level,
3400 x_mode_of_transport;
3401 --
3402 IF l_dlvy_ship_method_code_csr_tp%NOTFOUND THEN
3403 close l_dlvy_ship_method_code_csr_tp;
3404 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_SHIP_METHOD');
3405 x_return_status := wsh_util_core.g_ret_sts_error;
3406 wsh_util_core.add_message(x_return_status,l_module_name);
3407 RAISE FND_API.G_EXC_ERROR;
3408 END IF;
3409 --
3410 close l_dlvy_ship_method_code_csr_tp;
3411 ELSE
3412 open l_dlvy_ship_method_code_csr;
3413 fetch l_dlvy_ship_method_code_csr into x_carrier_id,
3414 x_service_level,
3415 x_mode_of_transport;
3416 --
3417 IF l_dlvy_ship_method_code_csr%NOTFOUND THEN
3418 close l_dlvy_ship_method_code_csr;
3419 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_SHIP_METHOD');
3420 x_return_status := wsh_util_core.g_ret_sts_error;
3421 wsh_util_core.add_message(x_return_status,l_module_name);
3422 RAISE FND_API.G_EXC_ERROR;
3423 END IF;
3424 --
3425 close l_dlvy_ship_method_code_csr;
3426 END IF;
3427 --
3428 ELSE -- p_entity_type = 'TRIP'
3429 --
3430 open l_trip_ship_method_code_csr;
3431 fetch l_trip_ship_method_code_csr into x_carrier_id,
3432 x_service_level,
3433 x_mode_of_transport;
3434 --
3435 IF l_trip_ship_method_code_csr%NOTFOUND THEN
3436 close l_trip_ship_method_code_csr;
3437 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_SHIP_METHOD');
3438 x_return_status := wsh_util_core.g_ret_sts_error;
3439 wsh_util_core.add_message(x_return_status,l_module_name);
3440 RAISE FND_API.G_EXC_ERROR;
3441 END IF;
3442 --
3443 close l_trip_ship_method_code_csr;
3444 --
3445 END IF;
3446 --
3447 ELSIF nvl(x_carrier_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3448 AND nvl(x_service_level,fnd_api.g_miss_char) <> fnd_api.g_miss_char
3449 AND nvl(x_mode_of_transport,fnd_api.g_miss_char) <> fnd_api.g_miss_char
3450 THEN
3451 --
3452 IF p_entity_type = 'DLVY' THEN
3453 --
3454 IF p_caller='WSH_TP_RELEASE' THEN
3455 open l_dlvy_carrier_services_csr_tp;
3456 fetch l_dlvy_carrier_services_csr_tp into x_ship_method_code;
3457 IF l_dlvy_carrier_services_csr_tp%NOTFOUND THEN
3458 close l_dlvy_carrier_services_csr_tp;
3459 FND_MESSAGE.SET_NAME('WSH','WSH_NO_SHIP_METHOD_FOR_CS');
3460 x_return_status := wsh_util_core.g_ret_sts_warning;
3461 wsh_util_core.add_message(x_return_status,l_module_name);
3462 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
3463 END IF;
3464 --
3465 close l_dlvy_carrier_services_csr_tp;
3466 ELSE
3467 open l_dlvy_carrier_services_csr;
3468 fetch l_dlvy_carrier_services_csr into x_ship_method_code;
3469 IF l_dlvy_carrier_services_csr%NOTFOUND THEN
3470 close l_dlvy_carrier_services_csr;
3471 FND_MESSAGE.SET_NAME('WSH','WSH_NO_SHIP_METHOD_FOR_CS');
3472 x_return_status := wsh_util_core.g_ret_sts_warning;
3473 wsh_util_core.add_message(x_return_status,l_module_name);
3474 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
3475 END IF;
3476 --
3477 close l_dlvy_carrier_services_csr;
3478 END IF;
3479 --
3480 ELSE -- p_entity_type = 'TRIP'
3481 --
3482 open l_trip_carrier_services_csr;
3483 fetch l_trip_carrier_services_csr into x_ship_method_code;
3484 IF l_trip_carrier_services_csr%NOTFOUND THEN
3485 close l_trip_carrier_services_csr;
3486 FND_MESSAGE.SET_NAME('WSH','WSH_NO_SHIP_METHOD_FOR_CS');
3487 x_return_status := wsh_util_core.g_ret_sts_warning;
3488 wsh_util_core.add_message(x_return_status,l_module_name);
3489 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
3490 END IF;
3491 --
3492 close l_trip_carrier_services_csr;
3493 --
3494 END IF;
3495 --
3496 validate_ship_method(
3497 p_ship_method_code => x_ship_method_code,
3498 p_ship_method_name => p_ship_method_name,
3499 x_return_status => l_return_status);
3500 --
3501 IF l_debug_on THEN
3502 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_ship_method Name',l_return_status);
3503 END IF;
3504 --
3505 WSH_UTIL_CORE.api_post_call(
3506 p_return_status => l_return_status,
3507 x_num_warnings => l_num_warnings,
3508 x_num_errors => l_num_errors);
3509 --
3510 -- 10374068 : do not invoke the following condition as incoming Svc. Level or
3511 -- Mode of Tpt. could be Null or in Database there could be more than 1 Carrier
3512 -- Service Recs. with Null in these two attributes.
3513 ELSIF ( nvl(x_carrier_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num )
3514 AND (x_service_level is NOT null and x_mode_of_transport is not null)
3515 THEN
3516 --
3517 IF p_entity_type = 'DLVY' THEN
3518 --
3519 open l_dlvy_carrier_services_csr;
3520 fetch l_dlvy_carrier_services_csr into x_ship_method_code;
3521 close l_dlvy_carrier_services_csr;
3522 --
3523 ELSE -- p_entity_type = 'TRIP'
3524 --
3525 open l_trip_carrier_services_csr;
3526 fetch l_trip_carrier_services_csr into x_ship_method_code;
3527 close l_trip_carrier_services_csr;
3528 --
3529 END IF;
3530 --
3531 validate_ship_method(
3532 p_ship_method_code => x_ship_method_code,
3533 p_ship_method_name => p_ship_method_name,
3534 x_return_status => l_return_status);
3535 --
3536 IF l_debug_on THEN
3537 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_ship_method Name',l_return_status);
3538 END IF;
3539 --
3540 WSH_UTIL_CORE.api_post_call(
3541 p_return_status => l_return_status,
3542 x_num_warnings => l_num_warnings,
3543 x_num_errors => l_num_errors);
3544 --
3545 END IF;
3546
3547 -- Bug 4086855
3548 -- If the trip's mode is PARCEL or LTL, the trip can have at most two
3549 -- stops (linked stops are counted as one stop in this case).
3550
3551 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y')
3552 AND (p_entity_type = 'TRIP') AND (x_mode_of_transport in ('LTL', 'PARCEL')) THEN
3553
3554 OPEN l_trip_stop_count(p_entity_id);
3555 FETCH l_trip_stop_count INTO l_stop_count;
3556 CLOSE l_trip_stop_count;
3557
3558 IF l_stop_count = 3 THEN
3559
3560 l_trip_name := WSH_TRIPS_PVT.Get_Name(p_entity_id);
3561 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_MOT_STOP_COUNT');
3562 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
3563 x_return_status := wsh_util_core.g_ret_sts_error;
3564 wsh_util_core.add_message(x_return_status,l_module_name);
3565 RAISE FND_API.G_EXC_ERROR;
3566
3567 END IF;
3568 END IF;
3569 --
3570
3571 --
3572 --
3573 -- Fix for bug 4310011(OTM R12, glog proj)
3574 -- Do not show this warning message for FTE
3575 IF p_caller NOT LIKE 'FTE%' THEN
3576
3577 IF NVL(l_ship_method_code_backup,fnd_api.G_MISS_CHAR) <>
3578 NVL(x_ship_method_code,fnd_api.G_MISS_CHAR) THEN
3579 --
3580 IF l_debug_on THEN
3581 wsh_debug_sv.logmsg(l_module_name,'Ship method calculated is different');
3582 END IF;
3583 FND_MESSAGE.SET_NAME('WSH','WSH_SHIP_METHOD_CHANGE');
3584 FND_MESSAGE.SET_TOKEN('ORIG_SM', l_ship_method_code_backup);
3585 FND_MESSAGE.SET_TOKEN('CALC_SM', x_ship_method_code);
3586 x_return_status := wsh_util_core.g_ret_sts_warning;
3587 wsh_util_core.add_message(x_return_status,l_module_name);
3588 END IF;
3589
3590 END IF;
3591
3592 x_return_status := wsh_util_core.g_ret_sts_success;
3593 --
3594 IF l_debug_on THEN
3595 WSH_DEBUG_SV.pop(l_module_name);
3596 END IF;
3597 --
3598 EXCEPTION
3599 --
3600 WHEN FND_API.G_EXC_ERROR THEN
3601 --
3602 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3603 IF l_debug_on THEN
3604 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3605 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3606 END IF;
3607 --
3608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3609 --
3610 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3611 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_FREIGHT_CARRIER');
3612 IF l_debug_on THEN
3613 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3614 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3615 END IF;
3616 --
3617 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3618 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3619 --
3620 IF l_debug_on THEN
3621 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3622 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNIN
3623 G');
3624 END IF;
3625 --
3626 WHEN OTHERS THEN
3627 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3628 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.VALIDATE_FREIGHT_CARRIER');
3629 --
3630 IF l_debug_on THEN
3631 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3632 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3633 END IF;
3634 END Validate_Freight_Carrier;
3635 -- I Harmonization: rvishnuv *******
3636
3637 -- ---------------------------------------------------------------------
3638 -- Procedure: Find_Item_Type
3639 --
3640 -- Parameters:
3641 --
3642 -- Description: This procedure gives the item type (either container_item or vehicle_item) for the given
3643 -- inventory item id and organization id.
3644 -- Created: Harmonization Project. Patchset I. KVENKATE
3645 -- -----------------------------------------------------------------------
3646 PROCEDURE Find_Item_Type(
3647 p_inventory_item_id IN NUMBER,
3648 p_organization_id IN NUMBER,
3649 x_item_type OUT NOCOPY VARCHAR2,
3650 x_return_status OUT NOCOPY VARCHAR2)
3651
3652 IS
3653
3654 l_container_item_flag VARCHAR2(1);
3655 l_shippable_flag VARCHAR2(1);
3656 l_vehicle_item_flag VARCHAR2(1);
3657 l_wms_org VARCHAR2(1) := 'N';
3658
3659 CURSOR veh_cont_item_cur(l_item_id NUMBER, l_organization_id NUMBER) IS
3660 SELECT container_item_flag, shippable_item_flag, vehicle_item_flag
3661 FROM mtl_system_items
3662 WHERE inventory_item_id = l_item_id AND
3663 organization_id = l_organization_id;
3664
3665 --
3666 l_debug_on BOOLEAN;
3667 --
3668 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_ITEM_TYPE';
3669 --
3670
3671 BEGIN
3672
3673 --
3674 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3675 --
3676 IF l_debug_on IS NULL
3677 THEN
3678 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3679 END IF;
3680 --
3681 IF l_debug_on THEN
3682 WSH_DEBUG_SV.push(l_module_name);
3683 --
3684 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
3685 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
3686 END IF;
3687 --
3688 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3689
3690 OPEN veh_cont_item_cur(p_inventory_item_id, p_organization_id);
3691 FETCH veh_cont_item_cur INTO
3692 l_container_item_flag, l_shippable_flag, l_vehicle_item_flag;
3693 IF veh_cont_item_cur%NOTFOUND THEN
3694 CLOSE veh_cont_item_cur;
3695 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ITEM');
3696 RAISE FND_API.G_EXC_ERROR;
3697 END IF;
3698 CLOSE veh_cont_item_cur;
3699
3700 l_wms_org := wsh_util_validate.Check_Wms_Org(p_organization_id);
3701
3702 IF l_debug_on THEN
3703 WSH_DEBUG_SV.log(l_module_name,'l_wms_org',l_wms_org);
3704 END IF;
3705
3706 IF(nvl(l_vehicle_item_flag, 'N') = 'Y') THEN
3707 x_item_type := 'VEH_ITEM';
3708 ELSIF ( l_container_item_flag = 'Y'
3709 AND NVL(l_vehicle_item_flag, 'N') = 'N'
3710 AND (( l_shippable_flag = 'Y' AND l_wms_org = 'N' )
3711 OR (l_wms_org = 'Y'))) THEN
3712 x_item_type := 'CONT_ITEM';
3713 END IF;
3714
3715 IF l_debug_on THEN
3716 WSH_DEBUG_SV.pop(l_module_name);
3717 END IF;
3718 --
3719
3720 EXCEPTION
3721 WHEN FND_API.G_EXC_ERROR THEN
3722 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3723 wsh_util_core.add_message(x_return_status,l_module_name);
3724 IF l_debug_on THEN
3725 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3726 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3727 END IF;
3728
3729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3730 --
3731 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3732 IF l_debug_on THEN
3733 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3734 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3735 END IF;
3736
3737 WHEN OTHERS THEN
3738 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3739 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.FIND_ITEM_TYPE');
3740 --
3741 IF l_debug_on THEN
3742 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3743 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3744 END IF;
3745
3746 END Find_Item_Type;
3747 -- I Harmonization: KVENKATE
3748
3749 FUNCTION Get_Org_Type (
3750 p_organization_id IN NUMBER,
3751 p_event_key IN VARCHAR2 DEFAULT NULL,
3752 p_delivery_id IN NUMBER DEFAULT NULL,
3753 p_delivery_detail_id IN NUMBER DEFAULT NULL,
3754 p_msg_display IN VARCHAR2 DEFAULT 'Y',
3755 x_return_status OUT NOCOPY VARCHAR2
3756 ) RETURN VARCHAR2
3757 IS
3758 --
3759 l_return_status VARCHAR2(1);
3760 l_wms_installed VARCHAR2(10);
3761 l_org_type VARCHAR2(32767);
3762 l_org_type_wms VARCHAR2(32767);
3763 --
3764 l_debug_on BOOLEAN;
3765 --
3766 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ORG_TYPE';
3767 --
3768 BEGIN
3769 --
3770 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3771 --
3772 IF l_debug_on IS NULL
3773 THEN
3774 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3775 END IF;
3776 --
3777 IF l_debug_on THEN
3778 wsh_debug_sv.push(l_module_name, 'Get_Org_Type');
3779 wsh_debug_sv.log (l_module_name,'Organization id', p_organization_id);
3780 END IF;
3781
3782 l_org_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type (
3783 p_organization_id => p_organization_id,
3784 p_event_key => p_event_key,
3785 x_return_status => l_return_status,
3786 p_delivery_id => p_delivery_id,
3787 p_delivery_detail_id => p_delivery_detail_id,
3788 p_msg_display => p_msg_display);
3789
3790 IF l_return_status = wsh_util_core.g_ret_sts_error THEN
3791 raise FND_API.G_EXC_ERROR;
3792 ELSIF l_return_status = wsh_util_core.g_ret_sts_unexp_error THEN
3793 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3794 END IF;
3795
3796 l_wms_installed := wsh_util_validate.Check_Wms_Org(p_organization_id);
3797
3798 IF l_wms_installed = 'Y' THEN
3799 l_org_type_wms := 'WMS';
3800 END IF;
3801
3802 IF l_debug_on THEN
3803 wsh_debug_sv.log (l_module_name,'l_org_type', l_org_type);
3804 wsh_debug_sv.log (l_module_name,'l_org_type_wms', l_org_type_wms);
3805 END IF;
3806
3807 IF l_org_type IS NULL THEN
3808 l_org_type := l_org_type_wms;
3809 ELSIF l_org_type_wms IS NOT NULL THEN
3810 l_org_type := l_org_type || '.' || l_org_type_wms;
3811 END IF;
3812
3813 x_return_status := wsh_util_core.g_ret_sts_success;
3814
3815 IF l_debug_on THEN
3816 wsh_debug_sv.pop(l_module_name, 'Get_Org_Type');
3817 END IF;
3818
3819 RETURN l_org_type;
3820 EXCEPTION
3821 WHEN FND_API.G_EXC_ERROR THEN
3822 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3823 --
3824 IF l_debug_on THEN
3825 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3826 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3827 END IF;
3828 RETURN l_org_type;
3829 --
3830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3831 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3832 --
3833 IF l_debug_on THEN
3834 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3836 END IF;
3837 RETURN l_org_type;
3838 --
3839 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3840 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3841 --
3842 IF l_debug_on THEN
3843 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3844 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3845 END IF;
3846 RETURN l_org_type;
3847 --
3848 WHEN OTHERS THEN
3849 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3850 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.GET_ORG_TYPE');
3851 --
3852 IF l_debug_on THEN
3853 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3854 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3855 END IF;
3856 RETURN l_org_type;
3857 --
3858 END Get_Org_Type;
3859
3860
3861 --========================================================================
3862 -- PROCEDURE : get_item_info
3863 --
3864 -- PARAMETERS: p_organization_id Item's Organization Id
3865 -- p_inventory_item_id Inventory Item Id
3866 -- x_Item_info_rec stores the item information
3867 -- x_return_status return status
3868 -- COMMENT : This API manages a cache, which contains item information
3869 -- The information on the cached is retrieved based on the
3870 -- organization id and inventory id. If this information does not
3871 -- exist in the cache, it will be queried and added to it.
3872 -- If there is a collision in the cache, then the new information
3873 -- will be retrieved and will replace the old ones
3874 --========================================================================
3875
3876
3877 PROCEDURE get_item_info (
3878 p_organization_id IN NUMBER,
3879 p_inventory_item_id IN NUMBER,
3880 x_Item_info_rec OUT NOCOPY
3881 item_info_rec_type,
3882 x_return_status OUT NOCOPY VARCHAR2)
3883 IS
3884
3885 --
3886 l_debug_on BOOLEAN;
3887 --
3888 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
3889 'GET_ITEM_INFO';
3890 --
3891 l_hash_string VARCHAR2(1000);
3892 l_hash_value NUMBER;
3893 l_Item_info_rec item_info_rec_type;
3894 l_cache_hit BOOLEAN := FALSE;
3895
3896 CURSOR c_inventory_item_info(v_inventory_item_id number,
3897 v_organization_id number) is
3898 SELECT primary_uom_code,
3899 description,
3900 hazard_class_id,
3901 weight_uom_code,
3902 unit_weight,
3903 volume_uom_code,
3904 unit_volume
3905 FROM mtl_system_items
3906 WHERE inventory_item_id = v_inventory_item_id
3907 AND organization_id = v_organization_id;
3908
3909 BEGIN
3910 --
3911 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3912 --
3913 IF l_debug_on IS NULL THEN
3914 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3915 END IF;
3916 --
3917 IF l_debug_on THEN
3918 WSH_DEBUG_SV.push(l_module_name);
3919 --
3920 WSH_DEBUG_SV.log(l_module_name,'p_organization_id',p_organization_id);
3921 WSH_DEBUG_SV.log(l_module_name,'p_inventory_item_id',
3922 p_inventory_item_id);
3923 END IF;
3924 --
3925 IF (p_organization_id IS NULL ) OR (p_inventory_item_id IS NULL )THEN
3926 RAISE FND_API.G_EXC_ERROR;
3927 END IF;
3928 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3929
3930 l_hash_string := p_organization_id || '-'||p_inventory_item_id;
3931 l_hash_value := dbms_utility.get_hash_value(
3932 name => l_hash_string,
3933 base => c_hash_base,
3934 hash_size =>c_hash_size );
3935
3936 IF G_ITEM_INFO_TAB.exists(l_hash_value) THEN --{
3937 IF G_ITEM_INFO_TAB(l_hash_value).organization_id = p_organization_id
3938 AND G_ITEM_INFO_TAB(l_hash_value).inventory_item_id
3939 = p_inventory_item_id
3940 THEN --{
3941 x_Item_info_rec := G_ITEM_INFO_TAB(l_hash_value);
3942 l_cache_hit := TRUE;
3943
3944 IF l_debug_on THEN
3945 WSH_DEBUG_SV.logmsg(l_module_name,'hit ');
3946 END IF;
3947
3948 END IF; --}
3949
3950 END IF; --}
3951
3952 IF NOT l_cache_hit THEN --{
3953 OPEN c_inventory_item_info(p_inventory_item_id, p_organization_id);
3954 FETCH c_inventory_item_info INTO
3955 l_Item_info_rec.primary_uom_code,
3956 l_Item_info_rec.description,
3957 l_Item_info_rec.hazard_class_id,
3958 l_Item_info_rec.weight_uom_code,
3959 l_Item_info_rec.unit_weight,
3960 l_Item_info_rec.volume_uom_code,
3961 l_Item_info_rec.unit_volume;
3962
3963 IF c_inventory_item_info%NOTFOUND THEN
3964 CLOSE c_inventory_item_info;
3965 RAISE FND_API.G_EXC_ERROR;
3966 END IF;
3967
3968 CLOSE c_inventory_item_info;
3969
3970 l_Item_info_rec.organization_id := p_organization_id;
3971 l_Item_info_rec.inventory_item_id := p_inventory_item_id;
3972
3973 x_Item_info_rec := l_Item_info_rec;
3974 G_ITEM_INFO_TAB(l_hash_value) := l_Item_info_rec;
3975
3976 END IF; --}
3977
3978 IF l_debug_on THEN
3979 WSH_DEBUG_SV.log(l_module_name, 'primary_uom_code',
3980 x_Item_info_rec.primary_uom_code);
3981 WSH_DEBUG_SV.log(l_module_name, 'description',
3982 x_Item_info_rec.description);
3983 WSH_DEBUG_SV.log(l_module_name, 'hazard_class_id',
3984 x_Item_info_rec.hazard_class_id);
3985 WSH_DEBUG_SV.log(l_module_name, 'weight_uom_code',
3986 x_Item_info_rec.weight_uom_code);
3987 WSH_DEBUG_SV.log(l_module_name, 'unit_weight',
3988 x_Item_info_rec.unit_weight);
3989 WSH_DEBUG_SV.log(l_module_name, 'volume_uom_code',
3990 x_Item_info_rec.volume_uom_code);
3991 WSH_DEBUG_SV.log(l_module_name, 'unit_volume',
3992 x_Item_info_rec.unit_volume);
3993 WSH_DEBUG_SV.pop(l_module_name);
3994 END IF;
3995 EXCEPTION
3996
3997 WHEN FND_API.G_EXC_ERROR THEN
3998 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3999 --
4000 IF l_debug_on THEN
4001 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4002 || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4003 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4004 END IF;
4005 --
4006 WHEN OTHERS THEN
4007 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4008 wsh_util_core.default_handler('WSH_UTIL_CORE.GET_ITEM_INFO');
4009 --
4010 IF l_debug_on THEN
4011 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4012 'Oracle error message is '||
4013 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4014 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4015 END IF;
4016
4017
4018 END get_item_info;
4019
4020 --========================================================================
4021 -- PROCEDURE : Default_container
4022 --
4023 -- PARAMETERS: p_item_id Item's Organization Id
4024 -- x_master_container_item_id default value for master container
4025 -- x_detail_container_item_id default value for detail container
4026 -- x_return_status return status
4027 -- COMMENT : This API calculates the default value for the fields
4028 -- detail_container_item_id and master_container_item_id. It then
4029 -- caches these values for future calls.
4030 --========================================================================
4031
4032 PROCEDURE Default_container (
4033 p_item_id IN NUMBER,
4034 x_master_container_item_id OUT NOCOPY NUMBER,
4035 x_detail_container_item_id OUT NOCOPY NUMBER,
4036 x_return_status OUT NOCOPY VARCHAR2)
4037 IS
4038 l_debug_on BOOLEAN;
4039 --
4040 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
4041 || 'DEFAULT_CONTAINER';
4042 l_cache_hit BOOLEAN := FALSE;
4043 i NUMBER;
4044
4045 CURSOR c_default_container(l_customer_item_id NUMBER) IS
4046 SELECT master_container_item_id,
4047 detail_container_item_id
4048 FROM mtl_customer_items
4049 WHERE customer_item_id = l_customer_item_id;
4050 --
4051
4052 BEGIN
4053 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4054 --
4055 IF l_debug_on IS NULL THEN
4056 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4057 END IF;
4058 --
4059 IF l_debug_on THEN
4060 WSH_DEBUG_SV.push(l_module_name);
4061 --
4062 WSH_DEBUG_SV.log(l_module_name,'p_item_id',p_item_id);
4063 END IF;
4064 --
4065 IF p_item_id IS NULL THEN
4066 RAISE FND_API.G_EXC_ERROR;
4067 END IF;
4068 --
4069 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4070
4071 IF p_item_id < C_IDX_LIMT THEN --{
4072
4073 IF G_DEF_CONT_INF_TAB.EXISTS(p_item_id) THEN
4074 x_master_container_item_id :=
4075 G_DEF_CONT_INF_TAB(p_item_id).master_container_item_id;
4076 x_detail_container_item_id :=
4077 G_DEF_CONT_INF_TAB(p_item_id).detail_container_item_id;
4078 l_cache_hit := TRUE;
4079
4080 END IF;
4081 ELSE --}{
4082
4083 i := G_DEF_CONT_INFO_EXT_TAB.FIRST;
4084 WHILE i IS NOT NULL LOOP
4085 IF G_DEF_CONT_INFO_EXT_TAB(i).key = p_item_id THEN
4086 l_cache_hit := TRUE;
4087 x_master_container_item_id :=
4088 G_DEF_CONT_INFO_EXT_TAB(i).master_container_item_id;
4089 x_detail_container_item_id :=
4090 G_DEF_CONT_INFO_EXT_TAB(i).detail_container_item_id;
4091 EXIT;
4092 END IF;
4093 i := G_DEF_CONT_INFO_EXT_TAB.NEXT(i);
4094 END LOOP;
4095
4096 END IF; --}
4097 IF NOT l_cache_hit THEN --{
4098
4099 OPEN c_default_container(p_item_id);
4100 FETCH c_default_container INTO x_master_container_item_id,
4101 x_detail_container_item_id;
4102 CLOSE c_default_container;
4103
4104 IF p_item_id < C_IDX_LIMT THEN
4105 G_DEF_CONT_INF_TAB(p_item_id).master_container_item_id :=
4106 x_master_container_item_id;
4107 G_DEF_CONT_INF_TAB(p_item_id).detail_container_item_id :=
4108 x_detail_container_item_id;
4109 ELSE
4110 i := G_DEF_CONT_INFO_EXT_TAB.COUNT;
4111 G_DEF_CONT_INFO_EXT_TAB(i+1).master_container_item_id :=
4112 x_master_container_item_id;
4113 G_DEF_CONT_INFO_EXT_TAB(i+1).detail_container_item_id :=
4114 x_detail_container_item_id;
4115 G_DEF_CONT_INFO_EXT_TAB(i+1).key := p_item_id;
4116 END IF;
4117 ELSE --}{
4118 IF l_debug_on THEN
4119 --
4120 WSH_DEBUG_SV.logmsg(l_module_name,'hit ');
4121 --
4122 END IF;
4123 END IF; --}
4124
4125
4126 IF l_debug_on THEN
4127 WSH_DEBUG_SV.log(l_module_name, 'x_master_container_item_id',
4128 x_master_container_item_id);
4129 WSH_DEBUG_SV.log(l_module_name, 'x_detail_container_item_id',
4130 x_detail_container_item_id);
4131 WSH_DEBUG_SV.pop(l_module_name);
4132 END IF;
4133
4134 EXCEPTION
4135 WHEN FND_API.G_EXC_ERROR THEN
4136 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4137 --
4138 IF l_debug_on THEN
4139 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4140 || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4141 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4142 END IF;
4143 --
4144
4145 WHEN OTHERS THEN
4146 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4147 wsh_util_core.default_handler('WSH_UTIL_CORE.DEFAULT_CONTAINER');
4148 --
4149 IF l_debug_on THEN
4150 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4151 'Oracle error message is '||
4152 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4153 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4154 END IF;
4155
4156 END Default_container;
4157
4158
4159 --========================================================================
4160 -- PROCEDURE : Calc_ignore_for_planning
4161 --
4162 -- PARAMETERS: p_organization_id
4163 -- p_carrier_id
4164 -- p_ship_method_code
4165 -- p_tp_installed
4166 -- x_return_status return status
4167 -- p_otm_installed optional parameter to pass shipping
4168 -- parameter OTM_INSTALLED
4169 -- p_client_id clientId value. Consider OTM enabled value on Client.
4170 --
4171 -- COMMENT : This procedure calulates the value for the field
4172 -- ignore_for_planning_flag
4173 -- : Added the new parameter p_otm_installed.
4174 --========================================================================
4175
4176 PROCEDURE Calc_ignore_for_planning(
4177 p_organization_id IN NUMBER,
4178 p_carrier_id IN NUMBER,
4179 p_ship_method_code IN VARCHAR2,
4180 p_tp_installed IN VARCHAR2,
4181 p_caller IN VARCHAR2,
4182 x_ignore_for_planning OUT NOCOPY VARCHAR2,
4183 x_return_status OUT NOCOPY VARCHAR2,
4184 p_otm_installed IN VARCHAR2, --OTM R12 Org-Specific
4185 p_client_id IN NUMBER DEFAULT NULL) -- LSP PROJECT
4186 IS
4187 l_debug_on BOOLEAN;
4188 --
4189 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME ||
4190 '.' || 'CALC_IGNORE_FOR_PLANNING';
4191 l_hash_string VARCHAR2(1000);
4192 l_hash_value NUMBER;
4193 l_wh_type VARCHAR2(100);
4194 l_return_status VARCHAR2(1);
4195 l_hit boolean := FALSE;
4196 --OTM R12 Org-Specific
4197 l_shipping_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
4198
4199 BEGIN
4200
4201 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4202 IF l_debug_on IS NULL
4203 THEN
4204 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4205 END IF;
4206 --
4207 IF l_debug_on THEN
4208 wsh_debug_sv.push (l_module_name);
4209 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id',p_organization_id);
4210 WSH_DEBUG_SV.log(l_module_name, 'p_carrier_id',p_carrier_id);
4211 WSH_DEBUG_SV.log(l_module_name, 'p_ship_method_code',p_ship_method_code);
4212 WSH_DEBUG_SV.log(l_module_name, 'p_ship_method_code',p_ship_method_code);
4213 WSH_DEBUG_SV.log(l_module_name, 'p_caller',p_caller);
4214 WSH_DEBUG_SV.log(l_module_name, 'p_tp_installed',p_tp_installed);
4215 WSH_DEBUG_SV.log(l_module_name, 'p_otm_installed',p_otm_installed); --OTM R12 Org-Specific
4216 WSH_DEBUG_SV.log(l_module_name, 'p_client_id',p_client_id); -- LSP PROJECT
4217 END IF;
4218 --
4219 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4220 IF p_caller = 'PO' THEN --{
4221 x_ignore_for_planning := 'Y';
4222 ELSIF p_otm_installed = 'Y' then --{ --OTM R12 Start Org-Specific
4223 --{
4224 WSH_SHIPPING_PARAMS_PVT.Get(
4225 p_organization_id => p_organization_id,
4226 p_client_id => p_client_id, -- LSP PROJECT
4227 x_param_info => l_shipping_param_info,
4228 x_return_status => l_return_status);
4229 IF l_debug_on THEN
4230 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PARAMS_PVT.Get l_return_status '
4231 ,l_return_status);
4232 WSH_DEBUG_SV.log(l_module_name,'l_shipping_param_info.otm_enabled '
4233 ,l_shipping_param_info.otm_enabled);
4234 END IF;
4235 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,
4236 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4237 Raise FND_API.G_EXC_ERROR;
4238 END IF;
4239 IF l_shipping_param_info.otm_enabled = 'Y' THEN
4240 x_ignore_for_planning := 'N';
4241 ELSE
4242 x_ignore_for_planning := 'Y';
4243 END IF;
4244
4245 --} --OTM R12 End
4246 ELSIF p_tp_installed = 'Y'THEN --}{
4247 l_hash_string := p_organization_id ||'-'|| p_carrier_id||
4248 '-'|| p_ship_method_code ;
4249 l_hash_value := dbms_utility.get_hash_value(
4250 name => l_hash_string,
4251 base => c_hash_base,
4252 hash_size =>c_hash_size );
4253
4254 IF l_debug_on THEN
4255 WSH_DEBUG_SV.log(l_module_name,'l_hash_value ',l_hash_value);
4256 END IF;
4257 IF G_IGNORE_PLAN_TAB.EXISTS(l_hash_value) THEN --{
4258 IF G_IGNORE_PLAN_TAB(l_hash_value).organization_id = p_organization_id
4259 AND NVL(G_IGNORE_PLAN_TAB(l_hash_value).carrier_id,-22) =
4260 NVL(p_carrier_id, -22)
4261 AND NVL(G_IGNORE_PLAN_TAB(l_hash_value).ship_method_code, -22) =
4262 NVL(p_ship_method_code,-22)
4263 THEN --{
4264 x_ignore_for_planning :=
4265 G_IGNORE_PLAN_TAB(l_hash_value).ignore_for_planning;
4266 l_hit := TRUE;
4267 IF l_debug_on THEN
4268 WSH_DEBUG_SV.log(l_module_name,'hit ',x_ignore_for_planning);
4269 END IF;
4270 END IF; --}
4271 END IF; --}
4272 IF (NOT l_hit) THEN --{
4273 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
4274 (p_organization_id => p_organization_id,
4275 x_return_status => l_return_status,
4276 p_carrier_id => p_carrier_id,
4277 p_ship_method_code => p_ship_method_code,
4278 p_msg_display => 'N'
4279 );
4280 IF l_debug_on THEN
4281 WSH_DEBUG_SV.log(l_module_name,'l_wh_type,l_return_status',
4282 l_wh_type||l_return_status);
4283 END IF;
4284 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS')) THEN
4285 x_ignore_for_planning:='Y';
4286 ELSE
4287 x_ignore_for_planning:='N';
4288 END IF;
4289 G_IGNORE_PLAN_TAB(l_hash_value).ignore_for_planning :=
4290 x_ignore_for_planning;
4291 G_IGNORE_PLAN_TAB(l_hash_value).organization_id :=
4292 p_organization_id;
4293 G_IGNORE_PLAN_TAB(l_hash_value).carrier_id := p_carrier_id;
4294 G_IGNORE_PLAN_TAB(l_hash_value).ship_method_code :=
4295 p_ship_method_code;
4296
4297 END IF; --}
4298 ELSE --}{
4299 x_ignore_for_planning := 'N';
4300 END IF; --}
4301 IF l_debug_on THEN
4302 WSH_DEBUG_SV.pop(l_module_name,x_ignore_for_planning);
4303 END IF;
4304 EXCEPTION
4305 WHEN FND_API.G_EXC_ERROR THEN --{ --OTM R12 Start
4306 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4307 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
4308 IF l_debug_on THEN
4309 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has '
4310 || 'occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4311 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4312 END IF; --} --OTM R12 End
4313 WHEN OTHERS THEN
4314 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4315 wsh_util_core.default_handler('WSH_UTIL_CORE.DEFAULT_CONTAINER');
4316 --
4317 IF l_debug_on THEN
4318 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. '||
4319 'Oracle error message is '||
4320 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4321 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4322 END IF;
4323 END Calc_ignore_for_planning;
4324
4325 -- Added for Inbound Logistics
4326 -- Start of comments
4327 -- API name : VALIDATE_FOB
4328 -- Type : Public
4329 -- Pre-reqs : None.
4330 -- Function : This API is used to check whether the FOB supplied as the
4331 -- parameter is present in the AR lookups.If it is not present
4332 -- it creates the FOB under AR lookups.
4333 -- Lookup type is 'FOB' in AR_LOOKUPS
4334 -- Parameters :
4335 -- IN:
4336 -- p_fob IN VARCHAR2
4337 -- IN OUT:
4338 -- OUT:
4339 -- x_return_status OUT NOCOPY VARCHAR2
4340 -- Version : 1.0
4341 -- Previous version 1.0
4342 -- Initial version 1.0
4343 -- End of comments
4344
4345 PROCEDURE validate_fob(
4346 p_fob IN VARCHAR2,
4347 x_return_status OUT NOCOPY VARCHAR2) IS
4348
4349 --Cursor to determine whether the given look up code is present in ar lookups.
4350 Cursor c_fob(p_lookup_code VARCHAR2) IS
4351 SELECT 1
4352 FROM
4353 ar_lookups
4354 WHERE
4355 lookup_type = 'FOB' AND
4356 lookup_code = p_lookup_code AND
4357 nvl(start_date_active,SYSDATE) <= SYSDATE AND
4358 nvl(end_date_active,SYSDATE) >= SYSDATE AND
4359 enabled_flag = 'Y';
4360
4361 --Cursor to get the meaning and description of a look up code.
4362 Cursor c_po_fob (p_lookup_code VARCHAR2) IS
4363 SELECT meaning,description
4364 from FND_LOOKUP_VALUES_VL
4365 where lookup_code = p_lookup_code AND
4366 lookup_type = 'FOB' AND
4367 nvl(start_date_active,SYSDATE) <= SYSDATE AND
4368 nvl(end_date_active,SYSDATE) >= SYSDATE AND
4369 enabled_flag = 'Y'AND
4370 view_application_id = 201;
4371
4372 l_fob NUMBER;
4373 l_meaning VARCHAR2(80);
4374 l_desc VARCHAR2(240);
4375 l_rowid VARCHAR2(30);
4376
4377 --
4378 l_debug_on BOOLEAN;
4379 --
4380 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FOB';
4381 --
4382 BEGIN
4383 --
4384 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4385 --
4386 IF l_debug_on IS NULL
4387 THEN
4388 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4389 END IF;
4390 --
4391 --
4392 -- Debug Statements
4393 --
4394 IF l_debug_on THEN
4395 WSH_DEBUG_SV.push(l_module_name);
4396 --
4397 WSH_DEBUG_SV.log(l_module_name,'P_FOB',P_FOB);
4398 END IF;
4399 --
4400 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4401
4402 OPEN c_fob(p_fob);
4403 FETCH c_fob INTO l_fob;
4404
4405 -- If True -> the given FOB is not in AR lokups. Then it gets the details of the FOB
4406 -- from FND lookups and inserts the same into AR lookups.
4407 IF c_fob%NOTFOUND THEN
4408 OPEN c_po_fob(p_fob);
4409 FETCH c_po_fob INTO l_meaning,l_desc;
4410 CLOSE c_po_fob;
4411
4412 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
4413 X_ROWID => l_rowid,
4414 X_LOOKUP_TYPE => 'FOB',
4415 X_SECURITY_GROUP_ID => 0,
4416 X_VIEW_APPLICATION_ID => 222,
4417 X_LOOKUP_CODE => p_fob,
4418 X_TAG => NULL,
4419 X_ATTRIBUTE_CATEGORY => NULL,
4420 X_ATTRIBUTE1 => NULL,
4421 X_ATTRIBUTE2 => NULL,
4422 X_ATTRIBUTE3 => NULL,
4423 X_ATTRIBUTE4 => NULL,
4424 X_ENABLED_FLAG => 'Y',
4425 X_START_DATE_ACTIVE => SYSDATE,
4426 X_END_DATE_ACTIVE => NULL,
4427 X_TERRITORY_CODE => NULL,
4428 X_ATTRIBUTE5 => NULL,
4429 X_ATTRIBUTE6 => NULL,
4430 X_ATTRIBUTE7 => NULL,
4431 X_ATTRIBUTE8 => NULL,
4432 X_ATTRIBUTE9 => NULL,
4433 X_ATTRIBUTE10 => NULL,
4434 X_ATTRIBUTE11 => NULL,
4435 X_ATTRIBUTE12 => NULL,
4436 X_ATTRIBUTE13 => NULL,
4437 X_ATTRIBUTE14 => NULL,
4438 X_ATTRIBUTE15 => NULL,
4439 X_MEANING => l_meaning,
4440 X_DESCRIPTION => l_desc,
4441 X_CREATION_DATE => SYSDATE,
4442 X_CREATED_BY => FND_GLOBAL.USER_ID,
4443 X_LAST_UPDATE_DATE => SYSDATE,
4444 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
4445 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
4446 END IF;
4447 CLOSE c_fob;
4448
4449 --
4450 -- Debug Statements
4451 --
4452 IF l_debug_on THEN
4453 WSH_DEBUG_SV.pop(l_module_name);
4454 END IF;
4455 --
4456 EXCEPTION
4457 WHEN OTHERS THEN
4458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4459 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.VALIDATE_FOB',l_module_name);
4460 --
4461 -- Debug Statements
4462 --
4463 IF l_debug_on THEN
4464 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4465 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4466 END IF;
4467 --
4468 END validate_fob;
4469
4470 -- Start of comments
4471 -- API name : VALIDATE_FREIGHT_TERMS
4472 -- Type : Public
4473 -- Pre-reqs : None.
4474 -- Function : This API is used to check whether the freight terms supplied
4475 -- in the parameters is present in the ONT lookups.If it is not
4476 -- present it creates the same under ONT lookups and allow the
4477 -- delivery details to be updated.
4478 -- Lookup type is 'FREIGHT_TERMS' in ONT_LOOKUPS
4479 -- Parameters :
4480 -- IN:
4481 -- p_freight_terms_code IN VARCHAR2
4482 -- IN OUT:
4483 -- OUT:
4484 -- x_return_status OUT NOCOPY VARCHAR2
4485 -- Version : 1.0
4486 -- Previous version 1.0
4487 -- Initial version 1.0
4488 -- End of comments
4489
4490 PROCEDURE validate_freight_terms(
4491 p_freight_terms_code IN VARCHAR2,
4492 x_return_status OUT NOCOPY VARCHAR2 ) IS
4493
4494 --Cursor to determine whether the given look up code is present in oe lookups.
4495 Cursor c_freight(p_lookup_code VARCHAR2) IS
4496 SELECT '1'
4497 FROM oe_lookups
4498 WHERE
4499 lookup_type = 'FREIGHT_TERMS' AND
4500 lookup_code = p_lookup_code AND
4501 nvl(start_date_active,SYSDATE) <= SYSDATE
4502 AND nvl(end_date_active,SYSDATE) >= SYSDATE
4503 AND enabled_flag = 'Y';
4504
4505 --Cursor to get the meaning and description of a look up code.
4506 Cursor c_po_freight (p_lookup_code VARCHAR2) IS
4507 SELECT meaning,description
4508 FROM FND_LOOKUP_VALUES_VL
4509 WHERE
4510 lookup_type = 'FREIGHT TERMS' AND
4511 lookup_code = p_lookup_code
4512 AND nvl(start_date_active,SYSDATE) <= SYSDATE
4513 AND nvl(end_date_active,SYSDATE) >= SYSDATE
4514 AND enabled_flag = 'Y'
4515 AND view_application_id = 201;
4516
4517 l_fgt NUMBER;
4518 l_meaning VARCHAR2(80);
4519 l_rowid VARCHAR2(30);
4520 l_desc VARCHAR2(240);
4521
4522 --
4523 l_debug_on BOOLEAN;
4524 --
4525 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FREIGHT_TERMS';
4526 --
4527 BEGIN
4528
4529 --
4530 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4531 --
4532 IF l_debug_on IS NULL
4533 THEN
4534 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4535 END IF;
4536 --
4537 --
4538 -- Debug Statements
4539 --
4540 IF l_debug_on THEN
4541 WSH_DEBUG_SV.push(l_module_name);
4542 --
4543 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_TERMS_CODE',P_FREIGHT_TERMS_CODE);
4544 END IF;
4545 --
4546 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4547
4548 OPEN c_freight(p_freight_terms_code);
4549 FETCH c_freight INTO l_fgt;
4550
4551 -- If true -> the given Freight Term is not in OE lokups. Then it gets the details of the Freight Term
4552 -- from FND lookups and inserts the same into OE lookups.
4553 IF c_freight%NOTFOUND THEN
4554 OPEN c_po_freight(p_freight_terms_code);
4555 FETCH c_po_freight into l_meaning,l_desc;
4556 CLOSE c_po_freight;
4557
4558 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
4559 X_ROWID => l_rowid,
4560 X_LOOKUP_TYPE => 'FREIGHT_TERMS',
4561 X_SECURITY_GROUP_ID => 0,
4562 X_VIEW_APPLICATION_ID => 660,
4563 X_LOOKUP_CODE => p_freight_terms_code,
4564 X_TAG => NULL,
4565 X_ATTRIBUTE_CATEGORY => NULL,
4566 X_ATTRIBUTE1 => NULL,
4567 X_ATTRIBUTE2 => NULL,
4568 X_ATTRIBUTE3 => NULL,
4569 X_ATTRIBUTE4 => NULL,
4570 X_ENABLED_FLAG => 'Y',
4571 X_START_DATE_ACTIVE => SYSDATE,
4572 X_END_DATE_ACTIVE => NULL,
4573 X_TERRITORY_CODE => NULL,
4574 X_ATTRIBUTE5 => NULL,
4575 X_ATTRIBUTE6 => NULL,
4576 X_ATTRIBUTE7 => NULL,
4577 X_ATTRIBUTE8 => NULL,
4578 X_ATTRIBUTE9 => NULL,
4579 X_ATTRIBUTE10 => NULL,
4580 X_ATTRIBUTE11 => NULL,
4581 X_ATTRIBUTE12 => NULL,
4582 X_ATTRIBUTE13 => NULL,
4583 X_ATTRIBUTE14 => NULL,
4584 X_ATTRIBUTE15 => NULL,
4585 X_MEANING => l_meaning,
4586 X_DESCRIPTION => l_desc,
4587 X_CREATION_DATE => SYSDATE,
4588 X_CREATED_BY => FND_GLOBAL.USER_ID,
4589 X_LAST_UPDATE_DATE => SYSDATE,
4590 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
4591 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
4592 END IF;
4593 CLOSE c_freight;
4594
4595 --
4596 -- Debug Statements
4597 --
4598 IF l_debug_on THEN
4599 WSH_DEBUG_SV.pop(l_module_name);
4600 END IF;
4601 --
4602 EXCEPTION
4603 WHEN OTHERS THEN
4604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4605 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.VALIDATE_FREIGHT_TERMS',l_module_name);
4606 --
4607 -- Debug Statements
4608 --
4609 IF l_debug_on THEN
4610 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4611 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4612 END IF;
4613 --
4614 END Validate_freight_terms;
4615
4616 -- J-IB-NPARIKH-{
4617 --
4618 -- ----------------------------------------------------------------------
4619 -- Procedure: validate_supplier_location
4620 -- Parameters: p_vendor_id in number -- Vendor ID
4621 -- p_party_id in number -- Vendor Party ID
4622 -- p_location_id in number -- Ship from location ID(New value)
4623 -- x_return_status out varchar2 -- Return status of API
4624 --
4625 -- Description: This procedure validates that input location is a valid
4626 -- ship-from location for the input supplier.
4627 --
4628 -- ----------------------------------------------------------------------
4629 PROCEDURE validate_supplier_location
4630 (
4631 p_vendor_id IN NUMBER,
4632 p_party_id IN NUMBER,
4633 p_location_id IN NUMBER,
4634 x_return_status OUT NOCOPY VARCHAR2
4635 )
4636 IS
4637 --{
4638 --
4639 -- Check that for vendor party, a party site is defined
4640 -- with usage as SUPPLIER_SHIP_FROM and location same as the input.
4641 --
4642 CURSOR locn_csr (p_location_id IN NUMBER, p_party_id IN NUMBER)
4643 IS
4644 SELECT 1
4645 FROM hz_party_sites hps,
4646 hz_party_site_uses hpsu
4647 WHERE hps.party_id = p_party_id
4648 AND hps.location_id = p_location_id
4649 AND hpsu.party_site_id = hps.party_site_id
4650 AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
4651 --
4652 l_dummy NUMBER;
4653 l_location_name VARCHAR2(60);
4654 --
4655 l_debug_on BOOLEAN;
4656 --
4657 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'validate_supplier_location';
4658 --
4659 --}
4660 BEGIN
4661 --{
4662 --
4663 l_debug_on := wsh_debug_interface.g_debug;
4664 --
4665 IF l_debug_on IS NULL THEN
4666 l_debug_on := wsh_debug_sv.is_debug_enabled;
4667 END IF;
4668 --
4669 IF l_debug_on THEN
4670 wsh_debug_sv.push(l_module_name);
4671 --
4672 wsh_debug_sv.LOG(l_module_name, 'p_vendor_id', p_vendor_id);
4673 wsh_debug_sv.LOG(l_module_name, 'p_party_id', p_party_id);
4674 wsh_debug_sv.LOG(l_module_name, 'p_location_id', p_location_id);
4675 END IF;
4676 --
4677 x_return_status := wsh_util_core.g_ret_sts_success;
4678 --
4679 --
4680 OPEN locn_csr (p_location_id, p_party_id);
4681 --
4682 FETCH locn_csr
4683 INTO l_dummy;
4684 --
4685 IF locn_csr%NOTFOUND
4686 THEN
4687 --{
4688
4689 IF l_debug_on THEN
4690 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4691 END IF;
4692 --
4693 l_location_name := SUBSTRB(
4694 WSH_UTIL_CORE.get_location_description
4695 (
4696 p_location_id,
4697 'NEW UI CODE'
4698 ),
4699 1,
4700 60
4701 );
4702 --
4703 fnd_message.SET_name('WSH', 'WSH_SUPP_LOCN_ERROR');
4704 FND_MESSAGE.SET_TOKEN('LOCATION_NAME',l_location_name);
4705 --
4706 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
4707 RAISE FND_API.G_EXC_ERROR;
4708 --}
4709 END IF;
4710 --
4711 CLOSE locn_csr;
4712 --
4713 --
4714 IF l_debug_on THEN
4715 wsh_debug_sv.pop(l_module_name);
4716 END IF;
4717 --
4718 --}
4719 EXCEPTION
4720 --{
4721 --
4722 WHEN FND_API.G_EXC_ERROR THEN
4723
4724 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4725 --
4726 IF l_debug_on THEN
4727 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4728 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4729 END IF;
4730 --
4731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4732
4733 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4734 --
4735 IF l_debug_on THEN
4736 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4737 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4738 END IF;
4739 --
4740 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4741 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4742 --
4743 IF l_debug_on THEN
4744 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4745 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4746 END IF;
4747 --
4748 WHEN OTHERS THEN
4749
4750 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4751 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.validate_supplier_location', l_module_name);
4752 IF l_debug_on THEN
4753 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4754 END IF;
4755 --
4756 --}
4757 END validate_supplier_location;
4758 -- J-IB-NPARIKH-}
4759
4760
4761 --===============================================================================================
4762 -- START Bug #3266659: PICK RELEASE BATCH PUBLIC API
4763 --==============================================================================================
4764
4765 --========================================================================
4766 -- PROCEDURE : Validate_Pick_Group_Rule_Name
4767 --
4768 -- COMMENT : Validates Pick_Grouping_Rule_Id/Pick_Grouping_Rule_Name against table
4769 -- wsh_pick_grouping_rules. If both values are specified then only
4770 -- Pick_Grouping_Rule_Id is used
4771 --========================================================================
4772
4773 PROCEDURE Validate_Pick_Group_Rule_Name
4774 (p_pick_grouping_rule_id IN OUT NOCOPY NUMBER ,
4775 p_pick_grouping_rule_name IN VARCHAR2 ,
4776 x_return_status OUT NOCOPY VARCHAR2 ) IS
4777
4778 CURSOR check_pick_grouping_rule_name IS
4779 SELECT pick_grouping_rule_id
4780 FROM wsh_pick_grouping_rules
4781 WHERE p_pick_grouping_rule_id IS NOT NULL
4782 AND pick_grouping_rule_id = p_pick_grouping_rule_id
4783 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1)
4784 UNION ALL
4785 SELECT pick_grouping_rule_id
4786 FROM wsh_pick_grouping_rules
4787 WHERE p_pick_grouping_rule_id IS NULL
4788 AND name = p_pick_grouping_rule_name
4789 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1);
4790
4791 --
4792 l_debug_on BOOLEAN;
4793 --
4794 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_PICK_GROUP_RULE_NAME';
4795 --
4796 BEGIN
4797 --
4798 --
4799 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4800 --
4801 IF l_debug_on IS NULL THEN
4802 --{
4803 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4804 --}
4805 END IF;
4806 --
4807 IF l_debug_on THEN
4808 --{
4809 WSH_DEBUG_SV.push(l_module_name);
4810 --
4811 WSH_DEBUG_SV.log(l_module_name,'P_PICK_GROUPING_RULE_ID',p_pick_grouping_rule_id);
4812 WSH_DEBUG_SV.log(l_module_name,'P_PICK_GROUPING_RULE_NAME',p_pick_grouping_rule_name);
4813 --}
4814 END IF;
4815 --
4816 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4817
4818 IF (p_pick_grouping_rule_id IS NOT NULL) OR (p_pick_grouping_rule_name IS NOT NULL) THEN
4819 --{
4820 OPEN check_pick_grouping_rule_name;
4821 FETCH check_pick_grouping_rule_name INTO p_pick_grouping_rule_id;
4822 IF (check_pick_grouping_rule_name%NOTFOUND) THEN
4823 --{
4824 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
4825 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pick Grouping Rule');
4826 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4827 wsh_util_core.add_message(x_return_status,l_module_name);
4828 --}
4829 END IF;
4830 CLOSE check_pick_grouping_rule_name;
4831 --}
4832 END IF;
4833 --
4834 IF l_debug_on THEN
4835 --{
4836 WSH_DEBUG_SV.pop(l_module_name);
4837 --}
4838 END IF;
4839 --
4840 END Validate_Pick_Group_Rule_Name;
4841
4842 --========================================================================
4843 -- PROCEDURE : Validate_Pick_Seq_Rule_Name
4844 --
4845 -- COMMENT : Validates Pick_Sequence_Rule_Id/Pick_Sequence_Rule_Name against table
4846 -- wsh_pick_sequence_rules. If both values are specified then only
4847 -- Pick_Sequence_Rule_Id is used
4848 --========================================================================
4849
4850 PROCEDURE Validate_Pick_Seq_Rule_Name
4851 (p_Pick_Sequence_Rule_Id IN OUT NOCOPY NUMBER ,
4852 p_Pick_Sequence_Rule_Name IN VARCHAR2 ,
4853 x_return_status OUT NOCOPY VARCHAR2 ) IS
4854
4855 CURSOR check_pick_sequence_rule_name IS
4856 SELECT pick_sequence_rule_id
4857 FROM wsh_pick_sequence_rules
4858 WHERE p_pick_sequence_rule_id IS NOT NULL
4859 AND pick_sequence_rule_id = p_pick_sequence_rule_id
4860 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1)
4861 UNION ALL
4862 SELECT pick_sequence_rule_id
4863 FROM wsh_pick_sequence_rules
4864 WHERE p_pick_sequence_rule_id IS NULL
4865 AND name = p_pick_sequence_rule_name
4866 AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1);
4867
4868 --
4869 l_debug_on BOOLEAN;
4870 --
4871 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_PICK_SEQ_RULE_NAME';
4872 --
4873 BEGIN
4874 --
4875 --
4876 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4877 --
4878 IF l_debug_on IS NULL THEN
4879 --{
4880 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4881 --}
4882 END IF;
4883 --
4884 IF l_debug_on THEN
4885 --{
4886 WSH_DEBUG_SV.push(l_module_name);
4887 --
4888 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SEQUENCE_RULE_ID',p_pick_sequence_rule_id);
4889 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SEQUENCE_RULE_NAME',p_pick_sequence_rule_NAME);
4890 --}
4891 END IF;
4892 --
4893 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4894
4895 IF (p_pick_sequence_rule_id IS NOT NULL) OR (p_pick_sequence_rule_name IS NOT NULL) THEN
4896 --{
4897 OPEN check_pick_sequence_rule_name;
4898 FETCH check_pick_sequence_rule_name INTO p_pick_sequence_rule_id;
4899 IF (check_pick_sequence_rule_name%NOTFOUND) THEN
4900 --{
4901 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
4902 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pick Sequence Rule');
4903 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4904 wsh_util_core.add_message(x_return_status,l_module_name);
4905 --}
4906 END IF;
4907 CLOSE check_pick_sequence_rule_name;
4908 --}
4909 END IF;
4910 --
4911 IF l_debug_on THEN
4912 --{
4913 WSH_DEBUG_SV.pop(l_module_name);
4914 --}
4915 END IF;
4916 --
4917 END Validate_Pick_Seq_Rule_Name;
4918
4919 --========================================================================
4920 -- PROCEDURE : Validate_Ship_Con_Rule_Name
4921 --
4922 -- COMMENT : Validates Ship_Confirm_Rule_Id/Ship_Confirm_Rule_Name against table
4923 -- wsh_ship_confirm_rules. If both values are specified then only
4924 -- Ship_Confirm_Rule_Id is used
4925 --========================================================================
4926
4927 PROCEDURE Validate_Ship_Con_Rule_Name
4928 (p_ship_confirm_rule_id IN OUT NOCOPY NUMBER ,
4929 p_ship_confirm_rule_name IN VARCHAR2 ,
4930 x_return_status OUT NOCOPY VARCHAR2 ) IS
4931
4932 CURSOR check_Ship_Confirm_rule_name IS
4933 SELECT Ship_Confirm_rule_id
4934 FROM wsh_Ship_Confirm_rules
4935 WHERE p_Ship_Confirm_rule_id IS NOT NULL
4936 AND Ship_Confirm_rule_id = p_Ship_Confirm_rule_id
4937 AND trunc(sysdate) BETWEEN nvl(effective_start_date,trunc(sysdate)) AND nvl(effective_end_date,trunc(sysdate) + 1)
4938 UNION ALL
4939 SELECT Ship_Confirm_rule_id
4940 FROM wsh_Ship_Confirm_rules
4941 WHERE p_Ship_Confirm_rule_id IS NULL
4942 AND name = p_Ship_Confirm_rule_name
4943 AND trunc(sysdate) BETWEEN nvl(effective_start_date,trunc(sysdate)) AND nvl(effective_end_date,trunc(sysdate) + 1);
4944
4945 --
4946 l_debug_on BOOLEAN;
4947 --
4948 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SHIP_CON_RULE_NAME';
4949 --
4950 BEGIN
4951 --
4952 --
4953 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4954 --
4955 IF l_debug_on IS NULL THEN
4956 --{
4957 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4958 --}
4959 END IF;
4960 --
4961 IF l_debug_on THEN
4962 --{
4963 WSH_DEBUG_SV.push(l_module_name);
4964 --
4965 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_CONFIRM_RULE_ID',p_ship_confirm_rule_id);
4966 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_CONFIRM_RULE_NAME',p_ship_confirm_rule_name);
4967 --}
4968 END IF;
4969 --
4970 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4971
4972 IF (p_ship_confirm_rule_id IS NOT NULL) OR (p_ship_confirm_rule_name IS NOT NULL) THEN
4973 --{
4974 OPEN check_ship_confirm_rule_name;
4975 FETCH check_ship_confirm_rule_name INTO p_ship_confirm_rule_id;
4976 IF (check_ship_confirm_rule_name%NOTFOUND) THEN
4977 --{
4978 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
4979 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ship Confirm Rule');
4980 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4981 wsh_util_core.add_message(x_return_status,l_module_name);
4982 --}
4983 END IF;
4984 CLOSE check_ship_confirm_rule_name;
4985 --}
4986 END IF;
4987 --
4988 IF l_debug_on THEN
4989 --{
4990 WSH_DEBUG_SV.pop(l_module_name);
4991 --}
4992 END IF;
4993 --
4994 END Validate_Ship_Con_Rule_Name;
4995
4996
4997 --========================================================================
4998 -- PROCEDURE : Validate_Picking_Batch_Name
4999 --
5000 -- COMMENT : Validates picking_Batch_Id/Picking_Batch_Name against table
5001 -- wsh_picking_Batches. If both values are specified then only
5002 -- picking_Batch_Id is used
5003 --========================================================================
5004
5005 PROCEDURE Validate_Picking_Batch_Name
5006 (p_picking_batch_id IN OUT NOCOPY NUMBER ,
5007 p_picking_batch_name IN VARCHAR2 ,
5008 x_return_status OUT NOCOPY VARCHAR2 ) IS
5009
5010 CURSOR check_picking_batch_name IS
5011 SELECT batch_id
5012 FROM wsh_picking_Batches
5013 WHERE p_picking_batch_id IS NOT NULL
5014 AND batch_id = p_picking_batch_id
5015 UNION ALL
5016 SELECT batch_id
5017 FROM wsh_picking_batches
5018 WHERE p_picking_batch_id IS NULL
5019 AND name = p_picking_batch_name;
5020
5021 --
5022 l_debug_on BOOLEAN;
5023 --
5024 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_PICKING_BATCH_NAME';
5025 --
5026 BEGIN
5027 --
5028 --
5029 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5030 --
5031 IF l_debug_on IS NULL THEN
5032 --{
5033 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5034 --}
5035 END IF;
5036 --
5037 IF l_debug_on THEN
5038 --{
5039 WSH_DEBUG_SV.push(l_module_name);
5040 --
5041 WSH_DEBUG_SV.log(l_module_name,'P_PICKING_BATCH_ID',p_Picking_Batch_id);
5042 WSH_DEBUG_SV.log(l_module_name,'P_PICKING_BATCH_NAME',p_Picking_Batch_name);
5043 --}
5044 END IF;
5045 --
5046 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5047
5048 IF (p_Picking_Batch_id IS NOT NULL) OR (p_Picking_Batch_name IS NOT NULL) THEN
5049 --{
5050 OPEN check_Picking_Batch_name;
5051 FETCH check_Picking_Batch_name INTO p_Picking_Batch_id;
5052 IF (check_Picking_Batch_name%NOTFOUND) THEN
5053 --{
5054 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE');
5055 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Picking Batch Name');
5056 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5057 wsh_util_core.add_message(x_return_status,l_module_name);
5058 --}
5059 END IF;
5060 CLOSE check_Picking_Batch_name;
5061 --}
5062 END IF;
5063 --
5064 IF l_debug_on THEN
5065 --{
5066 WSH_DEBUG_SV.pop(l_module_name);
5067 --}
5068 END IF;
5069 --
5070 END Validate_Picking_Batch_Name;
5071 -- END Bug #3266659
5072
5073 -- Bug#3880569: Adding a new procedure Validate_Active_SM
5074 --========================================================================
5075 -- PROCEDURE : Validate_Active_SM
5076 --
5077 -- COMMENT : Validates Active Ship_Method_Code/Name against wsh_carrier_services.
5078 -- If both values are specified then only Ship_Method_Code is used
5079 --========================================================================
5080
5081 PROCEDURE Validate_Active_SM
5082 (p_ship_method_code IN OUT NOCOPY VARCHAR2,
5083 p_ship_method_name IN OUT NOCOPY VARCHAR2,
5084 x_return_status OUT NOCOPY VARCHAR2) IS
5085
5086 CURSOR check_ship_method IS
5087 select flv.lookup_code ship_method_code,
5088 flv.meaning
5089 from fnd_lookup_values_vl flv, wsh_carrier_services wcs
5090 where flv.lookup_code = wcs.ship_method_code
5091 and wcs.ship_method_code = p_ship_method_code
5092 and flv.lookup_type = 'SHIP_METHOD'
5093 and flv.view_application_id = 3
5094 and wcs.enabled_flag='Y'
5095 UNION ALL
5096 select flv.lookup_code ship_method_code,
5097 flv.meaning
5098 from fnd_lookup_values_vl flv, wsh_carrier_services wcs
5099 where flv.lookup_code = wcs.ship_method_code
5100 and NVL(p_ship_method_code,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5101 and flv.lookup_type = 'SHIP_METHOD'
5102 and flv.view_application_id = 3
5103 and flv.meaning = p_ship_method_name
5104 and wcs.enabled_flag='Y' ;
5105
5106 --
5107 l_debug_on BOOLEAN;
5108 --
5109 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ACTIVE_SM';
5110 --
5111 BEGIN
5112 --
5113 --
5114 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5115 --
5116 IF l_debug_on IS NULL
5117 THEN
5118 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5119 END IF;
5120 --
5121 IF l_debug_on THEN
5122 WSH_DEBUG_SV.push(l_module_name);
5123 --
5124 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
5125 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_NAME',P_SHIP_METHOD_NAME);
5126 END IF;
5127 --
5128 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5129 --
5130 IF nvl(p_ship_method_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
5131 OR nvl(p_ship_method_name, fnd_api.g_miss_char) <> fnd_api.g_miss_char
5132 THEN
5133 --
5134 OPEN check_ship_method;
5135 FETCH check_ship_method INTO p_ship_method_code, p_ship_method_name;
5136 --
5137 IF (check_ship_method%NOTFOUND) THEN
5138 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INACTIVE_SHIP_METHOD');
5139 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5140 wsh_util_core.add_message(x_return_status,l_module_name);
5141 END IF;
5142 --
5143 CLOSE check_ship_method;
5144 --
5145 END IF;
5146 --
5147 IF l_debug_on THEN
5148 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_code',p_ship_method_code);
5149 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_name',p_ship_method_name);
5150 WSH_DEBUG_SV.pop(l_module_name);
5151 END IF;
5152 --
5153 EXCEPTION
5154 WHEN OTHERS THEN
5155 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5156 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.Validate_Active_SM');
5157 IF l_debug_on THEN
5158 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5159 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5160 END IF;
5161 END Validate_Active_SM;
5162
5163 -- Added new internal procedure for OTM R12, glog project
5164 --========================================================================
5165 -- PROCEDURE : Validate_Lookup_Upper
5166 --
5167 -- COMMENT : Validates Lookup_code and Meaning against view fnd_lookups.
5168 -- If both values are specified then only Lookup_code is used
5169 -- For glog project, Validate lookup_code in UPPER CASE
5170 --
5171 -- NOTE : For old data, it is possible that the cursor might fetch
5172 -- multiple rows when using UPPER CASE CONVERSION
5173 -- If more than 1 record is found, raise an error requesting
5174 -- fix for the data.
5175 --========================================================================
5176
5177 PROCEDURE Validate_Lookup_Upper
5178 (p_lookup_type IN VARCHAR2,
5179 p_lookup_code IN OUT NOCOPY VARCHAR2,
5180 p_meaning IN VARCHAR2,
5181 x_return_status OUT NOCOPY VARCHAR2) IS
5182
5183 -- Bug 3821688 Split Cursor
5184 CURSOR check_lookup_code IS
5185 SELECT lookup_code
5186 FROM fnd_lookup_values_vl
5187 WHERE UPPER(lookup_code) = UPPER(p_lookup_code) AND
5188 lookup_type = p_lookup_type AND
5189 nvl(start_date_active,sysdate)<=sysdate AND nvl(end_date_active,sysdate)>=sysdate AND
5190 view_application_id = 660 AND
5191 enabled_flag = 'Y';
5192
5193 CURSOR check_lookup_meaning IS
5194 SELECT lookup_code
5195 FROM fnd_lookup_values_vl
5196 WHERE meaning = p_meaning AND
5197 lookup_type = p_lookup_type AND
5198 nvl(start_date_active,sysdate)<=sysdate AND nvl(end_date_active,sysdate)>=sysdate AND
5199 view_application_id = 660 AND
5200 enabled_flag = 'Y';
5201
5202 -- Bug 3821688
5203 l_index NUMBER;
5204 l_flag VARCHAR2(1);
5205 l_return_status VARCHAR2(1);
5206 l_cache_rec Generic_Cache_Rec_Typ;
5207 l_count NUMBER;
5208
5209 --
5210 --
5211 l_debug_on BOOLEAN;
5212 --
5213 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_LOOKUP_UPPER';
5214 --
5215 BEGIN
5216 --
5217 --
5218 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5219 --
5220 IF l_debug_on IS NULL
5221 THEN
5222 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5223 END IF;
5224 --
5225 IF l_debug_on THEN
5226 WSH_DEBUG_SV.push(l_module_name);
5227 --
5228 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_TYPE',P_LOOKUP_TYPE);
5229 WSH_DEBUG_SV.log(l_module_name,'P_LOOKUP_CODE',P_LOOKUP_CODE);
5230 WSH_DEBUG_SV.log(l_module_name,'P_MEANING',P_MEANING);
5231 END IF;
5232 --
5233 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5234
5235 IF (p_lookup_code IS NOT NULL) OR (p_meaning IS NOT NULL) THEN
5236 -- Bug 3821688
5237 l_cache_rec.input_param1 := p_lookup_code;
5238 l_cache_rec.input_param2 := p_meaning;
5239 l_cache_rec.input_param3 := p_lookup_type;
5240 -- Always Call get_table_index to check if value exists in cache
5241 -- If no record exists,then we can insert new record with the output index
5242 get_table_index
5243 (p_validate_rec => l_cache_rec,
5244 p_generic_tab => g_lookup_tab,
5245 x_index => l_index,
5246 x_return_status => l_return_status,
5247 x_flag => l_flag
5248 );
5249 -- l_flag = U means use this index value to insert record in table
5250 IF l_flag = 'U' AND l_index IS NOT NULL THEN--{
5251 IF p_lookup_code IS NOT NULL THEN --{
5252 l_count := 0;
5253 l_cache_rec.valid_flag := 'N';
5254 FOR rec in check_lookup_code
5255 LOOP
5256 l_cache_rec.output_param1 := rec.lookup_code;
5257 l_count := l_count + 1;
5258 END LOOP;
5259
5260 IF l_count = 1 THEN
5261 l_cache_rec.valid_flag := 'Y';
5262 ELSIF l_count > 1 THEN -- more than 1 record found
5263 -- Add this Message to the one at the end
5264 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_LOOKUP_CODE');
5265 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE',p_lookup_type);
5266 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5267 wsh_util_core.add_message(x_return_status,l_module_name);
5268 END IF;
5269
5270 ELSIF p_meaning IS NOT NULL THEN
5271 OPEN check_lookup_meaning;
5272 FETCH check_lookup_meaning INTO l_cache_rec.output_param1;
5273 IF (check_lookup_meaning%NOTFOUND) THEN
5274 l_cache_rec.valid_flag := 'N';
5275 ELSE
5276 l_cache_rec.valid_flag := 'Y';
5277 END IF;
5278 CLOSE check_lookup_meaning;
5279 END IF;--}
5280
5281 g_lookup_tab(l_index) := l_cache_rec;
5282 END IF;--}
5283
5284 -- Always check if input is valid or not
5285 IF g_lookup_tab(l_index).valid_flag = 'N' THEN
5286 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOOKUP');
5287 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5288 wsh_util_core.add_message(x_return_status,l_module_name);
5289 END IF;
5290 -- Always Populate return variables
5291 p_lookup_code := g_lookup_tab(l_index).output_param1;
5292
5293 END IF;
5294 --
5295 IF l_debug_on THEN
5296 WSH_DEBUG_SV.log(l_module_name,'p_lookup_code',p_lookup_code);
5297 WSH_DEBUG_SV.pop(l_module_name);
5298 END IF;
5299
5300 --
5301 EXCEPTION
5302 --
5303 WHEN OTHERS THEN
5304 --
5305 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5306 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.Validate_Lookup_Upper');
5307 --
5308 IF l_debug_on THEN
5309 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5310 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5311 END IF;
5312 --
5313 END Validate_Lookup_Upper;
5314
5315
5316 /*======================================================================
5317 PROCEDURE : ValidateActualDepartureDate
5318
5319 COMMENT : This is just a wrapper around the function
5320 WSH_UITL_CORE.ValidateActualDepartureDate
5321
5322 This procedure calls a similar function in WSH_UTIL_CORE
5323 and logs an error message if the actual departure date is
5324 not valid.
5325
5326 HISTORY : rlanka 03/08/2005 Created
5327 =======================================================================*/
5328 PROCEDURE ValidateActualDepartureDate
5329 (p_ship_confirm_rule_id IN NUMBER,
5330 p_actual_departure_date IN DATE,
5331 x_return_status OUT NOCOPY VARCHAR2) IS
5332 --
5333 l_debug_on BOOLEAN;
5334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
5335 || 'ValidateActualDepartureDate';
5336 --
5337 v_ValidDate BOOLEAN;
5338 --
5339 BEGIN
5340 --
5341 v_ValidDate := FALSE;
5342 --
5343 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5344 --
5345 IF l_debug_on IS NULL THEN
5346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5347 END IF;
5348 --
5349 IF l_debug_on THEN
5350 WSH_DEBUG_SV.push(l_module_name);
5351 WSH_DEBUG_SV.log(l_module_name,'p_ship_confirm_rule_id',
5352 p_ship_confirm_rule_id);
5353 WSH_DEBUG_SV.log(l_module_name,'p_actual_departure_date',
5354 p_actual_departure_date);
5355 END IF;
5356 --
5357 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5358 --
5359 v_ValidDate := WSH_UTIL_CORE.ValidateActualDepartureDate(
5360 p_ship_confirm_rule_id => p_ship_confirm_rule_id,
5361 p_actual_departure_date => p_actual_departure_date);
5362 --
5363 IF NOT v_ValidDate THEN
5364 --
5365 IF l_debug_on THEN
5366 wsh_debug_sv.logmsg(l_module_name, 'Future ship date is not allowed');
5367 END IF;
5368 --
5369 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_FUTURE_SHIP_DATE');
5370 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5371 wsh_util_core.add_message(x_return_status);
5372 --
5373 END IF;
5374 --
5375 IF l_debug_on THEN
5376 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5377 WSH_DEBUG_SV.pop(l_module_name);
5378 END IF;
5379 --
5380 EXCEPTION
5381 --
5382 WHEN OTHERS THEN
5383 --
5384 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5385 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.ValidateActualDepartureDate');
5386 --
5387 IF l_debug_on THEN
5388 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle
5389 error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5390 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5391 END IF;
5392 --
5393 END ValidateActualDepartureDate;
5394 --
5395 -- Standalone Project - Start
5396 --
5397 --=============================================================================
5398 -- PUBLIC PROCEDURE :
5399 -- Validate_SR_Organization
5400 --
5401 -- PARAMETERS:
5402 -- p_organization_id => Organization Id
5403 -- x_return_status => Return Status of API (Either S,E,U)
5404 --
5405 -- COMMENT:
5406 -- API to validate whether Organization is WMS enabled and NOT Process
5407 -- manufacturing enabled.
5408 -- HISTORY :
5409 -- ueshanka 19/Nov/2008 Created
5410 --=============================================================================
5411 --
5412 PROCEDURE Validate_SR_Organization(
5413 p_organization_id IN NUMBER,
5414 x_return_status OUT NOCOPY VARCHAR2)
5415 IS
5416 l_org_cache_rec Generic_Cache_Rec_Typ;
5417 l_index NUMBER;
5418 l_return_status VARCHAR2(1);
5419 l_flag VARCHAR2(1);
5420 --
5421 l_debug_on BOOLEAN;
5422 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_SR_Organization';
5423 --
5424 BEGIN
5425 --Debug Push
5426 --
5427 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5428 --
5429 IF l_debug_on IS NULL THEN
5430 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5431 END IF;
5432 --
5433 IF l_debug_on THEN
5434 WSH_DEBUG_SV.push(l_module_name);
5435 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
5436 END IF;
5437 --
5438
5439 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5440 l_org_cache_rec.input_param1 := p_organization_id;
5441
5442 Get_Table_Index(
5443 p_validate_rec => l_org_cache_rec,
5444 p_generic_tab => g_sr_org_tab,
5445 x_index => l_index,
5446 x_return_status => l_return_status,
5447 x_flag => l_flag );
5448
5449 IF l_index is not null THEN
5450 IF l_flag = 'U' THEN --Not Found in Cache, so use this index to add in Cache
5451 g_sr_org_tab(l_index).input_param1 := p_organization_id;
5452 g_sr_org_tab(l_index).output_param1 :=
5453 WSH_UTIL_VALIDATE.Check_Wms_Org (
5454 p_organization_id => p_organization_id );
5455
5456 IF INV_GMI_RSV_BRANCH.PROCESS_BRANCH(p_organization_id) THEN
5457 g_sr_org_tab(l_index).output_param2 := 'Y' ;
5458 ELSE
5459 g_sr_org_tab(l_index).output_param2 := 'N' ;
5460 END IF;
5461
5462 IF g_sr_org_tab(l_index).output_param1 = 'N' or
5463 g_sr_org_tab(l_index).output_param2 = 'Y'
5464 THEN
5465 g_sr_org_tab(l_index).valid_flag := 'N';
5466 ELSE
5467 g_sr_org_tab(l_index).valid_flag := 'Y';
5468 END IF;
5469 END IF;
5470
5471 IF g_sr_org_tab(l_index).valid_flag = 'N' THEN
5472 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5473 --WMS Enabled Check
5474 IF g_sr_org_tab(l_index).output_param1 = 'N' THEN
5475 --
5476 IF l_debug_on THEN
5477 WSH_DEBUG_SV.logmsg(l_module_name, 'Organization is NOT WMS Enabled');
5478 END IF;
5479 --
5480 FND_MESSAGE.SET_NAME('WSH','WSH_STND_WMS_NOT_INSTALLED');
5481 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
5482 END IF;
5483
5484 --OPM Enabled Check
5485 IF g_sr_org_tab(l_index).output_param2 = 'Y' THEN
5486 --
5487 IF l_debug_on THEN
5488 WSH_DEBUG_SV.logmsg(l_module_name, 'Organization is OPM Enabled');
5489 END IF;
5490 --
5491 FND_MESSAGE.SET_NAME('WSH','WSH_STND_OPM_INSTALLED');
5492 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
5493 END IF;
5494 END IF;
5495 END IF;
5496
5497 --
5498 IF l_debug_on THEN
5499 WSH_DEBUG_SV.pop(l_module_name);
5500 END IF;
5501 --
5502 EXCEPTION
5503 WHEN OTHERS THEN
5504 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5505 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.Validate_SR_Organization');
5506 --
5507 IF l_debug_on THEN
5508 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5510 END IF;
5511 --
5512 END Validate_SR_Organization;
5513 --
5514 --=============================================================================
5515 -- PUBLIC PROCEDURE :
5516 -- Validate_Locator_Code
5517 --
5518 -- PARAMETERS:
5519 -- p_locator_code => Locator Code
5520 -- p_organization_id => Organization Id
5521 -- x_locator_id => Locator Id
5522 -- x_return_status => Return Status of API (Either S,E,U)
5523 --
5524 -- COMMENT:
5525 -- API to derive Locator Id based on Locator Code and Organization passed
5526 -- HISTORY :
5527 -- ueshanka 19/Nov/2008 Created
5528 --=============================================================================
5529 --
5530 PROCEDURE Validate_Locator_Code(
5531 p_locator_code IN VARCHAR2,
5532 p_organization_id IN NUMBER,
5533 x_locator_id OUT NOCOPY NUMBER,
5534 x_return_status OUT NOCOPY VARCHAR2)
5535 IS
5536 l_locator_cache_rec Generic_Cache_Rec_Typ;
5537 l_index NUMBER;
5538 l_return_status VARCHAR2(1);
5539 l_flag VARCHAR2(1);
5540 --
5541 l_debug_on BOOLEAN;
5542 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Locator_Code';
5543 --
5544 BEGIN
5545 --Debug Push
5546 --
5547 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5548 --
5549 IF l_debug_on IS NULL THEN
5550 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5551 END IF;
5552 --
5553 IF l_debug_on THEN
5554 WSH_DEBUG_SV.push(l_module_name);
5555 WSH_DEBUG_SV.log(l_module_name, 'p_locator_code', p_locator_code);
5556 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
5557 END IF;
5558 --
5559
5560 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5561 l_locator_cache_rec.input_param1 := p_locator_code;
5562 l_locator_cache_rec.input_param2 := p_organization_id;
5563
5564 Get_Table_Index(
5565 p_validate_rec => l_locator_cache_rec,
5566 p_generic_tab => g_locator_code_tab,
5567 x_index => l_index,
5568 x_return_status => l_return_status,
5569 x_flag => l_flag );
5570
5571 IF l_index is not null THEN
5572 IF l_flag = 'U' THEN --Not Found in Cache, so use this index to add in Cache
5573 --
5574 IF l_debug_on THEN
5575 WSH_DEBUG_SV.logmsg(l_module_name, 'Locator not found in cache');
5576 END IF;
5577 --
5578 g_locator_code_tab(l_index).input_param1 := p_locator_code;
5579 g_locator_code_tab(l_index).input_param2 := p_organization_id;
5580 BEGIN
5581 select inventory_location_id
5582 into x_locator_id
5583 from mtl_item_locations_kfv
5584 where concatenated_segments = p_locator_code
5585 and organization_id = p_organization_id
5586 and rownum = 1;
5587
5588 g_locator_code_tab(l_index).valid_flag := 'Y';
5589 g_locator_code_tab(l_index).output_param1 := x_locator_id;
5590
5591 EXCEPTION
5592 WHEN NO_DATA_FOUND THEN
5593 g_locator_code_tab(l_index).valid_flag := 'N';
5594 END;
5595 END IF;
5596
5597 IF g_locator_code_tab(l_index).valid_flag = 'N' THEN
5598 --
5599 IF l_debug_on THEN
5600 WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Locator Code');
5601 END IF;
5602 --
5603 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5604 FND_MESSAGE.SET_NAME('WSH','WSH_STND_INVALID_LOCATOR');
5605 FND_MESSAGE.SET_TOKEN('LOCATOR_CODE', p_locator_code);
5606 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
5607 ELSE
5608 x_locator_id := to_number(g_locator_code_tab(l_index).output_param1);
5609 END IF;
5610 END IF;
5611
5612 --
5613 IF l_debug_on THEN
5614 WSH_DEBUG_SV.log(l_module_name, 'x_locator_id', x_locator_id);
5615 WSH_DEBUG_SV.pop(l_module_name);
5616 END IF;
5617 --
5618 EXCEPTION
5619 WHEN OTHERS THEN
5620 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5621 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.Validate_Locator_Code');
5622 --
5623 IF l_debug_on THEN
5624 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5625 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5626 END IF;
5627 --
5628 END Validate_Locator_Code;
5629 --
5630 --=============================================================================
5631 -- PUBLIC PROCEDURE :
5632 -- Validate_Item
5633 --
5634 -- PARAMETERS:
5635 -- p_item_number => Inventory Item Name
5636 -- p_organization_id => Organization Id
5637 -- x_inventory_item_id => Inventory Item Id
5638 -- x_return_status => Return Status of API (Either S,E,U)
5639 --
5640 -- COMMENT:
5641 -- API to derive Inventory Item Id based on Item Number and Organization
5642 -- passed
5643 -- HISTORY :
5644 -- ueshanka 19/Nov/2008 Created
5645 --=============================================================================
5646 --
5647 PROCEDURE Validate_Item(
5648 p_item_number IN VARCHAR2,
5649 p_organization_id IN NUMBER,
5650 x_inventory_item_id OUT NOCOPY NUMBER,
5651 x_return_status OUT NOCOPY VARCHAR2)
5652 IS
5653 l_item_cache_rec Generic_Cache_Rec_Typ;
5654 l_index NUMBER;
5655 l_return_status VARCHAR2(1);
5656 l_flag VARCHAR2(1);
5657 --
5658 l_debug_on BOOLEAN;
5659 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Item';
5660 --
5661 BEGIN
5662 --Debug Push
5663 --
5664 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5665 --
5666 IF l_debug_on IS NULL THEN
5667 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5668 END IF;
5669 --
5670 IF l_debug_on THEN
5671 WSH_DEBUG_SV.push(l_module_name);
5672 WSH_DEBUG_SV.log(l_module_name, 'p_item_number', p_item_number);
5673 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
5674 END IF;
5675 --
5676
5677 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5678
5679 l_item_cache_rec.input_param1 := p_item_number;
5680 l_item_cache_rec.input_param2 := p_organization_id;
5681
5682 Get_Table_Index(
5683 p_validate_rec => l_item_cache_rec,
5684 p_generic_tab => g_inventory_item_tab,
5685 x_index => l_index,
5686 x_return_status => l_return_status,
5687 x_flag => l_flag );
5688
5689 IF l_index is not null THEN
5690 IF l_flag = 'U' THEN --Not Found in Cache, so use this index to add in Cache
5691 --
5692 IF l_debug_on THEN
5693 WSH_DEBUG_SV.logmsg(l_module_name, 'Inventory item not found in cache');
5694 END IF;
5695 --
5696 g_inventory_item_tab(l_index).input_param1 := p_item_number;
5697 g_inventory_item_tab(l_index).input_param2 := p_organization_id;
5698
5699 --
5700 IF l_debug_on THEN
5701 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_EXTERNAL_INTERFACE_SV.Validate_Item', WSH_DEBUG_SV.C_PROC_LEVEL);
5702 END IF;
5703 --
5704 WSH_EXTERNAL_INTERFACE_SV.Validate_Item(
5705 p_concatenated_segments => p_item_number,
5706 p_organization_id => p_organization_id,
5707 x_inventory_item_id => x_inventory_item_id,
5708 x_return_status => l_return_status );
5709
5710 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS and
5711 x_inventory_item_id is not null
5712 THEN
5713 g_inventory_item_tab(l_index).valid_flag := 'Y';
5714 g_inventory_item_tab(l_index).output_param1 := x_inventory_item_id;
5715 ELSE
5716 g_inventory_item_tab(l_index).valid_flag := 'N';
5717 END IF;
5718 END IF;
5719
5720 IF g_inventory_item_tab(l_index).valid_flag = 'N' THEN
5721 --
5722 IF l_debug_on THEN
5723 WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Inventory Item');
5724 END IF;
5725 --
5726 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5727 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_INV_ITEM');
5728 FND_MESSAGE.Set_Token('ITEM_NAME', p_item_number);
5729 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
5730 ELSE
5731 x_inventory_item_id := to_number(g_inventory_item_tab(l_index).output_param1);
5732 END IF;
5733 END IF;
5734
5735 --
5736 IF l_debug_on THEN
5737 WSH_DEBUG_SV.log(l_module_name, 'x_inventory_item_id', x_inventory_item_id);
5738 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5739 WSH_DEBUG_SV.pop(l_module_name);
5740 END IF;
5741 --
5742 EXCEPTION
5743 WHEN OTHERS THEN
5744 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5745 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.Validate_Item');
5746 --
5747 IF l_debug_on THEN
5748 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5749 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5750 END IF;
5751 --
5752 END Validate_Item;
5753 --
5754 --=============================================================================
5755 -- PUBLIC PROCEDURE :
5756 -- Validate_Customer_Item
5757 --
5758 -- PARAMETERS:
5759 -- p_item_number => Inventory Item Name
5760 -- p_customer_id => SoldTo Customer Id
5761 -- p_address_id => ShipTo Customer Address Id
5762 -- x_customer_item_id => Customer Item Id
5763 -- x_return_status => Return Status of API (Either S,E,U)
5764 --
5765 -- COMMENT:
5766 -- API to derive Customer Item Id based on Item Number, Customer Id and
5767 -- Customer Address Id passed.
5768 -- HISTORY :
5769 -- ueshanka 19/Nov/2008 Created
5770 --=============================================================================
5771 --
5772 PROCEDURE Validate_Customer_Item(
5773 p_item_number IN VARCHAR2,
5774 p_customer_id IN NUMBER,
5775 p_address_id IN VARCHAR2,
5776 x_customer_item_id OUT NOCOPY NUMBER,
5777 x_return_status OUT NOCOPY VARCHAR2)
5778 IS
5779 l_cust_item_cache_rec Generic_Cache_Rec_Typ;
5780 l_index NUMBER;
5781 l_return_status VARCHAR2(1);
5782 l_flag VARCHAR2(1);
5783 --
5784 l_debug_on BOOLEAN;
5785 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Customer_Item';
5786 --
5787 BEGIN
5788 --Debug Push
5789 --
5790 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5791 --
5792 IF l_debug_on IS NULL THEN
5793 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5794 END IF;
5795 --
5796 IF l_debug_on THEN
5797 WSH_DEBUG_SV.push(l_module_name);
5798 WSH_DEBUG_SV.log(l_module_name, 'p_item_number', p_item_number);
5799 WSH_DEBUG_SV.log(l_module_name, 'p_customer_id', p_customer_id);
5800 WSH_DEBUG_SV.log(l_module_name, 'p_address_id', p_address_id);
5801 END IF;
5802 --
5803
5804 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5805
5806 l_cust_item_cache_rec.input_param1 := p_item_number;
5807 l_cust_item_cache_rec.input_param2 := p_customer_id;
5808 l_cust_item_cache_rec.input_param3 := p_address_id;
5809
5810 Get_Table_Index(
5811 p_validate_rec => l_cust_item_cache_rec,
5812 p_generic_tab => g_customer_item_tab,
5813 x_index => l_index,
5814 x_return_status => l_return_status,
5815 x_flag => l_flag );
5816
5817 IF l_index is not null THEN
5818 IF l_flag = 'U' THEN --Not Found in Cache, so use this index to add in Cache
5819 --
5820 IF l_debug_on THEN
5821 WSH_DEBUG_SV.logmsg(l_module_name, 'Customer item not found in cache');
5822 END IF;
5823 --
5824 g_customer_item_tab(l_index).input_param1 := p_item_number;
5825 g_customer_item_tab(l_index).input_param2 := p_customer_id;
5826 g_customer_item_tab(l_index).input_param3 := p_address_id;
5827
5828 BEGIN
5829
5830 select cust_items.customer_item_id
5831 into x_customer_item_id
5832 from
5833 ( select mci.customer_item_id, mci.item_definition_level
5834 from mtl_customer_items mci
5835 where mci.customer_item_number = p_item_number
5836 and mci.customer_id = p_customer_id
5837 and mci.item_definition_level = 1
5838 union
5839 select mci1.customer_item_id, mci1.item_definition_level
5840 from mtl_customer_items mci1
5841 where mci1.customer_item_number = p_item_number
5842 and mci1.customer_id = p_customer_id
5843 and mci1.address_id = p_address_id
5844 and mci1.item_definition_level = 3
5845 order by 2 desc ) cust_items
5846 where rownum = 1;
5847
5848 g_customer_item_tab(l_index).valid_flag := 'Y';
5849 g_customer_item_tab(l_index).output_param1 := x_customer_item_id;
5850 EXCEPTION
5851 WHEN NO_DATA_FOUND THEN
5852 --
5853 IF l_debug_on THEN
5854 WSH_DEBUG_SV.logmsg(l_module_name, 'Inside No-Data-Found Exception');
5855 END IF;
5856 --
5857 g_customer_item_tab(l_index).valid_flag := 'N';
5858 END;
5859 END IF;
5860
5861 IF g_customer_item_tab(l_index).valid_flag = 'N' THEN
5862 --
5863 IF l_debug_on THEN
5864 WSH_DEBUG_SV.logmsg(l_module_name, 'Invalid Customer Item');
5865 END IF;
5866 --
5867 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5868 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CUST_ITEM');
5869 FND_MESSAGE.Set_Token('CUST_ITEM', p_item_number);
5870 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
5871 ELSE
5872 x_customer_item_id := to_number(g_customer_item_tab(l_index).output_param1);
5873 END IF;
5874 END IF;
5875
5876 --
5877 IF l_debug_on THEN
5878 WSH_DEBUG_SV.log(l_module_name, 'x_customer_item_id', x_customer_item_id);
5879 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5880 WSH_DEBUG_SV.pop(l_module_name);
5881 END IF;
5882 --
5883 EXCEPTION
5884 WHEN OTHERS THEN
5885 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5886 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.Validate_Customer_Item');
5887 --
5888 IF l_debug_on THEN
5889 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5890 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5891 END IF;
5892 --
5893 END Validate_Customer_Item;
5894 --
5895 --=============================================================================
5896 -- PUBLIC PROCEDURE :
5897 -- Validate_Ship_Method
5898 --
5899 -- PARAMETERS:
5900 -- p_organization_id => Organization Id
5901 -- p_carrier_code => Carrier Code
5902 -- p_service_level => Service Level
5903 -- p_mode_of_transport => Mode of Transport
5904 -- x_ship_method_code => Ship Method Code
5905 -- x_return_status => Return Status of API (Either S,E,U)
5906 --
5907 -- COMMENT:
5908 -- API to derive Ship Method Code based on Carrier, Service Level, Mode
5909 -- of Transport and Organization passed.
5910 -- HISTORY :
5911 -- ueshanka 19/Nov/2008 Created
5912 --=============================================================================
5913 --
5914 PROCEDURE Validate_Ship_Method(
5915 p_organization_id IN NUMBER,
5916 p_carrier_code IN VARCHAR2,
5917 p_service_level IN VARCHAR2,
5918 p_mode_of_transport IN VARCHAR2,
5919 x_ship_method_code OUT NOCOPY VARCHAR2,
5920 x_return_status OUT NOCOPY VARCHAR2)
5921 IS
5922 l_ship_method_cache_rec Generic_Cache_Rec_Typ;
5923 l_index NUMBER;
5924 l_return_status VARCHAR2(1);
5925 l_flag VARCHAR2(1);
5926 l_service_level VARCHAR2(30);
5927 l_mode_of_transport VARCHAR2(30);
5928 --
5929 l_debug_on BOOLEAN;
5930 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Ship_Method';
5931 --
5932 BEGIN
5933 --Debug Push
5934 --
5935 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5936 --
5937 IF l_debug_on IS NULL THEN
5938 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5939 END IF;
5940 --
5941 IF l_debug_on THEN
5942 WSH_DEBUG_SV.push(l_module_name);
5943 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
5944 WSH_DEBUG_SV.log(l_module_name, 'p_carrier_code', p_carrier_code);
5945 WSH_DEBUG_SV.log(l_module_name, 'p_service_level', p_service_level);
5946 WSH_DEBUG_SV.log(l_module_name, 'p_mode_of_transport', p_mode_of_transport);
5947 END IF;
5948 --
5949
5950 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5951
5952 l_ship_method_cache_rec.input_param1 := p_organization_id;
5953 l_ship_method_cache_rec.input_param2 := p_carrier_code;
5954 l_ship_method_cache_rec.input_param3 := p_service_level;
5955 l_ship_method_cache_rec.input_param4 := p_mode_of_transport;
5956
5957 Get_Table_Index(
5958 p_validate_rec => l_ship_method_cache_rec,
5959 p_generic_tab => g_shipping_method_tab,
5960 x_index => l_index,
5961 x_return_status => l_return_status,
5962 x_flag => l_flag );
5963
5964 IF l_index is not null THEN
5965 IF l_flag = 'U' THEN --Not Found in Cache, so use this index to add in Cache
5966 --
5967 IF l_debug_on THEN
5968 WSH_DEBUG_SV.logmsg(l_module_name, 'Ship Method Code not found in cache');
5969 END IF;
5970 --
5971 g_shipping_method_tab(l_index).input_param1 := p_organization_id;
5972 g_shipping_method_tab(l_index).input_param2 := p_carrier_code;
5973 g_shipping_method_tab(l_index).input_param3 := p_service_level;
5974 g_shipping_method_tab(l_index).input_param4 := p_mode_of_transport;
5975
5976 BEGIN
5977 --
5978 IF l_debug_on THEN
5979 WSH_DEBUG_SV.logmsg(l_module_name, 'Querying Ship Method Code from table');
5980 END IF;
5981 --
5982 select ship_method_code
5983 into x_ship_method_code
5984 from wsh_carriers wc,
5985 wsh_carrier_services wcs,
5986 wsh_org_carrier_services wocs
5987 where wocs.organization_id = p_organization_id
5988 and wocs.carrier_service_id = wcs.carrier_service_id
5989 and wcs.mode_of_transport = p_mode_of_transport
5990 and wcs.service_level = p_service_level
5991 and wcs.carrier_id = wc.carrier_id
5992 and wc.freight_code = p_carrier_code;
5993
5994 g_shipping_method_tab(l_index).valid_flag := 'Y';
5995 g_shipping_method_tab(l_index).output_param1 := x_ship_method_code;
5996 EXCEPTION
5997 WHEN NO_DATA_FOUND THEN
5998 --
5999 IF l_debug_on THEN
6000 WSH_DEBUG_SV.logmsg(l_module_name, 'Inside No-Data-Found Exception');
6001 END IF;
6002 --
6003 g_shipping_method_tab(l_index).valid_flag := 'N';
6004 END;
6005 END IF;
6006
6007 --
6008 IF l_debug_on THEN
6009 WSH_DEBUG_SV.logmsg(l_module_name, 'Valid Flag => ' || g_shipping_method_tab(l_index).valid_flag);
6010 END IF;
6011 --
6012
6013 IF g_shipping_method_tab(l_index).valid_flag = 'N' THEN
6014 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6015 --
6016 IF l_debug_on THEN
6017 WSH_DEBUG_SV.logmsg(l_module_name, 'Ship Method does not exists');
6018 END IF;
6019 --
6020 FND_MESSAGE.SET_NAME('WSH','WSH_STND_INVALID_SM');
6021 FND_MESSAGE.SET_TOKEN('CARRIER', p_carrier_code);
6022 FND_MESSAGE.SET_TOKEN('SERVICE_LEVEL', p_service_level);
6023 FND_MESSAGE.SET_TOKEN('MODE_OF_TRANS', p_mode_of_transport);
6024 FND_MESSAGE.SET_TOKEN('WAREHOUSE', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
6025 WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
6026 ELSIF g_shipping_method_tab(l_index).valid_flag = 'Y' THEN
6027 x_ship_method_code := g_shipping_method_tab(l_index).output_param1;
6028 END IF;
6029 END IF;
6030
6031 --
6032 IF l_debug_on THEN
6033 WSH_DEBUG_SV.log(l_module_name, 'x_ship_method_code', x_ship_method_code);
6034 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
6035 WSH_DEBUG_SV.pop(l_module_name);
6036 END IF;
6037 --
6038 EXCEPTION
6039 WHEN OTHERS THEN
6040 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6041 WSH_UTIL_CORE.Default_Handler('WSH_UTIL_VALIDATE.Validate_Ship_Method');
6042 --
6043 IF l_debug_on THEN
6044 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6045 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6046 END IF;
6047 --
6048 END Validate_Ship_Method;
6049 --
6050 -- Standalone Project - End
6051
6052 --========================================================================
6053 -- PROCEDURE : Validate_Freight_Code Private
6054 --
6055 -- PARAMETERS: p_freight_code Freight Code
6056 -- x_carrier_id In / Out Carrier id
6057 -- x_return_status return status
6058 --
6059 -- VERSION : current version 1.0
6060 -- initial version 1.0
6061 -- COMMENT : This procedure is used to validate carrier_id and freight_code
6062 --========================================================================
6063 PROCEDURE Validate_Freight_Code(
6064 p_freight_code IN VARCHAR2,
6065 x_carrier_id IN OUT NOCOPY NUMBER,
6066 x_return_status OUT NOCOPY VARCHAR2)
6067 IS
6068 --
6069 l_debug_on BOOLEAN;
6070 --
6071 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Freight_Code';
6072 --
6073 CURSOR l_carrier_csr is
6074 SELECT carrier_id
6075 FROM wsh_carriers_v
6076 WHERE nvl(x_carrier_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
6077 AND carrier_id = x_carrier_id
6078 AND nvl(generic_flag, 'N') = 'N'
6079 AND active = 'A'
6080 UNION ALL
6081 SELECT carrier_id
6082 FROM wsh_carriers_v
6083 WHERE nvl(x_carrier_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
6084 AND freight_code = p_freight_code
6085 AND nvl(generic_flag, 'N') = 'N'
6086 AND active = 'A';
6087
6088 BEGIN
6089
6090 --
6091 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
6092 --
6093 IF l_debug_on IS NULL
6094 THEN
6095 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
6096 END IF;
6097 --
6098 IF l_debug_on THEN
6099 WSH_DEBUG_SV.push(l_module_name);
6100 WSH_DEBUG_SV.log(l_module_name,'p_freight_code', p_freight_code);
6101 WSH_DEBUG_SV.log(l_module_name,'x_carrier_id', x_carrier_id);
6102 END IF;
6103
6104 x_return_status := wsh_util_core.g_ret_sts_success;
6105
6106 IF (nvl(x_carrier_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num OR NVL(p_freight_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char) THEN
6107 --{
6108 OPEN l_carrier_csr;
6109 FETCH l_carrier_csr INTO x_carrier_id;
6110 IF l_carrier_csr%NOTFOUND THEN
6111 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NOT_FOUND');
6112 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6113 wsh_util_core.add_message(x_return_status,l_module_name);
6114 END IF;
6115 CLOSE l_carrier_csr;
6116 --}
6117 END IF;
6118
6119 IF l_debug_on THEN
6120 WSH_DEBUG_SV.pop(l_module_name);
6121 END IF;
6122
6123 EXCEPTION
6124
6125 WHEN OTHERS THEN
6126 --{
6127 IF l_carrier_csr%ISOPEN THEN
6128 CLOSE l_carrier_csr;
6129 END IF;
6130 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6131
6132 wsh_util_core.default_handler('WSH_UTIL_VALIDATE.Validate_Freight_Code');
6133 IF l_debug_on THEN
6134 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6135 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6136 END IF;
6137 --}
6138 END Validate_Freight_Code;
6139
6140 END WSH_UTIL_VALIDATE;