[Home] [Help]
PACKAGE BODY: APPS.RCI_ORG_DFCY_ETL_PKG
Source
1 PACKAGE BODY RCI_ORG_DFCY_ETL_PKG AS
2 --$Header: rciodfcyetlb.pls 120.6.12000000.1 2007/01/16 20:46:24 appldev ship $
3
4 -- Global Varaiables
5
6 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
7 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
8 C_OK CONSTANT NUMBER := 0; -- concurrent manager success code
9 C_ERRBUF_SIZE CONSTANT NUMBER := 300; -- length of formatted error message
10
11 -- User Defined Exceptions
12
13 INITIALIZATION_ERROR EXCEPTION;
14 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20900);
15 INITIALIZATION_ERROR_MESG CONSTANT VARCHAR2(200) := 'Error in Global setup';
16
17 -- File scope variables
18
19 g_global_start_date DATE;
20 g_rci_schema VARCHAR2(30);
21 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
22 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
23
24 -- Common Procedures (for initial and incremental load)
25
26 /**
27 FUNCTION get_master_organization_id
28 RETURN NUMBER;
29 **/
30
31 -- Common Procedures Definitions
32 -- check_initial_load_setup
33 -- Gets the GSD.
34 -- History:
35 -- Date Author Action
36 -- 09/05/2005 Panandikar Nilesh G Defined procedure.
37
38 PROCEDURE check_initial_load_setup (
39 x_global_start_date OUT NOCOPY DATE
40 ,x_rci_schema OUT NOCOPY VARCHAR2)
41 IS
42 l_proc_name VARCHAR2 (40);
43 l_stmt_id NUMBER;
44 l_setup_good BOOLEAN;
45 l_status VARCHAR2(30) ;
46 l_industry VARCHAR2(30) ;
47 l_message VARCHAR2(100);
48 BEGIN
49
50 -- Initialization
51 l_proc_name := 'setup_load';
52 l_stmt_id := 0;
53
54 -- Check for the global start date setup.
55 -- These parameter must be set up prior to any DBI load.
56
57 x_global_start_date := trunc (bis_common_parameters.get_global_start_date);
58 IF (x_global_start_date IS NULL) THEN
59 l_message := ' Global Start Date is NULL ';
60 RAISE INITIALIZATION_ERROR;
61 END IF;
62
63 l_setup_good := fnd_installation.get_app_info('AMW', l_status, l_industry, x_rci_schema);
64 IF (l_setup_good = FALSE OR x_rci_schema IS NULL) THEN
65 l_message := 'Schema not found';
66 RAISE INITIALIZATION_ERROR;
67 END IF;
68 EXCEPTION
69 WHEN INITIALIZATION_ERROR THEN
70 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (INITIALIZATION_ERROR_MESG || ':' || l_message,l_proc_name, l_stmt_id));
71 WHEN OTHERS THEN
72 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_id));
73 RAISE;
74 END check_initial_load_setup;
75
76 -- check_incr_load_setup
77 -- Gets the GSD.
78 -- History:
79 -- Date Author Action
80 -- 09/06/2005 Panandikar Nilesh G Defined procedure.
81
82 FUNCTION get_last_run_date(p_fact_name VARCHAR2)
83 RETURN DATE
84 IS
85 l_func_name VARCHAR2(40);
86 l_stmt_id NUMBER;
87 l_last_run_date DATE;
88 BEGIN
89 -- Initialization
90 l_func_name := 'get_last_run_date';
91 l_stmt_id := 0;
92
93 SELECT last_run_date
94 into l_last_run_date
95 FROM rci_dr_inc
96 WHERE fact_name = p_fact_name ;
97
98 RETURN l_last_run_date;
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg ('Please launch the Initial Load Request Set for the RCI Organization Certifications Summary page.'
102 ,l_func_name,l_stmt_id));
103 RAISE ;
104 WHEN OTHERS THEN
105 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_func_name,l_stmt_id));
106 RAISE ;
107 END get_last_run_date;
108
109
110 -- err_mesg
111 -- History:
112 -- Date Author Action
113 -- 09/06/2005 Panandikar Nilesh G Defined procedure.
114
115 FUNCTION err_mesg (
116 p_mesg IN VARCHAR2
117 ,p_proc_name IN VARCHAR2 DEFAULT NULL
118 ,p_stmt_id IN NUMBER DEFAULT -1)
119 RETURN VARCHAR2
120 IS
121 l_proc_name VARCHAR2 (60);
122 l_stmt_id NUMBER;
123 l_formatted_message VARCHAR2 (300) ;
124 BEGIN
125 l_formatted_message := substr ((p_proc_name || ' #' ||to_char (p_stmt_id) || ': ' || p_mesg),
126 1, C_ERRBUF_SIZE);
127 RETURN l_formatted_message;
128 EXCEPTION
129 WHEN OTHERS THEN
130 -- the exception happened in the exception reporting function !!
131 -- return with ERROR.
132 l_formatted_message := 'Error in error reporting.';
133 RETURN l_formatted_message;
134 END err_mesg;
135
136 -- run_initial_load
137 -- Parameters:
138 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
139 -- errbuf - empty on successful completion, message on error or warning
140 --
141 -- History:
142 -- Date Author Action
143 -- 08/31/2005 Nilesh Panandikar Defined Body.
144
145 PROCEDURE initial_load(
146 errbuf IN OUT NOCOPY VARCHAR2
147 ,retcode IN OUT NOCOPY NUMBER)
148 IS
149
150 cursor c_load_cols is
151 select certification_id
152 ,organization_id
153 ,process_id
154 from rci_org_proc_dfcy_f;
155
156 l_stmnt_id NUMBER := 0;
157 l_run_date DATE;
158 l_proc_name VARCHAR2(30);
159 l_status VARCHAR2(30) ;
160 l_industry VARCHAR2(30) ;
161 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
162
163 l_user_id NUMBER ;
164 l_login_id NUMBER ;
165 l_program_id NUMBER ;
166 l_program_login_id NUMBER ;
167 l_program_application_id NUMBER ;
168 l_request_id NUMBER ;
169
170 l_counter number := 0;
171 l_ineffective_controls number;
172 l_unmitigated_risks number;
173
174 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
175 l_key_control varchar2(2);
176 BEGIN
177 l_user_id := NVL(fnd_global.USER_ID, -1);
178 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
179 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
180 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
181 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
182 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
183
184 ----dbms_output.put_line( '1 **************' );
185
186 l_stmnt_id := 0;
187 l_proc_name := 'intitial_load';
188 check_initial_load_setup(
189 x_global_start_date => g_global_start_date
190 ,x_rci_schema => g_rci_schema);
191
192 l_stmnt_id := 10;
193 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_PROC_DFCY_F';
194
195 ----dbms_output.put_line( '2 **************' );
196
197 l_stmnt_id := 20;
198 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_PROC_DFCY_F');
199
200 ----dbms_output.put_line( '3 **************' );
201 l_stmnt_id := 30;
202 l_run_date := sysdate - 5/(24*60);
203
204 INSERT INTO RCI_ORG_PROC_DFCY_F(
205 project_id
206 ,fin_certification_id
207 ,certification_id
208 ,certification_status
209 ,certification_type
210 ,certification_period_name
211 ,certification_period_set_name
212 ,organization_id
213 ,process_id
214 ,significant_process_flag
215 ,standard_process_flag
216 ,certification_result_code
217 ,certified_by_id
218 ,evaluation_result_code
219 ,last_evaluated_by_id
220 ,last_evaluated_on
221 ,unmitigated_risks
222 ,ineffective_controls
223 ,natural_account_id
224 /** 10.20.2005 npanandi begin ***/
225 ,period_year
226 ,period_num
227 ,quarter_num
228 ,ent_period_id
229 ,ent_qtr_id
230 ,ent_year_id
231 ,report_date_julian
232 /** 10.20.2005 npanandi end ***/
233 ,creation_date
234 ,created_by
235 ,last_update_date
236 ,last_updated_by
237 ,last_update_login
238 ---,unmitigated_risks hold on to these for now
239 ---,ineffective_controls
240 ) select distinct aolv2.pk2_value /*project_id*/
241 ,afpes.fin_certification_id
242 ,-10000 /*certification_id*/
243 ,acb.certification_status
244 ,acb.certification_type
245 ,acb.certification_period_name
246 ,acb.certification_period_set_name
247 ,afpes.organization_id
248 ,afpes.process_id
249 ,upper(nvl(alrv.significant_process_flag,'N'))
250 ,upper(nvl(alrv.standard_process_flag,'N'))
251 ,aolv1.audit_result_code /*certification_result_code*/
252 ,aolv1.authored_by /*certified_by_id*/
253 ,aolv2.audit_result_code /*evaluation_result_code*/
254 ,aolv2.authored_by /*last_evaluated_by_id*/
255 ,aolv2.authored_date /*last_evaluated_on*/
256 ,null /*unmitigated_risks*/
257 ,null /*ineffective_controls*/
258 ,afkav.natural_account_id
259 ,agpv.period_year /*period_year*/
260 ,agpv.period_num /*period_num*/
261 ,agpv.quarter_num /*quarter_num*/
262 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) /*ent_period_id*/
263 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) /*ent_qtr_id*/
264 ,agpv.period_year /*ent_year_id*/
265 ,to_number(to_char(agpv.end_date,'J')) /*report_date_julian*/
266 ,sysdate
267 ,G_USER_ID
268 ,sysdate
269 ,G_USER_ID
270 ,G_LOGIN_ID
271 from amw_fin_process_eval_sum afpes
272 ,amw_certification_b acb
273 ,amw_latest_revisions_v alrv
274 ,amw_opinions_log_v aolv1
275 ,amw_opinions_log_v aolv2
276 ,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
277 ,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
278 ,amw_gl_periods_v agpv
279 where afpes.fin_certification_id = acb.certification_id
280 and afpes.process_id = alrv.process_id
281 and afpes.cert_opinion_log_id = aolv1.opinion_log_id(+)
282 and afpes.eval_opinion_log_id = aolv2.opinion_log_id(+)
283 and aaa.pk1(+) = afpes.organization_id
284 and aaa.pk2(+) = afpes.process_id
285 and aaa.natural_account_id = afkav.natural_account_id(+)
286 and acb.certification_period_name = agpv.period_name
287 and acb.certification_period_set_name = agpv.period_set_name
288 and exists (SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
289 ,o.organization_id
290 ,all_ctrls.control_id
291 ,op.audit_result_code
292 ,op.authored_by /*last_evaluated_by_id*/
293 ,op.authored_date /*last_evaluated_on*/
294 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
295 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
296 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
297 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
298 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
299 and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
300 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
301 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
302 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
303 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
304 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
305 and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
306 FROM AMW_CONTROL_ASSOCIATIONS ctrls,
307 AMW_CONTROLS_ALL_VL all_ctrls,
308 HR_ALL_ORGANIZATION_UNITS o,
309 HR_ALL_ORGANIZATION_UNITS_TL otl,
310 AMW_OPINIONS_LOG_V op
311 WHERE ctrls.object_type = 'RISK_FINCERT'
312 and ctrls.control_rev_id = all_ctrls.control_rev_id
313 and all_ctrls.APPROVAL_STATUS = 'A'
314 and o.organization_id = ctrls.pk2
315 and o.organization_id = otl.organization_id
316 and otl.language = userenv('LANG')
317 and op.opinion_log_id(+) = ctrls.pk5
318 and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
319 AND ctrls.pk1 = afpes.fin_certification_id
320 and o.ORGANIZATION_ID = afpes.organization_id
321 and ctrls.pk3 = afpes.process_id);
322 /****select distinct aov2.pk2_value ---project_id
323 ,aca.pk1 --fin_certified_id
324 ,-10000 --certification_id
325 ,acb.certification_status
326 ,acb.certification_type
327 ,acb.certification_period_name
328 ,acb.certification_period_set_name
329 ,haou.organization_id
330 ,alrv.process_id
331 ,nvl(alrv.significant_process_flag,'N')
332 ,nvl(alrv.standard_process_flag,'N')
333 ,aov1.audit_result_code --certification_result_code
334 ,aov1.authored_by --certified_by_id
335 ,aov2.audit_result_code --evaluation_result_code
336 ,aov2.authored_by --last_evaluated_by_id
337 ,aov2.authored_date --last_evaluated_on
338 ,null --unmitigated_risks
339 ,null --ineffective_controls
340 ,afkav.natural_account_id
341 ,agpv.period_year --period_year
342 ,agpv.period_num --period_num
343 ,agpv.quarter_num --quarter_num
344 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) --ent_period_id
345 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) --ent_qtr_id
346 ,agpv.period_year --ent_year_id
347 ,to_number(to_char(agpv.end_date,'J')) --report_date_julian
348 ,sysdate
349 ,G_USER_ID
350 ,sysdate
351 ,G_USER_ID
352 ,G_LOGIN_ID
353 from amw_control_associations aca
354 ,amw_opinions_log_v aolv
355 ,amw_latest_revisions_v alrv
356 ,HR_ALL_ORGANIZATION_UNITS haou
357 ,HR_ALL_ORGANIZATION_UNITS_TL haout
358 ,amw_opinions_v aov1
359 ,amw_opinions_v aov2
360 ,amw_certification_b acb
361 ,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
362 ,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
363 ,amw_gl_periods_v agpv
364 where aca.object_type='RISK_FINCERT'
365 and aca.pk5=aolv.opinion_log_id
366 and aca.pk3=alrv.process_id
367 and aca.pk2=haou.organization_id
368 and haou.organization_id=haout.organization_id
369 and haout.language=userenv('LANG')
370 and aov1.opinion_type_code='CERTIFICATION'
371 and aov1.object_name='AMW_ORG_PROCESS'
372 and aov1.pk1_value=alrv.process_id
373 and aov1.pk3_value=aca.pk2
374 and aov2.opinion_type_code='EVALUATION'
375 and aov2.object_name='AMW_ORG_PROCESS'
376 and aov2.pk1_value=alrv.process_id
377 and aov2.pk3_value=aca.pk2
378 and aca.pk1=acb.certification_id
379 and aaa.pk1(+) = aca.pk2
380 and aaa.pk2(+) = aca.pk3
381 and aaa.natural_account_id = afkav.natural_account_id(+)
382 and acb.certification_period_name = agpv.period_name
383 and acb.certification_period_set_name = agpv.period_set_name
384 and aov2.authored_date in (select max(aov.authored_date)
385 from AMW_OPINIONS aov
386 where aov.object_opinion_type_id = aov2.object_opinion_type_id
387 and aov.pk1_value = aov2.pk1_value
388 and aov.pk3_value = aov2.pk3_value);***/
389
390
391 for r_load_cols in c_load_cols loop
392 ----dbms_output.put_line( '' );
393 if(r_load_cols.certification_id is not null and
397 ,r_load_cols.organization_id
394 r_load_cols.organization_id is not null and
395 r_load_cols.process_id is not null) then
396 OPEN c_unmitigated_risks(r_load_cols.certification_id
398 ,r_load_cols.process_id);
399 FETCH c_unmitigated_risks INTO l_unmitigated_risks;
400 CLOSE c_unmitigated_risks;
401
402 OPEN c_ineffective_controls(r_load_cols.certification_id
403 ,r_load_cols.organization_id
404 ,r_load_cols.process_id);
405 FETCH c_ineffective_controls INTO l_ineffective_controls;
406 CLOSE c_ineffective_controls;
407
408 /** 12.12.2005 npanandi: added below for key_control
409 bug 4862301 fix
410 **/
411 SELECT decode(count(aca.control_id),0,'N','Y')
412 into l_key_control
413 FROM amw_control_associations aca
414 ,amw_controls_b acb
415 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
416 AND aca.pk1 = r_load_cols.certification_id
417 AND aca.pk2 = r_load_cols.organization_id
418 AND aca.pk3 IN (SELECT DISTINCT process_id
419 FROM amw_execution_scope
420 START WITH process_id = r_load_cols.process_id
421 AND organization_id = r_load_cols.organization_id
422 AND entity_id = r_load_cols.certification_id
423 and entity_type='BUSIPROC_CERTIFICATION'
424 CONNECT BY PRIOR process_id = parent_process_id
425 AND organization_id = PRIOR organization_id
426 AND entity_id = PRIOR entity_id
427 and entity_type=prior entity_type)
428 and aca.control_id = acb.control_id
429 and acb.curr_approved_flag = 'Y'
430 and nvl(acb.key_mitigating,'N') = 'Y';
431 /** 12.12.2005 npanandi: ends bug 4862301 fix **/
432 end if;
433
434 update RCI_ORG_PROC_DFCY_F
435 set unmitigated_risks = l_unmitigated_risks
436 ,ineffective_controls = l_ineffective_controls
437 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
438 ,key_control = l_key_control
439 where certification_id = r_load_cols.certification_id
440 and organization_id = r_load_cols.organization_id
441 and process_id = r_load_cols.process_id;
442 end loop;
443
444
445 ----dbms_output.put_line( '4 **************' );
446
447 l_stmnt_id :=50;
448
449 ----dbms_output.put_line( '5 **************' );
450
451 l_stmnt_id :=60;
452 INSERT INTO rci_dr_inc( fact_name
453 ,last_run_date
454 ,created_by
455 ,creation_date
456 ,last_update_date
457 ,last_updated_by
458 ,last_update_login
459 ,program_id
460 ,program_login_id
461 ,program_application_id
462 ,request_id ) VALUES (
463 'RCI_ORG_PROC_DFCY_F'
464 ,l_run_date
465 ,l_user_id
466 ,sysdate
467 ,sysdate
468 ,l_user_id
469 ,l_login_id
470 ,l_program_id
471 ,l_program_login_id
472 ,l_program_application_id
473 ,l_request_id );
474
475 ----dbms_output.put_line( '6 **************' );
476 l_stmnt_id := 70;
477 commit;
478 retcode := C_OK;
479 EXCEPTION
480 WHEN OTHERS THEN
481 retcode := C_ERROR;
482 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
483 ROLLBACK;
484 RAISE;
485 END initial_load;
486
487
488 PROCEDURE initial_load_obsolete(
489 errbuf IN OUT NOCOPY VARCHAR2
490 ,retcode IN OUT NOCOPY NUMBER)
491 IS
492
493 cursor c_load_cols is
494 select certification_id
495 ,organization_id
496 ,process_id
497 from rci_org_proc_dfcy_f;
498
499 l_stmnt_id NUMBER := 0;
500 l_run_date DATE;
501 l_proc_name VARCHAR2(30);
502 l_status VARCHAR2(30) ;
503 l_industry VARCHAR2(30) ;
504 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
505
506 l_user_id NUMBER ;
507 l_login_id NUMBER ;
508 l_program_id NUMBER ;
509 l_program_login_id NUMBER ;
510 l_program_application_id NUMBER ;
511 l_request_id NUMBER ;
512
513 l_counter number := 0;
514 l_ineffective_controls number;
515 l_unmitigated_risks number;
516
517 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
518 l_key_control varchar2(2);
519 BEGIN
520 l_user_id := NVL(fnd_global.USER_ID, -1);
521 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
522 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
523 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
524 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
525 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
526
527 ----dbms_output.put_line( '1 **************' );
528
529 l_stmnt_id := 0;
530 l_proc_name := 'intitial_load';
531 check_initial_load_setup(
532 x_global_start_date => g_global_start_date
533 ,x_rci_schema => g_rci_schema);
537
534
535 l_stmnt_id := 10;
536 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_PROC_DFCY_F';
538 ----dbms_output.put_line( '2 **************' );
539
540 l_stmnt_id := 20;
541 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_PROC_DFCY_F');
542
543 ----dbms_output.put_line( '3 **************' );
544 l_stmnt_id := 30;
545 l_run_date := sysdate - 5/(24*60);
546
547 INSERT INTO RCI_ORG_PROC_DFCY_F(
548 project_id
549 ,fin_certification_id
550 ,certification_id
551 ,certification_status
552 ,certification_type
553 ,certification_period_name
554 ,certification_period_set_name
555 ,organization_id
556 ,process_id
557 ,significant_process_flag
558 ,standard_process_flag
559 ,certification_result_code
560 ,certified_by_id
561 ,evaluation_result_code
562 ,last_evaluated_by_id
563 ,last_evaluated_on
564 ,natural_account_id
565 /** 10.20.2005 npanandi begin ***/
566 ,period_year
567 ,period_num
568 ,quarter_num
569 ,ent_period_id
570 ,ent_qtr_id
571 ,ent_year_id
572 ,report_date_julian
573 /** 10.20.2005 npanandi end ***/
574 ,creation_date
575 ,created_by
576 ,last_update_date
577 ,last_updated_by
578 ,last_update_login
579 ---,unmitigated_risks hold on to these for now
580 ---,ineffective_controls
581 ) SELECT distinct aapv.AUDIT_PROJECT_ID,
582 acv2.certification_id,
583 execs.entity_id,
584 acv.CERTIFICATION_STATUS,
585 acv.CERTIFICATION_TYPE,
586 acv.CERTIFICATION_PERIOD_NAME,
587 acv.CERTIFICATION_PERIOD_SET_NAME,
588 execs.organization_id,
589 execs.process_id,
590 nvl(process.significant_process_flag,'N'),
591 nvl(process.standard_process_flag,'N'),
592 opinions_cert.audit_result_code,
593 opinions_cert.authored_by,
594 opinions_eval.audit_result_code,
595 opinions_eval.AUTHORED_BY,
596 opinions_eval.authored_date,
597 afkav.natural_account_id,
598 /** 10.20.2005 npanandi begin ***/
599 agpv.period_year,
600 agpv.period_num,
601 agpv.quarter_num,
602 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
603 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
604 agpv.period_year,
605 to_number(to_char(agpv.end_date,'J')),
606 /** 10.20.2005 npanandi end ***/
607 sysdate,
608 G_USER_ID,
609 sysdate,
610 G_USER_ID,
611 G_LOGIN_ID
612 FROM amw_proc_cert_eval_sum proccert,
613 amw_opinions_log_v opinions_eval,
614 amw_opinions_v opinions_cert,
615 amw_process_organization_vl process,
616 amw_execution_scope execs,
617 amw_audit_units_v audit_v,
618 amw_audit_projects_v aapv,
619 amw_certification_vl acv,
620 amw_certification_vl acv2,
621 AMW_FIN_PROC_CERT_RELAN afpcr,
622 (select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa,
623 AMW_FIN_KEY_ACCOUNTS_VL AFKAV
624 /** 10.20.2005 npanandi begin ***/
625 ,amw_gl_periods_v agpv
626 /** 10.20.2005 npanandi end ***/
627 WHERE execs.entity_id = opinions_cert.pk2_value(+)
628 AND execs.organization_id = opinions_cert.pk3_value(+)
629 AND execs.process_id = opinions_cert.pk1_value(+)
630 AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
631 AND execs.entity_id = proccert.certification_id
632 and acv.CERTIFICATION_ID = proccert.CERTIFICATION_ID
633 and acv.OBJECT_TYPE = 'PROCESS'
634 and aapv.audit_project_id(+) = opinions_eval.pk2_value
635 AND execs.process_org_rev_id = proccert.process_org_rev_id
636 AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
637 AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
638 AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
639 AND process.process_org_rev_id = execs.process_org_rev_id
640 AND process.organization_id = audit_v.organization_id
641 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
642 and afpcr.END_DATE is null
643 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
644 and acv2.object_type='FIN_STMT'
645 and aaa.pk1(+) = execs.organization_id
646 and aaa.pk2(+) = execs.process_id
647 and aaa.natural_account_id = afkav.natural_account_id(+)
648 /** 10.20.2005 npanandi begin ***/
649 and acv.certification_period_name = agpv.period_name
650 and acv.certification_period_set_name = agpv.period_set_name;
651 /** 10.20.2005 npanandi end ***/
652
653 for r_load_cols in c_load_cols loop
654 ----dbms_output.put_line( '' );
655 if(r_load_cols.certification_id is not null and
656 r_load_cols.organization_id is not null and
657 r_load_cols.process_id is not null) then
658 OPEN c_unmitigated_risks(r_load_cols.certification_id
659 ,r_load_cols.organization_id
660 ,r_load_cols.process_id);
664 OPEN c_ineffective_controls(r_load_cols.certification_id
661 FETCH c_unmitigated_risks INTO l_unmitigated_risks;
662 CLOSE c_unmitigated_risks;
663
665 ,r_load_cols.organization_id
666 ,r_load_cols.process_id);
667 FETCH c_ineffective_controls INTO l_ineffective_controls;
668 CLOSE c_ineffective_controls;
669
670 /** 12.12.2005 npanandi: added below for key_control
671 bug 4862301 fix
672 **/
673 SELECT decode(count(aca.control_id),0,'N','Y')
674 into l_key_control
675 FROM amw_control_associations aca
676 ,amw_controls_b acb
677 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
678 AND aca.pk1 = r_load_cols.certification_id
679 AND aca.pk2 = r_load_cols.organization_id
680 AND aca.pk3 IN (SELECT DISTINCT process_id
681 FROM amw_execution_scope
682 START WITH process_id = r_load_cols.process_id
683 AND organization_id = r_load_cols.organization_id
684 AND entity_id = r_load_cols.certification_id
685 and entity_type='BUSIPROC_CERTIFICATION'
686 CONNECT BY PRIOR process_id = parent_process_id
687 AND organization_id = PRIOR organization_id
688 AND entity_id = PRIOR entity_id
689 and entity_type=prior entity_type)
690 and aca.control_id = acb.control_id
691 and acb.curr_approved_flag = 'Y'
692 and nvl(acb.key_mitigating,'N') = 'Y';
693 /** 12.12.2005 npanandi: ends bug 4862301 fix **/
694 end if;
695
696 update RCI_ORG_PROC_DFCY_F
697 set unmitigated_risks = l_unmitigated_risks
698 ,ineffective_controls = l_ineffective_controls
699 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
700 ,key_control = l_key_control
701 where certification_id = r_load_cols.certification_id
702 and organization_id = r_load_cols.organization_id
703 and process_id = r_load_cols.process_id;
704 end loop;
705
706
707 ----dbms_output.put_line( '4 **************' );
708
709 l_stmnt_id :=50;
710
711 ----dbms_output.put_line( '5 **************' );
712
713 l_stmnt_id :=60;
714 INSERT INTO rci_dr_inc( fact_name
715 ,last_run_date
716 ,created_by
717 ,creation_date
718 ,last_update_date
719 ,last_updated_by
720 ,last_update_login
721 ,program_id
722 ,program_login_id
723 ,program_application_id
724 ,request_id ) VALUES (
725 'RCI_ORG_PROC_DFCY_F'
726 ,l_run_date
727 ,l_user_id
728 ,sysdate
729 ,sysdate
730 ,l_user_id
731 ,l_login_id
732 ,l_program_id
733 ,l_program_login_id
734 ,l_program_application_id
735 ,l_request_id );
736
737 ----dbms_output.put_line( '6 **************' );
738 l_stmnt_id := 70;
739 commit;
740 retcode := C_OK;
741 EXCEPTION
742 WHEN OTHERS THEN
743 retcode := C_ERROR;
744 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
745 ROLLBACK;
746 RAISE;
747 END initial_load_obsolete;
748
749
750 -- run_incr_load_opm
751 -- Parameters:
752 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
753 -- errbuf - empty on successful completion, message on error or warning
754 --
755 -- History:
756 -- Date Author Action
757 -- 09/06/2005 Panandikar Nilesh G Defined Body.
758
759 PROCEDURE incr_load(
760 errbuf in out NOCOPY VARCHAR2
761 ,retcode in out NOCOPY NUMBER)
762 IS
763 cursor c_load_cols is
764 select certification_id
765 ,organization_id
766 ,process_id
767 from rci_org_proc_dfcy_f;
768
769 l_stmnt_id NUMBER := 0;
770 l_run_date DATE;
771 l_last_run_date DATE;
772 l_proc_name VARCHAR2(30);
773 l_message VARCHAR2(30);
774 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
775 l_count NUMBER;
776
777 l_user_id NUMBER;
778 l_login_id NUMBER;
779 l_program_id NUMBER;
780 l_program_login_id NUMBER;
781 l_program_application_id NUMBER;
782 l_request_id NUMBER;
783
784 l_unmitigated_risks number;
785 l_ineffective_controls number;
786
787 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
788 l_key_control varchar2(2);
789 BEGIN
790 l_user_id := NVL(fnd_global.USER_ID, -1);
791 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
792 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
793 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
794 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
795 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
796
797 l_stmnt_id := 10;
798 l_proc_name := 'run_incr_load_drm';
799 l_last_run_date := get_last_run_date('RCI_ORG_PROC_DFCY_F');
800
801 IF l_last_run_date IS NULL THEN
805
802 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
803 RAISE INITIALIZATION_ERROR;
804 END IF;
806 l_stmnt_id := 20;
807 l_run_date := sysdate - 5/(24*60);
808 ---l_master_org := get_master_organization_id;
809
810 l_stmnt_id := 30;
811 /** 01.16.06 npanandi: added below procedure cal as RSG errors otherwise **/
812 check_initial_load_setup(
813 x_global_start_date => g_global_start_date
814 ,x_rci_schema => g_rci_schema);
815 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_PROC_DFCY_F');
816
817 l_stmnt_id := 40;
818
819 INSERT INTO RCI_ORG_PROC_DFCY_F(
820 project_id
821 ,fin_certification_id
822 ,certification_id
823 ,certification_status
824 ,certification_type
825 ,certification_period_name
826 ,certification_period_set_name
827 ,organization_id
828 ,process_id
829 ,significant_process_flag
830 ,standard_process_flag
831 ,certification_result_code
832 ,certified_by_id
833 ,evaluation_result_code
834 ,last_evaluated_by_id
835 ,last_evaluated_on
836 ,unmitigated_risks
837 ,ineffective_controls
838 ,natural_account_id
839 /** 10.20.2005 npanandi begin ***/
840 ,period_year
841 ,period_num
842 ,quarter_num
843 ,ent_period_id
844 ,ent_qtr_id
845 ,ent_year_id
846 ,report_date_julian
847 /** 10.20.2005 npanandi end ***/
848 ,creation_date
849 ,created_by
850 ,last_update_date
851 ,last_updated_by
852 ,last_update_login
853 ---,unmitigated_risks hold on to these for now
854 ---,ineffective_controls
855 ) select distinct aolv2.pk2_value /*project_id*/
856 ,afpes.fin_certification_id
857 ,-10000 /*certification_id*/
858 ,acb.certification_status
859 ,acb.certification_type
860 ,acb.certification_period_name
861 ,acb.certification_period_set_name
862 ,afpes.organization_id
863 ,afpes.process_id
864 ,upper(nvl(alrv.significant_process_flag,'N'))
865 ,upper(nvl(alrv.standard_process_flag,'N'))
866 ,aolv1.audit_result_code /*certification_result_code*/
867 ,aolv1.authored_by /*certified_by_id*/
868 ,aolv2.audit_result_code /*evaluation_result_code*/
869 ,aolv2.authored_by /*last_evaluated_by_id*/
870 ,aolv2.authored_date /*last_evaluated_on*/
871 ,null /*unmitigated_risks*/
872 ,null /*ineffective_controls*/
873 ,afkav.natural_account_id
874 ,agpv.period_year /*period_year*/
875 ,agpv.period_num /*period_num*/
876 ,agpv.quarter_num /*quarter_num*/
877 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) /*ent_period_id*/
878 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) /*ent_qtr_id*/
879 ,agpv.period_year /*ent_year_id*/
880 ,to_number(to_char(agpv.end_date,'J')) /*report_date_julian*/
881 ,sysdate
882 ,G_USER_ID
883 ,sysdate
884 ,G_USER_ID
885 ,G_LOGIN_ID
886 from amw_fin_process_eval_sum afpes
887 ,amw_certification_b acb
888 ,amw_latest_revisions_v alrv
889 ,amw_opinions_log_v aolv1
890 ,amw_opinions_log_v aolv2
891 ,(select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa
892 ,AMW_FIN_KEY_ACCOUNTS_VL AFKAV
893 ,amw_gl_periods_v agpv
894 where afpes.fin_certification_id = acb.certification_id
895 and afpes.process_id = alrv.process_id
896 and afpes.cert_opinion_log_id = aolv1.opinion_log_id(+)
897 and afpes.eval_opinion_log_id = aolv2.opinion_log_id(+)
898 and aaa.pk1(+) = afpes.organization_id
899 and aaa.pk2(+) = afpes.process_id
900 and aaa.natural_account_id = afkav.natural_account_id(+)
901 and acb.certification_period_name = agpv.period_name
902 and acb.certification_period_set_name = agpv.period_set_name
903 and exists (SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
904 ,o.organization_id
905 ,all_ctrls.control_id
906 ,op.audit_result_code
907 ,op.authored_by /*last_evaluated_by_id*/
908 ,op.authored_date /*last_evaluated_on*/
909 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
910 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
911 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
912 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
913 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
914 and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
915 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
916 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
917 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
918 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
919 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
920 and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
924 HR_ALL_ORGANIZATION_UNITS_TL otl,
921 FROM AMW_CONTROL_ASSOCIATIONS ctrls,
922 AMW_CONTROLS_ALL_VL all_ctrls,
923 HR_ALL_ORGANIZATION_UNITS o,
925 AMW_OPINIONS_LOG_V op
926 WHERE ctrls.object_type = 'RISK_FINCERT'
927 and ctrls.control_rev_id = all_ctrls.control_rev_id
928 and all_ctrls.APPROVAL_STATUS = 'A'
929 and o.organization_id = ctrls.pk2
930 and o.organization_id = otl.organization_id
931 and otl.language = userenv('LANG')
932 and op.opinion_log_id(+) = ctrls.pk5
933 and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
934 AND ctrls.pk1 = afpes.fin_certification_id
935 and o.ORGANIZATION_ID = afpes.organization_id
936 and ctrls.pk3 = afpes.process_id);
937
938 for r_load_cols in c_load_cols loop
939 ----dbms_output.put_line( '' );
940 if(r_load_cols.certification_id is not null and
941 r_load_cols.organization_id is not null and
942 r_load_cols.process_id is not null) then
943 OPEN c_unmitigated_risks(r_load_cols.certification_id
944 ,r_load_cols.organization_id
945 ,r_load_cols.process_id);
946 FETCH c_unmitigated_risks INTO l_unmitigated_risks;
947 CLOSE c_unmitigated_risks;
948
949 OPEN c_ineffective_controls(r_load_cols.certification_id
950 ,r_load_cols.organization_id
951 ,r_load_cols.process_id);
952 FETCH c_ineffective_controls INTO l_ineffective_controls;
953 CLOSE c_ineffective_controls;
954
955 /** 12.12.2005 npanandi: added below for key_control
956 bug 4862301 fix
957 **/
958 SELECT decode(count(aca.control_id),0,'N','Y')
959 into l_key_control
960 FROM amw_control_associations aca
961 ,amw_controls_b acb
962 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
963 AND aca.pk1 = r_load_cols.certification_id
964 AND aca.pk2 = r_load_cols.organization_id
965 AND aca.pk3 IN (SELECT DISTINCT process_id
966 FROM amw_execution_scope
967 START WITH process_id = r_load_cols.process_id
968 AND organization_id = r_load_cols.organization_id
969 AND entity_id = r_load_cols.certification_id
970 and entity_type='BUSIPROC_CERTIFICATION'
971 CONNECT BY PRIOR process_id = parent_process_id
972 AND organization_id = PRIOR organization_id
973 AND entity_id = PRIOR entity_id
974 and entity_type=prior entity_type)
975 and aca.control_id = acb.control_id
976 and acb.curr_approved_flag = 'Y'
977 and nvl(acb.key_mitigating,'N') = 'Y';
978 /** 12.12.2005 npanandi: ends bug 4862301 fix **/
979 end if;
980
981 update RCI_ORG_PROC_DFCY_F
982 set unmitigated_risks = l_unmitigated_risks
983 ,ineffective_controls = l_ineffective_controls
984 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
985 ,key_control = l_key_control
986 where certification_id = r_load_cols.certification_id
987 and organization_id = r_load_cols.organization_id
988 and process_id = r_load_cols.process_id;
989 end loop;
990
991
992 l_stmnt_id :=50;
993 UPDATE rci_dr_inc
994 SET last_run_date = l_run_date
995 ,last_update_date = sysdate
996 ,last_updated_by = l_user_id
997 ,last_update_login = l_login_id
998 ,program_id = l_program_id
999 ,program_login_id = l_program_login_id
1000 ,program_application_id = l_program_application_id
1001 ,request_id = l_request_id
1002 WHERE fact_name = 'RCI_ORG_PROC_DFCY_F' ;
1003
1004
1005 commit;
1006 retcode := C_OK;
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 retcode := C_ERROR;
1010 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
1011 ROLLBACK;
1012 RAISE;
1013 END incr_load;
1014
1015 PROCEDURE incr_load_obsolete(
1016 errbuf in out NOCOPY VARCHAR2
1017 ,retcode in out NOCOPY NUMBER)
1018 IS
1019 cursor c_load_cols is
1020 select certification_id
1021 ,organization_id
1022 ,process_id
1023 from rci_org_proc_dfcy_f;
1024
1025 l_stmnt_id NUMBER := 0;
1026 l_run_date DATE;
1027 l_last_run_date DATE;
1028 l_proc_name VARCHAR2(30);
1029 l_message VARCHAR2(30);
1030 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
1031 l_count NUMBER;
1032
1033 l_user_id NUMBER;
1034 l_login_id NUMBER;
1035 l_program_id NUMBER;
1036 l_program_login_id NUMBER;
1037 l_program_application_id NUMBER;
1038 l_request_id NUMBER;
1039
1040 l_unmitigated_risks number;
1041 l_ineffective_controls number;
1042
1043 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
1044 l_key_control varchar2(2);
1045 BEGIN
1046 l_user_id := NVL(fnd_global.USER_ID, -1);
1047 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1048 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
1049 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
1050 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
1051 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
1052
1053 l_stmnt_id := 10;
1054 l_proc_name := 'run_incr_load_drm';
1055 l_last_run_date := get_last_run_date('RCI_ORG_PROC_DFCY_F');
1056
1057 IF l_last_run_date IS NULL THEN
1058 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
1059 RAISE INITIALIZATION_ERROR;
1060 END IF;
1061
1062 l_stmnt_id := 20;
1063 l_run_date := sysdate - 5/(24*60);
1064 ---l_master_org := get_master_organization_id;
1065
1066 l_stmnt_id := 30;
1067 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_PROC_DFCY_F');
1068
1069 l_stmnt_id := 40;
1070
1071 INSERT INTO RCI_ORG_PROC_DFCY_F(
1072 project_id
1073 ,fin_certification_id
1074 ,certification_id
1075 ,certification_status
1076 ,certification_type
1077 ,certification_period_name
1078 ,certification_period_set_name
1079 ,organization_id
1080 ,process_id
1081 ,significant_process_flag
1082 ,standard_process_flag
1083 ,certification_result_code
1084 ,certified_by_id
1085 ,evaluation_result_code
1086 ,last_evaluated_by_id
1087 ,last_evaluated_on
1088 ,natural_account_id
1089 /** 10.20.2005 npanandi begin ***/
1090 ,period_year
1091 ,period_num
1092 ,quarter_num
1093 ,ent_period_id
1094 ,ent_qtr_id
1095 ,ent_year_id
1096 ,report_date_julian
1097 /** 10.20.2005 npanandi end ***/
1098 ,creation_date
1099 ,created_by
1100 ,last_update_date
1101 ,last_updated_by
1102 ,last_update_login
1103 ---,unmitigated_risks hold on to these for now
1104 ---,ineffective_controls
1105 ) SELECT distinct aapv.AUDIT_PROJECT_ID,
1106 acv2.certification_id,
1107 execs.entity_id,
1108 acv.CERTIFICATION_STATUS,
1109 acv.CERTIFICATION_TYPE,
1110 acv.CERTIFICATION_PERIOD_NAME,
1111 acv.CERTIFICATION_PERIOD_SET_NAME,
1112 execs.organization_id,
1113 execs.process_id,
1114 nvl(process.significant_process_flag,'N'),
1115 nvl(process.standard_process_flag,'N'),
1116 opinions_cert.audit_result_code,
1117 opinions_cert.authored_by,
1118 opinions_eval.audit_result_code,
1119 opinions_eval.AUTHORED_BY,
1120 opinions_eval.authored_date,
1121 afkav.natural_account_id,
1122 /** 10.20.2005 npanandi begin ***/
1123 agpv.period_year,
1124 agpv.period_num,
1125 agpv.quarter_num,
1126 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
1127 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
1128 agpv.period_year,
1129 to_number(to_char(agpv.end_date,'J')),
1130 /** 10.20.2005 npanandi end ***/
1131 sysdate,
1132 G_USER_ID,
1133 sysdate,
1134 G_USER_ID,
1135 G_LOGIN_ID
1136 FROM amw_proc_cert_eval_sum proccert,
1137 amw_opinions_log_v opinions_eval,
1138 amw_opinions_v opinions_cert,
1139 amw_process_organization_vl process,
1140 amw_execution_scope execs,
1141 amw_audit_units_v audit_v,
1142 amw_audit_projects_v aapv,
1143 amw_certification_vl acv,
1144 amw_certification_vl acv2,
1145 AMW_FIN_PROC_CERT_RELAN afpcr,
1146 (select pk1,pk2,NATURAL_ACCOUNT_ID from AMW_ACCT_ASSOCIATIONS where object_type='PROCESS_ORG' and approval_date is not null and deletion_approval_date is null) aaa,
1147 AMW_FIN_KEY_ACCOUNTS_VL AFKAV
1148 /** 10.20.2005 npanandi begin ***/
1149 ,amw_gl_periods_v agpv
1150 /** 10.20.2005 npanandi end ***/
1151 WHERE execs.entity_id = opinions_cert.pk2_value(+)
1152 AND execs.organization_id = opinions_cert.pk3_value(+)
1153 AND execs.process_id = opinions_cert.pk1_value(+)
1154 AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
1155 AND execs.entity_id = proccert.certification_id
1156 and acv.CERTIFICATION_ID = proccert.CERTIFICATION_ID
1157 and acv.OBJECT_TYPE = 'PROCESS'
1158 and aapv.audit_project_id(+) = opinions_eval.pk2_value
1159 AND execs.process_org_rev_id = proccert.process_org_rev_id
1160 AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
1161 AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
1162 AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
1163 AND process.process_org_rev_id = execs.process_org_rev_id
1164 AND process.organization_id = audit_v.organization_id
1168 and acv2.object_type='FIN_STMT'
1165 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
1166 and afpcr.END_DATE is null
1167 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
1169 and aaa.pk1(+) = execs.organization_id
1170 and aaa.pk2(+) = execs.process_id
1171 and aaa.natural_account_id = afkav.natural_account_id(+)
1172 /** 10.20.2005 npanandi begin ***/
1173 and acv.certification_period_name = agpv.period_name
1174 and acv.certification_period_set_name = agpv.period_set_name;
1175 /** 10.20.2005 npanandi end ***/
1176
1177 for r_load_cols in c_load_cols loop
1178 ----dbms_output.put_line( '' );
1179 if(r_load_cols.certification_id is not null and
1180 r_load_cols.organization_id is not null and
1181 r_load_cols.process_id is not null) then
1182 OPEN c_unmitigated_risks(r_load_cols.certification_id
1183 ,r_load_cols.organization_id
1184 ,r_load_cols.process_id);
1185 FETCH c_unmitigated_risks INTO l_unmitigated_risks;
1186 CLOSE c_unmitigated_risks;
1187
1188 OPEN c_ineffective_controls(r_load_cols.certification_id
1189 ,r_load_cols.organization_id
1190 ,r_load_cols.process_id);
1191 FETCH c_ineffective_controls INTO l_ineffective_controls;
1192 CLOSE c_ineffective_controls;
1193
1194 /** 12.12.2005 npanandi: added below for key_control
1195 bug 4862301 fix
1196 **/
1197 SELECT decode(count(aca.control_id),0,'N','Y')
1198 into l_key_control
1199 FROM amw_control_associations aca
1200 ,amw_controls_b acb
1201 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
1202 AND aca.pk1 = r_load_cols.certification_id
1203 AND aca.pk2 = r_load_cols.organization_id
1204 AND aca.pk3 IN (SELECT DISTINCT process_id
1205 FROM amw_execution_scope
1206 START WITH process_id = r_load_cols.process_id
1207 AND organization_id = r_load_cols.organization_id
1208 AND entity_id = r_load_cols.certification_id
1209 and entity_type='BUSIPROC_CERTIFICATION'
1210 CONNECT BY PRIOR process_id = parent_process_id
1211 AND organization_id = PRIOR organization_id
1212 AND entity_id = PRIOR entity_id
1213 and entity_type=prior entity_type)
1214 and aca.control_id = acb.control_id
1215 and acb.curr_approved_flag = 'Y'
1216 and nvl(acb.key_mitigating,'N') = 'Y';
1217 /** 12.12.2005 npanandi: ends bug 4862301 fix **/
1218 end if;
1219
1220 update RCI_ORG_PROC_DFCY_F
1221 set unmitigated_risks = l_unmitigated_risks
1222 ,ineffective_controls = l_ineffective_controls
1223 /** 12.12.2005 npanandi: bug 4862301 fix for keyControl **/
1224 ,key_control = l_key_control
1225 where certification_id = r_load_cols.certification_id
1226 and organization_id = r_load_cols.organization_id
1227 and process_id = r_load_cols.process_id;
1228 end loop;
1229
1230
1231 l_stmnt_id :=50;
1232 UPDATE rci_dr_inc
1233 SET last_run_date = l_run_date
1234 ,last_update_date = sysdate
1235 ,last_updated_by = l_user_id
1236 ,last_update_login = l_login_id
1237 ,program_id = l_program_id
1238 ,program_login_id = l_program_login_id
1239 ,program_application_id = l_program_application_id
1240 ,request_id = l_request_id
1241 WHERE fact_name = 'RCI_ORG_PROC_DFCY_F' ;
1242
1243
1244 commit;
1245 retcode := C_OK;
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 retcode := C_ERROR;
1249 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
1250 ROLLBACK;
1251 RAISE;
1252 END incr_load_obsolete;
1253
1254 END RCI_ORG_DFCY_ETL_PKG;