[Home] [Help]
PACKAGE BODY: APPS.AMS_CAT_ACTIVITIES_PKG
Source
1 PACKAGE BODY AMS_CAT_ACTIVITIES_PKG as
2 /* $Header: amstcacb.pls 115.4 2002/12/27 11:38:08 cgoyal ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_CAT_ACTIVITIES_PKG
7 -- Purpose
8 --
9 -- History
10 -- 30-Jan-2001 abhola Created
11 -- 05-Nov-2001 musman Commented out the reference to security_group_id
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_CAT_ACTIVITIES_PKG';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstcacb.pls';
20
21
22 ----------------------------------------------------------
23 ---- MEDIA ----
24 ----------------------------------------------------------
25
26 -- ========================================================
27 --
28 -- NAME
29 -- createInsertBody
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
39 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
40 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
41
42 PROCEDURE Insert_Row(
43 px_cat_activity_id IN OUT NOCOPY NUMBER,
44 px_object_version_number IN OUT NOCOPY NUMBER,
45 p_category_id NUMBER,
46 p_activity_id NUMBER,
47 p_last_update_date DATE,
48 p_last_updated_by NUMBER,
49 p_creation_date DATE,
50 p_created_by NUMBER,
51 p_last_update_login NUMBER,
52 -- p_security_group_id NUMBER,
53 p_attribute_category VARCHAR2,
54 p_attribute1 VARCHAR2,
55 p_attribute2 VARCHAR2,
56 p_attribute3 VARCHAR2,
57 p_attribute4 VARCHAR2,
58 p_attribute5 VARCHAR2,
59 p_attribute6 VARCHAR2,
60 p_attribute7 VARCHAR2,
61 p_attribute8 VARCHAR2,
62 p_attribute9 VARCHAR2,
63 p_attribute10 VARCHAR2,
64 p_attribute11 VARCHAR2,
65 p_attribute12 VARCHAR2,
66 p_attribute13 VARCHAR2,
67 p_attribute14 VARCHAR2,
68 p_attribute15 VARCHAR2)
69
70 IS
71 x_rowid VARCHAR2(30);
72
73
74 BEGIN
75
76
77 px_object_version_number := 1;
78
79
80 INSERT INTO AMS_CAT_ACTIVITIES(
81 cat_activity_id,
82 object_version_number,
83 category_id,
84 activity_id,
85 last_update_date,
86 last_updated_by,
87 creation_date,
88 created_by,
89 last_update_login,
90 --security_group_id,
91 attribute_category,
92 attribute1,
93 attribute2,
94 attribute3,
95 attribute4,
96 attribute5,
97 attribute6,
98 attribute7,
99 attribute8,
100 attribute9,
101 attribute10,
102 attribute11,
103 attribute12,
104 attribute13,
105 attribute14,
106 attribute15
107 ) VALUES (
108 DECODE( px_cat_activity_id, FND_API.g_miss_num, NULL, px_cat_activity_id),
109 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
110 DECODE( p_category_id, FND_API.g_miss_num, NULL, p_category_id),
111 DECODE( p_activity_id, FND_API.g_miss_num, NULL, p_activity_id),
112 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
113 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
114 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
115 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
116 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
117 --DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
118 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
119 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
120 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
121 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
122 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
123 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
124 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
125 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
126 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
127 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
128 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
129 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
130 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
131 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
132 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
133 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15));
134 END Insert_Row;
135
136
137 ----------------------------------------------------------
138 ---- MEDIA ----
139 ----------------------------------------------------------
140
141 -- ========================================================
142 --
143 -- NAME
144 -- createUpdateBody
145 --
146 -- PURPOSE
147 --
148 -- NOTES
149 --
150 -- HISTORY
151 --
152 -- ========================================================
153 PROCEDURE Update_Row(
154 p_cat_activity_id NUMBER,
155 p_object_version_number NUMBER,
156 p_category_id NUMBER,
157 p_activity_id NUMBER,
158 p_last_update_date DATE,
159 p_last_updated_by NUMBER,
160 p_creation_date DATE,
161 p_created_by NUMBER,
162 p_last_update_login NUMBER,
163 --p_security_group_id NUMBER,
164 p_attribute_category VARCHAR2,
165 p_attribute1 VARCHAR2,
166 p_attribute2 VARCHAR2,
167 p_attribute3 VARCHAR2,
168 p_attribute4 VARCHAR2,
169 p_attribute5 VARCHAR2,
170 p_attribute6 VARCHAR2,
171 p_attribute7 VARCHAR2,
172 p_attribute8 VARCHAR2,
173 p_attribute9 VARCHAR2,
174 p_attribute10 VARCHAR2,
175 p_attribute11 VARCHAR2,
176 p_attribute12 VARCHAR2,
177 p_attribute13 VARCHAR2,
178 p_attribute14 VARCHAR2,
179 p_attribute15 VARCHAR2)
180
181 IS
182 BEGIN
183 Update AMS_CAT_ACTIVITIES
184 SET
185 cat_activity_id = DECODE( p_cat_activity_id, FND_API.g_miss_num, cat_activity_id, p_cat_activity_id),
186 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
187 category_id = DECODE( p_category_id, FND_API.g_miss_num, category_id, p_category_id),
188 activity_id = DECODE( p_activity_id, FND_API.g_miss_num, activity_id, p_activity_id),
189 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
190 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
191 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
192 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
193 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
194 --security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
195 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
196 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
197 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
198 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
199 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
200 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
201 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
202 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
203 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
204 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
205 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
206 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
207 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
208 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
209 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
210 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15)
211 WHERE CAT_ACTIVITY_ID = p_CAT_ACTIVITY_ID
212 AND object_version_number = p_object_version_number;
213
214 IF (SQL%NOTFOUND) THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 END Update_Row;
218
219
220 ----------------------------------------------------------
221 ---- MEDIA ----
222 ----------------------------------------------------------
223
224 -- ========================================================
225 --
226 -- NAME
227 -- createDeleteBody
228 --
229 -- PURPOSE
230 --
231 -- NOTES
232 --
233 -- HISTORY
234 --
235 -- ========================================================
236 PROCEDURE Delete_Row(
237 p_CAT_ACTIVITY_ID NUMBER,
238 p_object_version_number NUMBER)
239 IS
240 BEGIN
241 DELETE FROM AMS_CAT_ACTIVITIES
242 WHERE CAT_ACTIVITY_ID = p_CAT_ACTIVITY_ID
243 AND object_version_number = p_object_version_number;
244 If (SQL%NOTFOUND) then
245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 End If;
247 END Delete_Row ;
248
249
250
251 ----------------------------------------------------------
252 ---- MEDIA ----
253 ----------------------------------------------------------
254
255 -- ========================================================
256 --
257 -- NAME
258 -- createLockBody
259 --
260 -- PURPOSE
261 --
262 -- NOTES
263 --
264 -- HISTORY
265 --
266 -- ========================================================
267 PROCEDURE Lock_Row(
268 p_cat_activity_id NUMBER,
269 p_object_version_number NUMBER,
270 p_category_id NUMBER,
271 p_activity_id NUMBER,
272 p_last_update_date DATE,
273 p_last_updated_by NUMBER,
274 p_creation_date DATE,
275 p_created_by NUMBER,
276 p_last_update_login NUMBER,
277 --p_security_group_id NUMBER,
278 p_attribute_category VARCHAR2,
279 p_attribute1 VARCHAR2,
280 p_attribute2 VARCHAR2,
281 p_attribute3 VARCHAR2,
282 p_attribute4 VARCHAR2,
283 p_attribute5 VARCHAR2,
284 p_attribute6 VARCHAR2,
285 p_attribute7 VARCHAR2,
286 p_attribute8 VARCHAR2,
287 p_attribute9 VARCHAR2,
288 p_attribute10 VARCHAR2,
289 p_attribute11 VARCHAR2,
290 p_attribute12 VARCHAR2,
291 p_attribute13 VARCHAR2,
292 p_attribute14 VARCHAR2,
293 p_attribute15 VARCHAR2)
294
295 IS
296 CURSOR C IS
297 SELECT *
298 FROM AMS_CAT_ACTIVITIES
299 WHERE CAT_ACTIVITY_ID = p_CAT_ACTIVITY_ID
300 FOR UPDATE of CAT_ACTIVITY_ID NOWAIT;
301 Recinfo C%ROWTYPE;
302 BEGIN
303 OPEN c;
304 FETCH c INTO Recinfo;
305 If (c%NOTFOUND) then
306 CLOSE c;
307 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
308 APP_EXCEPTION.RAISE_EXCEPTION;
309 END IF;
310 CLOSE C;
311 IF (
312 ( Recinfo.cat_activity_id = p_cat_activity_id)
313 AND ( ( Recinfo.object_version_number = p_object_version_number)
314 OR ( ( Recinfo.object_version_number IS NULL )
315 AND ( p_object_version_number IS NULL )))
316 AND ( ( Recinfo.category_id = p_category_id)
317 OR ( ( Recinfo.category_id IS NULL )
318 AND ( p_category_id IS NULL )))
319 AND ( ( Recinfo.activity_id = p_activity_id)
320 OR ( ( Recinfo.activity_id IS NULL )
321 AND ( p_activity_id IS NULL )))
322 AND ( ( Recinfo.last_update_date = p_last_update_date)
323 OR ( ( Recinfo.last_update_date IS NULL )
324 AND ( p_last_update_date IS NULL )))
325 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
326 OR ( ( Recinfo.last_updated_by IS NULL )
327 AND ( p_last_updated_by IS NULL )))
328 AND ( ( Recinfo.creation_date = p_creation_date)
329 OR ( ( Recinfo.creation_date IS NULL )
330 AND ( p_creation_date IS NULL )))
331 AND ( ( Recinfo.created_by = p_created_by)
332 OR ( ( Recinfo.created_by IS NULL )
333 AND ( p_created_by IS NULL )))
334 AND ( ( Recinfo.last_update_login = p_last_update_login)
335 OR ( ( Recinfo.last_update_login IS NULL )
336 AND ( p_last_update_login IS NULL )))
337 /*
341 */
338 --AND ( ( Recinfo.security_group_id = p_security_group_id)
339 -- OR ( ( Recinfo.security_group_id IS NULL )
340 -- AND ( p_security_group_id IS NULL )))
342 AND ( ( Recinfo.attribute_category = p_attribute_category)
343 OR ( ( Recinfo.attribute_category IS NULL )
344 AND ( p_attribute_category IS NULL )))
345 AND ( ( Recinfo.attribute1 = p_attribute1)
346 OR ( ( Recinfo.attribute1 IS NULL )
347 AND ( p_attribute1 IS NULL )))
348 AND ( ( Recinfo.attribute2 = p_attribute2)
349 OR ( ( Recinfo.attribute2 IS NULL )
350 AND ( p_attribute2 IS NULL )))
351 AND ( ( Recinfo.attribute3 = p_attribute3)
352 OR ( ( Recinfo.attribute3 IS NULL )
353 AND ( p_attribute3 IS NULL )))
354 AND ( ( Recinfo.attribute4 = p_attribute4)
355 OR ( ( Recinfo.attribute4 IS NULL )
356 AND ( p_attribute4 IS NULL )))
357 AND ( ( Recinfo.attribute5 = p_attribute5)
358 OR ( ( Recinfo.attribute5 IS NULL )
359 AND ( p_attribute5 IS NULL )))
360 AND ( ( Recinfo.attribute6 = p_attribute6)
361 OR ( ( Recinfo.attribute6 IS NULL )
362 AND ( p_attribute6 IS NULL )))
363 AND ( ( Recinfo.attribute7 = p_attribute7)
364 OR ( ( Recinfo.attribute7 IS NULL )
365 AND ( p_attribute7 IS NULL )))
366 AND ( ( Recinfo.attribute8 = p_attribute8)
367 OR ( ( Recinfo.attribute8 IS NULL )
368 AND ( p_attribute8 IS NULL )))
369 AND ( ( Recinfo.attribute9 = p_attribute9)
370 OR ( ( Recinfo.attribute9 IS NULL )
371 AND ( p_attribute9 IS NULL )))
372 AND ( ( Recinfo.attribute10 = p_attribute10)
373 OR ( ( Recinfo.attribute10 IS NULL )
374 AND ( p_attribute10 IS NULL )))
375 AND ( ( Recinfo.attribute11 = p_attribute11)
376 OR ( ( Recinfo.attribute11 IS NULL )
377 AND ( p_attribute11 IS NULL )))
378 AND ( ( Recinfo.attribute12 = p_attribute12)
379 OR ( ( Recinfo.attribute12 IS NULL )
380 AND ( p_attribute12 IS NULL )))
381 AND ( ( Recinfo.attribute13 = p_attribute13)
382 OR ( ( Recinfo.attribute13 IS NULL )
383 AND ( p_attribute13 IS NULL )))
384 AND ( ( Recinfo.attribute14 = p_attribute14)
385 OR ( ( Recinfo.attribute14 IS NULL )
386 AND ( p_attribute14 IS NULL )))
387 AND ( ( Recinfo.attribute15 = p_attribute15)
388 OR ( ( Recinfo.attribute15 IS NULL )
389 AND ( p_attribute15 IS NULL )))
390 ) THEN
391 RETURN;
392 ELSE
393 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
394 APP_EXCEPTION.RAISE_EXCEPTION;
395 END IF;
396 END Lock_Row;
397
398 END AMS_CAT_ACTIVITIES_PKG;