SELECT /*+ LEADING(SAL.DAY) */
SAL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
,CASE WHEN SAL.EFFECTIVE_START_DATE = TRUNC(SYSDATE) THEN HR_GENERAL.END_OF_TIME
ELSE SAL.EFFECTIVE_END_DATE
END EFFECTIVE_END_DATE
,SAL.JOB_ID JOB_ID
,SAL.COUNTRY COUNTRY
,SAL.PAY_BASIS_ID PAY_BASIS_ID
,SUM(SAL.HEADCOUNT) TOTAL_HEADCOUNT
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SAL.SALARY) MED_ANL_SLRY
,SAL.CURRENCY_CODE ANL_SLRY_CURRENCY
,ROUND(AVG((SAL.SALARY/CASE WHEN SAL.GRADE_MID_VALUE = 0 THEN 1 ELSE SAL.GRADE_MID_VALUE END)*100), 2) COMPARATIO
,ROUND(AVG(((SAL.SALARY - SAL.GRADE_MIN)/CASE WHEN (SAL.GRADE_MAX-SAL.GRADE_MIN) = 0 THEN 1 ELSE (SAL.GRADE_MAX-SAL.GRADE_MIN) END)*100), 2) RANGE_POSITION
,TRUNC(AVG(
CASE
WHEN SAL.SALARY <= SAL.GRADE_MIN THEN 0
WHEN SAL.SALARY >= SAL.GRADE_MAX THEN 5
WHEN SAL.SALARY <= (SAL.GRADE_MID_VALUE + SAL.GRADE_MIN)/2 THEN 1
WHEN SAL.SALARY < SAL.GRADE_MID_VALUE THEN 2
WHEN SAL.SALARY >= (SAL.GRADE_MID_VALUE + SAL.GRADE_MAX)/2 THEN 4
WHEN SAL.SALARY >= SAL.GRADE_MID_VALUE THEN 3
END
), 1) QUARTILE
FROM (
SELECT DAY.EFFECTIVE_DATE EFFECTIVE_START_DATE
, ADD_MONTHS(DAY.EFFECTIVE_DATE, 12) -1 EFFECTIVE_END_DATE
, ASG.BUSINESS_GROUP_ID
, ASG.ASSIGNMENT_ID
, ASG.JOB_ID JOB_ID
, LOC.COUNTRY COUNTRY
, ASG.PAY_BASIS_ID
, ASG.GRADE_ID
, PGR.RATE_ID
, HRI_BPL_ABV.CALC_ABV(
ASG.ASSIGNMENT_ID
,ASG.BUSINESS_GROUP_ID
,'HEAD'
,PRO.CHANGE_DATE) HEADCOUNT
,CASE WHEN PPB.PAY_BASIS = 'HOURLY' THEN (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)
ELSE (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)/CASE WHEN PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID ,DAY.EFFECTIVE_DATE) = 0 THEN 1 ELSE PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID ,DAY.EFFECTIVE_DATE) END
END SALARY
,PGR.CURRENCY_CODE GRADE_CURRENCY
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE != PET.INPUT_CURRENCY_CODE
THEN
PER_SALADMIN_UTILITY.GET_CURRENCY_RATE(
PGR.CURRENCY_CODE,
PET.INPUT_CURRENCY_CODE,
DAY.EFFECTIVE_DATE,
ASG.BUSINESS_GROUP_ID) * PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
ELSE PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
END GRADE_MIN
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE != PET.INPUT_CURRENCY_CODE
THEN
PER_SALADMIN_UTILITY.GET_CURRENCY_RATE(
PGR.CURRENCY_CODE,
PET.INPUT_CURRENCY_CODE,
DAY.EFFECTIVE_DATE,
ASG.BUSINESS_GROUP_ID) * PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
ELSE PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
END GRADE_MAX
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE IS NOT NULL
AND PGR.CURRENCY_CODE != PET.INPUT_CURRENCY_CODE
THEN
PER_SALADMIN_UTILITY.GET_CURRENCY_RATE(
PGR.CURRENCY_CODE,
PET.INPUT_CURRENCY_CODE,
DAY.EFFECTIVE_DATE,
ASG.BUSINESS_GROUP_ID) * PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR
ELSE PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR
END GRADE_MID_VALUE
,PET.INPUT_CURRENCY_CODE CURRENCY_CODE
FROM PER_ALL_ASSIGNMENTS_F ASG
,PER_PAY_BASES PPB
,PER_PAY_PROPOSALS PRO
,PAY_INPUT_VALUES_F PIV
,PAY_ELEMENT_TYPES_F PET
,HR_LOCATIONS_ALL LOC
,PAY_GRADE_RULES_F PGR
,( SELECT TRUNC(SYSDATE) EFFECTIVE_DATE FROM DUAL
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE), -12) EFFECTIVE_DATE FROM DUAL
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE), -24) EFFECTIVE_DATE FROM DUAL
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE), -36) EFFECTIVE_DATE FROM DUAL
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE), -48) EFFECTIVE_DATE FROM DUAL
) DAY
WHERE DAY.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND ASG.JOB_ID IS NOT NULL
AND ASG.GRADE_ID = PGR.GRADE_OR_SPINAL_POINT_ID
AND PPB.RATE_ID = PGR.RATE_ID
AND ASG.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE = (SELECT MAX(CHANGE_DATE)
FROM PER_PAY_PROPOSALS PRO2
WHERE PRO2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO2.CHANGE_DATE <= DAY.EFFECTIVE_DATE
AND PRO2.APPROVED = 'Y')
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND LOC.LOCATION_ID = ASG.LOCATION_ID
AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE BETWEEN PGR.EFFECTIVE_START_DATE AND PGR.EFFECTIVE_END_DATE
) SAL
GROUP BY
SAL.EFFECTIVE_START_DATE
,SAL.EFFECTIVE_END_DATE
,SAL.JOB_ID
,SAL.COUNTRY
,SAL.PAY_BASIS_ID
,SAL.CURRENCY_CODE
[Home] [Help]