4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_CM_APP_LIB_PVT';
1 PACKAGE BODY ar_cm_app_lib_pvt AS
2 /* $Header: ARXCMALB.pls 120.2 2005/07/22 15:40:30 naneja ship $ */
3
5
6 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
7 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
8 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
9 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
10 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
11 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
12
13 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
14
15 PROCEDURE Default_customer_trx_id(
16 p_customer_trx_id IN OUT NOCOPY NUMBER,
17 p_trx_number IN VARCHAR,
18 p_return_status OUT NOCOPY VARCHAR2
19 ) IS
20 BEGIN
21 IF PG_DEBUG in ('Y', 'C') THEN
22 arp_util.debug('Default_customer_trx_id ()+');
23 END IF;
24 p_return_status := FND_API.G_RET_STS_SUCCESS;
25 IF p_customer_trx_id IS NULL THEN
26 IF p_trx_number IS NOT NULL THEN
27 BEGIN
28 SELECT customer_trx_id
29 INTO p_customer_trx_id
30 FROM ra_customer_trx
31 WHERE trx_number = p_trx_number;
32 EXCEPTION
33 WHEN no_data_found THEN
34 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_INVALID');
35 FND_MSG_PUB.Add;
36 p_return_status := FND_API.G_RET_STS_ERROR ;
37 END;
38 END IF;
39
40 ELSE
41
42 IF p_trx_number IS NOT NULL
43 THEN
44 --give a warning message to indicate that the trx number
45 --entered by the user has been ignored.
46 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
47 THEN
48 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_IGN');
49 FND_MSG_PUB.Add;
50 END IF;
51 END IF;
52 END IF;
53 IF PG_DEBUG in ('Y', 'C') THEN
54 arp_util.debug('Default_customer_trx_id ()-');
55 END IF;
56 EXCEPTION
57 WHEN others THEN
58 IF PG_DEBUG in ('Y', 'C') THEN
59 arp_util.debug('EXCEPTION: Default_customer_trx_id()', G_MSG_UERROR);
60 END IF;
61 END Default_customer_trx_id;
62
63 PROCEDURE Default_gl_date(p_entered_date IN DATE,
64 p_gl_date OUT NOCOPY DATE,
65 p_validation_date IN DATE,
66 p_return_status OUT NOCOPY VARCHAR2) IS
67 l_error_message VARCHAR2(128);
68 l_defaulting_rule_used VARCHAR2(100);
69 l_default_gl_date DATE;
70 BEGIN
71 p_return_status := FND_API.G_RET_STS_SUCCESS;
72 IF PG_DEBUG in ('Y', 'C') THEN
73 arp_util.debug('Default_gl_date ()+');
74 END IF;
75 IF p_gl_date IS NULL THEN
76 IF (arp_util.validate_and_default_gl_date(
77 p_entered_date,
78 NULL,
79 p_validation_date,
80 NULL,
81 NULL,
82 p_entered_date,
83 NULL,
84 NULL,
85 'N',
86 NULL,
87 arp_global.set_of_books_id,
88 222,
89 l_default_gl_date,
90 l_defaulting_rule_used,
91 l_error_message) = TRUE)
92 THEN
93 p_gl_date := l_default_gl_date;
94 IF PG_DEBUG in ('Y', 'C') THEN
95 arp_util.debug('Default_gl_date: ' || 'Defaulted GL Date : '||to_char(p_gl_date,'DD-MON-YYYY'));
96 END IF;
97 ELSE
98 IF PG_DEBUG in ('Y', 'C') THEN
99 arp_util.debug('Default_gl_date: ' || 'GL Date could not be defaulted ');
100 END IF;
101 -- Raise error message if failure in defaulting the gl_date
102 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
103 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
104 FND_MSG_PUB.Add;
105 p_return_status := FND_API.G_RET_STS_ERROR;
106 END IF;
107 END IF;
108 IF PG_DEBUG in ('Y', 'C') THEN
109 arp_util.debug('Default_gl_date ()-');
110 END IF;
111 END default_gl_date;
112
113 PROCEDURE Default_CM_Info(
117 p_trx_date OUT NOCOPY DATE,
114 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
115 p_cm_ps_id OUT NOCOPY NUMBER,
116 p_cm_currency_code OUT NOCOPY VARCHAR2,
118 p_cm_gl_date OUT NOCOPY DATE,
119 p_cm_unapp_amount OUT NOCOPY NUMBER,
120 p_cm_receipt_method_id OUT NOCOPY NUMBER,
121 p_return_status OUT NOCOPY VARCHAR2
122 ) IS
123
124 BEGIN
125 IF PG_DEBUG in ('Y', 'C') THEN
126 arp_util.debug('Default_CM_Info ()+');
127 END IF;
128 p_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 IF p_customer_trx_id IS NOT NULL THEN
131
132 SELECT
133 ps.payment_schedule_id
134 , ps.invoice_currency_code
135 , ct.trx_date
136 , ps.gl_date
137 , (ps.amount_due_remaining * -1)
138 , ct.receipt_method_id
139 INTO
140 p_cm_ps_id,
141 p_cm_currency_code,
142 p_trx_date,
143 p_cm_gl_date ,
144 p_cm_unapp_amount,
145 p_cm_receipt_method_id
146 FROM
147 ra_customer_trx ct
148 , ar_payment_schedules ps
149 WHERE
150 ps.class = 'CM'
151 AND ct.customer_trx_id(+) = ps.customer_trx_id
152 AND ct.previous_customer_trx_id is null
153 AND ct.customer_trx_id = p_customer_trx_id
154 ;
155
156 ELSE --case when p_customer_trx_id is null
157 --no further validation done in the validation routines for customer_trx_id
158 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_NULL');
159 FND_MSG_PUB.Add;
160 p_return_status := FND_API.G_RET_STS_ERROR ;
161
162 END IF;
163
164 IF PG_DEBUG in ('Y', 'C') THEN
165 arp_util.debug('Default_CM_Info ()-');
166 END IF;
167 EXCEPTION
168 WHEN no_data_found THEN
169 IF PG_DEBUG in ('Y', 'C') THEN
170 arp_util.debug('Default_CM_Info : No data found ');
171 END IF;
172
173 p_return_status := FND_API.G_RET_STS_ERROR ;
174 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
175 FND_MSG_PUB.ADD;
176
177 WHEN others THEN
178 IF PG_DEBUG in ('Y', 'C') THEN
179 arp_util.debug('EXCEPTION: Default_CM_Info: sqlerrm()');
180 END IF;
181 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
182 raise;
183 END Default_CM_Info;
184
185 PROCEDURE Default_activity_info(
186 p_customer_trx_id IN NUMBER,
187 p_cm_ps_id OUT NOCOPY NUMBER,
188 p_cm_currency_code OUT NOCOPY VARCHAR2,
189 p_cm_gl_date OUT NOCOPY DATE,
190 p_cm_unapp_amount OUT NOCOPY NUMBER,
191 p_cm_receipt_method_id OUT NOCOPY NUMBER,
192 p_trx_date OUT NOCOPY DATE,
193 p_amount_applied IN OUT NOCOPY NUMBER,
194 p_apply_date IN OUT NOCOPY DATE,
195 p_apply_gl_date IN OUT NOCOPY DATE,
196 p_return_status OUT NOCOPY VARCHAR2
197 ) IS
198 l_cm_amount NUMBER;
199 l_cm_return_status VARCHAR2(1);
200 l_gl_date_return_status VARCHAR2(1);
201 l_trx_date DATE;
202 l_amount_applied NUMBER;
203 l_cm_unapp_amount NUMBER;
204
205 BEGIN
206 IF PG_DEBUG in ('Y', 'C') THEN
207 arp_util.debug('Default_activity_info ()+');
208 END IF;
209 p_return_status := FND_API.G_RET_STS_SUCCESS;
210 l_cm_return_status := FND_API.G_RET_STS_SUCCESS;
211 l_gl_date_return_status := FND_API.G_RET_STS_SUCCESS;
212
213 l_amount_applied := p_amount_applied;
214
215 Default_CM_Info( p_customer_trx_id ,
216 p_cm_ps_id,
217 p_cm_currency_code,
218 p_trx_date,
219 p_cm_gl_date,
220 p_cm_unapp_amount,
221 p_cm_receipt_method_id,
222 l_cm_return_status
223 );
224 IF PG_DEBUG in ('Y', 'C') THEN
225 arp_util.debug('Default_activity_info: ' || 'CM defaulting return status :'||l_cm_return_status);
226 END IF;
227
228 IF p_apply_date IS NULL THEN
229 p_apply_date := GREATEST(SYSDATE,(NVL(p_trx_date,SYSDATE)));
230 END IF;
231
232 IF p_apply_gl_date IS NULL THEN
233 Default_gl_date(p_cm_gl_date,
234 p_apply_gl_date,
235 NULL,
236 l_gl_date_return_status);
237 END IF;
238 IF PG_DEBUG in ('Y', 'C') THEN
239 arp_util.debug('Default_activity_info: ' || 'Defaulting apply gl date return status :'|| l_gl_date_return_status);
240 END IF;
241
242 --default the amount applied
243 IF l_amount_applied IS NULL THEN
244 l_amount_applied := p_cm_unapp_amount;
245 END IF;
246 IF PG_DEBUG in ('Y', 'C') THEN
247 arp_util.debug('Amount applied: ' || l_amount_applied );
248 END IF;
249
250 --do the precision
251 p_amount_applied := arp_util.CurrRound(
252 l_amount_applied,
253 p_cm_currency_code
254 );
255 IF PG_DEBUG in ('Y', 'C') THEN
256 arp_util.debug('Amount applied: ' || p_amount_applied );
257 END IF;
258
259
260 IF l_cm_return_status <> FND_API.G_RET_STS_SUCCESS OR
261 l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS THEN
262 p_return_status := FND_API.G_RET_STS_ERROR ;
266 IF PG_DEBUG in ('Y', 'C') THEN
263 END IF;
264
265
267 arp_util.debug('Default_activity_info: ' || '***************Default Values *****************');
268 arp_util.debug('Default_activity_info: ' || 'p_customer_trx_id : '||to_char(p_customer_trx_id));
269 arp_util.debug('Default_activity_info: ' || 'p_cm_gl_date : '||to_char(p_cm_gl_date,'DD-MON-YYYY'));
270 arp_util.debug('Default_activity_info: ' || 'p_cm_unapp_amount : '||to_char(p_cm_unapp_amount));
271 arp_util.debug('Default_activity_info: ' || 'p_amount_applied : '||to_char(p_amount_applied));
272 arp_util.debug('Default_activity_info: ' || 'p_apply_gl_date : '||to_char(p_apply_gl_date,'DD-MON-YYYY'));
273 arp_util.debug('Default_activity_info: ' || 'p_apply_date : '||to_char(p_apply_date,'DD-MON-YYYY'));
274 arp_util.debug('Default_activity_info ()-');
275 END IF;
276 EXCEPTION
277 WHEN others THEN
278 IF PG_DEBUG in ('Y', 'C') THEN
279 arp_util.debug('EXCEPTION: Default_activity_info() ');
280 END IF;
281 raise;
282 END Default_activity_info;
283
284 PROCEDURE Derive_activity_unapp_ids(
285 p_trx_number IN VARCHAR2,
286 p_customer_trx_id IN OUT NOCOPY NUMBER,
287 p_receivable_application_id IN OUT NOCOPY NUMBER,
288 p_apply_gl_date OUT NOCOPY DATE,
289 p_return_status OUT NOCOPY VARCHAR2
290 ) IS
291 l_rec_appln_id NUMBER ;
292 l_apply_gl_date DATE;
293 l_customer_trx_id NUMBER;
294 BEGIN
295 p_return_status := FND_API.G_RET_STS_SUCCESS;
296 IF PG_DEBUG in ('Y', 'C') THEN
297 arp_util.debug('Derive_activity_unapp_ids ()+');
298 END IF;
299 --derive the customer_trx_id from the trx_number
300 IF p_trx_number IS NOT NULL THEN
301 Default_customer_trx_id (p_customer_trx_id ,
302 p_trx_number ,
303 p_return_status);
304 END IF;
305 l_customer_trx_id := p_customer_trx_id;
306
307 --get the receivable application id for the activity application
308 --on this credit memo. If more than one activity application exists
309 --raise error.
310 IF p_customer_trx_id IS NOT NULL THEN
311
312 BEGIN
313 SELECT receivable_application_id, gl_date
314 INTO l_rec_appln_id , p_apply_gl_date
315 FROM ar_receivable_applications
316 WHERE customer_trx_id = p_customer_trx_id
317 AND display = 'Y'
318 AND applied_payment_schedule_id = -8
319 AND status = 'ACTIVITY';
320 EXCEPTION
321 WHEN no_data_found THEN
322 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_INVALID');
323 FND_MSG_PUB.Add;
324 p_return_status := FND_API.G_RET_STS_ERROR ;
325 WHEN too_many_rows THEN
326 IF p_receivable_application_id IS NULL THEN
327 FND_MESSAGE.SET_NAME('AR','AR_RAPI_MULTIPLE_ACTIVITY_APP');
328 FND_MSG_PUB.Add;
329 p_return_status := FND_API.G_RET_STS_ERROR ;
330 END IF;
331
332 END;
333
334 END IF;
335
336 IF p_receivable_application_id IS NOT NULL THEN
337
338 BEGIN
339 SELECT customer_trx_id, gl_date
340 INTO l_customer_trx_id , p_apply_gl_date
341 FROM ar_receivable_applications
342 WHERE receivable_application_id = p_receivable_application_id
343 and display = 'Y'
344 and applied_payment_schedule_id = -8
345 and status = 'ACTIVITY';
346 EXCEPTION
347 WHEN no_data_found THEN
348 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
349 FND_MSG_PUB.Add;
350 p_return_status := FND_API.G_RET_STS_ERROR ;
351 END;
352
353 --Compare the two customer_trx_ids
354 IF p_customer_trx_id IS NOT NULL THEN
355 IF p_customer_trx_id <> NVL(l_customer_trx_id,p_customer_trx_id) THEN
356 --raise error X validation failed
357 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_RA_ID_X_INVALID');
358 FND_MSG_PUB.Add;
359 p_return_status := FND_API.G_RET_STS_ERROR ;
360 END IF;
361 END IF;
362
363 ELSE
364 p_receivable_application_id := l_rec_appln_id ;
365 END IF;
366
367 IF p_customer_trx_id IS NULL THEN
368 p_customer_trx_id := l_customer_trx_id;
369 END IF;
370
371 IF PG_DEBUG in ('Y', 'C') THEN
372 arp_util.debug('Derive_activity_unapp_ids ()+');
373 END IF;
374 END Derive_activity_unapp_ids;
375
376 PROCEDURE Default_unapp_activity_info(
377 p_receivable_application_id IN NUMBER,
378 p_apply_gl_date IN DATE,
379 p_customer_trx_id IN NUMBER,
380 p_reversal_gl_date IN OUT NOCOPY DATE,
381 p_cm_gl_date OUT NOCOPY DATE,
382 p_cm_ps_id OUT NOCOPY NUMBER,
383 p_cm_unapp_amount OUT NOCOPY NUMBER,
384 p_return_status OUT NOCOPY VARCHAR2
385 ) IS
386 l_apply_date DATE;
387 l_apply_gl_date DATE;
388 l_customer_trx_id NUMBER;
389 l_rec_appln_id NUMBER;
390 l_default_gl_date DATE;
391 l_defaulting_rule_used VARCHAR2(100);
392 l_error_message VARCHAR2(240);
393 BEGIN
397 l_apply_gl_date := p_apply_gl_date;
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_util.debug('Default_unapp_activity_info: ' || 'Default_unapp_activity_info ()+');
396 END IF;
398 l_customer_trx_id := p_customer_trx_id;
399
400 IF p_reversal_gl_date is null THEN
401 IF (arp_util.validate_and_default_gl_date(
402 nvl(l_apply_gl_date,trunc(sysdate)),
403 NULL,
404 l_apply_gl_date,
405 NULL,
406 NULL,
407 NULL,
408 NULL,
409 NULL,
410 'N',
411 NULL,
412 arp_global.set_of_books_id,
413 222,
414 l_default_gl_date,
415 l_defaulting_rule_used,
416 l_error_message) = TRUE) THEN
417
418 p_reversal_gl_date := l_default_gl_date;
419 ELSE
420 --we were not able to default the gl_date put the message
421 --here on the stack, but the return status will be set
422 --to FND_API.G_RET_STS_ERROR in the validation phase.
423 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
424 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
425 FND_MSG_PUB.Add;
426 END IF;
427 END IF;
428
429 IF p_receivable_application_id IS NOT NULL THEN
430 BEGIN
431 SELECT customer_trx_id
432 INTO l_customer_trx_id
433 FROM ar_receivable_applications
434 WHERE receivable_application_id = p_receivable_application_id
435 AND applied_payment_schedule_id = -8
436 AND status = 'ACTIVITY'
437 and display = 'Y';
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
441 FND_MSG_PUB.Add;
442 p_return_status := FND_API.G_RET_STS_ERROR ;
443 END;
444 ELSIF p_customer_trx_id IS NOT NULL THEN
445 BEGIN
446 SELECT receivable_application_id
447 INTO l_rec_appln_id
448 FROM ar_receivable_applications
449 WHERE customer_trx_id = p_customer_trx_id
450 AND applied_payment_schedule_id = -8
451 AND display = 'Y'
452 AND status = 'ACTIVITY';
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
456 FND_MSG_PUB.Add;
457 p_return_status := FND_API.G_RET_STS_ERROR ;
458 WHEN TOO_MANY_ROWS THEN
459 FND_MESSAGE.SET_NAME('AR','AR_RAPI_MULTIPLE_ACTIVITY_APP');
460 FND_MSG_PUB.Add;
461 p_return_status := FND_API.G_RET_STS_ERROR ;
462
463 END;
464 END IF;
465
466 IF l_customer_trx_id IS NOT NULL
467 THEN
468 BEGIN
469 SELECT gl_date,
470 payment_schedule_id,
471 (amount_due_remaining * -1)
472 INTO p_cm_gl_date,
473 p_cm_ps_id,
474 p_cm_unapp_amount
475 FROM ar_payment_schedules
476 WHERE customer_trx_id = l_customer_trx_id;
477 EXCEPTION
478 WHEN no_data_found THEN
479 IF PG_DEBUG in ('Y', 'C') THEN
480 arp_util.debug('Default_unapp_activity_info: ' || 'Could not get the cm_gl_date. ');
481 END IF;
482 RAISE;
483 END;
484 END IF;
485 IF PG_DEBUG in ('Y', 'C') THEN
486 arp_util.debug('Default_unapp_activity_info: ' || '*****Defaulted Values *********');
487 arp_util.debug('Default_unapp_activity_info: ' || 'p_customer_trx_id : '||to_char(p_customer_trx_id));
488 arp_util.debug('Default_unapp_activity_info: ' || 'p_receivable_application_id : '||to_char(p_receivable_application_id));
489 arp_util.debug('Default_unapp_activity_info: ' || 'p_apply_gl_date : '||to_char(p_apply_gl_date,'DD-MON-YYYY'));
490 arp_util.debug('Default_unapp_activity_info: ' || 'p_reversal_gl_date : '||to_char(p_reversal_gl_date,'DD-MON-YYYY'));
491 arp_util.debug('Default_unapp_activity_info: ' || 'p_cm_unapp_amount : '||p_cm_unapp_amount);
492 arp_util.debug('Default_unapp_activity_info: ' || 'p_cm_ps_id : '||p_cm_ps_id);
493 arp_util.debug('Default_unapp_activity_info: ' || 'Default_unapp_on_acc_act_info ()-');
494 END IF;
495
496 END Default_unapp_activity_info;
497
498 END ar_cm_app_lib_pvt;
[Home] [Help]