[Home] [Help]
199: p_org_new_tbl => p_org_new_tbl);
200:
201: FOR each_rec IN 1..p_org_tbl.count
202: loop
203: delete from amw_execution_scope where entity_type = p_entity_type
204: and entity_id = p_entity_id and organization_id = p_org_tbl(each_rec).org_id;
205: end loop;
206:
207: -- Create a new list of subsidiaries
241: --Step 8 : Find parents of LOBs all the way till the root node
242: --Step 9 : Populate table with LOB hierarchy
243: --Step 10: Find CustomORGs based on HR hierarchy all the way till the root node
244: --Step 11: Populate selected CustomORGS into table and build hierarchy
245: --Step 12: Call to populate amw_execution_scope with processes/org relation
246: --Step 13: Call to populate association tables with object_type = 'BUSIPROC_CERTIFICATION'
247:
248: --Step 1 : Find all relevant organizations
249: -- loop through all organizations in the list
595:
596: -- the following "if" is added to make this procedure
597: -- reusable in finstmt_cert.
598: IF p_entity_type <> 'FINSTMT_CERTIFICATION' THEN
599: --Step 12: Call to populate amw_execution_scope with processes information
600: populate_process_hierarchy
601: (
602: p_entity_type => p_entity_type,
603: p_entity_id => p_entity_id,
1082:
1083: -- Initialize API return status to SUCCESS
1084: x_return_status := FND_API.G_RET_STS_SUCCESS;
1085:
1086: /* DELETE FROM AMW_EXECUTION_SCOPE
1087: WHERE entity_id = p_entity_id
1088: AND entity_type = p_entity_type
1089: and level_id < 4;*/
1090:
1091: FOR each_rec IN 1..p_org_tbl.count
1092: LOOP
1093: FOR audit_rec IN c_audit_unit(p_org_tbl(each_rec).org_id)
1094: LOOP
1095: INSERT INTO AMW_EXECUTION_SCOPE (
1096: EXECUTION_SCOPE_ID,
1097: ENTITY_TYPE,
1098: ENTITY_ID,
1099: CREATED_BY,
1112: TOP_PROCESS_ID,
1113: PARENT_PROCESS_ID,
1114: PROCESS_ORG_REV_ID,
1115: SCOPE_MODIFIED_DATE)
1116: SELECT amw_execution_scope_s.nextval,
1117: p_entity_type,
1118: p_entity_id,
1119: FND_GLOBAL.USER_ID,
1120: SYSDATE,
1134: null,
1135: sysdate
1136: FROM DUAL
1137: WHERE not exists (SELECT 'Y'
1138: FROM AMW_EXECUTION_SCOPE
1139: WHERE entity_type=p_entity_type
1140: AND entity_id= p_entity_id
1141: AND subsidiary_vs = audit_rec.subsidiary_valueset
1142: AND subsidiary_code= audit_rec.company_code
1154: END IF;
1155:
1156: IF l_sub_lob_exists IS NOT NULL AND l_sub_lob_exists='Y'
1157: THEN
1158: INSERT INTO AMW_EXECUTION_SCOPE (
1159: EXECUTION_SCOPE_ID,
1160: ENTITY_TYPE,
1161: ENTITY_ID,
1162: CREATED_BY,
1175: TOP_PROCESS_ID,
1176: PARENT_PROCESS_ID,
1177: PROCESS_ORG_REV_ID,
1178: SCOPE_MODIFIED_DATE)
1179: SELECT amw_execution_scope_s.nextval,
1180: p_entity_type,
1181: p_entity_id,
1182: FND_GLOBAL.USER_ID,
1183: SYSDATE,
1197: null,
1198: SYSDATE
1199: FROM DUAL
1200: WHERE not exists (SELECT 'Y'
1201: FROM AMW_EXECUTION_SCOPE
1202: WHERE entity_type=p_entity_type
1203: AND entity_id= p_entity_id
1204: AND subsidiary_vs = audit_rec.subsidiary_valueset
1205: AND subsidiary_code= audit_rec.company_code
1206: AND lob_vs = audit_rec.lob_valueset
1207: AND lob_code = audit_rec.lob_code
1208: AND level_id=2);
1209:
1210: INSERT INTO AMW_EXECUTION_SCOPE (
1211: EXECUTION_SCOPE_ID,
1212: ENTITY_TYPE,
1213: ENTITY_ID,
1214: CREATED_BY,
1227: TOP_PROCESS_ID,
1228: PARENT_PROCESS_ID,
1229: PROCESS_ORG_REV_ID,
1230: SCOPE_MODIFIED_DATE )
1231: SELECT amw_execution_scope_s.nextval,
1232: p_entity_type,
1233: p_entity_id,
1234: FND_GLOBAL.USER_ID,
1235: SYSDATE,
1249: null,
1250: SYSDATE
1251: FROM DUAL
1252: WHERE NOT EXISTS (SELECT 'Y'
1253: FROM AMW_EXECUTION_SCOPE
1254: WHERE entity_type=p_entity_type
1255: AND entity_id= p_entity_id
1256: AND subsidiary_vs = audit_rec.subsidiary_valueset
1257: AND subsidiary_code= audit_rec.company_code
1259: AND lob_code = audit_rec.lob_code
1260: AND organization_id = audit_rec.organization_id
1261: AND level_id=3);
1262: ELSE
1263: INSERT INTO AMW_EXECUTION_SCOPE (
1264: EXECUTION_SCOPE_ID,
1265: ENTITY_TYPE,
1266: ENTITY_ID,
1267: CREATED_BY,
1280: TOP_PROCESS_ID,
1281: PARENT_PROCESS_ID,
1282: PROCESS_ORG_REV_ID,
1283: SCOPE_MODIFIED_DATE)
1284: SELECT amw_execution_scope_s.nextval,
1285: p_entity_type,
1286: p_entity_id,
1287: FND_GLOBAL.USER_ID,
1288: SYSDATE,
1302: null,
1303: SYSDATE
1304: FROM DUAL
1305: WHERE not exists (SELECT 'Y'
1306: FROM AMW_EXECUTION_SCOPE
1307: WHERE entity_type=p_entity_type
1308: AND entity_id= p_entity_id
1309: AND subsidiary_vs = audit_rec.subsidiary_valueset
1310: AND subsidiary_code= audit_rec.company_code
1312: AND lob_code = 'AMW_DUMMY_LOBCODE'
1313: AND level_id=2);
1314:
1315:
1316: INSERT INTO AMW_EXECUTION_SCOPE (
1317: EXECUTION_SCOPE_ID,
1318: ENTITY_TYPE,
1319: ENTITY_ID,
1320: CREATED_BY,
1333: TOP_PROCESS_ID,
1334: PARENT_PROCESS_ID,
1335: PROCESS_ORG_REV_ID,
1336: SCOPE_MODIFIED_DATE )
1337: SELECT amw_execution_scope_s.nextval,
1338: p_entity_type,
1339: p_entity_id,
1340: FND_GLOBAL.USER_ID,
1341: SYSDATE,
1355: null,
1356: SYSDATE
1357: FROM DUAL
1358: WHERE not exists (SELECT 'Y'
1359: FROM AMW_EXECUTION_SCOPE
1360: WHERE entity_type=p_entity_type
1361: AND entity_id= p_entity_id
1362: AND subsidiary_vs = audit_rec.subsidiary_valueset
1363: AND subsidiary_code= audit_rec.company_code
1395: l_lob_code,
1396: l_lob_valueset;
1397: EXIT WHEN process_cursor%NOTFOUND;
1398:
1399: INSERT INTO AMW_EXECUTION_SCOPE (
1400: EXECUTION_SCOPE_ID,
1401: ENTITY_TYPE,
1402: ENTITY_ID,
1403: CREATED_BY,
1416: TOP_PROCESS_ID,
1417: PARENT_PROCESS_ID,
1418: PROCESS_ORG_REV_ID,
1419: SCOPE_MODIFIED_DATE)
1420: SELECT amw_execution_scope_s.nextval,
1421: p_entity_type,
1422: p_entity_id,
1423: FND_GLOBAL.USER_ID,
1424: SYSDATE,
1438: l_process_org_rev_id,
1439: SYSDATE
1440: FROM DUAL
1441: WHERE not exists (SELECT 'Y'
1442: FROM AMW_EXECUTION_SCOPE
1443: WHERE entity_type=p_entity_type
1444: AND entity_id= p_entity_id
1445: AND subsidiary_vs = l_subsidiary_valueset
1446: AND subsidiary_code= l_company_code
1502: BEGIN
1503: FOR proc_rec IN c_process LOOP
1504: Insert_Process (p_level_id+1,proc_rec.process_id,p_top_process_id,proc_rec.process_org_rev_id, p_subsidiary_vs,
1505: p_subsidiary_code,p_lob_vs,p_lob_code,p_organization_id,p_entity_type,p_entity_id);
1506: INSERT INTO AMW_EXECUTION_SCOPE (
1507: EXECUTION_SCOPE_ID,
1508: ENTITY_TYPE,
1509: ENTITY_ID,
1510: CREATED_BY,
1523: TOP_PROCESS_ID,
1524: PARENT_PROCESS_ID,
1525: PROCESS_ORG_REV_ID,
1526: SCOPE_MODIFIED_DATE)
1527: SELECT amw_execution_scope_s.nextval,
1528: p_entity_type,
1529: p_entity_id,
1530: FND_GLOBAL.USER_ID,
1531: SYSDATE,
1568: CURSOR c_project_scope_org IS
1569: SELECT organization_id,
1570: process_id,
1571: scope_changed_status
1572: FROM AMW_EXECUTION_SCOPE
1573: WHERE entity_type='PROJECT'
1574: AND entity_id=p_audit_project_id
1575: AND organization_id IS NOT NULL
1576: AND process_id IS NULL
1579: CURSOR c_project_scope IS
1580: SELECT organization_id,
1581: process_id,
1582: scope_changed_status
1583: FROM AMW_EXECUTION_SCOPE
1584: WHERE entity_type='PROJECT'
1585: AND entity_id=p_audit_project_id
1586: AND process_id IS NOT NULL
1587: AND SCOPE_CHANGED_STATUS = 'C';
1642: AND pk1 = p_audit_project_id
1643: AND pk3 IS NOT NULL
1644: AND not exists
1645: (select 'Y'
1646: from amw_execution_scope
1647: where entity_type='PROJECT'
1648: and entity_id = p_audit_project_id
1649: and organization_id = ara.pk2
1650: and process_id = ara.pk3);
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
1681: where aes.entity_type = 'PROJECT'
1682: and aes.entity_id = p_audit_project_id
1683: and aes.organization_id = apa.pk2)
1684: and pk3 = -1
1691: AND pk1 = p_audit_project_id
1692: AND pk3 IS NULL
1693: AND not exists
1694: (select 'Y'
1695: from amw_execution_scope
1696: where entity_type='PROJECT'
1697: and entity_id = p_audit_project_id
1698: and organization_id = ara.pk2
1699: and process_id IS NULL);
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
1730: where aes.entity_type = 'PROJECT'
1731: and aes.entity_id = p_audit_project_id
1732: and aes.organization_id = apa.pk2)
1733: and pk3 = -1
2495: X_pk4_value => ap_attachment_rec.pk4_value);
2496: END LOOP; -- end of FOR ap_attachment_rec IN c_ap_attachments LOOP
2497:
2498:
2499: UPDATE AMW_EXECUTION_SCOPE
2500: SET SCOPE_CHANGED_STATUS = null
2501: WHERE entity_type='PROJECT'
2502: AND entity_id=p_audit_project_id
2503: AND SCOPE_CHANGED_STATUS = 'C';
2531:
2532: DELETE FROM amw_proc_cert_eval_sum pcert
2533: WHERE certification_id = p_entity_id
2534: AND NOT EXISTS (SELECT 'Y'
2535: FROM amw_execution_scope exec
2536: WHERE exec.entity_id = pcert.certification_id
2537: AND exec.entity_type = p_entity_type
2538: AND exec.process_id = pcert.process_id
2539: AND exec.organization_id = pcert.organization_id
2557: SYSDATE,
2558: fnd_global.user_id,
2559: SYSDATE,
2560: fnd_global.user_id
2561: FROM amw_execution_scope exec
2562: WHERE NOT EXISTS (SELECT 'Y'
2563: FROM amw_proc_cert_eval_sum pcert
2564: WHERE pcert.certification_id = exec.entity_id
2565: AND pcert.organization_id = exec.organization_id
2585: SYSDATE,
2586: fnd_global.user_id,
2587: SYSDATE,
2588: fnd_global.user_id
2589: FROM amw_execution_scope exec
2590: WHERE NOT EXISTS (SELECT 'Y'
2591: FROM amw_org_cert_eval_sum ocert
2592: WHERE ocert.certification_id = exec.entity_id
2593: AND ocert.organization_id = exec.organization_id
2614: ) IS
2615:
2616: CURSOR get_records_in_scope IS
2617: SELECT organization_id,process_id, scope_changed_status
2618: FROM AMW_EXECUTION_SCOPE
2619: WHERE entity_type = p_entity_type
2620: AND entity_id = p_entity_id
2621: AND scope_changed_status = 'C'
2622: AND process_id IS NOT NULL
2626: CURSOR c_scope_org IS
2627: SELECT organization_id,
2628: process_id,
2629: scope_changed_status
2630: FROM AMW_EXECUTION_SCOPE
2631: WHERE entity_type=p_entity_type
2632: AND entity_id=p_entity_id
2633: AND organization_id IS NOT NULL
2634: AND process_id IS NULL
2665: AND pk1 = p_entity_id
2666: AND pk3 IS NOT NULL
2667: AND NOT EXISTS
2668: (SELECT 'Y'
2669: FROM AMW_EXECUTION_SCOPE
2670: WHERE entity_type = p_entity_type
2671: AND entity_id = p_entity_id
2672: AND organization_id = ara.pk2
2673: AND process_id = ara.pk3);
2694: AND pk1 = p_entity_id
2695: AND pk3 IS NULL
2696: AND not exists
2697: (select 'Y'
2698: from amw_execution_scope
2699: where entity_type=p_entity_type
2700: and entity_id = p_entity_id
2701: and organization_id = ara.pk2
2702: and process_id IS NULL);
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
2734: where aes.entity_type = p_entity_type
2735: and aes.entity_id = p_entity_id
2736: and aes.organization_id = apa.pk2)
2737: and pk3 = -1
3114:
3115:
3116: END LOOP; -- FOR scope_org_rec IN c_project_scope_org LOOP
3117:
3118: UPDATE amw_execution_scope
3119: SET SCOPE_CHANGED_STATUS = null
3120: WHERE entity_type = p_entity_type
3121: AND entity_id = p_entity_id
3122: AND SCOPE_CHANGED_STATUS = 'C';
3259: )
3260: IS
3261: CURSOR c_scope_org IS
3262: SELECT SUBSIDIARY_VS, SUBSIDIARY_CODE,LOB_VS, LOB_CODE
3263: FROM AMW_EXECUTION_SCOPE
3264: WHERE entity_type = p_entity_type
3265: AND entity_id = p_entity_id
3266: AND organization_id = p_organization_id
3267: FOR UPDATE NOWAIT;
3267: FOR UPDATE NOWAIT;
3268:
3269: CURSOR get_proc_org_rev_id(p_process_id NUMBER) IS
3270: SELECT aorv.child_process_org_rev_id
3271: FROM amw_curr_app_hierarchy_org_v aorv,amw_execution_scope aes,amw_lookups lk
3272: WHERE aorv.child_organization_id = aes.organization_id(+)
3273: AND aorv.child_process_id = aes.process_id(+)
3274: AND aorv.child_process_id <> -2
3275: AND lk.lookup_type = 'AMW_SCOPE_ENTITY_TYPE'
3321: FETCH c_scope_org INTO l_subsidiary_vs, l_sub_code,l_lob_vs, l_lob_code;
3322: CLOSE c_scope_org;
3323:
3324: --Step 1: Remove existing processes rows
3325: DELETE FROM amw_execution_scope
3326: WHERE entity_type = p_entity_type
3327: AND entity_id = p_entity_id
3328: AND organization_id = p_organization_id
3329: AND level_id > 3;
3336: OPEN get_proc_org_rev_id(p_proc_hier_tbl(i).process_id);
3337: FETCH get_proc_org_rev_id INTO l_process_org_rev_id;
3338: CLOSE get_proc_org_rev_id;
3339:
3340: INSERT INTO AMW_EXECUTION_SCOPE (
3341: EXECUTION_SCOPE_ID,
3342: ENTITY_TYPE,
3343: ENTITY_ID,
3344: CREATED_BY,
3357: TOP_PROCESS_ID,
3358: PARENT_PROCESS_ID,
3359: PROCESS_ORG_REV_ID,
3360: SCOPE_MODIFIED_DATE)
3361: SELECT amw_execution_scope_s.nextval,
3362: p_entity_type,
3363: p_entity_id,
3364: FND_GLOBAL.USER_ID,
3365: SYSDATE,
3377: p_proc_hier_tbl(i).top_process_id,
3378: p_proc_hier_tbl(i).parent_process_id,
3379: l_process_org_rev_id,
3380: SYSDATE
3381: FROM dual where not exists (select 'Y' from amw_execution_scope
3382: where entity_type = p_entity_type and entity_id = p_entity_id
3383: and organization_id = p_organization_id and process_id = p_proc_hier_tbl(i).process_id
3384: and top_process_id = p_proc_hier_tbl(i).top_process_id and parent_process_id = p_proc_hier_tbl(i).parent_process_id
3385: and level_id = p_proc_hier_tbl(i).level_id
3391: p_process_tbl(i).process_id := p_proc_hier_tbl(i).process_id;
3392: END LOOP;
3393:
3394: --Step 2.1: Set the scope_changed_status for the org also.
3395: UPDATE amw_execution_scope
3396: SET scope_changed_status = 'C'
3397: WHERE entity_type = p_entity_type
3398: AND entity_id = p_entity_id
3399: AND organization_id = p_organization_id
3501: l_return_status VARCHAR2(32767);
3502: l_msg_count NUMBER;
3503: l_msg_data VARCHAR2(32767);
3504:
3505: -- To delete all the relevant entries from AMW_EXECUTION_SCOPE TABLE
3506:
3507: CURSOR c_audit_unit(p_org_id NUMBER)
3508: IS
3509: SELECT audit_v.company_code,
3591: FOR audit_rec IN c_audit_unit(l_object_tbl(each_rec).org_id)
3592: LOOP
3593: If (p_object_type = 'SUBSIDIARY')
3594: THEN
3595: DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
3596: and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
3597: and SUBSIDIARY_CODE = audit_rec.company_code;
3598: END IF;
3599:
3598: END IF;
3599:
3600: If (p_object_type = 'LINEOFBUSINESS')
3601: THEN
3602: DELETE FROM AMW_EXECUTION_SCOPE WHERE entity_id = p_entity_id
3603: and entity_type = p_entity_type and subsidiary_vs = audit_rec.subsidiary_valueset
3604: and SUBSIDIARY_CODE = audit_rec.company_code
3605: AND NVL(lob_vs, 'AMW_NULL_CODE') = NVL(audit_rec.lob_valueset, NVL(lob_vs, 'AMW_NULL_CODE'))
3606: AND NVL(lob_code, 'AMW_NULL_CODE') = NVL(audit_rec.lob_code, NVL(lob_code, 'AMW_NULL_CODE'))
3622: x_msg_data => l_msg_data
3623: );
3624: END IF;
3625:
3626: --Step 5: Delete all relevant rows in AMW_EXECUTION_SCOPE
3627: DELETE FROM AMW_EXECUTION_SCOPE
3628: WHERE entity_type = p_entity_type
3629: AND entity_id = p_entity_id
3630: AND subsidiary_vs = p_subsidiary_vs
3623: );
3624: END IF;
3625:
3626: --Step 5: Delete all relevant rows in AMW_EXECUTION_SCOPE
3627: DELETE FROM AMW_EXECUTION_SCOPE
3628: WHERE entity_type = p_entity_type
3629: AND entity_id = p_entity_id
3630: AND subsidiary_vs = p_subsidiary_vs
3631: AND subsidiary_code = p_subsidiary_code
3890: DELETE FROM amw_audit_scope_organizations
3891: WHERE audit_project_id = p_audit_project_id
3892: AND organization_id NOT IN
3893: (SELECT organization_id
3894: FROM amw_execution_scope
3895: WHERE entity_type = 'PROJECT'
3896: AND entity_id = p_audit_project_id
3897: AND level_id = 3);
3898:
3920: g_user_id,
3921: sysdate,
3922: g_login_id,
3923: 1
3924: FROM amw_audit_units_v au, amw_execution_scope es
3925: WHERE au.organization_id = es.organization_id
3926: AND es.entity_type = 'PROJECT'
3927: AND es.entity_id = p_audit_project_id
3928: AND es.level_id = 3
3934: DELETE FROM amw_audit_scope_processes
3935: WHERE audit_project_id = p_audit_project_id
3936: AND (organization_id, process_id) NOT IN
3937: (SELECT organization_id, process_id
3938: FROM amw_execution_scope
3939: WHERE entity_type = 'PROJECT'
3940: AND entity_id = p_audit_project_id
3941: AND process_id IS NOT NULL);
3942:
3960: g_user_id,
3961: sysdate,
3962: g_login_id,
3963: 1
3964: FROM amw_execution_scope
3965: WHERE entity_type = 'PROJECT'
3966: AND entity_id = p_audit_project_id
3967: AND level_id > 3
3968: AND (organization_id, process_id) NOT IN (
4107: px_proc_ids IN OUT NOCOPY VARCHAR2)
4108: IS
4109: CURSOR c_sub_procs IS
4110: SELECT process_id
4111: FROM amw_execution_scope
4112: WHERE entity_id = p_entity_id
4113: AND entity_type = p_entity_type
4114: AND organization_id = p_org_id
4115: AND parent_process_id = p_proc_id;
4145: x_proc_ids OUT NOCOPY VARCHAR2)
4146: IS
4147: CURSOR c_root_procs IS
4148: SELECT process_id
4149: FROM amw_execution_scope
4150: WHERE entity_id = p_entity_id
4151: AND entity_type = p_entity_type
4152: AND level_id=4;
4153: