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