[Home] [Help]
PACKAGE BODY: APPS.CN_SECURITY_PROFILES_PKG
Source
1 PACKAGE BODY CN_SECURITY_PROFILES_PKG AS
2 /* $Header: cntsecpb.pls 115.6 2001/10/29 17:16:49 pkm ship $ */
3 --
4 -- Package Name
5 -- CN_SECURITY_PROFILES_PKG
6 -- Purpose
7 -- Table handler for CN_SECURITY_PROFILES
8 -- Form
9 -- CNSPROF
10 -- Block
11 -- SECURITY_PROFILES
12 --
13 -- History
14 -- 28-Jul-99 Yonghong Mao Created
15
16 PROCEDURE get_security_profile_id(x_security_profile_id IN OUT NUMBER) IS
17 BEGIN
18 SELECT cn_security_profiles_s.NEXTVAL
19 INTO x_security_profile_id
20 FROM dual;
21 END get_security_profile_id;
22
23 PROCEDURE Insert_Row( x_new_rec IN OUT security_profiles_rec_type) IS
24 CURSOR c IS
25 SELECT 1
26 FROM cn_security_profiles
27 WHERE security_profile_id = x_new_rec.security_profile_id;
28 l_dummy number;
29 BEGIN
30 IF x_new_rec.security_profile_id IS NULL THEN
31 get_security_profile_id(x_new_rec.security_profile_id);
32 END IF;
33
34 SELECT
35 Decode(x_new_rec.attribute_category,
36 fnd_api.g_miss_char, NULL,
37 x_new_rec.attribute_category),
38 Decode(x_new_rec.attribute1,
39 fnd_api.g_miss_char, NULL,
40 x_new_rec.attribute1),
41 Decode(x_new_rec.attribute2,
42 fnd_api.g_miss_char, NULL,
43 x_new_rec.attribute2),
44 Decode(x_new_rec.attribute3,
45 fnd_api.g_miss_char, NULL,
46 x_new_rec.attribute3),
47 Decode(x_new_rec.attribute4,
48 fnd_api.g_miss_char, NULL,
49 x_new_rec.attribute4),
50 Decode(x_new_rec.attribute5,
51 fnd_api.g_miss_char, NULL,
52 x_new_rec.attribute5),
53 Decode(x_new_rec.attribute6,
54 fnd_api.g_miss_char, NULL,
55 x_new_rec.attribute6),
56 Decode(x_new_rec.attribute7,
57 fnd_api.g_miss_char, NULL,
58 x_new_rec.attribute7),
59 Decode(x_new_rec.attribute8,
60 fnd_api.g_miss_char, NULL,
61 x_new_rec.attribute8),
62 Decode(x_new_rec.attribute9,
63 fnd_api.g_miss_char, NULL,
64 x_new_rec.attribute9),
65 Decode(x_new_rec.attribute10,
66 fnd_api.g_miss_char, NULL,
67 x_new_rec.attribute10),
68 Decode(x_new_rec.attribute11,
69 fnd_api.g_miss_char, NULL,
70 x_new_rec.attribute11),
71 Decode(x_new_rec.attribute12,
72 fnd_api.g_miss_char, NULL,
73 x_new_rec.attribute12),
74 Decode(x_new_rec.attribute13,
75 fnd_api.g_miss_char, NULL,
76 x_new_rec.attribute13),
77 Decode(x_new_rec.attribute14,
78 fnd_api.g_miss_char, NULL,
79 x_new_rec.attribute14),
80 Decode(x_new_rec.attribute15,
81 fnd_api.g_miss_char, NULL,
82 x_new_rec.attribute15),
83 Decode(x_new_rec.created_by,
84 cn_api.g_miss_id, g_created_by,
85 x_new_rec.created_by),
86 Decode(x_new_rec.creation_date,
87 fnd_api.g_miss_date, g_creation_date,
88 x_new_rec.creation_date),
89 Decode(x_new_rec.last_update_login,
90 cn_api.g_miss_id, g_last_update_login,
91 x_new_rec.last_update_login),
92 Decode(x_new_rec.last_update_date,
93 fnd_api.g_miss_date, g_last_update_date,
94 x_new_rec.last_update_date),
95 Decode(x_new_rec.last_updated_by,
96 cn_api.g_miss_id, g_last_updated_by,
97 x_new_rec.last_updated_by)
98 INTO
99 x_new_rec.attribute_category,
100 x_new_rec.attribute1,
101 x_new_rec.attribute2,
102 x_new_rec.attribute3,
103 x_new_rec.attribute4,
104 x_new_rec.attribute5,
105 x_new_rec.attribute6,
106 x_new_rec.attribute7,
107 x_new_rec.attribute8,
108 x_new_rec.attribute9,
109 x_new_rec.attribute10,
110 x_new_rec.attribute11,
111 x_new_rec.attribute12,
112 x_new_rec.attribute13,
113 x_new_rec.attribute14,
114 x_new_rec.attribute15,
115 x_new_rec.created_by,
116 x_new_rec.creation_date,
117 x_new_rec.last_update_login,
118 x_new_rec.last_update_date,
119 x_new_rec.last_updated_by
120 FROM dual;
121
122 INSERT INTO cn_security_profiles
123 (security_profile_id,
124 profile_user_id,
125 attribute_category,
126 attribute1,
127 attribute2,
128 attribute3,
129 attribute4,
130 attribute5,
131 attribute6,
132 attribute7,
133 attribute8,
134 attribute9,
135 attribute10,
136 attribute11,
137 attribute12,
138 attribute13,
139 attribute14,
140 attribute15,
141 created_by,
142 creation_date,
143 last_update_login,
144 last_update_date,
145 last_updated_by
146 )
147 VALUES
148 (x_new_rec.security_profile_id,
149 x_new_rec.profile_user_id,
150 x_new_rec.attribute_category,
151 x_new_rec.attribute1,
152 x_new_rec.attribute2,
153 x_new_rec.attribute3,
154 x_new_rec.attribute4,
155 x_new_rec.attribute5,
156 x_new_rec.attribute6,
157 x_new_rec.attribute7,
158 x_new_rec.attribute8,
159 x_new_rec.attribute9,
160 x_new_rec.attribute10,
161 x_new_rec.attribute11,
162 x_new_rec.attribute12,
163 x_new_rec.attribute13,
164 x_new_rec.attribute14,
165 x_new_rec.attribute15,
166 x_new_rec.created_by,
167 x_new_rec.creation_date,
168 x_new_rec.last_update_login,
169 x_new_rec.last_update_date,
170 x_new_rec.last_updated_by
171 );
172
173 OPEN c;
174 FETCH c INTO l_dummy;
175 IF (c%notfound) THEN
176 CLOSE c;
177 RAISE no_data_found;
178 END IF;
179 CLOSE c;
180
181 END Insert_Row;
182
183 PROCEDURE Update_Row(x_new_rec security_profiles_rec_type) IS
184 CURSOR c IS
185 SELECT *
186 FROM cn_security_profiles
187 WHERE security_profile_id = x_new_rec.security_profile_id
188 FOR UPDATE OF security_profile_id nowait;
189
190 rec c%ROWTYPE;
191
192 BEGIN
193 OPEN c;
194 FETCH c INTO rec;
195
196 -- Actually, this following check is already done in the LOCK_ROW procedure
197 IF (c%notfound) THEN
198 CLOSE c;
199 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
200 app_exception.raise_exception;
201 END IF;
202 CLOSE c;
203 SELECT
204 Decode(x_new_rec.profile_user_id,
205 cn_api.g_miss_id, rec.profile_user_id,
206 x_new_rec.profile_user_id),
207 Decode(x_new_rec.attribute_category,
208 fnd_api.g_miss_char, rec.attribute_category,
209 x_new_rec.attribute_category),
210 Decode(x_new_rec.attribute1,
211 fnd_api.g_miss_char, rec.attribute1,
212 x_new_rec.attribute1),
213 Decode(x_new_rec.attribute2,
214 fnd_api.g_miss_char, rec.attribute2,
215 x_new_rec.attribute2),
216 Decode(x_new_rec.attribute3,
217 fnd_api.g_miss_char, rec.attribute3,
218 x_new_rec.attribute3),
219 Decode(x_new_rec.attribute4,
220 fnd_api.g_miss_char, rec.attribute4,
221 x_new_rec.attribute4),
222 Decode(x_new_rec.attribute5,
223 fnd_api.g_miss_char, rec.attribute5,
224 x_new_rec.attribute5),
225 Decode(x_new_rec.attribute6,
226 fnd_api.g_miss_char, rec.attribute6,
227 x_new_rec.attribute6),
228 Decode(x_new_rec.attribute7,
229 fnd_api.g_miss_char, rec.attribute7,
230 x_new_rec.attribute7),
231 Decode(x_new_rec.attribute8,
232 fnd_api.g_miss_char, rec.attribute8,
233 x_new_rec.attribute8),
234 Decode(x_new_rec.attribute9,
235 fnd_api.g_miss_char, rec.attribute9,
236 x_new_rec.attribute9),
237 Decode(x_new_rec.attribute10,
238 fnd_api.g_miss_char, rec.attribute10,
239 x_new_rec.attribute10),
240 Decode(x_new_rec.attribute11,
241 fnd_api.g_miss_char, rec.attribute11,
242 x_new_rec.attribute11),
243 Decode(x_new_rec.attribute12,
244 fnd_api.g_miss_char, rec.attribute12,
245 x_new_rec.attribute12),
246 Decode(x_new_rec.attribute13,
247 fnd_api.g_miss_char, rec.attribute13,
248 x_new_rec.attribute13),
249 Decode(x_new_rec.attribute14,
250 fnd_api.g_miss_char, rec.attribute14,
251 x_new_rec.attribute14),
252 Decode(x_new_rec.attribute15,
253 fnd_api.g_miss_char, rec.attribute15,
254 x_new_rec.attribute15),
255 Decode(x_new_rec.last_update_login,
256 cn_api.g_miss_id, g_last_update_login,
257 x_new_rec.last_update_login),
258 Decode(x_new_rec.last_update_date,
259 fnd_api.g_miss_date, g_last_update_date,
260 x_new_rec.last_update_date),
261 Decode(x_new_rec.last_updated_by,
262 cn_api.g_miss_id, g_last_updated_by,
263 x_new_rec.last_updated_by)
264 INTO
265 rec.profile_user_id,
266 rec.attribute_category,
267 rec.attribute1,
268 rec.attribute2,
269 rec.attribute3,
270 rec.attribute4,
271 rec.attribute5,
272 rec.attribute6,
273 rec.attribute7,
274 rec.attribute8,
275 rec.attribute9,
276 rec.attribute10,
277 rec.attribute11,
278 rec.attribute12,
279 rec.attribute13,
280 rec.attribute14,
281 rec.attribute15,
282 rec.last_update_login,
283 rec.last_update_date,
284 rec.last_updated_by
285 FROM dual;
286
287 UPDATE cn_security_profiles SET
288 profile_user_id = rec.profile_user_id,
289 attribute_category = rec.attribute_category,
290 attribute1 = rec.attribute1,
291 attribute2 = rec.attribute2,
292 attribute3 = rec.attribute3,
293 attribute4 = rec.attribute4,
294 attribute5 = rec.attribute5,
295 attribute6 = rec.attribute6,
296 attribute7 = rec.attribute7,
297 attribute8 = rec.attribute8,
298 attribute9 = rec.attribute9,
299 attribute10 = rec.attribute10,
300 attribute11 = rec.attribute11,
301 attribute12 = rec.attribute12,
302 attribute13 = rec.attribute13,
303 attribute14 = rec.attribute14,
304 attribute15 = rec.attribute15,
305 last_update_login = rec.last_update_login,
306 last_update_date = rec.last_update_date,
307 last_updated_by = rec.last_updated_by
308 WHERE security_profile_id = x_new_rec.security_profile_id;
309
310 IF (SQL%notfound) THEN
311 RAISE no_data_found;
312 END IF;
313
314 END Update_Row;
315
316 PROCEDURE Lock_Row(x_rec security_profiles_rec_type) IS
317 CURSOR c IS
318 SELECT *
319 FROM cn_security_profiles
320 WHERE security_profile_id = x_rec.security_profile_id
321 FOR UPDATE OF security_profile_id nowait;
322 recinfo c%ROWTYPE;
323
324 BEGIN
325 OPEN c;
326 FETCH c INTO recinfo;
327
328 IF (c%notfound) THEN
329 CLOSE c;
330 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
331 app_exception.raise_exception;
332 END IF;
333 CLOSE c;
334
335 IF ( (recinfo.security_profile_id = x_rec.security_profile_id) AND
336 (recinfo.profile_user_id = x_rec.profile_user_id) AND
337 (
338 (recinfo.attribute_category = x_rec.attribute_category) OR
339 (recinfo.attribute_category IS NULL AND x_rec.attribute_category IS NULL)
340 ) AND
341 (
342 (recinfo.attribute1 = x_rec.attribute1) OR
343 (recinfo.attribute1 IS NULL AND x_rec.attribute1 IS NULL)
344 ) AND
345 (
346 (recinfo.attribute2 = x_rec.attribute2) OR
347 (recinfo.attribute2 IS NULL AND x_rec.attribute2 IS NULL)
348 ) AND
349 (
350 (recinfo.attribute3 = x_rec.attribute3) OR
351 (recinfo.attribute3 IS NULL AND x_rec.attribute3 IS NULL)
352 ) AND
353 (
354 (recinfo.attribute4 = x_rec.attribute4) OR
355 (recinfo.attribute4 IS NULL AND x_rec.attribute4 IS NULL)
356 ) AND
357 (
358 (recinfo.attribute5 = x_rec.attribute5) OR
359 (recinfo.attribute5 IS NULL AND x_rec.attribute5 IS NULL)
360 ) AND
361 (
362 (recinfo.attribute6 = x_rec.attribute6) OR
363 (recinfo.attribute6 IS NULL AND x_rec.attribute6 IS NULL)
364 ) AND
365 (
366 (recinfo.attribute7 = x_rec.attribute7) OR
367 (recinfo.attribute7 IS NULL AND x_rec.attribute7 IS NULL)
368 ) AND
369 (
370 (recinfo.attribute8 = x_rec.attribute8) OR
371 (recinfo.attribute8 IS NULL AND x_rec.attribute8 IS NULL)
372 ) AND
373 (
374 (recinfo.attribute9 = x_rec.attribute9) OR
375 (recinfo.attribute9 IS NULL AND x_rec.attribute9 IS NULL)
376 ) AND
377 (
378 (recinfo.attribute10 = x_rec.attribute10) OR
379 (recinfo.attribute10 IS NULL AND x_rec.attribute10 IS NULL)
380 ) AND
381 (
382 (recinfo.attribute11 = x_rec.attribute11) OR
383 (recinfo.attribute11 IS NULL AND x_rec.attribute11 IS NULL)
384 ) AND
385 (
386 (recinfo.attribute12 = x_rec.attribute12) OR
387 (recinfo.attribute12 IS NULL AND x_rec.attribute12 IS NULL)
388 ) AND
389 (
390 (recinfo.attribute13 = x_rec.attribute13) OR
391 (recinfo.attribute13 IS NULL AND x_rec.attribute13 IS NULL)
392 ) AND
393 (
394 (recinfo.attribute14 = x_rec.attribute14) OR
395 (recinfo.attribute14 IS NULL AND x_rec.attribute14 IS NULL)
396 ) AND
397 (
398 (recinfo.attribute15 = x_rec.attribute15) OR
399 (recinfo.attribute15 IS NULL AND x_rec.attribute15 IS NULL)
400 )
401 ) THEN
402 RETURN;
403 ELSE
404 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
405 app_exception.raise_exception;
406 END IF;
407
408 END Lock_Row;
409
410 PROCEDURE Delete_Row(x_security_profile_id NUMBER) IS
411 BEGIN
412 DELETE cn_security_profiles
413 WHERE security_profile_id = x_security_profile_id;
414
415 IF (SQL%notfound) THEN
416 RAISE no_data_found;
417 END IF;
418
419 END Delete_Row;
420
421 END CN_SECURITY_PROFILES_PKG;