HRMSi - Employee Separation (Organization Hierarchy) Template Analytics: by Year, Organization & Separation Category

Description
Categories: Discoverer
Analyses the reasons why people are leaving the enterprise
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWSPT
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,o101660.VALUE as E148305,MAX(o101614.START_DATE) as as108159_148260_NEW,MAX(o101660.ORDER_BY) as as108200_148305_NEW,SUM(( 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) )*NVL(o101680.LOSS_SEP_INDICATOR,0)) as C_1
 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_WAC_SEPCAT_X_V o101660,
      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 (o101660.ID = o101680.SEPARATION_CATEGORY_CODE)
   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 ( ( 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 ( ( o101630.VALUE = :grade_ OR :grade_ IS NULL  ) )
   AND ( ( o101631.VALUE = :job_ OR :job_ IS NULL  ) )
   AND (NVL(o101634.VALUE,'NA_EDW') = NVL(:job_category_,'NA_EDW'))
   AND ( ( o101635.VALUE = :geography_location_ OR :geography_location_ IS NULL  ) )
   AND ( ( o101629.VALUE = :geography_region_ OR :geography_region_ IS NULL  ) )
   AND ( ( o101628.VALUE = :geography_country_ OR :geography_country_ IS NULL  ) )
   AND ( ( o101627.VALUE = :geography_area_ OR :geography_area_ IS NULL  ) )
   AND (:workforce_measurement_type_ IS NOT NULL )
   AND (o101643.VALUE = :top_organization_)
   AND (o101640.VALUE = :organization_hierarchy_versio_)
   AND (NVL(o101680.LOSS_SEP_INDICATOR,0) = 1)
 GROUP BY o101614.END_DATE,o101614.START_DATE,o101614.VALUE,o101640.VALUE,o101642.VALUE,o101643.VALUE,o101660.VALUE
Parameter NameSQL textValidation
Organization Hierarchy Version
 
LOV
Top Organization
 
LOV
Job
 
LOV
Grade
 
LOV
Position
 
LOV
End Date
 
Date
Start Date
 
Date
Rollup Organizations
 
Job Category
 
Geography Location
 
Geography Region
 
Geography Country
 
Geography Area
 
Workforce Measurement Type
 
Number