View: AMW_ORG_CERT_EVAL_SUM_V

Product:	AMW - Internal Controls Manager
Description:	view of an organization certification summary table
Implementation/DBA Data:	APPS.AMW_ORG_CERT_EVAL_SUM_V

View Text

SELECT OSUM.CERTIFICATION_ID
, OSUM.ORGANIZATION_ID
, DECODE(OSUM.TOTAL_SUB_ORG
, 0
, NULL
, (SELECT CASE WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF' THEN TO_CHAR(OSUM.SUB_ORG_CERT) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_TOTAL' THEN DECODE(OSUM.SUB_ORG_CERT
, NULL
, NULL
, OSUM.SUB_ORG_CERT||' / '||OSUM.TOTAL_SUB_ORG) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED' THEN DECODE(OSUM.SUB_ORG_CERT
, NULL
, NULL
, OSUM.SUB_ORG_CERT||' / '||OSUM.TOTAL_SUB_ORG) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED_TOTAL' THEN DECODE(OSUM.SUB_ORG_CERT
, NULL
, NULL
, OSUM.SUB_ORG_CERT||' / '||OSUM.TOTAL_SUB_ORG) ELSE (ROUND(OSUM.SUB_ORG_CERT/OSUM.TOTAL_SUB_ORG*100)||'%') END
FROM DUAL)) SUB_ORG_CERT
, DECODE(OSUM.TOTAL_PROCESSES
, 0
, NULL
, (SELECT CASE WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF' THEN TO_CHAR(OSUM.PROCESSES_CERTIFIED) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_TOTAL' THEN DECODE(OSUM.PROCESSES_CERTIFIED
, NULL
, NULL
, OSUM.PROCESSES_CERTIFIED||' / '||OSUM.TOTAL_PROCESSES) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED' THEN DECODE(OSUM.PROCESSES_CERTIFIED
, NULL
, NULL
, OSUM.PROCESSES_CERTIFIED||' / '||OSUM.TOTAL_PROCESSES) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED_TOTAL' THEN DECODE(OSUM.PROCESSES_CERTIFIED
, NULL
, NULL
, OSUM.PROCESSES_CERTIFIED||' / '||OSUM.TOTAL_PROCESSES) ELSE (ROUND(OSUM.PROCESSES_CERTIFIED/OSUM.TOTAL_PROCESSES*100)||'%') END
FROM DUAL)) PROCESSES_CERTIFIED
, DECODE(OSUM.TOTAL_PROCESSES
, 0
, NULL
, (SELECT CASE WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF' THEN TO_CHAR(INEFF_PROCESSES) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_TOTAL' THEN DECODE(OSUM.INEFF_PROCESSES
, NULL
, NULL
, OSUM.INEFF_PROCESSES||' / '||OSUM.TOTAL_PROCESSES) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED' THEN DECODE(OSUM.INEFF_PROCESSES
, NULL
, NULL
, OSUM.INEFF_PROCESSES||' / '||OSUM.EVALUATED_PROCESSES) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED_TOTAL' THEN DECODE(OSUM.INEFF_PROCESSES
, NULL
, NULL
, OSUM.INEFF_PROCESSES||' / '||OSUM.EVALUATED_PROCESSES||' /'||OSUM.TOTAL_PROCESSES) ELSE DECODE(OSUM.INEFF_PROCESSES_PRCNT
, NULL
, NULL
, OSUM.INEFF_PROCESSES_PRCNT||'%') END
FROM DUAL)) INEFF_PROCESSES
, DECODE(OSUM.TOTAL_RISKS
, 0
, NULL
, (SELECT CASE WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF' THEN TO_CHAR(UNMITIGATED_RISKS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_TOTAL' THEN DECODE(OSUM.UNMITIGATED_RISKS
, NULL
, NULL
, OSUM.UNMITIGATED_RISKS||' / '||OSUM.TOTAL_RISKS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED' THEN DECODE(OSUM.UNMITIGATED_RISKS
, NULL
, NULL
, OSUM.UNMITIGATED_RISKS||' / '||OSUM.EVALUATED_RISKS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED_TOTAL' THEN DECODE(OSUM.UNMITIGATED_RISKS
, NULL
, NULL
, OSUM.UNMITIGATED_RISKS||' / '||OSUM.EVALUATED_RISKS||' / '||OSUM.TOTAL_RISKS) ELSE DECODE(OSUM.UNMITIGATED_RISKS_PRCNT
, NULL
, NULL
, OSUM.UNMITIGATED_RISKS_PRCNT||'%') END
FROM DUAL)) UNMITIGATED_RISKS
, DECODE(OSUM.TOTAL_CONTROLS
, 0
, NULL
, (SELECT CASE WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF' THEN TO_CHAR(INEFFECTIVE_CONTROLS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_TOTAL' THEN DECODE(OSUM.INEFFECTIVE_CONTROLS
, NULL
, NULL
, OSUM.INEFFECTIVE_CONTROLS||' / '||OSUM.TOTAL_CONTROLS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED' THEN DECODE(OSUM.INEFFECTIVE_CONTROLS
, NULL
, NULL
, OSUM.INEFFECTIVE_CONTROLS||' / '||OSUM.EVALUATED_CONTROLS) WHEN FND_PROFILE.VALUE('AMW_OPINION_NUMBERS_OPTION') = 'INEFF_VERIFIED_TOTAL' THEN DECODE(OSUM.INEFFECTIVE_CONTROLS
, NULL
, NULL
, OSUM.INEFFECTIVE_CONTROLS||' / '||OSUM.EVALUATED_CONTROLS||' / '||OSUM.TOTAL_CONTROLS) ELSE DECODE(OSUM.INEFF_CONTROLS_PRCNT
, NULL
, NULL
, OSUM.INEFF_CONTROLS_PRCNT||'%') END
FROM DUAL)) INEFFECTIVE_CONTROLS
, DECODE(OSUM.TOTAL_PROCESSES
, 0
, NULL
, (SELECT IMAGE_FILE
FROM AMW_THRESHOLD_VALUES
WHERE OSUM.INEFF_PROCESSES_PRCNT BETWEEN START_PERCENT_VALUE
AND END_PERCENT_VALUE
AND NOT (FND_PROFILE.VALUE('AMW_THRSH_DISPLAY_OPTION') <> 'IMAGE'
AND START_PERCENT_VALUE = 0))) INEFF_PROCESSES_IMAGE
, DECODE(OSUM.TOTAL_RISKS
, 0
, NULL
, (SELECT IMAGE_FILE
FROM AMW_THRESHOLD_VALUES
WHERE OSUM.UNMITIGATED_RISKS_PRCNT BETWEEN START_PERCENT_VALUE
AND END_PERCENT_VALUE
AND NOT (FND_PROFILE.VALUE('AMW_THRSH_DISPLAY_OPTION') <> 'IMAGE'
AND START_PERCENT_VALUE = 0))) UNMITIGATED_RISKS_IMAGE
, DECODE(OSUM.TOTAL_CONTROLS
, 0
, NULL
, (SELECT IMAGE_FILE
FROM AMW_THRESHOLD_VALUES
WHERE OSUM.INEFF_CONTROLS_PRCNT BETWEEN START_PERCENT_VALUE
AND END_PERCENT_VALUE
AND NOT (FND_PROFILE.VALUE('AMW_THRSH_DISPLAY_OPTION') <> 'IMAGE'
AND START_PERCENT_VALUE = 0))) INEFF_CONTROLS_IMAGE
, OPEN_FINDINGS
, OPEN_ISSUES
, EVALUATION_OPINION_LOG_ID
FROM AMW_ORG_CERT_EVAL_SUM OSUM

Columns