eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIV_RT_SR_AGE_REPORT_PKG

Source


1 PACKAGE BODY BIV_RT_SR_AGE_REPORT_PKG AS
2 	/* $Header: bivrblab.pls 115.15 2004/01/23 04:55:41 vganeshk ship $ */
3 	-- Service Request Backlog Age Report
4 	-- profile option
5 	g_err VARCHAR2(500);
6 
7 	g_sev_1 NUMBER;
8 	g_sev_2 NUMBER;
9 	g_sev_3 NUMBER;
10 	g_sev_4 NUMBER;
11 	g_sev_5 NUMBER;
12 
13 	-- global params
14     g_agrp VARCHAR2(20);
15     g_ogrp VARCHAR2(20);
16 	g_party_id VARCHAR2(20);
17     g_cntr_id VARCHAR2(20);
18 	g_prod_id VARCHAR2(20);
19     g_manager VARCHAR2(20);
20 	g_esc_level VARCHAR2(20);
21 
22 	g_report_code VARCHAR2(30);
23 	g_extra_param VARCHAR2(1000);
24 
25 	g_query VARCHAR2(5000);
26 	g_select VARCHAR2(3000);
27 	g_table VARCHAR2(1000);
28 	g_where VARCHAR2(1000);
29 	g_v_sp VARCHAR2(5) := biv_core_pkg.g_value_sep;
30 	g_p_sp VARCHAR2(5) := biv_core_pkg.g_param_sep;
31 	g_session_id NUMBER ;
32 	g_debug_flag VARCHAR2(1) := nvl(fnd_profile.value('BIV:DEBUG'),'N');
33 
34 	PROCEDURE load_sr_backlog_age_report(p_param_str IN VARCHAR2 /*DEFAULT NULL*/) IS
35 		l_cur_id  PLS_INTEGER;
36 		l_return_num PLS_INTEGER := 0;
37 		l_total_label VARCHAR2(10);
38       x_where_clause varchar2(2000);
39 	BEGIN
40 		biv_core_pkg.clean_dcf_table('BIV_TMP_HS2');
41 	g_session_id := biv_core_pkg.get_session_id;
42 		-- set profile options
43 		if g_debug_flag = 'Y' then
44 		   biv_core_pkg.biv_debug('Param Str:'||p_param_str,
45                                g_report_code);
46 		end if;
47 		g_sev_1 := fnd_profile.value('BIV:INC_SEVERITY_1');
48 		g_sev_2 := fnd_profile.value('BIV:INC_SEVERITY_2');
49 		g_sev_3 := fnd_profile.value('BIV:INC_SEVERITY_3');
50 		g_sev_4 := fnd_profile.value('BIV:INC_SEVERITY_4');
51 		g_sev_5 := fnd_profile.value('BIV:INC_SEVERITY_5');
52 
53         if g_sev_1 is null then g_sev_1 := 0; end if;
54         if g_sev_2 is null then g_sev_2 := 0; end if;
55         if g_sev_3 is null then g_sev_3 := 0; end if;
56         if g_sev_4 is null then g_sev_4 := 0; end if;
57         if g_sev_5 is null then g_sev_5 := 0; end if;
58 
59 		-- set params
60 		g_agrp := biv_core_pkg.get_parameter_value(p_param_str,'P_AGRP');
61 		g_ogrp := biv_core_pkg.get_parameter_value(p_param_str,'P_OGRP');
62 		g_party_id := biv_core_pkg.get_parameter_value(p_param_str,'P_CUST_ID');
63 		g_cntr_id := biv_core_pkg.get_parameter_value(p_param_str,'P_CNTR_ID');
64 		g_prod_id := biv_core_pkg.get_parameter_value(p_param_str,'P_PRD_ID');
65 		g_manager := biv_core_pkg.get_parameter_value(p_param_str,'P_MGR_ID');
66 		g_esc_level := biv_core_pkg.get_parameter_value(p_param_str,'P_ESC_LVL');
67 
68 		-- set report code
69 		g_report_code            := 'BIV_RT_SR_AGE_REPORT';
70                 biv_core_pkg.g_report_id := 'BIV_RT_SR_AGE_REPORT';
71 
72 		-- set extra URL
73 		g_extra_param := get_extra_param;
74 
75 		-- build query
76       biv_core_pkg.get_report_parameters(p_param_str);
77 		g_select := get_select;
78 		g_table := get_table;
79       biv_core_pkg.get_where_clause(g_table,x_where_clause);
80 		g_where := x_where_clause || get_where;
81 
82 		g_query := g_select || ' '||  g_table || ' '||  g_where;
83 		g_query := g_query || ' group by ins.name, sr.incident_status_id order by ins.name ';
84 
85 		if g_debug_flag = 'Y' then
86 			biv_core_pkg.biv_debug(g_query,g_report_code);
87 			biv_core_pkg.biv_debug('session_id:'||
88                                     to_char(g_session_id),g_report_code);
89 			commit;
90 		end if;
91 
92 		l_cur_id := DBMS_SQL.OPEN_CURSOR;
93 		DBMS_SQL.PARSE(l_cur_id,g_query,DBMS_SQL.NATIVE);
94         biv_core_pkg.bind_all_variables(l_cur_id);
95         DBMS_SQL.BIND_VARIABLE(l_cur_id,':session_id',g_session_id);
96         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_1_a',g_sev_1);
97         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_2_a',g_sev_2);
98         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_3_a',g_sev_3);
99         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_4_a',g_sev_4);
100         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_5_a',g_sev_5);
101         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_1_b',g_sev_1);
102         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_2_b',g_sev_2);
103         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_3_b',g_sev_3);
104         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_4_b',g_sev_4);
105         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_5_b',g_sev_5);
106         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_1_c',g_sev_1);
107         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_2_c',g_sev_2);
108         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_3_c',g_sev_3);
109         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_4_c',g_sev_4);
110         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_sev_5_c',g_sev_5);
111         DBMS_SQL.BIND_VARIABLE(l_cur_id,':g_report_code',g_report_code);
112 		l_return_num := DBMS_SQL.EXECUTE(l_cur_id);
113 		DBMS_SQL.CLOSE_CURSOR(l_cur_id);
114                 commit;
115 
116 		-- update the average, URL
117                 if (g_debug_flag = 'Y') then
118                    biv_core_pkg.biv_debug('Updating Hyperlinks',g_report_code);
119                 end if;
120 		UPDATE biv_tmp_hs2
121 			SET col3 = 'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_STS_ID'||g_v_sp||col3||g_p_sp||'P_SEV'||g_v_sp||g_sev_1||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
122 			col7 = 'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_STS_ID'||g_v_sp||col7||g_p_sp||'P_SEV'||g_v_sp||g_sev_2||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
123 			col11 = 'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_STS_ID'||g_v_sp||col11||g_p_sp||'P_SEV'||g_v_sp||g_sev_3||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
124 			col15 = 'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_STS_ID'||g_v_sp||col15||g_p_sp||'P_SEV'||g_v_sp||g_sev_4||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
125 			col19 = 'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_STS_ID'||g_v_sp||col19||g_p_sp||'P_SEV'||g_v_sp||g_sev_5||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
126 			col6 = decode(col4,0,0,round(col6/col4)),
127 			col10 = decode(col8,0,0,round(col10/col8)),
128 			col14 = decode(col12,0,0,round(col14/col12)),
129 			col18 = decode(col16,0,0,round(col18/col16)),
130 			col22 = decode(col20,0,0,round(col22/col20)),
131                         rowno = 1
132 		where session_id = biv_core_pkg.get_session_id;
133              commit;
134 
135 		select attribute_label_long INTO l_total_label
136         from ak_attributes_vl
137         where attribute_code = 'P_SR_SEV_RPT_6'
138         and attribute_application_id = 862;
139 
140                 if (g_debug_flag = 'Y') then
141                    biv_core_pkg.biv_debug('Adding Total Row',g_report_code);
142                 end if;
143 		-- insert the total row
144 		INSERT INTO biv_tmp_hs2 (report_code,session_id,col2,
145 												col3,col4,col6,
146 												col7,col8,col10,
147 												col11,col12,col14,
148 												col15,col16,col18,
149 												col19,col20,col22,col24,rowno)
150 		select g_report_code,g_session_id,l_total_label,
151 		'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_SEV'||g_v_sp||g_sev_1||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
152 		nvl(sum(col4),0),
153 		'',
154 		'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_SEV'||g_v_sp||g_sev_2||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
155 		nvl(sum(col8),0),
156 		'',
157 		'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_SEV'||g_v_sp||g_sev_3||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
158 		nvl(sum(col12),0),
159 		'',
160 		'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_SEV'||g_v_sp||g_sev_4||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
161 		nvl(sum(col16),0),
162 		'',
163 		'BIV_SERVICE_REQUEST'||g_p_sp||'jtfBinId'||g_v_sp||'BIV_SERVICE_REQUEST'||g_p_sp||'P_SEV'||g_v_sp||g_sev_5||g_p_sp||'P_BLOG'||g_v_sp||'Y'||g_extra_param,
164 		nvl(sum(col20),0),
165 		'',
166 		'',2 FROM biv_tmp_hs2
167 		where session_id = biv_core_pkg.get_session_id
168 		;
169 
170                 commit;
171                 if (g_debug_flag = 'Y') then
172                    biv_core_pkg.biv_debug('End of report',g_report_code);
173                 end if;
174 		EXCEPTION
175 		WHEN OTHERS THEN
176                   rollback;
177                   if (g_debug_flag = 'Y') then
178 	             g_err := 'Err in BIV_RT_SR_AGE_REPORT_PKG.' ||
179                               'load_sr_backlog_age_report:' ||
180                               substr(sqlerrm,1,500);
181 		     biv_core_pkg.biv_debug(g_err,g_report_code);
182                  end if;
183 
184 	END load_sr_backlog_age_report;
185 
186 
187 	-- build the select statement
188 	FUNCTION  get_select RETURN VARCHAR2 IS
189 		l_select_stmt varchar2(3000);
190 	BEGIN
191 		l_select_stmt := '
192 			INSERT INTO biv_tmp_hs2 (report_code,session_id,col2,
193 													col3,col4,col6,
194 													col7,col8,col10,
195 													col11,col12,col14,
196 													col15,col16,col18,
197 													col19,col20,col22,col24)
198 			select :g_report_code ,:session_id,ins.name,
199 			sr.incident_status_id,
200 			sum(decode(sr.incident_severity_id,:g_sev_1_a,1,0)),
201 			sum(decode(sr.incident_severity_id,:g_sev_1_b,sysdate-sr.incident_date,0)),
202 			sr.incident_status_id,
203 		   sum(decode(sr.incident_severity_id,:g_sev_2_a,1,0)),
204 			sum(decode(sr.incident_severity_id,:g_sev_2_b,sysdate-sr.incident_date,0)),
205 			sr.incident_status_id,
206 		   sum(decode(sr.incident_severity_id,:g_sev_3_a,1,0)),
207 			sum(decode(sr.incident_severity_id,:g_sev_3_b,sysdate-sr.incident_date,0)),
208 			sr.incident_status_id,
209 		   sum(decode(sr.incident_severity_id,:g_sev_4_a,1,0)),
210 			sum(decode(sr.incident_severity_id,:g_sev_4_b,sysdate-sr.incident_date,0)),
211 			sr.incident_status_id,
212 		   sum(decode(sr.incident_severity_id,:g_sev_5_a,1,0)),
213 			sum(decode(sr.incident_severity_id,:g_sev_5_b,sysdate-sr.incident_date,0)),
214 		   (sum(decode(sr.incident_severity_id,:g_sev_1_c,1,0))+
215 		    sum(decode(sr.incident_severity_id,:g_sev_2_c,1,0))+
216 		    sum(decode(sr.incident_severity_id,:g_sev_3_c,1,0))+
217 		    sum(decode(sr.incident_severity_id,:g_sev_4_c,1,0))+
218 		    sum(decode(sr.incident_severity_id,:g_sev_5_c,1,0)))
219 	    	';
220 		return l_select_stmt;
221 	END get_select;
222 
223 	-- build the table stmt
224 	FUNCTION get_table RETURN VARCHAR2 IS
225 		l_table_str VARCHAR2(300);
226 	BEGIN
227                 -- Change for Bug 3386946
228 		l_table_str := ' from cs_incidents_b_sec sr, cs_incident_statuses_vl ins ';
229 --		l_table_str := l_table_str || ' cs_lookups clp, hz_parties p, ';
230 --		l_table_str := l_table_str || ' cs_incident_severities_b insv, ';
231 --		l_table_str := l_table_str || ' JTF_RS_EMP_DTLS_VL OWN ';
232 		return l_table_str;
233 	END get_table;
234 
235 	-- build where clause
236 	FUNCTION get_where RETURN VARCHAR2 IS
237 		l_where_str VARCHAR2(1000);
238 	BEGIN
239 		l_where_str := ' ';
240 	--	l_where_str := l_where_str || ' and sr.problem_code = clp.lookup_code (+) ';
241 	--	l_where_str := l_where_str || ' and clp.lookup_type(+) = ''REQUEST_PROBLEM_CODE'' ';
242 		l_where_str := l_where_str || ' and sr.incident_status_id = ins.incident_status_id ';
243 	--	l_where_str := l_where_str || ' and sr.customer_id = p.party_id(+) ';
244 	--	l_where_str := l_where_str || ' and sr.incident_owner_id = OWN.RESOURCE_ID (+) ';
245 	--	l_where_str := l_where_str || ' and sr.incident_severity_id = insv.incident_severity_id ';
246 		l_where_str := l_where_str || ' and sr.incident_status_id = ins.incident_status_id ';
247 		l_where_str := l_where_str || ' and nvl(ins.close_flag,''N'') != ''Y'' ';
248 		return l_where_str;
249 	END get_where;
250 
251 	-- build the extra URL
252 	FUNCTION get_extra_param RETURN VARCHAR2 IS
253 		l_extra_url VARCHAR2(100);
254 	BEGIN
255 		l_extra_url := g_p_sp;
256 		if g_agrp is not null then
257 			l_extra_url := l_extra_url||'P_AGRP'||g_v_sp||g_agrp||g_p_sp;
258 		end if;
259 		if g_ogrp is not null then
260 			l_extra_url := l_extra_url||'P_OGRP'||g_v_sp||g_ogrp||g_p_sp;
261 		end if;
262 		if g_cntr_id is not null then
263 			l_extra_url := l_extra_url||'P_CNTR_ID'||g_v_sp||g_cntr_id||g_p_sp;
264 		end if;
265 		if g_manager is not null then
266 			l_extra_url := l_extra_url||'P_MGR_ID'||g_v_sp||g_manager||g_p_sp;
267 		end if;
268 		if g_party_id is not null then
269                         -- Change for Bug 3044558
270 			l_extra_url := l_extra_url || 'P_CUST_ID'||g_v_sp||g_party_id||g_p_sp;
271 		end if;
272 		if g_prod_id is not null then
273 			l_extra_url := l_extra_url||'P_PRD_ID'||g_v_sp||g_prod_id||g_p_sp;
274 		end if;
275 		if g_esc_level is not null then
276 			l_extra_url := l_extra_url||'P_ESC_LVL'||g_v_sp||g_esc_level||g_p_sp;
277 		end if;
278 		return l_extra_url;
279 	END get_extra_param;
280 
281 
282 	-- Get Column Label
283 	FUNCTION get_sr_blog_col_1_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/)
284 		RETURN VARCHAR2 IS
285     	l_label_1 VARCHAR2(20);
286     	l_label_2 VARCHAR2(20);
287 	BEGIN
288 		-- get severity type
289 		SELECT a.name INTO l_label_1
290 		FROM cs_incident_severities_vl a
291 		WHERE a.incident_severity_id = fnd_profile.value('BIV:INC_SEVERITY_1');
292 
293 		-- get "Severity"
294 		SELECT attribute_label_long INTO l_label_2
295 		FROM ak_attributes_vl
296 		WHERE attribute_application_id = 862
297 		AND attribute_code = 'P_DASH_SR_VIEW_11';
298 
299 		return l_label_2 || ' ' || l_label_1;
300 	END get_sr_blog_col_1_label;
301 
302 	FUNCTION get_sr_blog_col_2_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/)
303 		RETURN VARCHAR2 IS
304     	l_label_1 VARCHAR2(20);
305     	l_label_2 VARCHAR2(20);
306 	BEGIN
307 		-- get severity type
308 		SELECT a.name INTO l_label_1
309 		FROM cs_incident_severities_vl a
310 		WHERE a.incident_severity_id = fnd_profile.value('BIV:INC_SEVERITY_2');
311 
312 		-- get "Severity"
313 		SELECT attribute_label_long INTO l_label_2
314 		FROM ak_attributes_vl
315 		WHERE attribute_application_id = 862
316 		AND attribute_code = 'P_DASH_SR_VIEW_11';
317 
318 		return l_label_2 || ' ' || l_label_1;
319 	END get_sr_blog_col_2_label;
320 
321 	FUNCTION get_sr_blog_col_3_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/)
322 		RETURN VARCHAR2 IS
323     	l_label_1 VARCHAR2(20);
324     	l_label_2 VARCHAR2(20);
325 	BEGIN
326 		-- get severity type
327 		SELECT a.name INTO l_label_1
328 		FROM cs_incident_severities_vl a
329 		WHERE a.incident_severity_id = fnd_profile.value('BIV:INC_SEVERITY_3');
330 
331 		-- get "Severity"
332 		SELECT attribute_label_long INTO l_label_2
333 		FROM ak_attributes_vl
334 		WHERE attribute_application_id = 862
335 		AND attribute_code = 'P_DASH_SR_VIEW_11';
336 
337 		return l_label_2 || ' ' || l_label_1;
338 	END get_sr_blog_col_3_label;
339 
340 	FUNCTION get_sr_blog_col_4_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/)
341 		RETURN VARCHAR2 IS
342     	l_label_1 VARCHAR2(20);
343     	l_label_2 VARCHAR2(20);
344 	BEGIN
345 		-- get severity type
346 		SELECT a.name INTO l_label_1
347 		FROM cs_incident_severities_vl a
348 		WHERE a.incident_severity_id = fnd_profile.value('BIV:INC_SEVERITY_4');
349 
350 		-- get "Severity"
351 		SELECT attribute_label_long INTO l_label_2
352 		FROM ak_attributes_vl
353 		WHERE attribute_application_id = 862
354 		AND attribute_code = 'P_DASH_SR_VIEW_11';
355 
356 		return l_label_2 || ' ' || l_label_1;
357 	END get_sr_blog_col_4_label;
358 
359 	FUNCTION get_sr_blog_col_5_label(p_param_str IN VARCHAR2 /*DEFAULT NULL*/)
360 		RETURN VARCHAR2 IS
361     	l_label_1 VARCHAR2(20);
362     	l_label_2 VARCHAR2(20);
363 	BEGIN
364 		-- get severity type
365 		SELECT a.name INTO l_label_1
366 		FROM cs_incident_severities_vl a
367 		WHERE a.incident_severity_id = fnd_profile.value('BIV:INC_SEVERITY_5');
368 
369 		-- get "Severity"
370 		SELECT attribute_label_long INTO l_label_2
371 		FROM ak_attributes_vl
372 		WHERE attribute_application_id = 862
373 		AND attribute_code = 'P_DASH_SR_VIEW_11';
374 
375 		return l_label_2 || ' ' || l_label_1;
376 	END get_sr_blog_col_5_label;
377 
378 
379 END BIV_RT_SR_AGE_REPORT_PKG;