[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_row_inserted VARCHAR2(1) := null;
select DEAL_NO, PAYMENT_SCHEDULE_CODE, DEAL_TYPE,
DEAL_SUBTYPE, PRODUCT_TYPE, INTEREST_RATE,
PI_AMOUNT_DUE, CURRENCY, SETTLE_DATE,
PORTFOLIO_CODE,COMPANY_CODE,DEAL_DATE,
CPARTY_CODE,CLIENT_CODE,DEALER_CODE
from XTR_DEALS_V d
where d.DEAL_TYPE = 'RTMM'
and d.DEAL_SUBTYPE = nvl(p_deal_subtype, d.DEAL_SUBTYPE)
and d.CURRENCY = nvl(p_currency, d.CURRENCY)
and d.PAYMENT_SCHEDULE_CODE = nvl(p_payment_schedule_code, d.PAYMENT_SCHEDULE_CODE)
and d.STATUS_CODE = 'CURRENT'
and exists( select 'ANY TRANS'
from XTR_ROLLOVER_TRANSACTIONS_V t
where nvl(t.STATUS_CODE, 'CURRENT') = 'CURRENT'
and t.BALANCE_OUT between nvl(p_min_balance, 0) and
nvl(p_max_balance, t.BALANCE_OUT + 1)
and t.SETTLE_DATE is NULL
and t.START_DATE >= l_effective_from_date
and t.START_DATE >= nvl(d.SETTLE_DATE, t.START_DATE)
--and nvl(RATE_EFFECTIVE_CREATED,l_effective_from_date )
-- <= l_effective_from_date
and t.DEAL_NUMBER = d.DEAL_NO);
select 'Y'
from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = p_deal_no
and START_DATE = p_date;
select nvl(max(TRANSACTION_NUMBER),0) + 1
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = p_deal_no;
select PI_AMOUNT_DUE,
INTEREST_RATE,
PI_AMOUNT_RECEIVED,
MATURITY_DATE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = p_deal_no
and START_DATE < p_date
and MATURITY_DATE >= p_date
and nvl(PI_AMOUNT_DUE,0) <> 0
order by START_DATE desc,TRANSACTION_NUMBER desc;
select dealer_code
from xtr_dealer_codes_v
where user_id = fnd_global.user_id;
update XTR_ROLLOVER_TRANSACTIONS_V
set INTEREST_RATE = p_new_interest_rate,
RATE_EFFECTIVE_CREATED = l_created_on, -- AW 7/15 sysdate,
PI_AMOUNT_DUE = nvl(l_new_pi_amount_due, PI_AMOUNT_DUE)
where DEAL_NUMBER = deal.DEAL_NO
and nvl(STATUS_CODE, 'CURRENT') = 'CURRENT'
and (BALANCE_OUT between nvl(p_min_balance, 0) and
nvl(p_max_balance, BALANCE_OUT + 1))
and SETTLE_DATE is NULL
and START_DATE >= l_effective_date;
update XTR_DEALS
set INTEREST_RATE = p_new_interest_rate
where DEAL_NO = deal.DEAL_NO;
insert into XTR_ROLLOVER_TRANSACTIONS_V
(DEAL_NUMBER,
TRANSACTION_NUMBER,
DEAL_TYPE,
RATE_FIXING_DATE,
START_DATE,
MATURITY_DATE,
INTEREST_RATE,
NO_OF_DAYS,
PI_AMOUNT_DUE,
BALANCE_OUT_BF,
BALANCE_OUT,
CREATED_BY,
CREATED_ON,
PRINCIPAL_ADJUST,
STATUS_CODE,
PORTFOLIO_CODE,
CURRENCY,
DEAL_SUBTYPE,
COMPANY_CODE,
DEAL_DATE,
PRODUCT_TYPE,
CPARTY_CODE,
CLIENT_CODE,
DEALER_CODE)
values
(deal.DEAL_NO,
l_tran_num,
'RTMM',
l_effective_date,
l_effective_date,
l_maturity_date,
p_new_interest_rate,
(l_maturity_date - l_effective_date),
l_pi_amount_due,
0,
0,
'-1',
SYSDATE,
0,
'CURRENT',
deal.PORTFOLIO_CODE,
deal.CURRENCY,
deal.DEAL_SUBTYPE,
deal.COMPANY_CODE,
deal.DEAL_DATE,
deal.PRODUCT_TYPE,
deal.CPARTY_CODE,
deal.CLIENT_CODE,
deal.DEALER_CODE);
l_row_inserted := 'Y';
/* AW 7/15 Bug 914129 Should not allow to update previous actions for this deal !!!
update XTR_term_actions_V
set NEW_INTEREST_RATE = p_new_interest_rate,
EFFECTIVE_FROM_DATE = sysdate,
CREATED_ON = SYSDATE,
CREATED_BY = '-1'
where deal_no = deal.DEAL_NO;
insert into XTR_term_actions_V(
DEAL_NO,
NEW_INTEREST_RATE,
EFFECTIVE_FROM_DATE,
CREATED_ON,
CREATED_BY,
MASS_RATE_UPDATE)
values (deal.DEAL_NO,
p_new_interest_rate,
l_effective_date, -- AW 7/15 Bug 914129 sysdate,
l_created_on, -- AW 7/15 Bug 914129 sysdate,
p_created_by,
'Y');
select deal.DEAL_NO,
p_new_interest_rate,
l_effective_date, -- AW 7/15 Bug 914129 sysdate,
l_created_on, -- AW 7/15 Bug 914129 sysdate,
'-1'
from dual;
l_row_inserted,
l_effective_date,
l_tran_num,
'N',
'N',
g_expected_balance_bf,
g_balance_out_bf,
g_accum_interest_bf,
g_principal_adjust,
null,
null,
null,
null,
null );
update XTR_RATE_SETS
set CONCURRENT_REQUEST = 'Y'
where EFFECTIVE_FROM = to_date(p_effective_from_date,'YYYY/MM/DD')
and RATE = p_new_interest_rate
and DEAL_SUBTYPE = p_deal_subtype
and PRODUCT_TYPE = p_payment_schedule_code
and CURRENCY = p_currency
and LOW_RANGE = p_min_balance
and HIGH_RANGE = p_max_balance;
select PAYMENT_SCHEDULE_CODE, DEAL_TYPE,DEAL_SUBTYPE,
PRODUCT_TYPE, INTEREST_RATE, MATURITY_DATE,
PI_AMOUNT_DUE, CURRENCY
from XTR_DEALS_V d
where d.DEAL_NO = p_deal_number;
select START_DATE, MATURITY_DATE,
EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,
PRINCIPAL_ADJUST,
INTEREST_RATE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = p_deal_number
and STATUS_CODE = 'CURRENT'
and MATURITY_DATE >= p_effective_from_date
and START_DATE < p_effective_from_date
order by START_DATE asc,TRANSACTION_NUMBER asc;
select START_DATE,EXPECTED_BALANCE_BF, PRINCIPAL_ADJUST
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = p_deal_number
and STATUS_CODE = 'CURRENT'
and START_DATE >= p_effective_from_date
order by START_DATE asc,TRANSACTION_NUMBER asc;
select PAYMENT_FREQUENCY,
JAN_YN,FEB_YN,MAR_YN,APR_YN,MAY_YN,JUN_YN,
JUL_YN,AUG_YN,SEP_YN,OCT_YN,NOV_YN,DEC_YN
from XTR_PAYMENT_SCHEDULE_V
where PAYMENT_SCHEDULE_CODE = l_payment_schedule_code
and DEAL_TYPE = 'RTMM'
and DEAL_SUBTYPE = l_deal_subtype;
select ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_currency;
select round((nvl(l_expected_balance_bf,0) + nvl(l_principal_adjust,0)) *
power((1 + (nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),l_tot_pymts)/
((power((1 + (nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),l_tot_pymts) - 1) /
(nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),
NVL(l_round,2))
into p_new_pi_amount_due
from dual;