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_ORG_PROC_CERT_DATED_SUMM dependencies on AMW_OPINIONS_LOG

Line 23: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

19: CURSOR get_unmitigated_risks
20: IS
21: SELECT count(1)
22: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
23: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
24: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
25: AND ara.pk1 = p_certification_id
26: AND ara.pk2 = p_org_id
27: AND ara.risk_rev_id = arb.risk_rev_id

Line 35: FROM amw_opinions_log aov2

31: AND aov.pk3_value = ara.pk2 --org_id
32: AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
33: AND aov.pk1_value = ara.risk_id
34: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
35: FROM amw_opinions_log aov2
36: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
37: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
38: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
39: AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1)

Line 72: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

68: CURSOR get_eval_risks
69: IS
70: SELECT count(1)
71: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
72: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
73: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
74: AND ara.pk1 = p_certification_id
75: AND ara.pk2 = p_org_id
76: AND ara.risk_rev_id = arb.risk_rev_id

Line 84: FROM amw_opinions_log aov2

80: AND aov.pk3_value = ara.pk2 --org_id
81: AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
82: AND aov.pk1_value = ara.risk_id
83: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
84: FROM amw_opinions_log aov2
85: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
86: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
87: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
88: AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1) --process_id

Line 155: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

151: CURSOR get_unmitigated_risks
152: IS
153: SELECT count(1)
154: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
155: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
156: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
157: AND ara.pk1 = p_certification_id
158: AND ara.pk2 = p_org_id
159: AND ara.risk_rev_id = arb.risk_rev_id

Line 167: FROM amw_opinions_log aov2

163: AND aov.pk3_value = ara.pk2 --org_id
164: AND nvl(aov.pk4_value, -1) = nvl(ara.pk3, -1) --process_id
165: AND aov.pk1_value = ara.risk_id
166: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
167: FROM amw_opinions_log aov2
168: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
169: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
170: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
171: AND nvl(aov2.pk4_value, -1) = nvl(aov.pk4_value, -1)

Line 214: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

210: CURSOR get_eval_controls
211: IS
212: SELECT count(1)
213: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
214: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
215: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
216: AND acb.control_rev_id = aca.control_rev_id
217: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
218: AND aca.pk1 = p_certification_id

Line 225: FROM amw_opinions_log aov2

221: AND aov.opinion_type_code = 'EVALUATION'
222: AND aov.pk3_value = p_org_id
223: AND aov.pk1_value = aca.control_id
224: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
225: FROM amw_opinions_log aov2
226: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
227: AND aov2.pk3_value = aov.pk3_value
228: AND aov2.pk1_value = aov.pk1_value
229: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 283: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

279: CURSOR get_ineff_controls
280: IS
281: SELECT count(1)
282: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
283: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
284: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
285: AND acb.control_rev_id = aca.control_rev_id
286: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
287: AND aca.pk1 = p_certification_id

Line 295: FROM amw_opinions_log aov2

291: AND aov.pk3_value = p_org_id
292: AND aov.pk1_value = aca.control_id
293: AND aov.audit_result_code <> 'EFFECTIVE'
294: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
295: FROM amw_opinions_log aov2
296: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
297: AND aov2.pk3_value = aov.pk3_value
298: AND aov2.pk1_value = aov.pk1_value
299: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 324: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

320: CURSOR get_ineff_controls
321: IS
322: SELECT count(1)
323: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
324: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
325: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
326: AND acb.control_rev_id = aca.control_rev_id
327: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
328: AND aca.pk1 = p_certification_id

Line 336: FROM amw_opinions_log aov2

332: AND aov.pk3_value = p_org_id
333: AND aov.pk1_value = aca.control_id
334: AND aov.audit_result_code <> 'EFFECTIVE'
335: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
336: FROM amw_opinions_log aov2
337: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
338: AND aov2.pk3_value = aov.pk3_value
339: AND aov2.pk1_value = aov.pk1_value
340: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 417: FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo

413: IS
414: CURSOR get_certified_processes
415: IS
416: SELECT count(DISTINCT aes.process_id)
417: FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo
418: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
419: AND aes.organization_id = p_org_id
420: AND aes.entity_id = p_certification_id
421: AND aes.level_id > 4

Line 430: FROM amw_opinions_log aov2

426: AND opinion.pk2_value = aes.entity_id
427: AND opinion.opinion_type_code = 'CERTIFICATION'
428: AND opinion.object_name = 'AMW_PROCESS_ORG'
429: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
430: FROM amw_opinions_log aov2
431: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
432: AND aov2.pk3_value = opinion.pk3_value
433: AND aov2.pk1_value = opinion.pk1_value
434: AND aov2.pk2_value = opinion.pk2_value

Line 460: FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo

456: IS
457: CURSOR get_proc_cert_issues
458: IS
459: SELECT count(DISTINCT aes.process_id)
460: FROM amw_execution_scope aes, amw_opinions_log_v opinion, amw_process_organization apo
461: WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
462: AND aes.organization_id = p_org_id
463: AND aes.entity_id = p_certification_id
464: AND aes.level_id > 4

Line 473: FROM amw_opinions_log aov2

469: AND opinion.pk2_value = aes.entity_id
470: AND opinion.opinion_type_code = 'CERTIFICATION'
471: AND opinion.object_name = 'AMW_PROCESS_ORG'
472: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
473: FROM amw_opinions_log aov2
474: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
475: AND aov2.pk3_value = opinion.pk3_value
476: AND aov2.pk1_value = opinion.pk1_value
477: AND aov2.pk2_value = opinion.pk2_value

Line 510: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

506: CURSOR get_ineff_controls
507: IS
508: SELECT count(1)
509: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
510: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
511: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
512: AND acb.control_rev_id = aca.control_rev_id
513: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
514: AND aca.pk1 = p_certification_id

Line 533: FROM amw_opinions_log aov2

529: AND aov.pk3_value = p_org_id
530: AND aov.pk1_value = aca.control_id
531: AND aov.audit_result_code <> 'EFFECTIVE'
532: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
533: FROM amw_opinions_log aov2
534: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
535: AND aov2.pk3_value = aov.pk3_value
536: AND aov2.pk1_value = aov.pk1_value
537: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 565: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

561: CURSOR get_eval_controls
562: IS
563: SELECT count(1)
564: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
565: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
566: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
567: AND acb.control_rev_id = aca.control_rev_id
568: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
569: AND aca.pk1 = p_certification_id

Line 587: FROM amw_opinions_log aov2

583: AND aov.opinion_type_code = 'EVALUATION'
584: AND aov.pk3_value = p_org_id
585: AND aov.pk1_value = aca.control_id
586: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
587: FROM amw_opinions_log aov2
588: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
589: AND aov2.pk3_value = aov.pk3_value
590: AND aov2.pk1_value = aov.pk1_value
591: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 656: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb

652: CURSOR get_ineff_controls
653: IS
654: SELECT count(1)
655: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
656: FROM amw_control_associations aca,amw_opinions_log_v aov, amw_controls_b acb
657: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
658: AND acb.control_rev_id = aca.control_rev_id
659: AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
660: AND aca.pk1 = p_certification_id

Line 679: FROM amw_opinions_log aov2

675: AND aov.pk3_value = p_org_id
676: AND aov.pk1_value = aca.control_id
677: AND aov.audit_result_code <> 'EFFECTIVE'
678: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
679: FROM amw_opinions_log aov2
680: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
681: AND aov2.pk3_value = aov.pk3_value
682: AND aov2.pk1_value = aov.pk1_value
683: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)

Line 766: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

762: CURSOR get_unmitigated_risks
763: IS
764: SELECT count(1)
765: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
766: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
767: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
768: AND ara.pk1 = p_certification_id
769: AND ara.pk2 = p_org_id
770: AND ara.risk_rev_id = arb.risk_rev_id

Line 789: FROM amw_opinions_log aov2

785: AND aov.pk3_value = ara.pk2 --org_id
786: AND aov.pk4_value = ara.pk3 --process_id
787: AND aov.pk1_value = ara.risk_id
788: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
789: FROM amw_opinions_log aov2
790: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
791: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
792: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
793: AND aov2.pk4_value = aov.pk4_value

Line 878: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

874: CURSOR get_eval_risks
875: IS
876: SELECT count(1)
877: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
878: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
879: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
880: AND ara.pk1 = p_certification_id
881: AND ara.pk2 = p_org_id
882: AND ara.risk_rev_id = arb.risk_rev_id

Line 901: FROM amw_opinions_log aov2

897: AND aov.pk3_value = ara.pk2 --org_id
898: AND aov.pk4_value = ara.pk3 --process_id
899: AND aov.pk1_value = ara.risk_id
900: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
901: FROM amw_opinions_log aov2
902: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
903: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
904: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
905: AND aov2.pk4_value = aov.pk4_value

Line 935: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb

931: CURSOR get_unmitigated_risks
932: IS
933: SELECT count(1)
934: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
935: FROM amw_risk_associations ara, amw_opinions_log_v aov, amw_risks_b arb
936: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
937: AND ara.pk1 = p_certification_id
938: AND ara.pk2 = p_org_id
939: AND ara.risk_rev_id = arb.risk_rev_id

Line 958: FROM amw_opinions_log aov2

954: AND aov.pk3_value = ara.pk2 --org_id
955: AND aov.pk4_value = ara.pk3 --process_id
956: AND aov.pk1_value = ara.risk_id
957: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
958: FROM amw_opinions_log aov2
959: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
960: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
961: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
962: AND aov2.pk4_value = aov.pk4_value

Line 1062: FROM amw_opinions_log_v opinion

1058: IS
1059: CURSOR get_org_proc_cert_issues
1060: IS
1061: SELECT count(distinct pk3_value)
1062: FROM amw_opinions_log_v opinion
1063: WHERE opinion.pk2_value = p_certification_id
1064: AND opinion.pk1_value = p_process_id
1065: AND opinion.opinion_type_code = 'CERTIFICATION'
1066: AND opinion.object_name = 'AMW_ORG_PROCESS'

Line 1069: FROM amw_opinions_log aov2

1065: AND opinion.opinion_type_code = 'CERTIFICATION'
1066: AND opinion.object_name = 'AMW_ORG_PROCESS'
1067: AND opinion.audit_result_code <> 'EFFECTIVE'
1068: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
1069: FROM amw_opinions_log aov2
1070: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
1071: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
1072: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
1073: AND aov2.pk2_value = opinion.pk2_value

Line 1097: FROM amw_opinions_log_v opinion,

1093: CURSOR get_var_org_proc_cert_issues
1094: IS
1095: SELECT count(1)
1096: FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
1097: FROM amw_opinions_log_v opinion,
1098: amw_execution_scope scp,
1099: amw_process_organization procorg
1100: WHERE opinion.pk2_value = p_certification_id
1101: AND opinion.pk1_value = scp.process_id

Line 1124: FROM amw_opinions_log aov2

1120: AND entity_type = PRIOR entity_type)
1121: AND opinion.opinion_type_code = 'CERTIFICATION'
1122: AND opinion.object_name = 'AMW_ORG_PROCESS'
1123: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
1124: FROM amw_opinions_log aov2
1125: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
1126: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
1127: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
1128: AND aov2.pk2_value = opinion.pk2_value

Line 1163: FROM amw_opinions_log_v opinion

1159: IS
1160: CURSOR get_org_proc_certified
1161: IS
1162: SELECT count(distinct pk3_value)
1163: FROM amw_opinions_log_v opinion
1164: WHERE opinion.pk2_value = p_certification_id
1165: AND opinion.pk1_value = p_process_id
1166: AND opinion.opinion_type_code = 'CERTIFICATION'
1167: AND opinion.object_name = 'AMW_ORG_PROCESS'

Line 1169: FROM amw_opinions_log aov2

1165: AND opinion.pk1_value = p_process_id
1166: AND opinion.opinion_type_code = 'CERTIFICATION'
1167: AND opinion.object_name = 'AMW_ORG_PROCESS'
1168: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
1169: FROM amw_opinions_log aov2
1170: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
1171: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
1172: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
1173: AND aov2.pk2_value = opinion.pk2_value

Line 1197: FROM amw_opinions_log_v opinion,

1193: CURSOR get_var_org_proc_certified
1194: IS
1195: SELECT count(1)
1196: FROM (SELECT distinct opinion.pk1_value, opinion.pk3_value
1197: FROM amw_opinions_log_v opinion,
1198: amw_execution_scope scp,
1199: amw_process_organization procorg
1200: WHERE opinion.pk2_value = p_certification_id
1201: AND opinion.pk1_value = scp.process_id

Line 1204: FROM amw_opinions_log aov2

1200: WHERE opinion.pk2_value = p_certification_id
1201: AND opinion.pk1_value = scp.process_id
1202: AND opinion.pk3_value = scp.organization_id
1203: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
1204: FROM amw_opinions_log aov2
1205: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
1206: AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
1207: AND aov2.authored_date < nvl(p_to_date, sysdate+1)
1208: AND aov2.pk2_value = opinion.pk2_value