[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pg_update_claim_amount NUMBER; /* Bug 4170060 for rct to rct applications */
select nvl(RECEIPT_CREATION_RULE_CODE,'MANUAL')
into l_rec_creation_rule_code
from AR_RECEIPT_METHODS
where receipt_method_id = l_receipt_method_id;
arp_proc_receipts2.insert_cash_receipt(
l_currency_code,
l_amount,
l_customer_id,
l_receipt_number,
l_receipt_date,
l_gl_date,
l_maturity_date,
p_comments,
l_exchange_rate_type,
l_exchange_rate,
l_exchange_rate_date,
NULL, --batch_id
l_attribute_rec.attribute_category,
l_attribute_rec.attribute1,
l_attribute_rec.attribute2,
l_attribute_rec.attribute3,
l_attribute_rec.attribute4,
l_attribute_rec.attribute5,
l_attribute_rec.attribute6,
l_attribute_rec.attribute7,
l_attribute_rec.attribute8,
l_attribute_rec.attribute9,
l_attribute_rec.attribute10,
l_attribute_rec.attribute11,
l_attribute_rec.attribute12,
l_attribute_rec.attribute13,
l_attribute_rec.attribute14,
l_attribute_rec.attribute15,
l_override_remit_account_flag,
l_remit_bank_acct_use_id ,
l_customer_bank_account_id ,
l_customer_site_use_id,
l_customer_receipt_reference ,
l_factor_discount_amount ,
l_deposit_date,
l_receipt_method_id,
l_doc_sequence_value,
l_doc_sequence_id,
l_ussgl_transaction_code ,
NULL, --Vat_tax_id
l_anticipated_clearing_date ,
l_customer_bank_branch_id,
l_postmark_date,
l_global_attribute_rec.global_attribute1,
l_global_attribute_rec.global_attribute2,
l_global_attribute_rec.global_attribute3,
l_global_attribute_rec.global_attribute4,
l_global_attribute_rec.global_attribute5,
l_global_attribute_rec.global_attribute6,
l_global_attribute_rec.global_attribute7,
l_global_attribute_rec.global_attribute8,
l_global_attribute_rec.global_attribute9,
l_global_attribute_rec.global_attribute10,
l_global_attribute_rec.global_attribute11,
l_global_attribute_rec.global_attribute12,
l_global_attribute_rec.global_attribute13,
l_global_attribute_rec.global_attribute14,
l_global_attribute_rec.global_attribute15,
l_global_attribute_rec.global_attribute16,
l_global_attribute_rec.global_attribute17,
l_global_attribute_rec.global_attribute18,
l_global_attribute_rec.global_attribute19,
l_global_attribute_rec.global_attribute20,
l_global_attribute_rec.global_attribute_category,
p_issuer_name,
trunc(p_issue_date), /* Bug fix 3135407 */
p_issuer_bank_branch_id,
null, -- application_notes
l_cr_id,
l_ps_id,
l_row_id,
'RAPI',
p_api_version
,p_called_from
,l_legal_entity_id /* R12 LE updtake */
,l_payment_trxn_extension_id /* payment uptake */
);
FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ARP_PROC_RECEIPTS2.INSERT_CASH_RECEIPT : '||SQLERRM);
select COUNT(*) into l_gt from ra_customer_trx_lines_gt where customer_trx_id=p_customer_trx_id;
delete FROM ra_customer_trx_lines_gt where customer_trx_id=p_customer_trx_id;
select count(1) into l_count from ar_llca_trx_errors_gt
where customer_trx_id = p_customer_trx_id;
ar_receipt_val_pvt.validate_llca_insert_ad(
p_cash_receipt_id => l_cash_receipt_id
,p_customer_trx_id => l_customer_trx_id
,p_customer_trx_line_id => l_customer_trx_line_id
,p_cr_unapp_amount => l_cr_unapp_amount
,p_llca_type => l_llca_type
,p_group_id => l_group_id
,p_line_amount => l_line_amount
,p_tax_amount => l_tax_amount
,p_freight_amount => l_freight_amount
,p_charges_amount => l_charges_amount
,p_line_discount => l_line_discount
,p_tax_discount => l_tax_discount
,p_freight_discount => l_freight_discount
,p_amount_applied => l_amount_applied
,p_amount_applied_from => l_amount_applied_from
,p_trans_to_receipt_rate => l_trans_to_receipt_rate
,p_invoice_currency_code => l_trx_currency_code
,p_receipt_currency_code => l_cr_currency_code
,p_earned_discount => l_discount_earned_allowed
,p_unearned_discount => l_discount_unearned
,p_max_discount => l_discount_max_allowed
,p_line_items_original => l_line_items_original
,p_line_items_remaining => l_line_items_remaining
,p_tax_original => l_tax_original
,p_tax_remaining => l_tax_remaining
,p_freight_original => l_freight_original
,p_freight_remaining => l_freight_remaining
,p_rec_charges_charged => l_rec_charges_charged
,p_rec_charges_remaining => l_rec_charges_remaining
,p_attribute_category => l_line_attribute_rec.attribute_category
,p_attribute1 => l_line_attribute_rec.attribute1
,p_attribute2 => l_line_attribute_rec.attribute2
,p_attribute3 => l_line_attribute_rec.attribute3
,p_attribute4 => l_line_attribute_rec.attribute4
,p_attribute5 => l_line_attribute_rec.attribute5
,p_attribute6 => l_line_attribute_rec.attribute6
,p_attribute7 => l_line_attribute_rec.attribute7
,p_attribute8 => l_line_attribute_rec.attribute8
,p_attribute9 => l_line_attribute_rec.attribute9
,p_attribute10 => l_line_attribute_rec.attribute10
,p_attribute11 => l_line_attribute_rec.attribute11
,p_attribute12 => l_line_attribute_rec.attribute12
,p_attribute13 => l_line_attribute_rec.attribute13
,p_attribute14 => l_line_attribute_rec.attribute14
,p_attribute15 => l_line_attribute_rec.attribute15
,p_comments => p_comments
,p_return_status => l_llca_return_status
,p_msg_count => l_llca_msg_count
,p_msg_data => l_llca_msg_data
);
ar_receipt_val_pvt.validate_llca_insert_app(
p_cash_receipt_id => l_cash_receipt_id
,p_customer_trx_id => l_customer_trx_id
,p_disc_earn_allowed => l_discount_earned_allowed
,p_disc_max_allowed => l_discount_max_allowed
,p_return_status => l_llca_app_return_status
,p_msg_count => l_llca_app_msg_count
,p_msg_data => l_llca_app_msg_data
);
update ar_activity_details
set source_table = 'RA',
source_id = llca_ra_rec.receivable_application_id
where source_id is null
and nvl(current_activity_flag, 'Y') = 'Y' -- Bug 7241111
and cash_receipt_id = l_cash_receipt_id
and customer_trx_line_id in (select customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id);
delete from ar_llca_trx_lines_gt
where customer_trx_id = l_customer_trx_id;
SELECT application_ref_type,
secondary_application_ref_id,
amount_applied
INTO l_application_ref_type,
l_secondary_app_ref_id,
l_amount_applied
FROM ar_receivable_applications
WHERE receivable_application_id = l_receivable_application_id;
-- LLCA - Delete the activity record if llca exists. We need to modify the LLCA update
-- logic to preserve the previous record details on AR_ACTIVITY_DETAILS instead of
--- removing it. Open bug exist for this issue.
/*
delete from ar_activity_details ad
where ad.cash_receipt_id = l_cash_receipt_id
and ad.customer_trx_line_id in
(select customer_trx_line_id from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id);
INSERT INTO AR_ACTIVITY_DETAILS(
CASH_RECEIPT_ID,
CUSTOMER_TRX_LINE_ID,
ALLOCATED_RECEIPT_AMOUNT,
AMOUNT,
TAX,
FREIGHT,
CHARGES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LINE_DISCOUNT,
TAX_DISCOUNT,
FREIGHT_DISCOUNT,
LINE_BALANCE,
TAX_BALANCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COMMENTS,
APPLY_TO,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
GROUP_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
OBJECT_VERSION_NUMBER,
CREATED_BY_MODULE,
SOURCE_ID,
SOURCE_TABLE,
LINE_ID,
CURRENT_ACTIVITY_FLAG)
SELECT
LLD.CASH_RECEIPT_ID,
LLD.CUSTOMER_TRX_LINE_ID,
LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
LLD.AMOUNT*-1,
LLD.TAX*-1,
LLD.FREIGHT*-1,
LLD.CHARGES*-1,
LLD.LAST_UPDATE_DATE,
LLD.LAST_UPDATED_BY,
LLD.LINE_DISCOUNT,
LLD.TAX_DISCOUNT,
LLD.FREIGHT_DISCOUNT,
LLD.LINE_BALANCE,
LLD.TAX_BALANCE,
LLD.CREATION_DATE,
LLD.CREATED_BY,
LLD.LAST_UPDATE_LOGIN,
LLD.COMMENTS,
LLD.APPLY_TO,
LLD.ATTRIBUTE1,
LLD.ATTRIBUTE2,
LLD.ATTRIBUTE3,
LLD.ATTRIBUTE4,
LLD.ATTRIBUTE5,
LLD.ATTRIBUTE6,
LLD.ATTRIBUTE7,
LLD.ATTRIBUTE8,
LLD.ATTRIBUTE9,
LLD.ATTRIBUTE10,
LLD.ATTRIBUTE11,
LLD.ATTRIBUTE12,
LLD.ATTRIBUTE13,
LLD.ATTRIBUTE14,
LLD.ATTRIBUTE15,
LLD.ATTRIBUTE_CATEGORY,
LLD.GROUP_ID,
LLD.REFERENCE1,
LLD.REFERENCE2,
LLD.REFERENCE3,
LLD.REFERENCE4,
LLD.REFERENCE5,
LLD.OBJECT_VERSION_NUMBER,
LLD.CREATED_BY_MODULE,
LLD.SOURCE_ID,
LLD.SOURCE_TABLE,
ar_Activity_details_s.nextval,
'R'
FROM ar_Activity_details LLD
where LLD.cash_receipt_id = l_cash_receipt_id
and nvl(LLD.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
and LLD.customer_trx_line_id in
(select customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id);
UPDATE ar_Activity_details dtl
set CURRENT_ACTIVITY_FLAG = 'N'
where dtl.cash_receipt_id = l_cash_receipt_id
and nvl(dtl.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
and dtl.customer_trx_line_id in
(select customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id);
SELECT cr.receipt_number,
cr.amount,
cr.currency_code,
rm.merchant_ref,
rc.creation_status,
ba.bank_branch_id,
ba.bank_account_num,
ba.bank_account_name,
ba.inactive_date,
cr.unique_reference --bug 3672953
FROM ar_cash_receipts cr,
ar_receipt_methods rm,
ar_receipt_classes rc,
ap_bank_accounts ba
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND cr.customer_bank_account_id = ba.bank_account_id
AND cr.receipt_method_id = rm.receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id;
select 'ARI_'||ar_payment_server_ord_num_s.nextval
into l_payment_server_order_num
from dual;
ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, p_cash_receipt_id);
arp_standard.debug('Apply: ' || 'CR rec updated with payment server auth code');
SELECT cr.receipt_number,
cr.amount,
cr.currency_code,
rm.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
rc.creation_status, /* AR USE */
cr.org_id,
party.party_id,
cr.pay_from_customer,
cr.customer_site_use_id,
cr.payment_trxn_extension_id,
cr.receipt_date,
pr.home_country
FROM ar_cash_receipts_all cr,
ar_receipt_methods rm,
ar_receipt_classes rc,
hz_cust_accounts hca,
hz_parties party,
/* Need to pass country code for SEPA specific receipts */
ce_bank_acct_uses bau,
ce_bank_accounts cba,
hz_parties bank,
hz_organization_profiles pr
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND hca.cust_account_id = cr.pay_from_customer
AND party.party_id = hca.party_id
AND rm.receipt_method_id = cr.receipt_method_id
AND rc.receipt_class_id = rm.receipt_class_id
AND bau.bank_acct_use_id = cr.remit_bank_acct_use_id
AND cba.bank_account_id = bau.bank_account_id
AND bank.party_id = cba.bank_id
AND pr.party_id = bank.party_id;
SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
into l_auth_flag
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE summ.transactionid = op.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)
AND op.trxn_extension_id = p_payment_trxn_extension_id
AND summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20));
select AUTHORIZATION_ID
into l_auth_id
from IBY_TRXN_EXT_AUTHS_V
where TRXN_EXTENSION_ID = p_payment_trxn_extension_id;
ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, p_cash_receipt_id);
arp_standard.debug('CR rec updated with auth_id and auth code ');
ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, p_cash_receipt_id);
arp_standard.debug('CR rec updated with auth_id and auth code ');
SELECT party.party_id
INTO l_party_id
FROM hz_cust_accounts hca,
hz_parties party
WHERE hca.party_id = party.party_id
AND hca.cust_account_id = l_customer_id ;
SELECT INSTR_ASSIGNMENT_ID
INTO l_assignment_id
from iby_fndcpt_tx_extensions
where trxn_extension_id = l_payment_trxn_extension_id;
SELECT i.start_date, i.end_date
INTO l_start_date, l_end_date
FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i
WHERE x.instr_assignment_id = i.instrument_payment_use_id
AND x.trxn_extension_id = p_payment_trxn_extension_id
AND x.INSTR_ASSIGNMENT_ID = l_assignment_id;
SELECT INSTR_ASSIGNMENT_ID
into l_assignment_id
FROM iby_fndcpt_tx_extensions
where trxn_extension_id = l_init_extension_id;
SELECT party.party_id
INTO l_party_id
FROM hz_cust_accounts hca,
hz_parties party
WHERE hca.party_id = party.party_id
AND hca.cust_account_id = l_customer_id ;
SELECT payment_channel_code
INTO l_payment_channel_code
from ar_receipt_methods
where receipt_method_id = p_receipt_method_id;
SELECT i.start_date, i.end_date
INTO l_start_date, l_end_date
FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i
WHERE x.instr_assignment_id = i.instrument_payment_use_id
AND x.trxn_extension_id = p_payment_trxn_extension_id
AND x.INSTR_ASSIGNMENT_ID = l_assignment_id;
arp_standard.debug(to_char(SQL%ROWCOUNT) || 'PMT_EXTN_ID row(s) updated.');
select cr.payment_trxn_extension_id into l_trxn_extn_id
from ar_Cash_receipts cr, ar_cash_receipt_history crh
where cr.cash_receipt_id=crh.cash_receipt_id
and crh.current_record_flag= 'Y'
and crh.status = 'REMITTED'
and cr.cash_receipt_id = p_cash_receipt_id;
Select receipt_method_id
INTO l_receipt_method_id
FROM ar_cash_receipts
WHERE cash_receipt_id = l_cash_receipt_id ;
delete from ar_activity_details
where cash_receipt_id = l_cash_receipt_id;
INSERT INTO AR_ACTIVITY_DETAILS(
CASH_RECEIPT_ID,
CUSTOMER_TRX_LINE_ID,
ALLOCATED_RECEIPT_AMOUNT,
AMOUNT,
TAX,
FREIGHT,
CHARGES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LINE_DISCOUNT,
TAX_DISCOUNT,
FREIGHT_DISCOUNT,
LINE_BALANCE,
TAX_BALANCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COMMENTS,
APPLY_TO,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
GROUP_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
OBJECT_VERSION_NUMBER,
CREATED_BY_MODULE,
SOURCE_ID,
SOURCE_TABLE,
LINE_ID,
CURRENT_ACTIVITY_FLAG)
SELECT
LLD.CASH_RECEIPT_ID,
LLD.CUSTOMER_TRX_LINE_ID,
LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
LLD.AMOUNT*-1,
LLD.TAX*-1,
LLD.FREIGHT*-1,
LLD.CHARGES*-1,
LLD.LAST_UPDATE_DATE,
LLD.LAST_UPDATED_BY,
LLD.LINE_DISCOUNT,
LLD.TAX_DISCOUNT,
LLD.FREIGHT_DISCOUNT,
LLD.LINE_BALANCE,
LLD.TAX_BALANCE,
LLD.CREATION_DATE,
LLD.CREATED_BY,
LLD.LAST_UPDATE_LOGIN,
LLD.COMMENTS,
LLD.APPLY_TO,
LLD.ATTRIBUTE1,
LLD.ATTRIBUTE2,
LLD.ATTRIBUTE3,
LLD.ATTRIBUTE4,
LLD.ATTRIBUTE5,
LLD.ATTRIBUTE6,
LLD.ATTRIBUTE7,
LLD.ATTRIBUTE8,
LLD.ATTRIBUTE9,
LLD.ATTRIBUTE10,
LLD.ATTRIBUTE11,
LLD.ATTRIBUTE12,
LLD.ATTRIBUTE13,
LLD.ATTRIBUTE14,
LLD.ATTRIBUTE15,
LLD.ATTRIBUTE_CATEGORY,
LLD.GROUP_ID,
LLD.REFERENCE1,
LLD.REFERENCE2,
LLD.REFERENCE3,
LLD.REFERENCE4,
LLD.REFERENCE5,
LLD.OBJECT_VERSION_NUMBER,
LLD.CREATED_BY_MODULE,
LLD.SOURCE_ID,
LLD.SOURCE_TABLE,
ar_Activity_details_s.nextval,
'R'
FROM ar_Activity_details LLD
where LLD.cash_receipt_id = l_cash_receipt_id
and nvl(LLD.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
UPDATE ar_Activity_details dtl
set CURRENT_ACTIVITY_FLAG = 'N'
where dtl.cash_receipt_id = l_cash_receipt_id
and nvl(dtl.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
select count(*) into l_exist_flag
from AR_MISC_CASH_DISTRIBUTIONS_ALL mcd, ar_cash_receipt_history_all rh
where rh.cash_receipt_id = l_cash_receipt_id
and mcd.cash_receipt_id = rh.cash_receipt_id
and mcd.cash_receipt_history_id = rh.cash_receipt_history_id
and mcd.request_id = arp_global.request_id
and rh.postable_flag = 'Y'
and mcd.posting_control_id = -3
and mcd.event_id is null;
SELECT applied_payment_schedule_id
, secondary_application_ref_id
, amount_applied
INTO l_applied_ps_id
, l_sec_app_ref_id
, l_amount_applied
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id;
/* Bug 4170060 do not update with claim balance for partial
rct to rct applications */
IF (NVL(p_called_from,'RAPI') = 'APPLY_OPEN_RECEIPT' AND
NVL(pg_update_claim_amount,0) <> 0) THEN
NULL;
arp_process_application.update_claim(
p_claim_id => l_sec_app_ref_id
, p_invoice_ps_id => NULL
, p_customer_trx_id => NULL
, p_amount => 0
, p_amount_applied => l_amount_applied
, p_apply_date => trunc(SYSDATE)
, p_cash_receipt_id => l_cash_receipt_id
, p_receipt_number => l_receipt_number
, p_action_type => 'U'
, x_claim_reason_code_id => l_claim_reason_code_id
, x_claim_reason_name => l_claim_reason_name
, x_claim_number => l_claim_number
, x_return_status => l_clm_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
SELECT term_id
INTO l_term_id
FROM ap_terms_bat_pay_terms_v
WHERE term_id = p_terms_id;
SELECT applied_payment_schedule_id, application_ref_id
INTO l_applied_ps_id, l_application_ref_id
FROM ar_receivable_applications
WHERE receivable_application_id = l_receivable_application_id;
/* 5955921 Replaced select statement */
SELECT tax_account_ccid
INTO l_tax_account_id
FROM zx_accounts
WHERE tax_account_entity_id = l_vat_tax_id
AND tax_account_entity_code = 'RATES'
AND internal_organization_id = l_org_id;
/*SELECT tax_account_id
INTO l_tax_account_id
FROM ar_vat_tax
WHERE vat_tax_id = l_vat_tax_id;*/
select pay_from_customer,customer_site_use_id
into l_customer_id,l_customer_site_use_id
from ar_cash_receipts
--where payment_trxn_extension_id = l_payment_trxn_extension_id;
arp_process_misc_receipts.insert_misc_receipt(
p_currency_code
=> l_currency_code,
p_amount
=> p_amount,
p_receivables_trx_id
=> l_receivables_trx_id,
p_misc_payment_source
=> p_misc_payment_source,
p_receipt_number
=> l_receipt_number,
p_receipt_date
=> l_receipt_date,
p_gl_date
=> l_gl_date,
p_comments
=> p_comments,
p_exchange_rate_type
=> l_exchange_rate_type,
p_exchange_rate
=> l_exchange_rate,
p_exchange_date
=> l_exchange_date,
p_batch_id
=> null,
p_attribute_category
=> l_attribute_rec.attribute_category,
p_attribute1
=> l_attribute_rec.attribute1,
p_attribute2
=> l_attribute_rec.attribute2,
p_attribute3
=> l_attribute_rec.attribute3,
p_attribute4
=> l_attribute_rec.attribute4,
p_attribute5
=> l_attribute_rec.attribute5,
p_attribute6
=> l_attribute_rec.attribute6,
p_attribute7
=> l_attribute_rec.attribute7,
p_attribute8
=> l_attribute_rec.attribute8,
p_attribute9
=> l_attribute_rec.attribute9,
p_attribute10
=> l_attribute_rec.attribute10,
p_attribute11
=> l_attribute_rec.attribute11,
p_attribute12
=> l_attribute_rec.attribute12,
p_attribute13
=> l_attribute_rec.attribute13,
p_attribute14
=> l_attribute_rec.attribute14,
p_attribute15
=> l_attribute_rec.attribute15,
p_remittance_bank_account_id
=> l_remit_bank_acct_use_id,
p_deposit_date
=> l_deposit_date,
p_receipt_method_id
=> l_receipt_method_id,
p_doc_sequence_value
=> l_doc_sequence_value,
p_doc_sequence_id
=> l_doc_sequence_id,
p_distribution_set_id
=> l_distribution_set_id,
p_reference_type
=> p_reference_type,
p_reference_id
=> l_reference_id,
p_vat_tax_id
=> l_vat_tax_id,
p_ussgl_transaction_code
=> p_ussgl_transaction_code,
p_anticipated_clearing_date
=> l_anticipated_clearing_date, /* Bug fix 3135407 */
p_global_attribute1
=> l_global_attribute_rec.global_attribute1,
p_global_attribute2
=> l_global_attribute_rec.global_attribute2,
p_global_attribute3
=> l_global_attribute_rec.global_attribute3,
p_global_attribute4
=> l_global_attribute_rec.global_attribute4,
p_global_attribute5
=> l_global_attribute_rec.global_attribute5,
p_global_attribute6
=> l_global_attribute_rec.global_attribute6,
p_global_attribute7
=> l_global_attribute_rec.global_attribute7,
p_global_attribute8
=> l_global_attribute_rec.global_attribute8,
p_global_attribute9
=> l_global_attribute_rec.global_attribute9,
p_global_attribute10
=> l_global_attribute_rec.global_attribute10,
p_global_attribute11
=> l_global_attribute_rec.global_attribute11,
p_global_attribute12
=> l_global_attribute_rec.global_attribute12,
p_global_attribute13
=> l_global_attribute_rec.global_attribute13,
p_global_attribute14
=> l_global_attribute_rec.global_attribute14,
p_global_attribute15
=> l_global_attribute_rec.global_attribute15,
p_global_attribute16
=> l_global_attribute_rec.global_attribute16,
p_global_attribute17
=> l_global_attribute_rec.global_attribute17,
p_global_attribute18
=> l_global_attribute_rec.global_attribute18,
p_global_attribute19
=> l_global_attribute_rec.global_attribute19,
p_global_attribute20
=> l_global_attribute_rec.global_attribute20,
p_global_attribute_category
=> l_global_attribute_rec.global_attribute_category,
p_cr_id
=> p_misc_receipt_id,
p_row_id
=> l_row_id,
p_form_name
=> 'RAPI',
p_form_version
=> p_api_version,
p_tax_rate
=> l_tax_rate,
p_gl_tax_acct
=> l_tax_account_id ,/* Bug fix 2300268 */
p_crh_id
=> l_crh_id, /* Bug fix 2742388 */
p_legal_entity_id => l_legal_entity_id, /* R12 LE uptake */
p_payment_trxn_extension_id => l_copy_pmt_trxn_extension_id
);
'ARP_PROCESS_MISC_RECEIPTS.INSERT_MISC_RECEIPT : '||SQLERRM);
pg_update_claim_amount := l_reapply_amount * -1; /* Bug 4170060 */
arp_app_pkg.update_p(l_app_rec);
arp_app_pkg.update_p(l_app_rec);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
select cash_receipt_history_id,
cash_receipt_id,
reversal_gl_date,
gl_date,
ar_cash_receipt_history_s.nextval,
batch_id,
amount,
cc_error_code,
cc_error_text,
cc_instrtype,
'VALID'
from ar_rr_crh_gt;
select crhgt.cash_receipt_id
from ar_rr_crh_gt crhgt
where crhgt.cash_receipt_id
not in
(
select crh.cash_receipt_id
from ar_cash_receipt_history crh
where crh.cash_receipt_id= crhgt.cash_receipt_id
and crh.status = 'CONFIRMED'
and crh.current_record_flag='Y'
and crh.request_id =p_req_id
);
delete from ar_rr_crh_gt;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
/*Insert into ar_rr_crh_gt select * from ar_cash_receipt_history*/
FORALL i IN p_cash_receipts_id.cash_receipt_id.first..p_cash_receipts_id.cash_receipt_id.last
INSERT INTO AR_RR_CRH_GT
(
CASH_RECEIPT_HISTORY_ID,
CASH_RECEIPT_ID,
STATUS,
TRX_DATE,
AMOUNT,
FIRST_POSTED_RECORD_FLAG,
POSTABLE_FLAG,
FACTOR_FLAG,
GL_DATE,
CURRENT_RECORD_FLAG,
BATCH_ID,
ACCOUNT_CODE_COMBINATION_ID,
REVERSAL_GL_DATE,
REVERSAL_CASH_RECEIPT_HIST_ID,
FACTOR_DISCOUNT_AMOUNT,
BANK_CHARGE_ACCOUNT_CCID,
POSTING_CONTROL_ID,
REVERSAL_POSTING_CONTROL_ID,
GL_POSTED_DATE,
REVERSAL_GL_POSTED_DATE,
LAST_UPDATE_LOGIN,
ACCTD_AMOUNT,
ACCTD_FACTOR_DISCOUNT_AMOUNT,
CREATED_BY,
CREATION_DATE,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
LAST_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LAST_UPDATED_BY,
PRV_STAT_CASH_RECEIPT_HIST_ID,
CREATED_FROM,
REVERSAL_CREATED_FROM,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ORG_ID,
EVENT_ID,
CC_ERROR_CODE,
CC_ERROR_TEXT,
CC_INSTRTYPE
)
select
CASH_RECEIPT_HISTORY_ID,
CASH_RECEIPT_ID,
STATUS,
TRX_DATE,
AMOUNT,
FIRST_POSTED_RECORD_FLAG,
POSTABLE_FLAG,
FACTOR_FLAG,
GL_DATE,
CURRENT_RECORD_FLAG,
BATCH_ID,
ACCOUNT_CODE_COMBINATION_ID,
REVERSAL_GL_DATE,
REVERSAL_CASH_RECEIPT_HIST_ID,
FACTOR_DISCOUNT_AMOUNT,
BANK_CHARGE_ACCOUNT_CCID,
POSTING_CONTROL_ID,
REVERSAL_POSTING_CONTROL_ID,
GL_POSTED_DATE,
REVERSAL_GL_POSTED_DATE,
LAST_UPDATE_LOGIN,
ACCTD_AMOUNT,
ACCTD_FACTOR_DISCOUNT_AMOUNT,
CREATED_BY,
CREATION_DATE,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
LAST_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LAST_UPDATED_BY,
PRV_STAT_CASH_RECEIPT_HIST_ID,
CREATED_FROM,
REVERSAL_CREATED_FROM,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ORG_ID,
EVENT_ID,
p_cash_receipts_id.CC_ERROR_CODE(i),
p_cash_receipts_id.CC_ERROR_TEXT(i),
p_cash_receipts_id.CC_INSTRTYPE(i)
FROM ar_Cash_receipt_history
where cash_receipt_id=p_cash_receipts_id.cash_receipt_id(i)
and current_record_flag = 'Y'
and status = 'REMITTED'
and NOT EXISTS (SELECT * FROM AR_RR_CRH_GT WHERE cash_receipt_id = p_cash_receipts_id.cash_receipt_id(i)); -- Bug 13549918
arp_standard.debug('Records for update: ' || 'COUNT = 0 and LAST FETCH ');
UPDATE ar_cash_receipts SET
CC_ERROR_FLAG ='Y',
CC_ERROR_CODE = l_crh_upd.cc_error_code(i),
CC_ERROR_TEXT = l_crh_upd.cc_error_text(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = l_last_update_login,
LAST_UPDATED_BY = l_last_updated_by
WHERE cash_receipt_id = l_crh_upd.cash_receipt_id(i)
AND l_crh_upd.cc_instrtype(i) in ('CREDITCARD','BANKACCOUNT')
AND l_crh_upd.rec_status(i) ='VALID';
/*Update existing CRH record of remittance*/
FORALL i in l_crh_upd.cash_receipt_history_id.first..l_crh_upd.cash_receipt_history_id.last
update ar_cash_receipt_history set
reversal_cash_receipt_hist_id = l_crh_upd.reversal_cash_receipt_hist_id(i),
reversal_gl_date = l_crh_upd.reversal_gl_date(i),
reversal_created_from = 'ARREVREM',
current_record_flag = NULL,
last_update_date = sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE cash_receipt_history_id = l_crh_upd.cash_receipt_history_id(i)
AND current_record_flag = 'Y'
AND status = 'REMITTED'
AND l_crh_upd.rec_status(i) = 'VALID';
/*Inserting new CRH record for CONFIRM State*/
FORALL i in l_crh_upd.cash_receipt_history_id.first..l_crh_upd.cash_receipt_history_id.last
Insert into ar_cash_receipt_history
(
CASH_RECEIPT_HISTORY_ID,
CASH_RECEIPT_ID,
STATUS,
TRX_DATE,
AMOUNT,
FIRST_POSTED_RECORD_FLAG,
POSTABLE_FLAG,
FACTOR_FLAG,
GL_DATE,
CURRENT_RECORD_FLAG,
BATCH_ID,
ACCOUNT_CODE_COMBINATION_ID,
REVERSAL_GL_DATE,
REVERSAL_CASH_RECEIPT_HIST_ID,
FACTOR_DISCOUNT_AMOUNT,
BANK_CHARGE_ACCOUNT_CCID,
POSTING_CONTROL_ID,
REVERSAL_POSTING_CONTROL_ID,
GL_POSTED_DATE,
REVERSAL_GL_POSTED_DATE,
LAST_UPDATE_LOGIN,
ACCTD_AMOUNT,
ACCTD_FACTOR_DISCOUNT_AMOUNT,
CREATED_BY,
CREATION_DATE,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
LAST_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LAST_UPDATED_BY,
PRV_STAT_CASH_RECEIPT_HIST_ID,
CREATED_FROM,
REVERSAL_CREATED_FROM,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ORG_ID,
EVENT_ID
)
SELECT
l_crh_upd.reversal_cash_receipt_hist_id(i),
CASH_RECEIPT_ID,
STATUS,
TRX_DATE,
AMOUNT,
'N',
POSTABLE_FLAG,
FACTOR_FLAG,
l_crh_upd.reversal_gl_date(i),
'Y',
NULL,
ACCOUNT_CODE_COMBINATION_ID,
NULL,
NULL,
FACTOR_DISCOUNT_AMOUNT,
BANK_CHARGE_ACCOUNT_CCID,
-3,
NULL,
NULL,
NULL,
l_last_update_login,
ACCTD_AMOUNT,
ACCTD_FACTOR_DISCOUNT_AMOUNT,
l_created_by,
sysdate,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
sysdate,
l_PROGRAM_APPLICATION_ID,
l_PROGRAM_ID,
sysdate,
l_request_id_bulk,
l_last_updated_by,
l_crh_upd.CASH_RECEIPT_HISTORY_ID(i),
'ARREVREM',
NULL,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ORG_ID,
NULL
FROM AR_CASH_RECEIPT_HISTORY
WHERE reversal_cash_receipt_hist_id=l_crh_upd.cash_receipt_history_id(i)
AND l_crh_upd.rec_status(i)='VALID';
arp_util.debug('Inserted new CRH record for new status CONFIRM');
update ar_cash_receipt_history set
request_id = l_request_id
WHERE cash_receipt_history_id = l_crh_upd.reversal_cash_receipt_hist_id(i)
AND current_record_flag = 'Y'
AND status = 'CONFIRMED'
AND l_crh_upd.rec_status(i)='VALID'
AND request_id = l_request_id_bulk;
update ar_batches set
control_count = control_count - 1,
control_amount= control_amount - l_crh_upd.amount(i)
WHERE batch_id = l_crh_upd.batch_id(i)
AND l_crh_upd.rec_status(i)='VALID';
INSERT INTO ar_distributions (
line_id,
source_id,
source_table,
source_type,
source_type_secondary,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
source_id_secondary,
source_table_secondary,
currency_code ,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
third_party_id,
third_party_sub_id,
tax_code_id,
location_segment_id,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
reversed_source_id,
tax_group_code_id,
org_id,
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr,
ref_account_class,
activity_bucket,
ref_dist_ccid,
ref_mf_dist_flag
)
select ar_distributions_s.nextval,
l_crh_upd.reversal_Cash_receipt_hist_id(i),
'CRH',
ard.source_type,
ard.source_type_secondary,
ard.code_combination_id,
decode(sign(nvl(ard.amount_dr,0)- nvl(ard.amount_cr,0)),-1,ard.amount_cr,NULL),
decode(sign(nvl(ard.amount_cr,0)- nvl(ard.amount_dr,0)),-1,ard.amount_dr,NULL),
decode(sign(nvl(ard.acctd_amount_dr,0)- nvl(ard.acctd_amount_cr,0)),-1,ard.acctd_amount_cr,NULL),
decode(sign(nvl(ard.acctd_amount_cr,0)- nvl(ard.acctd_amount_dr,0)),-1,ard.acctd_amount_dr,NULL),
arp_standard.profile.user_id,
SYSDATE,
arp_standard.profile.user_id,
SYSDATE,
arp_standard.profile.last_update_login,
ard.source_id_secondary,
ard.source_table_secondary,
ard.currency_code ,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_code_id,
ard.location_segment_id,
ard.taxable_entered_dr,
ard.taxable_entered_cr,
ard.taxable_accounted_dr,
ard.taxable_accounted_cr,
ard.tax_link_id,
ard.reversed_source_id,
ard.tax_group_code_id,
ard.org_id,
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
ard.from_amount_dr,
ard.from_amount_cr,
ard.from_acctd_amount_dr,
ard.from_acctd_amount_cr,
ard.ref_account_class,
ard.activity_bucket,
ard.ref_dist_ccid,
ard.ref_mf_dist_flag
FROM ar_distributions ard,
ar_Cash_receipt_history crh
WHERE ard.source_id=crh.cash_receipt_history_id
and ard.source_table = 'CRH'
and cash_receipt_history_id=l_crh_upd.cash_receipt_history_id(i)
AND l_crh_upd.rec_status(i)='VALID';
update ar_cash_receipts
SET last_update_date = sysdate,
request_id = p_request_id
WHERE cash_receipt_id in
( select cash_receipt_id
from ar_create_receipts_gt
where gt_id = p_gt_id
and request_id = p_request_id
and return_error_status = 'S'
and org_id = p_org_id );
arp_standard.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
update ar_cash_receipt_history SET
last_update_date = sysdate,
request_id = p_request_id
WHERE cash_receipt_id in
( select cash_receipt_id
from ar_create_receipts_gt
where gt_id = p_gt_id
and request_id = p_request_id
and return_error_status = 'S'
and org_id = p_org_id );
arp_standard.debug ( 'NO of Receipts updated CRH = '|| to_char(SQL%ROWCOUNT));
update AR_payment_schedules SET
last_update_date = sysdate,
request_id = p_request_id
WHERE cash_receipt_id in
( select cash_receipt_id
from ar_create_receipts_gt
where gt_id = p_gt_id
and request_id = p_request_id
and return_error_status = 'S'
and org_id = p_org_id );
arp_standard.debug ( 'NO of Receipts updated PS = '|| to_char(SQL%ROWCOUNT));
update ar_receivable_applications SET
last_update_date = sysdate,
request_id = p_request_id
WHERE cash_receipt_id in
( select cash_receipt_id
from ar_create_receipts_gt
where gt_id = p_gt_id
and request_id = p_request_id
and return_error_status = 'S'
and org_id = p_org_id );
arp_standard.debug ( 'NO of RA updated = '|| to_char(SQL%ROWCOUNT));
select /*+ LEADING (GT) INDEX (GT AR_CREATE_RECEIPTS_GT_N2) USE_NL(GT RA)
INDEX (RA AR_RECEIVABLE_APPLICATIONS_N1) */
min(gt.cash_receipt_id),
max(gt.cash_receipt_id),
min(ra.receivable_application_id),
max(ra.receivable_application_id)
into l_from_doc_id,
l_to_doc_id,
l_from_ra_doc_id,
l_to_ra_doc_id
from ar_create_receipts_gt gt, ar_receivable_applications ra
where gt.cash_receipt_id = ra.cash_receipt_id
AND gt_id = p_gt_id
and gt.request_id = p_request_id
and return_error_status = 'S'
and gt.org_id = p_org_id ;
LAST_UPDATE_DATE DBMS_SQL.DATE_TABLE,
CREATION_DATE DBMS_SQL.DATE_TABLE,
CREATED_BY DBMS_SQL.NUMBER_TABLE,
LAST_UPDATE_LOGIN DBMS_SQL.NUMBER_TABLE,
PROGRAM_APPLICATION_ID DBMS_SQL.NUMBER_TABLE,
PROGRAM_ID DBMS_SQL.NUMBER_TABLE,
PROGRAM_UPDATE_DATE DBMS_SQL.DATE_TABLE,
LAST_UPDATED_BY DBMS_SQL.NUMBER_TABLE );
SELECT org_id
FROM ar_create_receipts_gt
GROUP BY org_id;
SELECT *
FROM ar_create_receipts_gt
WHERE org_id = p_c_org_id
AND return_error_status IS NULL;
SELECT FND_CONCURRENT_REQUESTS_S.nextval
INTO l_request_id
FROM dual;
l_rcpt_error_info_tab.BATCH_ID.DELETE;
l_rcpt_error_info_tab.REQUEST_ID.DELETE;
l_rcpt_error_info_tab.CASH_RECEIPT_ID.DELETE;
l_rcpt_error_info_tab.RECEIPT_NUMBER.DELETE;
l_rcpt_error_info_tab.RECEIPT_DATE.DELETE;
l_rcpt_error_info_tab.CUSTOMER_TRX_ID.DELETE;
l_rcpt_error_info_tab.TRXN_NUMBER.DELETE;
l_rcpt_error_info_tab.PAYMENT_SCHEDULE_ID.DELETE;
l_rcpt_error_info_tab.APPLIED_PAYMENT_SCHEDULE_ID.DELETE;
l_rcpt_error_info_tab.PAYING_CUSTOMER_ID.DELETE;
l_rcpt_error_info_tab.PAYING_SITE_USE_ID.DELETE;
l_rcpt_error_info_tab.EXCEPTION_CODE.DELETE;
l_rcpt_error_info_tab.ADDITIONAL_MESSAGE.DELETE;
l_rcpt_error_info_tab.REMIT_BANK_ACCT_USE_ID.DELETE;
l_rcpt_error_info_tab.LAST_UPDATE_DATE.DELETE;
l_rcpt_error_info_tab.CREATION_DATE.DELETE;
l_rcpt_error_info_tab.CREATED_BY.DELETE;
l_rcpt_error_info_tab.LAST_UPDATE_LOGIN.DELETE;
l_rcpt_error_info_tab.PROGRAM_APPLICATION_ID.DELETE;
l_rcpt_error_info_tab.PROGRAM_ID.DELETE;
l_rcpt_error_info_tab.PROGRAM_UPDATE_DATE.DELETE;
l_rcpt_error_info_tab.LAST_UPDATED_BY.DELETE;
l_cash_receipt_info_rec.cash_receipt_id.delete;
l_cash_receipt_info_rec.receipt_number.delete;
l_cash_receipt_info_rec.receipt_date.delete;
l_cash_receipt_info_rec.gt_id.delete;
l_cash_receipt_info_rec.request_id.delete;
l_cash_receipt_info_rec.return_error_status.delete;
l_cash_receipt_info_rec.org_id.delete;
UPDATE ar_create_receipts_gt
SET request_id = l_cash_receipt_info_rec.request_id(j),
cash_receipt_id = l_cash_receipt_info_rec.cash_receipt_id(j),
return_error_status = l_cash_receipt_info_rec.return_error_status(j),
gt_id = l_cash_receipt_info_rec.gt_id(j)
WHERE receipt_number = l_cash_receipt_info_rec.receipt_number(j)
AND receipt_date = l_cash_receipt_info_rec.receipt_date(j)
AND org_id = l_cash_receipt_info_rec.org_id(j);
INSERT INTO AR_CREATE_RECEIPTS_ERROR VALUES (
NULL,
l_rcpt_error_info_tab.request_id(k),
l_rcpt_error_info_tab.cash_receipt_id(k),
l_rcpt_error_info_tab.receipt_number(k),
l_rcpt_error_info_tab.receipt_date(k),
NULL,
NULL,
NULL,
NULL,
l_rcpt_error_info_tab.paying_customer_id(k),
l_rcpt_error_info_tab.paying_site_use_id(k),
l_rcpt_error_info_tab.exception_code(k),
l_rcpt_error_info_tab.additional_message(k),
l_rcpt_error_info_tab.remit_bank_acct_use_id(k),
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.prog_appl_id,
NULL,
SYSDATE,
fnd_global.user_id ) ;
END LOOP;--main select cursor loop
SELECT nvl(pay_unrelated_invoices_flag,'N')
INTO l_pay_unrel_inv_flag
FROM ar_system_parameters;
SELECT pay_from_customer
, receipt_date
INTO l_rec_customer_id
, l_rec_date
FROM ar_cash_receipts
WHERE cash_receipt_id = l_cash_receipt_id;
SELECT cust_acct.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
and cust_acct.cust_account_id = l_customer_id
and cust_acct.status = 'A' ;
SELECT cust_acct.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND cust_acct.status = 'A'
AND cust_acct.cust_account_id = l_customer_id
AND cust_acct.cust_account_id in
(
SELECT TO_NUMBER(l_rec_customer_id)
FROM dual
UNION
SELECT related_cust_account_id
FROM hz_cust_acct_relate r
WHERE r.cust_account_id = l_rec_customer_id
AND r.status = 'A'
AND r.bill_to_flag = 'Y'
UNION
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND acc.cust_account_id = l_rec_customer_id
AND l_rec_date BETWEEN effective_start_date
AND effective_end_date
);
SELECT site_uses.site_use_id
INTO l_location
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses site_uses
WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND site_uses.site_use_code in ('BILL_TO','DRAWEE')
AND acct_site.cust_account_id = l_customer_id
AND site_uses.site_use_id=l_location;
| Update receivable_application with new value|
+-----------------------------------------------*/
BEGIN
UPDATE ar_receivable_applications
SET ON_ACCT_CUST_ID = l_customer_id,
ON_ACCT_CUST_SITE_USE_ID = l_location
WHERE cash_receipt_id = l_cash_receipt_id
AND receivable_application_id = l_receivable_application_id;