[Home] [Help]
167: END IF;
168:
169: IF (p_total_row_ind = 1 /*or p_total_row_ind = 3*/) THEN
170: g_sqlstmt := g_sqlstmt ||
171: ', jtf_rs_group_members mem
172: , jtf_rs_resource_extns rsc ';
173: END IF;
174:
175: g_sqlstmt := g_sqlstmt || 'WHERE period_start_date_time between :start_date and :end_date ';
318:
319:
320: /* Fetch the data from the appropiate MV depending on the time range */
321: IF (g_time_range = 1) THEN
322: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_call_sum a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
323: ELSIF (g_time_range = 2) THEN
324: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
325: ELSIF (g_time_range = 3) THEN
326: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
320: /* Fetch the data from the appropiate MV depending on the time range */
321: IF (g_time_range = 1) THEN
322: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_call_sum a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
323: ELSIF (g_time_range = 2) THEN
324: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
325: ELSIF (g_time_range = 3) THEN
326: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
327: ELSIF (g_time_range = 4) THEN
328: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
322: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_call_sum a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
323: ELSIF (g_time_range = 2) THEN
324: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
325: ELSIF (g_time_range = 3) THEN
326: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
327: ELSIF (g_time_range = 4) THEN
328: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
329: ELSE
330: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum_day_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
324: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
325: ELSIF (g_time_range = 3) THEN
326: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
327: ELSIF (g_time_range = 4) THEN
328: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
329: ELSE
330: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum_day_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
331: END IF;
332:
326: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
327: ELSIF (g_time_range = 4) THEN
328: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
329: ELSE
330: g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum_day_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
331: END IF;
332:
333: IF (p_total_row_ind = FALSE) THEN
334: g_sqlstmt := g_sqlstmt ||
459: /* Form and execute the SQL statement to insert into the temp table */
460: /* the total row corresponding to the agent group user has chosen */
461:
462: select count( distinct(b.resource_id) ) into agentNum
463: from bix_dm_agent_call_sum b, JTF_RS_GROUP_MEMBERS j
464: where group_id = p_group_id
465: and b.resource_id = j.resource_id;
466:
467: if agentNum is null then agentNum :=0;