[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_CATEGORIES_PKG
Source
1 PACKAGE BODY CN_QUOTA_CATEGORIES_PKG AS
2 /* $Header: cntqcatb.pls 115.5 2002/01/28 20:05:11 pkm ship $*/
3
4
5 -- * -------------------------------------------------------------------------*
6 -- Procedure Name
7 -- Insert_row
8 -- Purpose
9 -- Main insert procedure
10 -- Note
11 -- 1. Primary key should be populated from sequence before call
12 -- this procedure. No refernece to sequence in this procedure.
13 -- 2. All paramaters are IN parameter.
14 -- * -------------------------------------------------------------------------*
15 PROCEDURE insert_row
16 ( p_quota_categories_rec IN QUOTA_CATEGORIES_REC_TYPE) IS
17
18 l_quota_category_id NUMBER := 0;
19
20 BEGIN
21 --
22 SELECT cn_quota_categories_s.NEXTVAL
23 INTO l_quota_category_id
24 FROM dual;
25 --
26 INSERT into CN_QUOTA_CATEGORIES
27 ( QUOTA_CATEGORY_ID,
28 NAME,
29 DESCRIPTION,
30 TYPE,
31 COMPUTE_FLAG,
32 INTERVAL_TYPE_ID,
33 QUOTA_UNIT_CODE,
34 ATTRIBUTE_CATEGORY,
35 ATTRIBUTE1,
36 ATTRIBUTE2,
37 ATTRIBUTE3,
38 ATTRIBUTE4,
39 ATTRIBUTE5,
40 ATTRIBUTE6,
41 ATTRIBUTE7,
42 ATTRIBUTE8,
43 ATTRIBUTE9,
44 ATTRIBUTE10,
45 ATTRIBUTE11,
46 ATTRIBUTE12,
47 ATTRIBUTE13,
48 ATTRIBUTE14,
49 ATTRIBUTE15,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 CREATION_DATE,
54 CREATED_BY,
55 OBJECT_VERSION_NUMBER)
56 select
57 l_quota_category_id,
58 DECODE(p_quota_categories_rec.NAME, FND_API.G_MISS_CHAR, NULL,
59 p_quota_categories_rec.NAME),
60 DECODE(p_quota_categories_rec.DESCRIPTION, FND_API.G_MISS_CHAR, NULL,
61 p_quota_categories_rec.DESCRIPTION),
62 DECODE(p_quota_categories_rec.TYPE, FND_API.G_MISS_CHAR, NULL,
63 p_quota_categories_rec.TYPE),
64 DECODE(p_quota_categories_rec.COMPUTE_FLAG, FND_API.G_MISS_CHAR, NULL,
65 p_quota_categories_rec.COMPUTE_FLAG),
66 DECODE(p_quota_categories_rec.INTERVAL_TYPE_ID, FND_API.G_MISS_NUM, NULL,
67 p_quota_categories_rec.INTERVAL_TYPE_ID),
68 DECODE(p_quota_categories_rec.QUOTA_UNIT_CODE, FND_API.G_MISS_CHAR, NULL,
69 p_quota_categories_rec.QUOTA_UNIT_CODE),
70 DECODE(p_quota_categories_rec.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,
71 p_quota_categories_rec.ATTRIBUTE_CATEGORY),
72 DECODE(p_quota_categories_rec.ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,
73 p_quota_categories_rec.ATTRIBUTE1),
74 DECODE(p_quota_categories_rec.ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,
75 p_quota_categories_rec.ATTRIBUTE2),
76 DECODE(p_quota_categories_rec.ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,
77 p_quota_categories_rec.ATTRIBUTE3),
78 DECODE(p_quota_categories_rec.ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,
79 p_quota_categories_rec.ATTRIBUTE4),
80 DECODE(p_quota_categories_rec.ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,
81 p_quota_categories_rec.ATTRIBUTE5),
82 DECODE(p_quota_categories_rec.ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,
83 p_quota_categories_rec.ATTRIBUTE6),
84 DECODE(p_quota_categories_rec.ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,
85 p_quota_categories_rec.ATTRIBUTE7),
86 DECODE(p_quota_categories_rec.ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,
87 p_quota_categories_rec.ATTRIBUTE8),
88 DECODE(p_quota_categories_rec.ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,
89 p_quota_categories_rec.ATTRIBUTE9),
90 DECODE(p_quota_categories_rec.ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,
91 p_quota_categories_rec.ATTRIBUTE10),
92 DECODE(p_quota_categories_rec.ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,
93 p_quota_categories_rec.ATTRIBUTE11),
94 DECODE(p_quota_categories_rec.ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,
95 p_quota_categories_rec.ATTRIBUTE12),
96 DECODE(p_quota_categories_rec.ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,
97 p_quota_categories_rec.ATTRIBUTE13),
98 DECODE(p_quota_categories_rec.ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,
99 p_quota_categories_rec.ATTRIBUTE14),
100 DECODE(p_quota_categories_rec.ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,
101 p_quota_categories_rec.ATTRIBUTE15),
102 G_LAST_UPDATE_DATE,
103 G_LAST_UPDATED_BY,
104 G_LAST_UPDATE_LOGIN,
105 G_CREATION_DATE,
106 G_CREATED_BY,
107 1
108 from dual;
109
110 END insert_row;
111
112
113 -- * -------------------------------------------------------------------------*
114 -- Procedure Name
115 -- update_row
116 -- Purpose
117 -- Main update procedure
118 -- Note
119 -- 1. No object version checking, overwrite may happen
120 -- 2. Calling lock_update for object version checking
121 -- 3. All paramaters are IN parameter.
122 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
123 -- * -------------------------------------------------------------------------*
124 PROCEDURE update_row
125 ( p_quota_categories_rec IN QUOTA_CATEGORIES_REC_TYPE) IS
126
127 BEGIN
128
129 UPDATE CN_QUOTA_CATEGORIES oldrec
130 SET
131 NAME = DECODE(p_quota_categories_rec.NAME,
132 FND_API.G_MISS_CHAR,
133 oldrec.NAME,
134 p_quota_categories_rec.NAME),
135 DESCRIPTION = DECODE(p_quota_categories_rec.DESCRIPTION,
136 FND_API.G_MISS_CHAR,
137 oldrec.DESCRIPTION,
138 p_quota_categories_rec.DESCRIPTION),
139 TYPE = DECODE(p_quota_categories_rec.TYPE,
140 FND_API.G_MISS_CHAR,
141 oldrec.TYPE,
142 p_quota_categories_rec.TYPE),
143 COMPUTE_FLAG = DECODE(p_quota_categories_rec.COMPUTE_FLAG,
144 FND_API.G_MISS_CHAR,
145 oldrec.COMPUTE_FLAG,
146 p_quota_categories_rec.COMPUTE_FLAG),
147 INTERVAL_TYPE_ID = DECODE(p_quota_categories_rec.INTERVAL_TYPE_ID,
148 FND_API.G_MISS_NUM,
149 oldrec.INTERVAL_TYPE_ID,
150 p_quota_categories_rec.INTERVAL_TYPE_ID),
151 QUOTA_UNIT_CODE = DECODE(p_quota_categories_rec.QUOTA_UNIT_CODE,
152 FND_API.G_MISS_CHAR,
153 oldrec.QUOTA_UNIT_CODE,
154 p_quota_categories_rec.QUOTA_UNIT_CODE),
155 ATTRIBUTE_CATEGORY = DECODE(p_quota_categories_rec.ATTRIBUTE_CATEGORY,
156 FND_API.G_MISS_CHAR,
157 oldrec.ATTRIBUTE_CATEGORY,
158 p_quota_categories_rec.ATTRIBUTE_CATEGORY),
159 ATTRIBUTE1 = DECODE(p_quota_categories_rec.ATTRIBUTE1,
160 FND_API.G_MISS_CHAR,
161 oldrec.ATTRIBUTE1,
162 p_quota_categories_rec.ATTRIBUTE1),
163 ATTRIBUTE2 = DECODE(p_quota_categories_rec.ATTRIBUTE2,
164 FND_API.G_MISS_CHAR,
165 oldrec.ATTRIBUTE2,
166 p_quota_categories_rec.ATTRIBUTE2),
167 ATTRIBUTE3 = DECODE(p_quota_categories_rec.ATTRIBUTE3,
168 FND_API.G_MISS_CHAR,
169 oldrec.ATTRIBUTE3,
170 p_quota_categories_rec.ATTRIBUTE3),
171 ATTRIBUTE4 = DECODE(p_quota_categories_rec.ATTRIBUTE4,
172 FND_API.G_MISS_CHAR,
173 oldrec.ATTRIBUTE4,
174 p_quota_categories_rec.ATTRIBUTE4),
175 ATTRIBUTE5 = DECODE(p_quota_categories_rec.ATTRIBUTE5,
176 FND_API.G_MISS_CHAR,
177 oldrec.ATTRIBUTE5,
178 p_quota_categories_rec.ATTRIBUTE5),
179 ATTRIBUTE6 = DECODE(p_quota_categories_rec.ATTRIBUTE6,
180 FND_API.G_MISS_CHAR,
181 oldrec.ATTRIBUTE6,
182 p_quota_categories_rec.ATTRIBUTE6),
183 ATTRIBUTE7 = DECODE(p_quota_categories_rec.ATTRIBUTE7,
184 FND_API.G_MISS_CHAR,
185 oldrec.ATTRIBUTE7,
186 p_quota_categories_rec.ATTRIBUTE7),
187 ATTRIBUTE8 = DECODE(p_quota_categories_rec.ATTRIBUTE8,
188 FND_API.G_MISS_CHAR,
189 oldrec.ATTRIBUTE8,
190 p_quota_categories_rec.ATTRIBUTE8),
191 ATTRIBUTE9 = DECODE(p_quota_categories_rec.ATTRIBUTE9,
192 FND_API.G_MISS_CHAR,
193 oldrec.ATTRIBUTE9,
194 p_quota_categories_rec.ATTRIBUTE9),
195 ATTRIBUTE10 = DECODE(p_quota_categories_rec.ATTRIBUTE10,
196 FND_API.G_MISS_CHAR,
197 oldrec.ATTRIBUTE10,
198 p_quota_categories_rec.ATTRIBUTE10),
199 ATTRIBUTE11 = DECODE(p_quota_categories_rec.ATTRIBUTE11,
200 FND_API.G_MISS_CHAR,
201 oldrec.ATTRIBUTE11,
202 p_quota_categories_rec.ATTRIBUTE11),
203 ATTRIBUTE12 = DECODE(p_quota_categories_rec.ATTRIBUTE12,
204 FND_API.G_MISS_CHAR,
205 oldrec.ATTRIBUTE12,
206 p_quota_categories_rec.ATTRIBUTE12),
207 ATTRIBUTE13 = DECODE(p_quota_categories_rec.ATTRIBUTE13,
208 FND_API.G_MISS_CHAR,
209 oldrec.ATTRIBUTE13,
210 p_quota_categories_rec.ATTRIBUTE13),
211 ATTRIBUTE14 = DECODE(p_quota_categories_rec.ATTRIBUTE14,
212 FND_API.G_MISS_CHAR,
213 oldrec.ATTRIBUTE14,
214 p_quota_categories_rec.ATTRIBUTE14),
215 ATTRIBUTE15 = DECODE(p_quota_categories_rec.ATTRIBUTE15,
216 FND_API.G_MISS_CHAR,
217 oldrec.ATTRIBUTE15,
218 p_quota_categories_rec.ATTRIBUTE15),
219 LAST_UPDATE_DATE = G_LAST_UPDATE_DATE,
220 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
221 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN,
222 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1
223 WHERE quota_category_id = p_quota_categories_rec.quota_category_id;
224
225 IF (SQL%ROWCOUNT=0) THEN
226 RAISE NO_DATA_FOUND;
227 END IF;
228
229 END update_row;
230
231
232 -- * -------------------------------------------------------------------------*
233 -- Procedure Name
234 -- lock_update_row
235 -- Purpose
236 -- Main lcok and update procedure
237 -- Note
238 -- 1. Object version checking is performed before checking
239 -- 2. Calling update_row if you don not want object version checking
240 -- 3. All paramaters are IN parameter.
241 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
242 -- * -------------------------------------------------------------------------*
243 PROCEDURE lock_update_row
244 ( p_quota_categories_rec IN QUOTA_CATEGORIES_REC_TYPE) IS
245
246 CURSOR c IS
247 SELECT object_version_number
248 FROM CN_QUOTA_CATEGORIES
249 WHERE quota_category_id = p_quota_categories_rec.quota_category_id;
250
251 tlinfo c%ROWTYPE ;
252 BEGIN
253
254 open c;
255 fetch c into tlinfo;
256 if (c%notfound) then
257 close c;
258 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
259 fnd_msg_pub.add;
260 raise fnd_api.g_exc_unexpected_error;
261 end if;
262 close c;
263
264 if (tlinfo.object_version_number <> p_quota_categories_rec.object_version_number) then
265 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
266 fnd_msg_pub.add;
267 raise fnd_api.g_exc_unexpected_error;
268 end if;
269 UPDATE CN_QUOTA_CATEGORIES oldrec
270 SET
271 NAME = DECODE(p_quota_categories_rec.NAME,
272 FND_API.G_MISS_CHAR,
273 oldrec.NAME,
274 p_quota_categories_rec.NAME),
275 DESCRIPTION = DECODE(p_quota_categories_rec.DESCRIPTION,
276 FND_API.G_MISS_CHAR,
277 oldrec.DESCRIPTION,
278 p_quota_categories_rec.DESCRIPTION),
279 TYPE = DECODE(p_quota_categories_rec.TYPE,
280 FND_API.G_MISS_CHAR,
281 oldrec.TYPE,
282 p_quota_categories_rec.TYPE),
283 COMPUTE_FLAG = DECODE(p_quota_categories_rec.COMPUTE_FLAG,
284 FND_API.G_MISS_CHAR,
285 oldrec.COMPUTE_FLAG,
286 p_quota_categories_rec.COMPUTE_FLAG),
290 p_quota_categories_rec.INTERVAL_TYPE_ID),
287 INTERVAL_TYPE_ID = DECODE(p_quota_categories_rec.INTERVAL_TYPE_ID,
288 FND_API.G_MISS_NUM,
289 oldrec.INTERVAL_TYPE_ID,
291 QUOTA_UNIT_CODE = DECODE(p_quota_categories_rec.QUOTA_UNIT_CODE,
292 FND_API.G_MISS_CHAR,
293 oldrec.QUOTA_UNIT_CODE,
294 p_quota_categories_rec.QUOTA_UNIT_CODE),
295 ATTRIBUTE_CATEGORY = DECODE(p_quota_categories_rec.ATTRIBUTE_CATEGORY,
296 FND_API.G_MISS_CHAR,
297 oldrec.ATTRIBUTE_CATEGORY,
298 p_quota_categories_rec.ATTRIBUTE_CATEGORY),
299 ATTRIBUTE1 = DECODE(p_quota_categories_rec.ATTRIBUTE1,
300 FND_API.G_MISS_CHAR,
301 oldrec.ATTRIBUTE1,
302 p_quota_categories_rec.ATTRIBUTE1),
303 ATTRIBUTE2 = DECODE(p_quota_categories_rec.ATTRIBUTE2,
304 FND_API.G_MISS_CHAR,
305 oldrec.ATTRIBUTE2,
306 p_quota_categories_rec.ATTRIBUTE2),
307 ATTRIBUTE3 = DECODE(p_quota_categories_rec.ATTRIBUTE3,
308 FND_API.G_MISS_CHAR,
309 oldrec.ATTRIBUTE3,
310 p_quota_categories_rec.ATTRIBUTE3),
311 ATTRIBUTE4 = DECODE(p_quota_categories_rec.ATTRIBUTE4,
312 FND_API.G_MISS_CHAR,
313 oldrec.ATTRIBUTE4,
314 p_quota_categories_rec.ATTRIBUTE4),
315 ATTRIBUTE5 = DECODE(p_quota_categories_rec.ATTRIBUTE5,
316 FND_API.G_MISS_CHAR,
317 oldrec.ATTRIBUTE5,
318 p_quota_categories_rec.ATTRIBUTE5),
319 ATTRIBUTE6 = DECODE(p_quota_categories_rec.ATTRIBUTE6,
320 FND_API.G_MISS_CHAR,
321 oldrec.ATTRIBUTE6,
322 p_quota_categories_rec.ATTRIBUTE6),
323 ATTRIBUTE7 = DECODE(p_quota_categories_rec.ATTRIBUTE7,
324 FND_API.G_MISS_CHAR,
325 oldrec.ATTRIBUTE7,
326 p_quota_categories_rec.ATTRIBUTE7),
327 ATTRIBUTE8 = DECODE(p_quota_categories_rec.ATTRIBUTE8,
328 FND_API.G_MISS_CHAR,
329 oldrec.ATTRIBUTE8,
330 p_quota_categories_rec.ATTRIBUTE8),
331 ATTRIBUTE9 = DECODE(p_quota_categories_rec.ATTRIBUTE9,
332 FND_API.G_MISS_CHAR,
333 oldrec.ATTRIBUTE9,
334 p_quota_categories_rec.ATTRIBUTE9),
335 ATTRIBUTE10 = DECODE(p_quota_categories_rec.ATTRIBUTE10,
336 FND_API.G_MISS_CHAR,
337 oldrec.ATTRIBUTE10,
338 p_quota_categories_rec.ATTRIBUTE10),
339 ATTRIBUTE11 = DECODE(p_quota_categories_rec.ATTRIBUTE11,
340 FND_API.G_MISS_CHAR,
341 oldrec.ATTRIBUTE11,
342 p_quota_categories_rec.ATTRIBUTE11),
343 ATTRIBUTE12 = DECODE(p_quota_categories_rec.ATTRIBUTE12,
344 FND_API.G_MISS_CHAR,
345 oldrec.ATTRIBUTE12,
346 p_quota_categories_rec.ATTRIBUTE12),
347 ATTRIBUTE13 = DECODE(p_quota_categories_rec.ATTRIBUTE13,
348 FND_API.G_MISS_CHAR,
349 oldrec.ATTRIBUTE13,
350 p_quota_categories_rec.ATTRIBUTE13),
351 ATTRIBUTE14 = DECODE(p_quota_categories_rec.ATTRIBUTE14,
352 FND_API.G_MISS_CHAR,
353 oldrec.ATTRIBUTE14,
354 p_quota_categories_rec.ATTRIBUTE14),
355 ATTRIBUTE15 = DECODE(p_quota_categories_rec.ATTRIBUTE15,
356 FND_API.G_MISS_CHAR,
357 oldrec.ATTRIBUTE15,
358 p_quota_categories_rec.ATTRIBUTE15),
359 LAST_UPDATE_DATE = G_LAST_UPDATE_DATE,
360 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
361 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN,
362 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1
363 WHERE quota_category_id = p_quota_categories_rec.quota_category_id;
364
365 IF (SQL%ROWCOUNT=0) THEN
366 RAISE NO_DATA_FOUND;
367 END IF;
368
369 END lock_update_row;
370
371
372 -- * -------------------------------------------------------------------------*
373 -- Procedure Name
374 -- delete_row
375 -- Purpose
376 -- Main lcok and update procedure
377 -- Note
378 -- 1. All paramaters are IN parameter.
379 -- 2. Raise NO_DATA_FOUND exception if no reocrd deleted (??)
380 -- * -------------------------------------------------------------------------*
381 PROCEDURE delete_row
382 (
383 p_quota_category_id NUMBER
384 ) IS
385
386 BEGIN
387
388 DELETE FROM CN_QUOTA_CATEGORIES
389 WHERE quota_category_id = p_quota_category_id;
390
391 IF (SQL%ROWCOUNT=0) THEN
392 RAISE NO_DATA_FOUND;
393 END IF;
394
395 END Delete_row;
396
397
398 END CN_QUOTA_CATEGORIES_PKG;