1 PACKAGE BODY CCT_MEDIA_QUEUE_PUB AS
2 /* $Header: cctpmqb.pls 115.7 2002/12/06 01:06:44 svinamda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_MEDIA_QUEUE_PUB';
5
6
7
8 PROCEDURE UPDATE_DEQUEUE_COUNT
9 ( p_api_version IN NUMBER ,
10 p_init_msg_list IN VARCHAR2 ,
11 p_commit IN VARCHAR2 ,
12 p_root_svr_group_id IN NUMBER,
13 p_media_type IN NUMBER,
14 p_dequeue_count IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2 ,
16 x_msg_count OUT NOCOPY NUMBER ,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )
19 IS
20 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DEQUEUE_COUNT';
21 l_api_version CONSTANT NUMBER := 1.0;
22
23 BEGIN
24 -- Standard Start of API savepoint
25 SAVEPOINT UPDATE_DEQUEUE_COUNT_PUB;
26 -- Standard call to check for call compatibility.
27 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
28 p_api_version ,
29 l_api_name ,
30 G_PKG_NAME )
31 THEN
32 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33 END IF;
34 -- Initialize message list if p_init_msg_list is set to TRUE.
35 IF FND_API.To_Boolean( p_init_msg_list ) THEN
36 FND_MSG_PUB.initialize;
37 END IF;
38 -- Initialize API return status to success
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40 -- API body
41
42
43 update cct_media_type_dequeue_count
44 set dequeue_count = p_dequeue_count
45 where server_group_id = p_root_svr_group_id and media_type = p_media_type;
46
47 IF sql%notfound THEN raise NO_DATA_FOUND;
48 END IF;
49
50
51 -- End of API body.
52 -- Standard check of p_commit.
53 IF FND_API.To_Boolean( p_commit ) THEN
54 COMMIT WORK;
55 END IF;
56 -- Standard call to get message count and if count is 1, get message info.
57 FND_MSG_PUB.Count_And_Get
58 ( p_count => x_msg_count ,
59 p_data => x_msg_data
60 );
61 EXCEPTION
62
63 WHEN NO_DATA_FOUND THEN
64
65 insert into cct_media_type_dequeue_count
66 ( media_type, dequeue_count, server_group_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login )
67 values (p_media_type, p_dequeue_count, p_root_svr_group_id, 1, sysdate, 1, sysdate, 1);
68
69 IF FND_API.To_Boolean( p_commit ) THEN
70 COMMIT WORK;
71 END IF;
72
73
74 WHEN FND_API.G_EXC_ERROR THEN
75 ROLLBACK TO UPDATE_DEQUEUE_COUNT_PUB;
76 x_return_status := FND_API.G_RET_STS_ERROR ;
77 FND_MSG_PUB.Count_And_Get
78 ( p_count => x_msg_count ,
79 p_data => x_msg_data
80 );
81 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
82 ROLLBACK TO UPDATE_DEQUEUE_COUNT_PUB;
83 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
84 FND_MSG_PUB.Count_And_Get
85 ( p_count => x_msg_count ,
86 p_data => x_msg_data
87 );
88 WHEN OTHERS THEN
89 ROLLBACK TO UPDATE_DEQUEUE_COUNT_PUB;
90 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
91 IF FND_MSG_PUB.Check_Msg_Level
92 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
93 THEN
94 FND_MSG_PUB.Add_Exc_Msg
95 ( G_PKG_NAME ,
96 l_api_name
97 );
98 END IF;
99 FND_MSG_PUB.Count_And_Get
100 ( p_count => x_msg_count ,
101 p_data => x_msg_data
102 );
103 END UPDATE_DEQUEUE_COUNT;
104
105
106 PROCEDURE GET_MEDIA_QUEUE_DETAILS
107 (
108 p_api_version IN NUMBER,
109 p_init_msg_list IN VARCHAR2 ,
110 p_commit IN VARCHAR2 ,
111 p_media_item_id IN NUMBER , -- Required
112 p_server_group_name IN VARCHAR2, -- Required
113 x_abs_pos_media_type OUT NOCOPY NUMBER, -- absolute position of media item in the media type queue
114 x_relative_pos_media_type OUT NOCOPY NUMBER, -- absolute position by media type / no of agents logged in for media type
115 x_abs_pos_all_media_types OUT NOCOPY NUMBER, -- absolute position of media item for all media types
116 x_relative_pos_all_media_types OUT NOCOPY NUMBER, -- absolute position of all media types / total # of agents logged in
117 x_return_status OUT NOCOPY VARCHAR2 ,
118 x_msg_count OUT NOCOPY NUMBER ,
119 x_msg_data OUT NOCOPY VARCHAR2
120 )
121
122 IS
123 l_api_name CONSTANT VARCHAR2(30) := 'GET_MEDIA_QUEUE_DETAILS';
124 l_api_version CONSTANT NUMBER := 1.0;
125 l_media_type_any CONSTANT NUMBER := 4 ;
126 l_root_server_group_id NUMBER;
127 l_root_server_group_name VARCHAR2(256);
128 MEDIA_ITEM_NOT_FOUND EXCEPTION;
129 l_queue_position_undefined EXCEPTION;
130 l_queue_seq_num_undefined EXCEPTION;
131 l_agents_media_type NUMBER := 0;
132 l_agents_any_media_type NUMBER := 0;
133 l_agents_all_media_types NUMBER :=0 ;
134 l_queue_position NUMBER := 0;
135 l_queue_seq_num NUMBER := 0;
136 l_dequeue_count NUMBER := 0;
137 l_dequeue_count_all_types NUMBER := 0;
138 l_media_type NUMBER := -1;
139
140 BEGIN
141 -- Standard Start of API savepoint
142 SAVEPOINT GET_MEDIA_QUEUE_DETAILS_PUB;
143 -- Standard call to check for call compatibility.
144 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
145 p_api_version ,
146 l_api_name ,
147 G_PKG_NAME )
148 THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151 -- Initialize message list if p_init_msg_list is set to TRUE.
152 IF FND_API.To_Boolean( p_init_msg_list ) THEN
153 FND_MSG_PUB.initialize;
154 END IF;
155 -- Initialize API return status to success
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 -- API body
159
160
161 CCT_AQ_PUB.get_root_group_name (
162 p_api_version => 1.0,
163 p_server_group_name => p_server_group_name,
164 x_return_status => x_return_status,
165 x_msg_count => x_msg_count,
166 x_msg_data => x_msg_data,
167 x_root_server_group_name => l_root_server_group_name
168 );
169
170 select server_group_id into l_root_server_group_id
171 from ieo_svr_groups where group_name = l_root_server_group_name;
172
173 -- determine media type for media item.
174
175 select media_type into l_media_type
176 from cct_media_items where media_item_id = p_media_item_id;
177
178 IF sql%notfound THEN raise MEDIA_ITEM_NOT_FOUND;
179 END IF;
180
181
182 x_abs_pos_media_type := 0;
183 x_relative_pos_media_type := 0;
184 x_abs_pos_all_media_types := 0;
185 x_relative_pos_all_media_types := 0;
186
187 -- x_abs_pos_media_type = dequeue_count - queue_position
188
189 select queue_position into l_queue_position from cct_media_items
190 where media_item_id = p_media_item_id;
191
192 IF (l_queue_position = 0) THEN
193 raise l_queue_position_undefined;
194 END IF;
195
196
197 select dequeue_count into l_dequeue_count from cct_media_type_dequeue_count
198 where media_type = l_media_type and server_group_id = l_root_server_group_id;
199
200 IF ((l_queue_position - l_dequeue_count) > 0) THEN
201 x_abs_pos_media_type := l_queue_position - l_dequeue_count;
202 END IF;
203
204
205 -- x_relative_pos_media_type == x_abs_pos_media_type / l_agents_media_type
206
207 -- no of agents logged in for that media type
208
209 select count(*) into l_agents_media_type
210 from cct_agent_rt_stats where attribute11 = l_root_server_group_id
211 and attribute1 = 'T' and media_type = l_media_type ;
212
213 select count(*) into l_agents_any_media_type
214 from cct_agent_rt_stats where attribute11 = l_root_server_group_id
215 and attribute1 = 'T' and media_type = l_media_type_any;
216
217
218 IF (x_abs_pos_media_type > 0) THEN
219 IF (l_agents_media_type > 0) THEN
220 x_relative_pos_media_type := CEIL (x_abs_pos_media_type / ((l_agents_any_media_type * 0.3333333) + l_agents_media_type));
221
222 ELSE
223 x_relative_pos_media_type := x_abs_pos_media_type;
224 END IF;
225 END IF;
226
227 -- x_abs_pos_all_media_types = dequeue_count_all_media_types - queue_seq_num
228
229 select queue_seq_num into l_queue_seq_num from cct_media_items
230 where media_item_id = p_media_item_id;
231
232 IF (l_queue_seq_num = 0) THEN
233 raise l_queue_seq_num_undefined;
234 END IF;
235
236
237 select sum(dequeue_count) into l_dequeue_count_all_types
238 from cct_media_type_dequeue_count where server_group_id = l_root_server_group_id;
239
240 IF ((l_queue_seq_num - l_dequeue_count_all_types) > 0 ) THEN
241 x_abs_pos_all_media_types := l_queue_seq_num - l_dequeue_count_all_types;
242 END IF;
243
244 -- x_relative_pos_all_media_types = x_abs_pos_all_media_types / l_agents_all_media_types
245
246 select count(*) into l_agents_all_media_types from cct_agent_rt_stats
247 where attribute1 = 'T' and attribute11= l_root_server_group_id;
248
249 IF (x_abs_pos_all_media_types > 0) THEN
250 IF (l_agents_all_media_types > 0) THEN
251 x_relative_pos_all_media_types := CEIL (x_abs_pos_all_media_types / l_agents_all_media_types);
252 ELSE
253 x_relative_pos_all_media_types := x_abs_pos_all_media_types;
254 END IF;
255 END IF;
256
257
258 -- End of API body.
259 -- Standard check of p_commit.
260 IF FND_API.To_Boolean( p_commit ) THEN
261 COMMIT WORK;
262 END IF;
263 -- Standard call to get message count and if count is 1, get message info.
264 FND_MSG_PUB.Count_And_Get
265 ( p_count => x_msg_count ,
266 p_data => x_msg_data
267 );
268 EXCEPTION
269
270 WHEN MEDIA_ITEM_NOT_FOUND THEN
271 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
272 FND_MESSAGE.SET_NAME('CCT','CCT_MEDIA_ITEM_NOT_FOUND');
273 FND_MSG_PUB.Add;
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275
276 WHEN l_queue_position_undefined THEN
277 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
278 FND_MESSAGE.SET_NAME('CCT','CCT_QUEUE_POSITION_UNDEFINED');
279 FND_MSG_PUB.Add;
280 x_return_status := FND_API.G_RET_STS_ERROR ;
281
282
283 WHEN l_queue_seq_num_undefined THEN
284 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
285 FND_MESSAGE.SET_NAME('CCT','CCT_QUEUE_SEQ_NUM_UNDEFINED');
286 FND_MSG_PUB.Add;
287 x_return_status := FND_API.G_RET_STS_ERROR ;
288
289
290 WHEN FND_API.G_EXC_ERROR THEN
291 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
292 x_return_status := FND_API.G_RET_STS_ERROR ;
293 FND_MSG_PUB.Count_And_Get
294 ( p_count => x_msg_count ,
295 p_data => x_msg_data
296 );
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
300 FND_MSG_PUB.Count_And_Get
301 ( p_count => x_msg_count ,
302 p_data => x_msg_data
303 );
304 WHEN OTHERS THEN
305 ROLLBACK TO GET_MEDIA_QUEUE_DETAILS_PUB;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
307 IF FND_MSG_PUB.Check_Msg_Level
308 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg
311 ( G_PKG_NAME ,
312 l_api_name
313 );
314 END IF;
315 FND_MSG_PUB.Count_And_Get
316 ( p_count => x_msg_count ,
317 p_data => x_msg_data
318 );
319 END GET_MEDIA_QUEUE_DETAILS ;
320
321 END CCT_MEDIA_QUEUE_PUB;
[Home] [Help]