[Home] [Help]
PACKAGE BODY: APPS.RCI_PROC_DETAIL_ETL_PKG
Source
1 PACKAGE BODY RCI_PROC_DETAIL_ETL_PKG AS
2 --$Header: rciprdtetlb.pls 120.7.12000000.1 2007/01/16 20:46:40 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
210
211 PROCEDURE initial_load(
212 errbuf IN OUT NOCOPY VARCHAR2
213 ,retcode IN OUT NOCOPY NUMBER)
214 IS
215
216 l_proc_w_ineff_ctrls number := 0;
217 l_ineffective_ctrls number := 0;
218 l_unmitigated_risks number := 0;
219 l_open_issues number := 0;
220
221 l_stmnt_id NUMBER := 0;
222 l_run_date DATE;
223 l_proc_name VARCHAR2(30);
224 l_status VARCHAR2(30) ;
225 l_industry VARCHAR2(30) ;
226 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
227
228 l_user_id NUMBER ;
229 l_login_id NUMBER ;
230 l_program_id NUMBER ;
231 l_program_login_id NUMBER ;
232 l_program_application_id NUMBER ;
233 l_request_id NUMBER ;
234
235 BEGIN
236 l_user_id := NVL(fnd_global.USER_ID, -1);
237 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
238 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
239 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
240 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
241 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
242
243 ----dbms_output.put_line( '1 **************' );
244
245 l_stmnt_id := 0;
246 l_proc_name := 'intitial_load';
247 check_initial_load_setup(
248 x_global_start_date => g_global_start_date
249 ,x_rci_schema => g_rci_schema);
250
251 l_stmnt_id := 10;
252 DELETE FROM rci_dr_inc where fact_name = 'RCI_PROCESS_DETAIL_F';
253
254 ----dbms_output.put_line( '2 **************' );
255
256 l_stmnt_id := 20;
257 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_PROCESS_DETAIL_F');
258
259 ----dbms_output.put_line( '3 **************' );
260 l_stmnt_id := 30;
261 l_run_date := sysdate - 5/(24*60);
262
263 INSERT INTO RCI_PROCESS_DETAIL_F(
264 project_id
265 ,process_org_rev_id
266 ,fin_certification_id
267 ,certification_id
268 ,certification_status
269 ,certification_type
270 ,certification_period_name
271 ,certification_period_set_name
272 ,organization_id
273 ,process_id
274 ,natural_account_id
275 ,significant_process_flag
276 ,standard_process_flag
277 ,process_category
278 ,certification_result_code
279 ,certified_by_id
280 ,certified_on
281 ,evaluation_result_code
282 ,evaluated_by_id
283 ,last_evaluated_on
284 /** 10.19.2005 npanandi begin ***/
285 ,period_year
286 ,period_num
287 ,quarter_num
288 ,ent_period_id
289 ,ent_qtr_id
290 ,ent_year_id
291 ,report_date_julian
292 /** 10.19.2005 npanandi end ***/
293 ,creation_date
294 ,created_by
295 ,last_update_date
296 ,last_updated_by
297 ,last_update_login)
298 select distinct evalopn.pk2_value, /*project_id*/
299 proc.process_org_rev_id,
300 finprocsum.FIN_CERTIFICATION_ID,
301 -10000, /*certification_id, cannot insert NULL here*/
302 acb.certification_status,
303 acb.certification_type,
304 acb.certification_period_name,
305 acb.certification_period_set_name,
306 o.organization_id,
307 proc.process_id,
308 aaa.natural_account_id,
309 nvl(proc.significant_process_flag,'N'),
310 nvl(proc.standard_process_flag,'N'),
311 proc.process_category,
312 certopn.audit_result_code, /*certification_result_code*/
313 certopn.authored_by, /*certified_by_id*/
314 certopn.authored_date, /*certified_on*/
315 evalopn.audit_result_code, /*evaluation_result_code*/
316 evalopn.authored_by, /*evaluated_by_id*/
317 evalopn.authored_date, /*last_evaluated_on*/
318 agpv.period_year,
319 agpv.period_num,
320 agpv.quarter_num,
321 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
322 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
323 agpv.period_year,
324 to_number(to_char(agpv.end_date,'J')),
325 sysdate,
326 G_USER_ID,
327 sysdate,
328 G_USER_ID,
329 G_LOGIN_ID
330 from AMW_FIN_CERT_SCOPE finscope,
331 AMW_FIN_PROC_CERT_RELAN REL,
332 AMW_FIN_PROCESS_EVAL_SUM finprocsum,
333 HR_ALL_ORGANIZATION_UNITS o,
334 HR_ALL_ORGANIZATION_UNITS_TL otl,
335 AMW_PROCESS_ORGANIZATION_VL proc,
336 /*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V certopn,
337 /*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V evalopn,
338 amw_certification_b acb,
339 (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,
340 amw_gl_periods_v agpv
341 where rel.FIN_STMT_CERT_ID = finprocsum.FIN_CERTIFICATION_ID
342 and rel.end_date is null
343 and finprocsum.FIN_CERTIFICATION_ID = finscope.fin_certification_id
344 and finprocsum.ORGANIZATION_ID = finscope.ORGANIZATION_ID
345 and finprocsum.PROCESS_ID = finscope.PROCESS_ID
346 and finprocsum.PROCESS_ORG_REV_ID = proc.PROCESS_ORG_REV_ID
347 and o.organization_id = finscope.organization_id
348 and o.organization_id = otl.organization_id
349 and otl.language = userenv('LANG')
350 and finprocsum.EVAL_OPINION_log_ID = evalopn.opinion_log_id(+)
351 and finprocsum.cert_opinion_log_id = certopn.opinion_log_id(+)
352 /***and certopn.opinion_type_code = 'CERTIFICATION'
353 and certopn.object_name = 'AMW_ORG_PROCESS'
354 and certopn.pk1_value = finscope.process_id
355 and certopn.pk2_value = rel.PROC_CERT_ID
356 and certopn.pk3_value = finscope.organization_id
357 AND certopn.authored_date = (select max(aov2.authored_date) from AMW_OPINIONS aov2
358 where aov2.object_opinion_type_id = certopn.object_opinion_type_id
359 and aov2.pk3_value = certopn.pk3_value
360 AND aov2.pk2_value in (select proc_cert_Id
361 from AMW_FIN_PROC_CERT_RELAN
362 where fin_stmt_cert_id = finprocsum.FIN_CERTIFICATION_ID
366 and finprocsum.FIN_CERTIFICATION_ID = acb.certification_id
363 and end_date is null)
364 and aov2.pk1_value = certopn.pk1_value)
365 ***/
367 and aaa.pk1(+) = finprocsum.organization_id
368 and aaa.pk2(+) = finprocsum.process_id
369 and acb.certification_period_name = agpv.period_name
370 and acb.certification_period_set_name = agpv.period_set_name;
371
372 l_stmnt_id :=40;
373
374 INSERT INTO rci_dr_inc( fact_name
375 ,last_run_date
376 ,created_by
377 ,creation_date
378 ,last_update_date
379 ,last_updated_by
380 ,last_update_login
381 ,program_id
382 ,program_login_id
383 ,program_application_id
384 ,request_id ) VALUES (
385 'RCI_PROCESS_DETAIL_F'
386 ,l_run_date
387 ,l_user_id
388 ,sysdate
389 ,sysdate
390 ,l_user_id
391 ,l_login_id
392 ,l_program_id
393 ,l_program_login_id
394 ,l_program_application_id
395 ,l_request_id );
396
397 l_stmnt_id := 50;
398 commit;
399 retcode := C_OK;
400 EXCEPTION
401 WHEN OTHERS THEN
402 retcode := C_ERROR;
403 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
404 ROLLBACK;
405 RAISE;
406 END initial_load;
407
408 PROCEDURE initial_load_obsolete(
409 errbuf IN OUT NOCOPY VARCHAR2
410 ,retcode IN OUT NOCOPY NUMBER)
411 IS
412
413 l_proc_w_ineff_ctrls number := 0;
414 l_ineffective_ctrls number := 0;
415 l_unmitigated_risks number := 0;
416 l_open_issues number := 0;
417
418 l_stmnt_id NUMBER := 0;
419 l_run_date DATE;
420 l_proc_name VARCHAR2(30);
421 l_status VARCHAR2(30) ;
422 l_industry VARCHAR2(30) ;
423 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
424
425 l_user_id NUMBER ;
426 l_login_id NUMBER ;
427 l_program_id NUMBER ;
428 l_program_login_id NUMBER ;
429 l_program_application_id NUMBER ;
430 l_request_id NUMBER ;
431
432 BEGIN
433 l_user_id := NVL(fnd_global.USER_ID, -1);
434 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
435 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
436 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
437 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
438 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
439
440 ----dbms_output.put_line( '1 **************' );
441
442 l_stmnt_id := 0;
443 l_proc_name := 'intitial_load';
444 check_initial_load_setup(
445 x_global_start_date => g_global_start_date
446 ,x_rci_schema => g_rci_schema);
447
448 l_stmnt_id := 10;
449 DELETE FROM rci_dr_inc where fact_name = 'RCI_PROCESS_DETAIL_F';
450
451 ----dbms_output.put_line( '2 **************' );
452
453 l_stmnt_id := 20;
454 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_PROCESS_DETAIL_F');
455
456 ----dbms_output.put_line( '3 **************' );
457 l_stmnt_id := 30;
458 l_run_date := sysdate - 5/(24*60);
459
460 INSERT INTO RCI_PROCESS_DETAIL_F(
461 project_id
462 ,process_org_rev_id
463 ,fin_certification_id
464 ,certification_id
465 ,certification_status
466 ,certification_type
467 ,certification_period_name
468 ,certification_period_set_name
469 ,organization_id
470 ,process_id
471 ,natural_account_id
472 ,significant_process_flag
473 ,standard_process_flag
474 ,process_category
475 ,certification_result_code
476 ,certified_by_id
477 ,certified_on
478 ,evaluation_result_code
479 ,evaluated_by_id
480 ,last_evaluated_on
481 /** 10.19.2005 npanandi begin ***/
482 ,period_year
483 ,period_num
484 ,quarter_num
485 ,ent_period_id
486 ,ent_qtr_id
487 ,ent_year_id
488 ,report_date_julian
489 /** 10.19.2005 npanandi end ***/
490 ,creation_date
491 ,created_by
492 ,last_update_date
493 ,last_updated_by
494 ,last_update_login)
495 SELECT distinct opinions_eval.pk2_value,
496 execs.PROCESS_ORG_REV_ID,
497 acv2.certification_id,
498 execs.entity_id,
499 acv.certification_status,
500 acv.certification_type,
501 acv.CERTIFICATION_PERIOD_NAME,
502 acv.CERTIFICATION_PERIOD_SET_NAME,
503 execs.organization_id,
504 execs.process_id,
505 afkav.natural_account_id,
506 nvl(process.significant_process_flag,'N'),
507 nvl(process.standard_process_flag,'N'),
508 process.process_category,
509 opinions_cert.audit_result_code,
510 opinions_cert.authored_by,
511 opinions_cert.authored_date,
512 opinions_eval.audit_result_code,
513 opinions_eval.authored_by,
514 opinions_eval.authored_date,
515 /** 10.19.2005 npanandi begin ***/
516 agpv.period_year,
517 agpv.period_num,
518 agpv.quarter_num,
519 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
520 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
521 agpv.period_year,
522 to_number(to_char(agpv.end_date,'J')),
523 /** 10.19.2005 npanandi end ***/
524 sysdate,
525 G_USER_ID,
526 sysdate,
527 G_USER_ID,
528 G_LOGIN_ID
529 FROM amw_proc_cert_eval_sum proccert,
530 amw_opinions_log_v opinions_eval,
531 amw_opinions_v opinions_cert,
532 amw_process_organization_vl process,
533 amw_execution_scope execs,
534 amw_audit_units_v audit_v,
535 amw_certification_vl acv,
536 amw_audit_projects_v aapv,
537 amw_certification_vl acv2,
538 AMW_FIN_PROC_CERT_RELAN afpcr,
539 (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,
540 AMW_FIN_KEY_ACCOUNTS_VL AFKAV,
541 /** 10.19.2005 npanandi begin ***/
542 amw_gl_periods_v agpv
543 /** 10.19.2005 npanandi end ***/
544 WHERE execs.entity_id = opinions_cert.pk2_value(+)
545 AND execs.organization_id = opinions_cert.pk3_value(+)
546 AND execs.process_id = opinions_cert.pk1_value(+)
547 AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
548 AND execs.entity_id = proccert.certification_id
549 AND execs.process_org_rev_id = proccert.process_org_rev_id
550 and execs.entity_id = acv.certification_id
551 AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
552 AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
553 AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
554 AND process.process_org_rev_id = execs.process_org_rev_id
555 AND process.organization_id = audit_v.organization_id
556 and aapv.audit_project_id(+) = opinions_eval.pk2_value
557 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
558 and afpcr.END_DATE is null
559 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
560 and acv2.object_type='FIN_STMT'
561 and aaa.pk1(+) = execs.organization_id
562 and aaa.pk2(+) = execs.process_id
563 and aaa.natural_account_id = afkav.natural_account_id(+)
564 /** 10.19.2005 npanandi begin ***/
565 and acv.certification_period_name = agpv.period_name
566 and acv.certification_period_set_name = agpv.period_set_name
567 /** 10.19.2005 npanandi end ***/
568 ORDER BY execs.entity_id,execs.organization_id,execs.process_id asc;
569
570 ----dbms_output.put_line( '4 **************' );
571
572 l_stmnt_id :=50;
573
574 INSERT INTO rci_dr_inc( fact_name
575 ,last_run_date
576 ,created_by
577 ,creation_date
578 ,last_update_date
579 ,last_updated_by
580 ,last_update_login
581 ,program_id
582 ,program_login_id
583 ,program_application_id
584 ,request_id ) VALUES (
585 'RCI_PROCESS_DETAIL_F'
586 ,l_run_date
587 ,l_user_id
588 ,sysdate
589 ,sysdate
590 ,l_user_id
591 ,l_login_id
592 ,l_program_id
593 ,l_program_login_id
594 ,l_program_application_id
595 ,l_request_id );
596
597 ----dbms_output.put_line( '6 **************' );
598 l_stmnt_id := 70;
599 commit;
600 retcode := C_OK;
601 EXCEPTION
602 WHEN OTHERS THEN
603 retcode := C_ERROR;
604 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
605 ROLLBACK;
606 RAISE;
607 END initial_load_obsolete;
608
609
610 -- run_incr_load_opm
611 -- Parameters:
612 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
613 -- errbuf - empty on successful completion, message on error or warning
614 --
615 -- History:
619 PROCEDURE incr_load(
616 -- Date Author Action
617 -- 09/08/2005 Panandikar Nilesh G Defined Body.
618
620 errbuf in out NOCOPY VARCHAR2
621 ,retcode in out NOCOPY NUMBER)
622 IS
623
624 l_stmnt_id NUMBER := 0;
625 l_run_date DATE;
626 l_last_run_date DATE;
627 l_proc_name VARCHAR2(30);
628 l_message VARCHAR2(30);
629 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
630 l_count NUMBER;
631
632 l_user_id NUMBER;
633 l_login_id NUMBER;
634 l_program_id NUMBER;
635 l_program_login_id NUMBER;
636 l_program_application_id NUMBER;
637 l_request_id NUMBER;
638
639 l_proc_w_ineff_ctrls number;
640 l_ineffective_ctrls number;
641 l_unmitigated_risks number;
642 l_open_issues number;
643
644 BEGIN
645 l_user_id := NVL(fnd_global.USER_ID, -1);
646 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
647 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
648 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
649 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
650 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
651
652 l_stmnt_id := 10;
653 l_proc_name := 'run_incr_load_drm';
654 l_last_run_date := get_last_run_date('RCI_PROCESS_DETAIL_F');
655
656 IF l_last_run_date IS NULL THEN
657 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
658 RAISE INITIALIZATION_ERROR;
659 END IF;
660
661 l_stmnt_id := 20;
662 l_run_date := sysdate - 5/(24*60);
663 ---l_master_org := get_master_organization_id;
664
665 l_stmnt_id := 30;
666 /** 01.16.06 npanandi: added below procedure cal as RSG errors otherwise **/
667 check_initial_load_setup(
668 x_global_start_date => g_global_start_date
669 ,x_rci_schema => g_rci_schema);
670 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_PROCESS_DETAIL_F');
671
672 INSERT INTO RCI_PROCESS_DETAIL_F(
673 project_id
674 ,process_org_rev_id
675 ,fin_certification_id
676 ,certification_id
677 ,certification_status
678 ,certification_type
679 ,certification_period_name
680 ,certification_period_set_name
681 ,organization_id
682 ,process_id
683 ,natural_account_id
684 ,significant_process_flag
685 ,standard_process_flag
686 ,process_category
687 ,certification_result_code
688 ,certified_by_id
689 ,certified_on
690 ,evaluation_result_code
691 ,evaluated_by_id
692 ,last_evaluated_on
693 /** 10.19.2005 npanandi begin ***/
694 ,period_year
695 ,period_num
696 ,quarter_num
697 ,ent_period_id
698 ,ent_qtr_id
699 ,ent_year_id
700 ,report_date_julian
701 /** 10.19.2005 npanandi end ***/
702 ,creation_date
703 ,created_by
704 ,last_update_date
705 ,last_updated_by
709 finprocsum.FIN_CERTIFICATION_ID,
706 ,last_update_login)
707 select distinct evalopn.pk2_value, /*project_id*/
708 proc.process_org_rev_id,
710 -10000, /*certification_id, cannot insert NULL here*/
711 acb.certification_status,
712 acb.certification_type,
713 acb.certification_period_name,
714 acb.certification_period_set_name,
715 o.organization_id,
716 proc.process_id,
717 aaa.natural_account_id,
718 nvl(proc.significant_process_flag,'N'),
719 nvl(proc.standard_process_flag,'N'),
720 proc.process_category,
721 certopn.audit_result_code, /*certification_result_code*/
722 certopn.authored_by, /*certified_by_id*/
723 certopn.authored_date, /*certified_on*/
724 evalopn.audit_result_code, /*evaluation_result_code*/
725 evalopn.authored_by, /*evaluated_by_id*/
726 evalopn.authored_date, /*last_evaluated_on*/
727 agpv.period_year,
728 agpv.period_num,
729 agpv.quarter_num,
730 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
731 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
732 agpv.period_year,
733 to_number(to_char(agpv.end_date,'J')),
734 sysdate,
735 G_USER_ID,
736 sysdate,
737 G_USER_ID,
738 G_LOGIN_ID
739 from AMW_FIN_CERT_SCOPE finscope,
740 AMW_FIN_PROC_CERT_RELAN REL,
741 AMW_FIN_PROCESS_EVAL_SUM finprocsum,
742 HR_ALL_ORGANIZATION_UNITS o,
743 HR_ALL_ORGANIZATION_UNITS_TL otl,
744 AMW_PROCESS_ORGANIZATION_VL proc,
745 /*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V certopn,
746 /*AMW_OPINIONS_V*/ AMW_OPINIONS_LOG_V evalopn,
747 amw_certification_b acb,
748 (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,
749 amw_gl_periods_v agpv
750 where rel.FIN_STMT_CERT_ID = finprocsum.FIN_CERTIFICATION_ID
751 and rel.end_date is null
752 and finprocsum.FIN_CERTIFICATION_ID = finscope.fin_certification_id
753 and finprocsum.ORGANIZATION_ID = finscope.ORGANIZATION_ID
754 and finprocsum.PROCESS_ID = finscope.PROCESS_ID
755 and finprocsum.PROCESS_ORG_REV_ID = proc.PROCESS_ORG_REV_ID
756 and o.organization_id = finscope.organization_id
757 and o.organization_id = otl.organization_id
758 and otl.language = userenv('LANG')
759 and finprocsum.EVAL_OPINION_log_ID = evalopn.opinion_log_id(+)
760 and finprocsum.cert_opinion_log_id = certopn.opinion_log_id(+)
761 /***and certopn.opinion_type_code = 'CERTIFICATION'
762 and certopn.object_name = 'AMW_ORG_PROCESS'
763 and certopn.pk1_value = finscope.process_id
764 and certopn.pk2_value = rel.PROC_CERT_ID
765 and certopn.pk3_value = finscope.organization_id
766 AND certopn.authored_date = (select max(aov2.authored_date) from AMW_OPINIONS aov2
767 where aov2.object_opinion_type_id = certopn.object_opinion_type_id
768 and aov2.pk3_value = certopn.pk3_value
769 AND aov2.pk2_value in (select proc_cert_Id
770 from AMW_FIN_PROC_CERT_RELAN
771 where fin_stmt_cert_id = finprocsum.FIN_CERTIFICATION_ID
772 and end_date is null)
773 and aov2.pk1_value = certopn.pk1_value)
774 ***/
775 and finprocsum.FIN_CERTIFICATION_ID = acb.certification_id
776 and aaa.pk1(+) = finprocsum.organization_id
777 and aaa.pk2(+) = finprocsum.process_id
778 and acb.certification_period_name = agpv.period_name
779 and acb.certification_period_set_name = agpv.period_set_name;
780
781 l_stmnt_id :=40;
782 UPDATE rci_dr_inc
783 SET last_run_date = l_run_date
784 ,last_update_date = sysdate
785 ,last_updated_by = l_user_id
786 ,last_update_login = l_login_id
787 ,program_id = l_program_id
788 ,program_login_id = l_program_login_id
789 ,program_application_id = l_program_application_id
790 ,request_id = l_request_id
791 WHERE fact_name = 'RCI_PROCESS_DETAIL_F' ;
792
793 l_stmnt_id :=50;
794 commit;
795 retcode := C_OK;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 retcode := C_ERROR;
800 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
801 ROLLBACK;
802 RAISE;
803 END incr_load;
804
805 PROCEDURE incr_load_obsolete(
806 errbuf in out NOCOPY VARCHAR2
807 ,retcode in out NOCOPY NUMBER)
808 IS
809
810 l_stmnt_id NUMBER := 0;
811 l_run_date DATE;
812 l_last_run_date DATE;
813 l_proc_name VARCHAR2(30);
814 l_message VARCHAR2(30);
815 l_master_org MTL_PARAMETERS.MASTER_ORGANIZATION_ID%TYPE;
816 l_count NUMBER;
817
818 l_user_id NUMBER;
819 l_login_id NUMBER;
820 l_program_id NUMBER;
821 l_program_login_id NUMBER;
822 l_program_application_id NUMBER;
823 l_request_id NUMBER;
824
825 l_proc_w_ineff_ctrls number;
826 l_ineffective_ctrls number;
827 l_unmitigated_risks number;
828 l_open_issues number;
829
830 BEGIN
831 l_user_id := NVL(fnd_global.USER_ID, -1);
832 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
833 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
834 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
835 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
836 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
837
838 l_stmnt_id := 10;
839 l_proc_name := 'run_incr_load_drm';
840 l_last_run_date := get_last_run_date('RCI_PROCESS_DETAIL_F');
841
842 IF l_last_run_date IS NULL THEN
843 l_message := 'Please launch the Initial Load Request Set for the Organization Certification Summary page.';
844 RAISE INITIALIZATION_ERROR;
845 END IF;
846
847 l_stmnt_id := 20;
848 l_run_date := sysdate - 5/(24*60);
849 ---l_master_org := get_master_organization_id;
850
851 l_stmnt_id := 30;
852 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_PROCESS_DETAIL_F');
853
854 INSERT INTO RCI_PROCESS_DETAIL_F(
855 project_id
856 ,process_org_rev_id
857 ,fin_certification_id
858 ,certification_id
859 ,certification_status
860 ,certification_type
861 ,certification_period_name
862 ,certification_period_set_name
863 ,organization_id
864 ,process_id
865 ,natural_account_id
866 ,significant_process_flag
867 ,standard_process_flag
868 ,process_category
869 ,certification_result_code
870 ,certified_by_id
871 ,certified_on
872 ,evaluation_result_code
873 ,evaluated_by_id
874 ,last_evaluated_on
875 /** 10.19.2005 npanandi begin ***/
876 ,period_year
877 ,period_num
878 ,quarter_num
879 ,ent_period_id
880 ,ent_qtr_id
881 ,ent_year_id
885 ,created_by
882 ,report_date_julian
883 /** 10.19.2005 npanandi end ***/
884 ,creation_date
886 ,last_update_date
887 ,last_updated_by
888 ,last_update_login)
889 SELECT distinct opinions_eval.pk2_value,
890 execs.PROCESS_ORG_REV_ID,
891 acv2.certification_id,
892 execs.entity_id,
893 acv.certification_status,
894 acv.certification_type,
895 acv.CERTIFICATION_PERIOD_NAME,
896 acv.CERTIFICATION_PERIOD_SET_NAME,
897 execs.organization_id,
898 execs.process_id,
899 afkav.natural_account_id,
900 nvl(process.significant_process_flag,'N'),
901 nvl(process.standard_process_flag,'N'),
902 process.process_category,
903 opinions_cert.audit_result_code,
904 opinions_cert.authored_by,
905 opinions_cert.authored_date,
906 opinions_eval.audit_result_code,
907 opinions_eval.authored_by,
908 opinions_eval.authored_date,
909 /** 10.19.2005 npanandi begin ***/
910 agpv.period_year,
911 agpv.period_num,
912 agpv.quarter_num,
913 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
914 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
915 agpv.period_year,
916 to_number(to_char(agpv.end_date,'J')),
917 /** 10.19.2005 npanandi end ***/
918 sysdate,
919 G_USER_ID,
920 sysdate,
921 G_USER_ID,
922 G_LOGIN_ID
923 FROM amw_proc_cert_eval_sum proccert,
924 amw_opinions_log_v opinions_eval,
925 amw_opinions_v opinions_cert,
926 amw_process_organization_vl process,
927 amw_execution_scope execs,
928 amw_audit_units_v audit_v,
929 amw_certification_vl acv,
930 amw_audit_projects_v aapv,
931 amw_certification_vl acv2,
932 AMW_FIN_PROC_CERT_RELAN afpcr,
933 (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,
934 AMW_FIN_KEY_ACCOUNTS_VL AFKAV,
935 /** 10.19.2005 npanandi begin ***/
936 amw_gl_periods_v agpv
937 /** 10.19.2005 npanandi end ***/
938 WHERE execs.entity_id = opinions_cert.pk2_value(+)
939 AND execs.organization_id = opinions_cert.pk3_value(+)
940 AND execs.process_id = opinions_cert.pk1_value(+)
941 AND execs.entity_type = 'BUSIPROC_CERTIFICATION'
942 AND execs.entity_id = proccert.certification_id
943 AND execs.process_org_rev_id = proccert.process_org_rev_id
944 and execs.entity_id = acv.certification_id
945 AND opinions_cert.opinion_type_code(+) = 'CERTIFICATION'
946 AND opinions_cert.object_name(+) = 'AMW_ORG_PROCESS'
947 AND proccert.evaluation_opinion_log_id = opinions_eval.opinion_log_id(+)
948 AND process.process_org_rev_id = execs.process_org_rev_id
949 AND process.organization_id = audit_v.organization_id
950 and aapv.audit_project_id(+) = opinions_eval.pk2_value
951 and afpcr.PROC_CERT_ID = acv.CERTIFICATION_ID
952 and afpcr.END_DATE is null
953 and afpcr.FIN_STMT_CERT_ID = acv2.CERTIFICATION_ID
954 and acv2.object_type='FIN_STMT'
955 and aaa.pk1(+) = execs.organization_id
956 and aaa.pk2(+) = execs.process_id
957 and aaa.natural_account_id = afkav.natural_account_id(+)
958 /** 10.19.2005 npanandi begin ***/
959 and acv.certification_period_name = agpv.period_name
960 and acv.certification_period_set_name = agpv.period_set_name
961 /** 10.19.2005 npanandi end ***/
962 ORDER BY execs.entity_id,execs.organization_id,execs.process_id asc;
963
964 l_stmnt_id :=40;
965 UPDATE rci_dr_inc
966 SET last_run_date = l_run_date
967 ,last_update_date = sysdate
968 ,last_updated_by = l_user_id
969 ,last_update_login = l_login_id
970 ,program_id = l_program_id
971 ,program_login_id = l_program_login_id
972 ,program_application_id = l_program_application_id
973 ,request_id = l_request_id
974 WHERE fact_name = 'RCI_PROCESS_DETAIL_F' ;
975
976
977 commit;
978 retcode := C_OK;
979
980 EXCEPTION
981 WHEN OTHERS THEN
982 retcode := C_ERROR;
983 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM || ':' || l_message, l_proc_name, l_stmnt_id));
984 ROLLBACK;
985 RAISE;
986 END incr_load_obsolete;
987
988 END RCI_PROC_DETAIL_ETL_PKG;