[Home] [Help]
PACKAGE BODY: APPS.BIV_BIN_ESC_RSC_PKG
Source
1 package body biv_bin_esc_rsc_pkg as
2 /* $Header: bivbescb.pls 115.19 2004/01/27 06:59:29 vganeshk ship $ */
3 ---------------------------------------------
4 procedure sr_esc_bin (p_param_str varchar2) is
5 l_profile varchar2(50) := 'BIV_DASH_' || fnd_global.user_id || '_' ||
6 fnd_global.resp_id;
7 l_report_code varchar2(50) := 'BIV_DASH_ESC_BIN';
8 l_session_id biv_tmp_bin.session_id%type;
9 l_new_param_str varchar2(2000);
10 l_sql_sttmnt varchar2(4000);
11 l_from_list varchar2(500);
12 l_where_clause varchar2(2000);
13 l_cur number;
14 l_dummy number;
15 l_err varchar2(2000);
16 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
17 begin
18 biv_core_pkg.g_report_id := 'BIV_BIN_SR_ESCALATION';
19
20 l_session_id := biv_core_pkg.get_session_id;
21 biv_core_pkg.clean_dcf_table('biv_tmp_bin');
22 if (l_debug = 'Y') then
23 biv_core_pkg.biv_debug('Parameters:'||p_param_str,
24 biv_core_pkg.g_report_id);
25 end if;
26 commit;
27 /*
28 l_new_param_str := 'BIV_TASK_SUMMARY' ||biv_core_pkg.g_param_sep ;
29 l_new_param_str := l_new_param_str || 'jtfBinId' ||
30 biv_core_pkg.g_value_sep || 'BIV_TASK_SUMMARY' ||
31 changed drill down to service request page 5/9/2
32 */
33 -- Change for Bug 3386946
34 l_from_list := ' from jtf_task_references_b r,
35 cs_incidents_b_sec sr,
36 jtf_tasks_b t,
37 cs_incident_statuses_b stat,
38 fnd_lookups lup
39 ' ;
40 biv_core_pkg.get_report_parameters(p_param_str);
41 biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
42 l_where_clause := l_where_clause || '
43 and sr.incident_id = r.OBJECT_ID
44 and sr.incident_status_id = stat.incident_status_id
45 and nvl(stat.close_flag,''N'') <> ''Y''
46 and r.object_type_code = ''SR''
47 and r.reference_code = ''ESC''
48 and r.task_id = t.task_id
49 and t.task_type_id = 22
50 and lup.lookup_type = ''JTF_TASK_ESC_LEVEL''
51 and lup.lookup_code = t.escalation_level
52 ';
53
54 l_sql_sttmnt := '
55 insert into biv_tmp_bin(report_code, col1, col2, col4,session_id)
56 select ''BIV_BIN_SR_ESCALATION'',lup.lookup_code,lup.meaning,
57 count(distinct sr.incident_id),:session_id
58 ' || l_from_list || l_where_clause ||
59 ' group by lup.lookup_code, lup.meaning';
60 if (l_debug = 'Y') then
61 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
62 commit;
63 end if;
64
65 l_cur := dbms_sql.open_cursor;
66 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
67 biv_core_pkg.bind_all_variables(l_cur);
68 dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
69 l_dummy := dbms_sql.execute(l_cur);
70 dbms_sql.close_cursor(l_cur);
71
72 /**********************************************************************
73 insert into biv_tmp_bin(report_code, col1, col2, col4,session_id)
74 select 'BIV_BIN_SR_ESCALATION',lup.lookup_code,lup.meaning,
75 count(distinct t.task_id),l_session_id
76 from jtf_task_references_b r,
77 cs_incidents_all_b ina,
78 jtf_tasks_b t,
79 cs_incident_statuses_b stat,
80 fnd_lookups lup
81 where ina.incident_id = r.OBJECT_ID
82 and ina.incident_status_id = stat.incident_status_id
83 and nvl(stat.close_flag,'N') <> 'Y'
84 and r.object_type_code = 'SR'
85 and r.reference_code = 'ESC'
86 and r.task_id = t.task_id
87 and t.task_type_id = 22
88 and lup.lookup_type = 'JTF_TASK_ESC_LEVEL'
89 and lup.lookup_code = t.escalation_level
90 group by lup.lookup_code, lup.meaning;
91 *********************************************************************/
92 l_new_param_str := 'BIV_SERVICE_REQUEST' ||biv_core_pkg.g_param_sep ||
93 biv_core_pkg.reconstruct_param_str;
94 l_new_param_str := l_new_param_str || 'jtfBinId' ||
95 biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
96 biv_core_pkg.g_param_sep ||
97 'P_BLOG' ||
98 biv_core_pkg.g_value_sep || 'Y' ||
99 biv_core_pkg.g_param_sep ||
100 'P_ESC_LVL' ||
101 biv_core_pkg.g_value_sep ;
102
103 update biv_tmp_bin
104 set col1 = l_new_param_str || col1
105 where report_code = 'BIV_BIN_SR_ESCALATION'
106 and session_id = l_session_id;
107
108 exception
109 when others then
110 rollback;
111 if (l_debug = 'Y') then
112 l_err := 'Error in SR_escalation:'|| substr(sqlerrm,1,1500);
113 biv_core_pkg.biv_debug(l_err, biv_core_pkg.g_report_id);
114 commit;
115 end if;
116 end sr_esc_bin;
117 --------------------------------
118 procedure resource_bin (p_param_str varchar2) is
119 l_session_id biv_tmp_bin.session_id%type;
120 l_new_param_str varchar2(2000);
121 l_sql_sttmnt varchar2(4000);
122 l_from_list varchar2(1000);
123 l_where_clause varchar2(2000);
124 l_cur number;
125 l_dummy number;
126 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
127 l_all fnd_lookups.meaning % type :=
128 biv_core_pkg.get_lookup_meaning('ALL');
129 begin
130 biv_core_pkg.g_report_id := 'BIV_BIN_RESOURCE';
131 l_session_id := biv_core_pkg.get_session_id;
132 biv_core_pkg.clean_dcf_table('biv_tmp_bin');
133
134 biv_core_pkg.get_report_parameters(p_param_str);
135 get_resource_where_clause(l_from_list, l_where_clause);
136
137 l_new_param_str := 'BIV_RT_AGENT_REPORT' ||biv_core_pkg.g_param_sep ||
138 biv_core_pkg.reconstruct_param_str;
139 l_new_param_str := l_new_param_str || 'jtfBinId' ||
140 biv_core_pkg.g_value_sep || 'BIV_RT_AGENT_REPORT' ||
141 biv_core_pkg.g_param_sep ||
142 'P_SRT_BY' || biv_core_pkg.g_value_sep || '1' ||
143 biv_core_pkg.g_param_sep ||
144 'P_RSC' || biv_core_pkg.g_value_sep ;
145
146 l_sql_sttmnt := '
147 insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
148 select ''BIV_BIN_RESOURCE'', rownum, id, descr, total,:session_id
149 from (select nvl(ra.mode_of_availability,''ALL'') id,
150 nvl(ra.mode_of_availability,:all_meaning) descr,
151 count(*) total ' ||
152 l_from_list || l_where_clause || '
153 group by ra.mode_of_availability)';
154
155 if (l_debug = 'Y') then
156 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
157 end if;
158 --
159 l_cur := dbms_sql.open_cursor;
160 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
161 biv_core_pkg.bind_all_variables(l_cur);
162 if (l_debug = 'Y') then
163 biv_core_pkg.biv_debug('Bef session binding',biv_core_pkg.g_report_id);
164 end if;
165 dbms_sql.bind_variable(l_cur,':session_id', l_session_id);
166 if (l_debug = 'Y') then
167 biv_core_pkg.biv_debug('Bef all binding',biv_core_pkg.g_report_id);
168 end if;
169 dbms_sql.bind_variable(l_cur,':all_meaning' , l_all );
170 l_dummy := dbms_sql.execute(l_cur);
171
172 -- web available employee count
173 /* 3/13/02 web available is being inserted in above statement.
174 This is due to change sql Statment.
175 l_from_list := l_from_list || ',
176 jtf_rs_res_availability avl ';
177 l_where_clause := l_where_clause || '
178 and avl.resource_id = rsc.resource_id';
179 l_sql_sttmnt := '
180 insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
181 select ''BIV_BIN_RESOURCE'', rownum, id, descr, total,:session_id
182 from (select ''WEB'' id, ''WEB'' descr,count(*) total ' ||
183 l_from_list || l_where_clause || ')';
184
185 if (l_debug = 'Y') then
186 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
187 commit;
188 end if;
189 l_cur := dbms_sql.open_cursor;
190 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
191 biv_core_pkg.bind_all_variables(l_cur);
192 dbms_sql.bind_variable(l_cur,':session_id', l_session_id);
193 l_dummy := dbms_sql.execute(l_cur);
194 */
195 /**************************************************************************
196 insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
197 select 'BIV_BIN_RESOURCE', rownum, id, descr, total,l_session_id
198 from (select 'ALL' id, 'ALL' descr,count(*) total
199 from jtf_rs_resource_extns
200 where category = 'EMPLOYEE'
201 );
202 insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
203 select 'BIV_BIN_RESOURCE', 2, id, descr, total,l_session_id
204 from (select 'WEB' id, 'WEB' descr,count(*) total
205 from jtf_rs_res_availability
206 );
207 ***************************************/
208 update biv_tmp_bin
209 set col1 = l_new_param_str || col1 ||
210 biv_core_pkg.g_param_sep ||
211 'P_DISP' ||
212 biv_core_pkg.g_value_sep || col4
213 where report_code = 'BIV_BIN_RESOURCE'
214 and session_id = l_session_id;
215 exception
216 when others then
217 if (l_debug = 'Y') then
218 biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
219 commit;
220 end if;
221 end resource_bin;
222 ---------------------------------------------
223 procedure tsk_summry_rep(p_param_str varchar2) is
224 l_esc_lvl varchar2(80);
225 l_session_id biv_tmp_bin.session_id%type;
226 l_new_param_str varchar2(2000);
227 l_sql_sttmnt varchar2(4000);
228 l_from_list varchar2(1000);
229 l_where_clause varchar2(2000);
230 l_cur number;
231 l_dummy number;
232 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
233 begin
234 biv_core_pkg.g_report_id := 'BIV_TASK_SUMMARY';
235 l_session_id := biv_core_pkg.get_session_id;
236 biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
237 --l_esc_lvl := biv_core_pkg.get_parameter_value(p_esc_level, 'P_ESC_LVL');
238 -- Change for Bug 3386946
239 l_from_list := '
240 from jtf_task_references_b r,
241 cs_incidents_b_sec ina,
242 cs_incident_statuses_vl stat,
243 jtf_tasks_b task,
244 jtf_rs_resource_extns rsc,
245 hz_parties p';
246
247 biv_core_pkg.get_report_parameters(p_param_str);
248 biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
249
250 l_where_clause := l_where_clause || '
251 and ina.incident_id = r.OBJECT_ID
252 and r.object_type_code = ''SR''
253 and r.reference_code = ''ESC''
254 and r.TASK_ID = task.task_id
255 and task.task_type_id = 22
256 and ina.incident_status_id = stat.incident_status_id
257 and ina.customer_id = p.party_id
258 and task.owner_id = rsc.resource_id
259 and nvl(stat.close_flag,''N'') <> ''Y''';
260
261 --'X' || biv_core_pkg.g_param_sep || 'task_id=' ||t.task_id,
262 l_sql_sttmnt := '
263 insert into biv_tmp_rt2(report_code, rowno, id,col2, col4,
264 col6, col8, col10, col12, col13,session_id)
265 select ''BIV_TASK_SUMMARY'',
266 rownum,
267 task.task_id,
268 task.task_number,
269 p.party_name,
270 stat.name,
271 ina.incident_date,
272 task.planned_end_date,
273 rsc.source_name,
274 reason_code,
275 :session_id ' || l_from_list || l_where_clause;
276
277 if (l_debug = 'Y') then
278 biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
279 end if;
280 l_cur := dbms_sql.open_cursor;
281 dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
282 biv_core_pkg.bind_all_variables(l_cur);
283 dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
284 l_dummy := dbms_sql.execute(l_cur);
285 dbms_sql.close_cursor(l_cur);
286 update biv_tmp_rt2
287 set col1 = 'task' || biv_core_pkg.g_param_sep ||
288 'task_id' || biv_core_pkg.g_value_sep || id
289 ;
290 update biv_tmp_rt2 t
291 set col14 = ( select meaning from fnd_lookups lup
292 where lup.lookup_code = t.col13
293 and lup.lookup_type = 'JTF_TASK_REASON_CODES'
294 );
295 /*
296 insert into biv_tmp_rt2(report_code, rowno, col1,col2, col4,
297 col6, col8, col10, col12, col14,session_id)
298 select 'BIV_TASK_SUMMARY',
299 rownum,
300 'X' || biv_core_pkg.g_param_sep || 'task_id=' ||t.task_id,
301 t.task_number,
302 p.party_name,
303 stat.name,
304 ina.incident_date,
305 t.planned_end_date,
306 rsc.source_name,
307 reason_code,
308 l_session_id
309 from jtf_task_references_b r,
310 cs_incidents_all_b ina,
311 cs_incident_statuses_vl stat,
312 jtf_tasks_b t,
313 jtf_rs_resource_extns rsc,
314 hz_parties p
315 where ina.incident_id = r.OBJECT_ID
316 and r.object_type_code = 'SR'
317 and r.reference_code = 'ESC'
318 and r.TASK_ID = t.task_id
319 and t.task_type_id = 22
320 and ina.incident_status_id = stat.incident_status_id
321 and ina.customer_id = p.party_id
322 and t.owner_id = rsc.resource_id
323 and t.escalation_level = l_esc_lvl
324 and nvl(stat.close_flag,'N') <> 'Y';
325 */
326 commit;
327 exception
328 when others then
329 if (l_debug = 'Y') then
330 biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
331 end if;
332 end tsk_summry_rep;
333 --------------------------------------------
334 procedure rltd_task_rep(p_sr_id varchar2) is
335 l_sr_id varchar2(20);
336 l_session_id biv_tmp_bin.session_id%type;
337 l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
338 begin
339 l_session_id := biv_core_pkg.get_session_id;
340 biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
341 l_sr_id := biv_core_pkg.get_parameter_value(p_sr_id, 'P_SR_ID');
342 insert into biv_tmp_rt2(report_code, rowno, col1,col2, col4, col6,
343 col8, col10, col12, col14, col16,session_id)
344 select 'BIV_RELATED_TASK_REPORT', rownum,
345 tsk.task_id,
346 tsk.task_number,
347 tskl.task_name,
348 tskl.description,
349 pr.name,
350 'ESC',
351 stat.name,
352 rsc.source_name,
353 typ.name,
354 l_session_id
355 from jtf_tasks_b tsk,
356 jtf_tasks_tl tskl,
357 jtf_task_priorities_vl pr,
358 jtf_task_statuses_vl stat,
359 jtf_task_types_vl typ,
363 and tsk.task_status_id = stat.task_status_id
360 jtf_rs_resource_extns rsc
361 where tsk.source_object_type_code = 'SR'
362 and tsk.source_object_id = to_number(l_sr_id)
364 and tsk.task_type_id = typ.task_type_id
365 and tsk.task_priority_id = pr.task_priority_id
366 and tsk.owner_id = rsc.resource_id (+)
367 and tsk.task_id = tskl.task_id (+)
368 and userenv('LANG') = tskl.language (+)
369 ;
370 end rltd_task_rep;
371 ---------------------------------------------------
372 procedure get_resource_where_clause (p_from_list out nocopy varchar2,
373 p_where_clause out nocopy varchar2) is
374 begin
375 p_from_list := '
376 from jtf_rs_resource_extns rsc,
377 jtf_objects_vl o,
378 jtf_object_usages ou,
379 jtf_rs_res_availability ra ';
380 -- to_date removed because of GSCC fail
381 p_where_clause := '
382 WHERE rsc.category = o.object_code
383 and o.object_code = ou.object_code
384 and ou.object_user_code = ''RESOURCE_CATEGORIES''
385 and rsc.resource_id = ra.resource_id (+)
386 and sysdate between
387 nvl(rsc.start_date_active,sysdate-1)
388 and nvl(rsc.end_date_active, sysdate+1) ';
389 -----
390 if (biv_core_pkg.g_agrp_cnt > 0 ) then
391 p_from_list := p_from_list || ',
392 jtf_rs_groups_denorm adnorm1,
393 jtf_rs_group_members agmmbr';
394 p_where_clause := p_where_clause || '
395 and agmmbr.group_id = adnorm1.group_id
396 and rsc.resource_id = agmmbr.resource_id ';
397 end if;
398 -----
399 if (biv_core_pkg.g_ogrp_cnt > 0) then
400 p_from_list := p_from_list || ',
401 jtf_rs_groups_denorm odnorm1,
402 jtf_rs_group_members ogmmbr';
403 p_where_clause := p_where_clause || '
404 and ogmmbr.group_id = odnorm1.group_id
405 and rsc.resource_id = ogmmbr.resource_id ';
406 end if;
407 -----
408 /* 3/13/02 where clause for resource changed. resources are now obtained
409 by joining jtf_rs_resurce_extns with jtf_object, jtf_object_usages
410 and jtd_res_availability tables.
411
412 p_where_clause := p_where_clause || '
413 and category = ''EMPLOYEE''';
414 */
415 biv_core_pkg.add_a_condition(biv_core_pkg.g_ogrp,
416 biv_core_pkg.g_ogrp_cnt,
417 'odnorm1', 'parent_group_id',
418 null, p_where_clause);
419 biv_core_pkg.add_a_condition(biv_core_pkg.g_agrp,
420 biv_core_pkg.g_agrp_cnt,
421 'adnorm1', 'parent_group_id',
422 null, p_where_clause);
423 biv_core_pkg.add_a_condition(biv_core_pkg.g_mgr_id,
424 biv_core_pkg.g_mgr_id_cnt,
425 'rsc', 'source_mgr_id',
426 null, p_where_clause);
427 end get_resource_where_clause;
428 -----------------------------
429 end;