VIEW: APPS.HRI_DBI_CL_PER_MGR_H_V

Source

View Text - Preformatted

SELECT suph.sup_person_id id ,per.value value ,asg.supervisor_id parent_id ,day.id effective_date ,TO_CHAR(suph.sup_level) || per.order_by order_by ,suph.sub_person_id selected_mgr_person_id ,suph.sub_level selected_mgr_level ,suph.sup_person_id mgr_person_id ,suph.sup_level mgr_level FROM hri_cs_suph suph ,hri_cl_wkr_sup_status_ct stt ,fii_time_day_v day ,hri_dbi_cl_per_n_v per ,per_all_assignments_f asg WHERE stt.person_id = suph.sub_person_id AND suph.sub_invalid_flag_code = 'N' AND asg.person_id = suph.sup_person_id AND per.id = suph.sup_person_id AND asg.primary_flag = 'Y' AND asg.assignment_type IN ('E', 'C') AND stt.supervisor_flag = 'Y' AND day.id BETWEEN stt.effective_start_date AND stt.effective_end_date AND day.id BETWEEN suph.effective_start_date AND suph.effective_end_date AND day.id BETWEEN per.effective_start_date AND per.effective_end_date AND day.id BETWEEN asg.effective_start_date AND asg.effective_end_date AND EXISTS (SELECT NULL FROM hri_cs_suph sec WHERE sec.sub_invalid_flag_code = 'N' AND sec.sup_person_id = hri_bpl_security.get_apps_signin_person_id AND sec.sub_person_id = suph.sup_person_id AND day.id BETWEEN sec.effective_start_date AND sec.effective_end_date ) UNION ALL SELECT suph.sub_person_id id ,per.value value ,suph.sup_person_id parent_id ,day.id effective_date ,TO_CHAR(suph.sub_level) || per.order_by order_by ,suph.sup_person_id selected_mgr_person_id ,suph.sup_level selected_mgr_level ,suph.sub_person_id mgr_person_id ,suph.sub_level mgr_level FROM hri_cs_suph suph ,hri_cl_wkr_sup_status_ct stt ,fii_time_day_v day ,hri_dbi_cl_per_n_v per WHERE stt.person_id = suph.sub_person_id AND suph.sub_invalid_flag_code = 'N' AND per.id = suph.sub_person_id AND suph.sub_relative_level = 1 AND stt.supervisor_flag = 'Y' AND day.id BETWEEN stt.effective_start_date AND stt.effective_end_date AND day.id BETWEEN suph.effective_start_date AND suph.effective_end_date AND day.id BETWEEN per.effective_start_date AND per.effective_end_date AND EXISTS (SELECT NULL FROM hri_cs_suph sec WHERE sec.sub_invalid_flag_code = 'N' AND sec.sup_person_id = hri_bpl_security.get_apps_signin_person_id AND sec.sub_person_id = suph.sub_person_id AND day.id BETWEEN sec.effective_start_date AND sec.effective_end_date ) WITH READ ONLY

View Text - HTML Formatted

SELECT SUPH.SUP_PERSON_ID ID
, PER.VALUE VALUE
, ASG.SUPERVISOR_ID PARENT_ID
, DAY.ID EFFECTIVE_DATE
, TO_CHAR(SUPH.SUP_LEVEL) || PER.ORDER_BY ORDER_BY
, SUPH.SUB_PERSON_ID SELECTED_MGR_PERSON_ID
, SUPH.SUB_LEVEL SELECTED_MGR_LEVEL
, SUPH.SUP_PERSON_ID MGR_PERSON_ID
, SUPH.SUP_LEVEL MGR_LEVEL
FROM HRI_CS_SUPH SUPH
, HRI_CL_WKR_SUP_STATUS_CT STT
, FII_TIME_DAY_V DAY
, HRI_DBI_CL_PER_N_V PER
, PER_ALL_ASSIGNMENTS_F ASG
WHERE STT.PERSON_ID = SUPH.SUB_PERSON_ID
AND SUPH.SUB_INVALID_FLAG_CODE = 'N'
AND ASG.PERSON_ID = SUPH.SUP_PERSON_ID
AND PER.ID = SUPH.SUP_PERSON_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND STT.SUPERVISOR_FLAG = 'Y'
AND DAY.ID BETWEEN STT.EFFECTIVE_START_DATE
AND STT.EFFECTIVE_END_DATE
AND DAY.ID BETWEEN SUPH.EFFECTIVE_START_DATE
AND SUPH.EFFECTIVE_END_DATE
AND DAY.ID BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND DAY.ID BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND EXISTS (SELECT NULL
FROM HRI_CS_SUPH SEC
WHERE SEC.SUB_INVALID_FLAG_CODE = 'N'
AND SEC.SUP_PERSON_ID = HRI_BPL_SECURITY.GET_APPS_SIGNIN_PERSON_ID
AND SEC.SUB_PERSON_ID = SUPH.SUP_PERSON_ID
AND DAY.ID BETWEEN SEC.EFFECTIVE_START_DATE
AND SEC.EFFECTIVE_END_DATE ) UNION ALL SELECT SUPH.SUB_PERSON_ID ID
, PER.VALUE VALUE
, SUPH.SUP_PERSON_ID PARENT_ID
, DAY.ID EFFECTIVE_DATE
, TO_CHAR(SUPH.SUB_LEVEL) || PER.ORDER_BY ORDER_BY
, SUPH.SUP_PERSON_ID SELECTED_MGR_PERSON_ID
, SUPH.SUP_LEVEL SELECTED_MGR_LEVEL
, SUPH.SUB_PERSON_ID MGR_PERSON_ID
, SUPH.SUB_LEVEL MGR_LEVEL
FROM HRI_CS_SUPH SUPH
, HRI_CL_WKR_SUP_STATUS_CT STT
, FII_TIME_DAY_V DAY
, HRI_DBI_CL_PER_N_V PER
WHERE STT.PERSON_ID = SUPH.SUB_PERSON_ID
AND SUPH.SUB_INVALID_FLAG_CODE = 'N'
AND PER.ID = SUPH.SUB_PERSON_ID
AND SUPH.SUB_RELATIVE_LEVEL = 1
AND STT.SUPERVISOR_FLAG = 'Y'
AND DAY.ID BETWEEN STT.EFFECTIVE_START_DATE
AND STT.EFFECTIVE_END_DATE
AND DAY.ID BETWEEN SUPH.EFFECTIVE_START_DATE
AND SUPH.EFFECTIVE_END_DATE
AND DAY.ID BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT NULL
FROM HRI_CS_SUPH SEC
WHERE SEC.SUB_INVALID_FLAG_CODE = 'N'
AND SEC.SUP_PERSON_ID = HRI_BPL_SECURITY.GET_APPS_SIGNIN_PERSON_ID
AND SEC.SUB_PERSON_ID = SUPH.SUB_PERSON_ID
AND DAY.ID BETWEEN SEC.EFFECTIVE_START_DATE
AND SEC.EFFECTIVE_END_DATE ) WITH READ ONLY