[Home] [Help]
1648: and entity_id = p_audit_project_id
1649: and organization_id = ara.pk2
1650: and process_id = ara.pk3);
1651:
1652: DELETE FROM amw_control_associations aca
1653: WHERE object_type='PROJECT'
1654: AND pk1 = p_audit_project_id
1655: AND pk3 IS NOT NULL
1656: AND not exists
1664: DELETE FROM amw_ap_associations apa
1665: WHERE object_type='PROJECT'
1666: AND pk1 = p_audit_project_id
1667: AND not exists
1668: (select 'Y' from amw_control_associations aca
1669: where aca.object_type = 'PROJECT'
1670: and aca.pk1 = p_audit_project_id
1671: and aca.pk2 = apa.pk2
1672: -- and aca.pk3 IS NOT NULL --process
1697: and entity_id = p_audit_project_id
1698: and organization_id = ara.pk2
1699: and process_id IS NULL);
1700:
1701: DELETE FROM amw_control_associations aca
1702: WHERE object_type='PROJECT'
1703: AND pk1 = p_audit_project_id
1704: AND pk3 IS NULL
1705: AND not exists
1713: DELETE FROM amw_ap_associations apa
1714: WHERE object_type='PROJECT'
1715: AND pk1 = p_audit_project_id
1716: AND not exists
1717: (select 'Y' from amw_control_associations aca
1718: where aca.object_type = 'PROJECT'
1719: and aca.pk1 = p_audit_project_id
1720: and aca.pk2 = apa.pk2
1721: -- and aca.pk3 IS NULL --process
1792: and ara2.risk_id = risk.risk_id
1793: and ara2.risk_rev_id = risk.risk_rev_id
1794: );
1795:
1796: UPDATE AMW_CONTROL_ASSOCIATIONS aca
1797: SET aca.control_rev_id = (SELECT control_rev_id
1798: FROM AMW_CONTROLS_B control
1799: WHERE control.control_id = aca.control_id
1800: AND control.curr_approved_flag = 'Y')
1802: AND aca.pk1 = p_audit_project_id
1803: AND aca.pk2 = scope_rec.organization_id
1804: AND aca.pk3 = scope_rec.process_id;
1805:
1806: INSERT INTO amw_control_associations
1807: (
1808: control_association_id,
1809: last_update_date,
1810: last_updated_by,
1819: pk4,
1820: object_type,
1821: object_version_number
1822: )
1823: SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
1824: SYSDATE,
1825: FND_GLOBAL.USER_ID,
1826: SYSDATE,
1827: FND_GLOBAL.USER_ID,
1833: scope_rec.process_id,
1834: ara.risk_id,
1835: 'PROJECT',
1836: 1
1837: FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
1838: WHERE aca.object_type = 'RISK_ORG'
1839: AND aca.pk1 = scope_rec.organization_id
1840: AND aca.pk2 = scope_rec.process_id
1841: AND aca.pk3 = ara.risk_id
1847: AND ara.pk1 = p_audit_project_id
1848: AND ara.pk2 = scope_rec.organization_id
1849: AND ara.pk3 = scope_rec.process_id
1850: AND not exists
1851: (SELECT 'Y' from amw_control_associations aca2
1852: WHERE aca2.object_type = 'PROJECT'
1853: AND aca2.pk1 = p_audit_project_id
1854: AND aca2.pk2 = scope_rec.organization_id
1855: AND aca2.pk3 = scope_rec.process_id
1905: pt2.task_id
1906: FROM amw_ap_associations apa,
1907: amw_audit_procedures_b aapb,
1908: amw_ap_tasks apt,
1909: amw_control_associations aca,
1910: amw_audit_projects_v pp,
1911: amw_audit_tasks_v pt1,
1912: amw_audit_tasks_v pt2
1913: WHERE apa.object_type = 'CTRL_ORG'
1978: pt2.task_id
1979: FROM amw_ap_associations apa,
1980: amw_audit_procedures_b aapb,
1981: amw_ap_tasks apt,
1982: amw_control_associations aca,
1983: amw_audit_projects_v pp,
1984: amw_template_tasks_v pt1,
1985: amw_audit_tasks_v pt2
1986: WHERE apa.object_type = 'CTRL_ORG'
2048: apa.pk1 organization_id,
2049: apa.pk3 control_id
2050: FROM amw_ap_associations apa,
2051: amw_audit_procedures_b aapb,
2052: amw_control_associations aca
2053: WHERE apa.object_type = 'CTRL_ORG'
2054: AND aca.object_type='PROJECT'
2055: AND aca.pk1 = p_audit_project_id
2056: AND aca.pk2 = scope_rec.organization_id
2131: and ara2.risk_id = risk.risk_id
2132: and ara2.risk_rev_id = risk.risk_rev_id
2133: );
2134:
2135: UPDATE AMW_CONTROL_ASSOCIATIONS aca
2136: SET aca.control_rev_id = (SELECT control_rev_id
2137: FROM AMW_CONTROLS_B control
2138: WHERE control.control_id = aca.control_id
2139: AND control.curr_approved_flag = 'Y')
2142: AND aca.pk2 = scope_org_rec.organization_id
2143: AND aca.pk3 IS NULL;
2144:
2145: if(not l_ineff_controls) THEN
2146: INSERT INTO amw_control_associations
2147: (
2148: control_association_id,
2149: last_update_date,
2150: last_updated_by,
2159: pk4,
2160: object_type,
2161: object_version_number
2162: )
2163: SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2164: SYSDATE,
2165: FND_GLOBAL.USER_ID,
2166: SYSDATE,
2167: FND_GLOBAL.USER_ID,
2173: null,
2174: ara.risk_id,
2175: 'PROJECT',
2176: 1
2177: FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2178: WHERE aca.object_type = 'ENTITY_CONTROL'
2179: AND aca.pk1 = scope_org_rec.organization_id
2180: AND aca.pk2 = ara.risk_id
2181: AND aca.pk3 IS NULL
2185: AND ara.pk1 = p_audit_project_id
2186: AND ara.pk2 = scope_org_rec.organization_id
2187: AND ara.pk3 IS NULL
2188: AND not exists
2189: (SELECT 'Y' from amw_control_associations aca2
2190: WHERE aca2.object_type = 'PROJECT'
2191: AND aca2.pk1 = p_audit_project_id
2192: AND aca2.pk2 = scope_org_rec.organization_id
2193: AND aca2.pk3 IS NULL
2195: AND aca2.control_id = control.control_id
2196: AND aca2.control_rev_id = control.control_rev_id
2197: );
2198: ELSE
2199: INSERT INTO amw_control_associations
2200: (
2201: control_association_id,
2202: last_update_date,
2203: last_updated_by,
2212: pk4,
2213: object_type,
2214: object_version_number
2215: )
2216: SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2217: SYSDATE,
2218: FND_GLOBAL.USER_ID,
2219: SYSDATE,
2220: FND_GLOBAL.USER_ID,
2226: null,
2227: ara.risk_id,
2228: 'PROJECT',
2229: 1
2230: FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2231: WHERE aca.object_type = 'ENTITY_CONTROL'
2232: AND aca.pk1 = scope_org_rec.organization_id
2233: AND aca.pk2 = ara.risk_id
2234: AND aca.pk3 IS NULL
2238: AND ara.pk1 = p_audit_project_id
2239: AND ara.pk2 = scope_org_rec.organization_id
2240: AND ara.pk3 IS NULL
2241: AND not exists
2242: (SELECT 'Y' from amw_control_associations aca2
2243: WHERE aca2.object_type = 'PROJECT'
2244: AND aca2.pk1 = p_audit_project_id
2245: AND aca2.pk2 = scope_org_rec.organization_id
2246: AND aca2.pk3 IS NULL
2248: AND aca2.control_id = control.control_id
2249: AND aca2.control_rev_id = control.control_rev_id
2250: )
2251: and aca.control_id in
2252: (select distinct control_id from amw_control_associations where pk1=p_source_project_id and object_type='PROJECT'
2253: and control_id not in (select pk1_value from amw_opinions_v where pk2_value =p_source_project_id
2254: and audit_result_code ='EFFECTIVE' and
2255: object_name='AMW_ORG_CONTROL') );
2256: END IF;
2302: pt2.task_id
2303: FROM amw_ap_associations apa,
2304: amw_audit_procedures_b aapb,
2305: amw_ap_tasks apt,
2306: amw_control_associations aca,
2307: amw_audit_projects_v pp,
2308: amw_audit_tasks_v pt1,
2309: amw_audit_tasks_v pt2
2310: WHERE apa.object_type = 'ENTITY_AP'
2373: pt2.task_id
2374: FROM amw_ap_associations apa,
2375: amw_audit_procedures_b aapb,
2376: amw_ap_tasks apt,
2377: amw_control_associations aca,
2378: amw_audit_projects_v pp,
2379: amw_template_tasks_v pt1,
2380: amw_audit_tasks_v pt2
2381: WHERE apa.object_type = 'ENTITY_AP'
2441: apa.pk1 organization_id,
2442: apa.pk2 control_id
2443: FROM amw_ap_associations apa,
2444: amw_audit_procedures_b aapb,
2445: amw_control_associations aca
2446: WHERE apa.object_type = 'ENTITY_AP'
2447: AND aca.object_type='PROJECT'
2448: AND aca.pk1 = p_audit_project_id
2449: AND aca.pk2 = scope_org_rec.organization_id
2671: AND entity_id = p_entity_id
2672: AND organization_id = ara.pk2
2673: AND process_id = ara.pk3);
2674:
2675: DELETE FROM AMW_CONTROL_ASSOCIATIONS aca
2676: WHERE object_type = p_entity_type
2677: AND pk1 = p_entity_id
2678: AND pk3 IS NOT NULL
2679: AND NOT EXISTS
2700: and entity_id = p_entity_id
2701: and organization_id = ara.pk2
2702: and process_id IS NULL);
2703:
2704: DELETE FROM amw_control_associations aca
2705: WHERE object_type=p_entity_type
2706: AND pk1 = p_entity_id
2707: AND pk3 IS NULL
2708: AND not exists
2718: DELETE FROM AMW_AP_ASSOCIATIONS apa
2719: WHERE object_type = p_entity_type
2720: AND pk1 = p_entity_id
2721: AND NOT EXISTS
2722: (SELECT 'Y' FROM AMW_CONTROL_ASSOCIATIONS aca
2723: WHERE aca.object_type = p_entity_type
2724: AND aca.pk1 = p_entity_id
2725: AND aca.pk2 = apa.pk2
2726: AND aca.control_id = apa.pk3)
2794: AND ara2.risk_rev_id = risk.risk_rev_id
2795: );
2796:
2797:
2798: UPDATE amw_control_associations aca
2799: SET aca.control_rev_id = (SELECT control_rev_id
2800: FROM amw_controls_b control
2801: WHERE control.control_id = aca.control_id
2802: AND control.curr_approved_flag = 'Y')
2804: AND aca.pk1 = p_entity_id
2805: AND aca.pk2 = each_rec.organization_id
2806: AND aca.pk3 = each_rec.process_id;
2807:
2808: INSERT INTO amw_control_associations (
2809: control_association_id,
2810: last_update_date,
2811: last_updated_by,
2812: creation_date,
2819: pk3,
2820: pk4,
2821: object_type,
2822: object_version_number)
2823: SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
2824: sysdate,
2825: fnd_global.user_id,
2826: sysdate,
2827: fnd_global.user_id,
2833: each_rec.process_id,
2834: ara.risk_id,
2835: p_entity_type,
2836: 1
2837: FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
2838: WHERE aca.object_type = 'RISK_ORG'
2839: AND aca.pk1 = each_rec.organization_id
2840: AND aca.pk2 = each_rec.process_id
2841: AND aca.pk3 = ara.risk_id
2846: AND ara.object_type = 'PROCESS_ORG'
2847: AND ara.pk1 = each_rec.organization_id
2848: AND ara.pk2 = each_rec.process_id
2849: AND NOT EXISTS
2850: (SELECT 'Y' FROM amw_control_associations aca2
2851: WHERE aca2.object_type = p_entity_type
2852: AND aca2.pk1 = p_entity_id
2853: AND aca2.pk2 = each_rec.organization_id
2854: AND aca2.pk3 = each_rec.process_id
2898: aapb.audit_procedure_id,
2899: aapb.audit_procedure_rev_id,
2900: apa.pk1 organization_id,
2901: aca.control_id
2902: FROM amw_ap_associations apa,amw_audit_procedures_b aapb,amw_control_associations aca
2903: WHERE apa.object_type = 'CTRL_ORG'
2904: AND apa.pk1 = each_rec.organization_id
2905: AND apa.pk2 = each_rec.process_id
2906: AND apa.pk3 = aca.control_id
2979: and ara2.risk_id = risk.risk_id
2980: and ara2.risk_rev_id = risk.risk_rev_id
2981: );
2982:
2983: UPDATE AMW_CONTROL_ASSOCIATIONS aca
2984: SET aca.control_rev_id =
2985: (SELECT control_rev_id
2986: FROM AMW_CONTROLS_B control
2987: WHERE control.control_id = aca.control_id
2990: AND aca.pk1 = p_entity_id
2991: AND aca.pk2 = scope_org_rec.organization_id
2992: AND aca.pk3 IS NULL;
2993:
2994: INSERT INTO amw_control_associations
2995: (
2996: control_association_id,
2997: last_update_date,
2998: last_updated_by,
3007: pk4,
3008: object_type,
3009: object_version_number
3010: )
3011: SELECT AMW_CONTROL_ASSOCIATIONS_S.nextval,
3012: SYSDATE,
3013: FND_GLOBAL.USER_ID,
3014: SYSDATE,
3015: FND_GLOBAL.USER_ID,
3021: null,
3022: ara.risk_id,
3023: p_entity_type,
3024: 1
3025: FROM amw_control_associations aca,amw_risk_associations ara,amw_controls_b control
3026: WHERE aca.object_type = 'ENTITY_CONTROL'
3027: AND aca.pk1 = scope_org_rec.organization_id
3028: AND aca.pk2 = ara.risk_id
3029: AND aca.pk3 IS NULL
3033: AND ara.pk1 = p_entity_id
3034: AND ara.pk2 = scope_org_rec.organization_id
3035: AND ara.pk3 IS NULL
3036: AND not exists
3037: (SELECT 'Y' from amw_control_associations aca2
3038: WHERE aca2.object_type = p_entity_type
3039: AND aca2.pk1 = p_entity_id
3040: AND aca2.pk2 = scope_org_rec.organization_id
3041: AND aca2.pk3 IS NULL
3089: aapb.audit_procedure_rev_id,
3090: apa.pk1 organization_id,
3091: aca.control_id
3092: FROM amw_ap_associations apa,amw_audit_procedures_b aapb,
3093: amw_control_associations aca
3094: WHERE apa.object_type = 'ENTITY_AP'
3095: AND aca.object_type = p_entity_type
3096: AND aca.pk1 = p_entity_id
3097: AND aca.pk2 = scope_org_rec.organization_id