[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_INSERT_FOR_CASHFLOW IN VARCHAR2,
P_KNOCK_TYPE IN VARCHAR2,
P_KNOCK_INSERT_TYPE IN VARCHAR2,
P_SELL_AMOUNT IN NUMBER,
P_BUY_AMOUNT IN NUMBER,
P_SELL_HCE_AMOUNT IN NUMBER,
P_BUY_HCE_AMOUNT IN NUMBER,
P_SELL_ACCOUNT_NO IN VARCHAR2,
P_OLD_SELL_ACCOUNT_NO IN VARCHAR2,
P_BUY_ACCOUNT_NO IN VARCHAR2,
P_OLD_BUY_ACCOUNT_NO IN VARCHAR2,
P_VALUE_DATE IN DATE,
P_EXPIRY_DATE IN DATE,
P_OPTION_COMMENCEMENT IN DATE,
P_COMMENTS IN VARCHAR2,
P_OLD_STATUS_CODE IN VARCHAR2,
P_QUICK_INPUT IN VARCHAR2,
P_START_AMOUNT IN NUMBER,
P_START_HCE_AMOUNT IN NUMBER,
P_MATURITY_AMOUNT IN NUMBER,
P_MATURITY_HCE_AMOUNT IN NUMBER,
P_MATURITY_ACCOUNT_NO IN VARCHAR2,
P_OLD_MATURITY_ACCOUNT_NO IN VARCHAR2,
P_MATURITY_BALANCE_AMOUNT IN NUMBER,
P_MATURITY_BALANCE_HCE_AMOUNT IN NUMBER,
P_INTEREST_AMOUNT IN NUMBER,
P_INTEREST_HCE_AMOUNT IN NUMBER,
P_RISKPARTY_LIMIT_CODE IN VARCHAR2,
P_RISKPARTY_CODE IN VARCHAR2,
P_BOND_ISSUE IN VARCHAR2,
P_COUPON_ACTION IN VARCHAR2,
P_ACCRUED_INTEREST_PRICE IN NUMBER,
P_CUM_COUPON_DATE IN DATE,
P_NEXT_COUPON_DATE IN DATE,
P_COUPON_RATE IN NUMBER,
P_FREQUENCY IN NUMBER,
P_ACCEPTOR_CODE IN VARCHAR2,
P_CAPITAL_PRICE IN NUMBER,
P_PREMIUM_CURRENCY IN VARCHAR2,
P_CONTRACT_RATE IN NUMBER,
P_CONTRACT_COMMISSION IN NUMBER,
P_CONTRACT_FEES IN NUMBER,
P_BASE_RATE IN NUMBER,
P_NI_PROFIT_LOSS IN NUMBER,
P_RATE_FIXING_DATE IN DATE,
P_PROFIT_LOSS IN NUMBER,
P_OLD_PROFIT_LOSS IN NUMBER,
P_FX_RO_PD_RATE IN NUMBER,
P_OLD_FX_RO_PD_RATE IN NUMBER,
P_FX_M1_DEAL_NO IN NUMBER,
P_OLD_FX_M1_DEAL_NO IN NUMBER) is
--
cursor C_GET_COUNTRY (pc_party_code varchar2) is
select country_code
from XTR_parties_V
where party_code = pc_party_code;
select nvl(limit_weighting,100)
from xtr_fx_period_weightings_v
where deal_type = v_deal_type
and deal_subtype = v_deal_subtype;
select CURRENCY_FIRST||'/'||CURRENCY_SECOND,CURRENCY_FIRST
from XTR_BUY_SELL_COMBINATIONS
where (CURRENCY_BUY = P_CURRENCY_BUY and CURRENCY_SELL = P_CURRENCY_SELL)
or (CURRENCY_BUY = P_CURRENCY_SELL and CURRENCY_SELL = P_CURRENCY_BUY);
select 1
from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = P_DEAL_NO
and AMOUNT_TYPE = 'FXOBUY'
and DATE_TYPE = 'VALUE';
select 1
from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO and
deal_type = 'BDO' and
date_type = 'SETTLE';
select m.ROUNDING_FACTOR
from XTR_PRO_PARAM_V p,
XTR_MASTER_CURRENCIES_V m
where p.PARAM_NAME = 'SYSTEM_FUNCTION_CCY'
and m.CURRENCY = p.PARAM_VALUE;
select s.HCE_RATE
from XTR_MASTER_CURRENCIES s
where s.CURRENCY = c_currency;
select YEAR_BASIS,CALC_TYPE,RIC_CODE
from XTR_BOND_ISSUES
where BOND_ISSUE_CODE = P_BOND_ISSUE
and AUTHORISED = 'Y'
and ((ISSUER=P_COMPANY_CODE and P_DEAL_SUBTYPE='ISSUE') or P_DEAL_SUBTYPE<>'ISSUE')
and nvl(BOND_OR_DEBENTURE_ISSUE,'B') = 'B';
select 1
from XTR_DEAL_DATE_AMOUNTS
where DEAL_NUMBER = P_DEAL_NO
and AMOUNT_TYPE = 'BAL_FV'
and DATE_TYPE = 'MATURE';
select 1
from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO and
deal_type = 'SWPTN' and
date_type = 'SETTLE';
select 1 from xtr_deal_date_amounts
where company_code =P_COMPANY_CODE
and deal_number = P_DEAL_NO
and deal_type = 'FXO'
and transaction_number = 1
and amount_type = 'PREMIUM'
and date_type = 'PREMIUM';
Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
the deal no for the deal. In that case, let us assume that the user modified the deal account number as
'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
account number as is.
We basically do not want to overwrite an account number set at settlement by original account number.
*/
If P_Settle_Account_No <> P_Old_Settle_Account_No then
v_settle_account_no := P_Settle_Account_No;
if P_ACTION = 'INSERT' and P_STATUS_CODE = 'CURRENT' then
if P_DEAL_TYPE = 'FRA' then
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,
product_type,status_code,portfolio_code,
dealer_code,client_code,cparty_code,settle,limit_code,
limit_party,commence_date,quick_input)
values ('FRA','FACEVAL','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_FACE_VALUE_AMOUNT,P_FACE_VALUE_HCE_AMOUNT,
P_START_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
NULL,P_CPARTY_CODE,P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,commence_date,QUICK_INPUT)
values ('FRA','N/A','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,commence_date,QUICK_INPUT)
values ('FRA','N/A','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,commence_date,QUICK_INPUT,LIMIT_CODE,LIMIT_PARTY)
values ('FRA','N/A','LIMIT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
P_START_DATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT,
nvl(P_LIMIT_CODE,'NILL'),P_CPARTY_CODE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,commence_date,QUICK_INPUT)
values ('FRA','FACEVAL','RATESET',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
P_RATE_FIXING_DATE,P_SETTLE_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,cparty_code,settle,client_code,
limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_CPARTY_CODE,'N',P_CLIENT_CODE,
decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,cparty_code,settle,
client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
P_DEALER_CODE,l_combin,P_QUICK_INPUT);
if nvl(P_INSERT_FOR_CASHFLOW,'N') = 'Y' then
insert into XTR_DEAL_DATE_AMOUNTS
(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,
deal_subtype,product_type,status_code,cparty_code,settle,
client_code,portfolio_code,dealer_code,currency_combination,
exposure_ref_date,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOBUY','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
P_VALUE_DATE,P_TRANSACTION_RATE,
decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',P_BUY_AMOUNT,0),
P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
deal_subtype,product_type,status_code,cparty_code,settle,
client_code,portfolio_code,dealer_code,currency_combination,
exposure_ref_date,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOSELL','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
P_VALUE_DATE,P_TRANSACTION_RATE,
decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',(-1)*P_SELL_AMOUNT,0),
P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
P_DEALER_CODE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,QUICK_INPUT)
values('FXO','N/A','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,QUICK_INPUT)
values('FXO','N/A','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,deal_subtype,product_type,status_code,
dealer_code,cparty_code,client_code,portfolio_code,
settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'BUY','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,P_VALUE_DATE,
P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'B',0,P_BUY_AMOUNT),P_COMPANY_CODE,
P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),
P_STATUS_CODE,P_DEALER_CODE,P_CPARTY_CODE,
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,cparty_account_no,deal_subtype,
product_type,status_code,dealer_code,cparty_code,
client_code,portfolio_code,settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'SELL','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,nvl(P_BUY_HCE_AMOUNT,0),
P_VALUE_DATE,P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'S',0,(-1) * P_SELL_AMOUNT),
P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,cparty_account_no,deal_subtype,
product_type,status_code,dealer_code,cparty_code,
client_code,portfolio_code,settle,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'N/A','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,nvl(P_SELL_HCE_AMOUNT,0),
P_DEAL_DATE,P_TRANSACTION_RATE,0,
P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),P_STATUS_CODE,
P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',l_combin,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,
commence_date,quick_input,limit_code)
values('NI','COMENCE','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_START_AMOUNT,P_START_HCE_AMOUNT,P_START_DATE,P_INTEREST_RATE,
decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*P_START_AMOUNT,0), -- Bug 3776211
P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT,
decode(P_RISKPARTY_LIMIT_CODE, null,
decode(P_DEAL_SUBTYPE,'SELL','NILL',NULL),NULL)); -- jhung bug 1477157
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,commence_date,quick_input)
values('NI','INTL_FV',decode(P_DEAL_SUBTYPE,'SELL','COMENCE','COVER','COMENCE','MATURE'),
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,'COVER',P_START_DATE,P_MATURITY_DATE),
p_INTEREST_RATE,0,
P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,
limit_code,limit_party,commence_date,quick_input)
values('NI','BAL_FV','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_MATURITY_BALANCE_AMOUNT,
P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
-- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
P_PRODUCT_TYPE,
nvl(P_RISKPARTY_LIMIT_CODE, decode(P_LIMIT_CODE, null, decode(P_DEAL_SUBTYPE,
'BUY','NILL','ISSUE', 'NILL', NULL), P_LIMIT_CODE)),
nvl(P_RISKPARTY_CODE,decode(P_DEAL_SUBTYPE,'BUY',P_CPARTY_CODE,null)), --jhung bug 1477157
P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,deal_number,
transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,portfolio_code,status_code,
dealer_code,client_code,deal_subtype,cparty_code,
settle,product_type,commence_date,quick_input)
values('NI','INT','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_INTEREST_AMOUNT,P_INTEREST_HCE_AMOUNT,
P_START_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_PORTFOLIO_CODE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code,commence_date,quick_input)
values('NI','N/A','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_START_AMOUNT,P_START_HCE_AMOUNT,P_DEAL_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,
commence_date,quick_input)
values('NI','BALCOM','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_START_AMOUNT,
P_START_HCE_AMOUNT,P_MATURITY_DATE,
P_INTEREST_RATE, 0,
P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
insert into XTR_ROLLOVER_TRANSACTIONS
(deal_number,transaction_number,deal_type,start_date,no_of_days,
maturity_date,interest_rate,interest,interest_hce,deal_subtype,
product_type,company_code,cparty_code,client_code,currency,
deal_date,status_code,created_by,created_on,settle_date)
values(P_DEAL_NO,l_trans_num,'BOND',l_start_date,(coupon_date -
l_start_date),coupon_date,P_COUPON_RATE,coupon,coupon_hce,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_COMPANY_CODE,P_CPARTY_CODE,
P_CLIENT_CODE,P_CURRENCY,P_DEAL_DATE,'CURRENT',L_USER,
L_SYSDATE,coupon_date);
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,portfolio_code,
dealer_code,client_code,deal_subtype,cparty_code,settle,product_type)
values
('BOND','COUPON','COUPON',P_DEAL_NO,l_trans_num,P_DEAL_DATE,
P_CURRENCY,coupon,coupon_hce,coupon_date,P_COUPON_RATE,
decode(P_DEAL_SUBTYPE,'BUY',1,-1) * coupon,P_COMPANY_CODE,
P_MATURITY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
P_PRODUCT_TYPE);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type)
values('BOND','COMENCE','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_START_AMOUNT,P_START_HCE_AMOUNT,
P_START_DATE,P_INTEREST_RATE,
decode(P_DEAL_SUBTYPE,'BUY',-1,1) * P_START_AMOUNT,
P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,
limit_code,limit_party)
values('BOND','INTL_FV','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
P_MATURITY_DATE,P_INTEREST_RATE,
decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
P_MATURITY_AMOUNT,P_COMPANY_CODE,
P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
P_PRODUCT_TYPE,nvl(P_LIMIT_CODE,'NILL'),P_ACCEPTOR_CODE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,status_code,
portfolio_code,dealer_code,client_code,deal_subtype,
cparty_code,settle,product_type)
values('BOND','INT','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_INT_VALUE,round(P_INT_VALUE/hce_rate,round_fac),P_START_DATE,
P_INTEREST_RATE,0,P_COMPANY_CODE,P_STATUS_CODE,
P_PORTFOLIO_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,status_code,
portfolio_code,dealer_code,client_code,deal_subtype,
cparty_code,settle,product_type)
values('BOND',decode(sign(P_PREM_VALUE),-1,'DISC','PREMIUM'),
'COMENCE',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
abs(P_PREM_VALUE),abs(round(P_PREM_VALUE/hce_rate,round_fac)),
P_START_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
limit_code,limit_party,portfolio_code)
values('IRO','N/A','LIMIT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_EXPIRY_DATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code)
values('IRO','FACEVAL','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_EXPIRY_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,dealer_code,
client_code,cparty_code,settle,portfolio_code)
values('IRO','PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
nvl(P_PREMIUM_DATE,P_START_DATE),0,
decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('IRO','FACEVAL','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_DEAL_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('IRO','FACEVAL','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
0,P_START_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('IRO','N/A','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
limit_code,limit_party,portfolio_code)
values('BDO','N/A','LIMIT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_EXPIRY_DATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code)
values('BDO','FACEVAL','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_EXPIRY_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,dealer_code,
client_code,cparty_code,settle,portfolio_code)
values('BDO','PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
nvl(P_PREMIUM_DATE,P_START_DATE),0,
decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('BDO','FACEVAL','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_DEAL_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('BDO','FACEVAL','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
P_START_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,
deal_subtype,product_type,status_code,dealer_code,
client_code,cparty_code,portfolio_code)
values('BDO','N/A','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
limit_code,limit_party,portfolio_code,contract_code)
values('FUT','FACEVAL','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,
nvl(P_CURRENCY_BUY,'N/A'),
nvl(P_START_AMOUNT,0),nvl(P_START_HCE_AMOUNT,
P_START_AMOUNT),P_EXPIRY_DATE,nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
dealer_code,client_code,cparty_code,settle,
portfolio_code,contract_code)
values('FUT','PREMIUM','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',
P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code)
values('FUT','DEALT','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
limit_code,limit_party,portfolio_code)
values('SWPTN','N/A','LIMIT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
P_EXPIRY_DATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code)
values('SWPTN','FACEVAL','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
P_EXPIRY_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,dealer_code,
client_code,cparty_code,settle,portfolio_code)
values('SWPTN','PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
nvl(P_PREMIUM_DATE,P_START_DATE),0,
decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
values('SWPTN','FACEVAL','DEALT',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
dealer_code,client_code,cparty_code,settle,portfolio_code)
values('SWPTN','SETTLE','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
nvl(P_SETTLE_DATE,P_START_DATE),0,
decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --BUG 2910654
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code)
values('SWPTN','FACEVAL','COMENCE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
P_START_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',P_PORTFOLIO_CODE);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
values('SWPTN','N/A','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
elsif P_ACTION = 'UPDATE' then
-- Delete rows in DDA for cancelled transactions
if P_STATUS_CODE = 'CANCELLED' then
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO;
delete from XTR_ROLLOVER_TRANSACTIONS
where deal_number = P_DEAL_NO;
update XTR_ROLLOVER_TRANSACTIONS
set STATUS_CODE = 'CANCELLED'
where deal_number = P_DEAL_NO;
update XTR_PARCEL_SPLITS
set status_code = 'CANCELLED'
where deal_no = P_DEAL_NO
and parcel_size = nvl(parcel_remaining,0);
update XTR_ROLLOVER_TRANSACTIONS
set STATUS_CODE = 'CANCELLED'
where DEAL_NUMBER = P_DEAL_NO
and TRANS_CLOSEOUT_NO is null;
update XTR_DEAL_DATE_AMOUNTS
set amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
hce_amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
amount_date = decode(DATE_TYPE,'COMENCE',P_START_DATE,'MATURE',P_MATURITY_DATE,'DEALT',P_DEAL_DATE,
'RATESET',P_RATE_FIXING_DATE,amount_date),
transaction_rate = decode(DATE_TYPE,'COMENCE',P_INTEREST_RATE,'MATURE',P_INTEREST_RATE,
'RATESET',P_SETTLE_RATE,transaction_rate),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
action_code = NULL,
limit_code = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',nvl(P_LIMIT_CODE,'NILL'),NULL),NULL),
limit_party = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',P_CPARTY_CODE,NULL),NULL)
where DEAL_NUMBER = P_DEAL_NO
and DEAL_TYPE = 'FRA'
and AMOUNT_TYPE <> 'SETTLE'
and DATE_TYPE <> 'LIMIT';
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
HCE_AMOUNT = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
AMOUNT_DATE = P_SETTLE_DATE,
STATUS_CODE = P_STATUS_CODE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
limit_code = nvl(P_LIMIT_CODE,'NILL'),
limit_party = P_CPARTY_CODE
where DEAL_NUMBER = P_DEAL_NO
and DATE_TYPE = 'LIMIT'
and DEAL_TYPE = 'FRA';
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT = nvl(P_SETTLE_AMOUNT,0),
ACTION_CODE = P_SETTLE_ACTION,
HCE_AMOUNT = nvl(P_SETTLE_HCE_AMOUNT,0),
CASHFLOW_AMOUNT = (decode(P_SETTLE_ACTION,'PAY',-1,1) * nvl(P_SETTLE_AMOUNT,0)),
CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
ACCOUNT_NO = nvl(v_SETTLE_ACCOUNT_NO, account_no),
AMOUNT_DATE = P_SETTLE_DATE,
STATUS_CODE = P_STATUS_CODE,
TRANSACTION_RATE = P_SETTLE_RATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE
where DEAL_NUMBER = P_DEAL_NO
and AMOUNT_TYPE = 'SETTLE'
and DATE_TYPE = 'SETTLE'
and DEAL_TYPE = 'FRA';
insert into XTR_DEAL_DATE_AMOUNTS
(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,cparty_account_no,action_code,
deal_subtype,product_type,status_code,portfolio_code,
dealer_code,client_code,cparty_code,settle)
values('FRA','SETTLE','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
P_SETTLE_DATE,nvl(P_SETTLE_RATE,0),
decode(P_SETTLE_ACTION,'PAY',-1,1) *
nvl(P_SETTLE_AMOUNT,0),P_COMPANY_CODE,
P_SETTLE_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,P_SETTLE_ACTION,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_PORTFOLIO_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N');
update XTR_DEAL_DATE_AMOUNTS
set amount = decode(amount_type,'FXOBUY',P_BUY_AMOUNT,P_SELL_AMOUNT),
hce_amount = decode(amount_type,'FXOBUY',P_BUY_HCE_AMOUNT,P_SELL_HCE_AMOUNT),
amount_date = P_EXPIRY_DATE,
-- account_no = decode(amount_type,'FXOBUY',P_BUY_ACCOUNT_NO,P_SELL_ACCOUNT_NO),
-- for bug 965188 part 2 account number should not be updated at all
limit_code = decode(P_STATUS_CODE,'CURRENT',
decode(amount_type,'FXOBUY',
decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL)),NULL),
limit_party = decode(P_STATUS_CODE,'CURRENT',
decode(amount_type,'FXOBUY',
decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL)),NULL),
transaction_date = P_DEAL_DATE,
currency = decode(amount_type,'FXOBUY',P_CURRENCY_BUY,P_CURRENCY_SELL),
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and date_type = 'EXPIRY'
and deal_type = P_DEAL_TYPE;
if P_STATUS_CODE='CURRENT' and nvl(P_KNOCK_TYPE,'@#@')='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'then
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,cparty_code,settle,client_code,
limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_CPARTY_CODE,'N',P_CLIENT_CODE,
decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,cparty_code,settle,
client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
P_DEALER_CODE,l_combin,P_QUICK_INPUT);
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_DEAL_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY_BUY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and date_type = 'DEALT'
and deal_type = P_DEAL_TYPE;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_PREMIUM_AMOUNT,
hce_amount = P_PREMIUM_HCE_AMOUNT,
amount_date = P_PREMIUM_DATE,
cashflow_amount = DECODE(P_PREMIUM_ACTION,'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
action_code = P_PREMIUM_ACTION
where deal_number = P_DEAL_NO
and date_type = 'PREMIUM'
and deal_type = P_DEAL_TYPE;
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
cparty_code,settle,client_code,portfolio_code,dealer_code)
values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,'SETTLE',
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE);
or (P_KNOCK_TYPE='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'))
and P_INSERT_FOR_CASHFLOW = 'Y' then
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SELL_AMOUNT,
hce_amount = P_SELL_HCE_AMOUNT,
amount_date = decode(date_type,'VALUE',P_VALUE_DATE,P_EXPIRY_DATE),
exposure_ref_date = decode(date_type,'VALUE',P_START_DATE,exposure_ref_date),
cashflow_amount = decode(status_code,'CURRENT',
decode(DATE_TYPE,'EXPIRY',0,
decode(P_INSERT_FOR_CASHFLOW,'Y',(-1) * P_SELL_AMOUNT,0))
,0),
account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
limit_code = decode(status_code,'EXERCISED',NULL,
decode(DATE_TYPE,'EXPIRY',
decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),NULL)),
limit_party = decode(DATE_TYPE,'EXPIRY',decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),NULL),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY_SELL,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and date_type = 'VALUE'
and deal_type = P_DEAL_TYPE;
insert into XTR_DEAL_DATE_AMOUNTS
(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,
deal_subtype,product_type,status_code,cparty_code,settle,
client_code,portfolio_code,dealer_code,currency_combination,
exposure_ref_date)
values(P_DEAL_TYPE,'FXOBUY','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
P_VALUE_DATE,P_TRANSACTION_RATE,P_BUY_AMOUNT,
P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
P_DEALER_CODE,l_combin,P_START_DATE);
insert into XTR_DEAL_DATE_AMOUNTS
(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,
deal_subtype,product_type,status_code,cparty_code,settle,
client_code,portfolio_code,dealer_code,currency_combination,
exposure_ref_date)
values(P_DEAL_TYPE,'FXOSELL','VALUE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
P_VALUE_DATE,P_TRANSACTION_RATE,(-1) * P_SELL_AMOUNT,
P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
P_DEALER_CODE,l_combin,P_START_DATE);
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = P_DEAL_TYPE
and date_type = 'VALUE';
if (P_KNOCK_TYPE='I' and P_KNOCK_INSERT_TYPE IS NULL) then
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
cparty_code,settle,client_code,portfolio_code,dealer_code)
values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
P_DEAL_NO,1,sysdate,P_CURRENCY,
P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
P_EXPIRY_DATE,0,decode(P_PREMIUM_ACTION,
'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE);
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = P_DEAL_TYPE
and date_type = 'VALUE';
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT_DATE = P_EXPIRY_DATE
where DEAL_NUMBER = P_DEAL_NO
and DEAL_TYPE = P_DEAL_TYPE
and DATE_TYPE = 'EXPIRY';
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = P_DEAL_TYPE
and date_type in ('VALUE','EXPIRY');
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = P_DEAL_TYPE
and date_type = 'VALUE';
update XTR_DEAL_DATE_AMOUNTS
set DATE_TYPE = 'SETTLE',
--BUG 8561305 starts
AMOUNT_DATE = p_settle_date,
--AMOUNT_DATE = trunc(sysdate),
--Bug 8561305 ends
TRANSACTION_DATE = trunc(sysdate)
where DEAL_NUMBER = P_DEAL_NO
and DEAL_TYPE = P_DEAL_TYPE
and DATE_TYPE = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT_DATE = p_settle_date
where DEAL_NUMBER = P_DEAL_NO
and DEAL_TYPE = P_DEAL_TYPE
and DATE_TYPE = 'SETTLE';
UPDATE XTR_DEAL_DATE_AMOUNTS
SET AMOUNT = P_BUY_AMOUNT,
HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
STATUS_CODE = P_STATUS_CODE,
AMOUNT_DATE = P_START_DATE,
CASHFLOW_AMOUNT = 0,
DATE_TYPE = 'ROLLPRE',
ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no ),
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
LIMIT_CODE = NULL,
limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
currency = P_CURRENCY_BUY,
currency_combination = l_combin,
product_type = P_PRODUCT_TYPE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE
WHERE DEAL_NUMBER = P_DEAL_NO
AND DEAL_TYPE = P_DEAL_TYPE
AND DATE_TYPE = 'VALUE'
AND AMOUNT_TYPE = 'BUY'
AND TRANSACTION_NUMBER = 1;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SELL_AMOUNT,
hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
amount_date = P_START_DATE,
cashflow_amount = 0,
DATE_TYPE = 'ROLLPRE',
STATUS_CODE = P_STATUS_CODE,
account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
LIMIT_CODE = NULL,
limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
currency = P_CURRENCY_SELL,
currency_combination = l_combin,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE
where deal_number = P_DEAL_NO
and amount_type = 'SELL'
and deal_type = P_DEAL_TYPE
AND date_type = 'VALUE'
and transaction_number = 1;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SELL_AMOUNT,
hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
status_code = P_STATUS_CODE,
amount_date = P_DEAL_DATE,
currency = P_CURRENCY_SELL,
account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no ),
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE,
currency_combination = l_combin
where deal_number = P_DEAL_NO
and amount_type = 'N/A'
and deal_type = P_DEAL_TYPE;
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT = P_BUY_AMOUNT,
HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
AMOUNT_DATE = P_VALUE_DATE,
STATUS_CODE = P_STATUS_CODE,
CASHFLOW_AMOUNT = P_BUY_AMOUNT,
ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no),
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
LIMIT_CODE = decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
currency = P_CURRENCY_BUY,
currency_combination = l_combin,
product_type = P_PRODUCT_TYPE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE
where deal_number = P_DEAL_NO
and amount_type = 'BUY'
and deal_type = P_DEAL_TYPE
AND date_type = 'VALUE'
and transaction_number = 1;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SELL_AMOUNT,
hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
amount_date = P_VALUE_DATE,
cashflow_amount = (-1) * P_SELL_AMOUNT,
STATUS_CODE = P_STATUS_CODE,
account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
LIMIT_CODE = decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
currency = P_CURRENCY_SELL,
currency_combination = l_combin,
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE
where deal_number = P_DEAL_NO
and amount_type = 'SELL'
and deal_type = P_DEAL_TYPE
AND date_type = 'VALUE'
and transaction_number = 1;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SELL_AMOUNT,
hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
status_code = P_STATUS_CODE,
amount_date = P_DEAL_DATE,
currency = P_CURRENCY_SELL,
account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
product_type = P_PRODUCT_TYPE,
portfolio_code = P_PORTFOLIO_CODE,
company_code = P_COMPANY_CODE,
cparty_code = P_CPARTY_CODE,
client_code = P_CLIENT_CODE,
dealer_code = P_DEALER_CODE,
transaction_rate = P_TRANSACTION_RATE,
transaction_date = P_DEAL_DATE,
currency_combination = l_combin
where deal_number = P_DEAL_NO
and amount_type = 'N/A'
and deal_type = P_DEAL_TYPE;
update XTR_DEAL_DATE_AMOUNTS
set ACCOUNT_NO = nvl(v_MATURITY_ACCOUNT_NO, account_no),
STATUS_CODE = P_STATUS_CODE,
CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
COMMENCE_DATE = P_START_DATE,
CPARTY_CODE = P_CPARTY_CODE,
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
DEAL_SUBTYPE = P_DEAL_SUBTYPE,
DEALER_CODE = P_DEALER_CODE,
CLIENT_CODE = P_CLIENT_CODE,
TRANSACTION_RATE = P_INTEREST_RATE,
TRANSACTION_DATE = P_DEAL_DATE,
CURRENCY = P_CURRENCY,
LIMIT_CODE = decode(AMOUNT_TYPE,'COMENCE',
decode(P_RISKPARTY_LIMIT_CODE,NULL,'NILL',NULL),
'BAL_FV',
decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_LIMIT_CODE),
LIMIT_CODE),
LIMIT_PARTY = decode(AMOUNT_TYPE,'COMENCE',
NULL,
'BAL_FV',
decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_CODE),
LIMIT_PARTY),
AMOUNT_DATE = decode(AMOUNT_TYPE,'COMENCE',P_START_DATE,
'BAL_FV',P_MATURITY_DATE,
'INTL_FV',decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,
'COVER',P_START_DATE,P_MATURITY_DATE),
'INT',P_START_DATE,
AMOUNT_DATE),
AMOUNT = decode(AMOUNT_TYPE,'COMENCE',nvl(P_START_AMOUNT,0),
'BAL_FV',nvl(P_MATURITY_BALANCE_AMOUNT,0),
'INTL_FV', nvl(P_MATURITY_AMOUNT,0),
'INT', nvl(P_INTEREST_AMOUNT,0),
AMOUNT),
HCE_AMOUNT = decode(AMOUNT_TYPE,'COMENCE', nvl(P_START_HCE_AMOUNT,0),
'BAL_FV', nvl(P_MATURITY_BALANCE_HCE_AMOUNT,0),
'INTL_FV', nvl(P_MATURITY_HCE_AMOUNT,0),
'INT', nvl(P_INTEREST_HCE_AMOUNT,0),
HCE_AMOUNT),
CASHFLOW_AMOUNT = decode(AMOUNT_TYPE,'COMENCE',
decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*nvl(P_START_AMOUNT,0),0),
-- 'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
CASHFLOW_AMOUNT) -- bug 3776211
where DEAL_NUMBER = P_DEAL_NO
and DEAL_TYPE = P_DEAL_TYPE;
insert into XTR_DEAL_DATE_AMOUNTS
(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,
cparty_account_no,status_code,portfolio_code,dealer_code,
client_code,deal_subtype,cparty_code,settle,product_type,
limit_code,limit_party,commence_date,quick_input)
values('NI','BAL_FV','MATURE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
P_MATURITY_BALANCE_AMOUNT,
P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
-- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
P_PRODUCT_TYPE,decode(P_DEAL_SUBTYPE,'SELL',NULL,nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL')),
decode(P_DEAL_SUBTYPE,'SELL',NULL,P_RISKPARTY_CODE),P_START_DATE,P_QUICK_INPUT);
update XTR_DEAL_DATE_AMOUNTS
set AMOUNT_DATE = P_START_DATE,
AMOUNT = abs(nvl(P_NI_PROFIT_LOSS,0)),
HCE_AMOUNT = abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
ACTION_CODE = decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'),
CASHFLOW_AMOUNT = 0
where DEAL_NUMBER = P_DEAL_NO
and AMOUNT_TYPE = 'REAL'
and DATE_TYPE ='REVAL';
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,status_code,dealer_code,
deal_subtype,product_type,settle,cparty_code,client_code,
portfolio_code,action_code)
values('NI','REAL','REVAL',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
abs(nvl(P_NI_PROFIT_LOSS,0)),abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
P_START_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,
P_STATUS_CODE,P_DEALER_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,'N',P_CPARTY_CODE,P_CLIENT_CODE,
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'));
update XTR_DEAL_DATE_AMOUNTS
set amount = P_START_AMOUNT,
hce_amount = P_START_HCE_AMOUNT,
cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_START_AMOUNT,
transaction_rate = P_INTEREST_RATE,
transaction_date = P_DEAL_DATE,
account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no ),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no)
where deal_number = P_DEAL_NO
and amount_date >= L_SYSDATE
and amount_type = 'COMENCE'
and date_type = 'COMENCE'
and deal_type = P_DEAL_TYPE;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_MATURITY_AMOUNT,
hce_amount = P_MATURITY_HCE_AMOUNT,
cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_MATURITY_AMOUNT,
transaction_rate = P_INTEREST_RATE,
transaction_date = P_DEAL_DATE,
account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
limit_code = nvl(P_LIMIT_CODE,'NILL')
where deal_number = P_DEAL_NO
and amount_type = 'INTL_FV'
and date_type = 'MATURE'
and deal_type = P_DEAL_TYPE
and amount_date >= L_SYSDATE;
update XTR_DEAL_DATE_AMOUNTS
set amount = P_INT_VALUE,
hce_amount = round(P_INT_VALUE/hce_rate,round_fac),
transaction_date = P_DEAL_DATE,
transaction_rate = P_INTEREST_RATE
where deal_number = P_DEAL_NO
and amount_type = 'INT'
and date_type = 'COMENCE'
and deal_type = P_DEAL_TYPE
and amount_date >= L_SYSDATE;
update XTR_DEAL_DATE_AMOUNTS
set amount = abs(P_PREM_VALUE),
hce_amount = abs(round(P_PREM_VALUE/hce_rate,round_fac)),
transaction_date = P_DEAL_DATE,
transaction_rate = P_INTEREST_RATE
where deal_number = P_DEAL_NO
and amount_type IN('DISC','PREMIUM')
and date_type = 'COMENCE'
and deal_type = P_DEAL_TYPE
and amount_date >= L_SYSDATE;
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
amount_date = P_EXPIRY_DATE,
transaction_rate = P_INTEREST_RATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_PREMIUM_AMOUNT,0),
hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
company_code = P_COMPANY_CODE,
account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
action_code = P_PREMIUM_ACTION,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'PREMIUM';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_DEAL_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'DEALT';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_MATURITY_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'MATURE';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_START_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'COMENCE';
update XTR_DEAL_DATE_AMOUNTS
set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
amount_date = P_SETTLE_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
limit_code = nvl(P_LIMIT_CODE,'NILL'),
limit_party = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'LIMIT';
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_SETTLE_AMOUNT,0),
hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
amount_date = P_SETTLE_DATE,
cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
transaction_rate = P_SETTLE_RATE,
account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
action_code = P_SETTLE_ACTION,
--Bug 3060946 Removed assignment to cparty_account_no
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'IRO'
and date_type = 'SETTLE';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
amount_date = P_EXPIRY_DATE,
transaction_rate = P_INTEREST_RATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_PREMIUM_AMOUNT,0),
hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
company_code = P_COMPANY_CODE,
account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
action_code = P_PREMIUM_ACTION,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'PREMIUM';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_DEAL_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'DEALT';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_MATURITY_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'MATURE';
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_START_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'COMENCE';
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
amount_date = P_SETTLE_DATE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
limit_code = nvl(P_LIMIT_CODE,'NILL'),
limit_party = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'LIMIT';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_SETTLE_AMOUNT,0),
hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
amount_date = P_SETTLE_DATE,
cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
transaction_rate = P_SETTLE_RATE,
account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
action_code = P_SETTLE_ACTION,
cparty_account_no = nvl(v_settle_ac,nvl(v_cparty_account_no, cparty_account_no)), --Bug 2855642
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'BDO'
and date_type = 'SETTLE';
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
dealer_code,client_code,cparty_code,settle,portfolio_code)
values('BDO','SETTLE','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
nvl(P_SETTLE_DATE,P_START_DATE),P_SETTLE_RATE,
decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --Bug 2855642
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,QUICK_INPUT)
values('BDO','N/A','SETTLE',
P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
update XTR_DEAL_DATE_AMOUNTS
set amount = P_START_AMOUNT,
hce_amount = P_START_HCE_AMOUNT,
transaction_rate = nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),
amount_date = P_EXPIRY_DATE,
limit_code = nvl(P_LIMIT_CODE,'NILL'),
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY_BUY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
limit_party = P_CPARTY_CODE,
contract_code = P_BOND_ISSUE
where deal_number = P_DEAL_NO
and deal_type = 'FUT'
and amount_type = 'FACEVAL';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0),
hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
cashflow_amount = -(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
amount_date = P_PREMIUM_DATE,
transaction_date = P_DEAL_DATE,
currency = P_PREMIUM_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
contract_code = P_BOND_ISSUE
where deal_number = P_DEAL_NO
and deal_type = 'FUT'
and amount_type = 'PREMIUM';
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
dealer_code,client_code,cparty_code,settle,
portfolio_code,contract_code)
values('FUT','PREMIUM','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
'CURRENT',P_DEALER_CODE,P_CLIENT_CODE,
P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
update XTR_DEAL_DATE_AMOUNTS
set amount_date = P_DEAL_DATE,
transaction_date = P_DEAL_DATE,
currency = P_PREMIUM_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'FUT'
and amount_type = 'DEALT';
update XTR_DEAL_DATE_AMOUNTS
set amount = P_SETTLE_AMOUNT,
hce_amount = P_SETTLE_HCE_AMOUNT,
amount_date = P_SETTLE_DATE,
cashflow_amount =
decode(P_SETTLE_ACTION,'PAY',(-1),1) * P_SETTLE_AMOUNT,
transaction_rate = nvl(P_BASE_RATE,P_SETTLE_RATE),
account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
cparty_account_no = decode(P_SETTLE_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),NULL),
action_code = P_SETTLE_ACTION,
currency = P_PREMIUM_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
contract_code = P_BOND_ISSUE
where deal_number = P_DEAL_NO
and deal_type = 'FUT'
and amount_type = 'SETTLE';
insert into XTR_DEAL_DATE_AMOUNTS
(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,status_code,
dealer_code,client_code,cparty_code,settle,
portfolio_code,contract_code)
values('FUT','SETTLE','SETTLE',
P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
P_SETTLE_DATE,
nvl(P_BASE_RATE,P_SETTLE_RATE),decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
decode(P_SETTLE_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,NULL),
P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
update XTR_DEAL_DATE_AMOUNTS
set limit_code = NULL
where deal_number = P_DEAL_NO
and limit_code is NOT NULL;
update XTR_DEAL_DATE_AMOUNTS
set amount_date = decode(date_type,'DEALT',P_DEAL_DATE,
'COMENCE',P_START_DATE,
'MATURE',P_MATURITY_DATE,amount_date),
amount = decode(amount_type,'N/A',0,
'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
hce_amount = decode(amount_type,'N/A',0,
'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
transaction_date = P_DEAL_DATE,
transaction_rate = P_INTEREST_RATE,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type not in ('EXPIRY','PREMIUM','SETTLE');
update XTR_DEAL_DATE_AMOUNTS
set amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
amount = nvl(P_PREMIUM_AMOUNT,0),
hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1)*nvl(P_PREMIUM_AMOUNT,0),
currency = P_CURRENCY_BUY,
transaction_date = P_DEAL_DATE,
transaction_rate = P_INTEREST_RATE,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
status_code = P_STATUS_CODE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
action_code = P_PREMIUM_ACTION,
account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
cparty_account_no= decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type = 'PREMIUM';
update XTR_DEAL_DATE_AMOUNTS
set amount = P_FACE_VALUE_AMOUNT,
hce_amount = P_FACE_VALUE_HCE_AMOUNT,
amount_date = P_EXPIRY_DATE,
transaction_rate = P_INTEREST_RATE,
status_code = P_STATUS_CODE,
transaction_date = P_DEAL_DATE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type = 'EXPIRY';
delete from XTR_DEAL_DATE_AMOUNTS
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type = 'EXPIRY';
update XTR_DEAL_DATE_AMOUNTS
set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
amount_date = P_SETTLE_DATE,
status_code = P_STATUS_CODE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
limit_code = nvl(P_LIMIT_CODE,'NILL'),
limit_party = P_CPARTY_CODE
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type = 'LIMIT';
update XTR_DEAL_DATE_AMOUNTS
set amount = nvl(P_SETTLE_AMOUNT,0),
hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
amount_date = P_SETTLE_DATE,
cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
transaction_rate = P_SETTLE_RATE,
account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
--Bug 3060946 Removed assignement to cparty_account_no
status_code = P_STATUS_CODE,
currency = P_CURRENCY,
company_code = P_COMPANY_CODE,
deal_subtype = P_DEAL_SUBTYPE,
product_type = P_PRODUCT_TYPE,
dealer_code = P_DEALER_CODE,
client_code = P_CLIENT_CODE,
cparty_code = P_CPARTY_CODE,
portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
where deal_number = P_DEAL_NO
and deal_type = 'SWPTN'
and date_type = 'SETTLE';
insert into XTR_DEAL_DATE_AMOUNTS
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,deal_subtype,product_type,
status_code,dealer_code,client_code,cparty_code,settle,
portfolio_code,QUICK_INPUT)
values('SWPTN','N/A','SETTLE',
P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
P_CLIENT_CODE,P_CPARTY_CODE,'N',
nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
update XTR_DEAL_DATE_AMOUNTS
set limit_code = NULL,
status_code = P_STATUS_CODE
where deal_number = P_DEAL_NO
and limit_code is not null
and date_type <> 'LIMIT';