R_WrkAssignmentBankAccountTP_2

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@EndUserText.label: 'TP View for Bank Accounts for Workforce Person'
@Metadata.ignorePropagatedAnnotations: true
@VDM: {
  viewType: #TRANSACTIONAL,
  lifecycle.contract.type: #SAP_INTERNAL_API
}

@ObjectModel.usageType:{
  serviceQuality: #C,
  sizeCategory: #XL,
  dataClass: #MIXED
}
@AccessControl.personalData.blocking: #NOT_REQUIRED
//@Consumption.dbHints: [ 'USE_HEX_PLAN' ]

@Consumption.dbHints: [ '&PREFER_JOIN_WITH_FDA 0&', 'USE_HEX_PLAN' ]
define view entity R_WrkAssignmentBankAccountTP_2
  as select from I_BusinessPartnerBank_2 as _BusinessPartnerBank
    inner join   R_WorkAssignmentBP      as _BuPaIdentification on _BuPaIdentification.WorkAssignmentBusinessPartner = _BusinessPartnerBank.BusinessPartner

  association [0..1] to I_CountryText               as _CountryText     on  $projection.BankCountryKey = _CountryText.Country
                                                                        and _CountryText.Language      = $session.system_language

  //Below associations for handling DCL

  association [1..1] to R_WorkforcePersonTP_2       as _WorkforcePerson on  $projection.BusinessPartner = _WorkforcePerson.BusinessPartner
  association        to parent R_WorkAssignmentTP_2 as _WorkAssignment  on  $projection.WorkAssignmentBusinessPartner = _WorkAssignment.WorkAssignmentBusinessPartner

{
  key    _BusinessPartnerBank.BusinessPartner                         as WorkAssignmentBusinessPartner,
  key    _BusinessPartnerBank.BankIdentification                      as BankIdentification,
         _BuPaIdentification.WorkforceAssignment                      as WorkforceAssignment,
         _BusinessPartnerBank.BankAccountHolderName                   as BankAccountHolderName,
         _BusinessPartnerBank.BankAccountName                         as BankAccountName,
         _BusinessPartnerBank.BankControlKey                          as BankControlKey,
         _BusinessPartnerBank.BankCountryKey                          as BankCountryKey,
         _BusinessPartnerBank.BankNumber                              as Bank,
         _BusinessPartnerBank.BankName                                as BankName,
         _BusinessPartnerBank.BankAccount                             as BankAccount,
         _BusinessPartnerBank.IBAN                                    as IBAN,
         _BusinessPartnerBank.SWIFTCode                               as SWIFTCode,
         @Semantics.businessDate.from: true
         case when _BusinessPartnerBank.ValidityStartDate != 00010101000000
         then tstmp_to_dats(_BusinessPartnerBank.ValidityStartDate,'UTC',  $session.client, 'NULL')
         else cast ( '00010101' as abap.dats )
         end                                                          as ValidityStartDate,
         @Semantics.businessDate.to: true
         case when _BusinessPartnerBank.ValidityEndDate != 00010101000000
         then tstmp_to_dats(_BusinessPartnerBank.ValidityEndDate,'UTC',  $session.client, 'NULL')
         else cast ( '99991231' as abap.dats)
         end                                                          as ValidityEndDate,
         cast(_BusinessPartnerBank.BankAccountReferenceText as refbk) as BankReference,
         _BuPaIdentification.BusinessPartner                          as BusinessPartner,
         @Semantics.booleanIndicator
         _BusinessPartnerBank.BPBankIsProtected                       as BPBankIsProtected,
         _WorkAssignment,
         _WorkforcePerson,
         _CountryText
         
}
where
     _BusinessPartnerBank.BankNumber  is not null
  or _BusinessPartnerBank.BankAccount is not null
  or _BusinessPartnerBank.IBAN        is not null