[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select XTR_BATCHES_S.NEXTVAL
from DUAL;
select 1
from XTR_BATCHES
where BATCH_ID = l_batch_id;
select XTR_REVALUATION_DETAILS_S.NEXTVAL
from DUAL;
select RIC_CODE, CURRENCY_A, CURRENCY_B, TERM_TYPE, TERM_LENGTH,
LAST_DOWNLOAD_TIME, NOS_OF_DAYS
from XTR_MARKET_PRICES
where TERM_TYPE in ('O', 'V');
select a.UNIQUE_PERIOD_ID,a.RATE_DATE,a.BID_RATE, a.OFFER_RATE
from XTR_INTEREST_PERIOD_RATES a
where a.UNIQUE_PERIOD_ID = l_vol_code
and ((a.CONTRA_OPTION_CCY = p_CURRENCYB and p_CURRENCYB is NOT NULL) or
(p_CURRENCYB is NULL))
and a.RATE_DATE < trunc(l_end_date+1)
order by a.RATE_DATE desc;
select RIC_CODE,TERM_LENGTH,TERM_YEAR,CURRENCY_A,CURRENCY_B,TERM_TYPE, DAY_COUNT_BASIS,
nvl(BID_PRICE, 0),nvl(ASK_PRICE,0),LAST_DOWNLOAD_TIME,
NOS_OF_DAYS
from XTR_MARKET_PRICES
where TERM_TYPE not in('O','V','A')
and ((TERM_TYPE = 'S' and (CURRENCY_A = 'USD' or CURRENCY_B = 'USD'))
or (TERM_TYPE <> 'S'));
select a.RATE_DATE,
nvl(a.BID_RATE_AGAINST_USD,0),nvl(a.OFFER_RATE_AGAINST_USD,0)
from XTR_SPOT_RATES a
where a.CURRENCY = decode(p_CURRENCYA,'USD',p_CURRENCYB,p_CURRENCYA)
and a.RATE_DATE < trunc(l_end_date+1)
order by a.RATE_DATE desc;
select a.RATE_DATE,nvl(a.BID_RATE,0), nvl(a.OFFER_RATE,0)
from XTR_INTEREST_PERIOD_RATES a
where a.UNIQUE_PERIOD_ID = p_REVAL_TYPE
and a.RATE_DATE < trunc(l_end_date+1)
order by a.RATE_DATE desc;
select distinct currency,bond_issue
from XTR_DEALS
where deal_type='BOND'
and status_code='CURRENT'
and maturity_date >l_end_date;
select ric_code
from XTR_BOND_ISSUES
where bond_issue_code=l_bond_issue
and ric_code IS NOT NULL;
select a.RATE_DATE,nvl(a.BID_RATE,0), nvl(a.OFFER_RATE,0)
from XTR_INTEREST_PERIOD_RATES a
where a.UNIQUE_PERIOD_ID = l_ric_code
and a.RATE_DATE < trunc(l_end_date+1)
order by a.RATE_DATE desc;
insert into XTR_BATCHES
(batch_id, company_code, period_start, period_end, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, gl_group_id, upgrade_batch)
values
(p_BATCH_ID, l_company, nvl(l_start_date, to_date('01/01/1980', 'DD/MM/YYYY')),
l_end_date, fnd_global.user_id, l_sysdate, fnd_global.user_id,
l_sysdate, fnd_global.login_id, null, nvl(l_upgrade_batch, 'N'));
update XTR_BATCHES
set last_updated_by = fnd_global.user_id,
last_update_date = l_sysdate
where BATCH_ID = l_batch_id;
insert into XTR_REVALUATION_RATES
(batch_id,unique_ref_num,company_code,period_from,period_to,reval_type,
currencya,bid,ask,entered_on,
entered_by,rate_date,volatility_or_rate,day_mth,day_count_basis,currencyb,period_ref1,
period_ref2,number_of_days,created_on,created_by)
values
(p_BATCH_ID,p_UNIQUE_REF_NUM,l_company,l_start_date,l_end_date,
l_vol_code,p_CURRENCYA,p_BID,p_ASK,
l_sysdate,fnd_global.user_id,nvl(p_RATE_DATE,l_end_date),p_VOLATILITY_OR_RATE,
p_DAY_MTH,p_DAY_COUNT_BASIS,p_CURRENCYB,p_PERIOD_REF1,p_PERIOD_REF2,
IM_NOS_OF_DAYS,l_sysdate,fnd_global.user_id);
insert into XTR_REVALUATION_RATES
(batch_id,unique_ref_num,company_code,period_from,period_to,reval_type,
currencya,bid,ask,entered_on,
entered_by,rate_date,volatility_or_rate,day_mth,day_count_basis,currencyb,period_ref1,
period_ref2,number_of_days,created_on,created_by)
values
(p_BATCH_ID,p_UNIQUE_REF_NUM,l_company,l_start_date,l_end_date,
p_REVAL_TYPE,p_CURRENCYA,p_BID,p_ASK,
l_sysdate,fnd_global.user_id,nvl(p_RATE_DATE,l_end_date),p_VOLATILITY_OR_RATE,
p_DAY_MTH,p_day_count_basis,p_CURRENCYB,p_PERIOD_REF1,p_PERIOD_REF2,
IM_NOS_OF_DAYS,l_sysdate,fnd_global.user_id);
insert into XTR_REVALUATION_RATES
(batch_id,unique_ref_num,company_code,period_from,period_to,reval_type,
currencya,bid, ask,entered_on,
entered_by,rate_date,volatility_or_rate,day_mth,day_count_basis,currencyb,period_ref1,
period_ref2,number_of_days,created_on,created_by)
values
(p_BATCH_ID,p_UNIQUE_REF_NUM,l_company,l_start_date,l_end_date,
p_REVAL_TYPE,p_CURRENCYA,p_BID,p_ASK,
l_sysdate,fnd_global.user_id,nvl(p_RATE_DATE,l_end_date),p_VOLATILITY_OR_RATE,
p_DAY_MTH,p_DAY_COUNT_BASIS,p_CURRENCYB,p_PERIOD_REF1,p_PERIOD_REF2,
IM_NOS_OF_DAYS,l_sysdate,fnd_global.user_id);
/* This procedure calculation Revaluation details and insert into table*/
/* Before calculating reval detail, we need to make sure: */
/* (1) the previous batch for the same company has been run */
/* (2) The batch is not run yet */
/***********************************************************************/
PROCEDURE CALC_REVALS(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_company IN VARCHAR2,
p_batch_id IN NUMBER) IS
Cursor CHK_PRE_BATCH is
Select 'Y'
From XTR_BATCHES CUR, XTR_BATCHES PRE,
XTR_BATCH_EVENTS EV
Where cur.batch_id = p_batch_id
and cur.company_code = pre.company_code
and (( cur.batch_id > pre.batch_id
and pre.batch_id = ev.batch_id
and ev.event_code = 'REVAL' ) or pre.batch_id is null)
order by pre.batch_id desc;
Select 'Y'
From XTR_BATCH_EVENTS
Where batch_id = p_batch_id
and event_code = 'REVAL';
SELECT SUBSTR(value,1,DECODE(INSTR(value,','),0,LENGTH(value),INSTR(value,',')-1) )
into l_dirname
from v$parameter
where name = 'utl_file_dir';
Select * from XTR_BDO_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_BOND_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_FRA_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_ELIGIBLE_HEDGES_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_FX_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code
and deal_no in (select h.primary_code
from xtr_hedge_relationships H, xtr_revaluation_details R
where h.instrument_item_flag = 'U'
and r.batch_id = p_batch_id
and h.hedge_attribute_id = r.deal_no);
Select * from XTR_FX_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code
and deal_no not in (select h.primary_code
from xtr_hedge_relationships H, xtr_revaluation_details R
where h.instrument_item_flag = 'U'
and r.batch_id = p_batch_id
and h.hedge_attribute_id = r.deal_no);
Select * from XTR_FXO_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select distinct deal_no, currencya from XTR_IG_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_IRO_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_IRS_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_NI_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select distinct deal_no, deal_subtype, currencya, portfolio_code
from XTR_ONC_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_SWPTN_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_RTMM_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code
and deal_no in (select deal_no from xtr_deals where deal_type = 'RTMM'
and last_reval_batch_id is null);
select * from XTR_STOCK_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select * from XTR_TMM_ELIGIBLE_DEALS_V
where eligible_date <= l_batch_end and company_code = l_company_code;
Select distinct ca.account_no, ca.currencya, dda.deal_number,
ca.portfolio_code
from XTR_CA_ELIGIBLE_DEALS_V CA,
XTR_DEAL_DATE_AMOUNTS DDA
where ca.company_code = l_company_code
and ca.eligible_date <= l_batch_end
and ca.company_code = dda.company_code
and ca.currencya = dda.currency
and dda.deal_type = 'CA'
and ca.account_no = dda.account_no;
select PERIOD_END, COMPANY_CODE, PERIOD_START
into l_batch_end, l_company_code, l_batch_start
from xtr_batches
where BATCH_ID = p_batch_id;
select PARAMETER_VALUE_CODE into l_accounting
from xtr_company_parameters
where COMPANY_CODE = l_company_code and
PARAMETER_CODE = C_DEAL_SETTLE_ACCOUNTING;
select PARAMETER_VALUE_CODE into l_exchange_type
from xtr_company_parameters
where COMPANY_CODE = l_company_code and
PARAMETER_CODE = C_EXCHANGE_RATE_TYPE;
select sob.currency_code
into l_sob_ccy
from gl_sets_of_books sob, xtr_party_info pinfo
where pinfo.party_code = l_company_code
and pinfo.set_of_books_id = sob.SET_OF_BOOKS_ID;
select param_value
into l_fx_param
from XTR_PRO_PARAM
where param_type = 'DFLTVAL'
and param_name = 'FX_REALIZED_RATE';
xtr_insert_event(p_batch_id);
-- Insert realized g/l info to XTR_REVALUATION_DETAILS
rel_pl_value := rec.fair_value - rec.init_fv;
select profit_loss, fx_ro_pd_rate
into rel_pl_value, l_fx_rate
from XTR_DEALS
where deal_no = rec.deal_no;
select trans_closeout_no, ni_profit_loss, initial_fair_value
into l_close_no, l_ni_pl, rec.init_fv
from xtr_rollover_transactions
where deal_number = rec.deal_no
and transaction_number = rec.trans_no;
select decode(l_accounting, 'TRADE', deal_date, start_date)
into l_reneg_date
from XTR_DEALS
where deal_no = l_close_no;
select interest_rate
into rec.reval_rate
from XTR_DEALS
where deal_no = (select trans_closeout_no
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and transaction_number = rec.trans_no);
-- We do have realized G/L for this record. insert a new row
xtr_revl_real_log(rec, 0, 0, 0, rel_curr_gl, r_rd, retcode);
select nvl(sum(principal_adjust), 0)
into rec.face_value
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and deal_type = 'ONC'
and transaction_number in (select transaction_number
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and start_date <= rec.revldate
and (cross_ref_to_trans is null));
select face_value, product_type
into rec.face_value, rec.product_type
from xtr_ig_eligible_deals_v
where deal_no = rec.deal_no
and company_code = rec.company_code
and transaction_no = (select max(transaction_no)
from xtr_ig_eligible_deals_v
where deal_no = rec.deal_no
and effective_date = (select max(effective_date)
from xtr_ig_eligible_deals_v
where deal_no = rec.deal_no
and company_code = rec.company_code
and eligible_date <= rec.revldate));
update XTR_INTERGROUP_TRANSFERS
set first_batch_id = rec.batch_id
where company_code = rec.company_code
and deal_number = rec.deal_no
and transfer_date <= rec.revldate;
select nvl(face_value,0), transaction_rate -- added nvl for R12
into rec.face_value, rec.transaction_rate
from xtr_ca_eligible_deals_v
where account_no = rec.account_no
and company_code = rec.company_code
and effective_date = (select max(effective_date)
from xtr_ca_eligible_deals_v
where account_no = rec.account_no
and company_code = rec.company_code
and eligible_date <= rec.revldate);
update XTR_BANK_BALANCES
set first_batch_id = rec.batch_id
where company_code = rec.company_code
and account_number = rec.account_no
and balance_date <= rec.revldate;
select sum(reference_amount)
from xtr_hedge_relationships
where hedge_attribute_id = rec.deal_no
and instrument_item_flag = 'I';
select min(reclass_balance_amt)
from XTR_RECLASS_DETAILS
where hedge_attribute_id = rec.deal_no
and reclass_date <= rec.batch_start;
select initial_fair_value
into rec.init_fv
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and transaction_number = rec.trans_no;
select initial_fair_value
into rec.init_fv
from XTR_DEALS
where deal_no = rec.deal_no;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select RIC_CODE
into l_ric_code
from XTR_BOND_ISSUES
where bond_issue_code = rec.contract_code;
select exercise_price
into rec.reval_rate
from xtr_deals
where deal_no = rec.deal_no;
select base_rate, capital_price -- Get exercise price for BDO
into rec.reval_rate, l_strike_price
from xtr_deals
where deal_no = rec.deal_no;
select settle_rate
into rec.reval_rate
from XTR_DEALS
where deal_no = rec.deal_no;
select interest_rate
into rec.reval_rate
from XTR_DEALS
where int_swap_ref = (select swap_ref from XTR_DEALS
where deal_no = rec.deal_no)
and deal_subtype = (select decode(D.coupon_action, 'REC', 'FUND', 'INVEST')
from XTR_DEALS D where d.deal_no = rec.deal_no);
select settle_rate
into rec.reval_rate
from xtr_deals
where deal_no = rec.deal_no;
select knock_type, knock_execute_date
into l_knock_type, l_knock_date
from XTR_DEALS
where deal_no = rec.deal_no;
select base_rate
into rec.reval_rate
from xtr_deals
where deal_no = (select fxo_deal_no
from xtr_deals
where deal_no = rec.deal_no);
select settle_rate
into rec.reval_rate
from xtr_deals
where deal_no = rec.deal_no;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select sum(PRINCIPAL_ADJUST)
into l_in_pri_adjust
from xtr_rollover_transactions
where principal_action = 'INCRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and start_date <> (select start_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_de_pri_adjust
from xtr_rollover_transactions
where principal_action = 'DECRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and start_date <> (select maturity_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_in_pri_adjust
from xtr_rollover_transactions
where principal_action = 'INCRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and START_DATE >= rec.period_start
and start_date <> (select start_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_de_pri_adjust
from xtr_rollover_transactions
where principal_action = 'DECRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and START_DATE >= rec.period_start
and start_date <> (select maturity_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_in_pri_adjust
from xtr_rollover_transactions
where principal_action = 'INCRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and start_date <> (select start_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_de_pri_adjust
from xtr_rollover_transactions
where principal_action = 'DECRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and start_date <> (select maturity_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(pi_amount_received)
into l_recon
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(settle_date,maturity_date) <= rec.revldate;
select sum(interest)
into l_int
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(settle_date,maturity_date) <= rec.revldate
and nvl(pi_amount_received, 0) <> 0;
select sum(PRINCIPAL_ADJUST)
into l_in_pri_adjust
from xtr_rollover_transactions
where principal_action = 'INCRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and START_DATE >= rec.period_start
and start_date <> (select start_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(PRINCIPAL_ADJUST)
into l_de_pri_adjust
from xtr_rollover_transactions
where principal_action = 'DECRSE'
and DEAL_NUMBER = rec.deal_no
and START_DATE <= rec.revldate
and START_DATE >= rec.period_start
and start_date <> (select maturity_date
from XTR_DEALS
where deal_no = rec.deal_no);
select sum(pi_amount_received)
into l_recon
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(settle_date,maturity_date) <= rec.revldate
and nvl(settle_date,maturity_date) >= rec.period_start;
select sum(interest)
into l_int
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(settle_date,maturity_date) <= rec.revldate
and nvl(settle_date,maturity_date) >= rec.period_start
and nvl(pi_amount_received, 0) <> 0;
select PARAMETER_VALUE_CODE into l_accounting
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_DEAL_SETTLE_ACCOUNTING;
select PARAMETER_VALUE_CODE into l_begin_fv
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_BEGIN_FV;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.sob_ccy;
select exchange_rate_one, sob_fair_value_amount
into l_pre_gl_rate, l_pre_sob_fv
from XTR_REVALUATION_DETAILS
where deal_no = rec.deal_no
and transaction_no = rec.trans_no
and nvl(realized_flag, 'N') = 'N'
and batch_id = l_pre_batch;
Select a.start_date, a.transaction_number, a.principal_adjust,
a.principal_action, a.balance_out, a.first_reval_batch_id,
b.start_date, b.transaction_number, b.principal_adjust,
b.principal_action, b.balance_out
from xtr_rollover_transactions a,
xtr_rollover_transactions b
where a.deal_number = rec.deal_no
and a.deal_number = b.deal_number
and a.start_date < b.start_date
and b.start_date <= rec.revldate
and nvl(a.principal_adjust, 0) <> 0
and nvl(b.principal_adjust, 0) <> 0
and a.start_date =
(select max(c.start_date)
from xtr_rollover_transactions c
where c.deal_number = a.deal_number
and nvl(c.principal_adjust,0) <> 0
and c.start_date < b.start_date)
and a.last_reval_batch_id is null
and b.last_reval_batch_id is null
order by a.start_date;
select start_date, transaction_number, principal_adjust, principal_action,
balance_out, first_reval_batch_id
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0
and start_date <= rec.revldate
order by start_date desc, transaction_number desc;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.sob_ccy;
select exchange_rate_one
into l_unrel_start_rate
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N';
select average_exchange_rate -- Get previous transaction's average rate
into l_pre_avg_rate
from xtr_rollover_transactions
where deal_number = rec.deal_no
and average_exchange_rate is NOT NULL -- bug 4598526
and (start_date, maturity_date) = (select max(start_date),max(maturity_date) -- bug 5598286
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0
and start_date < l_start_date0
and average_exchange_rate is NOT NULL);
select balance_out -- Get previous transaction's outstandin balance
into l_pre_balance_out
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0 -- bug 4598526
and (start_date,maturity_date) = (select max(start_date),max(maturity_date) -- bug 5598286
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0
and start_date < l_start_date0);
Update XTR_ROLLOVER_TRANSACTIONS
set first_reval_batch_id = nvl(first_reval_batch_id, l_first_batch_id),
last_reval_batch_id = nvl(last_reval_batch_id, l_last_batch_id),
currency_exchange_rate = nvl(currency_exchange_rate, l_trans_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate)
where deal_number = rec.deal_no
and transaction_number = l_trans_no0;
select exchange_rate_one
into l_unrel_start_rate
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N';
select average_exchange_rate -- Get previous transaction's average rate
into l_pre_avg_rate
from xtr_rollover_transactions
where deal_number = rec.deal_no
and average_exchange_rate is NOT NULL -- bug 4598526
and (start_date, maturity_date) = (select max(start_date), max(maturity_date) -- bug 5598286
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0
and start_date < l_start_date0
and average_exchange_rate is NOT NULL);
select balance_out -- Get previous transaction's outstandin balance
into l_pre_balance_out
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0 -- bug 4598526
and (start_date, maturity_date) = (select max(start_date),max(maturity_date) -- bug 5598286
from xtr_rollover_transactions
where deal_number = rec.deal_no
and nvl(principal_adjust, 0) <> 0
and start_date < l_start_date0);
Update XTR_ROLLOVER_TRANSACTIONS
set first_reval_batch_id = nvl(first_reval_batch_id, l_first_batch_id),
currency_exchange_rate = nvl(currency_exchange_rate, l_trans_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate)
where deal_number = rec.deal_no
and transaction_number = l_trans_no0;
SELECT a.balance_date, nvl(a.balance_cflow,0), a.first_batch_id,
a.interest_rate,
b.balance_date, nvl(b.balance_cflow,0), b.interest_rate
FROM xtr_bank_balances a,
xtr_bank_balances b
Where a.company_code = rec.company_code
and a.company_code = b.company_code
and a.account_number = rec.account_no
and a.account_number = b.account_number
and a.balance_date < b.balance_date
and b.balance_date <= rec.revldate
and a.balance_date =
(SELECT max(c.balance_date)
FROM xtr_bank_balances c
where c.company_code = a.company_code
AND c.account_number = a.account_number
AND c.balance_date < b.balance_date)
and a.last_batch_id is null
and b.last_batch_id is null
ORDER by 1;
Select balance_date, nvl(balance_cflow,0), first_batch_id, interest_rate
From XTR_BANK_BALANCES
Where company_code = rec.company_code
and account_number = rec.account_no
and balance_date =
(select max(effective_date)
from XTR_CA_ELIGIBLE_DEALS_V
where company_code = rec.company_code
and account_no = rec.account_no
and effective_date <= rec.revldate)
and last_batch_id is null;
SELECT average_exchange_rate
FROM xtr_bank_balances
WHERE company_code = rec.company_code
AND account_number = rec.account_no
AND first_batch_id = l_pre_batch_id
ORDER BY balance_date desc;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.sob_ccy;
select exchange_rate_one, (period_to + 1)
into l_unrel_start_rate, l_last_unrel_date
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and company_code = rec.company_code
and account_no = rec.account_no
and effective_date = l_bal_date0
and nvl(realized_flag, 'N') = 'N';
update XTR_BANK_BALANCES
set first_batch_id = nvl(first_batch_id, l_first_batch_id),
last_batch_id = nvl(last_batch_id, l_last_batch_id),
exchange_rate = nvl(exchange_rate, l_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate0)
where company_code = rec.company_code
and account_number = rec.account_no
and balance_date = l_bal_date0;
update XTR_BANK_BALANCES
set average_exchange_rate = l_avg_rate
where company_code = rec.company_code
and account_number = rec.account_no
and balance_date = l_bal_date1;
select exchange_rate_one, (period_to + 1)
into l_unrel_start_rate, l_last_unrel_date
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and company_code = rec.company_code
and account_no = rec.account_no
and effective_date = l_bal_date0
and nvl(realized_flag, 'N') = 'N';
update XTR_BANK_BALANCES
set first_batch_id = nvl(first_batch_id, l_first_batch_id),
exchange_rate = nvl(exchange_rate, l_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate0)
where company_code = rec.company_code
and account_number = rec.account_no
and balance_date = l_bal_date0;
SELECT a.transfer_date, a.balance_out, a.transaction_number, a.first_batch_id,
a.product_type, a.portfolio,
b.transfer_date, b.balance_out, b.transaction_number, b.product_type,
b.portfolio
FROM XTR_INTERGROUP_TRANSFERS a,
XTR_INTERGROUP_TRANSFERS b
WHERE a.company_code = rec.company_code
AND a.company_code = b.company_code
AND a.deal_number = rec.deal_no
AND a.deal_number = b.deal_number
AND b.transfer_date <= rec.revldate
AND ((b.transaction_number > a.transaction_number and a.transfer_date = b.transfer_date)
or(b.transfer_date > a.transfer_date))
AND a.transfer_date =
(select max(transfer_date)
from XTR_INTERGROUP_TRANSFERS c
where c.deal_number = a.deal_number
and ((c.transfer_date < b.transfer_date) or
(c.transaction_number < b.transaction_number and c.transfer_date = b.transfer_date)))
AND a.last_batch_id is null
AND b.last_batch_id is null
ORDER by 1;
SELECT b.transfer_date, b.balance_out, b.transaction_number,
b.product_type, b.portfolio
FROM XTR_INTERGROUP_TRANSFERS b
WHERE b.company_code = rec.company_code
AND b.deal_number = rec.deal_no
AND b.transfer_date <= rec.revldate
AND b.last_batch_id is null
ORDER by b.transfer_date, b.transaction_number;
SELECT a.transfer_date, a.balance_out, a.transaction_number, a.first_batch_id,
a.product_type, a.portfolio
FROM XTR_INTERGROUP_TRANSFERS a
WHERE a.company_code = rec.company_code
AND a.deal_number = rec.deal_no
AND ((l_trans_no1 > a.transaction_number and a.transfer_date = l_bal_date1)
or(l_bal_date1 > a.transfer_date))
AND a.last_batch_id is null
ORDER by a.transfer_date desc, a.transaction_number desc;
SELECT transaction_number, transfer_date, balance_out,
first_batch_id, product_type, portfolio
FROM XTR_INTERGROUP_TRANSFERS
Where deal_number = rec.deal_no
AND transfer_date <= rec.revldate
AND last_batch_id is null
ORDER BY transfer_date desc, transaction_number desc;
and transfer_date = (select max(transfer_date)
from XTR_INTERGROUP_TRANSFERS
where deal_number = rec.deal_no)
and transaction_number = (select max(transaction_number)
from XTR_INTERGROUP_TRANSFERS
where deal_number = rec.deal_no)
and last_batch_id is null;
SELECT average_exchange_rate
FROM xtr_intergroup_transfers
WHERE deal_number = rec.deal_no
AND transfer_date <= l_bal_date0
AND first_batch_id = l_pre_batch_id
ORDER BY transfer_date desc, transaction_number desc;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.sob_ccy;
select exchange_rate_one, (period_to +1)
into l_unrel_start_rate, l_last_unrel_date
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and deal_no = rec.deal_no
and deal_type = rec.deal_type
and transaction_no = l_trans_no0
and nvl(realized_flag, 'N') = 'N';
update XTR_INTERGROUP_TRANSFERS
set first_batch_id = nvl(first_batch_id, l_first_batch_id),
last_batch_id = nvl(last_batch_id, l_last_batch_id),
exchange_rate = nvl(exchange_rate, l_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate0)
where company_code = rec.company_code
and deal_number = rec.deal_no
and transaction_number = l_trans_no0;
update XTR_INTERGROUP_TRANSFERS
set average_exchange_rate = l_avg_rate
where company_code = rec.company_code
and deal_number = rec.deal_no
and transaction_number = l_trans_no1;
select exchange_rate_one, (period_to +1)
into l_unrel_start_rate, l_last_unrel_date
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and deal_no = rec.deal_no
and deal_type = rec.deal_type
and transaction_no = l_trans_no0
and nvl(realized_flag, 'N') = 'N';
update XTR_INTERGROUP_TRANSFERS
set first_batch_id = nvl(first_batch_id, l_first_batch_id),
exchange_rate = nvl(exchange_rate, l_ex_rate0),
average_exchange_rate = nvl(average_exchange_rate, l_avg_rate0)
where company_code = rec.company_code
and deal_number = rec.deal_no
and transaction_number = rec.trans_no;
select r.deal_subtype, r.transaction_number,
decode(cp.parameter_value_code, 'TRADE', r.deal_date,
r.start_date) start_date,
r.maturity_date, r.balance_out, r.repay_amount,
r.first_reval_batch_id, r.interest_rate,
r.average_exchange_rate, r.cross_ref_to_trans, r.product_type,
r.currency_exchange_rate, r.accum_int_action, r.accum_interest_bf --bug 2895074
from XTR_ROLLOVER_TRANSACTIONS R,
XTR_COMPANY_PARAMETERS CP
where r.deal_number = rec.deal_no
and cp.company_code = r.company_code
and cp.parameter_code = 'ACCNT_TSDTM'
and decode(cp.parameter_value_code, 'TRADE', r.deal_date,
r.start_date) <= rec.revldate
and last_reval_batch_id is null
and status_code <> 'CANCELLED'
order by transaction_number asc;
select balance_out, currency_exchange_rate,
average_exchange_rate --bug 3041100
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and cross_ref_to_trans = rec.trans_no;
select rowid
from XTR_ROLLOVER_TRANSACTIONS
Where DEAL_NUMBER = rec.deal_no
And TRANSACTION_NUMBER = rec.trans_no
And DEAL_TYPE = 'ONC'
for update of FIRST_REVAL_BATCH_ID NOWAIT;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.sob_ccy;
select count(*)
into l_dummy
from xtr_rollover_transactions
where deal_number = rec.deal_no
and cross_ref_to_trans = rec.trans_no;
select nvl(average_exchange_rate,currency_exchange_rate),balance_out
into l_pre_avg_rate, l_pre_balance
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and cross_ref_to_trans = rec.trans_no;
select exchange_rate_one
into l_unrel_start_rate
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch_id
and deal_no = rec.deal_no
and transaction_no = rec.trans_no
and deal_type = rec.deal_type
and nvl(realized_flag, 'N') = 'N';
select min(start_date)
into l_rel_start_date
from XTR_ROLLOVER_TRANSACTIONS
where cross_ref_to_trans = rec.trans_no;
Update XTR_ROLLOVER_TRANSACTIONS
Set FIRST_REVAL_BATCH_ID = nvl(l_trans_first_batch, FIRST_REVAL_BATCH_ID),
LAST_REVAL_BATCH_ID = nvl(l_trans_last_batch, LAST_REVAL_BATCH_ID),
CURRENCY_EXCHANGE_RATE = nvl(CURRENCY_EXCHANGE_RATE, l_trans_gl_rate),
AVERAGE_EXCHANGE_RATE = nvl(AVERAGE_EXCHANGE_RATE, l_trans_avg_rate)
Where rowid = l_rowid;
select fair_value, cumm_gain_loss_amount, reval_rate
from xtr_revaluation_details
where DEAL_NO = rec.deal_no
and TRANSACTION_NO = rec.trans_no
and nvl(realized_flag, 'N') = 'N'
and BATCH_ID = l_batch_id
order by period_to desc; -- bug 4214521 issue 1
select ni_disc_amount
from xtr_revaluation_details
where DEAL_NO = rec.deal_no
and TRANSACTION_NO = rec.trans_no
and BATCH_ID = l_batch_id;
select initial_fair_value
into p_fair_value
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and transaction_number = rec.trans_no;
select initial_fair_value
into p_fair_value
from XTR_DEALS
where deal_no = rec.deal_no;
select interest
into p_ni_disc_amt
from xtr_rollover_transactions
where deal_number = rec.deal_no
and transaction_number = rec.trans_no;
select PARAMETER_VALUE_CODE -- determine if we are going to include transaction cost in deal type
into l_inclu_cost
from xtr_company_parameters
where company_code = rec.company_code
and parameter_code = C_INCOST;
select discount
into l_discount
from xtr_deals
where deal_no = rec.deal_no;
select face_value_amount, deal_subtype
into init_fv, l_deal_subtype
from xtr_deals
where deal_no = rec.deal_no;
select brokerage_amount
into init_fv
from xtr_deals
where deal_no = rec.deal_no;
select premium_action, premium_amount
into l_pre_action, l_pre_amt
from xtr_deals
where deal_no = rec.deal_no;
select decode(l_inclu_cost, 'N', (BALANCE_OUT - INTEREST),
(BALANCE_OUT - INTEREST + nvl(BROKERAGE_AMOUNT, 0))),
deal_subtype
into init_fv, l_deal_subtype
from xtr_rollover_transactions
where DEAL_NUMBER = rec.deal_no
and TRANSACTION_NUMBER = rec.trans_no;
select maturity_amount, brokerage_amount,
nvl(base_rate, capital_price), deal_subtype
into l_face_value, l_brk_amt, l_int_rate, l_deal_subtype
from xtr_deals
where deal_no = rec.deal_no;
select start_amount, brokerage_amount
into l_face_value, l_brk_amt
from XTR_DEALS
where deal_no = rec.deal_no;
select face_value_amount, brokerage_amount, deal_subtype
into l_face_value, l_brk_amt, l_deal_subtype
from xtr_deals
where deal_no = rec.deal_no;
select BALANCE_OUT
into init_fv
from XTR_INTERGROUP_TRANSFERS_V
where DEAL_NUMBER = rec.deal_no
and TRANSACTION_NUMBER = rec.trans_no;
select STATEMENT_BALANCE
into init_fv
from XTR_BANK_BALANCES_V
where company_code = rec.company_code
and account_number = rec.account_no
and BALANCE_DATE <= rec.revldate;
update xtr_deals set initial_fair_value = fv
where deal_no = p_deal_no and
deal_type = p_deal_type and
transaction_no = p_transaction_no and
company_code = p_company_code;
update xtr_deals set initial_fair_value = fv
where deal_no = p_deal_no and
deal_type = p_deal_type and
company_code = p_company_code;
update xtr_rollover_transactions set initial_fair_value = fv
where deal_number = p_deal_no and
deal_type = p_deal_type and
transaction_number = p_transaction_no and
company_code = p_company_code;
update xtr_rollover_transactions set initial_fair_value = fv
where ((deal_number = p_deal_no) or (trans_closeout_no = p_deal_no)) and
deal_type = p_deal_type and
company_code = p_company_code;
select PARAMETER_VALUE_CODE
into l_inclu_cost
from XTR_COMPANY_PARAMETERS
where company_code = p_company_code
and parameter_code = C_INCOST;
select rt.balance_out, nvl(rt.brokerage_amount, 0), d.year_calc_type,
d.calc_basis, rt.start_date, rt.maturity_date
into l_face_value, l_brk_amt, l_year_calc_type, l_disc_yield, l_start_date, l_end_date
from xtr_deals D,
xtr_rollover_transactions RT
where D.deal_no = p_deal_no
and D.deal_no = RT.deal_number
and RT.transaction_number = p_transaction_no;
select rt.balance_out, nvl(rt.brokerage_amount, 0), d.year_calc_type,
d.calc_basis, rt.start_date, rt.maturity_date
into l_face_value, l_brk_amt, l_year_calc_type, l_disc_yield,
l_start_date, l_end_date
from xtr_deals D, xtr_rollover_transactions RT
where D.deal_no = p_deal_no
and D.deal_no = RT.deal_number
and RT.transaction_number = p_transaction_no;
select interest_rate
into l_all_in_rate
from xtr_rollover_transactions
where deal_number = p_deal_no
and transaction_number = p_transaction_no;
select interest_rate
into l_all_in_rate
from xtr_rollover_transactions
where deal_number = p_deal_no
and transaction_number = p_transaction_no;
update xtr_rollover_transactions
set all_in_rate = l_all_in_rate
where deal_number = p_deal_no and
deal_type = p_deal_type and
transaction_number = p_transaction_no and
company_code = p_company_code;
update xtr_rollover_transactions
set all_in_rate = l_all_in_rate
where ((deal_number = p_deal_no) or (trans_closeout_no = p_deal_no)) and
deal_type = p_deal_type and
company_code = p_company_code;
update xtr_rollover_transactions set initial_fair_value = fv
where deal_number = p_deal_no and
deal_type = p_deal_type and
transaction_number = p_transaction_no and
company_code = p_company_code;
update xtr_rollover_transactions set initial_fair_value = fv
where deal_number = p_deal_no and
deal_type = p_deal_type and
company_code = p_company_code;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select R.BALANCE_OUT_BF
into end_fv
from XTR_ROLLOVER_TRANSACTIONS R
where deal_number = rec.deal_no
and transaction_number =
(select max(transaction_number)
from xtr_rollover_transactions
where deal_number = rec.deal_no);
select decode(discount,'Y', (initial_fair_value - face_value_amount),
initial_fair_value)
into end_fv
from XTR_DEALS
where deal_subtype = 'FUND'
and int_swap_ref = (select swap_ref
from XTR_DEALS
where deal_no = rec.deal_no);
select decode(discount,'Y', (initial_fair_value - face_value_amount),
initial_fair_value)
into end_fv
from XTR_DEALS
where deal_subtype = 'INVEST'
and int_swap_ref = (select swap_ref
from XTR_DEALS
where deal_no = rec.deal_no);
select batch_id
from xtr_batches
where COMPANY_CODE = rec.company_code
and PERIOD_END = (select max(b.period_end)
from xtr_batches b, xtr_revaluation_details r
where b.BATCH_ID <> rec.batch_id
and r.deal_no = rec.deal_no
and b.batch_id = r.batch_id)
and nvl(upgrade_batch, 'N') <> 'Y'
and batch_type is NULL;
select START_DATE, MATURITY_DATE
from xtr_rollover_transactions
where DEAL_NUMBER = rec.deal_no and TRANSACTION_NUMBER =
rec.trans_no;
select PARAMETER_VALUE_CODE into l_discount_date_method
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_FRA_DISCOUNT_METHOD;
select PARAMETER_VALUE_CODE
into l_discount_date_method
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_FX_DISCOUNT_METHOD;
select param_value
into l_fx_param
from XTR_PRO_PARAM
where param_type = 'DFLTVAL'
and param_name = 'FX_REALIZED_RATE';
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select 'Y'
from XTR_REVALUATION_DETAILS R,
XTR_BATCHES B
where r.deal_no = rec.deal_no
and r.batch_id = b.batch_id
and r.batch_id <> rec.batch_id
and b.upgrade_batch <> 'Y';
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select max(b.period_end)
from xtr_batches b, xtr_revaluation_details r
where b.batch_id = r.batch_id
and b.company_code = rec.company_code
and b.batch_type is NULL
and r.deal_no = rec.deal_no;
select a.start_date reval_date
from XTR_HEDGE_ATTRIBUTES a,
XTR_HEDGE_RELATIONSHIPS r
where a.hedge_attribute_id = r.hedge_attribute_id
and r.primary_code = rec.deal_no
and r.instrument_item_flag = 'U'
and a.start_date < rec.revldate
and a.start_date >= p_last_end_date
union
select nvl(discontinue_date, end_date) reval_date
from XTR_HEDGE_ATTRIBUTES a,
XTR_HEDGE_RELATIONSHIPS r
where a.hedge_attribute_id = r.hedge_attribute_id
and r.primary_code = rec.deal_no
and r.instrument_item_flag = 'U'
and nvl(a.discontinue_date, a.end_date) < rec.revldate
and nvl(a.discontinue_date, a.end_date) >= p_last_end_date
union
select c.reclass_date reval_date
from XTR_RECLASS_DETAILS c, xtr_hedge_relationships R
where c.hedge_attribute_id = r.hedge_attribute_id
and r.primary_code = rec.deal_no
and r.instrument_item_flag = 'U'
and c.reclass_date > p_last_end_date -- bug 4214523
and c.reclass_date < rec.revldate
union
select effective_date reval_date
from xtr_fx_eligible_deals_v
where deal_no = rec.deal_no
and effective_date < rec.revldate
union
select period_end reval_date
from xtr_batches
where batch_id = rec.batch_id
order by reval_date asc;
/* This procedure calculates and insert record into table */
/* for Hedge Items Revaluations */
/**************************************************************/
PROCEDURE xtr_revl_fv_hedge (rec IN OUT NOCOPY xtr_revl_rec) IS
Cursor C_REVAL_DATE is -- Find hedge reclass date within the batch range
select reclass_date reval_date
from XTR_RECLASS_DETAILS
where hedge_attribute_id = rec.deal_no
and reclass_date < rec.revldate
and last_reval_batch_id is NULL
union
select nvl(discontinue_date, end_date) reval_date
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and nvl(discontinue_date, end_date) < rec.revldate
union
select effective_date reval_date
from XTR_ELIGIBLE_HEDGES_V
where deal_no = rec.deal_no
and effective_date < rec.revldate
union
select period_end reval_date
from xtr_batches
where batch_id = rec.batch_id
order by reval_date asc;
select RECLASS_BALANCE_AMT
from xtr_reclass_details
where HEDGE_ATTRIBUTE_ID = rec.deal_no
and RECLASS_DATE = (select max(RECLASS_DATE) from xtr_reclass_details
where HEDGE_ATTRIBUTE_ID = rec.deal_no and rECLASS_DATE < p_revldate);
select pros_frequency_num, pros_frequency_unit
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and PROS_METHOD <> 'NOTEST';
select start_date
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and PROS_METHOD <> 'NOTEST';
select least( nvl(discontinue_date, end_date),reclass_date)
from XTR_HEDGE_ATTRIBUTES hat , XTR_RECLASS_DETAILS rd
where hat.hedge_attribute_id = rec.deal_no
and PROS_METHOD <> 'NOTEST'
and reclass_balance_amt = 0
and hat.hedge_attribute_id = rd.hedge_attribute_id;
select max(result_date)
from XTR_HEDGE_PRO_TESTS
where hedge_attribute_id = rec.deal_no;
select PARAMETER_VALUE_CODE
into l_test
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_BTEST;
select reclass_details_id, reclass_balance_amt
from XTR_RECLASS_DETAILS
where hedge_attribute_id = rec.deal_no
and reclass_date >= p_start_date
and reclass_date < p_end_date;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
Update XTR_RECLASS_DETAILS
Set last_reval_batch_id = rec.batch_id
where reclass_details_id = l_reclass_id;
select reclass_details_id, reclass_balance_amt
from XTR_RECLASS_DETAILS
where hedge_attribute_id = rec.deal_no
and reclass_date >= p_start_date
and reclass_date < p_end_date; -- bug 4276970
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
Update XTR_RECLASS_DETAILS
Set last_reval_batch_id = rec.batch_id
where reclass_details_id = l_reclass_id;
select fair_value, exchange_rate_one, cumm_gain_loss_amount
from XTR_REVALUATION_DETAILS
where deal_no = rec.deal_no
and company_code = rec.company_code
and period_to = (select max(period_to)
from xtr_revaluation_details
where company_code = rec.company_code
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N');
select fair_value, reval_rate, CTR_CURR_SOB_CURR_FWD_RATE,
cumm_gain_loss_amount
from XTR_REVALUATION_DETAILS
where deal_no = rec.deal_no
and company_code = rec.company_code
and period_to = (select max(period_to)
from xtr_revaluation_details
where company_code = rec.company_code
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N');
select PARAMETER_VALUE_CODE into l_discount_date_method
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_FUTURE_DATE_NI; -- 'REVAL_FDNDR'
select BALANCE_OUT
into l_parcel_amt
from xtr_rollover_transactions
where DEAL_NUMBER = rec.deal_no and
TRANSACTION_NUMBER = rec.trans_no;
select MARGIN, CALC_BASIS
into l_margin, l_disc_yield_basis
from XTR_DEALS
where deal_no = rec.deal_no;
select MARGIN, COUPON_ACTION
into l_margin, l_coupon_action
from XTR_DEALS
where DEAL_NO = rec.deal_no;
select RIC_CODE
into l_ric_code
from XTR_BOND_ISSUES
where bond_issue_code = rec.contract_code;
Select bond.deal_no, bond.cross_ref_no, bond.face_value, bond.initial_fair_value,
bond.amc_real_gain_loss, bond.mtm_real_gain_loss, bond.cross_ref_clean_px,
decode(cp.parameter_value_code, 'TRADE', bond.cross_ref_deal_date,
bond.cross_ref_start_date) resale_rec_date
from XTR_BOND_ALLOC_DETAILS BOND,
XTR_COMPANY_PARAMETERS CP,
XTR_DEALS D
Where bond.deal_no = rec.deal_no
and bond.deal_no = d.deal_no
and bond.batch_id is null
and cp.company_code = d.company_code
and cp.parameter_code = 'ACCNT_TSDTM'
and decode(cp.parameter_value_code, 'TRADE', bond.cross_ref_deal_date,
bond.cross_ref_start_date) <= rec.revldate;
select ROUNDING_FACTOR
into l_rounding
from XTR_MASTER_CURRENCIES_V
where currency = rec.currencya;
select fair_value, face_value, exchange_rate_one, cumm_gain_loss_amount
into bo_rec.start_fair_value, bo_rec.maturity_face_value,
bo_rec.pre_gl_rate, bo_rec.cum_unrel_gl
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N'
and transaction_no = 1;
Update XTR_BOND_ALLOC_DETAILS
Set batch_id = rec.batch_id
where deal_no = bo_rec.deal_no
and cross_ref_no = bo_rec.cross_ref_no;
update XTR_DEALS
set last_reval_batch_id = rec.batch_id
where deal_no = rec.deal_no;
update XTR_DEALS
set last_reval_batch_id = rec.batch_id
where deal_no = rec.deal_no;
/* This procedure insert value to revaluation details */
/* table with BOND amount type 'REALAMC' information */
/*********************************************************/
PROCEDURE xtr_revl_bond_realamc(rec IN OUT NOCOPY xtr_revl_rec,
bo_rec IN xtr_bond_rec) IS
l_buf Varchar2(500);
/* This procedure insert value to revaluation details */
/* table with BOND amount type 'REALMTM' information */
/*********************************************************/
PROCEDURE xtr_revl_bond_realmtm(rec IN OUT NOCOPY xtr_revl_rec,
bo_rec IN xtr_bond_rec,
p_resale IN BOOLEAN) IS
l_buf Varchar2(500);
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
Select nvl(initial_fair_value,rec.fair_value)
into l_full_init_fv
from xtr_deals
where deal_no = rec.deal_no;
select sum(initial_fair_value) -- 2879585. Correction from initial design.
into l_bond_init_fv
from xtr_bond_alloc_details
where deal_no = rec.deal_no;
/* This procedure insert value to revaluation details */
/* table with BOND amount type 'UNREAL' information */
/* We only allow overwrite Price for UNREAL amount type */
/* with transaction no = 1 */
/*********************************************************/
PROCEDURE xtr_revl_bond_unreal(rec IN OUT NOCOPY xtr_revl_rec,
bo_rec IN OUT NOCOPY xtr_bond_rec,
p_resale IN BOOLEAN,
p_overwrite IN BOOLEAN) IS
l_buf Varchar2(500);
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select realised_pl, reval_rate
into l_mtm_real, l_reval_rate
from XTR_REVALUATION_DETAILS
where batch_id = rec.batch_id
and deal_no = rec.deal_no
and transaction_no = rec.trans_no
and amount_type = 'REAL';
if p_overwrite = FALSE then -- insert new record from concurrent program
xtr_revl_unreal_log(rec, unrel_pl_value, cum_pl_value, fv_sob_amt,
unrel_sob_gl, currency_gl, r_rd, retcode);
select TRANSACTION_NUMBER, START_DATE, MATURITY_DATE, PRINCIPAL_ACTION,
INTEREST_RATE, INTEREST_SETTLED, PRINCIPAL_ADJUST, ACCUM_INTEREST,
BALANCE_OUT, SETTLE_TERM_INTEREST, INTEREST, INTEREST_REFUND, SETTLE_DATE
from xtr_rollover_transactions
where DEAL_NUMBER = rec.deal_no
and maturity_date >= rec.revldate
order by start_date, transaction_number asc;
select day_count_type, rounding_type, prepaid_interest
into l_day_count_type, l_round_type, l_pre_int
from XTR_DEALS_V
where deal_no = rec.deal_no;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select max(transaction_number)
into l_last_rec_trans
from xtr_rollover_transactions
where DEAL_NUMBER=rec.deal_no
and START_DATE = MATURITY_DATE;
Select stock.deal_no, stock.cross_ref_no, stock.fair_value, stock.init_consideration,
stock.real_gain_loss, stock.quantity, stock.remaining_quantity,
stock.price_per_share, decode(cp.parameter_value_code, 'TRADE',
stock.cross_ref_deal_date, stock.cross_ref_start_date) resale_rec_date
from XTR_STOCK_ALLOC_DETAILS STOCK,
XTR_COMPANY_PARAMETERS CP,
XTR_DEALS D
Where stock.deal_no = rec.deal_no
and stock.deal_no = d.deal_no
and stock.batch_id is null
and cp.company_code = d.company_code
and cp.parameter_code = 'ACCNT_TSDTM'
and decode(cp.parameter_value_code, 'TRADE', stock.cross_ref_deal_date,
stock.cross_ref_start_date) <= rec.revldate
Order by resale_rec_date asc, stock.cross_ref_no asc;
select ROUNDING_FACTOR
into l_rounding
from XTR_MASTER_CURRENCIES_V
where currency = rec.currencya;
select quantity, quantity, reval_rate, exchange_rate_one, cumm_gain_loss_amount
into st_rec.init_quantity, st_rec.remaining_quantity, st_rec.prev_price,
st_rec.pre_gl_rate, st_rec.cum_unrel_gl
from XTR_REVALUATION_DETAILS
where batch_id = l_pre_batch
and deal_no = rec.deal_no
and nvl(realized_flag, 'N') = 'N'
and transaction_no = 1;
Update XTR_STOCK_ALLOC_DETAILS
Set batch_id = rec.batch_id
where deal_no = st_rec.deal_no
and cross_ref_no = st_rec.cross_ref_no;
update XTR_DEALS
set last_reval_batch_id = rec.batch_id
where deal_no = rec.deal_no;
/* This procedure inserts STOCK realized G/L records */
/*********************************************************/
PROCEDURE xtr_revl_stock_real(rec IN OUT NOCOPY xtr_revl_rec,
st_rec IN xtr_stock_rec) is
l_buf Varchar2(500);
/* This procedure inserts STOCK unrealized G/L records */
/* We only allow overwrite Price for UNREAL amount type */
/* with transaction no = 1 */
/*********************************************************/
PROCEDURE xtr_revl_stock_unreal(rec IN OUT NOCOPY xtr_revl_rec,
st_rec IN xtr_stock_rec,
p_resale IN BOOLEAN,
p_overwrite IN BOOLEAN,
unrel_pl_value IN OUT NOCOPY NUMBER,
cum_pl_value IN OUT NOCOPY NUMBER,
currency_gl IN OUT NOCOPY NUMBER) IS
l_buf Varchar2(500);
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select exchange_rate_one
into rec.deal_ex_rate_one
from XTR_REVALUATION_DETAILS
where deal_no = rec.deal_no
and transaction_no = 1
and nvl(realized_flag, 'N') = 'N'
and batch_id = l_pre_batch;
select fair_value
into l_begin_fv
from XTR_REVALUATION_DETAILS
where deal_no = rec.deal_no
and transaction_no = 1
and nvl(realized_flag, 'N') = 'N'
and batch_id = l_pre_batch;
if p_overwrite = FALSE then -- insert new record from concurrent program
xtr_revl_unreal_log(rec, unrel_pl_value, cum_pl_value, fv_sob_amt,
unrel_sob_gl, currency_gl, r_rd, retcode);
select RIC_CODE
into l_ric_code
from XTR_STOCK_ISSUES
where stock_issue_code = rec.contract_code;
select TRANSACTION_NUMBER, START_DATE, MATURITY_DATE, PRINCIPAL_ACTION,
INTEREST_RATE, INTEREST_SETTLED, PRINCIPAL_ADJUST, ACCUM_INTEREST,
BALANCE_OUT, SETTLE_TERM_INTEREST, INTEREST, SETTLE_DATE
from xtr_rollover_transactions
where DEAL_NUMBER = rec.deal_no
and maturity_date >= rec.revldate
order by transaction_number asc;
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select fixed_or_floating_rate, day_count_type, rounding_type, start_date, margin
into l_fix_float, l_day_count_type, l_round_type, l_deal_begin_date,
l_margin -- Bug 3230779
from XTR_DEALS_V
where deal_no = rec.deal_no;
select max(transaction_number)
into l_max_trans_no
from XTR_ROLLOVER_TRANSACTIONS_V
where deal_number = rec.deal_no
and start_date = l_deal_begin_date;
select CURRENCY_FIRST
from XTR_BUY_SELL_COMBINATIONS
where (CURRENCY_BUY = p_base and CURRENCY_SELL = p_contra)
or (CURRENCY_BUY = p_contra and CURRENCY_SELL = p_base);
select rounding_factor
into l_round
from xtr_master_currencies_v
where currency = rec.reval_ccy;
select all_in_rate, rounding_type
into l_all_in_rate, l_rounding_type -- 5130446
from xtr_rollover_transactions, xtr_deals
where deal_number = rec.deal_no
and deal_number= deal_no
and transaction_number = rec.trans_no;
t_log.delete;
/* This procedure insert unrealized values into xtr_revaluation_details table*/
/* Also update xtr_deals and xtr_rollover_transactions, */
/* xtr_bank_balance, xtr_intergroup_transfers */
/******************************************************************/
PROCEDURE xtr_revl_unreal_log(
rec IN xtr_revl_rec,
unrel_pl_value IN NUMBER,
cum_pl_value IN NUMBER,
fv_sob_amt IN NUMBER,
unrel_sob_gl IN NUMBER,
currency_gl IN NUMBER,
r in XTR_REVALUATION_DETAILS%rowtype,
retcode OUT NOCOPY NUMBER,
p_hedge_flag IN VARCHAR2 DEFAULT NULL) IS
l_ROWID VARCHAR2(20);
select rowid
from XTR_DEALS
where DEAL_NO = rec.deal_no
and DEAL_TYPE not in ('NI', 'ONC', 'CA', 'IG')
and FIRST_REVAL_BATCH_ID is null
for update of FIRST_REVAL_BATCH_ID NOWAIT;
select rowid
from XTR_ROLLOVER_TRANSACTIONS
Where DEAL_NUMBER = rec.deal_no
And TRANSACTION_NUMBER = rec.trans_no
And DEAL_TYPE = 'NI'
And FIRST_REVAL_BATCH_ID is null
for update of FIRST_REVAL_BATCH_ID NOWAIT;
select rowid
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and first_batch_id is NULL
for update of first_batch_id NOWAIT;
select rowid
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and last_batch_id is NULL
for update of last_batch_id NOWAIT;
select XTR_REVALUATION_DETAILS_S.NEXTVAL
into l_reval_detail_id from DUAL;
XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
X_ROWID => l_ROWID,
X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
X_COMPLETE_FLAG => l_complete,
X_FAIR_VALUE => rec.fair_value,
X_CUMM_GAIN_LOSS_AMOUNT => cum_pl_value,
X_CURR_GAIN_LOSS_AMOUNT => currency_gl,
X_SOB_FV_GAIN_LOSS_AMOUNT => unrel_sob_gl,
X_SOB_FAIR_VALUE_AMOUNT => fv_sob_amt,
X_CTR_CURR_SOB_CURR_FWD_RATE => rec.reval_fx_fwd_rate,
X_EXCHANGE_RATE_TWO => rec.reval_ex_rate_two,
X_ACTION_CODE => null, -- obsolete
X_COMPANY_CODE => rec.company_code,
X_CONTRACT_CODE => rec.CONTRACT_CODE,
X_CURRENCYA => rec.CURRENCYA,
X_CURRENCYB => rec.CURRENCYB,
X_DEAL_NO => rec.deal_no,
X_DEAL_SUBTYPE => rec.deal_subtype,
X_DEAL_TYPE => rec.deal_type,
X_EFFECTIVE_DATE => rec.EFFECTIVE_DATE,
X_EFFECTIVE_DAYS => r.EFFECTIVE_DAYS,
X_ENTERED_BY => fnd_global.user_id,
X_ENTERED_ON => l_sysdate,
X_FACE_VALUE => rec.FACE_VALUE,
X_FXO_SELL_REF_AMOUNT => rec.FXO_SELL_REF_AMOUNT,
X_PERIOD_FROM => rec.period_start,
X_PERIOD_TO => rec.period_end,
X_PORTFOLIO_CODE => rec.PORTFOLIO_CODE,
X_PRODUCT_TYPE => nvl(rec.PRODUCT_TYPE, 'NOT APPLIC'),
X_REALISED_PL => null,
X_REVAL_CCY => rec.REVAL_CCY,
X_REVAL_RATE => rec.REVAL_RATE,
X_TRANSACTION_NO => rec.trans_no,
X_TRANSACTION_PERIOD => r.TRANSACTION_PERIOD,
X_TRANSACTION_RATE => rec.TRANSACTION_RATE,
X_UNREALISED_PL => unrel_pl_value,
X_UPDATED_BY => fnd_global.user_id,
X_UPDATED_ON => l_sysdate,
X_YEAR_BASIS => rec.YEAR_BASIS,
X_CREATED_ON => l_sysdate,
X_EXCHANGE_RATE_ONE => rec.reval_ex_rate_one,
X_REALIZED_FLAG => 'N',
X_OVERWRITE_TYPE => rec.ow_type,
X_OVERWRITE_VALUE => rec.ow_value,
X_OVERWRITE_REASON => null,
X_BATCH_ID => rec.batch_id,
X_CREATED_BY => fnd_global.user_id,
X_ACCOUNT_NO => rec.account_no,
X_SWAP_REF => rec.swap_ref,
X_NI_DISC_AMOUNT => rec.ni_disc_amount,
X_AMOUNT_TYPE => nvl(r.amount_type, 'UNREAL'),
X_QUANTITY => rec.quantity
);
Update XTR_DEALS
Set FIRST_REVAL_BATCH_ID = rec.batch_id,
EXCHANGE_RATE_ONE = rec.deal_ex_rate_one,
EXCHANGE_RATE_TWO = rec.deal_ex_rate_two
Where rowid = l_deal_rowid;
Update XTR_ROLLOVER_TRANSACTIONS
Set FIRST_REVAL_BATCH_ID = rec.batch_id,
CURRENCY_EXCHANGE_RATE = rec.deal_ex_rate_one
Where rowid = l_deal_rowid;
Update XTR_HEDGE_ATTRIBUTES
Set FIRST_BATCH_ID = rec.batch_id,
INIT_FAIR_VALUE_RATE = rec.deal_ex_rate_one
Where rowid = l_deal_rowid;
Update XTR_HEDGE_ATTRIBUTES
Set LAST_BATCH_ID = rec.batch_id
Where rowid = l_deal_rowid;
/* This procedure insert realized values into xtr_revaluation_details table*/
/* Also update xtr_deals and xtr_rollover_transactions, */
/* xtr_bank_balance, xtr_intergroup_transfers */
/******************************************************************/
PROCEDURE xtr_revl_real_log (
rec IN xtr_revl_rec,
rel_pl_value IN NUMBER,
fv_sob_amt IN NUMBER,
rel_sob_gl IN NUMBER,
currency_gl IN NUMBER,
r in XTR_REVALUATION_DETAILS%rowtype,
retcode OUT NOCOPY NUMBER) IS
l_ROWID VARCHAR2(20);
select rowid
from XTR_DEALS
where DEAL_NO = rec.deal_no
and ((DEAL_TYPE not in ('NI', 'ONC', 'CA', 'IG', 'BOND', 'STOCK','TMM', 'IRS'))
or (DEAL_TYPE in ('TMM', 'IRS') and rec.effective_date <= rec.revldate))
for update of FIRST_REVAL_BATCH_ID NOWAIT;
select rowid
from XTR_ROLLOVER_TRANSACTIONS
Where DEAL_NUMBER = rec.deal_no
And TRANSACTION_NUMBER = rec.trans_no
And DEAL_TYPE = 'NI'
for update of FIRST_REVAL_BATCH_ID NOWAIT;
select XTR_REVALUATION_DETAILS_S.NEXTVAL
into l_reval_detail_id from DUAL;
XTR_REVALUATION_DETAILS_PKG.INSERT_ROW(
X_ROWID => l_ROWID,
X_REVALUATION_DETAILS_ID=> l_reval_detail_id,
X_REVAL_CURR_FV_AMOUNT => rec.fair_value,
X_COMPLETE_FLAG => l_complete,
X_FAIR_VALUE => rec.fair_value,
X_CUMM_GAIN_LOSS_AMOUNT => 0,
X_CURR_GAIN_LOSS_AMOUNT => currency_gl,
X_SOB_FV_GAIN_LOSS_AMOUNT => rel_sob_gl,
X_SOB_FAIR_VALUE_AMOUNT => fv_sob_amt,
X_CTR_CURR_SOB_CURR_FWD_RATE => rec.reval_fx_fwd_rate,
X_EXCHANGE_RATE_TWO => rec.reval_ex_rate_two,
X_ACTION_CODE => null, -- obsolete
X_COMPANY_CODE => rec.company_code,
X_CONTRACT_CODE => rec.CONTRACT_CODE,
X_CURRENCYA => rec.CURRENCYA,
X_CURRENCYB => rec.CURRENCYB,
X_DEAL_NO => rec.deal_no,
X_DEAL_SUBTYPE => rec.deal_subtype,
X_DEAL_TYPE => rec.deal_type,
X_EFFECTIVE_DATE => rec.EFFECTIVE_DATE,
X_EFFECTIVE_DAYS => r.EFFECTIVE_DAYS,
X_ENTERED_BY => fnd_global.user_id,
X_ENTERED_ON => l_sysdate,
X_FACE_VALUE => rec.FACE_VALUE,
X_FXO_SELL_REF_AMOUNT => rec.FXO_SELL_REF_AMOUNT,
X_PERIOD_FROM => rec.period_start,
X_PERIOD_TO => rec.period_end,
X_PORTFOLIO_CODE => rec.portfolio_code,
X_PRODUCT_TYPE => nvl(rec.PRODUCT_TYPE, 'NOT APPLICABLE'),
X_REALISED_PL => rel_pl_value,
X_REVAL_CCY => rec.REVAL_CCY,
X_REVAL_RATE => rec.REVAL_RATE,
X_TRANSACTION_NO => rec.trans_no,
X_TRANSACTION_PERIOD => r.TRANSACTION_PERIOD,
X_TRANSACTION_RATE => rec.TRANSACTION_RATE,
X_UNREALISED_PL => null,
X_UPDATED_BY => fnd_global.user_id,
X_UPDATED_ON => l_sysdate,
X_YEAR_BASIS => rec.YEAR_BASIS,
X_CREATED_ON => l_sysdate,
X_EXCHANGE_RATE_ONE => rec.reval_ex_rate_one,
X_REALIZED_FLAG => 'Y',
X_OVERWRITE_TYPE => rec.ow_type,
X_OVERWRITE_VALUE => rec.ow_value,
X_OVERWRITE_REASON => null,
X_BATCH_ID => rec.batch_id,
X_CREATED_BY => fnd_global.user_id,
X_ACCOUNT_NO => rec.account_no,
X_SWAP_REF => rec.swap_ref,
X_NI_DISC_AMOUNT => rec.ni_disc_amount,
X_AMOUNT_TYPE => nvl(r.amount_type, 'REAL'),
X_QUANTITY => rec.quantity
);
Update XTR_DEALS
Set LAST_REVAL_BATCH_ID = rec.batch_id
Where rowid = l_deal_rowid;
Update XTR_ROLLOVER_TRANSACTIONS
Set LAST_REVAL_BATCH_ID = rec.batch_id
Where rowid = l_deal_rowid;
/* This procedure insert value into XTR_BATCH_EVENTS table */
/***********************************************************/
PROCEDURE xtr_insert_event(
p_batch_ID IN NUMBER) is
Cursor CHK_BATCH_RUN is
Select 'Y'
From XTR_BATCH_EVENTS
Where batch_id = p_batch_id
and event_code = 'REVAL';
select XTR_BATCH_EVENTS_S.NEXTVAL
into l_event_id from DUAL;
Insert into XTR_BATCH_EVENTS(batch_event_id, batch_id, event_code, authorized,
authorized_by, authorized_on, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
values(l_event_id, p_batch_id, 'REVAL', 'N', null, null, fnd_global.user_id,
l_sysdate, fnd_global.user_id, l_sysdate, fnd_global.login_id);
select DEFAULT_MARKET_DATA_SET
from xtr_product_types
where DEAL_TYPE = rec.deal_type and
PRODUCT_TYPE = rec.product_type;
select PARAMETER_VALUE_CODE into p_mds
from xtr_company_parameters
where COMPANY_CODE = rec.company_code and
PARAMETER_CODE = C_MARKET_DATA_SET;
select 'Y'
from XTR_DEALS
where deal_no = rec.deal_no
and deal_type not in ('CA', 'IG', 'ONC', 'NI')
and first_reval_batch_id is NOT NULL
and first_reval_batch_id <> rec.batch_id;
select 'Y'
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = rec.deal_no
and transaction_number = rec.trans_no
and deal_type in ('NI', 'ONC')
and first_reval_batch_id is NOT NULL
and first_reval_batch_id <> rec.batch_id;
select 'Y'
from XTR_HEDGE_ATTRIBUTES
where hedge_attribute_id = rec.deal_no
and first_batch_id is NOT NULL;
PROCEDURE UPDATE_FX_REVALS (l_deal_no IN NUMBER,
l_transaction_no IN NUMBER,
l_deal_type IN VARCHAR2) is
Begin
Delete from xtr_revaluation_details
where deal_no = l_deal_no
and transaction_no = l_transaction_no
and deal_type = l_deal_type;
End UPDATE_FX_REVALS;