P_EmployStatDet

@AbapCatalog.sqlViewName: 'PEMPLOYSTATDET'
@VDM.viewType: #BASIC
//@EndUserText.label: 'Employment status'

@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
define view P_EmployStatDet
  as select from    pa0000         as it0000
    inner join      pa0001         as it0001 on  it0000.pernr =       it0001.pernr
                                             and it0001.sprps <>      'X'
                                             and it0000.endda between it0001.begda and it0001.endda
    left outer join wfd_d_new_pers as switch on switch.mandt = it0000.mandt
  association [0..1] to P_EmploymentOp as Employment on it0000.pernr = Employment.EmploymentInternalID

{
  key it0000.pernr                as EmploymentInternalID,
      @Semantics.businessDate.from/to
  key it0000.begda                as StartDate,
      @Semantics.businessDate.from/to
      it0000.endda                as EndDate,
      Employment.PersonnelNumber  as PersonnelNumber,  //cast(Employment.PersonnelNumber  as /shcm/employee_int_id)   as PersonnelNumber,

      Employment.IsMainEmployment as IsMainEmployment, //cast(Employment.IsMainEmployment as /shcm/main_employmnt_indicator) as IsMainEmployment,

      case it0000.stat2
       when '3' then '1'
       else '0'
      end                         as EmploymentStatus,
      it0001.vdsk1                as PersonWorkAgrmtAuthznGrpg
}
where
  (
       switch.switch is null
    or switch.switch <> 'NEW'
  )

union all select from wfd_d_assgmtdets as assgmtdets
  inner join          wfd_d_assgmt     as assgmt on  assgmt.workforce_assgmt_id =       assgmtdets.workforce_assgmt_id
                                                 and assgmtdets.end_date        between assgmt.start_date and assgmt.end_date
  inner join          wfd_d_new_pers   as switch on switch.mandt = assgmtdets.client
association [0..1] to P_EmploymentOp as Employment on assgmtdets.workforce_assgmt_id = Employment.EmploymentInternalID
{
  key assgmtdets.workforce_assgmt_id as EmploymentInternalID,
      @Semantics.businessDate.from/to
  key assgmtdets.start_date          as StartDate,
      @Semantics.businessDate.from/to
  key assgmtdets.end_date            as EndDate,
      Employment.PersonnelNumber     as PersonnelNumber, //cast(Employment.PersonnelNumber  as /shcm/employee_int_id)   as PersonnelNumber,

      Employment.IsMainEmployment    as IsMainEmployment, //cast(Employment.IsMainEmployment as /shcm/main_employmnt_indicator) as IsMainEmployment,

      assgmtdets.employment_status   as EmploymentStatus,
      ''                             as PersonWorkAgrmtAuthznGrpg

}
where
  switch.switch = 'NEW'