I_InactiveWorkAssignment

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@EndUserText.label: 'Work Assignment Status of a Worker'
@Metadata.ignorePropagatedAnnotations: true
@VDM: {
  viewType: #COMPOSITE,
  lifecycle.contract.type: #SAP_INTERNAL_API
}
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #XL,
  dataClass: #MIXED
}
@Consumption.dbHints:[ 'USE_HEX_PLAN' ]
//............................................................................................................................

// This CDS view is meant for private consumption in Worker Overview ONLY ---- Do not use it for for other applications

//............................................................................................................................


define view entity I_InactiveWorkAssignment
  as select from   I_WorkforcePersonKeyMap as keymap     
//  I_WorkforceAssignment_1             as assgmt

//    inner join   I_WorkforcePersonKeyMap as keymap     on keymap.WorkAssignment = assgmt.WorkforceAssignment

    inner join   I_WorkAssignmentDetail_1         as assgmtdets on assgmtdets.WorkforceAssignment = keymap.WorkAssignment
                                                       and(
                                                         (
                                                           assgmtdets.WorkforceAssgmtStartDate        <= $session.system_date
                                                           and assgmtdets.WorkforceAssgmtEndDate      >= $session.system_date
                                                         )
                                                         or(
                                                           assgmtdets.WorkforceAssgmtStartDate        >= $session.system_date
                                                         )
                                                       )


{

  key keymap.BusinessPartner,
      max(assgmtdets.WorkforceAssignmentStatus) as WorkforceAssignmentStatus,
      @Semantics.booleanIndicator: true
      min(assgmtdets.IsBlocked)         as IsBlocked
}
group by
  keymap.BusinessPartner
  
  
//{

//{

//  key assgmt.workforce_assgmt_id   as WorkAssignment,

//  key assgmt.business_partner_id   as BusinessPartner,

//      assgmt.start_date            as StartDate,

//      assgmt.end_date              as EndDate,

//      @Semantics.booleanIndicator

//      assgmtdets.employment_status as WorkAssignmentStatus,

//      case

//      when

//      assgmtdets.employment_status = '0'

//      then cast ( 'X' as boolean )

//      else cast ( ' ' as boolean )

//      end                          as WorkerIsInactive,

//      //

//      case

//      when keymap.IsBlocked = 'X'

//      then  cast( 'X' as wfd_blocked)

//      else cast( ' ' as wfd_blocked )

//      end                          as IsBlocked

//}

//where

//  (

//        assgmtdets.start_date      < $session.system_date

//    and assgmtdets.end_date        > $session.system_date

//  )

//  and   assgmt.business_partner_id is not initial