[Home] [Help]
PACKAGE BODY: APPS.CN_PAY_GROUPS_PKG
Source
4
1 PACKAGE BODY CN_PAY_GROUPS_PKG as
2 -- $Header: cnpgrptb.pls 120.3 2005/07/26 02:36:09 sjustina ship $
3
5 g_temp_status_code VARCHAR2(30) := NULL;
6 g_program_type VARCHAR2(30) := NULL;
7
8 --------------------------------------------------------------------------
9 -- Procedure Name : Get_UID
10 -- Purpose : Get the Sequence Number to Create a new Pay Group
11 --------------------------------------------------------------------------
12
13 PROCEDURE Get_UID( X_pay_group_id IN OUT NOCOPY NUMBER) IS
14
15 BEGIN
16
17 SELECT cn_pay_groups_s.nextval
18 INTO X_pay_group_id
19 FROM sys.dual;
20
21 END Get_UID;
22
23
24 -------------------------------------------------------------------------
25 -- Procedure Name : Insert_Record
26 -- Purpose : Main insert procedure
27 -------------------------------------------------------------------------
28
29 PROCEDURE Insert_Record(
30 x_Rowid IN OUT NOCOPY VARCHAR2
31 ,x_pay_group_Id IN OUT NOCOPY NUMBER
32 ,x_name VARCHAR2
33 ,x_period_set_name VARCHAR2
34 ,x_period_type VARCHAR2
35 ,x_start_date DATE
36 ,x_end_date DATE
37 ,x_pay_group_description VARCHAR2
38 ,x_period_set_id NUMBER
39 ,x_period_type_id NUMBER
40 ,x_attribute_category VARCHAR2
41 ,x_attribute1 VARCHAR2
42 ,x_attribute2 VARCHAR2
43 ,x_attribute3 VARCHAR2
44 ,x_attribute4 VARCHAR2
45 ,x_attribute5 VARCHAR2
46 ,x_attribute6 VARCHAR2
47 ,x_attribute7 VARCHAR2
48 ,x_attribute8 VARCHAR2
49 ,x_attribute9 VARCHAR2
50 ,x_attribute10 VARCHAR2
51 ,x_attribute11 VARCHAR2
52 ,x_attribute12 VARCHAR2
53 ,x_attribute13 VARCHAR2
54 ,x_attribute14 VARCHAR2
55 ,x_attribute15 VARCHAR2
56 ,x_Created_By NUMBER
57 ,x_Creation_Date DATE
58 ,x_Last_Updated_By NUMBER
59 ,x_Last_Update_Date DATE
60 ,x_Last_Update_Login NUMBER
61 ,x_object_version_number OUT NOCOPY NUMBER
62 ,x_org_id NUMBER) IS
63
64 BEGIN
65
66 IF x_pay_group_id is null
67 THEN
68 Get_UID( X_pay_group_id );
69 END IF;
70
71 INSERT INTO cn_pay_groups(
72 pay_group_id
73 ,name
74 ,period_set_name
75 ,period_type
76 ,start_date
77 ,end_date
78 ,pay_group_description
79 ,period_set_id
80 ,period_type_id
81 ,attribute_category
82 ,attribute1
83 ,attribute2
84 ,attribute3
85 ,attribute4
86 ,attribute5
87 ,attribute6
88 ,attribute7
89 ,attribute8
90 ,attribute9
91 ,attribute10
92 ,attribute11
93 ,attribute12
94 ,attribute13
95 ,attribute14
96 ,attribute15
97 ,Created_By
98 ,Creation_Date
99 ,Last_Updated_By
100 ,Last_Update_Date
101 ,Last_Update_Login
102 ,object_version_number
103 ,org_id)
104 VALUES (
105 x_pay_group_id
106 ,x_name
107 ,x_period_set_name
108 ,x_period_type
109 ,x_start_date
110 ,x_end_date
111 ,x_pay_group_description
112 ,x_period_set_id
113 ,x_period_type_id
114 ,x_attribute_category
115 ,x_attribute1
116 ,x_attribute2
117 ,x_attribute3
118 ,x_attribute4
119 ,x_attribute5
123 ,x_attribute9
120 ,x_attribute6
121 ,x_attribute7
122 ,x_attribute8
124 ,x_attribute10
125 ,x_attribute11
126 ,x_attribute12
127 ,x_attribute13
128 ,x_attribute14
129 ,x_attribute15
130 ,x_Created_By
131 ,x_Creation_Date
132 ,x_Last_Updated_By
133 ,x_Last_Update_Date
134 ,x_Last_Update_Login
135 ,1
136 ,x_org_id
137 );
138 x_object_version_number := 1;
139 END Insert_Record;
140
141 --------------------------------------------------------------------------
142 -- Procedure Name : Update Record
143 -- Purpose : To Update the Pay Groups
144 --------------------------------------------------------------------------
145
146 PROCEDURE Update_Record(
147 x_pay_group_id NUMBER
148 ,x_name VARCHAR2 := fnd_api.g_miss_char
149 ,x_period_set_name VARCHAR2 := fnd_api.g_miss_char
150 ,x_period_type VARCHAR2 := fnd_api.g_miss_char
151 ,x_start_date DATE := fnd_api.g_miss_date
152 ,x_end_date DATE := fnd_api.g_miss_date
153 ,x_pay_group_description VARCHAR2 := fnd_api.g_miss_char
154 ,x_period_set_id NUMBER := cn_api.g_miss_id
155 ,x_period_type_id NUMBER := cn_api.g_miss_id
156 ,x_attribute_category VARCHAR2 := fnd_api.g_miss_char
157 ,x_attribute1 VARCHAR2 := fnd_api.g_miss_char
158 ,x_attribute2 VARCHAR2 := fnd_api.g_miss_char
159 ,x_attribute3 VARCHAR2 := fnd_api.g_miss_char
160 ,x_attribute4 VARCHAR2 := fnd_api.g_miss_char
161 ,x_attribute5 VARCHAR2 := fnd_api.g_miss_char
162 ,x_attribute6 VARCHAR2 := fnd_api.g_miss_char
163 ,x_attribute7 VARCHAR2 := fnd_api.g_miss_char
164 ,x_attribute8 VARCHAR2 := fnd_api.g_miss_char
165 ,x_attribute9 VARCHAR2 := fnd_api.g_miss_char
166 ,x_attribute10 VARCHAR2 := fnd_api.g_miss_char
167 ,x_attribute11 VARCHAR2 := fnd_api.g_miss_char
168 ,x_attribute12 VARCHAR2 := fnd_api.g_miss_char
169 ,x_attribute13 VARCHAR2 := fnd_api.g_miss_char
170 ,x_attribute14 VARCHAR2 := fnd_api.g_miss_char
171 ,x_attribute15 VARCHAR2 := fnd_api.g_miss_char
172 ,x_Last_Updated_By NUMBER
173 ,x_Last_Update_Date DATE
174 ,x_Last_Update_Login NUMBER
175 ,x_object_version_number OUT NOCOPY NUMBER
176 ,x_org_id NUMBER ) IS
177
178 l_name cn_pay_groups.name%TYPE;
179 l_period_set_name cn_pay_groups.period_set_name%TYPE;
180 l_period_type cn_pay_groups.period_type%TYPE;
181 l_start_date cn_pay_groups.start_date%TYPE;
182 l_end_date cn_pay_groups.end_date%TYPE;
183 l_pay_group_description cn_pay_groups.pay_group_description%TYPE;
184 l_period_set_id cn_pay_groups.period_set_id%TYPE;
185 l_period_type_id cn_pay_groups.period_type_id%TYPE;
186 l_attribute_category cn_pay_groups.attribute_category%TYPE;
187 l_attribute1 cn_pay_groups.attribute1%TYPE;
188 l_attribute2 cn_pay_groups.attribute2%TYPE;
189 l_attribute3 cn_pay_groups.attribute3%TYPE;
190 l_attribute4 cn_pay_groups.attribute4%TYPE;
191 l_attribute5 cn_pay_groups.attribute5%TYPE;
192 l_attribute6 cn_pay_groups.attribute6%TYPE;
193 l_attribute7 cn_pay_groups.attribute7%TYPE;
194 l_attribute8 cn_pay_groups.attribute8%TYPE;
195 l_attribute9 cn_pay_groups.attribute9%TYPE;
196 l_attribute10 cn_pay_groups.attribute10%TYPE;
197 l_attribute11 cn_pay_groups.attribute11%TYPE;
198 l_attribute12 cn_pay_groups.attribute12%TYPE;
199 l_attribute13 cn_pay_groups.attribute13%TYPE;
200 l_attribute14 cn_pay_groups.attribute14%TYPE;
201 l_attribute15 cn_pay_groups.attribute15%TYPE;
202 l_org_id cn_pay_groups.org_id%TYPE;
203
204 CURSOR pay_group_cur IS
205 SELECT *
206 FROM cn_pay_groups
207 WHERE pay_group_id = x_pay_group_id;
208
209 l_pay_group_rec pay_group_cur%ROWTYPE;
210
211 BEGIN
212
213 OPEN pay_group_cur;
214 FETCH pay_group_cur INTO l_pay_group_rec;
215 CLOSE pay_group_cur;
216
217 SELECT decode(x_name,
218 fnd_api.g_miss_char, l_pay_group_rec.name,
219 x_name),
220 decode(x_period_set_name,
221 fnd_api.g_miss_char, l_pay_group_rec.period_set_name,
222 x_period_set_name),
223 decode(x_period_type,
224 fnd_api.g_miss_char, l_pay_group_rec.period_type,
225 x_period_type),
226 decode(x_start_date,
227 fnd_api.g_miss_date, l_pay_group_rec.start_date,
228 x_start_date),
229 decode(x_end_date,
230 fnd_api.g_miss_date, l_pay_group_rec.end_date,
231 x_end_date),
232 decode(x_pay_group_description,
233 fnd_api.g_miss_char, l_pay_group_rec.pay_group_description,
234 x_pay_group_description),
235 decode(x_period_set_id,
236 cn_api.g_miss_id, l_pay_group_rec.period_set_id,
237 x_period_set_id),
238 decode(x_period_type_id,
239 cn_api.g_miss_id, l_pay_group_rec.period_type_id,
240 x_period_type_id),
241 decode(x_attribute_category,
242 fnd_api.g_miss_char, l_pay_group_rec.attribute_category,
243 x_attribute_category),
244 decode(x_attribute1,
248 fnd_api.g_miss_char, l_pay_group_rec.attribute2,
245 fnd_api.g_miss_char, l_pay_group_rec.attribute1,
246 x_attribute1),
247 decode(x_attribute2,
249 x_attribute2),
250 decode(x_attribute3,
251 fnd_api.g_miss_char, l_pay_group_rec.attribute3,
252 x_attribute3),
253 decode(x_attribute4,
254 fnd_api.g_miss_char, l_pay_group_rec.attribute4,
255 x_attribute4),
256 decode(x_attribute5,
257 fnd_api.g_miss_char, l_pay_group_rec.attribute5,
258 x_attribute5),
259 decode(x_attribute6,
260 fnd_api.g_miss_char, l_pay_group_rec.attribute6,
261 x_attribute6),
262 decode(x_attribute7,
263 fnd_api.g_miss_char, l_pay_group_rec.attribute7,
264 x_attribute7),
265 decode(x_attribute8,
266 fnd_api.g_miss_char, l_pay_group_rec.attribute8,
267 x_attribute8),
268 decode(x_attribute9,
269 fnd_api.g_miss_char, l_pay_group_rec.attribute9,
270 x_attribute9),
271 decode(x_attribute10,
272 fnd_api.g_miss_char, l_pay_group_rec.attribute10,
273 x_attribute10),
274 decode(x_attribute11,
275 fnd_api.g_miss_char, l_pay_group_rec.attribute11,
276 x_attribute11),
277 decode(x_attribute12,
278 fnd_api.g_miss_char, l_pay_group_rec.attribute12,
279 x_attribute12),
280 decode(x_attribute13,
281 fnd_api.g_miss_char, l_pay_group_rec.attribute13,
282 x_attribute13),
283 decode(x_attribute14,
284 fnd_api.g_miss_char, l_pay_group_rec.attribute14,
285 x_attribute14),
286 decode(x_attribute15,
287 fnd_api.g_miss_char, l_pay_group_rec.attribute15,
288 x_attribute15),
289 decode(x_org_id,
290 cn_api.g_miss_id, l_pay_group_rec.org_id,
291 x_org_id)
292
293 INTO l_name,
294 l_period_set_name,
295 l_period_type,
296 l_start_date,
297 l_end_date,
298 l_pay_group_description,
299 l_period_set_id,
300 l_period_type_id,
301 l_attribute_category,
302 l_attribute1,
303 l_attribute2,
304 l_attribute3,
305 l_attribute4,
306 l_attribute5,
307 l_attribute6,
308 l_attribute7,
309 l_attribute8,
310 l_attribute9,
311 l_attribute10,
312 l_attribute11,
313 l_attribute12,
314 l_attribute13,
315 l_attribute14,
316 l_attribute15,
317 l_org_id
318 FROM dual;
319
320
321 UPDATE cn_pay_groups
322 SET
323 name = l_name,
324 period_set_name = l_period_set_name,
325 period_type = l_period_type,
326 start_date = l_start_date,
327 end_date = l_end_date,
328 pay_group_description = l_pay_group_description,
329 period_set_id = l_period_set_id,
330 period_type_id = l_period_type_id,
331 attribute_category = l_attribute_category,
332 attribute1 = l_attribute1,
333 attribute2 = l_attribute2,
334 attribute3 = l_attribute3,
335 attribute4 = l_attribute4,
336 attribute5 = l_attribute5,
337 attribute6 = l_attribute6,
338 attribute7 = l_attribute7,
339 attribute8 = l_attribute8,
340 attribute9 = l_attribute9,
341 attribute10 = l_attribute10,
342 attribute11 = l_attribute11,
343 attribute12 = l_attribute12,
344 attribute13 = l_attribute13,
345 attribute14 = l_attribute14,
346 attribute15 = l_attribute15,
347 last_update_date = x_Last_Update_Date,
348 last_updated_by = x_Last_Updated_By,
349 last_update_login = x_Last_Update_Login,
350 object_version_number = object_version_number + 1
351 WHERE pay_group_id = x_pay_group_id ;
352
353 select object_version_number into x_object_version_number
354 from cn_pay_groups where pay_group_id = x_pay_group_id;
355
356 if (SQL%NOTFOUND) then
357 Raise NO_DATA_FOUND;
358 end if;
359
360 END Update_Record;
361
362
363 -------------------------------------------------------------------------
364 -- Procedure Name : Delete_Record
365 -- Purpose : Delete the Pay Group if it has not been assigned
366 -- to a salesrep
367 -------------------------------------------------------------------------
368
369 PROCEDURE Delete_Record( x_pay_group_id NUMBER ) IS
370 BEGIN
371
372 DELETE FROM cn_pay_groups
373 WHERE pay_group_id = x_pay_group_id;
374
375
376 END Delete_Record;
377
378
379 -----------------------------------------------------------------------------
380 -- Procedure Name : BEGIN_RECORD
381 -- Purpose : This PUBLIC procedure is called at the start of the
382 -- commit cycle.
383 -----------------------------------------------------------------------------
384 PROCEDURE Begin_Record(
385 x_Operation VARCHAR2
386 ,x_Rowid IN OUT NOCOPY VARCHAR2
387 ,x_pay_group_id IN OUT NOCOPY NUMBER
388 ,x_name VARCHAR2
389 ,x_period_set_name VARCHAR2
390 ,x_period_type VARCHAR2
391 ,x_start_date DATE
395 ,x_period_type_id NUMBER
392 ,x_end_date DATE
393 ,x_pay_group_description VARCHAR2
394 ,x_period_set_id NUMBER
396 ,x_attribute_category VARCHAR2
397 ,x_attribute1 VARCHAR2
398 ,x_attribute2 VARCHAR2
399 ,x_attribute3 VARCHAR2
400 ,x_attribute4 VARCHAR2
401 ,x_attribute5 VARCHAR2
402 ,x_attribute6 VARCHAR2
403 ,x_attribute7 VARCHAR2
404 ,x_attribute8 VARCHAR2
405 ,x_attribute9 VARCHAR2
406 ,x_attribute10 VARCHAR2
407 ,x_attribute11 VARCHAR2
408 ,x_attribute12 VARCHAR2
409 ,x_attribute13 VARCHAR2
410 ,x_attribute14 VARCHAR2
411 ,x_attribute15 VARCHAR2
412 ,x_Created_By NUMBER
413 ,x_Creation_Date DATE
414 ,x_Last_Updated_By NUMBER
415 ,x_Last_Update_Date DATE
416 ,x_Last_Update_Login NUMBER
417 ,x_Program_Type VARCHAR2
418 ,x_object_version_number OUT NOCOPY NUMBER
419 ,x_org_id NUMBER) IS
420
421 BEGIN
422
423 IF X_Operation = 'INSERT' THEN
424
425 Insert_Record( X_Rowid
426 ,X_pay_group_id
427 ,X_name
428 ,X_period_set_name
429 ,x_period_type
430 ,X_start_date
431 ,X_end_date
432 ,X_pay_group_description
433 ,X_period_set_id
434 ,X_period_type_id
435 ,X_attribute_category
436 ,X_attribute1
437 ,X_attribute2
438 ,X_attribute3
439 ,X_attribute4
440 ,X_attribute5
441 ,X_attribute6
442 ,X_attribute7
443 ,X_attribute8
444 ,X_attribute9
445 ,X_attribute10
446 ,X_attribute11
447 ,X_attribute12
448 ,X_attribute13
449 ,X_attribute14
450 ,X_attribute15
451 ,X_Created_By
452 ,X_Creation_Date
453 ,X_Last_Updated_By
454 ,X_Last_Update_Date
455 ,X_Last_Update_Login
456 ,x_object_version_number
457 ,x_org_id);
458
459 ELSIF X_Operation = 'UPDATE' THEN
460
461 Update_Record( X_pay_group_id
462 ,X_name
463 ,X_period_set_name
464 ,X_period_type
465 ,X_start_date
466 ,X_end_date
467 ,X_pay_group_description
468 ,X_period_set_id
469 ,X_period_type_id
470 ,X_attribute_category
471 ,X_attribute1
472 ,X_attribute2
473 ,X_attribute3
474 ,X_attribute4
475 ,X_attribute5
476 ,X_attribute6
477 ,X_attribute7
478 ,X_attribute8
479 ,X_attribute9
480 ,X_attribute10
481 ,X_attribute11
482 ,X_attribute12
483 ,X_attribute13
484 ,X_attribute14
485 ,X_attribute15
486 ,X_Last_Updated_By
487 ,X_Last_Update_Date
488 ,X_Last_Update_Login
489 ,x_object_version_number
490 ,x_org_id);
491
492 ELSIF X_Operation = 'DELETE' THEN
493
494 Delete_Record( X_pay_group_id );
495
496 END IF;
497
498 END Begin_Record;
499
500 END CN_PAY_GROUPS_PKG;