eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMW_PROCESS_CERT_SUMMARY dependencies on AMW_EXECUTION_SCOPE

Line 138: FROM amw_execution_scope

134: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
135: AND ara.pk1 = p_certification_id
136: AND ara.pk2 = p_org_id
137: AND ara.pk3 IN (SELECT DISTINCT process_id
138: FROM amw_execution_scope
139: START WITH process_id = p_process_id
140: AND organization_id = p_org_id
141: AND entity_id = p_certification_id
142: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 171: FROM amw_execution_scope

167: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
168: AND ara.pk1 = p_certification_id
169: AND ara.pk2 = p_org_id
170: AND ara.pk3 IN (SELECT DISTINCT process_id
171: FROM amw_execution_scope
172: START WITH process_id = p_process_id
173: AND organization_id = p_org_id
174: AND entity_id = p_certification_id
175: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 199: FROM amw_execution_scope

195: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
196: AND ara.pk1 = p_certification_id
197: AND ara.pk2 = p_org_id
198: AND ara.pk3 IN (SELECT DISTINCT process_id
199: FROM amw_execution_scope
200: START WITH process_id = p_process_id
201: AND organization_id = p_org_id
202: AND entity_id = p_certification_id
203: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 224: FROM amw_execution_scope

220: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
221: AND aca.pk1 = p_certification_id
222: AND aca.pk2 = p_org_id
223: AND aca.pk3 IN (SELECT DISTINCT process_id
224: FROM amw_execution_scope
225: START WITH process_id = p_process_id
226: AND organization_id = p_org_id
227: AND entity_id = p_certification_id
228: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 258: FROM amw_execution_scope

254: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
255: AND aca.pk1 = p_certification_id
256: AND aca.pk2 = p_org_id
257: AND aca.pk3 IN (SELECT DISTINCT process_id
258: FROM amw_execution_scope
259: START WITH process_id = p_process_id
260: AND organization_id = p_org_id
261: AND entity_id = p_certification_id
262: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 285: FROM amw_execution_scope

281: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
282: AND aca.pk1 = p_certification_id
283: AND aca.pk2 = p_org_id
284: AND aca.pk3 IN (SELECT DISTINCT process_id
285: FROM amw_execution_scope
286: START WITH process_id = p_process_id
287: AND organization_id = p_org_id
288: AND entity_id = p_certification_id
289: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 301: FROM amw_execution_scope processorg

297:
298: CURSOR get_total_org_certified
299: IS
300: SELECT count(distinct processorg.organization_id)
301: FROM amw_execution_scope processorg
302: WHERE processorg.process_id = p_process_id
303: AND processorg.organization_id IN
304: (SELECT object_id
305: FROM amw_entity_hierarchies

Line 321: FROM amw_execution_scope scp,

317: CURSOR get_var_total_org_certified
318: IS
319: SELECT count(1)
320: FROM (SELECT distinct procorg.organization_id, procorg.process_id
321: FROM amw_execution_scope scp,
322: amw_process_organization procorg
323: WHERE scp.process_org_rev_id = procorg.process_org_rev_id
324: AND procorg.standard_variation IN
325: (select process_rev_id

Line 362: AND exists (select 'Y' from amw_execution_scope scope

358: CONNECT BY parent_object_id = PRIOR object_id
359: AND parent_object_type = PRIOR object_type
360: AND entity_id = PRIOR entity_id
361: AND entity_type = PRIOR entity_type)
362: AND exists (select 'Y' from amw_execution_scope scope
363: where scope.entity_type='BUSIPROC_CERTIFICATION'
364: and scope.entity_id=p_certification_id
365: and scope.organization_id=opinion.pk3_value
366: and scope.process_id=opinion.pk1_value);

Line 373: amw_execution_scope scp,

369: IS
370: SELECT count(1)
371: FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
372: FROM amw_opinions_v opinion,
373: amw_execution_scope scp,
374: amw_process_organization procorg
375: WHERE opinion.pk2_value = p_certification_id
376: AND opinion.pk1_value = scp.process_id
377: AND opinion.pk3_value = scp.organization_id

Line 403: FROM amw_execution_scope

399:
400: CURSOR get_all_sub_processes
401: IS
402: SELECT count(distinct process_id)
403: FROM amw_execution_scope
404: START WITH parent_process_id = p_process_id
405: AND organization_id = p_org_id
406: AND entity_id = p_certification_id
407: ---07.05.2005 npanandi: add entityType, bugfix 4471783

Line 418: FROM amw_execution_scope amw_exec

414:
415: CURSOR get_certified_sub_processes
416: IS
417: SELECT count(distinct process_id)
418: FROM amw_execution_scope amw_exec
419: WHERE EXISTS (SELECT opinion.opinion_id
420: FROM amw_opinions_v opinion
421: WHERE opinion.pk1_value = amw_exec.process_id
422: AND opinion.pk3_value = p_org_id

Line 441: FROM amw_execution_scope amw_exec

437:
438: CURSOR get_sub_process_cert_issues
439: IS
440: SELECT count(distinct process_id)
441: FROM amw_execution_scope amw_exec
442: WHERE EXISTS (SELECT opinion.opinion_id
443: FROM amw_opinions_v opinion
444: WHERE opinion.pk1_value = amw_exec.process_id
445: AND opinion.pk3_value = p_org_id

Line 483: AND exists (select 'Y' from amw_execution_scope scope

479: CONNECT BY parent_object_id = PRIOR object_id
480: AND parent_object_type = PRIOR object_type
481: AND entity_id = PRIOR entity_id
482: AND entity_type = PRIOR entity_type)
483: AND exists (select 'Y' from amw_execution_scope scope
484: where scope.entity_type='BUSIPROC_CERTIFICATION'
485: and scope.entity_id=p_certification_id
486: and scope.organization_id=opinion.pk3_value
487: and scope.process_id=opinion.pk1_value);

Line 494: amw_execution_scope scp,

490: IS
491: SELECT count(1)
492: FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
493: FROM amw_opinions_v opinion,
494: amw_execution_scope scp,
495: amw_process_organization procorg
496: WHERE opinion.pk2_value = p_certification_id
497: AND opinion.pk1_value = scp.process_id
498: AND opinion.pk3_value = scp.organization_id

Line 809: FROM amw_execution_scope

805: -- select all processes in scope for the certification
806: CURSOR get_all_processes
807: IS
808: SELECT DISTINCT process_id, organization_id
809: FROM amw_execution_scope
810: WHERE entity_type = 'BUSIPROC_CERTIFICATION'
811: AND entity_id = p_certification_id
812: AND process_id IS NOT NULL;
813:

Line 916: FROM AMW_CURR_APPROVED_REV_ORG_V apo, amw_execution_scope aes

912: FROM AMW_RISK_ASSOCIATIONS
913: WHERE creation_date >= p_start_date
914: AND object_type = 'PROCESS_ORG'
915: AND pk1 in (SELECT apo.process_organization_id
916: FROM AMW_CURR_APPROVED_REV_ORG_V apo, amw_execution_scope aes
917: WHERE apo.process_id = aes.process_id
918: AND apo.organization_id = aes.organization_id
919: AND aes.entity_type = 'BUSIPROC_CERTIFICATION'
920: AND aes.entity_id = p_certification_id);*/

Line 924: amw_execution_scope aes,

920: AND aes.entity_id = p_certification_id);*/
921: SELECT count(1)
922: from (select distinct ara.risk_id, ara.pk1, ara.pk2
923: FROM AMW_RISK_ASSOCIATIONS ara,
924: amw_execution_scope aes,
925: ---05.24.2005 npanandi: added AmwCertificationB, AmwGlPeriodsV
926: ---in the joins below
927: amw_certification_b acb,
928: amw_gl_periods_v period,

Line 959: AMW_EXECUTION_SCOPE aes,

955: SELECT count(1)
956: FROM (SELECT distinct aca.control_id, aes.organization_id
957: FROM AMW_CONTROL_ASSOCIATIONS aca,
958: AMW_RISK_ASSOCIATIONS ara,
959: AMW_EXECUTION_SCOPE aes,
960: ---05.24.2005 npanandi: added AmwCertificationB, AmwGlPeriodsV
961: ---in the joins below
962: amw_certification_b acb,
963: amw_gl_periods_v period,

Line 992: FROM AMW_EXECUTION_SCOPE aes

988:
989: CURSOR global_proc_not_certified IS
990: SELECT count(1)
991: FROM (SELECT distinct aes.organization_id, aes.process_id
992: FROM AMW_EXECUTION_SCOPE aes
993: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
994: AND aes.entity_id = p_certification_id
995: AND aes.level_id > 3
996: AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')

Line 1005: --suggest to use amw_proc_cert_eval_sum instead of amw_execution_scope because it can sync with ProcCertIssuesVO definition.

1001: AND aov.pk3_value = aes.organization_id
1002: AND aov.pk2_value = p_certification_id
1003: AND aov.pk1_value = aes.process_id));
1004: --modified by dliao on 10.06.05, add distinct on select statement.
1005: --suggest to use amw_proc_cert_eval_sum instead of amw_execution_scope because it can sync with ProcCertIssuesVO definition.
1006:
1007: CURSOR global_proc_with_issue IS
1008: SELECT count(1)
1009: FROM (SELECT distinct aes.organization_id, aes.process_id

Line 1010: FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov

1006:
1007: CURSOR global_proc_with_issue IS
1008: SELECT count(1)
1009: FROM (SELECT distinct aes.organization_id, aes.process_id
1010: FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov
1011: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1012: AND aes.entity_id = p_certification_id
1013: AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
1014: AND aes.level_id > 3

Line 1025: FROM AMW_EXECUTION_SCOPE aes

1021:
1022: CURSOR local_proc_not_certified IS
1023: SELECT count(1)
1024: FROM (SELECT distinct aes.organization_id, aes.process_id
1025: FROM AMW_EXECUTION_SCOPE aes
1026: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1027: AND aes.entity_id = p_certification_id
1028: AND aes.level_id > 3
1029: AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)

Line 1041: FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov

1037:
1038: CURSOR local_proc_with_issue IS
1039: SELECT count(1)
1040: FROM (SELECT aes.organization_id, aes.process_id
1041: FROM AMW_EXECUTION_SCOPE aes, AMW_OPINIONS_V aov
1042: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1043: AND aes.entity_id = p_certification_id
1044: AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1045: AND aes.level_id > 3

Line 1055: FROM amw_execution_scope aes,amw_opinions_v aov

1051: AND aov.audit_result_code <> 'EFFECTIVE');
1052:
1053: CURSOR global_proc_with_ineff_ctrl IS
1054: SELECT count(distinct aes.process_id)
1055: FROM amw_execution_scope aes,amw_opinions_v aov
1056: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1057: AND aes.entity_id = p_certification_id
1058: AND aes.level_id > 3
1059: AND aes.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')

Line 1074: FROM amw_execution_scope aes,amw_opinions_v aov

1070:
1071: CURSOR local_proc_with_ineff_ctrl IS
1072: SELECT count(1)
1073: FROM (SELECT distinct aes.organization_id, aes.process_id
1074: FROM amw_execution_scope aes,amw_opinions_v aov
1075: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1076: AND aes.entity_id = p_certification_id
1077: AND aes.level_id > 3
1078: AND aes.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)

Line 1096: FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,

1092: ---AMw.D datamodel
1093: /** CURSOR unmitigated_risks IS
1094: SELECT count(1)
1095: FROM (SELECT distinct aes.organization_id, aes.process_id, ara.risk_id
1096: FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,
1097: AMW_RISK_ASSOCIATIONS ara,
1098: AMW_OPINIONS_V aov
1099: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1100: AND aes.entity_id = p_certification_id

Line 1122: FROM AMW_EXECUTION_SCOPE aes,

1118: AND aov.audit_result_code <> 'EFFECTIVE'); **/
1119: CURSOR unmitigated_risks IS
1120: SELECT count(1)
1121: FROM (SELECT distinct aes.organization_id, aes.process_id, ara.risk_id
1122: FROM AMW_EXECUTION_SCOPE aes,
1123: AMW_RISK_ASSOCIATIONS ara,
1124: AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215 -- only consider those Orgs that are valid*/
1125: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1126: AND aes.entity_id = p_certification_id

Line 1153: FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,

1149: ---AMw.D datamodel
1150: /* CURSOR ineffective_controls IS
1151: SELECT count(1)
1152: FROM (SELECT distinct aes.organization_id, aca.control_id
1153: FROM AMW_EXECUTION_SCOPE aes, AMW_CURR_APPROVED_REV_ORG_V apo,
1154: AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1155: AMW_OPINIONS_V aov
1156: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1157: AND aes.entity_id = p_certification_id

Line 1179: FROM AMW_EXECUTION_SCOPE aes, ---AMW_CURR_APPROVED_REV_ORG_V apo,

1175: AND aov.audit_result_code <> 'EFFECTIVE'); */
1176: CURSOR ineffective_controls IS
1177: SELECT count(1)
1178: FROM (SELECT distinct aes.organization_id, aca.control_id, aes.process_id /** 01/31/2007 npanandi: added processId in distinct **/
1179: FROM AMW_EXECUTION_SCOPE aes, ---AMW_CURR_APPROVED_REV_ORG_V apo,
1180: AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
1181: AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215: consider only those Orgs that are valid*/
1182: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1183: AND aes.entity_id = p_certification_id

Line 1207: FROM AMW_EXECUTION_SCOPE aes

1203: AND aov.audit_result_code <> 'EFFECTIVE');
1204:
1205: CURSOR orgs_pending_in_scope IS
1206: SELECT count(distinct aes.organization_id)
1207: FROM AMW_EXECUTION_SCOPE aes
1208: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1209: AND aes.entity_id = p_certification_id
1210: AND aes.level_id = 4
1211: AND not exists (SELECT 'Y'

Line 1221: FROM AMW_EXECUTION_SCOPE aes

1217: AND aov.pk1_value = aes.process_id);
1218:
1219: CURSOR orgs_in_scope IS
1220: SELECT count(distinct aes.organization_id)
1221: FROM AMW_EXECUTION_SCOPE aes
1222: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1223: AND aes.entity_id = p_certification_id
1224: AND aes.level_id = 3;
1225:

Line 1228: FROM AMW_EXECUTION_SCOPE aes

1224: AND aes.level_id = 3;
1225:
1226: CURSOR orgs_pending_cert IS
1227: SELECT count(distinct aes.organization_id)
1228: FROM AMW_EXECUTION_SCOPE aes
1229: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
1230: AND aes.entity_id = p_certification_id
1231: AND aes.level_id = 3
1232: AND not exists (SELECT 'Y'

Line 1609: FROM amw_execution_scope

1605: -- select all processes in scope for the certification
1606: CURSOR get_all_processes
1607: IS
1608: SELECT DISTINCT process_id, organization_id
1609: FROM amw_execution_scope
1610: WHERE entity_type = 'BUSIPROC_CERTIFICATION'
1611: AND entity_id = p_certification_id
1612: AND process_id IS NOT NULL;
1613:

Line 1617: FROM amw_execution_scope

1613:
1614: CURSOR get_all_orgs
1615: IS
1616: SELECT DISTINCT organization_id
1617: FROM amw_execution_scope
1618: WHERE entity_type = 'BUSIPROC_CERTIFICATION'
1619: AND entity_id = p_certification_id
1620: AND level_id = 3;
1621: