[Home] [Help]
PACKAGE BODY: APPS.IEC_AGT_ASSN_PUB
Source
1 PACKAGE BODY IEC_AGT_ASSN_PUB AS
2 /* $Header: IECASNB.pls 115.6 2004/04/29 22:47:00 anayak noship $ */
3
4 -- Used by AO Plugin to get agent to campaign and schedule assignments
5 -- Returns all agents' assignments (assigned directly or to group)
6 PROCEDURE GET_ALL_AGENT_CPN_SCH_ASSNS
7 ( X_ASSIGNMENTS OUT NOCOPY ASSIGNMENT_CURSOR )
8 AS
9 l_assignment_cursor ASSIGNMENT_CURSOR;
10 l_assignment_stmt VARCHAR2(1000);
11
12 BEGIN
13
14 l_assignment_stmt := ' SELECT ' ||
15 ' astcamp.resource_id RESOURCE_ID, ' ||
16 ' amssched.campaign_id CAMPAIGN_ID, ' ||
17 ' astcamp.campaign_id SCHEDULE_ID ' ||
18 ' FROM ' ||
19 ' AMS_CAMPAIGN_SCHEDULES_B amssched, ' ||
20 ' AST_RS_CAMPAIGNS astcamp ' ||
21 ' WHERE ' ||
22 ' astcamp.campaign_id = amssched.schedule_id ' ||
23 ' UNION ' ||
24 ' SELECT ' ||
25 ' grpmem.resource_id RESOURCE_ID, ' ||
26 ' amssched.campaign_id CAMPAIGN_ID, ' ||
27 ' grpcamp.campaign_id SCHEDULE_ID ' ||
28 ' FROM ' ||
29 ' AMS_CAMPAIGN_SCHEDULES_B amssched, ' ||
30 ' AST_GRP_CAMPAIGNS grpcamp, ' ||
31 ' JTF_RS_GROUP_MEMBERS grpmem, ' ||
32 ' JTF_RS_GROUPS_DENORM grpdenorm ' ||
33 ' WHERE ' ||
34 ' grpmem.group_id = grpdenorm.group_id ' ||
35 ' and grpcamp.group_id = grpdenorm.parent_group_id ' ||
36 ' and grpcamp.campaign_id = amssched.schedule_id ';
37
38 OPEN l_assignment_cursor for l_assignment_stmt;
39
40 X_ASSIGNMENTS := l_assignment_cursor;
41
42 return;
43
44 END GET_ALL_AGENT_CPN_SCH_ASSNS;
45
46
47 -- Used by Blending to get agent to campaign assignments and counts
48 -- Returns all agents' assignments (assigned directly or to group)
49 PROCEDURE GET_WB_ALL_AGENT_CPN_ASSNS
50 ( X_ASSIGNMENTS OUT NOCOPY ASSIGNMENT_CURSOR )
51 AS
52 l_assignment_cursor ASSIGNMENT_CURSOR;
53 l_assignment_stmt VARCHAR2(350);
54
55 BEGIN
56
57 l_assignment_stmt := ' SELECT RESOURCE_ID, IEU_PARAM_PK_VALUE CAMPAIGN_ID, QUEUE_COUNT ' ||
58 ' FROM IEC_ADV_OUTB_WORKNODE_UWQ_V ';
59
60 OPEN l_assignment_cursor for l_assignment_stmt;
61
62 X_ASSIGNMENTS := l_assignment_cursor;
63
64 return;
65
66 END GET_WB_ALL_AGENT_CPN_ASSNS;
67
68
69 -- Used by Blending to get agent to campaign assignments and counts
70 -- Returns a specified agent's assignments (assigned directly or to group)
71 PROCEDURE GET_WB_AGENT_CPN_ASSNS
72 ( P_RESOURCE_ID IN NUMBER
73 , X_ASSIGNMENTS OUT NOCOPY ASSIGNMENT_CURSOR )
74 AS
75 l_assignment_cursor ASSIGNMENT_CURSOR;
76 l_assignment_stmt VARCHAR2(350);
77
78 BEGIN
79
80 l_assignment_stmt := ' SELECT RESOURCE_ID, IEU_PARAM_PK_VALUE CAMPAIGN_ID, QUEUE_COUNT ' ||
81 ' FROM IEC_ADV_OUTB_WORKNODE_UWQ_V ' ||
82 ' WHERE RESOURCE_ID = :1 ';
83
84 OPEN l_assignment_cursor for l_assignment_stmt using P_RESOURCE_ID;
85
86 X_ASSIGNMENTS := l_assignment_cursor;
87
88 return;
89
90 END GET_WB_AGENT_CPN_ASSNS;
91
92
93 -- Used by Blending to get the campaign queue counts
94 -- Campaigns/Schedules not assigned to agents are excluded from the counts
95 PROCEDURE GET_WB_ASSIGNED_CPN_COUNTS
96 ( X_CPN_COUNTS OUT NOCOPY ASSIGNMENT_CURSOR )
97 AS
98 l_cpn_count_cursor ASSIGNMENT_CURSOR;
99 l_cpn_count_stmt VARCHAR2(350);
100
101 BEGIN
102
103 l_cpn_count_stmt := 'SELECT CAMPAIGN_ID, CAMPAIGN_NAME, SUM(QUEUE_COUNT) ' ||
104 ' FROM ( ' ||
105 ' SELECT CAMPAIGN_ID, CAMPAIGN_NAME, SCHEDULE_ID, QUEUE_COUNT ' ||
106 ' FROM IEC_AGENT_WORK_ASSIGNMENTS_V ' ||
107 ' UNION ' ||
108 ' SELECT CAMPAIGN_ID, CAMPAIGN_NAME, SCHEDULE_ID, QUEUE_COUNT ' ||
109 ' FROM IEC_GROUP_WORK_ASSIGNMENTS_V ) ' ||
110 ' GROUP BY CAMPAIGN_ID, CAMPAIGN_NAME';
111
112 OPEN l_cpn_count_cursor for l_cpn_count_stmt;
113
114 X_CPN_COUNTS := l_cpn_count_cursor;
115
116 return;
117
118 END GET_WB_ASSIGNED_CPN_COUNTS;
119
120
121 END IEC_AGT_ASSN_PUB;