DBA Data[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;