[Home] [Help]
PACKAGE BODY: APPS.BIV_CORE_PKG
Source
1 package body biv_core_pkg as
2 /* $Header: bivcoreb.pls 115.45 2004/04/06 06:20:10 nbhamidi ship $ */
3 function get_lookup_meaning(p_lookup_code varchar2) return varchar2 is
4 l_meaning fnd_lookups.meaning % type;
5 begin
6 select meaning into l_meaning from fnd_lookups
7 where lookup_type = 'BIV_LABELS'
8 and lookup_code = p_lookup_code;
9 return l_meaning;
10 exception
11 when others then return p_lookup_code;
12 end;
13 --------------------------------------------------------------------------
14 procedure get_parameter_values_all(p_param_values in out nocopy g_parameter_array,
15 p_total_values in out nocopy number,
16 p_param_str varchar2,
17 p_param_name varchar2) is
18 l_value_count number;
19 l_value_str varchar2(500);
20 i number;
21 begin
22 l_value_str := biv_core_pkg.get_parameter_value(p_param_str,
23 p_param_name,
24 g_param_sep,
25 g_value_sep);
26
27 /*
28 biv_core_pkg.biv_debug('Param:'||p_param_str,g_report_id);
29 biv_core_pkg.biv_debug( 'Values String for Param:'||p_param_name ||
30 '---->' || nvl(l_value_str,'NULL'),g_report_id);
31 commit;
32 */
33 if (nvl(l_value_str,'NOT_FOUND') <> 'NOT_FOUND' ) then
34 p_total_values := jtfb_dcf.get_multiselect_count(l_value_str,
35 g_multi_value_sep);
36 /***
37 biv_core_pkg.biv_debug('Total Value for :'||p_param_name ||
38 ' : ' ||to_char(p_total_values),g_report_id);
39 ****/
40 if (p_total_values > 100) then
41 --dbms_output.put_line('Two Many Values for :'|| p_param_name);
42 if (g_debug = 'Y') then
43 biv_core_pkg.biv_debug('Too many values for :'||p_param_name ||
44 ' : ' ||to_char(p_total_values),g_report_id);
45 end if;
46 else
47 for i in 1..p_total_values loop
48 p_param_values(i) := jtfb_dcf.get_multiselect_value(
49 l_value_str,
50 i,
51 g_multi_value_sep);
52 end loop;
53 end if;
54 else
55 p_total_values := 0;
56 end if;
57 end;
58 ------------------------------------------------------------------------------
59 /***********************
60 procedure get_parameter_values_all_73001(p_param_values in out nocopy g_parameter_array,
61 p_total_values in out nocopy number,
62 p_param_str varchar2,
63 p_param_name varchar2,
64 p_param_sep varchar2 default g_param_sep,
65 p_value_sep varchar2 default g_value_sep) as
66 i number;
67 x_start_pos number;
68 x_end_pos number;
69 x_param_value varchar2(80);
70 begin
71 i := 0;
72 p_total_values := 0;
73 x_start_pos := 1;
74 loop
75 x_param_value := get_parameter_value(p_param_str ,
76 p_param_name ,
77 x_end_pos ,
78 x_start_pos ,
79 p_param_sep ,
80 p_value_sep );
81 if ( x_param_value is null ) then exit; end if;
82 i := i + 1;
83 --dbms_output.put_line('From all proc, value:'|| x_param_value ||
84 -- to_char(x_end_pos));
85 p_param_values(i) := x_param_value;
86 x_start_pos := x_end_pos;
87 end loop;
88 p_total_values := i;
89 end;
90 ************************************/
91 procedure prt_parameter_values(p_param_values in out nocopy g_parameter_array,
92 p_total_values in out nocopy number) as
93 i number;
94 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
95 begin
96 i := 0;
97 loop
98 i := i + 1;
99 if ( i > p_total_values) then exit; end if;
100 /*
101 dbms_output.put_line('Parameter value at index:'||
102 to_char(i) || ' is:'||
103 p_param_values(i)
104 );
105 */
106 if (l_debug = 'Y') then
107 biv_core_pkg.biv_debug ('Parameter value at index:'||
108 to_char(i) || ' is:'||
109 p_param_values(i), g_report_id
110 );
111 end if;
112 end loop;
113 end;
114 procedure biv_debug(p_msg varchar2,
115 p_report varchar2) is
116 l_msg varchar2(4000);
117 q_msg varchar2(6000);
118 begin
119 --if (nvl(g_debug,'N') = 'Y' ) then
120 q_msg := replace(p_msg,g_local_chr,'<BR>');
121 l_msg := substr(q_msg,1,4000);
122 insert into biv_debug(report_id,message,creation_date,session_id,seq_no)
123 values(p_report,l_msg,sysdate,get_session_id, g_srl_no);
124 g_srl_no := g_srl_no + 1;
125 if (length(q_msg) > 4000) then
126 l_msg := substr(q_msg,4001,4000);
127 insert into biv_debug(report_id,message,creation_date,
128 session_id,seq_no)
129 values(p_report,l_msg,sysdate,get_session_id, g_srl_no);
130 g_srl_no := g_srl_no + 1;
131 end if;
132 if (length(q_msg) > 8000) then
133 l_msg := substr(q_msg,8001,4000);
134 insert into biv_debug(report_id,message,creation_date,
135 session_id,seq_no)
136 values(p_report,l_msg,sysdate,get_session_id, g_srl_no);
137 g_srl_no := g_srl_no + 1;
138 end if;
139 commit;
140 -- end if;
141 end biv_debug;
142 -------------------------
143 function get_parameter_value(p_param_str varchar2,
144 p_param_name varchar2,
145 p_param_end_pos in out nocopy number,
146 p_start_pos number /*default 1*/,
147 p_param_sep varchar2 /*default g_param_sep*/,
148 p_value_sep varchar2 /*default g_value_sep*/)
149 return varchar2 is
150 x_name_end number;
151 x_value_end number;
152 x_param_str_len number;
153 x_value_sep_len number;
154 x_param_sep_len number;
155 x_start_pos number;
156 x_param_name varchar2(80);
157 x_param_val varchar2(80);
158 begin
159 x_param_str_len := length(p_param_str);
160 x_value_sep_len := length(p_value_sep);
161 x_param_sep_len := length(p_param_sep);
162 x_param_val := null;
163 x_start_pos := p_start_pos;
164 loop
165 p_param_end_pos := 0;
166 --dbms_output.put_line('Start Loop');
167 --dbms_output.put_line('String Length:'||to_char(x_param_str_len));
168 --dbms_output.put_line('Parameter Name:'||p_param_name);
169 --dbms_output.put_line('Starting Pos:'||to_char(x_start_pos));
170 if ( x_start_pos < x_param_str_len ) then
171 x_name_end := instr(p_param_str,p_value_sep,x_start_pos);
172 if (x_name_end = 0) then return null; --'NOVALSEP';
173 end if;
174 x_param_name := substr(p_param_str,x_start_pos,
175 x_name_end-x_start_pos);
176 x_value_end := instr(p_param_str,p_param_sep,
177 x_name_end+x_value_sep_len);
178 --dbms_output.put_line('name end:'||to_char(x_name_end));
179 --dbms_output.put_line('value end:'||to_char(x_value_end));
180 if (x_value_end = 0) then
181 -- return 'NOPARAMSEP';
182 return null;
183 end if;
184 if ( x_param_name = p_param_name) then
185 x_param_val := substr(p_param_str,x_name_end+x_value_sep_len,
186 x_value_end-x_name_end-x_value_sep_len);
187 p_param_end_pos := x_value_end+x_param_sep_len;
188 return(x_param_val);
189 end if;
190 x_start_pos := x_value_end + x_param_sep_len;
191 --dbms_output.put_line('Parameter Name :'||x_param_name);
192 --dbms_output.put_line('Parameter Value:'||x_param_val);
193 --dbms_output.put_line('Start and End Pos:'||to_char(x_start_pos));
194 --dbms_output.put_line('x_name_end:'||to_char(x_name_end));
195 --dbms_output.put_line('----------');
196 --p_end_pos := x_value_end + x_param_sep_len;
197 else
198 --p_end_pos := -1;
199 exit;
200 end if;
201 end loop;
202 --dbms_output.put_line('Parameter Name :'||p_param_name);
203 --dbms_output.put_line('Parameter Value:'||x_param_val);
204 --dbms_output.put_line('Start and End Pos:'||to_char(x_start_pos));
205 --dbms_output.put_line('x_name_end:'||to_char(x_name_end));
206 --dbms_output.put_line('----------');
207 return(null);
208 end get_parameter_value;
209 function get_parameter_value(p_param_str varchar2,
210 p_param_name varchar2,
211 p_param_sep varchar2 /*default g_param_sep*/,
212 p_value_sep varchar2 /*default g_value_sep*/)
213 return varchar2 as
214 x_param_end_pos number;
215 l_val varchar2(400);
216 begin
217 l_val := jtfb_dcf.get_parameter_value(p_param_str,
218 p_param_name,
219 g_param_sep,
220 g_value_sep);
221 /***********************************************
222 dbms_output.put_line('Lval:'||p_param_str || '-' ||
223 p_param_name || ':' ||l_val);
224 ***********************************************/
225 if (l_val = 'NOT_FOUND') then return null;
226 elsif ltrim(l_val) is null then return null;
227 else return l_val;
228 end if;
229 /*****************************
230 return( get_parameter_value(p_param_str,
231 p_param_name,
232 x_param_end_pos,
233 1,
234 p_param_sep,
235 p_value_sep)
236 );
237 **********************/
238 end;
239 procedure yesterday(x_start_date in out nocopy date,
240 x_end_date in out nocopy date) is
241 x_date date;
242 begin
243 x_date := sysdate -1;
244 x_start_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 00:00:00',
245 'dd-mon-yyyy hh24:mi:ss');
246 x_end_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 23:59:59',
247 'dd-mon-yyyy hh24:mi:ss');
248 end;
249 procedure last_year(x_start_date in out nocopy date,
250 x_end_date in out nocopy date) as
251 x_date date;
252 begin
253 x_date := add_months(sysdate,-12);
254 x_start_date := to_date('01-jan-' ||to_char(x_date,'yyyy') || ' 00:00:00',
255 'dd-mon-yyyy hh24:mi:ss');
256 x_end_date := to_date('31-dec-' ||to_char(x_date,'yyyy') || ' 23:59:59',
257 'dd-mon-yyyy hh24:mi:ss');
258 end;
259 procedure last_month(x_start_date in out nocopy date,
260 x_end_date in out nocopy date) as
261 x_date date;
262 begin
263 x_date := to_date('01-' || to_char(add_months(sysdate,-1),'mon-yyyy'),
264 'dd-mon-yyyy');
265
266 x_start_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 00:00:00',
267 'dd-mon-yyyy hh24:mi:ss');
268 x_date := add_months(x_date,1) -1;
269 x_end_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 23:59:59',
270 'dd-mon-yyyy hh24:mi:ss');
271 end;
272 procedure last_week (x_start_date in out nocopy date,
273 x_end_date in out nocopy date) as
274 x_date date;
275 l_sat varchar2(80);
276 begin
277 l_sat := to_char(to_date('01/01/2000','dd/mm/yyyy'),'dy');
278 if (g_debug = 'Y') then
279 biv_core_pkg.biv_debug('Saturday in nls Lang:' || l_sat, g_report_id);
280 end if;
281 x_date := next_day(sysdate,l_sat) - 13;
282 x_start_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 00:00:00',
283 'dd-mon-yyyy hh24:mi:ss');
284 x_date := x_date +6;
285 x_end_date := to_date(to_char(x_date,'dd-mon-yyyy') || ' 23:59:59',
286 'dd-mon-yyyy hh24:mi:ss');
287 end;
288 procedure last_13weeks(x_start_date in out nocopy date,
289 x_end_date in out nocopy date) as
290 x_date date;
291 begin
292 last_week(x_start_date,x_end_date);
293 x_start_date := x_start_date - 12* 7;
294 end;
295 procedure get_dates (p_period_type varchar2,
296 x_start_date in out nocopy date,
297 x_end_date in out nocopy date) as
298 begin
299 if (p_period_type = 'YDAY') then
300 yesterday(x_start_date, x_end_date);
301 elsif (p_period_type = 'LY') then
302 last_year(x_start_date, x_end_date);
303 elsif (p_period_type = 'LM') then
304 last_month(x_start_date, x_end_date);
305 elsif (p_period_type = 'LW') then
306 last_week(x_start_date, x_end_date);
307 elsif (p_period_type = 'L13W') then
308 last_13weeks(x_start_date, x_end_date);
309 end if;
310 -- truncate x_end_date because time component 23:59:59 is taken care of in
311 -- sql query. such as sr.incident_date < (g_cr_end + 1)
312 x_end_date := trunc(x_end_date);
313 end;
314 -- This procedure extracts all possible parameters from parameter string
315 -- and sets global varibale for respective parameter.
316 procedure get_report_parameters(p_param_str varchar2) as
317 l_dt varchar2(30);
318 l_dt_fmt varchar2(30) := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
319 begin
320 g_debug := fnd_profile.value('BIV:DEBUG');
321 g_cust_id_cnt := 0;
322 g_cntr_id_cnt := 0;
323 g_ogrp_cnt := 0;
324 g_agrp_cnt := 0;
325 g_prd_id_cnt := 0;
326 g_sev_cnt := 0;
327 g_esc_lvl_cnt := 0;
328 g_prd_ver_cnt := 0;
329 g_comp_id_cnt := 0;
330 g_subcomp_id_cnt := 0;
331 g_platform_id_cnt := 0;
332 g_sts_id_cnt := 0;
333 g_debug := fnd_profile.value('BIV:DEBUG');
334 --g_srl_no := 1;
335 biv_core_pkg.get_parameter_values_all(g_cust_id ,
336 g_cust_id_cnt ,
337 p_param_str ,
338 'P_CUST_ID');
339 biv_core_pkg.get_parameter_values_all(g_cntr_id ,
340 g_cntr_id_cnt ,
341 p_param_str ,
342 'P_CNTR_ID');
343 biv_core_pkg.get_parameter_values_all(g_ogrp ,
344 g_ogrp_cnt ,
345 p_param_str ,
346 'P_OGRP');
347 biv_core_pkg.get_parameter_values_all(g_agrp ,
348 g_agrp_cnt ,
349 p_param_str ,
350 'P_AGRP');
351 biv_core_pkg.get_parameter_values_all(g_prd_id ,
352 g_prd_id_cnt ,
353 p_param_str ,
354 'P_PRD_ID');
355 biv_core_pkg.get_parameter_values_all(g_sev ,
356 g_sev_cnt ,
360 g_esc_lvl_cnt ,
357 p_param_str ,
358 'P_SEV');
359 biv_core_pkg.get_parameter_values_all(g_esc_lvl ,
361 p_param_str ,
362 'P_ESC_LVL');
363 biv_core_pkg.get_parameter_values_all(g_prd_ver ,
364 g_prd_ver_cnt ,
365 p_param_str ,
366 'P_PRD_VER');
367 biv_core_pkg.get_parameter_values_all(g_comp_id ,
368 g_comp_id_cnt ,
369 p_param_str ,
370 'P_COMP_ID');
371 biv_core_pkg.get_parameter_values_all(g_subcomp_id ,
372 g_subcomp_id_cnt ,
373 p_param_str ,
374 'P_SUBCOMP_ID');
375 biv_core_pkg.get_parameter_values_all(g_platform_id ,
376 g_platform_id_cnt ,
377 p_param_str ,
378 'P_PLATFORM_ID');
379 biv_core_pkg.get_parameter_values_all(g_sts_id ,
380 g_sts_id_cnt ,
381 p_param_str ,
382 'P_STS_ID');
383 biv_core_pkg.get_parameter_values_all(g_mgr_id ,
384 g_mgr_id_cnt ,
385 p_param_str ,
386 'P_MGR_ID');
387 biv_core_pkg.get_parameter_values_all(g_site_id ,
388 g_site_id_cnt ,
389 p_param_str ,
390 'P_SITE_ID');
391
392 -- get all parameters which return single value
393 g_rsc := biv_core_pkg.get_parameter_value(p_param_str, 'P_RSC');
394 g_blog := biv_core_pkg.get_parameter_value(p_param_str, 'P_BLOG');
395 g_other_blog := biv_core_pkg.get_parameter_value(p_param_str, 'P_OTHER_BLOG');
396 g_chnl := biv_core_pkg.get_parameter_value(p_param_str, 'P_CHNL');
397 -- Change for bug 3093779 starts
398 g_ua := biv_core_pkg.get_parameter_value(p_param_str,'P_UA');
399
400 if (g_ua is null) then
401 g_ua := 'Y';
402 end if;
403
404 g_pr := biv_core_pkg.get_parameter_value(p_param_str,'P_PREVR');
405
406 if(g_pr is null) then
407 g_pr := 'N';
408 end if;
409
410 -- Change for bug 3093779 ends
411
412 -- Change for enh 2914005 starts
413 g_total := biv_core_pkg.get_parameter_value(p_param_str,'P_TOTAL');
414
415 if (g_total is null) then
416 g_total := 'N';
417 end if;
418
419 -- Change for enh 2914005 ends
420
421 if (g_chnl = 'ALL') then
422 g_chnl := null;
423 end if;
424 g_resl_code := biv_core_pkg.get_parameter_value(p_param_str, 'P_RESL_CODE');
425 g_arvl_tm := biv_core_pkg.get_parameter_value(p_param_str, 'P_ARVL_TM');
426 g_close_sr := biv_core_pkg.get_parameter_value(p_param_str, 'P_CLOSE_SR');
427 g_oblog := biv_core_pkg.get_parameter_value(p_param_str, 'P_OBLOG');
428 g_eblog := biv_core_pkg.get_parameter_value(p_param_str, 'P_EBLOG');
429 g_reopen := biv_core_pkg.get_parameter_value(p_param_str, 'P_REOPEN');
430 g_reclose := biv_core_pkg.get_parameter_value(p_param_str, 'P_RECLOSE');
431 g_new_sr := biv_core_pkg.get_parameter_value(p_param_str, 'P_NEW_SR');
432 g_agent_id := biv_core_pkg.get_parameter_value(p_param_str, 'P_AGENT_ID');
433 g_today_only := biv_core_pkg.get_parameter_value(p_param_str, 'P_TODAY_ONLY');
434 g_tm_zn := biv_core_pkg.get_parameter_value(p_param_str, 'P_TM_ZN');
435 g_ott := biv_core_pkg.get_parameter_value(p_param_str, 'P_OTT');
436 g_unown := biv_core_pkg.get_parameter_value(p_param_str, 'P_UNOWN');
437 g_esc_sr := biv_core_pkg.get_parameter_value(p_param_str, 'P_ESC_SR');
438 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_ST_DATE');
439 g_st_date := to_date(l_dt,l_dt_fmt);
440 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_END_DATE');
441 g_end_date := to_date(l_dt,l_dt_fmt);
442 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_CR_ST');
443 g_cr_st := to_date(l_dt,l_dt_fmt);
444 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_CR_END');
445 g_cr_end := to_date(l_dt,l_dt_fmt);
446 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_CL_ST');
447 g_cl_st := to_date(l_dt,l_dt_fmt);
448 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_CL_END');
449 g_cl_end := to_date(l_dt,l_dt_fmt);
450 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_ESC_ST');
451 g_esc_st := to_date(l_dt,l_dt_fmt);
452 l_dt := biv_core_pkg.get_parameter_value(p_param_str, 'P_ESC_END');
453 g_esc_end := to_date(l_dt,l_dt_fmt);
454
455 g_lvl := biv_core_pkg.get_parameter_value(p_param_str, 'P_LVL');
456 g_agrp_lvl := biv_core_pkg.get_parameter_value(p_param_str, 'P_AGRP_LVL');
457 g_ogrp_lvl := biv_core_pkg.get_parameter_value(p_param_str, 'P_OGRP_LVL');
458 g_srt_by := biv_core_pkg.get_parameter_value(p_param_str,'P_SRT_BY');
459 if (g_srt_by is null) then
460 g_srt_by := '2';
461 end if;
462 g_view_by := biv_core_pkg.get_parameter_value(p_param_str,'P_VIEW_BY');
463 if (g_view_by is null ) then
467 'BIV_HS_SR_ESCALATION', 'BIV_RT_TASK_ACTIVITY')
464 if (g_report_id in ('BIV_RT_MANAGER_REPORT',
465 'BIV_RT_BACKLOG_BY_STATUS','BIV_HS_SR_ACTIVITY',
466 'BIV_RT_ESC_SR',
468 )then
469 g_view_by := 'MGR';
470 elsif (g_debug = 'Y') then
471 biv_core_pkg.biv_debug('No default view by for the report '||
472 g_report_id, g_report_id);
473 end if;
474 end if;
475 if (g_debug = 'Y') then
476 biv_core_pkg.biv_debug('Report Id:'||g_report_id,g_report_id);
477 end if;
478 g_disp := biv_core_pkg.get_parameter_value(p_param_str,'P_DISP');
479 if (g_disp is null) then
480 g_disp := 10;
481 elsif (upper(g_disp) = 'ALL') then
482 g_disp := '5000';
483 /*
484 else
485 g_display := substr(g_display,2);
486 ***/
487 end if;
488 g_time_frame := biv_core_pkg.get_parameter_value(p_param_str,'P_TIME_FRAME');
489 g_cr_tm_prd :=
490 biv_core_pkg.get_parameter_value(p_param_str,'P_CR_TM_PRD');
491 g_cl_tm_prd :=
492 biv_core_pkg.get_parameter_value(p_param_str,'P_CL_TM_PRD');
493 g_esc_tm_prd :=
494 biv_core_pkg.get_parameter_value(p_param_str,'P_ESC_TM_PRD');
495
496 -- get base column for query
497 if ( g_view_by ='AGRP') then
498 g_base_column := 'adnorm.parent_group_id';
499 elsif (g_view_by ='OGRP') then
500 g_base_column := 'odnorm.parent_group_id';
501 elsif (g_view_by = 'MGR') then
502 g_base_column := 'rsc.source_mgr_id';
503 elsif (g_view_by = 'CUST') then
504 g_base_column := 'sr.customer_id';
505 elsif (g_view_by = 'PRD') then
506 g_base_column := 'sr.inventory_item_id ';
507 g_prd_org := fnd_profile.value('CS_INV_VALIDATION_ORG');
508 --ASO_PRODUCT_ORGANIZATION_ID');
509 elsif (g_view_by = 'CNTRCT') then
510 g_base_column := 'sr.contract_number';
511 elsif (g_view_by = 'ESCONR') then
512 g_base_column := 'srs.esc_owner_id';
513 elsif (g_view_by = 'SSITE') then
514 g_base_column := 'sr.site_id';
515 elsif (g_view_by = 'AGENT') then
516 g_base_column := 'sr.incident_owner_id';
517 else
518 g_base_column := null;
519 end if;
520
521 -- set start and end dates based on time frame
522 if (g_time_frame is not null and
523 (g_st_date is null or g_end_date is null)) then
524 biv_core_pkg.get_dates(g_time_frame, g_st_date, g_end_date);
525 end if;
526 if (g_cr_tm_prd is not null and
527 (g_cr_st is null or g_cr_end is null) ) then
528 biv_core_pkg.get_dates(g_cr_tm_prd,
529 g_cr_st, g_cr_end);
530 end if;
531 if (g_cl_tm_prd is not null and
532 (g_cl_st is null or g_cl_end is null)) then
533 biv_core_pkg.get_dates(g_cl_tm_prd,
534 g_cl_st, g_cl_end);
535 end if;
536 if (g_esc_tm_prd is not null and
537 (g_esc_st is null or g_esc_end is null)) then
538 biv_core_pkg.get_dates(g_esc_tm_prd, g_esc_st, g_esc_end);
539 g_esc_st := trunc(g_esc_st );
540 g_esc_end := trunc(g_esc_end);
541 end if;
542 exception
543 when others then
544 if (g_debug = 'Y' ) then
545 biv_debug('Error:'||substr(sqlerrm,1,200), g_report_id);
546 end if;
547 end;
548 ------------------------------------------------------------
549 procedure add_a_bind(p_cursor number,
550 p_param_array biv_core_pkg.g_parameter_array,
551 p_param_array_size number,
552 p_prefix varchar2,
553 p_column_name varchar2) is
554 l_bind_var_name varchar2(80);
555 l_indx number := 1;
556 begin
557 /************************************
558 dbms_output.put_line('Parameter:'||p_column_name ||
559 ',Count:'||to_char(p_param_array_size));
560 ***********************************************/
561 l_bind_var_name := ':'||p_prefix||'_'||p_column_name;
562 if (p_param_array_size = 1) then
563 if (p_param_array(1) <> biv_core_pkg.g_null) then
564 dbms_sql.bind_variable(p_cursor,l_bind_var_name,p_param_array(l_indx));
565 end if;
566 else
567 loop
568 if (l_indx > nvl(p_param_array_size,0)) then exit; end if;
569 dbms_sql.bind_variable(p_cursor,l_bind_var_name||to_char(l_indx),
570 p_param_array(l_indx));
571 l_indx := l_indx + 1;
572 end loop;
573 end if;
574 end;
575 ------------------------------------------------------------
576 procedure bind_all_variables (p_cursor number) is
577 l_stat varchar2(20);
578 begin
579 add_a_bind(p_cursor ,
580 g_cust_id ,
581 g_cust_id_cnt ,
582 'sr' ,
583 'customer_id' );
584 add_a_bind(p_cursor ,
585 g_cntr_id ,
586 g_cntr_id_cnt ,
587 'sr' ,
588 'contract_number' );
589 add_a_bind(p_cursor ,
590 g_ogrp ,
591 g_ogrp_cnt ,
592 'odnorm1' ,
593 'parent_group_id');
597 'adnorm1' ,
594 add_a_bind(p_cursor ,
595 g_agrp ,
596 g_agrp_cnt ,
598 'parent_group_id');
599 add_a_bind(p_cursor ,
600 g_prd_id ,
601 g_prd_id_cnt ,
602 'sr' ,
603 'inventory_item_id');
604 if (g_report_type = 'RT') then
605 add_a_bind(p_cursor ,
606 g_esc_lvl ,
607 g_esc_lvl_cnt ,
608 'task' ,
609 'escalation_level');
610 else
611 add_a_bind(p_cursor ,
612 g_esc_lvl ,
613 g_esc_lvl_cnt ,
614 'srs' ,
615 'escalation_level');
616 end if;
617 add_a_bind(p_cursor ,
618 g_sev ,
619 g_sev_cnt ,
620 'sr' ,
621 'incident_severity_id');
622 add_a_bind(p_cursor ,
623 g_prd_ver ,
624 g_prd_ver_cnt ,
625 'sr' ,
626 'product_revision');
627 add_a_bind(p_cursor ,
628 g_comp_id ,
629 g_comp_id_cnt ,
630 'sr' ,
631 'cp_component_id');
632 add_a_bind(p_cursor ,
633 g_subcomp_id ,
634 g_subcomp_id_cnt ,
635 'sr' ,
636 'cp_subcomponent_id');
637 add_a_bind(p_cursor ,
638 g_platform_id ,
639 g_platform_id_cnt ,
640 'sr' ,
641 'platform_id');
642 add_a_bind(p_cursor ,
643 g_sts_id ,
644 g_sts_id_cnt ,
645 'sr' ,
646 'incident_status_id');
647 add_a_bind(p_cursor ,
648 g_mgr_id ,
649 g_mgr_id_cnt ,
650 'rsc' ,
651 'source_mgr_id');
652 add_a_bind(p_cursor ,
653 g_site_id ,
654 g_site_id_cnt ,
655 'sr' ,
656 'site_id');
657 if (nvl(g_agent_id,g_null) <> g_null) then
658 dbms_sql.bind_variable(p_cursor,':incident_owner_id',g_agent_id);
659 end if;
660 if (g_chnl is not null) then
661 dbms_sql.bind_variable(p_cursor,':sr_creation_channel',g_chnl);
662 end if;
663 if (nvl(g_resl_code,biv_core_pkg.g_null) <> biv_core_pkg.g_null) then
664 dbms_sql.bind_variable(p_cursor,':resolution_code',g_resl_code);
665 end if;
666 if (g_arvl_tm is not null) then
667 dbms_sql.bind_variable(p_cursor,':arrival_time',g_arvl_tm);
668 end if;
669
670 -- date parameter binding
671 if (g_st_date is not null and
672 (nvl(g_reopen,'N') = 'Y' or nvl(g_reclose,'N') = 'Y' or
673 nvl(g_oblog,'N') = 'Y')) then
674 dbms_sql.bind_variable(p_cursor,':start_date',g_st_date);
675 end if;
676 if (g_end_date is not null and
677 (nvl(g_reopen,'N') = 'Y' or nvl(g_reclose,'N') = 'Y' or
678 nvl(g_eblog,'N') = 'Y')) then
679 dbms_sql.bind_variable(p_cursor,':end_date',g_end_date);
680 end if;
681
682 /**************
683 if (nvl(g_reopen,'N') = 'Y') then
684 dbms_sql.bind_variable(p_cursor,':reopen_st' ,g_st_date );
685 dbms_sql.bind_variable(p_cursor,':reopen_end',g_end_date);
686 end if;
687 **********************/
688 if (g_cr_st is not null) then
689 dbms_sql.bind_variable(p_cursor,':created_start_date',g_cr_st);
690 end if;
691 if (g_cr_end is not null) then
692 dbms_sql.bind_variable(p_cursor,':created_end_date',g_cr_end);
693 end if;
694
695 if (g_cl_st is not null) then
696 dbms_sql.bind_variable(p_cursor,':closed_start_date',g_cl_st);
697 end if;
698 if (g_cl_end is not null) then
699 dbms_sql.bind_variable(p_cursor,':closed_end_date',g_cl_end);
700 end if;
701 if (g_esc_st is not null) then
702 dbms_sql.bind_variable(p_cursor,':esc_st',g_esc_st);
703 end if;
704 if (g_esc_end is not null) then
705 dbms_sql.bind_variable(p_cursor,':esc_end',g_esc_end);
706 end if;
707
708 if (nvl(g_other_blog,'N')='Y') then
709 l_stat := fnd_profile.value('BIV:INC_STATUS_1');
710 dbms_sql.bind_variable(p_cursor,':stat1',l_stat);
711
712 l_stat := fnd_profile.value('BIV:INC_STATUS_2');
713 dbms_sql.bind_variable(p_cursor,':stat2',l_stat);
714
715 l_stat := fnd_profile.value('BIV:INC_STATUS_3');
716 dbms_sql.bind_variable(p_cursor,':stat3',l_stat);
717 end if;
718 if (g_view_by in ('AGRP', 'OGRP') or g_agrp_lvl is not null or
719 g_ogrp_lvl is not null) then
720 dbms_sql.bind_variable(p_cursor,':g_lvl',g_lvl);
721 end if;
722 end;
723 ------------------------------------------------------------
724 procedure add_a_condition(p_param_array biv_core_pkg.g_parameter_array,
725 p_param_array_size number,
726 p_prefix varchar2,
730 p_outer_cond varchar2 /*default 'N'*/) is
727 p_column_name varchar2,
728 p_table varchar2,
729 p_where_clause in out nocopy varchar2,
731 l_indx number;
732 l_outer_suffx varchar2(20);
733 begin
734 if (p_outer_cond = 'Y') then
735 l_outer_suffx := '(+)';
736 else
737 l_outer_suffx := ' ';
738 end if;
739 if (p_param_array_size = 1) then
740 if (p_param_array(1) = biv_core_pkg.g_null) then
741 p_where_clause := p_where_clause || '
742 and ' || p_prefix || '.' || p_column_name || l_outer_suffx ||
743 ' is null ';
744 else
745 p_where_clause := p_where_clause || '
746 and ' || p_prefix || '.' ||p_column_name || l_outer_suffx
747 || ' = :' || p_prefix || '_' ||p_column_name;
748 end if;
749 elsif (p_param_array_size > 1) then
750 p_where_clause := p_where_clause || '
751 and ' || p_prefix || '.' || p_column_name || ' in ( ';
752 l_indx := 1;
753 loop
754 p_where_clause := p_where_clause || ':'||p_prefix || '_' ||
755 p_column_name|| to_char(l_indx);
756 if (l_indx = p_param_array_size) then exit; end if;
757 l_indx := l_indx + 1;
758 p_where_clause := p_where_clause || ',
759 ';
760 end loop;
761 p_where_clause := p_where_clause || ')';
762 end if;
763 end;
764 ------------------------------------------------------------
765 procedure get_where_clause(p_from_clause in out nocopy varchar2,
766 p_where_clause in out nocopy varchar2) as
767 begin
768 if (g_base_column is null) then
769 p_where_clause := ' where 1 = 1';
770 else
771 p_where_clause := ' where 1 = 1';
772 -- 4/29/02
773 -- this is put in comment so that null values too can ne displayed.
774 --p_where_clause := ' where ' || g_base_column || ' is not null';
775 end if;
776 add_a_condition(g_cust_id,
777 g_cust_id_cnt,
778 'sr','customer_id',
779 null,
780 p_where_clause );
781 add_a_condition(g_cntr_id,
782 g_cntr_id_cnt,
783 'sr','contract_number',
784 null,
785 p_where_clause );
786 add_a_condition(g_ogrp,
787 g_ogrp_cnt,
788 'odnorm1','parent_group_id',
789 null,
790 p_where_clause );
791 add_a_condition(g_agrp,
792 g_agrp_cnt,
793 'adnorm1','parent_group_id',
794 null,
795 p_where_clause );
796 add_a_condition(g_prd_id,
797 g_prd_id_cnt,
798 'sr','inventory_item_id',
799 null,
800 p_where_clause );
801 add_a_condition(g_sev,
802 g_sev_cnt,
803 'sr','incident_severity_id',
804 null,
805 p_where_clause );
806 if (g_report_type = 'RT') then
807 add_a_condition(g_esc_lvl,
808 g_esc_lvl_cnt,
809 'task','escalation_level',
810 null,
811 p_where_clause );
812 else
813 add_a_condition(g_esc_lvl,
814 g_esc_lvl_cnt,
815 'srs','escalation_level',
816 null,
817 p_where_clause );
818 end if;
819 add_a_condition(g_prd_ver,
820 g_prd_ver_cnt,
821 'sr','product_revision',
822 null,
823 p_where_clause );
824 add_a_condition(g_comp_id,
825 g_comp_id_cnt,
826 'sr','cp_component_id',
827 null,
828 p_where_clause );
829 add_a_condition(g_subcomp_id,
830 g_subcomp_id_cnt,
831 'sr','cp_subcomponent_id',
832 null,
833 p_where_clause );
834 add_a_condition(g_platform_id,
835 g_platform_id_cnt,
836 'srs','platform_id',
837 null,
838 p_where_clause );
839 add_a_condition(g_sts_id,
840 g_sts_id_cnt,
841 'sr','incident_status_id',
842 null,
843 p_where_clause );
844 add_a_condition(g_mgr_id,
845 g_mgr_id_cnt,
846 'rsc','source_mgr_id',
847 null,
848 p_where_clause );
849 add_a_condition(g_site_id,
850 g_site_id_cnt,
851 'sr','site_id',
852 null,
853 p_where_clause );
854 if (nvl(g_blog,'N') = 'Y' ) then
855 p_where_clause := p_where_clause || '
856 and nvl(stat.close_flag,''N'') <> ''Y''';
857 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
858 p_from_clause := p_from_clause || ',
862 end if;
859 cs_incident_statuses_b stat';
860 p_where_clause := p_where_clause || '
861 and sr.incident_status_id = stat.incident_status_id';
863 end if;
864 if (nvl(g_other_blog,'N') = 'Y' ) then
865 p_where_clause := p_where_clause || '
866 and nvl(stat.close_flag,''N'') <> ''Y''
867 and sr.incident_status_id <> :stat1
868 and sr.incident_status_id <> :stat2
869 and sr.incident_status_id <> :stat3';
870 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
871 p_from_clause := p_from_clause || ',
872 cs_incident_statuses_b stat';
873 p_where_clause := p_where_clause || '
874 and sr.incident_status_id = stat.incident_status_id';
875 end if;
876 end if;
877 if ( g_ott is not null) then
878 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
879 p_from_clause := p_from_clause || ',
880 cs_incident_statuses_b stat';
881 p_where_clause := p_where_clause || '
882 and sr.incident_status_id = stat.incident_status_id';
883 end if;
884 p_where_clause := p_where_clause || '
885 and nvl(stat.close_flag,''N'') <> ''Y''
886 and exists ( select 1 from jtf_tasks_b t,
887 jtf_task_statuses_b s
888 where t.source_object_type_code = ''SR''
889 and t.source_object_id = sr.incident_id
890 and t.task_status_id = s.task_status_id
891 and nvl(s.closed_flag,''N'') <> ''Y''
892 ) ';
893 end if;
894 if (g_unown is not null) then
895 p_where_clause := p_where_clause || '
896 and (nvl(sr.resource_type,''X'') <> ''RS_EMPLOYEE''
897 or sr.incident_owner_id is null)';
898 end if;
899 if (g_agent_id is not null) then
900 if (g_agent_id = g_null) then
901 p_where_clause := p_where_clause || '
902 and sr.incident_owner_id is null';
903 else
904 p_where_clause := p_where_clause || '
905 and sr.incident_owner_id = :incident_owner_id';
906 end if;
907 end if;
908 if (g_today_only='Y') then
909 p_where_clause := p_where_clause || '
910 and sr.incident_date >= trunc(sysdate)
911 and sr.incident_date < trunc(sysdate+1)';
912 end if;
913 if (g_chnl is not null) then
914 /* 4/30/02 no channel is in cs_incident_all_b table itself.
915 if (instr(upper(p_from_clause),'CS_INCIDENTS_ALL_TL') = 0) then
916 p_from_clause := p_from_clause || ',
917 cs_incidents_all_tl srl';
918 p_where_clause := p_where_clause || '
919 and srl.incident_id = sr.incident_id
920 and srl.language = userenv(''LANG'') ';
921 end if;
922 */
923 p_where_clause := p_where_clause || '
924 and upper(sr.sr_creation_channel) = :sr_creation_channel';
925 end if;
926 if (g_resl_code is not null) then
927 if (g_resl_code = biv_core_pkg.g_null) then
928 p_where_clause := p_where_clause || '
929 and sr.resolution_code is null';
930 else
931 p_where_clause := p_where_clause || '
932 and sr.resolution_code = :resolution_code';
933 end if;
934 end if;
935 if (nvl(g_close_sr,'N') = 'Y') then
936 p_where_clause := p_where_clause || '
937 and nvl(stat.close_flag,''N'') = ''Y''';
938 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
939 p_from_clause := p_from_clause || ',
940 cs_incident_statuses_b stat';
941 p_where_clause := p_where_clause || '
942 and sr.incident_status_id = stat.incident_status_id';
943 end if;
944 end if;
945 if (nvl(g_new_sr,'N') = 'Y') then
946 p_where_clause := p_where_clause || '
947 and sr.incident_date >= trunc(sysdate)
948 and sr.incident_date < trunc(sysdate+1)';
949 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
950 p_from_clause := p_from_clause || ',
951 cs_incident_statuses_b stat';
952 p_where_clause := p_where_clause || '
953 and sr.incident_status_id = stat.incident_status_id';
954 end if;
955 end if;
956 if (nvl(g_reopen ,'N') = 'Y' or
957 nvl(g_reclose,'N') = 'Y') then
958 if (instr(upper(p_from_clause),'BIV_SR_SUMMARY') = 0) then
959 p_from_clause := p_from_clause || ',
960 biv_sr_summary srs';
961 p_where_clause := p_where_clause || '
962 and srs.incident_id=sr.incident_id';
963 end if;
964 end if;
965 if (nvl(g_reopen,'N') = 'Y') then
966 p_where_clause := p_where_clause || '
967 and srs.reopen_date is not null
968 and srs.reopen_date between :start_date
969 and :end_date ';
973 and srs.reclose_date is not null
970 end if;
971 if (nvl(g_reclose,'N') = 'Y') then
972 p_where_clause := p_where_clause || '
974 and srs.reclose_date between :start_date
975 and :end_date ';
976 end if;
977 if (nvl(g_oblog,'N') = 'Y') then
978 p_where_clause := p_where_clause || '
979 and sr.incident_date < :start_date
980 and nvl(stat.close_flag,''N'') <> ''Y''';
981 /* above line replaces these lines. it is better to check
982 close flag as cllose_date is not very reliable
983 due to this, added from clause too.
984 and (sr.close_date is null or
985 sr.close_date >= :start_date) ';
986 5/9/2 */
987 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
988 p_from_clause := p_from_clause || ',
989 cs_incident_statuses_b stat';
990 p_where_clause := p_where_clause || '
991 and sr.incident_status_id = stat.incident_status_id';
992 end if;
993 end if;
994 if (nvl(g_eblog,'N') = 'Y') then
995 p_where_clause := p_where_clause || '
996 and sr.incident_date < :end_date
997 and nvl(stat.close_flag,''N'') <> ''Y''';
998 if (instr(upper(p_from_clause),'CS_INCIDENT_STATUSES_B') = 0) then
999 p_from_clause := p_from_clause || ',
1000 cs_incident_statuses_b stat';
1001 p_where_clause := p_where_clause || '
1002 and sr.incident_status_id = stat.incident_status_id';
1003 end if;
1004 end if;
1005 if ( nvl(g_rsc,'X') = 'WEB') then
1006 p_from_clause := p_from_clause || ',
1007 jtf_rs_res_availability avl ';
1008 p_where_clause := p_where_clause || '
1009 and avl.resource_id = sr.incident_owner_id';
1010 end if;
1011 /*
1012 if (g_time_frame is not null) then
1013 p_where_clause := p_where_clause || '
1014 and sr.incident_date between ' || ':start_date and :end_date ';
1015 end if;
1016 if (g_cr_tm_prd is not null) then
1017 p_where_clause := p_where_clause || '
1018 and sr.incident_date between ' ||
1019 ':created_start_date and :created_end_date ';
1020 end if;
1021 if (g_cl_tm_prd is not null) then
1022 p_where_clause := p_where_clause || '
1023 and sr.close_date between ' ||
1024 ':closed_start_date and :closed_end_date ';
1025 end if;
1026 */
1027 if (g_cr_st is not null) then
1028 p_where_clause := p_where_clause || '
1029 and sr.incident_date >= :created_start_date ';
1030 end if;
1031 if (g_cr_end is not null) then
1032 p_where_clause := p_where_clause || '
1033 and sr.incident_date < (:created_end_date+1) ';
1034 end if;
1035 if (g_cl_st is not null) then
1036 p_where_clause := p_where_clause || '
1037 and sr.close_date >= :closed_start_date ';
1038 end if;
1039 if (g_cl_end is not null) then
1040 p_where_clause := p_where_clause || '
1041 and sr.close_date < (:closed_end_date+1) ';
1042 end if;
1043 if (g_esc_st is not null ) then
1044 if (instr(upper(p_from_clause),'BIV_SR_SUMMARY') = 0) then
1045 p_from_clause := p_from_clause || ',
1046 biv_sr_summary srs';
1047 p_where_clause := p_where_clause || '
1048 and srs.incident_id=sr.incident_id';
1049 end if;
1050 p_where_clause := p_where_clause || '
1051 and srs.escalation_date >= :esc_st ';
1052 end if;
1053 if (g_esc_end is not null ) then
1054 if (instr(upper(p_from_clause),'BIV_SR_SUMMARY') = 0) then
1055 p_from_clause := p_from_clause || ',
1056 biv_sr_summary srs';
1057 p_where_clause := p_where_clause || '
1058 and srs.incident_id=sr.incident_id';
1059 end if;
1060 p_where_clause := p_where_clause || '
1061 and srs.escalation_date < (:esc_end+1) ';
1062 end if;
1063
1064
1065 if (g_esc_lvl_cnt > 0 or g_esc_sr is not null) then
1066 if (g_report_type = 'RT') then
1067 if (instr(upper(p_from_clause),'JTF_TASKS_B') = 0) then
1068 p_from_clause := p_from_clause || ',
1069 jtf_tasks_b task,
1070 jtf_task_references_b ref';
1071 p_where_clause := p_where_clause || '
1072 and ref.object_type_code = ''SR''
1073 and ref.object_id = sr.incident_id
1074 and ref.reference_code = ''ESC''
1075 and ref.task_id = task.task_id
1076 and task.task_type_id = 22 ';
1077 end if;
1078 else
1079 if (instr(upper(p_from_clause),'BIV_SR_SUMMARY') = 0) then
1080 p_from_clause := p_from_clause || ',
1081 biv_sr_summary srs';
1082 p_where_clause := p_where_clause || '
1083 and srs.incident_id=sr.incident_id';
1084 end if;
1085 end if;
1086 end if;
1087 if (g_esc_sr is not null) then
1088 if (g_report_type = 'RT') then
1092 p_where_clause := p_where_clause || '
1089 p_where_clause := p_where_clause || '
1090 and task.escalation_level is not null ';
1091 else
1093 and srs.escalation_level is not null ';
1094 end if;
1095 end if;
1096 --
1097 if (g_arvl_tm is not null) then
1098 if (instr(upper(p_from_clause),'BIV_SR_SUMMRY') = 0) then
1099 p_from_clause := p_from_clause || ',
1100 biv_sr_summary srs';
1101 p_where_clause := p_where_clause || '
1102 and srs.incident_id=sr.incident_id';
1103 end if;
1104 p_where_clause := p_where_clause || '
1105 and srs.arrival_time = :arrival_time';
1106 end if;
1107
1108 if (g_mgr_id_cnt <> 0) then
1109 if (instr(upper(p_from_clause), 'JTF_RS_RESOURCE_EXTNS RSC') = 0) then
1110 p_from_clause := p_from_clause || ',
1111 jtf_rs_resource_extns rsc';
1112 p_where_clause:= p_where_clause || '
1113 and (sr.incident_owner_id = rsc.resource_id(+) /* or
1114 sr.incident_owner_id = mgr.resource_id */) ';
1115 end if;
1116 /*
1117 if (instr(upper(p_from_clause), 'JTF_RS_RESOURCE_EXTNS MGR') = 0) then
1118 p_from_clause := p_from_clause || ',
1119 jtf_rs_resource_extns mgr';
1120 p_where_clause:= p_where_clause || '
1121 and mgr.source_id = rsc.source_mgr_id ';
1122 end if;
1123 */
1124 --Change for Bug 3093779 begins
1125 else
1126 if (g_pr = 'BIV_RT_BACKLOG_BY_STATUS' and g_ua <> 'N' and g_report_id = 'BIV_SERVICE_REQUEST' ) then
1127 if (instr(upper(p_from_clause),'JTF_RS_RESOURCE_EXTNS RSC') = 0) then
1128 p_from_clause := p_from_clause ||',
1129 jtf_rs_resource_extns rsc';
1130 p_where_clause := p_where_clause ||' and
1131 (sr.incident_owner_id = rsc.resource_id(+)) and
1132 rsc.source_mgr_id is null';
1133 end if;
1134 -- Change for Big 3093779 ends
1135 end if;
1136 end if;
1137
1138 -- Change for enh 2914005 begins
1139 if(g_pr = 'BIV_DASH_SR_BIN' and g_total = 'Y' and g_report_id = 'BIV_SERVICE_REQUEST') then
1140 p_from_clause := p_from_clause || ', cs_incident_statuses_b stat';
1141 p_where_clause := p_where_clause || '
1142 and sr.incident_status_id = stat.incident_status_id
1143 and stat.incident_subtype = ''INC''
1144 and nvl(stat.close_flag,''N'') != ''Y''
1145 ';
1146 end if;
1147 -- Change for enh 2914005 ends
1148
1149 -- Change for Bug 3188504 begins
1150 if (g_pr = 'BIV_HS_SR_ACTIVITY' and g_report_id ='BIV_SERVICE_REQUEST' ) then
1151 /*if(g_reclose is null and g_reopen is null) then
1152 p_from_clause := p_from_clause || ',biv_sr_summary srs';
1153 p_where_clause := p_where_clause || ' and (sr.incident_id = srs.incident_id)';
1154 end if;*/
1155 if(g_st_date is not null and g_reopen is null) then
1156 p_where_clause:= replace(p_where_clause,'and nvl(stat.close_flag,''N'') <> ''Y''',
1157 'and (nvl(stat.close_flag,''N'')<> ''Y'' or nvl(sr.close_date,sysdate-1000) > :start_date)');
1158 else
1159 if(g_end_date is not null and g_reclose is null) then
1160 p_where_clause:= replace(p_where_clause,'and nvl(stat.close_flag,''N'') <> ''Y''',
1161 'and (nvl(stat.close_flag,''N'')<> ''Y'' or nvl(sr.close_date,sysdate-1000) > :end_date)');
1162 end if;
1163 end if;
1164 -- Change for Bug 2948411
1165 if(instr(upper(p_from_clause),'BIV_SR_SUMMARY') =0) then
1166 p_from_clause := p_from_clause ||',
1167 biv_sr_summary srs';
1168 p_where_clause := p_where_clause ||' and (sr.incident_id =
1169 srs.incident_id)';
1170 end if;
1171 end if;
1172 -- Change for Bug 3188504 ends
1173
1174 -- Change for Bug 2948411
1175 if(g_pr = 'BIV_HS_SR_ARRIVAL_TM' and g_report_id='BIV_SERVICE_REQUEST') then
1176 if(instr(upper(p_from_clause),'BIV_SR_SUMMARY')=0) then
1177 p_from_clause := p_from_clause || ' ,biv_sr_summary srs';
1178 p_where_clause := p_where_clause || ' and (sr.incident_id =
1179 srs.incident_id)';
1180 end if;
1181 end if;
1182 -- usage is not added here because group is known. It added in case of
1183 -- view_by = OGRP or AGRP only because there we need groups of particular
1184 -- level with desired usage
1185
1186 /*
1187 if (g_ogrp_cnt > 0 or
1188 g_view_by = 'OGRP' ) then
1189 p_from_clause := p_from_clause || ',
1190 jtf_rs_groups_denorm odnorm,
1191 jtf_rs_group_members gmmbr';
1192 p_where_clause := p_where_clause || '
1193 and gmmbr.group_id = odnorm.group_id
1194 and sr.incident_owner_id = gmmbr.resource_id';
1195 end if;
1196 if (g_agrp_cnt > 0 or
1197 g_view_by = 'AGRP' ) then
1198 p_from_clause := p_from_clause || ',
1199 jtf_rs_groups_denorm adnorm,
1200 jtf_rs_groups_denorm adnorm1';
1201 p_where_clause := p_where_clause || '
1205 */
1202 and sr.owner_group_id = adnorm.group_id
1203 and adnorm.parent_group_id = adnorm1.group_id';
1204 end if;
1206
1207 -- condition for view by parameter
1208 if (g_view_by = 'OGRP' or g_ogrp_lvl is not null) then
1209 p_from_clause := p_from_clause || ',
1210 jtf_rs_groups_denorm odnorm,
1211 jtf_rs_group_members gmmbr,
1212 biv_resource_groups biv_rg';
1213 p_where_clause:= p_where_clause || '
1214 and gmmbr.group_id = odnorm.group_id
1215 and sr.incident_owner_id = gmmbr.resource_id
1216 and nvl(gmmbr.delete_flag,''N'') <> ''Y''
1217 and biv_rg.group_id = odnorm.parent_group_id
1218 and biv_rg.usage = ''METRICS''
1219 and biv_rg.group_level = nvl(:g_lvl,''1'')';
1220 elsif (g_view_by = 'AGRP' or g_agrp_lvl is not null) then
1221 p_from_clause := p_from_clause || ',
1222 jtf_rs_groups_denorm adnorm,
1223 biv_resource_groups biv_rg';
1224 p_where_clause:= p_where_clause || '
1225 and sr.owner_group_id = adnorm.group_id (+)
1226 and biv_rg.group_id = adnorm.parent_group_id' ||
1227 ' and biv_rg.usage = ''SUPPORT'' ' ||
1228 ' and biv_rg.group_level = nvl(:g_lvl,''1'')';
1229 elsif (g_view_by = 'MGR') then
1230 if (instr(upper(p_from_clause), 'JTF_RS_RESOURCE_EXTNS RSC') = 0) then
1231 p_from_clause := p_from_clause || ',
1232 jtf_rs_resource_extns rsc';
1233 p_where_clause:= p_where_clause || '
1234 and (sr.incident_owner_id = rsc.resource_id(+) /*or
1235 sr.incident_owner_id = mgr.resource_id */) ';
1236 end if;
1237 /*
1238 if (instr(upper(p_from_clause), 'JTF_RS_RESOURCE_EXTNS MGR') = 0) then
1239 p_from_clause := p_from_clause || ',
1240 jtf_rs_resource_extns mgr';
1241 p_where_clause:= p_where_clause || '
1242 and mgr.source_id = rsc.source_mgr_id ';
1243 end if;
1244 */
1245 end if;
1246 if (g_agrp_cnt > 0 ) then
1247 p_from_clause := p_from_clause || ',
1248 jtf_rs_groups_denorm adnorm1';
1249 if (nvl(g_view_by,'X') <> 'AGRP' and g_agrp_lvl is null) then
1250 p_where_clause := p_where_clause || '
1251 and sr.owner_group_id = adnorm1.group_id';
1252 else
1253 p_where_clause := p_where_clause || '
1254 and adnorm.parent_group_id = adnorm1.group_id';
1255 end if;
1256 end if;
1257 -------------------
1258 /*
1259 if (g_ogrp_cnt > 0 or
1260 g_view_by = 'OGRP' ) then
1261 p_from_clause := p_from_clause || ',
1262 jtf_rs_groups_denorm odnorm,
1263 jtf_rs_group_members gmmbr';
1264 p_where_clause := p_where_clause || '
1265 and gmmbr.group_id = odnorm.group_id
1266 and sr.incident_owner_id = gmmbr.resource_id';
1267 end if;
1268 */
1269 -------------------
1270 if (g_ogrp_cnt > 0) then
1271 p_from_clause := p_from_clause || ',
1272 jtf_rs_groups_denorm odnorm1';
1273 if (nvl(g_view_by,'X') <> 'OGRP' and g_ogrp_lvl is null) then
1274 p_from_clause := p_from_clause || ',
1275 jtf_rs_group_members gmmbr';
1276 p_where_clause := p_where_clause || '
1277 and gmmbr.group_id = odnorm1.group_id
1278 and nvl(gmmbr.delete_flag,''N'') <> ''Y''
1279 and sr.incident_owner_id = gmmbr.resource_id';
1280 else
1281 p_where_clause := p_where_clause || '
1282 and odnorm.parent_group_id = odnorm1.group_id';
1283 end if;
1284 end if;
1285
1286 /***
1287 --
1288 if (length(p_where_clause) > 15 ) then
1289 p_where_clause := '
1290 where ' ||
1291 substr(p_where_clause,instr(p_where_clause,'and')+4);
1292 end if;
1293 *****/
1294 end get_where_clause;
1295 function param_for_base_col return varchar2 is
1296 begin
1297 if (g_view_by = 'MGR') then
1298 return('P_MGR_ID');
1299 elsif (g_view_by = 'OGRP') then
1300 return('P_OGRP');
1301 elsif (g_view_by = 'AGRP') then
1302 return('P_AGRP');
1303 elsif (g_view_by = 'PRD') then
1304 return('P_PRD_ID');
1305 elsif (g_view_by = 'MGR') then
1306 return('P_MGR_ID');
1307 elsif (g_view_by = 'SSITE') then
1308 return('P_SITE_ID');
1309 elsif (g_view_by = 'CUST') then
1310 return('P_CUST_ID');
1311 else return('P_AGENT_ID');
1312 end if;
1313 end;
1314 procedure update_base_col_desc(p_tbl_name varchar2 /*default null*/) is
1315 l_tbl varchar2(30);
1316 l_sql varchar2(500);
1317 l_err varchar2(250);
1318 l_session_id biv_tmp_rt2.session_id % type;
1319 l_null_desc fnd_lookups.meaning % type;
1320 begin
1321 l_session_id := biv_core_pkg.get_session_id;
1322 l_tbl := nvl(p_tbl_name,'jtfb_temp_report');
1323 l_sql := null;
1324 if (g_view_by = 'AGRP' or g_view_by='OGRP') then
1325 l_sql := 'update ' || l_tbl || ' rep
1326 set col2 = (select substr(group_name,1,50)
1327 from jtf_rs_groups_vl grp
1331 l_sql := 'update ' || l_tbl || ' rep
1328 where grp.group_id = nvl(rep.col1,rep.id))
1329 where session_id = :session_id' ;
1330 elsif (g_view_by = 'PRD') then
1332 set col2 = (select substr(description,1,50) from mtl_system_items_vl
1333 where inventory_item_id = nvl(rep.col1,rep.id)
1334 and organization_id = ' ||g_prd_org || ')
1335 where session_id = :session_id';
1336 elsif (g_view_by = 'MGR') then
1337 l_sql := 'update ' || l_tbl || ' rep
1338 set col2 = (select substr(full_name,1,50) from per_people_f
1339 where person_id = nvl(rep.col1,rep.id)
1340 and sysdate between
1341 nvl(effective_start_date,sysdate-1) and
1342 nvl(effective_end_date,sysdate+2)
1343 )
1344 where session_id = :session_id';
1345 elsif (g_view_by = 'SSITE') then
1346 l_sql := 'update ' || l_tbl || ' rep
1347 set col2 = (select substr(party_site_name,1,50) from hz_party_sites
1348 where party_site_id = nvl(rep.col1,rep.id))
1349 where session_id = :session_id';
1350 elsif (g_view_by = 'CUST') then
1351 l_sql := 'update ' || l_tbl || ' rep
1352 set col2 = (select substr(party_name,1,50) from hz_parties
1353 where party_id = nvl(rep.col1,rep.id))
1354 where session_id = :session_id';
1355 elsif (g_report_id = 'BIV_RT_AGENT_REPORT' or g_view_by = 'AGENT' or
1356 g_view_by = 'ESCONR') then
1357 l_sql := 'update ' || l_tbl || ' rep
1358 set col2 = (select substr(source_name,1,50) from jtf_rs_resource_extns
1359 where resource_id = rep.col1)
1360 where session_id = :session_id';
1361 else
1362 l_sql := 'update ' || l_tbl || ' rep
1363 set col2 = col1
1364 where session_id = :session_id' ;
1365 end if;
1366 if (g_debug = 'Y') then
1367 biv_core_pkg.biv_debug('SQL for updating Description:'||l_sql, g_report_id);
1368 end if;
1369 commit;
1370 if (l_sql is null) then
1371 if (g_debug = 'Y') then
1372 biv_core_pkg.biv_debug('Invalid Value for P_VIEW parameter:'||g_view_by,
1373 g_report_id);
1374 end if;
1375 else
1376 execute immediate l_sql using l_session_id;
1377 end if;
1378 l_null_desc := get_lookup_meaning('NA');
1379 l_sql := 'update ' || l_tbl || '
1380 set col2 = :null_desc
1381 where col2 is null
1382 and session_id = :session_id';
1383 if (g_debug = 'Y') then
1384 biv_core_pkg.biv_debug('SQL for NULLL Description:'||l_sql, g_report_id);
1385 end if;
1386 execute immediate l_sql using l_null_desc, l_session_id;
1387
1388 exception
1389 when others then
1390 if (g_debug = 'Y') then
1391 l_err := 'Err in update_base_col:' ||substr(sqlerrm,1,200);
1392 biv_core_pkg.biv_debug(l_err,g_report_id);
1393 biv_core_pkg.biv_debug(l_sql,g_report_id);
1394 end if;
1395 end;
1396 procedure update_description(p_id_type varchar2,
1397 p_id_col varchar2,
1398 p_desc_col varchar2,
1399 p_tbl_name varchar2 /*default null*/) as
1400 l_sql_sttmnt varchar2(2000);
1401 l_id_type varchar2(100);
1402 l_tbl varchar2(50);
1403 l_err varchar2(250);
1404 l_null_desc fnd_lookups.meaning % type;
1405 begin
1406 l_id_type := upper(p_id_type);
1407 l_tbl := nvl(p_tbl_name,'jtfb_temp_report');
1408 if (l_id_type = 'P_AGRP' or l_id_type='P_OGRP') then
1409 l_sql_sttmnt := '
1410 update ' || l_tbl || ' rep
1411 set '|| p_desc_col || ' = (select substr(group_name,1,50)
1412 from jtf_rs_groups_vl grp
1413 where grp.group_id = rep.' || p_id_col || ')
1414 where session_id = :session_id' ;
1415 elsif (l_id_type = 'P_PRD_ID') then
1416 l_sql_sttmnt := '
1417 update ' || l_tbl || ' rep
1418 set ' || p_desc_col || ' = (select substr(description,1,50) from mtl_system_items_vl
1419 where inventory_item_id = rep.' || p_id_col || '
1420 and organization_id = '|| g_prd_org || ')
1421 where session_id = :session_id';
1422 elsif (l_id_type = 'P_MGR_ID') then
1423 l_sql_sttmnt := '
1424 update ' || l_tbl || ' rep
1425 set ' || p_desc_col || ' = (select substr(full_name,1,50) from per_people_f
1426 where person_id = rep.' || p_id_col || '
1427 and sysdate between
1428 nvl(effective_start_date,sysdate-1) and
1429 nvl(effective_end_date,sysdate+2)
1430 )
1431 where session_id = :session_id';
1432 elsif (l_id_type = 'P_SSITE_ID') then
1433 l_sql_sttmnt := '
1434 update ' || l_tbl || ' rep
1435 set ' || p_desc_col || ' = (select substr(party_site_name,1,50) from hz_party_sites
1436 where party_site_id = rep.' || p_id_col || ')
1437 where session_id = :session_id';
1438 elsif (l_id_type = 'P_CUST_ID') then
1439 l_sql_sttmnt := '
1440 update ' || l_tbl || ' rep
1441 set ' || p_desc_col || ' = (select substr(party_name,1,50) from hz_parties
1445 l_sql_sttmnt := '
1442 where party_id = rep.' || p_id_col || ')
1443 where session_id = :session_id';
1444 elsif (l_id_type = 'P_AGENT_ID') then
1446 update ' || l_tbl || ' rep
1447 set ' || p_desc_col || ' = (select substr(source_name,1,50)
1448 from jtf_rs_resource_extns
1449 where resource_id = rep.' || p_id_col || ')
1450 where session_id = :session_id';
1451 end if;
1452
1453 if (g_debug = 'Y') then
1454 biv_core_pkg.biv_debug('SQL for updating Description:'||l_sql_sttmnt,
1455 g_report_id);
1456 end if;
1457 execute immediate l_sql_sttmnt using biv_core_pkg.get_session_id;
1458
1459 if (upper(p_desc_col) = 'COL2') then
1460 l_null_desc := get_lookup_meaning('NA');
1461 l_sql_sttmnt := 'update ' || l_tbl || '
1462 set col2 = :nul_desc
1463 where col2 is null and session_id = :session_id';
1464 if (g_debug = 'Y') then
1465 biv_core_pkg.biv_debug('SQL for updating NULL Description:'||
1466 l_sql_sttmnt, g_report_id);
1467 end if;
1468 execute immediate l_sql_sttmnt using l_null_desc,
1469 biv_core_pkg.get_session_id;
1470 end if;
1471 exception
1472 when others then
1473 if (g_debug = 'Y') then
1474 l_err := 'Err in update_description:' ||substr(sqlerrm,1,200);
1475 biv_core_pkg.biv_debug(l_err,g_report_id);
1476 biv_core_pkg.biv_debug(l_sql_sttmnt,g_report_id);
1477 end if;
1478 end;
1479 function are_all_parameters_null return number is
1480 begin
1481 if ( nvl(g_cust_id_cnt ,0) = 0 and
1482 nvl(g_cntr_id_cnt ,0) = 0 and
1483 nvl(g_ogrp_cnt ,0) = 0 and
1484 nvl(g_agrp_cnt ,0) = 0 and
1485 nvl(g_prd_id_cnt ,0) = 0 and
1486 nvl(g_sev_cnt ,0) = 0 and
1487 nvl(g_esc_lvl_cnt ,0) = 0 and
1488 nvl(g_prd_ver_cnt ,0) = 0 and
1489 nvl(g_comp_id_cnt ,0) = 0 and
1490 nvl(g_subcomp_id_cnt ,0) = 0 and
1491 nvl(g_platform_id_cnt ,0) = 0 and
1492 nvl(g_sts_id_cnt ,0) = 0 and
1493
1494 g_time_frame is null and
1495 g_base_column is null and
1496 g_view_by is null and
1497 g_lvl is null and
1498 g_st_date is null and
1499 g_end_date is null and
1500 g_srt_by is null and
1501 g_cr_tm_prd is null and
1502 g_cl_tm_prd is null and
1503 g_cl_st is null and
1504 g_cl_end is null and
1505 g_esc_st is null and
1506 g_esc_end is null and
1507 g_cr_st is null and
1508 g_cr_end is null and
1509 g_disp is null and
1510
1511 g_blog is null and
1512 g_other_blog is null and
1513 g_agent_id is null and
1514 g_close_sr is null and
1515 g_today_only is null and
1516 g_chnl is null
1517 ) then
1518 return 1;
1519 else
1520 return 0;
1521 end if;
1522 end;
1523 procedure concatenate_date_param (p_param_value date,
1524 p_param_name varchar2,
1525 p_param_str in out nocopy varchar2) is
1526 l_dt_fmt varchar2(30) := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
1527 begin
1528 if (p_param_value is not null) then
1529 p_param_str := p_param_str || p_param_name || g_value_sep ||
1530 to_char( p_param_value,l_dt_fmt) ||g_param_sep;
1531
1532 end if;
1533 end;
1534 procedure concatenate_single_val_param(p_param_value varchar2,
1535 p_param_name varchar2,
1536 p_param_str in out nocopy varchar2) is
1537 begin
1538 if (p_param_value is not null) then
1539 p_param_str := p_param_str || p_param_name || g_value_sep ||
1540 p_param_value ||g_param_sep;
1541
1542 end if;
1543 end;
1544 procedure concatenate_multi_val_param(p_param_values g_parameter_array,
1545 p_value_count number,
1546 p_param_str in out nocopy varchar2,
1547 p_param_name varchar2) is
1548 i number;
1549 begin
1550 if (nvl(p_value_count,0) <> 0) then
1551 p_param_str := p_param_str || p_param_name || g_value_sep;
1552 for i in 1..p_value_count loop
1553 p_param_str := p_param_str || p_param_values(i);
1554 if (i<> p_value_count) then
1555 p_param_str := p_param_str || g_multi_value_sep;
1556 end if;
1557 end loop;
1558 p_param_str := p_param_str || g_param_sep;
1559 end if;
1560 end;
1561 function reconstruct_param_str return varchar2 is
1562 p_param_str varchar2(2000);
1563 begin
1564 p_param_str := null;
1565 concatenate_multi_val_param(g_cust_id ,
1566 g_cust_id_cnt ,
1567 p_param_str ,
1568 'P_CUST_ID');
1569 concatenate_multi_val_param(g_cntr_id ,
1570 g_cntr_id_cnt ,
1571 p_param_str ,
1572 'P_CNTR_ID');
1576 'P_OGRP');
1573 concatenate_multi_val_param(g_ogrp ,
1574 g_ogrp_cnt ,
1575 p_param_str ,
1577 concatenate_multi_val_param(g_agrp ,
1578 g_agrp_cnt ,
1579 p_param_str ,
1580 'P_AGRP');
1581 concatenate_multi_val_param(g_prd_id ,
1582 g_prd_id_cnt ,
1583 p_param_str ,
1584 'P_PRD_ID');
1585 concatenate_multi_val_param(g_sev ,
1586 g_sev_cnt ,
1587 p_param_str ,
1588 'P_SEV');
1589 concatenate_multi_val_param(g_esc_lvl ,
1590 g_esc_lvl_cnt ,
1591 p_param_str ,
1592 'P_ESC_LVL');
1593 concatenate_multi_val_param(g_prd_ver ,
1594 g_prd_ver_cnt ,
1595 p_param_str ,
1596 'P_PRD_VER');
1597 concatenate_multi_val_param(g_comp_id ,
1598 g_comp_id_cnt ,
1599 p_param_str ,
1600 'P_COMP_ID');
1601 concatenate_multi_val_param(g_subcomp_id ,
1602 g_subcomp_id_cnt ,
1603 p_param_str ,
1604 'P_SUBCOMP_ID');
1605 concatenate_multi_val_param(g_platform_id ,
1606 g_platform_id_cnt ,
1607 p_param_str ,
1608 'P_PLATFORM_ID');
1609 concatenate_multi_val_param(g_sts_id ,
1610 g_sts_id_cnt ,
1611 p_param_str ,
1612 'P_STS_ID');
1613 concatenate_multi_val_param(g_mgr_id ,
1614 g_mgr_id_cnt,
1615 p_param_str ,
1616 'P_MGR_ID' );
1617 concatenate_multi_val_param(g_site_id ,
1618 g_site_id_cnt,
1619 p_param_str ,
1620 'P_SITE_ID' );
1621 concatenate_single_val_param(g_agent_id , 'P_AGENT_ID' , p_param_str );
1622 concatenate_single_val_param(g_blog , 'P_BLOG' , p_param_str );
1623 concatenate_single_val_param(g_eblog , 'P_EBLOG' , p_param_str );
1624 concatenate_single_val_param(g_oblog , 'P_OBLOG' , p_param_str );
1625 concatenate_single_val_param(g_other_blog , 'P_OTHER_BLOG' , p_param_str );
1626 concatenate_single_val_param(g_chnl , 'P_CHNL' , p_param_str );
1627 concatenate_single_val_param(g_close_sr , 'P_CLOSE_SR' , p_param_str );
1628 concatenate_single_val_param(g_new_sr , 'P_NEW_SR' , p_param_str );
1629 concatenate_single_val_param(g_reopen , 'P_REOPEN' , p_param_str );
1630 concatenate_single_val_param(g_reclose , 'P_RECLOSE' , p_param_str );
1631 concatenate_single_val_param(g_time_frame , 'P_TIME_FRAME' , p_param_str );
1632 concatenate_single_val_param(g_lvl , 'P_LVL' , p_param_str );
1633 concatenate_single_val_param(g_ogrp_lvl , 'P_OGRP_LVL' , p_param_str );
1634 concatenate_single_val_param(g_agrp_lvl , 'P_AGRP_LVL' , p_param_str );
1635
1636 concatenate_single_val_param(g_unown , 'P_UNOWN' , p_param_str );
1637 concatenate_single_val_param(g_esc_sr , 'P_ESC_SR' , p_param_str );
1638
1639 concatenate_date_param(g_st_date , 'P_ST_DATE' , p_param_str );
1640 concatenate_date_param(g_end_date , 'P_END_DATE' , p_param_str );
1641 concatenate_date_param(g_cl_st , 'P_CL_ST' , p_param_str );
1642 concatenate_date_param(g_cl_end , 'P_CL_END' , p_param_str );
1643 concatenate_date_param(g_esc_st , 'P_ESC_ST' , p_param_str );
1644 concatenate_date_param(g_esc_end , 'P_ESC_END' , p_param_str );
1645 concatenate_date_param(g_cr_st , 'P_CR_ST' , p_param_str );
1646 concatenate_date_param(g_cr_end , 'P_CR_END' , p_param_str );
1647
1648 return p_param_str;
1649 end;
1650 procedure reset_view_by_param is
1651 begin
1652 if ( g_view_by ='AGRP') then
1653 g_agrp_cnt := 0;
1654 elsif (g_view_by ='OGRP') then
1655 g_ogrp_cnt := 0;
1656 elsif (g_view_by = 'MGR') then
1657 g_mgr_id_cnt := 0;
1658 elsif (g_view_by = 'PRD') then
1659 g_prd_id_cnt := 0;
1660 elsif (g_view_by = 'SSITE') then
1661 g_site_id_cnt := 0;
1662 end if;
1663 end;
1664
1665 function get_session_id return number is
1666 begin
1667 return icx_sec.g_session_id;
1668 end get_session_id;
1669
1670 procedure clean_dcf_table(p_code varchar2) is
1671 l_session_id NUMBER;
1672 l_code varchar2(50);
1673 begin
1674 l_code := upper(p_code);
1675 l_session_id := biv_core_pkg.get_session_id;
1676 if l_code = 'BIV_TMP_BIN' then
1677 execute immediate 'delete from BIV_TMP_BIN where
1678 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1679 elsif (l_code = 'BIV_TMP_RT1') then
1680 execute immediate 'delete from BIV_TMP_RT1 where
1681 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1682 elsif (l_code = 'BIV_TMP_RT2') then
1683 execute immediate 'delete from BIV_TMP_RT2 where
1684 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1685 elsif (l_code = 'BIV_TMP_HS1') then
1686 execute immediate 'delete from BIV_TMP_HS1 where
1687 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1688 elsif (l_code = 'BIV_TMP_HS2') then
1689 execute immediate 'delete from BIV_TMP_HS2 where
1690 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1691 elsif (l_code = 'BIV_TMP_SR_ARRVL') then
1692 execute immediate 'delete from BIV_TMP_SR_ARRVL where
1693 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1694 else
1695 execute immediate 'delete from BIV_TMP_BIN where
1696 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1697 execute immediate 'delete from BIV_TMP_RT1 where
1698 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1699 execute immediate 'delete from BIV_TMP_RT2 where
1700 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1701 execute immediate 'delete from BIV_TMP_HS2 where
1702 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1703 execute immediate 'delete from BIV_TMP_SR_ARRVL where
1704 session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1705 end if;
1706 g_debug := fnd_profile.value('BIV:DEBUG');
1707 if (g_debug = 'Y') then
1708 execute immediate 'delete from biv_debug where session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1709 end if;
1710 biv_core_pkg.g_srl_no := 0;
1711 if (g_debug = 'Y') then
1712 biv_core_pkg.biv_debug('Code:'||l_code,biv_core_pkg.g_report_id);
1713 end if;
1714 -- biv_core_pkg.biv_debug('Code:'||l_code,biv_core_pkg.g_report_id);
1715 -- biv_core_pkg.biv_debug('Debug:'||g_debug,biv_core_pkg.g_report_id);
1716 commit;
1717 exception when others then
1718 rollback;
1719 if (g_debug = 'Y') then
1720 biv_core_pkg.biv_debug('Error:'||substr(sqlerrm,1,200),
1721 biv_core_pkg.g_report_id);
1722 end if;
1723
1724 end clean_dcf_table;
1725
1729 l_session_id := biv_core_pkg.get_session_id;
1726 procedure clean_region_table(p_region varchar2) is
1727 l_session_id NUMBER;
1728 begin
1730 if p_region IN ('BIV_BIN_SR',
1731 'BIV_BIN_SR_ESCALATION',
1732 'BIV_BIN_RESOURCE') then
1733 execute immediate 'delete from BIV_TMP_BIN where
1734 session_id = :l_session_id ' using l_session_id;
1735 elsif (p_region IN ('BIV_RT_CUS_BLOG',
1736 'BIV_RT_SR_SUM_MONITOR',
1737 'BIV_RT_SR_SEV',
1738 'BIV_RT_ESC_SR',
1739 'BIV_RT_CUS_BLOG_DD')) then
1740 execute immediate 'delete from BIV_TMP_RT1 where
1741 session_id = :l_session_id ' using l_session_id;
1742 elsif (p_region IN ('BIV_RT_AGENT_REPORT',
1743 'BIV_RT_MANAGER_REPORT',
1744 'BIV_RT_BACKLOG_BY_STATUS',
1745 'BIV_RT_TASK_ACTIVITY',
1746 'BIV_TASK_SUMMARY',
1747 'BIV_RELATED_TASK',
1748 'BIV_SERVICE_REQUEST')) then
1749 execute immediate 'delete from BIV_TMP_RT2 where
1750 session_id = :l_session_id ' using l_session_id;
1751 elsif (p_region IN ('BIV_HS_PROB_AVOID',
1752 'BIV_HS_PROB_AVOID_RES')) then
1753 execute immediate 'delete from BIV_TMP_HS1 where
1754 session_id = :l_session_id ' using l_session_id;
1755 elsif (p_region IN ('BIV_HS_SR_ESCALATION',
1756 'BIV_HS_SR_ACTIVITY',
1757 'BIV_HS_EACALATION_VIEW',
1758 'BIV_SERVICE_REQUEST',
1759 'BIV_RT_SR_AGE_REPORT')) then
1760 execute immediate 'delete from BIV_TMP_HS2 where
1761 session_id = :l_session_id ' using l_session_id;
1762 elsif (p_region IN ('BIV_TMP_SR_ARRVL',
1763 'BIV_HS_SR_ARRIVAL_TM',
1764 'BIV_HS_SR_ARRIVAL_PRD')) then
1765 execute immediate 'delete from BIV_TMP_SR_ARRVL where
1766 session_id = :l_session_id ' using l_session_id;
1767 else
1768 delete from biv_tmp_rt1 where session_id = l_session_id;
1769 delete from biv_tmp_rt2 where session_id = l_session_id;
1770 delete from biv_tmp_hs1 where session_id = l_session_id;
1771 delete from biv_tmp_hs2 where session_id = l_session_id;
1772 delete from biv_tmp_bin where session_id = l_session_id;
1773 end if;
1774 if (g_debug = 'Y') then
1775 execute immediate 'delete from biv_debug where session_id = :l_session_id or creation_date < sysdate -1 ' using l_session_id;
1776 end if;
1777 commit;
1778 end clean_region_table;
1779 -----------------------
1780 function base_column_description(p_param_str varchar2) return varchar2 is
1781 l_view_by varchar2(80);
1782 l_meaning varchar2(80);
1783 begin
1784 if (g_debug = 'Y') then
1785 biv_core_pkg.biv_debug('AA' ||p_param_str,biv_core_pkg.g_report_id);
1786 commit;
1787 end if;
1788 l_view_by := biv_core_pkg.get_parameter_value(p_param_str,'P_VIEW_BY');
1789 if (g_debug = 'Y') then
1790 biv_core_pkg.biv_debug('Param:'||p_param_str,'g_report_id');
1791 commit;
1792 end if;
1793 select meaning into l_meaning
1794 from fnd_lookups
1795 where lookup_type = 'BIV_VIEW_BY'
1796 and lookup_code = nvl(l_view_by,'MGR');
1797 --dbms_output.put_line(l_meaning);
1798 return(nvl(l_meaning,'Base Column'));
1799 exception
1800 when others then
1801 --return(nvl(l_view_by,p_param_str||'AA'));
1802 return(nvl(l_view_by,'Base Column'));
1803 end;
1804
1805 end;