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';
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
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: )
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'
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'
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'
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'
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'
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'
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'
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'
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'
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'
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