P_O_PERSWRKAGRMTMGRFORKEYDTE

@AbapCatalog.sqlViewName: 'POPERWRKAGRMTMGR'
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AbapCatalog.preserveKey:true
//@Analytics.dataCategory: #DIMENSION

//@Metadata.allowExtensions: true

@ObjectModel.representativeKey: 'PersonWorkAgreement'
//@EndUserText.label: 'Retrieve manager for given date'

//@Metadata.ignorePropagatedAnnotations:true

define view P_O_PersWrkAgrmtMgrForKeyDte
  with parameters
    @Consumption.hidden     : true
    @Environment.systemField: #SYSTEM_DATE
    P_KeyDate : vdm_v_key_date
  as select from    P_O_PersWrkAgrmtPosDet                                             as PersWrkAgrmtPosDet
    left outer join I_PersWrkAgrmtPosToMgrPosition(P_KeyDate: $parameters.P_KeyDate) as PersWrkAgrmtPosToMgrPosition on PersWrkAgrmtPosToMgrPosition.PersonWorkAgreement = PersWrkAgrmtPosDet.PersonWorkAgreement

    left outer join P_O_PersWrkAgrmtPosDet                                             as PersonWrkAgrmtManager        on  PersonWrkAgrmtManager.PersonWrkAgrmtPosition = PersWrkAgrmtPosToMgrPosition.ManagerWrkAgrmtPosition // retrieve the corresponding workagreement of teh position

                                                                                                                     and PersonWrkAgrmtManager.StartDate              <= $parameters.P_KeyDate
                                                                                                                     and PersonWrkAgrmtManager.EndDate                >= $parameters.P_KeyDate

  association [1..1] to I_PersonWorkAgreement   as _ManagerPersonWorkAgreement   on _ManagerPersonWorkAgreement.PersonWorkAgreement = $projection.ManagerPersonWorkAgreement
  association [1..1] to I_PersonWorkAgreement_1 as _ManagerPersonWorkAgreement_1 on _ManagerPersonWorkAgreement_1.PersonWorkAgreement = $projection.ManagerPersonWorkAgreement

{
  key PersWrkAgrmtPosDet.PersonWorkAgreement as PersonWorkAgreement,
  key $parameters.P_KeyDate                  as KeyDate,
      @ObjectModel.foreignKey.association: '_ManagerPersonWorkAgreement_1'
  key case when PersWrkAgrmtPosToMgrPosition.ManagerWrkAgrmtPosition is not null
    then PersonWrkAgrmtManager.PersonWorkAgreement
   when PersWrkAgrmtPosDet.ManagerPersonWorkAgreement is not null
     then  PersWrkAgrmtPosDet.ManagerPersonWorkAgreement
   end                                       as ManagerPersonWorkAgreement,
      PersWrkAgrmtPosDet.PersonWorkAgrmtAuthznGrpg,
      @API.element.releaseState: #DEPRECATED
      @API.element.successor: '_ManagerPersonWorkAgreement_1'
      _ManagerPersonWorkAgreement,
      _ManagerPersonWorkAgreement_1
}
where
          PersWrkAgrmtPosDet.StartDate                         <= $parameters.P_KeyDate
  and     PersWrkAgrmtPosDet.EndDate                           >= $parameters.P_KeyDate
  and(
    (
          PersWrkAgrmtPosToMgrPosition.ManagerWrkAgrmtPosition is not null
      and PersWrkAgrmtPosToMgrPosition.ManagerWrkAgrmtPosition <> ''
      and PersonWrkAgrmtManager.PersonWorkAgreement            is not null
    )
    or(
          PersWrkAgrmtPosDet.ManagerPersonWorkAgreement        is not null
      and PersWrkAgrmtPosDet.ManagerPersonWorkAgreement        <> ''
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PERSWRKAGRMTPOSTOMGRPOSITION",
"P_O_PERSWRKAGRMTPOSDET"
],
"ASSOCIATED":
[
"I_PERSONWORKAGREEMENT",
"I_PERSONWORKAGREEMENT_1"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/