[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYROLLS_F_PKG
Source
1 PACKAGE BODY PAY_PAYROLLS_F_PKG as
2 /* $Header: pyprl01t.pkb 120.2 2006/11/10 16:49:25 ajeyam noship $ */
3 --
4 c_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
5 --
6 -----------------------------------------------------------------------------
7 -- Name --
8 -- maintain_soft_coding_keyflex --
9 -- Purpose --
10 -- Maintains the SCL keyflex. As the SCL keyflex can be set at different --
11 -- levels ie. assignment, payroll, organization etc ... the standard FND --
12 -- VALID cannot deal with partial flexfields so this function replaces --
13 -- it. --
14 -- Arguments --
15 -- See below. --
16 -- Notes --
17 -- None --
18 -----------------------------------------------------------------------------
19 --
20 function maintain_soft_coding_keyflex
21 (
22 p_scl_structure number,
23 p_soft_coding_keyflex_id number,
24 p_concatenated_segments varchar2,
25 p_summary_flag varchar2,
26 p_start_date_active date,
27 p_end_date_active date,
28 p_segment1 varchar2,
29 p_segment2 varchar2,
30 p_segment3 varchar2,
31 p_segment4 varchar2,
32 p_segment5 varchar2,
33 p_segment6 varchar2,
34 p_segment7 varchar2,
35 p_segment8 varchar2,
36 p_segment9 varchar2,
37 p_segment10 varchar2,
38 p_segment11 varchar2,
39 p_segment12 varchar2,
40 p_segment13 varchar2,
44 p_segment17 varchar2,
41 p_segment14 varchar2,
42 p_segment15 varchar2,
43 p_segment16 varchar2,
45 p_segment18 varchar2,
46 p_segment19 varchar2,
47 p_segment20 varchar2,
48 p_segment21 varchar2,
49 p_segment22 varchar2,
50 p_segment23 varchar2,
51 p_segment24 varchar2,
52 p_segment25 varchar2,
53 p_segment26 varchar2,
54 p_segment27 varchar2,
55 p_segment28 varchar2,
56 p_segment29 varchar2,
57 p_segment30 varchar2
58 ) return number is
59 --
60 cursor csr_soft_coding_exists is
61 select hsc.soft_coding_keyflex_id
62 from hr_soft_coding_keyflex hsc
63 where hsc.soft_coding_keyflex_id = p_soft_coding_keyflex_id;
64 --
65 v_dummy number;
66 v_soft_coding_keyflex_id number := p_soft_coding_keyflex_id;
67 --
68 begin
69 --
70 -- A soft_keyflex_id has been specified so confirm it still is valid.
71 if (v_soft_coding_keyflex_id is not null and
72 v_soft_coding_keyflex_id <> -1) then
73 --
74 open csr_soft_coding_exists;
75 fetch csr_soft_coding_exists into v_dummy;
76 --
77 -- Keyflex does not exist so need to rederive a soft_keyflex_id.
78 if csr_soft_coding_exists%notfound then
79 v_soft_coding_keyflex_id := -1;
80 -- Keyflex does exist.
81 else
82 v_soft_coding_keyflex_id := p_soft_coding_keyflex_id;
83 end if;
84 --
85 close csr_soft_coding_exists;
86 --
87 end if;
88 --
89 if (v_soft_coding_keyflex_id = -1) then
90 --
91 -- Need to check for a partial value.
92 begin
93 select s.soft_coding_keyflex_id
94 into v_soft_coding_keyflex_id
95 from hr_soft_coding_keyflex s
96 where s.id_flex_num = p_scl_structure
97 and s.enabled_flag = 'Y'
98 and (s.segment1 = p_segment1
99 or (s.segment1 is null
100 and p_segment1 is null))
101 and (s.segment2 = p_segment2
102 or (s.segment2 is null
103 and p_segment2 is null))
104 and (s.segment3 = p_segment3
105 or (s.segment3 is null
106 and p_segment3 is null))
107 and (s.segment4 = p_segment4
108 or (s.segment4 is null
109 and p_segment4 is null))
110 and (s.segment5 = p_segment5
111 or (s.segment5 is null
112 and p_segment5 is null))
113 and (s.segment6 = p_segment6
114 or (s.segment6 is null
115 and p_segment6 is null))
116 and (s.segment7 = p_segment7
117 or (s.segment7 is null
118 and p_segment7 is null))
119 and (s.segment8 = p_segment8
120 or (s.segment8 is null
121 and p_segment8 is null))
122 and (s.segment9 = p_segment9
123 or (s.segment9 is null
124 and p_segment9 is null))
125 and (s.segment10 = p_segment10
126 or (s.segment10 is null
127 and p_segment10 is null))
128 and (s.segment11 = p_segment11
129 or (s.segment11 is null
130 and p_segment11 is null))
131 and (s.segment12 = p_segment12
132 or (s.segment12 is null
133 and p_segment12 is null))
134 and (s.segment13 = p_segment13
135 or (s.segment13 is null
136 and p_segment13 is null))
137 and (s.segment14 = p_segment14
138 or (s.segment14 is null
139 and p_segment14 is null))
140 and (s.segment15 = p_segment15
141 or (s.segment15 is null
142 and p_segment15 is null))
143 and (s.segment16 = p_segment16
144 or (s.segment16 is null
145 and p_segment16 is null))
146 and (s.segment17 = p_segment17
147 or (s.segment17 is null
148 and p_segment17 is null))
149 and (s.segment18 = p_segment18
150 or (s.segment18 is null
151 and p_segment18 is null))
152 and (s.segment19 = p_segment19
153 or (s.segment19 is null
154 and p_segment19 is null))
155 and (s.segment20 = p_segment20
156 or (s.segment20 is null
157 and p_segment20 is null))
158 and (s.segment21 = p_segment21
159 or (s.segment21 is null
160 and p_segment21 is null))
161 and (s.segment22 = p_segment22
162 or (s.segment22 is null
163 and p_segment22 is null))
164 and (s.segment23 = p_segment23
165 or (s.segment23 is null
166 and p_segment23 is null))
167 and (s.segment24 = p_segment24
168 or (s.segment24 is null
169 and p_segment24 is null))
170 and (s.segment25 = p_segment25
171 or (s.segment25 is null
172 and p_segment25 is null))
173 and (s.segment26 = p_segment26
174 or (s.segment26 is null
175 and p_segment26 is null))
176 and (s.segment27 = p_segment27
177 or (s.segment27 is null
178 and p_segment27 is null))
179 and (s.segment28 = p_segment28
183 or (s.segment29 is null
180 or (s.segment28 is null
181 and p_segment28 is null))
182 and (s.segment29 = p_segment29
184 and p_segment29 is null))
185 and (s.segment30 = p_segment30
186 or (s.segment30 is null
187 and p_segment30 is null));
188 exception
189 when no_data_found then null;
190 when too_many_rows then null;
191 end;
192 --
193 -- check to see if the soft coding keyflex combination already
194 -- exists. if it doesn't then, insert the required row.
195 if (v_soft_coding_keyflex_id = -1) then
196 --
197 -- select the next sequence value for the soft coding keyflex.
198 begin
199 select hr_soft_coding_keyflex_s.nextval
200 into v_soft_coding_keyflex_id
201 from sys.dual;
202 exception
203 when NO_DATA_FOUND then
204 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
205 hr_utility.set_message_token('PROCEDURE',
206 'pay_payrolls_f_pkg.maintain_soft_coding_keyflex');
207 hr_utility.set_message_token('STEP','1');
208 hr_utility.raise_error;
209 end;
210 --
211 -- Insert the new row.
212 begin
213 insert into hr_soft_coding_keyflex
214 (soft_coding_keyflex_id
215 ,concatenated_segments
216 ,id_flex_num
217 ,last_update_date
218 ,last_updated_by
219 ,summary_flag
220 ,enabled_flag
221 ,start_date_active
222 ,end_date_active
223 ,segment1
224 ,segment2
225 ,segment3
226 ,segment4
227 ,segment5
228 ,segment6
229 ,segment7
230 ,segment8
231 ,segment9
232 ,segment10
233 ,segment11
234 ,segment12
235 ,segment13
236 ,segment14
237 ,segment15
238 ,segment16
239 ,segment17
240 ,segment18
241 ,segment19
242 ,segment20
243 ,segment21
244 ,segment22
245 ,segment23
246 ,segment24
247 ,segment25
248 ,segment26
249 ,segment27
250 ,segment28
251 ,segment29
252 ,segment30)
253 values
254 (v_soft_coding_keyflex_id
255 ,p_concatenated_segments
256 ,p_scl_structure
257 ,null
258 ,null
259 ,p_summary_flag
260 ,'Y'
261 ,p_start_date_active
262 ,p_end_date_active
263 ,p_segment1
264 ,p_segment2
265 ,p_segment3
266 ,p_segment4
267 ,p_segment5
268 ,p_segment6
269 ,p_segment7
270 ,p_segment8
271 ,p_segment9
272 ,p_segment10
273 ,p_segment11
274 ,p_segment12
275 ,p_segment13
276 ,p_segment14
277 ,p_segment15
278 ,p_segment16
279 ,p_segment17
280 ,p_segment18
281 ,p_segment19
282 ,p_segment20
283 ,p_segment21
284 ,p_segment22
285 ,p_segment23
286 ,p_segment24
287 ,p_segment25
288 ,p_segment26
289 ,p_segment27
290 ,p_segment28
291 ,p_segment29
292 ,p_segment30);
293 end;
294 --
295 end if;
296 --
297 return(v_soft_coding_keyflex_id);
298 --
299 end if;
300 --
301 return(v_soft_coding_keyflex_id);
302 --
303 end maintain_soft_coding_keyflex;
304 --
305 -----------------------------------------------------------------------------
306 -- Name --
307 -- current_values --
308 -- Purpose --
309 -- Returns the current values for several columns so that a check can be --
310 -- made to see if the value has changed. --
311 -- Arguments --
312 -- See below. --
313 -- Notes --
314 -- None --
315 -----------------------------------------------------------------------------
316 --
317 procedure current_values
318 (
319 p_rowid varchar2,
320 p_payroll_name in out nocopy varchar2,
321 p_number_of_years in out nocopy number,
322 p_default_payment_method_id in out nocopy number
323 ) is
324 --
325 cursor csr_current_values is
326 select prl.payroll_name,
327 prl.number_of_years,
328 prl.default_payment_method_id
329 from pay_payrolls_f prl
330 where prl.rowid = p_rowid;
331 --
332 v_values_rec csr_current_values%rowtype;
333 --
334 begin
335 --
336 open csr_current_values;
337 fetch csr_current_values into v_values_rec;
338 if csr_current_values%notfound then
339 close csr_current_values;
340 hr_utility.set_message(801, 'ALL_PROCEDURE_FAIL');
344 hr_utility.raise_error;
341 hr_utility.set_message_token('PROCEDURE',
342 'pay_payrolls_f_pkg.current_values');
343 hr_utility.set_message_token('STEP', 1);
345 else
346 close csr_current_values;
347 end if;
348 --
349 p_payroll_name := v_values_rec.payroll_name;
350 p_number_of_years := v_values_rec.number_of_years;
351 p_default_payment_method_id := v_values_rec.default_payment_method_id;
352 --
353 end current_values;
354 --
355 -----------------------------------------------------------------------------
356 -- Name --
357 -- lock_payroll --
358 -- Purpose --
359 -- Locks the specified payroll. --
360 -- Arguments --
361 -- See below. --
362 -- Notes --
363 -- This is used to reduce concurrency problems when changing / checking --
364 -- time periods. --
365 -----------------------------------------------------------------------------
366 --
367 procedure lock_payroll
368 (
369 p_payroll_id number
370 ) is
371 --
372 cursor csr_lock_payroll is
373 select prl.payroll_id
374 from pay_payrolls_f prl
375 where prl.payroll_id = p_payroll_id
376 for update;
377 --
378 begin
379 --
380 open csr_lock_payroll;
381 close csr_lock_payroll;
382 --
383 end lock_payroll;
384 --
385 -----------------------------------------------------------------------------
386 -- Name --
387 -- chk_payroll_unique --
388 -- Purpose --
389 -- Make sure that the payroll name being entered is unique within the --
390 -- business group. --
391 -- Arguments --
392 -- See Below. --
393 -- Notes --
394 -- None --
395 -----------------------------------------------------------------------------
396 --
397 procedure chk_payroll_unique
398 (
399 p_payroll_id number,
400 p_payroll_name varchar2,
401 p_business_group_id number
402 ) is
403 --
404 cursor csr_payroll_exists is
405 SELECT prl.payroll_id
406 from pay_all_payrolls_f prl
407 where upper(prl.payroll_name) = upper(p_payroll_name)
408 and (prl.payroll_id <> p_payroll_id or
409 p_payroll_id is null)
410 and prl.business_group_id + 0 = p_business_group_id;
411 --
412 v_payroll_id number;
413 --
414 begin
415 --
416 -- Make sure payroll name is unique.
417 open csr_payroll_exists;
418 fetch csr_payroll_exists into v_payroll_id;
419 if csr_payroll_exists%found then
420 close csr_payroll_exists;
421 hr_utility.set_message(801, 'HR_6667_PAY_PAYROLL_EXISTS');
422 hr_utility.raise_error;
423 else
424 close csr_payroll_exists;
425 end if;
426 --
427 end chk_payroll_unique;
428 --
429 -----------------------------------------------------------------------------
430 -- Name --
431 -- propagate_changes --
432 -- Purpose --
433 -- Copies values accross all datetrack rows for a payroll. --
434 -- Arguments --
435 -- See Below. --
436 -- Notes --
437 -- The NUMBER_OF_YEARS and PAYROLL_NAME columns are not datetracked in --
438 -- that they should be the same for the lifetime of the payroll. As a --
439 -- workaround to datetrack the rows are updated after datetrack has been --
440 -- called. --
441 -----------------------------------------------------------------------------
442 --
443 procedure propagate_changes
444 (
445 p_payroll_id number,
446 p_payroll_name varchar2,
447 p_number_of_years number
448 ) is
449 --
450 cursor csr_payroll_rows is
451 select prl.payroll_name,
452 prl.number_of_years
453 from pay_payrolls_f prl
454 where prl.payroll_id = p_payroll_id
455 for update;
456 --
457 begin
458 --
459 for v_prl_rec in csr_payroll_rows loop
460 --
461 update pay_payrolls_f prl
462 set prl.payroll_name = nvl(p_payroll_name,
463 v_prl_rec.payroll_name),
464 prl.number_of_years = nvl(p_number_of_years,
465 v_prl_rec.number_of_years)
466 where current of csr_payroll_rows;
467 --
468 end loop;
469 --
470 end propagate_changes;
471 --
472 -----------------------------------------------------------------------------
476 -- 1.Retrieves keyflex structure for the legislations SCL and also for the --
473 -- Name --
474 -- get_bg_and_leg_info --
475 -- Purpose --
477 -- costing keyflex for the business group. --
478 -- 2.Looks for rules in PAY_LEGISLATION_RULES for the disabling of --
479 -- update of time period --
480 -- dates ie. some legislations require that some dates cannot be changed --
481 -- eg. cannot update the regular payment date etc... --
482 -- 3.Looks for rule in PAY_LEGISLATION_RULES which determines whether --
483 -- the pay offset date must be negative or whether it can be negative --
484 -- or positive.
485 -- Arguments --
486 -- p_regular_payment_date --
487 -- p_default_dd_date TRUE if update of the particular date is --
488 -- p_pay_advice_date disallowed. --
489 -- p_cut_off_date --
490 -- p_pay_date_offset_rule
491 -- p_payslip_view_date by rajeesha bug 4246280
492 -- Notes --
493 -- The existence of a row in PAY_LEGISLATION_RULES with a RULE_TYPE as --
494 -- shown below means that the date is disabled ie. cannot be updated. --
495 -- RULE_TYPE DATE --
496 -- P REGULAR_PAYMENT_DATE --
497 -- C CUT_OFF_DATE --
498 -- D DEFAULT_DD_DATE --
499 -- A PAY_ADVICE_DATE --
500 -- With a rule type of PDO the value N signifies only negative values --
501 -- are allowed , NP signifies Negative and Positive values are allowed --
502 -- If the rule is missing then the value NP is assumed. --
503 -----------------------------------------------------------------------------
504 --
505 procedure get_bg_and_leg_info
506 (
507 p_business_group_id number,
508 p_legislation_code varchar2,
509 p_cost_id_flex_num out nocopy varchar2,
510 p_scl_id_flex_num out nocopy varchar2,
511 p_regular_payment_date out nocopy boolean,
512 p_default_dd_date out nocopy boolean,
513 p_pay_advice_date out nocopy boolean,
514 p_cut_off_date out nocopy boolean,
515 p_pay_date_offset_rule out nocopy varchar2,
516 p_scl_enabled out nocopy boolean,
517 p_payslip_view_date out nocopy boolean
518 ) is
519 --
520 cursor csr_cost_id_flex_num is
521 select bg.cost_allocation_structure
522 from per_business_groups bg
523 where bg.business_group_id + 0 = p_business_group_id;
524 --
525 cursor csr_leg_rules is
526 select lr.rule_type,
527 lr.rule_mode
528 from pay_legislation_rules lr
529 where lr.legislation_code = p_legislation_code;
530 --
531 cursor csr_leg_rules2 is
532 select lr.rule_type,
533 lr.rule_mode
534 from pay_legislative_field_info lr
535 where lr.legislation_code = p_legislation_code
536 and lr.field_name = 'PAYSLIP_VIEW_DATE'
537 and lr.rule_type = 'UPDATE';
538 --
539 v_regular_payment_date boolean := false;
540 v_default_dd_date boolean := false;
541 v_pay_advice_date boolean := false;
542 v_cut_off_date boolean := false;
543 v_scl_enabled boolean := false;
544 v_pay_date_offset_rule varchar2(60) := 'NP' ;
545 v_payslip_view_date boolean := false;
546 --
547 begin
548 --
549 -- Retrieve the costing structure identifier for the business group.
550 open csr_cost_id_flex_num;
551 fetch csr_cost_id_flex_num into p_cost_id_flex_num;
552 close csr_cost_id_flex_num;
553 --
554 -- Retrieve all legislation rules for the legislation.
555 for v_leg_rule in csr_leg_rules loop
556 --
557 --
558 -- The dates that can be updated for the legislation.
559 --
560 if v_leg_rule.rule_type = 'A' then
561 v_pay_advice_date := true;
562 elsif v_leg_rule.rule_type = 'D' then
563 v_default_dd_date := true;
564 elsif v_leg_rule.rule_type = 'C' then
565 v_cut_off_date := true;
566 elsif v_leg_rule.rule_type = 'P' then
567 v_regular_payment_date := true;
568 --
569 --
570 -- The SCL structure for the legislation
571 --
572 elsif v_leg_rule.rule_type = 'S' then
573 p_scl_id_flex_num := v_leg_rule.rule_mode;
574 --
575 --
576 -- The rule for maintaining the pay date offset.
577 --
578 elsif v_leg_rule.rule_type = 'PDO' then
579 v_pay_date_offset_rule := v_leg_rule.rule_mode;
580 --
581 --
582 -- Is the SCL enabled to appear at the payroll level for the legislation.
583 --
584 elsif v_leg_rule.rule_type = 'SDL' and v_leg_rule.rule_mode = 'P' then
585 v_scl_enabled := true;
586 end if;
587 --
588 end loop;
589 --
590 for v_leg_rule in csr_leg_rules2 loop
591 if v_leg_rule.rule_mode = 'Y' then
592 v_payslip_view_date := true ;
593 end if;
594 end loop;
595
596 p_regular_payment_date := v_regular_payment_date;
597 p_default_dd_date := v_default_dd_date;
601 p_scl_enabled := v_scl_enabled;
598 p_pay_advice_date := v_pay_advice_date;
599 p_cut_off_date := v_cut_off_date;
600 p_pay_date_offset_rule := v_pay_date_offset_rule;
602 p_payslip_view_date := v_payslip_view_date;
603 --
604 end get_bg_and_leg_info;
605 --
606 -----------------------------------------------------------------------------
607 -- Name --
608 -- validate_dflt_payment_method --
609 -- Purpose --
610 -- Makes sure that the default payment method exists for the life of --
611 -- the payroll record that uses it. --
612 -- Arguments --
613 -- See Below. --
614 -- Notes --
615 -- None --
616 -----------------------------------------------------------------------------
617 --
618 procedure validate_dflt_payment_method
619 (
620 p_default_payment_method_id number,
621 p_validation_start_date date,
622 p_validation_end_date date
623 ) is
624 --
625 cursor csr_opm_dates is
626 select opm.effective_start_date,
627 opm.effective_end_date
628 from pay_org_payment_methods_f opm
629 where opm.org_payment_method_id = p_default_payment_method_id
630 order by opm.effective_start_date
631 for update;
632 --
633 v_opm_rec csr_opm_dates%rowtype;
634 v_start_date date;
635 v_end_date date;
636 --
637 begin
638 --
639 open csr_opm_dates;
640 fetch csr_opm_dates into v_opm_rec;
641 if csr_opm_dates%notfound then
642 close csr_opm_dates;
643 hr_utility.set_message(801, 'ALL_PROCEDURE_FAIL');
644 hr_utility.set_message_token('PROCEDURE',
645 'validate_dflt_payment_method');
646 hr_utility.set_message_token('STEP', 1);
647 hr_utility.raise_error;
648 else
649 v_start_date := v_opm_rec.effective_start_date;
650 v_end_date := v_opm_rec.effective_end_date;
651 end if;
652 --
653 loop
654 fetch csr_opm_dates into v_opm_rec;
655 exit when csr_opm_dates%notfound;
656 v_end_date := v_opm_rec.effective_end_date;
657 end loop;
658 close csr_opm_dates;
659 --
660 if v_start_date > p_validation_start_date or
661 v_end_date < p_validation_end_date then
662 hr_utility.set_message(801, 'HR_7096_PAYM_PYRLL_DFLT_INVID');
663 hr_utility.raise_error;
664 end if;
665 --
666 end validate_dflt_payment_method;
667 --
668 -----------------------------------------------------------------------------
669 -- Name --
670 -- maintain_dflt_payment_method --
671 -- Purpose --
672 -- Creates OPMU's to represent the default payment method chosen for the --
673 -- payroll. --
674 -- Arguments --
675 -- See Below. --
676 -- Notes --
677 -- None --
678 -----------------------------------------------------------------------------
679 --
680 procedure maintain_dflt_payment_method
681 (
682 p_payroll_id number,
683 p_default_payment_method_id number,
684 p_validation_start_date date,
685 p_validation_end_date date
686 ) is
687 --
688 cursor csr_payroll_opmu is
689 select opmu.effective_start_date,
690 opmu.effective_end_date
691 from pay_org_pay_method_usages_f opmu
692 where opmu.payroll_id = p_payroll_id
693 and opmu.org_payment_method_id = p_default_payment_method_id
694 and opmu.effective_start_date <= p_validation_end_date
695 and opmu.effective_end_date >= p_validation_start_date
696 order by opmu.effective_start_date
697 for update;
698 --
699 v_insert_record boolean := TRUE;
700 --
701 begin
702 --
703 -- The new opmu to be created is for the default payment method for the
704 -- payroll and should exist for the duration of the default on the payroll
705 -- record ie. valiadation_start_date to validation_end_date.
706 --
707 -- Retrieve all opmu's for a payroll for the specified payment type that
708 -- overlap with the period of change.
709 for v_opmu_rec in csr_payroll_opmu loop
710 --
711 -- An existing opmu already represents the default so do nothing.
712 -- current opmu |------------------------------------|
713 -- required opmu |----------------------------|
714 if v_opmu_rec.effective_start_date <= p_validation_start_date and
715 v_opmu_rec.effective_end_date >= p_validation_end_date then
716 --
717 v_insert_record := FALSE;
718 --
719 -- opmu overlaps with start of required opmu so need to shorten it ie.
720 -- current opmu |--------|
721 -- required opmu . |----------------------------|
722 -- . . .
723 -- adjust opmu |--|. .
727 update pay_org_pay_method_usages_f opmu
724 -- insert new opmu |----------------------------| (see below)
725 elsif v_opmu_rec.effective_start_date < p_validation_start_date then
726 --
728 set opmu.effective_end_date = p_validation_start_date - 1
729 where current of csr_payroll_opmu;
730 --
731 -- opmu overlaps with end of required opmu so need to shorten it ie.
732 -- current opmu |--------|
733 -- required opmu |----------------------------| .
734 -- . . .
735 -- adjust opmu . .|----|
736 -- insert new opmu |----------------------------| (see below)
737 elsif v_opmu_rec.effective_end_date > p_validation_end_date then
738 --
739 update pay_org_pay_method_usages_f opmu
740 set opmu.effective_start_date = p_validation_end_date + 1
741 where current of csr_payroll_opmu;
742 --
743 -- opmu overlaps within required opmu so need to remove it ie.
744 -- current opmu |----)
745 -- required opmu |----------------------------|
746 -- . .
747 -- remove opmu . .
748 -- insert new opmu |----------------------------| (see below)
749 else
750 --
751 delete from pay_org_pay_method_usages_f
752 where current of csr_payroll_opmu;
753 --
754 end if;
755
756 end loop;
757 --
758 if v_insert_record then
759 --
760 -- Create opmu to represent the default payment method selected for the
761 -- payroll.
762 insert into pay_org_pay_method_usages_f
763 (org_pay_method_usage_id,
764 effective_start_date,
765 effective_end_date,
766 payroll_id,
767 org_payment_method_id,
768 last_update_date,
769 last_updated_by,
770 last_update_login,
771 created_by,
772 creation_date)
773 values
774 (pay_org_pay_method_usages_s.nextval,
775 p_validation_start_date,
776 p_validation_end_date,
777 p_payroll_id,
778 p_default_payment_method_id,
779 trunc(sysdate),
780 0,
781 0,
782 0,
783 trunc(sysdate));
784 --
785 end if;
786 --
787 end maintain_dflt_payment_method;
788 --
789 -----------------------------------------------------------------------------
790 -- Name --
791 -- dflt_scl_from_bg --
792 -- Purpose --
793 -- Retrieves the current values for the SCL that were set up for the --
794 -- business group. These are then used as defaults when creating a SCL --
795 -- for a payroll. --
796 -- Arguments --
797 -- See Below. --
798 -- Notes --
799 -- None --
800 -----------------------------------------------------------------------------
801 --
802 procedure dflt_scl_from_bg
803 (
804 p_business_group_id number,
805 p_segment1 in out nocopy varchar2,
806 p_segment2 in out nocopy varchar2,
807 p_segment3 in out nocopy varchar2,
808 p_segment4 in out nocopy varchar2,
809 p_segment5 in out nocopy varchar2,
810 p_segment6 in out nocopy varchar2,
811 p_segment7 in out nocopy varchar2,
812 p_segment8 in out nocopy varchar2,
813 p_segment9 in out nocopy varchar2,
814 p_segment10 in out nocopy varchar2,
815 p_segment11 in out nocopy varchar2,
816 p_segment12 in out nocopy varchar2,
817 p_segment13 in out nocopy varchar2,
818 p_segment14 in out nocopy varchar2,
819 p_segment15 in out nocopy varchar2,
820 p_segment16 in out nocopy varchar2,
821 p_segment17 in out nocopy varchar2,
822 p_segment18 in out nocopy varchar2,
823 p_segment19 in out nocopy varchar2,
824 p_segment20 in out nocopy varchar2,
825 p_segment21 in out nocopy varchar2,
826 p_segment22 in out nocopy varchar2,
827 p_segment23 in out nocopy varchar2,
828 p_segment24 in out nocopy varchar2,
829 p_segment25 in out nocopy varchar2,
830 p_segment26 in out nocopy varchar2,
831 p_segment27 in out nocopy varchar2,
832 p_segment28 in out nocopy varchar2,
833 p_segment29 in out nocopy varchar2,
834 p_segment30 in out nocopy varchar2
835 ) is
836 --
837 cursor csr_bg_scl_segs is
838 select *
839 from hr_soft_coding_keyflex kf
840 where exists
841 (select null
842 from hr_all_organization_units org
843 where org.organization_id = p_business_group_id
844 and org.soft_coding_keyflex_id = kf.soft_coding_keyflex_id);
845 --
846 v_scl_rec csr_bg_scl_segs%rowtype;
847 --
848 begin
849 --
850 -- Retrieve the SCL values set for the business group. This is then used to
851 -- default the SCL when creating an SCL for a payroll.
852 open csr_bg_scl_segs;
853 fetch csr_bg_scl_segs into v_scl_rec;
857 p_segment3 := v_scl_rec.segment3;
854 if csr_bg_scl_segs%found then
855 p_segment1 := v_scl_rec.segment1;
856 p_segment2 := v_scl_rec.segment2;
858 p_segment4 := v_scl_rec.segment4;
859 p_segment5 := v_scl_rec.segment5;
860 p_segment6 := v_scl_rec.segment6;
861 p_segment7 := v_scl_rec.segment7;
862 p_segment8 := v_scl_rec.segment8;
863 p_segment9 := v_scl_rec.segment9;
864 p_segment10 := v_scl_rec.segment10;
865 p_segment11 := v_scl_rec.segment11;
866 p_segment12 := v_scl_rec.segment12;
867 p_segment13 := v_scl_rec.segment13;
868 p_segment14 := v_scl_rec.segment14;
869 p_segment15 := v_scl_rec.segment15;
870 p_segment16 := v_scl_rec.segment16;
871 p_segment17 := v_scl_rec.segment17;
872 p_segment18 := v_scl_rec.segment18;
873 p_segment19 := v_scl_rec.segment19;
874 p_segment20 := v_scl_rec.segment20;
875 p_segment21 := v_scl_rec.segment21;
876 p_segment22 := v_scl_rec.segment22;
877 p_segment23 := v_scl_rec.segment23;
878 p_segment24 := v_scl_rec.segment24;
879 p_segment25 := v_scl_rec.segment25;
880 p_segment26 := v_scl_rec.segment26;
881 p_segment27 := v_scl_rec.segment27;
882 p_segment28 := v_scl_rec.segment28;
883 p_segment29 := v_scl_rec.segment29;
884 p_segment30 := v_scl_rec.segment30;
885 end if;
886 close csr_bg_scl_segs;
887 --
888 end dflt_scl_from_bg;
889 --
890 -----------------------------------------------------------------------------
891 -- Name --
892 -- validate_insert_payroll --
893 -- Purpose --
894 -- Validates the creation of a payroll. --
895 -- Arguments --
896 -- See Below. --
897 -- Notes --
898 -- 1. Is the payroll name unique. --
899 -- 2. If a default payment method is chosen does it exist for the --
900 -- lifetime of the payroll. --
901 -----------------------------------------------------------------------------
902 --
903 procedure validate_insert_payroll
904 (
905 p_business_group_id number,
906 p_payroll_name varchar2,
907 p_default_payment_method_id number,
908 p_validation_start_date date,
909 p_validation_end_date date
910 ) is
911 --
912 begin
913
914 -- Make sure payroll name is unique within the business group.
915 chk_payroll_unique
916 (null,
917 p_payroll_name,
918 p_business_group_id);
919
920 -- Make sure default payment method is valid for the duration of the payroll.
921 if p_default_payment_method_id is not null then
922 --
923 validate_dflt_payment_method
924 (p_default_payment_method_id,
925 p_validation_start_date,
926 p_validation_end_date);
927 --
928 end if;
929 --
930 end validate_insert_payroll;
931 --
932 -----------------------------------------------------------------------------
933 -- Name --
934 -- insert_payroll --
935 -- Purpose --
936 -- Mainatins payroll related tables on creation of a payroll. --
937 -- Arguments --
938 -- See Below. --
939 -- Notes --
940 -- 1. Create time periods for payroll. --
941 -- 2. Create OPMU for default payment method if it is specified. --
942 -----------------------------------------------------------------------------
943 --
944 procedure insert_payroll
945 (
946 p_payroll_id number,
947 p_default_payment_method_id number,
948 p_validation_start_date date,
949 p_validation_end_date date
950 ) is
951 --
952 begin
953
954 -- Create payroll time periods based on the payroll definition.
955 hr_payrolls.create_payroll_proc_periods
956 (p_payroll_id,
957 null, -- last_update_date
958 null, -- last_updated_by
959 null, -- last_update_login
960 null, -- created_by
961 null); -- creation_date
962
963 -- create opmu for default payment method if it has benn specified.
964 if p_default_payment_method_id is not null then
965 --
966 maintain_dflt_payment_method
967 (p_payroll_id,
968 p_default_payment_method_id,
969 p_validation_start_date,
970 p_validation_end_date);
971 --
972 end if;
973 --
974 end insert_payroll;
975 --
976 -----------------------------------------------------------------------------
977 -- Name --
978 -- validate_update_payroll --
979 -- Purpose --
980 -- Validates the updating of a payroll. --
981 -- Arguments --
985 -- 2. If the default payment method has chnaged make sure it exists for --
982 -- See Below. --
983 -- Notes --
984 -- 1. Is the payroll name unique. --
986 -- the lifetime of the payroll. --
987 -----------------------------------------------------------------------------
988 --
989 procedure validate_update_payroll
990 (
991 p_business_group_id number,
992 p_payroll_id number,
993 p_payroll_name varchar2,
994 p_s_payroll_name varchar2,
995 p_default_payment_method_id number,
996 p_s_default_payment_method_id number,
997 p_validation_start_date date,
998 p_validation_end_date date
999 ) is
1000 --
1001 begin
1002 --
1003 -- Make sure payroll name is unique within the business group.
1004 if p_payroll_name <> p_s_payroll_name then
1005 --
1006 chk_payroll_unique
1007 (p_payroll_id,
1008 p_payroll_name,
1009 p_business_group_id);
1010 --
1011 end if;
1012 --
1013 -- Make sure default payment method is valid for the duration of the payroll.
1014 if p_default_payment_method_id <> nvl(p_s_default_payment_method_id,
1015 0) and
1016 p_default_payment_method_id is not null then
1017 --
1018 validate_dflt_payment_method
1019 (p_default_payment_method_id,
1020 p_validation_start_date,
1021 p_validation_end_date);
1022 --
1023 end if;
1024 --
1025 end validate_update_payroll;
1026 --
1027 -----------------------------------------------------------------------------
1028 -- Name --
1029 -- update_payroll --
1030 -- Purpose --
1031 -- Maintains payroll related tables on update of a payroll. --
1032 -- Arguments --
1033 -- See Below. --
1034 -- Notes --
1035 -- 1. If payroll name has changed then copy it to all payroll rows. --
1036 -- 2. If the number of years has changed then copy it to all payroll --
1037 -- rows. --
1038 -- 3. If the number of years has been increased then create more time --
1039 -- periods for the payroll. --
1040 -- 4. If the default payment method has chnaged then create an OPMU. --
1041 -----------------------------------------------------------------------------
1042 --
1043 procedure update_payroll
1044 (
1045 p_business_group_id number,
1046 p_payroll_id number,
1047 p_payroll_name varchar2,
1048 p_s_payroll_name varchar2,
1049 p_default_payment_method_id number,
1050 p_s_default_payment_method_id number,
1051 p_number_of_years number,
1052 p_s_number_of_years number,
1053 p_validation_start_date date,
1054 p_validation_end_date date
1055 ) is
1056 --
1057 begin
1058 --
1059 -- Copy new payroll_name to all rows for the payroll ie,. this should not be
1060 -- datetracked.
1061 if p_payroll_name <> p_s_payroll_name then
1062 --
1063 propagate_changes
1064 (p_payroll_id,
1065 p_payroll_name,
1066 null); -- number of years
1067 --
1068 end if;
1069 --
1070 -- Extend the number of payroll time periods if the number of years has been
1071 -- increased.
1072 if p_number_of_years > p_s_number_of_years then
1073 --
1074 -- copy new number_of_years to all rows for the payroll ie,. this should
1075 -- not be datetracked.
1076 propagate_changes
1077 (p_payroll_id,
1078 null, -- payroll name
1079 p_number_of_years);
1080 --
1081 hr_payrolls.create_payroll_proc_periods
1082 (p_payroll_id,
1083 null, -- last_update_date
1084 null, -- last_updated_by
1085 null, -- last_update_login
1086 null, -- created_by
1087 null); -- creation_date
1088 --
1089 end if;
1090 --
1091 -- If default payment method for the payroll has changed then create
1092 -- opmu to represent it.
1093 if p_default_payment_method_id <> nvl(p_s_default_payment_method_id,
1094 0) and
1095 p_default_payment_method_id is not null then
1096 --
1097 maintain_dflt_payment_method
1098 (p_payroll_id,
1099 p_default_payment_method_id,
1100 p_validation_start_date,
1101 p_validation_end_date);
1102 --
1103 end if;
1104 --
1105 end update_payroll;
1106 --
1107 -----------------------------------------------------------------------------
1108 -- Name --
1109 -- validate_delete_payroll --
1110 -- Purpose --
1111 -- Validates the deletion of a payroll. --
1112 -- Arguments --
1113 -- See Below. --
1114 -- Notes --
1118 -- 2. If the payroll record is being opened up then make sure that the --
1115 -- 1. Checks per_all_assignments_f, pay_element_links_f, --
1116 -- pay_exchange_rates_f,pay_payroll_actions, hr_assignment_sets, --
1117 -- pay_payroll_list, pay_security_payrolls, pay_message_lines. --
1119 -- default payment method on the last row is valid for the new lifetime --
1120 -- of the payroll.t method on the last row is valid for the new lifetime --
1121 -----------------------------------------------------------------------------
1122 --
1123 procedure validate_delete_payroll
1124 (
1125 p_payroll_id number,
1126 p_default_payment_method_id number,
1127 p_dt_delete_mode varchar2,
1128 p_validation_start_date date,
1129 p_validation_end_date date
1130 ) is
1131 --
1132 cursor csr_chk_assignment is
1133 select 'found'
1134 from per_all_assignments_f asg
1135 where asg.payroll_id = p_payroll_id
1136 and asg.effective_start_date <= p_validation_end_date
1137 and asg.effective_end_date >= p_validation_start_date;
1138 --
1139 cursor csr_chk_element_link is
1140 select 'found'
1141 from pay_element_links_f el
1142 where el.payroll_id = p_payroll_id
1143 and el.effective_start_date <= p_validation_end_date
1144 and el.effective_end_date >= p_validation_start_date;
1145
1146 cursor csr_chk_position is
1147 select 'found'
1148 from hr_all_positions_f po
1149 where po.pay_freq_payroll_id = p_payroll_id
1150 and po.effective_start_date <= p_validation_end_date
1151 and po.effective_end_date >= p_validation_start_date;
1152
1153 --
1154 -- commented out as pay_exchange rates is no longer supported
1155 -- it had been replaced by gl daily_rates
1156 --
1157 -- cursor csr_chk_exchange_rate is
1158 -- select 'found'
1159 -- from pay_exchange_rates_f exr
1160 -- where exr.payroll_id = p_payroll_id
1161 -- and exr.effective_start_date <= p_validation_end_date
1162 -- and exr.effective_end_date >= p_validation_start_date;
1163 --
1164 cursor csr_chk_payroll_action is
1165 select 'found'
1166 from pay_payroll_actions ppa
1167 where ppa.payroll_id = p_payroll_id;
1168 --
1169 cursor csr_chk_assignment_set is
1170 select 'found'
1171 from hr_assignment_sets a
1172 where a.payroll_id = p_payroll_id;
1173 --
1174 cursor csr_chk_security_payroll is
1175 select 'found'
1176 from pay_security_payrolls psp
1177 where psp.payroll_id = p_payroll_id;
1178 --
1179 cursor csr_chk_message_line is
1180 select 'found'
1181 from pay_message_lines pml
1182 where pml.payroll_id = p_payroll_id;
1183 --
1184 v_result varchar2(5);
1185 v_text varchar2(2000);
1186 NO_OTL_PACKAGE_FUNCTION exception;
1187 --
1188 pragma exception_init(NO_OTL_PACKAGE_FUNCTION,-6550);
1189 --
1190 begin
1191 --
1192 -- If default payment method is being extended then make sure that the opm
1193 -- exists for the duration of the default.
1194 if p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
1195 p_default_payment_method_id is not null then
1196 --
1197 validate_dflt_payment_method
1198 (p_default_payment_method_id,
1199 p_validation_start_date,
1200 p_validation_end_date);
1201 --
1202 end if;
1203 --
1204 -- Do validation checks on date effective children of payroll.
1205 if p_dt_delete_mode in ('ZAP','DELETE') then
1206 --
1207 open csr_chk_assignment;
1208 fetch csr_chk_assignment into v_result;
1209 if csr_chk_assignment%found then
1210 close csr_chk_assignment;
1211 hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
1212 hr_utility.raise_error;
1213 else
1214 close csr_chk_assignment;
1215 end if;
1216 --
1217 open csr_chk_element_link;
1218 fetch csr_chk_element_link into v_result;
1219 if csr_chk_element_link%found then
1220 close csr_chk_element_link;
1221 hr_utility.set_message(801, 'HR_6675_PAY_ELE');
1222 hr_utility.raise_error;
1223 else
1224 close csr_chk_element_link;
1225 end if;
1226
1227 open csr_chk_position;
1228 fetch csr_chk_position into v_result;
1229 if csr_chk_position%found then
1230 close csr_chk_position;
1231 hr_utility.set_message(800, 'HR_DEL_PAYROLL_POSITION_EXISTS');
1232 hr_utility.raise_error;
1233 else
1234 close csr_chk_position;
1235 end if;
1236
1237 end if;
1238 --
1239 -- Do validation checks on non date effective children of payroll.
1240 if p_dt_delete_mode = 'ZAP' then
1241 --
1242 open csr_chk_payroll_action;
1243 fetch csr_chk_payroll_action into v_result;
1244 if csr_chk_payroll_action%found then
1245 close csr_chk_payroll_action;
1246 hr_utility.set_message(801, 'HR_6488_PAY_DEL_ACTIONS');
1247 hr_utility.raise_error;
1248 else
1249 close csr_chk_payroll_action;
1250 end if;
1251 --
1252 open csr_chk_assignment_set;
1253 fetch csr_chk_assignment_set into v_result;
1254 if csr_chk_assignment_set%found then
1255 close csr_chk_assignment_set;
1256 hr_utility.set_message(801, 'HR_6489_PAY_DEL_ASS');
1257 hr_utility.raise_error;
1258 else
1259 close csr_chk_assignment_set;
1260 end if;
1261 --
1265 close csr_chk_security_payroll;
1262 open csr_chk_security_payroll;
1263 fetch csr_chk_security_payroll into v_result;
1264 if csr_chk_security_payroll%found then
1266 hr_utility.set_message(801, 'HR_6491_PAY_DEL_SEC_PAY');
1267 hr_utility.raise_error;
1268 else
1269 close csr_chk_security_payroll;
1270 end if;
1271 --
1272 open csr_chk_message_line;
1273 fetch csr_chk_message_line into v_result;
1274 if csr_chk_message_line%found then
1275 close csr_chk_message_line;
1276 hr_utility.set_message(801, 'HR_6731_PAY_DEL_MESS');
1277 hr_utility.raise_error;
1278 else
1279 close csr_chk_message_line;
1280 end if;
1281 --
1282 begin
1283 v_text := 'declare l_payroll_used boolean;
1284 begin
1285 l_payroll_used := hxc_resource_rules_utils.chk_criteria_exists
1286 (''PAYROLL'',:p_eligibility_criteria_id);
1287 if l_payroll_used then
1288 hr_utility.set_message(801, ''PAY_33198_PAYROLL_USED_IN_OTL'');
1289 hr_utility.raise_error;
1290 end if;
1291 end;' ;
1292
1293 execute immediate v_text using p_payroll_id ;
1294 exception
1295 when NO_OTL_PACKAGE_FUNCTION then
1296 null;
1297 end;
1298 --
1299 end if;
1300 --
1301 end validate_delete_payroll;
1302 --
1303 -----------------------------------------------------------------------------
1304 -- Name --
1305 -- delete_payroll --
1306 -- Purpose --
1307 -- Maintains payroll related tables on deletion of a payroll. --
1308 -- Arguments --
1309 -- See Below. --
1310 -- Notes --
1311 -- None --
1312 -----------------------------------------------------------------------------
1313 --
1314 procedure delete_payroll
1315 (
1316 p_payroll_id number,
1317 p_default_payment_method_id number,
1318 p_dt_delete_mode varchar2,
1319 p_validation_start_date date,
1320 p_validation_end_date date
1321 ) is
1322 --
1323 cursor csr_comment_rows_zap is
1324 select com.comment_id
1325 from pay_payrolls_f prl,
1326 hr_comments com
1327 where prl.payroll_id = p_payroll_id
1328 and com.comment_id = prl.comment_id
1329 for update of com.comment_id;
1330 --
1331 cursor csr_comment_rows_delete is
1332 select com.comment_id
1333 from pay_payrolls_f prl,
1334 hr_comments com
1335 where prl.payroll_id = p_payroll_id
1336 and prl.effective_start_date >= p_validation_start_date
1337 and com.comment_id = prl.comment_id
1338 and not exists
1339 (select null
1340 from pay_payrolls_f prl2
1341 where prl2.payroll_id = prl.payroll_id
1342 and prl2.effective_start_date < p_validation_start_date
1343 and prl2.comment_id = prl.comment_id)
1344 for update of com.comment_id;
1345
1346 cursor csr_period_end_date is
1347 select max(tpe.end_date), min(tpe.start_date)
1348 from per_time_periods tpe
1349 where tpe.payroll_id = p_payroll_id;
1350 --
1351 l_last_period_end_date date;
1352 l_first_period_start_date date;
1353 --
1354 begin
1355 --
1356 if p_dt_delete_mode = 'ZAP' then
1357 --
1358 delete from per_time_periods tpe
1359 where tpe.payroll_id = p_payroll_id;
1360 --
1361 delete from pay_org_pay_method_usages_f opu
1362 where opu.payroll_id = p_payroll_id;
1363 --
1364 delete from pay_payroll_gl_flex_maps gfm
1365 where gfm.payroll_id = p_payroll_id;
1366 --
1367 for v_com_rec in csr_comment_rows_zap loop
1368 delete from hr_comments
1369 where current of csr_comment_rows_zap;
1370 end loop;
1371 --
1372 -- Payroll Lists are created by default for all the security profiles
1373 -- within the business group when a payroll is created. Hence we can
1374 -- safely purge them.
1375 --
1376 hr_security.delete_payroll_from_list(p_payroll_id);
1377 --
1378 elsif p_dt_delete_mode = 'DELETE_NEXT_CHANGE' then
1379
1380 -- If default payment method is being extended then make sure that the
1381 -- opmu is extended to represent the default payment method.
1382 if p_default_payment_method_id is not null then
1383 --
1384 maintain_dflt_payment_method
1385 (p_payroll_id,
1386 p_default_payment_method_id,
1387 p_validation_start_date,
1388 p_validation_end_date);
1389 --
1390 end if;
1391 --
1392 -- Record is being opened up so need to recreate any time periods that
1393 -- were removed when the payroll was shut down.
1394 if p_validation_end_date = c_end_of_time then
1395 --
1396 -- Create payroll time periods based on the payroll definition.
1397 hr_payrolls.create_payroll_proc_periods
1398 (p_payroll_id,
1399 null, -- last_update_date
1400 null, -- last_updated_by
1401 null, -- last_update_login
1405 end if;
1402 null, -- created_by
1403 null); -- creation_date
1404 --
1406 --
1407 elsif p_dt_delete_mode = 'DELETE' then
1408 --
1409 -- All opmu's for a payroll must exist within the lifetime of the payroll
1410 -- so any opmus that exist outisde the new dates will be removed /
1411 -- shortened.
1412 delete from pay_org_pay_method_usages_f opu
1413 where opu.payroll_id = p_payroll_id
1414 and opu.effective_start_date >= p_validation_start_date;
1415 --
1416 update pay_org_pay_method_usages_f opu
1417 set opu.effective_end_date = p_validation_start_date - 1
1418 where opu.payroll_id = p_payroll_id
1419 and opu.effective_end_date >= p_validation_start_date;
1420 --
1421 -- Only time periods that fit completely within the lifetime of the
1422 -- payroll are valid.
1423 delete from per_time_periods tpe
1424 where tpe.payroll_id = p_payroll_id
1425 and tpe.end_date >= p_validation_start_date;
1426
1427 Open csr_period_end_date;
1428 Fetch csr_period_end_date into l_last_period_end_date,
1429 l_first_period_start_date;
1430 Close csr_period_end_date;
1431 --
1432 -- The number of years should be updated to the span of the payroll
1433 -- period.
1434 --
1435 if l_last_period_end_date is not null and
1436 l_first_period_start_date is not null
1437 then
1438 update pay_payrolls_f
1439 set number_of_years =
1440 round(months_between(l_last_period_end_date,l_first_period_start_date))/12
1441 where payroll_id = p_payroll_id;
1442 end if;
1443 --
1444 -- Remove any comments that only exist after the date on which the payroll
1445 -- is being closed down.
1446 for v_com_rec in csr_comment_rows_delete loop
1447 delete from hr_comments
1448 where current of csr_comment_rows_delete;
1449 end loop;
1450 --
1451 end if;
1452 --
1453 end delete_payroll;
1454 --
1455 -----------------------------------------------------------------------------
1456 -- Name --
1457 -- Insert_Row --
1458 -- Purpose --
1459 -- Table handler procedure that supports the insert of a payroll via the --
1460 -- Define Payroll form. --
1461 -- Arguments --
1462 -- See below. --
1463 -- Notes --
1464 -- --
1465 -- Additions --
1466 -- Added X_payslip_view_date_offset By Rajeesha Bug 4246280 --
1467 -----------------------------------------------------------------------------
1468 --
1469 PROCEDURE Insert_Row(X_Rowid IN out nocopy VARCHAR2,
1470 X_Payroll_Id IN out nocopy NUMBER,
1471 X_Effective_Start_Date DATE,
1472 X_Effective_End_Date DATE,
1473 X_Default_Payment_Method_Id NUMBER,
1474 X_Business_Group_Id NUMBER,
1475 X_Consolidation_Set_Id NUMBER,
1476 X_Cost_Allocation_Keyflex_Id NUMBER,
1477 X_Suspense_Account_Keyflex_Id NUMBER,
1478 X_Gl_Set_Of_Books_Id NUMBER,
1479 X_Soft_Coding_Keyflex_Id NUMBER,
1480 X_Period_Type VARCHAR2,
1481 X_Organization_Id NUMBER,
1482 X_Cut_Off_Date_Offset NUMBER,
1483 X_Direct_Deposit_Date_Offset NUMBER,
1484 X_First_Period_End_Date DATE,
1485 X_Negative_Pay_Allowed_Flag VARCHAR2,
1486 X_Number_Of_Years NUMBER,
1487 X_Pay_Advice_Date_Offset NUMBER,
1488 X_Pay_Date_Offset NUMBER,
1489 X_Payroll_Name VARCHAR2,
1490 X_Workload_Shifting_Level VARCHAR2,
1491 X_Comment_Id NUMBER,
1492 X_Attribute_Category VARCHAR2,
1493 X_Attribute1 VARCHAR2,
1494 X_Attribute2 VARCHAR2,
1495 X_Attribute3 VARCHAR2,
1496 X_Attribute4 VARCHAR2,
1497 X_Attribute5 VARCHAR2,
1498 X_Attribute6 VARCHAR2,
1499 X_Attribute7 VARCHAR2,
1500 X_Attribute8 VARCHAR2,
1501 X_Attribute9 VARCHAR2,
1502 X_Attribute10 VARCHAR2,
1503 X_Attribute11 VARCHAR2,
1504 X_Attribute12 VARCHAR2,
1505 X_Attribute13 VARCHAR2,
1506 X_Attribute14 VARCHAR2,
1510 X_Attribute18 VARCHAR2,
1507 X_Attribute15 VARCHAR2,
1508 X_Attribute16 VARCHAR2,
1509 X_Attribute17 VARCHAR2,
1511 X_Attribute19 VARCHAR2,
1512 X_Attribute20 VARCHAR2,
1513 -- Payroll Developer DF
1514 X_Prl_Information_Category VARCHAR2 DEFAULT NULL,
1515 X_Prl_Information1 VARCHAR2 DEFAULT NULL,
1516 X_Prl_Information2 VARCHAR2 DEFAULT NULL,
1517 X_Prl_Information3 VARCHAR2 DEFAULT NULL,
1518 X_Prl_Information4 VARCHAR2 DEFAULT NULL,
1519 X_Prl_Information5 VARCHAR2 DEFAULT NULL,
1520 X_Prl_Information6 VARCHAR2 DEFAULT NULL,
1521 X_Prl_Information7 VARCHAR2 DEFAULT NULL,
1522 X_Prl_Information8 VARCHAR2 DEFAULT NULL,
1523 X_Prl_Information9 VARCHAR2 DEFAULT NULL,
1524 X_Prl_Information10 VARCHAR2 DEFAULT NULL,
1525 X_Prl_Information11 VARCHAR2 DEFAULT NULL,
1526 X_Prl_Information12 VARCHAR2 DEFAULT NULL,
1527 X_Prl_Information13 VARCHAR2 DEFAULT NULL,
1528 X_Prl_Information14 VARCHAR2 DEFAULT NULL,
1529 X_Prl_Information15 VARCHAR2 DEFAULT NULL,
1530 X_Prl_Information16 VARCHAR2 DEFAULT NULL,
1531 X_Prl_Information17 VARCHAR2 DEFAULT NULL,
1532 X_Prl_Information18 VARCHAR2 DEFAULT NULL,
1533 X_Prl_Information19 VARCHAR2 DEFAULT NULL,
1534 X_Prl_Information20 VARCHAR2 DEFAULT NULL,
1535 X_Prl_Information21 VARCHAR2 DEFAULT NULL,
1536 X_Prl_Information22 VARCHAR2 DEFAULT NULL,
1537 X_Prl_Information23 VARCHAR2 DEFAULT NULL,
1538 X_Prl_Information24 VARCHAR2 DEFAULT NULL,
1539 X_Prl_Information25 VARCHAR2 DEFAULT NULL,
1540 X_Prl_Information26 VARCHAR2 DEFAULT NULL,
1541 X_Prl_Information27 VARCHAR2 DEFAULT NULL,
1542 X_Prl_Information28 VARCHAR2 DEFAULT NULL,
1543 X_Prl_Information29 VARCHAR2 DEFAULT NULL,
1544 X_Prl_Information30 VARCHAR2 DEFAULT NULL,
1545 -- Extra Columns
1546 X_Validation_Start_date DATE,
1547 X_Validation_End_date DATE,
1548 X_Arrears_Flag VARCHAR2,
1549 X_Multi_Assignments_Flag VARCHAR2 DEFAULT NULL,
1550 X_Period_Reset_Years VARCHAR2 DEFAULT NULL,
1551 X_payslip_view_date_offset NUMBER DEFAULT NULL
1552 ) IS
1553 --
1554 CURSOR C IS SELECT rowid FROM PAY_ALL_PAYROLLS_F
1555 WHERE payroll_id = X_Payroll_Id;
1556 --
1557 CURSOR C2 IS SELECT pay_payrolls_s.nextval FROM sys.dual;
1558 --
1559 l_midpoint_offset number := 0 ;
1560 --
1561 BEGIN
1562 --
1563 pay_payrolls_f_pkg.validate_insert_payroll
1564 (x_business_group_id,
1565 x_payroll_name,
1566 x_default_payment_method_id,
1567 x_validation_start_date,
1568 x_validation_end_date);
1569 --
1570 if (X_Payroll_Id is NULL) then
1571 OPEN C2;
1572 FETCH C2 INTO X_Payroll_Id;
1573 CLOSE C2;
1574 end if;
1575 --
1576 if ( X_period_type = 'Semi-Month' ) then
1577 l_midpoint_offset := -15 ;
1578 else
1579 l_midpoint_offset := 0 ;
1580 end if ;
1581 --
1582 INSERT INTO PAY_PAYROLLS_F
1583 (payroll_id,
1584 effective_start_date,
1585 effective_end_date,
1586 default_payment_method_id,
1587 business_group_id,
1588 consolidation_set_id,
1589 cost_allocation_keyflex_id,
1590 suspense_account_keyflex_id,
1591 gl_set_of_books_id,
1592 soft_coding_keyflex_id,
1593 period_type,
1594 organization_id,
1595 cut_off_date_offset,
1596 direct_deposit_date_offset,
1597 first_period_end_date,
1598 midpoint_offset,
1599 negative_pay_allowed_flag,
1600 number_of_years,
1601 pay_advice_date_offset,
1602 pay_date_offset,
1603 payroll_name,
1604 workload_shifting_level,
1605 comment_id,
1606 attribute_category,
1607 attribute1,
1608 attribute2,
1609 attribute3,
1610 attribute4,
1611 attribute5,
1612 attribute6,
1613 attribute7,
1614 attribute8,
1615 attribute9,
1616 attribute10,
1617 attribute11,
1618 attribute12,
1619 attribute13,
1620 attribute14,
1621 attribute15,
1622 attribute16,
1623 attribute17,
1624 attribute18,
1625 attribute19,
1626 attribute20,
1627 prl_information_category,
1628 prl_information1,
1629 prl_information2,
1630 prl_information3,
1631 prl_information4,
1632 prl_information5,
1633 prl_information6,
1634 prl_information7,
1638 prl_information11,
1635 prl_information8,
1636 prl_information9,
1637 prl_information10,
1639 prl_information12,
1640 prl_information13,
1641 prl_information14,
1642 prl_information15,
1643 prl_information16,
1644 prl_information17,
1645 prl_information18,
1646 prl_information19,
1647 prl_information20,
1648 prl_information21,
1649 prl_information22,
1650 prl_information23,
1651 prl_information24,
1652 prl_information25,
1653 prl_information26,
1654 prl_information27,
1655 prl_information28,
1656 prl_information29,
1657 prl_information30,
1658 arrears_flag,
1659 multi_assignments_flag,
1660 period_reset_years,
1661 payslip_view_date_offset)
1662 VALUES
1663 (X_Payroll_Id,
1664 X_Effective_Start_Date,
1665 X_Effective_End_Date,
1666 X_Default_Payment_Method_Id,
1667 X_Business_Group_Id,
1668 X_Consolidation_Set_Id,
1669 X_Cost_Allocation_Keyflex_Id,
1670 X_Suspense_Account_Keyflex_Id,
1671 X_Gl_Set_Of_Books_Id,
1672 X_Soft_Coding_Keyflex_Id,
1673 X_Period_Type,
1674 X_Organization_Id,
1675 X_Cut_Off_Date_Offset,
1676 X_Direct_Deposit_Date_Offset,
1677 X_First_Period_End_Date,
1678 l_midpoint_offset,
1679 X_Negative_Pay_Allowed_Flag,
1680 X_Number_Of_Years,
1681 X_Pay_Advice_Date_Offset,
1682 X_Pay_Date_Offset,
1683 X_Payroll_Name,
1684 X_Workload_Shifting_Level,
1685 X_Comment_Id,
1686 X_Attribute_Category,
1687 X_Attribute1,
1688 X_Attribute2,
1689 X_Attribute3,
1690 X_Attribute4,
1691 X_Attribute5,
1692 X_Attribute6,
1693 X_Attribute7,
1694 X_Attribute8,
1695 X_Attribute9,
1696 X_Attribute10,
1697 X_Attribute11,
1698 X_Attribute12,
1699 X_Attribute13,
1700 X_Attribute14,
1701 X_Attribute15,
1702 X_Attribute16,
1703 X_Attribute17,
1704 X_Attribute18,
1705 X_Attribute19,
1706 X_Attribute20,
1707 X_Prl_Information_Category,
1708 X_Prl_Information1,
1709 X_Prl_Information2,
1710 X_Prl_Information3,
1711 X_Prl_Information4,
1712 X_Prl_Information5,
1713 X_Prl_Information6,
1714 X_Prl_Information7,
1715 X_Prl_Information8,
1716 X_Prl_Information9,
1717 X_Prl_Information10,
1718 X_Prl_Information11,
1719 X_Prl_Information12,
1720 X_Prl_Information13,
1721 X_Prl_Information14,
1722 X_Prl_Information15,
1723 X_Prl_Information16,
1724 X_Prl_Information17,
1725 X_Prl_Information18,
1726 X_Prl_Information19,
1727 X_Prl_Information20,
1728 X_Prl_Information21,
1729 X_Prl_Information22,
1730 X_Prl_Information23,
1731 X_Prl_Information24,
1732 X_Prl_Information25,
1733 X_Prl_Information26,
1734 X_Prl_Information27,
1735 X_Prl_Information28,
1736 X_Prl_Information29,
1737 X_Prl_Information30,
1738 X_Arrears_Flag,
1739 X_Multi_Assignments_Flag,
1740 X_Period_Reset_Years,
1741 X_payslip_view_date_offset);
1742 --
1743 OPEN C;
1744 FETCH C INTO X_Rowid;
1745 if (C%NOTFOUND) then
1746 CLOSE C;
1747 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1748 hr_utility.set_message_token('PROCEDURE',
1749 'pay_payrolls_f_pkg.insert_row');
1750 hr_utility.set_message_token('STEP','1');
1751 hr_utility.raise_error;
1752 end if;
1753 CLOSE C;
1754 --
1755 -- Code added as part of bug fix 2593982
1756 -- Code to view the payrolls which were initially not visible in secured
1757 -- responsibility
1758
1759 hr_security_internal.populate_new_payroll
1760 (X_Business_Group_Id
1761 ,X_Payroll_Id
1762 );
1763 --
1764 -- Bug 3596436
1765 -- Add newly created payroll to the cache so that it is available
1766 -- for the rest of session
1767 --
1768 hr_security.add_payroll(x_payroll_id);
1769 --
1770 pay_payrolls_f_pkg.insert_payroll
1771 (x_payroll_id,
1772 x_default_payment_method_id,
1773 x_validation_start_date,
1774 x_validation_end_date);
1775 --
1776 END Insert_Row;
1777 --
1778 --
1779 -----------------------------------------------------------------------------
1780 -- Name --
1781 -- Insert_Row --
1782 -- Purpose --
1783 -- Table handler procedure that supports the insert of a payroll via the --
1784 -- Define Payroll form. --
1785 -- Arguments --
1786 -- See below. --
1787 -- Notes --
1788 -- --
1789 -- Additions --
1790 -- Added X_payslip_view_date_offset By Rajeesha Bug 4246280 --
1791 -- Added the Overloaded procedure to call the API 5144323 --
1792 -----------------------------------------------------------------------------
1793 --
1794 PROCEDURE Insert_Row(X_Rowid IN out nocopy VARCHAR2,
1798 X_Consolidation_Set_Id NUMBER,
1795 X_Payroll_Id IN out nocopy NUMBER,
1796 X_Default_Payment_Method_Id NUMBER,
1797 X_Business_Group_Id NUMBER,
1799 X_Cost_Allocation_Keyflex_Id NUMBER,
1800 X_Suspense_Account_Keyflex_Id NUMBER,
1801 X_Gl_Set_Of_Books_Id NUMBER,
1802 X_Soft_Coding_Keyflex_Id NUMBER,
1803 X_Period_Type VARCHAR2,
1804 X_Organization_Id NUMBER,
1805 X_Cut_Off_Date_Offset NUMBER,
1806 X_Direct_Deposit_Date_Offset NUMBER,
1807 X_First_Period_End_Date DATE,
1808 X_Negative_Pay_Allowed_Flag VARCHAR2,
1809 X_Number_Of_Years NUMBER,
1810 X_Pay_Advice_Date_Offset NUMBER,
1811 X_Pay_Date_Offset NUMBER,
1812 X_Payroll_Name VARCHAR2,
1813 X_Workload_Shifting_Level VARCHAR2,
1814 X_Attribute_Category VARCHAR2,
1815 X_Attribute1 VARCHAR2,
1816 X_Attribute2 VARCHAR2,
1817 X_Attribute3 VARCHAR2,
1818 X_Attribute4 VARCHAR2,
1819 X_Attribute5 VARCHAR2,
1820 X_Attribute6 VARCHAR2,
1821 X_Attribute7 VARCHAR2,
1822 X_Attribute8 VARCHAR2,
1823 X_Attribute9 VARCHAR2,
1824 X_Attribute10 VARCHAR2,
1825 X_Attribute11 VARCHAR2,
1826 X_Attribute12 VARCHAR2,
1827 X_Attribute13 VARCHAR2,
1828 X_Attribute14 VARCHAR2,
1829 X_Attribute15 VARCHAR2,
1830 X_Attribute16 VARCHAR2,
1831 X_Attribute17 VARCHAR2,
1832 X_Attribute18 VARCHAR2,
1833 X_Attribute19 VARCHAR2,
1834 X_Attribute20 VARCHAR2,
1835 -- Payroll Developer DF
1836 X_Prl_Information_Category VARCHAR2 DEFAULT NULL,
1837 X_Prl_Information1 VARCHAR2 DEFAULT NULL,
1838 X_Prl_Information2 VARCHAR2 DEFAULT NULL,
1839 X_Prl_Information3 VARCHAR2 DEFAULT NULL,
1840 X_Prl_Information4 VARCHAR2 DEFAULT NULL,
1841 X_Prl_Information5 VARCHAR2 DEFAULT NULL,
1842 X_Prl_Information6 VARCHAR2 DEFAULT NULL,
1843 X_Prl_Information7 VARCHAR2 DEFAULT NULL,
1844 X_Prl_Information8 VARCHAR2 DEFAULT NULL,
1845 X_Prl_Information9 VARCHAR2 DEFAULT NULL,
1846 X_Prl_Information10 VARCHAR2 DEFAULT NULL,
1847 X_Prl_Information11 VARCHAR2 DEFAULT NULL,
1848 X_Prl_Information12 VARCHAR2 DEFAULT NULL,
1849 X_Prl_Information13 VARCHAR2 DEFAULT NULL,
1850 X_Prl_Information14 VARCHAR2 DEFAULT NULL,
1851 X_Prl_Information15 VARCHAR2 DEFAULT NULL,
1852 X_Prl_Information16 VARCHAR2 DEFAULT NULL,
1853 X_Prl_Information17 VARCHAR2 DEFAULT NULL,
1854 X_Prl_Information18 VARCHAR2 DEFAULT NULL,
1855 X_Prl_Information19 VARCHAR2 DEFAULT NULL,
1856 X_Prl_Information20 VARCHAR2 DEFAULT NULL,
1857 X_Prl_Information21 VARCHAR2 DEFAULT NULL,
1858 X_Prl_Information22 VARCHAR2 DEFAULT NULL,
1859 X_Prl_Information23 VARCHAR2 DEFAULT NULL,
1860 X_Prl_Information24 VARCHAR2 DEFAULT NULL,
1861 X_Prl_Information25 VARCHAR2 DEFAULT NULL,
1862 X_Prl_Information26 VARCHAR2 DEFAULT NULL,
1863 X_Prl_Information27 VARCHAR2 DEFAULT NULL,
1864 X_Prl_Information28 VARCHAR2 DEFAULT NULL,
1865 X_Prl_Information29 VARCHAR2 DEFAULT NULL,
1866 X_Prl_Information30 VARCHAR2 DEFAULT NULL,
1867 -- Extra Columns
1868 X_Validation_Start_date DATE,
1869 X_Validation_End_date DATE,
1870 X_Arrears_Flag VARCHAR2,
1871 X_Multi_Assignments_Flag VARCHAR2 DEFAULT NULL,
1872 X_Period_Reset_Years VARCHAR2 DEFAULT NULL,
1873 X_payslip_view_date_offset NUMBER DEFAULT NULL
1874 --bug 5609830 / 5144323 TEST starts
1875 ,X_Effective_Date DATE --new
1879 ,X_Effective_End_Date OUT nocopy DATE --out type added
1876 ,X_payroll_type VARCHAR2 DEFAULT NULL --new
1877 ,X_comments VARCHAR2 DEFAULT NULL --new
1878 ,X_Effective_Start_Date OUT nocopy DATE --out type added
1880 ,X_Comment_Id OUT nocopy NUMBER --out type added
1881 --bug 5609830 / 5144323 TEST ends
1882 ) IS
1883 --
1884 CURSOR C IS SELECT rowid, comment_id FROM PAY_ALL_PAYROLLS_F
1885 WHERE payroll_id = X_Payroll_Id;
1886 --
1887 l_midpoint_offset number := 0 ;
1888 --
1889 l_payroll_id number;
1890 l_org_pay_method_usage_id number;
1891 l_prl_object_version_number number;
1892 l_opm_object_version_number number;
1893 l_opm_effective_start_date date;
1894 l_opm_effective_end_date date;
1895
1896 l_cost_allocation_keyflex_id PAY_ALL_PAYROLLS_F.COST_ALLOCATION_KEYFLEX_ID%TYPE ;
1897 l_suspense_account_keyflex_id PAY_ALL_PAYROLLS_F.SUSPENSE_ACCOUNT_KEYFLEX_ID%TYPE ;
1898 l_soft_coding_keyflex_id PAY_ALL_PAYROLLS_F.SOFT_CODING_KEYFLEX_ID%TYPE ;
1899
1900 l_cost_concat_segments PAY_COST_ALLOCATION_KEYFLEX.concatenated_segments%TYPE ;
1901 l_susp_concat_segments PAY_COST_ALLOCATION_KEYFLEX.concatenated_segments%TYPE ;
1902 l_scl_concat_segments HR_SOFT_CODING_KEYFLEX.concatenated_segments%TYPE;
1903 --
1904 BEGIN
1905 --
1906 pay_payrolls_f_pkg.validate_insert_payroll
1907 (x_business_group_id,
1908 x_payroll_name,
1909 x_default_payment_method_id,
1910 x_validation_start_date,
1911 x_validation_end_date);
1912
1913 if ( X_period_type = 'Semi-Month' ) then
1914 l_midpoint_offset := -15 ;
1915 else
1916 l_midpoint_offset := 0 ;
1917 end if ;
1918 --
1919
1920 pay_payroll_api.create_payroll(
1921 p_validate => FALSE
1922 ,p_effective_date => X_Effective_Date
1923 ,p_payroll_name => X_Payroll_Name
1924 ,p_consolidation_set_id => X_Consolidation_Set_Id
1925 ,p_period_type => X_Period_Type
1926 ,p_first_period_end_date => X_First_Period_End_Date
1927 ,p_number_of_years => X_Number_Of_Years
1928 ,p_payroll_type => X_payroll_type
1929 ,p_pay_date_offset => X_Pay_Date_Offset
1930 ,p_direct_deposit_date_offset => X_Direct_Deposit_Date_Offset
1931 ,p_pay_advice_date_offset => X_Pay_Advice_Date_Offset
1932 ,p_cut_off_date_offset => X_Cut_Off_Date_Offset
1933 ,p_midpoint_offset => l_midpoint_offset
1934 ,p_default_payment_method_id => X_Default_Payment_Method_Id
1935 ,p_cost_alloc_keyflex_id_in => X_Cost_Allocation_Keyflex_Id
1936 ,p_susp_account_keyflex_id_in => X_Suspense_Account_Keyflex_Id
1937 ,p_negative_pay_allowed_flag => X_Negative_Pay_Allowed_Flag
1938 ,p_gl_set_of_books_id => X_Gl_Set_Of_Books_Id
1939 ,p_soft_coding_keyflex_id_in => X_Soft_Coding_Keyflex_Id
1940 ,p_comments => X_comments
1941 ,p_attribute_category => X_Attribute_Category
1942 ,p_attribute1 => X_attribute1
1943 ,p_attribute2 => X_attribute2
1944 ,p_attribute3 => X_attribute3
1945 ,p_attribute4 => X_attribute4
1946 ,p_attribute5 => X_attribute5
1947 ,p_attribute6 => X_attribute6
1948 ,p_attribute7 => X_attribute7
1949 ,p_attribute8 => X_attribute8
1950 ,p_attribute9 => X_attribute9
1951 ,p_attribute10 => X_attribute10
1952 ,p_attribute11 => X_attribute11
1953 ,p_attribute12 => X_attribute12
1954 ,p_attribute13 => X_attribute13
1955 ,p_attribute14 => X_attribute14
1956 ,p_attribute15 => X_attribute15
1957 ,p_attribute16 => X_attribute16
1958 ,p_attribute17 => X_attribute17
1959 ,p_attribute18 => X_attribute18
1960 ,p_attribute19 => X_attribute19
1961 ,p_attribute20 => X_attribute20
1962 ,p_arrears_flag => X_Arrears_Flag
1963 ,p_period_reset_years => X_Period_Reset_Years
1964 ,p_multi_assignments_flag => X_Multi_Assignments_Flag
1965 ,p_organization_id => X_Organization_Id
1966 ,p_prl_information1 => X_prl_information1
1967 ,p_prl_information2 => X_prl_information2
1968 ,p_prl_information3 => X_prl_information3
1969 ,p_prl_information4 => X_prl_information4
1970 ,p_prl_information5 => X_prl_information5
1971 ,p_prl_information6 => X_prl_information6
1972 ,p_prl_information7 => X_prl_information7
1973 ,p_prl_information8 => X_prl_information8
1974 ,p_prl_information9 => X_prl_information9
1975 ,p_prl_information10 => X_prl_information10
1976 ,p_prl_information11 => X_prl_information11
1977 ,p_prl_information12 => X_prl_information12
1978 ,p_prl_information13 => X_prl_information13
1979 ,p_prl_information14 => X_prl_information14
1980 ,p_prl_information15 => X_prl_information15
1981 ,p_prl_information16 => X_prl_information16
1982 ,p_prl_information17 => X_prl_information17
1983 ,p_prl_information18 => X_prl_information18
1984 ,p_prl_information19 => X_prl_information19
1985 ,p_prl_information20 => X_prl_information20
1986 ,p_prl_information21 => X_prl_information21
1987 ,p_prl_information22 => X_prl_information22
1988 ,p_prl_information23 => X_prl_information23
1989 ,p_prl_information24 => X_prl_information24
1990 ,p_prl_information25 => X_prl_information25
1991 ,p_prl_information26 => X_prl_information26
1992 ,p_prl_information27 => X_prl_information27
1996 --
1993 ,p_prl_information28 => X_prl_information28
1994 ,p_prl_information29 => X_prl_information29
1995 ,p_prl_information30 => X_prl_information30
1997 ,p_workload_shifting_level => X_Workload_Shifting_Level
1998 ,p_payslip_view_date_offset => X_payslip_view_date_offset
1999 --
2000 ,p_payroll_id => l_payroll_id
2001 ,p_org_pay_method_usage_id => l_org_pay_method_usage_id
2002
2003 ,p_prl_object_version_number => l_prl_object_version_number
2004 ,p_opm_object_version_number => l_opm_object_version_number
2005
2006 ,p_prl_effective_start_date => X_effective_start_date
2007 ,p_prl_effective_end_date => X_effective_end_date
2008 ,p_opm_effective_start_date => l_opm_effective_start_date
2009 ,p_opm_effective_end_date => l_opm_effective_end_date
2010 ,p_comment_id => X_comment_id
2011 --
2012 ,p_cost_alloc_keyflex_id_out => l_cost_allocation_keyflex_id
2013 ,p_susp_account_keyflex_id_out => l_suspense_account_keyflex_id
2014 ,p_soft_coding_keyflex_id_out => l_soft_coding_keyflex_id
2015 ,p_cost_concat_segments_out => l_cost_concat_segments
2016 ,p_susp_concat_segments_out => l_susp_concat_segments
2017 ,p_scl_concat_segments_out => l_scl_concat_segments
2018 --
2019 );
2020
2021 X_payroll_id := l_payroll_id;
2022 --
2023 -- Comment_id not returned from create_payroll_api
2024 OPEN C;
2025 FETCH C INTO X_Rowid, X_comment_id;
2026 if (C%NOTFOUND) then
2027 CLOSE C;
2028 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2029 hr_utility.set_message_token('PROCEDURE',
2030 'pay_payrolls_f_pkg.insert_row');
2031 hr_utility.set_message_token('STEP','1');
2032 hr_utility.raise_error;
2033 end if;
2034 CLOSE C;
2035 --
2036 -- Code added as part of bug fix 2593982
2037 -- Code to view the payrolls which were initially not visible in secured
2038 -- responsibility
2039
2040 hr_security_internal.populate_new_payroll
2041 (X_Business_Group_Id
2042 ,X_Payroll_Id
2043 );
2044 --
2045 -- Bug 3596436
2046 -- Add newly created payroll to the cache so that it is available
2047 -- for the rest of session
2048 --
2049 hr_security.add_payroll(x_payroll_id);
2050 --
2051 pay_payrolls_f_pkg.insert_payroll
2052 (x_payroll_id,
2053 x_default_payment_method_id,
2054 x_validation_start_date,
2055 x_validation_end_date);
2056 --
2057 END Insert_Row;
2058 --
2059 -----------------------------------------------------------------------------
2060 -- Name --
2061 -- Lock_Row --
2062 -- Purpose --
2063 -- Table handler procedure that supports the insert , update and delete --
2064 -- of a formula by applying a lock on a payroll in the Define Payroll --
2065 -- form. --
2066 -- Arguments --
2067 -- See below. --
2068 -- Notes --
2069 -- None. --
2070 -- Additions --
2071 -- Added X_payslip_view_date_offset By Rajeesha Bug 4246280 --
2072 -----------------------------------------------------------------------------
2073 --
2074 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
2075 X_Payroll_Id NUMBER,
2076 X_Effective_Start_Date DATE,
2077 X_Effective_End_Date DATE,
2078 X_Default_Payment_Method_Id NUMBER,
2079 X_Business_Group_Id NUMBER,
2080 X_Consolidation_Set_Id NUMBER,
2081 X_Cost_Allocation_Keyflex_Id NUMBER,
2082 X_Suspense_Account_Keyflex_Id NUMBER,
2083 X_Gl_Set_Of_Books_Id NUMBER,
2084 X_Soft_Coding_Keyflex_Id NUMBER,
2085 X_Period_Type VARCHAR2,
2086 X_Organization_Id NUMBER,
2087 X_Cut_Off_Date_Offset NUMBER,
2088 X_Direct_Deposit_Date_Offset NUMBER,
2089 X_First_Period_End_Date DATE,
2090 X_Negative_Pay_Allowed_Flag VARCHAR2,
2091 X_Number_Of_Years NUMBER,
2092 X_Pay_Advice_Date_Offset NUMBER,
2093 X_Pay_Date_Offset NUMBER,
2094 X_Payroll_Name VARCHAR2,
2095 X_Workload_Shifting_Level VARCHAR2,
2096 X_Comment_Id NUMBER,
2097 X_Attribute_Category VARCHAR2,
2098 X_Attribute1 VARCHAR2,
2099 X_Attribute2 VARCHAR2,
2100 X_Attribute3 VARCHAR2,
2101 X_Attribute4 VARCHAR2,
2102 X_Attribute5 VARCHAR2,
2103 X_Attribute6 VARCHAR2,
2107 X_Attribute10 VARCHAR2,
2104 X_Attribute7 VARCHAR2,
2105 X_Attribute8 VARCHAR2,
2106 X_Attribute9 VARCHAR2,
2108 X_Attribute11 VARCHAR2,
2109 X_Attribute12 VARCHAR2,
2110 X_Attribute13 VARCHAR2,
2111 X_Attribute14 VARCHAR2,
2112 X_Attribute15 VARCHAR2,
2113 X_Attribute16 VARCHAR2,
2114 X_Attribute17 VARCHAR2,
2115 X_Attribute18 VARCHAR2,
2116 X_Attribute19 VARCHAR2,
2117 X_Attribute20 VARCHAR2,
2118 -- Payroll Developer DF
2119 X_Prl_Information_Category VARCHAR2 DEFAULT NULL,
2120 X_Prl_Information1 VARCHAR2 DEFAULT NULL,
2121 X_Prl_Information2 VARCHAR2 DEFAULT NULL,
2122 X_Prl_Information3 VARCHAR2 DEFAULT NULL,
2123 X_Prl_Information4 VARCHAR2 DEFAULT NULL,
2124 X_Prl_Information5 VARCHAR2 DEFAULT NULL,
2125 X_Prl_Information6 VARCHAR2 DEFAULT NULL,
2126 X_Prl_Information7 VARCHAR2 DEFAULT NULL,
2127 X_Prl_Information8 VARCHAR2 DEFAULT NULL,
2128 X_Prl_Information9 VARCHAR2 DEFAULT NULL,
2129 X_Prl_Information10 VARCHAR2 DEFAULT NULL,
2130 X_Prl_Information11 VARCHAR2 DEFAULT NULL,
2131 X_Prl_Information12 VARCHAR2 DEFAULT NULL,
2132 X_Prl_Information13 VARCHAR2 DEFAULT NULL,
2133 X_Prl_Information14 VARCHAR2 DEFAULT NULL,
2134 X_Prl_Information15 VARCHAR2 DEFAULT NULL,
2135 X_Prl_Information16 VARCHAR2 DEFAULT NULL,
2136 X_Prl_Information17 VARCHAR2 DEFAULT NULL,
2137 X_Prl_Information18 VARCHAR2 DEFAULT NULL,
2138 X_Prl_Information19 VARCHAR2 DEFAULT NULL,
2139 X_Prl_Information20 VARCHAR2 DEFAULT NULL,
2140 X_Prl_Information21 VARCHAR2 DEFAULT NULL,
2141 X_Prl_Information22 VARCHAR2 DEFAULT NULL,
2142 X_Prl_Information23 VARCHAR2 DEFAULT NULL,
2143 X_Prl_Information24 VARCHAR2 DEFAULT NULL,
2144 X_Prl_Information25 VARCHAR2 DEFAULT NULL,
2145 X_Prl_Information26 VARCHAR2 DEFAULT NULL,
2146 X_Prl_Information27 VARCHAR2 DEFAULT NULL,
2147 X_Prl_Information28 VARCHAR2 DEFAULT NULL,
2148 X_Prl_Information29 VARCHAR2 DEFAULT NULL,
2149 X_Prl_Information30 VARCHAR2 DEFAULT NULL,
2150 --
2151 X_Arrears_Flag VARCHAR2,
2152 X_Multi_Assignments_Flag VARCHAR2 DEFAULT NULL,
2153 X_Period_Reset_Years VARCHAR2 DEFAULT NULL,
2154 X_payslip_view_date_offset NUMBER DEFAULT NULL
2155 ) IS
2156
2157 --
2158 CURSOR C IS SELECT * FROM PAY_PAYROLLS_F
2159 WHERE rowid = X_Rowid FOR UPDATE of Payroll_Id NOWAIT;
2160 --
2161 Recinfo C%ROWTYPE;
2162 --
2163 BEGIN
2164 --
2165 OPEN C;
2166 FETCH C INTO Recinfo;
2167 if (C%NOTFOUND) then
2168 CLOSE C;
2169 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2170 hr_utility.set_message_token('PROCEDURE',
2171 'pay_payrolls_f_pkg.lock_row');
2172 hr_utility.set_message_token('STEP','1');
2173 hr_utility.raise_error;
2174 end if;
2175 CLOSE C;
2176 --
2177 -- Remove trailing spaces.
2178 Recinfo.period_type := rtrim(Recinfo.period_type);
2179 Recinfo.negative_pay_allowed_flag :=
2180 rtrim(Recinfo.negative_pay_allowed_flag);
2181 Recinfo.payroll_name := rtrim(Recinfo.payroll_name);
2182 Recinfo.workload_shifting_level := rtrim(Recinfo.workload_shifting_level);
2183 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
2184 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
2185 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
2186 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
2187 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
2188 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
2189 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
2190 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
2191 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
2192 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
2193 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
2194 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
2195 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
2196 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
2197 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
2198 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
2199 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
2200 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
2201 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
2202 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
2206 Recinfo.prl_information1 := rtrim(Recinfo.prl_information1);
2203 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
2204 --
2205 Recinfo.prl_information_category := rtrim(Recinfo.prl_information_category);
2207 Recinfo.prl_information2 := rtrim(Recinfo.prl_information2);
2208 Recinfo.prl_information3 := rtrim(Recinfo.prl_information3);
2209 Recinfo.prl_information4 := rtrim(Recinfo.prl_information4);
2210 Recinfo.prl_information5 := rtrim(Recinfo.prl_information5);
2211 Recinfo.prl_information6 := rtrim(Recinfo.prl_information6);
2212 Recinfo.prl_information7 := rtrim(Recinfo.prl_information7);
2213 Recinfo.prl_information8 := rtrim(Recinfo.prl_information8);
2214 Recinfo.prl_information9 := rtrim(Recinfo.prl_information9);
2215 Recinfo.prl_information10 := rtrim(Recinfo.prl_information10);
2216 Recinfo.prl_information11 := rtrim(Recinfo.prl_information11);
2217 Recinfo.prl_information12 := rtrim(Recinfo.prl_information12);
2218 Recinfo.prl_information13 := rtrim(Recinfo.prl_information13);
2219 Recinfo.prl_information14 := rtrim(Recinfo.prl_information14);
2220 Recinfo.prl_information15 := rtrim(Recinfo.prl_information15);
2221 Recinfo.prl_information16 := rtrim(Recinfo.prl_information16);
2222 Recinfo.prl_information17 := rtrim(Recinfo.prl_information17);
2223 Recinfo.prl_information18 := rtrim(Recinfo.prl_information18);
2224 Recinfo.prl_information19 := rtrim(Recinfo.prl_information19);
2225 Recinfo.prl_information20 := rtrim(Recinfo.prl_information20);
2226 Recinfo.prl_information21 := rtrim(Recinfo.prl_information21);
2227 Recinfo.prl_information22 := rtrim(Recinfo.prl_information22);
2228 Recinfo.prl_information23 := rtrim(Recinfo.prl_information23);
2229 Recinfo.prl_information24 := rtrim(Recinfo.prl_information24);
2230 Recinfo.prl_information25 := rtrim(Recinfo.prl_information25);
2231 Recinfo.prl_information26 := rtrim(Recinfo.prl_information26);
2232 Recinfo.prl_information27 := rtrim(Recinfo.prl_information27);
2233 Recinfo.prl_information28 := rtrim(Recinfo.prl_information28);
2234 Recinfo.prl_information29 := rtrim(Recinfo.prl_information29);
2235 Recinfo.prl_information30 := rtrim(Recinfo.prl_information30);
2236 --
2237 Recinfo.arrears_flag:= rtrim(Recinfo.arrears_flag);
2238 Recinfo.multi_assignments_flag:= rtrim(Recinfo.multi_assignments_flag);
2239 --
2240 if ( ( (Recinfo.payroll_id = X_Payroll_Id)
2241 OR ( (Recinfo.payroll_id IS NULL)
2242 AND (X_Payroll_Id IS NULL)))
2243 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
2244 OR ( (Recinfo.effective_start_date IS NULL)
2245 AND (X_Effective_Start_Date IS NULL)))
2246 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
2247 OR ( (Recinfo.effective_end_date IS NULL)
2248 AND (X_Effective_End_Date IS NULL)))
2249 AND ( (Recinfo.default_payment_method_id = X_Default_Payment_Method_Id)
2250 OR ( (Recinfo.default_payment_method_id IS NULL)
2251 AND (X_Default_Payment_Method_Id IS NULL)))
2252 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
2253 OR ( (Recinfo.business_group_id IS NULL)
2254 AND (X_Business_Group_Id IS NULL)))
2255 AND ( (Recinfo.consolidation_set_id = X_Consolidation_Set_Id)
2256 OR ( (Recinfo.consolidation_set_id IS NULL)
2257 AND (X_Consolidation_Set_Id IS NULL)))
2258 AND ( (Recinfo.cost_allocation_keyflex_id = X_Cost_Allocation_Keyflex_Id)
2259 OR ( (Recinfo.cost_allocation_keyflex_id IS NULL)
2260 AND (X_Cost_Allocation_Keyflex_Id IS NULL)))
2261 AND ( (Recinfo.suspense_account_keyflex_id = X_Suspense_Account_Keyflex_Id)
2262 OR ( (Recinfo.suspense_account_keyflex_id IS NULL)
2263 AND (X_Suspense_Account_Keyflex_Id IS NULL)))
2264 AND ( (Recinfo.gl_set_of_books_id = X_Gl_Set_Of_Books_Id)
2265 OR ( (Recinfo.gl_set_of_books_id IS NULL)
2266 AND (X_Gl_Set_Of_Books_Id IS NULL)))
2267 AND ( (Recinfo.soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id)
2268 OR ( (Recinfo.soft_coding_keyflex_id IS NULL)
2269 AND (X_Soft_Coding_Keyflex_Id IS NULL)))
2270 AND ( (Recinfo.period_type = X_Period_Type)
2271 OR ( (Recinfo.period_type IS NULL)
2272 AND (X_Period_Type IS NULL)))
2273 AND ( (Recinfo.organization_id = X_Organization_Id)
2274 OR ( (Recinfo.organization_id IS NULL)
2275 AND (X_Organization_Id IS NULL)))
2276 AND ( (Recinfo.cut_off_date_offset = X_Cut_Off_Date_Offset)
2277 OR ( (Recinfo.cut_off_date_offset IS NULL)
2278 AND (X_Cut_Off_Date_Offset IS NULL)))
2279 AND ( (Recinfo.direct_deposit_date_offset = X_Direct_Deposit_Date_Offset)
2280 OR ( (Recinfo.direct_deposit_date_offset IS NULL)
2281 AND (X_Direct_Deposit_Date_Offset IS NULL)))
2282 AND ( (Recinfo.first_period_end_date = X_First_Period_End_Date)
2283 OR ( (Recinfo.first_period_end_date IS NULL)
2284 AND (X_First_Period_End_Date IS NULL)))
2285 AND ( (Recinfo.negative_pay_allowed_flag = X_Negative_Pay_Allowed_Flag)
2286 OR ( (Recinfo.negative_pay_allowed_flag IS NULL)
2287 AND (X_Negative_Pay_Allowed_Flag IS NULL)))
2288 AND ( (Recinfo.number_of_years = X_Number_Of_Years)
2289 OR ( (Recinfo.number_of_years IS NULL)
2290 AND (X_Number_Of_Years IS NULL)))
2291 AND ( (Recinfo.pay_advice_date_offset = X_Pay_Advice_Date_Offset)
2292 OR ( (Recinfo.pay_advice_date_offset IS NULL)
2293 AND (X_Pay_Advice_Date_Offset IS NULL)))
2294 AND ( (Recinfo.pay_date_offset = X_Pay_Date_Offset)
2295 OR ( (Recinfo.pay_date_offset IS NULL)
2296 AND (X_Pay_Date_Offset IS NULL)))
2300 AND ( (Recinfo.workload_shifting_level = X_Workload_Shifting_Level)
2297 AND ( (Recinfo.payroll_name = X_Payroll_Name)
2298 OR ( (Recinfo.payroll_name IS NULL)
2299 AND (X_Payroll_Name IS NULL)))
2301 OR ( (Recinfo.workload_shifting_level IS NULL)
2302 AND (X_Workload_Shifting_Level IS NULL)))
2303 AND ( (Recinfo.comment_id = X_Comment_Id)
2304 OR ( (Recinfo.comment_id IS NULL)
2305 AND (X_Comment_Id IS NULL)))
2306 AND ( (Recinfo.attribute_category = X_Attribute_Category)
2307 OR ( (Recinfo.attribute_category IS NULL)
2308 AND (X_Attribute_Category IS NULL)))
2309 AND ( (Recinfo.attribute1 = X_Attribute1)
2310 OR ( (Recinfo.attribute1 IS NULL)
2311 AND (X_Attribute1 IS NULL)))
2312 AND ( (Recinfo.attribute2 = X_Attribute2)
2313 OR ( (Recinfo.attribute2 IS NULL)
2314 AND (X_Attribute2 IS NULL)))
2315 AND ( (Recinfo.attribute3 = X_Attribute3)
2316 OR ( (Recinfo.attribute3 IS NULL)
2317 AND (X_Attribute3 IS NULL)))
2318 AND ( (Recinfo.attribute4 = X_Attribute4)
2319 OR ( (Recinfo.attribute4 IS NULL)
2320 AND (X_Attribute4 IS NULL)))
2321 AND ( (Recinfo.attribute5 = X_Attribute5)
2322 OR ( (Recinfo.attribute5 IS NULL)
2323 AND (X_Attribute5 IS NULL)))
2324 AND ( (Recinfo.attribute6 = X_Attribute6)
2325 OR ( (Recinfo.attribute6 IS NULL)
2326 AND (X_Attribute6 IS NULL)))
2327 AND ( (Recinfo.attribute7 = X_Attribute7)
2328 OR ( (Recinfo.attribute7 IS NULL)
2329 AND (X_Attribute7 IS NULL)))
2330 AND ( (Recinfo.attribute8 = X_Attribute8)
2331 OR ( (Recinfo.attribute8 IS NULL)
2332 AND (X_Attribute8 IS NULL)))
2333 AND ( (Recinfo.attribute9 = X_Attribute9)
2334 OR ( (Recinfo.attribute9 IS NULL)
2335 AND (X_Attribute9 IS NULL)))
2336 AND ( (Recinfo.attribute10 = X_Attribute10)
2337 OR ( (Recinfo.attribute10 IS NULL)
2338 AND (X_Attribute10 IS NULL)))
2342 AND ( (Recinfo.attribute12 = X_Attribute12)
2339 AND ( (Recinfo.attribute11 = X_Attribute11)
2340 OR ( (Recinfo.attribute11 IS NULL)
2341 AND (X_Attribute11 IS NULL)))
2343 OR ( (Recinfo.attribute12 IS NULL)
2344 AND (X_Attribute12 IS NULL)))
2345 AND ( (Recinfo.attribute13 = X_Attribute13)
2346 OR ( (Recinfo.attribute13 IS NULL)
2347 AND (X_Attribute13 IS NULL)))
2348 AND ( (Recinfo.attribute14 = X_Attribute14)
2349 OR ( (Recinfo.attribute14 IS NULL)
2350 AND (X_Attribute14 IS NULL)))
2351 AND ( (Recinfo.attribute15 = X_Attribute15)
2352 OR ( (Recinfo.attribute15 IS NULL)
2353 AND (X_Attribute15 IS NULL)))
2354 AND ( (Recinfo.attribute16 = X_Attribute16)
2355 OR ( (Recinfo.attribute16 IS NULL)
2356 AND (X_Attribute16 IS NULL)))
2357 AND ( (Recinfo.attribute17 = X_Attribute17)
2358 OR ( (Recinfo.attribute17 IS NULL)
2359 AND (X_Attribute17 IS NULL)))
2360 AND ( (Recinfo.attribute18 = X_Attribute18)
2361 OR ( (Recinfo.attribute18 IS NULL)
2362 AND (X_Attribute18 IS NULL)))
2363 AND ( (Recinfo.attribute19 = X_Attribute19)
2364 OR ( (Recinfo.attribute19 IS NULL)
2365 AND (X_Attribute19 IS NULL)))
2366 AND ( (Recinfo.attribute20 = X_Attribute20)
2367 OR ( (Recinfo.attribute20 IS NULL)
2368 AND (X_Attribute20 IS NULL)))
2369 --
2370 AND ( (Recinfo.prl_information_category = X_Prl_Information_Category)
2371 OR ( (Recinfo.prl_information_category IS NULL)
2372 AND (X_Prl_Information_Category IS NULL)))
2373 AND ( (Recinfo.prl_information1 = X_Prl_Information1)
2374 OR ( (Recinfo.prl_information1 IS NULL)
2375 AND (X_Prl_Information1 IS NULL)))
2376 AND ( (Recinfo.prl_information2 = X_Prl_Information2)
2377 OR ( (Recinfo.prl_information2 IS NULL)
2378 AND (X_Prl_Information2 IS NULL)))
2379 AND ( (Recinfo.prl_information3 = X_Prl_Information3)
2380 OR ( (Recinfo.prl_information3 IS NULL)
2381 AND (X_Prl_Information3 IS NULL)))
2382 AND ( (Recinfo.prl_information4 = X_Prl_Information4)
2383 OR ( (Recinfo.prl_information4 IS NULL)
2384 AND (X_Prl_Information4 IS NULL)))
2385 AND ( (Recinfo.prl_information5 = X_Prl_Information5)
2389 OR ( (Recinfo.prl_information6 IS NULL)
2386 OR ( (Recinfo.prl_information5 IS NULL)
2387 AND (X_Prl_Information5 IS NULL)))
2388 AND ( (Recinfo.prl_information6 = X_Prl_Information6)
2390 AND (X_Prl_Information6 IS NULL)))
2391 AND ( (Recinfo.prl_information7 = X_Prl_Information7)
2392 OR ( (Recinfo.prl_information7 IS NULL)
2393 AND (X_Prl_Information7 IS NULL)))
2394 AND ( (Recinfo.prl_information8 = X_Prl_Information8)
2395 OR ( (Recinfo.prl_information8 IS NULL)
2396 AND (X_Prl_Information8 IS NULL)))
2397 AND ( (Recinfo.prl_information9 = X_Prl_Information9)
2398 OR ( (Recinfo.prl_information9 IS NULL)
2399 AND (X_Prl_Information9 IS NULL)))
2400 AND ( (Recinfo.prl_information10 = X_Prl_Information10)
2401 OR ( (Recinfo.prl_information10 IS NULL)
2402 AND (X_Prl_Information10 IS NULL)))
2403 AND ( (Recinfo.prl_information11 = X_Prl_Information11)
2404 OR ( (Recinfo.prl_information11 IS NULL)
2405 AND (X_Prl_Information11 IS NULL)))
2406 AND ( (Recinfo.prl_information12 = X_Prl_Information12)
2407 OR ( (Recinfo.prl_information12 IS NULL)
2408 AND (X_Prl_Information12 IS NULL)))
2409 AND ( (Recinfo.prl_information13 = X_Prl_Information13)
2410 OR ( (Recinfo.prl_information13 IS NULL)
2411 AND (X_Prl_Information13 IS NULL)))
2412 AND ( (Recinfo.prl_information14 = X_Prl_Information14)
2413 OR ( (Recinfo.prl_information14 IS NULL)
2414 AND (X_Prl_Information14 IS NULL)))
2415 AND ( (Recinfo.prl_information15 = X_Prl_Information15)
2416 OR ( (Recinfo.prl_information15 IS NULL)
2417 AND (X_Prl_Information15 IS NULL)))
2418 AND ( (Recinfo.prl_information16 = X_Prl_Information16)
2419 OR ( (Recinfo.prl_information16 IS NULL)
2420 AND (X_Prl_Information16 IS NULL)))
2421 AND ( (Recinfo.prl_information17 = X_Prl_Information17)
2422 OR ( (Recinfo.prl_information17 IS NULL)
2423 AND (X_Prl_Information17 IS NULL)))
2424 AND ( (Recinfo.prl_information18 = X_Prl_Information18)
2425 OR ( (Recinfo.prl_information18 IS NULL)
2426 AND (X_Prl_Information18 IS NULL)))
2427 AND ( (Recinfo.prl_information19 = X_Prl_Information19)
2428 OR ( (Recinfo.prl_information19 IS NULL)
2429 AND (X_Prl_Information19 IS NULL)))
2430 AND ( (Recinfo.prl_information20 = X_Prl_Information20)
2431 OR ( (Recinfo.prl_information20 IS NULL)
2432 AND (X_Prl_Information20 IS NULL)))
2433 AND ( (Recinfo.prl_information21 = X_Prl_Information21)
2434 OR ( (Recinfo.prl_information21 IS NULL)
2435 AND (X_Prl_Information21 IS NULL)))
2436 AND ( (Recinfo.prl_information22 = X_Prl_Information22)
2437 OR ( (Recinfo.prl_information22 IS NULL)
2438 AND (X_Prl_Information22 IS NULL)))
2439 AND ( (Recinfo.prl_information23 = X_Prl_Information23)
2440 OR ( (Recinfo.prl_information23 IS NULL)
2441 AND (X_Prl_Information23 IS NULL)))
2442 AND ( (Recinfo.prl_information24 = X_Prl_Information24)
2443 OR ( (Recinfo.prl_information24 IS NULL)
2444 AND (X_Prl_Information24 IS NULL)))
2445 AND ( (Recinfo.prl_information25 = X_Prl_Information25)
2446 OR ( (Recinfo.prl_information25 IS NULL)
2447 AND (X_Prl_Information25 IS NULL)))
2448 AND ( (Recinfo.prl_information26 = X_Prl_Information26)
2449 OR ( (Recinfo.prl_information26 IS NULL)
2450 AND (X_Prl_Information26 IS NULL)))
2451 AND ( (Recinfo.prl_information27 = X_Prl_Information27)
2452 OR ( (Recinfo.prl_information27 IS NULL)
2453 AND (X_Prl_Information27 IS NULL)))
2454 AND ( (Recinfo.prl_information28 = X_Prl_Information28)
2455 OR ( (Recinfo.prl_information28 IS NULL)
2456 AND (X_Prl_Information28 IS NULL)))
2457 AND ( (Recinfo.prl_information29 = X_Prl_Information29)
2458 OR ( (Recinfo.prl_information29 IS NULL)
2459 AND (X_Prl_Information29 IS NULL)))
2460 AND ( (Recinfo.prl_information30 = X_Prl_Information30)
2461 OR ( (Recinfo.prl_information30 IS NULL)
2462 AND (X_Prl_Information30 IS NULL)))
2463 --
2464 AND ( (Recinfo.arrears_flag = X_Arrears_Flag)
2465 OR ( (Recinfo.arrears_flag IS NULL)
2466 AND (X_Arrears_Flag IS NULL)))
2467 AND ( (Recinfo.multi_assignments_flag = X_Multi_Assignments_Flag)
2468 OR ( (Recinfo.multi_assignments_flag IS NULL)
2469 AND (X_Multi_Assignments_Flag IS NULL)))
2470 AND ( (Recinfo.period_reset_years = X_Period_Reset_Years)
2471 OR ( (Recinfo.period_reset_years IS NULL)
2472 AND (X_Period_Reset_Years IS NULL)))
2473 AND ( (Recinfo.payslip_view_date_offset = X_payslip_view_Date_Offset)
2474 OR ( (Recinfo.payslip_view_date_offset IS NULL)
2475 AND (X_payslip_view_Date_Offset IS NULL)))
2476
2477 ) then
2478 return;
2479 else
2480 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2481 APP_EXCEPTION.RAISE_EXCEPTION;
2482 end if;
2483 --
2484 END Lock_Row;
2485 --
2486 -----------------------------------------------------------------------------
2487 -- Name --
2488 -- Update_Row --
2489 -- Purpose --
2493 -- See below. --
2490 -- Table handler procedure that supports the update of a payroll via the --
2491 -- Define Payroll form. --
2492 -- Arguments --
2494 -- Notes --
2495 -- None. --
2496 -- Additions --
2497 -- Added X_payslip_view_date_offset By Rajeesha Bug 4246280 --
2498 -----------------------------------------------------------------------------
2499 --
2500 PROCEDURE Update_Row(X_Rowid VARCHAR2,
2501 X_Payroll_Id NUMBER,
2502 X_Effective_Start_Date DATE,
2503 X_Effective_End_Date DATE,
2504 X_Default_Payment_Method_Id NUMBER,
2505 X_Business_Group_Id NUMBER,
2506 X_Consolidation_Set_Id NUMBER,
2507 X_Cost_Allocation_Keyflex_Id NUMBER,
2508 X_Suspense_Account_Keyflex_Id NUMBER,
2509 X_Gl_Set_Of_Books_Id NUMBER,
2510 X_Soft_Coding_Keyflex_Id NUMBER,
2511 X_Period_Type VARCHAR2,
2512 X_Organization_Id NUMBER,
2513 X_Cut_Off_Date_Offset NUMBER,
2514 X_Direct_Deposit_Date_Offset NUMBER,
2515 X_First_Period_End_Date DATE,
2516 X_Negative_Pay_Allowed_Flag VARCHAR2,
2517 X_Number_Of_Years NUMBER,
2518 X_Pay_Advice_Date_Offset NUMBER,
2519 X_Pay_Date_Offset NUMBER,
2520 X_Payroll_Name VARCHAR2,
2521 X_Workload_Shifting_Level VARCHAR2,
2522 X_Comment_Id NUMBER,
2523 X_Attribute_Category VARCHAR2,
2524 X_Attribute1 VARCHAR2,
2525 X_Attribute2 VARCHAR2,
2526 X_Attribute3 VARCHAR2,
2527 X_Attribute4 VARCHAR2,
2528 X_Attribute5 VARCHAR2,
2529 X_Attribute6 VARCHAR2,
2530 X_Attribute7 VARCHAR2,
2531 X_Attribute8 VARCHAR2,
2532 X_Attribute9 VARCHAR2,
2533 X_Attribute10 VARCHAR2,
2534 X_Attribute11 VARCHAR2,
2535 X_Attribute12 VARCHAR2,
2536 X_Attribute13 VARCHAR2,
2537 X_Attribute14 VARCHAR2,
2538 X_Attribute15 VARCHAR2,
2539 X_Attribute16 VARCHAR2,
2540 X_Attribute17 VARCHAR2,
2541 X_Attribute18 VARCHAR2,
2542 X_Attribute19 VARCHAR2,
2543 X_Attribute20 VARCHAR2,
2544 -- Payroll Developer DF
2545 X_Prl_Information_Category VARCHAR2 DEFAULT NULL,
2546 X_Prl_Information1 VARCHAR2 DEFAULT NULL,
2547 X_Prl_Information2 VARCHAR2 DEFAULT NULL,
2548 X_Prl_Information3 VARCHAR2 DEFAULT NULL,
2549 X_Prl_Information4 VARCHAR2 DEFAULT NULL,
2550 X_Prl_Information5 VARCHAR2 DEFAULT NULL,
2551 X_Prl_Information6 VARCHAR2 DEFAULT NULL,
2552 X_Prl_Information7 VARCHAR2 DEFAULT NULL,
2553 X_Prl_Information8 VARCHAR2 DEFAULT NULL,
2554 X_Prl_Information9 VARCHAR2 DEFAULT NULL,
2555 X_Prl_Information10 VARCHAR2 DEFAULT NULL,
2556 X_Prl_Information11 VARCHAR2 DEFAULT NULL,
2557 X_Prl_Information12 VARCHAR2 DEFAULT NULL,
2558 X_Prl_Information13 VARCHAR2 DEFAULT NULL,
2559 X_Prl_Information14 VARCHAR2 DEFAULT NULL,
2560 X_Prl_Information15 VARCHAR2 DEFAULT NULL,
2564 X_Prl_Information19 VARCHAR2 DEFAULT NULL,
2561 X_Prl_Information16 VARCHAR2 DEFAULT NULL,
2562 X_Prl_Information17 VARCHAR2 DEFAULT NULL,
2563 X_Prl_Information18 VARCHAR2 DEFAULT NULL,
2565 X_Prl_Information20 VARCHAR2 DEFAULT NULL,
2566 X_Prl_Information21 VARCHAR2 DEFAULT NULL,
2567 X_Prl_Information22 VARCHAR2 DEFAULT NULL,
2568 X_Prl_Information23 VARCHAR2 DEFAULT NULL,
2569 X_Prl_Information24 VARCHAR2 DEFAULT NULL,
2570 X_Prl_Information25 VARCHAR2 DEFAULT NULL,
2571 X_Prl_Information26 VARCHAR2 DEFAULT NULL,
2572 X_Prl_Information27 VARCHAR2 DEFAULT NULL,
2573 X_Prl_Information28 VARCHAR2 DEFAULT NULL,
2574 X_Prl_Information29 VARCHAR2 DEFAULT NULL,
2575 X_Prl_Information30 VARCHAR2 DEFAULT NULL,
2576 -- Extra Columns
2577 X_Validation_Start_date DATE,
2578 X_Validation_End_date DATE,
2579 X_Arrears_Flag VARCHAR2,
2580 X_Multi_Assignments_Flag VARCHAR2 DEFAULT NULL,
2581 X_Period_Reset_Years VARCHAR2 DEFAULT NULL,
2582 X_payslip_view_date_offset NUMBER DEFAULT NULL
2583 ) IS
2584
2585 --
2586 v_payroll_name varchar2(80);
2587 v_number_of_years number;
2588 v_default_payment_method_id number;
2589 --
2590 BEGIN
2591 --
2592 -- Find the current values for payroll name, default payment method and
2593 -- the number of years.
2594 current_values
2595 (X_Rowid,
2596 v_payroll_name,
2597 v_number_of_years,
2598 v_default_payment_method_id);
2599 --
2600 pay_payrolls_F_pkg.validate_update_payroll
2601 (X_Business_Group_id,
2602 X_Payroll_Id,
2603 X_Payroll_Name,
2604 v_payroll_name, -- Current payroll name
2605 X_Default_Payment_Method_Id,
2606 v_default_payment_method_id, -- Current default payment method
2607 X_Validation_Start_Date,
2608 X_Validation_End_Date);
2609
2610 UPDATE PAY_PAYROLLS_F
2611 SET payroll_id = X_Payroll_Id,
2612 effective_start_date = X_Effective_Start_Date,
2613 effective_end_date = X_Effective_End_Date,
2614 default_payment_method_id = X_Default_Payment_Method_Id,
2615 business_group_id = X_Business_Group_Id,
2616 consolidation_set_id = X_Consolidation_Set_Id,
2617 cost_allocation_keyflex_id = X_Cost_Allocation_Keyflex_Id,
2618 suspense_account_keyflex_id = X_Suspense_Account_Keyflex_Id,
2619 gl_set_of_books_id = X_Gl_Set_Of_Books_Id,
2620 soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id,
2621 period_type = X_Period_Type,
2622 organization_id = X_Organization_Id,
2623 cut_off_date_offset = X_Cut_Off_Date_Offset,
2624 direct_deposit_date_offset = X_Direct_Deposit_Date_Offset,
2625 first_period_end_date = X_First_Period_End_Date,
2626 negative_pay_allowed_flag = X_Negative_Pay_Allowed_Flag,
2627 number_of_years = X_Number_Of_Years,
2628 pay_advice_date_offset = X_Pay_Advice_Date_Offset,
2629 pay_date_offset = X_Pay_Date_Offset,
2630 payroll_name = X_Payroll_Name,
2631 workload_shifting_level = X_Workload_Shifting_Level,
2632 comment_id = X_Comment_Id,
2633 attribute_category = X_Attribute_Category,
2634 attribute1 = X_Attribute1,
2635 attribute2 = X_Attribute2,
2636 attribute3 = X_Attribute3,
2637 attribute4 = X_Attribute4,
2638 attribute5 = X_Attribute5,
2639 attribute6 = X_Attribute6,
2640 attribute7 = X_Attribute7,
2641 attribute8 = X_Attribute8,
2645 attribute12 = X_Attribute12,
2642 attribute9 = X_Attribute9,
2643 attribute10 = X_Attribute10,
2644 attribute11 = X_Attribute11,
2646 attribute13 = X_Attribute13,
2647 attribute14 = X_Attribute14,
2648 attribute15 = X_Attribute15,
2649 attribute16 = X_Attribute16,
2650 attribute17 = X_Attribute17,
2651 attribute18 = X_Attribute18,
2652 attribute19 = X_Attribute19,
2653 attribute20 = X_Attribute20,
2654 prl_information_category = X_Prl_Information_Category,
2655 prl_information1 = X_Prl_Information1,
2656 prl_information2 = X_Prl_Information2,
2657 prl_information3 = X_Prl_Information3,
2658 prl_information4 = X_Prl_Information4,
2659 prl_information5 = X_Prl_Information5,
2660 prl_information6 = X_Prl_Information6,
2661 prl_information7 = X_Prl_Information7,
2662 prl_information8 = X_Prl_Information8,
2663 prl_information9 = X_Prl_Information9,
2664 prl_information10 = X_Prl_Information10,
2665 prl_information11 = X_Prl_Information11,
2666 prl_information12 = X_Prl_Information12,
2667 prl_information13 = X_Prl_Information13,
2668 prl_information14 = X_Prl_Information14,
2669 prl_information15 = X_Prl_Information15,
2670 prl_information16 = X_Prl_Information16,
2671 prl_information17 = X_Prl_Information17,
2672 prl_information18 = X_Prl_Information18,
2673 prl_information19 = X_Prl_Information19,
2674 prl_information20 = X_Prl_Information20,
2675 prl_information21 = X_Prl_Information21,
2676 prl_information22 = X_Prl_Information22,
2677 prl_information23 = X_Prl_Information23,
2681 prl_information27 = X_Prl_Information27,
2678 prl_information24 = X_Prl_Information24,
2679 prl_information25 = X_Prl_Information25,
2680 prl_information26 = X_Prl_Information26,
2682 prl_information28 = X_Prl_Information28,
2683 prl_information29 = X_Prl_Information29,
2684 prl_information30 = X_Prl_Information30,
2685 arrears_flag = X_Arrears_Flag,
2686 multi_assignments_flag = X_Multi_Assignments_Flag,
2687 period_reset_years = X_Period_Reset_Years,
2688 payslip_view_date_offset = X_payslip_view_date_offset
2689 WHERE rowid = X_rowid;
2690 --
2691 if (SQL%NOTFOUND) then
2692 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2693 hr_utility.set_message_token('PROCEDURE',
2694 'pay_payrolls_f_pkg.update_row');
2695 hr_utility.set_message_token('STEP','1');
2696 hr_utility.raise_error;
2697 --
2698 end if;
2699 --
2700 pay_payrolls_f_pkg.update_payroll
2701 (x_business_group_id,
2702 x_payroll_id,
2703 x_payroll_name,
2704 v_payroll_name, -- Current payroll name
2705 x_default_payment_method_id,
2706 v_default_payment_method_id, -- Current default payment method
2707 x_number_of_years,
2708 v_number_of_years, -- Current number of years
2709 x_validation_start_date,
2710 x_validation_end_date);
2711 --
2712 END Update_Row;
2713 --
2714 --
2715 -----------------------------------------------------------------------------
2716 -- Name --
2717 -- Update_Row --
2718 -- Purpose --
2719 -- Table handler procedure that supports the update of a payroll via the --
2720 -- Define Payroll form. --
2721 -- Arguments --
2722 -- See below. --
2723 -- Notes --
2724 -- None. --
2725 -- Additions --
2726 -- Added X_payslip_view_date_offset By Rajeesha Bug 4246280 --
2730 PROCEDURE Update_Row(X_Default_Payment_Method_Id NUMBER,
2727 -- Added the Overloaded procedure to call the API 5144323 --
2728 -----------------------------------------------------------------------------
2729 --
2731 X_Business_Group_Id NUMBER,
2732 X_Consolidation_Set_Id NUMBER,
2733 X_Cost_Allocation_Keyflex_Id NUMBER,
2734 X_Suspense_Account_Keyflex_Id NUMBER,
2735 X_Gl_Set_Of_Books_Id NUMBER,
2736 X_Soft_Coding_Keyflex_Id NUMBER,
2737 X_Period_Type VARCHAR2,
2738 X_Organization_Id NUMBER,
2739 X_Cut_Off_Date_Offset NUMBER,
2740 X_Direct_Deposit_Date_Offset NUMBER,
2741 X_First_Period_End_Date DATE,
2742 X_Negative_Pay_Allowed_Flag VARCHAR2,
2743 X_Number_Of_Years NUMBER,
2744 X_Pay_Advice_Date_Offset NUMBER,
2745 X_Pay_Date_Offset NUMBER,
2746 X_Payroll_Name VARCHAR2,
2747 X_Workload_Shifting_Level VARCHAR2,
2748 X_Attribute_Category VARCHAR2,
2749 X_Attribute1 VARCHAR2,
2750 X_Attribute2 VARCHAR2,
2751 X_Attribute3 VARCHAR2,
2752 X_Attribute4 VARCHAR2,
2753 X_Attribute5 VARCHAR2,
2754 X_Attribute6 VARCHAR2,
2755 X_Attribute7 VARCHAR2,
2756 X_Attribute8 VARCHAR2,
2757 X_Attribute9 VARCHAR2,
2758 X_Attribute10 VARCHAR2,
2759 X_Attribute11 VARCHAR2,
2760 X_Attribute12 VARCHAR2,
2761 X_Attribute13 VARCHAR2,
2762 X_Attribute14 VARCHAR2,
2763 X_Attribute15 VARCHAR2,
2764 X_Attribute16 VARCHAR2,
2765 X_Attribute17 VARCHAR2,
2766 X_Attribute18 VARCHAR2,
2767 X_Attribute19 VARCHAR2,
2768 X_Attribute20 VARCHAR2,
2769 -- Payroll Developer DF
2770 X_Prl_Information_Category VARCHAR2 DEFAULT NULL,
2771 X_Prl_Information1 VARCHAR2 DEFAULT NULL,
2772 X_Prl_Information2 VARCHAR2 DEFAULT NULL,
2773 X_Prl_Information3 VARCHAR2 DEFAULT NULL,
2774 X_Prl_Information4 VARCHAR2 DEFAULT NULL,
2775 X_Prl_Information5 VARCHAR2 DEFAULT NULL,
2776 X_Prl_Information6 VARCHAR2 DEFAULT NULL,
2777 X_Prl_Information7 VARCHAR2 DEFAULT NULL,
2778 X_Prl_Information8 VARCHAR2 DEFAULT NULL,
2779 X_Prl_Information9 VARCHAR2 DEFAULT NULL,
2780 X_Prl_Information10 VARCHAR2 DEFAULT NULL,
2781 X_Prl_Information11 VARCHAR2 DEFAULT NULL,
2782 X_Prl_Information12 VARCHAR2 DEFAULT NULL,
2783 X_Prl_Information13 VARCHAR2 DEFAULT NULL,
2784 X_Prl_Information14 VARCHAR2 DEFAULT NULL,
2785 X_Prl_Information15 VARCHAR2 DEFAULT NULL,
2789 X_Prl_Information19 VARCHAR2 DEFAULT NULL,
2786 X_Prl_Information16 VARCHAR2 DEFAULT NULL,
2787 X_Prl_Information17 VARCHAR2 DEFAULT NULL,
2788 X_Prl_Information18 VARCHAR2 DEFAULT NULL,
2790 X_Prl_Information20 VARCHAR2 DEFAULT NULL,
2791 X_Prl_Information21 VARCHAR2 DEFAULT NULL,
2792 X_Prl_Information22 VARCHAR2 DEFAULT NULL,
2793 X_Prl_Information23 VARCHAR2 DEFAULT NULL,
2794 X_Prl_Information24 VARCHAR2 DEFAULT NULL,
2795 X_Prl_Information25 VARCHAR2 DEFAULT NULL,
2796 X_Prl_Information26 VARCHAR2 DEFAULT NULL,
2797 X_Prl_Information27 VARCHAR2 DEFAULT NULL,
2798 X_Prl_Information28 VARCHAR2 DEFAULT NULL,
2799 X_Prl_Information29 VARCHAR2 DEFAULT NULL,
2800 X_Prl_Information30 VARCHAR2 DEFAULT NULL,
2801 -- Extra Columns
2802 X_Validation_Start_date DATE,
2803 X_Validation_End_date DATE,
2804 X_Arrears_Flag VARCHAR2,
2805 X_Multi_Assignments_Flag VARCHAR2 DEFAULT NULL,
2806 X_Period_Reset_Years VARCHAR2 DEFAULT NULL,
2807 X_payslip_view_date_offset NUMBER DEFAULT NULL
2808 --bug 5609830 / 5144323 TEST starts contents
2809 ,X_Dt_Update_Mode VARCHAR2 --new
2810 ,X_effective_date DATE --new
2811 ,X_Comments VARCHAR2 DEFAULT NULL --new
2812 ,X_effective_start_date OUT nocopy DATE --type out added
2813 ,X_effective_end_date OUT nocopy DATE --type out added
2814 ,X_Comment_Id OUT nocopy NUMBER --type out added
2815 ,X_Rowid in OUT nocopy VARCHAR2 --type in out added
2816 ,X_Payroll_Id in OUT nocopy NUMBER --type in out added
2817 --bug 5609830 / 5144323 TEST ends contents
2818 ) IS
2819
2820 --
2821 v_payroll_name varchar2(80);
2822 v_number_of_years number;
2823 v_default_payment_method_id number;
2824 --
2825 l_payroll_id number := X_payroll_id;
2826 l_rowid VARCHAR2(150);
2827 l_prl_object_version_number NUMBER;
2828
2829 cursor csr_prl_ovn is
2830 select object_version_number
2831 from pay_all_payrolls_f
2832 where rowid = X_Rowid;
2833
2834 cursor csr_prl_rowid is
2835 select rowid
2836 from pay_all_payrolls_f
2837 where payroll_id = l_Payroll_id
2838 and object_version_number = l_prl_object_version_number;
2839 --
2840 l_cost_allocation_keyflex_id PAY_ALL_PAYROLLS_F.COST_ALLOCATION_KEYFLEX_ID%TYPE ;
2841 l_suspense_account_keyflex_id PAY_ALL_PAYROLLS_F.SUSPENSE_ACCOUNT_KEYFLEX_ID%TYPE ;
2842 l_soft_coding_keyflex_id PAY_ALL_PAYROLLS_F.SOFT_CODING_KEYFLEX_ID%TYPE ;
2846 --
2843 l_cost_concat_segments PAY_COST_ALLOCATION_KEYFLEX.concatenated_segments%TYPE ;
2844 l_susp_concat_segments PAY_COST_ALLOCATION_KEYFLEX.concatenated_segments%TYPE ;
2845 l_scl_concat_segments HR_SOFT_CODING_KEYFLEX.concatenated_segments%TYPE;
2847 BEGIN
2848 --
2849 -- Find the current values for payroll name, default payment method and
2850 -- the number of years.
2851 current_values
2852 (X_Rowid,
2853 v_payroll_name,
2854 v_number_of_years,
2855 v_default_payment_method_id);
2856 --
2857 pay_payrolls_F_pkg.validate_update_payroll
2858 (X_Business_Group_id,
2859 X_Payroll_Id,
2860 X_Payroll_Name,
2861 v_payroll_name, -- Current payroll name
2862 X_Default_Payment_Method_Id,
2863 v_default_payment_method_id, -- Current default payment method
2864 X_Validation_Start_Date,
2865 X_Validation_End_Date);
2866
2867 open csr_prl_ovn;
2868 fetch csr_prl_ovn into l_prl_object_version_number;
2869 close csr_prl_ovn;
2870
2871 pay_payroll_api.update_payroll(
2872 p_validate => FALSE
2873 ,p_effective_date => X_effective_date
2874 ,p_datetrack_mode => X_Dt_Update_Mode
2875 ,p_payroll_name => X_payroll_name
2876 ,p_consolidation_set_id => X_consolidation_set_id
2877 ,p_number_of_years => X_number_of_years
2878 ,p_default_payment_method_id => X_default_payment_method_id
2879 ,p_cost_alloc_keyflex_id_in => X_cost_allocation_keyflex_id
2880 ,p_susp_account_keyflex_id_in => X_suspense_account_keyflex_id
2881 ,p_negative_pay_allowed_flag => X_negative_pay_allowed_flag
2882 ,p_soft_coding_keyflex_id_in => X_soft_coding_keyflex_id
2883 ,p_comments => X_comments
2884 ,p_attribute_category => X_attribute_category
2885 ,p_attribute1 => X_attribute1
2886 ,p_attribute2 => X_attribute2
2887 ,p_attribute3 => X_attribute3
2888 ,p_attribute4 => X_attribute4
2889 ,p_attribute5 => X_attribute5
2890 ,p_attribute6 => X_attribute6
2891 ,p_attribute7 => X_attribute7
2892 ,p_attribute8 => X_attribute8
2893 ,p_attribute9 => X_attribute9
2894 ,p_attribute10 => X_attribute10
2895 ,p_attribute11 => X_attribute11
2896 ,p_attribute12 => X_attribute12
2897 ,p_attribute13 => X_attribute13
2898 ,p_attribute14 => X_attribute14
2899 ,p_attribute15 => X_attribute15
2900 ,p_attribute16 => X_attribute16
2901 ,p_attribute17 => X_attribute17
2902 ,p_attribute18 => X_attribute18
2903 ,p_attribute19 => X_attribute19
2904 ,p_attribute20 => X_attribute20
2905 ,p_arrears_flag => X_arrears_flag
2906 ,p_multi_assignments_flag => X_multi_assignments_flag
2907 ,p_prl_information1 => X_prl_information1
2908 ,p_prl_information2 => X_prl_information2
2909 ,p_prl_information3 => X_prl_information3
2910 ,p_prl_information4 => X_prl_information4
2911 ,p_prl_information5 => X_prl_information5
2912 ,p_prl_information6 => X_prl_information6
2913 ,p_prl_information7 => X_prl_information7
2914 ,p_prl_information8 => X_prl_information8
2915 ,p_prl_information9 => X_prl_information9
2919 ,p_prl_information13 => X_prl_information13
2916 ,p_prl_information10 => X_prl_information10
2917 ,p_prl_information11 => X_prl_information11
2918 ,p_prl_information12 => X_prl_information12
2920 ,p_prl_information14 => X_prl_information14
2921 ,p_prl_information15 => X_prl_information15
2922 ,p_prl_information16 => X_prl_information16
2923 ,p_prl_information17 => X_prl_information17
2924 ,p_prl_information18 => X_prl_information18
2925 ,p_prl_information19 => X_prl_information19
2926 ,p_prl_information20 => X_prl_information20
2927 ,p_prl_information21 => X_prl_information21
2928 ,p_prl_information22 => X_prl_information22
2929 ,p_prl_information23 => X_prl_information23
2930 ,p_prl_information24 => X_prl_information24
2931 ,p_prl_information25 => X_prl_information25
2932 ,p_prl_information26 => X_prl_information26
2933 ,p_prl_information27 => X_prl_information27
2934 ,p_prl_information28 => X_prl_information28
2935 ,p_prl_information29 => X_prl_information29
2936 ,p_prl_information30 => X_prl_information30
2937 --
2938 ,p_workload_shifting_level => X_Workload_Shifting_Level
2939 ,p_payslip_view_date_offset => X_payslip_view_date_offset
2940 --
2941 ,p_payroll_id => l_Payroll_Id
2942 ,p_object_version_number => l_prl_object_version_number
2946 ,p_comment_id => X_comment_id
2943
2944 ,p_effective_start_date => X_effective_start_date
2945 ,p_effective_end_date => X_effective_end_date
2947 --
2948 ,p_cost_alloc_keyflex_id_out => l_cost_allocation_keyflex_id
2949 ,p_susp_account_keyflex_id_out => l_suspense_account_keyflex_id
2950 ,p_soft_coding_keyflex_id_out => l_soft_coding_keyflex_id
2951 ,p_cost_concat_segments_out => l_cost_concat_segments
2952 ,p_susp_concat_segments_out => l_susp_concat_segments
2953 ,p_scl_concat_segments_out => l_scl_concat_segments
2954 --
2955 );
2956
2957 open csr_prl_rowid;
2958 fetch csr_prl_rowid into l_rowid;
2959 close csr_prl_rowid;
2960
2961 X_Payroll_Id := l_payroll_id;
2962 X_rowid := l_rowid;
2963
2964 --
2965 pay_payrolls_f_pkg.update_payroll
2966 (x_business_group_id,
2967 x_payroll_id,
2968 x_payroll_name,
2969 v_payroll_name, -- Current payroll name
2970 x_default_payment_method_id,
2971 v_default_payment_method_id, -- Current default payment method
2972 x_number_of_years,
2973 v_number_of_years, -- Current number of years
2974 x_validation_start_date,
2975 x_validation_end_date);
2976 --
2977 END Update_Row;
2978 --
2979 -----------------------------------------------------------------------------
2980 -- Name --
2981 -- Delete_Row --
2982 -- Purpose --
2983 -- Table handler procedure that supports the delete of a payroll via the --
2984 -- Define Payroll form. --
2985 -- Arguments --
2986 -- See below. --
2987 -- Notes --
2988 -- --
2989 -----------------------------------------------------------------------------
2990 --
2991 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
2992 -- Extra Columns
2993 X_Payroll_Id NUMBER,
2994 X_Default_Payment_Method_Id NUMBER,
2995 X_Dt_Delete_Mode VARCHAR2,
2996 X_Validation_Start_date DATE,
2997 X_Validation_End_date DATE) IS
2998 BEGIN
2999 --
3000 pay_payrolls_f_pkg.validate_delete_payroll
3001 (x_payroll_id,
3002 x_default_payment_method_id,
3003 x_dt_delete_mode,
3004 x_validation_start_date,
3005 x_validation_end_date);
3006 --
3007 DELETE FROM PAY_PAYROLLS_F
3008 WHERE rowid = X_Rowid;
3009 --
3010 if (SQL%NOTFOUND) then
3011 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3012 hr_utility.set_message_token('PROCEDURE',
3013 'pay_payrolls_f_pkg.delete_row');
3014 hr_utility.set_message_token('STEP','1');
3015 hr_utility.raise_error;
3016 end if;
3017 --
3018 pay_payrolls_F_pkg.delete_payroll
3019 (x_payroll_id,
3020 x_default_payment_method_id,
3021 x_dt_delete_mode,
3022 x_validation_start_date,
3023 x_validation_end_date);
3024 --
3025 END Delete_Row;
3026 --
3027 -----------------------------------------------------------------------------
3028 -- Name --
3029 -- Delete_Row --
3030 -- Purpose --
3031 -- Table handler procedure that supports the delete of a payroll via the --
3032 -- Define Payroll form. --
3033 -- Arguments --
3034 -- See below. --
3035 -- Notes --
3036 -- --
3037 -- Added the Overloaded procedure to call the API 5144323 --
3038 -----------------------------------------------------------------------------
3039 --
3040 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
3041 -- Extra Columns
3042 X_Payroll_Id NUMBER,
3043 X_Default_Payment_Method_Id NUMBER,
3044 X_Dt_Delete_Mode VARCHAR2,
3045 X_Validation_Start_date DATE,
3046 X_Validation_End_date DATE
3047 -- bug 5609830 / 5144323 TEST starts contents
3048 ,X_effective_date DATE
3049 -- bug 5609830 / 5144323 TEST ends contents
3050 ) IS
3051 --
3052 l_effective_start_date date;
3053 l_effective_end_date date;
3054 l_prl_object_version_number NUMBER;
3055
3056 cursor csr_prl_ovn is
3057 select object_version_number
3058 from pay_all_payrolls_f
3062 --
3059 where rowid = X_Rowid;
3060 --
3061 BEGIN
3063 pay_payrolls_f_pkg.validate_delete_payroll
3064 (x_payroll_id,
3065 x_default_payment_method_id,
3066 x_dt_delete_mode,
3067 x_validation_start_date,
3068 x_validation_end_date);
3069 --
3070 open csr_prl_ovn;
3071 fetch csr_prl_ovn into l_prl_object_version_number;
3072 close csr_prl_ovn;
3073
3074 pay_payroll_api.delete_payroll
3075 (p_validate => FALSE,
3076 p_effective_date => X_effective_date,
3077 p_datetrack_mode => X_Dt_Delete_Mode,
3078 p_payroll_id => X_payroll_id,
3079 p_object_version_number => l_prl_object_version_number,
3080 p_effective_start_date => l_effective_start_date,
3081 p_effective_end_date => l_effective_end_date
3082 );
3083 --
3084 pay_payrolls_F_pkg.delete_payroll
3085 (x_payroll_id,
3086 x_default_payment_method_id,
3087 x_dt_delete_mode,
3088 x_validation_start_date,
3089 x_validation_end_date);
3090 --
3091 END Delete_Row;
3092 --
3093 -----------------------------------------------------------------------------
3094 -- Name --
3095 -- get_offset_field_prompts --
3096 -- Purpose --
3097 -- To retrieve the labels for the form PAYWSDPG taking the legislation --
3098 -- code as parameter. --
3099 -- Arguments --
3100 -- See below. --
3101 -- Notes --
3102 -- --
3103 -- Additions --
3104 -- Added p_payslip_view_date_prompt By Rajeesha Bug 4246280 --
3105 -----------------------------------------------------------------------------
3106 --
3107 PROCEDURE get_offset_field_prompts ( p_legislation_code IN varchar2,
3108 p_pay_date_prompt IN out nocopy varchar2,
3109 p_dd_offset_prompt IN out nocopy varchar2,
3110 p_pay_advice_offset_prompt IN out nocopy varchar2,
3111 p_cut_off_date IN out nocopy varchar2,
3112 p_arrears_flag IN out nocopy varchar2,
3113 p_payslip_view_date_prompt IN out nocopy varchar2
3114 )
3115 IS
3116 CURSOR c_advance_pay IS
3117 SELECT 'X'
3118 FROM pay_legislation_rules
3119 WHERE legislation_code = p_legislation_code
3120 AND rule_type = 'ADVANCE';
3121
3122 l_lookup_type varchar2(80);
3123 BEGIN
3124
3125 l_lookup_type := p_legislation_code || '_PAYWSDPG_PROMPT';
3126
3127 p_pay_date_prompt := hr_general.decode_lookup(l_lookup_type, 'PAY_DATE_PROMPT');
3128 p_dd_offset_prompt := hr_general.decode_lookup(l_lookup_type, 'DD_OFFSET_PROMPT');
3129 p_pay_advice_offset_prompt := hr_general.decode_lookup(l_lookup_type, 'PAY_ADVICE_OFFSET_PROMPT');
3130 p_cut_off_date := hr_general.decode_lookup(l_lookup_type, 'CUT_OFF_DATE');
3131 p_payslip_view_date_prompt := hr_general.decode_lookup(l_lookup_type,'PAYSLIP_VIEW_DATE_PROMPT');
3132 --
3133 -- Currently the Arrears flag is only relevant for legislations using Advance
3134 -- Pay. Although it's part of the core product it may appear confusing to
3135 -- customers in other legislations (US) if this flag was not set but they were
3136 -- running an Arrears Payroll. This temporary piece of code will ensure that
3137 -- only legislations with the 'ADVANCE' element defined will display the flag.
3138 OPEN c_advance_pay;
3139 FETCH c_advance_pay
3140 INTO p_arrears_flag;
3141 CLOSE c_advance_pay;
3142
3143 END get_offset_field_prompts;
3144 --
3145 -----------------------------------------------------------------------------
3146 -- Name --
3147 -- show_ddf_canvas_yesno --
3148 -- Purpose --
3149 -- If at least a segment has been defined for the Payroll DDF, then --
3150 -- the PAYROLL_DDF canvas will be shown --
3151 -- Arguments --
3152 -- See below. --
3153 -- Notes --
3154 -- --
3158 p_legislation_code IN varchar2,
3155 -----------------------------------------------------------------------------
3156 --
3157 PROCEDURE show_ddf_canvas_yesno ( p_ddf_name IN varchar2,
3159 p_show_ddf_canvas out nocopy boolean)
3160 IS
3161 CURSOR c_show_ddf_canvas IS
3162 SELECT 1
3163 FROM fnd_descr_flex_column_usages
3164 WHERE application_id = 801
3165 AND descriptive_flexfield_name = p_ddf_name
3166 AND descriptive_flex_context_code = p_legislation_code;
3167 --
3168 v_dummy number;
3169 v_show_ddf_canvas boolean := false;
3170 --
3171 BEGIN
3172 --
3173 hr_utility.set_location('pyprl01t.show_ddf_canvas_yesno',100);
3174
3175 -- the flexfield canvas will be shown only if there are segments defined
3176 -- for the Payroll DDF
3177
3178 OPEN c_show_ddf_canvas;
3179 FETCH c_show_ddf_canvas into v_dummy;
3180 if c_show_ddf_canvas%found then
3181 v_show_ddf_canvas := true;
3182 end if;
3183 CLOSE c_show_ddf_canvas;
3184
3185 p_show_ddf_canvas := v_show_ddf_canvas;
3186 hr_utility.set_location('pyprl01t.show_ddf_canvas_yesno',200);
3187
3188 END show_ddf_canvas_yesno;
3189 --
3190 END pay_payrolls_f_pkg;