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