...
DWHOUSE table | Oracle HCM Table Mapping | SQL statement |
---|---|---|
GEMS_PS_CURRENT_PRIMARY_JOB | select * from [hcm].[GlobalPersonPVOViewAll] | |
GEMS_PS_HR_ACCTG_LINE | Pending mapping in crosswalk | |
GEMS_PS_PER_ORG_ASGN | Pending mapping in crosswalk | |
GEMS_PS_SUPVSR_LVL_TBL | select distinct DESC_SUPERVISORY_LEVEL_, SUPERVISORY_LEVEL_ | |
GEMS_PS_U_PAYPERIOD_TBL | select TIMEPERIODPEOPERIODNAME, TIMEPERIODPEOSTARTDATE, TIMEPERIODPEOENDDATE, TIMEPERIODPEODEFAULTPAYDATE, PROCESSSUBDATE | |
GEMS_LABOR_POSITION | select distinct position_dff.KEY_POSITIONS_, worker.PERSONDETAILSPEOPERSONNUMBER, worker.PERSONNAMEPEOFIRSTNAME, worker.PERSONNAMEPEOLASTNAME, worker.JOBPEOJOBFUNCTIONCODE | |
PS_Position_Data - Key Position | SELECT PERSONDETAILSPEOPERSONNUMBER, PERSONID, POSITIONPEOPOSITIONCODE, S_K_5000, S_K_5001, S_K_5002, POSITIONPEOPOSITIONID, ASSIGNMENTPEOEFFECTIVESTARTDATE, ASSIGNMENTPEOEFFECTIVEENDDATE, ASSIGNMENTPEOASSIGNMENTSEQUENCE, [POSITIONPEOEFFECTIVESTARTDATE] | |
PS_PER_POI_TRANS, PS_PER_POI_TYPE_TBL | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] | |
PS_U_PRIMARY_JOB_V | SELECT * FROM [hcm].[GlobalPersonPVOViewAll] | |
PS_EMPL_CLASS_TBL - SalaryPlan, EmployeeClass0, DescSalaryPlan and DescEmployeeClass | 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.* Or you can directly get the values from ita.EmployeeAssignment select [EmployeeClass0], [SalaryPlan],[DescSalaryPlan],[DescEmployeeClass], e.* from [ita].[EmployeeAssignment] e |