P_FXTransactionNominal

@AbapCatalog.sqlViewName: 'PFXTRANSNOM'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #BASIC
@VDM.private:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_FXTransactionNominal 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
    left outer join atpa
    on atpa.sgsart = ha.sgsart
 {
       ha.rfha as deal_number, // for drilldown

       wzbetr as Currency,
       ha.bukrs as CompanyCode,
       cast(sanlf as ftr_gen_fin_instr_prod_categ)         as FinancialInstrProductCategory,
       case ssign
         when '-' then ( -1 ) * bzbetr
         else bzbetr
       end as Amount,
       hedge_class as hedging_class,
       ha.sgsart as FinancialInstrumentProductType,
       merkm as trm_characteristics,
       ha.rantyp as trm_contract_type,
       zuond as trm_assignment,
//       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

       hazu.delfz as trm_end_of_term,
       rportb as portfolio,
       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,
       settlfl

 }
     where ha.saktiv = '0'
       and hazu.saktiv = '0'
       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' )


  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 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
    left outer join atpa
    on atpa.sgsart = ha.sgsart
    {
       ha.rfha as deal_number,  // for drilldown

       wzbetr as Currency ,
       ha.bukrs as CompanyCode,
       cast(ha.sanlf as ftr_gen_fin_instr_prod_categ)         as FinancialInstrProductCategory,
       case ssign
         when '-' then ( -1 ) * bzbetr
         else bzbetr
       end as Amount,
       hedge_class as hedging_class,
       ha.sgsart as FinancialInstrumentProductType,
       merkm as trm_characteristics,
       ha.rantyp as trm_contract_type,
       zuond as trm_assignment,
       ihazu.delfz as trm_end_of_term,
       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,
       atpa.settlfl
    }
    where ifha.saktiv = '0' 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",
"VTBFHA",
"VTBFHAPO",
"VTBFHAZU",
"VTIFHA",
"VTIFHAPO",
"VTIFHAZU",
"VTIOFZU"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/