1 PACKAGE AMW_ORG_PROC_CERT_DATED_SUMM as
2 /* $Header: amwpcers.pls 120.0 2005/07/29 00:36:53 appldev noship $ */
3
4
5 -- ORGANIZATION FUNCTIONs
6
7 -- Get number of unmitigated risks given a certification and org within the certification
8 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
9 -- If material risks flag is passed, then only material risks are considered.
10 -- Considers entity risks also.
11 FUNCTION get_unmit_risk_for_org
12 (p_certification_id in NUMBER,
13 p_org_id in NUMBER,
14 p_from_date in DATE,
15 p_to_date in DATE,
16 p_material_risks_flag in VARCHAR2)
17 RETURN NUMBER;
18
19
20 -- Get number of evaluated risks given a certification and org within the certification
21 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
22 -- If material risks flag is passed, then only material risks are considered.
23 -- Considers entity risks also.
24 FUNCTION get_eval_risk_for_org
25 (p_certification_id IN NUMBER,
26 p_org_id in NUMBER,
27 p_from_date in DATE,
28 p_to_date in DATE,
29 p_material_risks_flag in VARCHAR2)
30 RETURN NUMBER;
31
32 -- Get number of risks given a certification and org within the certification
33 -- If material risks flag is passed, then only material risks are considered.
34 -- Considers entity risks also.
35 FUNCTION get_total_risks_for_org
36 (p_certification_id IN NUMBER,
37 p_org_id in NUMBER,
38 p_material_risks_flag in VARCHAR2)
39 RETURN NUMBER;
40
41 -- Get number of evaluated controls given a certification and org within the certification
42 -- If key controls flag is passed, then only key controls are considered.
43 FUNCTION get_eval_ctrls_for_org
44 (p_certification_id IN NUMBER,
45 p_org_id in NUMBER,
46 p_from_date in DATE,
47 p_to_date in DATE,
48 p_key_ctrls_flag in VARCHAR2)
49 RETURN NUMBER;
50
51 -- Get number of controls given a certification and org within the certification
52 -- If key controls flag is passed, then only key controls are considered.
53 FUNCTION get_total_ctrls_for_org
54 (p_certification_id IN NUMBER,
55 p_org_id in NUMBER,
56 p_key_ctrls_flag in VARCHAR2)
57 RETURN NUMBER;
58
59 -- Get number of ineffective controls given a certification and org within the certification
60 -- If key controls flag is passed, then only key controls are considered.
61 FUNCTION get_ineff_ctrls_for_org
62 (p_certification_id IN NUMBER,
63 p_org_id in NUMBER,
64 p_from_date in DATE,
65 p_to_date in DATE,
66 p_key_ctrls_flag in VARCHAR2)
67 RETURN NUMBER;
68
69 -- Get number of process given a certification and org within the certification
70 -- If significant process flag is passed then filter on significant process flag.
71 FUNCTION get_all_process_in_org
72 (p_certification_id IN NUMBER,
73 p_org_id in NUMBER,
74 p_sig_process in VARCHAR2)
75 RETURN NUMBER;
76
77
78 -- Get number of process certified given a certification and org within the certification
79 -- If significant process flag is passed then filter on significant process flag.
80 FUNCTION get_cert_process_in_org
81 (p_certification_id IN NUMBER,
82 p_org_id in NUMBER,
83 p_from_date in DATE,
84 p_to_date in DATE,
85 p_sig_process in VARCHAR2)
86 RETURN NUMBER;
87
88 -- Get number of process certified with issues given a certification and org within the certification
89 -- If significant process flag is passed then filter on significant process flag.
90 FUNCTION get_process_cert_issues_in_org
91 (p_certification_id IN NUMBER,
92 p_org_id in NUMBER,
93 p_from_date in DATE,
94 p_to_date in DATE,
95 p_sig_process in VARCHAR2)
96 RETURN NUMBER;
97
98 -----------------------------------------------------------------------------------------------------
99 -- ORG PROCESS FUNCTIONs
100
101 -- Get number of ineffective controls given a certification, process and org within the certification
102 -- If key controls flag is passed, then only key controls are considered.
103 FUNCTION get_ineff_ctrls_for_org_proc
104 (p_certification_id IN NUMBER,
105 p_org_id in NUMBER,
106 p_process_id in NUMBER,
107 p_from_date in DATE,
108 p_to_date in DATE,
109 p_key_ctrls_flag in VARCHAR2)
110 RETURN NUMBER;
111
112 -- Get number of evaluated controls given a certification, process and org within the certification
113 -- If key controls flag is passed, then only key controls are considered.
114 FUNCTION get_eval_ctrls_for_org_proc
115 (p_certification_id IN NUMBER,
116 p_org_id in NUMBER,
117 p_process_id in NUMBER,
118 p_from_date in DATE,
119 p_to_date in DATE,
120 p_key_ctrls_flag in VARCHAR2)
121 RETURN NUMBER;
122
123 -- Get number of controls given a certification, process and org within the certification
124 -- If key controls flag is passed, then only key controls are considered.
125 FUNCTION get_total_ctrls_for_org_proc
126 (p_certification_id IN NUMBER,
127 p_org_id in NUMBER,
128 p_process_id in NUMBER,
129 p_key_ctrls_flag in VARCHAR2)
130 RETURN NUMBER;
131
132 -- Get number of risks given a certification, process and org within the certification
133 -- If material risks flag is passed, then only material risks are considered.
134 FUNCTION get_total_risks_for_org_proc
135 (p_certification_id IN NUMBER,
136 p_org_id in NUMBER,
137 p_process_id in NUMBER,
138 p_material_risks_flag in VARCHAR2)
139 RETURN NUMBER;
140
141
142 -- Get number of evaluated risks given a certification, process and org within the certification
143 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
144 -- If material risks flag is passed, then only material risks are considered.
145 FUNCTION get_eval_risk_for_org_proc
146 (p_certification_id IN NUMBER,
147 p_org_id in NUMBER,
148 p_process_id in NUMBER,
149 p_from_date in DATE,
150 p_to_date in DATE,
151 p_material_risks_flag in VARCHAR2)
152 RETURN NUMBER;
153
154 -- Get number of unmitigated risks given a certification, org and process within the certification
155 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
156 -- If material risks flag is passed, then only material risks are considered.
157 FUNCTION get_unmit_risk_for_org_proc
158 (p_certification_id in NUMBER,
159 p_org_id in NUMBER,
160 p_process_id in NUMBER,
161 p_from_date in DATE,
162 p_to_date in DATE,
163 p_material_risks_flag in VARCHAR2)
164 RETURN NUMBER;
165
166 -- Get number of sub orgs associated to the given process within a given org.
167 FUNCTION get_total_org
168 (p_certification_id in NUMBER,
169 p_org_id in NUMBER,
170 p_process_id in NUMBER)
171 RETURN NUMBER;
172
173
174 -- Get number of sub orgs certified with issues that are associated to the given process and within a given org.
175 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
176 FUNCTION get_total_org_cert_issues
177 (p_certification_id in NUMBER,
178 p_org_id in NUMBER,
179 p_process_id in NUMBER,
180 p_from_date in DATE,
181 p_to_date in DATE)
182 RETURN NUMBER;
183
184 -- Get number of sub orgs certified that are associated to the given process and within a given org.
185 -- If fromDate and toDate are passed, then only evaluations created within that period are considered.
186 FUNCTION get_total_org_cert
187 (p_certification_id in NUMBER,
188 p_org_id in NUMBER,
189 p_process_id in NUMBER,
190 p_from_date in DATE,
191 p_to_date in DATE)
192 RETURN NUMBER;
193
194 -- Get number of sub processes of a process
195 FUNCTION get_total_sub_process
196 (p_certification_id in NUMBER,
197 p_org_id in NUMBER,
198 p_process_id in NUMBER)
199 RETURN NUMBER;
200
201 FUNCTION get_cert_sub_process
202 (p_certification_id in NUMBER,
203 p_org_id in NUMBER,
204 p_process_id in NUMBER,
205 p_from_date in DATE,
206 p_to_date in DATE)
207 RETURN NUMBER;
208
209 FUNCTION get_sub_process_cert_issues
210 (p_certification_id in NUMBER,
211 p_org_id in NUMBER,
212 p_process_id in NUMBER,
213 p_from_date in DATE,
214 p_to_date in DATE)
215 RETURN NUMBER;
216
217 FUNCTION get_ineff_ctrl_prcnt_for_org
218 (p_certification_id IN NUMBER,
219 p_org_id in NUMBER,
220 p_from_date in DATE,
221 p_to_date in DATE,
222 p_key_ctrls_flag in VARCHAR2)
223 RETURN NUMBER;
224
225 FUNCTION get_ineff_ctrl_prcnt_org_proc
226 (p_certification_id IN NUMBER,
227 p_org_id in NUMBER,
228 p_process_id in NUMBER,
229 p_from_date in DATE,
230 p_to_date in DATE,
231 p_key_ctrls_flag in VARCHAR2)
232 RETURN NUMBER;
233
234 FUNCTION get_unmit_risk_prcnt_org_proc
235 (p_certification_id IN NUMBER,
236 p_org_id in NUMBER,
237 p_process_id in NUMBER,
238 p_from_date in DATE,
239 p_to_date in DATE,
240 p_material_risks_flag in VARCHAR2)
241 RETURN NUMBER;
242
243 FUNCTION get_unmit_risk_prcnt_for_org
244 (p_certification_id IN NUMBER,
245 p_org_id in NUMBER,
246 p_from_date in DATE,
247 p_to_date in DATE,
248 p_material_risks_flag in VARCHAR2)
249 RETURN NUMBER;
250
251 END AMW_ORG_PROC_CERT_DATED_SUMM;