HRMSi - Employee Separation (Organization Hierarchy) Template Analytics: Details

Description
Categories: Discoverer
Analyses the reasons why people are leaving the enterprise
Owner: SYSADMIN (System Administrator)
Identifier: HRIAWSPT
SELECT  o101630.VALUE as BG_Security_Flag_Code____Y_,o101631.VALUE as E148277,o101640.VALUE as BG_Security_Flag_Code____Y_,o101642.VALUE as BG_Security_Flag_Code____Y_,o101643.VALUE as E148289,o101648.VALUE as BG_Security_Flag_Code____Y_,o101655.VALUE as BG_Security_Flag_Code____Y_,o101660.VALUE as E148305,o101680.EVENT_DATE as E157423,o101635.VALUE as E168037,MAX(o101630.ORDER_BY) as BG_Security_Flag_Code____Y_,MAX(o101631.ORDER_BY) as Application_Id,MAX(o101648.ORDER_BY) as BG_Security_Flag_Code____Y_,MAX(o101655.ORDER_BY) as BG_Security_Flag_Code____Y_,MAX(o101660.ORDER_BY) as Application_Id,as108176_168037_OLD as Application_Id,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_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_RSN_SEP_X_V o101655,
      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,
     (SELECT o101635.VALUE AS as108176_168037_OLD_2, MAX(o101635.ORDER_BY) AS as108176_168037_OLD FROM HRI_CL_LOC_HR_V o101635  WHERE o101635.BG_SECURITY_FLAG_CODE = 'Y' GROUP BY o101635.VALUE)
 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 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 o101655.ID = o101680.SEPARATION_REASON_CODE
   and o101660.ID = o101680.SEPARATION_CATEGORY_CODE
   and o101664.LOCATION_ID = o101680.LOCATION_FROM_ID
   and (o101635.VALUE = as108176_168037_OLD_2(+)))
   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 (o101680.EVENT_DATE BETWEEN :start_date AND :end_date)
   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 (NVL(o101680.LOSS_SEP_INDICATOR,0) = 1)
 GROUP BY as108176_168037_OLD,o101630.VALUE,o101631.VALUE,o101640.VALUE,o101642.VALUE,o101643.VALUE,o101648.VALUE,o101655.VALUE,o101660.VALUE,o101680.EVENT_DATE,o101635.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
End Date
 
Date
Start Date
 
Date
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