HRMSi - Workforce Gain (Organization Hierarchy) Status Analytics: by Organization

Description
Categories: Discoverer
Investigates the different ways workforce is growing. For example people may be added to an organization as new hires or transfers
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWGNS
SELECT  o101640.VALUE as BG_Security_Flag_Code____Y_,o101642.VALUE as BG_Security_Flag_Code____Y_,o101643.VALUE as E148289,SUM(CASE WHEN o101679.GAIN_ORG_INDICATOR = 1 THEN o101679.GAIN_ORG_INDICATOR-HRI_OLTP_DISC_ORG.INDICATE_IN_ORGH(o101667.ORG_HIERARCHY_VERSION_ID,o101667.SUP_ORGANIZATION_ID,o101679.ORGANIZATION_FROM_ID) ELSE 0 END*o101679.ACTIVE_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101679.ASSIGNMENT_TO_ID),(o101679.BUSINESS_GROUP_TO_ID),:workforce_measurement_type,o101679.EVENT_DATE,(o101679.PRIMARY_TO_FLAG_CODE)) )) as C_4,SUM(o101679.GAIN_LTA_INDICATOR*o101679.ACTIVE_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101679.ASSIGNMENT_TO_ID),(o101679.BUSINESS_GROUP_TO_ID),:workforce_measurement_type,o101679.EVENT_DATE,(o101679.PRIMARY_TO_FLAG_CODE)) )) as C_3,SUM(o101679.GAIN_HIRE_INDICATOR*o101679.ACTIVE_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101679.ASSIGNMENT_TO_ID),(o101679.BUSINESS_GROUP_TO_ID),:workforce_measurement_type,o101679.EVENT_DATE,(o101679.PRIMARY_TO_FLAG_CODE)) )) as C_1,SUM(o101679.GAIN_ASG_INDICATOR*o101679.ACTIVE_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP((o101679.ASSIGNMENT_TO_ID),(o101679.BUSINESS_GROUP_TO_ID),:workforce_measurement_type,o101679.EVENT_DATE,(o101679.PRIMARY_TO_FLAG_CODE)) )) as C_2
 FROM HRI_CL_GRADE_V o101630,
      HRI_CL_JOB_V o101631,
      HRI_CL_JOB_CAT_JOB_V o101634,
      HRI_CL_ORG_HRCYVRSN_B_V o101640,
      HRI_CL_ORG_HR_N_V o101642,
      HRI_CL_ORG_HR_N_V o101643,
      HRI_CL_POSITION_V o101648,
      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,
      HRI_CS_ORGH_V o101667, HRI_MD_WAC_GAIN_V o101679
 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 o101667.SUB_ORGANIZATION_ID = o101679.ORGANIZATION_TO_ID
   and o101630.ID = o101679.GRADE_TO_ID
   and o101631.ID = o101679.JOB_TO_ID
   and o101634.ID = o101679.JOB_TO_ID
   and o101640.ID = o101667.ORG_HIERARCHY_VERSION_ID
   and o101642.ID = o101679.ORGANIZATION_TO_ID
   and o101643.ID = o101667.SUP_ORGANIZATION_ID
   and o101648.ID = o101679.POSITION_TO_ID
   and o101664.LOCATION_ID = o101679.LOCATION_TO_ID)
   AND o101630.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101631.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101642.HR_SECURITY_FLAG_CODE = 'Y'
   AND o101642.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 o101679.HR_SECURITY_FLAG_CODE = 'Y'
   AND o101679.BG_SECURITY_FLAG_CODE = 'Y'
   AND o101679.HR_SECURITY_FLAG_CODE = 'Y'
   AND o101679.BG_SECURITY_FLAG_CODE = 'Y'
   AND ( ( :rollup_organizations = HR_BIS.BIS_DECODE_LOOKUP('YES_NO_ALL','Y') OR o101667.SUB_ORG_RELATIVE_LEVEL = 0 ) )
   AND (o101679.EVENT_DATE >= :start_date AND o101679.EVENT_DATE <= :end_date)
   AND (:workforce_measurement_type IS NOT NULL )
   AND (NVL(o101634.VALUE,'NA_EDW') = NVL(:job_category,'NA_EDW'))
   AND ( (  ( o101679.GAIN_HIRE_INDICATOR = 1 OR o101679.GAIN_ASG_INDICATOR = 1 OR o101679.GAIN_LTA_INDICATOR = 1 OR o101679.GAIN_ASG_INDICATOR = 1 )  OR o101679.GAIN_ORG_INDICATOR = 1 ) )
 GROUP BY o101640.VALUE,o101642.VALUE,o101643.VALUE
Parameter Name SQL text Validation
Rollup Organizations
 
Position
o101648.VALUE = :position
LOV
Grade
o101630.VALUE = :grade
LOV
Job
o101631.VALUE = :job
LOV
Geography Region
o101629.VALUE = :geography_region
Geography Location
o101635.VALUE = :geography_location
Geography Country
o101628.VALUE = :geography_country
Geography Area
o101627.VALUE = :geography_area
End Date
 
Date
Start Date
 
Date
Workforce Measurement Type
 
Number
Job Category
 
Top Organization
o101643.VALUE = :top_organization
LOV
Organization Hierarchy Version
o101640.VALUE = :organization_hierarchy_version
LOV