P_PersonWorkAgrmtToExternalID

@AbapCatalog.sqlViewName: 'PPERWKAGREXT'
@VDM.viewType: #BASIC
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory: #L
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@Metadata.ignorePropagatedAnnotations:true
@ObjectModel.representativeKey: 'PersonWorkAgreement'
@AbapCatalog.preserveKey:true
//@EndUserText.label: 'Mapping Wrk agrmnt internal&External IDs'

define view P_PersonWorkAgrmtToExternalID
  as select distinct from pa0001                  as PA0001
    inner join            pa0001                  as PA0001_ForAuthorization on  pa0001.pernr                  =  PA0001_ForAuthorization.pernr
                                                                             and PA0001_ForAuthorization.endda =  '99991231'
                                                                             and PA0001_ForAuthorization.sprps <> 'X'
    left outer join       wfd_d_new_pers          as switch                  on pa0001.mandt = switch.mandt
    left outer join       I_PersonWorkAgrmtToECId as PA0105                  on pa0001.pernr = PA0105.PersonWorkAgreement
  association [1] to I_CompanyCode as _CompanyCode on pa0001.bukrs = _CompanyCode.CompanyCode

{
  key pa0001.pernr                  as PersonWorkAgreement,
      @ObjectModel.foreignKey.association : '_CompanyCode'
  key pa0001.bukrs                  as CompanyCode,
      case when PA0105.PersonWorkAgreementExternalID is not null
      then cast ( PA0105.PersonWorkAgreementExternalID as /shcm/workagreement_externalid )
      else
         cast( pa0001.pernr as /shcm/workagreement_externalid )
      end                           as PersonWorkAgreementExternalID,
      //for DCL

      PA0001_ForAuthorization.vdsk1 as PersonWorkAgrmtAuthznGrpg,
      _CompanyCode

}
where
      pa0001.sprps  <> 'X'
  and switch.switch is null
  or  switch.switch <> 'NEW'

union all select from wfd_d_assgmtdets        as wfd_d_assgmtdets
  inner join          wfd_d_new_pers          as switch on wfd_d_assgmtdets.client = switch.mandt
  left outer join     P_PersonWorkAgrmtToECId as PA0105 on wfd_d_assgmtdets.workforce_assgmt_id = PA0105.PersonWorkAgreement
association [1] to I_CompanyCode as _CompanyCode on wfd_d_assgmtdets.company_code = _CompanyCode.CompanyCode

{
  key wfd_d_assgmtdets.workforce_assgmt_id as PersonWorkAgreement,
      @ObjectModel.foreignKey.association : '_CompanyCode'
  key wfd_d_assgmtdets.company_code        as CompanyCode,
      case when PA0105.PersonWorkAgreementExternalID is not null
          then cast ( PA0105.PersonWorkAgreementExternalID as /shcm/workagreement_externalid )
          else
             cast( wfd_d_assgmtdets.workforce_assgmt_id as /shcm/workagreement_externalid )
          end                              as PersonWorkAgreementExternalID,
      ''                                   as PersonWorkAgrmtAuthznGrpg,
      _CompanyCode

}

where
  switch.switch = 'NEW'
  and wfd_d_assgmtdets.block_ind = ''