1 PACKAGE BODY OE_FREIGHT_RATING_util AS
2 /* $Header: OEXUFRRB.pls 120.1.12010000.2 2008/08/04 15:02:49 amallik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_FREIGHT_RATING_UTIL';
5
6 FUNCTION IS_FREIGHT_RATING_AVAILABLE RETURN BOOLEAN IS
7 l_code_release varchar2(30) := NULL;
8 l_enable_freight_rating varchar2(1) := NULL;
9
10 --
11 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
12 --
13 BEGIN
14
15 IF l_debug_level > 0 THEN
16 oe_debug_pub.add( 'OEXUFRRB: IN IS_FREIGHT_RATING_AVAILABLE' ) ;
17 END IF;
18
19 l_code_release := OE_CODE_CONTROL.Get_Code_Release_Level;
20
21 IF l_code_release < '110509' THEN
22 IF l_debug_level > 0 THEN
23 oe_debug_pub.add( 'LESS THAN PACK I' , 3 ) ;
24 END IF;
25 Return False;
26 END IF;
27
28 -- Check whether FTE is Installed. If not Exit
29
30 IF G_FTE_INSTALLED IS NULL THEN
31 G_FTE_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(716);
32 END IF;
33
34 IF G_FTE_INSTALLED = 'N' THEN
35 --FND_MESSAGE.Set_Name('ONT','ONT_FTE_NOT_INSTALLED');
36 --OE_MSG_PUB.Add;
37 IF l_debug_level > 0 THEN
38 oe_debug_pub.add( 'FTE IS NOT INSTALLED!' , 3 ) ;
39 END IF;
40 RETURN False;
41 END IF;
42
43 l_enable_freight_rating := nvl(OE_Sys_Parameters.Value('FTE_INTEGRATION'), 'N');
44
45 IF l_enable_freight_rating in ('N', 'S') THEN
46 IF l_debug_level > 0 THEN
47 oe_debug_pub.add( 'ENABLE_FREIGHT_RATING IS NO ' , 3 ) ;
48 END IF;
49 Return False;
50 END IF;
51
52
53 Return True;
54
55 EXCEPTION
56
57 WHEN OTHERS THEN
58
59 Return False;
60
61 END;
62
63 FUNCTION Get_Cost_Amount
64 ( p_cost_type_code IN VARCHAR2
65 )RETURN VARCHAR2 IS
66 l_api_version_number CONSTANT NUMBER := 1.0;
67 l_api_name CONSTANT VARCHAR2(30):= 'Get_Cost_Amount';
68 l_line_rec OE_Order_PUB.Line_Rec_Type;
69 l_cost_amount NUMBER := 0.0;
70 --
71 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
72 --
73 BEGIN
74
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( 'OEXUFRRB:INSIDE GET COST AMOUNT FOR' || P_COST_TYPE_CODE , 1 ) ;
77 END IF;
78
79 -- Get the Line record from the Global Record
80 l_line_rec := OE_ORDER_PUB.G_LINE;
81
82 -- Validate the Line_id.
83
84 IF l_line_rec.line_id is NULL OR l_line_rec.line_id = FND_API.G_MISS_NUM
85 THEN
86 IF l_debug_level > 0 THEN
87 oe_debug_pub.add( 'L_LINE_REC.LINE_ID IS NULL OR L_LINE_REC.LINE_ID IS FND_API.G_MISS_NUM' ) ;
88 oe_debug_pub.add( 'EXITING CHARGES' ) ;
89 END IF;
90 RETURN NULL;
91 END IF;
92
93 -- Check for values of cost_type_code
94
95 IF p_cost_type_code is NULL OR p_cost_type_code = FND_API.G_MISS_CHAR THEN
96 IF l_debug_level > 0 THEN
97 oe_debug_pub.add( 'P_COST_TYPE_CODE IS NULL OR P_COST_TYPE_CODE IS FND_API.G_MISS_CHAR' ) ;
98 oe_debug_pub.add( 'EXITING CHARGES' ) ;
99 END IF;
100 RETURN NULL;
101 END IF;
102
103 -- Check for Pricing Quantity
104
105 /*
106 IF l_line_rec.pricing_quantity IS NULL OR
107 l_line_rec.pricing_quantity = FND_API.G_MISS_NUM OR
108 l_line_rec.pricing_quantity <= 0 THEN
109 oe_debug_pub.add('l_line_rec.pricing_quantity = FND_API.G_MISS_NUM OR _line_rec.pricing_quantity <= 0');
110 oe_debug_pub.add('exiting charges');
111 RETURN NULL;
112 END IF;
113 */
114
115 -- Check whether the line is shippable and has got shipped
116
117 IF l_line_rec.shippable_flag = 'Y' THEN
118 IF l_line_rec.shipped_quantity > 0 THEN
119
120 -- Cost records are stored in OE_PRICE_ADJUSTMENTS table with
121 -- list_line_type_code = 'COST'
122 IF l_debug_level > 0 THEN
123 oe_debug_pub.add( 'LINE IS SHIPPABLE AND IS SHIPPED' , 3 ) ;
124 END IF;
125 SELECT NVL(SUM(ADJUSTED_AMOUNT),0)
126 INTO l_cost_amount
127 FROM OE_PRICE_ADJUSTMENTS
128 WHERE LINE_ID = l_line_rec.line_id
129 AND LIST_LINE_TYPE_CODE = 'COST'
130 AND CHARGE_TYPE_CODE = p_cost_type_code
131 AND nvl(ESTIMATED_FLAG, 'N') <> 'Y';
132
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add( 'AFTER GETTING COST AMOUNT ' || TO_CHAR ( L_COST_AMOUNT ) , 3 ) ;
135 END IF;
136
137 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(l_cost_amount);
138 ELSE
139 SELECT NVL(SUM(ADJUSTED_AMOUNT),0)
140 INTO l_cost_amount
141 FROM OE_PRICE_ADJUSTMENTS
142 WHERE LINE_ID = l_line_rec.line_id
143 AND LIST_LINE_TYPE_CODE = 'COST'
144 AND CHARGE_TYPE_CODE = p_cost_type_code
145 AND ESTIMATED_FLAG = 'Y';
146
147 IF l_debug_level > 0 THEN
148 oe_debug_pub.add( 'AFTER GETTING ESTIMATED COST AMOUNT ' || TO_CHAR ( L_COST_AMOUNT ) , 3 ) ;
149 END IF;
150
151 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(l_cost_amount);
152 END IF;
153 ELSE
154 IF l_debug_level > 0 THEN
155 oe_debug_pub.add( 'LINE NOT SHIPPABLE OR IS NOT SHIPPED' , 3 ) ;
156 END IF;
157 RETURN NULL;
158
159 END IF;
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add( 'LEAVING CHARGES' ) ;
162 END IF;
163 EXCEPTION
164
165 WHEN NO_DATA_FOUND THEN
166 IF l_debug_level > 0 THEN
167 oe_debug_pub.add( 'NO COST INFORMATION FOUND IN OE_PRICE_ADJUSTMENTS' ) ;
168 END IF;
169 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(0);
170
171 WHEN OTHERS THEN
172
173 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
174 THEN
175 FND_MSG_PUB.Add_Exc_Msg
176 ( G_PKG_NAME
177 , 'Get_Cost_Amount'
178 );
179 END IF;
180 IF l_debug_level > 0 THEN
181 oe_debug_pub.add( 'UNEXCPETED ERRORS:'||SQLERRM ) ;
182 END IF;
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END Get_Cost_Amount;
185
186 FUNCTION Get_List_Line_Type_Code
187 ( p_key IN NUMBER)
188 RETURN VARCHAR2
189 IS
190
191 l_list_line_type_code VARCHAR2(30);
192 l_list_line_type_code_rec List_Line_Type_Code_Rec_Type;
193
194 --
195 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
196 --
197 BEGIN
198
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add( 'ENTERING OE_FREIGHT_RATING_UTIL.GET_LIST_LINE_TYPE_CODE' , 1 ) ;
201 END IF;
202
203 IF p_key IS NOT NULL THEN
204
205 IF g_list_line_type_code_tbl.Exists(p_key) THEN
206
207 l_list_line_type_code
208 := g_list_line_type_code_tbl(p_key).list_line_type_code;
209
210 IF l_debug_level > 0 THEN
211 oe_debug_pub.add( 'LIST LINE TYPE CODE FOR HEADER: ' || P_KEY ||' IS: ' || L_LIST_LINE_TYPE_CODE , 3 ) ;
212 END IF;
213
214 ELSE
215
216 BEGIN
217 SELECT list_line_type_code
218 INTO l_list_line_type_code
219 FROM oe_price_adjustments
220 WHERE header_id = p_key
221 AND list_header_id = p_key * (-1)
222 AND list_line_type_code = 'OM_CALLED_FREIGHT_RATES'
223 AND rownum = 1;
224
225 EXCEPTION WHEN NO_DATA_FOUND THEN
226 l_list_line_type_code := NULL;
227 END;
228
229 IF l_list_line_type_code IS NOT NULL THEN
230 l_list_line_type_code_rec.list_line_type_code
231 := l_list_line_type_code;
232 g_list_line_type_code_tbl(p_key)
233 := l_list_line_type_code_rec;
234
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add( 'LOADING LIST LINE TYPE CODE FOR HEADER: ' || P_KEY ||' IS: ' || L_LIST_LINE_TYPE_CODE , 3 ) ;
237 END IF;
238 END IF;
239
240 END IF;
241
242 END IF;
243
244 IF l_debug_level > 0 THEN
245 oe_debug_pub.add( 'EXITING OE_FREIGHT_RATING_UTIL.GET_LIST_LINE_TYPE_CODE' , 1 ) ;
246 END IF;
247
248 RETURN l_list_line_type_code;
249
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 NULL;
253
254 WHEN OTHERS THEN
255
256 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
257 THEN
258 OE_MSG_PUB.Add_Exc_Msg
259 ( G_PKG_NAME ,
260 'Get_List_Line_Type_Code'
261 );
262 END IF;
263
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END Get_List_Line_Type_Code;
266
267 FUNCTION Get_Estimated_Cost_Amount
268 ( p_cost_type_code IN VARCHAR2
269 )RETURN VARCHAR2 IS
270 l_api_version_number CONSTANT NUMBER := 1.0;
271 l_api_name CONSTANT VARCHAR2(30):= 'Get_Cost_Amount';
272 l_line_rec OE_Order_PUB.Line_Rec_Type;
273 l_cost_amount NUMBER := 0.0;
274 --
275 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
276 --
277 BEGIN
278
279 IF l_debug_level > 0 THEN
280 oe_debug_pub.add( 'OEXUFRRB:INSIDE get_estimated_cost_amount FOR' || P_COST_TYPE_CODE , 1 ) ;
281 END IF;
282
283 -- Get the Line record from the Global Record
284 l_line_rec := OE_ORDER_PUB.G_LINE;
285
286 -- Validate the Line_id.
287
288 IF l_line_rec.line_id is NULL OR l_line_rec.line_id = FND_API.G_MISS_NUM
289 THEN
290 IF l_debug_level > 0 THEN
291 oe_debug_pub.add( 'L_LINE_REC.LINE_ID IS NULL OR L_LINE_REC.LINE_ID IS FND_API.G_MISS_NUM' ) ;
292 oe_debug_pub.add( 'EXITING CHARGES' ) ;
293 END IF;
294 RETURN NULL;
295 END IF;
296
297 -- Check for values of cost_type_code
298
299 IF p_cost_type_code is NULL OR p_cost_type_code = FND_API.G_MISS_CHAR THEN
300 IF l_debug_level > 0 THEN
301 oe_debug_pub.add( 'P_COST_TYPE_CODE IS NULL OR P_COST_TYPE_CODE IS FND_API.G_MISS_CHAR' ) ;
302 oe_debug_pub.add( 'EXITING CHARGES' ) ;
303 END IF;
304 RETURN NULL;
305 END IF;
306
307 -- Check for Pricing Quantity
308
309 /*
310 IF l_line_rec.pricing_quantity IS NULL OR
311 l_line_rec.pricing_quantity = FND_API.G_MISS_NUM OR
312 l_line_rec.pricing_quantity <= 0 THEN
313 oe_debug_pub.add('l_line_rec.pricing_quantity = FND_API.G_MISS_NUM OR _line_rec.pricing_quantity <= 0');
314 oe_debug_pub.add('exiting charges');
315 RETURN NULL;
316 END IF;
317 */
318
319 -- Check whether the line is shippable and has got shipped
320
321 IF l_line_rec.shippable_flag = 'Y' THEN
322 SELECT NVL(SUM(ADJUSTED_AMOUNT),0)
323 INTO l_cost_amount
324 FROM OE_PRICE_ADJUSTMENTS
325 WHERE LINE_ID = l_line_rec.line_id
326 AND LIST_LINE_TYPE_CODE = 'COST'
327 AND CHARGE_TYPE_CODE = p_cost_type_code
328 AND ESTIMATED_FLAG = 'Y';
329
330 IF l_debug_level > 0 THEN
331 oe_debug_pub.add( 'AFTER GETTING ESTIMATED COST AMOUNT ' || TO_CHAR ( L_COST_AMOUNT ) , 3 ) ;
332 END IF;
333
334 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(l_cost_amount);
335 ELSE
336 IF l_debug_level > 0 THEN
337 oe_debug_pub.add( 'LINE NOT SHIPPABLE ' , 3 ) ;
338 END IF;
339 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(0);
340
341 END IF;
342 IF l_debug_level > 0 THEN
343 oe_debug_pub.add( 'LEAVING CHARGES' ) ;
344 END IF;
345 EXCEPTION
346
347 WHEN NO_DATA_FOUND THEN
348 IF l_debug_level > 0 THEN
349 oe_debug_pub.add( 'NO COST INFORMATION FOUND IN OE_PRICE_ADJUSTMENTS' ) ;
350 END IF;
351 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(0);
352
353 WHEN OTHERS THEN
354
355 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
356 THEN
357 FND_MSG_PUB.Add_Exc_Msg
358 ( G_PKG_NAME
359 , 'Get_Cost_Amount'
360 );
361 END IF;
362 IF l_debug_level > 0 THEN
363 oe_debug_pub.add( 'UNEXCPETED ERRORS:'||SQLERRM ) ;
364 END IF;
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END Get_Estimated_Cost_Amount;
367
368 PROCEDURE Create_Dummy_Adjustment(p_header_id in number) IS
369 l_price_adjustment_id number := -1;
370 --
371 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
372 --
373 BEGIN
374
375 IF p_header_id is not null THEN
376
377 select oe_price_adjustments_s.nextval
378 into l_price_adjustment_id
379 from dual;
380
381 INSERT INTO oe_price_adjustments
382 (PRICE_ADJUSTMENT_ID
383 ,HEADER_ID
384 ,LINE_ID
385 ,PRICING_PHASE_ID
386 ,LIST_LINE_TYPE_CODE
387 ,LIST_HEADER_ID
388 ,LIST_LINE_ID
389 ,ADJUSTED_AMOUNT
390 ,AUTOMATIC_FLAG
391 ,UPDATED_FLAG
392 ,APPLIED_FLAG
393 ,CREATION_DATE
394 ,CREATED_BY
395 ,LAST_UPDATE_DATE
396 ,LAST_UPDATED_BY
397 )
398 VALUES
399 (l_price_adjustment_id
400 ,p_header_id
401 ,NULL
402 ,-1
403 ,'OM_CALLED_FREIGHT_RATES'
404 ,-1*p_header_id
405 ,NULL
406 ,-1
407 ,'N'
408 ,'Y'
409 ,NULL
410 ,sysdate
411 ,1
412 ,sysdate
413 ,1
414 );
415
416 END IF;
417
418
419 END Create_Dummy_Adjustment;
420
421 -- Added as part of bug 6955343
422 FUNCTION Get_Estimated_Cost_Amount_Ns
423 ( p_cost_type_code IN VARCHAR2
424 )RETURN VARCHAR2 IS
425 l_api_version_number CONSTANT NUMBER := 1.0;
426 l_api_name CONSTANT VARCHAR2(30):= 'Get_Estimated_Cost_Amount_Ns';
427 l_line_rec OE_Order_PUB.Line_Rec_Type;
428 l_cost_amount NUMBER := 0.0;
429 --
430 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
431 --
432 BEGIN
433
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add('OEXUFRRB: Inside Procedure Get_Estimated_Cost_Amount_Ns');
436 oe_debug_pub.add('p_cost_type_code : ' || P_COST_TYPE_CODE , 1 ) ;
437 END IF;
438 -- Get the Line record from the Global Record
439 l_line_rec := OE_ORDER_PUB.G_LINE;
440
441 -- Validate the Line_id.
442
443 IF l_line_rec.line_id is NULL OR l_line_rec.line_id = FND_API.G_MISS_NUM
444 THEN
445 IF l_debug_level > 0 THEN
446 oe_debug_pub.add( 'L_LINE_REC.LINE_ID IS NULL OR L_LINE_REC.LINE_ID IS FND_API.G_MISS_NUM' ) ;
447 oe_debug_pub.add( 'EXITING CHARGES' ) ;
448 END IF;
449 RETURN NULL;
450 END IF;
451
452 -- Check for values of cost_type_code
453
454 IF p_cost_type_code is NULL OR p_cost_type_code = FND_API.G_MISS_CHAR THEN
455 IF l_debug_level > 0 THEN
456 oe_debug_pub.add( 'P_COST_TYPE_CODE IS NULL OR P_COST_TYPE_CODE IS FND_API.G_MISS_CHAR' ) ;
457 oe_debug_pub.add( 'EXITING CHARGES' ) ;
458 END IF;
459 RETURN NULL;
460 END IF;
461
462 SELECT NVL(SUM(ADJUSTED_AMOUNT),0)
463 INTO l_cost_amount
464 FROM OE_PRICE_ADJUSTMENTS
465 WHERE LINE_ID = l_line_rec.line_id
466 AND LIST_LINE_TYPE_CODE = 'COST'
467 AND CHARGE_TYPE_CODE = p_cost_type_code
468 AND ESTIMATED_FLAG = 'Y';
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'AFTER GETTING ESTIMATED COST AMOUNT ' || TO_CHAR ( L_COST_AMOUNT ) , 3 ) ;
472 END IF;
473 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(l_cost_amount);
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add( 'LEAVING CHARGES' ) ;
476 END IF;
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 IF l_debug_level > 0 THEN
480 oe_debug_pub.add( 'NO COST INFORMATION FOUND IN OE_PRICE_ADJUSTMENTS' ) ;
481 END IF;
482 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(0);
483 WHEN OTHERS THEN
484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
485 THEN
486 FND_MSG_PUB.Add_Exc_Msg
487 ( G_PKG_NAME
488 , 'Get_Cost_Amount'
489 );
490 END IF;
491 IF l_debug_level > 0 THEN
492 oe_debug_pub.add( 'UNEXCPETED ERRORS:'||SQLERRM ) ;
493 END IF;
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END Get_Estimated_Cost_Amount_ns;
496
497 END OE_FREIGHT_RATING_util;
[Home] [Help]