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