Product: | FII - Financial Intelligence (Obsolete) |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT SHS_SUP.SUP_LEVEL SUP_ABSOLUTE_LEVEL
, SHS_SUP.SUB_LEVEL SUB_ABSOLUTE_LEVEL
, SHS_SUP.SUB_RELATIVE_LEVEL SUB_RELATIVE_LEVEL
, SHS_SUB.SUB_LEVEL SUBRO_ABSOLUTE_LEVEL
, SHS_SUB.SUB_RELATIVE_LEVEL SUBRO_SUP_RELATIVE_LEVEL
, SHS_SUB.SUB_LEVEL - SHS_SUP.SUP_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
, 'Y' 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_UPDATE_DATE
, SHS_SUB.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM HRI_CS_SUPH SHS_SUP
, HRI_CS_SUPH 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)