HRMSi - Workforce Count Change by Job Category (Organization Hierarchy) Status Analytics: by Job

Description
Categories: Discoverer
Compares how workforce values in different job categories change over time
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWJCS
SELECT  o101631.VALUE as E148277,o101633.VALUE as E148279,o101640.VALUE as BG_Security_Flag_Code____Y_,o101643.VALUE as E148289,MAX(o101631.ORDER_BY) as Application_Id,MAX(o101633.ORDER_BY) as Application_Id,TO_NUMBER(DECODE(NVL(( SUM(CASE WHEN :start_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:start_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ),0),0,to_number(NULL),( NVL(( SUM(CASE WHEN :end_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:end_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ),0)-NVL(( SUM(CASE WHEN :start_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:start_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ),0) )/( SUM(CASE WHEN :start_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:start_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ))) as C_4,NVL(( SUM(CASE WHEN :end_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:end_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ),0)-NVL(( SUM(CASE WHEN :start_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:start_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) ),0) as C_3,SUM(CASE WHEN :end_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:end_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) as C_1,SUM(CASE WHEN :start_date BETWEEN o101681.EFFECTIVE_START_DATE AND o101681.EFFECTIVE_END_DATE THEN ( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101681.ASSIGNMENT_ID),(o101681.BUSINESS_GROUP_ID),:workforce_measurement_type,:start_date,(o101681.ASG_PRIMARY_FLAG_CODE)) ) ELSE 0 END) as C_2
 FROM HRI_CL_GRADE_V o101630,
      HRI_CL_JOB_V o101631,
      HRI_CL_JOB_CAT_V o101633,
      HRI_CL_ORG_HRCYVRSN_B_V o101640,
      HRI_CL_ORG_HR_N_V o101643,
      HRI_CL_POSITION_V o101648,
      HRI_CS_JOBCAT_V o101665,
      HRI_CS_ORGH_V o101667,
      HRI_MB_WMV_V o101681,
      HRI_CL_GEO_AREA_V o101627, HRI_CL_GEO_COUNTRY_V o101628, HRI_CL_GEO_REGION_V o101629, HRI_CL_LOC_HR_V o101635, HRI_CS_GEO_LOCHR_CT o101664
 WHERE 1=1 AND ( o101627.ID = o101664.AREA_CODE
   and o101628.COUNTRY_CODE = o101664.COUNTRY_CODE
   and o101629.REGION_CODE = o101664.REGION_CODE
   and o101635.ID = o101664.LOCATION_ID
   and o101630.ID = o101681.GRADE_ID
   and o101631.ID = o101681.JOB_ID
   and o101633.ID = o101665.JOB_CAT_CODE
   and o101640.ID = o101667.ORG_HIERARCHY_VERSION_ID
   and o101643.ID = o101667.SUP_ORGANIZATION_ID
   and o101648.ID = o101681.POSITION_ID
   and o101665.JOB_ID = o101681.JOB_ID
   and o101667.SUB_ORGANIZATION_ID = o101681.ORGANIZATION_ID
   and o101664.LOCATION_ID = o101681.LOCATION_ID)
   AND o101630.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101631.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101643.HR_SECURITY_FLAG_CODE = 'Y'
   AND o101643.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101648.HR_SECURITY_FLAG_CODE = 'Y'
   AND o101648.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101635.BG_SECURITY_FLAG_CODE = 'Y'
   AND (o101681.EFFECTIVE_END_DATE >= :start_date AND o101681.EFFECTIVE_START_DATE <= :end_date)
   AND (:workforce_measurement_type IS NOT NULL )
   AND ( ( :rollup_organizations = HR_BIS.BIS_DECODE_LOOKUP('YES_NO','Y') OR o101667.SUB_ORG_RELATIVE_LEVEL = 0 ) )
 GROUP BY o101631.VALUE,o101633.VALUE,o101640.VALUE,o101643.VALUE
Parameter Name SQL text Validation
End_Date
 
Date
Start_Date
 
Date
Workforce Measurement Type
 
Number
Job Category
o101633.VALUE = :job_category
LOV
Geography Region
o101629.VALUE = :geography_region
LOV
Geography Country
o101628.VALUE = :geography_country
Geography Area
o101627.VALUE = :geography_area
Rollup Organizations
 
Position
o101648.VALUE = :position
LOV
Grade
o101630.VALUE = :grade
LOV
Job
o101631.VALUE = :job
LOV
Location
o101635.VALUE = :location
Top Organization
o101643.VALUE = :top_organization
LOV
Organization Hierarchy
o101640.VALUE = :organization_hierarchy
LOV