1 PACKAGE BODY OE_CHARGE_PVT AS
2 /* $Header: OEXVCHRB.pls 120.2 2005/11/02 14:45:11 sdatti ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Charge_PVT';
7
8 -- Start of Comments
9 -- API name Get_Charge_Amount
10 --
11 -- Procedure to get charge totals at Order Line or Order Header level
12 -- If the header_id is passed and line_id is NULL then total for charges at
13 -- Order Header level is returned
14 -- If header_id and line_id is passed then total for charges at Order line
15 -- level is returned.
16 --
17 -- Type Public
18 --
19 -- Pre-reqs
20 --
21 -- Parameters
22 --
23 -- Version Current version = 1.0
24 -- Initial version = 1.0
25 --
26 -- Notes
27 --
28 -- End of Comments
29
30 PROCEDURE Get_Charge_Amount
31 ( p_api_version_number IN NUMBER
32 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
33 , p_header_id IN NUMBER
34 , p_line_id IN NUMBER
35 , p_all_charges IN VARCHAR2 := FND_API.G_FALSE
36 , x_return_status OUT NOCOPY VARCHAR2
37
38 , x_msg_count OUT NOCOPY NUMBER
39
40 , x_msg_data OUT NOCOPY VARCHAR2
41
42 , x_charge_amount OUT NOCOPY NUMBER
43
44 )
45 IS
46 l_api_version_number CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(30):= 'Get_Charge_Amount';
48 l_charge_amount NUMBER := 0.0;
49 l_hdr_charge_amount NUMBER := 0.0;
50 l_line_charge_amount NUMBER := 0.0;
51 Is_fmt BOOLEAN;
52 --
53 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
54 --
55 BEGIN
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57
58 -- Check for Header Id
59
60 IF p_header_id is NULL OR p_header_id = FND_API.G_MISS_NUM THEN
61
62 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
63 THEN
64
65 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
66 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','header');
67 OE_MSG_PUB.Add;
68
69 END IF;
70 RAISE FND_API.G_EXC_ERROR;
71
72 END IF;
73
74 IF NVL(p_header_id,-1)<>NVL(OE_ORDER_UTIL.G_Header_id,-10)
75 OR OE_ORDER_UTIL.G_Precision IS NULL THEN
76 Is_fmt:= OE_ORDER_UTIL.Get_Precision(
77 p_header_id=>p_header_id
78 );
79 END IF;
80
81 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
82 OE_ORDER_UTIL.G_Precision:=2;
83 END IF;
84
85
86
87 -- Check the operation whether all charges for the Order are required
88 IF p_all_charges = FND_API.G_TRUE THEN
89 -- Getting Order Total charge amount.
90
91 -- bug 4060810, improve performance of SQL, with fix for bug 2785662
92 SELECT SUM(nvl(ROUND(
93 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-1,1) *
94 DECODE(P.LINE_ID, NULL,
95 P.OPERAND,
96 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
97 DECODE(L.ORDERED_QUANTITY,0,0,NULL,NULL,P.OPERAND),
98 L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
99 ,OE_ORDER_UTIL.G_Precision),0))
100 INTO l_charge_amount
101 FROM OE_PRICE_ADJUSTMENTS P,
102 OE_ORDER_LINES_ALL L
103 WHERE P.HEADER_ID = p_header_id
104 AND P.LINE_ID = L.LINE_ID(+)
105 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
106 AND L.charge_periodicity_code(+) IS NULL -- added for recurring charge
107 AND P.APPLIED_FLAG = 'Y';
108
109 /*
110 SELECT SUM(CHARGE_AMOUNT)
111 INTO l_charge_amount
112 FROM OE_CHARGE_LINES_V
113 WHERE header_id = p_header_id;
114 */
115
116
117 -- If the line_id is NULL and Header_id is not null then header
118 -- level charges are required.
119
120 ELSIF p_line_id is NULL OR p_line_id = FND_API.G_MISS_NUM THEN
121
122 -- Getting Header level charge amount.
123
124 SELECT SUM(ROUND(
125 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND)
126 ,OE_ORDER_UTIL.G_Precision)
127 )
128 INTO l_charge_amount
129 FROM OE_PRICE_ADJUSTMENTS P
130 WHERE P.HEADER_ID = p_header_id
131 AND P.LINE_ID IS NULL
132 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
133 AND P.APPLIED_FLAG = 'Y';
134
135 /*
136 SELECT SUM(CHARGE_AMOUNT)
137 INTO l_charge_amount
138 FROM OE_CHARGE_LINES_V
139 WHERE header_id = p_header_id
140 AND line_id IS NULL;
141 */
142
143 ELSE
144
145 -- Getting Line level charge amount.
146
147 SELECT SUM(ROUND(
148 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C', -1, 1) *
149 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
150 DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
151 (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
152 ,OE_ORDER_UTIL.G_Precision)
153 )
154 INTO l_charge_amount
155 FROM OE_PRICE_ADJUSTMENTS P,
156 OE_ORDER_LINES_ALL L
157 WHERE P.HEADER_ID = p_header_id
158 AND P.LINE_ID = p_line_id
159 AND P.LINE_ID = L.LINE_ID
160 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
161 AND P.APPLIED_FLAG = 'Y';
162
163 /*
164 SELECT SUM(CHARGE_AMOUNT)
165 INTO l_charge_amount
166 FROM OE_CHARGE_LINES_V
167 WHERE header_id = p_header_id
168 AND line_id = p_line_id;
169 */
170
171 END IF;
172 IF l_charge_amount IS NULL THEN
173 l_charge_amount := 0.0;
174 END IF;
175 x_charge_amount := l_charge_amount;
176 EXCEPTION
177
178 WHEN FND_API.G_EXC_ERROR THEN
179
180 x_return_status := FND_API.G_RET_STS_ERROR;
181
182 -- Get message count and data
183
184 OE_MSG_PUB.Count_And_Get
185 ( p_count => x_msg_count
186 , p_data => x_msg_data
187 );
188
189 WHEN OTHERS THEN
190
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192
193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg
196 ( G_PKG_NAME
197 , 'Get_Charge_Amount'
198 );
199 END IF;
200
201 -- Get message count and data
202
203 OE_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count
205 , p_data => x_msg_data
206 );
207
208 END Get_Charge_Amount;
209
210 -- Start of Comments
211 -- Function name Get_Cost_Amount
212 --
213 -- Function to source the Pricing Attributes for COST_AMOUNTS.
214 -- (E.g. INSURANCE_COST, HANDLING_COST, DUTY_COST, EXPORT_COST)
215 -- If the Order line is Shippable and shipping has transferred all costs for
216 -- this line, then this function takes the cost_type_code as an input and finds
217 -- the cost amount for this cost_type_code from OE_PRICE_ADJUSTMENTS table.
218 --
219 -- Type Private
220 --
221 -- Pre-reqs
222 --
223 -- Parameters
224 --
225 -- Version Current version = 1.0
226 -- Initial version = 1.0
227 --
228 -- Notes
229 --
230 -- End of Comments
231
232 FUNCTION Get_Cost_Amount
233 ( p_cost_type_code IN VARCHAR2
234 )RETURN VARCHAR2
235 IS
236 l_api_version_number CONSTANT NUMBER := 1.0;
237 l_api_name CONSTANT VARCHAR2(30):= 'Get_Cost_Amount';
238 l_line_rec OE_Order_PUB.Line_Rec_Type;
239 l_cost_amount NUMBER := 0.0;
240 --
241 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
242 --
243 BEGIN
244
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add( 'CHARGES:INSIDE GET COST AMOUNT FOR' || P_COST_TYPE_CODE , 1 ) ;
247 END IF;
248
249 -- Get the Line record from the Global Record
250 l_line_rec := OE_ORDER_PUB.G_LINE;
251
252 -- Validate the Line_id.
253
254 IF l_line_rec.line_id is NULL OR l_line_rec.line_id = FND_API.G_MISS_NUM
255 THEN
256 IF l_debug_level > 0 THEN
257 oe_debug_pub.add( 'L_LINE_REC.LINE_ID IS NULL OR L_LINE_REC.LINE_ID IS FND_API.G_MISS_NUM' ) ;
258 END IF;
259 IF l_debug_level > 0 THEN
260 oe_debug_pub.add( 'EXITING CHARGES' ) ;
261 END IF;
262 RETURN NULL;
263 END IF;
264
265 -- Check for values of cost_type_code
266
267 IF p_cost_type_code is NULL OR p_cost_type_code = FND_API.G_MISS_CHAR THEN
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( 'P_COST_TYPE_CODE IS NULL OR P_COST_TYPE_CODE IS FND_API.G_MISS_CHAR' ) ;
270 END IF;
271 IF l_debug_level > 0 THEN
272 oe_debug_pub.add( 'EXITING CHARGES' ) ;
273 END IF;
274 RETURN NULL;
275 END IF;
276
277 -- Check for Pricing Quantity
278
279 IF l_line_rec.pricing_quantity IS NULL OR
280 l_line_rec.pricing_quantity = FND_API.G_MISS_NUM OR
281 l_line_rec.pricing_quantity <= 0 THEN
282 IF l_debug_level > 0 THEN
283 oe_debug_pub.add( 'L_LINE_REC.PRICING_QUANTITY = FND_API.G_MISS_NUM OR _LINE_REC.PRICING_QUANTITY <= 0' ) ;
284 END IF;
285 IF l_debug_level > 0 THEN
286 oe_debug_pub.add( 'EXITING CHARGES' ) ;
287 END IF;
288 RETURN NULL;
289 END IF;
290
291 -- Check whether the line is shippable and has got shipped
292
293 IF l_line_rec.shippable_flag = 'Y' AND
294 l_line_rec.shipped_quantity > 0 THEN
295
296 -- Cost records are stored in OE_PRICE_ADJUSTMENTS table with
297 -- list_line_type_code = 'COST'
298 IF l_debug_level > 0 THEN
299 oe_debug_pub.add( 'LINE IS SHIPPABLE AND IS SHIPPED' ) ;
300 END IF;
301 SELECT SUM(ADJUSTED_AMOUNT)
302 INTO l_cost_amount
303 FROM OE_PRICE_ADJUSTMENTS_V
304 WHERE LINE_ID = l_line_rec.line_id
305 AND LIST_LINE_TYPE_CODE = 'COST'
306 AND CHARGE_TYPE_CODE = p_cost_type_code;
307
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'AFTER GETTING COST AMOUNT ' || TO_CHAR ( L_COST_AMOUNT ) , 1 ) ;
310 END IF;
311
312 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(l_cost_amount);
313 ELSE
314 IF l_debug_level > 0 THEN
315 oe_debug_pub.add( 'LINE NOT SHIPPABLE OR IS NOT SHIPPED' ) ;
316 END IF;
317 RETURN NULL;
318 END IF;
319 IF l_debug_level > 0 THEN
320 oe_debug_pub.add( 'LEAVING CHARGES' ) ;
321 END IF;
322 EXCEPTION
323
324 WHEN NO_DATA_FOUND THEN
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add( 'NO COST INFORMATION FOUND IN OE_PRICE_ADJUSTMENTS' ) ;
327 END IF;
328 RETURN NULL;
329
330 WHEN OTHERS THEN
331
332 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
333 THEN
334 FND_MSG_PUB.Add_Exc_Msg
335 ( G_PKG_NAME
336 , 'Get_Cost_Amount'
337 );
338 END IF;
339 IF l_debug_level > 0 THEN
340 oe_debug_pub.add( 'UNEXCPETED ERRORS:'||SQLERRM ) ;
341 END IF;
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END Get_Cost_Amount;
344
345 -- Start of Comments
346 -- Function name Get_Cost_Types
347 --
348 -- Function to source the Qualifier Attribute FREIGHT_COST_TYPE
349 -- If the Order line is Shippable and shipping has transferred all costs for
350 -- this line, then this function finds and returns all cost_type_codes from
351 -- OE_PRICE_ADJUSTMENTS table where costs are maintained. It returns a table of
352 -- VARCHAR2 as output.
353 --
354 -- Type Private
355 --
356 -- Pre-reqs
357 --
358 -- Parameters
359 --
360 -- Version Current version = 1.0
361 -- Initial version = 1.0
362 --
363 -- Notes
364 --
365 -- End of Comments
366
367 FUNCTION Get_Cost_Types
368 RETURN QP_Attr_Mapping_PUB.t_MultiRecord
369 IS
370 l_api_name CONSTANT VARCHAR2(30):= 'Get_Cost_Types';
371 l_cost_tbl QP_Attr_Mapping_PUB.t_MultiRecord;
372 l_cost_type_code VARCHAR2(30) := NULL;
373 l_count NUMBER := 0;
377 FROM OE_PRICE_ADJUSTMENTS_V
374 l_line_rec OE_Order_PUB.Line_Rec_Type;
375 Cursor C_Get_Cost_Types(p_line_id NUMBER) IS
376 SELECT DISTINCT CHARGE_TYPE_CODE
378 WHERE LINE_ID = p_line_id
379 AND LIST_LINE_TYPE_CODE = 'COST';
380 --
381 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
382 --
383 BEGIN
384
385 IF l_debug_level > 0 THEN
386 oe_debug_pub.add( 'INSIDE GET COST TYPE' , 1 ) ;
387 END IF;
388 -- Get the Line record from the Global Record
389 l_line_rec := OE_ORDER_PUB.G_LINE;
390
391 IF l_line_rec.line_id is NULL OR l_line_rec.line_id = FND_API.G_MISS_NUM
392 THEN
393 RETURN l_cost_tbl;
394 END IF;
395
396 IF l_line_rec.shippable_flag = 'Y' AND
397 l_line_rec.shipped_quantity > 0
398 THEN
399 l_count := 1;
400 OPEN C_Get_Cost_Types(l_line_rec.line_id);
401 LOOP
402 FETCH C_Get_Cost_Types INTO l_cost_tbl(l_count);
403 EXIT WHEN C_Get_Cost_Types%NOTFOUND;
404 l_count := l_count + 1;
405 END LOOP;
406
407 CLOSE C_Get_Cost_Types;
408
409 IF l_cost_tbl.COUNT > 0 THEN
410 l_count := l_cost_tbl.FIRST;
411 WHILE l_count IS NOT NULL LOOP
412 IF l_debug_level > 0 THEN
413 oe_debug_pub.add( 'COST TYPES ARE'|| L_COST_TBL ( L_COUNT ) , 3 ) ;
414 END IF;
415 l_count := l_cost_tbl.NEXT(l_count);
416 END LOOP;
417 END IF;
418
419 END IF;
420 IF l_debug_level > 0 THEN
421 oe_debug_pub.add( 'AFTER GETTING COST TYPE SUCCESSFULLY' , 1 ) ;
422 END IF;
423
424 RETURN l_cost_tbl;
425
426 EXCEPTION
427
428 WHEN OTHERS THEN
429
430 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
431 THEN
432 FND_MSG_PUB.Add_Exc_Msg
433 ( G_PKG_NAME
434 , 'Get_Cost_Types'
435 );
436 END IF;
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438
439 END Get_Cost_Types;
440
441 -- Start of Comments
442 -- Function name Get_Shipped_Status
443 --
444 -- This function will be used to source the Qualifier Attribute "SHIPPED_FLAG"
445 --
446 -- Type Private
447 --
448 -- Pre-reqs
449 --
450 -- Parameters
451 --
452 -- Version Current version = 1.0
453 -- Initial version = 1.0
454 --
455 -- Notes
456 --
457 -- End of Comments
458
459 FUNCTION Get_Shipped_status
460 RETURN VARCHAR2
461 IS
462 l_api_name CONSTANT VARCHAR2(30):= 'Get_Shipped_Status';
463 l_result VARCHAR2(1) := 'N';
464 l_line_rec OE_Order_PUB.Line_Rec_Type;
465 --
466 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
467 --
468 BEGIN
469
470 -- Get the Line record from the Global Record
471 l_line_rec := OE_ORDER_PUB.G_LINE;
472
473 IF l_line_rec.shippable_flag = 'Y'AND
474 l_line_rec.shipped_quantity > 0
475 THEN
476 l_result := 'Y';
477 END IF;
478 RETURN l_result;
479
480 EXCEPTION
481
482 WHEN OTHERS THEN
483
484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
485 THEN
486 FND_MSG_PUB.Add_Exc_Msg
487 ( G_PKG_NAME
488 , 'Get_Shipped_Status'
489 );
490 END IF;
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END Get_Shipped_Status;
493
494 PROCEDURE Check_Duplicate_Line_Charges
495 (
496 p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
497 , p_x_line_adj_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Tbl_Type
498 , p_x_line_adj_att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Tbl_Type
499 )
500
501 IS
502 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type := p_x_line_adj_tbl;
503 l_line_adj_att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type := p_x_line_adj_att_tbl;
504 l_line_index NUMBER := 0;
505 l_charge_index NUMBER := 0;
506 l_tmp_index NUMBER := 0;
507 l_att_index NUMBER := 0;
508 --
509 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
510 --
511 BEGIN
512 l_line_index := p_line_tbl.FIRST;
513 WHILE l_line_index IS NOT NULL LOOP
514
515 IF l_debug_level > 0 THEN
516 oe_debug_pub.add( 'THE LINE RECORD IS ' || TO_CHAR ( L_LINE_INDEX ) ) ;
517 END IF;
518 l_charge_index := l_Line_Adj_Tbl.FIRST;
519 IF l_debug_level > 0 THEN
520 oe_debug_pub.add( 'THE ADJ LINE RECORD IS ' || TO_CHAR ( L_CHARGE_INDEX ) ) ;
521 END IF;
522
523 WHILE l_charge_index IS NOT NULL LOOP
524
525 IF l_debug_level > 0 THEN
526 oe_debug_pub.add( 'THE ALL CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .CHARGE_TYPE_CODE ) ;
527 END IF;
528 IF l_debug_level > 0 THEN
529 oe_debug_pub.add( 'THE ALL OPERATION IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .OPERATION ) ;
530 END IF;
531 IF l_debug_level > 0 THEN
535 oe_debug_pub.add( 'THE ALL APPLIED_FLAG IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .APPLIED_FLAG ) ;
532 oe_debug_pub.add( 'THE ALL ADJUSTED_AMOUNT IS ' ||TO_CHAR ( L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .ADJUSTED_AMOUNT ) ) ;
533 END IF;
534 IF l_debug_level > 0 THEN
536 END IF;
537 IF l_line_adj_tbl(l_charge_index).list_line_type_code = 'FREIGHT_CHARGE'
538 AND NVL(l_line_adj_tbl(l_charge_index).applied_flag,'N') = 'Y'
539 AND l_line_adj_tbl(l_charge_index).operation IN
540 (OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
541 AND l_line_adj_tbl(l_charge_index).line_id =
542 p_line_tbl(l_line_index).line_id
543 THEN
544
545 IF l_debug_level > 0 THEN
546 oe_debug_pub.add( 'THE SELECTED CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .CHARGE_TYPE_CODE ) ;
547 END IF;
548 IF l_debug_level > 0 THEN
549 oe_debug_pub.add( 'THE SELECTED OPERATION IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .OPERATION ) ;
550 END IF;
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'THE SELECTED ADJUSTED_AMOUNT IS ' ||TO_CHAR ( L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .ADJUSTED_AMOUNT ) ) ;
553 END IF;
554 l_tmp_index := l_line_adj_tbl.FIRST;
555 WHILE l_tmp_index IS NOT NULL LOOP
556
557 IF l_tmp_index <> l_charge_index
558 AND l_line_adj_tbl(l_tmp_index).operation IN
559 (OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
560 AND l_line_adj_tbl(l_tmp_index).list_line_type_code
561 = 'FREIGHT_CHARGE'
562 AND NVL(l_line_adj_tbl(l_tmp_index).applied_flag,'N') = 'Y'
563 AND l_line_adj_tbl(l_charge_index).charge_type_code
564 = l_line_adj_tbl(l_tmp_index).charge_type_code
565 AND NVL(l_line_adj_tbl(l_charge_index).charge_subtype_code,'SUB') =
566 NVL(l_line_adj_tbl(l_tmp_index).charge_subtype_code,'SUB')
567 THEN
568
569 IF l_debug_level > 0 THEN
570 oe_debug_pub.add( 'THE MATCHING CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_TMP_INDEX ) .CHARGE_TYPE_CODE ) ;
571 END IF;
572 IF l_debug_level > 0 THEN
573 oe_debug_pub.add( 'THE MATCHING OPERATION IS ' || L_LINE_ADJ_TBL ( L_TMP_INDEX ) .OPERATION ) ;
574 END IF;
575 IF l_debug_level > 0 THEN
576 oe_debug_pub.add( 'THE MATCHING ADJUSTED_AMOUNT IS ' ||TO_CHAR ( L_LINE_ADJ_TBL ( L_TMP_INDEX ) .ADJUSTED_AMOUNT ) ) ;
577 END IF;
578 IF l_line_adj_tbl(l_tmp_index).adjusted_amount >=
579 l_line_adj_tbl(l_charge_index).adjusted_amount
580 THEN
581 IF l_line_adj_tbl(l_charge_index).operation =
582 OE_GLOBALS.G_OPR_UPDATE
583 THEN
584 IF l_debug_level > 0 THEN
585 oe_debug_pub.add( 'THE SELECTED OPERATION IS SET TO DELETE' ) ;
586 END IF;
587 l_line_adj_tbl(l_charge_index).operation :=
588 OE_GLOBALS.G_OPR_DELETE;
589 ELSE
590 IF l_debug_level > 0 THEN
591 oe_debug_pub.add( 'THE SELECTED OPERATION IS SET TO NONE' ) ;
592 END IF;
593 l_line_adj_tbl(l_charge_index).operation :=
594 OE_GLOBALS.G_OPR_NONE;
595 END IF;
596 EXIT;
597 END IF;
598
599 END IF;
600 l_tmp_index := l_line_adj_tbl.NEXT(l_tmp_index);
601
602 END LOOP; /* For local temp loop */
603
604 IF p_x_line_adj_tbl(l_charge_index).operation <>
605 l_line_adj_tbl(l_charge_index).operation
606 THEN
607
608 l_att_index := l_line_adj_att_tbl.FIRST;
609 WHILE l_att_index IS NOT NULL LOOP
610
611 IF l_line_adj_att_tbl(l_att_index).operation =
612 OE_GLOBALS.G_OPR_UPDATE
613 AND l_line_adj_tbl(l_charge_index).price_adjustment_id =
614 l_line_adj_att_tbl(l_att_index).price_adjustment_id
615 THEN
616
617 l_line_adj_att_tbl(l_att_index).operation :=
618 OE_GLOBALS.G_OPR_DELETE;
619
620 ELSIF l_line_adj_att_tbl(l_att_index).operation =
621 OE_GLOBALS.G_OPR_CREATE
622 AND l_charge_index = l_line_adj_att_tbl(l_att_index).adj_index
623 THEN
624
625 l_line_adj_att_tbl(l_att_index).operation :=
626 OE_GLOBALS.G_OPR_NONE;
627
628 END IF;
629
630 l_att_index := l_line_adj_att_tbl.NEXT(l_att_index);
631 END LOOP; /* For Line Adj Att table */
632
633 END IF;
634 IF l_line_adj_tbl(l_charge_index).operation = OE_GLOBALS.G_OPR_NONE
635 THEN
636 IF l_debug_level > 0 THEN
637 oe_debug_pub.add( 'THE DELETING CHARGE TYPE IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .CHARGE_TYPE_CODE ) ;
638 END IF;
639 IF l_debug_level > 0 THEN
640 oe_debug_pub.add( 'THE DELETING OPERATION IS ' || L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .OPERATION ) ;
641 END IF;
642 IF l_debug_level > 0 THEN
643 oe_debug_pub.add( 'THE DELETING ADJUSTED_AMOUNT IS ' ||TO_CHAR ( L_LINE_ADJ_TBL ( L_CHARGE_INDEX ) .ADJUSTED_AMOUNT ) ) ;
644 END IF;
645 l_line_adj_tbl.delete(l_charge_index);
646 END IF;
647
648 END IF;
649 l_charge_index := l_Line_Adj_Tbl.NEXT(l_charge_index);
650
651 END LOOP; /* For Line Adj table */
652
653 l_line_index := p_Line_Tbl.NEXT(l_line_index);
654
658
655 END LOOP; /* For Line Tbl */
656 p_x_line_adj_tbl := l_line_adj_tbl;
657 p_x_line_adj_att_tbl := l_line_adj_att_tbl;
659 END Check_Duplicate_Line_Charges;
660
661
662
663 PROCEDURE Check_Duplicate_Header_Charges
664 (
665 p_header_rec IN OE_Order_PUB.Header_Rec_Type
666 , p_x_Header_adj_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Tbl_Type
667 , p_x_Header_adj_att_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Att_Tbl_Type
668 )
669
670 IS
671 l_Header_adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type := p_x_Header_adj_tbl;
672 l_Header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type := p_x_Header_adj_att_tbl;
673 l_Header_id NUMBER := 0;
674 l_charge_index NUMBER := 0;
675 l_tmp_index NUMBER := 0;
676 l_att_index NUMBER := 0;
677 --
678 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
679 --
680 BEGIN
681 l_Header_id := p_Header_rec.header_id;
682
683 l_charge_index := l_Header_Adj_Tbl.FIRST;
684
685 WHILE l_charge_index IS NOT NULL LOOP
686
687 IF l_Header_adj_tbl(l_charge_index).list_line_type_code = 'FREIGHT_CHARGE'
688 AND NVL(l_Header_adj_tbl(l_charge_index).applied_flag,'N') = 'Y'
689 AND l_Header_adj_tbl(l_charge_index).operation IN
690 (OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
691 AND l_Header_adj_tbl(l_charge_index).Header_id = l_header_id
692 AND (l_Header_adj_tbl(l_charge_index).line_id IS NULL OR
693 l_Header_adj_tbl(l_charge_index).line_id = FND_API.G_MISS_NUM)
694 THEN
695
696 l_tmp_index := l_Header_adj_tbl.FIRST;
697 WHILE l_tmp_index IS NOT NULL LOOP
698
699 IF l_tmp_index <> l_charge_index
700 AND l_Header_adj_tbl(l_tmp_index).operation IN
701 (OE_GLOBALS.G_OPR_UPDATE, OE_GLOBALS.G_OPR_CREATE)
702 AND l_Header_adj_tbl(l_tmp_index).list_Line_type_code
703 = 'FREIGHT_CHARGE'
704 AND NVL(l_Header_adj_tbl(l_tmp_index).applied_flag,'N') = 'Y'
705 AND l_Header_adj_tbl(l_charge_index).charge_type_code
706 = l_Header_adj_tbl(l_tmp_index).charge_type_code
707 AND NVL(l_Header_adj_tbl(l_charge_index).charge_subtype_code,'SUB')
708 = NVL(l_Header_adj_tbl(l_tmp_index).charge_subtype_code,'SUB')
709 THEN
710
711 IF l_Header_adj_tbl(l_tmp_index).adjusted_amount >=
712 l_Header_adj_tbl(l_charge_index).adjusted_amount
713 THEN
714 IF l_Header_adj_tbl(l_charge_index).operation =
715 OE_GLOBALS.G_OPR_UPDATE
716 THEN
717 l_Header_adj_tbl(l_charge_index).operation :=
718 OE_GLOBALS.G_OPR_DELETE;
719 ELSE
720 l_Header_adj_tbl(l_charge_index).operation :=
721 OE_GLOBALS.G_OPR_NONE;
722 END IF;
723 EXIT;
724 END IF;
725
726 END IF;
727 l_tmp_index := l_Header_adj_tbl.NEXT(l_tmp_index);
728
729 END LOOP; /* For local temp loop */
730
731 IF p_x_Header_adj_tbl(l_charge_index).operation <>
732 l_Header_adj_tbl(l_charge_index).operation
733 THEN
734
735 l_att_index := l_Header_adj_att_tbl.FIRST;
736 WHILE l_att_index IS NOT NULL LOOP
737
738 IF l_Header_adj_att_tbl(l_att_index).operation =
739 OE_GLOBALS.G_OPR_UPDATE
740 AND l_Header_adj_tbl(l_charge_index).price_adjustment_id =
741 l_Header_adj_att_tbl(l_att_index).price_adjustment_id
742 THEN
743
744 l_Header_adj_att_tbl(l_att_index).operation :=
745 OE_GLOBALS.G_OPR_DELETE;
746
747 ELSIF l_Header_adj_att_tbl(l_att_index).operation =
748 OE_GLOBALS.G_OPR_CREATE
749 AND l_charge_index = l_Header_adj_att_tbl(l_att_index).adj_index
750 THEN
751
752 l_Header_adj_att_tbl(l_att_index).operation :=
753 OE_GLOBALS.G_OPR_NONE;
754
755 END IF;
756
757 l_att_index := l_Header_adj_att_tbl.NEXT(l_att_index);
758 END LOOP; /* For Header Adj Att table */
759
760 END IF;
761
762 END IF;
763 l_charge_index := l_Header_Adj_Tbl.NEXT(l_charge_index);
764
765 END LOOP; /* For Header Adj table */
766
767 p_x_Header_adj_tbl := l_Header_adj_tbl;
768 p_x_Header_adj_att_tbl := l_Header_adj_att_tbl;
769
770 END Check_Duplicate_Header_Charges;
771
772 -- This procedure will be used in Process Order API to check if any duplicate
773 -- charges exists on a Order Header or a Line before applying any charge.
774
775 PROCEDURE Check_Duplicate_Charges
776 (
777 p_Header_id IN NUMBER
778 , p_line_id IN NUMBER
779 , p_charge_type_code IN VARCHAR2
780 , p_charge_subtype_code IN VARCHAR2
781 , x_duplicate_flag OUT NOCOPY VARCHAR2
782
783 )
784 IS
785 l_Line_Adj_Tbl OE_Order_PUB.Line_Adj_Tbl_Type;
786 l_Header_Adj_Tbl OE_Order_PUB.Header_Adj_Tbl_Type;
787 l_duplicate_flag VARCHAR2(1) := 'N';
788 l_count NUMBER;
789 --
790 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
791 --
792 BEGIN
793 IF p_header_id IS NULL OR p_header_id = FND_API.G_MISS_NUM THEN
794
798 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
795 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
796 THEN
797
799 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','header');
800 OE_MSG_PUB.Add;
801
802 END IF;
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805 IF p_line_id IS NULL OR p_line_id = FND_API.G_MISS_NUM THEN
806
807 -- l_Header_Adj_Tbl := OE_Header_Adj_Util.query_rows(
808 -- p_header_id => p_header_id);
809
810 OE_Header_Adj_Util.Query_Rows( p_header_id => p_header_id,
811 x_header_adj_tbl => l_header_adj_tbl);
812 IF l_Header_Adj_Tbl.COUNT > 0 THEN
813 l_count := l_Header_Adj_Tbl.FIRST;
814 WHILE l_count IS NOT NULL LOOP
815
816 IF l_Header_Adj_Tbl(l_count).charge_type_code = p_charge_type_code
817 AND l_Header_Adj_Tbl(l_count).list_line_type_code =
818 'FREIGHT_CHARGE'
819 AND NVL(l_Header_Adj_Tbl(l_count).charge_subtype_code,'SUB') =
820 NVL(p_charge_subtype_code,'SUB')
821 AND l_Header_Adj_Tbl(l_count).applied_flag = 'Y' THEN
822
823 l_duplicate_flag := 'Y';
824 EXIT;
825 END IF;
826
827 END LOOP;
828
829 END IF;
830
831 ELSE
832 -- l_Line_Adj_Tbl := OE_Line_Adj_Util.query_rows( p_line_id => p_line_id);
833 OE_Line_Adj_Util.Query_Rows(p_line_id => p_line_id,
834 x_line_adj_tbl => l_line_adj_tbl);
835
836 IF l_Line_Adj_Tbl.COUNT > 0 THEN
837 l_count := l_Line_Adj_Tbl.FIRST;
838 WHILE l_count IS NOT NULL LOOP
839
840 IF l_Line_Adj_Tbl(l_count).charge_type_code = p_charge_type_code
841 AND l_Line_Adj_Tbl(l_count).list_line_type_code =
842 'FREIGHT_CHARGE'
843 AND NVL(l_Line_Adj_Tbl(l_count).charge_subtype_code,'SUB') =
844 NVL(p_charge_subtype_code,'SUB')
845 AND l_Header_Adj_Tbl(l_count).applied_flag = 'Y' THEN
846
847 l_duplicate_flag := 'Y';
848 EXIT;
849 END IF;
850
851 END LOOP;
852
853 END IF;
854 END IF;
855 x_duplicate_flag := l_duplicate_flag;
856 EXCEPTION
857
858 WHEN NO_DATA_FOUND THEN
859 x_duplicate_flag := l_duplicate_flag;
860
861 WHEN OTHERS THEN
862
863 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
864 THEN
865 FND_MSG_PUB.Add_Exc_Msg
866 ( G_PKG_NAME
867 , 'Check_Duplicate_Charges'
868 );
869 END IF;
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 END Check_Duplicate_Charges;
872
873
874 -- Start of Comments
875 -- Function name Get_Line_Weight_Or_Volume
876 --
877 -- This function will be used to source the qualifier attributes LINE_WEIGHT
878 -- and LINE_VOLUME. It will lookup at the following profile options to get the
879 -- target UOM for weight and volume. QP: Line Volume UOM Code and
880 -- QP: Line Weight UOM Code. Then the procedure will call the conversion
881 -- routine to get the values.
882 --
883 -- Type Private
884 --
885 -- Pre-reqs
886 --
887 -- Parameters
888 -- p_uom_class IN VARCHAR2 possible values are 'WEIGHT' or 'VOLUME'
889 --
890 --
891 -- Version Current version = 1.0
892 -- Initial version = 1.0
893 --
894 -- Notes
895 --
896 -- End of Comments
897
898 FUNCTION Get_Line_Weight_Or_Volume
899 ( p_uom_class IN VARCHAR2)
900 RETURN VARCHAR2
901 IS
902 l_line_rec OE_Order_PUB.Line_Rec_Type;
903 l_uom_code VARCHAR2(3);
904 l_uom_rate NUMBER;
905 --
906 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
907 --
908 BEGIN
909
910 IF p_uom_class NOT IN ('Weight','Volume')
911 THEN
912 IF l_debug_level > 0 THEN
913 oe_debug_pub.add( 'INVALIDE PARAMETER' || P_UOM_CLASS ) ;
914 END IF;
915 RETURN NULL;
916 END IF;
917
918 -- Get the Line record from the Global Record
919 l_line_rec := OE_ORDER_PUB.G_LINE;
920
921 IF p_uom_class = 'Weight' THEN
922 l_uom_code := FND_PROFILE.VALUE('QP_LINE_WEIGHT_UOM_CODE');
923 ELSE
924 l_uom_code := FND_PROFILE.VALUE('QP_LINE_VOLUME_UOM_CODE');
925 END IF;
926
927 IF l_uom_code IS NULL THEN
928 IF l_debug_level > 0 THEN
929 oe_debug_pub.add( 'NO VALUE SET IN THE PROFILE OPTIONS.' ) ;
930 END IF;
931 RETURN NULL;
932 END IF;
933 INV_CONVERT.INV_UM_CONVERSION(l_line_rec.order_quantity_uom,
934 l_uom_code,
935 l_line_rec.inventory_item_id,
936 l_uom_rate);
937 IF l_uom_rate > 0 THEN
938 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(TRUNC(l_uom_rate * l_line_rec.ordered_quantity, 2));
939 ELSE
940 IF l_debug_level > 0 THEN
944 END IF;
941 oe_debug_pub.add( 'NO CONVERSION INFORMATION IS AVAILABLE FOR CONVERTING FROM ' || L_LINE_REC.ORDER_QUANTITY_UOM || ' TO ' || L_UOM_CODE ) ;
942 END IF;
943 RETURN NULL;
945 END Get_Line_Weight_Or_Volume;
946
947 Procedure Freight_Debug(p_header_name In Varchar2 default null,
948 p_list_line_id In Number default null,
949 p_line_id In Number,
950 p_org_id In Number)
951 As
952 l_list_header_id Number;
953 l_list_header_name Varchar2(250);
954 l_pricing_phase_id Number;
955 l_list_line_id Number;
956 l_line_rec Oe_Order_Pub.Line_Rec_Type;
957 l_freeze_override_flag Varchar2(1);
958 l_cost_type_code Varchar2(30);
959 l_cost_amount Number;
960 l_pricing_contexts_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
961 l_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
962 l_found boolean default false;
963 l_dummy Varchar2(30);
964 j Number;
965
966 Cursor list_line_info1 is
967 select b.list_header_id,
968 b.list_line_id,
969 b.list_line_type_code,
970 b.start_date_active,
971 b.end_date_active,
972 b.modifier_level_code,
973 b.pricing_phase_id,
974 b.incompatibility_grp_code,
975 b.price_break_type_code,
976 b.operand,
977 b.arithmetic_operator,
978 b.qualification_ind,
979 b.product_precedence
980 from qp_list_headers_vl a,
981 qp_list_lines b
982 where a.name = p_header_name
983 and a.list_header_id = b.list_header_id;
984
985 Cursor list_line_info2 is
986 select b.list_header_id,
987 b.list_line_id,
988 b.list_line_type_code,
989 b.start_date_active,
990 b.end_date_active,
991 b.modifier_level_code,
992 b.pricing_phase_id,
993 b.incompatibility_grp_code,
994 b.price_break_type_code,
995 b.operand,
996 b.arithmetic_operator,
997 b.qualification_ind,
998 b.product_precedence
999 From qp_list_lines b
1000 where list_line_id = p_list_line_id;
1001
1002 Cursor pricing_attribute_info Is
1003 select list_line_id
1004 , list_header_id
1005 , pricing_phase_id
1006 , product_attribute_context
1007 , product_attribute
1008 , product_attr_value
1009 , product_uom_code
1010 , comparison_operator_code
1011 , pricing_attribute_context
1012 , pricing_attribute
1013 , pricing_attr_value_from
1014 , pricing_attr_value_to
1015 , attribute_grouping_no
1016 , qualification_ind
1017 , excluder_flag
1018 from qp_pricing_attributes
1019 where list_line_id = p_list_line_id;
1020
1021 /*select dl.delivery_id,
1022 pa.line_id,
1023 pa.cost_id,
1024 pa.list_line_type_code,
1025 pa.adjusted_amount,
1026 pa.operand
1027 from oe_price_adjustments pa,
1028 wsh_delivery_details dd,
1029 wsh_delivery_assignments da,
1030 wsh_new_deliveries dl
1031 where dl.name = 'delivery_name'
1032 and dl.delivery_id = da.delivery_id
1033 and da.delivery_detail_id = dd.delivery_detail_id
1034 and dd.source_code = 'OE'
1035 and dd.source_line_id = pa.line_id
1036 and pa.list_line_type_code = 'COST'; */
1037
1038 Cursor Other_Cost is
1039 Select CHARGE_TYPE_CODE,Adjusted_amount
1040 From oe_price_adjustments
1041 Where line_id = l_line_rec.line_id
1042 and list_line_type_code = 'COST';
1043
1044 --Type list_line_info_type list_line_info1%rowtype;
1045 l_list_line_info list_line_info1%rowtype;
1046 l_pricing_attribute_info pricing_attribute_info%rowtype;
1047
1048 --
1049 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1050 --
1051 Begin
1052 --MOAC Changes
1053 --dbms_application_info.set_client_info(p_org_id);
1054 mo_global.set_policy_context('S',p_org_id);
1055 --MOAC Changes
1056
1057 --Hardcode it for now, need to revisit this later.
1058 l_cost_type_code := 'FREIGHT';
1059
1060 If p_list_line_id is null and p_header_name is null Then
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add( 'PLEASE ENTER PROVIDE MODIFIER HEADER NAME OR LIST LINE ID' ) ;
1063 END IF;
1064 Return;
1065 End If;
1066
1067 If p_list_line_id is not null and p_header_name is not null Then
1068 IF l_debug_level > 0 THEN
1069 oe_debug_pub.add( 'PLEASE ENTER EITHER HEADER NAME OR LIST LINE ID. NOT BOTH' ) ;
1070 END IF;
1071 Return;
1072 End If;
1073
1074 If p_list_line_id is not null Then
1075 Begin
1076 Open list_line_info2;
1077 Fetch list_line_info2 into l_list_line_info;
1078
1079 Exception When Others Then
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add( SQLERRM ) ;
1082 END IF;
1083 End;
1084 Close list_line_info2;
1085 Elsif p_header_name is not null Then
1086 Begin
1087 Open list_line_info1;
1088 Fetch list_line_info1 into l_list_line_info;
1089
1090 If list_line_info1%ROWCOUNT > 1 Then
1091 IF l_debug_level > 0 THEN
1095 Return;
1092 oe_debug_pub.add( 'THIS HEADER HAS MULTIPLE MODIFIERS , PLEASE SPECIFY ONE BY JUST PASSING LIST LINE ID' ) ;
1093 END IF;
1094 close list_line_info1;
1096 End If;
1097
1098 Exception When Others Then
1099 IF l_debug_level > 0 THEN
1100 oe_debug_pub.add( SQLERRM ) ;
1101 END IF;
1102 End;
1103 close list_line_info1;
1104 End If;
1105
1106 --check if there is data qp_list_header_phases
1107 --if not this is a pricing bug
1108 IF l_debug_level > 0 THEN
1109 oe_debug_pub.add( 'CHECKING FOR PRICING BUG' ) ;
1110 END IF;
1111 Begin
1112 Select list_header_id,
1113 pricing_phase_id
1114 Into l_list_header_id,l_pricing_phase_id
1115 from qp_list_header_phases
1116 where list_header_id = l_list_line_info.list_header_id;
1117 Exception
1118 when no_data_found Then
1119 --check if it has line level qualifier
1120 Begin
1121 Select list_line_id into l_dummy
1122 From qp_qualifiers
1123 Where list_header_id = l_list_line_info.list_header_id
1124 and nvl(list_line_id,-1) = l_list_line_id
1125 and rownum = 1;
1126
1127 IF l_debug_level > 0 THEN
1128 oe_debug_pub.add( ' ORACLE PRICING BUGS.' ) ;
1129 END IF;
1130 IF l_debug_level > 0 THEN
1131 oe_debug_pub.add( ' PLEASE APPLY PRICING PATCH 1806021 IF THIS IS AN UPGRADE' ) ;
1132 END IF;
1133 IF l_debug_level > 0 THEN
1134 oe_debug_pub.add( ' OTHERWISE APPLY 1797603' ) ;
1135 END IF;
1136
1137 Exception When no_data_found then null;
1138 End;
1139 when too_many_rows Then
1140 Null;
1141 when others Then
1142 IF l_debug_level > 0 THEN
1143 oe_debug_pub.add( SQLERRM ) ;
1144 END IF;
1145 End;
1146
1147 --check if the freeze_override_flag set to Y
1148 Begin
1149 select a.freeze_override_flag
1150 into l_freeze_override_flag
1151 from qp_pricing_phases a, qp_event_phases b
1152 where a.pricing_phase_id = b.pricing_phase_id
1153 and b.pricing_event_code='SHIP'
1154 and a.pricing_phase_id =l_list_line_info.pricing_phase_id;
1155
1156 If l_freeze_override_flag Is Null or l_freeze_override_flag = 'N' Then
1157 IF l_debug_level > 0 THEN
1158 oe_debug_pub.add( ' FREEZE OVERRIDE FLAG FOR SHIP EVENT AND PHASE ID '||L_LIST_LINE_INFO.PRICING_PHASE_ID ||'IS ''N'' OR NULLL' ) ;
1159 END IF;
1160 IF l_debug_level > 0 THEN
1161 oe_debug_pub.add( ' PLEASE CONTACT ORACLE PRICING TO FIX THIS PROBLEM' ) ;
1162 END IF;
1163 End If;
1164
1165 Exception when others then
1166 IF l_debug_level > 0 THEN
1167 oe_debug_pub.add( SQLERRM||':EVENT PHASES CHECK' ) ;
1168 END IF;
1169 End;
1170
1171 --query line and header record
1172 --Set org?
1173 oe_line_util.query_row(p_line_id,l_line_rec);
1174
1175 If l_line_rec.line_id is null Then
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.add( 'INVALID LINE ID OR INCORRECT ORG_ID' ) ;
1178 END IF;
1179 Return;
1180 End If;
1181
1182 --testing qp attribute mapping
1183 OE_Order_Pub.G_Line := l_line_rec;
1184
1185 Begin
1186 QP_Attr_Mapping_PUB.Build_Contexts(p_request_type_code => 'ONT',
1187 p_pricing_type => 'L',
1188 x_price_contexts_result_tbl => l_pricing_contexts_Tbl,
1189 x_qual_contexts_result_tbl => l_qualifier_Contexts_Tbl);
1190
1191 Exception when others then
1192 IF l_debug_level > 0 THEN
1193 oe_debug_pub.add( 'QP ATTRIBUTE MAPPING:'||SQLERRM ) ;
1194 END IF;
1195 End;
1196 OE_Order_Pub.G_Line := NULL;
1197
1198 --Test if attribute mapping sorces required pricing attributes
1199 For i in pricing_attribute_info Loop
1200 l_found:=false;
1201 IF l_debug_level > 0 THEN
1202 oe_debug_pub.add( 'CHECK IF ATTRIBUTE MAPPING SOURCES:'||I.PRICING_ATTRIBUTE_CONTEXT||' , '||I.PRICING_ATTRIBUTE||' , '||I.PRICING_ATTR_VALUE_FROM ) ;
1203 END IF;
1204
1205 j := l_pricing_contexts_tbl.first;
1206 While j is not null Loop
1207 if i.pricing_attribute_context = l_pricing_contexts_tbl(j).context_name
1208 and i.pricing_attribute = l_pricing_contexts_tbl(j).attribute_name Then
1209 IF l_debug_level > 0 THEN
1210 oe_debug_pub.add( ' THIS ATTRIBUTE IS SOURCED WITH VALUE:'||L_PRICING_CONTEXTS_TBL ( J ) .ATTRIBUTE_VALUE ) ;
1211 END IF;
1212 l_found := True;
1213 exit;
1214 End If;
1215 j:= l_pricing_contexts_tbl.next(j);
1216 End Loop;
1217
1218 If not l_found Then
1219 IF l_debug_level > 0 THEN
1220 oe_debug_pub.add( ' THIS ATTRIBUTE DID NOT GET SOURCED. THE CAUSED COULD BE:' ) ;
1221 END IF;
1222 IF l_debug_level > 0 THEN
1223 oe_debug_pub.add( ' 1. YOU HAVE NOT RUN QP BUILD SOURCING CONCURENT PROGRAM' ) ;
1224 END IF;
1225 IF l_debug_level > 0 THEN
1226 oe_debug_pub.add( ' 2. THE COST RECORD WAS NOT PASSED TO OM' ) ;
1227 END IF;
1228 End If;
1229
1230 End Loop;
1231
1232
1233 --check if this is a shippable line
1234 IF l_debug_level > 0 THEN
1238 IF l_debug_level > 0 THEN
1235 oe_debug_pub.add( 'CHECKING IF THE LINE IS SHIPPABLE' ) ;
1236 END IF;
1237 If l_line_rec.shippable_flag = 'N' or l_line_rec.shipped_quantity <= 0 Then
1239 oe_debug_pub.add( ' EITHER THIS LINE IS NOT SHIPPABLE OR HAS NOT BEEN SHIP CONFIRMED' ) ;
1240 END IF;
1241 End If;
1242
1243
1244 --check if cost record have been inserted into OM
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add( 'CHECKING IF FREIGHT COST HAS BEEN PASSED TO OM' ) ;
1247 END IF;
1248
1249 -- Cost records are stored in OE_PRICE_ADJUSTMENTS table with
1250 -- list_line_type_code = 'COST'
1251 Begin
1252 SELECT SUM(ADJUSTED_AMOUNT)
1253 INTO l_cost_amount
1254 FROM OE_PRICE_ADJUSTMENTS_V
1255 WHERE LINE_ID = l_line_rec.line_id
1256 AND LIST_LINE_TYPE_CODE = 'COST'
1257 AND CHARGE_TYPE_CODE = l_cost_type_code;
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add( ' COST RECORD INSERTED WITH VALUE:'||L_COST_AMOUNT ) ;
1260 END IF;
1261 Exception
1262 When No_Data_Found Then
1263 IF l_debug_level > 0 THEN
1264 oe_debug_pub.add( ' FREIGHT COST RECORD IS NOT PASSED BY SHIPPING OR YOU HAVE NOT ENTERED THE FREIGHT COST' ) ;
1265 END IF;
1266 End;
1267
1268 For i in Other_Cost Loop
1269 IF l_debug_level > 0 THEN
1270 oe_debug_pub.add( ' PASSED CHARGE COST TYPE IN OM:'||I.CHARGE_TYPE_CODE ) ;
1271 END IF;
1272 IF l_debug_level > 0 THEN
1273 oe_debug_pub.add( ' COST AMOUNT:'||I.ADJUSTED_AMOUNT ) ;
1274 END IF;
1275 End Loop;
1276
1277 End;
1278
1279 --Recurring Charges
1280 PROCEDURE Get_Rec_Charge_Amount
1281 ( p_api_version_number IN NUMBER
1282 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1283 , p_header_id IN NUMBER
1284 , p_line_id IN NUMBER
1285 , p_all_charges IN VARCHAR2 := FND_API.G_FALSE
1286 , p_charge_periodicity_code IN VARCHAR2
1287 , x_return_status OUT NOCOPY VARCHAR2
1288 , x_msg_count OUT NOCOPY NUMBER
1289 , x_msg_data OUT NOCOPY VARCHAR2
1290 , x_charge_amount OUT NOCOPY NUMBER
1291 )
1292 IS
1293 l_api_version_number CONSTANT NUMBER := 1.0;
1294 l_api_name CONSTANT VARCHAR2(30):= 'Get_Rec_Charge_Amount';
1295 l_charge_amount NUMBER := 0.0;
1296 l_hdr_charge_amount NUMBER := 0.0;
1297 l_line_charge_amount NUMBER := 0.0;
1298 Is_fmt BOOLEAN;
1299 --
1300 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1301 --
1302 BEGIN
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304
1305 -- Check for Header Id
1306
1307 IF p_header_id is NULL OR p_header_id = FND_API.G_MISS_NUM THEN
1308
1309 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1310 THEN
1311
1312 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1313 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','header');
1314 OE_MSG_PUB.Add;
1315
1316 END IF;
1317 RAISE FND_API.G_EXC_ERROR;
1318
1319 END IF;
1320
1321 IF NVL(p_header_id,-1)<>NVL(OE_ORDER_UTIL.G_Header_id,-10)
1322 OR OE_ORDER_UTIL.G_Precision IS NULL THEN
1323 Is_fmt:= OE_ORDER_UTIL.Get_Precision(
1324 p_header_id=>p_header_id
1325 );
1326 END IF;
1327
1328 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
1329 OE_ORDER_UTIL.G_Precision:=2;
1330 END IF;
1331
1332 -- Check the operation whether all charges for the Order are required
1333 IF p_all_charges = FND_API.G_TRUE THEN
1334 SELECT SUM(ROUND(
1335 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
1336 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1337 DECODE(L.ORDERED_QUANTITY,0,0,(-P.OPERAND)),
1338 (-L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
1339 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1340 DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
1341 (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
1342 )
1343 ,OE_ORDER_UTIL.G_Precision)
1344 )
1345 INTO l_charge_amount
1346 FROM OE_PRICE_ADJUSTMENTS P,
1347 OE_ORDER_LINES_ALL L
1348 WHERE P.HEADER_ID = p_header_id
1349 AND P.LINE_ID = L.LINE_ID(+)
1350 AND nvl(L.CHARGE_PERIODICITY_CODE,'ONE') = p_charge_periodicity_code
1351 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
1352 AND P.APPLIED_FLAG = 'Y';
1353
1354 END IF;
1355
1356 IF l_charge_amount IS NULL THEN
1357 l_charge_amount := 0.0;
1358 END IF;
1359 x_charge_amount := l_charge_amount;
1360 EXCEPTION
1361
1362 WHEN FND_API.G_EXC_ERROR THEN
1363
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365
1366 -- Get message count and data
1367
1368 OE_MSG_PUB.Count_And_Get
1369 ( p_count => x_msg_count
1370 , p_data => x_msg_data
1371 );
1372
1373 WHEN OTHERS THEN
1374
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1376
1377 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1378 THEN
1379 FND_MSG_PUB.Add_Exc_Msg
1380 ( G_PKG_NAME
1381 , 'Get_Charge_Amount'
1382 );
1383 END IF;
1384
1385 -- Get message count and data
1386
1387 OE_MSG_PUB.Count_And_Get
1388 ( p_count => x_msg_count
1389 , p_data => x_msg_data
1390 );
1391
1392 END Get_Rec_Charge_Amount;
1393
1394 END OE_Charge_PVT;