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.

[Home] [Help]

APPS.AMW_ORG_CERT_EVAL_SUM_PVT dependencies on AMW_OPINIONS

Line 128: FROM amw_opinions_v opinion

124:
125: CURSOR get_certification_opinion
126: IS
127: SELECT opinion.opinion_id
128: FROM amw_opinions_v opinion
129: WHERE opinion.pk1_value = p_org_id
130: AND opinion.pk2_value = p_certification_id
131: AND opinion.opinion_type_code = 'CERTIFICATION'
132: AND opinion.object_name = 'AMW_ORGANIZATION';

Line 137: FROM amw_opinions_v opinion

133:
134: CURSOR get_evaluation_opinion
135: IS
136: SELECT opinion.opinion_id
137: FROM amw_opinions_v opinion
138: WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
139: FROM amw_opinions_v opinion2
140: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
141: AND opinion2.pk1_value = opinion.pk1_value

Line 139: FROM amw_opinions_v opinion2

135: IS
136: SELECT opinion.opinion_id
137: FROM amw_opinions_v opinion
138: WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
139: FROM amw_opinions_v opinion2
140: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
141: AND opinion2.pk1_value = opinion.pk1_value
142: )
143: )

Line 151: FROM amw_opinions_log_v opinion

147:
148: CURSOR get_evaluation_opinion_log
149: IS
150: SELECT opinion.opinion_log_id
151: FROM amw_opinions_log_v opinion
152: WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
153: FROM amw_opinions opinion2
154: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
155: AND opinion2.pk1_value = opinion.pk1_value)

Line 153: FROM amw_opinions opinion2

149: IS
150: SELECT opinion.opinion_log_id
151: FROM amw_opinions_log_v opinion
152: WHERE (opinion.authored_date IN (SELECT MAX(opinion2.authored_date)
153: FROM amw_opinions opinion2
154: WHERE opinion2.object_opinion_type_id = opinion.object_opinion_type_id
155: AND opinion2.pk1_value = opinion.pk1_value)
156: )
157: AND opinion.pk1_value = p_org_id

Line 165: FROM amw_risk_associations ara,amw_opinions_v aov

161: CURSOR get_unmitigated_risks
162: IS
163: SELECT count(1)
164: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
165: FROM amw_risk_associations ara,amw_opinions_v aov
166: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
167: AND ara.pk1 = p_certification_id
168: AND ara.pk2 = p_org_id
169: AND aov.object_name = 'AMW_ORG_PROCESS_RISK'

Line 177: FROM amw_opinions aov2

173: AND NVL(aov.pk4_value,-1)
174: = NVL(ara.pk3, -1) --process_id
175: AND aov.audit_result_code <> 'EFFECTIVE'
176: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
177: FROM amw_opinions aov2
178: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
179: AND NVL(aov2.pk4_value, -1)
180: = NVL(aov.pk4_value, -1)
181: AND aov2.pk3_value = aov.pk3_value

Line 190: FROM amw_risk_associations ara,amw_opinions_v aov

186: CURSOR get_evaluated_risks
187: IS
188: SELECT count(1)
189: FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
190: FROM amw_risk_associations ara,amw_opinions_v aov
191: WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
192: AND ara.pk1 = p_certification_id
193: AND ara.pk2 = p_org_id
194: AND aov.object_name = 'AMW_ORG_PROCESS_RISK'

Line 223: FROM amw_control_associations aca,amw_opinions_v aov

219: ---instead of having a distinct on
220: ---certificationId, organizationId, processId, riskId, controlId
221: ---FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
222: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
223: FROM amw_control_associations aca,amw_opinions_v aov
224: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
225: AND aca.pk1 = p_certification_id
226: AND aca.pk2 = p_org_id
227: AND aov.object_name = 'AMW_ORG_CONTROL'

Line 233: FROM amw_opinions aov2

229: AND aov.pk1_value = aca.control_id
230: AND aov.pk3_value = aca.pk2
231: AND aov.audit_result_code <> 'EFFECTIVE'
232: AND aov.authored_date = (SELECT MAX(aov2.authored_date)
233: FROM amw_opinions aov2
234: WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
235: AND aov2.pk3_value = aov.pk3_value
236: AND aov2.pk1_value = aov.pk1_value)
237: );

Line 249: FROM amw_control_associations aca,amw_opinions_v aov

245: ---instead of having a distinct on
246: ---certificationId, organizationId, processId, riskId, controlId
247: ---FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id, aca.control_id
248: FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
249: FROM amw_control_associations aca,amw_opinions_v aov
250: WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
251: AND aca.pk1 = p_certification_id
252: AND aca.pk2 = p_org_id
253: AND aov.object_name = 'AMW_ORG_CONTROL'

Line 284: FROM amw_opinions_v opinion

280: IS
281: SELECT count(DISTINCT process_id)
282: FROM amw_execution_scope amw_exec
283: WHERE EXISTS (SELECT opinion.opinion_id
284: FROM amw_opinions_v opinion
285: WHERE opinion.pk1_value = amw_exec.process_id
286: AND opinion.pk3_value = p_org_id
287: AND opinion.pk2_value = p_certification_id
288: AND opinion.opinion_type_code = 'CERTIFICATION'

Line 299: FROM amw_opinions_v opinion

295: IS
296: SELECT count(DISTINCT process_id)
297: FROM amw_execution_scope amw_exec
298: WHERE EXISTS (SELECT opinion.opinion_id
299: FROM amw_opinions_v opinion
300: WHERE opinion.pk1_value = amw_exec.process_id
301: AND opinion.pk3_value = p_org_id
302: AND opinion.pk2_value = p_certification_id
303: AND opinion.opinion_type_code = 'CERTIFICATION'

Line 319: FROM amw_opinions_v opinion

315: FROM amw_execution_scope amw_exec
316: WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
317: AND amw_exec.entity_id = p_certification_id
318: AND EXISTS (SELECT opinion.opinion_id
319: FROM amw_opinions_v opinion
320: WHERE opinion.pk1_value = amw_exec.process_id
321: --AND opinion.pk2_value = p_certification_id
322: AND opinion.pk3_value = p_org_id
323: AND opinion.opinion_type_code = 'EVALUATION'

Line 327: FROM amw_opinions aov2

323: AND opinion.opinion_type_code = 'EVALUATION'
324: AND opinion.object_name = 'AMW_ORG_PROCESS'
325: AND opinion.audit_result_code IS NOT NULL
326: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
327: FROM amw_opinions aov2
328: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
329: AND aov2.pk3_value = opinion.pk3_value
330: AND aov2.pk1_value = opinion.pk1_value)
331: );

Line 344: FROM amw_opinions_v opinion

340: FROM amw_execution_scope amw_exec
341: WHERE amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
342: AND amw_exec.entity_id = p_certification_id
343: AND EXISTS (SELECT opinion.opinion_id
344: FROM amw_opinions_v opinion
345: WHERE opinion.pk1_value = amw_exec.process_id
346: --AND opinion.pk2_value = p_certification_id
347: AND opinion.pk3_value = p_org_id
348: AND opinion.opinion_type_code = 'EVALUATION'

Line 352: FROM amw_opinions aov2

348: AND opinion.opinion_type_code = 'EVALUATION'
349: AND opinion.object_name = 'AMW_ORG_PROCESS'
350: AND opinion.audit_result_code <> 'EFFECTIVE'
351: AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
352: FROM amw_opinions aov2
353: WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
354: AND aov2.pk3_value = opinion.pk3_value
355: AND aov2.pk1_value = opinion.pk1_value)
356: );

Line 363: FROM amw_opinions_v opinion

359: IS
360: SELECT count(distinct object_id)
361: FROM amw_entity_hierarchies ent
362: WHERE EXISTS (SELECT opinion.opinion_id
363: FROM amw_opinions_v opinion
364: WHERE opinion.pk1_value = p_org_id
365: AND opinion.pk2_value = p_certification_id
366: AND opinion.opinion_type_code = 'CERTIFICATION'
367: AND opinion.object_name = 'AMW_ORGANIZATION'

Line 383: FROM amw_opinions_v opinion

379: IS
380: SELECT count(distinct object_id)
381: FROM amw_entity_hierarchies ent
382: WHERE EXISTS (SELECT opinion.opinion_id
383: FROM amw_opinions_v opinion
384: WHERE opinion.pk1_value = p_org_id
385: AND opinion.pk2_value = p_certification_id
386: AND opinion.opinion_type_code = 'CERTIFICATION'
387: AND opinion.object_name = 'AMW_ORGANIZATION'

Line 434: FROM AMW_OPINIONS_V aov

430: AND aes.parent_process_id = -1 --need to verify if this is -2
431: AND aes.entity_id = p_certification_id
432: AND aes.organization_id = p_org_id
433: AND NOT EXISTS (SELECT 'Y'
434: FROM AMW_OPINIONS_V aov
435: WHERE aov.object_name = 'AMW_ORG_PROCESS'
436: AND aov.opinion_type_code = 'CERTIFICATION'
437: AND aov.pk3_value = aes.organization_id
438: AND aov.pk2_value = p_certification_id