1 PACKAGE BODY BIX_UWQ_TEMP_PKG AS
2 /*$Header: bixxuwtb.pls 115.15 2003/01/10 00:14:13 achanda ship $ */
3
4 v_date DATE;
5 v_default_group_id INTEGER;
6 v_context VARCHAR2(1000); -- this is used to store the value that is passed from bin or report drill down
7 v_session_id NUMBER ; -- this is used to insert the session id
8
9 PROCEDURE get_param_values(p_context IN VARCHAR2)
10 --
11 --This procedure gets the parameter values.
12 --It also assigns v_context which is the value passed from id column in the drill down.
13 --It also assigns v_date, which will be equal to the parameter value when the parameter
14 --is keyed in, or will be equal to the value stored in the id column when drilling down,
15 --or will be null if neither paramter is keyed in nor is drill down been performed
16 --
17 IS
18
19 BEGIN
20
21 --
22 --Get the value of p_context passed in to the procedure. Store this in v_context.
23 --If v_context is not null, it will have the group/agent id + date in v_context.
24 --v_date stores the parameter that the user types in while running the report.
25 --
26
27 v_context := bix_util_pkg.get_parameter_value(p_context, 'pContext');
28
29 --
30 --Note, when using calendar picker from JTF, parameter is being passed in as
31 --ICX_DATE_FORMAT_MASK. This is being used directly in the to_date function
32 --below to convert the string to a date
33 --
34
35 v_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'STARTDATE'),
36 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
37
38 --
39 --Due to a JTF bug, old pcontext is always being passed in.
40 --This will cause problems when running from listing pages.
41 --To avoid this, if pcontext is equal to the region code
42 --then make this value null.
43 --
44 IF v_context = 'BIX_UWQ_LOGINS_RPT'
45 OR v_context = 'BIX_UWQ_DURATIONS_RPT'
46 THEN
47 v_context := NULL;
48 END IF;
49
50 IF v_date IS NULL AND p_context IS NOT NULL
51 --
52 --This could be because the report is run by clicking on the UWQ bin OR
53 --the user did not key in a date value in the parameter field while running the report.
54 --If the report is run from the bin, we will pick this up from the p_context value as
55 --passed from the bin. If the report was not run from the bin and v_date is still null, then
56 --we will not return any data. The bin passes the date in DD-MON-YYYY format.
57 --
58 THEN
59
60 /* to get rid of gscc error , v_date was assigned null as it is an obsoleted package */
61 v_date := to_date(null); /* (substr(v_context,2,11),'DD-MON-YYYY'); */
62
63 END IF;
64
65 IF v_context IS NULL
66 --
67 --Means it was not run from the bin
68 --
69 THEN
70 BEGIN
71 select fnd_profile.value('BIX_DM_DEFAULT_GROUP')
72 into v_default_group_id
73 from dual;
74
75 EXCEPTION
76 WHEN OTHERS
77 THEN
78 --This means the default group id is null.
79 --We will pick up the group to which the resource was most
80 --recently associated with.
81
82 BEGIN
83
84 select grp.group_id
85 into v_default_group_id
86 from jtf_rs_group_members grp, jtf_rs_resource_extns res
87 where res.resource_id = grp.resource_id
88 and res.user_id = fnd_profile.value('USER_ID')
89 and grp.last_update_date = (select max(grp2.last_update_date)
90 from jtf_rs_group_members grp2, jtf_rs_resource_extns res2
91 where res2.resource_id = grp2.resource_id
92 and res2.user_id = fnd_profile.value('USER_ID')
93 );
94 EXCEPTION
95 WHEN OTHERS
96 THEN
97 v_default_group_id := NULL;
98
99 END;
100
101 END;
102
103 END IF;
104
105 EXCEPTION
106 WHEN NO_DATA_FOUND
107 THEN
108 NULL;
109 WHEN OTHERS THEN
110 RAISE;
111
112 END get_param_values;
113
114 PROCEDURE POPULATE_BIN (p_context IN VARCHAR2 DEFAULT NULL)
115 --
116 --This procedure populates the BIX_DM_BIN table for the UWQ Activity bin.
117 --
118 IS
119
120 v_max_date DATE;
121
122 --
123 --This cursor will fetch the immediate child groups of the agent's default group.
124 --These are the groups which will be displayed in the bin. If the default value
125 --is null, it will pick up the most recent group. If that also returns null, it is
126 --set to null.
127 --
128
129 CURSOR c_child_groups(p_group_id INTEGER)
130 IS
131 select denorm.group_id GID
132 from jtf_rs_groups_denorm denorm
133 where denorm.parent_group_id = p_group_id
134 and denorm.immediate_parent_flag = 'Y';
135
136 --
137 --This cursor will fetch the agents directly associated with the agent's default group.
138 --These agents will be displayed directly in the bin. We dont check for default group
139 --for these agents.
140 --
141
142 CURSOR c_child_agents(p_group_id INTEGER)
143 IS
144 SELECT DISTINCT res.resource_id RID, res.source_name RNAME
145 FROM jtf_rs_group_members grp, jtf_rs_resource_extns res
146 WHERE grp.group_id = p_group_id
147 AND grp.resource_id = res.resource_id;
148
149 BEGIN
150
151 v_session_id := NULL;
152 v_session_id := icx_sec.g_session_id; -- this is used to insert the session id
153
154 delete from bix_dm_bin
155 where bin_code = 'BIX_UWQ_ACTIVITY_BIN'
156 and session_id = v_session_id;
157
158 commit;
159
160 select max(day)-1
161 into v_max_date
162 from bix_dm_uwq_agent_sum;
163
164 --
165 --Select the user who is logged in and the user's default group
166 --
167
168 BEGIN
169
170 select fnd_profile.value('BIX_DM_DEFAULT_GROUP')
171 into v_default_group_id
172 from dual;
173
174 EXCEPTION
175 WHEN OTHERS
176 THEN
177 --This means the default group id is null.
178 --We will pick up the group to which the resource was most
179 --recently associated with.
180
181 BEGIN
182
183 select grp.group_id
184 into v_default_group_id
185 from jtf_rs_group_members grp, jtf_rs_resource_extns res
186 where res.resource_id = grp.resource_id
187 and res.user_id = fnd_profile.value('USER_ID')
188 and grp.last_update_date = (select max(grp2.last_update_date)
189 from jtf_rs_group_members grp2, jtf_rs_resource_extns res2
190 where res.resource_id = grp.resource_id
191 and res.user_id = fnd_profile.value('USER_ID')
192 );
193 EXCEPTION
194 WHEN OTHERS
195 THEN
196 v_default_group_id := NULL;
197
198 END;
199
200 END;
201
202 --
203 --Now we have the agent's default group. Open the child agents cursor
204 --loop through and calculate the measures. Then insert these into the temp
205 --table.
206
207 FOR rec_child_agents IN c_child_agents(v_default_group_id)
208 LOOP
209
210 --
211 --Now we have one resource id. Find the login and duration information for this agent.
212 --
213
214 INSERT INTO bix_dm_bin (bin_code, col2, col3, col4, col5, col6, session_id)
215 SELECT 'BIX_UWQ_ACTIVITY_BIN',
216 rec_child_agents.RNAME,
217 'A'||to_char(v_max_date,'DD-MON-YYYY')||rec_child_agents.RID,
218 DAY_LOGIN,
219 'A'||to_char(v_max_date,'DD-MON-YYYY')||rec_child_agents.RID,
220 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
221 v_session_id
222 FROM bix_dm_uwq_agent_sum summ
223 WHERE summ.resource_id = rec_child_agents.RID
224 AND summ.DAY = trunc(v_max_date);
225
226
227 END LOOP;
228
229 --
230 --Now open the child agents cursor. Loop through every group. Find all the sub groups
231 --for every group. Find the agents at each of these sub-groups. Add up the measures
232 --for all agents. Then insert the measures into the temp table.
233 --
234
235 FOR rec_child_groups IN c_child_groups(v_default_group_id)
236 LOOP
237
238 INSERT INTO bix_dm_bin (bin_code, col2, col3, col4, col5, col6, session_id)
239 SELECT 'BIX_UWQ_ACTIVITY_BIN',
240 vl.group_name,
241 'G'||to_char(v_max_date,'DD-MON-YYYY')||rec_child_groups.GID,
242 DAY_LOGIN,
243 'G'||to_char(v_max_date,'DD-MON-YYYY')||rec_child_groups.GID,
244 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
245 v_session_id
246 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
247 WHERE summ.group_id = rec_child_groups.GID
248 AND summ.group_id = vl.group_id
249 AND summ.day = trunc(v_max_date);
250
251 END LOOP;
252
253 COMMIT;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND
257 THEN
258 NULL;
259 WHEN OTHERS
260 THEN
261 RAISE;
262
263 END POPULATE_BIN;
264
265 PROCEDURE POPULATE_LOGINS_REPORT(p_context IN VARCHAR2 DEFAULT NULL)
266 --
267 --This procedure populates the BIX_DM_REPORT table for the UWQ logins report.
268 --
269 IS
270
271 BEGIN
272
273 v_session_id := NULL;
274 v_session_id := icx_sec.g_session_id; -- this is used to insert the session id
275
276 --
277 --Delete the records from the previous run of the report.
278 --
279 delete from bix_dm_report
280 where report_code = 'BIX_UWQ_LOGINS_RPT'
281 and session_id = v_session_id;
282
283 commit;
284
285 get_param_values(p_context);
286
287 IF UPPER(substr(v_context,1,1)) = 'A'
288 THEN
289
290 --
291 --Display only that agent's information. There will be no further drill downs.
292 --
293
294 INSERT INTO bix_dm_report (report_code, col2, col4,
295 col6, col8, col10, col12, col14, col16,
296 col18, col20, col22, col24, col26, session_id)
297 SELECT 'BIX_UWQ_LOGINS_RPT', res.source_name, '',
298 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
299 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
300 v_session_id
301 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
302 WHERE summ.resource_id = substr(v_context,13)
303 AND summ.resource_id = res.resource_id
304 AND summ.day = v_date;
305
306 ELSIF UPPER(substr(v_context,1,1)) = 'G'
307 THEN
308
309 --
310 --Total row
311 --
312 INSERT INTO bix_dm_report (report_code, col2, col4,
313 col6, col8, col10, col12, col14, col16,
314 col18, col20, col22, col24, col26, session_id)
315 SELECT 'BIX_UWQ_LOGINS_RPT', vl.group_name, '',
316 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
317 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
318 v_session_id
319 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
320 WHERE summ.group_id = substr(v_context,13)
321 AND summ.group_id = vl.group_id
322 AND summ.day = v_date;
323
324 --
325 --Sub group details for immediate child groups of the default group.
326 --
327 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
328 col6, col8, col10, col12, col14, col16,
329 col18, col20, col22, col24, col26, session_id)
330 SELECT 'BIX_UWQ_LOGINS_RPT', '', 'G'||to_char(v_date,'DD-MON-YYYY')||summ.group_id, vl.group_name,
331 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
332 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
333 v_session_id
334 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
335 WHERE summ.group_id IN
336 (select denorm.group_id GID
337 from jtf_rs_groups_denorm denorm
338 where denorm.parent_group_id = substr(v_context,13)
339 and denorm.immediate_parent_flag = 'Y'
340 )
341 AND summ.group_id = vl.group_id
342 AND summ.day = v_date;
343
344 --
345 --Agents directly associated with the group
346 --
347 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
348 col6, col8, col10, col12, col14, col16,
349 col18, col20, col22, col24, col26, session_id)
350 SELECT 'BIX_UWQ_LOGINS_RPT', '', 'A'||to_char(v_date,'DD-MON-YYYY')||summ.resource_id, res.source_name,
351 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
352 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
353 v_session_id
354 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
355 WHERE summ.resource_id IN
356 (
357 SELECT DISTINCT res.resource_id
358 FROM jtf_rs_group_members grp, jtf_rs_resource_extns res
359 WHERE grp.group_id = substr(v_context,13)
360 AND grp.resource_id = res.resource_id
361 )
362 AND summ.resource_id = res.resource_id
363 AND summ.day = v_date;
364
365 ELSIF v_context IS NULL
366 THEN
367
368 --
369 --This means the report is not being run from the bin. It is being run directly
370 --from the reports page. In this case, check for the date parameter for the default group.
371 --
372
373 --
374 --Total row
375 --
376 INSERT INTO bix_dm_report (report_code, col2, col4,
377 col6, col8, col10, col12, col14, col16,
378 col18, col20, col22, col24, col26, session_id)
379 SELECT 'BIX_UWQ_LOGINS_RPT', vl.group_name, '',
380 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
381 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
382 v_session_id
383 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
384 WHERE summ.group_id = v_default_group_id
385 AND summ.group_id = vl.group_id
386 AND summ.day = v_date;
387
388 --
389 --Sub group details for immediate child groups of the default group.
390 --
391 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
392 col6, col8, col10, col12, col14, col16,
393 col18, col20, col22, col24, col26, session_id)
394 SELECT 'BIX_UWQ_LOGINS_RPT', '', 'G'||to_char(v_date,'DD-MON-YYYY')||summ.group_id, vl.group_name,
395 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
396 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
397 v_session_id
398 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
399 WHERE summ.group_id IN
400 (select denorm.group_id GID
401 from jtf_rs_groups_denorm denorm
402 where denorm.parent_group_id = v_default_group_id
403 and denorm.immediate_parent_flag = 'Y'
404 )
405 AND summ.group_id = vl.group_id
406 AND summ.day = v_date;
407
408 --
409 --Agents directly associated with the default group
410 --
411 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
412 col6, col8, col10, col12, col14, col16,
416 DAY_LOGIN, PRIOR_WEEK_LOGIN, CURRENT_WEEK_LOGIN, PRIOR_MONTH_LOGIN, CURRENT_MONTH_LOGIN,
413 col18, col20, col22, col24, col26, session_id)
414 SELECT 'BIX_UWQ_LOGINS_RPT', '', 'A'||to_char(v_date,'DD-MON-YYYY')||summ.resource_id, res.source_name,
415 DAY6_LOGIN, DAY5_LOGIN, DAY4_LOGIN, DAY3_LOGIN, DAY2_LOGIN, DAY1_LOGIN,
417 v_session_id
418 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
419 WHERE summ.resource_id IN
420 (
421 SELECT DISTINCT res.resource_id
422 FROM jtf_rs_group_members grp, jtf_rs_resource_extns res
423 WHERE grp.group_id = v_default_group_id
424 AND grp.resource_id = res.resource_id
425 )
426 AND summ.resource_id = res.resource_id
427 AND summ.day = v_date;
428
429 END IF;
430
431 COMMIT;
432
433 EXCEPTION
434 WHEN NO_DATA_FOUND
435 THEN
436 NULL;
437 WHEN OTHERS
438 THEN
439 RAISE;
440
441 END populate_logins_report;
442
443 PROCEDURE POPULATE_DURATIONS_REPORT(p_context IN VARCHAR2 DEFAULT NULL)
444 --
445 --This procedure populates the BIX_DM_REPORT table for the UWQ durations report.
446 --
447 IS
448
449 BEGIN
450
451 v_session_id := NULL;
452 v_session_id := icx_sec.g_session_id; -- this is used to insert the session id
453
454 --
455 --Delete old rows from the previous run of the report.
456 --
457 delete from bix_dm_report
458 where report_code = 'BIX_UWQ_DURATIONS_RPT'
459 and session_id = v_session_id;
460
461 commit;
462
463 get_param_values(p_context);
464
465 IF UPPER(substr(v_context,1,1)) = 'A'
466 THEN
467
468 --
469 --Display only that agent's information. There will be no further drill downs.
470 --
471 INSERT INTO bix_dm_report (report_code, col2, col4,
472 col6, col8, col10, col12, col14, col16,
473 col18, col20, col22, col24, col26, session_id)
474 SELECT 'BIX_UWQ_DURATIONS_RPT', res.source_name, '',
475 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
476 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
477 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
478 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
479 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
480 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
481 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
482 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
483 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
484 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
485 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
486 v_session_id
487 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
488 WHERE summ.resource_id = substr(v_context,13)
489 AND summ.resource_id = res.resource_id
490 AND summ.day = v_date;
491
492
493 ELSIF UPPER(substr(v_context,1,1)) = 'G'
494 THEN
495
496 --
497 --Total row
498 --
499 INSERT INTO bix_dm_report (report_code, col2, col4,
500 col6, col8, col10, col12, col14, col16,
501 col18, col20, col22, col24, col26, session_id)
502 SELECT 'BIX_UWQ_DURATIONS_RPT', vl.group_name, '',
503 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
504 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
505 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
506 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
507 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
508 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
509 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
510 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
511 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
512 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
513 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
514 v_session_id
515 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
516 WHERE summ.group_id = substr(v_context,13)
517 AND summ.group_id = vl.group_id
518 AND summ.day = v_date;
519
520 --
521 --Sub groups
522 --
523 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
527 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
524 col6, col8, col10, col12, col14, col16,
525 col18, col20, col22, col24, col26, session_id)
526 SELECT 'BIX_UWQ_DURATIONS_RPT', '', 'G'||to_char(v_date,'DD-MON-YYYY')||summ.group_id, vl.group_name,
528 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
529 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
530 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
531 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
532 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
533 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
534 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
535 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
536 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
537 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
538 v_session_id
539 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
540 WHERE summ.group_id IN
541 (select denorm.group_id GID
542 from jtf_rs_groups_denorm denorm
543 where denorm.parent_group_id = substr(v_context,13)
544 and denorm.immediate_parent_flag = 'Y'
545 )
546 AND summ.group_id = vl.group_id
547 AND summ.day = v_date;
548
549 --
550 --Agents directly associated with the group
551 --
552 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
553 col6, col8, col10, col12, col14, col16,
554 col18, col20, col22, col24, col26, session_id)
555 SELECT 'BIX_UWQ_DURATIONS_RPT', '', 'A'||to_char(v_date,'DD-MON-YYYY')||summ.resource_id, res.source_name,
556 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
557 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
558 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
559 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
560 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
561 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
562 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
563 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
564 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
565 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
566 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
567 v_session_id
568 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
569 WHERE summ.resource_id IN
570 (
571 SELECT DISTINCT res.resource_id
572 FROM jtf_rs_group_members grp, jtf_rs_resource_extns res
573 WHERE grp.group_id = substr(v_context,13)
574 AND grp.resource_id = res.resource_id
575 )
576 AND summ.resource_id = res.resource_id
577 AND summ.day = v_date;
578
579
580 ELSIF v_context IS NULL
581 THEN
582 --
583 --This means the report is not being run from the bin. It is being run directly
584 --from the reports page. In this case, check for the date parameter for the default group.
585 --
586
587 --
588 --Total row
589 --
590 INSERT INTO bix_dm_report (report_code, col2, col4,
591 col6, col8, col10, col12, col14, col16,
592 col18, col20, col22, col24, col26, session_id)
593 SELECT 'BIX_UWQ_DURATIONS_RPT', vl.group_name, '',
594 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
595 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
596 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
597 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
598 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
599 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
600 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
601 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
602 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
606 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
603 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
604 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
605 v_session_id
607 WHERE summ.group_id = v_default_group_id
608 AND summ.group_id = vl.group_id
609 AND summ.day = v_date;
610
611 --
612 --Sub groups
613 --
614 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
615 col6, col8, col10, col12, col14, col16,
616 col18, col20, col22, col24, col26, session_id)
617 SELECT 'BIX_UWQ_DURATIONS_RPT', '', 'G'||to_char(v_date,'DD-MON-YYYY')||summ.group_id, vl.group_name,
618 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
619 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
620 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
621 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
622 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
623 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
624 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
625 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
626 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
627 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
628 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
629 v_session_id
630 FROM bix_dm_uwq_group_sum summ, jtf_rs_groups_vl vl
631 WHERE summ.group_id IN
632 (select denorm.group_id GID
633 from jtf_rs_groups_denorm denorm
634 where denorm.parent_group_id = v_default_group_id
635 and denorm.immediate_parent_flag = 'Y'
636 )
637 AND summ.group_id = vl.group_id
638 AND summ.day = v_date;
639
640 --
641 --Agents directly associated with default group.
642 --
643 INSERT INTO bix_dm_report (report_code, col2, col3, col4,
644 col6, col8, col10, col12, col14, col16,
645 col18, col20, col22, col24, col26, session_id)
646 SELECT 'BIX_UWQ_DURATIONS_RPT', '', 'A'||to_char(v_date,'DD-MON-YYYY')||summ.resource_id, res.source_name,
647 bix_util_pkg.get_hrmi_frmt( decode(DAY6_LOGIN,0,0,round(DAY6_DURATION/DAY6_LOGIN)) ),
648 bix_util_pkg.get_hrmi_frmt( decode(DAY5_LOGIN,0,0,round(DAY5_DURATION/DAY5_LOGIN)) ),
649 bix_util_pkg.get_hrmi_frmt( decode(DAY4_LOGIN,0,0,round(DAY4_DURATION/DAY4_LOGIN)) ),
650 bix_util_pkg.get_hrmi_frmt( decode(DAY3_LOGIN,0,0,round(DAY3_DURATION/DAY3_LOGIN)) ),
651 bix_util_pkg.get_hrmi_frmt( decode(DAY2_LOGIN,0,0,round(DAY2_DURATION/DAY2_LOGIN)) ),
652 bix_util_pkg.get_hrmi_frmt( decode(DAY1_LOGIN,0,0,round(DAY1_DURATION/DAY1_LOGIN)) ),
653 bix_util_pkg.get_hrmi_frmt( decode(DAY_LOGIN,0,0,round(DAY_DURATION/DAY_LOGIN)) ),
654 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_WEEK_LOGIN,0,0,round(PRIOR_WEEK_DURATION/PRIOR_WEEK_LOGIN)) ),
655 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_WEEK_LOGIN,0,0,round(CURRENT_WEEK_DURATION/CURRENT_WEEK_LOGIN)) ),
656 bix_util_pkg.get_hrmi_frmt( decode(PRIOR_MONTH_LOGIN,0,0,round(PRIOR_MONTH_DURATION/PRIOR_MONTH_LOGIN)) ),
657 bix_util_pkg.get_hrmi_frmt( decode(CURRENT_MONTH_LOGIN,0,0,round(CURRENT_MONTH_DURATION/CURRENT_MONTH_LOGIN)) ),
658 v_session_id
659 FROM bix_dm_uwq_agent_sum summ, jtf_rs_resource_extns res
660 WHERE summ.resource_id IN
661 (
662 SELECT DISTINCT res.resource_id
663 FROM jtf_rs_group_members grp, jtf_rs_resource_extns res
664 WHERE grp.group_id = v_default_group_id
665 AND grp.resource_id = res.resource_id
666 )
667 AND summ.resource_id = res.resource_id
668 AND summ.day = v_date;
669
670 END IF;
671
672 COMMIT;
673
674 EXCEPTION
675 WHEN NO_DATA_FOUND
679 THEN
676 THEN
677 NULL;
678 WHEN OTHERS
680 RAISE;
681
682 END populate_durations_report;
683
684
685 END BIX_UWQ_TEMP_PKG;
[Home] [Help]