[Home] [Help]
PACKAGE BODY: APPS.RCI_UNMTG_RISKS_ETL_PKG
Source
1 PACKAGE BODY RCI_UNMTG_RISKS_ETL_PKG AS
2 --$Header: rciounmretlb.pls 120.7.12000000.1 2007/01/16 20:46:36 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 l_proc_w_ineff_ctrls number;
150 l_ineffective_ctrls number;
151 l_unmitigated_risks number;
152 l_open_issues number;
153
154 l_stmnt_id NUMBER := 0;
155 l_run_date DATE;
156 l_proc_name VARCHAR2(30);
157 l_status VARCHAR2(30) ;
158 l_industry VARCHAR2(30) ;
159 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
160
161 l_user_id NUMBER ;
162 l_login_id NUMBER ;
163 l_program_id NUMBER ;
164 l_program_login_id NUMBER ;
165 l_program_application_id NUMBER ;
166 l_request_id NUMBER ;
167
168 BEGIN
169 l_user_id := NVL(fnd_global.USER_ID, -1);
170 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
171 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
172 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
173 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
174 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
175
176
177 l_stmnt_id := 0;
178 l_proc_name := 'intitial_load';
179 check_initial_load_setup(
180 x_global_start_date => g_global_start_date
181 ,x_rci_schema => g_rci_schema);
182
183 l_stmnt_id := 10;
184 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_RISKS_F';
185
186
187 l_stmnt_id := 20;
188 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_RISKS_F');
189
190 l_stmnt_id := 30;
191 l_run_date := sysdate - 5/(24*60);
192
193 INSERT INTO RCI_ORG_CERT_RISKS_F(
194 project_id
195 ,fin_certification_id
196 ,certification_id
197 ,certification_status
198 ,certification_type
199 /** 10.20.2005 npanandi begin ***/
200 ,certification_period_name
201 ,certification_period_set_name
202 /** 10.20.2005 npanandi end ***/
203 ,organization_id
204 ,process_id
205 ,natural_account_id
206 ,risk_id
207 ,material
208 ,risk_impact
209 ,likelihood
210 ,last_evaluator_id
211 ,last_evaluated_on
212 ,risk_rev_id
213 ,audit_result_code
214 /** 10.20.2005 npanandi begin ***/
215 ,period_year
216 ,period_num
217 ,quarter_num
218 ,ent_period_id
219 ,ent_qtr_id
220 ,ent_year_id
221 ,report_date_julian
222 /** 10.20.2005 npanandi end ***/
223 ,creation_date
224 ,created_by
225 ,last_update_date
226 ,last_updated_by
227 ,last_update_login)
228 SELECT DISTINCT op.pk2_value, /*project_id*/
229 risks.pk1 as certification_id, /*fin_certification_id*/
230 -10000, /*certification_id, cannot insert NULL*/
231 acv.certification_status, /*certification_status*/
232 acv.certification_type, /*certification_type*/
233 acv.certification_period_name,
234 acv.certification_period_set_name,
235 proc.organization_id,
236 proc.process_id,
237 AFKAV.natural_account_id,
238 all_risks.risk_id,
239 nvl(all_risks.material,'N'),
240 all_risks.risk_impact,
241 all_risks.likelihood,
242 op.last_updated_by, /*last_evaluator_id*/
243 op.authored_date, /*last_evaluated_on*/
244 all_risks.risk_rev_id,
245 op.audit_result_code,
246 agpv.period_year,
247 agpv.period_num,
248 agpv.quarter_num,
249 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
250 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
251 agpv.period_year,
252 to_number(to_char(agpv.end_date,'J')),
253 sysdate,
254 G_USER_ID,
255 sysdate,
256 G_USER_ID,
257 G_LOGIN_ID
258 FROM AMW_RISK_ASSOCIATIONS risks,
259 AMW_RISKS_ALL_VL all_risks,
260 AMW_PROCESS_ORGANIZATION_VL proc,
261 HR_ALL_ORGANIZATION_UNITS o,
262 HR_ALL_ORGANIZATION_UNITS_TL otl,
263 AMW_OPINIONS_LOG_V op,
264 amw_certification_vl acv,
265 (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,
266 AMW_FIN_KEY_ACCOUNTS_VL AFKAV,
267 amw_gl_periods_v agpv
268 WHERE risks.object_type = 'PROCESS_FINCERT'
269 and all_risks.risk_rev_id = risks.risk_rev_id
270 and o.organization_id = risks.pk2
271 and o.organization_id = otl.organization_id
272 and otl.language = userenv('LANG')
273 and proc.organization_id = risks.pk2
274 and proc.process_id = risks.pk3
275 and risks.approval_date is not null
276 and proc.approval_status = 'A'
277 and proc.approval_date = risks.approval_date
278 and op.opinion_log_id(+) = risks.pk4
279 and risks.pk1 = acv.certification_id
280 and aaa.pk1(+) = proc.organization_id
281 and aaa.pk2(+) = proc.process_id
282 and aaa.natural_account_id = afkav.natural_account_id(+)
283 ----and op.audit_result_code <> 'EFFECTIVE'
284 and acv.certification_period_name = agpv.period_name
285 and acv.certification_period_set_name = agpv.period_set_name;
286
287 l_stmnt_id :=40;
288
289 INSERT INTO rci_dr_inc( fact_name
290 ,last_run_date
291 ,created_by
292 ,creation_date
293 ,last_update_date
294 ,last_updated_by
295 ,last_update_login
296 ,program_id
297 ,program_login_id
298 ,program_application_id
299 ,request_id ) VALUES (
300 'RCI_ORG_CERT_RISKS_F'
301 ,l_run_date
302 ,l_user_id
303 ,sysdate
304 ,sysdate
305 ,l_user_id
306 ,l_login_id
307 ,l_program_id
308 ,l_program_login_id
309 ,l_program_application_id
310 ,l_request_id );
311
312 l_stmnt_id := 50;
313 commit;
314 retcode := C_OK;
315 EXCEPTION
316 WHEN OTHERS THEN
317 retcode := C_ERROR;
318 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
319 ROLLBACK;
320 RAISE;
321 END initial_load;
322
323
324 PROCEDURE initial_load_obsolete(
325 errbuf IN OUT NOCOPY VARCHAR2
326 ,retcode IN OUT NOCOPY NUMBER)
327 IS
328 l_proc_w_ineff_ctrls number;
329 l_ineffective_ctrls number;
330 l_unmitigated_risks number;
331 l_open_issues number;
332
333 l_stmnt_id NUMBER := 0;
334 l_run_date DATE;
335 l_proc_name VARCHAR2(30);
336 l_status VARCHAR2(30) ;
337 l_industry VARCHAR2(30) ;
338 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
339
340 l_user_id NUMBER ;
341 l_login_id NUMBER ;
342 l_program_id NUMBER ;
343 l_program_login_id NUMBER ;
344 l_program_application_id NUMBER ;
345 l_request_id NUMBER ;
346
347 BEGIN
348 l_user_id := NVL(fnd_global.USER_ID, -1);
349 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
350 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
351 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
352 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
353 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
354
355
356 l_stmnt_id := 0;
357 l_proc_name := 'intitial_load';
358 check_initial_load_setup(
359 x_global_start_date => g_global_start_date
363 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_RISKS_F';
360 ,x_rci_schema => g_rci_schema);
361
362 l_stmnt_id := 10;
364
365
366 l_stmnt_id := 20;
367 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_RISKS_F');
368
369 l_stmnt_id := 30;
370 l_run_date := sysdate - 5/(24*60);
371
372 INSERT INTO RCI_ORG_CERT_RISKS_F(
373 project_id
374 ,fin_certification_id
375 ,certification_id
376 ,certification_status
377 ,certification_type
378 /** 10.20.2005 npanandi begin ***/
379 ,certification_period_name
380 ,certification_period_set_name
381 /** 10.20.2005 npanandi end ***/
382 ,organization_id
383 ,process_id
384 ,natural_account_id
385 ,risk_id
386 ,material
387 ,risk_impact
388 ,likelihood
389 ,last_evaluator_id
390 ,last_evaluated_on
391 ,risk_rev_id
392 ,audit_result_code
393 /** 10.20.2005 npanandi begin ***/
394 ,period_year
395 ,period_num
396 ,quarter_num
397 ,ent_period_id
398 ,ent_qtr_id
399 ,ent_year_id
400 ,report_date_julian
401 /** 10.20.2005 npanandi end ***/
402 ,creation_date
403 ,created_by
404 ,last_update_date
405 ,last_updated_by
406 ,last_update_login)
407 SELECT DISTINCT opinionstable.pk2_value,
408 acv2.certification_id,
409 assoctable.pk1,
410 acb.certification_status,
411 acb.certification_type,
412 /** 10.20.2005 npanandi begin ***/
413 acb.certification_period_name,
414 acb.certification_period_set_name,
415 /** 10.20.2005 npanandi end ***/
416 orgtable.organization_id,
417 assoctable.pk3,
418 afkav.natural_account_id,
419 assoctable.risk_id,
420 nvl(risktable.material,'N'),
421 risktable.risk_impact,
422 risktable.likelihood,
423 opinionstable.last_updated_by, ---storing last_updated_by, since this maps to papf to give the evaluator name
424 opinionstable.authored_date,
425 assoctable.risk_rev_id,
426 opinionstable.audit_result_code,
427 /** 10.20.2005 npanandi begin ***/
428 agpv.period_year,
429 agpv.period_num,
430 agpv.quarter_num,
431 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
432 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
433 agpv.period_year,
434 to_number(to_char(agpv.end_date,'J')),
435 /** 10.20.2005 npanandi end ***/
436 sysdate,
437 G_USER_ID,
438 sysdate,
439 G_USER_ID,
440 G_LOGIN_ID
441 FROM amw_risk_associations assoctable,
445 amw_execution_scope execs,
442 amw_risks_all_vl risktable,
443 amw_audit_units_v orgtable,
444 amw_opinions_log_v opinionstable,
446 AMW_PROCESS_ORGANIZATION_VL procorg,
447 amw_audit_projects_v aapv,
448 AMW_CERTIFICATION_B acb,
449 AMW_CERTIFICATION_TL ACT,
450 amw_certification_vl acv2,
451 AMW_FIN_PROC_CERT_RELAN afpcr,
452 (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,
453 AMW_FIN_KEY_ACCOUNTS_VL AFKAV
454 /** 10.20.2005 npanandi begin ***/
455 ,amw_gl_periods_v agpv
456 /** 10.20.2005 npanandi end ***/
457 WHERE execs.entity_id = assoctable.pk1
458 AND execs.entity_type = assoctable.object_type
459 AND execs.organization_id = orgtable.organization_id
460 AND assoctable.object_type = 'BUSIPROC_CERTIFICATION'
461 AND execs.process_org_rev_id = procorg.process_org_rev_id --(+)
462 AND assoctable.pk4 = opinionstable.opinion_log_id ---(+)
463 AND assoctable.pk2 = orgtable.organization_id
464 AND NVL(assoctable.pk3, -1) = NVL(execs.process_id,-1)
465 AND assoctable.risk_rev_id = risktable.risk_rev_id
466 and opinionstable.pk2_value = aapv.audit_project_id ---(+)
467 and assoctable.pk1 = acb.certification_id
468 and acb.certification_id = act.certification_id
469 and act.language = userenv('LANG')
470 and opinionstable.audit_result_code <> 'EFFECTIVE'
471 and afpcr.PROC_CERT_ID = acb.CERTIFICATION_ID
472 and afpcr.END_DATE is null
473 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
474 and acv2.object_type='FIN_STMT'
475 and aaa.pk1(+) = execs.organization_id
476 and aaa.pk2(+) = execs.process_id
477 and aaa.natural_account_id = afkav.natural_account_id(+)
478 /*** 11.28.2005 npanandi: added below ***/
479 ---12.20.2005 npanandi: commenting this currApprovedFlag out for compatibility
480 ---between this fact table ETL and the orgcertsummary fact table
481 ---unmitigated risk count
482 ---and risktable.curr_approved_flag='Y'
483 and procorg.approval_date is not null
484 and procorg.approval_end_date is null
485 /*** 11.28.2005 npanandi: added below ***/
486 /** 10.20.2005 npanandi begin ***/
487 and acb.certification_period_name = agpv.period_name
488 and acb.certification_period_set_name = agpv.period_set_name
489 /** 10.20.2005 npanandi end ***/
490 order by acv2.certification_id,assoctable.pk1,orgtable.organization_id,assoctable.pk3,afkav.natural_account_id,assoctable.risk_id;
491
492
493 l_stmnt_id :=50;
494
495
496 l_stmnt_id :=60;
497 INSERT INTO rci_dr_inc( fact_name
498 ,last_run_date
499 ,created_by
500 ,creation_date
501 ,last_update_date
502 ,last_updated_by
503 ,last_update_login
504 ,program_id
505 ,program_login_id
506 ,program_application_id
507 ,request_id ) VALUES (
508 'RCI_ORG_CERT_RISKS_F'
509 ,l_run_date
510 ,l_user_id
511 ,sysdate
512 ,sysdate
513 ,l_user_id
514 ,l_login_id
515 ,l_program_id
516 ,l_program_login_id
517 ,l_program_application_id
518 ,l_request_id );
519
520 l_stmnt_id := 70;
521 commit;
522 retcode := C_OK;
523 EXCEPTION
524 WHEN OTHERS THEN
525 retcode := C_ERROR;
526 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
527 ROLLBACK;
528 RAISE;
529 END initial_load_obsolete;
530
531
532 -- run_incr_load_opm
533 -- Parameters:
534 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
535 -- errbuf - empty on successful completion, message on error or warning
536 --
537 -- History:
538 -- Date Author Action
539 -- 09/06/2005 Panandikar Nilesh G Defined Body.
540
541 PROCEDURE incr_load(
542 errbuf in out NOCOPY VARCHAR2
543 ,retcode in out NOCOPY NUMBER)
544 IS
545 l_stmnt_id NUMBER := 0;
546 l_run_date DATE;
547 l_last_run_date DATE;
548 l_proc_name VARCHAR2(30);
549 l_message VARCHAR2(30);
550 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
551 l_count NUMBER;
552
553 l_user_id NUMBER;
554 l_login_id NUMBER;
555 l_program_id NUMBER;
556 l_program_login_id NUMBER;
557 l_program_application_id NUMBER;
558 l_request_id NUMBER;
559 BEGIN
560 l_user_id := NVL(fnd_global.USER_ID, -1);
561 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
562 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
563 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
564 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
565 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
566
567 l_stmnt_id := 10;
571 IF l_last_run_date IS NULL THEN
568 l_proc_name := 'run_incr_load_drm';
569 l_last_run_date := get_last_run_date('RCI_ORG_CERT_RISKS_F');
570
572 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
573 RAISE INITIALIZATION_ERROR;
574 END IF;
575
576 l_stmnt_id := 20;
577 l_run_date := sysdate - 5/(24*60);
578 ---l_master_org := get_master_organization_id;
579
580 l_stmnt_id := 30;
581 /*** 01.16.2006 npanandi: added below as the RSG errors out otherwise **/
582 check_initial_load_setup(
583 x_global_start_date => g_global_start_date
584 ,x_rci_schema => g_rci_schema);
585 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_RISKS_F');
586
587 l_stmnt_id := 40;
588 INSERT INTO RCI_ORG_CERT_RISKS_F(
589 project_id
590 ,fin_certification_id
591 ,certification_id
592 ,certification_status
593 ,certification_type
594 /** 10.20.2005 npanandi begin ***/
595 ,certification_period_name
596 ,certification_period_set_name
597 /** 10.20.2005 npanandi end ***/
598 ,organization_id
599 ,process_id
600 ,natural_account_id
601 ,risk_id
602 ,material
603 ,risk_impact
604 ,likelihood
605 ,last_evaluator_id
606 ,last_evaluated_on
607 ,risk_rev_id
608 ,audit_result_code
609 /** 10.20.2005 npanandi begin ***/
610 ,period_year
611 ,period_num
612 ,quarter_num
613 ,ent_period_id
614 ,ent_qtr_id
615 ,ent_year_id
616 ,report_date_julian
617 /** 10.20.2005 npanandi end ***/
618 ,creation_date
619 ,created_by
620 ,last_update_date
621 ,last_updated_by
622 ,last_update_login)
623 SELECT DISTINCT op.pk2_value, /*project_id*/
627 acv.certification_type, /*certification_type*/
624 risks.pk1 as certification_id, /*fin_certification_id*/
625 -10000, /*certification_id, cannot insert NULL*/
626 acv.certification_status, /*certification_status*/
628 acv.certification_period_name,
629 acv.certification_period_set_name,
630 proc.organization_id,
631 proc.process_id,
632 AFKAV.natural_account_id,
633 all_risks.risk_id,
634 nvl(all_risks.material,'N'),
635 all_risks.risk_impact,
636 all_risks.likelihood,
637 op.last_updated_by, /*last_evaluator_id*/
638 op.authored_date, /*last_evaluated_on*/
639 all_risks.risk_rev_id,
640 op.audit_result_code,
641 agpv.period_year,
642 agpv.period_num,
643 agpv.quarter_num,
644 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
645 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
646 agpv.period_year,
647 to_number(to_char(agpv.end_date,'J')),
648 sysdate,
649 G_USER_ID,
650 sysdate,
651 G_USER_ID,
652 G_LOGIN_ID
653 FROM AMW_RISK_ASSOCIATIONS risks,
654 AMW_RISKS_ALL_VL all_risks,
655 AMW_PROCESS_ORGANIZATION_VL proc,
656 HR_ALL_ORGANIZATION_UNITS o,
657 HR_ALL_ORGANIZATION_UNITS_TL otl,
658 AMW_OPINIONS_LOG_V op,
659 amw_certification_vl acv,
660 (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,
661 AMW_FIN_KEY_ACCOUNTS_VL AFKAV,
662 amw_gl_periods_v agpv
663 WHERE risks.object_type = 'PROCESS_FINCERT'
664 and all_risks.risk_rev_id = risks.risk_rev_id
665 and o.organization_id = risks.pk2
666 and o.organization_id = otl.organization_id
667 and otl.language = userenv('LANG')
668 and proc.organization_id = risks.pk2
669 and proc.process_id = risks.pk3
670 and risks.approval_date is not null
671 and proc.approval_status = 'A'
672 and proc.approval_date = risks.approval_date
673 and op.opinion_log_id(+) = risks.pk4
674 and risks.pk1 = acv.certification_id
675 and aaa.pk1(+) = proc.organization_id
676 and aaa.pk2(+) = proc.process_id
677 and aaa.natural_account_id = afkav.natural_account_id(+)
678 ----and op.audit_result_code <> 'EFFECTIVE'
679 and acv.certification_period_name = agpv.period_name
680 and acv.certification_period_set_name = agpv.period_set_name;
681
682 l_stmnt_id :=50;
683
684 UPDATE rci_dr_inc
685 SET last_run_date = l_run_date
686 ,last_update_date = sysdate
687 ,last_updated_by = l_user_id
688 ,last_update_login = l_login_id
689 ,program_id = l_program_id
690 ,program_login_id = l_program_login_id
691 ,program_application_id = l_program_application_id
692 ,request_id = l_request_id
693 WHERE fact_name = 'RCI_ORG_CERT_RISKS_F' ;
694
695
696 commit;
697 retcode := C_OK;
698 EXCEPTION
699 WHEN OTHERS THEN
700 retcode := C_ERROR;
701 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
702 ROLLBACK;
703 RAISE;
704 END incr_load;
705
706 END RCI_UNMTG_RISKS_ETL_PKG;