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.BIX_CAMP_PERF_REPORT

Source


1 PACKAGE BODY BIX_CAMP_PERF_REPORT AS
2 /*$Header: bixxrcpb.pls 115.16 2003/01/10 00:14:34 achanda ship $*/
3 
4 g_time_range NUMBER;
5 
6 PROCEDURE insert_temp_table(p_campaign_id IN NUMBER,
7                             p_site_id     IN NUMBER,
8                             p_group_id    IN NUMBER,
9                             p_time        IN NUMBER,
10                             p_start_period IN DATE,
11                             p_end_period   IN DATE)
12 IS
13   v_resource_id NUMBER;
14   l_index NUMBER;
15 --  p_group_id NUMBER;
16 --  p_site_id NUMBER;
17   v_group_name VARCHAR2(80);
18   v_group_id NUMBER;
19   v_campaign_name VARCHAR2(80);
20   v_campaign_id NUMBER;
21 --  p_campaign_id NUMBER;
22   v_resource_name VARCHAR2(80);
23   v_source_code VARCHAR2(30);
24   v_user_currency  VARCHAR2(80);
25   v_global_currency  VARCHAR2(80);
26   v_conversion_type  VARCHAR2(80);
27   v_user_def_curr  VARCHAR2(80);
28   v_format_mask VARCHAR2(80);
29 
30   l_denom_rate NUMBER;
31   l_num_rate NUMBER;
32   l_status   NUMBER;
33   l_factor NUMBER;
34 
35   cursor get_campaigns is
36   select distinct a.campaign_name, b.campaign_id
37   from   bix_dm_group_call_sum b, ams_campaigns_vl a
38   where    ((b.campaign_id = p_campaign_id) or (p_campaign_id is null or p_campaign_id = -999))
39   and ((b.server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
40   and b.campaign_id is not null
41   and a.campaign_id = b.campaign_id;
42 
43   cursor get_groups is
44   select distinct j.group_id, j.group_name
45   from  jtf_rs_groups_vl j, bix_dm_group_call_sum b
46   where b.group_id = j.group_id
47   and   b.campaign_id = v_campaign_id
48   and  ((b.group_id = p_group_id) or (p_group_id is null or p_group_id = -999))
49   and ((b.server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
50   and b.campaign_id  is not null;
51 
52   cursor group_agents is
53   select j1.resource_name, j1.resource_id
54   from   jtf_rs_resource_extns_vl j1,  jtf_rs_group_members j2
55   where  j1.resource_id = j2.resource_id
56   and  j2.group_id = v_group_id
57   and ((j1.server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999));
58 
59   cursor all_agents is
60   select j1.resource_name, j1.resource_id
61   from   jtf_rs_resource_extns_vl j1;
62 
63 
64 
65 BEGIN
66 /* Dummy row inserted for testing, plz remove */
67 /*
68   INSERT INTO jtfb_temp_report(
69 			 report_code
70 			 , col1
71 			 ,  col2
72 			 ,  col4
73 			 ,  col6
74 			 ,  col8
75 			 ,  col10
76 			 ,  col12
77 			 ,  col14
78 			 ,  col16
79 			 ,  col18
80 			 ,  col20
81 			 ,  col22
82 			 ,  col24
83 			 ,  col26)
84    VALUES('BIX_CMPGN_PERF_REPORT'
85 		 , '1'
86 		 , 'Summer Special'
87 		 , ''
88 		 , NULL
89 		 , '45:22:25'
90 		 , '2000'
91 		 , '00:20:00'
92 		 , '00:20:00'
93 		 , '00:20:00'
94 		 , '90.0%'
95 		 , '1800'
96 		 , '900,000'
97 		 , '1500'
98 		 , '500,000'
99 		 );
100 */
101   v_user_currency :=  fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY');
102   v_global_currency :=  fnd_profile.value('BIX_DM_PREFERRED_CURRENCY');
103   v_conversion_type := fnd_profile.value( 'BIX_DM_CONVERSION_TYPE' );
104   v_user_def_curr  :=  fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY');
105   v_format_mask  :=  fnd_currency.get_format_mask(v_user_currency,  30 );
106   --to_char( amt, l_format_mask)
107   bix_util_pkg.get_conversion_rate(v_global_currency, v_user_currency, sysdate, v_conversion_type, l_denom_rate,  l_num_rate, l_status );
108   if (l_denom_rate = 0 or l_denom_rate is null) then
109 	 l_factor := 1;
110   else
111 	 l_factor := l_num_rate/l_denom_rate;
112   end if;
113 
114   if v_format_mask is null then
115 	v_format_mask := '999990';
116 	end if;
117 
118   l_index := 1;
119   for campaigns in get_campaigns LOOP
120 	v_campaign_id := campaigns.campaign_id;
121      v_campaign_name := campaigns.campaign_name;
122       INSERT INTO jtfb_temp_report(
123 			 report_code
124 			 , col1
125 			 ,  col2
126 			 ,  col4
127 			 ,  col6
128 			 ,  col8
129 			 ,  col10
130 			 ,  col12
131 			 ,  col14
132 			 ,  col16
133 			 ,  col18
134 			 ,  col20
135 			 ,  col22
136 			 ,  col24
137 			 ,  col26)
138       (SELECT
139 			 'BIX_CMPGN_PERF_REPORT'
140 			 , l_index
141 			 , v_campaign_name
142 			 , null
143 			 , null
144 			 , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
145 			 , SUM(IN_CALLS_HANDLED + out_calls_handled)
146 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + out_talk_time)
147 			 / DECODE(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
148 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time + out_wrap_time)
149 			 /decode(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
150                 , bix_util_pkg.get_hrmiss_frmt(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))
151 			 , trunc(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)/sum(login_time) * 100, 2)
152 			 ,sum(leads_created)
153 			 ,to_char(sum(leads_amount) * l_factor, v_format_mask)
154 			 ,sum(opportunities_won)
155 			 ,to_char(sum(opportunities_won_amount) * l_factor,  v_format_mask)
156         from bix_dm_group_call_sum
157 	   where campaign_id = v_campaign_id
158 	   and period_start_date_time between p_start_period and p_end_period);
159    for groups in get_groups LOOP
160      l_index := l_index + 1;
161 	 v_group_id := groups.group_id;
162 	 v_group_name := groups.group_name;
163       INSERT INTO jtfb_temp_report(
164 			 report_code
165 			 , col1
166 			 ,  col2
167 			 ,  col4
168 			 ,  col6
169 			 ,  col8
170 			 ,  col10
171 			 ,  col12
172 			 ,  col14
173 			 ,  col16
174 			 ,  col18
175 			 ,  col20
176 			 ,  col22
177 			 ,  col24
178 			 ,  col26)
179       (SELECT
180 			 'BIX_CMPGN_PERF_REPORT'
181 			 , l_index
182 			 , NULL
183 			 , v_group_name
184 			 , null
185 			 , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
186 			 , SUM(IN_CALLS_HANDLED + out_calls_handled)
187 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + out_talk_time)
188 			 / DECODE(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
189 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time + out_wrap_time)
190 			 /decode(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
191                 , bix_util_pkg.get_hrmiss_frmt(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))
192 			 , trunc(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)/sum(login_time) * 100, 2)
193 			 ,sum(leads_created)
194 			 ,to_char(sum(leads_amount)* l_factor, v_format_mask)
195 			 ,sum(opportunities_won)
196 			 ,to_char(sum(opportunities_won_amount) * l_factor, v_format_mask)
197         from bix_dm_group_call_sum
198 	   where group_id = v_group_id
199 	   and   campaign_id = v_campaign_id
200 	   and period_start_date_time between p_start_period and p_end_period);
201 	for data in group_agents loop
202          l_index := l_index + 1;
203 	    v_resource_id := data.resource_id;
204 	    v_resource_name := data.resource_name;
205       INSERT INTO jtfb_temp_report(
206 			 report_code
207 			 , col1
208 			 ,  col2
209 			 ,  col4
210 			 ,  col6
211 			 ,  col8
212 			 ,  col10
213 			 ,  col12
214 			 ,  col14
215 			 ,  col16
216 			 ,  col18
217 			 ,  col20
218 			 ,  col22
219 			 ,  col24
220 			 ,  col26)
221       (SELECT
222 			 'BIX_CMPGN_PERF_REPORT'
223 			 ,l_index
224 			 , null
225 			 , v_resource_name
226 			 , null
227 			 , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
228 			 , (SUM(IN_CALLS_HANDLED + out_calls_handled))
229 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + out_talk_time)
230 			 / DECODE(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
231 			 , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time + out_wrap_time)/decode(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
232                 , bix_util_pkg.get_hrmiss_frmt(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))
233 			 , trunc(sum((available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))/sum(login_time) * 100, 2),
234 			 sum(leads_created),
235 			 to_char(sum(leads_amount) * l_factor, v_format_mask),
236 			 sum(opportunities_won),
237 			to_char(sum(opportunities_won_amount) * l_factor, v_format_mask)
238       from    bix_dm_agent_call_sum
239 	 where   resource_id = v_resource_id
240 	   and   campaign_id = v_campaign_id
241 	   and  period_start_date_time between p_start_period and p_end_period
242 	   and   ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999)));
243 	 l_index := l_index + 1;
244 	 INSERT INTO jtfb_temp_report(
245 					report_code
246 					, col1
247 					,  col2
248 					,  col4
249 					,  col6
250 	     			,  col8
251 	     			,  col10
252 					,  col12
253 					,  col14
254 					 ,  col16
255 					 ,  col18
256 					 ,  col20
257 					 ,  col22
258 					  ,  col24
259 					  ,  col26)
260       (SELECT
261 			 'BIX_CMPGN_PERF_REPORT'
262 			 , l_index
263 			  , null
264 			  , NULL
265 			  , decode(g_time_range,1,period_start_time,
266 			  2,substr(period_start_time,1,2),
267 			  3,to_char(floor(substr(period_start_time,1,2) / 2) * 2),
268 			  4,to_char(floor(substr(period_start_time,1,2) / 4) * 4),period_start_date)
269 			  , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
270 			   , (SUM(in_calls_handled + out_calls_handled))
271 			   , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + out_talk_time)/ DECODE(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
272 			   , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time + out_wrap_time)/decode(SUM(in_calls_handled + out_calls_handled),0,1,SUM(in_calls_handled + out_calls_handled)))
273 			 , bix_util_pkg.get_hrmiss_frmt(sum(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))
274 			 , trunc(sum((available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time))/sum(login_time) * 100, 2),
275 			 sum(leads_created),
276 			 to_char(sum(leads_amount) * l_factor, v_format_mask),
277 			 sum(opportunities_won),
278 			 to_char(sum(opportunities_won_amount) * l_factor, v_format_mask)
279 			 FROM        bix_dm_agent_call_sum
280      		 WHERE       resource_id = v_resource_id
281 			 and   campaign_id = v_campaign_id
282 			 and   period_start_date_time between p_start_period and p_end_period
283 			 and   ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
284 			 GROUP BY    decode(g_time_range,1,period_start_time,
285 			    2,substr(period_start_time,1,2),
286 			    3,to_char(floor(substr(period_start_time,1,2) / 2) * 2),
287 			    4,to_char(floor(substr(period_start_time,1,2) / 4) * 4),period_start_date));
288 
289    END LOOP;
290    END LOOP;
291    END LOOP;
292 
293 END insert_temp_table;
294 
295 
296 PROCEDURE populate(p_context IN VARCHAR2)
297 IS
298 v_campaign_id NUMBER;
299 p_campaign_id NUMBER;
300 p_site_id NUMBER;
301 p_group_id NUMBER;
302 p_time NUMBER;
303 p_start_period DATE;
304 p_end_period DATE;
305 v_site_id NUMBER;
306 v_group_id NUMBER;
307 v_time  NUMBER;
308 v_start_period DATE;
309 v_end_period DATE;
310 
311 BEGIN
312   v_time := 8;
313   SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
314   INTO   g_time_range
315   FROM   dual;
316 
317   IF g_time_range IS NULL THEN
318     g_time_range := 1;
319   END IF;
320   v_campaign_id :=  to_number(jtfb_dcf.get_parameter_value(p_context,'P_CAMPAIGN_ID'));
321   v_site_id :=  to_number(jtfb_dcf.get_parameter_value(p_context,'P_SITE_ID'));
322   v_group_id :=  to_number(jtfb_dcf.get_parameter_value(p_context,'P_GROUP_ID'));
323   v_time :=  to_number(jtfb_dcf.get_parameter_value(p_context,'P_TIME'));
324   -- get the start period and end period
325   if (v_time <> 9) then
326    if (v_time = 7) then
327 	v_start_period := sysdate - 2;
328 	v_end_period := sysdate - 1;
329    elsif (v_time = 8) then
330      v_start_period := sysdate - 1;
331      v_end_period := sysdate;
332    else
333       bix_util_pkg.get_time_range(v_time , v_start_period, v_end_period);
334    end if;
335   else
336   v_start_period :=  to_date(jtfb_dcf.get_parameter_value(p_context,'P_START_PERIOD'));
337   v_end_period :=  to_date(jtfb_dcf.get_parameter_value(p_context,'P_END_PERIOD'));
338   end if;
339   insert_temp_table(v_campaign_id, v_site_id, v_group_id, v_time, v_start_period, v_end_period);
340 EXCEPTION
341     WHEN OTHERS
342     THEN RETURN;
343 END populate;
344 
345 FUNCTION get_heading RETURN varchar2
346 IS
347  l_label VARCHAR2(1000);
348  l_message VARCHAR2(1000);
349  l_date DATE;
350 BEGIN
351   select max(period_start_date_time)
352   into l_date
353   from bix_dm_agent_call_sum;
354   l_message := fnd_message.get_string('BIX', 'BIX_DM_REFRESH_MSG');
355   l_label :=  l_message  || ' ' ||to_char(l_date, 'DD-MON-YYYY HH12:MI:SS AM');
356   /*
357   l_label := 'Campaign Performance - Agent Report (' || l_message  || ' ' ||to_char(l_date, 'DD-MON-YYYY HH12:MI:SS AM') || ')';
358   */
359    return l_label;
360 END;
361 
362 END BIX_CAMP_PERF_REPORT;