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_OPINIONS_V

Line 92: FROM amw_opinions_v opinion

88: IS
89: CURSOR get_certification_opinion
90: IS
91: SELECT opinion.opinion_id
92: FROM amw_opinions_v opinion
93: WHERE opinion.pk3_value = p_org_id
94: AND opinion.pk2_value = p_certification_id
95: AND opinion.pk1_value = p_process_id
96: AND opinion.opinion_type_code = 'CERTIFICATION'

Line 102: FROM amw_opinions_v opinion

98:
99: CURSOR get_evaluation_opinion
100: IS
101: SELECT opinion.opinion_id
102: FROM amw_opinions_v opinion
103: WHERE (opinion.authored_date in (SELECT MAX(opinion2.authored_date)
104: FROM amw_opinions_v opinion2
105: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
106: AND opinion2.pk1_value = opinion.pk1_value

Line 104: FROM amw_opinions_v opinion2

100: IS
101: SELECT opinion.opinion_id
102: FROM amw_opinions_v opinion
103: WHERE (opinion.authored_date in (SELECT MAX(opinion2.authored_date)
104: FROM amw_opinions_v opinion2
105: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
106: AND opinion2.pk1_value = opinion.pk1_value
107: AND opinion2.pk3_value = opinion.pk3_value)
108: )

Line 133: FROM amw_risk_associations ara, amw_opinions_v aov

129: CURSOR get_unmitigated_risks
130: IS
131: SELECT count(1)
132: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
133: FROM amw_risk_associations ara, amw_opinions_v aov
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

Line 166: FROM amw_risk_associations ara, amw_opinions_v aov

162: CURSOR get_evaluated_risks
163: IS
164: SELECT count(1)
165: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
166: FROM amw_risk_associations ara, amw_opinions_v aov
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

Line 219: FROM amw_control_associations aca,amw_opinions_v aov

215: IS
216: SELECT count(1)
217: --FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
218: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
219: FROM amw_control_associations aca,amw_opinions_v aov
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

Line 253: FROM amw_control_associations aca,amw_opinions_v aov

249: CURSOR get_evaluated_controls
250: IS
251: SELECT count(1)
252: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
253: FROM amw_control_associations aca,amw_opinions_v aov
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

Line 346: FROM amw_opinions_v opinion

342:
343: CURSOR get_org_processes_certified
344: IS
345: SELECT count(distinct pk3_value)
346: FROM amw_opinions_v opinion
347: WHERE opinion.pk2_value = p_certification_id
348: AND opinion.pk1_value = p_process_id
349: AND opinion.opinion_type_code = 'CERTIFICATION'
350: AND opinion.object_name = 'AMW_ORG_PROCESS'

Line 372: FROM amw_opinions_v opinion,

368: CURSOR get_var_org_proc_certified
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

Line 420: FROM amw_opinions_v opinion

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
423: AND opinion.pk2_value = p_certification_id
424: AND opinion.opinion_type_code = 'CERTIFICATION'

Line 443: FROM amw_opinions_v opinion

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
446: AND opinion.pk2_value = p_certification_id
447: AND opinion.opinion_type_code = 'CERTIFICATION'

Line 466: FROM amw_opinions_v opinion

462:
463: CURSOR get_org_process_cert_issues
464: IS
465: SELECT count(distinct pk3_value)
466: FROM amw_opinions_v opinion
467: WHERE opinion.pk2_value = p_certification_id
468: AND opinion.pk1_value = p_process_id
469: AND opinion.opinion_type_code = 'CERTIFICATION'
470: AND opinion.object_name = 'AMW_ORG_PROCESS'

Line 493: FROM amw_opinions_v opinion,

489: CURSOR get_var_org_proc_cert_issues
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

Line 998: FROM AMW_OPINIONS_V aov

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')
997: AND not exists (SELECT 'Y'
998: FROM AMW_OPINIONS_V aov
999: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1000: AND aov.opinion_type_code = 'CERTIFICATION'
1001: AND aov.pk3_value = aes.organization_id
1002: AND aov.pk2_value = p_certification_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 1031: FROM AMW_OPINIONS_V aov

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)
1030: AND not exists (SELECT 'Y'
1031: FROM AMW_OPINIONS_V aov
1032: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1033: AND aov.opinion_type_code = 'CERTIFICATION'
1034: AND aov.pk3_value = aes.organization_id
1035: AND aov.pk2_value = p_certification_id

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 1065: FROM amw_opinions_v aov2

1061: AND aov.opinion_type_code = 'EVALUATION'
1062: AND aov.pk3_value = aes.organization_id
1063: AND aov.pk1_value = aes.process_id
1064: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
1065: FROM amw_opinions_v aov2
1066: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
1067: AND aov2.pk3_value = aov.pk3_value
1068: AND aov2.pk1_value = aov.pk1_value)
1069: AND aov.audit_result_code <> 'EFFECTIVE';

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 1084: FROM amw_opinions_v aov2

1080: AND aov.opinion_type_code = 'EVALUATION'
1081: AND aov.pk3_value = aes.organization_id
1082: AND aov.pk1_value = aes.process_id
1083: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
1084: FROM amw_opinions_v aov2
1085: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
1086: AND aov2.pk3_value = aov.pk3_value
1087: AND aov2.pk1_value = aov.pk1_value)
1088: AND aov.audit_result_code <> 'EFFECTIVE');

Line 1098: AMW_OPINIONS_V aov

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
1101: AND aes.level_id > 3
1102: AND apo.organization_id = aes.organization_id

Line 1113: from AMW_OPINIONS_V aov2

1109: AND aov.pk4_value = aes.process_id
1110: AND aov.pk1_value = ara.risk_id
1111: AND aov.authored_date =
1112: (select max(aov2.authored_date)
1113: from AMW_OPINIONS_V aov2
1114: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1115: and aov2.pk4_value = aov.pk4_value
1116: and aov2.pk3_value = aov.pk3_value
1117: and aov2.pk1_value = aov.pk1_value)

Line 1124: AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215 -- only consider those Orgs that are valid*/

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
1127: AND aes.level_id > 3
1128: ---AND apo.organization_id = aes.organization_id

Line 1141: from AMW_OPINIONS_V aov2

1137: AND aov.pk3_value = aes.organization_id
1138: AND aov.pk4_value = aes.process_id
1139: AND aov.pk1_value = ara.risk_id
1140: AND aov.authored_date = (select max(aov2.authored_date)
1141: from AMW_OPINIONS_V aov2
1142: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1143: and aov2.pk4_value = aov.pk4_value
1144: and aov2.pk3_value = aov.pk3_value
1145: and aov2.pk1_value = aov.pk1_value)

Line 1155: AMW_OPINIONS_V aov

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
1158: AND aes.level_id > 3
1159: AND apo.organization_id = aes.organization_id

Line 1171: from AMW_OPINIONS_V aov2

1167: AND aov.pk3_value = aes.organization_id
1168: AND aov.pk1_value = aca.control_id
1169: AND aov.authored_date =
1170: (select max(aov2.authored_date)
1171: from AMW_OPINIONS_V aov2
1172: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1173: and aov2.pk3_value = aov.pk3_value
1174: and aov2.pk1_value = aov.pk1_value)
1175: AND aov.audit_result_code <> 'EFFECTIVE'); */

Line 1181: AMW_OPINIONS_V aov, amw_audit_units_v aauv /* 03.19.2007 npanandi: bug 5862215: consider only those Orgs that are valid*/

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
1184: AND aes.level_id > 3
1185: AND ara.object_type = 'PROCESS_ORG'

Line 1199: from AMW_OPINIONS_V aov2

1195: AND aov.opinion_type_code = 'EVALUATION'
1196: AND aov.pk3_value = aes.organization_id
1197: AND aov.pk1_value = aca.control_id
1198: AND aov.authored_date = (select max(aov2.authored_date)
1199: from AMW_OPINIONS_V aov2
1200: where aov2.object_opinion_type_id = aov.object_opinion_type_id
1201: and aov2.pk3_value = aov.pk3_value
1202: and aov2.pk1_value = aov.pk1_value)
1203: AND aov.audit_result_code <> 'EFFECTIVE');

Line 1212: FROM AMW_OPINIONS_V aov

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'
1212: FROM AMW_OPINIONS_V aov
1213: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1214: AND aov.opinion_type_code = 'CERTIFICATION'
1215: AND aov.pk3_value = aes.organization_id
1216: AND aov.pk2_value = p_certification_id

Line 1233: FROM AMW_OPINIONS_V aov

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'
1233: FROM AMW_OPINIONS_V aov
1234: WHERE aov.object_name = 'AMW_ORGANIZATION'
1235: AND aov.opinion_type_code = 'CERTIFICATION'
1236: AND aov.pk1_value = aes.organization_id
1237: AND aov.pk2_value = p_certification_id);