[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_id into l_org_id
from ar_cash_receipts_all
where cash_receipt_id = p_cr_id;
SELECT receipt_number, payment_trxn_extension_id
into l_receipt_number, l_payment_trxn_extn_id
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cr_id ;
arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
arp_cr_history_pkg.update_p( l_crh_rec_old );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
/*
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
/*
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_rate_adjustments_pkg.insert_p( l_radj_rec,l_radj_rec.rate_adjustment_id );
ARP_GLOBAL.last_updated_by,
ARP_GLOBAL.last_update_date,
ARP_GLOBAL.last_update_login,
FALSE,
l_crh_rec_new.cash_receipt_history_id
);
UPDATE AR_CASH_RECEIPTS
SET actual_value_date = p_actual_value_date,
rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
last_updated_by = arp_global.last_updated_by,
last_update_date = SYSDATE,
last_update_login = arp_global.last_update_login
WHERE cash_receipt_id = p_cr_id;
select 'Y'
into g_refresh_running
from ar_conc_process_requests
where concurrent_program_name = 'ARSUMREF';
arp_rate_adjustments_pkg.insert_p( l_radj_rec,l_radj_rec.rate_adjustment_id );
ARP_GLOBAL.last_updated_by,
ARP_GLOBAL.last_update_date,
ARP_GLOBAL.last_update_login,
TRUE, -- should this be FALSE??? OS 7/6/99
l_new_crh_id_fr_radj
);
arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
/* Bug 9761480 : If rate adjustment is reversed, then update those CRH records also*/
IF l_new_crh_id_fr_radj IS NOT NULL THEN
-- Update the old history record
l_crh_rec_old.current_record_flag := NULL;
arp_cr_history_pkg.update_p( l_crh_rec_old );
arp_cr_history_pkg.update_p( l_crh_rec_fr_radj );
arp_cr_history_pkg.update_p( l_crh_rec_old );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
UPDATE AR_CASH_RECEIPTS
SET actual_value_date = p_actual_value_date,
rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
last_updated_by = arp_global.last_updated_by,
last_update_date = SYSDATE,
last_update_login = arp_global.last_update_login
WHERE cash_receipt_id = p_cr_id;
arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
arp_cr_history_pkg.update_p( l_crh_rec_old );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
select 'Y'
into g_refresh_running
from ar_conc_process_requests
where concurrent_program_name = 'ARSUMREF';
arp_cr_history_pkg.insert_p( l_crh_rec_new, l_crh_rec_new.cash_receipt_history_id );
arp_cr_history_pkg.update_p( l_crh_rec_old );
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_distributions_pkg.insert_p( l_dist_rec,l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
| all inserts. Also added parameter tax_rate |
| required for VAT |
| 04-JUN-99 GJWANG Derive distribution_set_id from in parameter |
| receivables_trx_id when create misc receipt |
| 30-SEP-02 R Kader Bug fix 2300268 : Added a new variable and |
| used this variable while calling the proc |
| insert_misc_receipt()
| 21-FEB-03 R Kader Bug fix 2742388 : Added a new variable and |
| used this variable while calling the proc |
| insert_misc_receipt()
| 01-Feb-13 Jixun Bug fix 14726865: AUTO RECONCILIATION |
| COMPLETES IN ERROR WITH SQLERRM:ORA-28115: |
| policy with check option violation
+===========================================================================*/
PROCEDURE ins_misc_txn(
p_receipt_number IN ar_cash_receipts.receipt_number%TYPE,
p_document_number IN ar_cash_receipts.doc_sequence_value%TYPE,
p_doc_sequence_id IN ar_cash_receipts.doc_sequence_id%TYPE,
p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
p_deposit_date IN ar_cash_receipts.deposit_date%TYPE,
p_receipt_amount IN ar_cash_receipts.amount%TYPE,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_exchange_date IN ar_cash_receipt_history.exchange_date%TYPE,
p_exchange_rate_type IN ar_cash_receipt_history.exchange_rate_type%TYPE,
p_exchange_rate IN ar_cash_receipt_history.exchange_rate%TYPE,
p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
p_remit_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%TYPE,
p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
p_comments IN ar_cash_receipts.comments%TYPE,
p_vat_tax_id IN ar_cash_receipts.vat_tax_id%TYPE,
p_reference_type IN ar_cash_receipts.reference_type%TYPE,
p_reference_id IN ar_cash_receipts.reference_id%TYPE,
p_misc_payment_source IN ar_cash_receipts.misc_payment_source%TYPE,
p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_cr_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
p_tax_rate IN NUMBER ) IS
--
p_row_id VARCHAR2(30);
/*SELECT default_acctg_distribution_set
INTO l_dis_set_id
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id;*/
SELECT default_acctg_distribution_set, org_id
INTO l_dis_set_id, l_org_id
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id;
/* bug 6034914 , commented out the select and added next 6 lines.
SELECT tax_account_id
INTO l_tax_account_id
FROM ar_vat_tax
WHERE vat_tax_id = p_vat_tax_id;
ARP_PROCESS_MISC_RECEIPTS.insert_misc_receipt (
p_currency_code,
p_receipt_amount,
p_receivables_trx_id,
p_misc_payment_source,
p_receipt_number,
p_receipt_date,
p_gl_date,
p_comments,
p_exchange_rate_type,
p_exchange_rate,
p_exchange_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_remit_bank_account_id,
p_deposit_date,
p_receipt_method_id,
p_document_number,
p_doc_sequence_id,
l_dis_set_id,
p_reference_type,
p_reference_id,
p_vat_tax_id,
NULL, -- Bug 975560/962254
p_anticipated_clearing_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_cr_id,
p_row_id,
p_module_name,
p_module_version,
p_tax_rate,
l_tax_account_id, /* Bug fix 2300268 */
l_crh_id); /* Bug fix 2742388 */
SELECT crh.cash_receipt_history_id
FROM ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = p_cr_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED';
SELECT attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15
INTO 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
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cr_id;
SELECT 'Y'
FROM ar_payment_schedules ps,
ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.reserved_type IS NOT NULL
AND ps.reserved_value IS NOT NULL
AND ra.status = 'APP'
AND ra.display = 'Y';
SELECT 'Y'
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.applied_payment_schedule_id = -2
AND ra.display = 'Y';
P_PROGRAM_UPDATE_DATE IN ar_batches.program_update_date%TYPE,
P_REMITTANCE_BANK_BRANCH_ID IN ar_batches.remittance_bank_branch_id%TYPE,
P_REMIT_METHOD_CODE IN ar_batches.remit_method_code%TYPE,
P_REQUEST_ID IN ar_batches.request_id%TYPE,
P_SET_OF_BOOKS_ID IN ar_batches.set_of_books_id%TYPE,
P_TRANSMISSION_ID IN ar_batches.transmission_id%TYPE,
P_BANK_DEPOSIT_NUMBER IN ar_batches.bank_deposit_number%TYPE)
IS
CURSOR C IS
SELECT crh.cash_receipt_history_id
FROM ar_cash_receipt_history crh, ar_cash_receipts acr
WHERE crh.batch_id = P_BATCH_ID
AND crh.status not in ('REVERSED')
AND crh.cash_receipt_id = acr.cash_receipt_id
FOR UPDATE NOWAIT;
SELECT *
FROM ar_batches
WHERE batch_id = P_BATCH_ID
FOR UPDATE NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
SELECT sum(amount) , sum(acctd_amount)
INTO c_amount, c_acctd_amount
FROM ar_cash_receipt_history
WHERE batch_id = P_BATCH_ID
AND status not in ('REVERSED');
AND (Batchinfo.program_update_date = P_PROGRAM_UPDATE_DATE OR
(Batchinfo.program_update_date is NULL AND P_PROGRAM_UPDATE_DATE is NULL))
AND (Batchinfo.remittance_bank_branch_id = P_REMITTANCE_BANK_BRANCH_ID OR
(Batchinfo.remittance_bank_branch_id is NULL AND P_REMITTANCE_BANK_BRANCH_ID is NULL))
AND (Batchinfo.remit_method_code = P_REMIT_METHOD_CODE OR
(Batchinfo.remit_method_code is NULL AND P_REMIT_METHOD_CODE is NULL))
AND (Batchinfo.request_id = P_REQUEST_ID OR
(Batchinfo.request_id is NULL AND P_REQUEST_ID is NULL))
AND (Batchinfo.set_of_books_id = P_SET_OF_BOOKS_ID OR
(Batchinfo.set_of_books_id is NULL AND P_SET_OF_BOOKS_ID is NULL))
AND (Batchinfo.transmission_id = P_TRANSMISSION_ID OR
(Batchinfo.transmission_id is NULL AND P_TRANSMISSION_ID is NULL))
AND (Batchinfo.bank_deposit_number = P_BANK_DEPOSIT_NUMBER OR
(Batchinfo.bank_deposit_number is NULL AND P_BANK_DEPOSIT_NUMBER is NULL))
)
then
return;
SELECT 'Y'
INTO l_result
FROM ar_payment_schedules ps_dm
WHERE ps_dm.reversed_cash_receipt_id = p_cash_receipt_id
AND ps_dm.class = 'DM';
PROCEDURE update_actual_value_date(p_cash_receipt_id IN NUMBER,
p_actual_value_date IN DATE) IS
BEGIN
--Yao zhang modified for bug 10124745
UPDATE AR_CASH_RECEIPTS
SET actual_value_date = p_actual_value_date,
rec_version_number = nvl(rec_version_number,1)+1, /* bug 3372585 */
last_updated_by = arp_global.last_updated_by,
last_update_date = SYSDATE,
last_update_login = arp_global.last_update_login
WHERE cash_receipt_id = p_cash_receipt_id;
ARP_CASHBOOK.g_customer_id_tab.delete;
ARP_CASHBOOK.g_site_use_id_tab.delete;
ARP_CASHBOOK.g_currency_tab.delete;
ARP_CASHBOOK.g_org_id_tab.delete;
ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;