1 PACKAGE BODY AMW_ORG_PROC_CERT_DATED_SUMM as
2 /* $Header: amwpcerb.pls 120.0 2005/07/29 00:36:25 appldev noship $ */
3
4
5 -- ORGANIZATION FUNCTIONs
6
7 -- Get number of unmitigated risks given a certification and org within the certification
8 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
9 -- If material risks flag is passed, then only material risks are considered.
10 -- Considers entity risks also.
11 FUNCTION get_unmit_risk_for_org
12 (p_certification_id in NUMBER,
13 p_org_id in NUMBER,
14 p_from_date in DATE,
15 p_to_date in DATE,
16 p_material_risks_flag in VARCHAR2)
17 RETURN NUMBER
18 IS
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
28 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
29 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
30 AND aov.opinion_type_code = 'EVALUATION'
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)
40 AND aov2.pk3_value = aov.pk3_value
41 AND aov2.pk1_value = aov.pk1_value)
42 AND aov.audit_result_code <> 'EFFECTIVE'
43 );
44
45 l_unmit_risks NUMBER;
46 BEGIN
47 l_unmit_risks := 0;
48 OPEN get_unmitigated_risks;
49 FETCH get_unmitigated_risks into l_unmit_risks;
50 CLOSE get_unmitigated_risks;
51
52 RETURN l_unmit_risks;
53 END get_unmit_risk_for_org;
54
55
56 -- Get number of evaluated risks given a certification and org within the certification
57 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
58 -- If material risks flag is passed, then only material risks are considered.
59 -- Considers entity risks also.
60 FUNCTION get_eval_risk_for_org
61 (p_certification_id IN NUMBER,
62 p_org_id in NUMBER,
63 p_from_date in DATE,
64 p_to_date in DATE,
65 p_material_risks_flag in VARCHAR2)
66 RETURN NUMBER
67 IS
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
77 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
78 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
79 AND aov.opinion_type_code = 'EVALUATION'
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
89 AND aov2.pk3_value = aov.pk3_value
90 AND aov2.pk1_value = aov.pk1_value)
91 AND aov.audit_result_code IS NOT NULL
92 );
93 l_eval_risks NUMBER;
94 BEGIN
95 l_eval_risks := 0;
96 OPEN get_eval_risks;
97 FETCH get_eval_risks into l_eval_risks;
98 CLOSE get_eval_risks;
99 RETURN l_eval_risks;
100 END get_eval_risk_for_org;
101
102 -- Get number of risks given a certification and org within the certification
103 -- If material risks flag is passed, then only material risks are considered.
104 -- Considers entity risks also.
105 FUNCTION get_total_risks_for_org
106 (p_certification_id IN NUMBER,
107 p_org_id in NUMBER,
108 p_material_risks_flag in VARCHAR2)
109 RETURN NUMBER
110 IS
111 CURSOR get_total_risks
112 IS
113 SELECT count(1)
114 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
115 FROM amw_risk_associations ara, amw_risks_b arb
116 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
117 AND ara.risk_rev_id = arb.risk_rev_id
118 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
119 AND ara.pk1 = p_certification_id
120 AND ara.pk2 = p_org_id);
121
122 l_total_risks NUMBER;
123 BEGIN
124 l_total_risks := 0;
125 OPEN get_total_risks;
126 FETCH get_total_risks into l_total_risks;
127 CLOSE get_total_risks;
128 RETURN l_total_risks;
129 END get_total_risks_for_org;
130
131
132 FUNCTION get_unmit_risk_prcnt_for_org
133 (p_certification_id IN NUMBER,
134 p_org_id in NUMBER,
135 p_from_date in DATE,
136 p_to_date in DATE,
137 p_material_risks_flag in VARCHAR2)
138 RETURN NUMBER
139 IS
140 CURSOR get_total_risks
141 IS
142 SELECT count(1)
143 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
144 FROM amw_risk_associations ara, amw_risks_b arb
145 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
146 AND ara.risk_rev_id = arb.risk_rev_id
147 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
148 AND ara.pk1 = p_certification_id
149 AND ara.pk2 = p_org_id);
150
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
160 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
161 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
162 AND aov.opinion_type_code = 'EVALUATION'
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)
172 AND aov2.pk3_value = aov.pk3_value
173 AND aov2.pk1_value = aov.pk1_value)
174 AND aov.audit_result_code <> 'EFFECTIVE'
175 );
176
177 l_total_risks NUMBER;
178 l_unmitigated_risks NUMBER;
179 l_risk_prcnt NUMBER;
180 BEGIN
181 l_total_risks := 0;
182 OPEN get_total_risks;
183 FETCH get_total_risks into l_total_risks;
184 CLOSE get_total_risks;
185
186 l_unmitigated_risks := 0;
187 OPEN get_unmitigated_risks;
188 FETCH get_unmitigated_risks into l_unmitigated_risks;
189 CLOSE get_unmitigated_risks;
190
191 if l_total_risks = 0
192 then
193 RETURN l_total_risks;
194 else
195 l_risk_prcnt := round(l_unmitigated_risks/l_total_risks*100);
196 RETURN l_risk_prcnt;
197 END IF;
198 END get_unmit_risk_prcnt_for_org;
199
200 -- Get number of evaluated controls given a certification and org within the certification
201 -- If key controls flag is passed, then only key controls are considered.
202 FUNCTION get_eval_ctrls_for_org
203 (p_certification_id IN NUMBER,
204 p_org_id in NUMBER,
205 p_from_date in DATE,
206 p_to_date in DATE,
207 p_key_ctrls_flag in VARCHAR2)
208 RETURN NUMBER
209 IS
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
219 AND aca.pk2 = p_org_id
220 AND aov.object_name = 'AMW_ORG_CONTROL'
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)
230 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
231 AND aov.audit_result_code IS NOT NULL);
232 l_eval_ctrls NUMBER;
233 BEGIN
234 l_eval_ctrls := 0;
235 OPEN get_eval_controls;
236 FETCH get_eval_controls into l_eval_ctrls;
237 CLOSE get_eval_controls;
238 RETURN l_eval_ctrls;
239 END get_eval_ctrls_for_org;
240
241
242 -- Get number of controls given a certification and org within the certification
243 -- If key controls flag is passed, then only key controls are considered.
244 FUNCTION get_total_ctrls_for_org
245 (p_certification_id IN NUMBER,
246 p_org_id in NUMBER,
247 p_key_ctrls_flag in VARCHAR2)
248 RETURN NUMBER
249 IS
250 CURSOR get_total_controls
251 IS
252 SELECT count(1)
253 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
254 FROM amw_control_associations aca, amw_controls_b acb
255 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
256 AND acb.control_rev_id = aca.control_rev_id
257 AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
258 AND aca.pk1 = p_certification_id
259 AND aca.pk2 = p_org_id);
260 l_total_ctrls NUMBER;
261 BEGIN
262 l_total_ctrls := 0;
263 OPEN get_total_controls;
264 FETCH get_total_controls into l_total_ctrls;
265 CLOSE get_total_controls;
266 RETURN l_total_ctrls;
267 END get_total_ctrls_for_org;
268
269 -- Get number of ineffective controls given a certification and org within the certification
270 -- If key controls flag is passed, then only key controls are considered.
271 FUNCTION get_ineff_ctrls_for_org
272 (p_certification_id IN NUMBER,
273 p_org_id in NUMBER,
274 p_from_date in DATE,
275 p_to_date in DATE,
276 p_key_ctrls_flag in VARCHAR2)
277 RETURN NUMBER
278 IS
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
288 AND aca.pk2 = p_org_id
289 AND aov.object_name = 'AMW_ORG_CONTROL'
290 AND aov.opinion_type_code = 'EVALUATION'
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)
300 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
301 );
302
303 l_ineff_ctrls NUMBER;
304 BEGIN
305 l_ineff_ctrls := 0;
306 OPEN get_ineff_controls;
307 FETCH get_ineff_controls into l_ineff_ctrls;
308 CLOSE get_ineff_controls;
309 RETURN l_ineff_ctrls;
310 END get_ineff_ctrls_for_org;
311
312 FUNCTION get_ineff_ctrl_prcnt_for_org
313 (p_certification_id IN NUMBER,
314 p_org_id in NUMBER,
315 p_from_date in DATE,
316 p_to_date in DATE,
317 p_key_ctrls_flag in VARCHAR2)
318 RETURN NUMBER
319 IS
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
329 AND aca.pk2 = p_org_id
330 AND aov.object_name = 'AMW_ORG_CONTROL'
331 AND aov.opinion_type_code = 'EVALUATION'
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)
341 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
342 );
343 CURSOR get_total_controls
344 IS
345 SELECT count(1)
346 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.control_id
347 FROM amw_control_associations aca, amw_controls_b acb
348 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
349 AND acb.control_rev_id = aca.control_rev_id
350 AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
351 AND aca.pk1 = p_certification_id
352 AND aca.pk2 = p_org_id);
353
354 l_total_controls NUMBER;
355 l_ineff_controls NUMBER;
356 l_ctrl_prcnt NUMBER;
357
358 BEGIN
359 l_ineff_controls := 0;
360 OPEN get_ineff_controls;
361 FETCH get_ineff_controls into l_ineff_controls;
362 CLOSE get_ineff_controls;
363
364 l_total_controls := 0;
365 OPEN get_total_controls;
366 FETCH get_total_controls into l_total_controls;
367 CLOSE get_total_controls;
368
369 if l_total_controls = 0
370 then
371 RETURN l_total_controls;
372 else
373 l_ctrl_prcnt := round(l_ineff_controls/l_total_controls*100);
374 RETURN l_ctrl_prcnt;
375 END IF;
376 END get_ineff_ctrl_prcnt_for_org;
377
378
379 -- Get number of process given a certification and org within the certification
380 -- If significant process flag is passed then filter on significant process flag.
381 FUNCTION get_all_process_in_org
382 (p_certification_id IN NUMBER,
383 p_org_id in NUMBER,
384 p_sig_process in VARCHAR2)
385 RETURN NUMBER
386 IS
387 CURSOR get_all_processes
388 IS
389 SELECT count(distinct process_id)
390 FROM amw_execution_scope
391 WHERE organization_id = p_org_id
392 AND entity_id = p_certification_id
393 AND level_id > 4;
394 l_total_proc NUMBER;
395 BEGIN
396 l_total_proc := 0;
397 OPEN get_all_processes;
398 FETCH get_all_processes into l_total_proc;
399 CLOSE get_all_processes;
400 RETURN l_total_proc;
401 END get_all_process_in_org;
402
403
404 -- Get number of process certified given a certification and org within the certification
405 -- If significant process flag is passed then filter on significant process flag.
406 FUNCTION get_cert_process_in_org
407 (p_certification_id IN NUMBER,
408 p_org_id in NUMBER,
409 p_from_date in DATE,
410 p_to_date in DATE,
411 p_sig_process in VARCHAR2)
412 RETURN NUMBER
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
421 AND aes.level_id > 4
418 WHERE aes.entity_type = 'BUSIPROC_CERTIFICATION'
419 AND aes.organization_id = p_org_id
420 AND aes.entity_id = p_certification_id
422 AND apo.process_org_rev_id = aes.process_org_rev_id
423 AND nvl(apo.significant_process_flag, 'N') = nvl(p_sig_process, nvl(apo.significant_process_flag, 'N'))
424 AND opinion.pk1_value = aes.process_id
425 AND opinion.pk3_value = aes.organization_id
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
435 AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
436 AND aov2.authored_date < nvl(p_to_date, sysdate+1));
437 l_certified_proc NUMBER;
438 BEGIN
439 l_certified_proc := 0;
440 OPEN get_certified_processes;
441 FETCH get_certified_processes into l_certified_proc;
442 CLOSE get_certified_processes;
443 RETURN l_certified_proc;
444 END get_cert_process_in_org;
445
446
447 -- Get number of process certified with issues given a certification and org within the certification
448 -- If significant process flag is passed then filter on significant process flag.
449 FUNCTION get_process_cert_issues_in_org
450 (p_certification_id IN NUMBER,
451 p_org_id in NUMBER,
452 p_from_date in DATE,
453 p_to_date in DATE,
454 p_sig_process in VARCHAR2)
455 RETURN NUMBER
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
465 AND apo.process_org_rev_id = aes.process_org_rev_id
466 AND nvl(apo.significant_process_flag, 'N') = nvl(p_sig_process, nvl(apo.significant_process_flag, 'N'))
467 AND opinion.pk1_value = aes.process_id
468 AND opinion.pk3_value = aes.organization_id
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
478 AND aov2.authored_date >= nvl(p_from_date, sysdate-10000)
479 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
480 AND opinion.audit_result_code <> 'EFFECTIVE';
481 l_proc_CWI NUMBER;
482 BEGIN
483 l_proc_CWI := 0;
487 RETURN l_proc_CWI;
484 OPEN get_proc_cert_issues;
485 FETCH get_proc_cert_issues into l_proc_CWI;
486 CLOSE get_proc_cert_issues;
488 END get_process_cert_issues_in_org;
489
490
491 -----------------------------------------------------------------------------------------------------
492 -- ORG PROCESS FUNCTIONs
493
494 -- Get number of ineffective controls given a certification, process and org within the certification
495 -- If key controls flag is passed, then only key controls are considered.
496 FUNCTION get_ineff_ctrls_for_org_proc
497 (p_certification_id IN NUMBER,
498 p_org_id in NUMBER,
499 p_process_id in NUMBER,
500 p_from_date in DATE,
501 p_to_date in DATE,
502 p_key_ctrls_flag in VARCHAR2)
503 RETURN NUMBER
504 IS
505
506 CURSOR get_ineff_controls
507 IS
508 SELECT count(1)
512 AND acb.control_rev_id = aca.control_rev_id
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'
513 AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
514 AND aca.pk1 = p_certification_id
515 AND aca.pk2 = p_org_id
516 AND aca.pk3 IN (SELECT DISTINCT process_id
517 FROM amw_execution_scope
518 START WITH process_id = p_process_id
519 AND entity_type = aca.object_type
520 AND organization_id = p_org_id
521 AND entity_id = p_certification_id
522 CONNECT BY PRIOR process_id = parent_process_id
523 AND organization_id = PRIOR organization_id
524 AND entity_id = PRIOR entity_id
525 AND entity_type = PRIOR entity_type
526 )
527 AND aov.object_name = 'AMW_ORG_CONTROL'
528 AND aov.opinion_type_code = 'EVALUATION'
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)
538 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
539 );
540 l_ineff_ctrls NUMBER;
541 BEGIN
542 l_ineff_ctrls := 0;
543 OPEN get_ineff_controls;
544 FETCH get_ineff_controls into l_ineff_ctrls;
545 CLOSE get_ineff_controls;
546 RETURN l_ineff_ctrls;
547 END get_ineff_ctrls_for_org_proc;
548
549
550 -- Get number of evaluated controls given a certification, process and org within the certification
551 -- If key controls flag is passed, then only key controls are considered.
552 FUNCTION get_eval_ctrls_for_org_proc
553 (p_certification_id IN NUMBER,
554 p_org_id in NUMBER,
555 p_process_id in NUMBER,
556 p_from_date in DATE,
557 p_to_date in DATE,
558 p_key_ctrls_flag in VARCHAR2)
559 RETURN NUMBER
560 IS
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
570 AND aca.pk2 = p_org_id
571 AND aca.pk3 IN (SELECT DISTINCT process_id
572 FROM amw_execution_scope
573 START WITH process_id = p_process_id
574 AND entity_type = aca.object_type
575 AND organization_id = p_org_id
576 AND entity_id = p_certification_id
577 CONNECT BY PRIOR process_id = parent_process_id
578 AND organization_id = PRIOR organization_id
579 AND entity_id = PRIOR entity_id
580 AND entity_type = PRIOR entity_type
581 )
582 AND aov.object_name = 'AMW_ORG_CONTROL'
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)
592 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
593 AND aov.audit_result_code IS NOT NULL);
594 l_eval_ctrls NUMBER;
595 BEGIN
596 l_eval_ctrls := 0;
597 OPEN get_eval_controls;
598 FETCH get_eval_controls into l_eval_ctrls;
599 CLOSE get_eval_controls;
600 RETURN l_eval_ctrls;
601 END get_eval_ctrls_for_org_proc;
602
603 -- Get number of controls given a certification, process and org within the certification
604 -- If key controls flag is passed, then only key controls are considered.
605 FUNCTION get_total_ctrls_for_org_proc
606 (p_certification_id IN NUMBER,
607 p_org_id in NUMBER,
608 p_process_id in NUMBER,
609 p_key_ctrls_flag in VARCHAR2)
610 RETURN NUMBER
611 IS
612 CURSOR get_total_controls
613 IS
614 SELECT count(1)
615 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
616 FROM amw_control_associations aca, amw_controls_b acb
617 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
618 AND acb.control_rev_id = aca.control_rev_id
619 AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
620 AND aca.pk1 = p_certification_id
621 AND aca.pk2 = p_org_id
622 AND aca.pk3 IN (SELECT DISTINCT process_id
623 FROM amw_execution_scope
624 START WITH process_id = p_process_id
625 AND entity_type = aca.object_type
626 AND organization_id = p_org_id
627 AND entity_id = p_certification_id
628 CONNECT BY PRIOR process_id = parent_process_id
629 AND organization_id = PRIOR organization_id
630 AND entity_id = PRIOR entity_id
634 BEGIN
631 AND entity_type = PRIOR entity_type
632 ));
633 l_total_ctrls NUMBER;
635 l_total_ctrls := 0;
636 OPEN get_total_controls;
637 FETCH get_total_controls into l_total_ctrls;
638 CLOSE get_total_controls;
639 RETURN l_total_ctrls;
640 END get_total_ctrls_for_org_proc;
641
642
643 FUNCTION get_ineff_ctrl_prcnt_org_proc
644 (p_certification_id IN NUMBER,
645 p_org_id in NUMBER,
646 p_process_id in NUMBER,
647 p_from_date in DATE,
648 p_to_date in DATE,
649 p_key_ctrls_flag in VARCHAR2)
650 RETURN NUMBER
651 IS
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
661 AND aca.pk2 = p_org_id
662 AND aca.pk3 IN (SELECT DISTINCT process_id
663 FROM amw_execution_scope
664 START WITH process_id = p_process_id
665 AND entity_type = aca.object_type
666 AND organization_id = p_org_id
667 AND entity_id = p_certification_id
668 CONNECT BY PRIOR process_id = parent_process_id
669 AND organization_id = PRIOR organization_id
670 AND entity_id = PRIOR entity_id
671 AND entity_type = PRIOR entity_type
672 )
673 AND aov.object_name = 'AMW_ORG_CONTROL'
674 AND aov.opinion_type_code = 'EVALUATION'
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)
684 AND aov2.authored_date < nvl(p_to_date, sysdate+1))
685 );
686 CURSOR get_total_controls
687 IS
688 SELECT count(1)
689 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.control_id
690 FROM amw_control_associations aca, amw_controls_b acb
691 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
692 AND acb.control_rev_id = aca.control_rev_id
693 AND nvl(acb.key_mitigating, 'N') = nvl(p_key_ctrls_flag, nvl(acb.key_mitigating, 'N'))
694 AND aca.pk1 = p_certification_id
695 AND aca.pk2 = p_org_id
696 AND aca.pk3 IN (SELECT DISTINCT process_id
697 FROM amw_execution_scope
698 START WITH process_id = p_process_id
699 AND entity_type = aca.object_type
700 AND organization_id = p_org_id
701 AND entity_id = p_certification_id
702 CONNECT BY PRIOR process_id = parent_process_id
703 AND organization_id = PRIOR organization_id
704 AND entity_id = PRIOR entity_id
705 AND entity_type = PRIOR entity_type
706 ));
707 l_total_controls NUMBER;
708 l_ineff_controls NUMBER;
709 l_ctrl_prcnt NUMBER;
710
711 BEGIN
712 l_ineff_controls := 0;
713 OPEN get_ineff_controls;
714 FETCH get_ineff_controls into l_ineff_controls;
715 CLOSE get_ineff_controls;
716
717 l_total_controls := 0;
718 OPEN get_total_controls;
719 FETCH get_total_controls into l_total_controls;
720 CLOSE get_total_controls;
721
722 if l_total_controls = 0
723 then
724 RETURN l_total_controls;
725 else
726 l_ctrl_prcnt := round(l_ineff_controls/l_total_controls*100);
727 RETURN l_ctrl_prcnt;
728 END IF;
729 END get_ineff_ctrl_prcnt_org_proc;
730
731
732 FUNCTION get_unmit_risk_prcnt_org_proc
733 (p_certification_id IN NUMBER,
734 p_org_id in NUMBER,
735 p_process_id in NUMBER,
736 p_from_date in DATE,
737 p_to_date in DATE,
738 p_material_risks_flag in VARCHAR2)
739 RETURN NUMBER
740 IS
741 CURSOR get_total_risks
742 IS
743 SELECT count(1)
744 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
745 FROM amw_risk_associations ara, amw_risks_b arb
746 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
747 AND ara.risk_rev_id = arb.risk_rev_id
751 AND ara.pk3 IN (SELECT DISTINCT process_id
748 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
749 AND ara.pk1 = p_certification_id
750 AND ara.pk2 = p_org_id
752 FROM amw_execution_scope
753 START WITH process_id = p_process_id
754 AND entity_type = ara.object_type
755 AND organization_id = p_org_id
756 AND entity_id = p_certification_id
757 CONNECT BY PRIOR process_id = parent_process_id
758 AND organization_id = PRIOR organization_id
759 AND entity_id = PRIOR entity_id
760 AND entity_type = PRIOR entity_type
761 ));
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
771 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
772 AND ara.pk3 IN (SELECT DISTINCT process_id
773 FROM amw_execution_scope
774 START WITH process_id = p_process_id
775 AND entity_type = ara.object_type
776 AND organization_id = p_org_id
777 AND entity_id = p_certification_id
778 CONNECT BY PRIOR process_id = parent_process_id
779 AND organization_id = PRIOR organization_id
780 AND entity_id = PRIOR entity_id
781 AND entity_type = PRIOR entity_type
782 )
783 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
784 AND aov.opinion_type_code = 'EVALUATION'
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
793 AND aov2.pk4_value = aov.pk4_value
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)
794 AND aov2.pk3_value = aov.pk3_value
795 AND aov2.pk1_value = aov.pk1_value)
796 AND aov.audit_result_code <> 'EFFECTIVE'
797 );
798
799 l_total_risks NUMBER;
800 l_unmitigated_risks NUMBER;
801 l_risk_prcnt NUMBER;
802 BEGIN
803 l_total_risks := 0;
804 OPEN get_total_risks;
805 FETCH get_total_risks into l_total_risks;
806 CLOSE get_total_risks;
807
808 l_unmitigated_risks := 0;
809 OPEN get_unmitigated_risks;
810 FETCH get_unmitigated_risks into l_unmitigated_risks;
811 CLOSE get_unmitigated_risks;
812
813 if l_total_risks = 0
814 then
815 RETURN l_total_risks;
816 else
817 l_risk_prcnt := round(l_unmitigated_risks/l_total_risks*100);
818 RETURN l_risk_prcnt;
819 END IF;
820 END get_unmit_risk_prcnt_org_proc;
821
822 -- Get number of risks given a certification, process and org within the certification
823 -- If material risks flag is passed, then only material risks are considered.
824 FUNCTION get_total_risks_for_org_proc
825 (p_certification_id IN NUMBER,
826 p_org_id in NUMBER,
827 p_process_id in NUMBER,
828 p_material_risks_flag in VARCHAR2)
829 RETURN NUMBER
830 IS
831 CURSOR get_total_risks
832 IS
833 SELECT count(1)
834 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
835 FROM amw_risk_associations ara, amw_risks_b arb
836 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
837 AND ara.risk_rev_id = arb.risk_rev_id
838 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
839 AND ara.pk1 = p_certification_id
840 AND ara.pk2 = p_org_id
841 AND ara.pk3 IN (SELECT DISTINCT process_id
842 FROM amw_execution_scope
843 START WITH process_id = p_process_id
844 AND entity_type = ara.object_type
845 AND organization_id = p_org_id
846 AND entity_id = p_certification_id
847 CONNECT BY PRIOR process_id = parent_process_id
848 AND organization_id = PRIOR organization_id
849 AND entity_id = PRIOR entity_id
850 AND entity_type = PRIOR entity_type
851 ));
852 l_total_risks NUMBER;
853 BEGIN
854 l_total_risks := 0;
855 OPEN get_total_risks;
856 FETCH get_total_risks into l_total_risks;
857 CLOSE get_total_risks;
858 RETURN l_total_risks;
859 END get_total_risks_for_org_proc;
860
861
862 -- Get number of evaluated risks given a certification, process and org within the certification
863 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
867 p_org_id in NUMBER,
864 -- If material risks flag is passed, then only material risks are considered.
865 FUNCTION get_eval_risk_for_org_proc
866 (p_certification_id IN NUMBER,
868 p_process_id in NUMBER,
869 p_from_date in DATE,
870 p_to_date in DATE,
871 p_material_risks_flag in VARCHAR2)
872 RETURN NUMBER
873 IS
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
883 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
884 AND ara.pk3 IN (SELECT DISTINCT process_id
885 FROM amw_execution_scope
886 START WITH process_id = p_process_id
887 AND entity_type = ara.object_type
888 AND organization_id = p_org_id
889 AND entity_id = p_certification_id
890 CONNECT BY PRIOR process_id = parent_process_id
891 AND organization_id = PRIOR organization_id
892 AND entity_id = PRIOR entity_id
893 AND entity_type = PRIOR entity_type
894 )
895 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
896 AND aov.opinion_type_code = 'EVALUATION'
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
906 AND aov2.pk3_value = aov.pk3_value
907 AND aov2.pk1_value = aov.pk1_value)
908 AND aov.audit_result_code IS NOT NULL
909 );
910 l_eval_risks NUMBER;
911 BEGIN
912 l_eval_risks := 0;
913 OPEN get_eval_risks;
914 FETCH get_eval_risks into l_eval_risks;
915 CLOSE get_eval_risks;
916 RETURN l_eval_risks;
917 END get_eval_risk_for_org_proc;
918
919 -- Get number of unmitigated risks given a certification, org and process within the certification
920 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
921 -- If material risks flag is passed, then only material risks are considered.
922 FUNCTION get_unmit_risk_for_org_proc
923 (p_certification_id in NUMBER,
924 p_org_id in NUMBER,
925 p_process_id in NUMBER,
926 p_from_date in DATE,
927 p_to_date in DATE,
928 p_material_risks_flag in VARCHAR2)
929 RETURN NUMBER
930 IS
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
940 AND nvl(p_material_risks_flag, nvl(arb.material, 'N')) = nvl(arb.material, 'N')
941 AND ara.pk3 IN (SELECT DISTINCT process_id
942 FROM amw_execution_scope
943 START WITH process_id = p_process_id
944 AND entity_type = ara.object_type
945 AND organization_id = p_org_id
946 AND entity_id = p_certification_id
947 CONNECT BY PRIOR process_id = parent_process_id
948 AND organization_id = PRIOR organization_id
949 AND entity_id = PRIOR entity_id
950 AND entity_type = PRIOR entity_type
951 )
952 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
953 AND aov.opinion_type_code = 'EVALUATION'
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
963 AND aov2.pk3_value = aov.pk3_value
964 AND aov2.pk1_value = aov.pk1_value)
965 AND aov.audit_result_code <> 'EFFECTIVE'
966 );
967 l_unmit_risks NUMBER;
968
969 BEGIN
970 l_unmit_risks := 0;
971 OPEN get_unmitigated_risks;
972 FETCH get_unmitigated_risks into l_unmit_risks;
973 CLOSE get_unmitigated_risks;
974 RETURN l_unmit_risks;
975 END get_unmit_risk_for_org_proc;
976
977
978 -- Get number of sub orgs associated to the given process within a given org.
979 FUNCTION get_total_org
980 (p_certification_id in NUMBER,
981 p_org_id in NUMBER,
982 p_process_id in NUMBER)
983 RETURN NUMBER
984 IS
985 CURSOR get_total_org
986 IS
987 SELECT count(distinct processorg.organization_id)
988 FROM amw_execution_scope processorg
989 WHERE processorg.process_id = p_process_id
990 AND processorg.organization_id IN
991 (SELECT object_id
992 FROM amw_entity_hierarchies
993 START WITH parent_object_id = p_org_id
994 AND object_type = 'ORG'
995 AND entity_id = p_certification_id
996 AND entity_type='BUSIPROC_CERTIFICATION'
997 CONNECT BY parent_object_id = PRIOR object_id
998 AND parent_object_type = PRIOR object_type
1002 AND processorg.entity_type='BUSIPROC_CERTIFICATION';
999 AND entity_id = PRIOR entity_id
1000 AND entity_type = PRIOR entity_type)
1001 AND processorg.entity_id=p_certification_id
1003
1004 CURSOR get_var_total_org
1005 IS
1006 SELECT count(1)
1007 FROM (SELECT distinct procorg.organization_id, procorg.process_id
1008 FROM amw_execution_scope scp,
1009 amw_process_organization procorg
1010 WHERE scp.process_org_rev_id = procorg.process_org_rev_id
1011 AND procorg.standard_variation IN
1012 (select process_rev_id
1013 from amw_process
1014 where process_id = p_process_id)
1015 AND scp.organization_id IN
1016 (SELECT object_id
1017 FROM amw_entity_hierarchies
1018 START WITH parent_object_id = p_org_id
1019 AND object_type = 'ORG'
1020 AND entity_id = p_certification_id
1021 AND entity_type='BUSIPROC_CERTIFICATION'
1022 CONNECT BY parent_object_id = PRIOR object_id
1023 AND parent_object_type = PRIOR object_type
1024 AND entity_id = PRIOR entity_id
1025 AND entity_type = PRIOR entity_type)
1026 AND scp.entity_id=p_certification_id
1027 AND scp.entity_type='BUSIPROC_CERTIFICATION');
1028
1029 l_total_org NUMBER;
1030 l_var_total_org NUMBER;
1031 BEGIN
1032 l_total_org := 0;
1033 l_var_total_org := 0;
1034 OPEN get_total_org;
1035 FETCH get_total_org into l_total_org;
1036 CLOSE get_total_org;
1037
1038 OPEN get_var_total_org;
1039 FETCH get_var_total_org into l_var_total_org;
1040 CLOSE get_var_total_org;
1041
1042 l_total_org := l_total_org + l_var_total_org;
1043
1044 RETURN l_total_org;
1045
1046 END get_total_org;
1047
1048
1049 -- Get number of sub orgs certified with issues that are associated to the given process and within a given org.
1050 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
1051 FUNCTION get_total_org_cert_issues
1052 (p_certification_id in NUMBER,
1053 p_org_id in NUMBER,
1054 p_process_id in NUMBER,
1055 p_from_date in DATE,
1056 p_to_date in DATE)
1057 RETURN NUMBER
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'
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
1074 AND aov2.pk3_value = opinion.pk3_value
1075 AND aov2.pk1_value = opinion.pk1_value)
1076 AND opinion.pk3_value IN
1077 (SELECT object_id
1078 FROM amw_entity_hierarchies
1079 START WITH parent_object_id = p_org_id
1080 AND object_type = 'ORG'
1081 AND entity_id = p_certification_id
1082 AND entity_type='BUSIPROC_CERTIFICATION'
1083 CONNECT BY parent_object_id = PRIOR object_id
1084 AND parent_object_type = PRIOR object_type
1085 AND entity_id = PRIOR entity_id
1086 AND entity_type = PRIOR entity_type)
1087 AND exists (select 'Y' from amw_execution_scope scope
1088 where scope.entity_type='BUSIPROC_CERTIFICATION'
1089 and scope.entity_id=p_certification_id
1090 and scope.organization_id=opinion.pk3_value
1091 and scope.process_id=opinion.pk1_value);
1092
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
1102 AND opinion.pk3_value = scp.organization_id
1103 AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
1104 AND scp.entity_id = p_certification_id
1105 AND scp.process_org_rev_id = procorg.process_org_rev_id
1106 AND procorg.standard_variation in
1107 (select process_rev_id
1108 from amw_process
1109 where process_id = p_process_id)
1110 AND scp.organization_id IN
1111 (SELECT object_id
1112 FROM amw_entity_hierarchies
1113 START WITH parent_object_id = p_org_id
1114 AND object_type = 'ORG'
1115 AND entity_id = p_certification_id
1116 AND entity_type='BUSIPROC_CERTIFICATION'
1117 CONNECT BY parent_object_id = PRIOR object_id
1118 AND parent_object_type = PRIOR object_type
1119 AND entity_id = PRIOR entity_id
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
1129 AND aov2.pk3_value = opinion.pk3_value
1130 AND aov2.pk1_value = opinion.pk1_value)
1131 AND opinion.audit_result_code <> 'EFFECTIVE');
1132 l_total_org NUMBER;
1136 l_var_total_org := 0;
1133 l_var_total_org NUMBER;
1134 BEGIN
1135 l_total_org := 0;
1137 OPEN get_org_proc_cert_issues;
1138 FETCH get_org_proc_cert_issues into l_total_org;
1139 CLOSE get_org_proc_cert_issues;
1140
1141 OPEN get_var_org_proc_cert_issues;
1142 FETCH get_var_org_proc_cert_issues into l_var_total_org;
1143 CLOSE get_var_org_proc_cert_issues;
1144
1145 l_total_org := l_total_org + l_var_total_org;
1146 RETURN l_total_org;
1147 END get_total_org_cert_issues;
1148
1149
1150 -- Get number of sub orgs certified that are associated to the given process and within a given org.
1151 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
1152 FUNCTION get_total_org_cert
1153 (p_certification_id in NUMBER,
1154 p_org_id in NUMBER,
1155 p_process_id in NUMBER,
1156 p_from_date in DATE,
1157 p_to_date in DATE)
1158 RETURN NUMBER
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'
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
1174 AND aov2.pk3_value = opinion.pk3_value
1175 AND aov2.pk1_value = opinion.pk1_value)
1176 AND opinion.pk3_value IN
1177 (SELECT object_id
1178 FROM amw_entity_hierarchies
1179 START WITH parent_object_id = p_org_id
1180 AND object_type = 'ORG'
1181 AND entity_id = p_certification_id
1182 AND entity_type='BUSIPROC_CERTIFICATION'
1183 CONNECT BY parent_object_id = PRIOR object_id
1184 AND parent_object_type = PRIOR object_type
1185 AND entity_id = PRIOR entity_id
1186 AND entity_type = PRIOR entity_type)
1187 AND exists (select 'Y' from amw_execution_scope scope
1188 where scope.entity_type='BUSIPROC_CERTIFICATION'
1189 and scope.entity_id=p_certification_id
1190 and scope.organization_id=opinion.pk3_value
1191 and scope.process_id=opinion.pk1_value);
1192
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
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
1209 AND aov2.pk3_value = opinion.pk3_value
1210 AND aov2.pk1_value = opinion.pk1_value)
1211 AND scp.entity_type = 'BUSIPROC_CERTIFICATION'
1212 AND scp.entity_id = p_certification_id
1213 AND scp.process_org_rev_id = procorg.process_org_rev_id
1214 AND procorg.standard_variation in
1215 (select process_rev_id
1216 from amw_process
1217 where process_id = p_process_id)
1218 AND scp.organization_id IN
1219 (SELECT object_id
1220 FROM amw_entity_hierarchies
1221 START WITH parent_object_id = p_org_id
1222 AND object_type = 'ORG'
1223 AND entity_id = p_certification_id
1224 AND entity_type='BUSIPROC_CERTIFICATION'
1225 CONNECT BY parent_object_id = PRIOR object_id
1226 AND parent_object_type = PRIOR object_type
1227 AND entity_id = PRIOR entity_id
1228 AND entity_type = PRIOR entity_type)
1229 AND opinion.opinion_type_code = 'CERTIFICATION'
1230 AND opinion.object_name = 'AMW_ORG_PROCESS');
1231 l_total_org NUMBER;
1232 l_var_total_org NUMBER;
1233 BEGIN
1234 l_total_org := 0;
1235 l_var_total_org := 0;
1236 OPEN get_org_proc_certified;
1237 FETCH get_org_proc_certified into l_total_org;
1238 CLOSE get_org_proc_certified;
1239
1240 OPEN get_var_org_proc_certified;
1241 FETCH get_var_org_proc_certified into l_var_total_org;
1242 CLOSE get_var_org_proc_certified;
1243
1244 l_total_org := l_total_org + l_var_total_org;
1245 RETURN l_total_org;
1246 END get_total_org_cert;
1247
1248 -- Get number of sub processes of a process
1249 FUNCTION get_total_sub_process
1250 (p_certification_id in NUMBER,
1251 p_org_id in NUMBER,
1252 p_process_id in NUMBER)
1253 RETURN NUMBER
1254 IS
1255 CURSOR get_all_sub_processes
1256 IS
1257 SELECT count(distinct process_id)
1258 FROM amw_execution_scope
1259 START WITH parent_process_id = p_process_id
1260 AND organization_id = p_org_id
1261 AND entity_id = p_certification_id
1262 AND entity_type = 'BUSIPROC_CERTIFICATION'
1263 CONNECT BY PRIOR process_id = parent_process_id
1264 AND organization_id = PRIOR organization_id
1265 AND entity_id = PRIOR entity_id
1266 AND entity_type = PRIOR entity_type;
1267 l_total_proc NUMBER;
1268 BEGIN
1269 l_total_proc := 0;
1270 OPEN get_all_sub_processes;
1274 END get_total_sub_process;
1271 FETCH get_all_sub_processes into l_total_proc;
1272 CLOSE get_all_sub_processes;
1273 RETURN l_total_proc;
1275
1276 FUNCTION get_cert_sub_process
1277 (p_certification_id in NUMBER,
1278 p_org_id in NUMBER,
1279 p_process_id in NUMBER,
1280 p_from_date in DATE,
1281 p_to_date in DATE)
1282 RETURN NUMBER
1283 IS
1284 CURSOR get_cert_sub_process
1285 IS
1286 SELECT count(distinct process_id)
1287 FROM amw_execution_scope amw_exec
1288 WHERE EXISTS (SELECT opinion.opinion_id
1289 FROM amw_opinions_v opinion
1290 WHERE opinion.pk1_value = amw_exec.process_id
1291 AND opinion.pk3_value = p_org_id
1292 AND opinion.pk2_value = p_certification_id
1293 AND opinion.opinion_type_code = 'CERTIFICATION'
1294 AND opinion.object_name = 'AMW_ORG_PROCESS'
1295 )
1296 START WITH parent_process_id = p_process_id
1297 AND organization_id = p_org_id
1298 AND entity_id = p_certification_id
1299 AND entity_type = 'BUSIPROC_CERTIFICATION'
1300 CONNECT BY PRIOR process_id = parent_process_id
1301 AND organization_id = PRIOR organization_id
1302 AND entity_id = PRIOR entity_id
1303 AND entity_type = PRIOR entity_type;
1304 l_total_proc NUMBER;
1305 BEGIN
1306 l_total_proc := 0;
1307 OPEN get_cert_sub_process;
1308 FETCH get_cert_sub_process into l_total_proc;
1309 CLOSE get_cert_sub_process;
1310 RETURN l_total_proc;
1311 END get_cert_sub_process;
1312
1313 FUNCTION get_sub_process_cert_issues
1314 (p_certification_id in NUMBER,
1315 p_org_id in NUMBER,
1316 p_process_id in NUMBER,
1317 p_from_date in DATE,
1318 p_to_date in DATE)
1319 RETURN NUMBER
1320 IS
1321 CURSOR get_sub_process_cert_issues
1322 IS
1323 SELECT count(distinct process_id)
1324 FROM amw_execution_scope amw_exec
1325 WHERE EXISTS (SELECT opinion.opinion_id
1326 FROM amw_opinions_v opinion
1327 WHERE opinion.pk1_value = amw_exec.process_id
1328 AND opinion.pk3_value = p_org_id
1329 AND opinion.pk2_value = p_certification_id
1330 AND opinion.opinion_type_code = 'CERTIFICATION'
1331 AND opinion.object_name = 'AMW_ORG_PROCESS'
1332 AND opinion.audit_result_code <> 'EFFECTIVE'
1333 )
1334 START WITH parent_process_id = p_process_id
1335 AND organization_id = p_org_id
1336 AND entity_id = p_certification_id
1337 AND entity_type = 'BUSIPROC_CERTIFICATION'
1338 CONNECT BY PRIOR process_id = parent_process_id
1339 AND organization_id = PRIOR organization_id
1340 AND entity_id = PRIOR entity_id
1341 AND entity_type = PRIOR entity_type;
1342 l_total_proc NUMBER;
1343 BEGIN
1344 l_total_proc := 0;
1345 OPEN get_sub_process_cert_issues;
1346 FETCH get_sub_process_cert_issues into l_total_proc;
1347 CLOSE get_sub_process_cert_issues;
1348 RETURN l_total_proc;
1349 END get_sub_process_cert_issues;
1350
1351
1352 END AMW_ORG_PROC_CERT_DATED_SUMM;
[Home] [Help]