How To Use BICC Mappings X-Walk
- Jefferson Russell
The BICC Mappings X-Walk is a mapping of tables and fields from the source GEMS to the equivalent table and fields within Oracle HCM and to the HRLakehouse. The mapping is provided as a method for users to find a table or field that the users are familiar with from GEMS and then lookup the new location of that table and field within Oracle HCM and to the HRLakehouse.
Overview of Layout of Excel
There are several tabs at the bottom of the screen. Each tab represents a logical grouping provided by the Oracle HCM or Analytics team.
Given the above screen shot of the Worker tab the columns are as follows:
PeopleSoft Recname - GEMS Table
PeopleSoft Fieldname - GEMS Field
BICC BUSINESS OBJECT - Alternative tab in the X-Walk
BICC PVO - View that might be available in HR Lakehouse
BICC PVE - Field that is available in the view
Cloud Database Table - The table that the field is from in Oracle HCM
Cloud Database Column - Oracle HCM column in the Cloud Database Table
Notes - Can be important showing any comments, or primary keys
Example Usage
Given the above screen shot of the Worker tab if the user were looking for PS_JOB.EMPL_STATUS - the equivalent field is GlobalPersonPVOViewAll.AssginmentStatusTypesTLPEOUserStatus
When connected via sql to the HRLakehouse the sql would be - SELECT [AssginmentStatusTypesTLPEOUserStatus] FROM [hcm].[GlobalPersonPVOViewAll]
How to find the GEMS Table or Field
The easiest method to find a specific GEMS field is to use Find and Replace option in Excel. When you first try to find text in Excel it will default to the sheet within Search options. Make sure to open the search options.
And change it to within Workbook before you search.
Worker Tab
Position
itaEmployeeAssignment
PS_PAY_ERN_DIST
Notice that POSITION_NBR exist in 4 sheets. (Worker, Position, ita.EmployeeAssignment, PS_PAY_ERN_DIST). However, if you follow through to those pages you will see that the field POSITION_NBR comes from 2 gems tables, 1 fast table, and maps to different destination sources within the HRLakehouse.
Tab | Source | HRLakehoouse |
|---|---|---|
Worker | PS_JOB.POSTION_NBR | hcm.GlobalPersonPVOViewAll.PositionPEOPositionCode |
Position | PS_POSITION_DATA.POSITION_NBR | hcm.PositionPVOViewAll.PositionPEOPositionCode |
ita.EmployeeAssignment | View that references GlobalPersonPVOViewAll.PositionPEOPositionCode | ita.EmployeeAssignment.PositionCode |
PS_PAY_ERN_DIST | PS_PAY_ERN_DIST.POSTION_NBR | fast.PS_PAY_ERN_DIST.POSTION_NBR |
ita.EmployeeInformation
The ITA.EmployeeIInformation view is a consolidated view created by the Analytics team. It is a prebuilt view that joins the several Oracle HCM tables for the end users in order to create a single repository for employee information. This view has friendlier names for user consumption and understanding. From the Excel tab ita.EmployeeInformation you can see the columns available for use in this view.
The previous GEMS tables of PS_NAMES, PS_ADDRESS, PS_JOB, etc. and the HCM tables of GlobalPersonPVOViewAll, ExternalIdentifierPVO, etc. have now been pre-joined to provide this consolidated view.
ita.EmployeeAssignment
The ITA.EmployeeAssignment view is a consolidated view created by the Analytics team. It is a prebuilt view that joins the several Oracle HCM tables for the end users in order to create a single repository for employee assignments. This view has friendlier names for user consumption and understanding. From the Excel tab ita.EmployeeAssignment you can see the columns available for use in this view.
The previous GEMS tables of PS_JOB, PS_EG_TENURE_DATA, etc. and the HCM tables of GlobalPersonPVOViewAll, JOBPVOViewAll, etc. have now been pre-joined to provide this consolidated view.
Be aware that there are several USF employees that have multiple assignments. So this view will not necessarily return one person, but one assignment per person.