VIEW: APPS.HRI_CS_SUPHRO_V

Source

View Text - Preformatted

SELECT shs_sup.sup_absolute_level SUP_ABSOLUTE_LEVEL , shs_sup.sub_absolute_level SUB_ABSOLUTE_LEVEL , shs_sup.sub_relative_level SUB_RELATIVE_LEVEL , shs_sub.sub_absolute_level SUBRO_ABSOLUTE_LEVEL , shs_sub.sub_relative_level SUBRO_SUP_RELATIVE_LEVEL , shs_sub.sub_absolute_level - shs_sup.sup_absolute_level SUBRO_SUB_RELATIVE_LEVEL , GREATEST(shs_sub.effective_start_date ,shs_sup.effective_start_date) EFFECTIVE_START_DATE , LEAST(shs_sub.effective_end_date ,shs_sup.effective_end_date) EFFECTIVE_END_DATE , shs_sup.primary_hierarchy_flag_code PRIMARY_HIERARCHY_FLAG_CODE , shs_sup.sup_person_id SUP_PERSON_ID , shs_sup.sup_assignment_id SUP_ASSIGNMENT_ID , shs_sup.sub_assignment_id SUB_ASSIGNMENT_ID , shs_sup.sub_person_id SUB_PERSON_ID , shs_sub.sub_assignment_id SUBRO_ASSIGNMENT_ID , shs_sub.sub_person_id SUBRO_PERSON_ID , shs_sup.sup_business_group_id SUP_BUSINESS_GROUP_ID , shs_sup.sub_business_group_id SUB_BUSINESS_GROUP_ID , shs_sub.sub_business_group_id SUBRO_BUSINESS_GROUP_ID , GREATEST(shs_sup.last_change_date ,shs_sub.last_change_date) LAST_CHANGE_DATE FROM hri_cs_suph_v shs_sup , hri_cs_suph_v shs_sub WHERE shs_sub.sup_assignment_id = shs_sup.sub_assignment_id AND (shs_sub.effective_start_date BETWEEN shs_sup.effective_start_date AND shs_sup.effective_end_date OR shs_sup.effective_start_date BETWEEN shs_sub.effective_start_date AND shs_sub.effective_end_date)

View Text - HTML Formatted

SELECT SHS_SUP.SUP_ABSOLUTE_LEVEL SUP_ABSOLUTE_LEVEL
, SHS_SUP.SUB_ABSOLUTE_LEVEL SUB_ABSOLUTE_LEVEL
, SHS_SUP.SUB_RELATIVE_LEVEL SUB_RELATIVE_LEVEL
, SHS_SUB.SUB_ABSOLUTE_LEVEL SUBRO_ABSOLUTE_LEVEL
, SHS_SUB.SUB_RELATIVE_LEVEL SUBRO_SUP_RELATIVE_LEVEL
, SHS_SUB.SUB_ABSOLUTE_LEVEL - SHS_SUP.SUP_ABSOLUTE_LEVEL SUBRO_SUB_RELATIVE_LEVEL
, GREATEST(SHS_SUB.EFFECTIVE_START_DATE
, SHS_SUP.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, LEAST(SHS_SUB.EFFECTIVE_END_DATE
, SHS_SUP.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, SHS_SUP.PRIMARY_HIERARCHY_FLAG_CODE PRIMARY_HIERARCHY_FLAG_CODE
, SHS_SUP.SUP_PERSON_ID SUP_PERSON_ID
, SHS_SUP.SUP_ASSIGNMENT_ID SUP_ASSIGNMENT_ID
, SHS_SUP.SUB_ASSIGNMENT_ID SUB_ASSIGNMENT_ID
, SHS_SUP.SUB_PERSON_ID SUB_PERSON_ID
, SHS_SUB.SUB_ASSIGNMENT_ID SUBRO_ASSIGNMENT_ID
, SHS_SUB.SUB_PERSON_ID SUBRO_PERSON_ID
, SHS_SUP.SUP_BUSINESS_GROUP_ID SUP_BUSINESS_GROUP_ID
, SHS_SUP.SUB_BUSINESS_GROUP_ID SUB_BUSINESS_GROUP_ID
, SHS_SUB.SUB_BUSINESS_GROUP_ID SUBRO_BUSINESS_GROUP_ID
, GREATEST(SHS_SUP.LAST_CHANGE_DATE
, SHS_SUB.LAST_CHANGE_DATE) LAST_CHANGE_DATE
FROM HRI_CS_SUPH_V SHS_SUP
, HRI_CS_SUPH_V SHS_SUB
WHERE SHS_SUB.SUP_ASSIGNMENT_ID = SHS_SUP.SUB_ASSIGNMENT_ID
AND (SHS_SUB.EFFECTIVE_START_DATE BETWEEN SHS_SUP.EFFECTIVE_START_DATE
AND SHS_SUP.EFFECTIVE_END_DATE OR SHS_SUP.EFFECTIVE_START_DATE BETWEEN SHS_SUB.EFFECTIVE_START_DATE
AND SHS_SUB.EFFECTIVE_END_DATE)