[Home] [Help]
PACKAGE BODY: APPS.HZ_TIMEZONE_UTILS_PVT
Source
1 PACKAGE BODY HZ_TIMEZONE_UTILS_PVT AS
2 /*$Header: ARHTZUTB.pls 120.5 2005/10/30 03:55:25 appldev noship $ */
3
4 procedure duplicate_country_code(p_territory_code in varchar2, x_return_status out nocopy varchar2) is
5
6 cursor territory_code_exist_csr is
7 select 'Y'
8 from hz_phone_country_codes
9 where territory_code = p_territory_code;
10
11 l_exist varchar2(1);
12
13 begin
14 x_return_status := FND_API.G_RET_STS_SUCCESS;
15 open territory_code_exist_csr;
16 fetch territory_code_exist_csr into l_exist;
17 close territory_code_exist_csr;
18 if l_exist = 'Y'
19 then
20 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
21 FND_MESSAGE.SET_TOKEN('COLUMN', 'territory_code');
22 FND_MSG_PUB.ADD;
23 x_return_status := FND_API.G_RET_STS_ERROR;
24 end if;
25 end;
26
27 procedure duplicate_area_code(p_territory_code in varchar2, p_area_code in varchar2,
28 x_return_status out nocopy varchar2) is
29
30 cursor area_code_exist_csr is
31 select 'Y'
32 from hz_phone_area_codes
33 where territory_code = p_territory_code
34 and area_code = p_area_code;
35
36 l_exist varchar2(1);
37
38 begin
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40 open area_code_exist_csr;
41 fetch area_code_exist_csr into l_exist;
42 close area_code_exist_csr;
43 if l_exist = 'Y'
44 then
45 FND_MESSAGE.SET_NAME('AR', 'HZ_TZ_AREA_DUP_ERROR');
46 FND_MSG_PUB.ADD;
47 x_return_status := FND_API.G_RET_STS_ERROR;
48 end if;
49 end;
50
51 PROCEDURE create_area_code(
52 p_territory_code IN VARCHAR2,
53 p_phone_country_code IN VARCHAR2,
54 p_area_code IN VARCHAR2,
55 p_description IN VARCHAR2,
56 p_timezone_id IN NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2) is
60
61 l_rowid ROWID;
62 begin
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 fnd_msg_pub.initialize;
65 SAVEPOINT create_area_code;
66
67 if p_area_code <> TRANSLATE (
68 p_area_code,
69 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$%^*_,|}{[]?<>=";:',
70 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')
71 then
72 fnd_message.set_name('AR','HZ_INVALID_AREA_CODE');
73 FND_MSG_PUB.ADD;
74 x_return_status := FND_API.G_RET_STS_ERROR;
75 RAISE FND_API.G_EXC_ERROR;
76 end if;
77
78 duplicate_area_code(p_territory_code,p_area_code, x_return_status);
79 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
80 RAISE FND_API.G_EXC_ERROR;
81 END IF;
82
83 HZ_PHONE_AREA_CODES_PKG.insert_row(
84 p_rowid => l_rowid,
85 p_TERRITORY_CODE =>p_TERRITORY_CODE,
86 p_AREA_CODE =>p_AREA_CODE,
87 p_PHONE_COUNTRY_CODE =>p_PHONE_COUNTRY_CODE,
88 p_DESCRIPTION =>p_DESCRIPTION,
89 p_CREATED_BY =>hz_utility_v2pub.created_by,
90 p_CREATION_DATE =>hz_utility_v2pub.creation_date,
91 p_LAST_UPDATE_LOGIN => hz_utility_v2pub.last_update_login,
92 p_LAST_UPDATE_DATE => hz_utility_v2pub.last_update_date,
93 p_LAST_UPDATED_BY =>hz_utility_v2pub.last_updated_by,
94 p_OBJECT_VERSION_NUMBER =>1,
95 p_TIMEZONE_ID =>p_TIMEZONE_ID);
96
97 EXCEPTION
98 WHEN FND_API.G_EXC_ERROR THEN
99 ROLLBACK to create_area_code;
100 x_return_status := FND_API.G_RET_STS_ERROR;
101 FND_MSG_PUB.Count_And_Get(
102 p_encoded => FND_API.G_FALSE,
103 p_count => x_msg_count,
104 p_data => x_msg_data);
105
106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
107 ROLLBACK to create_area_code;
108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
109 FND_MSG_PUB.Count_And_Get(
110 p_encoded => FND_API.G_FALSE,
111 p_count => x_msg_count,
112 p_data => x_msg_data);
113
114 WHEN OTHERS THEN
115 ROLLBACK to create_area_code;
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117
118 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
119 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
120 FND_MSG_PUB.ADD;
121
122 FND_MSG_PUB.Count_And_Get(
123 p_encoded => FND_API.G_FALSE,
124 p_count => x_msg_count,
125 p_data => x_msg_data);
126 END;
127
128 PROCEDURE update_area_code(
129 p_territory_code IN VARCHAR2,
130 p_area_code IN VARCHAR2,
131 p_old_area_code IN VARCHAR2,
132 p_description IN VARCHAR2,
133 p_timezone_id IN NUMBER,
134 p_object_version_number IN OUT NOCOPY NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2) is
138
139 l_object_version_number NUMBER;
140 l_rowid ROWID;
141 l_area_code varchar2(30);
142
143 begin
144 x_return_status := FND_API.G_RET_STS_SUCCESS;
145 fnd_msg_pub.initialize;
146 SAVEPOINT update_area_code;
147
148
149 if p_area_code <> TRANSLATE (
150 p_area_code,
151 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$%^*_,|}{[]?<>=";:',
152 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')
153 then
154 fnd_message.set_name('AR','HZ_INVALID_AREA_CODE');
155 FND_MSG_PUB.ADD;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 RAISE FND_API.G_EXC_ERROR;
158 end if;
159
160 -- check whether record has been updated by another user. If not, lock it.
161 BEGIN
162 SELECT OBJECT_VERSION_NUMBER,rowid,area_code
163 INTO l_object_version_number, l_rowid, l_area_code
164 FROM HZ_PHONE_AREA_CODES
165 WHERE TERRITORY_CODE = p_territory_code
166 and area_code = p_old_area_code
167 FOR UPDATE OF TERRITORY_CODE NOWAIT;
168
169 IF NOT ((p_object_version_number is null and l_object_version_number is
170 null)
171 OR (p_object_version_number = l_object_version_number))
172 THEN
173 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
174 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PHONE_AREA_CODES');
175 FND_MSG_PUB.ADD;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179
180 p_object_version_number := nvl(l_object_version_number, 1) + 1;
181
182 EXCEPTION WHEN NO_DATA_FOUND THEN
183 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
184 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_PHONE_AREA_CODES');
185 FND_MESSAGE.SET_TOKEN('VALUE', 'territory_code+area_code');
186 FND_MSG_PUB.ADD;
187 RAISE FND_API.G_EXC_ERROR;
188 END;
189
190 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194 if l_area_code <> p_area_code
195 then
196 duplicate_area_code(p_territory_code,p_area_code, x_return_status);
197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200 end if;
201
202 HZ_PHONE_AREA_CODES_PKG.update_row(
203 p_rowid => l_rowid,
204 p_TERRITORY_CODE =>p_TERRITORY_CODE,
205 p_AREA_CODE =>p_AREA_CODE,
206 p_PHONE_COUNTRY_CODE =>fnd_api.g_miss_char,
207 p_DESCRIPTION =>p_DESCRIPTION,
208 p_CREATED_BY =>hz_utility_v2pub.created_by,
209 p_CREATION_DATE =>hz_utility_v2pub.creation_date,
210 p_LAST_UPDATE_LOGIN => hz_utility_v2pub.last_update_login,
211 p_LAST_UPDATE_DATE => hz_utility_v2pub.last_update_date,
212 p_LAST_UPDATED_BY =>hz_utility_v2pub.last_updated_by,
213 p_OBJECT_VERSION_NUMBER =>l_object_version_number,
214 p_TIMEZONE_ID =>p_TIMEZONE_ID );
215
216 EXCEPTION
217 WHEN FND_API.G_EXC_ERROR THEN
218 ROLLBACK to update_area_code;
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 FND_MSG_PUB.Count_And_Get(
221 p_encoded => FND_API.G_FALSE,
222 p_count => x_msg_count,
223 p_data => x_msg_data);
224
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 ROLLBACK to update_area_code;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 FND_MSG_PUB.Count_And_Get(
229 p_encoded => FND_API.G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data);
232
233 WHEN OTHERS THEN
234 ROLLBACK to update_area_code;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236
237 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
238 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
239 FND_MSG_PUB.ADD;
240
241 FND_MSG_PUB.Count_And_Get(
242 p_encoded => FND_API.G_FALSE,
243 p_count => x_msg_count,
244 p_data => x_msg_data);
245 end;
246 function areacode_timezone_exist(p_territory_code in varchar2) return varchar2 is
247
248 cursor areacode_timezone_exist_csr is
249 select 'Y'
250 from hz_phone_area_codes
251 where territory_code = p_territory_code
252 and timezone_id is not null;
253
254 l_exist varchar2(1);
255 begin
256 open areacode_timezone_exist_csr;
257 fetch areacode_timezone_exist_csr into l_exist;
258 close areacode_timezone_exist_csr;
259 if l_exist = 'Y'
260 then return 'Y';
261 else return 'N';
262 end if;
263 end;
264
265 PROCEDURE update_country_timezone(
266 p_territory_code IN VARCHAR2,
267 p_timezone_id IN NUMBER,
268 p_object_version_number IN OUT NOCOPY NUMBER,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2) is
272
273 l_exist_flag varchar2(1);
274 l_object_version_number number;
275 begin
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277 fnd_msg_pub.initialize;
278
279 BEGIN
280 SELECT OBJECT_VERSION_NUMBER
281 INTO l_object_version_number
282 FROM HZ_PHONE_COUNTRY_CODES
283 WHERE TERRITORY_CODE = p_territory_code
284 FOR UPDATE OF TERRITORY_CODE NOWAIT;
285
286 IF NOT ((p_object_version_number is null and l_object_version_number is
287 null)
288 OR (p_object_version_number = l_object_version_number))
289 THEN
290 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
291 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PHONE_COUNTRY_CODES');
292 FND_MSG_PUB.ADD;
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296
297 p_object_version_number := nvl(l_object_version_number, 1) + 1;
298
299 EXCEPTION WHEN NO_DATA_FOUND THEN
300 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
301 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_PHONE_AREA_CODES');
302 FND_MESSAGE.SET_TOKEN('VALUE', 'territory_code+area_code');
303 FND_MSG_PUB.ADD;
304 RAISE FND_API.G_EXC_ERROR;
305 END;
306
307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
308 RAISE FND_API.G_EXC_ERROR;
309 END IF;
310
311
312 update hz_phone_country_codes
313 set timezone_id = p_timezone_id,
314 object_version_number = nvl(l_object_version_number, 1) + 1,
315 -- Bug 3032780
316 --CREATED_BY =hz_utility_v2pub.created_by,
317 --CREATION_DATE =hz_utility_v2pub.creation_date,
318 LAST_UPDATE_LOGIN = hz_utility_v2pub.last_update_login,
319 LAST_UPDATE_DATE = hz_utility_v2pub.last_update_date,
320 LAST_UPDATED_BY =hz_utility_v2pub.last_updated_by
321 where territory_code = p_territory_code;
322
323 If (SQL%NOTFOUND) then
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 RAISE NO_DATA_FOUND;
326 End If;
327
328 l_exist_flag := areacode_timezone_exist(p_territory_code);
329 -- if timezone is entered from country level, delete area code level timezone
330 if p_timezone_id is not null and l_exist_flag = 'Y'
331 then
332 update hz_phone_area_codes
333 set timezone_id = null,
334 object_version_number = nvl(object_version_number,1)+1,
335 -- Bug 3032780
336 -- CREATED_BY =hz_utility_v2pub.created_by,
337 -- CREATION_DATE =hz_utility_v2pub.creation_date,
338 LAST_UPDATE_LOGIN = hz_utility_v2pub.last_update_login,
339 LAST_UPDATE_DATE = hz_utility_v2pub.last_update_date,
340 LAST_UPDATED_BY =hz_utility_v2pub.last_updated_by
341 where territory_code = p_territory_code;
342 end if;
343
344 end;
345
346
347 END HZ_TIMEZONE_UTILS_PVT;