@AbapCatalog.sqlViewName: 'TOEV_TR_DEALS'
@ClientHandling.type: #INHERITED
@ClientHandling.algorithm: #AUTOMATED
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'cds view for deal nominals in TOENE'
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #M
@ObjectModel.usageType.dataClass: #MIXED
define view TOE_CDS_TRM_DEALS
with parameters
p_keydate :dats
as select from vtbfha as ha
inner join vtbfhazu as hazu on hazu.rfha = ha.rfha
and hazu.bukrs = ha.bukrs
and hazu.rfhazu = ha.rfhazul
inner join vtbfhapo as hapo on ha.rfha = hapo.rfha
and ha.bukrs = hapo.bukrs
and ha.rfhazul = hapo.rfhazu
and hazu.dcrdat = hapo.dcrdat
and hazu.tcrtim = hapo.tcrtim
inner join t001 on t001.bukrs = ha.bukrs
left outer join atpa on atpa.sgsart = ha.sgsart
left outer join (
refon inner join refh on refon.refnr = refh.refnr
and refon.reftyp = refh.reftyp
and refh.status = '0'
and refon.status = '0'
and refh.reftyp = 'OPT' )
on refon.objnr = ha.objnr
and refon.reftyp = refh.reftyp
{
ha.rfha as deal_number, // for drilldown
case
when wzbetr <> t001.waers then wzbetr
when ha.wgschft1 <> t001.waers then ha.wgschft1
else ha.wgschft2
end as currency,
// Get Amount depending on parameter (filled with KeyDate or '00000000'
case
when hazu.delfz >= :p_keydate then
case
when ssign = '-' and wzbetr <> t001.waers then ( -1 ) * bzbetr
when ssign = '+' and wzbetr <> t001.waers then bzbetr
else 0
end
else 0
end as amount,
ha.bukrs as company_code,
ha.sfgtyp as trm_trans_cat,
ha.sfgtyp as trm_trans_type_long, // extra output field for restriction of selection via WHERE in lcl_hedges_query
hedge_class as hedging_class,
ha.sgsart as trm_product_type,
merkm as trm_characteristics,
ha.rantyp as trm_contract_type,
zuond as trm_assignment,
hazu.delfz as trm_end_of_term,
case atpa.settlfl
when '3' then hazu.dfix // fixing date is hedging relevant for NDFs but
else hazu.delfz // due date is hedging relevant for forwards
end as trm_fixing_date,
hazu.delfz as end_of_term_opt_exec, // needed for union with options with exercise date as relevant date
case atpa.settlfl
when '3' then hazu.dfix // fixing date is hedging relevant for NDFs
else hazu.delfz // due date is hedging relevant for forwards
end as trm_end_opt_ndf, // both Opt and NDF flag is set
rportb as portfolio,
ha.kontrh as business_partner,
sfgzustt as trm_activity_category,
ha.sfhaart as trm_transaction_type,
dvtrab as trm_contract_conclusion_date,
prctr as profit_center,
rcntr as cost_center,
ps_posid as wbs_element,
rbusa as business_area,
country as country,
fb_segment as segment,
behalf_of_company as on_behalf_of_company,
t001.waers as riskfree_currency,
ha.hedge_request_id as hedge_request_id,
ha.dblfz as trm_contract_date,
case
when hazu.delfz >= :p_keydate then
case
when ssign = '-' and wzbetr = t001.waers then ( -1 ) * bzbetr
when ssign = '+' and wzbetr = t001.waers then bzbetr
else 0
end
else 0
end as company_code_amount,
refon.refnr as collar_ref_number
}
where
ha.saktiv = '0'
and hazu.saktiv = '0'
and not(
hazu.sfgzustt = '80' or
hazu.sfgzustt = '90' )
and not(
(
hapo.sbktyp = '31'
or hapo.sbktyp = '32' // exclude cash settlement flows
or hapo.sbktyp = '90' // exclude additional flows
or hapo.sbktyp = '30'
) // exclude option premiums
and hapo.sbkklas = '1'
)
and(
hapo.sherkunft = 'LEA1' or // restriction so the additional flows of NDF are not read
hapo.sherkunft = 'LEA2'
)
union all
// Option underlyings
select from vtbfha as ha
inner join vtbfhazu as hazu on hazu.rfha = ha.rfha
and hazu.bukrs = ha.bukrs
and hazu.rfhazu = ha.rfhazul
inner join vtiof as iof on iof.optnr = ha.rgatt
inner join vtiofzu as iofzu on iofzu.optnr = ha.rgatt
inner join vtifha as ifha on ifha.rfha = iofzu.rfha
inner join vtifhazu as ihazu on ihazu.rfha = ifha.rfha
and ihazu.rfhazu = ifha.rfhazul
inner join vtifhapo as ihapo on ifha.rfha = ihapo.rfha
and ifha.rfhazul = ihapo.rfhazu
inner join t001 on t001.bukrs = ha.bukrs
left outer join (
refon inner join refh on refon.refnr = refh.refnr
and refon.reftyp = refh.reftyp
and refh.status = '0'
and refon.status = '0'
and refh.reftyp = 'OPT' )
on refon.objnr = ha.objnr
and refon.reftyp = refh.reftyp
{
ha.rfha as deal_number, // for drilldown
case
when ihapo.wzbetr <> t001.waers then ihapo.wzbetr
when ifha.wgschft1 <> t001.waers then ifha.wgschft1
else ifha.wgschft2
end as currency,
// Get Amount depending on parameter (filled with KeyDate or '00000000'
case
when ihazu.delfz >= :p_keydate then
case
when ihapo.ssign = '-' and ihapo.wzbetr <> t001.waers then ( -1 ) * ihapo.bzbetr
when ihapo.ssign = '+' and ihapo.wzbetr <> t001.waers then bzbetr
else 0
end
else 0
end as amount,
ha.bukrs as company_code,
ha.sfgtyp as trm_trans_cat,
case ha.sfgtyp
when '100' then ha.sfgtyp // Long Option
when '200' then ha.sfgtyp // Short Option
else '000' // we neglect the Short Options
end as trm_trans_type_long, // extra output field for restriction of selection via WHERE in lcl_hedges_query
hedge_class as hedging_class,
ha.sgsart as trm_product_type,
merkm as trm_characteristics,
ha.rantyp as trm_contract_type,
zuond as trm_assignment,
ihazu.delfz as trm_end_of_term,
ihazu.delfz as trm_fixing_date,
iof.dmatur as end_of_term_opt_exec,
iof.dmatur as trm_end_opt_ndf, // both Opt and NDF flag is set
ha.rportb as portfolio,
ha.kontrh as business_partner,
hazu.sfgzustt as trm_activity_category,
ha.sfhaart as trm_transaction_type,
hazu.dvtrab as trm_contract_conclusion_date,
prctr as profit_center,
rcntr as cost_center,
ps_posid as wbs_element,
rbusa as business_area,
country as country,
fb_segment as segment,
behalf_of_company as on_behalf_of_company,
t001.waers as riskfree_currency,
ha.hedge_request_id as hedge_request_id,
ha.dblfz as trm_contract_date,
case
when ihazu.delfz >= :p_keydate then
case
when ihapo.ssign = '-' and ihapo.wzbetr = t001.waers then ( -1 ) * ihapo.bzbetr
when ihapo.ssign = '+' and ihapo.wzbetr = t001.waers then bzbetr
else 0
end
else 0
end as company_code_amount,
refon.refnr as collar_ref_number
}
where
ifha.saktiv = '0'
and not(
hazu.sfgzustt = '80' or
hazu.sfgzustt = '90' or
hazu.sfgzustt = '42' )
and ha.saktiv = '0'
and hazu.saktiv = '0'
and ihazu.saktiv = '0'
and
// ifha.sanlf = '600' and // only plain vanilla fx options -> allowed product types are filtered in hedging area filter def.
iofzu.vrfha = ''
and // option not yet exercised,
// else underlying is in vtb* world and we must not count double
not(
(
ihapo.sbktyp = '31'
or ihapo.sbktyp = '32'
or ihapo.sbktyp = '90'
or ihapo.sbktyp = '30'
)
and ihapo.sbkklas = '1'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ATPA",
"REFH",
"REFON",
"T001",
"VTBFHA",
"VTBFHAPO",
"VTBFHAZU",
"VTIFHA",
"VTIFHAPO",
"VTIFHAZU",
"VTIOF",
"VTIOFZU"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/