I_WorkerSearch

@AbapCatalog.sqlViewName: 'IWORKERSEARCH'
@AbapCatalog.preserveKey:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType:  #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@EndUserText.label: 'Workforce CDS view for OP'
define view I_WorkerSearch
  as select from    I_WorkforceAssignment        as assgmt
    inner join      I_WorkforceAssignmentDetails    as assgmtdets        on  assgmtdets.WorkforceAssignment = assgmt.WorkforceAssignment
                                                             and 
                                                             ( ( 
                                                             assgmtdets.WorkforceAssgmtStartDate         <= $session.system_date
                                                             and assgmtdets.WorkforceAssgmtEndDate            >= $session.system_date)
                                                             
                                                             or
                                                             (
                                                             assgmtdets.WorkforceAssgmtStartDate         >= $session.system_date
                                                             ))
    inner join      I_BusinessPartner              as bp      on  bp.BusinessPartner = assgmt.BusinessPartner
                                                             and bp.BusinessPartnerCategory    = '1'
   inner join      I_WorkforceAssignmentKeyMap       as keymap           on  keymap.WorkforceAssignment = assgmt.WorkforceAssignment
//                                                                         and keymap.BusinessPartner = bp.BusinessPartner


//    inner join      but000              as bp                on  bp.partner = assgmt.business_partner_id

//                                                             and bp.type    = '1'

    inner join      I_BPUsrExternalID            as bpexid    on bp.BusinessPartner         = bpexid.BusinessPartner
                                                             and(
                                                               bpexid.BPIdentificationType = 'HCM001'
                                                               or // HCM001 - Employee ID

                                                               bpexid.BPIdentificationType = 'HCM030'
                                                               or // HCM030 - Service Agent

                                                               bpexid.BPIdentificationType = 'HCM031'
                                                             )    // HCM031 - Freelancer

    left outer join I_CompanyCode       as cocd              on cocd.CompanyCode = assgmtdets.CompanyCode

  //    left outer join I_CostCenter     as cocs       on  cocs.CostCenter = assgmtdets.cost_center

  //                                                 and assgmtdets.company_code = cocs.CompanyCode

    left outer join I_WorkforceAssignmentJobTitle as jobnm     on  jobnm.WorkforcePersonJobCode = assgmtdets.WorkforcePersonJobCode
                                                             and jobnm.WorkforceAssgmtEndDate = '99991231'
                                                             and jobnm.Language = $session.system_language

    left outer join I_EmployeeskillTags as EmployeeSkillTags on  keymap.CentralPerson  = EmployeeSkillTags.PersonnelNumber
                                                                and EmployeeSkillTags.EmployeeSkillTagType = 'U'



  //  left outer join /shcm/d_cnt_wrk as roleflg          on assgmt.workforce_assgmt_id = roleflg.pernr

  //  left outer join but050          as ServicePerformer on  ServicePerformer.partner2 = bp.partner

  //                                                     and ServicePerformer.reltyp   = 'BUR025'


  // association [0..1] to but000 as _Supplier on _Supplier.partner = ServicePerformer.partner1



{
  key assgmt.WorkforceAssignment             as EmploymentInternalID,
      //  key assgmtdets.workforce_assgmt_id                                     as EmploymentInternalID,

      bp.BusinessPartner                     as EmployeeInternalID,
      bpexid.BPIdentificationNumber          as Employee,
      bp.PersonFullName                      as EmployeeFullName,
      bp.LastName                            as FamilyName,
      bp.FirstName                           as GivenName,
      bp.BusinessPartnerNicknameLabel        as BusinessPartnerNicknameLabel,
      assgmtdets.CompanyCode                 as CompanyCode,
      cocd.CompanyCodeName,
      ////

      assgmtdets.CostCenter                  as CostCenter,
      //      cocs._Text[1: Language =  $session.system_language].CostCenterName as CostCenterName,


      ''                                     as BusinessPartnerType,
      assgmtdets.WorkforcePersonJobCode      as Job,
      jobnm.WorkforcePersonJobTitle          as JobName,
     EmployeeSkillTags.EmployeeSkillTagName  as EmployeeSkillTagName,

      ''                                     as Supplier,
      bp.AuthorizationGroup                  as AuthorizationGroup,
      bp.IsBusinessPurposeCompleted          as IsBusinessPurposeCompleted

}
where

  // ( assgmtdets.cost_center <> '' and assgmtdets.company_code <> ''  )


  (
   /* (
          assgmtdets.cost_center      <> ''
      or  assgmt.is_contingent_worker =  'X'
    )
    and */
      assgmtdets.CompanyCode     <> ''
      and
      assgmtdets.WorkforceAssignmentStatus = '1'
      
  )

  and(
    (
          assgmt.WorkforceAssgmtStartDate           <= $session.system_date
      and assgmt.WorkforceAssgmtEndDate             >= $session.system_date
    )
    or(
          assgmt.WorkforceAssgmtStartDate           >= $session.system_date
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BPUSREXTERNALID",
"I_BUSINESSPARTNER",
"I_COMPANYCODE",
"I_EMPLOYEESKILLTAGS",
"I_WORKFORCEASSIGNMENT",
"I_WORKFORCEASSIGNMENTDETAILS",
"I_WORKFORCEASSIGNMENTJOBTITLE",
"I_WORKFORCEASSIGNMENTKEYMAP"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/