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