[Home] [Help]
PACKAGE BODY: APPS.BIV_RT_ESC_CUS_BLOG_PKG
Source
1 PACKAGE BODY biv_rt_esc_cus_blog_pkg AS
2 /* $Header: bivecbgb.pls 115.30 2004/04/06 21:19:57 ltong ship $ */
3
4 -- severity label in customer backlog report
5 function severity_label(p_param_str IN varchar2 /*default null*/) return varchar2 is
6 l_label_m varchar2(100);
7 l_label_s varchar2(100);
8 l_check_val number;
9 begin
10
11 select attribute_label_long into l_label_m
12 from ak_attributes_vl
13 where attribute_application_id = 862
14 and attribute_code='P_DASH_SR_VIEW_11';
15
16 l_check_val:=fnd_profile.value('BIV:INC_SEVERITY_1') ;
17
18 if l_check_val is not null then
19 execute immediate ' select description
20 from cs_incident_severities_vl
21 where incident_severity_id=:l_check_val' into l_label_s using l_check_val;
22 l_label_m:=l_label_m||' '||l_label_s;
23 end if;
24
25 return l_label_m;
26 end severity_label;
27
28
29 function inc_status_1_label(p_param_str IN varchar2 /*default null*/) return varchar2 is
30 l_label_m varchar2(100);
31 l_label_m1 varchar2(100);
32 l_label_m2 varchar2(100);
33 l_label_m3 varchar2(100);
34 l_label_s varchar2(100);
35 l_check_val number;
36 begin
37 select attribute_label_long into l_label_m1
38 from ak_attributes_vl
39 where attribute_application_id = 862
40 and attribute_code='P_SR_SEV_RPT_5';
41
42 select attribute_label_long into l_label_m2
43 from ak_attributes_vl
44 where attribute_application_id = 862
45 and attribute_code='P_SR_SEV_RPT_7';
46
47
48 select attribute_label_long into l_label_m3
49 from ak_attributes_vl
50 where attribute_application_id = 862
51 and attribute_code='P_DASH_SR_VIEW_9';
52
53 l_label_m:=l_label_m1||' '||l_label_m3||' '||l_label_m2;
54
55
56 l_check_val:= fnd_profile.value('BIV:INC_STATUS_1');
57
58
59 if l_check_val is not null then
60 execute immediate ' select name
61 from cs_incident_statuses_vl
62 where incident_status_id=:l_check_val' into l_label_s using l_check_val;
63 l_label_m:=l_label_m1||' '||l_label_m2||' '||l_label_s||' '||l_label_m3;
64 -- l_label_m:=l_label_s||' '||l_label_m;
65 end if;
66
67 return l_label_m;
68 end inc_status_1_label;
69
70
71
72 function inc_status_2_label(p_param_str IN varchar2 /*default null*/) return varchar2 is
73 l_label_m varchar2(100);
74 l_label_m1 varchar2(100);
75 l_label_m2 varchar2(100);
76 l_label_m3 varchar2(100);
77 l_label_s varchar2(100);
78 l_check_val number;
79 begin
80 select attribute_label_long into l_label_m1
81 from ak_attributes_vl
82 where attribute_application_id = 862
83 and attribute_code='P_SR_SEV_RPT_5';
84
85 select attribute_label_long into l_label_m2
86 from ak_attributes_vl
87 where attribute_application_id = 862
88 and attribute_code='P_SR_SEV_RPT_7';
89
90
91 select attribute_label_long into l_label_m3
92 from ak_attributes_vl
93 where attribute_application_id = 862
94 and attribute_code='P_DASH_SR_VIEW_9';
95
96 l_label_m:=l_label_m1||' '||l_label_m3||' '||l_label_m2;
97
98
99 l_check_val:= fnd_profile.value('BIV:INC_STATUS_2');
100
101
102 if l_check_val is not null then
103 execute immediate ' select name
104 from cs_incident_statuses_vl
105 where incident_status_id=:l_check_val' into l_label_s using l_check_val;
106 l_label_m:=l_label_m1||' '||l_label_m2||' '||l_label_s||' '||l_label_m3;
107 -- l_label_m:=l_label_s||' '||l_label_m;
108 end if;
109
110 return l_label_m;
111 end inc_status_2_label;
112
113 function inc_status_3_label(p_param_str IN varchar2 /*default null*/) return varchar2 is
114 l_label_m varchar2(100);
115 l_label_m1 varchar2(100);
116 l_label_m2 varchar2(100);
117 l_label_m3 varchar2(100);
118 l_label_s varchar2(100);
119 l_check_val number;
120 begin
121 select attribute_label_long into l_label_m1
122 from ak_attributes_vl
123 where attribute_application_id = 862
124 and attribute_code='P_SR_SEV_RPT_5';
125
126 select attribute_label_long into l_label_m2
127 from ak_attributes_vl
128 where attribute_application_id = 862
129 and attribute_code='P_SR_SEV_RPT_7';
130
131
132 select attribute_label_long into l_label_m3
133 from ak_attributes_vl
134 where attribute_application_id = 862
135 and attribute_code='P_DASH_SR_VIEW_9';
136
137 l_label_m:=l_label_m1||' '||l_label_m3||' '||l_label_m2;
138
139
140 l_check_val:= fnd_profile.value('BIV:INC_STATUS_3');
141
142
143 if l_check_val is not null then
144 execute immediate ' select name
145 from cs_incident_statuses_vl
146 where incident_status_id=:l_check_val' into l_label_s using l_check_val;
147 l_label_m:=l_label_m1||' '||l_label_m2||' '||l_label_s||' '||l_label_m3;
148 -- l_label_m:=l_label_s||' '||l_label_m;
149 end if;
150
151 return l_label_m;
152 end inc_status_3_label;
153
154
155 function base_column_label(p_param_str IN varchar2 /*default null*/) return varchar2 is
156 l_label varchar2(100);
157
158 begin
159
160
161 select meaning into l_label from fnd_lookups where lookup_type='BIV_VIEW_BY'
162 and lookup_code=biv_core_pkg.g_view_by;
163
164 return l_label;
165 end base_column_label;
166
167
168 procedure customer_backlog ( p_param_str in varchar2 ) is
169
170 x_where_clause1 varchar2(2000);
171 x_where_clause2 varchar2(2000);
172 x_where_clause varchar2(2000);
173 x_from_list varchar2(1000);
174 x_from_list1 varchar2(1000);
175 x_from_list2 varchar2(1000);
176 x_sql_sttmnt long;
177 x_order number;
178 x_cur pls_integer;
179 x_dummy pls_integer;
180 x_order_by number;
181 x_display number;
182 x_severity_id number;
183 l_new_param_str varchar2(200);
184 l_new_param_str1 varchar2(200);
185 q3_str varchar2(2000);
186 q4_str varchar2(2000);
187 q5_str varchar2(2000);
188 q6_str varchar2(2000);
189 x_session number;
190 x_sev_count number;
191 l_ttl_recs number;
192 l_ttl_meaning fnd_lookups.meaning % type;
193 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
194
195 begin
196 x_session:=biv_core_pkg.get_session_id;
197
198 biv_core_pkg.clean_dcf_table('BIV_TMP_RT1');
199
200 q3_str:=' ';
201 q4_str:=' ';
202 q5_str:=' ';
203 q6_str:=' ';
204 x_severity_id:=fnd_profile.value('BIV:INC_SEVERITY_1');
205
206 biv_core_pkg.get_report_parameters(p_param_str);
207 biv_core_pkg.g_report_type:='RT';
208 if (l_debug = 'Y') then
209 biv_core_pkg.biv_debug('Param :'||p_param_str,'BIV_CUSTOMER_BACKLOG');
210 commit;
211 end if;
212
213 x_sev_count:=biv_core_pkg.g_sev_cnt;
214 --Change for Bug 3386946
215 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
216
217 -- Buliding from list and where clause for severity query
218 biv_core_pkg.g_sev_cnt:=0;
219 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
220 x_where_clause1:= x_where_clause||' and sr.incident_status_id=stat.incident_status_id
221 and sr.incident_severity_id=:x_severity_id ' ;
222 x_from_list1:=x_from_list;
223 if (l_debug = 'Y') then
224 biv_core_pkg.biv_debug('Severity Query Where Clause :'||x_where_clause1,
225 'BIV_CUSTOMER_BACKLOG');
226 biv_core_pkg.biv_debug('Severity Query From List :'||x_from_list1,
227 'BIV_CUSTOMER_BACKLOG');
228 commit;
229 end if;
230 -- Restting severity parameter and building where clause and from list
231 biv_core_pkg.g_sev_cnt:=x_sev_count;
232 -- Change for Bug 3386946
233 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
234 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
235 x_where_clause:= x_where_clause||' and sr.incident_status_id=stat.incident_status_id ';
236 if (l_debug = 'Y') then
237 biv_core_pkg.biv_debug('Others Where Clause :'||x_where_clause,
238 'BIV_CUSTOMER_BACKLOG');
239 biv_core_pkg.biv_debug('Others From List :'||x_from_list,
240 'BIV_CUSTOMER_BACKLOG');
241 commit;
242 end if;
243
244 -- Building from list and where clause in Escalated Request case
245 if (instr(upper(x_from_list),'JTF_TASKS_B') = 0) then
246 x_from_list2 := x_from_list || ',
247 jtf_tasks_b task,
248 jtf_task_references_b ref';
249 else
250 x_from_list2 := x_from_list;
251 end if;
252 x_where_clause2:= x_where_clause || ' and sr.incident_id = ref.object_id
253 and ref.object_type_code = ''SR''
254 and ref.reference_code = ''ESC''
255 and ref.task_id = task.task_id
256 and task.task_type_id=22 and escalation_level is not null ' ;
257
258 if (l_debug = 'Y') then
259 biv_core_pkg.biv_debug('Escalated Sr Where Clause :'||x_where_clause2,
260 'BIV_CUSTOMER_BACKLOG');
261 biv_core_pkg.biv_debug('Escalated SR From List :'||x_from_list2,
262 'BIV_CUSTOMER_BACKLOG');
263 commit;
264 end if;
265 -- Setting order by
266 if biv_core_pkg.g_srt_by is null then
267 x_order_by:=4;
268 else
269 x_order_by:=to_number(nvl(biv_core_pkg.g_srt_by,0))+2;
270 end if;
271
272 x_display:=to_number(nvl(biv_core_pkg.g_disp,0))+1;
273
274 -- q2_str : unowned_sr q3_str :escalated_sr , q4_str : total_backlog , q5_str : severity_backlog,
275
276 -- Building Sql to populate the BIV_TMP_RT1 table
277 if (x_order_by=4)then
278 x_sql_sttmnt:='select ''X'',:x_session, sr.customer_id,count(1)'
279 ||x_from_list||x_where_clause||'
280 and (nvl(sr.resource_type,''X'') <>''RS_EMPLOYEE'' or sr.incident_owner_id is null )
281 and nvl(stat.close_flag,''N'') <> ''Y''
282 group by sr.customer_id';
283
284 elsif (x_order_by=5) then
285 x_sql_sttmnt:=' select ''X'',:x_session,sr.customer_id,count(1)'
286 ||x_from_list2||x_where_clause2||'
287 and nvl(stat.close_flag,''N'') <> ''Y''
288 group by sr.customer_id ';
289
290 elsif (x_order_by=6) then
291 x_sql_sttmnt:='select ''X'',:x_session,sr.customer_id,count(1)'
292 ||x_from_list||x_where_clause||'
293 and nvl(stat.close_flag,''N'') <> ''Y''
294 group by sr.customer_id';
295
296 elsif (x_order_by=7 ) then
297 biv_core_pkg.g_sev_cnt:=0;
298 x_sql_sttmnt:=' select ''X'',:x_session,sr.customer_id,count(1)'
299 ||x_from_list1||x_where_clause1||'
300 and nvl( stat.close_flag,''N'') <>''Y''
301 group by sr.customer_id ';
302
303 end if;
304
305 x_sql_sttmnt:='insert into biv_tmp_rt1 rep(report_code,session_id, ID,col2)(select * from ( '
306 ||x_sql_sttmnt||' order by 4 desc ) where rownum < :x_display )';
307
308
309
310 if (l_debug = 'Y') then
311 biv_core_pkg.biv_debug('FIRST SQL STATEMENT ORDER BY'||
312 TO_CHAR(X_ORDER_BY)||':'||x_sql_sttmnt,'BIV_CUSTOMER_BACKLOG');
313 commit;
314 end if;
315
316 x_cur:=dbms_sql.open_cursor;
317 dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
318 biv_core_pkg.bind_all_variables(x_cur);
319 if x_order_by=7 then
320 dbms_sql.bind_variable(x_cur,':x_severity_id',x_severity_id);
321 end if;
322 dbms_sql.bind_variable(x_cur,':x_display',x_display);
323 dbms_sql.bind_variable(x_cur,':x_session',x_session);
324 x_dummy:=dbms_sql.execute(x_cur);
325 dbms_sql.close_cursor(x_cur);
326 -- setting the customer id parameter to null in the parameter list
327
328
329 biv_core_pkg.g_cust_id_cnt:=0;
330 biv_core_pkg.g_sev_cnt:=x_sev_count;
331 biv_core_pkg.g_sev_cnt:=0;
332 -- Change for Bug 3386946
333 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
334 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
335 x_from_list1:=x_from_list;
336 x_where_clause1:= x_where_clause ||' and sr.incident_status_id=stat.incident_status_id
337 and sr.incident_severity_id=:x_severity_id ' ;
338
339
340 biv_core_pkg.g_sev_cnt:=x_sev_count;
341 if (l_debug = 'Y') then
342 biv_core_pkg.biv_debug('SEVERITY Count :'||to_char(x_sev_count),
343 'BIV_CUSTOMER_BACKLOG');
344 end if;
345 -- Change for Bug 3386946
346 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
347 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
348 x_where_clause:= x_where_clause ||' and sr.incident_status_id=stat.incident_status_id ' ;
349
350 x_from_list :=x_from_list||', biv_tmp_rt1 rep ' ;
351 x_from_list1 :=x_from_list1||', biv_tmp_rt1 rep ' ;
352
353 if (l_debug = 'Y') then
354 biv_core_pkg.biv_debug('Rebuild Severity Query Where Clause :'||
355 x_where_clause1,'BIV_CUSTOMER_BACKLOG');
356 biv_core_pkg.biv_debug('Rebuild Severity Query From List :'||x_from_list1,
357 'BIV_CUSTOMER_BACKLOG');
358 biv_core_pkg.biv_debug('Rebuild OTher Where Clause :'||x_where_clause,
359 'BIV_CUSTOMER_BACKLOG');
360 biv_core_pkg.biv_debug('Rebuild Other From List :'||x_from_list,
361 'BIV_CUSTOMER_BACKLOG');
362 commit;
363 end if;
364
365 if (instr(upper(x_from_list),'JTF_TASKS_B') = 0) then
366 x_from_list2 := x_from_list || ',
367 jtf_tasks_b task,
368 jtf_task_references_b ref';
369 else
370 x_from_list2 := x_from_list;
371 end if;
372
373 x_where_clause2:= x_where_clause || ' and sr.incident_id = ref.object_id
374 and ref.object_type_code = ''SR''
375 and ref.reference_code = ''ESC''
376 and ref.task_id = task.task_id
377 and task.task_type_id=22 and escalation_level is not null ' ;
378
379 if (l_debug = 'Y') then
380 biv_core_pkg.biv_debug('Rebuild ESR Query Where Clause :'||x_where_clause2,
381 'BIV_CUSTOMER_BACKLOG');
382 biv_core_pkg.biv_debug('Rebuild ESR Query From List :'||x_from_list2,
383 'BIV_CUSTOMER_BACKLOG');
384 commit;
385 end if;
386
387 q3_str:='select sr.customer_id a,count(1) b,0 c,0 d,0 e '
388 ||x_from_list||x_where_clause||'
389 and (nvl(sr.resource_type,''X'') <>''RS_EMPLOYEE'' or sr.incident_owner_id is null)
390 and nvl(stat.close_flag,''N'') <> ''Y''
391 and sr.customer_id=rep.ID
392 and rep.session_id=:x_session group by sr.customer_id';
393 q4_str:=' select sr.customer_id a,0 b,count(1)c,0 d,0 e '
394 ||x_from_list2||x_where_clause2||'
395 and sr.customer_id=rep.ID
396 and nvl(stat.close_flag,''N'') <> ''Y''
397 and rep.session_id=:x_session group by sr.customer_id ' ;
398 q5_str:=' select sr.customer_id a, 0 b,0 c,count(1) d, 0 e '
399 ||x_from_list||x_where_clause||'
400 and nvl(stat.close_flag,''N'') <> ''Y''
401 and sr.customer_id=rep.ID
402 and rep.session_id=:x_session group by sr.customer_id' ;
403 q6_str:='select sr.customer_id a,0 b, 0 c, 0 d,count(1) e '
404 ||x_from_list1||x_where_clause1||'
405 and nvl( stat.close_flag,''N'') <>''Y''
406 and sr.customer_id=rep.ID
407 and rep.session_id=:x_session group by sr.customer_id ';
408
409 if (x_order_by=4)then
410 q3_str:='select ID a,to_number(col2) b ,0 c,0 d,0 e from biv_tmp_rt1 where report_code=''X''
411 and session_id=:x_session';
412 elsif (x_order_by=5) then
413 q4_str:='select ID a,0 b ,to_number(col2) c,0 d,0 e from biv_tmp_rt1 where report_code=''X''
414 and session_id=:x_session ';
415 elsif (x_order_by=6) then
416 q5_str:='select ID a,0 b ,0 c,to_number(col2) d,0 e from biv_tmp_rt1 where report_code=''X''
417 and session_id=:x_session ';
418 elsif (x_order_by=7 ) then
419 q6_str:='select ID a,0 b ,0 c,0 d,to_number(col2) e from biv_tmp_rt1 where report_code=''X''
420 and session_id=:x_session ';
421 end if;
422
423
424 x_sql_sttmnt:='insert into biv_tmp_rt1 ( report_code,rowno,session_id,ID,col4,col6,col8,col10)
425 (select ''BIV_CUSTOMER_BACKLOG'',rownum,ses,ID,col4,col6,col8,col10 from (
426 (select ''BIV_CUSTOMER_BACKLOG'',:x_session ses, a ID , sum(b) col4, sum(c) col6, sum(d) col8, sum(e) col10 from ('
427 ||q3_str||' union all '||q4_str||' union all '||q5_str||' union all '||q6_str||
428 ') group by a ) order by '||x_order_by||' desc ))';
429
430
431 if (l_debug = 'Y') then
432 biv_core_pkg.biv_debug(x_sql_sttmnt,'BIV_CUSTOMER_BACKLOG');
433 commit;
434 end if;
435
436 x_cur:=dbms_sql.open_cursor;
437 dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
438 biv_core_pkg.bind_all_variables(x_cur);
439 if x_order_by <> 7 then
440 dbms_sql.bind_variable(x_cur,':x_severity_id',x_severity_id);
441 end if;
442 --dbms_sql.bind_variable(x_cur,':x_display',x_display);
443 dbms_sql.bind_variable(x_cur,':x_session',x_session);
444 x_dummy:=dbms_sql.execute(x_cur);
445 dbms_sql.close_cursor(x_cur);
446 -- x_cur:=dbms_sql.open_cursor;
447
448 execute immediate 'delete from biv_tmp_rt1 where report_code=''X'' and session_id=:x_session'
449 using x_session;
450
451 biv_core_pkg.update_description('P_CUST_ID','ID','col2','BIV_TMP_RT1');
452
453 biv_core_pkg.reset_view_by_param;
454 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
455 biv_core_pkg.reconstruct_param_str;
456 l_new_param_str := l_new_param_str ||'P_CUST_ID' ||
457 biv_core_pkg.g_value_sep ;
458 -- biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
459
460 l_new_param_str1 := 'BIV_RT_CUS_BLOG_DD' ||biv_core_pkg.g_param_sep ||
461 biv_core_pkg.reconstruct_param_str;
462 l_new_param_str1 := l_new_param_str1 ||'P_CUST_ID' ||biv_core_pkg.g_value_sep ;
463 -- biv_core_pkg.g_value_sep || 'BIV_RT_CUS_BLOG_DD' ||
464
465 update biv_tmp_rt1 rep
466 set col1=l_new_param_str1||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
467 biv_core_pkg.g_param_sep,
468 col3=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
469 biv_core_pkg.g_param_sep ||'P_UNOWN'|| biv_core_pkg.g_value_sep ||
470 'Y'||biv_core_pkg.g_param_sep||'P_BLOG'||
471 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
472 col5=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
473 biv_core_pkg.g_param_sep ||'P_ESC_SR'|| biv_core_pkg.g_value_sep ||
474 'Y'||biv_core_pkg.g_param_sep||'P_BLOG'||
475 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
476 col7=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
477 biv_core_pkg.g_param_sep ||'P_BLOG'|| biv_core_pkg.g_value_sep ||
478 'Y'||biv_core_pkg.g_param_sep,
479 col9=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
480 biv_core_pkg.g_param_sep ||'P_SEV'|| biv_core_pkg.g_value_sep ||
481 to_char(x_severity_id)||biv_core_pkg.g_param_sep||'P_BLOG'||
482 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
483 creation_date = sysdate,
484 col20 = 'INDV_ROW';
485 select count(1) into l_ttl_recs
486 from biv_tmp_rt1
487 where session_id = x_session
488 and report_code = 'BIV_CUSTOMER_BACKLOG';
489 if (nvl(l_ttl_recs,0) between 2 and biv_core_pkg.g_disp-1) then
490 if (l_debug = 'Y') then
491 biv_core_pkg.biv_debug('Inserting totol row','BIV_CUSTOMER_BACKLOG');
492 end if;
493 insert into biv_tmp_rt1(report_code,session_id, rowno,col4,col6,col8,
494 col10,col20)
495 select 'BIV_CUSTOMER_BACKLOG',x_session,max(rowno)+1,
496 sum(col4), sum(col6),
497 sum(col8), sum(col10),'TTL_ROW'
498 from biv_tmp_rt1
499 where session_id = x_session
500 and report_code = 'BIV_CUSTOMER_BACKLOG'
501 and col20 = 'INDV_ROW';
502 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
503 biv_core_pkg.reconstruct_param_str;
504
505 l_new_param_str1 := 'BIV_RT_CUS_BLOG_DD' ||biv_core_pkg.g_param_sep ||
506 biv_core_pkg.reconstruct_param_str;
507 if (l_debug = 'Y') then
508 biv_core_pkg.biv_debug('Updating hyper links in total row',
509 'BIV_CUSTOMER_BACKLOG');
510 end if;
511 l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
512 update biv_tmp_rt1 rep
513 set col1=l_new_param_str1 ,
514 col2=l_ttl_meaning,
515 col3=l_new_param_str||
516 'P_UNOWN'|| biv_core_pkg.g_value_sep ||
517 'Y'||biv_core_pkg.g_param_sep||'P_BLOG'||
518 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
519 col5=l_new_param_str||
520 'P_ESC_SR'|| biv_core_pkg.g_value_sep ||
521 'Y'||biv_core_pkg.g_param_sep||'P_BLOG'||
522 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
523 col7=l_new_param_str||
524 'P_BLOG'|| biv_core_pkg.g_value_sep ||
525 'Y'||biv_core_pkg.g_param_sep,
526 col9=l_new_param_str||
527 'P_SEV'|| biv_core_pkg.g_value_sep ||
528 to_char(x_severity_id)||biv_core_pkg.g_param_sep||'P_BLOG'||
529 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
530 creation_date = sysdate
531 where col20 = 'TTL_ROW';
532 end if;
533
534 exception
535 when others then
536 if (l_debug = 'Y') then
537 biv_core_pkg.biv_debug('Error:'||substr(sqlerrm,1,200),
538 'BIV_CUSTOMER_BACKLOG');
539 end if;
540
541 end; -- procedure customer_backlog
542
543
544 procedure escalated_sr_backlog ( p_param_str in varchar2 ) is
545 x_where_clause1 varchar2(2000);
546 x_where_clause2 varchar2(2000);
547 x_where_clause varchar2(2000);
548 x_from_list varchar2(1000);
549 x_from_list1 varchar2(1000);
550 x_from_list2 varchar2(1000);
551 x_sql_sttmnt long;
552 x_cur pls_integer;
553 x_dummy pls_integer;
554 x_order_by number;
555 x_display number;
556 x_severity_id number;
557 x_stat_1 number;
558 x_stat_2 number;
559 x_stat_3 number;
560 dd_param_str varchar2(2000);
561 l_new_param_str varchar2(200);
562 x_session number;
563 q3_str varchar2(2000);
564 q4_str varchar2(4000);
565 x_sev_count number;
566 l_ttl_recs number;
567 l_ttl_meaning fnd_lookups.meaning % type;
568 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
569
570 begin
571 x_session:=biv_core_pkg.get_session_id;
572 biv_core_pkg.clean_dcf_table('BIV_TMP_RT1');
573
574
575 x_severity_id:=fnd_profile.value('BIV:INC_SEVERITY_1');
576 x_stat_1:=fnd_profile.value('BIV:INC_STATUS_1');
577 x_stat_2:=fnd_profile.value('BIV:INC_STATUS_2');
578 x_stat_3:=fnd_profile.value('BIV:INC_STATUS_3');
579
580 biv_core_pkg.g_report_id:='BIV_RT_ESC_SR';
581 biv_core_pkg.get_report_parameters(p_param_str);
582 biv_core_pkg.g_report_type:='RT';
583
584 if biv_core_pkg.g_view_by is null then
585 biv_core_pkg.g_base_column:='sr.inventory_item_id ';
586 end if;
587
588 x_sev_count:=biv_core_pkg.g_sev_cnt;
589
590 -- Building the from list and where clause for severity query
591 -- Change for Bug 3386946
592 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
593 biv_core_pkg.g_sev_cnt:=0;
594 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
595 x_from_list1:=x_from_list;
596 x_where_clause1:= x_where_clause || ' and sr.incident_status_id = stat.incident_status_id
597 and nvl(stat.close_flag,''N'') <> ''Y'' ';
598 -- resseting the severity parameter and building the where clause for escalated queries...
599 biv_core_pkg.g_sev_cnt:=x_sev_count ;
600 -- Change for bug 3386946
601 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
602 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
603 x_where_clause:= x_where_clause || ' and sr.incident_status_id = stat.incident_status_id
604 and nvl(stat.close_flag,''N'') <> ''Y'' ';
605
606
607 -- Building the from list and where clause in Escalated Request case
608 if (instr(upper(x_from_list),'JTF_TASKS_B') = 0) then
609 x_from_list2 := x_from_list || ',
610 jtf_tasks_b task,
611 jtf_task_references_b ref';
612 else
613 x_from_list2 := x_from_list;
614 end if;
615 -- x_from_list2:=x_from_list;
616 x_where_clause2:= x_where_clause || ' and sr.incident_id = ref.object_id
617 and ref.object_type_code = ''SR''
618 and ref.reference_code = ''ESC''
619 and ref.task_id = task.task_id
620 and task.task_type_id=22 and escalation_level is not null ';
621
622 if biv_core_pkg.g_srt_by is null then
623 x_order_by:=4;
624 else
625 x_order_by:=to_number(nvl(biv_core_pkg.g_srt_by,0))+2;
626 end if;
627 x_display:=to_number(nvl(biv_core_pkg.g_disp,0))+1;
628
629
630 if (x_order_by=4)then /* Sort by Severity */
631 x_sql_sttmnt:='select ''X'',:x_session ,'|| biv_core_pkg.g_base_column||' , count(1) '
632 ||x_from_list1||x_where_clause1||'
633 and sr.incident_severity_id=:x_severity_id
634 group by '|| biv_core_pkg.g_base_column ;
635 elsif (x_order_by=5) then /* Sort by Escalation */
636 x_sql_sttmnt:=' select ''X'' ,:x_session ,'|| biv_core_pkg.g_base_column||' , count(1) '
637 ||x_from_list2||x_where_clause2||'
638 group by '|| biv_core_pkg.g_base_column ;
639
640 end if;
641
642
643
644 x_sql_sttmnt:='insert into biv_tmp_rt1 rep(report_code,session_id, ID,col2)(select * from ( '
645 ||x_sql_sttmnt||' order by 4 desc ) where rownum < :x_display )';
646
647 if (l_debug = 'Y') then
648 biv_core_pkg.biv_debug(x_sql_sttmnt,'BIV_ESCALATED_SR');
649 commit;
650 end if;
651
652 x_cur:=dbms_sql.open_cursor;
653 dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
654 biv_core_pkg.bind_all_variables(x_cur);
655 if x_order_by=4 then
656 dbms_sql.bind_variable(x_cur,':x_severity_id',x_severity_id);
657 end if;
658 dbms_sql.bind_variable(x_cur,':x_display',x_display);
659 dbms_sql.bind_variable(x_cur,':x_session',x_session);
660 x_dummy:=dbms_sql.execute(x_cur);
661 dbms_sql.close_cursor(x_cur);
662
663
664 -- Fix for bug 3461261
665 -- x_from_list1:=x_from_list1||',biv_tmp_rt1 rep ' ;
666
667 if (x_order_by=5) then /* Sort by Escalation */
668 x_from_list2:=x_from_list2||', biv_tmp_rt1 rep ' ;
669 end if;
670
671
672 -- Fix for bug 3461261
673 -- x_where_clause1:=x_where_clause1|| ' and nvl('||biv_core_pkg.g_base_column||',-99)=nvl(rep.ID,-99) and rep.session_id=:x_session and rep.report_code=''X'' ' ;
674
675 if (x_order_by=5) then /* Sort by Escalation */
676 x_where_clause2:=x_where_clause2|| ' and nvl('||biv_core_pkg.g_base_column||',-99)=nvl(rep.ID,-99) and rep.session_id=:x_session and rep.report_code=''X'' ';
677 end if;
678
679
680 if x_order_by=4 then /* Sort by Severity, overwrites previous values sort by Escalation */
681 q3_str:='(select ''BIV_ESCALATED_SR'',:x_session, ID a ,to_number(col2) b, 0 c ,0 d,0 e,0 f,0 g
682 from biv_tmp_rt1 where report_code=''X'' and session_id=:x_session )';
683 else
684 /* Used when sort by Escalation (first part of union all) */
685 q3_str:= '(select ''BIV_ESCALATED_SR'',:x_session ,'|| biv_core_pkg.g_base_column||' a,
686 count(1) b, 0 c ,0 d,0 e,0 f,0 g '
687 ||x_from_list1||x_where_clause1|| ' and sr.incident_severity_id=:x_severity_id
688 group by '|| biv_core_pkg.g_base_column||')';
689 end if;
690
691
692 /* Always used as the second union all part */
693 q4_str:= '(select ''BIV_ESCALATED_SR'',:x_session ,'|| biv_core_pkg.g_base_column||' a, 0 b ,
694 count(1) c ,
695 sum(decode(sr.incident_status_id,:x_stat_1,1,0)) d ,
696 sum(decode(sr.incident_status_id,:x_stat_2,1,0)) e ,
697 sum(decode(sr.incident_status_id,:x_stat_3,1,0)) f,
698 (count(1)-(sum(decode(sr.incident_status_id,:x_stat_1,1,0))+sum(decode(sr.incident_status_id,:x_stat_2,1,0))+
699 sum(decode(sr.incident_status_id,:x_stat_3,1,0)))) g '
700 ||x_from_list2||x_where_clause2||
701 ' group by '|| biv_core_pkg.g_base_column||')';
702
703
704 x_sql_sttmnt:= '(select ''BIV_ESCALATED_SR'',rownum ,ses,ID,col4,col6,col8,col10,col12,col14
705 from (select ''BIV_ESCALATED_SR'',:x_session ses,a ID,sum(b) col4,sum(c) col6,sum(d) col8,
706 sum(e) col10,sum(f) col12,sum(g) col14 from ('||q3_str ||'
707 union all '||q4_str||'
708 ) group by a
709 order by '||x_order_by ||' desc ) where rownum < :x_display)' ;
710
711 x_sql_sttmnt:='insert into biv_tmp_rt1 rep (report_code,rowno,session_id,ID,col4,col6,col8,col10,col12,col14)
712 '||x_sql_sttmnt||' ' ;
713
714 if (l_debug = 'Y') then
715 biv_core_pkg.biv_debug(x_sql_sttmnt,'BIV_ESCALATED_SR');
716 commit;
717 end if;
718
719 x_cur:=dbms_sql.open_cursor;
720 dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
721 biv_core_pkg.bind_all_variables(x_cur);
722 if x_order_by <> 4 then
723 dbms_sql.bind_variable(x_cur,':x_severity_id',x_severity_id);
724 end if;
725 dbms_sql.bind_variable(x_cur,':x_stat_1',x_stat_1);
726 dbms_sql.bind_variable(x_cur,':x_stat_2',x_stat_2);
727 dbms_sql.bind_variable(x_cur,':x_stat_3',x_stat_3);
728 dbms_sql.bind_variable(x_cur,':x_display',x_display);
729 dbms_sql.bind_variable(x_cur,':x_session',x_session);
730 x_dummy:=dbms_sql.execute(x_cur);
731 dbms_sql.close_cursor(x_cur);
732
733 execute immediate 'delete from biv_tmp_rt1 where report_code=''X'' and session_id=:x_session'
734 using x_session;
735
736
737 biv_core_pkg.update_base_col_desc('BIV_TMP_RT1');
738
739
740 dd_param_str:=biv_core_pkg.param_for_base_col;
741 --dd_param_str:='P_PRD_ID';
742 biv_core_pkg.reset_view_by_param;
743 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
744 biv_core_pkg.reconstruct_param_str;
745 l_new_param_str := l_new_param_str ||dd_param_str ||biv_core_pkg.g_value_sep ;
746 -- 'jtfBinId' || biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
747
748 update biv_tmp_rt1 rep
749 set col3=l_new_param_str||
750 nvl(to_char(rep.ID),biv_core_pkg.g_null)||
751 biv_core_pkg.g_param_sep ||'P_SEV'||
752 biv_core_pkg.g_value_sep ||to_char(x_severity_id)||
753 biv_core_pkg.g_param_sep||'P_BLOG'||
754 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
755 col5=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
756 biv_core_pkg.g_param_sep ||'P_ESC_SR'||
757 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep||
758 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y'||
759 biv_core_pkg.g_param_sep,
760 col7=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
761 biv_core_pkg.g_param_sep ||'P_ESC_SR'||
762 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep||
763 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_1) ||
764 biv_core_pkg.g_param_sep ||
765 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y'||
766 biv_core_pkg.g_param_sep,
767 col9=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
768 biv_core_pkg.g_param_sep ||'P_ESC_SR'||
769 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep||
770 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_2) ||
771 biv_core_pkg.g_param_sep ||
772 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y'||
773 biv_core_pkg.g_param_sep,
774 col11=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
775 biv_core_pkg.g_param_sep ||'P_ESC_SR'||
776 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep||
777 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_3) ||
778 biv_core_pkg.g_param_sep ||
779 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y'||
780 biv_core_pkg.g_param_sep,
781 col13=l_new_param_str||nvl(to_char(rep.ID),biv_core_pkg.g_null)||
782 biv_core_pkg.g_param_sep ||'P_ESC_SR'||
783 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep||
784 'P_OTHER_BLOG'||biv_core_pkg.g_value_sep ||'Y' ||
785 biv_core_pkg.g_param_sep ||
786 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y'||
787 biv_core_pkg.g_param_sep,
788 col20='INDV_ROW';
789 --
790 -- Generate total row
791 --
792 commit;
793 select count(1) into l_ttl_recs
794 from biv_tmp_rt1
795 where session_id = x_session
796 and report_code = 'BIV_ESCALATED_SR';
797 l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
798 if (nvl(l_ttl_recs,0) between 2 and biv_core_pkg.g_disp-1) then
799 insert into biv_tmp_rt1(report_code,rowno,col2,col4,col6,col8,col10,
800 col12, col14, session_id,col20)
801 select report_code, max(rowno)+1, l_ttl_meaning, sum(col4), sum(col6),
802 sum(col8), sum(col10), sum(col12), sum(col14), session_id,'TTL_ROW'
803 from biv_tmp_rt1
804 where report_code = 'BIV_ESCALATED_SR'
805 and session_id = x_session
806 and col20 = 'INDV_ROW'
807 group by report_code, session_id;
808 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
809 biv_core_pkg.reconstruct_param_str;
810 update biv_tmp_rt1 rep
811 set col3=l_new_param_str||'P_SEV'|| biv_core_pkg.g_value_sep ||
812 to_char(x_severity_id)||biv_core_pkg.g_param_sep||'P_BLOG'||
813 biv_core_pkg.g_value_sep || 'Y'||biv_core_pkg.g_param_sep,
814 col5=l_new_param_str||'P_ESC_SR'|| biv_core_pkg.g_value_sep || 'Y'||
815 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
816 'Y'||biv_core_pkg.g_param_sep,
817 col7=l_new_param_str||'P_ESC_SR'|| biv_core_pkg.g_value_sep || 'Y'||
818 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
819 'Y'||biv_core_pkg.g_param_sep ||
820 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_1) ||
821 biv_core_pkg.g_param_sep,
822 col9=l_new_param_str||'P_ESC_SR'|| biv_core_pkg.g_value_sep || 'Y'||
823 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
824 'Y'||biv_core_pkg.g_param_sep ||
825 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_2) ||
826 biv_core_pkg.g_param_sep,
827 col11=l_new_param_str||'P_ESC_SR'|| biv_core_pkg.g_value_sep || 'Y'||
828 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
829 'Y'||biv_core_pkg.g_param_sep ||
830 'P_STS_ID'||biv_core_pkg.g_value_sep ||to_char(x_stat_3) ||
831 biv_core_pkg.g_param_sep,
832 col13=l_new_param_str||'P_ESC_SR'|| biv_core_pkg.g_value_sep || 'Y'||
833 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
834 'Y'||biv_core_pkg.g_param_sep ||
835 'P_OTHER_BLOG'||biv_core_pkg.g_value_sep ||'Y' ||
836 biv_core_pkg.g_param_sep
837 where report_code = 'BIV_ESCALATED_SR'
838 and session_id = x_session
839 and col20 = 'TTL_ROW';
840 end if;
841
842 exception
843 when others then
844 if (l_debug='Y') then
845 biv_core_pkg.biv_debug('Error:'||substr(sqlerrm,1,200),
846 biv_core_pkg.g_report_id);
847 end if;
848
849
850 end; -- procedure escalated sr backlog
851
852 procedure customer_backlog_dd ( p_param_str in varchar2 ) is
853
854 x_where_clause1 varchar2(2000);
855 x_where_clause2 varchar2(2000);
856 x_where_clause varchar2(2000);
857 x_from_list varchar2(1000);
858 x_from_list1 varchar2(1000);
859 x_from_list2 varchar2(1000);
860 x_sql_sttmnt varchar2(4000);
861 x_cur pls_integer;
862 x_dummy pls_integer;
863 x_severity_id number;
864 l_cust_id number;
865 l_new_param_str varchar2(200);
866 x_session varchar2(50);
867 x_sev_count number;
868 l_ttl_recs number;
869 l_ttl_meaning fnd_lookups.meaning % type;
870 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
871
872 begin
873 x_session:=biv_core_pkg.get_session_id;
874
875 biv_core_pkg.clean_dcf_table('BIV_TMP_RT1');
876
877 x_severity_id:=fnd_profile.value('BIV:INC_SEVERITY_1');
878
879 biv_core_pkg.get_report_parameters(p_param_str);
880 biv_core_pkg.g_report_type:='RT';
881 if (l_debug = 'Y') then
882 biv_core_pkg.biv_debug('Param :'||p_param_str,'BIV_CUSTOMER_BACKLOG');
883 commit;
884 end if;
885
886 x_sev_count:=biv_core_pkg.g_sev_cnt;
887 -- Change for Bug 3386946
888 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
889
890 -- Buliding from list and where clause for severity query
891 biv_core_pkg.g_sev_cnt:=0;
892 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
893 x_where_clause1:= x_where_clause||' and sr.incident_status_id=stat.incident_status_id
894 and sr.incident_severity_id=:x_severity_id ' ;
895 x_from_list1:=x_from_list;
896 if (l_debug = 'Y') then
897 biv_core_pkg.biv_debug('Severity Query Where Clause :'||x_where_clause1,
898 'BIV_CUSTOMER_BACKLOG');
899 biv_core_pkg.biv_debug('Severity Query From List :'||x_from_list1,
900 'BIV_CUSTOMER_BACKLOG');
901 commit;
902 end if;
903 -- Restting severity parameter and building where clause and from list
904 biv_core_pkg.g_sev_cnt:=x_sev_count;
905 -- Change for Bug 3386946
906 x_from_list:='from cs_incidents_b_sec sr,cs_incident_statuses_b stat ';
907 biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
908 x_where_clause:= x_where_clause||' and sr.incident_status_id=stat.incident_status_id ';
909 if (l_debug = 'Y') then
910 biv_core_pkg.biv_debug('Others Where Clause :'||x_where_clause,
911 'BIV_CUSTOMER_BACKLOG');
912 biv_core_pkg.biv_debug('Others From List :'||x_from_list,
913 'BIV_CUSTOMER_BACKLOG');
914 commit;
915 end if;
916
917 -- Building from list and where clause in Escalated Request case
918 if (instr(upper(x_from_list),'JTF_TASKS_B') = 0) then
919 x_from_list2 := x_from_list || ',
920 jtf_tasks_b task,
921 jtf_task_references_b ref';
922 else
923 x_from_list2 := x_from_list;
924 end if;
925 x_where_clause2:= x_where_clause || ' and sr.incident_id = ref.object_id
926 and ref.object_type_code = ''SR''
927 and ref.reference_code = ''ESC''
928 and ref.task_id = task.task_id
929 and task.task_type_id=22 and escalation_level is not null ' ;
930
931 if (l_debug = 'Y') then
932 biv_core_pkg.biv_debug('Escalated Sr Where Clause :'||x_where_clause2,
933 'BIV_CUSTOMER_BACKLOG');
934 biv_core_pkg.biv_debug('Escalated SR From List :'||x_from_list2,
935 'BIV_CUSTOMER_BACKLOG');
936 commit;
937 end if;
938
939 x_sql_sttmnt:= '(SELECT ''BIV_CUSTOMER_BACKLOG'',rownum, ses, A ,col4,col6,col8,col10 FROM
940 (select ''BIV_CUSTOMER_BACKLOG'',:x_session ses, A ,sum(B) col4,sum(C)col6,sum(D)col8,sum(E) col10
941 from (
942 (select ''BIV_CUSTOMER_BACKLOG'' ,sr.contract_number A ,count(1) B ,0 C,0 D,0 E '
943 ||x_from_list||x_where_clause||'
944 and (nvl(sr.resource_type,''X'') <>''RS_EMPLOYEE'' or sr.incident_owner_id is null)
945 and nvl(stat.close_flag,''N'') <> ''Y'' group by sr.contract_number )
946 union all
947 (select ''BIV_CUSTOMER_BACKLOG'' ,sr.contract_number A , 0 B ,0 C,count(1) D, 0 E '
948 ||x_from_list||x_where_clause||'
949 and nvl(stat.close_flag,''N'') <> ''Y'' group by sr.contract_number)
950 union all
951 (select ''BIV_CUSTOMER_BACKLOG'' ,sr.contract_number A ,0 B ,0 C, 0 D,count(1) E '
952 ||x_from_list1||x_where_clause1||'
953 and nvl( stat.close_flag,''N'') <>''Y'' group by sr.contract_number)
954 union all
955 (select ''BIV_CUSTOMER_BACKLOG'' ,sr.contract_number A ,0 B ,count(1) C ,0 D ,0 E '
956 ||x_from_list2||x_where_clause2|| ' and nvl( stat.close_flag,''N'') <>''Y''
957 group by sr.contract_number)) group by A
958 ORDER BY 3 ))';
959
960
961 x_sql_sttmnt:='insert into biv_tmp_rt1(report_code,rowno,session_id,col2,col4,col6,col8,col10)
962 '||x_sql_sttmnt||' ' ;
963
964 if (l_debug = 'Y') then
965 biv_core_pkg.biv_debug(x_sql_sttmnt,'BIV_CUSTOMER_BACKLOG');
966 commit;
967 end if;
968
969 x_cur:=dbms_sql.open_cursor;
970 dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
971 biv_core_pkg.bind_all_variables(x_cur);
972 dbms_sql.bind_variable(x_cur,':x_severity_id',x_severity_id);
973 dbms_sql.bind_variable(x_cur,':x_session',x_session);
974 x_dummy:=dbms_sql.execute(x_cur);
975 dbms_sql.close_cursor(x_cur);
976
977 update biv_tmp_rt1
978 set ID=biv_core_pkg.g_cust_id(1);
979
980
981 biv_core_pkg.reset_view_by_param;
982 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
983 biv_core_pkg.reconstruct_param_str;
984 l_new_param_str := l_new_param_str ||'P_CUST_ID' ||biv_core_pkg.g_value_sep ;
985
986 if (l_debug = 'Y') then
987 biv_core_pkg.biv_debug(biv_core_pkg.reconstruct_param_str,
988 'BIV_CUSTOMER_BACKLOG');
989 end if;
990 -- 'jtfBinId' ||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
991 -- may be cust_id in update in not needed
992 update biv_tmp_rt1 d
993 set col3=l_new_param_str||nvl(to_char(d.ID),biv_core_pkg.g_null)||
994 biv_core_pkg.g_param_sep ||'P_UNOWN'|| biv_core_pkg.g_value_sep ||
995 'Y'||biv_core_pkg.g_param_sep ||'P_CNTR_ID' ||
996 biv_core_pkg.g_value_sep||nvl(d.col2,biv_core_pkg.g_null)||
997 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
998 'Y'||biv_core_pkg.g_param_sep,
999 col5=l_new_param_str||nvl(to_char(d.ID),biv_core_pkg.g_null)||
1000 biv_core_pkg.g_param_sep ||'P_ESC_SR'|| biv_core_pkg.g_value_sep ||
1001 'Y'||biv_core_pkg.g_param_sep ||'P_CNTR_ID' ||
1002 biv_core_pkg.g_value_sep||nvl(d.col2,biv_core_pkg.g_null)||
1003 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1004 'Y'||biv_core_pkg.g_param_sep,
1005 col7=l_new_param_str||nvl(to_char(d.ID),biv_core_pkg.g_null)||
1006 biv_core_pkg.g_param_sep ||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1007 'Y'||biv_core_pkg.g_param_sep ||'P_CNTR_ID' ||
1008 biv_core_pkg.g_value_sep||nvl(d.col2,biv_core_pkg.g_null)||
1009 biv_core_pkg.g_param_sep,
1010 col9=l_new_param_str||nvl(to_char(d.ID),biv_core_pkg.g_null)||
1011 biv_core_pkg.g_param_sep ||'P_SEV'|| biv_core_pkg.g_value_sep ||
1012 to_char(x_severity_id)||biv_core_pkg.g_param_sep ||'P_CNTR_ID' ||
1013 biv_core_pkg.g_value_sep||nvl(d.col2,biv_core_pkg.g_null)||
1014 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1015 'Y'||biv_core_pkg.g_param_sep,
1016 col20 = 'INDV_ROW',
1017 creation_date = sysdate;
1018
1019 /*** 7/30/2 not needed as this drill does not have g_disp. it is to display all
1020 select count(1) into l_ttl_recs
1021 from biv_tmp_rt1
1022 where session_id = x_session
1023 and report_code = 'BIV_CUSTOMER_BACKLOG';
1024 *****/
1025
1026 insert into biv_tmp_rt1 ( report_code,session_id, rowno, col4, col6,
1027 col8, col10, col20)
1028 select 'BIV_CUSTOMER_BACKLOG', x_session, max(rowno)+1,sum(col4),
1029 sum(col6), sum(col8), sum(col10), 'TTL_ROW'
1030 from biv_tmp_rt1
1031 where session_id = x_session
1032 and col20 = 'INDV_ROW'
1033 and report_code = 'BIV_CUSTOMER_BACKLOG';
1034
1035 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
1036 biv_core_pkg.reconstruct_param_str;
1037 l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
1038 update biv_tmp_rt1 d
1039 set col3=l_new_param_str||
1040 'P_UNOWN'|| biv_core_pkg.g_value_sep ||
1041 'Y'||
1042 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1043 'Y'||biv_core_pkg.g_param_sep,
1044 col5=l_new_param_str||
1045 'P_ESC_SR'|| biv_core_pkg.g_value_sep ||
1046 'Y'||
1047 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1048 'Y'||biv_core_pkg.g_param_sep,
1049 col7=l_new_param_str||
1050 'P_BLOG'|| biv_core_pkg.g_value_sep ||
1051 'Y'||
1052 biv_core_pkg.g_param_sep,
1053 col9=l_new_param_str||
1054 'P_SEV'|| biv_core_pkg.g_value_sep ||
1055 to_char(x_severity_id)||
1056 biv_core_pkg.g_param_sep||'P_BLOG'|| biv_core_pkg.g_value_sep ||
1057 'Y'||biv_core_pkg.g_param_sep,
1058 col2=l_ttl_meaning,
1059 creation_date = sysdate
1060 where session_id = x_session
1061 and col20 = 'TTL_ROW'
1062 and report_code = 'BIV_CUSTOMER_BACKLOG';
1063
1064 exception
1065 when others then
1066 if (l_debug = 'Y') then
1067 biv_core_pkg.biv_debug('Error:'||substr(sqlerrm,1,200),
1068 'BIV_CUSTOMER_BACKLOG');
1069 end if;
1070 end; -- procedure customer_backlog_drill_down
1071
1072 -- enter further code below as specified in the package spec.
1073 end;