[Home] [Help]
PACKAGE BODY: APPS.BIV_RT_AGENT_MGR_PKG
Source
1 package body biv_rt_agent_mgr_pkg as
2 /* $Header: bivrmgrb.pls 115.16 2004/02/17 08:04:22 vganeshk ship $ */
3 procedure agent_report(p_param_str varchar2) as
4 l_cur number;
5 l_from_list varchar2(1000);
6 l_from_list2 varchar2(1000);
7 l_from_list3 varchar2(1000);
8 l_where_clause varchar2(2000);
9 l_where_clause2 varchar2(2000);
10 l_where_clause3 varchar2(2000);
11 l_sql_sttmnt varchar2(5000);
12 l_order_by varchar2(80);
13 l_dummy number;
14 x_param_str varchar2(500);
15 l_new_param_str varchar2(200);
16 l_session_id varchar2(50);
17 l_dt varchar2(20);
18 l_dt_fmt varchar2(50) := fnd_profile.value('ICX_DATE_FORMAT_MASK');
19 l_ttl_recs number;
20 l_url1 varchar2(2000);
21 l_url3 varchar2(2000);
22 l_url5 varchar2(2000);
23 l_url7 varchar2(2000);
24 l_url9 varchar2(2000);
25 l_url_base_col varchar2(2000);
26 l_ttl_desc fnd_lookups.meaning % type;
27 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
28 begin
29 biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
30 biv_core_pkg.g_report_id := 'BIV_RT_AGENT_REPORT';
31 l_dt := to_char(sysdate,l_dt_fmt);
32 if (l_debug = 'Y') then
33 biv_core_pkg.biv_debug(p_param_str,biv_core_pkg.g_report_id);
34 biv_core_pkg.biv_debug('Date:'|| l_dt || ', format:' || l_dt_fmt,
35 biv_core_pkg.g_report_id);
36 end if;
37
38 l_session_id := biv_core_pkg.get_session_id;
39
40 biv_core_pkg.get_report_parameters(p_param_str);
41
42 -- Change for Bug 3386946
43 l_from_list := ' FROM cs_incidents_b_sec sr,
44 cs_incident_statuses_b stat';
45 biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
46 l_from_list2 := l_from_list || ',
47 cs_incidents_all_tl srt ';
48 l_where_clause := l_where_clause || '
49 and sr.incident_status_id = stat.incident_status_id ';
50 -- and sr.resource_type = ''RS_EMPLOYEE''
51 -- and sr.incident_owner_id is not null
52 l_where_clause2 := l_where_clause || '
53 and sr.incident_id = srt.incident_id
54 and srt.language = userenv(''LANG'') ';
55
56 if (l_debug = 'Y') then
57 biv_core_pkg.biv_debug(l_where_clause,biv_core_pkg.g_report_id);
58 end if;
59
60 /**** 10/11/2003. see the reason in manager report.
61 if (biv_core_pkg.g_srt_by = '2') then
62 l_sql_sttmnt := '
63 SELECT sr.incident_owner_id ,
64 count(sr.incident_id) col2
65 ' || l_from_list || l_where_clause || '
66 and nvl(stat.close_flag,''N'') <> ''Y''
67 group by sr.incident_owner_id
68 order by 2 desc';
69 elsif (biv_core_pkg.g_srt_by = '3') then
70 l_sql_sttmnt := '
71 SELECT sr.incident_owner_id,
72 sum(decode(upper(sr.sr_creation_channel), ''WEB'',1,0)) col2
73 ' || l_from_list || l_where_clause || '
74 and sr.incident_date >= trunc(sysdate)
75 and sr.incident_date < trunc(sysdate+1)
76 and sr.sr_creation_channel = ''WEB''
77 group by sr.incident_owner_id
78 order by 2 desc';
79 elsif (biv_core_pkg.g_srt_by = '4') then
80 l_sql_sttmnt := '
81 SELECT sr.incident_owner_id,
82 sum(decode(upper(sr.sr_creation_channel), ''PHONE'',1,0)) col2
83 ' || l_from_list || l_where_clause || '
84 and sr.incident_date >= trunc(sysdate)
85 and sr.incident_date < trunc(sysdate+1)
86 and sr.sr_creation_channel = ''PHONE''
87 group by sr.incident_owner_id
88 order by 2 desc';
89 elsif (biv_core_pkg.g_srt_by = '5') then
90 l_sql_sttmnt := '
91 SELECT sr.incident_owner_id ,
92 count(sr.incident_id) col2
93 ' || l_from_list || l_where_clause || '
94 and sr.close_date >= trunc(sysdate)
95 and sr.close_date < trunc(sysdate+1)
96 and nvl(stat.close_flag,''N'') = ''Y''
97 group by sr.incident_owner_id
98 order by 2 desc';
99 ******************** 10/11/2003 ***********************/
100 if (biv_core_pkg.g_srt_by = '1') then
101 if (biv_core_pkg.g_agrp_cnt > 0) then
102 l_sql_sttmnt := '
103 SELECT sr.incident_owner_id ,
104 count(sr.incident_id) col2
105 ' || l_from_list || l_where_clause || '
106 group by sr.incident_owner_id
107 order by 1 desc';
108 else
109 biv_bin_esc_rsc_pkg.get_resource_where_clause(l_from_list3,l_where_clause3);
110 l_sql_sttmnt := '
111 SELECT distinct rsc.resource_id incident_owner_id,
112 substr(rsc.source_name,1,50) col2
113 ' || l_from_list3 || l_where_clause3 || '
114 order by 2 asc';
115 end if;
116
117 l_sql_sttmnt := '
118 insert into biv_tmp_rt2(report_code, col1, col4, session_id)
119 SELECT ''X'', incident_owner_id, col2, :session_id
120 FROM (' || l_sql_sttmnt || ')
121 WHERE rownum <= :rows_to_display ';-- || biv_core_pkg.g_disp;
122
123 if (l_debug = 'Y') then
124 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
125 end if;
126
127 l_cur := dbms_sql.open_cursor;
128 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
129 biv_core_pkg.bind_all_variables(l_cur);
130 dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
131 dbms_sql.bind_variable(l_cur,':rows_to_display',
132 to_number(biv_core_pkg.g_disp));
133 l_dummy := dbms_sql.execute(l_cur);
134 dbms_sql.close_cursor(l_cur);
135 biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
136 end if;
137
138 /***** 10/11/2002
139 the above sql statement will few rows where order col is not null.
140 this is causing total line to be different and drill down from total line
141 print real total and hence confusion
142 l_from_list := l_from_list || ',
143 biv_tmp_rt2 rep';
144 l_from_list2:= l_from_list2 || ',
145 biv_tmp_rt2 rep';
146 l_where_clause := l_where_clause || '
147 and to_char(nvl(sr.incident_owner_id'||',-999))=nvl(rep.col1,''-999'')
148 and rep.report_code = ''X''
149 and rep.session_id = :session_id ';
150 l_where_clause2 := l_where_clause2 || '
151 and to_char(nvl(sr.incident_owner_id'||',-999))=nvl(rep.col1,''-999'')
152 and rep.report_code = ''X''
153 and rep.session_id = :session_id ';
154 *****/
155 l_sql_sttmnt := '
156 SELECT sr.incident_owner_id col1,
157 1 col4, 0 col6, 0 col8, 0 col10
158 ' || l_from_list || l_where_clause || '
159 and nvl(stat.close_flag,''N'') <> ''Y''
160 UNION ALL
161 SELECT sr.incident_owner_id,
162 0,decode(upper(sr.sr_creation_channel), ''WEB'',1,0),
163 decode(upper(sr.sr_creation_channel), ''PHONE'',1,0),0
164 ' || l_from_list || l_where_clause || '
165 and sr.incident_date >= trunc(sysdate)
166 and sr.incident_date < trunc(sysdate+1)
167 UNION ALL
168 SELECT sr.incident_owner_id ,
169 0, 0, 0, 1
170 ' || l_from_list || l_where_clause || '
171 and sr.close_date >= trunc(sysdate)
172 and sr.close_date < trunc(sysdate+1)
173 and nvl(stat.close_flag,''N'') = ''Y''';
174
175 if (biv_core_pkg.g_srt_by = '1') then
176 l_sql_sttmnt := l_sql_sttmnt || '
177 UNION ALL
178 SELECT to_number(col1), 0,0,0,0
179 FROM biv_tmp_rt2 rep
180 WHERE session_id = :session_id
181 ';
182 end if;
183
184 l_sql_sttmnt := '
185 SELECT col1, sum(col4) col4, sum(col6) col6,
186 sum(col8) col8, sum(col10) col10
187 FROM (' || l_sql_sttmnt || ')
188 group by col1';
189
190 l_sql_sttmnt := '
191 insert into biv_tmp_rt2 (report_code,rowno,
192 col1, col4, col6, col8, col10, col12, session_id)
193 SELECT ''Y'', rownum,
194 col1, col4, col6, col8, col10, ''N'', :session_id
195 FROM (' || l_sql_sttmnt || ')
196 ';
197
198 if (l_debug = 'Y') then
199 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
200 end if;
201
202 l_cur := dbms_sql.open_cursor;
203 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
204 biv_core_pkg.bind_all_variables(l_cur);
205 dbms_sql.bind_variable(l_cur, ':session_id', l_session_id);
206 l_dummy := dbms_sql.execute(l_cur);
207 biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
208
209 if (nvl(biv_core_pkg.g_srt_by,'1') = '1') then
210 l_order_by := '1 asc';
211 else
212 l_order_by := 'to_number(col' || to_number(biv_core_pkg.g_srt_by)*2 ||
213 ') desc';
214 end if;
215 x_param_str := substr(p_param_str,1,length(p_param_str)-2);
216 l_sql_sttmnt := '
217 insert into biv_tmp_rt2(report_code,rowno,id,
218 col1,col2,col4,col6,col8,col10,col12,col3,col5,col7,col9,
219 session_id)
220 SELECT ''BIV_RT_AGENT_REPORT'', rownum,col1,
221 col1, col2, col4, col6, col8, col10,col12,
222 col1 ,
223 col1 ,
224 col1 ,
225 col1 ,
226 session_id
227 FROM (SELECT col2,col4, col6,col8,col10,col12, col1, session_id
228 FROM biv_tmp_rt2
229 WHERE report_code = ''Y''
230 and session_id = :session_id
231 order by ' || l_order_by || ', col2
232 )
233 WHERE rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,200);
234 if (l_debug = 'Y') then
235 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
236 end if;
237 execute immediate l_sql_sttmnt using l_session_id,
238 biv_core_pkg.g_disp;
239
240 biv_core_pkg.reset_view_by_param;
241 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep
242 || biv_core_pkg.reconstruct_param_str;
243 -- 7/23/2 above line reconstruct_param_str is needed because assignment
244 -- group parameter compares owner_group_id and one agent may be
245 -- present in many owner group ids. same could be applicable to other
246 -- parameters.
247 -- 5/9/2 above line s commented because for all the lines we have agent id
248 -- as parameter, so no need for manage or org or asg group ids
249 -- those parameter will cause extrac join but results will be same.
250 l_new_param_str := l_new_param_str || 'jtfBinId' ||
251 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
252 biv_core_pkg.g_param_sep ||'P_AGENT_ID' ||
253 biv_core_pkg.g_value_sep ;
254
255 delete FROM biv_tmp_rt2
256 WHERE report_code in ('X', 'Y')
257 and session_id = l_session_id
258 ;
259 commit;
260 update biv_tmp_rt2
261 set col1 = 'resource' || biv_core_pkg.g_param_sep ||
262 -- 'ID' || biv_core_pkg.g_value_sep || col1,
263 'p_resource_id' || biv_core_pkg.g_value_sep || col1,
264 col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
265 biv_core_pkg.g_param_sep ||
266 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y',
267 col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
268 biv_core_pkg.g_param_sep ||
269 'P_CHNL' || biv_core_pkg.g_value_sep || 'PHONE' ||
270 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
271 biv_core_pkg.g_value_sep || 'Y',
272 col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
273 biv_core_pkg.g_param_sep ||
274 'P_CHNL' || biv_core_pkg.g_value_sep || 'WEB' ||
275 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
276 biv_core_pkg.g_value_sep || 'Y',
277 col9 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
278 biv_core_pkg.g_param_sep ||
279 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
280 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
281 biv_core_pkg.g_value_sep || l_dt ||
282 biv_core_pkg.g_param_sep || 'P_CL_END' ||
283 biv_core_pkg.g_value_sep || l_dt ,
284 creation_date = sysdate
285 WHERE report_code = 'BIV_RT_AGENT_REPORT'
286 and session_id = l_session_id;
287
288 -- Change for Bug 3448591
289 update biv_tmp_rt2 rpt
290 set col12 = 'Y'
291 WHERE session_id = l_session_id
292 and report_code = 'BIV_RT_AGENT_REPORT'
293 and exists ( SELECT 1 FROM JTF_RS_WEB_AVAILABLE_V avl
294 WHERE avl.resource_id = rpt.id);
295 commit;
296 ---
297 --- Add a row fot toal of all column
298 ---
299 SELECT count(*) into l_ttl_recs
300 FROM biv_tmp_rt2
301 WHERE report_code = 'BIV_RT_AGENT_REPORT'
302 and session_id = l_session_id;
303 if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
304 if (l_debug = 'Y') then
305 biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
306 end if;
307 insert into biv_tmp_rt2 (report_code, rowno,
308 col4, col6, col8, col10, col13,session_id)
309 SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
310 sum(col10) ,'Y', session_id
311 FROM biv_tmp_rt2
312 WHERE session_id = l_session_id
313 and report_code = 'BIV_RT_AGENT_REPORT'
314 group by report_code, session_id;
315 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
316 biv_core_pkg.reconstruct_param_str;
317 l_new_param_str := l_new_param_str || 'jtfBinId' ||
318 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
319 biv_core_pkg.g_param_sep
320 ;
321
322 l_ttl_desc := biv_core_pkg.get_lookup_meaning('TOTAL');
323 update biv_tmp_rt2
324 set col2 = l_ttl_desc,
325 col1 = 'resource' || biv_core_pkg.g_param_sep ||
326 'p_resource_id' || biv_core_pkg.g_value_sep || col1,
327 col3 = l_new_param_str ||
328 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y',
329 col7 = l_new_param_str ||
330 'P_CHNL' || biv_core_pkg.g_value_sep || 'PHONE' ||
331 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
332 biv_core_pkg.g_value_sep || 'Y',
333 col5 = l_new_param_str ||
334 'P_CHNL' || biv_core_pkg.g_value_sep || 'WEB' ||
335 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
336 biv_core_pkg.g_value_sep || 'Y',
337 col9 = l_new_param_str ||
338 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
339 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
340 biv_core_pkg.g_value_sep || l_dt ||
341 biv_core_pkg.g_param_sep || 'P_CL_END' ||
342 biv_core_pkg.g_value_sep || l_dt ,
343 creation_date = sysdate
344 WHERE report_code = 'BIV_RT_AGENT_REPORT'
345 and session_id = l_session_id
346 and col13 = 'Y';
347 end if;
348 --- End of Total row
349 if (l_debug = 'Y') then
350 biv_core_pkg.biv_debug('End of Report',biv_core_pkg.g_report_id);
351 end if;
352 biv_core_pkg.g_report_id := null;
353 exception
354 when others then
355 rollback;
356 if (l_debug = 'Y') then
357 biv_core_pkg.biv_debug(l_sql_sttmnt, biv_core_pkg.g_report_id);
358 biv_core_pkg.biv_debug('Err:' ||sqlerrm, biv_core_pkg.g_report_id);
359 l_new_param_str := 'Err:'||substr(sqlerrm,1,145);
360 insert into biv_tmp_rt2 (report_code, session_id,col2)
361 values('BIV_RT_AGENT_REPORT',l_session_id, l_new_param_str);
362 commit;
363 end if;
364 end;
365 procedure manager_report(p_param_str varchar2) as
366 l_cur number;
367 l_from_list varchar2(1000);
368 l_where_clause varchar2(2000);
369 l_from_list2 varchar2(1000);
370 l_where_clause2 varchar2(2000);
371 l_sql_sttmnt varchar2(4000);
372 l_order_by varchar2(80);
373 l_new_param_str varchar2(200);
374 l_ttl_param_str varchar2(200);
375 l_dummy number;
376 l_session_id biv_tmp_rt2.session_id % type;
377 l_pos varchar2(50);
378 l_dt_fmt varchar2(50) := fnd_profile.value('ICX_DATE_FORMAT_MASK');
379 l_dt varchar2(50);
380 l_ttl_recs number;
381 l_ttl_desc fnd_lookups.meaning % type;
382 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
383 begin
384 biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
385 l_dt := to_char(sysdate,l_dt_fmt);
386 biv_core_pkg.g_report_id := 'BIV_RT_MANAGER_REPORT';
387 if (l_debug = 'Y') then
388 biv_core_pkg.biv_debug(p_param_str,biv_core_pkg.g_report_id);
389 end if;
390 l_session_id := biv_core_pkg.get_session_id;
391 biv_core_pkg.get_report_parameters(p_param_str);
392 -- Change for Bug 3386946
393 l_from_list := ' FROM cs_incidents_b_sec sr';
394 biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
395
396 l_from_list2 := l_from_list || ',
397 cs_incident_statuses_b stat ';
398 l_where_clause2 := l_where_clause || '
399 and sr.incident_status_id = stat.incident_status_id ';
400
401 -- remove it
402 --
403 --
404 --biv_core_pkg.g_srt_by := '4';
405 --
406 --
407 --
408 --
409 --
410 --
411 --
412 --
413
414 /* 10/11/2003 This part is not necessary. Total line problem
415 report print total from rows displayed but real total of columns
416 such as total backlog may be different and when you drilldown, it
417 displays real total.
418
419 This part was written so that you get data for order by column and
420 rejected record with 0 values. In this way this section is rejecting
421 all other records where other columns have values. Suppose order by is
422 New requests and there are 10 managers who do not have any new request
423 bug have some backlog. Then this section will cause those managers
424 not get selected. so total backlog in total line will not represent
425 TOTAL backlog. when click on total backlog, it displays the TOTAL
426 backlog which obivously does not match the total line value.
427 if (biv_core_pkg.g_srt_by = '2') then
428 l_sql_sttmnt := '
429 SELECT ' || biv_core_pkg.g_base_column || ' col1,
430 count(sr.incident_id) col2
431 ' || l_from_list || l_where_clause || '
432 and sr.incident_date >= trunc(sysdate)
433 and sr.incident_date < trunc(sysdate+1)
434 group by ' || biv_core_pkg.g_base_column || '
435 order by 2 desc ';
436 elsif (biv_core_pkg.g_srt_by = '3') then
437 l_sql_sttmnt := '
438 SELECT ' || biv_core_pkg.g_base_column || ' col1,
439 count(sr.incident_id) col2
440 ' || l_from_list2 || l_where_clause2 || '
441 and nvl(stat.close_flag,''N'') = ''Y''
442 and sr.close_date >= trunc(sysdate)
443 and sr.close_date < trunc(sysdate+1)
444 group by ' || biv_core_pkg.g_base_column || '
445 order by 2 desc ';
446 elsif (biv_core_pkg.g_srt_by = '4') then
447 l_sql_sttmnt := '
448 SELECT ' || biv_core_pkg.g_base_column || ' col1,
449 count(sr.incident_id) col2
450 ' || l_from_list2 || l_where_clause2 || '
451 and nvl(stat.close_flag,''N'') <> ''Y''
452 group by ' || biv_core_pkg.g_base_column || '
453 order by 2 desc ' ;
454 else
455 l_sql_sttmnt := '
456 SELECT ' || biv_core_pkg.g_base_column || ' col1, ' ||
457 biv_core_pkg.g_base_column || ' col2 '
458 || l_from_list || l_where_clause || '
459 group by ' || biv_core_pkg.g_base_column ;
460 end if;
461
462 l_sql_sttmnt := '
463 insert into biv_tmp_rt2(report_code, col1, col4,session_id)
464 SELECT ''X'', col1, col2, :session_id
465 FROM (' || l_sql_sttmnt || ')
466 WHERE rownum <= :rows_to_display '; -- || biv_core_pkg.g_disp;
467
468 if (l_debug = 'Y') then
469 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
470 end if;
471
472 l_cur := dbms_sql.open_cursor;
473 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
474 biv_core_pkg.bind_all_variables(l_cur);
475 dbms_sql.bind_variable(l_cur,':session_id', l_session_id);
476 dbms_sql.bind_variable(l_cur,':rows_to_display', biv_core_pkg.g_disp);
477 l_pos := 'Before Execute';
478 l_dummy := dbms_sql.execute(l_cur);
479 dbms_sql.close_cursor(l_cur);
480
481 l_from_list := l_from_list || ',
482 biv_tmp_rt2 rep';
483 l_from_list2:= l_from_list2 || ',
484 biv_tmp_rt2 rep';
485 l_where_clause := l_where_clause || '
486 and nvl('||biv_core_pkg.g_base_column||
487 ',''-999'') = to_number(nvl(rep.col1,''-999''))
488 and session_id = :session_id ';
489 l_where_clause2 := l_where_clause2 || '
490 and nvl('||biv_core_pkg.g_base_column||
491 ',''-999'') = to_number(nvl(rep.col1,''-999''))
492 and session_id = :session_id ';
493 ****************************************************/
494
495
496
497 l_sql_sttmnt := '
498 SELECT col1, sum(col4) col4, sum(col6) col6, sum(col8) col8,
499 :session_id session_id
500 FROM ( SELECT ' || biv_core_pkg.g_base_column || ' col1,
501 1 col4, 0 col6, 0 col8
502 ' || l_from_list || l_where_clause || '
503 and sr.incident_date >= trunc(sysdate)
504 and sr.incident_date < trunc(sysdate+1)
505 UNION ALL
506 SELECT ' || biv_core_pkg.g_base_column || ' col1,
507 0, 1, 0
508 ' || l_from_list2 || l_where_clause2 || '
509 and nvl(stat.close_flag,''N'') = ''Y''
510 and sr.close_date >= trunc(sysdate)
511 and sr.close_date < trunc(sysdate+1)
512 UNION ALL
513 SELECT ' || biv_core_pkg.g_base_column || ' col1,
514 0, 0, 1
515 ' || l_from_list2 || l_where_clause2 || '
516 and nvl(stat.close_flag,''N'') <> ''Y''
517 ) group by col1 ';
518
519 l_sql_sttmnt := '
520 insert into biv_tmp_rt2 (report_code,rowno,
521 col1, col4, col6, col8,session_id)
522 SELECT ''Y'', rownum,
523 col1, col4, col6, col8,session_id
524 FROM (' || l_sql_sttmnt || ')';
525
526 if (l_debug = 'Y') then
527 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
528 end if;
529
530 l_cur := dbms_sql.open_cursor;
531 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
532 biv_core_pkg.bind_all_variables(l_cur);
533 dbms_sql.bind_variable(l_cur,':session_id', l_session_id);
534 l_pos := 'Before 2nd Execute';
535 l_dummy := dbms_sql.execute(l_cur);
536 biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
537
538 if (biv_core_pkg.g_srt_by = '2') then
539 l_order_by := 'to_number(col4) desc';
540 elsif (biv_core_pkg.g_srt_by = '3') then
541 l_order_by := 'to_number(col6) desc';
542 elsif (biv_core_pkg.g_srt_by = '4') then
543 l_order_by := 'to_number(col8) desc';
544 else
545 l_order_by := 'col2 asc';
546 end if;
547
548 l_ttl_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
549 biv_core_pkg.reconstruct_param_str;
550 l_ttl_param_str := l_ttl_param_str || 'jtfBinId' ||
551 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
552 biv_core_pkg.g_param_sep;
553 -- new reset view by param for all other rows.
554 biv_core_pkg.reset_view_by_param;
555 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
556 biv_core_pkg.reconstruct_param_str;
557 l_new_param_str := l_new_param_str || 'jtfBinId' ||
558 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
559 biv_core_pkg.g_param_sep ||
560 biv_core_pkg.param_for_base_col ||
561 biv_core_pkg.g_value_sep ;
562
563 l_sql_sttmnt := '
564 insert into biv_tmp_rt2(report_code,rowno, col1, col2,
565 col4, col6, col8,session_id)
566 SELECT ''BIV_RT_MANAGER_REPORT'', rownum,
567 col1, col2, col4, col6, col8,session_id
568 FROM (SELECT col1, col2, col4, col6, col8,session_id
569 FROM biv_tmp_rt2
570 WHERE report_code =''Y''
571 and session_id = :session_id
572 order by ' || l_order_by || ')
573 WHERE rownum <= :rows_to_display '; --|| nvl(biv_core_pkg.g_disp,'10');
574 l_pos := 'Before 3rd Execute';
575 if (l_debug = 'Y') then
576 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
577 biv_core_pkg.biv_debug('Order By :'||l_order_by||':',
578 biv_core_pkg.g_report_id);
579 end if;
580 execute immediate l_sql_sttmnt using l_session_id,
581 biv_core_pkg.g_disp;
582 l_pos := 'deleting temp records';
583 delete from biv_tmp_rt2
584 where session_id = l_session_id
585 and report_code in ('X', 'Y');
586 l_pos := 'Before Update of odd columns';
587 update biv_tmp_rt2
588 set col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
589 biv_core_pkg.g_param_sep ||
590 'P_BLOG'||biv_core_pkg.g_value_sep||'Y',
591 col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
592 biv_core_pkg.g_param_sep ||
593 'P_TODAY_ONLY'||biv_core_pkg.g_value_sep||'Y',
594 col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
595 biv_core_pkg.g_param_sep ||
596 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
597 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
598 biv_core_pkg.g_value_sep || l_dt ||
599 biv_core_pkg.g_param_sep || 'P_CL_END' ||
600 biv_core_pkg.g_value_sep || l_dt ,
601 creation_date = sysdate
602 WHERE report_code = 'BIV_RT_MANAGER_REPORT'
603 and session_id = l_session_id;
604 ---
605 --- Add a row fot toal of all column
606 ---
607 SELECT count(*) into l_ttl_recs
608 FROM biv_tmp_rt2
609 WHERE report_code = 'BIV_RT_MANAGER_REPORT'
610 and session_id = l_session_id;
611 if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
612 if (l_debug = 'Y') then
613 biv_core_pkg.biv_debug('Adding Total row: Manager Report',
614 biv_core_pkg.g_report_id);
615 end if;
616 insert into biv_tmp_rt2 (report_code, rowno,
617 col4, col6, col8, col13,session_id)
618 SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
619 'Y', session_id
620 FROM biv_tmp_rt2
621 WHERE session_id = l_session_id
622 and report_code = 'BIV_RT_MANAGER_REPORT'
623 group by report_code, session_id;
624 if (biv_core_pkg.g_view_by = 'AGRP') then
625 l_ttl_param_str := l_ttl_param_str || 'P_AGRP_LVL' ||
626 biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
627 biv_core_pkg.g_param_sep;
628 elsif (biv_core_pkg.g_view_by = 'OGRP') then
629 l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
630 biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
631 biv_core_pkg.g_param_sep;
632 end if;
633 l_ttl_desc := biv_core_pkg.get_lookup_meaning('TOTAL');
634 update biv_tmp_rt2
635 set col2 = l_ttl_desc,
636 col7 = l_ttl_param_str ||
637 'P_BLOG'||biv_core_pkg.g_value_sep||'Y',
638 col3 = l_ttl_param_str ||
639 'P_TODAY_ONLY'||biv_core_pkg.g_value_sep||'Y',
640 col5 = l_ttl_param_str ||
641 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
642 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
643 biv_core_pkg.g_value_sep || l_dt ||
644 biv_core_pkg.g_param_sep || 'P_CL_END' ||
645 biv_core_pkg.g_value_sep || l_dt ,
646 creation_date = sysdate
647 WHERE report_code = 'BIV_RT_MANAGER_REPORT'
648 and session_id = l_session_id
649 and col13 = 'Y';
650 end if;
651
652 ----
653 ----
654 ----
655 if (l_debug = 'Y') then
656 biv_core_pkg.biv_debug('End of Report',biv_core_pkg.g_report_id);
657 end if;
658 biv_core_pkg.g_report_id := null;
659 commit;
660 exception
661 when others then
662 if (l_debug = 'Y') then
663 l_new_param_str := 'Err-manager_report at ' ||l_pos || ':'
664 ||substr(sqlerrm,1,145);
665 biv_core_pkg.biv_debug(l_new_param_str, biv_core_pkg.g_report_id);
666 end if;
667 end;
668 end;