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