[Home] [Help]
PACKAGE BODY: APPS.RCI_SIG_ACCT_EVAL_SUMM_PKG
Source
1 PACKAGE BODY RCI_SIG_ACCT_EVAL_SUMM_PKG AS
2 /*$Header: rcisgacb.pls 120.26.12000000.1 2007/01/16 20:46:46 appldev ship $*/
3
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
16 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
17
18 PROCEDURE get_kpi(
19 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
20 ,p_exp_source_sql out NOCOPY VARCHAR2
21 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
22 is
23 l_sqlstmt VARCHAR2(15000);
24 total_qry VARCHAR2(15000);
25 counter NUMBER := 0;
26 where_clause VARCHAR2(2000) := ' ';
27 v_total NUMBER;
28 v_period varchar2(10);
29 l_bind_rec BIS_QUERY_ATTRIBUTES;
30
31 BEGIN
32
33 counter := p_page_parameter_tbl.COUNT;
34 FOR i IN 1..counter LOOP
35 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
36 IF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
37 where_clause := where_clause || ' AND accteval.certification_id='|| p_page_parameter_tbl(i).parameter_id ;
38 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
39 where_clause := where_clause || ' AND accteval.cert_type='|| p_page_parameter_tbl(i).parameter_id ;
40 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
41 v_period := p_page_parameter_tbl(i).parameter_id;
42 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
43 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
44 v_period := p_page_parameter_tbl(i).parameter_id;
45 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
46 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
47 v_period := p_page_parameter_tbl(i).parameter_id;
48 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
49 END IF;
50 END IF; -- parameter_id IS NOT NULL
51 END LOOP;
52
53 total_qry := 'SELECT COUNT(1) FROM (
54 select
55 sig_acct_id
56 ,acct_eval_result_code
57 ,certification_id
58 ,acct_eval_e
59 ,acct_eval_ne
60 ,acct_eval_ie
61 ,proc_cert_result_cwi
62 ,proc_cert_result_c
63 ,proc_cert_result_nc
64 ,orgs_with_ineff_ctrls
65 ,unmiti_risks
66 ,ineff_ctrls
67 from
68 rci_sig_acct_eval_f accteval,fii_time_day ftd
69 where
70 accteval.report_date_julian = ftd.report_date_julian
71 '|| where_clause || '
72 group by
73 sig_acct_id
74 ,acct_eval_result_code
75 ,certification_id
76 ,acct_eval_e
77 ,acct_eval_ne
78 ,acct_eval_ie
79 ,proc_cert_result_cwi
80 ,proc_cert_result_c
81 ,proc_cert_result_nc
82 ,orgs_with_ineff_ctrls
83 ,unmiti_risks
84 ,ineff_ctrls )' ;
85 EXECUTE IMMEDIATE total_qry INTO v_total using v_period;
86
87 IF v_total = 0 THEN
88 v_total := 1;
89 END IF;
90
91 l_sqlstmt :='SELECT
92 ROUND((nvl(MES1,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM1,
93 ROUND((nvl(MES2,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM2,
94 ROUND((nvl(MES3,0)*100/'||v_total ||'),2) AS RCI_SIG_ACCT_EVAL_SUMM_DIM3
95 FROM (
96 SELECT
97 SUM(acct_eval_ne) MES1,
98 SUM(acct_eval_ie) MES2,
99 SUM(acct_eval_e) MES3
100 FROM
101 (select
102 sig_acct_id
103 ,acct_eval_result_code
104 ,certification_id
105 ,acct_eval_e
106 ,acct_eval_ne
107 ,acct_eval_ie
108 ,proc_cert_result_cwi
109 ,proc_cert_result_c
110 ,proc_cert_result_nc
111 ,orgs_with_ineff_ctrls
112 ,unmiti_risks
113 ,ineff_ctrls
114 from
115 rci_sig_acct_eval_f accteval,fii_time_day ftd
116 where
117 accteval.report_date_julian = ftd.report_date_julian
118 '|| where_clause || '
119 group by
120 sig_acct_id
121 ,acct_eval_result_code
122 ,certification_id
123 ,acct_eval_e
124 ,acct_eval_ne
125 ,acct_eval_ie
126 ,proc_cert_result_cwi
127 ,proc_cert_result_c
128 ,proc_cert_result_nc
129 ,orgs_with_ineff_ctrls
130 ,unmiti_risks
131 ,ineff_ctrls
132 ) accteval
133 )
134 ';
135
136 p_exp_source_sql := l_sqlstmt;
137 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
138 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
139
140 p_exp_source_output.EXTEND;
141 l_bind_rec.attribute_name := ':TIME';
142 l_bind_rec.attribute_value := v_period;
143 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
144 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
145 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
146
147 END get_kpi;
148
149 PROCEDURE get_sig_acct_eval_details(
150 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
151 ,p_exp_source_sql out NOCOPY VARCHAR2
152 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
153 is
154 l_sqlstmt VARCHAR2(15000);
155 counter NUMBER := 0;
156 where_clause VARCHAR2(2000) := '';
157 v_period varchar2(10);
158 l_bind_rec BIS_QUERY_ATTRIBUTES;
159 BEGIN
160 counter := p_page_parameter_tbl.COUNT;
161 FOR i IN 1..counter LOOP
162 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
163 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
164 where_clause := where_clause || ' AND accteval.organization_id='|| p_page_parameter_tbl(i).parameter_id;
165 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
166 where_clause := where_clause || ' AND accteval.process_id='|| p_page_parameter_tbl(i).parameter_id;
167 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
168 where_clause := where_clause || ' AND accteval.certification_id='|| p_page_parameter_tbl(i).parameter_id;
169 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
170 where_clause := where_clause || ' AND accteval.cert_status='|| p_page_parameter_tbl(i).parameter_id;
171 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
172 /* 12.16.2005 npanandi: changed below because cert_type is a number
173 bug 4893008 fix **/
174 where_clause := where_clause || ' AND accteval.cert_type='|| p_page_parameter_tbl(i).parameter_id;
175 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' THEN
176 if p_page_parameter_tbl(i).parameter_id = 'NOT_EVALUATED' then
177 where_clause := where_clause || ' AND accteval.acct_eval_result_code is null ';
178 else
179 /* 12.16.2005 npanandi: changed below because 3 quotes gives error bug 4893008 fix **/
180 where_clause := where_clause || ' AND accteval.acct_eval_result_code='|| p_page_parameter_tbl(i).parameter_id;
181 end if;
182 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
183 where_clause := where_clause || ' AND accteval.sig_acct_id='|| p_page_parameter_tbl(i).parameter_id;
184 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
185 v_period := p_page_parameter_tbl(i).parameter_id;
186 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
187 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
188 v_period := p_page_parameter_tbl(i).parameter_id;
189 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
190 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
191 v_period := p_page_parameter_tbl(i).parameter_id;
192 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
193 END IF;
194 END IF; -- parameter_id IS NOT NULL
195 END LOOP;
196 --todo if needed join ,amw_fin_key_accounts_tl afka on afka.natural_account_id
197 l_sqlstmt :=
198 '
199 SELECT DISTINCT
200 accteval.sig_acct_id RCI_DRILLDOWN_PARAM2
201 ,'' '' RCI_PROC_CERT_MEASURE14
202 ,afces.fin_certification_id RCI_DRILLDOWN_PARAM1
203 ,acv.certification_name RCI_PROC_CERT_MEASURE1
204 ,typelook.value RCI_PROC_CERT_MEASURE2
205 ,aecv.full_name RCI_PROC_CERT_MEASURE3
206 ,afsv.name RCI_PROC_CERT_MEASURE4
207 ,agpv.quarter_num RCI_PROC_CERT_MEASURE5
208 ,agpv.period_year RCI_PROC_CERT_MEASURE6
209 ,statuslook.value RCI_PROC_CERT_MEASURE7
210 ,trunc(acv.certification_creation_date) RCI_PROC_CERT_MEASURE8
211 ,trunc(acv.target_completion_date) RCI_PROC_CERT_MEASURE9
212 ,proc_pending_certification RCI_PROC_CERT_MEASURE10
213 -- ,evallook.value RCI_PROC_CERT_MEASURE11
214 /*01.26.2006 npanandi: bug 5000443 fix below**/
215 ,/*o.audit_result*/rfaev.value RCI_PROC_CERT_MEASURE11
216 ,o.author RCI_PROC_CERT_MEASURE12
217 ,trunc(o.authored_date) RCI_PROC_CERT_MEASURE13
218 FROM
219 rci_sig_acct_eval_f accteval
220 ,fii_time_day ftd
221 ,amw_fin_cert_eval_sum afces
222 ,amw_opinions_v o
223 ,amw_certification_vl acv
224 ,amw_employees_current_v aecv
225 ,amw_fin_stmnt_vl afsv
226 /* ,(select id,value from rci_fs_acct_eval_v where obj_name=''AMW_KEY_ACCOUNT'') evallook
227 ,(select * from rci_fs_cert_status_v rfcsv) statuslook
228 ,(select * from rci_fs_cert_type_v rfctv) typelook
229 ,(select period_name, quarter_num, period_year from amw_gl_periods_v) agpv*/
230 ,rci_fs_cert_status_v statuslook
231 ,rci_fs_cert_type_v typelook
232 ,amw_gl_periods_v agpv
233 /*01.26.2006 npanandi: bug 5000443 fix below**/
234 ,RCI_FS_ACCT_EVAL_V rfaev
235 WHERE
236 accteval.sig_acct_id = afces.natural_account_id
237 AND accteval.report_date_julian = ftd.report_date_julian
238 AND afces.object_type = ''ACCOUNT''
239 AND accteval.certification_id = acv.certification_id
240 AND afces.fin_certification_id = acv.certification_id
241 AND o.pk1_value(+) = afces.natural_account_id
242 AND o.pk2_value (+)= afces.fin_certification_id
243 AND o.opinion_type_code(+) = ''EVALUATION''
244 AND o.object_name(+) = ''AMW_KEY_ACCOUNT''
245 /*01.26.2006 npanandi: bug 5000443 fix below 2 lines**/
246 and nvl(o.audit_result_code,''NOT_EVALUATED'')=rfaev.id(+)
247 and rfaev.obj_name(+)=''AMW_KEY_ACCOUNT''
248 AND typelook.id(+) = accteval.cert_type
249 AND statuslook.id(+) = accteval.cert_status
250 -- AND evallook.id(+) = o.audit_result_code
251 AND acv.certification_owner_id = aecv.party_id
252 AND acv.financial_statement_id = afsv.financial_statement_id
253 ' || where_clause || '
254 and acv.certification_period_name = agpv.period_name
255 ';
256
257 p_exp_source_sql := l_sqlstmt;
258 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
259 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
260
261 p_exp_source_output.EXTEND;
262 l_bind_rec.attribute_name := ':TIME';
263 l_bind_rec.attribute_value := v_period;
264 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
265 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
266 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
267
268 END get_sig_acct_eval_details;
269
270 -- used in get_org_details
271 FUNCTION get_risk_count(p_org_id number) return number is
272 v_count number;
273 BEGIN
274 select count(*) into v_count from amw_risk_Associations where pk1=p_org_id and (object_type='PROCESS_ORG' or object_type='ENTITY_RISK' );
275 return v_count;
276 END;
277
278 -- used in get_org_details
279 FUNCTION get_control_count(p_org_id number) return number is
280 v_count number;
281 BEGIN
282 select count(*) into v_count from amw_control_Associations where pk1=p_org_id and (object_type='RISK_ORG' or object_type='ENTITY_CONTROL');
283 return v_count;
284 END;
285
286 -- used in get_org_details
287 FUNCTION get_latest_engagement(p_org_id number) return varchar2 is
288 v_name varchar2(80);
289 BEGIN
290 select project_name into v_name from amw_audit_projects_v where audit_project_id =
291 (select entity_id from
292 (select distinct entity_id,creation_date from amw_Execution_scope
293 where entity_type='PROJECT' and organization_id=p_org_id
294 order by creation_date desc
295 ) where rownum<2
296 );
297
298 return v_name;
299 END;
300
301 PROCEDURE get_org_details(
302 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
303 ,p_exp_source_sql out NOCOPY VARCHAR2
304 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
305 is
306 l_sqlstmt VARCHAR2(15000);
307 counter NUMBER := 0;
308 where_clause VARCHAR2(2000) := '';
309 ---01.05.2006 npanandi: added l_act_sqlstmt below
310 l_act_sqlstmt VARCHAR2(15000);
311 v_period varchar2(10);
312 l_bind_rec BIS_QUERY_ATTRIBUTES;
313 BEGIN
314 counter := p_page_parameter_tbl.COUNT;
315 FOR i IN 1..counter LOOP
316 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
317 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
318 where_clause := where_clause || ' AND rocsf.organization_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
319 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
320 where_clause := where_clause || ' AND rocsf.process_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
321 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
322 where_clause := where_clause || ' AND rocsf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
323 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
324 /***12.19.2005 npanandi: 3 quotes below give error ***/
325 where_clause := where_clause || ' AND rocsf.certification_status='|| p_page_parameter_tbl(i).parameter_id ||' ';
326 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
327 /***12.19.2005 npanandi: 3 quotes below give error ***/
328 where_clause := where_clause || ' AND rocsf.certification_type='|| p_page_parameter_tbl(i).parameter_id ||' ';
332 where_clause := where_clause || ' AND rocsf.org_certification_status is null ';
329 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_ORG_CERT_RESULT' THEN
330 /***12.07.2005 npanandi: commented out Divyesh's parameter handler***/
331 /**if p_page_parameter_tbl(i).parameter_id = '''NOT_CERTIFIED''' then
333 else
334 where_clause := where_clause || ' AND rocsf.org_certification_status='|| p_page_parameter_tbl(i).parameter_id ||' ';
335 end if;***/
336 /*** and added the following modified parameter handler ***/
337 if(p_page_parameter_tbl(i).parameter_id = '''EFFECTIVE''')then
338 where_clause := where_clause || ' and rocsf.org_certification_status=''EFFECTIVE'' ';
339 elsif(p_page_parameter_tbl(i).parameter_id = '''INEFFECTIVE''') then
340 /**01.11.2006 npanandi: changed below to handle bug in populating
341 cert_opinion_log_id column in amw_org_eval_sum tbl ***/
342 where_clause := where_clause || ' and decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'')=''INEFFECTIVE'' ';
343 else
344 where_clause := where_clause || ' and rocsf.org_certification_status IS NULL ';
345 end if;
346 /***12.07.2005 npanandi: ends changed code ***/
347 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
348 where_clause := where_clause || ' AND rocsf.natural_account_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
349 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
350 ----12.08.2005 npanandi: changed from agpv to ftd below
351 v_period := p_page_parameter_tbl(i).parameter_id;
352 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
353 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
354 ----12.08.2005 npanandi: changed from agpv to ftd below
355 v_period := p_page_parameter_tbl(i).parameter_id;
356 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
357 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
358 ----12.08.2005 npanandi: changed from agpv to ftd below
359 v_period := p_page_parameter_tbl(i).parameter_id;
360 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
361 END IF;
362 END IF; -- parameter_id IS NOT NULL
363 END LOOP;
364
365 /*** 12.08.2005 npanandi: commenting Divyesh's SQL query construction below
366 l_sqlstmt :=
367 '
368 SELECT DISTINCT
369 aauv.organization_id RCI_DRILLDOWN_PARAM1
370 ,aauv.NAME RCI_PROC_CERT_MEASURE1
371 ,aauv.company RCI_PROC_CERT_MEASURE2
372 ,aauv.lob_description RCI_PROC_CERT_MEASURE3
373 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_risk_count(aauv.organization_id) RCI_PROC_CERT_MEASURE4
374 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_control_count(aauv.organization_id) RCI_PROC_CERT_MEASURE5
375 ,'' '' RCI_PROC_CERT_MEASURE6
376 ,acv.certification_name RCI_PROC_CERT_MEASURE7
377 ,certres.value RCI_PROC_CERT_MEASURE8
378 ,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE9
379 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE10
380 ,evalopinion.eval_result RCI_PROC_CERT_MEASURE11
381 ,trunc(evalopinion.eval_date)RCI_PROC_CERT_MEASURE12
382 ,evalopinion.eval_by RCI_PROC_CERT_MEASURE13
383 FROM
384 amw_audit_units_v aauv
385 ,rci_org_cert_summ_f rocsf
386 ,amw_certification_vl acv
387 ,rci_bp_cert_result_v certres
388 ,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by from amw_opinions_v aov1
389 where aov1.opinion_type_code(+) = ''EVALUATION'' and aov1.object_name(+) = ''AMW_ORGANIZATION''
390 and aov1.authored_date = (select max(aov2.authored_date) from amw_opinions_v aov2
391 where aov2.opinion_type_code(+) = ''EVALUATION'' and aov2.object_name(+) = ''AMW_ORGANIZATION''
392 and aov1.pk1_value=aov2.pk1_value))
393 evalopinion
394 ,(select period_name, period_set_name,
395 to_number(to_char(period_year)||to_char(quarter_num)||to_char(period_num)) ent_period_id,
396 to_number(to_char(period_year)||to_char(quarter_num)) ent_qtr_id,
397 period_year ent_year_id from amw_gl_periods_v) agpv
398 WHERE
399 aauv.organization_id = rocsf.organization_id
400 AND acv.certification_id = rocsf.certification_id
401 AND certres.id = nvl(rocsf.org_certification_status,''NOT_CERTIFIED'')
402 AND evalopinion.org_id(+) = aauv.organization_id
403 ' || where_clause || '
404 AND acv.certification_period_name = agpv.period_name
405 AND acv.certification_period_set_name = agpv.period_set_name
406 ';
407 ***/
408 /**** 12.08.2005 npanandi: ends commenting of Divyesh's SQL query above ***/
409
410
411 /*** 12.08.2005 npanandi: modified SQL query for report generation ***/
412 l_sqlstmt := 'SELECT DISTINCT aauv.organization_id RCI_DRILLDOWN_PARAM1
413 ,aauv.NAME RCI_PROC_CERT_MEASURE1
414 ,aauv.company RCI_PROC_CERT_MEASURE2
415 ,aauv.lob_description RCI_PROC_CERT_MEASURE3
416 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_risk_count(aauv.organization_id) RCI_PROC_CERT_MEASURE4
417 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_control_count(aauv.organization_id) RCI_PROC_CERT_MEASURE5
418 ,'' '' RCI_PROC_CERT_MEASURE6
419 ,acv.certification_name RCI_PROC_CERT_MEASURE7
420 ,certres.value RCI_PROC_CERT_MEASURE8
421 ,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE9
422 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE10
426 FROM amw_audit_units_v aauv
423 ,evalopinion.eval_result RCI_PROC_CERT_MEASURE11
424 ,evalopinion.eval_by RCI_PROC_CERT_MEASURE12
425 ,trunc(evalopinion.eval_date) RCI_PROC_CERT_MEASURE13
427 ,rci_org_cert_summ_f rocsf
428 ,amw_certification_vl acv
429 ,rci_bp_cert_result_v certres
430 ,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by
431 from amw_opinions_v aov1
432 where aov1.opinion_type_code(+) = ''EVALUATION''
433 and aov1.object_name(+) = ''AMW_ORGANIZATION''
434 and aov1.authored_date = (select max(aov2.authored_date)
435 from amw_opinions_v aov2
436 where aov2.opinion_type_code(+) = ''EVALUATION''
437 and aov2.object_name(+) = ''AMW_ORGANIZATION''
438 and aov1.pk1_value=aov2.pk1_value)) evalopinion
439 ,fii_time_day ftd
440 WHERE aauv.organization_id = rocsf.organization_id
441 /***01.10.2006 npanandi: changed below to join to rocsf
442 fin_certification_id instead of previous certification_id ***/
443 AND acv.certification_id = rocsf.fin_certification_id
444 /**01.11.2006 npanandi: changed below to handle bug in populating
445 cert_opinion_log_id column in amw_org_eval_sum tbl ***/
446 /***AND certres.id = nvl(rocsf.org_certification_status,''NOT_CERTIFIED'')***/
447 AND certres.id = decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'')
448 AND evalopinion.org_id(+) = aauv.organization_id
449 and rocsf.report_date_julian=ftd.report_date_julian';
450 /*** 12.08.2005 npanandi: ends modified SQL query for report generation ***/
451
452 /*** 01.05.2006 npanandi: added following modification for sorting **/
453 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
454 l_act_sqlstmt := 'select RCI_DRILLDOWN_PARAM1,RCI_PROC_CERT_MEASURE1,RCI_PROC_CERT_MEASURE2
455 ,RCI_PROC_CERT_MEASURE3,RCI_PROC_CERT_MEASURE4
456 ,RCI_PROC_CERT_MEASURE5,RCI_PROC_CERT_MEASURE6
457 ,RCI_PROC_CERT_MEASURE7,RCI_PROC_CERT_MEASURE8
458 ,RCI_PROC_CERT_MEASURE9,RCI_PROC_CERT_MEASURE10
459 ,RCI_PROC_CERT_MEASURE11,RCI_PROC_CERT_MEASURE12,RCI_PROC_CERT_MEASURE13
460 from (select t.*
461 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
462 from ( '||l_sqlstmt || where_clause||'
463 ) t ) a
464 order by a.col_rank ';
465
466
467 p_exp_source_sql := l_act_sqlstmt;
468 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
469 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
470
471 p_exp_source_output.EXTEND;
472 l_bind_rec.attribute_name := ':TIME';
473 l_bind_rec.attribute_value := v_period;
474 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
475 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
476 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
477 END get_org_details;
478
479 -- used in get_org_def_details
480 FUNCTION get_unmiti_risks(p_cert_id number, p_org_id number) return number is
481 v_cnt number;
482 BEGIN
483 SELECT
484 count(1) into v_cnt
485 FROM (SELECT DISTINCT aca.pk1 cert_id, aca.pk2 org_id, aca.control_id
486 FROM amw_control_associations aca,amw_opinions_v aov
487 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
488 AND aca.pk1 = p_cert_id
489 AND aca.pk2 = p_org_id
490 AND aov.object_name = 'AMW_ORG_CONTROL'
491 AND aov.opinion_type_code = 'EVALUATION'
492 AND aov.pk1_value = aca.control_id
493 AND aov.pk3_value = aca.pk2
494 AND aov.audit_result_code <> 'EFFECTIVE'
495 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
496 FROM amw_opinions aov2
497 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
498 AND aov2.pk3_value = aov.pk3_value
499 AND aov2.pk1_value = aov.pk1_value)
500 );
501
502 return v_cnt;
503 END get_unmiti_risks;
504
505 -- used in get_org_def_details
506 FUNCTION get_ineff_ctrls(p_cert_id number, p_org_id number) return number is
507 v_cnt number;
508 BEGIN
509 SELECT count(1) into v_cnt
510 FROM (SELECT DISTINCT aca.pk1 cert_id, aca.pk2 org_id, aca.control_id
511 FROM amw_control_associations aca,amw_opinions_v aov
512 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
513 AND aca.pk1 = p_cert_id
514 AND aca.pk2 = p_org_id
515 AND aov.object_name = 'AMW_ORG_CONTROL'
516 AND aov.opinion_type_code = 'EVALUATION'
517 AND aov.pk1_value = aca.control_id
518 AND aov.pk3_value = aca.pk2
519 AND aov.audit_result_code <> 'EFFECTIVE'
520 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
521 FROM amw_opinions aov2
522 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
523 AND aov2.pk3_value = aov.pk3_value
524 AND aov2.pk1_value = aov.pk1_value)
525 );
526 return v_cnt;
530 FUNCTION get_ineff_procs(p_cert_id number, p_org_id number) return number is
527 END get_ineff_ctrls;
528
529 -- used in get_org_def_details
531 v_cnt number;
532 BEGIN
533 SELECT
534 count(DISTINCT amw_exec.process_id) into v_cnt
535 FROM
536 amw_execution_scope amw_exec
537 WHERE
538 amw_exec.entity_type = 'BUSIPROC_CERTIFICATION'
539 AND amw_exec.entity_id = p_cert_id
540 AND EXISTS (SELECT opinion.opinion_id
541 FROM amw_opinions_v opinion
542 WHERE opinion.pk1_value = amw_exec.process_id
543 AND opinion.pk3_value = p_org_id
544 AND opinion.opinion_type_code = 'EVALUATION'
545 AND opinion.object_name = 'AMW_ORG_PROCESS'
546 AND opinion.audit_result_code <> 'EFFECTIVE'
547 AND opinion.authored_date = (SELECT MAX(aov2.authored_date)
548 FROM amw_opinions aov2
549 WHERE aov2.object_opinion_type_id = opinion.object_opinion_type_id
550 AND aov2.pk3_value = opinion.pk3_value
551 AND aov2.pk1_value = opinion.pk1_value)
552 );
553 return v_cnt;
554 END get_ineff_procs;
555
556 PROCEDURE get_org_def_details(
557 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
558 ,p_exp_source_sql out NOCOPY VARCHAR2
559 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
560 is
561 l_sqlstmt VARCHAR2(15000);
562 counter NUMBER := 0;
563 where_clause VARCHAR2(2000) := '';
564 v_period varchar2(10);
565 l_bind_rec BIS_QUERY_ATTRIBUTES;
566 BEGIN
567 counter := p_page_parameter_tbl.COUNT;
568 FOR i IN 1..counter LOOP
569 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
570 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
571 where_clause := where_clause || ' AND rocsf.organization_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
572 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
573 where_clause := where_clause || ' AND rocsf.process_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
574 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
575 where_clause := where_clause || ' AND rocsf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
576 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
577 where_clause := where_clause || ' AND rocsf.certification_status='|| p_page_parameter_tbl(i).parameter_id ||' ';
578 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
579 where_clause := where_clause || ' AND rocsf.certification_type='|| p_page_parameter_tbl(i).parameter_id ||' ';
580 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_ORG_CERT_RESULT' THEN
581 if(p_page_parameter_tbl(i).parameter_id = '''EFFECTIVE''')then
582 where_clause := where_clause || ' and rocsf.org_certification_status=''EFFECTIVE'' ';
583 elsif(p_page_parameter_tbl(i).parameter_id = '''INEFFECTIVE''') then
584 where_clause := where_clause || ' and rocsf.org_certification_status=''INEFFECTIVE'' ';
585 else
586 where_clause := where_clause || ' and rocsf.org_certification_status IS NULL ';
587 end if;
588 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
589 where_clause := where_clause || ' AND rocsf.natural_account_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
590 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
591 v_period := p_page_parameter_tbl(i).parameter_id;
592 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
593 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
594 v_period := p_page_parameter_tbl(i).parameter_id;
595 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
596 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
597 v_period := p_page_parameter_tbl(i).parameter_id;
598 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
599 END IF;
600 END IF; -- parameter_id IS NOT NULL
601 END LOOP;
602
603 l_sqlstmt :=
604 '
605 SELECT
606 DISTINCT aauv.organization_id RCI_DRILLDOWN_PARAM1
607 ,aauv.NAME RCI_PROC_CERT_MEASURE1
608 ,aauv.company RCI_PROC_CERT_MEASURE2
609 ,aauv.lob_description RCI_PROC_CERT_MEASURE3
610 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_unmiti_risks(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE4
611 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_ineff_ctrls(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE5
612 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_ineff_procs(acv.certification_id,aauv.organization_id) RCI_PROC_CERT_MEASURE6
613 ,'' '' RCI_PROC_CERT_MEASURE7
614 ,acv.certification_name RCI_PROC_CERT_MEASURE8
615 ,certres.value RCI_PROC_CERT_MEASURE9
616 ,trunc(rocsf.ORG_CERTIFIED_ON) RCI_PROC_CERT_MEASURE10
617 ,RCI_SIG_ACCT_EVAL_SUMM_PKG.get_latest_engagement(aauv.organization_id) RCI_PROC_CERT_MEASURE11
618 ,evalopinion.eval_result RCI_PROC_CERT_MEASURE12
619 ,evalopinion.eval_by RCI_PROC_CERT_MEASURE13
620 ,trunc(evalopinion.eval_date) RCI_PROC_CERT_MEASURE14
621 FROM amw_audit_units_v aauv
625 ,(select audit_result eval_result,authored_date eval_date,pk1_value org_id,pk2_value project_id,author eval_by
622 ,rci_org_cert_summ_f rocsf
623 ,amw_certification_vl acv
624 ,rci_bp_cert_result_v certres
626 from amw_opinions_v aov1
627 where aov1.opinion_type_code(+) = ''EVALUATION''
628 and aov1.object_name(+) = ''AMW_ORGANIZATION''
629 and aov1.authored_date = (select max(aov2.authored_date)
630 from amw_opinions_v aov2
631 where aov2.opinion_type_code(+) = ''EVALUATION''
632 and aov2.object_name(+) = ''AMW_ORGANIZATION''
633 and aov1.pk1_value=aov2.pk1_value)) evalopinion
634 ,fii_time_day ftd
635 WHERE aauv.organization_id = rocsf.organization_id
636 AND acv.certification_id = rocsf.fin_certification_id
637 AND certres.id = decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'')
638 AND evalopinion.org_id(+) = aauv.organization_id
639 AND rocsf.report_date_julian=ftd.report_date_julian
640 AND rocsf.ineffective_controls > 0
641 '||where_clause;
642
643 p_exp_source_sql := l_sqlstmt;
644 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
645 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
646
647 p_exp_source_output.EXTEND;
648 l_bind_rec.attribute_name := ':TIME';
649 l_bind_rec.attribute_value := v_period;
650 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
651 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
652 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
653
654 END get_org_def_details;
655
656 PROCEDURE get_sig_acct_details(
657 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
658 ,p_exp_source_sql out NOCOPY VARCHAR2
659 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
660 is
661 l_sqlstmt VARCHAR2(15000);
662 counter NUMBER := 0;
663 where_clause VARCHAR2(2000) := '';
664 v_period varchar2(10);
665 l_bind_rec BIS_QUERY_ATTRIBUTES;
666 BEGIN
667 counter := p_page_parameter_tbl.COUNT;
668 FOR i IN 1..counter LOOP
669 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
670 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
671 where_clause := where_clause || ' AND accteval.organization_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
672 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
673 where_clause := where_clause || ' AND accteval.process_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
674 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
675 where_clause := where_clause || ' AND accteval.certification_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
676 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
677 /* 12.16.2005 npanandi: changed below because 3 quotes gives an error -- bug 4893008 fix **/
678 where_clause := where_clause || ' AND accteval.cert_status='|| p_page_parameter_tbl(i).parameter_id;
679 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
680 /* 12.16.2005 npanandi: changed below because cert_type is a number
681 bug 4893008 fix **/
682 where_clause := where_clause || ' AND accteval.cert_type='|| p_page_parameter_tbl(i).parameter_id;
683 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' THEN
684 if p_page_parameter_tbl(i).parameter_id = 'NOT_EVALUATED' then
685 where_clause := where_clause || ' AND accteval.acct_eval_result_code is null ';
686 else
687 /* 12.16.2005 npanandi: changed below because 3 quotes gives an error -- bug 4893008 fix **/
688 where_clause := where_clause || ' AND accteval.acct_eval_result_code='|| p_page_parameter_tbl(i).parameter_id ||' ';
689 end if;
690 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
691 where_clause := where_clause || ' AND accteval.sig_acct_id='|| p_page_parameter_tbl(i).parameter_id ||' ';
692 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
693 v_period := p_page_parameter_tbl(i).parameter_id;
694 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
695 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
696 v_period := p_page_parameter_tbl(i).parameter_id;
697 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
698 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
699 v_period := p_page_parameter_tbl(i).parameter_id;
700 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
701 END IF;
702 END IF; -- parameter_id IS NOT NULL
703 END LOOP;
704 --add (+) on the first two lines of where clause
705 l_sqlstmt :=
706 '
707 SELECT DISTINCT
708 accteval.certification_id RCI_DRILLDOWN_PARAM1
709 ,accteval.sig_acct_id RCI_DRILLDOWN_PARAM2
710 ,afka.account_name RCI_PROC_CERT_MEASURE1
711 ,nvl(afces.proc_pending_certification,0) RCI_PROC_CERT_MEASURE2
712 ,nvl(afces.proc_with_ineffective_controls,0) RCI_PROC_CERT_MEASURE3
713 ,nvl(afces.unmitigated_risks,0) RCI_PROC_CERT_MEASURE4
714 ,nvl(afces.ineffective_controls,0) RCI_PROC_CERT_MEASURE5
715 ,acv.certification_name RCI_PROC_CERT_MEASURE6
716 ,rfaev.value RCI_PROC_CERT_MEASURE7
720 rci_sig_acct_eval_f accteval
717 ,o.author RCI_PROC_CERT_MEASURE8
718 ,trunc(o.authored_date) RCI_PROC_CERT_MEASURE9
719 FROM
721 ,fii_time_day ftd
722 ,amw_fin_cert_eval_sum afces
723 ,amw_fin_key_accounts_vl afka
724 ,(select distinct authored_date,pk1_value,pk2_value,author
725 from amw_opinions_v aov1
726 where aov1.opinion_type_code(+) = ''EVALUATION''
727 and aov1.object_name(+) = ''AMW_KEY_ACCOUNT''
728 and aov1.authored_date = (select max(aov2.authored_date)
729 from amw_opinions_v aov2
730 where aov2.opinion_type_code(+) = ''EVALUATION''
731 and aov2.object_name(+) = ''AMW_KEY_ACCOUNT''
732 and aov1.pk1_value=aov2.pk1_value
733 and aov1.pk2_value=aov2.pk2_value)) o
734 ,amw_certification_vl acv
735 ,RCI_FS_ACCT_EVAL_V rfaev
736 WHERE
737 afces.fin_certification_id(+) = accteval.certification_id
738 AND accteval.report_date_julian = ftd.report_date_julian
739 AND afces.natural_account_id(+) = accteval.sig_acct_id
740 AND afces.financial_Statement_id(+) = accteval.financial_Statement_id
741 AND afces.financial_item_id(+) = accteval.financial_item_id
742 AND rfaev.id = accteval.acct_eval_result_code
743 AND o.pk1_value(+) = accteval.sig_acct_id
744 AND o.pk2_value (+)= accteval.certification_id
745 AND rfaev.obj_name=''AMW_KEY_ACCOUNT''
746 AND acv.certification_id = accteval.certification_id
747 AND afka.natural_account_id = accteval.sig_acct_id
748 ' || where_clause;
749
750 p_exp_source_sql := l_sqlstmt;
751 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
752 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
753
754 p_exp_source_output.EXTEND;
755 l_bind_rec.attribute_name := ':TIME';
756 l_bind_rec.attribute_value := v_period;
757 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
758 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
759 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
760
761 END get_sig_acct_details;
762
763 PROCEDURE get_sig_acct_eval_result(
764 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
765 ,p_exp_source_sql out NOCOPY VARCHAR2
766 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
767 is
768 l_sqlstmt VARCHAR2(15000);
769 l_act_sqlstmt VARCHAR2(15000);
770 counter NUMBER := 0;
771 where_clause VARCHAR2(2000) := ' ';
772 total_qry VARCHAR2(15000);
773 v_total number;
774 v_period varchar2(10);
775 l_bind_rec BIS_QUERY_ATTRIBUTES;
776 BEGIN
777 counter := p_page_parameter_tbl.COUNT;
778 FOR i IN 1..counter LOOP
779 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
780 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
781 where_clause := where_clause || ' AND accteval.organization_id='|| p_page_parameter_tbl(i).parameter_id ;
782 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
783 where_clause := where_clause || ' AND accteval.process_id='|| p_page_parameter_tbl(i).parameter_id ;
784 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
785 where_clause := where_clause || ' AND accteval.certification_id='|| p_page_parameter_tbl(i).parameter_id ;
786 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
787 /* 12.16.2005 npanandi: changed the way cert_status was in the where clause
788 because 3 quotes gives error --> bug 4893008 fix **/
789 where_clause := where_clause || ' AND accteval.cert_status='|| p_page_parameter_tbl(i).parameter_id;
790 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
791 /* 12.16.2005 npanandi: changed the way cert_type was in the where clause
792 bug 4893008 fix **/
793 /* where_clause := where_clause || ' AND accteval.cert_type='''|| p_page_parameter_tbl(i).parameter_id ||''' '; */
794 where_clause := where_clause || ' AND accteval.cert_type='|| p_page_parameter_tbl(i).parameter_id;
795 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' THEN
796 where_clause := where_clause || ' AND accteval.acct_eval_result_code='|| p_page_parameter_tbl(i).parameter_id ||' ';
797 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
798 where_clause := where_clause || ' AND accteval.sig_acct_id='|| p_page_parameter_tbl(i).parameter_id;
799 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
800 v_period := p_page_parameter_tbl(i).parameter_id;
801 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
802 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
803 v_period := p_page_parameter_tbl(i).parameter_id;
804 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
805 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
806 v_period := p_page_parameter_tbl(i).parameter_id;
807 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
808 END IF;
809 END IF; -- parameter_id IS NOT NULL
810 END LOOP;
811
812 total_qry := 'SELECT COUNT(1) FROM (
813 select
814 sig_acct_id
815 ,acct_eval_result_code
816 ,certification_id
820 ,proc_cert_result_cwi
817 ,acct_eval_e
818 ,acct_eval_ne
819 ,acct_eval_ie
821 ,proc_cert_result_c
822 ,proc_cert_result_nc
823 ,orgs_with_ineff_ctrls
824 ,unmiti_risks
825 ,ineff_ctrls
826 from
827 rci_sig_acct_eval_f accteval
828 ,fii_time_day ftd
829 where
830 accteval.report_date_julian = ftd.report_date_julian
831 '|| where_clause || '
832 group by
833 sig_acct_id
834 ,acct_eval_result_code
835 ,certification_id
836 ,acct_eval_e
837 ,acct_eval_ne
838 ,acct_eval_ie
839 ,proc_cert_result_cwi
840 ,proc_cert_result_c
841 ,proc_cert_result_nc
842 ,orgs_with_ineff_ctrls
843 ,unmiti_risks
844 ,ineff_ctrls )' ;
845 EXECUTE IMMEDIATE total_qry INTO v_total using v_period;
846
847 IF v_total=0 THEN
848 v_total := 1;
849 END IF;
850
851 l_sqlstmt :=
852 '
853 SELECT
854 value VIEWBY,
855 nvl(RCI_SIG_ACCT_EVAL_SUMM_DIM1,0) RCI_SIG_ACCT_EVAL_SUMM_DIM1,
856 nvl(RCI_SIG_ACCT_EVAL_SUMM_DIM2,0) RCI_SIG_ACCT_EVAL_SUMM_DIM2,
857 0 RCI_SIG_ACCT_EVAL_SUMM_DIM3,
858 rci_fs_acct_eval_v.id RCI_DRILLDOWN_PARAM1
859 FROM
860 (SELECT
861 acct_eval_result_code,
862 count(acct_eval_result_code) RCI_SIG_ACCT_EVAL_SUMM_DIM1,
863 ROUND(COUNT(acct_eval_result_code)/'||v_total||'*100,2) RCI_SIG_ACCT_EVAL_SUMM_DIM2
864 FROM
865 (select
866 sig_acct_id
867 ,acct_eval_result_code
868 ,certification_id
869 ,acct_eval_e
870 ,acct_eval_ne
871 ,acct_eval_ie
872 ,proc_cert_result_cwi
873 ,proc_cert_result_c
874 ,proc_cert_result_nc
875 ,orgs_with_ineff_ctrls
876 ,unmiti_risks
877 ,ineff_ctrls
878 from
879 rci_sig_acct_eval_f accteval
880 ,fii_time_day ftd
881 where
882 accteval.report_date_julian = ftd.report_date_julian
883 '|| where_clause || '
884 group by
885 sig_acct_id
886 ,acct_eval_result_code
887 ,certification_id
888 ,acct_eval_e
889 ,acct_eval_ne
890 ,acct_eval_ie
891 ,proc_cert_result_cwi
892 ,proc_cert_result_c
893 ,proc_cert_result_nc
894 ,orgs_with_ineff_ctrls
895 ,unmiti_risks
896 ,ineff_ctrls) accteval
897 GROUP BY
898 acct_eval_result_code) rsae
899 ,(select id,value from rci_fs_acct_eval_v where obj_name=''AMW_KEY_ACCOUNT'') rci_fs_acct_eval_v
900 WHERE
901 id = acct_eval_result_code(+)
902 ';
903
904 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
905 l_act_sqlstmt := 'select VIEWBY,RCI_SIG_ACCT_EVAL_SUMM_DIM1,RCI_SIG_ACCT_EVAL_SUMM_DIM2
906 ,RCI_SIG_ACCT_EVAL_SUMM_DIM3,RCI_DRILLDOWN_PARAM1
907 from (select t.*
908 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
909 from ( '||l_sqlstmt||'
910 ) t ) a
911 order by a.col_rank ';
912
913 p_exp_source_sql := l_act_sqlstmt;
914 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
915 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
916
917 p_exp_source_output.EXTEND;
918 l_bind_rec.attribute_name := ':TIME';
919 l_bind_rec.attribute_value := v_period;
920 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
921 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
922 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
923
924 END get_sig_acct_eval_result;
925
926 PROCEDURE get_sig_acct_eval_summ_result(
927 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
928 ,p_exp_source_sql out NOCOPY VARCHAR2
929 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
930 is
931 l_sqlstmt VARCHAR2(15000);
932 l_act_sqlstmt varchar2(15000);
933 counter NUMBER := 0;
934 where_clause VARCHAR2(2000) := ' ';
935 where_clause1 VARCHAR2(2000) := ' ';
936 v_period varchar2(10);
937 l_bind_rec BIS_QUERY_ATTRIBUTES;
938 BEGIN
939 counter := p_page_parameter_tbl.COUNT;
940 FOR i IN 1..counter LOOP
941 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
942 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
943 where_clause := where_clause || ' AND accteval.organization_id='|| p_page_parameter_tbl(i).parameter_id;
944 where_clause1 := where_clause1 || ' AND fa_tab.organization_id='|| p_page_parameter_tbl(i).parameter_id;
945 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
946 where_clause := where_clause || ' AND accteval.process_id='|| p_page_parameter_tbl(i).parameter_id ;
947 where_clause1 := where_clause1 || ' AND fa_tab.process_id='|| p_page_parameter_tbl(i).parameter_id;
948 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
949 where_clause := where_clause || ' AND accteval.certification_id='|| p_page_parameter_tbl(i).parameter_id ;
950 where_clause1 := where_clause1 || ' AND fa_tab.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id;
954 where_clause1 := where_clause1 || ' AND fa_tab.certification_status='|| p_page_parameter_tbl(i).parameter_id;
951 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
952 /* 12.16.2005 npanandi: 3 quotes give error bug 4893008 fix **/
953 where_clause := where_clause || ' AND accteval.cert_status='|| p_page_parameter_tbl(i).parameter_id;
955 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
956 /* 12.16.2005 npanandi: changed the way cert_type was in the where clause
957 bug 4893008 fix **/
958 where_clause := where_clause || ' AND accteval.cert_type='|| p_page_parameter_tbl(i).parameter_id;
959 where_clause1 := where_clause1 || ' AND fa_tab.certification_type='|| p_page_parameter_tbl(i).parameter_id;
960 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' THEN
961 IF p_page_parameter_tbl(i).parameter_id = 'EFFECTIVE' THEN
962 where_clause := where_clause || ' AND accteval.acct_eval_e=1';
963 ELSIF p_page_parameter_tbl(i).parameter_id = 'NOT_EVALUATED' THEN
964 where_clause := where_clause || ' AND accteval.acct_eval_ne=1';
965 ELSE
966 /* 12.16.2005 npanandi: 3 quotes give error bug 4893008 fix **/
967 where_clause := where_clause || ' AND accteval.acct_eval_result_code='|| p_page_parameter_tbl(i).parameter_id;
968 END IF;
969 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' THEN
970 where_clause := where_clause || ' AND accteval.sig_acct_id='|| p_page_parameter_tbl(i).parameter_id ;
971 where_clause1 := where_clause1 || ' AND fa_tab.natural_account_id='|| p_page_parameter_tbl(i).parameter_id;
972 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
973 v_period := p_page_parameter_tbl(i).parameter_id;
974 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
975 where_clause1 := where_clause1 || ' AND ftd.ent_period_id = :TIME ';
976 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
977 v_period := p_page_parameter_tbl(i).parameter_id;
978 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
979 where_clause1 := where_clause1 || ' AND ftd.ent_qtr_id = :TIME ';
980 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
981 v_period := p_page_parameter_tbl(i).parameter_id;
982 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
983 where_clause1 := where_clause1 || ' AND ftd.ent_year_id = :TIME ';
984 END IF;
985 END IF; -- parameter_id IS NOT NULL
986 END LOOP;
987
988 l_sqlstmt :=
989 '
990 SELECT
991 DISTINCT name || '' ('' || one.natural_account_value || '')'' VIEWBY
992 ,0 RCI_GRAND_TOTAL
993 ,sig_acct_id RCI_DRILLDOWN_PARAM1
994 ,RCI_SIG_ACCT_EVAL_SUMM_DIM1 ,RCI_SIG_ACCT_EVAL_SUMM_DIM2 ,RCI_SIG_ACCT_EVAL_SUMM_DIM3
995 ,RCI_SIG_ACCT_EVAL_SUMM_DIM4 ,RCI_SIG_ACCT_EVAL_SUMM_DIM5 ,RCI_SIG_ACCT_EVAL_SUMM_DIM6
996 ,RCI_SIG_ACCT_EVAL_SUMM_DIM7 ,RCI_SIG_ACCT_EVAL_SUMM_DIM8 ,RCI_SIG_ACCT_EVAL_SUMM_DIM9
997 FROM
998 (SELECT DISTINCT natural_account_value, sig_acct_id
999 ,RCI_SIG_ACCT_EVAL_SUMM_DIM1 ,RCI_SIG_ACCT_EVAL_SUMM_DIM2 ,RCI_SIG_ACCT_EVAL_SUMM_DIM3
1000 ,RCI_SIG_ACCT_EVAL_SUMM_DIM4 ,RCI_SIG_ACCT_EVAL_SUMM_DIM5 ,RCI_SIG_ACCT_EVAL_SUMM_DIM6
1001 ,RCI_SIG_ACCT_EVAL_SUMM_DIM7 ,RCI_SIG_ACCT_EVAL_SUMM_DIM8 ,RCI_SIG_ACCT_EVAL_SUMM_DIM9
1002 FROM
1003 (
1004 select
1005 acct_eval.sig_acct_id
1006 ,acct_eval.ie RCI_SIG_ACCT_EVAL_SUMM_DIM1
1007 ,acct_eval.e RCI_SIG_ACCT_EVAL_SUMM_DIM2
1008 ,acct_eval.ne RCI_SIG_ACCT_EVAL_SUMM_DIM3
1009 ,nvl(ineff_org.org_with_ie_ctrls,0) RCI_SIG_ACCT_EVAL_SUMM_DIM4
1010 ,nvl(proc_cert.ie,0) RCI_SIG_ACCT_EVAL_SUMM_DIM5
1011 ,nvl(proc_cert.e,0) RCI_SIG_ACCT_EVAL_SUMM_DIM6
1012 ,nvl(proc_cert.ne,0) RCI_SIG_ACCT_EVAL_SUMM_DIM7
1013 ,nvl(unmiti_risks.risk_cnt,0) RCI_SIG_ACCT_EVAL_SUMM_DIM8
1014 ,nvl(ineff_ctrls.ctrl_cnt,0) RCI_SIG_ACCT_EVAL_SUMM_DIM9
1015 from
1016 (select
1017 sig_acct_id
1018 ,sum(acct_eval_ie) ie
1019 ,sum(acct_eval_e) e
1020 ,sum(acct_eval_ne) ne
1021 from (
1022 select
1023 sig_acct_id
1024 ,acct_eval_result_code
1025 ,certification_id
1026 ,acct_eval_ie
1027 ,acct_eval_e
1028 ,acct_eval_ne
1029 from
1030 rci_sig_acct_eval_f accteval
1031 ,fii_time_day ftd
1032 where
1033 accteval.report_date_julian = ftd.report_date_julian
1034 ' || where_clause || '
1035 group by
1036 sig_acct_id
1037 ,acct_eval_result_code
1038 ,certification_id
1039 ,acct_eval_ie
1040 ,acct_eval_e
1041 ,acct_eval_ne
1042 )
1043 group by
1044 sig_acct_id
1045 ) acct_eval
1046 ,(select
1047 NATURAL_ACCOUNT_ID, count(1) org_with_ie_ctrls
1051 from
1048 from (
1049 select
1050 distinct NATURAL_ACCOUNT_ID, ORGANIZATION_ID, fin_certification_id
1052 rci_org_cert_summ_f fa_tab, fii_time_day ftd
1053 where
1054 fa_tab.report_date_julian = ftd.report_date_julian
1055 and fa_tab.ineffective_controls > 0
1056 ' || where_clause1 || '
1057 )
1058 group by
1059 NATURAL_ACCOUNT_ID
1060 ) ineff_org
1061 ,(select
1062 NATURAL_ACCOUNT_ID, sum(ne) ne, sum(e) e, sum(ie) ie
1063 from(
1064 select
1065 NATURAL_ACCOUNT_ID
1066 ,sum(decode(CERTIFICATION_RESULT_CODE, null,1,0)) ne
1067 ,sum(decode(CERTIFICATION_RESULT_CODE, ''EFFECTIVE'',1,0)) e
1068 ,sum(decode(CERTIFICATION_RESULT_CODE, ''INEFFECTIVE'',1,0)) ie
1069 from
1070 rci_process_detail_f fa_tab,fii_time_day ftd
1071 where
1072 fa_tab.report_date_julian = ftd.report_date_julian
1073 ' || where_clause1 || '
1074 group by
1075 NATURAL_ACCOUNT_ID, CERTIFICATION_RESULT_CODE
1076 )
1077 group by
1078 NATURAL_ACCOUNT_ID
1079 ) proc_cert
1080 ,(select NATURAL_ACCOUNT_ID, count(1) risk_cnt
1081 from (
1082 select
1083 distinct NATURAL_ACCOUNT_ID, RISK_ID, ORGANIZATION_ID, PROCESS_ID
1084 from
1085 rci_org_cert_risks_f fa_tab, fii_time_day ftd
1086 where
1087 fa_tab.report_date_julian = ftd.report_date_julian
1088 and fa_tab.AUDIT_RESULT_CODE <> ''EFFECTIVE''
1089 ' || where_clause1 || '
1090 )
1091 group by
1092 NATURAL_ACCOUNT_ID
1093 ) unmiti_risks
1094 ,(select NATURAL_ACCOUNT_ID, count(1) ctrl_cnt
1095 from (
1096 select
1097 distinct NATURAL_ACCOUNT_ID, CONTROL_ID, ORGANIZATION_ID
1098 from
1099 rci_org_cert_ctrls_f fa_tab, fii_time_day ftd
1100 where
1101 fa_tab.report_date_julian = ftd.report_date_julian
1102 and fa_tab.AUDIT_RESULT_CODE <> ''EFFECTIVE''
1103 ' || where_clause1 || '
1104 )
1105 group by
1106 NATURAL_ACCOUNT_ID
1107 ) ineff_ctrls
1108 where
1109 acct_eval.sig_acct_id = proc_cert.NATURAL_ACCOUNT_ID(+)
1110 and acct_eval.sig_acct_id = ineff_org.NATURAL_ACCOUNT_ID(+)
1111 and acct_eval.sig_acct_id = unmiti_risks.NATURAL_ACCOUNT_ID(+)
1112 and acct_eval.sig_acct_id = ineff_ctrls.NATURAL_ACCOUNT_ID(+)
1113 ) rsae, amw_fin_key_accounts_b accts
1114 where sig_acct_id = natural_account_id
1115 ) one, AMW_FIN_KEY_ACCOUNTS_TL two
1116 WHERE one.sig_acct_id = two.natural_account_id
1117 and two.language=userenv(''LANG'')
1118 ';
1119 -- WHERE one.natural_account_value = two.natural_account_value
1120
1121 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
1122 l_act_sqlstmt := 'select VIEWBY,RCI_GRAND_TOTAL,RCI_DRILLDOWN_PARAM1
1123 ,RCI_SIG_ACCT_EVAL_SUMM_DIM1,RCI_SIG_ACCT_EVAL_SUMM_DIM2,RCI_SIG_ACCT_EVAL_SUMM_DIM3
1124 ,RCI_SIG_ACCT_EVAL_SUMM_DIM4,RCI_SIG_ACCT_EVAL_SUMM_DIM5,RCI_SIG_ACCT_EVAL_SUMM_DIM6
1125 ,RCI_SIG_ACCT_EVAL_SUMM_DIM7,RCI_SIG_ACCT_EVAL_SUMM_DIM8,RCI_SIG_ACCT_EVAL_SUMM_DIM9
1126 from (select t.*
1127 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1128 from ( '||l_sqlstmt||'
1129 ) t ) a
1130 order by a.col_rank ';
1131
1132 p_exp_source_sql := l_act_sqlstmt;
1133 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1134 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1135
1136 p_exp_source_output.EXTEND;
1137 l_bind_rec.attribute_name := ':TIME';
1138 l_bind_rec.attribute_value := v_period;
1139 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1140 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1141 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
1142 END get_sig_acct_eval_summ_result;
1143
1144 /*
1145 --Procedure to be called from concurrent program--
1146 */
1147 PROCEDURE sig_acct_incremental_load(
1148 errbuf IN OUT NOCOPY VARCHAR2
1149 ,retcode IN OUT NOCOPY NUMBER)
1150 IS
1151 BEGIN
1152 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.rci_sig_acct_eval_f');
1153 sig_acct_initial_load(
1154 errbuf => errbuf
1155 ,retcode => retcode);
1156 EXECUTE IMMEDIATE ('COMMIT');
1157 END sig_acct_incremental_load;
1158 /*
1159 --------------------------------------------------------------------------
1160 */
1161 PROCEDURE sig_acct_initial_load(
1162 errbuf IN OUT NOCOPY VARCHAR2
1163 ,retcode IN OUT NOCOPY NUMBER)
1164 IS
1165 /*todo 01/06/2005 remove this
1166 CURSOR c_get_acct_evaluations
1167 IS
1168 SELECT pk1_value, pk2_value, audit_result_code
1172
1169 FROM amw_opinions_v
1170 WHERE opinion_type_code = 'EVALUATION'
1171 AND object_name = 'AMW_KEY_ACCOUNT';
1173 CURSOR c_get_process_certifications
1174 IS
1175 SELECT pk1_value, pk2_value, pk3_value, audit_result_code
1176 FROM amw_opinions_v
1177 WHERE opinion_type_code = 'CERTIFICATION'
1178 AND object_name = 'AMW_ORG_PROCESS';
1179
1180 CURSOR c_stmt_grp is
1181 SELECT DISTINCT STATEMENT_GROUP_ID, FINANCIAL_STATEMENT_ID, FINANCIAL_ITEM_ID
1182 FROM rci_sig_acct_eval_f;
1183
1184 v_certification_result amw_opinion_values_b.opinion_value_code%type;
1185 v_cert_with_issues rci_sig_acct_eval_f.proc_cert_result_cwi%type := 0;
1186 v_certified rci_sig_acct_eval_f.proc_cert_result_c%type := 0;
1187 v_not_certified rci_sig_acct_eval_f.proc_cert_result_nc%type := 0;
1188
1189 v_evaluation_result amw_opinion_values_b.opinion_value_code%type;
1190 v_ineffective rci_sig_acct_eval_f.acct_eval_ie%type := 0;
1191 v_effective rci_sig_acct_eval_f.acct_eval_e%type := 0;
1192 v_not_evaluated rci_sig_acct_eval_f.acct_eval_ne%type := 0;
1193
1194 p_statement_group_id rci_sig_acct_eval_f.statement_group_id%type := 0;
1195 p_financial_statement_id rci_sig_acct_eval_f.financial_statement_id%type := 0;
1196 p_financial_item_id rci_sig_acct_eval_f.financial_item_id%type := 0;
1197
1198 v_orgs_with_ineff_ctrls rci_sig_acct_eval_f.orgs_with_ineff_ctrls%type := 0;
1199 v_unmiti_risks rci_sig_acct_eval_f.unmiti_risks%type := 0;
1200 v_ineff_ctrls rci_sig_acct_eval_f.ineff_ctrls%type := 0;
1201 */
1202 l_user_id NUMBER ;
1203 l_login_id NUMBER ;
1204 l_program_id NUMBER ;
1205 l_program_login_id NUMBER ;
1206 l_program_application_id NUMBER ;
1207 l_request_id NUMBER ;
1208 l_run_date DATE;
1209 --upd_flag BOOLEAN;
1210 BEGIN
1211 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.rci_sig_acct_eval_f');
1212
1213 INSERT INTO RCI_SIG_ACCT_EVAL_F(
1214 STATEMENT_GROUP_ID
1215 ,FINANCIAL_STATEMENT_ID
1216 ,FINANCIAL_ITEM_ID
1217 ,ACCOUNT_GROUP_ID
1218 ,CERT_STATUS
1219 ,CERT_TYPE
1220 ,CERTIFICATION_ID
1221 ,SIG_ACCT_ID
1222 ,ORGANIZATION_ID
1223 ,PROCESS_ID
1224 ,ACCT_EVAL_RESULT_CODE
1225 ,ACCT_EVAL_E
1226 ,ACCT_EVAL_IE
1227 ,ACCT_EVAL_NE
1228 ,ORGS_WITH_INEFF_CTRLS
1229 ,PROC_CERT_RESULT_CWI
1230 ,PROC_CERT_RESULT_C
1231 ,PROC_CERT_RESULT_NC
1232 ,UNMITI_RISKS
1233 ,INEFF_CTRLS
1234 ,PERIOD_YEAR
1235 ,PERIOD_NUM
1236 ,QUARTER_NUM
1237 ,ENT_PERIOD_ID
1238 ,ENT_QTR_ID
1239 ,ENT_YEAR_ID
1240 ,REPORT_DATE_JULIAN
1241 ,CREATED_BY
1242 ,LAST_UPDATE_LOGIN
1243 ,CREATION_DATE
1244 ,LAST_UPDATED_BY
1245 ,LAST_UPDATE_DATE)
1246 SELECT
1247 DISTINCT
1248 a.statement_group_id
1249 ,a.financial_statement_id
1250 ,a.financial_item_id
1251 ,a.account_group_id
1252 ,acb.certification_status CERT_STATUS
1253 ,acb.certification_type CERT_TYPE
1254 ,acb.certification_id
1255 ,a.natural_account_id sig_acct_id
1256 ,a.organization_id
1257 ,a.process_id
1258 ,nvl(o.audit_result_code,'NOT_EVALUATED') ACCT_EVAL_RESULT_CODE
1259 ,decode(o.audit_result_code, 'EFFECTIVE', 1,0) ACCT_EVAL_E
1260 ,case o.audit_result_code when 'INEFFECTIVE' then 1 when 'SOMEWHAT_EFFECTIVE' then 1
1261 when 'NEARLY_INEFFECTIVE' then 1 else 0 end ACCT_EVAL_IE
1262 ,decode(o.audit_result_code, null, 1,0) ACCT_EVAL_NE
1263 ,nvl(afces.org_with_ineffective_controls,0) ORGS_WITH_INEFF_CTRLS
1264 ,nvl(afces.proc_certified_with_issues,0) PROC_CERT_RESULT_CWI
1265 ,nvl(afces.procs_for_cert_done,0) PROC_CERT_RESULT_C
1266 ,nvl(afces.proc_pending_certification,0) PROC_CERT_RESULT_NC
1267 ,nvl(afces.unmitigated_risks,0) UNMITI_RISKS
1268 ,nvl(afces.ineffective_controls,0) INEFF_CTRLS
1269 ,agpv.period_year
1270 ,agpv.period_num
1271 ,agpv.quarter_num
1272 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) ENT_PERIOD_ID
1273 ,to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) ENT_QTR_ID
1274 ,agpv.period_year ENT_YEAR_ID
1275 ,to_number(to_char(agpv.end_date,'J')) REPORT_DATE_JULIAN
1276 ,G_USER_ID,G_LOGIN_ID,sysdate,G_USER_ID,sysdate
1277 FROM
1278 amw_fin_cert_scope a
1279 ,amw_fin_cert_eval_sum afces
1280 ,amw_certification_b acb
1281 ,amw_opinions_v o
1282 ,amw_gl_periods_v agpv
1283 WHERE
1284 a.fin_certification_id = acb.certification_id
1285 and acb.object_type = 'FIN_STMT'
1286 and afces.object_type = 'ACCOUNT'
1287 and afces.natural_account_id = a.natural_account_id
1288 and afces.fin_certification_id = a.fin_certification_id
1289 and afces.financial_statement_id(+) = a.financial_statement_id
1290 and afces.financial_item_id(+) = a.financial_item_id
1291 and o.pk1_value(+) = a.natural_account_id
1292 and o.pk2_value (+)= a.fin_certification_id
1293 and o.opinion_type_code(+) = 'EVALUATION'
1294 and o.object_name(+) = 'AMW_KEY_ACCOUNT'
1298 ;
1295 and acb.certification_period_name = agpv.period_name
1296 and acb.certification_period_set_name = agpv.period_set_name
1297 -- and a.natural_account_id is not null
1299 /*todo 01/06/2005 remove this
1300 FOR r_get_acct_evaluations IN c_get_acct_evaluations LOOP
1301 upd_flag := false;
1302 v_not_evaluated := 0; v_effective := 0; v_ineffective := 0;
1303 v_evaluation_result := r_get_acct_evaluations.audit_result_code;
1304
1305 IF v_evaluation_result='EFFECTIVE' THEN
1306 upd_flag := true;
1307 v_effective := 1;
1308 ELSE --INEFFECTIVE, NEARLY_INEFFECTIVE or SOMEWHAT_EFFECTIVE
1309 upd_flag := true;
1310 v_ineffective := 1;
1311 END IF;
1312 IF upd_flag THEN
1313 UPDATE rci_sig_acct_eval_f
1314 SET
1315 ACCT_EVAL_RESULT_CODE = v_evaluation_result,
1316 ACCT_EVAL_IE = v_ineffective,
1317 ACCT_EVAL_E = v_effective,
1321 lAST_UPDATE_LOGIN = G_LOGIN_ID
1318 ACCT_EVAL_NE = v_not_evaluated,
1319 lAST_UPDATE_DATE = SYSDATE,
1320 lAST_UPDATED_BY = G_USER_ID,
1322 WHERE
1323 CERTIFICATION_ID = r_get_acct_evaluations.pk2_value
1324 AND SIG_ACCT_ID = r_get_acct_evaluations.pk1_value;
1325 END IF; --end upd_flag IF
1326 END LOOP;
1327
1328 FOR r_get_process_certifications IN c_get_process_certifications LOOP
1329 upd_flag := false;
1330 v_not_certified := 0; v_certified := 0; v_cert_with_issues := 0;
1331 v_certification_result := r_get_process_certifications.audit_result_code;
1332
1333 IF v_certification_result='INEFFECTIVE' THEN
1334 upd_flag := true;
1335 v_cert_with_issues := 1;
1336 ELSIF v_certification_result='EFFECTIVE' THEN
1337 upd_flag := true;
1338 v_certified := 1;
1339 END IF;
1340
1341 IF upd_flag THEN
1342 UPDATE rci_sig_acct_eval_f
1343 SET
1344 PROC_CERT_RESULT_CWI = v_cert_with_issues,
1345 PROC_CERT_RESULT_C = v_certified,
1346 PROC_CERT_RESULT_NC = v_not_certified,
1347 lAST_UPDATE_DATE = SYSDATE,
1348 lAST_UPDATED_BY = G_USER_ID,
1349 lAST_UPDATE_LOGIN = G_LOGIN_ID
1350 WHERE
1351 PROCESS_ID = r_get_process_certifications.pk1_value
1352 AND CERTIFICATION_ID = r_get_process_certifications.pk2_value
1353 AND ORGANIZATION_ID = r_get_process_certifications.pk3_value;
1354 END IF; --end upd_flag IF
1355 END LOOP;
1356
1360 p_financial_item_id := r_stmt_grp.FINANCIAL_ITEM_ID;
1357 FOR r_stmt_grp IN c_stmt_grp LOOP
1358 p_statement_group_id := r_stmt_grp.STATEMENT_GROUP_ID;
1359 p_financial_statement_id := r_stmt_grp.FINANCIAL_STATEMENT_ID;
1361
1362 CountOrgsIneffCtrl_finitem(p_statement_group_id ,
1363 p_financial_statement_id, p_financial_item_id , v_orgs_with_ineff_ctrls );
1364 CountUnmittigatedRisk_finitem(p_statement_group_id ,
1365 p_financial_statement_id, p_financial_item_id , v_unmiti_risks );
1366 CountIneffectiveCtrls_finitem(p_statement_group_id ,
1367 p_financial_statement_id, p_financial_item_id , v_ineff_ctrls );
1368
1369 UPDATE rci_sig_acct_eval_f
1370 SET
1371 ORGS_WITH_INEFF_CTRLS = v_orgs_with_ineff_ctrls,
1372 UNMITI_RISKS = v_unmiti_risks,
1373 INEFF_CTRLS = v_ineff_ctrls,
1374 lAST_UPDATE_DATE = SYSDATE,
1375 lAST_UPDATED_BY = G_USER_ID,
1376 lAST_UPDATE_LOGIN = G_LOGIN_ID
1377 WHERE STATEMENT_GROUP_ID = p_statement_group_id
1378 AND FINANCIAL_STATEMENT_ID = p_financial_statement_id
1379 AND FINANCIAL_ITEM_ID = p_financial_item_id;
1380
1381 END LOOP;
1382 */
1383 l_user_id := NVL(fnd_global.USER_ID, -1);
1384 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1385 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
1386 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
1387 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
1388 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
1389 l_run_date := sysdate - 5/(24*60);
1390
1391 DELETE FROM rci_dr_inc WHERE fact_name='RCI_SIG_ACCT_EVAL_F';
1392
1393 INSERT INTO rci_dr_inc( fact_name
1394 ,last_run_date
1395 ,created_by
1396 ,creation_date
1397 ,last_update_date
1398 ,last_updated_by
1399 ,last_update_login
1400 ,program_id
1401 ,program_login_id
1402 ,program_application_id
1403 ,request_id ) VALUES (
1404 'RCI_SIG_ACCT_EVAL_F'
1405 ,l_run_date
1406 ,l_user_id
1407 ,sysdate
1408 ,sysdate
1409 ,l_user_id
1410 ,l_login_id
1411 ,l_program_id
1412 ,l_program_login_id
1413 ,l_program_application_id
1414 ,l_request_id );
1415
1416 EXECUTE IMMEDIATE ('COMMIT');
1417 END sig_acct_initial_load;
1418 END RCI_SIG_ACCT_EVAL_SUMM_PKG;