[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cr.cash_receipt_id cash_receipt_id,
cr.set_of_books_id set_of_books_id,
cr.currency_code currency_code,
cr.receipt_method_id receipt_method_id,
sob.currency_code functional_currency,
cr.type type,
cr.amount amount,
ps.payment_schedule_id payment_schedule_id,
rma.unapplied_ccid unapplied_ccid,
rma.unidentified_ccid unidentified_ccid,
cr.tax_rate tax_rate,
cr.receivables_trx_id receivables_trx_id
FROM ar_cash_receipts cr,
ar_payment_schedules ps,
ar_receipt_method_accounts rma,
gl_sets_of_books sob
WHERE cr.cash_receipt_id = cr_id
AND ps.cash_receipt_id(+) = cr.cash_receipt_id
AND rma.receipt_method_id = cr.receipt_method_id
AND rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id
AND cr.set_of_books_id = sob.set_of_books_id
FOR UPDATE OF cr.exchange_date,
cr.exchange_rate,
cr.exchange_rate_type;
SELECT *
FROM AR_CASH_RECEIPT_HISTORY
WHERE CASH_RECEIPT_ID = cr_id
AND CURRENT_RECORD_FLAG = 'Y';
SELECT *
FROM AR_CASH_RECEIPT_HISTORY
WHERE CASH_RECEIPT_HISTORY_ID =
(SELECT PRV_STAT_CASH_RECEIPT_HIST_ID
FROM AR_CASH_RECEIPT_HISTORY
WHERE CASH_RECEIPT_ID = cr_id
AND CURRENT_RECORD_FLAG = 'Y');
SELECT SUM(nvl(d.acctd_amount_dr,0)
- nvl(d.acctd_amount_cr,0)) sum_amount,
d.code_combination_id,
crh2.acctd_amount,
crh2.acctd_factor_discount_amount,
crh2.cash_receipt_history_id,
d.source_type,
d.source_table
FROM AR_CASH_RECEIPT_HISTORY crh,
AR_CASH_RECEIPT_HISTORY crh2,
AR_DISTRIBUTIONS d
WHERE crh.cash_receipt_id = cr_id
AND crh2.cash_receipt_id = cr_id
AND d.source_id = crh.cash_receipt_history_id
AND crh2.current_record_flag = 'Y'
AND d.source_table = 'CRH'
GROUP BY crh.cash_receipt_id,
d.source_type,
d.source_table,
d.code_combination_id,
crh2.acctd_amount,
crh2.acctd_factor_discount_amount,
crh2.cash_receipt_history_id /* Bug 4443931: Added OR Below */
HAVING SUM(nvl(d.acctd_amount_dr,0) - nvl(d.acctd_amount_cr,0)) <> 0
OR (SUM(nvl(d.acctd_amount_dr,0) - nvl(d.acctd_amount_cr,0)) = 0
AND SUM(nvl(d.amount_dr,0) - nvl(d.amount_cr,0)) <> 0 ) ;
SELECT *
FROM AR_MISC_CASH_DISTRIBUTIONS
WHERE CASH_RECEIPT_ID = cr_id
AND REVERSAL_GL_DATE IS NULL;
SELECT *
FROM AR_RECEIVABLE_APPLICATIONS
WHERE CASH_RECEIPT_ID = cr_id
AND REVERSAL_GL_DATE IS NULL
ORDER BY decode(status,
'APP' ,1,
'ACTIVITY' ,2,
'ACC' ,3,
'OTHER ACC' ,4,
'UNID' ,5,
'UNAPP' ,6); --This ordering is required for pairing UNAPP with APP, ACC or UNID rec record
SELECT AMOUNT_DUE_REMAINING,
ACCTD_AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES
WHERE PAYMENT_SCHEDULE_ID = pay_id;
SELECT ARCH.ACCTD_AMOUNT,
ARCH.ACCTD_FACTOR_DISCOUNT_AMOUNT,
ARPS.ACCTD_AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES ARPS,
AR_CASH_RECEIPT_HISTORY ARCH
WHERE ARPS.CASH_RECEIPT_ID = cr_id
AND ARCH.CASH_RECEIPT_ID = cr_id
AND ARCH.CURRENT_RECORD_FLAG = 'Y';
SELECT SECONDARY_APPLICATION_REF_ID
FROM AR_RECEIVABLE_APPLICATIONS
WHERE CASH_RECEIPT_ID = cr_id
AND STATUS = 'APP'
AND APPLICATION_REF_TYPE = 'CLAIM'
AND NVL(TRANS_TO_RECEIPT_RATE,1) <> 1
AND SECONDARY_APPLICATION_REF_ID IS NOT NULL
AND DISPLAY = 'Y';
SELECT payment_channel_code
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
SELECT *
FROM ar_receivable_applications
WHERE application_ref_id = p_cr_id
and application_ref_type = 'MISC_RECEIPT'
and display = 'Y';
SELECT type
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id;
SELECT upgrade_method
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id;
SELECT *
FROM ar_distributions
WHERE source_table = 'CRH'
AND source_id = p_source_id
AND ((DECODE(p_status,'CONFIRMED',DECODE(source_type,'CONFIRMATION','Y','N'),
'REMITTED' ,DECODE(source_type,'REMITTANCE','Y','N'),
'CLEARED' ,DECODE(source_type,'CASH','Y','N'),
'RISK_ELIMINATED',DECODE(source_type,'FACTOR','Y',
'SHORT_TERM_DEBT','Y','N')) = 'Y')
OR
(source_type = 'BANK_CHARGES'));
SELECT RATE_ADJUSTMENT_ID ,
CASH_RECEIPT_ID ,
OLD_EXCHANGE_RATE ,
NEW_EXCHANGE_RATE ,
OLD_EXCHANGE_RATE_TYPE,
NEW_EXCHANGE_RATE_TYPE,
OLD_EXCHANGE_DATE ,
NEW_EXCHANGE_DATE ,
GAIN_LOSS ,
GL_DATE ,
GL_POSTED_DATE
FROM ar_rate_adjustments
WHERE cash_receipt_id = p_cr_id
AND rate_adjustment_id = p_rate_adjustment_id;
SELECT COUNT(CUSTOMER_TRX_LINE_ID),
CUSTOMER_TRX_LINE_ID
FROM ra_customer_trx_lines_gt
WHERE CUSTOMER_TRX_ID = p_customer_trx_id
GROUP BY CUSTOMER_TRX_LINE_ID;
SELECT ACCTD_AMOUNT_DUE_REMAINING ,
AMOUNT_DUE_REMAINING ,
CHRG_ACCTD_AMOUNT_REMAINING,
CHRG_AMOUNT_REMAINING ,
FRT_ADJ_ACCTD_REMAINING ,
FRT_ADJ_REMAINING ,
FRT_ED_ACCTD_AMOUNT ,
FRT_ED_AMOUNT ,
FRT_UNED_ACCTD_AMOUNT ,
FRT_UNED_AMOUNT ,
customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id;
new_adj.last_updated_by := new_lub;
new_adj.last_update_date := new_lud;
new_adj.last_update_login := new_lul;
old_crh.program_update_date := NULL;
old_crh.last_updated_by := new_adj.last_updated_by;
old_crh.last_update_date := new_adj.last_update_date;
old_crh.last_update_login := new_adj.last_update_login;
arp_standard.debug('Insert the new cash_receipt_history record');
new_crh_id := arp_cash_receipt_history.InsertRecord
(amount => old_crh.amount,
acctd_amount => old_crh.acctd_amount,
cash_receipt_id => old_crh.cash_receipt_id,
factor_flag => old_crh.factor_flag,
first_posted_record_flag => old_crh.first_posted_record_flag,
gl_date => old_crh.gl_date,
postable_flag => old_crh.postable_flag,
status => old_crh.status,
trx_date => old_crh.trx_date,
acctd_factor_discount_amount => old_crh.acctd_factor_discount_amount,
account_code_combination_id => old_crh.account_code_combination_id,
bank_charge_account_ccid => old_crh.bank_charge_account_ccid,
batch_id => old_crh.batch_id,
current_record_flag => old_crh.current_record_flag,
exchange_date => old_crh.exchange_date,
exchange_rate => old_crh.exchange_rate,
exchange_rate_type => old_crh.exchange_rate_type,
factor_discount_amount => old_crh.factor_discount_amount,
gl_posted_date => old_crh.gl_posted_date,
posting_control_id => old_crh.posting_control_id,
reversal_cash_rec_hist_id => old_crh.reversal_cash_receipt_hist_id,
reversal_gl_date => old_crh.reversal_gl_date,
reversal_gl_posted_date => old_crh.reversal_gl_posted_date,
reversal_posting_control_id => old_crh.reversal_posting_control_id,
request_id => old_crh.request_id,
program_application_id => old_crh.program_application_id,
program_id => old_crh.program_id,
program_update_date => old_crh.program_update_date,
created_by => old_crh.created_by,
creation_date => old_crh.creation_date,
last_updated_by => old_crh.last_updated_by,
last_update_date => old_crh.last_update_date,
last_update_login => old_crh.last_update_login,
prv_stat_cash_rec_hist_id => old_crh.prv_stat_cash_receipt_hist_id,
created_from => old_crh.created_from,
reversal_created_from => old_crh.reversal_created_from);
arp_standard.debug('Update the rate_adjustments info on the old cash_receipt_history record');
new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login);
| Update Cash Receipts record with New exchange rate Information |
*----------------------------------------------------------------*/
arp_standard.debug('Update Cash Receipts record with New exchange rate Information');
new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login);
l_dist_rec.last_update_date := new_lud;
l_dist_rec.last_updated_by := new_lub;
l_dist_rec.last_update_login := new_lul;
arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
l_dist_rec.last_update_date := new_lud;
l_dist_rec.last_updated_by := new_lub;
l_dist_rec.last_update_login := new_lul;
arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
/* If the net receipt amount is zero, we need to insert the
distribution amount separately */
/*
IF old_crh.amount = 0 AND old_crh.status <> 'APPROVED' THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug(' Receipt Amount is zero.');
SELECT decode( old_crh.status,
'CONFIRMED','CONFIRMATION',
'REMITTED', 'REMITTANCE',
'CLEARED', 'CASH' )
INTO l_dist_rec.source_type
FROM dual;
l_dist_rec.last_update_date := new_lud;
l_dist_rec.last_updated_by := new_lub;
l_dist_rec.last_update_login := new_lul;
arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
old_crh_reverse_rec.program_update_date := NULL;
old_crh_reverse_rec.last_updated_by := new_adj.last_updated_by;
old_crh_reverse_rec.last_update_date := new_adj.last_update_date;
old_crh_reverse_rec.last_update_login := new_adj.last_update_login;
old_reverse_crh_id := arp_cash_receipt_history.InsertRecord
(amount => old_crh_reverse_rec.amount,
acctd_amount => old_crh_reverse_rec.acctd_amount,
cash_receipt_id => old_crh_reverse_rec.cash_receipt_id,
factor_flag => old_crh_reverse_rec.factor_flag,
first_posted_record_flag => old_crh_reverse_rec.first_posted_record_flag,
gl_date => old_crh_reverse_rec.gl_date,
postable_flag => old_crh_reverse_rec.postable_flag,
status => old_crh_reverse_rec.status,
trx_date => old_crh_reverse_rec.trx_date,
acctd_factor_discount_amount => old_crh_reverse_rec.acctd_factor_discount_amount,
account_code_combination_id => old_crh_reverse_rec.account_code_combination_id,
bank_charge_account_ccid => old_crh_reverse_rec.bank_charge_account_ccid,
batch_id => old_crh_reverse_rec.batch_id,
current_record_flag => old_crh_reverse_rec.current_record_flag,
exchange_date => old_crh_reverse_rec.exchange_date,
exchange_rate => old_crh_reverse_rec.exchange_rate,
exchange_rate_type => old_crh_reverse_rec.exchange_rate_type,
factor_discount_amount => old_crh_reverse_rec.factor_discount_amount,
gl_posted_date => old_crh_reverse_rec.gl_posted_date,
posting_control_id => old_crh_reverse_rec.posting_control_id,
reversal_cash_rec_hist_id => old_crh_reverse_rec.reversal_cash_receipt_hist_id,
reversal_gl_date => old_crh_reverse_rec.reversal_gl_date,
reversal_gl_posted_date => old_crh_reverse_rec.reversal_gl_posted_date,
reversal_posting_control_id => old_crh_reverse_rec.reversal_posting_control_id,
request_id => old_crh_reverse_rec.request_id,
program_application_id => old_crh_reverse_rec.program_application_id,
program_id => old_crh_reverse_rec.program_id,
program_update_date => old_crh_reverse_rec.program_update_date,
created_by => old_crh_reverse_rec.created_by,
creation_date => old_crh_reverse_rec.creation_date,
last_updated_by => old_crh_reverse_rec.last_updated_by,
last_update_date => old_crh_reverse_rec.last_update_date,
last_update_login => old_crh_reverse_rec.last_update_login,
prv_stat_cash_rec_hist_id => old_crh_reverse_rec.prv_stat_cash_receipt_hist_id,
created_from => old_crh_reverse_rec.created_from,
reversal_created_from => old_crh_reverse_rec.reversal_created_from);
l_dist_rec.last_update_date := new_lud;
l_dist_rec.last_updated_by := new_lub;
l_dist_rec.last_update_login := new_lul;
arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
(new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login,
new_adj.created_by,
new_adj.creation_date,
new_adj.cash_receipt_id,
old_misc.code_combination_id,
old_misc.set_of_books_id,
GREATEST(new_adj.gl_date, old_misc.gl_date),
old_misc.percent,
-1 * old_misc.amount,
old_misc.comments,
NULL,
old_misc.apply_date,
-3,
NULL,
NULL,
NULL,
NULL,
-1 * old_misc.acctd_amount,
old_misc.ussgl_transaction_code,
old_misc.ussgl_transaction_code_context,
'RATE ADJUSTMENT TRIGGER',
GREATEST(new_adj.gl_date, old_misc.gl_date),
--BUG#5201086
old_misc.cash_receipt_history_id);
SELECT code_combination_id
INTO l_ae_doc_rec.gl_tax_acct
FROM ar_distributions
WHERE source_id = old_misc.misc_cash_distribution_id
AND source_table ='MCD'
AND source_type = 'TAX';
| Update the Reversed Distribution |
*----------------------------------*/
arp_misc_cd.upd_reversal_gl_date
(old_misc.misc_cash_distribution_id,
GREATEST(new_adj.gl_Date,old_misc.gl_date),
new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login,
--BUG#5201086
old_misc.cash_receipt_history_id);
| Insert the new Distribution |
*-----------------------------*/
temp_num := arp_misc_cd.ins_misc_cash_distributions
(new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login,
new_adj.created_by,
new_adj.creation_date,
new_adj.cash_receipt_id,
old_misc.code_combination_id,
old_misc.set_of_books_id,
GREATEST(new_adj.gl_date, old_misc.gl_date),
old_misc.percent,
old_misc.amount,
old_misc.comments,
NULL,
old_misc.apply_date,
-3,
NULL,
NULL,
NULL,
NULL,
dist_acctd_amount,
old_misc.ussgl_transaction_code,
old_misc.ussgl_transaction_code_context,
'RATE ADJUSTMENT TRIGGER',
NULL,
--BUG#5201086
new_crh_id);
| Update Payment Schedules Record, resetting amount_due_remaining |
| and change exchange rate information |
*-----------------------------------------------------------------*/
cr_acctd_amount := arp_standard.functional_amount(cr.amount,
cr.functional_currency,
new_adj.new_exchange_rate,
NULL,
NULL);
new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login);
SELECT NVL(MAX_WRTOFF_AMOUNT,0)
INTO l_max_wrt_off_amount
FROM AR_SYSTEM_PARAMETERS;
SELECT status into l_status
FROM ar_cash_receipt_history crh
WHERE crh.current_record_flag = 'Y'
-- AND crh.first_posted_record_flag='Y'
-- AND created_from ='BR_FACTORED_WITH_RECOURSE'
AND crh.cash_receipt_id= cr.cash_receipt_id
AND EXISTS
(select 'Y' from ar_Cash_receipt_history crh1
where crh1.cash_receipt_id=cr.cash_receipt_id
AND crh1.cash_receipt_id=crh.cash_receipt_id
AND crh1.created_from ='BR_FACTORED_WITH_RECOURSE'
AND crh1.status = 'REMITTED');
select count(*) into l_count from AR_RECEIVABLE_APPLICATIONS
WHERE CASH_RECEIPT_ID = cr.cash_receipt_id
AND nvl(applied_payment_schedule_id,-2) <> -2;
ins_ra_rec.last_updated_by := new_adj.last_updated_by;
ins_ra_rec.last_update_date := new_adj.last_update_date;
ins_ra_rec.last_update_login := new_adj.last_update_login;
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, temp_num );
arp_process_application.update_claim(
p_claim_id => l_claim_id
, p_invoice_ps_id => old_rec_app.applied_payment_schedule_id
, p_customer_trx_id => old_rec_app.customer_trx_id
, p_amount => l_claim_amount
, p_amount_applied => old_rec_app.amount_applied
, p_apply_date => old_rec_app.apply_date
, p_cash_receipt_id => cr.cash_receipt_id
, p_receipt_number => NULL
, p_action_type => 'A'
, 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_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
INSERT INTO ar_line_app_detail_gt
( ACCTD_AMOUNT
,REF_ACCOUNT_CLASS
,AMOUNT
,APP_LEVEL
,BASE_CURRENCY
,ACTIVITY_BUCKET
,CCID
,GT_ID
,LEDGER_ID
,ORG_ID
,REF_CUSTOMER_TRX_ID
,REF_CUSTOMER_TRX_LINE_ID
,REF_CUST_TRX_LINE_GL_DIST_ID
,REF_LINE_ID
,SOURCE_ID
,SOURCE_TABLE
,SOURCE_TYPE
,TAXABLE_ACCTD_AMOUNT
,TAXABLE_AMOUNT
,TAX_INC_FLAG
,TAX_LINK_ID
,TO_CURRENCY
,REF_MF_DIST_FLAG)
SELECT
DECODE(ard.activity_bucket,
'APP_LINE' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_TAX' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_FRT' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_CHRG' , -(NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
-(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0))) -- ACCTD_AMOUNT
,ard.ref_account_class -- REF_ACCOUNT_CLASS
,DECODE(ard.activity_bucket,
'APP_LINE' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_TAX' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_FRT' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_CHRG' , -(NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
-(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0))) -- AMOUNT
,'LINE' -- APP_LEVEL
,arp_global.functional_currency -- BASE_CURRENCY
,ard.ACTIVITY_BUCKET -- ACTIVITY_BUCKET
,ard.code_combination_id -- CCID
,l_gt_id -- GT_ID
,ora.set_of_books_id -- LEDGER_ID
,ard.org_id -- ORG_ID
,ora.applied_customer_trx_id -- REF_CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
,ard.ref_line_id -- REF_LINE_ID
,ard.source_id -- SOURCE_ID
,ard.source_table -- SOURCE_TABLE
,ora.application_type -- SOURCE_TYPE
,'' -- TAXABLE_ACCTD_AMOUNT
,'' -- TAXABLE_AMOUNT
,'' -- TAX_INC_FLAG
,'' -- TAX_LINK_ID
,trx.invoice_currency_code -- TO_CURRENCY
,'' -- REF_MF_DIST_FLAG
FROM ar_distributions ard,
ar_receivable_applications ora,
ra_customer_trx trx
WHERE ora.receivable_application_id = old_rec_app.receivable_application_id
AND ard.source_table = 'RA'
AND ard.source_id = ora.receivable_application_id
AND ard.activity_bucket IS NOT NULL
AND ora.applied_customer_trx_id = trx.customer_trx_id;
| Update reversed record, setting DISPLAY and |
| REVERSAL_GL_DATE |
*---------------------------------------------*/
arp_app_pkg.fetch_p( old_rec_app.receivable_application_id, upd_ra_rec );
upd_ra_rec.last_updated_by := new_adj.last_updated_by;
upd_ra_rec.last_update_date := new_adj.last_update_date;
upd_ra_rec.last_update_login := new_adj.last_update_login;
arp_app_pkg.update_p(upd_ra_rec);
| Calculate new acctd_amount and update Payment Schedules |
| (for Applied applications) |
| |
*---------------------------------------------------------*/
IF (old_rec_app.status in ('APP','ACTIVITY')) THEN
/*-------------------------------------------*
| |
| Calculate ACCTD_AMOUNT of new application |
| |
*-------------------------------------------*/
ps_remaining.amount_due_remaining :=
ps_remaining.amount_due_remaining +
nvl(old_rec_app.amount_applied_from, old_rec_app.amount_applied);
UPDATE ar_payment_schedules
SET status = 'OP',
gl_date_closed = ARP_GLOBAL.G_MAX_DATE,
actual_date_closed = ARP_GLOBAL.G_MAX_DATE
WHERE payment_schedule_id = cr.payment_schedule_id;
arp_cash_receipts_pkg.update_p(l_cr_rec, cr.cash_receipt_id);
| Update Payment Schedules with new |
| amount due remaining |
*-----------------------------------*/
arp_pay_sched.upd_amt_due_remaining(cr.payment_schedule_id,
ps_remaining.amount_due_remaining,
ps_remaining.acctd_amount_due_remaining,
new_adj.last_updated_by,
new_adj.last_update_date,
new_adj.last_update_login);
ins_ra_rec.last_updated_by := new_adj.last_updated_by;
ins_ra_rec.last_update_date := new_adj.last_update_date;
SELECT exchange_rate
INTO l_exchange_rate
FROM ar_payment_schedules
WHERE payment_schedule_id = old_rec_app.applied_payment_schedule_id;
ins_ra_rec.last_update_login := new_adj.last_update_login;
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, temp_num );
Update the gl_date_closed of concerned invoice in the payment schedules if the status is closed and
the current gl_date closed is less than gl_date of the reate adjustment */
SELECT gl_date_closed,status
INTO l_inv_gl_date_closed,l_inv_ps_status
FROM ar_payment_schedules
WHERE payment_schedule_id=ins_ra_rec.applied_payment_schedule_id;
UPDATE ar_payment_schedules
SET gl_date_closed=ins_ra_rec.gl_date
WHERE payment_schedule_id=ins_ra_rec.applied_payment_schedule_id;
ins_ra_rec.last_updated_by := new_adj.last_updated_by;
ins_ra_rec.last_update_date := new_adj.last_update_date;
ins_ra_rec.last_update_login := new_adj.last_update_login;
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, temp_num );
INSERT INTO ar_line_app_detail_gt
( ACCTD_AMOUNT
,REF_ACCOUNT_CLASS
,AMOUNT
,APP_LEVEL
,BASE_CURRENCY
,ACTIVITY_BUCKET
,CCID
,GT_ID
,LEDGER_ID
,ORG_ID
,REF_CUSTOMER_TRX_ID
,REF_CUSTOMER_TRX_LINE_ID
,REF_CUST_TRX_LINE_GL_DIST_ID
,REF_LINE_ID
,SOURCE_ID
,SOURCE_TABLE
,SOURCE_TYPE
,TAXABLE_ACCTD_AMOUNT
,TAXABLE_AMOUNT
,TAX_INC_FLAG
,TAX_LINK_ID
,TO_CURRENCY
,REF_MF_DIST_FLAG)
SELECT
DECODE(ard.activity_bucket,
'APP_LINE' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_TAX' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_FRT' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
'APP_CHRG' , (NVL(ard.acctd_amount_dr,0)-NVL(ard.acctd_amount_cr,0)),
(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0))) -- ACCTD_AMOUNT
,ard.ref_account_class -- REF_ACCOUNT_CLASS
,DECODE(ard.activity_bucket,
'APP_LINE' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_TAX' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_FRT' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
'APP_CHRG' , (NVL(ard.amount_dr,0)-NVL(ard.amount_cr,0)),
(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0))) -- AMOUNT
,'LINE' -- APP_LEVEL
,arp_global.functional_currency -- BASE_CURRENCY
,ard.ACTIVITY_BUCKET -- ACTIVITY_BUCKET
,ard.code_combination_id -- CCID
,l_gt_id -- GT_ID
,ora.set_of_books_id -- LEDGER_ID
,ard.org_id -- ORG_ID
,ora.applied_customer_trx_id -- REF_CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
,ard.ref_line_id -- REF_LINE_ID
,ard.source_id -- SOURCE_ID
,ard.source_table -- SOURCE_TABLE
,ora.application_type -- SOURCE_TYPE
,'' -- TAXABLE_ACCTD_AMOUNT
,'' -- TAXABLE_AMOUNT
,'' -- TAX_INC_FLAG
,'' -- TAX_LINK_ID
,trx.invoice_currency_code -- TO_CURRENCY
,'' -- REF_MF_DIST_FLAG
FROM ar_distributions ard,
ar_receivable_applications ora,
ra_customer_trx trx
WHERE ora.receivable_application_id = old_rec_app.receivable_application_id
AND ard.source_table = 'RA'
AND ard.source_id = ora.receivable_application_id
AND ard.activity_bucket IS NOT NULL
AND ora.applied_customer_trx_id = trx.customer_trx_id;
UPDATE ra_customer_trx_lines SET
ACCTD_AMOUNT_DUE_REMAINING = l_ACCTD_AMOUNT_DUE_REMAINING(i),
AMOUNT_DUE_REMAINING = l_AMOUNT_DUE_REMAINING(i),
CHRG_ACCTD_AMOUNT_REMAINING= l_CHRG_ACCTD_AMOUNT_REMAINING(i),
CHRG_AMOUNT_REMAINING = l_CHRG_AMOUNT_REMAINING(i),
FRT_ADJ_ACCTD_REMAINING = l_FRT_ADJ_ACCTD_REMAINING(i),
FRT_ADJ_REMAINING = l_FRT_ADJ_REMAINING(i),
FRT_ED_ACCTD_AMOUNT = l_FRT_ED_ACCTD_AMOUNT(i),
FRT_ED_AMOUNT = l_FRT_ED_AMOUNT(i),
FRT_UNED_ACCTD_AMOUNT = l_FRT_UNED_ACCTD_AMOUNT(i),
FRT_UNED_AMOUNT = l_FRT_UNED_AMOUNT(i)
WHERE customer_trx_line_id = l_customer_trx_line_id(i);
arp_app_pkg.update_p( net_ra_rec );
arp_app_pkg.update_p( net_ra_rec );
ins_ra_rec.last_updated_by := new_adj.last_updated_by;
ins_ra_rec.last_update_date := new_adj.last_update_date;
ins_ra_rec.last_update_login := new_adj.last_update_login;
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, temp_num );
ins_ra_rec.last_updated_by := new_adj.last_updated_by;
ins_ra_rec.last_update_date := new_adj.last_update_date;
ins_ra_rec.last_update_login := new_adj.last_update_login;
ins_ra_rec.program_update_date := NULL;
arp_app_pkg.insert_p( ins_ra_rec, temp_num );
Update the gl_date_closed of reciept in the payment schedules if the status is closed and
the current gl_date closed is less than gl_date of the rate adjustment */
SELECT gl_date_closed,status
INTO l_rct_gl_date_closed,l_rct_ps_status
FROM ar_payment_schedules
WHERE payment_schedule_id= cr.payment_schedule_id;
UPDATE ar_payment_schedules
SET gl_date_closed = new_crh.gl_date
WHERE payment_schedule_id = cr.payment_schedule_id;