[Home] [Help]
1591: FROM fnd_attached_documents fad
1592: WHERE fad.entity_name = 'AMW_PROJECT_AP'
1593: AND fad.pk1_value = p_audit_project_id
1594: AND NOT EXISTS (select 'Y'
1595: from amw_ap_associations ap_assoc
1596: where ap_assoc.object_type='PROJECT'
1597: and ap_assoc.pk1 = fad.pk1_value
1598: and ap_assoc.pk2 = fad.pk2_value
1599: and ap_assoc.pk4 = fad.pk3_value
1600: and ap_assoc.audit_procedure_rev_id = fad.pk4_value);
1601: -- To fetch the new audit procedure id
1602: CURSOR c_apdetails IS
1603: select distinct audit_procedure_rev_id, pk1,pk2, pk4
1604: from amw_ap_associations
1605: where object_type = 'PROJECT_NEW'
1606: and pk1 = p_audit_project_id
1607: and audit_procedure_rev_id is not null;
1608:
1660: and ara.pk2 = aca.pk2
1661: and ara.pk3 = aca.pk3
1662: and ara.risk_id = aca.pk4);
1663:
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
1672: -- and aca.pk3 IS NOT NULL --process
1673: and aca.control_id = apa.pk3)
1674: and pk2 <> -1 and pk3 <> -1;
1675:
1676: DELETE FROM amw_ap_associations apa
1677: WHERE object_type='PROJECT'
1678: AND pk1 = p_audit_project_id
1679: AND not exists
1680: (select 'Y' from amw_execution_scope aes
1709: and ara.pk2 = aca.pk2
1710: and ara.pk3 IS NULL
1711: and ara.risk_id = aca.pk4);
1712:
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
1721: -- and aca.pk3 IS NULL --process
1722: and aca.control_id = apa.pk3)
1723: and pk2 <> -1 and pk3 <> -1;
1724:
1725: DELETE FROM amw_ap_associations apa
1726: WHERE object_type='PROJECT'
1727: AND pk1 = p_audit_project_id
1728: AND not exists
1729: (select 'Y' from amw_execution_scope aes
1858: AND aca2.control_rev_id = control.control_rev_id
1859: );
1860:
1861:
1862: UPDATE AMW_AP_ASSOCIATIONS apa
1863: SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
1864: FROM amw_audit_procedures_b aapb1
1865: WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
1866: AND aapb1.curr_approved_flag = 'Y')
1867: WHERE apa.object_type = 'PROJECT'
1868: AND apa.pk1 = p_audit_project_id
1869: AND apa.pk2 = scope_rec.organization_id;
1870:
1871: INSERT INTO amw_ap_associations (
1872: ap_association_id,
1873: last_update_date,
1874: last_updated_by,
1875: creation_date,
1882: pk3,
1883: pk4,
1884: object_type,
1885: object_version_number)
1886: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1887: SYSDATE,
1888: FND_GLOBAL.USER_ID,
1889: SYSDATE,
1890: FND_GLOBAL.USER_ID,
1902: aapb.audit_procedure_rev_id,
1903: apa.pk1 organization_id,
1904: apa.pk3 control_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,
1930: AND pt1.task_number = pt2.task_number
1931: and apt.audit_procedure_id = apa.audit_procedure_id
1932: AND pt2.audit_project_id = p_audit_project_id) ttt
1933: WHERE NOT EXISTS
1934: (SELECT 'Y' from amw_ap_associations apa2
1935: where apa2.object_type in ('PROJECT','PROJECT_NEW')
1936: AND apa2.pk1 = p_audit_project_id
1937: AND apa2.pk2 = ttt.organization_id
1938: AND apa2.pk3 = ttt.control_id
1940: AND apa2.audit_procedure_id = ttt.audit_procedure_id
1941: AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
1942: );
1943:
1944: INSERT INTO amw_ap_associations (
1945: ap_association_id,
1946: last_update_date,
1947: last_updated_by,
1948: creation_date,
1955: pk3,
1956: pk4,
1957: object_type,
1958: object_version_number)
1959: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1960: SYSDATE,
1961: FND_GLOBAL.USER_ID,
1962: SYSDATE,
1963: FND_GLOBAL.USER_ID,
1975: aapb.audit_procedure_rev_id,
1976: apa.pk1 organization_id,
1977: apa.pk3 control_id,
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,
2002: AND pt1.task_number = pt2.task_number
2003: and apt.audit_procedure_id = apa.audit_procedure_id
2004: AND pt2.audit_project_id = p_audit_project_id) ttt
2005: WHERE NOT EXISTS
2006: (SELECT 'Y' from amw_ap_associations apa2
2007: where apa2.object_type in ('PROJECT','PROJECT_NEW')
2008: AND apa2.pk1 = p_audit_project_id
2009: AND apa2.pk2 = ttt.organization_id
2010: AND apa2.pk3 = ttt.control_id
2012: AND apa2.audit_procedure_id = ttt.audit_procedure_id
2013: AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2014: );
2015:
2016: INSERT INTO amw_ap_associations (
2017: ap_association_id,
2018: last_update_date,
2019: last_updated_by,
2020: creation_date,
2027: pk3,
2028: pk4,
2029: object_type,
2030: object_version_number)
2031: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2032: sysdate,
2033: fnd_global.user_id,
2034: sysdate,
2035: fnd_global.user_id,
2046: aapb.audit_procedure_id,
2047: aapb.audit_procedure_rev_Id,
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'
2062: AND apa.deletion_date IS NULL
2063: AND apa.audit_procedure_id = aapb.audit_procedure_id
2064: AND aapb.curr_approved_flag='Y') ttt
2065: WHERE NOT EXISTS
2066: (SELECT 'Y' from amw_ap_associations apa2
2067: WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
2068: AND apa2.pk1 = p_audit_project_id
2069: AND apa2.pk2 = ttt.organization_id
2070: AND apa2.pk3 = ttt.control_id
2255: object_name='AMW_ORG_CONTROL') );
2256: END IF;
2257:
2258:
2259: UPDATE AMW_AP_ASSOCIATIONS apa
2260: SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
2261: FROM amw_audit_procedures_b aapb1
2262: WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
2263: AND aapb1.curr_approved_flag = 'Y')
2264: WHERE apa.object_type = 'PROJECT'
2265: AND apa.pk1 = p_audit_project_id
2266: AND apa.pk2 = scope_org_rec.organization_id;
2267:
2268: INSERT INTO amw_ap_associations (
2269: ap_association_id,
2270: last_update_date,
2271: last_updated_by,
2272: creation_date,
2279: pk3,
2280: pk4,
2281: object_type,
2282: object_version_number)
2283: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2284: SYSDATE,
2285: FND_GLOBAL.USER_ID,
2286: SYSDATE,
2287: FND_GLOBAL.USER_ID,
2299: aapb.audit_procedure_rev_id,
2300: apa.pk1 organization_id,
2301: apa.pk2 control_id,
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,
2325: AND pt1.task_number = pt2.task_number
2326: and apt.audit_procedure_id = apa.audit_procedure_id
2327: AND pt2.audit_project_id = p_audit_project_id) ttt
2328: WHERE NOT EXISTS
2329: (SELECT 'Y' from amw_ap_associations apa2
2330: where apa2.object_type in ('PROJECT','PROJECT_NEW')
2331: AND apa2.pk1 = p_audit_project_id
2332: AND apa2.pk2 = ttt.organization_id
2333: AND apa2.pk3 = ttt.control_id
2335: AND apa2.audit_procedure_id = ttt.audit_procedure_id
2336: AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2337: );
2338:
2339: INSERT INTO amw_ap_associations (
2340: ap_association_id,
2341: last_update_date,
2342: last_updated_by,
2343: creation_date,
2350: pk3,
2351: pk4,
2352: object_type,
2353: object_version_number)
2354: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2355: SYSDATE,
2356: FND_GLOBAL.USER_ID,
2357: SYSDATE,
2358: FND_GLOBAL.USER_ID,
2370: aapb.audit_procedure_rev_id,
2371: apa.pk1 organization_id,
2372: apa.pk2 control_id,
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,
2395: AND pt1.task_number = pt2.task_number
2396: and apt.audit_procedure_id = apa.audit_procedure_id
2397: AND pt2.audit_project_id = p_audit_project_id) ttt
2398: WHERE NOT EXISTS
2399: (SELECT 'Y' from amw_ap_associations apa2
2400: where apa2.object_type in ('PROJECT','PROJECT_NEW')
2401: AND apa2.pk1 = p_audit_project_id
2402: AND apa2.pk2 = ttt.organization_id
2403: AND apa2.pk3 = ttt.control_id
2405: AND apa2.audit_procedure_id = ttt.audit_procedure_id
2406: AND apa2.audit_procedure_rev_id = ttt.audit_procedure_rev_id
2407: );
2408:
2409: INSERT INTO amw_ap_associations (
2410: ap_association_id,
2411: last_update_date,
2412: last_updated_by,
2413: creation_date,
2420: pk3,
2421: pk4,
2422: object_type,
2423: object_version_number)
2424: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2425: sysdate,
2426: fnd_global.user_id,
2427: sysdate,
2428: fnd_global.user_id,
2439: aapb.audit_procedure_id,
2440: aapb.audit_procedure_rev_Id,
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'
2453: AND apa.audit_procedure_id = aapb.audit_procedure_id
2454: AND apa.association_creation_date IS NOT NULL
2455: AND aapb.curr_approved_flag='Y') ttt
2456: WHERE NOT EXISTS
2457: (SELECT 'Y' from amw_ap_associations apa2
2458: WHERE apa2.object_type in ('PROJECT','PROJECT_NEW')
2459: AND apa2.pk1 = p_audit_project_id
2460: AND apa2.pk2 = ttt.organization_id
2461: AND apa2.pk3 = ttt.control_id
2482:
2483: END LOOP; -- end of FOR aapdetails_rec IN c_apdetails LOOP
2484:
2485: -- Change entity_type 'PROJECT_NEW' to 'PROJECT'
2486: UPDATE AMW_AP_ASSOCIATIONS SET object_type='PROJECT' WHERE object_type = 'PROJECT_NEW';
2487: --- Copy Attachment ends here
2488:
2489: FOR ap_attachment_rec IN c_ap_attachments LOOP
2490: -- Delete all the attachments for the audit procedure that is not present in the project
2714: and ara.risk_id = aca.pk4);
2715:
2716: /* Entity risk/control/ap changes end*/
2717:
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
2725: AND aca.pk2 = apa.pk2
2726: AND aca.control_id = apa.pk3)
2727: and pk2 <> -1 and pk3 <> -1;
2728:
2729: DELETE FROM amw_ap_associations apa
2730: WHERE object_type = p_entity_type
2731: AND pk1 = p_entity_id
2732: AND not exists
2733: (select 'Y' from amw_execution_scope aes
2856: AND aca2.control_id = control.control_id
2857: AND aca2.control_rev_id = control.control_rev_id
2858: );
2859:
2860: UPDATE amw_ap_associations apa
2861: SET apa.audit_procedure_rev_id = (SELECT audit_procedure_rev_id
2862: FROM amw_audit_procedures_b aapb1
2863: WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
2864: AND aapb1.curr_approved_flag = 'Y')
2865: WHERE apa.object_type = p_entity_type
2866: AND apa.pk1 = p_entity_id
2867: AND apa.pk2 = each_rec.organization_id;
2868:
2869: INSERT INTO amw_ap_associations (
2870: ap_association_id,
2871: last_update_date,
2872: last_updated_by,
2873: creation_date,
2879: pk2,
2880: pk3,
2881: object_type,
2882: object_version_number)
2883: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
2884: sysdate,
2885: fnd_global.user_id,
2886: sysdate,
2887: fnd_global.user_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
2909: AND apa.pk2 = aca.pk2 -- process_id
2910: AND apa.audit_procedure_id = aapb.audit_procedure_id
2911: AND aapb.curr_approved_flag='Y'
2912: AND NOT EXISTS
2913: (SELECT 'Y' FROM amw_ap_associations apa2
2914: WHERE apa2.object_type = p_entity_type
2915: AND apa2.pk1 = p_entity_id
2916: AND apa2.pk2 = each_rec.organization_id
2917: AND apa2.pk3 = aca.control_id
3044: AND aca2.control_rev_id = control.control_rev_id
3045: );
3046:
3047:
3048: UPDATE AMW_AP_ASSOCIATIONS apa
3049: SET apa.audit_procedure_rev_id =
3050: (SELECT audit_procedure_rev_id
3051: FROM amw_audit_procedures_b aapb1
3052: WHERE aapb1.audit_procedure_id = apa.audit_procedure_id
3055: AND apa.pk1 = p_entity_id
3056: AND apa.pk2 = scope_org_rec.organization_id;
3057:
3058:
3059: INSERT INTO amw_ap_associations (
3060: ap_association_id,
3061: last_update_date,
3062: last_updated_by,
3063: creation_date,
3069: pk2,
3070: pk3,
3071: object_type,
3072: object_version_number)
3073: SELECT AMW_AP_ASSOCIATIONS_S.nextval,
3074: sysdate,
3075: fnd_global.user_id,
3076: sysdate,
3077: fnd_global.user_id,
3088: aapb.audit_procedure_id,
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
3101: AND apa.association_creation_date IS NOT NULL
3102: AND apa.audit_procedure_id = aapb.audit_procedure_id
3103: AND aapb.curr_approved_flag='Y'
3104: AND NOT EXISTS
3105: (SELECT 'Y' FROM amw_ap_associations apa2
3106: WHERE apa2.object_type = p_entity_type
3107: AND apa2.pk1 = p_entity_id
3108: AND apa2.pk2 = scope_org_rec.organization_id
3109: AND apa2.pk3 = aca.control_id