[Home] [Help]
PACKAGE BODY: APPS.WSH_FREIGHT_COSTS_GRP
Source
1 PACKAGE BODY WSH_FREIGHT_COSTS_GRP as
2 /* $Header: WSHFCGPB.pls 120.12 2012/01/24 12:54:40 shtadepa noship $ */
3 -- standard global constants
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_FREIGHT_COSTS_GRP';
5
6
7 --===================
8 -- PROCEDURES
9 --===================
10 PROCEDURE Validate_freight_cost_type(
11 p_freight_cost_type IN VARCHAR2
12 , x_freight_cost_type_id IN OUT NOCOPY NUMBER
13 , x_return_status OUT NOCOPY VARCHAR2
14 )
15 IS
16 invalid_type EXCEPTION;
17 l_type_id NUMBER;
18 --
19 l_debug_on BOOLEAN;
20 --
21 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_FREIGHT_COST_TYPE';
22 --
23 BEGIN
24 --
25 --
26 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
27 --
28 IF l_debug_on IS NULL
29 THEN
30 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
31 END IF;
32 --
33 IF l_debug_on THEN
34 WSH_DEBUG_SV.push(l_module_name);
35 --
36 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE',P_FREIGHT_COST_TYPE);
37 WSH_DEBUG_SV.log(l_module_name,'X_FREIGHT_COST_TYPE_ID',X_FREIGHT_COST_TYPE_ID);
38 END IF;
39 --
40 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
41
42 IF (x_freight_cost_type_id IS NULL) THEN
43 IF (p_freight_cost_type <> FND_API.G_MISS_CHAR) THEN
44 SELECT freight_cost_type_id INTO x_freight_cost_type_id
45 FROM wsh_freight_cost_types
46 WHERE name = p_freight_cost_type;
47 IF (SQL%NOTFOUND) THEN
48 RAISE invalid_type;
49 END IF;
50 END IF;
51 ELSE
52 SELECT freight_cost_type_id INTO l_type_id
53 FROM wsh_freight_cost_types
54 WHERE freight_cost_type_id = x_freight_cost_type_id;
55 IF (SQL%NOTFOUND) THEN
56 RAISE invalid_type;
57 END IF;
58 END IF;
59
60 IF l_debug_on THEN
61 WSH_DEBUG_SV.log(l_module_name,
62 'x_freight_cost_type_id',x_freight_cost_type_id);
63 END IF;
64 --
65 IF l_debug_on THEN
66 WSH_DEBUG_SV.pop(l_module_name);
67 END IF;
68 --
69 EXCEPTION
70 WHEN No_Data_Found THEN
71 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
72 --
73 IF l_debug_on THEN
74 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
75 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
76 END IF;
77 --
78 WHEN Invalid_Type THEN
79 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
80 --
81 IF l_debug_on THEN
82 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
83 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_TYPE');
84 END IF;
85 --
86 WHEN others THEN
87 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
88 --
89 IF l_debug_on THEN
90 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
91 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
92 END IF;
93 --
94 END validate_freight_cost_type;
95
96 PROCEDURE Delete_Freight_Costs (
97 p_api_version_number IN NUMBER
98 , p_init_msg_list IN VARCHAR2
99 , p_commit IN VARCHAR2
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_msg_count OUT NOCOPY NUMBER
102 , x_msg_data OUT NOCOPY VARCHAR2
103 , p_pub_freight_costs IN WSH_FREIGHT_COSTS_GRP.PubFreightCostRecType
104 )
105 IS
106 l_return_status VARCHAR2(30);
107 --
108 l_debug_on BOOLEAN;
109 --
110 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FREIGHT_COSTS';
111 --
112 BEGIN
113 --
114 --
115 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
116 --
117 IF l_debug_on IS NULL
118 THEN
119 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
120 END IF;
121 --
122 IF l_debug_on THEN
123 WSH_DEBUG_SV.push(l_module_name);
124 --
125 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
126 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
127 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
128 END IF;
129 --
130 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
131
132 WSH_FREIGHT_COSTS_PVT.Delete_freight_cost(
133 p_rowid => NULL,
134 p_freight_cost_id => p_pub_freight_costs.freight_cost_id,
135 x_return_status => x_return_status);
136
137 --
138 IF l_debug_on THEN
139 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
140 WSH_DEBUG_SV.pop(l_module_name);
141 END IF;
142 --
143 EXCEPTION
144 WHEN OTHERS THEN
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
146 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
147 FND_MSG_PUB.Add_Exc_Msg (
148 G_PKG_NAME,
149 '_x_'
150 );
151 END IF;
152 -- Get message count and data
153 FND_MSG_PUB.Count_And_Get (
154 p_count => x_msg_count,
155 p_data => x_msg_data
156 );
157 --
158 IF l_debug_on THEN
159 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
160 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
161 END IF;
162 --
163 END Delete_Freight_Costs;
164
165
166 --Harmonizing Project I :heali
167 PROCEDURE map_freightgrp_to_pvt(
168 p_grp_freight_rec IN PubFreightCostRecType,
169 x_pvt_freight_rec OUT NOCOPY WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type,
170 x_return_status OUT NOCOPY VARCHAR2) IS
171
172 --
173 l_debug_on BOOLEAN;
174 --
175 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME|| '.' || 'MAP_FREIGHTPUB_TO_PVT';
176 --
177 BEGIN
178 --
179 --
180 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
181 --
182 IF l_debug_on IS NULL
183 THEN
184 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
185 END IF;
186 --
187 IF l_debug_on THEN
188 WSH_DEBUG_SV.push(l_module_name);
189 WSH_DEBUG_SV.log(l_module_name,'p_grp_freight_rec.FREIGHT_COST_ID',p_grp_freight_rec.FREIGHT_COST_ID);
190 WSH_DEBUG_SV.log(l_module_name,'p_grp_freight_rec.FREIGHT_COST_TYPE_ID',p_grp_freight_rec.FREIGHT_COST_TYPE_ID);
191 END IF;
192 --
193 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
194
195 x_pvt_freight_rec.FREIGHT_COST_ID := p_grp_freight_rec.FREIGHT_COST_ID;
196 x_pvt_freight_rec.FREIGHT_COST_TYPE_ID := p_grp_freight_rec.FREIGHT_COST_TYPE_ID;
197 x_pvt_freight_rec.UNIT_AMOUNT := p_grp_freight_rec.UNIT_AMOUNT;
198 x_pvt_freight_rec.CALCULATION_METHOD := p_grp_freight_rec.CALCULATION_METHOD;
199 x_pvt_freight_rec.UOM := p_grp_freight_rec.UOM;
200 x_pvt_freight_rec.QUANTITY := p_grp_freight_rec.QUANTITY;
201 x_pvt_freight_rec.TOTAL_AMOUNT := p_grp_freight_rec.TOTAL_AMOUNT;
202 x_pvt_freight_rec.CURRENCY_CODE := p_grp_freight_rec.CURRENCY_CODE;
203 x_pvt_freight_rec.CONVERSION_DATE := p_grp_freight_rec.CONVERSION_DATE;
204 x_pvt_freight_rec.CONVERSION_RATE := p_grp_freight_rec.CONVERSION_RATE;
205 x_pvt_freight_rec.CONVERSION_TYPE_CODE := p_grp_freight_rec.CONVERSION_TYPE_CODE;
206 x_pvt_freight_rec.TRIP_ID := p_grp_freight_rec.TRIP_ID;
207 x_pvt_freight_rec.STOP_ID := p_grp_freight_rec.STOP_ID;
208 x_pvt_freight_rec.DELIVERY_ID := p_grp_freight_rec.DELIVERY_ID;
209 x_pvt_freight_rec.DELIVERY_LEG_ID := p_grp_freight_rec.DELIVERY_LEG_ID;
210 x_pvt_freight_rec.DELIVERY_DETAIL_ID := p_grp_freight_rec.DELIVERY_DETAIL_ID;
211 x_pvt_freight_rec.ATTRIBUTE_CATEGORY := p_grp_freight_rec.ATTRIBUTE_CATEGORY;
212 x_pvt_freight_rec.ATTRIBUTE1 := p_grp_freight_rec.ATTRIBUTE1;
213 x_pvt_freight_rec.ATTRIBUTE2 := p_grp_freight_rec.ATTRIBUTE2;
214 x_pvt_freight_rec.ATTRIBUTE3 := p_grp_freight_rec.ATTRIBUTE3;
215 x_pvt_freight_rec.ATTRIBUTE4 := p_grp_freight_rec.ATTRIBUTE4;
216 x_pvt_freight_rec.ATTRIBUTE5 := p_grp_freight_rec.ATTRIBUTE5;
217 x_pvt_freight_rec.ATTRIBUTE6 := p_grp_freight_rec.ATTRIBUTE6;
218 x_pvt_freight_rec.ATTRIBUTE7 := p_grp_freight_rec.ATTRIBUTE7;
219 x_pvt_freight_rec.ATTRIBUTE8 := p_grp_freight_rec.ATTRIBUTE8;
220 x_pvt_freight_rec.ATTRIBUTE9 := p_grp_freight_rec.ATTRIBUTE9;
221 x_pvt_freight_rec.ATTRIBUTE10 := p_grp_freight_rec.ATTRIBUTE10;
222 x_pvt_freight_rec.ATTRIBUTE11 := p_grp_freight_rec.ATTRIBUTE11;
223 x_pvt_freight_rec.ATTRIBUTE12 := p_grp_freight_rec.ATTRIBUTE12;
224 x_pvt_freight_rec.ATTRIBUTE13 := p_grp_freight_rec.ATTRIBUTE13;
225 x_pvt_freight_rec.ATTRIBUTE14 := p_grp_freight_rec.ATTRIBUTE14;
226 x_pvt_freight_rec.ATTRIBUTE15 := p_grp_freight_rec.ATTRIBUTE15;
227 x_pvt_freight_rec.CREATION_DATE := p_grp_freight_rec.CREATION_DATE;
228 x_pvt_freight_rec.CREATED_BY := p_grp_freight_rec.CREATED_BY;
229 x_pvt_freight_rec.LAST_UPDATE_DATE := p_grp_freight_rec.LAST_UPDATE_DATE;
230 x_pvt_freight_rec.LAST_UPDATED_BY := p_grp_freight_rec.LAST_UPDATED_BY;
231 x_pvt_freight_rec.LAST_UPDATE_LOGIN := p_grp_freight_rec.LAST_UPDATE_LOGIN;
232 x_pvt_freight_rec.PROGRAM_APPLICATION_ID := p_grp_freight_rec.PROGRAM_APPLICATION_ID;
233 x_pvt_freight_rec.PROGRAM_ID := p_grp_freight_rec.PROGRAM_ID;
234 x_pvt_freight_rec.PROGRAM_UPDATE_DATE := p_grp_freight_rec.PROGRAM_UPDATE_DATE;
235 x_pvt_freight_rec.REQUEST_ID := p_grp_freight_rec.REQUEST_ID;
236 x_pvt_freight_rec.PRICING_LIST_HEADER_ID := p_grp_freight_rec.PRICING_LIST_HEADER_ID;
237 x_pvt_freight_rec.PRICING_LIST_LINE_ID := p_grp_freight_rec.PRICING_LIST_LINE_ID;
238 x_pvt_freight_rec.APPLIED_TO_CHARGE_ID := p_grp_freight_rec.APPLIED_TO_CHARGE_ID;
239 x_pvt_freight_rec.CHARGE_UNIT_VALUE := p_grp_freight_rec.CHARGE_UNIT_VALUE;
240 x_pvt_freight_rec.CHARGE_SOURCE_CODE := p_grp_freight_rec.CHARGE_SOURCE_CODE;
241 x_pvt_freight_rec.LINE_TYPE_CODE := p_grp_freight_rec.LINE_TYPE_CODE;
242 x_pvt_freight_rec.ESTIMATED_FLAG := p_grp_freight_rec.ESTIMATED_FLAG;
243 x_pvt_freight_rec.FREIGHT_CODE := p_grp_freight_rec.FREIGHT_CODE;
244 x_pvt_freight_rec.TRIP_NAME := p_grp_freight_rec.TRIP_NAME;
245 x_pvt_freight_rec.DELIVERY_NAME := p_grp_freight_rec.DELIVERY_NAME;
246 x_pvt_freight_rec.FREIGHT_COST_TYPE := p_grp_freight_rec.FREIGHT_COST_TYPE;
247 x_pvt_freight_rec.STOP_LOCATION_ID := p_grp_freight_rec.STOP_LOCATION_ID;
248 x_pvt_freight_rec.PLANNED_DEP_DATE := p_grp_freight_rec.PLANNED_DEP_DATE;
249 x_pvt_freight_rec.COMMODITY_CATEGORY_ID := p_grp_freight_rec.COMMODITY_CATEGORY_ID;
250
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.pop(l_module_name);
253 END IF;
254 --
255 EXCEPTION
256 WHEN OTHERS THEN
257 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_FTE_INTEGRATION.map_freightgrp_to_pvt',l_module_name);
258 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
259 --
260 IF l_debug_on THEN
261 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
262 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
263 END IF;
264 --
265 END map_freightgrp_to_pvt;
266
267
268 PROCEDURE Get_Disabled_List (
269 p_freight_rec IN WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type
270 , p_action IN VARCHAR2 DEFAULT 'UPDATE'
271 , p_caller IN VARCHAR2
272 , x_freight_rec OUT NOCOPY WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type
273 , x_return_status OUT NOCOPY VARCHAR2
274 ) IS
275
276 l_debug_on BOOLEAN;
277 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
278
279 l_disabled_list WSH_UTIL_CORE.column_tab_type;
280 l_db_col_rec WSH_TRIPS_PVT.trip_rec_type;
281 l_return_status VARCHAR2(30);
282 l_field_name VARCHAR2(100);
283 l_conversion_type VARCHAR2(100); --Bugfix 8736256
284
285 CURSOR c_tbl_rec IS
286 SELECT FREIGHT_COST_ID
287 , FREIGHT_COST_TYPE_ID
288 , UNIT_AMOUNT
289 , CALCULATION_METHOD
290 , UOM
291 , QUANTITY
292 , TOTAL_AMOUNT
293 , CURRENCY_CODE
294 , CONVERSION_DATE
295 , CONVERSION_RATE
296 , CONVERSION_TYPE_CODE
297 , TRIP_ID
298 , STOP_ID
299 , DELIVERY_ID
300 , DELIVERY_LEG_ID
301 , DELIVERY_DETAIL_ID
302 , ATTRIBUTE_CATEGORY
303 , ATTRIBUTE1
304 , ATTRIBUTE2
305 , ATTRIBUTE3
306 , ATTRIBUTE4
307 , ATTRIBUTE5
308 , ATTRIBUTE6
309 , ATTRIBUTE7
310 , ATTRIBUTE8
311 , ATTRIBUTE9
312 , ATTRIBUTE10
313 , ATTRIBUTE11
314 , ATTRIBUTE12
315 , ATTRIBUTE13
316 , ATTRIBUTE14
317 , ATTRIBUTE15
318 , CREATION_DATE
319 , CREATED_BY
320 , LAST_UPDATE_DATE
321 , LAST_UPDATED_BY
322 , LAST_UPDATE_LOGIN
323 , PROGRAM_APPLICATION_ID
324 , PROGRAM_ID
325 , PROGRAM_UPDATE_DATE
326 , REQUEST_ID
327 , PRICING_LIST_HEADER_ID
328 , PRICING_LIST_LINE_ID
329 , APPLIED_TO_CHARGE_ID
330 , CHARGE_UNIT_VALUE
331 , CHARGE_SOURCE_CODE
332 , LINE_TYPE_CODE
333 , ESTIMATED_FLAG
334 , FREIGHT_CODE
335 , NULL TRIP_NAME
336 , NULL DELIVERY_NAME
337 , NULL FREIGHT_COST_TYPE
338 , NULL STOP_LOCATION_ID
339 , NULL PLANNED_DEP_DATE
340 , COMMODITY_CATEGORY_ID
341 , BILLABLE_QUANTITY
342 , BILLABLE_UOM
343 , BILLABLE_BASIS
344 FROM wsh_freight_costs
345 WHERE FREIGHT_COST_ID= p_freight_rec.freight_cost_id;
346
347 l_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
348
349 e_dp_no_entity EXCEPTION;
350 missing_freight_cost_id EXCEPTION; -- Bug 13590798
351 BEGIN
352 --
353 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
354 --
355 IF l_debug_on IS NULL
356 THEN
357 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
358 END IF;
359 --
360 IF l_debug_on THEN
361 WSH_DEBUG_SV.push(l_module_name);
362 WSH_DEBUG_SV.log(l_module_name,'freight_cost_id',p_freight_rec.freight_cost_id);
363 END IF;
364
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 IF p_action = 'UPDATE' THEN
368 IF p_freight_rec.freight_cost_id <> FND_API.G_MISS_NUM THEN -- Added for Bug 13590798
369 OPEN c_tbl_rec;
370 FETCH c_tbl_rec INTO x_freight_rec;
371 IF c_tbl_rec%NOTFOUND THEN
372 CLOSE c_tbl_rec;
373 RAISE e_dp_no_entity;
374 END IF;
375 CLOSE c_tbl_rec;
376 ELSE
377 raise missing_freight_cost_id ; -- Added for Bug 13590798
378 END IF;
379 END IF;
380
381 IF p_action = 'CREATE' THEN
382 x_freight_rec.CREATION_DATE:= SYSDATE;
383 x_freight_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
384
385 -- Standalone TPW FP bug 8579149
386 -- Found a regression of the bug 8736256 while working in TPW FP bug
387 -- Moving the fix done has a part of bug 8736256 inside if loop,
388 -- validating only if conversion_type_code is not null
389 --Bug 8640930
390 IF NOT(nvl(p_freight_rec.conversion_type_code,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR ) THEN
391
392 --Bugfix 8736256 Start -- Validate Conversion Type Code
393 BEGIN
394 SELECT conversion_type INTO l_conversion_type
395 FROM gl_daily_conversion_types
396 WHERE conversion_type = p_freight_rec.conversion_type_code;
397
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400
401 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
402 --
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occurred for conversion type.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
405 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
406 END IF;
407
408 END;
409 --Bugfix 8736256 End
410
411 x_freight_rec.CONVERSION_TYPE_CODE:= p_freight_rec.conversion_type_code;
412 x_freight_rec.CONVERSION_DATE:= SYSDATE;
413 END IF;
414
415 END IF;
416
417 x_freight_rec.LAST_UPDATE_DATE:= SYSDATE;
418 x_freight_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
419 x_freight_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.USER_ID;
420
421
422 IF ( p_freight_rec.FREIGHT_COST_TYPE_ID <> FND_API.G_MISS_NUM ) THEN
423 x_freight_rec.FREIGHT_COST_TYPE_ID := p_freight_rec.FREIGHT_COST_TYPE_ID;
424 END IF;
425 IF ( p_freight_rec.UNIT_AMOUNT <> FND_API.G_MISS_NUM
426 OR p_freight_rec.UNIT_AMOUNT IS NULL ) THEN
427 x_freight_rec.UNIT_AMOUNT:= p_freight_rec.UNIT_AMOUNT;
428 END IF;
429 IF ( p_freight_rec.CALCULATION_METHOD <> FND_API.G_MISS_CHAR
430 OR p_freight_rec.CALCULATION_METHOD IS NULL ) THEN
431 x_freight_rec.CALCULATION_METHOD:= p_freight_rec.CALCULATION_METHOD;
432 END IF;
433 IF ( p_freight_rec.UOM <> FND_API.G_MISS_CHAR
434 OR p_freight_rec.UOM IS NULL ) THEN
435 x_freight_rec.UOM:= p_freight_rec.UOM;
436 END IF;
437 IF ( p_freight_rec.QUANTITY <> FND_API.G_MISS_NUM
438 OR p_freight_rec.QUANTITY IS NULL) THEN
439 x_freight_rec.QUANTITY:= p_freight_rec.QUANTITY;
440 END IF;
441 IF ( p_freight_rec.TOTAL_AMOUNT <> FND_API.G_MISS_NUM
442 OR p_freight_rec.TOTAL_AMOUNT IS NULL) THEN
443 x_freight_rec.TOTAL_AMOUNT:= p_freight_rec.TOTAL_AMOUNT;
444 END IF;
445 IF ( p_freight_rec.CURRENCY_CODE <> FND_API.G_MISS_CHAR
446 OR p_freight_rec.CURRENCY_CODE IS NULL) THEN
447 x_freight_rec.CURRENCY_CODE:= p_freight_rec.CURRENCY_CODE;
448 END IF;
449 IF ( p_freight_rec.CONVERSION_RATE <> FND_API.G_MISS_NUM
450 OR p_freight_rec.CONVERSION_RATE IS NULL) THEN
451 x_freight_rec.CONVERSION_RATE:= p_freight_rec.CONVERSION_RATE;
452 END IF;
453 IF ( p_freight_rec.TRIP_ID <> FND_API.G_MISS_NUM
454 OR p_freight_rec.TRIP_ID IS NULL) THEN
455 x_freight_rec.TRIP_ID:= p_freight_rec.TRIP_ID;
456 END IF;
457
458 IF ( p_freight_rec.STOP_ID <> FND_API.G_MISS_NUM
459 OR p_freight_rec.STOP_ID IS NULL) THEN
460 x_freight_rec.STOP_ID:= p_freight_rec.STOP_ID;
461 END IF;
462
463 IF ( p_freight_rec.DELIVERY_ID <> FND_API.G_MISS_NUM
464 OR p_freight_rec.DELIVERY_ID IS NULL) THEN
465 x_freight_rec.DELIVERY_ID:= p_freight_rec.DELIVERY_ID;
466 END IF;
467
468 IF ( p_freight_rec.DELIVERY_LEG_ID <> FND_API.G_MISS_NUM
469 OR p_freight_rec.DELIVERY_LEG_ID IS NULL) THEN
470 x_freight_rec.DELIVERY_LEG_ID:= p_freight_rec.DELIVERY_LEG_ID;
471 END IF;
472 IF ( p_freight_rec.DELIVERY_DETAIL_ID <> FND_API.G_MISS_NUM
473 OR p_freight_rec.DELIVERY_DETAIL_ID IS NULL) THEN
474 x_freight_rec.DELIVERY_DETAIL_ID:= p_freight_rec.DELIVERY_DETAIL_ID;
475 END IF;
476 IF ( p_freight_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
477 OR p_freight_rec.ATTRIBUTE_CATEGORY IS NULL) THEN
478 x_freight_rec.ATTRIBUTE_CATEGORY:= p_freight_rec.ATTRIBUTE_CATEGORY;
479 END IF;
480 IF ( p_freight_rec.ATTRIBUTE1 <> FND_API.G_MISS_CHAR
481 OR p_freight_rec.ATTRIBUTE1 IS NULL) THEN
482 x_freight_rec.ATTRIBUTE1:= p_freight_rec.ATTRIBUTE1;
483 END IF;
484 IF ( p_freight_rec.ATTRIBUTE2 <> FND_API.G_MISS_CHAR
485 OR p_freight_rec.ATTRIBUTE2 IS NULL) THEN
486 x_freight_rec.ATTRIBUTE2:= p_freight_rec.ATTRIBUTE2;
487 END IF;
488 IF ( p_freight_rec.ATTRIBUTE3 <> FND_API.G_MISS_CHAR
489 OR p_freight_rec.ATTRIBUTE3 IS NULL) THEN
490 x_freight_rec.ATTRIBUTE3:= p_freight_rec.ATTRIBUTE3;
491 END IF;
492 IF ( p_freight_rec.ATTRIBUTE4 <> FND_API.G_MISS_CHAR
493 OR p_freight_rec.ATTRIBUTE4 IS NULL) THEN
494 x_freight_rec.ATTRIBUTE4:= p_freight_rec.ATTRIBUTE4;
495 END IF;
496 IF ( p_freight_rec.ATTRIBUTE5 <> FND_API.G_MISS_CHAR
497 OR p_freight_rec.ATTRIBUTE5 IS NULL) THEN
498 x_freight_rec.ATTRIBUTE5:= p_freight_rec.ATTRIBUTE5;
499 END IF;
500 IF ( p_freight_rec.ATTRIBUTE6 <> FND_API.G_MISS_CHAR
501 OR p_freight_rec.ATTRIBUTE6 IS NULL) THEN
502 x_freight_rec.ATTRIBUTE6:= p_freight_rec.ATTRIBUTE6;
503 END IF;
504 IF ( p_freight_rec.ATTRIBUTE7 <> FND_API.G_MISS_CHAR
505 OR p_freight_rec.ATTRIBUTE7 IS NULL) THEN
506 x_freight_rec.ATTRIBUTE7:= p_freight_rec.ATTRIBUTE7;
507 END IF;
508 IF ( p_freight_rec.ATTRIBUTE8 <> FND_API.G_MISS_CHAR
509 OR p_freight_rec.ATTRIBUTE8 IS NULL) THEN
510 x_freight_rec.ATTRIBUTE8:= p_freight_rec.ATTRIBUTE8;
511 END IF;
512 IF ( p_freight_rec.ATTRIBUTE9 <> FND_API.G_MISS_CHAR
513 OR p_freight_rec.ATTRIBUTE9 IS NULL) THEN
514 x_freight_rec.ATTRIBUTE9:= p_freight_rec.ATTRIBUTE9;
515 END IF;
516 IF ( p_freight_rec.ATTRIBUTE10 <> FND_API.G_MISS_CHAR
517 OR p_freight_rec.ATTRIBUTE10 IS NULL) THEN
518 x_freight_rec.ATTRIBUTE10:= p_freight_rec.ATTRIBUTE10;
519 END IF;
520 IF ( p_freight_rec.ATTRIBUTE11 <> FND_API.G_MISS_CHAR
521 OR p_freight_rec.ATTRIBUTE11 IS NULL) THEN
522 x_freight_rec.ATTRIBUTE11:= p_freight_rec.ATTRIBUTE11;
523 END IF;
524 IF ( p_freight_rec.ATTRIBUTE12 <> FND_API.G_MISS_CHAR
525 OR p_freight_rec.ATTRIBUTE12 IS NULL) THEN
526 x_freight_rec.ATTRIBUTE12:= p_freight_rec.ATTRIBUTE12;
527 END IF;
528 IF ( p_freight_rec.ATTRIBUTE13 <> FND_API.G_MISS_CHAR
529 OR p_freight_rec.ATTRIBUTE13 IS NULL) THEN
530 x_freight_rec.ATTRIBUTE13:= p_freight_rec.ATTRIBUTE13;
531 END IF;
532 IF ( p_freight_rec.ATTRIBUTE14 <> FND_API.G_MISS_CHAR
533 OR p_freight_rec.ATTRIBUTE14 IS NULL) THEN
534 x_freight_rec.ATTRIBUTE14:= p_freight_rec.ATTRIBUTE14;
535 END IF;
536 IF ( p_freight_rec.ATTRIBUTE15 <> FND_API.G_MISS_CHAR
537 OR p_freight_rec.ATTRIBUTE15 IS NULL) THEN
538 x_freight_rec.ATTRIBUTE15:= p_freight_rec.ATTRIBUTE15;
539 END IF;
540
541 IF ( p_freight_rec.PROGRAM_APPLICATION_ID <> FND_API.G_MISS_NUM
542 OR p_freight_rec.PROGRAM_APPLICATION_ID IS NULL) THEN
543 x_freight_rec.PROGRAM_APPLICATION_ID:= p_freight_rec.PROGRAM_APPLICATION_ID;
544 END IF;
545 IF ( p_freight_rec.PROGRAM_ID <> FND_API.G_MISS_NUM
546 OR p_freight_rec.PROGRAM_ID IS NULL) THEN
547 x_freight_rec.PROGRAM_ID:= p_freight_rec.PROGRAM_ID;
548 END IF;
549 IF ( p_freight_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
550 OR p_freight_rec.PROGRAM_UPDATE_DATE IS NULL) THEN
551 x_freight_rec.PROGRAM_UPDATE_DATE:= p_freight_rec.PROGRAM_UPDATE_DATE;
552 END IF;
553 IF ( p_freight_rec.REQUEST_ID <> FND_API.G_MISS_NUM
554 OR p_freight_rec.REQUEST_ID IS NULL) THEN
555 x_freight_rec.REQUEST_ID:= p_freight_rec.REQUEST_ID;
556 END IF;
557 IF ( p_freight_rec.FREIGHT_CODE <> FND_API.G_MISS_CHAR
558 OR p_freight_rec.FREIGHT_CODE IS NULL) THEN
559 x_freight_rec.FREIGHT_CODE:= p_freight_rec.FREIGHT_CODE;
560 END IF;
561 IF ( p_freight_rec.LINE_TYPE_CODE <> FND_API.G_MISS_CHAR
562 OR p_freight_rec.LINE_TYPE_CODE IS NULL) THEN
563 x_freight_rec.LINE_TYPE_CODE:= p_freight_rec.FREIGHT_CODE;
564 END IF;
565 IF ( p_freight_rec.PRICING_LIST_HEADER_ID <> FND_API.G_MISS_NUM
566 OR p_freight_rec.PRICING_LIST_HEADER_ID IS NULL) THEN
567 x_freight_rec.PRICING_LIST_HEADER_ID:= p_freight_rec.PRICING_LIST_HEADER_ID;
568 END IF;
569 IF ( p_freight_rec.PRICING_LIST_LINE_ID <> FND_API.G_MISS_NUM
570 OR p_freight_rec.PRICING_LIST_LINE_ID IS NULL) THEN
571 x_freight_rec.PRICING_LIST_LINE_ID:= p_freight_rec.PRICING_LIST_LINE_ID;
572 END IF;
573 IF ( p_freight_rec.APPLIED_TO_CHARGE_ID <> FND_API.G_MISS_NUM
574 OR p_freight_rec.APPLIED_TO_CHARGE_ID IS NULL) THEN
575 x_freight_rec.APPLIED_TO_CHARGE_ID:= p_freight_rec.APPLIED_TO_CHARGE_ID;
576 END IF;
577 IF ( p_freight_rec.CHARGE_UNIT_VALUE <> FND_API.G_MISS_NUM
578 OR p_freight_rec.CHARGE_UNIT_VALUE IS NULL) THEN
579 x_freight_rec.CHARGE_UNIT_VALUE:= p_freight_rec.CHARGE_UNIT_VALUE;
580 END IF;
581 IF ( p_freight_rec.CHARGE_SOURCE_CODE <> FND_API.G_MISS_CHAR
582 OR p_freight_rec.CHARGE_SOURCE_CODE IS NULL) THEN
583 x_freight_rec.CHARGE_SOURCE_CODE:= p_freight_rec.CHARGE_SOURCE_CODE;
584 END IF;
585 IF ( p_freight_rec.ESTIMATED_FLAG <> FND_API.G_MISS_CHAR
586 OR p_freight_rec.ESTIMATED_FLAG IS NULL) THEN
587 x_freight_rec.ESTIMATED_FLAG:= p_freight_rec.ESTIMATED_FLAG;
588 END IF;
589 IF ( p_freight_rec.FREIGHT_COST_TYPE <> FND_API.G_MISS_CHAR
590 OR p_freight_rec.FREIGHT_COST_TYPE IS NULL) THEN
591 x_freight_rec.FREIGHT_COST_TYPE := p_freight_rec.FREIGHT_COST_TYPE;
592 END IF;
593 IF ( p_freight_rec.COMMODITY_CATEGORY_ID <> FND_API.G_MISS_NUM
594 OR p_freight_rec.COMMODITY_CATEGORY_ID IS NULL) THEN
595 x_freight_rec.COMMODITY_CATEGORY_ID := p_freight_rec.COMMODITY_CATEGORY_ID;
596 END IF;
597
598 --bug 3614196
599 --trip_name, stop_location_id, planned_dep_date, delivery_name
600 --need to copied to output record
601 IF ( p_freight_rec.TRIP_NAME <> FND_API.G_MISS_CHAR
602 OR p_freight_rec.TRIP_NAME IS NULL) THEN
603 x_freight_rec.TRIP_NAME:= p_freight_rec.TRIP_NAME;
604 END IF;
605
606 IF ( p_freight_rec.STOP_LOCATION_ID <> FND_API.G_MISS_NUM
607 OR p_freight_rec.STOP_LOCATION_ID IS NULL) THEN
608 x_freight_rec.STOP_LOCATION_ID:= p_freight_rec.STOP_LOCATION_ID;
609 END IF;
610
611 IF ( p_freight_rec.PLANNED_DEP_DATE <> FND_API.G_MISS_DATE
612 OR p_freight_rec.PLANNED_DEP_DATE IS NULL) THEN
613 x_freight_rec.PLANNED_DEP_DATE:= p_freight_rec.PLANNED_DEP_DATE;
614 END IF;
615
616 IF ( p_freight_rec.DELIVERY_NAME <> FND_API.G_MISS_CHAR
617 OR p_freight_rec.DELIVERY_NAME IS NULL) THEN
618 x_freight_rec.DELIVERY_NAME:= p_freight_rec.DELIVERY_NAME;
619 END IF;
620
621
622 IF (p_caller IN ('FTE_RATING')) THEN
623
624 IF (p_freight_rec.BILLABLE_QUANTITY <> FND_API.G_MISS_NUM
625 OR p_freight_rec.BILLABLE_QUANTITY IS NULL) THEN
626 x_freight_rec.BILLABLE_QUANTITY := p_freight_rec.BILLABLE_QUANTITY;
627 END IF;
628
629 IF (p_freight_rec.BILLABLE_UOM <> FND_API.G_MISS_CHAR
630 OR p_freight_rec.BILLABLE_UOM IS NULL) THEN
631 x_freight_rec.BILLABLE_UOM := p_freight_rec.BILLABLE_UOM;
632 END IF;
633
634 IF (p_freight_rec.BILLABLE_BASIS <> FND_API.G_MISS_CHAR
635 OR p_freight_rec.BILLABLE_BASIS IS NULL) THEN
636 x_freight_rec.BILLABLE_BASIS := p_freight_rec.BILLABLE_BASIS;
637 END IF;
638
639 END IF;
640
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.pop(l_module_name);
643 END IF;
644
645 EXCEPTION
646 WHEN e_dp_no_entity THEN
647 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
648
649 -- Added for bug 13590798
650 WHEN missing_freight_cost_id THEN
651 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652
653 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
654 FND_MSG_PUB.Add_Exc_Msg (
655 G_PKG_NAME,
656 'Missing FREIGHT COST ID'
657 );
658 END IF;
659 --
660 IF l_debug_on THEN
661 WSH_DEBUG_SV.logmsg(l_module_name,'Exception occured: Missing FREIGHT COST ID ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
662 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MISSING_FREIGHT_COST_ID');
663 END IF;
664 --
665
666 WHEN OTHERS THEN
667 wsh_util_core.default_handler('WSH_FREIGHT_COSTS_GRP.get_disabled_list',l_module_name);
668 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
669 IF l_debug_on THEN
670 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
671 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
672 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
673 END IF;
674 END Get_Disabled_List;
675
676 PROCEDURE Create_Update_Freight_Costs(
677 p_api_version_number IN NUMBER,
678 p_init_msg_list IN VARCHAR2,
679 p_commit IN VARCHAR2,
680 p_freight_info_tab IN freight_rec_tab_type,
681 p_in_rec IN freightInRecType,
682 x_out_tab OUT NOCOPY freight_out_tab_type,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2) IS
686
687 l_api_version_number CONSTANT NUMBER := 1.0;
688 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Freight_Costs';
689 l_debug_on BOOLEAN;
690 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_FREIGHT_COSTS';
691
692 RECORD_LOCKED EXCEPTION;
693 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
694
695
696 CURSOR get_freight_cost_type_del(p_freight_cost_type_id NUMBER) IS
697 SELECT currency_code, amount
698 FROM wsh_freight_cost_types
699 WHERE freight_cost_type_id=p_freight_cost_type_id;
700
701 --J-IB-JCKWOK
702 CURSOR c_detail_rec(v_detail_id NUMBER ) IS
703 SELECT delivery_detail_id ,
704 organization_id,
705 released_status,
706 container_flag,
707 source_code,
708 lpn_id,
709 line_direction,
710 ship_from_location_id,
711 move_order_line_id, -- R12, X-dock project
712 NULL, -- OTM R12
713 client_id -- LSP PROJECT :Just added for dependency
714 FROM wsh_delivery_details
715 WHERE delivery_detail_id = v_detail_id;
716
717 CURSOR c_trip_rec(v_trip_id NUMBER ) IS
718 SELECT trip_id,
719 NULL, -- organization_id,
720 status_code,
721 planned_flag,
722 load_tender_status, -- R12 Select Carrier dependent change
723 lane_id,
724 shipments_type_flag,
725 NVL(ignore_for_planning, 'N') --OTM R12,glog proj
726 FROM wsh_trips
727 WHERE trip_id = v_trip_id;
728
729 CURSOR c_stop_rec(v_stop_id NUMBER ) IS
730 SELECT stop_id,
731 NULL, -- organization_id,
732 status_code,
733 shipments_type_flag
734 FROM wsh_trip_stops
735 WHERE stop_id = v_stop_id;
736
737 CURSOR c_del_rec(v_del_id NUMBER ) IS
738 SELECT delivery_id,
739 organization_id,
740 status_code,
741 planned_flag,
742 shipment_direction,
743 delivery_type, -- MDC
744 NVL(ignore_for_planning, 'N'), --OTM R12, glog proj
745 NVL(tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT), --OTM R12, glog proj
746 NULL, -- --OTM R12,
747 client_id -- LSP PROJECT : Just added for dependency.
748 FROM wsh_new_deliveries
749 WHERE delivery_id = v_del_id;
750
751 l_detail_rec WSH_DETAILS_VALIDATIONS.detail_rec_type;
752 l_del_rec WSH_DELIVERY_VALIDATIONS.dlvy_rec_type;
753 l_trip_rec WSH_TRIP_VALIDATIONS.trip_rec_type;
754 l_stop_rec WSH_TRIP_STOPS_VALIDATIONS.stop_rec_type;
755 l_detail_rec_tab WSH_DETAILS_VALIDATIONS.detail_rec_tab_type;
756 l_del_rec_tab WSH_DELIVERY_VALIDATIONS.dlvy_rec_tab_type;
757 l_trip_rec_tab WSH_TRIP_VALIDATIONS.trip_rec_tab_type;
758 l_stop_rec_tab WSH_TRIP_STOPS_VALIDATIONS.stop_rec_tab_type;
759
760 l_valid_index_tab wsh_util_core.id_tab_type;
761 l_valid_id_tab wsh_util_core.id_tab_type;
762 l_error_ids wsh_util_core.id_tab_type;
763 --J-IB-JCKWOK
764
765 l_currency_code VARCHAR2(30);
766 l_amount NUMBER;
767 l_return_status VARCHAR2(30);
768 l_counter NUMBER;
769 l_counts NUMBER;
770 l_num_entity NUMBER:=0;
771 l_trip_id NUMBER := NULL;
772 l_stop_id NUMBER := NULL;
773 l_delivery_id NUMBER := NULL;
774 l_rowid VARCHAR2(30) := NULL;
775 l_num_errors NUMBER :=0;
776 l_num_warnings NUMBER :=0;
777 l_index NUMBER;
778 l_freight_info_tab freight_rec_tab_type;
779 --
780 --OTM R12, glog proj
781 l_adjusted_amount NUMBER;
782 --
783 l_status VARCHAR2(1):= 'N'; --Bugfix 6816437
784 l_name VARCHAR2(1000):= NULL; --Bugfix 6816437
785 l_stop_loc_id NUMBER := NULL; --Bugfix 6816437
786 l_entity_name VARCHAR2(30):= NULL; --Bugfix 6816437
787 l_con_flag VARCHAR2(1) := 'N'; --Bugfix 6816437
788
789 BEGIN
790 --
791 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
792 --
793 IF l_debug_on IS NULL
794 THEN
795 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
796 END IF;
797 --
798 SAVEPOINT Create_Update_Freight_Costs_Gp;
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.push(l_module_name);
801 --
802 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
803 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
804 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
805 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.caller',p_in_rec.caller);
806 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.action_code',p_in_rec.action_code);
807 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.phase',p_in_rec.phase);
808 END IF;
809 --
810 IF NOT FND_API.Compatible_API_Call(l_api_version_number, p_api_version_number,l_api_name,G_PKG_NAME) THEN
811 IF l_debug_on THEN
812 WSH_DEBUG_SV.log(l_module_name,'Not compatible');
813 END IF;
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF;
816
817 IF FND_API.to_Boolean(p_init_msg_list) THEN
818 FND_MSG_PUB.initialize;
819 END IF;
820
821 IF (p_in_rec.caller IS NULL) THEN
822 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
823 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.caller');
824 wsh_util_core.add_message(x_return_status,l_module_name);
825 raise fnd_api.g_exc_error;
826 END IF;
827 IF (nvl(p_in_rec.phase,1) <> 1) THEN
828 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
829 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.phase');
830 wsh_util_core.add_message(x_return_status,l_module_name);
831 raise fnd_api.g_exc_error;
832 END IF;
833 IF (p_in_rec.action_code IS NULL OR p_in_rec.action_code NOT IN ('CREATE','UPDATE') ) THEN
834 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
835 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.action_code');
836 wsh_util_core.add_message(x_return_status,l_module_name);
837 raise fnd_api.g_exc_error;
838 END IF;
839
840 WSH_ACTIONS_LEVELS.set_validation_level (
841 p_entity => 'FRST',
842 p_caller => p_in_rec.caller,
843 p_phase => p_in_rec.phase,
844 p_action => p_in_rec.action_code,
845 x_return_status => l_return_status);
846
847 IF l_debug_on THEN
848 WSH_DEBUG_SV.log(l_module_name,'WSH_ACTIONS_LEVELS.set_validation_level l_return_status',l_return_status);
849 WSH_DEBUG_SV.log(l_module_name,'C_FREIGHT_UNIT_AMT_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_UNIT_AMT_LVL));
850 WSH_DEBUG_SV.log(l_module_name,'C_FREIGHT_CONV_RATE_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CONV_RATE_LVL));
851 WSH_DEBUG_SV.log(l_module_name,'C_FREIGHT_CURR_CODE_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CURR_CODE_LVL));
852 WSH_DEBUG_SV.log(l_module_name,'C_PARENT_ENTITY_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL));
853 WSH_DEBUG_SV.log(l_module_name,'C_FREIGHT_COST_TYPE_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_COST_TYPE_LVL));
854 WSH_DEBUG_SV.log(l_module_name,'C_ACTION_ENABLED_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL));
855 END IF;
856
857 WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
858 x_num_warnings =>l_num_warnings,
859 x_num_errors =>l_num_errors);
860
861 l_freight_info_tab:=p_freight_info_tab;
862 l_index := p_freight_info_tab.FIRST;
863 --
864 WHILE l_index IS NOT NULL LOOP
865 --
866 BEGIN
867 --
868 SAVEPOINT create_update_freight_loop;
869
870 --
871 --J-IB-JCKWOK
872 --
873 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DISABLED_LIST_LVL) = 1 ) THEN
874 Get_Disabled_List (
875 p_freight_rec => p_freight_info_tab(l_index),
876 p_action => p_in_rec.action_code,
877 p_caller => p_in_rec.caller,
878 x_freight_rec => l_freight_info_tab(l_index),
879 x_return_status => l_return_status);
880 END IF;
881
882
883 WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
884 x_num_warnings =>l_num_warnings,
885 x_num_errors =>l_num_errors);
886
887 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_COST_TYPE_LVL)=1) THEN
888 IF (l_freight_info_tab(l_index).freight_cost_type IS NULL AND
889 l_freight_info_tab(l_index).freight_cost_type_id IS NULL) THEN
890
891 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
892 WSH_UTIL_CORE.api_post_call(p_return_status => l_return_status,
893 x_num_warnings =>l_num_warnings,
894 x_num_errors =>l_num_errors,
895 p_module_name => l_module_name,
896 p_msg_data => 'WSH_REQUIRED_FIELD_NULL',
897 p_token1 => 'FIELD_NAME',
898 p_value1 => 'freight_cost_type');
899 ELSE
900 validate_freight_cost_type(
901 p_freight_cost_type => l_freight_info_tab(l_index).freight_cost_type,
902 x_freight_cost_type_id => l_freight_info_tab(l_index).freight_cost_type_id,
903 x_return_status => l_return_status);
904 IF l_debug_on THEN
905 WSH_DEBUG_SV.log(l_module_name,'validate_freight_cost_type l_return_status',l_return_status);
906 END IF;
907
908 WSH_UTIL_CORE.api_post_call(
909 p_return_status => l_return_status,
910 x_num_warnings => l_num_warnings,
911 x_num_errors => l_num_errors,
912 p_module_name => l_module_name,
913 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
914 p_token1 => 'PARAMETER',
915 p_value1 => 'freight_cost_type');
916
917 IF (l_freight_info_tab(l_index).currency_code IS NULL
918 OR l_freight_info_tab(l_index).unit_amount IS NULL) THEN
919 OPEN get_freight_cost_type_del(l_freight_info_tab(l_index).freight_cost_type_id);
920 FETCH get_freight_cost_type_del INTO l_currency_code,l_amount;
921 CLOSE get_freight_cost_type_del;
922
923 l_freight_info_tab(l_index).currency_code :=nvl(l_freight_info_tab(l_index).currency_code,
924 l_currency_code);
925 l_freight_info_tab(l_index).unit_amount :=nvl(l_freight_info_tab(l_index).unit_amount,l_amount);
926 END IF;
927 END IF;
928 END IF;
929
930 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_UNIT_AMT_LVL)=1) THEN
931 --Bug 3266333
932 WSH_UTIL_VALIDATE.validate_negative(
933 p_value => l_freight_info_tab(l_index).unit_amount,
934 p_field_name => 'unit_amount',
935 x_return_status => l_return_status );
936 --
937 IF l_debug_on THEN
938 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Negative x_return_status',l_return_status);
939 END IF;
940 WSH_UTIL_CORE.api_post_call(
941 p_return_status => l_return_status,
942 x_num_warnings => l_num_warnings,
943 x_num_errors => l_num_errors,
944 p_module_name => l_module_name,
945 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
946 p_token1 => 'PARAMETER',
947 p_value1 => 'unit_amount');
948 END IF;
949
950 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CONV_RATE_LVL)=1) THEN
951 --Bug 3266333
952 WSH_UTIL_VALIDATE.validate_negative(
953 p_value => l_freight_info_tab(l_index).conversion_rate,
954 p_field_name => 'conversion_rate',
955 x_return_status => l_return_status );
956 --
957 IF l_debug_on THEN
958 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Negative x_return_status',l_return_status);
959 END IF;
960 WSH_UTIL_CORE.api_post_call(
961 p_return_status => l_return_status,
962 x_num_warnings => l_num_warnings,
963 x_num_errors => l_num_errors,
964 p_module_name => l_module_name,
965 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
966 p_token1 => 'PARAMETER',
967 p_value1 => 'conversion_rate');
968 END IF;
969
970 IF ((WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CURR_CODE_LVL)=1)
971 AND (l_freight_info_tab(l_index).currency_code <>FND_API.G_MISS_CHAR)) THEN
972 WSH_UTIL_VALIDATE.validate_currency(
973 p_currency_code => l_freight_info_tab(l_index).currency_code,
974 p_currency_name => NULL,
975 p_amount => l_freight_info_tab(l_index).unit_amount,
976 x_return_status => l_return_status,
977 x_adjusted_amount => l_adjusted_amount); -- OTM R12, glog proj
978
979 IF l_debug_on THEN
980 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Currency l_return_status',l_return_status);
981 END IF;
982
983 WSH_UTIL_CORE.api_post_call(
984 p_return_status => l_return_status,
985 x_num_warnings => l_num_warnings,
986 x_num_errors => l_num_errors,
987 p_module_name => l_module_name,
988 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
989 p_token1 => 'PARAMETER',
990 p_value1 => 'currency_code');
991 END IF;
992
993 IF ((l_freight_info_tab(l_index).trip_id IS NOT NULL ) OR l_freight_info_tab(l_index).trip_name IS NOT NULL) THEN
994 l_num_entity := l_num_entity +1;
995 END IF;
996 IF ((l_freight_info_tab(l_index).stop_id IS NOT NULL) OR
997 l_freight_info_tab(l_index).stop_location_id IS NOT NULL) THEN
998 l_num_entity := l_num_entity +1;
999 END IF;
1000 IF ((l_freight_info_tab(l_index).delivery_id IS NOT NULL) OR
1001 l_freight_info_tab(l_index).delivery_name IS NOT NULL) THEN
1002 l_num_entity := l_num_entity +1;
1003 END IF;
1004 IF (l_freight_info_tab(l_index).delivery_leg_id IS NOT NULL) THEN
1005 l_num_entity := l_num_entity +1;
1006 END IF;
1007 IF (l_freight_info_tab(l_index).delivery_detail_id IS NOT NULL) THEN
1008 l_num_entity := l_num_entity +1;
1009 END IF;
1010
1011 IF (l_num_entity > 1 ) THEN
1012 WSH_UTIL_CORE.api_post_call(
1013 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1014 x_num_warnings => l_num_warnings,
1015 x_num_errors => l_num_errors,
1016 p_module_name => l_module_name,
1017 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1018
1019 END IF;
1020
1021
1022 l_num_entity := 0;
1023
1024 IF ((l_freight_info_tab(l_index).trip_id IS NOT NULL ) OR l_freight_info_tab(l_index).trip_name IS NOT NULL) THEN
1025 IF (l_freight_info_tab(l_index).trip_id IS NULL) THEN
1026 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1027 WSH_UTIL_VALIDATE.Validate_Trip_name(
1028 p_trip_id => l_trip_id,
1029 p_trip_name => l_freight_info_tab(l_index).trip_name,
1030 x_return_status => l_return_status);
1031
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Trip_name x_return_status',l_return_status);
1034 END IF;
1035 WSH_UTIL_CORE.api_post_call(
1036 p_return_status => l_return_status,
1037 x_num_warnings => l_num_warnings,
1038 x_num_errors => l_num_errors,
1039 p_module_name => l_module_name,
1040 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
1041 p_token1 => 'PARAMETER',
1042 p_value1 => 'trip_name');
1043 END IF;
1044 ELSE
1045 l_trip_id := l_freight_info_tab(l_index).trip_id;
1046 END IF;
1047
1048 l_num_entity := l_num_entity + 1;
1049
1050 --Bugfix 6816437 Start --Code has been written to check the oe interface flag when inserting/updating Freight Cost Record and display warning
1051 IF p_in_rec.caller = 'PLSQL' THEN
1052 BEGIN
1053 SELECT 'Y',wt.name into l_status,l_name
1054 FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_new_deliveries wnd,
1055 wsh_delivery_assignments wda, wsh_delivery_details wdd
1056 WHERE wt.trip_id = l_trip_id
1057 AND wts.trip_id = wt.trip_id
1058 AND wdl.pick_up_stop_id = wts.stop_id
1059 AND wnd.delivery_id = wdl.delivery_id
1060 AND wda.delivery_id = wnd.delivery_id
1061 AND wdd.delivery_detail_id = wda.delivery_detail_id
1062 AND wdd.oe_interfaced_flag = 'Y'
1063 AND ROWNUM = 1;
1064
1065 IF l_status = 'Y' THEN
1066 WSH_UTIL_CORE.api_post_call(p_return_status => WSH_UTIL_CORE.G_RET_STS_WARNING,
1067 x_num_warnings => l_num_warnings,
1068 x_num_errors => l_num_errors,
1069 p_module_name => l_module_name,
1070 p_msg_data => 'WSH_FC_OTHER_WARN',
1071 p_token1 => 'ENTITY_NAME',
1072 p_value1 => 'trip',
1073 p_token2 => 'ENTITY_ID',
1074 p_value2 => l_name);
1075 END IF;
1076 EXCEPTION
1077 WHEN NO_DATA_FOUND THEN
1078 NULL;
1079 END;
1080 END IF;
1081 --Bugfix 6816437 End
1082
1083 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1084 SELECT COUNT(*) INTO l_counts
1085 FROM wsh_trips
1086 WHERE trip_id = l_trip_id
1087 AND ROWNUM = 1;
1088
1089 IF (l_counts = 0) THEN
1090 WSH_UTIL_CORE.api_post_call(
1091 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1092 x_num_warnings => l_num_warnings,
1093 x_num_errors => l_num_errors,
1094 p_module_name => l_module_name,
1095 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1096 END IF;
1097 END IF;
1098
1099 ELSIF ((l_freight_info_tab(l_index).stop_id IS NOT NULL) OR l_freight_info_tab(l_index).stop_location_id IS NOT NULL) THEN
1100 IF (l_freight_info_tab(l_index).stop_id IS NULL) THEN
1101 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1102 WSH_UTIL_VALIDATE.Validate_stop_name(
1103 p_stop_id =>l_stop_id,
1104 p_trip_id =>l_freight_info_tab(l_index).trip_name,
1105 p_stop_location_id =>l_freight_info_tab(l_index).stop_location_id,
1106 p_planned_dep_date =>l_freight_info_tab(l_index).planned_dep_date,
1107 x_return_status =>l_return_status);
1108
1109 IF l_debug_on THEN
1110 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_stop_name x_return_status',l_return_status);
1111 END IF;
1112 WSH_UTIL_CORE.api_post_call(
1113 p_return_status => l_return_status,
1114 x_num_warnings => l_num_warnings,
1115 x_num_errors => l_num_errors,
1116 p_module_name => l_module_name,
1117 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
1118 p_token1 => 'PARAMETER',
1119 p_value1 => 'stop_name');
1120 END IF;
1121 ELSE
1122 l_stop_id := l_freight_info_tab(l_index).stop_id;
1123 END IF;
1124
1125 l_num_entity := l_num_entity + 1;
1126
1127 --Bugfix 6816437 Start --Code has been written to check the oe interface flag when inserting/updating Freight Cost Record
1128 IF p_in_rec.caller = 'PLSQL' THEN
1129 BEGIN
1130 SELECT 'Y',wts.stop_location_id into l_status,l_stop_loc_id
1131 FROM wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_new_deliveries wnd,
1132 wsh_delivery_assignments wda, wsh_delivery_details wdd
1133 WHERE wts.stop_id = l_stop_id
1134 AND (wdl.pick_up_stop_id = wts.stop_id OR wdl.drop_off_stop_id = wts.stop_id)
1135 AND wnd.delivery_id = wdl.delivery_id
1136 AND wda.delivery_id = wnd.delivery_id
1137 AND wdd.delivery_detail_id = wda.delivery_detail_id
1138 AND wdd.oe_interfaced_flag = 'Y'
1139 AND ROWNUM = 1;
1140 IF l_status = 'Y' THEN
1141 l_name := wsh_util_core.get_location_description(l_stop_loc_id,'NEW UI CODE INFO');
1142 WSH_UTIL_CORE.api_post_call(p_return_status => WSH_UTIL_CORE.G_RET_STS_WARNING,
1143 x_num_warnings => l_num_warnings,
1144 x_num_errors => l_num_errors,
1145 p_module_name => l_module_name,
1146 p_msg_data => 'WSH_FC_OTHER_WARN',
1147 p_token1 => 'ENTITY_NAME',
1148 p_value1 => 'stop',
1149 p_token2 => 'ENTITY_ID',
1150 p_value2 => l_name);
1151 END IF;
1152 EXCEPTION
1153 WHEN NO_DATA_FOUND THEN
1154 NULL;
1155 END;
1156 END IF;
1157 --Bugfix 6816437 End
1158
1159 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1160 SELECT COUNT(*) INTO l_counts
1161 FROM wsh_trip_stops
1162 WHERE stop_id = l_stop_id
1163 AND ROWNUM = 1;
1164
1165 IF l_debug_on THEN
1166 WSH_DEBUG_SV.log(l_module_name,'l_counts',l_counts);
1167 END IF;
1168 IF (l_counts = 0) THEN
1169 WSH_UTIL_CORE.api_post_call(
1170 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1171 x_num_warnings => l_num_warnings,
1172 x_num_errors => l_num_errors,
1173 p_module_name => l_module_name,
1174 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1175 END IF;
1176 END IF;
1177
1178 ELSIF ((l_freight_info_tab(l_index).delivery_id IS NOT NULL) OR l_freight_info_tab(l_index).delivery_name IS NOT NULL) THEN
1179 IF (l_freight_info_tab(l_index).delivery_id IS NULL OR l_freight_info_tab(l_index).delivery_name IS NOT NULL) THEN
1180 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1181 WSH_UTIL_VALIDATE.Validate_delivery_name(
1182 p_delivery_id =>l_delivery_id,
1183 p_delivery_name =>l_freight_info_tab(l_index).delivery_name,
1184 x_return_status =>l_return_status);
1185
1186 IF l_debug_on THEN
1187 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_delivery_name x_return_status',l_return_status);
1188 END IF;
1189 WSH_UTIL_CORE.api_post_call(
1190 p_return_status => l_return_status,
1191 x_num_warnings => l_num_warnings,
1192 x_num_errors => l_num_errors,
1193 p_module_name => l_module_name,
1194 p_msg_data => 'WSH_PUB_INVALID_PARAMETER',
1195 p_token1 => 'PARAMETER',
1196 p_value1 => 'delivery_name');
1197 END IF;
1198 ELSE
1199 l_delivery_id := l_freight_info_tab(l_index).delivery_id;
1200 END IF;
1201
1202 l_num_entity := l_num_entity + 1;
1203
1204 --Bugfix 6816437 Start --Code has been written to check the oe interface flag when inserting/updating Freight Cost Record
1205 IF p_in_rec.caller = 'PLSQL' THEN
1206 BEGIN
1207 SELECT 'Y',wnd.name into l_status,l_name
1208 FROM wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
1209 WHERE wnd.delivery_id = l_delivery_id
1210 AND wda.delivery_id = wnd.delivery_id
1211 AND wdd.delivery_detail_id = wda.delivery_detail_id
1212 AND wdd.oe_interfaced_flag = 'Y'
1213 AND ROWNUM = 1;
1214 IF l_status = 'Y' THEN
1215 WSH_UTIL_CORE.api_post_call(p_return_status => WSH_UTIL_CORE.G_RET_STS_WARNING,
1216 x_num_warnings => l_num_warnings,
1217 x_num_errors => l_num_errors,
1218 p_module_name => l_module_name,
1219 p_msg_data => 'WSH_FC_OTHER_WARN',
1220 p_token1 => 'ENTITY_NAME',
1221 p_value1 => 'delivery',
1222 p_token2 => 'ENTITY_ID',
1223 p_value2 => l_name);
1224 END IF;
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 NULL;
1228 END;
1229 END IF;
1230 --Bugfix 6816437 End
1231
1232 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1233 SELECT COUNT(*) INTO l_counts
1234 FROM wsh_new_deliveries
1235 WHERE delivery_id = l_delivery_id
1236 AND ROWNUM = 1;
1237
1238 IF (l_counts = 0) THEN
1239 WSH_UTIL_CORE.api_post_call(
1240 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1241 x_num_warnings => l_num_warnings,
1242 x_num_errors => l_num_errors,
1243 p_module_name => l_module_name,
1244 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1245 END IF;
1246 END IF;
1247
1248 ELSIF (l_freight_info_tab(l_index).delivery_leg_id IS NOT NULL) THEN
1249 l_num_entity := l_num_entity + 1;
1250
1251 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1252 SELECT delivery_id INTO l_counts
1253 FROM wsh_delivery_legs
1254 WHERE delivery_leg_id = l_freight_info_tab(l_index).delivery_leg_id
1255 AND ROWNUM = 1;
1256
1257 IF (l_counts = 0) THEN
1258 WSH_UTIL_CORE.api_post_call(
1259 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1260 x_num_warnings => l_num_warnings,
1261 x_num_errors => l_num_errors,
1262 p_module_name => l_module_name,
1263 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1264 END IF;
1265 END IF;
1266
1267 ELSIF (l_freight_info_tab(l_index).delivery_detail_id IS NOT NULL) THEN
1268 l_num_entity := l_num_entity + 1;
1269
1270 --Bugfix 6816437 Start --Code has been written to check the oe interface flag when inserting/updating Freight Cost Record
1271 IF p_in_rec.caller = 'PLSQL' THEN
1272 BEGIN
1273 SELECT container_flag,container_name into l_con_flag,l_name
1274 FROM wsh_delivery_details
1275 WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id;
1276
1277 IF l_con_flag = 'Y' THEN
1278 l_entity_name := 'LPN';
1279 SELECT 'Y' into l_status
1280 FROM wsh_delivery_details
1281 WHERE delivery_detail_id in (SELECT delivery_detail_id
1282 FROM wsh_delivery_assignments
1283 WHERE parent_delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id)
1284 AND oe_interfaced_flag = 'Y'
1285 AND container_flag = 'N'
1286 AND ROWNUM = 1;
1287 ELSE
1288 l_entity_name := 'Delivery line';
1289 SELECT oe_interfaced_flag,delivery_detail_id into l_status,l_name
1290 FROM wsh_delivery_details
1291 WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id;
1292 END IF;
1293
1294 IF l_status = 'Y' THEN
1295 WSH_UTIL_CORE.api_post_call(p_return_status => WSH_UTIL_CORE.G_RET_STS_WARNING,
1296 x_num_warnings => l_num_warnings,
1297 x_num_errors => l_num_errors,
1298 p_module_name => l_module_name,
1299 p_msg_data => 'WSH_FC_DET_WARN',
1300 p_token1 => 'ENTITY_NAME',
1301 p_value1 => l_entity_name,
1302 p_token2 => 'ENTITY_ID',
1303 p_value2 => l_name);
1304 END IF;
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 NULL;
1308 END;
1309 END IF;
1310 --Bugfix 6816437 End
1311
1312 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PARENT_ENTITY_LVL)=1) THEN
1313 SELECT COUNT(delivery_detail_id) INTO l_counts
1314 FROM wsh_delivery_details
1315 WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id
1316 AND ROWNUM = 1;
1317
1318 IF (l_counts = 0) THEN
1319 WSH_UTIL_CORE.api_post_call(
1320 p_return_status => WSH_UTIL_CORE.G_RET_STS_ERROR,
1321 x_num_warnings => l_num_warnings,
1322 x_num_errors => l_num_errors,
1323 p_module_name => l_module_name,
1324 p_msg_data => 'WSH_FC_ONE_MASTER_ENTITY');
1325 END IF;
1326 END IF;
1327 END IF;
1328
1329 l_freight_info_tab(l_index).trip_id := l_trip_id;
1330 l_freight_info_tab(l_index).stop_id := l_stop_id;
1331 l_freight_info_tab(l_index).delivery_id := l_delivery_id;
1332
1333 --Following changes for bug 3614196
1334 -- moved the code for is_action_enabled. Only after validations are done, entity ids would have a value.
1335 -- Add nvl with g_miss_num
1336
1337 --J-IB-JCKWOK
1338 --
1339 IF nvl(l_freight_info_tab(l_index).DELIVERY_DETAIL_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1340 --
1341 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
1342 --
1343 OPEN c_detail_rec(l_freight_info_tab(l_index).DELIVERY_DETAIL_ID);
1344 FETCH c_detail_rec INTO l_detail_rec_tab(1);
1345 --
1346 IF c_detail_rec%NOTFOUND THEN
1347 --
1348 CLOSE c_detail_rec;
1349 FND_MESSAGE.SET_NAME('WSH','WSH_DETAIL_NOT_EXIST');
1350 FND_MESSAGE.SET_TOKEN('DETAIL_ID', l_freight_info_tab(l_index).DELIVERY_DETAIL_ID);
1351 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1352 IF l_debug_on THEN
1353 wsh_debug_sv.log (l_module_name,'WSH_DETAIL_NOT_EXIST');
1354 END IF;
1355 RAISE FND_API.G_EXC_ERROR;
1356 --
1357 END IF;
1358 --
1359 CLOSE c_detail_rec;
1360 --
1361 WSH_DETAILS_VALIDATIONS.Is_Action_Enabled(
1362 p_del_detail_rec_tab => l_detail_rec_tab,
1363 p_action => 'ASSIGN-FREIGHT-COSTS',
1364 p_caller => p_in_rec.caller,
1365 p_deliveryid => NULL,
1366 x_return_status => l_return_status,
1367 x_valid_ids => l_valid_id_tab ,
1368 x_error_ids => l_error_ids ,
1369 x_valid_index_tab => l_valid_index_tab);
1370 --
1371 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1372 x_num_warnings =>l_num_warnings,
1373 x_num_errors =>l_num_errors);
1374 --
1375 END IF;
1376 --
1377 ELSIF nvl(l_freight_info_tab(l_index).DELIVERY_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1378 --
1379 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
1380 OPEN c_del_rec(l_freight_info_tab(l_index).DELIVERY_ID);
1381 FETCH c_del_rec INTO l_del_rec_tab(1);
1382 --
1383 IF c_del_rec%NOTFOUND THEN
1384 CLOSE c_del_rec;
1385 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_NOT_EXIST');
1386 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', l_freight_info_tab(l_index).DELIVERY_NAME);
1387 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1388 --
1389 IF l_debug_on THEN
1390 wsh_debug_sv.log (l_module_name,'WSH_DELIVERY_NOT_EXIST');
1391 END IF;
1392 --
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395 --
1396 CLOSE c_del_rec;
1397 --
1398 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
1399 p_dlvy_rec_tab => l_del_rec_tab,
1400 p_action => 'ASSIGN-FREIGHT-COSTS',
1401 p_caller => p_in_rec.caller,
1402 x_return_status => l_return_status,
1403 x_valid_ids => l_valid_id_tab ,
1404 x_error_ids => l_error_ids ,
1405 x_valid_index_tab => l_valid_index_tab);
1406
1407 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1408 x_num_warnings =>l_num_warnings,
1409 x_num_errors =>l_num_errors);
1410 END IF;
1411 --
1412 ELSIF nvl(l_freight_info_tab(l_index).TRIP_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1413 --
1414 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
1415 OPEN c_trip_rec(l_freight_info_tab(l_index).TRIP_ID);
1416 FETCH c_trip_rec INTO l_trip_rec_tab(1);
1417 --
1418 IF c_trip_rec%NOTFOUND THEN
1419 CLOSE c_trip_rec;
1420 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_EXIST');
1421 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_freight_info_tab(l_index).TRIP_NAME);
1422 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1423 --
1424 IF l_debug_on THEN
1425 wsh_debug_sv.log (l_module_name,'WSH_TRIP_NOT_EXIST');
1426 END IF;
1427 --
1428 RAISE FND_API.G_EXC_ERROR;
1429 END IF;
1430 --
1431 CLOSE c_trip_rec;
1432 --
1433 WSH_TRIP_VALIDATIONS.Is_Action_Enabled(
1434 p_trip_rec_tab => l_trip_rec_tab,
1435 p_action => 'ASSIGN-FREIGHT-COSTS',
1436 p_caller => p_in_rec.caller,
1437 x_return_status => l_return_status,
1438 x_valid_ids => l_valid_id_tab ,
1439 x_error_ids => l_error_ids ,
1440 x_valid_index_tab => l_valid_index_tab);
1441
1442 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1443 x_num_warnings =>l_num_warnings,
1444 x_num_errors =>l_num_errors);
1445 END IF;
1446 --
1447 ELSIF nvl(l_freight_info_tab(l_index).STOP_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1448 --
1449 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
1450 OPEN c_stop_rec(l_freight_info_tab(l_index).STOP_ID);
1451 FETCH c_stop_rec INTO l_stop_rec_tab(1);
1452 --
1453 IF c_stop_rec%NOTFOUND THEN
1454 CLOSE c_stop_rec;
1455 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
1456 FND_MESSAGE.SET_TOKEN('STOP_ID', l_freight_info_tab(l_index).STOP_ID);
1457 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1458 --
1459 IF l_debug_on THEN
1460 wsh_debug_sv.log (l_module_name,'WSH_STOP_NOT_EXIST');
1461 END IF;
1462 --
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465 --
1466 CLOSE c_stop_rec;
1467 --
1468 WSH_TRIP_STOPS_VALIDATIONS.Is_Action_Enabled(
1469 p_stop_rec_tab => l_stop_rec_tab,
1470 p_action => 'ASSIGN-FREIGHT-COSTS',
1471 p_caller => p_in_rec.caller,
1472 x_return_status => l_return_status,
1473 x_valid_ids => l_valid_id_tab ,
1474 x_error_ids => l_error_ids ,
1475 x_valid_index_tab => l_valid_index_tab);
1476
1477 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1478 x_num_warnings =>l_num_warnings,
1479 x_num_errors =>l_num_errors);
1480 END IF;
1481 --
1482 END IF;
1483
1484
1485 IF (p_in_rec.action_code= 'CREATE') THEN
1486 WSH_FREIGHT_COSTS_PVT.Create_Freight_Cost(
1487 p_freight_cost_info => l_freight_info_tab(l_index),
1488 x_rowid => x_out_tab(l_index).rowid,
1489 x_freight_cost_id => x_out_tab(l_index).freight_cost_id,
1490 x_return_status => l_return_status);
1491
1492 IF l_debug_on THEN
1493 WSH_DEBUG_SV.log(l_module_name,'WSH_FREIGHT_COSTS_PVT.Create_Freight_Cost x_freight_cost_id,x_return_status',
1494 x_out_tab(l_index).freight_cost_id||','||l_return_status);
1495 END IF;
1496 WSH_UTIL_CORE.api_post_call(
1497 p_return_status => l_return_status,
1498 x_num_warnings => l_num_warnings,
1499 x_num_errors => l_num_errors,
1500 p_module_name => l_module_name,
1501 p_msg_data => 'WSH_PUB_CREATE_FAILURE',
1502 p_token1 => 'ENTITY',
1503 p_value1 => 'Freight_Cost');
1504
1505 ELSIF (p_in_rec.action_code= 'UPDATE') THEN
1506 WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
1507 p_rowid => NULL,
1508 p_freight_cost_info => l_freight_info_tab(l_index),
1509 x_return_status => l_return_status);
1510
1511 IF l_debug_on THEN
1512 WSH_DEBUG_SV.log(l_module_name,'Update_Freight_Costs x_return_status',l_return_status);
1513 END IF;
1514 WSH_UTIL_CORE.api_post_call(
1515 p_return_status => l_return_status,
1516 x_num_warnings => l_num_warnings,
1517 x_num_errors => l_num_errors,
1518 p_module_name => l_module_name,
1519 p_msg_data => 'WSH_PUB_UPDATE_FAILURE',
1520 p_token1 => 'ENTITY',
1521 p_value1 => 'Freight_Cost');
1522 END IF;
1523
1524 EXCEPTION
1525 WHEN fnd_api.g_exc_error THEN
1526 -- OTM R12, glog proj
1527 IF get_freight_cost_type_del%ISOPEN THEN
1528 CLOSE get_freight_cost_type_del;
1529 END IF;
1530 IF c_detail_rec%ISOPEN THEN
1531 CLOSE c_detail_rec;
1532 END IF;
1533 IF c_trip_rec%ISOPEN THEN
1534 CLOSE c_trip_rec;
1535 END IF;
1536 IF c_stop_rec%ISOPEN THEN
1537 CLOSE c_stop_rec;
1538 END IF;
1539 IF c_del_rec%ISOPEN THEN
1540 CLOSE c_del_rec;
1541 END IF;
1542 ROLLBACK to create_update_freight_loop;
1543
1544 WHEN fnd_api.g_exc_unexpected_error THEN
1545 -- OTM R12, glog proj
1546 IF get_freight_cost_type_del%ISOPEN THEN
1547 CLOSE get_freight_cost_type_del;
1548 END IF;
1549 IF c_detail_rec%ISOPEN THEN
1550 CLOSE c_detail_rec;
1551 END IF;
1552 IF c_trip_rec%ISOPEN THEN
1553 CLOSE c_trip_rec;
1554 END IF;
1555 IF c_stop_rec%ISOPEN THEN
1556 CLOSE c_stop_rec;
1557 END IF;
1558 IF c_del_rec%ISOPEN THEN
1559 CLOSE c_del_rec;
1560 END IF;
1561 ROLLBACK to create_update_freight_loop;
1562
1563 WHEN others THEN
1564 -- OTM R12, glog proj
1565 IF get_freight_cost_type_del%ISOPEN THEN
1566 CLOSE get_freight_cost_type_del;
1567 END IF;
1568 IF c_detail_rec%ISOPEN THEN
1569 CLOSE c_detail_rec;
1570 END IF;
1571 IF c_trip_rec%ISOPEN THEN
1572 CLOSE c_trip_rec;
1573 END IF;
1574 IF c_stop_rec%ISOPEN THEN
1575 CLOSE c_stop_rec;
1576 END IF;
1577 IF c_del_rec%ISOPEN THEN
1578 CLOSE c_del_rec;
1579 END IF;
1580 ROLLBACK to create_update_freight_loop;
1581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1582 END;
1583
1584 l_index := p_freight_info_tab.NEXT(l_index);
1585 END LOOP;
1586
1587 IF (l_num_errors = p_freight_info_tab.count ) THEN
1588 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1589 RAISE FND_API.G_EXC_ERROR;
1590 ELSIF (l_num_errors > 0 ) THEN
1591 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1592 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1593 ELSIF (l_num_warnings > 0 ) THEN
1594 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1595 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1596 ELSE
1597 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1598 END IF;
1599
1600
1601 IF FND_API.TO_BOOLEAN(p_commit) THEN
1602 COMMIT WORK;
1603 END IF;
1604
1605 FND_MSG_PUB.Count_And_Get (
1606 p_count => x_msg_count,
1607 p_data => x_msg_data);
1608
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.pop(l_module_name);
1611 END IF;
1612
1613 EXCEPTION
1614 WHEN RECORD_LOCKED THEN
1615 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1616 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
1617 wsh_util_core.add_message(x_return_status,l_module_name);
1618 IF l_debug_on THEN
1619 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1620 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
1621 END IF;
1622 Rollback to Create_Update_Freight_Costs_Gp;
1623
1624 WHEN FND_API.G_EXC_ERROR THEN
1625 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1626 FND_MSG_PUB.Count_And_Get (
1627 p_count => x_msg_count,
1628 p_data => x_msg_data,
1629 p_encoded => FND_API.G_FALSE);
1630 IF l_debug_on THEN
1631 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1632 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1633 END IF;
1634 Rollback to Create_Update_Freight_Costs_Gp;
1635
1636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1637 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1638 FND_MSG_PUB.Count_And_Get (
1639 p_count => x_msg_count,
1640 p_data => x_msg_data,
1641 p_encoded => FND_API.G_FALSE);
1642 IF l_debug_on THEN
1643 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1644 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1645 END IF;
1646 Rollback to Create_Update_Freight_Costs_Gp;
1647
1648 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1649 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1650 FND_MSG_PUB.Count_And_Get (
1651 p_count => x_msg_count,
1652 p_data => x_msg_data,
1653 p_encoded => FND_API.G_FALSE);
1654 IF l_debug_on THEN
1655 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1656 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1657 END IF;
1658
1659 WHEN OTHERS THEN
1660 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1661 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
1662 FND_MSG_PUB.Count_And_Get (
1663 p_count => x_msg_count,
1664 p_data => x_msg_data,
1665 p_encoded => FND_API.G_FALSE);
1666 IF l_debug_on THEN
1667 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1668 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1669 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1670 END IF;
1671 Rollback to Create_Update_Freight_Costs_Gp;
1672 END Create_Update_Freight_Costs;
1673
1674
1675 --========================================================================
1676 -- PROCEDURE : Create_Update_Freight_Costs Wrapper API
1677 --
1678 -- PARAMETERS: p_api_version_number known api versionerror buffer
1679 -- p_init_msg_list initialize message stack
1680 -- x_return_status return status
1681 -- x_msg_count number of messages in the list
1682 -- x_msg_data text of messages
1683 -- p_changed_attributes changed attributes for delivery details
1684 -- p_action_code action to perform
1685 --
1686 --
1687 -- COMMENT : Validates Organization_id and Organization_code against view
1688 -- org_organization_definitions. If both values are
1689 -- specified then only Org_Id is used
1690 --========================================================================
1691 PROCEDURE Create_Update_Freight_Costs (
1692 p_api_version_number IN NUMBER
1693 , p_init_msg_list IN VARCHAR2
1694 , p_commit IN VARCHAR2
1695 , x_return_status OUT NOCOPY VARCHAR2
1696 , x_msg_count OUT NOCOPY NUMBER
1697 , x_msg_data OUT NOCOPY VARCHAR2
1698 , p_pub_freight_costs IN WSH_FREIGHT_COSTS_GRP.PubFreightCostRecType
1699 , p_action_code IN VARCHAR2
1700 , x_freight_cost_id OUT NOCOPY NUMBER
1701 )
1702 IS
1703
1704 l_api_version_number CONSTANT NUMBER := 1.0;
1705 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Freight_Costs';
1706 l_debug_on BOOLEAN;
1707 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_FREIGHT_COSTS';
1708
1709 l_pvt_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
1710 l_in_rec FreightInRecType;
1711 l_freight_info_tab freight_rec_tab_type;
1712 l_out_tab freight_out_tab_type;
1713 BEGIN
1714
1715 --
1716 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1717 --
1718 IF l_debug_on IS NULL
1719 THEN
1720 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1721 END IF;
1722 --
1723 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
1724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1725 END IF;
1726
1727 IF FND_API.to_Boolean(p_init_msg_list) THEN
1728 FND_MSG_PUB.initialize;
1729 END IF;
1730
1731 map_freightgrp_to_pvt (
1732 p_grp_freight_rec => p_pub_freight_costs,
1733 x_pvt_freight_rec => l_pvt_freight_rec,
1734 x_return_status => x_return_status);
1735 IF l_debug_on THEN
1736 wsh_debug_sv.log (l_module_name, 'map_freightgrp_to_pvt x_return_status',x_return_status);
1737 END IF;
1738 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1739 raise FND_API.G_EXC_ERROR;
1740 END IF;
1741
1742 l_in_rec.caller := 'WSH_GRP';
1743 l_in_rec.phase := 1;
1744 l_in_rec.action_code := p_action_code;
1745 l_freight_info_tab(1) := l_pvt_freight_rec;
1746
1747 WSH_INTERFACE_GRP.Create_Update_Freight_Costs(
1748 p_api_version_number => p_api_version_number,
1749 p_init_msg_list => p_init_msg_list,
1750 p_commit => p_commit,
1751 x_return_status => x_return_status,
1752 x_msg_count => x_msg_count,
1753 x_msg_data => x_msg_data,
1754 p_freight_info_tab => l_freight_info_tab,
1755 p_in_rec => l_in_rec,
1756 x_out_tab => l_out_tab );
1757
1758 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_out_tab.COUNT > 0 ) THEN
1759 x_freight_cost_id := l_out_tab(l_out_tab.FIRST).freight_cost_id;
1760 END IF;
1761
1762 IF l_debug_on THEN
1763 WSH_DEBUG_SV.pop(l_module_name);
1764 END IF;
1765
1766 EXCEPTION
1767 WHEN FND_API.G_EXC_ERROR THEN
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1770 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1771 END IF;
1772
1773
1774 WHEN OTHERS THEN
1775 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1776 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
1777 IF l_debug_on THEN
1778 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1779 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1780 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1781 END IF;
1782 END Create_Update_Freight_Costs;
1783 --Harmonizing Project I :heali
1784
1785 END WSH_FREIGHT_COSTS_GRP;