1 PACKAGE BODY CCT_UTIL_PUB AS
2 /* $Header: cctpub.pls 120.0 2005/06/02 09:20:42 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_UTIL_PUB';
5
6
7
8 PROCEDURE GET_MIDDLEWARE_ID
9 ( p_api_version IN NUMBER ,
10 p_init_msg_list IN VARCHAR2 ,
11 p_commit IN VARCHAR2 ,
12 p_agent_id IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2 ,
14 x_msg_count OUT NOCOPY NUMBER ,
15 x_msg_data OUT NOCOPY VARCHAR2 ,
16 x_middleware_id OUT NOCOPY NUMBER
17 )
18 IS
19 l_api_name CONSTANT VARCHAR2(30) := 'GET_MIDDLEWARE_ID';
20 l_api_version CONSTANT NUMBER := 1.0;
21
22 BEGIN
23 -- Standard Start of API savepoint
24 SAVEPOINT GET_MIDDLEWARE_ID_PUB;
25 -- Standard call to check for call compatibility.
26 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
27 p_api_version ,
28 l_api_name ,
29 G_PKG_NAME )
30 THEN
31 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
32 END IF;
33 -- Initialize message list if p_init_msg_list is set to TRUE.
34 IF FND_API.To_Boolean( p_init_msg_list ) THEN
35 FND_MSG_PUB.initialize;
36 END IF;
37 -- Initialize API return status to success
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39 -- API body
40
41 x_middleware_id := -1;
42
43 select middleware_id into x_middleware_id from cct_telesets where teleset_id =
44 (select client_id from cct_agent_rt_stats where attribute1='T' and agent_id = p_agent_id);
45
46 -- End of API body.
47 -- Standard check of p_commit.
48 IF FND_API.To_Boolean( p_commit ) THEN
49 COMMIT WORK;
50 END IF;
51 -- Standard call to get message count and if count is 1, get message info.
52 FND_MSG_PUB.Count_And_Get
53 ( p_count => x_msg_count ,
54 p_data => x_msg_data
55 );
56 EXCEPTION
57
58 WHEN FND_API.G_EXC_ERROR THEN
59 ROLLBACK TO GET_MIDDLEWARE_ID_PUB;
60 x_return_status := FND_API.G_RET_STS_ERROR ;
61 FND_MSG_PUB.Count_And_Get
62 ( p_count => x_msg_count ,
63 p_data => x_msg_data
64 );
65 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
66 ROLLBACK TO GET_MIDDLEWARE_ID_PUB;
67 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
68 FND_MSG_PUB.Count_And_Get
69 ( p_count => x_msg_count ,
70 p_data => x_msg_data
71 );
72 WHEN OTHERS THEN
73 ROLLBACK TO GET_MIDDLEWARE_ID_PUB;
74 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
75 IF FND_MSG_PUB.Check_Msg_Level
76 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
77 THEN
78 FND_MSG_PUB.Add_Exc_Msg
79 ( G_PKG_NAME ,
80 l_api_name
81 );
82 END IF;
83 FND_MSG_PUB.Count_And_Get
84 ( p_count => x_msg_count ,
85 p_data => x_msg_data
86 );
87 END GET_MIDDLEWARE_ID;
88
89
90
91 PROCEDURE CLOSE_MILCS
92 ( p_api_version IN NUMBER ,
93 p_init_msg_list IN VARCHAR2 ,
94 p_commit IN VARCHAR2,
95 p_milcs_type_id IN NUMBER,
96 p_media_item_id IN NUMBER,
97 p_end_date_time IN DATE,
98 x_return_status OUT NOCOPY VARCHAR2 ,
99 x_msg_count OUT NOCOPY NUMBER ,
100 x_msg_data OUT NOCOPY VARCHAR2
101 )
102 IS
103 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_MILCS';
104 l_api_version CONSTANT NUMBER := 1.0;
105 l_milcs_id NUMBER := -1;
106 l_start_date_time DATE ;
107 l_milcs_duration NUMBER;
108
109 BEGIN
110 -- Standard Start of API savepoint
111 SAVEPOINT CLOSE_MILCS_PUB;
112 -- Standard call to check for call compatibility.
113 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
114 p_api_version ,
115 l_api_name ,
116 G_PKG_NAME )
117 THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122 -- API body
123
124 select milcs_id, start_date_time into l_milcs_id, l_start_date_time
125 from jtf_ih_media_item_lc_segs
126 where media_id = p_media_item_id and milcs_type_id = p_milcs_type_id
127 and end_date_time is null;
128
129 if sql%notfound then raise NO_DATA_FOUND;
130 end if;
131
132 l_milcs_duration := p_end_date_time - l_start_date_time;
133 l_milcs_duration := round(24*60*60*l_milcs_duration);
134
135 JTF_IH_PUB_W.Update_MediaLifecycle
136 (p_api_version=>1.0
137 ,p_init_msg_list=>FND_API.G_FALSE
138 ,p_commit=>FND_API.G_TRUE
139 ,p_resp_appl_id=>1 -- IN RESP APPL ID
140 ,p_resp_id=>1 -- IN RESP ID
141 ,p_user_id=>FND_GLOBAL.USER_ID -- IN USER ID
142 ,p_login_id=>NULL -- IN LOGIN ID
143 ,p10_a3=>l_milcs_duration -- IN duration
144 ,p10_a4=>p_end_date_time -- IN end date time
145 ,p10_a5=>l_milcs_id -- IN milcs id
146 ,p10_a7=>p_media_item_id -- IN media id
147 ,p10_a8=>CCT_IH_PUB.G_IH_CCT_HANDLER_ID -- IN handler id
148 ,x_return_status=>x_return_status
149 ,x_msg_count=>x_msg_count
150 ,x_msg_data=>x_msg_data );
151
152
153 -- End of API body.
154 -- Standard check of p_commit.
155 IF FND_API.To_Boolean( p_commit ) THEN
156 COMMIT WORK;
157 END IF;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 x_msg_data := 'CCT_NO_DATA_FOUND';
162 x_msg_count := 1;
163 WHEN FND_API.G_EXC_ERROR THEN
164 ROLLBACK TO CLOSE_MILCS_PUB;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
166 x_msg_data := 'CCT_UNEXPECTED_ERROR';
167 x_msg_count := 1;
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 ROLLBACK TO CLOSE_MILCS_PUB;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
171 x_msg_data := 'CCT_UNEXPECTED_ERROR';
172 x_msg_count := 1;
173 WHEN OTHERS THEN
174 ROLLBACK TO CLOSE_MILCS_PUB;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
176 x_msg_data := 'CCT_UNEXPECTED_ERROR';
177 x_msg_count := 1;
178 END CLOSE_MILCS;
179
180
181 -- return value = 0 => media item can be closed.
182 -- return value = 1 => media item cannot be closed.
183
184 PROCEDURE CAN_CLOSE_MEDIA_ITEM
185 ( p_api_version IN NUMBER ,
186 p_init_msg_list IN VARCHAR2 ,
187 p_commit IN VARCHAR2,
188 p_media_item_id IN NUMBER,
189 x_can_close_media_item OUT NOCOPY NUMBER,
190 x_return_status OUT NOCOPY VARCHAR2 ,
191 x_msg_count OUT NOCOPY NUMBER ,
192 x_msg_data OUT NOCOPY VARCHAR2
193 )
194 IS
195 l_api_name CONSTANT VARCHAR2(30) := 'CAN_CLOSE_MEDIA_ITEM';
196 l_api_version CONSTANT NUMBER := 1.0;
197 IH_MILCS_TYPE_WITH_AGENT CONSTANT NUMBER := 5;
198 IH_MILCS_TYPE_IN_QUEUE CONSTANT NUMBER := 3;
199 l_end_date_time DATE;
200 l_start_date_time DATE Default null;
201 l_in_queue_start_date_time DATE Default null;
202 l_nwa_count NUMBER ;
203 media_item_at_route_point_ex EXCEPTION;
204 no_data_found_ex EXCEPTION;
205
206 BEGIN
207 -- Standard Start of API savepoint
208 SAVEPOINT CAN_CLOSE_MEDIA_ITEM_PUB;
209 -- Standard call to check for call compatibility.
210 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
211 p_api_version ,
212 l_api_name ,
213 G_PKG_NAME )
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217
218 -- API body
219
220 -- dbms_output.put_line('Processing media item id ' || p_media_item_id);
221
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223 x_can_close_media_item := 1; -- initialize to cannot close.
224
225 select max(c.end_date_time) into l_end_date_time from jtf_ih_media_item_lc_segs c
226 where c.media_id = p_media_item_id and c.milcs_type_id = IH_MILCS_TYPE_WITH_AGENT;
227
228 if l_end_date_time is null then raise NO_DATA_FOUND;
229 end if;
230
231 select count(*) into l_nwa_count from jtf_ih_media_item_lc_segs c
232 where c.media_id = p_media_item_id and c.milcs_type_id = IH_MILCS_TYPE_WITH_AGENT
233 and c.end_date_time IS NULL ;
234
235 if ( l_nwa_count > 0 ) then raise NO_DATA_FOUND ;
236 end if;
237
238 select max(c.start_date_time) into l_start_date_time from jtf_ih_media_item_lc_segs c
239 where c.media_id = p_media_item_id and c.milcs_type_id = IH_MILCS_TYPE_WITH_AGENT;
240 l_in_queue_start_date_time := null;
241 select max(c.start_date_time) into l_in_queue_start_date_time from jtf_ih_media_item_lc_segs c
242 where c.media_id = p_media_item_id and c.milcs_type_id = IH_MILCS_TYPE_IN_QUEUE;
243
244 if (l_in_queue_start_date_time is not null) and
245 (l_start_date_time is not null) and
246 (l_start_date_time < l_in_queue_start_date_time)
247 then raise media_item_at_route_point_ex;
248 -- dbms_output.put_line('media item id transferred');
249 end if;
250
251 -- dbms_output.put_line('media item is a candidate for closure');
252 x_can_close_media_item := 0;
253
254 -- End of API body.
255 -- Standard check of p_commit.
256 IF FND_API.To_Boolean( p_commit ) THEN
257 COMMIT WORK;
258 END IF;
259
260 EXCEPTION
261 WHEN media_item_at_route_point_ex then
262 begin
263 fnd_file.put_line(fnd_file.log,'media item is at route point, cannot close.');
264 -- dbms_output.put_line('media item is at route point, cannot close.');
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266 x_msg_data := 'CCT_MEDIA_ITEM_AT_ROUTE_POINT';
267 x_msg_count := 1;
268 end;
269 WHEN NO_DATA_FOUND THEN
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 x_msg_data := 'CCT_NO_DATA_FOUND';
272 x_msg_count := 1;
273 WHEN OTHERS THEN
274 ROLLBACK TO CAN_CLOSE_MEDIA_ITEM_PUB;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276 x_msg_data := 'CCT_UNEXPECTED_ERROR';
277 x_msg_count := 1;
278 END CAN_CLOSE_MEDIA_ITEM;
279
280
281
282 END CCT_UTIL_PUB;
[Home] [Help]