[Home] [Help]
PACKAGE BODY: APPS.ARP_CASHBOOK
Source
1 PACKAGE BODY ARP_CASHBOOK AS
2 /*$Header: ARRECBKB.pls 120.29.12020000.6 2013/02/20 18:25:35 rravikir ship $*/
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 g_refresh_running VARCHAR2(1);
6 --
7 -- Public Procedures
8 --
9 PROCEDURE clear(
10 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
11 p_trx_date IN ar_cash_receipt_history.trx_date%TYPE,
12 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
13 p_actual_value_date IN DATE,
14 p_exchange_date IN ar_cash_receipt_history.exchange_date%TYPE,
15 p_exchange_rate_type IN ar_cash_receipt_history.exchange_rate_type%TYPE,
16 p_exchange_rate IN ar_cash_receipt_history.exchange_rate%TYPE,
17 p_bank_currency IN ce_bank_accounts.currency_code%TYPE,
18 p_amount_cleared IN ar_cash_receipt_history.amount%TYPE,
19 p_amount_factored IN ar_cash_receipt_history.factor_discount_amount%TYPE,
20 p_module_name IN VARCHAR2,
21 p_module_version IN VARCHAR2,
22 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
23 --
24 /*----------------------------------
25 Some notes to use this clear procedure:
26
27 1. The p_amount_cleared and p_amount_factored to be passed in
28 should be in the bank currency.
29
30 2. If p_bank_currency <> the currency of the receipt, this
31 means the p_bank_currency must be the functional currency,
32 In this case, it assumes the following has been
33 validated before calling this procedure:
34
35 p_amount_cleared+p_amount_factored =
36 p_exchange_rate * ar_cash_receipts.amount
37
38 3. If p_bank_currency = the currency of the receipt,
39 In this case, it assumes the following has been validated
40 before calling this procedure:
41
42 p_amount_cleared+p_amount_factored =
43 ar_cash_receipts.amount
44
45 ------------------------------------*/
46 l_cr_rec ar_cash_receipts%ROWTYPE;
47 l_rma_rec ar_receipt_method_accounts%ROWTYPE;
48 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
49 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
50 l_dist_rec ar_distributions%ROWTYPE;
51 l_radj_rec ar_rate_adjustments%ROWTYPE;
52 l_acctd_receipt_amt_new ar_cash_receipt_history.acctd_amount%TYPE;
53 l_acctd_receipt_amt_old ar_cash_receipt_history.acctd_amount%TYPE;
54 l_cash_amt ar_cash_receipt_history.amount%TYPE;
55 l_acctd_cash_amt ar_cash_receipt_history.acctd_amount%TYPE;
56 l_bank_amt ar_cash_receipt_history.factor_discount_amount%TYPE;
57 l_acctd_bank_amt ar_cash_receipt_history.acctd_factor_discount_amount%TYPE;
58 l_convert_receipt_amt ar_cash_receipts.amount%TYPE;
59 --Bug#2750340
60 l_event_rec arp_xla_events.xla_events_type;
61 l_org_id number;
62
63 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
64 l_payment_trxn_extn_id iby_fndcpt_tx_operations.trxn_extension_id%type;
65 l_status BOOLEAN := FALSE;
66 l_settle_error_message varchar2(2000);
67 settlement_pending_raise exception;
68
69 l_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
70 l_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
71 l_exchange_rate_type ar_cash_receipt_history.exchange_rate_type%TYPE;
72
73 /* 9363502 - define tables used by refresh_at_risk_value */
74 l_customer_id_tab ar_bus_event_sub_pvt.generic_id_type;
75 l_site_use_id_tab ar_bus_event_sub_pvt.generic_id_type;
76 l_org_id_tab ar_bus_event_sub_pvt.generic_id_type;
77 l_currency_tab ar_bus_event_sub_pvt.currency_type;
78 /* end 9363502 */
79
80 BEGIN
81
82 IF PG_DEBUG in ('Y', 'C') THEN
83 arp_standard.debug('clear: ' || 'p_exchange_rate_type = ' || p_exchange_rate_type);
84 arp_standard.debug('clear: ' || 'p_exchange_rate = ' || to_char(p_exchange_rate));
85 arp_standard.debug('clear: ' || 'p_bank_currency = ' || p_bank_currency);
86 arp_standard.debug('p_amount_cleared = ' || to_char(p_amount_cleared));
87 arp_standard.debug('clear: ' || 'p_amount_factored = ' || to_char(p_amount_factored));
88 arp_standard.debug('p_cr_id:'|| p_cr_id);
89 arp_standard.debug('p_trx_date :'||p_trx_date);
90 arp_standard.debug('p_gl_date :'||p_gl_date);
91 arp_standard.debug('p_actual_value_date :'||p_actual_value_date);
92
93 arp_util.debug( '>>>>>>> arp_cashbook.clear' );
94 END IF;
95
96 -- Bug 7443802
97
98 begin
99 select org_id into l_org_id
100 from ar_cash_receipts_all
101 where cash_receipt_id = p_cr_id;
102
103 mo_global.init('AR');
104 mo_global.set_policy_context('S',l_org_id);
105
106 exception
107 when others then
108 arp_standard.debug('Unable to drive the org id for p_cr_id:'|| p_cr_id);
109 end;
110
111 -- Bug 7443802 END
112
113 --Setting the Org Context Bug5212892
114 ar_mo_global_cache.populate;
115 arp_global.init_global(mo_global.get_current_org_id);
116 arp_standard.init_standard(mo_global.get_current_org_id);
117
118 /* Bug 7828491: Check for settlement status in IBY summaries table.
119 Do not allow user to clear the receipt.Raise error if it is still pending.*/
120
121 BEGIN
122 IF PG_DEBUG in ('Y', 'C') THEN
123 arp_standard.debug('Check for settlement status in IBY table');
124 END IF;
125
126 SELECT receipt_number, payment_trxn_extension_id
127 into l_receipt_number, l_payment_trxn_extn_id
128 FROM ar_cash_receipts
129 WHERE cash_receipt_id = p_cr_id ;
130
131 IF PG_DEBUG in ('Y', 'C') THEN
132 arp_standard.debug('l_receipt_number: '|| l_receipt_number);
133 arp_standard.debug('l_payment_trxn_extn_id: '|| l_payment_trxn_extn_id);
134 END IF;
135
136 IF l_payment_trxn_extn_id is not null then
137
138 BEGIN
139 /* Call to check the settlement status of an extension.
140 Reusing the function from ARP_REVERSE_RECEIPT doing
141 the same. */
142 l_status := ARP_REVERSE_RECEIPT.check_settlement_status(
143 l_payment_trxn_extn_id);
144
145 IF l_status then
146
147 -- Only status = 0 are success cases whose settlement is completed in Payments.
148 -- This is an error staus, so error has to be raised.
149 IF p_module_name = 'AR_AUTOMATIC_CLEARING' THEN
150 -- If call is from AR conc request, put messages in fnd log file
151 FND_MESSAGE.SET_NAME('AR','AR_IBY_SETTLEMENT_PENDING_CLR');
152 FND_MESSAGE.SET_TOKEN('RECEIPT_NUMBER',l_receipt_number);
153 l_settle_error_message := FND_MESSAGE.GET;
154 fnd_file.put_line(FND_FILE.LOG,l_settle_error_message);
155
156 l_settle_error_message := 'Extension ID queried is: ' || l_payment_trxn_extn_id;
157 fnd_file.put_line(FND_FILE.LOG,l_settle_error_message);
158 ELSE
159 -- If call is not from AR conc request, raise FND exception raise.
160 FND_MESSAGE.SET_NAME('AR','AR_IBY_SETTLEMENT_PENDING_CLR');
161 FND_MESSAGE.SET_TOKEN('RECEIPT_NUMBER',l_receipt_number);
162 RAISE settlement_pending_raise;
163 END IF;
164
165 RETURN ;
166 END IF;
167
168 EXCEPTION
169 when settlement_pending_raise then
170 raise;
171 WHEN OTHERS THEN
172 -- Still raise the error, As the receipt is remitted but no record in IBY table.
173 IF p_module_name = 'AR_AUTOMATIC_CLEARING' THEN
174 FND_MESSAGE.SET_NAME('AR','AR_IBY_SETTLEMENT_PENDING_CLR');
175 FND_MESSAGE.SET_TOKEN('RECEIPT_NUMBER',l_receipt_number);
176 l_settle_error_message := FND_MESSAGE.GET;
177 fnd_file.put_line(FND_FILE.LOG,l_settle_error_message);
178
179 l_settle_error_message := 'Extension ID queried is: ' || l_payment_trxn_extn_id;
180 fnd_file.put_line(FND_FILE.LOG,l_settle_error_message);
181 ELSE
182 FND_MESSAGE.SET_NAME('AR','AR_IBY_SETTLEMENT_PENDING_CLR');
183 FND_MESSAGE.SET_TOKEN('RECEIPT_NUMBER',l_receipt_number);
184 RAISE settlement_pending_raise;
185 END IF;
186
187 RETURN ;
188 END;
189
190 END IF;
191
192 EXCEPTION
193 WHEN settlement_pending_raise then
194 RAISE settlement_pending_raise;
195 WHEN OTHERS THEN
196 IF p_module_name = 'AR_AUTOMATIC_CLEARING' THEN
197 l_settle_error_message := 'Exception while quering cash receipt ID: '|| p_cr_id ;
198 fnd_file.put_line(FND_FILE.LOG,l_settle_error_message);
199 ELSE
200 FND_MESSAGE.SET_NAME('AR','GENERIC_MESSAGE');
201 APP_EXCEPTION.RAISE_EXCEPTION;
202 END IF;
203 RETURN ;
204 END;
205
206 -- Assume this receipt has already been locked
207
208 -- Validate the GL Date is in open or future period
209
210 -- Validate exchange info is correct : all missing or all provided
211
212 -- Validate the amt_clr +bank_charge = receipt amt
213 -- receipt amt * rate = accnt amount
214
215 -- Fetch the history record
216 arp_cr_history_pkg.fetch_f_crid( p_cr_id, l_crh_rec_old );
217
218 --Bug8866537
219 l_exchange_rate_type := NVL(p_exchange_rate_type,l_crh_rec_old.exchange_rate_type);
220 l_exchange_date := trunc(NVL(p_exchange_date,SYSDATE));
221
222
223 -- check if receipt is reversed. If yes, raise exception.
224 -- (bug 376817)
225
226 IF ( l_crh_rec_old.status = 'REVERSED' ) THEN
227 fnd_message.set_name('AR', 'AR_CANNOT_CLEAR_REV_RECEIPT' );
228 app_exception.raise_exception;
229 END IF;
230
231 -- Fetch the cash receipt record
232 l_cr_rec.cash_receipt_id := p_cr_id;
233 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
234
235
236 -- Fetch the receipt method bank account record
237 arp_rm_accounts_pkg.fetch_p( l_cr_rec.receipt_method_id,
238 l_cr_rec.remit_bank_acct_use_id,
239 l_rma_rec );
240
241 -- Insert a new history record
242
243 -- Calculate entered amount and acctd amount
244 -- If the receipt is functional currency, then amount and
245 -- acctd amounts are the same.
246 -- If bank currency is the same as the receipt's currency,
247 -- then the amount_cleared passed in is in the entered amount.
248 -- If bank currency is not the same as the receipt's currency,
249 -- then the amount_cleared passed in is in the functional amount.
250 l_acctd_receipt_amt_old := l_crh_rec_old.acctd_amount + nvl(L_crh_rec_old.acctd_factor_discount_amount,0);
251
252 IF PG_DEBUG in ('Y', 'C') THEN
253 arp_standard.debug('clear: ' || 'l_cr_rec.currency_code = ' || l_cr_rec.currency_code);
254 arp_standard.debug('clear: ' || 'ARP_GLOBAL.functional_currency = ' || ARP_GLOBAL.functional_currency);
255 arp_standard.debug('clear: ' || 'l_crh_rec_old.exchange_rate = ' || to_char(l_crh_rec_old.exchange_rate));
256 END IF;
257
258 IF ( l_cr_rec.currency_code = ARP_GLOBAL.functional_currency )
259 THEN
260 l_acctd_receipt_amt_new := l_cr_rec.amount;
261 l_crh_rec_new.amount := p_amount_cleared;
262 l_crh_rec_new.acctd_amount := p_amount_cleared;
263 l_crh_rec_new.factor_discount_amount := p_amount_factored;
264 l_crh_rec_new.acctd_factor_discount_amount := p_amount_factored;
265 ELSE
266 IF p_bank_currency = l_cr_rec.currency_code
267 THEN
268 IF ( NVL(p_exchange_rate,-1) <> l_crh_rec_old.exchange_rate )
269 THEN
270 -- Changes for triangulation: If exchange rate type is not
271 -- user, call GL API to calculate accounted amount
272
273 -- Bug 925765: instead of l_cr_rec.exchange_rate_type
274 -- (i.e. old rate type) use the new one for comparison
275 -- with 'User'!
276
277 IF (p_exchange_rate_type IS NOT NULL) AND (p_exchange_rate_type = 'User') THEN
278 l_acctd_receipt_amt_new := arp_util.functional_amount(
279 l_cr_rec.amount,
280 ARP_GLOBAL.functional_currency,
281 nvl(p_exchange_rate,1),
282 NULL,NULL );
283
284 l_exchange_rate := nvl(p_exchange_rate,1);
285
286 ELSIF (p_exchange_rate_type IS NULL) AND (l_exchange_rate_type = 'User') THEN
287 l_acctd_receipt_amt_new := arp_util.functional_amount(
288 l_cr_rec.amount,
289 ARP_GLOBAL.functional_currency,
290 nvl(l_crh_rec_old.exchange_rate,1),
291 NULL,NULL );
292
293 l_exchange_rate := nvl(l_crh_rec_old.exchange_rate,1);
294 ELSE
295 l_acctd_receipt_amt_new := gl_currency_api.convert_amount(
296 l_cr_rec.currency_code,
297 ARP_GLOBAL.functional_currency,
298 l_exchange_date,
299 l_exchange_rate_type,
300 l_cr_rec.amount);
301
302 l_exchange_rate := gl_currency_api.get_rate(
303 l_cr_rec.currency_code,
304 ARP_GLOBAL.functional_currency,
305 l_exchange_date,
306 l_exchange_rate_type);
307 END IF;
308 ELSE
309 l_acctd_receipt_amt_new := l_acctd_receipt_amt_old;
310
311 END IF;
312
313 l_crh_rec_new.amount := p_amount_cleared;
314 -- Changes for triangulation: If exchange rate type is not
315 -- user, call GL API to calculate accounted amount
316
317 -- Bug 925765: instead of l_cr_rec.exchange_rate_type
318 -- (i.e. old rate type) use the new one for comparison
319 -- with 'User'!
320
321 IF (p_exchange_rate_type IS NOT NULL) AND (p_exchange_rate_type = 'User') THEN
322 l_crh_rec_new.acctd_amount := arp_util.functional_amount(
323 p_amount_cleared,
324 ARP_GLOBAL.functional_currency,
325 nvl(p_exchange_rate,1),
326 NULL,NULL );
327
328 l_exchange_rate := nvl(p_exchange_rate,1);
329
330 ELSIF (p_exchange_rate_type IS NULL) AND (l_exchange_rate_type = 'User') THEN
331 l_crh_rec_new.acctd_amount := arp_util.functional_amount(
332 p_amount_cleared,
333 ARP_GLOBAL.functional_currency,
334 nvl(l_crh_rec_old.exchange_rate,1),
335 NULL,NULL );
336
337 l_exchange_rate := nvl(l_crh_rec_old.exchange_rate,1);
338
339 ELSE
340 l_crh_rec_new.acctd_amount := gl_currency_api.convert_amount(
341 l_cr_rec.currency_code,
342 ARP_GLOBAL.functional_currency,
343 l_exchange_date,
344 l_exchange_rate_type,
345 p_amount_cleared);
346
347 l_exchange_rate := gl_currency_api.get_rate(
348 l_cr_rec.currency_code,
349 ARP_GLOBAL.functional_currency,
350 l_exchange_date,
351 l_exchange_rate_type);
352
353 END IF;
354
355 l_crh_rec_new.factor_discount_amount := p_amount_factored;
356 l_crh_rec_new.acctd_factor_discount_amount := l_acctd_receipt_amt_new - L_crh_rec_new.acctd_amount;
357
358 ELSE
359 IF ( NVL(p_exchange_rate,-1) <> l_crh_rec_old.exchange_rate )
360 THEN
361 l_acctd_receipt_amt_new := p_amount_cleared + p_amount_factored;
362 ELSE
363 l_acctd_receipt_amt_new := l_acctd_receipt_amt_old;
364 END IF;
365
366 -- Bug 646561
367 -- Convert the receipt amount to the same currency as the
368 -- cleared amount.
369 -- Changes for triangulation: If exchange rate type is not
370 -- user, call GL API to calculate accounted amount
371
372 -- Bug 925765: instead of l_cr_rec.exchange_rate_type
373 -- (i.e. old rate type) use the new one for comparison
374 -- with 'User'!
375
376 IF (p_exchange_rate_type IS NOT NULL) AND (p_exchange_rate_type = 'User') THEN
377 l_convert_receipt_amt := arp_util.functional_amount
378 (l_cr_rec.amount,
379 ARP_GLOBAL.functional_currency,
380 NVL(p_exchange_rate,1),null,null);
381
382 l_exchange_rate := nvl(p_exchange_rate,1);
383
384 ELSIF (p_exchange_rate_type IS NULL) AND (l_exchange_rate_type = 'User') THEN
385 l_convert_receipt_amt := arp_util.functional_amount
386 (l_cr_rec.amount,
387 ARP_GLOBAL.functional_currency,
388 NVL(l_crh_rec_old.exchange_rate,1),null,null);
389
390 l_exchange_rate := nvl(l_crh_rec_old.exchange_rate,1);
391
392 ELSE
393 l_convert_receipt_amt := gl_currency_api.convert_amount(
394 l_cr_rec.currency_code,
395 ARP_GLOBAL.functional_currency,
396 l_exchange_date,
397 l_exchange_rate_type,
398 l_cr_rec.amount);
399
400 l_exchange_rate := gl_currency_api.get_rate(
401 l_cr_rec.currency_code,
402 ARP_GLOBAL.functional_currency,
403 l_exchange_date,
404 l_exchange_rate_type);
405 END IF;
406
407 -- If the converted receipt amount is the same as the cleared
408 -- amount then we don't need to calculate a new receipt amount.
409
410 IF l_convert_receipt_amt = p_amount_cleared then
411
412 l_crh_rec_new.amount := l_cr_rec.amount;
413
414 ELSE
415
416 -- Changes for triangulation: If exchange rate type is not
417 -- user, call GL API to calculate accounted amount
418
419 -- Bug 925765: instead of l_cr_rec.exchange_rate_type
420 -- (i.e. old rate type) use the new one for comparison
421 -- with 'User'!
422
423 IF (p_exchange_rate_type IS NOT NULL) AND (p_exchange_rate_type = 'User') THEN
424 l_crh_rec_new.amount := arp_util.functional_amount(
425 p_amount_cleared,
426 l_cr_rec.currency_code,
427 1/nvl(p_exchange_rate,1),
428 NULL,NULL );
429
430 l_exchange_rate := 1/nvl(p_exchange_rate,1);
431
432 ELSIF (p_exchange_rate_type IS NULL) AND (l_exchange_rate_type = 'User') THEN
433 l_crh_rec_new.amount := arp_util.functional_amount(
434 p_amount_cleared,
435 l_cr_rec.currency_code,
436 1/nvl(l_crh_rec_old.exchange_rate,1),
437 NULL,NULL );
438
439 l_exchange_rate := 1/nvl(l_crh_rec_old.exchange_rate,1);
440
441 ELSE
442 l_crh_rec_new.amount := gl_currency_api.convert_amount(
443 ARP_GLOBAL.functional_currency,
444 l_cr_rec.currency_code,
445 l_exchange_date,
446 l_exchange_rate_type,
447 p_amount_cleared);
448
449 l_exchange_rate := gl_currency_api.get_rate(
450 ARP_GLOBAL.functional_currency,
451 l_cr_rec.currency_code,
452 l_exchange_date,
453 l_exchange_rate_type);
454 END IF;
455
456 END IF;
457
458 l_crh_rec_new.acctd_amount := p_amount_cleared;
459 l_crh_rec_new.factor_discount_amount := l_cr_rec.amount - L_crh_rec_new.amount;
460 l_crh_rec_new.acctd_factor_discount_amount := p_amount_factored;
461 END IF;
462 END IF;
463
464 IF ( p_exchange_date = l_crh_rec_old.exchange_date ) OR
465 ( (p_exchange_date IS NULL) AND (l_crh_rec_old.exchange_date IS NULL) )
466 THEN
467 l_crh_rec_new.exchange_date := l_crh_rec_old.exchange_date;
468 ELSE
469 l_crh_rec_new.exchange_date := l_exchange_date;
470 END IF;
471
472 IF ( p_exchange_rate = l_crh_rec_old.exchange_rate ) OR
473 ( (p_exchange_rate IS NULL) AND (l_crh_rec_old.exchange_rate IS NULL) )
474 THEN
475 l_crh_rec_new.exchange_rate := l_crh_rec_old.exchange_rate;
476 ELSE
477 l_crh_rec_new.exchange_rate := l_exchange_rate;
478 END IF;
479
480 IF ( p_exchange_rate_type = l_crh_rec_old.exchange_rate_type ) OR
481 ( (p_exchange_rate_type IS NULL) AND (l_crh_rec_old.exchange_rate_type IS NULL) )
482 THEN
483 l_crh_rec_new.exchange_rate_type := l_crh_rec_old.exchange_rate_type;
484 ELSE
485 l_crh_rec_new.exchange_rate_type := l_exchange_rate_type;
486 END IF;
487
488 -- 11.5 VAT changes:
489 -- modified to get the conversion information from the cash receipt history
490 -- record.
491 l_dist_rec.currency_code := l_cr_rec.currency_code;
492 l_dist_rec.currency_conversion_rate := l_crh_rec_new.exchange_rate;
493 l_dist_rec.currency_conversion_type := l_crh_rec_new.exchange_rate_type;
494 l_dist_rec.currency_conversion_date := l_crh_rec_new.exchange_date;
495 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
496 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
497
498 l_crh_rec_new.cash_receipt_id := p_cr_id;
499 l_crh_rec_new.status := 'CLEARED';
500 l_crh_rec_new.trx_date := p_trx_date;
501 l_crh_rec_new.first_posted_record_flag := 'N';
502 l_crh_rec_new.postable_flag := 'Y';
503 l_crh_rec_new.factor_flag := l_crh_rec_old.factor_flag;
504 l_crh_rec_new.gl_date := p_gl_date;
505 l_crh_rec_new.current_record_flag := 'Y';
506
507 l_crh_rec_new.batch_id := l_crh_rec_old.batch_id;
508 -- fix for bug # 766382
509 -- populating batch_id in ar_cash_receipt_history table
510 -- with the batch_id of the remittance record.
511 -- l_crh_rec_new.batch_id := NULL;
512
513 l_crh_rec_new.account_code_combination_id := l_rma_rec.cash_ccid;
514
515 l_crh_rec_new.reversal_gl_date := NULL;
516 l_crh_rec_new.reversal_cash_receipt_hist_id := NULL;
517
518 l_crh_rec_new.bank_charge_account_ccid := l_rma_rec.bank_charges_ccid;
519 l_crh_rec_new.posting_control_id := -3;
520 l_crh_rec_new.reversal_posting_control_id := NULL;
521 l_crh_rec_new.gl_posted_date := NULL;
522 l_crh_rec_new.reversal_gl_posted_date := NULL;
523 IF (l_crh_rec_old.status = 'CLEARED' )
524 THEN
525 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.prv_stat_cash_receipt_hist_id;
526 ELSE
527 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.cash_receipt_history_id;
528 END IF;
529 l_crh_rec_new.created_from := substrb(p_module_name||'ARP_CASHBOOK.CLEAR',1,30);
530 l_crh_rec_new.reversal_created_from := NULL;
531 arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
532
533 -- Update the old history record
534 l_crh_rec_old.current_record_flag := NULL;
535 l_crh_rec_old.reversal_gl_date := p_gl_date;
536 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_rec_new.cash_receipt_history_id;
537 l_crh_rec_old.reversal_posting_control_id := -3;
538 l_crh_rec_old.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.CLEAR',1,30);
539 arp_cr_history_pkg.update_p( l_crh_rec_old );
540
541 --Bug#2750340
542 --{BUG#5051143 - the cash book call is document based not request based
543 -- l_event_rec.xla_req_id := arp_global.request_id;
544 -- l_event_rec.xla_mode := 'B';
545 --}
546 l_event_rec.xla_from_doc_id := p_cr_id;
547 l_event_rec.xla_to_doc_id := p_cr_id;
548 l_event_rec.xla_doc_table := 'CRH';
549 l_event_rec.xla_mode := 'O';
550 l_event_rec.xla_call := 'B';
551 arp_xla_events.Create_Events(p_xla_ev_rec => l_event_rec );
552
553 -- Insert the cash account ar_distributions record
554 ------------------------------------------------------------------------------------
555 -- Removed the following 'if' as part of bug fix 868448
556 -- because we should be able to create zero dollar misc receipts and later clear them.
557 -- Because of this if, records are never created in ar_distributions and as a result
558 -- gl_transfer does'nt pick up these records to post
559 -----------------------------------------------------------------------------------
560 --IF ( l_crh_rec_new.amount <>0 ) OR
561 -- ( l_crh_rec_new.acctd_amount <> 0 )
562 --THEN
563 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
564 l_dist_rec.source_table := 'CRH';
565 l_dist_rec.source_type := 'CASH';
566
567 IF ( l_crh_rec_old.status = 'REMITTED' )
568 THEN
569 l_dist_rec.code_combination_id := l_rma_rec.cash_ccid;
570 l_cash_amt := l_crh_rec_new.amount;
571 l_acctd_cash_amt := l_crh_rec_new.acctd_amount;
572 ELSE
573 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
574 'CRH', 'CASH', l_rma_rec,
575 l_dist_rec.code_combination_id);
576
577 l_cash_amt := l_crh_rec_new.amount - L_crh_rec_old.amount;
578 l_acctd_cash_amt := l_crh_rec_new.acctd_amount - L_crh_rec_old.acctd_amount;
579 END IF;
580
581 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
582 IF ( l_cash_amt < 0 )
583 THEN
584 l_dist_rec.amount_dr := NULL;
585 l_dist_rec.amount_cr := -l_cash_amt;
586 l_dist_rec.acctd_amount_dr := NULL;
587 l_dist_rec.acctd_amount_cr := -l_acctd_cash_amt;
588 ELSE
589 l_dist_rec.amount_dr := l_cash_amt;
590 l_dist_rec.amount_cr := NULL;
591 l_dist_rec.acctd_amount_dr := l_acctd_cash_amt;
592 l_dist_rec.acctd_amount_cr := NULL;
593 END IF;
594 /*
595 IF ( l_acctd_cash_amt < 0 )
596 THEN
597 l_dist_rec.acctd_amount_dr := NULL;
598 l_dist_rec.acctd_amount_cr := -l_acctd_cash_amt;
599 ELSE
600 l_dist_rec.acctd_amount_dr := l_acctd_cash_amt;
601 l_dist_rec.acctd_amount_cr := NULL;
602 END IF;
603 */
604 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
605
606 -- Bug 2580219: reinitialize variables for MRC use only.
607 l_dist_rec.source_id := l_crh_rec_old.cash_receipt_history_id;
608 l_dist_rec.source_table_secondary := 'MRC';
609 l_dist_rec.source_id_secondary := l_crh_rec_new.cash_receipt_history_id;
610
611 /* need to insert records into the MRC table. Calling new
612 mrc engine */
613 /*
614 ar_mrc_engine2.maintain_mrc_data2(
615 p_event_mode => 'INSERT',
616 p_table_name => 'AR_DISTRIBUTIONS',
617 p_mode => 'SINGLE',
618 p_key_value => l_dist_rec.line_id,
619 p_row_info => l_dist_rec);
620 */
621 -- Bug 2580219 reset the values after the call.. just incase they were used.
622 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
623 l_dist_rec.source_table_secondary := NULL;
624 l_dist_rec.source_id_secondary := NULL;
625
626 --END IF;
627
628 -- insert the remittance account ar_distributions record
629
630 /* Bug No. 3644849 JVARKEY */
631 -- For Remittance Row The exchange parameters must be as that of its history
632 IF ( l_crh_rec_old.status = 'REMITTED' )
633 THEN
634
635 l_dist_rec.currency_conversion_rate := l_crh_rec_old.exchange_rate;
636 l_dist_rec.currency_conversion_type := l_crh_rec_old.exchange_rate_type;
637 l_dist_rec.currency_conversion_date := l_crh_rec_old.exchange_date;
638
639 END IF;
640
641 IF ( l_crh_rec_old.status = 'REMITTED' ) AND
642 ( l_crh_rec_old.factor_flag <> 'Y' ) -- AND
643 /* skoukunt: comment to Fix bug 1198295
644 (( l_crh_rec_old.amount <>0 ) OR
645 ( l_crh_rec_old.acctd_amount <> 0 ))
646 */
647 THEN
648 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
649 l_dist_rec.source_table := 'CRH';
650 l_dist_rec.source_type := 'REMITTANCE';
651 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
652 'CRH', 'REMITTANCE', l_rma_rec,
653 l_dist_rec.code_combination_id);
654 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
655 IF ( l_cr_rec.amount < 0 )
656 THEN
657 l_dist_rec.amount_cr := NULL;
658 l_dist_rec.amount_dr := -l_cr_rec.amount;
659 l_dist_rec.acctd_amount_cr := NULL;
660 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt_old;
661 ELSE
662 l_dist_rec.amount_cr := l_cr_rec.amount;
663 l_dist_rec.amount_dr := NULL;
664 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt_old;
665 l_dist_rec.acctd_amount_dr := NULL;
666 END IF;
667 /*
668 IF ( l_acctd_receipt_amt_old < 0 )
669 THEN
670 l_dist_rec.acctd_amount_cr := NULL;
671 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt_old;
672 ELSE
673 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt_old;
674 l_dist_rec.acctd_amount_dr := NULL;
675 END IF;
676 */
677 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
678
679 /* need to insert records into the MRC table. Calling new
680 mrc engine */
681 /*
682 ar_mrc_engine2.maintain_mrc_data2(
683 p_event_mode => 'INSERT',
684 p_table_name => 'AR_DISTRIBUTIONS',
685 p_mode => 'SINGLE',
686 p_key_value => l_dist_rec.line_id,
687 p_row_info => l_dist_rec);
688 */
689 END IF;
690 /*4401288 New Exchange rate,type and date needs to be passed for new records*/
691 IF ( l_crh_rec_old.status = 'REMITTED' )
692 THEN
693
694 l_dist_rec.currency_conversion_rate := l_crh_rec_new.exchange_rate;
695 l_dist_rec.currency_conversion_type := l_crh_rec_new.exchange_rate_type;
696 l_dist_rec.currency_conversion_date := l_crh_rec_new.exchange_date;
697
698 END IF;
699
700 -- insert the short term debt account ar_distributions record
701 IF ( l_crh_rec_old.status = 'REMITTED' ) AND
702 ( l_crh_rec_old.factor_flag ='Y' ) -- AND
703 /* skoukunt: comment to Fix bug 1198295
704 (( l_crh_rec_old.amount <>0 ) OR
705 ( l_crh_rec_old.acctd_amount <> 0 ))
706 */
707 THEN
708 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
709 l_dist_rec.source_table := 'CRH';
710 l_dist_rec.source_type := 'SHORT_TERM_DEBT';
711 l_dist_rec.code_combination_id := l_rma_rec.short_term_debt_ccid;
712 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
713 IF ( l_cr_rec.amount < 0 )
714 THEN
715 l_dist_rec.amount_cr := NULL;
716 l_dist_rec.amount_dr := -l_cr_rec.amount;
717 l_dist_rec.acctd_amount_cr := NULL;
718 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt_new;
719 ELSE
720 l_dist_rec.amount_cr := l_cr_rec.amount;
721 l_dist_rec.amount_dr := NULL;
722 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt_new;
723 l_dist_rec.acctd_amount_dr := NULL;
724 END IF;
725 /*
726 IF ( l_acctd_receipt_amt_new < 0 )
727 THEN
728 l_dist_rec.acctd_amount_cr := NULL;
729 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt_new;
730 ELSE
731 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt_new;
732 l_dist_rec.acctd_amount_dr := NULL;
733 END IF;
734 */
735
736 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
737
738 /* need to insert records into the MRC table. Calling new
739 mrc engine */
740
741 ar_mrc_engine2.maintain_mrc_data2(
742 p_event_mode => 'INSERT',
743 p_table_name => 'AR_DISTRIBUTIONS',
744 p_mode => 'SINGLE',
745 p_key_value => l_dist_rec.line_id,
746 p_row_info => l_dist_rec);
747
748 END IF;
749
750 -- insert the factor account ar_distributions record if it's
751 -- factor='Y' and there's a rate adj involved
752 IF ( l_crh_rec_old.factor_flag = 'Y' ) AND
753 ( l_crh_rec_old.exchange_rate <> l_crh_rec_new.exchange_rate ) AND
754 ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) <> 0 )
755 THEN
756 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
757 l_dist_rec.source_table := 'CRH';
758 l_dist_rec.source_type := 'FACTOR';
759 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
760 'CRH', 'FACTOR', l_rma_rec,
761 l_dist_rec.code_combination_id);
762 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
763 IF ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) < 0 )
764 THEN
765 l_dist_rec.amount_dr := NULL;
766 l_dist_rec.amount_cr := 0;
767 l_dist_rec.acctd_amount_dr := NULL;
768 l_dist_rec.acctd_amount_cr := -(l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
769 ELSE
770 l_dist_rec.amount_dr := 0;
771 l_dist_rec.amount_cr := NULL;
772 l_dist_rec.acctd_amount_dr := (l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
773 l_dist_rec.acctd_amount_cr := NULL;
774 END IF;
775 /*
776 IF ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) < 0 )
777 THEN
778 l_dist_rec.acctd_amount_dr := NULL;
779 l_dist_rec.acctd_amount_cr := -(l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
780 ELSE
781 l_dist_rec.acctd_amount_dr := (l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
782 l_dist_rec.acctd_amount_cr := NULL;
783 END IF;
784 */
785
786 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
787
788 /* need to insert records into the MRC table. Calling new
789 mrc engine */
790
791 ar_mrc_engine2.maintain_mrc_data2(
792 p_event_mode => 'INSERT',
793 p_table_name => 'AR_DISTRIBUTIONS',
794 p_mode => 'SINGLE',
795 p_key_value => l_dist_rec.line_id,
796 p_row_info => l_dist_rec);
797
798 END IF;
799
800 -- insert the short term debt ar_distributions record if it's
801 -- factor='Y' and there's a rate adj involved and it's
802 -- prior history record is 'CLEARED'
803 IF ( l_crh_rec_old.factor_flag = 'Y' ) AND
804 ( l_crh_rec_old.status = 'CLEARED' ) AND
805 ( l_crh_rec_old.exchange_rate <> l_crh_rec_new.exchange_rate ) AND
806 ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) <> 0 )
807 THEN
808 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
809 l_dist_rec.source_table := 'CRH';
810 l_dist_rec.source_type := 'SHORT_TERM_DEBT';
811 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
812 'CRH', 'SHORT_TERM_DEBT', l_rma_rec,
813 l_dist_rec.code_combination_id);
814 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
815 IF ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) < 0 )
816 THEN
817 l_dist_rec.amount_cr := NULL;
818 l_dist_rec.amount_dr := 0;
819 l_dist_rec.acctd_amount_cr := NULL;
820 l_dist_rec.acctd_amount_dr := -(l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
821 ELSE
822 l_dist_rec.amount_cr := 0;
823 l_dist_rec.amount_dr := NULL;
824 l_dist_rec.acctd_amount_cr := (l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
825 l_dist_rec.acctd_amount_dr := NULL;
826 END IF;
827 /*
828 IF ( (l_acctd_receipt_amt_new - l_acctd_receipt_amt_old) < 0 )
829 THEN
830 l_dist_rec.acctd_amount_cr := NULL;
831 l_dist_rec.acctd_amount_dr := -(l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
832 ELSE
833 l_dist_rec.acctd_amount_cr := (l_acctd_receipt_amt_new - L_acctd_receipt_amt_old);
834 l_dist_rec.acctd_amount_dr := NULL;
835 END IF;
836 */
837
838 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
839
840 /* need to insert records into the MRC table. Calling new
841 mrc engine */
842
843 ar_mrc_engine2.maintain_mrc_data2(
844 p_event_mode => 'INSERT',
845 p_table_name => 'AR_DISTRIBUTIONS',
846 p_mode => 'SINGLE',
847 p_key_value => l_dist_rec.line_id,
848 p_row_info => l_dist_rec);
849
850 END IF;
851
852 -- insert the bank charge account ar_distributions record
853 IF ( l_crh_rec_new.factor_discount_amount <>0 ) OR
854 ( l_crh_rec_new.acctd_factor_discount_amount <> 0 )
855 THEN
856 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
857 l_dist_rec.source_table := 'CRH';
858 l_dist_rec.source_type := 'BANK_CHARGES';
859
860 IF ( l_crh_rec_old.status = 'REMITTED' )
861 THEN
862 l_dist_rec.code_combination_id := l_crh_rec_new.bank_charge_account_ccid;
863 l_bank_amt := nvl(l_crh_rec_new.factor_discount_amount,0);
864 l_acctd_bank_amt := nvl(l_crh_rec_new.acctd_factor_discount_amount,0);
865 ELSE
866 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
867 'CRH', 'BANK_CHARGES', l_rma_rec,
868 l_dist_rec.code_combination_id);
869 l_bank_amt := nvl(l_crh_rec_new.factor_discount_amount,0) - nvl(L_crh_rec_old.factor_discount_amount,0);
870 l_acctd_bank_amt := nvl(l_crh_rec_new.acctd_factor_discount_amount,0) - nvl(L_crh_rec_old.acctd_factor_discount_amount,0);
871 END IF;
872
873 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
874 IF ( l_bank_amt < 0 )
875 THEN
876 l_dist_rec.amount_dr := NULL;
877 l_dist_rec.amount_cr := -l_bank_amt;
878 l_dist_rec.acctd_amount_dr := NULL;
879 l_dist_rec.acctd_amount_cr := -l_acctd_bank_amt;
880 ELSE
881 l_dist_rec.amount_dr := l_bank_amt;
882 l_dist_rec.amount_cr := NULL;
883 l_dist_rec.acctd_amount_dr := l_acctd_bank_amt;
884 l_dist_rec.acctd_amount_cr := NULL;
885 END IF;
886 /*
887 IF ( l_acctd_bank_amt < 0 )
888 THEN
889 l_dist_rec.acctd_amount_dr := NULL;
890 l_dist_rec.acctd_amount_cr := -l_acctd_bank_amt;
891 ELSE
892 l_dist_rec.acctd_amount_dr := l_acctd_bank_amt;
893 l_dist_rec.acctd_amount_cr := NULL;
894 END IF;
895 */
896 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
897
898 /* need to insert records into the MRC table. Calling new
899 mrc engine */
900
901 ar_mrc_engine2.maintain_mrc_data2(
902 p_event_mode => 'INSERT',
903 p_table_name => 'AR_DISTRIBUTIONS',
904 p_mode => 'SINGLE',
905 p_key_value => l_dist_rec.line_id,
906 p_row_info => l_dist_rec);
907
908 END IF;
909
910 -- If exchange rate has been changed
911 -- Insert a record into ar_rate_adjustments
912 -- Call arplbrad.main() to take care the ar_cash_receipts, ar_payment_schedules
913 -- and ar_receivable_applications/ar_misc_cash_distributions
914 --
915 -- 17-MAY-1999 J Rautiainen truncation exits on exchange rate so the comparison
916 -- was changed from comparing exchange rates to comparing accounted amounts in order
917 -- to fix bug 874052.
918 -- Commented out NOCOPY for bug fix 874052 IF ( l_crh_rec_old.exchange_rate <> l_crh_rec_new.exchange_rate )
919 IF ( (l_crh_rec_old.acctd_amount + NVL(l_crh_rec_old.acctd_factor_discount_amount,0) )
920 <> (l_crh_rec_new.acctd_amount + NVL(l_crh_rec_new.acctd_factor_discount_amount,0)))
921 THEN
922 l_radj_rec.cash_receipt_id := p_cr_id;
923 l_radj_rec.gain_loss := arp_util.functional_amount(
924 l_cr_rec.amount,
925 ARP_GLOBAL.functional_currency,
926 (l_crh_rec_new.exchange_rate - l_crh_rec_old.exchange_rate),
927 NULL,NULL );
928 l_radj_rec.gl_date := p_gl_date;
929 l_radj_rec.new_exchange_date := l_crh_rec_new.exchange_date;
930 l_radj_rec.new_exchange_rate := l_crh_rec_new.exchange_rate;
931 l_radj_rec.new_exchange_rate_type:= l_crh_rec_new.exchange_rate_type ;
932 l_radj_rec.old_exchange_date := l_crh_rec_old.exchange_date;
933 l_radj_rec.old_exchange_rate := l_crh_rec_old.exchange_rate;
934 l_radj_rec.old_exchange_rate_type:= l_crh_rec_old.exchange_rate_type;
935 l_radj_rec.gl_posted_date := NULL;
936 l_radj_rec.posting_control_id := -3;
937 l_radj_rec.created_from := substrb(p_module_name||'ARP_CASHBOOK.CLEAR',1,30);
938 arp_rate_adjustments_pkg.insert_p( l_radj_rec,l_radj_rec.rate_adjustment_id );
939
940 arp_rate_adj.main(
941 p_cr_id,
942 l_radj_rec.new_exchange_date,
943 l_radj_rec.new_exchange_rate,
944 l_radj_rec.new_exchange_rate_type,
945 l_radj_rec.gl_date,
946 ARP_GLOBAL.created_by,
947 ARP_GLOBAL.creation_date,
948 ARP_GLOBAL.last_updated_by,
949 ARP_GLOBAL.last_update_date,
950 ARP_GLOBAL.last_update_login,
951 FALSE,
952 l_crh_rec_new.cash_receipt_history_id
953 );
954
955 END IF;
956
957
958 -- Insert value date into CR record
959 --Yao Zhang modified for bug 10124745
960 UPDATE AR_CASH_RECEIPTS
961 SET actual_value_date = p_actual_value_date,
962 rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
963 last_updated_by = arp_global.last_updated_by,
964 last_update_date = SYSDATE,
965 last_update_login = arp_global.last_update_login
966
967 WHERE cash_receipt_id = p_cr_id;
968
969 -- Populate OUT NOCOPY parameters
970 p_crh_id := l_crh_rec_new.cash_receipt_history_id;
971
972 /* 9363502 - set receipt_at_risk_value in ar_trx_val_summary
973 for regular and misc receipts */
974 IF l_cr_rec.pay_from_customer IS NOT NULL
975 THEN
976
977 /* Check for REFRESH running first */
978 IF g_refresh_running IS NULL
979 THEN
980 BEGIN
981 select 'Y'
982 into g_refresh_running
983 from ar_conc_process_requests
984 where concurrent_program_name = 'ARSUMREF';
985 EXCEPTION
986 WHEN OTHERS THEN
987 g_refresh_running := 'N';
988 END;
989 END IF;
990
991 IF g_refresh_running = 'N'
992 THEN
993
994 /* 9363502 - Set receipt_at_risk_value in ar_trx_bal_summary */
995 l_customer_id_tab(0) := l_cr_rec.pay_from_customer;
996 l_site_use_id_tab(0) := NVL(l_cr_rec.customer_site_use_id,-99);
997 l_currency_tab(0) := l_cr_rec.currency_code;
998 l_org_id_tab(0) := l_org_id;
999
1000 /*bug 13858491 Starts*/
1001 -- Commented for bug 16229574
1002 /*If(p_module_name = 'CE_AUTO_BANK_REC') then
1003 ar_bus_event_sub_pvt.refresh_at_risk_value(l_customer_id_tab,
1004 l_site_use_id_tab,
1005 l_currency_tab,
1006 l_org_id_tab,
1007 l_cr_rec.cash_receipt_id);
1008 else*/
1009 /*ar_bus_event_sub_pvt.refresh_at_risk_value(l_customer_id_tab,
1010 l_site_use_id_tab,
1011 l_currency_tab,
1012 l_org_id_tab);
1013 */
1014
1015 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
1016 store the records in the plsql table and invoke it at at time*/
1017 declare
1018 l_temp_comb_str varchar2(1000);
1019 begin
1020 l_temp_comb_str := l_cr_rec.pay_from_customer||'*'||NVL(l_cr_rec.customer_site_use_id,-99)||'*'||l_cr_rec.currency_code||'*'||l_org_id||'#';
1021 if(not(g_unq_cust_site_cur_org_cmb.exists(l_temp_comb_str))) then
1022 --store the unq combination
1023 g_unq_cust_site_cur_org_cmb(l_temp_comb_str) := l_temp_comb_str;
1024
1025 --store the values in respective tables
1026 g_customer_id_tab(g_customer_id_tab.count+1) := l_cr_rec.pay_from_customer;
1027 g_site_use_id_tab(g_site_use_id_tab.count+1) := NVL(l_cr_rec.customer_site_use_id,-99);
1028 g_currency_tab(g_currency_tab.count+1) := l_cr_rec.currency_code;
1029 g_org_id_tab(g_org_id_tab.count+1) := l_org_id;
1030 end if;
1031 end;
1032
1033 -- end if;
1034 /*bug 13858491 ends*/
1035 END IF;
1036 END IF;
1037
1038 IF PG_DEBUG in ('Y', 'C') THEN
1039 arp_util.debug(' g_refresh_running = ' || g_refresh_running);
1040 arp_util.debug( '<<<<<<< arp_cashbook.clear' );
1041 END IF;
1042
1043 EXCEPTION
1044 WHEN settlement_pending_raise then
1045 APP_EXCEPTION.RAISE_EXCEPTION;
1046 WHEN OTHERS THEN
1047 IF PG_DEBUG in ('Y', 'C') THEN
1048 arp_util.debug( 'EXCEPTION: ARP_CASHBOOK.clear' );
1049 END IF;
1050 RAISE;
1051
1052 END clear;
1053
1054 PROCEDURE unclear(
1055 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1056 p_trx_date IN ar_cash_receipt_history.trx_date%TYPE,
1057 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
1058 p_actual_value_date IN ar_cash_receipts.actual_value_date%TYPE,
1059 p_module_name IN VARCHAR2,
1060 p_module_version IN VARCHAR2,
1061 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
1062 --
1063 l_cr_rec ar_cash_receipts%ROWTYPE;
1064 l_crh_rec_prv_stat ar_cash_receipt_history%ROWTYPE;
1065 l_crh_rec_fr_radj ar_cash_receipt_history%ROWTYPE;
1066 l_crh_rec_prv_stat_cash ar_cash_receipt_history%ROWTYPE;
1067 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
1068 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
1069 l_rma_rec ar_receipt_method_accounts%ROWTYPE;
1070 l_radj_rec ar_rate_adjustments%ROWTYPE;
1071 l_dist_rec ar_distributions%ROWTYPE;
1072 l_receipt_amt ar_cash_receipt_history.amount%TYPE;
1073 l_acctd_receipt_amt ar_cash_receipt_history.acctd_amount%TYPE;
1074 l_new_crh_id_fr_radj ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1075
1076 l_xla_ev_rec arp_xla_events.xla_events_type;
1077
1078 l_rate_Adj_reverted varchar2(1);
1079 l_use_remit_row_amt varchar2(1);
1080
1081 BEGIN
1082
1083 IF PG_DEBUG in ('Y', 'C') THEN
1084 arp_util.debug( '>>>>>>> arp_cashbook.unclear' );
1085 END IF;
1086
1087
1088 --Setting the Org Context Bug5212892
1089 ar_mo_global_cache.populate;
1090 arp_global.init_global(mo_global.get_current_org_id);
1091 arp_standard.init_standard(mo_global.get_current_org_id);
1092
1093
1094 -- Assume this receipt has already been locked
1095
1096 -- Validate the GL Date is in open or future period
1097
1098 -- Fetch the history record
1099 arp_cr_history_pkg.fetch_f_crid( p_cr_id, l_crh_rec_old );
1100
1101 -- Check if this receipt has already been reversed or unclear, then
1102 -- fail and give an error message
1103 IF ( l_crh_rec_old.status = 'REVERSED')
1104 THEN
1105 fnd_message.set_name('AR', 'AR_CANNOT_UNCLEAR_REV_RECEIPT' );
1106 app_exception.raise_exception;
1107 ELSE
1108 IF ( l_crh_rec_old.status <> 'CLEARED' )
1109 THEN
1110 fnd_message.set_name('AR', 'AR_RECEIPT_CANNOT_UNCLEAR' );
1111 app_exception.raise_exception;
1112 END IF;
1113 END IF;
1114 -- make sure the receipt was not created as CLEARED
1115 -- (in that case there is no previous state, and we return to caller
1116 -- without error (see Bug 305482)):
1117
1118 IF ( l_crh_rec_old.prv_stat_cash_receipt_hist_id IS NOT NULL )
1119 THEN
1120
1121 -- Fetch the history record of the prv status
1122 arp_cr_history_pkg.fetch_p( l_crh_rec_old.prv_stat_cash_receipt_hist_id, l_crh_rec_prv_stat );
1123
1124 /*Bug 9761480 Fetch the CLEARED row that is reversal of the previous status REMITTED row */
1125 arp_cr_history_pkg.fetch_p( l_crh_rec_prv_stat.reversal_cash_receipt_hist_id, l_crh_rec_prv_stat_cash );
1126
1127 -- Fetch the cash receipt record
1128 l_cr_rec.cash_receipt_id := p_cr_id;
1129 arp_cash_receipts_pkg.fetch_p( l_cr_rec );
1130
1131 -- 11.5 VAT changes:
1132 l_dist_rec.currency_code := l_cr_rec.currency_code;
1133 l_dist_rec.currency_conversion_rate := l_crh_rec_old.exchange_rate;
1134 l_dist_rec.currency_conversion_type := l_crh_rec_old.exchange_rate_type;
1135 l_dist_rec.currency_conversion_date := l_crh_rec_old.exchange_date;
1136 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
1137 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
1138
1139 -- Fetch the receipt method bank account record
1140 arp_rm_accounts_pkg.fetch_p( l_cr_rec.receipt_method_id,
1141 l_cr_rec.remit_bank_acct_use_id,
1142 l_rma_rec );
1143
1144
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 arp_standard.debug('unclear: ' || 'crh_id_old: ' || to_char(l_crh_rec_old.cash_receipt_history_id));
1147 arp_standard.debug('unclear: ' || 'crh_id_prv_stat: ' || to_char(l_crh_rec_prv_stat.cash_receipt_history_id));
1148 END IF;
1149
1150 /* Bug 9761480 : First revert all the rate adjustments done on Clear receipt.
1151 Then create the REMITTED row. */
1152 -- This is a fix to conform to the old 10.5 design,when
1153 -- you unclear a row, it should retain the same exchange rate as
1154 -- the original remitted row.
1155 -- So, if the rate of the cleared row is different from the original
1156 -- remitted row, then we do a rate adjustment to adjust the exchange rate
1157 -- back to it's old rate comes from the remitted row.
1158 l_new_crh_id_fr_radj := NULL;
1159
1160 -- 17-MAY-1999 J Rautiainen truncation exits on exchange rate so the comparison
1161 -- was changed from comparing exchange rates to comparing accounted amounts in order
1162 -- to fix bug 874052.
1163 -- Commented out NOCOPY for bugfix 874052 IF ( nvl(l_crh_rec_prv_stat.exchange_rate,1) <> nvl(l_crh_rec_new.exchange_rate,1))
1164 l_rate_Adj_reverted := 'N';
1165 IF ( (nvl(l_crh_rec_prv_stat.acctd_amount,1) + NVL(l_crh_rec_prv_stat.acctd_factor_discount_amount,0) )
1166 <> (nvl(l_crh_rec_old.acctd_amount,1) + NVL(l_crh_rec_old.acctd_factor_discount_amount,0)))
1167 THEN
1168 l_radj_rec.cash_receipt_id := p_cr_id;
1169 l_radj_rec.gain_loss := arp_util.functional_amount(
1170 l_cr_rec.amount,
1171 ARP_GLOBAL.functional_currency,
1172 (l_crh_rec_prv_stat.exchange_rate - l_crh_rec_old.exchange_rate),
1173 NULL,NULL );
1174 l_radj_rec.gl_date := p_gl_date;
1175 l_radj_rec.new_exchange_date := l_crh_rec_prv_stat.exchange_date;
1176 l_radj_rec.new_exchange_rate := l_crh_rec_prv_stat.exchange_rate;
1177 l_radj_rec.new_exchange_rate_type := l_crh_rec_prv_stat.exchange_rate_type ;
1178 l_radj_rec.old_exchange_date := l_crh_rec_old.exchange_date;
1179 l_radj_rec.old_exchange_rate := l_crh_rec_old.exchange_rate;
1180 l_radj_rec.old_exchange_rate_type:= l_crh_rec_old.exchange_rate_type;
1181 l_radj_rec.gl_posted_date := NULL;
1182 l_radj_rec.posting_control_id := -3;
1183 l_radj_rec.created_from := substrb(p_module_name||'ARP_CASHBOOK.UNCLEAR',1,30);
1184 arp_rate_adjustments_pkg.insert_p( l_radj_rec,l_radj_rec.rate_adjustment_id );
1185
1186 arp_rate_adj.main(
1187 p_cr_id,
1188 l_radj_rec.new_exchange_date,
1189 l_radj_rec.new_exchange_rate,
1190 l_radj_rec.new_exchange_rate_type,
1191 l_radj_rec.gl_date,
1192 ARP_GLOBAL.created_by,
1193 ARP_GLOBAL.creation_date,
1194 ARP_GLOBAL.last_updated_by,
1195 ARP_GLOBAL.last_update_date,
1196 ARP_GLOBAL.last_update_login,
1197 TRUE, -- should this be FALSE??? OS 7/6/99
1198 l_new_crh_id_fr_radj
1199 );
1200 l_rate_Adj_reverted := 'Y';
1201 END IF;
1202
1203 -- Insert a new history record
1204 l_receipt_amt := l_crh_rec_old.amount + nvl(L_crh_rec_old.factor_discount_amount,0);
1205 l_acctd_receipt_amt := l_crh_rec_prv_stat.acctd_amount + nvl(l_crh_rec_prv_stat.acctd_factor_discount_amount,0);
1206 l_crh_rec_new.amount := l_crh_rec_prv_stat.amount;
1207 l_crh_rec_new.factor_discount_amount := l_crh_rec_prv_stat.factor_discount_amount;
1208 -- Changes for triangulation: If exchange rate type is not
1209 -- user, call GL API to calculate accounted amount
1210
1211 -- Bug 925765: gl api was called with 'User' when it shouldn't be.
1212 -- Problem was that IF statement compared l_crh_rec_old.exchange_rate_type
1213 -- with 'User', but then we're really going to use the earlier value from
1214 -- l_crh_rec_prv_stat.exchange_rate_type.
1215
1216 IF (l_crh_rec_prv_stat.exchange_rate_type = 'User') THEN
1217 l_crh_rec_new.acctd_amount := arp_util.functional_amount(
1218 l_crh_rec_prv_stat.amount,
1219 ARP_GLOBAL.functional_currency,
1220 nvl(l_crh_rec_prv_stat.exchange_rate,1),
1221 NULL,NULL );
1222 ELSE
1223 l_crh_rec_new.acctd_amount := gl_currency_api.convert_amount(
1224 l_cr_rec.currency_code,
1225 ARP_GLOBAL.functional_currency,
1226 l_crh_rec_prv_stat.exchange_date,
1227 l_crh_rec_prv_stat.exchange_rate_type,
1228 l_crh_rec_prv_stat.amount);
1229 END IF;
1230
1231 l_crh_rec_new.acctd_factor_discount_amount := l_acctd_receipt_amt - L_crh_rec_new.acctd_amount;
1232
1233 -- Bug 925765: we were using the exchange rate, date, and type from
1234 -- the l_crh_rec_old record, but we should have used the values from
1235 -- the l_crh_rec_prv_stat record, since we're going back to that status.
1236
1237 l_crh_rec_new.exchange_date := l_crh_rec_prv_stat.exchange_date;
1238 l_crh_rec_new.exchange_rate := l_crh_rec_prv_stat.exchange_rate;
1239 l_crh_rec_new.exchange_rate_type := l_crh_rec_prv_stat.exchange_rate_type;
1240 l_crh_rec_new.cash_receipt_id := p_cr_id;
1241 l_crh_rec_new.status := l_crh_rec_prv_stat.status;
1242 l_crh_rec_new.trx_date := p_trx_date;
1243 l_crh_rec_new.first_posted_record_flag := 'N';
1244 l_crh_rec_new.postable_flag := 'Y';
1245 l_crh_rec_new.factor_flag := l_crh_rec_old.factor_flag;
1246 l_crh_rec_new.gl_date := p_gl_date;
1247 l_crh_rec_new.current_record_flag := 'Y';
1248 l_crh_rec_new.batch_id := l_crh_rec_prv_stat.batch_id;
1249 IF ( l_crh_rec_old.factor_flag = 'Y' )
1250 THEN
1251 arp_cr_util.get_dist_ccid( l_crh_rec_prv_stat.cash_receipt_id,
1252 'CRH', 'FACTOR', l_rma_rec,
1253 l_crh_rec_new.account_code_combination_id);
1254 ELSE
1255 arp_cr_util.get_dist_ccid( l_crh_rec_prv_stat.cash_receipt_id,
1256 'CRH', 'REMITTANCE', l_rma_rec,
1257 l_crh_rec_new.account_code_combination_id);
1258 END IF;
1259 l_crh_rec_new.reversal_gl_date := NULL;
1260 l_crh_rec_new.reversal_cash_receipt_hist_id := NULL;
1261 arp_cr_util.get_dist_ccid( l_crh_rec_prv_stat.cash_receipt_id,
1262 'CRH', 'BANK_CHARGES', l_rma_rec,
1263 l_crh_rec_new.bank_charge_account_ccid);
1264 l_crh_rec_new.posting_control_id := -3;
1265 l_crh_rec_new.reversal_posting_control_id := NULL;
1266 l_crh_rec_new.gl_posted_date := NULL;
1267 l_crh_rec_new.reversal_gl_posted_date := NULL;
1268 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_prv_stat.prv_stat_cash_receipt_hist_id;
1269 l_crh_rec_new.created_from := substrb(p_module_name||'ARP_CASHBOOK.UNCLEAR',1,30);
1270 l_crh_rec_new.reversal_created_from := NULL;
1271 arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
1272
1273 /* Bug 9761480 : If rate adjustment is reversed, then update those CRH records also*/
1274 IF l_new_crh_id_fr_radj IS NOT NULL THEN
1275 -- Update the old history record
1276 l_crh_rec_old.current_record_flag := NULL;
1277 l_crh_rec_old.reversal_gl_date := p_gl_date;
1278 l_crh_rec_old.reversal_cash_receipt_hist_id := l_new_crh_id_fr_radj;
1279 l_crh_rec_old.reversal_posting_control_id := -3;
1280 l_crh_rec_old.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.UNCLEAR',1,30);
1281 arp_cr_history_pkg.update_p( l_crh_rec_old );
1282
1283 /* Fetch newly created Rate Adjustment records */
1284 arp_cr_history_pkg.fetch_p( l_new_crh_id_fr_radj, l_crh_rec_fr_radj );
1285
1286 l_crh_rec_fr_radj.current_record_flag := NULL;
1287 l_crh_rec_fr_radj.reversal_gl_date := p_gl_date;
1288 l_crh_rec_fr_radj.reversal_cash_receipt_hist_id := l_crh_rec_new.cash_receipt_history_id;
1289 l_crh_rec_fr_radj.reversal_posting_control_id := -3;
1290 l_crh_rec_fr_radj.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.UNCLEAR',1,30);
1291 arp_cr_history_pkg.update_p( l_crh_rec_fr_radj );
1292
1293 ELSE
1294 l_crh_rec_old.current_record_flag := NULL;
1295 l_crh_rec_old.reversal_gl_date := p_gl_date;
1296 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_rec_new.cash_receipt_history_id;
1297 l_crh_rec_old.reversal_posting_control_id := -3;
1298 l_crh_rec_old.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.UNCLEAR',1,30);
1299 arp_cr_history_pkg.update_p( l_crh_rec_old );
1300
1301 END IF;
1302
1303 --BUG#5569338
1304 l_xla_ev_rec.xla_from_doc_id := p_cr_id;
1305 l_xla_ev_rec.xla_to_doc_id := p_cr_id;
1306 l_xla_ev_rec.xla_doc_table := 'CRH';
1307 l_xla_ev_rec.xla_mode := 'O';
1308 l_xla_ev_rec.xla_call := 'B';
1309 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1310
1311
1312
1313 -- Insert the remittance/short_term_debt account ar_distributions record
1314 /* skoukunt: comment to Fix bug 1198295
1315 IF ( l_receipt_amt <>0 ) OR
1316 ( l_acctd_receipt_amt <> 0 )
1317 THEN
1318 */
1319 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1320 l_dist_rec.source_table := 'CRH';
1321 IF ( l_crh_rec_old.factor_flag = 'Y' )
1322 THEN
1323 l_dist_rec.source_type := 'SHORT_TERM_DEBT';
1324 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1325 'CRH', 'SHORT_TERM_DEBT', l_rma_rec,
1326 l_dist_rec.code_combination_id);
1327 ELSE
1328 l_dist_rec.source_type := 'REMITTANCE';
1329 arp_cr_util.get_dist_ccid( l_crh_rec_prv_stat.cash_receipt_id,
1330 'CRH', 'REMITTANCE', l_rma_rec,
1331 l_dist_rec.code_combination_id);
1332 END IF;
1333
1334 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1335 IF ( l_receipt_amt < 0 )
1336 THEN
1337 l_dist_rec.amount_dr := NULL;
1338 l_dist_rec.amount_cr := -l_receipt_amt;
1339 l_dist_rec.acctd_amount_dr := NULL;
1340 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1341 ELSE
1342 l_dist_rec.amount_dr := l_receipt_amt;
1343 l_dist_rec.amount_cr := NULL;
1344 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1345 l_dist_rec.acctd_amount_cr := NULL;
1346 END IF;
1347 /*
1348 IF ( l_acctd_receipt_amt < 0 )
1349 THEN
1350 l_dist_rec.acctd_amount_dr := NULL;
1351 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1352 ELSE
1353 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1354 l_dist_rec.acctd_amount_cr := NULL;
1355 END IF;
1356 */
1357 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1358
1359 -- END IF;
1360
1361 -- Insert the cash account ar_distributions record
1362 /* skoukunt: comment to Fix bug 1198295
1363 IF ( l_crh_rec_old.amount <>0 ) OR
1364 ( l_crh_rec_old.acctd_amount <> 0 )
1365 THEN
1366 */
1367
1368 /* Bug 9761480: Since we have reverted the Rate Adjustment if any exists on CASH record.
1369 So while reversing receipt back to REMITTED status, we will consider CLEARED row
1370 which is a pair of original remitted row to get the amounts bucket.
1371 Data is already fetched in variable l_crh_rec_prv_stat_cash */
1372
1373 /* Bug 13943452: There is a chance rate is aadjusted at time of clearing receipt based on
1374 clearance date of receipt. This rate adjustment is implicit without any rate adjustment trigger
1375 event. In those case also rate adjustment is reversed as per bug fix 9761480
1376 Hence amount to be utilize in this case should be from previous state 'REMITTED' row
1377 */
1378
1379
1380 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1381 l_dist_rec.source_table := 'CRH';
1382 l_dist_rec.source_type := 'CASH';
1383 arp_cr_util.get_dist_ccid( l_crh_rec_prv_stat_cash.cash_receipt_id,
1384 'CRH', 'CASH', l_rma_rec,
1385 l_dist_rec.code_combination_id);
1386
1387 IF l_crh_rec_old.created_from <> 'RATE ADJUSTMENT TRIGGER' and l_crh_rec_old.status = 'CLEAR'
1388 and l_rate_Adj_reverted = 'Y' and p_module_name = 'CEXCABMR' THEN
1389 l_use_remit_row_amt := 'Y';
1390 ELSE
1391 l_use_remit_row_amt := 'N';
1392 END IF;
1393 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1394
1395 IF nvl(l_use_remit_row_amt,'N') = 'N' THEN
1396 IF ( l_crh_rec_prv_stat_cash.amount < 0 )
1397 THEN
1398 l_dist_rec.amount_cr := NULL;
1399 l_dist_rec.amount_dr := -l_crh_rec_prv_stat_cash.amount;
1400 l_dist_rec.acctd_amount_cr := NULL;
1401 l_dist_rec.acctd_amount_dr := -l_crh_rec_prv_stat_cash.acctd_amount;
1402 ELSE
1403 l_dist_rec.amount_cr := l_crh_rec_prv_stat_cash.amount;
1404 l_dist_rec.amount_dr := NULL;
1405 l_dist_rec.acctd_amount_cr := l_crh_rec_prv_stat_cash.acctd_amount;
1406 l_dist_rec.acctd_amount_dr := NULL;
1407 END IF;
1408
1409 ELSE
1410
1411 IF ( l_crh_rec_prv_stat_cash.amount < 0 )
1412 THEN
1413 l_dist_rec.amount_cr := NULL;
1414 l_dist_rec.amount_dr := -l_crh_rec_prv_stat.amount;
1415 l_dist_rec.acctd_amount_cr := NULL;
1416 l_dist_rec.acctd_amount_dr := -l_crh_rec_prv_stat.acctd_amount;
1417 ELSE
1418 l_dist_rec.amount_cr := l_crh_rec_prv_stat.amount;
1419 l_dist_rec.amount_dr := NULL;
1420 l_dist_rec.acctd_amount_cr := l_crh_rec_prv_stat.acctd_amount;
1421 l_dist_rec.acctd_amount_dr := NULL;
1422 END IF;
1423
1424 END IF;
1425 /*
1426 IF ( l_crh_rec_old.acctd_amount < 0 )
1427 THEN
1428 l_dist_rec.acctd_amount_cr := NULL;
1429 l_dist_rec.acctd_amount_dr := -l_crh_rec_old.acctd_amount;
1430 ELSE
1431 l_dist_rec.acctd_amount_cr := l_crh_rec_old.acctd_amount;
1432 l_dist_rec.acctd_amount_dr := NULL;
1433 END IF;
1434 */
1435 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1436
1437
1438 -- END IF;
1439
1440 -- Insert the bank charge account ar_distributions record
1441 IF ( NVL(l_crh_rec_old.factor_discount_amount,0) <>0 ) OR
1442 ( NVL(l_crh_rec_old.acctd_factor_discount_amount,0) <> 0 )
1443 THEN
1444 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1445 l_dist_rec.source_table := 'CRH';
1446 l_dist_rec.source_type := 'BANK_CHARGES';
1447 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1448 'CRH', 'BANK_CHARGES', l_rma_rec,
1449 l_dist_rec.code_combination_id);
1450
1451 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1452 IF ( l_crh_rec_old.factor_discount_amount < 0 )
1453 THEN
1454 l_dist_rec.amount_cr := NULL;
1455 l_dist_rec.amount_dr := -l_crh_rec_old.factor_discount_amount;
1456 l_dist_rec.acctd_amount_cr := NULL;
1457 l_dist_rec.acctd_amount_dr := -l_crh_rec_old.acctd_factor_discount_amount;
1458 ELSE
1459 l_dist_rec.amount_cr := l_crh_rec_old.factor_discount_amount;
1460 l_dist_rec.amount_dr := NULL;
1461 l_dist_rec.acctd_amount_cr := l_crh_rec_old.acctd_factor_discount_amount;
1462 l_dist_rec.acctd_amount_dr := NULL;
1463 END IF;
1464 /*
1465 IF ( l_crh_rec_old.acctd_factor_discount_amount < 0 )
1466 THEN
1467 l_dist_rec.acctd_amount_cr := NULL;
1468 l_dist_rec.acctd_amount_dr := -l_crh_rec_old.acctd_factor_discount_amount;
1469 ELSE
1470 l_dist_rec.acctd_amount_cr := l_crh_rec_old.acctd_factor_discount_amount;
1471 l_dist_rec.acctd_amount_dr := NULL;
1472 END IF;
1473 */
1474 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1475
1476
1477 END IF;
1478
1479 -- Insert value date into CR record
1480 --Yao zhang modified for bug 10124745
1481 UPDATE AR_CASH_RECEIPTS
1482 SET actual_value_date = p_actual_value_date,
1483 rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
1484 last_updated_by = arp_global.last_updated_by,
1485 last_update_date = SYSDATE,
1486 last_update_login = arp_global.last_update_login
1487 WHERE cash_receipt_id = p_cr_id;
1488
1489 -- Populate OUT NOCOPY parameters
1490 p_crh_id := nvl(l_new_crh_id_fr_radj,l_crh_rec_new.cash_receipt_history_id);
1491 ELSE
1492 -- if unclear() cannot be performed because of the receipt being
1493 -- created as CLEARED, we return NULL as crh_id.
1494 p_crh_id := NULL;
1495 END IF;
1496
1497 IF PG_DEBUG in ('Y', 'C') THEN
1498 arp_util.debug( '<<<<<<<< arp_cashbook.unclear' );
1499 END IF;
1500
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 IF PG_DEBUG in ('Y', 'C') THEN
1504 arp_util.debug( 'EXCEPTION: ARP_CASHBOOK.unclear' );
1505 END IF;
1506 RAISE;
1507
1508 END unclear;
1509
1510 PROCEDURE risk_eliminate(
1511 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1512 p_trx_date IN ar_cash_receipt_history.trx_date%TYPE,
1513 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
1514 p_module_name IN VARCHAR2,
1515 p_module_version IN VARCHAR2,
1516 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
1517 --
1518 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
1519 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
1520 l_dist_rec ar_distributions%ROWTYPE;
1521 l_receipt_amt ar_cash_receipt_history.amount%TYPE;
1522 l_acctd_receipt_amt ar_cash_receipt_history.acctd_amount%TYPE;
1523 NULL_VAR ar_receipt_method_accounts%ROWTYPE;
1524 l_cr_rec ar_cash_receipts%ROWTYPE;
1525 l_risk_event_rec arp_xla_events.xla_events_type;
1526
1527 /* 9363502 - define tables used by refresh_at_risk_value */
1528 l_customer_id_tab ar_bus_event_sub_pvt.generic_id_type;
1529 l_site_use_id_tab ar_bus_event_sub_pvt.generic_id_type;
1530 l_org_id_tab ar_bus_event_sub_pvt.generic_id_type;
1531 l_currency_tab ar_bus_event_sub_pvt.currency_type;
1532 /* end 9363502 */
1533
1534 BEGIN
1535
1536 IF PG_DEBUG in ('Y', 'C') THEN
1537 arp_util.debug( '>>>>>>> arp_cashbook.risk_eliminate' );
1538 END IF;
1539
1540 -- Assume this receipt has already been locked
1541
1542 -- Validate the GL Date is in open or future period
1543
1544 -- Fetch cash receipt record for 11.5 VAT changes:
1545 l_cr_rec.cash_receipt_id := p_cr_id;
1546 arp_cash_receipts_pkg.fetch_p(l_cr_rec);
1547
1548 -- Fetch the history record
1549 arp_cr_history_pkg.fetch_f_crid( p_cr_id, l_crh_rec_old );
1550
1551 -- 11.5 VAT changes:
1552 l_dist_rec.currency_code := l_cr_rec.currency_code;
1553 l_dist_rec.currency_conversion_rate := l_crh_rec_old.exchange_rate;
1554 l_dist_rec.currency_conversion_type := l_crh_rec_old.exchange_rate_type;
1555 l_dist_rec.currency_conversion_date := l_crh_rec_old.exchange_date;
1556 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
1557 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
1558
1559 -- Check if this receipt has already been risk eliminated,
1560 -- Also, if it's not factoring, cannot risk eliminate either.
1561 -- then fail and give an error message.
1562 IF ( l_crh_rec_old.status = 'RISK_ELIMINATED' ) OR
1563 ( l_crh_rec_old.factor_flag <> 'Y' )
1564 THEN
1565 fnd_message.set_name('AR', 'AR_CANNOT_ELIMINATE_RISK' );
1566 app_exception.raise_exception;
1567 END IF;
1568
1569 -- Insert a new history record
1570 l_receipt_amt := l_crh_rec_old.amount + nvl(L_crh_rec_old.factor_discount_amount,0);
1571 l_acctd_receipt_amt := l_crh_rec_old.acctd_amount + nvl(L_crh_rec_old.acctd_factor_discount_amount,0);
1572 l_crh_rec_new.amount := l_crh_rec_old.amount;
1573 l_crh_rec_new.factor_discount_amount := l_crh_rec_old.factor_discount_amount;
1574 l_crh_rec_new.acctd_amount := l_crh_rec_old.acctd_amount;
1575 l_crh_rec_new.acctd_factor_discount_amount := l_crh_rec_old.acctd_factor_discount_amount;
1576
1577 l_crh_rec_new.exchange_date := l_crh_rec_old.exchange_date;
1578 l_crh_rec_new.exchange_rate := l_crh_rec_old.exchange_rate;
1579 l_crh_rec_new.exchange_rate_type := l_crh_rec_old.exchange_rate_type;
1580 l_crh_rec_new.cash_receipt_id := p_cr_id;
1581 l_crh_rec_new.status := 'RISK_ELIMINATED';
1582 l_crh_rec_new.trx_date := p_trx_date;
1583 l_crh_rec_new.first_posted_record_flag := 'N';
1584 l_crh_rec_new.postable_flag := 'Y';
1585 l_crh_rec_new.factor_flag := l_crh_rec_old.factor_flag;
1586 l_crh_rec_new.gl_date := p_gl_date;
1587 l_crh_rec_new.current_record_flag := 'Y';
1588 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1589 'CRH','SHORT_TERM_DEBT', NULL_VAR,
1590 l_crh_rec_new.account_code_combination_id);
1591 l_crh_rec_new.reversal_gl_date := NULL;
1592 l_crh_rec_new.reversal_cash_receipt_hist_id := NULL;
1593 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1594 'CRH', 'BANK_CHARGES', NULL_VAR,
1595 l_crh_rec_new.bank_charge_account_ccid);
1596 l_crh_rec_new.posting_control_id := -3;
1597 l_crh_rec_new.reversal_posting_control_id := NULL;
1598 l_crh_rec_new.gl_posted_date := NULL;
1599 l_crh_rec_new.reversal_gl_posted_date := NULL;
1600 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.cash_receipt_history_id;
1601 l_crh_rec_new.created_from := substrb(p_module_name||'ARP_CASHBOOK.RISK_ELIMINATE',1,30);
1602 l_crh_rec_new.reversal_created_from := NULL;
1603 arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
1604
1605 -- Update the old history record
1606 l_crh_rec_old.current_record_flag := NULL;
1607 l_crh_rec_old.reversal_gl_date := p_gl_date;
1608 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_rec_new.cash_receipt_history_id;
1609 l_crh_rec_old.reversal_posting_control_id := -3;
1610 l_crh_rec_old.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.RISK_ELIMINATE',1,30);
1611 arp_cr_history_pkg.update_p( l_crh_rec_old );
1612
1613 /* Bug 6494186 */
1614 l_risk_event_rec.xla_from_doc_id := p_cr_id;
1615 l_risk_event_rec.xla_to_doc_id := p_cr_id;
1616 l_risk_event_rec.xla_doc_table := 'CRH';
1617 l_risk_event_rec.xla_mode := 'O';
1618 l_risk_event_rec.xla_call := 'B';
1619 arp_xla_events.Create_Events(p_xla_ev_rec => l_risk_event_rec );
1620
1621 -- Insert the short_term_debt account ar_distributions record
1622 /* skoukunt: comment to Fix bug 1198295
1623 IF ( l_receipt_amt <>0 ) OR
1624 ( l_acctd_receipt_amt <> 0 )
1625 THEN
1626 */
1627 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1628 l_dist_rec.source_table := 'CRH';
1629 l_dist_rec.source_type := 'SHORT_TERM_DEBT';
1630 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1631 'CRH', 'SHORT_TERM_DEBT', NULL_VAR,
1632 l_dist_rec.code_combination_id);
1633 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1634 IF ( l_receipt_amt < 0 )
1635 THEN
1636 l_dist_rec.amount_dr := NULL;
1637 l_dist_rec.amount_cr := -l_receipt_amt;
1638 l_dist_rec.acctd_amount_dr := NULL;
1639 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1640 ELSE
1641 l_dist_rec.amount_dr := l_receipt_amt;
1642 l_dist_rec.amount_cr := NULL;
1643 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1644 l_dist_rec.acctd_amount_cr := NULL;
1645 END IF;
1646 /*
1647 IF ( l_acctd_receipt_amt < 0 )
1648 THEN
1649 l_dist_rec.acctd_amount_dr := NULL;
1650 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1651 ELSE
1652 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1653 l_dist_rec.acctd_amount_cr := NULL;
1654 END IF;
1655 */
1656 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1657
1658 /* need to insert records into the MRC table. Calling new
1659 mrc engine */
1660
1661 ar_mrc_engine2.maintain_mrc_data2(
1662 p_event_mode => 'INSERT',
1663 p_table_name => 'AR_DISTRIBUTIONS',
1664 p_mode => 'SINGLE',
1665 p_key_value => l_dist_rec.line_id,
1666 p_row_info => l_dist_rec);
1667
1668 -- END IF;
1669
1670 -- Insert the factor account ar_distributions record
1671 /* skoukunt: comment to Fix bug 1198295
1672 IF ( l_crh_rec_old.amount <>0 ) OR
1673 ( l_crh_rec_old.acctd_amount <> 0 )
1674 THEN
1675 */
1676 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1677 l_dist_rec.source_table := 'CRH';
1678 l_dist_rec.source_type := 'FACTOR';
1679 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1680 'CRH', 'FACTOR', NULL_VAR,
1681 l_dist_rec.code_combination_id);
1682 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1683 IF ( l_receipt_amt < 0 )
1684 THEN
1685 l_dist_rec.amount_cr := NULL;
1686 l_dist_rec.amount_dr := -l_receipt_amt;
1687 l_dist_rec.acctd_amount_cr := NULL;
1688 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt;
1689 ELSE
1690 l_dist_rec.amount_cr := l_receipt_amt;
1691 l_dist_rec.amount_dr := NULL;
1692 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt;
1693 l_dist_rec.acctd_amount_dr := NULL;
1694 END IF;
1695 /*
1696 IF ( l_acctd_receipt_amt < 0 )
1697 THEN
1698 l_dist_rec.acctd_amount_cr := NULL;
1699 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt;
1700 ELSE
1701 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt;
1702 l_dist_rec.acctd_amount_dr := NULL;
1703 END IF;
1704 */
1705 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1706
1707 /* need to insert records into the MRC table. Calling new
1708 mrc engine */
1709
1710 ar_mrc_engine2.maintain_mrc_data2(
1711 p_event_mode => 'INSERT',
1712 p_table_name => 'AR_DISTRIBUTIONS',
1713 p_mode => 'SINGLE',
1714 p_key_value => l_dist_rec.line_id,
1715 p_row_info => l_dist_rec);
1716
1717 -- END IF;
1718
1719 /* 9363502 - set receipt_at_risk_value in ar_trx_val_summary
1720 for regular and misc receipts */
1721 IF l_cr_rec.pay_from_customer IS NOT NULL
1722 THEN
1723
1724 /* Check for REFRESH running first */
1725 IF g_refresh_running IS NULL
1726 THEN
1727 BEGIN
1728 select 'Y'
1729 into g_refresh_running
1730 from ar_conc_process_requests
1731 where concurrent_program_name = 'ARSUMREF';
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 g_refresh_running := 'N';
1735 END;
1736 END IF;
1737
1738 IF g_refresh_running = 'N'
1739 THEN
1740
1741 /* 9363502 - Set receipt_at_risk_value in ar_trx_bal_summary */
1742 /*l_customer_id_tab(0) := l_cr_rec.pay_from_customer;
1743 l_site_use_id_tab(0) := NVL(l_cr_rec.customer_site_use_id,-99);
1744 l_currency_tab(0) := l_cr_rec.currency_code;
1745 l_org_id_tab(0) := l_cr_rec.org_id;
1746
1747 ar_bus_event_sub_pvt.refresh_at_risk_value(l_customer_id_tab,
1748 l_site_use_id_tab,
1749 l_currency_tab,
1750 l_org_id_tab);
1751 */
1752
1753 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
1754 store the records in the plsql table and invoke it at at time*/
1755 declare
1756 l_temp_comb_str varchar2(1000);
1757 begin
1758 l_temp_comb_str := l_cr_rec.pay_from_customer||'*'||NVL(l_cr_rec.customer_site_use_id,-99)||'*'||l_cr_rec.currency_code||'*'||l_cr_rec.org_id||'#';
1759 if(not(g_unq_cust_site_cur_org_cmb.exists(l_temp_comb_str))) then
1760 --store the unq combination
1761 g_unq_cust_site_cur_org_cmb(l_temp_comb_str) := l_temp_comb_str;
1762 --store the values in respective tables
1763 g_customer_id_tab(g_customer_id_tab.count+1) := l_cr_rec.pay_from_customer;
1764 g_site_use_id_tab(g_site_use_id_tab.count+1) := NVL(l_cr_rec.customer_site_use_id,-99);
1765 g_currency_tab(g_currency_tab.count+1) := l_cr_rec.currency_code;
1766 g_org_id_tab(g_org_id_tab.count+1) := l_cr_rec.org_id;
1767 end if;
1768 end;
1769
1770 END IF;
1771 END IF;
1772
1773 -- Populate OUT NOCOPY parameters
1774 p_crh_id := l_crh_rec_new.cash_receipt_history_id;
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_util.debug( '<<<<<<<< arp_cashbook.risk_eliminate' );
1777 END IF;
1778
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 IF PG_DEBUG in ('Y', 'C') THEN
1782 arp_util.debug( 'EXCEPTION: ARP_CASHBOOK.risk_eliminate' );
1783 END IF;
1784 RAISE;
1785
1786 END risk_eliminate;
1787
1788 PROCEDURE undo_risk_eliminate(
1789 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1790 p_trx_date IN ar_cash_receipt_history.trx_date%TYPE,
1791 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
1792 p_module_name IN VARCHAR2,
1793 p_module_version IN VARCHAR2,
1794 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
1795 --
1796 l_crh_rec_old ar_cash_receipt_history%ROWTYPE;
1797 l_crh_rec_new ar_cash_receipt_history%ROWTYPE;
1798 l_dist_rec ar_distributions%ROWTYPE;
1799 l_receipt_amt ar_cash_receipt_history.amount%TYPE;
1800 l_acctd_receipt_amt ar_cash_receipt_history.acctd_amount%TYPE;
1801 NULL_VAR ar_receipt_method_accounts%ROWTYPE;
1802 l_cr_rec ar_cash_receipts%ROWTYPE;
1803 BEGIN
1804
1805 IF PG_DEBUG in ('Y', 'C') THEN
1806 arp_util.debug( '>>>>>>> arp_cashbook.undo_risk_eliminate' );
1807 END IF;
1808
1809 -- Assume this receipt has already been locked
1810
1811 -- Validate the GL Date is in open or future period
1812
1813 -- Fetch cash receipt record for 11.5 VAT changes:
1814 l_cr_rec.cash_receipt_id := p_cr_id;
1815 arp_cash_receipts_pkg.fetch_p(l_cr_rec);
1816
1817 -- Fetch the history record
1818 arp_cr_history_pkg.fetch_f_crid( p_cr_id, l_crh_rec_old );
1819
1820 -- 11.5 VAT changes:
1821 l_dist_rec.currency_code := l_cr_rec.currency_code;
1822 l_dist_rec.currency_conversion_rate := l_crh_rec_old.exchange_rate;
1823 l_dist_rec.currency_conversion_type := l_crh_rec_old.exchange_rate_type;
1824 l_dist_rec.currency_conversion_date := l_crh_rec_old.exchange_date;
1825 l_dist_rec.third_party_id := l_cr_rec.pay_from_customer;
1826 l_dist_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
1827
1828
1829 -- Check if this receipt has already been risk eliminated,
1830 -- Also, if it's not factoring, cannot risk eliminate either.
1831 -- then fail and give an error message.
1832 IF ( l_crh_rec_old.status <> 'RISK_ELIMINATED' ) OR
1833 ( l_crh_rec_old.factor_flag <> 'Y' )
1834 THEN
1835 fnd_message.set_name('AR', 'AR_CANNOT_UNDO_RISK_ELIMINATE' );
1836 app_exception.raise_exception;
1837 END IF;
1838
1839 -- Insert a new history record
1840 l_receipt_amt := l_crh_rec_old.amount + nvl(L_crh_rec_old.factor_discount_amount,0);
1841 l_acctd_receipt_amt := l_crh_rec_old.acctd_amount + nvl(L_crh_rec_old.acctd_factor_discount_amount,0);
1842 l_crh_rec_new.amount := l_crh_rec_old.amount;
1843 l_crh_rec_new.factor_discount_amount := l_crh_rec_old.factor_discount_amount;
1844 l_crh_rec_new.acctd_amount := l_crh_rec_old.acctd_amount;
1845 l_crh_rec_new.acctd_factor_discount_amount := l_crh_rec_old.acctd_factor_discount_amount;
1846
1847 l_crh_rec_new.exchange_date := l_crh_rec_old.exchange_date;
1848 l_crh_rec_new.exchange_rate := l_crh_rec_old.exchange_rate;
1849 l_crh_rec_new.exchange_rate_type := l_crh_rec_old.exchange_rate_type;
1850 l_crh_rec_new.cash_receipt_id := p_cr_id;
1851 l_crh_rec_new.status := 'CLEARED';
1852 l_crh_rec_new.trx_date := p_trx_date;
1853 l_crh_rec_new.first_posted_record_flag := 'N';
1854 l_crh_rec_new.postable_flag := 'Y';
1855 l_crh_rec_new.factor_flag := l_crh_rec_old.factor_flag;
1856 l_crh_rec_new.gl_date := p_gl_date;
1857 l_crh_rec_new.current_record_flag := 'Y';
1858 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1859 'CRH','FACTOR', NULL_VAR,
1860 l_crh_rec_new.account_code_combination_id);
1861 l_crh_rec_new.reversal_gl_date := NULL;
1862 l_crh_rec_new.reversal_cash_receipt_hist_id := NULL;
1863 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1864 'CRH', 'BANK_CHARGES', NULL_VAR,
1865 l_crh_rec_new.bank_charge_account_ccid);
1866 l_crh_rec_new.posting_control_id := -3;
1867 l_crh_rec_new.reversal_posting_control_id := NULL;
1868 l_crh_rec_new.gl_posted_date := NULL;
1869 l_crh_rec_new.reversal_gl_posted_date := NULL;
1870 l_crh_rec_new.prv_stat_cash_receipt_hist_id := l_crh_rec_old.cash_receipt_history_id;
1871 l_crh_rec_new.created_from := substrb(p_module_name||'ARP_CASHBOOK.UNDO_RISK_ELIMINATE',1,30);
1872 l_crh_rec_new.reversal_created_from := NULL;
1873 arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
1874
1875 -- Update the old history record
1876 l_crh_rec_old.current_record_flag := NULL;
1877 l_crh_rec_old.reversal_gl_date := p_gl_date;
1878 l_crh_rec_old.reversal_cash_receipt_hist_id := l_crh_rec_new.cash_receipt_history_id;
1879 l_crh_rec_old.reversal_posting_control_id := -3;
1880 l_crh_rec_old.reversal_created_from := substrb(p_module_name||'ARP_CASHBOOK.UNDO_RISK_ELIMINATE',1,30);
1881 arp_cr_history_pkg.update_p( l_crh_rec_old );
1882
1883 -- Insert the short_term_debt account ar_distributions record
1884 /* skoukunt: comment to Fix bug 1198295
1885 IF ( l_receipt_amt <>0 ) OR
1886 ( l_acctd_receipt_amt <> 0 )
1887 THEN
1888 */
1889 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1890 l_dist_rec.source_table := 'CRH';
1891 l_dist_rec.source_type := 'SHORT_TERM_DEBT';
1892 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1893 'CRH', 'SHORT_TERM_DEBT', NULL_VAR,
1894 l_dist_rec.code_combination_id);
1895 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1896 IF ( l_receipt_amt < 0 )
1897 THEN
1898 l_dist_rec.amount_cr := NULL;
1899 l_dist_rec.amount_dr := -l_receipt_amt;
1900 l_dist_rec.acctd_amount_cr := NULL;
1901 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt;
1902 ELSE
1903 l_dist_rec.amount_cr := l_receipt_amt;
1904 l_dist_rec.amount_dr := NULL;
1905 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt;
1906 l_dist_rec.acctd_amount_dr := NULL;
1907 END IF;
1908 /*
1909 IF ( l_acctd_receipt_amt < 0 )
1910 THEN
1911 l_dist_rec.acctd_amount_cr := NULL;
1912 l_dist_rec.acctd_amount_dr := -l_acctd_receipt_amt;
1913 ELSE
1914 l_dist_rec.acctd_amount_cr := l_acctd_receipt_amt;
1915 l_dist_rec.acctd_amount_dr := NULL;
1916 END IF;
1917 */
1918 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1919
1920 /* need to insert records into the MRC table. Calling new
1921 mrc engine */
1922
1923 ar_mrc_engine2.maintain_mrc_data2(
1924 p_event_mode => 'INSERT',
1925 p_table_name => 'AR_DISTRIBUTIONS',
1926 p_mode => 'SINGLE',
1927 p_key_value => l_dist_rec.line_id,
1928 p_row_info => l_dist_rec);
1929
1930
1931 -- END IF;
1932
1933 -- Insert the factor account ar_distributions record
1934 /* skoukunt: comment to Fix bug 1198295
1935 IF ( l_crh_rec_old.amount <>0 ) OR
1936 ( l_crh_rec_old.acctd_amount <> 0 )
1937 THEN
1938 */
1939 l_dist_rec.source_id := l_crh_rec_new.cash_receipt_history_id;
1940 l_dist_rec.source_table := 'CRH';
1941 l_dist_rec.source_type := 'FACTOR';
1942 arp_cr_util.get_dist_ccid( l_crh_rec_old.cash_receipt_id,
1943 'CRH', 'FACTOR', NULL_VAR,
1944 l_dist_rec.code_combination_id);
1945 -- Fix 1119979, assign acctd_amount_dr and acctd_amount_cr in above condn
1946 IF ( l_receipt_amt < 0 )
1947 THEN
1948 l_dist_rec.amount_dr := NULL;
1949 l_dist_rec.amount_cr := -l_receipt_amt;
1950 l_dist_rec.acctd_amount_dr := NULL;
1951 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1952 ELSE
1953 l_dist_rec.amount_dr := l_receipt_amt;
1954 l_dist_rec.amount_cr := NULL;
1955 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1956 l_dist_rec.acctd_amount_cr := NULL;
1957 END IF;
1958 /*
1959 IF ( l_acctd_receipt_amt < 0 )
1960 THEN
1961 l_dist_rec.acctd_amount_dr := NULL;
1962 l_dist_rec.acctd_amount_cr := -l_acctd_receipt_amt;
1963 ELSE
1964 l_dist_rec.acctd_amount_dr := l_acctd_receipt_amt;
1965 l_dist_rec.acctd_amount_cr := NULL;
1966 END IF;
1967 */
1968 arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
1969
1970 /* need to insert records into the MRC table. Calling new
1971 mrc engine */
1972
1973 ar_mrc_engine2.maintain_mrc_data2(
1974 p_event_mode => 'INSERT',
1975 p_table_name => 'AR_DISTRIBUTIONS',
1976 p_mode => 'SINGLE',
1977 p_key_value => l_dist_rec.line_id,
1978 p_row_info => l_dist_rec);
1979
1980 -- END IF;
1981
1982 -- Populate OUT NOCOPY parameters
1983 p_crh_id := l_crh_rec_new.cash_receipt_history_id;
1984 IF PG_DEBUG in ('Y', 'C') THEN
1985 arp_util.debug( '<<<<<<<< arp_cashbook.undo_risk_eliminate' );
1986 END IF;
1987
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 IF PG_DEBUG in ('Y', 'C') THEN
1991 arp_util.debug( 'EXCEPTION: ARP_CASHBOOK.undo_risk_eliminate' );
1992 END IF;
1993 RAISE;
1994
1995 END undo_risk_eliminate;
1996
1997 /*===========================================================================+
1998 | PROCEDURE |
1999 | ins_misc_txn |
2000 | |
2001 | DESCRIPTION |
2002 | Creates a miscellaneous receipt. |
2003 | |
2004 | SCOPE - PRIVATE |
2005 | |
2006 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2007 | |
2008 | ARGUMENTS |
2009 | IN: |
2010 | OUT: |
2011 | |
2012 | RETURNS |
2013 | |
2014 | NOTES |
2015 | |
2016 | MODIFICATION HISTORY |
2017 | 30-SEP-98 K.Murphy Cash Management Enhancement: Allow creation |
2018 | of Misc Receipts with distribution set. |
2019 | Removed code that sets up the misc receipt |
2020 | distribution record and calls the entity |
2021 | handler. Now calls the distribution |
2022 | procedure passing the activity id. This |
2023 | procedure creates the required distribution |
2024 | rows based on the activity. |
2025 | 04-JAN-99 D. Jancis Modified for 11.5 VAT project. Added calls |
2026 | to get currency_code, |
2027 | currency_conversion_rate, |
2028 | currency_conversion_type, and |
2029 | currency_conversion_date |
2030 | 01-MAR-99 D. Jancis Modified routine to call GUI handler to do |
2031 | all inserts. Also added parameter tax_rate |
2032 | required for VAT |
2033 | 04-JUN-99 GJWANG Derive distribution_set_id from in parameter |
2034 | receivables_trx_id when create misc receipt |
2035 | 30-SEP-02 R Kader Bug fix 2300268 : Added a new variable and |
2036 | used this variable while calling the proc |
2037 | insert_misc_receipt()
2038 | 21-FEB-03 R Kader Bug fix 2742388 : Added a new variable and |
2039 | used this variable while calling the proc |
2040 | insert_misc_receipt()
2041 | 01-Feb-13 Jixun Bug fix 14726865: AUTO RECONCILIATION |
2042 | COMPLETES IN ERROR WITH SQLERRM:ORA-28115: |
2043 | policy with check option violation
2044 +===========================================================================*/
2045
2046 PROCEDURE ins_misc_txn(
2047 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE,
2048 p_document_number IN ar_cash_receipts.doc_sequence_value%TYPE,
2049 p_doc_sequence_id IN ar_cash_receipts.doc_sequence_id%TYPE,
2050 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
2051 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
2052 p_deposit_date IN ar_cash_receipts.deposit_date%TYPE,
2053 p_receipt_amount IN ar_cash_receipts.amount%TYPE,
2054 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
2055 p_exchange_date IN ar_cash_receipt_history.exchange_date%TYPE,
2056 p_exchange_rate_type IN ar_cash_receipt_history.exchange_rate_type%TYPE,
2057 p_exchange_rate IN ar_cash_receipt_history.exchange_rate%TYPE,
2058 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
2059 p_remit_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%TYPE,
2060 p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
2061 p_comments IN ar_cash_receipts.comments%TYPE,
2062 p_vat_tax_id IN ar_cash_receipts.vat_tax_id%TYPE,
2063 p_reference_type IN ar_cash_receipts.reference_type%TYPE,
2064 p_reference_id IN ar_cash_receipts.reference_id%TYPE,
2065 p_misc_payment_source IN ar_cash_receipts.misc_payment_source%TYPE,
2066 p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE,
2067 p_module_name IN VARCHAR2,
2068 p_module_version IN VARCHAR2,
2069 p_cr_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
2070 p_tax_rate IN NUMBER ) IS
2071 --
2072 p_row_id VARCHAR2(30);
2073 l_cr_id ar_cash_Receipts.cash_receipt_id%TYPE;
2074 l_dis_set_id ar_cash_receipts.distribution_set_id%TYPE;
2075 /* Bug fix 2300268 */
2076 l_tax_account_id ar_distributions.code_combination_id%TYPE;
2077 /* Bug fix 2742388 */
2078 l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2079 l_org_id NUMBER; --added by Jixun for bug#14726865
2080 BEGIN
2081
2082 IF PG_DEBUG in ('Y', 'C') THEN
2083 arp_util.debug( '>>>>>>> arp_cashbook.ins_misc_txn' );
2084 END IF;
2085
2086 --modified by Jixun for bug#14726865 begin
2087 /*SELECT default_acctg_distribution_set
2088 INTO l_dis_set_id
2089 FROM ar_receivables_trx
2090 WHERE receivables_trx_id = p_receivables_trx_id;*/
2091
2092 BEGIN
2093 SELECT default_acctg_distribution_set, org_id
2094 INTO l_dis_set_id, l_org_id
2095 FROM ar_receivables_trx
2096 WHERE receivables_trx_id = p_receivables_trx_id;
2097
2098 mo_global.init('AR');
2099 mo_global.set_policy_context('S', l_org_id);
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 arp_standard.debug('Unable to drive the org id for p_cr_id:' ||
2103 p_cr_id);
2104 END;
2105
2106 ar_mo_global_cache.populate;
2107 arp_global.init_global(mo_global.get_current_org_id);
2108 arp_standard.init_standard(mo_global.get_current_org_id);
2109 --modified by Jixun for bug#14726865 end
2110
2111 /* Bug fix 2300268
2112 Get the tax account id corresponding to the vat_tax_id */
2113 IF p_vat_tax_id IS NOT NULL THEN
2114 /* bug 6034914 , commented out the select and added next 6 lines.
2115 SELECT tax_account_id
2116 INTO l_tax_account_id
2117 FROM ar_vat_tax
2118 WHERE vat_tax_id = p_vat_tax_id;
2119 */
2120 l_tax_account_id := arp_etax_util.get_tax_account(p_vat_tax_id,
2121 trunc(p_receipt_date),'TAX','TAX_RATE');
2122 if l_tax_account_id = -1
2123 then
2124 l_tax_account_id := NULL;
2125 end if;
2126 ELSE
2127 l_tax_account_id := NULL;
2128 END IF;
2129 /* End Bug fix 2300268 */
2130
2131 IF PG_DEBUG in ('Y', 'C') THEN
2132 arp_util.debug('ins_misc_txn: ' || ' ====> Receipt_number ' || p_receipt_number);
2133 arp_util.debug('ins_misc_txn: ' || ' ====> distribution_set_id ' || l_dis_set_id);
2134 END IF;
2135
2136 -- Bugs 975560/962254: Added NULL for P_USSGL_TRANSACTION_CODE
2137 -- parameter.
2138
2139 ARP_PROCESS_MISC_RECEIPTS.insert_misc_receipt (
2140 p_currency_code,
2141 p_receipt_amount,
2142 p_receivables_trx_id,
2143 p_misc_payment_source,
2144 p_receipt_number,
2145 p_receipt_date,
2146 p_gl_date,
2147 p_comments,
2148 p_exchange_rate_type,
2149 p_exchange_rate,
2150 p_exchange_date,
2151 NULL,
2152 NULL,
2153 NULL,
2154 NULL,
2155 NULL,
2156 NULL,
2157 NULL,
2158 NULL,
2159 NULL,
2160 NULL,
2161 NULL,
2162 NULL,
2163 NULL,
2164 NULL,
2165 NULL,
2166 NULL,
2167 NULL,
2168 p_remit_bank_account_id,
2169 p_deposit_date,
2170 p_receipt_method_id,
2171 p_document_number,
2172 p_doc_sequence_id,
2173 l_dis_set_id,
2174 p_reference_type,
2175 p_reference_id,
2176 p_vat_tax_id,
2177 NULL, -- Bug 975560/962254
2178 p_anticipated_clearing_date,
2179 NULL,
2180 NULL,
2181 NULL,
2182 NULL,
2183 NULL,
2184 NULL,
2185 NULL,
2186 NULL,
2187 NULL,
2188 NULL,
2189 NULL,
2190 NULL,
2191 NULL,
2192 NULL,
2193 NULL,
2194 NULL,
2195 NULL,
2196 NULL,
2197 NULL,
2198 NULL,
2199 NULL,
2200 l_cr_id,
2201 p_row_id,
2202 p_module_name,
2203 p_module_version,
2204 p_tax_rate,
2205 l_tax_account_id, /* Bug fix 2300268 */
2206 l_crh_id); /* Bug fix 2742388 */
2207
2208 -- Populate OUT NOCOPY parameters
2209 p_cr_id := l_cr_id;
2210 IF PG_DEBUG in ('Y', 'C') THEN
2211 arp_util.debug( '<<<<<<<< arp_cashbook.ins_misc_txn' );
2212 END IF;
2213
2214 EXCEPTION
2215 WHEN OTHERS THEN
2216 IF PG_DEBUG in ('Y', 'C') THEN
2217 arp_util.debug( 'EXCEPTION: arp_cashbook.ins_misc_txn' );
2218 END IF;
2219 RAISE;
2220
2221 END ins_misc_txn;
2222
2223 PROCEDURE reverse(
2224 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2225 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
2226 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
2227 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
2228 p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
2229 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
2230 p_module_name IN VARCHAR2,
2231 p_module_version IN VARCHAR2,
2232 p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE) IS
2233
2234 CURSOR current_crh_cur IS
2235 SELECT crh.cash_receipt_history_id
2236 FROM ar_cash_receipt_history crh
2237 WHERE crh.cash_receipt_id = p_cr_id
2238 AND crh.current_record_flag = 'Y'
2239 AND crh.status = 'REVERSED';
2240
2241 l_reversal_category AR_CASH_RECEIPTS.REVERSAL_CATEGORY%TYPE;
2242 l_reversal_reason_code AR_CASH_RECEIPTS.REVERSAL_REASON_CODE%TYPE;
2243 l_attribute_rec AR_RECEIPT_API_PUB.ATTRIBUTE_REC_TYPE; /* Added for bug 2688370 */
2244 l_return_status VARCHAR2(1);
2245 l_msg_count NUMBER;
2246 l_msg_data VARCHAR2(2000);
2247 l_cr_id NUMBER;
2248 current_crh_rec current_crh_cur%ROWTYPE;
2249 API_exception EXCEPTION;
2250 l_msg_index number;
2251
2252 BEGIN
2253
2254 IF PG_DEBUG in ('Y', 'C') THEN
2255 arp_util.debug( '>>>>>>> arp_cashbook.reverse' );
2256 END IF;
2257
2258 -- if reversal category and reversal reason code are not
2259 -- passed in by CE, use 'NSF' as a default.
2260
2261 IF (p_reversal_category IS NULL) THEN
2262 l_reversal_category := 'NSF';
2263 ELSE
2264 l_reversal_category := p_reversal_category;
2265 END IF;
2266
2267 IF (p_reversal_reason_code IS NULL) THEN
2268 l_reversal_reason_code := 'NSF';
2269 ELSE
2270 l_reversal_reason_code := p_reversal_reason_code;
2271 END IF;
2272
2273 /* Bugfix 2688370. Code modified so that the DFF values are passed
2274 to the call of AR_RECEIPT_API_PUB.Reverse */
2275 SELECT attribute_category,
2276 attribute1, attribute2,
2277 attribute3, attribute4,
2278 attribute5, attribute6,
2279 attribute7, attribute8,
2280 attribute9, attribute10,
2281 attribute11, attribute12,
2282 attribute13, attribute14,
2283 attribute15
2284 INTO l_attribute_rec.attribute_category,
2285 l_attribute_rec.attribute1, l_attribute_rec.attribute2,
2286 l_attribute_rec.attribute3, l_attribute_rec.attribute4,
2287 l_attribute_rec.attribute5, l_attribute_rec.attribute6,
2288 l_attribute_rec.attribute7, l_attribute_rec.attribute8,
2289 l_attribute_rec.attribute9, l_attribute_rec.attribute10,
2290 l_attribute_rec.attribute11, l_attribute_rec.attribute12,
2291 l_attribute_rec.attribute13, l_attribute_rec.attribute14,
2292 l_attribute_rec.attribute15
2293 FROM ar_cash_receipts
2294 WHERE cash_receipt_id = p_cr_id;
2295
2296 BEGIN
2297
2298 AR_RECEIPT_API_PUB.Reverse(p_api_version => 1.0,
2299 p_init_msg_list => FND_API.G_TRUE,
2300 x_return_status => l_return_status,
2301 x_msg_count => l_msg_count,
2302 x_msg_data => l_msg_data,
2303 p_cash_receipt_id => p_cr_id,
2304 p_reversal_category_code => l_reversal_category,
2305 p_reversal_gl_date => p_reversal_gl_date,
2306 p_reversal_date => p_reversal_date,
2307 p_reversal_reason_code => l_reversal_reason_code,
2308 p_reversal_comments => p_reversal_comments,
2309 p_attribute_rec => l_attribute_rec,
2310 p_called_from => 'ARRECBKB');
2311
2312 /*------------------------------------------------+
2313 | Write API output to the concurrent program log |
2314 +------------------------------------------------*/
2315 IF PG_DEBUG in ('Y', 'C') THEN
2316 arp_util.debug('reverse: ' || 'API error count '||to_char(NVL(l_msg_count,0)));
2317 END IF;
2318
2319 IF NVL(l_msg_count,0) > 0 Then
2320
2321 IF l_msg_count = 1 Then
2322 /*------------------------------------------------+
2323 | There is one message returned by the API, so it|
2324 | has been sent out NOCOPY in the parameter x_msg_data |
2325 +------------------------------------------------*/
2326 IF PG_DEBUG in ('Y', 'C') THEN
2327 arp_util.debug('reverse: ' || l_msg_data);
2328 END IF;
2329
2330 ELSIF l_msg_count > 1 Then
2331
2332 /*-------------------------------------------------------+
2333 | There are more than one messages returned by the API, |
2334 | so call them in a loop and print the messages |
2335 +-------------------------------------------------------*/
2336
2337 FOR l_count IN 1..l_msg_count LOOP
2338
2339 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
2340 IF PG_DEBUG in ('Y', 'C') THEN
2341 arp_util.debug('reverse: ' || to_char(l_count)||' : '||l_msg_data);
2342 END IF;
2343
2344 END LOOP;
2345
2346 END IF;
2347
2348 END IF;
2349
2350 /*-----------------------------------------------------+
2351 | If API return status is not SUCCESS raise exception |
2352 +-----------------------------------------------------*/
2353 IF l_return_status = FND_API.G_RET_STS_SUCCESS Then
2354
2355 /*-----------------------------------------------------+
2356 | Success do nothing, else branch introduced to make |
2357 | sure that NULL case will also raise exception |
2358 +-----------------------------------------------------*/
2359 NULL;
2360
2361 ELSE
2362 /*---------------------------+
2363 | Error, raise an exception |
2364 +---------------------------*/
2365 RAISE API_exception;
2366
2367 END IF;
2368
2369 /*----------------------------------+
2370 | APIs propagate exception upwards |
2371 +----------------------------------*/
2372 EXCEPTION
2373 WHEN API_exception THEN
2374 IF PG_DEBUG in ('Y', 'C') THEN
2375 arp_util.debug('API Exception: arp_cashbook.reverse '||SQLERRM);
2376 END IF;
2377 FND_MSG_PUB.Get (FND_MSG_PUB.G_FIRST, FND_API.G_TRUE, l_msg_data, l_msg_index);
2378 FND_MESSAGE.Set_Encoded (l_msg_data);
2379 app_exception.raise_exception;
2380
2381 WHEN OTHERS THEN
2382 IF PG_DEBUG in ('Y', 'C') THEN
2383 arp_util.debug('Exception: arp_cashbook.reverse '||SQLERRM);
2384 END IF;
2385 fnd_message.set_name('AR', 'AR_BR_CANNOT_REVERSE_REC');
2386 app_exception.raise_exception;
2387
2388 END;
2389
2390 -- Populate OUT NOCOPY parameters
2391 OPEN current_crh_cur;
2392 FETCH current_crh_cur INTO current_crh_rec;
2393
2394 IF current_crh_cur%NOTFOUND THEN
2395 app_exception.raise_exception;
2396 END IF;
2397
2398 CLOSE current_crh_cur;
2399
2400 p_crh_id := current_crh_rec.cash_receipt_history_id;
2401
2402 IF PG_DEBUG in ('Y', 'C') THEN
2403 arp_util.debug( '<<<<<<<< arp_cashbook.reverse' );
2404 END IF;
2405
2406 EXCEPTION
2407 WHEN OTHERS THEN
2408 IF PG_DEBUG in ('Y', 'C') THEN
2409 arp_util.debug( 'EXCEPTION: ARP_CASHBOOK.reverse' );
2410 END IF;
2411 RAISE;
2412
2413 END reverse;
2414
2415 PROCEDURE debit_memo_reversal (
2416 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2417 p_cc_id IN ra_cust_trx_line_gl_dist.code_combination_id%TYPE,
2418 p_dm_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
2419 p_dm_cust_trx_type IN ra_cust_trx_types.name%TYPE,
2420 p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
2421 p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
2422 p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
2423 p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
2424 p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
2425 p_dm_number OUT NOCOPY ar_payment_schedules.trx_number%TYPE,
2426 p_dm_doc_sequence_value IN ra_customer_trx.doc_sequence_value%TYPE,
2427 p_dm_doc_sequence_id IN ra_customer_trx.doc_sequence_id%TYPE,
2428 p_tw_status IN OUT NOCOPY VARCHAR2,
2429 p_module_name IN VARCHAR2,
2430 p_module_version IN VARCHAR2
2431 ) IS
2432
2433 CURSOR applied_to_reserved_br_cur IS
2434 SELECT 'Y'
2435 FROM ar_payment_schedules ps,
2436 ar_receivable_applications ra
2437 WHERE ra.cash_receipt_id = p_cash_receipt_id
2438 AND ra.applied_payment_schedule_id = ps.payment_schedule_id
2439 AND ps.reserved_type IS NOT NULL
2440 AND ps.reserved_value IS NOT NULL
2441 AND ra.status = 'APP'
2442 AND ra.display = 'Y';
2443
2444 CURSOR applied_to_std_cur IS
2445 SELECT 'Y'
2446 FROM ar_receivable_applications ra
2447 WHERE ra.cash_receipt_id = p_cash_receipt_id
2448 AND ra.applied_payment_schedule_id = -2
2449 AND ra.display = 'Y';
2450
2451 l_cr_rec ar_cash_receipts%ROWTYPE;
2452 l_dm_number ar_payment_schedules.trx_number%TYPE;
2453 applied_to_reserved_br_rec applied_to_reserved_br_cur%ROWTYPE;
2454 applied_to_std_rec applied_to_std_cur%ROWTYPE;
2455
2456
2457 BEGIN
2458
2459 IF PG_DEBUG in ('Y', 'C') THEN
2460 arp_util.debug('>>>>>>>>>>>>> arp_cashbook.debit_memo_reversal ');
2461 END IF;
2462
2463 OPEN applied_to_std_cur;
2464 FETCH applied_to_std_cur INTO applied_to_std_rec;
2465
2466 IF applied_to_std_cur%FOUND THEN
2467 fnd_message.set_name('AR', 'AR_RW_CANNOT_REVERSE_BR_STD');
2468 app_exception.raise_exception;
2469 END IF;
2470
2471 CLOSE applied_to_std_cur;
2472
2473 OPEN applied_to_reserved_br_cur;
2474 FETCH applied_to_reserved_br_cur INTO applied_to_reserved_br_rec;
2475
2476 IF applied_to_reserved_br_cur%FOUND THEN
2477 fnd_message.set_name('AR', 'AR_RW_CANNOT_REVERSE_BR_STD');
2478 app_exception.raise_exception;
2479 END IF;
2480
2481 CLOSE applied_to_reserved_br_cur;
2482
2483 -- get cash receipt record:
2484 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
2485 arp_cash_receipts_pkg.nowaitlock_fetch_p(l_cr_rec);
2486
2487
2488 arp_reverse_receipt.debit_memo_reversal(
2489 l_cr_rec,
2490 p_cc_id,
2491 p_dm_cust_trx_type_id,
2492 p_dm_cust_trx_type,
2493 p_reversal_gl_date,
2494 p_reversal_date,
2495 p_reversal_category,
2496 p_reversal_reason_code,
2497 p_reversal_comments,
2498 NULL, NULL,
2499 NULL, NULL, NULL,
2500 NULL, NULL, NULL,
2501 NULL, NULL, NULL,
2502 NULL, NULL, NULL,
2503 NULL, NULL,
2504 l_dm_number,
2505 p_dm_doc_sequence_value,
2506 p_dm_doc_sequence_id,
2507 p_tw_status,
2508 p_module_name,
2509 p_module_version);
2510
2511 -- Populate OUT NOCOPY parameters
2512 p_dm_number := l_dm_number;
2513
2514 EXCEPTION
2515 WHEN OTHERS THEN
2516 IF PG_DEBUG in ('Y', 'C') THEN
2517 arp_util.debug('EXCEPTION: ARP_CASHBOOK.debit_memo_reversal');
2518 END IF;
2519 RAISE;
2520 END debit_memo_reversal;
2521
2522
2523 PROCEDURE Lock_Row(
2524 P_BATCH_ID IN ar_batches.batch_id%TYPE,
2525 P_AMOUNT IN ar_cash_receipt_history.amount%TYPE,
2526 P_ACCTD_AMOUNT IN ar_cash_receipt_history.acctd_amount%TYPE,
2527 P_NAME IN ar_batches.name%TYPE,
2528 P_BATCH_DATE IN ar_batches.batch_date%TYPE,
2529 P_GL_DATE IN ar_batches.gl_date%TYPE,
2530 P_STATUS IN ar_batches.status%TYPE,
2531 P_DEPOSIT_DATE IN ar_batches.deposit_date%TYPE,
2532 P_CLOSED_DATE IN ar_batches.closed_date%TYPE,
2533 P_TYPE IN ar_batches.type%TYPE,
2534 P_BATCH_SOURCE_ID IN ar_batches.batch_source_id%TYPE,
2535 P_CONTROL_COUNT IN ar_batches.control_count%TYPE,
2536 P_CONTROL_AMOUNT IN ar_batches.control_amount%TYPE,
2537 P_BATCH_APPLIED_STATUS IN ar_batches.batch_applied_status%TYPE,
2538 P_CURRENCY_CODE IN ar_batches.currency_code%TYPE,
2539 P_EXCHANGE_RATE_TYPE IN ar_batches.exchange_rate_type%TYPE,
2540 P_EXCHANGE_DATE IN ar_batches.exchange_date%TYPE,
2541 P_EXCHANGE_RATE IN ar_batches.exchange_rate%TYPE,
2542 P_TRANSMISSION_REQUEST_ID IN ar_batches.transmission_request_id%TYPE,
2543 P_LOCKBOX_ID IN ar_batches.lockbox_id%TYPE,
2544 P_LOCKBOX_BATCH_NAME IN ar_batches.lockbox_batch_name%TYPE,
2545 P_COMMENTS IN ar_batches.comments%TYPE,
2546 P_ATTRIBUTE_CATEGORY IN ar_batches.attribute_category%TYPE,
2547 P_ATTRIBUTE1 IN ar_batches.attribute1%TYPE,
2548 P_ATTRIBUTE2 IN ar_batches.attribute2%TYPE,
2549 P_ATTRIBUTE3 IN ar_batches.attribute3%TYPE,
2550 P_ATTRIBUTE4 IN ar_batches.attribute4%TYPE,
2551 P_ATTRIBUTE5 IN ar_batches.attribute5%TYPE,
2552 P_ATTRIBUTE6 IN ar_batches.attribute6%TYPE,
2553 P_ATTRIBUTE7 IN ar_batches.attribute7%TYPE,
2554 P_ATTRIBUTE8 IN ar_batches.attribute8%TYPE,
2555 P_ATTRIBUTE9 IN ar_batches.attribute9%TYPE,
2556 P_ATTRIBUTE10 IN ar_batches.attribute10%TYPE,
2557 P_MEDIA_REFERENCE IN ar_batches.media_reference%TYPE,
2558 P_OPERATION_REQUEST_ID IN ar_batches.operation_request_id%TYPE,
2559 P_RECEIPT_METHOD_ID IN ar_batches.receipt_method_id%TYPE,
2560 P_REMITTANCE_BANK_ACCOUNT_ID IN ar_batches.remit_bank_acct_use_id%TYPE,
2561 P_RECEIPT_CLASS_ID IN ar_batches.receipt_class_id%TYPE,
2562 P_ATTRIBUTE11 IN ar_batches.attribute11%TYPE,
2563 P_ATTRIBUTE12 IN ar_batches.attribute12%TYPE,
2564 P_ATTRIBUTE13 IN ar_batches.attribute13%TYPE,
2565 P_ATTRIBUTE14 IN ar_batches.attribute14%TYPE,
2566 P_ATTRIBUTE15 IN ar_batches.attribute15%TYPE,
2567 P_PROGRAM_APPLICATION_ID IN ar_batches.program_application_id%TYPE,
2568 P_PROGRAM_ID IN ar_batches.program_id%TYPE,
2569 P_PROGRAM_UPDATE_DATE IN ar_batches.program_update_date%TYPE,
2570 P_REMITTANCE_BANK_BRANCH_ID IN ar_batches.remittance_bank_branch_id%TYPE,
2571 P_REMIT_METHOD_CODE IN ar_batches.remit_method_code%TYPE,
2572 P_REQUEST_ID IN ar_batches.request_id%TYPE,
2573 P_SET_OF_BOOKS_ID IN ar_batches.set_of_books_id%TYPE,
2574 P_TRANSMISSION_ID IN ar_batches.transmission_id%TYPE,
2575 P_BANK_DEPOSIT_NUMBER IN ar_batches.bank_deposit_number%TYPE)
2576 IS
2577
2578 CURSOR C IS
2579 SELECT crh.cash_receipt_history_id
2580 FROM ar_cash_receipt_history crh, ar_cash_receipts acr
2581 WHERE crh.batch_id = P_BATCH_ID
2582 AND crh.status not in ('REVERSED')
2583 AND crh.cash_receipt_id = acr.cash_receipt_id
2584 FOR UPDATE NOWAIT;
2585
2586 CURSOR BATCH IS
2587 SELECT *
2588 FROM ar_batches
2589 WHERE batch_id = P_BATCH_ID
2590 FOR UPDATE NOWAIT;
2591
2592 Recinfo C%ROWTYPE;
2593 Batchinfo BATCH%ROWTYPE;
2594 c_batch_id ar_batches.batch_id%TYPE;
2595 c_amount ar_cash_receipt_history.amount%TYPE;
2596 c_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
2597
2598 BEGIN
2599
2600 OPEN C;
2601 CLOSE C;
2602
2603 OPEN BATCH;
2604 FETCH BATCH INTO Batchinfo;
2605 if (BATCH%NOTFOUND) then
2606 CLOSE BATCH;
2607 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
2608 APP_EXCEPTION.Raise_Exception;
2609 end if;
2610 CLOSE BATCH;
2611
2612 SELECT sum(amount) , sum(acctd_amount)
2613 INTO c_amount, c_acctd_amount
2614 FROM ar_cash_receipt_history
2615 WHERE batch_id = P_BATCH_ID
2616 AND status not in ('REVERSED');
2617
2618
2619 if ( (c_amount = P_AMOUNT OR
2620 (c_amount is NULL AND P_AMOUNT is NULL))
2621 AND (c_acctd_amount = P_ACCTD_AMOUNT OR
2622 (c_acctd_amount is NULL AND P_ACCTD_AMOUNT is NULL))
2623 AND (Batchinfo.batch_id = P_BATCH_ID OR
2624 (Batchinfo.batch_id is NULL AND P_BATCH_ID is NULL))
2625 AND (Batchinfo.name = P_NAME OR
2626 (Batchinfo.name is NULL AND P_NAME is NULL))
2627 AND (Batchinfo.batch_date = P_BATCH_DATE OR
2628 (Batchinfo.batch_date is NULL AND P_BATCH_DATE is NULL))
2629 AND (Batchinfo.gl_date = P_GL_DATE OR
2630 (Batchinfo.gl_date is NULL AND P_GL_DATE is NULL))
2631 AND (Batchinfo.status = P_STATUS OR
2632 (Batchinfo.status is NULL AND P_STATUS is NULL))
2633 AND (Batchinfo.deposit_date = P_DEPOSIT_DATE OR
2634 (Batchinfo.deposit_date is NULL AND P_DEPOSIT_DATE is NULL))
2635 AND (Batchinfo.closed_date = P_CLOSED_DATE OR
2636 (Batchinfo.closed_date is NULL AND P_CLOSED_DATE is NULL))
2637 AND (Batchinfo.type = P_TYPE OR
2638 (Batchinfo.type is NULL AND P_TYPE is NULL))
2639 AND (Batchinfo.batch_source_id = P_BATCH_SOURCE_ID OR
2640 (Batchinfo.batch_source_id is NULL AND P_BATCH_SOURCE_ID is NULL))
2641 AND (Batchinfo.control_count = P_CONTROL_COUNT OR
2642 (Batchinfo.control_count is NULL AND P_CONTROL_COUNT is NULL))
2643 AND (Batchinfo.control_amount = P_CONTROL_AMOUNT OR
2644 (Batchinfo.control_amount is NULL AND P_CONTROL_AMOUNT is NULL))
2645 AND (Batchinfo.batch_applied_status = P_BATCH_APPLIED_STATUS OR
2646 (Batchinfo.batch_applied_status is NULL AND P_BATCH_APPLIED_STATUS is NULL))
2647 AND (Batchinfo.currency_code = P_CURRENCY_CODE OR
2648 (Batchinfo.currency_code is NULL AND P_CURRENCY_CODE is NULL))
2649 AND (Batchinfo.exchange_rate_type = P_EXCHANGE_RATE_TYPE OR
2650 (Batchinfo.exchange_rate_type is NULL AND P_EXCHANGE_RATE_TYPE is NULL))
2651 AND (Batchinfo.exchange_date = P_EXCHANGE_DATE OR
2652 (Batchinfo.exchange_date is NULL AND P_EXCHANGE_DATE is NULL))
2653 AND (Batchinfo.exchange_rate = P_EXCHANGE_RATE OR
2654 (Batchinfo.exchange_rate is NULL AND P_EXCHANGE_RATE is NULL))
2655 AND (Batchinfo.transmission_request_id = P_TRANSMISSION_REQUEST_ID OR
2656 (Batchinfo.transmission_request_id is NULL AND P_TRANSMISSION_REQUEST_ID is NULL))
2657 AND (Batchinfo.lockbox_id = P_LOCKBOX_ID OR
2658 (Batchinfo.lockbox_id is NULL AND P_LOCKBOX_ID is NULL))
2659 AND (Batchinfo.lockbox_batch_name = P_LOCKBOX_BATCH_NAME OR
2660 (Batchinfo.lockbox_batch_name is NULL AND P_LOCKBOX_BATCH_NAME is NULL))
2661 AND (Batchinfo.comments = P_COMMENTS OR
2662 (Batchinfo.comments is NULL and P_COMMENTS is NULL))
2663 AND (Batchinfo.attribute_category = P_ATTRIBUTE_CATEGORY OR
2664 (Batchinfo.attribute_category is NULL AND P_ATTRIBUTE_CATEGORY is NULL))
2665 AND (Batchinfo.attribute1 = P_ATTRIBUTE1 OR
2666 (Batchinfo.attribute1 is NULL AND P_ATTRIBUTE1 is NULL))
2667 AND (Batchinfo.attribute2 = P_ATTRIBUTE2 OR
2668 (Batchinfo.attribute2 is NULL AND P_ATTRIBUTE2 is NULL))
2669 AND (Batchinfo.attribute3 = P_ATTRIBUTE3 OR
2670 (Batchinfo.attribute3 is NULL AND P_ATTRIBUTE3 is NULL))
2671 AND (Batchinfo.attribute4 = P_ATTRIBUTE4 OR
2672 (Batchinfo.attribute4 is NULL AND P_ATTRIBUTE4 is NULL))
2673 AND (Batchinfo.attribute5 = P_ATTRIBUTE5 OR
2674 (Batchinfo.attribute5 is NULL AND P_ATTRIBUTE5 is NULL))
2675 AND (Batchinfo.attribute6 = P_ATTRIBUTE6 OR
2676 (Batchinfo.attribute6 is NULL AND P_ATTRIBUTE6 is NULL))
2677 AND (Batchinfo.attribute7 = P_ATTRIBUTE7 OR
2678 (Batchinfo.attribute7 is NULL AND P_ATTRIBUTE7 is NULL))
2679 AND (Batchinfo.attribute8 = P_ATTRIBUTE8 OR
2680 (Batchinfo.attribute8 is NULL AND P_ATTRIBUTE8 is NULL))
2681 AND (Batchinfo.attribute9 = P_ATTRIBUTE9 OR
2682 (Batchinfo.attribute9 is NULL AND P_ATTRIBUTE9 is NULL))
2683 AND (Batchinfo.attribute10 = P_ATTRIBUTE10 OR
2684 (Batchinfo.attribute10 is NULL AND P_ATTRIBUTE10 is NULL))
2685 AND (Batchinfo.media_reference = P_MEDIA_REFERENCE OR
2686 (Batchinfo.media_reference is NULL AND P_MEDIA_REFERENCE is NULL))
2687 AND (Batchinfo.operation_request_id = P_OPERATION_REQUEST_ID OR
2688 (Batchinfo.operation_request_id is NULL AND P_OPERATION_REQUEST_ID is NULL))
2689 AND (Batchinfo.receipt_method_id = P_RECEIPT_METHOD_ID OR
2690 (Batchinfo.receipt_method_id is NULL AND P_RECEIPT_METHOD_ID is NULL))
2691 AND (Batchinfo.remit_bank_acct_use_id = P_REMITTANCE_BANK_ACCOUNT_ID OR
2692 (Batchinfo.remit_bank_acct_use_id is NULL AND P_REMITTANCE_BANK_ACCOUNT_ID is NULL))
2693 AND (Batchinfo.receipt_class_id = P_RECEIPT_CLASS_ID OR
2694 (Batchinfo.receipt_class_id is NULL AND P_RECEIPT_CLASS_ID is NULL))
2695 AND (Batchinfo.attribute11 = P_ATTRIBUTE11 OR
2696 (Batchinfo.attribute11 is NULL AND P_ATTRIBUTE11 is NULL))
2697 AND (Batchinfo.attribute12 = P_ATTRIBUTE12 OR
2698 (Batchinfo.attribute12 is NULL AND P_ATTRIBUTE12 is NULL))
2699 AND (Batchinfo.attribute13 = P_ATTRIBUTE13 OR
2700 (Batchinfo.attribute13 is NULL AND P_ATTRIBUTE13 is NULL))
2701 AND (Batchinfo.attribute14 = P_ATTRIBUTE14 OR
2702 (Batchinfo.attribute14 is NULL AND P_ATTRIBUTE14 is NULL))
2703 AND (Batchinfo.attribute15 = P_ATTRIBUTE15 OR
2704 (Batchinfo.attribute15 is NULL AND P_ATTRIBUTE15 is NULL))
2705 AND (Batchinfo.program_application_id = P_PROGRAM_APPLICATION_ID OR
2706 (Batchinfo.program_application_id is NULL AND P_PROGRAM_APPLICATION_ID is NULL))
2707 AND (Batchinfo.program_id = P_PROGRAM_ID OR
2708 (Batchinfo.program_id is NULL AND P_PROGRAM_ID is NULL))
2709 AND (Batchinfo.program_update_date = P_PROGRAM_UPDATE_DATE OR
2710 (Batchinfo.program_update_date is NULL AND P_PROGRAM_UPDATE_DATE is NULL))
2711 AND (Batchinfo.remittance_bank_branch_id = P_REMITTANCE_BANK_BRANCH_ID OR
2712 (Batchinfo.remittance_bank_branch_id is NULL AND P_REMITTANCE_BANK_BRANCH_ID is NULL))
2713 AND (Batchinfo.remit_method_code = P_REMIT_METHOD_CODE OR
2714 (Batchinfo.remit_method_code is NULL AND P_REMIT_METHOD_CODE is NULL))
2715 AND (Batchinfo.request_id = P_REQUEST_ID OR
2716 (Batchinfo.request_id is NULL AND P_REQUEST_ID is NULL))
2717 AND (Batchinfo.set_of_books_id = P_SET_OF_BOOKS_ID OR
2718 (Batchinfo.set_of_books_id is NULL AND P_SET_OF_BOOKS_ID is NULL))
2719 AND (Batchinfo.transmission_id = P_TRANSMISSION_ID OR
2720 (Batchinfo.transmission_id is NULL AND P_TRANSMISSION_ID is NULL))
2721 AND (Batchinfo.bank_deposit_number = P_BANK_DEPOSIT_NUMBER OR
2722 (Batchinfo.bank_deposit_number is NULL AND P_BANK_DEPOSIT_NUMBER is NULL))
2723 )
2724 then
2725 return;
2726 else
2727 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2728 APP_EXCEPTION.Raise_Exception;
2729 end if;
2730 END Lock_Row;
2731
2732 /* ----------------------------------------------------------------------
2733 Function receipt_debit_memo_reversed
2734
2735 Parameters: p_cash_receipt_id
2736
2737 Return Value: VARCHAR2(1) 'Y' if receipt was debit-memo-reversed
2738 'N' if receipt was not debit-memo-reversed
2739
2740 This function was added for CE enhancement request 681187. The function
2741 can be used in a view to determine if a given receipt was debit-memo-
2742 reversed.
2743 Note that the function will return 'N' if the receipt was reversed
2744 with normal (non-debit-memo) reversal. It will also return 'N'
2745 if the passed in parameter is not a valid cash_receipt_id, i.e.,
2746 there is no error handling for this case.
2747
2748 Modification History:
2749
2750 08-JUL-98 Guat Eng Tan created
2751 ---------------------------------------------------------------------- */
2752
2753
2754 FUNCTION receipt_debit_memo_reversed( p_cash_receipt_id IN NUMBER)
2755 RETURN VARCHAR2 IS
2756 l_result VARCHAR2(1);
2757 BEGIN
2758
2759 BEGIN
2760 SELECT 'Y'
2761 INTO l_result
2762 FROM ar_payment_schedules ps_dm
2763 WHERE ps_dm.reversed_cash_receipt_id = p_cash_receipt_id
2764 AND ps_dm.class = 'DM';
2765
2766 EXCEPTION
2767 WHEN NO_DATA_FOUND THEN
2768 l_result := 'N';
2769 WHEN OTHERS THEN
2770 RAISE;
2771 END;
2772
2773 RETURN l_result;
2774
2775 END;
2776
2777
2778 PROCEDURE update_actual_value_date(p_cash_receipt_id IN NUMBER,
2779 p_actual_value_date IN DATE) IS
2780 BEGIN
2781
2782 --Yao zhang modified for bug 10124745
2783 UPDATE AR_CASH_RECEIPTS
2784 SET actual_value_date = p_actual_value_date,
2785 rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
2786 last_updated_by = arp_global.last_updated_by,
2787 last_update_date = SYSDATE,
2788 last_update_login = arp_global.last_update_login
2789 WHERE cash_receipt_id = p_cash_receipt_id;
2790
2791 END;
2792
2793 -- Added for bug 16229574
2794 PROCEDURE refresh_risk_value IS
2795 BEGIN
2796 ar_bus_event_sub_pvt.refresh_at_risk_value(ARP_CASHBOOK.g_customer_id_tab,
2797 ARP_CASHBOOK.g_site_use_id_tab,
2798 ARP_CASHBOOK.g_currency_tab,
2799 ARP_CASHBOOK.g_org_id_tab);
2800
2801 ARP_CASHBOOK.g_customer_id_tab.delete;
2802 ARP_CASHBOOK.g_site_use_id_tab.delete;
2803 ARP_CASHBOOK.g_currency_tab.delete;
2804 ARP_CASHBOOK.g_org_id_tab.delete;
2805 ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 IF PG_DEBUG in ('Y', 'C') THEN
2809 arp_util.debug('EXCEPTION: ARP_CASHBOOK.refresh_risk_value');
2810 fnd_file.put_line(FND_FILE.LOG,'Error in setting refresh at risk value');
2811 END IF;
2812 RAISE;
2813 END;
2814
2815 FUNCTION revision RETURN VARCHAR2 IS
2816 BEGIN
2817
2818 RETURN '$Revision: 120.29.12020000.6 $';
2819
2820 END revision;
2821
2822 END ARP_CASHBOOK;