1 Package Body AR_RECEIPT_VAL_PVT AS
2 /* $Header: ARXPREVB.pls 120.62.12020000.3 2013/03/01 06:59:45 riqi ship $ */
3 --Validation procedures are contained in this package
4
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11
12 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 PROCEDURE Validate_Receipt_Date(p_receipt_date IN DATE,
15 p_return_status OUT NOCOPY VARCHAR2) IS
16 BEGIN
17 IF PG_DEBUG in ('Y', 'C') THEN
18 arp_util.debug('Validate_Receipt_Date()+');
19 END IF;
20
21 p_return_status := FND_API.G_RET_STS_SUCCESS;
22
23 IF PG_DEBUG in ('Y', 'C') THEN
24 arp_util.debug('Validate_Receipt_Date()-');
25 END IF;
26 END Validate_Receipt_Date;
27
28 PROCEDURE Validate_Gl_Date(p_gl_date IN DATE,
29 p_return_status OUT NOCOPY VARCHAR2) IS
30 BEGIN
31 IF PG_DEBUG in ('Y', 'C') THEN
32 arp_util.debug('Validate_Gl_Date ()+');
33 END IF;
34 p_return_status := FND_API.G_RET_STS_SUCCESS;
35 IF ( NOT arp_util.is_gl_date_valid( p_gl_date )) THEN
36 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
37 --Int'l Calendar Project
38 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
39 FND_MSG_PUB.Add;
40 p_return_status := FND_API.G_RET_STS_ERROR;
41 END IF;
42 IF PG_DEBUG in ('Y', 'C') THEN
43 arp_util.debug('Validate_Gl_Date ()+');
44 END IF;
45 END Validate_Gl_Date;
46
47 PROCEDURE Validate_Deposit_Date(p_deposit_date IN DATE,
48 p_return_status OUT NOCOPY VARCHAR2) IS
49 BEGIN
50 IF PG_DEBUG in ('Y', 'C') THEN
51 arp_util.debug('Validate_Deposit_Date ()+');
52 END IF;
53 p_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 END Validate_Deposit_Date;
56
57 PROCEDURE Validate_Maturity_Date(p_maturity_date IN DATE,
58 p_receipt_date IN DATE,
59 p_return_status OUT NOCOPY VARCHAR2) IS
60 BEGIN
61 IF PG_DEBUG in ('Y', 'C') THEN
62 arp_util.debug('Validate_Maturity_Date ()+');
63 END IF;
64 IF (p_maturity_date < p_receipt_date) THEN
65 p_return_status := FND_API.G_RET_STS_ERROR;
66 FND_MESSAGE.set_name( 'AR','AR_RW_MAT_BEFORE_RCT_DATE');
67 -- arp_util.debug('m'||FND_MESSAGE.GET_ENCODED);
68 FND_MSG_PUB.ADD;
69 ELSE
70 p_return_status := FND_API.G_RET_STS_SUCCESS;
71 END IF;
72 IF PG_DEBUG in ('Y', 'C') THEN
73 arp_util.debug('Validate_Maturity_Date ()-');
74 END IF;
75 END Validate_Maturity_Date;
76
77 PROCEDURE Validate_amount(p_amount IN OUT NOCOPY NUMBER,
78 p_factor_discount_amount IN NUMBER,
79 p_state IN VARCHAR2,
80 p_type IN VARCHAR2,
81 p_return_status OUT NOCOPY VARCHAR2) IS
82 BEGIN
83 IF PG_DEBUG in ('Y', 'C') THEN
84 arp_util.debug('Validate_amount () +');
85 END IF;
86 p_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 --Raise error if the receipt amount is null or negative
89 IF p_amount IS NULL THEN
90 p_return_status := FND_API.G_RET_STS_ERROR;
91 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_AMOUNT_NULL');
92 FND_MSG_PUB.Add;
93
94 ElSIF (p_amount < 0) AND (p_type = 'CASH')
95 THEN
96 p_return_status := FND_API.G_RET_STS_ERROR;
97 FND_MESSAGE.SET_NAME('AR','AR_RW_RCT_AMOUNT_NEGATIVE');
98 FND_MSG_PUB.Add;
99
100 END IF;
101
102
103 -- If the profile option AR : Create bank charges = No or
104 -- the state is not CONFIRMED then raise error if the
105 -- bank charges exist.
106
107 IF (NVL(ar_receipt_lib_pvt.pg_profile_create_bk_charges,'N') = 'N')
108 THEN
109
110 IF ( NVL(p_factor_discount_amount,0) <> 0 )
111 THEN
112 p_return_status := FND_API.G_RET_STS_ERROR;
113 FND_MESSAGE.SET_NAME('AR', 'AR_BK_CH_NOT_ALLWD_IF_NOT_CLR');
114 FND_MSG_PUB.Add;
115 IF PG_DEBUG in ('Y', 'C') THEN
116 arp_util.debug('Validate_amount: ' || 'Bank charges are not allowed ');
117 END IF;
118 END IF;
119
120 ELSE
121
122 IF (p_state <> 'CLEARED') AND (NVL(p_factor_discount_amount,0) <> 0 )
123 THEN
124 -- raise error about bank charges not allowed because the
125 -- state of the receipt is <> 'CLEARED'
126 p_return_status := FND_API.G_RET_STS_ERROR;
127 FND_MESSAGE.SET_NAME('AR','AR_BK_CH_NOT_ALLWD_IF_NOT_CLR');
128 FND_MSG_PUB.Add;
129 IF PG_DEBUG in ('Y', 'C') THEN
130 arp_util.debug('Validate_amount: ' || 'The bank charges are not allowed if the state <> CLEARED');
131 END IF;
132 END IF;
133
134 IF p_factor_discount_amount < 0 THEN
135
136 -- Raise error if the bank charges amount less than 0
137 p_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MESSAGE.SET_NAME('AR','AR_JG_BC_AMOUNT_NEGATIVE');
139 FND_MSG_PUB.Add;
140 END IF;
141
142 p_amount := nvl(p_amount,0) + nvl(p_factor_discount_amount,0);
143
144 IF (p_amount < 0) AND (p_type = 'CASH')
145 THEN
146 p_return_status := FND_API.G_RET_STS_ERROR;
147 FND_MESSAGE.SET_NAME('AR','AR_RW_RCT_AMOUNT_NEGATIVE');
148 FND_MSG_PUB.Add;
149 IF PG_DEBUG in ('Y', 'C') THEN
150 arp_util.debug('Validate_amount: ' || 'Receipt amount is negative ');
151 END IF;
152 END IF;
153
154 END IF;
155 IF PG_DEBUG in ('Y', 'C') THEN
156 arp_util.debug('Validate_amount () +');
157 END IF;
158 END Validate_amount;
159
160 PROCEDURE Validate_Customer(p_customer_id IN NUMBER,
161 /* 6612301 */
162 p_customer_bank_account_id IN OUT NOCOPY NUMBER,
163 p_location IN VARCHAR2,
164 p_customer_site_use_id IN OUT NOCOPY NUMBER,
165 p_currency_code IN VARCHAR2,
166 p_receipt_date IN DATE,
167 p_return_status OUT NOCOPY VARCHAR2) IS
168
169 l_temp BINARY_INTEGER;
170 l_dummy_cust Customer_Rec;
171 l_record_exists_in_cache VARCHAR2(2);
172 BEGIN
173 IF PG_DEBUG in ('Y', 'C') THEN
174 arp_util.debug('Validate_amount: ' || 'Validate_Customer_id()+');
175 END IF;
176 p_return_status := FND_API.G_RET_STS_SUCCESS;
177 /* l_temp := Customer_Cache_Tbl.LAST;
178 IF PG_DEBUG in ('Y', 'C') THEN
179 arp_util.debug('Validate_amount: ' || 'cache ');
180 END IF;
181
182 IF l_temp IS NULL THEN
183 --The cache is empty : populate it directly.
184 l_record_exists_in_cache := 'N';
185
186 ELSE
187 --this is the case where records exist in the cache, compare the current record
188 --with these records.
189
190 l_record_exists_in_cache := 'N';
191
192 FOR l_counter IN 1..l_temp LOOP
193 IF (Customer_Cache_Tbl(l_counter).customer_id = p_customer_id) AND
194 (Customer_Cache_Tbl(l_counter).site_use_id = p_customer_site_use_id)
195 THEN
196 --Current record exists in the cache
197 l_record_exists_in_cache := 'Y';
198 EXIT;
199 END IF;
200 END LOOP;
201 END IF;
202
203 IF PG_DEBUG in ('Y', 'C') THEN
204 arp_util.debug('Validate_amount: ' || 'record_exists_in_cache = '||l_record_exists_in_cache);
205 END IF;
206 */
207 IF (p_customer_id IS NOT NULL) THEN
208 IF PG_DEBUG in ('Y', 'C') THEN
209 arp_util.debug('Validate_amount: ' || 'Now Validating Customer id ');
210 END IF;
211 /*--------------------------------+
212 | |
213 | Validate Customer_id |
214 | |
215 +--------------------------------*/
216
217 -- IF l_record_exists_in_cache = 'N' THEN
218
219 /* modified for tca uptake */
220 /* fixed bug 1544201: removed references to
221 customer_prospect_code */
222 BEGIN
223 SELECT cust.cust_account_id
224 INTO l_dummy_cust.customer_id
225 FROM hz_cust_accounts cust,
226 hz_customer_profiles cp,
227 hz_parties party
228 WHERE cust.cust_account_id = cp.cust_account_id (+) and
229 cp.site_use_id is null and
230 cust.cust_account_id = p_customer_id and
231 cust.party_id = party.party_id;
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 p_return_status := FND_API.G_RET_STS_ERROR;
235 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_ID_INVALID');
236 FND_MSG_PUB.Add;
237
238 WHEN OTHERS THEN
239 IF PG_DEBUG in ('Y', 'C') THEN
240 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Cache_Customer_id() ');
241 arp_util.debug('Validate_amount: ' || 'p_customer_id = ' ||TO_CHAR(p_customer_id));
242 END IF;
243 RAISE;
244 END;
245
246 /*------------------------------------+
247 | |
248 | Validate Customer site_use_id |
249 | |
250 +------------------------------------*/
251 --There is no point in validating the customer_site_use_id
252 --and the customer bank account id if the validation of
253 --customer id has failed
254
255 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
256 --no need to validate site_use_id if derived from the transaction
257 IF ar_receipt_lib_pvt.pg_cust_derived_from IS NULL THEN
258
259 IF p_customer_site_use_id IS NOT NULL THEN
260 --no need to validate site_use_id if derived from the transaction
261 IF PG_DEBUG in ('Y', 'C') THEN
262 arp_util.debug('Validate_amount: ' || 'Now validating Customer site_use_id ');
263 END IF;
264 BEGIN
265 SELECT site_uses.site_use_id
266 INTO l_dummy_cust.site_use_id
267 FROM hz_cust_site_uses_all site_uses,
268 hz_cust_acct_sites acct_site
269 WHERE acct_site.cust_account_id = p_customer_id
270 /*AND acct_site.status = 'A' Bug 4317815*/
271 AND acct_site.cust_acct_site_id =
272 site_uses.cust_acct_site_id
273 AND site_uses.site_use_code IN ('BILL_TO','DRAWEE')
274 /*AND site_uses.status = 'A' Bug 4317815*/
275 AND site_uses.site_use_id = p_customer_site_use_id;
276 ar_receipt_lib_pvt.pg_cust_derived_from := NULL;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 p_return_status := FND_API.G_RET_STS_ERROR;
280 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_STE_USE_ID_INVALID');
281 FND_MSG_PUB.Add;
282
283 WHEN OTHERS THEN
284 IF PG_DEBUG in ('Y', 'C') THEN
285 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Validate_Customer_Site_Use_id() ');
286 arp_util.debug('Validate_amount: ' || 'p_customer_site_use_id = '
287 ||TO_CHAR(p_customer_site_use_id));
288 END IF;
289 RAISE;
290 END;
291
292 ELSE
293 --here we need to differentiate between the case where the location was
294 --passed in but the site_use_id could not be derived and the case where
295 --location was not passed in
296 IF p_location IS NOT NULL THEN
297 -- for the specified location there is no data in
298 -- hz_cust_site_uses
299 -- the error message was not raised in the defaulting routine.
300 p_return_status := FND_API.G_RET_STS_ERROR;
301 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_LOC_INVALID');
302 FND_MSG_PUB.Add;
303 ELSE
304
305 --This is the case where customer site use id is null, neither it was supplied
306 --by the user nor it could be defaulted a WARNING message is raised to
307 --indicate that the customer site use id could not be defaulted.
308
309 IF nvl(arp_global.sysparam.site_required_flag,'N') = 'Y' THEN
310 --error
311 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_STE_USE_ID_NOT_DEF');
312 FND_MSG_PUB.Add;
313 p_return_status := FND_API.G_RET_STS_ERROR;
314 ELSE
315 --warning
316 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
317 THEN
318 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_STE_USE_ID_NOT_DEF');
319 FND_MSG_PUB.Add;
320 IF PG_DEBUG in ('Y', 'C') THEN
321 arp_util.debug('Validate_amount: ' || 'Customer site use id is null');
322 END IF;
323 END IF;
324 END IF;
325
326 END IF;
327 END IF;
328 ELSE
329 ar_receipt_lib_pvt.pg_cust_derived_from := NULL;
330 END IF;
331
332 /*------------------------------------+
333 | |
334 | Validate Customer bank_account_id |
335 | |
336 +------------------------------------*/
337 /* 6612301 */
338 /* Revert changes done for customer bank ref under payment uptake */
339 IF p_customer_bank_account_id IS NOT NULL THEN
340
341 BEGIN
342 /* SELECT ba.bank_account_id
343 INTO l_dummy_cust.bank_account_id
344 FROM ap_bank_accounts ba,
345 ap_bank_account_uses bau
346 WHERE ba.bank_account_id = bau.external_bank_account_id
347 and bau.customer_id = p_customer_id
348 and (bau.customer_site_use_id is null
349 or bau.customer_site_use_id = p_customer_site_use_id)
350 and (ba.currency_code = p_currency_code or
351 ba.bank_branch_id = 1)
352 -- OSTEINME 2/27/2001: change for iReceivables:
353 -- for credit card bank accounts the currency is
354 -- irrelevant. See bug 1659130
355 and p_receipt_date
356 between nvl(bau.start_date,p_receipt_date)
357 and nvl(bau.end_date,p_receipt_date)
358 and nvl(ba.inactive_date,p_receipt_date) >=
359 p_receipt_date
360 and ba.bank_account_id = p_customer_bank_account_id; */
361
362 select bb.bank_account_id
363 into l_dummy_cust.bank_account_id
364 from iby_fndcpt_payer_assgn_instr_v a,
365 iby_ext_bank_accounts_v bb
366 where a.cust_account_id = p_customer_id
367 and a.instrument_type = 'BANKACCOUNT'
368 and ( a.acct_site_use_id = p_customer_site_use_id or a.acct_site_use_id is null)
369 and p_receipt_date between nvl(trunc(bb.start_date),p_receipt_date)
370 and nvl(trunc(bb.end_date),p_receipt_date)
371 and a.currency_code = p_currency_code
372 and bb.ext_bank_account_id = a.instrument_id
373 and bb.bank_account_id = p_customer_bank_account_id;
374
375 EXCEPTION
376 WHEN no_data_found THEN
377 IF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_id IS NOT NULL THEN
378 p_return_status := FND_API.G_RET_STS_ERROR;
379 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_ID_INVALID');
380 FND_MSG_PUB.Add;
381 ELSIF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_num IS NOT NULL THEN
382 p_return_status := FND_API.G_RET_STS_ERROR;
383 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_NUM_INVALID');
384 FND_MSG_PUB.Add;
385 ELSIF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_name IS NOT NULL THEN
386 p_return_status := FND_API.G_RET_STS_ERROR;
387 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_NAME_INVALID');
388 FND_MSG_PUB.Add;
389 END IF;
390 WHEN too_many_rows THEN
391 --Each customer site can have multiple accounts, so if it retrives more than
392 --one record, the validation is true.
393 null;
394 WHEN OTHERS THEN
395 IF PG_DEBUG in ('Y', 'C') THEN
396 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Validate_Customer_Bank_account_id() ');
397 arp_util.debug('Validate_amount: ' || 'p_customer_bank_account_id = '
398 ||TO_CHAR(p_customer_bank_account_id));
399 END IF;
400 RAISE;
401
402 END;
403
404 ELSE
405 --this is the case where the bank account id is neither entered
406 --by the user not could it be defaulted from the bank account number or name.
407 --the error for not being able to default the id from the name/number
408 --already raised in the defaulting routine
409 null;
410 END IF;
411
412 END IF;
413
414 /* IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
415
416 l_temp := nvl(l_temp,0) + 1;
417 Customer_Cache_Tbl(l_temp) := l_dummy_cust;
418
419 END IF;
420 */
421 --END IF; --if record_exists_in_cache = 'N'
422
423 ELSE
424 --If p_customer_id is null and the customer_bank_account_id or
425 --the site_use_id has been passed in, then raise an error.
426 /* 6612301 */
427 IF p_customer_bank_account_id IS NOT NULL THEN
428 IF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_id IS NOT NULL
429 THEN
430 p_return_status := FND_API.G_RET_STS_ERROR;
431 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_ID_INVALID');
432 FND_MSG_PUB.Add;
433 ELSIF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_num IS NOT NULL
434 THEN
435 p_return_status := FND_API.G_RET_STS_ERROR;
436 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_NUM_INVALID');
437 FND_MSG_PUB.Add;
438 ELSIF ar_receipt_api_pub.Original_create_cash_info.customer_bank_account_name IS NOT NULL
439 THEN
440 p_return_status := FND_API.G_RET_STS_ERROR;
441 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_NAME_INVALID');
442 FND_MSG_PUB.Add;
443 END IF;
444
445 END IF; /* bichatte payment uptake commenting ( Reverted) */
446
447 IF p_customer_site_use_id IS NOT NULL THEN
448 IF ar_receipt_api_pub.Original_create_cash_info.cust_site_use_id IS NOT NULL
449 THEN
450 p_return_status := FND_API.G_RET_STS_ERROR;
451 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_SITE_USE_ID_INVALID');
452 FND_MSG_PUB.Add;
453 ELSIF ar_receipt_api_pub.Original_create_cash_info.location IS NULL THEN
454 p_return_status := FND_API.G_RET_STS_ERROR;
455 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_LOC_INVALID');
456 FND_MSG_PUB.Add;
457 END IF;
458 END IF;
459
460 END IF;
461 IF PG_DEBUG in ('Y', 'C') THEN
462 arp_util.debug('Validate_amount: ' || 'Validate_Customer_id ()-');
463 END IF;
464 END Validate_Customer;
465
466 PROCEDURE Validate_receipt_method (p_receipt_method_id IN NUMBER,
467 p_remittance_bank_account_id IN NUMBER,
468 p_receipt_date IN DATE,
469 p_currency_code IN VARCHAR2,
470 p_state IN VARCHAR2,
471 p_called_from IN VARCHAR2,
472 p_return_status OUT NOCOPY VARCHAR2) IS
473 l_temp BINARY_INTEGER;
474 l_dummy_method Receipt_Method_Rec;
475 l_record_exists_in_cache VARCHAR2(2);
476 receipt_md_null EXCEPTION;
477 remittance_bank_valid EXCEPTION;
478 remittance_bank_invalid EXCEPTION;
479 remittance_bank_null EXCEPTION;
480 receipt_method_invalid EXCEPTION;
481 BEGIN
482
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_util.debug('Validate_amount: ' || 'Validate_Receipt_Method()+');
485 END IF;
486
487 p_return_status := FND_API.G_RET_STS_SUCCESS;
488
489 l_temp := Method_Info_Cache_Tbl.LAST;
490 IF l_temp IS NULL THEN
491 --The cache is empty : populate it directly.
492 l_record_exists_in_cache := 'N';
493
494 ELSE
495 --The records exist in the cache, compare them with the current record
496 l_record_exists_in_cache := 'N';
497 FOR l_counter IN 1..l_temp LOOP
498 IF Method_Info_Cache_Tbl(l_counter).method_id = p_receipt_method_id AND
499 Method_Info_Cache_Tbl(l_counter).bank_account_id = p_remittance_bank_account_id AND
500 Method_Info_Cache_Tbl(l_counter).currency = p_currency_code --bug12981398
501 THEN
502 --current record exists in the cache, exit out NOCOPY of the loop
503 l_record_exists_in_cache := 'Y';
504 EXIT;
505 END IF;
506 END LOOP;
507 END IF;
508 IF PG_DEBUG in ('Y', 'C') THEN
509 arp_util.debug('Validate_amount: ' || 'l_record_exists_in_cache = '||l_record_exists_in_cache);
510 END IF;
511 IF l_record_exists_in_cache = 'N' THEN
512
513 --validate the existing record against the database
514
515 /*--------------------------------+
516 | |
517 | Validate Receipt_method |
518 | and Remit bank_account_id |
519 +--------------------------------*/
520 IF p_receipt_method_id IS NOT NULL
521 THEN
522
523 --if the creation_status that was derived at the defaulting phase ,
524 -- for this receipt method , is null then the receipt method id is invalid
525 IF p_state IS NULL THEN
526 raise receipt_method_invalid;
527 ELSE
528 --this is for the case where user had entered the receipt_method
529 --and the remittance bank id, and only the p_state was defaulted by
530 --the defaulting routines.
531 --As per the defaulting routines if remittance bank account id remains
532 --null(can't be defaulted) then p_state shall also be null.
533 --
534 --We validate the receipt method and the remittance bank account id
535 --seperately to get the relevant error message:
536 /*Bug9081614*/
537 BEGIN
538 SELECT rm.receipt_method_id,
539 ba.bank_acct_use_id,
540 rc.creation_method_code,
541 rc.remit_flag,
542 cba.currency_code
543 INTO l_dummy_method.method_id,
544 l_dummy_method.bank_account_id,
545 l_dummy_method.state,
546 l_dummy_method.remit_flag,
547 l_dummy_method.currency
548 FROM ar_receipt_methods rm,
549 ce_bank_accounts cba,
550 ce_bank_acct_uses_ou ba,
551 ar_receipt_method_accounts rma,
552 ar_receipt_classes rc
553 WHERE rm.receipt_method_id = p_receipt_method_id
554 and (p_receipt_date between rm.start_date and nvl(rm.end_date, p_receipt_date))
555 and ((rc.creation_method_code = DECODE(p_called_from,'BR_REMITTED','BR_REMIT',
556 'BR_FACTORED_WITH_RECOURSE','BR_REMIT',
557 'BR_FACTORED_WITHOUT_RECOURSE','BR_REMIT','@*%?&')) or
558 (rc.creation_method_code = 'MANUAL') or
559 (rc.creation_method_code = 'NETTING') or
560 (rc.creation_method_code = 'AUTOMATIC' and
561 -- rc.remit_flag = 'Y' and
562 -- OSTEINME 2/27/2001: removed remit_flag
563 -- condition for iReceivables CC functionality.
564 -- See bug 1659109.
565 rc.confirm_flag = decode(p_called_from, 'AUTORECAPI',rc.confirm_flag,'N')))
566 and cba.account_classification = 'INTERNAL'
567 and nvl(ba.end_date, p_receipt_date +1) > p_receipt_date
568 and p_receipt_date between rma.start_date and
569 nvl(rma.end_date, p_receipt_date)
570 and cba.currency_code = decode(cba.receipt_multi_currency_flag, 'Y',
571 cba.currency_code, p_currency_code)
572 and rc.receipt_class_id = rm.receipt_class_id
573 and rm.receipt_method_id = rma.receipt_method_id
574 and rma.remit_bank_acct_use_id = ba.bank_acct_use_id
575 and ba.bank_account_id = cba.bank_account_id
576 --APANDIT: changes made for the misc receipt creation api.
577 and ((nvl(p_called_from,'*&#$') <> 'MISC')
578 or
579 (rm.receipt_class_id not in (
580 SELECT arc.receipt_class_id
581 FROM ar_receipt_classes arc
582 WHERE arc.notes_receivable='Y'
583 or arc.bill_of_exchange_flag='Y')));
584
585 --this above PL/SQL block will get successfully executed only in the case when
586 --receipt method has only one valid remittance bank account and in this case
587 --we can directly compare the remittance bank account id with the value obtained
588 --from the above query and if it is not same then the remittance bank account id
589 --is invalid
590
591 IF p_remittance_bank_account_id IS NOT NULL THEN
592 IF l_dummy_method.bank_account_id = p_remittance_bank_account_id THEN
593 IF PG_DEBUG in ('Y', 'C') THEN
594 arp_util.debug('Validate_amount: ' || 'Remittance bank account id is valid ');
595 END IF;
596
597 --Cache the valid record [Bug 6454022]
598 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
599 l_temp := nvl(l_temp,0) + 1;
600 Method_Info_Cache_Tbl(l_temp) := l_dummy_method;
601 END IF;
602
603 raise remittance_bank_valid;
604 ELSE
605 raise remittance_bank_invalid;
606 END IF;
607 --if the remittance bank account id is null then whatever has defaulted into
608 --l_dummy_method.bank_account_id is valid is the above validation sql
609 --got executed sucessfully
610 END IF;
611 EXCEPTION
612 WHEN too_many_rows THEN
613 --the receipt method is valid but has more than one remittance bank account
614 --If the remittance bank account id is null at this stage that means that neither the
615 --user had not entered one and nor could it be defaulted
616 IF PG_DEBUG in ('Y', 'C') THEN
617 arp_util.debug('Validate_amount: ' || 'Too_many_rows raised');
618 END IF;
619 null;
620 WHEN no_data_found THEN
621 IF PG_DEBUG in ('Y', 'C') THEN
622 arp_util.debug('Validate_amount: ' || 'no_data_found_raised');
623 END IF;
624 raise receipt_method_invalid;
625 --raising the exception here so that the next block which validates the
626 --remittance bank_id does not executed and the exception in trapped in the
627 --exception handler of the outer block.
628 END;
629 END IF; --p_state is null
630 ELSE
631 raise receipt_md_null;
632 END IF; --p_method_id is null
633
634 --this code would get executed only in case of the too_many_rows exception
635 --being raised in the previous block
636 IF p_remittance_bank_account_id IS NULL THEN
637 raise remittance_bank_null;
638 ELSE
639 /*Bug9081614*/
640 BEGIN
641 SELECT rm.receipt_method_id,
642 ba.bank_acct_use_id,
643 rc.creation_method_code,
644 rc.remit_flag,
645 cba.currency_code
646 INTO l_dummy_method.method_id,
647 l_dummy_method.bank_account_id,
648 l_dummy_method.state,
649 l_dummy_method.remit_flag,
650 l_dummy_method.currency
651 FROM ar_receipt_methods rm,
652 ce_bank_accounts cba,
653 ce_bank_acct_uses_ou ba,
654 ar_receipt_method_accounts rma,
655 ar_receipt_classes rc
656 WHERE rm.receipt_method_id = p_receipt_method_id
657 and rma.remit_bank_acct_use_id = p_remittance_bank_account_id
658 and (p_receipt_date between rm.start_date and nvl(rm.end_date, p_receipt_date))
659 and ((rc.creation_method_code = DECODE(p_called_from,'BR_REMITTED','BR_REMIT',
660 'BR_FACTORED_WITH_RECOURSE','BR_REMIT',
661 'BR_FACTORED_WITHOUT_RECOURSE','BR_REMIT','@*%?&')) or
662 (rc.creation_method_code = 'MANUAL') or
663 (rc.creation_method_code = 'NETTING') or
664 (rc.creation_method_code = 'AUTOMATIC' and
665 -- rc.remit_flag = 'Y' and
666 -- OSTEINME 2/27/2001: removed remit_flag
667 -- condition for iReceivables CC functionality.
668 -- See bug 1659109.
669 -- bichatte autorecapi.
670 rc.confirm_flag = decode(p_called_from, 'AUTORECAPI',rc.confirm_flag,'N')))
671 and cba.account_classification = 'INTERNAL'
672 and nvl(ba.end_date, p_receipt_date +1) > p_receipt_date
673 and p_receipt_date between rma.start_date and
674 nvl(rma.end_date, p_receipt_date)
675 and cba.currency_code = decode(cba.receipt_multi_currency_flag, 'Y',
676 cba.currency_code, p_currency_code)
677 and rc.receipt_class_id = rm.receipt_class_id
678 and rm.receipt_method_id = rma.receipt_method_id
679 and rma.remit_bank_acct_use_id = ba.bank_acct_use_id
680 and ba.bank_account_id = cba.bank_account_id
681 --APANDIT: changes made for the misc receipt creation api.
682 and ((nvl(p_called_from,'*&#$') <> 'MISC')
683 or
684 (rm.receipt_class_id not in (
685 SELECT arc.receipt_class_id
686 FROM ar_receipt_classes arc
687 WHERE arc.notes_receivable='Y'
688 OR arc.bill_of_exchange_flag='Y')));
689
690 EXCEPTION
691 WHEN no_data_found THEN
692 raise remittance_bank_invalid;
693 END;
694 END IF;
695
696 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
697 l_temp := nvl(l_temp,0) + 1;
698 Method_Info_Cache_Tbl(l_temp) := l_dummy_method;
699 END IF;
700
701 END IF;
702 IF PG_DEBUG in ('Y', 'C') THEN
703 arp_util.debug('Validate_amount: ' || 'Validate_Receipt_Method ()-');
704 END IF;
705 EXCEPTION
706 WHEN receipt_method_invalid THEN
707 IF ar_receipt_api_pub.Original_create_cash_info.receipt_method_id IS NOT NULL THEN
708 p_return_status := FND_API.G_RET_STS_ERROR;
709 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_MD_ID_INVALID');
710 FND_MSG_PUB.Add;
711 IF PG_DEBUG in ('Y', 'C') THEN
712 arp_util.debug('Validate_amount: ' || 'Invalid receipt method id ');
713 END IF;
714 ELSIF ar_receipt_api_pub.Original_create_cash_info.receipt_method_name IS NOT NULL THEN
715 p_return_status := FND_API.G_RET_STS_ERROR;
716 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCT_MD_NAME_INVALID');
717 FND_MSG_PUB.Add;
718 IF PG_DEBUG in ('Y', 'C') THEN
719 arp_util.debug('Validate_amount: ' || 'Invalid receipt method name ');
720 END IF;
721 END IF;
722
723 WHEN remittance_bank_valid THEN
724 null;
725 IF PG_DEBUG in ('Y', 'C') THEN
726 arp_util.debug('Validate_amount: ' || 'Exception: remittance_bank_valid ');
727 END IF;
728
729 WHEN remittance_bank_invalid THEN
730 IF ar_receipt_api_pub.Original_create_cash_info.remit_bank_acct_use_id IS NOT NULL THEN
731 p_return_status := FND_API.G_RET_STS_ERROR;
732 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REM_BK_AC_ID_INVALID');
733 FND_MSG_PUB.Add;
734 IF PG_DEBUG in ('Y', 'C') THEN
735 arp_util.debug('Validate_amount: ' || 'Invalid remittance bank account id');
736 END IF;
737 ELSIF ar_receipt_api_pub.Original_create_cash_info.remittance_bank_account_num IS NOT NULL THEN
738 p_return_status := FND_API.G_RET_STS_ERROR;
739 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REM_BK_AC_NUM_INVALID');
740 FND_MSG_PUB.Add;
741 IF PG_DEBUG in ('Y', 'C') THEN
742 arp_util.debug('Validate_amount: ' || 'Invalid remittance bank account number');
743 END IF;
744 ELSIF ar_receipt_api_pub.Original_create_cash_info.remittance_bank_account_name IS NOT NULL THEN
745 p_return_status := FND_API.G_RET_STS_ERROR;
746 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REM_BK_AC_NAME_INVALID');
747 FND_MSG_PUB.Add;
748 IF PG_DEBUG in ('Y', 'C') THEN
749 arp_util.debug('Validate_amount: ' || 'Invalid remittance bank account name');
750 END IF;
751 END IF;
752
753 WHEN remittance_bank_null THEN
754 p_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REM_BK_AC_ID_NULL');
756 FND_MSG_PUB.Add;
757 IF PG_DEBUG in ('Y', 'C') THEN
758 arp_util.debug('Validate_amount: ' || 'The remittance bank account could not be defaulted ');
759 END IF;
760 WHEN receipt_md_null THEN
761 p_return_status := FND_API.G_RET_STS_ERROR;
762 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_MD_ID_NULL');
763 FND_MSG_PUB.Add;
764 IF PG_DEBUG in ('Y', 'C') THEN
765 arp_util.debug('Validate_amount: ' || 'The receipt method id is null ');
766 END IF;
767 WHEN others THEN
768 IF PG_DEBUG in ('Y', 'C') THEN
769 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Validate_Receipt_Method_() ');
770 arp_util.debug('Validate_amount: ' || 'p_receipt_method_id = '
771 ||TO_CHAR(p_receipt_method_id));
772 arp_util.debug('Validate_amount: ' || 'p_remittance_bank_account_id = '
773 ||TO_CHAR(p_remittance_bank_account_id));
774 END IF;
775 RAISE;
776 END Validate_Receipt_Method;
777
778 PROCEDURE Validate_Exchange_Rate(
779 p_currency_code IN VARCHAR2,
780 p_exchange_rate_type IN VARCHAR2,
781 p_exchange_rate IN NUMBER,
782 p_exchange_rate_date IN DATE,
783 p_return_status OUT NOCOPY VARCHAR2) IS
784 l_euro_to_emu_rate NUMBER;
785 l_cross_rate NUMBER;
786 l_conversion_rate NUMBER;
787 l_exchange_rate_valid varchar2(2);
788 BEGIN
789
790 p_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 IF p_currency_code <> arp_global.functional_currency THEN
793
794 IF p_exchange_rate_type IS NULL THEN
795
796 -- raise exception
797 p_return_status := FND_API.G_RET_STS_ERROR;
798 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_NULL');
799 FND_MSG_PUB.Add;
800 Return;
801
802 ELSE
803 -- Validate the rate_type against the database values
804 -- if invalid then return
805 BEGIN
806 SELECT 'Y'
807 INTO l_exchange_rate_valid
808 FROM gl_daily_conversion_types
809 WHERE conversion_type = p_exchange_rate_type;
810 EXCEPTION
811 WHEN no_data_found THEN
812 p_return_status := FND_API.G_RET_STS_ERROR;
813 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_INVALID');
814 FND_MSG_PUB.Add;
815 Return;
816 END;
817
818 IF p_exchange_rate_type = 'User' THEN
819
820 IF p_exchange_rate IS NULL THEN
821 --raise error message, because for rate_type 'User' the rate should be specified.
822 p_return_status := FND_API.G_RET_STS_ERROR;
823 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_NULL');
824 FND_MSG_PUB.Add;
825 ELSIF p_exchange_rate = 0 THEN
826 p_return_status := FND_API.G_RET_STS_ERROR ;
827 FND_MESSAGE.SET_NAME('AR','AR_EXCHANGE_RATE_ZERO');
828 FND_MSG_PUB.Add;
829 ELSIF p_exchange_rate < 0 THEN
830 p_return_status := FND_API.G_RET_STS_ERROR ;
831 FND_MESSAGE.SET_NAME('AR','AR_EXCHANGE_RATE_NEGATIVE');
832 FND_MSG_PUB.Add;
833 END IF;
834
835 ELSE
836 --this is the case where rate_type <> 'User'
837 IF p_exchange_rate IS NULL THEN
838 --This could happen only in case if the defaulting routines
839 --could not get the exchange_rate
840 --raise an error message in that case
841
842 p_return_status := FND_API.G_RET_STS_ERROR;
843 FND_MESSAGE.SET_NAME('AR','AR_NO_RATE_DATA_FOUND');
844 FND_MSG_PUB.Add;
845 END IF;
846
847 END IF;
848
849 END IF;
850 ELSE
851 --the functional and the entered currency are same
852 --so there should be no exchange_rate information
853
854 IF (p_exchange_rate IS NOT NULL) THEN
855 p_return_status := FND_API.G_RET_STS_ERROR ;
856 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_INVALID');
857 FND_MSG_PUB.Add;
858 END IF;
859 IF (p_exchange_rate_type IS NOT NULL) THEN
860 p_return_status := FND_API.G_RET_STS_ERROR ;
861 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_TYPE_INVALID');
862 FND_MSG_PUB.Add;
863 END IF;
864 IF (p_exchange_rate_date IS NOT NULL) THEN
865 p_return_status := FND_API.G_RET_STS_ERROR ;
866 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_DATE_INVALID');
867 FND_MSG_PUB.Add;
868 END IF;
869 END IF;
870 EXCEPTION
871 WHEN others THEN
872 IF PG_DEBUG in ('Y', 'C') THEN
873 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Validate_Exchange_Rate() ');
874 arp_util.debug('Validate_amount: ' || 'p_exchange_rate_type = '
875 ||p_exchange_rate_type);
876 END IF;
877 RAISE;
878 END Validate_Exchange_Rate;
879
880 FUNCTION Is_currency_valid(p_currency_code IN
881 ar_cash_receipts.currency_code%TYPE) RETURN VARCHAR2 IS
882 l_currency_valid VARCHAR2(1);
883 BEGIN
884 SELECT 'Y'
885 INTO l_currency_valid
886 FROM fnd_currencies
887 WHERE p_currency_code = currency_code;
888
889 RETURN(l_currency_valid);
890 EXCEPTION
891 WHEN no_data_found THEN
892 l_currency_valid := 'N';
893 RETURN(l_currency_valid);
894 WHEN others THEN
895 IF PG_DEBUG in ('Y', 'C') THEN
896 arp_util.debug('Validate_amount: ' || 'EXCEPTION: Validate_Exchange_Rate() ');
897 arp_util.debug('Validate_amount: ' || 'p_currency_code = '||p_currency_code);
898 END IF;
899 raise;
900 END Is_currency_valid;
901
902 PROCEDURE Validate_Currency(
903 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
904 p_exchange_rate_type IN ar_cash_receipts.exchange_rate_type%TYPE,
905 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
906 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE,
907 p_return_status OUT NOCOPY VARCHAR2) IS
908 BEGIN
909 p_return_status := FND_API.G_RET_STS_SUCCESS;
910 IF (Is_currency_valid(p_currency_code) = 'Y') THEN
911
912 IF ((arp_global.functional_currency <> p_currency_code) OR
913 (p_exchange_rate_type IS NOT NULL OR
914 p_exchange_rate IS NOT NULL OR
915 p_exchange_rate_date IS NOT NULL)) THEN
916
917 Validate_Exchange_Rate(p_currency_code,
918 p_exchange_rate_type,
919 p_exchange_rate,
920 p_exchange_rate_date,
921 p_return_status);
922 END IF;
923 ELSE
924 --the entered currency is invalid
925 p_return_status := FND_API.G_RET_STS_ERROR;
926 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
927 FND_MSG_PUB.Add;
928 END IF;
929 END Validate_Currency;
930
931 PROCEDURE val_duplicate_receipt(p_receipt_number IN VARCHAR2,
932 p_receipt_date IN DATE,
933 p_amount IN NUMBER,
934 p_type IN VARCHAR2,
935 p_customer_id IN NUMBER,
936 p_return_status OUT NOCOPY VARCHAR2) IS
937 l_duplicate_receipt varchar2(1) := 'N';
938 CURSOR validate_duplicate_receipt IS
939 SELECT 'Y'
940 FROM ar_cash_receipts cr
941 WHERE cr.receipt_number = p_receipt_number
942 AND cr.receipt_date = p_receipt_date
943 AND cr.amount = p_amount
944 AND NVL(cr.pay_from_customer, -99999) = NVL(p_customer_id, -99999)
945 AND cr.type = p_type
946 AND cr.status NOT IN (
947 SELECT arl.lookup_code FROM ar_lookups arl
948 WHERE arl.lookup_type = 'REVERSAL_CATEGORY_TYPE');
949
950 BEGIN
951 IF PG_DEBUG in ('Y', 'C') THEN
952 arp_util.debug('Validate_amount: ' || 'val_duplicate_receipt ()+');
953 END IF;
954 BEGIN
955
956 OPEN validate_duplicate_receipt;
957 FETCH validate_duplicate_receipt INTO l_duplicate_receipt;
958 CLOSE validate_duplicate_receipt;
959
960 EXCEPTION
961 WHEN NO_DATA_FOUND THEN
962 null;
963 WHEN TOO_MANY_ROWS THEN
964 l_duplicate_receipt := 'Y';
965 END;
966
967 IF PG_DEBUG in ('Y', 'C') THEN
968 arp_util.debug('Validate_amount: ' || 'l_duplicate_receipt :'||l_duplicate_receipt);
969 END IF;
970 -- Do not allow to create duplicate receipts
971 IF l_duplicate_receipt = 'Y' THEN
972 IF p_type = 'CASH' THEN
973 FND_MESSAGE.SET_NAME('AR','AR_RW_CASH_DUPLICATE_RECEIPT');
974 FND_MSG_PUB.ADD;
975 p_return_status := FND_API.G_RET_STS_ERROR;
976 ELSIF p_type = 'MISC' THEN
977 FND_MESSAGE.SET_NAME('AR','AR_RW_MISC_DUPLICATE_RECEIPT');
978 FND_MSG_PUB.ADD;
979 p_return_status := FND_API.G_RET_STS_ERROR;
980 END IF;
981
982 END IF;
983 IF PG_DEBUG in ('Y', 'C') THEN
984 arp_util.debug('Validate_amount: ' || 'val_duplicate_receipt ()-');
985 END IF;
986 END val_duplicate_receipt;
987
988 PROCEDURE Validate_Cash_Receipt(
989 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE,
990 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
991 p_state IN ar_receipt_classes.creation_status%TYPE,
992 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
993 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
994 p_maturity_date IN DATE,
995 p_deposit_date IN ar_cash_receipts.deposit_date%TYPE,
996 p_amount IN OUT NOCOPY ar_cash_receipts.amount%TYPE,
997 p_factor_discount_amount IN ar_cash_receipts.factor_discount_amount%TYPE,
998 p_customer_id IN ar_cash_receipts.pay_from_customer%TYPE,
999 /* 6612301 */
1000 p_customer_bank_account_id IN OUT NOCOPY ar_cash_receipts.customer_bank_account_id%TYPE,
1001 p_location IN hz_cust_site_uses.location%TYPE,
1002 p_customer_site_use_id IN OUT NOCOPY ar_cash_receipts.customer_site_use_id%TYPE,
1003 p_remittance_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%TYPE,
1004 p_override_remit_account_flag IN ar_cash_receipts.override_remit_account_flag%TYPE,
1005 p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE,
1006 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
1007 p_exchange_rate_type IN ar_cash_receipts.exchange_rate_type%TYPE,
1008 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
1009 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE,
1010 p_doc_sequence_value IN NUMBER,
1011 p_called_from IN VARCHAR2,
1012 p_return_status OUT NOCOPY VARCHAR2)
1013 IS
1014 l_receipt_date_return_status VARCHAR2(1);
1015 l_gl_date_return_status VARCHAR2(1);
1016 l_deposit_date_return_status VARCHAR2(1);
1017 l_maturity_date_return_status VARCHAR2(1);
1018 l_rcpt_md_return_status VARCHAR2(1);
1019 l_amount_return_status VARCHAR2(1);
1020 l_customer_return_status VARCHAR2(1);
1021 l_override_remit_return_status VARCHAR2(1);
1022 l_currency_return_status VARCHAR2(1);
1023 l_doc_seq_return_status VARCHAR2(1);
1024 l_dup_return_status VARCHAR2(1);
1025 BEGIN
1026 IF PG_DEBUG in ('Y', 'C') THEN
1027 arp_util.debug('Validate_amount: ' || 'Validate_Receipt()+ ');
1028 END IF;
1029
1030 p_return_status := FND_API.G_RET_STS_SUCCESS;
1031
1032 --Validate receipt_date
1033
1034 Validate_Receipt_Date(p_receipt_date,
1035 l_receipt_date_return_status);
1036 IF PG_DEBUG in ('Y', 'C') THEN
1037 arp_util.debug('Validate_amount: ' || 'l_receipt_date_return_status : '||l_receipt_date_return_status);
1038 END IF;
1039
1040 --Validate gl_date
1041
1042 Validate_Gl_Date(p_gl_date,
1043 l_gl_date_return_status);
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 arp_util.debug('Validate_amount: ' || 'l_gl_date_return_status : '||l_gl_date_return_status);
1046 END IF;
1047 --Validate deposit_date
1048
1049 Validate_Deposit_Date(p_deposit_date,
1050 l_deposit_date_return_status);
1051 IF PG_DEBUG in ('Y', 'C') THEN
1052 arp_util.debug('Validate_amount: ' || 'l_deposit_date_return_status : '||l_deposit_date_return_status);
1053 END IF;
1054
1055 --Validate maturity_date
1056 /* bug 16386733: drop this maturity date validation
1057 Validate_Maturity_Date(p_maturity_date,
1058 p_receipt_date,
1059 l_maturity_date_return_status);
1060 IF PG_DEBUG in ('Y', 'C') THEN
1061 arp_util.debug('Validate_amount: ' || 'l_maturity_date_return_status : '||l_maturity_date_return_status);
1062 END IF;
1063 */
1064
1065 --Validate Receipt_method
1066 Validate_Receipt_Method(p_receipt_method_id,
1067 p_remittance_bank_account_id,
1068 p_receipt_date,
1069 p_currency_code,
1070 p_state,
1071 p_called_from,
1072 l_rcpt_md_return_status);
1073 IF PG_DEBUG in ('Y', 'C') THEN
1074 arp_util.debug('Validate_amount: ' || 'l_rcpt_md_return_status : '||l_rcpt_md_return_status);
1075 END IF;
1076
1077 --Validate document sequence value
1078
1079 IF(NVL(ar_receipt_lib_pvt.pg_profile_doc_seq, 'N') = 'N' ) AND
1080 p_doc_sequence_value IS NOT NULL
1081 THEN
1082 l_doc_seq_return_status := FND_API.G_RET_STS_ERROR ;
1083 FND_MESSAGE.SET_NAME('AR','AR_RAPI_DOC_SEQ_VAL_INVALID');
1084 FND_MSG_PUB.Add;
1085 END IF;
1086 --Validate amount
1087 Validate_amount(p_amount ,
1088 p_factor_discount_amount,
1089 p_state,
1090 'CASH',
1091 l_amount_return_status);
1092 IF PG_DEBUG in ('Y', 'C') THEN
1093 arp_util.debug('Validate_amount: ' || 'l_amount_return_status : '||l_amount_return_status);
1094 END IF;
1095
1096 --Validate Customer info
1097
1098 Validate_Customer(p_customer_id,
1099 /* 6612301 */
1100 p_customer_bank_account_id,
1101 p_location,
1102 p_customer_site_use_id,
1103 p_currency_code,
1104 p_receipt_date,
1105 l_customer_return_status);
1106 IF PG_DEBUG in ('Y', 'C') THEN
1107 arp_util.debug('Validate_amount: ' || 'l_customer_return_status : '||l_customer_return_status);
1108 END IF;
1109
1110 --Validate the override_remit_bank_account_flag
1111 IF (p_override_remit_account_flag NOT IN ('Y','N')) THEN
1112 FND_MESSAGE.SET_NAME('AR','AR_OVERR_REM_BK_FLAG_INVALID');
1113 FND_MSG_PUB.ADD;
1114 l_override_remit_return_status := FND_API.G_RET_STS_ERROR;
1115 ELSE
1116 l_override_remit_return_status := FND_API.G_RET_STS_SUCCESS;
1117 END IF;
1118 IF PG_DEBUG in ('Y', 'C') THEN
1119 arp_util.debug('Validate_amount: ' || 'l_override_remit_return_status : '||l_override_remit_return_status);
1120 END IF;
1121
1122 --Validate currency and exchange rate info.
1123 IF p_currency_code <> arp_global.functional_currency OR
1124 p_exchange_rate_type IS NOT NULL OR
1125 p_exchange_rate IS NOT NULL OR
1126 p_exchange_rate_date IS NOT NULL THEN
1127 Validate_currency(p_currency_code,
1128 p_exchange_rate_type,
1129 p_exchange_rate,
1130 p_exchange_rate_date,
1131 l_currency_return_status);
1132 END IF;
1133 IF PG_DEBUG in ('Y', 'C') THEN
1134 arp_util.debug('Validate_amount: ' || 'l_currency_return_status : '||l_currency_return_status);
1135 END IF;
1136
1137 IF p_receipt_number IS NOT NULL AND
1138 p_amount IS NOT NULL THEN
1139 val_duplicate_receipt(p_receipt_number ,
1140 p_receipt_date ,
1141 p_amount ,
1142 'CASH' ,
1143 p_customer_id ,
1144 l_dup_return_status );
1145 END IF;
1146
1147 IF (l_receipt_date_return_status = FND_API.G_RET_STS_ERROR) OR
1148 (l_gl_date_return_status = FND_API.G_RET_STS_ERROR) OR
1149 (l_deposit_date_return_status = FND_API.G_RET_STS_ERROR) OR
1150 (l_maturity_date_return_status = FND_API.G_RET_STS_ERROR) OR
1151 (l_rcpt_md_return_status = FND_API.G_RET_STS_ERROR) OR
1152 (l_amount_return_status = FND_API.G_RET_STS_ERROR) OR
1153 (l_customer_return_status = FND_API.G_RET_STS_ERROR) OR
1154 (l_override_remit_return_status = FND_API.G_RET_STS_ERROR) OR
1155 (l_currency_return_status = FND_API.G_RET_STS_ERROR) OR
1156 (l_doc_seq_return_status = FND_API.G_RET_STS_ERROR) OR
1157 (l_dup_return_status = FND_API.G_RET_STS_ERROR)
1158 THEN
1159 p_return_status := FND_API.G_RET_STS_ERROR;
1160 END IF;
1161 IF PG_DEBUG in ('Y', 'C') THEN
1162 arp_util.debug('Validate_amount: ' || 'Validate_Cash_Receipt Return status :'||p_return_status);
1163 END IF;
1164
1165 EXCEPTION
1166 WHEN others THEN
1167 raise;
1168
1169 END Validate_Cash_Receipt;
1170
1171
1172 PROCEDURE Validate_amount_applied(
1173 p_amount_applied IN NUMBER,
1174 p_applied_payment_schedule_id IN NUMBER,
1175 p_customer_trx_line_id IN NUMBER,
1176 p_inv_line_amount IN NUMBER,
1177 p_creation_sign IN VARCHAR2,
1178 p_allow_overappln_flag IN VARCHAR2,
1179 p_natural_appln_only_flag IN VARCHAR2,
1180 p_discount IN NUMBER,
1181 p_amount_due_remaining IN NUMBER,
1182 p_amount_due_original IN NUMBER,
1183 p_return_status OUT NOCOPY VARCHAR2
1184 ) IS
1185 l_message_name VARCHAR2(50);
1186 BEGIN
1187 IF PG_DEBUG in ('Y', 'C') THEN
1188 arp_util.debug('Validate_amount_applied ()+');
1189 END IF;
1190 p_return_status := FND_API.G_RET_STS_SUCCESS;
1191
1192
1193 IF p_amount_applied IS NULL
1194 THEN
1195 p_return_status := FND_API.G_RET_STS_ERROR;
1196 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
1197 FND_MSG_PUB.Add;
1198 return;
1199 /* The amount Applied can be greater than the line amount. The line level application
1200 is not supported yet: Bug 3476306 */
1201 /*ELSE
1202 IF (p_customer_trx_line_id IS NOT NULL) AND
1203 (nvl(p_inv_line_amount,0) < p_amount_applied)
1204 --in case of line_number being not null and
1205 --inv_line_amount being null error message
1206 --would have been raised in the validate_line_number
1207 THEN
1208 p_return_status := FND_API.G_RET_STS_ERROR;
1209 FND_MESSAGE.SET_NAME('AR','AR_RW_APPLIED_GREATER_LINE');
1210 FND_MESSAGE.SET_TOKEN('AMOUNT',p_inv_line_amount);
1211 FND_MSG_PUB.Add;
1212 END IF; */
1213 END IF;
1214 IF PG_DEBUG in ('Y', 'C') THEN
1215 arp_util.debug('Validate_amount: ' || 'Before calling the check_natural_application routine ');
1216 END IF;
1217 IF p_applied_payment_schedule_id > 0 THEN
1218
1219 arp_non_db_pkg.check_natural_application(
1220 p_creation_sign => p_creation_sign
1221 , p_allow_overapplication_flag=> p_allow_overappln_flag
1222 , p_natural_app_only_flag => p_natural_appln_only_flag
1223 , p_sign_of_ps => '-'
1224 , p_chk_overapp_if_zero => 'N'
1225 , p_payment_amount => p_amount_applied
1226 , p_discount_taken => p_discount
1227 , p_amount_due_remaining => p_amount_due_remaining
1228 , p_amount_due_original => p_amount_due_original
1229 , event => 'WHEN-VALIDATE-ITEM'
1230 , p_message_name => l_message_name);
1231
1232 IF ( l_message_name IS NOT NULL)
1233 THEN
1234 p_return_status := FND_API.G_RET_STS_ERROR;
1235 FND_MESSAGE.SET_NAME('AR',l_message_name);
1236 FND_MSG_PUB.Add;
1237 END IF;
1238
1239 END IF;
1240
1241 IF PG_DEBUG in ('Y', 'C') THEN
1242 arp_util.debug('Validate_amount_applied ()-');
1243 END IF;
1244 END Validate_amount_applied;
1245
1246 --- LLCA
1247 PROCEDURE Validate_line_applied(
1248 p_line_amount IN NUMBER,
1249 p_applied_payment_schedule_id IN NUMBER,
1250 p_customer_trx_line_id IN NUMBER,
1251 p_inv_line_amount IN NUMBER,
1252 p_creation_sign IN VARCHAR2,
1253 p_allow_overappln_flag IN VARCHAR2,
1254 p_natural_appln_only_flag IN VARCHAR2,
1255 p_llca_type IN VARCHAR2,
1256 p_discount IN NUMBER,
1257 p_line_items_remaining IN NUMBER,
1258 p_line_items_original IN NUMBER,
1259 p_return_status OUT NOCOPY VARCHAR2
1260 ) IS
1261 l_line_message_name VARCHAR2(50);
1262 BEGIN
1263 IF PG_DEBUG in ('Y', 'C') THEN
1264 arp_util.debug('Validate_line_applied ()+');
1265 END IF;
1266 p_return_status := FND_API.G_RET_STS_SUCCESS;
1267
1268 IF p_line_amount IS NULL THEN
1269 return;
1270 END IF;
1271
1272 IF PG_DEBUG in ('Y', 'C') THEN
1273 arp_util.debug('Validate_amount: ' || 'Before calling the check_natural_application routine ');
1274 END IF;
1275
1276 IF p_applied_payment_schedule_id > 0 THEN
1277
1278 arp_non_db_pkg.check_natural_application(
1279 p_creation_sign => p_creation_sign
1280 , p_allow_overapplication_flag=> p_allow_overappln_flag
1281 , p_natural_app_only_flag => p_natural_appln_only_flag
1282 , p_sign_of_ps => '-'
1283 , p_chk_overapp_if_zero => 'N'
1284 , p_payment_amount => p_line_amount
1285 , p_discount_taken => p_discount
1286 , p_amount_due_remaining => p_line_items_remaining
1287 , p_amount_due_original => p_line_items_original
1288 , event => 'WHEN-VALIDATE-ITEM'
1289 , p_message_name => l_line_message_name);
1290
1291 IF ( l_line_message_name IS NOT NULL)
1292 THEN
1293 p_return_status := FND_API.G_RET_STS_ERROR;
1294 FND_MESSAGE.SET_NAME('AR',l_line_message_name);
1295 FND_MSG_PUB.Add;
1296 END IF;
1297
1298 END IF;
1299
1300 IF PG_DEBUG in ('Y', 'C') THEN
1301 arp_util.debug('Validate_line_applied ()-');
1302 END IF;
1303 END Validate_line_applied;
1304 --- LLCA
1305 PROCEDURE Validate_tax_applied(
1306 p_tax_amount IN NUMBER,
1307 p_applied_payment_schedule_id IN NUMBER,
1308 p_creation_sign IN VARCHAR2,
1309 p_allow_overappln_flag IN VARCHAR2,
1310 p_natural_appln_only_flag IN VARCHAR2,
1311 p_llca_type IN VARCHAR2,
1312 p_discount IN NUMBER,
1313 p_tax_remaining IN NUMBER,
1314 p_tax_original IN NUMBER,
1315 p_return_status OUT NOCOPY VARCHAR2
1316 ) IS
1317 l_tax_message_name VARCHAR2(50);
1318 BEGIN
1319 IF PG_DEBUG in ('Y', 'C') THEN
1320 arp_util.debug('Validate_tax_applied ()+');
1321 END IF;
1322 p_return_status := FND_API.G_RET_STS_SUCCESS;
1323
1324 IF p_tax_amount IS NULL THEN
1325 return;
1326 END IF;
1327
1328 IF PG_DEBUG in ('Y', 'C') THEN
1329 arp_util.debug('Validate_amount: ' || 'Before calling the check_natural_application routine ');
1330 END IF;
1331
1332 IF p_applied_payment_schedule_id > 0 THEN
1333
1334 arp_non_db_pkg.check_natural_application(
1335 p_creation_sign => p_creation_sign
1336 , p_allow_overapplication_flag=> p_allow_overappln_flag
1337 , p_natural_app_only_flag => p_natural_appln_only_flag
1338 , p_sign_of_ps => '-'
1339 , p_chk_overapp_if_zero => 'N'
1340 , p_payment_amount => p_tax_amount
1341 , p_discount_taken => p_discount
1342 , p_amount_due_remaining => p_tax_remaining
1343 , p_amount_due_original => p_tax_original
1344 , event => 'WHEN-VALIDATE-ITEM'
1345 , p_message_name => l_tax_message_name);
1346
1347 IF ( l_tax_message_name IS NOT NULL)
1348 THEN
1349 p_return_status := FND_API.G_RET_STS_ERROR;
1350 FND_MESSAGE.SET_NAME('AR',l_tax_message_name);
1351 FND_MSG_PUB.Add;
1352 END IF;
1353
1354 END IF;
1355
1356 IF PG_DEBUG in ('Y', 'C') THEN
1357 arp_util.debug('Validate_tax_applied ()-');
1358 END IF;
1359 END Validate_tax_applied;
1360
1361 --- LLCA
1362 PROCEDURE Validate_freight_applied(
1363 p_freight_amount IN NUMBER,
1364 p_applied_payment_schedule_id IN NUMBER,
1365 p_customer_trx_line_id IN NUMBER,
1366 p_inv_line_amount IN NUMBER,
1367 p_creation_sign IN VARCHAR2,
1368 p_allow_overappln_flag IN VARCHAR2,
1369 p_natural_appln_only_flag IN VARCHAR2,
1370 p_llca_type IN VARCHAR2,
1371 p_discount IN NUMBER,
1372 p_freight_remaining IN NUMBER,
1373 p_freight_original IN NUMBER,
1374 p_return_status OUT NOCOPY VARCHAR2
1375 ) IS
1376 l_frt_message_name VARCHAR2(50);
1377 BEGIN
1378 IF PG_DEBUG in ('Y', 'C') THEN
1379 arp_util.debug('Validate_freight_applied ()+');
1380 END IF;
1381 p_return_status := FND_API.G_RET_STS_SUCCESS;
1382
1383 IF p_freight_amount IS NULL THEN
1384 return;
1385 END IF;
1386
1387 IF PG_DEBUG in ('Y', 'C') THEN
1388 arp_util.debug('Validate_amount: ' || 'Before calling the check_natural_application routine ');
1389 END IF;
1390
1391 IF p_applied_payment_schedule_id > 0 THEN
1392
1393 arp_non_db_pkg.check_natural_application(
1394 p_creation_sign => p_creation_sign
1395 , p_allow_overapplication_flag=> p_allow_overappln_flag
1396 , p_natural_app_only_flag => p_natural_appln_only_flag
1397 , p_sign_of_ps => '-'
1398 , p_chk_overapp_if_zero => 'N'
1399 , p_payment_amount => p_freight_amount
1400 , p_discount_taken => p_discount
1401 , p_amount_due_remaining => p_freight_remaining
1402 , p_amount_due_original => p_freight_original
1403 , event => 'WHEN-VALIDATE-ITEM'
1404 , p_message_name => l_frt_message_name);
1405
1406 IF ( l_frt_message_name IS NOT NULL)
1407 THEN
1408 p_return_status := FND_API.G_RET_STS_ERROR;
1409 FND_MESSAGE.SET_NAME('AR',l_frt_message_name);
1410 FND_MSG_PUB.Add;
1411 END IF;
1412
1413 END IF;
1414
1415 IF PG_DEBUG in ('Y', 'C') THEN
1416 arp_util.debug('Validate_freight_applied ()-');
1417 END IF;
1418 END Validate_freight_applied;
1419
1420
1421 --- LLCA
1422 PROCEDURE Validate_charges_applied(
1423 p_charges_amount IN NUMBER,
1424 p_applied_payment_schedule_id IN NUMBER,
1425 p_customer_trx_line_id IN NUMBER,
1426 p_inv_line_amount IN NUMBER,
1427 p_creation_sign IN VARCHAR2,
1428 p_allow_overappln_flag IN VARCHAR2,
1429 p_natural_appln_only_flag IN VARCHAR2,
1430 p_llca_type IN VARCHAR2,
1431 p_discount IN NUMBER,
1432 p_rec_charges_remaining IN NUMBER,
1433 p_rec_charges_charged IN NUMBER,
1434 p_return_status OUT NOCOPY VARCHAR2
1435 ) IS
1436 l_message_name VARCHAR2(50);
1437 BEGIN
1438 IF PG_DEBUG in ('Y', 'C') THEN
1439 arp_util.debug('Validate_charges_applied ()+');
1440 END IF;
1441 p_return_status := FND_API.G_RET_STS_SUCCESS;
1442
1443 IF p_charges_amount IS NULL THEN
1444 return;
1445 END IF;
1446
1447 IF PG_DEBUG in ('Y', 'C') THEN
1448 arp_util.debug('Validate_amount: ' || 'Before calling the check_natural_application routine ');
1449 END IF;
1450
1451 IF p_applied_payment_schedule_id > 0 THEN
1452
1453 arp_non_db_pkg.check_natural_application(
1454 p_creation_sign => p_creation_sign
1455 , p_allow_overapplication_flag=> p_allow_overappln_flag
1456 , p_natural_app_only_flag => p_natural_appln_only_flag
1457 , p_sign_of_ps => '-'
1458 , p_chk_overapp_if_zero => 'N'
1459 , p_payment_amount => p_charges_amount
1460 , p_discount_taken => p_discount
1461 , p_amount_due_remaining => p_rec_charges_remaining
1462 , p_amount_due_original => p_rec_charges_charged
1463 , event => 'WHEN-VALIDATE-ITEM'
1464 , p_message_name => l_message_name);
1465
1466 IF ( l_message_name IS NOT NULL)
1467 THEN
1468 p_return_status := FND_API.G_RET_STS_ERROR;
1469 FND_MESSAGE.SET_NAME('AR',l_message_name);
1470 FND_MSG_PUB.Add;
1471 END IF;
1472
1473 END IF;
1474
1475 IF PG_DEBUG in ('Y', 'C') THEN
1476 arp_util.debug('Validate_charges_applied ()-');
1477 END IF;
1478 END Validate_charges_applied;
1479
1480 PROCEDURE Validate_amount_applied_from(
1481 p_amount_applied_from IN NUMBER,
1482 p_amount_applied IN NUMBER,
1483 p_cr_unapp_amount IN NUMBER,
1484 p_cr_currency_code IN VARCHAR2,
1485 p_trx_currency_code IN VARCHAR2,
1486 p_return_status OUT NOCOPY VARCHAR2
1487 ) IS
1488 l_remaining_unapp_rct_amt NUMBER;
1489
1490 BEGIN
1491 IF PG_DEBUG in ('Y', 'C') THEN
1492 arp_util.debug('Validate_amount_applied_from ()+');
1493 END IF;
1494 p_return_status := FND_API.G_RET_STS_SUCCESS;
1495 /* Bugfix 2634721. Modified the NVL clause */
1496 l_remaining_unapp_rct_amt := p_cr_unapp_amount - nvl(p_amount_applied_from, p_amount_applied);
1497
1498 IF l_remaining_unapp_rct_amt < 0 THEN
1499 IF PG_DEBUG in ('Y', 'C') THEN
1500 arp_util.debug('Validate_amount: ' || 'l_remaining_unapp_rct_amt :'||to_char(l_remaining_unapp_rct_amt));
1501 END IF;
1502 p_return_status := FND_API.G_RET_STS_ERROR;
1503 FND_MESSAGE.SET_NAME('AR','AR_RW_APP_NEG_UNAPP');
1504 FND_MSG_PUB.Add;
1505 END IF;
1506
1507 IF p_cr_currency_code = p_trx_currency_code AND
1508 p_amount_applied_from IS NOT NULL
1509 THEN
1510 FND_MESSAGE.SET_NAME('AR','AR_RAPI_AMT_APP_FROM_INVALID');
1511 FND_MSG_PUB.Add;
1512 p_return_status := FND_API.G_RET_STS_ERROR;
1513 END IF;
1514 IF PG_DEBUG in ('Y', 'C') THEN
1515 arp_util.debug('Validate_amount_applied_from ()-');
1516 END IF;
1517 END Validate_amount_applied_from;
1518
1519 /* Added this over loaded procedure for bug 3119391 */
1520 PROCEDURE Validate_amount_applied_from(
1521 p_receivable_application_id IN NUMBER,
1522 p_cr_unapp_amount IN NUMBER,
1523 p_return_status OUT NOCOPY VARCHAR2
1524 ) IS
1525 l_amount_applied NUMBER;
1526 l_amount_applied_from NUMBER;
1527 l_remaining_unapp_rct_amt NUMBER;
1528 BEGIN
1529 IF PG_DEBUG in ('Y', 'C') THEN
1530 arp_util.debug('Validate_amount_applied_from over loaded()+');
1531 END IF;
1532 p_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 SELECT amount_applied,
1535 amount_applied_from INTO l_amount_applied,l_amount_applied_from
1536 FROM ar_receivable_applications
1537 WHERE receivable_application_id = p_receivable_application_id;
1538
1539 l_remaining_unapp_rct_amt := p_cr_unapp_amount + nvl(l_amount_applied_from, l_amount_applied);
1540
1541 IF l_remaining_unapp_rct_amt < 0 THEN
1542 IF PG_DEBUG in ('Y', 'C') THEN
1543 arp_util.debug('Validate_amount_applied_from: ' || 'l_remaining_unapp_rct_amt :'||to_char(l_remaining_unapp_rct_amt));
1544 END IF;
1545 p_return_status := FND_API.G_RET_STS_ERROR;
1546 FND_MESSAGE.SET_NAME('AR','AR_RW_AMOUNT_LESS_THAN_APP');
1547 FND_MSG_PUB.Add;
1548 END IF;
1549 IF PG_DEBUG in ('Y', 'C') THEN
1550 arp_util.debug('Validate_amount_applied_from over loaded()-');
1551 END IF;
1552 END Validate_amount_applied_from;
1553
1554 PROCEDURE Validate_trans_to_receipt_rate(
1555 p_trans_to_receipt_rate IN NUMBER,
1556 p_cr_currency_code IN VARCHAR2,
1557 p_trx_currency_code IN VARCHAR2,
1558 p_amount_applied IN NUMBER,
1559 p_amount_applied_from IN NUMBER,
1560 p_return_status OUT NOCOPY VARCHAR2
1561 ) IS
1562 l_amount_applied_cr NUMBER; --amount_applied in receipt currency
1563 l_amount_applied_from NUMBER;
1564 l_amount_applied NUMBER;
1565 BEGIN
1566 IF PG_DEBUG in ('Y', 'C') THEN
1567 arp_util.debug('Validate_trans_to_receipt_rate ()+');
1568 END IF;
1569 p_return_status := FND_API.G_RET_STS_SUCCESS;
1570 --Validate the trans_to_receipt_rate
1571 IF p_trx_currency_code = p_cr_currency_code AND
1572 p_trans_to_receipt_rate IS NOT NULL
1573 THEN
1574 --raise error because this is not a cross-currency application
1575 --and the user should not have specified a value for trans_to_receipt_rate
1576
1577 p_return_status := FND_API.G_RET_STS_ERROR;
1578 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CC_RATE_INVALID');
1579 FND_MSG_PUB.Add;
1580 ELSIF p_trx_currency_code <> p_cr_currency_code THEN
1581 IF p_trans_to_receipt_rate IS NULL THEN
1582
1583 p_return_status := FND_API.G_RET_STS_ERROR;
1584 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CC_RATE_NULL');
1585 FND_MSG_PUB.Add;
1586 ELSIF p_trans_to_receipt_rate < 0 THEN
1587
1588 p_return_status := FND_API.G_RET_STS_ERROR;
1589 FND_MESSAGE.SET_NAME('AR','AR_RW_CC_RATE_POSITIVE');
1590 FND_MSG_PUB.Add;
1591
1592 ELSE
1593
1594 --Validate the trans_to_receipt_rate with reference to
1595 --amount_applied_from and amount_applied as we should
1596 --always maintain the following relationship between the
1597 --three :
1598 --amount_applied * trans_to_receipt_rate
1599 -- = amount_applied_from.
1600 --this is to be done only if the user had passed in the values
1601 --for both amount_applied_from and trans_to_receipt_rate
1602 IF p_amount_applied IS NOT NULL AND
1603 ar_receipt_api_pub.Original_application_info.amount_applied_from IS NOT NULL AND
1604 ar_receipt_api_pub.Original_application_info.trans_to_receipt_rate IS NOT NULL
1605 THEN
1606 l_amount_applied := arp_util.CurrRound(
1607 p_amount_applied,
1608 p_trx_currency_code
1609 );
1610 l_amount_applied_cr := arp_util.CurrRound(
1611 l_amount_applied*p_trans_to_receipt_rate,
1612 p_cr_currency_code
1613 );
1614 l_amount_applied_from := arp_util.CurrRound(
1615 p_amount_applied_from,
1616 p_cr_currency_code
1617 );
1618
1619 IF l_amount_applied_cr <> l_amount_applied_from THEN
1620 p_return_status := FND_API.G_RET_STS_ERROR;
1621 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CC_RATE_AMTS_INVALID');
1622 FND_MSG_PUB.Add;
1623 END IF;
1624 END IF;
1625 END IF;
1626 END IF;
1627 IF PG_DEBUG in ('Y', 'C') THEN
1628 arp_util.debug('Validate_trans_to_receipt_rate ()-');
1629 END IF;
1630 EXCEPTION
1631 WHEN others THEN
1632 IF PG_DEBUG in ('Y', 'C') THEN
1633 arp_util.debug('Validate_trans_to_receipt_rate: ' || 'EXCEPTION: Validate_Exchange_Rate() ');
1634 END IF;
1635 raise;
1636 END Validate_trans_to_receipt_rate;
1637
1638 PROCEDURE Validate_discount(p_discount IN NUMBER,
1639 p_amount_due_remaining IN NUMBER,
1640 p_amount_due_original IN NUMBER,
1641 p_amount_applied IN NUMBER,
1642 p_partial_discount_flag IN VARCHAR2,
1643 p_applied_payment_schedule_id IN NUMBER,
1644 p_discount_earned_allowed IN NUMBER,
1645 p_discount_max_allowed IN NUMBER,
1646 p_trx_currency_code IN VARCHAR2,
1647 p_return_status OUT NOCOPY VARCHAR2
1648 ) IS
1649 BEGIN
1650 IF PG_DEBUG in ('Y', 'C') THEN
1651 arp_util.debug('Validate_discount ()+');
1652 END IF;
1653 p_return_status := FND_API.G_RET_STS_SUCCESS;
1654 -- Do not allow discount for "On Account" applications
1655 IF (p_discount <> 0
1656 AND p_applied_payment_schedule_id = -1)
1657 THEN
1658 p_return_status := FND_API.G_RET_STS_ERROR;
1659 fnd_message.set_name ('AR','AR_RW_VAL_ONACC_DISC');
1660 fnd_msg_pub.Add;
1661
1662 -- Do not negative discounts unless the amount applied is also
1663 -- negative.
1664 ELSIF p_amount_applied >= 0 and
1665 p_discount < 0 THEN
1666 p_return_status := FND_API.G_RET_STS_ERROR;
1667 fnd_message.set_name ('AR','AR_RW_VAL_NEG_DISCNT');
1668 fnd_msg_pub.Add;
1669 -- Bug 3527600: Do not allow positive discounts unless the amount
1670 -- applied is also positive.
1671 ELSIF p_amount_applied < 0 and
1672 p_discount > 0 THEN
1673 fnd_message.set_name ('AR','AR_RW_VAL_POS_DISCNT');
1674 fnd_msg_pub.Add;
1675 p_return_status := FND_API.G_RET_STS_ERROR;
1676
1677 -- If partial payment discounts are not allowed.
1678
1679 -- OSTEINME 3/9/01: Bug 1680623: partial discount
1680 -- should be allowed if payment amount + discount equal
1681 -- original balance (since it's not a partial discount!)
1682 -- Added p_discount to the 2nd AND clause.
1683 -- Bug 3527600: Allow for negative discount
1684
1685 ELSIF p_partial_discount_flag = 'N'
1686 AND p_discount <> 0
1687 -- AND (p_amount_due_original - (p_amount_applied+p_discount)) > 0
1688 -- Fixed the inconsistency between UI/Receipt API, Bug # 3072421
1689 -- Bug 3527600: Allow for negative discount
1690 -- Bug 3845905: Allow for overapplications
1691 AND ( (p_amount_applied >= 0 AND
1692 (p_amount_due_remaining - (p_amount_applied + p_discount)) > 0)
1693 OR (p_amount_applied < 0 AND
1694 (p_amount_due_remaining - (p_amount_applied + p_discount)) < 0))
1695 THEN
1696 p_return_status := FND_API.G_RET_STS_ERROR;
1697 fnd_message.set_name ('AR','AR_NO_PARTIAL_DISC');
1698 fnd_msg_pub.Add;
1699
1700 ELSIF p_discount IS NOT NULL THEN
1701
1702 --Do not give discounts more than allowed.
1703 -- Bug 3527600: Allow for negative discount
1704 IF (ABS(p_discount) > ABS(p_discount_max_allowed))
1705 THEN
1706 fnd_message.set_name ('AR','AR_RW_VAL_DISCOUNT');
1707 fnd_message.set_token ('DISC_AVAILABLE'
1708 ,TO_CHAR(p_discount_max_allowed,
1709 fnd_currency.get_format_mask (p_trx_currency_code,30))
1710 ||' '||p_trx_currency_code);
1711 fnd_msg_pub.Add;
1712 p_return_status := FND_API.G_RET_STS_ERROR;
1713
1714 -- Check for Unearned Discounts.
1715 -- Bug 3527600: Allow for negative discount
1716 ELSIF arp_global.sysparam.unearned_discount = 'N'
1717 AND ABS(p_discount) > ABS(p_discount_earned_allowed)
1718 THEN
1719 fnd_message.set_name ('AR','AR_RW_VAL_UNEARNED_DISCOUNT');
1720 fnd_msg_pub.Add;
1721 p_return_status := FND_API.G_RET_STS_ERROR;
1722 END IF;
1723 END IF;
1724
1725 IF PG_DEBUG in ('Y', 'C') THEN
1726 arp_util.debug('Validate_discount ()-');
1727 END IF;
1728 END Validate_discount;
1729
1730 PROCEDURE Validate_apply_gl_date(p_apply_gl_date IN DATE,
1731 p_trx_gl_date IN DATE,
1732 p_cr_gl_date IN DATE,
1733 p_return_status OUT NOCOPY VARCHAR2
1734 ) IS
1735 l_bool BOOLEAN;
1736
1737 BEGIN
1738 IF PG_DEBUG in ('Y', 'C') THEN
1739 arp_util.debug('Validate_apply_gl_date ()+');
1740 END IF;
1741 p_return_status := FND_API.G_RET_STS_SUCCESS;
1742
1743 IF p_apply_gl_date IS NOT NULL THEN
1744
1745 -- Check that the application GL Date is not before the invoice GL Date.
1746 IF p_apply_gl_date < p_trx_gl_date THEN
1747 FND_MESSAGE.SET_NAME('AR','AR_VAL_GL_INV_GL');
1748 FND_MSG_PUB.Add;
1749 p_return_status := FND_API.G_RET_STS_ERROR;
1750
1751 -- Check that the application GL Date is not before the receipt GL Date.
1752 END IF;
1753
1754 IF p_apply_gl_date < p_cr_gl_date THEN
1755 FND_MESSAGE.SET_NAME('AR','AR_RW_GL_DATE_BEFORE_REC_GL');
1756 FND_MSG_PUB.Add;
1757 p_return_status := FND_API.G_RET_STS_ERROR;
1758 END IF;
1759
1760
1761 -- Check that the Application GL Date is in an open or future GL period.
1762 IF ( NOT arp_util.is_gl_date_valid( p_apply_gl_date )) THEN
1763 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
1764 --Int'l Calendar Project
1765 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_apply_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
1766 FND_MSG_PUB.Add;
1767 p_return_status := FND_API.G_RET_STS_ERROR;
1768 END IF;
1769
1770
1771 END IF;
1772 IF PG_DEBUG in ('Y', 'C') THEN
1773 arp_util.debug('Validate_apply_gl_date ()-');
1774 END IF;
1775
1776 END Validate_apply_gl_date;
1777
1778 PROCEDURE Validate_apply_date(p_apply_date IN DATE,
1779 p_trx_date IN DATE,
1780 p_cr_date IN DATE,
1781 p_return_status OUT NOCOPY VARCHAR2
1782 ) IS
1783
1784 BEGIN
1785 IF PG_DEBUG in ('Y', 'C') THEN
1786 arp_util.debug('Validate_apply_date ()+');
1787 END IF;
1788 p_return_status := FND_API.G_RET_STS_SUCCESS;
1789
1790 IF p_apply_date IS NOT NULL THEN
1791
1792 -- check that the apply date is not before the invoice date.
1793 IF p_apply_date < p_trx_date THEN
1794 FND_MESSAGE.SET_NAME('AR','AR_APPLY_BEFORE_TRANSACTION');
1795 FND_MSG_PUB.Add;
1796 p_return_status := FND_API.G_RET_STS_ERROR;
1797
1798 -- check that the application date is not before the receipt date.
1799 END IF;
1800
1801 IF p_apply_date < p_cr_date THEN
1802 FND_MESSAGE.SET_NAME('AR','AR_APPLY_BEFORE_RECEIPT');
1803 FND_MSG_PUB.Add;
1804 p_return_status := FND_API.G_RET_STS_ERROR;
1805 END IF;
1806
1807 END IF;
1808
1809 IF PG_DEBUG in ('Y', 'C') THEN
1810 arp_util.debug('Validate_apply_date ()-');
1811 END IF;
1812 END Validate_apply_date;
1813
1814 PROCEDURE Validate_Application_info(
1815 p_apply_date IN DATE,
1816 p_cr_date IN DATE,
1817 p_trx_date IN DATE,
1818 p_apply_gl_date IN DATE,
1819 p_trx_gl_date IN DATE,
1820 p_cr_gl_date IN DATE,
1821 p_amount_applied IN NUMBER,
1822 p_applied_payment_schedule_id IN NUMBER,
1823 p_customer_trx_line_id IN NUMBER,
1824 p_inv_line_amount IN NUMBER,
1825 p_creation_sign IN VARCHAR2,
1826 p_allow_overappln_flag IN VARCHAR2,
1827 p_natural_appln_only_flag IN VARCHAR2,
1828 p_discount IN NUMBER,
1829 p_amount_due_remaining IN NUMBER,
1830 p_amount_due_original IN NUMBER,
1831 p_trans_to_receipt_rate IN NUMBER,
1832 p_cr_currency_code IN VARCHAR2,
1833 p_trx_currency_code IN VARCHAR2,
1834 p_amount_applied_from IN NUMBER,
1835 p_cr_unapp_amount IN NUMBER,
1836 p_partial_discount_flag IN VARCHAR2,
1837 p_discount_earned_allowed IN NUMBER,
1838 p_discount_max_allowed IN NUMBER,
1839 p_move_deferred_tax IN VARCHAR2,
1840 p_llca_type IN VARCHAR2,
1841 p_line_amount IN NUMBER,
1842 p_tax_amount IN NUMBER,
1843 p_freight_amount IN NUMBER,
1844 p_charges_amount IN NUMBER,
1845 p_line_discount IN NUMBER,
1846 p_tax_discount IN NUMBER,
1847 p_freight_discount IN NUMBER,
1848 p_line_items_original IN NUMBER,
1849 p_line_items_remaining IN NUMBER,
1850 p_tax_original IN NUMBER,
1851 p_tax_remaining IN NUMBER,
1852 p_freight_original IN NUMBER,
1853 p_freight_remaining IN NUMBER,
1854 p_rec_charges_charged IN NUMBER,
1855 p_rec_charges_remaining IN NUMBER,
1856 p_return_status OUT NOCOPY VARCHAR2
1857 ) IS
1858 l_gl_date_return_status VARCHAR2(1);
1859 l_amt_applied_return_status VARCHAR2(1);
1860 l_x_rate_return_status VARCHAR2(1);
1861 l_disc_return_status VARCHAR2(1);
1862 l_amt_app_from_return_status VARCHAR2(1);
1863 l_apply_date_return_status VARCHAR2(1);
1864 --LLCA
1865 l_line_applied_return_status VARCHAR2(1);
1866 l_tax_applied_return_status VARCHAR2(1);
1867 l_frt_applied_return_status VARCHAR2(1);
1868 l_chg_applied_return_status VARCHAR2(1);
1869 BEGIN
1870
1871 --The customer_trx_id, cash_receipt_id and the applied_payment_schedule_id
1872 --have already been validated in the defaulting routines.
1873
1874 IF PG_DEBUG in ('Y', 'C') THEN
1875 arp_util.debug('Validate_Application_info ()+');
1876 END IF;
1877 p_return_status := FND_API.G_RET_STS_SUCCESS;
1878 --validation of the trx_number/customer_trx_id and
1879 --receipt_number/cash_receipt_id entered by the user
1880 --is done in the process of defaulting the Trx info and
1881 --the Receipt Info by the respective defaulting routines
1882
1883 validate_apply_date(p_apply_date,
1884 p_trx_date,
1885 p_cr_date,
1886 l_apply_date_return_status
1887 );
1888
1889 validate_apply_gl_date(p_apply_gl_date ,
1890 p_trx_gl_date ,
1891 p_cr_gl_date ,
1892 l_gl_date_return_status
1893 );
1894
1895 IF PG_DEBUG in ('Y', 'C') THEN
1896 arp_util.debug('Validate_Application_info: ' || 'Apply gl_date return
1897 status :'||l_gl_date_return_status);
1898 END IF;
1899 validate_amount_applied(
1900 p_amount_applied ,
1901 p_applied_payment_schedule_id ,
1902 p_customer_trx_line_id ,
1903 p_inv_line_amount ,
1904 p_creation_sign ,
1905 p_allow_overappln_flag ,
1906 p_natural_appln_only_flag,
1907 p_discount ,
1908 p_amount_due_remaining ,
1909 p_amount_due_original,
1910 l_amt_applied_return_status
1911 );
1912 IF PG_DEBUG in ('Y', 'C') THEN
1913 arp_util.debug('Validate_Application_info: ' || 'Amount applied return
1914 status :'||l_amt_applied_return_status);
1915 END IF;
1916
1917 If p_llca_type = 'S' THEN
1918 -- Validate the line/tax/freight/charges amount only if p_llca_type is
1919 -- not null.
1920 If nvl(p_line_amount,0) <> 0 THEN
1921 validate_line_applied(
1922 p_line_amount ,
1923 p_applied_payment_schedule_id ,
1924 p_customer_trx_line_id ,
1925 p_inv_line_amount ,
1926 p_creation_sign ,
1927 p_allow_overappln_flag ,
1928 p_natural_appln_only_flag,
1929 p_llca_type,
1930 p_discount ,
1931 p_line_items_remaining ,
1932 p_line_items_original,
1933 l_line_applied_return_status
1934 );
1935 IF PG_DEBUG in ('Y', 'C') THEN
1936 arp_util.debug('Validate_Application_info: ' || 'Line
1937 Amount applied return status :'||l_line_applied_return_status);
1938 END IF;
1939 End If;
1940
1941 If nvl(p_tax_amount,0) <> 0 THEN
1942 validate_tax_applied(
1943 p_tax_amount ,
1944 p_applied_payment_schedule_id ,
1945 p_creation_sign ,
1946 p_allow_overappln_flag ,
1947 p_natural_appln_only_flag,
1948 p_llca_type,
1949 p_discount ,
1950 p_tax_remaining ,
1951 p_tax_original,
1952 l_tax_applied_return_status
1953 );
1954 IF PG_DEBUG in ('Y', 'C') THEN
1955 arp_util.debug('Validate_Application_info: ' || 'Tax Amount
1956 applied return status :'||l_tax_applied_return_status);
1957 END IF;
1958 END IF;
1959
1960 IF nvl(p_freight_amount,0) <> 0 THEN
1961 validate_Freight_applied(
1962 p_freight_amount ,
1963 p_applied_payment_schedule_id ,
1964 p_customer_trx_line_id ,
1965 p_inv_line_amount ,
1966 p_creation_sign ,
1967 p_allow_overappln_flag ,
1968 p_natural_appln_only_flag,
1969 p_llca_type,
1970 p_discount ,
1971 p_freight_remaining ,
1972 p_freight_original,
1973 l_frt_applied_return_status
1974 );
1975 IF PG_DEBUG in ('Y', 'C') THEN
1976 arp_util.debug('Validate_Application_info: ' || 'Freight Amount
1977 applied return status :'||l_frt_applied_return_status);
1978 END IF;
1979 END IF;
1980
1981 IF nvl(p_charges_amount,0) <> 0 THEN
1982 validate_charges_applied(
1983 p_charges_amount ,
1984 p_applied_payment_schedule_id ,
1985 p_customer_trx_line_id ,
1986 p_inv_line_amount ,
1987 p_creation_sign ,
1988 p_allow_overappln_flag ,
1989 p_natural_appln_only_flag,
1990 p_llca_type,
1991 p_discount ,
1992 p_rec_charges_remaining ,
1993 -- p_rec_charges_charged, Pass original as remaining bcoz
1994 -- ar_open_trx_v does not have charges original
1995 p_rec_charges_remaining ,
1996 l_chg_applied_return_status
1997 );
1998 IF PG_DEBUG in ('Y', 'C') THEN
1999 arp_util.debug('Validate_Application_info: ' || 'Charges Amount
2000 applied return status :'||l_chg_applied_return_status);
2001 END IF;
2002 END IF;
2003 END IF;
2004
2005 validate_trans_to_receipt_rate(
2006 p_trans_to_receipt_rate ,
2007 p_cr_currency_code ,
2008 p_trx_currency_code ,
2009 p_amount_applied ,
2010 p_amount_applied_from ,
2011 l_x_rate_return_status
2012 );
2013 IF PG_DEBUG in ('Y', 'C') THEN
2014 arp_util.debug('Validate_Application_info: ' || 'Trans to receipt rate return status :'||l_x_rate_return_status);
2015 END IF;
2016 validate_amount_applied_from(
2017 p_amount_applied_from ,
2018 p_amount_applied,
2019 p_cr_unapp_amount ,
2020 p_cr_currency_code ,
2021 p_trx_currency_code ,
2022 l_amt_app_from_return_status
2023 );
2024 IF PG_DEBUG in ('Y', 'C') THEN
2025 arp_util.debug('Validate_Application_info: ' || 'Amount applied from return_status :'||l_amt_app_from_return_status);
2026 END IF;
2027
2028 validate_discount(p_discount ,
2029 p_amount_due_remaining ,
2030 p_amount_due_original ,
2031 p_amount_applied ,
2032 p_partial_discount_flag,
2033 p_applied_payment_schedule_id,
2034 p_discount_earned_allowed ,
2035 p_discount_max_allowed ,
2036 p_trx_currency_code,
2037 l_disc_return_status
2038 );
2039
2040 -- LLCA
2041 /* If P_llca_type is NOT NULL
2042 THEN
2043 IF (( Nvl(p_line_discount,0) + Nvl(p_tax_discount,0)
2044 + Nvl(p_freight_discount,0) )
2045 > Nvl(p_discount_max_allowed,0)
2046 )
2047 THEN
2048 FND_MESSAGE.SET_NAME( 'AR','AR_RAPI_LTFC_DISC_OAPP');
2049 FND_MSG_PUB.ADD;
2050 p_return_status := FND_API.G_RET_STS_ERROR ;
2051 END IF;
2052 END IF; */
2053
2054 --validate p_move_deferred_tax
2055 IF p_move_deferred_tax NOT IN ('Y','N') THEN
2056 FND_MESSAGE.SET_NAME('AR','AR_RAPI_DEF_TAX_FLAG_INVALID');
2057 FND_MSG_PUB.Add;
2058 p_return_status := FND_API.G_RET_STS_ERROR;
2059 END IF;
2060 IF PG_DEBUG in ('Y', 'C') THEN
2061 arp_util.debug('Validate_Application_info: ' || 'Discount return status :'||l_disc_return_status);
2062 END IF;
2063
2064 IF l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
2065 l_amt_applied_return_status <> FND_API.G_RET_STS_SUCCESS OR
2066 l_x_rate_return_status <> FND_API.G_RET_STS_SUCCESS OR
2067 l_disc_return_status <> FND_API.G_RET_STS_SUCCESS OR
2068 l_amt_app_from_return_status <> FND_API.G_RET_STS_SUCCESS OR
2069 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2070
2071 p_return_status := FND_API.G_RET_STS_ERROR;
2072 END IF;
2073
2074 IF PG_DEBUG in ('Y', 'C') THEN
2075 arp_util.debug('Validate_Application_info ()-');
2076 END IF;
2077 EXCEPTION
2078 WHEN others THEN
2079 IF PG_DEBUG in ('Y', 'C') THEN
2080 arp_util.debug('EXCEPTION: Validate_Application_Info() ');
2081 END IF;
2082 raise;
2083 END Validate_Application_info;
2084
2085 PROCEDURE Validate_Rev_gl_date(p_reversal_gl_date IN DATE,
2086 p_apply_gl_date IN DATE,
2087 p_receipt_gl_date IN DATE,
2088 p_recpt_last_state_gl_date IN DATE, /* Bug fix 2441105 */
2089 p_return_status OUT NOCOPY VARCHAR2
2090 ) IS
2091
2092 BEGIN
2093 IF PG_DEBUG in ('Y', 'C') THEN
2094 arp_util.debug('Validate_Rev_gl_date ()+');
2095 END IF;
2096 p_return_status := FND_API.G_RET_STS_SUCCESS;
2097 IF p_reversal_gl_date IS NOT NULL THEN
2098
2099 IF p_reversal_gl_date < NVL(p_apply_gl_date,p_reversal_gl_date) THEN
2100 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_APP_GL_DATE');
2101 --Int'l Calendar Project
2102 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(p_apply_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2103 FND_MSG_PUB.Add;
2104 p_return_status := FND_API.G_RET_STS_ERROR;
2105 END IF;
2106 IF p_reversal_gl_date < nvl(p_receipt_gl_date,p_reversal_gl_date) THEN
2107 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_RECEIPT_GL_DATE');
2108 --Int'l Calendar Project
2109 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(p_receipt_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2110 FND_MSG_PUB.Add;
2111 p_return_status := FND_API.G_RET_STS_ERROR;
2112 END IF;
2113 /* Bug fix 2441105 */
2114 IF p_reversal_gl_date < nvl(p_recpt_last_state_gl_date,p_reversal_gl_date) THEN
2115 FND_MESSAGE.SET_NAME('AR','AR_RW_BEF_RCPT_STATE_GL_DATE');
2116 --Int'l Calendar Project
2117 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(p_recpt_last_state_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2118 FND_MSG_PUB.Add;
2119 p_return_status := FND_API.G_RET_STS_ERROR;
2120 END IF;
2121 IF ( NOT arp_util.is_gl_date_valid(p_reversal_gl_date)) THEN
2122 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
2123 --Int'l Calendar Project
2124 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_reversal_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2125 FND_MSG_PUB.Add;
2126 p_return_status := FND_API.G_RET_STS_ERROR;
2127 END IF;
2128
2129 ELSE
2130 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_GL_DATE_NULL');
2131 FND_MSG_PUB.Add;
2132 p_return_status := FND_API.G_RET_STS_ERROR;
2133 IF PG_DEBUG in ('Y', 'C') THEN
2134 arp_util.debug('Validate_Rev_gl_date: ' || 'The Reversal gl date is null ');
2135 END IF;
2136 END IF;
2137
2138 IF PG_DEBUG in ('Y', 'C') THEN
2139 arp_util.debug('Validate_Rev_gl_date ()+');
2140 END IF;
2141 EXCEPTION
2142 WHEN others THEN
2143 IF PG_DEBUG in ('Y', 'C') THEN
2144 arp_util.debug('EXCEPTION: Validate_rev_gl_date() ');
2145 END IF;
2146 raise;
2147 END Validate_Rev_gl_date;
2148
2149
2150 /* 8668394: No reversal allowed before application date OR application gl_date */
2151 PROCEDURE validate_rev_appln_date(p_cash_receipt_id IN NUMBER,
2152 p_reversal_gl_date IN DATE,
2153 p_reversal_date IN DATE ,
2154 p_return_status OUT NOCOPY VARCHAR2
2155 ) IS
2156 l_apply_date date;
2157 l_gl_date date;
2158
2159 BEGIN
2160 IF PG_DEBUG in ('Y', 'C') THEN
2161 arp_util.debug('validate_rev_appln_date ()+');
2162 END IF;
2163
2164 p_return_status := FND_API.G_RET_STS_SUCCESS;
2165
2166 Select max(apply_date) , max(gl_date)
2167 into l_apply_date , l_gl_date
2168 from ar_receivable_applications
2169 where cash_receipt_id = p_cash_receipt_id;
2170
2171 IF p_reversal_gl_date < l_gl_date THEN
2172 FND_MESSAGE.SET_NAME('AR','AR_RW_BEF_RCPT_APP_GL_DATE');
2173 --Int'l Calendar Project
2174 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(l_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2175 FND_MSG_PUB.Add;
2176 p_return_status := FND_API.G_RET_STS_ERROR;
2177 END IF;
2178
2179 IF p_reversal_date < l_apply_date THEN
2180 FND_MESSAGE.SET_NAME('AR','AR_RW_BEF_RCPT_APP_DATE');
2181 --Int'l Calendar Project
2182 FND_MESSAGE.SET_TOKEN('APPLY_DATE', fnd_date.date_to_chardate(l_apply_date, calendar_aware=> FND_DATE.calendar_aware_alt));
2183 FND_MSG_PUB.Add;
2184 p_return_status := FND_API.G_RET_STS_ERROR;
2185 END IF;
2186
2187 IF PG_DEBUG in ('Y', 'C') THEN
2188 arp_util.debug('validate_rev_appln_date ()-');
2189 END IF;
2190
2191 EXCEPTION
2192 WHEN NO_DATA_FOUND THEN
2193 IF PG_DEBUG in ('Y', 'C') THEN
2194 arp_util.debug('No application exists for this receipt.');
2195 END IF;
2196 null;
2197 WHEN OTHERS THEN
2198 IF PG_DEBUG in ('Y', 'C') THEN
2199 arp_util.debug('EXCEPTION: validate_rev_appln_date() ' || sqlerrm);
2200 END IF;
2201 raise;
2202 END validate_rev_appln_date;
2203
2204
2205 PROCEDURE Validate_receivable_appln_id(
2206 p_receivable_application_id IN NUMBER,
2207 p_application_type IN VARCHAR2,
2208 p_return_status OUT NOCOPY VARCHAR2) IS
2209 l_valid NUMBER;
2210 BEGIN
2211 p_return_status := FND_API.G_RET_STS_SUCCESS;
2212 IF PG_DEBUG in ('Y', 'C') THEN
2213 arp_util.debug('Validate_receivable_appln_id ()+');
2214 END IF;
2215 --validate the receivable application id only if it was passed in
2216 --directly as a parameter. No need to validate if it was derived.
2217 IF p_receivable_application_id IS NOT NULL AND
2218 ar_receipt_api_pub.Original_unapp_info.receivable_application_id IS NOT NULL
2219 THEN
2220 SELECT count(*)
2221 INTO l_valid
2222 FROM AR_RECEIVABLE_APPLICATIONS ra
2223 WHERE ra.receivable_application_id = p_receivable_application_id
2224 and ra.display = 'Y'
2225 and ra.status = p_application_type
2226 and ra.application_type = 'CASH';
2227
2228 IF l_valid = 0 THEN
2229 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
2230 FND_MSG_PUB.Add;
2231 p_return_status := FND_API.G_RET_STS_ERROR;
2232 END IF;
2233
2234 ELSIF p_receivable_application_id IS NULL THEN
2235 IF ar_receipt_api_pub.Original_unapp_info.trx_number IS NULL AND
2236 ar_receipt_api_pub.Original_unapp_info.customer_trx_id IS NULL AND
2237 ar_receipt_api_pub.Original_unapp_info.applied_ps_id IS NULL AND
2238 ar_receipt_api_pub.Original_unapp_info.cash_receipt_id IS NULL AND
2239 ar_receipt_api_pub.Original_unapp_info.receipt_number IS NULL
2240 THEN
2241 --receivable application id is null
2242 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_NULL');
2243 FND_MSG_PUB.Add;
2244 p_return_status := FND_API.G_RET_STS_ERROR;
2245 ELSIF ar_receipt_api_pub.Original_unapp_info.trx_number IS NULL AND
2246 ar_receipt_api_pub.Original_unapp_info.customer_trx_id IS NULL AND
2247 ar_receipt_api_pub.Original_unapp_info.applied_ps_id IS NULL AND
2248 (ar_receipt_api_pub.Original_unapp_info.cash_receipt_id IS NOT NULL OR
2249 ar_receipt_api_pub.Original_unapp_info.receipt_number IS NOT NULL)
2250 THEN
2251 --the transaction was not specified
2252 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_NULL');
2253 FND_MSG_PUB.Add;
2254 p_return_status := FND_API.G_RET_STS_ERROR;
2255 ELSIF (ar_receipt_api_pub.Original_unapp_info.trx_number IS NOT NULL OR
2256 ar_receipt_api_pub.Original_unapp_info.customer_trx_id IS NOT NULL OR
2257 ar_receipt_api_pub.Original_unapp_info.applied_ps_id IS NOT NULL) AND
2258 ar_receipt_api_pub.Original_unapp_info.cash_receipt_id IS NULL AND
2259 ar_receipt_api_pub.Original_unapp_info.receipt_number IS NULL
2260 THEN
2261 --the receipt was not specified
2262 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_NULL');
2263 FND_MSG_PUB.Add;
2264 p_return_status := FND_API.G_RET_STS_ERROR;
2265 END IF;
2266
2267 END IF;
2268 IF PG_DEBUG in ('Y', 'C') THEN
2269 arp_util.debug('Validate_receivable_appln_id ()-');
2270 END IF;
2271 EXCEPTION
2272 WHEN others THEN
2273 IF PG_DEBUG in ('Y', 'C') THEN
2274 arp_util.debug('EXCEPTION: Validate_receivable_appln_id()');
2275 END IF;
2276 raise;
2277 END Validate_receivable_appln_id;
2278
2279 /*Added parameter p_cr_unapp_amount for bug 3119391 */
2280 PROCEDURE Validate_unapp_info(
2281 p_receipt_gl_date IN DATE,
2282 p_receivable_application_id IN NUMBER,
2283 p_reversal_gl_date IN DATE,
2284 p_apply_gl_date IN DATE,
2285 p_cr_unapp_amount IN NUMBER,
2286 p_return_status OUT NOCOPY VARCHAR2
2287 ) IS
2288 l_rec_app_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2289 l_rev_gl_date_return_status VARCHAR2(1) ;
2290 l_amt_app_from_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;/*Added for 3119391 */
2291 BEGIN
2292 p_return_status := FND_API.G_RET_STS_SUCCESS;
2293 IF PG_DEBUG in ('Y', 'C') THEN
2294 arp_util.debug('Validate_unapp_info ()+');
2295 END IF;
2296
2297 --In case the user has entered the receivable application id
2298 -- as well as the receipt and transaction info, then the cross validation
2299 --is done at the defaulting phase itself so no need to do that here.
2300 Validate_receivable_appln_id(
2301 p_receivable_application_id,
2302 'APP',
2303 l_rec_app_return_status);
2304
2305 Validate_rev_gl_date( p_reversal_gl_date ,
2306 p_apply_gl_date ,
2307 p_receipt_gl_date,
2308 NULL,
2309 l_rev_gl_date_return_status
2310 );
2311 /*Addded this call for bug 3119391 */
2312 Validate_amount_applied_from( p_receivable_application_id,
2313 p_cr_unapp_amount,
2314 l_amt_app_from_return_status);
2315 /*Added l_amt_app_from_return_status condition for bug 3119391 */
2316 IF l_rev_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
2317 l_rec_app_return_status <> FND_API.G_RET_STS_SUCCESS OR
2318 l_amt_app_from_return_status <> FND_API.G_RET_STS_SUCCESS
2319 THEN
2320 p_return_status := FND_API.G_RET_STS_ERROR;
2321 ELSE
2322 p_return_status := FND_API.G_RET_STS_SUCCESS;
2323 END IF;
2324 IF PG_DEBUG in ('Y', 'C') THEN
2325 arp_util.debug('Validate_unapp_info: ' || 'Recevable appln id return status '||l_rec_app_return_status);
2326 arp_util.debug('Validate_unapp_info: ' || 'Rev_gl_date_return_status :'||l_rev_gl_date_return_status);
2327 arp_util.debug('Validate_unapp_info ()-');
2328 END IF;
2329 END Validate_unapp_info;
2330
2331 PROCEDURE check_std_reversible(p_cash_receipt_id IN NUMBER,
2332 p_reversal_date IN DATE,
2333 p_receipt_state IN VARCHAR2,
2334 p_called_from IN VARCHAR2,
2335 p_std_reversal_possible OUT NOCOPY VARCHAR2
2336 ) IS
2337 l_dummy NUMBER;
2338 l_reserved VARCHAR2(1) DEFAULT 'N';
2339 l_std_appln VARCHAR2(1) DEFAULT 'N';
2340 BEGIN
2341 IF PG_DEBUG in ('Y', 'C') THEN
2342 arp_util.debug('check_std_reversible ()+');
2343 END IF;
2344 --Check whether it has not been reversed yet
2345 IF p_reversal_date IS NULL OR
2346 p_receipt_state <> 'APPROVED' THEN
2347 -- Check if a 'CB' was created against this PMT to be reversed.
2348 -- Check if there are any PMT, ADJ, or CM or CB against this 'CB' records
2349 -- in AR_PAYMENT_SCHEDULES table. Also check to see if the CB has
2350 -- already been posted. If any of these 2 conditions is TRUE, then
2351 -- PMT can only be reversed using DM Reversal.
2352 -- Make sure that the adj which is automatically created against the CB
2353 -- associated with the receipt being reversed does not get caught in
2354 -- the SQL. For such an adj, the adj.receivables_trx_id = -12
2355
2356
2357 SELECT COUNT(payment_schedule_id)
2358 INTO l_dummy
2359 FROM ar_payment_schedules ps,
2360 ra_cust_trx_line_gl_dist rctlg
2361 WHERE ps.associated_cash_receipt_id = p_cash_receipt_id
2362 AND ps.class = 'CB'
2363 AND ps.customer_trx_id = rctlg.customer_trx_id
2364 AND ( nvl(ps.amount_applied, 0) <> 0
2365 OR nvl(ps.amount_credited, 0) <> 0
2366 OR 0 <> ( SELECT sum(adj.amount)
2367 FROM ar_adjustments adj
2368 WHERE adj.payment_schedule_id =
2369 ps.payment_schedule_id
2370 AND adj.receivables_trx_id <> -12
2371 )
2372 );
2373
2374 IF (l_dummy > 0) THEN
2375 p_std_reversal_possible := 'N';
2376 ELSE
2377 p_std_reversal_possible := 'Y';
2378 END IF;
2379 ELSE
2380 p_std_reversal_possible := 'N';
2381 END IF;
2382
2383
2384 --added code to check if there is a bill of type 'reserved' is
2385 --applied to the receipt
2386 IF p_std_reversal_possible = 'Y' THEN
2387
2388 --check if there is a SHORT TERM DEBT application on the receipt
2389 BEGIN
2390 SELECT 'Y'
2391 INTO l_std_appln
2392 FROM ar_receivable_applications ra
2393 WHERE ra.cash_receipt_id = p_cash_receipt_id
2394 AND ra.status = 'ACTIVITY'
2395 AND ra.applied_payment_schedule_id = -2
2396 AND display = 'Y'
2397 AND p_called_from NOT IN ('BR_REMITTED',
2398 'BR_FACTORED_WITH_RECOURSE',
2399 'BR_FACTORED_WITHOUT_RECOURSE'); --fixed bug 1450460
2400 EXCEPTION
2401 WHEN no_data_found THEN
2402 null;
2403 WHEN others THEN
2404 raise;
2405 END;
2406
2407 --check the receipt has been applied to a bill for which
2408 --the reversed_type and the reversed_value columns in the payment_schedule
2409 --record have not null values(indicating that it is in the br remit process)
2410
2411 IF p_called_from IN ('BR_REMITTED',
2412 'BR_FACTORED_WITH_RECOURSE',
2413 'BR_FACTORED_WITHOUT_RECOURSE') THEN
2414 --called from the BR Remittance program
2415 null;
2416 ELSE
2417
2418 BEGIN
2419 SELECT 'Y'
2420 INTO l_reserved
2421 FROM ar_payment_schedules ps,
2422 ar_receivable_applications ra
2423 WHERE ra.cash_receipt_id = p_cash_receipt_id
2424 AND ra.applied_payment_schedule_id = ps.payment_schedule_id
2425 AND ps.reserved_type IS NOT NULL
2426 AND ps.reserved_value IS NOT NULL
2427 AND ra.status = 'APP'
2428 AND ra.display = 'Y';
2429 EXCEPTION
2430 WHEN no_data_found THEN
2431 null;
2432 WHEN others THEN
2433 raise;
2434 END;
2435
2436 END IF;
2437
2438 IF l_reserved = 'Y' OR
2439 l_std_appln = 'Y' THEN
2440 p_std_reversal_possible := 'N';
2441 END IF;
2442
2443 END IF;
2444 IF PG_DEBUG in ('Y', 'C') THEN
2445 arp_util.debug('check_std_reversible ()-');
2446 END IF;
2447
2448 EXCEPTION
2449 WHEN others THEN
2450 IF PG_DEBUG in ('Y', 'C') THEN
2451 arp_util.debug('EXCEPTION: check_std_reversible()');
2452 END IF;
2453 raise;
2454 END check_std_reversible;
2455
2456 PROCEDURE Validate_cash_receipt_id(
2457 p_type IN VARCHAR2,
2458 p_cash_receipt_id IN NUMBER,
2459 p_status1 IN VARCHAR2,
2460 p_status2 IN VARCHAR2,
2461 p_status3 IN VARCHAR2,
2462 p_status4 IN VARCHAR2,
2463 p_status5 IN VARCHAR2,
2464 p_return_status OUT NOCOPY VARCHAR2) IS
2465 l_valid NUMBER;
2466 BEGIN
2467 IF PG_DEBUG in ('Y', 'C') THEN
2468 arp_util.debug('Validate_cash_receipt_id ()+');
2469 END IF;
2470 IF p_cash_receipt_id IS NOT NULL THEN
2471
2472 SELECT count(*)
2473 INTO l_valid
2474 FROM ar_cash_receipts cr,
2475 ar_cash_receipt_history crh
2476 WHERE cr.cash_receipt_id = p_cash_receipt_id
2477 and cr.cash_receipt_id = crh.cash_receipt_id
2478 and crh.current_record_flag = 'Y'
2479 and crh.status
2480 IN (p_status1,p_status2,p_status3,p_status4,p_status5);
2481
2482 IF l_valid = 0 THEN
2483 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_INVALID');
2484 FND_MSG_PUB.Add;
2485 p_return_status := FND_API.G_RET_STS_ERROR;
2486 END IF;
2487 ELSE
2488 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_NULL');
2489 FND_MSG_PUB.Add;
2490 p_return_status := FND_API.G_RET_STS_ERROR;
2491 END IF;
2492 IF PG_DEBUG in ('Y', 'C') THEN
2493 arp_util.debug('Validate_cash_receipt_id ()-');
2494 END IF;
2495 EXCEPTION
2496 WHEN others THEN
2497 IF PG_DEBUG in ('Y', 'C') THEN
2498 arp_util.debug('EXCEPTION: Validate_cash_receipt_id() ');
2499 END IF;
2500 raise;
2501 END Validate_cash_receipt_id;
2502
2503 PROCEDURE Validate_reversal_catg_code(
2504 p_reversal_category_code IN VARCHAR2,
2505 p_return_status OUT NOCOPY VARCHAR2
2506 ) IS
2507 l_valid NUMBER;
2508 BEGIN
2509 IF PG_DEBUG in ('Y', 'C') THEN
2510 arp_util.debug('Validate_reversal_catg_code ()+');
2511 END IF;
2512 IF p_reversal_category_code IS NOT NULL THEN
2513 SELECT count(*)
2514 INTO l_valid
2515 FROM ar_lookups
2516 WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'
2517 and enabled_flag = 'Y'
2518 and lookup_code = p_reversal_category_code;
2519 IF l_valid =0 THEN
2520 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_CAT_CD_INVALID');
2521 FND_MSG_PUB.Add;
2522 p_return_status := FND_API.G_RET_STS_ERROR;
2523 END IF;
2524 ELSE
2525 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_CAT_CD_NULL');
2526 FND_MSG_PUB.Add;
2527 p_return_status := FND_API.G_RET_STS_ERROR;
2528 END IF;
2529 IF PG_DEBUG in ('Y', 'C') THEN
2530 arp_util.debug('Validate_reversal_catg_code ()-');
2531 END IF;
2532 EXCEPTION
2533 WHEN others THEN
2534 IF PG_DEBUG in ('Y', 'C') THEN
2535 arp_util.debug('EXCEPTION: Validate_reversal_catg_code() ');
2536 END IF;
2537 raise;
2538 END Validate_reversal_catg_code;
2539
2540 PROCEDURE Validate_reversal_reason_code(
2541 p_reversal_reason_code IN VARCHAR2,
2542 p_return_status OUT NOCOPY VARCHAR2
2543 ) IS
2544 l_valid NUMBER;
2545 BEGIN
2546 p_return_status := FND_API.G_RET_STS_SUCCESS;
2547 IF p_reversal_reason_code IS NOT NULL THEN
2548 SELECT count(*)
2549 INTO l_valid
2550 FROM ar_lookups
2551 WHERE lookup_type = 'CKAJST_REASON'
2552 and enabled_flag = 'Y'
2553 and lookup_code = p_reversal_reason_code;
2554 IF l_valid =0 THEN
2555 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_REAS_CD_INVALID');
2556 FND_MSG_PUB.Add;
2557 p_return_status := FND_API.G_RET_STS_ERROR;
2558 END IF;
2559 ELSE
2560 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_REAS_CD_NULL');
2561 FND_MSG_PUB.Add;
2562 p_return_status := FND_API.G_RET_STS_ERROR;
2563 END IF;
2564 EXCEPTION
2565 WHEN others THEN
2566 IF PG_DEBUG in ('Y', 'C') THEN
2567 arp_util.debug('EXCEPTION: Validate_reversal_reason_code() ');
2568 END IF;
2569 raise;
2570 END Validate_reversal_reason_code;
2571
2572 PROCEDURE Validate_reverse_info(
2573 p_cash_receipt_id IN NUMBER,
2574 p_receipt_gl_date IN DATE,
2575 p_reversal_category_code IN VARCHAR2,
2576 p_reversal_reason_code IN VARCHAR2,
2577 p_reversal_gl_date IN DATE,
2578 p_reversal_date IN DATE,
2579 p_return_status OUT NOCOPY VARCHAR2
2580 ) IS
2581 l_cr_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
2582 l_rev_cat_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
2583 l_rev_res_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
2584 l_rev_gld_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
2585 l_rev_appln_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
2586 BEGIN
2587 p_return_status := FND_API.G_RET_STS_SUCCESS;
2588 IF PG_DEBUG in ('Y', 'C') THEN
2589 arp_util.debug('Validate_reverse_info ()+');
2590 END IF;
2591 Validate_cash_receipt_id('ALL',
2592 p_cash_receipt_id,
2593 'APPROVED',
2594 'CONFIRMED',
2595 'CLEARED',
2596 'REMITTED',
2597 'RISK_ELIMINATED', /* Bug fix 3263841 */
2598 l_cr_return_status);
2599
2600 Validate_reversal_catg_code(p_reversal_category_code,
2601 l_rev_cat_return_status);
2602
2603 Validate_reversal_reason_code(p_reversal_reason_code,
2604 l_rev_res_return_status
2605 );
2606
2607 Validate_Rev_gl_date(p_reversal_gl_date,
2608 NULL, --apply gl date not valid in this case.
2609 NULL,
2610 p_receipt_gl_date,
2611 l_rev_gld_return_status
2612 );
2613
2614 /* Bug 8668394 */
2615 validate_rev_appln_date(p_cash_receipt_id,
2616 p_reversal_gl_date,
2617 p_reversal_date,
2618 l_rev_appln_return_status
2619 );
2620
2621 IF l_rev_gld_return_status <> FND_API.G_RET_STS_SUCCESS OR
2622 l_rev_res_return_status <> FND_API.G_RET_STS_SUCCESS OR
2623 l_rev_cat_return_status <> FND_API.G_RET_STS_SUCCESS OR
2624 l_cr_return_status <> FND_API.G_RET_STS_SUCCESS OR
2625 l_rev_appln_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2626 p_return_status := FND_API.G_RET_STS_ERROR;
2627 END IF;
2628 IF PG_DEBUG in ('Y', 'C') THEN
2629 arp_util.debug('Validate_reverse_info ()-');
2630 END IF;
2631 EXCEPTION
2632 WHEN others THEN
2633 IF PG_DEBUG in ('Y', 'C') THEN
2634 arp_util.debug('EXCEPTION: Validate_reverse_info ()');
2635 END IF;
2636 raise;
2637 END Validate_reverse_info;
2638
2639 PROCEDURE validate_on_ac_app( p_cash_receipt_id IN NUMBER,
2640 p_cr_gl_date IN DATE,
2641 p_cr_unapp_amount IN NUMBER,
2642 p_cr_date IN DATE,
2643 p_cr_payment_schedule_id IN NUMBER,
2644 p_applied_amount IN NUMBER,
2645 p_apply_gl_date IN DATE,
2646 p_apply_date IN DATE,
2647 p_return_status OUT NOCOPY VARCHAR2,
2648 p_applied_ps_id IN NUMBER,
2649 p_called_from IN VARCHAR2
2650 ) IS
2651 l_cr_return_status VARCHAR2(1);
2652 l_amt_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2653 l_gl_date_return_status VARCHAR2(1);
2654 l_apply_date_return_status VARCHAR2(1);
2655 BEGIN
2656 p_return_status := FND_API.G_RET_STS_SUCCESS;
2657 IF PG_DEBUG in ('Y', 'C') THEN
2658 arp_util.debug('validate_on_ac_app ()+');
2659 END IF;
2660
2661 validate_cash_receipt_id('ALL',
2662 p_cash_receipt_id,
2663 NULL,
2664 'CONFIRMED',
2665 'CLEARED',
2666 'REMITTED',
2667 'RISK_ELIMINATED', /* Bug fix 3263841 */
2668 l_cr_return_status);
2669
2670 validate_apply_date(p_apply_date,
2671 p_apply_date,
2672 p_cr_date,
2673 l_apply_date_return_status
2674 );
2675
2676 -- validate amount applied
2677 IF p_applied_amount IS NULL THEN
2678 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
2679 FND_MSG_PUB.Add;
2680 l_amt_return_status := FND_API.G_RET_STS_ERROR;
2681
2682 -- Bug 2751910 - allow -ve amount on application to receipt (ps>0)
2683 ELSIF (p_applied_amount < 0 AND NVL(p_applied_ps_id,-1) <> -4 AND
2684 NVL(p_applied_ps_id,-1) <> -3 AND
2685 NVL(p_applied_ps_id,-1) < 0) THEN
2686 IF p_applied_ps_id = -8 THEN
2687 FND_MESSAGE.SET_NAME('AR','AR_REF_CM_APP_NEG');
2688 FND_MSG_PUB.Add;
2689 ELSE
2690 FND_MESSAGE.SET_NAME('AR','AR_RW_APP_NEG_ON_ACCT');
2691 FND_MSG_PUB.Add;
2692 END IF;
2693 l_amt_return_status := FND_API.G_RET_STS_ERROR;
2694 -- Bug 2897244 - amount not checked if called from form/postbatch
2695 ELSIF ((nvl(p_cr_unapp_amount,0)- p_applied_amount) < 0 AND
2696 NVL(p_applied_ps_id,-1) <> -4 AND
2697 NVL(p_called_from,'RAPI') NOT IN ('ARXRWAPP','ARCAPB')) THEN
2698 FND_MESSAGE.SET_NAME('AR','AR_RW_AMOUNT_LESS_THAN_APP');
2699 FND_MSG_PUB.Add;
2700 l_amt_return_status := FND_API.G_RET_STS_ERROR;
2701 END IF;
2702
2703 validate_apply_gl_date(p_apply_gl_date,
2704 p_apply_gl_date,
2705 p_cr_gl_date,
2706 l_gl_date_return_status
2707 );
2708
2709 IF l_cr_return_status <> FND_API.G_RET_STS_SUCCESS OR
2710 l_amt_return_status <> FND_API.G_RET_STS_SUCCESS OR
2711 l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
2712 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS
2713 THEN
2714 p_return_status := FND_API.G_RET_STS_ERROR;
2715 END IF;
2716
2717 IF PG_DEBUG in ('Y', 'C') THEN
2718 arp_util.debug('validate_on_ac_app ()-');
2719 END IF;
2720 EXCEPTION
2721 WHEN others THEN
2722 IF PG_DEBUG in ('Y', 'C') THEN
2723 arp_util.debug('EXCEPTION: validate_on_ac_app()');
2724 END IF;
2725 raise;
2726 END validate_on_ac_app;
2727
2728
2729 /*----------------------------------------------------------+
2730 | validate_unapp_on_ac_act_info routine is called for both |
2731 | 1) activity_unapplication and |
2732 | 2) on_account_unapplication |
2733 +----------------------------------------------------------*/
2734 PROCEDURE validate_unapp_on_ac_act_info(
2735 p_receipt_gl_date IN DATE,
2736 p_receivable_application_id IN NUMBER,
2737 p_reversal_gl_date IN DATE,
2738 p_apply_gl_date IN DATE,
2739 p_cr_unapp_amt IN NUMBER, /* Bug fix 3569640 */
2740 p_return_status OUT NOCOPY VARCHAR2
2741 ) IS
2742 l_amt_app_from_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;/*Added for 3569640 */
2743 BEGIN
2744 p_return_status := FND_API.G_RET_STS_SUCCESS;
2745 IF PG_DEBUG in ('Y', 'C') THEN
2746 arp_util.debug('validate_unapp_on_ac_act_info: ' || 'Validate_unapp_on_acc_act_info ()+');
2747 END IF;
2748
2749
2750 --the receivable_application_id was validated in the defaulting stage
2751 --for all cases : 1. only receivable_application_id specified
2752 -- 2. only cash receipt specified
2753 -- 3. both cash receipt and receivable_application_id specified
2754 -- no need to validate it here
2755
2756 Validate_rev_gl_date( p_reversal_gl_date ,
2757 p_apply_gl_date ,
2758 p_receipt_gl_date,
2759 NULL,
2760 p_return_status
2761 );
2762
2763 /* Bug fix 3569640 */
2764 IF p_receivable_application_id IS NOT NULL
2765 AND p_cr_unapp_amt IS NOT NULL THEN
2766 Validate_amount_applied_from( p_receivable_application_id,
2767 p_cr_unapp_amt,
2768 l_amt_app_from_return_status);
2769 END IF;
2770 IF l_amt_app_from_return_status <> FND_API.G_RET_STS_SUCCESS OR
2771 p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2772 p_return_status := FND_API.G_RET_STS_ERROR;
2773 END IF;
2774
2775 IF PG_DEBUG in ('Y', 'C') THEN
2776 arp_util.debug('validate_unapp_on_ac_act_info: ' || 'p_return_status :'||p_return_status);
2777 arp_util.debug('validate_unapp_on_ac_act_info: ' || 'Validate_unapp_on_acc_act_info ()-');
2778 END IF;
2779 END validate_unapp_on_ac_act_info;
2780
2781 PROCEDURE validate_ccrefund(
2782 p_cash_receipt_id IN NUMBER,
2783 p_applied_ps_id IN NUMBER,
2784 p_return_status IN OUT NOCOPY VARCHAR2
2785 ) IS
2786 l_payment_type VARCHAR2(30);
2787 l_status ar_cash_receipt_history.status%TYPE;
2788
2789 BEGIN
2790
2791 --If the applied payment schedule_id -7, we can issue the refund only to credit card.
2792
2793 IF PG_DEBUG in ('Y', 'C') THEN
2794 arp_util.debug('Validate_ccrefund (+)');
2795 END IF;
2796 IF p_applied_ps_id IN (-6, -9) THEN
2797 BEGIN
2798 SELECT NVL(arm.payment_channel_code,'NONE')
2799 INTO l_payment_type
2800 FROM ar_cash_receipts cr,
2801 ar_receipt_methods arm
2802 WHERE cr.receipt_method_id = arm.receipt_method_id
2803 AND cr.cash_receipt_id=p_cash_receipt_id;
2804
2805 IF l_payment_type <> 'CREDIT_CARD' THEN
2806 IF PG_DEBUG in ('Y', 'C') THEN
2807 arp_util.debug('This receipt is not a Credit Card Receipt');
2808 END IF;
2809
2810 IF p_applied_ps_id = -6 THEN
2811 FND_MESSAGE.SET_NAME('AR','AR_RW_CCR_NOT_CC_RECEIPT');
2812 FND_MSG_PUB.Add;
2813 p_return_status := FND_API.G_RET_STS_ERROR;
2814 ELSE
2815 FND_MESSAGE.SET_NAME('AR','AR_RW_CC_CHARGE_NOT_CC_RECEIPT');
2816 FND_MSG_PUB.Add;
2817 p_return_status := FND_API.G_RET_STS_ERROR;
2818 END IF;
2819 END IF;
2820
2821 EXCEPTION
2822 WHEN others THEN
2823 IF PG_DEBUG in ('Y', 'C') THEN
2824 arp_util.debug('Exception ' || sqlerrm);
2825 END IF;
2826 p_return_status := FND_API.G_RET_STS_ERROR;
2827 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
2828 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Validate ccrefund' ||SQLERRM);
2829 FND_MSG_PUB.Add;
2830 END;
2831
2832
2833 BEGIN
2834 SELECT status INTO l_status
2835 FROM ar_cash_receipt_history
2836 WHERE cash_receipt_id = p_cash_receipt_id
2837 AND current_record_flag = 'Y';
2838
2839 IF l_status NOT IN ('REMITTED', 'CLEARED') THEN
2840 IF PG_DEBUG in ('Y', 'C') THEN
2841 arp_util.debug('This receipt is still not REMITTED.');
2842 END IF;
2843
2844 IF p_applied_ps_id = -6 THEN
2845 FND_MESSAGE.SET_NAME ('AR','AR_RW_CCR_NOT_REMITTED');
2846 FND_MSG_PUB.Add;
2847 p_return_status := FND_API.G_RET_STS_ERROR;
2848 ELSE
2849 FND_MESSAGE.SET_NAME ('AR','AR_RW_CC_CHARGE_NOT_REMITTED');
2850 FND_MSG_PUB.Add;
2851 p_return_status := FND_API.G_RET_STS_ERROR;
2852 END IF;
2853 END IF ;
2854
2855 EXCEPTION
2856 WHEN others THEN
2857 IF PG_DEBUG in ('Y', 'C') THEN
2858 arp_util.debug('Exception ' || sqlerrm);
2859 END IF;
2860 p_return_status := FND_API.G_RET_STS_ERROR;
2861 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
2862 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Validate ccrefund' ||SQLERRM);
2863 FND_MSG_PUB.Add;
2864 END;
2865
2866 END IF;
2867 IF PG_DEBUG in ('Y', 'C') THEN
2868 arp_util.debug('Validate_ccrefund (-)');
2869 END IF;
2870 END validate_ccrefund;
2871
2872
2873 --
2874 --Bug 1645041 : Added parameters p_cr_currency_code, p_applied_amount
2875 -- and p_cash_receipt_id which were to be used in approval limit
2876 -- validation logic added in this procedure for the write-off activity.
2877 -- Bug 2270825 - validation for claims
2878 --
2879 PROCEDURE validate_activity(p_receivables_trx_id IN NUMBER,
2880 p_applied_ps_id IN NUMBER,
2881 p_cash_receipt_id IN NUMBER,
2882 p_applied_amount IN NUMBER,
2883 p_cr_currency_code IN VARCHAR2,
2884 p_val_writeoff_limits_flag IN VARCHAR2,
2885 p_return_status IN OUT NOCOPY VARCHAR2
2886 ) IS
2887 l_activity_type VARCHAR2(30);
2888 l_amount_from NUMBER;
2889 l_amount_to NUMBER;
2890 l_user_id NUMBER;
2891 l_existing_wo_amount NUMBER;
2892 l_tot_write_off_amt NUMBER;
2893 l_max_wrt_off_amount NUMBER;
2894 l_min_wrt_off_amount NUMBER;
2895
2896 --Bug 5367753
2897 l_exchange_rate NUMBER;
2898 l_tot_writeoff_amt_func NUMBER;
2899 l_functional_currency ar_cash_receipts.currency_code%TYPE;
2900
2901 cursor activity_type is
2902 select type
2903 from ar_receivables_trx rt
2904 where receivables_trx_id = p_receivables_trx_id;
2905
2906 BEGIN
2907
2908 IF PG_DEBUG in ('Y', 'C') THEN
2909 arp_util.debug('validate_activity (+)');
2910 END IF;
2911
2912 OPEN activity_type;
2913 FETCH activity_type INTO l_activity_type;
2914 IF activity_type%NOTFOUND THEN
2915 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_TRX_ID_INVALID');
2916 FND_MSG_PUB.Add;
2917 p_return_status := FND_API.G_RET_STS_ERROR;
2918 END IF;
2919 CLOSE activity_type;
2920
2921 IF l_activity_type IS NOT NULL THEN
2922 --Validate applied ps_id
2923 --additional conditions need to be added for the ps_id in future
2924 -- -2 corresponds to short term debit
2925 IF p_applied_ps_id = -2 THEN
2926 IF l_activity_type <> 'SHORT_TERM_DEBT' THEN
2927 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
2928 FND_MSG_PUB.Add;
2929 p_return_status := FND_API.G_RET_STS_ERROR;
2930 END IF;
2931
2932 --SNAMBIAR - Modified for Write-Off
2933 ELSIF p_applied_ps_id = -3 THEN
2934 IF l_activity_type <> 'WRITEOFF' THEN
2935 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
2936 FND_MSG_PUB.Add;
2937 p_return_status := FND_API.G_RET_STS_ERROR;
2938 END IF;
2939
2940 --some additional validation that we need to do for the Write-Off: Bug 1645041.
2941 -- Bug 2751910 - Validation against user limits excluded if flag set
2942
2943
2944 l_user_id := to_number(fnd_profile.value('USER_ID'));
2945
2946 --get the existing write-off amount on the receipt.
2947
2948 BEGIN
2949 -- Bug 11729918
2950 SELECT /*+ index(ra AR_RECEIVABLE_APPLICATIONS_N1) */SUM(AMOUNT_APPLIED)
2951 --sum(amount_applied)
2952 INTO l_existing_wo_amount
2953 FROM ar_receivable_applications ra
2954 WHERE applied_payment_schedule_id = -3
2955 AND status = 'ACTIVITY'
2956 AND NVL(confirmed_flag,'Y') = 'Y'
2957 AND cash_receipt_id = p_cash_receipt_id;
2958
2959 --Bug 5367753 fetch exchange_rate of the receipt.
2960 SELECT nvl(exchange_rate,1)
2961 INTO l_exchange_rate
2962 FROM ar_cash_receipts
2963 WHERE cash_receipt_id = p_cash_receipt_id;
2964
2965 l_tot_write_off_amt := NVL(l_existing_wo_amount,0) + NVL(p_applied_amount,0);
2966
2967 EXCEPTION
2968 WHEN no_data_found THEN
2969 l_tot_write_off_amt := p_applied_amount;
2970 END;
2971
2972 IF NVL(p_val_writeoff_limits_flag,'Y') <> 'N' THEN
2973 BEGIN
2974 SELECT NVL(amount_from,0),
2975 NVL(amount_to,0)
2976 INTO l_amount_from,
2977 l_amount_to
2978 FROM ar_approval_user_limits
2979 where currency_code = p_cr_currency_code
2980 and user_id = l_user_id
2981 and document_type ='WRTOFF';
2982 EXCEPTION
2983 WHEN NO_DATA_FOUND THEN
2984 fnd_message.set_name ('AR','AR_WR_NO_LIMIT');
2985 FND_MSG_PUB.Add;
2986 p_return_status := FND_API.G_RET_STS_ERROR;
2987 END;
2988
2989 IF (NVL(l_tot_write_off_amt,0) > l_amount_to) OR
2990 (NVL(l_tot_write_off_amt,l_amount_from) < l_amount_from)
2991 THEN
2992 fnd_message.set_name ('AR','AR_WR_USER_LIMIT');
2993 fnd_message.set_token('FROM_AMOUNT', to_char(l_amount_from), FALSE);
2994 fnd_message.set_token('TO_AMOUNT', to_char(l_amount_to), FALSE);
2995 FND_MSG_PUB.Add;
2996 p_return_status := FND_API.G_RET_STS_ERROR;
2997 END IF;
2998
2999 END IF;
3000
3001 -- Bug 2751910 - validate against system limits
3002 --Bug 5367753 Modified SQL to fetch functional currency code.
3003 SELECT MAX_WRTOFF_AMOUNT,
3004 MIN_WRTOFF_AMOUNT,
3005 sob.currency_code
3006 INTO l_max_wrt_off_amount,
3007 l_min_wrt_off_amount,
3008 l_functional_currency
3009 FROM AR_SYSTEM_PARAMETERS sys,gl_sets_of_books sob
3010 WHERE sys.set_of_books_id = sob.set_of_books_id;
3011
3012 -- Bug 3136127 - if writeoff amount > 0 then max limit must have a value
3013 -- if < 0, then min limit must have a value < 0
3014 IF ((l_max_wrt_off_amount IS NULL AND
3015 NVL(l_tot_write_off_amt,0) > 0 )
3016 OR
3017 (l_min_wrt_off_amount IS NULL AND
3018 NVL(l_tot_write_off_amt,0) < 0 )
3019 ) THEN
3020 fnd_message.set_name ('AR','AR_SYSTEM_WR_NO_LIMIT_SET');
3021 FND_MSG_PUB.Add;
3022 p_return_status := FND_API.G_RET_STS_ERROR;
3023 END IF;
3024
3025 IF PG_DEBUG in ('Y', 'C') THEN
3026 arp_util.debug('l_max_wrt_off_amount = '||l_max_wrt_off_amount);
3027 arp_util.debug('l_min_wrt_off_amount = '||l_min_wrt_off_amount);
3028 arp_util.debug('l_tot_write_off_amt = '||l_tot_write_off_amt);
3029 END IF;
3030
3031 /**Bug 5367753 Condition is added to convert the writeoff amount into
3032 functional currency for validation */
3033 IF l_functional_currency <> p_cr_currency_code THEN
3034 l_tot_writeoff_amt_func := arpcurr.functional_amount(
3035 l_tot_write_off_amt,
3036 l_functional_currency,
3037 l_exchange_rate,
3038 arp_global.base_precision,
3039 arp_global.base_min_acc_unit);
3040 END IF;
3041
3042 IF l_tot_writeoff_amt_func IS NULL THEN
3043 l_tot_writeoff_amt_func := l_tot_write_off_amt;
3044 END IF;
3045
3046 IF ( (NVL(l_tot_write_off_amt,0) > l_max_wrt_off_amount) OR
3047 (NVL(l_tot_write_off_amt,0) < l_min_wrt_off_amount) ) THEN
3048 arp_util.debug('ERROR l_tot_write_off_amt = '||l_tot_write_off_amt);
3049 fnd_message.set_name ('AR','AR_WR_TOTAL_EXCEED_MAX_AMOUNT');
3050 FND_MSG_PUB.Add;
3051 p_return_status := FND_API.G_RET_STS_ERROR;
3052 END IF;
3053
3054 ELSIF p_applied_ps_id = -4 THEN
3055 IF l_activity_type <> 'CLAIM_INVESTIGATION' THEN
3056 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3057 FND_MSG_PUB.Add;
3058 p_return_status := FND_API.G_RET_STS_ERROR;
3059 END IF;
3060 ELSIF p_applied_ps_id = -5 THEN
3061 IF (l_activity_type <> 'ADJUST') OR (p_receivables_trx_id <> -11) THEN
3062 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3063 FND_MSG_PUB.Add;
3064 p_return_status := FND_API.G_RET_STS_ERROR;
3065 END IF;
3066 ELSIF p_applied_ps_id = -6 THEN
3067 IF l_activity_type <> 'CCREFUND' THEN
3068 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3069 FND_MSG_PUB.Add;
3070 p_return_status := FND_API.G_RET_STS_ERROR;
3071 END IF;
3072 ELSIF p_applied_ps_id = -7 THEN
3073 IF l_activity_type <> 'PREPAYMENT' THEN
3074 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3075 FND_MSG_PUB.Add;
3076 p_return_status := FND_API.G_RET_STS_ERROR;
3077 END IF;
3078 ELSIF p_applied_ps_id = -8 THEN
3079 IF l_activity_type <> 'CM_REFUND' THEN
3080 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3081 FND_MSG_PUB.Add;
3082 p_return_status := FND_API.G_RET_STS_ERROR;
3083 END IF;
3084 ELSIF p_applied_ps_id = -9 THEN
3085 IF l_activity_type <> 'CC_CHARGEBACK' THEN
3086 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
3087 FND_MSG_PUB.Add;
3088 p_return_status := FND_API.G_RET_STS_ERROR;
3089 END IF;
3090 ELSE
3091 --the applied payment schedule id is invalid
3092 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
3093 FND_MSG_PUB.Add;
3094 p_return_status := FND_API.G_RET_STS_ERROR;
3095 END IF; --additional control structures to be added for new activity types.
3096 END IF;
3097 IF PG_DEBUG in ('Y', 'C') THEN
3098 arp_util.debug('validate_activity (-)');
3099 END IF;
3100 END validate_activity;
3101
3102 PROCEDURE validate_activity_app( p_receivables_trx_id IN NUMBER,
3103 p_applied_ps_id IN NUMBER,
3104 p_cash_receipt_id IN NUMBER,
3105 p_cr_gl_date IN DATE,
3106 p_cr_unapp_amount IN NUMBER,
3107 p_cr_date IN DATE,
3108 p_cr_payment_schedule_id IN NUMBER,
3109 p_applied_amount IN NUMBER,
3110 p_apply_gl_date IN DATE,
3111 p_apply_date IN DATE,
3112 p_link_to_customer_trx_id IN NUMBER,
3113 p_cr_currency_code IN VARCHAR2,
3114 p_return_status OUT NOCOPY VARCHAR2,
3115 p_val_writeoff_limits_flag IN VARCHAR2,
3116 p_called_from IN VARCHAR2 -- Bug 2897244
3117 ) IS
3118 l_valid VARCHAR2(1) DEFAULT 'N';
3119 BEGIN
3120 IF PG_DEBUG in ('Y', 'C') THEN
3121 arp_util.debug('validate_activity_app ()+');
3122 END IF;
3123 p_return_status := FND_API.G_RET_STS_SUCCESS;
3124 validate_on_ac_app(
3125 p_cash_receipt_id,
3126 p_cr_gl_date,
3127 p_cr_unapp_amount,
3128 p_cr_date,
3129 p_cr_payment_schedule_id,
3130 p_applied_amount,
3131 p_apply_gl_date,
3132 p_apply_date,
3133 p_return_status,
3134 p_applied_ps_id,
3135 p_called_from -- Bug 2897244
3136 );
3137 IF p_receivables_trx_id <> -16 -- Seeded netting activity
3138 THEN
3139 validate_activity(
3140 p_receivables_trx_id,
3141 p_applied_ps_id,
3142 p_cash_receipt_id,
3143 p_applied_amount,
3144 p_cr_currency_code,
3145 p_val_writeoff_limits_flag,
3146 p_return_status
3147 );
3148 END IF;
3149 -- if this routine is called for ccrefund,this routine will check whether
3150 -- the receipt is a credit card receipt or not. We can issue refund only
3151 -- to credit card at this point.
3152
3153 validate_ccrefund(
3154 p_cash_receipt_id,
3155 p_applied_ps_id,
3156 p_return_status
3157 );
3158
3159 --SNAMBIAR for chargeback activity,customer_trx_id of the CB should be passed and should
3160 --be valid
3161 IF p_applied_ps_id = -5 THEN
3162 IF p_link_to_customer_trx_id IS NOT NULL THEN
3163 BEGIN
3164 SELECT 'Y'
3165 INTO l_valid
3166 FROM ar_payment_schedules
3167 WHERE customer_trx_id=p_link_to_customer_trx_id
3168 AND class='CB';
3169 EXCEPTION
3170 WHEN no_data_found THEN
3171 FND_MESSAGE.SET_NAME('AR','AR_RAPI_LK_CUS_TRX_ID_INVALID');
3172 FND_MSG_PUB.Add;
3173 p_return_status := FND_API.G_RET_STS_ERROR;
3174 WHEN others THEN
3175 raise;
3176 END;
3177 IF l_valid <> 'Y' THEN
3178 FND_MESSAGE.SET_NAME('AR','AR_RAPI_LK_CUS_TRX_ID_INVALID');
3179 FND_MSG_PUB.Add;
3180 p_return_status := FND_API.G_RET_STS_ERROR;
3181 END IF;
3182 l_valid := Null;
3183 ELSE
3184 FND_MESSAGE.SET_NAME('AR','AR_RAPI_LK_CUS_TRX_ID_INVALID');
3185 FND_MSG_PUB.Add;
3186 p_return_status := FND_API.G_RET_STS_ERROR;
3187 END IF;
3188 END IF;
3189
3190 --validate the p_link_to_customer_trx_id
3191 --SNAMBIAR Modified for Write-off
3192 IF p_link_to_customer_trx_id IS NOT NULL AND p_applied_ps_id <> -5 THEN
3193
3194 BEGIN
3195 SELECT 'Y'
3196 INTO l_valid
3197 FROM ar_transaction_history
3198 WHERE status IN ('FACTORED', 'MATURED_PEND_RISK_ELIMINATION',
3199 'PENDING_REMITTANCE','CLOSED')
3200 AND customer_trx_id = p_link_to_customer_trx_id
3201 AND current_record_flag = 'Y';
3202
3203 IF l_valid <> 'Y' THEN
3204 FND_MESSAGE.SET_NAME('AR','AR_RAPI_LK_CUS_TRX_ID_INVALID');
3205 FND_MSG_PUB.Add;
3206 p_return_status := FND_API.G_RET_STS_ERROR;
3207 END IF;
3208
3209 EXCEPTION
3210 WHEN no_data_found THEN
3211 FND_MESSAGE.SET_NAME('AR','AR_RAPI_LK_CUS_TRX_ID_INVALID');
3212 FND_MSG_PUB.Add;
3213 p_return_status := FND_API.G_RET_STS_ERROR;
3214 WHEN others THEN
3215 raise;
3216 END;
3217 END IF;
3218 arp_util.debug('fnd_api.g_ret_sts_error = '||fnd_api.g_ret_sts_error);
3219 IF PG_DEBUG in ('Y', 'C') THEN
3220 arp_util.debug('validate_activity_app ()-');
3221 END IF;
3222 END validate_activity_app;
3223
3224 -- Bug 2270825 - additional validation for claims
3225 PROCEDURE validate_application_ref(
3226 p_applied_ps_id IN NUMBER,
3227 p_application_ref_type IN VARCHAR2,
3228 p_application_ref_id IN NUMBER,
3229 p_application_ref_num IN VARCHAR2,
3230 p_secondary_application_ref_id IN NUMBER,
3231 p_cash_receipt_id IN NUMBER,
3232 p_amount_applied IN NUMBER,
3233 p_amount_due_remaining IN NUMBER,
3234 p_cr_currency_code IN VARCHAR2,
3235 p_trx_currency_code IN VARCHAR2,
3236 p_application_ref_reason IN VARCHAR2,
3237 p_return_status OUT NOCOPY VARCHAR2
3238 ) IS
3239 l_valid VARCHAR2(1) := 'N';
3240 l_query_text VARCHAR2(2000);
3241 l_dummy VARCHAR2(100);
3242 l_claim_id NUMBER;
3243 l_net_claim_amount NUMBER;
3244 l_check_amount NUMBER;
3245 l_reason_code_id NUMBER;
3246 l_currency_code fnd_currencies.currency_code%TYPE;
3247 invalid_claim EXCEPTION;
3248
3249 BEGIN
3250 arp_util.debug('validate_application_reference ()+');
3251
3252 IF (p_application_ref_type IS NOT NULL AND p_applied_ps_id < 0) THEN
3253 IF p_applied_ps_id IS NOT NULL and p_applied_ps_id in (-4,-5,-6,-7) THEN
3254 BEGIN
3255 SELECT 'Y'
3256 INTO l_valid
3257 FROM ar_lookups
3258 WHERE lookup_type = DECODE(p_applied_ps_id,-4,'APPLICATION_REF_TYPE',
3259 -5,'CHARGEBACK',
3260 -6, 'MISC_RECEIPT',
3261 -7,'AR_PREPAYMENT_TYPE',
3262 'NONE')
3263 AND enabled_flag = 'Y'
3264 AND lookup_code = p_application_ref_type;
3265 EXCEPTION
3266 WHEN no_data_found THEN
3267 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INVALID_APP_REF');
3268 FND_MSG_PUB.Add;
3269 p_return_status := FND_API.G_RET_STS_ERROR;
3270 END;
3271 ELSE
3272 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
3273 FND_MSG_PUB.Add;
3274 p_return_status := FND_API.G_RET_STS_ERROR;
3275 END IF;
3276 END IF;
3277 /* Bug 2270825 - claim specific validation */
3278 /* Bug 2751910 - reason no longer compulsory */
3279 IF p_application_ref_type = 'CLAIM'
3280 THEN
3281 IF (p_application_ref_num IS NULL AND p_secondary_application_ref_id IS NULL)
3282 THEN
3283 IF p_application_ref_reason IS NOT NULL THEN
3284 /* Bug 3780081: bind variable used for reason_code_id */
3285 l_reason_code_id := TO_NUMBER(p_application_ref_reason);
3286 l_query_text :=
3287 ' select reason_code_id from ozf_reason_codes_vl '||
3288 ' where reason_code_id = :application_ref_reason '||
3289 ' and sysdate between nvl(start_date_active,sysdate) '||
3290 ' and nvl(end_date_active,sysdate) ';
3291 BEGIN
3292 EXECUTE IMMEDIATE l_query_text INTO l_dummy USING l_reason_code_id;
3293 EXCEPTION
3294 WHEN OTHERS THEN
3295 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INVALID_REF_REASON');
3296 FND_MSG_PUB.Add;
3297 p_return_status := FND_API.G_RET_STS_ERROR;
3298 END;
3299 END IF;
3300 ELSE
3301
3302 /* Bug 3780081: split query into 2 variants using bind variables */
3303 IF p_applied_ps_id = -4
3304 THEN
3305 l_currency_code := p_cr_currency_code;
3306 ELSE
3307 l_currency_code := p_trx_currency_code;
3308 END IF;
3309 l_query_text :=
3310 ' select claim_id from ozf_ar_deductions_v ';
3311 IF p_secondary_application_ref_id IS NOT NULL
3312 THEN
3313 l_query_text := l_query_text ||
3314 ' where claim_id = :secondary_application_ref_id '||
3315 ' and currency_code = :currency_code ';
3316 arp_util.debug('claim query text : '||l_query_text);
3317 BEGIN
3318 EXECUTE IMMEDIATE l_query_text INTO l_claim_id
3319 USING p_secondary_application_ref_id, l_currency_code ;
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 RAISE invalid_claim;
3323 END;
3324 ELSE
3325 l_query_text := l_query_text ||
3326 ' where claim_number = :application_ref_num '||
3327 ' and currency_code = :currency_code ';
3328 arp_util.debug('claim query text : '||l_query_text);
3329 BEGIN
3330 EXECUTE IMMEDIATE l_query_text INTO l_claim_id
3331 USING p_application_ref_num, l_currency_code ;
3332 EXCEPTION
3333 WHEN OTHERS THEN
3334 RAISE invalid_claim;
3335 END;
3336 END IF;
3337
3338 /* Bug 2353144 - amount checking uses net amount remaining for claim */
3339 IF p_applied_ps_id = -4
3340 THEN
3341 l_check_amount := (p_amount_applied * -1);
3342 ELSE
3343 l_check_amount := (p_amount_due_remaining - p_amount_applied);
3344 END IF;
3345 arp_util.debug('l_check_amount = '||l_check_amount);
3346 /* Bug 2751910 - no longer need to cross check the amount */
3347
3348 END IF;
3349 END IF;
3350
3351 arp_util.debug('validate_application_reference ()-');
3352 EXCEPTION
3353 WHEN invalid_claim THEN
3354 IF p_secondary_application_ref_id IS NOT NULL
3355 THEN
3356 FND_MESSAGE.SET_NAME('AR','AR_RW_INVALID_CLAIM_ID');
3357 FND_MESSAGE.SET_TOKEN('CLAIM_ID',p_secondary_application_ref_id);
3358 ELSE
3359 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INVALID_CLAIM_NUM');
3360 FND_MESSAGE.SET_TOKEN('CLAIM_NUM',p_application_ref_num);
3361 END IF;
3362 FND_MSG_PUB.Add;
3363 p_return_status := FND_API.G_RET_STS_ERROR;
3364 RAISE;
3365 WHEN others THEN
3366 p_return_status := FND_API.G_RET_STS_ERROR;
3367 arp_util.debug('EXCEPTION :validate_application_reference ()-'||SQLERRM);
3368 raise;
3369 END;
3370
3371 PROCEDURE Validate_misc_receipt(
3372 p_receipt_number IN VARCHAR2,
3373 p_receipt_method_id IN NUMBER,
3374 p_state IN VARCHAR2,
3375 p_receipt_date IN DATE,
3376 p_gl_date IN DATE,
3377 p_deposit_date IN DATE,
3378 p_amount IN NUMBER,
3379 p_orig_receivables_trx_id IN NUMBER,
3380 p_receivables_trx_id IN NUMBER,
3381 p_distribution_set_id IN OUT NOCOPY NUMBER,
3382 p_orig_vat_tax_id IN NUMBER,
3383 p_vat_tax_id IN NUMBER,
3384 p_tax_rate IN OUT NOCOPY NUMBER,
3385 p_tax_amount IN NUMBER,
3386 p_reference_num IN VARCHAR2,
3387 p_orig_reference_id IN NUMBER,
3388 p_reference_id IN NUMBER,
3389 p_reference_type IN VARCHAR2,
3390 p_remittance_bank_account_id IN NUMBER,
3391 p_anticipated_clearing_date IN DATE,
3392 p_currency_code IN VARCHAR2,
3393 p_exchange_rate_type IN VARCHAR2,
3394 p_exchange_rate IN NUMBER,
3395 p_exchange_date IN DATE,
3396 p_doc_sequence_value IN NUMBER,
3397 p_return_status OUT NOCOPY VARCHAR2
3398 )
3399 IS
3400
3401 l_receipt_date_return_status VARCHAR2(1);
3402 l_gl_date_return_status VARCHAR2(1);
3403 l_deposit_date_return_status VARCHAR2(1);
3404 l_rcpt_md_return_status VARCHAR2(1);
3405 l_amount_return_status VARCHAR2(1);
3406 l_currency_return_status VARCHAR2(1);
3407 l_doc_seq_return_status VARCHAR2(1);
3408 l_dup_return_status VARCHAR2(1);
3409 l_activity_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
3410 l_tax_id_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
3411 l_tax_rate_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
3412 l_tax_rate NUMBER;
3413 l_tax_validate_flag VARCHAR2(1);
3414 l_reference_valid VARCHAR2(1);
3415 l_ref_id_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
3416 l_ref_type_return_status VARCHAR2(1) DEFAULT FND_API.G_RET_STS_SUCCESS;
3417 BEGIN
3418 IF PG_DEBUG in ('Y', 'C') THEN
3419 arp_util.debug('Validate_misc_receipt()+ ');
3420 END IF;
3421
3422 p_return_status := FND_API.G_RET_STS_SUCCESS;
3423
3424 --Validate receipt_date
3425
3426 Validate_Receipt_Date(p_receipt_date,
3427 l_receipt_date_return_status);
3428 IF PG_DEBUG in ('Y', 'C') THEN
3429 arp_util.debug('Validate_misc_receipt: ' || 'l_receipt_date_return_status : '||l_receipt_date_return_status);
3430 END IF;
3431
3432 --Validate gl_date
3433
3434 Validate_Gl_Date(p_gl_date,
3435 l_gl_date_return_status);
3436 IF PG_DEBUG in ('Y', 'C') THEN
3437 arp_util.debug('Validate_misc_receipt: ' || 'l_gl_date_return_status : '||l_gl_date_return_status);
3438 END IF;
3439
3440 --Validate deposit_date
3441
3442 Validate_Deposit_Date(p_deposit_date,
3443 l_deposit_date_return_status);
3444 IF PG_DEBUG in ('Y', 'C') THEN
3445 arp_util.debug('Validate_misc_receipt: ' || 'l_deposit_date_return_status : '||l_deposit_date_return_status);
3446 END IF;
3447
3448
3449 --Validate Receipt_method
3450 Validate_Receipt_Method(p_receipt_method_id,
3451 p_remittance_bank_account_id,
3452 p_receipt_date,
3453 p_currency_code,
3454 p_state,
3455 'MISC',
3456 l_rcpt_md_return_status);
3457 IF PG_DEBUG in ('Y', 'C') THEN
3458 arp_util.debug('Validate_misc_receipt: ' || 'l_rcpt_md_return_status : '||l_rcpt_md_return_status);
3459 END IF;
3460
3461 --Validate document sequence value
3462
3463 IF(NVL(ar_receipt_lib_pvt.pg_profile_doc_seq, 'N') = 'N' ) AND
3464 p_doc_sequence_value IS NOT NULL
3465 THEN
3466 l_doc_seq_return_status := FND_API.G_RET_STS_ERROR ;
3467 FND_MESSAGE.SET_NAME('AR','AR_RAPI_DOC_SEQ_VAL_INVALID');
3468 FND_MSG_PUB.Add;
3469 END IF;
3470
3471 --Validate currency and exchange rate info.
3472 IF p_currency_code <> arp_global.functional_currency OR
3473 p_exchange_rate_type IS NOT NULL OR
3474 p_exchange_rate IS NOT NULL OR
3475 p_exchange_date IS NOT NULL
3476 THEN
3477 Validate_currency(p_currency_code,
3478 p_exchange_rate_type,
3479 p_exchange_rate,
3480 p_exchange_date,
3481 l_currency_return_status);
3482 END IF;
3483 IF PG_DEBUG in ('Y', 'C') THEN
3484 arp_util.debug('Validate_misc_receipt: ' || 'l_currency_return_status : '||l_currency_return_status);
3485 END IF;
3486
3487 IF p_receipt_number IS NOT NULL AND
3488 p_amount IS NOT NULL
3489 THEN
3490 val_duplicate_receipt(p_receipt_number,
3491 p_receipt_date,
3492 p_amount,
3493 'MISC',
3494 null,
3495 l_dup_return_status );
3496 END IF;
3497
3498 --Validate the activity on the misc receipt.
3499 --Also default the distribution_set_id.
3500 IF PG_DEBUG in ('Y', 'C') THEN
3501 arp_util.debug('Validate_misc_receipt: ' || 'Validating the activity ');
3502 END IF;
3503 IF p_receivables_trx_id IS NOT NULL THEN
3504
3505 --CC Chargeback logic
3506 BEGIN
3507 SELECT rt.default_acctg_distribution_set
3508 INTO p_distribution_set_id
3509 FROM ar_receivables_trx rt
3510 WHERE rt.receivables_trx_id = p_receivables_trx_id
3511 AND rt.type in
3512 ('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND','CC_CHARGEBACK')
3513 AND nvl(rt.status, 'A') = 'A'
3514 AND p_receipt_date >= nvl(rt.start_date_active, p_receipt_date)
3515 AND p_receipt_date <= nvl(rt.end_date_active, p_receipt_date);
3516 EXCEPTION
3517 WHEN no_data_found THEN
3518 IF p_orig_receivables_trx_id IS NULL THEN
3519 l_activity_return_status := FND_API.G_RET_STS_ERROR ;
3520 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_INVALID');
3521 FND_MSG_PUB.Add;
3522 ELSE
3523 l_activity_return_status := FND_API.G_RET_STS_ERROR ;
3524 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_TRX_ID_INVALID');
3525 FND_MSG_PUB.Add;
3526 END IF;
3527 END;
3528 ELSE
3529 l_activity_return_status := FND_API.G_RET_STS_ERROR ;
3530 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_TRX_ID_NULL');
3531 FND_MSG_PUB.Add;
3532 END IF;
3533
3534
3535 --Validate vat_tax_id
3536 IF PG_DEBUG in ('Y', 'C') THEN
3537 arp_util.debug('Validate_misc_receipt: ' || 'Validating vat_tax_id');
3538 END IF;
3539
3540 IF arp_global.sysparam.accounting_method = 'ACCRUAL'
3541 THEN
3542 IF p_vat_tax_id IS NOT NULL THEN
3543 BEGIN
3544 SELECT percentage_rate
3545 INTO l_tax_rate
3546 FROM zx_sco_rates vt
3547 WHERE p_receipt_date between
3548 nvl(vt.effective_from, p_receipt_date)
3549 and nvl(vt.effective_to, p_receipt_date)
3550 AND (vt.tax_class = decode(sign(p_amount), 1, 'OUTPUT',
3551 0, 'OUTPUT',-1, 'INPUT') OR vt.tax_class IS NULL) -- Added condition --> vt.tax_class IS NULL to handle (bug 8648248)
3552 AND vt.tax_rate_id = p_vat_tax_id; -- the Miscellaneous Receipt creation through Standard API
3553 /* Bug 5955921 - Replaced the obsoleted ar_vat_tax with zx_sco_rates
3554 SELECT tax_rate, validate_flag
3555 INTO l_tax_rate, l_tax_validate_flag
3556 FROM ar_vat_tax vt
3557 WHERE p_receipt_date between
3558 nvl(vt.start_date, p_receipt_date)
3559 and nvl(vt.end_date, p_receipt_date)
3560 AND vt.set_of_books_id = arp_global.set_of_books_id
3561 AND vt.tax_class = decode(sign(p_amount), 1, 'O', 0, 'O', -1, 'I')
3562 AND vt.enabled_flag='Y'
3563 AND vt.tax_type <> 'TAX_GROUP'
3564 AND vt.tax_type <> 'LOCATION'
3565 AND vt.tax_type <> 'SALES_TAX'
3566 AND vt.displayed_flag='Y'
3567 AND vt.vat_tax_id = p_vat_tax_id; */
3568
3569 EXCEPTION
3570 WHEN no_data_found THEN
3571 IF p_orig_vat_tax_id IS NOT NULL THEN
3572 l_tax_id_return_status := FND_API.G_RET_STS_ERROR ;
3573 FND_MESSAGE.SET_NAME('AR','AR_RAPI_VAT_TAX_ID_INVALID');
3574 FND_MSG_PUB.Add;
3575 ELSE
3576 l_tax_id_return_status := FND_API.G_RET_STS_ERROR ;
3577 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TAX_CODE_INVALID');
3578 FND_MSG_PUB.Add;
3579 END IF;
3580 END;
3581
3582 --In case where user has specified the tax_rate/tax amount , we need to verify
3583 --the adhoc flag on the tax_code as well as the profile option
3584 --'Tax: Allow Ad Hoc Tax Changes'
3585 --to see if he alowed to do so.
3586 --p_tax_rate is the user specified tax rate or the tax rate derived from the
3587 --user specified tax amount and the receipt amount.
3588 /* 4743228 - use ZX profile instead */
3589 IF p_tax_rate IS NOT NULL THEN
3590 /* Bug 5955921 l_tax_validate_flag = 'N' OR */
3591 IF fnd_profile.value('ZX_ALLOW_TAX_UPDATE') = 'N'
3592 THEN
3593 l_tax_rate_return_status := FND_API.G_RET_STS_ERROR;
3594 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TAX_RATE_INVALID');
3595 FND_MSG_PUB.Add;
3596 END IF;
3597 ELSE
3598
3599 IF arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
3600 p_tax_rate := l_tax_rate;
3601 END IF;
3602
3603 END IF;
3604
3605
3606 ELSE
3607 --this is the case where we dont have any vat_tax_id, but the user has specified the
3608 --tax rate as a input parameter.
3609 IF p_tax_rate IS NOT NULL THEN
3610 l_tax_id_return_status := FND_API.G_RET_STS_ERROR ;
3611 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TAX_RATE_INVALID');
3612 FND_MSG_PUB.Add;
3613 END IF;
3614 END IF;
3615
3616 ELSE
3617 --if the accounting is cash basis.
3618 IF p_vat_tax_id IS NOT NULL THEN
3619 --raise error as no tax accounting is done for cash basis in misc receipt.
3620 l_tax_id_return_status := FND_API.G_RET_STS_ERROR ;
3621 FND_MESSAGE.SET_NAME('AR','AR_RAPI_VAT_TAX_ID_INVALID');
3622 FND_MSG_PUB.Add;
3623 END IF;
3624
3625 IF p_tax_rate IS NOT NULL THEN
3626 l_tax_id_return_status := FND_API.G_RET_STS_ERROR ;
3627 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TAX_RATE_INVALID');
3628 FND_MSG_PUB.Add;
3629 END IF;
3630
3631 END IF;
3632
3633
3634
3635 --Validate reference_id, reference_type
3636 IF PG_DEBUG in ('Y', 'C') THEN
3637 arp_util.debug('Validate_misc_receipt: ' || 'Validation for reference id begins');
3638 END IF;
3639 IF p_reference_type IS NOT NULL THEN
3640 IF p_reference_id IS NOT NULL THEN
3641 BEGIN
3642 IF (p_reference_type = 'PAYMENT') THEN
3643 --get from ap_checks.
3644 select 'y'
3645 into l_reference_valid
3646 from ap_checks
3647 where check_id = p_reference_id /* Bug fix 2982212 */
3648 and ce_bank_acct_use_id = p_remittance_bank_account_id;/*bug8449826*/
3649 /*and bank_account_id = p_remittance_bank_account_id;*/
3650 ELSIF (p_reference_type = 'PAYMENT_BATCH' ) THEN
3651 --
3652 select 'y'
3653 into l_reference_valid
3654 from ap_invoice_selection_criteria isc
3655 where isc.checkrun_id = p_reference_id /* Bug fix 2982212 */
3656 and bank_account_id = p_remittance_bank_account_id;
3657 ELSIF (p_reference_type = 'RECEIPT' ) THEN
3658 --
3659 select 'y'
3660 into l_reference_valid
3661 from ar_cash_receipts
3662 where cash_receipt_id = p_reference_id
3663 and remit_bank_acct_use_id = p_remittance_bank_account_id;
3664 ELSIF (p_reference_type = 'REMITTANCE' ) THEN
3665 --
3666 select 'y'
3667 into l_reference_valid
3668 from ar_batches
3669 where batch_id = p_reference_id /* Bug fix 2982212 */
3670 and type = 'REMITTANCE'
3671 and remit_bank_acct_use_id = p_remittance_bank_account_id;
3672 /* Bug 4112494 - added for credit memo refunds */
3673 ELSIF (p_reference_type = 'CREDIT_MEMO' ) THEN
3674 --
3675 select 'y'
3676 into l_reference_valid
3677 from ra_customer_trx
3678 where customer_trx_id = p_reference_id;
3679 ELSE
3680 --the reference_type is invalid, raise error.
3681 l_ref_type_return_status := FND_API.G_RET_STS_ERROR;
3682 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_TYPE_INVALID');
3683 FND_MSG_PUB.Add;
3684
3685 END IF;
3686 EXCEPTION
3687 WHEN no_data_found THEN
3688 IF p_orig_reference_id IS NULL THEN
3689 l_ref_id_return_status := FND_API.G_RET_STS_ERROR;
3690 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_NUM_INVALID');
3691 FND_MSG_PUB.Add;
3692 ELSE
3693 l_ref_id_return_status := FND_API.G_RET_STS_ERROR;
3694 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_ID_INVALID');
3695 FND_MSG_PUB.Add;
3696 END IF;
3697 END;
3698 ELSE
3699 --the reference_id is null, raise error.
3700 IF p_reference_num IS NOT NULL THEN
3701 --this would happen if the reference_id could not be
3702 --derived from reference number
3703 l_ref_id_return_status := FND_API.G_RET_STS_ERROR;
3704 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_NUM_INVALID');
3705 FND_MSG_PUB.Add;
3706 ELSE
3707 l_ref_id_return_status := FND_API.G_RET_STS_ERROR;
3708 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_ID_NULL');
3709 FND_MSG_PUB.Add;
3710 END IF;
3711
3712 END IF;
3713
3714 ELSE
3715 --reference_type is null
3716
3717 IF p_orig_reference_id IS NULL AND
3718 p_reference_num IS NULL
3719 THEN
3720 null;
3721 ELSE
3722 --this means any one of the orig_reference_id, reference_num or
3723 --reference_id is specified, so a null reference type should
3724 --raise an error.
3725 l_ref_type_return_status := FND_API.G_RET_STS_ERROR;
3726 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REF_TYPE_NULL');
3727 FND_MSG_PUB.Add;
3728 END IF;
3729 END IF;
3730
3731
3732 IF (l_receipt_date_return_status = FND_API.G_RET_STS_ERROR) OR
3733 (l_gl_date_return_status = FND_API.G_RET_STS_ERROR) OR
3734 (l_deposit_date_return_status = FND_API.G_RET_STS_ERROR) OR
3735 (l_rcpt_md_return_status = FND_API.G_RET_STS_ERROR) OR
3736 (l_amount_return_status = FND_API.G_RET_STS_ERROR) OR
3737 (l_currency_return_status = FND_API.G_RET_STS_ERROR) OR
3738 (l_doc_seq_return_status = FND_API.G_RET_STS_ERROR) OR
3739 (l_dup_return_status = FND_API.G_RET_STS_ERROR) OR
3740 (l_activity_return_status = FND_API.G_RET_STS_ERROR) OR
3741 (l_tax_id_return_status = FND_API.G_RET_STS_ERROR) OR
3742 (l_ref_id_return_status = FND_API.G_RET_STS_ERROR) OR
3743 (l_ref_type_return_status = FND_API.G_RET_STS_ERROR)
3744 THEN
3745 p_return_status := FND_API.G_RET_STS_ERROR;
3746 END IF;
3747 IF PG_DEBUG in ('Y', 'C') THEN
3748 arp_util.debug('Validate_misc_receipt return status :'||p_return_status);
3749 END IF;
3750
3751 EXCEPTION
3752 WHEN others THEN
3753 IF PG_DEBUG in ('Y', 'C') THEN
3754 arp_util.debug('EXCEPTION : Validate_misc_receipt()');
3755 END IF;
3756 raise;
3757
3758 END Validate_misc_receipt;
3759
3760 PROCEDURE validate_prepay_amount(
3761 p_receipt_number IN VARCHAR2,
3762 p_cash_receipt_id IN NUMBER,
3763 p_applied_ps_id IN NUMBER,
3764 p_receivable_application_id IN NUMBER,
3765 p_refund_amount IN NUMBER,
3766 p_return_status OUT NOCOPY VARCHAR2
3767 ) IS
3768 l_cash_receipt_id NUMBER;
3769 l_prepay_amount NUMBER;
3770 BEGIN
3771
3772 arp_util.debug('Validate prepay amount (+)');
3773 p_return_status := FND_API.G_RET_STS_SUCCESS;
3774
3775 l_cash_receipt_id := p_cash_receipt_id;
3776
3777 IF p_receipt_number IS NOT NULL THEN
3778 ar_receipt_lib_pvt.Default_cash_receipt_id(l_cash_receipt_id ,
3779 p_receipt_number ,
3780 p_return_status);
3781 END IF;
3782
3783 IF l_cash_receipt_id IS NOT NULL THEN
3784
3785 SELECT sum(nvl(amount_applied,0))
3786 INTO l_prepay_amount
3787 FROM ar_receivable_applications
3788 WHERE cash_receipt_id = p_cash_receipt_id
3789 AND applied_payment_schedule_id = p_applied_ps_id
3790 AND display = 'Y'
3791 AND status = 'OTHER ACC';
3792
3793 END IF;
3794
3795 IF p_receivable_application_id IS NOT NULL THEN
3796
3797 SELECT sum(nvl(amount_applied,0))
3798 INTO l_prepay_amount
3799 FROM ar_receivable_applications
3800 WHERE receivable_application_id = p_receivable_application_id
3801 AND display = 'Y'
3802 AND applied_payment_schedule_id = p_applied_ps_id
3803 AND status = 'OTHER ACC';
3804
3805 END IF;
3806
3807 IF nvl(p_refund_amount,0) > l_prepay_amount THEN
3808 --raise error X validation failed
3809 FND_MESSAGE.SET_NAME('AR','AR_RAPI_PREPAY_AMT_LESS');
3810 FND_MSG_PUB.Add;
3811 p_return_status := FND_API.G_RET_STS_ERROR ;
3812 END IF;
3813
3814 arp_util.debug('Validate prepay amount (-)');
3815
3816 EXCEPTION
3817 WHEN others THEN
3818 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
3819 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
3820 FND_MSG_PUB.Add;
3821 p_return_status := FND_API.G_RET_STS_ERROR ;
3822 arp_util.debug('EXCEPTION :Validate prepay amount '||SQLERRM);
3823 END;
3824
3825 PROCEDURE validate_payment_type(
3826 p_receipt_number IN VARCHAR2,
3827 p_cash_receipt_id IN NUMBER,
3828 p_receivable_application_id IN NUMBER,
3829 p_payment_action IN VARCHAR2,
3830 p_return_status OUT NOCOPY VARCHAR2
3831 ) IS
3832
3833 l_payment_type_code VARCHAR2(30);
3834 l_cash_receipt_id NUMBER;
3835
3836 BEGIN
3837 arp_util.debug('Validate payment Type (+)');
3838 p_return_status := FND_API.G_RET_STS_SUCCESS;
3839
3840 l_cash_receipt_id := p_cash_receipt_id;
3841
3842 IF p_receipt_number IS NOT NULL THEN
3843 ar_receipt_lib_pvt.Default_cash_receipt_id(l_cash_receipt_id ,
3844 p_receipt_number ,
3845 p_return_status);
3846 END IF;
3847
3848 IF l_cash_receipt_id IS NOT NULL THEN
3849
3850 SELECT NVL(payment_channel_code,'CASH')
3851 INTO l_payment_type_code
3852 FROM ar_receipt_methods arm,
3853 ar_cash_receipts cr
3854 WHERE cr.receipt_method_id = arm.receipt_method_id
3855 AND cr.cash_receipt_id=l_cash_receipt_id;
3856
3857
3858 ELSIF p_receivable_application_id is not null THEN
3859
3860 SELECT NVL(payment_channel_code,'CASH')
3861 INTO l_payment_type_code
3862 FROM ar_receipt_methods arm,
3863 ar_cash_receipts cr,
3864 ar_receivable_applications app
3865 WHERE cr.receipt_method_id = arm.receipt_method_id
3866 AND app.cash_receipt_id=cr.cash_receipt_id
3867 AND app.receivable_application_id = p_receivable_application_id;
3868
3869 END IF;
3870
3871 IF (NVL(l_payment_type_code,'CASH') <> 'CREDIT_CARD') THEN
3872
3873 IF p_payment_action = 'CREATE_RCPT' THEN
3874
3875 FND_MESSAGE.set_name ('AR','AR_RAPI_PREPAY_ONLYFOR_CC');
3876 FND_MSG_PUB.Add;
3877 p_return_status := FND_API.G_RET_STS_ERROR ;
3878
3879 ELSIF p_payment_action = 'REFUND_RCPT' THEN
3880
3881 FND_MESSAGE.set_name ('AR','AR_RW_CCR_NOT_CC_RECEIPT');
3882 FND_MSG_PUB.Add;
3883 p_return_status := FND_API.G_RET_STS_ERROR ;
3884
3885 END IF;
3886 END IF;
3887
3888 arp_util.debug('Validate payment Type (-)');
3889
3890 EXCEPTION
3891 WHEN others THEN
3892 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
3893 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
3894 FND_MSG_PUB.Add;
3895 p_return_status := FND_API.G_RET_STS_ERROR ;
3896 arp_util.debug('EXCEPTION :Validate payment type '||SQLERRM);
3897 END;
3898
3899 -- Bug 2270809
3900 -- If a claim investigation app, then check the claim status.
3901 -- If not OPEN,CANCELLED,COMPLETE then disallow unapply
3902
3903 PROCEDURE validate_claim_unapply(
3904 p_secondary_app_ref_id IN VARCHAR2,
3905 p_invoice_ps_id IN NUMBER,
3906 p_customer_trx_id IN NUMBER,
3907 p_cash_receipt_id IN NUMBER,
3908 p_receipt_number IN VARCHAR2,
3909 p_amount_applied IN NUMBER,
3910 p_cancel_claim_flag IN VARCHAR2,
3911 p_return_status OUT NOCOPY VARCHAR2)
3912 IS
3913 l_claim_status VARCHAR2(30);
3914 l_msg_count NUMBER;
3915 l_msg_data VARCHAR2(2000);
3916 l_secondary_app_ref_id NUMBER;
3917 l_claim_reason_code_id NUMBER;
3918 l_claim_reason_name VARCHAR2(100);
3919 l_claim_number VARCHAR2(30);
3920
3921
3922 BEGIN
3923 IF PG_DEBUG in ('Y', 'C') THEN
3924 arp_util.debug('ar_receipt_val_pvt.validate_claim_unapply()+');
3925 END IF;
3926
3927 l_secondary_app_ref_id := p_secondary_app_ref_id;
3928
3929 arp_process_application.update_claim(
3930 p_claim_id => l_secondary_app_ref_id
3931 , p_invoice_ps_id => p_invoice_ps_id
3932 , p_customer_trx_id => p_customer_trx_id
3933 , p_amount => 0
3934 , p_amount_applied => p_amount_applied
3935 , p_apply_date => SYSDATE
3936 , p_cash_receipt_id => p_cash_receipt_id
3937 , p_receipt_number => p_receipt_number
3938 , p_action_type => 'U'
3939 , x_claim_reason_code_id => l_claim_reason_code_id
3940 , x_claim_reason_name => l_claim_reason_name
3941 , x_claim_number => l_claim_number
3942 , x_return_status => p_return_status
3943 , x_msg_count => l_msg_count
3944 , x_msg_data => l_msg_data);
3945 IF PG_DEBUG in ('Y', 'C') THEN
3946 arp_util.debug('ar_receipt_val_pvt.validate_claim_unapply()-');
3947 END IF;
3948 EXCEPTION
3949 WHEN others THEN
3950 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
3951 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
3952 FND_MSG_PUB.Add;
3953 p_return_status := FND_API.G_RET_STS_ERROR ;
3954 IF PG_DEBUG in ('Y', 'C') THEN
3955 arp_util.debug('EXCEPTION :ar_receipt_val_pvt.validate_claim_unapply '||SQLERRM);
3956 END IF;
3957 END validate_claim_unapply;
3958
3959 PROCEDURE validate_open_receipt_info(
3960 p_cash_receipt_id IN NUMBER
3961 , p_open_cash_receipt_id IN NUMBER
3962 , p_apply_date IN DATE
3963 , p_apply_gl_date IN DATE
3964 , p_cr_gl_date IN DATE
3965 , p_open_cr_gl_date IN DATE
3966 , p_cr_date IN DATE
3967 , p_amount_applied IN NUMBER
3968 , p_other_amount_applied IN NUMBER
3969 , p_receipt_currency IN VARCHAR2
3970 , p_open_receipt_currency IN VARCHAR2
3971 , p_cr_customer_id IN NUMBER
3972 , p_open_cr_customer_id IN NUMBER
3973 , p_unapplied_cash IN NUMBER
3974 , p_called_from IN VARCHAR2
3975 , p_return_status OUT NOCOPY VARCHAR2
3976 ) IS
3977 l_rct_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3978 l_gl_date_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3979 l_act_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3980 l_amt_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3981 l_cust_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3982 l_cur_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3983 l_apply_date_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3984 l_func_currency gl_sets_of_books.currency_code%TYPE;
3985 l_activity_name ar_receivables_trx.name%TYPE;
3986 l_ccid NUMBER;
3987
3988 BEGIN
3989
3990 IF PG_DEBUG in ('Y', 'C') THEN
3991 arp_util.debug('ar_receipt_val_pvt.validate_open_receipt_info()+');
3992 END IF;
3993
3994 -- Bug 3235089: checks for activity existing and accounting set up
3995 BEGIN
3996 SELECT name, code_combination_id
3997 INTO l_activity_name, l_ccid
3998 FROM ar_receivables_trx
3999 WHERE receivables_trx_id = -16;
4000
4001 IF l_ccid IS NULL THEN
4002 FND_MESSAGE.SET_NAME('AR','AR_RW_NO_NETTING_ACCOUNT');
4003 FND_MSG_PUB.Add;
4004 l_act_return_status := FND_API.G_RET_STS_ERROR;
4005 END IF;
4006 EXCEPTION
4007 WHEN NO_DATA_FOUND THEN
4008 FND_MESSAGE.SET_NAME('AR','AR_RW_MISSING_NETTING_ACTIVITY');
4009 FND_MSG_PUB.Add;
4010 l_act_return_status := FND_API.G_RET_STS_ERROR;
4011 END;
4012
4013 IF p_cash_receipt_id = p_open_cash_receipt_id THEN
4014 FND_MESSAGE.SET_NAME('AR','AR_RW_NET_RCT_APPLY_SELF');
4015 FND_MSG_PUB.Add;
4016 l_rct_return_status := FND_API.G_RET_STS_ERROR;
4017 END IF;
4018
4019 -- Check if valid paying customer
4020 IF NOT arp_trx_validate.validate_paying_customer(
4021 p_paying_customer_id => p_cr_customer_id,
4022 p_trx_date => p_cr_date,
4023 p_bill_to_customer_id => p_open_cr_customer_id,
4024 p_ct_prev_paying_customer_id => p_cr_customer_id,
4025 p_currency_code => p_receipt_currency,
4026 p_pay_unrelated_invoices_flag => arp_global.sysparam.pay_unrelated_invoices_flag,
4027 p_ct_prev_trx_date => p_cr_date) THEN
4028 FND_MESSAGE.SET_NAME('AR','ARTA_PYMNT_UNRELATED_CUST');
4029 FND_MSG_PUB.Add;
4030 l_cust_return_status := FND_API.G_RET_STS_ERROR;
4031 END IF;
4032
4033
4034 validate_apply_date(p_apply_date,
4035 p_apply_date, /* Bug fix 3286069 */
4036 p_cr_date,
4037 l_apply_date_return_status
4038 );
4039
4040 IF PG_DEBUG in ('Y', 'C') THEN
4041 arp_util.debug('Validate_open_receipt_info: ' || 'Apply date return status :'||l_apply_date_return_status);
4042 END IF;
4043
4044 validate_apply_gl_date(p_apply_gl_date ,
4045 p_cr_gl_date ,
4046 p_cr_gl_date ,
4047 l_gl_date_return_status
4048 );
4049
4050 IF p_apply_gl_date < p_open_cr_gl_date THEN
4051 FND_MESSAGE.SET_NAME('AR','AR_RW_GL_DATE_BEFORE_OPEN_REC');
4052 FND_MSG_PUB.Add;
4053 l_gl_date_return_status := FND_API.G_RET_STS_ERROR;
4054 END IF;
4055
4056
4057 IF PG_DEBUG in ('Y', 'C') THEN
4058 arp_util.debug('Validate_open_receipt_info: ' || 'Apply gl_date return status :'||l_gl_date_return_status);
4059 arp_util.debug('Validate_open_receipt_info: ' || 'p_unapplied_cash :'||p_unapplied_cash);
4060 arp_util.debug('Validate_open_receipt_info: ' || 'p_amount_applied :'||p_amount_applied);
4061 arp_util.debug('Validate_open_receipt_info: ' || 'p_called_from :'||p_called_from);
4062 END IF;
4063 -- validate amount applied
4064 IF p_amount_applied IS NULL THEN
4065 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
4066 FND_MSG_PUB.Add;
4067 l_amt_return_status := FND_API.G_RET_STS_ERROR;
4068
4069 ELSE
4070 -- Bug 2897244 - receipt overapplication not checked if called
4071 -- from ARXRWAPP or PostBatch
4072 IF NVL(p_called_from,'RAPI') NOT IN ('ARXRWAPP','ARCAPB')
4073 THEN
4074 IF (nvl(p_unapplied_cash,0)- p_amount_applied) < 0 THEN
4075 FND_MESSAGE.SET_NAME('AR','AR_RW_AMOUNT_LESS_THAN_APP');
4076 FND_MSG_PUB.Add;
4077 l_amt_return_status := FND_API.G_RET_STS_ERROR;
4078 END IF;
4079 END IF;
4080 IF ((SIGN(p_other_amount_applied * -1) <> SIGN(p_amount_applied)) OR
4081 (ABS(p_amount_applied) > ABS(p_other_amount_applied)) ) THEN
4082 FND_MESSAGE.SET_NAME('AR','AR_RW_NET_OPEN_AMT_INC');
4083 FND_MSG_PUB.Add;
4084 l_amt_return_status := FND_API.G_RET_STS_ERROR;
4085 END IF;
4086
4087 END IF;
4088
4089 IF PG_DEBUG in ('Y', 'C') THEN
4090 arp_util.debug('Validate_open_receipt_info: ' || 'Amount return status :'||l_amt_return_status);
4091 END IF;
4092
4093 SELECT sob.currency_code
4094 INTO l_func_currency
4095 FROM ar_system_parameters sp,
4096 gl_sets_of_books sob
4097 WHERE sp.set_of_books_id = sob.set_of_books_id;
4098
4099 IF (p_receipt_currency <> p_open_receipt_currency) THEN
4100 FND_MESSAGE.SET_NAME('AR','AR_RW_NET_DIFF_RCT_CURR');
4101 FND_MSG_PUB.Add;
4102 l_cur_return_status := FND_API.G_RET_STS_ERROR;
4103 END IF;
4104
4105 IF l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
4106 l_rct_return_status <> FND_API.G_RET_STS_SUCCESS OR
4107 l_act_return_status <> FND_API.G_RET_STS_SUCCESS OR
4108 l_amt_return_status <> FND_API.G_RET_STS_SUCCESS OR
4109 l_cur_return_status <> FND_API.G_RET_STS_SUCCESS OR
4110 l_cust_return_status <> FND_API.G_RET_STS_SUCCESS OR
4111 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4112
4113 p_return_status := FND_API.G_RET_STS_ERROR;
4114 END IF;
4115
4116 IF PG_DEBUG in ('Y', 'C') THEN
4117 arp_util.debug('ar_receipt_val_pvt.validate_open_receipt_info()-');
4118 END IF;
4119 EXCEPTION
4120 WHEN others THEN
4121 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
4122 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
4123 FND_MSG_PUB.Add;
4124 p_return_status := FND_API.G_RET_STS_ERROR ;
4125 IF PG_DEBUG in ('Y', 'C') THEN
4126 arp_util.debug('EXCEPTION :ar_receipt_val_pvt.validate_open_receipt_info '||SQLERRM);
4127 END IF;
4128
4129 END validate_open_receipt_info;
4130
4131 PROCEDURE validate_unapp_open_receipt(
4132 p_applied_cash_receipt_id IN NUMBER
4133 , p_amount_applied IN NUMBER
4134 , p_return_status IN OUT NOCOPY VARCHAR2
4135 ) IS
4136
4137 l_cr_amount NUMBER;
4138 l_amount_applied NUMBER;
4139 BEGIN
4140
4141 IF PG_DEBUG in ('Y', 'C') THEN
4142 arp_util.debug('ar_receipt_val_pvt.validate_unapp_open_receipt()+');
4143 END IF;
4144
4145 -- Check if unapplication will send the applied-to receipt negative
4146
4147 SELECT amount
4148 INTO l_cr_amount
4149 FROM ar_cash_receipts
4150 WHERE cash_receipt_id = p_applied_cash_receipt_id;
4151
4152 SELECT NVL(SUM(amount_applied),0)
4153 INTO l_amount_applied
4154 FROM ar_receivable_applications
4155 WHERE cash_receipt_id = p_applied_cash_receipt_id
4156 AND display = 'Y';
4157
4158 IF PG_DEBUG in ('Y', 'C') THEN
4159 arp_util.debug('validate_unapp_open_receipt-Receipt amount: '||l_cr_amount);
4160 arp_util.debug('validate_unapp_open_receipt-Applied amount: '||l_amount_applied);
4161 END IF;
4162
4163 IF (l_cr_amount - l_amount_applied - p_amount_applied) < 0 THEN
4164 FND_MESSAGE.set_name('AR','AR_RW_NET_UNAPP_OVERAPP');
4165 FND_MSG_PUB.Add;
4166 p_return_status := FND_API.G_RET_STS_ERROR;
4167 END IF;
4168
4169 IF PG_DEBUG in ('Y', 'C') THEN
4170 arp_util.debug('ar_receipt_val_pvt.validate_unapp_open_receipt()-');
4171 END IF;
4172
4173 EXCEPTION
4174 WHEN others THEN
4175 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
4176 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',SQLERRM);
4177 FND_MSG_PUB.Add;
4178 p_return_status := FND_API.G_RET_STS_ERROR ;
4179 IF PG_DEBUG in ('Y', 'C') THEN
4180 arp_util.debug('EXCEPTION :ar_receipt_val_pvt.validate_unapp_open_receipt '||SQLERRM);
4181 END IF;
4182
4183 END validate_unapp_open_receipt;
4184
4185 PROCEDURE validate_llca_insert_ad(
4186 p_cash_receipt_id IN NUMBER
4187 ,p_customer_trx_id IN NUMBER
4188 ,p_customer_trx_line_id IN NUMBER
4189 ,p_cr_unapp_amount IN NUMBER
4190 ,p_llca_type IN VARCHAR2
4191 ,p_group_id IN VARCHAR2
4192 ,p_line_amount IN NUMBER
4193 ,p_tax_amount IN NUMBER
4194 ,p_freight_amount IN NUMBER
4195 ,p_charges_amount IN NUMBER
4196 ,p_line_discount IN NUMBER
4197 ,p_tax_discount IN NUMBER
4198 ,p_freight_discount IN NUMBER
4199 ,p_amount_applied IN NUMBER
4200 ,p_amount_applied_from IN NUMBER
4201 ,p_trans_to_receipt_rate IN NUMBER
4202 ,p_invoice_currency_code IN VARCHAR2
4203 ,p_receipt_currency_code IN VARCHAR2
4204 ,p_earned_discount IN NUMBER
4205 ,p_unearned_discount IN NUMBER
4206 ,p_max_discount IN NUMBER
4207 ,p_line_items_original IN NUMBER
4208 ,p_line_items_remaining IN NUMBER
4209 ,p_tax_original IN NUMBER
4210 ,p_tax_remaining IN NUMBER
4211 ,p_freight_original IN NUMBER
4212 ,p_freight_remaining IN NUMBER
4213 ,p_rec_charges_charged IN NUMBER
4214 ,p_rec_charges_remaining IN NUMBER
4215 ,p_attribute_category IN VARCHAR2
4216 ,p_attribute1 IN VARCHAR2
4217 ,p_attribute2 IN VARCHAR2
4218 ,p_attribute3 IN VARCHAR2
4219 ,p_attribute4 IN VARCHAR2
4220 ,p_attribute5 IN VARCHAR2
4221 ,p_attribute6 IN VARCHAR2
4222 ,p_attribute7 IN VARCHAR2
4223 ,p_attribute8 IN VARCHAR2
4224 ,p_attribute9 IN VARCHAR2
4225 ,p_attribute10 IN VARCHAR2
4226 ,p_attribute11 IN VARCHAR2
4227 ,p_attribute12 IN VARCHAR2
4228 ,p_attribute13 IN VARCHAR2
4229 ,p_attribute14 IN VARCHAR2
4230 ,p_attribute15 IN VARCHAR2
4231 ,p_comments IN VARCHAR2
4232 ,p_return_status OUT NOCOPY VARCHAR2
4233 ,p_msg_count OUT NOCOPY NUMBER
4234 ,p_msg_data OUT NOCOPY VARCHAR2
4235 ) IS
4236
4237 cursor all_lines_in_grp (p_cust_trx_id in number,
4238 p_grp_id in number) is
4239 select to_char(line.line_number) apply_to,
4240 line.customer_trx_line_id LINE_ID,
4241 nvl(line.source_data_key4,0) GROUP_ID ,
4242 nvl(line.amount_due_remaining,0) line_to_apply,
4243 nvl(tax.amount_due_remaining,0) tax_to_apply
4244 from ra_customer_trx_lines line,
4245 (select link_to_cust_trx_line_id,
4246 line_type,
4247 sum(nvl(amount_due_original,0)) amount_due_original,
4248 sum(nvl(amount_due_remaining,0)) amount_due_remaining
4249 from ra_customer_trx_lines
4250 where customer_trx_id = p_cust_trx_id -- Bug 7241703 Added condition
4251 and nvl(line_type,'TAX') = 'TAX'
4252 group by link_to_cust_trx_line_id,line_type
4253 ) tax
4254 where line.customer_Trx_id = p_cust_trx_id
4255 and line.line_type = 'LINE'
4256 and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
4257 and line.source_data_key4 = p_grp_id;
4258
4259 cursor all_lines_cur (p_cust_trx_id in number) is
4260 select to_char(line.line_number) apply_to,
4261 line.customer_trx_line_id line_id,
4262 nvl(line.source_data_key4,0) group_id ,
4263 nvl(line.amount_due_remaining,0) line_to_apply,
4264 nvl(tax.amount_due_remaining,0) tax_to_apply
4265 from ra_customer_trx_lines line,
4266 (select link_to_cust_trx_line_id,
4267 line_type,
4268 sum(nvl(amount_due_original,0)) amount_due_original,
4269 sum(nvl(amount_due_remaining,0)) amount_due_remaining
4270 from ra_customer_trx_lines
4271 where customer_trx_id = p_cust_trx_id -- Bug 7241703 Added condition
4272 and nvl(line_type,'TAX') = 'TAX'
4273 group by link_to_cust_trx_line_id,line_type
4274 ) tax
4275 where line.customer_Trx_id = p_cust_trx_id
4276 and line.line_type = 'LINE'
4277 and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
4278 order by line_number;
4279
4280 cursor gt_lines_cur (p_cust_trx_id in number) is
4281 select * from ar_llca_trx_lines_gt
4282 where customer_trx_id = p_cust_trx_id;
4283
4284 ll_msg_data varchar2(2000);
4285 ll_return_status varchar2(1);
4286 ll_msg_count number;
4287 l_gt_count NUMBER :=0;
4288
4289 l_ctl_id number;
4290 llca_ra_rec ar_receivable_applications%rowtype;
4291
4292 -- LLCA - LINE LEVEL
4293 l_rowid rowid;
4294 l_group_id ra_customer_trx_lines.source_data_key4%type;
4295 l_line_amount_remaining NUMBER;
4296 l_line_tax_remaining NUMBER;
4297 l_line_number NUMBER;
4298 l_calc_tot_amount_app NUMBER;
4299 l_calc_amount_app_from NUMBER; -- Amount in Receipt Currency
4300 l_calc_line_per NUMBER;
4301 l_calc_line_amount NUMBER;
4302 l_calc_tax_amount NUMBER;
4303 l_calc_freight_amount NUMBER;
4304 l_cr_unapp_bal NUMBER;
4305 l_dflex_val_return_status VARCHAR2(1); --bug7311231
4306 l_attribute_rec ar_receipt_api_pub.attribute_rec_type; --bug7311231
4307 BEGIN
4308 IF PG_DEBUG in ('Y', 'C') THEN
4309 arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_ad()+');
4310 END IF;
4311
4312 p_return_status := FND_API.G_RET_STS_SUCCESS;
4313
4314 IF PG_DEBUG in ('Y', 'C') THEN
4315 arp_util.debug('Initialization the GT tables ...');
4316 arp_util.debug('DONE In the default initialization the GT tables ...');
4317 END IF;
4318 /*
4319 -- Initialize the Sys Parameters /INV PS /REC PS / Copy Trx lines into GT
4320 arp_process_det_pkg.initialization (p_customer_trx_id,
4321 p_cash_receipt_id,
4322 p_return_status,
4323 p_msg_data,
4324 p_msg_count); */
4325 IF PG_DEBUG in ('Y', 'C') THEN
4326 arp_util.debug('Initialization Return_status = '||p_return_status);
4327 END IF;
4328
4329 -- LLCA Summary
4330 IF p_llca_type = 'S'
4331 THEN
4332
4333 IF PG_DEBUG in ('Y', 'C') THEN
4334 arp_util.debug('Summary Level application... ');
4335 arp_util.debug('p_line_amount ... '||p_line_amount);
4336 arp_util.debug('p_tax_amount ... '||p_tax_amount);
4337 arp_util.debug('p_freight_amount ... '||p_freight_amount);
4338 arp_util.debug('p_charges_amount ... '||p_charges_amount);
4339 arp_util.debug('p_line_discount ... '||p_line_discount);
4340 arp_util.debug('p_tax_discount ... '||p_tax_discount);
4341 arp_util.debug('p_freight_discount ... '||p_freight_discount);
4342 arp_util.debug('Trans_to_receipt_rate='||p_trans_to_receipt_rate);
4343 arp_util.debug('Invoice Currency Code='||p_invoice_currency_code);
4344 arp_util.debug('Receipt Currency Code='||p_receipt_currency_code);
4345 arp_util.debug('............................ ');
4346 END IF;
4347
4348 --bug7311231.
4349 ar_ll_rcv_summary_pkg.insert_row(
4350 x_cash_receipt_id => p_cash_receipt_id,
4351 x_customer_trx_id => p_customer_trx_id,
4352 x_lin => p_line_amount,
4353 x_tax => p_tax_amount,
4354 x_frt => p_freight_amount,
4355 x_chg => p_charges_amount,
4356 x_lin_dsc => p_line_discount,
4357 x_tax_dsc => p_tax_discount,
4358 x_frt_dsc => p_freight_discount,
4359 x_created_by_module => 'RAPI'
4360 ,x_inv_curr_code => p_invoice_currency_code
4361 ,x_inv_to_rct_rate => p_trans_to_receipt_rate
4362 ,x_rct_curr_code => p_receipt_currency_code
4363 ,x_attribute_category => p_attribute_category
4364 ,x_attribute1 => p_attribute1
4365 ,x_attribute2 => p_attribute2
4366 ,x_attribute3 => p_attribute3
4367 ,x_attribute4 => p_attribute4
4368 ,x_attribute5 => p_attribute5
4369 ,x_attribute6 => p_attribute6
4370 ,x_attribute7 => p_attribute7
4371 ,x_attribute8 => p_attribute8
4372 ,x_attribute9 => p_attribute9
4373 ,x_attribute10 => p_attribute10
4374 ,x_attribute11 => p_attribute11
4375 ,x_attribute12 => p_attribute12
4376 ,x_attribute13 => p_attribute13
4377 ,x_attribute14 => p_attribute14
4378 ,x_attribute15 => p_attribute15
4379 );
4380 -- Group Level
4381 ELSIF p_llca_type = 'G'
4382 THEN
4383
4384 IF PG_DEBUG in ('Y', 'C') THEN
4385 arp_util.debug('Group Level application... ');
4386 END IF;
4387
4388 If p_group_id is NOT NULL
4389 THEN
4390 -- Customer Select Specify Group
4391 l_cr_unapp_bal := Nvl(p_cr_unapp_amount,0);
4392
4393 For line_grp in All_lines_in_grp (p_customer_trx_id,p_group_id)
4394 LOOP
4395 l_calc_tot_amount_app := Nvl(line_grp.line_to_apply,0)
4396 + Nvl(line_grp.tax_to_apply,0);
4397
4398 If Nvl(l_cr_unapp_bal,0) = 0
4399 Then
4400 l_calc_tot_amount_app := 0;
4401 l_calc_line_amount := 0;
4402 l_calc_tax_amount := 0;
4403 Else
4404 If Nvl(l_calc_tot_amount_app,0) > Nvl(l_cr_unapp_bal,0) Then
4405 -- Unapplied balance is non zero and > than amount applied, so default the
4406 -- Unapplied balance to total amount applied and calculate the line amount as
4407 -- amount_applied * (line_bal/(line_bal+tax_bal)) and tax_amount =
4408 -- amount_applied - line_amount
4409
4410 l_calc_tot_amount_app := arp_util.CurrRound(l_cr_unapp_bal
4411 ,p_invoice_currency_code);
4412
4413 Select decode ( ( Nvl(line_grp.line_to_apply,0)
4414 / (Nvl(line_grp.line_to_apply,0)
4415 + Nvl(line_grp.tax_to_apply,0)
4416 )
4417 ),0,1,
4418 ( Nvl(line_grp.line_to_apply,0)
4419 / (Nvl(line_grp.line_to_apply,0)
4420 + Nvl(line_grp.tax_to_apply,0)
4421 )
4422 )
4423 )
4424 into l_calc_line_per
4425 from dual;
4426
4427 l_calc_line_amount := arp_util.CurrRound((l_calc_tot_amount_app
4428 * l_calc_line_per),p_invoice_currency_code);
4429 l_calc_tax_amount := arp_util.CurrRound((Nvl(l_calc_tot_amount_app,0)
4430 - Nvl(l_calc_line_amount,0))
4431 ,p_invoice_currency_code);
4432 Else
4433 l_calc_line_amount := Nvl(line_grp.line_to_apply,0);
4434 l_calc_tax_amount := Nvl(line_grp.tax_to_apply,0);
4435 End If;
4436
4437 -- Reset the balance
4438 l_cr_unapp_bal := Nvl(l_cr_unapp_bal,0) - (Nvl(l_calc_line_amount,0)
4439 + Nvl(l_calc_tax_amount,0));
4440 End If;
4441
4442 ar_activity_details_pkg.insert_row (
4443 x_rowid => l_rowid,
4444 x_cash_receipt_id => p_cash_receipt_id,
4445 x_customer_trx_line_id => line_grp.line_id,
4446 x_allocated_receipt_amount => Nvl(l_calc_tot_amount_app,0),
4447 x_amount => Nvl(l_calc_line_amount,0),
4448 x_tax => Nvl(l_calc_tax_amount,0),
4449 x_line_discount => '',
4450 x_tax_discount => '',
4451 x_line_balance => line_grp.line_to_apply,
4452 x_tax_balance => Nvl(line_grp.tax_to_apply,0),
4453 x_apply_to => line_grp.apply_to,
4454 x_attribute_category => p_attribute_category,
4455 x_attribute1 => p_attribute1,
4456 x_attribute2 => p_attribute2,
4457 x_attribute3 => p_attribute3,
4458 x_attribute4 => p_attribute4,
4459 x_attribute5 => p_attribute5,
4460 x_attribute6 => p_attribute6,
4461 x_attribute7 => p_attribute7,
4462 x_attribute8 => p_attribute8,
4463 x_attribute9 => p_attribute9,
4464 x_attribute10 => p_attribute10,
4465 x_attribute11 => p_attribute11,
4466 x_attribute12 => p_attribute12,
4467 x_attribute13 => p_attribute13,
4468 x_attribute14 => p_attribute14,
4469 x_attribute15 => p_attribute15,
4470 x_comments => p_comments,
4471 x_group_id => line_grp.group_id,
4472 x_object_version_number => 1,
4473 x_created_by_module => 'RAPI',
4474 x_reference1 => '',
4475 x_reference2 => '',
4476 x_reference3 => '',
4477 x_reference4 => '',
4478 x_reference5 => ''
4479 );
4480 End Loop;
4481 End If;
4482 -- Line Level
4483 ELSIF p_llca_type = 'L'
4484 THEN
4485 IF PG_DEBUG in ('Y', 'C') THEN
4486 arp_util.debug('Line Level application... ');
4487 END IF;
4488
4489 select count(*)
4490 into l_gt_count
4491 from ar_llca_trx_lines_gt
4492 where customer_trx_id = p_customer_trx_id
4493 and rownum = 1;
4494
4495 -- All Lines
4496 IF nvl(l_gt_count,0) = 0
4497 THEN
4498 IF PG_DEBUG in ('Y', 'C') THEN
4499 arp_util.debug('All Lines... ');
4500 END IF;
4501 -- Customer Select All lines
4502 l_cr_unapp_bal := Nvl(p_cr_unapp_amount,0);
4503
4504 For All_lines_row in All_lines_cur (p_customer_trx_id)
4505 LOOP
4506
4507 l_calc_tot_amount_app := Nvl(All_lines_row.line_to_apply,0)
4508 + Nvl(All_lines_row.tax_to_apply,0);
4509 /* Bug 5438627 : Amount in Receipt Currency */
4510 If p_trans_to_receipt_rate <> 0 then
4511 l_calc_amount_app_from := arp_util.CurrRound((Nvl(l_calc_tot_amount_app,0) *
4512 nvl(p_trans_to_receipt_rate,0)), p_receipt_currency_code);
4513 Else
4514 l_calc_amount_app_from := Nvl(l_calc_tot_amount_app,0);
4515 End If;
4516
4517 If Nvl(l_cr_unapp_bal,0) = 0
4518 Then
4519 l_calc_tot_amount_app := 0;
4520 l_calc_amount_app_from := 0;
4521 l_calc_line_amount := 0;
4522 l_calc_tax_amount := 0;
4523 Else
4524 IF PG_DEBUG in ('Y', 'C') THEN
4525 arp_util.debug('Customer_trx_line_id => '||to_char(All_lines_row.line_id));
4526 arp_util.debug('l_calc_tot_amount_app -> '||to_char(l_calc_tot_amount_app));
4527 arp_util.debug('l_calc_amount_app_from-> '||to_char(l_calc_amount_app_from));
4528 arp_util.debug('l_cr_unapp_bal -> '||to_char(l_cr_unapp_bal));
4529 END IF;
4530 If Nvl(l_calc_amount_app_from,0) > Nvl(l_cr_unapp_bal,0) Then
4531 -- Unapplied balance is non zero and > than amount applied, so default the
4532 -- Unapplied balance to total amount applied and calculate the line amount as
4533 -- amount_applied * (line_bal/(line_bal+tax_bal)) and tax_amount =
4534 -- amount_applied - line_amount
4535
4536 IF PG_DEBUG in ('Y', 'C') THEN
4537 arp_util.debug('l_calc_amount_app_from > l_cr_unapp_bal' );
4538 arp_util.debug('Resetting... amount applied and buckets');
4539 END IF;
4540
4541 l_calc_amount_app_from := Nvl(l_cr_unapp_bal,0);
4542
4543 If p_trans_to_receipt_rate <> 0 then
4544 l_calc_tot_amount_app := arp_util.CurrRound((l_cr_unapp_bal/p_trans_to_receipt_rate)
4545 ,p_invoice_currency_code);
4546 Else
4547 l_calc_tot_amount_app := arp_util.CurrRound(l_cr_unapp_bal
4548 ,p_invoice_currency_code);
4549 End If;
4550
4551
4552 Select decode ( ( Nvl(All_lines_row.line_to_apply,0)
4553 / (Nvl(All_lines_row.line_to_apply,0)
4554 + Nvl(All_lines_row.tax_to_apply,0)
4555 )
4556 ),0,1,
4557 ( Nvl(All_lines_row.line_to_apply,0)
4558 / (Nvl(All_lines_row.line_to_apply,0)
4559 + Nvl(All_lines_row.tax_to_apply,0)
4560 )
4561 )
4562 )
4563 into l_calc_line_per
4564 from dual;
4565
4566 l_calc_line_amount := arp_util.CurrRound((l_calc_tot_amount_app
4567 * l_calc_line_per),p_invoice_currency_code);
4568 l_calc_tax_amount := arp_util.CurrRound((Nvl(l_calc_tot_amount_app,0)
4569 - Nvl(l_calc_line_amount,0))
4570 ,p_invoice_currency_code);
4571 Else
4572 l_calc_line_amount := Nvl(All_lines_row.line_to_apply,0);
4573 l_calc_tax_amount := Nvl(All_lines_row.tax_to_apply,0);
4574 End If;
4575
4576 -- Reset the balance
4577 l_cr_unapp_bal := Nvl(l_cr_unapp_bal,0) - Nvl(l_calc_amount_app_from,0);
4578
4579 IF PG_DEBUG in ('Y', 'C') THEN
4580 arp_util.debug('l_calc_line_amount -> '||to_char(l_calc_line_amount));
4581 arp_util.debug('l_calc_tax_amount -> '||to_char(l_calc_tax_amount));
4582 arp_util.debug('l_cr_unapp_bal (R) => '||to_char(l_cr_unapp_bal));
4583 END IF;
4584 End If;
4585
4586 ar_activity_details_pkg.insert_row (
4587 x_rowid => l_rowid,
4588 x_cash_receipt_id => p_cash_receipt_id,
4589 x_customer_trx_line_id => All_lines_row.line_id,
4590 x_allocated_receipt_amount => Nvl(l_calc_amount_app_from,0),
4591 x_amount => Nvl(l_calc_line_amount,0),
4592 x_tax => Nvl(l_calc_tax_amount,0),
4593 x_line_discount => '',
4594 x_tax_discount => '',
4595 x_line_balance => All_lines_row.line_to_apply,
4596 x_tax_balance => Nvl(All_lines_row.tax_to_apply,0),
4597 x_apply_to => All_lines_row.apply_to,
4598 x_attribute_category => p_attribute_category,
4599 x_attribute1 => p_attribute1,
4600 x_attribute2 => p_attribute2,
4601 x_attribute3 => p_attribute3,
4602 x_attribute4 => p_attribute4,
4603 x_attribute5 => p_attribute5,
4604 x_attribute6 => p_attribute6,
4605 x_attribute7 => p_attribute7,
4606 x_attribute8 => p_attribute8,
4607 x_attribute9 => p_attribute9,
4608 x_attribute10 => p_attribute10,
4609 x_attribute11 => p_attribute11,
4610 x_attribute12 => p_attribute12,
4611 x_attribute13 => p_attribute13,
4612 x_attribute14 => p_attribute14,
4613 x_attribute15 => p_attribute15,
4614 x_comments => p_comments,
4615 x_group_id => All_lines_row.group_id,
4616 x_object_version_number => 1,
4617 x_created_by_module => 'RAPI',
4618 x_reference1 => '',
4619 x_reference2 => '',
4620 x_reference3 => '',
4621 x_reference4 => '',
4622 x_reference5 => ''
4623 );
4624 End Loop;
4625 -- SPECIFIED LINES
4626 ELSIF Nvl(l_gt_count,0) > 0
4627 THEN
4628 ---
4629 IF PG_DEBUG in ('Y', 'C') THEN
4630 arp_util.debug('Specified one or more lines in PLSQL table... ');
4631 END IF;
4632
4633 -- Calculate the line level amounts
4634 l_cr_unapp_bal := Nvl(p_cr_unapp_amount,0);
4635
4636 --bug7311231, start
4637 IF PG_DEBUG in ('Y', 'C') THEN
4638 arp_util.debug('Validating Value passed for Descriptive Flexfield at line level.');
4639 END IF;
4640 For sp_lines_row in gt_lines_cur(p_customer_trx_id)
4641 LOOP
4642 l_attribute_rec.attribute_category := sp_lines_row.attribute_category;
4643 l_attribute_rec.attribute1 := sp_lines_row.attribute1;
4644 l_attribute_rec.attribute2 := sp_lines_row.attribute2;
4645 l_attribute_rec.attribute3 := sp_lines_row.attribute3;
4646 l_attribute_rec.attribute4 := sp_lines_row.attribute4;
4647 l_attribute_rec.attribute5 := sp_lines_row.attribute5;
4648 l_attribute_rec.attribute6 := sp_lines_row.attribute6;
4649 l_attribute_rec.attribute7 := sp_lines_row.attribute7;
4650 l_attribute_rec.attribute8 := sp_lines_row.attribute8;
4651 l_attribute_rec.attribute9 := sp_lines_row.attribute9;
4652 l_attribute_rec.attribute10 := sp_lines_row.attribute10;
4653 l_attribute_rec.attribute11 := sp_lines_row.attribute11;
4654 l_attribute_rec.attribute12 := sp_lines_row.attribute12;
4655 l_attribute_rec.attribute13 := sp_lines_row.attribute13;
4656 l_attribute_rec.attribute14 := sp_lines_row.attribute14;
4657 l_attribute_rec.attribute15 := sp_lines_row.attribute15;
4658
4659 ar_receipt_lib_pvt.Validate_Desc_Flexfield(
4660 l_attribute_rec,
4661 'AR_ACTIVITY_DETAILS',
4662 l_dflex_val_return_status
4663 );
4664
4665 If l_dflex_val_return_status <> FND_API.G_RET_STS_SUCCESS Then
4666 p_return_status := 'X';
4667 ar_receipt_lib_pvt.populate_errors_gt(
4668 p_customer_trx_id => p_customer_trx_id,
4669 p_customer_trx_line_id => sp_lines_row.customer_trx_line_id,
4670 p_error_message =>
4671 'Flexfield Validation at Line Level Failed.',
4672 p_invalid_value => NULL);
4673 End If;
4674 End Loop;
4675
4676 If p_return_status = 'X' Then
4677 IF PG_DEBUG in ('Y', 'C') THEN
4678 arp_util.debug('Failed: Validation of Descriptive Flexfield at line level.');
4679 END IF;
4680 return;
4681 End If;
4682 --bug7311231, End.
4683
4684 For sp_lines_row in gt_lines_cur(p_customer_trx_id)
4685 LOOP
4686 BEGIN
4687 select nvl(line.source_data_key4,0) group_id,
4688 nvl(line.amount_due_remaining,0),
4689 nvl(tax.amount_due_remaining,0)
4690 into
4691 l_group_id,
4692 l_line_amount_remaining,
4693 l_line_tax_remaining
4694 from ra_customer_trx_lines line,
4695 (select link_to_cust_trx_line_id,
4696 line_type,
4697 sum(nvl(amount_due_original,0)) amount_due_original,
4698 sum(nvl(amount_due_remaining,0)) amount_due_remaining
4699 from ra_customer_trx_lines
4700 where customer_trx_id = sp_lines_row.customer_trx_id -- Bug 7241703 Added condition
4701 and nvl(line_type,'TAX') = 'TAX'
4702 group by link_to_cust_trx_line_id,line_type
4703 ) tax
4704 where line.customer_Trx_id = sp_lines_row.customer_trx_id
4705 and line.customer_trx_line_id = sp_lines_row.customer_trx_line_id
4706 and line.line_type = 'LINE'
4707 and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+);
4708 EXCEPTION
4709 WHEN NO_DATA_FOUND THEN
4710 p_return_status := FND_API.G_RET_STS_ERROR ;
4711 FND_MESSAGE.SET_NAME( 'AR','AR_RAPI_TRX_LINE_ID_INVALID');
4712 FND_MSG_PUB.ADD;
4713 RAISE;
4714 WHEN others THEN
4715 IF PG_DEBUG in ('Y', 'C') THEN
4716 arp_util.debug('' || 'EXCEPTION: validate_llac_insert_ad()');
4717 END IF;
4718 RAISE;
4719 END;
4720
4721 /* Bug 5438627 : Amount in Receipt Currency */
4722 If p_trans_to_receipt_rate <> 0 then
4723 l_calc_amount_app_from := arp_util.CurrRound((Nvl(sp_lines_row.amount_applied,0) *
4724 nvl(p_trans_to_receipt_rate,0)), p_receipt_currency_code);
4725 Else
4726 l_calc_amount_app_from := Nvl(sp_lines_row.amount_applied,0);
4727 End If;
4728
4729 IF PG_DEBUG in ('Y', 'C') THEN
4730 arp_util.debug('Amount_applied -> '||to_char(sp_lines_row.amount_applied));
4731 arp_util.debug('l_calc_amount_app_from-> '||to_char(l_calc_amount_app_from));
4732 arp_util.debug('l_cr_unapp_bal -> '||to_char(l_cr_unapp_bal));
4733 END IF;
4734
4735 -- Check the Unapplied balance
4736 /* Acctd amount will be validated via validate_amount_applied_from, so
4737 the below validation is not required.
4738 If Nvl(l_calc_amount_app_from,0) > Nvl(l_cr_unapp_bal,0)
4739 Then
4740 IF PG_DEBUG in ('Y', 'C') THEN
4741 arp_util.debug('l_calc_amount_app_from > l_cr_unapp_bal' );
4742 arp_util.debug('Raise an error... amount applied and buckets');
4743 END IF;
4744 p_return_status := FND_API.G_RET_STS_ERROR;
4745 fnd_message.set_name ('AR','AR_RW_APP_NEG_UNAPP');
4746 fnd_msg_pub.Add;
4747 End If; */
4748
4749 -- Reset the balance
4750 l_cr_unapp_bal := Nvl(l_cr_unapp_bal,0) - Nvl(l_calc_amount_app_from,0);
4751
4752 IF PG_DEBUG in ('Y', 'C') THEN
4753 arp_util.debug('l_calc_line_amount -> '||to_char(l_calc_line_amount));
4754 arp_util.debug('l_calc_tax_amount -> '||to_char(l_calc_tax_amount));
4755 arp_util.debug('l_cr_unapp_bal (R) => '||to_char(l_cr_unapp_bal));
4756 END IF;
4757 --- End If;
4758 --bug7311231, Picking the flexfield value from ar_llca_trx_lines_gt, for each line.
4759 ar_activity_details_pkg.insert_row(
4760 x_rowid => l_rowid,
4761 x_cash_receipt_id => p_cash_receipt_id,
4762 x_customer_trx_line_id => sp_lines_row.customer_trx_line_id,
4763 x_allocated_receipt_amount => Nvl(l_calc_amount_app_from,0),
4764 x_amount => Nvl(sp_lines_row.line_amount,0),
4765 x_tax => Nvl(sp_lines_row.tax_amount,0),
4766 x_line_discount => Nvl(sp_lines_row.line_discount,0),
4767 x_tax_discount => Nvl(sp_lines_row.tax_discount,0),
4768 x_line_balance => l_line_amount_remaining,
4769 x_tax_balance => l_line_tax_remaining,
4770 x_apply_to => sp_lines_row.line_number,
4771 x_attribute_category => sp_lines_row.attribute_category,
4772 x_attribute1 => sp_lines_row.attribute1,
4773 x_attribute2 => sp_lines_row.attribute2,
4774 x_attribute3 => sp_lines_row.attribute3,
4775 x_attribute4 => sp_lines_row.attribute4,
4776 x_attribute5 => sp_lines_row.attribute5,
4777 x_attribute6 => sp_lines_row.attribute6,
4778 x_attribute7 => sp_lines_row.attribute7,
4779 x_attribute8 => sp_lines_row.attribute8,
4780 x_attribute9 => sp_lines_row.attribute9,
4781 x_attribute10 => sp_lines_row.attribute10,
4782 x_attribute11 => sp_lines_row.attribute11,
4783 x_attribute12 => sp_lines_row.attribute12,
4784 x_attribute13 => sp_lines_row.attribute13,
4785 x_attribute14 => sp_lines_row.attribute14,
4786 x_attribute15 => sp_lines_row.attribute15,
4787 x_comments => p_comments,
4788 x_group_id => l_group_id,
4789 x_object_version_number => 1,
4790 x_created_by_module => 'RAPI',
4791 x_reference1 => '',
4792 x_reference2 => '',
4793 x_reference3 => '',
4794 x_reference4 => '',
4795 x_reference5 => ''
4796 );
4797 End Loop;
4798 End IF; /* End of l_gt_count */
4799
4800 -- Check for freight amount
4801 If NVL(p_freight_amount,0) <> 0
4802 THEN
4803 ar_ll_rcv_summary_pkg.insert_frt_rows(
4804 x_cash_receipt_id => p_cash_receipt_id,
4805 x_customer_trx_id => p_customer_trx_id,
4806 x_frt => p_freight_amount,
4807 x_frt_dsc => p_freight_discount,
4808 x_created_by_module => 'RAPI'
4809 ,x_inv_curr_code => p_invoice_currency_code
4810 ,x_inv_to_rct_rate => p_trans_to_receipt_rate
4811 ,x_rct_curr_code => p_receipt_currency_code
4812 ,x_comments => NULL
4813 );
4814 END If;
4815
4816 End If; /* End of LLCA TYPE */
4817
4818 IF PG_DEBUG in ('Y', 'C') THEN
4819 arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_ad()-');
4820 END IF;
4821 EXCEPTION
4822 WHEN others THEN
4823 IF PG_DEBUG in ('Y', 'C') THEN
4824 arp_util.debug('EXCEPTION: ar_receipt_val_pvt.validate_llac_insert_ad()');
4825 END IF;
4826 RAISE;
4827 END validate_llca_insert_ad;
4828
4829 PROCEDURE validate_llca_insert_app(
4830 p_cash_receipt_id IN NUMBER
4831 ,p_customer_trx_id IN NUMBER
4832 ,p_disc_earn_allowed IN NUMBER
4833 ,p_disc_max_allowed IN NUMBER
4834 ,p_return_status OUT NOCOPY VARCHAR2
4835 ,p_msg_count OUT NOCOPY NUMBER
4836 ,p_msg_data OUT NOCOPY VARCHAR2
4837 ) IS
4838 cursor rcv_lines_cur (p_cust_trx_id in number, p_cash_rec_id in number) is
4839 select
4840 trx_lines.line_type,
4841 trx_lines.source_data_key1 sdk1,
4842 trx_lines.source_data_key2 sdk2,
4843 trx_lines.source_data_key3 sdk3,
4844 trx_lines.source_data_key4 sdk4,
4845 trx_lines.source_data_key5 sdk5,
4846 trx_lines.customer_Trx_line_id ctl_id,
4847 --
4848 rcv_lines.amount lin,
4849 rcv_lines.tax tax,
4850 rcv_lines.freight frt,
4851 rcv_lines.charges chg,
4852 --
4853 --
4854 rcv_lines.line_discount lin_disc,
4855 rcv_lines.tax_discount tax_disc,
4856 rcv_lines.freight_discount frt_disc,
4857 0 chg_disc,
4858 --
4859 rcv_lines.allocated_receipt_amount
4860 from ar_activity_details rcv_lines,
4861 ra_customer_trx_lines trx_lines
4862 where trx_lines.customer_trx_id = p_cust_trx_id
4863 and rcv_lines.cash_receipt_id = p_cash_rec_id
4864 and nvl(rcv_lines.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- Bug 7241111
4865 and trx_lines.line_type = 'LINE'
4866 and rcv_lines.customer_trx_line_id = trx_lines.customer_trx_line_id;
4867
4868 cursor rcv_frtchg_cur (pf_ct_id in number, pf_cr_id in number) is
4869 select trx_lines.line_type,
4870 sum(Nvl(rcv_lines.amount,0)) lin,
4871 sum(Nvl(rcv_lines.tax,0)) tax,
4872 sum(Nvl(rcv_lines.freight,0)) frt,
4873 sum(Nvl(rcv_lines.charges,0)) chg,
4874 sum(Nvl(rcv_lines.line_discount,0)) lin_disc,
4875 sum(Nvl(rcv_lines.tax_discount,0)) tax_disc,
4876 sum(NVl(rcv_lines.freight_discount,0)) frt_disc,
4877 sum(Nvl(rcv_lines.allocated_receipt_amount,0)) allocated
4878 from ar_Activity_details rcv_lines,
4879 ra_customer_trx_lines_all trx_lines
4880 where trx_lines.customer_trx_id = pf_ct_id
4881 and rcv_lines.cash_receipt_id = pf_cr_id
4882 and nvl(rcv_lines.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- Bug 7241111
4883 and trx_lines.line_type in ('FREIGHT','CHARGES')
4884 and rcv_lines.customer_trx_line_id = trx_lines.customer_trx_line_id
4885 group by trx_lines.line_type;
4886
4887
4888 ll_msg_data varchar2(2000);
4889 ll_return_status varchar2(1);
4890 ll_msg_count number;
4891 l_demon NUMBER;
4892 l_calc_ed_line_disc NUMBER :=0;
4893 l_calc_ued_line_disc NUMBER :=0;
4894 l_calc_ed_tax_disc NUMBER :=0;
4895 l_calc_ued_tax_disc NUMBER :=0;
4896 l_calc_ed_frt_disc NUMBER :=0;
4897 l_calc_ued_frt_disc NUMBER :=0;
4898 lf_calc_ed_frt_disc NUMBER :=0;
4899 lf_calc_ued_frt_disc NUMBER :=0;
4900 Begin
4901 IF PG_DEBUG in ('Y', 'C') THEN
4902 arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app()+');
4903 END IF;
4904 ll_return_status := FND_API.G_RET_STS_SUCCESS;
4905 -- Execute the application and populate the Line wise details into RA GT
4906 For rcv_lines_row in rcv_lines_cur (p_customer_trx_id, p_cash_receipt_id)
4907 LOOP
4908
4909 l_demon := p_disc_earn_allowed + (p_disc_max_allowed - p_disc_earn_allowed);
4910 If l_demon <> 0
4911 Then
4912 l_calc_ed_line_disc := (rcv_lines_row.lin_disc / l_demon) * p_disc_earn_allowed;
4913 l_calc_ued_line_disc := rcv_lines_row.lin_disc - l_calc_ed_line_disc;
4914 l_calc_ed_tax_disc := (rcv_lines_row.tax_disc / l_demon) * p_disc_earn_allowed;
4915 l_calc_ued_tax_disc := rcv_lines_row.tax_disc - l_calc_ed_tax_disc;
4916 l_calc_ed_frt_disc := (rcv_lines_row.frt_disc / l_demon) * p_disc_earn_allowed;
4917 l_calc_ued_frt_disc := rcv_lines_row.frt_disc - l_calc_ed_frt_disc;
4918 End If;
4919
4920 IF PG_DEBUG in ('Y', 'C') THEN
4921 arp_util.debug('Calling Application Execute for Lines and Tax');
4922 arp_util.debug('Customer Trx Line ID => '||rcv_lines_row.ctl_id);
4923 arp_util.debug('Line Amount => '||rcv_lines_row.lin);
4924 arp_util.debug('Tax Amount => '||rcv_lines_row.Tax);
4925 arp_util.debug('Freight Amount => '||rcv_lines_row.frt);
4926 arp_util.debug('Charges Amount => '||rcv_lines_row.Chg);
4927 arp_util.debug('Line Earned Discount => '||l_calc_ed_line_disc);
4928 arp_util.debug('Tax Earned Discount => '||l_calc_ed_tax_disc);
4929 arp_util.debug('Frt Earned Discount => '||l_calc_ed_frt_disc);
4930 arp_util.debug('Line UNearned Discount=> '||l_calc_ued_line_disc);
4931 arp_util.debug('Tax UNearned Discount=> '||l_calc_ued_tax_disc);
4932 arp_util.debug('Frt UNearned Discount=> '||l_calc_ued_frt_disc);
4933 END IF;
4934 arp_process_det_pkg.application_execute(
4935 p_app_level =>'LINE',
4936 p_source_data_key1 =>rcv_lines_row.sdk1,
4937 p_source_data_key2 =>rcv_lines_row.sdk2,
4938 p_source_data_key3 =>rcv_lines_row.sdk3,
4939 p_source_data_key4 =>rcv_lines_row.sdk4,
4940 p_source_data_key5 =>rcv_lines_row.sdk5,
4941 p_ctl_id =>rcv_lines_row.ctl_id,
4942 --
4943 p_line_applied =>rcv_lines_row.lin,
4944 p_tax_applied =>rcv_lines_row.tax,
4945 p_freight_applied =>rcv_lines_row.frt,
4946 p_charges_applied =>rcv_lines_row.chg,
4947 --
4948 p_line_ediscounted =>l_calc_ed_line_disc,
4949 p_tax_ediscounted =>l_calc_ed_tax_disc,
4950 p_freight_ediscounted =>l_calc_ed_frt_disc,
4951 p_charges_ediscounted =>0,
4952 --
4953 p_line_uediscounted =>l_calc_ued_line_disc,
4954 p_tax_uediscounted =>l_calc_ued_tax_disc,
4955 p_freight_uediscounted =>l_calc_ued_frt_disc,
4956 p_charges_uediscounted =>0,
4957 --
4958 x_return_status =>ll_return_status,
4959 x_msg_count =>ll_msg_count,
4960 x_msg_data =>ll_msg_data);
4961 End Loop;
4962
4963 IF PG_DEBUG in ('Y', 'C') THEN
4964 arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app(Line)+');
4965 END IF;
4966
4967 IF ll_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4968 ll_return_status := FND_API.G_RET_STS_ERROR;
4969 ELSE
4970 FOR rcv_frtchg_row in rcv_frtchg_cur (p_customer_trx_id, p_cash_receipt_id)
4971 LOOP
4972 l_demon := p_disc_earn_allowed + (p_disc_max_allowed - p_disc_earn_allowed);
4973 If l_demon <> 0
4974 Then
4975 lf_calc_ed_frt_disc := (rcv_frtchg_row.frt_disc / l_demon) * p_disc_earn_allowed;
4976 lf_calc_ued_frt_disc := rcv_frtchg_row.frt_disc - lf_calc_ed_frt_disc;
4977 End If;
4978 IF PG_DEBUG in ('Y', 'C') THEN
4979 arp_util.debug('Calling Application Execute for Freight Lines ');
4980 arp_util.debug('Line Amount => '||rcv_frtchg_row.lin);
4981 arp_util.debug('Tax Amount => '||rcv_frtchg_row.Tax);
4982 arp_util.debug('Freight Amount => '||rcv_frtchg_row.frt);
4983 arp_util.debug('Charges Amount => '||rcv_frtchg_row.Chg);
4984 arp_util.debug('Frt Earned Discount => '||lf_calc_ed_frt_disc);
4985 arp_util.debug('Frt UNearned Discount=> '||lf_calc_ued_frt_disc);
4986 END IF;
4987 arp_process_det_pkg.application_execute(
4988 p_app_level =>'TRANSACTION',
4989 p_source_data_key1 =>NULL,
4990 p_source_data_key2 =>NULL,
4991 p_source_data_key3 =>NULL,
4992 p_source_data_key4 =>NULL,
4993 p_source_data_key5 =>NULL,
4994 p_ctl_id =>NULL, -- Taxable line id
4995 --
4996 p_line_applied =>rcv_frtchg_row.lin,
4997 p_tax_applied =>rcv_frtchg_row.tax,
4998 p_freight_applied =>rcv_frtchg_row.frt,
4999 p_charges_applied =>rcv_frtchg_row.chg,
5000 --
5001 p_line_ediscounted =>0,
5002 p_tax_ediscounted =>0,
5003 p_freight_ediscounted =>lf_calc_ed_frt_disc,
5004 p_charges_ediscounted =>0,
5005 --
5006 p_line_uediscounted =>0,
5007 p_tax_uediscounted =>0,
5008 p_freight_uediscounted =>lf_calc_ued_frt_disc,
5009 p_charges_uediscounted =>0,
5010 --
5011 x_return_status =>ll_return_status,
5012 x_msg_count =>ll_msg_count,
5013 x_msg_data =>ll_msg_data);
5014 END LOOP;
5015 END IF;
5016 IF PG_DEBUG in ('Y', 'C') THEN
5017 arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app()-');
5018 END IF;
5019
5020 EXCEPTION
5021 WHEN others THEN
5022 IF PG_DEBUG in ('Y', 'C') THEN
5023 arp_util.debug('EXCEPTION: ar_receipt_val_pvt.validate_llac_insert_app()');
5024 END IF;
5025 RAISE;
5026 END validate_llca_insert_app;
5027
5028 END AR_RECEIPT_VAL_PVT;
[Home] [Help]