[Home] [Help]
PACKAGE BODY: APPS.ARP_RATE_ADJ
Source
1 PACKAGE BODY arp_rate_adj AS
2 /* $Header: ARPLRADB.pls 120.25.12010000.2 2008/11/10 13:30:28 spdixit ship $ */
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8
9 --
10 --gscc change
11 PG_DEBUG varchar2(1);
12
13 PROCEDURE main(new_crid IN NUMBER,
14 new_ed IN DATE,
15 new_er IN NUMBER,
16 new_ert IN VARCHAR2,
17 new_gd IN DATE,
18 new_cb IN NUMBER,
19 new_cd IN DATE,
20 new_lub IN NUMBER,
21 new_lud IN DATE,
22 new_lul IN NUMBER,
23 touch_hist_and_dist IN BOOLEAN DEFAULT TRUE,
24 crh_id_out OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE)
25 IS
26 CURSOR cr_info (cr_id AR_CASH_RECEIPTS.CASH_RECEIPT_ID%TYPE) IS
27 SELECT cr.cash_receipt_id cash_receipt_id,
28 cr.set_of_books_id set_of_books_id,
29 cr.currency_code currency_code,
30 cr.receipt_method_id receipt_method_id,
31 sob.currency_code functional_currency,
32 cr.type type,
33 cr.amount amount,
34 ps.payment_schedule_id payment_schedule_id,
35 rma.unapplied_ccid unapplied_ccid,
36 rma.unidentified_ccid unidentified_ccid,
37 cr.tax_rate tax_rate,
38 cr.receivables_trx_id receivables_trx_id
39 FROM ar_cash_receipts cr,
40 ar_payment_schedules ps,
41 ar_receipt_method_accounts rma,
42 gl_sets_of_books sob
43 WHERE cr.cash_receipt_id = cr_id
44 AND ps.cash_receipt_id(+) = cr.cash_receipt_id
45 AND rma.receipt_method_id = cr.receipt_method_id
46 AND rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id
47 AND cr.set_of_books_id = sob.set_of_books_id
48 FOR UPDATE OF cr.exchange_date,
49 cr.exchange_rate,
50 cr.exchange_rate_type;
51
52 CURSOR crh_info (cr_id NUMBER) IS
53 SELECT *
54 FROM AR_CASH_RECEIPT_HISTORY
55 WHERE CASH_RECEIPT_ID = cr_id
56 AND CURRENT_RECORD_FLAG = 'Y';
57
58 CURSOR crh_prv_stat_info (cr_id NUMBER) IS
59 SELECT *
60 FROM AR_CASH_RECEIPT_HISTORY
61 WHERE CASH_RECEIPT_HISTORY_ID =
62 (SELECT PRV_STAT_CASH_RECEIPT_HIST_ID
63 FROM AR_CASH_RECEIPT_HISTORY
64 WHERE CASH_RECEIPT_ID = cr_id
65 AND CURRENT_RECORD_FLAG = 'Y');
66
67 CURSOR dis_info (cr_id NUMBER) IS
68 SELECT SUM(nvl(d.acctd_amount_dr,0)
69 - nvl(d.acctd_amount_cr,0)) sum_amount,
70 d.code_combination_id,
71 crh2.acctd_amount,
72 crh2.acctd_factor_discount_amount,
73 crh2.cash_receipt_history_id,
74 d.source_type,
75 d.source_table
76 FROM AR_CASH_RECEIPT_HISTORY crh,
77 AR_CASH_RECEIPT_HISTORY crh2,
78 AR_DISTRIBUTIONS d
79 WHERE crh.cash_receipt_id = cr_id
80 AND crh2.cash_receipt_id = cr_id
81 AND d.source_id = crh.cash_receipt_history_id
82 AND crh2.current_record_flag = 'Y'
83 AND d.source_table = 'CRH'
84 GROUP BY crh.cash_receipt_id,
85 d.source_type,
86 d.source_table,
87 d.code_combination_id,
88 crh2.acctd_amount,
89 crh2.acctd_factor_discount_amount,
90 crh2.cash_receipt_history_id /* Bug 4443931: Added OR Below */
91 HAVING SUM(nvl(d.acctd_amount_dr,0) - nvl(d.acctd_amount_cr,0)) <> 0
92 OR (SUM(nvl(d.acctd_amount_dr,0) - nvl(d.acctd_amount_cr,0)) = 0
93 AND SUM(nvl(d.amount_dr,0) - nvl(d.amount_cr,0)) <> 0 ) ;
94
95 CURSOR misc_info (cr_id AR_MISC_CASH_DISTRIBUTIONS.CASH_RECEIPT_ID%TYPE) IS
96 SELECT *
97 FROM AR_MISC_CASH_DISTRIBUTIONS
98 WHERE CASH_RECEIPT_ID = cr_id
99 AND REVERSAL_GL_DATE IS NULL;
100
101 CURSOR rec_app_info (cr_id AR_CASH_RECEIPTS.CASH_RECEIPT_ID%TYPE) IS
102 SELECT *
103 FROM AR_RECEIVABLE_APPLICATIONS
104 WHERE CASH_RECEIPT_ID = cr_id
105 AND REVERSAL_GL_DATE IS NULL
106 ORDER BY decode(status,
107 'APP' ,1,
108 'ACTIVITY' ,2,
109 'ACC' ,3,
110 'OTHER ACC' ,4,
111 'UNID' ,5,
112 'UNAPP' ,6); --This ordering is required for pairing UNAPP with APP, ACC or UNID rec record
113
114 CURSOR ps_remaining_info
115 (pay_id AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID%TYPE) IS
116 SELECT AMOUNT_DUE_REMAINING,
117 ACCTD_AMOUNT_DUE_REMAINING
118 FROM AR_PAYMENT_SCHEDULES
119 WHERE PAYMENT_SCHEDULE_ID = pay_id;
120
121 CURSOR get_acctd_amounts
122 (cr_id AR_PAYMENT_SCHEDULES.CASH_RECEIPT_ID%TYPE) IS
123 SELECT ARCH.ACCTD_AMOUNT,
124 ARCH.ACCTD_FACTOR_DISCOUNT_AMOUNT,
125 ARPS.ACCTD_AMOUNT_DUE_REMAINING
126 FROM AR_PAYMENT_SCHEDULES ARPS,
127 AR_CASH_RECEIPT_HISTORY ARCH
128 WHERE ARPS.CASH_RECEIPT_ID = cr_id
129 AND ARCH.CASH_RECEIPT_ID = cr_id
130 AND ARCH.CURRENT_RECORD_FLAG = 'Y';
131
132 CURSOR apps_with_claims
133 (cr_id AR_PAYMENT_SCHEDULES.CASH_RECEIPT_ID%TYPE) IS
134 SELECT SECONDARY_APPLICATION_REF_ID
135 FROM AR_RECEIVABLE_APPLICATIONS
136 WHERE CASH_RECEIPT_ID = cr_id
137 AND STATUS = 'APP'
138 AND APPLICATION_REF_TYPE = 'CLAIM'
139 AND NVL(TRANS_TO_RECEIPT_RATE,1) <> 1
140 AND SECONDARY_APPLICATION_REF_ID IS NOT NULL
141 AND DISPLAY = 'Y';
142
143 /*add cursors needed for CCR logic 1st- to get the payment type code to determine if this is a CCR and
144 2nd to get the receivable application id associated with the Negative Credit Card Miscellaneous receipt */
145
146 CURSOR ar_rm_c(p_receipt_method_id number) is
147 SELECT payment_channel_code
148 FROM ar_receipt_methods
149 WHERE receipt_method_id = p_receipt_method_id;
150
151 CURSOR ar_rc_rec(p_cr_id number) is
152 SELECT *
153 FROM ar_receivable_applications
154 WHERE application_ref_id = p_cr_id
155 and application_ref_type = 'MISC_RECEIPT'
156 and display = 'Y';
157
158 /* Bug 4112494 CM refunds */
159 CURSOR ar_rt_c(p_receivables_trx_id NUMBER) IS
160 SELECT type
161 FROM ar_receivables_trx
162 WHERE receivables_trx_id = p_receivables_trx_id;
163
164 l_rc_app ar_rc_rec%ROWTYPE;
165 l_rm_code ar_receipt_methods.payment_type_code%TYPE;
166 l_credit_card boolean := FALSE;
167 l_rt_type ar_receivables_trx.type%TYPE;
168 l_cm_refund BOOLEAN := FALSE;
169 ln_rec_application_id ar_receivable_applications.receivable_application_id%TYPE;
170 ln_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
171 ln_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
172 l_bal_due_remaining number;
173
174 l_app_ra_rec ar_receivable_applications%ROWTYPE; /* MRC */
175
176 /* end of modification in the declare section for CCR - pkt */
177
178 new_adj NewAdjTyp;
179 cr cr_info%ROWTYPE;
180 old_crh crh_info%ROWTYPE;
181 old_old_crh crh_info%ROWTYPE;
182 new_crh crh_info%ROWTYPE;
183 old_misc misc_info%ROWTYPE;
184 old_rec_app rec_app_info%ROWTYPE;
185 ps_remaining ps_remaining_info%ROWTYPE;
186 acctd get_acctd_amounts%ROWTYPE;
187
188 new_crh_id AR_CASH_RECEIPT_HISTORY.CASH_RECEIPT_HISTORY_ID%TYPE;
189 old_crh_id AR_CASH_RECEIPT_HISTORY.CASH_RECEIPT_HISTORY_ID%TYPE;
190 unapp_id AR_RECEIVABLE_APPLICATIONS.RECEIVABLE_APPLICATION_ID%TYPE;
191
192 dis DIS_INFO%ROWTYPE;
193 acctd_curr_amount AR_CASH_RECEIPT_HISTORY.ACCTD_AMOUNT%TYPE;
194 acctd_dis_amount AR_DISTRIBUTIONS.ACCTD_AMOUNT_DR%TYPE;
195 amount_cr NUMBER;
196 amount_dr NUMBER;
197 acctd_amount_cr NUMBER;
198 acctd_amount_dr NUMBER;
199
200 amt_due_remaining AR_MISC_CASH_DISTRIBUTIONS.AMOUNT%TYPE;
201 acctd_amt_due_remaining AR_MISC_CASH_DISTRIBUTIONS.ACCTD_AMOUNT%TYPE;
202 dist_acctd_amount AR_MISC_CASH_DISTRIBUTIONS.ACCTD_AMOUNT%TYPE;
203 new_acctd_amount AR_MISC_CASH_DISTRIBUTIONS.ACCTD_AMOUNT%TYPE;
204
205 app_acctd_amount AR_PAYMENT_SCHEDULES.ACCTD_AMOUNT_DUE_REMAINING%TYPE;
206 new_ps_acctd_amount AR_PAYMENT_SCHEDULES.ACCTD_AMOUNT_DUE_REMAINING%TYPE;
207
208 acctd_diff AR_CASH_RECEIPT_HISTORY.ACCTD_AMOUNT%TYPE;
209
210 temp_num NUMBER;
211
212 total_unid NUMBER := 0; -- running total of 'UNID' records
213 cr_acctd_amount NUMBER;
214
215 ins_ra_rec AR_RECEIVABLE_APPLICATIONS%ROWTYPE;
216 upd_ra_rec AR_RECEIVABLE_APPLICATIONS%ROWTYPE;
217 net_ra_rec AR_RECEIVABLE_APPLICATIONS%ROWTYPE;
218
219 -- Added for 11.5 VAT changes
220 l_cr_rec ar_cash_receipts%ROWTYPE;
221 l_dist_rec ar_distributions%ROWTYPE;
222 l_dummy ar_distributions.line_id%TYPE;
223 l_app_id ar_receivable_applications.receivable_application_id%TYPE;
224 l_ae_doc_rec ae_doc_rec_type;
225
226 -- Added for iClaim API calls
227 l_bill_to_site_id hz_cust_acct_sites.cust_acct_site_id%TYPE;
228 l_return_status VARCHAR2(1);
229 l_msg_count NUMBER;
230 l_msg_data VARCHAR2(2000);
231 l_mesg VARCHAR2(2000); --GSCC Change
232 l_claim_id NUMBER := NULL;
233 l_claim_amount NUMBER := NULL;
234 l_claim_number VARCHAR2(30);
235 l_claim_reason_code_id NUMBER;
236 l_claim_reason_name VARCHAR2(80);
237
238 --Added for write-off
239 l_max_wrt_off_amount ar_system_parameters.max_wrtoff_amount%TYPE;
240 -- Bug 2076743
241 l_claim_status VARCHAR2(30);
242 l_new_rec_app_id NUMBER;
243 l_new_net_rec_app_id NUMBER;
244 l_exchange_rate NUMBER;
245
246 --Added for Bug No.3682777
247 l_inv_gl_date_closed ar_payment_schedules.gl_date_closed%TYPE;
248 l_inv_ps_status ar_payment_schedules.status%TYPE;
249
250 --Added for Bug No.3713101
251 l_rct_gl_date_closed ar_payment_schedules.gl_date_closed%TYPE;
252 l_rct_ps_status ar_payment_schedules.status%TYPE;
253 claim_cancel_api_error EXCEPTION;
254 claim_create_api_error EXCEPTION;
255
256 --Bug#2750340
257 l_xla_ev_rec arp_xla_events.xla_events_type;
258
259 --BUG#5022786
260 CURSOR c_trx(p_trx_id IN NUMBER) IS
261 SELECT upgrade_method
262 FROM ra_customer_trx
263 WHERE customer_trx_id = p_trx_id;
264
265 l_gt_id NUMBER;
266 x_return_status VARCHAR2(10);
267 x_msg_count NUMBER;
268 x_msg_data VARCHAR2(2000);
269 l_upgrade_methode VARCHAR2(30);
270
271
272 --BUG#5201086
273 old_crh_reverse_rec ar_cash_receipt_history_all%ROWTYPE;
274 old_reverse_crh_id NUMBER;
275 CURSOR c_distrib(p_source_id IN NUMBER,
276 p_source_table IN VARCHAR2,
277 p_status IN VARCHAR2)
278 IS
279 SELECT *
280 FROM ar_distributions
281 WHERE source_table = 'CRH'
282 AND source_id = p_source_id
283 AND ((DECODE(p_status,'CONFIRMED',DECODE(source_type,'CONFIRMATION','Y','N'),
284 'REMITTED' ,DECODE(source_type,'REMITTANCE','Y','N'),
285 'CLEARED' ,DECODE(source_type,'CASH','Y','N'),
286 'RISK_ELIMINATED',DECODE(source_type,'FACTOR','Y',
287 'SHORT_TERM_DEBT','Y','N')) = 'Y')
288 OR
289 (source_type = 'BANK_CHARGES'));
290
291
292 l_distrib_rec c_distrib%ROWTYPE;
293 l_old_acctd_amount NUMBER;
294
295 CURSOR c_rate_adj
296 (p_cr_id IN NUMBER,
297 p_rate_adjustment_id IN NUMBER)
298 IS
299 SELECT RATE_ADJUSTMENT_ID ,
300 CASH_RECEIPT_ID ,
301 OLD_EXCHANGE_RATE ,
302 NEW_EXCHANGE_RATE ,
303 OLD_EXCHANGE_RATE_TYPE,
304 NEW_EXCHANGE_RATE_TYPE,
305 OLD_EXCHANGE_DATE ,
306 NEW_EXCHANGE_DATE ,
307 GAIN_LOSS ,
308 GL_DATE ,
309 GL_POSTED_DATE
310 FROM ar_rate_adjustments
311 WHERE cash_receipt_id = p_cr_id
312 AND rate_adjustment_id = p_rate_adjustment_id;
313
314 l_rate_adj_rec c_rate_adj%ROWTYPE;
315 l_rate_adjustment_id NUMBER;
316 l_old_new_indicator VARCHAR2(30);
317
318 --{HYU_Rate_Adj
319 CURSOR c_trx_gt(p_customer_trx_id IN NUMBER)
320 IS
321 SELECT COUNT(CUSTOMER_TRX_LINE_ID),
322 CUSTOMER_TRX_LINE_ID
323 FROM ra_customer_trx_lines_gt
324 WHERE CUSTOMER_TRX_ID = p_customer_trx_id
325 GROUP BY CUSTOMER_TRX_LINE_ID;
326
327 l_cnt_line DBMS_SQL.NUMBER_TABLE;
328 l_line_tab DBMS_SQL.NUMBER_TABLE;
329 l_reset_rem VARCHAR2(1) := 'N';
330
331 CURSOR c_trx_rem_gt(p_customer_trx_id IN NUMBER)
332 IS
333 SELECT ACCTD_AMOUNT_DUE_REMAINING ,
334 AMOUNT_DUE_REMAINING ,
335 CHRG_ACCTD_AMOUNT_REMAINING,
336 CHRG_AMOUNT_REMAINING ,
337 FRT_ADJ_ACCTD_REMAINING ,
338 FRT_ADJ_REMAINING ,
339 FRT_ED_ACCTD_AMOUNT ,
340 FRT_ED_AMOUNT ,
341 FRT_UNED_ACCTD_AMOUNT ,
342 FRT_UNED_AMOUNT ,
343 customer_trx_line_id
344 FROM ra_customer_trx_lines
345 WHERE customer_trx_id = p_customer_trx_id;
346
347 l_ACCTD_AMOUNT_DUE_REMAINING DBMS_SQL.NUMBER_TABLE;
348 l_AMOUNT_DUE_REMAINING DBMS_SQL.NUMBER_TABLE;
349 l_CHRG_ACCTD_AMOUNT_REMAINING DBMS_SQL.NUMBER_TABLE;
350 l_CHRG_AMOUNT_REMAINING DBMS_SQL.NUMBER_TABLE;
351 l_FRT_ADJ_ACCTD_REMAINING DBMS_SQL.NUMBER_TABLE;
352 l_FRT_ADJ_REMAINING DBMS_SQL.NUMBER_TABLE;
353 l_FRT_ED_ACCTD_AMOUNT DBMS_SQL.NUMBER_TABLE;
354 l_FRT_ED_AMOUNT DBMS_SQL.NUMBER_TABLE;
355 l_FRT_UNED_ACCTD_AMOUNT DBMS_SQL.NUMBER_TABLE;
356 l_FRT_UNED_AMOUNT DBMS_SQL.NUMBER_TABLE;
357 l_customer_trx_line_id DBMS_SQL.NUMBER_TABLE;
358 g_ae_sys_rec arp_acct_main.ae_sys_rec_type;
359 BEGIN
360 --
361 IF PG_DEBUG in ('Y', 'C') THEN
362 arp_standard.debug( '>> ARBRAD MAIN' );
363 arp_standard.debug( 'new_crid :'||new_crid);
364 arp_standard.debug( 'new_ed :'||new_ed);
365 arp_standard.debug( 'new_er :'||new_er);
366 arp_standard.debug( 'new_ert :'||new_ert);
367 arp_standard.debug( 'new_gd :'||new_gd);
368 arp_standard.debug( 'new_cb :'||new_cb);
369 arp_standard.debug( 'new_cd :'||new_cd);
370 arp_standard.debug( 'new_lub :'||new_lub);
371 arp_standard.debug( 'new_lud :'||new_lud);
372 arp_standard.debug( 'new_lul :'||new_lul);
373 -- arp_standard.debug( 'p_rate_adjustment_id :'||p_rate_adjustment_id);
374 IF touch_hist_and_dist THEN
375 arp_standard.debug( 'touch_hist_and_dist : TRUE');
376 ELSE
377 arp_standard.debug( 'touch_hist_and_dist : FALSE');
378 END IF;
379 END IF;
380
381 l_mesg := '';
382
383 new_adj.cash_receipt_id := new_crid;
384 new_adj.new_exchange_rate := new_er;
385 new_adj.new_exchange_date := new_ed;
386 new_adj.new_exchange_rate_type := new_ert;
387 new_adj.gl_date := new_gd;
388 new_adj.created_by := new_cb;
389 new_adj.creation_date := new_cd;
390 new_adj.last_updated_by := new_lub;
391 new_adj.last_update_date := new_lud;
392 new_adj.last_update_login := new_lul;
393
394 /*-----------------------*
395 | Get Cash Receipt Info |
396 *-----------------------*/
397 OPEN cr_info(new_adj.cash_receipt_id);
398 FETCH cr_info INTO cr;
399 CLOSE cr_info;
400
401
402 --BUG#5201086 get_rate_adjustment info
403 -- IF p_rate_adjustment_id IS NOT NULL THEN
404 -- OPEN c_rate_adj(new_crid,p_rate_adjustment_id);
405 -- FETCH c_rate_adj INTO l_rate_adj_rec;
406 -- CLOSE c_rate_adj;
407 -- END IF;
408
409 /*----------------------------------------------------------*
410 | Check if receipt has claims and if they can be cancelled |
411 *----------------------------------------------------------*/
412 FOR clrec in apps_with_claims(new_adj.cash_receipt_id) LOOP
413 -- Bug 2076743 - cater for cancelled claims
414 -- Bug 2353144 - use check_cancel_deduction instead of status OPEN
415 -- to determine if claim is cancellable
416 IF NOT OZF_Claim_GRP.Check_Cancell_Deduction(
417 p_claim_id => clrec.secondary_application_ref_id)
418 THEN
419 FND_MESSAGE.SET_NAME('AR', 'AR_RW_APP_NO_NEW_RATE_IF_CLAIM');
420 IF PG_DEBUG in ('Y', 'C') THEN
421 arp_standard.debug('Receipt has uncancellable claims - ARP_RATE_ADJ.MAIN' );
422 END IF;
423 app_exception.raise_exception;
424 END IF;
425 END LOOP;
426
427 /*-------------------------------------------*
428 | Fetch current Cash receipt History Record |
429 *-------------------------------------------*/
430
431 IF ( touch_hist_and_dist ) THEN
432 OPEN crh_info(new_adj.cash_receipt_id);
433 FETCH crh_info INTO old_crh;
434 CLOSE crh_info;
435 ELSE
436 OPEN crh_info(new_adj.cash_receipt_id);
437 FETCH crh_info INTO new_crh;
438 CLOSE crh_info;
439
440 new_crh_id := new_crh.cash_receipt_history_id;
441 crh_id_out := new_crh_id;
442
443 OPEN crh_prv_stat_info(new_adj.cash_receipt_id);
444 FETCH crh_prv_stat_info INTO old_crh;
445 CLOSE crh_prv_stat_info;
446 END IF;
447
448 old_old_crh := old_crh;
449 old_crh_id := old_crh.cash_receipt_history_id;
450
451
452
453
454 /*-----------------------------------------*
455 | Create New Cash Receipts History Record |
456 *-----------------------------------------*/
457 IF (touch_hist_and_dist ) THEN
458
459 -- Accounted amount for the CRH record except BANK_CHARGES
460 new_crh.amount := old_crh.amount;
461 l_old_acctd_amount := old_crh.acctd_amount;
462
463
464 -- Accounted amount for the CRH record for BANK_CHARGES
465 new_crh.factor_discount_amount := old_crh.factor_discount_amount;
466 old_crh.acctd_amount := arp_standard.functional_amount(old_crh.amount,
467 cr.functional_currency,
468 new_adj.new_exchange_rate,
469 NULL,
470 NULL);
471
472 new_crh.acctd_amount := old_crh.acctd_amount;
473 old_crh.gl_date := new_adj.gl_date;
474 new_crh.gl_date := old_crh.gl_date;
475
476 /* This is a design change:
477 The new cash receipt history trx_date should remain the
478 same as what it was before */
479 old_crh.trx_date := old_crh.trx_date;
480 new_crh.trx_date := old_crh.trx_date;
481
482 IF (old_crh.factor_discount_amount IS NULL) THEN
483 old_crh.acctd_factor_discount_amount := NULL;
484 new_crh.acctd_factor_discount_amount := old_crh.acctd_factor_discount_amount;
485 ELSE
486 old_crh.acctd_factor_discount_amount :=
487 arp_standard.functional_amount(old_crh.amount +
488 old_crh.factor_discount_amount,
489 cr.functional_currency,
490 new_adj.new_exchange_rate,
491 NULL,
492 NULL) - old_crh.acctd_amount;
493 new_crh.acctd_factor_discount_amount := old_crh.acctd_factor_discount_amount;
494 END IF;
495
496 old_crh.first_posted_record_flag := 'N';
497 old_crh.current_record_flag := 'Y';
498 old_crh.exchange_date := new_adj.new_exchange_date;
499 old_crh.exchange_rate := new_adj.new_exchange_rate;
500 old_crh.exchange_rate_type := new_adj.new_exchange_rate_type;
501 old_crh.gl_posted_date := NULL;
502 old_crh.posting_control_id := -3;
503 old_crh.reversal_cash_receipt_hist_id := NULL;
504 old_crh.reversal_gl_date := NULL;
505 old_crh.reversal_gl_posted_date := NULL;
506 old_crh.reversal_posting_control_id := NULL;
507 old_crh.request_id := NULL;
508 old_crh.program_application_id := NULL;
509 old_crh.program_id := NULL;
510 old_crh.program_update_date := NULL;
511 old_crh.created_by := new_adj.created_by;
512 old_crh.creation_date := new_adj.creation_date;
513 old_crh.last_updated_by := new_adj.last_updated_by;
514 old_crh.last_update_date := new_adj.last_update_date;
515 old_crh.last_update_login := new_adj.last_update_login;
516 old_crh.created_from := 'RATE ADJUSTMENT TRIGGER';
517
518 arp_standard.debug('Insert the new cash_receipt_history record');
519 --HYU use the new for rate_adj
520 new_crh_id := arp_cash_receipt_history.InsertRecord
521 (amount => old_crh.amount,
522 acctd_amount => old_crh.acctd_amount,
523 cash_receipt_id => old_crh.cash_receipt_id,
524 factor_flag => old_crh.factor_flag,
525 first_posted_record_flag => old_crh.first_posted_record_flag,
526 gl_date => old_crh.gl_date,
527 postable_flag => old_crh.postable_flag,
528 status => old_crh.status,
529 trx_date => old_crh.trx_date,
530 acctd_factor_discount_amount => old_crh.acctd_factor_discount_amount,
531 account_code_combination_id => old_crh.account_code_combination_id,
532 bank_charge_account_ccid => old_crh.bank_charge_account_ccid,
533 batch_id => old_crh.batch_id,
534 current_record_flag => old_crh.current_record_flag,
535 exchange_date => old_crh.exchange_date,
536 exchange_rate => old_crh.exchange_rate,
537 exchange_rate_type => old_crh.exchange_rate_type,
538 factor_discount_amount => old_crh.factor_discount_amount,
539 gl_posted_date => old_crh.gl_posted_date,
540 posting_control_id => old_crh.posting_control_id,
541 reversal_cash_rec_hist_id => old_crh.reversal_cash_receipt_hist_id,
542 reversal_gl_date => old_crh.reversal_gl_date,
543 reversal_gl_posted_date => old_crh.reversal_gl_posted_date,
544 reversal_posting_control_id => old_crh.reversal_posting_control_id,
545 request_id => old_crh.request_id,
546 program_application_id => old_crh.program_application_id,
547 program_id => old_crh.program_id,
548 program_update_date => old_crh.program_update_date,
549 created_by => old_crh.created_by,
550 creation_date => old_crh.creation_date,
551 last_updated_by => old_crh.last_updated_by,
552 last_update_date => old_crh.last_update_date,
553 last_update_login => old_crh.last_update_login,
554 prv_stat_cash_rec_hist_id => old_crh.prv_stat_cash_receipt_hist_id,
555 created_from => old_crh.created_from,
556 reversal_created_from => old_crh.reversal_created_from);
557
558 crh_id_out := new_crh_id;
559 arp_standard.debug('crh_id_out:'||crh_id_out);
560
561 /*------------------------------------------------*
562 | Close out NOCOPY current Cash Receipts History Record |
563 *------------------------------------------------*/
564 arp_standard.debug('Update the rate_adjustments info on the old cash_receipt_history record');
565 --HYU rate adjustment OLD
566 arp_cash_receipt_history.Reverse(new_crh_id,
567 old_crh.gl_date,
568 old_crh_id,
569 new_adj.last_updated_by,
570 new_adj.last_update_date,
571 new_adj.last_update_login);
572
573 --BUG#2750340
574 l_xla_ev_rec.xla_from_doc_id := old_crh.cash_receipt_id;
575 l_xla_ev_rec.xla_to_doc_id := old_crh.cash_receipt_id;
576 l_xla_ev_rec.xla_doc_table := 'CRH';
577 l_xla_ev_rec.xla_mode := 'O';
578 l_xla_ev_rec.xla_call := 'B';
579 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
580 END IF;
581
582
583 /*----------------------------------------------------------------*
584 | Update Cash Receipts record with New exchange rate Information |
585 *----------------------------------------------------------------*/
586 arp_standard.debug('Update Cash Receipts record with New exchange rate Information');
587 arp_cash_rec.upd_cash_receipts(new_adj.new_exchange_date,
588 new_adj.new_exchange_rate,
589 new_adj.new_exchange_rate_type,
590 new_adj.cash_receipt_id,
591 new_adj.last_updated_by,
592 new_adj.last_update_date,
593 new_adj.last_update_login);
594
595
596 /*----------------------------------------------------------------*
597 | Create New Distributions Records |
598 *----------------------------------------------------------------*/
599 IF (touch_hist_and_dist ) THEN
600
601 -- Since we have the cash_receipt_id, we can get the
602 -- cash receipt information.
603 l_cr_rec.cash_receipt_id := new_adj.cash_receipt_id;
604 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
605
606
607 ---{Start Obsolete after testing
608 -- HYU in R12 we need to reverse backout the last CRH record at last stage
609 -- Cases:
610 -- Cash Receipt created as Confirmed
611 -- CONF(CRH) UNAPP(RA)
612 -- REM(CRH1) CONF(CRH1)
613 -- CASH(CRH2) REM(CRH2)
614 -- If applications
615 -- UNAPP(RA1) APP(RA2)
616 -- Here we need to backout the last CRH record at last stage
617 /*
618 -- 11.5 VAT changes:
619 -- using the new_adj exchange info because this is what is
620 -- is used to create new crh row.
621 l_dist_rec.currency_code := l_cr_rec.currency_code;
622 l_dist_rec.currency_conversion_rate := new_adj.new_exchange_rate;
623 l_dist_rec.currency_conversion_type := new_adj.new_exchange_rate_type;
624 l_dist_rec.currency_conversion_date := new_adj.new_exchange_date;
625 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
626 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
627
628 OPEN dis_info(new_adj.cash_receipt_id);
629 LOOP
630 FETCH dis_info INTO dis;
631 EXIT WHEN dis_info%NOTFOUND OR dis_info%NOTFOUND IS NULL;
632
633
634 IF dis.source_type IN ('CONFIRMATION', 'REMITTANCE', 'FACTOR',
635 'SHORT_TERM_DEBT') THEN
636 acctd_curr_amount := nvl(dis.acctd_amount,0) +
637 nvl(dis.acctd_factor_discount_amount,0);
638 END IF;
639 --
640 IF dis.source_type = 'CASH' THEN
641 acctd_curr_amount := nvl(dis.acctd_amount,0);
642 END IF;
643 --
644 IF dis.source_type = 'BANK_CHARGES' THEN
645 acctd_curr_amount := nvl(dis.acctd_factor_discount_amount,0);
646 END IF;
647 --
648
649 -- Find the absolute amount of difference between what the amount
650 -- should be and what is already in the database.
651
652 acctd_dis_amount := abs(nvl(acctd_curr_amount,0)) -
653 abs(nvl(dis.sum_amount,0));
654 --
655 -- If acctd_dis_amount is less than 0, then the amount should
656 -- be a debit if the original sum_amount is a credit and vice
657 -- versa. If acctd_dis_amount is greater than or equal to 0,
658 -- then it should be on the same debit or credit side as the
659 -- original sum_amount.
660
661 IF acctd_dis_amount < 0 THEN
662 IF nvl(dis.sum_amount,0) < 0 THEN
663 acctd_amount_dr := -acctd_dis_amount;
664 acctd_amount_cr := NULL;
665 amount_dr := 0;
666 amount_cr := NULL;
667 ELSE
668 acctd_amount_dr := NULL;
669 acctd_amount_cr := -acctd_dis_amount;
670 amount_dr := NULL;
671 amount_cr := 0;
672 END IF;
673
674 ELSE
675 IF nvl(dis.sum_amount,0) < 0 THEN
676 acctd_amount_dr := NULL;
677 acctd_amount_cr := acctd_dis_amount;
678 amount_dr := NULL;
679 amount_cr := 0;
680 ELSE
681 acctd_amount_dr := acctd_dis_amount;
682 acctd_amount_cr := NULL;
683 amount_dr := 0;
684 amount_cr := NULL;
685 END IF;
686 END IF;
687
688 -- Populate the l_dis_rec with the correct values:
689 l_dist_rec.amount_dr := amount_dr;
690 l_dist_rec.amount_cr := amount_cr;
691 l_dist_rec.acctd_amount_dr := acctd_amount_dr;
692 l_dist_rec.acctd_amount_cr := acctd_amount_cr;
693 l_dist_rec.code_combination_id := dis.code_combination_id;
694 l_dist_rec.source_table := dis.source_table;
695 l_dist_rec.source_type := dis.source_type;
696 l_dist_rec.source_id := new_crh_id;
697 l_dist_rec.last_update_date := new_lud;
698 l_dist_rec.last_updated_by := new_lub;
699 l_dist_rec.last_update_login := new_lul;
700 l_dist_rec.creation_date := new_cd;
701 l_dist_rec.created_by := new_cb;
702 arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
703 -- store line_id in the dist record for use in mrc call
704 l_dist_rec.line_id := l_dummy;
705 END LOOP;
706 --
707 CLOSE dis_info;
708 */
709 --} Obsoleted after testing end
710
711
712 --HYU distribution for the new cash receipt history record with the new exchange rate info
713
714 arp_standard.debug('Create distributions for the new CRH reord');
715 OPEN c_distrib(old_crh.cash_receipt_history_id,'CRH',old_crh.status);
716 LOOP
717 FETCH c_distrib INTO l_distrib_rec;
718 EXIT WHEN c_distrib%NOTFOUND;
719
720 l_dist_rec.LINE_ID := l_distrib_rec.line_id;
721 l_dist_rec.SOURCE_ID := l_distrib_rec.source_id;
722 l_dist_rec.SOURCE_TABLE := l_distrib_rec.source_table;
723 l_dist_rec.SOURCE_TYPE := l_distrib_rec.source_type;
724 l_dist_rec.CODE_COMBINATION_ID := l_distrib_rec.code_combination_id;
725 l_dist_rec.ORG_ID := l_distrib_rec.org_id;
726 l_dist_rec.SOURCE_TABLE_SECONDARY := l_distrib_rec.source_table_secondary;
727 l_dist_rec.SOURCE_ID_SECONDARY := l_distrib_rec.source_id_secondary;
728 l_dist_rec.CURRENCY_CODE := l_distrib_rec.currency_code;
729 l_dist_rec.THIRD_PARTY_ID := l_distrib_rec.third_party_id;
730 l_dist_rec.THIRD_PARTY_SUB_ID := l_distrib_rec.third_party_sub_id;
731 l_dist_rec.REVERSED_SOURCE_ID := l_distrib_rec.reversed_source_id;
732 l_dist_rec.TAX_CODE_ID := l_distrib_rec.tax_code_id;
733 l_dist_rec.LOCATION_SEGMENT_ID := l_distrib_rec.location_segment_id;
734 l_dist_rec.SOURCE_TYPE_SECONDARY := l_distrib_rec.source_type_secondary;
735 l_dist_rec.TAX_GROUP_CODE_ID := l_distrib_rec.tax_group_code_id;
736 l_dist_rec.REF_CUSTOMER_TRX_LINE_ID := l_distrib_rec.ref_customer_trx_line_id;
737 l_dist_rec.REF_CUST_TRX_LINE_GL_DIST_ID:= l_distrib_rec.ref_cust_trx_line_gl_dist_id;
738 l_dist_rec.REF_ACCOUNT_CLASS := l_distrib_rec.ref_account_class;
739 l_dist_rec.ACTIVITY_BUCKET := l_distrib_rec.activity_bucket;
740 l_dist_rec.REF_LINE_ID := l_distrib_rec.ref_line_id;
741
742 IF l_dist_rec.SOURCE_TYPE = 'BANK_CHARGES' THEN
743 IF old_crh.factor_discount_amount > 0 THEN
744 l_dist_rec.amount_cr := NULL;
745 l_dist_rec.amount_dr := old_crh.factor_discount_amount;
746 ELSE
747 l_dist_rec.amount_cr := ABS(old_crh.factor_discount_amount);
748 l_dist_rec.amount_dr := NULL;
749 END IF;
750 ELSE
751 -- Case l_dist_rec.SOURCE_TYPE IN 'CONFIRMATION',
752 -- 'REMITTANCE',
753 -- 'FACTOR',
754 -- 'SHORT_TERM_DEBT',
755 -- 'CASH'
756 IF old_crh.amount > 0 THEN
757 l_dist_rec.amount_cr := NULL;
758 l_dist_rec.amount_dr := old_crh.amount;
759 ELSE
760 l_dist_rec.amount_cr := ABS(old_crh.amount);
761 l_dist_rec.amount_dr := NULL;
762 END IF;
763 END IF;
764
765 IF l_dist_rec.SOURCE_TYPE = 'BANK_CHARGES' THEN
766 IF old_crh.acctd_factor_discount_amount > 0 THEN
767 l_dist_rec.acctd_amount_cr := NULL;
768 l_dist_rec.acctd_amount_dr := old_crh.acctd_factor_discount_amount;
769 ELSE
770 l_dist_rec.acctd_amount_cr := ABS(old_crh.acctd_factor_discount_amount);
771 l_dist_rec.acctd_amount_dr := NULL;
772 END IF;
773 ELSE
774 -- Case l_dist_rec.SOURCE_TYPE IN 'CONFIRMATION',
775 -- 'REMITTANCE',
776 -- 'FACTOR',
777 -- 'SHORT_TERM_DEBT',
778 -- 'CASH'
779 IF old_crh.acctd_amount > 0 THEN
780 l_dist_rec.acctd_amount_cr := NULL;
781 l_dist_rec.acctd_amount_dr := old_crh.acctd_amount;
782 ELSE
783 l_dist_rec.acctd_amount_cr := ABS(old_crh.acctd_amount);
784 l_dist_rec.acctd_amount_dr := NULL;
785 END IF;
786 END IF;
787
788 l_dist_rec.currency_code := l_cr_rec.currency_code;
789 l_dist_rec.currency_conversion_rate := new_adj.new_exchange_rate;
790 l_dist_rec.currency_conversion_type := new_adj.new_exchange_rate_type;
791 l_dist_rec.currency_conversion_date := new_adj.new_exchange_date;
792 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
793 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
794 l_dist_rec.source_id := new_crh_id;
795 l_dist_rec.last_update_date := new_lud;
796 l_dist_rec.last_updated_by := new_lub;
797 l_dist_rec.last_update_login := new_lul;
798 l_dist_rec.creation_date := new_cd;
799 l_dist_rec.created_by := new_cb;
800
801 arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
802
803 -- store line_id in the dist record for use in mrc call
804 l_dist_rec.line_id := l_dummy;
805 END LOOP;
806 CLOSE c_distrib;
807
808
809
810 /* Bug fix 3193590 */
811 /* If the net receipt amount is zero, we need to insert the
812 distribution amount separately */
813 /*
814 IF old_crh.amount = 0 AND old_crh.status <> 'APPROVED' THEN
815 IF PG_DEBUG in ('Y', 'C') THEN
816 arp_standard.debug(' Receipt Amount is zero.');
817 arp_standard.debug('old_crh_id = '||to_char(old_crh_id));
818 arp_standard.debug('new_crh_id = '||to_char(new_crh_id));
819 END IF;
820 SELECT decode( old_crh.status,
821 'CONFIRMED','CONFIRMATION',
822 'REMITTED', 'REMITTANCE',
823 'CLEARED', 'CASH' )
824 INTO l_dist_rec.source_type
825 FROM dual;
826 l_dist_rec.amount_dr := 0;
827 l_dist_rec.amount_cr := NULL;
828 l_dist_rec.acctd_amount_dr := 0;
829 l_dist_rec.acctd_amount_cr := NULL;
830 l_dist_rec.source_table := 'CRH';
831 l_dist_rec.code_combination_id := old_crh.account_code_combination_id;
832 l_dist_rec.source_id := new_crh_id;
833 l_dist_rec.last_update_date := new_lud;
834 l_dist_rec.last_updated_by := new_lub;
835 l_dist_rec.last_update_login := new_lul;
836 l_dist_rec.creation_date := new_cd;
837 l_dist_rec.created_by := new_cb;
838
839 arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
840 IF PG_DEBUG in ('Y', 'C') THEN
841 arp_standard.debug('line_id = ' ||to_char(l_dummy));
842 END IF;
843
844 l_dist_rec.line_id := l_dummy;
845 END IF;
846 */
847
848
849
850 arp_standard.debug('Create the CRH record for reversing the CRH before rate adjustment');
851
852 old_crh_reverse_rec := old_old_crh;
853
854 old_crh_reverse_rec.amount := -old_crh_reverse_rec.amount;
855 old_crh_reverse_rec.factor_discount_amount := -old_crh_reverse_rec.factor_discount_amount;
856 old_crh_reverse_rec.acctd_amount := -old_crh_reverse_rec.acctd_amount;
857 old_crh_reverse_rec.gl_date := new_adj.gl_date;
858 old_crh_reverse_rec.acctd_factor_discount_amount := -old_crh_reverse_rec.acctd_factor_discount_amount;
859 old_crh_reverse_rec.first_posted_record_flag := 'N';
860 old_crh_reverse_rec.current_record_flag := 'N';
861 old_crh_reverse_rec.gl_posted_date := NULL;
862 old_crh_reverse_rec.posting_control_id := -3;
863 old_crh_reverse_rec.reversal_cash_receipt_hist_id := NULL;
864 old_crh_reverse_rec.reversal_gl_date := NULL;
865 old_crh_reverse_rec.reversal_gl_posted_date := NULL;
866 old_crh_reverse_rec.reversal_posting_control_id := NULL;
867 old_crh_reverse_rec.request_id := NULL;
868 old_crh_reverse_rec.program_application_id := NULL;
869 old_crh_reverse_rec.program_id := NULL;
870 old_crh_reverse_rec.program_update_date := NULL;
871 old_crh_reverse_rec.created_by := new_adj.created_by;
872 old_crh_reverse_rec.creation_date := new_adj.creation_date;
873 old_crh_reverse_rec.last_updated_by := new_adj.last_updated_by;
874 old_crh_reverse_rec.last_update_date := new_adj.last_update_date;
875 old_crh_reverse_rec.last_update_login := new_adj.last_update_login;
876 old_crh_reverse_rec.created_from := 'RATE ADJUSTMENT TRIGGER';
877
878
879 old_reverse_crh_id := arp_cash_receipt_history.InsertRecord
880 (amount => old_crh_reverse_rec.amount,
881 acctd_amount => old_crh_reverse_rec.acctd_amount,
882 cash_receipt_id => old_crh_reverse_rec.cash_receipt_id,
883 factor_flag => old_crh_reverse_rec.factor_flag,
884 first_posted_record_flag => old_crh_reverse_rec.first_posted_record_flag,
885 gl_date => old_crh_reverse_rec.gl_date,
886 postable_flag => old_crh_reverse_rec.postable_flag,
887 status => old_crh_reverse_rec.status,
888 trx_date => old_crh_reverse_rec.trx_date,
889 acctd_factor_discount_amount => old_crh_reverse_rec.acctd_factor_discount_amount,
890 account_code_combination_id => old_crh_reverse_rec.account_code_combination_id,
891 bank_charge_account_ccid => old_crh_reverse_rec.bank_charge_account_ccid,
892 batch_id => old_crh_reverse_rec.batch_id,
893 current_record_flag => old_crh_reverse_rec.current_record_flag,
894 exchange_date => old_crh_reverse_rec.exchange_date,
895 exchange_rate => old_crh_reverse_rec.exchange_rate,
896 exchange_rate_type => old_crh_reverse_rec.exchange_rate_type,
897 factor_discount_amount => old_crh_reverse_rec.factor_discount_amount,
898 gl_posted_date => old_crh_reverse_rec.gl_posted_date,
899 posting_control_id => old_crh_reverse_rec.posting_control_id,
900 reversal_cash_rec_hist_id => old_crh_reverse_rec.reversal_cash_receipt_hist_id,
901 reversal_gl_date => old_crh_reverse_rec.reversal_gl_date,
902 reversal_gl_posted_date => old_crh_reverse_rec.reversal_gl_posted_date,
903 reversal_posting_control_id => old_crh_reverse_rec.reversal_posting_control_id,
904 request_id => old_crh_reverse_rec.request_id,
905 program_application_id => old_crh_reverse_rec.program_application_id,
906 program_id => old_crh_reverse_rec.program_id,
907 program_update_date => old_crh_reverse_rec.program_update_date,
908 created_by => old_crh_reverse_rec.created_by,
909 creation_date => old_crh_reverse_rec.creation_date,
910 last_updated_by => old_crh_reverse_rec.last_updated_by,
911 last_update_date => old_crh_reverse_rec.last_update_date,
912 last_update_login => old_crh_reverse_rec.last_update_login,
913 prv_stat_cash_rec_hist_id => old_crh_reverse_rec.prv_stat_cash_receipt_hist_id,
914 created_from => old_crh_reverse_rec.created_from,
915 reversal_created_from => old_crh_reverse_rec.reversal_created_from);
916
917
918 arp_standard.debug('Creating the offset distributions');
919 OPEN c_distrib(old_old_crh.cash_receipt_history_id,'CRH',old_old_crh.status);
920 LOOP
921 FETCH c_distrib INTO l_distrib_rec;
922 EXIT WHEN c_distrib%NOTFOUND;
923 l_dist_rec := l_distrib_rec;
924 l_dist_rec.SOURCE_ID := old_reverse_crh_id;
925
926 l_dist_rec.amount_cr := l_distrib_rec.amount_dr;
927 l_dist_rec.amount_dr := l_distrib_rec.amount_cr;
928 l_dist_rec.acctd_amount_cr := l_distrib_rec.acctd_amount_dr;
929 l_dist_rec.acctd_amount_dr := l_distrib_rec.acctd_amount_cr;
930 l_dist_rec.last_update_date := new_lud;
931 l_dist_rec.last_updated_by := new_lub;
932 l_dist_rec.last_update_login := new_lul;
933 l_dist_rec.creation_date := new_cd;
934 l_dist_rec.created_by := new_cb;
935 arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
936
937 l_dist_rec.line_id := l_dummy;
938 END LOOP;
939 CLOSE c_distrib;
940
941
942 --Bug#2750340
943 l_xla_ev_rec.xla_from_doc_id := new_adj.cash_receipt_id;
944 l_xla_ev_rec.xla_to_doc_id := new_adj.cash_receipt_id;
945 l_xla_ev_rec.xla_doc_table := 'CRH';
946 l_xla_ev_rec.xla_mode := 'O';
947 l_xla_ev_rec.xla_call := 'B';
948 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
949 END IF;
950
951
952
953 IF (cr.type = 'MISC') THEN
954 /*---------------------------------------------------------*
955 | Create New Distributions for Miscellaneous Cash Receipt |
956 *---------------------------------------------------------*/
957 amt_due_remaining := new_crh.amount +
958 NVL(new_crh.factor_discount_amount, 0);
959
960 acctd_amt_due_remaining := new_crh.acctd_amount +
961 NVL(new_crh.acctd_factor_discount_amount, 0);
962 /*----------------------------*
963 | Retrieve each distribution |
964 *----------------------------*/
965 FOR old_misc IN misc_info(new_adj.cash_receipt_id) LOOP
966 /*-------------------------------*
967 | Create reversing Distribution |
968 *-------------------------------*/
969 --BUG#5201086
970 IF old_misc.cash_receipt_history_id IS NULL THEN
971 --Use the old Cash_receipt_history_id HYUHYU
972 old_misc.cash_receipt_history_id := old_reverse_crh_id;
973 END IF;
974
975 temp_num := arp_misc_cd.ins_misc_cash_distributions
976 (new_adj.last_updated_by,
977 new_adj.last_update_date,
978 new_adj.last_update_login,
979 new_adj.created_by,
980 new_adj.creation_date,
981 new_adj.cash_receipt_id,
982 old_misc.code_combination_id,
983 old_misc.set_of_books_id,
984 GREATEST(new_adj.gl_date, old_misc.gl_date),
985 old_misc.percent,
986 -1 * old_misc.amount,
987 old_misc.comments,
988 NULL,
989 old_misc.apply_date,
990 -3,
991 NULL,
992 NULL,
993 NULL,
994 NULL,
995 -1 * old_misc.acctd_amount,
996 old_misc.ussgl_transaction_code,
997 old_misc.ussgl_transaction_code_context,
998 'RATE ADJUSTMENT TRIGGER',
999 GREATEST(new_adj.gl_date, old_misc.gl_date),
1000 --BUG#5201086
1001 old_misc.cash_receipt_history_id);
1002
1003 /* Bugfix 2753644 */
1004 BEGIN
1005 IF l_ae_doc_rec.gl_tax_acct IS NULL AND
1006 cr.tax_rate IS NOT NULL
1007 THEN
1008 SELECT code_combination_id
1009 INTO l_ae_doc_rec.gl_tax_acct
1010 FROM ar_distributions
1011 WHERE source_id = old_misc.misc_cash_distribution_id
1012 AND source_table ='MCD'
1013 AND source_type = 'TAX';
1014 END IF;
1015 EXCEPTION
1016 WHEN no_data_found THEN
1017 null;
1018 WHEN others THEN
1019 raise;
1020 END;
1021
1022 --
1023 --Release 11.5 VAT changes, reverse the application accounting for
1024 --misc cash accounting records in ar_distributions.
1025 --
1026 l_ae_doc_rec.document_type := 'RECEIPT';
1027 l_ae_doc_rec.document_id := old_misc.cash_receipt_id;
1028 l_ae_doc_rec.accounting_entity_level := 'ONE';
1029 l_ae_doc_rec.source_table := 'MCD';
1030 l_ae_doc_rec.source_id := temp_num; --new record
1031 l_ae_doc_rec.source_id_old := old_misc.misc_cash_distribution_id; --old record for reversal
1032 l_ae_doc_rec.other_flag := 'REVERSE';
1033 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1034
1035 /*----------------------------------*
1036 | Update the Reversed Distribution |
1037 *----------------------------------*/
1038 arp_misc_cd.upd_reversal_gl_date
1039 (old_misc.misc_cash_distribution_id,
1040 GREATEST(new_adj.gl_Date,old_misc.gl_date),
1041 new_adj.last_updated_by,
1042 new_adj.last_update_date,
1043 new_adj.last_update_login,
1044 --BUG#5201086
1045 old_misc.cash_receipt_history_id);
1046
1047 /*-----------------------------*
1048 | Calculate New ACCTD_AMOUNT |
1049 *-----------------------------*/
1050 amt_due_remaining := amt_due_remaining - old_misc.amount;
1051
1052 new_acctd_amount := arp_standard.functional_amount(amt_due_remaining,
1053 cr.functional_currency,
1054 new_adj.new_exchange_rate,
1055 NULL,
1056 NULL);
1057
1058 dist_acctd_amount := acctd_amt_due_remaining -
1059 new_acctd_amount;
1060 acctd_amt_due_remaining := new_acctd_amount;
1061
1062 /*-----------------------------*
1063 | Insert the new Distribution |
1064 *-----------------------------*/
1065 temp_num := arp_misc_cd.ins_misc_cash_distributions
1066 (new_adj.last_updated_by,
1067 new_adj.last_update_date,
1068 new_adj.last_update_login,
1069 new_adj.created_by,
1070 new_adj.creation_date,
1071 new_adj.cash_receipt_id,
1072 old_misc.code_combination_id,
1073 old_misc.set_of_books_id,
1074 GREATEST(new_adj.gl_date, old_misc.gl_date),
1075 old_misc.percent,
1076 old_misc.amount,
1077 old_misc.comments,
1078 NULL,
1079 old_misc.apply_date,
1080 -3,
1081 NULL,
1082 NULL,
1083 NULL,
1084 NULL,
1085 dist_acctd_amount,
1086 old_misc.ussgl_transaction_code,
1087 old_misc.ussgl_transaction_code_context,
1088 'RATE ADJUSTMENT TRIGGER',
1089 NULL,
1090 --BUG#5201086
1091 new_crh_id);
1092
1093 END LOOP;
1094
1095 --
1096 --Release 11.5 VAT changes, create accounting for the new MCD records
1097 --in the ar_distributions table.
1098 --
1099 l_ae_doc_rec.document_type := 'RECEIPT';
1100 l_ae_doc_rec.document_id := new_adj.cash_receipt_id;
1101 l_ae_doc_rec.accounting_entity_level := 'ONE';
1102 l_ae_doc_rec.source_table := 'MCD';
1103 l_ae_doc_rec.source_id := '';
1104 l_ae_doc_rec.source_id_old := '';
1105 l_ae_doc_rec.other_flag := '';
1106 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1107
1108 /*CCR- Check to see if this MISC receipt is a negative credit card receipt
1109 -- Paula for CC Refund Rate adjustment. */
1110
1111 OPEN ar_rm_c(cr.receipt_method_id);
1112 FETCH ar_rm_c INTO l_rm_code;
1113 IF l_rm_code = 'CREDIT_CARD' THEN
1114 l_credit_card := TRUE;
1115 END IF;
1116 CLOSE ar_rm_c;
1117
1118 IF (cr.amount < 0 and l_credit_card ) THEN
1119
1120 OPEN ar_rc_rec(new_adj.cash_receipt_id);
1121 FETCH ar_rc_rec into l_rc_app;
1122 /* call the receipt api to unapply the cCR on the cash receipt - pkt */
1123 BEGIN
1124 IF PG_DEBUG in ('Y', 'C') THEN
1125 arp_standard.debug('main: ' || 'new_adj gl_date ' || new_adj.gl_date);
1126 arp_standard.debug('main: ' || 'old_misc gl_date ' ||old_misc.gl_date);
1127 arp_standard.debug('bal due remain ' ||l_bal_due_remaining);
1128 END IF;
1129
1130 arp_process_application.reverse(
1131 l_rc_app.receivable_application_id,
1132 greatest(new_adj.gl_date, l_rc_app.gl_date),
1133 trunc(sysdate),
1134 'RATE ADJUSTMENT TRIGGER',
1135 null,
1136 l_bal_due_remaining,
1137 'RATE_ADJUST_MISC');
1138
1139 EXCEPTION
1140 WHEN others THEN
1141 IF PG_DEBUG in ('Y', 'C') THEN
1142 arp_standard.debug('main: ' || 'EXCEPTION in unapplying the
1143 CCR for the MISC recipt in ARP_RATE_ADJ.MAIN');
1144 arp_standard.debug('main: ' || SQLERRM(SQLCODE));
1145 END IF;
1146 raise;
1147 close ar_rc_rec;
1148 END;
1149
1150
1151 /* now that the old CCR has been unapplied; apply the new CCR with the new rate- pkt */
1152 BEGIN
1153 arp_process_application.activity_application (
1154 p_receipt_ps_id => l_rc_app.payment_schedule_id,
1155 p_application_ps_id =>l_rc_app.applied_payment_schedule_id ,
1156 p_link_to_customer_trx_id => NULL,
1157 p_amount_applied => l_rc_app.amount_applied,
1158 p_apply_date => l_rc_app.apply_date,
1159 p_gl_date => greatest(new_adj.gl_date, l_rc_app.gl_date),
1160 p_receivables_trx_id => l_rc_app.receivables_trx_id,
1161 p_ussgl_transaction_code => l_rc_app.ussgl_transaction_code,
1162 p_attribute_category => l_rc_app.attribute_category,
1163 p_attribute1 => l_rc_app.attribute1,
1164 p_attribute2 => l_rc_app.attribute2,
1165 p_attribute3 => l_rc_app.attribute3,
1166 p_attribute4 => l_rc_app.attribute4,
1167 p_attribute5 => l_rc_app.attribute5,
1168 p_attribute6 => l_rc_app.attribute6,
1169 p_attribute7 => l_rc_app.attribute7,
1170 p_attribute8 => l_rc_app.attribute8,
1171 p_attribute9 => l_rc_app.attribute9,
1172 p_attribute10 => l_rc_app.attribute10,
1173 p_attribute11 => l_rc_app.attribute11,
1174 p_attribute12 =>l_rc_app.attribute12,
1175 p_attribute13 => l_rc_app.attribute13,
1176 p_attribute14 => l_rc_app.attribute14,
1177 p_attribute15 => l_rc_app.attribute15,
1178 p_global_attribute_category => l_rc_app.global_attribute_category,
1179 p_global_attribute1 => l_rc_app.global_attribute1,
1180 p_global_attribute2 => l_rc_app.global_attribute2,
1181 p_global_attribute3 => l_rc_app.global_attribute3,
1182 p_global_attribute4 => l_rc_app.global_attribute4,
1183 p_global_attribute5 => l_rc_app.global_attribute5,
1184 p_global_attribute6 => l_rc_app.global_attribute6,
1185 p_global_attribute7 => l_rc_app.global_attribute7,
1186 p_global_attribute8 => l_rc_app.global_attribute8,
1187 p_global_attribute9 => l_rc_app.global_attribute9,
1188 p_global_attribute10 => l_rc_app.global_attribute10,
1189 p_global_attribute11 => l_rc_app.global_attribute11,
1190 p_global_attribute12 => l_rc_app.global_attribute12,
1191 p_global_attribute13 => l_rc_app.global_attribute13,
1192 p_global_attribute14 => l_rc_app.global_attribute14,
1193 p_global_attribute15 => l_rc_app.global_attribute15,
1194 p_global_attribute16 => l_rc_app.global_attribute16,
1195 p_global_attribute17 => l_rc_app.global_attribute17,
1196 p_global_attribute18 => l_rc_app.global_attribute18,
1197 p_global_attribute19 => l_rc_app.global_attribute19,
1198 p_global_attribute20 => l_rc_app.global_attribute20,
1199 p_comments => l_rc_app.comments,
1200 p_module_name => 'RATE_ADJUSTMENT_MAIN',
1201 p_module_version => '1.0',
1202 p_secondary_application_ref_id => l_rc_app.secondary_application_ref_id,
1203 p_application_ref_type => l_rc_app.application_ref_type,
1204 p_application_ref_id => l_rc_app.application_ref_id,
1205 p_application_ref_num => l_rc_app.application_ref_num,
1206 -- *** OUT NOCOPY
1207 p_out_rec_application_id => ln_rec_application_id);
1208
1209
1210 EXCEPTION
1211 WHEN others THEN
1212 IF PG_DEBUG in ('Y', 'C') THEN
1213 arp_standard.debug('main: ' || 'EXCEPTION in applying the
1214 CCR for the MISC recipt in ARP_RATE_ADJ.MAIN');
1215 arp_standard.debug('main: ' || SQLERRM(SQLCODE));
1216 END IF;
1217 raise;
1218 close ar_rc_rec;
1219 END;
1220
1221 CLOSE ar_rc_rec;
1222
1223 END IF; -- negative credit card miscellaneous receipt pkt
1224 -- End of Paula's changes for CC Refund rate change.
1225
1226 ELSE
1227 /*-----------------------------------------------------------------*
1228 | Update Payment Schedules Record, resetting amount_due_remaining |
1229 | and change exchange rate information |
1230 *-----------------------------------------------------------------*/
1231 cr_acctd_amount := arp_standard.functional_amount(cr.amount,
1232 cr.functional_currency,
1233 new_adj.new_exchange_rate,
1234 NULL,
1235 NULL);
1236
1237 arp_pay_sched.upd_payment_schedules
1238 (-cr.amount,
1239 -cr_acctd_amount,
1240 new_adj.new_exchange_rate,
1241 new_adj.new_exchange_date,
1242 new_adj.new_exchange_rate_type,
1243 cr.payment_schedule_id,
1244 new_adj.last_updated_by,
1245 new_adj.last_update_date,
1246 new_adj.last_update_login);
1247
1248 /*------------------------------------------------------------*
1249 | |
1250 | Now get the amount due remaining from the payment schedule |
1251 | |
1252 *------------------------------------------------------------*/
1253 OPEN ps_remaining_info(cr.payment_schedule_id);
1254 FETCH ps_remaining_info INTO ps_remaining;
1255 CLOSE ps_remaining_info;
1256
1257 /*------------------------------------------------------------*
1258 |Get the maximum write-off amount set at the system level |
1259 *------------------------------------------------------------*/
1260 SELECT NVL(MAX_WRTOFF_AMOUNT,0)
1261 INTO l_max_wrt_off_amount
1262 FROM AR_SYSTEM_PARAMETERS;
1263
1264 /*----------------------------------*
1265 | |
1266 | For each un-reversed application |
1267 | |
1268 *----------------------------------*/
1269 FOR old_rec_app IN rec_app_info(cr.cash_receipt_id) LOOP
1270 /*------------------------------*
1271 | Create reversing Application |
1272 *------------------------------*/
1273 -- Release 11
1274 -- Modified call to create new receivable application record to use the
1275 -- latest and greatest receivable applications table handler.
1276 --
1277 -- Firstly need to setup the record structure that is passed to the insert procedure.
1278 --
1279 ins_ra_rec.acctd_amount_applied_from := -1 * old_rec_app.acctd_amount_applied_from;
1280 ins_ra_rec.amount_applied := -1 * old_rec_app.amount_applied;
1281 ins_ra_rec.amount_applied_from := -1 * old_rec_app.amount_applied_from;
1282 ins_ra_rec.trans_to_receipt_rate := old_rec_app.trans_to_receipt_rate;
1283 ins_ra_rec.application_rule := 'RATE ADJUSTMENT TRIGGER';
1284 ins_ra_rec.application_type := old_rec_app.application_type;
1285 ins_ra_rec.apply_date := old_rec_app.apply_date;
1286 ins_ra_rec.code_combination_id := old_rec_app.code_combination_id;
1287 ins_ra_rec.created_by := new_adj.created_by;
1288 ins_ra_rec.creation_date := new_adj.creation_date;
1289 ins_ra_rec.display := 'N';
1290 ins_ra_rec.gl_date := GREATEST(new_adj.gl_date, old_rec_app.gl_date);
1291 ins_ra_rec.last_updated_by := new_adj.last_updated_by;
1292 ins_ra_rec.last_update_date := new_adj.last_update_date;
1293 ins_ra_rec.payment_schedule_id := old_rec_app.payment_schedule_id;
1294 ins_ra_rec.set_of_books_id := old_rec_app.set_of_books_id;
1295 ins_ra_rec.status := old_rec_app.status;
1296 ins_ra_rec.acctd_amount_applied_to := -1 * old_rec_app.acctd_amount_applied_to;
1297 ins_ra_rec.acctd_earned_discount_taken := -1 * old_rec_app.acctd_earned_discount_taken;
1298 ins_ra_rec.acctd_unearned_discount_taken := -1 * old_rec_app.acctd_unearned_discount_taken;
1299 ins_ra_rec.applied_customer_trx_id := old_rec_app.applied_customer_trx_id;
1300 ins_ra_rec.applied_customer_trx_line_id := old_rec_app.applied_customer_trx_line_id;
1301 ins_ra_rec.applied_payment_schedule_id := old_rec_app.applied_payment_schedule_id;
1302 ins_ra_rec.cash_receipt_id := old_rec_app.cash_receipt_id;
1303 ins_ra_rec.comments := old_rec_app.comments;
1304 ins_ra_rec.confirmed_flag := old_rec_app.confirmed_flag;
1305 ins_ra_rec.customer_trx_id := old_rec_app.customer_trx_id;
1306 ins_ra_rec.days_late := old_rec_app.days_late;
1307 ins_ra_rec.earned_discount_taken := -1 * old_rec_app.earned_discount_taken;
1308 ins_ra_rec.freight_applied := -1 * old_rec_app.freight_applied;
1309 ins_ra_rec.gl_posted_date := NULL;
1310 ins_ra_rec.last_update_login := new_adj.last_update_login;
1311 ins_ra_rec.line_applied := -1 * old_rec_app.line_applied;
1312 ins_ra_rec.on_account_customer := old_rec_app.on_account_customer;
1313 ins_ra_rec.postable := old_rec_app.postable;
1314 ins_ra_rec.posting_control_id := -3;
1315 ins_ra_rec.program_application_id := NULL;
1316 ins_ra_rec.program_id := NULL;
1317 ins_ra_rec.program_update_date := NULL;
1318 ins_ra_rec.receivables_charges_applied := -1 * old_rec_app.receivables_charges_applied;
1319 ins_ra_rec.receivables_trx_id := old_rec_app.receivables_trx_id;
1320 ins_ra_rec.request_id := NULL;
1321 ins_ra_rec.tax_applied := -1 * old_rec_app.tax_applied;
1322 ins_ra_rec.unearned_discount_taken := -1 * old_rec_app.unearned_discount_taken;
1323 ins_ra_rec.unearned_discount_ccid := old_rec_app.unearned_discount_ccid;
1324 ins_ra_rec.earned_discount_ccid := old_rec_app.earned_discount_ccid;
1325 ins_ra_rec.ussgl_transaction_code := old_rec_app.ussgl_transaction_code;
1326 ins_ra_rec.ussgl_transaction_code_context := old_rec_app.ussgl_transaction_code_context;
1327 ins_ra_rec.reversal_gl_date := GREATEST(old_rec_app.gl_date, new_adj.gl_date);
1328 ins_ra_rec.cash_receipt_history_id := new_crh_id; /* bug 3730165 */
1329
1330 -- Additional Columns for Application Rule Sets
1331
1332 ins_ra_rec.LINE_EDISCOUNTED := old_rec_app.LINE_EDISCOUNTED;
1333 ins_ra_rec.LINE_UEDISCOUNTED := old_rec_app.LINE_UEDISCOUNTED;
1334 ins_ra_rec.TAX_EDISCOUNTED := old_rec_app.TAX_EDISCOUNTED;
1335 ins_ra_rec.TAX_UEDISCOUNTED := old_rec_app.TAX_UEDISCOUNTED;
1336 ins_ra_rec.FREIGHT_EDISCOUNTED := old_rec_app.FREIGHT_EDISCOUNTED;
1337 ins_ra_rec.FREIGHT_UEDISCOUNTED := old_rec_app.FREIGHT_UEDISCOUNTED;
1338 ins_ra_rec.CHARGES_EDISCOUNTED := old_rec_app.CHARGES_EDISCOUNTED;
1339 ins_ra_rec.CHARGES_UEDISCOUNTED := old_rec_app.CHARGES_UEDISCOUNTED;
1340
1341 --Bug 2071717
1342 ins_ra_rec.APPLICATION_REF_TYPE := old_rec_app.APPLICATION_REF_TYPE;
1343 ins_ra_rec.application_ref_id := old_rec_app.application_ref_id;
1344 ins_ra_rec.application_ref_num := old_rec_app.application_ref_num;
1345 /* Bug 2254777 - new columns for Trade Management */
1346 ins_ra_rec.application_ref_reason := old_rec_app.application_ref_reason;
1347 ins_ra_rec.customer_reference := old_rec_app.customer_reference;
1348
1349 /*Bug3505753 */
1350 ins_ra_rec.link_to_customer_trx_id := old_rec_app.link_to_customer_trx_id;
1351
1352 /* Bug 2821139 - more new columns for Trade Mgt and netting */
1353 ins_ra_rec.customer_reason := old_rec_app.customer_reason;
1354 ins_ra_rec.applied_rec_app_id := old_rec_app.applied_rec_app_id;
1355 --BUG#5201086
1356 ins_ra_rec.cash_receipt_history_id := old_reverse_crh_id; --BUG#5201086
1357
1358 arp_app_pkg.insert_p( ins_ra_rec, temp_num );
1359
1360 --Bug#2750340
1361 l_xla_ev_rec.xla_from_doc_id := temp_num;
1362 l_xla_ev_rec.xla_to_doc_id := temp_num;
1363 l_xla_ev_rec.xla_doc_table := 'APP';
1364 l_xla_ev_rec.xla_mode := 'O';
1365 l_xla_ev_rec.xla_call := 'B';
1366 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1367
1368 --BUG#5022786
1369 IF old_rec_app.applied_customer_trx_id IS NOT NULL AND old_rec_app.status = 'APP' THEN
1370 OPEN c_trx(old_rec_app.applied_customer_trx_id);
1371 FETCH c_trx INTO l_upgrade_methode;
1372 IF c_trx%NOTFOUND THEN
1373 l_upgrade_methode := NULL;
1374 END IF;
1375 CLOSE c_trx;
1376 END IF;
1377
1378 --
1379 -- iClaim/Deductions - update invoice related claim if exists
1380 --
1381 IF (old_rec_app.APPLICATION_REF_TYPE = 'CLAIM' AND
1382 old_rec_app.STATUS = 'APP' AND
1383 NVL(old_rec_app.trans_to_receipt_rate,1) <> 1 AND
1384 old_rec_app.SECONDARY_APPLICATION_REF_ID IS NOT NULL)
1385 THEN
1386 -- Bug 2076743 - cater for cancelled claims
1387 -- Bug 2353144 - use check_cancel_deduction instead of status
1388 -- OPEN to determine if claim is cancellable
1389 IF OZF_Claim_GRP.Check_Cancell_Deduction(
1390 p_claim_id => old_rec_app.secondary_application_ref_id)
1391 THEN
1392 l_claim_id := NULL;
1393 l_claim_amount := ps_remaining.amount_due_remaining +
1394 nvl(old_rec_app.amount_applied_from, old_rec_app.amount_applied);
1395 arp_process_application.update_claim(
1396 p_claim_id => l_claim_id
1397 , p_invoice_ps_id => old_rec_app.applied_payment_schedule_id
1398 , p_customer_trx_id => old_rec_app.customer_trx_id
1399 , p_amount => l_claim_amount
1400 , p_amount_applied => old_rec_app.amount_applied
1401 , p_apply_date => old_rec_app.apply_date
1402 , p_cash_receipt_id => cr.cash_receipt_id
1403 , p_receipt_number => NULL
1404 , p_action_type => 'A'
1405 , x_claim_reason_code_id => l_claim_reason_code_id
1406 , x_claim_reason_name => l_claim_reason_name
1407 , x_claim_number => l_claim_number
1408 , x_return_status => l_return_status
1409 , x_msg_count => l_msg_count
1410 , x_msg_data => l_msg_data);
1411 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1412 THEN
1413 IF l_msg_count > 1 THEN
1414 fnd_msg_pub.reset;
1415 -- get first message only from the stack for forms users
1416 l_mesg := fnd_msg_pub.get(p_encoded=>FND_API.G_FALSE);
1417 ELSE
1418 l_mesg := l_msg_data;
1419 END IF;
1420
1421 --Now set the message token
1422 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
1423 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_mesg);
1424
1425 RAISE claim_cancel_api_error;
1426
1427 END IF;
1428 END IF;
1429 END IF;
1430
1431 --
1432 --Release 11.5 VAT changes, reverse the application accounting for
1433 --confirmed records in ar_distributions.
1434 --
1435 l_ae_doc_rec.document_type := 'RECEIPT';
1436 l_ae_doc_rec.document_id := cr.cash_receipt_id;
1437 l_ae_doc_rec.accounting_entity_level := 'ONE';
1438 l_ae_doc_rec.source_table := 'RA';
1439 l_ae_doc_rec.source_id := temp_num; --new record
1440 l_ae_doc_rec.source_id_old := old_rec_app.receivable_application_id; --old record for reversal
1441 l_ae_doc_rec.other_flag := 'REVERSE';
1442
1443 --Commented out NOCOPY for fixing the accounting
1444 --Bug 1329091 - PS is updated before Accounting Engine Call
1445 --l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1446
1447 --{HYU line level Reversal
1448 IF old_rec_app.status = 'APP' AND l_upgrade_methode IN ('R12','R12_11IMFAR') THEN
1449
1450
1451
1452 --Rate Adj
1453 OPEN c_trx_rem_gt(p_customer_trx_id => old_rec_app.applied_customer_trx_id);
1454 FETCH c_trx_rem_gt BULK COLLECT INTO
1455 l_ACCTD_AMOUNT_DUE_REMAINING ,
1456 l_AMOUNT_DUE_REMAINING ,
1457 l_CHRG_ACCTD_AMOUNT_REMAINING,
1458 l_CHRG_AMOUNT_REMAINING ,
1459 l_FRT_ADJ_ACCTD_REMAINING ,
1460 l_FRT_ADJ_REMAINING ,
1461 l_FRT_ED_ACCTD_AMOUNT ,
1462 l_FRT_ED_AMOUNT ,
1463 l_FRT_UNED_ACCTD_AMOUNT ,
1464 l_FRT_UNED_AMOUNT ,
1465 l_customer_trx_line_id;
1466 IF l_customer_trx_line_id.COUNT > 0 THEN
1467 l_reset_rem := 'Y';
1468 ELSE
1469 l_reset_rem := 'N';
1470 END IF;
1471 CLOSE c_trx_rem_gt;
1472 --}
1473
1474 arp_det_dist_pkg.get_gt_sequence
1475 (x_gt_id => l_gt_id,
1476 x_return_status => x_return_status,
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data);
1479
1480 INSERT INTO ar_line_app_detail_gt
1481 ( ACCTD_AMOUNT
1482 ,REF_ACCOUNT_CLASS
1483 ,AMOUNT
1484 ,APP_LEVEL
1485 ,BASE_CURRENCY
1486 ,ACTIVITY_BUCKET
1487 ,CCID
1488 ,GT_ID
1489 ,LEDGER_ID
1490 ,ORG_ID
1491 ,REF_CUSTOMER_TRX_ID
1492 ,REF_CUSTOMER_TRX_LINE_ID
1493 ,REF_CUST_TRX_LINE_GL_DIST_ID
1494 ,REF_LINE_ID
1495 ,SOURCE_ID
1496 ,SOURCE_TABLE
1497 ,SOURCE_TYPE
1498 ,TAXABLE_ACCTD_AMOUNT
1499 ,TAXABLE_AMOUNT
1500 ,TAX_INC_FLAG
1501 ,TAX_LINK_ID
1502 ,TO_CURRENCY
1503 ,REF_MF_DIST_FLAG)
1504 SELECT
1505 DECODE(ard.activity_bucket,
1506 'APP_LINE' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1507 'APP_TAX' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1508 'APP_FRT' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1509 'APP_CHRG' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1510 -(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0))) -- ACCTD_AMOUNT
1511 ,ard.ref_account_class -- REF_ACCOUNT_CLASS
1512 ,DECODE(ard.activity_bucket,
1513 'APP_LINE' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1514 'APP_TAX' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1515 'APP_FRT' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1516 'APP_CHRG' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1517 -(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0))) -- AMOUNT
1518 ,'LINE' -- APP_LEVEL
1519 ,arp_global.functional_currency -- BASE_CURRENCY
1520 ,ard.ACTIVITY_BUCKET -- ACTIVITY_BUCKET
1521 ,ard.code_combination_id -- CCID
1522 ,l_gt_id -- GT_ID
1523 ,ora.set_of_books_id -- LEDGER_ID
1524 ,ard.org_id -- ORG_ID
1525 ,ora.applied_customer_trx_id -- REF_CUSTOMER_TRX_ID
1526 ,ard.ref_customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
1527 ,ard.ref_cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
1528 ,ard.ref_line_id -- REF_LINE_ID
1529 ,ard.source_id -- SOURCE_ID
1530 ,ard.source_table -- SOURCE_TABLE
1531 ,ora.application_type -- SOURCE_TYPE
1532 ,'' -- TAXABLE_ACCTD_AMOUNT
1533 ,'' -- TAXABLE_AMOUNT
1534 ,'' -- TAX_INC_FLAG
1535 ,'' -- TAX_LINK_ID
1536 ,trx.invoice_currency_code -- TO_CURRENCY
1537 ,'' -- REF_MF_DIST_FLAG
1538 FROM ar_distributions ard,
1539 ar_receivable_applications ora,
1540 ra_customer_trx trx
1541 WHERE ora.receivable_application_id = old_rec_app.receivable_application_id
1542 AND ard.source_table = 'RA'
1543 AND ard.source_id = ora.receivable_application_id
1544 AND ard.activity_bucket IS NOT NULL
1545 AND ora.applied_customer_trx_id = trx.customer_trx_id;
1546
1547 arp_acct_main.Create_Acct_Entry(
1548 p_ae_doc_rec => l_ae_doc_rec,
1549 p_client_server => NULL,
1550 p_from_llca_call=> 'Y',
1551 p_gt_id => l_gt_id);
1552
1553 ELSE
1554 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1555 END IF;
1556
1557
1558 /*---------------------------------------------*
1559 | Update reversed record, setting DISPLAY and |
1560 | REVERSAL_GL_DATE |
1561 *---------------------------------------------*/
1562 arp_app_pkg.fetch_p( old_rec_app.receivable_application_id, upd_ra_rec );
1563
1564 upd_ra_rec.display := 'N';
1565 upd_ra_rec.reversal_gl_date := GREATEST(old_rec_app.gl_date, new_adj.gl_date);
1566 upd_ra_rec.last_updated_by := new_adj.last_updated_by;
1567 upd_ra_rec.last_update_date := new_adj.last_update_date;
1568 upd_ra_rec.last_update_login := new_adj.last_update_login;
1569
1570 arp_app_pkg.update_p(upd_ra_rec);
1571
1572 -- keep a running total of the amount of UNID
1573 IF old_rec_app.status = 'UNID' THEN
1574 total_unid := total_unid + old_rec_app.amount_applied;
1575 ELSE
1576 /*---------------------------------------------------------*
1577 | |
1578 | Calculate new acctd_amount and update Payment Schedules |
1579 | (for Applied applications) |
1580 | |
1581 *---------------------------------------------------------*/
1582 IF (old_rec_app.status in ('APP','ACTIVITY')) THEN
1583 /*-------------------------------------------*
1584 | |
1585 | Calculate ACCTD_AMOUNT of new application |
1586 | |
1587 *-------------------------------------------*/
1588 ps_remaining.amount_due_remaining :=
1589 ps_remaining.amount_due_remaining +
1590 nvl(old_rec_app.amount_applied_from, old_rec_app.amount_applied);
1591
1592 new_ps_acctd_amount := arp_standard.functional_amount
1593 (ps_remaining.amount_due_remaining,
1594 cr.functional_currency,
1595 new_adj.new_exchange_rate,
1596 NULL,
1597 NULL);
1598
1599 app_acctd_amount := new_ps_acctd_amount -
1600 ps_remaining.acctd_amount_due_remaining;
1601
1602 ps_remaining.acctd_amount_due_remaining :=
1603 new_ps_acctd_amount;
1604
1605
1606 IF ((old_rec_app.status = 'ACTIVITY')
1607 AND (old_rec_app.applied_payment_schedule_id = -3)
1608 AND (NVL(app_acctd_amount,0) > l_max_wrt_off_amount))
1609 THEN
1610 /*-------------------------------------------------------------*
1611 |Bug 1832122 - If the new write-off accounted amount exceeds |
1612 |maximum write-off amount at system level,then we leave the |
1613 |write-off record as unapplied. In that case, we have to open the|
1614 |receipt PS and ar_cash_receipts record |
1615 *-------------------------------------------------------------*/
1616 --Since in this case we are not re-creating the write-off record
1617 --we should not update the amounts in PS only status needs to be
1618 --opened.
1619
1620 UPDATE ar_payment_schedules
1621 SET status = 'OP',
1622 gl_date_closed = ARP_GLOBAL.G_MAX_DATE,
1623 actual_date_closed = ARP_GLOBAL.G_MAX_DATE
1624 WHERE payment_schedule_id = cr.payment_schedule_id;
1625
1626 l_cr_rec.cash_receipt_id := cr.cash_receipt_id;
1627 l_cr_rec.status := 'UNAPP';
1628
1629 -- Update cash receipt status
1630 arp_cash_receipts_pkg.update_p(l_cr_rec, cr.cash_receipt_id);
1631
1632 ELSE
1633 /*-----------------------------------*
1634 | Update Payment Schedules with new |
1635 | amount due remaining |
1636 *-----------------------------------*/
1637
1638 arp_pay_sched.upd_amt_due_remaining(cr.payment_schedule_id,
1639 ps_remaining.amount_due_remaining,
1640 ps_remaining.acctd_amount_due_remaining,
1641 new_adj.last_updated_by,
1642 new_adj.last_update_date,
1643 new_adj.last_update_login);
1644
1645 END IF;
1646 ELSE
1647
1648 app_acctd_amount := arp_standard.functional_amount
1649 (old_rec_app.amount_applied,
1650 cr.functional_currency,
1651 new_adj.new_exchange_rate,
1652 NULL,
1653 NULL);
1654 END IF;
1655
1656 /*------------------------*
1657 | |
1658 | Create new application |
1659 | |
1660 *------------------------*/
1661 -- Release 11
1662 -- Modified call to create new receivable application record to use the
1663 -- latest and greatest receivable applications table handler.
1664 --
1665 -- Firstly need to setup the record structure that is passed to the insert procedure.
1666 --
1667 IF old_rec_app.status IN ( 'APP', 'ACC' ,'OTHER ACC','ACTIVITY') THEN
1668
1669 /*-----------------------------------------------------------------*
1670 | Bug 1815650 - When rate adjusting write-off application,the new |
1671 | accounted amount should not exceed maximum write off limit set |
1672 | at the system level.If it exceeds, do not create write-off |
1673 | ACTIVITY record or associated UNAPP record |
1674 *-----------------------------------------------------------------*/
1675 IF ((old_rec_app.status = 'ACTIVITY')
1676 AND (old_rec_app.applied_payment_schedule_id = -3)
1677 AND (NVL(app_acctd_amount,0) > l_max_wrt_off_amount))
1678 THEN
1679 --Do not re-create the write-off record is it exceeds
1680 --maximum write-off amount set.
1681 NULL;
1682 ELSE
1683
1684 /*Bug3505753 */
1685 ins_ra_rec.link_to_customer_trx_id := old_rec_app.link_to_customer_trx_id;
1686 ins_ra_rec.acctd_amount_applied_from := app_acctd_amount;
1687 ins_ra_rec.amount_applied := old_rec_app.amount_applied;
1688 ins_ra_rec.amount_applied_from := old_rec_app.amount_applied_from;
1689 ins_ra_rec.trans_to_receipt_rate := old_rec_app.trans_to_receipt_rate;
1690 ins_ra_rec.application_rule := 'RATE ADJUSTMENT TRIGGER';
1691 ins_ra_rec.application_type := old_rec_app.application_type;
1692 ins_ra_rec.apply_date := old_rec_app.apply_date;
1693 ins_ra_rec.code_combination_id := old_rec_app.code_combination_id;
1694 ins_ra_rec.created_by := new_adj.created_by;
1695 ins_ra_rec.creation_date := new_adj.creation_date;
1696 ins_ra_rec.display := old_rec_app.display;
1697 ins_ra_rec.gl_date := GREATEST(new_adj.gl_date, old_rec_app.gl_date);
1698 ins_ra_rec.last_updated_by := new_adj.last_updated_by;
1699 ins_ra_rec.last_update_date := new_adj.last_update_date;
1700 ins_ra_rec.payment_schedule_id := old_rec_app.payment_schedule_id;
1701 ins_ra_rec.set_of_books_id := old_rec_app.set_of_books_id;
1702 ins_ra_rec.status := old_rec_app.status;
1703
1704 /* Bug 2821139 - if a payment netting then the acctd_amount_applied_to is
1705 recalculated as this receipt becomes the 'main' receipt. */
1706 IF old_rec_app.receivables_trx_id = -16 THEN
1707 SELECT exchange_rate
1708 INTO l_exchange_rate
1709 FROM ar_payment_schedules
1710 WHERE payment_schedule_id = old_rec_app.applied_payment_schedule_id;
1711 ins_ra_rec.acctd_amount_applied_to :=
1712 ARPCURR.functional_amount(
1713 amount => old_rec_app.amount_applied
1714 , currency_code => cr.functional_currency
1715 , exchange_rate => l_exchange_rate
1716 , precision => NULL
1717 , min_acc_unit => NULL );
1718 ELSE
1719 ins_ra_rec.acctd_amount_applied_to := old_rec_app.acctd_amount_applied_to;
1720 END IF;
1721
1722 ins_ra_rec.acctd_earned_discount_taken := old_rec_app.acctd_earned_discount_taken;
1723 ins_ra_rec.acctd_unearned_discount_taken := old_rec_app.acctd_unearned_discount_taken;
1724 ins_ra_rec.applied_customer_trx_id := old_rec_app.applied_customer_trx_id;
1725 ins_ra_rec.applied_customer_trx_line_id := old_rec_app.applied_customer_trx_line_id;
1726 ins_ra_rec.applied_payment_schedule_id := old_rec_app.applied_payment_schedule_id;
1727 ins_ra_rec.cash_receipt_id := old_rec_app.cash_receipt_id;
1728 ins_ra_rec.comments := old_rec_app.comments;
1729 ins_ra_rec.confirmed_flag := old_rec_app.confirmed_flag;
1730 ins_ra_rec.customer_trx_id := old_rec_app.customer_trx_id;
1731 ins_ra_rec.days_late := old_rec_app.days_late;
1732 ins_ra_rec.earned_discount_taken := old_rec_app.earned_discount_taken;
1733 ins_ra_rec.freight_applied := old_rec_app.freight_applied;
1734 ins_ra_rec.gl_posted_date := NULL;
1735 ins_ra_rec.last_update_login := new_adj.last_update_login;
1736 ins_ra_rec.line_applied := old_rec_app.line_applied;
1737 ins_ra_rec.on_account_customer := old_rec_app.on_account_customer;
1738 ins_ra_rec.postable := old_rec_app.postable;
1739 ins_ra_rec.posting_control_id := -3;
1740 ins_ra_rec.program_application_id := NULL;
1741 ins_ra_rec.program_id := NULL;
1742 ins_ra_rec.program_update_date := NULL;
1743 ins_ra_rec.receivables_charges_applied := old_rec_app.receivables_charges_applied;
1744 ins_ra_rec.receivables_trx_id := old_rec_app.receivables_trx_id;
1745 ins_ra_rec.request_id := NULL;
1746 ins_ra_rec.tax_applied := old_rec_app.tax_applied;
1747 ins_ra_rec.unearned_discount_taken := old_rec_app.unearned_discount_taken;
1748 ins_ra_rec.unearned_discount_ccid := old_rec_app.unearned_discount_ccid;
1749 ins_ra_rec.earned_discount_ccid := old_rec_app.earned_discount_ccid;
1750 ins_ra_rec.ussgl_transaction_code := old_rec_app.ussgl_transaction_code;
1751 ins_ra_rec.ussgl_transaction_code_context := old_rec_app.ussgl_transaction_code_context;
1752 ins_ra_rec.reversal_gl_date := NULL;
1753 ins_ra_rec.cash_receipt_history_id := new_crh_id;
1754
1755 -- Additional Columns for Application Rule Sets
1756 ins_ra_rec.LINE_EDISCOUNTED := old_rec_app.LINE_EDISCOUNTED;
1757 ins_ra_rec.LINE_UEDISCOUNTED := old_rec_app.LINE_UEDISCOUNTED;
1758 ins_ra_rec.TAX_EDISCOUNTED := old_rec_app.TAX_EDISCOUNTED;
1759 ins_ra_rec.TAX_UEDISCOUNTED := old_rec_app.TAX_UEDISCOUNTED;
1760 ins_ra_rec.FREIGHT_EDISCOUNTED := old_rec_app.FREIGHT_EDISCOUNTED;
1761 ins_ra_rec.FREIGHT_UEDISCOUNTED := old_rec_app.FREIGHT_UEDISCOUNTED;
1762 ins_ra_rec.CHARGES_EDISCOUNTED := old_rec_app.CHARGES_EDISCOUNTED;
1763 ins_ra_rec.CHARGES_UEDISCOUNTED := old_rec_app.CHARGES_UEDISCOUNTED;
1764
1765 --
1766 -- Additional application reference columns
1767 --
1768 ins_ra_rec.APPLICATION_REF_TYPE := old_rec_app.APPLICATION_REF_TYPE;
1769
1770 --S.Nambiar added payment_set_id for prepayment application.
1771 ins_ra_rec.payment_set_id := old_rec_app.payment_set_id;
1772 --
1773 -- If non invoice related claim call API to create
1774 -- a new claim
1775 --
1776 ins_ra_rec.application_ref_id := old_rec_app.application_ref_id;
1777 ins_ra_rec.application_ref_num := old_rec_app.application_ref_num;
1778 ins_ra_rec.secondary_application_ref_id := old_rec_app.secondary_application_ref_id;
1779 /* Bug 2821139 - more new columns for Trade Mgt and netting */
1780 ins_ra_rec.application_ref_reason := old_rec_app.application_ref_reason;
1781 ins_ra_rec.customer_reference := old_rec_app.customer_reference;
1782 ins_ra_rec.customer_reason := old_rec_app.customer_reason;
1783 ins_ra_rec.applied_rec_app_id := old_rec_app.applied_rec_app_id;
1784
1785 temp_num := NULL; --bug6271951
1786 arp_app_pkg.insert_p( ins_ra_rec, temp_num );
1787
1788 --Bug2750340
1789 l_xla_ev_rec.xla_from_doc_id := temp_num;
1790 l_xla_ev_rec.xla_to_doc_id := temp_num;
1791 l_xla_ev_rec.xla_doc_table := 'APP';
1792 l_xla_ev_rec.xla_mode := 'O';
1793 l_xla_ev_rec.xla_call := 'B';
1794 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1795
1796 /* Bug No. 3682777 JVARKEY
1797 Update the gl_date_closed of concerned invoice in the payment schedules if the status is closed and
1798 the current gl_date closed is less than gl_date of the reate adjustment */
1799
1800 SELECT gl_date_closed,status
1801 INTO l_inv_gl_date_closed,l_inv_ps_status
1802 FROM ar_payment_schedules
1803 WHERE payment_schedule_id=ins_ra_rec.applied_payment_schedule_id;
1804
1805 IF ((l_inv_gl_date_closed<ins_ra_rec.gl_date)
1806 AND l_inv_ps_status='CL')
1807 THEN
1808 UPDATE ar_payment_schedules
1809 SET gl_date_closed=ins_ra_rec.gl_date
1810 WHERE payment_schedule_id=ins_ra_rec.applied_payment_schedule_id;
1811 END IF;
1812
1813
1814 -- save the app_rec info for mrc use
1815 l_app_ra_rec := ins_ra_rec;
1816 l_app_ra_rec.receivable_application_id := temp_num;
1817 -- Store the rec app id for Netting
1818 l_new_rec_app_id := temp_num;
1819 --
1820 --Release 11.5 VAT changes, create the APP record for the new rate
1821 --adjustment
1822 --
1823 l_ae_doc_rec.document_type := 'RECEIPT';
1824 l_ae_doc_rec.document_id := cr.cash_receipt_id;
1825 l_ae_doc_rec.accounting_entity_level := 'ONE';
1826 l_ae_doc_rec.source_table := 'RA';
1827 l_ae_doc_rec.source_id := temp_num; --new APP record
1828 l_ae_doc_rec.source_id_old := '';
1829 l_ae_doc_rec.other_flag := '';
1830
1831 --Bug 1329091 - PS is updated before Accounting Engine Call
1832 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1833
1834
1835 l_app_id := temp_num;
1836 --
1837 -- Create the complementary UNAPP record
1838 --
1839 ins_ra_rec.acctd_amount_applied_from := -app_acctd_amount;
1840 ins_ra_rec.amount_applied := nvl(-old_rec_app.amount_applied_from, -old_rec_app.amount_applied);
1841 ins_ra_rec.amount_applied_from := -old_rec_app.amount_applied_from;
1842 ins_ra_rec.trans_to_receipt_rate := NULL;
1843 ins_ra_rec.application_rule := 'RATE ADJUSTMENT TRIGGER';
1844 ins_ra_rec.application_type := old_rec_app.application_type;
1845 ins_ra_rec.apply_date := old_rec_app.apply_date;
1846 ins_ra_rec.code_combination_id := cr.unapplied_ccid;
1847 ins_ra_rec.created_by := new_adj.created_by;
1848 ins_ra_rec.creation_date := new_adj.creation_date;
1849 ins_ra_rec.display := 'N';
1850 ins_ra_rec.gl_date := GREATEST(new_adj.gl_date, old_rec_app.gl_date);
1851 ins_ra_rec.last_updated_by := new_adj.last_updated_by;
1852 ins_ra_rec.last_update_date := new_adj.last_update_date;
1853 ins_ra_rec.payment_schedule_id := old_rec_app.payment_schedule_id;
1854 ins_ra_rec.set_of_books_id := old_rec_app.set_of_books_id;
1855 ins_ra_rec.status := 'UNAPP';
1856 ins_ra_rec.acctd_amount_applied_to := NULL;
1857 ins_ra_rec.acctd_earned_discount_taken := NULL;
1858 ins_ra_rec.acctd_unearned_discount_taken := NULL;
1859 ins_ra_rec.applied_customer_trx_id := NULL;
1860 ins_ra_rec.applied_customer_trx_line_id := NULL;
1861 ins_ra_rec.applied_payment_schedule_id := NULL;
1862 ins_ra_rec.cash_receipt_id := old_rec_app.cash_receipt_id;
1863 ins_ra_rec.comments := old_rec_app.comments;
1864 ins_ra_rec.confirmed_flag := old_rec_app.confirmed_flag;
1865 ins_ra_rec.customer_trx_id := NULL;
1866 ins_ra_rec.days_late := NULL;
1867 ins_ra_rec.earned_discount_taken := NULL;
1868 ins_ra_rec.freight_applied := NULL;
1869 ins_ra_rec.gl_posted_date := NULL;
1870 ins_ra_rec.last_update_login := new_adj.last_update_login;
1871 ins_ra_rec.line_applied := NULL;
1872 ins_ra_rec.on_account_customer := old_rec_app.on_account_customer;
1873 ins_ra_rec.postable := old_rec_app.postable;
1874 ins_ra_rec.posting_control_id := -3;
1875 ins_ra_rec.program_application_id := NULL;
1876 ins_ra_rec.receivables_charges_applied := NULL;
1877 ins_ra_rec.program_id := NULL;
1878 ins_ra_rec.program_update_date := NULL;
1879 ins_ra_rec.receivables_trx_id := old_rec_app.receivables_trx_id;
1880 ins_ra_rec.request_id := NULL;
1881 ins_ra_rec.tax_applied := NULL;
1882 ins_ra_rec.unearned_discount_taken := NULL;
1883 ins_ra_rec.unearned_discount_ccid := NULL;
1884 ins_ra_rec.earned_discount_ccid := NULL;
1885 ins_ra_rec.ussgl_transaction_code := old_rec_app.ussgl_transaction_code;
1886 ins_ra_rec.ussgl_transaction_code_context := old_rec_app.ussgl_transaction_code_context;
1887 ins_ra_rec.reversal_gl_date := NULL;
1888 ins_ra_rec.cash_receipt_history_id := new_crh_id;
1889 ins_ra_rec.application_ref_type := NULL;
1890 ins_ra_rec.application_ref_id := NULL;
1891 ins_ra_rec.application_ref_num := NULL;
1892 ins_ra_rec.secondary_application_ref_id := NULL;
1893 /* Bug 2254777 reason and cust reference */
1894 ins_ra_rec.application_ref_reason := NULL;
1895 ins_ra_rec.customer_reference := NULL;
1896 ins_ra_rec.payment_set_id := NULL;
1897 /*Bug3505753 */
1898 ins_ra_rec.link_to_customer_trx_id:=NULL;
1899
1900 temp_num := NULL; --bug6271951
1901 arp_app_pkg.insert_p( ins_ra_rec, temp_num );
1902
1903 --Bug#2750340
1904 l_xla_ev_rec.xla_from_doc_id := temp_num;
1905 l_xla_ev_rec.xla_to_doc_id := temp_num;
1906 l_xla_ev_rec.xla_doc_table := 'APP';
1907 l_xla_ev_rec.xla_mode := 'O';
1908 l_xla_ev_rec.xla_call := 'B';
1909 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1910
1911
1912 --{BUG#5022786 Call the creation of APP distribution in LLCA mode if required
1913 IF old_rec_app.status = 'APP' AND l_upgrade_methode IN ('R12','R12_11IMFAR') THEN
1914
1915 arp_det_dist_pkg.get_gt_sequence
1916 (x_gt_id => l_gt_id,
1917 x_return_status => x_return_status,
1918 x_msg_count => x_msg_count,
1919 x_msg_data => x_msg_data);
1920
1921 INSERT INTO ar_line_app_detail_gt
1922 ( ACCTD_AMOUNT
1923 ,REF_ACCOUNT_CLASS
1924 ,AMOUNT
1925 ,APP_LEVEL
1926 ,BASE_CURRENCY
1927 ,ACTIVITY_BUCKET
1928 ,CCID
1929 ,GT_ID
1930 ,LEDGER_ID
1931 ,ORG_ID
1932 ,REF_CUSTOMER_TRX_ID
1933 ,REF_CUSTOMER_TRX_LINE_ID
1934 ,REF_CUST_TRX_LINE_GL_DIST_ID
1935 ,REF_LINE_ID
1936 ,SOURCE_ID
1937 ,SOURCE_TABLE
1938 ,SOURCE_TYPE
1939 ,TAXABLE_ACCTD_AMOUNT
1940 ,TAXABLE_AMOUNT
1941 ,TAX_INC_FLAG
1942 ,TAX_LINK_ID
1943 ,TO_CURRENCY
1944 ,REF_MF_DIST_FLAG)
1945 SELECT
1946 DECODE(ard.activity_bucket,
1947 'APP_LINE' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1948 'APP_TAX' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1949 'APP_FRT' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1950 'APP_CHRG' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
1951 (NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0))) -- ACCTD_AMOUNT
1952 ,ard.ref_account_class -- REF_ACCOUNT_CLASS
1953 ,DECODE(ard.activity_bucket,
1954 'APP_LINE' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1955 'APP_TAX' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1956 'APP_FRT' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1957 'APP_CHRG' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
1958 (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0))) -- AMOUNT
1959 ,'LINE' -- APP_LEVEL
1960 ,arp_global.functional_currency -- BASE_CURRENCY
1961 ,ard.ACTIVITY_BUCKET -- ACTIVITY_BUCKET
1962 ,ard.code_combination_id -- CCID
1963 ,l_gt_id -- GT_ID
1964 ,ora.set_of_books_id -- LEDGER_ID
1965 ,ard.org_id -- ORG_ID
1966 ,ora.applied_customer_trx_id -- REF_CUSTOMER_TRX_ID
1967 ,ard.ref_customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
1968 ,ard.ref_cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
1969 ,ard.ref_line_id -- REF_LINE_ID
1970 ,ard.source_id -- SOURCE_ID
1971 ,ard.source_table -- SOURCE_TABLE
1972 ,ora.application_type -- SOURCE_TYPE
1973 ,'' -- TAXABLE_ACCTD_AMOUNT
1974 ,'' -- TAXABLE_AMOUNT
1975 ,'' -- TAX_INC_FLAG
1976 ,'' -- TAX_LINK_ID
1977 ,trx.invoice_currency_code -- TO_CURRENCY
1978 ,'' -- REF_MF_DIST_FLAG
1979 FROM ar_distributions ard,
1980 ar_receivable_applications ora,
1981 ra_customer_trx trx
1982 WHERE ora.receivable_application_id = old_rec_app.receivable_application_id
1983 AND ard.source_table = 'RA'
1984 AND ard.source_id = ora.receivable_application_id
1985 AND ard.activity_bucket IS NOT NULL
1986 AND ora.applied_customer_trx_id = trx.customer_trx_id;
1987
1988 arp_acct_main.Create_Acct_Entry(
1989 p_ae_doc_rec => l_ae_doc_rec,
1990 p_client_server => NULL,
1991 p_from_llca_call=> 'Y',
1992 p_gt_id => l_gt_id);
1993
1994
1995 IF l_reset_rem = 'Y' THEN
1996 FORALL i IN l_customer_trx_line_id.FIRST ..l_customer_trx_line_id.LAST
1997 UPDATE ra_customer_trx_lines SET
1998 ACCTD_AMOUNT_DUE_REMAINING = l_ACCTD_AMOUNT_DUE_REMAINING(i),
1999 AMOUNT_DUE_REMAINING = l_AMOUNT_DUE_REMAINING(i),
2000 CHRG_ACCTD_AMOUNT_REMAINING= l_CHRG_ACCTD_AMOUNT_REMAINING(i),
2001 CHRG_AMOUNT_REMAINING = l_CHRG_AMOUNT_REMAINING(i),
2002 FRT_ADJ_ACCTD_REMAINING = l_FRT_ADJ_ACCTD_REMAINING(i),
2003 FRT_ADJ_REMAINING = l_FRT_ADJ_REMAINING(i),
2004 FRT_ED_ACCTD_AMOUNT = l_FRT_ED_ACCTD_AMOUNT(i),
2005 FRT_ED_AMOUNT = l_FRT_ED_AMOUNT(i),
2006 FRT_UNED_ACCTD_AMOUNT = l_FRT_UNED_ACCTD_AMOUNT(i),
2007 FRT_UNED_AMOUNT = l_FRT_UNED_AMOUNT(i)
2008 WHERE customer_trx_line_id = l_customer_trx_line_id(i);
2009
2010 l_reset_rem := 'N';
2011
2012
2013 END IF;
2014 --}
2015
2016 ELSE
2017 --Call the creation of distributions in normal case
2018 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2019 END IF;
2020 --}
2021
2022 --
2023 --Release 11.5 VAT changes, create the
2024 --complementary UNAPP record
2025 --accounting.
2026 --
2027 l_ae_doc_rec.document_type := 'RECEIPT';
2028 l_ae_doc_rec.document_id := cr.cash_receipt_id;
2029 l_ae_doc_rec.accounting_entity_level := 'ONE';
2030 l_ae_doc_rec.source_table := 'RA';
2031 l_ae_doc_rec.source_id := temp_num; --new UNAPP record
2032 l_ae_doc_rec.source_id_old := l_app_id; --paired APP record
2033 l_ae_doc_rec.other_flag := 'PAIR';
2034 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2035
2036 END IF; --end if for maximum write-off amount check
2037
2038 /* Bug 2821139 - if a netting application the opposing
2039 application on the netted receipt is reversed/reapplied */
2040 IF old_rec_app.receivables_trx_id = -16 THEN
2041
2042 arp_app_pkg.fetch_p (old_rec_app.applied_rec_app_id
2043 , net_ra_rec);
2044
2045
2046
2047 arp_process_application.reverse(
2048 p_ra_id => old_rec_app.applied_rec_app_id
2049 , p_reversal_gl_date => GREATEST(old_rec_app.gl_date, new_adj.gl_date)
2050 , p_reversal_date => TRUNC(SYSDATE)
2051 , p_module_name => 'ARPLRADB'
2052 , p_module_version => '1.0'
2053 , p_bal_due_remaining => l_bal_due_remaining
2054 , p_called_from => 'ARPLRADB');
2055
2056 arp_process_application.activity_application (
2057 p_receipt_ps_id => net_ra_rec.payment_schedule_id,
2058 p_application_ps_id => net_ra_rec.applied_payment_schedule_id,
2059 p_link_to_customer_trx_id => net_ra_rec.link_to_customer_trx_id,
2060 p_amount_applied => net_ra_rec.amount_applied,
2061 p_apply_date => net_ra_rec.apply_date,
2062 p_gl_date => GREATEST(old_rec_app.gl_date, new_adj.gl_date),
2063 p_receivables_trx_id => net_ra_rec.receivables_trx_id,
2064 p_ussgl_transaction_code => net_ra_rec.ussgl_transaction_code,
2065 p_attribute_category=> net_ra_rec.attribute_category,
2066 p_attribute1 => net_ra_rec.attribute1,
2067 p_attribute2 => net_ra_rec.attribute2,
2068 p_attribute3 => net_ra_rec.attribute3,
2069 p_attribute4 => net_ra_rec.attribute4,
2070 p_attribute5 => net_ra_rec.attribute5,
2071 p_attribute6 => net_ra_rec.attribute6,
2072 p_attribute7 => net_ra_rec.attribute7,
2073 p_attribute8 => net_ra_rec.attribute8,
2074 p_attribute9 => net_ra_rec.attribute9,
2075 p_attribute10 => net_ra_rec.attribute10,
2076 p_attribute11 => net_ra_rec.attribute11,
2077 p_attribute12 => net_ra_rec.attribute12,
2078 p_attribute13 => net_ra_rec.attribute13,
2079 p_attribute14 => net_ra_rec.attribute14,
2080 p_attribute15 => net_ra_rec.attribute15,
2081 p_global_attribute1 => net_ra_rec.global_attribute1,
2082 p_global_attribute2 => net_ra_rec.global_attribute2,
2083 p_global_attribute3 => net_ra_rec.global_attribute3,
2084 p_global_attribute4 => net_ra_rec.global_attribute4,
2085 p_global_attribute5 => net_ra_rec.global_attribute5,
2086 p_global_attribute6 => net_ra_rec.global_attribute6,
2087 p_global_attribute7 => net_ra_rec.global_attribute7,
2088 p_global_attribute8 => net_ra_rec.global_attribute8,
2089 p_global_attribute9 => net_ra_rec.global_attribute9,
2090 p_global_attribute10 => net_ra_rec.global_attribute10,
2091 p_global_attribute11 => net_ra_rec.global_attribute11,
2092 p_global_attribute12 => net_ra_rec.global_attribute12,
2093 p_global_attribute13 => net_ra_rec.global_attribute13,
2094 p_global_attribute14 => net_ra_rec.global_attribute14,
2095 p_global_attribute15 => net_ra_rec.global_attribute15,
2096 p_global_attribute16 => net_ra_rec.global_attribute16,
2097 p_global_attribute17 => net_ra_rec.global_attribute17,
2098 p_global_attribute18 => net_ra_rec.global_attribute18,
2099 p_global_attribute19 => net_ra_rec.global_attribute19,
2100 p_global_attribute20 => net_ra_rec.global_attribute20,
2101 p_global_attribute_category => net_ra_rec.global_attribute_category,
2102 p_module_name => 'ARPLRADB',
2103 p_comments => net_ra_rec.comments,
2104 p_application_ref_type => net_ra_rec.application_ref_type,
2105 p_application_ref_id => net_ra_rec.application_ref_id,
2106 p_application_ref_num => net_ra_rec.application_ref_num,
2107 p_secondary_application_ref_id => net_ra_rec.secondary_application_ref_id,
2108 p_payment_set_id => net_ra_rec.payment_set_id,
2109 p_module_version => '1.0',
2110 p_out_rec_application_id => l_new_net_rec_app_id,
2111 p_customer_reference => net_ra_rec.customer_reference,
2112 p_netted_receipt_flag => 'Y',
2113 p_netted_cash_receipt_id => net_ra_rec.cash_receipt_id
2114 );
2115
2116 -- Updating both new activity records with each others
2117 -- new rec app id
2118 arp_app_pkg.fetch_p(l_new_rec_app_id, net_ra_rec );
2119 net_ra_rec.applied_rec_app_id := l_new_net_rec_app_id;
2120 arp_app_pkg.update_p( net_ra_rec );
2121
2122 arp_app_pkg.fetch_p (l_new_net_rec_app_id, net_ra_rec);
2123 net_ra_rec.applied_rec_app_id := l_new_rec_app_id;
2124 arp_app_pkg.update_p( net_ra_rec );
2125
2126 END IF;
2127
2128 END IF; -- end if status
2129 END IF; --end if for unid
2130
2131 temp_num := NULL; --bug6271951
2132 END LOOP;
2133
2134
2135 IF total_unid = 0 THEN
2136
2137 -- create an 'UNAPP' record for the value of the cr
2138 ins_ra_rec.acctd_amount_applied_from := cr_acctd_amount;
2139 ins_ra_rec.amount_applied := cr.amount;
2140 ins_ra_rec.amount_applied_from := NULL;
2141 ins_ra_rec.trans_to_receipt_rate := NULL;
2142 ins_ra_rec.application_rule := 'RATE ADJUSTMENT TRIGGER';
2143 ins_ra_rec.application_type := 'CASH';
2144 ins_ra_rec.apply_date := new_crh.trx_date;
2145 ins_ra_rec.code_combination_id := cr.unapplied_ccid;
2146 ins_ra_rec.created_by := new_adj.created_by;
2147 ins_ra_rec.creation_date := new_adj.creation_date;
2148 ins_ra_rec.display := 'N';
2149 ins_ra_rec.gl_date := new_crh.gl_date;
2150 ins_ra_rec.last_updated_by := new_adj.last_updated_by;
2151 ins_ra_rec.last_update_date := new_adj.last_update_date;
2152 ins_ra_rec.payment_schedule_id := cr.payment_schedule_id;
2153 ins_ra_rec.set_of_books_id := cr.set_of_books_id;
2154 ins_ra_rec.status := 'UNAPP';
2155 ins_ra_rec.acctd_amount_applied_to := NULL;
2156 ins_ra_rec.acctd_earned_discount_taken := NULL;
2157 ins_ra_rec.acctd_unearned_discount_taken := NULL;
2158 ins_ra_rec.applied_customer_trx_id := NULL;
2159 ins_ra_rec.applied_customer_trx_line_id := NULL;
2160 ins_ra_rec.applied_payment_schedule_id := NULL;
2161 ins_ra_rec.cash_receipt_id := cr.cash_receipt_id;
2162 ins_ra_rec.comments := NULL;
2163 ins_ra_rec.confirmed_flag := 'Y';
2164 ins_ra_rec.customer_trx_id := NULL;
2165 ins_ra_rec.days_late := NULL;
2166 ins_ra_rec.earned_discount_taken := NULL;
2167 ins_ra_rec.freight_applied := NULL;
2168 ins_ra_rec.gl_posted_date := NULL;
2169 ins_ra_rec.last_update_login := new_adj.last_update_login;
2170 ins_ra_rec.line_applied := NULL;
2171 ins_ra_rec.on_account_customer := NULL;
2172 ins_ra_rec.postable := NULL;
2173 ins_ra_rec.posting_control_id := -3;
2174 ins_ra_rec.program_application_id := NULL;
2175 ins_ra_rec.program_id := NULL;
2176 ins_ra_rec.program_update_date := NULL;
2177 ins_ra_rec.receivables_charges_applied := NULL;
2178 ins_ra_rec.receivables_trx_id := NULL;
2179 ins_ra_rec.request_id := NULL;
2180 ins_ra_rec.tax_applied := NULL;
2181 ins_ra_rec.unearned_discount_taken := NULL;
2182 ins_ra_rec.unearned_discount_ccid := NULL;
2183 ins_ra_rec.earned_discount_ccid := NULL;
2184 ins_ra_rec.ussgl_transaction_code := NULL;
2185 ins_ra_rec.ussgl_transaction_code_context := NULL;
2186 ins_ra_rec.reversal_gl_date := NULL;
2187 ins_ra_rec.cash_receipt_history_id := new_crh_id;
2188 ins_ra_rec.application_ref_type := NULL;
2189 ins_ra_rec.application_ref_id := NULL;
2190 ins_ra_rec.application_ref_num := NULL;
2191 ins_ra_rec.secondary_application_ref_id := NULL;
2192 ins_ra_rec.application_ref_reason := NULL;
2193 ins_ra_rec.customer_reference := NULL;
2194 /*Bug3505753 */
2195 ins_ra_rec.link_to_customer_trx_id:=NULL;
2196
2197 arp_app_pkg.insert_p( ins_ra_rec, temp_num );
2198
2199
2200 --Bug#2750340
2201 l_xla_ev_rec.xla_from_doc_id := temp_num;
2202 l_xla_ev_rec.xla_to_doc_id := temp_num;
2203 l_xla_ev_rec.xla_doc_table := 'APP';
2204 l_xla_ev_rec.xla_mode := 'O';
2205 l_xla_ev_rec.xla_call := 'B';
2206 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
2207
2208 --
2209 --Release 11.5 VAT changes, create the UNAPP record accounting.
2210 --
2211 l_ae_doc_rec.document_type := 'RECEIPT';
2212 l_ae_doc_rec.document_id := cr.cash_receipt_id;
2213 l_ae_doc_rec.accounting_entity_level := 'ONE';
2214 l_ae_doc_rec.source_table := 'RA';
2215 l_ae_doc_rec.source_id := temp_num; --new UNAPP record
2216 l_ae_doc_rec.source_id_old := '';
2217 l_ae_doc_rec.other_flag := '';
2218 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2219
2220 ELSE
2221 ins_ra_rec.acctd_amount_applied_from := cr_acctd_amount;
2222 ins_ra_rec.amount_applied := cr.amount;
2223 ins_ra_rec.amount_applied_from := NULL;
2224 ins_ra_rec.trans_to_receipt_rate := NULL;
2225 ins_ra_rec.application_rule := 'RATE ADJUSTMENT TRIGGER';
2226 ins_ra_rec.application_type := 'CASH';
2227 ins_ra_rec.apply_date := new_crh.trx_date;
2228 ins_ra_rec.code_combination_id := cr.unidentified_ccid;
2229 ins_ra_rec.created_by := new_adj.created_by;
2230 ins_ra_rec.creation_date := new_adj.creation_date;
2231 ins_ra_rec.display := 'N';
2232 ins_ra_rec.gl_date := new_crh.gl_date;
2233 ins_ra_rec.last_updated_by := new_adj.last_updated_by;
2234 ins_ra_rec.last_update_date := new_adj.last_update_date;
2235 ins_ra_rec.payment_schedule_id := cr.payment_schedule_id;
2236 ins_ra_rec.set_of_books_id := cr.set_of_books_id;
2237 ins_ra_rec.status := 'UNID';
2238 ins_ra_rec.acctd_amount_applied_to := NULL;
2239 ins_ra_rec.acctd_earned_discount_taken := NULL;
2240 ins_ra_rec.acctd_unearned_discount_taken := NULL;
2241 ins_ra_rec.applied_customer_trx_id := NULL;
2242 ins_ra_rec.applied_customer_trx_line_id := NULL;
2243 ins_ra_rec.applied_payment_schedule_id := NULL;
2244 ins_ra_rec.cash_receipt_id := cr.cash_receipt_id;
2245 ins_ra_rec.comments := NULL;
2246 ins_ra_rec.confirmed_flag := 'Y';
2247 ins_ra_rec.customer_trx_id := NULL;
2248 ins_ra_rec.days_late := NULL;
2249 ins_ra_rec.earned_discount_taken := NULL;
2250 ins_ra_rec.freight_applied := NULL;
2251 ins_ra_rec.gl_posted_date := NULL;
2252 ins_ra_rec.last_update_login := new_adj.last_update_login;
2253 ins_ra_rec.line_applied := NULL;
2254 ins_ra_rec.on_account_customer := NULL;
2255 ins_ra_rec.postable := NULL;
2256 ins_ra_rec.posting_control_id := -3;
2257 ins_ra_rec.program_application_id := NULL;
2258 ins_ra_rec.program_id := NULL;
2259 ins_ra_rec.program_update_date := NULL;
2260 ins_ra_rec.receivables_charges_applied := NULL;
2261 ins_ra_rec.receivables_trx_id := NULL;
2262 ins_ra_rec.request_id := NULL;
2263 ins_ra_rec.tax_applied := NULL;
2264 ins_ra_rec.unearned_discount_taken := NULL;
2265 ins_ra_rec.unearned_discount_ccid := NULL;
2266 ins_ra_rec.earned_discount_ccid := NULL;
2267 ins_ra_rec.ussgl_transaction_code := NULL;
2268 ins_ra_rec.ussgl_transaction_code_context := NULL;
2269 ins_ra_rec.reversal_gl_date := NULL;
2270 ins_ra_rec.cash_receipt_history_id := new_crh_id;
2271 ins_ra_rec.application_ref_type := NULL;
2272 ins_ra_rec.application_ref_id := NULL;
2273 ins_ra_rec.application_ref_num := NULL;
2274 ins_ra_rec.secondary_application_ref_id := NULL;
2275 ins_ra_rec.application_ref_reason := NULL;
2276 ins_ra_rec.customer_reference := NULL;
2277 /*Bug3505753 */
2278 ins_ra_rec.link_to_customer_trx_id:=NULL;
2279
2280
2281 arp_app_pkg.insert_p( ins_ra_rec, temp_num );
2282
2283
2284 --Bug#2750340
2285 l_xla_ev_rec.xla_from_doc_id := temp_num;
2286 l_xla_ev_rec.xla_to_doc_id := temp_num;
2287 l_xla_ev_rec.xla_doc_table := 'APP';
2288 l_xla_ev_rec.xla_mode := 'O';
2289 l_xla_ev_rec.xla_call := 'B';
2290 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
2291
2292 --
2293 --Release 11.5 VAT changes, create the UNID record accounting.
2294 --
2295 l_ae_doc_rec.document_type := 'RECEIPT';
2296 l_ae_doc_rec.document_id := cr.cash_receipt_id;
2297 l_ae_doc_rec.accounting_entity_level := 'ONE';
2298 l_ae_doc_rec.source_table := 'RA';
2299 l_ae_doc_rec.source_id := temp_num; --new UNID record
2300 l_ae_doc_rec.source_id_old := '';
2301 l_ae_doc_rec.other_flag := '';
2302 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
2303
2304 END IF;
2305 --arp_standard.debug('HYU-5');
2306
2307 /* Bug No. 3825830
2308 Update the gl_date_closed of reciept in the payment schedules if the status is closed and
2309 the current gl_date closed is less than gl_date of the rate adjustment */
2310
2311 SELECT gl_date_closed,status
2312 INTO l_rct_gl_date_closed,l_rct_ps_status
2313 FROM ar_payment_schedules
2314 WHERE payment_schedule_id= cr.payment_schedule_id;
2315 IF ((l_rct_gl_date_closed < nvl(new_crh.gl_date,l_rct_gl_date_closed))
2316 AND l_rct_ps_status='CL') THEN
2317 UPDATE ar_payment_schedules
2318 SET gl_date_closed = new_crh.gl_date
2319 WHERE payment_schedule_id = cr.payment_schedule_id;
2320 END IF;
2321 END IF;
2322 --
2323 IF PG_DEBUG in ('Y', 'C') THEN
2324 arp_standard.debug( '<< ARBRAD MAIN' );
2325 END IF;
2326 EXCEPTION
2327 WHEN claim_create_api_error THEN
2328 IF PG_DEBUG in ('Y', 'C') THEN
2329 arp_standard.debug('claim_create_api_error - ARP_RATE_ADJ.MAIN' );
2330 END IF;
2331 RAISE;
2332
2333 WHEN claim_cancel_api_error THEN
2334 IF PG_DEBUG in ('Y', 'C') THEN
2335 arp_standard.debug('claim_cancel_api_error - ARP_RATE_ADJ.MAIN' );
2336 END IF;
2337 RAISE;
2338
2339 WHEN OTHERS THEN
2340 IF PG_DEBUG in ('Y', 'C') THEN
2341 arp_standard.debug('EXCEPTION: ARP_RATE_ADJ.MAIN');
2342 arp_standard.debug('EXCEPTION OTHERS: '||SQLERRM);
2343
2344 END IF;
2345 RAISE;
2346 --
2347 END main;
2348 --
2349 --gscc warning fix : moved initialization of package variable to the
2350 --new initialization section.
2351 begin
2352 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2353
2354 --
2355 END arp_rate_adj;