[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select s.HCE_RATE,s.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = P_CURRENCY;
select rowid, as_at_date
from XTR_POSITION_HISTORY
where AS_AT_DATE >= V_START_DATE
and AS_AT_DATE < V_END_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER
order by AS_AT_DATE
for update of BASE_REF_AMOUNT NOWAIT;
select rowid, as_at_date
from XTR_POSITION_HISTORY
where AS_AT_DATE >= V_START_DATE
and AS_AT_DATE < V_END_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
order by AS_AT_DATE
for update of BASE_REF_AMOUNT NOWAIT;
select rowid, as_at_date
from XTR_POSITION_HISTORY
where AS_AT_DATE >= V_START_DATE
and AS_AT_DATE < V_END_DATE
and DEAL_TYPE = V_DEAL_TYPE
and COMPANY_CODE = V_COMPANY_CODE
and ACCOUNT_NO = V_ACCOUNT_NO
order by AS_AT_DATE
for update of BASE_REF_AMOUNT NOWAIT;
select rowid, as_at_date
from XTR_POSITION_HISTORY
where AS_AT_DATE >= V_START_DATE
and AS_AT_DATE < V_END_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAl_NUMBER
order by AS_AT_DATE
for update of BASE_REF_AMOUNT NOWAIT;
is one to one. Therefore it is much more effecient to update XTR_COST_OF_FUNDS at the same time
we update XTR_POSITION_HISTORY. Block update. However to do this it was necessicary to duplicate
some of the logic from maintain_cof procedure. This procedure is for IG deals and only IG deals.
In order to use this hack, the developer must adhere to the following requirements and must not
deviate or modify them. Be careful when making modifications to any of the affected code areas so
as not to violate these assumptions.
First, INSERT and UPDATE for IG deals are executed ONLY by the maintain position history procedure.
The maintain COF procedure will do no processing for IG deals for INSERT and UPDATE but will continue
to be responsible for IG DELETEs. Call MAINTAIN_COF_IG for IG deals and IG deals only. Position
History is now responsible for IG data in COF so it must not forget to call this function for INSERT
and UPDATE. For anyone writing SQL upgrade scripts, if data is inserted or updated in XTR_POSITION_HISTORY
for IG deals then make sure the snapshot of COF is updated appropriately.
*/
procedure maintain_cof_ig is
l_fac number;
select hce_rate,rounding_factor
from XTR_MASTER_CURRENCIES_V
where currency=l_in_currency;
select ROWID,AS_AT_DATE
from XTR_COST_OF_FUNDS
where AS_AT_DATE >= V_START_DATE
and AS_AT_DATE < V_END_DATE
and DEAL_TYPE = V_DEAL_TYPE
--and DEAL_SUBTYPE = V_DEAL_SUBTYPE /* IG DEAL HAS ONE AND ONLY ONE ENTRY. UPDATE RESPECTIVELY */
and COMPANY_CODE = V_COMPANY_CODE
and CURRENCY = V_CURRENCY
--and nvl(CURRENCY_COMBINATION,'%')=nvl(V_CURRENCY_COMBINATION,'%') /* IG, NOT FX */
--and nvl(PRODUCT_TYPE,'%') = nvl(V_PRODUCT_TYPE,'%') /* SAME AS DEAL_SUBTYPE */
--and nvl(PORTFOLIO_CODE,'%') = nvl(V_PORTFOLIO_CODE,'%') /* SAME AS DEAL_SUBTYPE */
and nvl(PARTY_CODE,'%') = nvl(V_CPARTY_CODE,'%')
for update of GROSS_PRINCIPAL NOWAIT;
/* all dates not in the table are saved in T_AS_AT_DATE_INS to be inserted */
FOR I IN 1..(L_END_DATE-L_REF_DATE) LOOP
IF N_COF_AS_AT_DATE_CP <= N_COF_COUNTER AND T_COF_AS_AT_DATE(N_COF_AS_AT_DATE_CP)=L_REF_DATE+I-1 THEN
N_COF_AS_AT_DATE_CP:=N_COF_AS_AT_DATE_CP+1;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = L_BASE_REF_AMOUNT,
HCE_GROSS_PRINCIPAL = L_HCE_BASE_REF_AMOUNT,
WEIGHTED_AVG_PRINCIPAL = L_WEIGHTED_AVG_PRIN,
AVG_INTEREST_RATE = L_CONVERT_RATE,
BASE_WEIGHTED_AVG_PRINCIPAL = L_BASE_WEIGHTED_AVG_PRIN,
AVG_BASE_RATE = P_BASE_RATE,
INTEREST = L_DAILY_INT,
HCE_INTEREST = L_HCE_INT,
DEAL_SUBTYPE = L_DEAL_SUBTYPE,
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE
where rowid=T_COF_ROWID(i);
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
currency,currency_combination,contra_ccy,
account_no,created_on,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,hce_interest,
base_weighted_avg_principal,avg_base_rate, gross_base_amount,
gross_contra_trans_amount, gross_contra_spot_amount)
values(
T_COF_AS_AT_DATE_INS(i),
P_COMPANY_CODE,
P_DEAL_TYPE,
L_DEAL_SUBTYPE,
P_CPARTY_CODE,
P_PORTFOLIO_CODE,
P_PRODUCT_TYPE,
P_CURRENCY,
NULL,
P_CONTRA_CCY,
P_ACCOUNT_NO,
sysdate,
L_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
L_WEIGHTED_AVG_PRIN,
L_CONVERT_RATE, --avg_int_rate
L_DAILY_INT, -- interest
L_HCE_INT, -- hce_interest
L_BASE_WEIGHTED_AVG_PRIN,
P_BASE_RATE, -- avg_base_rate
NULL, -- gross_base_amount
NULL, --gross_contra_trans
NULL --gross_contra_spot
);
/* INSERT */
/**************/
if P_ACTION='INSERT' and P_STATUS_CODE='CURRENT' then
L_DEAL_SUBTYPE := P_DEAL_SUBTYPE;
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
T_AS_AT_DATE(i),
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
L_DEAL_SUBTYPE,
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
L_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
L_CONVERT_RATE,
P_BASE_RATE,
L_DAILY_INT,
L_HCE_INT);
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
L_AS_AT_DATE,
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
decode(P_DEAL_TYPE,
'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
P_DEAL_SUBTYPE), -- bug 2345708
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
P_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
P_BASE_RATE,
decode(P_DEAL_TYPE, 'FX', 0, L_DAILY_INT),
decode(P_DEAL_TYPE, 'FX', 0, L_HCE_INT));
/* DELETE */
/**************/
elsif P_ACTION = 'DELETE' then
if P_DEAL_TYPE='CA' then
delete from XTR_POSITION_HISTORY
where DEAL_TYPE='CA'
and AS_AT_DATE >= P_START_DATE
and ACCOUNT_NO = P_ACCOUNT_NO
and COMPANY_CODE = P_COMPANY_CODE;
delete from XTR_POSITION_HISTORY
where DEAL_TYPE='IG'
and AS_AT_DATE >= P_START_DATE
and DEAL_NUMBER = P_DEAL_NUMBER;
delete from XTR_POSITION_HISTORY
where AS_AT_DATE >= L_END_DATE
and DEAL_TYPE = P_DEAL_TYPE
and DEAL_NUMBER = P_DEAL_NUMBER
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
update XTR_POSITION_HISTORY
set COMPANY_CODE = P_COMPANY_CODE,
CPARTY_CODE = P_CPARTY_CODE,
DEAL_SUBTYPE = p_deal_subtype,
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
CURRENCY = P_CURRENCY,
CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
ACCOUNT_NO = P_ACCOUNT_NO,
BASE_REF_AMOUNT = p_base_ref_amount,
HCE_BASE_REF_AMOUNT = l_hce_base_ref_amount,
BASE_RATE = p_base_rate,
TRANSACTION_RATE = l_convert_rate,
INTEREST = l_daily_int,
HCE_INTEREST = l_hce_int
where rowid=T_ROWID(I);
delete from XTR_POSITION_HISTORY
where DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where AS_AT_DATE >= L_END_DATE
and DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where AS_AT_DATE >= L_END_DATE
and DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where DEAL_TYPE = P_DEAL_TYPE
and DEAL_NUMBER = P_DEAL_NUMBER;
delete from XTR_POSITION_HISTORY
where AS_AT_DATE >= L_END_DATE
and DEAL_TYPE = P_DEAL_TYPE
and DEAL_NUMBER = P_DEAL_NUMBER
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
delete from XTR_POSITION_HISTORY
where DEAL_NUMBER = P_DEAL_NUMBER
and DEAL_TYPE = P_DEAL_TYPE
and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
/* UPDATE */
/**************/
elsif P_ACTION='UPDATE' then
if P_DEAL_TYPE NOT in('TMM','RTMM','CA','IG') then
open CHK_LOCK_ROWS_ONC(L_REF_DATE,L_END_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,P_COMPANY_CODE);
/* all dates not in the table are saved in T_AS_AT_DATE_INS to be inserted */
FOR I IN 1..(L_END_DATE-L_REF_DATE) LOOP
IF N_AS_AT_DATE_CP <= N_COUNTER AND T_AS_AT_DATE(N_AS_AT_DATE_CP)=L_REF_DATE+I-1 THEN
N_AS_AT_DATE_CP:=N_AS_AT_DATE_CP+1;
update XTR_POSITION_HISTORY
set -- COMPANY_CODE removed from update because it cannot change and it was causing an index to be recalculated
CPARTY_CODE = P_CPARTY_CODE,
DEAL_SUBTYPE = L_DEAL_SUBTYPE,
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
CURRENCY = P_CURRENCY,
CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
ACCOUNT_NO = P_ACCOUNT_NO,
BASE_REF_AMOUNT = L_BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT = L_HCE_BASE_REF_AMOUNT,
BASE_RATE = P_BASE_RATE,
TRANSACTION_RATE = L_CONVERT_RATE,
INTEREST = L_DAILY_INT,
HCE_INTEREST = L_HCE_INT
where rowid=T_ROWID(I);
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
T_AS_AT_DATE_INS(I),
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
L_DEAL_SUBTYPE,
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
L_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
L_CONVERT_RATE,
P_BASE_RATE,
L_DAILY_INT,
L_HCE_INT);
update XTR_POSITION_HISTORY
set COMPANY_CODE = P_COMPANY_CODE,
CPARTY_CODE = P_CPARTY_CODE,
DEAL_SUBTYPE = decode(DEAL_TYPE,
'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
P_DEAL_SUBTYPE),
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
CURRENCY = P_CURRENCY,
CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
ACCOUNT_NO = P_ACCOUNT_NO,
BASE_REF_AMOUNT = decode(DEAL_TYPE,'CA',abs(P_BASE_REF_AMOUNT),
'IG',abs(P_BASE_REF_AMOUNT),
P_BASE_REF_AMOUNT),
HCE_BASE_REF_AMOUNT =decode(DEAL_TYPE,'CA',abs(L_HCE_BASE_REF_AMOUNT),
'IG',abs(L_HCE_BASE_REF_AMOUNT),
L_HCE_BASE_REF_AMOUNT),
BASE_RATE = P_BASE_RATE,
TRANSACTION_RATE = decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
INTEREST = decode(DEAL_TYPE, 'CA', abs(L_DAILY_INT),
'IG', abs(L_DAILY_INT),
'FX', 0,
L_DAILY_INT),
HCE_INTEREST = decode(DEAL_TYPE, 'CA', abs(L_HCE_INT),
'IG', abs(L_HCE_INT),
'FX', 0,
L_HCE_INT)
where rowid=l_rowid;
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
L_AS_AT_DATE,
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
decode(P_DEAL_TYPE,
'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
P_DEAL_SUBTYPE), --bug 2345708
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
L_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
P_BASE_RATE,
decode(P_DEAL_TYPE, 'FX', 0, abs(L_DAILY_INT)),
decode(P_DEAL_TYPE, 'FX', 0, abs(L_HCE_INT)));
select s.HCE_RATE,s.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = P_CURRENCY;
select max(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER;
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER
for update of BASE_REF_AMOUNT NOWAIT;
select max(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER;
select cross_ref_start_date, avg_rate_last_processed
from xtr_bond_alloc_details
where deal_no = V_DEAL_NUMBER
and CROSS_REF_START_DATE <= V_AS_AT_DATE
and avg_rate_last_processed is null
order by cross_ref_start_date; -- 4470022
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
for update of BASE_REF_AMOUNT NOWAIT;
select max(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER;
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
for update of BASE_REF_AMOUNT NOWAIT;
select max(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAl_TYPE
and COMPANY_CODE = V_COMPANY_CODE
and ACCOUNT_NO = V_ACCOUNT_NO;
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and COMPANY_CODE = V_COMPANY_CODE
and ACCOUNT_NO = V_ACCOUNT_NO
for update of BASE_REF_AMOUNT NOWAIT;
select MAX(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAl_NUMBER;
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAl_NUMBER
for update of BASE_REF_AMOUNT NOWAIT;
select b.calc_type, d.coupon_rate, d.maturity_amount, d.start_amount
from xtr_deals D, xtr_bond_issues B
where d.bond_issue = b.bond_issue_code
and d.deal_no = P_DEAL_NUMBER;
Select sum(face_value), max(cross_ref_start_date)
From XTR_BOND_ALLOC_DETAILS
Where deal_no = P_DEAL_NUMBER
and cross_ref_start_date <= P_AS_AT_DATE;
Select maturity_date
from xtr_deals
where deal_no = p_deal_number
and deal_type = 'BOND'
and maturity_date <= P_AS_AT_DATE;
Delete from XTR_POSITION_HISTORY
Where deal_number = P_DEAL_NUMBER
And as_at_date >= l_last_resale_date;
Select sum(face_value)
into l_face_value
From XTR_BOND_ALLOC_DETAILS
Where deal_no = P_DEAL_NUMBER
and cross_ref_start_date <= L_AS_AT_DATE;
SELECT SUM(interest), SUM(original_amount)
INTO l_interest,l_original_amount
FROM xtr_rollover_transactions
WHERE deal_number = P_DEAL_NUMBER;
/* Insert or Update postion history table */
/*========================================*/
if L_ROWID is not null then
update XTR_POSITION_HISTORY
set COMPANY_CODE = P_COMPANY_CODE,
CPARTY_CODE = P_CPARTY_CODE,
/* DEAL_SUBTYPE = decode(DEAL_TYPE,
'CA',decode(sign(nvl(BASE_REF_AMOUNT,0)
+nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
'IG',decode(sign(nvl(BASE_REF_AMOUNT,0)
+nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
P_DEAL_SUBTYPE), */
DEAL_SUBTYPE = decode(DEAL_TYPE,
'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
P_DEAL_SUBTYPE), -- bug 2345708
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
CURRENCY = P_CURRENCY,
CONTRA_CCY = P_CONTRA_CCY,
CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
ACCOUNT_NO = P_ACCOUNT_NO,
BASE_REF_AMOUNT = decode(DEAL_TYPE,'CA',abs(P_BASE_REF_AMOUNT),
'IG',abs(P_BASE_REF_AMOUNT),
'BOND', abs(nvl(L_BASE_REF_AMOUNT,P_BASE_REF_AMOUNT)),
P_BASE_REF_AMOUNT),
HCE_BASE_REF_AMOUNT =decode(DEAL_TYPE,'CA',abs(L_HCE_BASE_REF_AMOUNT),
'IG',abs(L_HCE_BASE_REF_AMOUNT),
L_HCE_BASE_REF_AMOUNT), -- bug2345708
BASE_RATE = P_BASE_RATE,
TRANSACTION_RATE = decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
INTEREST = decode(DEAL_TYPE, 'CA', abs(L_DAILY_INT),
'IG', abs(L_DAILY_INT),
'FX', 0,
L_DAILY_INT),
HCE_INTEREST = decode(DEAL_TYPE, 'CA', abs(L_HCE_INT),
'IG', abs(L_HCE_INT),
'FX', 0,
L_HCE_INT)
where rowid=l_rowid;
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
L_AS_AT_DATE,
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
abs(nvl(L_BASE_REF_AMOUNT, P_BASE_REF_AMOUNT)),
abs(L_HCE_BASE_REF_AMOUNT),
decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
P_BASE_RATE,
decode(P_DEAL_TYPE, 'FX', 0, abs(L_DAILY_INT)),
decode(P_DEAL_TYPE, 'FX', 0, abs(L_HCE_INT)));
Update XTR_BOND_ALLOC_DETAILS
set avg_rate_last_processed = greatest(nvl(avg_rate_last_processed, L_AS_AT_DATE),
L_AS_AT_DATE)
where deal_no = P_DEAL_NUMBER;
select ROWID,GROSS_PRINCIPAL
from XTR_COST_OF_FUNDS
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_SUBTYPE = V_DEAL_SUBTYPE
and COMPANY_CODE = V_COMPANY_CODE
and CURRENCY = V_CURRENCY
and nvl(CURRENCY_COMBINATION,'%')=nvl(V_CURRENCY_COMBINATION,'%')
and nvl(PRODUCT_TYPE,'%') = nvl(V_PRODUCT_TYPE,'%')
and nvl(PORTFOLIO_CODE,'%') = nvl(V_PORTFOLIO_CODE,'%')
and nvl(PARTY_CODE,'%') = nvl(V_CPARTY_CODE,'%')
for update of GROSS_PRINCIPAL NOWAIT;
select hce_rate,rounding_factor
from XTR_MASTER_CURRENCIES_V
where currency=l_currency;
/* INSERT */
/**************/
if P_ACTION='INSERT' then
if (NEW_DEAL_TYPE<>'IG') then /* IG handled by maintain_position_history, see comments for maintain_cof_ig */
l_currency :=NEW_CURRENCY;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),
GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0),
GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0),
AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
INTEREST = 0,
HCE_INTEREST = 0
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
AVG_INTEREST_RATE = decode((nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)),0,null,
abs((nvl(INTEREST,0) - nvl(NEW_INTEREST,0))/
(nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)))) * 36500,
-- bug 2345708
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),0,null,
decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
0,null,
round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac))/
(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0)),l_fac)))),
INTEREST = nvl(INTEREST,0) + nvl(NEW_INTEREST,0),
HCE_INTEREST = nvl(HCE_INTEREST,0) + nvl(NEW_HCE_INTEREST,0)
where rowid=l_rowid;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
currency,currency_combination,contra_ccy,
account_no,created_on,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,hce_interest,
base_weighted_avg_principal,avg_base_rate,gross_base_amount,
gross_contra_trans_amount, gross_contra_spot_amount)
values(
NEW_AS_AT_DATE,
NEW_COMPANY_CODE,
NEW_DEAL_TYPE,
NEW_DEAL_SUBTYPE,
NEW_CPARTY_CODE,
NEW_PORTFOLIO_CODE,
NEW_PRODUCT_TYPE,
NEW_CURRENCY,
NEW_CURRENCY_COMBINATION,
NEW_CONTRA_CCY,
NEW_ACCOUNT_NO,
sysdate, -- created_on
NEW_BASE_REF_AMOUNT, -- gross_principal
NEW_HCE_BASE_REF_AMOUNT, -- hce_gross_principal
round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac), -- weightedavgprin
NEW_TRANSACTION_RATE, -- avg_interest_rate
decode(NEW_DEAL_TYPE, 'FX', 0, NEW_INTEREST), -- interest
decode(NEW_DEAL_TYPE, 'FX', 0, NEW_HCE_INTEREST), -- hce_interest
round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac), -- base_w_avg_prin
NEW_BASE_RATE, -- avg_base_rate
decode(NEW_DEAL_TYPE, 'FX', abs(NEW_BASE_REF_AMOUNT), NULL), -- gross_base_amount
decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
*nvl(NEW_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
*nvl(NEW_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
);
/* DELETE */
/**************/
elsif P_ACTION = 'DELETE' then
l_currency :=OLD_CURRENCY;
delete from XTR_COST_OF_FUNDS
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),
GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0),
GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0),
AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
INTEREST = 0,
HCE_INTEREST = 0
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
AVG_INTEREST_RATE = decode((nvl(INTEREST,0) - nvl(NEW_INTEREST,0)), 0, null,
abs((nvl(INTEREST,0) - nvl(NEW_INTEREST,0))/
(nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)))),
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),0,null,
decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_BASE_RATE,0)/v_100,l_fac))/
(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),l_fac)))),
INTEREST = nvl(INTEREST,0) - nvl(OLD_INTEREST,0),
HCE_INTEREST = nvl(HCE_INTEREST,0) - nvl(OLD_HCE_INTEREST,0)
where rowid=l_rowid;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
currency,currency_combination,contra_ccy,
account_no,created_on,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,hce_interest,
base_weighted_avg_principal,avg_base_rate,gross_base_amount,
gross_contra_trans_amount, gross_contra_spot_amount)
values(
OLD_AS_AT_DATE,
OLD_COMPANY_CODE,
OLD_DEAL_TYPE,
OLD_DEAL_SUBTYPE,
OLD_CPARTY_CODE,
OLD_PORTFOLIO_CODE,
OLD_PRODUCT_TYPE,
OLD_CURRENCY,
OLD_CURRENCY_COMBINATION,
OLD_CONTRA_CCY,
OLD_ACCOUNT_NO,
sysdate,
OLD_BASE_REF_AMOUNT,
OLD_HCE_BASE_REF_AMOUNT,
0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
OLD_TRANSACTION_RATE,
decode(OLD_DEAL_TYPE, 'FX', 0, OLD_INTEREST),
decode(OLD_DEAL_TYPE, 'FX', 0, OLD_HCE_INTEREST),
0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
OLD_BASE_RATE,
decode(OLD_DEAL_TYPE, 'FX', abs(OLD_BASE_REF_AMOUNT), NULL), -- gross_base_amount
decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
*nvl(OLD_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
*nvl(OLD_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
);
/* UPDATE */
/**************/
elsif P_ACTION='UPDATE' then
if (NEW_DEAL_TYPE<>'IG') then /* IG handled by maintain_position_history, see comments for maintain_cof_ig */
l_currency :=NEW_CURRENCY;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),
GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0),
GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0),
AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
INTEREST = 0,
HCE_INTEREST = 0
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
AVG_INTEREST_RATE = decode((nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)
+nvl(NEW_BASE_REF_AMOUNT,0)), 0,null,
abs((nvl(INTEREST,0)-nvl(OLD_INTEREST,0)+nvl(NEW_INTEREST,0))/
(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)+nvl(NEW_BASE_REF_AMOUNT,0))
)) * 36500, -- bug 2345708
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),0,null,
decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*
nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
+round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac))/
(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0)),l_fac)))),
INTEREST = nvl(INTEREST,0) + nvl(NEW_INTEREST,0),
HCE_INTEREST = nvl(HCE_INTEREST,0) + nvl(NEW_HCE_INTEREST,0)
where rowid=l_rowid;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
currency,currency_combination,contra_ccy,
account_no,created_on,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,hce_interest,
base_weighted_avg_principal,avg_base_rate, gross_base_amount,
gross_contra_trans_amount, gross_contra_spot_amount)
values(
NEW_AS_AT_DATE,
NEW_COMPANY_CODE,
NEW_DEAL_TYPE,
NEW_DEAL_SUBTYPE,
NEW_CPARTY_CODE,
NEW_PORTFOLIO_CODE,
NEW_PRODUCT_TYPE,
NEW_CURRENCY,
NEW_CURRENCY_COMBINATION,
NEW_CONTRA_CCY,
NEW_ACCOUNT_NO,
sysdate,
NEW_BASE_REF_AMOUNT,
NEW_HCE_BASE_REF_AMOUNT,
round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
NEW_TRANSACTION_RATE,
decode(NEW_DEAL_TYPE, 'FX', 0, NEW_INTEREST), -- interest
decode(NEW_DEAL_TYPE, 'FX', 0, NEW_HCE_INTEREST), -- hce_interest
round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
NEW_BASE_RATE,
decode(NEW_DEAL_TYPE, 'FX', abs(NEW_BASE_REF_AMOUNT), NULL), -- gross_base_amount
decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
*nvl(NEW_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
*nvl(NEW_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
);
delete from XTR_COST_OF_FUNDS
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),
GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0),
GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0),
AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0))/
(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
INTEREST = 0,
HCE_INTEREST = 0
where rowid=l_rowid;
update XTR_COST_OF_FUNDS
set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
AVG_INTEREST_RATE =decode((nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),0,null,
abs((nvl(INTEREST,0)-nvl(OLD_INTEREST,0))/
(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)))) * 36500
, -- bug 2345708
BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),0,null,
decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*
nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac))/
(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),l_fac)))),
INTEREST = nvl(INTEREST,0) - nvl(OLD_INTEREST,0),
HCE_INTEREST = nvl(HCE_INTEREST,0) - nvl(OLD_HCE_INTEREST,0)
where rowid=l_rowid;
insert into XTR_COST_OF_FUNDS
(as_at_date,company_code,deal_type,
deal_subtype,party_code,portfolio_code,product_type,
currency,currency_combination,contra_ccy,
account_no,created_on,
gross_principal,hce_gross_principal,
weighted_avg_principal,avg_interest_rate,interest,hce_interest,
base_weighted_avg_principal,avg_base_rate, gross_base_amount,
gross_contra_trans_amount, gross_contra_spot_amount)
values(
OLD_AS_AT_DATE,
OLD_COMPANY_CODE,
OLD_DEAL_TYPE,
OLD_DEAL_SUBTYPE,
OLD_CPARTY_CODE,
OLD_PORTFOLIO_CODE,
OLD_PRODUCT_TYPE,
OLD_CURRENCY,
OLD_CURRENCY_COMBINATION,
OLD_CONTRA_CCY,
OLD_ACCOUNT_NO,
sysdate,
OLD_BASE_REF_AMOUNT,
OLD_HCE_BASE_REF_AMOUNT,
0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
OLD_TRANSACTION_RATE,
decode(OLD_DEAL_TYPE, 'FX', 0, 0-OLD_INTEREST), -- interest
decode(OLD_DEAL_TYPE, 'FX', 0, 0-OLD_HCE_INTEREST), --hce_interest
0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
OLD_BASE_RATE,
decode(OLD_DEAL_TYPE, 'FX', abs(OLD_BASE_REF_AMOUNT), NULL), -- gross_base_amount
decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
*nvl(OLD_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
*nvl(OLD_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
);
select a.deal_no deal_number,
1 transaction_number,
a.status_code status_code,
a.company_code company_code,
a.cparty_code cparty_code,
a.deal_type deal_type,
a.deal_subtype deal_subtype,
a.currency currency,
a.currency_buy currency_buy,
a.currency_sell currency_sell,
a.product_type,
a.portfolio_code,
'ACTUAL/ACTUAL' year_calc_type,
a.year_basis,
a.interest_rate interest_rate,
a.transaction_rate,
a.base_rate base_rate,
a.calc_basis,
a.start_date,
a.value_date,
a.option_commencement,
a.expiry_date,
a.maturity_date,
a.maturity_account_no account_no,
a.buy_amount base_amount,
a.sell_amount second_amount,
a.maturity_amount,
a.start_amount,
-- Added for Interest Override feature
-- But FX is always populate null value to day_count_type
a.day_count_type
from XTR_DEALS a
where a.deal_type ='FX'
and a.status_code <> 'CANCELLED'
and a.deal_date <= l_date
and nvl(a.start_date,l_date+1) > l_date
and value_date >l_date
Union all
/*********/
/* BOND */
/*********/
select a.deal_no deal_number,
1 transaction_number,
a.status_code status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.currency_buy,
a.currency_sell,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.year_basis,
a.interest_rate interest_rate,
a.transaction_rate,
a.base_rate base_rate,
a.calc_basis,
a.start_date,
a.value_date,
a.option_commencement,
a.expiry_date,
a.maturity_date,
a.maturity_account_no account_no,
a.start_amount base_amount,
a.settle_amount second_amount,
a.maturity_amount,
a.start_amount,
-- Added for Interest Override feature
a.day_count_type
from XTR_DEALS a
where a.deal_type ='BOND'
and a.deal_subtype in ('BUY', 'ISSUE')
and a.status_code ='CURRENT'
and a.start_date <=l_date
and a.maturity_date > l_date
Union All
/****************/
/* Bond resale */
/****************/
select a.deal_no deal_number,
1 transaction_number,
a.status_code status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.currency_buy,
a.currency_sell,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.year_basis,
a.interest_rate interest_rate,
a.transaction_rate,
a.base_rate base_rate,
a.calc_basis,
a.start_date,
a.value_date,
a.option_commencement,
a.expiry_date,
a.maturity_date,
a.maturity_account_no account_no,
a.start_amount base_amount,
a.settle_amount second_amount,
a.maturity_amount,
a.start_amount,
-- Added for the Interest Override feature
a.day_count_type
from XTR_DEALS a
where a.deal_type ='BOND'
and a.deal_subtype in ('BUY', 'ISSUE') -- Bond Repurchase Project - 2879858.
and a.status_code ='CURRENT'
and a.deal_no in (select distinct d.deal_no
from XTR_DEALS d,
XTR_BOND_ALLOC_DETAILS R
where d.deal_no = r.deal_no
and d.maturity_date <= l_date
and nvl(r.avg_rate_last_processed, r.cross_ref_start_date)
< d.maturity_date);
select a.party_code,
a.currency,
a.account_number,
a.bank_code,
a.portfolio_code,
a.year_calc_type
from xtr_bank_accounts a,
xtr_party_info b
where a.party_code=b.party_code
and b.party_type='C'
and nvl(a.setoff_account_yn,'N') ='N'
and nvl(a.opening_balance,0) <> 0 ;
select nvl(balance_adjustment,0)+nvl(statement_balance,0) base_amount,
interest_rate, balance_date, day_count_type -- Added day_count_type for Interest Override
from xtr_bank_balances
where company_code = L_COMPANY_CODE
and account_number = L_ACCOUNT_NO
and balance_date <= l_date
order by balance_date desc;
select a.deal_number, -- bug 2345708
a.amount_date,
a.company_code,
a.currency,
a.account_no,
a.limit_party,
a.portfolio_code,
a.product_type
from xtr_mirror_dda_limit_row a
where a.deal_type = 'IG';
select nvl(balance_out,0) base_amount,
interest_rate,
day_count_type -- Added for Interest Override feature
from xtr_intergroup_transfers
where company_code = L_COMPANY_CODE
and party_code= L_CPARTY_CODE
and currency = L_CURRENCY
and deal_number = L_DEAL_NUMBER
and transfer_date <= l_date
order by transfer_date desc,transaction_number desc;
select a.deal_number,
a.transaction_number,
a.status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.interest_rate,
a.start_date,
a.maturity_date,
a.balance_out_bf base_amount,
decode(nvl(a.principal_action,'DECRSE'),'INCRSE',
nvl(a.PRINCIPAL_ADJUST,0),0-nvl(a.PRINCIPAL_ADJUST,0)) second_amount,
a.interest,
-- Added for Interest Override feature
a.first_transaction_flag,
d.day_count_type
from XTR_ROLLOVER_TRANSACTIONS a,
-- Added for Interest Override feature
XTR_DEALS d
where a.deal_type in('TMM','RTMM')
and a.start_date <=l_date
and a.maturity_date >l_date
and a.status_code='CURRENT'
-- Added for Interest Override feature
and d.deal_no = a.deal_number
Union all
select a.deal_number,
a.transaction_number,
a.status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.interest_rate,
a.start_date,
a.maturity_date,
a.balance_out base_amount,
0 second_amount,
a.interest,
-- Added for Interest Override feature
a.first_transaction_flag,
d.day_count_type
from XTR_ROLLOVER_TRANSACTIONS a,
-- Added for Interest Override feature
XTR_DEALS d
where a.deal_type ='ONC'
and a.start_date <=l_date
and nvl(a.maturity_date,l_date+1) >l_date
and a.interest_rate is not null
and a.status_code='CURRENT'
-- Added for Interest Override feature
and d.deal_no = a.deal_number
Union all
select a.deal_number,
a.transaction_number,
a.status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.interest_rate,
a.start_date,
nvl(a.ni_reneg_date,a.maturity_date) maturity_date,
nvl(a.balance_out,0)-nvl(a.interest,0) base_amount,
0 second_amount,
a.interest,
-- Added for Interest Override feature
a.first_transaction_flag,
d.day_count_type
from XTR_ROLLOVER_TRANSACTIONS a,
-- Added for Interest Override feature
XTR_DEALS d
where a.deal_type ='NI'
and a.deal_subtype in ('BUY', 'ISSUE')
and a.start_date <=l_date
and nvl(a.ni_reneg_date,a.maturity_date) >l_date
-- Added for Interest Override feature
and d.deal_no = a.deal_number;
select CURRENCY_FIRST||'/'||CURRENCY_SECOND
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 ig_year_basis
from XTR_MASTER_CURRENCIES
where currency = L_CURRENCY;
select calc_basis
from xtr_deals
where deal_no=L_DEAL_NUMBER;
select b.year_calc_type, b.calc_type
from XTR_DEALS A, XTR_BOND_ISSUES B
where a.bond_issue = b.bond_issue_code
and a.deal_no = D.DEAL_NUMBER;
select 1
from XTR_COST_OF_FUNDS
where as_at_date = l_date;
select sum(face_value), max(cross_ref_start_date)
from xtr_bond_alloc_details
where deal_no = D.DEAL_NUMBER
and CROSS_REF_START_DATE <= l_date;
Update XTR_BOND_ALLOC_DETAILS
Set avg_rate_last_processed = L_DATE
where deal_no = d.deal_number -- bug 4470022 Added where clause
and cross_ref_start_date = l_cross_ref_start_date;
Select SUM(orig_coupon_amount),SUM(interest),SUM(original_amount)
Into l_orig_coupon,l_interest,l_original_amount
From xtr_rollover_transactions
Where deal_number = p_deal_no;
SELECT COUNT(deal_no)
INTO l_resale_count
FROM xtr_bond_alloc_details
WHERE deal_no=p_deal_no;
* and insert that summary information in the "temporary" XTR_AVG_RATES_RESULTS
* table. This table is actually always in the database but data that is
* inserted is never committed so the results can be seen during a session
* and erased once the session is over. This way no maintenance is necessary
* for this table.
* This procedure takes an unique batch id so the caller can distinguish one
* result set from another. Currently, the XTR_EXPOSURE_TRANS_S sequence is
* used to generate the id so it is advisable to use it to keep the uniqueness.
*/
PROCEDURE UPLOAD_AVG_RATES_RESULTS(
p_batch_id IN NUMBER,
p_group_type IN VARCHAR2,
p_date_from IN DATE,
p_date_to IN DATE,
p_company_code IN VARCHAR2,
p_deal_type IN VARCHAR2,
p_currency IN VARCHAR2,
p_contra_ccy IN VARCHAR2,
p_cparty_code IN VARCHAR2,
p_product_type IN VARCHAR2,
p_portfolio_code IN VARCHAR2,
p_group_by_month IN VARCHAR2,
p_group_by_year IN VARCHAR2,
p_group_by_company IN VARCHAR2,
p_group_by_deal IN VARCHAR2,
p_group_by_currency IN VARCHAR2,
p_group_by_cparty IN VARCHAR2,
p_group_by_product IN VARCHAR2,
p_group_by_portfolio IN VARCHAR2) IS
--
v_date_format VARCHAR2(15);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
-- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
decode(Sum(gross_principal),0,0,
(sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
/(p_date_to - p_date_from +1))) avg_rate,
Sum(no_of_deals) num_deals
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code = p_company_code
AND deal_type LIKE Nvl(p_deal_type,'%')
AND ( (deal_type = 'NI' AND deal_subtype = 'BUY')
OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
Decode(p_group_by_currency, 'Y', currency, Null),
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
-- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
decode(Sum(gross_principal),0,0,
(sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
/(p_date_to - p_date_from +1))) avg_rate,
Sum(no_of_deals) num_deals
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')
AND deal_type LIKE Nvl(p_deal_type,'%')
AND ( (deal_type = 'NI' AND deal_subtype = 'BUY')
OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
Decode(p_group_by_currency, 'Y', currency, Null),
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
-- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
decode(Sum(gross_principal),0,0,
(sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
/(p_date_to - p_date_from +1))) avg_rate,
Sum(no_of_deals) num_deals
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code = p_company_code
AND deal_type LIKE Nvl(p_deal_type,'%')
AND ( (deal_type = 'NI' AND deal_subtype = 'ISSUE')
OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
Decode(p_group_by_currency, 'Y', currency, Null),
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
-- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
decode(Sum(gross_principal),0,0,
(sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
/(p_date_to - p_date_from +1))) avg_rate,
Sum(no_of_deals) num_deals
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')
AND deal_type LIKE Nvl(p_deal_type,'%')
AND ( (deal_type = 'NI' AND deal_subtype = 'ISSUE')
OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
Decode(p_group_by_currency, 'Y', currency, Null),
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
currency_combination,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(gross_principal)/((p_date_to-p_date_from)+1) principal,
Sum(interest) interest,
Decode(sum(gross_base_amount),0,0, Sum(gross_contra_trans_amount)/Sum(gross_base_amount)) avg_rate,
Sum(no_of_deals) num_deals,
Decode(Sum(gross_base_amount),0,0, Sum(gross_contra_spot_amount)/Sum(gross_base_amount)) avg_spot_rate
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code = p_company_code
AND deal_type LIKE Nvl(p_deal_type,'%')
AND deal_type = 'FX'
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(contra_ccy,'%') LIKE Nvl(p_contra_ccy,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
currency_combination,
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT 'P', -- p_i,
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
currency_combination,
Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
Sum(gross_principal)/((p_date_to-p_date_from)+1) principal,
Sum(interest) interest,
Decode(sum(gross_base_amount),0,0, Sum(gross_contra_trans_amount)/Sum(gross_base_amount)) avg_rate,
Sum(no_of_deals) num_deals,
Decode(Sum(gross_base_amount),0,0, Sum(gross_contra_spot_amount)/Sum(gross_base_amount)) avg_spot_rate
FROM xtr_cost_of_funds_v
WHERE as_at_date BETWEEN p_date_from AND p_date_to
AND company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')
AND deal_type LIKE Nvl(p_deal_type,'%')
AND deal_type = 'FX'
AND currency LIKE Nvl(p_currency,'%')
AND Nvl(contra_ccy,'%') LIKE Nvl(p_contra_ccy,'%')
AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
GROUP BY 'P',
Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
Decode(p_group_by_company, 'Y', company_code, Null),
Decode(p_group_by_deal, 'Y', deal_type, Null),
currency_combination,
Decode(p_group_by_product, 'Y', product_type, Null),
Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
Decode(p_group_by_cparty, 'Y', party_code, Null);
SELECT Min(transaction_rate), Max(transaction_rate)
FROM xtr_position_history
WHERE as_at_date BETWEEN p_from_date and p_to_date
AND ((v_company IS NOT NULL AND company_code = v_company)
OR (v_company IS NULL AND
company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')))
AND deal_type LIKE Nvl(v_deal, '%')
AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
AND ((p_p_i = 'P') AND ((deal_type = 'NI' AND deal_subtype = 'BUY')
OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
OR ((deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM')) AND (deal_subtype = p_group_type))));
SELECT Min(transaction_rate), Max(transaction_rate)
FROM xtr_position_history
WHERE as_at_date BETWEEN p_from_date and p_to_date
AND ((v_company IS NOT NULL AND company_code = v_company)
OR (v_company IS NULL AND
company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')))
AND deal_type LIKE Nvl(v_deal, '%')
AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
AND ((p_p_i = 'P') AND ((deal_type = 'NI' AND deal_subtype = 'ISSUE')
OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
OR ((deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM')) AND (deal_subtype = p_group_type))));
SELECT Min(transaction_rate), Max(transaction_rate)
FROM xtr_position_history
WHERE as_at_date BETWEEN p_from_date and p_to_date
AND ((v_company IS NOT NULL AND company_code = v_company)
OR (v_company IS NULL AND
company_code IN (SELECT p.party_code
FROM xtr_parties_v p
WHERE p.party_type = 'C')))
AND deal_type LIKE Nvl(v_deal, '%')
AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
AND (currency_combination LIKE Nvl(v_currency_combination, '%') or
currency_combination is null)
AND ((p_p_i = 'P') AND (deal_type = 'FX'));
INSERT INTO xtr_avg_rates_results(unique_id, p_i, period, date_from, date_to, company_code, deal_type,
product_type, portfolio_code, cparty_code, currency, currency_combination, principal,
interest, average_contract_rate, average_spot_rate, minimum_rate, maximum_rate, num_deals)
VALUES(
p_batch_id, v_p_i, v_period,
v_from_date, v_to_date, v_company,
v_deal, v_product, v_portfolio, v_cparty,
v_currency, v_currency_combination, v_principal,
v_interest, v_avg_contract_rate, v_avg_spot_rate,
v_min_rate, v_max_rate, v_num_deals);