[Home] [Help]
PACKAGE BODY: APPS.BIV_HS_ESC_ACTVTY_PKG
Source
1 package body biv_hs_esc_actvty_pkg as
2 /* $Header: bivhactb.pls 115.23 2004/01/23 04:54:52 vganeshk ship $ */
3 -------------------------------------------
4 procedure sr_escalation(p_param_str varchar2) as
5 l_cur number;
6 l_from_list varchar2(1000);
7 l_where_clause varchar2(2000);
8 l_sql_sttmnt varchar2(5000);
9 l_dummy number;
10 l_start_date date;
11 l_end_date date;
12 l_time_frame varchar2(80);
13 l_order_by varchar2(60);
14 l_desc_asc varchar2(20);
15 l_param_col varchar2(20);
16 l_new_param_str varchar2(2000);
17 l_new_param_str1 varchar2(2000);
18 l_gt_param_str varchar2(2000);
19 l_bt_param_str varchar2(2000);
20 l_session_id biv_tmp_hs2.session_id % type;
21 l_err varchar2(1000);
22 l_loc varchar2( 100);
23 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
24 begin
25 biv_core_pkg.g_report_id := 'BIV_HS_SR_ESCALATION';
26 l_session_id := biv_core_pkg.get_session_id;
27 biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
28 biv_core_pkg.g_srl_no := 1;
29 biv_core_pkg.get_report_parameters(p_param_str);
30 if (l_debug = 'Y') then
31 biv_core_pkg.biv_debug('Param :'||p_param_str,'BIV_HS_SR_ESCALATION');
32 end if;
33
34 -- Change for Bug 3386946
35 l_from_list := ' from cs_incidents_b_sec sr, biv_sr_summary srs /*,
36 cs_incident_statuses_b stat*/ ';
37
38 biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
39
40 if (biv_core_pkg.g_srt_by = '1') then
41 l_order_by := 'col2 ';
42 l_desc_asc := ' asc ';
43 l_param_col := ' col1';
44 elsif (biv_core_pkg.g_srt_by = '2') then
45 l_order_by := 'col4 ';
46 l_desc_asc := ' desc ';
47 l_param_col := ' col4';
48 elsif (biv_core_pkg.g_srt_by = '3') then
49 l_order_by := 'col6 ';
50 l_desc_asc := ' desc ';
51 l_param_col := ' col4';
52 end if;
53
54 l_where_clause := l_where_clause || '
55 and sr.incident_id = srs.incident_id
56 --and sr.incident_status_id = stat.incident_status_id
57 --and nvl(stat.close_flag,''N'') <> ''Y''
58 and srs.escalation_level is not null';
59 l_sql_sttmnt := '
60 select ' || biv_core_pkg.g_base_column || ' col1,
61 srs.escalation_level col4,
62 count(distinct sr.incident_id) col6
63 ' || l_from_list || '
64 ' || l_where_clause || '
65 group by ' || biv_core_pkg.g_base_column || ',srs.escalation_level
66 ' ;
67
68 l_sql_sttmnt := 'insert into biv_tmp_hs2(report_code, rowno,
69 col1,col4,col6,session_id)
70 select ''SR_ESC'', rownum, col1, col4, col6, :session_id from (
71 ' || l_sql_sttmnt || ')
72 ';
73
74 if (l_debug = 'Y') then
75 biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_HS_SR_ESCALATION');
76 commit;
77 end if;
78 l_cur := dbms_sql.open_cursor;
79 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
80 biv_core_pkg.bind_all_variables(l_cur);
81 dbms_sql.bind_variable(l_cur,':session_id', l_session_id);
82 l_loc := 'Before Second Execute';
83 l_dummy := dbms_sql.execute(l_cur);
84
85 biv_core_pkg.update_base_col_desc('biv_tmp_hs2');
86 -- Here rownum*2 is select so that there are gaps and in those gaps
87 -- we could fit total for groups
88 l_sql_sttmnt := 'insert into biv_tmp_hs2(report_code, rowno,
89 col1,col2, col4,col6,session_id)
90 select ''BIV_HS_SR_ESCALATION'', rownum * 2, col1, col2, col4,
91 col6 ,session_id
92 from (
93 select col1, col2, col4, col6,session_id
94 from biv_tmp_hs2
95 where report_code = ''SR_ESC''
96 and session_id = :session_id
97 order by ' || l_order_by || ' ,nvl(col2,'' ''))
98 where rownum <= :rows_to_display '; -- || biv_core_pkg.g_disp ;
99 l_loc := 'Before third execute';
100 if (l_debug = 'Y') then
101 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
102 end if;
103 execute immediate l_sql_sttmnt using l_session_id, biv_core_pkg.g_disp;
104 --, to_number(biv_core_pkg.g_disp);
105
106 l_gt_param_str := 'BIV_HS_ESCALATION_VIEW' ||biv_core_pkg.g_param_sep ||
107 biv_core_pkg.reconstruct_param_str || 'jtfBinId' ||
108 biv_core_pkg.g_value_sep || 'BIV_HS_ESCALATION_VIEW' --||
109 -- biv_core_pkg.g_param_sep || 'P_BLOG' ||
110 -- biv_core_pkg.g_value_sep || 'Y'
111 ;
112 biv_core_pkg.reset_view_by_param;
113 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
114 biv_core_pkg.reconstruct_param_str;
115 -- was used for col3 value. This URL is disbaled for time being 4/27/02
116 l_new_param_str := l_new_param_str || 'jtfBinId' ||
117 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118 biv_core_pkg.g_param_sep ||
119 biv_core_pkg.param_for_base_col ||
120 biv_core_pkg.g_value_sep ;
121
122 l_new_param_str1 := 'BIV_HS_ESCALATION_VIEW' ||biv_core_pkg.g_param_sep ||
123 biv_core_pkg.reconstruct_param_str;
124 l_new_param_str1 := l_new_param_str1 || 'jtfBinId' ||
125 biv_core_pkg.g_value_sep || 'BIV_HS_ESCALATION_VIEW' ||
126 -- biv_core_pkg.g_param_sep || 'P_BLOG' ||
127 -- biv_core_pkg.g_value_sep || 'Y' ||
128 biv_core_pkg.g_param_sep ||
129 biv_core_pkg.param_for_base_col ||
130 biv_core_pkg.g_value_sep ;
131
132 delete from biv_tmp_hs2
133 where report_code = 'SR_ESCaa'
134 and session_id = l_session_id;
135 l_loc := 'Before update of odd col';
136 update biv_tmp_hs2
137 set /*col5 = l_new_param_str || col1 || biv_core_pkg.g_param_sep ||
138 'P_ESC_LVL' || biv_core_pkg.g_value_sep || col4,*/
139 -- this was column col3
140 col5 = l_new_param_str1 || nvl(col1,biv_core_pkg.g_null) ||
141 biv_core_pkg.g_param_sep ||
142 'P_ESC_LVL' || biv_core_pkg.g_value_sep || col4,
143 creation_date = sysdate
144 where session_id = l_session_id
145 and report_code = 'BIV_HS_SR_ESCALATION' ;
146
147 --
148 --
149 -- this will insert total for the group
150 --
151 --
152 if (l_order_by <> 'col6 ') then
153 l_new_param_str1 := 'BIV_HS_ESCALATION_VIEW' ||biv_core_pkg.g_param_sep ||
154 biv_core_pkg.reconstruct_param_str --||
155 -- 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
156 --biv_core_pkg.g_param_sep
157 ;
158 l_new_param_str1 := l_new_param_str1 || 'jtfBinId' ||
159 biv_core_pkg.g_value_sep || 'BIV_HS_ESCALATION_VIEW' ||
160 biv_core_pkg.g_param_sep ;
161 if (l_order_by = 'col2 ') then
162 l_new_param_str1 := l_new_param_str1 ||
163 biv_core_pkg.param_for_base_col ;
164 else
165 l_new_param_str1 := l_new_param_str1 ||
166 'P_ESC_LVL';
167 end if;
168 l_new_param_str1 := l_new_param_str1 || biv_core_pkg.g_value_sep ;
169 l_sql_sttmnt := '
170 insert into biv_tmp_hs2 (report_code, rowno, col4, col6,session_id,col5,col9,
171 creation_date)
172 select ''BIV_HS_SR_ESCALATION'', max(rowno)+1,
173 ''Total '' || nvl(' || l_order_by ||','' ''), sum(col6), :session_id,
174 :l_new_param_str1 || nvl('||l_param_col ||',''' ||
175 biv_core_pkg.g_null ||'''),''Total'', sysdate
176 from biv_tmp_hs2
177 where report_code = ''BIV_HS_SR_ESCALATION''
178 and session_id = :session_id
179 group by nvl(' ||l_order_by ||','' '')' ||
180 ', nvl('|| l_param_col || ','''||biv_core_pkg.g_null ||
181 ''')'
182 ;
183 if (l_debug = 'Y') then
184 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
185 end if;
186 execute immediate l_sql_sttmnt using l_session_id,l_new_param_str1,
187 l_session_id;
188 end if;
189
190 -- this will insert grand total
191 insert into biv_tmp_hs2 (report_code, rowno, col4, col6,session_id,col5,
192 creation_date)
193 select 'BIV_HS_SR_ESCALATION', max(rowno)+1,
194 'Grand Total ', sum(col6), l_session_id,
195 l_gt_param_str, sysdate
196 from biv_tmp_hs2
197 where report_code = 'BIV_HS_SR_ESCALATION'
198 and session_id = l_session_id
199 and (l_order_by = 'col6 ' or col9 = 'Total')
200 --and col1 is not null
201 ;
202 if (l_debug = 'Y') then
203 biv_core_pkg.biv_debug('End of Report', biv_core_pkg.g_report_id);
204 end if;
205 biv_core_pkg.g_report_id := 'NULL';
206 commit;
207 exception
208 when others then
209 if (l_debug = 'Y') then
210 l_err := 'Err at ' || l_loc|| ':'|| substr(sqlerrm,1,500);
211 biv_core_pkg.biv_debug(l_err,'BIV_HS_SR_ESCALATION');
212 end if;
213 end;
214 -------------------------------------------------
215 procedure escalation_view(p_param_str varchar2) as
216 l_cur number;
217 l_from_list varchar2(1000);
218 l_where_clause varchar2(2000);
219 l_sql_sttmnt varchar2(5000);
220 l_dummy number;
221 l_start_date date;
222 l_end_date date;
223 l_time_frame varchar2(80);
224 l_session_id biv_tmp_hs2.session_id % type;
225 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
226 begin
227 biv_core_pkg.g_report_id := 'BIV_HS_ESCALATION_VIEW';
228 l_session_id := biv_core_pkg.get_session_id;
229 biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
230 biv_core_pkg.g_srl_no := 1;
231 if (l_debug = 'Y') then
232 biv_core_pkg.biv_debug('Param Passed to Proc:'||p_param_str,
233 'BIV_HS_ESCALATION_VIEW');
234 end if;
235
236 biv_core_pkg.get_report_parameters(p_param_str);
237
238 -- Change for Bug 3386946
239 l_from_list := ' from cs_incidents_b_sec sr,
240 biv_sr_summary srs';
241
242 biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
243 l_where_clause := l_where_clause || '
244 and sr.incident_id = srs.incident_id
245 and srs.escalation_level is not null';
246 l_sql_sttmnt := '
247 select sr.customer_id col1, srs.escalation_level col4,
248 sr.incident_id col5,
249 sr.incident_number col6, sr.incident_owner_id col7,
250 srs.esc_owner_id col9, sr.inventory_item_id col11,
251 null col14, sr.platform_id col16' || l_from_list ||
252 l_where_clause ; --|| '
253 --group by ' || biv_core_pkg.g_base_column;
254
255
256 l_sql_sttmnt := '
257 insert into biv_tmp_hs2 (report_code,
258 col1,col4, col5, col6, col7, col9, col11, col14, col16,
259 session_id)
260 select ''TEMP'', col1,col4,col5,col6, col7, col9, col11,
261 col14, col16, :x_session_id
262 from (' || l_sql_sttmnt || ')'
263 ;
264
265 if (l_debug = 'Y') then
266 biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_HS_ESCALATION_VIEW');
267 commit;
268 end if;
269 l_cur := dbms_sql.open_cursor;
270 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
271 biv_core_pkg.bind_all_variables(l_cur);
272 dbms_sql.bind_variable(l_cur,':x_session_id', l_session_id);
273 l_dummy := dbms_sql.execute(l_cur);
274
275 --biv_core_pkg.update_base_col_desc('biv_tmp_hs2');
276 biv_core_pkg.update_description('P_AGENT_ID','col7' ,'col8' ,'biv_tmp_hs2');
277 biv_core_pkg.update_description('P_AGENT_ID','col9' ,'col10','biv_tmp_hs2');
278 biv_core_pkg.update_description('P_PRD_ID' ,'col11','col12','biv_tmp_hs2');
279 biv_core_pkg.update_description('P_CUST_ID' ,'col1' ,'col2' ,'biv_tmp_hs2');
280 insert into biv_tmp_hs2 (report_code, rowno,
281 col1, col2, col4, col5, col6, col7, col8, col9, col10,
282 col11, col12, col14, col16,session_id)
283 select * from (
284 select 'BIV_HS_ESCALATION_VIEW' report_code, rownum rowno,
285 col1, col2, col4, col5, col6, col7, col8, col9, col10,
286 col11, col12, col14, col16,session_id
287 from biv_tmp_hs2
288 where report_code = 'TEMP'
289 and session_id = l_session_id
290 order by col2)
291 ;
292 delete from biv_tmp_hs2 where report_code = 'TEMP'
293 and session_id = l_session_id;
294
295 update biv_tmp_hs2
296 set col5 = 'X' || biv_core_pkg.g_param_sep || 'SR_ID=' || col5,
297 --set col5 = 'X' || biv_core_pkg.g_param_sep || 'SrCreate_SrID=' || col5,
298 creation_date = sysdate
299 where report_code = 'BIV_HS_ESCALATION_VIEW'
300 and session_id = l_session_id;
301
302 if (l_debug = 'Y') then
303 biv_core_pkg.biv_debug('End of Report', 'BIV_HS_ESCALATION_VIEW');
304 end if;
305 biv_core_pkg.g_report_id := 'NULL';
306 exception
307 when others then
308 if (l_debug = 'Y') then
309 biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
310 end if;
311 end;
312 procedure sr_activity(p_param_str varchar2) as
313 l_cur number;
314 l_from_list varchar2(1000);
315 l_where_clause varchar2(2000);
316 l_sql_sttmnt varchar2(5000);
317 l_dummy number;
318 l_start_date date;
319 l_end_date date;
320 l_time_frame varchar2(80);
321 l_order_by varchar2(60);
322 l_session_id biv_tmp_hs2.session_id % type;
323 l_err varchar2(1000);
324 l_new_param_str varchar2(2000);
325 l_new_param_str1 varchar2(2000);
326 l_new_view_by varchar2(30);
327 l_loc varchar2(100);
328 l_dt_fmt varchar2(20);
329 l_drilldown_rep varchar2(30);
330 l_ttl_recs number;
331 l_ttl_meaning fnd_lookups.meaning%type :=
332 biv_core_pkg.get_lookup_meaning('TOTAL');
333 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
334 begin
335 l_dt_fmt := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
336 biv_core_pkg.g_report_id := 'BIV_HS_SR_ACTIVITY';
337 l_session_id := biv_core_pkg.get_session_id;
338 biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
339 biv_core_pkg.g_srl_no := 1;
340 if (l_debug = 'Y') then
341 biv_core_pkg.biv_debug('Prameters Are:'||p_param_str,'BIV_HS_SR_ACTIVITY');
342 end if;
343 biv_core_pkg.get_report_parameters(p_param_str);
344
345 l_time_frame := biv_core_pkg.g_time_frame;
346 l_start_date := nvl(biv_core_pkg.g_st_date,trunc(sysdate)-30);
347 l_end_date := trunc(nvl(biv_core_pkg.g_end_date,sysdate))+1;
348 -------------------------
349 if (l_debug = 'Y') then
350 biv_core_pkg.biv_debug('Start Date:'||
351 to_char(l_start_date,'dd-mon-yyyy hh24:mi:ss'),
352 biv_core_pkg.g_report_id);
353 biv_core_pkg.biv_debug('End Date:'||
354 to_char(l_end_date,'dd-mon-yyyy hh24:mi:ss'),
355 biv_core_pkg.g_report_id);
356 end if;
357 -------------------------
358 biv_core_pkg.g_time_frame := null;
359 biv_core_pkg.g_st_date := null;
360 biv_core_pkg.g_end_date := null;
361
362 -- Change for Bug 3386946
363 l_from_list := ' from cs_incidents_vl_sec sr,
364 biv_sr_summary srs,
365 cs_incident_statuses_b stat
366 ';
367
368 biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
369 l_where_clause := l_where_clause || '
370 and sr.incident_id = srs.incident_id
371 and sr.incident_status_id = stat.incident_status_id
372 and (sr.close_date is null or
373 nvl(stat.close_flag,''N'') <> ''Y'' or
374 --sr.close_date between :y_start_date8
375 -- and :y_end_date8 or
376 sr.close_date >= :y_start_date8 or --Change for Bug 3188504
377 srs.reclose_date between :y_start_date9
378 and :y_end_date9 or
379 srs.reopen_date between :y_start_date10
380 and :y_end_date10 or
381 sr.incident_date between :y_start_date11
382 and :y_end_date11)
383 ';
384
385 /*
386 sum(decode(sign(sr.incident_date-:y_end_date5),
387 -1,decode(sign(nvl(sr.close_date,sysdate+1000)-
388 :y_end_date6),-1,0,1)
389 )) col14, --ending_blog
390 */
391 /* 5/8/02 new def of open blog
392 this will not work because SR may have been close in the input
393 period. so at the beginning of period, it was a backlog
394 sum(decode(sign(sr.incident_date-:y_start_date1),
395 -1,decode(nvl(stat.close_flag,''N''),''Y'',0,1)
396 )) col4, --open_blog
397 */
398 l_sql_sttmnt := '
399 select ' || biv_core_pkg.g_base_column ||' col1,
400 sum(decode(sign(sr.incident_date-:y_start_date1),
401 -1,decode(nvl(close_flag,''N''),''N'',1,
402 decode(sign(nvl(sr.close_date,sysdate-1000)-
403 :y_start_date2),-1,0,1)
404 ),
405 0)) col4, --open_blog
406 sum(decode(sign(sr.incident_date-:y_start_date3),
407 -1,0,decode(sign(sr.incident_date-:y_end_date1),-1,1,0))
408 ) col6, --new_sr
409 sum(decode(sign(nvl(sr.close_date,sysdate+1000)-:y_start_date4),
410 -1,0,decode(sign(nvl(sr.close_date,sysdate+1000)-
411 :y_end_date2),-1,1,0))
412 ) col8, --closed_sr
413 sum(decode(sign(srs.reopen_date-:y_start_date5),
414 -1,0,decode(sign(srs.reopen_date-:y_end_date3),-1,1,0))
415 ) col10, --reopened_sr
416 sum(decode(sign(srs.reclose_date-:y_start_date6),
417 -1,0,decode(sign(srs.reclose_date-:y_end_date4),-1,1,0))
418 ) col12, --reclosed_sr
419 /* 5/9/2 this is causing problem with null close_date
420 sum(decode(sign(sr.incident_date-:y_end_date5),
421 -1,decode(nvl(stat.close_flag,''N''), ''Y'',0,1)
422 )) col14,
423 */
424 sum(decode(sign(sr.incident_date-:y_end_date5),
425 -1,decode(nvl(close_flag,''N''),''N'',1,
426 decode(sign(nvl(sr.close_date,sysdate-1000)-
427 :y_end_date6)-1,0,1)
428 ),
429 0)
430 ) col14,
431 avg(srs.days_to_close) col16, --time_to_close
432 sum(decode(sign(sr.incident_date-:y_start_date7),
433 -1,0,decode(sign(sr.incident_date-:y_end_date7),1,0,
434 decode(sr.sr_creation_channel,''WEB'',1,0))
435 )
436 ) col18, --new_web_sr
437 0 col20, --updated_via_web
438 avg(decode(sr.sr_creation_channel,''WEB'',srs.response_time,null)) col22, --resp_time'; --Bug 2960243
439 if ( nvl(biv_core_pkg.g_view_by,'AGENT') = 'AGENT' or
440 nvl(biv_core_pkg.g_view_by,'AGENT') = 'PRD' ) then
441 l_sql_sttmnt := l_sql_sttmnt || '
442 to_char(avg(decode(nvl(stat.close_flag,''N''), ''Y'', null,
443 sysdate - sr.incident_date
444 )
445 ),''999,999.00'') col24
446 ';
447 else
448 l_sql_sttmnt := l_sql_sttmnt || '
449 count(distinct sr.incident_owner_id) col24
450 ';
451 end if;
452
453 /************************
454 decode(closed_sr,0,0,
455 to_char(closed_sr/no_of_agents,''999,999.00''))
456 ***************/
457 if (biv_core_pkg.g_srt_by = '1') then
458 l_order_by := 'col2 asc';
459 else
460 l_order_by := 'to_number(col' || to_number(biv_core_pkg.g_srt_by)*2 ||
461 ') desc ';
462 end if;
463 l_sql_sttmnt := '
464 insert into biv_tmp_hs2(report_code,rowno,
465 col1,col4,col6,col8,col10,col12,
466 col14, col16,col18,col20, col22, col24,
467 session_id)
468 select ''SR_ACT'', rownum, col1,
469 col4, col6, col8,
470 col10, col12, col14,
471 to_char(col16,''999,999.99''),
472 col18, col20,
473 to_char(col22*24,''999,999,999.00''),
474 col24, :session_id
475 from (' || l_sql_sttmnt || l_from_list || l_where_clause ||
476 ' group by ' || biv_core_pkg.g_base_column || ')'
477 /*order by ' || l_order_by || ')
478 where rownum <= ' || biv_core_pkg.g_disp */ ;
479 if (l_debug = 'Y') then
480 biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_HS_SR_ACTIVITY');
481 commit;
482 end if;
483 l_cur := dbms_sql.open_cursor;
484 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
485 biv_core_pkg.bind_all_variables(l_cur);
486 l_loc := 'Before bind variables';
487 dbms_sql.bind_variable(l_cur,':y_start_date1' , l_start_date);
488 dbms_sql.bind_variable(l_cur,':y_start_date2' , l_start_date);
489 dbms_sql.bind_variable(l_cur,':y_start_date3' , l_start_date);
490 dbms_sql.bind_variable(l_cur,':y_start_date4' , l_start_date);
491 dbms_sql.bind_variable(l_cur,':y_start_date5' , l_start_date);
492 dbms_sql.bind_variable(l_cur,':y_start_date6' , l_start_date);
493 dbms_sql.bind_variable(l_cur,':y_start_date7' , l_start_date);
494 dbms_sql.bind_variable(l_cur,':y_start_date8' , l_start_date);
495 dbms_sql.bind_variable(l_cur,':y_start_date9' , l_start_date);
496 dbms_sql.bind_variable(l_cur,':y_start_date10', l_start_date);
497 dbms_sql.bind_variable(l_cur,':y_start_date11', l_start_date);
498 dbms_sql.bind_variable(l_cur,':y_end_date1' , l_end_date );
499 dbms_sql.bind_variable(l_cur,':y_end_date2' , l_end_date );
500 dbms_sql.bind_variable(l_cur,':y_end_date3' , l_end_date );
501 dbms_sql.bind_variable(l_cur,':y_end_date4' , l_end_date );
502 dbms_sql.bind_variable(l_cur,':y_end_date5' , l_end_date );
503 dbms_sql.bind_variable(l_cur,':y_end_date6' , l_end_date );
504 dbms_sql.bind_variable(l_cur,':y_end_date7' , l_end_date );
505 -- dbms_sql.bind_variable(l_cur,':y_end_date8' , l_end_date );
506 dbms_sql.bind_variable(l_cur,':y_end_date9' , l_end_date );
507 dbms_sql.bind_variable(l_cur,':y_end_date10' , l_end_date );
508 dbms_sql.bind_variable(l_cur,':y_end_date11' , l_end_date );
509 dbms_sql.bind_variable(l_cur,':session_id' , l_session_id);
510 l_loc := 'Before first Execute';
511 l_dummy := dbms_sql.execute(l_cur);
512 l_loc := 'After first Execute';
513 biv_core_pkg.update_base_col_desc('biv_tmp_hs2');
514 l_sql_sttmnt := '
515 insert into biv_tmp_hs2(report_code,rowno,
516 col1,col2,col4,col6,col8,col10,col12,
517 col14, col16,col18,col20, col22, col24,
518 session_id)
519 select ''BIV_HS_SR_ACTIVITY'', rownum, col1,
520 col2,col4, col6, col8,
521 col10, col12, col14,
522 col16, col18, col20, col22, col24,session_id
523 from ( select col1,col2, col4, col6, col8, col10, col12,
524 col14, col16, col18, col20, col22, col24, session_id
525 from biv_tmp_hs2
526 where report_code = ''SR_ACT''
527 and session_id = :session_id
528 order by ' || l_order_by || ')' ;
529 if (biv_core_pkg.g_view_by <> 'AGENT' and
530 biv_core_pkg.g_view_by <> 'PRD') then
531 l_sql_sttmnt := l_sql_sttmnt || '
532 where rownum <= :rows_to_display '; -- || biv_core_pkg.g_disp;
533 execute immediate l_sql_sttmnt using l_session_id, biv_core_pkg.g_disp ;
534 else
535 execute immediate l_sql_sttmnt using l_session_id ;
536 end if;
537 l_loc := 'After second Execute, execute immediate';
538
539 if (biv_core_pkg.g_view_by = 'AGENT') then
540 l_new_view_by := 'PRD';
541 -- because agent id is available, so no need for any mgr_id if present.
542 -- 1/7/03biv_core_pkg.g_mgr_id_cnt := 0;
543 -- will be needed for total row.
544 l_drilldown_rep := 'BIV_HS_SR_ACTIVITY_PRD';
545 else
546 l_drilldown_rep := 'BIV_HS_SR_ACTIVITY';
547 l_new_view_by := 'AGENT';
548 end if;
549 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
550 biv_core_pkg.reconstruct_param_str;
551 l_new_param_str1:= l_drilldown_rep ||biv_core_pkg.g_param_sep ||
552 biv_core_pkg.reconstruct_param_str;
553 --Change for bug 3188504 appended P_PREVR parameter to the urls
554 l_new_param_str := l_new_param_str || 'jtfBinId' ||
555 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
556 biv_core_pkg.g_param_sep ||
557 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_HS_SR_ACTIVITY' ||
558 biv_core_pkg.g_param_sep ||
559 biv_core_pkg.param_for_base_col ||
560 biv_core_pkg.g_value_sep ;
561 l_new_param_str1 := l_new_param_str1 || 'jtfBinId' ||
562 biv_core_pkg.g_value_sep || l_drilldown_rep ||
563 biv_core_pkg.g_param_sep ||
564 biv_core_pkg.param_for_base_col ||
565 biv_core_pkg.g_value_sep ;
566
567 l_loc := 'Before update of odd cols for drill down';
568 update biv_tmp_hs2
569 set id = col1,
570 col1 = l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
571 biv_core_pkg.g_param_sep || 'P_VIEW_BY' ||
572 biv_core_pkg.g_value_sep || l_new_view_by ||
573 biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
574 biv_core_pkg.g_value_sep ||
575 to_char(l_start_date,l_dt_fmt) ||
576 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
577 biv_core_pkg.g_value_sep ||
578 to_char(l_end_date-1,l_dt_fmt),
579 col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
580 biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
581 biv_core_pkg.g_value_sep ||
582 to_char(l_start_date,l_dt_fmt) ||
583 biv_core_pkg.g_param_sep ||
584 'P_OBLOG' || biv_core_pkg.g_value_sep || 'Y',
585 col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
586 biv_core_pkg.g_param_sep ||'P_CR_ST' ||
587 biv_core_pkg.g_value_sep ||
588 to_char(l_start_date,l_dt_fmt) ||
589 biv_core_pkg.g_param_sep ||'P_CR_END' ||
590 biv_core_pkg.g_value_sep ||
591 -- Change for Bug 3285048 l_end_date changed to l_end_date-1
592 to_char(l_end_date-1,l_dt_fmt),
593 col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
594 biv_core_pkg.g_param_sep ||'P_CL_ST' ||
595 biv_core_pkg.g_value_sep ||
596 to_char(l_start_date,l_dt_fmt) ||
597 biv_core_pkg.g_param_sep ||'P_CL_END' ||
598 biv_core_pkg.g_value_sep ||
599 to_char(l_end_date-1,l_dt_fmt) ,
600 col9 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
601 biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
602 biv_core_pkg.g_value_sep ||
603 to_char(l_start_date,l_dt_fmt) ||
604 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
605 biv_core_pkg.g_value_sep ||
606 -- Change for Bug 3285048 l_end_date changed to l_end_date-1
607 to_char(l_end_date-1,l_dt_fmt) ||
608 biv_core_pkg.g_param_sep ||
609 'P_REOPEN' || biv_core_pkg.g_value_sep || 'Y',
610 col11 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
611 biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
612 biv_core_pkg.g_value_sep ||
613 to_char(l_start_date,l_dt_fmt) ||
614 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
615 biv_core_pkg.g_value_sep ||
616 -- Change for Bug 3285048 l_end_date changed to l_end_date-1
617 to_char(l_end_date-1,l_dt_fmt) ||
618 biv_core_pkg.g_param_sep ||
619 'P_RECLOSE' || biv_core_pkg.g_value_sep || 'Y',
620 col13 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
621 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
622 biv_core_pkg.g_value_sep ||
623 to_char(l_end_date,l_dt_fmt) ||
624 biv_core_pkg.g_param_sep ||
625 'P_EBLOG' || biv_core_pkg.g_value_sep || 'Y',
626 col17 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
627 biv_core_pkg.g_param_sep ||'P_CR_ST' ||
628 biv_core_pkg.g_value_sep ||
629 to_char(l_start_date,l_dt_fmt) ||
630 biv_core_pkg.g_param_sep ||'P_CR_END' ||
631 biv_core_pkg.g_value_sep ||
632 -- Change for Bug 3285048 changed l_end_date to l_end_date-1
633 to_char(l_end_date-1,l_dt_fmt) ||
634 biv_core_pkg.g_param_sep || 'P_CHNL=WEB',
635 creation_date = sysdate
636 where report_code = 'BIV_HS_SR_ACTIVITY'
637 and session_id = l_session_id;
638
639 ---
640 --- Add a row fot toal of all column
641 ---
642 SELECT count(*) into l_ttl_recs
643 FROM biv_tmp_hs2
644 WHERE report_code = 'BIV_HS_SR_ACTIVITY'
645 and session_id = l_session_id;
646 if (l_debug = 'Y') then
647 biv_core_pkg.biv_debug('Total Recs:'||to_char(l_ttl_recs),
648 biv_core_pkg.g_report_id);
649 end if;
650 if ( l_ttl_recs > 1 and ( l_ttl_recs < biv_core_pkg.g_disp or
651 biv_core_pkg.g_view_by = 'AGENT' or
652 biv_core_pkg.g_view_by = 'PRD') ) then
653 if (l_debug = 'Y') then
654 biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
655 end if;
656 insert into biv_tmp_hs2 (report_code, rowno,
657 col4, col6, col8, col10, col12,
658 col14, col18, col1, session_id)
659 SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
660 sum(col10), sum(col12), sum(col14), sum(col18), 'Y', session_id
661 FROM biv_tmp_hs2
662 WHERE session_id = l_session_id
663 and report_code = 'BIV_HS_SR_ACTIVITY'
664 group by report_code, session_id;
665 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
666 biv_core_pkg.reconstruct_param_str;
667 --Change for bug 3188504 appended P_PREVR to the url
668 l_new_param_str := l_new_param_str || 'jtfBinId' ||
669 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
670 biv_core_pkg.g_param_sep || 'P_PREVR' ||
671 biv_core_pkg.g_value_sep || 'BIV_HS_SR_ACTIVITY' ||
672 biv_core_pkg.g_param_sep;
673 l_new_param_str1:= l_drilldown_rep ||biv_core_pkg.g_param_sep ||
674 biv_core_pkg.reconstruct_param_str;
675 l_new_param_str1 := l_new_param_str1 || 'jtfBinId' ||
676 biv_core_pkg.g_value_sep || l_drilldown_rep ||
677 biv_core_pkg.g_param_sep ;
678 --
679 if (biv_core_pkg.g_view_by = 'AGRP') then
680 l_new_param_str := l_new_param_str || 'P_AGRP_LVL' ||
681 biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
682 biv_core_pkg.g_param_sep;
683 elsif (biv_core_pkg.g_view_by = 'OGRP') then
684 l_new_param_str := l_new_param_str || 'P_OGRP_LVL' ||
685 biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
686 biv_core_pkg.g_param_sep;
687 end if;
688
689 l_loc := 'Before update of odd cols for drill down for Total Row';
690 SELECT count(*) into l_ttl_recs
691 FROM biv_tmp_hs2
692 WHERE report_code = 'BIV_HS_SR_ACTIVITY'
693 and session_id = l_session_id;
694 if (l_debug = 'Y') then
695 biv_core_pkg.biv_debug('Total Recs:'||to_char(l_ttl_recs),
696 biv_core_pkg.g_report_id);
697 end if;
698 update biv_tmp_hs2
699 set --id = col1,
700 col1 = l_new_param_str1|| 'P_VIEW_BY' ||
701 biv_core_pkg.g_value_sep || l_new_view_by ||
702 biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
703 biv_core_pkg.g_value_sep ||
704 to_char(l_start_date,l_dt_fmt) ||
705 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
706 biv_core_pkg.g_value_sep ||
707 to_char(l_end_date-1,l_dt_fmt),
708 col3 = l_new_param_str || 'P_ST_DATE' ||
709 biv_core_pkg.g_value_sep ||
710 to_char(l_start_date,l_dt_fmt) ||
711 biv_core_pkg.g_param_sep ||
712 'P_OBLOG' || biv_core_pkg.g_value_sep || 'Y',
713 col5 = l_new_param_str || 'P_CR_ST' ||
714 biv_core_pkg.g_value_sep ||
715 to_char(l_start_date,l_dt_fmt) ||
716 biv_core_pkg.g_param_sep ||'P_CR_END' ||
717 biv_core_pkg.g_value_sep ||
718 -- Change for Bug 3285048 chnaged l_end_date to l_end_date-1
719 to_char(l_end_date-1,l_dt_fmt),
720 col7 = l_new_param_str || 'P_CL_ST' ||
721 biv_core_pkg.g_value_sep ||
722 to_char(l_start_date,l_dt_fmt) ||
723 biv_core_pkg.g_param_sep ||'P_CL_END' ||
724 biv_core_pkg.g_value_sep ||
725 to_char(l_end_date-1,l_dt_fmt) ,
726 col9 = l_new_param_str || 'P_ST_DATE' ||
727 biv_core_pkg.g_value_sep ||
728 to_char(l_start_date,l_dt_fmt) ||
729 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
730 biv_core_pkg.g_value_sep ||
731 -- Change for Bug 3285048 changed l_end_date to l_end_date-1
732 to_char(l_end_date-1,l_dt_fmt) ||
733 biv_core_pkg.g_param_sep ||
734 'P_REOPEN' || biv_core_pkg.g_value_sep || 'Y',
735 col11 = l_new_param_str || 'P_ST_DATE' ||
736 biv_core_pkg.g_value_sep ||
737 to_char(l_start_date,l_dt_fmt) ||
738 biv_core_pkg.g_param_sep ||'P_END_DATE' ||
739 biv_core_pkg.g_value_sep ||
740 -- Change for Bug 3285048 changed l_end_date to l_end_date-1
741 to_char(l_end_date-1,l_dt_fmt) ||
742 biv_core_pkg.g_param_sep ||
743 'P_RECLOSE' || biv_core_pkg.g_value_sep || 'Y',
744 col13 = l_new_param_str || 'P_END_DATE' ||
745 biv_core_pkg.g_value_sep ||
746 to_char(l_end_date,l_dt_fmt) ||
747 biv_core_pkg.g_param_sep ||
748 'P_EBLOG' || biv_core_pkg.g_value_sep || 'Y',
749 col17 = l_new_param_str || 'P_CR_ST' ||
750 biv_core_pkg.g_value_sep ||
751 to_char(l_start_date,l_dt_fmt) ||
752 biv_core_pkg.g_param_sep ||'P_CR_END' ||
753 biv_core_pkg.g_value_sep ||
754 -- Change for Bug 3285048 changed l_end_date to l_end_date-1
755 to_char(l_end_date-1,l_dt_fmt) ||
756 biv_core_pkg.g_param_sep || 'P_CHNL=WEB',
757 col2 = l_ttl_meaning,
758 creation_date = sysdate
759 where report_code = 'BIV_HS_SR_ACTIVITY'
760 and session_id = l_session_id
761 and col1 = 'Y';
762
763 end if;
764 l_loc := 'After update of odd cols';
765 if (l_debug = 'Y') then
766 biv_core_pkg.biv_debug('End of Report', biv_core_pkg.g_report_id);
767 end if;
768 biv_core_pkg.g_report_id := 'NULL';
769 exception
770 when others then
771 if (l_debug = 'Y') then
772 l_err := 'Err at ' || l_loc|| ':'|| substr(sqlerrm,1,500);
773 biv_core_pkg.biv_debug(l_err,'BIV_HS_SR_ACTIVITY');
774 commit;
775 end if;
776 end;
777 function col_heading_10 (p_param_str varchar2) return varchar2 as
778 l_view_by varchar2(80);
779 begin
780 l_view_by := biv_core_pkg.get_parameter_value(p_param_str,'P_VIEW_BY');
781 if (l_view_by = 'AGENT' or l_view_by = 'PRD') then
782 return biv_core_pkg.get_lookup_meaning('AVG_BACKLOG_AGE');
783 else
784 return biv_core_pkg.get_lookup_meaning('TOTAL_HEAD_COUNT');
785 end if;
786
787 end;
788 end;