[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select PARAM_VALUE
from XTR_PRO_PARAM
where PARAM_NAME = p_param_name;
select PARAM_VALUE
from XTR_PRO_PARAM
where PARAM_NAME = p_param_name;
select m.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V m
where m.CURRENCY = l_ccy ;
select s.HCE_RATE
from XTR_MASTER_CURRENCIES s
where s.CURRENCY = l_ccy;
select a.deal_no,1 trans_no,a.start_date,a.maturity_date,a.next_coupon_date,a.interest_rate,
a.maturity_amount,a.coupon_action,a.bond_issue,a.currency,
decode(nvl(a.frequency,0),0,1,a.frequency) frequency,
a.coupon_rate,a.start_amount,a.status_code,a.bond_reneg_date,
a.bond_sale_date,a.deal_subtype,a.product_type,a.portfolio_code,a.cparty_code,
a.year_calc_type,capital_price,
a.day_count_type, a.rounding_type, -- AW Japan Project
decode(a.day_count_type,'B','Y','N') first_trans_flag -- AW Japan Project
from XTR_DEALS a
where a.deal_type = 'BOND'
and a.company_code = p_company
and a.deal_subtype IN ('BUY','ISSUE')
and a.status_code <> 'CANCELLED'
and a.start_date <= p_end_date
and (a.maturity_date >= p_start_date
or a.deal_no not in
( select b.deal_no
from XTR_ACCRLS_AMORT b
where b.company_code=p_company
and b.deal_type = 'BOND'
and b.amount_type <> 'CPMADJ'))
and a.maturity_date >= a.start_date;
select r.deal_number,r.cparty_code,r.interest,r.interest_hce,r.start_date,
r.maturity_date,r.interest_rate,r.deal_subtype,r.product_type,r.transaction_number,
r.portfolio_code,r.currency,
nvl(d.day_count_type,'L') day_count_type, -- AW Japan Project
nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'B',decode(r.transaction_number,2,'Y','N'),
'N') first_trans_flag -- AW Japan Project
from XTR_ROLLOVER_TRANSACTIONS r,
XTR_DEALS d
where r.deal_type = 'BOND'
and r.deal_number = l_deal_nos
and r.status_code = 'CURRENT'
and r.maturity_date > maturiting_date
and d.deal_no = r.deal_number -- AW Japan Project
and d.deal_type = 'BOND'
order by r.maturity_date;
select 'N'
from XTR_ACCRLS_AMORT
where deal_no=l_deal_nos
and trans_no=l_trans_nos
and deal_type ='BOND';
select year_calc_type
from xtr_bond_issues
where bond_issue_code=l_bond_issue;
select nvl(EFFECTIVE_CALCULATED_VALUE,0) accrls_value
from XTR_ACCRLS_AMORT
where deal_no=l_deal_nos
and trans_no =l_trans_nos
and deal_type = 'BOND'
and amount_type=l_amount_type
and period_to
select nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal
from XTR_ACCRLS_AMORT
where deal_no=l_deal_nos
and trans_no =l_trans_nos
and deal_type = 'BOND'
and amount_type=l_amount_type
and period_to
select trans_no,nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal
from XTR_ACCRLS_AMORT
where deal_no=l_deal_nos
and amount_type=l_amount_type
and deal_type='BOND'
and action_code='POS'
and period_to < p_end_date
and (trans_no,period_to)
in(select trans_no,max(period_to)
from XTR_ACCRLS_AMORT
where deal_no=l_deal_nos
and amount_type=l_amount_type
and action_code='POS'
and period_to < p_end_date
group by trans_no);
insert into XTR_ACCRLS_AMORT
(BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL,EFFECTIVE_CALCULATED_VALUE,ACTION_CODE)
values(p_batch_id,bond_det.deal_no,1,p_company,bond_det.deal_subtype,
'BOND',bond_det.currency,p_start_date,p_end_date,
bond_det.cparty_code,bond_det.product_type,
bond_det.portfolio_code,bond_det.interest_rate,
bond_det.maturity_amount,l_amount_type,
abs(l_amount_to_accrue_amort),NULL,
l_first_accrual_indic,starting_date,maturiting_date,
NULL,l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0),
nvl(l_face_discount,0)+nvl(l_coupon_discount,0)-nvl(l_accr_interest,0),l_action);
insert into XTR_ACCRLS_AMORT
(BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL,ACTION_CODE)
values(p_batch_id,bond_det.deal_no,1,p_company,bond_det.deal_subtype,
'BOND',bond_det.currency,p_start_date,p_end_date,
bond_det.cparty_code,bond_det.product_type,
bond_det.portfolio_code,bond_det.interest_rate,
bond_det.maturity_amount,l_amount_type,
l_amount_to_accrue_amort,NULL,
l_first_accrual_indic,starting_date,maturiting_date,
NULL,l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0),
decode(sign(l_amount_to_accrue_amort+nvl(l_accrls_amount_bal,0)),-1,'POS','REV'));
select parameter_value_code
from XTR_COMPANY_PARAMETERS
where company_code = p_company
and parameter_code = 'ACCNT_BPSTP';
select XTR_BATCHES_S.NEXTVAL
from DUAL;
Select XTR_BATCH_EVENTS_S.NEXTVAL
From DUAL;
select 1
from XTR_BATCH_EVENTS
where batch_id = p_batch_id
and event_code = 'REVAL'
and authorized = 'Y';
select m.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V m
where m.CURRENCY =l_ccy;
select PARAM_VALUE
from XTR_PRO_PARAM
where PARAM_NAME = p_param_name;
select PARAMETER_VALUE_CODE
from XTR_COMPANY_PARAMETERS
where company_code = p_company
and parameter_code = 'ACCNT_TSDTM';
select 1
from XTR_ROLLOVER_TRANSACTIONS
where deal_type = l_deal_type
and deal_number = l_deal_no
and transaction_number <> l_tran_no
and start_date <= l_date_from
and maturity_date <= l_date_to;
/* Select deals for accrual calculations */
/*---------------------------------------*/
cursor ACCRUAL_DEALS is select
-----------------------------------------------
-- 'TMM','RTMM','IRS','ONC','BOND'(Coupon)
-----------------------------------------------
a.status_code status_code,
a.deal_type deal_type,
a.deal_number deal_nos,
a.transaction_number trans_nos,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
a.currency ccy,
a.cparty_code cparty,
a.client_code client,
NULL action,
decode(a.deal_type,'BOND','CPMADJ', 'INTADJ') main_amt_type,
decode(a.deal_type,'BOND',a.interest, a.balance_out) main_amount,
decode(a.deal_type,'BOND',a.interest_hce, a.balance_out_hce) hce_amount,
a.interest_rate rate,
a.start_date date_from,
decode(a.maturity_date,NULL, 'PEREND', 'MATURE') date_type_to,
a.maturity_date date_to, -- AW 2113171 For ONC without maturity date
--nvl(a.maturity_date,p_end_date) date_to, -- old
a.year_calc_type year_calc_type,
a.no_of_days no_of_days,
NULL bond_issue,
a.maturity_date deal_action_date,
decode(a.deal_type,'ONC',a.interest + nvl(a.interest_refund,0),a.interest) override_amount, -- AW Japan Project
nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'B',decode(a.deal_type,'TMM', decode(a.transaction_number,1,'Y','N'),
'ONC', nvl(a.first_transaction_flag,'N'),
'BOND',decode(a.transaction_number,2,'Y','N'),
'N'),
'N') first_trans_flag -- AW Japan Project
from XTR_ROLLOVER_TRANSACTIONS a,
XTR_DEALS d
where a.company_code = p_company
and a.deal_number = d.deal_no
and a.deal_type = d.deal_type
and a.deal_type in ('TMM','RTMM','IRS','ONC','BOND')
and nvl(a.maturity_date,a.start_date+1) > a.start_date
and nvl(a.interest_rate,0) <> 0
and ((a.start_date <= p_end_date and a.deal_type <> 'BOND')
or (a.start_date <= p_end_date and a.deal_type = 'BOND' and
p_end_date >= (select b.start_date
from xtr_deals b
where b.deal_no = a.deal_number
and b.deal_type = 'BOND')))
and ((a.maturity_date >= p_start_date or a.maturity_date is NULL)
or (a.deal_number,a.transaction_number) not in (select b.deal_no,b.trans_no
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.deal_type in ('TMM','RTMM','IRS','ONC','BOND')))
and ((a.deal_type <> 'BOND' and a.status_code <> 'CANCELLED')
or (a.deal_type = 'BOND' and a.status_code not in ('CANCELLED','CLOSED')
and a.deal_subtype in ('BUY','ISSUE'))
and a.deal_number not in (select deal_no from
xtr_bond_alloc_details b
where b.deal_no = d.deal_no
and b.face_value = d.maturity_amount
and b.cross_ref_start_date = d.start_date)) -- bug 5490311
-----------------------------
-- BOND (Discount/Premium) --
-----------------------------
union all
select
a.status_code status_code,
a.deal_type deal_type,
a.deal_no deal_nos,
1 trans_nos,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
a.currency ccy,
a.cparty_code cparty,
a.client_code client,
NULL action,
decode(sign(a.capital_price-100),-1,'SLDISC','SLPREM') main_amt_type,
abs(a.maturity_amount) main_amount,
a.maturity_hce_amount hce_amount,
a.interest_rate rate,
decode(l_trade_settle,'TRADE',a.deal_date,a.start_date) date_from,
decode(a.maturity_date,NULL,'PEREND','MATURE') date_type_to, --Always MATURE cos maturity_date is not null
a.maturity_date date_to,
a.year_calc_type year_calc_type,
a.no_of_days no_of_days,
NULL bond_issue,
a.bond_sale_date deal_action_date, -- not use bond_reneg_date !
abs(a.maturity_amount) override_amount, -- AW Japan Project, not used
nvl(a.rounding_type,'R') rounding_type, -- AW Japan Project
decode(nvl(a.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
decode(nvl(a.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
decode(nvl(a.day_count_type,'L'),'B','Y','N') first_trans_flag -- AW Japan Project
from XTR_DEALS a
where a.company_code = p_company
and a.deal_type = 'BOND'
and a.deal_subtype in ('BUY','ISSUE')
and decode(l_trade_settle,'TRADE',a.deal_date,a.start_date) <= p_end_date
and ((a.maturity_amount <> 0
and a.deal_no not in ( select b.deal_no
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.trans_no = 1
and b.deal_type = 'BOND'
and b.action_code = 'POS'
and b.amount_type in ('SLDISC','SLPREM')))
or (a.maturity_date >= p_start_date
and a.deal_no in (select b.deal_no
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.trans_no = 1
and b.deal_type = 'BOND'
and b.action_code = 'POS'
and b.amount_type in ('SLDISC','SLPREM')
and nvl(b.calc_face_value,0) <> 0
and b.batch_id = ( select max(c.batch_id)
from XTR_ACCRLS_AMORT c
where c.company_code = p_company
and c.deal_no = b.deal_no
and c.trans_no = 1
and c.deal_type = 'BOND'
and c.action_code = 'POS'
and c.amount_type in ('SLDISC','SLPREM')))))
and a.status_code not in ('CANCELLED', 'CLOSED')
and a.deal_no not in (select deal_no from
xtr_bond_alloc_details b
where b.deal_no = a.deal_no
and b.face_value = a.maturity_amount
and b.cross_ref_start_date = a.start_date) -- bug 5490311
-------------------------------
-- NI (Straight Line Method) --
-------------------------------
-- Bug 2448432.
-- Removed references to company trade/settle date accounting method parameter.
-- Accrual of interest is not to begin until the deal start date always.
union all
select
a.status_code status_code,
a.deal_type deal_type,
a.deal_number deal_nos,
a.transaction_number trans_nos,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
a.currency ccy,
a.cparty_code cparty,
a.client_code client,
NULL action,
'INTADJ' main_amt_type,
a.interest main_amount,
a.interest_hce hce_amount,
a.interest_rate rate,
a.start_date date_from,
decode(a.maturity_date,NULL, 'PEREND', 'MATURE') date_type_to,
a.maturity_date date_to,
a.year_calc_type year_calc_type,
a.no_of_days no_of_days,
NULL bond_issue,
a.ni_reneg_date deal_action_date,
a.interest override_amount, -- AW Japan Project, not used
nvl(d.rounding_type,'R') rounding_type, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'F',1,0) forward_adjust, -- AW Japan Project
decode(nvl(d.day_count_type,'L'),'B','Y','N') first_trans_flag -- AW Japan Project
from XTR_ROLLOVER_TRANSACTIONS a,
XTR_DEALS d
where a.company_code = p_company
and a.deal_number = d.deal_no
and a.deal_type = d.deal_type
and a.deal_type = 'NI'
and a.deal_subtype in ('BUY','SHORT','ISSUE')
and a.status_code <> 'CANCELLED'
and nvl(a.interest_rate,0) <> 0
and a.start_date <= p_end_date
and (a.maturity_date >= p_start_date or
(a.deal_number,a.transaction_number,'INTADJ') not in (select b.deal_no,b.trans_no,b.amount_type
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.deal_type = 'NI'))
-----------------------------------------------
-- 'FXO','IRO','BDO','SWPTN' -- premium
-----------------------------------------------
union all
select
a.status_code status_code,
a.deal_type deal_type,
a.deal_no deal_nos,
1 trans_nos,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
nvl(a.premium_currency,a.currency) ccy,
a.cparty_code cparty,
a.client_code client,
a.settle_action action,
'PREMADJ' main_amt_type,
a.premium_amount main_amount,
a.premium_hce_amount hce_amount,
decode(a.deal_type,'FXO',a.transaction_rate,
'BDO',a.capital_price,a.interest_rate) rate, -- AW 2113171 No rate displayed for BDO.
a.premium_date date_from,
'MATURE' date_type_to,
a.expiry_date date_to,
a.year_calc_type year_calc_type,
a.no_of_days no_of_days,
a.bond_issue bond_issue,
a.settle_date deal_action_date,
a.premium_amount override_amount, -- AW Japan Project, not used
'R' rounding_type, -- AW Japan Project
l_days_adjust day_count_type, -- AW Japan Project
l_forward_adjust forward_adjust, -- AW Japan Project
'N' first_trans_flag -- AW Japan Project
from XTR_DEALS a
where a.company_code = p_company
and a.deal_type in ('IRO','BDO','SWPTN','FXO')
and a.premium_date <= p_end_date
and (a.expiry_date >= p_start_date
or a.deal_no not in ( select b.deal_no
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.deal_type in ('FXO','IRO','BDO','SWPTN')
and b.amount_type = 'PREMADJ')) -- AW 1395208
and a.status_code <> 'CANCELLED'
and nvl(a.premium_amount,0) <> 0
--------------------------------------------------------
-- 'FRA','BDO','IRO','SWPTN' -- interest AW 1395208
--------------------------------------------------------
union all
select
a.status_code status_code,
a.deal_type deal_type,
a.deal_no deal_nos,
1 trans_nos,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
a.currency ccy,
a.cparty_code cparty,
a.client_code client,
a.settle_action action,
decode(a.deal_type,'BDO',decode(a.deal_subtype,'BCAP','SLDISC','SCAP','SLDISC','SLPREM'),
'INTADJ') main_amt_type,
a.settle_amount main_amount,
a.settle_hce_amount hce_amount,
decode(a.deal_type,'BDO',a.exercise_price,a.settle_rate) rate,
a.start_date date_from,
'MATURE' date_type_to,
a.maturity_date date_to,
a.year_calc_type year_calc_type, -- if null, ACTUAL/ACTUAL later
a.no_of_days no_of_days,
a.bond_issue bond_issue,
a.settle_date deal_action_date,
a.settle_amount override_amount, -- only apply to FRA
decode(a.deal_type,'FRA',nvl(a.settle_rounding_type,'R'),
'R') rounding_type,
decode(a.deal_type,'FRA',decode(nvl(a.settle_day_count_type,l_days_adjust),'F','PRIOR',
'L','FOLLOWING',
'B','B',
l_days_adjust),
l_days_adjust) day_count_type,
decode(decode(a.deal_type,'FRA',nvl(a.settle_day_count_type,l_days_adjust),
l_days_adjust),
'F',1,'PRIOR',1,0) forward_adjust,
decode(a.deal_type,'FRA','Y','N') first_trans_flag
from XTR_DEALS a
where a.company_code = p_company
and a.deal_type in ('FRA','BDO','IRO','SWPTN')
and a.maturity_date is not null
and a.start_date < a.maturity_date -- avoid Bug 3006377 in BDO allowing Start >= Maturity Date
and a.start_date <= p_end_date
and (a.maturity_date >= p_start_date
or a.deal_no not in ( select b.deal_no
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.deal_type in ('FRA','BDO','IRO','SWPTN')
and b.amount_type in ('INTADJ','SLDISC','SLPREM')))
and a.status_code in ('EXERCISED','SETTLED')
and nvl(a.settle_amount,0) <> 0
order by 2,3,4;
select decode(l_trade_settle,'TRADE',cross_ref_deal_date,cross_ref_start_date) resale_recognition_date,
front_end_prem_disc,
face_value,
cross_ref_no
from XTR_BOND_ALLOC_DETAILS
where deal_no = l_deal_nos
and decode(l_trade_settle,'TRADE',cross_ref_deal_date,cross_ref_start_date) <= p_end_date
and (deal_no,cross_ref_no) not in (select deal_no,trans_no
from xtr_accrls_amort
where company_code = p_company);
select b.back_end_interest,
b.cross_ref_start_date,
b.face_value,
d.coupon_action -- 2422480 added
from XTR_BOND_ALLOC_DETAILS b,
XTR_DEALS d
where b.deal_no = l_deal_nos
and d.deal_no = l_deal_nos
and b.cross_ref_start_date between l_elig_resale_start and l_elig_resale_end;
select 'N'
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and deal_type = l_deal_type
and ((deal_type = 'NI' and amount_type = 'INTADJ') -- To differentiate from new amt type EFFINT for NI.
or (deal_type = 'BOND' and amount_type = l_amount_type and
l_amount_type in ('SLDISC','SLPREM') and action_code = 'POS')
or (deal_type = 'BOND' and amount_type = l_amount_type and
l_amount_type = 'CPMADJ' and trans_no = l_trans_nos) -- AW 2113171 1st accrual indic for coupon.
or (deal_type in ('TMM','RTMM','IRS','ONC') and trans_no = l_trans_nos) -- AW 2113171 1st accrual indic
or (deal_type in ('BDO','IRO','SWPTN') and amount_type = l_amount_type and -- AW 1395208
action_code = 'POS') -- AW 1395208
or (deal_type not in ('NI','BOND','TMM','RTMM','IRS','ONC','BDO','IRO','SWPTN'))); -- AW 1395208
select year_calc_type
from xtr_deals
where deal_no = l_deal_nos
and deal_type = l_deal_type;
select bond_issue,
capital_price,
coupon_action,
maturity_amount,
start_amount,
start_date
from xtr_deals
where deal_no = l_deal_nos
and deal_type = l_deal_type;
select amount
from XTR_DEAL_DATE_AMOUNTS
where deal_number = l_deal_nos
and transaction_number = 1
and amount_type = 'INT'
and date_type = 'COMENCE';
select year_calc_type,
price_rounding,
nvl(no_of_coupons_per_year,0),
calc_type,
commence_date, -- COMPOUND COUPON
maturity_date -- COMPOUND COUPON
from xtr_bond_issues
where bond_issue_code = l_bond_issue;
select nvl(decode(l_amount_type,'CPMADJ',EFFINT_ACCRLS_AMOUNT_BAL,ACCRLS_AMOUNT_BAL),0),
nvl(CALC_FACE_VALUE,0)
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and trans_no = l_trans_nos
and deal_type = l_deal_type
and amount_type = l_amount_type
and action_code in ('POS','REV') -- 2422480. Added 'REV'.
and batch_id < nvl(p_batch_id, l_batch_id) -- To handle inaugural batch. period_to < p_end_date
order by period_to desc, calc_face_value asc; -- 2422480. Added calc_face_value to handle multi-sales on same day.
select 'Y'
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and trans_no = l_trans_nos
and deal_type = l_deal_type
and amount_type = l_amount_type
and action_code = 'REV'
and batch_id < nvl(p_batch_id, l_batch_id) -- To handle inaugural batch. period_to < p_end_date
order by period_to desc;
select count(*)-1, -- Total FULL Coupon
min(coupon_date) -- Odd Coupon Maturity
from xtr_bond_coupon_dates
where bond_issue_code = p_issue_code;
select max(COUPON_DATE), -- Previous Coupon Date
greatest(count(*)-1,0) -- Previous Full Coupon
from XTR_BOND_COUPON_DATES
where BOND_ISSUE_CODE = l_bond_issue
and COUPON_DATE <= p_end_date;
select min(COUPON_DATE) -- Next Coupon Date
from XTR_BOND_COUPON_DATES
where BOND_ISSUE_CODE = l_bond_issue
and COUPON_DATE > p_end_date;
select sum(decode(action_code,'POS',nvl(ACCRLS_AMOUNT,0),nvl(-ACCRLS_AMOUNT,0)))
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and trans_no = l_trans_nos
and deal_type = 'BOND'
and amount_type = 'CPMADJ'
and action_code in ('POS','REV');
select nvl(sum(face_value),0)
from XTR_BOND_ALLOC_DETAILS
where deal_no = p_deal_no
and cross_ref_start_date <= p_date;
Insert into XTR_BATCHES(batch_id, company_code, period_start, period_end,
gl_group_id, upgrade_batch, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
values (l_batch_id, p_company, p_start_date, p_end_date,
null, nvl(p_upgrade_batch,'N'), fnd_global.user_id, l_sysdate,
fnd_global.user_id, l_sysdate, fnd_global.login_id);
/* Delete before recalculation */
/*-----------------------------*/
if p_batch_id is not null then
delete from XTR_ACCRLS_AMORT
where company_code = p_company
and batch_id = p_batch_id;
select nvl(min(COUPON_DATE),p_end_date)
into l_dummy_date
from XTR_BOND_COUPON_DATES
where BOND_ISSUE_CODE = l_bond_issue
and COUPON_DATE > l_deal_start;
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT,
CALC_FACE_VALUE,
YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
values (l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
decode(l_first_accrual_indic,'Y',period_start,
greatest(period_start,p_start_date)),
discprem_det.resale_recognition_date,
onc_det.cparty, onc_det.product, onc_det.portfolio,
onc_det.rate, onc_det.main_amount,
decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'), 'POS',
l_period_resale_amort,
discprem_det.face_value,
deal_yr_basis, l_first_accrual_indic,
decode(l_first_accrual_indic,'Y',period_start,
greatest(period_start,p_start_date)),
discprem_det.resale_recognition_date,
l_no_of_days, l_resale_total_amort);
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT,
CALC_FACE_VALUE,
YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
period_start, discprem_det.resale_recognition_date,
onc_det.cparty, onc_det.product, onc_det.portfolio,
onc_det.rate, onc_det.main_amount,
decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'), 'REV',
abs(discprem_det.front_end_prem_disc),
discprem_det.face_value,
deal_yr_basis, l_first_accrual_indic,
period_start, discprem_det.resale_recognition_date,
l_no_of_days, abs(discprem_det.front_end_prem_disc));
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT,
CALC_FACE_VALUE,
YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos, discprem_det.cross_ref_no,
p_company, onc_det.subtype, onc_det.deal_type,onc_det.ccy,
period_start, discprem_det.resale_recognition_date,
onc_det.cparty, onc_det.product, onc_det.portfolio,
onc_det.rate, onc_det.main_amount,
decode(sign(100-l_clean_price),-1,'SLUAMP','SLUAMD'), 'REV',
abs(discprem_det.front_end_prem_disc) - l_resale_total_amort,
discprem_det.face_value,
deal_yr_basis, l_first_accrual_indic,
period_start, discprem_det.resale_recognition_date,
l_no_of_days, l_resale_total_amort);
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT,
CALC_FACE_VALUE,
YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, ACCRLS_AMOUNT_BAL,
EFFINT_ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos, onc_det.trans_nos,
p_company, onc_det.subtype, onc_det.deal_type, onc_det.ccy,
l_group_period_start,
l_calc_period_end,
onc_det.cparty, onc_det.product, onc_det.portfolio,
onc_det.rate, l_adj_coupon_amt,
'CPMADJ', decode(sign(l_group_period_accrual_amt),-1,'REV','POS'),
abs(l_group_period_accrual_amt),
l_face_value_bal,
l_yr_basis, l_first_accrual_indic,
l_group_period_start,
l_calc_period_end,
l_no_of_days,
l_to_date_amort_amt,
xtr_fps2_p.interest_round(l_accrls_amount_bal,
l_rounding,onc_det.rounding_type));
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,CALC_FACE_VALUE,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
onc_det.subtype,
onc_det.deal_type,onc_det.ccy,
decode(l_first_accrual_indic,'Y',period_start,
greatest(period_start,p_start_date)),
least(onc_det.date_to,p_end_date),
onc_det.cparty,onc_det.product,
onc_det.portfolio,onc_det.rate,
onc_det.main_amount,
decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'),
'POS',decode(l_maturity_face_value,0,0,abs(l_period_accrual_amount)),
l_maturity_face_value,
deal_yr_basis,l_first_accrual_indic,
decode(l_first_accrual_indic,'Y',period_start,
greatest(period_start,p_start_date)),
least(onc_det.date_to,p_end_date),
l_no_of_days,l_amount_to_accrue_amort);
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,CALC_FACE_VALUE,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
onc_det.subtype,
onc_det.deal_type,onc_det.ccy,
period_start, onc_det.date_to,
onc_det.cparty,onc_det.product,
onc_det.portfolio,onc_det.rate,
onc_det.main_amount,
decode(sign(100-l_clean_price),-1,'SLPREM','SLDISC'),
'REV', l_amount_to_accrue_amort,
l_maturity_face_value,
deal_yr_basis,l_first_accrual_indic,
period_start, onc_det.date_to,
l_no_of_days,l_amount_to_accrue_amort);
select nvl(sum(decode(ACTION_CODE,'REV',-1*ACCRLS_AMOUNT,ACCRLS_AMOUNT)),0)
into l_sum_prev_accrls
from xtr_accrls_amort
where deal_no = onc_det.deal_nos
and trans_no = onc_det.trans_nos
and amount_type = 'CPMADJ'
and action_code in ('POS','REV');
select nvl(sum(back_end_interest),0)
into l_sum_backend_int
from XTR_BOND_ALLOC_DETAILS
where deal_no = onc_det.deal_nos
and cross_ref_start_date <= period_end
and cross_ref_start_date >= period_start; -- Bug 4613248 Added the condition
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE, CURRENCY,
PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT,
CALC_FACE_VALUE,
YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, ACCRLS_AMOUNT_BAL,
EFFINT_ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos, onc_det.trans_nos,
p_company, onc_det.subtype, onc_det.deal_type, onc_det.ccy,
period_start,
period_end,
onc_det.cparty, onc_det.product, onc_det.portfolio,
onc_det.rate, onc_det.main_amount, -- l_adj_coupon_amt, AW Japan
'CPMADJ',
decode(sign(l_period_accrual_amount),-1,'REV','POS'), -- AW Japan Project
abs(xtr_fps2_p.interest_round(abs(l_period_accrual_amount), -- AW Japan Project
l_rounding,onc_det.rounding_type)),
l_face_value_bal,
l_yr_basis, l_first_accrual_indic,
period_start,
period_end,
l_no_of_days,
l_to_date_amort_amt,
xtr_fps2_p.interest_round(l_accrls_amount_bal,
l_rounding,onc_det.rounding_type));
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(l_batch_id, onc_det.deal_nos,onc_det.trans_nos,p_company,
onc_det.subtype,
onc_det.deal_type,onc_det.ccy,p_start_date,l_actual_maturity,
--onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
onc_det.cparty,onc_det.product,
onc_det.portfolio,onc_det.rate,
onc_det.main_amount,onc_det.main_amt_type,
l_action_code, abs(l_period_accrual_amount),
deal_yr_basis,l_first_accrual_indic,
l_actual_start_date,l_actual_maturity,
l_no_of_days,l_amount_to_accrue_amort);
insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(l_batch_id,onc_det.deal_nos,onc_det.trans_nos,
p_company,onc_det.subtype,
onc_det.deal_type,onc_det.ccy,p_start_date,l_actual_maturity,
--onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
onc_det.cparty,onc_det.product,
onc_det.portfolio,onc_det.rate,
onc_det.main_amount,onc_det.main_amt_type,
decode(sign(l_amount_to_accrue_amort),-1,'POS','REV'),
abs(l_amount_to_accrue_amort),
deal_yr_basis,l_first_accrual_indic,l_actual_start_date,
l_actual_maturity,
l_no_of_days,0);
insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,
DEAL_SUBTYPE, DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(l_batch_id,onc_det.deal_nos,onc_det.trans_nos,
p_company,onc_det.subtype,
onc_det.deal_type,onc_det.ccy,p_start_date,
nvl(l_actual_maturity,p_end_date), --Bug 2416970
--decode(onc_det.deal_type,'NI',l_actual_maturity,p_end_date), Bug 2416970
--onc_det.deal_type,onc_det.ccy,p_start_date,p_end_date,
onc_det.cparty,onc_det.product,
onc_det.portfolio,onc_det.rate,
onc_det.main_amount,onc_det.main_amt_type,
l_action_code,abs(l_period_accrual_amount),
deal_yr_basis,l_first_accrual_indic,
l_actual_start_date,l_actual_maturity,
l_no_of_days,l_amount_to_accrue_amort);
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, l_batch_id, 'ACCRUAL', decode(p_upgrade_batch,'I','Y','N'),
null, null, fnd_global.user_id,
l_sysdate, fnd_global.user_id, l_sysdate, fnd_global.login_id);
select PARAM_VALUE
from XTR_PRO_PARAM
where PARAM_NAME = p_param_name;
select 'N'
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and deal_type = l_deal_type;
select nvl(ACCRLS_AMOUNT_BAL,0) accrls_bal, deal_subtype -- 3866372 to get previous deal subtype
from XTR_ACCRLS_AMORT
where deal_no = l_deal_nos
and deal_type = l_deal_type
and amount_type = 'INTADJ'
and action_code = 'POS'
and period_to < p_end_date
order by period_to desc;
select s.ROUNDING_FACTOR,s.YEAR_BASIS,s.HCE_RATE,s.IG_YEAR_BASIS
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = l_ccy;
select i.COMPANY_CODE,
i.PARTY_CODE cparty,
i.PORTFOLIO portfolio_code,
i.PRODUCT_TYPE,
i.TRANSFER_DATE balance_date,
i.CURRENCY,
nvl(i.BALANCE_OUT,0) bal_out, -- AW 2113171 Use abs(i.BALANCE_OUT) to avoid negative IG balance ???
nvl(i.ACCRUAL_INTEREST,0) main_amount,
nvl(i.INTEREST_RATE,0) int_rate,
i.TRANSACTION_NUMBER,
i.DEAL_NUMBER,
decode(i.DAY_COUNT_TYPE,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
nvl(i.ROUNDING_TYPE,'R') rounding_type, -- AW Japan Project
decode(nvl(i.DAY_COUNT_TYPE,'L'),'F',1,0) forward_adjust -- AW Japan Project
from XTR_INTERGROUP_TRANSFERS i
where i.COMPANY_CODE = p_company
and i.TRANSACTION_NUMBER =
(select max(k.TRANSACTION_NUMBER)
from XTR_INTERGROUP_TRANSFERS k
where k.COMPANY_CODE = i.COMPANY_CODE
and k.PARTY_CODE = i.PARTY_CODE
and k.CURRENCY = i.CURRENCY
and k.TRANSFER_DATE =
(select max(j.TRANSFER_DATE)
from XTR_INTERGROUP_TRANSFERS j
where j.TRANSFER_DATE <= p_end_date
and j.COMPANY_CODE = i.COMPANY_CODE
and j.PARTY_CODE = i.PARTY_CODE
and j.CURRENCY = i.CURRENCY))
order by i.CURRENCY;
select b.COMPANY_CODE,
b.ACCOUNT_NUMBER,
b.BALANCE_DATE,
a.CURRENCY,
a.BANK_CODE cparty,
nvl(b.accrual_interest,0) main_amount,
b.INTEREST_RATE int_rate,
nvl(b.statement_balance,0)+nvl(b.balance_adjustment,0) bal_out,
a.portfolio_code,
nvl(a.year_calc_type,'ACTUAL/ACTUAL') year_calc_type,
decode(b.DAY_COUNT_TYPE,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
nvl(b.ROUNDING_TYPE,'R') rounding_type, -- AW Japan Project
decode(nvl(b.DAY_COUNT_TYPE,'L'),'F',1,0) forward_adjust -- AW Japan Project
from XTR_BANK_ACCOUNTS a,
XTR_BANK_BALANCES b
where b.COMPANY_CODE = p_company
and a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
and a.PARTY_CODE = b.COMPANY_CODE
--and nvl(a.SETOFF_ACCOUNT_YN,'N') <> 'Y'
and b.BALANCE_DATE = (select max(c.BALANCE_DATE)
from XTR_BANK_BALANCES c
where c.BALANCE_DATE <= p_end_date
and c.COMPANY_CODE = b.COMPANY_CODE
and c.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER)
order by a.CURRENCY,a.ACCOUNT_NUMBER;
select DEAL_NUMBER,TRANSACTION_NUMBER,PRODUCT_TYPE
from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_TYPE = 'CA'
--and AMOUNT_TYPE = 'BAL'
and ACCOUNT_NO = v_account_number
and CURRENCY = v_currency
and COMPANY_CODE = p_company;
select TRANSFER_DATE
from XTR_INTERGROUP_TRANSFERS
where DEAL_NUMBER = v_deal_no
order by TRANSFER_DATE;
select b.BALANCE_DATE
from XTR_BANK_ACCOUNTS a,
XTR_BANK_BALANCES b
where b.COMPANY_CODE = v_comp
and a.PARTY_CODE = v_comp
and a.ACCOUNT_NUMBER = v_acct_no
and a.CURRENCY = v_ccy
and a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
order by BALANCE_DATE;
/* AW Japan Project - select from IG and CA table.
open ADJUST('ACCRUAL_DAYS_ADJUST');
insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(p_batch_id,l_deal_nos,l_trans_nos,p_company,l_subtype,
'IG',ig_det.currency,p_start_date,p_end_date,
ig_det.cparty,ig_det.product_type,
ig_det.portfolio_code,ig_det.int_rate,
abs(l_period_accrual_amount),'INTADJ', -- AW 2113171 Display same as accrl amt
--ig_det.main_amount,'INTADJ',
'POS',abs(l_period_accrual_amount),
l_year,l_first_accrual_indic,
ig_det.balance_date,l_actual_end_date,
calc_days,l_amount_to_accrue_amort);
insert into XTR_ACCRLS_AMORT (BATCH_ID,DEAL_NO,TRANS_NO,COMPANY_CODE,DEAL_SUBTYPE,
DEAL_TYPE,CURRENCY,PERIOD_FROM,PERIOD_TO,
CPARTY_CODE,PRODUCT_TYPE,PORTFOLIO_CODE,
INTEREST_RATE,TRANSACTION_AMOUNT,AMOUNT_TYPE,
ACTION_CODE,ACCRLS_AMOUNT,YEAR_BASIS,
FIRST_ACCRUAL_INDIC,ACTUAL_START_DATE,ACTUAL_MATURITY_DATE,
NO_OF_DAYS,ACCRLS_AMOUNT_BAL)
values(p_batch_id,l_deal_nos,l_trans_nos,p_company,l_subtype,
'CA',bk_det.currency,p_start_date,p_end_date,
bk_det.cparty,l_product_type,
bk_det.portfolio_code,bk_det.int_rate,
abs(l_period_accrual_amount),'INTADJ', -- AW 2113171 Display same as accrl amt
--bk_det.main_amount,'INTADJ',
'POS',abs(l_period_accrual_amount),
l_year,l_first_accrual_indic,
bk_det.balance_date,l_actual_end_date,
calc_days,l_amount_to_accrue_amort);
select PARAM_VALUE
from XTR_PRO_PARAM
where PARAM_NAME = p_param_name;
select PARAMETER_VALUE_CODE
from XTR_COMPANY_PARAMETERS
where company_code = p_company
and parameter_code = 'ACCNT_TSDTM';
select m.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V m
where m.CURRENCY = p_ccy;
cursor EFFINT_DEALS is select
a.status_code status_code,
a.deal_type deal_type,
a.deal_number deal_no,
a.transaction_number trans_no,
a.deal_subtype subtype,
a.product_type product,
a.portfolio_code portfolio,
a.currency ccy,
a.cparty_code cparty,
a.client_code client,
'EFFINT' EFFINT_amt_type,
a.balance_out face_value,
a.interest interest,
a.all_in_rate all_in_rate,
a.initial_fair_value initial_fair_value,
a.start_date deal_start,
a.maturity_date deal_maturity,
a.ni_reneg_date deal_action_date,
b.year_calc_type year_calc_type,
decode(b.calc_basis,'DISCOUNT','D','Y') calc_basis,
decode(b.day_count_type,'F','PRIOR','L','FOLLOWING','B') day_count_type, -- AW Japan Project
b.rounding_type rounding_type, -- AW Japan Project
a.trans_closeout_no resale_deal_no, -- Bug 2448432.
a.interest_rate interest_rate --bug 4969194
from XTR_ROLLOVER_TRANSACTIONS a,
XTR_DEALS b
where a.company_code = p_company
and a.deal_type = 'NI'
and a.deal_subtype in ('BUY','SHORT','ISSUE')
and b.company_code = p_company
and b.deal_no = a.deal_number
and b.deal_type = a.deal_type
and a.status_code <> 'CANCELLED'
and nvl(a.all_in_rate,0) <> 0
and a.start_date <= p_batch_end
--and (a.maturity_date >= p_batch_start
and (nvl(a.ni_reneg_date,a.maturity_date) >= p_batch_start
or (a.deal_number,a.transaction_number,'EFFINT') not in (select b.deal_no,b.trans_no,b.amount_type
from XTR_ACCRLS_AMORT b
where b.company_code = p_company
and b.deal_type = 'NI'));
select 'N'
from XTR_ACCRLS_AMORT
where deal_no = p_deal_no
and trans_no = p_trans_no
and deal_type = p_deal_type
and amount_type = p_amt_type;
select nvl(EFFINT_ACCRLS_AMOUNT_BAL,0)
from XTR_ACCRLS_AMORT
where deal_no = p_deal_no
and trans_no = p_trans_no
and deal_type = p_deal_type
and amount_type = p_amt_type
and action_code = 'POS'
and batch_id < nvl(p_cur_batch_id, p_new_batch_id)
order by period_to desc;
select sum(decode(action_code,'POS',ACCRLS_AMOUNT,-ACCRLS_AMOUNT))
from XTR_ACCRLS_AMORT
where deal_no = p_deal_no
and trans_no = p_trans_no
and deal_type = p_deal_type
and amount_type = p_amt_type
and action_code in ('POS','ADJ'); -- Bug 2448432. Added new action code ADJ.
Select day_count_type
into l_day_cnt_type
from xtr_deals
where deal_no = p_deal_no;
select sum(ACCRLS_AMOUNT)
into l_cumm_amt_bal
from xtr_accrls_amort
where company_code = p_company
and batch_id < p_new_batch_id
and deal_no = p_deal_no
and trans_no = p_trans_no
and amount_type = p_amt_type
and action_code = p_action_code;
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
CURRENCY, PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
ACCRLS_AMOUNT_BAL)
values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
p_company, EFF.subtype, EFF.deal_type,
EFF.ccy, p_batch_start, l_actual_start,
--EFF.ccy, p_batch_start, p_batch_end,
EFF.cparty, EFF.product, EFF.portfolio,
EFF.all_in_rate, EFF.interest,
EFF.EFFINT_amt_type, decode(sign(l_period_accrual_amount),-1,'ADJ','POS'),
abs(nvl(l_period_accrual_amount,0)),
l_year_basis, l_first_accrual_indic,
l_deal_start, EFF.deal_maturity,
-- l_actual_start, EFF.deal_maturity,
nvl(l_no_of_days,0),
abs(l_amount_to_accrue_amort),
l_accrls_amt_bal);
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
CURRENCY, PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
ACCRLS_AMOUNT_BAL)
values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
p_company, EFF.subtype, EFF.deal_type,
EFF.ccy, p_batch_start, l_actual_start,
--EFF.ccy, p_batch_start, p_batch_end,
EFF.cparty, EFF.product, EFF.portfolio,
EFF.all_in_rate, EFF.interest,
EFF.EFFINT_amt_type, 'REV',
xtr_fps2_p.interest_round(l_REV_amt,l_rounding,EFF.rounding_type),
l_year_basis, l_first_accrual_indic,
l_deal_start, EFF.deal_maturity,
--l_actual_start, EFF.deal_maturity,
nvl(l_no_of_days,0), 0,
0);
insert into XTR_ACCRLS_AMORT (BATCH_ID, DEAL_NO, TRANS_NO,
COMPANY_CODE, DEAL_SUBTYPE, DEAL_TYPE,
CURRENCY, PERIOD_FROM, PERIOD_TO,
CPARTY_CODE, PRODUCT_TYPE, PORTFOLIO_CODE,
INTEREST_RATE, TRANSACTION_AMOUNT,
AMOUNT_TYPE, ACTION_CODE,
ACCRLS_AMOUNT, YEAR_BASIS, FIRST_ACCRUAL_INDIC,
ACTUAL_START_DATE, ACTUAL_MATURITY_DATE,
NO_OF_DAYS, EFFINT_ACCRLS_AMOUNT_BAL,
ACCRLS_AMOUNT_BAL)
values(p_new_batch_id, EFF.deal_no, EFF.trans_no,
p_company, EFF.subtype, EFF.deal_type,
EFF.ccy, p_batch_start, l_actual_start,
--EFF.ccy, p_batch_start, p_batch_end,
EFF.cparty, EFF.product, EFF.portfolio,
EFF.all_in_rate, EFF.interest,
EFF.EFFINT_amt_type, 'POS',
xtr_fps2_p.interest_round(abs(l_period_accrual_amount),
l_rounding,EFF.rounding_type),
l_year_basis, l_first_accrual_indic,
l_deal_start, EFF.deal_maturity,
--l_actual_start, EFF.deal_maturity,
nvl(l_no_of_days,0),
xtr_fps2_p.interest_round(abs(l_amount_to_accrue_amort),
l_rounding,EFF.rounding_type),
xtr_fps2_p.interest_round( l_accrls_amt_bal,l_rounding,EFF.rounding_type));
select ROWID,DEAL_NO,TRANS_NO,DEAL_TYPE,DEAL_SUBTYPE,PRODUCT_TYPE,PORTFOLIO_CODE,ACTION_CODE,
CURRENCY,ACCRLS_AMOUNT,AMOUNT_TYPE ACC_AMOUNT_TYPE
from XTR_ACCRLS_AMORT
where COMPANY_CODE = p_company
and PERIOD_TO = p_end_date
order by currency;
select s.hce_rate
from XTR_MASTER_CURRENCIES s
where s.CURRENCY = l_ccy;
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,deal_number,transaction_date,
currency,amount,hce_amount,amount_date,action_code,
cashflow_amount,company_code,transaction_number,
deal_subtype,authorised,product_type,status_code,
portfolio_code)
values
(l_type,l_amttype,l_date_ty,l_deal_no,l_sysdate,
l_ccy,l_acclrs_amount,l_amt_hce,p_end_date,l_action,0,
p_company,l_trans_nos,l_subty,'N',l_product,'CURRENT',
l_portfolio);
update XTR_ACCRLS_AMORT
set TRANSFERED_ON = l_sysdate
where rowid=l_rowid;