VIEW: APPS.HRI_CL_PER_SUPH_ORGMGR_V

Source

View Text - Preformatted

SELECT distinct mgr.sub_person_id ID , per.value VALUE , mgr.sup_person_id PARENT_ID , mgr.effective_start_date START_DATE , mgr.effective_end_date END_DATE , mgr.effective_start_date EFFECTIVE_START_DATE , mgr.effective_end_date EFFECTIVE_END_DATE , 1 VIEW_ORDER , '1' || per.order_by ORDER_BY FROM hri_cs_suph_orgmgr_ct mgr , hri_dbi_cl_per_n_v per WHERE mgr.sub_person_id = per.id AND mgr.sub_person_id = mgr.sup_person_id AND mgr.sub_person_id = fnd_global.employee_id AND mgr.sub_relative_level = 0 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date UNION ALL SELECT distinct mgr.sub_person_id ID , per.value VALUE , mgr.sup_person_id PARENT_ID , mgr.effective_start_date START_DATE , mgr.effective_end_date END_DATE , mgr.effective_start_date EFFECTIVE_START_DATE , mgr.effective_end_date EFFECTIVE_END_DATE , 2 VIEW_ORDER , '2' || per.order_by ORDER_BY FROM hri_cs_suph_orgmgr_ct mgr , hri_dbi_cl_per_n_v per WHERE mgr.sub_person_id = per.id AND mgr.sub_relative_level = 0 AND mgr.sup_person_id <> fnd_global.employee_id AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date AND EXISTS (SELECT 1 FROM hri_cs_suph_orgmgr_ct mgr1 WHERE mgr1.sup_person_id = fnd_global.employee_id AND mgr1.sub_person_id = mgr.sub_person_id AND mgr.effective_start_date BETWEEN mgr1.effective_start_date AND mgr1.effective_end_date) UNION ALL SELECT distinct mgr.sub_person_id ID , per.value VALUE , mgr.sup_person_id PARENT_ID , mgr.effective_start_date START_DATE , mgr.effective_end_date END_DATE , mgr.effective_start_date EFFECTIVE_START_DATE , mgr.effective_end_date EFFECTIVE_END_DATE , 3 VIEW_ORDER , '3' || per.order_by ORDER_BY FROM hri_cs_suph_orgmgr_ct mgr , hri_dbi_cl_per_n_v per WHERE mgr.sub_person_id = per.id AND sub_person_id <> fnd_global.employee_id AND mgr.sub_relative_level = 1 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date AND EXISTS (SELECT 1 FROM hri_cs_suph_orgmgr_ct mgr1 WHERE mgr1.sup_person_id = fnd_global.employee_id AND mgr1.sub_person_id = mgr.sub_person_id AND mgr.effective_start_date BETWEEN mgr1.effective_start_date AND mgr1.effective_end_date) ORDER BY VIEW_ORDER , VALUE

View Text - HTML Formatted

SELECT DISTINCT MGR.SUB_PERSON_ID ID
, PER.VALUE VALUE
, MGR.SUP_PERSON_ID PARENT_ID
, MGR.EFFECTIVE_START_DATE START_DATE
, MGR.EFFECTIVE_END_DATE END_DATE
, MGR.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, MGR.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 1 VIEW_ORDER
, '1' || PER.ORDER_BY ORDER_BY
FROM HRI_CS_SUPH_ORGMGR_CT MGR
, HRI_DBI_CL_PER_N_V PER
WHERE MGR.SUB_PERSON_ID = PER.ID
AND MGR.SUB_PERSON_ID = MGR.SUP_PERSON_ID
AND MGR.SUB_PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND MGR.SUB_RELATIVE_LEVEL = 0
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE UNION ALL SELECT DISTINCT MGR.SUB_PERSON_ID ID
, PER.VALUE VALUE
, MGR.SUP_PERSON_ID PARENT_ID
, MGR.EFFECTIVE_START_DATE START_DATE
, MGR.EFFECTIVE_END_DATE END_DATE
, MGR.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, MGR.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 2 VIEW_ORDER
, '2' || PER.ORDER_BY ORDER_BY
FROM HRI_CS_SUPH_ORGMGR_CT MGR
, HRI_DBI_CL_PER_N_V PER
WHERE MGR.SUB_PERSON_ID = PER.ID
AND MGR.SUB_RELATIVE_LEVEL = 0
AND MGR.SUP_PERSON_ID <> FND_GLOBAL.EMPLOYEE_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT 1
FROM HRI_CS_SUPH_ORGMGR_CT MGR1
WHERE MGR1.SUP_PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND MGR1.SUB_PERSON_ID = MGR.SUB_PERSON_ID
AND MGR.EFFECTIVE_START_DATE BETWEEN MGR1.EFFECTIVE_START_DATE
AND MGR1.EFFECTIVE_END_DATE) UNION ALL SELECT DISTINCT MGR.SUB_PERSON_ID ID
, PER.VALUE VALUE
, MGR.SUP_PERSON_ID PARENT_ID
, MGR.EFFECTIVE_START_DATE START_DATE
, MGR.EFFECTIVE_END_DATE END_DATE
, MGR.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, MGR.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 3 VIEW_ORDER
, '3' || PER.ORDER_BY ORDER_BY
FROM HRI_CS_SUPH_ORGMGR_CT MGR
, HRI_DBI_CL_PER_N_V PER
WHERE MGR.SUB_PERSON_ID = PER.ID
AND SUB_PERSON_ID <> FND_GLOBAL.EMPLOYEE_ID
AND MGR.SUB_RELATIVE_LEVEL = 1
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT 1
FROM HRI_CS_SUPH_ORGMGR_CT MGR1
WHERE MGR1.SUP_PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND MGR1.SUB_PERSON_ID = MGR.SUB_PERSON_ID
AND MGR.EFFECTIVE_START_DATE BETWEEN MGR1.EFFECTIVE_START_DATE
AND MGR1.EFFECTIVE_END_DATE) ORDER BY VIEW_ORDER
, VALUE