1 PACKAGE RCI_ORG_DFCY_ETL_PKG AUTHID CURRENT_USER AS
2 --$Header: rciodfcyetls.pls 120.3.12000000.1 2007/01/16 20:46:25 appldev ship $
3
4 /**01.01.2006 npanandi: made a copy of initial_load and obsoleted earlier one **/
5 PROCEDURE initial_load(
6 errbuf IN OUT NOCOPY VARCHAR2
7 ,retcode IN OUT NOCOPY NUMBER);
8
9 PROCEDURE initial_load_obsolete(
10 errbuf IN OUT NOCOPY VARCHAR2
11 ,retcode IN OUT NOCOPY NUMBER);
12
13 /**01.01.2006 npanandi: made a copy of incremental_load and obsoleted earlier one **/
14 PROCEDURE incr_load(
15 errbuf IN OUT NOCOPY VARCHAR2
16 ,retcode IN OUT NOCOPY NUMBER);
17
18 PROCEDURE incr_load_obsolete(
19 errbuf IN OUT NOCOPY VARCHAR2
20 ,retcode IN OUT NOCOPY NUMBER);
21
22 FUNCTION get_last_run_date ( p_fact_name VARCHAR2) RETURN DATE;
23
24 FUNCTION err_mesg (
25 p_mesg IN VARCHAR2
26 ,p_proc_name IN VARCHAR2 DEFAULT NULL
27 ,p_stmt_id IN NUMBER DEFAULT -1) RETURN VARCHAR2 ;
28
29 PROCEDURE check_initial_load_setup (
30 x_global_start_date OUT NOCOPY DATE
31 ,x_rci_schema OUT NOCOPY VARCHAR2);
32
33 ----this is to determine number of unmitigated_risks
34 ---per process, per organization, per certification
35 cursor c_unmitigated_risks (c_certification_id in number
36 ,c_organization_id in number
37 ,c_process_id in number) is
38 SELECT count(1)
39 FROM (SELECT DISTINCT ara.pk1 certification_id, ara.pk2 organization_id, ara.pk3 process_id, ara.risk_id
40 FROM amw_risk_associations ara, amw_opinions_v aov
41 WHERE ara.object_type = 'BUSIPROC_CERTIFICATION'
42 AND ara.pk1 = c_certification_id
43 AND ara.pk2 = c_organization_id
44 AND ara.pk3 IN (SELECT DISTINCT process_id
45 FROM amw_execution_scope
46 START WITH process_id = c_process_id
47 AND organization_id = c_organization_id
48 AND entity_id = c_certification_id
49 and entity_type='BUSIPROC_CERTIFICATION'
50 CONNECT BY PRIOR process_id = parent_process_id
51 AND organization_id = PRIOR organization_id
52 AND entity_id = PRIOR entity_id
53 and entity_type=prior entity_type)
54 AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
55 AND aov.opinion_type_code = 'EVALUATION'
56 AND aov.pk3_value = ara.pk2 --org_id
57 AND aov.pk4_value = ara.pk3 --process_id
58 AND aov.pk1_value = ara.risk_id
59 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
60 FROM amw_opinions aov2
61 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
62 AND aov2.pk4_value = aov.pk4_value
63 AND aov2.pk3_value = aov.pk3_value
64 AND aov2.pk1_value = aov.pk1_value)
65 AND aov.audit_result_code <> 'EFFECTIVE');
66
67
68 ----this is to determine number of ineffective controls
69 ---per process, per organization, per certification
70 cursor c_ineffective_controls (c_certification_id in number
71 ,c_organization_id in number
72 ,c_process_id in number) is
73 SELECT count(1) FROM
74 (SELECT DISTINCT aca.pk1 certification_id,
75 aca.pk2 organization_id,
76 aca.pk3 process_id,
77 aca.pk4 risk_id,
78 aca.control_id
79 FROM amw_control_associations aca,
80 amw_opinions_v aov
81 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
82 AND aca.pk1 = c_certification_id
83 AND aca.pk2 = c_organization_id
84 AND aca.pk3 IN (SELECT DISTINCT process_id
85 FROM amw_execution_scope
86 START WITH process_id = c_process_id
87 AND organization_id = c_organization_id
88 AND entity_id = c_certification_id
89 and entity_type='BUSIPROC_CERTIFICATION'
90 CONNECT BY PRIOR process_id = parent_process_id
91 AND organization_id = PRIOR organization_id
92 AND entity_id = PRIOR entity_id
93 and entity_type=prior entity_type)
94 AND aov.object_name = 'AMW_ORG_CONTROL'
95 AND aov.opinion_type_code = 'EVALUATION'
96 AND aov.pk3_value = c_organization_id
97 AND aov.pk1_value = aca.control_id
98 AND aov.audit_result_code <> 'EFFECTIVE'
99 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
100 FROM amw_opinions aov2
101 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
102 AND aov2.pk3_value = aov.pk3_value
103 AND aov2.pk1_value = aov.pk1_value));
104
105
106
107 END RCI_ORG_DFCY_ETL_PKG;
[Home] [Help]