VIEW: APPS.AMW_CUSTOM_HIER_V

Source

View Text - Preformatted

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))

View Text - HTML Formatted

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))