View: AMW_CUSTOM_HIER_V

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

View Text

SELECT AU.ORG_TYPE OBJECT_TYPE
, NULL PARENT_PROCESS_ID
, NULL PARENT_REVISION_NUMBER
, NULL PARENT_PROCESS_CODE
, NULL PARENT_DISPLAY_NAME
, NULL PARENT_PROCESS_ORG_REV_ID
, NULL PARENT_ORGANIZATION_ID
, AU.NAME CHILD_DISPLAY_NAME
, NULL CHILD_DESCRIPTION
, -1*AU.ORGANIZATION_ID CHILD_PROCESS_ID
, AU.ORGANIZATION_ID CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, NULL CHILD_REVISION_NUMBER
, NULL CHILD_PROCESS_ORG_REV_ID
, NULL CHILD_APPROVAL_STATUS
, NULL CHILD_START_DATE
, NULL CHILD_APPROVAL_DATE
, NULL CHILD_APPROVAL_END_DATE
, NULL CHILD_END_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_OWNER_ID
, NULL CHILD_FINANCE_OWNER_ID
, NULL CHILD_APPLICATION_OWNER_ID
, NULL CHILD_STANDARD_VARIATION
, NULL CHILD_PROCESS_TYPE
, NULL CHILD_CONTROL_ACTIVITY_TYPE
, NULL CHILD_ORDER_NUMBER
, AMW_UTILITY_PVT.GET_PROJECT_COUNT(AU.ORGANIZATION_ID) ACTIVE_AUDIT_PROJECT_COUNT
FROM AMW_AUDIT_UNITS_V AU
WHERE ORGANIZATION_ID IN (SELECT ORGANIZATION_ID_PARENT
FROM PER_ORG_STRUCTURE_ELEMENTS
WHERE ORG_STRUCTURE_VERSION_ID = (SELECT ORG_STRUCTURE_VERSION_ID
FROM PER_ORG_STRUCTURE_VERSIONS
WHERE ORGANIZATION_STRUCTURE_ID = (SELECT ORGANIZATION_STRUCTURE_ID
FROM PER_ORGANIZATION_STRUCTURES
WHERE NAME = FND_PROFILE.VALUE('AMW_ORG_SECURITY_HIERARCHY'))
AND TRUNC(SYSDATE) BETWEEN DATE_FROM
AND NVL(DATE_TO
, SYSDATE)))
AND ORGANIZATION_ID NOT IN (SELECT ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS
WHERE ORG_STRUCTURE_VERSION_ID = (SELECT ORG_STRUCTURE_VERSION_ID
FROM PER_ORG_STRUCTURE_VERSIONS
WHERE ORGANIZATION_STRUCTURE_ID = (SELECT ORGANIZATION_STRUCTURE_ID
FROM PER_ORGANIZATION_STRUCTURES
WHERE NAME = FND_PROFILE.VALUE('AMW_ORG_SECURITY_HIERARCHY'))
AND TRUNC(SYSDATE) BETWEEN DATE_FROM
AND NVL(DATE_TO
, SYSDATE))) UNION ALL SELECT AU.ORG_TYPE OBJECT_TYPE
, -1*PER.ORGANIZATION_ID_PARENT PARENT_PROCESS_ID
, NULL PARENT_REVISION_NUMBER
, NULL PARENT_PROCESS_CODE
, NULL PARENT_DISPLAY_NAME
, NULL PARENT_PROCESS_ORG_REV_ID
, PER.ORGANIZATION_ID_PARENT PARENT_ORGANIZATION_ID
, AU.NAME CHILD_DISPLAY_NAME
, NULL CHILD_DESCRIPTION
, -1*PER.ORGANIZATION_ID_CHILD CHILD_PROCESS_ID
, PER.ORGANIZATION_ID_CHILD CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, NULL CHILD_REVISION_NUMBER
, NULL CHILD_PROCESS_ORG_REV_ID
, NULL CHILD_APPROVAL_STATUS
, NULL CHILD_START_DATE
, NULL CHILD_APPROVAL_DATE
, NULL CHILD_APPROVAL_END_DATE
, NULL CHILD_END_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_OWNER_ID
, NULL CHILD_FINANCE_OWNER_ID
, NULL CHILD_APPLICATION_OWNER_ID
, NULL CHILD_STANDARD_VARIATION
, NULL CHILD_PROCESS_TYPE
, NULL CHILD_CONTROL_ACTIVITY_TYPE
, NULL CHILD_ORDER_NUMBER
, AMW_UTILITY_PVT.GET_PROJECT_COUNT(AU.ORGANIZATION_ID) ACTIVE_AUDIT_PROJECT_COUNT
FROM PER_ORG_STRUCTURE_ELEMENTS PER
, AMW_AUDIT_UNITS_V AU
WHERE AU.ORGANIZATION_ID = PER.ORGANIZATION_ID_CHILD
AND ORG_STRUCTURE_VERSION_ID = (SELECT ORG_STRUCTURE_VERSION_ID
FROM PER_ORG_STRUCTURE_VERSIONS
WHERE ORGANIZATION_STRUCTURE_ID = (SELECT ORGANIZATION_STRUCTURE_ID
FROM PER_ORGANIZATION_STRUCTURES
WHERE NAME = FND_PROFILE.VALUE('AMW_ORG_SECURITY_HIERARCHY'))
AND TRUNC(SYSDATE) BETWEEN DATE_FROM
AND NVL(DATE_TO
, SYSDATE))

Columns