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