HRMSi - Workforce Gain (Organization Hierarchy) Template Analytics: by Year and 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: HRIAWGNT
SELECT  o101614.END_DATE as E125328,o101614.START_DATE as E145051,o101614.VALUE as E148260,o101640.VALUE as E148286,o101642.VALUE as E148288,o101643.VALUE as E148289,MAX(o101614.START_DATE) as as108159_148260_NEW,( 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)) )) )+( 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)) )) )+( 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)) )) )+( 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_5,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 FII_TIME_YEAR_V o101614,
      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 ( (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 (o101614.START_DATE <= o101679.EVENT_DATE AND o101614.END_DATE >= o101679.EVENT_DATE)
   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 (o101614.START_DATE >= :start_date_ AND o101614.END_DATE <= :end_date_)
   AND ( ( :rollup_organizations_ = HR_BIS.BIS_DECODE_LOOKUP('YES_NO_ALL','Y') OR o101667.SUB_ORG_RELATIVE_LEVEL = 0 ) )
   AND ( ( o101648.VALUE = :position_ OR :position_ IS NULL  ) )
   AND ( ( o101630.VALUE = :grade_ OR :grade_ IS NULL  ) )
   AND ( ( o101631.VALUE = :job_ OR :job_ IS NULL  ) )
   AND ( ( o101629.VALUE = :geography_region_ OR :geography_region_ IS NULL  ) )
   AND ( ( o101635.VALUE = :geography_location_ OR :geography_location_ IS NULL  ) )
   AND ( ( o101628.VALUE = :geography_country_ OR :geography_country_ IS NULL  ) )
   AND ( ( o101627.VALUE = :geography_area_ OR :geography_area_ IS NULL  ) )
   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 (o101643.VALUE = :top_organization_)
   AND (o101640.VALUE = :organization_hierarchy_versio_)
   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 o101614.END_DATE,o101614.START_DATE,o101614.VALUE,o101640.VALUE,o101642.VALUE,o101643.VALUE
Parameter Name SQL text Validation
Organization Hierarchy Version
 
LOV
Top Organization
 
LOV
Job
 
LOV
Grade
 
LOV
Position
 
LOV
Job Category
 
Workforce Measurement Type
 
Number
Start Date
 
Date
End Date
 
Date
Geography Area
 
Geography Country
 
Geography Location
 
Geography Region
 
Rollup Organizations