P_EMPLOYEECL

@VDM.private: true
@AbapCatalog.sqlViewName: 'PEMPLOYEECL'
@VDM.viewType:  #BASIC 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view P_EmployeeCl as select from but000 as bp  
    inner join P_PDOBJECTRELATION(P_ObjectType :'XE',
                                  P_RelationShipDirection :'A',
                                  P_RelationShip :'332',
                                  P_RelatedObjectType : 'BP' )  
                                  as pdrelation 
                                  on bp.partner = pdrelation.RelatedObjectID
                                  
    inner join /shcm/hrp5091 as LogDel on pdrelation.PlanVersion =   LogDel.plvar and
                                          pdrelation.ObjectType  =   LogDel.otype and 
                                          pdrelation.ObjectID    =   LogDel.objid and
                                          LogDel.logical_deleted = '0'   
      
    inner join but0id      as bpexid   on bp.partner = bpexid.partner and 
                                        ( bpexid.type = 'HCM001' or   // HCM001 - Employee ID    

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

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

    inner join but100     as bp_role  on bp_role.partner = bp.partner and
                                        ( bp_role.rltyp = 'BUP003' or   // BUP003 - Employee 

                                          bp_role.rltyp = 'BBP005' or   // BBP005 - Service Agent

                                          bp_role.rltyp = 'BBP010' )    // BBP010 - Freelancer     

                                           
                                   
//    left outer join I_User as User     on bp.partner_guid = User.BusinessPartnerUUID    

//    left outer join /shcm/hrp5092 as EmployeeImage on EmployeeImage.otype = 'XE' and

//                                                      EmployeeImage.objid = pdrelation.ObjectID

   {
    key pdrelation.ObjectID  as PersonnelNumber,            //Internal stable ID

    bp.partner               as EmployeeInternalID,         //BUPA ID

    bpexid.idnumber          as Employee,                   //External PERNR  

    @Semantics.businessDate.from/to   
    pdrelation.StartDate     as ValidityStartDate,
    @Semantics.businessDate.from/to
    pdrelation.EndDate       as ValidityEndDate, 
    bp.title      as FormOfAddress, 
    bp.name_last  as FamilyName,
    bp.name_first as FirstName,   //depricated

    bp.mc_name1   as LastName,            //Search - Use for search only 

    bp.mc_name2   as BusinessPartnerName, //Search - Use for search only 

    bp.name_first as GivenName,
    bp.namemiddle as MiddleName, 
    bp.name_lst2  as AdditionalFamilyName,
    bp.title_aca1 as AcademicTitle,
    bp.prefix1    as FamilyNamePrefix, 
    bp.initials   as Initials,   
    bp.name1_text as FullName, //depricated

    bp.name1_text as EmployeeFullName,
      
    bp.langu_corr as CorrespondenceLanguage,            // Association to Language view

    case bp.xsexm 
      when 'X' then '1'
      else
        case bp.xsexf
          when 'X' then '2'
          else '' 
        end
    end as GenderCode,
     
    '' as BusinessUser, 
    '' as UserID, 
    '' as EmployeeImageURL,
    bp_role.rltyp   as BusinessPartnerRole,
    bp.persnumber   as Person,
    bp.partner_guid as BusinessPartnerUUID,
    
    bp.crusr as CreatedByUser,
    bp.crdat as CreationDate,
    bp.crtim as CreationTime, 
    bp.chusr as LastChangedByUser,  
    bp.chdat as LastChangeDate,
    bp.chtim as LastChangeTime
} 

where bp.type = '1'