[Home] [Help]
PACKAGE BODY: APPS.OE_FREIGHT_CHOICES_PVT
Source
1 PACKAGE BODY OE_FREIGHT_CHOICES_PVT AS
2 /* $Header: OEXVFCHB.pls 120.4.12010000.5 2008/11/14 23:11:03 rbadadar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_FREIGHT_CHOICES_PVT';
5 G_config_count NUMBER;
6 --G_line_tbl OE_Order_PUB.Line_Tbl_Type;
7
8 TYPE number_type is table of number index by binary_integer;
9
10 /*--------------------------------------------------------------+
11 | Local Procedures and Function Declarations |
12 +--------------------------------------------------------------*/
13
14 PROCEDURE Print_Time(p_msg IN VARCHAR2);
15
16 PROCEDURE Prepare_Freight_Choices_Input
17 ( p_header_id IN NUMBER
18 ,p_x_fte_source_line_tab IN OUT NOCOPY
19 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
20 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
21 ,p_action IN VARCHAR2
22 ,x_config_count OUT NOCOPY /* file.sql.39 change */ NUMBER
23 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
24
25
26 PROCEDURE Print_Time(p_msg IN VARCHAR2)
27 IS
28 l_time VARCHAR2(100);
29 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30 BEGIN
31 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
32 'DD-MON-YY HH24:MI:SS');
33 IF l_debug_level > 0 THEN
34 OE_DEBUG_PUB.Add(p_msg || ': '|| l_time, 1);
35 END IF;
36 END Print_Time;
37
38
39 /*--------------------------------------------------------------+
40 Name : Prepare_Freight_Choices_Input
41 Parameters : IN OUT NOCOPY p_x_line_tbl
42 IN OUT NOCOPY p_x_fte_source_line_tab
43 IN p_header_id
44 IN p_line_id
45 IN p_action
46 OUT NOCOPY x_return_status
47 OUT NOCOPY x_config_count
48
49 Description : This Procedure prepares FTE input table to be
50 passed to FTE. This has two cursors. The
51 first Cursor is used to select all the attrib
52 to be passed to FTE. The second cursor is
53 is used to select all the included item
54 parents to process the included items.
55
56 we process the included items if they are not
57 frozen already. We will exclude all the non
58 eligible lines and prepate the fte input tab
59 at the same time we will also insert the
60 elgible lines in the oe_order_pub line table
61 to be used later.
62 Now we have line table with all the lines
63 beginnning with config lines.
64
65 The Same Procedure will be used by freight
66 rating also to prepare the Fte input table.
67 All non shippable lines not part of ATO
68 configuration should be marked as not
69 eligible for freight rate calculations.
70
71 This procedure is called from Process_FTE_Actions
72 API and p_action paramter can not have a NULL value.
73
74 Change Record :
75
76 +--------------------------------------------------------------*/
77
78 PROCEDURE Prepare_Freight_Choices_Input
79 ( p_header_id IN NUMBER
80 ,p_x_fte_source_line_tab IN OUT NOCOPY
81 FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab
82 ,p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
83 ,p_action IN VARCHAR2
84 ,x_config_count OUT NOCOPY /* file.sql.39 change */ NUMBER
85 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
86
87 IS
88 CURSOR C_SHIP_METHOD_LINES IS
89 SELECT line_id
90 ,ship_from_org_id
91 ,ship_to_org_id
92 ,sold_to_org_id
93 ,inventory_item_id
94 ,ordered_quantity
95 ,order_quantity_uom
96 ,request_date
97 ,schedule_ship_date
98 ,schedule_arrival_date
99 ,delivery_lead_time
100 ,DECODE(schedule_status_code,NULL,'N',
101 'SCHEDULED','Y','N') scheduled_flag
102 ,ship_set_id
103 ,arrival_set_id
104 ,ship_model_complete_flag
105 ,ato_line_id
106 ,top_model_line_id
107 ,shipping_method_code
108 ,freight_carrier_code
109 ,freight_terms_code
110 ,intmed_ship_to_org_id
111 ,fob_point_code
112 ,source_type_code
113 ,line_category_code
114 ,item_type_code
115 ,shipped_quantity
116 ,NVL(fulfilled_flag,'N') fulfilled_flag
117 ,open_flag
118 ,nvl(shippable_flag, 'N') shippable_flag
119 ,order_source_id
120 ,orig_sys_document_ref
121 ,orig_sys_line_ref
122 ,orig_sys_shipment_ref
123 ,change_sequence
124 ,source_document_type_id
125 ,source_document_id
126 ,source_document_line_id
127 FROM oe_order_lines_all
128 WHERE header_id = p_header_id
129 AND p_action <> 'R'
130 ORDER BY top_model_line_id, ato_line_id, sort_order;
131
132
133 CURSOR C_INC_ITEMS_PARENT IS
134 SELECT line_id
135 FROM oe_order_lines_all
136 WHERE item_type_code IN ('MODEL', 'CLASS', 'KIT')
137 AND ato_line_id is NULL
138 AND explosion_date is NULL
139 AND NVL(fulfilled_flag,'N') <> 'Y'
140 AND open_flag = 'Y'
141 AND shipped_quantity IS NULL
142 AND source_type_code = 'INTERNAL'
143 AND header_id = p_header_id;
144
145 l_open_flag VARCHAR2(1);
146 l_order_category_code VARCHAR2(30);
147 l_line_count NUMBER := 0;
148 l_config_count NUMBER := 0;
149 l_count NUMBER := 0;
150 l_transactional_curr_code VARCHAR2(15);
151 l_conversion_type_code VARCHAR2(30);
152 l_line_rec OE_ORDER_PUB.line_rec_type :=
153 OE_Order_Pub.G_MISS_LINE_REC;
154 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
155 BEGIN
156 Print_Time('Entering OE_FREIGHT_CHOICES_PVT.Prepare_Freight_Choices_Input...');
157
158 IF l_debug_level > 0 THEN
159 OE_DEBUG_PUB.Add('Order header Id:'||p_header_id,1);
160 END IF;
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 -- Validating Order
164 -- transactional curr code is also selected
165 BEGIN
166 SELECT open_flag,order_category_code,transactional_curr_code,conversion_type_code
167 INTO l_open_flag,l_order_category_code,l_transactional_curr_code,l_conversion_type_code
168 FROM oe_order_headers_all
169 WHERE header_id = p_header_id;
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 IF l_debug_level > 0 THEN
173 OE_DEBUG_PUB.Add('No Data Found when Validating Order',3);
174 END IF;
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 WHEN OTHERS THEN
177 IF l_debug_level > 0 THEN
178 OE_DEBUG_PUB.Add('When Others when Validating Order',3);
179 END IF;
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END;
182
183 IF l_open_flag = 'N' OR l_order_category_code = 'RETURN' THEN
184 IF l_debug_level > 0 THEN
185 OE_DEBUG_PUB.Add('Order is Return/Closed',1);
186 END IF;
187 RAISE FND_API.G_EXC_ERROR ;
188 END IF;
189
190
191 -- We need to create the included items if not already frozen.
192 -- Loop through the cursor Inc Item Parents to get the parent
193 -- lines of included items.
194
195 FOR c_inc_parent IN C_INC_ITEMS_PARENT
196 LOOP
197 IF l_debug_level > 0 THEN
198 OE_DEBUG_PUB.Add('Calling Process Included Items for Line:'||
199 c_inc_parent.line_id,3);
200 END IF;
201
202 x_return_status := OE_CONFIG_UTIL.Process_Included_Items
203 (p_line_id => c_inc_parent.line_id
204 ,p_freeze => FALSE);
205
206 IF l_debug_level > 0 THEN
207 OE_DEBUG_PUB.Add('After Calling Process Included Items: '||
208 x_return_status,3);
209 END IF;
210
211 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
214 RAISE FND_API.G_EXC_ERROR;
215 END IF;
216
217 END LOOP;
218
219
220 -- Query the Config lines and Put them in the line table
221 -- We are putting config lines in the line table in the
222 -- beginning. When traversing through the line table for
223 -- config lines it would be easy to identify them as they will
224 -- be in the beginning. We will insert all other lines in the
225 -- table next to config lines.
226 -- Now number of Config lines in the table
227
228 SELECT count(*)
229 INTO l_config_count
230 FROM oe_order_lines_all
231 WHERE item_type_code = 'CONFIG'
232 AND ato_line_id IS NOT NULL
233 AND header_id = p_header_id;
234
235 IF l_debug_level > 0 THEN
236 OE_DEBUG_PUB.Add('Config Line Count:'||l_config_count,3);
237 END IF;
238
239 x_config_count := l_config_count;
240 l_line_count := l_config_count;
241
242
243 -- Reset the Config Count Variable
244 l_config_count := 0;
245
246 -- Loop through the cursor Ship Method Lines and exclude all
247 -- Non eligible Lines.
248
249 FOR c_ship_method IN C_SHIP_METHOD_LINES
250 LOOP
251 -- We need to set the Message Context for each line.
252
253 OE_Msg_Pub.Set_Msg_Context
254 ( p_entity_code => OE_GLOBALS.G_ENTITY_LINE
255 ,p_entity_id => c_ship_method.line_id
256 ,p_header_id => p_header_id
257 ,p_line_id => c_ship_method.line_id
258 ,p_order_source_id => c_ship_method.order_source_id
259 ,p_orig_sys_document_ref => c_ship_method.orig_sys_document_ref
260 ,p_orig_sys_document_line_ref => c_ship_method.orig_sys_line_ref
261 ,p_orig_sys_shipment_ref => c_ship_method.orig_sys_shipment_ref
262 ,p_change_sequence => c_ship_method.change_sequence
263 ,p_source_document_type_id => c_ship_method.source_document_type_id
264 ,p_source_document_id => c_ship_method.source_document_id
265 ,p_source_document_line_id => c_ship_method.source_document_line_id);
266
267 -- If the line is sourced Externally the line is
268 -- not eligible
269
270 IF c_ship_method.source_type_code = 'EXTERNAL' OR
271 c_ship_method.item_type_code = 'SERVICE' OR
272 c_ship_method.line_category_code = 'RETURN' THEN
273
274 FND_MESSAGE.Set_Name('ONT','ONT_FTE_EXTERNAL_RET_SERVICE');
275 OE_MSG_PUB.Add;
276
277 IF l_debug_level > 0 THEN
278 OE_DEBUG_PUB.Add('Line is External/Service/Return:'||
279 c_ship_method.line_id ,3);
280 END IF;
281
282 ELSIF c_ship_method.ship_from_org_id IS NULL THEN
283
284 FND_MESSAGE.Set_Name('ONT','ONT_FTE_MISSING_SHIP_FROM');
285 OE_MSG_PUB.Add;
286 IF l_debug_level > 0 THEN
287 OE_DEBUG_PUB.Add('Line is Missing Ship From Org Id:'||
288 c_ship_method.line_id ,3);
289 END IF;
290
291 ELSIF c_ship_method.ship_to_org_id IS NULL THEN
292
293 FND_MESSAGE.Set_Name('ONT','ONT_FTE_MISSING_SHIP_TO');
294 OE_MSG_PUB.Add;
295 IF l_debug_level > 0 THEN
296 OE_DEBUG_PUB.Add('Line is Missing Ship To Org Id:'||
297 c_ship_method.line_id ,3);
298 END IF;
299
300 ELSIF c_ship_method.shipped_quantity IS NOT NULL OR
301 c_ship_method.fulfilled_flag = 'Y' OR
302 c_ship_method.open_flag <> 'Y' THEN
303
304 FND_MESSAGE.Set_Name('ONT','ONT_FTE_SHIP_FULFILL_CLOSED');
305 OE_MSG_PUB.Add;
306 IF l_debug_level > 0 THEN
307 OE_DEBUG_PUB.Add('Line is Shipped/Fulfilled/Closed:'||
308 c_ship_method.line_id ,1);
309 END IF;
310 ELSIF c_ship_method.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
311
312 -- We are not going to send Config lines to FTE
313 -- We have to store the config lines in the line table.
314 IF l_debug_level > 0 THEN
315 OE_DEBUG_PUB.Add('Config Line:'||c_ship_method.line_id,3);
316 END IF;
317
318 l_line_rec.line_id := c_ship_method.line_id;
319 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
320 l_line_rec.item_type_code := c_ship_method.item_type_code;
321 l_line_rec.shipping_method_code :=
322 c_ship_method.shipping_method_code;
323 l_line_rec.freight_terms_code :=
324 c_ship_method.freight_terms_code;
325
326 l_config_count := l_config_count + 1;
327
328 p_x_line_tbl(l_config_count) := l_line_rec;
329 IF l_debug_level > 0 THEN
330 OE_DEBUG_PUB.Add('Excluding Config Line from FTE Input:'||
331 c_ship_method.line_id ,1);
332 END IF;
333 ELSE
334 IF l_debug_level > 0 THEN
335 OE_DEBUG_PUB.Add('Sending Eligibile Line to FTE:'||
336 c_ship_method.line_id ,1);
337 END IF;
338
339 -- Increment the FTE line table count
340
341 l_count := l_count + 1;
342
343 p_x_fte_source_line_tab(l_count).source_type := 'ONT';
344
345 p_x_fte_source_line_tab(l_count).source_header_id :=
346 p_header_id;
347
348 p_x_fte_source_line_tab(l_count).source_line_id :=
349 c_ship_method.line_id;
350
351 p_x_fte_source_line_tab(l_count).ship_from_org_id :=
352 c_ship_method.ship_from_org_id;
353
354 p_x_fte_source_line_tab(l_count).ship_to_site_id :=
355 c_ship_method.ship_to_org_id;
356
357
358 p_x_fte_source_line_tab(l_count).customer_id :=
359 c_ship_method.sold_to_org_id;
360
361 p_x_fte_source_line_tab(l_count).inventory_item_id :=
362 c_ship_method.inventory_item_id;
363
364 p_x_fte_source_line_tab(l_count).source_quantity :=
365 c_ship_method.ordered_quantity;
366
367 p_x_fte_source_line_tab(l_count).source_quantity_uom:=
368 c_ship_method.order_quantity_uom;
369
370 p_x_fte_source_line_tab(l_count).ship_date :=
371 NVL(c_ship_method.schedule_ship_date
372 , NVL(c_ship_method.request_date,sysdate));
373
374 p_x_fte_source_line_tab(l_count).arrival_date :=
375 c_ship_method.schedule_arrival_date;
376
377 p_x_fte_source_line_tab(l_count).currency :=
378 l_transactional_curr_code;
379
380 p_x_fte_source_line_tab(l_count).currency_conversion_type :=
381 l_conversion_type_code;
382
383 IF c_ship_method.scheduled_flag = 'N' THEN
384 p_x_fte_source_line_tab(l_count).delivery_lead_time :=0;
385
386 ELSE
387 p_x_fte_source_line_tab(l_count).delivery_lead_time :=
388 c_ship_method.delivery_lead_time;
389
390 END IF;
391
392 p_x_fte_source_line_tab(l_count).scheduled_flag :=
393 c_ship_method.scheduled_flag;
394
395
396 IF c_ship_method.arrival_set_id IS NOT NULL THEN
397
398 p_x_fte_source_line_tab(l_count).order_set_type :=
399 'ARRIVAL';
400
401 p_x_fte_source_line_tab(l_count).order_set_id :=
402 c_ship_method.arrival_set_id;
403
404 ELSIF c_ship_method.ship_set_id IS NOT NULL THEN
405
406 p_x_fte_source_line_tab(l_count).order_set_type :=
407 'SHIP';
408
409 p_x_fte_source_line_tab(l_count).order_set_id :=
410 c_ship_method.ship_set_id;
411
412 ELSIF c_ship_method.top_model_line_id IS NOT NULL AND
413 c_ship_method.ship_model_complete_flag = 'Y' THEN
414
415 p_x_fte_source_line_tab(l_count).order_set_type := 'SMC';
416
417 p_x_fte_source_line_tab(l_count).order_set_id :=
418 c_ship_method.top_model_line_id;
419
420 ELSIF c_ship_method.ato_line_id IS NOT NULL THEN
421
422 p_x_fte_source_line_tab(l_count).order_set_type := 'ATO';
423
424 p_x_fte_source_line_tab(l_count).order_set_id :=
425 c_ship_method.ato_line_id;
426 ELSE
427 p_x_fte_source_line_tab(l_count).order_set_type := NULL;
428
429 p_x_fte_source_line_tab(l_count).order_set_id := NULL;
430
431 END IF;
432
433 p_x_fte_source_line_tab(l_count).carrier_id := NULL;
434
435 p_x_fte_source_line_tab(l_count).ship_method_code :=
436 c_ship_method.shipping_method_code;
437
438 p_x_fte_source_line_tab(l_count).freight_terms :=
439 c_ship_method.freight_terms_code;
440
441 p_x_fte_source_line_tab(l_count).fob_code :=
442 c_ship_method.fob_point_code;
443
444
445 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id :=
446 c_ship_method.intmed_ship_to_org_id;
447
448 p_x_fte_source_line_tab(l_count).ship_method_flag := 'Y';
449 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'Y';
450 p_x_fte_source_line_tab(l_count).override_ship_method := 'Y';
451
452 IF c_ship_method.shippable_flag = 'N' THEN
453
454 IF c_ship_method.ato_line_id is not NULL AND
455 NOT ((c_ship_method.item_type_code = 'OPTION' OR
456 c_ship_method.item_type_code = 'STANDARD') AND
457 c_ship_method.ato_line_id = c_ship_method.line_id)
458 THEN
459 IF l_debug_level > 0 THEN
460 OE_DEBUG_PUB.Add('calculate rating part of ato', 4);
461 END IF;
462 ELSE
463 IF l_debug_level > 0 THEN
464 OE_DEBUG_PUB.Add('do not calculate freight_rating '||
465 c_ship_method.line_id, 1);
466 END IF;
467 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'N';
468 END IF;
469
470 END IF;
471
472 IF l_debug_level > 0 THEN
473 oe_debug_pub.Add('--------- Input to FTE --------',3);
474
475 oe_debug_pub.Add('Source Line :'||
476 p_x_fte_source_line_tab(l_count).source_line_id,3);
477
478 oe_debug_pub.Add('Ship From Org :'||
479 p_x_fte_source_line_tab(l_count).ship_from_org_id,3);
480
481 oe_debug_pub.Add('Customer :'||
482 p_x_fte_source_line_tab(l_count).customer_id,3);
483
484 oe_debug_pub.Add('Inventory Item :'||
485 p_x_fte_source_line_tab(l_count).inventory_item_id,3);
486
487 oe_debug_pub.Add('Source Quantity :'||
488 p_x_fte_source_line_tab(l_count).source_quantity,3);
489
490 oe_debug_pub.Add('Ship Date :'||
491 p_x_fte_source_line_tab(l_count).ship_date,3);
492
493 oe_debug_pub.Add('Delivery Lead Time :'||
494 p_x_fte_source_line_tab(l_count).delivery_lead_time,3);
495
496 oe_debug_pub.Add('Scheduled :'||
497 p_x_fte_source_line_tab(l_count).scheduled_flag,3);
498
499 oe_debug_pub.Add('Order Set Type :'||
500 p_x_fte_source_line_tab(l_count).order_set_type,3);
501
502 oe_debug_pub.Add('Order Set :'||
503 p_x_fte_source_line_tab(l_count).order_set_id,3);
504
505 oe_debug_pub.Add('Ship Method :'||
506 p_x_fte_source_line_tab(l_count).ship_method_code,3);
507
508 oe_debug_pub.Add('Freight Terms :'||
509 p_x_fte_source_line_tab(l_count).freight_terms,3);
510
511 oe_debug_pub.Add('Freight on Board :'||
512 p_x_fte_source_line_tab(l_count).fob_code,3);
513
514 oe_debug_pub.Add('Intermediate Ship :'||
515 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id,3);
516
517 oe_debug_pub.Add('Transactional Currency :'||
518 p_x_fte_source_line_tab(l_count).currency,3);
519
520 oe_debug_pub.Add('-------------------------------',3);
521 END IF;
522
523 -- Store the old ship method code in the line table
524
525 l_line_rec.line_id := c_ship_method.line_id;
526 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
527 l_line_rec.item_type_code := c_ship_method.item_type_code;
528 l_line_rec.shipping_method_code :=
529 c_ship_method.shipping_method_code;
530 l_line_rec.freight_terms_code :=
531 c_ship_method.freight_terms_code;
532 l_line_rec.freight_carrier_code :=
533 c_ship_method.freight_carrier_code;
534
535 l_line_count := l_line_count + 1;
536 p_x_line_tbl(l_line_count) := l_line_rec;
537
538 END IF;
539
540 G_Ship_Date_tbl(c_ship_method.line_id).Schedule_Ship_Date := c_ship_method.Schedule_Ship_Date;
541 G_Ship_Date_tbl(c_ship_method.line_id).Line_id := c_ship_method.line_id;
542 END LOOP;
543
544 -- this check is added to show the message when no lines of the order are eligible for freight rating
545
546 IF p_x_line_tbl.count = 0 THEN
547
548 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NO_LINES_ELIGIBLE');
549 OE_MSG_PUB.Add;
550 IF l_debug_level > 0 THEN
551 OE_DEBUG_PUB.Add('No lines of the order are eligible for freight rating');
552 END IF; -- bug 7433107
553 --l_return_status = FND_API.G_RET_STS_ERROR;
554 RAISE FND_API.G_EXC_ERROR;
555 -- END IF; -- bug 7433107
556 END IF;
557
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.Add('FTE Input count:'||p_x_fte_source_line_tab.count,3);
560 oe_debug_pub.Add('Total count:'|| p_x_line_tbl.count,3);
561 END IF;
562
563 for i in G_Ship_Date_tbl.first..G_Ship_Date_tbl.last loop
564 if G_Ship_Date_tbl.exists(i) Then
565 oe_debug_pub.Add('Shedule Ship Date Value .. '||G_Ship_Date_tbl(i).Schedule_Ship_Date);
566 end if;
567 end loop;
568
569 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Prepare_Freight_Choices_Input...');
570 EXCEPTION
571
572 WHEN FND_API.G_EXC_ERROR THEN
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.Add('Expected Error in Create FTE Input',2);
575 END IF;
576
577 x_return_status := FND_API.G_RET_STS_ERROR;
578
579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.Add('Unexpected Error in Create FTE Input:'||SqlErrm, 1);
582 END IF;
583
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585
586
587 WHEN OTHERS THEN
588
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590
591 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
592 THEN
593 OE_MSG_PUB.Add_Exc_Msg
594 ( G_PKG_NAME,
595 'Prepare_Freight_Choices_Input');
596 END IF;
597
598 END Prepare_Freight_Choices_Input;
599
600 PROCEDURE Get_Shipment_Summary
601 (p_header_id IN NUMBER,
602 x_shipment_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
603 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
604 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
605 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
606
607 IS
608 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
609 l_fte_source_line_tab FTE_PROCESS_REQUESTS.fte_source_line_tab;
610 l_fte_source_header_tab FTE_PROCESS_REQUESTS.fte_source_header_tab;
611 l_fte_line_rates_tab FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
612 l_fte_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
613 --l_rating_parameters_tab FTE_PROCESS_REQUESTS.fte_rating_parameters_tab;
614 l_return_status VARCHAR2(1);
615 l_msg_data VARCHAR2(2000);
616 l_msg_count NUMBER;
617 l_config_count NUMBER;
618 i NUMBER;
619 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
620 l_msg_text VARCHAR2(2000);
621
622 BEGIN
623
624 oe_debug_pub.add(' Entering the procedure Get_shipment_Summary');
625
626 x_shipment_count := 0;
627
628 -- Prepare the input information for FTE.
629 --OE_FTE_INTEGRATION_PVT.Create_FTE_Input
630 Prepare_Freight_Choices_Input
631 ( p_header_id => p_header_id
632 --,p_line_id => NULL
633 ,p_x_fte_source_line_tab => l_fte_source_line_tab
634 ,p_x_line_tbl => l_line_tbl
635 ,p_action => 'X'
636 ,x_config_count => l_config_count
637 ,x_return_status => l_return_status
638 );
639
640 x_return_status := l_return_status;
641
642 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
645 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
646 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
647 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
648 IF l_debug_level > 0 THEN
649 oe_debug_pub.Add(l_msg_text, 3);
650 END IF;
651 oe_msg_pub.add_text(p_message_text => l_msg_text);
652 END LOOP;
653 END IF;
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656
657 --x_shipment_count := 15;
658
659
660 -- Call FTE to get group info only if the number of lines is greater than zero
661 IF l_fte_source_line_tab.Count > 0 THEN
662
663 Print_Time('Calling FTE for Get_Group ... ');
664 FTE_PROCESS_REQUESTS.Process_Lines(
665 p_source_line_tab => l_fte_source_line_tab,
666 p_source_header_tab => l_fte_source_header_tab,
667 p_source_type => 'ONT',
668 p_action => 'GET_GROUP', -- to get group info
669 --p_rating_parameters_tab => l_rating_parameters_tab,
670 x_source_line_rates_tab => l_fte_line_rates_tab,
671 x_source_header_rates_tab=> l_fte_header_rates_tab,
672 x_return_status => l_return_status,
673 x_msg_count => l_msg_count,
674 x_msg_data => l_msg_data);
675
676 x_return_status := l_return_status;
677 Print_Time('After Calling FTE for Get_Group ... ');
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.Add('After Calling FTE Process Lines:'||
680 l_return_status,3);
681 --x_shipment_count := 15;
682 END IF;
683
684 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
687 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
688 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
689 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
690 IF l_debug_level > 0 THEN
691 oe_debug_pub.Add(l_msg_text, 3);
692 END IF;
693 oe_msg_pub.add_text(p_message_text => l_msg_text);
694 END LOOP;
695 END IF;
696 RAISE FND_API.G_EXC_ERROR;
697 END IF;
698
699 g_line_tbl := l_line_tbl;
700 g_fte_source_line_tab := l_fte_source_line_tab;
701 g_fte_source_header_tab := l_fte_source_header_tab;
702
703 oe_debug_pub.Add('header tab count is : ' || l_fte_source_header_tab.Count);
704 oe_debug_pub.Add('line tab count is : ' || l_fte_source_line_tab.Count);
705
706
707 g_config_count := l_config_count;
708
709 -- populate the global table with FTE results.
710
711 --Get the ship_from,ship_to,total_weight,total_volume,freight_terms,scheduled_ship_date for the line from l_fte_header_source_tab
712 For i IN l_fte_source_header_tab.FIRST .. l_fte_source_header_tab.LAST LOOP
713 g_shipment_summary_tbl(i).consolidation_id := l_fte_source_header_tab(i).consolidation_id;
714 g_shipment_summary_tbl(i).ship_from := l_fte_source_header_tab(i).Ship_from_Org_Id;
715 g_shipment_summary_tbl(i).ship_to := l_fte_source_header_tab(i).Ship_to_site_Id;
716 g_shipment_summary_tbl(i).total_weight := l_fte_source_header_tab(i).total_weight;
717 g_shipment_summary_tbl(i).weight_uom := l_fte_source_header_tab(i).weight_uom_code;
718 g_shipment_summary_tbl(i).total_volume := l_fte_source_header_tab(i).total_volume;
719 g_shipment_summary_tbl(i).volume_uom := l_fte_source_header_tab(i).volume_uom_code;
720 g_shipment_summary_tbl(i).freight_terms := l_fte_source_header_tab(i).freight_terms;
721
722 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
723 IF g_fte_source_line_tab(j).consolidation_id = l_fte_source_header_tab(i).consolidation_id then
724 If G_Ship_Date_tbl.exists(g_fte_source_line_tab(j).source_line_id) Then
725 g_shipment_summary_tbl(i).scheduled_ship_date := G_Ship_Date_tbl(g_fte_source_line_tab(j).source_line_id).Schedule_Ship_Date;
726 End If;
727 End If;
728 End Loop;
729
730 END LOOP;
731
732 x_shipment_count := g_shipment_summary_tbl.count;
733
734 End If; -- l_fte_source_line_tab.Count > 0
735 --oe_debug_pub.add('Before print time');
736 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Summary...');
737 oe_debug_pub.add('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Summary');
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742 IF l_debug_level > 0 THEN
743 oe_debug_pub.Add('Expected Error in Get Shipment Summary',2);
744 END IF;
745
746
747 END Get_Shipment_Summary;
748
749 PROCEDURE Get_Shipment_Summary_Tbl
750 (x_shipment_summary_tbl IN OUT NOCOPY /* file.sql.39 change */ shipment_summary_tbl_type)
751 IS
752
753 BEGIN
754
755 oe_debug_pub.add('Entering Get shipment summary tbl');
756 oe_debug_pub.add('count:'||g_shipment_summary_tbl.count);
757
758 IF g_shipment_summary_tbl.count >0 THEN
759 For I IN g_shipment_summary_tbl.FIRST .. g_shipment_summary_tbl.LAST LOOP
760 x_shipment_summary_tbl(i).consolidation_id := g_shipment_summary_tbl(i).consolidation_id;
761 x_shipment_summary_tbl(i).ship_from := g_shipment_summary_tbl(i).ship_from;
762 x_shipment_summary_tbl(i).ship_to := g_shipment_summary_tbl(i).ship_to;
763 x_shipment_summary_tbl(i).total_weight := g_shipment_summary_tbl(i).total_weight;
764 x_shipment_summary_tbl(i).weight_uom := g_shipment_summary_tbl(i).weight_uom;
765 x_shipment_summary_tbl(i).total_volume := g_shipment_summary_tbl(i).total_volume;
766 x_shipment_summary_tbl(i).volume_uom := g_shipment_summary_tbl(i).volume_uom;
767 x_shipment_summary_tbl(i).freight_terms := g_shipment_summary_tbl(i).freight_terms;
768 x_shipment_summary_tbl(i).scheduled_ship_date := g_shipment_summary_tbl(i).scheduled_ship_date;
769 END LOOP;
770 END IF;
771
772 oe_debug_pub.add(' exiting the get_shipment_summary_tbl proc');
773
774 END Get_Shipment_Summary_Tbl;
775
776 PROCEDURE Prepare_Adj_Detail
777 ( p_header_id IN NUMBER
778 ,p_line_id IN NUMBER
779 ,p_fte_rates_rec IN FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec
780 ,x_line_adj_rec OUT NOCOPY OE_Order_PUB.Line_Adj_Rec_Type
781 ,x_return_status OUT NOCOPY VARCHAR2
782
783 ) IS
784
785 l_price_adjustment_id number := 0;
786
787 --
788 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
789 --
790 BEGIN
791
792 IF l_debug_level > 0 THEN
793 oe_debug_pub.add( 'ENTERING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
794 END IF;
795 x_return_status := FND_API.G_RET_STS_SUCCESS;
796
797 select oe_price_adjustments_s.nextval into l_price_adjustment_id
798 from dual;
799
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add( 'PRICE ADJUSTMENT ID IN PREPARE_ADJ_DETAIL IS: ' ||L_PRICE_ADJUSTMENT_ID , 1 ) ;
802 oe_debug_pub.add( 'LINE_ID IN PREPARE_ADJ_DETAIL IS: ' ||P_LINE_ID , 1 ) ;
803 END IF;
804
805 x_line_adj_rec.header_id := p_header_id;
806 x_line_adj_rec.line_id := p_line_id;
807 x_line_adj_rec.price_adjustment_id := l_price_adjustment_id;
808 x_line_adj_rec.creation_date := sysdate;
809 x_line_adj_rec.last_update_date := sysdate;
810 x_line_adj_rec.created_by := 1;
811 x_line_adj_rec.last_updated_by := 1;
812 x_line_adj_rec.last_update_login := 1;
813
814 x_line_adj_rec.automatic_flag := 'Y';
815 x_line_adj_rec.adjusted_amount := p_fte_rates_rec.adjusted_price;
816 oe_debug_pub.add('value of cost ' ||p_fte_rates_rec.adjusted_price);
817 x_line_adj_rec.charge_type_code := p_fte_rates_rec.cost_type;
818 oe_debug_pub.add('value of cost_type '||p_fte_rates_rec.cost_type);
819 x_line_adj_rec.list_line_type_code := 'COST';
820 x_line_adj_rec.estimated_flag := 'Y';
821 x_line_adj_rec.source_system_code := 'FTE';
822
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add( 'EXITING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
825 END IF;
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 IF l_debug_level > 0 THEN
831 oe_debug_pub.add( 'ERROR IN PROCEDURE PREPARE_ADJ_DETAIL: '||SUBSTR ( SQLERRM , 1 , 240 ) , 3 ) ;
832 END IF;
833 IF l_debug_level > 0 THEN
834 oe_debug_pub.add( 'UNEXPECTED ERROR IN PREPRARE_ADJ_DETAIL :'||SQLERRM , 3 ) ;
835 END IF;
836 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
837 THEN
838 OE_MSG_PUB.Add_Exc_Msg
839 ( G_PKG_NAME,
840 'Prepare_Adj_Detail');
841 END IF;
842 END Prepare_Adj_Detail;
843
844
845 PROCEDURE Create_Dummy_Adjustment(p_header_id in number
846 ) IS
847 l_price_adjustment_id number := -1;
848 --
849 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
850 --
851 BEGIN
852
853 IF p_header_id is not null THEN
854
855 select oe_price_adjustments_s.nextval
856 into l_price_adjustment_id
857 from dual;
858
859 INSERT INTO oe_price_adjustments
860 (PRICE_ADJUSTMENT_ID
861 ,HEADER_ID
862 ,LINE_ID
863 ,PRICING_PHASE_ID
864 ,LIST_LINE_TYPE_CODE
865 ,LIST_HEADER_ID
866 ,LIST_LINE_ID
867 ,ADJUSTED_AMOUNT
868 ,AUTOMATIC_FLAG
869 ,UPDATED_FLAG
870 ,APPLIED_FLAG
871 ,CREATION_DATE
872 ,CREATED_BY
873 ,LAST_UPDATE_DATE
874 ,LAST_UPDATED_BY
875 )
876 VALUES
877 (l_price_adjustment_id
878 ,p_header_id
879 ,NULL
880 ,-1
881 ,'OM_CALLED_CHOOSE_SHIP_METHOD'
882 ,-1*p_header_id
883 ,NULL
884 ,-1
885 ,'N'
886 ,'Y'
887 ,NULL
888 ,sysdate
889 ,1
890 ,sysdate
891 ,1
892 );
893
894 END IF;
895 END Create_Dummy_Adjustment;
896
897 Function Get_List_Line_Type_Code
898 ( p_key IN NUMBER)
899 RETURN Number
900 IS
901 l_count Number := 0;
902 Begin
903
904 Select Count(*) into l_count from
905 oe_price_adjustments
906 where header_id = p_key
907 and LIST_LINE_TYPE_CODE = 'OM_CALLED_CHOOSE_SHIP_METHOD';
908
909 Return l_count;
910
911 End;
912
913 PROCEDURE Get_Freight_Choices
914 ( p_consolidation_id IN NUMBER,
915 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
916 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
917 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
918 IS
919
920 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
921 l_fte_source_header_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Tab;
922 l_fte_line_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
923 l_fte_header_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_header_Rates_Tab;
924 l_price_control_rec OE_ORDER_PRICE_PVT.control_rec_type;
925 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
926 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
927 query_line_tbl OE_Order_PUB.Line_Tbl_Type;
928 l_config_count NUMBER;
929 l_no_opr_count NUMBER;
930 l_msg_text VARCHAR2(2000);
931 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
932 i NUMBER;
933 j NUMBER;
934 g_count Number := 0;
935 l_index NUMBER;
936 l_total_charges NUMBER := 0;
937 l_total_cost NUMBER := 0;
938 l_return_status VARCHAR2(10);
939 l_Adjusted_amount NUMBER := 0;
940 q_Adjusted_amount NUMBER := 0;
941 l_meaning VARCHAR2(50);
942 l_exists_flag BOOLEAN := FALSE;
943 l_cost_amount number := 0;
944 M NUMBER := 0;
945 l_next_index NUMBER := 0;
946 l_ship_method_code VARCHAR2(30);
947 l_source_line_id NUMBER := 0;
948 K NUMBER := 0;
949 l_lane_id NUMBER := 0;
950
951 deleted_costs number_type;
952
953 BEGIN
954
955 /* initialize g_freight_choices_tbl for this call */
956
957 oe_debug_pub.add( 'Entering Get Freight Choices procedure ');
958
959 g_freight_choices_tbl.DELETE;
960
961 For j in g_fte_source_header_tab.FIRST .. g_fte_source_header_tab.LAST LOOP
962 oe_debug_pub.add( ' value of g_fte_source_header_tab ' || g_fte_source_header_tab(j).consolidation_id);
963 End loop;
964
965 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
966 oe_debug_pub.add( ' value of g_fte_source_line_tab ' || g_fte_source_line_tab(j).consolidation_id);
967 End loop;
968
969 IF g_fte_source_header_tab(p_consolidation_id).consolidation_id = p_consolidation_id then
970 l_fte_source_header_tab(1) := g_fte_source_header_tab(p_consolidation_id);
971
972 END IF;
973
974 oe_debug_pub.add( ' Count 1');
975
976 i := 1;
977 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
978 IF g_fte_source_line_tab(j).consolidation_id = p_consolidation_id then
979 l_fte_source_line_tab(i) := g_fte_source_line_tab(j);
980 i := i+1;
981 End If;
982 END LOOP;
983
984 oe_debug_pub.add( ' Count 2');
985 /*
986 This is used to populate the return table for the shipment details block
987 in Get_Shipment_Details Procedure.
988 */
989 g_line_shipment_details_tbl := l_fte_source_line_tab;
990
991 -- Call FTE to get freight choices only if the number of lines is
992 -- greater than zero.
993 IF l_fte_source_line_tab.Count > 0 THEN
994
995 oe_debug_pub.Add('before process lines ');
996 oe_debug_pub.Add('source header id : ' || l_fte_source_line_tab(1).source_header_id);
997 Print_Time('Calling FTE for GET_RATE_CHOICE ... ');
998
999 FTE_PROCESS_REQUESTS.Process_Lines (
1000 p_source_line_tab => l_fte_source_line_tab,
1001 p_source_header_tab => l_fte_source_header_tab,
1002 p_source_type => 'ONT',
1003 p_action => 'GET_RATE_CHOICE', -- to get freight rates
1004 x_source_line_rates_tab => l_fte_line_rate_tab,
1005 x_source_header_rates_tab=> l_fte_header_rate_tab,
1006 x_return_status => l_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data);
1009
1010 Print_Time('After Calling FTE for GET_RATE_CHOICE ... ');
1011 IF l_debug_level > 0 THEN
1012 oe_debug_pub.Add('After Calling FTE Process Lines:'||
1013 l_return_status,3);
1014 END IF;
1015
1016 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1019 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
1020 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1021 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.Add(l_msg_text, 3);
1024 END IF;
1025 oe_msg_pub.add_text(p_message_text => l_msg_text);
1026 END LOOP;
1027 END IF;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031 g_line_shipment_details_tbl := l_fte_source_line_tab;
1032
1033 -- g_fte_source_line_tab := l_fte_source_line_tab;
1034 --g_fte_source_header_tab := l_fte_source_header_tab;
1035
1036 -- Bug 6186084
1037 For I IN l_fte_source_header_tab.FIRST .. l_fte_source_header_tab.LAST LOOP
1038 g_shipment_summary_tbl(p_consolidation_id).total_weight := l_fte_source_header_tab(i).total_weight;
1039 g_shipment_summary_tbl(p_consolidation_id).total_volume := l_fte_source_header_tab(i).total_volume;
1040 END LOOP;
1041
1042 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
1043 oe_debug_pub.add( ' value of g_fte_source_line_tab ' || g_fte_source_line_tab(j).consolidation_id);
1044 End loop;
1045
1046 g_fte_line_rate_tab := l_fte_line_rate_tab ;
1047 g_fte_header_rate_tab := l_fte_header_rate_tab;
1048
1049 l_index := 1;
1050
1051 I := l_fte_header_rate_tab.FIRST;
1052 j := 1;
1053 WHILE I IS NOT NULL LOOP
1054
1055 l_ship_method_code := l_fte_header_rate_tab(I).ship_method_code;
1056 l_lane_id := l_fte_header_rate_tab(I).lane_id;
1057
1058 IF l_debug_level > 0 THEN
1059 oe_debug_pub.add( '============ FTE RESULTS ============' , 3 ) ;
1060 END IF;
1061
1062 M := l_fte_header_rate_tab(I).first_line_index;
1063 l_source_line_id := l_fte_line_rate_tab(M).source_line_id;
1064 K := 0;
1065
1066 While M is not null loop
1067
1068 oe_debug_pub.add('source line id : ' || l_fte_line_rate_tab(M).source_line_id);
1069
1070 oe_debug_pub.add('ship method is: ' || l_fte_line_rate_tab(M).ship_method_code);
1071
1072 IF l_fte_line_rate_tab(M).ship_method_code = l_ship_method_code
1073 and l_fte_line_rate_tab(M).lane_id = l_lane_id
1074 and l_fte_line_rate_tab(M).consolidation_id = p_consolidation_id
1075 THEN
1076
1077 Prepare_Adj_Detail
1078 (p_header_id => l_fte_source_line_tab(1).source_header_id
1079 ,p_line_id => l_fte_line_rate_tab(M).source_line_id
1080 ,p_fte_rates_rec => l_fte_line_rate_tab(M)
1081 ,x_line_adj_rec => l_line_adj_rec
1082 ,x_return_status => l_return_status
1083 );
1084
1085 oe_debug_pub.add('104');
1086
1087 IF deleted_costs.EXISTS(l_fte_line_rate_tab(M).source_line_id) THEN
1088 NULL;
1089 ELSE
1090 DELETE FROM OE_PRICE_ADJUSTMENTS
1091 WHERE line_ID = l_fte_line_rate_tab(M).source_line_id
1092 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
1093 AND list_line_type_code = 'COST'
1094 AND ESTIMATED_FLAG = 'Y';
1095
1096 deleted_costs(l_fte_line_rate_tab(M).source_line_id) := l_fte_line_rate_tab(M).source_line_id;
1097
1098 oe_line_util.query_rows
1099 (p_line_id => l_fte_line_rate_tab(M).source_line_id
1100 ,x_line_tbl => query_line_tbl );
1101
1102 K := K + 1;
1103
1104 l_line_tbl(K) := query_line_tbl(1);
1105
1106 END IF; -- if deleted_costs...
1107
1108 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1109
1110 ELSE
1111 EXIT; -- We can exit the loop since l_fte_line_rate_tab is grouped
1112 -- according to lane_id, ship_method_code and consolidation_id
1113
1114 END IF; --if l_fte_line_rate_tab(M).ship_method_code = l_ship_method_code
1115
1116 M := l_fte_line_rate_tab.NEXT(M);
1117
1118 oe_debug_pub.add('105');
1119
1120 END LOOP; --While M is not null loop
1121
1122 deleted_costs.DELETE;
1123
1124 -- Calling Pricing Engine to calculate freight charges
1125 -- if being called from Action button.
1126
1127 oe_debug_pub.add('107');
1128
1129 /*
1130 Call Pricing to do cost to charge conversion with simulation mode for this order line and
1131 for this ship method combination.
1132 populate the simulate_flag to 'Y' and populate the freight_charge_flag to 'Y' in control record,
1133 Also set the write_to_db to false.
1134 Freight_Choices_Tab should store all the applicable freight charges for this particular order line.
1135 */
1136 oe_debug_pub.add('108');
1137
1138 l_price_control_rec.p_Request_Type_Code:='ONT';
1139 l_Price_control_rec.p_write_to_db:=FALSE;
1140 l_price_control_rec.p_honor_price_flag:='Y';
1141 l_price_control_rec.p_multiple_events:='N';
1142 l_price_control_rec.p_get_freight_flag:='Y';
1143 l_price_control_rec.p_simulation_flag := 'Y';
1144
1145 oe_debug_pub.add('109');
1146
1147 IF l_line_tbl.count > 0 THEN
1148 oe_order_price_pvt.price_line
1149 (p_Header_id => null
1150 ,p_Line_id => null
1151 ,px_line_Tbl => l_line_tbl
1152 ,p_Control_Rec => l_price_control_rec
1153 ,p_action_code => 'PRICE_LINE'
1154 ,p_Pricing_Events => 'BATCH'
1155 ,x_Return_Status => l_return_status
1156 );
1157
1158 oe_debug_pub.add('110');
1159 K := 1;
1160 K := l_line_tbl.FIRST;
1161 WHILE K IS NOT NULL LOOP
1162
1163 Select sum(nvl(l.ADJUSTMENT_AMOUNT,0)) into q_Adjusted_amount from QP_ldets_v l,QP_preq_lines_tmp q
1164 where l.line_index = q.line_index
1165 and q.line_id = l_line_tbl(K).line_id
1166 and q.line_type_code = 'LINE'
1167 AND nvl(l.automatic_flag,'N') = 'Y'
1168 AND l.list_line_type_code = 'FREIGHT_CHARGE';
1169
1170 oe_debug_pub.add('value of ordered qty : '||nvl(l_line_tbl(K).ordered_quantity,0));
1171
1172 -- Modified for bug # 7043225
1173 -- bug 6701769/6753485
1174 --l_Adjusted_amount := l_Adjusted_amount + nvl(l_line_tbl(K).ordered_quantity,0)*q_Adjusted_amount;
1175 l_Adjusted_amount := l_Adjusted_amount + (nvl(nvl(l_line_tbl(K).pricing_quantity,l_line_tbl(K).ordered_quantity),0)*nvl(q_Adjusted_amount,0));
1176
1177 oe_debug_pub.add('6701769 value of l_Adjusted_amount : '||l_Adjusted_amount);
1178
1179 K := l_line_tbl.NEXT(K);
1180
1181 END LOOP; -- while k is not null
1182
1183 K := 0;
1184
1185 oe_debug_pub.add('value of charges : '||l_Adjusted_amount);
1186
1187 --oe_debug_pub.add('value of ordered qty : '||nvl(l_line_tbl(K).ordered_quantity,0));
1188
1189 Select MEANING into l_meaning from oe_ship_methods_v
1190 where LOOKUP_CODE= l_fte_header_rate_tab(I).ship_method_code
1191 and LOOKUP_TYPE='SHIP_METHOD';
1192
1193
1194 /*
1195 We don't need to check if g_freight_choices_tbl already has
1196 as we are looping through fte_source_header_rates_tab and this contains
1197 one row for a unique combination of consolidation_id, ship_method_code and
1198 lane_id - Every Freight choice we show is for a unique combination of
1199 consolidation_id, ship_method_code and lane_id
1200 */
1201
1202 g_freight_choices_tbl(j).consolidation_id := l_fte_header_rate_tab(i).consolidation_id ;
1203 g_freight_choices_tbl(j).shipping_method := l_meaning;
1204 g_freight_choices_tbl(j).shipping_method_code := l_fte_header_rate_tab(i).ship_method_code;
1205 g_freight_choices_tbl(j).Transit_Time := l_fte_header_rate_tab(i).transit_time;
1206 g_freight_choices_tbl(j).transit_time_uom := l_fte_header_rate_tab(i).transit_time_uom;
1207 g_freight_choices_tbl(j).charge_amount := l_Adjusted_amount;
1208 g_freight_choices_tbl(j).cost := l_fte_header_rate_tab(i).price;
1209 g_freight_choices_tbl(j).lane_id := l_fte_header_rate_tab(i).lane_id ; -- bug 4408958
1210
1211 j := j + 1;
1212 END IF; -- If l_line_tbl.count > 0
1213 I := l_fte_header_rate_tab.NEXT(I);
1214 l_line_tbl.DELETE;
1215 l_Adjusted_amount := 0;
1216 END LOOP; --while i is not null loop
1217
1218 g_fte_source_line_rate_tab := l_fte_line_rate_tab;
1219 --This Global table is used in Procedure Process_Freight_Choices
1220
1221 END IF; --if l_fte_source_line_tab.count > 0
1222
1223 x_return_status := FND_API.G_RET_STS_SUCCESS;
1224
1225 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Freight_Choices...');
1226
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add( 'ERROR IN PROCEDURE Get_Freight_Choices: '||SUBSTR ( SQLERRM , 1 , 240 ) , 3 ) ;
1232 END IF;
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add( 'UNEXPECTED ERROR IN Get_Freight_Choices :'||SQLERRM , 3 ) ;
1235 END IF;
1236 /* IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1237 THEN
1238 OE_MSG_PUB.Add_Exc_Msg
1239 ( G_PKG_NAME,
1240 'Get_Freight_Choices');
1241 END IF; */
1242
1243 END Get_Freight_Choices;
1244
1245
1246 PROCEDURE Get_Freight_Choices_Tbl
1247 (x_freight_choices_tbl IN OUT NOCOPY /* file.sql.39 change */ freight_choices_tbl_type)
1248 IS
1249
1250 BEGIN
1251
1252 For I IN g_freight_choices_Tbl.FIRST .. g_freight_choices_tbl.LAST LOOP
1253 oe_debug_pub.add(' Value of Transit Time Uom .. '||g_freight_choices_tbl(i).transit_time_uom);
1254 x_freight_choices_tbl(i).consolidation_id := g_freight_choices_tbl(i).consolidation_id;
1255 x_freight_choices_tbl(i).shipping_method := g_freight_choices_tbl(i).shipping_method;
1256 x_freight_choices_tbl(i).shipping_method_code
1257 := g_freight_choices_tbl(i).shipping_method_code;
1258 x_freight_choices_tbl(i).transit_time := g_freight_choices_tbl(i).transit_time;
1259 x_freight_choices_tbl(i).transit_time_uom := g_freight_choices_tbl(i).transit_time_uom;
1260 x_freight_choices_tbl(i).charge_amount := g_freight_choices_tbl(i).charge_amount;
1261 x_freight_choices_tbl(i).cost := g_freight_choices_tbl(i).cost;
1262 x_freight_choices_tbl(i).lane_id := g_freight_choices_tbl(i).lane_id; --bug 4408958
1263
1264 END LOOP;
1265
1266 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Freight_Choices_Tbl...');
1267
1268 END Get_Freight_Choices_Tbl;
1269
1270
1271 PROCEDURE Get_Shipment_Details_Tbl
1272 (x_Line_Shipment_Details_tbl IN OUT NOCOPY /* file.sql.39 change */ line_Shipment_Details_tbl_type)
1273 IS
1274
1275 BEGIN
1276
1277
1278 For I IN g_line_shipment_details_tbl.FIRST .. g_line_shipment_details_tbl.LAST LOOP
1279 x_Line_Shipment_Details_tbl(i).source_line_id := g_line_shipment_details_tbl(i).source_line_id;
1280 x_Line_Shipment_Details_tbl(i).inventory_item_id := g_line_shipment_details_tbl(i).inventory_item_id;
1281 x_Line_Shipment_Details_tbl(i).source_quantity
1282 := g_line_shipment_details_tbl(i).source_quantity;
1283 x_Line_Shipment_Details_tbl(i).source_quantity_uom := g_line_shipment_details_tbl(i).source_quantity_uom;
1284 x_Line_Shipment_Details_tbl(i).ship_date := g_line_shipment_details_tbl(i).ship_date;
1285 x_Line_Shipment_Details_tbl(i).arrival_date := g_line_shipment_details_tbl(i).arrival_date;
1286
1287 END LOOP;
1288
1289 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Details_Tbl...');
1290
1291 END Get_Shipment_Details_Tbl;
1292
1293 --Bug 6186084
1294 PROCEDURE Repopulate_Freight_Choices
1295 (x_volume OUT NOCOPY NUMBER,
1296 x_weight OUT NOCOPY NUMBER,
1297 x_consolidation_id IN NUMBER)
1298 IS
1299
1300 BEGIN
1301 oe_debug_pub.add('Entering Repopulate_Freight_Choices');
1302
1303 IF g_shipment_summary_tbl.count >0 THEN
1304 x_weight := g_shipment_summary_tbl(x_consolidation_id).total_weight;
1305 x_volume := g_shipment_summary_tbl(x_consolidation_id).total_volume;
1306 END IF;
1307
1308 oe_debug_pub.add('Exiting the Repopulate_Freight_Choices proc');
1309
1310 END Repopulate_Freight_Choices;
1311
1312 PROCEDURE Process_Freight_Choices
1313 ( p_header_id IN NUMBER
1314 ,p_consolidation_id IN NUMBER
1315 ,p_ship_method_code IN VARCHAR2 -- ..This New parameter is added
1316 ,p_lane_id IN NUMBER --bug 4408958
1317 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1318 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1319 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1320 IS
1321
1322 l_freight_choices_rec OE_Freight_Choices_PVT.Freight_Choices_Rec_type;
1323 l_fte_line_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
1324 l_fte_rates_rec FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec;
1325 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
1326 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
1327 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1328 query_line_tbl OE_Order_PUB.Line_Tbl_Type;
1329 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1330 l_control_rec OE_GLOBALS.Control_Rec_Type;
1331 l_line_rec OE_ORDER_PUB.line_rec_type ;
1332 l_bulk_adj_rec OE_Freight_Rating_PVT.Bulk_Line_Adj_Rec_Type;
1333 l_count Number;
1334 l_fte_count Number := 1;
1335 i Number := 1;
1336 j Number;
1337 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1338 M Number;
1339 K Number;
1340 l_order_source_id NUMBER;
1341 l_orig_sys_document_ref VARCHAR2(50);
1342 l_orig_sys_line_ref VARCHAR2(50);
1343 l_orig_sys_shipment_ref VARCHAR2(50);
1344 l_change_sequence VARCHAR2(50);
1345 l_source_document_id NUMBER;
1346 l_source_document_line_id NUMBER;
1347 l_source_document_type_id NUMBER;
1348 l_ship_method_code VARCHAR2(30);
1349 l_lane_id NUMBER := 0;
1350 l_meaning VARCHAR2(50);
1351 l_return_status VARCHAR2(10);
1352 l_pricing_event VARCHAR2(30);
1353 l_index NUMBER := 1;
1354 l_adj_index NUMBER;
1355 l_line_id NUMBER;
1356 l_header_id NUMBER;
1357 l_config_line_exists NUMBER := 0;
1358
1359 deleted_costs number_type;
1360
1361 CURSOR C_CONFIG_ITEM_PARENTS(p_ato_line_id IN NUMBER) IS
1362 SELECT opa.price_adjustment_id,ool.line_id,
1363 opa.adjusted_amount, opa.list_line_type_code,
1364 opa.charge_type_code
1365 FROM oe_order_lines_all ool
1366 ,oe_price_adjustments opa
1367 WHERE opa.charge_type_code IN ('FTEPRICE','FTECHARGE')
1368 AND list_line_type_code = 'COST' -- For bug 7043225
1369 AND ool.line_id = opa.line_id
1370 AND ool.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
1371 AND ool.ato_line_id = p_ato_line_id;
1372
1373 /*CURSOR C_CONFIG_LINE_EXISTS(p_line_id IN NUMBER) IS
1374 SELECT 1 from oe_order_lines_all
1375 WHERE ato_line_id = p_line_id
1376 AND item_type_code = OE_GLOBALS.G_ITEM_CONFIG;
1377 */
1378 BEGIN
1379
1380 oe_debug_pub.add(' Entering the procedure process freight choices');
1381
1382 Savepoint Cancel_All;
1383 oe_debug_pub.add('first Consolidation Id got '|| g_fte_header_rate_tab(i).Consolidation_id);
1384
1385 I := g_fte_header_rate_tab.FIRST;
1386 WHILE I IS NOT NULL LOOP
1387 If g_fte_header_rate_tab(i).Consolidation_id = p_Consolidation_id and
1388 g_fte_header_rate_tab(i).Ship_method_code = p_ship_method_code and --bug 4408958
1389 g_fte_header_rate_tab(i).lane_id = p_lane_id then
1390 oe_debug_pub.add('In the first I loop');
1391 Exit;
1392 End If;
1393 I := g_fte_header_rate_tab.NEXT(I);
1394 End Loop;
1395
1396
1397 oe_debug_pub.add('Consolidation Id got '|| g_fte_header_rate_tab(i).Consolidation_id);
1398
1399 M := g_fte_header_rate_tab(I).first_line_index;
1400
1401 K := 0;
1402
1403 For j in g_fte_line_rate_tab.FIRST .. g_fte_line_rate_tab.LAST LOOP
1404 oe_debug_pub.add( ' Line Ids: value of g_fte_line_rate_tab' || g_fte_line_rate_tab(j).source_line_id);
1405 oe_debug_pub.add( ' Ship method ids : '||g_fte_line_rate_tab(j).ship_method_code);
1406 End loop;
1407
1408 While M is not null loop
1409
1410 IF g_fte_line_rate_tab(M).ship_method_code = p_ship_method_code and
1411 --and g_fte_line_rate_tab(M).lane_id = l_lane_id
1412 g_fte_line_rate_tab(M).consolidation_id = p_consolidation_id
1413 THEN
1414
1415 IF deleted_costs.EXISTS(g_fte_line_rate_tab(M).source_line_id) THEN
1416 NULL;
1417 ELSE
1418 DELETE FROM OE_PRICE_ADJUSTMENTS
1419 WHERE line_ID = g_fte_line_rate_tab(M).source_line_id
1420 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
1421 AND list_line_type_code = 'COST'
1422 AND ESTIMATED_FLAG = 'Y';
1423
1424 deleted_costs(g_fte_line_rate_tab(M).source_line_id) := g_fte_line_rate_tab(M).source_line_id;
1425
1426 oe_line_util.query_rows
1427 (p_line_id => g_fte_line_rate_tab(M).source_line_id
1428 ,x_line_tbl => query_line_tbl );
1429
1430 K := K + 1;
1431
1432 oe_debug_pub.add('The line ids for the lines added '|| g_fte_line_rate_tab(M).source_line_id);
1433 l_old_line_tbl(k) := query_line_tbl(1);
1434
1435 /* renga-review */
1436 For j in g_fte_source_line_tab.first .. g_fte_source_line_tab.Last Loop
1437 If g_fte_source_line_tab(j).source_line_id = g_fte_line_rate_tab(M).source_line_id Then
1438 l_count := j;
1439 Exit;
1440 End If;
1441 End Loop;
1442
1443
1444 IF (g_fte_line_rate_tab(M).ship_method_code) <>
1445 nvl(query_line_tbl(1).shipping_method_code,'-99')
1446 THEN
1447 IF l_debug_level > 0 THEN
1448 Null;
1449 END IF;
1450 oe_debug_pub.add('value of carrier freight code '||g_fte_line_rate_tab(M).carrier_freight_code);
1451 query_line_tbl(1).shipping_method_code :=
1452 g_fte_line_rate_tab(M).ship_method_code;
1453
1454 query_line_tbl(1).freight_carrier_code :=
1455 g_fte_line_rate_tab(M).carrier_freight_code;
1456
1457 query_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
1458
1459 /* Start Audit Trail */
1460
1461 query_line_tbl(1).change_reason := 'SYSTEM';
1462 query_line_tbl(1).change_comments := 'Choose Ship Method';
1463
1464 /* End Audit Trail */
1465
1466
1467 ELSE -- no change, set the operation to none.
1468 IF l_debug_level > 0 THEN
1469 oe_debug_pub.add('No Changes to save', 4);
1470 END IF;
1471 /* renga-review */
1472 --l_line_rec.operation := OE_GLOBALS.G_OPR_NONE;
1473
1474 query_line_tbl(1).operation := OE_GLOBALS.G_OPR_NONE;
1475
1476 --x_no_opr_count := x_no_opr_count + 1;
1477
1478 End If;
1479
1480 -- cascade to CONFIG line, we need to do this irrespective
1481 -- of the fte source line ret status because we need to
1482 -- push the offset anyway.
1483
1484 IF g_config_count > 0 AND l_index <= g_config_count THEN
1485
1486 IF query_line_tbl(1).ato_line_id =
1487 query_line_tbl(1).line_id THEN
1488
1489 -- This is an ATO Model
1490 IF l_debug_level > 0 THEN
1491 oe_debug_pub.add('ato model '||query_line_tbl(1).line_id,3);
1492 END IF;
1493
1494 -- added by Renga after review
1495 IF query_line_tbl(1).ato_line_id is not null then
1496
1497 select count(*) into l_config_line_exists
1498 from oe_order_lines_all
1499 where ato_line_id = query_line_tbl(1).ato_line_id
1500 and item_type_code = 'CONFIG';
1501
1502 END IF; --ato_line_id is not null
1503
1504
1505 IF g_line_tbl(l_index).ato_line_id =
1506 query_line_tbl(1).line_id
1507 THEN
1508
1509 IF l_debug_level > 0 THEN
1510 oe_debug_pub.add('cfg line '|| g_line_tbl(l_index).line_id, 3);
1511 END IF;
1512
1513 IF query_line_tbl(1).operation = OE_GLOBALS.G_OPR_UPDATE
1514 THEN
1515
1516 g_line_tbl(l_index).shipping_method_code :=
1517 query_line_tbl(1).shipping_method_code;
1518
1519 g_line_tbl(l_index).freight_carrier_code :=
1520 query_line_tbl(1).freight_carrier_code;
1521
1522 /* Start Audit Trail */
1523
1524 g_line_tbl(l_index).change_reason := 'SYSTEM';
1525 g_line_tbl(l_index).change_comments := 'Get Ship Method Action';
1526
1527 /* End Audit Trail */
1528
1529 IF l_debug_level > 0 THEN
1530 oe_debug_pub.Add('Cascading Ship Method from:' ||
1531 query_line_tbl(1).line_id ||' to ' ||
1532 g_line_tbl(l_index).line_id,3);
1533 END IF;
1534
1535 END IF;
1536 IF l_debug_level > 0 THEN
1537 oe_debug_pub.add
1538 ('cfg opr '||g_line_tbl(l_index).operation, 3);
1539 END IF;
1540
1541 g_line_tbl(l_index).operation:=
1542 query_line_tbl(1).operation;
1543
1544 l_index := l_index + 1;
1545
1546 END IF;
1547 END IF;
1548 END IF;
1549
1550 -- added here also
1551
1552 -- Even though the ship method did not change, rates could have
1553 -- changed. Hence registering the line, inspite of no change.
1554
1555 -- to register changed line so that repricing for this line
1556 -- would happen.
1557 oe_debug_pub.add('Register changed line: '||l_line_rec.line_id,1);
1558 OE_LINE_ADJ_UTIL.Register_Changed_Lines
1559 (p_line_id => query_line_tbl(1).line_id,
1560 p_header_id => query_line_tbl(1).header_id,
1561 p_operation => OE_GLOBALS.G_OPR_UPDATE);
1562
1563
1564 END IF; -- if deleted_costs...
1565 /* Commented for bug 7043225. We now need to allow charges to get created on ATO Model,
1566 Even if config item is not yet created
1567
1568 IF (query_line_tbl(1).ato_line_id =
1569 query_line_tbl(1).line_id ) THEN
1570 IF l_config_line_exists = 1 THEN
1571
1572 Prepare_Adj_Detail
1573 (p_header_id => query_line_tbl(1).header_id
1574 ,p_line_id => g_fte_line_rate_tab(M).source_line_id
1575 ,p_fte_rates_rec => g_fte_line_rate_tab(M)
1576 ,x_line_adj_rec => l_line_adj_rec
1577 ,x_return_status => l_return_status
1578 );
1579
1580 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1581
1582 l_config_line_exists := 0;
1583
1584 END IF;
1585
1586 ELSE
1587 */
1588
1589 Prepare_Adj_Detail
1590 (p_header_id => query_line_tbl(1).header_id
1591 ,p_line_id => g_fte_line_rate_tab(M).source_line_id
1592 ,p_fte_rates_rec => g_fte_line_rate_tab(M)
1593 ,x_line_adj_rec => l_line_adj_rec
1594 ,x_return_status => l_return_status
1595 );
1596
1597 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1598
1599 -- END IF;--bug 7043225
1600
1601 l_line_tbl(K) := query_line_tbl(1);
1602 /* renga-review: no need to do the following as we have done it
1603 for query_line_tbl in the if condition already */
1604
1605 M := g_fte_line_rate_tab.NEXT(M);
1606
1607 ELSE
1608 Exit;
1609 End If;
1610
1611 End Loop;
1612
1613
1614 l_header_id := l_line_tbl(1).header_id;
1615 l_index := 1;
1616 l_adj_index := 1;
1617
1618 WHILE l_index <= g_config_count LOOP
1619
1620 l_line_id := g_line_tbl(l_index).line_id;
1621 IF l_debug_level > 0 THEN
1622 oe_debug_pub.add( 'CASCADING ADJUSTMENT LINES TO CONFIG LINES. ' , 3 );
1623 END IF;
1624
1625 OPEN C_CONFIG_ITEM_PARENTS(g_line_tbl(l_index).ato_line_id);
1626 FETCH C_CONFIG_ITEM_PARENTS BULK COLLECT INTO
1627 l_bulk_adj_rec.price_adjustment_id
1628 ,l_bulk_adj_rec.line_id
1629 ,l_bulk_adj_rec.adjusted_amount
1630 ,l_bulk_adj_rec.list_line_type_code
1631 ,l_bulk_adj_rec.charge_type_code
1632 ;
1633
1634 CLOSE C_CONFIG_ITEM_PARENTS;
1635
1636 FOR i in 1..l_bulk_adj_rec.price_adjustment_id.COUNT LOOP
1637
1638 l_fte_rates_rec.cost_type := l_bulk_adj_rec.charge_type_code(i);
1639 l_fte_rates_rec.adjusted_price := l_bulk_adj_rec.adjusted_amount(i);
1640
1641 oe_debug_pub.add( ' in the config item loops .. line_id '||l_bulk_adj_rec.line_id(i));
1642
1643 Prepare_Adj_Detail
1644 (p_header_id => l_header_id
1645 ,p_line_id => l_line_id
1646 ,p_fte_rates_rec => l_fte_rates_rec
1647 ,x_line_adj_rec => l_line_adj_rec
1648 ,x_return_status => l_return_status
1649 );
1650
1651 --l_line_adj_tbl(l_adj_index) := l_line_adj_rec;
1652
1653 -- inserting for the config line
1654 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1655
1656 -- register changed line for config item line.
1657 OE_LINE_ADJ_UTIL.Register_Changed_Lines
1658 (p_line_id => l_line_id,
1659 p_header_id => l_header_id,
1660 p_operation => OE_GLOBALS.G_OPR_UPDATE);
1661
1662 -- deleting the parents of the config line.
1663 -- these deleted parent lines have been registered in
1664 -- previous loop looping through p_fte_rates_tab, so
1665 -- no need to register changed line again for these lines.
1666
1667 DELETE FROM oe_price_adjustments
1668 WHERE price_adjustment_id = l_bulk_adj_rec.price_adjustment_id(i);
1669
1670 l_adj_index := l_adj_index + 1;
1671
1672 END LOOP;
1673
1674 oe_line_util.query_rows
1675 (p_line_id => l_line_id
1676 ,x_line_tbl => query_line_tbl );
1677
1678 K := K + 1;
1679
1680 oe_debug_pub.add('The config lines added '|| l_line_id);
1681 l_line_tbl(k) := query_line_tbl(1);
1682
1683 l_index := l_index + 1;
1684
1685 END LOOP;
1686
1687 -- for ATO lines, only send config lines to Pricing.
1688 -- Commented for bug 7043225, as we want all the charges to get applied on ATO Model even before,
1689 -- config item is created. Hence we need to pass the CPF as 'Y' for all lines.
1690 -- Else the charges that got inserted on these items will never get displayed in the UI
1691 /* J := l_line_tbl.FIRST;
1692 WHILE J IS NOT NULL LOOP
1693 -- delete those non-shippable ATO parent lines
1694 IF l_line_tbl(J).ato_line_id IS NOT NULL
1695 AND l_line_tbl(J).item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
1696 l_line_tbl(J).calculate_price_flag := 'N';
1697 oe_debug_pub.add('ATO item price flag set to N :'||l_line_tbl(J).line_id);
1698 END IF;
1699 J := l_line_tbl.NEXT(J);
1700 END LOOP;*/
1701
1702 For j in l_line_tbl.FIRST .. l_line_tbl.LAST LOOP
1703 oe_debug_pub.add( ' Lines passed to pricing Ids: value of l_line_tbl' || l_line_tbl(j).line_id);
1704 End loop;
1705
1706 IF (Nvl(get_list_line_type_code(p_header_id),0) = 0 ) THEN
1707
1708 Create_Dummy_Adjustment
1709 (p_header_id => p_header_id
1710 );
1711
1712 END IF;
1713
1714 l_control_rec.default_attributes := TRUE;
1715 l_control_rec.controlled_operation := TRUE;
1716 l_control_rec.change_attributes := TRUE;
1717 l_control_rec.validate_entity := TRUE;
1718 l_control_rec.write_to_DB := TRUE;
1719 l_control_rec.process_entity := OE_GLOBALS.G_ENTITY_LINE;
1720
1721 --l_line_tbl.operation := OE_GLOBALS.G_OPR_UPDATE;
1722
1723 OE_ORDER_PVT.Lines
1724 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
1725 ,p_control_rec => l_control_rec
1726 ,p_x_line_tbl => l_line_tbl
1727 ,p_x_old_line_tbl => l_old_line_tbl
1728 ,x_return_status => x_return_status);
1729
1730 IF l_debug_level > 0 THEN
1731 oe_debug_pub.Add('After Calling Process Order...'||x_return_status,3);
1732 END IF; -- bug7433107
1733
1734 -- Logging a delayed request for Price Line in BATCH mode.
1735
1736 l_pricing_event := 'BATCH';
1737
1738 OE_delayed_requests_Pvt.log_request(
1739 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1740 p_entity_id => p_header_id,
1741 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1742 p_requesting_entity_id => p_header_id,
1743 p_request_unique_key1 => l_pricing_event,
1744 p_param1 => p_header_id,
1745 p_param2 => l_pricing_event,
1746 p_request_type => OE_GLOBALS.G_PRICE_ORDER,
1747 x_return_status => l_return_status);
1748
1749
1750 IF l_debug_level > 0 THEN -- bug7433107
1751 OE_DEBUG_PUB.Add('Before Calling Process Requests and Notify',3);
1752 END IF;
1753
1754 OE_ORDER_PVT.Process_Requests_And_notify
1755 ( p_process_requests => TRUE
1756 ,p_notify => TRUE
1757 ,x_return_status => x_return_status
1758 ,p_line_tbl => l_line_tbl
1759 ,p_old_line_tbl => l_old_line_tbl);
1760
1761 IF l_debug_level > 0 THEN
1762 OE_DEBUG_PUB.Add('After Calling Process Requests and Notify...'|| x_return_status,3);
1763 END IF;
1764
1765 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Process_Freight_Choices...');
1766
1767 EXCEPTION
1768
1769 WHEN FND_API.G_EXC_ERROR THEN
1770 IF l_debug_level > 0 THEN
1771 oe_debug_pub.Add('Expected Error in Process Freight Choices', 1);
1772 END IF;
1773
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775
1776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1777 IF l_debug_level > 0 THEN
1778 oe_debug_pub.Add('Unexpected Error in Process Freight Choices'||
1779 sqlerrm, 2);
1780 END IF;
1781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1782
1783 WHEN OTHERS THEN
1784
1785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1786
1787 /* IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1788 THEN
1789 OE_MSG_PUB.Add_Exc_Msg
1790 ( G_PKG_NAME,
1791 'Process_Freight_Choices');
1792 END IF; */
1793
1794 END Process_Freight_Choices;
1795
1796 PROCEDURE Cancel_all Is
1797
1798 NO_SAVEPOINT EXCEPTION;
1799 PRAGMA EXCEPTION_INIT(NO_SAVEPOINT, -1086);
1800
1801 Begin
1802 oe_debug_pub.add('Entering Cancel all');
1803 Rollback to Savepoint Cancel_All;
1804
1805 -- bug 5883660
1806 EXCEPTION WHEN NO_SAVEPOINT THEN
1807 Null;
1808 End Cancel_all;
1809
1810 END OE_FREIGHT_CHOICES_PVT;