The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into count_brs
from ar_transaction_history
where batch_id = p_reserved_value
and current_record_flag = 'Y';
select batch_applied_status
into l_batch_applied_status
from ar_batches
where batch_id = p_reserved_value;
select max(reserved_value), -- remittance batch id null check
max(payment_schedule_id)
into p_reserved_value,
p_payment_schedule_id
from ar_payment_schedules
where customer_trx_id = p_internal_reference
and reserved_type = 'REMITTANCE';
Select batch_applied_status,
status,
media_reference
into l_batch_applied_status,
l_status,
p_media_reference
from ar_batches
where batch_id = p_reserved_value
and type = 'BR_REMITTANCE';
select remit_bank_acct_use_id,
currency_code,
gl_date,
with_recourse_flag,
batch_date,
receipt_method_id,
remit_method_code,
batch_applied_status,
batch_id
into
l_remit_bank_acct_use_id,
l_currency_code,
l_gl_date,
l_with_recourse_flag,
l_batch_date,
l_receipt_method_id,
l_remit_method_code,
l_batch_applied_status,
l_batch_id
from ar_batches
where media_reference = p_media_reference
and gl_date = p_remittance_accounting_Date
and type = 'BR_REMITTANCE';
SELECT cba.bank_account_num INTO l_bank_account_num
FROM ce_bank_accounts cba,
ce_bank_acct_uses cbau
WHERE cbau.bank_acct_use_id =l_remit_bank_acct_use_id
AND cbau.bank_account_id = cba.bank_account_id;
select name
into l_payment_method
from ar_receipt_methods
where receipt_method_id = l_receipt_method_id;
| AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status); |
| AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(l_batch_id,l_payment_schedule_id,x_return_status); |
arp_standard.debug ('impremapi.Existing_Remittance - call deselect');
AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status);
AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(l_batch_id,l_payment_schedule_id,x_return_status);
| Insert the remittance into the databse by calling ARP_BR_REMIT_BATCHES.insert_remit. |
| |
| If the BR was already selected to a remittance then deselect it from that remittance. Check if the remittance is empty and |
| if it is then cancel it. |
| |
| CALLS TO EXTERNAL PROCEDURES :
| |
| ARP_BR_REMIT_BATCHES.insert_remit |
| AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit |
| |
|---------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE new_remittance(
p_media_reference IN ar_batches.media_reference%TYPE,
p_remittance_accounting_date IN ar_batches.gl_date%TYPE,
p_remittance_Date IN ar_batches.batch_date%TYPE,
p_internal_reference IN ra_customer_trx.Customer_trx_id%TYPE,
p_with_recourse_flag IN ar_batches.with_recourse_flag%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_remittance_method IN ar_batches.remit_method_code%TYPE,
p_remittance_bnk_branch_number IN ce_bank_branches_v.branch_number%TYPE,
p_remittance_bank_number IN ce_bank_branches_v.bank_number%TYPE,
p_remittance_bnk_acct_number IN ce_bank_accounts.bank_account_num%TYPE,
p_payment_method IN ar_receipt_methods.name%TYPE,
x_batch_name OUT NOCOPY ar_batches.name%TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_receipt_class_id ar_receipt_methods.receipt_class_id%TYPE;
SELECT branch_party_id
FROM ce_bank_branches_v cbb
WHERE cbb.bank_number = p_bank_number
AND cbb.branch_number = p_branch_number;
SELECT cbau.bank_acct_use_id
FROM ce_bank_accounts cba, ce_bank_acct_uses cbau
WHERE cba.bank_account_id = cbau.bank_account_id
AND cba.bank_branch_id = p_branch_id
AND cba.bank_account_num = p_bank_account_number
AND cba.currency_code = p_currency_code
AND cba.account_classification = 'INTERNAL';
select receipt_method_id,
nvl(auto_print_program_id,0),
nvl(auto_trans_program_id,0),
receipt_class_id
into l_receipt_method_id
,l_auto_print_program_id
,l_auto_trans_program_id
,l_receipt_class_id
from ar_receipt_methods
where name = p_payment_method;
select nvl(auto_print_program_id,0),
nvl(auto_trans_program_id,0)
into l_auto_print_program_id,
l_auto_trans_program_id
from ar_receipt_methods
where receipt_method_id = l_receipt_method_id;
arp_standard.debug('Call insert_remit : impremapi.New_Remittance');
ARP_BR_REMIT_BATCHES.insert_remit(
p_remittance_date,
p_remittance_accounting_date,
p_currency_code,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
p_media_reference,
l_receipt_method_id,
l_remit_bank_acct_use_id,
l_receipt_class_id,
l_bank_branch_id,
p_remittance_method,
p_with_recourse_flag,
null, -- p_bank_deposit_number
l_auto_print_program_id,
l_auto_trans_program_id,
x_batch_id,
x_batch_name,
x_batch_applied_status);
arp_standard.debug ('impremapi.New_Remittance Called Insert_remit');
| Deselect the BR from the batch it is currently assigned to |
---------------------------------------------------------------------------------------*/
IF l_reserved_value is not null THEN
arp_standard.debug ('impremapi.New_Remittance : call Deselect_BR_Remit');
AR_BILLS_MAINTAIN_PUB.Deselect_BR_Remit(l_payment_schedule_id,x_return_status);
arp_standard.debug ('impremapi.New_Remittance : called Deselect_BR_Remit');
AR_BILLS_MAINTAIN_PUB.Select_BR_Remit(x_batch_id,l_payment_schedule_id,x_return_status);
select batch_applied_status,
status
into
l_batch_applied_status,
l_status
from ar_batches
where media_reference = p_media_reference;