VIEW: APPS.HRI_DBI_CL_PER_N_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 ,fii_time_day_v day ,hri_dbi_cl_per_n_v per ,per_all_assignments_f asg WHERE 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 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 ) AND EXISTS (SELECT NULL FROM hri_cl_wkr_sup_status_ct mstt WHERE mstt.person_id = suph.sup_person_id AND mstt.supervisor_flag = 'Y' AND (mstt.effective_start_date BETWEEN day.id - 365 AND day.id OR day.id-365 BETWEEN mstt.effective_start_date AND mstt.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
, FII_TIME_DAY_V DAY
, HRI_DBI_CL_PER_N_V PER
, PER_ALL_ASSIGNMENTS_F ASG
WHERE 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 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 )
AND EXISTS (SELECT NULL
FROM HRI_CL_WKR_SUP_STATUS_CT MSTT
WHERE MSTT.PERSON_ID = SUPH.SUP_PERSON_ID
AND MSTT.SUPERVISOR_FLAG = 'Y'
AND (MSTT.EFFECTIVE_START_DATE BETWEEN DAY.ID - 365
AND DAY.ID OR DAY.ID-365 BETWEEN MSTT.EFFECTIVE_START_DATE
AND MSTT.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