[Home] [Help]
PACKAGE BODY: APPS.CSC_RESPONSE_CENTER_PKG
Source
1 PACKAGE BODY CSC_RESPONSE_CENTER_PKG AS
2 /* $Header: CSCCCRCB.pls 120.8 2010/07/13 09:22:17 spamujul ship $ */
3
4 -- Constants used as tokens for unexpected error messages.
5 G_PKG_NAME CONSTANT VARCHAR2(25):= 'CSC_RESPONSE_CENTER_PKG';
6
7 -- Local Function. Returns the Value of the Constant FND_API.G_MISS_NUM
8 FUNCTION G_MISS_NUM RETURN NUMBER IS
9 BEGIN
10 RETURN FND_API.G_MISS_NUM ;
11 END G_MISS_NUM ;
12
13 -- Local Function. Returns the Value of the Constant FND_API.G_MISS_CHAR to the caller
14 FUNCTION G_MISS_CHAR RETURN VARCHAR2 IS
15 BEGIN
16 RETURN FND_API.G_MISS_CHAR ;
17 END G_MISS_CHAR ;
18
19 -- Local Function. REturns the Value of the Constant FND_API.G_MISS_DATE to the caller
20 FUNCTION G_MISS_DATE RETURN DATE IS
21 BEGIN
22 RETURN FND_API.G_MISS_DATE ;
23 END G_MISS_DATE ;
24
25 -- Local Function. REturns the Value of the Constants
26 -- FND_API.G_VALID_LEVEL_NONE , FND_API.G_VALID_LEVEL_FULL to the caller
27 FUNCTION G_VALID_LEVEL(p_level varchar2) RETURN NUMBER IS
28 BEGIN
29 IF p_level = ('NONE') then
30 RETURN FND_API.G_VALID_LEVEL_NONE ;
31 ELSIF p_level = ('FULL') then
32 RETURN FND_API.G_VALID_LEVEL_FULL ;
33 ELSE
34 -- Unrecognized parameter.
35 FND_MSG_PUB.Add_Exc_Msg
36 ( p_pkg_name => G_PKG_NAME ,
37 p_procedure_name => 'G_VALID_LEVEL' ,
38 p_error_text => 'Unrecognized Value : '||p_level
39 );
40 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
41 END IF;
42 END G_VALID_LEVEL ;
43
44 -- Local Function. REturns the Value of the Constants
45 -- FND_API.G_TRUE , FND_API.G_FALSE To the caller
46 FUNCTION G_BOOLEAN(p_FLAG varchar2) RETURN VARCHAR2 IS
47 BEGIN
48 if p_flag = 'TRUE' then
49 return FND_API.G_TRUE ;
50 elsif p_flag = 'FALSE' then
51 return FND_API.G_FALSE ;
52 ELSE
53 -- Unrecognized parameter.
54 FND_MSG_PUB.Add_Exc_Msg
55 ( p_pkg_name => G_PKG_NAME ,
56 p_procedure_name => 'G_BOOLEAN' ,
57 p_error_text => 'Unrecognized Value : '||p_flag
58 );
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END if;
61 END G_BOOLEAN;
62
63 FUNCTION GET_ERROR_CONSTANT(err_msg VARCHAR2) RETURN VARCHAR2 IS
64
65 BEGIN
66 IF err_msg = 'G_RET_STS_ERROR' THEN
67 RETURN FND_API.G_RET_STS_ERROR;
68 ELSIF err_msg = 'G_RET_STS_UNEXP_ERROR' THEN
69 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
70 ELSIF err_msg = 'G_RET_STS_SUCCESS' THEN
71 RETURN FND_API.G_RET_STS_SUCCESS;
72 END IF;
73
74 END GET_ERROR_CONSTANT;
75
76
77
78 FUNCTION INIT_CC_SR_INT RETURN CC_SR_INT IS
79 TMP_CC_SR_INT CC_SR_INT;
80 BEGIN
81 RETURN TMP_CC_SR_INT;
82 END INIT_CC_SR_INT;
83
84 FUNCTION GET_ADDRESS_REC_TYPE RETURN CSC_RESPONSE_CENTER_PKG.ADDRESS_REC_TYPE IS
85 TMP_ADDRESS_REC_TYPE CSC_RESPONSE_CENTER_PKG.ADDRESS_REC_TYPE;
86 BEGIN
87 RETURN TMP_ADDRESS_REC_TYPE;
88 END GET_ADDRESS_REC_TYPE;
89
90 --
91 -- 28AUG00 Bug 1379490 - create account
92 --
93
94 Procedure Put_in_CC_SR_Buffer (
95 p_cc_sr_int_rec IN CC_SR_INT) Is
96 Begin
97 CC_SR_BUFFER := p_cc_sr_int_rec;
98 End Put_in_CC_SR_Buffer;
99
100 Procedure Get_From_CC_SR_Buffer (
101 x_cc_sr_int_rec OUT NOCOPY CC_SR_INT) Is
102 Begin
103 x_cc_sr_int_rec := CC_SR_BUFFER;
104 End Get_From_CC_SR_Buffer;
105
106 Procedure Init_CC_SR_Buffer
107 Is
108 Begin
109 CC_SR_BUFFER := G_MISS_CC_SR_INT;
110 End Init_CC_SR_Buffer;
111
112 -- Wrapper for HZ procedure : phone_format for phone number globalization
113 FUNCTION phone_format_Wrap( p_phone_country_code in varchar2,
114 p_phone_area_code in varchar2,
115 p_phone_number in varchar2)
116 RETURN varchar2 is
117 l_return_status VARCHAR2(1);
118 l_msg_count NUMBER;
119 l_msg_data VARCHAR2(2000);
120 l_phone_country_code varchar2(30):= NULL;
121 l_phone_area_code varchar2(30);
122 l_phone_number varchar2(30);
123 l_territory_code varchar2(30):= NULL;
124 l_formatted_phone_number varchar2(100);
125 l_raw_phone_number varchar2(10);
126 Begin
127
128 if p_phone_country_code is null then
129 FND_PROFILE.get('CSC_CC_DEFAULT_TERRITORY_CODE',l_territory_code);
130 else
131 l_phone_country_code := p_phone_country_code;
132 end if;
133
134 l_phone_area_code := p_phone_area_code;
135 l_phone_number := p_phone_number;
136
137 /* HZ_CONTACT_POINT_PUB.phone_format (
138 p_api_version => 1.0,
139 p_init_msg_list => FND_API.G_FALSE,
140 p_territory_code => l_territory_code,
141 x_formatted_phone_number => l_formatted_phone_number,
142 x_phone_country_code => l_phone_country_code,
143 x_phone_area_code => l_phone_area_code,
144 x_phone_number => l_phone_number,
145 x_return_status => l_return_status,
146 x_msg_count => l_msg_count,
147 x_msg_data => l_msg_data);
148 */
149 HZ_CONTACT_POINT_V2PUB.phone_format (
150 p_init_msg_list => FND_API.G_FALSE,
151 p_raw_phone_number => l_raw_phone_number,
152 x_formatted_phone_number => l_formatted_phone_number,
153 p_territory_code => l_territory_code,
154 x_phone_country_code => l_phone_country_code,
155 x_phone_area_code => l_phone_area_code,
156 x_phone_number => l_phone_number,
157 x_return_status => l_return_status,
158 x_msg_count => l_msg_count,
159 x_msg_data => l_msg_data);
160
161 -- If procedure does not return success then pass back null (since will be used in view definition)
162 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
163 -- return FND_MSG_PUB.Get(p_msg_index=>1,p_encoded =>'F' );
164 return null;
165 else
166 return l_formatted_phone_number;
167 end if;
168 End phone_format_wrap;
169
170 /* New SEARCH window related objects */
171 PROCEDURE get_account_details(x_account_rec IN OUT NOCOPY AccountRecTabType)
172 IS
173 row_count NUMBER;
174 l_Account_Name VARCHAR2(240);
175 l_Account_Number VARCHAR2(30);
176 l_Cust_Account_Id NUMBER;
177 l_Acct_object_version_number NUMBER;
178 l_account_status VARCHAR2(1);
179 l_multi_accounts VARCHAR2(1);
180
181 --
182 CURSOR Get_Oldest_Acct(p_party_id NUMBER) IS
183 SELECT account_name, account_number,cust_account_id, object_version_number
184 FROM hz_cust_accounts
185 WHERE party_id=p_party_id
186 AND ((status = 'A' AND l_account_status = 'Y') OR (l_account_status = 'N'))
187 AND status not in ('M', 'D')
188 ORDER BY creation_date ASC;
189
190 CURSOR Get_Latest_Acct(p_party_id NUMBER) IS
191 SELECT account_name, account_number,cust_account_id, object_version_number
192 FROM hz_cust_accounts
193 WHERE party_id=p_party_id
194 AND ((status = 'A' AND l_account_status = 'Y') OR (l_account_status = 'N'))
195 AND status not in ('M', 'D')
196 ORDER BY creation_date DESC;
197 BEGIN
198 Fnd_Profile.Get('CSC_CONTACT_CENTER_SHOW_ACTIVE_ACCOUNTS',l_account_status);
199 Fnd_Profile.Get('CSC_CC_DEFAULT_ACCT',l_multi_accounts);
200 l_account_status := nvl(l_account_status, 'N');
201 row_count := x_account_rec.COUNT;
202 FOR i IN 1..row_count LOOP
203 l_Account_name := NULL;
204 l_Account_number := NULL;
205 l_Cust_Account_Id := NULL;
206 l_Acct_object_version_number := NULL;
207 IF NVL(l_multi_accounts, 'N') = 'Y' THEN
208 OPEN Get_Oldest_Acct(x_Account_Rec(i).party_id);
209 FETCH Get_Oldest_Acct INTO l_Account_name, l_Account_number, l_Cust_Account_Id, l_Acct_object_version_number;
210 CLOSE Get_Oldest_Acct;
211
212 x_Account_Rec(i).Account_Name := l_Account_Name;
213 x_Account_Rec(i).Account_Number := l_Account_Number;
214 x_Account_Rec(i).Cust_Account_id := l_Cust_Account_Id;
215 x_Account_Rec(i).object_version_number := l_Acct_object_version_number;
216
217 ELSIF NVL(l_multi_accounts, 'N') = 'L' THEN
218 OPEN Get_Latest_Acct(x_Account_Rec(i).party_id);
219 FETCH Get_Latest_Acct INTO l_Account_name, l_Account_number, l_Cust_Account_Id, l_Acct_object_version_number;
220 CLOSE Get_Latest_Acct;
221
222 x_Account_Rec(i).Account_Name := l_Account_Name;
223 x_Account_Rec(i).Account_Number := l_Account_Number;
224 x_Account_Rec(i).Cust_Account_id := l_Cust_Account_Id;
225 x_Account_Rec(i).object_version_number := l_Acct_object_version_number;
226 ELSE
227 OPEN Get_Oldest_Acct(x_Account_Rec(i).party_id);
228 LOOP
229 FETCH Get_Oldest_Acct INTO l_Account_name, l_Account_number, l_Cust_Account_Id, l_Acct_object_version_number;
230 IF Get_Oldest_Acct%ROWCOUNT = 2 THEN
231 l_Account_name := NULL;
232 l_Account_number := NULL;
233 l_Cust_Account_Id := NULL;
234 l_Acct_object_version_number := NULL;
235 EXIT;
236 END IF;
237 IF Get_Oldest_Acct%NOTFOUND THEN
238 EXIT;
239 END IF;
240 END LOOP;
241 CLOSE Get_Oldest_Acct;
242
243 x_Account_Rec(i).Account_Name := l_Account_Name;
244 x_Account_Rec(i).Account_Number := l_Account_Number;
245 x_Account_Rec(i).Cust_Account_id := l_Cust_Account_Id;
246 x_Account_Rec(i).object_version_number := l_Acct_object_version_number;
247
248 END IF;
249 END LOOP;
250 EXCEPTION
251 WHEN OTHERS THEN
252 NULL;
253 END get_account_details;
254
255 PROCEDURE get_phone_details(x_phone_rec IN OUT NOCOPY PhoneRecTabType)
256 IS
257 row_count NUMBER;
258 l_Phone_Country_Code VARCHAR2(10);
259 l_Phone_Area_Code VARCHAR2(10);
260 l_Phone_Number VARCHAR2(40);
261 l_Phone_Line_Type VARCHAR2(80);
262 l_phone_Line_Code VARCHAR2(30);
263 l_Phone_Id NUMBER;
264 l_Phone_object_version_number NUMBER;
265 l_Phone_extension VARCHAR2(20);
266 l_Full_Phone VARCHAR2(60);
267 BEGIN
268
269 row_count := x_phone_rec.COUNT;
270 FOR i IN 1..row_count LOOP
271 BEGIN
272 SELECT ph.phone_country_code, ph.phone_area_code, ph.phone_number,
273 lkup.meaning, ph.phone_line_type,
274 ph.contact_point_id, ph.object_version_number, phone_extension,
275 ph.phone_country_code||ph.phone_area_code||ph.phone_number
276 INTO l_phone_country_code, l_phone_area_code, l_phone_number,
277 l_phone_line_type, l_phone_line_code,
278 l_phone_id, l_phone_object_version_number, l_phone_extension,
279 l_full_phone
280 FROM hz_contact_points ph, ar_lookups lkup
281 WHERE ph.phone_line_type = lkup.lookup_code
282 AND lkup.lookup_type = 'PHONE_LINE_TYPE'
283 AND lkup.enabled_flag = 'Y'
284 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(lkup.start_date_active, SYSDATE))
285 AND TRUNC(NVL(lkup.end_date_active,SYSDATE))
286 AND ph.owner_table_id = x_Phone_Rec(i).party_id
287 AND ph.owner_table_name = 'HZ_PARTIES'
288 AND ph.contact_point_type = 'PHONE'
289 AND ph.primary_flag = 'Y';
290
291 x_Phone_Rec(i).Phone_Country_Code := l_Phone_Country_Code;
292 x_Phone_Rec(i).Phone_Area_Code := l_Phone_Area_Code;
293 x_Phone_Rec(i).Phone_Number := l_Phone_Number;
294 x_Phone_Rec(i).Phone_Line_Type := l_Phone_Line_Type;
295 x_Phone_Rec(i).Phone_Line_Code := l_Phone_Line_Code;
296 x_Phone_Rec(i).Phone_Id := l_Phone_Id;
297 x_Phone_Rec(i).object_version_number := l_Phone_object_version_number;
298 x_Phone_Rec(i).Phone_Extension := l_phone_extension;
299 x_phone_rec(i).Full_Phone := l_full_phone;
300 EXCEPTION
301 WHEN OTHERS THEN
302 NULL;
303 END;
304
305 END LOOP;
306 EXCEPTION
307 WHEN OTHERS THEN
308 NULL;
309 END get_phone_details;
310
311 --
312 -- Name: Get_Sitephone_Details
313 -- Created for ER# 8606060 by mpathani
314 -- This procedure return the Site_Phone details
315 ---
316 PROCEDURE get_sitephone_details(p_site_id IN SiteIDRecTabType,
317 x_phone_rec IN OUT NOCOPY PhoneRecTabType)
318 IS
319 row_count NUMBER;
320 l_Phone_Country_Code VARCHAR2(10);
321 l_Phone_Area_Code VARCHAR2(10);
322 l_Phone_Number VARCHAR2(40);
323 l_Phone_Line_Type VARCHAR2(80);
324 l_phone_Line_Code VARCHAR2(30);
325 l_Phone_Id NUMBER;
326 l_Phone_object_version_number NUMBER;
327 l_Phone_extension VARCHAR2(20);
328 l_Full_Phone VARCHAR2(60);
329 BEGIN
330 row_count := x_phone_rec.COUNT;
331 FOR i IN 1..row_count
332 LOOP
333 BEGIN
334 SELECT ph.phone_country_code ,
335 ph.phone_area_code ,
336 ph.phone_number ,
337 lkup.meaning ,
338 ph.phone_line_type ,
339 ph.contact_point_id ,
340 ph.object_version_number,
341 phone_extension ,
342 ph.phone_country_code||ph.phone_area_code||ph.phone_number
343 INTO l_phone_country_code ,
344 l_phone_area_code ,
345 l_phone_number ,
346 l_phone_line_type ,
347 l_phone_line_code ,
348 l_phone_id ,
349 l_phone_object_version_number,
350 l_phone_extension ,
351 l_full_phone
352 FROM hz_contact_points ph,
353 ar_lookups lkup ,
354 hz_party_sites ps
355 WHERE ph.phone_line_type = lkup.lookup_code
356 AND lkup.lookup_type = 'PHONE_LINE_TYPE'
357 AND lkup.enabled_flag = 'Y'
358 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(lkup.start_date_active, SYSDATE)) AND TRUNC(NVL(lkup.end_date_active,SYSDATE))
359 AND ph.owner_table_id = p_site_id(i)
360 AND ps.party_site_id = ph.owner_table_id
361 AND ps.party_id = x_Phone_Rec(i).party_id
362 AND ph.owner_table_name = 'HZ_PARTY_SITES'
363 AND ph.contact_point_type = 'PHONE';
364
365 x_Phone_Rec(i).Phone_Country_Code := l_Phone_Country_Code;
366 x_Phone_Rec(i).Phone_Area_Code := l_Phone_Area_Code;
367 x_Phone_Rec(i).Phone_Number := l_Phone_Number;
368 x_Phone_Rec(i).Phone_Line_Type := l_Phone_Line_Type;
369 x_Phone_Rec(i).Phone_Line_Code := l_Phone_Line_Code;
370 x_Phone_Rec(i).Phone_Id := l_Phone_Id;
371 x_Phone_Rec(i).object_version_number := l_Phone_object_version_number;
372 x_Phone_Rec(i).Phone_Extension := l_phone_extension;
373 x_phone_rec(i).Full_Phone := l_full_phone;
374 EXCEPTION
375 WHEN OTHERS THEN
376 NULL;
377 END;
378 END LOOP;
379 EXCEPTION
380 WHEN OTHERS THEN
381 NULL;
382 END get_sitephone_details;
383
384 --This procedure calls the Open_MediaItem API to log a media item.
385 --This procedure has been moved to the server side because JTF_IH_PUB.media_rec_type
386 --references FND_API package (G_MISS_NUM) which cannot be accessed directly from libraries
387 PROCEDURE start_media_item( p_resp_appl_id in number,
388 p_resp_id in number,
389 p_user_id in number,
390 p_login_id in number,
391 x_return_status out nocopy varchar2,
392 x_msg_count out nocopy number,
393 x_msg_data out nocopy varchar2,
394 x_media_id out nocopy number
395 ,x_outbound_dnis in varchar2 DEFAULT NULL -- added by spamujul for 9370084
396 ,x_outbound_ani in varchar2 DEFAULT NULL -- added by spamujul for 9370084
397 ,p_media_direction in varchar2 DEFAULT 'OUTBOUND' -- added by spamujul for 9470243
398 ) IS
399
400
401 v_true VARCHAR2(5) := CSC_CORE_UTILS_PVT.G_TRUE;
402 v_false VARCHAR2(5) := CSC_CORE_UTILS_PVT.G_FALSE;
403 v_ret_sts_failure VARCHAR2(1) := 'E';
404 p_media_rec JTF_IH_PUB.media_rec_type;
405
406 BEGIN
407
408 p_media_rec.media_id := NULL;
409 p_media_rec.media_item_type := 'TELEPHONE';
410 p_media_rec.start_date_time := sysdate;
411 -- Begin fix by spamujul for 9470243
412 -- p_media_rec.direction := 'OUTBOUND';
413 IF NVL(p_media_direction,'OUTBOUND') <> 'OUTBOUND' THEN
414 p_media_rec.direction := p_media_direction;
415 ELSE
416 p_media_rec.direction := 'OUTBOUND';
417 END IF ;
418 -- End fix by spamujul for 9470243
419 -- Begin fix by spamujul for 9370084
420 p_media_rec.ani := x_outbound_ani ;
421 p_media_rec.dnis := x_outbound_dnis;
422 -- End fix by spamujul for 9370084
423
424 jtf_ih_pub.open_mediaitem( p_api_version => 1.0,
425 p_init_msg_list => v_true,
426 p_commit => v_true,
427 p_resp_appl_id => p_resp_appl_id,
428 p_resp_id => p_resp_id,
429 p_user_id => p_user_id,
430 p_login_id => p_login_id,
431 x_return_status => x_return_status,
432 x_msg_count => x_msg_count,
433 x_msg_data => x_msg_data,
434 p_media_rec => p_media_rec,
435 x_media_id => x_media_id);
436
437 if x_media_id is null then
438 x_return_status := v_ret_sts_failure;
439 end if;
440
441 END start_media_item;
442
443
444 /* End of SEARCH window related objects */
445
446 END CSC_RESPONSE_CENTER_PKG;