[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_IEC_PVT
Source
1 PACKAGE BODY JTF_IH_IEC_PVT AS
2 /* $Header: JTFIHPAB.pls 115.14 2004/07/28 12:32:02 vekrishn ship $ */
3
4 -- Sub-Program Unit Declarations
5
6 -----------------------------++++++-------------------------------
7 --
8 -- API name : GET_MEDIA_IDS
9 -- Type : Private
10 --
11 -- Version : Initial version 1.0
12 --
13 -----------------------------++++++-------------------------------
14 -- Create media items
15 -- Modified by IAleshin 08-Mar-2004 Enh# 3491849 JTH.R: IH CHANGES TO
16 -- SUPPORT FTC ABANDONMENT REGULATIONS
17 -- 05/24/1971 IAleshin - Fixed Bug#3646665 - DBIB9TST AO PRIVATE CLOSE API
18 -- NEEDS TO BE ABLE TO SET THE SERVER GROUP ID.
19 --
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_IEC_PVT';
21
22 PROCEDURE GET_MEDIA_IDS
23 ( P_COUNT IN NUMBER
24 , X_MEDIA_IDS OUT NOCOPY MEDIA_ID_CURSOR
25 )
26 IS
27 PRAGMA AUTONOMOUS_TRANSACTION;
28
29 l_media_id NUMBER;
30 l_null CHAR(1);
31
32 l_media_id_tab MEDIA_ID_TAB;
33 l_media_id_cursor MEDIA_ID_CURSOR;
34 l_media_id_stmt VARCHAR2(4000);
35
36 l_user_id NUMBER;
37 l_login_id NUMBER;
38
39 -- Perf variables
40 l_duration_perf NUMBER;
41 l_direction_perf VARCHAR2(240);
42 l_media_item_type_perf VARCHAR2(80);
43 l_active_perf VARCHAR2(1);
44 l_ao_update_pending_perf VARCHAR2(1);
45 l_soft_closed_perf VARCHAR2(1);
46
47 BEGIN
48
49 -- Perf variables
50 l_duration_perf := 0;
51 l_direction_perf := 'OUTBOUND';
52 l_media_item_type_perf := 'TELEPHONE';
53 l_active_perf := 'Y';
54 l_ao_update_pending_perf := 'Y';
55 l_soft_closed_perf := 'N';
56
57 l_user_id := NVL(FND_GLOBAL.user_id,-1);
58 l_login_id := NVL(FND_GLOBAL.conc_login_id,-1);
59
60 -- dbms_output.put_line('About to Enter Loop');
61
62 for L in 1..P_COUNT
63 Loop
64
65 SELECT JTF_IH_MEDIA_ITEMS_S1.NextVal into l_media_id FROM dual;
66 -- dbms_output.put_line('Media ID Returned: ' || to_char(l_media_id));
67
68 INSERT INTO jtf_ih_media_items
69 (
70 CREATED_BY,
71 CREATION_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_DATE,
74 LAST_UPDATE_LOGIN,
75 MEDIA_ID,
76 DURATION,
77 DIRECTION,
78 --END_DATE_TIME,
79 SOURCE_ITEM_CREATE_DATE_TIME,
80 SOURCE_ITEM_ID,
81 START_DATE_TIME,
82 SOURCE_ID,
83 MEDIA_ITEM_TYPE,
84 MEDIA_ITEM_REF,
85 MEDIA_DATA,
86 MEDIA_ABANDON_FLAG,
87 MEDIA_TRANSFERRED_FLAG,
88 ACTIVE,
89 SERVER_GROUP_ID,
90 DNIS,
91 ANI,
92 CLASSIFICATION,
93 -- Enh# 3491849
94 AO_UPDATE_PENDING,
95 SOFT_CLOSED
96 )
97 VALUES
98 (
99 l_user_id,
100 Sysdate,
101 l_user_id,
102 Sysdate,
103 l_login_id,
104 l_media_id,
105 l_duration_perf,
106 l_direction_perf,
107 --Sysdate,
108 NULL,
109 NULL,
110 Sysdate,
111 NULL,
112 l_media_item_type_perf,
113 NULL,
114 NULL,
115 NULL,
116 NULL,
117 l_active_perf,
118 NULL,
119 NULL,
120 NULL,
121 NULL,
122 l_ao_update_pending_perf,
123 l_soft_closed_perf
124 );
125
126 l_media_id_tab(L) := l_media_id;
127 -- dbms_output.put_line('Media Item Inserted');
128
129 End Loop;
130
131 l_media_id_stmt := 'Select ';
132
133 -- dbms_output.put_line('Starting Cursor statement construction loop');
134 for L in 1..l_media_id_tab.count
135 loop
136 l_media_id_stmt := l_media_id_stmt || l_media_id_tab(L);
137 if( L <> l_media_id_tab.count )then
138 l_media_id_stmt := l_media_id_stmt ||',';
139 end if;
140 end loop;
141 l_media_id_stmt := l_media_id_stmt || ' from dual order by 1';
142
143 -- dbms_output.put_line('Cursor statement construction complete');
144 -- dbms_output.put_line('statement length = ' || to_char(length(l_media_id_stmt)));
145
146 Open l_media_id_cursor for l_media_id_stmt;
147 -- dbms_output.put_line('Cursor opened');
148 X_MEDIA_IDS := l_media_id_cursor;
149 commit;
150 return;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 ROLLBACK;
155 -- Get messages and log these.
156 raise_application_error( -20000, 'Error Inserting Media Item. - Get IDS Failed',TRUE);
157
158 END GET_MEDIA_IDS;
159
160
161 -- Created by IAleshin 08-Mar-2004 Enh# 3491849 JTH.R: IH CHANGES TO
162 -- SUPPORT FTC ABANDONMENT REGULATIONS
163 --
164 PROCEDURE CLOSE_AO_CALL
165 (
166 p_Media_id IN NUMBER,
167 p_Hard_Close IN VARCHAR2,
168 p_source_item_id IN NUMBER ,
169 p_address IN VARCHAR2,
170 p_start_date_time IN DATE ,
171 p_end_date_time IN DATE,
172 p_duration IN NUMBER,
173 p_media_abandon_flag IN VARCHAR2,
174 x_Commit IN VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2,
178 -- Enh# 3646665
179 p_Server_Group_ID IN NUMBER DEFAULT NULL
180 )
181 AS
182 l_ao_update_pending VARCHAR2(1);
183 l_soft_closed VARCHAR2(1);
184 l_start_date_time DATE;
185 l_end_date_time DATE;
186 p_Media JTF_IH_PUB.media_rec_type;
187 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_AO_CALL';
188 l_Hard_Close VARCHAR2(2);
189 l_Commit VARCHAR2(10);
190
191 -- l_ao_update_pending_perf
192 l_ao_update_pending_perf VARCHAR2(1);
193
194 BEGIN
195
196 -- Perf fix for literal Usage
197 l_ao_update_pending_perf := 'N';
198
199 l_Hard_Close := NVL(p_Hard_Close,'N');
200 l_Commit := NVL(x_Commit,FND_API.G_FALSE);
201
202 SAVEPOINT close_ao_call;
203 -- Get current ao_update_pending and soft_closed values for current Media_Id
204 --
205 SELECT ao_update_pending, soft_closed, start_date_time, end_date_time
206 INTO l_ao_update_pending, l_soft_closed, l_start_date_time, l_end_date_time
207 FROM JTF_IH_MEDIA_ITEMS WHERE Media_Id = p_Media_id;
208
209 -- Set up parameters for Media Item.
210 --
211 p_Media.media_id := p_Media_id;
212 p_Media.direction := 'OUTBOUND';
213 p_Media.media_item_type := 'TELEPHONE';
214 p_Media.source_item_id := p_source_item_id;
215 p_Media.address := p_address;
216 p_Media.media_abandon_flag := p_media_abandon_flag;
217 p_Media.server_group_id := p_Server_Group_ID;
218
219 IF p_start_date_time IS NOT NULL THEN
220 p_Media.start_date_time := p_start_date_time;
221 END IF;
222
223 IF p_end_date_time IS NOT NULL THEN
224 p_Media.end_date_time := p_end_date_time;
225 END IF;
226
227 IF p_duration IS NOT NULL THEN
228 p_Media.duration := p_duration;
229 END IF;
230
231 -- Update AO_UPDATE_PENDING flag to 'N' for Media Item ID passed
232 --
233 -- l_ao_update_pending_perf
234 UPDATE jtf_ih_media_items SET ao_update_pending = l_ao_update_pending_perf
235 WHERE Media_ID = p_Media_id;
236
237 -- If MI should be hard closed then call JTF_IH_PUB.Close_MediaItem
238 -- else JTF_IH_PUB.Update_MediaItem
239 --
240 IF l_Hard_Close = 'Y' OR l_soft_closed = 'Y' THEN
241 JTF_IH_PUB.close_mediaitem(
242 p_api_version => 1.0,
243 p_init_msg_list => FND_API.G_TRUE,
244 p_commit => l_Commit,
245 p_resp_appl_id => fnd_global.resp_appl_id,
246 p_resp_id => fnd_global.resp_id,
247 p_user_id => fnd_global.user_id,
248 p_login_id => fnd_global.login_id,
249 x_return_status => x_return_status,
250 x_msg_count => x_msg_count,
251 x_msg_data => x_msg_data,
252 p_media_rec => p_Media
253 );
254 ELSE
255 JTF_IH_PUB.update_mediaitem(
256 p_api_version => 1.0,
257 p_init_msg_list => FND_API.G_TRUE,
258 p_commit => l_Commit,
259 p_resp_appl_id => fnd_global.resp_appl_id,
260 p_resp_id => fnd_global.resp_id,
261 p_user_id => fnd_global.user_id,
262 p_login_id => fnd_global.login_id,
263 x_return_status => x_return_status,
264 x_msg_count => x_msg_count,
265 x_msg_data => x_msg_data,
266 p_media_rec => p_Media
267 );
268 END IF;
269 IF x_return_status <> 'S' THEN
270 RAISE fnd_api.g_exc_error;
271 END IF;
272
273 IF l_Commit = FND_API.G_TRUE THEN
274 COMMIT WORK;
275 END IF;
276
277 EXCEPTION
278 WHEN fnd_api.g_exc_error THEN
279 ROLLBACK TO close_ao_call;
280 x_return_status := fnd_api.g_ret_sts_error;
281 fnd_msg_pub.count_and_get
282 ( p_count => x_msg_count,
283 p_data => x_msg_data );
284 x_msg_data := FND_MSG_PUB.Get(p_msg_index => x_msg_count, p_encoded=>'F');
285
286 WHEN OTHERS THEN
287 ROLLBACK TO close_ao_call;
288 x_return_status := fnd_api.g_ret_sts_unexp_error;
289 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
290 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
291 END IF;
292 fnd_msg_pub.count_and_get(p_count => x_msg_count,
293 p_data => x_msg_data );
294 x_msg_data := FND_MSG_PUB.Get(p_msg_index => x_msg_count, p_encoded=>'F');
295 END;
296
297 END JTF_IH_IEC_PVT;