1 PACKAGE BODY OE_FTE_INTEGRATION_PVT AS
2 /* $Header: OEXVFTEB.pls 120.0 2005/06/01 01:09:38 appldev noship $ */
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') AND
573 c_ship_method.ato_line_id = c_ship_method.line_id)
574 THEN
575 IF l_debug_level > 0 THEN
576 OE_DEBUG_PUB.Add('calculate rating part of ato', 4);
577 END IF;
578 ELSE
579 IF l_debug_level > 0 THEN
580 OE_DEBUG_PUB.Add('do not calculate freight_rating '||
581 c_ship_method.line_id, 1);
582 END IF;
583 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'N';
584 END IF;
585
586 END IF;
587
588 IF l_debug_level > 0 THEN
589 oe_debug_pub.Add('--------- Input to FTE --------',3);
590
591 oe_debug_pub.Add('Source Line :'||
592 p_x_fte_source_line_tab(l_count).source_line_id,3);
593
594 oe_debug_pub.Add('Ship From Org :'||
595 p_x_fte_source_line_tab(l_count).ship_from_org_id,3);
596
597 oe_debug_pub.Add('Customer :'||
598 p_x_fte_source_line_tab(l_count).customer_id,3);
599
600 oe_debug_pub.Add('Inventory Item :'||
601 p_x_fte_source_line_tab(l_count).inventory_item_id,3);
602
603 oe_debug_pub.Add('Source Quantity :'||
604 p_x_fte_source_line_tab(l_count).source_quantity,3);
605
606 oe_debug_pub.Add('Ship Date :'||
607 p_x_fte_source_line_tab(l_count).ship_date,3);
608
609 oe_debug_pub.Add('Delivery Lead Time :'||
610 p_x_fte_source_line_tab(l_count).delivery_lead_time,3);
611
612 oe_debug_pub.Add('Scheduled :'||
613 p_x_fte_source_line_tab(l_count).scheduled_flag,3);
614
615 oe_debug_pub.Add('Order Set Type :'||
616 p_x_fte_source_line_tab(l_count).order_set_type,3);
617
618 oe_debug_pub.Add('Order Set :'||
619 p_x_fte_source_line_tab(l_count).order_set_id,3);
620
621 oe_debug_pub.Add('Ship Method :'||
622 p_x_fte_source_line_tab(l_count).ship_method_code,3);
623
624 oe_debug_pub.Add('Freight Terms :'||
625 p_x_fte_source_line_tab(l_count).freight_terms,3);
626
627 oe_debug_pub.Add('Freight on Board :'||
628 p_x_fte_source_line_tab(l_count).fob_code,3);
629
630 oe_debug_pub.Add('Intermediate Ship :'||
631 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id,3);
632
633 oe_debug_pub.Add('-------------------------------',3);
634 END IF;
635
636 -- Store the old ship method code in the line table
637
638 l_line_rec.line_id := c_ship_method.line_id;
639 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
640 l_line_rec.item_type_code := c_ship_method.item_type_code;
641 l_line_rec.shipping_method_code :=
642 c_ship_method.shipping_method_code;
643 l_line_rec.freight_terms_code :=
644 c_ship_method.freight_terms_code;
645 l_line_rec.freight_carrier_code :=
646 c_ship_method.freight_carrier_code;
647
648 l_line_count := l_line_count + 1;
649 p_x_line_tbl(l_line_count) := l_line_rec;
650
651 END IF;
652
653 END LOOP;
654
655 -- this check is added to show the message when no lines of the order are eligible for freight rating
656
657 IF p_x_line_tbl.count = 0 THEN
658
659 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NO_LINES_ELIGIBLE');
660 OE_MSG_PUB.Add;
661 IF l_debug_level > 0 THEN
662 OE_DEBUG_PUB.Add('No lines of the order are eligible for freight rating');
663 END IF;
664 END IF;
665
666 IF l_debug_level > 0 THEN
667 oe_debug_pub.Add('FTE Input count:'||p_x_fte_source_line_tab.count,3);
668 oe_debug_pub.Add('Total count:'|| p_x_line_tbl.count,3);
669 END IF;
670
671 Print_Time('Exiting OE_FTE_INTEGRATION_PVT.Create_FTE_Input...');
672 EXCEPTION
673
674 WHEN FND_API.G_EXC_ERROR THEN
675 IF l_debug_level > 0 THEN
676 oe_debug_pub.Add('Expected Error in Create FTE Input',2);
677 END IF;
678
679 x_return_status := FND_API.G_RET_STS_ERROR;
680
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 IF l_debug_level > 0 THEN
683 oe_debug_pub.Add('Unexpected Error in Create FTE Input:'||SqlErrm, 1);
684 END IF;
685
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687
688
689 WHEN OTHERS THEN
690
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692
693 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
694 THEN
695 OE_MSG_PUB.Add_Exc_Msg
696 ( G_PKG_NAME,
697 'Create_FTE_Input');
698 END IF;
699
700 END Create_FTE_Input;
701
702
703 /*--------------------------------------------------------------+
704 Name : Process_FTE_Output
705 Parameters : IN OUT NOCOPY p_x_line_tbl
706 IN OUT NOCOPY p_x_fte_source_line_tab
707 IN p_config_count
708 OUT NOCOPY
709 x_return_status
710 x_no_opr_count
711 Description : This Procedure processes the FTE output and
712 is called from procedure process_fte_actions.
713 In this Procedure FTE line table is traversed
714 if there is any change in the ship method
715 we will update the changes in the line and
716 and Call Process Order. Process Order should
717 not be called when there are no lines with
718 operation equal to UPDATE. Count number of
719 lines which have operation as none and do
720 not call update results if there is atleast
721 one line with operation update.
722
723 o/p parameters are cascaded on CONFIG line
724 from its ato parent line.
725 Change Record :
726
727 +--------------------------------------------------------------*/
728
729 PROCEDURE Process_FTE_Output
730 ( p_x_fte_source_line_tab IN OUT NOCOPY
731 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
732 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type
733 ,p_config_count IN NUMBER
734 ,x_no_opr_count OUT NOCOPY /* file.sql.39 change */ NUMBER
735 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
736 IS
737 l_fte_count NUMBER := 1;
738 l_line_offset NUMBER := 0;
739 l_index NUMBER := 1;
740
741 l_order_source_id NUMBER;
742 l_orig_sys_document_ref VARCHAR2(50);
743 l_orig_sys_line_ref VARCHAR2(50);
744 l_orig_sys_shipment_ref VARCHAR2(50);
745 l_change_sequence VARCHAR2(50);
746 l_source_document_id NUMBER;
747 l_source_document_line_id NUMBER;
748 l_source_document_type_id NUMBER;
749 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
750 BEGIN
751
752 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Process_FTE_Output..');
753
754 x_no_opr_count := 0;
755 x_return_status := FND_API.G_RET_STS_SUCCESS;
756 l_line_offset := p_config_count + 1;
757
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.Add('Total Number of Config lines:'||p_config_count,3);
760 oe_debug_pub.Add('Line Offset is :'||l_line_offset,3);
761 END IF;
762
763 WHILE l_fte_count IS NOT NULL
764 LOOP
765 IF l_debug_level > 0 THEN
766 oe_debug_pub.Add('--------- FTE Results ----------',3);
767
768 oe_debug_pub.Add('Source Line :'||
769 p_x_fte_source_line_tab(l_fte_count).source_line_id,3);
770
771 oe_debug_pub.Add('Ship Method :'||
772 p_x_fte_source_line_tab(l_fte_count).ship_method_code,3);
773
774 oe_debug_pub.Add('Frieght Carrier :'||
775 p_x_fte_source_line_tab(l_fte_count).freight_carrier_code,3);
776
777 oe_debug_pub.Add('Service Level :' ||
778 p_x_fte_source_line_tab(l_fte_count).service_level,3);
779
780 oe_debug_pub.Add('Mode of Transport :'||
781 p_x_fte_source_line_tab(l_fte_count).mode_of_transport,3);
782
783 oe_debug_pub.Add('Frieght Terms :'||
784 p_x_fte_source_line_tab(l_fte_count).freight_terms,3);
785
786 oe_debug_pub.Add('Weight :'||
787 p_x_fte_source_line_tab(l_fte_count).weight,3);
788
789 oe_debug_pub.Add('Weight UOM :'||
790 p_x_fte_source_line_tab(l_fte_count).weight_uom_code,3);
791
792 oe_debug_pub.Add('Volume :'||
793 p_x_fte_source_line_tab(l_fte_count).volume,3);
794
795 oe_debug_pub.Add('Volume UOM :'||
796 p_x_fte_source_line_tab(l_fte_count).volume_uom_code,3);
797
798 oe_debug_pub.Add('Frieght Rate :'||
799 p_x_fte_source_line_tab(l_fte_count).freight_rate,3);
800
801 oe_debug_pub.Add('Frieght Rate Currency :'||
802 p_x_fte_source_line_tab(l_fte_count).freight_rate_currency,3);
803
804 oe_debug_pub.Add('Status :'||
805 p_x_fte_source_line_tab(l_fte_count).status,3);
806
807 END IF;
808
809 IF p_x_fte_source_line_tab(l_fte_count).status =
810 FND_API.G_RET_STS_ERROR THEN
811
812 IF p_x_fte_source_line_tab(l_fte_count).source_line_id IS NOT NULL AND
813 p_x_fte_source_line_tab(l_fte_count).source_line_id <> FND_API.G_MISS_NUM THEN
814 BEGIN
815 IF l_debug_level > 0 THEN
816 oe_debug_pub.add('Getting reference data for line_id:'||
817 p_x_fte_source_line_tab(l_fte_count).source_line_id);
818 END IF;
819 SELECT order_source_id, orig_sys_document_ref, orig_sys_line_ref,
820 orig_sys_shipment_ref, change_sequence, source_document_id,
821 source_document_line_id, source_document_type_id
822 INTO l_order_source_id, l_orig_sys_document_ref, l_orig_sys_line_ref,
823 l_orig_sys_shipment_ref, l_change_sequence, l_source_document_id,
824 l_source_document_line_id, l_source_document_type_id
825 FROM oe_order_lines_all
826 WHERE line_id = p_x_fte_source_line_tab(l_fte_count).source_line_id;
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 IF l_debug_level > 0 THEN
830 oe_debug_pub.add('No_data_found while getting reference data '||
831 'for line_id:'||p_x_fte_source_line_tab(l_fte_count).source_line_id);
832 END IF;
833 l_order_source_id := NULL;
834 l_orig_sys_document_ref := NULL;
835 l_orig_sys_line_ref := NULL;
836 l_orig_sys_shipment_ref := NULL;
837 l_change_sequence := NULL;
838 l_source_document_id := NULL;
839 l_source_document_line_id := NULL;
840 l_source_document_type_id := NULL;
841 END;
842 END IF;
843
844 OE_Msg_Pub.Set_Msg_Context
845 (p_entity_code => OE_GLOBALS.G_ENTITY_LINE
846 ,p_entity_id => p_x_fte_source_line_tab(l_fte_count).source_line_id
847 ,p_header_id => p_x_fte_source_line_tab(l_fte_count).source_header_id
848 ,p_line_id => p_x_fte_source_line_tab(l_fte_count).source_line_id
849 ,p_order_source_id => l_order_source_id
850 ,p_orig_sys_document_ref => l_orig_sys_document_ref
851 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
852 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
853 ,p_change_sequence => l_change_sequence
854 ,p_source_document_id => l_source_document_id
855 ,p_source_document_line_id => l_source_document_line_id
856 ,p_source_document_type_id => l_source_document_type_id);
857
858 OE_MSG_PUB.Add_Text(p_x_fte_source_line_tab(l_fte_count).message_data);
859 IF l_debug_level > 0 THEN
860 oe_debug_pub.Add
861 ('FTE Error :'|| p_x_fte_source_line_tab(l_fte_count).message_data,3);
862 END IF;
863 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_NONE;
864 x_no_opr_count := x_no_opr_count + 1;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866
867 ELSE -- warning or success
868
869 IF p_x_fte_source_line_tab(l_fte_count).status = 'W' THEN
870 OE_MSG_PUB.Add_Text
871 (p_x_fte_source_line_tab(l_fte_count).message_data);
872 IF l_debug_level > 0 THEN
873 oe_debug_pub.Add
874 ('FTE Warning:'|| p_x_fte_source_line_tab(l_fte_count).message_data,3);
875 END IF;
876 END IF;
877
878
879 IF (p_x_fte_source_line_tab(l_fte_count).ship_method_code) <>
880 nvl(p_x_line_tbl(l_line_offset).shipping_method_code,'-99') OR
881 (p_x_fte_source_line_tab(l_fte_count).freight_carrier_code) =
882 nvl(p_x_line_tbl(l_line_offset).freight_carrier_code,'-99') OR
883 (p_x_fte_source_line_tab(l_fte_count).freight_terms) =
884 nvl(p_x_line_tbl(l_line_offset).freight_terms_code,'-99')
885 THEN
886 IF l_debug_level > 0 THEN
887 oe_debug_pub.Add('Change :'|| p_x_line_tbl(l_line_offset).line_id,3);
888 END IF;
889
890 p_x_line_tbl(l_line_offset).shipping_method_code :=
891 p_x_fte_source_line_tab(l_fte_count).ship_method_code;
892
893 p_x_line_tbl(l_line_offset).freight_carrier_code :=
894 p_x_fte_source_line_tab(l_fte_count).freight_carrier_code;
895
896 p_x_line_tbl(l_line_offset).freight_terms_code :=
897 p_x_fte_source_line_tab(l_fte_count).freight_terms;
898
899 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_UPDATE;
900
901 /* Start Audit Trail */
902
903 p_x_line_tbl(l_line_offset).change_reason := 'SYSTEM';
904 p_x_line_tbl(l_line_offset).change_comments := 'Get Ship Method Action';
905
906 /* End Audit Trail */
907
908 ELSE -- no change, set the operation to none.
909 IF l_debug_level > 0 THEN
910 oe_debug_pub.add('No Changes to save', 4);
911 END IF;
912 p_x_line_tbl(l_line_offset).operation := OE_GLOBALS.G_OPR_NONE;
913 x_no_opr_count := x_no_opr_count + 1;
914 END IF;
915
916 END IF; -- if error.
917
918
919 -- cascade to CONFIG line, we need to do this irrespective
920 -- of the fte source line ret status because we need to
921 -- push the offset anyway.
922
923 IF p_config_count > 0 AND l_index <= p_config_count THEN
924
925 IF p_x_line_tbl(l_line_offset).ato_line_id =
926 p_x_line_tbl(l_line_offset).line_id THEN
927
928 -- This is an ATO Model
929 IF l_debug_level > 0 THEN
930 oe_debug_pub.add('ato model '||p_x_line_tbl(l_line_offset).line_id,3);
931 END IF;
932
933 IF p_x_line_tbl(l_index).ato_line_id =
934 p_x_line_tbl(l_line_offset).line_id
935 THEN
936
937 IF l_debug_level > 0 THEN
938 oe_debug_pub.add('cfg line '|| p_x_line_tbl(l_index).line_id, 3);
939 END IF;
940
941 IF p_x_line_tbl(l_line_offset).operation = OE_GLOBALS.G_OPR_UPDATE
942 THEN
943
944 p_x_line_tbl(l_index).shipping_method_code :=
945 p_x_line_tbl(l_line_offset).shipping_method_code;
946
947 p_x_line_tbl(l_index).freight_carrier_code :=
948 p_x_line_tbl(l_line_offset).freight_carrier_code;
949
950 p_x_line_tbl(l_index).freight_terms_code :=
951 p_x_line_tbl(l_line_offset).freight_terms_code;
952
953 /* Start Audit Trail */
954
955 p_x_line_tbl(l_index).change_reason := 'SYSTEM';
956 p_x_line_tbl(l_index).change_comments := 'Get Ship Method Action';
957
958 /* End Audit Trail */
959
960 IF l_debug_level > 0 THEN
961 oe_debug_pub.Add('Cascading Ship Method from:' ||
962 p_x_line_tbl(l_line_offset).line_id ||' to ' ||
963 p_x_line_tbl(l_index).line_id,3);
964 END IF;
965
966 END IF;
967 IF l_debug_level > 0 THEN
968 oe_debug_pub.add
969 ('cfg opr '||p_x_line_tbl(l_line_offset).operation, 3);
970 END IF;
971
972 p_x_line_tbl(l_index).operation :=
973 p_x_line_tbl(l_line_offset).operation;
974
975 l_index := l_index + 1;
976
977 END IF;
978
979 END IF;
980 END IF; -- l_config_count
981
982 l_fte_count := p_x_fte_source_line_tab.NEXT(l_fte_count);
983 l_line_offset := l_line_offset + 1;
984
985 END LOOP;
986
987 Print_Time('Entering OE_FTE_INTEGRATION_PVT.Process_FTE_Output..');
988
989 EXCEPTION
990
991 WHEN FND_API.G_EXC_ERROR THEN
992 IF l_debug_level > 0 THEN
993 oe_debug_pub.Add('Expected Error in Process FTE Output', 1);
994 END IF;
995
996 x_return_status := FND_API.G_RET_STS_ERROR;
997
998 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
999 IF l_debug_level > 0 THEN
1000 oe_debug_pub.Add('Unexpected Error in Process FTE Output'||
1001 sqlerrm, 2);
1002 END IF;
1003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1004
1005 WHEN OTHERS THEN
1006
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008
1009 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1010 THEN
1011 OE_MSG_PUB.Add_Exc_Msg
1012 ( G_PKG_NAME,
1013 'Process_FTE_Output');
1014 END IF;
1015 END Process_FTE_Output;
1016
1017
1018 /*--------------------------------------------------------------+
1019 Name : Process_FTE_Action
1020 Parameters : IN p_header_id
1021 p_line_id - should be null for now
1022 p_ui_flag
1023 p_action - 'C','R'or 'B'
1024 p_call_pricing_for_FR - for freight rates
1025 OUT NOCOPY
1026 x_msg_count
1027 x_msg_data
1028 x_return_status
1029 Description : This Procedure is called from OEXOEFRM.pld
1030 when Get Ship Method or Get Freight Rate or
1031 Get Ship Method and Freight Rate action is
1032 selected.
1033 It is also called from process order delayed
1034 requests and actions processing.
1035 THe p_action 'C','R'or 'B' to indicate
1036 whether the 3 actions that can be performed.
1037
1038 First FTE input record is prepared. The FTE procedure
1039 Process lines is called to process FTE action.
1040 Then based on the p_action we call apprpriate
1041 o/p processing API i.e. to save ship menthods
1042 and / or rates.
1043
1044 Change Record :
1045
1046 +--------------------------------------------------------------*/
1047
1048 PROCEDURE Process_FTE_Action
1049 ( p_header_id IN NUMBER
1050 ,p_line_id IN NUMBER
1051 ,p_ui_flag IN VARCHAR2
1052 ,p_action IN VARCHAR2
1053 ,p_call_pricing_for_FR IN VARCHAR2
1054 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1055 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1056 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1057 IS
1058 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
1059 l_fte_source_header_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Tab;
1060 l_fte_rates_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
1061 l_fte_header_rates_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Rates_Tab;
1062 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1063 l_config_count NUMBER;
1064 l_no_opr_count NUMBER;
1065 l_msg_text VARCHAR2(2000);
1066 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1067 BEGIN
1068
1069 Print_Time('Entering Process_FTE_Action...'|| p_action);
1070
1071 IF p_line_id is not NULL THEN
1072 IF l_debug_level > 0 THEN
1073 oe_debug_pub.add('line id not null, error for now', 1);
1074 END IF;
1075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1076 END IF;
1077
1078 x_return_status := FND_API.G_RET_STS_SUCCESS;
1079
1080 IF p_ui_flag = 'Y' THEN
1081 OE_MSG_PUB.Initialize;
1082 END IF;
1083
1084 -- Check whether FTE is Installed. If not Exit
1085
1086 IF OE_GLOBALS.G_FTE_INSTALLED IS NULL THEN
1087 OE_GLOBALS.G_FTE_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(716);
1088 END IF;
1089
1090 IF OE_GLOBALS.G_FTE_INSTALLED = 'N' THEN
1091 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NOT_INSTALLED');
1092 OE_MSG_PUB.Add;
1093 IF l_debug_level > 0 THEN
1094 oe_debug_pub.ADD('FTE is NOT Installed!',3);
1095 END IF;
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097 RETURN;
1098 END IF;
1099
1100 Create_FTE_Input
1101 ( p_header_id => p_header_id
1102 ,p_line_id => p_line_id
1103 ,p_x_fte_source_line_tab => l_fte_source_line_tab
1104 ,p_x_line_tbl => l_line_tbl
1105 ,p_action => p_action
1106 ,x_config_count => l_config_count
1107 ,x_return_status => x_return_status);
1108
1109 IF l_debug_level > 0 THEN
1110 oe_debug_pub.Add('After Calling Create FTE Input :'||
1111 x_return_status,3);
1112 END IF;
1113
1114 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1117 IF l_debug_level > 0 THEN
1118 oe_debug_pub.add(x_msg_data,1);
1119 END IF;
1120 RAISE FND_API.G_EXC_ERROR;
1121 END IF;
1122 IF l_debug_level > 0 THEN
1123 oe_debug_pub.Add('Before Calling FTE Process Lines...',3);
1124 END IF;
1125
1126 -- Call FTE only if the number of lines is greater than zero.
1127
1128 IF l_line_tbl.Count > 0 THEN
1129
1130 FTE_PROCESS_REQUESTS.Process_Lines
1131 ( p_source_line_tab => l_fte_source_line_tab
1132 ,p_source_header_tab => l_fte_source_header_tab
1133 ,p_source_type => 'ONT'
1134 ,p_action => p_action
1135 ,x_source_line_rates_tab => l_fte_rates_tab
1136 ,x_source_header_rates_tab => l_fte_header_rates_tab
1137 ,x_return_status => x_return_status
1138 ,x_msg_count => x_msg_count
1139 ,x_msg_data => x_msg_data );
1140
1141 IF l_debug_level > 0 THEN
1142 oe_debug_pub.Add('After Calling FTE Process Lines:'||
1143 x_return_status,3);
1144 END IF;
1145
1146 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1149 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
1150 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1151 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1152 IF l_debug_level > 0 THEN
1153 oe_debug_pub.Add(l_msg_text, 3);
1154 END IF;
1155 oe_msg_pub.add_text(p_message_text => l_msg_text);
1156 END LOOP;
1157 END IF;
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160
1161
1162
1163 IF p_action = 'B' OR
1164 p_action = 'C' THEN
1165 IF l_debug_level > 0 THEN
1166 oe_debug_pub.add('processing o/p for carrier', 1);
1167 END IF;
1168 Process_FTE_Output
1169 ( p_x_fte_source_line_tab => l_fte_source_line_tab
1170 ,p_x_line_tbl => l_line_tbl
1171 ,p_config_count => l_config_count
1172 ,x_no_opr_count => l_no_opr_count
1173 ,x_return_status => x_return_status);
1174
1175 IF l_debug_level > 0 THEN
1176 oe_debug_pub.Add('After Calling Process FTE Output: '||
1177 x_return_status);
1178 END IF;
1179
1180 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1182 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1183 RAISE FND_API.G_EXC_ERROR;
1184 END IF;
1185
1186 -- set the recursion flag so that FTE won't be called
1187 -- again for Freight Rating if ship method is changed
1188 -- due to the call to FTE.
1189 oe_globals.g_freight_recursion := 'Y';
1190
1191 IF l_debug_level > 0 THEN
1192 oe_debug_pub.Add('Before Calling Update FTE Results...',3);
1193 END IF;
1194
1195 -- If there are no lines with operation UPDATE donot call
1196 -- Update Fte Results.
1197
1198 IF l_no_opr_count < l_line_tbl.count THEN
1199 Update_FTE_Results
1200 ( p_x_line_tbl => l_line_tbl
1201 ,x_return_status => x_return_status);
1202
1203 IF l_debug_level > 0 THEN
1204 oe_debug_pub.Add('After Calling Update FTE Results...'||
1205 x_return_status,3);
1206 END IF;
1207
1208 ELSE
1209 IF l_debug_level > 0 THEN
1210 oe_debug_pub.Add('Donot Call Update Results!!:'||l_line_tbl.count,1);
1211 oe_debug_pub.Add('No of Lines with Opr None:'||l_no_opr_count,1);
1212 END IF;
1213 END IF;
1214
1215 oe_globals.g_freight_recursion := 'N';
1216
1217 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1220 RAISE FND_API.G_EXC_ERROR;
1221 END IF;
1222
1223
1224 END IF; -- action is carrier or both
1225
1226 IF p_action = 'B' OR
1227 p_action = 'R' THEN
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add('processing o/p for rating', 1);
1230 END IF;
1231
1232 OE_FREIGHT_RATING_PVT.Process_FTE_Output
1233 ( p_header_id => p_header_id
1234 ,p_x_fte_source_line_tab => l_fte_source_line_tab
1235 ,p_x_line_tbl => l_line_tbl
1236 ,p_fte_rates_tab => l_fte_rates_tab
1237 ,p_config_count => l_config_count
1238 ,p_ui_flag => p_ui_flag
1239 ,p_call_pricing_for_FR => p_call_pricing_for_FR
1240 ,x_return_status => x_return_status);
1241
1242 IF l_debug_level > 0 THEN
1243 oe_debug_pub.Add('After Calling FREIGHT Process_FTE_Output '||
1244 x_return_status,3);
1245 END IF;
1246
1247 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 END IF;
1252
1253 END IF;
1254 END IF; -- l_line_tbl.count > 0
1255
1256 OE_MSG_PUB.Count_And_Get
1257 ( p_count => x_msg_count
1258 ,p_data => x_msg_data);
1259
1260 IF l_debug_level > 0 THEN
1261 oe_debug_pub.Add('Message Count :'||x_msg_count,3);
1262 END IF;
1263
1264 Print_Time('Exiting OE_FTE_INTEGRATION_PVT.Process_FTE_Action...');
1265
1266 EXCEPTION
1267
1268 WHEN FND_API.G_EXC_ERROR THEN
1269 IF l_debug_level > 0 THEN
1270 oe_debug_pub.Add('Expected Error in Get Ship Method', 1);
1271 END IF;
1272
1273 OE_MSG_PUB.Count_And_Get
1274 ( p_count => x_msg_count
1275 ,p_data => x_msg_data);
1276
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278
1279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1280 IF l_debug_level > 0 THEN
1281 oe_debug_pub.Add('Unexpected Error in Get Ship Method'||sqlerrm, 2);
1282 END IF;
1283
1284 OE_MSG_PUB.Count_And_Get
1285 ( p_count => x_msg_count
1286 ,p_data => x_msg_data);
1287
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289
1290
1291 WHEN OTHERS THEN
1292
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 IF l_debug_level > 0 THEN
1295 oe_debug_pub.Add('When Others in Get Ship Method'||sqlerrm,3);
1296 END IF;
1297
1298 OE_MSG_PUB.Count_And_Get
1299 ( p_count => x_msg_count
1300 ,p_data => x_msg_data);
1301
1302 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1303 THEN
1304 OE_MSG_PUB.Add_Exc_Msg
1305 ( G_PKG_NAME,
1306 'Process_FTE_Action');
1307 END IF;
1308
1309 END Process_FTE_Action;
1310
1311 END OE_FTE_INTEGRATION_PVT;
[Home] [Help]