HRMS - Employee Salary Change Detail: Supervisor Hierarchy

Description
Categories: Discoverer
List of employees with details of the latest salary increment
Owner: SYSADMIN (System Administrator)
Identifier: HRIPOSAC
SELECT  o101847.ASSIGNMENT_ORGANIZATION_NAME as Probation_period_creation_date,o101879.CURRENCY as Last_Order_Number,o101879.EFFECTIVE_FROM_DATE as E124133,o101847.GRADE_NAME as E127385,o101800.GRADE_RATE_NAME as E127408,o101847.JOB_NAME as E129121,o101847.LOCATION_NAME as E131822,o101847.POSITION_NAME as E138577,o101890.SUBORDINATE_PERSON_NAME as E145676,o101890.SUPERVISOR_LEVEL as E145875,o101847.SUPERVISOR_NAME as E145883,o101890.SUPERVISOR_PERSON_NAME as E145905,TO_NUMBER(o101800.GRADE_MAXIMUM)*o101800.GRADE_ANNUALIZATION_FACTOR as C_1,TO_NUMBER(o101800.GRADE_MID_VALUE)*o101800.GRADE_ANNUALIZATION_FACTOR as C_2,TO_NUMBER(o101800.GRADE_MINIMUM)*o101800.GRADE_ANNUALIZATION_FACTOR as C_3,TO_NUMBER(( o101879.ANNUAL_SALARY-( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) ) )/( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) )) as C_5,HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) as C_4,o101879.ANNUAL_SALARY as E112729,o101847.EMPLOYEE_NUMBER as E124815,o101847.WORKING_HOURS_AMOUNT as E148817,o101847.WORKING_HOURS_FREQUENCY as E148827,GROUPING_ID(( TO_NUMBER(o101800.GRADE_MAXIMUM)*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBER(o101800.GRADE_MID_VALUE)*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBER(o101800.GRADE_MINIMUM)*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBER(( o101879.ANNUAL_SALARY-( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) ) )/( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) )) ),( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNT(o101879.PAY_PROPOSAL_ID) ),o101879.ANNUAL_SALARY,o101847.ASSIGNMENT_ORGANIZATION_NAME,o101879.CURRENCY,o101879.EFFECTIVE_FROM_DATE,o101847.EMPLOYEE_NUMBER,o101847.GRADE_NAME,o101800.GRADE_RATE_NAME,o101847.JOB_NAME,o101847.LOCATION_NAME,o101847.POSITION_NAME,o101890.SUBORDINATE_PERSON_NAME,o101890.SUPERVISOR_LEVEL,o101847.SUPERVISOR_NAME,o101890.SUPERVISOR_PERSON_NAME,o101847.WORKING_HOURS_AMOUNT,o101847.WORKING_HOURS_FREQUENCY) as GID
 FROM HRIFG_GRADE_RATES_BG o101800,
      HRFG_PERSON_ASSIGNMENT_HISTORY o101847,
      HRIFG_SAL_PRO o101879,
      HRIBG_SUPV_HRCHY_X o101890
 WHERE 1=1 AND ( (o101847.GRADE_ID = o101800.GRADE_ID AND o101847.SALARY_BASIS_ID = o101800.PAY_BASIS_ID)
   and o101847.ASSIGNMENT_ID = o101879.ASSIGNMENT_ID
   and o101890.SUBORDINATE_ASSIGNMENT_ID = o101847.ASSIGNMENT_ID)
   AND (o101879.LAST_CHANGE_DATE IS NOT NULL )
   AND (:supervisor_person_name IS NOT NULL )
   AND (o101879.EFFECTIVE_FROM_DATE <= :end_date)
   AND (o101879.EFFECTIVE_FROM_DATE >= :start_date)
   AND (o101890.SUPERVISOR_PERSON_NAME = :supervisor_person_name)
   AND (o101879.EFFECTIVE_FROM_DATE BETWEEN o101847.ASSIGNMENT_START_DATE AND o101847.ASSIGNMENT_END_DATE)
   AND (o101879.EFFECTIVE_FROM_DATE BETWEEN o101847.PERSON_START_DATE AND o101847.PERSON_END_DATE)
   AND (o101847.CURRENT_EMPLOYEE_FLAG = HR_BIS.BIS_DECODE_LOOKUP('YES_NO','Y'))
   AND (o101879.EFFECTIVE_FROM_DATE BETWEEN NVL(o101800.GRADE_RULE_START_DATE,o101879.EFFECTIVE_FROM_DATE) AND NVL(o101800.GRADE_RULE_END_DATE,o101879.EFFECTIVE_FROM_DATE+1))
 GROUP BY GROUPING SETS(( ( TO_NUMBERo101800.GRADE_MAXIMUM*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBERo101800.GRADE_MID_VALUE*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBERo101800.GRADE_MINIMUM*o101800.GRADE_ANNUALIZATION_FACTOR ),( TO_NUMBER(( o101879.ANNUAL_SALARY-( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNTo101879.PAY_PROPOSAL_ID ) )/( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNTo101879.PAY_PROPOSAL_ID )) ),( HR_DISC_CALCULATIONS.GET_PREV_SALARY_PRO_AMOUNTo101879.PAY_PROPOSAL_ID ),o101879.ANNUAL_SALARY,o101847.ASSIGNMENT_ORGANIZATION_NAME,o101879.CURRENCY,o101879.EFFECTIVE_FROM_DATE,o101847.EMPLOYEE_NUMBER,o101847.GRADE_NAME,o101800.GRADE_RATE_NAME,o101847.JOB_NAME,o101847.LOCATION_NAME,o101847.POSITION_NAME,o101890.SUBORDINATE_PERSON_NAME,o101890.SUPERVISOR_LEVEL,o101847.SUPERVISOR_NAME,o101890.SUPERVISOR_PERSON_NAME,o101847.WORKING_HOURS_AMOUNT,o101847.WORKING_HOURS_FREQUENCY ))
 HAVING (GROUP_ID()=0)
 ORDER BY GID DESC,o101890.SUBORDINATE_PERSON_NAME ASC
Parameter Name SQL text Validation
End Date
 
Date
Start Date
 
Date
Supervisor Person Name
 
LOV