Forum Stats

  • 3,770,393 Users
  • 2,253,101 Discussions


How to extract the latest Previous Incumbent to W_WRKFC_EVT_MONTH_F in the current design

User_A0WPE Member Posts: 14 Red Ribbon
edited Mar 29, 2021 12:08PM in Data Integrator

OBIA with ODI 11g and PSFT 9.2 as source

Have a requirement to add the previous Incumbent in a vacant position in the Vacant Position OBIEE report.

The current customized design is - one ODI interface to populate the customized Position Event Month Fact table for filled position from W_HR_POSITION_D and W_WRKFC_EVT_MONTH_F with some other dimensions like W_JOB_D, W_INT_ORG_D,W_LOCATION_D to populate corresponding WID columns.

Another interface similar to the above one for vacant positions. But here the W_WRKFC_EVT_MONTH_F is not used any more and Assignment IDs are hard coded as 0~0.

The new requirement is to add previous Incumbent in a position. For filled positions its easy as we are using LAG function and selecting the previous Assignment ID in a position from W_WRKFC_EVT_MONTH_F based on the Effective_from_dt.

For vacant positions, now when I am trying to add the fact table W_WRKFC_EVT_MONTH_F it is breaking the current design even if I select the latest ones with max of Effective_from_dt and data is filtering out few names.

Can anyone please advise how can I extract the last one in a position when the position is vacant.

let me know of any further clarification