[Home] [Help]
PACKAGE BODY: APPS.OE_FREIGHT_RATING_PVT
Source
1 PACKAGE BODY OE_FREIGHT_RATING_PVT AS
2 /* $Header: OEXVFRRB.pls 120.0.12010000.2 2009/06/26 12:28:41 nitagarw ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_FREIGHT_RATING_PVT';
5 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; -- Added for bug 8636027
6
7 PROCEDURE Print_Time(p_msg IN VARCHAR2);
8
9 PROCEDURE Prepare_Adj_Detail
10 (p_header_id IN NUMBER
11 ,p_line_id IN NUMBER
12 ,p_adj_index IN NUMBER
13 ,p_fte_rates_rec IN FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Rec
14 ,x_line_adj_rec OUT NOCOPY OE_Order_PUB.Line_Adj_Rec_Type
15
16 ,x_return_status OUT NOCOPY VARCHAR2
17
18 );
19
20
21 FUNCTION Get_List_Line_Type_Code
22 ( p_key IN NUMBER)
23 RETURN VARCHAR2
24 IS
25
26 l_list_line_type_code VARCHAR2(30);
27 l_list_line_type_code_rec List_Line_Type_Code_Rec_Type;
28
29 --
30 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
31 --
32 BEGIN
33
34 IF l_debug_level > 0 THEN
35 oe_debug_pub.add( 'ENTERING OE_FREIGHT_RATING_PVT.GET_LIST_LINE_TYPE_CODE' , 1 ) ;
36 END IF;
37
38 IF p_key IS NOT NULL THEN
39
40 -- list_line_type_code is already cached.
41 IF g_list_line_type_code_tbl.Exists(MOD(p_key,G_BINARY_LIMIT)) THEN
42
43 l_list_line_type_code
44 := g_list_line_type_code_tbl(MOD(p_key,G_BINARY_LIMIT)).list_line_type_code;
45
46 IF l_debug_level > 0 THEN
47 oe_debug_pub.add( 'LIST LINE TYPE CODE FOR HEADER: ' || P_KEY ||' IS: ' || L_LIST_LINE_TYPE_CODE , 3 ) ;
48 END IF;
49
50 ELSE
51
52 BEGIN
53 SELECT list_line_type_code
54 INTO l_list_line_type_code
55 FROM oe_price_adjustments
56 WHERE header_id = p_key
57 AND list_header_id = p_key * (-1);
58
59 EXCEPTION WHEN NO_DATA_FOUND THEN
60 l_list_line_type_code := NULL;
61 END;
62
63 IF l_list_line_type_code IS NOT NULL THEN
64 l_list_line_type_code_rec.list_line_type_code
65 := l_list_line_type_code;
66 g_list_line_type_code_tbl(MOD(p_key,G_BINARY_LIMIT))
67 := l_list_line_type_code_rec;
68
69 IF l_debug_level > 0 THEN
70 oe_debug_pub.add( 'LOADING LIST LINE TYPE CODE FOR HEADER: ' || P_KEY ||' IS: ' || L_LIST_LINE_TYPE_CODE , 3 ) ;
71 END IF;
72 END IF;
73
74
75
76 END IF;
77
78 END IF;
79
80 IF l_debug_level > 0 THEN
81 oe_debug_pub.add( 'EXITING OE_FREIGHT_RATING_PVT.GET_LIST_LINE_TYPE_CODE' , 1 ) ;
82 END IF;
83
84 RETURN l_list_line_type_code;
85
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 NULL;
89
90 WHEN OTHERS THEN
91
92 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
93 THEN
94 OE_MSG_PUB.Add_Exc_Msg
95 ( G_PKG_NAME ,
96 'Get_List_Line_Type_Code'
97 );
98 END IF;
99
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END Get_List_Line_Type_Code;
102
103
104 /*--------------------------------------------------------------+
105 | Name : Print_Time |
106 | Parameters : IN p_msg |
107 | |
108 | Description : This Procedure will print Current time along |
109 | with the Debug Message Passed as input. |
110 | This Procedure will be called from Main |
111 | Procedures to print Entering and Leaving Msg |
112 +--------------------------------------------------------------*/
113 PROCEDURE Print_Time(p_msg IN VARCHAR2)
114 IS
115 l_time VARCHAR2(100);
116 --
117 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
118 --
119 BEGIN
120 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
121 'DD-MON-YY HH24:MI:SS');
122 IF l_debug_level > 0 THEN
123 oe_debug_pub.add( P_MSG || ': '|| L_TIME , 1 ) ;
124 END IF;
125 END Print_Time;
126
127 PROCEDURE Prepare_Adj_Detail
128 ( p_header_id IN NUMBER
129 ,p_line_id IN NUMBER
130 ,p_adj_index IN NUMBER
131 ,p_fte_rates_rec IN FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec
132 ,x_line_adj_rec OUT NOCOPY OE_Order_PUB.Line_Adj_Rec_Type
133
134 ,x_return_status OUT NOCOPY VARCHAR2
135
136 ) IS
137
138 l_price_adjustment_id number := 0;
139
140 --
141 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
142 --
143 BEGIN
144
145 IF l_debug_level > 0 THEN
146 oe_debug_pub.add( 'ENTERING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
147 END IF;
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 select oe_price_adjustments_s.nextval into l_price_adjustment_id
151 from dual;
152
153 IF l_debug_level > 0 THEN
154 oe_debug_pub.add( 'PRICE ADJUSTMENT ID IN PREPARE_ADJ_DETAIL IS: ' ||L_PRICE_ADJUSTMENT_ID , 1 ) ;
155 END IF;
156 IF l_debug_level > 0 THEN
157 oe_debug_pub.add( 'LINE_ID IN PREPARE_ADJ_DETAIL IS: ' ||P_LINE_ID , 1 ) ;
158 END IF;
159
160 x_line_adj_rec.header_id := p_header_id;
161 x_line_adj_rec.line_id := p_line_id;
162 x_line_adj_rec.price_adjustment_id := l_price_adjustment_id;
163 x_line_adj_rec.creation_date := sysdate;
164 x_line_adj_rec.last_update_date := sysdate;
165 x_line_adj_rec.created_by := 1;
166 x_line_adj_rec.last_updated_by := 1;
167 x_line_adj_rec.last_update_login := 1;
168
169 x_line_adj_rec.automatic_flag := 'N';
170 x_line_adj_rec.adjusted_amount := p_fte_rates_rec.adjusted_price;
171 x_line_adj_rec.charge_type_code := p_fte_rates_rec.cost_type;
172 x_line_adj_rec.list_line_type_code := 'COST';
173 x_line_adj_rec.estimated_flag := 'Y';
174 x_line_adj_rec.source_system_code := 'FTE';
175
176 IF l_debug_level > 0 THEN
177 oe_debug_pub.add( 'EXITING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 IF l_debug_level > 0 THEN
184 oe_debug_pub.add( 'ERROR IN PROCEDURE PREPARE_ADJ_DETAIL: '||SUBSTR ( SQLERRM , 1 , 240 ) , 3 ) ;
185 END IF;
186 IF l_debug_level > 0 THEN
187 oe_debug_pub.add( 'UNEXPECTED ERROR IN PREPRARE_ADJ_DETAIL :'||SQLERRM , 3 ) ;
188 END IF;
189 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
190 THEN
191 OE_MSG_PUB.Add_Exc_Msg
192 ( G_PKG_NAME,
193 'Prepare_Adj_Detail');
194 END IF;
195
196
197
198 END Prepare_Adj_Detail;
199
200
201 PROCEDURE Process_FTE_Output
202 ( p_header_id IN NUMBER
203 ,p_x_fte_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
204 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
205 ,p_fte_rates_tab IN FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab
206 ,p_config_count IN NUMBER
207 ,p_ui_flag IN VARCHAR2
208 ,p_call_pricing_for_FR IN VARCHAR2
209 ,x_return_status OUT NOCOPY VARCHAR2 )
210
211 IS
212 l_fte_count NUMBER := 1;
213 l_index NUMBER := 0;
214 l_adj_index NUMBER := 1;
215 l_line_id NUMBER;
216 l_ato_line_id NUMBER;
217 l_price_adjustment_id NUMBER;
218 l_price_control_rec OE_ORDER_PRICE_PVT.control_rec_type;
219 l_request_rec oe_order_pub.request_rec_type;
220 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
221 l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
222 l_fte_rates_rec FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec;
223 l_bulk_adj_rec Bulk_Line_Adj_Rec_Type;
224 l_return_status VARCHAR2(1);
225 I pls_integer;
226 J pls_integer;
227 k pls_integer;
228 l_pricing_event VARCHAR2(30);
229 l_line_id_tbl Number_Type;
230
231 CURSOR C_CONFIG_ITEM_PARENTS(p_ato_line_id IN NUMBER) IS
232 SELECT opa.price_adjustment_id,ool.line_id,
233 opa.adjusted_amount, opa.list_line_type_code,
234 opa.charge_type_code
235 FROM oe_order_lines ool
236 ,oe_price_adjustments opa
237 WHERE opa.charge_type_code IN ('FTEPRICE','FTECHARGE')
238 AND ool.line_id = opa.line_id
239 AND ool.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
240 AND ool.ato_line_id = p_ato_line_id;
241
242 --
243 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
244 --
245 BEGIN
246
247 Print_Time('Entering OE_Freight_Rating_PVT.Process_FTE_Output..');
248
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251
252 -- delete the old records from previous FTE call.
253 DELETE FROM OE_PRICE_ADJUSTMENTS
254 WHERE HEADER_ID = P_HEADER_ID
255 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
256 AND list_line_type_code = 'COST'
257 AND ESTIMATED_FLAG = 'Y'
258 RETURNING line_id bulk collect into l_line_id_tbl;
259
260 k := l_line_id_tbl.FIRST;
261 WHILE k is not null LOOP
262
263 OE_LINE_ADJ_UTIL.Register_Changed_Lines
264 (p_line_id => l_line_id_tbl(k),
265 p_header_id => p_header_id,
266 p_operation => OE_GLOBALS.G_OPR_UPDATE);
267 k := l_line_id_tbl.NEXT(k);
268 END LOOP;
269
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add( 'TOTAL NUMBER OF CONFIG LINES:'||P_CONFIG_COUNT , 3 ) ;
272 END IF;
273
274 -- initialize these values for FTE rates.
275 l_index := 1;
276
277 -- insert the FTE output to database table.
278 I := p_fte_rates_tab.FIRST;
279 WHILE I IS NOT NULL LOOP
280 IF l_debug_level > 0 THEN
281 oe_debug_pub.add( '============ FTE RESULTS ============' , 3 ) ;
282 END IF;
283
284 IF l_debug_level > 0 THEN
285 oe_debug_pub.add( 'SOURCE LINE :'|| P_FTE_RATES_TAB ( I ) .SOURCE_LINE_ID , 3 ) ;
286 END IF;
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add( 'COST TYPE :'|| P_FTE_RATES_TAB ( I ) .COST_TYPE , 3 ) ;
289 END IF;
290 IF l_debug_level > 0 THEN
291 oe_debug_pub.add( 'COST SUB TYPE :'|| P_FTE_RATES_TAB ( I ) .COST_SUB_TYPE , 3 ) ;
292 END IF;
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( 'PRICED QUANTITY :'|| P_FTE_RATES_TAB ( I ) .PRICED_QUANTITY , 3 ) ;
295 END IF;
296 IF l_debug_level > 0 THEN
297 oe_debug_pub.add( 'PRICED UOM :'|| P_FTE_RATES_TAB ( I ) .PRICED_UOM , 3 ) ;
298 END IF;
299 IF l_debug_level > 0 THEN
300 oe_debug_pub.add( 'UNIT PRICE :'|| P_FTE_RATES_TAB ( I ) .UNIT_PRICE , 3 ) ;
301 END IF;
302 IF l_debug_level > 0 THEN
303 oe_debug_pub.add( 'BASE PRICE :'|| P_FTE_RATES_TAB ( I ) .BASE_PRICE , 3 ) ;
304 END IF;
305 IF l_debug_level > 0 THEN
306 oe_debug_pub.add( 'ADJUSTED UNIT PRICE :'|| P_FTE_RATES_TAB ( I ) .ADJUSTED_UNIT_PRICE , 3 ) ;
307 END IF;
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'ADJUSTED PRICE :'|| P_FTE_RATES_TAB ( I ) .ADJUSTED_PRICE , 3 ) ;
310 END IF;
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add( 'CURRENCY :'|| P_FTE_RATES_TAB ( I ) .CURRENCY , 3 ) ;
313 END IF;
314
315 Prepare_Adj_Detail
316 (p_header_id => p_header_id
317 ,p_line_id => p_fte_rates_tab(I).source_line_id
318 ,p_adj_index => l_adj_index
319 ,p_fte_rates_rec => p_fte_rates_tab(I)
320 ,x_line_adj_rec => l_line_adj_rec
321 ,x_return_status => x_return_status
322 );
323
324
325 l_line_adj_tbl(l_adj_index) := l_line_adj_rec;
326
327 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
328
329 -- to register changed line so that repricing for this line
330 -- would happen.
331 oe_debug_pub.add('Register changed line: '||p_fte_rates_tab(I).source_line_id,1);
332 OE_LINE_ADJ_UTIL.Register_Changed_Lines
333 (p_line_id => p_fte_rates_tab(I).source_line_id,
334 p_header_id => p_header_id,
335 p_operation => OE_GLOBALS.G_OPR_UPDATE);
336
337 l_adj_index := l_adj_index + 1;
338 I := p_fte_rates_tab.NEXT(I);
339
340 END LOOP;
341
342 -- cascade the estimated charges from parent lines to config item lines
343 -- and insert into database.
344 l_adj_index := 1;
345 WHILE l_index <= p_config_count LOOP
346
347 l_line_id := p_x_line_tbl(l_index).line_id;
348 IF l_debug_level > 0 THEN
349 oe_debug_pub.add( 'CASCADING ADJUSTMENT LINES TO CONFIG LINES. ' , 3 ) ;
350 END IF;
351
352 OPEN C_CONFIG_ITEM_PARENTS(p_x_line_tbl(l_index).ato_line_id);
353 FETCH C_CONFIG_ITEM_PARENTS BULK COLLECT INTO
354 l_bulk_adj_rec.price_adjustment_id
355 ,l_bulk_adj_rec.line_id
356 ,l_bulk_adj_rec.adjusted_amount
357 ,l_bulk_adj_rec.list_line_type_code
358 ,l_bulk_adj_rec.charge_type_code
359 ;
360
361 CLOSE C_CONFIG_ITEM_PARENTS;
362
363
364 FOR i in 1..l_bulk_adj_rec.price_adjustment_id.COUNT LOOP
365
366 l_fte_rates_rec.cost_type := l_bulk_adj_rec.charge_type_code(i);
367 l_fte_rates_rec.adjusted_price := l_bulk_adj_rec.adjusted_amount(i);
368
369
370 Prepare_Adj_Detail
371 (p_header_id => p_header_id
372 ,p_line_id => l_line_id
373 ,p_adj_index => l_adj_index
374 ,p_fte_rates_rec => l_fte_rates_rec
375 ,x_line_adj_rec => l_line_adj_rec
376 ,x_return_status => x_return_status
377 );
378
379 l_line_adj_tbl(l_adj_index) := l_line_adj_rec;
380
381 -- inserting for the config line
382 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
383
384 -- register changed line for config item line.
385 OE_LINE_ADJ_UTIL.Register_Changed_Lines
386 (p_line_id => l_line_id,
387 p_header_id => p_header_id,
388 p_operation => OE_GLOBALS.G_OPR_UPDATE);
389
390 -- deleting the parents of the config line.
391 -- these deleted parent lines have been registered in
392 -- previous loop looping through p_fte_rates_tab, so
393 -- no need to register changed line again for these lines.
394 DELETE FROM oe_price_adjustments
395 WHERE price_adjustment_id = l_bulk_adj_rec.price_adjustment_id(i);
396
397 l_adj_index := l_adj_index + 1;
398
399 END LOOP;
400
401 l_index := l_index + 1;
402
403 END LOOP;
404
405 -- for ATO lines, only send config lines to Pricing.
406 J := p_x_line_tbl.FIRST;
407 WHILE J IS NOT NULL LOOP
408 -- delete those non-shippable ATO parent lines
409 IF p_x_line_tbl(J).ato_line_id IS NOT NULL
410 AND p_x_line_tbl(J).item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
411 p_x_line_tbl.delete(J);
412 END IF;
413
414 J := p_x_line_tbl.NEXT(J);
415
416 END LOOP;
417
418 -- Calling Pricing Engine to calculate freight charges
419 -- if being called from Action button.
420 IF NVL(p_ui_flag, 'N') = 'Y' THEN
421 IF l_debug_level > 0 THEN
422 oe_debug_pub.add( 'CALLING PRICING ENGINE FOR FREIGHT FOR: '||P_HEADER_ID , 1 ) ;
423 END IF;
424
425 l_price_control_rec.p_request_type_code:='ONT';
426 l_Price_control_rec.p_write_to_db:=TRUE;
427 l_price_control_rec.p_honor_price_flag:='Y';
428 l_price_control_rec.p_multiple_events:='N';
429 l_price_control_rec.p_get_freight_flag:='Y';
430
431 oe_order_price_pvt.price_line
432 (p_Header_id => p_header_id
433 ,p_Line_id => null
434 ,px_line_Tbl => p_x_line_tbl
435 ,p_Control_Rec => l_price_control_rec
436 ,p_action_code => 'PRICE_ORDER'
437 ,p_Pricing_Events => 'BATCH'
438 ,x_Return_Status => l_return_status
439 );
440
441 END IF;
442
443
444 -- if this is called from Action button and it is the first time
445 -- FTE being called.
446 IF NVL(OE_FREIGHT_RATING_UTIL.get_list_line_type_code(p_header_id),'N')
447 <> 'OM_CALLED_FREIGHT_RATES' THEN
448
449 select oe_price_adjustments_s.nextval
450 into l_price_adjustment_id
451 from dual;
452 INSERT INTO oe_price_adjustments
453 (PRICE_ADJUSTMENT_ID
454 ,HEADER_ID
455 ,LINE_ID
456 ,PRICING_PHASE_ID
457 ,LIST_LINE_TYPE_CODE
458 ,LIST_HEADER_ID
459 ,LIST_LINE_ID
460 ,ADJUSTED_AMOUNT
461 ,AUTOMATIC_FLAG
462 ,UPDATED_FLAG
463 ,APPLIED_FLAG
464 ,CREATION_DATE
465 ,CREATED_BY
466 ,LAST_UPDATE_DATE
467 ,LAST_UPDATED_BY
468 )
469 VALUES
470 (l_price_adjustment_id
471 ,p_header_id
472 ,NULL
473 ,-1
474 ,'OM_CALLED_FREIGHT_RATES'
475 ,-1*p_header_id
476 ,NULL
477 ,-1
478 ,'N'
479 ,'Y'
480 ,NULL
481 ,sysdate
482 ,1
483 ,sysdate
484 ,1
485 );
486
487 END IF;
488
489
490
491 Print_Time('Entering OE_Freight_Rating_PVT.Process_FTE_Output..');
492
493 EXCEPTION
494
495 WHEN FND_API.G_EXC_ERROR THEN
496
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add( 'EXPECTED ERROR IN PROCESS FTE OUTPUT' , 1 ) ;
499 END IF;
500
501 x_return_status := FND_API.G_RET_STS_ERROR;
502
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504
505 IF l_debug_level > 0 THEN
506 oe_debug_pub.add( 'UNEXPECTED ERROR IN PROCESS FTE OUTPUT'|| SQLERRM , 2 ) ;
507 END IF;
508
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510
511 WHEN OTHERS THEN
512
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514
515 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
516 THEN
517 OE_MSG_PUB.Add_Exc_Msg
518 ( G_PKG_NAME,
519 'Process_FTE_Output');
520 END IF;
521 END Process_FTE_Output;
522
523 END OE_FREIGHT_RATING_PVT;