[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT ID,
VALUE,
PARENT_ID
FROM
(SELECT distinct mgr.sup_person_id id,
per.value value,
mgr.sup_person_id parent_id,
TO_CHAR(mgr.sup_level) || per.order_by order_by,
mgr.effective_start_date,
mgr.effective_end_date,
mgr.sub_person_id selected_mgr_person_id,
mgr.sub_level selected_mgr_level,
mgr.sup_person_id mgr_person_id,
mgr.sup_level mgr_level
FROM hri_cs_suph_orgmgr_ct mgr ,
hri_dbi_cl_per_n_v per
WHERE per.id = mgr.sup_person_id
AND trunc(&AS_OF_DATE) BETWEEN per.effective_start_date AND per.effective_end_date
AND EXISTS (SELECT NULL
FROM hri_cs_suph sec
WHERE sec.sup_person_id = &BIS_SELECTED_TOP_MANAGER
AND sec.sub_person_id = mgr.sup_person_id
AND mgr.effective_start_date BETWEEN sec.effective_start_date AND sec.effective_end_date )
UNION ALL
SELECT distinct mgr.sub_person_id id,
per.value value,
mgr.sup_person_id parent_id,
TO_CHAR(mgr.sub_level) || per.order_by order_by,
mgr.effective_start_date,
mgr.effective_end_date,
mgr.sup_person_id selected_mgr_person_id,
mgr.sup_level selected_mgr_level,
mgr.sub_person_id mgr_person_id,
mgr.sub_level mgr_level
FROM hri_cs_suph_orgmgr_ct mgr ,
hri_dbi_cl_per_n_v per
WHERE per.id = mgr.sub_person_id
AND mgr.sub_relative_level = 1
AND trunc(&AS_OF_DATE) BETWEEN per.effective_start_date AND per.effective_end_date
AND EXISTS (SELECT NULL
FROM hri_cs_suph sec
WHERE sec.sup_person_id = &BIS_SELECTED_TOP_MANAGER
AND sec.sub_person_id = mgr.sub_person_id
AND mgr.effective_start_date BETWEEN sec.effective_start_date AND sec.effective_end_date ))
WHERE SELECTED_MGR_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H
AND &AS_OF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
'
|| l_search ;