[Home] [Help]
PACKAGE BODY: APPS.AMW_ORG_CERT_AGGR_PKG
Source
1 PACKAGE BODY AMW_ORG_CERT_AGGR_PKG AS
2 /* $Header: amwocagb.pls 120.0 2005/09/30 15:54:43 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2 (30) := 'AMW_ORG_CERT_AGGR_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2 (15) := 'amwocagb.pls';
6
7
8 --
9 -- Private procedures
10 --
11
12 PROCEDURE recursive_populate_hier
13 (
14 p_certification_id IN NUMBER,
15 p_child_id IN NUMBER
16 )
17 IS
18 l_parent_id NUMBER;
19
20 CURSOR row_exists_check(c_parent_id NUMBER) IS
21 SELECT 1
22 FROM amw_full_entity_hier
23 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
24 AND entity_id = p_certification_id
25 AND object_type = 'SUBSIDIARY'
26 AND object_id = p_child_id
27 AND parent_object_type = 'SUBSIDIARY'
28 AND parent_object_id = c_parent_id;
29
30 l_dummy NUMBER;
31 BEGIN
32 IF p_child_id = -1 THEN
33 RETURN;
34 END IF;
35
36 -- Get the parent_id and insert a row in the table if appropriate
37 SELECT nvl(flv2.flex_value_id, -1) parent_id
38 INTO l_parent_id
39 FROM fnd_flex_values flv,
40 fnd_flex_value_children_v fchild,
41 fnd_flex_values flv2
42 WHERE fchild.flex_value (+)= flv.flex_value
43 AND fchild.flex_value_set_id (+)= flv.flex_value_set_id
44 AND flv2.flex_value (+)= fchild.parent_flex_value
45 AND flv2.flex_value_set_id (+)= fchild.flex_value_set_id
46 AND flv.flex_value_id = p_child_id;
47
48 OPEN row_exists_check(l_parent_id);
49 FETCH row_exists_check INTO l_dummy;
50
51 IF row_exists_check%NOTFOUND THEN
52 CLOSE row_exists_check;
53
54 INSERT INTO amw_full_entity_hier
55 (entity_hierarchy_id, entity_type, entity_id, object_type, object_id,
56 parent_object_type, parent_object_id, level_id, creation_date,
57 created_by, last_update_date, last_updated_by, last_update_login,
58 object_version_number, delete_flag)
59 VALUES(amw_full_entity_hier_s.nextval, 'BUSIPROC_CERTIFICATION',
60 p_certification_id, 'SUBSIDIARY', p_child_id, 'SUBSIDIARY',
61 l_parent_id, 1, sysdate, fnd_global.user_id, sysdate,
62 fnd_global.user_id, fnd_global.login_id, 1, 'N');
63
64 recursive_populate_hier(p_certification_id, l_parent_id);
65 ELSE
66 CLOSE row_exists_check;
67 END IF;
68
69 END recursive_populate_hier;
70
71
72 PROCEDURE recurse_aggregate
73 (
74 p_certification_id IN NUMBER,
75 p_subsidiary_id IN NUMBER
76 )
77 IS
78 l_parent_id NUMBER;
79
80 BEGIN
81 IF p_subsidiary_id = -1 THEN
82 RETURN;
83 END IF;
84
85 MERGE INTO amw_org_cert_aggr_sum sum_tab
86 USING
87 (SELECT nvl(SUM(nvl(SUB_ORG_CERT,0)),0) SUB_ORG_CERT,
88 nvl(SUM(nvl(TOTAL_SUB_ORG,0)),0) TOTAL_SUB_ORG,
89 nvl(SUM(nvl(PROCESSES_CERTIFIED,0)),0) PROCESSES_CERTIFIED,
90 nvl(SUM(nvl(TOTAL_PROCESSES,0)),0) TOTAL_PROCESSES,
91 nvl(SUM(nvl(EVALUATED_PROCESSES,0)),0) EVALUATED_PROCESSES,
92 nvl(SUM(nvl(INEFF_PROCESSES,0)),0) INEFF_PROCESSES,
93 nvl(SUM(nvl(UNMITIGATED_RISKS,0)),0) UNMITIGATED_RISKS,
94 nvl(SUM(nvl(EVALUATED_RISKS,0)),0) EVALUATED_RISKS,
95 nvl(SUM(nvl(TOTAL_RISKS,0)),0) TOTAL_RISKS,
96 nvl(SUM(nvl(INEFFECTIVE_CONTROLS,0)),0) INEFFECTIVE_CONTROLS,
97 nvl(SUM(nvl(EVALUATED_CONTROLS,0)),0) EVALUATED_CONTROLS,
98 nvl(SUM(nvl(TOTAL_CONTROLS,0)),0) TOTAL_CONTROLS,
99 nvl(SUM(nvl(OPEN_FINDINGS,0)),0) OPEN_FINDINGS,
100 nvl(SUM(nvl(OPEN_ISSUES,0)),0) OPEN_ISSUES,
101 nvl(SUM(nvl(TOP_ORG_PROCESSES,0)),0) TOP_ORG_PROCESSES,
102 nvl(SUM(nvl(TOP_ORG_PROC_PENDING_CERT,0)),0) TOP_ORG_PROC_PENDING_CERT,
103 nvl(SUM(nvl(SUB_ORG_CERT_ISSUES,0)),0) SUB_ORG_CERT_ISSUES,
104 nvl(SUM(nvl(PROC_CERT_ISSUES,0)),0) PROC_CERT_ISSUES
105 FROM (SELECT SUB_ORG_CERT,
106 TOTAL_SUB_ORG,
107 PROCESSES_CERTIFIED,
108 TOTAL_PROCESSES,
109 EVALUATED_PROCESSES,
110 INEFF_PROCESSES,
111 UNMITIGATED_RISKS,
112 EVALUATED_RISKS,
113 TOTAL_RISKS,
114 INEFFECTIVE_CONTROLS,
115 EVALUATED_CONTROLS,
116 TOTAL_CONTROLS,
117 OPEN_FINDINGS,
118 OPEN_ISSUES,
119 TOP_ORG_PROCESSES,
120 TOP_ORG_PROC_PENDING_CERT,
121 SUB_ORG_CERT_ISSUES,
122 PROC_CERT_ISSUES
123 FROM fnd_flex_values flv,
124 fnd_flex_value_children_v fchild,
125 fnd_flex_values flv_c,
126 amw_org_cert_aggr_sum ocas
127 WHERE flv.flex_value_id = p_subsidiary_id
128 AND fchild.flex_value_set_id = flv.flex_value_set_id
129 AND fchild.parent_flex_value = flv.flex_value
130 AND flv_c.flex_value_set_id = flv.flex_value_set_id
131 AND flv_c.flex_value = fchild.flex_value
132 AND ocas.certification_id = p_certification_id
133 AND ocas.object_type = 'SUBSIDIARY'
134 AND ocas.object_id = flv_c.flex_value_id
135 UNION ALL
136 SELECT SUB_ORG_CERT,
137 TOTAL_SUB_ORG,
138 PROCESSES_CERTIFIED,
139 TOTAL_PROCESSES,
140 EVALUATED_PROCESSES,
141 INEFF_PROCESSES,
142 UNMITIGATED_RISKS,
143 EVALUATED_RISKS,
144 TOTAL_RISKS,
145 INEFFECTIVE_CONTROLS,
146 EVALUATED_CONTROLS,
147 TOTAL_CONTROLS,
148 OPEN_FINDINGS,
149 OPEN_ISSUES,
150 TOP_ORG_PROCESSES,
151 TOP_ORG_PROC_PENDING_CERT,
152 SUB_ORG_CERT_ISSUES,
153 PROC_CERT_ISSUES
154 FROM fnd_flex_values flv,
155 hr_organization_information oi,
156 amw_org_cert_eval_sum oces
157 WHERE flv.flex_value_id = p_subsidiary_id
158 AND oi.org_information_context = 'AMW_AUDIT_UNIT'
159 AND oi.org_information1 = flv.flex_value
160 AND oi.org_information3 = flv.flex_value_set_id
161 AND oces.certification_id = p_certification_id
162 AND oces.organization_id = oi.organization_id) child_info) sum_query
163 ON (sum_tab.certification_id = p_certification_id AND
164 sum_tab.object_type = 'SUBSIDIARY' AND
165 sum_tab.object_id = p_subsidiary_id)
166 WHEN MATCHED THEN
167 UPDATE SET SUB_ORG_CERT = sum_query.SUB_ORG_CERT,
168 TOTAL_SUB_ORG = sum_query.TOTAL_SUB_ORG,
169 PROCESSES_CERTIFIED = sum_query.PROCESSES_CERTIFIED,
170 TOTAL_PROCESSES = sum_query.TOTAL_PROCESSES,
171 EVALUATED_PROCESSES = sum_query.EVALUATED_PROCESSES,
172 INEFF_PROCESSES = sum_query.INEFF_PROCESSES,
173 UNMITIGATED_RISKS = sum_query.UNMITIGATED_RISKS,
174 EVALUATED_RISKS = sum_query.EVALUATED_RISKS,
175 TOTAL_RISKS = sum_query.TOTAL_RISKS,
176 INEFFECTIVE_CONTROLS = sum_query.INEFFECTIVE_CONTROLS,
177 EVALUATED_CONTROLS = sum_query.EVALUATED_CONTROLS,
178 TOTAL_CONTROLS = sum_query.TOTAL_CONTROLS,
179 OPEN_FINDINGS = sum_query.OPEN_FINDINGS,
180 OPEN_ISSUES = sum_query.OPEN_ISSUES,
181 TOP_ORG_PROCESSES = sum_query.TOP_ORG_PROCESSES,
182 TOP_ORG_PROC_PENDING_CERT = sum_query.TOP_ORG_PROC_PENDING_CERT,
183 SUB_ORG_CERT_ISSUES = sum_query.SUB_ORG_CERT_ISSUES,
184 PROC_CERT_ISSUES = sum_query.PROC_CERT_ISSUES,
185 INEFF_PROCESSES_PRCNT =
186 decode(sum_query.total_processes,
187 0, 0,
188 trunc(100 * sum_query.ineff_processes /
189 sum_query.total_processes)),
190 UNMITIGATED_RISKS_PRCNT =
191 decode(sum_query.total_risks,
192 0, 0,
193 trunc(100 * sum_query.unmitigated_risks /
194 sum_query.total_risks)),
195 INEFF_CONTROLS_PRCNT =
196 decode(sum_query.total_controls,
197 0, 0,
198 trunc(100 * sum_query.ineffective_controls /
199 sum_query.total_controls)),
200 last_update_date = sysdate,
201 last_updated_by = fnd_global.user_id,
202 last_update_login = fnd_global.login_id
203 WHEN NOT MATCHED THEN
204 INSERT
205 (cert_org_aggr_sum_id,
206 certification_id,
207 object_type,
208 object_id,
209 sub_org_cert,
210 total_sub_org,
211 processes_certified,
212 total_processes,
213 evaluated_processes,
214 ineff_processes,
215 unmitigated_risks,
216 evaluated_risks,
217 total_risks,
218 ineffective_controls,
219 evaluated_controls,
220 total_controls,
221 open_findings,
222 open_issues,
223 top_org_processes,
224 top_org_proc_pending_cert,
225 created_by,
226 creation_date,
227 last_updated_by,
228 last_update_date,
229 last_update_login,
230 object_version_number,
231 sub_org_cert_issues,
232 proc_cert_issues,
233 ineff_processes_prcnt,
234 unmitigated_risks_prcnt,
235 ineff_controls_prcnt
236 )
237 VALUES
238 (amw_org_cert_aggr_sum_s.nextval,
239 p_certification_id,
240 'SUBSIDIARY',
241 p_subsidiary_id,
242 sum_query.sub_org_cert,
243 sum_query.total_sub_org,
244 sum_query.processes_certified,
245 sum_query.total_processes,
246 sum_query.evaluated_processes,
247 sum_query.ineff_processes,
248 sum_query.unmitigated_risks,
249 sum_query.evaluated_risks,
250 sum_query.total_risks,
251 sum_query.ineffective_controls,
252 sum_query.evaluated_controls,
253 sum_query.total_controls,
254 sum_query.open_findings,
255 sum_query.open_issues,
256 sum_query.top_org_processes,
257 sum_query.top_org_proc_pending_cert,
258 fnd_global.user_id,
259 sysdate,
260 fnd_global.user_id,
261 sysdate,
262 fnd_global.login_id,
263 1,
264 sum_query.sub_org_cert_issues,
265 sum_query.proc_cert_issues,
266 decode(sum_query.total_processes,
267 0, 0,
268 trunc(100 * sum_query.ineff_processes /
269 sum_query.total_processes)),
270 decode(sum_query.total_risks,
271 0, 0,
272 trunc(100 * sum_query.unmitigated_risks /
273 sum_query.total_risks)),
274 decode(sum_query.total_controls,
275 0, 0,
276 trunc(100 * sum_query.ineffective_controls /
277 sum_query.total_controls)));
278
279 -- Get the parent_id and recurse up the hierarchy
280 SELECT nvl(flv2.flex_value_id, -1) parent_id
281 INTO l_parent_id
282 FROM fnd_flex_values flv,
283 fnd_flex_value_children_v fchild,
284 fnd_flex_values flv2
285 WHERE fchild.flex_value (+)= flv.flex_value
286 AND fchild.flex_value_set_id (+)= flv.flex_value_set_id
287 AND flv2.flex_value (+)= fchild.parent_flex_value
288 AND flv2.flex_value_set_id (+)= fchild.flex_value_set_id
289 AND flv.flex_value_id = p_subsidiary_id;
290
291 recurse_aggregate(p_certification_id, l_parent_id);
292
293 END recurse_aggregate;
294
295 --
296 -- Public procedures
297 --
298
299 PROCEDURE populate_full_hierarchies
300 (
301 x_errbuf OUT NOCOPY VARCHAR2,
302 x_retcode OUT NOCOPY NUMBER,
303 p_certification_id IN NUMBER
304 )
305 IS
306 l_api_name CONSTANT VARCHAR2(30) := 'populate_full_hierarchies';
307
308 -- select all bottom-level subsidiaries in scope for the certification
309 CURSOR get_all_subs IS
310 SELECT DISTINCT fv.flex_value_id
311 FROM amw_execution_scope es,
312 fnd_flex_values fv
313 WHERE es.entity_type = 'BUSIPROC_CERTIFICATION'
314 AND es.entity_id = p_certification_id
315 AND es.level_id = 3
316 AND fv.flex_value_set_id = es.subsidiary_vs
317 AND fv.flex_value = es.subsidiary_code
318 AND fv.flex_value_set_id = fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT');
319
320 BEGIN
321 fnd_file.put_line(fnd_file.LOG, 'begin '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
322
323 fnd_file.put_line(fnd_file.LOG, 'Certification_Id:'||p_certification_id);
324
325 -- First clear out the hierarchy
326 DELETE FROM amw_full_entity_hier
327 WHERE entity_id = p_certification_id;
328
329 -- Loop through the flex values and create the hierarchy
330 FOR sub_info IN get_all_subs LOOP
331 recursive_populate_hier(p_certification_id, sub_info.flex_value_id);
332 END LOOP;
333
334 populate_org_cert_aggr_rows(p_certification_id);
335
336 fnd_file.put_line(fnd_file.LOG, 'end '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 fnd_file.put_line(fnd_file.LOG, 'error '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
341 RAISE;
342 END populate_full_hierarchies;
343
344
345 PROCEDURE populate_org_cert_aggr_rows
346 (
347 p_certification_id IN NUMBER
348 )
349 IS
350 l_api_name CONSTANT VARCHAR2(30) := 'populate_org_cert_aggr_rows';
351
352 -- select all bottom-level subsidiaries in scope for the certification
353 CURSOR get_all_subs IS
354 SELECT DISTINCT fv.flex_value_id
355 FROM amw_execution_scope es,
356 fnd_flex_values fv
357 WHERE es.entity_type = 'BUSIPROC_CERTIFICATION'
358 AND es.entity_id = p_certification_id
359 AND es.level_id = 3
360 AND fv.flex_value_set_id = es.subsidiary_vs
361 AND fv.flex_value = es.subsidiary_code
362 AND fv.flex_value_set_id = fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT');
363
364 BEGIN
365 fnd_file.put_line(fnd_file.LOG, 'begin '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
366
367 SAVEPOINT populate_org_cert_aggr_rows;
368
369 -- First clear out the existing data
370 DELETE FROM amw_org_cert_aggr_sum
371 WHERE certification_id = p_certification_id;
372
373 -- Loop through the flex values and populate the aggregation info
374 FOR sub_info IN get_all_subs LOOP
375 recurse_aggregate(p_certification_id, sub_info.flex_value_id);
376 END LOOP;
377
378 fnd_file.put_line(fnd_file.LOG, 'end '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 ROLLBACK TO populate_org_cert_aggr_rows;
383 fnd_file.put_line(fnd_file.LOG, 'error '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
384 RAISE;
385 END populate_org_cert_aggr_rows;
386
387
388 PROCEDURE update_org_cert_aggr_rows
389 (
390 p_certification_id IN NUMBER,
391 p_organization_id IN NUMBER
392 )
393 IS
394 l_api_name CONSTANT VARCHAR2(30) := 'update_org_cert_aggr_rows';
395
396 l_flex_value_id NUMBER;
397 BEGIN
398 fnd_file.put_line(fnd_file.LOG, 'begin '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
399
400 SAVEPOINT update_org_cert_aggr_rows;
401
402 SELECT to_number(org_information1)
403 INTO l_flex_value_id
404 FROM hr_organization_information
405 WHERE organization_id = p_organization_id
406 AND org_information_context = 'AMW_AUDIT_UNIT'
407 AND org_information3 = fnd_profile.value('AMW_SUBSIDIARY_AUDIT_UNIT');
408
409 recurse_aggregate(p_certification_id, l_flex_value_id);
410
411
412 fnd_file.put_line(fnd_file.LOG, 'end '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 ROLLBACK TO update_org_cert_aggr_rows;
417 fnd_file.put_line(fnd_file.LOG, 'error '||g_pkg_name||'.'||l_api_name||': '||to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
418 RAISE;
419 END update_org_cert_aggr_rows;
420
421
422 END AMW_ORG_CERT_AGGR_PKG;