[Home] [Help]
297: Update AMW_PROC_CERT_EVAL_SUM
298: SET UNMITIGATED_RISKS =
299: (
300: select distinct count(*)
301: from amw_risk_associations assoctable,
302: amw_risks_all_vl risktable,
303: amw_audit_units_v orgtable,
304: amw_wf_org_hierarchy_main_v processtable,
305: fnd_objects fo,
376: SET INEFFECTIVE_CONTROLS =
377: (select distinct Count(*)
378:
379: from amw_control_associations ctrlassoc,
380: amw_risk_associations riskassoc,
381: amw_process_organization procorg,
382: amw_controls_all_vl controltable,
383: amw_audit_units_v orgtable,
384: amw_wf_org_hierarchy_main_v processtable,
565: is
566:
567: CURSOR new_risks_added IS
568: SELECT count(1)
569: FROM AMW_RISK_ASSOCIATIONS
570: WHERE creation_date >= p_start_date
571: AND object_type = 'PROCESS_ORG'
572: AND pk1 in (
573: select distinct p_org.process_organization_id
635: SELECT count(1)
636: FROM (
637: SELECT distinct aca.control_id, p_org.organization_id
638: FROM AMW_CONTROL_ASSOCIATIONS aca,
639: AMW_RISK_ASSOCIATIONS ara,
640: AMW_PROCESS_ORGANIZATION apo,
641: AMW_ACCT_ASSOCIATIONS acct_assoc,
642: AMW_PROCESS_ORGANIZATION p_org,
643: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
695: AND aca.object_type = 'RISK_ORG'
696: AND aca.pk1 = ara.risk_association_id
697: AND not exists (SELECT 'Y'
698: FROM AMW_CONTROL_ASSOCIATIONS aca2,
699: AMW_RISK_ASSOCIATIONS ara2
700: WHERE aca2.creation_date < p_start_date
701: AND aca2.object_type = 'RISK_ORG'
702: AND aca2.pk1 = ara2.risk_association_id
703: AND ara2.object_type = 'PROCESS_ORG'
1084: SELECT count(1)
1085: FROM (SELECT distinct p_org.organization_id, p_org.process_id, ara.risk_id
1086: FROM AMW_PROCESS_ORGANIZATION apo,
1087: AMW_ACCT_ASSOCIATIONS acct_assoc,
1088: AMW_RISK_ASSOCIATIONS ara,
1089: AMW_OPINIONS_V aov,
1090: AMW_PROCESS_ORGANIZATION p_org,
1091: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
1092: from Amw_Org_Hierarchy_Denorm
1157: SELECT count(1)
1158: FROM (SELECT distinct p_org.organization_id, aca.control_id
1159: FROM AMW_PROCESS_ORGANIZATION apo,
1160: AMW_ACCT_ASSOCIATIONS acct_assoc,
1161: AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1162: AMW_OPINIONS_V aov,
1163: AMW_PROCESS_ORGANIZATION p_org,
1164: (select process_id, organization_id, parent_child_id child_process_id, up_down_ind
1165: from Amw_Org_Hierarchy_Denorm
1652:
1653: CURSOR unmitigated_risks IS
1654: SELECT count(1)
1655: FROM AMW_PROCESS_ORGANIZATION apo,
1656: AMW_RISK_ASSOCIATIONS ara,
1657: AMW_OPINIONS_V aov
1658: WHERE apo.organization_id = p_organization_id
1659: AND apo.process_id in ( select distinct(orgrel.child_process_id)
1660: from AMW_PROCESS_ORG_RELATIONS orgrel
1684:
1685: CURSOR ineffective_controls IS
1686: SELECT count(distinct aca.control_id)
1687: FROM AMW_PROCESS_ORGANIZATION apo,
1688: AMW_RISK_ASSOCIATIONS ara,
1689: AMW_CONTROL_ASSOCIATIONS aca,
1690: AMW_OPINIONS_V aov
1691: WHERE apo.organization_id = p_organization_id
1692: AND apo.process_id in ( select distinct(orgrel.child_process_id)
2547: from Amw_Org_Hierarchy_Denorm hier
2548: union select process_id, organization_id, process_id, 'D'
2549: from amw_process_organization) hier,
2550: AMW_PROCESS_ORGANIZATION po2,
2551: AMW_RISK_ASSOCIATIONS ara,
2552: AMW_OPINIONS_V aov
2553: WHERE aa.object_type = 'PROCESS_ORG'
2554: AND po.process_organization_id = aa.pk1
2555: AND hier.organization_id = po.organization_id
2616: from Amw_Org_Hierarchy_Denorm hier
2617: union select process_id, organization_id, process_id, 'D'
2618: from amw_process_organization) hier,
2619: AMW_PROCESS_ORGANIZATION po2,
2620: AMW_RISK_ASSOCIATIONS ara
2621: WHERE aa.object_type = 'PROCESS_ORG'
2622: AND po.process_organization_id = aa.pk1
2623: AND hier.organization_id = po.organization_id
2624: AND hier.process_id = po.process_id
2672: from Amw_Org_Hierarchy_Denorm hier
2673: union select process_id, organization_id, process_id, 'D'
2674: from amw_process_organization) hier,
2675: AMW_PROCESS_ORGANIZATION po2,
2676: AMW_RISK_ASSOCIATIONS ara,
2677: AMW_CONTROL_ASSOCIATIONS aca ,
2678: AMW_OPINIONS_V aov
2679: WHERE aa.object_type = 'PROCESS_ORG'
2680: AND po.process_organization_id = aa.pk1
2743: from Amw_Org_Hierarchy_Denorm hier
2744: union select process_id, organization_id, process_id, 'D'
2745: from amw_process_organization) hier,
2746: AMW_PROCESS_ORGANIZATION po2,
2747: AMW_RISK_ASSOCIATIONS ara,
2748: AMW_CONTROL_ASSOCIATIONS aca
2749: WHERE aa.object_type = 'PROCESS_ORG'
2750: AND po.process_organization_id = aa.pk1
2751: AND hier.organization_id = po.organization_id
5484: AMW_OPINION_TYPES_B opiniontype,
5485: FND_OBJECTS fndobject,
5486: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5487: amw_process_org_basicinfo_v orgprocess,
5488: amw_risk_associations riskassoc,
5489: amw_control_associations ctrlassoc
5490: WHERE
5491: (opinion.AUTHORED_DATE in
5492: (Select
5575: AMW_OPINION_TYPES_B opiniontype,
5576: FND_OBJECTS fndobject,
5577: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5578: amw_process_org_basicinfo_v orgprocess,
5579: amw_risk_associations riskassoc
5580: WHERE
5581: (opinion.AUTHORED_DATE in
5582: (Select
5583: MAX(opinion2.AUTHORED_DATE)
5661: count(1) into p_risks_verified
5662: from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
5663: FROM
5664: amw_process_org_basicinfo_v orgprocess,
5665: amw_risk_associations riskassoc
5666: WHERE
5667: orgprocess.process_organization_id = riskassoc.pk1
5668: and riskassoc.object_type = 'PROCESS_ORG' AND
5669: ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
5712: AMW_OPINION_TYPES_B opiniontype,
5713: FND_OBJECTS fndobject,
5714: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5715: amw_process_org_basicinfo_v orgprocess,
5716: amw_risk_associations riskassoc
5717: WHERE
5718: (opinion.AUTHORED_DATE in
5719: (Select
5720: MAX(opinion2.AUTHORED_DATE)
5800: FROM
5801: ( select distinct ctrlassoc.control_id, orgprocess.organization_id
5802: FROM
5803: amw_process_org_basicinfo_v orgprocess,
5804: amw_risk_associations riskassoc,
5805: amw_control_associations ctrlassoc
5806: WHERE
5807: orgprocess.process_organization_id = riskassoc.pk1
5808: and riskassoc.object_type = 'PROCESS_ORG'
5856: AMW_OPINION_TYPES_B opiniontype,
5857: FND_OBJECTS fndobject,
5858: AMW_OBJECT_OPINION_TYPES objectopiniontype,
5859: amw_process_org_basicinfo_v orgprocess,
5860: amw_risk_associations riskassoc,
5861: amw_control_associations ctrlassoc
5862: WHERE
5863: (opinion.AUTHORED_DATE in
5864: (Select
7054: AMW_OPINION_TYPES_B opiniontype,
7055: FND_OBJECTS fndobject,
7056: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7057: amw_process_org_basicinfo_v orgprocess,
7058: amw_risk_associations riskassoc,
7059: amw_control_associations ctrlassoc
7060: WHERE
7061: (opinion.AUTHORED_DATE in
7062: (Select
7200: AMW_OPINION_TYPES_B opiniontype,
7201: FND_OBJECTS fndobject,
7202: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7203: amw_process_org_basicinfo_v orgprocess,
7204: amw_risk_associations riskassoc
7205: WHERE
7206: (opinion.AUTHORED_DATE in
7207: (Select
7208: MAX(opinion2.AUTHORED_DATE)
7340: from (
7341: select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
7342: FROM
7343: amw_process_org_basicinfo_v orgprocess,
7344: amw_risk_associations riskassoc
7345: WHERE
7346: orgprocess.process_organization_id = riskassoc.pk1
7347: and riskassoc.object_type = 'PROCESS_ORG'
7348: and (orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
7440: AMW_OPINION_TYPES_B opiniontype,
7441: FND_OBJECTS fndobject,
7442: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7443: amw_process_org_basicinfo_v orgprocess,
7444: amw_risk_associations riskassoc
7445: WHERE
7446: (opinion.AUTHORED_DATE in
7447: (Select
7448: MAX(opinion2.AUTHORED_DATE)
7580: from (
7581: select distinct ctrlassoc.control_id, orgprocess.organization_id
7582: FROM
7583: amw_process_org_basicinfo_v orgprocess,
7584: amw_risk_associations riskassoc,
7585: amw_control_associations ctrlassoc
7586: WHERE
7587: orgprocess.process_organization_id = riskassoc.pk1
7588: and riskassoc.object_type = 'PROCESS_ORG'
7686: AMW_OPINION_TYPES_B opiniontype,
7687: FND_OBJECTS fndobject,
7688: AMW_OBJECT_OPINION_TYPES objectopiniontype,
7689: amw_process_org_basicinfo_v orgprocess,
7690: amw_risk_associations riskassoc,
7691: amw_control_associations ctrlassoc
7692: WHERE
7693: (opinion.AUTHORED_DATE in
7694: (Select