[Home] [Help]
PACKAGE BODY: APPS.RCI_CTRL_DETAIL_ETL_PKG
Source
1 PACKAGE BODY RCI_CTRL_DETAIL_ETL_PKG AS
2 --$Header: rcicdtetlb.pls 120.7 2007/01/20 00:27:54 sbag 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 l_stmnt_id := 0;
177 l_proc_name := 'intitial_load';
178 check_initial_load_setup(
179 x_global_start_date => g_global_start_date
180 ,x_rci_schema => g_rci_schema);
181
182 l_stmnt_id := 10;
183 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_CTRLS_F';
184
185
186 l_stmnt_id := 20;
187 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_CTRLS_F');
188
189 l_stmnt_id := 30;
190 l_run_date := sysdate - 5/(24*60);
191
192 INSERT INTO RCI_ORG_CERT_CTRLS_F(
193 fin_certification_id
194 ,certification_id
195 ,certification_status
196 ,certification_type
197 ,certification_period_name
198 ,certification_period_set_name
199 ,organization_id
200 ,process_id
201 ,natural_account_id
202 ,control_id
203 ,control_rev_id
204 ,control_type
205 ,control_location
206 ,automation_type
207 ,control_frequency
208 ,key_control
209 ,disclosure_control
210 ,latest_rev_num
211 ,audit_result_code
212 ,last_evaluated_by_id
213 ,last_evaluated_on
214 ,op_eff_id
215 ,des_eff_id
216 ,period_year
217 ,period_num
218 ,quarter_num
219 ,ent_period_id
220 ,ent_qtr_id
221 ,ent_year_id
222 ,report_date_julian
223 ,creation_date
224 ,created_by
225 ,last_update_date
226 ,last_updated_by
227 ,last_update_login)
228 SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
229 ,-10000 /*certification_id*/
230 ,acv.certification_status
231 ,acv.certification_type
232 ,acv.certification_period_name
233 ,acv.certification_period_set_name
234 ,o.organization_id
235 ,ctrls.pk3 /*process_id*/
236 ,afcs.natural_account_id /*natural_account_id*/
237 ,all_ctrls.control_id
238 ,all_ctrls.control_rev_id
239 ,all_ctrls.control_type
240 ,all_ctrls.control_location
241 ,all_ctrls.automation_type
242 ,all_ctrls.control_frequency
243 ,upper(nvl(all_ctrls.key_mitigating,'N'))
244 ,upper(nvl(all_ctrls.disclosure_control,'N'))
245 ,(select max(rev_num) from amw_controls_b where control_id=all_ctrls.control_id) /*latest_rev_num*/
246 ,op.audit_result_code
247 ,op.authored_by /*last_evaluated_by_id*/
248 ,op.authored_date /*last_evaluated_on*/
249 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
250 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
251 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
252 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
253 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
254 and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
255 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
256 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
257 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
258 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
259 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
260 and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
261 ,agpv.period_year
262 ,agpv.period_num
263 ,agpv.quarter_num
264 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num))
265 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num))
266 ,agpv.period_year
267 ,to_number(to_char(agpv.end_date,'J'))
268 ,sysdate
269 ,G_USER_ID
270 ,sysdate
271 ,G_USER_ID
272 ,G_LOGIN_ID
273 FROM AMW_CONTROL_ASSOCIATIONS ctrls,
274 AMW_CONTROLS_ALL_VL all_ctrls,
275 HR_ALL_ORGANIZATION_UNITS o,
276 HR_ALL_ORGANIZATION_UNITS_TL otl,
277 AMW_OPINIONS_LOG_V op,
278 amw_certification_vl acv,
279 amw_fin_cert_scope afcs,
280 amw_gl_periods_v agpv
281 WHERE ctrls.object_type = 'RISK_FINCERT'
282 and ctrls.control_rev_id = all_ctrls.control_rev_id
283 and all_ctrls.APPROVAL_STATUS = 'A'
284 and o.organization_id = ctrls.pk2
285 and o.organization_id = otl.organization_id
286 and otl.language = userenv('LANG')
287 and op.opinion_log_id(+) = ctrls.pk5
288 and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
289 and acv.certification_id = ctrls.pk1
290 and afcs.fin_certification_id = ctrls.pk1
291 and afcs.organization_id = o.organization_id
292 and afcs.process_id = ctrls.pk3
293 and acv.certification_period_name = agpv.period_name
294 and acv.certification_period_set_name = agpv.period_set_name;
295
296
297 l_stmnt_id :=50;
298
299 INSERT INTO rci_dr_inc( fact_name
300 ,last_run_date
301 ,created_by
302 ,creation_date
303 ,last_update_date
304 ,last_updated_by
305 ,last_update_login
306 ,program_id
307 ,program_login_id
308 ,program_application_id
309 ,request_id ) VALUES (
310 'RCI_ORG_CERT_CTRLS_F'
311 ,l_run_date
312 ,l_user_id
313 ,sysdate
314 ,sysdate
315 ,l_user_id
316 ,l_login_id
317 ,l_program_id
318 ,l_program_login_id
319 ,l_program_application_id
320 ,l_request_id );
321
322 l_stmnt_id := 60;
323 commit;
324 retcode := C_OK;
325 EXCEPTION
326 WHEN OTHERS THEN
327 retcode := C_ERROR;
328 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
329 ROLLBACK;
330 RAISE;
331 END initial_load;
332
333 /*****
334 PROCEDURE initial_load(
335 errbuf IN OUT NOCOPY VARCHAR2
336 ,retcode IN OUT NOCOPY NUMBER)
337 IS
338 l_proc_w_ineff_ctrls number;
339 l_ineffective_ctrls number;
340 l_unmitigated_risks number;
341 l_open_issues number;
342
343 l_stmnt_id NUMBER := 0;
344 l_run_date DATE;
345 l_proc_name VARCHAR2(30);
346 l_status VARCHAR2(30) ;
347 l_industry VARCHAR2(30) ;
348 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
349
350 l_user_id NUMBER ;
351 l_login_id NUMBER ;
352 l_program_id NUMBER ;
353 l_program_login_id NUMBER ;
354 l_program_application_id NUMBER ;
355 l_request_id NUMBER ;
356
357 BEGIN
358 l_user_id := NVL(fnd_global.USER_ID, -1);
359 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
360 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
361 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
362 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
363 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
364
365 l_stmnt_id := 0;
366 l_proc_name := 'intitial_load';
367 check_initial_load_setup(
368 x_global_start_date => g_global_start_date
369 ,x_rci_schema => g_rci_schema);
370
374
371 l_stmnt_id := 10;
372 DELETE FROM rci_dr_inc where fact_name = 'RCI_ORG_CERT_CTRLS_F';
373
375 l_stmnt_id := 20;
376 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_CTRLS_F');
377
378 l_stmnt_id := 30;
379 l_run_date := sysdate - 5/(24*60);
380
381 INSERT INTO RCI_ORG_CERT_CTRLS_F(
382 fin_certification_id
383 ,certification_id
384 ,certification_status
385 ,certification_type
386 ,certification_period_name
387 ,certification_period_set_name
388 ,organization_id
389 ,process_id
390 ,natural_account_id
391 ,control_id
392 ,control_rev_id
393 ,control_type
394 ,control_location
395 ,automation_type
396 ,control_frequency
397 ,key_control
398 ,disclosure_control
399 ,latest_rev_num
400 ,audit_result_code
401 ,last_evaluated_by_id
402 ,last_evaluated_on
403 ,op_eff_id
404 ,des_eff_id
405 ,period_year
406 ,period_num
407 ,quarter_num
408 ,ent_period_id
409 ,ent_qtr_id
410 ,ent_year_id
411 ,report_date_julian
412 ,creation_date
413 ,created_by
414 ,last_update_date
415 ,last_updated_by
416 ,last_update_login)
417 SELECT DISTINCT acv2.certification_id,
418 ctrlassoc.pk1,
419 acv.certification_status,
420 acv.certification_type,
421 acv.certification_period_name,
422 acv.certification_period_set_name,
423 orgtable.organization_id,
424 procorg.process_id,
425 afkav.natural_account_id,
426 controltable.control_id,
427 ctrlassoc.control_rev_id,
428 controltable.control_type,
429 controltable.control_location,
430 controltable.automation_type,
431 controltable.control_frequency,
432 nvl(controltable.key_mitigating,'N'),
433 nvl(controltable.disclosure_control,'N'),
434 (select max(rev_num) from amw_controls_b where control_id=controltable.control_id),
435 opinionstable.audit_result_code,
436 opinionstable.last_updated_by,
437 opinionstable.authored_date,
438 (SELECT valuestable.opinion_value_id
439 FROM amw_opinion_log_details details,
440 amw_opinion_values_tl valuestable,
441 amw_opinion_componts_b compb
442 WHERE opinionstable.opinion_log_id = details.opinion_log_id
443 AND details.opinion_component_id = compb.opinion_component_id
444 AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
445 AND compb.opinion_component_code = 'OPERATING'
446 AND valuestable.language = userenv('LANG')
447 AND details.opinion_value_id = valuestable.opinion_value_id ),
448 (SELECT valuestable.opinion_value_id
449 FROM amw_opinion_log_details details,
450 amw_opinion_values_tl valuestable,
451 amw_opinion_componts_b compb
452 WHERE opinionstable.opinion_log_id = details.opinion_log_id
453 AND details.opinion_component_id = compb.opinion_component_id
454 AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
455 AND compb.opinion_component_code = 'DESIGN'
456 AND valuestable.language = userenv('LANG')
457 AND details.opinion_value_id = valuestable.opinion_value_id ),
458 agpv.period_year,
459 agpv.period_num,
460 agpv.quarter_num,
461 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
462 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
463 agpv.period_year,
464 to_number(to_char(agpv.end_date,'J')),
465 sysdate,
466 G_USER_ID,
467 sysdate,
468 G_USER_ID,
469 G_LOGIN_ID
470 FROM amw_control_associations ctrlassoc,
471 amw_controls_all_vl controltable,
472 amw_audit_units_v orgtable,
473 amw_opinions_log_v opinionstable,
474 amw_execution_scope execs,
475 AMW_PROCESS_ORGANIZATION_VL procorg,
476 amw_certification_vl acv,
477 amw_certification_vl acv2,
478 AMW_FIN_PROC_CERT_RELAN afpcr,
479 (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,
480 AMW_FIN_KEY_ACCOUNTS_VL AFKAV
481 ,amw_gl_periods_v agpv
482 WHERE execs.entity_id = ctrlassoc.pk1
483 AND execs.entity_type = ctrlassoc.object_type
484 AND execs.organization_id = orgtable.organization_id
485 AND procorg.process_org_rev_id(+) = execs.process_org_rev_id
486 AND ctrlassoc.control_rev_id = controltable.control_rev_id
487 AND ctrlassoc.pk5 = opinionstable.opinion_log_id (+)
488 AND ctrlassoc.object_type = 'BUSIPROC_CERTIFICATION'
489 AND ctrlassoc.pk2 = orgtable.organization_id
490 AND NVL(ctrlassoc.pk3,-1) = NVL(execs.process_id,-1)
491 and acv.certification_id = execs.entity_id
492 and opinionstable.audit_result_code <> 'EFFECTIVE'
493 and opinionstable.OBJECT_NAME = 'AMW_ORG_CONTROL'
494 and opinionstable.OPINION_TYPE_CODE = 'EVALUATION'
498 and acv2.object_type='FIN_STMT'
495 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
496 and afpcr.END_DATE is null
497 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
499 and aaa.pk1(+) = execs.organization_id
500 and aaa.pk2(+) = execs.process_id
501 and aaa.natural_account_id = afkav.natural_account_id(+)
502 and acv.certification_period_name = agpv.period_name
503 and acv.certification_period_set_name = agpv.period_set_name
504 order by acv2.certification_id,ctrlassoc.pk1,orgtable.organization_id,procorg.process_id,controltable.control_id;
505
506 l_stmnt_id :=50;
507
508 INSERT INTO rci_dr_inc( fact_name
509 ,last_run_date
510 ,created_by
511 ,creation_date
512 ,last_update_date
513 ,last_updated_by
514 ,last_update_login
515 ,program_id
516 ,program_login_id
517 ,program_application_id
518 ,request_id ) VALUES (
519 'RCI_ORG_CERT_CTRLS_F'
520 ,l_run_date
521 ,l_user_id
522 ,sysdate
523 ,sysdate
524 ,l_user_id
525 ,l_login_id
526 ,l_program_id
527 ,l_program_login_id
528 ,l_program_application_id
529 ,l_request_id );
530
531 l_stmnt_id := 60;
532 commit;
533 retcode := C_OK;
534 EXCEPTION
535 WHEN OTHERS THEN
536 retcode := C_ERROR;
537 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
538 ROLLBACK;
539 RAISE;
540 END initial_load;
541 ******/
542
543
544 -- run_incr_load_opm
545 -- Parameters:
546 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
547 -- errbuf - empty on successful completion, message on error or warning
548 --
549 -- History:
550 -- Date Author Action
551 -- 09/06/2005 Panandikar Nilesh G Defined Body.
552
553 PROCEDURE incr_load(
554 errbuf in out NOCOPY VARCHAR2
555 ,retcode in out NOCOPY NUMBER)
556 IS
557 l_stmnt_id NUMBER := 0;
558 l_run_date DATE;
559 l_last_run_date DATE;
560 l_proc_name VARCHAR2(30);
561 l_message VARCHAR2(30);
562 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
563 l_count NUMBER;
564
565 l_user_id NUMBER;
566 l_login_id NUMBER;
567 l_program_id NUMBER;
568 l_program_login_id NUMBER;
569 l_program_application_id NUMBER;
570 l_request_id NUMBER;
571 BEGIN
572 l_user_id := NVL(fnd_global.USER_ID, -1);
573 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
574 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
575 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
576 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
577 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
578
579 l_stmnt_id := 10;
580 l_proc_name := 'run_incr_load_drm';
581 l_last_run_date := get_last_run_date('RCI_ORG_CERT_CTRLS_F');
582
583 IF l_last_run_date IS NULL THEN
584 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
585 RAISE INITIALIZATION_ERROR;
586 END IF;
587
588 l_stmnt_id := 20;
589 l_run_date := sysdate - 5/(24*60);
590 ---l_master_org := get_master_organization_id;
591
592 l_stmnt_id := 30;
593 /** 01.16.06 npanandi: added below procedure cal as RSG errors otherwise **/
594 check_initial_load_setup(
595 x_global_start_date => g_global_start_date
596 ,x_rci_schema => g_rci_schema);
597 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_CTRLS_F');
598
599 l_stmnt_id := 40;
600
601 INSERT INTO RCI_ORG_CERT_CTRLS_F(
602 fin_certification_id
603 ,certification_id
604 ,certification_status
605 ,certification_type
606 ,certification_period_name
607 ,certification_period_set_name
608 ,organization_id
609 ,process_id
610 ,natural_account_id
611 ,control_id
612 ,control_rev_id
613 ,control_type
614 ,control_location
615 ,automation_type
616 ,control_frequency
617 ,key_control
618 ,disclosure_control
619 ,latest_rev_num
620 ,audit_result_code
621 ,last_evaluated_by_id
622 ,last_evaluated_on
623 ,op_eff_id
624 ,des_eff_id
625 ,period_year
626 ,period_num
627 ,quarter_num
628 ,ent_period_id
629 ,ent_qtr_id
630 ,ent_year_id
631 ,report_date_julian
632 ,creation_date
633 ,created_by
634 ,last_update_date
635 ,last_updated_by
636 ,last_update_login)
637 SELECT DISTINCT ctrls.pk1 as certification_id /*fin_certification_id*/
638 ,-10000 /*certification_id*/
639 ,acv.certification_status
640 ,acv.certification_type
641 ,acv.certification_period_name
642 ,acv.certification_period_set_name
643 ,o.organization_id
644 ,ctrls.pk3 /*process_id*/
645 ,afcs.natural_account_id /*natural_account_id*/
649 ,all_ctrls.control_location
646 ,all_ctrls.control_id
647 ,all_ctrls.control_rev_id
648 ,all_ctrls.control_type
650 ,all_ctrls.automation_type
651 ,all_ctrls.control_frequency
652 ,upper(nvl(all_ctrls.key_mitigating,'N'))
653 ,upper(nvl(all_ctrls.disclosure_control,'N'))
654 ,(select max(rev_num) from amw_controls_b where control_id=all_ctrls.control_id) /*latest_rev_num*/
655 ,op.audit_result_code
656 ,op.authored_by /*last_evaluated_by_id*/
657 ,op.authored_date /*last_evaluated_on*/
658 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
659 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
660 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
661 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
662 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
663 and OPINION_COMPONENT_CODE = 'OPERATING')) op_eff_id
664 ,(select /*aov.OPINION_VALUE_NAME*/ aov.opinion_value_id from AMW_OPINION_LOG_DETAILS aod, AMW_OPINION_VALUES_TL aov
665 WHERE aov.language=userenv('LANG') and op.OPINION_LOG_ID = aod.OPINION_LOG_ID
666 and aod.OPINION_VALUE_ID = aov.OPINION_VALUE_ID
667 and aod.OPINION_COMPONENT_ID = (select OPINION_COMPONENT_ID from AMW_OPINION_COMPONTS_B
668 where OBJECT_OPINION_TYPE_ID = op.OBJECT_OPINION_TYPE_ID
669 and OPINION_COMPONENT_CODE = 'DESIGN')) des_eff_id
670 ,agpv.period_year
671 ,agpv.period_num
672 ,agpv.quarter_num
673 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num))
674 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num))
675 ,agpv.period_year
676 ,to_number(to_char(agpv.end_date,'J'))
677 ,sysdate
678 ,G_USER_ID
679 ,sysdate
680 ,G_USER_ID
681 ,G_LOGIN_ID
682 FROM AMW_CONTROL_ASSOCIATIONS ctrls,
683 AMW_CONTROLS_ALL_VL all_ctrls,
684 HR_ALL_ORGANIZATION_UNITS o,
685 HR_ALL_ORGANIZATION_UNITS_TL otl,
686 AMW_OPINIONS_LOG_V op,
687 amw_certification_vl acv,
688 amw_fin_cert_scope afcs,
689 amw_gl_periods_v agpv
690 WHERE ctrls.object_type = 'RISK_FINCERT'
691 and ctrls.control_rev_id = all_ctrls.control_rev_id
692 and all_ctrls.APPROVAL_STATUS = 'A'
693 and o.organization_id = ctrls.pk2
694 and o.organization_id = otl.organization_id
695 and otl.language = userenv('LANG')
696 and op.opinion_log_id(+) = ctrls.pk5
697 and op.AUDIT_RESULT_CODE <> 'EFFECTIVE'
698 and acv.certification_id = ctrls.pk1
699 and afcs.fin_certification_id = ctrls.pk1
700 and afcs.organization_id = o.organization_id
701 and afcs.process_id = ctrls.pk3
702 and acv.certification_period_name = agpv.period_name
703 and acv.certification_period_set_name = agpv.period_set_name;
704
705
706 l_stmnt_id :=50;
707 UPDATE rci_dr_inc
708 SET last_run_date = l_run_date
709 ,last_update_date = sysdate
710 ,last_updated_by = l_user_id
711 ,last_update_login = l_login_id
712 ,program_id = l_program_id
713 ,program_login_id = l_program_login_id
714 ,program_application_id = l_program_application_id
715 ,request_id = l_request_id
716 WHERE fact_name = 'RCI_ORG_CERT_CTRLS_F' ;
717
718
719 commit;
720 retcode := C_OK;
721 EXCEPTION
722 WHEN OTHERS THEN
723 retcode := C_ERROR;
724 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
725 ROLLBACK;
726 RAISE;
727 END incr_load;
728
729 /*****
730 PROCEDURE incr_load(
731 errbuf in out NOCOPY VARCHAR2
732 ,retcode in out NOCOPY NUMBER)
733 IS
734 l_stmnt_id NUMBER := 0;
735 l_run_date DATE;
736 l_last_run_date DATE;
737 l_proc_name VARCHAR2(30);
738 l_message VARCHAR2(30);
739 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
740 l_count NUMBER;
741
742 l_user_id NUMBER;
743 l_login_id NUMBER;
744 l_program_id NUMBER;
745 l_program_login_id NUMBER;
746 l_program_application_id NUMBER;
747 l_request_id NUMBER;
748 BEGIN
749 l_user_id := NVL(fnd_global.USER_ID, -1);
750 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
751 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
752 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
753 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
754 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
755
756 l_stmnt_id := 10;
757 l_proc_name := 'run_incr_load_drm';
758 l_last_run_date := get_last_run_date('RCI_ORG_CERT_CTRLS_F');
759
760 IF l_last_run_date IS NULL THEN
761 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
762 RAISE INITIALIZATION_ERROR;
763 END IF;
764
765 l_stmnt_id := 20;
766 l_run_date := sysdate - 5/(24*60);
770 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_ORG_CERT_CTRLS_F');
767 ---l_master_org := get_master_organization_id;
768
769 l_stmnt_id := 30;
771
772 l_stmnt_id := 40;
773
774 INSERT INTO RCI_ORG_CERT_CTRLS_F(
775 fin_certification_id
776 ,certification_id
777 ,certification_status
778 ,certification_type
779 ,certification_period_name
780 ,certification_period_set_name
781 ,organization_id
782 ,process_id
783 ,natural_account_id
784 ,control_id
785 ,control_rev_id
786 ,control_type
787 ,control_location
788 ,automation_type
789 ,control_frequency
790 ,key_control
791 ,disclosure_control
792 ,latest_rev_num
793 ,audit_result_code
794 ,last_evaluated_by_id
795 ,last_evaluated_on
796 ,op_eff_id
797 ,des_eff_id
798 ,period_year
799 ,period_num
800 ,quarter_num
801 ,ent_period_id
802 ,ent_qtr_id
803 ,ent_year_id
804 ,report_date_julian
805 ,creation_date
806 ,created_by
807 ,last_update_date
808 ,last_updated_by
809 ,last_update_login)
810 SELECT DISTINCT acv2.certification_id,
811 ctrlassoc.pk1,
812 acv.certification_status,
813 acv.certification_type,
814 acv.certification_period_name,
815 acv.certification_period_set_name,
816 orgtable.organization_id,
817 procorg.process_id,
818 afkav.natural_account_id,
819 controltable.control_id,
820 ctrlassoc.control_rev_id,
821 controltable.control_type,
822 controltable.control_location,
823 controltable.automation_type,
824 controltable.control_frequency,
825 nvl(controltable.key_mitigating,'N'),
826 nvl(controltable.disclosure_control,'N'),
827 (select max(rev_num) from amw_controls_b where control_id=controltable.control_id),
828 opinionstable.audit_result_code,
829 opinionstable.last_updated_by,
830 opinionstable.authored_date,
831 (SELECT valuestable.opinion_value_id
832 FROM amw_opinion_log_details details,
833 amw_opinion_values_tl valuestable,
834 amw_opinion_componts_b compb
835 WHERE opinionstable.opinion_log_id = details.opinion_log_id
836 AND details.opinion_component_id = compb.opinion_component_id
837 AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
838 AND compb.opinion_component_code = 'OPERATING'
839 AND valuestable.language = userenv('LANG')
840 AND details.opinion_value_id = valuestable.opinion_value_id ),
841 (SELECT valuestable.opinion_value_id
842 FROM amw_opinion_log_details details,
843 amw_opinion_values_tl valuestable,
844 amw_opinion_componts_b compb
845 WHERE opinionstable.opinion_log_id = details.opinion_log_id
846 AND details.opinion_component_id = compb.opinion_component_id
847 AND compb.object_opinion_type_id = opinionstable.object_opinion_type_id
848 AND compb.opinion_component_code = 'DESIGN'
849 AND valuestable.language = userenv('LANG')
850 AND details.opinion_value_id = valuestable.opinion_value_id ),
851 agpv.period_year,
852 agpv.period_num,
853 agpv.quarter_num,
854 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
855 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
856 agpv.period_year,
857 to_number(to_char(agpv.end_date,'J')),
858 sysdate,
859 G_USER_ID,
860 sysdate,
861 G_USER_ID,
862 G_LOGIN_ID
863 FROM amw_control_associations ctrlassoc,
864 amw_controls_all_vl controltable,
865 amw_audit_units_v orgtable,
866 amw_opinions_log_v opinionstable,
867 amw_execution_scope execs,
868 AMW_PROCESS_ORGANIZATION_VL procorg,
869 amw_certification_vl acv,
870 amw_certification_vl acv2,
871 AMW_FIN_PROC_CERT_RELAN afpcr,
872 (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,
873 AMW_FIN_KEY_ACCOUNTS_VL AFKAV
874 ,amw_gl_periods_v agpv
875 WHERE execs.entity_id = ctrlassoc.pk1
876 AND execs.entity_type = ctrlassoc.object_type
877 AND execs.organization_id = orgtable.organization_id
878 AND procorg.process_org_rev_id(+) = execs.process_org_rev_id
879 AND ctrlassoc.control_rev_id = controltable.control_rev_id
880 AND ctrlassoc.pk5 = opinionstable.opinion_log_id (+)
881 AND ctrlassoc.object_type = 'BUSIPROC_CERTIFICATION'
882 AND ctrlassoc.pk2 = orgtable.organization_id
883 AND NVL(ctrlassoc.pk3,-1) = NVL(execs.process_id,-1)
884 and acv.certification_id = execs.entity_id
885 and opinionstable.audit_result_code <> 'EFFECTIVE'
886 and opinionstable.OBJECT_NAME = 'AMW_ORG_CONTROL'
887 and opinionstable.OPINION_TYPE_CODE = 'EVALUATION'
888 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
892 and aaa.pk1(+) = execs.organization_id
889 and afpcr.END_DATE is null
890 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
891 and acv2.object_type='FIN_STMT'
893 and aaa.pk2(+) = execs.process_id
894 and aaa.natural_account_id = afkav.natural_account_id(+)
895 and acv.certification_period_name = agpv.period_name
896 and acv.certification_period_set_name = agpv.period_set_name
897 order by acv2.certification_id,ctrlassoc.pk1,orgtable.organization_id,procorg.process_id,controltable.control_id;
898
899
900 l_stmnt_id :=50;
901 UPDATE rci_dr_inc
902 SET last_run_date = l_run_date
903 ,last_update_date = sysdate
904 ,last_updated_by = l_user_id
905 ,last_update_login = l_login_id
909 ,request_id = l_request_id
906 ,program_id = l_program_id
907 ,program_login_id = l_program_login_id
908 ,program_application_id = l_program_application_id
910 WHERE fact_name = 'RCI_ORG_CERT_CTRLS_F' ;
911
912
913 commit;
914 retcode := C_OK;
915 EXCEPTION
916 WHEN OTHERS THEN
917 retcode := C_ERROR;
918 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
919 ROLLBACK;
920 RAISE;
921 END incr_load;
922 ***/
923
924 END RCI_CTRL_DETAIL_ETL_PKG;