Modules Involved : Human Resource Management System (HRMS)
Purpose : To get the Employee's assignment Information
Description : We need to pass the employee full_name. It retrieves Employee's Business Group,Hire Date,Job,Dept
,Division,Manager etc..
Note: Department and Division is vary from client to client depends on the company segments.
Here Valueset Name for the Department is hard-coded.
****************************************************************** */
SELECT PAPF.PERSON_ID
, PAPF.FULL_NAME
, PBG.NAME
, PAPF.ORIGINAL_DATE_OF_HIRE HIRE_DATE
, PPOS.DATE_START LATEST_START_DATE
, PPOS.ADJUSTED_SVC_DATE
, SUBSTR(PPG.GROUP_NAME,1,INSTR(PPG.GROUP_NAME,'.')-1) DIVISION
, JOB.NAME "JOB TITLE"
, GCC.SEGMENT2 "DEPT NUMBER"
, FVT.DESCRIPTION "DEPT NAME"
, SUP.FULL_NAME MANAGER
, PG.NAME GRADE
, LOC.LOCATION_CODE "WORK LOCATION"
FROM PER_ALL_PEOPLE_F PAPF
, PER_ALL_PEOPLE_F SUP
, PER_BUSINESS_GROUPS PBG
, PER_PERIODS_OF_SERVICE PPOS
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_JOBS_TL JOB
, PER_GRADES_TL PG
, PAY_PEOPLE_GROUPS PPG
, HR_LOCATIONS_ALL_TL LOC
, GL_CODE_COMBINATIONS GCC
, FND_FLEX_VALUE_SETS FVS
, FND_FLEX_VALUES FV
, FND_FLEX_VALUES_TL FVT
where 1=1
and PAPF.FULL_NAME = :EMPLOYEE_FULL_NAME
and PAPF.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
and PAAF.PERSON_ID = PAPF.PERSON_ID
and PAAF.ASSIGNMENT_TYPE = 'E'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(SUP.EFFECTIVE_START_DATE) AND TRUNC(SUP.EFFECTIVE_END_DATE)
AND PAAF.JOB_ID = JOB.JOB_ID
AND PAAF.SUPERVISOR_ID = SUP.PERSON_ID
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID(+)
AND PAAF.LOCATION_ID = LOC.LOCATION_ID(+)
AND GCC.CODE_COMBINATION_ID = PAAF.DEFAULT_CODE_COMB_ID
AND FVS.FLEX_VALUE_SET_NAME = 'SSFT Cost Center'
AND FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
and FV.FLEX_VALUE = GCC.SEGMENT2
Purpose : To get the Employee's assignment Information
Description : We need to pass the employee full_name. It retrieves Employee's Business Group,Hire Date,Job,Dept
,Division,Manager etc..
Note: Department and Division is vary from client to client depends on the company segments.
Here Valueset Name for the Department is hard-coded.
****************************************************************** */
SELECT PAPF.PERSON_ID
, PAPF.FULL_NAME
, PBG.NAME
, PAPF.ORIGINAL_DATE_OF_HIRE HIRE_DATE
, PPOS.DATE_START LATEST_START_DATE
, PPOS.ADJUSTED_SVC_DATE
, SUBSTR(PPG.GROUP_NAME,1,INSTR(PPG.GROUP_NAME,'.')-1) DIVISION
, JOB.NAME "JOB TITLE"
, GCC.SEGMENT2 "DEPT NUMBER"
, FVT.DESCRIPTION "DEPT NAME"
, SUP.FULL_NAME MANAGER
, PG.NAME GRADE
, LOC.LOCATION_CODE "WORK LOCATION"
FROM PER_ALL_PEOPLE_F PAPF
, PER_ALL_PEOPLE_F SUP
, PER_BUSINESS_GROUPS PBG
, PER_PERIODS_OF_SERVICE PPOS
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_JOBS_TL JOB
, PER_GRADES_TL PG
, PAY_PEOPLE_GROUPS PPG
, HR_LOCATIONS_ALL_TL LOC
, GL_CODE_COMBINATIONS GCC
, FND_FLEX_VALUE_SETS FVS
, FND_FLEX_VALUES FV
, FND_FLEX_VALUES_TL FVT
where 1=1
and PAPF.FULL_NAME = :EMPLOYEE_FULL_NAME
and PAPF.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
and PAAF.PERSON_ID = PAPF.PERSON_ID
and PAAF.ASSIGNMENT_TYPE = 'E'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(SUP.EFFECTIVE_START_DATE) AND TRUNC(SUP.EFFECTIVE_END_DATE)
AND PAAF.JOB_ID = JOB.JOB_ID
AND PAAF.SUPERVISOR_ID = SUP.PERSON_ID
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID(+)
AND PAAF.LOCATION_ID = LOC.LOCATION_ID(+)
AND GCC.CODE_COMBINATION_ID = PAAF.DEFAULT_CODE_COMB_ID
AND FVS.FLEX_VALUE_SET_NAME = 'SSFT Cost Center'
AND FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
and FV.FLEX_VALUE = GCC.SEGMENT2
order by 3
No comments:
Post a Comment