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_JOB - SalaryPlan and EmployeeClass0 | Worker Mapping to PeopleSoft | Select SUBSTRING(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY, 1, CHARINDEX('.', ASSIGNMENTPEOEMPLOYMENTCATEGORY) - 1) AS SAL_ADMIN_PLAN, SUBSTRING(GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY, CHARINDEX('.', ASSIGNMENTPEOEMPLOYMENTCATEGORY) + 1, LEN(ASSIGNMENTPEOEMPLOYMENTCATEGORY) ) AS EMPL_CLASS from [hcm].[GlobalPersonPVOViewAll] GP left join [hcm].[LookupValuesPVO] LV on GP.ASSIGNMENTPEOEMPLOYMENTCATEGORY =LV.LOOKUPCODE and LV.[LOOKUPTYPE] ='EMP_CAT' |