[Home] [Help]
PACKAGE BODY: APPS.OE_FREIGHT_CHOICES_PVT
Source
1 PACKAGE BODY OE_FREIGHT_CHOICES_PVT AS
2 /* $Header: OEXVFCHB.pls 120.9 2010/07/23 07:43:12 spothula 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' OR
457 c_ship_method.item_type_code = 'INCLUDED') AND --9775352
458 c_ship_method.ato_line_id = c_ship_method.line_id)
459 THEN
460 IF l_debug_level > 0 THEN
461 OE_DEBUG_PUB.Add('calculate rating part of ato', 4);
462 END IF;
463 ELSE
464 IF l_debug_level > 0 THEN
465 OE_DEBUG_PUB.Add('do not calculate freight_rating '||
466 c_ship_method.line_id, 1);
467 END IF;
468 p_x_fte_source_line_tab(l_count).freight_rating_flag:= 'N';
469 END IF;
470
471 END IF;
472
473 IF l_debug_level > 0 THEN
474 oe_debug_pub.Add('--------- Input to FTE --------',3);
475
476 oe_debug_pub.Add('Source Line :'||
477 p_x_fte_source_line_tab(l_count).source_line_id,3);
478
479 oe_debug_pub.Add('Ship From Org :'||
480 p_x_fte_source_line_tab(l_count).ship_from_org_id,3);
481
482 oe_debug_pub.Add('Customer :'||
483 p_x_fte_source_line_tab(l_count).customer_id,3);
484
485 oe_debug_pub.Add('Inventory Item :'||
486 p_x_fte_source_line_tab(l_count).inventory_item_id,3);
487
488 oe_debug_pub.Add('Source Quantity :'||
489 p_x_fte_source_line_tab(l_count).source_quantity,3);
490
491 oe_debug_pub.Add('Ship Date :'||
492 p_x_fte_source_line_tab(l_count).ship_date,3);
493
494 oe_debug_pub.Add('Delivery Lead Time :'||
495 p_x_fte_source_line_tab(l_count).delivery_lead_time,3);
496
497 oe_debug_pub.Add('Scheduled :'||
498 p_x_fte_source_line_tab(l_count).scheduled_flag,3);
499
500 oe_debug_pub.Add('Order Set Type :'||
501 p_x_fte_source_line_tab(l_count).order_set_type,3);
502
503 oe_debug_pub.Add('Order Set :'||
504 p_x_fte_source_line_tab(l_count).order_set_id,3);
505
506 oe_debug_pub.Add('Ship Method :'||
507 p_x_fte_source_line_tab(l_count).ship_method_code,3);
508
509 oe_debug_pub.Add('Freight Terms :'||
510 p_x_fte_source_line_tab(l_count).freight_terms,3);
511
512 oe_debug_pub.Add('Freight on Board :'||
513 p_x_fte_source_line_tab(l_count).fob_code,3);
514
515 oe_debug_pub.Add('Intermediate Ship :'||
516 p_x_fte_source_line_tab(l_count).intmed_ship_to_site_id,3);
517
518 oe_debug_pub.Add('Transactional Currency :'||
519 p_x_fte_source_line_tab(l_count).currency,3);
520
521 oe_debug_pub.Add('-------------------------------',3);
522 END IF;
523
524 -- Store the old ship method code in the line table
525
526 l_line_rec.line_id := c_ship_method.line_id;
527 l_line_rec.ato_line_id := c_ship_method.ato_line_id;
528 l_line_rec.item_type_code := c_ship_method.item_type_code;
529 l_line_rec.shipping_method_code :=
530 c_ship_method.shipping_method_code;
531 l_line_rec.freight_terms_code :=
532 c_ship_method.freight_terms_code;
533 l_line_rec.freight_carrier_code :=
534 c_ship_method.freight_carrier_code;
535
536 l_line_count := l_line_count + 1;
537 p_x_line_tbl(l_line_count) := l_line_rec;
538
539 END IF;
540
541 G_Ship_Date_tbl(c_ship_method.line_id).Schedule_Ship_Date := c_ship_method.Schedule_Ship_Date;
542 G_Ship_Date_tbl(c_ship_method.line_id).Line_id := c_ship_method.line_id;
543 END LOOP;
544
545 -- this check is added to show the message when no lines of the order are eligible for freight rating
546
547 IF p_x_line_tbl.count = 0 THEN
548
549 FND_MESSAGE.Set_Name('ONT','ONT_FTE_NO_LINES_ELIGIBLE');
550 OE_MSG_PUB.Add;
551 IF l_debug_level > 0 THEN
552 OE_DEBUG_PUB.Add('No lines of the order are eligible for freight rating');
553 END IF; -- bug 7433107
554 --l_return_status = FND_API.G_RET_STS_ERROR;
555 RAISE FND_API.G_EXC_ERROR;
556 -- END IF; -- bug 7433107
557 END IF;
558
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.Add('FTE Input count:'||p_x_fte_source_line_tab.count,3);
561 oe_debug_pub.Add('Total count:'|| p_x_line_tbl.count,3);
562 END IF;
563
564 for i in G_Ship_Date_tbl.first..G_Ship_Date_tbl.last loop
565 if G_Ship_Date_tbl.exists(i) Then
566 oe_debug_pub.Add('Shedule Ship Date Value .. '||G_Ship_Date_tbl(i).Schedule_Ship_Date);
567 end if;
568 end loop;
569
570 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Prepare_Freight_Choices_Input...');
571 EXCEPTION
572
573 WHEN FND_API.G_EXC_ERROR THEN
574 IF l_debug_level > 0 THEN
575 oe_debug_pub.Add('Expected Error in Create FTE Input',2);
576 END IF;
577
578 x_return_status := FND_API.G_RET_STS_ERROR;
579
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 IF l_debug_level > 0 THEN
582 oe_debug_pub.Add('Unexpected Error in Create FTE Input:'||SqlErrm, 1);
583 END IF;
584
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586
587
588 WHEN OTHERS THEN
589
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591
592 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
593 THEN
594 OE_MSG_PUB.Add_Exc_Msg
595 ( G_PKG_NAME,
596 'Prepare_Freight_Choices_Input');
597 END IF;
598
599 END Prepare_Freight_Choices_Input;
600
601 PROCEDURE Get_Shipment_Summary
602 (p_header_id IN NUMBER,
603 x_shipment_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
604 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
605 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
606 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
607
608 IS
609 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
610 l_fte_source_line_tab FTE_PROCESS_REQUESTS.fte_source_line_tab;
611 l_fte_source_header_tab FTE_PROCESS_REQUESTS.fte_source_header_tab;
612 l_fte_line_rates_tab FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
613 l_fte_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
614 --l_rating_parameters_tab FTE_PROCESS_REQUESTS.fte_rating_parameters_tab;
615 l_return_status VARCHAR2(1);
616 l_msg_data VARCHAR2(2000);
617 l_msg_count NUMBER;
618 l_config_count NUMBER;
619 i NUMBER;
620 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
621 l_msg_text VARCHAR2(2000);
622
623 BEGIN
624
625 oe_debug_pub.add(' Entering the procedure Get_shipment_Summary');
626
627 x_shipment_count := 0;
628
629 -- Prepare the input information for FTE.
630 --OE_FTE_INTEGRATION_PVT.Create_FTE_Input
631 Prepare_Freight_Choices_Input
632 ( p_header_id => p_header_id
633 --,p_line_id => NULL
634 ,p_x_fte_source_line_tab => l_fte_source_line_tab
635 ,p_x_line_tbl => l_line_tbl
636 ,p_action => 'X'
637 ,x_config_count => l_config_count
638 ,x_return_status => l_return_status
639 );
640
641 x_return_status := l_return_status;
642
643 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
646 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
647 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
648 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
649 IF l_debug_level > 0 THEN
650 oe_debug_pub.Add(l_msg_text, 3);
651 END IF;
652 oe_msg_pub.add_text(p_message_text => l_msg_text);
653 END LOOP;
654 END IF;
655 RAISE FND_API.G_EXC_ERROR;
656 END IF;
657
658 --x_shipment_count := 15;
659
660
661 -- Call FTE to get group info only if the number of lines is greater than zero
662 IF l_fte_source_line_tab.Count > 0 THEN
663
664 Print_Time('Calling FTE for Get_Group ... ');
665 FTE_PROCESS_REQUESTS.Process_Lines(
666 p_source_line_tab => l_fte_source_line_tab,
667 p_source_header_tab => l_fte_source_header_tab,
668 p_source_type => 'ONT',
669 p_action => 'GET_GROUP', -- to get group info
670 --p_rating_parameters_tab => l_rating_parameters_tab,
671 x_source_line_rates_tab => l_fte_line_rates_tab,
672 x_source_header_rates_tab=> l_fte_header_rates_tab,
673 x_return_status => l_return_status,
674 x_msg_count => l_msg_count,
675 x_msg_data => l_msg_data);
676
677 x_return_status := l_return_status;
678 Print_Time('After Calling FTE for Get_Group ... ');
679 IF l_debug_level > 0 THEN
680 oe_debug_pub.Add('After Calling FTE Process Lines:'||
681 l_return_status,3);
682 --x_shipment_count := 15;
683 END IF;
684
685 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
687 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
688 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
689 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
690 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
691 IF l_debug_level > 0 THEN
692 oe_debug_pub.Add(l_msg_text, 3);
693 END IF;
694 oe_msg_pub.add_text(p_message_text => l_msg_text);
695 END LOOP;
696 END IF;
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 g_line_tbl := l_line_tbl;
701 g_fte_source_line_tab := l_fte_source_line_tab;
702 g_fte_source_header_tab := l_fte_source_header_tab;
703
704 oe_debug_pub.Add('header tab count is : ' || l_fte_source_header_tab.Count);
705 oe_debug_pub.Add('line tab count is : ' || l_fte_source_line_tab.Count);
706
707
708 g_config_count := l_config_count;
709
710 -- populate the global table with FTE results.
711
712 --Get the ship_from,ship_to,total_weight,total_volume,freight_terms,scheduled_ship_date for the line from l_fte_header_source_tab
713 For i IN l_fte_source_header_tab.FIRST .. l_fte_source_header_tab.LAST LOOP
714 g_shipment_summary_tbl(i).consolidation_id := l_fte_source_header_tab(i).consolidation_id;
715 g_shipment_summary_tbl(i).ship_from := l_fte_source_header_tab(i).Ship_from_Org_Id;
716 g_shipment_summary_tbl(i).ship_to := l_fte_source_header_tab(i).Ship_to_site_Id;
717 g_shipment_summary_tbl(i).total_weight := l_fte_source_header_tab(i).total_weight;
718 g_shipment_summary_tbl(i).weight_uom := l_fte_source_header_tab(i).weight_uom_code;
719 g_shipment_summary_tbl(i).total_volume := l_fte_source_header_tab(i).total_volume;
720 g_shipment_summary_tbl(i).volume_uom := l_fte_source_header_tab(i).volume_uom_code;
721 g_shipment_summary_tbl(i).freight_terms := l_fte_source_header_tab(i).freight_terms;
722
723 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
724 IF g_fte_source_line_tab(j).consolidation_id = l_fte_source_header_tab(i).consolidation_id then
725 If G_Ship_Date_tbl.exists(g_fte_source_line_tab(j).source_line_id) Then
726 g_shipment_summary_tbl(i).scheduled_ship_date := G_Ship_Date_tbl(g_fte_source_line_tab(j).source_line_id).Schedule_Ship_Date;
727 End If;
728 End If;
729 End Loop;
730
731 END LOOP;
732
733 x_shipment_count := g_shipment_summary_tbl.count;
734
735 End If; -- l_fte_source_line_tab.Count > 0
736 --oe_debug_pub.add('Before print time');
737 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Summary...');
738 oe_debug_pub.add('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Summary');
739
740 EXCEPTION
741
742 WHEN FND_API.G_EXC_ERROR THEN
743 IF l_debug_level > 0 THEN
744 oe_debug_pub.Add('Expected Error in Get Shipment Summary',2);
745 END IF;
746
747
748 END Get_Shipment_Summary;
749
750 PROCEDURE Get_Shipment_Summary_Tbl
751 (x_shipment_summary_tbl IN OUT NOCOPY /* file.sql.39 change */ shipment_summary_tbl_type)
752 IS
753
754 BEGIN
755
756 oe_debug_pub.add('Entering Get shipment summary tbl');
757 oe_debug_pub.add('count:'||g_shipment_summary_tbl.count);
758
759 IF g_shipment_summary_tbl.count >0 THEN
760 For I IN g_shipment_summary_tbl.FIRST .. g_shipment_summary_tbl.LAST LOOP
761 x_shipment_summary_tbl(i).consolidation_id := g_shipment_summary_tbl(i).consolidation_id;
762 x_shipment_summary_tbl(i).ship_from := g_shipment_summary_tbl(i).ship_from;
763 x_shipment_summary_tbl(i).ship_to := g_shipment_summary_tbl(i).ship_to;
764 x_shipment_summary_tbl(i).total_weight := g_shipment_summary_tbl(i).total_weight;
765 x_shipment_summary_tbl(i).weight_uom := g_shipment_summary_tbl(i).weight_uom;
766 x_shipment_summary_tbl(i).total_volume := g_shipment_summary_tbl(i).total_volume;
767 x_shipment_summary_tbl(i).volume_uom := g_shipment_summary_tbl(i).volume_uom;
768 x_shipment_summary_tbl(i).freight_terms := g_shipment_summary_tbl(i).freight_terms;
769 x_shipment_summary_tbl(i).scheduled_ship_date := g_shipment_summary_tbl(i).scheduled_ship_date;
770 END LOOP;
771 END IF;
772
773 oe_debug_pub.add(' exiting the get_shipment_summary_tbl proc');
774
775 END Get_Shipment_Summary_Tbl;
776
777 PROCEDURE Prepare_Adj_Detail
778 ( p_header_id IN NUMBER
779 ,p_line_id IN NUMBER
780 ,p_fte_rates_rec IN FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec
781 ,x_line_adj_rec OUT NOCOPY OE_Order_PUB.Line_Adj_Rec_Type
782 ,x_return_status OUT NOCOPY VARCHAR2
783
784 ) IS
785
786 l_price_adjustment_id number := 0;
787
788 --
789 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
790 --
791 BEGIN
792
793 IF l_debug_level > 0 THEN
794 oe_debug_pub.add( 'ENTERING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
795 END IF;
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797
798 select oe_price_adjustments_s.nextval into l_price_adjustment_id
799 from dual;
800
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add( 'PRICE ADJUSTMENT ID IN PREPARE_ADJ_DETAIL IS: ' ||L_PRICE_ADJUSTMENT_ID , 1 ) ;
803 oe_debug_pub.add( 'LINE_ID IN PREPARE_ADJ_DETAIL IS: ' ||P_LINE_ID , 1 ) ;
804 END IF;
805
806 x_line_adj_rec.header_id := p_header_id;
807 x_line_adj_rec.line_id := p_line_id;
808 x_line_adj_rec.price_adjustment_id := l_price_adjustment_id;
809 x_line_adj_rec.creation_date := sysdate;
810 x_line_adj_rec.last_update_date := sysdate;
811 x_line_adj_rec.created_by := 1;
812 x_line_adj_rec.last_updated_by := 1;
813 x_line_adj_rec.last_update_login := 1;
814
815 x_line_adj_rec.automatic_flag := 'Y';
816 x_line_adj_rec.adjusted_amount := p_fte_rates_rec.adjusted_price;
817 oe_debug_pub.add('value of cost ' ||p_fte_rates_rec.adjusted_price);
818 x_line_adj_rec.charge_type_code := p_fte_rates_rec.cost_type;
819 oe_debug_pub.add('value of cost_type '||p_fte_rates_rec.cost_type);
820 x_line_adj_rec.list_line_type_code := 'COST';
821 x_line_adj_rec.estimated_flag := 'Y';
822 x_line_adj_rec.source_system_code := 'FTE';
823
824 IF l_debug_level > 0 THEN
825 oe_debug_pub.add( 'EXITING PROCEDURE PREPARE_ADJ_DETAIL.' , 3 ) ;
826 END IF;
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831 IF l_debug_level > 0 THEN
832 oe_debug_pub.add( 'ERROR IN PROCEDURE PREPARE_ADJ_DETAIL: '||SUBSTR ( SQLERRM , 1 , 240 ) , 3 ) ;
833 END IF;
834 IF l_debug_level > 0 THEN
835 oe_debug_pub.add( 'UNEXPECTED ERROR IN PREPRARE_ADJ_DETAIL :'||SQLERRM , 3 ) ;
836 END IF;
837 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
838 THEN
839 OE_MSG_PUB.Add_Exc_Msg
840 ( G_PKG_NAME,
841 'Prepare_Adj_Detail');
842 END IF;
843 END Prepare_Adj_Detail;
844
845
846 PROCEDURE Create_Dummy_Adjustment(p_header_id in number
847 ) IS
848 l_price_adjustment_id number := -1;
849 --
850 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
851 --
852 BEGIN
853
854 IF p_header_id is not null THEN
855
856 select oe_price_adjustments_s.nextval
857 into l_price_adjustment_id
858 from dual;
859
860 INSERT INTO oe_price_adjustments
861 (PRICE_ADJUSTMENT_ID
862 ,HEADER_ID
863 ,LINE_ID
864 ,PRICING_PHASE_ID
865 ,LIST_LINE_TYPE_CODE
866 ,LIST_HEADER_ID
867 ,LIST_LINE_ID
868 ,ADJUSTED_AMOUNT
869 ,AUTOMATIC_FLAG
870 ,UPDATED_FLAG
871 ,APPLIED_FLAG
872 ,CREATION_DATE
873 ,CREATED_BY
874 ,LAST_UPDATE_DATE
875 ,LAST_UPDATED_BY
876 )
877 VALUES
878 (l_price_adjustment_id
879 ,p_header_id
880 ,NULL
881 ,-1
882 ,'OM_CALLED_CHOOSE_SHIP_METHOD'
883 ,-1*p_header_id
884 ,NULL
885 ,-1
886 ,'N'
887 ,'Y'
888 ,NULL
889 ,sysdate
890 ,1
891 ,sysdate
892 ,1
893 );
894
895 END IF;
896 END Create_Dummy_Adjustment;
897
898 Function Get_List_Line_Type_Code
899 ( p_key IN NUMBER)
900 RETURN Number
901 IS
902 l_count Number := 0;
903 Begin
904
905 Select Count(*) into l_count from
906 oe_price_adjustments
907 where header_id = p_key
908 and LIST_LINE_TYPE_CODE = 'OM_CALLED_CHOOSE_SHIP_METHOD';
909
910 Return l_count;
911
912 End;
913
914 PROCEDURE Get_Freight_Choices
915 ( p_consolidation_id IN NUMBER,
916 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
917 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
918 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
919 IS
920
921 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
922 l_fte_source_header_tab FTE_PROCESS_REQUESTS.Fte_Source_Header_Tab;
923 l_fte_line_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
924 l_fte_header_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_header_Rates_Tab;
925 l_price_control_rec OE_ORDER_PRICE_PVT.control_rec_type;
926 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
927 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
928 query_line_tbl OE_Order_PUB.Line_Tbl_Type;
929 l_config_count NUMBER;
930 l_no_opr_count NUMBER;
931 l_msg_text VARCHAR2(2000);
932 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
933 i NUMBER;
934 j NUMBER;
935 g_count Number := 0;
936 l_index NUMBER;
937 l_total_charges NUMBER := 0;
938 l_total_cost NUMBER := 0;
939 l_return_status VARCHAR2(10);
940 l_Adjusted_amount NUMBER := 0;
941 q_Adjusted_amount NUMBER := 0;
942 l_meaning VARCHAR2(50);
943 l_exists_flag BOOLEAN := FALSE;
944 l_cost_amount number := 0;
945 M NUMBER := 0;
946 l_next_index NUMBER := 0;
947 l_ship_method_code VARCHAR2(30);
948 l_source_line_id NUMBER := 0;
949 K NUMBER := 0;
950 l_lane_id NUMBER := 0;
951
952 deleted_costs number_type;
953
954 BEGIN
955
956 /* initialize g_freight_choices_tbl for this call */
957
958 oe_debug_pub.add( 'Entering Get Freight Choices procedure ');
959
960 g_freight_choices_tbl.DELETE;
961
962 For j in g_fte_source_header_tab.FIRST .. g_fte_source_header_tab.LAST LOOP
963 oe_debug_pub.add( ' value of g_fte_source_header_tab ' || g_fte_source_header_tab(j).consolidation_id);
964 End loop;
965
966 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
967 oe_debug_pub.add( ' value of g_fte_source_line_tab ' || g_fte_source_line_tab(j).consolidation_id);
968 End loop;
969
970 IF g_fte_source_header_tab(p_consolidation_id).consolidation_id = p_consolidation_id then
971 l_fte_source_header_tab(1) := g_fte_source_header_tab(p_consolidation_id);
972
973 END IF;
974
975 oe_debug_pub.add( ' Count 1');
976
977 i := 1;
978 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
979 IF g_fte_source_line_tab(j).consolidation_id = p_consolidation_id then
980 l_fte_source_line_tab(i) := g_fte_source_line_tab(j);
981 i := i+1;
982 End If;
983 END LOOP;
984
985 oe_debug_pub.add( ' Count 2');
986 /*
987 This is used to populate the return table for the shipment details block
988 in Get_Shipment_Details Procedure.
989 */
990 g_line_shipment_details_tbl := l_fte_source_line_tab;
991
992 -- Call FTE to get freight choices only if the number of lines is
993 -- greater than zero.
994 IF l_fte_source_line_tab.Count > 0 THEN
995
996 oe_debug_pub.Add('before process lines ');
997 oe_debug_pub.Add('source header id : ' || l_fte_source_line_tab(1).source_header_id);
998 Print_Time('Calling FTE for GET_RATE_CHOICE ... ');
999
1000 FTE_PROCESS_REQUESTS.Process_Lines (
1001 p_source_line_tab => l_fte_source_line_tab,
1002 p_source_header_tab => l_fte_source_header_tab,
1003 p_source_type => 'ONT',
1004 p_action => 'GET_RATE_CHOICE', -- to get freight rates
1005 x_source_line_rates_tab => l_fte_line_rate_tab,
1006 x_source_header_rates_tab=> l_fte_header_rate_tab,
1007 x_return_status => l_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data);
1010
1011 Print_Time('After Calling FTE for GET_RATE_CHOICE ... ');
1012 IF l_debug_level > 0 THEN
1013 oe_debug_pub.Add('After Calling FTE Process Lines:'||
1014 l_return_status,3);
1015 END IF;
1016
1017 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1020 IF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
1021 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1022 l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1023 IF l_debug_level > 0 THEN
1024 oe_debug_pub.Add(l_msg_text, 3);
1025 END IF;
1026 oe_msg_pub.add_text(p_message_text => l_msg_text);
1027 END LOOP;
1028 END IF;
1029 RAISE FND_API.G_EXC_ERROR;
1030 END IF;
1031
1032 g_line_shipment_details_tbl := l_fte_source_line_tab;
1033
1034 -- g_fte_source_line_tab := l_fte_source_line_tab;
1035 --g_fte_source_header_tab := l_fte_source_header_tab;
1036
1037 -- Bug 6186084
1038 For I IN l_fte_source_header_tab.FIRST .. l_fte_source_header_tab.LAST LOOP
1039 g_shipment_summary_tbl(p_consolidation_id).total_weight := l_fte_source_header_tab(i).total_weight;
1040 g_shipment_summary_tbl(p_consolidation_id).total_volume := l_fte_source_header_tab(i).total_volume;
1041 END LOOP;
1042
1043 For j in g_fte_source_line_tab.FIRST .. g_fte_source_line_tab.LAST LOOP
1044 oe_debug_pub.add( ' value of g_fte_source_line_tab ' || g_fte_source_line_tab(j).consolidation_id);
1045 End loop;
1046
1047 g_fte_line_rate_tab := l_fte_line_rate_tab ;
1048 g_fte_header_rate_tab := l_fte_header_rate_tab;
1049
1050 l_index := 1;
1051
1052 I := l_fte_header_rate_tab.FIRST;
1053 j := 1;
1054 WHILE I IS NOT NULL LOOP
1055
1056 l_ship_method_code := l_fte_header_rate_tab(I).ship_method_code;
1057 l_lane_id := l_fte_header_rate_tab(I).lane_id;
1058
1059 IF l_debug_level > 0 THEN
1060 oe_debug_pub.add( '============ FTE RESULTS ============' , 3 ) ;
1061 END IF;
1062
1063 M := l_fte_header_rate_tab(I).first_line_index;
1064 l_source_line_id := l_fte_line_rate_tab(M).source_line_id;
1065 K := 0;
1066
1067 While M is not null loop
1068
1069 oe_debug_pub.add('source line id : ' || l_fte_line_rate_tab(M).source_line_id);
1070
1071 oe_debug_pub.add('ship method is: ' || l_fte_line_rate_tab(M).ship_method_code);
1072
1073 IF l_fte_line_rate_tab(M).ship_method_code = l_ship_method_code
1074 and l_fte_line_rate_tab(M).lane_id = l_lane_id
1075 and l_fte_line_rate_tab(M).consolidation_id = p_consolidation_id
1076 THEN
1077
1078 Prepare_Adj_Detail
1079 (p_header_id => l_fte_source_line_tab(1).source_header_id
1080 ,p_line_id => l_fte_line_rate_tab(M).source_line_id
1081 ,p_fte_rates_rec => l_fte_line_rate_tab(M)
1082 ,x_line_adj_rec => l_line_adj_rec
1083 ,x_return_status => l_return_status
1084 );
1085
1086 oe_debug_pub.add('104');
1087
1088 IF deleted_costs.EXISTS(l_fte_line_rate_tab(M).source_line_id) THEN
1089 NULL;
1090 ELSE
1091 DELETE FROM OE_PRICE_ADJUSTMENTS
1092 WHERE line_ID = l_fte_line_rate_tab(M).source_line_id
1093 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
1094 AND list_line_type_code = 'COST'
1095 AND ESTIMATED_FLAG = 'Y';
1096
1097 deleted_costs(l_fte_line_rate_tab(M).source_line_id) := l_fte_line_rate_tab(M).source_line_id;
1098
1099 oe_line_util.query_rows
1100 (p_line_id => l_fte_line_rate_tab(M).source_line_id
1101 ,x_line_tbl => query_line_tbl );
1102
1103 K := K + 1;
1104
1105 l_line_tbl(K) := query_line_tbl(1);
1106
1107 END IF; -- if deleted_costs...
1108
1109 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1110
1111 ELSE
1112 EXIT; -- We can exit the loop since l_fte_line_rate_tab is grouped
1113 -- according to lane_id, ship_method_code and consolidation_id
1114
1115 END IF; --if l_fte_line_rate_tab(M).ship_method_code = l_ship_method_code
1116
1117 M := l_fte_line_rate_tab.NEXT(M);
1118
1119 oe_debug_pub.add('105');
1120
1121 END LOOP; --While M is not null loop
1122
1123 deleted_costs.DELETE;
1124
1125 -- Calling Pricing Engine to calculate freight charges
1126 -- if being called from Action button.
1127
1128 oe_debug_pub.add('107');
1129
1130 /*
1131 Call Pricing to do cost to charge conversion with simulation mode for this order line and
1132 for this ship method combination.
1133 populate the simulate_flag to 'Y' and populate the freight_charge_flag to 'Y' in control record,
1134 Also set the write_to_db to false.
1135 Freight_Choices_Tab should store all the applicable freight charges for this particular order line.
1136 */
1137 oe_debug_pub.add('108');
1138
1139 l_price_control_rec.p_Request_Type_Code:='ONT';
1140 l_Price_control_rec.p_write_to_db:=FALSE;
1141 l_price_control_rec.p_honor_price_flag:='Y';
1142 l_price_control_rec.p_multiple_events:='N';
1143 l_price_control_rec.p_get_freight_flag:='Y';
1144 l_price_control_rec.p_simulation_flag := 'Y';
1145
1146 oe_debug_pub.add('109');
1147
1148 IF l_line_tbl.count > 0 THEN
1149 oe_order_price_pvt.price_line
1150 (p_Header_id => null
1151 ,p_Line_id => null
1152 ,px_line_Tbl => l_line_tbl
1153 ,p_Control_Rec => l_price_control_rec
1154 ,p_action_code => 'PRICE_LINE'
1155 ,p_Pricing_Events => 'BATCH'
1156 ,x_Return_Status => l_return_status
1157 );
1158
1159 oe_debug_pub.add('110');
1160 K := 1;
1161 K := l_line_tbl.FIRST;
1162 WHILE K IS NOT NULL LOOP
1163
1164 Select sum(nvl(l.ADJUSTMENT_AMOUNT,0)) into q_Adjusted_amount from QP_ldets_v l,QP_preq_lines_tmp q
1165 where l.line_index = q.line_index
1166 and q.line_id = l_line_tbl(K).line_id
1167 and q.line_type_code = 'LINE'
1168 AND nvl(l.automatic_flag,'N') = 'Y'
1169 AND l.list_line_type_code = 'FREIGHT_CHARGE';
1170
1171 oe_debug_pub.add('value of ordered qty : '||nvl(l_line_tbl(K).ordered_quantity,0));
1172
1173 -- Modified for bug # 7043225
1174 -- bug 6701769/6753485
1175 --l_Adjusted_amount := l_Adjusted_amount + nvl(l_line_tbl(K).ordered_quantity,0)*q_Adjusted_amount;
1176 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));
1177
1178 oe_debug_pub.add('6701769 value of l_Adjusted_amount : '||l_Adjusted_amount);
1179
1180 K := l_line_tbl.NEXT(K);
1181
1182 END LOOP; -- while k is not null
1183
1184 K := 0;
1185
1186 oe_debug_pub.add('value of charges : '||l_Adjusted_amount);
1187
1188 --oe_debug_pub.add('value of ordered qty : '||nvl(l_line_tbl(K).ordered_quantity,0));
1189
1190 Select MEANING into l_meaning from oe_ship_methods_v
1191 where LOOKUP_CODE= l_fte_header_rate_tab(I).ship_method_code
1192 and LOOKUP_TYPE='SHIP_METHOD';
1193
1194
1195 /*
1196 We don't need to check if g_freight_choices_tbl already has
1197 as we are looping through fte_source_header_rates_tab and this contains
1198 one row for a unique combination of consolidation_id, ship_method_code and
1199 lane_id - Every Freight choice we show is for a unique combination of
1200 consolidation_id, ship_method_code and lane_id
1201 */
1202
1203 g_freight_choices_tbl(j).consolidation_id := l_fte_header_rate_tab(i).consolidation_id ;
1204 g_freight_choices_tbl(j).shipping_method := l_meaning;
1205 g_freight_choices_tbl(j).shipping_method_code := l_fte_header_rate_tab(i).ship_method_code;
1206 g_freight_choices_tbl(j).Transit_Time := l_fte_header_rate_tab(i).transit_time;
1207 g_freight_choices_tbl(j).transit_time_uom := l_fte_header_rate_tab(i).transit_time_uom;
1208 g_freight_choices_tbl(j).charge_amount := l_Adjusted_amount;
1209 g_freight_choices_tbl(j).cost := l_fte_header_rate_tab(i).price;
1210 g_freight_choices_tbl(j).lane_id := l_fte_header_rate_tab(i).lane_id ; -- bug 4408958
1211
1212 j := j + 1;
1213 END IF; -- If l_line_tbl.count > 0
1214 I := l_fte_header_rate_tab.NEXT(I);
1215 l_line_tbl.DELETE;
1216 l_Adjusted_amount := 0;
1217 END LOOP; --while i is not null loop
1218
1219 g_fte_source_line_rate_tab := l_fte_line_rate_tab;
1220 --This Global table is used in Procedure Process_Freight_Choices
1221
1222 END IF; --if l_fte_source_line_tab.count > 0
1223
1224 x_return_status := FND_API.G_RET_STS_SUCCESS;
1225
1226 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Freight_Choices...');
1227
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'ERROR IN PROCEDURE Get_Freight_Choices: '||SUBSTR ( SQLERRM , 1 , 240 ) , 3 ) ;
1233 END IF;
1234 IF l_debug_level > 0 THEN
1235 oe_debug_pub.add( 'UNEXPECTED ERROR IN Get_Freight_Choices :'||SQLERRM , 3 ) ;
1236 END IF;
1237 /* IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1238 THEN
1239 OE_MSG_PUB.Add_Exc_Msg
1240 ( G_PKG_NAME,
1241 'Get_Freight_Choices');
1242 END IF; */
1243
1244 END Get_Freight_Choices;
1245
1246
1247 PROCEDURE Get_Freight_Choices_Tbl
1248 (x_freight_choices_tbl IN OUT NOCOPY /* file.sql.39 change */ freight_choices_tbl_type)
1249 IS
1250
1251 BEGIN
1252
1253 For I IN g_freight_choices_Tbl.FIRST .. g_freight_choices_tbl.LAST LOOP
1254 oe_debug_pub.add(' Value of Transit Time Uom .. '||g_freight_choices_tbl(i).transit_time_uom);
1255 x_freight_choices_tbl(i).consolidation_id := g_freight_choices_tbl(i).consolidation_id;
1256 x_freight_choices_tbl(i).shipping_method := g_freight_choices_tbl(i).shipping_method;
1257 x_freight_choices_tbl(i).shipping_method_code
1258 := g_freight_choices_tbl(i).shipping_method_code;
1259 x_freight_choices_tbl(i).transit_time := g_freight_choices_tbl(i).transit_time;
1260 x_freight_choices_tbl(i).transit_time_uom := g_freight_choices_tbl(i).transit_time_uom;
1261 x_freight_choices_tbl(i).charge_amount := g_freight_choices_tbl(i).charge_amount;
1262 x_freight_choices_tbl(i).cost := g_freight_choices_tbl(i).cost;
1263 x_freight_choices_tbl(i).lane_id := g_freight_choices_tbl(i).lane_id; --bug 4408958
1264
1265 END LOOP;
1266
1267 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Freight_Choices_Tbl...');
1268
1269 END Get_Freight_Choices_Tbl;
1270
1271
1272 PROCEDURE Get_Shipment_Details_Tbl
1273 (x_Line_Shipment_Details_tbl IN OUT NOCOPY /* file.sql.39 change */ line_Shipment_Details_tbl_type)
1274 IS
1275
1276 BEGIN
1277
1278
1279 For I IN g_line_shipment_details_tbl.FIRST .. g_line_shipment_details_tbl.LAST LOOP
1280 x_Line_Shipment_Details_tbl(i).source_line_id := g_line_shipment_details_tbl(i).source_line_id;
1281 x_Line_Shipment_Details_tbl(i).inventory_item_id := g_line_shipment_details_tbl(i).inventory_item_id;
1282 x_Line_Shipment_Details_tbl(i).source_quantity
1283 := g_line_shipment_details_tbl(i).source_quantity;
1284 x_Line_Shipment_Details_tbl(i).source_quantity_uom := g_line_shipment_details_tbl(i).source_quantity_uom;
1285 x_Line_Shipment_Details_tbl(i).ship_date := g_line_shipment_details_tbl(i).ship_date;
1286 x_Line_Shipment_Details_tbl(i).arrival_date := g_line_shipment_details_tbl(i).arrival_date;
1287
1288 END LOOP;
1289
1290 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Get_Shipment_Details_Tbl...');
1291
1292 END Get_Shipment_Details_Tbl;
1293
1294 --Bug 6186084
1295 PROCEDURE Repopulate_Freight_Choices
1296 (x_volume OUT NOCOPY NUMBER,
1297 x_weight OUT NOCOPY NUMBER,
1298 x_consolidation_id IN NUMBER)
1299 IS
1300
1301 BEGIN
1302 oe_debug_pub.add('Entering Repopulate_Freight_Choices');
1303
1304 IF g_shipment_summary_tbl.count >0 THEN
1305 x_weight := g_shipment_summary_tbl(x_consolidation_id).total_weight;
1306 x_volume := g_shipment_summary_tbl(x_consolidation_id).total_volume;
1307 END IF;
1308
1309 oe_debug_pub.add('Exiting the Repopulate_Freight_Choices proc');
1310
1311 END Repopulate_Freight_Choices;
1312
1313 PROCEDURE Process_Freight_Choices
1314 ( p_header_id IN NUMBER
1315 ,p_consolidation_id IN NUMBER
1316 ,p_ship_method_code IN VARCHAR2 -- ..This New parameter is added
1317 ,p_lane_id IN NUMBER --bug 4408958
1318 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1319 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1320 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1321 IS
1322
1323 l_freight_choices_rec OE_Freight_Choices_PVT.Freight_Choices_Rec_type;
1324 l_fte_line_rate_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_Tab;
1325 l_fte_rates_rec FTE_PROCESS_REQUESTS.Fte_Source_Line_Rates_rec;
1326 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
1327 l_fte_source_line_tab FTE_PROCESS_REQUESTS.Fte_Source_Line_Tab;
1328 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1329 query_line_tbl OE_Order_PUB.Line_Tbl_Type;
1330 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
1331 l_control_rec OE_GLOBALS.Control_Rec_Type;
1332 l_line_rec OE_ORDER_PUB.line_rec_type ;
1333 l_bulk_adj_rec OE_Freight_Rating_PVT.Bulk_Line_Adj_Rec_Type;
1334 l_count Number;
1335 l_fte_count Number := 1;
1336 i Number := 1;
1337 j Number;
1338 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1339 M Number;
1340 K Number;
1341 l_order_source_id NUMBER;
1342 l_orig_sys_document_ref VARCHAR2(50);
1343 l_orig_sys_line_ref VARCHAR2(50);
1344 l_orig_sys_shipment_ref VARCHAR2(50);
1345 l_change_sequence VARCHAR2(50);
1346 l_source_document_id NUMBER;
1347 l_source_document_line_id NUMBER;
1348 l_source_document_type_id NUMBER;
1349 l_ship_method_code VARCHAR2(30);
1350 l_lane_id NUMBER := 0;
1351 l_meaning VARCHAR2(50);
1352 l_return_status VARCHAR2(10);
1353 l_pricing_event VARCHAR2(30);
1354 l_index NUMBER := 1;
1355 l_adj_index NUMBER;
1356 l_line_id NUMBER;
1357 l_header_id NUMBER;
1358 l_config_line_exists NUMBER := 0;
1359
1360 deleted_costs number_type;
1361
1362 CURSOR C_CONFIG_ITEM_PARENTS(p_ato_line_id IN NUMBER) IS
1363 SELECT opa.price_adjustment_id,ool.line_id,
1364 opa.adjusted_amount, opa.list_line_type_code,
1365 opa.charge_type_code
1366 FROM oe_order_lines_all ool
1367 ,oe_price_adjustments opa
1368 WHERE opa.charge_type_code IN ('FTEPRICE','FTECHARGE')
1369 AND list_line_type_code = 'COST' -- For bug 7043225
1370 AND ool.line_id = opa.line_id
1371 AND ool.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG
1372 AND ool.ato_line_id = p_ato_line_id;
1373
1374 /*CURSOR C_CONFIG_LINE_EXISTS(p_line_id IN NUMBER) IS
1375 SELECT 1 from oe_order_lines_all
1376 WHERE ato_line_id = p_line_id
1377 AND item_type_code = OE_GLOBALS.G_ITEM_CONFIG;
1378 */
1379 BEGIN
1380
1381 oe_debug_pub.add(' Entering the procedure process freight choices');
1382
1383 Savepoint Cancel_All;
1384 oe_debug_pub.add('first Consolidation Id got '|| g_fte_header_rate_tab(i).Consolidation_id);
1385
1386 I := g_fte_header_rate_tab.FIRST;
1387 WHILE I IS NOT NULL LOOP
1388 If g_fte_header_rate_tab(i).Consolidation_id = p_Consolidation_id and
1389 g_fte_header_rate_tab(i).Ship_method_code = p_ship_method_code and --bug 4408958
1390 g_fte_header_rate_tab(i).lane_id = p_lane_id then
1391 oe_debug_pub.add('In the first I loop');
1392 Exit;
1393 End If;
1394 I := g_fte_header_rate_tab.NEXT(I);
1395 End Loop;
1396
1397
1398 oe_debug_pub.add('Consolidation Id got '|| g_fte_header_rate_tab(i).Consolidation_id);
1399
1400 M := g_fte_header_rate_tab(I).first_line_index;
1401
1402 K := 0;
1403
1404 For j in g_fte_line_rate_tab.FIRST .. g_fte_line_rate_tab.LAST LOOP
1405 oe_debug_pub.add( ' Line Ids: value of g_fte_line_rate_tab' || g_fte_line_rate_tab(j).source_line_id);
1406 oe_debug_pub.add( ' Ship method ids : '||g_fte_line_rate_tab(j).ship_method_code);
1407 End loop;
1408
1409 While M is not null loop
1410
1411 IF g_fte_line_rate_tab(M).ship_method_code = p_ship_method_code and
1412 --and g_fte_line_rate_tab(M).lane_id = l_lane_id
1413 g_fte_line_rate_tab(M).consolidation_id = p_consolidation_id
1414 THEN
1415
1416 IF deleted_costs.EXISTS(g_fte_line_rate_tab(M).source_line_id) THEN
1417 NULL;
1418 ELSE
1419 DELETE FROM OE_PRICE_ADJUSTMENTS
1420 WHERE line_ID = g_fte_line_rate_tab(M).source_line_id
1421 AND CHARGE_TYPE_CODE IN ('FTEPRICE','FTECHARGE')
1422 AND list_line_type_code = 'COST'
1423 AND ESTIMATED_FLAG = 'Y';
1424
1425 deleted_costs(g_fte_line_rate_tab(M).source_line_id) := g_fte_line_rate_tab(M).source_line_id;
1426
1427 oe_line_util.query_rows
1428 (p_line_id => g_fte_line_rate_tab(M).source_line_id
1429 ,x_line_tbl => query_line_tbl );
1430
1431 K := K + 1;
1432
1433 oe_debug_pub.add('The line ids for the lines added '|| g_fte_line_rate_tab(M).source_line_id);
1434 l_old_line_tbl(k) := query_line_tbl(1);
1435
1436 /* renga-review */
1437 For j in g_fte_source_line_tab.first .. g_fte_source_line_tab.Last Loop
1438 If g_fte_source_line_tab(j).source_line_id = g_fte_line_rate_tab(M).source_line_id Then
1439 l_count := j;
1440 Exit;
1441 End If;
1442 End Loop;
1443
1444
1445 IF (g_fte_line_rate_tab(M).ship_method_code) <>
1446 nvl(query_line_tbl(1).shipping_method_code,'-99')
1447 THEN
1448 IF l_debug_level > 0 THEN
1449 Null;
1450 END IF;
1451 oe_debug_pub.add('value of carrier freight code '||g_fte_line_rate_tab(M).carrier_freight_code);
1452 query_line_tbl(1).shipping_method_code :=
1453 g_fte_line_rate_tab(M).ship_method_code;
1454
1455 query_line_tbl(1).freight_carrier_code :=
1456 g_fte_line_rate_tab(M).carrier_freight_code;
1457
1458 query_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
1459
1460 /* Start Audit Trail */
1461
1462 query_line_tbl(1).change_reason := 'SYSTEM';
1463 query_line_tbl(1).change_comments := 'Choose Ship Method';
1464
1465 /* End Audit Trail */
1466
1467
1468 ELSE -- no change, set the operation to none.
1469 IF l_debug_level > 0 THEN
1470 oe_debug_pub.add('No Changes to save', 4);
1471 END IF;
1472 /* renga-review */
1473 --l_line_rec.operation := OE_GLOBALS.G_OPR_NONE;
1474
1475 query_line_tbl(1).operation := OE_GLOBALS.G_OPR_NONE;
1476
1477 --x_no_opr_count := x_no_opr_count + 1;
1478
1479 End If;
1480
1481 -- cascade to CONFIG line, we need to do this irrespective
1482 -- of the fte source line ret status because we need to
1483 -- push the offset anyway.
1484
1485 IF g_config_count > 0 AND l_index <= g_config_count THEN
1486
1487 IF query_line_tbl(1).ato_line_id =
1488 query_line_tbl(1).line_id THEN
1489
1490 -- This is an ATO Model
1491 IF l_debug_level > 0 THEN
1492 oe_debug_pub.add('ato model '||query_line_tbl(1).line_id,3);
1493 END IF;
1494
1495 -- added by Renga after review
1496 IF query_line_tbl(1).ato_line_id is not null then
1497
1498 select count(*) into l_config_line_exists
1499 from oe_order_lines_all
1500 where ato_line_id = query_line_tbl(1).ato_line_id
1501 and item_type_code = 'CONFIG';
1502
1503 END IF; --ato_line_id is not null
1504
1505
1506 IF g_line_tbl(l_index).ato_line_id =
1507 query_line_tbl(1).line_id
1508 THEN
1509
1510 IF l_debug_level > 0 THEN
1511 oe_debug_pub.add('cfg line '|| g_line_tbl(l_index).line_id, 3);
1512 END IF;
1513
1514 IF query_line_tbl(1).operation = OE_GLOBALS.G_OPR_UPDATE
1515 THEN
1516
1517 g_line_tbl(l_index).shipping_method_code :=
1518 query_line_tbl(1).shipping_method_code;
1519
1520 g_line_tbl(l_index).freight_carrier_code :=
1521 query_line_tbl(1).freight_carrier_code;
1522
1523 /* Start Audit Trail */
1524
1525 g_line_tbl(l_index).change_reason := 'SYSTEM';
1526 g_line_tbl(l_index).change_comments := 'Get Ship Method Action';
1527
1528 /* End Audit Trail */
1529
1530 IF l_debug_level > 0 THEN
1531 oe_debug_pub.Add('Cascading Ship Method from:' ||
1532 query_line_tbl(1).line_id ||' to ' ||
1533 g_line_tbl(l_index).line_id,3);
1534 END IF;
1535
1536 END IF;
1537 IF l_debug_level > 0 THEN
1538 oe_debug_pub.add
1539 ('cfg opr '||g_line_tbl(l_index).operation, 3);
1540 END IF;
1541
1542 g_line_tbl(l_index).operation:=
1543 query_line_tbl(1).operation;
1544
1545 l_index := l_index + 1;
1546
1547 END IF;
1548 END IF;
1549 END IF;
1550
1551 -- added here also
1552
1553 -- Even though the ship method did not change, rates could have
1554 -- changed. Hence registering the line, inspite of no change.
1555
1556 -- to register changed line so that repricing for this line
1557 -- would happen.
1558 oe_debug_pub.add('Register changed line: '||l_line_rec.line_id,1);
1559 OE_LINE_ADJ_UTIL.Register_Changed_Lines
1560 (p_line_id => query_line_tbl(1).line_id,
1561 p_header_id => query_line_tbl(1).header_id,
1562 p_operation => OE_GLOBALS.G_OPR_UPDATE);
1563
1564
1565 END IF; -- if deleted_costs...
1566 /* Commented for bug 7043225. We now need to allow charges to get created on ATO Model,
1567 Even if config item is not yet created
1568
1569 IF (query_line_tbl(1).ato_line_id =
1570 query_line_tbl(1).line_id ) THEN
1571 IF l_config_line_exists = 1 THEN
1572
1573 Prepare_Adj_Detail
1574 (p_header_id => query_line_tbl(1).header_id
1575 ,p_line_id => g_fte_line_rate_tab(M).source_line_id
1576 ,p_fte_rates_rec => g_fte_line_rate_tab(M)
1577 ,x_line_adj_rec => l_line_adj_rec
1578 ,x_return_status => l_return_status
1579 );
1580
1581 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1582
1583 l_config_line_exists := 0;
1584
1585 END IF;
1586
1587 ELSE
1588 */
1589
1590 Prepare_Adj_Detail
1591 (p_header_id => query_line_tbl(1).header_id
1592 ,p_line_id => g_fte_line_rate_tab(M).source_line_id
1593 ,p_fte_rates_rec => g_fte_line_rate_tab(M)
1594 ,x_line_adj_rec => l_line_adj_rec
1595 ,x_return_status => l_return_status
1596 );
1597
1598 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1599
1600 -- END IF;--bug 7043225
1601
1602 l_line_tbl(K) := query_line_tbl(1);
1603 /* renga-review: no need to do the following as we have done it
1604 for query_line_tbl in the if condition already */
1605
1606 M := g_fte_line_rate_tab.NEXT(M);
1607
1608 ELSE
1609 Exit;
1610 End If;
1611
1612 End Loop;
1613
1614
1615 l_header_id := l_line_tbl(1).header_id;
1616 l_index := 1;
1617 l_adj_index := 1;
1618
1619 WHILE l_index <= g_config_count LOOP
1620
1621 l_line_id := g_line_tbl(l_index).line_id;
1622 IF l_debug_level > 0 THEN
1623 oe_debug_pub.add( 'CASCADING ADJUSTMENT LINES TO CONFIG LINES. ' , 3 );
1624 END IF;
1625
1626 OPEN C_CONFIG_ITEM_PARENTS(g_line_tbl(l_index).ato_line_id);
1627 FETCH C_CONFIG_ITEM_PARENTS BULK COLLECT INTO
1628 l_bulk_adj_rec.price_adjustment_id
1629 ,l_bulk_adj_rec.line_id
1630 ,l_bulk_adj_rec.adjusted_amount
1631 ,l_bulk_adj_rec.list_line_type_code
1632 ,l_bulk_adj_rec.charge_type_code
1633 ;
1634
1635 CLOSE C_CONFIG_ITEM_PARENTS;
1636
1637 FOR i in 1..l_bulk_adj_rec.price_adjustment_id.COUNT LOOP
1638
1639 l_fte_rates_rec.cost_type := l_bulk_adj_rec.charge_type_code(i);
1640 l_fte_rates_rec.adjusted_price := l_bulk_adj_rec.adjusted_amount(i);
1641
1642 oe_debug_pub.add( ' in the config item loops .. line_id '||l_bulk_adj_rec.line_id(i));
1643
1644 Prepare_Adj_Detail
1645 (p_header_id => l_header_id
1646 ,p_line_id => l_line_id
1647 ,p_fte_rates_rec => l_fte_rates_rec
1648 ,x_line_adj_rec => l_line_adj_rec
1649 ,x_return_status => l_return_status
1650 );
1651
1652 --l_line_adj_tbl(l_adj_index) := l_line_adj_rec;
1653
1654 -- inserting for the config line
1655 OE_LINE_ADJ_UTIL.INSERT_ROW(l_line_adj_rec);
1656
1657 -- register changed line for config item line.
1658 OE_LINE_ADJ_UTIL.Register_Changed_Lines
1659 (p_line_id => l_line_id,
1660 p_header_id => l_header_id,
1661 p_operation => OE_GLOBALS.G_OPR_UPDATE);
1662
1663 -- deleting the parents of the config line.
1664 -- these deleted parent lines have been registered in
1665 -- previous loop looping through p_fte_rates_tab, so
1666 -- no need to register changed line again for these lines.
1667
1668 DELETE FROM oe_price_adjustments
1669 WHERE price_adjustment_id = l_bulk_adj_rec.price_adjustment_id(i);
1670
1671 l_adj_index := l_adj_index + 1;
1672
1673 END LOOP;
1674
1675 oe_line_util.query_rows
1676 (p_line_id => l_line_id
1677 ,x_line_tbl => query_line_tbl );
1678
1679 K := K + 1;
1680
1681 oe_debug_pub.add('The config lines added '|| l_line_id);
1682 l_line_tbl(k) := query_line_tbl(1);
1683
1684 l_index := l_index + 1;
1685
1686 END LOOP;
1687
1688 -- for ATO lines, only send config lines to Pricing.
1689 -- Commented for bug 7043225, as we want all the charges to get applied on ATO Model even before,
1690 -- config item is created. Hence we need to pass the CPF as 'Y' for all lines.
1691 -- Else the charges that got inserted on these items will never get displayed in the UI
1692 /* J := l_line_tbl.FIRST;
1693 WHILE J IS NOT NULL LOOP
1694 -- delete those non-shippable ATO parent lines
1695 IF l_line_tbl(J).ato_line_id IS NOT NULL
1696 AND l_line_tbl(J).item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
1697 l_line_tbl(J).calculate_price_flag := 'N';
1698 oe_debug_pub.add('ATO item price flag set to N :'||l_line_tbl(J).line_id);
1699 END IF;
1700 J := l_line_tbl.NEXT(J);
1701 END LOOP;*/
1702
1703 For j in l_line_tbl.FIRST .. l_line_tbl.LAST LOOP
1704 oe_debug_pub.add( ' Lines passed to pricing Ids: value of l_line_tbl' || l_line_tbl(j).line_id);
1705 End loop;
1706
1707 IF (Nvl(get_list_line_type_code(p_header_id),0) = 0 ) THEN
1708
1709 Create_Dummy_Adjustment
1710 (p_header_id => p_header_id
1711 );
1712
1713 END IF;
1714
1715 l_control_rec.default_attributes := TRUE;
1716 l_control_rec.controlled_operation := TRUE;
1717 l_control_rec.change_attributes := TRUE;
1718 l_control_rec.validate_entity := TRUE;
1719 l_control_rec.write_to_DB := TRUE;
1720 l_control_rec.process_entity := OE_GLOBALS.G_ENTITY_LINE;
1721
1722 --l_line_tbl.operation := OE_GLOBALS.G_OPR_UPDATE;
1723
1724 OE_ORDER_PVT.Lines
1725 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
1726 ,p_control_rec => l_control_rec
1727 ,p_x_line_tbl => l_line_tbl
1728 ,p_x_old_line_tbl => l_old_line_tbl
1729 ,x_return_status => x_return_status);
1730
1731 IF l_debug_level > 0 THEN
1732 oe_debug_pub.Add('After Calling Process Order...'||x_return_status,3);
1733 END IF; -- bug7433107
1734
1735 -- Logging a delayed request for Price Line in BATCH mode.
1736
1737 l_pricing_event := 'BATCH';
1738
1739 OE_delayed_requests_Pvt.log_request(
1740 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1741 p_entity_id => p_header_id,
1742 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1743 p_requesting_entity_id => p_header_id,
1744 p_request_unique_key1 => l_pricing_event,
1745 p_param1 => p_header_id,
1746 p_param2 => l_pricing_event,
1747 p_request_type => OE_GLOBALS.G_PRICE_ORDER,
1748 x_return_status => l_return_status);
1749
1750
1751 IF l_debug_level > 0 THEN -- bug7433107
1752 OE_DEBUG_PUB.Add('Before Calling Process Requests and Notify',3);
1753 END IF;
1754
1755 OE_ORDER_PVT.Process_Requests_And_notify
1756 ( p_process_requests => TRUE
1757 ,p_notify => TRUE
1758 ,x_return_status => x_return_status
1759 ,p_line_tbl => l_line_tbl
1760 ,p_old_line_tbl => l_old_line_tbl);
1761
1762 IF l_debug_level > 0 THEN
1763 OE_DEBUG_PUB.Add('After Calling Process Requests and Notify...'|| x_return_status,3);
1764 END IF;
1765
1766 Print_Time('Exiting OE_FREIGHT_CHOICES_PVT.Process_Freight_Choices...');
1767
1768 EXCEPTION
1769
1770 WHEN FND_API.G_EXC_ERROR THEN
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.Add('Expected Error in Process Freight Choices', 1);
1773 END IF;
1774
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776
1777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1778 IF l_debug_level > 0 THEN
1779 oe_debug_pub.Add('Unexpected Error in Process Freight Choices'||
1780 sqlerrm, 2);
1781 END IF;
1782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1783
1784 WHEN OTHERS THEN
1785
1786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1787
1788 /* IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1789 THEN
1790 OE_MSG_PUB.Add_Exc_Msg
1791 ( G_PKG_NAME,
1792 'Process_Freight_Choices');
1793 END IF; */
1794
1795 END Process_Freight_Choices;
1796
1797 PROCEDURE Cancel_all Is
1798
1799 NO_SAVEPOINT EXCEPTION;
1800 PRAGMA EXCEPTION_INIT(NO_SAVEPOINT, -1086);
1801
1802 Begin
1803 oe_debug_pub.add('Entering Cancel all');
1804 Rollback to Savepoint Cancel_All;
1805
1806 -- bug 5883660
1807 EXCEPTION WHEN NO_SAVEPOINT THEN
1808 Null;
1809 End Cancel_all;
1810
1811 END OE_FREIGHT_CHOICES_PVT;