VIEW: APPS.AMW_MANAG_HIER_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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