[Home] [Help]
28: l_ttl_recs number;
29: l_ttl_meaning fnd_lookups.meaning % type;
30: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
31: begin
32: biv_core_pkg.g_report_id := 'BIV_RT_BACKLOG_BY_STATUS';
33: l_session_id := biv_core_pkg.get_session_id;
34: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
35:
36: x_status1 := fnd_profile.value('BIV:INC_STATUS_1');
29: l_ttl_meaning fnd_lookups.meaning % type;
30: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
31: begin
32: biv_core_pkg.g_report_id := 'BIV_RT_BACKLOG_BY_STATUS';
33: l_session_id := biv_core_pkg.get_session_id;
34: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
35:
36: x_status1 := fnd_profile.value('BIV:INC_STATUS_1');
37: x_status2 := fnd_profile.value('BIV:INC_STATUS_2');
30: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
31: begin
32: biv_core_pkg.g_report_id := 'BIV_RT_BACKLOG_BY_STATUS';
33: l_session_id := biv_core_pkg.get_session_id;
34: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
35:
36: x_status1 := fnd_profile.value('BIV:INC_STATUS_1');
37: x_status2 := fnd_profile.value('BIV:INC_STATUS_2');
38: x_status3 := fnd_profile.value('BIV:INC_STATUS_3');
36: x_status1 := fnd_profile.value('BIV:INC_STATUS_1');
37: x_status2 := fnd_profile.value('BIV:INC_STATUS_2');
38: x_status3 := fnd_profile.value('BIV:INC_STATUS_3');
39:
40: biv_core_pkg.get_report_parameters(p_param_str);
41:
42: if (l_debug = 'Y') then
43: biv_core_pkg.biv_debug('Parameter:'||p_param_str,
44: biv_core_pkg.g_report_id);
39:
40: biv_core_pkg.get_report_parameters(p_param_str);
41:
42: if (l_debug = 'Y') then
43: biv_core_pkg.biv_debug('Parameter:'||p_param_str,
44: biv_core_pkg.g_report_id);
45: biv_core_pkg.biv_debug('Status Profiles are:'||x_status1||','||
46: x_status2||','||
47: x_status3, 'BIV_RT_SR_BACKLOG');
40: biv_core_pkg.get_report_parameters(p_param_str);
41:
42: if (l_debug = 'Y') then
43: biv_core_pkg.biv_debug('Parameter:'||p_param_str,
44: biv_core_pkg.g_report_id);
45: biv_core_pkg.biv_debug('Status Profiles are:'||x_status1||','||
46: x_status2||','||
47: x_status3, 'BIV_RT_SR_BACKLOG');
48: end if;
41:
42: if (l_debug = 'Y') then
43: biv_core_pkg.biv_debug('Parameter:'||p_param_str,
44: biv_core_pkg.g_report_id);
45: biv_core_pkg.biv_debug('Status Profiles are:'||x_status1||','||
46: x_status2||','||
47: x_status3, 'BIV_RT_SR_BACKLOG');
48: end if;
49:
48: end if;
49:
50: -- Change for Bug 3386946
51: x_from_list := ' from cs_incidents_b_sec sr, cs_incident_statuses_b stat ';
52: biv_core_pkg.get_where_clause(x_from_list,x_where_clause);
53: x_where_clause := x_where_clause ||
54: ' and sr.incident_status_id = stat.incident_status_id' ||
55: ' and nvl(stat.close_flag,''N'') != ''Y''';
56:
54: ' and sr.incident_status_id = stat.incident_status_id' ||
55: ' and nvl(stat.close_flag,''N'') != ''Y''';
56:
57: x_sql_sttmnt := 'select ''BACKLOG'', ' ||
58: biv_core_pkg.g_base_column || ' col1,' ||
59: 'sum(decode(stat.close_flag,''Y'',0,1)) col4,
60: sum(decode(sr.incident_status_id, :x_stat1,1,0)) col6,
61: sum(decode(sr.incident_status_id, :x_stat2,1,0)) col8,
62: sum(decode(sr.incident_status_id, :x_stat3,1,0)) col10,
61: sum(decode(sr.incident_status_id, :x_stat2,1,0)) col8,
62: sum(decode(sr.incident_status_id, :x_stat3,1,0)) col10,
63: :session_id '||
64: x_from_list || x_where_clause ||
65: ' group by ' || biv_core_pkg.g_base_column
66: -- || ' order by ' || nvl(biv_core_pkg.g_srt_by,'2')
67: ;
68: x_sql_sttmnt := 'insert into biv_tmp_rt2(report_code,col1,col4,
69: col6,col8,col10,session_id)
62: sum(decode(sr.incident_status_id, :x_stat3,1,0)) col10,
63: :session_id '||
64: x_from_list || x_where_clause ||
65: ' group by ' || biv_core_pkg.g_base_column
66: -- || ' order by ' || nvl(biv_core_pkg.g_srt_by,'2')
67: ;
68: x_sql_sttmnt := 'insert into biv_tmp_rt2(report_code,col1,col4,
69: col6,col8,col10,session_id)
70: ' || x_sql_sttmnt ;
69: col6,col8,col10,session_id)
70: ' || x_sql_sttmnt ;
71:
72: if (l_debug = 'Y') then
73: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
74: end if;
75: x_cur := dbms_sql.open_cursor;
76: dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
77: biv_core_pkg.bind_all_variables(x_cur);
73: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
74: end if;
75: x_cur := dbms_sql.open_cursor;
76: dbms_sql.parse(x_cur,x_sql_sttmnt,dbms_sql.native);
77: biv_core_pkg.bind_all_variables(x_cur);
78: dbms_sql.bind_variable(x_cur,':x_stat1', x_status1);
79: dbms_sql.bind_variable(x_cur,':x_stat2', x_status2);
80: dbms_sql.bind_variable(x_cur,':x_stat3', x_status3);
81: dbms_sql.bind_variable(x_cur,':session_id', l_session_id);
80: dbms_sql.bind_variable(x_cur,':x_stat3', x_status3);
81: dbms_sql.bind_variable(x_cur,':session_id', l_session_id);
82: x_dummy := dbms_sql.execute(x_cur);
83:
84: biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
85: update biv_tmp_rt2
86: set col12 = col4 - col6 - col8 - col10
87: where report_code = 'BACKLOG'
88: and session_id = l_session_id;
86: set col12 = col4 - col6 - col8 - col10
87: where report_code = 'BACKLOG'
88: and session_id = l_session_id;
89:
90: select decode(biv_core_pkg.g_srt_by, '2', 'to_number(col4) desc',
91: '3', 'to_number(col6) desc',
92: '4', 'to_number(col8) desc',
93: '5', 'to_number(col10) desc',
94: '6', 'to_number(col12) desc',
104: from biv_tmp_rt2
105: where report_code = ''BACKLOG''
106: and session_id = :session_id
107: order by ' || l_order_by || ')
108: where rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,10);
109: if (l_debug = 'Y') then
110: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
111: end if;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
106: and session_id = :session_id
107: order by ' || l_order_by || ')
108: where rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,10);
109: if (l_debug = 'Y') then
110: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
111: end if;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
108: where rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,10);
109: if (l_debug = 'Y') then
110: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
111: end if;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
109: if (l_debug = 'Y') then
110: biv_core_pkg.biv_debug(x_sql_sttmnt,biv_core_pkg.g_report_id);
111: end if;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
111: end if;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
112: execute immediate x_sql_sttmnt using l_session_id,biv_core_pkg.g_disp;
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
113: x_param_name := biv_core_pkg.param_for_base_col;
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
114:
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
115: l_ttl_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
116: biv_core_pkg.reconstruct_param_str ||
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
117: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
118: biv_core_pkg.g_param_sep;
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
119: biv_core_pkg.reset_view_by_param;
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
120: l_new_param_str := 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
121: biv_core_pkg.reconstruct_param_str ||
122: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST' ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
123: biv_core_pkg.g_param_sep ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
124: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
125: --Change for bug 3093779 and enh 2914005 appended P_PREVR
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
126: update biv_tmp_rt2
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
127: set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
128: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
129: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
130: col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
131: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
132: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
133: col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
141: biv_core_pkg.g_value_sep || 'Y' ||
134: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
135: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
141: biv_core_pkg.g_value_sep || 'Y' ||
142: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
136: col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
141: biv_core_pkg.g_value_sep || 'Y' ||
142: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
143: creation_date = sysdate
144: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
137: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
141: biv_core_pkg.g_value_sep || 'Y' ||
142: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
143: creation_date = sysdate
144: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
145: and session_id = l_session_id
138: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
139: col3= l_new_param_str || col1 ||
140: biv_core_pkg.g_param_sep || 'P_BLOG' ||
141: biv_core_pkg.g_value_sep || 'Y' ||
142: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
143: creation_date = sysdate
144: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
145: and session_id = l_session_id
146: ;
154: select count(*) into l_ttl_recs
155: from biv_tmp_rt2
156: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
157: and session_id = l_session_id;
158: if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
159: if (l_debug = 'Y') then
160: biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
161: end if;
162: insert into biv_tmp_rt2 (report_code, rowno,
156: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
157: and session_id = l_session_id;
158: if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
159: if (l_debug = 'Y') then
160: biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
161: end if;
162: insert into biv_tmp_rt2 (report_code, rowno,
163: col4, col6, col8, col10, col12, col13,session_id)
164: select report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
167: where session_id = l_session_id
168: and report_code = 'BIV_RT_BACKLOG_BY_STATUS'
169: group by report_code, session_id;
170:
171: if (biv_core_pkg.g_view_by = 'AGRP') then
172: l_ttl_param_str := l_ttl_param_str || 'P_AGRP_LVL' ||
173: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
169: group by report_code, session_id;
170:
171: if (biv_core_pkg.g_view_by = 'AGRP') then
172: l_ttl_param_str := l_ttl_param_str || 'P_AGRP_LVL' ||
173: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
176: l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
170:
171: if (biv_core_pkg.g_view_by = 'AGRP') then
172: l_ttl_param_str := l_ttl_param_str || 'P_AGRP_LVL' ||
173: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
176: l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
178: biv_core_pkg.g_param_sep;
171: if (biv_core_pkg.g_view_by = 'AGRP') then
172: l_ttl_param_str := l_ttl_param_str || 'P_AGRP_LVL' ||
173: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
176: l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
178: biv_core_pkg.g_param_sep;
179: end if;
173: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
176: l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
178: biv_core_pkg.g_param_sep;
179: end if;
180:
181: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
174: biv_core_pkg.g_param_sep;
175: elsif (biv_core_pkg.g_view_by = 'OGRP') then
176: l_ttl_param_str := l_ttl_param_str || 'P_OGRP_LVL' ||
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
178: biv_core_pkg.g_param_sep;
179: end if;
180:
181: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
182: --Change for bug 3093779 appended P_UA=N to all the total urls
177: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
178: biv_core_pkg.g_param_sep;
179: end if;
180:
181: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
182: --Change for bug 3093779 appended P_UA=N to all the total urls
183: --Change for enh 2914005 appended P_PREVR to all the urls
184: update biv_tmp_rt2
185: set col5= l_ttl_param_str||
182: --Change for bug 3093779 appended P_UA=N to all the total urls
183: --Change for enh 2914005 appended P_PREVR to all the urls
184: update biv_tmp_rt2
185: set col5= l_ttl_param_str||
186: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
187: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
183: --Change for enh 2914005 appended P_PREVR to all the urls
184: update biv_tmp_rt2
185: set col5= l_ttl_param_str||
186: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
187: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
184: update biv_tmp_rt2
185: set col5= l_ttl_param_str||
186: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
187: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
186: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
187: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
187: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
188: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
189: col7= l_ttl_param_str||
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
190: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
191: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
192: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
193: col9= l_ttl_param_str||
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
194: 'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
195: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
203: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
196: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
197: col11=l_ttl_param_str||
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
203: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
204: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
198: 'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
203: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
204: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
205: col2= l_ttl_meaning,
206: creation_date = sysdate
199: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
203: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
204: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
205: col2= l_ttl_meaning,
206: creation_date = sysdate
207: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
200: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
201: col3= l_ttl_param_str ||
202: 'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
203: biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
204: biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
205: col2= l_ttl_meaning,
206: creation_date = sysdate
207: where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
208: and session_id = l_session_id
210: ;
211: end if;
212: ---
213: ---
214: biv_core_pkg.g_report_id := 'NULL';
215: if (l_debug = 'Y') then
216: biv_core_pkg.biv_debug('End of Report',biv_core_pkg.g_report_id);
217: end if;
218: exception
212: ---
213: ---
214: biv_core_pkg.g_report_id := 'NULL';
215: if (l_debug = 'Y') then
216: biv_core_pkg.biv_debug('End of Report',biv_core_pkg.g_report_id);
217: end if;
218: exception
219: when others then
220: if (l_debug = 'Y') then
217: end if;
218: exception
219: when others then
220: if (l_debug = 'Y') then
221: biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
222: end if;
223: end sr_backlog;
224: procedure task_activity(p_param_str varchar2) as
225: l_cur number;
237: l_ttl_recs number;
238: l_ttl_meaning fnd_lookups.meaning % type;
239: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
240: begin
241: biv_core_pkg.g_report_id := 'BIV_RT_TASK_ACTIVITY';
242: l_session_id := biv_core_pkg.get_session_id;
243: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
244:
245: biv_core_pkg.get_report_parameters(p_param_str);
238: l_ttl_meaning fnd_lookups.meaning % type;
239: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
240: begin
241: biv_core_pkg.g_report_id := 'BIV_RT_TASK_ACTIVITY';
242: l_session_id := biv_core_pkg.get_session_id;
243: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
244:
245: biv_core_pkg.get_report_parameters(p_param_str);
246: -- Change for Bug 3386946
239: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
240: begin
241: biv_core_pkg.g_report_id := 'BIV_RT_TASK_ACTIVITY';
242: l_session_id := biv_core_pkg.get_session_id;
243: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
244:
245: biv_core_pkg.get_report_parameters(p_param_str);
246: -- Change for Bug 3386946
247: l_from_list := ' from cs_incidents_b_sec sr, jtf_tasks_b task' ||
241: biv_core_pkg.g_report_id := 'BIV_RT_TASK_ACTIVITY';
242: l_session_id := biv_core_pkg.get_session_id;
243: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
244:
245: biv_core_pkg.get_report_parameters(p_param_str);
246: -- Change for Bug 3386946
247: l_from_list := ' from cs_incidents_b_sec sr, jtf_tasks_b task' ||
248: ', jtf_task_statuses_b tstat, cs_incident_statuses_b stat';
249: biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
245: biv_core_pkg.get_report_parameters(p_param_str);
246: -- Change for Bug 3386946
247: l_from_list := ' from cs_incidents_b_sec sr, jtf_tasks_b task' ||
248: ', jtf_task_statuses_b tstat, cs_incident_statuses_b stat';
249: biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
250: l_where_clause := l_where_clause ||
251: ' and sr.incident_status_id = stat.incident_status_id' ||
252: ' and nvl(stat.close_flag,''N'') != ''Y''' ||
253: ' and sr.incident_id = task.source_object_id' ||
255: ' and task.task_status_id = tstat.task_status_id' ||
256: ' and nvl(tstat.closed_flag,''N'') != ''Y'''
257: ;
258: l_sql_sttmnt := '
259: select ' || biv_core_pkg.g_base_column || ' base_col,
260: count(distinct sr.incident_id) no_of_srs,
261: count(distinct task.task_id) no_of_tasks';
262:
263: l_sql_sttmnt := '
265: col1,col4, col6,session_id)
266: select ''X'', rownum,
267: base_col, no_of_srs, no_of_tasks, :session_id
268: from (' || l_sql_sttmnt || l_from_list || l_where_clause ||
269: ' group by ' || biv_core_pkg.g_base_column || ')';
270: if (l_debug = 'Y') then
271: biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_RT_TASK_ACTIVITY');
272: end if;
273: l_cur := dbms_sql.open_cursor;
267: base_col, no_of_srs, no_of_tasks, :session_id
268: from (' || l_sql_sttmnt || l_from_list || l_where_clause ||
269: ' group by ' || biv_core_pkg.g_base_column || ')';
270: if (l_debug = 'Y') then
271: biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_RT_TASK_ACTIVITY');
272: end if;
273: l_cur := dbms_sql.open_cursor;
274: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
275: biv_core_pkg.bind_all_variables(l_cur);
271: biv_core_pkg.biv_debug(l_sql_sttmnt,'BIV_RT_TASK_ACTIVITY');
272: end if;
273: l_cur := dbms_sql.open_cursor;
274: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
275: biv_core_pkg.bind_all_variables(l_cur);
276: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
277: l_dummy := dbms_sql.execute(l_cur);
278: biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
279:
274: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
275: biv_core_pkg.bind_all_variables(l_cur);
276: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
277: l_dummy := dbms_sql.execute(l_cur);
278: biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
279:
280: select decode(biv_core_pkg.g_srt_by,'2','to_number(col4) desc',
281: '3','to_number(col6) desc',
282: 'col2 asc')
276: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
277: l_dummy := dbms_sql.execute(l_cur);
278: biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
279:
280: select decode(biv_core_pkg.g_srt_by,'2','to_number(col4) desc',
281: '3','to_number(col6) desc',
282: 'col2 asc')
283: into l_order_by
284: from dual;
291: from biv_tmp_rt2
292: where report_code =''X''
293: and session_id = :session_id
294: order by ' || l_order_by || ' ,col2)
295: where rownum <= :rows_to_display ';--|| nvl(biv_core_pkg.g_disp,'10') ;
296: execute immediate l_sql_sttmnt using l_session_id, biv_core_pkg.g_disp;
297: if (l_debug = 'Y') then
298: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
299: end if;
292: where report_code =''X''
293: and session_id = :session_id
294: order by ' || l_order_by || ' ,col2)
295: where rownum <= :rows_to_display ';--|| nvl(biv_core_pkg.g_disp,'10') ;
296: execute immediate l_sql_sttmnt using l_session_id, biv_core_pkg.g_disp;
297: if (l_debug = 'Y') then
298: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
299: end if;
300:
294: order by ' || l_order_by || ' ,col2)
295: where rownum <= :rows_to_display ';--|| nvl(biv_core_pkg.g_disp,'10') ;
296: execute immediate l_sql_sttmnt using l_session_id, biv_core_pkg.g_disp;
297: if (l_debug = 'Y') then
298: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
299: end if;
300:
301: l_param_name := biv_core_pkg.param_for_base_col;
302:
297: if (l_debug = 'Y') then
298: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
299: end if;
300:
301: l_param_name := biv_core_pkg.param_for_base_col;
302:
303: l_ttl_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' ||
304: biv_core_pkg.g_param_sep ||
305: biv_core_pkg.reconstruct_param_str ||
300:
301: l_param_name := biv_core_pkg.param_for_base_col;
302:
303: l_ttl_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' ||
304: biv_core_pkg.g_param_sep ||
305: biv_core_pkg.reconstruct_param_str ||
306: 'jtfBinId'||biv_core_pkg.g_value_sep ||
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
301: l_param_name := biv_core_pkg.param_for_base_col;
302:
303: l_ttl_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' ||
304: biv_core_pkg.g_param_sep ||
305: biv_core_pkg.reconstruct_param_str ||
306: 'jtfBinId'||biv_core_pkg.g_value_sep ||
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
309:
302:
303: l_ttl_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' ||
304: biv_core_pkg.g_param_sep ||
305: biv_core_pkg.reconstruct_param_str ||
306: 'jtfBinId'||biv_core_pkg.g_value_sep ||
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
304: biv_core_pkg.g_param_sep ||
305: biv_core_pkg.reconstruct_param_str ||
306: 'jtfBinId'||biv_core_pkg.g_value_sep ||
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
306: 'jtfBinId'||biv_core_pkg.g_value_sep ||
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
307: 'BIV_SERVICE_REQUEST_TASKS' ||
308: biv_core_pkg.g_param_sep;
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
308: biv_core_pkg.g_param_sep;
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
309:
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
310: l_ttl_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
311: biv_core_pkg.reconstruct_param_str ||
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
312: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
313: biv_core_pkg.g_param_sep;
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
314: biv_core_pkg.reset_view_by_param;
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
315: l_new_param_str1 := 'BIV_SERVICE_REQUEST_TASKS' || biv_core_pkg.g_param_sep ||
316: biv_core_pkg.reconstruct_param_str ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
317: 'jtfBinId'||biv_core_pkg.g_value_sep ||'BIV_SERVICE_REQUEST_TASKS' ||
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
318: biv_core_pkg.g_param_sep ||
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
319: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
327: update biv_tmp_rt2
320:
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
321: l_new_param_str2 := 'BIV_OPEN_TASKS' || biv_core_pkg.g_param_sep ||
322: biv_core_pkg.reconstruct_param_str ||
323: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_OPEN_TASKS' ||
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
324: biv_core_pkg.g_param_sep ||
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
330: col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
331: biv_core_pkg.g_param_sep || 'P_OTT' ||
332: biv_core_pkg.g_value_sep || 'Y',
325: biv_core_pkg.param_for_base_col || biv_core_pkg.g_value_sep;
326:
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
330: col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
331: biv_core_pkg.g_param_sep || 'P_OTT' ||
332: biv_core_pkg.g_value_sep || 'Y',
333: creation_date = sysdate
326:
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
330: col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
331: biv_core_pkg.g_param_sep || 'P_OTT' ||
332: biv_core_pkg.g_value_sep || 'Y',
333: creation_date = sysdate
334: where report_code = 'BIV_RT_TASK_ACTIVITY'
327: update biv_tmp_rt2
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
330: col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
331: biv_core_pkg.g_param_sep || 'P_OTT' ||
332: biv_core_pkg.g_value_sep || 'Y',
333: creation_date = sysdate
334: where report_code = 'BIV_RT_TASK_ACTIVITY'
335: and session_id = l_session_id
328: set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
329: biv_core_pkg.g_param_sep,
330: col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
331: biv_core_pkg.g_param_sep || 'P_OTT' ||
332: biv_core_pkg.g_value_sep || 'Y',
333: creation_date = sysdate
334: where report_code = 'BIV_RT_TASK_ACTIVITY'
335: and session_id = l_session_id
336: ;
343: select count(*) into l_ttl_recs
344: from biv_tmp_rt2
345: where report_code = 'BIV_RT_TASK_ACTIVITY'
346: and session_id = l_session_id;
347: if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
348: if (l_debug = 'Y') then
349: biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
350: end if;
351: insert into biv_tmp_rt2 (report_code, rowno,
345: where report_code = 'BIV_RT_TASK_ACTIVITY'
346: and session_id = l_session_id;
347: if ( l_ttl_recs > 1 and l_ttl_recs < biv_core_pkg.g_disp ) then
348: if (l_debug = 'Y') then
349: biv_core_pkg.biv_debug('Adding Total row',biv_core_pkg.g_report_id);
350: end if;
351: insert into biv_tmp_rt2 (report_code, rowno,
352: col4, col6, col13,session_id)
353: select report_code, max(rowno) + 1, sum(col4), sum(col6),
357: and report_code = 'BIV_RT_TASK_ACTIVITY'
358: group by report_code, session_id;
359: end if;
360:
361: if (biv_core_pkg.g_view_by = 'AGRP') then
362: l_ttl_param_str1 := l_ttl_param_str1 || 'P_AGRP_LVL' ||
363: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
359: end if;
360:
361: if (biv_core_pkg.g_view_by = 'AGRP') then
362: l_ttl_param_str1 := l_ttl_param_str1 || 'P_AGRP_LVL' ||
363: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
360:
361: if (biv_core_pkg.g_view_by = 'AGRP') then
362: l_ttl_param_str1 := l_ttl_param_str1 || 'P_AGRP_LVL' ||
363: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
362: l_ttl_param_str1 := l_ttl_param_str1 || 'P_AGRP_LVL' ||
363: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
363: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
364: biv_core_pkg.g_param_sep;
365: l_ttl_param_str2 := l_ttl_param_str2 || 'P_AGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
372: l_ttl_param_str2 := l_ttl_param_str2 || 'P_OGRP_LVL' ||
366: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
372: l_ttl_param_str2 := l_ttl_param_str2 || 'P_OGRP_LVL' ||
373: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
374: biv_core_pkg.g_param_sep;
367: biv_core_pkg.g_param_sep;
368: elsif (biv_core_pkg.g_view_by = 'OGRP') then
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
372: l_ttl_param_str2 := l_ttl_param_str2 || 'P_OGRP_LVL' ||
373: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
374: biv_core_pkg.g_param_sep;
375: end if;
369: l_ttl_param_str1 := l_ttl_param_str1 || 'P_OGRP_LVL' ||
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
372: l_ttl_param_str2 := l_ttl_param_str2 || 'P_OGRP_LVL' ||
373: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
374: biv_core_pkg.g_param_sep;
375: end if;
376:
377: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
370: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
371: biv_core_pkg.g_param_sep;
372: l_ttl_param_str2 := l_ttl_param_str2 || 'P_OGRP_LVL' ||
373: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
374: biv_core_pkg.g_param_sep;
375: end if;
376:
377: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
378: update biv_tmp_rt2
373: biv_core_pkg.g_value_sep || biv_core_pkg.g_lvl ||
374: biv_core_pkg.g_param_sep;
375: end if;
376:
377: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
378: update biv_tmp_rt2
379: set col5= l_ttl_param_str2||
380: biv_core_pkg.g_param_sep,
381: col3= l_ttl_param_str1||
376:
377: l_ttl_meaning := biv_core_pkg.get_lookup_meaning('TOTAL');
378: update biv_tmp_rt2
379: set col5= l_ttl_param_str2||
380: biv_core_pkg.g_param_sep,
381: col3= l_ttl_param_str1||
382: biv_core_pkg.g_param_sep || 'P_OTT' ||
383: biv_core_pkg.g_value_sep || 'Y',
384: col2= l_ttl_meaning,
378: update biv_tmp_rt2
379: set col5= l_ttl_param_str2||
380: biv_core_pkg.g_param_sep,
381: col3= l_ttl_param_str1||
382: biv_core_pkg.g_param_sep || 'P_OTT' ||
383: biv_core_pkg.g_value_sep || 'Y',
384: col2= l_ttl_meaning,
385: creation_date = sysdate
386: where report_code = 'BIV_RT_TASK_ACTIVITY'
379: set col5= l_ttl_param_str2||
380: biv_core_pkg.g_param_sep,
381: col3= l_ttl_param_str1||
382: biv_core_pkg.g_param_sep || 'P_OTT' ||
383: biv_core_pkg.g_value_sep || 'Y',
384: col2= l_ttl_meaning,
385: creation_date = sysdate
386: where report_code = 'BIV_RT_TASK_ACTIVITY'
387: and session_id = l_session_id
387: and session_id = l_session_id
388: and col13 = 'Y';
389: --
390: --
391: biv_core_pkg.g_report_id := 'NULL';
392: exception
393: when others then
394: rollback;
395: if (l_debug = 'Y') then
392: exception
393: when others then
394: rollback;
395: if (l_debug = 'Y') then
396: biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
397: end if;
398: end;
399: ------------------------------------------------------
400: procedure open_tasks(p_param_str varchar2) as
409: l_dummy number;
410: l_session_id biv_tmp_rt2.session_id % type;
411: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
412: begin
413: l_session_id := biv_core_pkg.get_session_id;
414: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
415:
416: biv_core_pkg.get_report_parameters(p_param_str);
417: -- Change for Bug 3386946
410: l_session_id biv_tmp_rt2.session_id % type;
411: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
412: begin
413: l_session_id := biv_core_pkg.get_session_id;
414: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
415:
416: biv_core_pkg.get_report_parameters(p_param_str);
417: -- Change for Bug 3386946
418: l_from_list := ' cs_incidents_b_sec sr,
412: begin
413: l_session_id := biv_core_pkg.get_session_id;
414: biv_core_pkg.clean_dcf_table('biv_tmp_rt2');
415:
416: biv_core_pkg.get_report_parameters(p_param_str);
417: -- Change for Bug 3386946
418: l_from_list := ' cs_incidents_b_sec sr,
419: cs_incident_statuses_b stat,
420: jtf_tasks_b task,
420: jtf_tasks_b task,
421: jtf_task_statuses_vl tstat,
422: /*jtf_rs_resource_ext ns rsc, */
423: jtf_rs_resource_extns tsk_rsc ';
424: biv_core_pkg.get_where_clause(l_from_list,l_where_clause);
425: l_where_clause := l_where_clause ||
426: ' and sr.incident_status_id = stat.incident_status_id
427: and nvl(stat.close_flag,''N'') != ''Y''
428: --and sr.incident_owner_id = rsc.resource_id
438: col8, col10,
439: col12, col14, col15,col16, col17,
440: col19,session_id,creation_date)
441: select ''BIV_OPEN_TASKS'', ''task' ||
442: biv_core_pkg.g_param_sep || 'task_id' || biv_core_pkg.g_value_sep
443: || ''' || task.task_id,
444: task.task_number,
445: task.owner_id, tstat.name, null,
446: task.creation_date, task.last_update_date,
444: task.task_number,
445: task.owner_id, tstat.name, null,
446: task.creation_date, task.last_update_date,
447: task.escalation_level,
448: ''X' || biv_core_pkg.g_param_sep ||'SR_ID'||
449: biv_core_pkg.g_value_sep || ''' || task.source_object_id,
450: sr.incident_number,
451: sr.incident_owner_id,
452: sr.inventory_item_id,
445: task.owner_id, tstat.name, null,
446: task.creation_date, task.last_update_date,
447: task.escalation_level,
448: ''X' || biv_core_pkg.g_param_sep ||'SR_ID'||
449: biv_core_pkg.g_value_sep || ''' || task.source_object_id,
450: sr.incident_number,
451: sr.incident_owner_id,
452: sr.inventory_item_id,
453: :session_id,sysdate
453: :session_id,sysdate
454: from ' || l_from_list || l_where_clause;
455:
456: if (l_debug = 'Y') then
457: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
458: end if;
459: l_cur := dbms_sql.open_cursor;
460: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
461: biv_core_pkg.bind_all_variables(l_cur);
457: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
458: end if;
459: l_cur := dbms_sql.open_cursor;
460: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
461: biv_core_pkg.bind_all_variables(l_cur);
462: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
463: l_dummy := dbms_sql.execute(l_cur);
464: biv_core_pkg.update_description('P_AGENT_ID','col3' ,'col4' , 'biv_tmp_rt2');
465: biv_core_pkg.update_description('P_AGENT_ID','col17','col18', 'biv_tmp_rt2');
460: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
461: biv_core_pkg.bind_all_variables(l_cur);
462: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
463: l_dummy := dbms_sql.execute(l_cur);
464: biv_core_pkg.update_description('P_AGENT_ID','col3' ,'col4' , 'biv_tmp_rt2');
465: biv_core_pkg.update_description('P_AGENT_ID','col17','col18', 'biv_tmp_rt2');
466: biv_core_pkg.update_description('P_PRD_ID' ,'col19','col20', 'biv_tmp_rt2');
467: exception
468: when others then
461: biv_core_pkg.bind_all_variables(l_cur);
462: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
463: l_dummy := dbms_sql.execute(l_cur);
464: biv_core_pkg.update_description('P_AGENT_ID','col3' ,'col4' , 'biv_tmp_rt2');
465: biv_core_pkg.update_description('P_AGENT_ID','col17','col18', 'biv_tmp_rt2');
466: biv_core_pkg.update_description('P_PRD_ID' ,'col19','col20', 'biv_tmp_rt2');
467: exception
468: when others then
469: rollback;
462: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
463: l_dummy := dbms_sql.execute(l_cur);
464: biv_core_pkg.update_description('P_AGENT_ID','col3' ,'col4' , 'biv_tmp_rt2');
465: biv_core_pkg.update_description('P_AGENT_ID','col17','col18', 'biv_tmp_rt2');
466: biv_core_pkg.update_description('P_PRD_ID' ,'col19','col20', 'biv_tmp_rt2');
467: exception
468: when others then
469: rollback;
470: if (l_debug = 'Y') then
467: exception
468: when others then
469: rollback;
470: if (l_debug = 'Y') then
471: biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
472: end if;
473: end;
474: ------------------------------------------------------
475: procedure service_requests(p_param_str varchar2) as
484: l_new_param_str varchar2(200);
485: l_pos varchar2(80);
486: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
487: begin
488: biv_core_pkg.g_report_id := 'BIV_SERVICE_REQUEST';
489: l_session_id := biv_core_pkg.get_session_id;
490: biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
491:
492: if (l_debug = 'Y') then
485: l_pos varchar2(80);
486: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
487: begin
488: biv_core_pkg.g_report_id := 'BIV_SERVICE_REQUEST';
489: l_session_id := biv_core_pkg.get_session_id;
490: biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
491:
492: if (l_debug = 'Y') then
493: biv_core_pkg.biv_debug('Parameters:'||p_param_str,
486: l_debug varchar2(30) := fnd_profile.value('BIV:DEBUG');
487: begin
488: biv_core_pkg.g_report_id := 'BIV_SERVICE_REQUEST';
489: l_session_id := biv_core_pkg.get_session_id;
490: biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
491:
492: if (l_debug = 'Y') then
493: biv_core_pkg.biv_debug('Parameters:'||p_param_str,
494: biv_core_pkg.g_report_id);
489: l_session_id := biv_core_pkg.get_session_id;
490: biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
491:
492: if (l_debug = 'Y') then
493: biv_core_pkg.biv_debug('Parameters:'||p_param_str,
494: biv_core_pkg.g_report_id);
495: end if;
496: -- Change for Bug 3386946
497: l_from_list := ' from cs_incidents_b_sec sr';
490: biv_core_pkg.clean_dcf_table('biv_tmp_hs2');
491:
492: if (l_debug = 'Y') then
493: biv_core_pkg.biv_debug('Parameters:'||p_param_str,
494: biv_core_pkg.g_report_id);
495: end if;
496: -- Change for Bug 3386946
497: l_from_list := ' from cs_incidents_b_sec sr';
498: biv_core_pkg.get_report_parameters(p_param_str);
494: biv_core_pkg.g_report_id);
495: end if;
496: -- Change for Bug 3386946
497: l_from_list := ' from cs_incidents_b_sec sr';
498: biv_core_pkg.get_report_parameters(p_param_str);
499: biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
500: if (l_debug = 'Y') then
501: biv_core_pkg.biv_debug(l_from_list,biv_core_pkg.g_report_id);
502: biv_core_pkg.biv_debug(l_where_clause,biv_core_pkg.g_report_id);
495: end if;
496: -- Change for Bug 3386946
497: l_from_list := ' from cs_incidents_b_sec sr';
498: biv_core_pkg.get_report_parameters(p_param_str);
499: biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
500: if (l_debug = 'Y') then
501: biv_core_pkg.biv_debug(l_from_list,biv_core_pkg.g_report_id);
502: biv_core_pkg.biv_debug(l_where_clause,biv_core_pkg.g_report_id);
503: end if;
497: l_from_list := ' from cs_incidents_b_sec sr';
498: biv_core_pkg.get_report_parameters(p_param_str);
499: biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
500: if (l_debug = 'Y') then
501: biv_core_pkg.biv_debug(l_from_list,biv_core_pkg.g_report_id);
502: biv_core_pkg.biv_debug(l_where_clause,biv_core_pkg.g_report_id);
503: end if;
504: l_sql_sttmnt := '
505: select sr.incident_id col1,
498: biv_core_pkg.get_report_parameters(p_param_str);
499: biv_core_pkg.get_where_clause(l_from_list, l_where_clause);
500: if (l_debug = 'Y') then
501: biv_core_pkg.biv_debug(l_from_list,biv_core_pkg.g_report_id);
502: biv_core_pkg.biv_debug(l_where_clause,biv_core_pkg.g_report_id);
503: end if;
504: l_sql_sttmnt := '
505: select sr.incident_id col1,
506: sr.incident_number col2,
537: col19, col20, col22, col24,:session_id,sysdate
538: from ( ' || l_sql_sttmnt || ' )';
539:
540: if (l_debug = 'Y') then
541: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
542: end if;
543: l_cur := dbms_sql.open_cursor;
544: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
545: biv_core_pkg.bind_all_variables(l_cur);
541: biv_core_pkg.biv_debug(l_sql_sttmnt,biv_core_pkg.g_report_id);
542: end if;
543: l_cur := dbms_sql.open_cursor;
544: dbms_sql.parse(l_cur,l_sql_sttmnt,dbms_sql.native);
545: biv_core_pkg.bind_all_variables(l_cur);
546: dbms_sql.bind_variable(l_cur,':session_id',l_session_id);
547: l_dummy := dbms_sql.execute(l_cur);
548: commit;
549:
559: if (l_debug = 'Y') then
560: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
561: l_pos || ':' ||
562: substr(sqlerrm,1,500);
563: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
564: end if;
565: end;
566: l_pos := 'updating incident type';
567: begin
575: if (l_debug = 'Y') then
576: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
577: l_pos || ':' ||
578: substr(sqlerrm,1,500);
579: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
580: end if;
581: end;
582: l_pos := 'updating party name';
583: begin
591: if (l_debug = 'Y') then
592: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
593: l_pos || ':' ||
594: substr(sqlerrm,1,500);
595: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
596: commit;
597: end if;
598: end;
599: /***** channel is being taken from cs_incidents_all_b itself 4/30/02
610: when others then
611: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
612: l_pos || ':' ||
613: substr(sqlerrm,1,500);
614: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
615: commit;
616: end;
617: ******/
618: l_pos := 'updating product';
618: l_pos := 'updating product';
619: begin
620: update biv_tmp_hs2 r
621: set col12= (select substr(description,1,50) from mtl_system_items_vl i
622: where i.organization_id = biv_core_pkg.g_prd_org
623: and i.inventory_item_id = r.col11)
624: where report_code = 'BIV_SERVICE_REQUEST'
625: and session_id = l_session_id;
626: exception
628: if (l_debug = 'Y') then
629: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
630: l_pos || ':' ||
631: substr(sqlerrm,1,500);
632: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
633: commit;
634: end if;
635: end;
636: --
637: l_pos := 'updating componet name';
638: begin
639: update biv_tmp_hs2 r
640: set col9 = (select substr(description,1,50) from mtl_system_items_vl i
641: where i.organization_id = biv_core_pkg.g_prd_org
642: and i.inventory_item_id = r.col9)
643: where report_code = 'BIV_SERVICE_REQUEST'
644: and session_id = l_session_id;
645: exception
647: if (l_debug = 'Y') then
648: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
649: l_pos || ':' ||
650: substr(sqlerrm,1,500);
651: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
652: commit;
653: end if;
654: end;
655: --
656: l_pos := 'updating subcomponent name';
657: begin
658: update biv_tmp_hs2 r
659: set col13= (select substr(description,1,50) from mtl_system_items_vl i
660: where i.organization_id = biv_core_pkg.g_prd_org
661: and i.inventory_item_id = r.col13)
662: where report_code = 'BIV_SERVICE_REQUEST'
663: and session_id = l_session_id;
664: exception
666: if (l_debug = 'Y') then
667: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
668: l_pos || ':' ||
669: substr(sqlerrm,1,500);
670: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
671: commit;
672: end if;
673: end;
674: --
684: if (l_debug = 'Y') then
685: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
686: l_pos || ':' ||
687: substr(sqlerrm,1,500);
688: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
689: commit;
690: end if;
691: end;
692: l_pos := 'updating status';
701: if (l_debug = 'Y') then
702: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
703: l_pos || ':' ||
704: substr(sqlerrm,1,500);
705: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
706: commit;
707: end if;
708: end;
709: l_pos := 'updating escalation level';
724: if (l_debug = 'Y') then
725: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
726: l_pos || ':' ||
727: substr(sqlerrm,1,500);
728: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
729: commit;
730: end if;
731: end;
732: -- here col15 is set to col1 so that in case of service_request_task report
732: -- here col15 is set to col1 so that in case of service_request_task report
733: -- this will be used to update no of tasks for SR
734: l_pos := 'updating col15, col1 for Drill down';
735: begin
736: l_new_param_str := biv_core_pkg.g_param_sep ||
737: biv_core_pkg.reconstruct_param_str ||
738: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST';
739: update biv_tmp_hs2
740: set col15 = col1,
733: -- this will be used to update no of tasks for SR
734: l_pos := 'updating col15, col1 for Drill down';
735: begin
736: l_new_param_str := biv_core_pkg.g_param_sep ||
737: biv_core_pkg.reconstruct_param_str ||
738: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST';
739: update biv_tmp_hs2
740: set col15 = col1,
741: col1 = 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
734: l_pos := 'updating col15, col1 for Drill down';
735: begin
736: l_new_param_str := biv_core_pkg.g_param_sep ||
737: biv_core_pkg.reconstruct_param_str ||
738: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST';
739: update biv_tmp_hs2
740: set col15 = col1,
741: col1 = 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
742: 'SR_ID=' || col1 || l_new_param_str
737: biv_core_pkg.reconstruct_param_str ||
738: 'jtfBinId'||biv_core_pkg.g_value_sep || 'BIV_SERVICE_REQUEST';
739: update biv_tmp_hs2
740: set col15 = col1,
741: col1 = 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
742: 'SR_ID=' || col1 || l_new_param_str
743: --'SrCreate_SrID=' || col1 || l_new_param_str
744: where col1 is not null
745: and report_code = 'BIV_SERVICE_REQUEST'
749: if (l_debug = 'Y') then
750: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
751: l_pos || ':' ||
752: substr(sqlerrm,1,500);
753: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
754: commit;
755: end if;
756: end;
757: if (l_debug = 'Y') then
754: commit;
755: end if;
756: end;
757: if (l_debug = 'Y') then
758: biv_core_pkg.biv_debug('End of report',biv_core_pkg.g_report_id);
759: end if;
760: biv_core_pkg.g_report_id := 'NULL';
761:
762: exception
756: end;
757: if (l_debug = 'Y') then
758: biv_core_pkg.biv_debug('End of report',biv_core_pkg.g_report_id);
759: end if;
760: biv_core_pkg.g_report_id := 'NULL';
761:
762: exception
763: when others then
764: if (l_debug = 'Y') then
764: if (l_debug = 'Y') then
765: l_err := 'Err in biv_rt_task_blog_pkg.service_request at:' ||
766: l_pos || ':' ||
767: substr(sqlerrm,1,500);
768: biv_core_pkg.biv_debug(l_err,'BIV_SERVICE_REQUEST');
769: end if;
770: end;
771: function status_descr(p_sts_id varchar2) return varchar2 is
772: l_name varchar2(50);
771: function status_descr(p_sts_id varchar2) return varchar2 is
772: l_name varchar2(50);
773: l_bklg_meaning fnd_lookups.meaning%type;
774: begin
775: l_bklg_meaning := biv_core_pkg.get_lookup_meaning('BACKLOG');
776: select name into l_name
777: from cs_incident_statuses_vl
778: where incident_status_id = to_number(p_sts_id);
779: return(l_name || ' ' || l_bklg_meaning);
807: and tsk.task_status_id = tstat.task_status_id
808: and nvl(tstat.closed_flag,'N') <> 'Y'
809: )
810: where report_code = 'BIV_SERVICE_REQUEST'
811: and session_id = biv_core_pkg.get_session_id;
812:
813: exception
814: when others then
815: biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
811: and session_id = biv_core_pkg.get_session_id;
812:
813: exception
814: when others then
815: biv_core_pkg.biv_debug(sqlerrm,biv_core_pkg.g_report_id);
816: end;
817: end;