[Home] [Help]
PACKAGE BODY: APPS.IEX_WEBSWITCH_PVT
Source
1 package body iex_WebSwitch_PVT as
2 /* $Header: iexvadsb.pls 120.1 2005/07/06 19:23:53 jypark noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='IEX_WEBSWITCH_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) :='iexvadsb.pls';
6
7 G_APPL_ID NUMBER;
8 G_LOGIN_ID NUMBER;
9 G_PROGRAM_ID NUMBER;
10 G_USER_ID NUMBER;
11 G_REQUEST_ID NUMBER;
12
13 PROCEDURE Create_WebSwitch(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 p_commit IN VARCHAR2,
17 p_validation_level IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_cgi_switch_rec IN cgi_switch_rec_type,
22 p_switch_data_rec IN switch_data_rec_type
23 )
24 AS
25 l_api_version CONSTANT NUMBER := 1.0;
26 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebSwitch';
27 l_return_status VARCHAR2(1);
28 l_msg_count NUMBER;
29 l_msg_data VARCHAR2(32767);
30
31 -- added these for user hooks
32 l_cgi_switch_rec IEX_WEBSWITCH_PKG.cgi_switch_rec_type;
33 l_switch_data_rec IEX_WEBSWITCH_PKG.switch_data_rec_type;
34 l_cgi_switch_id NUMBER;
35 l_switch_data_id NUMBER;
36
37 CURSOR c_cgi_switch IS SELECT CGI_SWITCH_ID
38 FROM IEX_CGI_SWITCHES
39 WHERE QUERY_STRING_ID =l_cgi_switch_rec.QUERY_STRING_ID
40 AND SWITCH_CODE = l_cgi_switch_rec.SWITCH_CODE;
41 CURSOR c_switch_data IS SELECT IEX_SWITCH_DATA_S.NEXTVAL
42 FROM SYS.DUAL;
43 begin
44 -- Standard begin of API savepoint
45 SAVEPOINT Create_WebSwitch_PUB;
46
47 -- Standard call to check for call compatibility.
48 IF NOT FND_API.Compatible_API_Call (l_api_version,
49 p_api_version,
50 l_api_name,
51 G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55
56 -- Check p_init_msg_list
57 IF FND_API.to_Boolean( p_init_msg_list ) THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 -- Implementation of User Hooks
62 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
63 /* l_rec - will be used as In Out NOCOPY parameter in pre/post/Business API calls */
64 /* l_return_status - will be a out NOCOPY variable to get return code from called APIs */
65
66 l_cgi_switch_rec.query_string_id := p_cgi_switch_rec.query_string_id;
67 l_cgi_switch_rec.object_version_number := 1.0;
68 l_cgi_switch_rec.enabled_flag := p_cgi_switch_rec.enabled_flag;
69 l_cgi_switch_rec.switch_code := p_cgi_switch_rec.switch_code;
70 l_cgi_switch_rec.switch_type := p_cgi_switch_rec.switch_type;
71 l_cgi_switch_rec.is_required_yn := p_cgi_switch_rec.is_required_yn;
72 l_cgi_switch_rec.sort_order := p_cgi_switch_rec.sort_order;
73 l_cgi_switch_rec.data_separator := p_cgi_switch_rec.data_separator;
74 l_cgi_switch_rec.last_update_date := sysdate;
75 l_cgi_switch_rec.creation_date := sysdate;
76 l_cgi_switch_rec.created_by := g_user_id;
77 l_cgi_switch_rec.last_updated_by := g_user_id;
78 l_cgi_switch_rec.last_update_login := g_login_id;
79 l_cgi_switch_rec.attribute_category := p_cgi_switch_rec.attribute_category;
80 l_cgi_switch_rec.attribute1 := p_cgi_switch_rec.attribute1;
81 l_cgi_switch_rec.attribute2 := p_cgi_switch_rec.attribute2;
82 l_cgi_switch_rec.attribute3 := p_cgi_switch_rec.attribute3;
83 l_cgi_switch_rec.attribute4 := p_cgi_switch_rec.attribute4;
84 l_cgi_switch_rec.attribute5 := p_cgi_switch_rec.attribute5;
85 l_cgi_switch_rec.attribute6 := p_cgi_switch_rec.attribute6;
86 l_cgi_switch_rec.attribute7 := p_cgi_switch_rec.attribute7;
87 l_cgi_switch_rec.attribute8 := p_cgi_switch_rec.attribute8;
88 l_cgi_switch_rec.attribute9 := p_cgi_switch_rec.attribute9;
89 l_cgi_switch_rec.attribute10 := p_cgi_switch_rec.attribute10;
90 l_cgi_switch_rec.attribute11 := p_cgi_switch_rec.attribute11;
91 l_cgi_switch_rec.attribute12 := p_cgi_switch_rec.attribute12;
92 l_cgi_switch_rec.attribute13 := p_cgi_switch_rec.attribute13;
93 l_cgi_switch_rec.attribute14 := p_cgi_switch_rec.attribute14;
94 l_cgi_switch_rec.attribute15 := p_cgi_switch_rec.attribute15;
95
96 l_switch_data_rec.object_version_number := 1.0;
97 l_switch_data_rec.enabled_flag := p_switch_data_rec.enabled_flag;
98 l_switch_data_rec.first_name_yn := p_switch_data_rec.first_name_yn;
99 l_switch_data_rec.last_name_yn := p_switch_data_rec.last_name_yn;
100 l_switch_data_rec.city_yn := p_switch_data_rec.city_yn;
101 l_switch_data_rec.state_yn := p_switch_data_rec.state_yn;
102 l_switch_data_rec.zip_yn := p_switch_data_rec.zip_yn;
103 l_switch_data_rec.country_yn := p_switch_data_rec.country_yn;
104 l_switch_data_rec.address_yn := p_switch_data_rec.address_yn;
105 l_switch_data_rec.last_update_date := sysdate;
106 l_switch_data_rec.creation_date := sysdate;
107 l_switch_data_rec.created_by := g_user_id;
108 l_switch_data_rec.last_updated_by := g_user_id;
109 l_switch_data_rec.last_update_login := g_login_id;
110 l_switch_data_rec.attribute_category := p_switch_data_rec.attribute_category;
111 l_switch_data_rec.attribute1 := p_switch_data_rec.attribute1;
112 l_switch_data_rec.attribute2 := p_switch_data_rec.attribute2;
113 l_switch_data_rec.attribute3 := p_switch_data_rec.attribute3;
114 l_switch_data_rec.attribute4 := p_switch_data_rec.attribute4;
115 l_switch_data_rec.attribute5 := p_switch_data_rec.attribute5;
116 l_switch_data_rec.attribute6 := p_switch_data_rec.attribute6;
117 l_switch_data_rec.attribute7 := p_switch_data_rec.attribute7;
118 l_switch_data_rec.attribute8 := p_switch_data_rec.attribute8;
119 l_switch_data_rec.attribute9 := p_switch_data_rec.attribute9;
120 l_switch_data_rec.attribute10 := p_switch_data_rec.attribute10;
121 l_switch_data_rec.attribute11 := p_switch_data_rec.attribute11;
122 l_switch_data_rec.attribute12 := p_switch_data_rec.attribute12;
123 l_switch_data_rec.attribute13 := p_switch_data_rec.attribute13;
124 l_switch_data_rec.attribute14 := p_switch_data_rec.attribute14;
125 l_switch_data_rec.attribute15 := p_switch_data_rec.attribute15;
126
127 -- Initialize API return status to success
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 -- API body
131 OPEN c_cgi_switch;
132 FETCH c_cgi_switch INTO l_cgi_switch_id;
133 IF c_cgi_switch%NOTFOUND THEN
134 CLOSE c_cgi_switch;
135 DECLARE
136 CURSOR c_cgi_switch_seq IS SELECT IEX_CGI_SWITCHES_S.NEXTVAL
137 FROM SYS.DUAL;
138 BEGIN
139 OPEN c_cgi_switch_seq;
140 FETCH c_cgi_switch_seq INTO l_cgi_switch_id;
141 CLOSE c_cgi_switch_seq;
142 EXCEPTION
143 WHEN OTHERS THEN
144 null;
145 END;
146 ELSE
147 CLOSE c_cgi_switch;
148 END IF;
149
150 OPEN c_switch_data;
151 FETCH c_switch_data INTO l_switch_data_id;
152 CLOSE c_switch_data;
153
154
155 l_cgi_switch_rec.cgi_switch_id := l_cgi_switch_id;
156 l_switch_data_rec.switch_data_id := l_switch_data_id;
157 l_switch_data_rec.cgi_switch_id := l_cgi_switch_id;
158
159 IEX_WEBSWITCH_PKG.Create_WebSwitch(
160 p_api_version => p_api_version,
161 p_init_msg_list => p_init_msg_list,
162 p_commit => p_commit,
163 p_validation_level => p_validation_level,
164 x_return_status => l_return_status,
165 x_msg_count => l_msg_count,
166 x_msg_data => l_msg_data,
167 p_cgi_switch_rec => l_cgi_switch_rec,
168 p_switch_data_rec => l_switch_data_rec
169 );
170
171 -- END of API body
172
173 -- Standard check of p_commit.
174 IF FND_API.To_Boolean ( p_commit ) THEN
175 COMMIT WORK;
176 END IF;
177
178 -- Standard call to get message count and IF count is 1, get message info.
179 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
180 p_data => x_msg_data );
181
182
183 --
184 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
185 --
186 EXCEPTION
187
188 WHEN FND_API.G_EXC_ERROR THEN
189
190 ROLLBACK TO Create_WebSwitch_PUB;
191 x_return_status := FND_API.G_RET_STS_ERROR ;
192
193 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
194 p_data => x_msg_data );
195
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197
198 ROLLBACK TO Create_WebSwitch_PUB;
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
200
201 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
202 p_data => x_msg_data );
203
204 WHEN OTHERS THEN
205
206 ROLLBACK TO Create_WebSwitch_PUB;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208
209 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
210 THEN
211 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
212 END IF;
213
214 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
215 p_data => x_msg_data );
216
217 END;
218
219 PROCEDURE Update_WebSwitch(
220 p_api_version IN NUMBER,
221 p_init_msg_list IN VARCHAR2,
222 p_commit IN VARCHAR2,
223 p_validation_level IN NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 p_cgi_switch_rec IN cgi_switch_rec_type,
228 p_switch_data_rec IN switch_data_rec_type
229 )
230 AS
231 l_api_version CONSTANT NUMBER := 1.0;
232 l_api_name CONSTANT VARCHAR2(30) := 'Update_WebSwitch';
233 l_return_status VARCHAR2(1);
234 l_msg_count NUMBER;
235 l_msg_data VARCHAR2(32767);
236 -- added these for user hooks
237 l_cgi_switch_rec IEX_WEBSWITCH_PKG.cgi_switch_rec_type;
238 l_switch_data_rec IEX_WEBSWITCH_PKG.switch_data_rec_type;
239
240 begin
241 -- Standard begin of API savepoint
242 SAVEPOINT Update_WebSwitch_PVT;
243
244 -- Standard call to check for call compatibility.
245 IF NOT FND_API.Compatible_API_Call (l_api_version,
246 p_api_version,
247 l_api_name,
248 G_PKG_NAME)
249 THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252
253 -- Check p_init_msg_list
254 IF FND_API.to_Boolean( p_init_msg_list ) THEN
255 FND_MSG_PUB.initialize;
256 END IF;
257
258 -- Implementation of User Hooks
259 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
260 /* l_rec - will be used as In Out NOCOPY parameter in pre/post/Business API calls */
261 /* l_return_status - will be a out NOCOPY variable to get return code from called APIs */
262
263 -- Initialize API return status to success
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265
266 -- API body
267
268 l_cgi_switch_rec.query_string_id := p_cgi_switch_rec.query_string_id;
269 l_cgi_switch_rec.cgi_switch_id := p_cgi_switch_rec.cgi_switch_id;
270 l_cgi_switch_rec.object_version_number := 1.0;
271 l_cgi_switch_rec.enabled_flag := p_cgi_switch_rec.enabled_flag;
272 l_cgi_switch_rec.switch_code := p_cgi_switch_rec.switch_code;
273 l_cgi_switch_rec.switch_type := p_cgi_switch_rec.switch_type;
274 l_cgi_switch_rec.is_required_yn := p_cgi_switch_rec.is_required_yn;
275 l_cgi_switch_rec.sort_order := p_cgi_switch_rec.sort_order;
276 l_cgi_switch_rec.data_separator := p_cgi_switch_rec.data_separator;
277 l_cgi_switch_rec.last_update_date := sysdate;
278 l_cgi_switch_rec.creation_date := p_cgi_switch_rec.creation_date;
279 l_cgi_switch_rec.created_by := p_cgi_switch_rec.created_by;
280 l_cgi_switch_rec.last_updated_by := g_user_id;
281 l_cgi_switch_rec.last_update_login := g_login_id;
282 l_cgi_switch_rec.attribute_category := p_cgi_switch_rec.attribute_category;
283 l_cgi_switch_rec.attribute1 := p_cgi_switch_rec.attribute1;
284 l_cgi_switch_rec.attribute2 := p_cgi_switch_rec.attribute2;
285 l_cgi_switch_rec.attribute3 := p_cgi_switch_rec.attribute3;
286 l_cgi_switch_rec.attribute4 := p_cgi_switch_rec.attribute4;
287 l_cgi_switch_rec.attribute5 := p_cgi_switch_rec.attribute5;
288 l_cgi_switch_rec.attribute6 := p_cgi_switch_rec.attribute6;
289 l_cgi_switch_rec.attribute7 := p_cgi_switch_rec.attribute7;
290 l_cgi_switch_rec.attribute8 := p_cgi_switch_rec.attribute8;
291 l_cgi_switch_rec.attribute9 := p_cgi_switch_rec.attribute9;
292 l_cgi_switch_rec.attribute10 := p_cgi_switch_rec.attribute10;
293 l_cgi_switch_rec.attribute11 := p_cgi_switch_rec.attribute11;
294 l_cgi_switch_rec.attribute12 := p_cgi_switch_rec.attribute12;
295 l_cgi_switch_rec.attribute13 := p_cgi_switch_rec.attribute13;
296 l_cgi_switch_rec.attribute14 := p_cgi_switch_rec.attribute14;
297 l_cgi_switch_rec.attribute15 := p_cgi_switch_rec.attribute15;
298
299 l_switch_data_rec.switch_data_id := p_switch_data_rec.switch_data_id;
300 l_switch_data_rec.cgi_switch_id := p_switch_data_rec.cgi_switch_id;
301 l_switch_data_rec.object_version_number := 1.0;
302 l_switch_data_rec.enabled_flag := p_switch_data_rec.enabled_flag;
303 l_switch_data_rec.first_name_yn := p_switch_data_rec.first_name_yn;
304 l_switch_data_rec.last_name_yn := p_switch_data_rec.last_name_yn;
305 l_switch_data_rec.city_yn := p_switch_data_rec.city_yn;
306 l_switch_data_rec.state_yn := p_switch_data_rec.state_yn;
307 l_switch_data_rec.zip_yn := p_switch_data_rec.zip_yn;
308 l_switch_data_rec.country_yn := p_switch_data_rec.country_yn;
309 l_switch_data_rec.address_yn := p_switch_data_rec.address_yn;
310 l_switch_data_rec.last_update_date := sysdate;
311 l_switch_data_rec.creation_date := p_switch_data_rec.creation_date;
312 l_switch_data_rec.created_by := p_switch_data_rec.created_by;
313 l_switch_data_rec.last_updated_by := g_user_id;
314 l_switch_data_rec.last_update_login := g_login_id;
315 l_switch_data_rec.attribute_category := p_switch_data_rec.attribute_category;
316 l_switch_data_rec.attribute1 := p_switch_data_rec.attribute1;
317 l_switch_data_rec.attribute2 := p_switch_data_rec.attribute2;
318 l_switch_data_rec.attribute3 := p_switch_data_rec.attribute3;
319 l_switch_data_rec.attribute4 := p_switch_data_rec.attribute4;
320 l_switch_data_rec.attribute5 := p_switch_data_rec.attribute5;
321 l_switch_data_rec.attribute6 := p_switch_data_rec.attribute6;
322 l_switch_data_rec.attribute7 := p_switch_data_rec.attribute7;
323 l_switch_data_rec.attribute8 := p_switch_data_rec.attribute8;
324 l_switch_data_rec.attribute9 := p_switch_data_rec.attribute9;
325 l_switch_data_rec.attribute10 := p_switch_data_rec.attribute10;
326 l_switch_data_rec.attribute11 := p_switch_data_rec.attribute11;
327 l_switch_data_rec.attribute12 := p_switch_data_rec.attribute12;
328 l_switch_data_rec.attribute13 := p_switch_data_rec.attribute13;
329 l_switch_data_rec.attribute14 := p_switch_data_rec.attribute14;
330 l_switch_data_rec.attribute15 := p_switch_data_rec.attribute15;
331
332
333 IEX_WEBSWITCH_PKG.Update_WebSwitch(
334 p_api_version => p_api_version,
335 p_init_msg_list => p_init_msg_list,
336 p_commit => p_commit,
337 p_validation_level => p_validation_level,
338 x_return_status => l_return_status,
339 x_msg_count => l_msg_count,
340 x_msg_data => l_msg_data,
341 p_cgi_switch_rec => l_cgi_switch_rec,
342 p_switch_data_rec => l_switch_data_rec
343 );
344
345 -- END of API body
346
347
348 -- Standard check of p_commit.
349 IF FND_API.To_Boolean ( p_commit ) THEN
350 COMMIT WORK;
351 END IF;
352
353 -- Standard call to get message count and IF count is 1, get message info.
354 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
355 p_data => x_msg_data );
356
357
358 --
359 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
360 --
361 EXCEPTION
362
363 WHEN FND_API.G_EXC_ERROR THEN
364
365 ROLLBACK TO Update_WebSwitch_PVT;
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367
368 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
369 p_data => x_msg_data );
370
371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
372
373 ROLLBACK TO Update_WebSwitch_PVT;
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
375
376 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
377 p_data => x_msg_data );
378
379 WHEN OTHERS THEN
380
381 ROLLBACK TO Update_WebSwitch_PVT;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383
384 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
385 THEN
386 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
387 END IF;
388
389 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
390 p_data => x_msg_data );
391
392 END;
393 BEGIN
394
395 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
396 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
397 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
398 G_USER_ID := FND_GLOBAL.User_Id;
399 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
400 END;