[Home] [Help]
PACKAGE BODY: APPS.AR_VIEW_TERM_GRP
Source
1 PACKAGE BODY AR_VIEW_TERM_GRP AS
2 /* $Header: ARVTERMB.pls 120.1 2005/01/14 19:43:48 jbeckett noship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_VIEW_TERM_GRP';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14
15 /*=======================================================================+
16 | Declare PUBLIC Data Types and Variables
17 +=======================================================================*/
18 TYPE pay_now_cache_table IS TABLE OF pay_now_record INDEX BY BINARY_INTEGER;
19
20 pay_now_cache pay_now_cache_table;
21
22 /*========================================================================
23 | Prototype Declarations Procedures
24 *=======================================================================*/
25
26
27 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 /*========================================================================
30 | Prototype Declarations Functions
31 *=======================================================================*/
32
33 /*==========================================================================+
34 | PROCEDURE |
35 | pay_now_amounts |
36 | |
37 | DESCRIPTION |
38 | This procedure returns pay now amounts for a given line, tax and |
39 | freight amount and term_id |
40 | |
41 | SCOPE - PUBLIC |
42 | |
43 | ARGUMENTS : IN : p_term_id |
44 | p_line_amount |
45 | p_tax_amount |
46 | p_freight_amount |
47 | |
48 | OUT : x_pay_now_line_amount |
49 | x_pay_now_tax_amount |
50 | x_pay_now_freight_amount |
51 | x_pay_now_total_amount |
52 | |
53 | NOTES : |
54 | |
55 | MODIFICATION HISTORY |
56 | 10-NOV-04 JBECKETT Created |
57 | |
58 +===========================================================================*/
59 PROCEDURE pay_now_amounts(
60 -- Standard API parameters.
61 p_api_version IN NUMBER,
62 p_init_msg_list IN VARCHAR2,
63 p_validation_level IN NUMBER,
64 p_term_id IN NUMBER,
65 p_currency_code IN fnd_currencies.currency_code%TYPE,
66 p_line_amount IN NUMBER,
67 p_tax_amount IN NUMBER,
68 p_freight_amount IN NUMBER,
69 x_pay_now_line_amount OUT NOCOPY NUMBER,
70 x_pay_now_tax_amount OUT NOCOPY NUMBER,
71 x_pay_now_freight_amount OUT NOCOPY NUMBER,
72 x_pay_now_total_amount OUT NOCOPY NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2)
76 IS
77
78 l_api_name CONSTANT VARCHAR2(30) := 'pay_now_amounts';
79 l_api_version CONSTANT NUMBER := 1.0;
80
81 l_pay_now_percent NUMBER;
82 l_pay_now_line_amount NUMBER;
83 l_pay_now_line_amt_rnd NUMBER;
84 l_pay_now_tax_amount NUMBER;
85 l_pay_now_tax_amt_rnd NUMBER;
86 l_pay_now_freight_amount NUMBER;
87 l_pay_now_freight_amt_rnd NUMBER;
88
89 CURSOR c_term(p_term_id IN NUMBER) IS
90 SELECT NVL(base_amount,100),
91 first_installment_code
92 FROM ra_terms
93 WHERE term_id = p_term_id;
94
95 CURSOR c_term_line (p_term_id IN NUMBER) IS
96 SELECT NVL(SUM(tl.relative_amount),0),
97 NVL(SUM(DECODE(tl.sequence_num,1,1,0)),0)
98 FROM ra_terms_lines tl
99 WHERE tl.term_id = p_term_id
100 AND tl.due_days = 0;
101
102 BEGIN
103 IF PG_DEBUG in ('Y', 'C') THEN
104 arp_util.debug('AR_VIEW_TERM_GRP.pay_now_amounts()+');
105 END IF;
106
107 -- Standard call to check for call compatibility.
108 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
109 p_api_version ,
110 l_api_name ,
111 G_PKG_NAME )
112 THEN
113 IF PG_DEBUG in ('Y', 'C') THEN
114 arp_util.debug('Unexpected error: wrong API version '||sqlerrm||
115 ' at AR_VIEW_TERM_GRP.pay_now_amounts()+');
116 END IF;
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END IF;
119 -- Initialize message list if p_init_msg_list is set to TRUE.
120 IF FND_API.to_Boolean( p_init_msg_list )
121 THEN
122 FND_MSG_PUB.initialize;
123 END IF;
124 -- Initialize API return status to success
125 x_return_status := FND_API.G_RET_STS_SUCCESS;
126
127 -- Check if term_id is in cache, and insert if not
128 IF NOT pay_now_cache.EXISTS(p_term_id) THEN
129 OPEN c_term(p_term_id);
130 FETCH c_term INTO pay_now_cache(p_term_id).base_amount,
131 pay_now_cache(p_term_id).first_installment_code;
132 IF c_term%NOTFOUND THEN
133 FND_MESSAGE.set_name('AR','AR_TAPI_INVALID_TERMS_ID');
134 FND_MESSAGE.set_token('INVALID_VALUE',p_term_id);
135 FND_MSG_PUB.Add;
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138 CLOSE c_term;
139
140 OPEN c_term_line(p_term_id);
141 FETCH c_term_line INTO pay_now_cache(p_term_id).relative_amount_total,
142 pay_now_cache(p_term_id).first_installment_count;
143 CLOSE c_term_line;
144 END IF;
145 -- Calculate pay now amounts
146 l_pay_now_percent := pay_now_cache(p_term_id).relative_amount_total / pay_now_cache(p_term_id).base_amount;
147
148 l_pay_now_line_amount := p_line_amount * l_pay_now_percent;
149
150 BEGIN
151 l_pay_now_line_amt_rnd := arpcurr.CurrRound(l_pay_now_line_amount, p_currency_code);
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 FND_MESSAGE.set_name('AR','AR_CC_INVALID_CURRENCY');
155 FND_MSG_PUB.Add;
156 RAISE FND_API.G_EXC_ERROR;
157 END;
158
159 x_pay_now_line_amount := l_pay_now_line_amt_rnd;
160 IF pay_now_cache(p_term_id).first_installment_code = 'ALLOCATE' THEN
161 l_pay_now_tax_amount := p_tax_amount * l_pay_now_percent;
162 l_pay_now_tax_amt_rnd := arpcurr.CurrRound(l_pay_now_tax_amount, p_currency_code);
163 l_pay_now_freight_amount := p_freight_amount * l_pay_now_percent;
164 l_pay_now_freight_amt_rnd := arpcurr.CurrRound(l_pay_now_freight_amount, p_currency_code);
165 ELSE
166 l_pay_now_tax_amt_rnd := p_tax_amount * pay_now_cache(p_term_id).first_installment_count;
167 l_pay_now_freight_amt_rnd := p_freight_amount * pay_now_cache(p_term_id).first_installment_count;
168 END IF;
169
170 x_pay_now_tax_amount := l_pay_now_tax_amt_rnd;
171 x_pay_now_freight_amount := l_pay_now_freight_amt_rnd;
172 x_pay_now_total_amount := l_pay_now_line_amt_rnd + l_pay_now_tax_amt_rnd + l_pay_now_freight_amt_rnd;
173
174 IF PG_DEBUG in ('Y', 'C') THEN
175 arp_util.debug('AR_VIEW_TERM_GRP.pay_now_amounts()-');
176 END IF;
177
178 EXCEPTION
179 WHEN FND_API.G_EXC_ERROR THEN
180 x_return_status := FND_API.G_RET_STS_ERROR ;
181 FND_MSG_PUB.Count_And_Get
182 (p_encoded => FND_API.G_FALSE,
183 p_count => x_msg_count,
184 p_data => x_msg_data);
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 IF PG_DEBUG in ('Y', 'C') THEN
187 arp_util.debug('Unexpected error '||sqlerrm||
188 ' at ar_view_term_grp.pay_now_amounts()+');
189 END IF;
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
191 FND_MSG_PUB.Count_And_Get
192 (p_encoded => FND_API.G_FALSE,
193 p_count => x_msg_count,
194 p_data => x_msg_data);
195 WHEN OTHERS THEN
196 IF (SQLCODE = -20001)
197 THEN
198 IF PG_DEBUG in ('Y', 'C') THEN
199 arp_util.debug('20001 error '||
200 ' at ar_view_term_grp.pay_now_amounts()+');
201 END IF;
202 x_return_status := FND_API.G_RET_STS_ERROR ;
203 ELSE
204 IF PG_DEBUG in ('Y', 'C') THEN
205 arp_util.debug('Unexpected error '||sqlerrm||
206 ' at ar_view_term_grp.pay_now_amounts()+');
207 END IF;
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
209 IF FND_MSG_PUB.Check_Msg_Level
210 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
211 THEN
212 FND_MSG_PUB.Add_Exc_Msg
213 ( G_PKG_NAME ,
214 l_api_name
215 );
216 END IF;
217 END IF;
218 FND_MSG_PUB.Count_And_Get
219 (p_encoded => FND_API.G_FALSE,
220 p_count => x_msg_count,
221 p_data => x_msg_data);
222 END pay_now_amounts;
223
224 /*==========================================================================+
225 | PROCEDURE |
226 | pay_now_amounts (overloaded) |
227 | |
228 | DESCRIPTION |
229 | This procedure returns pay now amounts for a given line, tax and |
230 | freight amount and term_id - this version of the procedure has input/ |
231 | output parameters in tables. |
232 | |
233 | SCOPE - PUBLIC |
234 | |
235 | ARGUMENTS : IN OUT : p_amounts_tbl |
236 | |
237 | OUT : x_pay_now_summary_rec |
238 | |
239 | NOTES : |
240 | |
241 | MODIFICATION HISTORY |
242 | 10-NOV-04 JBECKETT Created |
243 | |
244 +===========================================================================*/
245 PROCEDURE pay_now_amounts(
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2,
248 p_validation_level IN NUMBER,
249 p_currency_code IN fnd_currencies.currency_code%TYPE,
250 p_amounts_tbl IN OUT NOCOPY ar_view_term_grp.amounts_table,
251 x_pay_now_summary_rec OUT NOCOPY ar_view_term_grp.summary_amounts_rec,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2)
255 IS
256 l_api_name CONSTANT VARCHAR2(30) := 'pay_now_amounts';
257 l_api_version CONSTANT NUMBER := 1.0;
258 l_msg_count NUMBER;
259
260 l_pay_now_line_amount NUMBER;
261 l_pay_now_tax_amount NUMBER;
262 l_pay_now_freight_amount NUMBER;
263 l_pay_now_total_amount NUMBER;
264 l_pay_now_line_amt_sum NUMBER;
265 l_pay_now_tax_amt_sum NUMBER;
266 l_pay_now_freight_amt_sum NUMBER;
267 l_pay_now_total_sum NUMBER;
268
269 BEGIN
270 IF PG_DEBUG in ('Y', 'C') THEN
271 arp_util.debug('AR_VIEW_TERM_GRP.pay_now_amounts(2)+');
272 END IF;
273
274 -- Standard call to check for call compatibility.
275 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
276 p_api_version ,
277 l_api_name ,
278 G_PKG_NAME )
279 THEN
280 IF PG_DEBUG in ('Y', 'C') THEN
281 arp_util.debug('Unexpected error: wrong API version '||sqlerrm||
282 ' at AR_VIEW_TERM_GRP.pay_now_amounts()+');
283 END IF;
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END IF;
286 -- Initialize message list if p_init_msg_list is set to TRUE.
287 IF FND_API.to_Boolean( p_init_msg_list )
288 THEN
289 FND_MSG_PUB.initialize;
290 END IF;
291 -- Initialize API return status to success
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293 x_msg_count := 0;
294
295 l_pay_now_line_amt_sum := 0;
296 l_pay_now_tax_amt_sum := 0;
297 l_pay_now_freight_amt_sum := 0;
298 l_pay_now_total_sum := 0;
299
300 FOR i in p_amounts_tbl.FIRST..p_amounts_tbl.LAST LOOP
301
302 ar_view_term_grp.pay_now_amounts
303 (p_api_version => 1.0,
304 p_init_msg_list => FND_API.G_FALSE,
305 p_term_id => p_amounts_tbl(i).term_id,
306 p_currency_code => p_currency_code,
307 p_line_amount => p_amounts_tbl(i).line_amount,
308 p_tax_amount => p_amounts_tbl(i).tax_amount,
309 p_freight_amount => p_amounts_tbl(i).freight_amount,
310 x_pay_now_line_amount => l_pay_now_line_amount,
311 x_pay_now_tax_amount => l_pay_now_tax_amount,
312 x_pay_now_freight_amount => l_pay_now_freight_amount,
316 x_msg_data => x_msg_data);
313 x_pay_now_total_amount => l_pay_now_total_amount,
314 x_return_status => x_return_status,
315 x_msg_count => l_msg_count,
317
318 p_amounts_tbl(i).line_amount := l_pay_now_line_amount;
319 p_amounts_tbl(i).tax_amount := l_pay_now_tax_amount;
320 p_amounts_tbl(i).freight_amount := l_pay_now_freight_amount;
321 p_amounts_tbl(i).total_amount := l_pay_now_total_amount;
322
323 x_msg_count := x_msg_count + l_msg_count;
324
325 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
326 RAISE FND_API.G_EXC_ERROR;
327 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 END IF;
330
331 l_pay_now_line_amt_sum := l_pay_now_line_amt_sum + l_pay_now_line_amount;
332 l_pay_now_tax_amt_sum := l_pay_now_tax_amt_sum + l_pay_now_tax_amount;
333 l_pay_now_freight_amt_sum := l_pay_now_freight_amt_sum + l_pay_now_freight_amount;
334 l_pay_now_total_sum := l_pay_now_total_sum + l_pay_now_total_amount;
335
336 END LOOP;
337
338 x_pay_now_summary_rec.line_amount := l_pay_now_line_amt_sum;
339 x_pay_now_summary_rec.tax_amount := l_pay_now_tax_amt_sum;
340 x_pay_now_summary_rec.freight_amount := l_pay_now_freight_amt_sum;
341 x_pay_now_summary_rec.total_amount := l_pay_now_total_sum;
342
343 IF PG_DEBUG in ('Y', 'C') THEN
344 arp_util.debug('AR_VIEW_TERM_GRP.pay_now_amounts(2)-');
345 END IF;
346
347 EXCEPTION
348 WHEN FND_API.G_EXC_ERROR THEN
349 x_return_status := FND_API.G_RET_STS_ERROR ;
350 FND_MSG_PUB.Count_And_Get
351 (p_encoded => FND_API.G_FALSE,
352 p_count => x_msg_count,
353 p_data => x_msg_data);
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 IF PG_DEBUG in ('Y', 'C') THEN
356 arp_util.debug('Unexpected error '||sqlerrm||
357 ' at ar_view_term_grp.pay_now_amounts(2)+');
358 END IF;
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
360 FND_MSG_PUB.Count_And_Get
361 (p_encoded => FND_API.G_FALSE,
362 p_count => x_msg_count,
363 p_data => x_msg_data);
364 WHEN OTHERS THEN
365 IF (SQLCODE = -20001)
366 THEN
367 IF PG_DEBUG in ('Y', 'C') THEN
368 arp_util.debug('20001 error '||
369 ' at ar_view_term_grp.pay_now_amounts(2)+');
370 END IF;
371 x_return_status := FND_API.G_RET_STS_ERROR ;
372 ELSE
373 IF PG_DEBUG in ('Y', 'C') THEN
374 arp_util.debug('Unexpected error '||sqlerrm||
375 ' at ar_view_term_grp.pay_now_amounts(2)+');
376 END IF;
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
378 IF FND_MSG_PUB.Check_Msg_Level
379 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
380 THEN
381 FND_MSG_PUB.Add_Exc_Msg
382 ( G_PKG_NAME ,
383 l_api_name
384 );
385 END IF;
386 END IF;
387 FND_MSG_PUB.Count_And_Get
388 (p_encoded => FND_API.G_FALSE,
389 p_count => x_msg_count,
390 p_data => x_msg_data);
391 END pay_now_amounts;
392
393 END AR_VIEW_TERM_GRP;