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_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]