You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 13
Next »
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 | hcm.GlobalPersonPVOViewAll | select * from [hcm].[GlobalPersonPVOViewAll] where ASSIGNMENTPEOPRIMARYASSIGNMENTFLAG = 'Y' and ASSIGNMENTPEOEFFECTIVEENDDATE = '4712-12-31' and POSITIONPEOPOSITIONCODE is not null | | |
GEMS_PS_HR_ACCTG_LINE | Pending mapping in crosswalk | | | |
GEMS_PS_PER_ORG_ASGN | Pending mapping in crosswalk | | | |
GEMS_PS_SUPVSR_LVL_TBL | hcm.FlexBIPositionCustomerFlexVI | select distinct DESC_SUPERVISORY_LEVEL_, SUPERVISORY_LEVEL_ FROM [hcm].[FlexBIPositionCustomerFlexVI] order by SUPERVISORY_LEVEL_ | | |
GEMS_PS_U_PAYPERIOD_TBL | hcm.TimePeriodPVO | select TIMEPERIODPEOPERIODNAME, TIMEPERIODPEOSTARTDATE, TIMEPERIODPEOENDDATE, TIMEPERIODPEODEFAULTPAYDATE, PROCESSSUBDATE FROM [hcm].[TimePeriodPVO] where PROCESSSUBDATE IS NOT NULL order by TIMEPERIODPEOSTARTDATE | | |
GEMS_LABOR_POSITION | Position PVO Mapping to PeopleSoft Worker Mapping to PeopleSoft | select distinct position_dff.KEY_POSITIONS_, worker.PERSONDETAILSPEOPERSONNUMBER, worker.PERSONNAMEPEOFIRSTNAME, worker.PERSONNAMEPEOLASTNAME, worker.JOBPEOJOBFUNCTIONCODE from hcm.GlobalPersonPVOViewAll worker left join hcm.FlexBIPositionCustomerFlexVI position_dff on worker.POSITIONPEOPOSITIONID = position_dff.S_K_5000 where position_dff.KEY_POSITIONS_ = 'Y' order by JOBPEOJOBFUNCTIONCODE | | |
PS_Position_Data - Key Position | | | | |
PS_PER_POI_TRANS, PS_PER_POI_TYPE_TBL | Worker Mapping to PeopleSoft | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] WHERE ASSIGNMENTPEOASSIGNMENTTYPE='N' | | |
PS_U_PRIMARY_JOB_V | Worker Mapping to PeopleSoft | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] WHERE ASSIGNMENTPEOPRIMARYASSIGNMENTFLAG = 'Y' | | |
PS_EMPL_CLASS_TBL - SalaryPlan, EmployeeClass0, DescSalaryPlan and DescEmployeeClass | Worker Mapping to PeopleSoft | 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) from [hcm].[GlobalPersonPVOViewAll] GP left join [hcm].[LookupValuesPVO] LV on GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY =LV.LOOKUPCODE and LV.[LOOKUPTYPE] ='EMP_CAT'
or you can directly get the values from ita.EmployeeAssignment select [EmployeeClass0], [SalaryPlan],[DescSalaryPlan],[DescEmployeeClass] from [ita].[EmployeeAssignment] | | |