[Home] [Help]
PACKAGE BODY: APPS.RCI_ORG_CERT_SUMM_PKG
Source
1 PACKAGE BODY RCI_ORG_CERT_SUMM_PKG AS
2 --$Header: rciocsb.pls 120.39.12000000.6 2007/04/09 19:57:49 ddesjard ship $
3
4 PROCEDURE get_org_kpi(
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
6 ,p_exp_source_sql out NOCOPY VARCHAR2
7 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8 is
9 l_sqlstmt VARCHAR2(15000);
10 v_dummy number := 99;
11
12 l_from_clause varchar2(15000);
13 l_getcnt varchar2(15000);
14 l_grp_by varchar2(15000);
15 l_where_clause varchar2(15000);
16 l_qry1 varchar2(15000);
17 l_qry2 varchar2(15000);
18 l_qry3 varchar2(15000);
19
20 l_total number;
21
22 v_period varchar2(100);
23 l_bind_rec BIS_QUERY_ATTRIBUTES;
24 BEGIN
25
26 /** 12.12.2005 npanandi: changed the construction of this query
27 to remove the NA text in the Org Cert KPI portlet
28 bug 4880422 fix ***/
29 l_getcnt := 'select count(1) from (
30 select distinct rocsf.fin_certification_id, organization_id
31 from rci_org_cert_summ_f rocsf
32 ,fii_time_day ftd
33 where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
34
35 ---looping through the parameters
36 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
37
38 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
39 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
40 l_from_clause := l_from_clause || ' and rocsf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
41
42 l_getcnt := l_getcnt || ' and rocsf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
43 END IF;
44
45 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
46 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
47 l_from_clause := l_from_clause || ' and rocsf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
48
49 l_getcnt := l_getcnt || ' and rocsf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
50 END IF;
51
52 /** 10.21.2005 npanandi begin ***/
53 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
54 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
55 /*04.27.2006 npanandi: use dynamic binding here
56 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
57 l_getcnt := l_getcnt || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
58 */
59 v_period := p_page_parameter_tbl(i).parameter_id;
60 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
61 l_getcnt := l_getcnt || ' and ftd.ent_period_id = :TIME2 ';
62 END IF;
63
64 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
65 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
66 /*04.27.2006 npanandi: use dynamic binding here
67 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
68 l_getcnt := l_getcnt || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
69 */
70 v_period := p_page_parameter_tbl(i).parameter_id;
71 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
72 l_getcnt := l_getcnt || ' and ftd.ent_qtr_id = :TIME2 ';
73 END IF;
74
75 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
76 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
77 /*04.27.2006 npanandi: use dynamic binding here
78 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
79 l_getcnt := l_getcnt || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
80 */
81 v_period := p_page_parameter_tbl(i).parameter_id;
82 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
83 l_getcnt := l_getcnt || ' and ftd.ent_year_id = :TIME2 ';
84 END IF;
85 /** 10.21.2005 npanandi end ***/
86
87 end loop;
88
89 ----now the l_getcnt query is complete, so this can be executed to get the
90 ----total which is stored in the variable l_total
91 l_getcnt := l_getcnt || ' )';
92 EXECUTE IMMEDIATE l_getcnt INTO l_total using v_period;
93
94 if( l_total = 0) then
95 ---l_sqlstmt := l_sqlstmt || ', 0 RCI_ORG_CERT_MEASURE3 ';
96 l_total := 1;
97 /**else
98 l_sqlstmt := l_sqlstmt || ', round(count(rocsf.organization_id)/'||l_total||'*100,2) RCI_ORG_CERT_MEASURE3 ';
99 **/
100 end if;
101
102 ---ROUND((nvl(MES1,0)*100/'||v_total ||'),2)
103 l_sqlstmt := 'select round((nvl(a1.not_certified,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE1
104 ,round((nvl(a2.certified_w_issues,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE2
105 ,round((nvl(a3.certified,0)*100/'||l_total||'),2) as RCI_ORG_CERT_MEASURE3
106 from ';
107
108 l_qry1 := '(select count(1) as certified
109 from (select distinct organization_id,fin_certification_id
110 from rci_org_cert_summ_f rocsf, fii_time_day ftd
111 where rocsf.report_date_julian = ftd.report_date_julian
112 and rocsf.org_certification_status=''EFFECTIVE'''||l_from_clause||')) a3, ';
113
114 l_qry2 := '(select count(1) as certified_w_issues
115 from (select distinct organization_id,fin_certification_id
116 from rci_org_cert_summ_f rocsf, fii_time_day ftd
117 where rocsf.report_date_julian = ftd.report_date_julian
118 and org_certification_status <> ''EFFECTIVE'''||l_from_clause||')) a2, ';
119
120 l_qry3 := '(select count(1) as not_certified
121 from (select distinct organization_id,fin_certification_id
122 from rci_org_cert_summ_f rocsf, fii_time_day ftd
123 where rocsf.report_date_julian = ftd.report_date_julian
124 and org_certification_status is null '||l_from_clause||')) a1';
125
126 p_exp_source_sql := l_sqlstmt || l_qry1 || l_qry2 || l_qry3;
127 /***12.12.2005 npanandi: bug 4880422 fix ends ***/
128
129 /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
130 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
131 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
132
133 p_exp_source_output.EXTEND;
134 l_bind_rec.attribute_name := ':TIME1';
135 l_bind_rec.attribute_value := v_period;
136 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
137 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
138 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
139 /**04.27.2006 npanandi: finished code for dynamic binding of time period dimensions**/
140 END get_org_kpi;
141
142 ---------------------------------------------------------------------------------
143 -- the get_org_certification_result procedure is called by Organization Certification Summary report.
144 PROCEDURE get_org_certification_result(
145 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
146 ,p_exp_source_sql out NOCOPY VARCHAR2
147 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
148 is
149 l_multi_factor NUMBER;
150 l_sqlstmt VARCHAR2(15000);
151 l_act_sqlstmt varchar2(15000);
152 l_sqlstmt1 VARCHAR2(15000);
153 l_subquery_m varchar2(15000);
154 l_subquery1 varchar2(15000);
155 l_subquery2 varchar2(15000);
156 l_subquery3 varchar2(15000);
157 l_subquery4 varchar2(15000);
158 l_grp_by varchar2(100);
159 l_where_clause varchar2(15000);
160 l_drill_url varchar2(15000);
161 l_unmtg_risks_url varchar2(15000);
162 l_ineff_ctrls_url varchar2(15000);
163 l_proc_ineff_ctrls_url varchar2(15000);
164 l_issue_detail_url varchar2(15000);
165 l_proc_certified_url varchar2(15000);
166
167 l_inner_sql varchar2(15000);
168 l_outer_sql varchar2(15000);
169 l_inner_from varchar2(15000);
170 l_org_status_where varchar2(2000);
171
172 /*04.27.2006 npanandi: added 3 variables below for
173 purposes of giving unique names for dynamic binding parameters
174 */
175 l_org_status_where1 varchar2(2000);
176 l_org_status_where2 varchar2(2000);
177 l_org_status_where3 varchar2(2000);
178 l_index number;
179
180 l_org_statuses varchar2(15000);
181
182 l_issues_sql varchar2(15000);
183 l_ctrls_sql varchar2(15000);
184 l_risks_sql varchar2(15000);
185 l_risks_from varchar2(15000);
186 l_ctrls_from varchar2(15000);
187 l_def_proc_sql varchar2(15000);
188 l_def_proc_where varchar2(2000);
189
190 v_yyyymm varchar2(6);
191
192 v_period varchar2(100);
193 l_bind_rec BIS_QUERY_ATTRIBUTES;
194 BEGIN
195 l_issues_sql := ' (select roif.organization_id,count(distinct roif.change_id) as open_issues
196 from rci_open_issues_f roif,eng_engineering_changes eec
197 where eec.change_id=roif.change_id and roif.certification_id is not null and roif.organization_id is not null ';
198
199 l_outer_sql := 'select name VIEWBY
200 ,0 RCI_GRAND_TOTAL
201 ,rocsf.organization_id RCI_ORG_CERT_MEASURE1
202 ,nvl(ro1.org_certified_with_issues,0) RCI_ORG_CERT_MEASURE2
203 ,nvl(ro2.org_certified,0) RCI_ORG_CERT_MEASURE3
204 ,nvl(ro3.org_not_certified,0) RCI_ORG_CERT_MEASURE4
205 ,/*sum(proc_w_ineff_ctrls)*/ nvl(def.processes,0) RCI_ORG_CERT_MEASURE5
206 ,sum(proc_certified_with_issues) RCI_ORG_CERT_MEASURE6
207 ,sum(proc_certified) RCI_ORG_CERT_MEASURE7
208 ,sum(proc_not_certified) RCI_ORG_CERT_MEASURE8
209 ,/*sum(unmitigated_risks)*/ nvl(risk.risk_id,0) RCI_ORG_CERT_MEASURE9
210 ,/*sum(ineffective_controls)*/ nvl(ctrls.controls,0) RCI_ORG_CERT_MEASURE10
211 ,/**sum(**/nvl(op.open_issues,0)/**)**/ RCI_ORG_CERT_MEASURE11
212 ,org_id RCI_ORG_CERT_URL1 from ( ';
213
214 l_inner_sql := 'select distinct rocsf.fin_certification_id
215 , aauv.name
216 ,rocsf.organization_id
217 ,rocsf.org_certified_with_issues
218 ,rocsf.org_certified
219 ,rocsf.org_not_certified
220 ,rocsf.process_id
221 ,rocsf.proc_w_ineff_ctrls
222 ,rocsf.proc_certified_with_issues
223 ,rocsf.proc_certified
224 ,rocsf.proc_not_certified
225 ,rocsf.unmitigated_risks
226 ,rocsf.organization_id org_id
227 ,rocsf.report_date_julian
228 from rci_org_cert_summ_f rocsf
229 ,amw_audit_units_v aauv
230 ,fii_time_day ftd
231 where rocsf.organization_id = aauv.organization_id
232 and rocsf.report_date_julian = ftd.report_date_julian ';
233
234
235 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
236 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
237 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
238
239 l_inner_from := l_inner_from || ' and rocsf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
240 l_issues_sql := l_issues_sql || ' and fin_cert_status = '||p_page_parameter_tbl(i).parameter_id;
241
242
243 END IF;
244
245 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
246 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
247
248 l_inner_from := l_inner_from || ' and rocsf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
249 l_issues_sql := l_issues_sql || ' and fin_cert_type = '||p_page_parameter_tbl(i).parameter_id;
250 END IF;
251
252 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
253 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
254
255 l_inner_from := l_inner_from || ' and rocsf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
256 l_risks_from := l_risks_from || ' and fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
257 l_ctrls_from := l_ctrls_from || ' and fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
258 l_issues_sql := l_issues_sql || ' and fin_cert_id = '||p_page_parameter_tbl(i).parameter_id;
259 l_def_proc_where := l_def_proc_where ||' and fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
260 END IF;
261
262 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
263 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
264
265 l_inner_from := l_inner_from || ' and rocsf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
266 l_risks_from := l_risks_from || ' and natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
267 l_ctrls_from := l_ctrls_from || ' and natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
268 l_def_proc_where := l_def_proc_where ||' and natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
269 END IF;
270
271 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
272 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
273
274 l_inner_from := l_inner_from || ' and rocsf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
275 l_issues_sql := l_issues_sql || ' and roif.organization_id = '||p_page_parameter_tbl(i).parameter_id;
276 l_def_proc_where := l_def_proc_where ||' and organization_id = '||p_page_parameter_tbl(i).parameter_id;
277 END IF;
278
279 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
280 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
281
282 l_inner_from := l_inner_from || ' and rocsf.process_id = '||p_page_parameter_tbl(i).parameter_id;
283 l_risks_from := l_risks_from ||' and process_id = '||p_page_parameter_tbl(i).parameter_id;
284 l_ctrls_from := l_ctrls_from || ' and process_id = '||p_page_parameter_tbl(i).parameter_id;
285 l_issues_sql := l_issues_sql || ' and process_id = '||p_page_parameter_tbl(i).parameter_id;
286 l_def_proc_where := l_def_proc_where ||' and process_id = '||p_page_parameter_tbl(i).parameter_id;
287 END IF;
288
289 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_ORG_CERT_RESULT' AND
290 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
291 if(p_page_parameter_tbl(i).parameter_id = '''EFFECTIVE''')then
292
293 l_inner_from := l_inner_from || ' and rocsf.org_certified > 0 ';
294 elsif(p_page_parameter_tbl(i).parameter_id = '''INEFFECTIVE''') then
295
296 l_inner_from := l_inner_from || ' and rocsf.org_certified_with_issues > 0 ';
297 else
301 END IF;
298
299 l_inner_from := l_inner_from || ' and rocsf.org_not_certified > 0 ';
300 end if;
302
303 /** 10.20.2005 npanandi begin ***/
304 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
305 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
306
307 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
308
309 /*04.27.2006 npanandi: use dynamic binding for time dimensions
310 l_inner_from := l_inner_from || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
311 l_risks_from := l_risks_from || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
312 l_ctrls_from := l_ctrls_from || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
313 l_def_proc_where := l_def_proc_where ||' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
314 */
315 v_period := p_page_parameter_tbl(i).parameter_id;
316 l_inner_from := l_inner_from || ' and ftd.ent_period_id = :TIME1 ';
317 l_risks_from := l_risks_from || ' and ftd.ent_period_id = :TIME2 ';
318 l_ctrls_from := l_ctrls_from || ' and ftd.ent_period_id = :TIME3 ';
319 l_def_proc_where := l_def_proc_where ||' and ftd.ent_period_id = :TIME4 ';
320
321 l_issues_sql := l_issues_sql || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
322 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
323 END IF;
324
325 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
326 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
327
328 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
329
330 /*04.27.2006 npanandi: use dynamic binding for time dimensions
331 l_inner_from := l_inner_from || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
332 l_risks_from := l_risks_from || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
333 l_ctrls_from := l_ctrls_from || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
334 l_def_proc_where := l_def_proc_where ||' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
335 */
336 v_period := p_page_parameter_tbl(i).parameter_id;
337 l_inner_from := l_inner_from || ' and ftd.ent_qtr_id = :TIME1 ';
338 l_risks_from := l_risks_from || ' and ftd.ent_qtr_id = :TIME2 ';
339 l_ctrls_from := l_ctrls_from || ' and ftd.ent_qtr_id = :TIME3 ';
340 l_def_proc_where := l_def_proc_where ||' and ftd.ent_qtr_id = :TIME4 ';
341
342 l_issues_sql := l_issues_sql || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
343 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
344 END IF;
345
346 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
347 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
348
349 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
350
351 /*04.27.2006 npanandi: use dynamic binding for time dimensions
352 l_inner_from := l_inner_from || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
353 l_risks_from := l_risks_from || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
354 l_ctrls_from := l_ctrls_from || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
355 l_def_proc_where := l_def_proc_where ||' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
356 */
357 v_period := p_page_parameter_tbl(i).parameter_id;
358 l_inner_from := l_inner_from || ' and ftd.ent_year_id = :TIME1 ';
359 l_risks_from := l_risks_from || ' and ftd.ent_year_id = :TIME2 ';
360 l_ctrls_from := l_ctrls_from || ' and ftd.ent_year_id = :TIME3 ';
361 l_def_proc_where := l_def_proc_where ||' and ftd.ent_year_id = :TIME4 ';
362
363 l_issues_sql := l_issues_sql || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
364 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
365 END IF;
366 /** 10.20.2005 npanandi end ***/
367
368
369
370 end loop;
371
372 /***04.28.2006 npanandi: what is being done below --- processing the string
373 to make each bind variable unique-named
374 ***/
375 l_org_status_where := l_inner_from;
376 l_index := instr(l_org_status_where,':TIME');
377 l_org_status_where1 := substr(l_org_status_where,0,l_index+4)||'5'||substr(l_org_status_where,l_index+6);
378 l_org_status_where2 := substr(l_org_status_where,0,l_index+4)||'6'||substr(l_org_status_where,l_index+6);
379 l_org_status_where3 := substr(l_org_status_where,0,l_index+4)||'7'||substr(l_org_status_where,l_index+6);
380 /**04.28.2006 npanandi: ends processing for name-uniqueness**/
381
382 l_inner_from := l_inner_from || ') rocsf, ';
383
384 l_org_statuses := ' (select organization_id,count(fin_certification_id) as org_certified_with_issues from (
385 select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
386 from rci_org_cert_summ_f rocsf,fii_time_day ftd
387 where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''INEFFECTIVE'' '||l_org_status_where1||' ) group by organization_id) ro1,
391 where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status=''EFFECTIVE'' '||l_org_status_where2||' ) group by organization_id) ro2,
388 (select organization_id,count(fin_certification_id) as org_certified from (
389 select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
390 from rci_org_cert_summ_f rocsf,fii_time_day ftd
392 (select organization_id,count(fin_certification_id) as org_not_certified from (
393 select distinct fin_certification_id,rocsf.report_date_julian,rocsf.organization_id
394 from rci_org_cert_summ_f rocsf,fii_time_day ftd
395 where rocsf.report_date_julian=ftd.report_date_julian and org_certification_status is null '||l_org_status_where3||' ) group by organization_id) ro3,';
396
397 l_issues_sql := l_issues_sql ||' group by roif.organization_id) op, ';
398 l_def_proc_sql := '(select organization_id,count(process_id) as processes from (
399 select distinct process_id,organization_id,fin_certification_id,certification_result_code,evaluation_result_code
400 from rci_org_proc_dfcy_f ropdf, fii_time_day ftd
401 where ropdf.report_date_julian=ftd.report_date_julian
402 '||l_def_proc_where||' ) group by organization_id) def, ';
403 l_ctrls_sql := '(select organization_id,count(distinct control_id) as controls
404 from RCI_ORG_CERT_CTRLS_F roccf,fii_time_day ftd where 1=1 and roccf.report_date_julian = ftd.report_date_julian '||l_ctrls_from||'
405 group by organization_id) ctrls, ';
406 l_risks_sql := '(select organization_id,count(risk_id) as risk_id from (select distinct organization_id,process_id,risk_id
407 from RCI_ORG_CERT_RISKS_F roccf,fii_time_day ftd
408 where roccf.report_date_julian = ftd.report_date_julian and audit_result_code <> ''EFFECTIVE'' and audit_result_code is not null
409 '||l_risks_from||' )
410 group by organization_id) risk
411 where rocsf.organization_id = op.organization_id(+)
412 and rocsf.organization_id = ro1.organization_id(+)
413 and rocsf.organization_id = ro2.organization_id(+)
414 and rocsf.organization_id = ro3.organization_id(+)
415 and rocsf.organization_id = ctrls.organization_id(+)
416 and rocsf.organization_id = risk.organization_id(+)
417 and rocsf.organization_id = def.organization_id(+)
418 group by name,rocsf.organization_id,ro1.org_certified_with_issues,ro2.org_certified,ro3.org_not_certified,def.processes,ctrls.controls,risk.risk_id,op.open_issues ';
419
420 /***l_inner_from := l_inner_from||' ) group by organization_id,organization_name,org_id '; ***/
421
422 /** 01.01.2006 npanandi: changed the org cert summary SQL according to
423 new financial statement changes
424 l_sqlstmt := l_sqlstmt1 || l_subquery_m || l_subquery1 || l_subquery2 || l_subquery3 || l_subquery4 ||l_where_clause;
425 ***/
426 l_sqlstmt := l_outer_sql||l_inner_sql||l_inner_from||l_org_statuses||l_issues_sql||l_def_proc_sql||l_ctrls_sql||l_risks_sql
427 /**01.26.2006 npanandi: added below line for bug 5000427**/
428 ||' order by RCI_ORG_CERT_MEASURE2 desc ';
429
430
431 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
432 l_act_sqlstmt := 'select VIEWBY,RCI_GRAND_TOTAL,RCI_ORG_CERT_MEASURE1
433 ,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3
434 ,RCI_ORG_CERT_MEASURE4,RCI_ORG_CERT_MEASURE5
435 ,RCI_ORG_CERT_MEASURE6,RCI_ORG_CERT_MEASURE7
436 ,RCI_ORG_CERT_MEASURE8,RCI_ORG_CERT_MEASURE9
437 ,RCI_ORG_CERT_MEASURE10,RCI_ORG_CERT_MEASURE11
438 ,RCI_ORG_CERT_URL1
439 from (select t.*
440 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
441 from ( '||l_sqlstmt ||'
442 ) t ) a
443 order by a.col_rank ';
444
445 p_exp_source_sql := l_act_sqlstmt;
446
447 /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
448 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
449 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
450
451 p_exp_source_output.EXTEND;
452 l_bind_rec.attribute_name := ':TIME1';
453 l_bind_rec.attribute_value := v_period;
454 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
455 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
456 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
457
458 p_exp_source_output.EXTEND;
459 l_bind_rec.attribute_name := ':TIME2';
460 l_bind_rec.attribute_value := v_period;
461 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
462 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
463 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
464
465 p_exp_source_output.EXTEND;
466 l_bind_rec.attribute_name := ':TIME3';
467 l_bind_rec.attribute_value := v_period;
468 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
469 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
470 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
471
472 p_exp_source_output.EXTEND;
473 l_bind_rec.attribute_name := ':TIME4';
474 l_bind_rec.attribute_value := v_period;
475 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
476 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
477 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
481 l_bind_rec.attribute_value := v_period;
478
479 p_exp_source_output.EXTEND;
480 l_bind_rec.attribute_name := ':TIME5';
482 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
483 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
484 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
485
486 p_exp_source_output.EXTEND;
487 l_bind_rec.attribute_name := ':TIME6';
488 l_bind_rec.attribute_value := v_period;
489 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
490 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
491 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
492
493 p_exp_source_output.EXTEND;
494 l_bind_rec.attribute_name := ':TIME7';
495 l_bind_rec.attribute_value := v_period;
496 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
497 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
498 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
499 /**04.27.2006 npanandi: finished code for dynamic binding of time period dimensions**/
500 END get_org_certification_result;
501
502 -- the get_org_cert_prcnt procedure is called by
503 -- Organization Certification Result report.
504 PROCEDURE get_org_cert_prcnt(
505 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
506 ,p_exp_source_sql out NOCOPY VARCHAR2
507 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
508 is
509 l_multi_factor NUMBER;
510 l_sqlstmt VARCHAR2(15000);
511 l_act_sqlstmt varchar2(15000);
512 l_from_clause varchar2(15000);
513 l_getcnt varchar2(15000);
514 l_grp_by varchar2(15000);
515 l_where_clause varchar2(15000);
516
517 l_total number;
518
519 v_period varchar2(100);
520 l_bind_rec BIS_QUERY_ATTRIBUTES;
521 BEGIN
522
523 l_getcnt := 'select count(1) from (
524 select distinct nvl(rocsf.fin_certification_id,0) certification_id,
525 nvl(rocsf.organization_id,0) organization_id,
526 nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status
527 from rci_org_cert_summ_f rocsf
528 ,fii_time_day ftd
529 where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
530
531 l_sqlstmt := 'select rbcrv.value VIEWBY
532 ,count(rocsf.organization_id) RCI_ORG_CERT_MEASURE1 ';
533
534 l_from_clause := ' from rci_bp_cert_result_v rbcrv
535 ,(select distinct nvl(rocsf.fin_certification_id,0) certification_id,
536 nvl(rocsf.organization_id,0) organization_id,
537 /**nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') org_certification_status**/
538 decode(rocsf.org_certification_status,null,''NOT_CERTIFIED'',''EFFECTIVE'',''EFFECTIVE'',''INEFFECTIVE'') org_certification_status
539 from rci_org_cert_summ_f rocsf
540 ,fii_time_day ftd
541 where 1=1 and rocsf.report_date_julian = ftd.report_date_julian ';
542
543
544 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
545
546 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_QTR' AND
547 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
548 l_from_clause := l_from_clause || ' and certification_period_name = '||p_page_parameter_tbl(i).parameter_id;
549
550 l_getcnt := l_getcnt || ' and certification_period_name = '||p_page_parameter_tbl(i).parameter_id;
551 END IF;
552
553 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
554 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
555 l_from_clause := l_from_clause || ' and certification_status = '||p_page_parameter_tbl(i).parameter_id;
556
557 l_getcnt := l_getcnt || ' and certification_status = '||p_page_parameter_tbl(i).parameter_id;
558 END IF;
559
560 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
561 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
562 l_from_clause := l_from_clause || ' and certification_type = '||p_page_parameter_tbl(i).parameter_id;
563
564 l_getcnt := l_getcnt || ' and certification_type = '||p_page_parameter_tbl(i).parameter_id;
565 END IF;
566
567 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
568 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
569 l_from_clause := l_from_clause || ' and fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
570
571 l_getcnt := l_getcnt || ' and fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
572 END IF;
573
574 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
575 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
576 l_from_clause := l_from_clause || ' and natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
577
578 l_getcnt := l_getcnt || ' and natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
579 END IF;
580
581 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
582 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
583 l_from_clause := l_from_clause || ' and organization_id = '||p_page_parameter_tbl(i).parameter_id;
584
588 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
585 l_getcnt := l_getcnt || ' and organization_id = '||p_page_parameter_tbl(i).parameter_id;
586 END IF;
587
589 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
590 l_from_clause := l_from_clause || ' and process_id = '||p_page_parameter_tbl(i).parameter_id;
591
592 l_getcnt := l_getcnt || ' and process_id = '||p_page_parameter_tbl(i).parameter_id;
593 END IF;
594
595 /** 10.20.2005 npanandi begin ***/
596 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
597 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
598 /*04.27.2006 npanandi: use dynamic binding for time dimensions
599 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
600 l_getcnt := l_getcnt || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
601 */
602 v_period := p_page_parameter_tbl(i).parameter_id;
603 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
604 l_getcnt := l_getcnt || ' and ftd.ent_period_id = :TIME2 ';
605 END IF;
606
607 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
608 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
609 /*04.27.2006 npanandi: use dynamic binding for time dimensions
610 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
611 l_getcnt := l_getcnt || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
612 */
613 v_period := p_page_parameter_tbl(i).parameter_id;
614 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
615 l_getcnt := l_getcnt || ' and ftd.ent_qtr_id = :TIME2 ';
616 END IF;
617
618 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
619 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
620 /*04.27.2006 npanandi: use dynamic binding for time dimensions
621 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
622 l_getcnt := l_getcnt || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
623 */
624 v_period := p_page_parameter_tbl(i).parameter_id;
625 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
626 l_getcnt := l_getcnt || ' and ftd.ent_year_id = :TIME2 ';
627 END IF;
628 /** 10.20.2005 npanandi end ***/
629
630 end loop;
631
632 l_getcnt := l_getcnt || ' )';
633 EXECUTE IMMEDIATE l_getcnt INTO l_total using v_period;
634
635 if( l_total = 0) then
636 l_sqlstmt := l_sqlstmt || ', 0 RCI_ORG_CERT_MEASURE2,0 RCI_ORG_CERT_MEASURE3,rbcrv.id RCI_ORG_CERT_URL1 ';
637 else
638 l_sqlstmt := l_sqlstmt || ', round(count(rocsf.organization_id)/'||l_total||'*100,2) RCI_ORG_CERT_MEASURE2
639 ,0 RCI_ORG_CERT_MEASURE3,rbcrv.id RCI_ORG_CERT_URL1 ';
640 end if;
641
642 l_grp_by := ' ) rocsf
643 where rbcrv.id = rocsf.org_certification_status (+)
644 group by rbcrv.value,rbcrv.id ';
645
646 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
647 l_act_sqlstmt := 'select VIEWBY
648 ,RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_URL1
649 from (select t.*
650 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
651 from ( '||l_sqlstmt || l_from_clause || l_grp_by||'
652 ) t ) a
653 order by a.col_rank ';
654 /**p_exp_source_sql := l_sqlstmt || l_from_clause || l_grp_by;**/
655 p_exp_source_sql := l_act_sqlstmt;
656
657 /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
658 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
659 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
660
661 p_exp_source_output.EXTEND;
662 l_bind_rec.attribute_name := ':TIME1';
663 l_bind_rec.attribute_value := v_period;
664 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
665 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
666 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
667 /**04.27.2006 npanandi: finished code for dynamic binding of time period dimensions**/
668 END get_org_cert_prcnt;
669
670 -- the get_unmitigated_risks procedure is called by
671 -- Organization Certification Unmitigated Risks List report.
672 PROCEDURE get_unmitigated_risks(
673 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
674 ,p_exp_source_sql out NOCOPY VARCHAR2
675 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
676 is
677 l_sqlstmt VARCHAR2(15000);
678 ---12.22.2005 npanandi: added l_act_sqlstmt below
679 l_act_sqlstmt VARCHAR2(15000);
680 l_from_clause varchar2(15000);
681 l_getcnt varchar2(15000);
682 l_grp_by varchar2(15000);
683 l_where_clause varchar2(15000);
684
685 l_total number;
686
687 l_drill_url varchar2(15000);
688
689 v_period varchar2(100);
690 l_bind_rec BIS_QUERY_ATTRIBUTES;
691 BEGIN
692
693 l_sqlstmt := 'select distinct art.name RCI_ORG_CERT_MEASURE1
694 ,aapv.project_name RCI_ORG_CERT_MEASURE2
695 ,aauv.name RCI_ORG_CERT_MEASURE3
696 ,apv.display_name RCI_ORG_CERT_MEASURE4
697 ,/*nvl(arb.material,''N'')*/ flv.meaning RCI_ORG_CERT_MEASURE5
701 ,papf.full_name RCI_ORG_CERT_MEASURE9
698 ,al1.meaning RCI_ORG_CERT_MEASURE6
699 ,al2.meaning RCI_ORG_CERT_MEASURE7
700 ,rfaev.value RCI_ORG_CERT_MEASURE8
702 ,rocrf.last_evaluated_on RCI_ORG_CERT_MEASURE10
703 ,arb.risk_rev_id RCI_ORG_CERT_URL1
704 ,arb.risk_id RCI_ORG_CERT_URL2
705 ,rocrf.process_id RCI_ORG_CERT_URL3
706 ,rocrf.organization_id RCI_ORG_CERT_URL4
707 from rci_org_cert_risks_f rocrf
708 ,amw_risks_b arb
709 ,amw_risks_tl art
710 ,amw_audit_projects_v aapv
711 ,amw_audit_units_v aauv
712 ,amw_process_vl apv
713 ,amw_lookups al1
714 ,amw_lookups al2
715 ,RCI_FS_ACCT_EVAL_V rfaev
716 ,PER_ALL_PEOPLE_F papf
717 ,FND_USER fu
718 /** 10.20.2005 npanandi begin ***/
719 ,fii_time_day ftd
720 /** 10.20.2005 npanandi end ***/
721 /**01.31.2006 npanandi: changing reference to fnd_lookups below because
722 of lang issues **/
723 ,/*fnd_lookup_values*/fnd_lookups flv
724 where arb.risk_rev_id = art.risk_rev_id
725 and upper(arb.curr_approved_flag) = ''Y''
726 and art.language = userenv(''LANG'')
727 and arb.risk_id = rocrf.risk_id
728 and rocrf.organization_id = aauv.organization_id
729 and aapv.audit_project_id = rocrf.project_id
730 and aauv.organization_id = rocrf.organization_id
731 and apv.process_id = rocrf.process_id
732 and apv.approval_date is not null
733 and apv.approval_end_date is null
734 and rocrf.risk_impact = al1.lookup_code(+)
735 and al1.lookup_type(+) = ''AMW_IMPACT''
736 and al1.enabled_flag(+) = ''Y''
737 and rocrf.likelihood = al2.lookup_code(+)
738 and al2.lookup_type(+) = ''AMW_LIKELIHOOD''
739 and al2.enabled_flag(+) = ''Y''
740 and rocrf.audit_result_code = rfaev.id
741 and rfaev.OBJ_NAME = ''AMW_ORG_PROCESS_RISK''
742 and rocrf.last_evaluator_id = fu.user_id
743 and fu.employee_id = papf.person_id
744 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
745 and papf.employee_number is not null
746 and rocrf.report_date_julian = ftd.report_date_julian
747 and nvl(arb.material,''N'') = flv.lookup_code(+)
748 and flv.lookup_type(+) = ''AMW_YES_NO''
749 AND rocrf.audit_result_code <> ''EFFECTIVE'' ';
750
751
752
753 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
754
755 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
756 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
757 l_from_clause := l_from_clause || ' and rocrf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
758 END IF;
759
760 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
761 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
762 l_from_clause := l_from_clause || ' and rocrf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
763 END IF;
764
765 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
766 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
767 l_from_clause := l_from_clause || ' and rocrf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
768 END IF;
769
770 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
771 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
772 l_from_clause := l_from_clause || ' and rocrf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
773 END IF;
774
775 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
776 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
777 l_from_clause := l_from_clause || ' and rocrf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
778 END IF;
779
780 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
781 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
782 l_from_clause := l_from_clause || ' and rocrf.process_id = '||p_page_parameter_tbl(i).parameter_id;
783 END IF;
784
785 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' AND
786 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
787 l_from_clause := l_from_clause || ' and rfaev.id = '||p_page_parameter_tbl(i).parameter_id;
788 END IF;
789
790 /** 10.20.2005 npanandi begin ***/
791 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
792 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
793 /*04.27.2006 npanandi: use dynamic binding for below time dimension
794 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
795 */
796 v_period := p_page_parameter_tbl(i).parameter_id;
797 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
798 END IF;
799
800 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
801 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
802 /*04.27.2006 npanandi: use dynamic binding for below time dimension
803 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
804 */
805 v_period := p_page_parameter_tbl(i).parameter_id;
806 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
810 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
807 END IF;
808
809 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
811 /*04.27.2006 npanandi: use dynamic binding for below time dimension
812 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
813 */
814 v_period := p_page_parameter_tbl(i).parameter_id;
815 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
816 END IF;
817 /** 10.20.2005 npanandi end ***/
818
819 /**** 12.09.2005 npanandi: bug 4862320 fix ****/
820 ----Impact parameter
821 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_LINE' AND
822 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
823 l_from_clause := l_from_clause || ' and rocrf.risk_impact = '||p_page_parameter_tbl(i).parameter_id;
824 END IF;
825
826 ----Likelihood parameter
827 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' AND
828 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
829 l_from_clause := l_from_clause || ' and rocrf.likelihood = '||p_page_parameter_tbl(i).parameter_id;
830 END IF;
831 /**** 12.09.2005 npanandi: bug 4862320 fix end ****/
832
833 /**** 12.16.2005 npanandi: bug 4908472 fix --- added material parameter ****/
834 ----Material parameter
835 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY+DUMMY_LEVEL' AND
836 p_page_parameter_tbl(i).parameter_id is NOT null and p_page_parameter_tbl(i).parameter_id <> 'ALL') THEN
837
838 l_from_clause := l_from_clause || ' and nvl(arb.material,''N'') = '''||p_page_parameter_tbl(i).parameter_id||''' ';
839 END IF;
840 /**** 12.16.2005 npanandi: ends bug 4908472 fix****/
841
842
843 end loop;
844
845
846 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
847 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
848 ,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
849 ,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
850 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
851 ,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
852 ,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2
853 ,RCI_ORG_CERT_URL3,RCI_ORG_CERT_URL4
854 from (select t.*
855 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
856 from ( '||l_sqlstmt || l_from_clause||'
857 ) t ) a
858 order by a.col_rank ';
859
860
861 p_exp_source_sql := l_act_sqlstmt;
862
863 /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
864 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
865 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
866
867 p_exp_source_output.EXTEND;
868 l_bind_rec.attribute_name := ':TIME1';
869 l_bind_rec.attribute_value := v_period;
870 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
871 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
872 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
873 /**04.27.2006 npanandi: finished code for dynamic binding of time period dimensions**/
874
875 END get_unmitigated_risks;
876
877 -- the get_control_list procedure is called by
878 -- Organization Certification Control Detail List report.
879 PROCEDURE get_control_list(
880 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
881 ,p_exp_source_sql out NOCOPY VARCHAR2
882 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
883 is
884 l_sqlstmt VARCHAR2(15000);
885 ---12.22.2005 npanandi: added l_act_sqlstmt below
886 l_act_sqlstmt VARCHAR2(15000);
887 l_from_clause varchar2(15000);
888 l_getcnt varchar2(15000);
889 l_grp_by varchar2(15000);
890 l_where_clause varchar2(15000);
891
892 l_total number;
893 l_drill_url varchar2(15000);
894
895 v_period varchar2(100);
896 l_bind_rec BIS_QUERY_ATTRIBUTES;
897 BEGIN
898
899 l_sqlstmt := 'select distinct act.name RCI_ORG_CERT_MEASURE1
900 ,act.description RCI_ORG_CERT_MEASURE2
901 ,aauv.name RCI_ORG_CERT_MEASURE3
902 ,al1.meaning RCI_ORG_CERT_MEASURE4
903 ,fl1.meaning RCI_ORG_CERT_MEASURE5
904 ,fl2.meaning RCI_ORG_CERT_MEASURE6
905 ,rfaev.value RCI_ORG_CERT_MEASURE7
906 ,aovt1.OPINION_VALUE_NAME RCI_ORG_CERT_MEASURE8
907 ,aovt2.OPINION_VALUE_NAME RCI_ORG_CERT_MEASURE9
908 ,papf.FULL_NAME RCI_ORG_CERT_MEASURE10
909 ,roccf.last_evaluated_on RCI_ORG_CERT_MEASURE11
910 ,acb.control_rev_id RCI_ORG_CERT_URL1
911 ,acb.control_id RCI_ORG_CERT_URL2
912 from rci_org_cert_ctrls_f roccf
913 ,amw_controls_b acb
914 ,amw_controls_tl act
915 ,amw_audit_units_v aauv
916 ,amw_lookups al1
917 ,fnd_lookups fl1
918 ,fnd_lookups fl2
919 ,amw_opinion_values_tl aovt1
920 ,amw_opinion_values_tl aovt2
921 ,RCI_FS_ACCT_EVAL_V rfaev
922 ,PER_ALL_PEOPLE_F papf
923 ,FND_USER fu
924 /** 10.20.2005 npanandi begin ***/
925 ,fii_time_day ftd
926 /** 10.20.2005 npanandi end ***/
927 where acb.control_rev_id = act.control_rev_id
931 and roccf.organization_id = aauv.organization_id
928 and upper(acb.curr_approved_flag) = ''Y''
929 and act.language = userenv(''LANG'')
930 and acb.control_id = roccf.control_id
932 and al1.LOOKUP_CODE = acb.control_type/*roccf.CONTROL_TYPE*/
933 and al1.LOOKUP_TYPE = ''AMW_CONTROL_TYPE''
934 and fl1.LOOKUP_CODE = roccf.KEY_CONTROL
935 and fl1.LOOKUP_TYPE = ''YES_NO''
936 and fl2.LOOKUP_CODE = roccf.DISCLOSURE_CONTROL
937 and fl2.LOOKUP_TYPE = ''YES_NO''
938 and roccf.audit_result_code = rfaev.id
939 and rfaev.OBJ_NAME = ''AMW_ORG_CONTROL''
940 and roccf.DES_EFF_ID = aovt1.OPINION_VALUE_ID(+)
941 and aovt1.LANGUAGE(+) = userenv(''LANG'')
942 and roccf.OP_EFF_ID = aovt2.OPINION_VALUE_ID(+)
943 and aovt2.LANGUAGE(+) = userenv(''LANG'')
944 and roccf.last_evaluated_by_id = fu.user_id
945 and fu.employee_id = papf.person_id
946 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
947 and papf.employee_number is not null
948 and roccf.report_date_julian = ftd.report_date_julian';
949
950
951 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
952 /**l_from_clause := l_from_clause ||' parameter_name: '||p_page_parameter_tbl(i).parameter_name;
953 l_from_clause := l_from_clause ||' parameter_value: '||p_page_parameter_tbl(i).parameter_id;**/
954
955 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
956 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
957 l_from_clause := l_from_clause || ' and roccf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
958 END IF;
959
960 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
961 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
962 l_from_clause := l_from_clause || ' and roccf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
963 END IF;
964
965 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
966 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
967 l_from_clause := l_from_clause || ' and roccf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
968 END IF;
969
970 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
971 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
972 l_from_clause := l_from_clause || ' and roccf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
973 END IF;
974
975 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
976 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
977 l_from_clause := l_from_clause || ' and roccf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
978 END IF;
979
980 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
981 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
982 l_from_clause := l_from_clause || ' and roccf.process_id = '||p_page_parameter_tbl(i).parameter_id;
983 END IF;
984
985 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_ACCT_EVAL' AND
986 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
987 l_from_clause := l_from_clause || ' and roccf.audit_result_code = '||p_page_parameter_tbl(i).parameter_id;
988 END IF;
989
990 ----- 10.20.2005 npanandi begin
991 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
992 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
993 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
994 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
995 */
996 v_period := p_page_parameter_tbl(i).parameter_id;
997 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
998 END IF;
999
1000 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
1001 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1002 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
1003 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1004 */
1005 v_period := p_page_parameter_tbl(i).parameter_id;
1006 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
1007 END IF;
1008
1009 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
1010 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1011 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
1012 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1013 */
1014 v_period := p_page_parameter_tbl(i).parameter_id;
1015 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
1016 END IF;
1017 ---- 10.20.2005 npanandi end
1018
1019 ----12.05.2005 npanandi start: bug 4862326
1020 --l_from_clause := l_from_clause || 'name: '|| p_page_parameter_tbl(i).parameter_name ||', id: '||p_page_parameter_tbl(i).parameter_id;
1021 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY+DUMMY_LEVEL' AND
1022 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1023 ---l_from_clause := l_from_clause || 'name: '|| p_page_parameter_tbl(i).parameter_name ||', id: '||p_page_parameter_tbl(i).parameter_id;
1024 if(p_page_parameter_tbl(i).parameter_id = 'Y')then
1028 end if;
1025 l_from_clause := l_from_clause || ' and roccf.key_control = ''Y''';
1026 elsif(p_page_parameter_tbl(i).parameter_id = 'N')then
1027 l_from_clause := l_from_clause || ' and roccf.key_control = ''N''';
1029 END IF;
1030
1031 --l_from_clause := l_from_clause || 'name: '|| p_page_parameter_tbl(i).parameter_name ||', id: '||p_page_parameter_tbl(i).parameter_id;
1032 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY_DIMENSION+DUMMY_DIMENSION_LEVEL' AND
1033 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1034 ---l_from_clause := l_from_clause || 'name: '|| p_page_parameter_tbl(i).parameter_name ||', id: '||p_page_parameter_tbl(i).parameter_id;
1035 if(p_page_parameter_tbl(i).parameter_id = 'Y')then
1036 l_from_clause := l_from_clause || ' and roccf.disclosure_control = ''Y''';
1037 elsif(p_page_parameter_tbl(i).parameter_id = 'N')then
1038 l_from_clause := l_from_clause || ' and roccf.disclosure_control = ''N''';
1039 end if;
1040 END IF;
1041 ----12.05.2005 npanandi end:
1042
1043 end loop;
1044
1045 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
1046 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
1047 ,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
1048 ,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
1049 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
1050 ,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
1051 ,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2
1052 from (select t.*
1053 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1054 from ( '||l_sqlstmt || l_from_clause||'
1055 ) t ) a
1056 order by a.col_rank ';
1057
1058
1059 p_exp_source_sql := l_act_sqlstmt;
1060
1061 /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1062 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1063 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1064
1065 p_exp_source_output.EXTEND;
1066 l_bind_rec.attribute_name := ':TIME1';
1067 l_bind_rec.attribute_value := v_period;
1068 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1069 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1070 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
1071 /**04.28.2006 npanandi: finished code for dynamic binding of time period dimensions**/
1072
1073
1074 END get_control_list;
1075
1076 -- the get_issue_detail procedure is called by
1077 -- Issue Detail List report.
1078 PROCEDURE get_issue_detail(
1079 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1080 ,p_exp_source_sql out NOCOPY VARCHAR2
1081 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1082 is
1083 l_sqlstmt VARCHAR2(15000);
1084 l_act_sqlstmt varchar2(15000);
1085 l_from_clause varchar2(15000);
1086 l_getcnt varchar2(15000);
1087 l_grp_by varchar2(15000);
1088 l_where_clause varchar2(15000);
1089 l_end_date varchar2(35);
1090
1091 l_total number;
1092 l_drill_url varchar2(15000);
1093
1094 v_yyyymm varchar2(6);
1095 l_dummy varchar2(10);
1096 BEGIN
1097
1098
1099 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
1100
1101 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
1102 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1103 /*l_from_clause := l_from_clause || ' and acv.certification_status = '||p_page_parameter_tbl(i).parameter_id;*/
1104 l_from_clause := l_from_clause || ' and open_issues.fin_cert_status = '||p_page_parameter_tbl(i).parameter_id;
1105 END IF;
1106
1107 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
1108 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1109 /*l_from_clause := l_from_clause || ' and acv.certification_type = '||p_page_parameter_tbl(i).parameter_id;*/
1110 l_from_clause := l_from_clause || ' and open_issues.fin_cert_type = '||p_page_parameter_tbl(i).parameter_id;
1111 END IF;
1112
1113 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
1114 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1115 /**01.12.2006 npanandi: changed the below to join with acv.certification_id
1116 instead of open_issues.certification_id**/
1117 /*l_from_clause := l_from_clause || ' and acv.certification_id = '||p_page_parameter_tbl(i).parameter_id;*/
1118 l_from_clause := l_from_clause || ' and open_issues.fin_cert_id = '||p_page_parameter_tbl(i).parameter_id;
1119 END IF;
1120
1121 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
1122 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1123 l_from_clause := l_from_clause || ' and open_issues.organization_id = '||p_page_parameter_tbl(i).parameter_id;
1124 END IF;
1125
1126 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
1127 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1128 l_from_clause := l_from_clause || ' and open_issues.process_id = '||p_page_parameter_tbl(i).parameter_id;
1129 END IF;
1130
1131 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' AND
1132 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1136 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' AND
1133 l_from_clause := l_from_clause || ' and open_issues.priority_code = '||p_page_parameter_tbl(i).parameter_id;
1134 END IF;
1135
1137 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1138 l_from_clause := l_from_clause || ' and open_issues.reason_code = '||p_page_parameter_tbl(i).parameter_id;
1139 END IF;
1140
1141 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' AND
1142 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1143 l_from_clause := l_from_clause || ' and open_issues.status_code = '||p_page_parameter_tbl(i).parameter_id;
1144 END IF;
1145
1146 /** 10.20.2005 npanandi begin ***/
1147 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
1148 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1149 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
1150 select min(distinct last_day(to_date(to_char(ent_period_end_date,'YYYYMM'),'YYYYMM')))
1151 into l_end_date /*gives in the form 30-SEP-06*/
1152 from fii_time_day
1153 where ent_period_id=p_page_parameter_tbl(i).parameter_id;
1154 ---l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
1155 l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
1156 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
1157 END IF;
1158
1159 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
1160 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1161 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
1162 select min(distinct last_day(to_date(to_char(ent_qtr_end_date,'YYYYMM'),'YYYYMM')))
1163 into l_end_date /*gives in the form 30-SEP-06*/
1164 from fii_time_day
1165 where ent_qtr_id=p_page_parameter_tbl(i).parameter_id;
1166 ---l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1167 l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
1168 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
1169 END IF;
1170
1171 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
1172 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1173 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
1174 select min(distinct last_day(to_date(to_char(ent_year_end_date,'YYYYMM'),'YYYYMM')))
1175 into l_end_date /*gives in the form 30-SEP-06*/
1176 from fii_time_day
1177 where ent_year_id=p_page_parameter_tbl(i).parameter_id;
1178
1179 ---l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1180 l_from_clause := l_from_clause || ' and ((eec.status_code not in (0,11) and eec.initiation_date < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
1181 ) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )) ';
1182 END IF;/** 10.20.2005 npanandi end ***/
1183
1184 /**06.12.2006 npanandi: bug 5014235 fix for links**/
1185 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY_DIMENSION+DUMMY_DIMENSION_LEVEL' AND
1186 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1187 l_dummy := p_page_parameter_tbl(i).parameter_id;
1188 /***l_from_clause := l_from_clause || ' and
1189 decode((abs((nvl(eec.need_by_date,sysdate)-eec.initiation_date))
1190 +abs((last_day(to_date('||v_yyyymm||',''YYYYMM''))-nvl(eec.need_by_date,sysdate))))
1191 ,abs((last_day(to_date('||v_yyyymm||',''YYYYMM''))-eec.initiation_date)),1,0) past_due ';
1192 ***/
1193 end if;
1194 /**06.12.2006 npanandi: bug 5014235 fix ends**/
1195 end loop;
1196 /*
1197 if(l_dummy = '''PASTDUE''') then
1198 l_from_clause := l_from_clause || ' and
1199 (abs((to_number(to_char(nvl(eec.need_by_date,sysdate),''J''))-to_number(to_char(eec.initiation_date,''J''))
1200 +abs((to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(nvl(eec.need_by_date,sysdate),''J'')))))))
1204 to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(eec.initiation_date,''J'')) in (0,1) ';
1201 =abs((to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(eec.initiation_date,''J'')))) ';
1202 elsif(l_dummy = '''AGEBUCK1''')then
1203 l_from_clause := l_from_clause || ' and
1205 elsif(l_dummy = '''AGEBUCK2''')then
1206 l_from_clause := l_from_clause || ' and
1207 to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(eec.initiation_date,''J'')) in (2,3,4,5) ';
1208 elsif(l_dummy = '''AGEBUCK3''')then
1209 l_from_clause := l_from_clause || ' and
1210 to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(eec.initiation_date,''J'')) in (6,7,8,9,10) ';
1211 elsif(l_dummy = '''AGEBUCK4''')then
1212 l_from_clause := l_from_clause || ' and
1213 to_number(to_char(last_day(to_date('||v_yyyymm||',''YYYYMM'')),''J''))-to_number(to_char(eec.initiation_date,''J'')) > 10 ';
1214 end if;
1215 */--commented by dpatel 20.09.2006 (bug 5533367)
1216 if(l_dummy = '''PASTDUE''') then
1217 l_from_clause := l_from_clause || ' and to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) > to_number(to_char(eec.need_by_date,''J'')) ';
1218 elsif(l_dummy = '''AGEBUCK1''')then
1219 l_from_clause := l_from_clause || ' and to_number(to_char(to_date('''||v_yyyymm||''',''YYYYMM''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) between 0 and 1 ';
1220 elsif(l_dummy = '''AGEBUCK2''')then
1221 l_from_clause := l_from_clause || ' and to_number(to_char(to_date('''||v_yyyymm||''',''YYYYMM''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) between 2 and 5 ';
1222 elsif(l_dummy = '''AGEBUCK3''')then
1223 l_from_clause := l_from_clause || ' and to_number(to_char(to_date('''||v_yyyymm||''',''YYYYMM''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) between 6 and 10 ';
1224 elsif(l_dummy = '''AGEBUCK4''')then
1225 l_from_clause := l_from_clause || ' and to_number(to_char(to_date('''||v_yyyymm||''',''YYYYMM''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) > 10 ';
1226 end if;
1227
1228
1229 l_sqlstmt := 'select distinct eec.change_name RCI_ORG_CERT_MEASURE1
1230 ,aecv.full_name RCI_ORG_CERT_MEASURE2
1231 ,ecst.status_name RCI_ORG_CERT_MEASURE3
1232 ,ecp.description RCI_ORG_CERT_MEASURE4
1233 ,round((sysdate - eec.initiation_date)) RCI_ORG_CERT_MEASURE5
1234 ,eec.need_by_date RCI_ORG_CERT_MEASURE6
1235 /*,decode(eec.need_by_date,null,round(sysdate - eec.initiation_date),round(sysdate - eec.need_by_date)) RCI_ORG_CERT_MEASURE7*/
1236 ,to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) RCI_ORG_CERT_MEASURE7
1237 , eec.change_notice RCI_ORG_CERT_MEASURE9
1238 ,eec.change_id RCI_ORG_CERT_URL1
1239 from rci_open_issues_f open_issues,
1240 /*amw_audit_units_v aauv,*/
1241 amw_latest_revisions_v alrv,
1242 eng_engineering_changes eec,
1243 amw_employees_current_v aecv,
1244 eng_change_statuses_tl ecst,
1245 eng_change_priorities ecp,
1246 fii_time_day ftd
1247 where /*aauv.organization_id=open_issues.organization_id(+)
1248 and*/ open_issues.change_id=eec.change_id
1249 and aecv.party_id = eec.assignee_id
1250 and eec.status_code = ecst.status_code
1251 and ecst.language = userenv(''LANG'')
1252 and eec.priority_code = ecp.eng_change_priority_code(+)
1253 /*and nvl(open_issues.process_id,-1) = nvl(alrv.process_id,-1)
1254 and open_issues.certification_id is not null
1255 and open_issues.organization_id is not null*/
1256 and open_issues.report_date_julian = ftd.report_date_julian';
1257
1258 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
1259 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2,RCI_ORG_CERT_MEASURE3
1260 ,RCI_ORG_CERT_MEASURE4,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
1261 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_URL1
1262 from (select t.*
1263 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1264 from ( '||l_sqlstmt || l_from_clause||'
1265 ) t ) a
1266 order by a.col_rank ';
1267
1268 p_exp_source_sql := l_act_sqlstmt;
1269 ---p_exp_source_sql := l_sqlstmt||l_where_clause||l_grp_by;
1270 END get_issue_detail;
1271
1272 -- the get_deficient_processes procedure is called by
1273 -- Process Deficiency Detail report.
1274 PROCEDURE get_deficient_processes(
1275 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1276 ,p_exp_source_sql out NOCOPY VARCHAR2
1277 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1278 is
1279 l_sqlstmt VARCHAR2(15000);
1280 ---12.22.2005 npanandi: added l_act_sqlstmt below
1281 l_act_sqlstmt VARCHAR2(15000);
1282 l_from_clause varchar2(15000);
1283 l_getcnt varchar2(15000);
1284 l_grp_by varchar2(15000);
1285 l_where_clause varchar2(15000);
1286
1287 l_total number;
1288 l_drill_url varchar2(15000);
1289
1290 v_period varchar2(100);
1291 l_bind_rec BIS_QUERY_ATTRIBUTES;
1292 BEGIN
1293
1294 /*** distinct is OK here, because the rolled up numbers from
1295 the previous page report (Organization Certification Summary)
1296 contains the Processes w/ Ineffective Controls, taking into
1297 account the Key Accounts
1298 ***/
1299 l_sqlstmt := 'select distinct alrv.display_name RCI_ORG_CERT_MEASURE1
1300 ,aauv.name RCI_ORG_CERT_MEASURE2
1301 ,fl1.meaning RCI_ORG_CERT_MEASURE3
1302 ,fl2.meaning RCI_ORG_CERT_MEASURE4
1303 ,acv.certification_name RCI_ORG_CERT_MEASURE5
1304 ,rbcrv.value RCI_ORG_CERT_MEASURE6
1305 ,papf1.full_name RCI_ORG_CERT_MEASURE7
1306 ,aapv.project_name RCI_ORG_CERT_MEASURE8
1307 ,rfaev.value RCI_ORG_CERT_MEASURE9
1308 ,papf2.full_name RCI_ORG_CERT_MEASURE10
1309 ,ropdf.last_evaluated_on RCI_ORG_CERT_MEASURE11
1310 ,ropdf.unmitigated_risks RCI_ORG_CERT_MEASURE12
1311 ,ropdf.ineffective_controls RCI_ORG_CERT_MEASURE13
1312 ,alrv.process_id RCI_ORG_CERT_URL1
1313 ,aauv.organization_id RCI_ORG_CERT_URL2
1314 ,alrv.revision_number RCI_ORG_CERT_URL3
1315 from rci_org_proc_dfcy_f ropdf
1316 ,amw_latest_revisions_v alrv
1317 ,amw_audit_units_v aauv
1318 ,fnd_lookups fl1
1319 ,fnd_lookups fl2
1320 ,amw_certification_vl acv
1321 ,RCI_BP_CERT_RESULT_V rbcrv
1322 ,(select full_name,person_id from PER_ALL_PEOPLE_F where employee_number is not null and (trunc(sysdate) between effective_start_date and effective_end_date)) papf1
1323 ,FND_USER fu1
1324 ,amw_audit_projects_v aapv
1325 ,rci_fs_acct_eval_v rfaev
1326 ,(select full_name,person_id from PER_ALL_PEOPLE_F where employee_number is not null and (trunc(sysdate) between effective_start_date and effective_end_date)) papf2
1327 ,FND_USER fu2
1328 /** 10.20.2005 npanandi begin ***/
1329 ,fii_time_day ftd
1330 /** 10.20.2005 npanandi end ***/
1331 where ropdf.process_id = alrv.process_id
1332 and ropdf.organization_id = aauv.organization_id
1333 and ropdf.significant_process_flag = fl1.lookup_code
1334 and fl1.lookup_type = ''YES_NO''
1335 and ropdf.standard_process_flag = fl2.lookup_code
1336 and fl2.lookup_type = ''YES_NO''
1337 and ropdf.fin_certification_id = acv.certification_id
1338 /**and acv.object_type = ''PROCESS''**/
1339 and nvl(ropdf.certification_result_code,''NOT_CERTIFIED'') = rbcrv.id(+)
1340 and ropdf.certified_by_id = fu1.user_id(+)
1341 and fu1.employee_id = papf1.person_id(+)
1342 and ropdf.project_id = aapv.audit_project_id(+)
1343 and ropdf.evaluation_result_code = rfaev.id(+)
1344 and rfaev.obj_name(+)=''AMW_ORG_PROCESS''
1345 and ropdf.last_evaluated_by_id = fu2.user_id(+)
1346 and fu2.employee_id = papf2.person_id(+)
1347 and ropdf.report_date_julian = ftd.report_date_julian';
1348 ---order by alrv.display_name,aauv.name,acv.certification_name';
1349
1350
1351 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
1352 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
1353 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1354 l_from_clause := l_from_clause || ' and ropdf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
1355 END IF;
1356
1357 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
1358 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1359 l_from_clause := l_from_clause || ' and ropdf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
1360 END IF;
1361
1362 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
1363 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1364 l_from_clause := l_from_clause || ' and ropdf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
1365 END IF;
1366
1367 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
1368 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1369 l_from_clause := l_from_clause || ' and ropdf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
1370 END IF;
1371
1372 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
1373 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1374 l_from_clause := l_from_clause || ' and ropdf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
1375 END IF;
1376
1377 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
1378 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1379 l_from_clause := l_from_clause || ' and ropdf.process_id = '||p_page_parameter_tbl(i).parameter_id;
1380 END IF;
1381
1382 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_CERT_RESULT' AND
1383 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1384 if(p_page_parameter_tbl(i).parameter_id = '''NOT_CERTIFIED''') then
1385 l_from_clause := l_from_clause || ' and ropdf.certification_result_code IS NULL ';
1386 else
1387 l_from_clause := l_from_clause || ' and ropdf.certification_result_code = '||p_page_parameter_tbl(i).parameter_id;
1388 end if;
1389 END IF;
1390
1391 /** 10.20.2005 npanandi begin ***/
1392 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
1393 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1394 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
1395 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
1396 */
1397 v_period := p_page_parameter_tbl(i).parameter_id;
1398 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
1399 END IF;
1400
1401 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
1402 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1403 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
1404 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1405 */
1406 v_period := p_page_parameter_tbl(i).parameter_id;
1407 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
1408 END IF;
1409
1410 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
1411 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1412 /*04.28.2006 npanandi: use dynamic binding for time dimensions below
1413 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1414 **/
1415 v_period := p_page_parameter_tbl(i).parameter_id;
1416 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
1417 END IF;
1418 /** 10.20.2005 npanandi end ***/
1419
1420 /** 12.12.2005 npanandi: bug 4862301 fix **/
1421 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY+DUMMY_LEVEL' AND
1422 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1423 if(p_page_parameter_tbl(i).parameter_id = 'Y') then
1424 ---l_from_clause := l_from_clause || ' and ropdf.significant_process_flag = '''||p_page_parameter_tbl(i).parameter_id||'''';
1425 l_from_clause := l_from_clause || ' and ropdf.significant_process_flag = ''Y''';
1426 elsif(p_page_parameter_tbl(i).parameter_id = 'N') then
1427 l_from_clause := l_from_clause || ' and ropdf.significant_process_flag = ''N''';
1428 end if;
1429 ---l_from_clause := l_from_clause || '################# p_page_parameter_tbl('||i||').parameter_name: '||p_page_parameter_tbl(i).parameter_name||', p_page_parameter_tbl('||i||').parameter_id: '||p_page_parameter_tbl(i).parameter_id;
1430 END IF;
1431
1435 ----l_from_clause := l_from_clause || ' and ropdf.key_control = '''||p_page_parameter_tbl(i).parameter_id||'''';
1432 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY_DIMENSION+DUMMY_DIMENSION_LEVEL' AND
1433 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1434 if(p_page_parameter_tbl(i).parameter_id = 'Y') then
1436 l_from_clause := l_from_clause || ' and ropdf.key_control = ''Y''';
1437 elsif(p_page_parameter_tbl(i).parameter_id = 'N') then
1438 l_from_clause := l_from_clause || ' and ropdf.key_control = ''N''';
1439 end if;
1440 END IF;
1441 /** 12.12.2005 npanandi: bug 4862301 fix ends **/
1442 end loop;
1443
1444 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
1445 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
1446 ,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
1447 ,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
1448 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
1449 ,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
1450 ,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12,RCI_ORG_CERT_MEASURE13
1451 ,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
1452 from (select t.*
1453 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1454 from ( '||l_sqlstmt || l_from_clause||'
1455 ) t ) a
1456 order by a.col_rank ';
1457
1458
1459 p_exp_source_sql := l_act_sqlstmt;
1460
1461 /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1462 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1463 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1464
1465 p_exp_source_output.EXTEND;
1466 l_bind_rec.attribute_name := ':TIME1';
1467 l_bind_rec.attribute_value := v_period;
1468 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1469 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1470 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
1471 /**04.28.2006 npanandi: finished code for dynamic binding of time period dimensions**/
1472 END get_deficient_processes;
1473
1474 -- the get_org_certification_detail procedure is called by
1475 -- Organization Certification Detail report.
1476 PROCEDURE get_org_certification_detail(
1477 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1478 ,p_exp_source_sql out NOCOPY VARCHAR2
1479 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1480 is
1481 l_sqlstmt VARCHAR2(15000);
1482 ---12.22.2005 npanandi: added l_act_sqlstmt below
1483 l_act_sqlstmt VARCHAR2(15000);
1484 l_from_clause varchar2(15000);
1485 l_getcnt varchar2(15000);
1486 l_grp_by varchar2(15000);
1487 l_where_clause varchar2(15000);
1488 l_group_by varchar2(15000);
1489
1490 l_total number;
1491 l_drill_url varchar2(15000);
1492
1493 v_period varchar2(100);
1494 l_bind_rec BIS_QUERY_ATTRIBUTES;
1495 BEGIN
1496
1497 l_sqlstmt := 'select distinct aauv.name RCI_ORG_CERT_MEASURE13
1498 , acv.certification_name RCI_ORG_CERT_MEASURE1
1499 ,al1.meaning RCI_ORG_CERT_MEASURE2
1500 ,aecv1.full_name RCI_ORG_CERT_MEASURE3
1501 ,''Q''||agpv.quarter_num RCI_ORG_CERT_MEASURE4
1502 ,agpv.period_year RCI_ORG_CERT_MEASURE5
1503 ,al2.meaning RCI_ORG_CERT_MEASURE6
1504 ,acv.certification_creation_date RCI_ORG_CERT_MEASURE7
1505 ,acv.target_completion_date RCI_ORG_CERT_MEASURE8
1506 ,sum(proc_not_certified) RCI_ORG_CERT_MEASURE9
1507 ,rbocrv.value RCI_ORG_CERT_MEASURE10
1508 ,papf.full_name RCI_ORG_CERT_MEASURE11
1509 ,rocsf.org_certified_on RCI_ORG_CERT_MEASURE12
1510 ,acv.certification_id RCI_ORG_CERT_URL1
1511 from rci_org_cert_summ_f rocsf
1512 ,amw_certification_vl acv
1513 ,amw_lookups al1
1514 ,amw_employees_current_v aecv1
1515 ,amw_gl_periods_v agpv
1516 ,amw_lookups al2
1517 ,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
1518 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
1519 and papf.employee_number is not null) papf
1520 ,RCI_BP_ORG_CERT_RESULT_V rbocrv
1521 ,hr_all_organization_units_tl aauv
1522 /** 10.20.2005 npanandi begin ***/
1523 ,fii_time_day ftd
1524 /** 10.20.2005 npanandi end ***/
1525 where rocsf.fin_certification_id = acv.certification_id
1526 and acv.object_type = ''FIN_STMT''
1527 and rocsf.certification_type = al1.lookup_code
1528 and al1.lookup_type = ''AMW_FINSTMT_CERTIFICATION_TYPE''
1529 and rocsf.certification_owner_id = aecv1.party_id
1530 and rocsf.certification_period_name = agpv.period_name
1531 and rocsf.certification_period_set_name = agpv.period_set_name
1532 and rocsf.certification_status = al2.lookup_code
1533 and al2.lookup_type = ''AMW_PROC_CERTIFICATION_STATUS''
1534 and rocsf.org_certified_by = papf.user_id(+)
1535 and nvl(rocsf.org_certification_status,''NOT_CERTIFIED'') = rbocrv.id(+)
1536 and rocsf.organization_id = aauv.organization_id
1537 and aauv.language = userenv(''LANG'')
1538 and rocsf.report_date_julian = ftd.report_date_julian
1539 /**** group by acv.certification_name,al1.meaning,aecv1.full_name,agpv.quarter_num,agpv.period_year,al2.meaning
1540 ,acv.certification_creation_date,acv.target_completion_date,aauv.name,rbocrv.value,papf.full_name
1541 ,rocsf.org_certified_on
1542 order by acv.certification_name,aauv.name ****/ ';
1543
1544
1545 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
1546
1547 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_QTR' AND
1548 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1549 l_from_clause := l_from_clause || ' and r1.certification_period_name = '||p_page_parameter_tbl(i).parameter_id;
1550
1551 ---l_url := l_url||'
1552 END IF;
1553
1554 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
1555 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1556 l_from_clause := l_from_clause || ' and rocsf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
1557 END IF;
1558
1559 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
1560 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1561 l_from_clause := l_from_clause || ' and rocsf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
1562 END IF;
1563
1564 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
1565 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1566 l_from_clause := l_from_clause || ' and rocsf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
1567 END IF;
1568
1569 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
1570 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1571 l_from_clause := l_from_clause || ' and rocsf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
1572 END IF;
1573
1574 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
1575 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1576 l_from_clause := l_from_clause || ' and rocsf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
1577 END IF;
1578
1579 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
1580 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1581 l_from_clause := l_from_clause || ' and rocsf.process_id = '||p_page_parameter_tbl(i).parameter_id;
1582 END IF;
1583
1584 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_ORG_CERT_RESULT' AND
1585 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1586 l_from_clause := l_from_clause || ' and rbocrv.id = '||p_page_parameter_tbl(i).parameter_id;
1587 END IF;
1588
1589 /** 10.20.2005 npanandi begin ***/
1590 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
1591 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1592 /*04.28.2006 npanandi: use dynamic binding for below time dimensions
1593 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
1594 */
1595 v_period := p_page_parameter_tbl(i).parameter_id;
1596 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
1597 END IF;
1598
1599 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
1600 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1601 /*04.28.2006 npanandi: use dynamic binding for below time dimensions
1602 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1603 */
1604 v_period := p_page_parameter_tbl(i).parameter_id;
1605 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
1606 END IF;
1607
1608 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
1609 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1610 /*04.28.2006 npanandi: use dynamic binding for below time dimensions
1611 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1612 */
1613 v_period := p_page_parameter_tbl(i).parameter_id;
1614 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
1615 END IF;
1616 /** 10.20.2005 npanandi end ***/
1617
1618 end loop;
1619
1620 l_group_by := ' group by acv.certification_name,al1.meaning,aecv1.full_name,agpv.quarter_num,agpv.period_year,al2.meaning
1621 ,acv.certification_creation_date,acv.target_completion_date,aauv.name,rbocrv.value,papf.full_name
1622 ,rocsf.org_certified_on,acv.certification_id
1623 /**order by acv.certification_name,aauv.name**/ ';
1624
1625
1626 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
1627 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE13, RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
1628 ,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
1629 ,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
1630 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
1631 ,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
1632 ,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12,RCI_ORG_CERT_URL1
1633 from (select t.*
1634 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1635 from ( '||l_sqlstmt || l_from_clause|| l_group_by || '
1636 ) t ) a
1637 order by a.col_rank ';
1638
1639
1640 p_exp_source_sql := l_act_sqlstmt;
1641 ----p_exp_source_sql := l_sqlstmt || l_from_clause || l_group_by;
1642
1643 /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1644 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1645 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1646
1647 p_exp_source_output.EXTEND;
1648 l_bind_rec.attribute_name := ':TIME1';
1649 l_bind_rec.attribute_value := v_period;
1650 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1651 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1652 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
1653 /**04.28.2006 npanandi: finished code for dynamic binding of time period dimensions**/
1654
1655 END get_org_certification_detail;
1656
1657 -- the get_process_detail procedure is called by
1661 ,p_exp_source_sql out NOCOPY VARCHAR2
1658 -- Organization Certification Detail + Significant Account Evaluation Summary report.
1659 PROCEDURE get_process_detail(
1660 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1662 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1663 is
1664 l_sqlstmt VARCHAR2(15000);
1665 ---12.22.2005 npanandi: added l_act_sqlstmt below
1666 l_act_sqlstmt VARCHAR2(15000);
1667 l_from_clause varchar2(15000);
1668 l_getcnt varchar2(15000);
1669 l_grp_by varchar2(15000);
1670 l_where_clause varchar2(15000);
1671
1672 l_total number;
1673 l_drill_url varchar2(15000);
1674
1675 v_period varchar2(100);
1676 l_bind_rec BIS_QUERY_ATTRIBUTES;
1677 BEGIN
1678
1679 /** 12.02.2005 npanandi: added distinct clause below ***/
1680 l_sqlstmt := 'select distinct apov.display_name RCI_ORG_CERT_MEASURE1
1681 ,aauv.name RCI_ORG_CERT_MEASURE2
1682 ,al1.meaning RCI_ORG_CERT_MEASURE3
1683 ,al2.meaning RCI_ORG_CERT_MEASURE4
1684 ,al3.meaning RCI_ORG_CERT_MEASURE5
1685 ,acv.certification_name RCI_ORG_CERT_MEASURE6
1686 ,rbcrv.value RCI_ORG_CERT_MEASURE7
1687 ,rpdf.certified_on RCI_ORG_CERT_MEASURE8
1688 ,aapv.project_name RCI_ORG_CERT_MEASURE9
1689 ,rfaev.value RCI_ORG_CERT_MEASURE10
1690 ,papf.full_name RCI_ORG_CERT_MEASURE11
1691 ,rpdf.last_evaluated_on RCI_ORG_CERT_MEASURE12
1692 ,apov.process_id RCI_ORG_CERT_URL1
1693 ,apov.organization_id RCI_ORG_CERT_URL2
1694 ,apov.revision_number RCI_ORG_CERT_URL3
1695 from rci_process_detail_f rpdf
1696 ,amw_process_organization_vl apov
1697 ,amw_audit_units_v aauv
1698 ,amw_lookups al1
1699 ,amw_lookups al2
1700 ,amw_lookups al3
1701 ,amw_certification_vl acv
1702 ,RCI_BP_CERT_RESULT_V rbcrv
1703 ,amw_audit_projects_v aapv
1704 /*** ,RCI_FS_ACCT_EVAL_V rfaev ***/
1705 ,(select id,value from RCI_FS_ACCT_EVAL_V where obj_name=''AMW_ORG_PROCESS'') rfaev
1706 ,(select papf.full_name,fu.user_id from PER_ALL_PEOPLE_F papf,fnd_user fu where fu.employee_id = papf.person_id
1707 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
1708 and papf.employee_number is not null) papf
1709 /** 10.19.2005 npanandi begin ***/
1710 ,fii_time_day ftd
1711 /** 10.19.2005 npanandi end ***/
1712 where rpdf.process_org_rev_id = apov.process_org_rev_id
1713 and rpdf.process_id = apov.process_id
1714 and rpdf.organization_id = apov.organization_id
1715 and rpdf.organization_id = aauv.organization_id
1716 and rpdf.significant_process_flag = al1.lookup_code
1717 and al1.lookup_type = ''AMW_SIGNIFICANT_PROCESS''
1718 and rpdf.standard_process_flag = al2.lookup_code
1719 and al2.lookup_type = ''AMW_STANDARD_PROCESS''
1720 and rpdf.process_category = al3.lookup_code(+)
1721 and al3.lookup_type(+) = ''AMW_PROCESS_CATEGORY''
1722 and rpdf.fin_certification_id = acv.certification_id
1723 /**01.25.2006 npanandi: bug 5000369 fix**/
1724 /**and rpdf.certification_result_code = rbcrv.id(+)**/
1725 and nvl(rpdf.certification_result_code,''NOT_CERTIFIED'') = rbcrv.id(+)and rpdf.project_id = aapv.audit_project_id(+)
1726 and rpdf.evaluation_result_code = rfaev.id(+)
1727 and rpdf.evaluated_by_id = papf.user_id(+)
1728 and rpdf.report_date_julian = ftd.report_date_julian';
1729
1730 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
1731
1732 ---l_from_clause := l_from_clause || ' p_page_parameter_tbl(i).parameter_name = '||p_page_parameter_tbl(i).parameter_id;
1733
1734 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
1735 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1736 l_from_clause := l_from_clause || ' and rpdf.certification_status = '||p_page_parameter_tbl(i).parameter_id;
1737 END IF;
1738
1739 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
1740 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1741 l_from_clause := l_from_clause || ' and rpdf.certification_type = '||p_page_parameter_tbl(i).parameter_id;
1742 END IF;
1743
1744 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
1745 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1746 l_from_clause := l_from_clause || ' and rpdf.fin_certification_id = '||p_page_parameter_tbl(i).parameter_id;
1747 END IF;
1748
1749 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_FINANCIAL_STATEMENT+RCI_FINANCIAL_ACCT' AND
1750 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1751 l_from_clause := l_from_clause || ' and rpdf.natural_account_id = '||p_page_parameter_tbl(i).parameter_id;
1752 END IF;
1753
1754 IF(p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
1755 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1756 l_from_clause := l_from_clause || ' and rpdf.organization_id = '||p_page_parameter_tbl(i).parameter_id;
1757 END IF;
1758
1759 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
1760 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1761 l_from_clause := l_from_clause || ' and rpdf.process_id = '||p_page_parameter_tbl(i).parameter_id;
1762 END IF;
1763
1764 IF(p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_ORG_CERT_RESULT' AND
1765 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1766 if(p_page_parameter_tbl(i).parameter_id = '''NOT_CERTIFIED''')then
1767 l_from_clause := l_from_clause || ' and rpdf.certification_result_code is null ';
1768 else
1769 l_from_clause := l_from_clause || ' and rpdf.certification_result_code = '||p_page_parameter_tbl(i).parameter_id;
1770 end if;
1771 END IF;
1772
1773 /** 10.19.2005 npanandi begin ***/
1774 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
1775 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1776 /**04.28.2006 npanandi: use dynamic binding for below time dimensions
1777 l_from_clause := l_from_clause || ' and ftd.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
1778 **/
1779 v_period := p_page_parameter_tbl(i).parameter_id;
1783 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
1780 l_from_clause := l_from_clause || ' and ftd.ent_period_id = :TIME1 ';
1781 END IF;
1782
1784 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1785 /**04.28.2006 npanandi: use dynamic binding for below time dimensions
1786 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
1787 **/
1788 v_period := p_page_parameter_tbl(i).parameter_id;
1789 l_from_clause := l_from_clause || ' and ftd.ent_qtr_id = :TIME1 ';
1790 END IF;
1791
1792 IF(p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
1793 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1794 /**04.28.2006 npanandi: use dynamic binding for below time dimensions
1795 l_from_clause := l_from_clause || ' and ftd.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
1796 **/
1797 v_period := p_page_parameter_tbl(i).parameter_id;
1798 l_from_clause := l_from_clause || ' and ftd.ent_year_id = :TIME1 ';
1799 END IF;
1800 /** 10.19.2005 npanandi end ***/
1801
1802 /*** 01.01.2006 npanandi: added if clauses for handling Significant Process and Key Control parameters ***/
1803 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY+DUMMY_LEVEL' AND
1804 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1805 if((p_page_parameter_tbl(i).parameter_id <> 'ALL') AND (p_page_parameter_tbl(i).parameter_id <> 'All')) then
1806 l_from_clause := l_from_clause || ' and nvl(rpdf.significant_process_flag,''N'') = '''||p_page_parameter_tbl(i).parameter_id||''' ';
1807 end if;
1808 END IF;
1809
1810 IF(p_page_parameter_tbl(i).parameter_name = 'DUMMY_DIMENSION+DUMMY_DIMENSION_LEVEL' AND
1811 p_page_parameter_tbl(i).parameter_id is NOT null) THEN
1812 if(p_page_parameter_tbl(i).parameter_id <> 'ALL') then
1813 ---l_from_clause := l_from_clause || ' and nvl(rpdf.key_control,''N'') = '''||p_page_parameter_tbl(i).parameter_id||''' ';
1814 l_from_clause := l_from_clause || ' and 1=1 ';
1815 end if;
1816 END IF;
1817 /*** 01.01.2006 npanandi: ends ***/
1818 end loop;
1819
1820
1821
1822 /** 12.22.2005 npanandi: added SQL below to handle order_by_clause -- bug 4758762 **/
1823 l_act_sqlstmt := 'select RCI_ORG_CERT_MEASURE1,RCI_ORG_CERT_MEASURE2
1824 ,RCI_ORG_CERT_MEASURE3,RCI_ORG_CERT_MEASURE4
1825 ,RCI_ORG_CERT_MEASURE5,RCI_ORG_CERT_MEASURE6
1826 ,RCI_ORG_CERT_MEASURE7,RCI_ORG_CERT_MEASURE8
1827 ,RCI_ORG_CERT_MEASURE9,RCI_ORG_CERT_MEASURE10
1828 ,RCI_ORG_CERT_MEASURE11,RCI_ORG_CERT_MEASURE12
1829 ,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
1830 from (select t.*
1831 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
1832 from ( '||l_sqlstmt || l_from_clause||'
1833 ) t ) a
1834 order by a.col_rank ';
1835
1836
1837 p_exp_source_sql := l_act_sqlstmt;
1838
1839 /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1840 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1841 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1842
1843 p_exp_source_output.EXTEND;
1844 l_bind_rec.attribute_name := ':TIME1';
1845 l_bind_rec.attribute_value := v_period;
1846 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1847 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1848 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
1849 /**04.28.2006 npanandi: finished code for dynamic binding of time period dimensions**/
1850 END get_process_detail;
1851
1852 ---12.08.2005 npanandi: added below function
1853 function get_default_year return varchar2
1854 is
1855 l_current_year varchar2(5);
1856 begin
1857 ---return '2004';
1858 select to_char(sysdate,'YYYY') into l_current_year from dual;
1859
1860 return l_current_year;
1861 end;
1862
1863 FUNCTION get_last_day(date_id NUMBER, type VARCHAR2) return varchar2
1864 IS
1868 BEGIN
1865 v_year varchar2(4);
1866 v_month varchar2(2);
1867 v_qtr number(1);
1869 IF type='M' THEN
1870 v_year := SUBSTR(date_id,1,4);
1871 v_month := SUBSTR(date_id,6);
1872 ELSIF type='Q' THEN
1873 v_year := SUBSTR(date_id,1,4);
1874 v_qtr := SUBSTR(date_id,5,1);
1875 CASE v_qtr
1876 WHEN 1 THEN v_month := '03';
1877 WHEN 2 THEN v_month := '06';
1878 WHEN 3 THEN v_month := '09';
1879 WHEN 4 THEN v_month := '12';
1880 END CASE;
1881 ELSIF type='Y' THEN
1882 v_year := date_id;
1883 v_month := '12';
1884 END IF;
1885 return v_year||v_month;
1886 END;
1887
1888 -- Common Procedures
1889
1890 END RCI_ORG_CERT_SUMM_PKG;