MATERIALIZED VIEW: APPS.HR_MBS_WRKFC_JCX_SUPH_MV

Source

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,
  sal.supervisor_id supervisor_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,
     SUPH.SUP_PERSON_ID SUPERVISOR_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 ,
    HRI_CS_SUPH SUPH ,      
        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 SUPH.SUB_PERSON_ID = ASG.PERSON_ID
    AND SUPH.SUB_RELATIVE_LEVEL <> 0
    AND DAY.EFFECTIVE_DATE BETWEEN SUPH.EFFECTIVE_START_DATE AND SUPH.EFFECTIVE_END_DATE
    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 CHANGE_DATE
                             FROM PER_PAY_PROPOSALS PRO2
                WHERE PRO2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
                  AND DAY.EFFECTIVE_DATE BETWEEN PRO2.CHANGE_DATE AND PRO2.DATE_TO
                  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.SUPERVISOR_ID,
       SAL.JOB_ID ,
       SAL.COUNTRY ,
       SAL.PAY_BASIS_ID,
       SAL.CURRENCY_CODE