APPS.RCI_ORG_DFCY_ETL_PKG dependencies on AMW_OPINIONS

Line 40: FROM amw_risk_associations ara, amw_opinions_v aov

36: ,c_organization_id in number
37: ,c_process_id in number) is
38: SELECT count(1)
39: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
40: FROM amw_risk_associations ara, amw_opinions_v aov
41: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
42: AND ara.pk1 = c_certification_id
43: AND ara.pk2 = c_organization_id
44: AND ara.pk3 IN (SELECT DISTINCT process_id

Line 60: FROM amw_opinions aov2

56: AND aov.pk3_value = ara.pk2 --org_id
57: AND aov.pk4_value = ara.pk3 --process_id
58: AND aov.pk1_value = ara.risk_id
59: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
60: FROM amw_opinions aov2
61: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
62: AND aov2.pk4_value = aov.pk4_value
63: AND aov2.pk3_value = aov.pk3_value
64: AND aov2.pk1_value = aov.pk1_value)

Line 80: amw_opinions_v aov

76: aca.pk3 process_id,
77: aca.pk4 risk_id,
78: aca.control_id
79: FROM amw_control_associations aca,
80: amw_opinions_v aov
81: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
82: AND aca.pk1 = c_certification_id
83: AND aca.pk2 = c_organization_id
84: AND aca.pk3 IN (SELECT DISTINCT process_id

Line 100: FROM amw_opinions aov2

96: AND aov.pk3_value = c_organization_id
97: AND aov.pk1_value = aca.control_id
98: AND aov.audit_result_code <> 'EFFECTIVE'
99: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
100: FROM amw_opinions aov2
101: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
102: AND aov2.pk3_value = aov.pk3_value
103: AND aov2.pk1_value = aov.pk1_value));
104: