[Home] [Help]
PACKAGE BODY: APPS.RCI_PROC_CERT_SUMM_PKG
Source
1 PACKAGE BODY RCI_PROC_CERT_SUMM_PKG as
2 /*$Header: rciproccertb.pls 120.25.12000000.2 2007/03/14 21:08:33 ddesjard 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 --- the get_proc_certification_result procedure is called by Process Certification Summary report.
15
16 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
17 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
18
19 function get_default_year return varchar2 is
20 begin
21 return to_char(sysdate,'YYYY');
22 end;
23
24 PROCEDURE get_kpi(
25 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
26 ,p_exp_source_sql out NOCOPY VARCHAR2
27 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
28 is
29 l_sqlstmt VARCHAR2(15000);
30 total_qry VARCHAR2(15000);
31 counter NUMBER := 0;
32 where_clause VARCHAR2(2000) := ' 1=1 ';
33 v_total NUMBER;
34
35 l_qry1 varchar2(2000);
36 l_qry2 varchar2(2000);
37 l_qry3 varchar2(2000);
38 --dynamic bind parameters for period
39 v_period varchar2(10);
40 l_bind_rec BIS_QUERY_ATTRIBUTES;
41 BEGIN
42 counter := p_page_parameter_tbl.COUNT;
43 FOR i IN 1..counter LOOP
44 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
45 IF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
46 where_clause := where_clause || ' AND rpdf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id;
47 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
48 where_clause := where_clause || ' AND rpdf.certification_type='|| p_page_parameter_tbl(i).parameter_id;
49 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
50 v_period := p_page_parameter_tbl(i).parameter_id;
51 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
52 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
53 v_period := p_page_parameter_tbl(i).parameter_id;
54 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
55 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
56 v_period := p_page_parameter_tbl(i).parameter_id;
57 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
58 END IF;
59 END IF; -- parameter_id IS NOT NULL
60 END LOOP;
61
62 ----total_qry := 'SELECT COUNT(1) FROM rci_proc_cert_sum_f rpcm WHERE '|| where_clause;
63 total_qry := 'select count(process_id) from (
64 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code
65 ,project_id,fin_certification_id,organization_id,process_id,evaluation_result_code
66 from rci_process_detail_f rpdf, fii_time_day ftd
67 where rpdf.report_date_julian=ftd.report_date_julian and '||where_clause||' ) ';
68
69 EXECUTE IMMEDIATE total_qry INTO v_total using v_period;
70
71 IF v_total=0 THEN
72 v_total := 1;
73 END IF;
74
75 l_qry1 := '(select count(process_id) as proc_not_certified from (
76 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code
77 ,project_id,fin_certification_id,organization_id,process_id,evaluation_result_code
78 from rci_process_detail_f rpdf, fii_time_day ftd
79 where rpdf.report_date_julian=ftd.report_date_julian
80 and certification_result_code is null and '||where_clause||' )) pc1, ';
81
82 l_qry2 := '(select count(process_id) as proc_certified_with_issues from (
83 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code
84 ,project_id,fin_certification_id,organization_id,process_id,evaluation_result_code
85 from rci_process_detail_f rpdf, fii_time_day ftd
86 where rpdf.report_date_julian=ftd.report_date_julian
87 and certification_result_code=''INEFFECTIVE'' and '||where_clause||' )) pc2, ';
88 l_qry3 := '(select count(process_id) as proc_certified from (
89 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code
90 ,project_id,fin_certification_id,organization_id,process_id,evaluation_result_code
91 from rci_process_detail_f rpdf, fii_time_day ftd
92 where rpdf.report_date_julian=ftd.report_date_julian
93 and certification_result_code=''EFFECTIVE'' and '||where_clause||' )) pc3 ';
94
95 l_sqlstmt := 'select ROUND((nvl(pc1.proc_not_certified,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE1,
96 ROUND((nvl(pc2.proc_certified_with_issues,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE2,
97 ROUND((nvl(pc3.proc_certified,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE3
98 from '||l_qry1||l_qry2||l_qry3;
99 /***
100 l_sqlstmt :='SELECT
101 ROUND((nvl(MES1,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE1,
102 ROUND((nvl(MES2,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE2,
103 ROUND((nvl(MES3,0)*100/'||v_total ||'),2) AS RCI_PROC_CERT_MEASURE3
104 FROM (
105 SELECT
106 SUM(CERT_RESULT_NC) MES1,
107 SUM(CERT_RESULT_CWI) MES2,
108 SUM(CERT_RESULT_C) MES3
109 FROM
110 rci_proc_cert_sum_f rpcm WHERE '|| where_clause ||'
111 )
112 ';**/
113
114 p_exp_source_sql := l_sqlstmt;
115 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
116 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
117
118 p_exp_source_output.EXTEND;
119 l_bind_rec.attribute_name := ':TIME';
120 l_bind_rec.attribute_value := v_period;
121 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
122 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
123 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
124
125 END get_kpi;
126
127 PROCEDURE get_proc_cert_details(
128 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
129 ,p_exp_source_sql out NOCOPY VARCHAR2
130 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
131 is
132 l_sqlstmt VARCHAR2(15000);
133 counter NUMBER := 0;
134 where_clause VARCHAR2(2000) := '';
135 v_period varchar2(10);
136 l_bind_rec BIS_QUERY_ATTRIBUTES;
137 BEGIN
138 counter := p_page_parameter_tbl.COUNT;
139 FOR i IN 1..counter LOOP
140 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
141 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
142 where_clause := where_clause || ' AND apov.organization_id='|| p_page_parameter_tbl(i).parameter_id ;
143 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
144 where_clause := where_clause || ' AND rpdf.process_id='|| p_page_parameter_tbl(i).parameter_id ;
145 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
146 where_clause := where_clause || ' AND rpdf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ;
147 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
148 where_clause := where_clause || ' AND rpdf.certification_status='|| p_page_parameter_tbl(i).parameter_id ;
149 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
150 where_clause := where_clause || ' AND rpdf.certification_type='|| p_page_parameter_tbl(i).parameter_id ;
151 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_PROCESS+RCI_BP_CERT_RESULT' THEN
152 if p_page_parameter_tbl(i).parameter_id = '''NOT_CERTIFIED''' then
153 where_clause := where_clause || ' AND rpdf.certification_result_code is null ' ;
154 else
155 where_clause := where_clause || ' AND rpdf.certification_result_code='|| p_page_parameter_tbl(i).parameter_id ;
156 end if;
157 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
158 v_period := p_page_parameter_tbl(i).parameter_id;
159 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
160 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
161 v_period := p_page_parameter_tbl(i).parameter_id;
162 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
163 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
164 v_period := p_page_parameter_tbl(i).parameter_id;
165 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
166 END IF;
167 END IF; -- parameter_id IS NOT NULL
168 END LOOP;
169
170 l_sqlstmt := '
171 SELECT DISTINCT
172 /*rpdf.process_id*/-100 RCI_DRILLDOWN_PARAM1
173 ,/*apov.organization_id*/-100 RCI_DRILLDOWN_PARAM2
174 /*,apov.display_name
175 ,apov.revision_number*/
176 ,/*apov.display_name*/ acv.CERTIFICATION_NAME RCI_PROC_CERT_MEASURE1
177 ,rfctv.value RCI_PROC_CERT_MEASURE2
178 ,/*apv.person_name*/aecv.full_name RCI_PROC_CERT_MEASURE3
179 ,''Q''||agpv.quarter_num RCI_PROC_CERT_MEASURE4
180 ,agpv.period_year RCI_PROC_CERT_MEASURE5
181 ,rfcsv.value RCI_PROC_CERT_MEASURE6
182 ,acv.certification_creation_date RCI_PROC_CERT_MEASURE7
183 ,acv.target_completion_date RCI_PROC_CERT_MEASURE8
184 /*,acv.certification_name RCI_PROC_CERT_MEASURE9*/
185 ,rbcrv.value RCI_PROC_CERT_MEASURE9
186 ,papf.full_name RCI_PROC_CERT_MEASURE10
187 ,rpdf.certified_on RCI_PROC_CERT_MEASURE11
188 ,acv.certification_id RCI_ORG_CERT_URL1
189 FROM
190 rci_process_detail_f rpdf
191 ,amw_process_organization_vl apov
192 /**,amw_people_v apv**/
193 ,amw_certification_vl acv
194 ,RCI_BP_CERT_RESULT_V rbcrv
195 ,RCI_FS_CERT_STATUS_V rfcsv
196 ,RCI_FS_CERT_TYPE_V rfctv
197 ,amw_gl_periods_v agpv
198 ,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
199 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
200 and papf.employee_number is not null) papf
201 ,amw_employees_current_v aecv
202 ,fii_time_day ftd
203 WHERE
204 rpdf.fin_certification_id = acv.certification_id
205 -- rpdf.process_org_rev_id = apov.process_org_rev_id
206 and rpdf.organization_id = apov.organization_id
207 and rpdf.process_id = apov.process_id
208 /**01.26.2006 npanandi: bug 5000369 fix**/
209 /**and rpdf.certification_result_code = rbcrv.id(+)**/
210 and nvl(rpdf.certification_result_code,''NOT_CERTIFIED'') = rbcrv.id(+)
211 and rpdf.certification_status = rfcsv.id(+)
212 and rpdf.certification_type = rfctv.id(+)
213 /*AND acv.certification_owner_id = apv.person_id*/
214 and acv.CERTIFICATION_OWNER_ID = aecv.party_id
215 and rpdf.certified_by_id = papf.user_id(+)
216 and rpdf.certification_period_name = agpv.period_name
217 and rpdf.certification_period_set_name = agpv.period_set_name
218 and rpdf.report_date_julian = ftd.report_date_julian
219 ' || where_clause ;
220
221 p_exp_source_sql := l_sqlstmt;
222
223 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
224 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
225
226 p_exp_source_output.EXTEND;
227 l_bind_rec.attribute_name := ':TIME';
228 l_bind_rec.attribute_value := v_period;
229 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
230 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
231 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
232
233 END get_proc_cert_details;
234
235 PROCEDURE get_proc_cert_result(
236 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
237 ,p_exp_source_sql out NOCOPY VARCHAR2
238 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
239 is
240 l_sqlstmt VARCHAR2(15000);
241 l_act_sqlstmt VARCHAR2(15000);
242 counter NUMBER := 0;
243 where_clause VARCHAR2(2000) := ' 1=1 ';
244 total_qry VARCHAR2(15000);
245 v_total number;
246 v_period varchar2(10);
247 l_bind_rec BIS_QUERY_ATTRIBUTES;
248 BEGIN
249
250 counter := p_page_parameter_tbl.COUNT;
251 FOR i IN 1..counter LOOP
252 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
253 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
254 where_clause := where_clause || ' AND rpdf.organization_id='|| p_page_parameter_tbl(i).parameter_id ;
255 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
256 where_clause := where_clause || ' AND rpdf.process_id='|| p_page_parameter_tbl(i).parameter_id ;
257 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
258 where_clause := where_clause || ' AND rpdf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ;
259 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
260 where_clause := where_clause || ' AND rpdf.certification_status='|| p_page_parameter_tbl(i).parameter_id ;
261 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
262 where_clause := where_clause || ' AND rpdf.certification_type='|| p_page_parameter_tbl(i).parameter_id ;
263 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
264 v_period := p_page_parameter_tbl(i).parameter_id;
265 where_clause := where_clause || ' AND ftd.ent_period_id = :TIME ';
266 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
267 v_period := p_page_parameter_tbl(i).parameter_id;
268 where_clause := where_clause || ' AND ftd.ent_qtr_id = :TIME ';
269 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
270 v_period := p_page_parameter_tbl(i).parameter_id;
271 where_clause := where_clause || ' AND ftd.ent_year_id = :TIME ';
272 END IF;
273 END IF; -- parameter_id IS NOT NULL
274 END LOOP;
278 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code
275
276 ----total_qry := 'SELECT COUNT(1) FROM rci_proc_cert_sum_f rpcm WHERE '|| where_clause;
277 total_qry := 'select count(process_id) from (
279 ,project_id,fin_certification_id,organization_id,process_id,evaluation_result_code
280 from rci_process_detail_f rpdf, fii_time_day ftd
281 where rpdf.report_date_julian=ftd.report_date_julian and '||where_clause||' ) ';
282 EXECUTE IMMEDIATE total_qry INTO v_total using v_period;
283
284 IF v_total=0 THEN
285 v_total := 1;
286 END IF;
287
288 /*l_sqlstmt :='
289 SELECT
290 res.value VIEWBY,
291 nvl(RCI_PROC_CERT_MEASURE1,0) RCI_PROC_CERT_MEASURE1,
292 nvl(RCI_PROC_CERT_MEASURE2,0) RCI_PROC_CERT_MEASURE2,
293 0 RCI_PROC_CERT_MEASURE3,
294 res.id RCI_DRILLDOWN_PARAM1
295 FROM
296 (SELECT cert_result,
297 COUNT(cert_result) RCI_PROC_CERT_MEASURE1,
298 ROUND(COUNT(cert_result)/'||v_total||'*100,2) RCI_PROC_CERT_MEASURE2
299 FROM rci_proc_cert_sum_f rpcm
300 WHERE
301 ' || where_clause ||'
302 GROUP BY cert_result) pcs,
303 rci_bp_cert_result_v res
304 WHERE
305 res.id=pcs.cert_result(+)';*/
306 l_sqlstmt := 'select value VIEWBY
307 ,count(r.process_id) RCI_PROC_CERT_MEASURE1
308 ,round(count(r.process_id)/'||v_total||'*100,2) RCI_PROC_CERT_MEASURE2
309 ,0 RCI_PROC_CERT_MEASURE3
310 ,id RCI_DRILLDOWN_PARAM1
311 from (
312 select distinct nvl(certification_result_code,''NOT_CERTIFIED'') as certification_result_code,project_id,fin_certification_id
313 ,organization_id,process_id,evaluation_result_code
314 from rci_process_detail_f rpdf,
315 fii_time_day ftd
316 where rpdf.report_date_julian=ftd.report_date_julian
317 and '||where_clause||' ) r,
318 rci_bp_cert_result_v rbcrv
319 where rbcrv.id = r.certification_result_code(+)
320 group by value,id ';
321
322 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
323 l_act_sqlstmt := 'select VIEWBY,RCI_PROC_CERT_MEASURE1,RCI_PROC_CERT_MEASURE2
324 ,RCI_PROC_CERT_MEASURE3,RCI_DRILLDOWN_PARAM1
325 from (select t.*
326 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
327 from ( '||l_sqlstmt||'
328 ) t ) a
329 order by a.col_rank ';
330
331 p_exp_source_sql := l_act_sqlstmt;
332 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
333 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
334
335 p_exp_source_output.EXTEND;
336 l_bind_rec.attribute_name := ':TIME';
337 l_bind_rec.attribute_value := v_period;
338 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
339 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
340 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
341
342 END get_proc_cert_result;
343
344 PROCEDURE get_proc_cert_summary(
345 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
346 ,p_exp_source_sql out NOCOPY VARCHAR2
347 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
348 is
349 l_sqlstmt VARCHAR2(15000);
350 l_act_sqlstmt varchar2(15000);
351 counter NUMBER := 0;
352 where_clause1 VARCHAR2(2000) := ' ';
353 where_clause VARCHAR2(2000) := ' 1=1 ';
354 inner_where_clause varchar2(2000) := ' 1=1 ';
355 l_issues_where varchar2(2000) := ' 1=1 ';
356 l_ineffctrls_frm varchar2(2000) := ' 1=1 ';
357 l_rf_where varchar2(2000) := ' 1=1 ';
358
359 v_yyyymm varchar2(6);
360 v_period varchar2(10);
361 l_bind_rec BIS_QUERY_ATTRIBUTES;
362 BEGIN
363 counter := p_page_parameter_tbl.COUNT;
364 FOR i IN 1..counter LOOP
365 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
366 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
367 where_clause1 := where_clause1 || ' AND rpcm.ORGANIZATION_ID='|| p_page_parameter_tbl(i).parameter_id ;
368 where_clause := where_clause || ' AND rocsf.organization_id='|| p_page_parameter_tbl(i).parameter_id ;
369 inner_where_clause := inner_where_clause || ' AND organization_id = '||p_page_parameter_tbl(i).parameter_id;
370 l_issues_where := l_issues_where || ' and roif.organization_id = '||p_page_parameter_tbl(i).parameter_id;
371 l_ineffctrls_frm := l_ineffctrls_frm || ' and aca.pk2 = '||p_page_parameter_tbl(i).parameter_id;
372 l_rf_where := l_rf_where || ' and rf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
373 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
374 where_clause1 := where_clause1 || ' AND rpcm.PROCESS_ID='|| p_page_parameter_tbl(i).parameter_id ;
375 where_clause := where_clause || ' AND rocsf.process_id='|| p_page_parameter_tbl(i).parameter_id ;
376 inner_where_clause := inner_where_clause || ' AND process_id = '||p_page_parameter_tbl(i).parameter_id;
377 l_issues_where := l_issues_where || ' and roif.process_id = '||p_page_parameter_tbl(i).parameter_id;
378 l_ineffctrls_frm := l_ineffctrls_frm || ' and aca.pk3 = '||p_page_parameter_tbl(i).parameter_id;
382 where_clause := where_clause || ' AND rocsf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ;
379 l_rf_where := l_rf_where || ' AND rf.process_id='|| p_page_parameter_tbl(i).parameter_id ;
380 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
381 where_clause1 := where_clause1 || ' AND rpcm.CERTIFICATION_ID='|| p_page_parameter_tbl(i).parameter_id ;
383 l_issues_where := l_issues_where || ' and roif.fin_cert_id = '||p_page_parameter_tbl(i).parameter_id;
384 l_ineffctrls_frm := l_ineffctrls_frm || ' and aca.pk1 = '||p_page_parameter_tbl(i).parameter_id;
385 l_rf_where := l_rf_where || ' AND rf.fin_certification_id='|| p_page_parameter_tbl(i).parameter_id ;
386 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' THEN
387 where_clause1 := where_clause1 || ' AND rpcm.CERT_STATUS='|| p_page_parameter_tbl(i).parameter_id ;
388 where_clause := where_clause || ' AND rocsf.certification_status='|| p_page_parameter_tbl(i).parameter_id ;
389 inner_where_clause := inner_where_clause || ' AND certification_status = '||p_page_parameter_tbl(i).parameter_id;
390 l_issues_where := l_issues_where || ' and fin_cert_status = '||p_page_parameter_tbl(i).parameter_id;
391 l_rf_where := l_rf_where || ' AND rf.certification_status='|| p_page_parameter_tbl(i).parameter_id ;
392 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
393 where_clause1 := where_clause1 || ' AND rpcm.CERT_TYPE='|| p_page_parameter_tbl(i).parameter_id ;
394 where_clause := where_clause || ' AND rocsf.certification_type='|| p_page_parameter_tbl(i).parameter_id ;
395 inner_where_clause := inner_where_clause || ' AND certification_type = '||p_page_parameter_tbl(i).parameter_id;
396 l_issues_where := l_issues_where || ' and fin_cert_type = '||p_page_parameter_tbl(i).parameter_id;
397 l_rf_where := l_rf_where || ' AND rf.certification_type='|| p_page_parameter_tbl(i).parameter_id ;
398 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_PROCESS+RCI_BP_CERT_RESULT' THEN
399 where_clause1 := where_clause1 || ' AND rpcm.CERT_RESULT='|| p_page_parameter_tbl(i).parameter_id ;
400 IF p_page_parameter_tbl(i).parameter_id = '''EFFECTIVE''' THEN
401 --where_clause := where_clause || ' AND rocsf.certification_result_code = ''EFFECTIVE'' ';
402 l_rf_where := l_rf_where || ' AND rf2.proc_certified > 0 ';
403 --where_clause := where_clause || ' AND rf.proc_certified>0';
404 ELSIF p_page_parameter_tbl(i).parameter_id = '''INEFFECTIVE''' THEN
405 --where_clause := where_clause || ' AND rocsf.certification_result_code = ''INEFFECTIVE''';
406 l_rf_where := l_rf_where || ' AND rocsf.proc_certified_with_issues > 0 ';
407 --where_clause := where_clause || ' AND rf.proc_certified_with_issues>0';
408 ELSIF p_page_parameter_tbl(i).parameter_id = '''NOT_CERTIFIED''' THEN
409 --where_clause := where_clause || ' AND rocsf.certification_result_code IS NULL ';
410 l_rf_where := l_rf_where || ' AND rf3.proc_not_certified > 0 ';
411 --where_clause := where_clause || ' AND rocsf.proc_not_certified>0';
412 END IF;
413 -- where_clause := where_clause || ' AND rpcm.cert_result='|| p_page_parameter_tbl(i).parameter_id ;
414 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
415 /**05.19.2006 npanandi: added below, else giving null**/
416 v_period := p_page_parameter_tbl(i).parameter_id;
417
418 v_yyyymm := rci_org_cert_summ_pkg.GET_LAST_DAY( p_page_parameter_tbl(i).parameter_id,'M');
419 /*01.09.2006 npanandi: changed reference to FII_TIME_DAY for time dimension**/
420 where_clause1 := where_clause1 || ' AND ftd.ent_period_id = :TIME ';
421 where_clause := where_clause || ' AND ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
422 inner_where_clause := inner_where_clause || ' AND ftd.ent_period_id = :TIME ';
423 l_rf_where := l_rf_where || ' AND ftd.ent_period_id = :TIME ';
424 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
425 /**05.19.2006 npanandi: added below, else giving null**/
426 v_period := p_page_parameter_tbl(i).parameter_id;
427
428 v_yyyymm := rci_org_cert_summ_pkg.GET_LAST_DAY( p_page_parameter_tbl(i).parameter_id,'Q');
429 /*01.09.2006 npanandi: changed reference to FII_TIME_DAY for time dimension**/
430 where_clause1 := where_clause1 || ' AND ftd.ent_qtr_id = :TIME ';
431 where_clause := where_clause || ' AND ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
432 inner_where_clause := inner_where_clause || ' AND ftd.ent_qtr_id = :TIME ';
433 l_rf_where := l_rf_where || ' AND ftd.ent_qtr_id = :TIME ';
434 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
435 /**05.19.2006 npanandi: added below, else giving null**/
436 v_period := p_page_parameter_tbl(i).parameter_id;
437
438 v_yyyymm := rci_org_cert_summ_pkg.GET_LAST_DAY( p_page_parameter_tbl(i).parameter_id,'Y');
439 /*01.09.2006 npanandi: changed reference to FII_TIME_DAY for time dimension**/
440 where_clause1 := where_clause1 || ' AND ftd.ent_year_id = :TIME ';
441 where_clause := where_clause || ' AND ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
442 inner_where_clause := inner_where_clause || ' AND ftd.ent_year_id = :TIME ';
443 l_rf_where := l_rf_where || ' AND ftd.ent_year_id = :TIME ';
444 END IF;
445 END IF; -- parameter_id IS NOT NULL
446 END LOOP;
447
448 l_sqlstmt := 'SELECT distinct
449 display_name VIEWBY
453 ,RCI_PROC_CERT_MEASURE7 ,RCI_PROC_CERT_MEASURE8 ,RCI_PROC_CERT_MEASURE9
450 ,0 RCI_GRAND_TOTAL
451 ,RCI_DRILLDOWN_PARAM1 ,RCI_PROC_CERT_MEASURE3
452 ,RCI_PROC_CERT_MEASURE4 ,RCI_PROC_CERT_MEASURE5 ,RCI_PROC_CERT_MEASURE6
454 ,RCI_PROC_CERT_MEASURE10 ,RCI_PROC_CERT_MEASURE11 ,RCI_PROC_CERT_MEASURE12
455 ,RCI_PROC_CERT_MEASURE13 ,RCI_PROC_CERT_MEASURE14 ,RCI_PROC_CERT_MEASURE15
456 ,RCI_PROC_CERT_MEASURE16 ,RCI_PROC_CERT_MEASURE17
457 from
458 (select distinct /*rocsf.organization_id,*/alrv.display_name,rf.process_id RCI_DRILLDOWN_PARAM1
459 ,nvl(rocsf.proc_certified_with_issues,0) RCI_PROC_CERT_MEASURE3
460 ,nvl(rf2.proc_certified,0) RCI_PROC_CERT_MEASURE4
461 ,nvl(rf3.proc_not_certified,0) RCI_PROC_CERT_MEASURE5
462 ,nvl(risk_ineff_ctrls.risk_id,0) RCI_PROC_CERT_MEASURE6 /*risks with ineffective controls*/
463 ,rpcsf.miti_risks RCI_PROC_CERT_MEASURE7
464 ,nvl(r1.risk_id,0) RCI_PROC_CERT_MEASURE8
465 ,nvl(r2.risk_id,0) RCI_PROC_CERT_MEASURE9
466 ,nvl(r3.risk_id,0) RCI_PROC_CERT_MEASURE10
467 ,rpcsf.ne_risks RCI_PROC_CERT_MEASURE11
468 ,rpcsf.eff_ctrls RCI_PROC_CERT_MEASURE12
469 ,nvl(c1.control_id,0) RCI_PROC_CERT_MEASURE14
470 ,nvl(c2.control_id,0) RCI_PROC_CERT_MEASURE15
471 ,nvl(c3.control_id,0) RCI_PROC_CERT_MEASURE13
472 ,rpcsf.ne_ctrls RCI_PROC_CERT_MEASURE16
473 ,nvl(op.open_issues,0) RCI_PROC_CERT_MEASURE17
474 from rci_org_cert_summ_f rf,fii_time_day ftd,
475 /***05.24.2006 npanandi: added organizationId in the subquery below
476 for rocsf,rf2,rf3 otherwise number mismatch between the summary page
477 and the Process Detail page
478 ***/
479 (select process_id, count(fin_certification_id) as proc_certified_with_issues from (select distinct fin_certification_id,organization_id,process_id from rci_process_detail_f rocsf,fii_time_day ftd
480 where rocsf.report_date_julian = ftd.report_date_julian and rocsf.certification_result_code=''INEFFECTIVE'' and '||where_clause||' ) group by process_id) rocsf
481 ,(select process_id, count(fin_certification_id) as proc_certified from (select distinct fin_certification_id,organization_id,process_id from rci_process_detail_f rocsf,fii_time_day ftd
482 where rocsf.report_date_julian = ftd.report_date_julian and rocsf.certification_result_code=''EFFECTIVE'' and '||where_clause||' ) group by process_id) rf2
483 ,(select process_id, count(fin_certification_id) as proc_not_certified from (select distinct fin_certification_id,organization_id,process_id from rci_process_detail_f rocsf,fii_time_day ftd
484 where rocsf.report_date_julian = ftd.report_date_julian and rocsf.certification_result_code IS NULL and '||where_clause||' ) group by process_id) rf3
485 /*,fii_time_day ftd*/
486 ,amw_latest_revisions_v alrv
487 ,(select aca.pk3 as process_id, count(distinct aca.pk4) as risk_id
488 from AMW_CONTROL_ASSOCIATIONS aca,amw_opinions_log_v aolv
489 where object_type=''RISK_FINCERT'' and aca.pk5=aolv.opinion_log_id
490 and aolv.audit_result_code <> ''EFFECTIVE'' and '||l_ineffctrls_frm|| '
491 group by pk3) risk_ineff_ctrls
492 ,(select process_id, count(distinct risk_id) as risk_id from (select distinct organization_id,process_id,risk_id,project_id,audit_result_code
493 from RCI_ORG_CERT_RISKS_F,fii_time_day ftd
494 where RCI_ORG_CERT_RISKS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
495 and audit_result_code=''SOMEWHAT_EFFECTIVE'' and '||inner_where_clause||'
496 ) group by process_id) r1
497 ,(select distinct process_id, count(distinct risk_id) as risk_id from (select distinct organization_id,process_id,risk_id,project_id,audit_result_code
498 from RCI_ORG_CERT_RISKS_F,fii_time_day ftd
499 where RCI_ORG_CERT_RISKS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
500 and audit_result_code=''NEARLY_INEFFECTIVE'' and '||inner_where_clause||'
501 ) group by process_id) r2
502 ,(select distinct process_id, count(distinct risk_id) as risk_id from (select distinct organization_id,process_id,risk_id,project_id,audit_result_code
503 from RCI_ORG_CERT_RISKS_F,fii_time_day ftd
507 ,(select process_id,count(control_id) as control_id from (select distinct process_id,control_id,organization_id,audit_result_code,DES_EFF_ID,OP_EFF_ID
504 where RCI_ORG_CERT_RISKS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
505 and audit_result_code=''INEFFECTIVE'' and '||inner_where_clause||'
506 ) group by process_id) r3
508 from RCI_ORG_CERT_CTRLS_F,fii_time_day ftd
509 where RCI_ORG_CERT_CTRLS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
510 and audit_result_code=''NEARLY_INEFFECTIVE'' and '||inner_where_clause||'
511 ) group by process_id) c1
512 ,(select process_id,count(control_id) as control_id from (select distinct process_id,control_id,organization_id,audit_result_code,DES_EFF_ID,OP_EFF_ID
513 from RCI_ORG_CERT_CTRLS_F,fii_time_day ftd
514 where RCI_ORG_CERT_CTRLS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
515 and audit_result_code=''INEFFECTIVE'' and '||inner_where_clause||'
516 ) group by process_id) c2
517 ,(select process_id,count(control_id) as control_id from (select distinct process_id,control_id,organization_id,audit_result_code,DES_EFF_ID,OP_EFF_ID
518 from RCI_ORG_CERT_CTRLS_F,fii_time_day ftd
519 where RCI_ORG_CERT_CTRLS_F.REPORT_DATE_JULIAN=ftd.REPORT_DATE_JULIAN
520 and audit_result_code=''SOMEWHAT_EFFECTIVE'' and '||inner_where_clause||'
521 ) group by process_id) c3
522 ,(select roif.process_id,count(distinct roif.change_id) as open_issues
523 from rci_open_issues_f roif,eng_engineering_changes eec
524 where eec.change_id=roif.change_id and roif.certification_id is not null
525 and '||l_issues_where||' and roif.organization_id is not null
526 and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
527 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) ))
528 group by roif.process_id ) op
529 ,(SELECT
530 process_id
531 ,sum(nvl(RISK_EVAL_M,0)) miti_risks
532 ,sum(nvl(RISK_EVAL_NE,0)) ne_risks
533 ,sum(nvl(CTRL_EVAL_E,0)) eff_ctrls
534 ,sum(nvl(CTRL_EVAL_NE,0)) ne_ctrls
535 from
536 rci_proc_cert_sum_f rpcm
537 ,fii_time_day ftd
538 where
539 rpcm.report_date_julian = ftd.report_date_julian ' || where_clause1 || '
540 group by process_id) rpcsf
541 where rf.report_date_julian = ftd.report_date_julian and '||l_rf_where|| '
542 and rf.process_id = alrv.process_id
543 and rf.process_id=rocsf.process_id(+)
544 and rf.process_id = rf2.process_id(+)
545 and rf.process_id = rf3.process_id(+)
546 and rf.process_id = risk_ineff_ctrls.process_id(+)
547 and rf.process_id = r1.process_id(+)
548 and rf.process_id = r2.process_id(+)
549 and rf.process_id = r3.process_id(+)
550 and rf.process_id = c1.process_id(+)
551 and rf.process_id = c2.process_id(+)
552 and rf.process_id = c3.process_id(+)
553 and rf.process_id = op.process_id(+)
554 and rf.process_id = rpcsf.process_id(+))
555 /**01.26.2006 npanandi: added below line for bug 5000427**/
556 order by RCI_PROC_CERT_MEASURE3 desc ';
557
558 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
559 l_act_sqlstmt := 'select VIEWBY
560 ,RCI_GRAND_TOTAL,RCI_DRILLDOWN_PARAM1,RCI_PROC_CERT_MEASURE3
561 ,RCI_PROC_CERT_MEASURE4,RCI_PROC_CERT_MEASURE5,RCI_PROC_CERT_MEASURE6
562 ,RCI_PROC_CERT_MEASURE7,RCI_PROC_CERT_MEASURE8,RCI_PROC_CERT_MEASURE9
563 ,RCI_PROC_CERT_MEASURE10,RCI_PROC_CERT_MEASURE11,RCI_PROC_CERT_MEASURE12
564 ,RCI_PROC_CERT_MEASURE13,RCI_PROC_CERT_MEASURE14,RCI_PROC_CERT_MEASURE15
565 ,RCI_PROC_CERT_MEASURE16,RCI_PROC_CERT_MEASURE17
566 from (select t.*
567 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
568 from ( '||l_sqlstmt||'
569 ) t ) a
570 order by a.col_rank ';
571
572 p_exp_source_sql := l_act_sqlstmt;
573 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
574 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
575
576 p_exp_source_output.EXTEND;
577 l_bind_rec.attribute_name := ':TIME';
578 l_bind_rec.attribute_value := v_period;
579 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
580 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
581 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
582
583 END get_proc_cert_summary;
584
585 /*
586 --Procedure to be called from concurrent program--
587 */
588 PROCEDURE proc_cert_incremental_load
589 (
590 errbuf IN OUT NOCOPY VARCHAR2
591 ,retcode IN OUT NOCOPY NUMBER)
592 IS
593 BEGIN
594 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.rci_proc_cert_sum_f');
595 proc_cert_initial_load(
596 errbuf => errbuf
597 ,retcode => retcode);
598 END proc_cert_incremental_load;
599 /*
600 --------------------------------------------------------------------------
601 */
602 PROCEDURE proc_cert_initial_load
603 (
604 errbuf IN OUT NOCOPY VARCHAR2
605 ,retcode IN OUT NOCOPY NUMBER)
606 IS
607 /*todo remove this later
608 CURSOR c_all_certifications is
609 SELECT DISTINCT acb.certification_id, acb.certification_status, acb.certification_type,
610 fin_scope.process_id, fin_scope.organization_id,
611 agpv.period_year period_year,
612 agpv.period_num period_num,
613 agpv.quarter_num quarter_num,
614 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) ent_period_id,
618 FROM
615 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) ent_qtr_id,
616 agpv.period_year ent_year_id,
617 to_number(to_char(agpv.end_date,'J')) report_date_julian
619 amw_certification_b acb, amw_gl_periods_v agpv, amw_fin_cert_scope fin_scope
620 WHERE
621 acb.object_type = 'FIN_STMT'
622 -- AND acb.certification_status in ('ACTIVE','DRAFT')
623 AND acb.certification_id = fin_scope.fin_certification_id
624 AND acb.certification_period_name = agpv.period_name
625 AND acb.certification_period_set_name = agpv.period_set_name
626 AND fin_scope.process_id IS NOT NULL
627 AND fin_scope.organization_id IS NOT NULL;
628 r_proc c_all_certifications%rowtype;
629 time_rec TIME_DIMENSIONS_RECORD;
630 cert_rec CERT_DETAIL_RECORD;*/
631 CURSOR c_all_certifications is
632 SELECT CERTIFICATION_ID, ORGANIZATION_ID, PROCESS_ID
633 FROM rci_proc_cert_sum_f;
634 r_proc c_all_certifications%rowtype;
635
636 l_user_id NUMBER ;
637 l_login_id NUMBER ;
638 l_program_id NUMBER ;
639 l_program_login_id NUMBER ;
640 l_program_application_id NUMBER ;
641 l_request_id NUMBER ;
642 l_run_date DATE;
643 BEGIN
644 DELETE FROM rci_proc_cert_sum_f;
645
646 INSERT INTO rci_proc_cert_sum_f(
647 CERTIFICATION_ID, ORGANIZATION_ID, PROCESS_ID,
648 CERT_STATUS, CERT_TYPE, CERT_RESULT,
649 CERT_RESULT_CWI, CERT_RESULT_C, CERT_RESULT_NC,
650 PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM,
651 ENT_PERIOD_ID, ENT_QTR_ID, ENT_YEAR_ID,
652 REPORT_DATE_JULIAN,
653 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
654 SELECT DISTINCT
655 acb.certification_id, aolv.pk3_value org_id, aolv.pk1_value process_id,
656 acb.certification_status, acb.certification_type, nvl(aolv.audit_result_code,'NOT_CERTIFIED'),
657 decode(aolv.audit_result_code,'INEFFECTIVE',1,0),
658 decode(aolv.audit_result_code,'EFFECTIVE',1,0),
659 decode(aolv.audit_result_code,NULL,1,0),
660 agpv.period_year period_year,
661 agpv.period_num period_num,
662 agpv.quarter_num quarter_num,
663 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)) ent_period_id,
664 to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)) ent_qtr_id,
665 agpv.period_year ent_year_id,
666 to_number(to_char(agpv.end_date,'J')) report_date_julian,
667 G_USER_ID, sysdate, G_USER_ID, sysdate, G_LOGIN_ID
668 FROM
669 amw_certification_b acb, amw_gl_periods_v agpv, amw_opinions_log_v aolv
670 WHERE
671 acb.object_type = 'PROCESS'
672 AND aolv.opinion_type_code = 'CERTIFICATION'
673 AND aolv.object_name = 'AMW_ORG_PROCESS'
674 AND aolv.pk2_value = acb.certification_id
675 AND acb.certification_period_name = agpv.period_name
676 AND acb.certification_period_set_name = agpv.period_set_name;
677
678 FOR r_proc in c_all_certifications LOOP
679 update_proc_cert_table(r_proc.process_id, r_proc.organization_id, r_proc.certification_id);
680 END LOOP;
681
682 /*todo remove this later
683 FOR r_proc in c_all_certifications LOOP
684 cert_rec.cert_id := r_proc.CERTIFICATION_ID;
685 cert_rec.cert_status := r_proc.CERTIFICATION_STATUS;
686 cert_rec.cert_type := r_proc.CERTIFICATION_TYPE;
687 time_rec.period_year := r_proc.period_year;
688 time_rec.period_num := r_proc.period_num;
689 time_rec.quarter_num := r_proc.quarter_num;
690 time_rec.ent_period_id := r_proc.ent_period_id;
691 time_rec.ent_qtr_id := r_proc.ent_qtr_id;
692 time_rec.ent_year_id := r_proc.ent_year_id;
693 time_rec.report_date_julian := r_proc.report_date_julian;
694 update_proc_cert_table(r_proc.process_id, r_proc.organization_id, cert_rec, time_rec);
695 END LOOP;
696 */
697 l_user_id := NVL(fnd_global.USER_ID, -1);
698 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
699 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
700 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
701 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
702 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
703 l_run_date := sysdate - 5/(24*60);
704
705 DELETE FROM rci_dr_inc WHERE fact_name='RCI_PROC_CERT_SUM_F';
706
707 INSERT INTO rci_dr_inc( fact_name
708 ,last_run_date
709 ,created_by
710 ,creation_date
711 ,last_update_date
712 ,last_updated_by
713 ,last_update_login
714 ,program_id
715 ,program_login_id
716 ,program_application_id
717 ,request_id ) VALUES (
718 'RCI_PROC_CERT_SUM_F'
719 ,l_run_date
720 ,l_user_id
721 ,sysdate
722 ,sysdate
723 ,l_user_id
724 ,l_login_id
725 ,l_program_id
726 ,l_program_login_id
727 ,l_program_application_id
728 ,l_request_id );
729
730 EXECUTE IMMEDIATE ('COMMIT');
731
732 END proc_cert_initial_load;
733 /*
734 --------------------------------------------------------------------------
735 */
736 PROCEDURE update_proc_cert_table(
737 p_process_id IN NUMBER,
738 p_org_id IN NUMBER,
739 p_cert_id IN NUMBER
740 )
741 IS
742 CURSOR c_get_risks_with_ineff_ctrls
743 IS
744 SELECT count(1)
748 AND aca.pk1 = p_cert_id
745 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id
746 FROM amw_control_associations aca,amw_opinions_v aov
747 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
749 AND aca.pk2 = p_org_id
750 AND aca.pk3 IN (SELECT DISTINCT process_id
751 FROM amw_execution_scope
752 START WITH process_id = p_process_id
753 AND organization_id = p_org_id
754 AND entity_id = p_cert_id
755 and entity_type='BUSIPROC_CERTIFICATION'
756 CONNECT BY PRIOR process_id = parent_process_id
757 AND organization_id = PRIOR organization_id
758 AND entity_id = PRIOR entity_id
759 and entity_type=prior entity_type
760 )
761 AND aov.object_name = 'AMW_ORG_CONTROL'
762 AND aov.opinion_type_code = 'EVALUATION'
763 AND aov.pk3_value = p_org_id
764 AND aov.pk1_value = aca.control_id
765 AND aov.audit_result_code <> 'EFFECTIVE'
766 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
767 FROM amw_opinions aov2
768 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
769 AND aov2.pk3_value = aov.pk3_value
770 AND aov2.pk1_value = aov.pk1_value)
771 );
772 CURSOR c_get_risk_evals
773 IS
774 SELECT
775 riskevalopin.audit_result_code AS risk_eval,
776 count(nvl(riskevalopin.audit_result_code,1)) cnt
777 FROM
778 (SELECT object_type,risk_id,pk1,pk2,pk3,pk4,pk5,risk_rev_id
779 FROM amw_risk_associations WHERE object_type='BUSIPROC_CERTIFICATION') ara,
780 amw_risks_all_vl arav,
781 amw_opinions_log_v riskevalopin
782 WHERE
783 ara.pk1(+)=p_cert_id
784 AND ara.pk2(+)=p_org_id
785 AND ara.pk3(+)=p_process_id
786 AND ara.risk_id=arav.risk_id
787 AND ara.risk_rev_id=arav.risk_rev_id
788 AND arav.latest_revision_flag(+)='Y'
789 AND riskevalopin.opinion_log_id(+)=ara.pk4
790 group by riskevalopin.audit_result_code;
791 CURSOR c_get_control_evals
792 IS
793 SELECT
794 ctrlevalopin.audit_result_code AS ctrl_eval,
795 count(nvl(ctrlevalopin.audit_result_code,1)) cnt
796 FROM
797 (SELECT object_type,control_id,pk1,pk2,pk3,pk4,pk5,control_rev_id
798 FROM amw_control_associations WHERE object_type='BUSIPROC_CERTIFICATION') aca,
799 amw_controls_all_vl acav,
800 amw_opinions_log_v ctrlevalopin
801 WHERE
802 aca.pk1(+)=p_cert_id
803 AND aca.pk2(+)=p_org_id
804 AND aca.pk3(+)=p_process_id
805 AND aca.control_id=acav.control_id
806 AND aca.control_rev_id=acav.control_rev_id
807 AND ctrlevalopin.opinion_log_id(+)=aca.pk5
808 AND acav.latest_revision_flag(+)='Y'
809 group by ctrlevalopin.audit_result_code;
810 r_get_risk_evals c_get_risk_evals%rowtype;
811 r_get_control_evals c_get_control_evals%rowtype;
812 v_risks_with_ineff_ctrls NUMBER;
813 v_re_mitigated rci_proc_cert_sum_f.RISK_EVAL_M%type := 0;
814 v_re_somewhat_mitigated rci_proc_cert_sum_f.RISK_EVAL_SM%type := 0;
815 v_re_somewhat_exposed rci_proc_cert_sum_f.RISK_EVAL_SE%type := 0;
816 v_re_fully_exposed rci_proc_cert_sum_f.RISK_EVAL_FE%type := 0;
817 v_re_not_evaluated rci_proc_cert_sum_f.RISK_EVAL_NE%type := 0;
818 v_ce_effective rci_proc_cert_sum_f.CTRL_EVAL_E%type := 0;
819 v_ce_materially_weak rci_proc_cert_sum_f.CTRL_EVAL_MW%type := 0;
820 v_ce_deficient rci_proc_cert_sum_f.CTRL_EVAL_D%type := 0;
821 v_ce_significantly_deficient rci_proc_cert_sum_f.CTRL_EVAL_SD%type := 0;
822 v_ce_not_evaluated rci_proc_cert_sum_f.CTRL_EVAL_NE%type := 0;
823 v_open_issues NUMBER;
824 BEGIN
825 v_open_issues := amw_findings_pkg.calculate_open_findings('AMW_PROC_CERT_ISSUES',
826 'PROCESS',
827 p_process_id,
828 'ORGANIZATION',
829 p_org_id,
830 'CERTIFICATION',
831 p_cert_id,
832 null, null, null, null);
833 OPEN c_get_risks_with_ineff_ctrls;
834 FETCH c_get_risks_with_ineff_ctrls into v_risks_with_ineff_ctrls;
835 CLOSE c_get_risks_with_ineff_ctrls;
836 FOR r_get_risk_evals IN c_get_risk_evals LOOP
837 IF r_get_risk_evals.risk_eval is null THEN
838 v_re_not_evaluated := r_get_risk_evals.cnt;
839 END IF;
840 IF r_get_risk_evals.risk_eval = 'EFFECTIVE' THEN
841 v_re_mitigated := r_get_risk_evals.cnt;
842 END IF;
843 IF r_get_risk_evals.risk_eval = 'SOMEWHAT_EFFECTIVE' THEN
844 v_re_somewhat_mitigated := r_get_risk_evals.cnt;
845 END IF;
846 IF r_get_risk_evals.risk_eval = 'NEARLY_INEFFECTIVE' THEN
847 v_re_somewhat_exposed := r_get_risk_evals.cnt;
848 END IF;
849 IF r_get_risk_evals.risk_eval = 'INEFFECTIVE' THEN
850 v_re_fully_exposed := r_get_risk_evals.cnt;
851 END IF;
852 END LOOP;
853 FOR r_get_control_evals IN c_get_control_evals LOOP
854 IF r_get_control_evals.ctrl_eval is null THEN
855 v_ce_not_evaluated := r_get_control_evals.cnt;
856 END IF;
857 IF r_get_control_evals.ctrl_eval = 'EFFECTIVE' THEN
858 v_ce_effective := r_get_control_evals.cnt;
859 END IF;
860 IF r_get_control_evals.ctrl_eval = 'SOMEWHAT_EFFECTIVE' THEN
861 v_ce_materially_weak := r_get_control_evals.cnt;
862 END IF;
866 IF r_get_control_evals.ctrl_eval = 'INEFFECTIVE' THEN
863 IF r_get_control_evals.ctrl_eval = 'NEARLY_INEFFECTIVE' THEN
864 v_ce_deficient := r_get_control_evals.cnt;
865 END IF;
867 v_ce_significantly_deficient := r_get_control_evals.cnt;
868 END IF;
869 END LOOP;
870
871 UPDATE rci_proc_cert_sum_f
872 SET
873 RISKS_WITH_INEFF_CTRLS = v_risks_with_ineff_ctrls,
874 RISK_EVAL_M = v_re_mitigated,
875 RISK_EVAL_SM = v_re_somewhat_mitigated,
876 RISK_EVAL_SE = v_re_somewhat_exposed,
877 RISK_EVAL_FE = v_re_somewhat_exposed,
878 RISK_EVAL_NE = v_re_not_evaluated,
879 CTRL_EVAL_E = v_ce_effective,
880 CTRL_EVAL_MW =v_ce_materially_weak,
881 CTRL_EVAL_D = v_ce_deficient,
882 CTRL_EVAL_SD = v_ce_significantly_deficient,
883 CTRL_EVAL_NE = v_ce_not_evaluated,
884 OPEN_ISSUES = v_open_issues,
885 lAST_UPDATE_DATE = SYSDATE,
886 lAST_UPDATED_BY = G_USER_ID,
887 lAST_UPDATE_LOGIN = G_LOGIN_ID
888 WHERE
889 CERTIFICATION_ID = p_cert_id
890 AND ORGANIZATION_ID = p_org_id
891 AND PROCESS_ID = p_process_id;
892
893 END update_proc_cert_table;
894 /*todo remove this later
895 PROCEDURE update_proc_cert_table(
896 p_process_id IN NUMBER,
897 p_org_id IN NUMBER,
898 cert_rec IN CERT_DETAIL_RECORD, time_rec IN TIME_DIMENSIONS_RECORD)
899 IS
900
901 CURSOR c_get_certification_opinion
902 IS
903 SELECT opinion.opinion_id,opinion.audit_result_code
904 FROM amw_opinions_v opinion
905 WHERE opinion.pk3_value = p_org_id
906 AND opinion.pk2_value = cert_rec.cert_id
907 AND opinion.pk1_value = p_process_id
908 AND opinion.opinion_type_code = 'CERTIFICATION'
909 AND opinion.object_name = 'AMW_ORG_PROCESS';
910 CURSOR c_get_risks_with_ineff_ctrls
911 IS
912 SELECT count(1)
913 FROM (SELECT DISTINCT aca.pk1 certification_id, aca.pk2 organization_id, aca.pk3 process_id, aca.pk4 risk_id
914 FROM amw_control_associations aca,amw_opinions_v aov
915 WHERE aca.object_type = 'BUSIPROC_CERTIFICATION'
916 AND aca.pk1 = cert_rec.cert_id
917 AND aca.pk2 = p_org_id
918 AND aca.pk3 IN (SELECT DISTINCT process_id
919 FROM amw_execution_scope
920 START WITH process_id = p_process_id
921 AND organization_id = p_org_id
922 AND entity_id = cert_rec.cert_id
923 and entity_type='BUSIPROC_CERTIFICATION'
924 CONNECT BY PRIOR process_id = parent_process_id
925 AND organization_id = PRIOR organization_id
926 AND entity_id = PRIOR entity_id
927 and entity_type=prior entity_type
928 )
929 AND aov.object_name = 'AMW_ORG_CONTROL'
930 AND aov.opinion_type_code = 'EVALUATION'
931 AND aov.pk3_value = p_org_id
932 AND aov.pk1_value = aca.control_id
933 AND aov.audit_result_code <> 'EFFECTIVE'
934 AND aov.authored_date = (SELECT MAX(aov2.authored_date)
935 FROM amw_opinions aov2
936 WHERE aov2.object_opinion_type_id = aov.object_opinion_type_id
937 AND aov2.pk3_value = aov.pk3_value
938 AND aov2.pk1_value = aov.pk1_value)
939 );
940 CURSOR c_get_risk_evals
941 IS
942 SELECT
943 riskevalopin.audit_result_code AS risk_eval,
944 count(nvl(riskevalopin.audit_result_code,1)) cnt
945 FROM
946 (SELECT object_type,risk_id,pk1,pk2,pk3,pk4,pk5,risk_rev_id
947 FROM amw_risk_associations WHERE object_type='BUSIPROC_CERTIFICATION') ara,
948 amw_risks_all_vl arav,
949 amw_opinions_log_v riskevalopin
950 WHERE
951 ara.pk1(+)=cert_rec.cert_id
952 AND ara.pk2(+)=p_org_id
953 AND ara.pk3(+)=p_process_id
954 AND ara.risk_id=arav.risk_id
955 AND ara.risk_rev_id=arav.risk_rev_id
956 AND arav.latest_revision_flag(+)='Y'
957 AND riskevalopin.opinion_log_id(+)=ara.pk4
958 group by riskevalopin.audit_result_code;
959 CURSOR c_get_control_evals
960 IS
961 SELECT
962 ctrlevalopin.audit_result_code AS ctrl_eval,
963 count(nvl(ctrlevalopin.audit_result_code,1)) cnt
964 FROM
965 (SELECT object_type,control_id,pk1,pk2,pk3,pk4,pk5,control_rev_id
966 FROM amw_control_associations WHERE object_type='BUSIPROC_CERTIFICATION') aca,
967 amw_controls_all_vl acav,
968 amw_opinions_log_v ctrlevalopin
969 WHERE
970 aca.pk1(+)=cert_rec.cert_id
971 AND aca.pk2(+)=p_org_id
972 AND aca.pk3(+)=p_process_id
973 AND aca.control_id=acav.control_id
974 AND aca.control_rev_id=acav.control_rev_id
975 AND ctrlevalopin.opinion_log_id(+)=aca.pk5
976 AND acav.latest_revision_flag(+)='Y'
977 group by ctrlevalopin.audit_result_code;
978 v_proc_name VARCHAR2(50);
979 r_certification_opinion c_get_certification_opinion%rowtype;
980 r_get_risk_evals c_get_risk_evals%rowtype;
981 r_get_control_evals c_get_control_evals%rowtype;
982 v_certification_result amw_opinions_v.audit_result%type;
983 v_cert_with_issues rci_proc_cert_sum_f.CERT_RESULT_CWI%type := 0;
984 v_certified rci_proc_cert_sum_f.CERT_RESULT_C%type := 0;
985 v_not_certified rci_proc_cert_sum_f.CERT_RESULT_NC%type := 0;
986 v_risks_with_ineff_ctrls NUMBER;
987 v_re_mitigated rci_proc_cert_sum_f.RISK_EVAL_M%type := 0;
988 v_re_somewhat_mitigated rci_proc_cert_sum_f.RISK_EVAL_SM%type := 0;
989 v_re_somewhat_exposed rci_proc_cert_sum_f.RISK_EVAL_SE%type := 0;
990 v_re_fully_exposed rci_proc_cert_sum_f.RISK_EVAL_FE%type := 0;
994 v_ce_deficient rci_proc_cert_sum_f.CTRL_EVAL_D%type := 0;
991 v_re_not_evaluated rci_proc_cert_sum_f.RISK_EVAL_NE%type := 0;
992 v_ce_effective rci_proc_cert_sum_f.CTRL_EVAL_E%type := 0;
993 v_ce_materially_weak rci_proc_cert_sum_f.CTRL_EVAL_MW%type := 0;
995 v_ce_significantly_deficient rci_proc_cert_sum_f.CTRL_EVAL_SD%type := 0;
996 v_ce_not_evaluated rci_proc_cert_sum_f.CTRL_EVAL_NE%type := 0;
997 v_open_issues NUMBER;
998 BEGIN
999 v_proc_name := 'update_proc_cert_table';
1000 fnd_file.put_line (fnd_file.LOG, v_proc_name||' start');
1001 v_open_issues := amw_findings_pkg.calculate_open_findings('AMW_PROC_CERT_ISSUES',
1002 'PROCESS',
1003 p_process_id,
1004 'ORGANIZATION',
1005 p_org_id,
1006 'CERTIFICATION',
1007 cert_rec.cert_id,
1008 null, null, null, null);
1009 open c_get_certification_opinion;
1010 fetch c_get_certification_opinion into r_certification_opinion;
1011 close c_get_certification_opinion;
1012 OPEN c_get_risks_with_ineff_ctrls;
1013 FETCH c_get_risks_with_ineff_ctrls into v_risks_with_ineff_ctrls;
1014 CLOSE c_get_risks_with_ineff_ctrls;
1015 FOR r_get_risk_evals IN c_get_risk_evals LOOP
1016 IF r_get_risk_evals.risk_eval is null THEN
1017 v_re_not_evaluated := r_get_risk_evals.cnt;
1018 END IF;
1019 IF r_get_risk_evals.risk_eval = 'EFFECTIVE' THEN
1020 v_re_mitigated := r_get_risk_evals.cnt;
1021 END IF;
1022 IF r_get_risk_evals.risk_eval = 'SOMEWHAT_EFFECTIVE' THEN
1023 v_re_somewhat_mitigated := r_get_risk_evals.cnt;
1024 END IF;
1025 IF r_get_risk_evals.risk_eval = 'NEARLY_INEFFECTIVE' THEN
1026 v_re_somewhat_exposed := r_get_risk_evals.cnt;
1027 END IF;
1028 IF r_get_risk_evals.risk_eval = 'INEFFECTIVE' THEN
1029 v_re_fully_exposed := r_get_risk_evals.cnt;
1030 END IF;
1031 END LOOP;
1032 FOR r_get_control_evals IN c_get_control_evals LOOP
1033 IF r_get_control_evals.ctrl_eval is null THEN
1034 v_ce_not_evaluated := r_get_control_evals.cnt;
1035 END IF;
1036 IF r_get_control_evals.ctrl_eval = 'EFFECTIVE' THEN
1037 v_ce_effective := r_get_control_evals.cnt;
1038 END IF;
1039 IF r_get_control_evals.ctrl_eval = 'SOMEWHAT_EFFECTIVE' THEN
1040 v_ce_materially_weak := r_get_control_evals.cnt;
1041 END IF;
1042 IF r_get_control_evals.ctrl_eval = 'NEARLY_INEFFECTIVE' THEN
1043 v_ce_deficient := r_get_control_evals.cnt;
1044 END IF;
1045 IF r_get_control_evals.ctrl_eval = 'INEFFECTIVE' THEN
1046 v_ce_significantly_deficient := r_get_control_evals.cnt;
1047 END IF;
1048 END LOOP;
1049
1050 v_certification_result := r_certification_opinion.audit_result_code;
1051 IF v_certification_result is null THEN
1052 v_not_certified := 1;
1053 v_certification_result := 'NOT_CERTIFIED';
1054 ELSIF v_certification_result='INEFFECTIVE' THEN
1055 v_cert_with_issues := 1;
1056 ELSIF v_certification_result='EFFECTIVE' THEN
1057 v_certified := 1;
1058 END IF;
1059 UPDATE rci_proc_cert_sum_f
1060 SET
1061 CERT_RESULT = v_certification_result,
1062 CERT_RESULT_CWI = v_cert_with_issues,
1063 CERT_RESULT_C = v_certified,
1064 CERT_RESULT_NC = v_not_certified,
1065 CERT_STATUS = cert_rec.cert_status,
1066 CERT_TYPE = cert_rec.cert_type,
1067 RISKS_WITH_INEFF_CTRLS = v_risks_with_ineff_ctrls,
1068 RISK_EVAL_M = v_re_mitigated,
1069 RISK_EVAL_SM = v_re_somewhat_mitigated,
1070 RISK_EVAL_SE = v_re_somewhat_exposed,
1071 RISK_EVAL_FE = v_re_somewhat_exposed,
1072 RISK_EVAL_NE = v_re_not_evaluated,
1073 CTRL_EVAL_E = v_ce_effective,
1074 CTRL_EVAL_MW =v_ce_materially_weak,
1075 CTRL_EVAL_D = v_ce_deficient,
1076 CTRL_EVAL_SD = v_ce_significantly_deficient,
1077 CTRL_EVAL_NE = v_ce_not_evaluated,
1078 OPEN_ISSUES = v_open_issues,
1079 -- certification_opinion_id = l_certification_opinion_id,
1080 lAST_UPDATE_DATE = SYSDATE,
1081 lAST_UPDATED_BY = G_USER_ID,
1082 lAST_UPDATE_LOGIN = G_LOGIN_ID
1083 WHERE
1084 CERTIFICATION_ID = cert_rec.cert_id
1085 AND ORGANIZATION_ID = p_org_id
1086 AND PROCESS_ID = p_process_id;
1087
1088 IF (SQL%NOTFOUND) THEN
1089 INSERT INTO rci_proc_cert_sum_f(
1090 CERTIFICATION_ID,
1091 ORGANIZATION_ID,
1092 PROCESS_ID,
1093 CERT_STATUS,
1094 CERT_TYPE,
1095 CERT_RESULT,
1096 CERT_RESULT_CWI,
1097 CERT_RESULT_C,
1098 CERT_RESULT_NC,
1099 RISKS_WITH_INEFF_CTRLS,
1100 RISK_EVAL_M,
1101 RISK_EVAL_SM,
1102 RISK_EVAL_SE,
1103 RISK_EVAL_FE,
1104 RISK_EVAL_NE,
1105 CTRL_EVAL_E,
1106 CTRL_EVAL_MW,
1107 CTRL_EVAL_D,
1108 CTRL_EVAL_SD,
1109 CTRL_EVAL_NE,
1110 OPEN_ISSUES,
1114 ENT_PERIOD_ID,
1111 PERIOD_YEAR,
1112 PERIOD_NUM,
1113 QUARTER_NUM,
1115 ENT_QTR_ID,
1116 ENT_YEAR_ID,
1117 REPORT_DATE_JULIAN,
1118 CREATED_BY,
1119 CREATION_DATE,
1120 LAST_UPDATED_BY,
1121 LAST_UPDATE_DATE,
1122 LAST_UPDATE_LOGIN)
1123 VALUES (
1124 cert_rec.cert_id,
1125 p_org_id,
1126 p_process_id,
1127 cert_rec.cert_status,
1128 cert_rec.cert_type,
1129 v_certification_result,
1130 v_cert_with_issues,
1131 v_certified,
1132 v_not_certified,
1133 v_risks_with_ineff_ctrls,
1134 v_re_mitigated,
1135 v_re_somewhat_mitigated,
1136 v_re_somewhat_exposed,
1137 v_re_fully_exposed,
1138 v_re_not_evaluated,
1139 v_ce_effective,
1140 v_ce_materially_weak,
1141 v_ce_deficient,
1142 v_ce_significantly_deficient,
1143 v_ce_not_evaluated,
1144 v_open_issues,
1145 time_rec.period_year ,
1146 time_rec.period_num ,
1147 time_rec.quarter_num ,
1148 time_rec.ent_period_id ,
1149 time_rec.ent_qtr_id ,
1150 time_rec.ent_year_id ,
1151 time_rec.report_date_julian ,
1152 G_USER_ID,
1153 sysdate,
1154 G_USER_ID,
1155 sysdate,
1156 G_LOGIN_ID);
1157 END IF;
1158 fnd_file.put_line (fnd_file.LOG, v_proc_name||' end');
1159 EXCEPTION
1160 WHEN NO_DATA_FOUND THEN
1161 fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in '||v_proc_name || ' '
1162 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1163 WHEN OTHERS THEN
1164 fnd_file.put_line (fnd_file.LOG, SUBSTR ('Error in '||v_proc_name || ' '
1165 || SUBSTR (SQLERRM, 1, 100), 1, 200));
1166 END update_proc_cert_table;*/
1167
1168 end RCI_PROC_CERT_SUMM_PKG;