DWHOUSE - Other GEMS Tables to Oracle HCM Equivalent
The following contains a list of GEMS tables from the data warehouse and their closest equivalent match to an Oracle HCM table. These can each be joined to other Oracle HCM tables as required for reporting purposes.
DWHOUSE table | Oracle HCM Table Mapping | SQL statement |
---|---|---|
GEMS_PS_CURRENT_PRIMARY_JOB | select * from [hcm].[GlobalPersonPVOViewAll] | |
GEMS_PS_HR_ACCTG_LINE | Pending mapping in crosswalk |
|
GEMS_PS_PER_ORG_ASGN | Pending mapping in crosswalk |
|
GEMS_PS_SUPVSR_LVL_TBL | select distinct DESC_SUPERVISORY_LEVEL_, SUPERVISORY_LEVEL_ | |
GEMS_PS_U_PAYPERIOD_TBL | select TIMEPERIODPEOPERIODNAME, TIMEPERIODPEOSTARTDATE, TIMEPERIODPEOENDDATE, TIMEPERIODPEODEFAULTPAYDATE, PROCESSSUBDATE | |
GEMS_LABOR_POSITION | select distinct position_dff.KEY_POSITIONS_, worker.PERSONDETAILSPEOPERSONNUMBER, worker.PERSONNAMEPEOFIRSTNAME, worker.PERSONNAMEPEOLASTNAME, worker.JOBPEOJOBFUNCTIONCODE | |
PS_Position_Data - Key Position | SELECT PERSONDETAILSPEOPERSONNUMBER, PERSONID, POSITIONPEOPOSITIONCODE, S_K_5000, S_K_5001, S_K_5002, POSITIONPEOPOSITIONID, ASSIGNMENTPEOEFFECTIVESTARTDATE, ASSIGNMENTPEOEFFECTIVEENDDATE, ASSIGNMENTPEOASSIGNMENTSEQUENCE, [POSITIONPEOEFFECTIVESTARTDATE] | |
PS_PER_POI_TRANS, PS_PER_POI_TYPE_TBL | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] | |
PS_U_PRIMARY_JOB_V | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] | |
PS_EMPL_CLASS_TBL - SalaryPlan, EmployeeClass0, DescSalaryPlan and DescEmployeeClass | SELECT DescEmployeeClass=SUBSTRING(LV.MEANING, (CASE WHEN CHARINDEX(';', LV.MEANING)=0 THEN 1 ELSE CHARINDEX(';', LV.MEANING)+1 END), LEN(LV.MEANING)), EmployeeClass0=SUBSTRING(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY, (CASE WHEN CHARINDEX('.', GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)=0 THEN 1 ELSE CHARINDEX('.', GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)+1 END), LEN(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)), DescSalaryPlan=SUBSTRING(LV.MEANING, 1, CASE WHEN CHARINDEX(';', LV.MEANING)=0 THEN LEN(LV.MEANING)ELSE CHARINDEX(';', LV.MEANING)-1 END), SalaryPlan=SUBSTRING(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY, 1, CASE WHEN CHARINDEX('.', GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)=0 THEN LEN(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)ELSE CHARINDEX('.', GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY)-1 END), GP.*
Or you can directly get the values from ita.EmployeeAssignment select [EmployeeClass0], [SalaryPlan],[DescSalaryPlan],[DescEmployeeClass], e.* from [ita].[EmployeeAssignment] e |