[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_RCTS
Source
1 PACKAGE BODY ARP_PROCESS_RCTS AS
2 /* $Header: ARRERGWB.pls 120.35.12020000.2 2013/01/20 11:05:49 kgnanase ship $ */
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9
10 /* ---------------------- Public functions -------------------------------- */
11
12
13 FUNCTION revision RETURN VARCHAR2 IS
14 BEGIN
15
16 RETURN '$Revision: 120.35.12020000.2 $';
17
18 END revision;
19
20
21 /*===========================================================================+
22 | PROCEDURE |
23 | lock_cash_receipt |
24 | |
25 | DESCRIPTION |
26 | Locks a cash receipt. |
27 | |
28 | SCOPE - PUBLIC |
29 | |
30 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
31 | |
32 | ARGUMENTS |
33 | IN: |
34 | OUT: |
35 | |
36 | RETURNS |
37 | |
38 | NOTES |
39 | |
40 | MODIFICATION HISTORY |
41 | |
42 | 20-NOV-95 OSTEINME created |
43 | 01-NOV-96 OSTEINME added parameter anticipated_clearing_date |
44 | for CashBook enhancement (float support) |
45 | 01-NOV-96 OSTEINME added parameters for Japan project: |
46 | - customer_bank_branch_id |
47 | 30-DEC-96 OSTEINME added global flexfield parameters |
48 | 05-FEB-03 RVSHARMA added new parameter p_receipt_status for |
49 | Bug 2688648. | | |
50 +===========================================================================*/
51
52
53 PROCEDURE lock_cash_receipt(
54 p_cash_receipt_id IN NUMBER,
55 p_currency_code IN VARCHAR2,
56 p_amount IN NUMBER,
57 p_pay_from_customer IN NUMBER,
58 p_receipt_number IN VARCHAR2,
59 p_receipt_date IN DATE,
60 p_gl_date IN DATE,
61 p_maturity_date IN DATE,
62 p_comments IN VARCHAR2,
63 p_exchange_rate_type IN VARCHAR2,
64 p_exchange_rate IN NUMBER,
65 p_exchange_date IN DATE,
66 p_batch_id IN NUMBER,
67 p_attribute_category IN VARCHAR2,
68 p_attribute1 IN VARCHAR2,
69 p_attribute2 IN VARCHAR2,
70 p_attribute3 IN VARCHAR2,
71 p_attribute4 IN VARCHAR2,
72 p_attribute5 IN VARCHAR2,
73 p_attribute6 IN VARCHAR2,
74 p_attribute7 IN VARCHAR2,
75 p_attribute8 IN VARCHAR2,
76 p_attribute9 IN VARCHAR2,
77 p_attribute10 IN VARCHAR2,
78 p_attribute11 IN VARCHAR2,
79 p_attribute12 IN VARCHAR2,
80 p_attribute13 IN VARCHAR2,
81 p_attribute14 IN VARCHAR2,
82 p_attribute15 IN VARCHAR2,
83 p_override_remit_account_flag IN VARCHAR2,
84 p_remittance_bank_account_id IN NUMBER,
85 p_customer_bank_account_id IN NUMBER,
86 p_customer_site_use_id IN NUMBER,
87 p_customer_receipt_reference IN VARCHAR2,
88 p_factor_discount_amount IN NUMBER,
89 p_deposit_date IN DATE,
90 p_receipt_method_id IN NUMBER,
91 p_doc_sequence_value IN NUMBER,
92 p_doc_sequence_id IN NUMBER,
93 p_ussgl_transaction_code IN VARCHAR2,
94 p_vat_tax_id IN NUMBER,
95 p_anticipated_clearing_date IN DATE,
96 p_customer_bank_branch_id IN NUMBER,
97 --
98 -- ******* Global Flexfield parameters *******
99 --
100 p_global_attribute1 IN VARCHAR2,
101 p_global_attribute2 IN VARCHAR2,
102 p_global_attribute3 IN VARCHAR2,
103 p_global_attribute4 IN VARCHAR2,
104 p_global_attribute5 IN VARCHAR2,
105 p_global_attribute6 IN VARCHAR2,
106 p_global_attribute7 IN VARCHAR2,
107 p_global_attribute8 IN VARCHAR2,
108 p_global_attribute9 IN VARCHAR2,
109 p_global_attribute10 IN VARCHAR2,
110 p_global_attribute11 IN VARCHAR2,
111 p_global_attribute12 IN VARCHAR2,
112 p_global_attribute13 IN VARCHAR2,
113 p_global_attribute14 IN VARCHAR2,
114 p_global_attribute15 IN VARCHAR2,
115 p_global_attribute16 IN VARCHAR2,
116 p_global_attribute17 IN VARCHAR2,
117 p_global_attribute18 IN VARCHAR2,
118 p_global_attribute19 IN VARCHAR2,
119 p_global_attribute20 IN VARCHAR2,
120 p_global_attribute_category IN VARCHAR2,
121 --
122 -- Notes Receivable
123 --
124 p_issuer_name IN VARCHAR2,
125 p_issue_date IN DATE,
126 p_issuer_bank_branch_id IN NUMBER,
127 --
128 p_application_notes IN VARCHAR2,
129 --
130 --
131 p_form_name IN VARCHAR2,
132 p_form_version IN VARCHAR2,
133 p_payment_server_order_num IN VARCHAR2,
134 p_approval_code IN VARCHAR2,
135 p_receipt_status IN VARCHAR2, /* Bug 2688648 */
136 p_rec_version_number IN NUMBER, /* Bug fix 3032059 */
137 p_payment_trxn_extension_id IN NUMBER,
138 p_automatch_set_id IN NUMBER, /* ER Automatch Application */
139 p_autoapply_flag IN VARCHAR2
140 ) IS
141 --
142 l_cr_rec AR_CASH_RECEIPTS%ROWTYPE;
143 --
144 BEGIN
145
146 IF PG_DEBUG in ('Y', 'C') THEN
147 arp_standard.debug('arp_process_receipts.lock_cash_receipt()+');
148 END IF;
149
150 arp_cash_receipts_pkg.set_to_dummy(l_cr_rec);
151
152 l_cr_rec.cash_receipt_id := p_cash_receipt_id;
153 l_cr_rec.currency_code := p_currency_code;
154 l_cr_rec.amount := p_amount;
155 l_cr_rec.receipt_number := p_receipt_number;
156 l_cr_rec.receipt_date := p_receipt_date;
157 l_cr_rec.comments := p_comments;
158 l_cr_rec.exchange_rate_type := p_exchange_rate_type;
159 l_cr_rec.exchange_rate := p_exchange_rate;
160 l_cr_rec.exchange_date := p_exchange_date;
161 l_cr_rec.attribute_category := p_attribute_category;
162 l_cr_rec.attribute1 := p_attribute1;
163 l_cr_rec.attribute2 := p_attribute2;
164 l_cr_rec.attribute3 := p_attribute3;
165 l_cr_rec.attribute4 := p_attribute4;
166 l_cr_rec.attribute5 := p_attribute5;
167 l_cr_rec.attribute6 := p_attribute6;
168 l_cr_rec.attribute7 := p_attribute7;
169 l_cr_rec.attribute8 := p_attribute8;
170 l_cr_rec.attribute9 := p_attribute9;
171 l_cr_rec.attribute10 := p_attribute10;
172 l_cr_rec.attribute11 := p_attribute11;
173 l_cr_rec.attribute12 := p_attribute12;
174 l_cr_rec.attribute13 := p_attribute13;
175 l_cr_rec.attribute14 := p_attribute14;
176 l_cr_rec.attribute15 := p_attribute15;
177
178 l_cr_rec.remittance_bank_account_id := p_remittance_bank_account_id;
179 l_cr_rec.override_remit_account_flag := p_override_remit_account_flag;
180 l_cr_rec.deposit_date := p_deposit_date;
181 l_cr_rec.receipt_method_id := p_receipt_method_id;
182
183 l_cr_rec.doc_sequence_value := p_doc_sequence_value;
184 l_cr_rec.doc_sequence_id := p_doc_sequence_id;
185 l_cr_rec.pay_from_customer := p_pay_from_customer;
186 l_cr_rec.customer_site_use_id := p_customer_site_use_id;
187 l_cr_rec.customer_receipt_reference := p_customer_receipt_reference;
188 l_cr_rec.customer_bank_account_id := p_customer_bank_account_id;
189 l_cr_rec.ussgl_transaction_code := p_ussgl_transaction_code;
190 l_cr_rec.vat_tax_id := p_vat_tax_id;
191 l_cr_rec.anticipated_clearing_date := p_anticipated_clearing_date;
192 l_cr_rec.customer_bank_branch_id := p_customer_bank_branch_id;
193
194 l_cr_rec.global_attribute1 := p_global_attribute1;
195 l_cr_rec.global_attribute2 := p_global_attribute2;
196 l_cr_rec.global_attribute3 := p_global_attribute3;
197 l_cr_rec.global_attribute4 := p_global_attribute4;
198 l_cr_rec.global_attribute5 := p_global_attribute5;
199 l_cr_rec.global_attribute6 := p_global_attribute6;
200 l_cr_rec.global_attribute7 := p_global_attribute7;
201 l_cr_rec.global_attribute8 := p_global_attribute8;
202 l_cr_rec.global_attribute9 := p_global_attribute9;
203 l_cr_rec.global_attribute10 := p_global_attribute10;
204 l_cr_rec.global_attribute11 := p_global_attribute11;
205 l_cr_rec.global_attribute12 := p_global_attribute12;
206 l_cr_rec.global_attribute13 := p_global_attribute13;
207 l_cr_rec.global_attribute14 := p_global_attribute14;
208 l_cr_rec.global_attribute15 := p_global_attribute15;
209 l_cr_rec.global_attribute16 := p_global_attribute16;
210 l_cr_rec.global_attribute17 := p_global_attribute17;
211 l_cr_rec.global_attribute18 := p_global_attribute18;
212 l_cr_rec.global_attribute19 := p_global_attribute19;
213 l_cr_rec.global_attribute20 := p_global_attribute20;
214 l_cr_rec.global_attribute_category := p_global_attribute_category;
215
216 l_cr_rec.issuer_name := p_issuer_name;
217 l_cr_rec.issue_date := p_issue_date;
218 l_cr_rec.issuer_bank_branch_id := p_issuer_bank_branch_id;
219
220 -- Enh. 2074220:
221 l_cr_rec.application_notes := p_application_notes;
222
223 l_cr_rec.payment_server_order_num := p_payment_server_order_num;
224 l_cr_rec.approval_code := p_approval_code;
225 /* Bug fix 2963757 : Revert the fix for bug 2688648 */
226 /* l_cr_rec.status := p_receipt_status; */ /* bug 2688648 */
227
228 /* Bug fix 3032059 */
229 l_cr_rec.rec_version_number := p_rec_version_number;
230
231 /* PAYMENT_UPTAKE */
232 l_cr_rec.payment_trxn_extension_id := p_payment_trxn_extension_id;
233 l_cr_rec.automatch_set_id := p_automatch_set_id; /* ER Automatch Application */
234 l_cr_rec.autoapply_flag := p_autoapply_flag;
235
236
237 IF PG_DEBUG in ('Y', 'C') THEN
238 arp_standard.debug('lock_cash_receipt: ' || 'Exchange rate = ' || p_exchange_rate);
239 arp_standard.debug('lock_cash_receipt: ' || 'Exchange rate date = ' || p_exchange_date);
240 arp_standard.debug('lock_cash_receipt: ' || 'Exchange rate type = ' || p_exchange_rate_type);
241 arp_standard.debug('lock_cash_receipt: ' || 'Currency code = ' || p_currency_code);
242 arp_standard.debug('lock_cash_receipt: ' || 'Receipt Number = ' || p_receipt_number);
243 arp_standard.debug('lock_cash_receipt: ' || 'Payment server ord num = ' || p_payment_server_order_num);
244 arp_standard.debug('lock_cash_receipt: ' || 'Approval code = ' || p_approval_code);
245 END IF;
246
247 arp_cash_receipts_pkg.lock_compare_p(l_cr_rec);
248
249 IF PG_DEBUG in ('Y', 'C') THEN
250 arp_standard.debug('arp_process_receipts.lock_cash_receipt()-');
251 END IF;
252
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
256 APP_EXCEPTION.Raise_Exception;
257 WHEN OTHERS THEN
258 IF PG_DEBUG in ('Y', 'C') THEN
259 arp_standard.debug('EXCEPTION: arp_process_receipts.lock_cash_receipt()');
260 END IF;
261 RAISE;
262
263 END lock_cash_receipt;
264
265
266 /*===========================================================================+
267 | PROCEDURE |
268 | delete_cash_receipt |
269 | |
270 | DESCRIPTION |
271 | Entity handler that delete cash receipts. |
272 | |
273 | SCOPE - PUBLIC |
274 | |
275 | NOTES |
276 | |
277 | MODIFICATION HISTORY |
278 | |
279 | 30-NOV-95 OSTEINME created |
280 | 28-Dec-98 DJANCIS added call to set posted flag to deterime if |
281 | receipt was posted before deleting it |
282 | 08-Nov-01 DJANCIS Modified for mrc trigger elimination project |
283 | added call to ar_mrc_engine for deletes to |
284 | ar_payment_schedules |
285 +===========================================================================*/
286
287 PROCEDURE delete_cash_receipt(
288 p_cash_receipt_id IN NUMBER,
289 p_batch_id IN NUMBER) IS
290
291 CURSOR get_app_C IS
292 select app.receivable_application_id app_id
293 from ar_receivable_applications app
294 where app.cash_receipt_id = p_cash_receipt_id
295 and nvl(app.confirmed_flag,'Y') = 'Y' --confirmed records have accounting only
296 and exists (select 'x'
297 from ar_distributions ard
298 where ard.source_table = 'RA'
299 and ard.source_id = app.receivable_application_id)
300 order by decode(app.status,
301 'UNAPP',1, --Delete UNAPP related accounting first as record may be paired
302 2);
303
304 /* Bug 4173339 */
305 l_trx_sum_hist_rec AR_TRX_SUMMARY_HIST%rowtype;
306 l_event_source_info xla_events_pub_pkg.t_event_source_info;
307 l_security xla_events_pub_pkg.t_security;
308 l_event_id NUMBER;
309
310 CURSOR get_app_ev is
311 select distinct ra.event_id event_id , ra.cash_receipt_id cash_receipt_id from
312 ar_receivable_applications ra where ra.cash_receipt_id = p_cash_receipt_id
313 and ra.status not in ('UNAPP','UNID')
314 and ra.event_id is not null
315 and exists
316 ( select 'x' from xla_events where event_id = ra.event_id
317 and application_id = 222 ) ;
318
319
320 CURSOR get_existing_ps IS
321 SELECT payment_schedule_id,
322 invoice_currency_code,
323 due_date,
324 amount_in_dispute,
325 amount_due_original,
326 amount_due_remaining,
327 amount_adjusted,
328 cash_receipt_id,
329 customer_id,
330 customer_site_use_id,
331 trx_date
332 FROM ar_payment_schedules
333 WHERE cash_receipt_id = p_cash_receipt_id;
334
335 CURSOR cReceiptDtls IS
336 SELECT receipt_number,
337 receipt_date
338 FROM ar_cash_receipts
339 WHERE cash_receipt_id = p_cash_receipt_id;
340
341
342 l_history_id NUMBER;
343
344 p_posted_flag BOOLEAN;
345 l_get_app_rec get_app_C%ROWTYPE;
346 l_ae_doc_rec ae_doc_rec_type;
347
348 l_ar_ps_key_value_list gl_ca_utility_pkg.r_key_value_arr;
349 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
350 l_rec_app_key_value_list gl_ca_utility_pkg.r_key_value_arr;
351
352 l_receipt_number ar_cash_receipts.receipt_number%type;
353 l_receipt_date ar_cash_receipts.receipt_date%type;
354
355
356 BEGIN
357 IF PG_DEBUG in ('Y', 'C') THEN
358 arp_standard.debug('arp_process_receipts.delete_cash_receipt()+');
359 END IF;
360
361 ARP_PROCESS_RCTS.set_posted_flag(p_cash_receipt_id,
362 p_posted_flag);
363 IF ( p_posted_flag = TRUE) THEN
364 -- raise and error and exit
365 IF PG_DEBUG in ('Y', 'C') THEN
366 arp_standard.debug('delete_cash_receipt: ' || 'posted flag = true ');
367 END IF;
368 fnd_message.set_name('AR','AR_RW_DEL_REC_POSTED');
369 app_exception.raise_exception;
370
371 END IF;
372
373 -- lock receipt record to make sure no one else has it locked
374
375 arp_cash_receipts_pkg.lock_p(p_cash_receipt_id);
376
377 -- delete AR_CASH_RECEIPTS receipt record:
378
379 -- Before Deletion get the recipt details for Summary Tables
380 FOR ReceiptDtlsRec IN cReceiptDtls
381 LOOP
382 l_receipt_number := ReceiptDtlsRec.receipt_number;
383 l_receipt_date := ReceiptDtlsRec.receipt_date;
384 END LOOP;
385
386 arp_cash_receipts_pkg.delete_p(p_cash_receipt_id);
387
388 -- delete AR_DISTRIBUTIONS records created for each
389 -- AR_CASH_RECEIPT_HISTORY record.
390
391 DELETE AR_DISTRIBUTIONS
392 WHERE source_table = 'CRH'
393 AND source_id IN (
394 SELECT cash_receipt_history_id
395 FROM ar_cash_receipt_history
396 WHERE cash_receipt_id = p_cash_receipt_id);
397 --Commented for bug 12989298
398 --RETURNING line_id
399 --BULK COLLECT INTO l_ar_dist_key_value_list;
400
401 /*---------------------------------+
402 | Calling central MRC library |
403 | for MRC Integration |
404 +---------------------------------*/
405
406 ar_mrc_engine.maintain_mrc_data(
407 p_event_mode => 'DELETE',
408 p_table_name => 'AR_DISTRIBUTIONS',
409 p_mode => 'BATCH',
410 p_key_value_list => l_ar_dist_key_value_list);
411
412
413 --Bug # 6450286
414 --------------------------------
415 -- Delete the corresponding event in XLA schema
416 --------------------------------
417 ARP_XLA_EVENTS.delete_event( p_document_id => p_cash_receipt_id,
418 p_doc_table => 'CRH');
419
420
421 -- delete all AR_CASH_RECEIPT_HISTORY records created for this
422 -- receipt:
423 -- Bug 2021718: Call entity handler for delete
424 arp_cr_history_pkg.delete_p_cr(p_cash_receipt_id);
425
426 -- DELETE AR_CASH_RECEIPT_HISTORY
427 -- WHERE cash_receipt_id = p_cash_receipt_id;
428
429 --Delete all associated accounting with the receivable applications
430 --first.
431
432 FOR l_get_app_rec IN get_app_C LOOP
433
434 l_ae_doc_rec.document_type := 'RECEIPT';
435 l_ae_doc_rec.document_id := p_cash_receipt_id;
436 l_ae_doc_rec.accounting_entity_level := 'ONE';
437 l_ae_doc_rec.source_table := 'RA';
438 l_ae_doc_rec.source_id := l_get_app_rec.app_id;
439 l_ae_doc_rec.source_id_old := '';
440 l_ae_doc_rec.other_flag := '';
441
442 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
443
444 END LOOP;
445
446 FOR l_get_ev IN get_app_ev LOOP
447
448 l_event_id := l_get_ev.event_id ;
449
450 IF l_event_id IS NOT NULL THEN
451
452 l_event_source_info.entity_type_code:= 'RECEIPTS';
453 l_security.security_id_int_1 := arp_global.sysparam.org_id;
454 l_event_source_info.application_id := 222;
455 l_event_source_info.ledger_id := arp_standard.sysparm.set_of_books_id; --to be set
456 l_event_source_info.source_id_int_1 := l_get_ev.cash_receipt_id ;
457
458 xla_events_pub_pkg.delete_event
459 ( p_event_source_info => l_event_source_info,
460 p_event_id => l_event_id,
461 p_valuation_method => NULL,
462 p_security_context => l_security);
463
464 END IF;
465
466 END LOOP;
467
468
469 -- delete all AR_RECEIVABLE_APPLICATIONS records created for this
470 -- receipt:
471
472 DELETE AR_RECEIVABLE_APPLICATIONS
473 WHERE cash_receipt_id = p_cash_receipt_id;
474 --Commented for bug 12989298
475 --RETURNING receivable_application_id
476 --BULK COLLECT INTO l_rec_app_key_value_list;
477
478 /*---------------------------------+
479 | Calling central MRC library |
480 | for MRC Integration |
481 +---------------------------------*/
482
483 ar_mrc_engine.maintain_mrc_data(
484 p_event_mode => 'DELETE',
485 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
486 p_mode => 'BATCH',
487 p_key_value_list => l_rec_app_key_value_list);
488
489 /* Bug 4173339
490 Store the ps record values into history table before deleting.
491 */
492 OPEN get_existing_ps;
493
494 FETCH get_existing_ps
495 INTO l_trx_sum_hist_rec.payment_schedule_id,
496 l_trx_sum_hist_rec.currency_code,
497 l_trx_sum_hist_rec.due_date,
498 l_trx_sum_hist_rec.amount_in_dispute,
499 l_trx_sum_hist_rec.amount_due_original,
500 l_trx_sum_hist_rec.amount_due_remaining,
501 l_trx_sum_hist_rec.amount_adjusted,
502 l_trx_sum_hist_rec.customer_trx_id,
503 l_trx_sum_hist_rec.customer_id,
504 l_trx_sum_hist_rec.site_use_id,
505 l_trx_sum_hist_rec.trx_date;
506
507 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
508 l_history_id,
509 'PMT',
510 'DELETE_PMT');
511
512 CLOSE get_existing_ps;
513
514 -- delete AR_PAYMENT_SCHEDULE record created for this receipt:
515
516 DELETE AR_PAYMENT_SCHEDULES
517 WHERE cash_receipt_id = p_cash_receipt_id;
518 --Commented for bug 12989298
519 --RETURNING payment_schedule_id
520 --BULK COLLECT INTO l_ar_ps_key_value_list;
521
522 /*---------------------------------+
523 | Calling central MRC library |
524 | for MRC Integration |
525 +---------------------------------*/
526
527 ar_mrc_engine.maintain_mrc_data(
528 p_event_mode => 'DELETE',
529 p_table_name => 'AR_PAYMENT_SCHEDULES',
530 p_mode => 'BATCH',
531 p_key_value_list => l_ar_ps_key_value_list);
532 --
533
534 -- update batch status
535
536 IF (p_batch_id IS NOT NULL) THEN
537 arp_rw_batches_check_pkg.update_batch_status(
538 p_batch_id);
539 END IF;
540
541 -- Raise the Deletion Business Event
542 AR_BUS_EVENT_COVER.Raise_Rcpt_Deletion_Event(
543 l_trx_sum_hist_rec.payment_schedule_id,
544 l_receipt_number,
545 l_receipt_date
546 ) ;
547
548 IF PG_DEBUG in ('Y', 'C') THEN
549 arp_standard.debug('arp_process_receipts.delete_cash_receipt()-');
550 END IF;
551
552 EXCEPTION
553 WHEN OTHERS THEN
554 IF PG_DEBUG in ('Y', 'C') THEN
555 arp_standard.debug('EXCEPTION: arp_process_receipts.delete_cash_receipts');
556 END IF;
557 RAISE;
558
559 END delete_cash_receipt;
560
561
562 /*===========================================================================+
563 | PROCEDURE |
564 | post_query_logic |
565 | |
566 | DESCRIPTION |
567 | Executes post-query logic for the ARXRWRCT.fmb form |
568 | SCOPE - PRIVATE |
569 | |
570 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
571 | |
572 | ARGUMENTS |
573 | IN: |
574 | OUT: |
575 | |
576 | RETURNS |
577 | |
578 | NOTES |
579 | |
580 | MODIFICATION HISTORY |
581 | |
582 | 21-FEB-95 OSTEINME created |
583 | 21-JUL-97 KLAWRANC Release 11. |
584 | Added cross currency logic. Should select |
585 | amount_applied_from when this is populated |
586 | otherwise use amount_applied. See notes. |
587 | Added the calculation and return of the total|
588 | exchange gain/loss at the header level. |
589 | Added cross currency apps flag as out NOCOPY |
590 | parameter. |
591 | 22-OCT-97 KLAWRANC Bug #550743. Changed query of applications. |
592 | For APP rows, don't include where confirmed |
593 | is 'N'. |
594 | 04-DEC-97 KLAWRANC Bug #591462. Removed distinct clause when |
595 | counting cash receipt history records. This |
596 | did not cater for the case where the cash |
597 | receipt record has been rate adjusted (the |
598 | receipt has not changed state but there is |
599 | more than one history record). |
600 | 10-MAR-98 KLAWRANC Bug #584086. Receipts Query Performance. |
601 | Added the selection and return of reversal |
602 | and confirmation details. These were |
603 | removed from the view and added to post query|
604 | for performance reasons. |
605 | Bug #584086. Added code to explicitly set |
606 | p_debit_memo to 'N' when no_data_found or |
607 | the receipt is not reversed. |
608 | |
609 | 20-APR-2000 J Rautiainen BR Implementation. Activity application of |
610 | type Short Term debt is considered as |
611 | applied amount. |
612 | 09-Oct-2000 S Nambiar Receipt write-off is considered as applied |
613 | But still we need to calculate write-off for |
614 | validation purpose |
615 | 22-DEC-2000 Yashaskar Bug # 1431322 : A check is made to see if the|
616 | Chargeback is posted . |
617 | 28-Mar-2001 S Nambiar Receipt chargeback is considered as applied |
618 | But still we need to calculate chargeback for |
619 | validation purpose |
620 | 02-DEC-2002 R Muthuraman Bug 2421800 : Reverted the fix for |
621 | bug 1431322. |
622 | 12-JUN-2003 J Beckett Bug 2821139 ACTIVITY is considered as applied|
623 | for exchange gain/loss calculation |
624 | 06-DEC-2003 P Pawar Bug 3252322 : Performance Issue. In procedure|
625 | post_query_logic, replaced |
626 | "ra.applied_payment_schedule_id = -6 " with |
627 | "ra.applied_payment_schedule_id+0 = -6 " |
628 | 02-FEB-2005 J Beckett Bug 4112494 CM refunds |
629 | 02-FEB-2005 J Pandey Bug 4166986 Credit Card Chargebacks added |
630 | p_cc_chargeback_amount in the parameter |
631 | 21-MAR-2005 J Pandey Bug 4166986 Credit Card Chargebacks amt |
632 | to be added to the amount_applied and in |
633 | logic preventing unapp/reversal of misc rct |
634 +===========================================================================*/
635
636 Procedure post_query_logic(
637 p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
638 p_receipt_type IN VARCHAR2,
639 p_reference_type IN VARCHAR2,
640 p_reference_id IN NUMBER,
641 p_std_reversal_possible OUT NOCOPY VARCHAR2,
642 p_apps_exist_flag OUT NOCOPY VARCHAR2,
643 p_rec_moved_state_flag OUT NOCOPY VARCHAR2,
644 p_amount_applied OUT NOCOPY NUMBER,
645 p_amount_unapplied OUT NOCOPY NUMBER,
646 p_write_off_amount OUT NOCOPY NUMBER,
647 p_cc_refund_amount OUT NOCOPY NUMBER,
648 p_cc_chargeback_amount OUT NOCOPY NUMBER,
649 p_chargeback_amount OUT NOCOPY NUMBER,
650 p_amount_on_account OUT NOCOPY NUMBER,
651 p_amount_in_claim OUT NOCOPY NUMBER,
652 p_prepayment_amount OUT NOCOPY NUMBER,
653 p_amount_unidentified OUT NOCOPY NUMBER,
654 p_discounts_earned OUT NOCOPY NUMBER,
655 p_discounts_unearned OUT NOCOPY NUMBER,
656 p_tot_exchange_gain_loss OUT NOCOPY NUMBER,
657 p_statement_number OUT NOCOPY VARCHAR2,
658 p_line_number OUT NOCOPY VARCHAR2,
659 p_statement_date OUT NOCOPY DATE,
660 p_reference_id_dsp OUT NOCOPY VARCHAR2,
661 p_cross_curr_apps_flag OUT NOCOPY VARCHAR2,
662 p_reversal_date IN DATE,
663 p_reversal_gl_date OUT NOCOPY DATE,
664 p_debit_memo OUT NOCOPY VARCHAR2,
665 p_debit_memo_ccid OUT NOCOPY NUMBER,
666 p_debit_memo_type OUT NOCOPY VARCHAR2,
667 p_debit_memo_number OUT NOCOPY VARCHAR2,
668 p_debit_memo_doc_number OUT NOCOPY NUMBER,
669 p_confirm_date OUT NOCOPY DATE,
670 p_confirm_gl_date OUT NOCOPY DATE
671
672 ) IS
673
674 l_apps_exist VARCHAR2(1);
675 l_rec_moved_state NUMBER;
676 l_amount_on_account NUMBER;
677 l_amount_in_claim NUMBER;
678 l_prepayment_amount NUMBER;
679 l_amount_applied NUMBER;
680 l_tot_exchange_gain_loss NUMBER;
681 l_dummy NUMBER;
682 l_cr_currency_code ar_cash_receipts.currency_code%TYPE;
683 l_status VARCHAR2(20);
684
685 BEGIN
686
687 IF PG_DEBUG in ('Y', 'C') THEN
688 arp_standard.debug('ARP_PROCESS_RCT_UTIL.post_query_logic()+');
689 arp_standard.debug('post_query_logic: ' || ' p_cr_id = ' || p_cr_id);
690 arp_standard.debug('post_query_logic: ' || ' p_type = ' || p_receipt_type);
691 END IF;
692
693 -- check if receipt has moved from creation state to a later state
694 /* Bug 2211303 Modified to SELECT count(distinct status) so that
695 the FLAG for Checking Receipt with changed states is SET
696 Properly . */
697
698 SELECT count(distinct status)
699 INTO l_rec_moved_state
700 FROM AR_CASH_RECEIPT_HISTORY
701 WHERE cash_receipt_id = p_cr_id;
702
703 IF (l_rec_moved_state > 1) THEN
704 p_rec_moved_state_flag := 'Y';
705 ELSE
706 p_rec_moved_state_flag := 'N';
707 END IF;
708
709 -- bug 16180140
710 SELECT status
711 INTO l_status
712 FROM AR_CASH_RECEIPT_HISTORY
713 WHERE cash_receipt_id = p_cr_id
714 AND current_record_flag = 'Y';
715
716 -- get the cash management items
717
718 /* bug4751467 -- added the call to cep_standard.init and replaced CE_STATEMENT_RECONCILIATIONS with
719 ce_statement_recon_gt_v */
720 -- bug 9020487
721 -- cep_standard.init_security;
722
723 SELECT
724 MAX(cb_sh.statement_number) statement_number,
725 MAX(cb_sl.line_number) line_number,
726 MAX(cb_sh.statement_date) statement_date
727 INTO
728 p_statement_number,
729 p_line_number,
730 p_statement_date
731 FROM
732 ce_statement_headers cb_sh,
733 ce_statement_lines cb_sl,
734 ce_statement_reconcils_ou cb_sr,
735 ar_cash_receipt_history crh_cb
736 WHERE
737 crh_cb.cash_receipt_id = p_cr_id
738 AND crh_cb.cash_receipt_history_id = cb_sr.reference_id (+)
739 AND nvl(crh_cb.current_record_flag,'N') = Decode(l_status,'REVERSED','Y',
740 nvl(crh_cb.current_record_flag,'N') ) -- bug 16180140
741 AND cb_sr.reference_type (+) = 'RECEIPT'
742 AND cb_sr.current_record_flag (+) = 'Y'
743 AND cb_sr.status_flag (+) = 'M'
744 AND cb_sr.statement_line_id = cb_sl.statement_line_id (+)
745 AND cb_sl.statement_header_id = cb_sh.statement_header_id (+);
746
747
748 -- for cash receipts, get the application amounts from
749 -- ar_receivable_applications
750
751 IF (p_receipt_type = 'MISC') THEN
752
753 p_apps_exist_flag := 'N';
754 p_std_reversal_possible := 'Y';
755
756 -- get reference number if necessary
757
758 IF (p_reference_type IS NOT NULL) THEN
759
760 IF (p_reference_type = 'REMITTANCE') THEN
761
762 SELECT name
763 INTO p_reference_id_dsp
764 FROM AR_BATCHES
765 WHERE BATCH_ID = p_reference_id;
766
767 ELSIF (p_reference_type = 'RECEIPT') THEN
768
769 SELECT receipt_number
770 INTO p_reference_id_dsp
771 FROM AR_CASH_RECEIPTS
772 WHERE cash_receipt_id = p_reference_id;
773
774 ELSIF (p_reference_type = 'PAYMENT_BATCH') THEN
775
776 SELECT checkrun_name
777 INTO p_reference_id_dsp
778 FROM AP_INVOICE_SELECTION_CRITERIA
779 WHERE CHECKRUN_ID = p_reference_id;
780
781 ELSIF (p_reference_type = 'PAYMENT') THEN
782
783 SELECT check_number
784 INTO p_reference_id_dsp
785 FROM AP_CHECKS
786 WHERE CHECK_ID = p_reference_id;
787
788 /* Bug 4122494 CM refunds */
789 ELSIF (p_reference_type = 'CREDIT_MEMO') THEN
790
791 SELECT trx_number
792 INTO p_reference_id_dsp
793 FROM RA_CUSTOMER_TRX
794 WHERE customer_trx_id = p_reference_id;
795
796 END IF;
797
798 END IF;
799
800 ELSE
801
802 -----------------------------------------------------------------------
803 -- For APP rows in receivable applications ...
804 --
805 -- Amount_applied stores the total amount of the application in the
806 -- currency of the transaction, i.e. the amount allocated to the
807 -- transaction. Also represents the receipt allocation for same
808 -- currency applications.
809 --
810 -- Amount_applied_from stores the total amount of the application in
811 -- the currency of the receipt, i.e. that portion of the receipt
812 -- allocated to the transaction. This is only populated for cross
813 -- currency applications.
814 --
815 -- As we are calculating the total amount applied in the context of
816 -- the receipt, we need to firstly select amount_applied_from (as if
817 -- populated, the application is cross currency and the receipt amount
818 -- is stored in this column), otherwise select amount_applied (as
819 -- the amount_applied_from must be null and the receipt amount is
820 -- stored in amount_applied).
821 -----------------------------------------------------------------------
822 /* 20-APR-2000 J Rautiainen BR Implementation
823 * Short Term Debt Activity application is considered as applied amount */
824
825 /* snambiar write-off amount is considered as applied. But for maximum
826 write-off amount on a receipts needs to be validated. So we are doing
827 a sum for the write-off amount with PS id -3 */
828
829 /* snambiar chargeback amount is considered as applied. */
830 /* Bug 2751910 Netting amount is considered as applied. */
831 /* Bug 2821139 Netting amount is considered as applied. */
832 /* Bug 2821139 ACTIVITY amount is considered as applied for exchange gain
833 loss calculation. */
834 /* jypandey cc_chargeback amount is considered as applied. */
835 /* Bug 4948423 Refund amount is considered as applied (-8) */
836
837 SELECT
838 SUM(DECODE(ra.status,
839 'APP',DECODE(ra.confirmed_flag,
840 'N', 0,
841 NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
842 'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
843 -2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
844 -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
845 -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
846 -6,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
847 -8,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
848 -9,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
849 DECODE(ra.receivables_trx_id,-16,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)))
850 ,0)) applied_amount,
851 SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
852 -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) write_off_amount,
853 SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
854 -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) chargeback_amount,
855 SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
856 -6,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) cc_refund_amount,
857 /* Bug 4166986 CC Chargeback */
858 SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
859 -9,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) cc_chargeback_amount,
860 SUM(DECODE(ra.status,'UNAPP',
861 NVL(ra.amount_applied,0),0)) unapplied_amount,
862 SUM(DECODE(ra.status,'ACC',
863 NVL(ra.amount_applied, 0),0)) on_account_amount,
864 SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
865 -4,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) claim_amount,
866 SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
867 -7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) prepayment_amount,
868 SUM(DECODE(ra.status,'UNID',
869 NVL(ra.amount_applied, 0),0)) unidentified_amount,
870 SUM(DECODE(ra.status,'APP',
871 NVL(ra.earned_discount_taken, 0),0)) discounts_earned,
872 SUM(DECODE(ra.status,'APP',
873 NVL(ra.unearned_discount_taken, 0),0)) discounts_unearned,
874 SUM(DECODE(ra.status,'APP',
875 NVL(ra.acctd_amount_applied_from - ra.acctd_amount_applied_to, 0),'ACTIVITY',
876 NVL(ra.acctd_amount_applied_from - ra.acctd_amount_applied_to, 0),0)) tot_exchange_gain_loss
877 INTO
878 l_amount_applied,
879 p_write_off_amount,
880 p_chargeback_amount,
881 p_cc_refund_amount,
882 p_cc_chargeback_amount,
883 p_amount_unapplied,
884 l_amount_on_account,
885 l_amount_in_claim,
886 l_prepayment_amount,
887 p_amount_unidentified,
888 p_discounts_earned,
889 p_discounts_unearned,
890 l_tot_exchange_gain_loss
891 FROM
892 ar_receivable_applications ra
893 WHERE
894 ra.cash_receipt_id = p_cr_id;
895
896 p_amount_on_account := l_amount_on_account;
897 p_amount_in_claim := l_amount_in_claim;
898 p_prepayment_amount := l_prepayment_amount;
899 p_amount_applied := l_amount_applied;
900 p_tot_exchange_gain_loss := l_tot_exchange_gain_loss;
901
902 /* 20-APR-2000 J Rautiainen BR Implementation
903 * Short Term Debt Activity application is considered as application */
904
905 -- Determine if the receipt has applications.
906 SELECT max(decode(ra.status, 'APP', 'Y',
907 'ACC', 'Y',
908 'OTHER ACC', 'Y',
909 'ACTIVITY', 'Y', 'N'))
910 INTO l_apps_exist
911 FROM ar_receivable_applications ra
912 WHERE ra.cash_receipt_id = p_cr_id
913 AND ra.reversal_gl_date is NULL;
914
915 p_apps_exist_flag := l_apps_exist;
916
917 -- Determine if the receipt currently has a cross currency
918 -- application(s). No point doint the select if it doesn't
919 -- have applications in the first place.
920 BEGIN
921 IF l_apps_exist = 'Y' THEN
922
923 SELECT cr.currency_code
924 INTO l_cr_currency_code
925 FROM ar_cash_receipts cr
926 WHERE cr.cash_receipt_id = p_cr_id
927 AND exists
928 (select 1
929 from ar_receivable_applications ra,
930 ar_payment_schedules ps
931 where ra.cash_receipt_id = cr.cash_receipt_id
932 and ra.applied_payment_schedule_id = ps.payment_schedule_id
933 and ps.invoice_currency_code <> cr.currency_code
934 and ra.reversal_gl_date is NULL
935 and ra.applied_payment_schedule_id NOT IN (-1, -3)); /*Bug 11806223*/
936
937 END IF;
938 EXCEPTION
939 WHEN NO_DATA_FOUND THEN
940 l_cr_currency_code := NULL;
941 END;
942
943 IF l_cr_currency_code is not NULL THEN
944 p_cross_curr_apps_flag := 'Y';
945 ELSE
946 p_cross_curr_apps_flag := 'N';
947 END IF;
948
949 /* --------------------------------------------------------------------
950 * Check if a 'CB' was created against this PMT to be reversed.
951 * Check if there are any PMT, ADJ, or CM or CB against this 'CB' records
952 * in AR_PAYMENT_SCHEDULES table. Also check to see if the CB has
953 * already been posted. If any of these 2 conditions is TRUE, then
954 * PMT can only be reversed using DM Reversal.
955 *
956 * Make sure that the adj which is automatically created against the CB
957 * associated with the receipt being reversed does not get caught in
958 * the SQL. For such an adj, the adj.receivables_trx_id = -12
959 * -------------------------------------------------------------------- */
960
961
962 SELECT COUNT(payment_schedule_id)
963 INTO l_dummy
964 FROM ar_payment_schedules ps,
965 ra_cust_trx_line_gl_dist rctlg
966 WHERE ps.associated_cash_receipt_id = p_cr_id
967 AND ps.class = 'CB'
968 AND ps.customer_trx_id = rctlg.customer_trx_id
969 AND ( nvl(ps.amount_applied, 0) <> 0
970 OR nvl(ps.amount_credited, 0) <> 0
971 OR 0 <> ( SELECT sum(adj.amount)
972 FROM ar_adjustments adj
973 WHERE adj.payment_schedule_id =
974 ps.payment_schedule_id
975 AND adj.receivables_trx_id <> -12
976 )
977 );
978
979 IF (l_dummy > 0) THEN
980 p_std_reversal_possible := 'N';
981 ELSE
982 p_std_reversal_possible := 'Y';
983 END IF;
984
985 IF p_std_reversal_possible = 'Y' THEN
986 BEGIN
987 /** If the -ve Miscellaneous receipt of CC Refund is already remitted or
988 ** cleared then do not allow the reversal or unapplication ***/
989
990 /* Added CC chargeback -ve misc receipt too for this condition */
991
992 SELECT 1
993 INTO l_dummy
994 FROM dual
995 WHERE
996 EXISTS
997 ( SELECT 1
998 FROM AR_CASH_RECEIPT_HISTORY crh, ar_receivable_applications ra
999 WHERE crh.cash_receipt_id = ra.application_ref_id
1000 AND ra.cash_receipt_id = p_cr_id
1001 AND ra.applied_payment_schedule_id+0 in (-6 , -9)
1002 AND ra.application_ref_type = 'MISC_RECEIPT'
1003 AND crh.status IN ('REMITTED', 'CLEARED'));
1004 --
1005 p_std_reversal_possible := 'N';
1006 EXCEPTION
1007 WHEN NO_DATA_FOUND THEN
1008 NULL;
1009 WHEN OTHERS THEN
1010 RAISE;
1011 END;
1012
1013 END IF;
1014
1015 -- Get Confirmation Details.
1016 -- This query was removed from the view to speed up
1017 -- performance.
1018
1019 BEGIN
1020 select crh_conf.trx_date,
1021 crh_conf.gl_date
1022 into p_confirm_date,
1023 p_confirm_gl_date
1024 from ar_cash_receipt_history crh_conf
1025 where crh_conf.cash_receipt_id = p_cr_id
1026 and crh_conf.status = 'CONFIRMED'
1027 and not exists (
1028 select cash_receipt_history_id
1029 from ar_cash_receipt_history crh2
1030 where crh2.status = 'CONFIRMED'
1031 and crh2.cash_receipt_id = p_cr_id
1032 and crh2.cash_receipt_history_id > crh_conf.cash_receipt_history_id);
1033 EXCEPTION
1034
1035 WHEN no_data_found THEN
1036
1037 p_confirm_date := NULL;
1038 p_confirm_gl_date := NULL;
1039 END;
1040
1041 END IF;
1042
1043 ---------------------------------------------------------
1044 -- If the Receipt has been Reversed then get DM Reversal
1045 -- details.
1046 -- This query was removed from the view to speed up
1047 -- performance.
1048 ---------------------------------------------------------
1049
1050 IF p_reversal_date is not null THEN
1051
1052 BEGIN
1053
1054 /*Bug3185358 Changed the ps_dm.gl_date to NVL(dm_gld.gl_Date,ct.gl_Date)
1055 and removed reference to ar_payment_schedultes as the record may not be
1056 there while the dm is incompleted */
1057
1058 select NVL(dm_gld.gl_date,ct_dm.trx_date),
1059 'Y',
1060 dm_gld.code_combination_id,
1061 ctt_dm.name,
1062 ct_dm.trx_number,
1063 ct_dm.doc_sequence_value
1064 into p_reversal_gl_date,
1065 p_debit_memo,
1066 p_debit_memo_ccid,
1067 p_debit_memo_type,
1068 p_debit_memo_number,
1069 p_debit_memo_doc_number
1070 from ra_cust_trx_types ctt_dm,
1071 ra_customer_trx ct_dm,
1072 ra_cust_trx_line_gl_dist dm_gld
1073 where ct_dm.reversed_cash_receipt_id = p_cr_id
1074 and ct_dm.cust_trx_type_id = ctt_dm.cust_trx_type_id
1075 and ct_dm.customer_trx_id = dm_gld.customer_trx_id
1076 and dm_gld.account_class = 'REC'
1077 and dm_gld.latest_rec_flag = 'Y';
1078
1079 EXCEPTION
1080
1081 WHEN no_data_found THEN
1082
1083 select crh_current.gl_date
1084 into p_reversal_gl_date
1085 from ar_cash_receipt_history crh_current
1086 where crh_current.cash_receipt_id = p_cr_id
1087 and crh_current.current_record_flag = 'Y';
1088
1089 p_debit_memo := 'N';
1090 p_debit_memo_ccid := NULL;
1091 p_debit_memo_type := NULL;
1092 p_debit_memo_number := NULL;
1093 p_debit_memo_doc_number := NULL;
1094
1095 END;
1096
1097 ELSE
1098
1099 p_debit_memo := 'N';
1100
1101 END IF;
1102
1103 IF PG_DEBUG in ('Y', 'C') THEN
1104 arp_standard.debug('ARP_PROCESS_RCT_UTIL.post_query_logic()+');
1105 END IF;
1106
1107 END post_query_logic;
1108
1109 /*===========================================================================+
1110 | PROCEDURE |
1111 | set_posted_flag |
1112 | |
1113 | DESCRIPTION |
1114 | Deterimines if a cash receipt has been posted |
1115 | |
1116 | SCOPE - PRIVATE |
1117 | |
1118 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1119 | arp_util.debug |
1120 | |
1121 | ARGUMENTS |
1122 | IN: |
1123 | p_cash_receipt_id |
1124 | OUT: |
1125 | p_posted_flag |
1126 | |
1127 | RETURNS : NONE |
1128 | |
1129 | NOTES |
1130 | |
1131 | MODIFICATION HISTORY |
1132 | |
1133 | 28-NOV-98 Debbie Sue Jancis created |
1134 | 03-FEB-99 Debbie Sue Jancis modified declaration of |
1135 | l_posted_flag from varchar to |
1136 | varchar2. |
1137 +===========================================================================*/
1138
1139 PROCEDURE set_posted_flag( p_cash_receipt_id IN number,
1140 p_posted_flag OUT NOCOPY BOOLEAN) IS
1141 l_posted_flag varchar2(2);
1142
1143 BEGIN
1144 IF PG_DEBUG in ('Y', 'C') THEN
1145 arp_util.debug('ARP_PROC_RCT_UTIL.set_posted_flag()+');
1146 END IF;
1147
1148 SELECT decode ( max(dummy), NULL, 'N','Y')
1149 INTO l_posted_flag
1150 FROM dual
1151 WHERE EXISTS
1152 (SELECT 'posted distribution exists'
1153 FROM ar_cash_receipt_history
1154 WHERE cash_receipt_id = p_cash_receipt_id
1155 AND gl_posted_date IS NOT NULL);
1156
1157 IF (l_posted_flag ='Y')
1158 THEN
1159 IF PG_DEBUG in ('Y', 'C') THEN
1160 arp_util.debug('set_posted_flag: ' || 'flag = true +');
1161 END IF;
1162 p_posted_flag := TRUE;
1163 ELSE
1164 IF PG_DEBUG in ('Y', 'C') THEN
1165 arp_util.debug('set_posted_flag: ' || 'flag = false +');
1166 END IF;
1167 p_posted_flag := FALSE;
1168
1169 END IF;
1170
1171 IF PG_DEBUG in ('Y', 'C') THEN
1172 arp_util.debug('ARP_PROC_RCT_UTIL.set_posted_flag()-');
1173 END IF;
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 IF PG_DEBUG in ('Y', 'C') THEN
1178 arp_util.debug('EXCEPTION: ARP_PROC_RCT_UTIL.set_posted_flag()');
1179 END IF;
1180 RAISE;
1181
1182 END set_posted_flag;
1183
1184
1185 --Bug 5033971
1186 PROCEDURE Delete_Transaction_Extension(
1187
1188 -- ***** Standard API parameters *****
1189 p_api_version IN NUMBER ,
1190 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1191 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1192 x_return_status OUT NOCOPY VARCHAR2 ,
1193 x_msg_count OUT NOCOPY NUMBER ,
1194 x_msg_data OUT NOCOPY VARCHAR2 ,
1195
1196 -- ***** Receipt Header information parameters *****
1197 p_org_id IN NUMBER DEFAULT NULL ,
1198 p_cust_Account_id IN NUMBER DEFAULT NULL ,
1199 p_account_site_use_id IN NUMBER DEFAULT NULL ,
1200 p_payment_trxn_extn_id IN IBY_TRXN_EXTENSIONS_V.TRXN_EXTENSION_ID%TYPE )
1201 IS
1202 l_payer_rec IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
1203 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.trxnextension_rec_type;
1204 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
1205 Begin
1206 arp_standard.debug('ARP_PROCESS_RCTS.Delete_Transaction_Extension()+ ');
1207 x_msg_count := NULL;
1208 x_msg_data := NULL;
1209 x_return_status := FND_API.G_RET_STS_SUCCESS;
1210 l_payer_rec.party_id := arp_trx_defaults_3.get_party_Id(p_cust_Account_id);
1211 l_payer_rec.payment_function := 'CUSTOMER_PAYMENT';
1212 l_payer_rec.org_type := 'OPERATING_UNIT';
1213 l_payer_rec.cust_account_id := p_cust_Account_id;
1214 l_payer_rec.org_id := P_ORG_ID;
1215 l_payer_rec.account_site_id := p_account_site_use_id;
1216
1217 /*-------------------------+
1218 | Call the IBY API |
1219 +-------------------------*/
1220 arp_standard.debug('Call TO IBY API ()+ ');
1221
1222 IBY_FNDCPT_TRXN_PUB.delete_transaction_extension(
1223 p_api_version => 1.0,
1224 p_init_msg_list => p_init_msg_list,
1225 p_commit => p_commit,
1226 x_return_status => x_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 p_payer => l_payer_rec,
1230 p_payer_equivalency => 'UPWARD',
1231 p_entity_id => p_payment_trxn_extn_id,
1232 x_response => l_response);
1233
1234 IF x_return_status = fnd_api.g_ret_sts_success
1235 THEN
1236 arp_standard.debug('Payment_Trxn_Extension_Id : ' || p_payment_trxn_extn_id);
1237 ElSIF l_response.result_code= 'EXTENSION_NOT_UPDATEABLE' and
1238 l_response.result_Category = 'INCORRECT_FLOW'
1239 THEN
1240 fnd_message.set_name('AR','AR_AUTH_RCT_NO_DELETE');
1241 app_exception.raise_exception;
1242 Else
1243 arp_standard.debug('Errors Reported by IBY API in ARP_PROCESS_RCTS.Delete Transaction Extension ');
1244 raise fnd_api.g_exc_unexpected_error;
1245 END IF;
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 arp_standard.debug('exception in ARP_PROCESS_RCTS.Delete_Transaction_Extension');
1249 RAISE;
1250 END Delete_Transaction_Extension;
1251
1252
1253
1254
1255
1256 END ARP_PROCESS_RCTS;