[Home] [Help]
PACKAGE BODY: APPS.WSH_FREIGHT_COSTS_PVT
Source
1 PACKAGE BODY WSH_FREIGHT_COSTS_PVT AS
2 /* $Header: WSHFCTHB.pls 120.2 2007/12/17 06:52:21 brana noship $ */
3 -- Package internal global variables
4 g_Return_Status VARCHAR2(1);
5
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_FREIGHT_COSTS_PVT';
8 --
9
10 PROCEDURE Create_Freight_Cost(
11 p_freight_cost_info IN Freight_Cost_Rec_Type
12 , x_rowid OUT NOCOPY VARCHAR2
13 , x_freight_cost_id OUT NOCOPY NUMBER
14 , x_return_status OUT NOCOPY VARCHAR2
15 )
16 IS
17 CURSOR C_Next_Freight_Cost_Id
18 IS
19 SELECT wsh_freight_costs_s.nextval
20 FROM sys.dual;
21
22
23 CURSOR c_new_row_id
24 IS
25 SELECT rowid
26 FROM wsh_freight_costs
27 WHERE freight_cost_id = x_freight_cost_id;
28
29
30 create_failure EXCEPTION;
31 --
32 l_debug_on BOOLEAN;
33 --
34 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_FREIGHT_COST';
35 --
36 BEGIN
37 --
38 -- Debug Statements
39 --
40 --
41 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
42 --
43 IF l_debug_on IS NULL
44 THEN
45 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
46 END IF;
47 --
48 IF l_debug_on THEN
49 WSH_DEBUG_SV.push(l_module_name);
50 END IF;
51 --
52 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
53
54 x_freight_cost_id := p_freight_cost_info.freight_cost_id;
55 IF (x_freight_cost_id IS NULL) THEN
56 LOOP
57 OPEN C_Next_Freight_Cost_Id;
58 FETCH C_Next_Freight_Cost_Id INTO x_freight_cost_id;
59 CLOSE C_Next_Freight_Cost_Id;
60
61 IF (x_freight_cost_id IS NOT NULL) THEN
62 x_rowid := NULL;
63 OPEN c_new_row_id;
64 FETCH c_new_row_id INTO x_rowid;
65 CLOSE c_new_row_id;
66
67 IF (x_rowid IS NULL) THEN
68 EXIT;
69 END IF;
70 ELSE
71 EXIT;
72 END IF;
73 END LOOP;
74 END IF;
75
76 INSERT INTO wsh_freight_costs(
77 freight_cost_id,
78 freight_cost_type_id,
79 unit_amount,
80 /* H Integration: datamodel changes wrudge uncommented 4 columns*/
81 calculation_method,
82 uom,
83 quantity,
84 total_amount,
85 currency_code,
86 conversion_date,
87 conversion_rate,
88 conversion_type_code,
89 trip_id,
90 stop_id,
91 delivery_id,
92 delivery_leg_id,
93 delivery_detail_id,
94 attribute_category,
95 attribute1,
96 attribute2,
97 attribute3,
98 attribute4,
99 attribute5,
100 attribute6,
101 attribute7,
102 attribute8,
103 attribute9,
104 attribute10,
105 attribute11,
106 attribute12,
107 attribute13,
108 attribute14,
109 attribute15,
110 creation_date,
111 created_by,
112 last_update_date,
113 last_updated_by,
114 last_update_login,
115 program_application_id,
116 program_id,
117 program_update_date,
118 request_id,
119 /* H Integration: datamodel changes wrudge */
120 pricing_list_header_id,
121 pricing_list_line_id,
122 applied_to_charge_id,
123 charge_unit_value,
124 charge_source_code,
125 line_type_code,
126 estimated_flag,
127 commodity_category_id,
128 /* R12 new attributes */
129 billable_quantity,
130 billable_uom,
131 billable_basis
132 ) VALUES (
133 x_freight_cost_id,
134 p_freight_cost_info.freight_cost_type_id,
135 p_freight_cost_info.unit_amount,
136 /* H Integration: datamodel changes wrudge uncommented 4 columns*/
137 p_freight_cost_info.calculation_method,
138 p_freight_cost_info.uom,
139 p_freight_cost_info.quantity,
140 p_freight_cost_info.total_amount,
141 p_freight_cost_info.currency_code,
142 p_freight_cost_info.conversion_date,
143 p_freight_cost_info.conversion_rate,
144 p_freight_cost_info.conversion_type_code,
145 p_freight_cost_info.trip_id,
146 p_freight_cost_info.stop_id,
147 p_freight_cost_info.delivery_id,
148 p_freight_cost_info.delivery_leg_id,
149 p_freight_cost_info.delivery_detail_id,
150 p_freight_cost_info.attribute_category,
151 p_freight_cost_info.attribute1,
152 p_freight_cost_info.attribute2,
153 p_freight_cost_info.attribute3,
154 p_freight_cost_info.attribute4,
155 p_freight_cost_info.attribute5,
156 p_freight_cost_info.attribute6,
157 p_freight_cost_info.attribute7,
158 p_freight_cost_info.attribute8,
159 p_freight_cost_info.attribute9,
160 p_freight_cost_info.attribute10,
161 p_freight_cost_info.attribute11,
162 p_freight_cost_info.attribute12,
163 p_freight_cost_info.attribute13,
164 p_freight_cost_info.attribute14,
165 p_freight_cost_info.attribute15,
166 p_freight_cost_info.creation_date,
167 p_freight_cost_info.created_by,
168 p_freight_cost_info.last_update_date,
169 p_freight_cost_info.last_updated_by,
170 p_freight_cost_info.last_update_login,
171 p_freight_cost_info.program_application_id,
172 p_freight_cost_info.program_id,
173 p_freight_cost_info.program_update_date,
174 p_freight_cost_info.request_id,
175 /* H Integration: datamodel changes wrudge */
176 p_freight_cost_info.pricing_list_header_id,
177 p_freight_cost_info.pricing_list_line_id,
178 p_freight_cost_info.applied_to_charge_id,
179 p_freight_cost_info.charge_unit_value,
180 p_freight_cost_info.charge_source_code,
181 p_freight_cost_info.line_type_code,
182 p_freight_cost_info.estimated_flag,
183 p_freight_cost_info.commodity_category_id,
184 /* R12 new attributes */
185 p_freight_cost_info.billable_quantity,
186 p_freight_cost_info.billable_uom,
187 p_freight_cost_info.billable_basis
188 ) RETURNING rowid INTO x_rowid;
189
190 --
191 -- Debug Statements
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.pop(l_module_name);
195 END IF;
196 --
197 EXCEPTION
198 WHEN create_failure THEN
199 wsh_util_core.default_handler('WSH_FREIGHT_COSTS_PVT.CREATE_FREIGHT_COST');
200 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
201
202 --
203 -- Debug Statements
204 --
205 IF l_debug_on THEN
206 WSH_DEBUG_SV.logmsg(l_module_name,'CREATE_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
207 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CREATE_FAILURE');
208 END IF;
209 --
210 END Create_Freight_Cost;
211
212 PROCEDURE Update_Freight_Cost(
213 p_rowid IN VARCHAR2
214 , p_freight_cost_info IN Freight_Cost_Rec_Type
215 , x_return_status OUT NOCOPY VARCHAR2
216 )
217 IS
218
219 --
220 l_debug_on BOOLEAN;
221 --
222 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_FREIGHT_COST';
223 --
224 BEGIN
225
226 --
227 -- Debug Statements
228 --
229 --
230 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
231 --
232 IF l_debug_on IS NULL
233 THEN
234 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
235 END IF;
236 --
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.push(l_module_name);
239 --
240 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
241 WSH_DEBUG_SV.log(l_module_name,'freight_cost_id ',p_freight_cost_info.freight_cost_id);
242 WSH_DEBUG_SV.log(l_module_name,'freight_cost_type_id ',p_freight_cost_info.freight_cost_type_id);
243 WSH_DEBUG_SV.log(l_module_name,'unit_amount ',p_freight_cost_info.unit_amount);
244 WSH_DEBUG_SV.log(l_module_name,'calculation_method ',p_freight_cost_info.calculation_method);
245 WSH_DEBUG_SV.log(l_module_name,'uom ',p_freight_cost_info.uom);
246 WSH_DEBUG_SV.log(l_module_name,'quantity ',p_freight_cost_info.quantity);
247 WSH_DEBUG_SV.log(l_module_name,'total_amount ',p_freight_cost_info.total_amount);
248 WSH_DEBUG_SV.log(l_module_name,'currency_code ',p_freight_cost_info.currency_code);
249 WSH_DEBUG_SV.log(l_module_name,'conversion_date ',p_freight_cost_info.conversion_date);
250 WSH_DEBUG_SV.log(l_module_name,'conversion_rate ',p_freight_cost_info.conversion_rate);
251 WSH_DEBUG_SV.log(l_module_name,'conversion_type_code ',p_freight_cost_info.conversion_type_code);
252 WSH_DEBUG_SV.log(l_module_name,'trip_id ',p_freight_cost_info.trip_id);
253 WSH_DEBUG_SV.log(l_module_name,'stop_id ',p_freight_cost_info.stop_id);
254 WSH_DEBUG_SV.log(l_module_name,'delivery_id ',p_freight_cost_info.delivery_id);
255 WSH_DEBUG_SV.log(l_module_name,'delivery_leg_id ',p_freight_cost_info.delivery_leg_id);
256 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id ',p_freight_cost_info.delivery_detail_id);
257 WSH_DEBUG_SV.log(l_module_name,'attribute_category ',p_freight_cost_info.attribute_category);
258 WSH_DEBUG_SV.log(l_module_name,'attribute1 ',p_freight_cost_info.attribute1);
259 WSH_DEBUG_SV.log(l_module_name,'attribute2 ',p_freight_cost_info.attribute2);
260 WSH_DEBUG_SV.log(l_module_name,'attribute3 ',p_freight_cost_info.attribute3);
261 WSH_DEBUG_SV.log(l_module_name,'attribute4 ',p_freight_cost_info.attribute4);
262 WSH_DEBUG_SV.log(l_module_name,'attribute5 ',p_freight_cost_info.attribute5);
263 WSH_DEBUG_SV.log(l_module_name,'attribute6 ',p_freight_cost_info.attribute6);
264 WSH_DEBUG_SV.log(l_module_name,'attribute7 ',p_freight_cost_info.attribute7);
265 WSH_DEBUG_SV.log(l_module_name,'attribute8 ',p_freight_cost_info.attribute8);
266 WSH_DEBUG_SV.log(l_module_name,'attribute9 ',p_freight_cost_info.attribute9);
267 WSH_DEBUG_SV.log(l_module_name,'attribute10 ',p_freight_cost_info.attribute10);
268 WSH_DEBUG_SV.log(l_module_name,'attribute11 ',p_freight_cost_info.attribute11);
269 WSH_DEBUG_SV.log(l_module_name,'attribute12 ',p_freight_cost_info.attribute12);
270 WSH_DEBUG_SV.log(l_module_name,'attribute13 ',p_freight_cost_info.attribute13);
271 WSH_DEBUG_SV.log(l_module_name,'attribute14 ',p_freight_cost_info.attribute14);
272 WSH_DEBUG_SV.log(l_module_name,'attribute15 ',p_freight_cost_info.attribute15);
273 WSH_DEBUG_SV.log(l_module_name,'creation_date ',p_freight_cost_info.creation_date);
274 WSH_DEBUG_SV.log(l_module_name,'created_by ',p_freight_cost_info.created_by);
275 WSH_DEBUG_SV.log(l_module_name,'last_update_date ',p_freight_cost_info.last_update_date);
276 WSH_DEBUG_SV.log(l_module_name,'last_updated_by ',p_freight_cost_info.last_updated_by);
277 WSH_DEBUG_SV.log(l_module_name,'last_update_login ',p_freight_cost_info.last_update_login);
278 WSH_DEBUG_SV.log(l_module_name,'program_application_id ',p_freight_cost_info.program_application_id);
279 WSH_DEBUG_SV.log(l_module_name,'program_id ',p_freight_cost_info.program_id);
280 WSH_DEBUG_SV.log(l_module_name,'program_update_date ',p_freight_cost_info.program_update_date);
281 WSH_DEBUG_SV.log(l_module_name,'request_id ',p_freight_cost_info.request_id);
282 WSH_DEBUG_SV.log(l_module_name,'pricing_list_header_id ',p_freight_cost_info.pricing_list_header_id);
283 WSH_DEBUG_SV.log(l_module_name,'pricing_list_line_id ',p_freight_cost_info.pricing_list_line_id);
284 WSH_DEBUG_SV.log(l_module_name,'applied_to_charge_id ',p_freight_cost_info.applied_to_charge_id);
285 WSH_DEBUG_SV.log(l_module_name,'charge_unit_value ',p_freight_cost_info.charge_unit_value);
286 WSH_DEBUG_SV.log(l_module_name,'charge_source_code ',p_freight_cost_info.charge_source_code);
287 WSH_DEBUG_SV.log(l_module_name,'line_type_code ',p_freight_cost_info.line_type_code);
288 WSH_DEBUG_SV.log(l_module_name,'estimated_flag ',p_freight_cost_info.estimated_flag);
289 WSH_DEBUG_SV.log(l_module_name,'estimated_flag ',p_freight_cost_info.estimated_flag);
290 WSH_DEBUG_SV.log(l_module_name,'commodity_category_id ',p_freight_cost_info.commodity_category_id);
291 WSH_DEBUG_SV.log(l_module_name,'billable_quantity',p_freight_cost_info.billable_quantity);
292 WSH_DEBUG_SV.log(l_module_name,'billable_uom',p_freight_cost_info.billable_uom);
293 WSH_DEBUG_SV.log(l_module_name,'billable_basis',p_freight_cost_info.billable_basis);
294 END IF;
295 --
296 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
297
298 UPDATE wsh_freight_costs
299 SET
300 freight_cost_id = p_freight_cost_info.freight_cost_id,
301 freight_cost_type_id = p_freight_cost_info.freight_cost_type_id,
302 unit_amount = p_freight_cost_info.unit_amount,
303 /* H Integration: datamodel changes wrudge uncommented 4 columns*/
304 calculation_method = p_freight_cost_info.calculation_method,
305 uom = p_freight_cost_info.uom,
306 quantity = p_freight_cost_info.quantity,
307 total_amount = p_freight_cost_info.total_amount,
308 currency_code = p_freight_cost_info.currency_code,
309 conversion_date = p_freight_cost_info.conversion_date,
310 conversion_rate = p_freight_cost_info.conversion_rate,
311 conversion_type_code = p_freight_cost_info.conversion_type_code,
312 trip_id = p_freight_cost_info.trip_id,
313 stop_id = p_freight_cost_info.stop_id,
314 delivery_id = p_freight_cost_info.delivery_id,
315 delivery_leg_id = p_freight_cost_info.delivery_leg_id,
316 delivery_detail_id = p_freight_cost_info.delivery_detail_id,
317 attribute_category = p_freight_cost_info.attribute_category,
318 attribute1 = p_freight_cost_info.attribute1,
319 attribute2 = p_freight_cost_info.attribute2,
320 attribute3 = p_freight_cost_info.attribute3,
321 attribute4 = p_freight_cost_info.attribute4,
322 attribute5 = p_freight_cost_info.attribute5,
323 attribute6 = p_freight_cost_info.attribute6,
324 attribute7 = p_freight_cost_info.attribute7,
325 attribute8 = p_freight_cost_info.attribute8,
326 attribute9 = p_freight_cost_info.attribute9,
327 attribute10 = p_freight_cost_info.attribute10,
328 attribute11 = p_freight_cost_info.attribute11,
329 attribute12 = p_freight_cost_info.attribute12,
330 attribute13 = p_freight_cost_info.attribute13,
331 attribute14 = p_freight_cost_info.attribute14,
332 attribute15 = p_freight_cost_info.attribute15,
333 last_update_date = p_freight_cost_info.last_update_date,
334 last_updated_by = p_freight_cost_info.last_updated_by,
335 last_update_login = p_freight_cost_info.last_update_login,
336 program_application_id = p_freight_cost_info.program_application_id,
337 program_id = p_freight_cost_info.program_id,
341 pricing_list_header_id = p_freight_cost_info.pricing_list_header_id,
338 program_update_date = p_freight_cost_info.program_update_date,
339 request_id = p_freight_cost_info.request_id,
340 /* H Integration: datamodel changes wrudge */
342 pricing_list_line_id = p_freight_cost_info.pricing_list_line_id,
343 applied_to_charge_id = p_freight_cost_info.applied_to_charge_id,
344 charge_unit_value = p_freight_cost_info.charge_unit_value,
345 charge_source_code = p_freight_cost_info.charge_source_code,
346 line_type_code = p_freight_cost_info.line_type_code,
347 estimated_flag = p_freight_cost_info.estimated_flag,
348 commodity_category_id = p_freight_cost_info.commodity_category_id,
349 /* R12 new attributes */
350 billable_quantity = p_freight_cost_info.billable_quantity,
351 billable_uom = p_freight_cost_info.billable_uom,
352 billable_basis = p_freight_cost_info.billable_basis
353 WHERE freight_cost_id = p_freight_cost_info.freight_cost_id;
354
355 IF (SQL%NOTFOUND) THEN
356 RAISE NO_DATA_FOUND;
357 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
358 END IF;
359
360 --
361 -- Debug Statements
362 --
363 IF l_debug_on THEN
364 WSH_DEBUG_SV.pop(l_module_name);
365 END IF;
366 --
367 EXCEPTION
368 WHEN OTHERS THEN
369 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
370 wsh_util_core.default_handler ('WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost');
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
373 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
374 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
375 END IF;
376 END Update_Freight_Cost;
377
378 PROCEDURE Lock_Freight_Cost(
379 p_rowid IN VARCHAR2
380 , p_freight_cost_info IN Freight_Cost_Rec_Type
381 )
382 IS
383
384 CURSOR lock_row IS
385 SELECT
386 freight_cost_id,
387 freight_cost_type_id,
388 unit_amount,
389 /* H Integration: datamodel changes wrudge uncommented 4 columns*/
390 calculation_method,
391 uom,
392 quantity,
393 total_amount,
394 currency_code,
395 conversion_date,
396 conversion_rate,
397 conversion_type_code,
398 trip_id,
399 stop_id,
400 delivery_id,
401 delivery_leg_id,
402 delivery_detail_id,
403 attribute_category,
404 attribute1,
405 attribute2,
406 attribute3,
407 attribute4,
408 attribute5,
409 attribute6,
410 attribute7,
411 attribute8,
412 attribute9,
413 attribute10,
414 attribute11,
415 attribute12,
416 attribute13,
417 attribute14,
418 attribute15,
419 creation_date,
420 created_by,
421 last_update_date,
422 last_updated_by,
423 last_update_login,
424 program_application_id,
425 program_id,
426 program_update_date,
427 request_id,
428 /* H Integration: datamodel changes wrudge */
429 pricing_list_header_id,
430 pricing_list_line_id,
431 applied_to_charge_id,
432 charge_unit_value,
433 charge_source_code,
434 line_type_code,
435 estimated_flag,
436 commodity_category_id,
437 /* R12 new attributes */
438 billable_quantity,
439 billable_uom,
440 billable_basis
441 FROM wsh_freight_costs
442 WHERE rowid = p_rowid
443 FOR UPDATE OF freight_cost_id NOWAIT;
444
445 Recinfo lock_row%ROWTYPE;
446
447 --
448 l_debug_on BOOLEAN;
449 --
450 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_FREIGHT_COST';
451 --
452 BEGIN
453
454 --
455 -- Debug Statements
456 --
457 --
458 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
459 --
460 IF l_debug_on IS NULL
461 THEN
462 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
463 END IF;
464 --
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.push(l_module_name);
467 --
468 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
469 END IF;
470 --
471 OPEN lock_row;
472 FETCH lock_row INTO Recinfo;
473
474 IF (lock_row%NOTFOUND) THEN
475 CLOSE lock_row;
476 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
477 IF l_debug_on THEN
478 WSH_DEBUG_SV.logmsg(l_module_name,'FORM_RECORD_DELETED Error has occured',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
479 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FORM_RECORD_DELETED');
480 END IF;
481 app_exception.raise_exception;
482 END IF;
483
484 CLOSE lock_row;
485 IF ( (Recinfo.freight_cost_id = p_freight_cost_info.freight_cost_id)
486 AND (Recinfo.freight_cost_type_id = p_freight_cost_info.freight_cost_type_id)
490 and p_freight_cost_info.unit_amount is null))
487 /* H Integration: datamodel changes wrudge column is nullable */
488 AND ( (Recinfo.unit_amount = p_freight_cost_info.unit_amount)
489 or (Recinfo.unit_amount is null
491 /* H Integration: datamodel changes wrudge enabled 4 nullable columns */
492 AND ( (Recinfo.calculation_method = p_freight_cost_info.calculation_method)
493 or (Recinfo.calculation_method is null
494 and p_freight_cost_info.calculation_method is null))
495 AND ( (Recinfo.uom = p_freight_cost_info.uom)
496 or (Recinfo.uom is null
497 and p_freight_cost_info.uom is null))
498 AND ( (Recinfo.quantity = p_freight_cost_info.quantity)
499 or (Recinfo.quantity is null
500 and p_freight_cost_info.quantity is null))
501 AND ( (Recinfo.total_amount = p_freight_cost_info.total_amount)
502 or (Recinfo.total_amount is null
503 and p_freight_cost_info.total_amount is null))
504 /* H Integration: datamodel changes wrudge collumn is nullable*/
505 AND ( (Recinfo.currency_code = p_freight_cost_info.currency_code)
506 or (Recinfo.currency_code is null
507 and p_freight_cost_info.currency_code is null))
508 AND ( (Recinfo.conversion_date = p_freight_cost_info.conversion_date)
509 OR ( (p_freight_cost_info.conversion_date IS NULL)
510 AND (Recinfo.conversion_date IS NULL)))
511 AND ( (Recinfo.conversion_rate = p_freight_cost_info.conversion_rate)
512 OR ( (p_freight_cost_info.conversion_rate IS NULL)
513 AND (Recinfo.conversion_rate IS NULL)))
514 AND ( (Recinfo.conversion_type_code = p_freight_cost_info.conversion_type_code)
515 OR ( (p_freight_cost_info.conversion_type_code IS NULL)
516 AND (Recinfo.conversion_type_code IS NULL)))
517 AND ( (Recinfo.trip_id = p_freight_cost_info.trip_id)
518 OR ( (p_freight_cost_info.trip_id IS NULL)
519 AND (Recinfo.trip_id IS NULL)))
520
521 AND ( (Recinfo.stop_id = p_freight_cost_info.stop_id)
522 OR ( (p_freight_cost_info.stop_id IS NULL)
523 AND (Recinfo.stop_id IS NULL)))
524 AND ( (Recinfo.delivery_id = p_freight_cost_info.delivery_id)
525 OR ( (p_freight_cost_info.delivery_id IS NULL)
526 AND (Recinfo.delivery_id IS NULL)))
527 AND ( (Recinfo.delivery_leg_id = p_freight_cost_info.delivery_leg_id)
528 OR ( (p_freight_cost_info.delivery_leg_id IS NULL)
529 AND (Recinfo.delivery_leg_id IS NULL)))
530 AND ( (Recinfo.delivery_detail_id = p_freight_cost_info.delivery_detail_id)
531 OR ( (p_freight_cost_info.delivery_detail_id IS NULL)
532 AND (Recinfo.delivery_detail_id IS NULL)))
533 AND ( (Recinfo.attribute1 = p_freight_cost_info.attribute1)
534 OR ( (p_freight_cost_info.attribute1 IS NULL)
535 AND (Recinfo.attribute1 IS NULL)))
536 AND ( (Recinfo.attribute2 = p_freight_cost_info.attribute2)
537 OR ( (p_freight_cost_info.attribute2 IS NULL)
538 AND (Recinfo.attribute2 IS NULL)))
539 AND ( (Recinfo.attribute3 = p_freight_cost_info.attribute3)
540 OR ( (p_freight_cost_info.attribute3 IS NULL)
541 AND (Recinfo.attribute3 IS NULL)))
542 AND ( (Recinfo.attribute4 = p_freight_cost_info.attribute4)
543 OR ( (p_freight_cost_info.attribute4 IS NULL)
544 AND (Recinfo.attribute4 IS NULL)))
545 AND ( (Recinfo.attribute5 = p_freight_cost_info.attribute5)
546 OR ( (p_freight_cost_info.attribute5 IS NULL)
547 AND (Recinfo.attribute5 IS NULL)))
548 AND ( (Recinfo.attribute6 = p_freight_cost_info.attribute6)
549 OR ( (p_freight_cost_info.attribute6 IS NULL)
550 AND (Recinfo.attribute6 IS NULL)))
551 AND ( (Recinfo.attribute7 = p_freight_cost_info.attribute7)
552 OR ( (p_freight_cost_info.attribute7 IS NULL)
553 AND (Recinfo.attribute7 IS NULL)))
554 AND ( (Recinfo.attribute8 = p_freight_cost_info.attribute8)
555 OR ( (p_freight_cost_info.attribute8 IS NULL)
556 AND (Recinfo.attribute8 IS NULL)))
557 AND ( (Recinfo.attribute9 = p_freight_cost_info.attribute9)
558 OR ( (p_freight_cost_info.attribute9 IS NULL)
559 AND (Recinfo.attribute9 IS NULL)))
560 AND ( (Recinfo.attribute10 = p_freight_cost_info.attribute10)
561 OR ( (p_freight_cost_info.attribute10 IS NULL)
562 AND (Recinfo.attribute10 IS NULL)))
563 AND ( (Recinfo.attribute11 = p_freight_cost_info.attribute11)
564 OR ( (p_freight_cost_info.attribute11 IS NULL)
565 AND (Recinfo.attribute11 IS NULL)))
566 AND ( (Recinfo.attribute12 = p_freight_cost_info.attribute12)
567 OR ( (p_freight_cost_info.attribute12 IS NULL)
568 AND (Recinfo.attribute12 IS NULL)))
569 AND ( (Recinfo.attribute13 = p_freight_cost_info.attribute13)
570 OR ( (p_freight_cost_info.attribute13 IS NULL)
571 AND (Recinfo.attribute13 IS NULL)))
572 AND ( (Recinfo.attribute14 = p_freight_cost_info.attribute14)
573 OR ( (p_freight_cost_info.attribute14 IS NULL)
574 AND (Recinfo.attribute14 IS NULL)))
575 AND ( (Recinfo.attribute15 = p_freight_cost_info.attribute15)
576 OR ( (p_freight_cost_info.attribute15 IS NULL)
580 AND (Recinfo.last_update_date = p_freight_cost_info.last_update_date)
577 AND (Recinfo.attribute15 IS NULL)))
578 AND (Recinfo.creation_date = p_freight_cost_info.creation_date)
579 AND (Recinfo.created_by = p_freight_cost_info.created_by)
581 AND (Recinfo.last_update_login = p_freight_cost_info.last_update_login)
582 /* H Integration: datamodel changes wrudge */
583 AND ( (Recinfo.pricing_list_header_id = p_freight_cost_info.pricing_list_header_id)
584 or (Recinfo.pricing_list_header_id is NULL
585 and p_freight_cost_info.pricing_list_header_id is NULL))
586 AND ( (Recinfo.pricing_list_line_id = p_freight_cost_info.pricing_list_line_id)
587 or (Recinfo.pricing_list_line_id is NULL
588 and p_freight_cost_info.pricing_list_line_id is NULL))
589 AND ( (Recinfo.applied_to_charge_id = p_freight_cost_info.applied_to_charge_id)
590 or (Recinfo.applied_to_charge_id is NULL
591 and p_freight_cost_info.applied_to_charge_id is NULL))
592 AND ( (Recinfo.charge_unit_value = p_freight_cost_info.charge_unit_value)
593 or (Recinfo.charge_unit_value is NULL
594 and p_freight_cost_info.charge_unit_value is NULL))
595 AND ( (Recinfo.charge_source_code = p_freight_cost_info.charge_source_code)
596 or (Recinfo.charge_source_code is NULL
597 and p_freight_cost_info.charge_source_code is NULL))
598 AND ( (Recinfo.line_type_code = p_freight_cost_info.line_type_code)
599 or (Recinfo.line_type_code is NULL
600 and p_freight_cost_info.line_type_code is NULL))
601 AND ( (Recinfo.estimated_flag = p_freight_cost_info.estimated_flag)
602 or (Recinfo.estimated_flag is NULL
603 and p_freight_cost_info.estimated_flag is NULL))
604 AND ( (Recinfo.commodity_category_id = p_freight_cost_info.commodity_category_id)
605 or (Recinfo.commodity_category_id is NULL
606 and p_freight_cost_info.commodity_category_id is NULL))
607 /* R12 new attributes */
608 AND ( (Recinfo.billable_quantity = p_freight_cost_info.billable_quantity)
609 or (Recinfo.billable_quantity is NULL
610 and p_freight_cost_info.billable_quantity is NULL))
611 AND ( (Recinfo.billable_uom = p_freight_cost_info.billable_uom)
612 or (Recinfo.billable_uom is NULL
613 and p_freight_cost_info.billable_uom is NULL))
614 AND ( (Recinfo.billable_basis = p_freight_cost_info.billable_basis)
615 or (Recinfo.billable_basis is NULL
616 and p_freight_cost_info.billable_basis is NULL))
617 ) THEN
618 --
619 -- Debug Statements
620 --
621 IF l_debug_on THEN
622 WSH_DEBUG_SV.pop(l_module_name);
623 END IF;
624 --
625 RETURN;
626 ELSE
627 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
628 IF l_debug_on THEN
629 WSH_DEBUG_SV.logmsg(l_module_name,'FORM_RECORD_DELETED Error has occured',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
630 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FORM_RECORD_CHANGED');
631 END IF;
632 app_exception.raise_exception;
633 END IF;
634
635 --
636 -- Debug Statements
637 --
638 IF l_debug_on THEN
639 WSH_DEBUG_SV.pop(l_module_name);
640 END IF;
641 --
642 EXCEPTION
643 WHEN others THEN
644
645 -- Is this necessary? Does PL/SQL automatically close a
646 -- cursor when it goes out of scope?
647
648 if (lock_row%ISOPEN) then
649 close lock_row;
650 end if;
651
652 --
653 -- Debug Statements
654 --
655 IF l_debug_on THEN
656 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
657 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
658 END IF;
659 --
660 raise;
661
662 --
663 -- Debug Statements
664 --
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.pop(l_module_name);
667 END IF;
668 --
669 END Lock_Freight_Cost;
670
671 PROCEDURE Delete_Freight_Cost(
672 p_rowid IN VARCHAR2
673 , p_freight_cost_id IN NUMBER
674 , x_return_status OUT NOCOPY VARCHAR2
675 )
676 IS
677 CURSOR C_Get_Freight_cost_id
678 IS
679 SELECT freight_cost_id
680 FROM wsh_freight_costs
681 WHERE rowid = p_rowid;
682
683 l_freight_cost_id NUMBER;
684 others EXCEPTION;
685
686 --
687 l_debug_on BOOLEAN;
688 --
689 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FREIGHT_COST';
690 --
691 BEGIN
692 --
693 -- Debug Statements
694 --
695 --
696 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
697 --
698 IF l_debug_on IS NULL
699 THEN
700 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
701 END IF;
702 --
703 IF l_debug_on THEN
704 WSH_DEBUG_SV.push(l_module_name);
705 --
706 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
707 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
708 END IF;
709 --
710 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
711 l_freight_cost_id := p_freight_cost_id;
712
713 IF (p_rowid IS NOT NULL) THEN
714 OPEN C_Get_freight_cost_id;
715 FETCH C_get_freight_cost_id INTO l_freight_cost_id;
716 CLOSE C_Get_Freight_cost_id;
717 END IF;
718
719 IF (l_freight_cost_id IS NOT NULL) THEN
720 DELETE FROM wsh_freight_costs
721 WHERE freight_cost_id = p_freight_cost_id;
722 ELSE
723 RAISE others;
724 END IF;
725
726 --
727 -- Debug Statements
728 --
729 IF l_debug_on THEN
730 WSH_DEBUG_SV.pop(l_module_name);
731 END IF;
732 --
733 EXCEPTION
734 WHEN others THEN
735 wsh_util_core.default_handler('WSH_FREIGHT_COSTS_PVT.DELETE_FREIGHT_COST');
736 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
737 --
738 -- Debug Statements
739 --
740 IF l_debug_on THEN
741 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
742 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
743 END IF;
744 --
745 END Delete_Freight_Cost;
746
747
748 PROCEDURE Split_Freight_Cost(
749 p_from_freight_cost_id IN NUMBER
750 , x_new_freight_cost_id OUT NOCOPY NUMBER
751 , p_new_delivery_detail_id IN NUMBER
752 , p_requested_quantity IN NUMBER
753 , p_split_requested_quantity IN NUMBER
754 , x_return_status OUT NOCOPY VARCHAR2
755 ) IS
756
757
758
759 CURSOR c_freight_cost ( c_freight_cost_id NUMBER ) IS
760 -- Changed for Bug# 3330869
761 -- SELECT *
762 SELECT
763 FREIGHT_COST_TYPE_ID,
764 UNIT_AMOUNT,
765 CURRENCY_CODE,
766 CALCULATION_METHOD,
767 UOM,
768 QUANTITY,
769 CONVERSION_DATE,
770 CONVERSION_RATE,
771 CONVERSION_TYPE_CODE,
772 TRIP_ID,
773 STOP_ID,
774 DELIVERY_ID,
775 DELIVERY_LEG_ID,
776 ATTRIBUTE_CATEGORY,
777 ATTRIBUTE1,
778 ATTRIBUTE2,
779 ATTRIBUTE3,
780 ATTRIBUTE4,
781 ATTRIBUTE5,
782 ATTRIBUTE6,
783 ATTRIBUTE7,
784 ATTRIBUTE8,
785 ATTRIBUTE9,
786 ATTRIBUTE10,
787 ATTRIBUTE11,
788 ATTRIBUTE12,
789 ATTRIBUTE13,
790 ATTRIBUTE14,
791 ATTRIBUTE15,
792 CREATED_BY,
793 LAST_UPDATED_BY,
794 LAST_UPDATE_LOGIN,
795 PROGRAM_APPLICATION_ID,
796 PROGRAM_ID,
797 PROGRAM_UPDATE_DATE,
798 REQUEST_ID,
799 PRICING_LIST_HEADER_ID,
800 PRICING_LIST_LINE_ID,
801 APPLIED_TO_CHARGE_ID,
802 CHARGE_UNIT_VALUE,
803 CHARGE_SOURCE_CODE,
804 LINE_TYPE_CODE,
805 ESTIMATED_FLAG,
806 COMMODITY_CATEGORY_ID
807 FROM WSH_FREIGHT_COSTS
808 WHERE FREIGHT_COST_ID = c_freight_cost_id FOR UPDATE;
809
810 l_from_freight_cost_rec c_freight_cost%ROWTYPE;
811 l_new_freight_cost_rec Freight_Cost_Rec_Type;
812 l_new_unit_amount NUMBER := 0;
813 l_round_unit_amount NUMBER := 0;
814 l_remained_unit_amount NUMBER := 0;
815 l_rowid VARCHAR2(30) := NULL;
816 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
817 l_freight_cost_id NUMBER := 0;
818
819 WSH_FC_NOT_FOUND EXCEPTION;
820
821 --
822 l_debug_on BOOLEAN;
823 --
824 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SPLIT_FREIGHT_COST';
825 --
826 BEGIN
827
828 --
829 -- Debug Statements
830 --
831 --
832 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
833 --
834 IF l_debug_on IS NULL
835 THEN
836 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
837 END IF;
838 --
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.push(l_module_name);
841 --
842 WSH_DEBUG_SV.log(l_module_name,'P_FROM_FREIGHT_COST_ID',P_FROM_FREIGHT_COST_ID);
843 WSH_DEBUG_SV.log(l_module_name,'P_NEW_DELIVERY_DETAIL_ID',P_NEW_DELIVERY_DETAIL_ID);
844 WSH_DEBUG_SV.log(l_module_name,'P_REQUESTED_QUANTITY',P_REQUESTED_QUANTITY);
845 WSH_DEBUG_SV.log(l_module_name,'P_SPLIT_REQUESTED_QUANTITY',P_SPLIT_REQUESTED_QUANTITY);
846 END IF;
847 --
848 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
849
850
851
852 OPEN c_freight_cost( p_from_freight_cost_id);
853 FETCH c_freight_cost INTO l_from_freight_cost_rec;
854 IF c_freight_cost%NOTFOUND THEN
855 RAISE WSH_FC_NOT_FOUND;
856 END IF;
857
858 SELECT wsh_freight_costs_s.nextval INTO l_freight_cost_id FROM sys.dual;
859
860 l_new_freight_cost_rec.FREIGHT_COST_ID := l_freight_cost_id;
861 l_new_freight_cost_rec.FREIGHT_COST_TYPE_ID := l_from_freight_cost_rec.FREIGHT_COST_TYPE_ID ;
862 l_new_unit_amount := l_from_freight_cost_rec.UNIT_AMOUNT *
863 p_split_requested_quantity / p_requested_quantity;
864
865 --
866 -- Debug Statements
867 --
868 IF l_debug_on THEN
869 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FC_INTERFACE_PKG.ROUND_COST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
870 END IF;
871 --
872 WSH_FC_INTERFACE_PKG.Round_Cost_Amount(l_new_unit_amount, l_from_freight_cost_rec.CURRENCY_CODE, l_round_unit_amount, l_return_status);
873 -- round the unit_amount
874 l_remained_unit_amount := l_from_freight_cost_rec.UNIT_AMOUNT - l_round_unit_amount;
875
876 l_new_freight_cost_rec.UNIT_AMOUNT := l_round_unit_amount ;
877 l_new_freight_cost_rec.CALCULATION_METHOD := l_from_freight_cost_rec.CALCULATION_METHOD ;
878 l_new_freight_cost_rec.UOM := l_from_freight_cost_rec.UOM ;
879 l_new_freight_cost_rec.QUANTITY := l_from_freight_cost_rec.QUANTITY ;
880 l_new_freight_cost_rec.TOTAL_AMOUNT := l_round_unit_amount;
881 l_new_freight_cost_rec.CURRENCY_CODE := l_from_freight_cost_rec.CURRENCY_CODE ;
882 l_new_freight_cost_rec.CONVERSION_DATE := l_from_freight_cost_rec.CONVERSION_DATE ;
883 l_new_freight_cost_rec.CONVERSION_RATE := l_from_freight_cost_rec.CONVERSION_RATE ;
884 l_new_freight_cost_rec.CONVERSION_TYPE_CODE := l_from_freight_cost_rec.CONVERSION_TYPE_CODE ;
885 l_new_freight_cost_rec.TRIP_ID := l_from_freight_cost_rec.TRIP_ID ;
886 l_new_freight_cost_rec.STOP_ID := l_from_freight_cost_rec.STOP_ID ;
887 l_new_freight_cost_rec.DELIVERY_ID := l_from_freight_cost_rec.DELIVERY_ID ;
888 l_new_freight_cost_rec.DELIVERY_LEG_ID := l_from_freight_cost_rec.DELIVERY_LEG_ID ;
889 l_new_freight_cost_rec.DELIVERY_DETAIL_ID := p_new_delivery_detail_id ;
890 l_new_freight_cost_rec.ATTRIBUTE_CATEGORY := l_from_freight_cost_rec.ATTRIBUTE_CATEGORY ;
891 l_new_freight_cost_rec.ATTRIBUTE1 := l_from_freight_cost_rec.ATTRIBUTE1 ;
892 l_new_freight_cost_rec.ATTRIBUTE2 := l_from_freight_cost_rec.ATTRIBUTE2 ;
893 l_new_freight_cost_rec.ATTRIBUTE3 := l_from_freight_cost_rec.ATTRIBUTE3 ;
894 l_new_freight_cost_rec.ATTRIBUTE4 := l_from_freight_cost_rec.ATTRIBUTE4 ;
895 l_new_freight_cost_rec.ATTRIBUTE5 := l_from_freight_cost_rec.ATTRIBUTE5 ;
896 l_new_freight_cost_rec.ATTRIBUTE6 := l_from_freight_cost_rec.ATTRIBUTE6 ;
897 l_new_freight_cost_rec.ATTRIBUTE7 := l_from_freight_cost_rec.ATTRIBUTE7 ;
898 l_new_freight_cost_rec.ATTRIBUTE8 := l_from_freight_cost_rec.ATTRIBUTE8 ;
899 l_new_freight_cost_rec.ATTRIBUTE9 := l_from_freight_cost_rec.ATTRIBUTE9 ;
900 l_new_freight_cost_rec.ATTRIBUTE10 := l_from_freight_cost_rec.ATTRIBUTE10 ;
901 l_new_freight_cost_rec.ATTRIBUTE11 := l_from_freight_cost_rec.ATTRIBUTE11 ;
902 l_new_freight_cost_rec.ATTRIBUTE12 := l_from_freight_cost_rec.ATTRIBUTE12 ;
903 l_new_freight_cost_rec.ATTRIBUTE13 := l_from_freight_cost_rec.ATTRIBUTE13 ;
904 l_new_freight_cost_rec.ATTRIBUTE14 := l_from_freight_cost_rec.ATTRIBUTE14 ;
905 l_new_freight_cost_rec.ATTRIBUTE15 := l_from_freight_cost_rec.ATTRIBUTE15 ;
909 l_new_freight_cost_rec.LAST_UPDATED_BY := l_from_freight_cost_rec.LAST_UPDATED_BY ;
906 l_new_freight_cost_rec.CREATION_DATE := sysdate;
907 l_new_freight_cost_rec.CREATED_BY := l_from_freight_cost_rec.CREATED_BY ;
908 l_new_freight_cost_rec.LAST_UPDATE_DATE := sysdate;
910 l_new_freight_cost_rec.LAST_UPDATE_LOGIN := l_from_freight_cost_rec.LAST_UPDATE_LOGIN ;
911 l_new_freight_cost_rec.PROGRAM_APPLICATION_ID := l_from_freight_cost_rec.PROGRAM_APPLICATION_ID ;
912 l_new_freight_cost_rec.PROGRAM_ID := l_from_freight_cost_rec.PROGRAM_ID ;
913 l_new_freight_cost_rec.PROGRAM_UPDATE_DATE := l_from_freight_cost_rec.PROGRAM_UPDATE_DATE ;
914 l_new_freight_cost_rec.REQUEST_ID := l_from_freight_cost_rec.REQUEST_ID ;
915 /* H Integration: datamodel changes wrudge */
916 l_new_freight_cost_rec.PRICING_LIST_HEADER_ID := l_from_freight_cost_rec.pricing_list_header_id ;
917 l_new_freight_cost_rec.PRICING_LIST_LINE_ID := l_from_freight_cost_rec.pricing_list_line_id ;
918 l_new_freight_cost_rec.APPLIED_TO_CHARGE_ID := l_from_freight_cost_rec.applied_to_charge_id ;
919 /* H Integration: Open issue: how does FTE want freight cost record split? */
920 l_new_freight_cost_rec.CHARGE_UNIT_VALUE := l_from_freight_cost_rec.charge_unit_value ;
921 l_new_freight_cost_rec.CHARGE_SOURCE_CODE := l_from_freight_cost_rec.charge_source_code ;
922 l_new_freight_cost_rec.LINE_TYPE_CODE := l_from_freight_cost_rec.line_type_code ;
923 l_new_freight_cost_rec.ESTIMATED_FLAG := l_from_freight_cost_rec.estimated_flag ;
924 l_new_freight_cost_rec.commodity_category_id := l_from_freight_cost_rec.commodity_category_id ;
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Create_Freight_Cost',WSH_DEBUG_SV.C_PROC_LEVEL);
927 END IF;
928 Create_Freight_Cost(
929 p_freight_cost_info => l_new_freight_cost_rec
930 , x_rowid => l_rowid
931 , x_freight_cost_id => l_freight_cost_id
932 , x_return_status => l_return_status
933 );
934
935 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
936 UPDATE WSH_FREIGHT_COSTS
937 SET UNIT_AMOUNT = l_remained_unit_amount
938 WHERE CURRENT OF c_freight_cost;
939 ELSE
940 x_return_status := l_return_status;
941 END IF;
942
943 CLOSE c_freight_cost;
944
945 --
946 -- Debug Statements
947 --
948 IF l_debug_on THEN
949 WSH_DEBUG_SV.pop(l_module_name);
950 END IF;
951 --
952 EXCEPTION
953
954 WHEN WSH_FC_NOT_FOUND THEN
955 wsh_util_core.default_handler('WSH_FREIGHT_COSTS_PVT.SPLIT_FREIGHT_COST');
956 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
957
958 --
959 -- Debug Statements
960 --
961 IF l_debug_on THEN
962 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FC_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
963 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FC_NOT_FOUND');
964 END IF;
965 --
966 END Split_Freight_Cost;
967
968 --This procedure needs to be removed as this is no longer used - post I.
969 --Waiting for STF changes before removing this.
970 --Replaced by another procedure with same name
971 PROCEDURE Get_Total_Freight_Cost(
972 p_entity_level IN VARCHAR2,
973 p_entity_id IN NUMBER,
974 p_currency_code IN VARCHAR2,
975 x_total_amount OUT NOCOPY NUMBER ,
976 x_return_status OUT NOCOPY VARCHAR2) IS
977
978 l_debug_on BOOLEAN;
979 --
980 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Total_Freight_Cost';
981
982
983 Cursor freight_cost_at_delivery(c_delivery_id number) IS
984 SELECT unit_amount,
985 currency_code,
986 NVL(conversion_type_code, 'Corporate'),
987 NVL(conversion_date, SYSDATE),
988 conversion_rate
989 FROM wsh_freight_costs
990 WHERE delivery_id = c_delivery_id AND
991 NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
992 NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
993 freight_cost_type_id <> -1 AND
994 unit_amount IS NOT NULL;
995
996 Cursor freight_cost_in_delivery(c_delivery_id number) IS
997 SELECT a.unit_amount,
998 a.currency_code,
999 NVL(conversion_type_code, 'Corporate'),
1000 NVL(conversion_date, SYSDATE),
1001 conversion_rate
1002 FROM wsh_freight_costs a
1003 WHERE a.delivery_detail_id in (
1004 SELECT c.delivery_detail_id
1005 FROM wsh_delivery_assignments_v b,
1006 wsh_delivery_details c
1007 WHERE b.delivery_id = c_delivery_id AND
1008 c.delivery_detail_id = b.delivery_detail_id AND
1009 c.released_status <> 'D' ) AND
1010 NVL(a.charge_source_code, 'MANUAL' ) = 'MANUAL' AND
1011 NVL(a.line_type_code, 'CHARGE') = 'CHARGE' AND
1012 a.freight_cost_type_id <> -1 AND
1013 a.unit_amount is NOT NULL;
1014
1015 l_freight_cost NUMBER := 0;
1016 l_currency_code VARCHAR2(15) := NULL;
1017 l_conversion_type_code VARCHAR2(15) := NULL;
1018 l_conversion_date DATE;
1019 l_conversion_rate NUMBER;
1020 l_total_amount NUMBER := 0;
1021 l_return_status VARCHAR2(10) := NULL;
1022 l_converted_amount NUMBER := 0;
1023 l_msg_data VARCHAR2(2000);
1024 convert_amount_error EXCEPTION;
1025
1026 BEGIN
1027 --
1028 -- Debug Statements
1029 --
1030 --
1034 THEN
1031 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1032 --
1033 IF l_debug_on IS NULL
1035 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1036 END IF;
1037 --
1038 IF l_debug_on THEN
1039 WSH_DEBUG_SV.push(l_module_name);
1040 END IF;
1041 --
1042 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1043
1044 x_total_amount := NULL;
1045
1046 IF p_entity_level = 'Delivery' THEN
1047 OPEN freight_cost_at_delivery(p_entity_id);
1048 LOOP
1049 FETCH freight_cost_at_delivery INTO l_freight_cost,
1050 l_currency_code,
1051 l_conversion_type_code,
1052 l_conversion_date,
1053 l_conversion_rate;
1054
1055 EXIT WHEN freight_cost_at_delivery%NOTFOUND ;
1056 Convert_Amount (
1057 p_from_currency => l_currency_code,
1058 p_to_currency => p_currency_code,
1059 p_conversion_date => l_conversion_date,
1060 p_conversion_rate => l_conversion_rate,
1061 p_conversion_type => l_conversion_type_code,
1062 p_amount => l_freight_cost,
1063 x_converted_amount => l_converted_amount,
1064 x_return_status => l_return_status) ;
1065
1066 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1067 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1068 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1069 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1070 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1071 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1072 END IF;
1073 ELSE
1074 raise convert_amount_error;
1075 END IF;
1076
1077
1078 END LOOP;
1079 CLOSE freight_cost_at_delivery;
1080
1081 OPEN freight_cost_in_delivery(p_entity_id);
1082 LOOP
1083 FETCH freight_cost_in_delivery INTO l_freight_cost,
1084 l_currency_code,
1085 l_conversion_type_code,
1086 l_conversion_date,
1087 l_conversion_rate;
1088
1089 EXIT WHEN freight_cost_in_delivery%NOTFOUND ;
1090 Convert_Amount (
1091 p_from_currency => l_currency_code,
1092 p_to_currency => p_currency_code,
1093 p_conversion_date => l_conversion_date,
1094 p_conversion_rate => l_conversion_rate,
1095 p_conversion_type => l_conversion_type_code,
1096 p_amount => l_freight_cost,
1097 x_converted_amount => l_converted_amount,
1098 x_return_status => l_return_status) ;
1099
1100 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1101 l_total_amount := l_total_amount + NVL(l_converted_amount, 0);
1102 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1103 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1104 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1105 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1106 END IF;
1107 ELSE
1108 raise convert_amount_error;
1109 END IF;
1110 END LOOP;
1111
1112 CLOSE freight_cost_in_delivery;
1113
1114 x_total_amount := l_total_amount;
1115
1116 END IF;
1117 --
1118 -- Debug Statements
1119 --
1120 IF l_debug_on THEN
1121 WSH_DEBUG_SV.pop(l_module_name);
1122 END IF;
1123 --
1124 EXCEPTION
1125
1126 WHEN convert_amount_error THEN
1127 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1128
1129 x_total_amount := NULL;
1130 IF l_debug_on THEN
1131 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Total_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1132 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Total_Freight_Cost FILURE');
1133 END IF;
1134
1135 WHEN OTHERS THEN
1136
1137 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1138
1139 x_total_amount := NULL;
1140 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_Total_Freight_Cost');
1141
1142 IF l_debug_on THEN
1143 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1144 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1145 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1146 END IF;
1147
1148
1149 END Get_Total_Freight_Cost;
1150
1151
1152 --TL Rating
1153 PROCEDURE Get_Detail_Freight_Cost(
1154 p_in_ids IN wsh_util_core.id_tab_type,
1155 p_currency_code IN VARCHAR2,
1156 x_detail_amount OUT NOCOPY NUMBER ,
1157 x_return_status OUT NOCOPY VARCHAR2) IS
1158
1159 l_debug_on BOOLEAN;
1160 --
1161 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Detail_Freight_Cost';
1162
1163 Cursor freight_cost_at_detail(c_det_id NUMBER) IS
1164 SELECT a.unit_amount,
1165 a.currency_code,
1166 NVL(conversion_type_code, 'Corporate'),
1170 WHERE wdd.delivery_detail_id=c_det_id AND
1167 NVL(conversion_date, SYSDATE),
1168 conversion_rate
1169 FROM wsh_freight_costs a, wsh_delivery_details wdd
1171 wdd.released_status <> 'D' AND
1172 a.delivery_detail_id = wdd.delivery_detail_id AND
1173 NVL(a.charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
1174 NVL(a.line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
1175 a.freight_cost_type_id <> -1 AND
1176 a.unit_amount > 0 ;
1177
1178 l_freight_cost NUMBER:=0;
1179 l_currency_code VARCHAR2(15) := NULL;
1180 l_conversion_type_code VARCHAR2(15) := NULL;
1181 l_conversion_date DATE;
1182 l_conversion_rate NUMBER;
1183 l_total_amount NUMBER := 0;
1184 l_return_status VARCHAR2(10) := NULL;
1185 l_converted_amount NUMBER := 0;
1186 l_msg_data VARCHAR2(2000);
1187 convert_amount_error EXCEPTION;
1188 l_index NUMBER;
1189 BEGIN
1190 --
1191 -- Debug Statements
1192 --
1193 --
1194 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1195 --
1196 IF l_debug_on IS NULL
1197 THEN
1198 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1199 END IF;
1200 --
1201 IF l_debug_on THEN
1202 WSH_DEBUG_SV.push(l_module_name);
1203 END IF;
1204 --
1205 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1206
1207 x_detail_amount := NULL;
1208 l_index:=p_in_ids.FIRST;
1209 WHILE l_index is not null LOOP
1210 OPEN freight_cost_at_detail(p_in_ids(l_index));
1211 LOOP
1212 FETCH freight_cost_at_detail INTO l_freight_cost,
1213 l_currency_code,
1214 l_conversion_type_code,
1215 l_conversion_date,
1216 l_conversion_rate;
1217
1218 EXIT WHEN freight_cost_at_detail%NOTFOUND ;
1219 Convert_Amount (
1220 p_from_currency => l_currency_code,
1221 p_to_currency => p_currency_code,
1222 p_conversion_date => l_conversion_date,
1223 p_conversion_rate => l_conversion_rate,
1224 p_conversion_type => l_conversion_type_code,
1225 p_amount => l_freight_cost,
1226 x_converted_amount => l_converted_amount,
1227 x_return_status => l_return_status) ;
1228
1229 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1230 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1231 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1232 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1233 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1234 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1235 END IF;
1236 ELSE
1237 raise convert_amount_error;
1238 END IF;
1239 END LOOP;
1240 CLOSE freight_cost_at_detail;
1241
1242 l_index:=p_in_ids.next(l_index);
1243 END LOOP;
1244
1245 x_detail_amount := l_total_amount;
1246
1247 IF l_debug_on THEN
1248 WSH_DEBUG_SV.log(l_module_name,'amounts detail: '||l_total_amount);
1249 END IF;
1250
1251 --
1252 -- Debug Statements
1253 --
1254 IF l_debug_on THEN
1255 WSH_DEBUG_SV.pop(l_module_name);
1256 END IF;
1257 --
1258 EXCEPTION
1259
1260 WHEN convert_amount_error THEN
1261 CLOSE freight_cost_at_detail;
1262 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1263 x_detail_amount := NULL;
1264 IF l_debug_on THEN
1265 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Detail_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1266 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Detail_Freight_Cost FILURE');
1267 END IF;
1268
1269 WHEN OTHERS THEN
1270 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1271 x_detail_amount := NULL;
1272 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_detail_Freight_Cost');
1273 IF l_debug_on THEN
1274 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1275 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1276 END IF;
1277
1278 END Get_Detail_Freight_Cost;
1279
1280 PROCEDURE Get_LPN_Freight_Cost(
1281 p_in_ids IN wsh_util_core.id_tab_type,
1282 p_currency_code IN VARCHAR2,
1283 x_main_lpn_amount OUT NOCOPY NUMBER, --to be used only if calling entity is LPN
1284 x_lpn_amount OUT NOCOPY NUMBER,
1285 x_detail_amount OUT NOCOPY NUMBER ,
1286 x_return_status OUT NOCOPY VARCHAR2) IS
1287
1288 l_debug_on BOOLEAN;
1289 --
1290 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_LPN_Freight_Cost';
1291
1292 CURSOR c_getchildren (p_detailid NUMBER) IS
1293 SELECT delivery_detail_id
1294 FROM wsh_delivery_assignments_v wda
1295 WHERE LEVEL <= 10
1296 START WITH delivery_detail_id = p_detailid
1297 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
1298
1299 CURSOR c_iscontainer(p_detailid NUMBER) IS
1300 select 'Y'
1301 from wsh_delivery_details
1305 l_dd_ids wsh_util_core.id_tab_type;
1302 where delivery_detail_id=p_detailid
1303 and container_flag='Y';
1304
1306 l_cont_ids wsh_util_core.id_tab_type;
1307
1308 l_freight_cost NUMBER := 0;
1309 l_currency_code VARCHAR2(15) := NULL;
1310 l_conversion_type_code VARCHAR2(15) := NULL;
1311 l_conversion_date DATE;
1312 l_conversion_rate NUMBER;
1313 l_total_amount NUMBER := 0;
1314 l_return_status VARCHAR2(10) := NULL;
1315 l_converted_amount NUMBER := 0;
1316 l_msg_data VARCHAR2(2000);
1317 convert_amount_error EXCEPTION;
1318 details_freight_error EXCEPTION;
1319
1320 l_detail_amount_temp NUMBER;
1321 l_detail_amount NUMBER;
1322 l_main_lpn_amount NUMBER;
1323 l_lpn_amount NUMBER;
1324
1325 l_index NUMBER;
1326 l_dummy VARCHAR2(1);
1327 BEGIN
1328 --
1329 -- Debug Statements
1330 --
1331 --
1332 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1333 --
1334 IF l_debug_on IS NULL
1335 THEN
1336 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1337 END IF;
1338 --
1339 IF l_debug_on THEN
1340 WSH_DEBUG_SV.push(l_module_name);
1341 END IF;
1342 --
1343 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1344
1345 x_lpn_amount := NULL;
1346 x_detail_amount:=null;
1347
1348 --get containers and loose items and call them separately
1349
1350 l_index:=p_in_ids.FIRST;
1351 WHILE l_index is not null LOOP
1352 --get all children, check if they're containers
1353 FOR cur IN c_getchildren(p_in_ids(l_index)) LOOP
1354 OPEN c_iscontainer(cur.delivery_detail_id);
1355 FETCH c_iscontainer into l_dummy;
1356 IF c_iscontainer%NOTFOUND THEN
1357 l_dd_ids(l_dd_ids.COUNT+1):=cur.delivery_detail_id;
1358 ELSE
1359 l_cont_ids(l_cont_ids.COUNT+1):=cur.delivery_detail_id;
1360 END IF;
1361 CLOSE c_iscontainer;
1362 END LOOP;
1363
1364 l_index:=p_in_ids.next(l_index);
1365 END LOOP;
1366
1367 IF l_dd_ids is not null and l_dd_ids.COUNT>0 THEN
1368 get_detail_freight_cost(
1369 p_in_ids => l_dd_ids,
1370 p_currency_code => p_currency_code,
1371 x_detail_amount => l_detail_amount ,
1372 x_return_status => l_return_status);
1373 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1374 raise details_freight_error;
1375 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1376 x_return_status:=l_return_status;
1377 END IF;
1378 END IF;
1379
1380 IF l_cont_ids is not null and l_cont_ids.COUNT>0 THEN
1381 get_detail_freight_cost(
1382 p_in_ids => l_cont_ids,
1383 p_currency_code => p_currency_code,
1384 x_detail_amount => l_lpn_amount,
1385 x_return_status => l_return_status);
1386 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1387 raise details_freight_error;
1388 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1389 x_return_status:=l_return_status;
1390 END IF;
1391 END IF;
1392
1393 IF p_in_ids is not null and p_in_ids.COUNT>0 THEN
1394 get_detail_freight_cost(
1395 p_in_ids => p_in_ids,
1396 p_currency_code => p_currency_code,
1397 x_detail_amount => l_main_lpn_amount,
1398 x_return_status => l_return_status);
1399 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1400 raise details_freight_error;
1401 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1402 x_return_status:=l_return_status;
1403 END IF;
1404 END IF;
1405
1406 x_main_lpn_amount:=l_main_lpn_amount;
1407 x_detail_amount := l_detail_amount;
1408 x_lpn_amount := l_lpn_amount;
1409
1410 IF l_debug_on THEN
1411 WSH_DEBUG_SV.log(l_module_name,'amounts main lpn, other lpn, detail: '||l_main_lpn_amount||', '||l_lpn_amount||', '||l_detail_amount);
1412 END IF;
1413
1414 --
1415 -- Debug Statements
1416 --
1417 IF l_debug_on THEN
1418 WSH_DEBUG_SV.pop(l_module_name);
1419 END IF;
1420 --
1421 EXCEPTION
1422 WHEN details_freight_error THEN
1423 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1424 x_main_lpn_amount := NULL;
1425 x_lpn_amount := NULL;
1426 x_detail_amount := NULL;
1427 IF l_debug_on THEN
1428 WSH_DEBUG_SV.logmsg(l_module_name,'Get_LPN_Freight_Cost exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1429 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_LPN_Freight_Cost FILURE');
1430 END IF;
1431 WHEN OTHERS THEN
1432 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1433 x_main_lpn_amount := NULL;
1434 x_lpn_amount := NULL;
1435 x_detail_amount := NULL;
1436 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_LPN_Freight_Cost');
1437 IF l_debug_on THEN
1438 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1442 END Get_LPN_Freight_Cost;
1439 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1440 END IF;
1441
1443
1444
1445 PROCEDURE Get_Delivery_Freight_Cost(
1446 p_in_ids IN wsh_util_core.id_tab_type,
1447 p_currency_code IN VARCHAR2,
1448 x_delivery_amount OUT NOCOPY NUMBER,
1449 x_lpn_amount OUT NOCOPY NUMBER,
1450 x_detail_amount OUT NOCOPY NUMBER ,
1451 x_return_status OUT NOCOPY VARCHAR2) IS
1452
1453 l_debug_on BOOLEAN;
1454 --
1455 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Delivery_Freight_Cost';
1456
1457
1458 Cursor freight_cost_at_delivery(c_delivery_id number) IS
1459 SELECT unit_amount,
1460 currency_code,
1461 NVL(conversion_type_code, 'Corporate'),
1462 NVL(conversion_date, SYSDATE),
1463 conversion_rate
1464 FROM wsh_freight_costs
1465 WHERE delivery_id = c_delivery_id AND
1466 NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
1467 NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
1468 freight_cost_type_id <> -1 AND
1469 unit_amount > 0 AND
1470 delivery_detail_id is null;
1471
1472 l_dd_ids wsh_util_core.id_tab_type;
1473 l_cont_ids wsh_util_core.id_tab_type;
1474
1475
1476 l_freight_cost NUMBER := 0;
1477 l_currency_code VARCHAR2(15) := NULL;
1478 l_conversion_type_code VARCHAR2(15) := NULL;
1479 l_conversion_date DATE;
1480 l_conversion_rate NUMBER;
1481 l_total_amount NUMBER := 0;
1482 l_return_status VARCHAR2(10) := NULL;
1483 l_converted_amount NUMBER := 0;
1484 l_msg_data VARCHAR2(2000);
1485 convert_amount_error EXCEPTION;
1486 details_freight_error EXCEPTION;
1487
1488 l_detail_amount_temp NUMBER;
1489 l_detail_amount NUMBER;
1490 l_lpn_amount NUMBER;
1491 l_main_lpn_amount NUMBER;
1492 l_index NUMBER;
1493 -- bugfix 6692716 replaced bind variable l_container_flag_yes or no by 'Y' and 'N'
1494 --l_container_flag_yes varchar2(1) ; -- BugFix3788678
1495 --l_container_flag_no varchar2(1) ; -- BugFix3788678
1496
1497 cursor c_get_contindel(p_delid NUMBER) is
1498 select wdd.delivery_detail_id
1499 from wsh_delivery_assignments_v wda, wsh_delivery_details wdd
1500 where wda.delivery_detail_id=wdd.delivery_detail_id
1501 and wdd.container_flag= 'Y'
1502 and wda.delivery_id=p_delid
1503 and wda.parent_delivery_detail_id is null;
1504
1505 cursor c_get_ddindel(p_delid NUMBER) is
1506 select wdd.delivery_detail_id
1507 from wsh_delivery_assignments_v wda, wsh_delivery_details wdd
1508 where wda.delivery_detail_id=wdd.delivery_detail_id
1509 and wdd.container_flag= 'N'
1510 and wda.delivery_id=p_delid
1511 and wda.parent_delivery_detail_id is null;
1512
1513 BEGIN
1514 --
1515 -- Debug Statements
1516 --
1517 --
1518 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1519 --
1520 IF l_debug_on IS NULL
1521 THEN
1522 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1523 END IF;
1524 --
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.push(l_module_name);
1527 END IF;
1528 --
1529 --bugfix 6692716
1530 --l_container_flag_yes := 'Y'; -- BugFix3788678
1531 --l_container_flag_no := 'N'; -- BugFix3788678
1532
1533 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1534
1535 x_delivery_amount := NULL;
1536
1537 l_index:=p_in_ids.FIRST;
1538 WHILE l_index is not null LOOP
1539 OPEN freight_cost_at_delivery(p_in_ids(l_index));
1540 LOOP
1541 FETCH freight_cost_at_delivery INTO l_freight_cost,
1542 l_currency_code,
1543 l_conversion_type_code,
1544 l_conversion_date,
1545 l_conversion_rate;
1546
1547 EXIT WHEN freight_cost_at_delivery%NOTFOUND ;
1548 Convert_Amount (
1549 p_from_currency => l_currency_code,
1550 p_to_currency => p_currency_code,
1551 p_conversion_date => l_conversion_date,
1552 p_conversion_rate => l_conversion_rate,
1553 p_conversion_type => l_conversion_type_code,
1554 p_amount => l_freight_cost,
1555 x_converted_amount => l_converted_amount,
1556 x_return_status => l_return_status) ;
1557
1558 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1559 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1560 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1561 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1562 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1563 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1564 END IF;
1565 ELSE
1566 raise convert_amount_error;
1567 END IF;
1568 END LOOP;
1569 CLOSE freight_cost_at_delivery;
1570
1571 --get containers and loose items and call them separately
1572 FOR cur IN c_get_contindel(p_in_ids(l_index)) LOOP
1573 l_cont_ids(l_cont_ids.COUNT+1):=cur.delivery_detail_id;
1574 END LOOP;
1575
1576 FOR cur IN c_get_ddindel(p_in_ids(l_index)) LOOP
1580 l_index:=p_in_ids.next(l_index);
1577 l_dd_ids(l_dd_ids.COUNT+1):=cur.delivery_detail_id;
1578 END LOOP;
1579
1581 END LOOP;
1582
1583 IF l_dd_ids is not null and l_dd_ids.COUNT>0 THEN
1584 get_detail_freight_cost(
1585 p_in_ids => l_dd_ids,
1586 p_currency_code => p_currency_code,
1587 x_detail_amount => l_detail_amount ,
1588 x_return_status => l_return_status);
1589 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1590 raise details_freight_error;
1591 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1592 x_return_status:=l_return_status;
1593 END IF;
1594 END IF;
1595
1596 IF l_cont_ids is not null and l_cont_ids.COUNT>0 THEN
1597 get_lpn_freight_cost(
1598 p_in_ids => l_cont_ids,
1599 p_currency_code => p_currency_code,
1600 x_detail_amount => l_detail_amount_temp ,
1601 x_main_lpn_amount => l_main_lpn_amount,
1602 x_lpn_amount => l_lpn_amount,
1603 x_return_status => l_return_status);
1604 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1605 raise details_freight_error;
1606 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1607 x_return_status:=l_return_status;
1608 END IF;
1609 END IF;
1610
1611 l_detail_amount:=nvl(l_detail_amount,0)+nvl(l_detail_amount_temp,0);
1612
1613 x_delivery_amount := l_total_amount;
1614 x_detail_amount := l_detail_amount;
1615 x_lpn_amount := l_lpn_amount;
1616
1617 IF l_debug_on THEN
1618 WSH_DEBUG_SV.log(l_module_name,'amounts del, lpn, detail: '||l_total_amount||', '||l_lpn_amount||', '||l_detail_amount);
1619 END IF;
1620
1621 --
1622 -- Debug Statements
1623 --
1624 IF l_debug_on THEN
1625 WSH_DEBUG_SV.pop(l_module_name);
1626 END IF;
1627 --
1628 EXCEPTION
1629 WHEN details_freight_error THEN
1630 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1631 x_delivery_amount := NULL;
1632 x_lpn_amount := NULL;
1633 x_detail_amount := NULL;
1634 IF l_debug_on THEN
1635 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Delivery_Freight_Cost exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1636 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Delivery_Freight_Cost FILURE');
1637 END IF;
1638 WHEN convert_amount_error THEN
1639 CLOSE freight_cost_at_delivery;
1640 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1641 x_delivery_amount := NULL;
1642 x_lpn_amount := NULL;
1643 x_detail_amount := NULL;
1644 IF l_debug_on THEN
1645 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Delivery_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1646 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Delivery_Freight_Cost FILURE');
1647 END IF;
1648
1649 WHEN OTHERS THEN
1650 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1651 x_delivery_amount := NULL;
1652 x_lpn_amount := NULL;
1653 x_detail_amount := NULL;
1654 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_delivery_Freight_Cost');
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1657 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1658 END IF;
1659
1660 END Get_Delivery_Freight_Cost;
1661
1662 PROCEDURE Get_Stop_Freight_Cost(
1663 p_in_ids IN wsh_util_core.id_tab_type,
1664 p_currency_code IN VARCHAR2,
1665 x_stop_amount OUT NOCOPY NUMBER ,
1666 x_return_status OUT NOCOPY VARCHAR2) IS
1667
1668 l_debug_on BOOLEAN;
1669 --
1670 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Stop_Freight_Cost';
1671
1672 Cursor freight_cost_at_stop(c_stop_id number) IS
1673 SELECT freight_cost_id,
1674 unit_amount,
1675 currency_code,
1676 NVL(conversion_type_code, 'Corporate'),
1677 NVL(conversion_date, SYSDATE),
1678 conversion_rate
1679 FROM wsh_freight_costs
1680 WHERE stop_id = c_stop_id AND
1681 NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
1682 NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
1683 freight_cost_type_id <> -1 AND
1684 unit_amount > 0
1685 union
1686 SELECT wfc.freight_cost_id,
1687 wfc.unit_amount,
1688 wfc.currency_code,
1689 NVL(wfc.conversion_type_code, 'Corporate'),
1690 NVL(wfc.conversion_date, SYSDATE),
1691 wfc.conversion_rate
1692 FROM wsh_freight_costs wfc, wsh_freight_cost_types wfct
1693 WHERE wfc.stop_id = c_stop_id AND
1694 NVL(wfc.charge_source_code, 'MANUAL') = 'PRICING_ENGINE' AND
1695 NVL(wfc.line_type_code, 'CHARGE') ='SUMMARY' AND
1696 wfc.freight_cost_type_id <> -1 AND
1697 wfc.unit_amount > 0 AND
1698 wfct.FREIGHT_COST_TYPE_ID = wfc.FREIGHT_COST_TYPE_ID AND
1699 NOT (wfct.name='SUMMARY' and wfct.freight_cost_type_code='FTESUMMARY')
1700 ;
1701
1702 l_freight_cost NUMBER:=0;
1703 l_currency_code VARCHAR2(15) := NULL;
1707 l_total_amount NUMBER := 0;
1704 l_conversion_type_code VARCHAR2(15) := NULL;
1705 l_conversion_date DATE;
1706 l_conversion_rate NUMBER;
1708 l_return_status VARCHAR2(10) := NULL;
1709 l_converted_amount NUMBER := 0;
1710 l_msg_data VARCHAR2(2000);
1711 l_freight_cost_id NUMBER;
1712
1713 convert_amount_error EXCEPTION;
1714 l_index NUMBER;
1715 BEGIN
1716 --
1717 -- Debug Statements
1718 --
1719 --
1720 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1721 --
1722 IF l_debug_on IS NULL
1723 THEN
1724 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1725 END IF;
1726 --
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.push(l_module_name);
1729 END IF;
1730 --
1731 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1732
1733 x_stop_amount := NULL;
1734 l_index:=p_in_ids.FIRST;
1735 WHILE l_index is not null LOOP
1736 OPEN freight_cost_at_stop(p_in_ids(l_index));
1737 LOOP
1738 FETCH freight_cost_at_stop INTO l_freight_cost_id,
1739 l_freight_cost,
1740 l_currency_code,
1741 l_conversion_type_code,
1742 l_conversion_date,
1743 l_conversion_rate;
1744
1745 EXIT WHEN freight_cost_at_stop%NOTFOUND ;
1746 Convert_Amount (
1747 p_from_currency => l_currency_code,
1748 p_to_currency => p_currency_code,
1749 p_conversion_date => l_conversion_date,
1750 p_conversion_rate => l_conversion_rate,
1751 p_conversion_type => l_conversion_type_code,
1752 p_amount => l_freight_cost,
1753 x_converted_amount => l_converted_amount,
1754 x_return_status => l_return_status) ;
1755
1756 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1757 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1758 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1759 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1760 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1761 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1762 END IF;
1763 ELSE
1764 raise convert_amount_error;
1765 END IF;
1766
1767
1768 END LOOP;
1769 CLOSE freight_cost_at_stop;
1770
1771 l_index:=p_in_ids.next(l_index);
1772 END LOOP;
1773
1774 x_stop_amount := l_total_amount;
1775
1776 IF l_debug_on THEN
1777 WSH_DEBUG_SV.log(l_module_name,'amounts stop : '||l_total_amount);
1778 END IF;
1779
1780 --
1781 -- Debug Statements
1782 --
1783 IF l_debug_on THEN
1784 WSH_DEBUG_SV.pop(l_module_name);
1785 END IF;
1786 --
1787 EXCEPTION
1788
1789 WHEN convert_amount_error THEN
1790 CLOSE freight_cost_at_stop;
1791 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1792 x_stop_amount := NULL;
1793 IF l_debug_on THEN
1794 WSH_DEBUG_SV.logmsg(l_module_name,'Get_stop_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1795 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_stop_Freight_Cost FILURE');
1796 END IF;
1797
1798 WHEN OTHERS THEN
1799 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1800 x_stop_amount := NULL;
1801 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_stop_Freight_Cost');
1802 IF l_debug_on THEN
1803 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1804 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1805 END IF;
1806
1807 END Get_Stop_Freight_Cost;
1808
1809 PROCEDURE Get_Trip_Freight_Cost(
1810 p_in_ids IN wsh_util_core.id_tab_type,
1811 p_currency_code IN VARCHAR2,
1812 x_trip_amount OUT NOCOPY NUMBER,
1813 x_stop_amount OUT NOCOPY NUMBER,
1814 x_delivery_amount OUT NOCOPY NUMBER,
1815 x_lpn_amount OUT NOCOPY NUMBER,
1816 x_detail_amount OUT NOCOPY NUMBER ,
1817 x_return_status OUT NOCOPY VARCHAR2) IS
1818
1819 l_debug_on BOOLEAN;
1820 --
1821 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Trip_Freight_Cost';
1822
1823 cursor c_get_stops(p_tripid NUMBER) is
1824 select stop_id
1825 from wsh_trip_stops
1826 where trip_id=p_tripid;
1827
1828 cursor c_get_dels(p_tripid NUMBER) is
1829 select delivery_id
1830 from wsh_delivery_legs wdl, wsh_trip_stops wts
1831 where wdl.pick_up_stop_id=wts.stop_id
1832 and wts.trip_id=p_tripid;
1833
1834 Cursor freight_cost_at_trip(c_trip_id number) IS
1835 SELECT freight_cost_id,
1836 unit_amount,
1837 currency_code,
1838 NVL(conversion_type_code, 'Corporate'),
1839 NVL(conversion_date, SYSDATE),
1840 conversion_rate
1841 FROM wsh_freight_costs
1842 WHERE trip_id = c_trip_id AND
1843 NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
1844 NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
1845 freight_cost_type_id <> -1 AND
1846 unit_amount > 0
1847 union
1848 SELECT wfc.freight_cost_id,
1852 NVL(wfc.conversion_date, SYSDATE),
1849 wfc.unit_amount,
1850 wfc.currency_code,
1851 NVL(wfc.conversion_type_code, 'Corporate'),
1853 wfc.conversion_rate
1854 FROM wsh_freight_costs wfc, wsh_freight_cost_types wfct
1855 WHERE wfc.trip_id = c_trip_id AND
1856 NVL(wfc.charge_source_code, 'MANUAL') = 'PRICING_ENGINE' AND
1857 NVL(wfc.line_type_code, 'CHARGE') ='SUMMARY' AND
1858 wfc.freight_cost_type_id <> -1 AND
1859 wfc.unit_amount > 0 AND
1860 wfct.FREIGHT_COST_TYPE_ID = wfc.FREIGHT_COST_TYPE_ID AND
1861 NOT (wfct.name='SUMMARY' and wfct.freight_cost_type_code='FTESUMMARY');
1862 l_stop_ids wsh_util_core.id_tab_type;
1863 l_del_ids wsh_util_core.id_tab_type;
1864
1865
1866 l_freight_cost NUMBER := 0;
1867 l_currency_code VARCHAR2(15) := NULL;
1868 l_conversion_type_code VARCHAR2(15) := NULL;
1869 l_conversion_date DATE;
1870 l_conversion_rate NUMBER;
1871 l_total_amount NUMBER := 0;
1872 l_return_status VARCHAR2(10) := NULL;
1873 l_converted_amount NUMBER := 0;
1874 l_msg_data VARCHAR2(2000);
1875 l_freight_cost_id NUMBER;
1876 convert_amount_error EXCEPTION;
1877 details_freight_error EXCEPTION;
1878
1879 l_detail_amount_temp NUMBER;
1880 l_detail_amount NUMBER;
1881 l_lpn_amount NUMBER;
1882 l_stop_amount NUMBER;
1883 l_delivery_amount NUMBER;
1884 l_index NUMBER;
1885
1886 BEGIN
1887 --
1888 -- Debug Statements
1889 --
1890 --
1891 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1892 --
1893 IF l_debug_on IS NULL
1894 THEN
1895 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1896 END IF;
1897 --
1898 IF l_debug_on THEN
1899 WSH_DEBUG_SV.push(l_module_name);
1900 END IF;
1901 --
1902 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1903
1904 x_trip_amount := NULL;
1905 x_stop_amount := NULL;
1906 x_delivery_amount := NULL;
1907 x_lpn_amount := NULL;
1908 x_detail_amount := NULL;
1909
1910 l_index:=p_in_ids.FIRST;
1911 WHILE l_index is not null LOOP
1912 OPEN freight_cost_at_trip(p_in_ids(l_index));
1913 LOOP
1914 FETCH freight_cost_at_trip INTO l_freight_cost_id,
1915 l_freight_cost,
1916 l_currency_code,
1917 l_conversion_type_code,
1918 l_conversion_date,
1919 l_conversion_rate;
1920
1921 EXIT WHEN freight_cost_at_trip%NOTFOUND ;
1922 Convert_Amount (
1923 p_from_currency => l_currency_code,
1924 p_to_currency => p_currency_code,
1925 p_conversion_date => l_conversion_date,
1926 p_conversion_rate => l_conversion_rate,
1927 p_conversion_type => l_conversion_type_code,
1928 p_amount => l_freight_cost,
1929 x_converted_amount => l_converted_amount,
1930 x_return_status => l_return_status) ;
1931
1932 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1933 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1934 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1935 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
1936 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1937 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1938 END IF;
1939 ELSE
1940 raise convert_amount_error;
1941 END IF;
1942 END LOOP;
1943 CLOSE freight_cost_at_trip;
1944
1945 --get stops and deliveries and call them separately
1946 FOR cur IN c_get_stops(p_in_ids(l_index)) LOOP
1947 l_stop_ids(l_stop_ids.COUNT+1):=cur.stop_id;
1948 END LOOP;
1949
1950 FOR cur IN c_get_dels(p_in_ids(l_index)) LOOP
1951 l_del_ids(l_del_ids.COUNT+1):=cur.delivery_id;
1952 END LOOP;
1953
1954 l_index:=p_in_ids.next(l_index);
1955 END LOOP;
1956
1957 IF l_stop_ids is not null and l_stop_ids.COUNT>0 THEN
1958 get_stop_freight_cost(
1959 p_in_ids => l_stop_ids,
1960 p_currency_code => p_currency_code,
1961 x_stop_amount => l_stop_amount ,
1962 x_return_status => l_return_status);
1963 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1964 raise details_freight_error;
1965 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1966 x_return_status:=l_return_status;
1967 END IF;
1968 END IF;
1969
1970 IF l_del_ids is not null and l_del_ids.COUNT>0 THEN
1971 get_delivery_freight_cost(
1972 p_in_ids => l_del_ids,
1973 p_currency_code => p_currency_code,
1974 x_detail_amount => l_detail_amount ,
1975 x_lpn_amount => l_lpn_amount,
1976 x_delivery_amount=> l_delivery_amount,
1977 x_return_status => l_return_status);
1978 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1979 raise details_freight_error;
1980 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1981 x_return_status:=l_return_status;
1982 END IF;
1983 END IF;
1984
1985 x_delivery_amount := l_delivery_amount;
1986 x_trip_amount:=l_total_amount;
1987 x_stop_amount:=l_stop_amount;
1988 x_detail_amount := l_detail_amount;
1989 x_lpn_amount := l_lpn_amount;
1990
1991 IF l_debug_on THEN
1992 WSH_DEBUG_SV.log(l_module_name,'amounts trip, stop, del, lpn, detail: '||l_total_amount||', '||l_stop_amount||', '||l_delivery_amount||', '||l_lpn_amount||', '||l_detail_amount);
1993 END IF;
1994
1995 --
1996 -- Debug Statements
1997 --
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.pop(l_module_name);
2000 END IF;
2001 --
2002 EXCEPTION
2003 WHEN details_freight_error THEN
2004 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2005 x_trip_amount := NULL;
2006 x_delivery_amount := NULL;
2007 x_stop_amount := NULL;
2008 x_lpn_amount := NULL;
2009 x_detail_amount := NULL;
2010 IF l_debug_on THEN
2011 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Trip_Freight_Cost exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2012 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Trip_Freight_Cost FILURE');
2013 END IF;
2014 WHEN convert_amount_error THEN
2015 CLOSE freight_cost_at_trip;
2016 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2017 x_trip_amount := NULL;
2018 x_stop_amount := NULL;
2019 x_delivery_amount := NULL;
2020 x_lpn_amount := NULL;
2021 x_detail_amount := NULL;
2022 IF l_debug_on THEN
2023 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Trip_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2024 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Trip_Freight_Cost FILURE');
2025 END IF;
2026
2027 WHEN OTHERS THEN
2028 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2029 x_trip_amount := NULL;
2030 x_stop_amount := NULL;
2031 x_delivery_amount := NULL;
2032 x_lpn_amount := NULL;
2033 x_detail_amount := NULL;
2034 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_Trip_Freight_Cost');
2035 IF l_debug_on THEN
2036 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2037 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2038 END IF;
2039
2040 END Get_Trip_Freight_Cost;
2041
2042
2043 --TL Rating spec changes
2044 --changed to get the costs at the entity level plus the costs in any level below
2045 --for example trip would return all the stops, deliveries, lpn's and detail cost
2046 --lpn would return the lpn and the detail costs etc.
2047
2048 PROCEDURE Get_Total_Freight_Cost(
2049 p_entity_level IN VARCHAR2,
2050 p_entity_id IN NUMBER,
2051 p_currency_code IN VARCHAR2,
2052 x_detail_amount OUT NOCOPY NUMBER ,
2053 x_lpn_amount OUT NOCOPY NUMBER ,
2054 x_main_lpn_amount OUT NOCOPY NUMBER, --to be used only for LPN
2055 x_delivery_amount OUT NOCOPY NUMBER ,
2056 x_stop_amount OUT NOCOPY NUMBER ,
2057 x_trip_amount OUT NOCOPY NUMBER ,
2058 x_return_status OUT NOCOPY VARCHAR2) IS
2059
2060 l_debug_on BOOLEAN;
2061 --
2062 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Total_Freight_Cost';
2063
2064 l_trip_amount NUMBER;
2065 l_stop_amount NUMBER;
2066 l_delivery_amount NUMBER;
2067 l_main_lpn_amount NUMBER;
2068 l_lpn_amount NUMBER;
2069 l_detail_amount NUMBER;
2070 l_in_ids wsh_util_core.id_tab_type;
2071
2072 l_return_status VARCHAR2(10) := NULL;
2073 l_msg_data VARCHAR2(2000);
2074 total_freight_error EXCEPTION;
2075 l_c_delivery CONSTANT VARCHAR2(30):= 'DELIVERY';
2076 l_c_stop CONSTANT VARCHAR2(30):= 'STOP';
2077 l_c_trip CONSTANT VARCHAR2(30):= 'TRIP';
2078 l_c_container CONSTANT VARCHAR2(30):= 'CONTAINER';
2079 l_c_line CONSTANT VARCHAR2(30):= 'LINE';
2080
2081 BEGIN
2082 --
2083 -- Debug Statements
2084 --
2085 --
2086 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2087 --
2088 IF l_debug_on IS NULL
2089 THEN
2090 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2091 END IF;
2092 --
2093 IF l_debug_on THEN
2094 WSH_DEBUG_SV.push(l_module_name);
2095 WSH_DEBUG_SV.log(l_module_name,'Entity, entity_id : '||p_entity_level||p_entity_id);
2096 END IF;
2097 --
2098 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2099 l_in_ids(1):=p_entity_id;
2100
2101 IF p_entity_level = l_c_delivery THEN
2102 Get_Delivery_Freight_Cost(
2103 p_in_ids => l_in_ids,
2104 p_currency_code => p_currency_code,
2105 x_delivery_amount => l_delivery_amount,
2106 x_lpn_amount => l_lpn_amount,
2107 x_detail_amount => l_detail_amount,
2111 p_in_ids => l_in_ids,
2108 x_return_status => l_return_status);
2109 ELSIF p_entity_level = l_c_stop THEN
2110 Get_Stop_Freight_Cost(
2112 p_currency_code => p_currency_code,
2113 x_stop_amount => l_stop_amount,
2114 x_return_status => l_return_status);
2115 ELSIF p_entity_level = l_c_trip THEN
2116 Get_Trip_Freight_Cost(
2117 p_in_ids => l_in_ids,
2118 p_currency_code => p_currency_code,
2119 x_trip_amount => l_trip_amount,
2120 x_stop_amount => l_stop_amount,
2121 x_delivery_amount => l_delivery_amount,
2122 x_lpn_amount => l_lpn_amount,
2123 x_detail_amount => l_detail_amount,
2124 x_return_status => l_return_status);
2125 ELSIF p_entity_level = l_c_line THEN
2126 Get_Detail_Freight_Cost(
2127 p_in_ids => l_in_ids,
2128 p_currency_code => p_currency_code,
2129 x_detail_amount => l_detail_amount,
2130 x_return_status => l_return_status);
2131 ELSIF p_entity_level = l_c_container THEN
2132 Get_LPN_Freight_Cost(
2133 p_in_ids => l_in_ids,
2134 p_currency_code => p_currency_code,
2135 x_main_lpn_amount => l_main_lpn_amount,
2136 x_lpn_amount => l_lpn_amount,
2137 x_detail_amount => l_detail_amount,
2138 x_return_status => l_return_status);
2139 END IF;
2140
2141 IF l_debug_on THEN
2142 WSH_DEBUG_SV.log(l_module_name,'amounts trip, stop, del, main lpn, lpn, detail: '||l_trip_amount||', '||l_stop_amount||', '||l_delivery_amount||', '||l_main_lpn_amount||', '||l_lpn_amount||', '||l_detail_amount);
2143 END IF;
2144
2145 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2146 raise total_freight_error;
2147 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2148 x_return_status:=l_return_status;
2149 END IF;
2150
2151 x_trip_amount := l_trip_amount;
2152 x_stop_amount := l_stop_amount;
2153 x_delivery_amount := l_delivery_amount;
2154 x_main_lpn_amount := l_main_lpn_amount;
2155 x_lpn_amount := l_lpn_amount;
2156 x_detail_amount := l_detail_amount;
2157
2158 --
2159 -- Debug Statements
2160 --
2161 IF l_debug_on THEN
2162 WSH_DEBUG_SV.pop(l_module_name);
2163 END IF;
2164 --
2165 EXCEPTION
2166
2167 WHEN total_freight_error THEN
2168 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2169 x_trip_amount := NULL;
2170 x_stop_amount := NULL;
2171 x_delivery_amount := NULL;
2172 x_lpn_amount := NULL;
2173 x_detail_amount := NULL;
2174 IF l_debug_on THEN
2175 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Total_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2176 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Total_Freight_Cost FILURE');
2177 END IF;
2178
2179 WHEN OTHERS THEN
2180 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2181 x_trip_amount := NULL;
2182 x_stop_amount := NULL;
2183 x_delivery_amount := NULL;
2184 x_lpn_amount := NULL;
2185 x_detail_amount := NULL;
2186 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_Total_Freight_Cost');
2187
2188 IF l_debug_on THEN
2189 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2190 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2192 END IF;
2193
2194
2195 END Get_Total_Freight_Cost;
2196 --TL Rating
2197
2198 PROCEDURE Get_Summary_Freight_Cost(
2199 p_entity_level IN VARCHAR2,
2200 p_entity_id IN NUMBER,
2201 p_currency_code IN VARCHAR2,
2202 x_total_amount OUT NOCOPY NUMBER,
2203 x_reprice_required OUT NOCOPY VARCHAR2,
2204 x_return_status OUT NOCOPY VARCHAR2) IS
2205 l_debug_on BOOLEAN;
2206 --
2207 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Summary_Freight_Cost';
2208
2209
2210
2211 Cursor freight_cost_in_delivery(c_delivery_id number) IS
2212 SELECT a.unit_amount,
2213 a.currency_code,
2214 NVL(conversion_type_code, 'Corporate'),
2215 NVL(conversion_date, SYSDATE),
2216 conversion_rate
2217 FROM wsh_freight_costs a
2218 WHERE a.delivery_detail_id in (
2219 SELECT c.delivery_detail_id
2220 FROM wsh_delivery_assignments_v b,
2221 wsh_delivery_details c
2222 WHERE b.delivery_id = c_delivery_id AND
2223 c.delivery_detail_id = b.delivery_detail_id AND
2224 c.released_status <> 'D') AND
2225 NVL(a.charge_source_code, 'MANUAL' ) in('MANUAL', 'PRICING_ENGINE') AND
2226 NVL(a.line_type_code, 'CHARGE') in ('CHARGE', 'PRICE') AND
2227 a.freight_cost_type_id <> -1 AND
2228 a.unit_amount is NOT NULL;
2229
2230
2231
2232 Cursor need_reprice(c_delivery_id NUMBER) IS
2233 SELECT 1
2234 FROM wsh_delivery_legs wshlg, wsh_delivery_assignments_v wshda,
2235 wsh_new_deliveries wshnd
2236 WHERE wshlg.delivery_id = c_delivery_id AND
2237 wshlg.reprice_required = 'Y' AND
2238 wshnd.delivery_id = wshlg.delivery_id and
2239 wshda.delivery_id = wshnd.delivery_id and
2240 wshda.delivery_detail_id is not null and
2241 rownum = 1;
2245 l_currency_code VARCHAR2(15) := NULL;
2242
2243
2244 l_freight_cost NUMBER := 0;
2246 l_conversion_type_code VARCHAR2(15) := NULL;
2247 l_conversion_date DATE;
2248 l_conversion_rate NUMBER;
2249 l_total_amount NUMBER := 0;
2250 l_return_status VARCHAR2(10) := NULL;
2251 l_converted_amount NUMBER := 0;
2252 l_msg_data VARCHAR2(2000);
2253 convert_amount_error EXCEPTION;
2254
2255 l_need_reprice NUMBER;
2256
2257 BEGIN
2258 --
2259 -- Debug Statements
2260 --
2261 --
2262 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2263 --
2264 IF l_debug_on IS NULL
2265 THEN
2266 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2267 END IF;
2268 --
2269 IF l_debug_on THEN
2270 WSH_DEBUG_SV.push(l_module_name);
2271 END IF;
2272 --
2273 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2274 x_total_amount := 0;
2275 x_reprice_required := 'N';
2276
2277 IF p_entity_level = 'Delivery' THEN
2278
2279 OPEN need_reprice(p_entity_id);
2280 FETCH need_reprice into l_need_reprice;
2281 IF need_reprice%NOTFOUND THEN
2282 x_reprice_required := 'N';
2283 ELSE
2284 x_reprice_required := 'Y';
2285 END IF;
2286 CLOSE need_reprice;
2287
2288 OPEN freight_cost_in_delivery(p_entity_id);
2289 LOOP
2290 FETCH freight_cost_in_delivery INTO l_freight_cost,
2291 l_currency_code,
2292 l_conversion_type_code,
2293 l_conversion_date,
2294 l_conversion_rate;
2295
2296 EXIT WHEN freight_cost_in_delivery%NOTFOUND ;
2297 Convert_Amount (
2298 p_from_currency => l_currency_code,
2299 p_to_currency => p_currency_code,
2300 p_conversion_date => l_conversion_date,
2301 p_conversion_rate => l_conversion_rate,
2302 p_conversion_type => l_conversion_type_code,
2303 p_amount => l_freight_cost,
2304 x_converted_amount => l_converted_amount,
2305 x_return_status => l_return_status) ;
2306
2307 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2308 l_total_amount := l_total_amount + NVL(l_converted_amount, 0);
2309 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2310 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
2311 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2312 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2313 END IF;
2314 ELSE
2315 raise convert_amount_error;
2316 END IF;
2317 END LOOP;
2318
2319 CLOSE freight_cost_in_delivery;
2320
2321 x_total_amount := l_total_amount;
2322
2323 END IF;
2324 --
2325 -- Debug Statements
2326 --
2327 IF l_debug_on THEN
2328 WSH_DEBUG_SV.pop(l_module_name);
2329 END IF;
2330 --
2331 EXCEPTION
2332
2333 WHEN convert_amount_error THEN
2334 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2335
2336 IF l_debug_on THEN
2337 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Summary_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2338 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Summary_Freight_Cost FILURE');
2339 END IF;
2340
2341 WHEN OTHERS THEN
2342
2343 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2344 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_Summary_Freight_Cost');
2345
2346 IF l_debug_on THEN
2347 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2348 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2349 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2350 END IF;
2351
2352
2353 END Get_Summary_Freight_Cost;
2354
2355
2356
2357 PROCEDURE Convert_Amount (
2358 p_from_currency IN VARCHAR2,
2359 p_to_currency IN VARCHAR2,
2360 p_conversion_date IN DATE,
2361 p_conversion_rate IN NUMBER,
2362 p_conversion_type IN VARCHAR2,
2363 p_amount IN NUMBER ,
2364 x_converted_amount OUT NOCOPY NUMBER,
2365 x_return_status OUT NOCOPY VARCHAR2)
2366
2367 IS
2368
2369 l_max_roll_days NUMBER := 300;
2370 l_denominator NUMBER := 0;
2371 l_numerator NUMBER := 0;
2372 l_rate NUMBER := 0;
2373 l_rate_exists VARCHAR2(10) := NULL;
2374
2375 l_debug_on BOOLEAN;
2376
2377 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Convert_Amount';
2378 WSH_CONVERT_ERROR EXCEPTION ;
2379 WSH_INVALID_CURRENCY EXCEPTION;
2380 WSH_NO_RATE EXCEPTION;
2381 BEGIN
2382
2383 --
2384 IF l_debug_on THEN
2385 WSH_DEBUG_SV.push(l_module_name);
2386 END IF;
2387 --
2388 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2389
2390 IF p_amount = 0 THEN
2394 WSH_DEBUG_SV.logmsg(l_module_name,'From currency is same as to currency ',WSH_DEBUG_SV.C_PROC_LEVEL);
2391 x_converted_amount := 0;
2392 ELSIF p_from_currency = p_to_currency THEN
2393 IF l_debug_on THEN
2395 END IF;
2396 x_converted_amount := p_amount;
2397
2398 ELSE
2399
2400 --
2401 IF (GL_CURRENCY_API.Is_Fixed_Rate(p_from_currency, p_to_currency, p_conversion_date) = 'Y') THEN
2402 --
2403 -- Debug Statements
2404 --
2405 IF l_debug_on THEN
2406 WSH_DEBUG_SV.logmsg(l_module_name, 'Convert' ||p_from_currency||' to '||p_to_currency ||' using fixed rate' );
2407 END IF;
2408
2409 BEGIN
2410 x_converted_amount := GL_CURRENCY_API.convert_amount(p_from_currency, p_to_currency, p_conversion_date, p_conversion_type, p_amount);
2411 EXCEPTION
2412 WHEN GL_CURRENCY_API.no_rate THEN
2413 IF l_debug_on THEN
2414 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( ) no rate ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2415 END IF;
2416 RAISE WSH_NO_RATE;
2417
2418 WHEN GL_CURRENCY_API.invalid_currency THEN
2419 IF l_debug_on THEN
2420 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( ) invalid currency',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2421 END IF;
2422 RAISE WSH_INVALID_CURRENCY;
2423
2424 WHEN others THEN
2425 IF l_debug_on THEN
2426 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( )',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2427 END IF;
2428 RAISE WSH_CONVERT_ERROR;
2429 END;
2430
2431 ELSIF (p_conversion_type = 'User') THEN
2432 IF (p_conversion_rate IS NOT NULL) THEN
2433 --
2434 -- Debug Statements
2435 --
2436 IF l_debug_on THEN
2437 WSH_DEBUG_SV.logmsg(l_module_name, 'Convert amount using user specified rate: ' || to_char(p_conversion_rate) , WSH_DEBUG_SV.C_EXCEP_LEVEL);
2438 END IF;
2439 --
2440 IF p_conversion_rate IS NULL THEN
2441 IF l_debug_on THEN
2442 WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Rate is NULL',WSH_DEBUG_SV.C_EXCEP_LEVEL );
2443 END IF;
2444 RAISE WSH_NO_RATE;
2445
2446 END IF;
2447 x_converted_amount := p_amount * p_conversion_rate;
2448 ELSE
2449 --
2450 -- Debug Statements
2451 --
2452 IF l_debug_on THEN
2453 WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion_type is user but no conversion_rate specified, convert_amount failed' );
2454 END IF;
2455
2456 RAISE WSH_CONVERT_ERROR;
2457 END IF;
2458 ELSE
2459
2460 --
2461 -- Debug Statements
2462 --
2463 IF l_debug_on THEN
2464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.RATE_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
2465 END IF;
2466 --
2467 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
2468 x_from_currency => p_from_currency,
2469 x_to_currency => p_to_currency,
2470 x_conversion_date => p_conversion_date,
2471 x_conversion_type => p_conversion_type
2472 );
2473 IF (l_rate_exists = 'Y') THEN
2474 --
2475 -- Debug Statements
2476 --
2477 IF l_debug_on THEN
2478 WSH_DEBUG_SV.logmsg(l_module_name, 'Convert amount using floating rate decided by conversion date' || to_char( p_conversion_date) );
2479 END IF;
2480 BEGIN
2481 x_converted_amount := GL_CURRENCY_API.convert_amount(p_from_currency, p_to_currency, p_conversion_date, p_conversion_type, p_amount);
2482 EXCEPTION
2483 WHEN GL_CURRENCY_API.no_rate THEN
2484 IF l_debug_on THEN
2485 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( ) no rate ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2486 END IF;
2487 RAISE WSH_NO_RATE;
2488
2489 WHEN GL_CURRENCY_API.invalid_currency THEN
2490 IF l_debug_on THEN
2491 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( ) invalid currency',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2492 END IF;
2493 RAISE WSH_INVALID_CURRENCY;
2494
2495 WHEN others THEN
2496 IF l_debug_on THEN
2497 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Amount( )',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2498 END IF;
2499 RAISE WSH_CONVERT_ERROR;
2500 END;
2501 ELSE
2502 --
2503 -- Debug Statements
2504 --
2505 IF l_debug_on THEN
2506 WSH_DEBUG_SV.logmsg(l_module_name, 'No rate exists , convert to closest amount , conversion date:'|| to_char ( p_conversion_date ) );
2507 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2508 END IF;
2509 --
2510 BEGIN
2511 GL_CURRENCY_API.convert_closest_amount(
2512 x_from_currency => p_from_currency,
2513 x_to_currency => p_to_currency,
2514 x_conversion_date => p_conversion_date,
2515 x_conversion_type => p_conversion_type,
2519 x_converted_amount=> x_converted_amount,
2516 x_user_rate => p_conversion_rate,
2517 x_amount => p_amount,
2518 x_max_roll_days => l_max_roll_days,
2520 x_denominator => l_denominator,
2521 x_numerator => l_numerator,
2522 x_rate => l_rate);
2523
2524 IF l_debug_on THEN
2525 WSH_DEBUG_SV.logmsg(l_module_name,'Converted amount from GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT is: '|| to_char(x_converted_amount),WSH_DEBUG_SV.C_PROC_LEVEL);
2526 END IF;
2527 EXCEPTION
2528 WHEN GL_CURRENCY_API.no_rate THEN
2529 IF l_debug_on THEN
2530 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Closest_Amount( ) no rate ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2531 END IF;
2532 RAISE WSH_NO_RATE;
2533
2534 WHEN GL_CURRENCY_API.invalid_currency THEN
2535 IF l_debug_on THEN
2536 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Closest_Amount( ) invalid currency',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2537 END IF;
2538 RAISE WSH_INVALID_CURRENCY;
2539
2540 WHEN others THEN
2541 IF l_debug_on THEN
2542 WSH_DEBUG_SV.logmsg(l_module_name,'Failed in GL_CURRENCY_API.Convert_Closest_Amount( )',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2543 END IF;
2544 RAISE WSH_CONVERT_ERROR;
2545 END;
2546 END IF;
2547 END IF;
2548
2549 END IF;
2550
2551 --
2552 -- Debug Statements
2553 --
2554 IF l_debug_on THEN
2555 WSH_DEBUG_SV.pop(l_module_name);
2556 END IF;
2557 --
2558
2559
2560 EXCEPTION
2561
2562 WHEN WSH_NO_RATE THEN
2563 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2564 x_converted_amount := NULL;
2565 IF l_debug_on THEN
2566 WSH_DEBUG_SV.logmsg(l_module_name,'Convert_Amount exception has occured. No convertion rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2567 WSH_DEBUG_SV.pop(l_module_name);
2568 END IF;
2569 FND_MESSAGE.Set_Name('WSH', 'WSH_FC_NO_RATE');
2570 FND_MESSAGE.Set_Token('FROM_CURRENCY', p_from_currency);
2571 FND_MESSAGE.Set_Token('TO_CURRENCY', p_to_currency);
2572 WSH_UTIL_CORE.Add_Message(x_return_status);
2573
2574 WHEN WSH_INVALID_CURRENCY THEN
2575 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2576 x_converted_amount := NULL;
2577 IF l_debug_on THEN
2578 WSH_DEBUG_SV.logmsg(l_module_name,'Convert_Amount exception has occured. Invalid currency.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2579 WSH_DEBUG_SV.pop(l_module_name);
2580 END IF;
2581 FND_MESSAGE.Set_Name('WSH', 'WSH_FC_INVALID_CURRENCY');
2582 WSH_UTIL_CORE.Add_Message(x_return_status);
2583
2584 WHEN WSH_CONVERT_ERROR THEN
2585 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2586 x_converted_amount := NULL;
2587 IF l_debug_on THEN
2588 WSH_DEBUG_SV.logmsg(l_module_name,'Convert_Amount exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2589 WSH_DEBUG_SV.pop(l_module_name);
2590 END IF;
2591 FND_MESSAGE.Set_Name('WSH', 'WSH_FC_NO_RATE');
2592 FND_MESSAGE.Set_Token('FROM_CURRENCY', p_from_currency);
2593 FND_MESSAGE.Set_Token('TO_CURRENCY', p_to_currency);
2594 WSH_UTIL_CORE.Add_Message(x_return_status);
2595
2596 WHEN others THEN
2597 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2598 x_converted_amount := NULL;
2599 IF l_debug_on THEN
2600 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2601 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2602 END IF;
2603 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Convert_Amount');
2604
2605 END Convert_Amount;
2606
2607
2608
2609 -- --------------------------------
2610 -- PROCEDURE Remove_FTE_Freight_Costs
2611 -- --------------------------------
2612 PROCEDURE Remove_FTE_Freight_Costs(
2613 p_delivery_details_tab IN WSH_UTIL_CORE.Id_Tab_Type,
2614 x_return_status OUT NOCOPY VARCHAR2 ) IS
2615
2616 CURSOR lock_freight_costs(c_delivery_detail_id NUMBER) IS
2617 SELECT freight_cost_id
2618 FROM wsh_freight_costs
2619 WHERE delivery_detail_id = c_delivery_detail_id AND
2620 charge_source_code = 'PRICING_ENGINE'
2621 FOR UPDATE NOWAIT;
2622
2623 CURSOR get_freight_costs (c_delivery_detail_id NUMBER) IS
2624 SELECT 1
2625 FROM wsh_freight_costs
2626 WHERE delivery_detail_id = c_delivery_detail_id AND
2627 charge_source_code = 'PRICING_ENGINE' AND
2628 rownum = 1;
2629
2630
2631 l_freight_cost_id NUMBER;
2632 l_freight_costs_exist NUMBER;
2633 l_debug_on BOOLEAN;
2634 --
2635 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REMOVE_FC_FREIGHT_COSTS';
2636 --
2637 BEGIN
2638 SAVEPOINT befor_delete;
2639 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2640 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2641 --
2642 IF l_debug_on IS NULL
2643 THEN
2644 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2645 END IF;
2646 --
2647 IF l_debug_on THEN
2648 WSH_DEBUG_SV.push(l_module_name);
2649 END IF;
2650
2651 FOR i in p_delivery_details_tab.FIRST .. p_delivery_details_tab.LAST
2652 LOOP
2653 IF l_debug_on THEN
2657 OPEN get_freight_costs(p_delivery_details_tab(i));
2654 WSH_DEBUG_SV.log(l_module_name,'Removing FTE Freight Costs for Delivery Detail: '|| p_delivery_details_tab(i));
2655 END IF;
2656
2658 FETCH get_freight_costs into l_freight_costs_exist ;
2659 IF get_freight_costs%NOTFOUND THEN
2660 NULL;
2661 ELSE
2662 OPEN lock_freight_costs(p_delivery_details_tab(i));
2663 LOOP
2664 FETCH lock_freight_costs INTO l_freight_cost_id;
2665 EXIT WHEN lock_freight_costs%NOTFOUND;
2666 DELETE wsh_freight_costs WHERE freight_cost_id = l_freight_cost_id;
2667 IF l_debug_on THEN
2668 WSH_DEBUG_SV.log(l_module_name,'Removed FTE Freight Costs ID: '|| l_freight_cost_id);
2669 END IF;
2670 END LOOP;
2671 CLOSE lock_freight_costs;
2672 END IF;
2673 CLOSE get_freight_costs;
2674 END LOOP;
2675 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2676 IF l_debug_on THEN
2677 WSH_DEBUG_SV.pop(l_module_name);
2678 END IF;
2679 EXCEPTION
2680
2681 WHEN OTHERS THEN
2682
2683 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2684 wsh_util_core.default_handler('WSH_FREIGHT_COSTS_PVT.Remove_FTE_Freight_Costs',l_module_name);
2685 ROLLBACK TO before_delete;
2686 --
2687 IF l_debug_on THEN
2688 WSH_DEBUG_SV.logmsg(l_module_name,'Cannot lock the fright cost records '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2689 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2690 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2691 END IF;
2692
2693
2694 END Remove_FTE_Freight_Costs;
2695
2696 /*************************************************************/
2697 PROCEDURE Get_Trip_Manual_Freight_Cost(
2698 p_trip_id IN NUMBER,
2699 p_currency_code IN VARCHAR2,
2700 x_trip_amount OUT NOCOPY NUMBER,
2701 x_return_status OUT NOCOPY VARCHAR2) IS
2702
2703 l_debug_on BOOLEAN;
2704 --
2705 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Trip_Manual_Freight_Cost';
2706
2707 Cursor freight_cost_at_trip(c_trip_id number) IS
2708 SELECT freight_cost_id,
2709 unit_amount,
2710 currency_code,
2711 NVL(conversion_type_code, 'Corporate'),
2712 NVL(conversion_date, SYSDATE),
2713 conversion_rate
2714 FROM wsh_freight_costs
2715 WHERE trip_id = c_trip_id AND
2716 NVL(charge_source_code, 'MANUAL') = 'MANUAL' AND
2717 NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
2718 freight_cost_type_id <> -1 AND
2719 unit_amount > 0;
2720
2721
2722 l_freight_cost NUMBER := 0;
2723 l_currency_code VARCHAR2(15) := NULL;
2724 l_conversion_type_code VARCHAR2(15) := NULL;
2725 l_conversion_date DATE;
2726 l_conversion_rate NUMBER;
2727 l_total_amount NUMBER := 0;
2728 l_return_status VARCHAR2(10) := NULL;
2729 l_converted_amount NUMBER := 0;
2730 l_msg_data VARCHAR2(2000);
2731 l_freight_cost_id NUMBER;
2732 convert_amount_error EXCEPTION;
2733
2734
2735 BEGIN
2736 --
2737 -- Debug Statements
2738 --
2739 --
2740 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2741 --
2742 IF l_debug_on IS NULL
2743 THEN
2744 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2745 END IF;
2746 --
2747 IF l_debug_on THEN
2748 WSH_DEBUG_SV.push(l_module_name);
2749 END IF;
2750 --
2751 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2752
2753 x_trip_amount := NULL;
2754
2755 OPEN freight_cost_at_trip(p_trip_id);
2756 LOOP
2757 --{
2758 FETCH freight_cost_at_trip INTO l_freight_cost_id,
2759 l_freight_cost,
2760 l_currency_code,
2761 l_conversion_type_code,
2762 l_conversion_date,
2763 l_conversion_rate;
2764
2765 EXIT WHEN freight_cost_at_trip%NOTFOUND ;
2766 Convert_Amount (
2767 p_from_currency => l_currency_code,
2768 p_to_currency => p_currency_code,
2769 p_conversion_date => l_conversion_date,
2770 p_conversion_rate => l_conversion_rate,
2771 p_conversion_type => l_conversion_type_code,
2772 p_amount => l_freight_cost,
2773 x_converted_amount => l_converted_amount,
2774 x_return_status => l_return_status) ;
2775
2776 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2777 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
2778 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2779 l_total_amount := l_total_amount + NVL(l_converted_amount,0);
2780 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2781 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2782 END IF;
2783 ELSE
2784 raise convert_amount_error;
2785 END IF;
2786 --}
2787 END LOOP;
2788 CLOSE freight_cost_at_trip;
2789
2790 x_trip_amount:=l_total_amount;
2791
2792 IF l_debug_on THEN
2793 WSH_DEBUG_SV.log(l_module_name,'Manual freight cost at trip : '||l_total_amount);
2794 END IF;
2795 --
2796 -- Debug Statements
2797 --
2798 IF l_debug_on THEN
2799 WSH_DEBUG_SV.pop(l_module_name);
2800 END IF;
2801 --
2802 EXCEPTION
2803 WHEN convert_amount_error THEN
2804 CLOSE freight_cost_at_trip;
2805 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2806 x_trip_amount := NULL;
2807 IF l_debug_on THEN
2808 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Trip_Manual_Freight_Cost exception has occured. No user rate specified.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2809 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Get_Trip_Freight_Cost FAILURE');
2810 END IF;
2811
2812 WHEN OTHERS THEN
2813 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2814 x_trip_amount := NULL;
2815 WSH_UTIL_CORE.Default_Handler('WSH_FREIGHT_COSTS_PVT.Get_Trip_Freight_Cost');
2816 IF l_debug_on THEN
2817 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2818 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2819 END IF;
2820
2821 END Get_Trip_Manual_Freight_Cost;
2822
2823 /************************************************************************/
2824
2825 END WSH_FREIGHT_COSTS_PVT;