P_Fa_Bkh

--@EndUserText.label: 'Bank Account Hierarchy for Flow Analyzer' //same as DDL description

@AbapCatalog.sqlViewName: 'PFA_BKH' //must start with "I"

@AccessControl.authorizationCheck: #NOT_REQUIRED
--@Analytics: { dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'BankAccountHierarchy'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.sizeCategory: #S
@ObjectModel.usageType.serviceQuality: #A
@Metadata.ignorePropagatedAnnotations: true
define view P_Fa_Bkh //must start with "I_"; same as DDL source name in upper-camelcase notation

  with parameters
    @Environment.systemField: #SYSTEM_LANGUAGE
    P_Language : spras,
    @Environment.systemField: #SYSTEM_DATE
    P_KeyDate  : datum
  --  @Environment.systemField: #SYSTEM_USER
  --  P_UserID   : uname
  as select from    I_BankAccountUniversalHier as bag
    left outer join hrrp_typt                  as typtext on  bag.HierarchyType = typtext.hrytyp
                                                          and typtext.spras     = $parameters.P_Language
{
  key bag.BankAccountHierarchy           as BankAccountHierarchy,
      bag._Text[1:Language= $parameters.P_Language].BankAccountHierarchyName,
      cast(' ' as fclm_bam_hiertype)     as HierarchyType,
      typtext.text                       as HierarchyTypeText,
      bag.IsPublicHierarchy              as IsPublicHierarchy
}
where
      bag.ValidityStartDate <= $parameters.P_KeyDate
  and bag.ValidityEndDate   >= $parameters.P_KeyDate

union all

select from       I_BankHierarchy as bph
  left outer join dd07t                     on  dd07t.domvalue_l = '03'
                                            and dd07t.ddlanguage = $parameters.P_Language
                                            and dd07t.domname    = 'FCLM_BAM_HIERTYPE'
{
  key bph.BankAccountHierarchy           as BankAccountHierarchy,
      bph._Text[1:Language= $parameters.P_Language].BankAccountHierarchyName,
      cast('03' as fclm_bam_hiertype)    as HierarchyType,
      coalesce(dd07t.ddtext, '')         as HierarchyTypeText,
      'X'                                as IsPublicHierarchy
}
where
      bph.ValidityStartDate <= $parameters.P_KeyDate
  and bph.ValidityEndDate   >= $parameters.P_KeyDate

union all

select from       I_CashPoolHierarchy as cph
  inner join      I_CashPoolMD        as cp   on cph.LeadingCashPoolID = cp.CashPoolName
  left outer join I_CashPoolMDDesc    as desc on  cph.LeadingCashPoolID = desc.CashPoolName
                                              and desc.Language         = $parameters.P_Language
  left outer join dd07t                       on  dd07t.domvalue_l = '04'
                                              and dd07t.ddlanguage = $parameters.P_Language
                                              and dd07t.domname    = 'FCLM_BAM_HIERTYPE'
{
  key cph.LeadingCashPoolID         as BankAccountHierarchy,
      desc.CashPoolDesc             as BankAccountHierarchyName,
      '04'                          as HierarchyType,
      coalesce(dd07t.ddtext, '')    as HierarchyTypeText,
      'X'                           as IsPublicHierarchy
}
where
      cp.CashPoolIsDeleted  =  'N'
  and cph.LeadingCashPoolID <> ''