1 PACKAGE BODY BIX_CALLS_HANDLED_BIN_PKG AS
2 /*$Header: bixxbchb.pls 115.10 2003/01/10 00:14:53 achanda ship $*/
3
4 PROCEDURE populate(p_context IN VARCHAR2 DEFAULT NULL)
5 IS
6
7 l_default_group_id NUMBER;
8 l_reporting_date DATE;
9 l_session_id NUMBER;
10
11 BEGIN
12
13 /* Get the ICX Session Id */
14 SELECT icx_sec.g_session_id
15 INTO l_session_id
16 FROM dual;
17
18 /* Delete the rows from table bix_dm_bin for the current icx session and bin */
19 DELETE bix_dm_bin
20 WHERE bin_code = 'BIX_CALLS_HANDLED_BIN'
21 AND session_id = l_session_id;
22
23 /* Get the default Agent Group of the Agent executing the report */
24 SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
25 INTO l_default_group_id
26 FROM dual;
27
28 /* If the user has not setup his/her default group fetch any one group */
29 /* to which the user belongs and report on that group; if the user does */
30 /* not belong to any group then do not show any data in the bin */
31 IF l_default_group_id IS NULL THEN
32 BEGIN
33 SELECT grp.group_id
34 INTO l_default_group_id
35 FROM jtf_rs_group_members grp
36 , jtf_rs_resource_extns rsc
37 WHERE grp.resource_id = rsc.resource_id
38 AND rsc.user_id = fnd_global.user_id()
39 AND ROWNUM <= 1;
40 EXCEPTION
41 WHEN no_data_found THEN
42 RETURN;
43 END;
44 END IF;
45
46 /* The bin will always display data for maximum date for which data has been collected */
47 SELECT MAX(period_start_date)
48 INTO l_reporting_date
49 FROM bix_dm_agent_call_sum;
50
51 /* Fetch the records of all the agents belonging to the group */
52 /* l_default_group_id and insert them in the table bix_dm_bin */
53 INSERT /*+ PARALLEL(tb,2) */ INTO bix_dm_bin tb (
54 session_id
55 , bin_code
56 , col1
57 , col2
58 , col4
59 , col6
60 , col8 )
61 ( SELECT /*+ PARALLEL(bac,2) */
62 l_session_id
63 , 'BIX_CALLS_HANDLED_BIN'
64 , 'p' || to_char(l_default_group_id)
65 , rsc.source_name
66 , SUM(bac.in_calls_handled)
67 , SUM(bac.out_calls_handled)
68 , bix_util_pkg.get_hrmiss_frmt(trunc(SUM(bac.in_talk_time + bac.out_talk_time + bac.in_wrap_time +
69 bac.out_wrap_time) / DECODE(SUM(bac.in_calls_handled + bac.out_calls_handled),
70 0, 1, SUM(bac.in_calls_handled + bac.out_calls_handled))))
71 FROM bix_dm_agent_call_sum bac
72 , jtf_rs_group_members mem
73 , jtf_rs_resource_extns rsc
74 WHERE mem.group_id = l_default_group_id
75 AND mem.resource_id = rsc.resource_id
76 AND mem.resource_id = bac.resource_id
77 AND bac.period_start_date = l_reporting_date
78 GROUP BY rsc.source_name );
79
80 /* Fetch the records of all the agent groups belonging to the group */
81 /* l_default_group_id and insert them in the table bix_dm_bin */
82 INSERT /*+ PARALLEL(tb,2) */ INTO bix_dm_bin tb (
83 session_id
84 , bin_code
85 , col1
86 , col2
87 , col4
88 , col6
89 , col8 )
90 ( SELECT /*+ PARALLEL(bgc,2) */
91 l_session_id
92 , 'BIX_CALLS_HANDLED_BIN'
93 , 'p' || to_char(grp.group_id)
94 , grp.group_name
95 , SUM(bgc.in_calls_handled)
96 , SUM(bgc.out_calls_handled)
97 , bix_util_pkg.get_hrmiss_frmt(trunc(SUM(bgc.in_talk_time + bgc.out_talk_time + bgc.in_wrap_time +
98 bgc.out_wrap_time) / DECODE(SUM(bgc.in_calls_handled + bgc.out_calls_handled), 0, 1,
99 SUM(bgc.in_calls_handled + bgc.out_calls_handled))))
100 FROM
101 bix_dm_group_call_sum bgc
102 , jtf_rs_groups_denorm dnm
103 , jtf_rs_groups_vl grp
104 WHERE dnm.parent_group_id = l_default_group_id
105 AND dnm.immediate_parent_flag = 'Y'
106 AND dnm.group_id = grp.group_id
107 AND dnm.group_id = bgc.group_id
108 AND bgc.period_start_date = l_reporting_date
109 GROUP BY grp.group_name
110 , grp.group_id );
111
112 commit;
113
114 EXCEPTION
115 WHEN OTHERS
116 THEN RAISE;
117 END populate;
118
119 END BIX_CALLS_HANDLED_BIN_PKG;
[Home] [Help]