DBA Data[Home] [Help]

PACKAGE BODY: APPS.OE_CHARGE_PVT

Source


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;