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