[Home] [Help]
PACKAGE BODY: APPS.PAY_CALENDARS_PKG
Source
1 PACKAGE BODY PAY_CALENDARS_PKG as
2 /* $Header: pycal01t.pkb 120.1 2005/10/05 21:59:16 saurgupt noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 pay_calendars_pkg
11 Purpose
12 Table Handler for the block CAL in the Define Budgetary Calendar form.
13 Notes
14 Used by the PAYWSDCL (Define Budgetary Calendar) form.
15 History
16 11-Mar-94 J.S.Hobbs 40.0 Date created.
17 22-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
18 02-Feb-95 J.S.Hobbs 40.5 Removed aol WHO columns.
19 07-JAN-2000 C.Simpson 110.1 Added chk_budget_exists to prevent
20 delete where per_budget record exists
21 of Training Plan type (OTA_BUDGET).
22 24-APR-2000 S Goyal 115.1 Updated chk_budget_exists to prevent
23 delete where pqh_budget record exists
24 ============================================================================*/
25
26 -- ----------------------------------------------------------------------------
27 -- | Private Global Definitions |
28 -- ----------------------------------------------------------------------------
29 g_package varchar2(33) := ' PAY_CALENDARS_PKG.'; -- Global package name
30
31
32
33 -- ----------------------------------------------------------------------------
34 -- | chk_budget_exists |
35 -- ----------------------------------------------------------------------------
36 PROCEDURE chk_budget_exists(X_Period_Set_Name IN VARCHAR2) IS
37 --
38 -- Private procedure
39 -- Called by Delete_Row
40 --
41 CURSOR c_budgets IS
42 SELECT NULL
43 FROM per_budgets pb
44 WHERE pb.period_set_name = X_Period_Set_Name
45 AND pb.budget_type_code = 'OTA_BUDGET';
46 --
47 CURSOR c_pqh_budgets IS
48 SELECT NULL
49 FROM pqh_budgets pb
50 WHERE pb.period_set_name = X_Period_Set_Name;
51 --
52 l_result VARCHAR2(255);
53 l_proc VARCHAR2(72) := g_package||'chk_budget_exists';
54 --
55 BEGIN
56 --
57 hr_utility.set_location('Entering:'||l_proc, 5);
58 --
59 OPEN c_budgets;
60 FETCH c_budgets INTO l_result;
61 IF c_budgets%FOUND THEN
62 CLOSE c_budgets;
63 hr_utility.set_location(' ota budget exists'||l_proc, 10);
64 hr_utility.set_message(800,'PER_52887_BUD_CAL_DELETE_FAIL');
65 hr_utility.raise_error;
66 END IF;
67 CLOSE c_budgets;
68 --
69 OPEN c_pqh_budgets;
70 FETCH c_pqh_budgets INTO l_result;
71 IF c_pqh_budgets%FOUND THEN
72 CLOSE c_pqh_budgets;
73 hr_utility.set_location(' pqh budget exists'||l_proc, 20);
74 hr_utility.set_message(800,'PQH_52887_BUD_CAL_DELETE_FAIL');
75 hr_utility.raise_error;
76 END IF;
77 CLOSE c_pqh_budgets;
78 --
79 hr_utility.set_location(' Leaving:'||l_proc, 50);
80 --
81 END chk_budget_exists;
82 --
83 --
84 --
85 -----------------------------------------------------------------------------
86 -- Name --
87 -- Insert_Row --
88 -- Purpose --
89 -- Table handler procedure that supports the insert of a calendar via the--
90 -- Define Budgetary Calendar form. --
91 -- Arguments --
92 -- See below. --
93 -- Notes --
94 -- --
95 -----------------------------------------------------------------------------
96 --
97 PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
98 X_Period_Set_Name VARCHAR2,
99 X_Actual_Period_Type VARCHAR2,
100 X_Proc_Period_Type VARCHAR2,
101 X_Start_Date DATE,
102 X_Comments VARCHAR2,
103 X_Attribute_Category VARCHAR2,
104 X_Attribute1 VARCHAR2,
105 X_Attribute2 VARCHAR2,
106 X_Attribute3 VARCHAR2,
107 X_Attribute4 VARCHAR2,
108 X_Attribute5 VARCHAR2,
109 X_Attribute6 VARCHAR2,
110 X_Attribute7 VARCHAR2,
111 X_Attribute8 VARCHAR2,
112 X_Attribute9 VARCHAR2,
113 X_Attribute10 VARCHAR2,
114 X_Attribute11 VARCHAR2,
115 X_Attribute12 VARCHAR2,
116 X_Attribute13 VARCHAR2,
117 X_Attribute14 VARCHAR2,
118 X_Attribute15 VARCHAR2,
119 X_Attribute16 VARCHAR2,
120 X_Attribute17 VARCHAR2,
121 X_Attribute18 VARCHAR2,
122 X_Attribute19 VARCHAR2,
123 X_Attribute20 VARCHAR2,
124 -- Extra Columns
125 X_Midpoint_Offset NUMBER) IS
126 --
127 CURSOR C IS SELECT rowid FROM pay_calendars
128 WHERE period_set_name = X_Period_Set_Name;
129 --
130 BEGIN
131 --
132 INSERT INTO pay_calendars
133 (period_set_name,
134 actual_period_type,
135 proc_period_type,
136 start_date,
137 comments,
138 attribute_category,
139 attribute1,
140 attribute2,
141 attribute3,
142 attribute4,
143 attribute5,
144 attribute6,
145 attribute7,
146 attribute8,
147 attribute9,
148 attribute10,
149 attribute11,
150 attribute12,
151 attribute13,
152 attribute14,
153 attribute15,
154 attribute16,
155 attribute17,
156 attribute18,
157 attribute19,
158 attribute20)
159 VALUES
160 (X_Period_Set_Name,
161 X_Actual_Period_Type,
162 X_Proc_Period_Type,
163 X_Start_Date,
164 X_Comments,
165 X_Attribute_Category,
166 X_Attribute1,
167 X_Attribute2,
168 X_Attribute3,
169 X_Attribute4,
170 X_Attribute5,
171 X_Attribute6,
172 X_Attribute7,
173 X_Attribute8,
174 X_Attribute9,
175 X_Attribute10,
176 X_Attribute11,
177 X_Attribute12,
178 X_Attribute13,
179 X_Attribute14,
180 X_Attribute15,
181 X_Attribute16,
182 X_Attribute17,
183 X_Attribute18,
184 X_Attribute19,
185 X_Attribute20);
186 --
187 OPEN C;
188 FETCH C INTO X_Rowid;
189 if (C%NOTFOUND) then
190 CLOSE C;
191 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
192 hr_utility.set_message_token('PROCEDURE','pay_calendars_pkg.insert_row');
193 hr_utility.set_message_token('STEP','1');
194 hr_utility.raise_error;
195 end if;
196 CLOSE C;
197 --
198 -- Create a years worth of calendar by default.
199 hr_budget_calendar.generate
200 (x_period_set_name,
201 x_midpoint_offset,
202 1);
203 --
204 END Insert_Row;
205 --
206 -----------------------------------------------------------------------------
207 -- Name --
208 -- Lock_Row --
209 -- Purpose --
210 -- Table handler procedure that supports the insert , update and delete --
211 -- of a calendar by applying a lock on a calendar in the Define --
212 -- Budgetary Calendar form. --
213 -- Arguments --
214 -- See below. --
215 -- Notes --
216 -- None. --
217 -----------------------------------------------------------------------------
218 --
219 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
220 X_Period_Set_Name VARCHAR2,
221 X_Actual_Period_Type VARCHAR2,
222 X_Proc_Period_Type VARCHAR2,
223 X_Start_Date DATE,
224 X_Comments VARCHAR2,
225 X_Attribute_Category VARCHAR2,
226 X_Attribute1 VARCHAR2,
227 X_Attribute2 VARCHAR2,
228 X_Attribute3 VARCHAR2,
229 X_Attribute4 VARCHAR2,
230 X_Attribute5 VARCHAR2,
231 X_Attribute6 VARCHAR2,
232 X_Attribute7 VARCHAR2,
233 X_Attribute8 VARCHAR2,
234 X_Attribute9 VARCHAR2,
235 X_Attribute10 VARCHAR2,
236 X_Attribute11 VARCHAR2,
237 X_Attribute12 VARCHAR2,
238 X_Attribute13 VARCHAR2,
239 X_Attribute14 VARCHAR2,
240 X_Attribute15 VARCHAR2,
241 X_Attribute16 VARCHAR2,
242 X_Attribute17 VARCHAR2,
243 X_Attribute18 VARCHAR2,
244 X_Attribute19 VARCHAR2,
245 X_Attribute20 VARCHAR2) IS
246 --
247 CURSOR C IS SELECT * FROM pay_calendars
248 WHERE rowid = X_Rowid FOR UPDATE of Period_Set_Name NOWAIT;
249 --
250 Recinfo C%ROWTYPE;
251 --
252 BEGIN
253 --
254 OPEN C;
255 FETCH C INTO Recinfo;
256 if (C%NOTFOUND) then
257 CLOSE C;
258 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
259 hr_utility.set_message_token('PROCEDURE','pay_calendars_pkg.lock_row');
260 hr_utility.set_message_token('STEP','1');
261 hr_utility.raise_error;
262 end if;
263 CLOSE C;
264 --
265 -- Remove trailing spaces.
266 Recinfo.period_set_name := rtrim(Recinfo.period_set_name);
267 Recinfo.actual_period_type := rtrim(Recinfo.actual_period_type);
268 Recinfo.proc_period_type := rtrim(Recinfo.proc_period_type);
269 Recinfo.comments := rtrim(Recinfo.comments);
270 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
271 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
272 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
273 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
274 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
275 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
276 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
277 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
278 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
279 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
280 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
281 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
282 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
283 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
284 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
285 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
286 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
287 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
288 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
289 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
290 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
291 --
292 if ( ( (Recinfo.period_set_name = X_Period_Set_Name)
293 OR ( (Recinfo.period_set_name IS NULL)
294 AND (X_Period_Set_Name IS NULL)))
295 AND ( (Recinfo.actual_period_type = X_Actual_Period_Type)
296 OR ( (Recinfo.actual_period_type IS NULL)
297 AND (X_Actual_Period_Type IS NULL)))
298 AND ( (Recinfo.proc_period_type = X_Proc_Period_Type)
299 OR ( (Recinfo.proc_period_type IS NULL)
300 AND (X_Proc_Period_Type IS NULL)))
301 AND ( (Recinfo.start_date = X_Start_Date)
302 OR ( (Recinfo.start_date IS NULL)
303 AND (X_Start_Date IS NULL)))
304 AND ( (Recinfo.comments = X_Comments)
305 OR ( (Recinfo.comments IS NULL)
306 AND (X_Comments IS NULL)))
307 AND ( (Recinfo.attribute_category = X_Attribute_Category)
308 OR ( (Recinfo.attribute_category IS NULL)
309 AND (X_Attribute_Category IS NULL)))
310 AND ( (Recinfo.attribute1 = X_Attribute1)
311 OR ( (Recinfo.attribute1 IS NULL)
312 AND (X_Attribute1 IS NULL)))
313 AND ( (Recinfo.attribute2 = X_Attribute2)
314 OR ( (Recinfo.attribute2 IS NULL)
315 AND (X_Attribute2 IS NULL)))
316 AND ( (Recinfo.attribute3 = X_Attribute3)
317 OR ( (Recinfo.attribute3 IS NULL)
318 AND (X_Attribute3 IS NULL)))
319 AND ( (Recinfo.attribute4 = X_Attribute4)
320 OR ( (Recinfo.attribute4 IS NULL)
321 AND (X_Attribute4 IS NULL)))
322 AND ( (Recinfo.attribute5 = X_Attribute5)
323 OR ( (Recinfo.attribute5 IS NULL)
324 AND (X_Attribute5 IS NULL)))
325 AND ( (Recinfo.attribute6 = X_Attribute6)
326 OR ( (Recinfo.attribute6 IS NULL)
327 AND (X_Attribute6 IS NULL)))
328 AND ( (Recinfo.attribute7 = X_Attribute7)
329 OR ( (Recinfo.attribute7 IS NULL)
330 AND (X_Attribute7 IS NULL)))
331 AND ( (Recinfo.attribute8 = X_Attribute8)
332 OR ( (Recinfo.attribute8 IS NULL)
333 AND (X_Attribute8 IS NULL)))
334 AND ( (Recinfo.attribute9 = X_Attribute9)
335 OR ( (Recinfo.attribute9 IS NULL)
336 AND (X_Attribute9 IS NULL)))
337 AND ( (Recinfo.attribute10 = X_Attribute10)
338 OR ( (Recinfo.attribute10 IS NULL)
339 AND (X_Attribute10 IS NULL)))
340 AND ( (Recinfo.attribute11 = X_Attribute11)
341 OR ( (Recinfo.attribute11 IS NULL)
342 AND (X_Attribute11 IS NULL)))
343 AND ( (Recinfo.attribute12 = X_Attribute12)
344 OR ( (Recinfo.attribute12 IS NULL)
345 AND (X_Attribute12 IS NULL)))
346 AND ( (Recinfo.attribute13 = X_Attribute13)
347 OR ( (Recinfo.attribute13 IS NULL)
348 AND (X_Attribute13 IS NULL)))
349 AND ( (Recinfo.attribute14 = X_Attribute14)
350 OR ( (Recinfo.attribute14 IS NULL)
351 AND (X_Attribute14 IS NULL)))
355 AND ( (Recinfo.attribute16 = X_Attribute16)
352 AND ( (Recinfo.attribute15 = X_Attribute15)
353 OR ( (Recinfo.attribute15 IS NULL)
354 AND (X_Attribute15 IS NULL)))
356 OR ( (Recinfo.attribute16 IS NULL)
357 AND (X_Attribute16 IS NULL)))
358 AND ( (Recinfo.attribute17 = X_Attribute17)
359 OR ( (Recinfo.attribute17 IS NULL)
360 AND (X_Attribute17 IS NULL)))
361 AND ( (Recinfo.attribute18 = X_Attribute18)
362 OR ( (Recinfo.attribute18 IS NULL)
363 AND (X_Attribute18 IS NULL)))
364 AND ( (Recinfo.attribute19 = X_Attribute19)
365 OR ( (Recinfo.attribute19 IS NULL)
366 AND (X_Attribute19 IS NULL)))
367 AND ( (Recinfo.attribute20 = X_Attribute20)
368 OR ( (Recinfo.attribute20 IS NULL)
369 AND (X_Attribute20 IS NULL)))
370 ) then
371 return;
372 else
373 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
374 APP_EXCEPTION.RAISE_EXCEPTION;
375 end if;
376 --
377 END Lock_Row;
378 --
379 -----------------------------------------------------------------------------
380 -- Name --
381 -- Update_Row --
382 -- Purpose --
383 -- Table handler procedure that supports the update of a calendar via the--
384 -- Define Budgetary Calendar form. --
385 -- Arguments --
386 -- See below. --
387 -- Notes --
388 -- None. --
389 -----------------------------------------------------------------------------
390 --
391 PROCEDURE Update_Row(X_Rowid VARCHAR2,
392 X_Period_Set_Name VARCHAR2,
393 X_Actual_Period_Type VARCHAR2,
394 X_Proc_Period_Type VARCHAR2,
395 X_Start_Date DATE,
396 X_Comments VARCHAR2,
397 X_Attribute_Category VARCHAR2,
398 X_Attribute1 VARCHAR2,
399 X_Attribute2 VARCHAR2,
400 X_Attribute3 VARCHAR2,
401 X_Attribute4 VARCHAR2,
402 X_Attribute5 VARCHAR2,
403 X_Attribute6 VARCHAR2,
404 X_Attribute7 VARCHAR2,
405 X_Attribute8 VARCHAR2,
406 X_Attribute9 VARCHAR2,
407 X_Attribute10 VARCHAR2,
408 X_Attribute11 VARCHAR2,
409 X_Attribute12 VARCHAR2,
410 X_Attribute13 VARCHAR2,
411 X_Attribute14 VARCHAR2,
412 X_Attribute15 VARCHAR2,
413 X_Attribute16 VARCHAR2,
414 X_Attribute17 VARCHAR2,
415 X_Attribute18 VARCHAR2,
416 X_Attribute19 VARCHAR2,
417 X_Attribute20 VARCHAR2) IS
418 --
419 BEGIN
420 --
421 UPDATE pay_calendars
422 SET period_set_name = X_Period_Set_Name,
423 actual_period_type = X_Actual_Period_Type,
424 proc_period_type = X_Proc_Period_Type,
425 start_date = X_Start_Date,
426 comments = X_Comments,
427 attribute_category = X_Attribute_Category,
428 attribute1 = X_Attribute1,
429 attribute2 = X_Attribute2,
430 attribute3 = X_Attribute3,
431 attribute4 = X_Attribute4,
432 attribute5 = X_Attribute5,
433 attribute6 = X_Attribute6,
434 attribute7 = X_Attribute7,
435 attribute8 = X_Attribute8,
436 attribute9 = X_Attribute9,
437 attribute10 = X_Attribute10,
438 attribute11 = X_Attribute11,
439 attribute12 = X_Attribute12,
440 attribute13 = X_Attribute13,
441 attribute14 = X_Attribute14,
442 attribute15 = X_Attribute15,
443 attribute16 = X_Attribute16,
444 attribute17 = X_Attribute17,
445 attribute18 = X_Attribute18,
446 attribute19 = X_Attribute19,
447 attribute20 = X_Attribute20
448 WHERE rowid = X_rowid;
449 --
450 if (SQL%NOTFOUND) then
451 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
452 hr_utility.set_message_token('PROCEDURE','pay_calendars_pkg.update_row');
453 hr_utility.set_message_token('STEP','1');
454 hr_utility.raise_error;
455 end if;
456 --
457 END Update_Row;
458 --
459 -----------------------------------------------------------------------------
460 -- Name --
461 -- Delete_Row --
462 -- Purpose --
463 -- Table handler procedure that supports the delete of a calendar via the--
464 -- Define Budgetary Calendar form. --
465 -- Arguments --
466 -- See below. --
467 -- Notes --
468 -- --
469 -----------------------------------------------------------------------------
470 --
471 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
472 -- Extra Columns
473 X_Period_Set_Name VARCHAR2) IS
474 BEGIN
475 --
476 -- New check procedure added to prevent delete of calendar record
477 -- if per_budgets record of 'OTA_BUDGET' budget_type_code exists
478 chk_budget_exists(X_Period_Set_Name);
479
480 -- Remove all the time periods for the calendar NB. makes sure that the
481 -- calendar has not been used for budgetting.
482 hr_budget_calendar.remove
483 (x_period_set_name,
484 0, -- number of years
485 false); -- at least one year to be kept
486 --
487 DELETE FROM pay_calendars
488 WHERE rowid = X_Rowid;
489 --
490 if (SQL%NOTFOUND) then
491 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
492 hr_utility.set_message_token('PROCEDURE','pay_calendars_pkg.delete_row');
493 hr_utility.set_message_token('STEP','1');
494 hr_utility.raise_error;
495 end if;
496 --
497 END Delete_Row;
498 --
499 END PAY_CALENDARS_PKG;