1 PACKAGE BODY OE_FTE_INTEGRATION_PVT AS
2 /* $Header: OEXVFTEB.pls 120.1 2010/07/23 07:40:43 spothula ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_FTE_INTEGRATION_PVT';
5
6
7 /*--------------------------------------------------------------+
8 | Local Procedures and Function Declarations |
9 +--------------------------------------------------------------*/
10
11 PROCEDURE Print_Time(p_msg IN VARCHAR2);
12
13 PROCEDURE Update_FTE_Results
14 ( p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
15 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
16
17 PROCEDURE Create_FTE_Input
18 ( p_header_id IN NUMBER
19 ,p_line_id IN NUMBER
20 ,p_x_fte_source_line_tab IN OUT NOCOPY
21 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
22 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
23 ,p_action IN VARCHAR2
24 ,x_config_count OUT NOCOPY /* file.sql.39 change */ NUMBER
25 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
26
27 PROCEDURE Process_FTE_Output
28 ( p_x_fte_source_line_tab IN OUT NOCOPY
29 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
30 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
31 ,p_config_count IN NUMBER
32 ,x_no_opr_count OUT NOCOPY /* file.sql.39 change */ NUMBER
33 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 );
34
35
36 /*--------------------------------------------------------------+
37 | Name : Print_Time |
38 | Parameters : IN p_msg |
39 | |
40 | Description : This Procedure will print Current time along |
41 | with the Debug Message Passed as input. |
42 | This Procedure will be called from Main |
43 | Procedures to print Entering and Leaving Msg |
44 +--------------------------------------------------------------*/
45
46 PROCEDURE Print_Time(p_msg IN VARCHAR2)
47 IS
48 l_time VARCHAR2(100);
49 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
50 BEGIN
51 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
52 'DD-MON-YY HH24:MI:SS');
53 IF l_debug_level > 0 THEN
54 OE_DEBUG_PUB.Add(p_msg || ': '|| l_time, 1);
55 END IF;
56 END Print_Time;
57
58
59 /*--------------------------------------------------------------+
60 | Name : Update_FTE_Results |
61 | Parameters : IN OUT NOCOPY p_x_line_tbl |
62 | OUT NOCOPY |
63 | x_return_status |
64 | |
65 | Description : This Procedure updates the FTE Results in db |
66 | This Calls Process Order to update the FTE |
67 | results. Then the Procedure Process delayed |
68 | requests and notify is called to execute all |
69 | pending delayed requests. This is called from|
70 | procedure Process FTE Requests. |
71 | Change Record : |
72 +--------------------------------------------------------------*/
73
74 PROCEDURE Update_FTE_Results
75 ( p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
76 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
77
78 IS
79 l_control_rec OE_GLOBALS.Control_Rec_Type;
80 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
81 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
82 BEGIN
83
84 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Update_FTE_Results..');
85
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 IF l_debug_level > 0 THEN
89 OE_DEBUG_PUB.Add('Before Calling Process Order..',3);
90 END IF;
91
92 OE_ORDER_PVT.Lines
93 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
94 ,p_control_rec => l_control_rec
95 ,p_x_line_tbl => p_x_line_tbl
96 ,p_x_old_line_tbl => l_old_line_tbl
97 ,x_return_status => x_return_status);
98
99
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.Add('After Calling Process Order...'||
102 x_return_status,3);
103 END IF;
104
105 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110
111 IF l_debug_level > 0 THEN
112 OE_DEBUG_PUB.Add('Before Calling Process Requests and Notify',3);
113 END IF;
114
115 OE_ORDER_PVT.Process_Requests_And_notify
116 ( p_process_requests => TRUE
117 ,p_notify => TRUE
118 ,x_return_status => x_return_status
119 ,p_line_tbl => p_x_line_tbl
120 ,p_old_line_tbl => l_old_line_tbl);
121
122 IF l_debug_level > 0 THEN
123 OE_DEBUG_PUB.Add('After Calling Process Requests' ||
124 'and Notify...'|| x_return_status,3);
125 END IF;
126
127 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132
133
134 Print_Time('Exiting OE_FTE_INTEGRATION_PVT.Update_FTE_Results..');
135
136 EXCEPTION
137
138 WHEN FND_API.G_EXC_ERROR THEN
139 IF l_debug_level > 0 THEN
140 OE_DEBUG_PUB.Add('Expected Error in Update FTE Results', 2);
141 END IF;
142 x_return_status := FND_API.G_RET_STS_ERROR;
143
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 IF l_debug_level > 0 THEN
146 OE_DEBUG_PUB.Add('Unexpected Error in Update FTE Results'||
147 sqlerrm, 1);
148 END IF;
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150
151 WHEN OTHERS THEN
152
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154
155 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 THEN
157 OE_MSG_PUB.Add_Exc_Msg
158 ( G_PKG_NAME,
159 'Update_FTE_Results');
160 END IF;
161
162 END Update_FTE_Results;
163
164
165 /*--------------------------------------------------------------+
166 Name : Create_FTE_Input
167 Parameters : IN OUT NOCOPY p_x_line_tbl
168 IN OUT NOCOPY p_x_fte_source_line_tab
169 IN p_header_id
170 IN p_line_id
171 IN p_action
172 OUT NOCOPY x_return_status
173 OUT NOCOPY x_config_count
174
175 Description : This Procedure prepares FTE input table to be
176 passed to FTE. This has two cursors. The
177 first Cursor is used to select all the attrib
178 to be passed to FTE. The second cursor is
179 is used to select all the included item
180 parents to process the included items.
181
182 we process the included items if they are not
183 frozen already. We will exclude all the non
184 eligible lines and prepate the fte input tab
185 at the same time we will also insert the
186 elgible lines in the oe_order_pub line table
187 to be used later.
188 Now we have line table with all the lines
189 beginnning with config lines.
190
191 The Same Procedure will be used by freight
192 rating also to prepare the Fte input table.
193 All non shippable lines not part of ATO
194 configuration should be marked as not
195 eligible for freight rate calculations.
196
197 This procedure is called from Process_FTE_Actions
198 API and p_action paramter can not have a NULL value.
199
200 Change Record :
201
202 +--------------------------------------------------------------*/
203
204 PROCEDURE Create_FTE_Input
205 ( p_header_id IN NUMBER
206 ,p_line_id IN NUMBER
207 ,p_x_fte_source_line_tab IN OUT NOCOPY
208 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
209 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
210 ,p_action IN VARCHAR2
211 ,x_config_count OUT NOCOPY /* file.sql.39 change */ NUMBER
212 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
213
214 IS
215 CURSOR C_SHIP_METHOD_LINES IS
216 SELECT line_id
217 ,ship_from_org_id
218 ,ship_to_org_id
219 ,sold_to_org_id
220 ,inventory_item_id
221 ,ordered_quantity
222 ,order_quantity_uom
223 ,request_date
224 ,schedule_ship_date
225 ,delivery_lead_time
226 ,DECODE(schedule_status_code,NULL,'N',
227 'SCHEDULED','Y','N') scheduled_flag
228 ,ship_set_id
229 ,arrival_set_id
230 ,ship_model_complete_flag
231 ,ato_line_id
232 ,top_model_line_id
233 ,shipping_method_code
234 ,freight_carrier_code
235 ,freight_terms_code
236 ,intmed_ship_to_org_id
237 ,fob_point_code
238 ,source_type_code
239 ,line_category_code
240 ,item_type_code
241 ,shipped_quantity
242 ,NVL(fulfilled_flag,'N') fulfilled_flag
243 ,open_flag
244 ,nvl(shippable_flag, 'N') shippable_flag
245 ,order_source_id
246 ,orig_sys_document_ref
247 ,orig_sys_line_ref
248 ,orig_sys_shipment_ref
249 ,change_sequence
250 ,source_document_type_id
251 ,source_document_id
252 ,source_document_line_id
253 FROM oe_order_lines
254 WHERE header_id = p_header_id
255 AND NOT (p_action = 'R' AND shipping_method_code is NULL)
256 ORDER BY top_model_line_id, ato_line_id, sort_order;
257
258
259 CURSOR C_INC_ITEMS_PARENT IS
260 SELECT line_id
261 FROM oe_order_lines
262 WHERE item_type_code IN ('MODEL', 'CLASS', 'KIT')
263 AND ato_line_id is NULL
264 AND explosion_date is NULL
265 AND NVL(fulfilled_flag,'N') <> 'Y'
266 AND open_flag = 'Y'
267 AND shipped_quantity IS NULL
268 AND line_category_code <> 'RETURN'
269 AND source_type_code = 'INTERNAL'
270 AND header_id = p_header_id;
271
272 l_open_flag VARCHAR2(1);
273 l_order_category_code VARCHAR2(30);
274 l_line_count NUMBER := 0;
275 l_config_count NUMBER := 0;
276 l_count NUMBER := 0;
277 l_line_rec OE_ORDER_PUB.line_rec_type :=
278 OE_Order_Pub.G_MISS_LINE_REC;
279 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
280 BEGIN
281 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Create_FTE_Input...');
282
283 IF l_debug_level > 0 THEN
284 OE_DEBUG_PUB.Add('Order header Id:'||p_header_id,1);
285 END IF;
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287
288
289 -- Validating Order
290
291 BEGIN
292 SELECT open_flag,order_category_code
293 INTO l_open_flag,l_order_category_code
294 FROM oe_order_headers_all
295 WHERE header_id = p_header_id;
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 IF l_debug_level > 0 THEN
299 OE_DEBUG_PUB.Add('No Data Found when Validating Order',3);
300 END IF;
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 WHEN OTHERS THEN
303 IF l_debug_level > 0 THEN
304 OE_DEBUG_PUB.Add('When Others when Validating Order',3);
305 END IF;
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 END;
308
309 IF l_open_flag = 'N' OR l_order_category_code = 'RETURN' THEN
310 IF l_debug_level > 0 THEN
311 OE_DEBUG_PUB.Add('Order is Return/Closed',1);
312 END IF;
313 RAISE FND_API.G_EXC_ERROR ;
314 END IF;
315
316
317 -- We need to create the included items if not already frozen.
318 -- Loop through the cursor Inc Item Parents to get the parent
319 -- lines of included items.
320
321 FOR c_inc_parent IN C_INC_ITEMS_PARENT
322 LOOP
323 IF l_debug_level > 0 THEN
324 OE_DEBUG_PUB.Add('Calling Process Included Items for Line:'||
325 c_inc_parent.line_id,3);
326 END IF;
327
328 x_return_status := OE_CONFIG_UTIL.Process_Included_Items
329 (p_line_id => c_inc_parent.line_id
330 ,p_freeze => FALSE);
331
332 IF l_debug_level > 0 THEN
333 OE_DEBUG_PUB.Add('After Calling Process Included Items: '||
334 x_return_status,3);
335 END IF;
336
337 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
339 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342
343 END LOOP;
344
345
346 -- Query the Config lines and Put them in the line table
347 -- We are putting config lines in the line table in the
348 -- beginning. When traversing through the line table for
349 -- config lines it would be easy to identify them as they will
350 -- be in the beginning. We will insert all other lines in the
351 -- table next to config lines.
352 -- Now number of Config lines in the table
353
354 SELECT count(*)
355 INTO l_config_count
356 FROM oe_order_lines
357 WHERE item_type_code = 'CONFIG'
358 AND ato_line_id IS NOT NULL
359 AND header_id = p_header_id;
360
361 IF l_debug_level > 0 THEN
362 OE_DEBUG_PUB.Add('Config Line Count:'||l_config_count,3);
363 END IF;
364
365 x_config_count := l_config_count;
366 l_line_count := l_config_count;
367
368
369 -- Reset the Config Count Variable
370 l_config_count := 0;
371
372 -- Loop through the cursor Ship Method Lines and exclude all
373 -- Non eligible Lines.
374
375 FOR c_ship_method IN C_SHIP_METHOD_LINES
376 LOOP
377 -- We need to set the Message Context for each line.
378
379 OE_Msg_Pub.Set_Msg_Context
380 ( p_entity_code => OE_GLOBALS.G_ENTITY_LINE
381 ,p_entity_id => c_ship_method.line_id
382 ,p_header_id => p_header_id
383 ,p_line_id => c_ship_method.line_id
384 ,p_order_source_id => c_ship_method.order_source_id
385 ,p_orig_sys_document_ref => c_ship_method.orig_sys_document_ref
386 ,p_orig_sys_document_line_ref => c_ship_method.orig_sys_line_ref
387 ,p_orig_sys_shipment_ref => c_ship_method.orig_sys_shipment_ref
388 ,p_change_sequence => c_ship_method.change_sequence
389 ,p_source_document_type_id => c_ship_method.source_document_type_id
390 ,p_source_document_id => c_ship_method.source_document_id
391 ,p_source_document_line_id => c_ship_method.source_document_line_id);
392
393 -- If the line is sourced Externally the line is
394 -- not eligible
395
396 IF c_ship_method.source_type_code = 'EXTERNAL' OR
397 c_ship_method.item_type_code = 'SERVICE' OR
398 c_ship_method.line_category_code = 'RETURN' THEN
399
400 FND_MESSAGE.Set_Name('ONT','ONT_FTE_EXTERNAL_RET_SERVICE');
401 OE_MSG_PUB.Add;
402
403 IF l_debug_level > 0 THEN
404 OE_DEBUG_PUB.Add('Line is External/Service/Return:'||
405 c_ship_method.line_id ,3);
406 END IF;
407
408 ELSIF c_ship_method.ship_from_org_id IS NULL THEN
409
410 FND_MESSAGE.Set_Name('ONT','ONT_FTE_MISSING_SHIP_FROM');
411 OE_MSG_PUB.Add;
412 IF l_debug_level > 0 THEN
413 OE_DEBUG_PUB.Add('Line is Missing Ship From Org Id:'||
414 c_ship_method.line_id ,3);
415 END IF;
416
417 ELSIF c_ship_method.ship_to_org_id IS NULL THEN
418
419 FND_MESSAGE.Set_Name('ONT','ONT_FTE_MISSING_SHIP_TO');
420 OE_MSG_PUB.Add;
421 IF l_debug_level > 0 THEN
422 OE_DEBUG_PUB.Add('Line is Missing Ship To Org Id:'||
423 c_ship_method.line_id ,3);
424 END IF;
425
426 ELSIF c_ship_method.shipped_quantity IS NOT NULL OR
427 c_ship_method.fulfilled_flag = 'Y' OR
428 c_ship_method.open_flag <> 'Y' THEN
429
430 FND_MESSAGE.Set_Name('ONT','ONT_FTE_SHIP_FULFILL_CLOSED');
431 OE_MSG_PUB.Add;
432 IF l_debug_level > 0 THEN
433 OE_DEBUG_PUB.Add('Line is Shipped/Fulfilled/Closed:'||
434 c_ship_method.line_id ,1);
435 END IF;
436 ELSIF c_ship_method.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
437
438 -- We are not going to send Config lines to FTE
439 -- We have to store the config lines in the line table.
440 IF l_debug_level > 0 THEN
441 OE_DEBUG_PUB.Add('Config Line:'||c_ship_method.line_id,3);
442 END IF;
443
444 l_line_rec.line_id := c_ship_method.line_id;
445 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
446 l_line_rec.item_type_code := c_ship_method.item_type_code;
447 l_line_rec.shipping_method_code :=
448 c_ship_method.shipping_method_code;
449 l_line_rec.freight_terms_code :=
450 c_ship_method.freight_terms_code;
451
452 l_config_count := l_config_count + 1;
453
454 p_x_line_tbl(l_config_count) := l_line_rec;
455 IF l_debug_level > 0 THEN
456 OE_DEBUG_PUB.Add('Excluding Config Line from FTE Input:'||
457 c_ship_method.line_id ,1);
458 END IF;
459 ELSE
460 IF l_debug_level > 0 THEN
461 OE_DEBUG_PUB.Add('Sending Eligibile Line to FTE:'||
462 c_ship_method.line_id ,1);
463 END IF;
464
465 -- Increment the FTE line table count
466
467 l_count := l_count + 1;
468
469 p_x_fte_source_line_tab(l_count).source_type := 'ONT';
470
471 p_x_fte_source_line_tab(l_count).source_header_id :=
472 p_header_id;
473
474 p_x_fte_source_line_tab(l_count).source_line_id :=
475 c_ship_method.line_id;
476
477 p_x_fte_source_line_tab(l_count).ship_from_org_id :=
478 c_ship_method.ship_from_org_id;
479
480 p_x_fte_source_line_tab(l_count).ship_to_site_id :=
481 c_ship_method.ship_to_org_id;
482
483
484 p_x_fte_source_line_tab(l_count).customer_id :=
485 c_ship_method.sold_to_org_id;
486
487 p_x_fte_source_line_tab(l_count).inventory_item_id :=
488 c_ship_method.inventory_item_id;
489
490 p_x_fte_source_line_tab(l_count).source_quantity :=
491 c_ship_method.ordered_quantity;
492
493 p_x_fte_source_line_tab(l_count).source_quantity_uom:=
494 c_ship_method.order_quantity_uom;
495
496 p_x_fte_source_line_tab(l_count).ship_date :=
497 NVL(c_ship_method.schedule_ship_date
498 , NVL(c_ship_method.request_date,sysdate));
499
500 IF c_ship_method.scheduled_flag = 'N' THEN
501 p_x_fte_source_line_tab(l_count).delivery_lead_time :=0;
502
503 ELSE
504 p_x_fte_source_line_tab(l_count).delivery_lead_time :=
505 c_ship_method.delivery_lead_time;
506
507 END IF;
508
509 p_x_fte_source_line_tab(l_count).scheduled_flag :=
510 c_ship_method.scheduled_flag;
511
512
513 IF c_ship_method.arrival_set_id IS NOT NULL THEN
514
515 p_x_fte_source_line_tab(l_count).order_set_type :=
516 'ARRIVAL';
517
518 p_x_fte_source_line_tab(l_count).order_set_id :=
519 c_ship_method.arrival_set_id;
520
521 ELSIF c_ship_method.ship_set_id IS NOT NULL THEN
522
523 p_x_fte_source_line_tab(l_count).order_set_type :=
524 'SHIP';
525
526 p_x_fte_source_line_tab(l_count).order_set_id :=
527 c_ship_method.ship_set_id;
528
529 ELSIF c_ship_method.top_model_line_id IS NOT NULL AND
530 c_ship_method.ship_model_complete_flag = 'Y' THEN
531
532 p_x_fte_source_line_tab(l_count).order_set_type := 'SMC';
533
534 p_x_fte_source_line_tab(l_count).order_set_id :=
535 c_ship_method.top_model_line_id;
536
537 ELSIF c_ship_method.ato_line_id IS NOT NULL THEN
538
539 p_x_fte_source_line_tab(l_count).order_set_type := 'ATO';
540
541 p_x_fte_source_line_tab(l_count).order_set_id :=
542 c_ship_method.ato_line_id;
543 ELSE
544 p_x_fte_source_line_tab(l_count).order_set_type := NULL;
545
546 p_x_fte_source_line_tab(l_count).order_set_id := NULL;
547
548 END IF;
549
550 p_x_fte_source_line_tab(l_count).carrier_id := NULL;
551
552 p_x_fte_source_line_tab(l_count).ship_method_code :=
553 c_ship_method.shipping_method_code;
554
555 p_x_fte_source_line_tab(l_count).freight_terms :=
556 c_ship_method.freight_terms_code;
557
558 p_x_fte_source_line_tab(l_count).fob_code :=
559 c_ship_method.fob_point_code;
560
561
562 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id :=
563 c_ship_method.intmed_ship_to_org_id;
564
565 p_x_fte_source_line_tab(l_count).ship_method_flag := 'Y';
566 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'Y';
567
568 IF c_ship_method.shippable_flag = 'N' THEN
569
570 IF c_ship_method.ato_line_id is not NULL AND
571 NOT ((c_ship_method.item_type_code = 'OPTION' OR
572 c_ship_method.item_type_code = 'STANDARD' OR
573 c_ship_method.item_type_code = 'INCLUDED') AND --9775352
574 c_ship_method.ato_line_id = c_ship_method.line_id)
575 THEN
576 IF l_debug_level > 0 THEN
577 OE_DEBUG_PUB.Add('calculate rating part of ato', 4);
578 END IF;
579 ELSE
580 IF l_debug_level > 0 THEN
581 OE_DEBUG_PUB.Add('do not calculate freight_rating '||
582 c_ship_method.line_id, 1);
583 END IF;
584 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'N';
585 END IF;
586
587 END IF;
588
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.Add('--------- Input to FTE --------',3);
591
592 oe_debug_pub.Add('Source Line :'||
593 p_x_fte_source_line_tab(l_count).source_line_id,3);
594
595 oe_debug_pub.Add('Ship From Org :'||
596 p_x_fte_source_line_tab(l_count).ship_from_org_id,3);
597
598 oe_debug_pub.Add('Customer :'||
599 p_x_fte_source_line_tab(l_count).customer_id,3);
600
601 oe_debug_pub.Add('Inventory Item :'||
602 p_x_fte_source_line_tab(l_count).inventory_item_id,3);
603
604 oe_debug_pub.Add('Source Quantity :'||
605 p_x_fte_source_line_tab(l_count).source_quantity,3);
606
607 oe_debug_pub.Add('Ship Date :'||
608 p_x_fte_source_line_tab(l_count).ship_date,3);
609
610 oe_debug_pub.Add('Delivery Lead Time :'||
611 p_x_fte_source_line_tab(l_count).delivery_lead_time,3);
612
613 oe_debug_pub.Add('Scheduled :'||
614 p_x_fte_source_line_tab(l_count).scheduled_flag,3);
615
616 oe_debug_pub.Add('Order Set Type :'||
617 p_x_fte_source_line_tab(l_count).order_set_type,3);
618
619 oe_debug_pub.Add('Order Set :'||
620 p_x_fte_source_line_tab(l_count).order_set_id,3);
621
622 oe_debug_pub.Add('Ship Method :'||
623 p_x_fte_source_line_tab(l_count).ship_method_code,3);
624
625 oe_debug_pub.Add('Freight Terms :'||
626 p_x_fte_source_line_tab(l_count).freight_terms,3);
627
628 oe_debug_pub.Add('Freight on Board :'||
629 p_x_fte_source_line_tab(l_count).fob_code,3);
630
631 oe_debug_pub.Add('Intermediate Ship :'||
632 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id,3);
633
634 oe_debug_pub.Add('-------------------------------',3);
635 END IF;
636
637 -- Store the old ship method code in the line table
638
639 l_line_rec.line_id := c_ship_method.line_id;
640 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
641 l_line_rec.item_type_code := c_ship_method.item_type_code;
642 l_line_rec.shipping_method_code :=
643 c_ship_method.shipping_method_code;
644 l_line_rec.freight_terms_code :=
645 c_ship_method.freight_terms_code;
646 l_line_rec.freight_carrier_code :=
647 c_ship_method.freight_carrier_code;
648
649 l_line_count := l_line_count + 1;
650 p_x_line_tbl(l_line_count) := l_line_rec;
651
652 END IF;
653
654 END LOOP;
655
656 -- this check is added to show the message when no lines of the order are eligible for freight rating
657
658 IF p_x_line_tbl.count = 0 THEN
659
660 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NO_LINES_ELIGIBLE');
661 OE_MSG_PUB.Add;
662 IF l_debug_level > 0 THEN
663 OE_DEBUG_PUB.Add('No lines of the order are eligible for freight rating');
664 END IF;
665 END IF;
666
667 IF l_debug_level > 0 THEN
668 oe_debug_pub.Add('FTE Input count:'||p_x_fte_source_line_tab.count,3);
669 oe_debug_pub.Add('Total count:'|| p_x_line_tbl.count,3);
670 END IF;
671
672 Print_Time('Exiting OE_FTE_INTEGRATION_PVT.Create_FTE_Input...');
673 EXCEPTION
674
675 WHEN FND_API.G_EXC_ERROR THEN
676 IF l_debug_level > 0 THEN
677 oe_debug_pub.Add('Expected Error in Create FTE Input',2);
678 END IF;
679
680 x_return_status := FND_API.G_RET_STS_ERROR;
681
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683 IF l_debug_level > 0 THEN
684 oe_debug_pub.Add('Unexpected Error in Create FTE Input:'||SqlErrm, 1);
685 END IF;
686
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688
689
690 WHEN OTHERS THEN
691
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693
694 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
695 THEN
696 OE_MSG_PUB.Add_Exc_Msg
697 ( G_PKG_NAME,
698 'Create_FTE_Input');
699 END IF;
700
701 END Create_FTE_Input;
702
703
704 /*--------------------------------------------------------------+
705 Name : Process_FTE_Output
706 Parameters : IN OUT NOCOPY p_x_line_tbl
707 IN OUT NOCOPY p_x_fte_source_line_tab
708 IN p_config_count
709 OUT NOCOPY
710 x_return_status
711 x_no_opr_count
712 Description : This Procedure processes the FTE output and
713 is called from procedure process_fte_actions.
714 In this Procedure FTE line table is traversed
715 if there is any change in the ship method
716 we will update the changes in the line and
717 and Call Process Order. Process Order should
718 not be called when there are no lines with
719 operation equal to UPDATE. Count number of
720 lines which have operation as none and do
721 not call update results if there is atleast
722 one line with operation update.
723
724 o/p parameters are cascaded on CONFIG line
725 from its ato parent line.
726 Change Record :
727
728 +--------------------------------------------------------------*/
729
730 PROCEDURE Process_FTE_Output
731 ( p_x_fte_source_line_tab IN OUT NOCOPY
732 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
733 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
734 ,p_config_count IN NUMBER
735 ,x_no_opr_count OUT NOCOPY /* file.sql.39 change */ NUMBER
736 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
737 IS
738 l_fte_count NUMBER := 1;
739 l_line_offset NUMBER := 0;
740 l_index NUMBER := 1;
741
742 l_order_source_id NUMBER;
743 l_orig_sys_document_ref VARCHAR2(50);
744 l_orig_sys_line_ref VARCHAR2(50);
745 l_orig_sys_shipment_ref VARCHAR2(50);
746 l_change_sequence VARCHAR2(50);
747 l_source_document_id NUMBER;
748 l_source_document_line_id NUMBER;
749 l_source_document_type_id NUMBER;
750 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
751 BEGIN
752
753 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Process_FTE_Output..');
754
755 x_no_opr_count := 0;
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757 l_line_offset := p_config_count + 1;
758
759 IF l_debug_level > 0 THEN
760 oe_debug_pub.Add('Total Number of Config lines:'||p_config_count,3);
761 oe_debug_pub.Add('Line Offset is :'||l_line_offset,3);
762 END IF;
763
764 WHILE l_fte_count IS NOT NULL
765 LOOP
766 IF l_debug_level > 0 THEN
767 oe_debug_pub.Add('--------- FTE Results ----------',3);
768
769 oe_debug_pub.Add('Source Line :'||
770 p_x_fte_source_line_tab(l_fte_count).source_line_id,3);
771
772 oe_debug_pub.Add('Ship Method :'||
773 p_x_fte_source_line_tab(l_fte_count).ship_method_code,3);
774
775 oe_debug_pub.Add('Frieght Carrier :'||
776 p_x_fte_source_line_tab(l_fte_count).freight_carrier_code,3);
777
778 oe_debug_pub.Add('Service Level :' ||
779 p_x_fte_source_line_tab(l_fte_count).service_level,3);
780
781 oe_debug_pub.Add('Mode of Transport :'||
782 p_x_fte_source_line_tab(l_fte_count).mode_of_transport,3);
783
784 oe_debug_pub.Add('Frieght Terms :'||
785 p_x_fte_source_line_tab(l_fte_count).freight_terms,3);
786
787 oe_debug_pub.Add('Weight :'||
788 p_x_fte_source_line_tab(l_fte_count).weight,3);
789
790 oe_debug_pub.Add('Weight UOM :'||
791 p_x_fte_source_line_tab(l_fte_count).weight_uom_code,3);
792
793 oe_debug_pub.Add('Volume :'||
794 p_x_fte_source_line_tab(l_fte_count).volume,3);
795
796 oe_debug_pub.Add('Volume UOM :'||
797 p_x_fte_source_line_tab(l_fte_count).volume_uom_code,3);
798
799 oe_debug_pub.Add('Frieght Rate :'||
800 p_x_fte_source_line_tab(l_fte_count).freight_rate,3);
801
802 oe_debug_pub.Add('Frieght Rate Currency :'||
803 p_x_fte_source_line_tab(l_fte_count).freight_rate_currency,3);
804
805 oe_debug_pub.Add('Status :'||
806 p_x_fte_source_line_tab(l_fte_count).status,3);
807
808 END IF;
809
810 IF p_x_fte_source_line_tab(l_fte_count).status =
811 FND_API.G_RET_STS_ERROR THEN
812
813 IF p_x_fte_source_line_tab(l_fte_count).source_line_id IS NOT NULL AND
814 p_x_fte_source_line_tab(l_fte_count).source_line_id <> FND_API.G_MISS_NUM THEN
815 BEGIN
816 IF l_debug_level > 0 THEN
817 oe_debug_pub.add('Getting reference data for line_id:'||
818 p_x_fte_source_line_tab(l_fte_count).source_line_id);
819 END IF;
820 SELECT order_source_id, orig_sys_document_ref, orig_sys_line_ref,
821 orig_sys_shipment_ref, change_sequence, source_document_id,
822 source_document_line_id, source_document_type_id
823 INTO l_order_source_id, l_orig_sys_document_ref, l_orig_sys_line_ref,
824 l_orig_sys_shipment_ref, l_change_sequence, l_source_document_id,
825 l_source_document_line_id, l_source_document_type_id
826 FROM oe_order_lines_all
827 WHERE line_id = p_x_fte_source_line_tab(l_fte_count).source_line_id;
828 EXCEPTION
829 WHEN NO_DATA_FOUND THEN
830 IF l_debug_level > 0 THEN
831 oe_debug_pub.add('No_data_found while getting reference data '||
832 'for line_id:'||p_x_fte_source_line_tab(l_fte_count).source_line_id);
833 END IF;
834 l_order_source_id := NULL;
835 l_orig_sys_document_ref := NULL;
836 l_orig_sys_line_ref := NULL;
837 l_orig_sys_shipment_ref := NULL;
838 l_change_sequence := NULL;
839 l_source_document_id := NULL;
840 l_source_document_line_id := NULL;
841 l_source_document_type_id := NULL;
842 END;
843 END IF;
844
845 OE_Msg_Pub.Set_Msg_Context
846 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE
847 ,p_entity_id => p_x_fte_source_line_tab(l_fte_count).source_line_id
848 ,p_header_id => p_x_fte_source_line_tab(l_fte_count).source_header_id
849 ,p_line_id => p_x_fte_source_line_tab(l_fte_count).source_line_id
850 ,p_order_source_id => l_order_source_id
851 ,p_orig_sys_document_ref => l_orig_sys_document_ref
852 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
853 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
854 ,p_change_sequence => l_change_sequence
855 ,p_source_document_id => l_source_document_id
856 ,p_source_document_line_id => l_source_document_line_id
857 ,p_source_document_type_id => l_source_document_type_id);
858
859 OE_MSG_PUB.Add_Text(p_x_fte_source_line_tab(l_fte_count).message_data);
860 IF l_debug_level > 0 THEN
861 oe_debug_pub.Add
862 ('FTE Error :'|| p_x_fte_source_line_tab(l_fte_count).message_data,3);
863 END IF;
864 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_NONE;
865 x_no_opr_count := x_no_opr_count + 1;
866 x_return_status := FND_API.G_RET_STS_ERROR;
867
868 ELSE -- warning or success
869
870 IF p_x_fte_source_line_tab(l_fte_count).status = 'W' THEN
871 OE_MSG_PUB.Add_Text
872 (p_x_fte_source_line_tab(l_fte_count).message_data);
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.Add
875 ('FTE Warning:'|| p_x_fte_source_line_tab(l_fte_count).message_data,3);
876 END IF;
877 END IF;
878
879
880 IF (p_x_fte_source_line_tab(l_fte_count).ship_method_code) <>
881 nvl(p_x_line_tbl(l_line_offset).shipping_method_code,'-99') OR
882 (p_x_fte_source_line_tab(l_fte_count).freight_carrier_code) =
883 nvl(p_x_line_tbl(l_line_offset).freight_carrier_code,'-99') OR
884 (p_x_fte_source_line_tab(l_fte_count).freight_terms) =
885 nvl(p_x_line_tbl(l_line_offset).freight_terms_code,'-99')
886 THEN
887 IF l_debug_level > 0 THEN
888 oe_debug_pub.Add('Change :'|| p_x_line_tbl(l_line_offset).line_id,3);
889 END IF;
890
891 p_x_line_tbl(l_line_offset).shipping_method_code :=
892 p_x_fte_source_line_tab(l_fte_count).ship_method_code;
893
894 p_x_line_tbl(l_line_offset).freight_carrier_code :=
895 p_x_fte_source_line_tab(l_fte_count).freight_carrier_code;
896
897 p_x_line_tbl(l_line_offset).freight_terms_code :=
898 p_x_fte_source_line_tab(l_fte_count).freight_terms;
899
900 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_UPDATE;
901
902 /* Start Audit Trail */
903
904 p_x_line_tbl(l_line_offset).change_reason := 'SYSTEM';
905 p_x_line_tbl(l_line_offset).change_comments := 'Get Ship Method Action';
906
907 /* End Audit Trail */
908
909 ELSE -- no change, set the operation to none.
910 IF l_debug_level > 0 THEN
911 oe_debug_pub.add('No Changes to save', 4);
912 END IF;
913 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_NONE;
914 x_no_opr_count := x_no_opr_count + 1;
915 END IF;
916
917 END IF; -- if error.
918
919
920 -- cascade to CONFIG line, we need to do this irrespective
921 -- of the fte source line ret status because we need to
922 -- push the offset anyway.
923
924 IF p_config_count > 0 AND l_index <= p_config_count THEN
925
926 IF p_x_line_tbl(l_line_offset).ato_line_id =
927 p_x_line_tbl(l_line_offset).line_id THEN
928
929 -- This is an ATO Model
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add('ato model '||p_x_line_tbl(l_line_offset).line_id,3);
932 END IF;
933
934 IF p_x_line_tbl(l_index).ato_line_id =
935 p_x_line_tbl(l_line_offset).line_id
936 THEN
937
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('cfg line '|| p_x_line_tbl(l_index).line_id, 3);
940 END IF;
941
942 IF p_x_line_tbl(l_line_offset).operation = OE_GLOBALS.G_OPR_UPDATE
943 THEN
944
945 p_x_line_tbl(l_index).shipping_method_code :=
946 p_x_line_tbl(l_line_offset).shipping_method_code;
947
948 p_x_line_tbl(l_index).freight_carrier_code :=
949 p_x_line_tbl(l_line_offset).freight_carrier_code;
950
951 p_x_line_tbl(l_index).freight_terms_code :=
952 p_x_line_tbl(l_line_offset).freight_terms_code;
953
954 /* Start Audit Trail */
955
956 p_x_line_tbl(l_index).change_reason := 'SYSTEM';
957 p_x_line_tbl(l_index).change_comments := 'Get Ship Method Action';
958
959 /* End Audit Trail */
960
961 IF l_debug_level > 0 THEN
962 oe_debug_pub.Add('Cascading Ship Method from:' ||
963 p_x_line_tbl(l_line_offset).line_id ||' to ' ||
964 p_x_line_tbl(l_index).line_id,3);
965 END IF;
966
967 END IF;
968 IF l_debug_level > 0 THEN
969 oe_debug_pub.add
970 ('cfg opr '||p_x_line_tbl(l_line_offset).operation, 3);
971 END IF;
972
973 p_x_line_tbl(l_index).operation :=
974 p_x_line_tbl(l_line_offset).operation;
975
976 l_index := l_index + 1;
977
978 END IF;
979
980 END IF;
981 END IF; -- l_config_count
982
983 l_fte_count := p_x_fte_source_line_tab.NEXT(l_fte_count);
984 l_line_offset := l_line_offset + 1;
985
986 END LOOP;
987
988 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Process_FTE_Output..');
989
990 EXCEPTION
991
992 WHEN FND_API.G_EXC_ERROR THEN
993 IF l_debug_level > 0 THEN
994 oe_debug_pub.Add('Expected Error in Process FTE Output', 1);
995 END IF;
996
997 x_return_status := FND_API.G_RET_STS_ERROR;
998
999 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1000 IF l_debug_level > 0 THEN
1001 oe_debug_pub.Add('Unexpected Error in Process FTE Output'||
1002 sqlerrm, 2);
1003 END IF;
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005
1006 WHEN OTHERS THEN
1007
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009
1010 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1011 THEN
1012 OE_MSG_PUB.Add_Exc_Msg
1013 ( G_PKG_NAME,
1014 'Process_FTE_Output');
1015 END IF;
1016 END Process_FTE_Output;
1017
1018
1019 /*--------------------------------------------------------------+
1020 Name : Process_FTE_Action
1021 Parameters : IN p_header_id
1022 p_line_id - should be null for now
1023 p_ui_flag
1024 p_action - 'C','R'or 'B'
1025 p_call_pricing_for_FR - for freight rates
1026 OUT NOCOPY
1027 x_msg_count
1028 x_msg_data
1029 x_return_status
1030 Description : This Procedure is called from OEXOEFRM.pld
1031 when Get Ship Method or Get Freight Rate or
1032 Get Ship Method and Freight Rate action is
1033 selected.
1034 It is also called from process order delayed
1035 requests and actions processing.
1036 THe p_action 'C','R'or 'B' to indicate
1037 whether the 3 actions that can be performed.
1038
1039 First FTE input record is prepared. The FTE procedure
1040 Process lines is called to process FTE action.
1041 Then based on the p_action we call apprpriate
1042 o/p processing API i.e. to save ship menthods
1043 and / or rates.
1044
1045 Change Record :
1046
1047 +--------------------------------------------------------------*/
1048
1049 PROCEDURE Process_FTE_Action
1050 ( p_header_id IN NUMBER
1051 ,p_line_id IN NUMBER
1052 ,p_ui_flag IN VARCHAR2
1053 ,p_action IN VARCHAR2
1054 ,p_call_pricing_for_FR IN VARCHAR2
1055 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1056 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1057 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1058 IS
1059 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
1060 l_fte_source_header_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Tab;
1061 l_fte_rates_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
1062 l_fte_header_rates_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Rates_Tab;
1063 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1064 l_config_count NUMBER;
1065 l_no_opr_count NUMBER;
1066 l_msg_text VARCHAR2(2000);
1067 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1068 BEGIN
1069
1070 Print_Time('Entering Process_FTE_Action...'|| p_action);
1071
1072 IF p_line_id is not NULL THEN
1073 IF l_debug_level > 0 THEN
1074 oe_debug_pub.add('line id not null, error for now', 1);
1075 END IF;
1076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077 END IF;
1078
1079 x_return_status := FND_API.G_RET_STS_SUCCESS;
1080
1081 IF p_ui_flag = 'Y' THEN
1082 OE_MSG_PUB.Initialize;
1083 END IF;
1084
1085 -- Check whether FTE is Installed. If not Exit
1086
1087 IF OE_GLOBALS.G_FTE_INSTALLED IS NULL THEN
1088 OE_GLOBALS.G_FTE_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(716);
1089 END IF;
1090
1091 IF OE_GLOBALS.G_FTE_INSTALLED = 'N' THEN
1092 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NOT_INSTALLED');
1093 OE_MSG_PUB.Add;
1094 IF l_debug_level > 0 THEN
1095 oe_debug_pub.ADD('FTE is NOT Installed!',3);
1096 END IF;
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 RETURN;
1099 END IF;
1100
1101 Create_FTE_Input
1102 ( p_header_id => p_header_id
1103 ,p_line_id => p_line_id
1104 ,p_x_fte_source_line_tab => l_fte_source_line_tab
1105 ,p_x_line_tbl => l_line_tbl
1106 ,p_action => p_action
1107 ,x_config_count => l_config_count
1108 ,x_return_status => x_return_status);
1109
1110 IF l_debug_level > 0 THEN
1111 oe_debug_pub.Add('After Calling Create FTE Input :'||
1112 x_return_status,3);
1113 END IF;
1114
1115 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1118 IF l_debug_level > 0 THEN
1119 oe_debug_pub.add(x_msg_data,1);
1120 END IF;
1121 RAISE FND_API.G_EXC_ERROR;
1122 END IF;
1123 IF l_debug_level > 0 THEN
1124 oe_debug_pub.Add('Before Calling FTE Process Lines...',3);
1125 END IF;
1126
1127 -- Call FTE only if the number of lines is greater than zero.
1128
1129 IF l_line_tbl.Count > 0 THEN
1130
1131 FTE_PROCESS_REQUESTS.Process_Lines
1132 ( p_source_line_tab => l_fte_source_line_tab
1133 ,p_source_header_tab => l_fte_source_header_tab
1134 ,p_source_type => 'ONT'
1135 ,p_action => p_action
1136 ,x_source_line_rates_tab => l_fte_rates_tab
1137 ,x_source_header_rates_tab => l_fte_header_rates_tab
1138 ,x_return_status => x_return_status
1139 ,x_msg_count => x_msg_count
1140 ,x_msg_data => x_msg_data );
1141
1142 IF l_debug_level > 0 THEN
1143 oe_debug_pub.Add('After Calling FTE Process Lines:'||
1144 x_return_status,3);
1145 END IF;
1146
1147 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1149 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1150 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
1151 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1152 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1153 IF l_debug_level > 0 THEN
1154 oe_debug_pub.Add(l_msg_text, 3);
1155 END IF;
1156 oe_msg_pub.add_text(p_message_text => l_msg_text);
1157 END LOOP;
1158 END IF;
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161
1162
1163
1164 IF p_action = 'B' OR
1165 p_action = 'C' THEN
1166 IF l_debug_level > 0 THEN
1167 oe_debug_pub.add('processing o/p for carrier', 1);
1168 END IF;
1169 Process_FTE_Output
1170 ( p_x_fte_source_line_tab => l_fte_source_line_tab
1171 ,p_x_line_tbl => l_line_tbl
1172 ,p_config_count => l_config_count
1173 ,x_no_opr_count => l_no_opr_count
1174 ,x_return_status => x_return_status);
1175
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.Add('After Calling Process FTE Output: '||
1178 x_return_status);
1179 END IF;
1180
1181 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1183 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186
1187 -- set the recursion flag so that FTE won't be called
1188 -- again for Freight Rating if ship method is changed
1189 -- due to the call to FTE.
1190 oe_globals.g_freight_recursion := 'Y';
1191
1192 IF l_debug_level > 0 THEN
1193 oe_debug_pub.Add('Before Calling Update FTE Results...',3);
1194 END IF;
1195
1196 -- If there are no lines with operation UPDATE donot call
1197 -- Update Fte Results.
1198
1199 IF l_no_opr_count < l_line_tbl.count THEN
1200 Update_FTE_Results
1201 ( p_x_line_tbl => l_line_tbl
1202 ,x_return_status => x_return_status);
1203
1204 IF l_debug_level > 0 THEN
1205 oe_debug_pub.Add('After Calling Update FTE Results...'||
1206 x_return_status,3);
1207 END IF;
1208
1209 ELSE
1210 IF l_debug_level > 0 THEN
1211 oe_debug_pub.Add('Donot Call Update Results!!:'||l_line_tbl.count,1);
1212 oe_debug_pub.Add('No of Lines with Opr None:'||l_no_opr_count,1);
1213 END IF;
1214 END IF;
1215
1216 oe_globals.g_freight_recursion := 'N';
1217
1218 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1220 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 END IF;
1223
1224
1225 END IF; -- action is carrier or both
1226
1227 IF p_action = 'B' OR
1228 p_action = 'R' THEN
1229 IF l_debug_level > 0 THEN
1230 oe_debug_pub.add('processing o/p for rating', 1);
1231 END IF;
1232
1233 OE_FREIGHT_RATING_PVT.Process_FTE_Output
1234 ( p_header_id => p_header_id
1235 ,p_x_fte_source_line_tab => l_fte_source_line_tab
1236 ,p_x_line_tbl => l_line_tbl
1237 ,p_fte_rates_tab => l_fte_rates_tab
1238 ,p_config_count => l_config_count
1239 ,p_ui_flag => p_ui_flag
1240 ,p_call_pricing_for_FR => p_call_pricing_for_FR
1241 ,x_return_status => x_return_status);
1242
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.Add('After Calling FREIGHT Process_FTE_Output '||
1245 x_return_status,3);
1246 END IF;
1247
1248 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1250 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1251 RAISE FND_API.G_EXC_ERROR;
1252 END IF;
1253
1254 END IF;
1255 END IF; -- l_line_tbl.count > 0
1256
1257 OE_MSG_PUB.Count_And_Get
1258 ( p_count => x_msg_count
1259 ,p_data => x_msg_data);
1260
1261 IF l_debug_level > 0 THEN
1262 oe_debug_pub.Add('Message Count :'||x_msg_count,3);
1263 END IF;
1264
1265 Print_Time('Exiting OE_FTE_INTEGRATION_PVT.Process_FTE_Action...');
1266
1267 EXCEPTION
1268
1269 WHEN FND_API.G_EXC_ERROR THEN
1270 IF l_debug_level > 0 THEN
1271 oe_debug_pub.Add('Expected Error in Get Ship Method', 1);
1272 END IF;
1273
1274 OE_MSG_PUB.Count_And_Get
1275 ( p_count => x_msg_count
1276 ,p_data => x_msg_data);
1277
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279
1280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1281 IF l_debug_level > 0 THEN
1282 oe_debug_pub.Add('Unexpected Error in Get Ship Method'||sqlerrm, 2);
1283 END IF;
1284
1285 OE_MSG_PUB.Count_And_Get
1286 ( p_count => x_msg_count
1287 ,p_data => x_msg_data);
1288
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1290
1291
1292 WHEN OTHERS THEN
1293
1294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1295 IF l_debug_level > 0 THEN
1296 oe_debug_pub.Add('When Others in Get Ship Method'||sqlerrm,3);
1297 END IF;
1298
1299 OE_MSG_PUB.Count_And_Get
1300 ( p_count => x_msg_count
1301 ,p_data => x_msg_data);
1302
1303 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1304 THEN
1305 OE_MSG_PUB.Add_Exc_Msg
1306 ( G_PKG_NAME,
1307 'Process_FTE_Action');
1308 END IF;
1309
1310 END Process_FTE_Action;
1311
1312 END OE_FTE_INTEGRATION_PVT;
[Home] [Help]