[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_PAY_ELEMENTS_PKG
Source
1 PACKAGE BODY CN_QUOTA_PAY_ELEMENTS_PKG AS
2 /* $Header: cntqpeb.pls 115.2 2002/02/05 00:26:05 pkm ship $ */
3 --
4 -- Package Name
5 -- CN_QUOTA_PAY_ELEMENTS_PKG
6 -- Purpose
7 -- Table Handler for CN_QUOTA_PAY_ELEMENTS
8 --
9 -- History
10 -- 02-feb-01 Kumar Sivasankaran
11 -- ==========================================================================
12 -- |
13 -- | PRIVATE VARIABLES
14 -- |
15 -- ==========================================================================
16 g_program_type VARCHAR2(30) := NULL;
17 -- ==========================================================================
18 -- |
19 -- | PRIVATE ROUTINES
20 -- |
21 -- ==========================================================================
22
23 -- ==========================================================================
24 -- | Custom Validation
25 -- ==========================================================================
26
27 -- ==========================================================================
28 -- Procedure Name
29 -- Get_UID
30 -- Purpose
31 -- Get the Sequence Number to Create a new quota pay element
32 -- ==========================================================================
33 PROCEDURE Get_UID( X_quota_pay_element_id IN OUT NUMBER) IS
34
35 BEGIN
36
37 SELECT cn_quota_pay_elements_s.nextval
38 INTO X_quota_pay_element_id
39 FROM dual;
40
41 END Get_UID;
42
43 -- ==========================================================================
44 -- Procedure Name
45 -- Insert_row
46 -- Purpose
47 -- Main insert procedure
48 -- ==========================================================================
49 PROCEDURE insert_row
50 (x_quota_pay_element_id IN OUT NUMBER
51 ,p_quota_id IN NUMBER
52 ,p_pay_element_type_id IN NUMBER
53 ,p_status VARCHAR2 := NULL
54 ,p_start_date DATE
55 ,p_end_date DATE
56 ,p_attribute_category VARCHAR2 := NULL
57 ,p_attribute1 VARCHAR2 := NULL
58 ,p_attribute2 VARCHAR2 := NULL
59 ,p_attribute3 VARCHAR2 := NULL
60 ,p_attribute4 VARCHAR2 := NULL
61 ,p_attribute5 VARCHAR2 := NULL
62 ,p_attribute6 VARCHAR2 := NULL
63 ,p_attribute7 VARCHAR2 := NULL
64 ,p_attribute8 VARCHAR2 := NULL
65 ,p_attribute9 VARCHAR2 := NULL
66 ,p_attribute10 VARCHAR2 := NULL
67 ,p_attribute11 VARCHAR2 := NULL
68 ,p_attribute12 VARCHAR2 := NULL
69 ,p_attribute13 VARCHAR2 := NULL
70 ,p_attribute14 VARCHAR2 := NULL
71 ,p_attribute15 VARCHAR2 := NULL
72 ,p_Created_By NUMBER
73 ,p_Creation_Date DATE
74 ,p_Last_Updated_By NUMBER
75 ,p_Last_Update_Date DATE
76 ,p_Last_Update_Login NUMBER )
77 IS
78 l_dummy NUMBER;
79
80 BEGIN
81
82 Get_UID( x_quota_pay_element_id );
83
84 INSERT INTO cn_quota_pay_elements
85 (quota_pay_element_id
86 ,quota_id
87 ,pay_element_type_id
88 ,status
89 ,start_date
90 ,end_date
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 ,Created_By
108 ,Creation_Date
109 ,Last_Updated_By
110 ,Last_Update_Date
111 ,Last_Update_Login)
112 VALUES
113 (x_quota_pay_element_id
114 ,p_quota_id
115 ,p_pay_element_type_id
116 ,p_status
117 ,p_start_date
118 ,p_end_date
119 ,p_attribute_category
120 ,p_attribute1
121 ,p_attribute2
122 ,p_attribute3
123 ,p_attribute4
124 ,p_attribute5
125 ,p_attribute6
126 ,p_attribute7
127 ,p_attribute8
128 ,p_attribute9
129 ,p_attribute10
130 ,p_attribute11
131 ,p_attribute12
132 ,p_attribute13
133 ,p_attribute14
134 ,p_attribute15
135 ,p_Created_By
136 ,p_Creation_Date
137 ,p_Last_Updated_By
138 ,p_Last_Update_Date
139 ,p_Last_Update_Login
140 );
141
142 select 1 INTO l_dummy from CN_QUOTA_PAY_ELEMENTS
143 where QUOTA_PAY_ELEMENT_ID = x_quota_pay_element_id;
144
145 END Insert_row;
146
147 -- ==========================================================================
148 -- Procedure Name
149 -- Lock_row
150 -- Purpose
151 -- Lock db row after form record is changed
152 -- Notes
153 -- ==========================================================================
154 PROCEDURE lock_row
155 ( p_quota_pay_element_id IN NUMBER
156 ,p_quota_id IN NUMBER
157 ,p_pay_element_type_id IN NUMBER
158 ,p_status VARCHAR2 := NULL
159 ,p_start_date DATE
160 ,p_end_date DATE
161 ,p_attribute_category VARCHAR2 := NULL
162 ,p_attribute1 VARCHAR2 := NULL
163 ,p_attribute2 VARCHAR2 := NULL
164 ,p_attribute3 VARCHAR2 := NULL
165 ,p_attribute4 VARCHAR2 := NULL
166 ,p_attribute5 VARCHAR2 := NULL
167 ,p_attribute6 VARCHAR2 := NULL
168 ,p_attribute7 VARCHAR2 := NULL
169 ,p_attribute8 VARCHAR2 := NULL
170 ,p_attribute9 VARCHAR2 := NULL
171 ,p_attribute10 VARCHAR2 := NULL
172 ,p_attribute11 VARCHAR2 := NULL
173 ,p_attribute12 VARCHAR2 := NULL
174 ,p_attribute13 VARCHAR2 := NULL
175 ,p_attribute14 VARCHAR2 := NULL
176 ,p_attribute15 VARCHAR2 := NULL
177 ) IS
178
179 CURSOR C IS
180 SELECT *
181 FROM cn_quota_pay_elements
182 WHERE quota_pay_element_id = p_quota_pay_element_id
183 FOR UPDATE of quota_pay_element_id NOWAIT;
184
185 tlinfo C%ROWTYPE;
186
187 BEGIN
188 OPEN C;
189 FETCH C INTO tlinfo;
190
191 IF (C%NOTFOUND) then
192 CLOSE C;
193 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
194 app_exception.raise_exception;
195 END IF;
196 CLOSE C;
197
198 IF ( ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
199 OR ((tlinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
200 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
201 OR ((tlinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
202 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
203 OR ((tlinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
204 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
205 OR ((tlinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
206 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
207 OR ((tlinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
208 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
209 OR ((tlinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
210 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
211 OR ((tlinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
212 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
213 OR ((tlinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
214 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
215 OR ((tlinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
216 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
217 OR ((tlinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
218 AND ((tlinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
219 OR ((tlinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
220 AND ((tlinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
221 OR ((tlinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
222 AND ((tlinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
223 OR ((tlinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
224 AND ((tlinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
225 OR ((tlinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
226 AND ((tlinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
227 OR ((tlinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
228 AND ((tlinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
229 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND
230 (P_ATTRIBUTE_CATEGORY is null)))
231 AND ((tlinfo.STATUS = P_STATUS)
232 OR ((tlinfo.STATUS is null) AND (P_STATUS is null)))
233 AND (tlinfo.START_DATE = P_START_DATE)
234 AND ((tlinfo.END_DATE = P_END_DATE)
235 OR ((tlinfo.END_DATE is null) AND (P_END_DATE is null)))
236 AND (tlinfo.QUOTA_ID = P_QUOTA_ID)
237 AND (tlinfo.PAY_ELEMENT_TYPE_ID = P_PAY_ELEMENT_TYPE_ID)
238 )
239 THEN
240 RETURN;
241 ELSE
242 fnd_message.Set_Name('FND', 'FORM_RECORD_CHANGED');
243 app_exception.raise_exception;
244 END IF;
245
246 END Lock_row;
247
248 -- ==========================================================================
249 -- Procedure Name
250 -- Update Record
251 -- Purpose
252 -- To Update the quota Pay element
253 --
254 -- ==========================================================================
255 PROCEDURE update_row
256 (p_quota_pay_element_id NUMBER
257 ,p_quota_id NUMBER
258 ,p_pay_element_type_id NUMBER
259 ,p_status VARCHAR2
260 ,p_start_date DATE
261 ,p_end_date DATE
262 ,p_attribute_category VARCHAR2
263 ,p_attribute1 VARCHAR2
264 ,p_attribute2 VARCHAR2
265 ,p_attribute3 VARCHAR2
266 ,p_attribute4 VARCHAR2
267 ,p_attribute5 VARCHAR2
268 ,p_attribute6 VARCHAR2
269 ,p_attribute7 VARCHAR2
270 ,p_attribute8 VARCHAR2
271 ,p_attribute9 VARCHAR2
272 ,p_attribute10 VARCHAR2
273 ,p_attribute11 VARCHAR2
274 ,p_attribute12 VARCHAR2
275 ,p_attribute13 VARCHAR2
276 ,p_attribute14 VARCHAR2
277 ,p_attribute15 VARCHAR2
278 ,p_Last_Updated_By NUMBER
279 ,p_Last_Update_Date DATE
280 ,p_Last_Update_Login NUMBER ) IS
281
282 l_quota_pay_element_id cn_quota_pay_elements.quota_pay_element_id%TYPE;
283 l_quota_id cn_quota_pay_elements.quota_id%TYPE;
284 l_pay_element_type_id cn_quota_pay_elements.pay_element_type_id%TYPE;
285 l_start_date cn_quota_pay_elements.start_date%TYPE;
286 l_end_date cn_quota_pay_elements.end_date%TYPE;
287 l_status cn_quota_pay_elements.status%TYPE;
288 l_attribute_category cn_quota_pay_elements.attribute_category%TYPE;
289 l_attribute1 cn_quota_pay_elements.attribute1%TYPE;
290 l_attribute2 cn_quota_pay_elements.attribute2%TYPE;
291 l_attribute3 cn_quota_pay_elements.attribute3%TYPE;
292 l_attribute4 cn_quota_pay_elements.attribute4%TYPE;
293 l_attribute5 cn_quota_pay_elements.attribute5%TYPE;
294 l_attribute6 cn_quota_pay_elements.attribute6%TYPE;
295 l_attribute7 cn_quota_pay_elements.attribute7%TYPE;
296 l_attribute8 cn_quota_pay_elements.attribute8%TYPE;
297 l_attribute9 cn_quota_pay_elements.attribute9%TYPE;
298 l_attribute10 cn_quota_pay_elements.attribute10%TYPE;
299 l_attribute11 cn_quota_pay_elements.attribute11%TYPE;
300 l_attribute12 cn_quota_pay_elements.attribute12%TYPE;
301 l_attribute13 cn_quota_pay_elements.attribute13%TYPE;
302 l_attribute14 cn_quota_pay_elements.attribute14%TYPE;
303 l_attribute15 cn_quota_pay_elements.attribute15%TYPE;
304
305 CURSOR C IS
306 SELECT *
307 FROM cn_quota_pay_elements
308 WHERE quota_pay_element_id = p_quota_pay_element_id
309 FOR UPDATE of quota_pay_element_id NOWAIT;
310 oldrow C%ROWTYPE;
311
312 BEGIN
313 OPEN C;
314 FETCH C INTO oldrow;
315
316 IF (C%NOTFOUND) then
317 CLOSE C;
318 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
319 app_exception.raise_exception;
320 END IF;
321 CLOSE C;
322
323 SELECT
324 decode(p_quota_id,
325 fnd_api.g_miss_num, oldrow.quota_id,
326 p_quota_id),
327 decode(p_pay_element_type_id,
328 fnd_api.g_miss_num, oldrow.pay_element_type_id,
329 p_pay_element_type_id),
330 decode(p_status,
331 fnd_api.g_miss_char, oldrow.status,
332 p_status),
333 decode(p_start_date,
334 fnd_api.g_miss_date, oldrow.start_date,
335 p_start_date),
336 decode(p_end_date,
337 fnd_api.g_miss_date, oldrow.end_date,
338 p_end_date),
339 decode(p_attribute_category,
340 fnd_api.g_miss_char, oldrow.attribute_category,
341 p_attribute_category),
342 decode(p_attribute1,
343 fnd_api.g_miss_char, oldrow.attribute1,
344 p_attribute1),
345 decode(p_attribute2,
346 fnd_api.g_miss_char, oldrow.attribute2,
347 p_attribute2),
348 decode(p_attribute3,
349 fnd_api.g_miss_char, oldrow.attribute3,
350 p_attribute3),
351 decode(p_attribute4,
352 fnd_api.g_miss_char, oldrow.attribute4,
353 p_attribute4),
354 decode(p_attribute5,
355 fnd_api.g_miss_char, oldrow.attribute5,
356 p_attribute5),
357 decode(p_attribute6,
358 fnd_api.g_miss_char, oldrow.attribute6,
359 p_attribute6),
360 decode(p_attribute7,
361 fnd_api.g_miss_char, oldrow.attribute7,
362 p_attribute7),
363 decode(p_attribute8,
364 fnd_api.g_miss_char, oldrow.attribute8,
365 p_attribute8),
366 decode(p_attribute9,
367 fnd_api.g_miss_char, oldrow.attribute9,
368 p_attribute9),
369 decode(p_attribute10,
370 fnd_api.g_miss_char, oldrow.attribute10,
371 p_attribute10),
372 decode(p_attribute11,
373 fnd_api.g_miss_char, oldrow.attribute11,
374 p_attribute11),
375 decode(p_attribute12,
376 fnd_api.g_miss_char, oldrow.attribute12,
377 p_attribute12),
378 decode(p_attribute13,
379 fnd_api.g_miss_char, oldrow.attribute13,
380 p_attribute13),
381 decode(p_attribute14,
382 fnd_api.g_miss_char, oldrow.attribute14,
383 p_attribute14),
384 decode(p_attribute15,
385 fnd_api.g_miss_char, oldrow.attribute15,
386 p_attribute15)
387 INTO
388 l_quota_id,
389 l_pay_element_type_id,
390 l_status,
391 l_start_date,
392 l_end_date,
393 l_attribute_category,
394 l_attribute1,
395 l_attribute2,
396 l_attribute3,
397 l_attribute4,
398 l_attribute5,
399 l_attribute6,
400 l_attribute7,
401 l_attribute8,
402 l_attribute9,
403 l_attribute10,
404 l_attribute11,
405 l_attribute12,
406 l_attribute13,
407 l_attribute14,
408 l_attribute15
409 FROM dual;
410
411 UPDATE cn_quota_pay_elements
412 SET
413 quota_id = l_quota_id,
414 pay_element_type_id = l_pay_element_type_id,
418 attribute_category = l_attribute_category,
415 status = l_status,
416 start_date = l_start_date,
417 end_date = l_end_date,
419 attribute1 = l_attribute1,
420 attribute2 = l_attribute2,
421 attribute3 = l_attribute3,
422 attribute4 = l_attribute4,
423 attribute5 = l_attribute5,
424 attribute6 = l_attribute6,
425 attribute7 = l_attribute7,
426 attribute8 = l_attribute8,
427 attribute9 = l_attribute9,
428 attribute10 = l_attribute10,
429 attribute11 = l_attribute11,
430 attribute12 = l_attribute12,
431 attribute13 = l_attribute13,
432 attribute14 = l_attribute14,
433 attribute15 = l_attribute15,
434 last_update_date = p_Last_Update_Date,
435 last_updated_by = p_Last_Updated_By,
436 last_update_login = p_Last_Update_Login
437 WHERE quota_pay_element_id= p_quota_pay_element_id;
438
439 IF (SQL%NOTFOUND) THEN
440 RAISE NO_DATA_FOUND;
441 END IF;
442
443 END Update_row;
444
445 -- ==========================================================================
446 -- Procedure Name
447 -- Delete_row
448 -- Purpose
449 -- Delete the Quota pay element
450 -- ==========================================================================
451
452 PROCEDURE Delete_row( p_quota_pay_element_id NUMBER ) IS
453 BEGIN
454
455 DELETE FROM cn_quota_pay_elements
456 WHERE quota_pay_element_id = p_quota_pay_element_id ;
457 IF (SQL%NOTFOUND) THEN
458 RAISE NO_DATA_FOUND;
459 END IF;
460
461 END Delete_row;
462
463 END CN_QUOTA_PAY_ELEMENTS_PKG;