VIEW: APPS.AMW_ORG_CERT_EVAL_SUM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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