DWHOUSE - Other GEMS Tables to Oracle HCM Equivalent

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_POSITION_DATA mapping to PeopleSoft

SELECT PERSONDETAILSPEOPERSONNUMBER, PERSONID, POSITIONPEOPOSITIONCODE, S_K_5000, S_K_5001, S_K_5002, POSITIONPEOPOSITIONID, ASSIGNMENTPEOEFFECTIVESTARTDATE, ASSIGNMENTPEOEFFECTIVEENDDATE, ASSIGNMENTPEOASSIGNMENTSEQUENCE, [POSITIONPEOEFFECTIVESTARTDATE]
FROM [hcm].[GlobalPersonPVOViewAll] gp
left outer join [hcm].[FlexBIPositionCustomerFlexVI] p on gp.POSITIONPEOPOSITIONID=p.S_K_5000 AND gp.[POSITIONPEOEFFECTIVESTARTDATE]=p.S_K_5001
WHERE 1=1 AND p.KEY_POSITIONS_='Y' AND p.S_K_5002='4712-12-31'
ORDER BY 1

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), GP.*
FROM [hcm].[GlobalPersonPVOViewAll] GP
inner 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], e.* from [ita].[EmployeeAssignment] e
WHERE [SalaryPlan] IS NOT NULL