[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select currency,
coupon_rate,
no_of_coupons_per_year,
maturity_date,
year_calc_type,
commence_date,
calc_type
from xtr_bond_issues
where bond_issue_code = p_bond_issue_code;
select commence_date,no_of_coupons_per_year,
first_coupon_date
from xtr_bond_issues
where bond_issue_code = p_bond_issue_code;
select coupon_date,
due_date,rate --bug 2804548
from xtr_bond_coupon_dates
where bond_issue_code = p_bond_issue_code
and coupon_date > l_coupon_date
order by coupon_date;
select max(coupon_date)
from xtr_bond_coupon_dates
where bond_issue_code = p_bond_issue_code
and coupon_date < p_next_coupon_date;
select dealer_code
from xtr_dealer_codes_v
where user_id = l_fnd_user;
select TAX_SETTLE_METHOD
from XTR_TAX_BROKERAGE_SETUP
where REFERENCE_CODE = p_tax_code;
select count(*)-1, min(coupon_date)
from xtr_bond_coupon_dates
where bond_issue_code = p_issue_code;
select due_date,rate
from xtr_bond_coupon_dates
where bond_issue_code=p_bond_issue_code
and coupon_date=p_cpn_date;
Insert into XTR_ROLLOVER_TRANSACTIONS (
deal_number,
transaction_number,
deal_type,
start_date,
no_of_days,
maturity_date,
interest_rate,
interest,
orig_coupon_amount,
deal_subtype,
product_type,
portfolio_code,
company_code,
cparty_code,
client_code,
currency,
deal_date,
status_code,
created_by,
created_on,
settle_date,
original_amount, --Added for Interest Override
tax_code,
tax_rate,
tax_amount,
tax_settled_reference,
coupon_due_date --bug 2804548
)
Values (p_deal_number,
l_transaction_number,
'BOND',
l_last_coupon_date,
l_nbr_days_in_period,
l_coupon_date,
l_coupon_rate,
l_coupon_amt,
l_coupon_amt,
p_deal_subtype,
p_product_type,
p_portfolio_code,
p_company_code,
p_cparty_code,
p_client_code,
l_currency,
p_deal_date,
'CURRENT',
l_xtr_user,
sysdate,
v_due_date,
l_original_amount,-- Added for Interest Override
p_income_tax_ref,
p_income_tax_rate,
l_income_tax_out,
p_income_tax_settled_ref,
v_due_date
);
-- also need to insert new tax row
IF (l_tax_settle_method IS NOT NULL) THEN
IF (l_tax_settle_method = 'NIA') THEN
-- for netting the coupon interest
l_coupon_amt := l_coupon_amt - l_income_tax_out;
-- insert new row with just tax
Insert into XTR_DEAL_DATE_AMOUNTS (
deal_type,
amount_type,
date_type,
deal_number,
transaction_number,
transaction_date,
currency,
amount,
amount_date,
transaction_rate,
cashflow_amount,
company_code,
deal_subtype,
product_type,
status_code,
client_code,
portfolio_code,
cparty_code,
settle,
dealer_code)
Values ('BOND',
'TAX',
'INCUR',
p_deal_number,
l_transaction_number,
l_coupon_date,
l_currency,
l_income_tax_out,
l_coupon_date,
0, -- ONC currently saves tax rate as 0
0, -- ONC currently saves cashflow amount as 0
p_company_code,
p_deal_subtype,
p_product_type,
'CURRENT',
p_client_code,
nvl(p_portfolio_code, 'NOTAPPL'),
p_cparty_code,
'N',
p_dealer_code);
Insert into XTR_DEAL_DATE_AMOUNTS (
deal_type,
amount_type,
date_type,
deal_number,
transaction_number,
transaction_date,
currency,
amount,
amount_date,
transaction_rate,
cashflow_amount,
company_code,
account_no,
status_code,
portfolio_code,
dealer_code,
client_code,
deal_subtype,
cparty_code,
settle,
product_type)
Values ('BOND',
'COUPON',
'COUPON',
p_deal_number,
l_transaction_number,
p_deal_date,
l_currency,
l_coupon_amt,
v_due_date,--bug 2804548
l_coupon_rate,
decode(p_deal_subtype,'BUY',1,-1) * l_coupon_amt,
p_company_code,
p_maturity_account_number,
p_status_code,
p_portfolio_code,
p_dealer_code,
p_client_code,
p_deal_subtype,
p_acceptor_code,
'N',
p_product_type);
l_least_inserted IN VARCHAR2,
l_ref_date IN DATE,
l_trans_num IN NUMBER,
l_last_row IN VARCHAR2,
g_chk_bal IN VARCHAR2,
g_expected_balance_bf IN OUT NOCOPY NUMBER,
g_balance_out_bf IN OUT NOCOPY NUMBER,
g_accum_interest_bf IN OUT NOCOPY NUMBER,
g_principal_adjust IN OUT NOCOPY NUMBER,
c_principal_action IN VARCHAR2,
c_principal_amount_type IN VARCHAR2,
c_principal_adjust IN NUMBER,
c_writoff_int IN NUMBER,
c_increase_effective_from IN DATE,
l_rounding_type IN VARCHAR2, --Add Interest Override
l_day_count_type IN VARCHAR2) IS --Add Interest Override
l_deal_date DATE;
select DEAL_DATE, COMPANY_CODE, DEAL_SUBTYPE,
PRODUCT_TYPE, PORTFOLIO_CODE, CURRENCY,
MATURITY_DATE, SETTLE_ACCOUNT_NO, CPARTY_CODE,
CLIENT_CODE, DEALER_CODE, YEAR_CALC_TYPE,
LIMIT_CODE, INTERNAL_TICKET_NO,
FACE_VALUE_AMOUNT, cparty_ref,
start_date, --Add Interest Override
DUAL_AUTHORISATION_BY, -- bug 3958736
DUAL_AUTHORISATION_ON
from XTR_DEALS
where DEAL_NO = l_deal_no
and DEAL_TYPE = 'RTMM';
select ACCOUNT_NUMBER
from XTR_BANK_ACCOUNTS_V
where PARTY_CODE = l_cparty
and BANK_SHORT_CODE = l_cparty_ref
and CURRENCY = l_ccy;
select ROUNDING_FACTOR,YEAR_BASIS,nvl(HCE_RATE,1) HCE_RATE
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_ccy;
select max(START_DATE)
from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = l_deal_no
and START_DATE <= l_ref_date --- <
and STATUS_CODE = 'CURRENT'
and TRANSACTION_NUMBER <>l_trans_num;
select rowid
from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = l_deal_no
and START_DATE >= l_start_date
and (MATURITY_DATE > l_start_date or l_last_row='Y') --- add
and STATUS_CODE = 'CURRENT'
-- and ((nvl(g_chk_bal,'N')='Y' and TRANSACTION_NUMBER=l_trans_num)
-- or nvl(g_chk_bal,'N')='N')
order by START_DATE desc,TRANSACTION_NUMBER desc;
select START_DATE,MATURITY_DATE,NO_OF_DAYS,BALANCE_OUT_BF,
BALANCE_OUT,PRINCIPAL_ADJUST,INTEREST_RATE,INTEREST,
INTEREST_SETTLED,PRINCIPAL_ACTION,TRANSACTION_NUMBER,
SETTLE_DATE,ACCUM_INTEREST_BF,PI_AMOUNT_DUE,PI_AMOUNT_RECEIVED,
ACCUM_INTEREST,ROWID,ADJUSTED_BALANCE,COMMENTS,
EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,PRINCIPAL_AMOUNT_TYPE,
ENDORSER_CODE,RATE_FIXING_DATE
from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = l_deal_no
and START_DATE >= l_start_date
and (MATURITY_DATE > l_start_date or l_last_row = 'Y')
and STATUS_CODE = 'CURRENT'
-- and ((nvl(g_chk_bal,'N') = 'Y' and TRANSACTION_NUMBER = l_trans_num)
-- or nvl(g_chk_bal,'N') = 'N')
order by START_DATE asc,TRANSACTION_NUMBER asc
for UPDATE OF START_DATE; --lock all rows until commit
select b.INTEREST_ACTION
from XTR_DEALS_V a,
XTR_PAYMENT_SCHEDULE_V b
where a.DEAL_NO = l_deal_no
and b.PAYMENT_SCHEDULE_CODE = a.PAYMENT_SCHEDULE_CODE;
select NULL
from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = l_deal_no
and (START_DATE = l_ref_date or MATURITY_DATE = l_ref_date)
and transaction_number <>l_trans_num
order by START_DATE desc,TRANSACTION_NUMBER desc ;
SELECT MAX(transaction_number)
INTO l_max_trans_no
FROM xtr_rollover_transactions_v
WHERE deal_number = l_deal_no
AND start_date = pmt.start_date;
if l_prv_row_exists = 'Y' and (nvl(l_least_inserted,'N') = 'Y' or l_trans_num = -1)
and nvl(g_chk_bal,'N') = 'N'
or nvl(g_chk_bal,'N') = 'Y' then
-- This is the row before the EARLIEST ROW CHANGED
-- ie reset its maturity date to the Start date of the row changed.
-- This is because the earliest row may have been inserted.
if nvl(c_principal_action,'@#@') = 'DECRSE' then
if nvl(c_principal_amount_type,'PRINFLW') = 'PRINFLW' then
pmt.PI_AMOUNT_DUE := nvl(c_principal_adjust,0);
update XTR_ROLLOVER_TRANSACTIONS
set START_DATE = pmt.START_DATE,
RATE_FIXING_DATE = pmt.START_DATE,
BALANCE_OUT_BF = pmt.BALANCE_OUT_BF,
BALANCE_OUT_BF_HCE = hce_balbf,
ACCUM_INTEREST_BF = pmt.ACCUM_INTEREST_BF,
ACCUM_INTEREST_BF_HCE = hce_accum_int_bf,
PI_AMOUNT_DUE = pmt.PI_AMOUNT_DUE,
PI_AMOUNT_RECEIVED = pmt.PI_AMOUNT_RECEIVED,
ADJUSTED_BALANCE = pmt.ADJUSTED_BALANCE,
BALANCE_OUT = pmt.BALANCE_OUT,
BALANCE_OUT_HCE = hce_balos,
PRINCIPAL_ADJUST_HCE = hce_princ,
PRINCIPAL_ADJUST = pmt.PRINCIPAL_ADJUST,
INTEREST = pmt.INTEREST,
INTEREST_SETTLED = pmt.INTEREST_SETTLED,
INTEREST_HCE = hce_interest,
ACCUM_INTEREST = pmt.ACCUM_INTEREST,
ACCUM_INTEREST_HCE = hce_accum_int,
SETTLE_DATE = pmt.SETTLE_DATE,
NO_OF_DAYS = pmt.NO_OF_DAYS,
MATURITY_DATE = pmt.MATURITY_DATE,
EXPECTED_BALANCE_BF = nvl(pmt.EXPECTED_BALANCE_BF,0),
EXPECTED_BALANCE_OUT = pmt.EXPECTED_BALANCE_OUT,
ENDORSER_CODE = l_date_exits
where ROWID = pmt.ROWID;
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER;
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,serial_reference,
dual_authorisation_by,
dual_authorisation_on)
values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
'COMENCE',l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,pmt.PRINCIPAL_ADJUST,hce_princ,
decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
,'FUND',pmt.PRINCIPAL_ADJUST
,'INVEST',(-1) * pmt.PRINCIPAL_ADJUST),0),
l_company,l_settle_acct ,'INCRSE',
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
l_dual_authorisation_by,
l_dual_authorisation_on);
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,serial_reference,
dual_authorisation_by,
dual_authorisation_on)
values ('RTMM',nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),
'SETTLE',
-- decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
-- decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
-- ,0,'FORCAST','SETTLE'),'SETTLE'),
l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,nvl(pmt.PRINCIPAL_ADJUST,0),
nvl(nvl(hce_princ,pmt.PRINCIPAL_ADJUST),0),
decode(nvl(g_chk_bal,'N'),'N',pmt.START_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',decode(l_subtype
,'FUND',(-1),1) * nvl(pmt.PRINCIPAL_ADJUST,0),0),
l_company,l_settle_acct ,'DECRSE',
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
l_dual_authorisation_by,
l_dual_authorisation_on);
end if; -- End of Insert rows for Principal Adjustments
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,serial_reference,
dual_authorisation_by,
dual_authorisation_on)
values ('RTMM','PRINFLW',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,'FORCAST','SETTLE'),
/*
decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,'FORCAST','SETTLE'),'SETTLE'),
decode(nvl(pmt.PRINCIPAL_ACTION,'@#@'),'DECRSE',
decode(nvl(pmt.PRINCIPAL_AMOUNT_TYPE,'PRINFLW'),'PRINFLW',
decode(nvl(pmt.PI_AMOUNT_RECEIVED,NULL),NULL,'FORCAST','SETTLE'),'SETTLE'),
decode(pmt.PI_AMOUNT_RECEIVED,NULL,'FORCAST','SETTLE')),
*/
l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,0,l_prin_decr),
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,0,round(l_prin_decr/l_hce_rate,rounding_fac)),
nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
decode(l_subtype,'FUND',(-1),1) *
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,0,l_prin_decr),
l_company,l_settle_acct ,'DECRSE',
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
l_dual_authorisation_by, -- bug 3958736
l_dual_authorisation_on);
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,
dual_authorisation_by, -- bug 3958736
dual_authorisation_on)
values ('RTMM','FACEVAL','RATESET',l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),hce_balos,
pmt.START_DATE,pmt.INTEREST_RATE,0,l_company,l_settle_acct ,NULL,
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,l_dealer,'N',l_client,
l_dual_authorisation_by, -- bug 3958736
l_dual_authorisation_on);
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,serial_reference,
dual_authorisation_by, -- bug 3958736
dual_authorisation_on)
values ('RTMM','INTSET',decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,'FORCAST','SETTLE'),
l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,pmt.PI_AMOUNT_DUE,pmt.INTEREST_SETTLED),
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,hce_due,hce_settled),
nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),pmt.INTEREST_RATE,
decode(l_subtype
,'FUND',(-1),1) * decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,nvl(pmt.PI_AMOUNT_DUE,0)
,nvl(pmt.INTEREST_SETTLED,0)),
l_company,l_settle_acct ,NULL,
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,substr(l_internal_ticket_no,1,12),
l_dual_authorisation_by, -- bug 3958736
l_dual_authorisation_on);
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and AMOUNT_TYPE = 'PRINFLW'
and ACTION_CODE = 'INCRSE';-- Why only INCRSE ????
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and DATE_TYPE = 'RATESET';
delete from XTR_ROLLOVER_TRANSACTIONS
where DEAL_NUMBER = l_deal_no
and START_DATE > l_nill_date
and SETTLE_DATE is null; --- >=
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = l_deal_no
and amount_date > l_nill_date;
update XTR_DEAL_DATE_AMOUNTS_V
set amount=nvl(pmt.BALANCE_OUT,0),
hce_amount=nvl(hce_balos,0)
where deal_type='RTMM' and amount_type='BALOUT' and deal_number=l_deal_no;
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,limit_code,limit_party,
dual_authorisation_by, -- bug 3958736
dual_authorisation_on)
values ('RTMM','BALOUT','COMENCE',
l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy ,nvl(pmt.BALANCE_OUT,0),
nvl(hce_balos,0),l_maturity ,pmt.INTEREST_RATE,0,
l_company,l_settle_acct ,NULL,
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,
nvl(l_limit_code,'NILL'),l_cparty,
l_dual_authorisation_by, -- bug 3958736
l_dual_authorisation_on);
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,limit_code,limit_party,
dual_authorisation_by, -- bug 3958736
dual_authorisation_on
)
values ('RTMM','WRTEINT','SETTLE',
l_deal_no,l_trans_num,
l_deal_date,l_ccy ,nvl(c_writoff_int,0),
round(nvl(c_writoff_int,0) / l_hce_rate,rounding_fac),
c_increase_effective_from,pmt.INTEREST_RATE,0,
l_company,l_settle_acct ,NULL,
l_cparty_acct ,l_subtype,l_product,
l_portfolio ,'CURRENT',l_cparty,
l_dealer,'N',l_client ,
null,l_cparty,
l_dual_authorisation_by, -- bug 3958736
l_dual_authorisation_on);