eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_FREIGHT_RATING_PVT

Source


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