[Home] [Help]
PACKAGE BODY: APPS.PER_TIME_PERIODS_PKG
Source
1 PACKAGE BODY PER_TIME_PERIODS_PKG as
2 /* $Header: pytpe01t.pkb 120.1 2005/10/04 23:06:40 pgongada noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 per_time_periods_pkg
11 Purpose
12 Supports the TPE block in the form PAYWSDPG (Define Payroll).
13 Notes
14
15 History
16 11-Mar-94 J.S.Hobbs 40.0 Date created.
17 16-Apr-94 J.S.Hobbs 40.1 Added rtrim calls to lock_row.
18 24-Jun-94 J.S.Hobbs 40.2 Added chk_unique_name procedure.
19 31-Jan-95 J.S.Hobbs 40.6 Removed aol WHO columns.
20 05-Mar-97 J.Alloun 40.8 Changed all occurances of system.dual
21 to sys.dual for next release requirements.
22 09-Jun-99 A.Mills 115.1 Added new Developer Descriptive Flexfield
23 columns to table handler procedures.
24 30-Apr-05 Rajeesha 115.2 Added new column payslip_view_date
25 03-Oct-05 pgongada 120.1 Cleared GSCC errors for R12.
26 ============================================================================*/
27 --
28 -----------------------------------------------------------------------------
29 -- Name --
30 -- chk_unique_name --
31 -- Purpose --
32 -- Makes sure the time period name is unique for the calendar. --
33 -- Arguments --
34 -- See below. --
35 -- Notes --
36 -- This is used by PAYWSDCL (Define Budgetary Calendar). --
37 -----------------------------------------------------------------------------
38 --
39 procedure chk_unique_name
40 (
41 p_period_set_name varchar2,
42 p_time_period_id number,
43 p_period_name varchar2
44 ) is
45 --
46 cursor csr_unique_time_period is
47 select tp.time_period_id
48 from per_time_periods tp
49 where tp.period_set_name = p_period_set_name
50 and tp.time_period_id <> p_time_period_id
51 and upper(tp.period_name) = upper(p_period_name);
52 --
53 v_dummy number;
54 --
55 begin
56 --
57 open csr_unique_time_period;
58 fetch csr_unique_time_period into v_dummy;
59 if csr_unique_time_period%found then
60 close csr_unique_time_period;
61 hr_utility.set_message(801, 'PAY_6802_CALEND_PERIOD_EXISTS');
62 hr_utility.raise_error;
63 else
64 close csr_unique_time_period;
65 end if;
66 --
67 end chk_unique_name;
68 --
69 -----------------------------------------------------------------------------
70 -- Name --
71 -- Insert_Row --
72 -- Purpose --
73 -- Table handler procedure that supports the insert of a time period via --
74 -- the Define Payroll form. --
75 -- Arguments --
76 -- See below. --
77 -- Notes --
78 -- --
79 -- Additions --
80 -- Added new column X_Payslip_view_Date by rajeesha bug 4246280 --
81 -----------------------------------------------------------------------------
82 --
83 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
84 X_Time_Period_Id IN OUT NOCOPY NUMBER,
85 X_Payroll_Id NUMBER,
86 X_End_Date DATE,
87 X_Period_Name VARCHAR2,
88 X_Period_Num NUMBER,
89 X_Period_Type VARCHAR2,
90 X_Start_Date DATE,
91 X_Cut_Off_Date DATE,
92 X_Default_Dd_Date DATE,
93 X_Description VARCHAR2,
94 X_Pay_Advice_Date DATE,
95 X_Period_Set_Name VARCHAR2,
96 X_Period_Year NUMBER,
97 X_Proc_Period_Type VARCHAR2,
98 X_Quarter_Num NUMBER,
99 X_Quickpay_Display_Number NUMBER,
100 X_Regular_Payment_Date DATE,
101 X_Run_Display_Number NUMBER,
102 X_Status VARCHAR2,
103 X_Year_Number NUMBER,
104 X_Attribute_Category VARCHAR2,
105 X_Attribute1 VARCHAR2,
106 X_Attribute2 VARCHAR2,
107 X_Attribute3 VARCHAR2,
108 X_Attribute4 VARCHAR2,
109 X_Attribute5 VARCHAR2,
110 X_Attribute6 VARCHAR2,
111 X_Attribute7 VARCHAR2,
112 X_Attribute8 VARCHAR2,
113 X_Attribute9 VARCHAR2,
114 X_Attribute10 VARCHAR2,
115 X_Attribute11 VARCHAR2,
116 X_Attribute12 VARCHAR2,
117 X_Attribute13 VARCHAR2,
118 X_Attribute14 VARCHAR2,
119 X_Attribute15 VARCHAR2,
120 X_Attribute16 VARCHAR2,
121 X_Attribute17 VARCHAR2,
122 X_Attribute18 VARCHAR2,
123 X_Attribute19 VARCHAR2,
124 X_Attribute20 VARCHAR2,
125 X_Prd_Information_Category VARCHAR2,
126 X_Prd_Information1 VARCHAR2,
127 X_Prd_Information2 VARCHAR2,
128 X_Prd_Information3 VARCHAR2,
129 X_Prd_Information4 VARCHAR2,
130 X_Prd_Information5 VARCHAR2,
131 X_Prd_Information6 VARCHAR2,
132 X_Prd_Information7 VARCHAR2,
133 X_Prd_Information8 VARCHAR2,
134 X_Prd_Information9 VARCHAR2,
135 X_Prd_Information10 VARCHAR2,
136 X_Prd_Information11 VARCHAR2,
137 X_Prd_Information12 VARCHAR2,
138 X_Prd_Information13 VARCHAR2,
139 X_Prd_Information14 VARCHAR2,
140 X_Prd_Information15 VARCHAR2,
141 X_Prd_Information16 VARCHAR2,
142 X_Prd_Information17 VARCHAR2,
143 X_Prd_Information18 VARCHAR2,
144 X_Prd_Information19 VARCHAR2,
145 X_Prd_Information20 VARCHAR2,
146 X_Prd_Information21 VARCHAR2,
147 X_Prd_Information22 VARCHAR2,
148 X_Prd_Information23 VARCHAR2,
149 X_Prd_Information24 VARCHAR2,
150 X_Prd_Information25 VARCHAR2,
151 X_Prd_Information26 VARCHAR2,
152 X_Prd_Information27 VARCHAR2,
153 X_Prd_Information28 VARCHAR2,
154 X_Prd_Information29 VARCHAR2,
155 X_Prd_Information30 VARCHAR2,
156 X_Payslip_view_date DATE default null
157 ) IS
158 --
159 CURSOR C IS SELECT rowid FROM per_time_periods
160 WHERE time_period_id = X_Time_Period_Id;
161 --
162 CURSOR C2 IS SELECT per_time_periods_s.nextval FROM sys.dual;
163 --
164 BEGIN
165 --
166 if (X_Time_Period_Id is NULL) then
167 OPEN C2;
168 FETCH C2 INTO X_Time_Period_Id;
169 CLOSE C2;
170 end if;
171 --
172 INSERT INTO per_time_periods
173 (time_period_id,
174 payroll_id,
175 end_date,
176 period_name,
177 period_num,
178 period_type,
179 start_date,
180 cut_off_date,
181 default_dd_date,
182 description,
183 pay_advice_date,
184 period_set_name,
185 period_year,
186 proc_period_type,
187 quarter_num,
188 quickpay_display_number,
189 regular_payment_date,
190 run_display_number,
191 status,
192 year_number,
193 attribute_category,
194 attribute1,
195 attribute2,
196 attribute3,
197 attribute4,
198 attribute5,
199 attribute6,
200 attribute7,
201 attribute8,
202 attribute9,
203 attribute10,
204 attribute11,
205 attribute12,
206 attribute13,
207 attribute14,
208 attribute15,
209 attribute16,
210 attribute17,
211 attribute18,
212 attribute19,
213 attribute20,
214 prd_information_category,
215 prd_information1,
216 prd_information2,
217 prd_information3,
218 prd_information4,
219 prd_information5,
220 prd_information6,
221 prd_information7,
222 prd_information8,
223 prd_information9,
224 prd_information10,
225 prd_information11,
226 prd_information12,
227 prd_information13,
228 prd_information14,
229 prd_information15,
230 prd_information16,
231 prd_information17,
232 prd_information18,
233 prd_information19,
234 prd_information20,
235 prd_information21,
236 prd_information22,
237 prd_information23,
238 prd_information24,
239 prd_information25,
240 prd_information26,
241 prd_information27,
242 prd_information28,
243 prd_information29,
244 prd_information30,
245 payslip_view_date)
246 VALUES
247 (X_Time_Period_Id,
248 X_Payroll_Id,
249 X_End_Date,
250 X_Period_Name,
251 X_Period_Num,
252 X_Period_Type,
253 X_Start_Date,
254 X_Cut_Off_Date,
255 X_Default_Dd_Date,
256 X_Description,
257 X_Pay_Advice_Date,
258 X_Period_Set_Name,
259 X_Period_Year,
260 X_Proc_Period_Type,
261 X_Quarter_Num,
262 X_Quickpay_Display_Number,
263 X_Regular_Payment_Date,
264 X_Run_Display_Number,
265 X_Status,
266 X_Year_Number,
267 X_Attribute_Category,
268 X_Attribute1,
269 X_Attribute2,
270 X_Attribute3,
271 X_Attribute4,
272 X_Attribute5,
273 X_Attribute6,
274 X_Attribute7,
275 X_Attribute8,
276 X_Attribute9,
277 X_Attribute10,
278 X_Attribute11,
279 X_Attribute12,
280 X_Attribute13,
281 X_Attribute14,
282 X_Attribute15,
283 X_Attribute16,
284 X_Attribute17,
285 X_Attribute18,
286 X_Attribute19,
287 X_Attribute20,
288 X_Prd_Information_Category,
289 X_Prd_Information1,
290 X_Prd_Information2,
291 X_Prd_Information3,
292 X_Prd_Information4,
293 X_Prd_Information5,
294 X_Prd_Information6,
295 X_Prd_Information7,
296 X_Prd_Information8,
297 X_Prd_Information9,
298 X_Prd_Information10,
299 X_Prd_Information11,
300 X_Prd_Information12,
301 X_Prd_Information13,
302 X_Prd_Information14,
303 X_Prd_Information15,
304 X_Prd_Information16,
305 X_Prd_Information17,
306 X_Prd_Information18,
307 X_Prd_Information19,
308 X_Prd_Information20,
309 X_Prd_Information21,
310 X_Prd_Information22,
311 X_Prd_Information23,
312 X_Prd_Information24,
313 X_Prd_Information25,
314 X_Prd_Information26,
315 X_Prd_Information27,
316 X_Prd_Information28,
317 X_Prd_Information29,
318 X_Prd_Information30,
319 X_payslip_view_date
320 );
321 --
322 OPEN C;
323 FETCH C INTO X_Rowid;
324 if (C%NOTFOUND) then
325 CLOSE C;
326 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
327 hr_utility.set_message_token('PROCEDURE',
328 'per_time_periods_pkg.insert_row');
329 hr_utility.set_message_token('STEP','1');
330 hr_utility.raise_error;
331 end if;
332 CLOSE C;
333 --
334 END Insert_Row;
335 --
336 -----------------------------------------------------------------------------
337 -- Name --
338 -- Lock_Row --
339 -- Purpose --
340 -- Table handler procedure that supports the insert , update and delete --
341 -- of a time period by applying a lock on a time period in the Define --
342 -- Payroll form. --
343 -- Arguments --
344 -- See below. --
345 -- Notes --
346 -- None. --
347 -- Additions --
348 -- Added new column X_Payslip_view_date by rajeesha bug 4246280 --
349 -----------------------------------------------------------------------------
350 --
351 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
352 X_Time_Period_Id NUMBER,
353 X_Payroll_Id NUMBER,
354 X_End_Date DATE,
355 X_Period_Name VARCHAR2,
356 X_Period_Num NUMBER,
357 X_Period_Type VARCHAR2,
358 X_Start_Date DATE,
359 X_Cut_Off_Date DATE,
360 X_Default_Dd_Date DATE,
361 X_Description VARCHAR2,
362 X_Pay_Advice_Date DATE,
363 X_Period_Set_Name VARCHAR2,
364 X_Period_Year NUMBER,
365 X_Proc_Period_Type VARCHAR2,
366 X_Quarter_Num NUMBER,
367 X_Quickpay_Display_Number NUMBER,
368 X_Regular_Payment_Date DATE,
369 X_Run_Display_Number NUMBER,
370 X_Status VARCHAR2,
371 X_Year_Number NUMBER,
372 X_Attribute_Category VARCHAR2,
373 X_Attribute1 VARCHAR2,
374 X_Attribute2 VARCHAR2,
375 X_Attribute3 VARCHAR2,
376 X_Attribute4 VARCHAR2,
377 X_Attribute5 VARCHAR2,
378 X_Attribute6 VARCHAR2,
379 X_Attribute7 VARCHAR2,
380 X_Attribute8 VARCHAR2,
381 X_Attribute9 VARCHAR2,
382 X_Attribute10 VARCHAR2,
383 X_Attribute11 VARCHAR2,
384 X_Attribute12 VARCHAR2,
385 X_Attribute13 VARCHAR2,
386 X_Attribute14 VARCHAR2,
387 X_Attribute15 VARCHAR2,
388 X_Attribute16 VARCHAR2,
389 X_Attribute17 VARCHAR2,
390 X_Attribute18 VARCHAR2,
391 X_Attribute19 VARCHAR2,
392 X_Attribute20 VARCHAR2,
393 X_Prd_Information_Category VARCHAR2,
394 X_Prd_Information1 VARCHAR2,
395 X_Prd_Information2 VARCHAR2,
396 X_Prd_Information3 VARCHAR2,
397 X_Prd_Information4 VARCHAR2,
398 X_Prd_Information5 VARCHAR2,
399 X_Prd_Information6 VARCHAR2,
400 X_Prd_Information7 VARCHAR2,
401 X_Prd_Information8 VARCHAR2,
402 X_Prd_Information9 VARCHAR2,
403 X_Prd_Information10 VARCHAR2,
404 X_Prd_Information11 VARCHAR2,
405 X_Prd_Information12 VARCHAR2,
406 X_Prd_Information13 VARCHAR2,
407 X_Prd_Information14 VARCHAR2,
408 X_Prd_Information15 VARCHAR2,
409 X_Prd_Information16 VARCHAR2,
410 X_Prd_Information17 VARCHAR2,
411 X_Prd_Information18 VARCHAR2,
412 X_Prd_Information19 VARCHAR2,
413 X_Prd_Information20 VARCHAR2,
414 X_Prd_Information21 VARCHAR2,
415 X_Prd_Information22 VARCHAR2,
416 X_Prd_Information23 VARCHAR2,
417 X_Prd_Information24 VARCHAR2,
418 X_Prd_Information25 VARCHAR2,
419 X_Prd_Information26 VARCHAR2,
420 X_Prd_Information27 VARCHAR2,
421 X_Prd_Information28 VARCHAR2,
422 X_Prd_Information29 VARCHAR2,
423 X_Prd_Information30 VARCHAR2,
424 X_payslip_view_date DATE default null
425 ) IS
426 --
427 CURSOR C IS SELECT * FROM per_time_periods
428 WHERE rowid = X_Rowid FOR UPDATE of Time_Period_Id NOWAIT;
429 --
430 Recinfo C%ROWTYPE;
431 --
432 BEGIN
433 --
434 OPEN C;
435 FETCH C INTO Recinfo;
436 if (C%NOTFOUND) then
437 CLOSE C;
438 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
439 hr_utility.set_message_token('PROCEDURE',
440 'per_time_periods_pkg.lock_row');
441 hr_utility.set_message_token('STEP','1');
442 hr_utility.raise_error;
443 end if;
444 CLOSE C;
445 --
446 -- Remove trailing spaces.
447 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
448 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
449 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
450 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
451 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
452 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
453 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
454 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
455 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
456 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
457 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
458 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
459 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
460 Recinfo.period_name := rtrim(Recinfo.period_name);
461 Recinfo.period_type := rtrim(Recinfo.period_type);
462 Recinfo.description := rtrim(Recinfo.description);
463 Recinfo.period_set_name := rtrim(Recinfo.period_set_name);
464 Recinfo.proc_period_type := rtrim(Recinfo.proc_period_type);
465 Recinfo.status := rtrim(Recinfo.status);
466 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
467 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
468 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
469 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
470 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
471 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
472 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
473 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
474 Recinfo.prd_information_category := rtrim(Recinfo.prd_information_category);
475 Recinfo.prd_information1 := rtrim(Recinfo.prd_information1);
476 Recinfo.prd_information2 := rtrim(Recinfo.prd_information2);
477 Recinfo.prd_information3 := rtrim(Recinfo.prd_information3);
478 Recinfo.prd_information4 := rtrim(Recinfo.prd_information4);
479 Recinfo.prd_information5 := rtrim(Recinfo.prd_information5);
480 Recinfo.prd_information6 := rtrim(Recinfo.prd_information6);
481 Recinfo.prd_information7 := rtrim(Recinfo.prd_information7);
482 Recinfo.prd_information8 := rtrim(Recinfo.prd_information8);
483 Recinfo.prd_information9 := rtrim(Recinfo.prd_information9);
484 Recinfo.prd_information10 := rtrim(Recinfo.prd_information10);
485 Recinfo.prd_information11 := rtrim(Recinfo.prd_information11);
486 Recinfo.prd_information12 := rtrim(Recinfo.prd_information12);
487 Recinfo.prd_information13 := rtrim(Recinfo.prd_information13);
488 Recinfo.prd_information14 := rtrim(Recinfo.prd_information14);
489 Recinfo.prd_information15 := rtrim(Recinfo.prd_information15);
490 Recinfo.prd_information16 := rtrim(Recinfo.prd_information16);
491 Recinfo.prd_information17 := rtrim(Recinfo.prd_information17);
492 Recinfo.prd_information18 := rtrim(Recinfo.prd_information18);
493 Recinfo.prd_information19 := rtrim(Recinfo.prd_information19);
494 Recinfo.prd_information20 := rtrim(Recinfo.prd_information20);
495 Recinfo.prd_information21 := rtrim(Recinfo.prd_information21);
496 Recinfo.prd_information22 := rtrim(Recinfo.prd_information22);
497 Recinfo.prd_information23 := rtrim(Recinfo.prd_information23);
498 Recinfo.prd_information24 := rtrim(Recinfo.prd_information24);
499 Recinfo.prd_information25 := rtrim(Recinfo.prd_information25);
500 Recinfo.prd_information26 := rtrim(Recinfo.prd_information26);
501 Recinfo.prd_information27 := rtrim(Recinfo.prd_information27);
502 Recinfo.prd_information28 := rtrim(Recinfo.prd_information28);
503 Recinfo.prd_information29 := rtrim(Recinfo.prd_information29);
504 Recinfo.prd_information30 := rtrim(Recinfo.prd_information30);
505 --
506 if ( ( (Recinfo.time_period_id = X_Time_Period_Id)
507 OR ( (Recinfo.time_period_id IS NULL)
508 AND (X_Time_Period_Id IS NULL)))
509 AND ( (Recinfo.payroll_id = X_Payroll_Id)
510 OR ( (Recinfo.payroll_id IS NULL)
511 AND (X_Payroll_Id IS NULL)))
512 AND ( (Recinfo.end_date = X_End_Date)
513 OR ( (Recinfo.end_date IS NULL)
514 AND (X_End_Date IS NULL)))
515 AND ( (Recinfo.period_name = X_Period_Name)
516 OR ( (Recinfo.period_name IS NULL)
517 AND (X_Period_Name IS NULL)))
518 AND ( (Recinfo.period_num = X_Period_Num)
519 OR ( (Recinfo.period_num IS NULL)
520 AND (X_Period_Num IS NULL)))
521 AND ( (Recinfo.period_type = X_Period_Type)
522 OR ( (Recinfo.period_type IS NULL)
523 AND (X_Period_Type IS NULL)))
524 AND ( (Recinfo.start_date = X_Start_Date)
525 OR ( (Recinfo.start_date IS NULL)
526 AND (X_Start_Date IS NULL)))
527 AND ( (Recinfo.cut_off_date = X_Cut_Off_Date)
528 OR ( (Recinfo.cut_off_date IS NULL)
529 AND (X_Cut_Off_Date IS NULL)))
530 AND ( (Recinfo.default_dd_date = X_Default_Dd_Date)
531 OR ( (Recinfo.default_dd_date IS NULL)
532 AND (X_Default_Dd_Date IS NULL)))
533 AND ( (Recinfo.description = X_Description)
534 OR ( (Recinfo.description IS NULL)
535 AND (X_Description IS NULL)))
536 AND ( (Recinfo.pay_advice_date = X_Pay_Advice_Date)
537 OR ( (Recinfo.pay_advice_date IS NULL)
538 AND (X_Pay_Advice_Date IS NULL)))
539 AND ( (Recinfo.period_set_name = X_Period_Set_Name)
540 OR ( (Recinfo.period_set_name IS NULL)
541 AND (X_Period_Set_Name IS NULL)))
542 AND ( (Recinfo.period_year = X_Period_Year)
543 OR ( (Recinfo.period_year IS NULL)
544 AND (X_Period_Year IS NULL)))
545 AND ( (Recinfo.proc_period_type = X_Proc_Period_Type)
546 OR ( (Recinfo.proc_period_type IS NULL)
547 AND (X_Proc_Period_Type IS NULL)))
548 AND ( (Recinfo.quarter_num = X_Quarter_Num)
549 OR ( (Recinfo.quarter_num IS NULL)
550 AND (X_Quarter_Num IS NULL)))
551 AND ( (Recinfo.quickpay_display_number = X_Quickpay_Display_Number)
552 OR ( (Recinfo.quickpay_display_number IS NULL)
553 AND (X_Quickpay_Display_Number IS NULL)))
554 AND ( (Recinfo.regular_payment_date = X_Regular_Payment_Date)
555 OR ( (Recinfo.regular_payment_date IS NULL)
556 AND (X_Regular_Payment_Date IS NULL)))
557 AND ( (Recinfo.run_display_number = X_Run_Display_Number)
558 OR ( (Recinfo.run_display_number IS NULL)
559 AND (X_Run_Display_Number IS NULL)))
560 AND ( (Recinfo.status = X_Status)
561 OR ( (Recinfo.status IS NULL)
562 AND (X_Status IS NULL)))
563 AND ( (Recinfo.year_number = X_Year_Number)
564 OR ( (Recinfo.year_number IS NULL)
565 AND (X_Year_Number IS NULL)))
566 AND ( (Recinfo.attribute_category = X_Attribute_Category)
567 OR ( (Recinfo.attribute_category IS NULL)
568 AND (X_Attribute_Category IS NULL)))
569 AND ( (Recinfo.attribute1 = X_Attribute1)
570 OR ( (Recinfo.attribute1 IS NULL)
571 AND (X_Attribute1 IS NULL)))
572 AND ( (Recinfo.attribute2 = X_Attribute2)
573 OR ( (Recinfo.attribute2 IS NULL)
574 AND (X_Attribute2 IS NULL)))
575 AND ( (Recinfo.attribute3 = X_Attribute3)
576 OR ( (Recinfo.attribute3 IS NULL)
577 AND (X_Attribute3 IS NULL)))
578 AND ( (Recinfo.attribute4 = X_Attribute4)
579 OR ( (Recinfo.attribute4 IS NULL)
580 AND (X_Attribute4 IS NULL)))
581 AND ( (Recinfo.attribute5 = X_Attribute5)
582 OR ( (Recinfo.attribute5 IS NULL)
583 AND (X_Attribute5 IS NULL)))
584 AND ( (Recinfo.attribute6 = X_Attribute6)
585 OR ( (Recinfo.attribute6 IS NULL)
586 AND (X_Attribute6 IS NULL)))
587 AND ( (Recinfo.attribute7 = X_Attribute7)
588 OR ( (Recinfo.attribute7 IS NULL)
589 AND (X_Attribute7 IS NULL)))
590 AND ( (Recinfo.attribute8 = X_Attribute8)
591 OR ( (Recinfo.attribute8 IS NULL)
592 AND (X_Attribute8 IS NULL)))
593 AND ( (Recinfo.attribute9 = X_Attribute9)
594 OR ( (Recinfo.attribute9 IS NULL)
595 AND (X_Attribute9 IS NULL)))
596 AND ( (Recinfo.attribute10 = X_Attribute10)
597 OR ( (Recinfo.attribute10 IS NULL)
598 AND (X_Attribute10 IS NULL)))
599 AND ( (Recinfo.attribute11 = X_Attribute11)
600 OR ( (Recinfo.attribute11 IS NULL)
601 AND (X_Attribute11 IS NULL)))
602 AND ( (Recinfo.attribute12 = X_Attribute12)
603 OR ( (Recinfo.attribute12 IS NULL)
604 AND (X_Attribute12 IS NULL)))
605 AND ( (Recinfo.attribute13 = X_Attribute13)
606 OR ( (Recinfo.attribute13 IS NULL)
607 AND (X_Attribute13 IS NULL)))
608 AND ( (Recinfo.attribute14 = X_Attribute14)
609 OR ( (Recinfo.attribute14 IS NULL)
610 AND (X_Attribute14 IS NULL)))
611 AND ( (Recinfo.attribute15 = X_Attribute15)
612 OR ( (Recinfo.attribute15 IS NULL)
613 AND (X_Attribute15 IS NULL)))
614 AND ( (Recinfo.attribute16 = X_Attribute16)
615 OR ( (Recinfo.attribute16 IS NULL)
616 AND (X_Attribute16 IS NULL)))
617 AND ( (Recinfo.attribute17 = X_Attribute17)
618 OR ( (Recinfo.attribute17 IS NULL)
619 AND (X_Attribute17 IS NULL)))
620 AND ( (Recinfo.attribute18 = X_Attribute18)
621 OR ( (Recinfo.attribute18 IS NULL)
622 AND (X_Attribute18 IS NULL)))
623 AND ( (Recinfo.attribute19 = X_Attribute19)
624 OR ( (Recinfo.attribute19 IS NULL)
625 AND (X_Attribute19 IS NULL)))
626 AND ( (Recinfo.attribute20 = X_Attribute20)
627 OR ( (Recinfo.attribute20 IS NULL)
628 AND (X_Attribute20 IS NULL)))
629 AND ( (Recinfo.prd_information_category = X_Prd_Information_Category)
630 OR ( (Recinfo.prd_information_category IS NULL)
631 AND (X_Prd_Information_Category IS NULL)))
632 AND ( (Recinfo.prd_information1 = X_Prd_Information1)
633 OR ( (Recinfo.prd_information1 IS NULL)
634 AND (X_Prd_Information1 IS NULL)))
635 AND ( (Recinfo.prd_information2 = X_Prd_Information2)
636 OR ( (Recinfo.prd_information2 IS NULL)
637 AND (X_Prd_Information2 IS NULL)))
638 AND ( (Recinfo.prd_information3 = X_Prd_Information3)
639 OR ( (Recinfo.prd_information3 IS NULL)
640 AND (X_Prd_Information3 IS NULL)))
641 AND ( (Recinfo.prd_information4 = X_Prd_Information4)
642 OR ( (Recinfo.prd_information4 IS NULL)
643 AND (X_Prd_Information4 IS NULL)))
644 AND ( (Recinfo.prd_information5 = X_Prd_Information5)
645 OR ( (Recinfo.prd_information5 IS NULL)
646 AND (X_Prd_Information5 IS NULL)))
647 AND ( (Recinfo.prd_information6 = X_Prd_Information6)
648 OR ( (Recinfo.prd_information6 IS NULL)
649 AND (X_Prd_Information6 IS NULL)))
650 AND ( (Recinfo.prd_information7 = X_Prd_Information7)
651 OR ( (Recinfo.prd_information7 IS NULL)
652 AND (X_Prd_Information7 IS NULL)))
653 AND ( (Recinfo.prd_information8 = X_Prd_Information8)
654 OR ( (Recinfo.prd_information8 IS NULL)
655 AND (X_Prd_Information8 IS NULL)))
656 AND ( (Recinfo.prd_information9 = X_Prd_Information9)
657 OR ( (Recinfo.prd_information9 IS NULL)
658 AND (X_Prd_Information9 IS NULL)))
659 AND ( (Recinfo.prd_information10 = X_Prd_Information10)
660 OR ( (Recinfo.prd_information10 IS NULL)
661 AND (X_Prd_Information10 IS NULL)))
662 AND ( (Recinfo.prd_information11 = X_Prd_Information11)
663 OR ( (Recinfo.prd_information11 IS NULL)
664 AND (X_Prd_Information11 IS NULL)))
665 AND ( (Recinfo.prd_information12 = X_Prd_Information12)
666 OR ( (Recinfo.prd_information12 IS NULL)
667 AND (X_Prd_Information12 IS NULL)))
668 AND ( (Recinfo.prd_information13 = X_Prd_Information13)
669 OR ( (Recinfo.prd_information13 IS NULL)
670 AND (X_Prd_Information13 IS NULL)))
671 AND ( (Recinfo.prd_information14 = X_Prd_Information14)
672 OR ( (Recinfo.prd_information14 IS NULL)
673 AND (X_Prd_Information14 IS NULL)))
674 AND ( (Recinfo.prd_information15 = X_Prd_Information15)
675 OR ( (Recinfo.prd_information15 IS NULL)
676 AND (X_Prd_Information15 IS NULL)))
677 AND ( (Recinfo.prd_information16 = X_Prd_Information16)
678 OR ( (Recinfo.prd_information16 IS NULL)
679 AND (X_Prd_Information16 IS NULL)))
680 AND ( (Recinfo.prd_information17 = X_Prd_Information17)
681 OR ( (Recinfo.prd_information17 IS NULL)
682 AND (X_Prd_Information17 IS NULL)))
683 AND ( (Recinfo.prd_information18 = X_Prd_Information18)
684 OR ( (Recinfo.prd_information18 IS NULL)
685 AND (X_Prd_Information18 IS NULL)))
686 AND ( (Recinfo.prd_information19 = X_Prd_Information19)
687 OR ( (Recinfo.prd_information19 IS NULL)
688 AND (X_Prd_Information19 IS NULL)))
689 AND ( (Recinfo.prd_information20 = X_Prd_Information20)
690 OR ( (Recinfo.prd_information20 IS NULL)
691 AND (X_Prd_Information20 IS NULL)))
692 AND ( (Recinfo.prd_information21 = X_Prd_Information21)
693 OR ( (Recinfo.prd_information21 IS NULL)
694 AND (X_Prd_Information21 IS NULL)))
695 AND ( (Recinfo.prd_information22 = X_Prd_Information22)
696 OR ( (Recinfo.prd_information22 IS NULL)
697 AND (X_Prd_Information22 IS NULL)))
698 AND ( (Recinfo.prd_information23 = X_Prd_Information23)
699 OR ( (Recinfo.prd_information23 IS NULL)
700 AND (X_Prd_Information23 IS NULL)))
701 AND ( (Recinfo.prd_information24 = X_Prd_Information24)
702 OR ( (Recinfo.prd_information24 IS NULL)
703 AND (X_Prd_Information24 IS NULL)))
704 AND ( (Recinfo.prd_information25 = X_Prd_Information25)
705 OR ( (Recinfo.prd_information25 IS NULL)
706 AND (X_Prd_Information25 IS NULL)))
707 AND ( (Recinfo.prd_information26 = X_Prd_Information26)
708 OR ( (Recinfo.prd_information26 IS NULL)
709 AND (X_Prd_Information26 IS NULL)))
710 AND ( (Recinfo.prd_information27 = X_Prd_Information27)
711 OR ( (Recinfo.prd_information27 IS NULL)
712 AND (X_Prd_Information27 IS NULL)))
713 AND ( (Recinfo.prd_information28 = X_Prd_Information28)
714 OR ( (Recinfo.prd_information28 IS NULL)
715 AND (X_Prd_Information28 IS NULL)))
716 AND ( (Recinfo.prd_information29 = X_Prd_Information29)
717 OR ( (Recinfo.prd_information29 IS NULL)
718 AND (X_Prd_Information29 IS NULL)))
719 AND ( (Recinfo.prd_information30 = X_Prd_Information30)
720 OR ( (Recinfo.prd_information30 IS NULL)
721 AND (X_Prd_Information30 IS NULL)))
722 AND ( (Recinfo.Payslip_view_date = X_Payslip_view_Date)
723 OR ( (Recinfo.Payslip_view_date IS NULL)
724 AND (X_Payslip_view_Date IS NULL)))
725 ) then
726 return;
727 else
728 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
729 APP_EXCEPTION.RAISE_EXCEPTION;
730 end if;
731 --
732 END Lock_Row;
733 --
734 -----------------------------------------------------------------------------
735 -- Name --
736 -- Update_Row --
737 -- Purpose --
738 -- Table handler procedure that supports the update of a time period via --
739 -- the Define Payroll form. --
740 -- Arguments --
741 -- See below. --
742 -- Notes --
743 -- None. --
744 -- Additions --
745 -- Added new column X_Payslip_view_date by rajeesha bug 4246280 --
746 -----------------------------------------------------------------------------
747 --
748 PROCEDURE Update_Row(X_Rowid VARCHAR2,
749 X_Time_Period_Id NUMBER,
750 X_Payroll_Id NUMBER,
751 X_End_Date DATE,
752 X_Period_Name VARCHAR2,
753 X_Period_Num NUMBER,
754 X_Period_Type VARCHAR2,
755 X_Start_Date DATE,
756 X_Cut_Off_Date DATE,
757 X_Default_Dd_Date DATE,
758 X_Description VARCHAR2,
759 X_Pay_Advice_Date DATE,
760 X_Period_Set_Name VARCHAR2,
761 X_Period_Year NUMBER,
762 X_Proc_Period_Type VARCHAR2,
763 X_Quarter_Num NUMBER,
764 X_Quickpay_Display_Number NUMBER,
765 X_Regular_Payment_Date DATE,
766 X_Run_Display_Number NUMBER,
767 X_Status VARCHAR2,
768 X_Year_Number NUMBER,
769 X_Attribute_Category VARCHAR2,
770 X_Attribute1 VARCHAR2,
771 X_Attribute2 VARCHAR2,
772 X_Attribute3 VARCHAR2,
773 X_Attribute4 VARCHAR2,
774 X_Attribute5 VARCHAR2,
775 X_Attribute6 VARCHAR2,
776 X_Attribute7 VARCHAR2,
777 X_Attribute8 VARCHAR2,
778 X_Attribute9 VARCHAR2,
779 X_Attribute10 VARCHAR2,
780 X_Attribute11 VARCHAR2,
781 X_Attribute12 VARCHAR2,
782 X_Attribute13 VARCHAR2,
783 X_Attribute14 VARCHAR2,
784 X_Attribute15 VARCHAR2,
785 X_Attribute16 VARCHAR2,
786 X_Attribute17 VARCHAR2,
787 X_Attribute18 VARCHAR2,
788 X_Attribute19 VARCHAR2,
789 X_Attribute20 VARCHAR2,
790 X_Prd_Information_Category VARCHAR2,
791 X_Prd_Information1 VARCHAR2,
792 X_Prd_Information2 VARCHAR2,
793 X_Prd_Information3 VARCHAR2,
794 X_Prd_Information4 VARCHAR2,
795 X_Prd_Information5 VARCHAR2,
796 X_Prd_Information6 VARCHAR2,
797 X_Prd_Information7 VARCHAR2,
798 X_Prd_Information8 VARCHAR2,
799 X_Prd_Information9 VARCHAR2,
800 X_Prd_Information10 VARCHAR2,
801 X_Prd_Information11 VARCHAR2,
802 X_Prd_Information12 VARCHAR2,
803 X_Prd_Information13 VARCHAR2,
804 X_Prd_Information14 VARCHAR2,
805 X_Prd_Information15 VARCHAR2,
806 X_Prd_Information16 VARCHAR2,
807 X_Prd_Information17 VARCHAR2,
808 X_Prd_Information18 VARCHAR2,
809 X_Prd_Information19 VARCHAR2,
810 X_Prd_Information20 VARCHAR2,
811 X_Prd_Information21 VARCHAR2,
812 X_Prd_Information22 VARCHAR2,
813 X_Prd_Information23 VARCHAR2,
814 X_Prd_Information24 VARCHAR2,
815 X_Prd_Information25 VARCHAR2,
816 X_Prd_Information26 VARCHAR2,
817 X_Prd_Information27 VARCHAR2,
818 X_Prd_Information28 VARCHAR2,
819 X_Prd_Information29 VARCHAR2,
820 X_Prd_Information30 VARCHAR2,
821 X_Payslip_view_Date DATE default null
822 ) IS
823 --
824 BEGIN
825 --
826 -- Lock payroll record to enforce integrity when changing time period
827 -- statuses.
828 pay_payrolls_f_pkg.lock_payroll(X_Payroll_Id);
829 --
830 UPDATE per_time_periods
831 SET time_period_id = X_Time_Period_Id,
832 payroll_id = X_Payroll_Id,
833 end_date = X_End_Date,
834 period_name = X_Period_Name,
835 period_num = X_Period_Num,
836 period_type = X_Period_Type,
837 start_date = X_Start_Date,
838 cut_off_date = X_Cut_Off_Date,
839 default_dd_date = X_Default_Dd_Date,
840 description = X_Description,
841 pay_advice_date = X_Pay_Advice_Date,
842 period_set_name = X_Period_Set_Name,
843 period_year = X_Period_Year,
844 proc_period_type = X_Proc_Period_Type,
845 quarter_num = X_Quarter_Num,
846 quickpay_display_number = X_Quickpay_Display_Number,
847 regular_payment_date = X_Regular_Payment_Date,
848 run_display_number = X_Run_Display_Number,
849 status = X_Status,
850 year_number = X_Year_Number,
851 attribute_category = X_Attribute_Category,
852 attribute1 = X_Attribute1,
853 attribute2 = X_Attribute2,
854 attribute3 = X_Attribute3,
855 attribute4 = X_Attribute4,
856 attribute5 = X_Attribute5,
857 attribute6 = X_Attribute6,
858 attribute7 = X_Attribute7,
859 attribute8 = X_Attribute8,
860 attribute9 = X_Attribute9,
861 attribute10 = X_Attribute10,
862 attribute11 = X_Attribute11,
863 attribute12 = X_Attribute12,
864 attribute13 = X_Attribute13,
865 attribute14 = X_Attribute14,
866 attribute15 = X_Attribute15,
867 attribute16 = X_Attribute16,
868 attribute17 = X_Attribute17,
869 attribute18 = X_Attribute18,
870 attribute19 = X_Attribute19,
871 attribute20 = X_Attribute20,
872 prd_information_category = X_Prd_Information_Category,
873 prd_information1 = X_Prd_Information1,
874 prd_information2 = X_Prd_Information2,
875 prd_information3 = X_Prd_Information3,
876 prd_information4 = X_Prd_Information4,
877 prd_information5 = X_Prd_Information5,
878 prd_information6 = X_Prd_Information6,
879 prd_information7 = X_Prd_Information7,
880 prd_information8 = X_Prd_Information8,
881 prd_information9 = X_Prd_Information9,
882 prd_information10 = X_Prd_Information10,
883 prd_information11 = X_Prd_Information11,
884 prd_information12 = X_Prd_Information12,
885 prd_information13 = X_Prd_Information13,
886 prd_information14 = X_Prd_Information14,
887 prd_information15 = X_Prd_Information15,
888 prd_information16 = X_Prd_Information16,
889 prd_information17 = X_Prd_Information17,
890 prd_information18 = X_Prd_Information18,
891 prd_information19 = X_Prd_Information19,
892 prd_information20 = X_Prd_Information20,
893 prd_information21 = X_Prd_Information21,
894 prd_information22 = X_Prd_Information22,
895 prd_information23 = X_Prd_Information23,
896 prd_information24 = X_Prd_Information24,
897 prd_information25 = X_Prd_Information25,
898 prd_information26 = X_Prd_Information26,
899 prd_information27 = X_Prd_Information27,
900 prd_information28 = X_Prd_Information28,
901 prd_information29 = X_Prd_Information29,
902 prd_information30 = X_Prd_Information30,
903 payslip_view_date = X_Payslip_view_date
904 WHERE rowid = X_rowid;
905 --
906 if (SQL%NOTFOUND) then
907 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
908 hr_utility.set_message_token('PROCEDURE',
909 'per_time_periods_pkg.update_row');
910 hr_utility.set_message_token('STEP','1');
911 hr_utility.raise_error;
912 end if;
913 --
914 END Update_Row;
915 --
916 -----------------------------------------------------------------------------
917 -- Name --
918 -- Delete_Row --
919 -- Purpose --
920 -- Table handler procedure that supports the delete of a time period via --
921 -- the Define Payroll form. --
922 -- Arguments --
923 -- See below. --
924 -- Notes --
925 -- --
926 -----------------------------------------------------------------------------
927 --
928 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
929 --
930 BEGIN
931 --
932 DELETE FROM per_time_periods
933 WHERE rowid = X_Rowid;
934 --
935 if (SQL%NOTFOUND) then
936 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
937 hr_utility.set_message_token('PROCEDURE',
938 'per_time_periods_pkg.delete_row');
939 hr_utility.set_message_token('STEP','1');
940 hr_utility.raise_error;
941 end if;
942 END Delete_Row;
943 --
944 --
945 ------------------------------------------------------------------------------------
946 --
947 PROCEDURE Update_Payslip_View_Date (P_Payroll_id Number,
948 P_offset Number
949 ) IS
950 Begin
951
952 Update per_time_periods
953 Set Payslip_view_Date = Regular_Payment_Date + P_offset
954 where Payroll_id = P_Payroll_id;
955
956 End Update_Payslip_View_Date;
957 ------------------------------------------------------------------------------------
958
959 END PER_TIME_PERIODS_PKG;