[Home] [Help]
PACKAGE BODY: APPS.BIV_DASH_BIN_PKG
Source
1 PACKAGE BODY BIV_DASH_BIN_PKG AS
2 -- $Header: bivdsbnb.pls 115.26 2004/01/23 04:53:11 vganeshk ship $ */
3 -- This package is used to render the picasso BIN in dashboard
4 -- all procedure are called by seeded AK_REGION via JTF
5
6 g_err VARCHAR2(500);
7 g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;
8 g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;
9 g_session_id NUMBER := biv_core_pkg.get_session_id;
10 g_debug_flag VARCHAR2(1) := nvl(fnd_profile.value('BIV:DEBUG'),'N');
11 g_esc_frm_lst varchar2(500);
12 g_esc_whr_cls varchar2(2000);
13 g_esc_sel_stt varchar2(2000);
14
15 -- Service Request Bin
16 PROCEDURE load_sr_bin(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
17 l_index NUMBER := 1;
18 x_where_clause varchar2(1000);
19 l_query VARCHAR2(3000);
20 l_select VARCHAR2(500);
21 l_table VARCHAR2(500);
22 l_where VARCHAR2(1000);
23 l_cur_id PLS_INTEGER;
24 l_return_num PLS_INTEGER := 0;
25 -- Variables l_total and l_totalurl, l_url added for enh 2914005
26 l_total VARCHAR(500);
27 l_totalurl VARCHAR(3000);
28 l_url VARCHAR(3000);
29
30 l_ogrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_OGRP');
31 l_agrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_AGRP');
32 l_mgr_id VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_MGR_ID');
33 BEGIN
34 biv_core_pkg.g_report_id := 'BIV_DASH_SR_BIN';
35 biv_core_pkg.clean_dcf_table('BIV_TMP_BIN');
36 g_session_id := biv_core_pkg.get_session_id;
37 biv_core_pkg.get_report_parameters(p_param_str);
38
39 l_select := 'INSERT INTO biv_tmp_bin (report_code, session_id, col2, col3, col4) ';
40 l_select := l_select || 'SELECT :report_id,:session_id,stat.name, ';
41 l_select := l_select || '''BIV_SERVICE_REQUEST';
42
43 /* Parameters being added to l_url rather than l_select to accomadate
44 enhancement 2914005
45 */
46 if l_ogrp is not null then
47 l_url := l_url || g_p_sp || 'P_OGRP' || g_v_sp || l_ogrp;
48 end if;
49 if l_agrp is not null then
50 l_url := l_url || g_p_sp || 'P_AGRP' || g_v_sp || l_agrp;
51 end if;
52 if l_mgr_id is not null then
53 l_url := l_url || g_p_sp || 'P_MGR_ID' || g_v_sp || l_mgr_id;
54 end if;
55
56 l_select := l_select || l_url || g_p_sp || 'P_STS_ID' || g_v_sp;
57 l_select := l_select || '''||stat.incident_status_id||''' || g_p_sp || '''';
58 l_select := l_select || ',count(sr.incident_id) ';
59 -- Change for Bug 3386946
60 l_table := ' FROM cs_incidents_b_sec sr, cs_incident_statuses_vl stat ';
61 biv_core_pkg.get_where_clause(l_table,x_where_clause);
62 l_where := x_where_clause || '
63 and sr.incident_status_id = stat.incident_status_id
64 and stat.incident_subtype = ''INC''
65 and nvl(stat.close_flag,''N'') != ''Y''
66 GROUP BY stat.incident_status_id,stat.name,stat.description
67 ';
68 l_query := l_select || ' ' || l_table || ' ' || l_where;
69
70 if g_debug_flag = 'Y' then
71 biv_core_pkg.biv_debug(l_query,biv_core_pkg.g_report_id);
72 commit;
73 end if;
74
75 l_cur_id := DBMS_SQL.OPEN_CURSOR;
76 DBMS_SQL.PARSE(l_cur_id,l_query,DBMS_SQL.NATIVE);
77 biv_core_pkg.bind_all_variables(l_cur_id);
78 DBMS_SQL.BIND_VARIABLE(l_cur_id,':report_id',biv_core_pkg.g_report_id);
79 DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',biv_core_pkg.get_session_id);
80 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
81 DBMS_SQL.CLOSE_CURSOR(l_cur_id);
82
83 -- Change for enh 2914005 starts
84 if l_return_num > 0 then
85 l_total := biv_core_pkg.get_lookup_meaning('TOTAL');
86 l_totalurl := 'BIV_SERVICE_REQUEST' || l_url;
87 l_totalurl := l_totalurl || g_p_sp || 'P_PREVR' || g_v_sp;
88 l_totalurl := l_totalurl || biv_core_pkg.g_report_id;
89 l_totalurl := l_totalurl || g_p_sp || 'P_TOTAL' || g_v_sp;
90 l_totalurl := l_totalurl || 'Y';
91 insert into biv_tmp_bin (report_code,session_id,col2,col4,col3) select biv_core_pkg.g_report_id,biv_core_pkg.get_session_id,
92 l_total,decode(sum(col4),null,0,sum(col4)),l_totalurl from biv_tmp_bin;
93 end if;
94 -- Change for enh 2914005 ends
95 EXCEPTION
96 WHEN OTHERS THEN
97 if (g_debug_flag = 'Y') then
98 g_err := 'Err in BIV_DASH_BIN_PKG.load_sr_bin:' ||
99 substr(sqlerrm,1,500);
100 biv_core_pkg.biv_debug(g_err,biv_core_pkg.g_report_id);
101 end if;
102 END load_sr_bin;
103
104 -- Service Request Summary Report - Monitor
105 PROCEDURE load_sr_sum_report(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
106 l_report_code VARCHAR2(30) := 'BIV_RT_SR_SUM_MONITOR';
107 x_where_clause varchar2(1000);
108 l_query VARCHAR2(3000);
109 l_select VARCHAR2(1000);
110 l_table VARCHAR2(500);
111 l_where VARCHAR2(1000);
112 l_cur_id PLS_INTEGER;
113 l_return_num PLS_INTEGER := 0;
114 l_url_param VARCHAR2(50);
115 l_ogrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_OGRP');
116 l_agrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_AGRP');
117 l_mgr_id VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_MGR_ID');
118 l_ttl_recs number;
119 l_ttl_meaning fnd_lookups.meaning % type :=
120 biv_core_pkg.get_lookup_meaning('TOTAL');
121 l_param_str biv_tmp_rt1.col1 % type;
122 BEGIN
123 biv_core_pkg.g_report_id := l_report_code;
124 biv_core_pkg.clean_dcf_table('BIV_TMP_RT1');
125 g_session_id := biv_core_pkg.get_session_id;
126 if (g_debug_flag = 'Y') then
127 biv_core_pkg.biv_debug('Start of Report, Params:'||
128 p_param_str,biv_core_pkg.g_report_id);
129 end if;
130 biv_core_pkg.get_report_parameters(p_param_str);
131
132 -- build extra params
133 l_url_param := g_p_sp;
134 if l_ogrp is not null then
135 l_url_param := l_url_param || 'P_OGRP' || g_v_sp || l_ogrp || g_p_sp;
136 end if;
137 if l_agrp is not null then
138 l_url_param := l_url_param || 'P_AGRP' || g_v_sp || l_agrp || g_p_sp;
139 end if;
140 if l_mgr_id is not null then
141 l_url_param := l_url_param || 'P_MGR_ID' || g_v_sp || l_mgr_id || g_p_sp;
142 end if;
143
144 l_select := 'INSERT INTO biv_tmp_rt1(report_code,session_id,col2, col3, col4, ';
145 l_select := l_select || 'col5, col6, col7, col8,col20) ';
146 l_select := l_select || 'SELECT :l_report_code,:x_session_id,stat.name, ';
147 l_select := l_select || '''BIV_RT_SR_SEV' || g_p_sp || 'jtfBinId';
148 l_select := l_select || g_v_sp || 'BIV_RT_SR_SEV' || g_p_sp;
149 l_select := l_select || 'P_CHNL' || g_v_sp || 'WEB' || g_p_sp;
150 l_select := l_select || 'P_STS_ID' || g_v_sp || '''||';
151 l_select := l_select || 'stat.incident_status_id||'''||l_url_param||''', ';
152 l_select := l_select || 'sum(decode(upper(sr.sr_creation_channel),''WEB'',1,0)), ';
153 l_select := l_select || '''BIV_RT_SR_SEV' || g_p_sp || 'jtfBinId';
154 l_select := l_select || g_v_sp || 'BIV_RT_SR_SEV' || g_p_sp;
155 l_select := l_select || 'P_CHNL' || g_v_sp || 'PHONE' || g_p_sp;
156 l_select := l_select || 'P_STS_ID' || g_v_sp || '''||';
157 l_select := l_select || 'stat.incident_status_id||'''||l_url_param||''', ';
158 l_select := l_select || 'sum(decode(upper(sr.sr_creation_channel),''PHONE'',1,0)), ';
159 l_select := l_select || '''BIV_RT_SR_SEV' || g_p_sp || 'jtfBinId';
160 l_select := l_select || g_v_sp || 'BIV_RT_SR_SEV' || g_p_sp;
161 l_select := l_select || 'P_ESC_SR' || g_v_sp || 'Y' || g_p_sp;
162 l_select := l_select || 'P_STS_ID' || g_v_sp || '''||';
163 l_select := l_select || 'stat.incident_status_id||'''||l_url_param||''', ';
164 l_select := l_select || 'biv_dash_bin_pkg.get_esc_sr_backlog(1,stat.incident_status_id) ,''INDV_ROW''';
165
166
167 l_table := 'FROM cs_incident_statuses_vl stat, cs_incidents_vl_sec sr ';
168
169 biv_core_pkg.get_where_clause(l_table,x_where_clause);
170 -- table and where clause to get escalated SR count.
171 -- this will be used in get_esc_sr_backlog
172 -- Change for Bug 3386946
173 g_esc_frm_lst := ' from cs_incidents_b_sec sr,
174 jtf_task_references_b ref,
175 jtf_tasks_b task ';
176 biv_core_pkg.get_where_clause(g_esc_frm_lst,g_esc_whr_cls);
177 g_esc_whr_cls := g_esc_whr_cls || '
178 and sr.incident_id = ref.object_id
179 and ref.task_id = task.task_id
180 and ref.object_type_code = ''SR''
181 and ref.reference_code = ''ESC''
182 and task.task_type_id = 22
183 and task.escalation_level is not null
184 and sr.incident_status_id = :p_status ';
185 g_esc_sel_stt := 'select count(sr.incident_id)
186 ' || g_esc_frm_lst || g_esc_whr_cls;
187 if (g_debug_flag = 'Y') then
188 biv_core_pkg.biv_debug('statement for escalated SR',
189 biv_core_pkg.g_report_id);
190 biv_core_pkg.biv_debug(g_esc_sel_stt,biv_core_pkg.g_report_id);
191 end if;
192
193 l_where := x_where_clause || ' ' || '
194 and nvl(stat.close_flag,''N'') != ''Y''
195 and stat.incident_subtype = ''INC''
196 and sr.incident_status_id = stat.incident_status_id
197 GROUP BY stat.incident_status_id,stat.name,stat.description
198 ';
199
200 l_query := l_select || ' ' || l_table || ' '|| l_where;
201
202 if g_debug_flag = 'Y' then
203 biv_core_pkg.biv_debug(l_query,l_report_code);
204 commit;
205 end if;
206
207 l_cur_id := DBMS_SQL.OPEN_CURSOR;
208 DBMS_SQL.PARSE(l_cur_id,l_query,DBMS_SQL.NATIVE);
209 biv_core_pkg.bind_all_variables(l_cur_id);
210 DBMS_SQL.BIND_VARIABLE(l_cur_id,':l_report_code',l_report_code);
211 DBMS_SQL.BIND_VARIABLE(l_cur_id,':x_session_id' ,g_session_id);
212 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
213 DBMS_SQL.CLOSE_CURSOR(l_cur_id);
214 --
215 -- Add total Row
216 --
217 select count(*) into l_ttl_recs
218 from biv_tmp_rt1
219 where report_code = l_report_code
220 and session_id = g_session_id;
221 if (g_debug_flag = 'Y') then
222 biv_core_pkg.biv_debug('Total Records:' || to_char(l_ttl_recs),
223 l_report_code);
224 end if;
225 if (l_ttl_recs > 1 /*and l_ttl_recs < biv_core_pkg.g_disp*/) then
226 l_param_str := 'BIV_RT_SR_SEV' || g_p_sp || 'jtfBinId' ||
227 g_v_sp || 'BIV_RT_SR_SEV' || g_p_sp || l_url_param ||
228 'P_BLOG' || g_v_sp || 'Y' || g_p_sp;
229 if (g_debug_flag = 'Y') then
230 biv_core_pkg.biv_debug('going to insert total row',
231 l_report_code);
232 end if;
233 insert into biv_tmp_rt1 (report_code,session_id,
234 col2,col4,col6,col8,col20,
235 col3, col5, col7)
236 select l_report_code, g_session_id, l_ttl_meaning, sum(col4),
237 sum(col6), sum(col8), 'TTL_ROW',
238 l_param_str || 'P_CHNL' || g_v_sp || 'WEB',
239 l_param_str || 'P_CHNL' || g_v_sp || 'PHONE',
240 l_param_str || 'P_ESC_SR' || g_v_sp || 'Y'
241 from biv_tmp_rt1
242 where report_code = l_report_code
243 and session_id = g_session_id;
244 end if;
245 --
246 --
247 if (g_debug_flag = 'Y') then
248 biv_core_pkg.biv_debug('End of Report',l_report_code);
249 end if;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 if (g_debug_flag = 'Y') then
254 g_err := 'Err in BIV_DASH_BIN_PKG.load_sr_bin:' ||
255 substr(sqlerrm,1,500);
256 biv_core_pkg.biv_debug(g_err,l_report_code);
257 end if;
258
259 END load_sr_sum_report;
260
261 -- Service Request Severity Report
262 PROCEDURE load_sr_sev_report(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
263 x_where_clause varchar2(1000);
264 l_query VARCHAR2(3000);
265 l_select VARCHAR2(1000);
266 l_table VARCHAR2(500);
267 l_where VARCHAR2(1000);
268 l_cur_id PLS_INTEGER;
269 l_return_num PLS_INTEGER := 0;
270 l_report_code VARCHAR2(30) := 'BIV_RT_SR_SEV';
271 l_channel VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_CHNL');
272 l_status VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_STS_ID');
273 l_esc VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_ESC_SR');
274 l_url_param VARCHAR2(50);
275 l_ogrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_OGRP');
276 l_agrp VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_AGRP');
277 l_mgr_id VARCHAR2(20) := biv_core_pkg.get_parameter_value(p_param_str,'P_MGR_ID');
278 BEGIN
279 biv_core_pkg.g_report_id := l_report_code;
280 biv_core_pkg.clean_dcf_table('BIV_TMP_RT1');
281 g_session_id := biv_core_pkg.get_session_id;
282 if (g_debug_flag = 'Y') then
283 biv_core_pkg.biv_debug('Start of Report, Params:'||
284 p_param_str,biv_core_pkg.g_report_id);
285 end if;
286 biv_core_pkg.get_report_parameters(p_param_str);
287
288 -- build extra params
289 l_url_param := g_p_sp;
290 if l_ogrp is not null then
291 l_url_param := l_url_param || 'P_OGRP' || g_v_sp || l_ogrp || g_p_sp;
292 end if;
293 if l_agrp is not null then
294 l_url_param := l_url_param || 'P_AGRP' || g_v_sp || l_agrp || g_p_sp;
295 end if;
296 if l_mgr_id is not null then
297 l_url_param := l_url_param || 'P_MGR_ID' || g_v_sp || l_mgr_id || g_p_sp;
298 end if;
299 l_url_param := l_url_param || 'P_BLOG' || g_v_sp || 'Y' || g_p_sp;
300
301 If ( l_esc is not NULL ) then
302 l_select := l_select || 'INSERT INTO biv_tmp_rt1(report_code,session_id,col2,col3,col4,col5,col6) ';
303 l_select := l_select || 'select :l_report_code,:g_session_id,c.NAME, ';
304 l_select := l_select || '''BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp;
305 l_select := l_select || 'BIV_SERVICE_REQUEST';
306 l_select := l_select || g_p_sp||'P_ESC_SR'||g_v_sp||'Y'||g_p_sp||'P_STS_ID'||g_v_sp;
310 l_select := l_select || 'BIV_SERVICE_REQUEST';
307 l_select := l_select || l_status||g_p_sp||'P_SEV'||g_v_sp||'''||c.incident_severity_id||'''||l_url_param||''', ';
308 l_select := l_select || ' count(sr.incident_id),';
309 l_select := l_select || '''BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp;
311 l_select := l_select || g_p_sp||'P_ESC_SR'||g_v_sp||'Y'||g_p_sp||'P_STS_ID'||g_v_sp;
312 l_select := l_select || l_status||g_p_sp||'P_SEV'||g_v_sp||'''||c.incident_severity_id||'''||l_url_param||''', ';
313 l_select := l_select || 'BIV_DASH_BIN_PKG.get_hours(min(sr.incident_date)) ';
314
315 -- Change for Bug 3386946
316 l_table := l_table || ' from cs_incidents_vl_sec sr, ';
317 l_table := l_table || ' cs_incident_statuses_b stat, ';
318 l_table := l_table || ' cs_incident_severities_vl c, ';
319 l_table := l_table || ' jtf_task_references_b r, ';
320 l_table := l_table || ' jtf_tasks_b task ';
321
322 biv_core_pkg.get_where_clause(l_table,x_where_clause);
323
324 l_where := x_where_clause || ' and sr.incident_status_id = stat.incident_status_id ';
325 l_where := l_where || ' and sr.incident_severity_id = c.incident_severity_id ';
326 --already coming from core pkg
327 --l_where := l_where || ' and nvl(b.close_flag,''N'') != ''Y'' ';
328 if (l_status is not null) then
329 l_where := l_where || ' and sr.incident_status_id = '||l_status||' ';
330 end if;
331 l_where := l_where || ' and sr.incident_id = r.object_id ';
332 l_where := l_where || ' and r.task_id = task.task_id ';
333 l_where := l_where || ' and r.object_type_code = ''SR'' ';
334 l_where := l_where || ' and r.reference_code = ''ESC'' ';
335 l_where := l_where || ' and task.task_type_id = 22 ';
336 l_where := l_where || ' group by c.NAME,c.incident_severity_id ';
337 else
338 l_select := l_select || 'INSERT INTO biv_tmp_rt1(report_code,session_id,col2,col3,col4,col5,col6) ';
339 l_select := l_select || 'select :l_report_code,:g_session_id,c.NAME, ';
340 l_select := l_select || '''BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp;
341 l_select := l_select || 'BIV_SERVICE_REQUEST';
342 l_select := l_select || g_p_sp||'P_CHNL'||g_v_sp||l_channel||g_p_sp||'P_STS_ID'||g_v_sp;
343 l_select := l_select || l_status||g_p_sp||'P_SEV'||g_v_sp||'''||c.incident_severity_id||'''||l_url_param||''', ';
344 l_select := l_select || ' count(sr.incident_id), ';
345 l_select := l_select || '''BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp;
346 l_select := l_select || 'BIV_SERVICE_REQUEST';
347 l_select := l_select || g_p_sp||'P_CHNL'||g_v_sp||l_channel||g_p_sp||'P_STS_ID'||g_v_sp;
348 l_select := l_select || l_status||g_p_sp||'P_SEV'||g_v_sp||'''||c.incident_severity_id||'''||l_url_param||''', ';
349 l_select := l_select || 'BIV_DASH_BIN_PKG.get_hours(min(sr.incident_date)) ';
350
351 -- Change for Bug 3386946
352 l_table := l_table || ' from cs_incidents_vl_sec sr, ';
353 -- l_table := l_table || ' cs_incident_statuses_b b, ';
354 l_table := l_table || ' cs_incident_severities_vl c ';
355
356 biv_core_pkg.get_where_clause(l_table,x_where_clause);
357
358 -- l_where := x_where_clause || ' and sr.incident_status_id = b.incident_status_id ';
359 l_where := x_where_clause;
360 l_where := l_where || ' and sr.incident_severity_id = c.incident_severity_id ';
361 /********** 5/13/02 these two are added automatically by get_where_clause
362 call.
363 l_where := l_where || ' and nvl(b.close_flag,''N'') != ''Y'' ';
364 l_where := l_where || ' and UPPER(sr.sr_creation_channel) = UPPER('''||l_channel||''') ';
365 l_where := l_where || ' and sr.incident_status_id = '||l_status||' ';
366 *********************************************************/
367 l_where := l_where || ' group by c.NAME,c.incident_severity_id ';
368 end if;
369
370 l_query := l_select || ' ' || l_table || ' '|| l_where;
371
372 if g_debug_flag = 'Y' then
373 biv_core_pkg.biv_debug(l_query,l_report_code);
374 commit;
375 end if;
376
377 l_cur_id := DBMS_SQL.OPEN_CURSOR;
378 DBMS_SQL.PARSE(l_cur_id,l_query,DBMS_SQL.NATIVE);
379 biv_core_pkg.bind_all_variables(l_cur_id);
380 DBMS_SQL.BIND_VARIABLE(l_cur_id,':l_report_code',l_report_code);
381 DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_session_id',g_session_id);
382 l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
383 DBMS_SQL.CLOSE_CURSOR(l_cur_id);
384 if (g_debug_flag = 'Y') then
385 biv_core_pkg.biv_debug('End of Report, Params:',
386 biv_core_pkg.g_report_id);
387 end if;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 if (g_debug_flag = 'Y') then
392 g_err := 'Err in BIV_DASH_BIN_PKG.load_srsev_report:' ||
393 substr(sqlerrm,1,500);
394 biv_core_pkg.biv_debug(g_err,l_report_code);
395 end if;
396
397 END load_sr_sev_report;
398
399 -- Get the Service Severity Report Label
400 FUNCTION get_sr_sev_report_name(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) RETURN VARCHAR2 IS
401 l_report_name VARCHAR2(100);
402 l_tmp VARCHAR2(100);
403 l_report_code VARCHAR2(30) := 'BIV_DASH_SR_SEV_REPORT';
404 l_channel VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_CHNL');
405 l_status VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_STS_ID');
409 select attribute_label_long into l_report_name
406 l_esc VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_ESC_LVL');
407 BEGIN
408 -- get the first word in the report name
410 from ak_attributes_vl
411 where attribute_code = 'P_SR_SEV_RPT_1';
412
413 -- getting the second word
414 if l_channel = 'WEB' then
415 select attribute_label_long into l_tmp
416 from ak_attributes_vl
417 where attribute_code = 'P_SR_SEV_RPT_3';
418 elsif l_channel = 'PHONE' then
419 select attribute_label_long into l_tmp
420 from ak_attributes_vl
421 where attribute_code = 'P_SR_SEV_RPT_4';
422 else
423 select attribute_label_long into l_tmp
424 from ak_attributes_vl
425 where attribute_code = 'P_SR_SEV_RPT_5';
426 end if;
427 l_report_name := l_report_name || ' ' || l_tmp;
428
429 -- get the third part
430 select attribute_label_long into l_tmp
431 from ak_attributes_vl
432 where attribute_code = 'P_SR_SEV_RPT_2';
433 l_report_name := l_report_name || ' ' || l_tmp;
434
435 -- get the last part
436 SELECT name INTO l_tmp
437 FROM cs_incident_statuses_vl
438 WHERE incident_status_id = to_number(l_status);
439
440 l_report_name := l_report_name || ' : ' || l_tmp;
441
442 return l_report_name;
443 END get_sr_sev_report_name;
444
445 -- Get the Service Severity Report Label
446 FUNCTION get_sr_sev_column_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) RETURN VARCHAR2 IS
447 l_label VARCHAR2(100);
448 l_tmp VARCHAR2(100);
449 l_report_code VARCHAR2(30) := 'BIV_DASH_SR_SEV_REPORT';
450 l_channel VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_CHNL');
451 l_status VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_STS_ID');
452 l_esc VARCHAR2(30) := biv_core_pkg.get_parameter_value(p_param_str,'P_ESC_LVL');
453 BEGIN
454 -- get the first word in the report name
455 select attribute_label_long into l_label
456 from ak_attributes_vl
457 where attribute_code = 'P_SR_SEV_RPT_6';
458
459 -- getting the second word
460 if l_channel = 'WEB' then
461 select attribute_label_long into l_tmp
462 from ak_attributes_vl
463 where attribute_code = 'P_SR_SEV_RPT_3';
464 elsif l_channel = 'PHONE' then
465 select attribute_label_long into l_tmp
466 from ak_attributes_vl
467 where attribute_code = 'P_SR_SEV_RPT_4';
468 else
469 select attribute_label_long into l_tmp
470 from ak_attributes_vl
471 where attribute_code = 'P_SR_SEV_RPT_5';
472 end if;
473 l_label := l_label || ' ' || l_tmp;
474
475 -- get the third part
476 select attribute_label_long into l_tmp
477 from ak_attributes_vl
478 where attribute_code = 'P_SR_SEV_RPT_7';
479 l_label := l_label || ' ' || l_tmp;
480
481 -- get the fourth part
482 SELECT name INTO l_tmp
483 FROM cs_incident_statuses_vl
484 WHERE incident_status_id = to_number(l_status);
485
486 l_label := l_label || ' ' || l_tmp;
487
488 -- get the last part
489 select attribute_label_long into l_tmp
490 from ak_attributes_vl
491 where attribute_code = 'P_SR_SEV_RPT_8';
492 l_label := l_label || ' ' || l_tmp;
493
494 return l_label;
495 END get_sr_sev_column_label;
496
497 -----------------------------------------------------
498 function get_esc_sr_backlog(p_owner number, p_status number) return number is
499 x_val number;
500 l_cur number;
501 l_dummy number;
502 begin
503 /*
504 select count(ina.incident_id) into x_val
505 FROM
506 cs_incidents_all_b ina,
507 jtf_task_references_b r, jtf_tasks_b t
508 WHERE ina.incident_id = r.object_id
509 and r.task_id = t.task_id
510 and r.object_type_code = 'SR'
511 and r.reference_code = 'ESC'
512 and t.task_type_id = 22
513 and t.escalation_level is not null
514 and ina.incident_status_id = p_status;
515 */
516 l_cur := dbms_sql.open_cursor;
517 dbms_sql.parse(l_cur,g_esc_sel_stt,dbms_sql.native);
518 biv_core_pkg.bind_all_variables(l_cur);
519 dbms_sql.bind_variable(l_cur,':p_status', p_status);
520 dbms_sql.define_column(l_cur,1,x_val);
521 l_dummy := dbms_sql.execute(l_cur);
522 IF dbms_sql.fetch_rows(l_cur) > 0 then
523 dbms_sql.column_value(l_cur, 1, x_val);
524 else x_val := 0;
525 dbms_sql.close_cursor(l_cur);
526 end if;
527 return(x_val);
528 exception
529 when others then
530 return 0;
531 end get_esc_sr_backlog;
532
533 ------------------------------
534 -- return Y if a SR is escalated
535 -- return N if not
536 ------------------------------
537 function check_esc (p_sr_id number) return varchar2 is
538 x_esc_flag VARCHAR2(1);
539 begin
540
541 select decode(count(*),0,'N','Y') into x_esc_flag
542 from jtf_task_references_b r,jtf_tasks_b t
543 where r.OBJECT_ID = p_sr_id
544 and r.object_type_code = 'SR'
545 and r.reference_code = 'ESC'
546 and r.task_id = t.task_id
547 and t.task_type_id = 22;
548
549 return x_esc_flag;
550 end check_esc;
551
552 ------------------------------
553 -- convert number of days to HH:MM:SS
554 ------------------------------
555 function get_hours (p_day Date) return varchar2 is
556 x_date_str VARCHAR2(100);
557 l_total_hours NUMBER;
558 l_tmp NUMBER;
559 begin
560 -- convert number of days to hours
561 l_total_hours := (sysdate-p_day)*24;
562
563 x_date_str := trunc(l_total_hours) || ':';
564
565 -- get mins
566 l_tmp := (l_total_hours - trunc(l_total_hours)) * 60;
567 if (trunc(l_tmp) < 10) then
568 x_date_str := x_date_str || '0' || trunc(l_tmp) || ':';
569 else
570 x_date_str := x_date_str || trunc(l_tmp) || ':';
571 end if;
572
573 -- get sec
574 l_tmp := (l_tmp - trunc(l_tmp)) * 60;
575
576 if (trunc(l_tmp) < 10) then
577 x_date_str := x_date_str || '0' || trunc(l_tmp);
578 else
579 x_date_str := x_date_str || trunc(l_tmp);
580 end if;
581
582 return x_date_str;
583 end get_hours;
584 END;