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