[Home] [Help]
PACKAGE BODY: APPS.BIV_HS_PROB_AVOID_REPORT_PKG
Source
1 PACKAGE BODY BIV_HS_PROB_AVOID_REPORT_PKG AS
2 -- $Header: bivhprob.pls 115.23 2004/01/23 04:55:17 vganeshk ship $ */
3 -- Problem Avoidence Report
4 -- global params
5 g_err VARCHAR2(500);
6 g_prod_id VARCHAR2(20);
7 g_product_version VARCHAR2(20);
8 g_component VARCHAR2(20);
9 g_subcomponent VARCHAR2(20);
10 g_platform VARCHAR2(20);
11 g_created_period_type VARCHAR2(20);
12 g_created_start VARCHAR2(20);
13 g_created_end VARCHAR2(20);
14 g_closed_period_type VARCHAR2(20);
15 g_closed_start VARCHAR2(20);
16 g_closed_end VARCHAR2(20);
17
18 g_report_code VARCHAR2(30);
19
20 g_query VARCHAR2(2300);
21 g_select VARCHAR2(1000);
22 g_table VARCHAR2(300);
23 g_where VARCHAR2(1000);
24 g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;
25 g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;
26 g_session_id NUMBER ;
27 g_debug_flag VARCHAR2(1) ;
28
29 /*** 1/30/03 this procedure is not used anymore **/
30 PROCEDURE get_params (p_param_str in VARCHAR2) IS
31 BEGIN
32 /****
33 g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
34 g_product_version := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_VER');
35 g_component := biv_core_pkg.get_parameter_value(p_param_str,'P_COMP_ID');
36 g_subcomponent := biv_core_pkg.get_parameter_value(p_param_str,'P_SUBCOMP_ID');
37 g_platform := biv_core_pkg.get_parameter_value(p_param_str,'P_PLATFORM_ID');
38 g_created_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
39 g_created_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_ST');
40 g_created_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CR_END');
41 g_closed_period_type := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
42 g_closed_start := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_ST');
43 g_closed_end := biv_core_pkg.get_parameter_value(p_param_str,'P_CL_END');
44 ****/
45 null;
46 END get_params;
47
48 --==============================================================================
49 -- Problem Avoidence Report
50 --==============================================================================
51 PROCEDURE load_prob_avoid_rpt(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
52 l_cur_id PLS_INTEGER;
53 l_return_num PLS_INTEGER := 0;
54 l_start_date DATE;
55 l_end_date DATE;
56 l_ttl_recs number;
57 l_session_id number := biv_core_pkg.get_session_id;
58 l_ttl_meaning fnd_lookups.meaning % type :=
59 biv_core_pkg.get_lookup_meaning('TOTAL');
60 l_param_for_drill varchar2(600);
61 BEGIN
62 g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
63 g_session_id := biv_core_pkg.get_session_id;
64 biv_core_pkg.g_report_type := 'HS';
65 biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
66 -- report code
67 g_report_code := 'BIV_HS_PROB_AVOID';
68 -- get all param
69 --get_params(p_param_str);
70 biv_core_pkg.get_report_parameters(p_param_str);
71
72 g_select := get_prob_avoid_rpt_select;
73 g_table := get_prob_avoid_rpt_table;
74 biv_core_pkg.get_where_clause(g_table,g_where);
75 g_where := g_where || get_prob_avoid_rpt_where;
76
77 g_query := g_select || g_table || g_where;
78
79 if g_debug_flag = 'Y' then
80 biv_core_pkg.biv_debug('Parameter: '||p_param_str,
81 g_report_code);
82 biv_core_pkg.biv_debug(g_query,g_report_code);
83 commit;
84 end if;
85
86 l_cur_id := DBMS_SQL.OPEN_CURSOR;
87 DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
88 DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_report_code',g_report_code);
89
90 -- sessionid also written to biv_debug
91 if (g_debug_flag = 'Y') then
92 biv_core_pkg.biv_debug('SessionId: '||g_session_id,
93 g_report_code);
94 end if;
95
96 DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);
97 biv_core_pkg.bind_all_variables(l_cur_id);
98 dbms_sql.bind_variable(l_cur_id, ':session_id', l_session_id);
99 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
100
101 -- update the total percentage column
102 g_query := 'UPDATE biv_tmp_hs1
103 SET col12 =
104 round((100*col12/(select sum(col10)
105 from biv_tmp_hs1 b
106 where b.report_code = ''BIV_HS_PROB_AVOID''
107 and b.session_id =
108 biv_core_pkg.get_session_id
109 )),2)
110 WHERE report_code = ''BIV_HS_PROB_AVOID'' and
111 session_id = :session_id ';
112 DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
113 DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);
114 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
115 DBMS_SQL.CLOSE_CURSOR(l_cur_id);
116
117 -- UPDATE COMP SUBCOMP DESC
118 execute immediate '
119 UPDATE biv_tmp_hs1
120 set col6 = (select description from mtl_system_items_vl
121 where inventory_item_id = to_number(col6) and rownum = 1)
122 WHERE report_code = ''BIV_HS_PROB_AVOID'' and
123 session_id = :session_id and
124 col6 is not null ' using biv_core_pkg.get_session_id;
125
126 execute immediate '
127 UPDATE biv_tmp_hs1
128 set col8 = (select description from mtl_system_items_vl
129 where inventory_item_id = to_number(col8) and rownum = 1),
130 col14 = 1
131 WHERE report_code = ''BIV_HS_PROB_AVOID'' and
132 session_id = :session_id and
133 col8 is not null ' using biv_core_pkg.get_session_id;
134
135 -- update drilldown link
136 l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
137 biv_core_pkg.g_param_sep ||
138 biv_core_pkg.reconstruct_param_str;
139 update biv_tmp_hs1
140 set col9 = l_param_for_drill || 'P_PRD_ID' ||
141 biv_core_pkg.g_value_sep || nvl(col2,biv_core_pkg.g_null) ||
142 biv_core_pkg.g_param_sep || 'P_COMP_ID' ||
143 biv_core_pkg.g_value_sep || nvl(col5,biv_core_pkg.g_null) ||
144 biv_core_pkg.g_param_sep || 'P_SUBCOMP_ID' ||
145 biv_core_pkg.g_value_sep || nvl(col7,biv_core_pkg.g_null) ||
146 biv_core_pkg.g_param_sep
147 where session_id = g_session_id
148 and report_code = 'BIV_HS_PROB_AVOID';
149 -- update drilldown link complete
150 --- Add total Row
151 select count(*) into l_ttl_recs
152 from biv_tmp_hs1
153 WHERE report_code = 'BIV_HS_PROB_AVOID' and
154 session_id = biv_core_pkg.get_session_id;
155 if (l_ttl_recs > 1 /*and l_ttl_recs < biv_core_pkg.g_disp*/ ) then
156 if (g_debug_flag = 'Y') then
157 biv_core_pkg.biv_debug('Inserting Total Row','BIV_HS_PROB_AVOID');
158 end if;
159 l_param_for_drill := 'BIV_HS_PROB_AVOID_RES' ||
160 biv_core_pkg.g_param_sep ||
161 biv_core_pkg.reconstruct_param_str;
162 insert into biv_tmp_hs1(report_code, session_id, rowno, col2, col10,
163 col14, col9)
164 select 'BIV_HS_PROB_AVOID', l_session_id, max(rowno)+1,
165 l_ttl_meaning,sum(col10),2, l_param_for_drill
166 from biv_tmp_hs1
167 where report_code = 'BIV_HS_PROB_AVOID'
168 and session_id = l_session_id;
169 end if;
170 EXCEPTION
171 WHEN OTHERS THEN
172 if (g_debug_flag = 'Y') then
173 g_err := 'Err in BIV_HS_PROB_AVOID_REPORT_PKG. ' ||
174 'load_prob_avoid_rpt:' || substr(sqlerrm,1,500);
175 biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID');
176 end if;
177
178 END load_prob_avoid_rpt;
179
180 -- get select
181 FUNCTION get_prob_avoid_rpt_select RETURN VARCHAR2 IS
182 l_select_stmt varchar2(1000);
183 l_na_desc fnd_lookups.meaning%type :=
184 biv_core_pkg.get_lookup_meaning('NA');
185 BEGIN
186 l_select_stmt := 'INSERT INTO biv_tmp_hs1 (report_code, session_id,col2, ';
187 l_select_stmt := l_select_stmt || 'col4,col6,col8,col10,col12,col5,col7) ';
188 l_select_stmt := l_select_stmt || 'SELECT :g_report_code, :session_id,';
189 l_select_stmt := l_select_stmt || 'sr.inventory_item_id prod_id, ';
190 l_select_stmt := l_select_stmt || 'substr(nvl(inv1.description,''' || l_na_desc || '''),1,50) product, ';
191 l_select_stmt := l_select_stmt || 'sr.inv_component_id component, ';
192 l_select_stmt := l_select_stmt || 'sr.inv_subcomponent_id subcomponent, ';
193
194 l_select_stmt := l_select_stmt || '
195 count(distinct sr.incident_id),
196 count(distinct sr.incident_id),
197 sr.inv_component_id, sr.inv_subcomponent_id ';
198 return l_select_stmt;
199 END get_prob_avoid_rpt_select;
200
201 -- get table
202 FUNCTION get_prob_avoid_rpt_table RETURN VARCHAR2 IS
203 l_table_stmt varchar2(500);
204 BEGIN
205 -- Change for Bug 3386946
206 l_table_stmt := '
207 FROM
208 cs_incidents_b_sec sr,
209 mtl_system_items_vl inv1
210 ';
211 return l_table_stmt;
212 END get_prob_avoid_rpt_table;
213
214 -- get where
215 FUNCTION get_prob_avoid_rpt_where RETURN VARCHAR2 IS
216 l_where_stmt varchar2(1000);
217 BEGIN
218 -- put outter join on inv* columns when testing
219 -- there are many null column in inv*
220 l_where_stmt := '
221 and inv1.organization_id (+)= fnd_profile.value(''CS_INV_VALIDATION_ORG'')
222 and sr.inventory_item_id = inv1.inventory_item_id(+) ';
223
224
225
226 l_where_stmt := l_where_stmt || '
227 GROUP BY
228 sr.inventory_item_id,
229 inv1.description,
230 sr.inv_component_id,
231 sr.inv_subcomponent_id ';
232
233 if g_platform is not null then
234 l_where_stmt := l_where_stmt || '
235 ,sr.platform_id
236 ';
237 end if;
238
239 return l_where_stmt;
240 END get_prob_avoid_rpt_where;
241
242
243 --==============================================================================
244 -- Problem Avoidance Resolution Report
245 --==============================================================================
246 PROCEDURE load_prob_avoid_res_rpt(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
247 l_cur_id PLS_INTEGER;
248 l_return_num PLS_INTEGER := 0;
249 l_start_date DATE;
250 l_end_date DATE;
251 l_new_param_str varchar2(500);
252 BEGIN
253 g_debug_flag := nvl(fnd_profile.value('BIV:DEBUG'),'N');
254 g_session_id := biv_core_pkg.get_session_id;
255 biv_core_pkg.g_report_type := 'HS';
256 biv_core_pkg.clean_dcf_table('BIV_TMP_HS1');
257 -- report code
258 g_report_code := 'BIV_HS_PROB_AVOID_RES';
259 -- get all param
260 --get_params(p_param_str);
261 biv_core_pkg.get_report_parameters(p_param_str);
262
263 g_select := get_prob_avoid_res_rpt_select;
264 g_table := get_prob_avoid_res_rpt_table;
265 biv_core_pkg.get_where_clause(g_table,g_where);
266 g_where := g_where || get_prob_avoid_res_rpt_where;
267
268 g_query := g_select || g_table || g_where;
269
270 if g_debug_flag = 'Y' then
271 biv_core_pkg.biv_debug('Parameter: '||p_param_str,
272 g_report_code);
273 biv_core_pkg.biv_debug(g_query,g_report_code);
274 commit;
275 end if;
276
277 l_cur_id := DBMS_SQL.OPEN_CURSOR;
278 DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
279 DBMS_SQL.BIND_VARIABLE(l_cur_id,':report_code',g_report_code);
280 DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);
281
282 biv_core_pkg.bind_all_variables(l_cur_id);
283 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
284
285 -- update the total percentage column
286 g_query := 'UPDATE biv_tmp_hs1
287 SET col4 =
288 round((100*col4/(select sum(b.col2)
289 from biv_tmp_hs1 b
290 where b.report_code = ''BIV_HS_PROB_AVOID_RES''
291 and b.session_id =
292 biv_core_pkg.get_session_id
293 )),2)
294 WHERE report_code = ''BIV_HS_PROB_AVOID_RES'' and
295 session_id = :session_id ';
296 DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
297 DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);
298 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
299 DBMS_SQL.CLOSE_CURSOR(l_cur_id);
300
301 l_new_param_str := 'BIV_SERVICE_REQUEST' ||
302 biv_core_pkg.g_param_sep ||
303 biv_core_pkg.reconstruct_param_str;
304 update biv_tmp_hs1
305 set col1 = l_new_param_str || 'P_RESL_CODE' ||
306 biv_core_pkg.g_value_sep ||
307 nvl(col5,biv_core_pkg.g_null) ||
308 biv_core_pkg.g_param_sep
309 where report_code = 'BIV_HS_PROB_AVOID_RES'
310 and session_id = g_session_id;
311 EXCEPTION
312 WHEN OTHERS THEN
313 if (g_debug_flag = 'Y') then
314 g_err := 'Err in BIV_HS_PROB_AVOID_REPORT_PKG.' ||
315 'load_prob_avoid_res_rpt:' || substr(sqlerrm,1,500);
316 biv_core_pkg.biv_debug(g_err,'BIV_HS_PROB_AVOID_RES');
317 end if;
318
319 END load_prob_avoid_res_rpt;
320
321 -- get select
322 FUNCTION get_prob_avoid_res_rpt_select RETURN VARCHAR2 IS
323 l_select_stmt varchar2(1000);
324 BEGIN
325 l_select_stmt := 'INSERT INTO biv_tmp_hs1 (report_code,session_id,col2, ';
326 l_select_stmt := l_select_stmt || 'col4,col5,col6) ';
327 l_select_stmt := l_select_stmt || 'SELECT :report_code,:session_id, ';
328
329 l_select_stmt := l_select_stmt || '
330 count(sr.incident_id),
331 count(sr.incident_id),sr.resolution_code,clr.meaning ';
332 return l_select_stmt;
333 END get_prob_avoid_res_rpt_select;
334
335 -- get table
336 FUNCTION get_prob_avoid_res_rpt_table RETURN VARCHAR2 IS
337 l_table_stmt varchar2(100);
338 BEGIN
339 -- Change for Bug 3386946
340 l_table_stmt := '
341 FROM
342 cs_incidents_b_sec sr,
343 cs_lookups clr ';
344 return l_table_stmt;
345 END get_prob_avoid_res_rpt_table;
346
347 -- get where
348 FUNCTION get_prob_avoid_res_rpt_where RETURN VARCHAR2 IS
349 l_where_stmt varchar2(1000);
350 BEGIN
351 l_where_stmt := '
352 and
353 sr.resolution_code = clr.lookup_code (+) and
354 clr.lookup_type (+) = ''REQUEST_RESOLUTION_CODE'' ';
355 l_where_stmt := l_where_stmt || '
356 GROUP BY sr.resolution_code,
357 clr.meaning ';
358
359
360 return l_where_stmt;
361 END get_prob_avoid_res_rpt_where;
362 END BIV_HS_PROB_AVOID_REPORT_PKG;