HRMSi - Workforce Loss (Organization Hierarchy) Template Analytics: by Year and Organization

Description
Categories: Discoverer
Investigates the different ways workforce is being lost. For example people may leave an organization by separating, transferring or going on long term leave.
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWLST
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(o101680.ACTIVE_INDICATOR*NVL(o101680.LOSS_ASG_INDICATOR,0)*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) )+( SUM(o101680.ACTIVE_INDICATOR*o101680.LOSS_LTA_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) )+( SUM(o101680.ACTIVE_INDICATOR*NVL(o101680.LOSS_SEP_INDICATOR,0)*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) )+( SUM(o101680.ACTIVE_INDICATOR*CASE WHEN NVL(o101680.LOSS_ORG_INDICATOR,0) = 1 THEN ( CASE WHEN HRI_OLTP_DISC_ORG.INDICATE_IN_ORGH(o101667.ORG_HIERARCHY_VERSION_ID,o101667.SUP_ORGANIZATION_ID,o101680.ORGANIZATION_TO_ID) = 1 THEN 0 ELSE 1 END ) ELSE 0 END*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) ) ) as C_5,SUM(o101680.ACTIVE_INDICATOR*CASE WHEN NVL(o101680.LOSS_ORG_INDICATOR,0) = 1 THEN ( CASE WHEN HRI_OLTP_DISC_ORG.INDICATE_IN_ORGH(o101667.ORG_HIERARCHY_VERSION_ID,o101667.SUP_ORGANIZATION_ID,o101680.ORGANIZATION_TO_ID) = 1 THEN 0 ELSE 1 END ) ELSE 0 END*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) as C_4,SUM(o101680.ACTIVE_INDICATOR*NVL(o101680.LOSS_SEP_INDICATOR,0)*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) as C_1,SUM(o101680.ACTIVE_INDICATOR*o101680.LOSS_LTA_INDICATOR*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_FLAG_CODE) )) as C_3,SUM(o101680.ACTIVE_INDICATOR*NVL(o101680.LOSS_ASG_INDICATOR,0)*( HRI_OLTP_DISC_WRKFRC.CALC_ABV_LOOKUP(o101680.ASSIGNMENT_FROM_ID,o101680.BUSINESS_GROUP_FROM_ID,:workforce_measurement_type_,o101680.MEASURE_CALC_DATE,o101680.PRIMARY_FROM_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_LOSS_V o101680
 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 = o101680.ORGANIZATION_FROM_ID)
   and (o101614.ID = TO_NUMBER(TO_CHAR(o101680.EVENT_DATE,'YYYY')))
   and (o101630.ID = o101680.GRADE_FROM_ID)
   and (o101631.ID = o101680.JOB_FROM_ID)
   and (o101634.ID = o101680.JOB_FROM_ID)
   and (o101640.ID = o101667.ORG_HIERARCHY_VERSION_ID)
   and (o101642.ID = o101680.ORGANIZATION_FROM_ID)
   and (o101643.ID = o101667.SUP_ORGANIZATION_ID)
   and (o101648.ID = o101680.POSITION_FROM_ID)
   and (o101664.LOCATION_ID = o101680.LOCATION_FROM_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 (o101680.HR_SECURITY_FLAG_CODE = 'Y')
   AND (o101680.BG_SECURITY_FLAG_CODE = 'Y')
   AND (o101680.HR_SECURITY_FLAG_CODE = 'Y')
   AND (o101680.BG_SECURITY_FLAG_CODE = 'Y')
   AND ( (  ( NVL(o101680.LOSS_SEP_INDICATOR,0) = 1 OR NVL(o101680.LOSS_ASG_INDICATOR,0) = 1 OR o101680.LOSS_LTA_INDICATOR = 1 )  OR NVL(o101680.LOSS_ORG_INDICATOR,0) = 1 ) )
   AND (o101614.START_DATE >= :start_date_ AND o101614.END_DATE <= :end_date_)
   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 ( ( o101667.SUB_ORG_RELATIVE_LEVEL = 0 OR :rollup_organizations_ = HR_BIS.BIS_DECODE_LOOKUP('YES_NO_ALL','Y') ) )
   AND ( ( o101648.VALUE = :position_ OR :position_ IS NULL  ) )
   AND ( ( o101634.VALUE = :job_category_ OR :job_category_ IS NOT NULL  ) )
   AND ( ( o101630.VALUE = :grade_ OR :grade_ IS NULL  ) )
   AND ( ( o101631.VALUE = :job_ OR :job_ IS NULL  ) )
   AND (:workforce_measurement_type_ IS NOT NULL )
   AND (o101643.VALUE = :top_organization_)
   AND (o101640.VALUE = :organization_hierarchy_versio_)
 GROUP BY o101614.END_DATE,o101614.START_DATE,o101614.VALUE,o101640.VALUE,o101642.VALUE,o101643.VALUE
Parameter NameSQL textValidation
Organization Hierarchy Version
 
LOV
Top Organization
 
LOV
Job
 
LOV
Grade
 
LOV
Position
 
LOV
Job Category
 
LOV
End Date
 
Date
Start Date
 
Date
Geography Region
 
Geography Location
 
Geography Country
 
Geography Area
 
Rollup Organizations
 
Workforce Measurement Type
 
Number