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(+);

3 comments:

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

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Interesting post! Thanks for writing it. What's wrong with this kind of post exactly? It follows your previous guideline for post length as well as clarity..
    HRMS Software in Dubai
    HRMS Software Dubai
    HRMS Software in UAE
    HRMS Software
    HR Management Software
    Payroll Software

    ReplyDelete