[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete_cash_receipt |
| |
| DESCRIPTION |
| Entity handler that delete cash receipts. |
| |
| SCOPE - PUBLIC |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 30-NOV-95 OSTEINME created |
| 28-Dec-98 DJANCIS added call to set posted flag to deterime if |
| receipt was posted before deleting it |
| 08-Nov-01 DJANCIS Modified for mrc trigger elimination project |
| added call to ar_mrc_engine for deletes to |
| ar_payment_schedules |
+===========================================================================*/
PROCEDURE delete_cash_receipt(
p_cash_receipt_id IN NUMBER,
p_batch_id IN NUMBER) IS
CURSOR get_app_C IS
select app.receivable_application_id app_id
from ar_receivable_applications app
where app.cash_receipt_id = p_cash_receipt_id
and nvl(app.confirmed_flag,'Y') = 'Y' --confirmed records have accounting only
and exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id)
order by decode(app.status,
'UNAPP',1, --Delete UNAPP related accounting first as record may be paired
2);
SELECT payment_schedule_id,
invoice_currency_code,
due_date,
amount_in_dispute,
amount_due_original,
amount_due_remaining,
amount_adjusted,
cash_receipt_id,
customer_id,
customer_site_use_id,
trx_date
FROM ar_payment_schedules
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT receipt_number,
receipt_date
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
arp_standard.debug('arp_process_receipts.delete_cash_receipt()+');
arp_standard.debug('delete_cash_receipt: ' || 'posted flag = true ');
arp_cash_receipts_pkg.delete_p(p_cash_receipt_id);
DELETE AR_DISTRIBUTIONS
WHERE source_table = 'CRH'
AND source_id IN (
SELECT cash_receipt_history_id
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id)
RETURNING line_id
BULK COLLECT INTO l_ar_dist_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_ar_dist_key_value_list);
ARP_XLA_EVENTS.delete_event( p_document_id => p_cash_receipt_id,
p_doc_table => 'CRH');
arp_cr_history_pkg.delete_p_cr(p_cash_receipt_id);
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
DELETE AR_RECEIVABLE_APPLICATIONS
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING receivable_application_id
BULK COLLECT INTO l_rec_app_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
p_mode => 'BATCH',
p_key_value_list => l_rec_app_key_value_list);
AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
l_history_id,
'PMT',
'DELETE_PMT');
DELETE AR_PAYMENT_SCHEDULES
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING payment_schedule_id
BULK COLLECT INTO l_ar_ps_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_PAYMENT_SCHEDULES',
p_mode => 'BATCH',
p_key_value_list => l_ar_ps_key_value_list);
arp_rw_batches_check_pkg.update_batch_status(
p_batch_id);
arp_standard.debug('arp_process_receipts.delete_cash_receipt()-');
arp_standard.debug('EXCEPTION: arp_process_receipts.delete_cash_receipts');
END delete_cash_receipt;
| Added cross currency logic. Should select |
| amount_applied_from when this is populated |
| otherwise use amount_applied. See notes. |
| Added the calculation and return of the total|
| exchange gain/loss at the header level. |
| Added cross currency apps flag as out NOCOPY |
| parameter. |
| 22-OCT-97 KLAWRANC Bug #550743. Changed query of applications. |
| For APP rows, don't include where confirmed |
| is 'N'. |
| 04-DEC-97 KLAWRANC Bug #591462. Removed distinct clause when |
| counting cash receipt history records. This |
| did not cater for the case where the cash |
| receipt record has been rate adjusted (the |
| receipt has not changed state but there is |
| more than one history record). |
| 10-MAR-98 KLAWRANC Bug #584086. Receipts Query Performance. |
| Added the selection and return of reversal |
| and confirmation details. These were |
| removed from the view and added to post query|
| for performance reasons. |
| Bug #584086. Added code to explicitly set |
| p_debit_memo to 'N' when no_data_found or |
| the receipt is not reversed. |
| |
| 20-APR-2000 J Rautiainen BR Implementation. Activity application of |
| type Short Term debt is considered as |
| applied amount. |
| 09-Oct-2000 S Nambiar Receipt write-off is considered as applied |
| But still we need to calculate write-off for |
| validation purpose |
| 22-DEC-2000 Yashaskar Bug # 1431322 : A check is made to see if the|
| Chargeback is posted . |
| 28-Mar-2001 S Nambiar Receipt chargeback is considered as applied |
| But still we need to calculate chargeback for |
| validation purpose |
| 02-DEC-2002 R Muthuraman Bug 2421800 : Reverted the fix for |
| bug 1431322. |
| 12-JUN-2003 J Beckett Bug 2821139 ACTIVITY is considered as applied|
| for exchange gain/loss calculation |
| 06-DEC-2003 P Pawar Bug 3252322 : Performance Issue. In procedure|
| post_query_logic, replaced |
| "ra.applied_payment_schedule_id = -6 " with |
| "ra.applied_payment_schedule_id+0 = -6 " |
| 02-FEB-2005 J Beckett Bug 4112494 CM refunds |
| 02-FEB-2005 J Pandey Bug 4166986 Credit Card Chargebacks added |
| p_cc_chargeback_amount in the parameter |
| 21-MAR-2005 J Pandey Bug 4166986 Credit Card Chargebacks amt |
| to be added to the amount_applied and in |
| logic preventing unapp/reversal of misc rct |
+===========================================================================*/
Procedure post_query_logic(
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_receipt_type IN VARCHAR2,
p_reference_type IN VARCHAR2,
p_reference_id IN NUMBER,
p_std_reversal_possible OUT NOCOPY VARCHAR2,
p_apps_exist_flag OUT NOCOPY VARCHAR2,
p_rec_moved_state_flag OUT NOCOPY VARCHAR2,
p_amount_applied OUT NOCOPY NUMBER,
p_amount_unapplied OUT NOCOPY NUMBER,
p_write_off_amount OUT NOCOPY NUMBER,
p_cc_refund_amount OUT NOCOPY NUMBER,
p_cc_chargeback_amount OUT NOCOPY NUMBER,
p_chargeback_amount OUT NOCOPY NUMBER,
p_amount_on_account OUT NOCOPY NUMBER,
p_amount_in_claim OUT NOCOPY NUMBER,
p_prepayment_amount OUT NOCOPY NUMBER,
p_amount_unidentified OUT NOCOPY NUMBER,
p_discounts_earned OUT NOCOPY NUMBER,
p_discounts_unearned OUT NOCOPY NUMBER,
p_tot_exchange_gain_loss OUT NOCOPY NUMBER,
p_statement_number OUT NOCOPY VARCHAR2,
p_line_number OUT NOCOPY VARCHAR2,
p_statement_date OUT NOCOPY DATE,
p_reference_id_dsp OUT NOCOPY VARCHAR2,
p_cross_curr_apps_flag OUT NOCOPY VARCHAR2,
p_reversal_date IN DATE,
p_reversal_gl_date OUT NOCOPY DATE,
p_debit_memo OUT NOCOPY VARCHAR2,
p_debit_memo_ccid OUT NOCOPY NUMBER,
p_debit_memo_type OUT NOCOPY VARCHAR2,
p_debit_memo_number OUT NOCOPY VARCHAR2,
p_debit_memo_doc_number OUT NOCOPY NUMBER,
p_confirm_date OUT NOCOPY DATE,
p_confirm_gl_date OUT NOCOPY DATE
) IS
l_apps_exist VARCHAR2(1);
/* Bug 2211303 Modified to SELECT count(distinct status) so that
the FLAG for Checking Receipt with changed states is SET
Properly . */
SELECT count(distinct status)
INTO l_rec_moved_state
FROM AR_CASH_RECEIPT_HISTORY
WHERE cash_receipt_id = p_cr_id;
SELECT
MAX(cb_sh.statement_number) statement_number,
MAX(cb_sl.line_number) line_number,
MAX(cb_sh.statement_date) statement_date
INTO
p_statement_number,
p_line_number,
p_statement_date
FROM
ce_statement_headers cb_sh,
ce_statement_lines cb_sl,
ce_statement_recon_gt_v cb_sr,
ar_cash_receipt_history crh_cb
WHERE
crh_cb.cash_receipt_id = p_cr_id
AND crh_cb.cash_receipt_history_id = cb_sr.reference_id (+)
AND cb_sr.reference_type (+) = 'RECEIPT'
AND cb_sr.current_record_flag (+) = 'Y'
AND cb_sr.status_flag (+) = 'M'
AND cb_sr.statement_line_id = cb_sl.statement_line_id (+)
AND cb_sl.statement_header_id = cb_sh.statement_header_id (+);
SELECT name
INTO p_reference_id_dsp
FROM AR_BATCHES
WHERE BATCH_ID = p_reference_id;
SELECT receipt_number
INTO p_reference_id_dsp
FROM AR_CASH_RECEIPTS
WHERE cash_receipt_id = p_reference_id;
SELECT checkrun_name
INTO p_reference_id_dsp
FROM AP_INVOICE_SELECTION_CRITERIA
WHERE CHECKRUN_ID = p_reference_id;
SELECT check_number
INTO p_reference_id_dsp
FROM AP_CHECKS
WHERE CHECK_ID = p_reference_id;
SELECT trx_number
INTO p_reference_id_dsp
FROM RA_CUSTOMER_TRX
WHERE customer_trx_id = p_reference_id;
SELECT
SUM(DECODE(ra.status,
'APP',DECODE(ra.confirmed_flag,
'N', 0,
NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
-2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
-3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
-5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
-6,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
-8,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
-9,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
DECODE(ra.receivables_trx_id,-16,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)))
,0)) applied_amount,
SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
-3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) write_off_amount,
SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
-5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) chargeback_amount,
SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
-6,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) cc_refund_amount,
/* Bug 4166986 CC Chargeback */
SUM(DECODE(ra.status,'ACTIVITY',DECODE(applied_payment_schedule_id,
-9,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) cc_chargeback_amount,
SUM(DECODE(ra.status,'UNAPP',
NVL(ra.amount_applied,0),0)) unapplied_amount,
SUM(DECODE(ra.status,'ACC',
NVL(ra.amount_applied, 0),0)) on_account_amount,
SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
-4,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) claim_amount,
SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
-7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) prepayment_amount,
SUM(DECODE(ra.status,'UNID',
NVL(ra.amount_applied, 0),0)) unidentified_amount,
SUM(DECODE(ra.status,'APP',
NVL(ra.earned_discount_taken, 0),0)) discounts_earned,
SUM(DECODE(ra.status,'APP',
NVL(ra.unearned_discount_taken, 0),0)) discounts_unearned,
SUM(DECODE(ra.status,'APP',
NVL(ra.acctd_amount_applied_from - ra.acctd_amount_applied_to, 0),'ACTIVITY',
NVL(ra.acctd_amount_applied_from - ra.acctd_amount_applied_to, 0),0)) tot_exchange_gain_loss
INTO
l_amount_applied,
p_write_off_amount,
p_chargeback_amount,
p_cc_refund_amount,
p_cc_chargeback_amount,
p_amount_unapplied,
l_amount_on_account,
l_amount_in_claim,
l_prepayment_amount,
p_amount_unidentified,
p_discounts_earned,
p_discounts_unearned,
l_tot_exchange_gain_loss
FROM
ar_receivable_applications ra
WHERE
ra.cash_receipt_id = p_cr_id;
SELECT max(decode(ra.status, 'APP', 'Y',
'ACC', 'Y',
'OTHER ACC', 'Y',
'ACTIVITY', 'Y', 'N'))
INTO l_apps_exist
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cr_id
AND ra.reversal_gl_date is NULL;
SELECT cr.currency_code
INTO l_cr_currency_code
FROM ar_cash_receipts cr
WHERE cr.cash_receipt_id = p_cr_id
AND exists
(select 1
from ar_receivable_applications ra,
ar_payment_schedules ps
where ra.cash_receipt_id = cr.cash_receipt_id
and ra.applied_payment_schedule_id = ps.payment_schedule_id
and ps.invoice_currency_code <> cr.currency_code
and ra.reversal_gl_date is NULL
and ra.applied_payment_schedule_id <> -1);
SELECT COUNT(payment_schedule_id)
INTO l_dummy
FROM ar_payment_schedules ps,
ra_cust_trx_line_gl_dist rctlg
WHERE ps.associated_cash_receipt_id = p_cr_id
AND ps.class = 'CB'
AND ps.customer_trx_id = rctlg.customer_trx_id
AND ( nvl(ps.amount_applied, 0) <> 0
OR nvl(ps.amount_credited, 0) <> 0
OR 0 <> ( SELECT sum(adj.amount)
FROM ar_adjustments adj
WHERE adj.payment_schedule_id =
ps.payment_schedule_id
AND adj.receivables_trx_id <> -12
)
);
SELECT 1
INTO l_dummy
FROM dual
WHERE
EXISTS
( SELECT 1
FROM AR_CASH_RECEIPT_HISTORY crh, ar_receivable_applications ra
WHERE crh.cash_receipt_id = ra.application_ref_id
AND ra.cash_receipt_id = p_cr_id
AND ra.applied_payment_schedule_id+0 in (-6 , -9)
AND ra.application_ref_type = 'MISC_RECEIPT'
AND crh.status IN ('REMITTED', 'CLEARED'));
select crh_conf.trx_date,
crh_conf.gl_date
into p_confirm_date,
p_confirm_gl_date
from ar_cash_receipt_history crh_conf
where crh_conf.cash_receipt_id = p_cr_id
and crh_conf.status = 'CONFIRMED'
and not exists (
select cash_receipt_history_id
from ar_cash_receipt_history crh2
where crh2.status = 'CONFIRMED'
and crh2.cash_receipt_id = p_cr_id
and crh2.cash_receipt_history_id > crh_conf.cash_receipt_history_id);
select NVL(dm_gld.gl_date,ct_dm.trx_date),
'Y',
dm_gld.code_combination_id,
ctt_dm.name,
ct_dm.trx_number,
ct_dm.doc_sequence_value
into p_reversal_gl_date,
p_debit_memo,
p_debit_memo_ccid,
p_debit_memo_type,
p_debit_memo_number,
p_debit_memo_doc_number
from ra_cust_trx_types ctt_dm,
ra_customer_trx ct_dm,
ra_cust_trx_line_gl_dist dm_gld
where ct_dm.reversed_cash_receipt_id = p_cr_id
and ct_dm.cust_trx_type_id = ctt_dm.cust_trx_type_id
and ct_dm.customer_trx_id = dm_gld.customer_trx_id
and dm_gld.account_class = 'REC'
and dm_gld.latest_rec_flag = 'Y';
select crh_current.gl_date
into p_reversal_gl_date
from ar_cash_receipt_history crh_current
where crh_current.cash_receipt_id = p_cr_id
and crh_current.current_record_flag = 'Y';
SELECT decode ( max(dummy), NULL, 'N','Y')
INTO l_posted_flag
FROM dual
WHERE EXISTS
(SELECT 'posted distribution exists'
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND gl_posted_date IS NOT NULL);
PROCEDURE Delete_Transaction_Extension(
-- ***** Standard API parameters *****
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
-- ***** Receipt Header information parameters *****
p_org_id IN NUMBER DEFAULT NULL ,
p_cust_Account_id IN NUMBER DEFAULT NULL ,
p_account_site_use_id IN NUMBER DEFAULT NULL ,
p_payment_trxn_extn_id IN IBY_TRXN_EXTENSIONS_V.TRXN_EXTENSION_ID%TYPE )
IS
l_payer_rec IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
arp_standard.debug('ARP_PROCESS_RCTS.Delete_Transaction_Extension()+ ');
IBY_FNDCPT_TRXN_PUB.delete_transaction_extension(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer_rec,
p_payer_equivalency => 'UPWARD',
p_entity_id => p_payment_trxn_extn_id,
x_response => l_response);
ElSIF l_response.result_code= 'EXTENSION_NOT_UPDATEABLE' and
l_response.result_Category = 'INCORRECT_FLOW'
THEN
fnd_message.set_name('AR','AR_AUTH_RCT_NO_DELETE');
arp_standard.debug('Errors Reported by IBY API in ARP_PROCESS_RCTS.Delete Transaction Extension ');
arp_standard.debug('exception in ARP_PROCESS_RCTS.Delete_Transaction_Extension');
END Delete_Transaction_Extension;