[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
C_SELECT_REMIT CONSTANT VARCHAR2(30) := 'SELECT_REMIT';
C_DESELECT_REMIT CONSTANT VARCHAR2(30) := 'DESELECT_REMIT';
C_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
SELECT count(*)
INTO l_count
FROM AR_TRANSACTION_HISTORY
WHERE customer_trx_id = p_trx_rec.customer_trx_id
AND event = C_COMPLETED;
SELECT name
INTO l_cat_code
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_trx_rec.cust_trx_type_id;
SELECT copy_doc_number_flag
INTO l_copy_doc_number_flag
FROM RA_BATCH_SOURCES
WHERE batch_source_id = p_trx_rec.batch_source_id;
SELECT remittance_batch_id
INTO p_batch_rec.batch_id
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
| Update_Reserved_Columns |
| |
| DESCRIPTION |
| Update the reserved type and reserved value of the payment schedule |
| |
+==============================================================================*/
PROCEDURE Update_Reserved_Columns ( p_payment_schedule_id IN NUMBER ,
p_reserved_type IN VARCHAR2,
p_reserved_value IN NUMBER )
IS
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Update_Reserved_Columns ()+ ');
arp_ps_pkg.update_p (l_ps_rec, l_ps_rec.payment_schedule_id);
arp_util.debug('AR_BILLS_MAINTAIN_LIB_PVT.Update_Reserved_Columns ()- ');
arp_util.debug('>>>>>>>>>> EXCEPTION : Update_Reserved_Columns () ');
END Update_Reserved_Columns;
Select SYSDATE
into p_trx_date
from dual;
SELECT br_ref_customer_trx_id, br_ref_payment_schedule_id, extended_amount,
customer_trx_line_id, extended_acctd_amount
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_trh_rec.customer_trx_id;
| Update the Assignment Information with |
| the Adjustment ID |
+-----------------------------------------------*/
arp_ctl_pkg.lock_p (assignment_rec.customer_trx_line_id);
UPDATE ra_customer_trx_lines
SET br_adjustment_id = l_new_adjust_id
WHERE customer_trx_line_id = assignment_rec.customer_trx_line_id;
| Update the reserved columns of the exchanged |
| Payment Schedule |
+-----------------------------------------------*/
update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, NULL, NULL);
arp_proc_transaction_history.insert_transaction_history (l_trh_rec ,
l_trh_rec.transaction_history_id);
SELECT br_ref_payment_schedule_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id;
| For each assignment, update the reserved |
| columns of the PS |
+-----------------------------------------------*/
FOR assignment_rec IN assignment_cur LOOP
update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, 'TRANSACTION', p_customer_trx_id);
SELECT max(adjustment_id)
INTO p_adj_id
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id;
select org_id
into l_org_id
from ar_adjustments
where adjustment_id = p_adj_id;
select org_id
into l_org_id
from ar_adjustments
where adjustment_id = p_adj_id;
| Update the reserved columns of the PS of the exchanged transactions |
| |
| Called by UNCOMPLETE_BR and CANCEL_BR |
| |
+==============================================================================*/
PROCEDURE Reverse_Assignments_Adjustment ( p_trh_rec IN AR_TRANSACTION_HISTORY%ROWTYPE ,
p_acceptance_flag IN VARCHAR2 )
IS
CURSOR assignment_cur IS
SELECT br_adjustment_id, br_ref_payment_schedule_id, customer_trx_line_id, br_ref_customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_trh_rec.customer_trx_id;
UPDATE ra_customer_trx_lines
SET br_adjustment_id = NULL
WHERE customer_trx_line_id = assignment_rec.customer_trx_line_id;
arp_proc_transaction_history.insert_transaction_history (l_trh_rec ,
l_trh_rec.transaction_history_id);
update_reserved_columns (assignment_rec.br_ref_payment_schedule_id, NULL, NULL);
SELECT cash_receipt_id
INTO p_cash_receipt_id
FROM ar_receivable_applications
where receivable_application_id = (SELECT MAX(receivable_application_id)
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_customer_trx_id
AND status = 'APP' );
SELECT receivable_application_id, cash_receipt_id
FROM ar_receivable_applications
WHERE link_to_customer_trx_id = p_customer_trx_id
AND status = 'ACTIVITY'
AND applied_payment_schedule_id = -2
AND display = 'Y'
ORDER BY receivable_application_id DESC;
SELECT br_std_receivables_trx_id
INTO l_receivables_trx_id
FROM ar_receipt_method_accounts
WHERE remit_bank_acct_use_id = l_cr_rec.remit_bank_acct_use_id
AND receipt_method_id = l_cr_rec.receipt_method_id;
update_reserved_columns (p_ps_id, NULL , NULL);
update_reserved_columns (p_ps_id, NULL , NULL);
update_reserved_columns (p_ps_id, NULL , NULL);
SELECT receipt_inherit_inv_num_flag
INTO l_receipt_inherit_inv_num_flag
FROM AR_RECEIPT_METHODS
WHERE receipt_method_id = p_batch_rec.receipt_method_id;
arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
SELECT receipt_inherit_inv_num_flag
INTO l_receipt_inherit_inv_num_flag
FROM AR_RECEIPT_METHODS
WHERE receipt_method_id = p_batch_rec.receipt_method_id;
arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,p_batch_rec.batch_id);
SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trh_rec.customer_trx_id
AND status = 'APP'
AND display = 'Y'
ORDER BY receivable_application_id DESC;
| Update the LINK_TO_TRX_HIST_ID on the |
| application |
+-----------------------------------------------*/
UPDATE ar_receivable_applications
SET LINK_TO_TRX_HIST_ID = p_trh_rec.transaction_history_id
WHERE receivable_application_id = last_application_rec.receivable_application_id;