HRMSi - Employee Separation by Length of Work (Organization Hierarchy) Status Analytics: by Organization

Description
Categories: Discoverer
Investigates the relationship between the amount of time people have been with the enterprise and various categories (e.g. Organization, Time, Separation Reason)
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWSLS
SELECT  o101636.VALUE as BG_Security_Flag_Code____Y_,o101640.VALUE as BG_Security_Flag_Code____Y_,o101642.VALUE as BG_Security_Flag_Code____Y_,o101643.VALUE as E148289,MAX(o101636.BAND_MIN_VALUE) as BG_Security_Flag_Code____Y_,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 HRI_CL_GRADE_V o101630,
      HRI_CL_JOB_V o101631,
      HRI_CL_JOB_CAT_JOB_V o101634,
      HRI_CL_LOW_BAND_V o101636,
      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 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 = o101680.ORGANIZATION_FROM_ID
   and o101630.ID = o101680.GRADE_FROM_ID
   and o101631.ID = o101680.JOB_FROM_ID
   and o101634.ID = o101680.JOB_FROM_ID
   and (o101636.BAND_MIN_VALUE <= o101680.LOW_MONTHS_LOSS_SEP AND (NVL(o101636.BAND_MAX_VALUE,9999)) > o101680.LOW_MONTHS_LOSS_SEP)
   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 ((o101667.SUB_ORG_RELATIVE_LEVEL = 0 OR :rollup_organizations = HR_BIS.BIS_DECODE_LOOKUP('YES_NO_ALL','Y') ) )
   AND (NVL(o101634.VALUE,'NA_EDW') = NVL(:job_category,'NA_EDW'))
   AND (:workforce_measurement_type IS NOT NULL )  
   AND (o101680.EVENT_DATE >= :start_date AND o101680.EVENT_DATE <= :end_date)
   AND (NVL(o101680.LOSS_SEP_INDICATOR,0) = 1)
 GROUP BY o101636.VALUE,o101640.VALUE,o101642.VALUE,o101643.VALUE
Parameter NameSQL textValidation
Organization Hierarchy Version
o101640.VALUE = :organization_hierarchy_version
LOV
Top Organization
o101643.VALUE = :top_organization
LOV
Job
o101631.VALUE = :job
LOV
Grade
o101630.VALUE = :grade
LOV
Position
o101648.VALUE = :position
LOV
Rollup Organizations
 
Job Category
 
Geography Location
o101635.VALUE = :geography_location
Geography Region
o101629.VALUE = :geography_region
Geography Country
o101628.VALUE = :geography_country
Geography Area
o101627.VALUE = :geography_area
Workforce Measurement Type
 
Number
End Date
 
Date
Start Date
 
Date