[Home] [Help]
PACKAGE BODY: APPS.RCI_COMPL_ENV_CHG_SUMM_PKG
Source
1 package body rci_compl_env_chg_summ_pkg as
2 /*$Header: rcicmpenvchgsumb.pls 120.11 2006/09/19 23:23:34 npanandi noship $*/
3
4 -- Global Varaiables
5 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
6 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
7 C_OK CONSTANT NUMBER := 0; -- concurrent manager success code
8 C_ERRBUF_SIZE CONSTANT NUMBER := 300; -- length of formatted error message
9
10 -- User Defined Exceptions
11 INITIALIZATION_ERROR EXCEPTION;
12 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20900);
13 INITIALIZATION_ERROR_MESG CONSTANT VARCHAR2(200) := 'Error in Global setup';
14
15 -- File scope variables
16 g_global_start_date DATE;
17 g_rci_schema VARCHAR2(30);
18 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
19 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
20
21 /*** 12.09.2005 npanandi: added below procedure ***/
22 PROCEDURE check_initial_load_setup (
23 x_global_start_date OUT NOCOPY DATE
24 ,x_rci_schema OUT NOCOPY VARCHAR2)
25 IS
26 l_proc_name VARCHAR2 (40);
27 l_stmt_id NUMBER;
28 l_setup_good BOOLEAN;
29 l_status VARCHAR2(30) ;
30 l_industry VARCHAR2(30) ;
31 l_message VARCHAR2(100);
32 BEGIN
33
34 -- Initialization
35 l_proc_name := 'setup_load';
36 l_stmt_id := 0;
37
38 -- Check for the global start date setup.
39 -- These parameter must be set up prior to any DBI load.
40
41 x_global_start_date := trunc (bis_common_parameters.get_global_start_date);
42 IF (x_global_start_date IS NULL) THEN
43 l_message := ' Global Start Date is NULL ';
44 RAISE INITIALIZATION_ERROR;
45 END IF;
46
47 l_setup_good := fnd_installation.get_app_info('AMW', l_status, l_industry, x_rci_schema);
48 IF (l_setup_good = FALSE OR x_rci_schema IS NULL) THEN
49 l_message := 'Schema not found';
50 RAISE INITIALIZATION_ERROR;
51 END IF;
52 EXCEPTION
53 WHEN INITIALIZATION_ERROR THEN
54 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (INITIALIZATION_ERROR_MESG || ':' || l_message,l_proc_name, l_stmt_id));
55 WHEN OTHERS THEN
56 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_id));
57 RAISE;
58 END check_initial_load_setup;
59
60 FUNCTION err_mesg (
61 p_mesg IN VARCHAR2
62 ,p_proc_name IN VARCHAR2 DEFAULT NULL
63 ,p_stmt_id IN NUMBER DEFAULT -1)
64 RETURN VARCHAR2
65 IS
66 l_proc_name VARCHAR2 (60);
67 l_stmt_id NUMBER;
68 l_formatted_message VARCHAR2 (300) ;
69 BEGIN
70 l_formatted_message := substr ((p_proc_name || ' #' ||to_char (p_stmt_id) || ': ' || p_mesg),
71 1, C_ERRBUF_SIZE);
72 RETURN l_formatted_message;
73 EXCEPTION
74 WHEN OTHERS THEN
75 -- the exception happened in the exception reporting function !!
76 -- return with ERROR.
77 l_formatted_message := 'Error in error reporting.';
78 RETURN l_formatted_message;
79 END err_mesg;
80
81 FUNCTION get_last_run_date(p_fact_name VARCHAR2)
82 RETURN DATE
83 IS
84 l_func_name VARCHAR2(40);
85 l_stmt_id NUMBER;
86 l_last_run_date DATE;
87 BEGIN
88 -- Initialization
89 l_func_name := 'get_last_run_date';
90 l_stmt_id := 0;
91
92 SELECT last_run_date
93 into l_last_run_date
94 FROM rci_dr_inc
95 WHERE fact_name = p_fact_name ;
96
97 RETURN l_last_run_date;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg ('Please launch the Initial Load Request Set for the RCI Organization Certifications Summary page.'
101 ,l_func_name,l_stmt_id));
102 RAISE ;
103 WHEN OTHERS THEN
104 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_func_name,l_stmt_id));
105 RAISE ;
106 END get_last_run_date;
107
108
109 procedure initial_load(
110 errbuf IN OUT NOCOPY VARCHAR2
111 ,retcode IN OUT NOCOPY NUMBER) is
112
113 /*02.02.2006 npanandi: changed below cursor for performance reasons
114 cursor cur_f is
115 select * from rci_compl_env_chg_summ_f;
116 */
117 cursor cur_f is
118 select distinct fin_certification_id,organization_id,process_id
119 from rci_compl_env_chg_summ_f
120 where organization_id is not null
121 and process_id is not null;
122
123 cur_rec cur_f%rowtype;
124
125 l_curr_rev_num number;
126 l_risk_count number;
127 l_control_count number;
128
129 l_user_id NUMBER ;
130 l_login_id NUMBER ;
131 l_program_id NUMBER ;
132 l_program_login_id NUMBER ;
133 l_program_application_id NUMBER ;
134 l_request_id NUMBER ;
135
136 l_stmnt_id NUMBER := 0;
137 l_run_date DATE;
138 l_proc_name VARCHAR2(30);
139 l_status VARCHAR2(30) ;
140 l_industry VARCHAR2(30) ;
141
142 l_significant_process varchar2(1);
143 l_key_control varchar2(1);
144 begin
145 l_user_id := NVL(fnd_global.USER_ID, -1);
146 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
147 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
148 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
149 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
150 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
151
152 ----dbms_output.put_line( '1 **************' );
153
154 l_stmnt_id := 0;
155 l_proc_name := 'intitial_load';
156 check_initial_load_setup(
157 x_global_start_date => g_global_start_date
158 ,x_rci_schema => g_rci_schema);
159
160 l_stmnt_id := 10;
161 DELETE FROM rci_dr_inc where fact_name = 'RCI_COMPL_ENV_CHG_SUMM_F';
162
163
164 -- change the following line to have schema.tablename
165 l_stmnt_id := 20;
166 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_COMPL_ENV_CHG_SUMM_F');
167
168 l_stmnt_id := 25;
169 l_run_date := sysdate - 5/(24*60);
170
171
172 l_stmnt_id := 30;
173 insert into rci_compl_env_chg_summ_f(
174 fin_certification_id,
175 cert_status,
176 cert_type,
177 cert_period_name,
178 cert_period_set_name,
179 statement_group_id,
180 financial_statement_id,
181 financial_item_id,
182 account_group_id,
183 natural_account_id,
184 organization_id,
185 process_id,
186 revision_number,
187 latest_appr_revision_number,
188 NEW_REVISIONS_SINCE,
189 REVISED_PROCESS,
190 Total_Risks,
191 Num_Changed_Risks,
192 Total_Controls,
193 Num_Changed_Controls,
194 period_year,
195 period_num,
196 quarter_num,
197 ent_period_id,
198 ent_qtr_id,
199 ent_year_id,
200 report_date_julian,
201 creation_date,
202 created_by,
203 last_update_date,
204 last_updated_by,
205 last_update_login) /*02.02.2006 npanandi: added distinct below*/
206 (select distinct sc.fin_certification_id,
207 b.certification_status,
208 b.certification_type,
209 b.certification_period_name,
210 b.certification_period_set_name,
211 /*02.02.2006 npanandi: not using the below columns for performance reasons*/
212 /*sc.statement_group_id,*/ -1000,
213 /*sc.financial_statement_id,*/ -1000,
214 /*sc.financial_item_id,*/ -1000,
215 /*sc.account_group_id,*/ -1000,
216 sc.natural_account_id,
217 sc.organization_id,
218 sc.process_id,
219 nvl(peval.revision_number,1),
220 0, 0, 0, 0, 0, 0, 0,
221 agpv.period_year,
222 agpv.period_num,
223 agpv.quarter_num,
224 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
225 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
226 agpv.period_year,
227 to_number(to_char(agpv.end_date,'J')),
228 sysdate,
229 G_USER_ID,
230 sysdate,
231 G_USER_ID,
232 G_LOGIN_ID
233 from amw_fin_cert_scope sc,
234 amw_fin_process_eval_sum peval,
235 amw_certification_b b,
236 amw_gl_periods_v agpv
237 where peval.fin_certification_id (+) = sc.fin_certification_id
238 and peval.organization_id (+) = sc.organization_id
239 and peval.process_id (+) = sc.process_id
240 and sc.fin_certification_id = b.certification_id
241 and b.certification_period_name = agpv.period_name
242 and b.certification_period_set_name = agpv.period_set_name);
243
244
245 l_stmnt_id := 40;
246 for cur_rec in cur_f loop
247 exit when cur_f%notfound;
248
249 if (cur_rec.organization_id is not null and cur_rec.process_id is not null) then
250 l_significant_process := NULL;
251 select revision_number, significant_process_flag
252 into l_curr_rev_num, l_significant_process
253 from amw_process_organization a
254 where a.approval_date is not null
255 and a.approval_end_date is null
256 and process_id = cur_rec.process_id
257 and organization_id = cur_rec.organization_id;
258
259 select count(risk_id)
260 into l_risk_count
261 from amw_risk_associations
262 where object_type = 'PROCESS_FINCERT'
263 and pk1 = cur_rec.fin_certification_id
264 and pk2 = cur_rec.organization_id
265 and pk3 = cur_rec.process_id;
266
267 select count(control_id)
268 into l_control_count
269 from amw_control_associations
270 where object_type = 'RISK_FINCERT'
271 and pk1 = cur_rec.fin_certification_id
272 and pk2 = cur_rec.organization_id
273 and pk3 = cur_rec.process_id;
274
275 select DECODE(count(ACA.control_id),0,'N','Y')
276 into l_key_control
277 from amw_control_associations ACA, AMW_CONTROLS_ALL_VL ACAV
278 where object_type = 'RISK_FINCERT'
279 and pk1 = cur_rec.fin_certification_id
280 and pk2 = cur_rec.organization_id
281 and pk3 = cur_rec.process_id
282 AND ACA.CONTROL_ID = ACAV.CONTROL_ID
283 AND ACAV.CURR_APPROVED_FLAG = 'Y'
284 AND NVL(ACAV.KEY_MITIGATING,'N') = 'Y';
285
286 update rci_compl_env_chg_summ_f
287 set LATEST_APPR_REVISION_NUMBER = l_curr_rev_num,
288 /**01.25.2006 npanandi: changed below math, since it results in
289 negative values at times***/
290 /***NEW_REVISIONS_SINCE = latest_appr_revision_number - revision_number,***/
291 NEW_REVISIONS_SINCE = l_curr_rev_num - revision_number,
292 /**REVISED_PROCESS = decode(NEW_REVISIONS_SINCE, 0, 0, 1),***/
293 REVISED_PROCESS = decode((l_curr_rev_num - revision_number), 0, 0, 1),
294 Total_Risks = l_risk_count,
295 Total_Controls = l_control_count,
296 Num_Changed_Risks = calculate_risks_chg(cur_rec.fin_certification_id,
297 cur_rec.organization_id,
298 cur_rec.process_id),
299 Num_Changed_Controls = calculate_cntrl_chg(cur_rec.fin_certification_id,
300 cur_rec.organization_id,
301 cur_rec.process_id),
302 significant_process = NVL(l_significant_process,'N'),
303 key_control = l_key_control
304 where fin_certification_id = cur_rec.fin_certification_id
305 and organization_id = cur_rec.organization_id
306 and process_id = cur_rec.process_id;
307
308
309 end if;
310 end loop;
311
312 l_stmnt_id :=50;
313 INSERT INTO rci_dr_inc( fact_name
314 ,last_run_date
315 ,created_by
316 ,creation_date
317 ,last_update_date
318 ,last_updated_by
319 ,last_update_login
320 ,program_id
321 ,program_login_id
322 ,program_application_id
323 ,request_id ) VALUES (
324 'RCI_COMPL_ENV_CHG_SUMM_F'
325 ,l_run_date
326 ,l_user_id
327 ,sysdate
328 ,sysdate
329 ,l_user_id
330 ,l_login_id
331 ,l_program_id
332 ,l_program_login_id
333 ,l_program_application_id
334 ,l_request_id );
335
336 ----dbms_output.put_line( '6 **************' );
337 l_stmnt_id := 70;
338 commit;
339 retcode := C_OK;
340
341 end initial_load;
342
343
344 -- currently incremental - initial, this needs to be reviewed
345 procedure incremental_load(
346 errbuf IN OUT NOCOPY VARCHAR2
347 ,retcode IN OUT NOCOPY NUMBER)
348 is
349 /*02.02.2006 npanandi: changed below cursor for performance reasons
350 cursor cur_f is
351 select * from rci_compl_env_chg_summ_f;
352 */
353 cursor cur_f is
354 select distinct fin_certification_id,organization_id,process_id
355 from rci_compl_env_chg_summ_f
356 where organization_id is not null
357 and process_id is not null;
358
359 cur_rec cur_f%rowtype;
360
361 l_curr_rev_num number;
362 l_risk_count number;
363 l_control_count number;
364
365 l_stmnt_id NUMBER := 0;
366 l_run_date DATE;
367 l_last_run_date DATE;
368 l_proc_name VARCHAR2(30);
369 l_message VARCHAR2(30);
370 l_count NUMBER;
371
372 l_user_id NUMBER;
373 l_login_id NUMBER;
374 l_program_id NUMBER;
375 l_program_login_id NUMBER;
376 l_program_application_id NUMBER;
377 l_request_id NUMBER;
378
379 l_significant_process varchar2(1);
380 l_key_control varchar2(1);
381 begin
382
383 /***
384 initial_load(
385 errbuf => errbuf
386 ,retcode => retcode);
387 ***/
388 l_user_id := NVL(fnd_global.USER_ID, -1);
389 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
390 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
391 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
392 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
393 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
394
395 l_stmnt_id := 10;
396 l_proc_name := 'run_incr_load_drm';
397 l_last_run_date := get_last_run_date('RCI_COMPL_ENV_CHG_SUMM_F');
398
399 IF l_last_run_date IS NULL THEN
400 l_message := 'Please launch the Initial Load Request Set for the Compliance Environment Change Summary page.';
401 RAISE INITIALIZATION_ERROR;
402 END IF;
403
404 l_stmnt_id := 20;
405 l_run_date := sysdate - 5/(24*60);
406
407 l_stmnt_id := 30;
408 /** 01.16.06 npanandi: added below procedure cal as RSG errors otherwise **/
409 check_initial_load_setup(
410 x_global_start_date => g_global_start_date
411 ,x_rci_schema => g_rci_schema);
412 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_COMPL_ENV_CHG_SUMM_F');
413
414
415 /*** initial load query comes here ***/
416 l_stmnt_id := 30;
417 insert into rci_compl_env_chg_summ_f(
418 fin_certification_id,
419 cert_status,
420 cert_type,
421 cert_period_name,
422 cert_period_set_name,
423 statement_group_id,
424 financial_statement_id,
425 financial_item_id,
426 account_group_id,
427 natural_account_id,
428 organization_id,
429 process_id,
430 revision_number,
431 latest_appr_revision_number,
432 NEW_REVISIONS_SINCE,
433 REVISED_PROCESS,
434 Total_Risks,
435 Num_Changed_Risks,
436 Total_Controls,
437 Num_Changed_Controls,
438 period_year,
439 period_num,
440 quarter_num,
441 ent_period_id,
442 ent_qtr_id,
443 ent_year_id,
444 report_date_julian,
445 creation_date,
446 created_by,
447 last_update_date,
448 last_updated_by,
449 last_update_login) /*02.02.2006 npanandi: added distinct below*/
450 (select distinct sc.fin_certification_id,
451 b.certification_status,
452 b.certification_type,
453 b.certification_period_name,
454 b.certification_period_set_name,
455 /*02.02.2006 npanandi: not using the below columns for performance reasons*/
456 /*sc.statement_group_id,*/ -1000,
457 /*sc.financial_statement_id,*/ -1000,
458 /*sc.financial_item_id,*/ -1000,
459 /*sc.account_group_id,*/ -1000,
460 sc.natural_account_id,
461 sc.organization_id,
462 sc.process_id,
463 nvl(peval.revision_number,1),
464 0, 0, 0, 0, 0, 0, 0,
465 agpv.period_year,
466 agpv.period_num,
467 agpv.quarter_num,
471 to_number(to_char(agpv.end_date,'J')),
468 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
469 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
470 agpv.period_year,
472 sysdate,
473 G_USER_ID,
474 sysdate,
475 G_USER_ID,
476 G_LOGIN_ID
477 from amw_fin_cert_scope sc,
478 amw_fin_process_eval_sum peval,
479 amw_certification_b b,
480 amw_gl_periods_v agpv
481 where peval.fin_certification_id (+) = sc.fin_certification_id
482 and peval.organization_id (+) = sc.organization_id
483 and peval.process_id (+) = sc.process_id
484 and sc.fin_certification_id = b.certification_id
485 and b.certification_period_name = agpv.period_name
486 and b.certification_period_set_name = agpv.period_set_name);
487
488
489 l_stmnt_id := 40;
490 for cur_rec in cur_f loop
491 exit when cur_f%notfound;
492
493 if (cur_rec.organization_id is not null and cur_rec.process_id is not null) then
494 l_significant_process := NULL;
495 select revision_number, significant_process_flag
496 into l_curr_rev_num, l_significant_process
497 from amw_process_organization a
498 where a.approval_date is not null
499 and a.approval_end_date is null
500 and process_id = cur_rec.process_id
501 and organization_id = cur_rec.organization_id;
502
503 select count(risk_id)
504 into l_risk_count
505 from amw_risk_associations
506 where object_type = 'PROCESS_FINCERT'
507 and pk1 = cur_rec.fin_certification_id
508 and pk2 = cur_rec.organization_id
509 and pk3 = cur_rec.process_id;
510
511 select count(control_id)
512 into l_control_count
513 from amw_control_associations
514 where object_type = 'RISK_FINCERT'
515 and pk1 = cur_rec.fin_certification_id
516 and pk2 = cur_rec.organization_id
517 and pk3 = cur_rec.process_id;
518
519 select DECODE(count(ACA.control_id),0,'N','Y')
520 into l_key_control
521 from amw_control_associations ACA, AMW_CONTROLS_ALL_VL ACAV
522 where object_type = 'RISK_FINCERT'
523 and pk1 = cur_rec.fin_certification_id
524 and pk2 = cur_rec.organization_id
525 and pk3 = cur_rec.process_id
526 AND ACA.CONTROL_ID = ACAV.CONTROL_ID
527 AND ACAV.CURR_APPROVED_FLAG = 'Y'
528 AND NVL(ACAV.KEY_MITIGATING,'N') = 'Y';
529
530 update rci_compl_env_chg_summ_f
531 set LATEST_APPR_REVISION_NUMBER = l_curr_rev_num,
532 /**01.25.2006 npanandi: changed below math, since it results in
533 negative values at times***/
534 /***NEW_REVISIONS_SINCE = latest_appr_revision_number - revision_number,***/
535 NEW_REVISIONS_SINCE = l_curr_rev_num - revision_number,
536 REVISED_PROCESS = decode(NEW_REVISIONS_SINCE, 0, 0, 1),
537 Total_Risks = l_risk_count,
538 Total_Controls = l_control_count,
539 Num_Changed_Risks = calculate_risks_chg(cur_rec.fin_certification_id,
540 cur_rec.organization_id,
541 cur_rec.process_id),
542 Num_Changed_Controls = calculate_cntrl_chg(cur_rec.fin_certification_id,
543 cur_rec.organization_id,
544 cur_rec.process_id),
545 significant_process = NVL(l_significant_process,'N'),
546 key_control = l_key_control
547 where fin_certification_id = cur_rec.fin_certification_id
548 and organization_id = cur_rec.organization_id
549 and process_id = cur_rec.process_id;
550
551
552 end if;
553 end loop;
554 /*** initial load query ends here ***/
555
556 l_stmnt_id :=50;
557
558 UPDATE rci_dr_inc
559 SET last_run_date = l_run_date
560 ,last_update_date = sysdate
561 ,last_updated_by = l_user_id
562 ,last_update_login = l_login_id
563 ,program_id = l_program_id
564 ,program_login_id = l_program_login_id
565 ,program_application_id = l_program_application_id
566 ,request_id = l_request_id
567 WHERE fact_name = 'RCI_COMPL_ENV_CHG_SUMM_F' ;
568
569 commit;
570 retcode := C_OK;
571 EXCEPTION
572 WHEN OTHERS THEN
573 retcode := C_ERROR;
574 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
575 ROLLBACK;
576 RAISE;
577 end incremental_load;
578
579
580 -- this function copmares the risks that were attached to an org-process
581 -- when the certification was created to the current list of risks.
582 -- the number returned is the sum of risks deleted and risks added.
583 -- Note that if a risk is deleted and added back, it may create revisions
584 -- but won't show up here. Also, if risk association attributes are changed,
585 -- process will get revised, as internally it'll be risk deleted+added,
586 -- but that won't show up here either. This is to keep the code
587 -- simple, and functionally, I feel this makes sense.
588 function calculate_risks_chg(cert_id in number,
589 org_id in number,
590 process_id in number) return number
591 is
592 l_cnt1 number;
593 l_cnt2 number;
594 begin
595 select count(risk_id)
596 into l_cnt1
597 from amw_risk_associations
598 where object_type = 'PROCESS_FINCERT'
599 and pk1 = cert_id
603 from amw_risk_associations
600 and pk2 = org_id
601 and pk3 = process_id
602 and risk_id not in (select risk_id
604 where object_type = 'PROCESS_ORG'
605 and pk1 = org_id
606 and pk2 = process_id
607 and approval_date is not null
608 and deletion_approval_date is null);
609
610
611 select count(risk_id)
612 into l_cnt2
613 from amw_risk_associations
614 where object_type = 'PROCESS_ORG'
615 and pk1 = org_id
616 and pk2 = process_id
617 and approval_date is not null
618 and deletion_approval_date is null
619 and risk_id not in (select risk_id
620 from amw_risk_associations
621 where object_type = 'PROCESS_FINCERT'
622 and pk1 = cert_id
623 and pk2 = org_id
624 and pk3 = process_id);
625
626 return l_cnt1+l_cnt2;
627 end calculate_risks_chg;
628
629 -- the same concept as calculate_risks_chg
630 function calculate_cntrl_chg(cert_id in number,
631 org_id in number,
632 process_id in number) return number
633 is
634 l_cnt1 number;
635 l_cnt2 number;
636 begin
637 select count(control_id)
638 into l_cnt1
639 from amw_control_associations
640 where object_type = 'RISK_FINCERT'
641 and pk1 = cert_id
642 and pk2 = org_id
643 and pk3 = process_id
644 and control_id not in (select control_id
645 from amw_control_associations
646 where object_type = 'RISK_ORG'
647 and pk1 = org_id
648 and pk2 = process_id
649 and approval_date is not null
650 and deletion_approval_date is null);
651
652 select count(control_id)
653 into l_cnt2
654 from amw_control_associations
655 where object_type = 'RISK_ORG'
656 and pk1 = org_id
657 and pk2 = process_id
658 and approval_date is not null
659 and deletion_approval_date is null
660 and control_id not in (select control_id
661 from amw_control_associations
662 where object_type = 'RISK_FINCERT'
663 and pk1 = cert_id
664 and pk2 = org_id
665 and pk3 = process_id);
666
667 return l_cnt1+l_cnt2;
668 end calculate_cntrl_chg;
669
670
671
672 PROCEDURE get_summ_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
673 x_custom_sql OUT NOCOPY VARCHAR2,
674 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
675 is
676
677 l_query0 VARCHAR2(32767);
678 l_query1 VARCHAR2(32767);
679 l_query2 VARCHAR2(32767);
680 l_act_sqlstmt varchar2(32767);
681 where_flag number := 1;
682 proc varchar2(100);
683 org varchar2(100);
684
685 v_period varchar2(100);
686 l_bind_rec BIS_QUERY_ATTRIBUTES;
687 begin
688 l_query0 := '';
689 l_query1 := '';
690 l_query2 := '';
691
692 FOR i in 1..p_param.COUNT LOOP
693
694 IF(p_param(i).parameter_name = 'VIEW_BY' AND
695 p_param(i).parameter_id = 'ORGANIZATION+RCI_ORG_AUDIT') THEN
696
697 l_query0 :=
698 'select f.organization_id VIEWBYID,
699 (select name from hr_all_organization_units_tl v
700 where v.organization_id= f.organization_id
701 and v.language = userenv(''LANG'')) VIEWBY,
702 count(distinct process_id) RCI_COMP_ENV_MEASURE3,
703 SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
704 sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
705 decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
706 sum(Total_Risks) RCI_COMP_ENV_ATT1,
707 sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
708 decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
709 sum(Total_Controls) RCI_COMP_ENV_ATT2,
710 sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
711 decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
712 from rci_compl_env_chg_summ_f f, fii_time_day ftd
713 where f.organization_id is not null
714 and f.report_date_julian = ftd.report_date_julian';
715
716 l_query2 := ' group by f.organization_id ';
717
718 END IF;
719
720 IF(p_param(i).parameter_name = 'VIEW_BY' AND
721 p_param(i).parameter_id = 'RCI_BP_CERT+RCI_BP_PROCESS') THEN
722
723 l_query0 :=
724 'select f.process_id VIEWBYID,
725 (select display_name from AMW_CURRENT_APPRVD_REV_V v
726 where v.process_id= f.process_id) VIEWBY,
727 count(distinct process_id) RCI_COMP_ENV_MEASURE3,
728 SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
729 sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
730 decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
731 sum(Total_Risks) RCI_COMP_ENV_ATT1,
732 sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
736 decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
733 decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
734 sum(Total_Controls) RCI_COMP_ENV_ATT2,
735 sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
737 from rci_compl_env_chg_summ_f f, fii_time_day ftd
738 where f.process_id is not null
739 and f.report_date_julian = ftd.report_date_julian';
740
741 l_query2 := ' group by f.process_id ';
742
743 END IF;
744
745 IF(p_param(i).parameter_name = 'VIEW_BY' AND
746 p_param(i).parameter_id = 'RCI_FS_CERT+RCI_FS_CERT') THEN
747
748 l_query0 :=
749 'select f.fin_certification_id VIEWBYID,
750 (select certification_name from amw_certification_vl v
751 where v.certification_id= f.fin_certification_id) VIEWBY,
752 count(distinct process_id) RCI_COMP_ENV_MEASURE3,
753 SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
754 sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
755 decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
756 sum(Total_Risks) RCI_COMP_ENV_ATT1,
757 sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
758 decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
759 sum(Total_Controls) RCI_COMP_ENV_ATT2,
760 sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
761 decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
762 from rci_compl_env_chg_summ_f f, fii_time_day ftd
763 where f.fin_certification_id is not null
764 and f.report_date_julian = ftd.report_date_julian';
765
766 l_query2 := ' group by f.fin_certification_id';
767
768 END IF;
769
770 IF(p_param(i).parameter_name = 'VIEW_BY' AND
771 p_param(i).parameter_id = 'RCI_FINANCIAL_ACCT+RCI_FINANCIAL_ACCT') THEN
772
773 l_query0 :=
774 'select f.natural_account_id VIEWBYID,
775 /**(select name from AMW_FIN_KEY_ACCOUNTS_TL tl
776 where tl.ACCOUNT_GROUP_ID= f.ACCOUNT_GROUP_ID
777 and tl.NATURAL_ACCOUNT_ID= f.NATURAL_ACCOUNT_ID
778 and tl.language=userenv('||'''LANG'''||')) VIEWBY,**/
779 rsav.value VIEWBY,
780 count(distinct process_id) RCI_COMP_ENV_MEASURE3,
781 SUM(REVISED_PROCESS) RCI_COMP_ENV_MEASURE2,
782 sum(NEW_REVISIONS_SINCE) RCI_COMP_ENV_MEASURE1,
783 decode(count(process_id), 0, null, SUM(REVISED_PROCESS)/count(process_id)*100) RCI_COMP_ENV_MEASURE4,
784 sum(Total_Risks) RCI_COMP_ENV_ATT1,
785 sum(Num_Changed_Risks) RCI_COMP_ENV_MEASURE5,
786 decode(sum(Total_Risks), 0, null, sum(Num_Changed_Risks)/sum(Total_Risks)*100) RCI_COMP_ENV_MEASURE6,
787 sum(Total_Controls) RCI_COMP_ENV_ATT2,
788 sum(Num_Changed_Controls) RCI_COMP_ENV_MEASURE7,
789 decode(sum(Total_Controls), 0, null, sum(Num_Changed_Controls)/sum(Total_Controls)*100) RCI_COMP_ENV_MEASURE8
790 from rci_compl_env_chg_summ_f f, fii_time_day ftd, RCI_SIGNIFICANT_ACCT_V rsav
791 where f.account_group_id is not null
792 and f.natural_account_id is not null
793 and f.natural_account_id = rsav.id
794 and f.report_date_julian = ftd.report_date_julian';
795
796 l_query2 := ' group by (rsav.value, f.natural_account_id)';
797
798 END IF;
799
800 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
801 p_param(i).parameter_id is NOT null) THEN
802 l_query1 := l_query1 || ' and fin_certification_id = '||p_param(i).parameter_id;
803 END IF;
804
805 IF(p_param(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
806 p_param(i).parameter_id is NOT null) THEN
807 l_query1 := l_query1 || ' and organization_id = '||p_param(i).parameter_id;
808 END IF;
809
810 IF(p_param(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
811 p_param(i).parameter_id is NOT null) THEN
812 l_query1 := l_query1 || ' and process_id = '||p_param(i).parameter_id;
813 END IF;
814
815 IF(p_param(i).parameter_name = 'RCI_FINANCIAL_ACCT+RCI_FINANCIAL_ACCT' AND
816 p_param(i).parameter_id is NOT null) THEN
817 l_query1 := l_query1 || ' and natural_account_id = '||p_param(i).parameter_id;
818 END IF;
819
820 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
821 p_param(i).parameter_id is NOT null) THEN
822 l_query1 := l_query1 || ' and cert_status = '||p_param(i).parameter_id;
823 END IF;
824
825 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
826 p_param(i).parameter_id is NOT null) THEN
827 l_query1 := l_query1 || ' and cert_type = '||p_param(i).parameter_id;
828 END IF;
829
830
831 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
832 p_param(i).parameter_id is NOT null) THEN
836 v_period := p_param(i).parameter_id;
833 /***05.05.2006 npanandi: use dynamic binding for time dimensions below
834 l_query1 := l_query1 || ' and ftd.ent_period_id = '||p_param(i).parameter_id;
835 ***/
837 l_query1 := l_query1 || ' and ftd.ent_period_id = :TIME1 ';
838 END IF;
839
840 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
841 p_param(i).parameter_id is NOT null) THEN
842 /***05.05.2006 npanandi: use dynamic binding for time dimensions below
843 l_query1 := l_query1 || ' and ftd.ent_qtr_id = '||p_param(i).parameter_id;
844 **/
845 v_period := p_param(i).parameter_id;
846 l_query1 := l_query1 || ' and ftd.ent_qtr_id = :TIME1 ';
847 END IF;
848
849 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
850 p_param(i).parameter_id is NOT null) THEN
851 /***05.05.2006 npanandi: use dynamic binding for time dimensions below
852 l_query1 := l_query1 || ' and ftd.ent_year_id = '||p_param(i).parameter_id;
853 **/
854 v_period := p_param(i).parameter_id;
855 l_query1 := l_query1 || ' and ftd.ent_year_id = :TIME1 ';
856 END IF;
857
858 /** 12.09.2005 npanandi: added below parameter checks -- bug 4862313 fix ***/
859 IF(p_param(i).parameter_name = 'DUMMY+DUMMY_LEVEL' AND
860 p_param(i).parameter_id is NOT null) THEN
861 if(p_param(i).parameter_id = 'Y') then
862 l_query1 := l_query1 || ' and significant_process = ''Y''';
863 elsif(p_param(i).parameter_id = 'N') then
864 l_query1 := l_query1 || ' and significant_process = ''N''';
865 end if;
866 ---l_query1 := l_query1 || ' ************ p_param('||i||').parameter_name: '||p_param(i).parameter_name||', p_param('||i||').parameter_id: '||p_param(i).parameter_id;
867 END IF;
868
869 IF(p_param(i).parameter_name = 'DUMMY_DIMENSION+DUMMY_DIMENSION_LEVEL' AND
870 p_param(i).parameter_id is NOT null) THEN
871 if(p_param(i).parameter_id = 'Y') then
872 l_query1 := l_query1 || ' and key_control = ''Y''';
873 elsif(p_param(i).parameter_id = 'N') then
874 l_query1 := l_query1 || ' and key_control = ''N''';
875 end if;
876 ---l_query1 := l_query1 || ' ************ p_param('||i||').parameter_name: '||p_param(i).parameter_name||', p_param('||i||').parameter_id: '||p_param(i).parameter_id;
877 END IF;
878 /** 12.09.2005 npanandi: ends bug 4862313 fix ***/
879
880 END LOOP;
881
882
883 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
884 l_act_sqlstmt := 'select VIEWBYID,VIEWBY,RCI_COMP_ENV_MEASURE3,RCI_COMP_ENV_MEASURE2
885 ,RCI_COMP_ENV_MEASURE1,RCI_COMP_ENV_MEASURE4,RCI_COMP_ENV_ATT1
886 ,RCI_COMP_ENV_MEASURE5,RCI_COMP_ENV_MEASURE6,RCI_COMP_ENV_ATT2
887 ,RCI_COMP_ENV_MEASURE7,RCI_COMP_ENV_MEASURE8
888 from (select t.*
889 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
890 from ( '||l_query0||l_query1||l_query2||'
891 ) t ) a
892 order by a.col_rank ';
893
894
895 x_custom_sql := l_act_sqlstmt;
896
897 /**05.05.2006 npanandi: adding code for dynamic binding of time period dimensions**/
898 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
899 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
900
901 x_custom_output.EXTEND;
902 l_bind_rec.attribute_name := ':TIME1';
903 l_bind_rec.attribute_value := v_period;
904 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
905 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
906 x_custom_output(x_custom_output.COUNT) := l_bind_rec;
907 /**05.05.2006 npanandi: finished code for dynamic binding of time period dimensions**/
908 end;
909
910
911 end rci_compl_env_chg_summ_pkg;