[Home] [Help]
PACKAGE BODY: APPS.RCI_OPEN_REMED_SUMM_PKG
Source
1 PACKAGE BODY RCI_OPEN_REMED_SUMM_PKG as
2 /*$Header: rciopnrb.pls 120.24.12000000.1 2007/01/16 20:46:31 appldev ship $*/
3
4 -- Purpose: Briefly explain the functionality of the package body
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Enter procedure, function bodies as shown below
10 -- Global Varaiables
11 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
12 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
13 C_OK CONSTANT NUMBER := 0; -- concurrent manager success code
14 C_ERRBUF_SIZE CONSTANT NUMBER := 300; -- length of formatted error message
15
16 -- User Defined Exceptions
17
18 INITIALIZATION_ERROR EXCEPTION;
19 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20900);
20 INITIALIZATION_ERROR_MESG CONSTANT VARCHAR2(200) := 'Error in Global setup';
21
22 -- File scope variables
23
24 g_global_start_date DATE;
25 g_rci_schema VARCHAR2(30);
26 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
27 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
28
29 PROCEDURE get_kpi(
30 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
31 ,p_exp_source_sql out NOCOPY VARCHAR2
32 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
33 is
34 l_sqlstmt VARCHAR2(15000);
35 counter NUMBER := 0;
36 where_clause VARCHAR2(4000) := ' ';
37 f_where_clause VARCHAR2(4000) := ' ';
38 r_where_clause VARCHAR2(4000) := ' ';
39
40 v_period varchar2(100);
41 v_qtr varchar2(100);
42 v_year varchar2(100);
43 l_bind_rec BIS_QUERY_ATTRIBUTES;
44 BEGIN
45 counter := p_page_parameter_tbl.COUNT;
46 FOR i IN 1..counter LOOP
47 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
48 /*IF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' THEN
49 where_clause := where_clause || ' AND f.certification_id='|| p_page_parameter_tbl(i).parameter_id ;
50 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' THEN
51 where_clause := where_clause || ' AND f.cert_type='|| p_page_parameter_tbl(i).parameter_id ;
52 ELS*/IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
53 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
54 /*04.21.2006 npanandi: implementation of passing time parameters
55 has been changed to account for dynamic binding -- see below
56 where_clause := where_clause || ' AND f.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
57 f_where_clause := f_where_clause || ' AND f.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
58 r_where_clause := r_where_clause || ' AND r.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
59 */
60 /** 12.16.2005 npanandi: added eec.initiation_date below **/
61 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_period_end_date from fii_time_day where ent_period_id = f.ent_period_id) ';
62
63 /*04.21.2006 npanandi: adding below for SQL repository fix**/
64 v_period := p_page_parameter_tbl(i).parameter_id;
65 where_clause := where_clause || ' AND f.ent_period_id = :WFTIME AND r.ent_period_id = :WRTIME';
66 f_where_clause := f_where_clause || ' AND f.ent_period_id = :FWTIME ';
67 r_where_clause := r_where_clause || ' AND r.ent_period_id = :RWTIME ';
68 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
69 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
70 /*04.21.2006 npanandi: implementation of passing time parameters
71 has been changed to account for dynamic binding -- see below
72 where_clause := where_clause || ' AND f.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
73 f_where_clause := f_where_clause || ' AND f.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
74 r_where_clause := r_where_clause || ' AND r.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
75 */
76 /** 12.16.2005 npanandi: added eec.initiation_date below **/
77 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_qtr_end_date from fii_time_day where ent_qtr_id = f.ent_qtr_id) ';
78
79 /*04.21.2006 npanandi: adding below for SQL repository fix**/
80 v_period := p_page_parameter_tbl(i).parameter_id;
81 where_clause := where_clause || ' AND f.ent_qtr_id = :WFTIME AND r.ent_qtr_id = :WRTIME';
82 f_where_clause := f_where_clause || ' AND f.ent_qtr_id = :FWTIME ';
83 r_where_clause := r_where_clause || ' AND r.ent_qtr_id = :RWTIME ';
84 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
85 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
86 /*04.21.2006 npanandi: implementation of passing time parameters
87 has been changed to account for dynamic binding -- see below
88 where_clause := where_clause || ' AND f.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
89 f_where_clause := f_where_clause || ' AND f.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
90 r_where_clause := r_where_clause || ' AND r.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
91 */
92 /** 12.16.2005 npanandi: added eec.initiation_date below **/
93 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_year_end_date from fii_time_day where ent_year_id = f.ent_year_id) ';
94
95 /*04.21.2006 npanandi: adding below for SQL repository fix**/
96 v_period := p_page_parameter_tbl(i).parameter_id;
97 where_clause := where_clause || ' AND f.ent_year_id = :WFTIME AND r.ent_year_id = :WRTIME ';
98 f_where_clause := f_where_clause || ' AND f.ent_year_id = :FWTIME ';
99 r_where_clause := r_where_clause || ' AND r.ent_year_id = :RWTIME ';
100 END IF;
101 END IF; --parameter_id not null
102 END LOOP;
103
104 l_sqlstmt :='
105 SELECT
106 null VIEWBY
107 ,nvl(sum(r_open),0) RCI_PROC_CERT_MEASURE1
108 ,nvl(sum(f_open),0) RCI_PROC_CERT_MEASURE2
109 FROM
110 (
111 SELECT
112 sum(f.open) f_open
113 ,0 r_open
114 FROM
115 RCI_OPEN_FINDINGS_F f
116 WHERE f.open=1 AND f.age_in_days >= 0
117 and f.organization_id is not null
118 '|| f_where_clause || '
119 UNION
120 SELECT
121 0 f_open
122 ,sum(r.open) r_open
123 FROM
124 RCI_OPEN_REMEDIATIONS_F r
125 WHERE r.open=1 AND r.age_in_days >= 0
126 and r.organization_id is not null
127 '|| r_where_clause || '
128 ) ';
129
130 p_exp_source_sql := l_sqlstmt;
131
132 /**04.21.2006 npanandi: adding code for dynamic binding of time period dimensions**/
133 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
134 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
135
136 p_exp_source_output.EXTEND;
137 l_bind_rec.attribute_name := ':WFTIME';
138 l_bind_rec.attribute_value := v_period;
139 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
140 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
141 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
142
143 p_exp_source_output.EXTEND;
144 l_bind_rec.attribute_name := ':WRTIME';
145 l_bind_rec.attribute_value := v_period;
146 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
147 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
148 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
149
150 p_exp_source_output.EXTEND;
151 l_bind_rec.attribute_name := ':FWTIME';
152 l_bind_rec.attribute_value := v_period;
153 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
154 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
155 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
156
157 p_exp_source_output.EXTEND;
158 l_bind_rec.attribute_name := ':RWTIME';
159 l_bind_rec.attribute_value := v_period;
160 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
161 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
162 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
163 /**04.21.2006 npanandi: finished code for dynamic binding of time period dimensions**/
164 END get_kpi;
165
166 /*
167 Procedure to be called by concurrent program
168 */
169 PROCEDURE get_open_remediation_result(
170 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
171 ,p_exp_source_sql out NOCOPY VARCHAR2
172 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
173 is
174 counter NUMBER := 0;
175 l_sqlstmt VARCHAR2(15000);
176 l_act_sqlstmt varchar2(15000);
177 join_table VARCHAR2(99) := '';
178 view_by_col VARCHAR2(99) := '';
179 where_clause VARCHAR2(4000) := ' ';
180 f_where_clause VARCHAR2(4000) := ' ';
181 r_where_clause VARCHAR2(4000) := ' ';
182 outer_where_clause VARCHAR2(2000) := ' ';
183 group_by_col VARCHAR2(99) := '';
184 group_by_col_name VARCHAR2(99) := '';
185 view_by_name varchar2(99) := '';
186 v_yyyymm varchar2(6);
187
188 v_period varchar2(100);
189 v_qtr varchar2(100);
190 v_year varchar2(100);
191 l_bind_rec BIS_QUERY_ATTRIBUTES;
192 BEGIN
193
194 counter := p_page_parameter_tbl.COUNT;
195 FOR i IN 1..counter LOOP
196 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
197 IF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
198 --view by parameters
199 view_by_name := p_page_parameter_tbl(i).parameter_id;
200 IF p_page_parameter_tbl(i).parameter_id = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
201 view_by_col := ' org.name ';
202 group_by_col := 'organization_id ';
203 group_by_col_name := ' v_org_id ';
204 join_table := ' ,amw_audit_units_v org ';
205 outer_where_clause := ' opn.v_org_id = org.organization_id ';
206 ELSIF p_page_parameter_tbl(i).parameter_id = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
207 view_by_col := ' proc.display_name ';
208 group_by_col := 'process_id ';
209 group_by_col_name := ' v_process_id ';
210 join_table := ' ,amw_process_vl proc ';
211 outer_where_clause := ' opn.v_process_id = proc.process_id
212 AND (proc.REVISION_NUMBER = (select max(REVISION_NUMBER) from amw_process_vl where proc.process_id=process_id)
213 OR proc.process_id is null ) ';
214 ELSIF p_page_parameter_tbl(i).parameter_id = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' THEN
215 view_by_col := ' ecp.description ';
216 group_by_col := 'priority_code ';
217 group_by_col_name := ' v_priority_id ';
218 join_table := ' ,eng_change_priorities ecp ';
219 outer_where_clause := ' opn.v_priority_id = ecp.eng_change_priority_code ';
220 ELSIF p_page_parameter_tbl(i).parameter_id = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' THEN
221 view_by_col := ' ecr.description ';
222 group_by_col := 'reason_code ';
223 group_by_col_name := ' v_reason_id ';
224 join_table := ' ,eng_change_reasons ecr ';
225 outer_where_clause := ' opn.v_reason_id = ecr.eng_change_reason_code ';
226 ELSIF p_page_parameter_tbl(i).parameter_id = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' THEN
227 view_by_col := ' ecs.status_name ';
228 group_by_col := 'phase_code ';
229 group_by_col_name := ' v_phase_id ';
230 join_table := ' ,eng_change_statuses_vl ecs ';
231 outer_where_clause := ' opn.v_phase_id = ecs.status_code ';
232 END IF;
233 --end view by parameters
234 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
235 where_clause := where_clause || ' AND f.organization_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.organization_id ='|| p_page_parameter_tbl(i).parameter_id;
236 f_where_clause := f_where_clause || ' AND f.organization_id ='|| p_page_parameter_tbl(i).parameter_id;
237 r_where_clause := r_where_clause || ' AND r.organization_id ='|| p_page_parameter_tbl(i).parameter_id;
238 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
239 where_clause := where_clause || ' AND f.process_id ='|| p_page_parameter_tbl(i).parameter_id || ' AND r.process_id ='|| p_page_parameter_tbl(i).parameter_id;
240 f_where_clause := f_where_clause || ' AND f.process_id ='|| p_page_parameter_tbl(i).parameter_id;
241 r_where_clause := r_where_clause || ' AND r.process_id ='|| p_page_parameter_tbl(i).parameter_id;
242 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' THEN
243 where_clause := where_clause || ' AND f.priority_code ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.priority_code ='|| p_page_parameter_tbl(i).parameter_id;
244 f_where_clause := f_where_clause || ' AND f.priority_code ='|| p_page_parameter_tbl(i).parameter_id;
245 r_where_clause := r_where_clause || ' AND r.priority_code ='|| p_page_parameter_tbl(i).parameter_id;
246 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' THEN
247 where_clause := where_clause || ' AND f.reason_code ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.reason_code ='|| p_page_parameter_tbl(i).parameter_id;
248 f_where_clause := f_where_clause || ' AND f.reason_code ='|| p_page_parameter_tbl(i).parameter_id;
249 r_where_clause := r_where_clause || ' AND r.reason_code ='|| p_page_parameter_tbl(i).parameter_id;
250 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' THEN
251 where_clause := where_clause || ' AND f.phase_code ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.phase_code ='|| p_page_parameter_tbl(i).parameter_id;
252 f_where_clause := f_where_clause || ' AND f.phase_code ='|| p_page_parameter_tbl(i).parameter_id;
253 r_where_clause := r_where_clause || ' AND r.phase_code ='|| p_page_parameter_tbl(i).parameter_id;
254 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
255 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
256 /** 12.16.2005 npanandi: joining with fii_time_day **/
257 /*04.25.2006 npanandi: implementation of passing time parameters
258 has been changed to account for dynamic binding -- see below
259 where_clause := where_clause || ' AND f.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
260 f_where_clause := f_where_clause || ' AND f.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
261 r_where_clause := r_where_clause || ' AND r.ent_period_id ='|| p_page_parameter_tbl(i).parameter_id;
262 */
266 v_period := p_page_parameter_tbl(i).parameter_id;
263 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_period_end_date from fii_time_day where ent_period_id = f.ent_period_id) ';
264
265 /*04.25.2006 npanandi: adding below for SQL repository fix**/
267 where_clause := where_clause || ' AND f.ent_period_id = :WFTIME AND r.ent_period_id = :WRTIME';
268 f_where_clause := f_where_clause || ' AND f.ent_period_id = :FWTIME ';
269 r_where_clause := r_where_clause || ' AND r.ent_period_id = :RWTIME ';
270 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
271 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
272 /** 12.16.2005 npanandi: joining with fii_time_day **/
273 /*04.25.2006 npanandi: implementation of passing time parameters
274 has been changed to account for dynamic binding -- see below
275 where_clause := where_clause || ' AND f.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
276 f_where_clause := f_where_clause || ' AND f.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
277 r_where_clause := r_where_clause || ' AND r.ent_qtr_id ='|| p_page_parameter_tbl(i).parameter_id;
278 */
279 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_qtr_end_date from fii_time_day where ent_qtr_id = f.ent_qtr_id) ';
280
281 /*04.25.2006 npanandi: adding below for SQL repository fix**/
282 v_period := p_page_parameter_tbl(i).parameter_id;
283 where_clause := where_clause || ' AND f.ent_qtr_id = :WFTIME AND r.ent_qtr_id = :WRTIME';
284 f_where_clause := f_where_clause || ' AND f.ent_qtr_id = :FWTIME ';
285 r_where_clause := r_where_clause || ' AND r.ent_qtr_id = :RWTIME ';
286 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
287 -- v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
288 /** 12.16.2005 npanandi: joining with fii_time_day **/
289 /*04.25.2006 npanandi: implementation of passing time parameters
290 has been changed to account for dynamic binding -- see below
291 where_clause := where_clause || ' AND f.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id|| ' AND r.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
292 f_where_clause := f_where_clause || ' AND f.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
293 r_where_clause := r_where_clause || ' AND r.ent_year_id ='|| p_page_parameter_tbl(i).parameter_id;
294 */
295 -- where_clause := where_clause || ' AND eec.initiation_date < (select distinct ent_year_end_date from fii_time_day where ent_year_id = f.ent_year_id) ';
296
297 /*04.25.2006 npanandi: adding below for SQL repository fix**/
298 v_period := p_page_parameter_tbl(i).parameter_id;
299 where_clause := where_clause || ' AND f.ent_year_id = :WFTIME AND r.ent_year_id = :WRTIME';
300 f_where_clause := f_where_clause || ' AND f.ent_year_id = :FWTIME ';
301 r_where_clause := r_where_clause || ' AND r.ent_year_id = :RWTIME ';
302 END IF;
303 END IF; -- parameter_id IS NOT NULL
304 END LOOP;
305 -- when no view_parameter is passed in the link e.g. from kpi portlet
306 IF view_by_col IS NULL THEN
307 view_by_col := ' org.name ';
308 group_by_col := 'organization_id ';
309 group_by_col_name := ' v_org_id ';
310 join_table := ' ,amw_audit_units_v org ';
311 outer_where_clause := ' opn.v_org_id = org.organization_id ';
312 END IF;
313
314 l_sqlstmt :='SELECT DISTINCT
315 '|| view_by_col ||' VIEWBY
316 ,'|| group_by_col_name ||' VIEWBYID
317 ,'''||view_by_name || ''' RCI_DRILLDOWN_PARAM1
318 ,'|| group_by_col_name ||' RCI_DRILLDOWN_PARAM2
319 ,RCI_PROC_CERT_MEASURE1, RCI_PROC_CERT_MEASURE2
320 ,decode(RCI_PROC_CERT_MEASURE1,0,0,round(RCI_PROC_CERT_MEASURE2/RCI_PROC_CERT_MEASURE1*100,2)) RCI_PROC_CERT_MEASURE3
321 ,RCI_PROC_CERT_MEASURE4, RCI_PROC_CERT_MEASURE5, RCI_PROC_CERT_MEASURE6
322 ,RCI_PROC_CERT_MEASURE7, RCI_PROC_CERT_MEASURE8, RCI_PROC_CERT_MEASURE9
323 ,RCI_PROC_CERT_MEASURE10
324 ,decode(RCI_PROC_CERT_MEASURE10,0,0,round(RCI_PROC_CERT_MEASURE10/RCI_PROC_CERT_MEASURE9*100,2)) RCI_PROC_CERT_MEASURE11
325 ,RCI_PROC_CERT_MEASURE12
326 ,RCI_PROC_CERT_MEASURE13, RCI_PROC_CERT_MEASURE14, RCI_PROC_CERT_MEASURE15
327 ,RCI_PROC_CERT_MEASURE16
328 FROM (
329 select
330 '||group_by_col_name||'
331 ,sum(r_open) RCI_PROC_CERT_MEASURE1
332 ,sum(r_past_due) RCI_PROC_CERT_MEASURE2
333 ,sum(r_AGE_IN_DAYS ) RCI_PROC_CERT_MEASURE4
334 ,sum(r_AGE_BUCKET_1) RCI_PROC_CERT_MEASURE5
338 ,sum(f_open) RCI_PROC_CERT_MEASURE9
335 ,sum(r_AGE_BUCKET_2) RCI_PROC_CERT_MEASURE6
336 ,sum(r_AGE_BUCKET_3) RCI_PROC_CERT_MEASURE7
337 ,sum(r_AGE_BUCKET_4) RCI_PROC_CERT_MEASURE8
339 ,sum(f_past_due) RCI_PROC_CERT_MEASURE10
340 ,sum(f_AGE_IN_DAYS ) RCI_PROC_CERT_MEASURE12
341 ,sum(f_AGE_BUCKET_1) RCI_PROC_CERT_MEASURE13
342 ,sum(f_AGE_BUCKET_2) RCI_PROC_CERT_MEASURE14
343 ,sum(f_AGE_BUCKET_3) RCI_PROC_CERT_MEASURE15
344 ,sum(f_AGE_BUCKET_4) RCI_PROC_CERT_MEASURE16
345 from(
346 SELECT f.'|| group_by_col ||group_by_col_name||'
347 ,sum(f.open) f_open
348 ,sum(f.past_due) f_past_due
349 ,round(avg(f.AGE_IN_DAYS )) f_AGE_IN_DAYS
350 ,sum(f.AGE_BUCKET_1) f_AGE_BUCKET_1
351 ,sum(f.AGE_BUCKET_2) f_AGE_BUCKET_2
352 ,sum(f.AGE_BUCKET_3) f_AGE_BUCKET_3
353 ,sum(f.AGE_BUCKET_4) f_AGE_BUCKET_4
354 ,0 r_open
355 ,0 r_past_due
356 ,0 r_AGE_IN_DAYS
357 ,0 r_AGE_BUCKET_1
358 ,0 r_AGE_BUCKET_2
359 ,0 r_AGE_BUCKET_3
360 ,0 r_AGE_BUCKET_4
361 FROM
362 RCI_OPEN_FINDINGS_F f
363 WHERE f.open=1 AND f.age_in_days >= 0
364 '|| f_where_clause || '
365 group by f.'|| group_by_col || '
366 UNION
367 SELECT r.'|| group_by_col ||group_by_col_name||'
368 ,0 f_open
369 ,0 f_past_due
370 ,0 f_AGE_IN_DAYS
371 ,0 f_AGE_BUCKET_1
372 ,0 f_AGE_BUCKET_2
373 ,0 f_AGE_BUCKET_3
374 ,0 f_AGE_BUCKET_4
375 ,sum(r.open) r_open
376 ,sum(r.past_due) r_past_due
377 ,round(avg(r.AGE_IN_DAYS )) r_AGE_IN_DAYS
378 ,sum(r.AGE_BUCKET_1) r_AGE_BUCKET_1
379 ,sum(r.AGE_BUCKET_2) r_AGE_BUCKET_2
380 ,sum(r.AGE_BUCKET_3) r_AGE_BUCKET_3
381 ,sum(r.AGE_BUCKET_4) r_AGE_BUCKET_4
382 FROM
383 RCI_OPEN_REMEDIATIONS_F r
384 WHERE r.open=1 AND r.age_in_days >= 0
385 '|| r_where_clause || '
386 group by r.'|| group_by_col || '
387 ) group by '||group_by_col_name||'
388 ) opn '
389 || join_table
390 || ' where
391 ' ||outer_where_clause
392 ;
393
394 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
395 l_act_sqlstmt := 'select VIEWBY,VIEWBYID,RCI_DRILLDOWN_PARAM1,RCI_DRILLDOWN_PARAM2
396 ,RCI_PROC_CERT_MEASURE1,RCI_PROC_CERT_MEASURE2,RCI_PROC_CERT_MEASURE3
397 ,RCI_PROC_CERT_MEASURE4,RCI_PROC_CERT_MEASURE5,RCI_PROC_CERT_MEASURE6
398 ,RCI_PROC_CERT_MEASURE7,RCI_PROC_CERT_MEASURE8,RCI_PROC_CERT_MEASURE9
399 ,RCI_PROC_CERT_MEASURE10,RCI_PROC_CERT_MEASURE11
400 ,RCI_PROC_CERT_MEASURE12,RCI_PROC_CERT_MEASURE13
401 ,RCI_PROC_CERT_MEASURE14,RCI_PROC_CERT_MEASURE15
402 ,RCI_PROC_CERT_MEASURE16
403 from (select t.*
404 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
405 from ( '||l_sqlstmt||'
406 ) t ) a
407 order by a.col_rank ';
408
409
410 --todo remove the status_code condition
411 p_exp_source_sql := l_act_sqlstmt;
412
413 /**04.25.2006 npanandi: adding code for dynamic binding of time period dimensions**/
414 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
415 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
416
417 p_exp_source_output.EXTEND;
418 l_bind_rec.attribute_name := ':WFTIME';
419 l_bind_rec.attribute_value := v_period;
420 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
421 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
422 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
423
424 p_exp_source_output.EXTEND;
425 l_bind_rec.attribute_name := ':WRTIME';
426 l_bind_rec.attribute_value := v_period;
427 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
428 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
429 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
430
431 p_exp_source_output.EXTEND;
432 l_bind_rec.attribute_name := ':FWTIME';
433 l_bind_rec.attribute_value := v_period;
434 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
435 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
436 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
437
438 p_exp_source_output.EXTEND;
439 l_bind_rec.attribute_name := ':RWTIME';
440 l_bind_rec.attribute_value := v_period;
441 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
442 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
443 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
444 /**04.25.2006 npanandi: finished code for dynamic binding of time period dimensions**/
445
446 END get_open_remediation_result;
447
448 PROCEDURE get_findings_details(
449 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
450 ,p_exp_source_sql out NOCOPY VARCHAR2
451 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
452 is
453 counter NUMBER := 0;
454 l_sqlstmt VARCHAR2(15000);
455 where_clause VARCHAR2(2000) := ' ';
456 param_name VARCHAR2(99) ;
457 param_value VARCHAR2(99);
458 v_yyyymm varchar2(6);
459
460 v_period varchar2(100);
461 v_qtr varchar2(100);
462 v_year varchar2(100);
463 l_bind_rec BIS_QUERY_ATTRIBUTES;
464 BEGIN
468 IF p_page_parameter_tbl(i).parameter_name = 'PARAM_VALUE' THEN
465 counter := p_page_parameter_tbl.COUNT;
466 FOR i IN 1..counter LOOP
467 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
469 param_value := p_page_parameter_tbl(i).parameter_id;
470 ELSIF p_page_parameter_tbl(i).parameter_name = 'PARAM_NAME' THEN
471 param_name := p_page_parameter_tbl(i).parameter_id;
472 ELSIF p_page_parameter_tbl(i).parameter_name = 'OPEN' THEN
473 where_clause := where_clause || ' AND f.open=1 ';
474 /*dpatel 21-dec-05*/
475 -- where_clause := where_clause || ' AND (f.completion_date is null OR round(f.completion_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) >0 ) ';
476 ELSIF p_page_parameter_tbl(i).parameter_name = 'PAST_DUE' THEN
477 where_clause := where_clause || ' AND f.past_due=1 ';
478 /*dpatel 21-dec-05*/
479 -- where_clause := where_clause || ' AND (round(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-f.need_by_date) >0 AND (f.completion_date is null OR round(f.completion_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) >0) ) ';
480 ELSIF p_page_parameter_tbl(i).parameter_name = 'BUCKET_ID' THEN
481 where_clause := where_clause || ' AND f.age_bucket_'||p_page_parameter_tbl(i).parameter_id ||'=1 ';
482 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
483 where_clause := where_clause || ' AND f.organization_id ='|| p_page_parameter_tbl(i).parameter_id ||' ';
484 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
485 where_clause := where_clause || ' AND f.process_id ='|| p_page_parameter_tbl(i).parameter_id ||' ';
486 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' THEN
487 where_clause := where_clause || ' AND f.priority_code ='|| p_page_parameter_tbl(i).parameter_id ||' ';
488 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' THEN
489 where_clause := where_clause || ' AND f.reason_code ='|| p_page_parameter_tbl(i).parameter_id ||' ';
490 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' THEN
491 where_clause := where_clause || ' AND f.phase_code='|| p_page_parameter_tbl(i).parameter_id ||' ';
492 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
493 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
494 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
495 where_clause := where_clause || ' AND f.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
496 */
497
498 /*04.25.2006 npanandi: adding below for SQL repository fix**/
499 v_period := p_page_parameter_tbl(i).parameter_id;
500 where_clause := where_clause || ' AND f.ent_period_id = :TIME ';
501 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
502 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
503 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
504 where_clause := where_clause || ' AND f.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
505 */
506
507 /*04.25.2006 npanandi: adding below for SQL repository fix**/
508 v_period := p_page_parameter_tbl(i).parameter_id;
509 where_clause := where_clause || ' AND f.ent_qtr_id = :TIME ';
510 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
511 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
512 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
513 where_clause := where_clause || ' AND f.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
514 **/
515
516 /*04.25.2006 npanandi: adding below for SQL repository fix**/
517 v_period := p_page_parameter_tbl(i).parameter_id;
518 where_clause := where_clause || ' AND f.ent_year_id = :TIME ';
519 /*dpatel 21-dec-05*/
520 /* ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
521 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
522 where_clause := where_clause || ' AND (f.completion_date is null or round(f.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
523 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
524 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
525 where_clause := where_clause || ' AND (f.completion_date is null or round(f.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
526 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
527 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
528 where_clause := where_clause || ' AND (f.completion_date is null or round(f.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
529 */ END IF;
530 END IF; -- parameter_id IS NOT NULL
531 END LOOP;
532
533 IF v_yyyymm IS NULL THEN
534 v_yyyymm := TO_CHAR(sysdate,'YYYYMM');
535 END IF;
536
537 IF param_name IS NOT NULL THEN
538 IF(Trim(param_value) IS NULL) THEN
539 IF param_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
540 where_clause := where_clause || ' AND f.organization_id is null ';
541 ELSIF param_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
542 where_clause := where_clause || ' AND f.process_id is null ';
543 ELSIF param_name = '+RCI_ISSUE_PRIORITY' THEN
544 where_clause := where_clause || ' AND f.priority_code is null ';
548 where_clause := where_clause || ' AND f.phase_code is null ';
545 ELSIF param_name = '+RCI_ISSUE_REASON' THEN
546 where_clause := where_clause || ' AND f.reason_code is null ';
547 ELSIF param_name = '+RCI_ISSUE_PHASE' THEN
549 END IF;
550 END IF;
551 END IF;
552
553 l_sqlstmt :='
554 SELECT
555 ecv.change_id RCI_DRILLDOWN_PARAM1
556 ,ecv.change_name RCI_PROC_CERT_MEASURE7
557 ,ecv.requestor RCI_PROC_CERT_MEASURE1
558 ,ecst.status_name RCI_PROC_CERT_MEASURE2
559 ,ecv.priority_code RCI_PROC_CERT_MEASURE3
560 ,ecv.reported_days_since RCI_PROC_CERT_MEASURE4
561 ,-ecv.days_until_due RCI_PROC_CERT_MEASURE5
562 ,trunc(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-ecv.days_until_due) RCI_PROC_CERT_MEASURE6
563 FROM
564 eng_changes_v ecv, eng_change_statuses_tl ecst
565 WHERE
566 change_mgmt_type_code = ''AMW_PROJ_FINDING''
567 and ecst.status_code = ecv.status_code
568 and ecst.language = userenv(''LANG'')
569 and change_id in ( SELECT
570 finding_id
571 FROM
572 rci_open_findings_f f
573 WHERE age_in_days >= 0
574 /** 12.26.2005 npanandi: added following clause to display
575 only OPEN findings from rci_open_findings_f
576 bug 4908320 fix ***/
577 and f.OPEN=1
578 ' || where_clause ||
579 ')'
580 ;
581 p_exp_source_sql := l_sqlstmt;
582
583 /**04.25.2006 npanandi: adding code for dynamic binding of time period dimensions**/
584 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
585 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
586
587 p_exp_source_output.EXTEND;
588 l_bind_rec.attribute_name := ':TIME';
589 l_bind_rec.attribute_value := v_period;
590 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
591 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
592 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
593 /**04.25.2006 npanandi: finished code for dynamic binding of time period dimensions**/
594
595 END get_findings_details;
596
597 PROCEDURE get_remediations_details(
598 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
599 ,p_exp_source_sql out NOCOPY VARCHAR2
600 ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
601 is
602 counter NUMBER := 0;
603 l_sqlstmt VARCHAR2(15000);
604 where_clause VARCHAR2(2000) := ' ';
605 param_name VARCHAR2(99) := ' ';
606 param_value VARCHAR2(99) := ' ';
607 v_yyyymm varchar2(6);
608
609 v_period varchar2(100);
610 v_qtr varchar2(100);
611 v_year varchar2(100);
612 l_bind_rec BIS_QUERY_ATTRIBUTES;
613 BEGIN
614
615 counter := p_page_parameter_tbl.COUNT;
616 FOR i IN 1..counter LOOP
617 IF p_page_parameter_tbl(i).parameter_id IS NOT NULL THEN
618 IF p_page_parameter_tbl(i).parameter_name = 'PARAM_VALUE' THEN
619 param_value := p_page_parameter_tbl(i).parameter_id;
620 ELSIF p_page_parameter_tbl(i).parameter_name = 'PARAM_NAME' THEN
621 param_name := p_page_parameter_tbl(i).parameter_id;
622 ELSIF p_page_parameter_tbl(i).parameter_name = 'OPEN' THEN
623 where_clause := where_clause || ' AND r.open=1 ';
624 /*dpatel 21-dec-05*/
625 -- where_clause := where_clause || ' AND (r.completion_date is null OR round(r.completion_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) >0 ) ';
626 ELSIF p_page_parameter_tbl(i).parameter_name = 'PAST_DUE' THEN
627 where_clause := where_clause || ' AND r.past_due=1 ';
628 /*dpatel 21-dec-05*/
629 -- where_clause := where_clause || ' AND (round(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-r.need_by_date) >0 AND(r.completion_date is null OR round(r.completion_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) >0) ) ';
630 ELSIF p_page_parameter_tbl(i).parameter_name = 'BUCKET_ID' THEN
631 where_clause := where_clause || ' AND r.age_bucket_'||p_page_parameter_tbl(i).parameter_id ||'=1 ';
632 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
633 if(p_page_parameter_tbl(i).parameter_id is not null and trim(p_page_parameter_tbl(i).parameter_id) is not null) then
634 where_clause := where_clause || ' AND r.organization_id ='|| p_page_parameter_tbl(i).parameter_id ||' ';
635 end if;
636 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
637 where_clause := where_clause || ' AND r.process_id ='|| p_page_parameter_tbl(i).parameter_id ||' ';
638 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' THEN
639 where_clause := where_clause || ' AND r.priority_code ='|| p_page_parameter_tbl(i).parameter_id ||' ';
640 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' THEN
641 where_clause := where_clause || ' AND r.reason_code ='|| p_page_parameter_tbl(i).parameter_id ||' ';
642 ELSIF p_page_parameter_tbl(i).parameter_name = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' THEN
643 where_clause := where_clause || ' AND r.phase_code='|| p_page_parameter_tbl(i).parameter_id ||' ';
647 where_clause := where_clause || ' AND r.ent_period_id = '||p_page_parameter_tbl(i).parameter_id;
644 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
645 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
646 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
648 */
649
650 /*04.25.2006 npanandi: adding below for SQL repository fix**/
651 v_period := p_page_parameter_tbl(i).parameter_id;
652 where_clause := where_clause || ' AND r.ent_period_id = :TIME ';
653 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
654 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
655 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
656 where_clause := where_clause || ' AND r.ent_qtr_id = '||p_page_parameter_tbl(i).parameter_id;
657 */
658
659 /*04.25.2006 npanandi: adding below for SQL repository fix**/
660 v_period := p_page_parameter_tbl(i).parameter_id;
661 where_clause := where_clause || ' AND r.ent_qtr_id = :TIME ';
662 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
663 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
664 /*04.25.2006 npanandi: commenting below, to enable dynamic binding, see below
665 where_clause := where_clause || ' AND r.ent_year_id = '||p_page_parameter_tbl(i).parameter_id;
666 */
667
668 /*04.25.2006 npanandi: adding below for SQL repository fix**/
669 v_period := p_page_parameter_tbl(i).parameter_id;
670 where_clause := where_clause || ' AND r.ent_year_id = :TIME ';
671 /*dpatel 21-dec-05*/
672 /* ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
673 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'M');
674 where_clause := where_clause || ' AND (r.completion_date is null or round(r.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
675 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' THEN
676 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Q');
677 where_clause := where_clause || ' AND (r.completion_date is null or round(r.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
678 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
679 v_yyyymm := get_last_day(p_page_parameter_tbl(i).parameter_id,'Y');
680 where_clause := where_clause || ' AND (r.completion_date is null or round(r.completion_date- last_day(to_date('''||v_yyyymm||''',''YYYYMM'')) )>0) ';
681 */ END IF;
682 END IF; -- parameter_id IS NOT NULL
683 END LOOP;
684
685 IF v_yyyymm IS NULL THEN
686 v_yyyymm := TO_CHAR(sysdate,'YYYYMM');
687 END IF;
688
689 IF param_name IS NOT NULL THEN
690 IF(Trim(param_value) IS NULL) THEN
691 IF param_name = 'ORGANIZATION+RCI_ORG_AUDIT' THEN
692 where_clause := where_clause || ' AND r.organization_id is null ';
693 ELSIF param_name = 'RCI_BP_CERT+RCI_BP_PROCESS' THEN
694 where_clause := where_clause || ' AND r.process_id is null ';
695 ELSIF param_name = '+RCI_ISSUE_PRIORITY' THEN
696 where_clause := where_clause || ' AND r.priority_code is null ';
697 ELSIF param_name = '+RCI_ISSUE_REASON' THEN
698 where_clause := where_clause || ' AND r.reason_code is null ';
699 ELSIF param_name = '+RCI_ISSUE_PHASE' THEN
700 where_clause := where_clause || ' AND r.phase_code is null ';
701 END IF;
702 END IF;
703 END IF;
704
705 l_sqlstmt :='
706 SELECT
707 ecv.change_id RCI_DRILLDOWN_PARAM1
708 ,ecv.change_name RCI_PROC_CERT_MEASURE7
709 ,ecv.requestor RCI_PROC_CERT_MEASURE1
710 ,ecst.status_name RCI_PROC_CERT_MEASURE2
711 ,ecv.priority_code RCI_PROC_CERT_MEASURE3
712 ,ecv.reported_days_since RCI_PROC_CERT_MEASURE4
713 ,-ecv.days_until_due RCI_PROC_CERT_MEASURE5
714 ,trunc(last_day(to_date('''||v_yyyymm||''',''YYYYMM''))-ecv.days_until_due) RCI_PROC_CERT_MEASURE6
715 FROM
716 eng_changes_v ecv, eng_change_statuses_tl ecst
717 WHERE
718 ecv.change_mgmt_type_code = ''AMW_REMEDIATION''
719 and ecst.status_code = ecv.status_code
720 and ecst.language = userenv(''LANG'')
721 /** 12.19.2005 npanandi: adding the below, because only those remediations
722 should be chosen which are initiated BEFORE the chosen periods last day
723 **/
724 and ecv.INITIATION_DATE < last_day(to_date('''||v_yyyymm||''',''YYYYMM''))
725 /** 12.19.2005 npanandi: need to see if this is open, and if not open,
726 whether it was set to completed AFTER the last day of the
727 chosen period ***/
728 and (ecv.STATUS_CODE not in (0,11) or (ecv.status_code=11 and round(ecv.last_update_date-last_day(to_date('''||v_yyyymm||''',''YYYYMM''))) > 0 ))
729 and ecv.change_id in ( SELECT
730 remediation_id
731 FROM
732 rci_open_remediations_f r
733 WHERE 1=1 ' || where_clause ||
734 ')'
735 ;
736
737 p_exp_source_sql := l_sqlstmt;
738
739 /**04.25.2006 npanandi: adding code for dynamic binding of time period dimensions**/
740 p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
741 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
742
743 p_exp_source_output.EXTEND;
744 l_bind_rec.attribute_name := ':TIME';
748 p_exp_source_output(p_exp_source_output.COUNT) := l_bind_rec;
745 l_bind_rec.attribute_value := v_period;
746 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
747 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
749 /**04.25.2006 npanandi: finished code for dynamic binding of time period dimensions**/
750
751
752 END get_remediations_details;
753
754 /*
755 --Procedure to be called from concurrent program--
756 */
757 PROCEDURE open_remed_act_increment_load
758 (
759 errbuf IN OUT NOCOPY VARCHAR2
760 ,retcode IN OUT NOCOPY NUMBER)
761 IS
762 BEGIN
763 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.RCI_OPEN_FINDINGS_F');
764 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.RCI_OPEN_REMEDIATIONS_F');
765 insert_findings_increment_load(
766 errbuf => errbuf
767 ,retcode => retcode);
768
769 insert_remeds_increment_load(
770 errbuf => errbuf
771 ,retcode => retcode);
772 EXECUTE IMMEDIATE ('COMMIT');
773 END open_remed_act_increment_load;
774
775 PROCEDURE open_remed_act_initial_load
776 (
777 errbuf IN OUT NOCOPY VARCHAR2
778 ,retcode IN OUT NOCOPY NUMBER)
779 IS
780 l_user_id NUMBER ;
781 l_login_id NUMBER ;
782 l_program_id NUMBER ;
783 l_program_login_id NUMBER ;
784 l_program_application_id NUMBER ;
785 l_request_id NUMBER ;
786 l_run_date DATE;
787
788 BEGIN
789
790 insert_findings_initial_load(
791 errbuf => errbuf
792 ,retcode => retcode);
793
794 insert_remeds_initial_load(
795 errbuf => errbuf
796 ,retcode => retcode);
797
798 /***
799 l_user_id := NVL(fnd_global.USER_ID, -1);
800 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
801 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
802 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
803 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
804 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
805 l_run_date := sysdate - 5/(24*60);
806
807 INSERT INTO rci_dr_inc( fact_name
808 ,last_run_date
809 ,created_by
810 ,creation_date
811 ,last_update_date
812 ,last_updated_by
813 ,last_update_login
814 ,program_id
815 ,program_login_id
816 ,program_application_id
817 ,request_id ) VALUES (
818 'RCI_OPEN_FINDINGS_F'
819 ,l_run_date
820 ,l_user_id
821 ,sysdate
822 ,sysdate
823 ,l_user_id
824 ,l_login_id
825 ,l_program_id
826 ,l_program_login_id
827 ,l_program_application_id
828 ,l_request_id );
829 INSERT INTO rci_dr_inc( fact_name
830 ,last_run_date
831 ,created_by
832 ,creation_date
833 ,last_update_date
834 ,last_updated_by
835 ,last_update_login
836 ,program_id
837 ,program_login_id
838 ,program_application_id
839 ,request_id ) VALUES (
840 'RCI_OPEN_REMEDIATIONS_F'
841 ,l_run_date
842 ,l_user_id
843 ,sysdate
844 ,sysdate
845 ,l_user_id
846 ,l_login_id
847 ,l_program_id
848 ,l_program_login_id
849 ,l_program_application_id
850 ,l_request_id );
851
852 EXECUTE IMMEDIATE ('COMMIT');
853 ***/
854
855 END open_remed_act_initial_load;
856
857 PROCEDURE insert_findings_increment_load(
858 errbuf IN OUT NOCOPY VARCHAR2
859 ,retcode IN OUT NOCOPY NUMBER) IS
860 BEGIN
861 insert_findings_initial_load(
862 errbuf => errbuf
863 ,retcode => retcode);
864
865 END insert_findings_increment_load;
866
867 /** 12.16.2005 npanandi: changed a lot of things here
868 added valid report_date_julian in the ETL insert
869 added entries in rci_dr_inc audit table for every load
870 added misc procedures -- check_initial_load_setup and err_mesg
871 **/
872 PROCEDURE insert_findings_initial_load(
873 errbuf IN OUT NOCOPY VARCHAR2
874 ,retcode IN OUT NOCOPY NUMBER) IS
875
876 CURSOR c_find_age IS
877 SELECT
878 DISTINCT finding_id, age_in_days
879 FROM
880 rci_open_findings_f rof;
881
882 CURSOR c_find IS
883 SELECT
884 DISTINCT finding_id
885 FROM
886 rci_open_findings_f rof, eng_change_subjects ecs
887 WHERE
888 rof.finding_id = ecs.change_id AND
889 (ecs.entity_name = 'PROJ_ORG' OR ecs.entity_name = 'PROJ_ORG_PROC');
890
891 CURSOR c_ch_sub(p_change_id eng_change_subjects.change_id%TYPE) IS
892 SELECT
893 entity_name, pk1_value
894 FROM
895 eng_change_subjects ecs
896 WHERE change_id = p_change_id;
897
898 v_finding_id rci_open_findings_f.finding_id%TYPE;
899 v_age rci_open_findings_f.age_in_days%TYPE;
900 v_bucket_1 Number;
901 v_bucket_2 Number;
902 v_bucket_3 Number;
903 v_bucket_4 Number;
904 v_end_date date;
905 v_period number;
906 v_qtr number;
907 v_year number;
908 q number(1);
909
910 l_stmnt_id NUMBER := 0;
914 l_industry VARCHAR2(30) ;
911 l_run_date DATE;
912 l_proc_name VARCHAR2(30);
913 l_status VARCHAR2(30) ;
915
916 l_user_id NUMBER ;
917 l_login_id NUMBER ;
918 l_program_id NUMBER ;
919 l_program_login_id NUMBER ;
920 l_program_application_id NUMBER ;
921 l_request_id NUMBER ;
922
923 BEGIN
924 l_user_id := NVL(fnd_global.USER_ID, -1);
925 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
926 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
927 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
928 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
929 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
930
931 ----dbms_output.put_line( '1 **************' );
932
933 l_stmnt_id := 0;
934 l_proc_name := 'intitial_load';
935 check_initial_load_setup(
936 x_global_start_date => g_global_start_date
937 ,x_rci_schema => g_rci_schema);
938
939 l_stmnt_id := 10;
940 DELETE FROM rci_dr_inc where fact_name = 'RCI_OPEN_FINDINGS_F';
941
942 ----dbms_output.put_line( '2 **************' );
943
944 l_stmnt_id := 20;
945 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_OPEN_FINDINGS_F');
946
947 ----dbms_output.put_line( '3 **************' );
948 l_stmnt_id := 30;
949 l_run_date := sysdate - 5/(24*60);
950
951
952 FOR y IN 2001..2010 LOOP
953 FOR m IN 1..12 LOOP
954 v_end_date := last_day(to_date(to_char(y)||to_char(m),'YYYYMM'));
955
956 CASE m
957 WHEN 3 THEN
958 v_qtr := to_number(to_char(y)||'1');
959 WHEN 6 THEN
960 v_qtr := to_number(to_char(y)||'2');
961 WHEN 9 THEN
962 v_qtr := to_number(to_char(y)||'3');
963 WHEN 12 THEN
964 v_qtr := to_number(to_char(y)||'4');
965 v_year := y;
966 ELSE
967 v_qtr := null;
968 v_year := null;
969 END CASE;
970
971 q := floor((m-1)/3)+1;
972 if(m < 10)then
973 v_period := to_number(to_char(y)||to_char(q)||'0'||to_char(m));
974 else
975 v_period := to_number(to_char(y)||to_char(q)||to_char(m));
976 end if;
977
978 INSERT INTO rci_open_findings_f (
979 finding_id, priority_code, reason_code, phase_code,
980 age_in_days,
981 need_by_date, completion_date,
982 open, past_due,
983 period_year, period_num, quarter_num,
984 ent_period_id, ent_qtr_id, ent_year_id,
985 report_date_julian,
986 created_by,last_update_login,creation_date,last_updated_by,last_update_date)
987 SELECT
988 eec1.change_id , eec1.priority_code, eec1.reason_code, eec1.status_code,
989 ROUND(v_end_date-eec1.initiation_date),
990 eec1.need_by_date, eec1.implementation_date,
991 case when(eec1.STATUS_CODE not in (0,11) or (eec1.status_code=11 and round(eec1.last_update_date-v_end_date) > 0 )) then 1 else 0 end,
992 -- case when( implementation_date is null or round(implementation_date-v_end_date)>0) then 1 else 0 end,
993 case when( round(v_end_date-need_by_date)>0 and (implementation_date is null or (implementation_date-v_end_date) >0 )) then 1 else 0 end,
994 y, m, q,
995 v_period, v_qtr, v_year,
996 to_number(to_char(v_end_date,'J')),/** 12.16.2005 npanandi: added report_date_julian **/
997 G_USER_ID, G_USER_ID, SYSDATE, G_USER_ID, SYSDATE
998 FROM
999 eng_engineering_changes eec1
1000 WHERE
1001 eec1.change_mgmt_type_code = 'AMW_PROJ_FINDING';
1002 END LOOP;--inner loop
1003 END LOOP;--outer loop
1004
1005 -- to update the bucket ids
1006 FOR r_find_age IN c_find_age LOOP
1007 v_finding_id := r_find_age.finding_id;
1008 v_age := r_find_age.age_in_days;
1009 fn_get_Bucket_Id(v_age, v_bucket_1,v_bucket_2,v_bucket_3,v_bucket_4);
1010 UPDATE rci_open_findings_f
1011 SET age_bucket_1 = v_bucket_1 , age_bucket_2 = v_bucket_2,
1012 age_bucket_3 = v_bucket_3, age_bucket_4 = v_bucket_4
1013 WHERE finding_id = v_finding_id;
1014 END LOOP;
1015
1016 -- to update the organization id and process id
1017 FOR r_find IN c_find LOOP
1018 v_finding_id := r_find.finding_id;
1019 FOR r_ch_sub IN c_ch_sub(v_finding_id) LOOP
1020 IF r_ch_sub.entity_name = 'PROJ_ORG' THEN
1021 UPDATE rci_open_findings_f
1022 SET organization_id = r_ch_sub.pk1_value
1023 WHERE finding_id = v_finding_id;
1024 ELSIF r_ch_sub.entity_name = 'PROJ_ORG_PROC' THEN
1025 UPDATE rci_open_findings_f
1026 SET process_id = r_ch_sub.pk1_value
1027 WHERE finding_id = v_finding_id;
1028 END IF;
1029 END LOOP;
1030 END LOOP;
1031
1032 begin
1033 UPDATE rci_dr_inc
1034 SET last_run_date = l_run_date
1035 ,last_update_date = sysdate
1039 ,program_login_id = l_program_login_id
1036 ,last_updated_by = l_user_id
1037 ,last_update_login = l_login_id
1038 ,program_id = l_program_id
1040 ,program_application_id = l_program_application_id
1041 ,request_id = l_request_id
1042 WHERE fact_name = 'RCI_OPEN_FINDINGS_F' ;
1043
1044 IF (SQL%NOTFOUND) THEN
1045 RAISE NO_DATA_FOUND;
1046 END IF;
1047
1048 exception
1049 when NO_DATA_FOUND then
1050 INSERT INTO rci_dr_inc( fact_name
1051 ,last_run_date
1052 ,created_by
1053 ,creation_date
1054 ,last_update_date
1055 ,last_updated_by
1056 ,last_update_login
1057 ,program_id
1058 ,program_login_id
1059 ,program_application_id
1060 ,request_id ) VALUES (
1061 'RCI_OPEN_FINDINGS_F'
1062 ,l_run_date
1063 ,l_user_id
1064 ,sysdate
1065 ,sysdate
1066 ,l_user_id
1067 ,l_login_id
1068 ,l_program_id
1069 ,l_program_login_id
1070 ,l_program_application_id
1071 ,l_request_id );
1072 end;
1073
1074 l_stmnt_id := 70;
1075 commit;
1076 retcode := C_OK;
1077
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 retcode := C_ERROR;
1081 ----dbms_output.put_line( 'In OTHERS **************' );
1082 ----dbms_output.put_line( 'errmsdg: '||substr ((l_proc_name || ' #' ||to_char (l_stmnt_id) || ': ' || SQLERRM),
1083 ----- 1, C_ERRBUF_SIZE) );
1084 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
1085 ROLLBACK;
1086 RAISE;
1087
1088 END insert_findings_initial_load;
1089
1090 PROCEDURE insert_remeds_increment_load(
1091 errbuf IN OUT NOCOPY VARCHAR2
1092 ,retcode IN OUT NOCOPY NUMBER) IS
1093 BEGIN
1094 insert_remeds_initial_load(
1095 errbuf => errbuf
1096 ,retcode => retcode);
1097 END insert_remeds_increment_load;
1098
1099 /** 12.16.2005 npanandi: changed a lot of things here
1100 added valid report_date_julian in the ETL insert
1101 added entries in rci_dr_inc audit table for every load
1102 added misc procedures -- check_initial_load_setup and err_mesg
1103 **/
1104 PROCEDURE insert_remeds_initial_load(
1105 errbuf IN OUT NOCOPY VARCHAR2
1109 SELECT
1106 ,retcode IN OUT NOCOPY NUMBER) IS
1107
1108 CURSOR c_remed_age IS
1110 DISTINCT finding_id, age_in_days
1111 FROM
1112 rci_open_remediations_f;
1113
1114 CURSOR c_remed IS
1115 SELECT
1116 DISTINCT finding_id, age_in_days
1117 FROM
1118 rci_open_remediations_f rof, eng_change_subjects ecs
1119 WHERE
1120 rof.finding_id = ecs.change_id AND
1121 (ecs.entity_name = 'PROJ_ORG' OR ecs.entity_name = 'PROJ_ORG_PROC');
1122
1123 CURSOR c_ch_sub(p_change_id eng_change_subjects.change_id%TYPE) IS
1124 SELECT
1125 entity_name, pk1_value
1126 FROM
1127 eng_change_subjects ecs
1128 WHERE change_id = p_change_id;
1129 v_finding_id rci_open_remediations_f.finding_id%TYPE;
1130 v_age rci_open_remediations_f.age_in_days%TYPE;
1131 v_bucket_1 Number;
1132 v_bucket_2 Number;
1133 v_bucket_3 Number;
1134 v_bucket_4 Number;
1135 v_end_date date;
1136 v_period number;
1137 v_qtr number;
1138 v_year number;
1139 q number(1);
1140
1141 l_stmnt_id NUMBER := 0;
1142 l_run_date DATE;
1143 l_proc_name VARCHAR2(30);
1144 l_status VARCHAR2(30) ;
1145 l_industry VARCHAR2(30) ;
1146
1147 l_user_id NUMBER ;
1148 l_login_id NUMBER ;
1149 l_program_id NUMBER ;
1150 l_program_login_id NUMBER ;
1151 l_program_application_id NUMBER ;
1152 l_request_id NUMBER ;
1153 BEGIN
1154 l_user_id := NVL(fnd_global.USER_ID, -1);
1155 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1156 l_program_id := NVL(fnd_global.CONC_PROGRAM_ID,-1);
1157 l_program_login_id := NVL(fnd_global.CONC_LOGIN_ID,-1);
1158 l_program_application_id := NVL(fnd_global.PROG_APPL_ID,-1);
1159 l_request_id := NVL(fnd_global.CONC_REQUEST_ID,-1);
1160
1161 ----dbms_output.put_line( '1 **************' );
1162
1163 l_stmnt_id := 0;
1164 l_proc_name := 'intitial_load';
1165 check_initial_load_setup(
1166 x_global_start_date => g_global_start_date
1167 ,x_rci_schema => g_rci_schema);
1168
1169 l_stmnt_id := 10;
1170 DELETE FROM rci_dr_inc where fact_name = 'RCI_OPEN_REMEDIATIONS_F';
1171
1172 ----dbms_output.put_line( '2 **************' );
1173
1174 l_stmnt_id := 20;
1175 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || g_rci_schema || '.RCI_OPEN_REMEDIATIONS_F');
1176
1177 ----dbms_output.put_line( '3 **************' );
1178 l_stmnt_id := 30;
1179 l_run_date := sysdate - 5/(24*60);
1180
1181 FOR y IN 2001..2010 LOOP
1182 FOR m IN 1..12 LOOP
1183 v_end_date := last_day(to_date(to_char(y)||to_char(m),'YYYYMM'));
1184
1185 CASE m
1186 WHEN 3 THEN
1187 v_qtr := to_number(to_char(y)||'1');
1188 WHEN 6 THEN
1189 v_qtr := to_number(to_char(y)||'2');
1190 WHEN 9 THEN
1191 v_qtr := to_number(to_char(y)||'3');
1192 WHEN 12 THEN
1193 v_qtr := to_number(to_char(y)||'4');
1194 v_year := y;
1195 ELSE
1196 v_qtr := null;
1197 v_year := null;
1198 END CASE;
1199
1200 q := floor((m-1)/3)+1;
1201 if(m < 10)then
1202 v_period := to_number(to_char(y)||to_char(q)||'0'||to_char(m));
1203 else
1204 v_period := to_number(to_char(y)||to_char(q)||to_char(m));
1205 end if;
1206
1207 INSERT INTO rci_open_remediations_f(
1208 finding_id, remediation_id, priority_code, reason_code, phase_code,
1209 age_in_days,
1210 need_by_date, completion_date,
1211 open, past_due,
1212 period_year, period_num, quarter_num,
1213 ent_period_id, ent_qtr_id, ent_year_id,
1214 report_date_julian,
1215 created_by,last_update_login,creation_date,last_updated_by,last_update_date)
1216 SELECT
1217 ecor.change_id , ecor.object_to_id1, eec1.priority_code, eec1.reason_code, eec1.status_code,
1218 ROUND(v_end_date-eec1.initiation_date),
1219 eec1.need_by_date, eec1.implementation_date,
1220 case when(eec1.STATUS_CODE not in (0,11) or (eec1.status_code=11 and round(eec1.last_update_date-v_end_date) > 0 )) then 1 else 0 end,
1221 -- case when( implementation_date is null or round(implementation_date-v_end_date)>0) then 1 else 0 end,
1222 case when( round(v_end_date-need_by_date)>0 and (implementation_date is null or (implementation_date-v_end_date) >0 )) then 1 else 0 end,
1223 y, m, q,
1224 v_period, v_qtr, v_year,
1225 to_number(to_char(v_end_date,'J')),/** 12.16.2005 npanandi: added report_date_julian **/
1226 G_USER_ID, G_USER_ID, SYSDATE, G_USER_ID, SYSDATE
1227 FROM
1228 eng_engineering_changes eec1, eng_change_obj_relationships ecor
1229 WHERE
1230 eec1.change_mgmt_type_code = 'AMW_REMEDIATION'
1231 AND ecor.relationship_code = 'RESOLVED_BY'
1232 AND eec1.change_id = ecor.object_to_id1;
1233
1234 END LOOP;--inner loop
1235 END LOOP;--outer loop
1236
1237 -- to update the bucket ids
1238 FOR r_remed_age IN c_remed_age LOOP
1242 UPDATE rci_open_remediations_f
1239 v_finding_id := r_remed_age.finding_id;
1240 v_age := r_remed_age.age_in_days;
1241 fn_get_Bucket_Id(v_age, v_bucket_1,v_bucket_2,v_bucket_3,v_bucket_4);
1243 SET age_bucket_1 = v_bucket_1 , age_bucket_2 = v_bucket_2,
1244 age_bucket_3 = v_bucket_3, age_bucket_4 = v_bucket_4
1245 WHERE finding_id = v_finding_id;
1246 END LOOP;
1247
1248 -- to update the organization id and process id
1249 FOR r_remed in c_remed LOOP
1250 v_finding_id := r_remed.finding_id;
1251 FOR r_ch_sub in c_ch_sub(v_finding_id) LOOP
1252 IF r_ch_sub.entity_name = 'PROJ_ORG' THEN
1253 update rci_open_remediations_f set organization_id = r_ch_sub.pk1_value
1254 WHERE finding_id = v_finding_id;
1255 ELSIF r_ch_sub.entity_name = 'PROJ_ORG_PROC' THEN
1256 update rci_open_remediations_f set process_id = r_ch_sub.pk1_value
1257 WHERE finding_id = v_finding_id;
1258 END IF;
1259 END LOOP;
1260 END LOOP;
1261
1262 begin
1263 UPDATE rci_dr_inc
1264 SET last_run_date = l_run_date
1265 ,last_update_date = sysdate
1266 ,last_updated_by = l_user_id
1267 ,last_update_login = l_login_id
1268 ,program_id = l_program_id
1269 ,program_login_id = l_program_login_id
1270 ,program_application_id = l_program_application_id
1271 ,request_id = l_request_id
1272 WHERE fact_name = 'RCI_OPEN_REMEDIATIONS_F' ;
1273
1274 IF (SQL%NOTFOUND) THEN
1275 RAISE NO_DATA_FOUND;
1276 END IF;
1277
1278 exception
1279 when NO_DATA_FOUND then
1280 INSERT INTO rci_dr_inc( fact_name
1281 ,last_run_date
1282 ,created_by
1283 ,creation_date
1284 ,last_update_date
1285 ,last_updated_by
1286 ,last_update_login
1287 ,program_id
1288 ,program_login_id
1289 ,program_application_id
1290 ,request_id ) VALUES (
1291 'RCI_OPEN_REMEDIATIONS_F'
1292 ,l_run_date
1293 ,l_user_id
1294 ,sysdate
1295 ,sysdate
1296 ,l_user_id
1297 ,l_login_id
1298 ,l_program_id
1299 ,l_program_login_id
1300 ,l_program_application_id
1301 ,l_request_id );
1302 end;
1303
1304 l_stmnt_id := 70;
1305 commit;
1306 retcode := C_OK;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 retcode := C_ERROR;
1311 ----dbms_output.put_line( 'In OTHERS **************' );
1312 ----dbms_output.put_line( 'errmsdg: '||substr ((l_proc_name || ' #' ||to_char (l_stmnt_id) || ': ' || SQLERRM),
1313 ----- 1, C_ERRBUF_SIZE) );
1314 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name, l_stmnt_id));
1315 ROLLBACK;
1316 RAISE;
1317 END insert_remeds_initial_load;
1318
1319 PROCEDURE fn_Get_Bucket_Id(age_in_days Number, v_1 out NOCOPY Number, v_2 out NOCOPY Number, v_3 out NOCOPY Number, v_4 out NOCOPY Number)
1320 IS
1321 v_bucket_id Number := 0;
1322 BEGIN
1323 v_1 := 0; v_2 := 0; v_3 := 0; v_4 := 0;
1324 --todo remove the hardcoding
1325 CASE
1326 WHEN age_in_days BETWEEN 0 AND 1 THEN
1327 v_bucket_id := 1;
1328 v_1 := 1;
1329 WHEN age_in_days BETWEEN 2 AND 5 THEN
1330 v_bucket_id := 2;
1331 v_2 := 1;
1332 WHEN age_in_days BETWEEN 6 AND 10 THEN
1333 v_bucket_id := 3;
1334 v_3 := 1;
1335 WHEN age_in_days > 10 THEN
1336 v_bucket_id := 4;
1337 v_4 := 1;
1338 ELSE
1339 v_1 := 0; v_2 := 0; v_3 := 0; v_4 := 0;
1340 END CASE;
1341 END fn_get_Bucket_Id;
1345 v_year varchar2(4);
1342
1343 FUNCTION get_last_day(date_id NUMBER, type VARCHAR2) return varchar2
1344 IS
1346 v_month varchar2(2);
1347 v_qtr number(1);
1348 BEGIN
1349 IF type='M' THEN
1350 v_year := SUBSTR(date_id,1,4);
1351 v_month := SUBSTR(date_id,6);
1352 ELSIF type='Q' THEN
1353 v_year := SUBSTR(date_id,1,4);
1354 v_qtr := SUBSTR(date_id,5,1);
1355 CASE v_qtr
1356 WHEN 1 THEN v_month := '03';
1357 WHEN 2 THEN v_month := '06';
1358 WHEN 3 THEN v_month := '09';
1359 WHEN 4 THEN v_month := '12';
1360 END CASE;
1361 ELSIF type='Y' THEN
1362 v_year := date_id;
1363 v_month := '12';
1364 END IF;
1365 return v_year||v_month;
1366 END;
1367
1368 -- Common Procedures Definitions
1369 -- check_initial_load_setup
1370 -- Gets the GSD.
1371 -- History:
1372 -- Date Author Action
1373 -- 12/16/2005 Panandikar Nilesh G Defined procedure.
1374
1375 PROCEDURE check_initial_load_setup (
1376 x_global_start_date OUT NOCOPY DATE
1377 ,x_rci_schema OUT NOCOPY VARCHAR2)
1378 IS
1379 l_proc_name VARCHAR2 (40);
1380 l_stmt_id NUMBER;
1381 l_setup_good BOOLEAN;
1382 l_status VARCHAR2(30) ;
1383 l_industry VARCHAR2(30) ;
1384 l_message VARCHAR2(100);
1385 BEGIN
1386
1387 -- Initialization
1388 l_proc_name := 'setup_load';
1389 l_stmt_id := 0;
1390
1391 -- Check for the global start date setup.
1392 -- These parameter must be set up prior to any DBI load.
1393
1394 x_global_start_date := trunc (bis_common_parameters.get_global_start_date);
1395 IF (x_global_start_date IS NULL) THEN
1396 l_message := ' Global Start Date is NULL ';
1397 RAISE INITIALIZATION_ERROR;
1398 END IF;
1399
1400 l_setup_good := fnd_installation.get_app_info('AMW', l_status, l_industry, x_rci_schema);
1401 IF (l_setup_good = FALSE OR x_rci_schema IS NULL) THEN
1402 l_message := 'Schema not found';
1403 RAISE INITIALIZATION_ERROR;
1404 END IF;
1405 EXCEPTION
1406 WHEN INITIALIZATION_ERROR THEN
1407 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (INITIALIZATION_ERROR_MESG || ':' || l_message,l_proc_name, l_stmt_id));
1408 WHEN OTHERS THEN
1409 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,l_stmt_id));
1410 RAISE;
1411 END check_initial_load_setup;
1412
1413 -- err_mesg
1414 -- History:
1415 -- Date Author Action
1416 -- 12/16/2005 Panandikar Nilesh G Defined procedure.
1417
1418 FUNCTION err_mesg (
1419 p_mesg IN VARCHAR2
1420 ,p_proc_name IN VARCHAR2 DEFAULT NULL
1421 ,p_stmt_id IN NUMBER DEFAULT -1)
1425 l_stmt_id NUMBER;
1422 RETURN VARCHAR2
1423 IS
1424 l_proc_name VARCHAR2 (60);
1426 l_formatted_message VARCHAR2 (300) ;
1427 BEGIN
1428 l_formatted_message := substr ((p_proc_name || ' #' ||to_char (p_stmt_id) || ': ' || p_mesg),
1429 1, C_ERRBUF_SIZE);
1430 RETURN l_formatted_message;
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 -- the exception happened in the exception reporting function !!
1434 -- return with ERROR.
1435 l_formatted_message := 'Error in error reporting.';
1436 RETURN l_formatted_message;
1437 END err_mesg;
1438
1439 END RCI_OPEN_REMED_SUMM_PKG;