[Home] [Help]
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);
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;
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';
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,
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:
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);
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:
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 ' ||
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:
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'
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: );
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
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);
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');