To get the Employee's assignment Information

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

order by 3