Search This Blog

Friday, April 6, 2012

HRMS- SQL Query for Extracting Employee's Assignments


  SELECT  HOU.NAME ORGANIZATION,   PJT.NAME JOB,
  PGT.NAME  GRADE,   ppg.group_name "Group",
  HRL.LOCATION_CODE LOCATION,   PAAF.ASSIGNMENT_NUMBER,
  DECODE(PAAF.ASSIGNMENT_TYPE, 'E', HR_GENERAL.DECODE_LOOKUP('EMP_CAT',
  PAAF.EMPLOYMENT_CATEGORY) ,'C',  HR_GENERAL.DECODE_LOOKUP('CWK_ASG_CATEGORY',
  PAAF.EMPLOYMENT_CATEGORY)) ASSIGNMENT_CATEGORY,
  PAAF.EFFECTIVE_START_DATE,   PAAF.EFFECTIVE_END_DATE,
  PPB.NAME SALARY_BASIS,   PAPF_SUP.FULL_NAME SUPERVISOR,
  PAAF.NORMAL_HOURS WORKING_HOURS,   HL_FREQ.MEANING FREQUENCY,
  PAAF.TIME_NORMAL_FINISH NORMAL_TIME_FROM,
    PAAF.TIME_NORMAL_START NORMAL_TIME_TO,
    PAAF.TITLE BILLING_TITLE,     PAAF.PROJECT_TITLE,   DECODE(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,'....',NULL,
    gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5) Purchase_Default_Account
  FROM PER_ALL_ASSIGNMENTS_F PAAF,
  HR_OPERATING_UNITS HOU,
  HR.PER_JOBS_TL PJT,
  HR.PER_GRADES_TL PGT,
  HR_LOCATIONS_ALL HRL,
  PAY_PEOPLE_GROUPS PPG,
  PER_PAY_BASES PPB,
  PER_ALL_PEOPLE_F PAPF_SUP,
  HR_LOOKUPS HL_FREQ,
  gl_code_combinations gcc
  WHERE 1=1
  AND paaf.PERSON_ID= %Person_ID
  AND HOU.ORGANIZATION_ID = PAAF.ORGANIZATION_ID
  and pgt.grade_id(+) = paaf.grade_id
  AND PJT.JOB_ID = PAAF.JOB_ID
  AND PAAF.LOCATION_ID = HRL.LOCATION_ID
  AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
  AND PAAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID(+)
  AND PAAF.SUPERVISOR_ID= PAPF_SUP.PERSON_ID(+)
  AND PAAF.FREQUENCY = HL_FREQ.LOOKUP_CODE
  AND HL_FREQ.LOOKUP_TYPE = 'FREQUENCY'
  and paaf.default_code_comb_id = gcc.code_combination_id(+);

1 comment:

  1. pl add a individuals employye code to see his particular assignment.

    ReplyDelete