1 PACKAGE BODY ARP_BALANCE_CHECK AS
2 /* $Header: ARBALCHB.pls 120.10.12020000.4 2012/10/31 06:34:02 jatian ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 PG_BAL_CHECK_ENABLED varchar2(1) := NVL(FND_PROFILE.value('AR_ENABLE_JOURNAL_BAL_CHECK'),'Y');
6 PG_LL_BAL_CHECK_ENABLED varchar2(1) := 'Y';
7 /*PG_AUTO_CORRECT_ENABLED varchar2(1) := NVL(FND_PROFILE.value('AR_ENABLE_AUTO_CORRECTION_CHECK'),'Y');*/
8 /* =======================================================================
9 | PROCEDURE Check_Transaction_Balance
10 |
11 | DESCRIPTION
12 | This procedure takes sum of debits and credits for transactions
13 | and tallies that debits equal credits, if not then it sets a
14 | message on the message stack indicating that items are out of
15 | balance.
16 |
17 | PARAMETERS
18 | p_customer_trx_id IN Cash receipt id
19 | p_called_from_api IN Y-api call out
20 * ======================================================================*/
21 PROCEDURE CHECK_TRANSACTION_BALANCE(p_customer_trx_id IN VARCHAR2,
22 p_called_from_api IN VARCHAR2 default 'N') IS
23
24 CURSOR C1(p_cust_trx_id NUMBER) IS
25 SELECT sum(decode(ctlgd.account_class,
26 'REC', nvl(amount,0) * -1,
27 nvl(amount,0))) net_amount,
28 sum(decode(ctlgd.account_class,
29 'REC', nvl(acctd_amount,0) * -1,
30 nvl(acctd_amount,0))) net_acctd_amount,
31 gl_date
32 from ra_cust_trx_line_gl_dist ctlgd
33 where ctlgd.customer_trx_id = p_cust_trx_id
34 and ctlgd.account_set_flag = 'N'
35 and ctlgd.posting_control_id = -3
36 and not exists (select 'x'
37 from ra_customer_trx_lines ctl
38 where ctl.customer_trx_id = p_customer_trx_id
39 and ctl.autorule_complete_flag||'' = 'N'
40 group by ctl.customer_trx_id)
41 group by customer_trx_id, gl_date;
42
43 l_amount NUMBER;
44 l_acctd_amount NUMBER;
45 l_no_balance VARCHAR2(1):= 'N';
46 -- OKL LLCA Bug 6129910
47 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
48 l_msg_data VARCHAR2(2000);
49 l_msg_count NUMBER;
50 l_customer_rec ra_customer_trx%ROWTYPE;
51 l_gen_line_level_bal_flag VARCHAR(1) := 'N';
52 l_complete_flag_db varchar2(1) := 'N';
53 excep_set_org_rem_amt_r12 EXCEPTION;
54
55
56 BEGIN
57
58 IF PG_DEBUG in ('Y', 'C') THEN
59 arp_standard.debug('ARP_BALANCE_CHK.Check_Transaction_Balance (+)' );
60 END IF;
61
62 IF p_customer_trx_id IS NOT NULL THEN
63
64 --- OKL LLCA Bug 6129910
65 l_customer_rec.customer_trx_id := p_customer_trx_id;
66 -- Check the batch source flag
67
68 SELECT NVL(gen_line_level_bal_flag,'N'),
69 NVL(rt.complete_flag,'N')
70 INTO l_gen_line_level_bal_flag,
71 l_complete_flag_db
72 FROM ra_batch_sources ra, ra_customer_trx rt
73 WHERE ra.batch_source_id = rt.batch_source_id
74 AND rt.customer_trx_id = p_customer_trx_id;
75
76 arp_standard.debug('old_complete_flag: '||ARP_PROCESS_HEADER_UPDTE_COVER.g_old_complete_flag);
77 arp_standard.debug('complete_flag_db: '||l_complete_flag_db);
78
79 IF l_gen_line_level_bal_flag = 'Y' AND
80 l_complete_flag_db = 'Y' AND
81 nvl(ARP_PROCESS_HEADER_UPDTE_COVER.g_old_complete_flag,'N') = 'N' THEN
82
83 ARP_DET_DIST_PKG.set_original_rem_amt_r12
84 ( p_customer_trx => l_customer_rec,
85 x_return_status => l_return_status,
86 x_msg_count => l_msg_count,
87 x_msg_data => l_msg_data,
88 p_from_llca => 'Y');
89
90
91 IF l_return_status <> fnd_api.g_ret_sts_success THEN
92 RAISE excep_set_org_rem_amt_r12;
93 END IF;
94
95 END IF;
96 --- OKL LLCA End
97 --bug6762463
98 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
99 FOR c2 in c1(p_customer_trx_id) LOOP
100
101 l_amount := c2.net_amount;
102 l_acctd_amount := c2.net_acctd_amount;
103
104 IF l_amount <> 0 OR l_acctd_amount <> 0 THEN
105 /* The exception could have been raised here, but continuing to
106 print the debug messages */
107 l_no_balance := 'Y' ;
108 END IF;
109 IF PG_DEBUG in ('Y', 'C') THEN
110 arp_standard.debug('GL_DATE : '||to_char(c2.gl_date,'DD-MON-YYYY'));
111 arp_standard.debug('Net Amount :' || l_amount);
112 arp_standard.debug('Net Acctd Amount :' || l_acctd_amount);
113 END IF;
114 END LOOP;
115
116 --------------------------------------------------------
117 --Set the message on the message stack
118 --------------------------------------------------------
119 IF l_no_balance = 'Y' THEN
120
121 IF p_called_from_api = 'Y' THEN
122 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
123 fnd_msg_pub.Add;
124 END IF;
125
126 RAISE out_of_balance;
127
128 END IF;
129
130 ELSE
131 IF PG_DEBUG in ('Y', 'C') THEN
132 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
133 END IF;
134 END IF;
135 END IF; -- p_customer_trx_id is not null , bug6762463
136
137 IF PG_DEBUG in ('Y', 'C') THEN
138 arp_standard.debug('ARP_BALANCE_CHK.Check_Transaction_Balance (-)' );
139 END IF;
140
141 EXCEPTION
142 WHEN out_of_balance THEN
143 IF PG_DEBUG in ('Y', 'C') THEN
144 arp_standard.debug('ARP_BALANCE_CHK.Check_Transaction_Balance - OUT_OF_BALANCE');
145 END IF;
146 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
147 app_exception.raise_exception;
148 WHEN NO_DATA_FOUND THEN
149 /* Case for invoice with rules */
150 IF PG_DEBUG in ('Y', 'C') THEN
151 arp_standard.debug( 'ARP_BALANCE_CHK.Check_Transaction_Balance - NO_DATA_FOUND' );
152 END IF;
153 WHEN excep_set_org_rem_amt_r12 THEN --LLCA
154 IF PG_DEBUG in ('Y', 'C') THEN
155 arp_standard.debug('EXCEPTION_set_original_rem_amt_r12 error count:'||l_msg_count);
156 arp_standard.debug('last error:'||l_msg_data);
157 END IF;
158 RAISE;
159
160 WHEN OTHERS THEN
161 IF PG_DEBUG in ('Y', 'C') THEN
162 arp_standard.debug( 'ARP_BALANCE_CHK.Check_Transaction_Balance - OTHERS' );
163 END IF;
164 RAISE;
165
166 END CHECK_TRANSACTION_BALANCE;
167
168 /* =======================================================================
169 | PROCEDURE Check_Recp_Balance
170 |
171 | DESCRIPTION
172 | This procedure takes sum of debits and credits for receipts and
173 | adjustments including discounts and tallies that debits equal
174 | credits, if not then it sets a message on the message stack
175 | indicating that items are out of balance.
176 |
177 | PARAMETERS
178 | p_cr_id IN Cash receipt id
179 | p_request_id IN Request id
180 | p_called_from_api IN Y-api call out
181 * ======================================================================*/
182 PROCEDURE CHECK_RECP_BALANCE(
183 p_cr_id IN NUMBER,
184 p_request_id IN NUMBER,
185 p_called_from_api IN VARCHAR2 default 'N') IS
186 cursor c1(p_cr_id IN NUMBER) is
187 select sum(nvl(amount_dr,0)) sum_amount_dr,
188 sum(nvl(amount_cr,0)) sum_amount_cr,
189 sum(nvl(acctd_amount_dr,0)) sum_acctd_amount_dr,
190 sum(nvl(acctd_amount_cr,0)) sum_acctd_amount_cr,
191 max(src.cc_flag) cc_flag, src.gl_date
192 from (select cash_receipt_id cr_id,
193 misc_cash_distribution_id src_id,
194 'MCD' src_tab,
195 'N' cc_flag,
196 gl_date
197 from ar_misc_cash_distributions
198 where cash_receipt_id = p_cr_id
199 and posting_control_id = -3
200 UNION ALL
201 select /*+ index(CRH AR_CASH_RECEIPT_HISTORY_N1) */
202 cash_receipt_id cr_id,
203 cash_receipt_history_id src_id,
204 'CRH' src_tab,
205 'N' cc_flag,
206 gl_date
207 from ar_cash_receipt_history
208 where cash_receipt_id = p_cr_id
209 and posting_control_id = -3
210 and nvl(postable_flag,'Y') = 'Y'
211 UNION ALL
212 select /*+ index(RA AR_RECEIVABLE_APPLICATIONS_N1) */
213 cash_receipt_id cr_id,
214 receivable_application_id src_id,
215 'RA' src_tab,
216 decode(amount_applied_from,NULL,
217 'N',
218 'Y') cc_flag,
219 gl_date
220 from ar_receivable_applications
221 where cash_receipt_id = p_cr_id
222 and nvl(confirmed_flag,'Y') = 'Y'
223 and nvl(postable,'Y') = 'Y'
224 and posting_control_id = -3) src,
225 ar_distributions ard
226 where ard.source_id = src.src_id
227 and ard.source_table = src.src_tab
228 group by src.gl_date;
229
230 l_no_balance VARCHAR2(1) := 'N';
231
232 l_corrupt_type VARCHAR2(20);
233 ps_data_corrupted EXCEPTION;
234
235 l_gl_date_closed DATE;
236 l_actual_date_closed DATE;
237 l_corruption_exists BOOLEAN := FALSE;
238 l_corruption_string VARCHAR2(50);
239
240 l_check_amount_dr NUMBER;
241 l_check_amount_cr NUMBER;
242
243 BEGIN
244
245 IF PG_DEBUG in ('Y', 'C') THEN
246 arp_standard.debug('ARP_BALANCE_CHK.CHECK_RECP_BALANCE (+)');
247 END IF;
248
249 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
250
251 IF p_cr_id IS NOT NULL AND arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
252
253 FOR c2 in c1(p_cr_id) LOOP
254
255 IF (c2.sum_amount_dr <> c2.sum_amount_cr AND c2.cc_flag = 'N') OR
256 (c2.sum_acctd_amount_dr <> c2.sum_acctd_amount_cr) THEN
257
258 /* Exception Out_of_balance could have been raised here. But continuing the flow
259 to print debug messages */
260 l_no_balance := 'Y' ;
261 l_check_amount_dr := c2.sum_acctd_amount_dr;
262 l_check_amount_cr := c2.sum_acctd_amount_cr;
263 END IF;
264
265 IF PG_DEBUG in ('Y', 'C') THEN
266 arp_standard.debug('GL_DATE : '||to_char(c2.gl_date,'DD-MON-YYYY'));
267 arp_standard.debug('Sum_amount_dr :' || c2.sum_amount_dr);
268 arp_standard.debug('Sum_amount_cr :' || c2.sum_amount_cr);
269 arp_standard.debug('Sum_acctd_amount_dr : '||c2.sum_acctd_amount_dr);
270 arp_standard.debug('Sum_acctd_amount_cr : '||c2.sum_acctd_amount_cr);
271 arp_standard.debug('CC_flag : '||c2.cc_flag);
272 END IF;
273 END LOOP;
274
275 --------------------------------------------------------
276 --Set the message on the message stack
277 --------------------------------------------------------
278 IF l_no_balance = 'Y' THEN
279
280 IF p_called_from_api = 'Y' THEN
281 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
282 fnd_msg_pub.Add;
283 END IF;
284
285 RAISE out_of_balance;
286
287 END IF;
288
289 END IF; --p_receipt id is NOT NULL
290
291 ELSE
292 IF PG_DEBUG in ('Y', 'C') THEN
293 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
294 END IF;
295 END IF;
296
297 IF ((p_ps_rec.payment_schedule_id is not null or p_ps_rec_pmt.payment_schedule_id is not null)
298 AND p_request_id is null
299 AND p_called_from_api = 'N'
300 ) THEN
301 BEGIN
302 arp_standard.debug('Class :' || p_ps_rec.class);
303 arp_standard.debug('p_request_id :' || p_request_id);
304 arp_standard.debug('p_called_from_api :' || p_called_from_api);
305 arp_standard.debug('psid1 :' || p_ps_rec.payment_schedule_id);
306 arp_standard.debug('psid2 :' || p_ps_rec_pmt.payment_schedule_id);
307
308 IF p_ps_rec.payment_schedule_id is not null THEN
309 CHECK_PS_DATE(
310 p_ps_rec => p_ps_rec,
311 p_corrupt_type => l_corrupt_type,
312 p_gl_date_closed => l_gl_date_closed,
313 p_actual_date_closed => l_actual_date_closed);
314 END IF;
315
316 IF p_ps_rec_pmt.payment_schedule_id is not null THEN
317 CHECK_PS_DATE(
318 p_ps_rec => p_ps_rec_pmt,
319 p_corrupt_type => l_corrupt_type,
320 p_gl_date_closed => l_gl_date_closed,
321 p_actual_date_closed => l_actual_date_closed);
322 END IF;
323
324
325
326
327 ARP_BALANCE_CHECK.P_ps_rec.payment_schedule_id := NULL;
328 ARP_BALANCE_CHECK.p_ps_rec.customer_trx_id := NULL;
329 ARP_BALANCE_CHECK.p_ps_rec.cash_receipt_id := NULL;
330 ARP_BALANCE_CHECK.p_ps_rec.class := NULL;
331 ARP_BALANCE_CHECK.p_ps_rec.actual_date_closed := NULL;
332 ARP_BALANCE_CHECK.p_ps_rec.status := NULL;
333 ARP_BALANCE_CHECK.p_ps_rec.trx_number := NULL;
334 ARP_BALANCE_CHECK.p_ps_rec.gl_date_closed := NULL;
335
336 ARP_BALANCE_CHECK.P_ps_rec_pmt.payment_schedule_id := NULL;
337 ARP_BALANCE_CHECK.P_ps_rec_pmt.customer_trx_id := NULL;
338 ARP_BALANCE_CHECK.P_ps_rec_pmt.cash_receipt_id := NULL;
339 ARP_BALANCE_CHECK.P_ps_rec_pmt.class := NULL;
340 ARP_BALANCE_CHECK.P_ps_rec_pmt.actual_date_closed := NULL;
341 ARP_BALANCE_CHECK.P_ps_rec_pmt.status := NULL;
342 ARP_BALANCE_CHECK.P_ps_rec_pmt.trx_number := NULL;
343 ARP_BALANCE_CHECK.P_ps_rec_pmt.gl_date_closed := NULL;
344
345 IF l_corrupt_type is not null then
346 l_corruption_exists :=TRUE;
347 RAISE ps_data_corrupted;
348 END IF;
349
350 EXCEPTION
351 WHEN ps_data_corrupted THEN
352 fnd_message.set_name('AR','GENERIC_MESSAGE');
353
354 IF l_corrupt_type = 'ACTUAL_DATE' THEN
355 l_corruption_string := 'Actual Date Closed';
356 ELSIF l_corrupt_type = 'GL_DATE' THEN
357 l_corruption_string := 'GL Date Closed';
358 ELSIF l_corrupt_type = 'BOTH_ACT_GL' THEN
359 l_corruption_string := 'Actual Date Closed and GL Date Closed';
360 END IF;
361
362 fnd_message.set_token ( token => 'GENERIC_TEXT',
363 value => 'The Value Is Incorrect for '||l_corruption_string ||' .Please Contact Oracle Support');
364 app_exception.raise_exception;
365 END;
366 END IF;
367
368 IF PG_DEBUG in ('Y', 'C') THEN
369 arp_standard.debug('ARP_BALANCE_CHK.CHECK_RECP_BALANCE (-)');
370 END IF;
371
372 EXCEPTION
373 WHEN out_of_balance THEN
374 IF PG_DEBUG in ('Y', 'C') THEN
375 arp_standard.debug('ARP_BALANCE_CHK.CHECK_RECP_BALANCE - OUT_OF_BALANCE');
376 END IF;
377
378 if CHECK_PRECISION(l_check_amount_dr) then
379 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
380
381 elsif CHECK_PRECISION(l_check_amount_cr) then
382 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
383
384 else
385 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
386 end if;
387
388 app_exception.raise_exception;
389
390 WHEN ps_data_corrupted THEN
391 app_exception.raise_exception;
392
393 WHEN OTHERS THEN
394 IF PG_DEBUG in ('Y', 'C') THEN
395 arp_standard.debug('ARP_BALANCE_CHK.CHECK_RECP_BALANCE - OTHERS');
396 END IF;
397 RAISE;
398
399 END CHECK_RECP_BALANCE;
400
401
402 /* =======================================================================
403 | PROCEDURE Check_Recp_Balance_Bulk
404 |
405 | DESCRIPTION
406 | This procedure takes sum of debits and credits for receipts
407 | including discounts and tallies that debits equal credits, if
408 | not then it sets a message on the message stack indicating that
409 | items are out of balance.
410 |
411 | PARAMETERS
412 | p_cr_id_low IN Cash Receipt Id Low.
413 | p_cr_id_high IN Cash Receipt Id High.
414 | p_unbalanced_cr_tbl OUT NOCOPY Unbalanced CR Id's.
415 * ======================================================================*/
416 PROCEDURE CHECK_RECP_BALANCE_BULK(
417 p_cr_id_low IN NUMBER,
418 p_cr_id_high IN NUMBER,
419 p_unbalanced_cr_tbl OUT NOCOPY unbalanced_receipts) IS
420
421 cursor c1(p_cr_id_low IN NUMBER,
422 p_cr_id_high IN NUMBER) is
423 select sum(nvl(amount_dr,0)) sum_amount_dr,
424 sum(nvl(amount_cr,0)) sum_amount_cr,
425 sum(nvl(acctd_amount_dr,0)) sum_acctd_amount_dr,
426 sum(nvl(acctd_amount_cr,0)) sum_acctd_amount_cr,
427 max(src.cc_flag) cc_flag, src.gl_date, src.cr_id
428 from ( select mcd.cash_receipt_id cr_id,
429 mcd.misc_cash_distribution_id src_id,
430 'MCD' src_tab,
431 'N' cc_flag,
432 mcd.gl_date
433 from ar_cash_receipts cr, ar_misc_cash_distributions mcd
434 where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
435 and cr.cash_receipt_id = mcd.cash_receipt_id
436 and mcd.posting_control_id = -3
437 UNION ALL
438 select crh.cash_receipt_id cr_id,
439 crh.cash_receipt_history_id src_id,
440 'CRH' src_tab,
441 'N' cc_flag,
442 crh.gl_date
443 from ar_cash_receipts cr, ar_cash_receipt_history crh
444 where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
445 and cr.cash_receipt_id = crh.cash_receipt_id
446 and crh.posting_control_id = -3
447 and nvl(crh.postable_flag,'Y') = 'Y'
448 UNION ALL
449 select ra.cash_receipt_id cr_id,
450 ra.receivable_application_id src_id,
451 'RA' src_tab,
452 decode(ra.amount_applied_from,NULL,
453 'N',
454 'Y') cc_flag,
455 ra.gl_date
456 from ar_cash_receipts cr, ar_receivable_applications ra
457 where cr.cash_receipt_id between p_cr_id_low and p_cr_id_high
458 and cr.cash_receipt_id = ra.cash_receipt_id
459 and nvl(ra.confirmed_flag,'Y') = 'Y'
460 and nvl(ra.postable,'Y') = 'Y'
461 and ra.posting_control_id = -3) src,
462 ar_distributions ard
463 where ard.source_id = src.src_id
464 and ard.source_table = src.src_tab
465 group by src.gl_date, src.cr_id
466 having ((sum(nvl(amount_dr,0)) <> sum(nvl(amount_cr,0)) AND max(src.cc_flag) = 'N')
467 OR (sum(nvl(acctd_amount_dr,0)) <> sum(nvl(acctd_amount_cr,0))))
468 order by src.cr_id;
469
470 l_bulk_index NUMBER := 0;
471
472 BEGIN
473
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_standard.debug('ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK (+)');
476 END IF;
477
478 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
479
480 IF p_cr_id_low IS NOT NULL AND p_cr_id_high IS NOT NULL AND arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
481
482 FOR c2 in c1(p_cr_id_low, p_cr_id_high) LOOP
483
484 IF PG_DEBUG in ('Y', 'C') THEN
485 arp_standard.debug('ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK - OUT_OF_BALANCE');
486 arp_standard.debug('Cash Receipt Id: ' || c2.cr_id);
487 arp_standard.debug('GL Date: ' || to_char(c2.gl_date,'DD-MON-YYYY'));
488 arp_standard.debug('Sum Amount Dr: ' || c2.sum_amount_dr);
489 arp_standard.debug('Sum Amount Cr: ' || c2.sum_amount_cr);
490 arp_standard.debug('Sum Acctd Amount Dr: ' || c2.sum_acctd_amount_dr);
491 arp_standard.debug('Sum Acctd Amount Cr: ' || c2.sum_acctd_amount_cr);
492 arp_standard.debug('Cross Currency Flag: ' || c2.cc_flag);
493 END IF;
494
495 l_bulk_index := l_bulk_index + 1;
496 p_unbalanced_cr_tbl(l_bulk_index).cash_receipt_id := c2.cr_id;
497
498 IF CHECK_PRECISION(c2.sum_acctd_amount_dr)
499 OR CHECK_PRECISION(c2.sum_acctd_amount_cr)
500 THEN
501 p_unbalanced_cr_tbl(l_bulk_index).message_code := 'AR_APP_CURR_PRECISION';
502 ELSE
503 p_unbalanced_cr_tbl(l_bulk_index).message_code := 'AR_AMOUNTS_NO_MATCH';
504 END IF;
505
506 END LOOP;
507
508 END IF;
509
510 ELSE
511
512 IF PG_DEBUG in ('Y', 'C') THEN
513 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
514 END IF;
515
516 END IF;
517
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_standard.debug('ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK (-)');
520 END IF;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 IF PG_DEBUG in ('Y', 'C') THEN
525 arp_standard.debug('ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK - OTHERS');
526 END IF;
527 RAISE;
528 END CHECK_RECP_BALANCE_BULK;
529
530
531 /* =======================================================================
532 | PROCEDURE Check_Adj_Balance
533 |
534 | DESCRIPTION
535 | This procedure takes sum of debits and credits for adjustments
536 | tallies that debits equal credits, if not then it sets a message
537 | on the message stack indicating that items are out of balance.
538 | For Non postable adjustments, it makes sure that the amount is
539 | equal to the amounts assigned to different buckets
540 |
541 | PARAMETERS
542 | p_adj_id IN Adjustment id
543 | p_request_id IN Request id
544 | p_called_from_api IN Y-api call out
545 * ======================================================================*/
546 PROCEDURE CHECK_ADJ_BALANCE(
547 p_adj_id IN NUMBER,
548 p_request_id IN NUMBER,
549 p_called_from_api IN VARCHAR2 default 'N') IS
550
551
552 l_amt_dr NUMBER;
553 l_amt_cr NUMBER;
554 l_acctd_amt_dr NUMBER;
555 l_acctd_amt_cr NUMBER;
556 l_amount NUMBER;
557 l_calc_amount NUMBER;
558 l_status VARCHAR2(1); /*5017553*/
559 l_corrupt_type VARCHAR2(20);
560 ps_data_corrupted EXCEPTION;
561 l_gl_date_closed DATE;
562 l_actual_date_closed DATE;
563 l_corruption_exists BOOLEAN := FALSE;
564 l_corruption_string VARCHAR2(50);
565 l_check_amount_dr NUMBER;
566 l_check_amount_cr NUMBER;
567
568 BEGIN
569 IF PG_DEBUG in ('Y', 'C') THEN
570 arp_standard.debug('ARP_BALANCE_CHK.CHECK_ADJ_BALANCE (+)');
571 END IF;
572
573 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
574
575 IF p_adj_id IS NOT NULL AND arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
576
577 /* Check amount_dr equals to amount_cr */
578
579 select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
580 sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0))
581 into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr
582 from (select adjustment_id src_id,
583 'ADJ' src_tab
584 from ar_adjustments
585 where adjustment_id = p_adj_id
586 and nvl(postable,'Y') = 'Y'
587 and posting_control_id = -3) src,
588 ar_distributions ard
589 where ard.source_id = src.src_id
590 and ard.source_table = src.src_tab;
591
592 --------------------------------------------------------
593 --Set the message on the message stack
594 --------------------------------------------------------
595 IF (l_amt_dr <> l_amt_cr) OR (l_acctd_amt_dr <> l_acctd_amt_cr) THEN
596
597 IF p_called_from_api = 'Y' THEN
598
599 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
600 fnd_msg_pub.Add;
601
602 END IF;
603 l_check_amount_dr := l_acctd_amt_dr;
604 l_check_amount_cr := l_acctd_amt_cr;
605 RAISE out_of_balance;
606
607 END IF;
608
609 /* Check that Amount = line_adjusted+tax+adjusted+freight_adjusted+
610 receivables_charges_adjusted */
611 select nvl(amount,0), nvl(line_adjusted,0)+nvl(tax_adjusted,0)+
612 nvl(freight_adjusted,0)+nvl(receivables_charges_adjusted,0),
613 status
614 into l_amount, l_calc_amount,l_status
615 from ar_adjustments
616 where adjustment_id = p_adj_id;
617
618 IF l_amount <> l_calc_amount AND l_status = 'A' THEN
619 IF p_called_from_api = 'Y' THEN
620
621 fnd_message.set_name('AR','AR_ADJ_AMT_NO_MATCH');
622 fnd_msg_pub.Add;
623
624 END IF;
625
626 RAISE amount_mismatch;
627 END IF;
628
629 END IF; --adjustment id is not null
630
631 ELSE
632 IF PG_DEBUG in ('Y', 'C') THEN
633 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
634 END IF;
635 END IF;
636
637 IF (p_ps_rec.payment_schedule_id is not null
638 AND p_request_id is null
639 AND p_called_from_api = 'N'
640 ) THEN
641
642 BEGIN
643 CHECK_PS_DATE(
644 p_ps_rec => p_ps_rec,
645 p_corrupt_type => l_corrupt_type,
646 p_gl_date_closed => l_gl_date_closed,
647 p_actual_date_closed => l_actual_date_closed);
648
649 ARP_BALANCE_CHECK.P_ps_rec.payment_schedule_id := NULL;
650 ARP_BALANCE_CHECK.p_ps_rec.customer_trx_id := NULL;
651 ARP_BALANCE_CHECK.p_ps_rec.cash_receipt_id := NULL;
652 ARP_BALANCE_CHECK.p_ps_rec.class := NULL;
653 ARP_BALANCE_CHECK.p_ps_rec.actual_date_closed := NULL;
654 ARP_BALANCE_CHECK.p_ps_rec.status := NULL;
655 ARP_BALANCE_CHECK.p_ps_rec.trx_number := NULL;
656 ARP_BALANCE_CHECK.p_ps_rec.gl_date_closed := NULL;
657
658 IF l_corrupt_type is not null then
659 l_corruption_exists :=TRUE;
660 RAISE ps_data_corrupted;
661 END IF;
662 EXCEPTION
663 WHEN ps_data_corrupted THEN
664 fnd_message.set_name('AR','GENERIC_MESSAGE');
665
666 IF l_corrupt_type = 'ACTUAL_DATE' THEN
667 l_corruption_string := 'Actual Date Closed';
668 ELSIF l_corrupt_type = 'GL_DATE' THEN
669 l_corruption_string := 'GL Date Closed';
670 ELSIF l_corrupt_type = 'BOTH_ACT_GL' THEN
671 l_corruption_string := 'Actual Date Closed and GL Date Closed';
672 END IF;
673
674 fnd_message.set_token ( token => 'GENERIC_TEXT',
675 value => 'The Value Is Incorrect for '||l_corruption_string ||' .Please Contact Oracle Support');
676 app_exception.raise_exception;
677 END;
678 END IF;
679
680 IF PG_DEBUG in ('Y', 'C') THEN
681 arp_standard.debug('ARP_BALANCE_CHK.CHECK_ADJ_BALANCE (-)');
682 END IF;
683
684 EXCEPTION
685 WHEN NO_DATA_FOUND THEN
686 IF PG_DEBUG in ('Y', 'C') THEN
687 arp_standard.debug('ARP_BALANCE_CHK.CHECK_ADJ_BALANCE - NO_DATA_FOUND');
688 END IF;
689 RAISE;
690 WHEN out_of_balance THEN
691 IF PG_DEBUG in ('Y', 'C') THEN
692 arp_standard.debug('ARP_BALANCE_CHK.CHECK_ADJ_BALANCE - OUT_OF_BALANCE');
693 arp_standard.debug('Adjustment_id = '||p_adj_id);
694 arp_standard.debug('Amount Debit = '||l_amt_dr);
695 arp_standard.debug('Amount Credit = '||l_amt_cr);
696 arp_standard.debug('Acctd Amount Debit = '||l_acctd_amt_dr);
697 arp_standard.debug('Acctd AmountCredit = '||l_acctd_amt_cr);
698 END IF;
699
700 if CHECK_PRECISION(l_check_amount_dr) then
701 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
702
703 elsif CHECK_PRECISION(l_check_amount_cr) then
704 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
705
706 else
707 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
708 end if;
709
710 app_exception.raise_exception;
711
712 WHEN amount_mismatch THEN
713 IF PG_DEBUG in ('Y', 'C') THEN
714 arp_standard.debug('ARP_BALANCE_CHK.CHECK_ADJ_BALANCE -AMOUNT_MISMATCH');
715 arp_standard.debug('Adjustment_id = '||p_adj_id);
716 arp_standard.debug('Amount = '||l_amount);
717 arp_standard.debug('Sum of Buckets = '||l_calc_amount);
718 END IF;
719 fnd_message.set_name('AR','AR_ADJ_AMT_NO_MATCH');
720 app_exception.raise_exception;
721
722 WHEN ps_data_corrupted THEN
723 app_exception.raise_exception;
724
725 WHEN OTHERS THEN
726 IF PG_DEBUG in ('Y', 'C') THEN
727 arp_standard.debug('ARP_BALANCE_CHK.CHECK_RECP_BALANCE - OTHERS');
728 END IF;
729 RAISE;
730
731 END CHECK_ADJ_BALANCE;
732
733 /* =======================================================================
734 | PROCEDURE Check_Appln_Balance
735 |
736 | DESCRIPTION
737 | This procedure takes sum of debits and credits for CM Applications
738 | tallies that debits equal credits, if not then it sets a message
739 | on the message stack indicating that items are out of balance.
740 |
741 | PARAMETERS
742 | p_receivable_application_id IN Receivable Application ID
743 | p_request_id IN Request id
744 | p_called_from_api IN Y-api call out
745 * ======================================================================*/
746 PROCEDURE CHECK_APPLN_BALANCE(
747 p_receivable_application_id IN NUMBER,
748 p_request_id IN NUMBER,
749 p_called_from_api IN VARCHAR2 default 'N') IS
750
751 l_amt_dr NUMBER;
752 l_amt_cr NUMBER;
753 l_acctd_amt_dr NUMBER;
754 l_acctd_amt_cr NUMBER;
755 l_corrupt_type VARCHAR2(20);
756 ps_data_corrupted EXCEPTION;
757 l_gl_date_closed DATE;
758 l_actual_date_closed DATE;
759 l_corruption_exists BOOLEAN := FALSE;
760 l_corruption_string VARCHAR2(50);
761 l_check_amount_dr NUMBER;
762 l_check_amount_cr NUMBER;
763
764 BEGIN
765
766 IF PG_DEBUG in ('Y', 'C') THEN
767 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE (+)');
768 END IF;
769
770 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
771
772 IF p_receivable_application_id IS NOT NULL
773 AND arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
774
775 select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
776 sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0))
777 into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr
778 from (select receivable_application_id src_id,
779 'RA' src_tab
780 from ar_receivable_applications
781 where receivable_application_id = p_receivable_application_id
782 and nvl(postable,'Y') = 'Y'
783 and posting_control_id = -3) src,
784 ar_distributions ard
785 where ard.source_id = src.src_id
786 and ard.source_table = src.src_tab;
787
788 --------------------------------------------------------
789 --Set the message on the message stack
790 --------------------------------------------------------
791 IF (l_amt_dr <> l_amt_cr) OR (l_acctd_amt_dr <> l_acctd_amt_cr) THEN
792 IF p_called_from_api = 'Y' THEN
793
794 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
795 fnd_msg_pub.Add;
796
797 END IF;
798 l_check_amount_dr := l_acctd_amt_dr;
799 l_check_amount_cr := l_acctd_amt_cr;
800 RAISE out_of_balance;
801
802 END IF;
803
804 END IF; --receivable application id is not null
805
806 ELSE
807 IF PG_DEBUG in ('Y', 'C') THEN
808 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
809 END IF;
810 END IF;
811 arp_standard.debug('Request id '||p_request_id);
812 arp_standard.debug('Called from API '||p_called_from_api);
813
814 IF ((p_ps_rec.payment_schedule_id is not null or p_ps_rec_pmt.payment_schedule_id is not null)
815 AND p_request_id is null
816 AND p_called_from_api = 'N'
817 ) THEN
818 BEGIN
819
820 IF p_ps_rec.payment_schedule_id is not null THEN
821 CHECK_PS_DATE(
822 p_ps_rec => p_ps_rec,
823 p_corrupt_type => l_corrupt_type,
824 p_gl_date_closed => l_gl_date_closed,
825 p_actual_date_closed => l_actual_date_closed);
826 END IF;
827
828 IF p_ps_rec_pmt.payment_schedule_id is not null THEN
829 CHECK_PS_DATE(
830 p_ps_rec => p_ps_rec_pmt,
831 p_corrupt_type => l_corrupt_type,
832 p_gl_date_closed => l_gl_date_closed,
833 p_actual_date_closed => l_actual_date_closed);
834 END IF;
835
836 ARP_BALANCE_CHECK.P_ps_rec.payment_schedule_id := NULL;
837 ARP_BALANCE_CHECK.p_ps_rec.customer_trx_id := NULL;
838 ARP_BALANCE_CHECK.p_ps_rec.cash_receipt_id := NULL;
839 ARP_BALANCE_CHECK.p_ps_rec.class := NULL;
840 ARP_BALANCE_CHECK.p_ps_rec.actual_date_closed := NULL;
841 ARP_BALANCE_CHECK.p_ps_rec.status := NULL;
842 ARP_BALANCE_CHECK.p_ps_rec.trx_number := NULL;
843 ARP_BALANCE_CHECK.p_ps_rec.gl_date_closed := NULL;
844
845 ARP_BALANCE_CHECK.P_ps_rec_pmt.payment_schedule_id := NULL;
846 ARP_BALANCE_CHECK.P_ps_rec_pmt.customer_trx_id := NULL;
847 ARP_BALANCE_CHECK.P_ps_rec_pmt.cash_receipt_id := NULL;
848 ARP_BALANCE_CHECK.P_ps_rec_pmt.class := NULL;
849 ARP_BALANCE_CHECK.P_ps_rec_pmt.actual_date_closed := NULL;
850 ARP_BALANCE_CHECK.P_ps_rec_pmt.status := NULL;
851 ARP_BALANCE_CHECK.P_ps_rec_pmt.trx_number := NULL;
852 ARP_BALANCE_CHECK.P_ps_rec_pmt.gl_date_closed := NULL;
853
854 IF l_corrupt_type is not null then
855 l_corruption_exists :=TRUE;
856 RAISE ps_data_corrupted;
857 END IF;
858
859 EXCEPTION
860 WHEN ps_data_corrupted THEN
861 fnd_message.set_name('AR','GENERIC_MESSAGE');
862
863 IF l_corrupt_type = 'ACTUAL_DATE' THEN
864 l_corruption_string := 'Actual Date Closed';
865 ELSIF l_corrupt_type = 'GL_DATE' THEN
866 l_corruption_string := 'GL Date Closed';
867 ELSIF l_corrupt_type = 'BOTH_ACT_GL' THEN
868 l_corruption_string := 'Actual Date Closed and GL Date Closed';
869 END IF;
870
871 fnd_message.set_token ( token => 'GENERIC_TEXT',
872 value => 'The Value Is Incorrect for '||l_corruption_string ||' .Please Contact Oracle Support');
873 app_exception.raise_exception;
874 END;
875 ELSE
876 ARP_BALANCE_CHECK.P_reg_cm := 'N';
877 END IF;
878
879 IF PG_DEBUG in ('Y', 'C') THEN
880 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE (-)');
881 END IF;
882
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN
885 /* Exception need not be raised as this can happen when the Application
886 is non postable */
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - NO_DATA_FOUND');
889 END IF;
890 WHEN out_of_balance THEN
891 IF PG_DEBUG in ('Y', 'C') THEN
892 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - OUT_OF_BALANCE');
893 arp_standard.debug('Receivable Application_id = '||p_receivable_application_id);
894 arp_standard.debug('Amount Debit = '||l_amt_dr);
895 arp_standard.debug('Amount Credit = '||l_amt_cr);
896 arp_standard.debug('Acctd Amount Debit = '||l_acctd_amt_dr);
897 arp_standard.debug('Acctd AmountCredit = '||l_acctd_amt_cr);
898 END IF;
899
900 if CHECK_PRECISION(l_check_amount_dr) then
901 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
902
903 elsif CHECK_PRECISION(l_check_amount_cr) then
904 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
905
906 else
907 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
908 end if;
909
910 app_exception.raise_exception;
911
912
913 WHEN OTHERS THEN
914 IF PG_DEBUG in ('Y', 'C') THEN
915 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - OTHERS');
916 END IF;
917 RAISE;
918
919 END CHECK_APPLN_BALANCE;
920
921 /* =======================================================================
922 | PROCEDURE Check_Appln_Balance
923 |
924 | DESCRIPTION
925 | This procedure takes sum of debits and credits for Receipt Applications.
926 | tallies that debits equal credits, if not then it sets a message
927 | on the message stack indicating that items are out of balance.
928 |
929 | PARAMETERS
930 | p_receivable_application_id1 IN Receivable Application ID
931 | p_receivable_application_id2 IN Receivable Application ID of the pair
932 | p_request_id IN Request id
933 | p_called_from_api IN Y-api call out
934 * ======================================================================*/
935 PROCEDURE CHECK_APPLN_BALANCE(
936 p_receivable_application_id1 IN NUMBER,
937 p_receivable_application_id2 IN NUMBER,
938 p_request_id IN NUMBER,
939 p_called_from_api IN VARCHAR2 default 'N') IS
940
941 l_amt_dr NUMBER;
942 l_amt_cr NUMBER;
943 l_acctd_amt_dr NUMBER;
944 l_acctd_amt_cr NUMBER;
945 l_cc_flag VARCHAR2(1);
946 l_check_amount_dr NUMBER;
947 l_check_amount_cr NUMBER;
948
949 BEGIN
950
951 IF PG_DEBUG in ('Y', 'C') THEN
952 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE (+) -> for Receipt Applications');
953 END IF;
954
955 IF PG_BAL_CHECK_ENABLED = 'Y' THEN
956
957 IF p_receivable_application_id1 IS NOT NULL
958 AND p_receivable_application_id2 IS NOT NULL
959 AND arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
960
961 select sum(nvl(amount_dr,0)), sum(nvl(amount_cr,0)),
962 sum(nvl(acctd_amount_dr,0)), sum(nvl(acctd_amount_cr,0)),
963 max(src.cc_flag) cc_flag
964 into l_amt_dr, l_amt_cr, l_acctd_amt_dr, l_acctd_amt_cr, l_cc_flag
965 from (select receivable_application_id src_id,
966 'RA' src_tab,
967 decode(amount_applied_from,NULL,
968 'N',
969 'Y') cc_flag
970 from ar_receivable_applications
971 where ( receivable_application_id = p_receivable_application_id1
972 or receivable_application_id = p_receivable_application_id2)
973 and nvl(postable,'Y') = 'Y'
974 and posting_control_id = -3) src,
975 ar_distributions ard
976 where ard.source_id = src.src_id
977 and ard.source_table = src.src_tab;
978
979 --------------------------------------------------------
980 --Set the message on the message stack
981 --------------------------------------------------------
982 IF (((l_amt_dr <> l_amt_cr) AND (l_cc_flag = 'N'))
983 OR (l_acctd_amt_dr <> l_acctd_amt_cr)) THEN
984 IF p_called_from_api = 'Y' THEN
985
986 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
987 fnd_msg_pub.Add;
988
989 END IF;
990 l_check_amount_dr := l_acctd_amt_dr;
991 l_check_amount_cr := l_acctd_amt_cr;
992 RAISE out_of_balance;
993
994 END IF;
995
996 END IF; --receivable application ids are not null
997
998 ELSE
999 IF PG_DEBUG in ('Y', 'C') THEN
1000 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
1001 END IF;
1002 END IF;
1003
1004 IF PG_DEBUG in ('Y', 'C') THEN
1005 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE (-) -> for Receipt Applications');
1006 END IF;
1007
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 /* Exception need not be raised as this can happen when the Application
1011 is non postable */
1012 IF PG_DEBUG in ('Y', 'C') THEN
1013 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - NO_DATA_FOUND');
1014 END IF;
1015 WHEN out_of_balance THEN
1016 IF PG_DEBUG in ('Y', 'C') THEN
1017 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - OUT_OF_BALANCE');
1018 arp_standard.debug('Receivable Application_id1 = '||p_receivable_application_id1);
1019 arp_standard.debug('Receivable Application_id2 = '||p_receivable_application_id2);
1020 arp_standard.debug('Amount Debit = '||l_amt_dr);
1021 arp_standard.debug('Amount Credit = '||l_amt_cr);
1022 arp_standard.debug('Acctd Amount Debit = '||l_acctd_amt_dr);
1023 arp_standard.debug('Acctd AmountCredit = '||l_acctd_amt_cr);
1024 END IF;
1025
1026 if CHECK_PRECISION(l_check_amount_dr) then
1027 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
1028
1029 elsif CHECK_PRECISION(l_check_amount_cr) then
1030 FND_MESSAGE.SET_NAME( 'AR', 'AR_APP_CURR_PRECISION' );
1031
1032 else
1033 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
1034 end if;
1035
1036 app_exception.raise_exception;
1037
1038 WHEN OTHERS THEN
1039 IF PG_DEBUG in ('Y', 'C') THEN
1040 arp_standard.debug('ARP_BALANCE_CHK.CHECK_APPLN_BALANCE - OTHERS -> for Receipt Applications');
1041 END IF;
1042 RAISE;
1043
1044 END CHECK_APPLN_BALANCE;
1045 /* =======================================================================
1046 | PROCEDURE Check_Ps_Date
1047 |
1048 | DESCRIPTION
1049 | This Procedure checks if the gl_date_closed and actual_date_closed
1050 | are stamped correctly in ar_payment_schedules when the payment schedule
1051 | is closed. It returns the correct values which can can then be sent to the
1052 | Fix_Ps_Date procedure to correct the data corruption
1053 |
1054 | PARAMETERS
1055 | p_ps_id IN Payment Schedule id
1056 | p_corrupt_type OUT Corruption Type
1057 | p_gl_date_closed OUT New Value for GL Closed Date
1058 | p_actual_date_closed OUT New Value for Actual Closed Date
1059 * ======================================================================*/
1060 PROCEDURE CHECK_PS_DATE(
1061 p_ps_rec IN ar_payment_schedules%ROWTYPE,
1062 p_corrupt_type OUT NOCOPY VARCHAR2,
1063 p_gl_date_closed OUT NOCOPY DATE,
1064 p_actual_date_closed OUT NOCOPY DATE) IS
1065
1066 l_max_apply_date DATE;
1067 l_max_gl_date DATE;
1068 BEGIN
1069
1070 IF PG_DEBUG in ('Y', 'C') THEN
1071 arp_standard.debug('ARP_BALANCE_CHK.CHECK_PS_DATE (+)');
1072 END IF;
1073
1074
1075
1076
1077
1078 --IF PG_AUTO_CORRECT_ENABLED = 'Y' AND p_ps_rec.status = 'CL' THEN --checking for the profile option
1079
1080
1081 IF p_ps_rec.payment_schedule_id is not null THEN
1082 l_max_apply_date := NULL;
1083 l_max_gl_date := NULL;
1084
1085 IF p_ps_rec.class <> 'PMT' THEN
1086 SELECT MAX(apply_date)
1087 INTO l_max_apply_date
1088 FROM (
1089 SELECT MAX(apply_date) apply_date
1090 FROM ar_receivable_applications ra
1091 WHERE status = 'APP'
1092 AND ra.payment_schedule_id = p_ps_rec.payment_schedule_id
1093 UNION ALL
1094 SELECT MAX(apply_date) apply_date
1095 FROM ar_receivable_applications ra
1096 WHERE status = 'APP'
1097 AND ra.applied_payment_schedule_id = p_ps_rec.payment_schedule_id
1098 UNION ALL
1099 SELECT MAX(apply_date) apply_date
1100 FROM ar_adjustments adj
1101 WHERE status = 'A'
1102 AND adj.payment_schedule_id = p_ps_rec.payment_schedule_id
1103 );
1104 ELSE
1105 SELECT MAX(apply_date)
1106 INTO l_max_apply_date
1107 FROM ar_receivable_applications ra
1108 WHERE payment_schedule_id = p_ps_rec.payment_schedule_id;
1109 END IF;
1110
1111 IF NVL(l_max_apply_date,p_ps_rec.actual_date_closed) > p_ps_rec.actual_date_closed THEN
1112 p_corrupt_type := 'ACTUAL_DATE';
1113
1114 IF PG_DEBUG in ('Y', 'C') THEN
1115 IF p_ps_rec.class <> 'PMT' THEN
1116 arp_standard.debug('Customer_Trx_Id : '||p_ps_rec.customer_trx_id);
1117 ELSE
1118 arp_standard.debug('Cash_Receipt_Id : '||p_ps_rec.cash_receipt_id);
1119 END IF;
1120 arp_standard.debug('Class :' || p_ps_rec.class);
1121 arp_standard.debug('Payment_schedule_id :' || p_ps_rec.payment_schedule_id);
1122 arp_standard.debug('Status : '||p_ps_rec.status);
1123 arp_standard.debug('Transaction Number : '||p_ps_rec.trx_number);
1124 arp_standard.debug('Current Value (Actual Date Closed) : '||p_ps_rec.actual_date_closed);
1125 arp_standard.debug('New Value (Actual Date Closed) : '||l_max_apply_date);
1126 END IF;
1127 END IF;
1128
1129 IF p_ps_rec.class <> 'PMT' THEN
1130 SELECT MAX(gl_date)
1131 INTO l_max_gl_date
1132 FROM (
1133 SELECT MAX(gl_date) gl_date
1134 FROM ar_receivable_applications ra
1135 WHERE status = 'APP'
1136 AND ra.payment_schedule_id = p_ps_rec.payment_schedule_id
1137 UNION ALL
1138 SELECT MAX(gl_date) gl_date
1139 FROM ar_receivable_applications ra
1140 WHERE status = 'APP'
1141 AND ra.applied_payment_schedule_id = p_ps_rec.payment_schedule_id
1142 UNION ALL
1143 SELECT MAX(gl_date) gl_date
1144 FROM ar_adjustments adj
1145 WHERE status = 'A'
1146 AND adj.payment_schedule_id = p_ps_rec.payment_schedule_id
1147 );
1148 ELSE
1149 SELECT MAX(gl_date)
1150 INTO l_max_gl_date
1151 FROM ar_receivable_applications ra
1152 WHERE payment_schedule_id = p_ps_rec.payment_schedule_id;
1153 END IF;
1154
1155 IF NVL(l_max_gl_date,p_ps_rec.gl_date_closed) > p_ps_rec.gl_date_closed THEN
1156 IF p_corrupt_type = 'ACTUAL_DATE' THEN
1157 p_corrupt_type := 'BOTH_ACT_GL';
1158 ELSE
1159 p_corrupt_type := 'GL_DATE';
1160 END IF;
1161
1162 IF PG_DEBUG in ('Y', 'C') THEN
1163 IF p_ps_rec.class <> 'PMT' THEN
1164 arp_standard.debug('Customer_Trx_Id : '||p_ps_rec.customer_trx_id);
1165 ELSE
1166 arp_standard.debug('Cash_Receipt_Id : '||p_ps_rec.cash_receipt_id);
1167 END IF;
1168 arp_standard.debug('Class :' || p_ps_rec.class);
1169 arp_standard.debug('Payment_schedule_id :' || p_ps_rec.payment_schedule_id);
1170 arp_standard.debug('Status : '||p_ps_rec.status);
1171 arp_standard.debug('Transaction Number : '||p_ps_rec.trx_number);
1172 arp_standard.debug('Current Value (GL Date Closed) : '||p_ps_rec.gl_date_closed);
1173 arp_standard.debug('New Value (GL Date Closed) : '||l_max_gl_date);
1174 END IF;
1175 END IF;
1176
1177 END IF;
1178 --END IF;
1179 p_gl_date_closed := l_max_gl_date;
1180 p_actual_date_closed := l_max_apply_date;
1181
1182 IF PG_DEBUG in ('Y', 'C') THEN
1183 arp_standard.debug('Corruption Type :'||p_corrupt_type);
1184 arp_standard.debug('ARP_BALANCE_CHK.CHECK_PS_DATE (-)');
1185 END IF;
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 IF PG_DEBUG in ('Y', 'C') THEN
1189 arp_standard.debug('ARP_BALANCE_CHK.CHECK_PS_DATE - OTHERS');
1190 END IF;
1191 RAISE;
1192 END CHECK_PS_DATE;
1193 /* =======================================================================
1194 | PROCEDURE Fix_Ps_Date
1195 |
1196 | DESCRIPTION
1197 | This procedure corrects the data for the fields gl_date_closed and actual_date_closed
1198 | in ar_payment_schedules. The correct values need to be fetched from the procedure
1199 | CHECK_PS_DATE
1200 |
1201 |
1202 | PARAMETERS
1203 | p_ps_id IN Payment Schedule id
1204 | p_corrupt_type IN Corruption Type
1205 | p_gl_date_closed IN New Value for GL Closed Date
1206 | p_actual_date_closed IN New Value for Actual Closed Date
1207 * ======================================================================*/
1208 PROCEDURE FIX_PS_DATE( p_ps_id IN NUMBER,
1209 p_corrupt_type IN VARCHAR2,
1210 p_gl_date_closed IN DATE,
1211 p_actual_date_closed IN DATE) IS
1212 l_ps_rec ar_payment_schedules%ROWTYPE;
1213 BEGIN
1214
1215 IF PG_DEBUG in ('Y', 'C') THEN
1216 arp_standard.debug('ARP_BALANCE_CHK.FIX_PS_DATE (+)');
1217 END IF;
1218
1219 --IF PG_AUTO_CORRECT_ENABLED = 'Y' THEN --checking for the profile option
1220 arp_ps_pkg.fetch_p(p_ps_id,l_ps_rec);
1221
1222 IF p_corrupt_type = 'ACTUAL_DATE' THEN
1223 l_ps_rec.actual_date_closed := p_actual_date_closed;
1224 ELSE
1225 IF p_corrupt_type = 'GL_DATE' THEN
1226 l_ps_rec.gl_date_closed := p_gl_date_closed;
1227 ELSE
1228 IF p_corrupt_type = 'BOTH_ACT_GL' THEN
1229 l_ps_rec.actual_date_closed := p_actual_date_closed;
1230 l_ps_rec.gl_date_closed := p_gl_date_closed;
1231 END IF;
1232 END IF;
1233 END IF;
1234
1235 l_ps_rec.last_update_date := SYSDATE;
1236 l_ps_rec.payment_schedule_id := p_ps_id;
1237 IF p_corrupt_type IS NOT NULL THEN
1238 arp_ps_pkg.update_p(l_ps_rec);
1239 END IF;
1240
1241 --END IF;
1242 IF PG_DEBUG in ('Y', 'C') THEN
1243 arp_standard.debug('ARP_BALANCE_CHK.FIX_PS_DATE (-)');
1244 END IF;
1245
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 IF PG_DEBUG in ('Y', 'C') THEN
1249 arp_standard.debug('ARP_BALANCE_CHK.FIX_PS_DATE - OTHERS');
1250 END IF;
1251 RAISE;
1252 END FIX_PS_DATE;
1253
1254
1255 FUNCTION Check_Precision( p_amount IN NUMBER )
1256 RETURN BOOLEAN IS
1257
1258 p_actual_amount NUMBER;
1259 p_FunctionalCurrency Varchar2(20);
1260
1261 BEGIN
1262 IF PG_DEBUG in ('Y', 'C') THEN
1263 arp_standard.debug('ARP_BALANCE_CHK.Check_Precision (+)');
1264 arp_standard.debug('p_amount ' || p_amount);
1265 END IF;
1266
1267 SELECT sob.currency_code
1268 INTO p_FunctionalCurrency
1269 FROM ar_system_parameters sp,
1270 gl_sets_of_books sob
1271 WHERE sob.set_of_books_id = sp.set_of_books_id;
1272
1273 p_actual_amount := ARPCURR.CurrRound(p_amount, p_FunctionalCurrency) ;
1274
1275 IF PG_DEBUG in ('Y', 'C') THEN
1276 arp_standard.debug('p_actual_amount ' || p_actual_amount);
1277 arp_standard.debug('p_FunctionalCurrency ' || p_amount);
1278 END IF;
1279
1280 IF p_amount <> p_actual_amount then
1281 arp_standard.debug('Application amount precision do not match current FND currency precision');
1282 Return TRUE;
1283 END IF;
1284
1285 IF PG_DEBUG in ('Y', 'C') THEN
1286 arp_standard.debug('Application amount precision matches with FND currency precision');
1287 arp_standard.debug('ARP_BALANCE_CHK.Check_Precision (-)');
1288 END IF;
1289 Return FALSE;
1290
1291 EXCEPTION WHEN OTHERS THEN
1292 arp_standard.debug('ARP_BALANCE_CHK.Check_Precision HANDLED EXCEPTION Return FALSE');
1293 Return FALSE;
1294 END;
1295
1296 /* =======================================================================
1297 | PROCEDURE Check_Line_Level_Transaction_Balance
1298 |
1299 | DESCRIPTION
1300 |
1301 | PARAMETERS
1302 | p_customer_trx_id IN Cash receipt id
1303 | p_called_from_api IN Y-api call out
1304 * ======================================================================*/
1305 PROCEDURE CHECK_LL_TRANSACTION_BALANCE(p_customer_trx_id IN VARCHAR2,
1306 p_called_from_api IN VARCHAR2 default 'N') IS
1307
1308 CURSOR C1(p_cust_trx_id NUMBER) IS
1309 select 'X'
1310 from ar_payment_schedules ps,
1311 ra_cust_trx_line_gl_dist gld,
1312 ra_customer_trx ct,
1313 (select customer_trx_id,
1314 sum(amount_due_remaining + nvl(chrg_amount_remaining, 0) + nvl(frt_adj_remaining, 0)) amount_due_remaining,
1315 sum(acctd_amount_due_remaining + nvl(chrg_acctd_amount_remaining, 0) + nvl(frt_adj_acctd_remaining, 0)) acctd_amount_due_remaining
1316 from ra_customer_trx_lines
1317 where customer_trx_id=decode(p_customer_trx_id,0,customer_trx_id,p_customer_trx_id)
1318 GROUP BY customer_trx_id) a
1319 where ct.upgrade_method='R12'
1320 and ct.customer_trx_id=decode(p_customer_trx_id,0,ct.customer_trx_id,p_customer_trx_id)
1321 and ps.customer_trx_id=ct.customer_trx_id
1322 and ct.customer_trx_id = a.customer_trx_id
1323 and ct.customer_trx_id = gld.customer_trx_id(+) --modified
1324 and gld.account_class(+) = 'ROUND'
1325 and((a.amount_due_remaining is not null
1326 and nvl(ps.amount_due_remaining,0) <> nvl(a.amount_due_remaining, 0))
1327 or
1328 (a.acctd_amount_due_remaining is not null
1329 and nvl(ps.acctd_amount_due_remaining-nvl(gld.acctd_amount,0),0)<>nvl(a.acctd_amount_due_remaining, 0) and ps.acctd_amount_due_remaining <> a.acctd_amount_due_remaining)) ;
1330
1331 l_amount NUMBER;
1332 l_acctd_amount NUMBER;
1333 l_no_balance VARCHAR2(1):= 'N';
1334 -- OKL LLCA Bug 6129910
1335 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1336 l_msg_data VARCHAR2(2000);
1337 l_msg_count NUMBER;
1338 l_customer_rec ra_customer_trx%ROWTYPE;
1339 l_gen_line_level_bal_flag VARCHAR(1) := 'N';
1340 l_complete_flag_db varchar2(1) := 'N';
1341 excep_set_org_rem_amt_r12 EXCEPTION;
1342
1343
1344 BEGIN
1345
1346 IF PG_DEBUG in ('Y', 'C') THEN
1347 arp_standard.debug('ARP_BALANCE_CHK.Check_Line_Level_Transaction_Balance (+)' );
1348 END IF;
1349
1350 IF p_customer_trx_id IS NOT NULL THEN
1351
1352
1353 IF PG_BAL_CHECK_ENABLED = 'Y' AND PG_LL_BAL_CHECK_ENABLED = 'Y' THEN
1354 FOR c2 in c1(p_customer_trx_id) LOOP
1355 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
1356 fnd_msg_pub.Add;
1357 RAISE out_of_balance;
1358 END LOOP;
1359
1360 ELSE
1361 IF PG_DEBUG in ('Y', 'C') THEN
1362 arp_standard.debug('Profile AR: Enable Journal Balance Check is disabled ');
1363 arp_standard.debug('Profile AR:Enable Line Level Journal Balance Check is disabled ');
1364 END IF;
1365 END IF;
1366 END IF; -- p_customer_trx_id is not null , bug6762463
1367
1368 IF PG_DEBUG in ('Y', 'C') THEN
1369 arp_standard.debug('ARP_BALANCE_CHK.Check_Line_Level_Transaction_Balance (-)' );
1370 END IF;
1371
1372 EXCEPTION
1373 WHEN out_of_balance THEN
1374 IF PG_DEBUG in ('Y', 'C') THEN
1375 arp_standard.debug('ARP_BALANCE_CHK.Check_Line_Level_Transaction_Balance - OUT_OF_BALANCE');
1376 END IF;
1377 fnd_message.set_name('AR','AR_AMOUNTS_NO_MATCH');
1378 app_exception.raise_exception;
1379 WHEN NO_DATA_FOUND THEN
1380 /* Case for invoice with rules */
1381 IF PG_DEBUG in ('Y', 'C') THEN
1382 arp_standard.debug( 'ARP_BALANCE_CHK.Check_Line_Level_Transaction_Balance - NO_DATA_FOUND' );
1383 END IF;
1384 WHEN excep_set_org_rem_amt_r12 THEN --LLCA
1385 IF PG_DEBUG in ('Y', 'C') THEN
1386 arp_standard.debug('EXCEPTION_set_original_rem_amt_r12 error count:'||l_msg_count);
1387 arp_standard.debug('last error:'||l_msg_data);
1388 END IF;
1389 RAISE;
1390
1391 WHEN OTHERS THEN
1392 IF PG_DEBUG in ('Y', 'C') THEN
1393 arp_standard.debug( 'ARP_BALANCE_CHK.Check_Line_Level_Transaction_Balance - OTHERS' );
1394 END IF;
1395 RAISE;
1396
1397 END CHECK_LL_TRANSACTION_BALANCE;
1398 END ARP_BALANCE_CHECK;
[Home] [Help]