- 17.9K All Categories
- 3.3K Industry Applications
- 3.3K Intelligent Advisor
- 60 Insurance
- 534.5K On-Premises Infrastructure
- 137.7K Analytics Software
- 38.5K Application Development Software
- 5.3K Cloud Platform
- 109.1K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 70.8K Infrastructure Software
- 105.1K Integration
- 41.5K Security Software
How to extract the latest Previous Incumbent to W_WRKFC_EVT_MONTH_F in the current design
OBIA 184.108.40.206.1 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