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