View: AMW_MANAG_HIER_V

Product:	AMW - Internal Controls Manager
Description:	View for Organizations Managerial hierarchy.
Implementation/DBA Data:	APPS.AMW_MANAG_HIER_V

View Text

SELECT FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID
, NULL PARENT_VALUE
, NULL PARENT_ID
, NULL PARENT_DISPLAY_NAME
, FLEX_VALUE CHILD_VALUE
, FLEX_VALUE_ID CHILD_ID
, DESCRIPTION CHILD_DISPLAY_NAME
, 'LOB' LINE_TYPE
, NULL CHILD_DESCRIPTION
, NULL CHILD_PROCESS_ID
, NULL CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, NULL CHILD_REVISION_NUMBER
, NULL CHILD_APPROVAL_STATUS
, NULL CHILD_APPROVAL_DATE
, NULL CHILD_CONTROL_COUNT
, NULL CHILD_RISK_COUNT
, NULL CHILD_SIGNIFICANT_PROCESS_FLAG
, NULL CHILD_STANDARD_PROCESS_FLAG
, NULL CHILD_CERTIFICATION_STATUS
, NULL CHILD_PROCESS_CATEGORY
, NULL CHILD_PROCESS_TYPE
, NULL CHILD_CONTROL_ACTIVITY_TYPE
, NULL CHILD_ORDER_NUMBER
, NULL ACTIVE_AUDIT_PROJECT_COUNT
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID IN (SELECT DISTINCT LOB_VALUESET
FROM AMW_AUDIT_UNITS_V
WHERE LOB_VALUESET IS NOT NULL)
AND ENABLED_FLAG = 'Y'
AND FLEX_VALUE NOT IN (SELECT FLEX_VALUE
FROM FND_FLEX_VALUE_CHILDREN_V
WHERE FLEX_VALUE_SET_ID IN (SELECT DISTINCT LOB_VALUESET
FROM AMW_AUDIT_UNITS_V
WHERE LOB_VALUESET IS NOT NULL)) UNION SELECT CH.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID
, CH.PARENT_FLEX_VALUE PARENT_VALUE
, FVLP.FLEX_VALUE_ID PARENT_ID
, FVLP.DESCRIPTION PARENT_DISPLAY_NAME
, CH.FLEX_VALUE CHILD_VALUE
, FVLC.FLEX_VALUE_ID CHILD_ID
, FVLC.DESCRIPTION CHILD_DISPLAY_NAME
, 'LOB' LINE_TYPE
, NULL CHILD_DESCRIPTION
, NULL CHILD_PROCESS_ID
, NULL CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, NULL CHILD_REVISION_NUMBER
, NULL CHILD_APPROVAL_STATUS
, NULL CHILD_APPROVAL_DATE
, NULL CHILD_CONTROL_COUNT
, NULL CHILD_RISK_COUNT
, NULL CHILD_SIGNIFICANT_PROCESS_FLAG
, NULL CHILD_STANDARD_PROCESS_FLAG
, NULL CHILD_CERTIFICATION_STATUS
, NULL CHILD_PROCESS_CATEGORY
, NULL CHILD_PROCESS_TYPE
, NULL CHILD_CONTROL_ACTIVITY_TYPE
, NULL CHILD_ORDER_NUMBER
, NULL ACTIVE_AUDIT_PROJECT_COUNT
FROM FND_FLEX_VALUE_CHILDREN_V CH
, FND_FLEX_VALUES_VL FVLP
, FND_FLEX_VALUES_VL FVLC
WHERE FVLC.FLEX_VALUE_SET_ID = CH.FLEX_VALUE_SET_ID
AND CH.FLEX_VALUE = FVLC.FLEX_VALUE
AND CH.FLEX_VALUE_SET_ID IN (SELECT DISTINCT LOB_VALUESET
FROM AMW_AUDIT_UNITS_V
WHERE LOB_VALUESET IS NOT NULL)
AND FVLP.FLEX_VALUE_SET_ID = CH.FLEX_VALUE_SET_ID
AND CH.PARENT_FLEX_VALUE = FVLP.FLEX_VALUE UNION SELECT TO_NUMBER(LOB_VALUESET) FLEX_VALUE_SET_ID
, LOB_CODE PARENT_VALUE
, FVLA.FLEX_VALUE_ID PARENT_ID
, LOB_DESCRIPTION PARENT_DISPLAY_NAME
, NULL CHILD_VALUE
, ORGANIZATION_ID CHILD_ID
, NAME CHILD_DISPLAY_NAME
, A.ORG_TYPE LINE_TYPE
, NULL CHILD_DESCRIPTION
, NULL CHILD_PROCESS_ID
, NULL CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, NULL CHILD_REVISION_NUMBER
, NULL CHILD_APPROVAL_STATUS
, NULL CHILD_APPROVAL_DATE
, NULL CHILD_CONTROL_COUNT
, NULL CHILD_RISK_COUNT
, NULL CHILD_SIGNIFICANT_PROCESS_FLAG
, NULL CHILD_STANDARD_PROCESS_FLAG
, NULL CHILD_CERTIFICATION_STATUS
, NULL CHILD_PROCESS_CATEGORY
, NULL CHILD_PROCESS_TYPE
, NULL CHILD_CONTROL_ACTIVITY_TYPE
, NULL CHILD_ORDER_NUMBER
, AMW_UTILITY_PVT.GET_PROJECT_COUNT(A.ORGANIZATION_ID) ACTIVE_AUDIT_PROJECT_COUNT
FROM AMW_AUDIT_UNITS_V A
, FND_FLEX_VALUES_VL FVLA
WHERE FVLA.FLEX_VALUE_SET_ID = A.LOB_VALUESET
AND FVLA.FLEX_VALUE = A.LOB_CODE

Columns