[Home] [Help]
PACKAGE BODY: APPS.OE_TOTALS_GRP
Source
1 PACKAGE BODY OE_Totals_GRP AS
2 /* $Header: OEXGTOTB.pls 120.2 2005/10/26 17:39:07 lkxu noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Totals_GRP';
7
8
9 -- Start of Comments
10 -- API name Get_Order_Total
11 -- Type Group
12 -- Function: The API to return Order/Line totals for (Line amount/Tax /Charges)
13 -- For Order level total, the Header_id should be passed in and Line_id
14 -- should be null. For line level totals, both should be passed in with values.
15 --
16 -- Pre-reqs
17 --
18 -- Parameters
19 -- p_header_id IN NUMBER
20 -- p_line_id IN NUMBER
21 -- p_total_type IN NUMBER possible values are
22 -- ('ALL','LINES','CHARGES','TAXES')
23 --
24 -- Version Current version = 1.0
25 -- Initial version = 1.0
26 --
27 -- Notes
28 --
29 -- End of Comments
30
31 FUNCTION Get_Order_Total
32 ( p_header_id IN NUMBER
33 , p_line_id IN NUMBER
34 , p_total_type IN VARCHAR2 := 'ALL'
35 ) RETURN NUMBER
36 IS
37 l_api_name CONSTANT VARCHAR2(30):= 'Get_Order_Total';
38 l_return_status VARCHAR2(1);
39 l_charge_amount NUMBER := 0;
40 l_line_amount NUMBER := 0;
41 l_tax_amount NUMBER := 0;
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(240);
44
45 BEGIN
46
47 IF p_header_id is NULL OR p_header_id = FND_API.G_MISS_NUM THEN
48
49 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
50 THEN
51
52 fnd_message.set_name('ONT','OE_CONFIG_PARAMETER_REQUIRED');
53 FND_MESSAGE.SET_TOKEN('PARAMETER','Header_Id');
54 OE_MSG_PUB.Add;
55
56 END IF;
57 RAISE FND_API.G_EXC_ERROR;
58
59 END IF;
60
61 IF p_total_type is NULL OR p_total_type = FND_API.G_MISS_CHAR THEN
62
63 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
64 THEN
65
66 fnd_message.set_name('ONT','OE_CONFIG_PARAMETER_REQUIRED');
67 FND_MESSAGE.SET_TOKEN('PARAMETER','Total_Type');
68 OE_MSG_PUB.Add;
69
70 END IF;
71 RAISE FND_API.G_EXC_ERROR;
72
73 END IF;
74
75 -- For Order Level Totals
76 IF p_line_id IS NULL OR p_line_id = FND_API.G_MISS_NUM THEN
77
78 OE_OE_TOTALS_SUMMARY.GLOBAL_TOTALS(p_header_id);
79 IF p_total_type = 'ALL' THEN
80
81 RETURN(OE_OE_TOTALS_SUMMARY.ORDER_SUBTOTALS( p_header_id ) +
82 OE_OE_TOTALS_SUMMARY.Charges ( p_header_id ) +
83 OE_OE_TOTALS_SUMMARY.Taxes ( p_header_id ));
84
85 ELSIF p_total_type = 'LINES' THEN
86
87 RETURN(OE_OE_TOTALS_SUMMARY.ORDER_SUBTOTALS( p_header_id ));
88
89 ELSIF p_total_type = 'TAXES' THEN
90
91 RETURN(OE_OE_TOTALS_SUMMARY.Taxes ( p_header_id ));
92
93 ELSIF p_total_type = 'CHARGES' THEN
94
95 RETURN(OE_OE_TOTALS_SUMMARY.Charges ( p_header_id ));
96 ELSE
97
98 RETURN 0;
99 END IF;
100
101 ELSE -- For Line level totals
102 BEGIN -- Bug#3277021
103 IF p_total_type IN ('ALL','LINES','TAXES') THEN
104 SELECT DECODE(line_category_code,'RETURN',-1,1)*
105 NVL(unit_selling_price,0)*
106 NVL(Ordered_Quantity,0),
107 DECODE(line_category_code,'RETURN',-1,1)*
108 NVL(tax_value,0)
109 INTO l_line_amount,
110 l_tax_amount
111 FROM oe_order_lines_all
112 WHERE line_id = p_line_id
113 AND NVL(cancelled_flag,'N')='N';
114 END IF;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 l_line_amount := 0;
118 l_tax_amount := 0;
119 END;
120 IF p_total_type IN ('CHARGES','ALL') THEN
121 OE_CHARGE_PVT.GET_CHARGE_AMOUNT(
122 p_api_version_number => 1.0
123 , p_header_id => p_header_id
124 , p_line_id => p_line_id
125 , p_all_charges => FND_API.G_FALSE
126 , x_return_status => l_return_status
127 , x_msg_count => l_msg_count
128 , x_msg_data => l_msg_data
129 , x_charge_amount => l_charge_amount
130 );
131 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
132 RAISE FND_API.G_EXC_ERROR;
133 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 END IF;
138 IF p_total_type = 'ALL' THEN
139 RETURN (l_line_amount + l_tax_amount + l_charge_amount);
140 ELSIF p_total_type = 'LINES' THEN
141 return l_line_amount;
142 ELSIF p_total_type = 'TAXES' THEN
143 return l_tax_amount;
144 ELSIF p_total_type = 'CHARGES' THEN
145 return l_charge_amount;
146 ELSE
147 return 0;
148 END IF;
149
150 END IF;
151 EXCEPTION
152
153 WHEN NO_DATA_FOUND THEN
154 RETURN 0;
155 WHEN OTHERS THEN
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157
158 END Get_Order_Total;
159
160 FUNCTION Get_Rec_Order_Total
161 ( p_header_id IN NUMBER
162 , p_line_id IN NUMBER
163 , p_charge_periodicity_code IN VARCHAR2
164 , p_total_type IN VARCHAR2 := 'ALL'
165 ) RETURN NUMBER
166 IS
167 l_return_status VARCHAR2(1);
168 l_charge_amount NUMBER := 0;
169 l_line_amount NUMBER := 0;
170 l_tax_amount NUMBER := 0;
171 l_msg_count NUMBER;
172 l_msg_data VARCHAR2(240);
173
174 BEGIN
175
176 IF p_header_id is NULL OR p_header_id = FND_API.G_MISS_NUM THEN
177
178 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
179 THEN
180
181 fnd_message.set_name('ONT','OE_CONFIG_PARAMETER_REQUIRED');
182 FND_MESSAGE.SET_TOKEN('PARAMETER','Header_Id');
183 OE_MSG_PUB.Add;
184
185 END IF;
186 RAISE FND_API.G_EXC_ERROR;
187
188 END IF;
189
190 IF p_total_type is NULL OR p_total_type = FND_API.G_MISS_CHAR THEN
191
192 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
193 THEN
194
195 fnd_message.set_name('ONT','OE_CONFIG_PARAMETER_REQUIRED');
196 FND_MESSAGE.SET_TOKEN('PARAMETER','Total_Type');
197 OE_MSG_PUB.Add;
198
199 END IF;
200 RAISE FND_API.G_EXC_ERROR;
201
202 END IF;
203
204 -- For Order Level Totals
205 IF p_line_id IS NULL OR p_line_id = FND_API.G_MISS_NUM THEN
206
207 OE_OE_TOTALS_SUMMARY.GLOBAL_REC_TOTALS(p_header_id,p_charge_periodicity_code);
208 IF p_total_type = 'ALL' THEN
209
210 RETURN(OE_OE_TOTALS_SUMMARY.REC_ORDER_SUBTOTALS( p_header_id ) +
211 OE_OE_TOTALS_SUMMARY.REC_Charges ( p_header_id,p_charge_periodicity_code ) +
212 OE_OE_TOTALS_SUMMARY.REC_Taxes ( p_header_id ));
213
214 ELSIF p_total_type = 'LINES' THEN
215
216 RETURN(OE_OE_TOTALS_SUMMARY.REC_ORDER_SUBTOTALS( p_header_id ));
217
218 ELSIF p_total_type = 'TAXES' THEN
219
220 RETURN(OE_OE_TOTALS_SUMMARY.REC_Taxes ( p_header_id ));
221
222 ELSIF p_total_type = 'CHARGES' THEN
223
224 RETURN(OE_OE_TOTALS_SUMMARY.REC_Charges ( p_header_id,p_charge_periodicity_code ));
225 ELSE
226
227 RETURN 0;
228 END IF;
229
230 ELSE -- For Line level totals
231 BEGIN -- Bug#3277021
232 IF p_total_type IN ('ALL','LINES','TAXES') THEN
233 SELECT DECODE(line_category_code,'RETURN',-1,1)*
234 NVL(unit_selling_price,0)*
235 NVL(Ordered_Quantity,0),
236 DECODE(line_category_code,'RETURN',-1,1)*
237 NVL(tax_value,0)
238 INTO l_line_amount,
239 l_tax_amount
240 FROM oe_order_lines_all
241 WHERE line_id = p_line_id
242 AND NVL(cancelled_flag,'N')='N';
243 END IF;
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 l_line_amount := 0;
247 l_tax_amount := 0;
248 END;
249 IF p_total_type IN ('CHARGES','ALL') THEN
250 OE_CHARGE_PVT.GET_CHARGE_AMOUNT(
251 p_api_version_number => 1.0
252 , p_header_id => p_header_id
253 , p_line_id => p_line_id
254 , p_all_charges => FND_API.G_FALSE
255 , x_return_status => l_return_status
256 , x_msg_count => l_msg_count
257 , x_msg_data => l_msg_data
258 , x_charge_amount => l_charge_amount
259 );
260 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
261 RAISE FND_API.G_EXC_ERROR;
262 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265
266 END IF;
267 IF p_total_type = 'ALL' THEN
268 RETURN (l_line_amount + l_tax_amount + l_charge_amount);
269 ELSIF p_total_type = 'LINES' THEN
270 return l_line_amount;
271 ELSIF p_total_type = 'TAXES' THEN
272 return l_tax_amount;
273 ELSIF p_total_type = 'CHARGES' THEN
274 return l_charge_amount;
275 ELSE
276 return 0;
277 END IF;
278
279 END IF;
280 EXCEPTION
281
282 WHEN NO_DATA_FOUND THEN
283 RETURN 0;
284 WHEN OTHERS THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286
287 END Get_Rec_Order_Total;
288
289 PROCEDURE GET_RECURRING_TOTALS
290 (
291 p_header_id IN NUMBER,
292 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type)
293 IS
294 BEGIN
295 OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS(
296 p_header_id=>p_header_id,
297 x_rec_charges_tbl=>x_rec_charges_tbl
298 );
299 EXCEPTION
300
301 WHEN OTHERS THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303
304 END GET_RECURRING_TOTALS;
305
306 --Pay Now Pay Later project
307 --group API which returns the pay now portion of an order or an order line. The pay now portion is broken up into subtotal, tax, charges and pay now total.
308 --Either line_id or header_id needs to be passed to the api
309 --p_total_type could take any of the values 'ALL', 'LINES', 'TAXES' or 'CHARGES'.
310 --Default value of p_total_type is NULL to avoid GCSS warnings. NULL would be treated as 'ALL'.
311 FUNCTION Get_PayNow_Total
312 ( p_header_id IN NUMBER
313 , p_line_id IN NUMBER
314 , p_total_type IN VARCHAR2 := NULL
315 ) RETURN NUMBER
316 IS
317 CURSOR lines_cur(p_header_id IN NUMBER) IS
318 SELECT line_id
319 , payment_term_id
320 FROM oe_order_lines_all
321 WHERE header_id = p_header_id;
322
323 l_api_name CONSTANT VARCHAR2(30):= 'Get_PayNow_Total';
324 l_pay_now_total_detail_tbl AR_VIEW_TERM_GRP.amounts_table;
325 l_pay_now_total_summary_rec AR_VIEW_TERM_GRP.summary_amounts_rec;
326 l_line_tbl oe_order_pub.line_tbl_type;
327 i pls_integer;
328 l_line_id NUMBER;
329 l_header_id NUMBER;
330 l_currency_code VARCHAR2(15);
331 l_msg_count NUMBER := 0 ;
332 l_msg_data VARCHAR2(2000) := NULL ;
333 l_return_status VARCHAR2(30) := NULL ;
334 l_org_id NUMBER;
335
336 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
337
338 BEGIN
339 IF l_debug_level > 0 THEN
340 oe_debug_pub.add('ENTERING OE_TOTALS_GRP.GET_PAYNOW_TOTAL');
341 END IF;
342 l_return_status := FND_API.G_RET_STS_SUCCESS;
343
344
345 IF (p_header_id is NULL OR p_header_id = FND_API.G_MISS_NUM) AND
346 (p_line_id is NULL OR p_line_id = FND_API.G_MISS_NUM) THEN
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add('Both header_id and line_id are either null or FND_API.G_MISS_NUM... Returning from the function');
349 END IF;
350 RETURN 0;
351 END IF;
352
353 IF p_header_id IS NOT NULL AND p_header_id <> FND_API.G_MISS_NUM THEN
354 OE_Order_Cache.load_order_header(p_header_id);
355 l_org_id := OE_Order_Cache.g_header_rec.org_id;
356
357 ELSE
358 BEGIN
359 SELECT org_id
360 INTO l_org_id
361 FROM oe_order_lines_all
362 WHERE line_id = p_line_id;
363 EXCEPTION WHEN NO_DATA_FOUND THEN
364 null;
365 END;
366 END IF;
367
368
369 IF OE_Prepayment_Util.Get_Installment_Options(l_org_id)
370 <> 'ENABLE_PAY_NOW' THEN
371 IF l_debug_level > 0 THEN
372 oe_debug_pub.add('Installment Options is not ENABLE_PAY_NOW... Returning from the function');
373 END IF;
374 RETURN 0;
375 END IF;
376
377 IF p_line_id IS NOT NULL AND
378 p_line_id <> FND_API.G_MISS_NUM THEN
379 -- this is for line payment
380 SELECT line_id
381 ,header_id
382 ,payment_term_id
383 INTO l_line_tbl(1).line_id
384 ,l_line_tbl(1).header_id
385 ,l_line_tbl(1).payment_term_id
386 FROM oe_order_lines_all
387 WHERE line_id=p_line_id;
388 ELSE
389 -- this is for header payment
390 i := 1;
391 FOR c_line_rec in lines_cur(p_header_id) LOOP
392 l_line_tbl(i).header_id := p_header_id;
393 l_line_tbl(i).line_id := c_line_rec.line_id;
394 l_line_tbl(i).payment_term_id := c_line_rec.payment_term_id;
395 i := i + 1;
396 END LOOP;
397
398 END IF;
399
400 -- populate information to pl/sql table in order to call API to get Pay Now portion
401 i := l_line_tbl.First;
402 OE_Order_Cache.load_order_header(l_line_tbl(i).header_id);
403 l_currency_code := OE_Order_Cache.g_header_rec.transactional_curr_code;
404 WHILE i IS NOT NULL LOOP
405
406 l_pay_now_total_detail_tbl(i).line_id := l_line_tbl(i).line_id;
407 l_pay_now_total_detail_tbl(i).term_id := l_line_tbl(i).payment_term_id;
408 l_line_id := l_line_tbl(i).line_id;
409 l_header_id := l_line_tbl(i).header_id;
410
411 l_pay_now_total_detail_tbl(i).line_amount :=
412 OE_Verify_Payment_PUB.Get_Line_Total
413 (p_line_id => l_line_id
414 ,p_header_id => l_header_id
415 ,p_currency_code => l_currency_code
416 ,p_level => NULL
417 ,p_amount_type => 'SUBTOTAL'
418 );
419 l_pay_now_total_detail_tbl(i).tax_amount :=
420 OE_Verify_Payment_PUB.Get_Line_Total
421 (p_line_id => l_line_id
422 ,p_header_id => l_header_id
423 ,p_currency_code => l_currency_code
424 ,p_level => NULL
425 ,p_amount_type => 'TAX'
426 );
427 l_pay_now_total_detail_tbl(i).freight_amount :=
428 OE_Verify_Payment_PUB.Get_Line_Total
429 (p_line_id => l_line_id
430 ,p_header_id => l_header_id
431 ,p_currency_code => l_currency_code
432 ,p_level => NULL
433 ,p_amount_type => 'CHARGES'
434 );
435
436 i := l_line_tbl.Next(i);
437 END LOOP;
438
439
440 i := l_pay_now_total_detail_tbl.count + 1;
441 -- append header level charges to the detail line table
442 IF p_line_id IS NULL OR
443 p_line_id = FND_API.G_MISS_NUM THEN
444 l_pay_now_total_detail_tbl(i).line_id := null;
445 l_pay_now_total_detail_tbl(i).line_amount :=0;
446 l_pay_now_total_detail_tbl(i).tax_amount :=0;
447 l_pay_now_total_detail_tbl(i).freight_amount :=
448 OE_VERIFY_PAYMENT_PUB.Outbound_Order_Total
449 (p_header_id => p_header_id
450 ,p_total_type => 'HEADER_CHARGES'
451 );
452 l_pay_now_total_detail_tbl(i).Term_id := OE_Order_Cache.g_header_rec.payment_term_id;
453 END IF;
454
455 -- calling AR API to get pay now total
456 MO_GLOBAL.set_policy_context('S',l_org_id);
457 AR_VIEW_TERM_GRP.pay_now_amounts
458 (p_api_version => 1.0
459 ,p_init_msg_list => FND_API.G_TRUE
460 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
461 ,p_currency_code => l_currency_code
462 ,p_amounts_tbl => l_pay_now_total_detail_tbl
463 ,x_pay_now_summary_rec => l_pay_now_total_summary_rec
464 ,x_return_status => l_return_status
465 ,x_msg_count => l_msg_count
466 ,x_msg_data => l_msg_data
467 );
468
469 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
470 IF l_msg_count = 1 THEN
471 IF l_debug_level > 0 THEN
472 oe_debug_pub.add('Error message after calling AR_VIEW_TERM_GRP.pay_now_amounts API: '||l_msg_data , 3 ) ;
473 END IF;
474 oe_msg_pub.add_text(p_message_text => l_msg_data);
475 ELSIF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
476 arp_util.enable_debug;
477 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
478 l_msg_data := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
479 IF l_debug_level > 0 THEN
480 oe_debug_pub.add( L_MSG_DATA , 3 ) ;
481 END IF;
482 oe_msg_pub.add_text(p_message_text => l_msg_data);
483 END LOOP;
484 END IF;
485 END IF;
486
487 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
488 RAISE FND_API.G_EXC_ERROR;
489 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF;
492
493 IF l_debug_level > 0 THEN
494 oe_debug_pub.add('TOTAL AMOUNT -> ' ||nvl(l_pay_now_total_summary_rec.total_amount, 0));
495 oe_debug_pub.add('LINE AMOUNT -> ' ||nvl(l_pay_now_total_summary_rec.line_amount, 0));
496 oe_debug_pub.add('TAX AMOUNT -> ' ||nvl(l_pay_now_total_summary_rec.tax_amount, 0));
497 oe_debug_pub.add('FREIGHT AMOUNT -> ' ||nvl(l_pay_now_total_summary_rec.freight_amount, 0));
498 oe_debug_pub.add('EXITING OE_TOTALS_GRP.GET_PAYNOW_TOTAL');
499 END IF;
500
501 IF p_total_type IS NULL OR
502 p_total_type = 'ALL' THEN
503 RETURN nvl(l_pay_now_total_summary_rec.total_amount, 0);
504 ELSIF p_total_type = 'LINES' THEN
505 RETURN nvl(l_pay_now_total_summary_rec.line_amount, 0);
506 ELSIF p_total_type = 'TAXES' THEN
507 RETURN nvl(l_pay_now_total_summary_rec.tax_amount, 0);
508 ELSIF p_total_type = 'CHARGES' THEN
509 RETURN nvl(l_pay_now_total_summary_rec.freight_amount, 0);
510 ELSE
511 RETURN 0;
512 END IF;
513
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 RETURN 0;
517
518 WHEN OTHERS THEN
519 IF l_debug_level > 0 THEN
520 OE_DEBUG_PUB.ADD('Unexpected Error in Get_PayNow_Total API: '||SUBSTR(SQLERRM,1,300) ) ;
521 END IF;
522
523 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
524 OE_MSG_PUB.Add_Exc_Msg
525 (G_PKG_NAME
526 ,l_api_name
527 );
528 END IF;
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530
531 END Get_PayNow_Total;
532
533 END OE_Totals_GRP;