1 Package Body pay_qpq_api as
2 /* $Header: pyqpqrhi.pkb 120.1 2005/06/22 02:41:47 mreid noship $ */
3 --
4 -- Current record structure definition
5 --
6 g_old_rec g_rec_type;
7 --
8 -- Global package name
9 --
10 g_package varchar2(33) := ' pay_qpq_api.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |---------------------------< use_qpay_excl_model >------------------------|
18 -- ----------------------------------------------------------------------------
19 function use_qpay_excl_model return varchar2 is
20 /*
21 || Introduced via Enhancement 3368211.
22 || This function is used to determine whether the new QuickPay Exclusions
23 || data model is in use.
24 */
25 --
26 l_short_name varchar2(30) := 'QPAY_EXCL_TABLE';
27 l_status varchar2(15);
28 l_use_qpay_excl_model boolean;
29 --
30 qpay_upgrade_in_progress exception;
31 pragma exception_init (qpay_upgrade_in_progress, -20001);
32 --
33 begin
34 --
35 pay_core_utils.get_upgrade_status(
36 p_bus_grp_id => null,
37 p_short_name => l_short_name,
38 p_status => l_status
39 );
40 --
41 return l_status;
42 --
43 exception
44 --
45 when qpay_upgrade_in_progress then
46 hr_utility.set_message(801,'PAY_33880_QPAY_UPG_IN_PROGRESS');
47 hr_utility.raise_error;
48 --
49 end use_qpay_excl_model;
50 --
51 -- ----------------------------------------------------------------------------
52 -- |-------------------------< control_separate_check_entries >---------------|
53 -- ----------------------------------------------------------------------------
54 procedure control_separate_check_entries
55
56 /*
57 || This procedure ensures that a 'quickpay' payroll run cannot contain
58 || an earnings entry that is required to be processed separately as well as any
59 || other earnings entry. This is done by looking for a specified set of input
60 || value names. The check is only meaningful for the US legislation.
61 */
62
63 (
64 pi_payroll_action_id in pay_payroll_actions.payroll_action_id%type
65 -- The "in" parameter used to identify Payroll Action
66 ) is
67
68 -- Trace variables used for debugging
69 l_procedure_name constant varchar2 (72) := g_package||'control_separate_check_entries';
70 l_step_reached varchar2 (4) := '0';
71
72 -- The conditions which must be met for the parameters to be valid
73 all_parameters_are_valid constant boolean := (pi_payroll_action_id is not null);
74
75 --
76 -- Enhancement 3368211
77 -- This is the version of csr_earnings to be used with the obsolete
78 -- QuickPay Inclusions model
79 --
80 cursor csr_earnings_old (
81
82 -- Get the number of earnings entries in the quickpay run
83
84 pi_look_for_separate_check in varchar2 default 'YES'
85
86 -- If parameter set to YES then look only for entries that must be processed
87 -- separately, otherwise look only for entries without that requirement.
88
89 ) is
90
91 select /*+ ORDERED INDEX(PAY_ACTION PAY_PAYROLL_ACTIONS_PK)
92 INDEX(ASGT_ACTION PAY_ASSIGNMENT_ACTIONS_N50)
93 INDEX(QP_INCL PAY_QUICKPAY_INCLUSIONS_FK2)
94 INDEX(ENTRY PAY_ELEMENT_ENTRIES_F_PK)
95 INDEX(LINK PAY_ELEMENT_LINKS_F_PK)
96 INDEX(ELEMENT PAY_ELEMENT_TYPES_F_PK)
97 INDEX(CLASS PAY_ELEMENT_CLASSIFICATION_PK)
98 INDEX(ENTVAL PAY_ELEMENT_ENTRY_VALUES_F_N50)
99 USE_NL(PAY_ACTION, ASGT_ACTION) USE_NL(ASGT_ACTION, QP_INCL)
100 USE_NL(QP_INCL, ENTRY) USE_NL(ENTRY, LINK) USE_NL(LINK, ELEMENT)
101 USE_NL(ELEMENT, CLASS) USE_NL(CLASS, ENTVAL) */
102 count (*) TOTAL_OF_ROWS
103 from pay_payroll_actions PAY_ACTION,
104 pay_assignment_actions ASGT_ACTION,
105 pay_quickpay_inclusions QP_INCL,
106 pay_element_entries_f ENTRY,
107 pay_element_links_f LINK,
108 pay_element_types_f ELEMENT,
109 pay_element_classifications CLASS,
110 pay_element_entry_values_f ENTVAL
111 where pay_action.payroll_action_id = PI_PAYROLL_ACTION_ID
112 and asgt_action.payroll_action_id = pay_action.payroll_action_id
113 and asgt_action.assignment_action_id = qp_incl.assignment_action_id
114 and qp_incl.element_entry_id = entry.element_entry_id
115 and entry.element_link_id = link.element_link_id
116 and link.element_type_id = element.element_type_id
117 and entry.element_entry_id = entval.element_entry_id (+)
118 and (entval.element_entry_value_id is null
119 or pay_action.effective_date between entval.effective_start_date
120 and entval.effective_end_date)
121 and pay_action.effective_date between entry.effective_start_date
122 and entry.effective_end_date
123 and pay_action.effective_date between link.effective_start_date
124 and link.effective_end_date
125 and pay_action.effective_date between element.effective_start_date
126 and element.effective_end_date
127 and element.classification_id = class.classification_id
128 /* and the element is an earnings element */
129 and class.classification_name in ( 'Earnings',
130 'Imputed Earnings',
131 'Supplemental Earnings')
132 /* and the element is in a US legislation */
133 and (element.legislation_code = 'US'
134 or (element.legislation_code is null
135 and exists (
136 select 1
137 from per_business_groups_perf BIZ_GRP
138 where element.business_group_id = biz_grp.business_group_id
139 and biz_grp.legislation_code = 'US')))
140 /* and there is or is not an input value indicating that the entry must be
141 processed separately, depending upon the parameter */
142 and (nvl (PI_LOOK_FOR_SEPARATE_CHECK, 'NO') = 'YES'
143 and exists (
144 select 1
145 from pay_input_values_f INPVAL
146 where inpval.element_type_id = element.element_type_id
147 and ((upper (inpval.name) in ( 'SEPARATE CHECK',
148 'TAX SEPARATELY')
149 and entval.screen_entry_value = 'Y')
150 or (upper (inpval.name) = 'DEDUCTION PROCESSING'
151 and entval.screen_entry_value in ('T', 'PTT')))
152 and inpval.input_value_id = entval.input_value_id
153 and pay_action.effective_date
154 between inpval.effective_start_date
155 and inpval.effective_end_date)
156 or (nvl (PI_LOOK_FOR_SEPARATE_CHECK, 'NO') <> 'YES'
157 and NOT exists (
158 select 1
159 from pay_input_values_f INPVAL
160 where inpval.element_type_id = element.element_type_id
161 and ((upper (inpval.name) in ( 'SEPARATE CHECK',
162 'TAX SEPARATELY')
163 and entval.screen_entry_value = 'Y')
164 or (upper (inpval.name) = 'DEDUCTION PROCESSING'
165 and entval.screen_entry_value in ('T', 'PTT')))
166 and inpval.input_value_id = entval.input_value_id
167 and pay_action.effective_date
168 between inpval.effective_start_date
169 and inpval.effective_end_date)));
170
171 --
172 -- Enhancement 3368211
173 -- This is the updated version of csr_earnings to be used with the new
174 -- QuickPay Exclusions model
175 --
176 cursor csr_earnings (
177
178 -- Get the number of earnings entries in the quickpay run
179
180 pi_look_for_separate_check in varchar2 default 'YES'
181
182 -- If parameter set to YES then look only for entries that must be processed
183 -- separately, otherwise look only for entries without that requirement.
184
185 ) is
186
187 select /*+ ORDERED INDEX(PAY_ACTION PAY_PAYROLL_ACTIONS_PK)
188 INDEX(ASGT_ACTION PAY_ASSIGNMENT_ACTIONS_N50)
189 INDEX(ENTRY PAY_ELEMENT_ENTRIES_F_N50)
190 INDEX(LINK PAY_ELEMENT_LINKS_F_PK)
191 INDEX(ELEMENT PAY_ELEMENT_TYPES_F_PK)
192 INDEX(CLASS PAY_ELEMENT_CLASSIFICATION_PK)
193 INDEX(ENTVAL PAY_ELEMENT_ENTRY_VALUES_F_N50)
194 USE_NL(PAY_ACTION, ASGT_ACTION) USE_NL(ENTRY, LINK)
195 USE_NL(LINK, ELEMENT) USE_NL(ELEMENT, CLASS)
196 USE_NL(CLASS, ENTVAL) */
197 count (*) TOTAL_OF_ROWS
198 from pay_payroll_actions PAY_ACTION,
199 pay_assignment_actions ASGT_ACTION,
200 pay_element_entries_f ENTRY,
201 pay_element_links_f LINK,
202 pay_element_types_f ELEMENT,
203 pay_element_classifications CLASS,
204 pay_element_entry_values_f ENTVAL
205 where pay_action.payroll_action_id = PI_PAYROLL_ACTION_ID
206 and asgt_action.payroll_action_id = pay_action.payroll_action_id
207 and asgt_action.assignment_id = entry.assignment_id
208 /* and entry doesn't exist in Pay_Quickpay_Exclusions */
209 and not (
210 exists (
211 select 'x'
212 from pay_quickpay_exclusions QP_EXCL
213 where qp_excl.assignment_action_id = asgt_action.assignment_action_id
214 and qp_excl.element_entry_id = entry.element_entry_id
215 )
216 )
217 and entry.element_link_id = link.element_link_id
218 and link.element_type_id = element.element_type_id
219 and entry.element_entry_id = entval.element_entry_id (+)
220 and (entval.element_entry_value_id is null
221 or pay_action.effective_date between entval.effective_start_date
222 and entval.effective_end_date)
223 and pay_action.effective_date between entry.effective_start_date
224 and entry.effective_end_date
225 and pay_action.effective_date between link.effective_start_date
226 and link.effective_end_date
227 and pay_action.effective_date between element.effective_start_date
228 and element.effective_end_date
229 and element.classification_id = class.classification_id
230 /* and the element is an earnings element */
231 and class.classification_name in ( 'Earnings',
232 'Imputed Earnings',
233 'Supplemental Earnings')
234 /* and the element is in a US legislation */
235 and (element.legislation_code = 'US'
236 or (element.legislation_code is null
237 and exists (
238 select 1
239 from per_business_groups_perf BIZ_GRP
240 where element.business_group_id = biz_grp.business_group_id
241 and biz_grp.legislation_code = 'US')))
242 /* and there is or is not an input value indicating that the entry must be
243 processed separately, depending upon the parameter */
244 and (nvl (PI_LOOK_FOR_SEPARATE_CHECK, 'NO') = 'YES'
245 and exists (
246 select 1
247 from pay_input_values_f INPVAL
248 where inpval.element_type_id = element.element_type_id
249 and ((upper (inpval.name) in ( 'SEPARATE CHECK',
250 'TAX SEPARATELY')
251 and entval.screen_entry_value = 'Y')
252 or (upper (inpval.name) = 'DEDUCTION PROCESSING'
253 and entval.screen_entry_value in ('T', 'PTT')))
254 and inpval.input_value_id = entval.input_value_id
255 and pay_action.effective_date
256 between inpval.effective_start_date
257 and inpval.effective_end_date)
258 or (nvl (PI_LOOK_FOR_SEPARATE_CHECK, 'NO') <> 'YES'
259 and NOT exists (
260 select 1
261 from pay_input_values_f INPVAL
262 where inpval.element_type_id = element.element_type_id
263 and ((upper (inpval.name) in ( 'SEPARATE CHECK',
264 'TAX SEPARATELY')
265 and entval.screen_entry_value = 'Y')
266 or (upper (inpval.name) = 'DEDUCTION PROCESSING'
267 and entval.screen_entry_value in ('T', 'PTT')))
268 and inpval.input_value_id = entval.input_value_id
269 and pay_action.effective_date
270 between inpval.effective_start_date
271 and inpval.effective_end_date)));
272
273 l_separate_check_earnings csr_earnings%rowtype;
274 l_collective_check_earnings csr_earnings%rowtype;
275
276 too_many_separate_checks exception;
277 -- It is not allowed to have more than one 'separate check' entry in
278 -- the same payroll run
279 separate_check_not_separate exception;
280 -- It is not allowed to have a 'separate check' entry in the payroll
281 -- run together with other earnings entries.
282
283 procedure step
284 -- Mark the step reached and output the debug location
285 (
286 pi_step in natural,
287 -- The point within the code that was reached
288
289 pi_message_prefix in varchar2 default null
290 --Text to be prepended to the location text
291
292 ) is
293
294 lpi_message_prefix varchar2 (80) := pi_message_prefix || ' ';
295
296 begin
297
298 l_step_reached := to_char (pi_step);
299
300 hr_utility.set_location (lpi_message_prefix || l_procedure_name,
301 l_step_reached);
302
303 end step;
304
305 BEGIN
306
307 step (1, 'Entering');
308 hr_general.assert_condition (all_parameters_are_valid);
309
310 step (2);
311
312 -- Get the number of earnings entries included in the run that must be
313 -- separately processed
314 --
315 -- Enhancement 3368211
316 -- First, check which version of csr_earnings we should be using
317 if use_qpay_excl_model = 'Y' then
318 open csr_earnings (pi_look_for_separate_check => 'YES');
319 fetch csr_earnings into l_separate_check_earnings;
320 close csr_earnings;
321 else
322 open csr_earnings_old (pi_look_for_separate_check => 'YES');
323 fetch csr_earnings_old into l_separate_check_earnings;
324 close csr_earnings_old;
325 end if;
326
327 step(3);
328
329 if
330 -- if there is one entry which must be separately processed
331 l_separate_check_earnings.total_of_rows = 1
332 then
333
334 step(4);
335
336 -- Get the number of earnings entries included in the run that do NOT require
337 -- to be separately processed.
338 --
339 -- Enhancement 3368211
340 -- First, check which version of csr_earnings we should be using
341 if use_qpay_excl_model = 'Y' then
342 open csr_earnings (pi_look_for_separate_check => 'NO');
343 fetch csr_earnings into l_collective_check_earnings;
344 close csr_earnings;
345 else
346 open csr_earnings_old (pi_look_for_separate_check => 'NO');
347 fetch csr_earnings_old into l_collective_check_earnings;
348 close csr_earnings_old;
349 end if;
350
351 if
352 -- if there is any earnings entry which should NOT be separately processed
353 l_collective_check_earnings.total_of_rows > 0
354 then
355
356 step (5);
357 raise separate_check_not_separate;
358
359 end if;
360
361 elsif
362 -- if there is more than one entry which must be separately processed
363 l_separate_check_earnings.total_of_rows > 1
364 then
365
366 step (6);
367 raise too_many_separate_checks;
368
369 end if;
370
371 step (7, 'Leaving');
372
373 EXCEPTION
374
375 when value_error
376 then
377
378 -- Probably caused by one of the conditions specified in the variable
379 -- all_parameters_are_valid being FALSE. This is a trap for invalid
380 -- parameter values.
381
382 fnd_message.set_name ('PAY','HR_6153_ALL_PROCEDURE_FAIL');
383 fnd_message.set_token ('PROCEDURE',l_procedure_name);
384 fnd_message.set_token ('STEP',l_step_reached);
385 fnd_message.raise_error;
386
387 when separate_check_not_separate
388 or too_many_separate_checks
389 then
390
391 fnd_message.set_name ('PAY','HR_51295_QP_MIXED_TAXSEP');
392 fnd_message.raise_error;
393
394 END control_separate_check_entries;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |-------------------------< chk_asg_on_payroll >---------------------------|
398 -- ----------------------------------------------------------------------------
399 --
400 procedure chk_asg_on_payroll
401 (p_assignment_id in pay_assignment_actions.assignment_id%TYPE
402 ) is
403 --
404 v_exists varchar2(1);
405 v_proc varchar2(72) := g_package||'chk_asg_on_payroll';
406 v_argument varchar2(30);
407 --
408 cursor pay_asg is
409 select 'Y'
410 from fnd_sessions ses
411 , per_assignments_f asg
412 where ses.session_id = userenv('SESSIONID')
413 and ses.effective_date between asg.effective_start_date
414 and asg.effective_end_date
415 and asg.payroll_id is not null
416 and asg.assignment_id = p_assignment_id;
417 begin
418 hr_utility.set_location('Entering:'|| v_proc, 5);
419 --
420 -- Check mandatory parameters have been set
421 --
422 hr_api.mandatory_arg_error
423 (p_api_name => v_proc
424 ,p_argument => 'assignment_id'
425 ,p_argument_value => p_assignment_id
426 );
427 --
428 -- Check the assignment is on the payroll as of the effective date
429 --
430 open pay_asg;
431 fetch pay_asg into v_exists;
432 if pay_asg%notfound then
433 close pay_asg;
434 -- Error: You have tried to define QuickPay for an assignment that has no
435 -- payroll component valid at Date Paid. For the run to process, you
436 -- must create a payroll component that is valid at this date.
437 hr_utility.set_message(801, 'HR_7242_QPAY_NO_PAY_D_PAID');
438 hr_utility.raise_error;
439 end if;
440 close pay_asg;
441 --
442 hr_utility.set_location(' Leaving:'|| v_proc, 10);
443 end chk_asg_on_payroll;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |--------------------------< chk_new_eff_date >----------------------------|
447 -- ----------------------------------------------------------------------------
448 --
449 procedure chk_new_eff_date
450 (p_assignment_id in varchar2
451 ,p_new_date in varchar2
452 ) is
453 --
454 v_exists varchar2(1);
455 v_proc varchar2(72) := g_package||'chk_new_eff_date';
456 v_argument varchar2(30);
457 --
458 cursor cur_dat (v_assignment_id number, v_new_date date) is
459 select 'Y'
460 from per_assignments_f asg
461 where asg.assignment_id = v_assignment_id
462 and asg.payroll_id is not null
463 and v_new_date between asg.effective_start_date
464 and asg.effective_end_date;
465 begin
466 hr_utility.set_location('Entering:'|| v_proc, 5);
467 --
468 -- Check mandatory parameters have been set
469 --
470 hr_api.mandatory_arg_error
471 (p_api_name => v_proc
472 ,p_argument => 'assignment_id'
473 ,p_argument_value => p_assignment_id
474 );
475 hr_api.mandatory_arg_error
476 (p_api_name => v_proc
477 ,p_argument => 'new_date'
478 ,p_argument_value => p_new_date
479 );
480 --
481 -- Check that the assignment is on a payroll as of
482 -- the proposed effective date
483 --
484 open cur_dat(fnd_number.canonical_to_number(p_assignment_id), fnd_date.canonical_to_date(p_new_date));
485 fetch cur_dat into v_exists;
486 if cur_dat%notfound then
487 close cur_dat;
488 -- Error: You cannot change to this effective date, because the current
489 -- assignment is not on a payroll as of the new date.
490 hr_utility.set_message(801, 'HR_7243_QPAY_NOT_PAY_NEW_DATE');
491 hr_utility.raise_error;
492 end if;
493 close cur_dat;
494 --
495 hr_utility.set_location(' Leaving:'|| v_proc, 10);
496 end chk_new_eff_date;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |----------------------------< chk_assignment >----------------------------|
500 -- ----------------------------------------------------------------------------
501 -- {Start Of Comments}
502 --
503 -- Description:
504 -- Validates that the assignment is in the same business group as the
505 -- QuickPay, as of a particular date.
506 --
507 -- Pre Conditions:
508 -- p_business_group_id is known to be an existing business group.
509 -- p_assignment_id is known to be a valid assignment which exists at some
510 -- point in time.
511 --
512 -- In Arguments:
513 -- p_assignment_id the assignment to check.
514 -- p_business_group_id the business group to check.
515 -- p_effective_date the date the assignment must exist.
516 --
517 -- Post Success:
518 -- End normally if the assignment exists as of p_effective_date and is in
519 -- business group p_business_group_id.
520 --
521 -- Post Failure:
522 -- Raises an application error if the assignment does not exist in
523 -- p_business_group_id at p_effective_date.
524 --
525 -- Access Status:
526 -- Internal Development Use Only.
527 --
528 -- {End Of Comments}
529 --
530 procedure chk_assignment
531 (p_assignment_id in pay_assignment_actions.assignment_id%TYPE
532 ,p_business_group_id in pay_payroll_actions.business_group_id%TYPE
533 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
534 ) is
535 --
536 v_exists varchar2(1);
537 v_proc varchar2(72) := g_package||'chk_assignment';
538 --
539 cursor sel_asg is
540 select 'Y'
541 from per_assignments_f
542 where assignment_id = p_assignment_id
543 and business_group_id + 0 = p_business_group_id
544 and p_effective_date between effective_start_date
545 and effective_end_date;
546 begin
547 hr_utility.set_location('Entering:'|| v_proc, 5);
548 --
549 open sel_asg;
550 fetch sel_asg into v_exists;
551 if sel_asg%notfound then
552 close sel_asg;
553 -- Error: You have tried to define QuickPay for an assignment that is to a
554 -- different Business Group, or does not exist at Date Paid.
555 hr_utility.set_message(801, 'HR_7244_QPAY_NO_PAY_D_EFF');
556 hr_utility.raise_error;
557 end if;
558 close sel_asg;
559 --
560 hr_utility.set_location(' Leaving:'|| v_proc, 10);
561 end chk_assignment;
562 --
563 -- ----------------------------------------------------------------------------
564 -- |----------------------------< chk_consol_set >----------------------------|
565 -- ----------------------------------------------------------------------------
566 -- {Start Of Comments}
567 --
568 -- Description:
569 -- Validates that a consolidation set exists in a particular business group.
570 --
571 -- Pre Conditions:
572 -- p_business_group_id is known to be an existing business group.
573 --
574 -- In Arguments:
575 -- p_consolidation_set_id the consolidation set to check.
576 -- p_business_group_id the id of the business group to check the
577 -- consolidation set is in.
578 --
579 -- Post Success:
580 -- Ends normally if the consolidation set (p_consolidation_set_id) exists
581 -- and is in the same business group as p_business_group_id.
582 --
583 -- Post Failure:
584 -- An application error is raised if any of the following are true:
585 -- 1) A consolidation_set does not exist with an id of
586 -- p_consolidation_set_id.
587 -- 2) The p_consolidation_set_id does exist but it is not in the same
588 -- business group as p_business_group_id.
589 --
590 -- Access Status:
591 -- Internal Development Use Only.
592 --
593 -- {End Of Comments}
594 --
595 procedure chk_consol_set
596 (p_consolidation_set_id in pay_consolidation_sets.consolidation_set_id%TYPE
597 ,p_business_group_id in pay_consolidation_sets.business_group_id%TYPE
598 ) is
599 --
600 v_exists varchar2(1);
601 v_proc varchar2(72) := g_package||'chk_consol_set';
602 --
603 cursor sel_set is
604 select 'Y'
605 from pay_consolidation_sets
606 where consolidation_set_id = p_consolidation_set_id
607 and business_group_id + 0 = p_business_group_id;
608 begin
609 hr_utility.set_location('Entering:'|| v_proc, 5);
610 --
611 open sel_set;
612 fetch sel_set into v_exists;
613 if sel_set%notfound then
614 close sel_set;
615 -- Error: You have tried to define QuickPay for a consolidation set that
616 -- does not exist, or does not exist in the Business Group defined for the
617 -- run.
618 hr_utility.set_message(801, 'HR_7245_QPAY_INVAL_CON_SET');
619 hr_utility.raise_error;
620 end if;
621 close sel_set;
622 --
623 hr_utility.set_location(' Leaving:'|| v_proc, 10);
624 end chk_consol_set;
625 --
626 -- ----------------------------------------------------------------------------
627 -- |---------------------------< chk_period_exists >--------------------------|
628 -- ----------------------------------------------------------------------------
629 -- {Start Of Comments}
630 --
631 -- Description:
632 -- Validates that a time period exists for a payroll as of a specified
633 -- date. Used for validating that a period exists for the QuickPay date paid
634 -- and date earned.
635 --
636 -- Pre Conditions:
637 -- p_payroll_id is known to be a valid payroll which does exist as of p_date.
638 -- p_message exists in the AOL message dictionary.
639 --
640 -- In Arguments:
641 -- p_date is the date to check for,
642 -- p_payroll_id is payroll to check there is a time period for as of p_date.
643 -- p_message is set to message_name to be raised if a period does
644 -- not exist.
645 --
646 -- Post Success:
647 -- p_time_period_id is set to per_time_periods.time_period_id and
648 -- p_period_name is set to per_time_periods.period_name for the period
649 -- which exists as of p_date for the payroll p_payroll_id.
650 --
651 -- Post Failure:
652 -- The application error p_message is raised if a time period does not
653 -- exist for the payroll as of p_date. The values of p_time_period_id and
654 -- p_period_name are undefined as the end of the procedure is not reached.
655 --
656 -- Access Status:
657 -- Internal Development Use Only.
658 --
659 -- {End Of Comments}
660 --
661 procedure chk_period_exists
662 (p_date in date
663 ,p_payroll_id in per_time_periods.payroll_id%TYPE
664 ,p_message in varchar2
665 ,p_time_period_id out nocopy per_time_periods.time_period_id%TYPE
666 ,p_period_name out nocopy per_time_periods.period_name%TYPE
667 ) is
668 --
669 v_time_period_id per_time_periods.time_period_id%TYPE;
670 v_period_name per_time_periods.period_name%TYPE;
671 v_proc varchar2(72) := g_package||'chk_period_exists';
672 --
673 cursor sel_tim is
674 select time_period_id
675 , period_name
676 from per_time_periods
677 where payroll_id = p_payroll_id
678 and p_date between start_date
679 and end_date;
680 --
681 begin
682 hr_utility.set_location('Entering:'|| v_proc, 5);
683 --
684 open sel_tim;
685 fetch sel_tim into v_time_period_id, v_period_name;
686 if sel_tim%notfound then
687 --
688 -- Could not find a row in per_time_periods which matches
689 -- p_date and p_payroll_id. So need to raise the error p_message.
690 --
691 close sel_tim;
692 hr_utility.set_message(801, p_message);
693 hr_utility.raise_error;
694 end if;
695 close sel_tim;
696 --
697 p_time_period_id := v_time_period_id;
698 p_period_name := v_period_name;
699 --
700 hr_utility.set_location(' Leaving:'|| v_proc, 10);
701 end chk_period_exists;
702 --
703 -- ----------------------------------------------------------------------------
704 -- |--------------------< chk_period_exists_eff_for_gb >----------------------|
705 -- ----------------------------------------------------------------------------
706 -- {Start Of Comments}
707 --
708 -- Description:
709 -- Validates that a time period exists for a payroll as of a specified
710 -- date. Used for validating that a period exists for the QuickPay date paid
711 -- and date earned.
712 --
713 -- Pre Conditions:
714 -- p_payroll_id is known to be a valid payroll which does exist as of p_date.
715 -- p_message exists in the AOL message dictionary.
716 --
717 -- In Arguments:
718 -- p_date is the date to check for,
719 -- p_payroll_id is payroll to check there is a time period for as of p_date.
720 -- p_message is set to message_name to be raised if a period does
721 -- not exist.
722 --
723 -- Post Success:
724 -- p_time_period_id is set to per_time_periods.time_period_id and
725 -- p_period_name is set to per_time_periods.period_name for the period
726 -- which exists as of p_date for the payroll p_payroll_id.
727 --
728 -- Post Failure:
729 -- The application error p_message is raised if a time period does not
730 -- exist for the payroll as of p_date. The values of p_time_period_id and
731 -- p_period_name are undefined as the end of the procedure is not reached.
732 --
733 -- Access Status:
734 -- Internal Development Use Only.
735 --
736 -- {End Of Comments}
737 --
738 procedure chk_period_exists_eff_for_gb
739 (p_date in date
740 ,p_payroll_id in per_time_periods.payroll_id%TYPE
741 ,p_message in varchar2
742 ,p_time_period_id out nocopy per_time_periods.time_period_id%TYPE
743 ,p_period_name out nocopy per_time_periods.period_name%TYPE
744 ) is
745 --
746 v_time_period_id per_time_periods.time_period_id%TYPE;
747 v_period_name per_time_periods.period_name%TYPE;
748 v_proc varchar2(72) := g_package||'chk_period_exists';
749 --
750 cursor sel_tim is
751 select time_period_id
752 , period_name
753 from per_time_periods
754 where payroll_id = p_payroll_id
755 and p_date = regular_payment_date;
756 --
757 begin
758 hr_utility.set_location('Entering:'|| v_proc, 5);
759 --
760 open sel_tim;
761 fetch sel_tim into v_time_period_id, v_period_name;
762 if sel_tim%notfound then
763 --
764 -- Could not find a row in per_time_periods which matches
765 -- p_date and p_payroll_id. So need to raise the error p_message.
766 --
767 close sel_tim;
768 hr_utility.set_message(801, p_message);
769 hr_utility.raise_error;
770 end if;
771 close sel_tim;
772 --
773 p_time_period_id := v_time_period_id;
774 p_period_name := v_period_name;
775 --
776 hr_utility.set_location(' Leaving:'|| v_proc, 10);
777 end chk_period_exists_eff_for_gb;
778 --
779 -- ----------------------------------------------------------------------------
780 -- |------------------------< get_date_earned_for_gb >------------------------|
781 -- ----------------------------------------------------------------------------
782 -- {Start Of Comments}
783 --
784 -- Description:
785 -- Derives the date_earned value for the GB legislation.
786 --
787 -- Pre Conditions:
788 -- The current business group is known to be for GB legislation.
789 -- Assumes the assignment exists and is on a payroll as of p_effective_date.
790 --
791 -- In Arguments:
792 -- p_effective_date set to the date paid value the user has entered or
793 -- the GB default value.
794 --
795 -- Post Success:
796 -- Returns the derived value for date_earned. If the default date earned
797 -- cannot be derived, then p_gb_date_earned will be set to null.
798 --
799 -- Post Failure:
800 -- None.
801 --
802 -- Access Status:
803 -- Internal Development Use Only.
804 --
805 -- {End Of Comments}
806 --
807 function get_date_earned_for_gb
808 (p_assignment_id in pay_assignment_actions.assignment_id%TYPE
809 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
810 ) return pay_payroll_actions.date_earned%TYPE is
811 --
812 -- Cursor definitions
813 --
814 -- Used to find the start and end dates of the matching period
815 --
816 cursor csr_per_dat is
817 select p.start_date
818 , p.end_date
819 , a.payroll_id
820 from per_time_periods p
821 , per_assignments_f a
822 where a.assignment_id = p_assignment_id
823 and p_effective_date between a.effective_start_date
824 and a.effective_end_date
825 and p.payroll_id = a.payroll_id
826 and p_effective_date = p.regular_payment_date;
827 --
828 -- Used to find all the assignment rows which overlap the
829 -- current time period.
830 --
831 cursor csr_per_asg (v_period_start date
832 ,v_period_end date
833 ) is
834 select a.effective_start_date
835 , a.effective_end_date
836 , a.payroll_id
837 from per_assignments_f a
838 where a.assignment_id = p_assignment_id
839 and a.effective_end_date >= v_period_start
840 and a.effective_start_date <= v_period_end
841 order by a.effective_start_date;
842 --
843 -- Local variables
844 --
845 -- The start date of the period which matches the assignment's payroll and
846 -- p_effective_date.
847 v_period_start_date date;
848 --
849 -- The end date of the period which matches the assignment's payroll and
850 -- p_effective_date.
851 v_period_end_date date;
852 --
853 -- The assignment's payroll as of p_effective_date.
854 v_cur_payroll_id per_assignments_f.payroll_id%TYPE;
855 --
856 -- Working value for the date earned. The best value so far given the
857 -- the processed records.
858 v_wrk_date date := null;
859 v_proc varchar2(72) := g_package||'get_date_earned_for_gb';
860 begin
861 hr_utility.set_location('Entering:'|| v_proc, 5);
862 --
863 -- Get the period start, end dates and the assignment's current payroll.
864 --
865 open csr_per_dat;
866 fetch csr_per_dat into v_period_start_date
867 , v_period_end_date
868 , v_cur_payroll_id;
869 close csr_per_dat;
870 --
871 -- Loop for assignment records which overlap with the current period
872 --
873 <<csr_per_asg_loop>>
874 for cur_asg IN csr_per_asg (v_period_start_date, v_period_end_date) loop
875 --
876 if cur_asg.effective_end_date <= v_period_end_date and
877 cur_asg.payroll_id = v_cur_payroll_id then
878 --
879 -- Use the date for the current assignment record only if the record
880 -- does not last after the end of the periods and the payroll_id
881 -- matches the current payroll.
882 --
883 v_wrk_date := cur_asg.effective_end_date;
884 hr_utility.set_location(v_proc, 6);
885 --
886 elsif cur_asg.payroll_id = v_cur_payroll_id then
887 --
888 -- If the assignment is still on the same payroll at the end of
889 -- the period, then use the end of period date.
890 --
891 v_wrk_date := v_period_end_date;
892 hr_utility.set_location(v_proc, 7);
893 --
894 else
895 --
896 -- The current assignment is not on the same payroll as of the
897 -- driving date.
898 --
899 null;
900 hr_utility.set_location(v_proc, 8);
901 end if;
902 --
903 end loop csr_per_asg_loop;
904 --
905 hr_utility.set_location(' Leaving:'|| v_proc, 10);
906 --
907 return v_wrk_date;
908 end get_date_earned_for_gb;
909 --
910 -- ----------------------------------------------------------------------------
911 -- |-------------------------< chk_eff_date_for_gb >--------------------------|
912 -- ----------------------------------------------------------------------------
913 -- {Start Of Comments}
914 --
915 -- Description:
916 -- This procedure contains additional GB specific validation for the
917 -- QuickPay Run effective_date attribute. It should not called directly.
918 -- It is a supporting procedure for chk_eff_date only.
919 --
920 -- Pre Conditions:
921 -- p_time_period_id is the id of a time period which is known to exist
922 -- as of p_effective_date.
923 --
924 -- In Arguments:
925 -- p_effective_date the date paid for the QuickPay Run Payroll Process.
926 -- p_time_period_id set to the time period which is current as of
927 -- p_effective_date for the current assignment's payroll.
928 --
929 -- Post Success:
930 -- Ends normally if p_effective_date corresponds to the regular payment
931 -- date for the current time period.
932 --
933 -- Post Failure:
934 -- An application error message is raised if p_effective_date does not
935 -- equal the regular payment date for the current time period.
936 --
937 -- Access Status:
938 -- Internal Development Use Only.
939 --
940 -- {End Of Comments}
941 --
942 procedure chk_eff_date_for_gb
943 (p_effective_date in pay_payroll_actions.effective_date%TYPE
944 ,p_time_period_id in pay_payroll_actions.time_period_id%TYPE
945 ) is
946 --
947 v_exists varchar2(1);
948 v_proc varchar2(72) := g_package||'chk_eff_date_for_gb';
949 --
950 cursor sel_tim is
951 select 'Y'
952 from per_time_periods
953 where time_period_id = p_time_period_id
954 and regular_payment_date = p_effective_date;
955 begin
956 hr_utility.set_location('Entering:'|| v_proc, 5);
957 --
958 open sel_tim;
959 fetch sel_tim into v_exists;
960 if sel_tim%notfound then
961 close sel_tim;
962 -- Error: The QuickPay Date Paid must be the same as a payroll
963 -- period regular payment date.
964 hr_utility.set_message(801, 'HR_7286_QPAY_EFF_FOR_GB');
965 hr_utility.raise_error;
966 end if;
967 close sel_tim;
968 --
969 hr_utility.set_location(' Leaving:'|| v_proc, 10);
970 end chk_eff_date_for_gb;
971 --
972 -- ----------------------------------------------------------------------------
973 -- |-----------------------------< chk_eff_date >-----------------------------|
974 -- ----------------------------------------------------------------------------
975 --
976 procedure chk_eff_date
977 (p_effective_date in pay_payroll_actions.effective_date%TYPE
978 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
979 ,p_legislation_code in per_business_groups.legislation_code%TYPE
980 ,p_recal_date_earned in boolean
981 ,p_payroll_id out nocopy pay_payroll_actions.payroll_id%TYPE
982 ,p_time_period_id out nocopy pay_payroll_actions.time_period_id%TYPE
983 ,p_period_name out nocopy per_time_periods.period_name%TYPE
984 ,p_new_date_earned out nocopy pay_payroll_actions.date_earned%TYPE
985 ) is
986 --
987 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
988 v_eff_time_period_id per_time_periods.time_period_id%TYPE;
989 v_eff_time_period_name per_time_periods.period_name%TYPE;
990 v_new_date_earned pay_payroll_actions.date_earned%TYPE;
991 v_proc varchar2(72) := g_package||'chk_eff_date';
992 v_rule_mode pay_legislation_rules.rule_mode%type;
993 --
994 -- Used to find out if the assingment is on a payroll as p_effective_date
995 --
996 cursor sel_pay is
997 select asg.payroll_id
998 from per_assignments_f asg
999 where /* Payroll as of effective date */
1000 asg.assignment_id = p_assignment_id
1001 and asg.payroll_id is not null
1002 and p_effective_date between asg.effective_start_date
1003 and asg.effective_end_date;
1004 --
1005 cursor get_leg_rule is
1006 select rule_mode
1007 from pay_legislation_rules
1008 where rule_type = 'ENABLE_QP_OFFSET'
1009 and legislation_code = p_legislation_code;
1010 --
1011 begin
1012 hr_utility.set_location('Entering:'|| v_proc, 5);
1013 --
1014 -- Check mandatory arguments have been set
1015 --
1016 hr_api.mandatory_arg_error
1017 (p_api_name => v_proc
1018 ,p_argument => 'effective_date'
1019 ,p_argument_value => p_effective_date
1020 );
1021 hr_api.mandatory_arg_error
1022 (p_api_name => v_proc
1023 ,p_argument => 'assignment_id'
1024 ,p_argument_value => p_assignment_id
1025 );
1026 hr_api.mandatory_arg_error
1027 (p_api_name => v_proc
1028 ,p_argument => 'legislation_code'
1029 ,p_argument_value => p_legislation_code
1030 );
1031 --
1032 -- Ensure the assignment is on a payroll as of the effective_date.
1033 --
1034 open sel_pay;
1035 fetch sel_pay into v_payroll_id;
1036 if sel_pay%notfound then
1037 close sel_pay;
1038 -- Error: You have tried to define QuickPay for an assignment that has no
1039 -- payroll component defined at Date Paid.
1040 hr_utility.set_message(801, 'HR_7246_QPAY_ASG_PAY_D_PAID');
1041 hr_utility.raise_error;
1042 end if;
1043 close sel_pay;
1044 hr_utility.set_location(v_proc, 6);
1045 --
1046 -- Possible Error to check for: You have tried to define QuickPay for an
1047 -- assignment that has no payroll period defined at Date Paid.
1048 --
1049 --
1050 -- Check ENABLE_QP_OFFSET legislation_rule. If leg rule does not exist
1051 -- or rule_mode = 'N', use eff date to derive period, else use
1052 -- regular payment date
1053 --
1054 open get_leg_rule;
1055 fetch get_leg_rule into v_rule_mode;
1056 if (get_leg_rule%notfound or v_rule_mode = 'N') then
1057 chk_period_exists
1058 (p_date => p_effective_date
1059 ,p_payroll_id => v_payroll_id
1060 ,p_message => 'HR_7247_QPAY_NO_PERIOD_D_PAID'
1061 ,p_time_period_id => v_eff_time_period_id
1062 ,p_period_name => v_eff_time_period_name
1063 );
1064 else
1065 chk_period_exists_eff_for_gb
1066 (p_date => p_effective_date
1067 ,p_payroll_id => v_payroll_id
1068 ,p_message => 'HR_7247_QPAY_NO_PERIOD_D_PAID'
1069 ,p_time_period_id => v_eff_time_period_id
1070 ,p_period_name => v_eff_time_period_name
1071 );
1072 --
1073 chk_eff_date_for_gb
1074 (p_effective_date => p_effective_date
1075 ,p_time_period_id => v_eff_time_period_id
1076 );
1077 --
1078 end if;
1079 hr_utility.set_location(v_proc, 8);
1080 close get_leg_rule;
1081 --
1082 -- If required re-calculate new default value for date_earned
1083 --
1084 if p_recal_date_earned then
1085 if p_legislation_code = 'GB' then
1086 --
1087 -- Re-calculate for GB legislation
1088 --
1089 v_new_date_earned := get_date_earned_for_gb
1090 (p_assignment_id => p_assignment_id
1091 ,p_effective_date => p_effective_date
1092 );
1093 else
1094 --
1095 -- Re-calculate for non-GB legislations
1096 --
1097 v_new_date_earned := p_effective_date;
1098 end if;
1099 else
1100 --
1101 -- The default should not be re-calculate
1102 --
1103 v_new_date_earned := null;
1104 end if;
1105 --
1106 p_payroll_id := v_payroll_id;
1107 p_time_period_id := v_eff_time_period_id;
1108 p_period_name := v_eff_time_period_name;
1109 p_new_date_earned := v_new_date_earned;
1110 --
1111 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1112 end chk_eff_date;
1113 --
1114 -- ----------------------------------------------------------------------------
1115 -- |------------------------< chk_date_earned_for_gb >------------------------|
1116 -- ----------------------------------------------------------------------------
1117 -- {Start Of Comments}
1118 --
1119 -- Description:
1120 -- This procedure contains additional GB specific validation for the
1121 -- QuickPay Run date_earned attribute. It should not called directly.
1122 -- It is a supporting procedure for chk_date_earned only.
1123 --
1124 -- Pre Conditions:
1125 -- p_effective_date has already been validated.
1126 --
1127 -- In Arguments:
1128 -- p_date_earned set user's value for date earned.
1129 -- p_assignment_id the current assignment.
1130 --
1131 -- Post Success:
1132 -- Ends normally if p_date_earned matches the value which must be used.
1133 --
1134 -- Post Failure:
1135 -- An application error message is raised if p_date_earned does not
1136 -- equal the default date_earned value.
1137 --
1138 -- Access Status:
1139 -- Internal Development Use Only.
1140 --
1141 -- {End Of Comments}
1142 --
1143 procedure chk_date_earned_for_gb
1144 (p_date_earned in pay_payroll_actions.date_earned%TYPE
1145 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
1146 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
1147 ) is
1148 --
1149 v_df_date_earned pay_payroll_actions.date_earned%TYPE;
1150 v_proc varchar2(72) := g_package||'chk_date_earned_for_gb';
1151 --
1152 begin
1153 hr_utility.set_location('Entering:'|| v_proc, 5);
1154 --
1155 -- Find out the default value for date earned
1156 --
1157 v_df_date_earned := get_date_earned_for_gb
1158 (p_assignment_id => p_assignment_id
1159 ,p_effective_date => p_effective_date
1160 );
1161 --
1162 -- If the value provided by the user is different to the default
1163 -- then raise an error.
1164 --
1165 if v_df_date_earned <> p_date_earned then
1166 -- Error: You must use the default value for the QuickPay Run Date Earned,
1167 -- when using GB legislation.
1168 hr_utility.set_message(801, 'HR_7288_QPAY_ERN_FOR_GB');
1169 hr_utility.raise_error;
1170 end if;
1171 --
1172 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1173 end chk_date_earned_for_gb;
1174 --
1175 -- ----------------------------------------------------------------------------
1176 -- |----------------------------< chk_date_earned >---------------------------|
1177 -- ----------------------------------------------------------------------------
1178 --
1179 procedure chk_date_earned
1180 (p_date_earned in pay_payroll_actions.date_earned%TYPE
1181 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
1182 ,p_legislation_code in per_business_groups.legislation_code%TYPE
1183 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
1184 ) is
1185 --
1186 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
1187 v_not_used_period_id per_time_periods.time_period_id%TYPE;
1188 v_not_used_period_name per_time_periods.period_name%TYPE;
1189 v_proc varchar2(72) := g_package||'chk_date_earned';
1190 v_argument varchar2(30);
1191 --
1192 cursor sel_pay is
1193 select asg.payroll_id
1194 from per_assignments_f asg
1195 where /* Payroll as of date_earned */
1196 asg.assignment_id = p_assignment_id
1197 and asg.payroll_id is not null
1198 and p_date_earned between asg.effective_start_date
1199 and asg.effective_end_date;
1200 --
1201 begin
1202 hr_utility.set_location('Entering:'|| v_proc, 5);
1203 --
1204 -- Check mandatory parameters have been set
1205 --
1206 hr_api.mandatory_arg_error
1207 (p_api_name => v_proc
1208 ,p_argument => 'date_earned'
1209 ,p_argument_value => p_date_earned
1210 );
1211 hr_api.mandatory_arg_error
1212 (p_api_name => v_proc
1213 ,p_argument => 'assignment_id'
1214 ,p_argument_value => p_assignment_id
1215 );
1216 hr_api.mandatory_arg_error
1217 (p_api_name => v_proc
1218 ,p_argument => 'legislation_code'
1219 ,p_argument_value => p_legislation_code
1220 );
1221 if p_legislation_code = 'GB' then
1222 hr_api.mandatory_arg_error
1223 (p_api_name => v_proc
1224 ,p_argument => 'effective_date'
1225 ,p_argument_value => p_effective_date
1226 );
1227 end if;
1228 --
1229 -- Ensure the assignment is on a payroll as of the date_earned.
1230 --
1231 open sel_pay;
1232 fetch sel_pay into v_payroll_id;
1233 if sel_pay%notfound then
1234 close sel_pay;
1235 -- Error: You have tried to define QuickPay for an assignment that has no
1236 -- payroll component defined at Date Earned.
1237 hr_utility.set_message(801, 'HR_7249_QPAY_ASG_PAY_D_EARNED');
1238 hr_utility.raise_error;
1239 end if;
1240 close sel_pay;
1241 hr_utility.set_location(v_proc, 6);
1242 --
1243 -- A time period must exist for the payroll
1244 -- as of date earned.
1245 --
1246 -- Possible error to check for: You have tried to define QuickPay for an
1247 -- assignment that has no payroll period defined at Date Earned.
1248 chk_period_exists
1249 (p_date => p_date_earned
1250 ,p_payroll_id => v_payroll_id
1251 ,p_message => 'HR_7250_QPAY_NO_PERIOD_D_EARN'
1252 ,p_time_period_id => v_not_used_period_id
1253 ,p_period_name => v_not_used_period_name
1254 );
1255 hr_utility.set_location(v_proc, 7);
1256 --
1257 -- For GB, carry out legislation specific checks.
1258 --
1259 if p_legislation_code = 'GB' then
1260 chk_date_earned_for_gb
1261 (p_date_earned => p_date_earned
1262 ,p_assignment_id => p_assignment_id
1263 ,p_effective_date => p_effective_date
1264 );
1265 end if;
1266 --
1267 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1268 end chk_date_earned;
1269 --
1270 -- ----------------------------------------------------------------------------
1271 -- |-----------------------< chk_eff_earned_payroll >-------------------------|
1272 -- ----------------------------------------------------------------------------
1273 -- {Start Of Comments}
1274 --
1275 -- Description:
1276 -- Validates the assignment is on the same payroll as of the effective_date
1277 -- and date_earned.
1278 --
1279 -- Pre Conditions:
1280 -- The assignment (p_assignment_id) is known to exist on a payroll as of
1281 -- p_effective_date and on a payroll as of p_date_earned.
1282 --
1283 -- In Arguments:
1284 -- p_date_earned is the QuickPay date earned.
1285 -- p_effective_date is the QuickPay date paid.
1286 -- p_assignment_id is the assignment to check.
1287 -- p_raise_error indicates if the an application error message should be
1288 -- raised if the assignment is on different payrolls as of the two dates.
1289 --
1290 -- Post Success:
1291 -- p_same_payroll is set to true if the assignment is on the same payroll
1292 -- as of date earned and date paid. Otherwise it is set to false.
1293 --
1294 -- Post Failure:
1295 -- If p_same_payroll will be set to false and p_raise_error is true an
1296 -- application error is raised.
1297 --
1298 -- Access Status:
1299 -- Internal Development Use Only.
1300 --
1301 -- {End Of Comments}
1302 --
1303 procedure chk_eff_earned_payroll
1304 (p_effective_date in pay_payroll_actions.effective_date%TYPE
1305 ,p_date_earned in pay_payroll_actions.date_earned%TYPE
1306 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
1307 ,p_raise_error in boolean
1308 ,p_same_payroll out nocopy boolean
1309 ) is
1310 --
1311 v_exists varchar2(1);
1312 v_proc varchar2(72) := g_package||'chk_eff_earned_payroll';
1313 --
1314 cursor sel_same is
1315 select 'Y'
1316 from per_assignments_f asg1
1317 , per_assignments_f asg2
1318 where /* Payroll as of date_earned */
1319 asg1.assignment_id = p_assignment_id
1320 and p_date_earned between asg1.effective_start_date
1321 and asg1.effective_end_date
1322 and asg1.payroll_id is not null
1323 /* Payroll as of the effective date */
1324 and asg2.assignment_id = p_assignment_id
1325 and p_effective_date between asg2.effective_start_date
1326 and asg2.effective_end_date
1327 and asg2.payroll_id is not null
1328 /* Payrolls are the same */
1329 and asg1.payroll_id = asg2.payroll_id;
1330 --
1331 begin
1332 hr_utility.set_location('Entering:'|| v_proc, 5);
1333 --
1334 -- Ensure the assignment is the same payroll
1335 -- as of the effective_date and date_earned.
1336 --
1337 open sel_same;
1338 fetch sel_same into v_exists;
1339 if sel_same%notfound then
1340 close sel_same;
1341 p_same_payroll := false;
1342 if p_raise_error then
1343 -- Error: You have tried to define QuickPay for an assignment that has
1344 -- different payroll components for Date Earned and Date Paid. The
1345 -- assignment must be to the same payroll for both dates.
1346 hr_utility.set_message(801, 'HR_7251_QPAY_DIFF_PAYROLLS');
1347 hr_utility.raise_error;
1348 end if;
1349 else
1350 close sel_same;
1351 p_same_payroll := true;
1352 end if;
1353 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1354 end chk_eff_earned_payroll;
1355 --
1356 -- ----------------------------------------------------------------------------
1357 -- |-------------------------< chk_action_status >----------------------------|
1358 -- ----------------------------------------------------------------------------
1359 -- {Start Of Comments}
1360 --
1361 -- Description:
1362 -- This procedure is used check the action_status has only been updated
1363 -- from 'C' (for Complete) to 'M' (for Mark for Retry). This is the only
1364 -- update which the user is allowed to do. All other action_status updates
1365 -- are only allowed from the Pre-payment process code.
1366 --
1367 -- Pre Conditions:
1368 -- None.
1369 --
1370 -- In Arguments:
1371 -- p_payroll_action_id is the id of the QuickPay Run being updated.
1372 -- p_old_action_status is set to the existing action_status in the database.
1373 -- p_new_action_status is set to the user's proposed new action_status value.
1374 --
1375 -- Post Success:
1376 -- Processing continues if the update is 'C' to 'M' and there are no other
1377 -- action interlocks preventing the update. Any run result details will be
1378 -- deleted.
1379 --
1380 -- Post Failure:
1381 -- An application error is raised if the user is trying to do any other
1382 -- update (i.e. not 'C' to 'M'). Also an error is raised if there are any
1383 -- action interlocks preventing the update.
1384 --
1385 -- Access Status:
1386 -- Internal Development Use Only.
1387 --
1388 -- {End Of Comments}
1389 --
1390 procedure chk_action_status
1391 (p_payroll_action_id in number
1392 ,p_old_action_status in varchar2
1393 ,p_new_action_status in varchar2
1394 ) is
1395 --
1396 v_proc varchar2(72) := g_package||'chk_action_status';
1397 --
1398 begin
1399 hr_utility.set_location('Entering:'||v_proc, 5);
1400 --
1401 -- Check the updated status is from 'C'omplete to 'M'ark for Retry
1402 --
1403 if (p_old_action_status not in ('C', 'S')) and (p_new_action_status <> 'M') then
1404 -- Error: You have tried to enter an invalid status for a completed
1405 -- QuickPay run. You can only update a completed assignment process
1406 -- status to Marked for Retry.
1407 hr_utility.set_message(801, 'HR_7252_QPAY_ONLY_MARK_RETRY');
1408 hr_utility.raise_error;
1409 end if;
1410 hr_utility.set_location(v_proc, 6);
1411 --
1412 -- Check that this QuickPay Run can have
1413 -- a status of Mark for Retry
1414 --
1415 py_rollback_pkg.rollback_payroll_action(
1416 p_payroll_action_id => p_payroll_action_id,
1417 p_rollback_mode => 'RETRY',
1418 p_leave_base_table_row => TRUE);
1419 --
1420 hr_utility.set_location(' Leaving:'||v_proc, 10);
1421 end chk_action_status;
1422 --
1423 -- KKAWOl : Ok, commenting chk_legislative_parameters out as this is no
1424 -- longer required. We pass run type id to the run instead.
1425 -- Will need a chk_run_type procedure.
1426 /*
1427 -- ----------------------------------------------------------------------------
1428 -- |----------------------< chk_legislative_parameters >----------------------|
1429 -- ----------------------------------------------------------------------------
1430 -- {Start Of Comments}
1431 --
1432 -- Description:
1433 -- This procedure validates the legislative_parameters attribute has been
1434 -- set correctly.
1435 --
1436 -- Pre Conditions:
1437 -- The p_business_group_id has already been validated as a business group
1438 -- which exists in the database.
1439 --
1440 -- In Arguments:
1441 -- p_legislation_code the current business group's legislation.
1442 -- p_action_status is set to the current Assignment Process action status.
1443 -- During insert p_action_status should be set to 'U', the code for
1444 -- Unprocessed.
1445 -- p_legislative_parameters is set to the legislative_parameters for the
1446 -- QuickPay Run.
1447 --
1448 -- Post Success:
1449 -- Processing will continue if the business group is for a US legislation
1450 -- and p_legislative_parameters has been set to 'R' (for Regular) or 'S'
1451 -- (for Supplemental) and p_action_status is not 'C' for Complete. Or the
1452 -- business group is for a non-US legislation and legislation_parameters
1453 -- is null.
1454 --
1455 -- Post Failure:
1456 -- An error will be raised if any of the following conditions are found:
1457 -- 1) The business group is for a US legislation and
1458 -- p_legislative_parameters is not set to 'R' or 'S'.
1459 -- 2) The business group is for a US legislation and p_action_status
1460 -- is set to 'C'. (This is because legislation_parameters cannot be
1461 -- updated if the status is Complete.)
1462 -- 3) The business group is for any non-US legislation and
1463 -- p_legislative_parameters is not null.
1464 --
1465 -- Access Status:
1466 -- Internal Development Use Only.
1467 --
1468 -- {End Of Comments}
1469 --
1470 Procedure chk_legislative_parameters
1471 (p_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE
1472 ,p_action_status varchar2
1473 ,p_legislation_code per_business_groups.legislation_code%TYPE
1474 ) Is
1475 --
1476 v_proc varchar2(72) := g_package||'chk_legislative_parameters';
1477 --
1478 begin
1479 hr_utility.set_location('Entering:'|| v_proc, 5);
1480 --
1481 if p_legislation_code = 'US' or
1482 p_legislation_code = 'CA' then
1483 --
1484 -- For a US legislation legislative_parameters cannot be updated
1485 -- when the action_status is Complete.
1486 --
1487 if p_action_status = 'C' then
1488 -- Error: You have tried to alter the Regular/Supplemental flag for a
1489 -- completed QuickPay run. You can only change this flag before a run
1490 -- commences.
1491 hr_utility.set_message(801, 'HR_7253_QPAY_REG_SUPP_NO_UPD');
1492 hr_utility.raise_error;
1493 end if;
1494 --
1495 -- When the legislation is US then legislative_parameters
1496 -- must be set to 'R' or 'S'.
1497 --
1498 if p_legislation_code = 'US' then
1499 if ((p_legislative_parameters is null) or
1500 (p_legislative_parameters <> 'R') and
1501 (p_legislative_parameters <> 'S')) then
1502 -- Error: For US Business Groups the payroll process
1503 -- legislative_parameters must be set to 'R' (for Regular) or 'S'
1504 -- (for Supplemental).
1505 hr_utility.set_message(801, 'HR_7254_QPAY_LEG_PAR_R_OR_S');
1506 hr_utility.raise_error;
1507 end if;
1508
1509 elsif p_legislation_code = 'CA' then
1510
1511 if ((p_legislative_parameters is null) or
1512 (p_legislative_parameters <> 'R') and
1513 (p_legislative_parameters <> 'N') and
1514 (p_legislative_parameters <> 'L')) then
1515
1516 -- Error: For Canadian Business Groups the payroll process
1517 -- legislative_parameters must be set to 'R' (for Regular) or
1518 -- 'N' (for Non Periodic) or 'L' (for Lump Sum).
1519
1520 hr_utility.set_message(801,'HR_7254_QPAY_LEG_PAR_R_OR_S');
1521 hr_utility.raise_error;
1522 end if;
1523
1524 end if;
1525 else
1526 --
1527 -- When the legislation is not US,CA legislative_parameters
1528 -- must be null.
1529 --
1530 if p_legislative_parameters is not null then
1531 -- Error: For this legislation, you must set the payroll process
1532 -- legislative_parameters attribute to null.
1533 hr_utility.set_message(801, 'HR_7255_QPAY_LEG_PAR_NULL');
1534 hr_utility.raise_error;
1535 end if;
1536 end if;
1537 --
1538 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1539 End chk_legislative_parameters;
1540 --
1541 */
1542 -- ----------------------------------------------------------------------------
1543 -- |-----------------------------< chk_run_type >-----------------------------|
1544 -- ----------------------------------------------------------------------------
1545 -- {Start Of Comments}
1546 --
1547 -- Description:
1548 -- This procedure is used in update and delete validation to check the
1549 -- run type value.
1550 -- a) If row exists on pay_legislative_field_info with
1551 -- field_name = 'TAX_PROCESSING_TYPE'
1552 -- target_location = 'PAYWSRQP'
1553 -- rule_type = 'RUN_TYPE_FLAG'
1554 -- rule_mode = 'Y'
1555 -- validation_name = 'DISPLAY'
1556 -- validation_type = 'ITEM_PROPERTY'
1557 -- then tax_processing_type can be not null.
1558 -- b) Foreign key to pay_run_types_f.
1559 --
1560 Procedure chk_run_type
1561 ( p_run_type_id in pay_run_types_f.run_type_id%type
1562 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
1563 ,p_business_group_id in pay_payroll_actions.business_group_id%TYPE
1564 ,p_legislation_code in per_business_groups.legislation_code%TYPE
1565 )
1566 --
1567 IS
1568 v_proc varchar2(72) := g_package||'chk_run_type';
1569 l_rule_mode pay_legislative_field_info.rule_mode%type;
1570 l_rt_exists pay_run_types_f.run_type_id%type;
1571 --
1572 cursor get_leg_rule is
1573 select lfi.rule_mode
1574 from pay_legislative_field_info lfi
1575 where lfi.field_name = 'TAX_PROCESSING_TYPE'
1576 and lfi.target_location = 'PAYWSRQP'
1577 and lfi.rule_type = 'RUN_TYPE_FLAG'
1578 and lfi.validation_type = 'ITEM_PROPERTY'
1579 and lfi.validation_name = 'DISPLAY' ;
1580 --
1581 cursor get_rt_exists is
1582 select rt.run_type_id
1583 from pay_run_types_f rt
1584 where rt.run_type_id = p_run_type_id
1585 and (rt.legislation_code = p_legislation_code
1586 or (rt.legislation_code is null
1587 and rt.business_group_id = p_business_group_id)
1588 or (rt.legislation_code is null and rt.business_group_id is null))
1589 and not exists
1590 ( select null
1591 from pay_legislative_field_info lfi
1592 where lfi.validation_type = 'EXCLUDE'
1593 and lfi.rule_type = 'DATA_VALIDATION'
1594 and lfi.field_name = 'TAX_PROCESSING_TYPE'
1595 and lfi.target_location = 'PAYWSRQP'
1596 and lfi.legislation_code = p_legislation_code
1597 and upper(lfi.validation_name) = upper(rt.run_type_name))
1598 and p_effective_date between rt.effective_start_date
1599 and rt.effective_end_date;
1600 --
1601 begin
1602 hr_utility.set_location('Entering:'|| v_proc, 5);
1603 --
1604 -- Cannot have a run type id as not null if the row on
1605 -- pay_legislative_field_info does not exist.
1606 --
1607 if p_run_type_id is not null then
1608 open get_leg_rule;
1609 fetch get_leg_rule into l_rule_mode;
1610 if ((get_leg_rule%notfound) OR
1611 (get_leg_rule%found and l_rule_mode <> 'Y')) then
1612 close get_leg_rule;
1613 hr_utility.set_message(801, 'PAY_52380_INVALID_RUN_TYPE');
1614 hr_utility.raise_error;
1615 end if;
1616 --
1617 close get_leg_rule;
1618 --
1619 -- Now check the run type is valid, i.e. exists on pay_run_types_f
1620 -- and is either global or belongs to bix grp or leg code.
1621 --
1622 open get_rt_exists;
1623 fetch get_rt_exists into l_rt_exists;
1624 if (get_rt_exists%notfound) then
1625 close get_rt_exists;
1626 hr_utility.set_message(801, 'PAY_52380_INVALID_RUN_TYPE');
1627 hr_utility.raise_error;
1628 end if;
1629 --
1630 close get_rt_exists;
1631 --
1632 end if;
1633 --
1634 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1635 End chk_run_type;
1636 --
1637 -- ----------------------------------------------------------------------------
1638 -- |-----------------------------< chk_cur_task >-----------------------------|
1639 -- ----------------------------------------------------------------------------
1640 -- {Start Of Comments}
1641 --
1642 -- Description:
1643 -- This procedure is used in update and delete validation to check the
1644 -- payroll_action current_task value. The user should not be allowed to
1645 -- update any QuickPay Run attribute or delete a QuickPay Run when the
1646 -- current_task is not null. (A not null value means a C process is
1647 -- still processing the action.)
1648 --
1649 -- Pre Conditions:
1650 -- None.
1651 --
1652 -- In Arguments:
1653 -- p_current_task set to the pay_payroll_actions.current_task of the
1654 -- current payroll_action.
1655 --
1656 -- Post Success:
1657 -- The current_task for this QuickPay Run is null. (Update or delete can
1658 -- be allowed to continue, subject to other validation.)
1659 --
1660 -- Post Failure:
1661 -- An application error is raised if the current_task value is not null.
1662 --
1663 -- {End Of Comments}
1664 --
1665 procedure chk_cur_task
1666 (p_current_task in pay_payroll_actions.current_task%TYPE
1667 ) is
1668 --
1669 v_proc varchar2(72) := g_package||'chk_cur_task';
1670 --
1671 begin
1672 hr_utility.set_location('Entering:'|| v_proc, 5);
1673 --
1674 if p_current_task is not null then
1675 -- Error: You have tried to update a field or to delete the QuickPay run
1676 -- while the run is processing.
1677 hr_utility.set_message(801, 'HR_7241_QPAY_Q_PROCESSING');
1678 hr_utility.raise_error;
1679 end if;
1680 --
1681 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1682 end chk_cur_task;
1683 --
1684 -- ----------------------------------------------------------------------------
1685 -- |-------------------------< chk_for_con_request >--------------------------|
1686 -- ----------------------------------------------------------------------------
1687 --
1688 procedure chk_for_con_request
1689 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
1690 ) is
1691 --
1692 v_request_id pay_payroll_actions.request_id%TYPE;
1693 v_bl boolean;
1694 v_phase varchar2(255);
1695 v_status varchar2(255);
1696 v_dev_phase varchar2(255);
1697 v_dev_status varchar2(255);
1698 v_message varchar2(255);
1699 v_proc varchar2(72) := g_package||'chk_for_con_request';
1700 --
1701 cursor sel_reqt is
1702 select request_id
1703 from pay_payroll_actions
1704 where payroll_action_id = p_payroll_action_id;
1705 begin
1706 hr_utility.set_location('Entering:'|| v_proc, 5);
1707 --
1708 -- Try to obtain the AOL request_id for this payroll process.
1709 --
1710 open sel_reqt;
1711 fetch sel_reqt into v_request_id;
1712 if sel_reqt%notfound then
1713 close sel_reqt;
1714 -- A row could not be found in pay_payroll_actions
1715 -- with an id of p_payroll_action_id.
1716 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1717 hr_utility.raise_error;
1718 end if;
1719 close sel_reqt;
1720 hr_utility.set_location(v_proc, 7);
1721 --
1722 -- Only need to find out the concurrent request status
1723 -- if the request_id is set and is non-zero.
1724 --
1725 if (v_request_id is not null) and (v_request_id <> 0) then
1726 hr_utility.set_location(v_proc, 8);
1727 --
1728 v_bl := fnd_concurrent.get_request_status
1729 (request_id => v_request_id
1730 ,phase => v_phase
1731 ,status => v_status
1732 ,dev_phase => v_dev_phase
1733 ,dev_status => v_dev_status
1734 ,message => v_message
1735 );
1736 hr_utility.set_location(v_proc, 9);
1737 --
1738 -- The process is still waiting or running if the
1739 -- concurrent phase value is not Completed.
1740 --
1741 if v_dev_phase <> 'COMPLETE' then
1742 -- Error: You cannot update or delete a QuickPay definition when a
1743 -- request is still running or waiting to run on the AOL concurrent
1744 -- manager. You may need to contact your system administrator to resolve
1745 -- this problem. Please quote AOL concurrent request_id *REQUEST_ID.
1746 hr_utility.set_message(801, 'HR_7264_QPAY_CON_REQ_STILL_RUN');
1747 hr_utility.set_message_token('REQUEST_ID', to_char(v_request_id));
1748 hr_utility.raise_error;
1749 end if;
1750 end if;
1751 --
1752 hr_utility.set_location(' Leaving:'|| v_proc, 15);
1753 end chk_for_con_request;
1754 --
1755 -- ----------------------------------------------------------------------------
1756 -- |------------------------< get_legislation_code >--------------------------|
1757 -- ----------------------------------------------------------------------------
1758 -- {Start Of Comments}
1759 --
1760 -- Description:
1761 -- Returns the legislation_code for a specified business group.
1762 --
1763 -- Pre Conditions:
1764 -- p_business_group_id is known to be a business group in the HR schema.
1765 --
1766 -- In Arguments:
1767 -- p_business_group_id is mandatory.
1768 --
1769 -- Post Success:
1770 -- Returns the legislation code for p_business_group_id.
1771 --
1772 -- Post Failure:
1773 -- Raises an error if the legislation code for p_business_group_id cannot
1774 -- be found.
1775 --
1776 -- Access Status:
1777 -- Internal Development Use Only.
1778 --
1779 -- {End Of Comments}
1780 --
1781 function get_legislation_code
1782 (p_business_group_id in pay_payroll_actions.business_group_id%TYPE
1783 ) return varchar2 is
1784 --
1785 cursor cur_leg is
1786 select legislation_code
1787 from per_business_groups_perf
1788 where business_group_id = p_business_group_id;
1789 --
1790 v_legislation_code per_business_groups.legislation_code%TYPE;
1791 v_proc varchar2(72) := g_package||'get_legislation_code';
1792 --
1793 Begin
1794 hr_utility.set_location('Entering:'|| v_proc, 5);
1795 --
1796 -- Find out the legislation code for the current business group
1797 --
1798 open cur_leg;
1799 fetch cur_leg into v_legislation_code;
1800 if cur_leg%notfound then
1801 close cur_leg;
1802 -- The legislation code could not be found for current business group.
1803 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1804 hr_utility.set_message_token('PROCEDURE', v_proc);
1805 hr_utility.set_message_token('STEP', '6');
1806 hr_utility.raise_error;
1807 end if;
1808 close cur_leg;
1809 --
1810 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1811 --
1812 return v_legislation_code;
1813 end get_legislation_code;
1814 --
1815 -- ----------------------------------------------------------------------------
1816 -- |------------------------< return_api_dml_status >-------------------------|
1817 -- ----------------------------------------------------------------------------
1818 --
1819 function return_api_dml_status Return Boolean Is
1820 --
1821 v_proc varchar2(72) := g_package||'return_api_dml_status';
1822 --
1823 begin
1824 hr_utility.set_location('Entering:'||v_proc, 5);
1825 --
1826 Return (nvl(g_api_dml, false));
1827 --
1828 hr_utility.set_location(' Leaving:'||v_proc, 10);
1829 end return_api_dml_status;
1830 --
1831 -- ----------------------------------------------------------------------------
1832 -- |---------------------------< constraint_error >---------------------------|
1833 -- ----------------------------------------------------------------------------
1834 -- {Start Of Comments}
1835 --
1836 -- Description:
1837 -- This procedure is called when a constraint has been violated (i.e.
1838 -- The exception hr_api.check_integrity_violated,
1839 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
1840 -- been raised).
1841 -- The exceptions can only be raised as follows:
1842 -- 1) A check constraint can only be violated during an INSERT or UPDATE
1843 -- dml operation.
1844 -- 2) A parent integrity constraint can only be violated during an
1845 -- INSERT or UPDATE dml operation.
1846 -- 3) A child integrity constraint can only be violated during an
1847 -- DELETE dml operation.
1848 --
1849 -- Pre Conditions:
1850 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
1851 -- or hr_api.child_integrity_violated has been raised with the subsequent
1852 -- stripping of the constraint name from the generated error message text.
1853 --
1854 -- In Arguments:
1855 -- p_constraint_name is in upper format and is just the constraint name
1856 -- (e.g. not prefixed by brackets, schema owner etc).
1857 --
1858 -- Post Success:
1859 -- Development dependant.
1860 --
1861 -- Post Failure:
1862 -- Development dependant.
1863 --
1864 -- Developer Implementation Notes:
1865 -- For each constraint being checked the hr system package failure message
1866 -- has been generated as a template only. These system error messages should
1867 -- be modified as required (i.e. change the system failure message to a user
1868 -- friendly defined error message).
1869 --
1870 -- Access Status:
1871 -- Public.
1872 --
1873 -- {End Of Comments}
1874 Procedure constraint_error
1875 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
1876 --
1877 v_proc varchar2(72) := g_package||'constraint_error';
1878 --
1879 Begin
1880 hr_utility.set_location('Entering:'||v_proc, 5);
1881 --
1882 If (p_constraint_name = 'PAY_PAYRACT_ACTION_POPULAT_CHK') Then
1883 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1884 hr_utility.set_message_token('PROCEDURE', v_proc);
1885 hr_utility.set_message_token('STEP','5');
1886 hr_utility.raise_error;
1887 elsif (p_constraint_name = 'PAY_PAYRACT_ACTION_STATUS_CHK') Then
1888 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1889 hr_utility.set_message_token('PROCEDURE', v_proc);
1890 hr_utility.set_message_token('STEP','10');
1891 hr_utility.raise_error;
1892 elsif (p_constraint_name = 'PAY_PAYRACT_ACTION_TYPE_CHK') Then
1893 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1894 hr_utility.set_message_token('PROCEDURE', v_proc);
1895 hr_utility.set_message_token('STEP','15');
1896 elsif (p_constraint_name = 'PAY_PAYRACT_CURRENT_TASK_CHK') Then
1897 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1898 hr_utility.set_message_token('PROCEDURE', v_proc);
1899 hr_utility.set_message_token('STEP','20');
1900 hr_utility.raise_error;
1901 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK1') Then
1902 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1903 hr_utility.set_message_token('PROCEDURE', v_proc);
1904 hr_utility.set_message_token('STEP','25');
1905 hr_utility.raise_error;
1906 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK2') Then
1907 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1908 hr_utility.set_message_token('PROCEDURE', v_proc);
1909 hr_utility.set_message_token('STEP','30');
1910 hr_utility.raise_error;
1911 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK5') Then
1912 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1913 hr_utility.set_message_token('PROCEDURE', v_proc);
1914 hr_utility.set_message_token('STEP','35');
1915 hr_utility.raise_error;
1916 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK6') Then
1917 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1918 hr_utility.set_message_token('PROCEDURE', v_proc);
1919 hr_utility.set_message_token('STEP','40');
1920 hr_utility.raise_error;
1921 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK7') Then
1922 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1923 hr_utility.set_message_token('PROCEDURE', v_proc);
1924 hr_utility.set_message_token('STEP','45');
1925 hr_utility.raise_error;
1926 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK8') Then
1927 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1928 hr_utility.set_message_token('PROCEDURE', v_proc);
1929 hr_utility.set_message_token('STEP','50');
1930 hr_utility.raise_error;
1931 else
1932 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1933 hr_utility.set_message_token('PROCEDURE', v_proc);
1934 hr_utility.set_message_token('STEP','55');
1935 hr_utility.raise_error;
1936 end if;
1937 --
1938 hr_utility.set_location(' Leaving:'||v_proc, 10);
1939 End constraint_error;
1940 --
1941 -- ----------------------------------------------------------------------------
1942 -- |-------------------------< check_mandatory_args >-------------------------|
1943 -- ----------------------------------------------------------------------------
1944 -- {Start Of Comments}
1945 --
1946 -- Description:
1947 -- This procedure checks all known mandatory arguments to ensure that they
1948 -- are not null. This check does not include system generated attributes
1949 -- such as primary keys or object version number because usually, these
1950 -- arguments are system maintained.
1951 --
1952 -- Pre Conditions:
1953 -- None.
1954 --
1955 -- In Arguments:
1956 --
1957 -- Post Success:
1958 -- Processing continues if p_rec.business_group_id and p_rec.effective_date
1959 -- are not null.
1960 --
1961 -- Post Failure:
1962 -- If p_rec.business_group_id or p_rec.effective_date are null then an
1963 -- application error will be raised and processing is terminated.
1964 --
1965 -- Access Status:
1966 -- Internal Development Use Only.
1967 --
1968 -- {End Of Comments}
1969 --
1970 Procedure check_mandatory_args(p_rec in g_rec_type) Is
1971 --
1972 v_proc varchar2(72) := g_package||'check_mandatory_args';
1973 v_argument varchar2(30);
1974 --
1975 Begin
1976 hr_utility.set_location('Entering:'||v_proc, 5);
1977 --
1978 -- This procedure does not check action_type, action_population_status,
1979 -- action_status because they are all set at by the pre-insert procedure.
1980 -- Consolidation_set_id is has been added to this procedure because it is
1981 -- mandatory for QuickPay Runs.
1982 --
1983 hr_api.mandatory_arg_error
1984 (p_api_name => v_proc
1985 ,p_argument => 'business_group_id'
1986 ,p_argument_value => p_rec.business_group_id
1987 );
1988 hr_api.mandatory_arg_error
1989 (p_api_name => v_proc
1990 ,p_argument => 'effective_date'
1991 ,p_argument_value => p_rec.effective_date
1992 );
1993 hr_api.mandatory_arg_error
1994 (p_api_name => v_proc
1995 ,p_argument => 'consolidation_set_id'
1996 ,p_argument_value => p_rec.consolidation_set_id
1997 );
1998 --
1999 hr_utility.set_location(' Leaving:'||v_proc, 10);
2000 End check_mandatory_args;
2001 --
2002 -- ----------------------------------------------------------------------------
2003 -- |----------------------< check_non_updateable_args >-----------------------|
2004 -- ----------------------------------------------------------------------------
2005 --
2006 -- {Start Of Comments}
2007 --
2008 -- Description:
2009 -- This procedure is used to ensure that non updateable attributes have
2010 -- not been updated. If an attribute has been updated an error is generated.
2011 --
2012 -- Pre Conditions:
2013 -- g_old_rec has been populated with details of the values currently in
2014 -- the database.
2015 --
2016 -- In Arguments:
2017 -- p_rec has been populated with the updated values the user would like the
2018 -- record set to.
2019 --
2020 -- Post Success:
2021 -- Processing continues if all the non updateable attributes have not
2022 -- changed.
2023 --
2024 -- Post Failure:
2025 -- An application error is raised if any of the non updatable attributes
2026 -- have been altered.
2027 --
2028 -- {End Of Comments}
2029 --
2030 Procedure check_non_updateable_args(p_rec in g_rec_type) is
2031 --
2032 v_proc varchar2(72) := g_package||'check_non_updateable_args';
2033 v_argument varchar2(30);
2034 --
2035 Begin
2036 hr_utility.set_location('Entering:'||v_proc, 5);
2037 if nvl(p_rec.business_group_id, hr_api.g_number) <>
2038 nvl(g_old_rec.business_group_id, hr_api.g_number) then
2039 v_argument := 'business_group_id';
2040 raise hr_api.argument_changed;
2041 end if;
2042 hr_utility.set_location(v_proc, 6);
2043 --
2044 if nvl(p_rec.effective_date, hr_api.g_date) <>
2045 nvl(g_old_rec.effective_date, hr_api.g_date) then
2046 v_argument := 'effective_date';
2047 raise hr_api.argument_changed;
2048 end if;
2049 hr_utility.set_location(v_proc, 7);
2050 --
2051 if nvl(p_rec.current_task, hr_api.g_varchar2) <>
2052 nvl(g_old_rec.current_task, hr_api.g_varchar2) then
2053 v_argument := 'current_task';
2054 raise hr_api.argument_changed;
2055 end if;
2056 hr_utility.set_location(v_proc, 8);
2057 --
2058 if nvl(p_rec.date_earned, hr_api.g_date) <>
2059 nvl(g_old_rec.date_earned, hr_api.g_date) then
2060 v_argument := 'date_earned';
2061 raise hr_api.argument_changed;
2062 end if;
2063 --
2064 hr_utility.set_location(' Leaving:'||v_proc, 10);
2065 Exception
2066 When hr_api.argument_changed Then
2067 --
2068 -- A non updatetable attribute has been changed therefore we
2069 -- must report this error
2070 --
2071 hr_api.argument_changed_error
2072 (p_api_name => v_proc
2073 ,p_argument => v_argument
2074 );
2075 --
2076 End check_non_updateable_args;
2077 --
2078 -- ----------------------------------------------------------------------------
2079 -- |------------------------------< pre_insert >------------------------------|
2080 -- ----------------------------------------------------------------------------
2081 -- {Start Of Comments}
2082 --
2083 -- Description:
2084 -- This private procedure contains any processing which is required before
2085 -- the insert dml. Presently, if the entity has a corresponding primary
2086 -- key which is maintained by an associating sequence, the primary key for
2087 -- the entity will be populated with the next sequence value in
2088 -- preparation for the insert dml.
2089 --
2090 -- Pre Conditions:
2091 -- This is an internal procedure which is called from the ins procedure.
2092 --
2093 -- In Arguments:
2094 -- A Pl/Sql record structure.
2095 --
2096 -- Post Success:
2097 -- p_rec.payroll_action_id is set with the primary key value.
2098 -- p_rec.action_status is set to 'U', the code for Unprocessed.
2099 -- p_action_type is set to 'Q', the code for QuickPay Run.
2100 -- p_action_population_status is to 'U', the code for unpopulated.
2101 --
2102 -- Post Failure:
2103 -- If an error has occurred, an error message and exception will be raised
2104 -- but not handled.
2105 --
2106 -- Access Status:
2107 -- Internal Development Use Only.
2108 --
2109 -- {End Of Comments}
2110 --
2111 Procedure pre_insert
2112 (p_rec in out nocopy g_rec_type
2113 ,p_action_type out nocopy pay_payroll_actions.action_type%TYPE
2114 ,p_action_population_status out nocopy
2115 pay_payroll_actions.action_population_status%TYPE
2116 ) is
2117 --
2118 v_proc varchar2(72) := g_package||'pre_insert';
2119 --
2120 Cursor C_Sel1 is select pay_payroll_actions_s.nextval from sys.dual;
2121 --
2122 Begin
2123 hr_utility.set_location('Entering:'|| v_proc, 5);
2124 --
2125 -- Set the following attributes to there insert values.
2126 -- (payroll_id and time_period_id are during the insert_validate logic.)
2127 --
2128 --
2129 -- Set the initial action_status to unprocessed
2130 --
2131 p_rec.action_status := 'U';
2132 --
2133 -- Set pay_payroll_action columns which are only
2134 -- set by non-process code at insert time.
2135 --
2136 -- Set action_type to QuickPay Run
2137 --
2138 p_action_type := 'Q';
2139 --
2140 -- Set action_population_status to unpopulated
2141 --
2142 p_action_population_status := 'U';
2143 --
2144 -- Select the next sequence number
2145 --
2146 open C_Sel1;
2147 fetch C_Sel1 into p_rec.payroll_action_id;
2148 close C_Sel1;
2149 --
2150 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2151 End pre_insert;
2152 --
2153 -- ----------------------------------------------------------------------------
2154 -- |------------------------------< pre_update >------------------------------|
2155 -- ----------------------------------------------------------------------------
2156 -- {Start Of Comments}
2157 --
2158 -- Description:
2159 -- This private procedure contains any processing which is required before
2160 -- the update dml.
2161 --
2162 -- Pre Conditions:
2163 -- This is an internal procedure which is called from the upd procedure.
2164 --
2165 -- In Arguments:
2166 -- A Pl/Sql record structure.
2167 --
2168 -- Post Success:
2169 -- Processing continues.
2170 --
2171 -- Post Failure:
2172 -- If an error has occurred, an error message and exception will be raised
2173 -- but not handled.
2174 --
2175 -- Access Status:
2176 -- Internal Development Use Only.
2177 --
2178 Procedure pre_update(p_rec in g_rec_type) is
2179 --
2180 v_proc varchar2(72) := g_package||'pre_update';
2181 --
2182 Begin
2183 hr_utility.set_location('Entering:'|| v_proc, 5);
2184 --
2185 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2186 End pre_update;
2187 --
2188 -- ----------------------------------------------------------------------------
2189 -- |------------------------------< pre_delete >------------------------------|
2190 -- ----------------------------------------------------------------------------
2191 -- {Start Of Comments}
2192 --
2193 -- Description:
2194 -- This private procedure contains any processing which is required before
2195 -- the delete dml.
2196 --
2197 -- Pre Conditions:
2198 -- This is an internal procedure which is called from the del procedure.
2199 --
2200 -- In Arguments:
2201 -- A Pl/Sql record structure.
2202 --
2203 -- Post Success:
2204 -- Processing continues.
2205 --
2206 -- Post Failure:
2207 -- If an error has occurred, an error message and exception will be raised
2208 -- but not handled.
2209 --
2210 -- Access Status:
2211 -- Internal Development Use Only.
2212 --
2213 -- {End Of Comments}
2214 --
2215 Procedure pre_delete(p_rec in g_rec_type) is
2216 --
2217 v_proc varchar2(72) := g_package||'pre_delete';
2218 --
2219 Begin
2220 hr_utility.set_location('Entering:'|| v_proc, 5);
2221 --
2222 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2223 End pre_delete;
2224 --
2225 -- ----------------------------------------------------------------------------
2226 -- |-----------------------------< post_insert >------------------------------|
2227 -- ----------------------------------------------------------------------------
2228 -- {Start Of Comments}
2229 --
2230 -- Description:
2231 -- This private procedure contains the processing which is required after the
2232 -- insert dml. It inserts an Assignment Process row for a QuickPay Payroll
2233 -- Process and all the default QuickPay inclusions.
2234 --
2235 -- Pre Conditions:
2236 -- This is an internal procedure which is called from the ins procedure.
2237 -- All business rule validation has been done of p_assignment_id.
2238 --
2239 -- In Arguments:
2240 -- p_rec contains the details of the insert QuickPay Payroll Process.
2241 -- p_assignment_id is the assignment the Assignment Process is to be created
2242 -- for.
2243 -- p_validate should be set to the same value as the ins procedure for
2244 -- the QuickPay Run ins procedure.
2245 --
2246 -- Post Success:
2247 -- p_assignment_action_id is set to the primary key id of the created
2248 -- Assignment Process.
2249 -- p_a_object_version_number is set to the object version number for the
2250 -- Assignment Process.
2251 -- The default QuickPay inclusions have been created.
2252 --
2253 -- Post Failure:
2254 -- If an error has occurred, an error message and exception will be raised
2255 -- but not handled.
2256 --
2257 -- Access Status:
2258 -- Internal Development Use Only.
2259 --
2260 -- {End Of Comments}
2261 --
2262 procedure post_insert
2263 (p_rec in g_rec_type
2264 ,p_assignment_id in number default null
2265 ,p_validate in boolean default false
2266 ,p_assignment_action_id out nocopy number
2267 ,p_a_object_version_number out nocopy number
2268 ) is
2269 --
2270 v_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
2271 v_object_version_number pay_assignment_actions.object_version_number%TYPE;
2272 v_proc varchar2(72) := g_package||'post_insert';
2273 --
2274 begin
2275 hr_utility.set_location('Entering:'|| v_proc, 5);
2276 --
2277 -- For a QuickPay insert the assignment action
2278 -- and quickpay inclusion rows. (The payroll action and
2279 -- assignment action must be inserted in the same commit unit.
2280 -- Either would be invalid without the other.)
2281 --
2282 -- Insert assignment action
2283 --
2284 hrassact.qpassact
2285 (p_payroll_action_id => p_rec.payroll_action_id
2286 ,p_assignment_id => p_assignment_id
2287 ,p_assignment_action_id => v_assignment_action_id
2288 ,p_object_version_number => v_object_version_number
2289 );
2290 hr_utility.set_location(v_proc, 6);
2291 --
2292 -- Insert default quickpay inclusions
2293 --
2294 -- Enhancement 3368211
2295 -- Check that we are not using the new QuickPay Exclusions model before
2296 -- doing the bulk insert of QuickPay Inclusions
2297 --
2298 if use_qpay_excl_model = 'N' then
2299 pay_qpi_api.bulk_default_ins
2300 (p_assignment_action_id => v_assignment_action_id
2301 ,p_validate => p_validate
2302 );
2303 end if;
2304 --
2305 hr_utility.set_location(v_proc, 7);
2306 --
2307 p_assignment_action_id := v_assignment_action_id;
2308 p_a_object_version_number := v_object_version_number;
2309 --
2310 hr_utility.set_location('Leaving:'|| v_proc, 10);
2311 end post_insert;
2312 --
2313 -- ----------------------------------------------------------------------------
2314 -- |-----------------------------< post_update >------------------------------|
2315 -- ----------------------------------------------------------------------------
2316 -- {Start Of Comments}
2317 --
2318 -- Description:
2319 -- This private procedure contains any processing which is required after the
2320 -- update dml.
2321 --
2322 -- Pre Conditions:
2323 -- This is an internal procedure which is called from the upd procedure.
2324 --
2325 -- In Arguments:
2326 -- A Pl/Sql record structure.
2327 --
2328 -- Post Success:
2329 -- Processing continues.
2330 --
2331 -- Post Failure:
2332 -- If an error has occurred, an error message and exception will be raised
2333 -- but not handled.
2334 --
2335 -- Access Status:
2336 -- Internal Development Use Only.
2337 --
2338 -- {End Of Comments}
2339 --
2340 Procedure post_update(p_rec in g_rec_type) is
2341 --
2342 v_proc varchar2(72) := g_package||'post_update';
2343 --
2344 Begin
2345 hr_utility.set_location('Entering:'|| v_proc, 5);
2346 --
2347 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2348 End post_update;
2349 --
2350 -- ----------------------------------------------------------------------------
2351 -- |-----------------------------< post_delete >------------------------------|
2352 -- ----------------------------------------------------------------------------
2353 -- {Start Of Comments}
2354 --
2355 -- Description:
2356 -- This private procedure contains any processing which is required after the
2357 -- delete dml.
2358 --
2359 -- Pre Conditions:
2360 -- This is an internal procedure which is called from the del procedure.
2361 --
2362 -- In Arguments:
2363 -- A Pl/Sql record structure.
2364 --
2365 -- Post Success:
2366 -- Processing continues.
2367 --
2368 -- Post Failure:
2369 -- If an error has occurred, an error message and exception will be raised
2370 -- but not handled.
2371 --
2372 -- Access Status:
2373 -- Internal Development Use Only.
2374 --
2375 -- {End Of Comments}
2376 --
2377 Procedure post_delete(p_rec in g_rec_type) is
2378 --
2379 v_proc varchar2(72) := g_package||'post_delete';
2380 --
2381 Begin
2382 hr_utility.set_location('Entering:'|| v_proc, 5);
2383 --
2384 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2385 End post_delete;
2386 --
2387 -- ----------------------------------------------------------------------------
2388 -- |------------------------------< insert_dml >------------------------------|
2389 -- ----------------------------------------------------------------------------
2390 -- {Start Of Comments}
2391 --
2392 -- Description:
2393 -- This procedure controls the actual dml insert logic. The functions of this
2394 -- procedure are as follows:
2395 -- 1. Initialise the object_version_number to 1.
2396 -- 2. To set and unset the g_api_dml status as required (as we are about to
2397 -- perform dml).
2398 -- 3. To insert the row into the schema.
2399 -- 4. To trap any constraint violations that may have occurred.
2400 -- 5. To raise any other errors.
2401 --
2402 -- Pre Conditions:
2403 -- This is an internal private procedure which must be called from the ins
2404 -- procedure and must have all mandatory arguments set (except the
2405 -- object_version_number which is initialised within this procedure).
2406 --
2407 -- In Arguments:
2408 -- p_rec contains all the details of the QuickPay Run, Payroll Process.
2409 --
2410 -- Post Success:
2411 -- The QuickPay Run, Payroll Process row will be inserted into the schema.
2412 --
2413 -- Post Failure:
2414 -- On the insert dml failure it is important to note that we always reset the
2415 -- g_api_dml status to false.
2416 -- If a check or parent integrity constraint violation is raised the
2417 -- constraint_error procedure will be called.
2418 -- If any other error is reported, the error will be raised after the
2419 -- g_api_dml status is reset.
2420 --
2421 -- Access Status:
2422 -- Internal Development Use Only.
2423 --
2424 -- {End Of Comments}
2425 Procedure insert_dml
2426 (p_rec in out nocopy g_rec_type
2427 ,p_action_type in pay_payroll_actions.action_type%TYPE
2428 ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
2429 ,p_time_period_id in pay_payroll_actions.time_period_id%TYPE
2430 ,p_action_population_status in
2431 pay_payroll_actions.action_population_status%TYPE
2432 ) is
2433 --
2434 v_proc varchar2(72) := g_package||'insert_dml';
2435 --
2436 Begin
2437 hr_utility.set_location('Entering:'|| v_proc, 5);
2438 p_rec.object_version_number := 1; -- Initialise the object version
2439 --
2440 g_api_dml := true; -- Set the api dml status
2441 --
2442 -- Insert the row into: pay_payroll_actions
2443 --
2444 insert into pay_payroll_actions
2445 (payroll_action_id
2446 ,action_type
2447 ,business_group_id
2448 ,consolidation_set_id
2449 ,payroll_id
2450 ,action_population_status
2451 ,action_status
2452 ,effective_date
2453 ,comments
2454 ,current_task
2455 ,legislative_parameters
2456 ,run_type_id
2457 ,date_earned
2458 ,pay_advice_date
2459 ,pay_advice_message
2460 ,object_version_number
2461 ,time_period_id
2462 )
2463 values
2464 (p_rec.payroll_action_id
2465 ,p_action_type
2466 ,p_rec.business_group_id
2467 ,p_rec.consolidation_set_id
2468 ,p_payroll_id
2469 ,p_action_population_status
2470 ,p_rec.action_status
2471 ,p_rec.effective_date
2472 ,p_rec.comments
2473 ,null
2474 ,p_rec.legislative_parameters
2475 ,p_rec.run_type_id
2476 ,p_rec.date_earned
2477 ,p_rec.pay_advice_date
2478 ,p_rec.pay_advice_message
2479 ,p_rec.object_version_number
2480 ,p_time_period_id
2481 );
2482 --
2483 g_api_dml := false; -- Unset the api dml status
2484 --
2485 hr_utility.set_location(' Leaving:'||v_proc, 10);
2486 Exception
2487 When hr_api.check_integrity_violated Then
2488 --
2489 -- A check constraint has been violated
2490 --
2491 -- Unset the api dml status
2492 g_api_dml := false;
2493 constraint_error
2494 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2495 When hr_api.parent_integrity_violated then
2496 --
2497 -- Parent integrity has been violated
2498 --
2499 -- Unset the api dml status
2500 g_api_dml := false;
2501 constraint_error
2502 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2503 When hr_api.unique_integrity_violated Then
2504 --
2505 -- Unique integrity has been violated
2506 --
2507 g_api_dml := false; -- Unset the api dml status
2508 constraint_error
2509 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2510 When Others Then
2511 --
2512 -- Unset the api dml status
2513 --
2514 g_api_dml := false;
2515 Raise;
2516 End insert_dml;
2517 --
2518 -- ----------------------------------------------------------------------------
2519 -- |------------------------------< update_dml >------------------------------|
2520 -- ----------------------------------------------------------------------------
2521 -- {Start Of Comments}
2522 --
2523 -- Description:
2524 -- This procedure controls the actual dml update logic. The functions of this
2525 -- procedure are as follows:
2526 -- 1. Increment the object_version_number by 1.
2527 -- 2. To set and unset the g_api_dml status as required (as we are about to
2528 -- perform dml).
2529 -- 3. To update the specified row in the schema using the primary key in
2530 -- the predicates.
2531 -- 4. To trap any constraint violations that may have occurred.
2532 -- 5. To raise any other errors.
2533 --
2534 -- Pre Conditions:
2535 -- This is an internal private procedure which must be called from the upd
2536 -- procedure.
2537 --
2538 -- In Arguments:
2539 -- p_rec should contain all the value as they are going to be set on the
2540 -- database. (Expect for object_version_number.)
2541 --
2542 -- Post Success:
2543 -- The QuickPay Run will be updated in the schema.
2544 --
2545 -- Post Failure:
2546 -- On the update dml failure it is important to note that we always reset the
2547 -- g_api_dml status to false.
2548 -- If a check or parent integrity constraint violation is raised the
2549 -- constraint_error procedure will be called.
2550 -- If any other error is reported, the error will be raised after the
2551 -- g_api_dml status is reset.
2552 --
2553 -- Access Status:
2554 -- Internal Development Use Only.
2555 --
2556 -- {End Of Comments}
2557 --
2558 Procedure update_dml(p_rec in out nocopy g_rec_type) is
2559 --
2560 v_proc varchar2(72) := g_package||'update_dml';
2561 --
2562 Begin
2563 hr_utility.set_location('Entering:'|| v_proc, 5);
2564 --
2565 -- Increment the object version
2566 --
2567 p_rec.object_version_number := p_rec.object_version_number + 1;
2568 --
2569 -- Set the api dml status
2570 --
2571 g_api_dml := true;
2572 --
2573 -- Update the pay_payroll_actions Row
2574 --
2575 update pay_payroll_actions set
2576 business_group_id = p_rec.business_group_id
2577 ,consolidation_set_id = p_rec.consolidation_set_id
2578 ,action_status = p_rec.action_status
2579 ,effective_date = p_rec.effective_date
2580 ,comments = p_rec.comments
2581 ,legislative_parameters = p_rec.legislative_parameters
2582 ,run_type_id = p_rec.run_type_id
2583 ,date_earned = p_rec.date_earned
2584 ,pay_advice_date = p_rec.pay_advice_date
2585 ,pay_advice_message = p_rec.pay_advice_message
2586 ,object_version_number = p_rec.object_version_number
2587 where payroll_action_id = p_rec.payroll_action_id;
2588 --
2589 -- Unset the api dml status
2590 --
2591 g_api_dml := false;
2592 --
2593 hr_utility.set_location(' Leaving:'||v_proc, 10);
2594 --
2595 Exception
2596 When hr_api.check_integrity_violated Then
2597 --
2598 -- A check constraint has been violated
2599 --
2600 -- Unset the api dml status
2601 g_api_dml := false;
2602 constraint_error
2603 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2604 When hr_api.parent_integrity_violated then
2605 --
2606 -- Parent integrity has been violated
2607 --
2608 -- Unset the api dml status
2609 g_api_dml := false;
2610 constraint_error
2611 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2612 When hr_api.unique_integrity_violated Then
2613 --
2614 -- Unique integrity has been violated
2615 --
2616 g_api_dml := false; -- Unset the api dml status
2617 constraint_error
2618 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2619 When Others Then
2620 --
2621 -- Unset the api dml status
2622 --
2623 g_api_dml := false;
2624 Raise;
2625 end update_dml;
2626 --
2627 -- ----------------------------------------------------------------------------
2628 -- |------------------------------< delete_dml >------------------------------|
2629 -- ----------------------------------------------------------------------------
2630 -- {Start Of Comments}
2631 --
2632 -- Description:
2633 -- This procedure controls the actual dml delete logic. The functions of this
2634 -- procedure are as follows:
2635 -- 1. To set and unset the g_api_dml status as required (as we are about to
2636 -- perform dml).
2637 -- 2. To delete the specified row from the schema using the primary key in
2638 -- the predicates.
2639 -- 3. To ensure that the row was deleted.
2640 -- 4. To trap any constraint violations that may have occurred.
2641 -- 5. To raise any other errors.
2642 --
2643 -- Pre Conditions:
2644 -- This is an internal private procedure which must be called from the del
2645 -- procedure.
2646 --
2647 -- In Arguments:
2648 -- p_rec has the primary key details set. i.e. p_rec.payroll_action_id is
2649 -- not null.
2650 --
2651 -- Post Success:
2652 -- The QuickPay Run row will be delete from the schema.
2653 --
2654 -- Post Failure:
2655 -- On the delete dml failure it is important to note that we always reset the
2656 -- g_api_dml status to false.
2657 -- If a child integrity constraint violation is raised the
2658 -- constraint_error procedure will be called.
2659 -- If any other error is reported, the error will be raised after the
2660 -- g_api_dml status is reset.
2661 --
2662 -- Access Status:
2663 -- Internal Development Use Only.
2664 --
2665 -- {End Of Comments}
2666 Procedure delete_dml(p_rec in g_rec_type) is
2667 --
2668 v_proc varchar2(72) := g_package||'delete_dml';
2669 --
2670 Begin
2671 hr_utility.set_location('Entering:'|| v_proc, 5);
2672 --
2673 -- Set the api dml status
2674 --
2675 g_api_dml := true;
2676 --
2677 -- Delete the pay_payroll_actions row.
2678 --
2679 delete from pay_payroll_actions
2680 where payroll_action_id = p_rec.payroll_action_id;
2681 --
2682 -- Unset the api dml status
2683 --
2684 g_api_dml := false;
2685 --
2686 hr_utility.set_location(' Leaving:'||v_proc, 10);
2687 --
2688 Exception
2689 When hr_api.child_integrity_violated then
2690 --
2691 -- Child integrity has been violated
2692 --
2693 -- Unset the api dml status
2694 g_api_dml := false;
2695 constraint_error
2696 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2697 When Others Then
2698 --
2699 -- Unset the api dml status
2700 --
2701 g_api_dml := false;
2702 Raise;
2703 End delete_dml;
2704 --
2705 -- ----------------------------------------------------------------------------
2706 -- |------------------------------< lck_general >-----------------------------|
2707 -- ----------------------------------------------------------------------------
2708 -- {Start Of Comments}
2709 --
2710 -- Description:
2711 -- This lock procedure can be called in two ways.
2712 -- 1) To lock a QuickPay Run or QuickPay Pre-payment.
2713 -- 2) To lock a Payroll Process of a specified type.
2714 -- This procedure will attempt to lock the Payroll Process and the associated
2715 -- Assignment Process. The row locking will only be successful if the
2716 -- rows are not currently locked by another user, the specified object
2717 -- version numbers match and there is no AOL request waiting or still
2718 -- running on the concurrent manager for the Payroll Process. If the lock is
2719 -- successfully taken, the Payroll Process row will be selected into the
2720 -- g_old_rec data structure.
2721 --
2722 -- Pre Conditions:
2723 -- None.
2724 --
2725 -- In Arguments:
2726 -- p_payroll_action_id is set to the id of the Payroll Process to be locked.
2727 -- p_p_object_version_number is set object version number of the Payroll
2728 -- Process.
2729 -- p_a_object_version_number is set object version number of the Assignment
2730 -- Process.
2731 -- p_only_action_type if the lck is to be taken out using method 2 then
2732 -- this argument should be set to the action_type of the Payroll Process.
2733 -- Otherwise p_only_action_type should be null.
2734 --
2735 -- Post Success:
2736 -- On successful completion of the Lck process the rows to be updated or
2737 -- deleted will be locked and the g_old_rec data structure will be set
2738 -- with the Payroll Process details. p_a_action_status will be set to the
2739 -- action_status of the Assignment Process.
2740 --
2741 -- Post Failure:
2742 -- The Lck process can fail for six reasons:
2743 -- 1) When attempting to lock the row the row could already be locked by
2744 -- another user. This will raise the HR_7165_OBJECT_LOCKED error.
2745 -- 2) A Payroll Process with id p_payroll_action_id doesn't exist in the HR
2746 -- Schema. This error is trapped and reported using the message name
2747 -- 'HR_7155_OBJECT_INVALID'.
2748 -- 3) The rows although existing in the HR Schema have different object
2749 -- version numbers than the object version numbers specified.
2750 -- This error is trapped and reported using the message name
2751 -- 'HR_7155_OBJECT_INVALID'.
2752 -- 4) An error is raised if an AOL concurrent request is waiting to run or
2753 -- still running on the concurrent manager for the Payroll Process.
2754 -- 5) If p_only_action_type is null the lock will fail if the Payroll
2755 -- Process is not a QuickPay Run or QuickPay Pre-payment.
2756 -- 6) If p_only_action_type has been set the lock will fail if the Payroll
2757 -- Process is not of that type.
2758 --
2759 -- Access Status:
2760 -- Internal Development Use Only.
2761 --
2762 -- {End Of Comments}
2763 --
2764 procedure lck_general
2765 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
2766 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
2767 ,p_a_object_version_number in
2768 pay_assignment_actions.object_version_number%TYPE
2769 ,p_only_action_type in pay_payroll_actions.action_type%TYPE
2770 ,p_actual_action_type out nocopy pay_payroll_actions.action_type%TYPE
2771 ,p_a_action_status out nocopy pay_assignment_actions.
2772 action_status%TYPE
2773 ) is
2774 v_a_object_version_number pay_assignment_actions.object_version_number%TYPE;
2775 --
2776 -- Cursor selects the 'current' row from the HR Schema
2777 -- (Locks pay_payroll_actions first, then pay_assignment_actions.)
2778 --
2779 Cursor C_Sel1 is
2780 select pya.payroll_action_id
2781 , pya.business_group_id
2782 , pya.consolidation_set_id
2783 , pya.action_status
2784 , pya.effective_date
2785 , pya.comments
2786 , pya.current_task
2787 , pya.legislative_parameters
2788 , pya.run_type_id
2789 , pya.date_earned
2790 , pya.pay_advice_date
2791 , pya.pay_advice_message
2792 , pya.object_version_number
2793 , pya.action_type
2794 , aga.action_status
2795 , aga.object_version_number
2796 from pay_payroll_actions pya
2797 , pay_assignment_actions aga
2798 where /* Payroll action lock */
2799 pya.payroll_action_id = p_payroll_action_id
2800 and ( pya.action_type = p_only_action_type
2801 or ( p_only_action_type is null
2802 and pya.action_type in ('Q', 'U','X')--Code added for archive
2803 )
2804 )
2805 /* Assignment action lock */
2806 and aga.payroll_action_id = pya.payroll_action_id
2807 for update nowait;
2808 --
2809 v_proc varchar2(72) := g_package||'lck_general';
2810 --
2811 Begin
2812 hr_utility.set_location('Entering:'|| v_proc, 5);
2813 --
2814 -- Check the mandatory args have been set
2815 --
2816 hr_api.mandatory_arg_error
2817 (p_api_name => v_proc
2818 ,p_argument => 'payroll_action_id'
2819 ,p_argument_value => p_payroll_action_id
2820 );
2821 hr_api.mandatory_arg_error
2822 (p_api_name => v_proc
2823 ,p_argument => 'p_object_version_number'
2824 ,p_argument_value => p_p_object_version_number
2825 );
2826 hr_api.mandatory_arg_error
2827 (p_api_name => v_proc
2828 ,p_argument => 'a_object_version_number'
2829 ,p_argument_value => p_a_object_version_number
2830 );
2831 hr_utility.set_location(v_proc, 6);
2832 --
2833 -- Additional logic specific to this entity:
2834 -- Do not allow the lock to be taken out if there is an AOL concurrent
2835 -- request waiting to run or still running on a concurrent manager.
2836 --
2837 chk_for_con_request
2838 (p_payroll_action_id => p_payroll_action_id);
2839 hr_utility.set_location(v_proc, 7);
2840 --
2841 -- If the primary key exists, we must now attempt to lock the
2842 -- row and check the object version numbers.
2843 --
2844 open C_Sel1;
2845 Fetch C_Sel1 Into g_old_rec.payroll_action_id
2846 , g_old_rec.business_group_id
2847 , g_old_rec.consolidation_set_id
2848 , g_old_rec.action_status
2849 , g_old_rec.effective_date
2850 , g_old_rec.comments
2851 , g_old_rec.current_task
2852 , g_old_rec.legislative_parameters
2853 , g_old_rec.run_type_id
2854 , g_old_rec.date_earned
2855 , g_old_rec.pay_advice_date
2856 , g_old_rec.pay_advice_message
2857 , g_old_rec.object_version_number
2858 , p_actual_action_type
2859 , p_a_action_status
2860 , v_a_object_version_number;
2861 --
2862 If C_Sel1%notfound then
2863 Close C_Sel1;
2864 --
2865 -- The primary key is invalid therefore we must error
2866 --
2867 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2868 hr_utility.raise_error;
2869 End If;
2870 Close C_Sel1;
2871 If (p_p_object_version_number <> g_old_rec.object_version_number) or
2872 (p_a_object_version_number <> v_a_object_version_number) Then
2873 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
2874 hr_utility.raise_error;
2875 End If;
2876 --
2877 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2878 --
2879 -- We need to trap the ORA LOCK exception
2880 --
2881 Exception
2882 When HR_api.Object_Locked then
2883 --
2884 -- The object is locked therefore we need to supply a meaningful
2885 -- error message.
2886 --
2887 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
2888 hr_utility.set_message_token('TABLE_NAME', 'pay_payroll_actions');
2889 hr_utility.raise_error;
2890 End lck_general;
2891 --
2892 -- ----------------------------------------------------------------------------
2893 -- |---------------------------------< lck >----------------------------------|
2894 -- ----------------------------------------------------------------------------
2895 --
2896 procedure lck
2897 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
2898 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
2899 ,p_a_object_version_number in
2900 pay_assignment_actions.object_version_number%TYPE
2901 ) is
2902 v_unwanted_type pay_payroll_actions.action_type%TYPE;
2903 v_unwanted_status pay_assignment_actions.action_status%TYPE;
2904 begin
2905 --
2906 -- Lock the row only if it is a QuickPay Run action
2907 --
2908 lck_general
2909 (p_payroll_action_id => p_payroll_action_id
2910 ,p_p_object_version_number => p_p_object_version_number
2911 ,p_a_object_version_number => p_a_object_version_number
2912 ,p_only_action_type => 'Q'
2913 ,p_actual_action_type => v_unwanted_type
2914 ,p_a_action_status => v_unwanted_status
2915 );
2916 end lck;
2917 --
2918 -- ----------------------------------------------------------------------------
2919 -- |-----------------------------< convert_args >-----------------------------|
2920 -- ----------------------------------------------------------------------------
2921 -- {Start Of Comments}
2922 --
2923 -- Description:
2924 -- This function is used to turn attribute arguments into the record
2925 -- structure g_rec_type.
2926 --
2927 -- Pre Conditions:
2928 -- This is a private function and can only be called from the ins or upd
2929 -- attribute processes.
2930 --
2931 -- In Arguments:
2932 -- The arguments should be set to the individual attributes of the QuickPay
2933 -- Run Process.
2934 --
2935 -- Post Success:
2936 -- The individual attributes are returned in a record structure.
2937 --
2938 -- Post Failure:
2939 -- No direct error handling is required within this function. Any possible
2940 -- errors within this function will be a PL/SQL value error due to conversion
2941 -- of datatypes or data lengths.
2942 --
2943 -- Access Status:
2944 -- Internal Development Use Only.
2945 --
2946 -- {End Of Comments}
2947 --
2948 function convert_args
2949 (p_payroll_action_id in number
2950 ,p_business_group_id in number
2951 ,p_consolidation_set_id in number
2952 ,p_action_status in varchar2
2953 ,p_effective_date in date
2954 ,p_comments in varchar2
2955 ,p_current_task in varchar2
2956 ,p_legislative_parameters in varchar2
2957 ,p_run_type_id in number
2958 ,p_date_earned in date
2959 ,p_pay_advice_date in date
2960 ,p_pay_advice_message in varchar2
2961 ,p_object_version_number in number
2962 ) return g_rec_type is
2963 --
2964 v_rec g_rec_type;
2965 v_proc varchar2(72) := g_package||'convert_args';
2966 --
2967 Begin
2968 --
2969 hr_utility.set_location('Entering:'|| v_proc, 5);
2970 --
2971 -- Convert arguments into local l_rec structure.
2972 --
2973 v_rec.payroll_action_id := p_payroll_action_id;
2974 v_rec.business_group_id := p_business_group_id;
2975 v_rec.consolidation_set_id := p_consolidation_set_id;
2976 v_rec.action_status := p_action_status;
2977 v_rec.effective_date := p_effective_date;
2978 v_rec.comments := p_comments;
2979 v_rec.legislative_parameters := p_legislative_parameters;
2980 v_rec.run_type_id := p_run_type_id;
2981 v_rec.date_earned := p_date_earned;
2982 v_rec.pay_advice_date := p_pay_advice_date;
2983 v_rec.pay_advice_message := p_pay_advice_message;
2984 v_rec.object_version_number := p_object_version_number;
2985 --
2986 -- Return the plsql record structure.
2987 --
2988 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2989 return(v_rec);
2990 --
2991 End convert_args;
2992 --
2993 -- ----------------------------------------------------------------------------
2994 -- |-----------------------------< convert_defs >-----------------------------|
2995 -- ----------------------------------------------------------------------------
2996 -- {Start Of Comments}
2997 --
2998 -- Description:
2999 -- The Convert_Defs function has one very important function:
3000 -- It must return the record structure for the row with all system defaulted
3001 -- values converted into its corresponding argument value for update. When
3002 -- we attempt to update a row through the Upd business process, certain
3003 -- arguments can be defaulted which enables flexibility in the calling of
3004 -- the upd process (e.g. only attributes which need to be updated need to be
3005 -- specified). For the upd business process to determine which attributes
3006 -- have NOT been specified we need to check if the argument has a reserved
3007 -- system default value. Therefore, for all attributes which have a
3008 -- corresponding reserved system default mechanism specified we need to
3009 -- check if a system default is being used. If a system default is being
3010 -- used then we convert the defaulted value into its corresponding attribute
3011 -- value held in the g_old_rec data structure.
3012 --
3013 -- Pre Conditions:
3014 -- This private function can only be called from the upd process.
3015 --
3016 -- In Arguments:
3017 -- A Pl/Sql record structure.
3018 --
3019 -- Post Success:
3020 -- The record structure will be returned with all system defaulted argument
3021 -- values converted into its current row attribute value.
3022 --
3023 -- Post Failure:
3024 -- No direct error handling is required within this function. Any possible
3025 -- errors within this function will be a PL/SQL value error due to
3026 -- conversion of datatypes or data lengths.
3027 --
3028 -- Access Status:
3029 -- Internal Development Use Only.
3030 --
3031 -- {End Of Comments}
3032 --
3033 function convert_defs(p_rec in out nocopy g_rec_type) return g_rec_type is
3034 --
3035 v_proc varchar2(72) := g_package||'convert_defs';
3036 --
3037 Begin
3038 --
3039 hr_utility.set_location('Entering:'|| v_proc, 5);
3040 --
3041 -- We must now examine each argument value in the
3042 -- p_rec plsql record structure
3043 -- to see if a system default is being used. If a system default
3044 -- is being used then we must set to the 'current' argument value.
3045 --
3046 If (p_rec.payroll_action_id = hr_api.g_number) then
3047 p_rec.payroll_action_id := g_old_rec.payroll_action_id;
3048 End If;
3049 If (p_rec.business_group_id = hr_api.g_number) then
3050 p_rec.business_group_id := g_old_rec.business_group_id;
3051 End If;
3052 If (p_rec.consolidation_set_id = hr_api.g_number) then
3053 p_rec.consolidation_set_id := g_old_rec.consolidation_set_id;
3054 End If;
3055 If (p_rec.action_status = hr_api.g_varchar2) then
3056 p_rec.action_status := g_old_rec.action_status;
3057 End If;
3058 If (p_rec.effective_date = hr_api.g_date) then
3059 p_rec.effective_date := g_old_rec.effective_date;
3060 End If;
3061 If (p_rec.comments = hr_api.g_varchar2) then
3062 p_rec.comments := g_old_rec.comments;
3063 End If;
3064 If (p_rec.current_task = hr_api.g_varchar2) then
3065 p_rec.current_task := g_old_rec.current_task;
3066 End If;
3067 If (p_rec.legislative_parameters = hr_api.g_varchar2) then
3068 p_rec.legislative_parameters := g_old_rec.legislative_parameters;
3069 End If;
3070 If (p_rec.date_earned = hr_api.g_date) then
3071 p_rec.date_earned := g_old_rec.date_earned;
3072 End If;
3073 If (p_rec.pay_advice_date = hr_api.g_date) then
3074 p_rec.pay_advice_date := g_old_rec.pay_advice_date;
3075 End If;
3076 If (p_rec.pay_advice_message = hr_api.g_varchar2) then
3077 p_rec.pay_advice_message := g_old_rec.pay_advice_message;
3078 End If;
3079 If (p_rec.object_version_number = hr_api.g_number) then
3080 p_rec.object_version_number := g_old_rec.object_version_number;
3081 End If;
3082 If (p_rec.run_type_id = hr_api.g_number) then
3083 p_rec.run_type_id := g_old_rec.run_type_id;
3084 End If;
3085 --
3086 -- Return the plsql record structure.
3087 --
3088 hr_utility.set_location(' Leaving:'||v_proc, 10);
3089 Return(p_rec);
3090 --
3091 End convert_defs;
3092 --
3093 -- ----------------------------------------------------------------------------
3094 -- |---------------------------< insert_validate >----------------------------|
3095 -- ----------------------------------------------------------------------------
3096 -- {Start Of Comments}
3097 --
3098 -- Description:
3099 -- This procedure controls the execution of all insert business rules
3100 -- validation.
3101 --
3102 -- Pre Conditions:
3103 -- This private procedure is called from ins procedure.
3104 --
3105 -- In Arguments:
3106 -- p_rec should contain details of the QuickPay Run to validate.
3107 -- p_assignment_id should be set to the id which will be used to create
3108 -- the associated Assignment Process.
3109 --
3110 -- Post Success:
3111 -- p_payroll_id is set assignment's payroll details as of the QuickPay Run's
3112 -- p_rec.effective_date.
3113 -- p_time_period_id is set to the time period which exists as of the
3114 -- QuickPay Run's p_rec.effective_date for the payroll p_payroll_id.
3115 --
3116 -- Post Failure:
3117 -- If a business rules fails the error will not be handled by this procedure
3118 -- unless explicitly coded.
3119 --
3120 -- Access Status:
3121 -- Internal Development Use Only.
3122 --
3123 -- {End Of Comments}
3124 --
3125 procedure insert_validate
3126 (p_rec in out nocopy g_rec_type
3127 ,p_assignment_id in number
3128 ,p_payroll_id out nocopy pay_payroll_actions.payroll_id%TYPE
3129 ,p_time_period_id out nocopy pay_payroll_actions.time_period_id%TYPE
3130 ) is
3131 --
3132 v_unused_return_b boolean;
3133 v_unused_return_d date;
3134 v_legislation_code per_business_groups.legislation_code%TYPE;
3135 v_unused_return_nam per_time_periods.period_name%TYPE;
3136 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
3137 v_time_period_id pay_payroll_actions.time_period_id%TYPE;
3138 v_proc varchar2(72) := g_package||'insert_validate';
3139 --
3140 Begin
3141 hr_utility.set_location('Entering:'|| v_proc, 5);
3142 --
3143 -- Call all supporting business operations
3144 --
3145 --
3146 -- Check mandatory values have been set
3147 --
3148 check_mandatory_args(p_rec => p_rec);
3149 hr_utility.set_location(v_proc, 6);
3150 --
3151 -- Validate business group id
3152 --
3153 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3154 hr_utility.set_location(v_proc, 7);
3155 --
3156 -- Validate the assignment is in the same business group
3157 -- (Some other validate checks are done when the assignment action is
3158 -- inserted as part of the post-insert logic.)
3159 --
3160 chk_assignment
3161 (p_assignment_id => p_assignment_id
3162 ,p_business_group_id => p_rec.business_group_id
3163 ,p_effective_date => p_rec.effective_date
3164 );
3165 hr_utility.set_location(v_proc, 8);
3166 --
3167 -- Validate the consolidation set exists and it is in
3168 -- the same business group as the QuickPay.
3169 --
3170 chk_consol_set
3171 (p_consolidation_set_id => p_rec.consolidation_set_id
3172 ,p_business_group_id => p_rec.business_group_id
3173 );
3174 hr_utility.set_location(v_proc, 9);
3175 --
3176 -- Find out the legislation code for the current business group
3177 -- (Then it does not have to be derived many times in different
3178 -- business rule checks.)
3179 --
3180 v_legislation_code := get_legislation_code(p_rec.business_group_id);
3181 --
3182 -- Check the assignment is on a payroll as of the effective date.
3183 -- Check that a time period exists for the assignment's payroll as of
3184 -- the effective date.
3185 --
3186 chk_eff_date
3187 (p_effective_date => p_rec.effective_date
3188 ,p_assignment_id => p_assignment_id
3189 ,p_legislation_code => v_legislation_code
3190 ,p_recal_date_earned => false
3191 ,p_payroll_id => v_payroll_id
3192 ,p_time_period_id => v_time_period_id
3193 ,p_period_name => v_unused_return_nam
3194 ,p_new_date_earned => v_unused_return_d
3195 );
3196 hr_utility.set_location(v_proc, 11);
3197 --
3198 -- Validate the assignment is on a payroll as of date earned.
3199 -- Check that a time period exists for the assignment's payroll as of
3200 -- date earned.
3201 --
3202 chk_date_earned
3203 (p_date_earned => p_rec.date_earned
3204 ,p_assignment_id => p_assignment_id
3205 ,p_legislation_code => v_legislation_code
3206 ,p_effective_date => p_rec.effective_date
3207 );
3208 hr_utility.set_location(v_proc, 12);
3209 --
3210 -- Validate the assignment is on the same payroll
3211 -- as of the effective_date and date_earned.
3212 --
3213 chk_eff_earned_payroll
3214 (p_effective_date => p_rec.effective_date
3215 ,p_date_earned => p_rec.date_earned
3216 ,p_assignment_id => p_assignment_id
3217 ,p_raise_error => true
3218 ,p_same_payroll => v_unused_return_b
3219 );
3220 hr_utility.set_location(v_proc, 13);
3221 --
3222 -- Validate the run_type - if not null, check it exists on
3223 -- pay_run_types_f.
3224 --
3225 chk_run_type
3226 (p_run_type_id => p_rec.run_type_id
3227 ,p_effective_date => p_rec.effective_date
3228 ,p_business_group_id => p_rec.business_group_id
3229 ,p_legislation_code => v_legislation_code
3230 );
3231 --
3232 -- Set out parameters
3233 --
3234 p_payroll_id := v_payroll_id;
3235 p_time_period_id := v_time_period_id;
3236 --
3237 hr_utility.set_location(' Leaving:'|| v_proc, 14);
3238 End insert_validate;
3239 --
3240 -- ----------------------------------------------------------------------------
3241 -- |---------------------------< update_validate >----------------------------|
3242 -- ----------------------------------------------------------------------------
3243 -- {Start Of Comments}
3244 --
3245 -- Description:
3246 -- This procedure controls the execution of all update business rules
3247 -- validation.
3248 --
3249 -- Pre Conditions:
3250 -- This private procedure is called from upd procedure.
3251 --
3252 -- In Arguments:
3253 -- p_rec contains the details of the proposed QuickPay Run values.
3254 --
3255 -- Post Success:
3256 -- Processing continues.
3257 --
3258 -- Post Failure:
3259 -- If a business rules fails the error will not be handled by this procedure
3260 -- unless explicitly coded.
3261 --
3262 -- Access Status:
3263 -- Internal Development Use Only.
3264 --
3265 -- {End Of Comments}
3266 --
3267 procedure update_validate
3268 (p_rec in g_rec_type
3269 ) is
3270 --
3271 v_legislation_code per_business_groups.legislation_code%TYPE;
3272 v_proc varchar2(72) := g_package||'update_validate';
3273 --
3274 Begin
3275 hr_utility.set_location('Entering:'|| v_proc, 5);
3276 --
3277 -- Call all supporting business operations
3278 --
3279 -- Check mandatory values have been set
3280 --
3281 check_mandatory_args(p_rec => p_rec);
3282 --
3283 -- Check that the payroll_action has a null current_task.
3284 --
3285 chk_cur_task
3286 (p_current_task => p_rec.current_task
3287 );
3288 --
3289 -- Check that the fields which cannot be
3290 -- updated have not be changed.
3291 --
3292 check_non_updateable_args(p_rec => p_rec);
3293 --
3294 -- If the consolidation set has changed, validate it exists
3295 -- and it is in the same business group as the QuickPay.
3296 --
3297 if p_rec.consolidation_set_id <> g_old_rec.consolidation_set_id then
3298 chk_consol_set
3299 (p_consolidation_set_id => p_rec.consolidation_set_id
3300 ,p_business_group_id => p_rec.business_group_id
3301 );
3302 end if;
3303 --
3304 -- If the action_status has changed, validate it has only been
3305 -- changed to 'M'ark for Retry and that action is allowed to have
3306 -- a 'M'ark for Retry status. (Ensure this is always the last validation
3307 -- step because the chk_action_status procedure will update child rows.)
3308 --
3309 if p_rec.action_status <> g_old_rec.action_status then
3310 chk_action_status
3311 (p_payroll_action_id => p_rec.payroll_action_id
3312 ,p_old_action_status => g_old_rec.action_status
3313 ,p_new_action_status => p_rec.action_status
3314 );
3315 end if;
3316 --
3317 -- If run type has changed, validate the new value is correct.
3318 --
3319 if nvl(p_rec.run_type_id, hr_api.g_number) <>
3320 nvl(g_old_rec.run_type_id, hr_api.g_number) then
3321 --
3322 -- Find out the legislation code for the current business group.
3323 v_legislation_code := get_legislation_code(p_rec.business_group_id);
3324 --
3325 -- Call business rule validation.
3326 --
3327 -- Validate the run_type - if not null, check it exists on
3328 -- pay_run_types_f.
3329 --
3330 chk_run_type
3331 (p_run_type_id => p_rec.run_type_id
3332 ,p_effective_date => p_rec.effective_date
3333 ,p_business_group_id => p_rec.business_group_id
3334 ,p_legislation_code => v_legislation_code
3335 );
3336 end if;
3337 --
3338 -- The user is allowed to update the pay advice date and
3339 -- the pay advice message. No validation is required.
3340 --
3341 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3342 End update_validate;
3343 --
3344 -- ----------------------------------------------------------------------------
3345 -- |---------------------------< delete_validate >----------------------------|
3346 -- ----------------------------------------------------------------------------
3347 -- {Start Of Comments}
3348 --
3349 -- Description:
3350 -- This procedure controls the execution of all delete business rules
3351 -- validation.
3352 --
3353 -- Pre Conditions:
3354 -- This private procedure is called from del procedure.
3355 --
3356 -- In Arguments:
3357 -- A Pl/Sql record structure.
3358 --
3359 -- Post Success:
3360 -- Processing continues.
3361 --
3362 -- Post Failure:
3363 -- If a business rules fails the error will not be handled by this procedure
3364 -- unless explicitly coded.
3365 --
3366 -- Access Status:
3367 -- Internal Development Use Only.
3368 --
3369 -- {End Of Comments}
3370 --
3371 Procedure delete_validate(p_rec in g_rec_type) is
3372 --
3373 v_proc varchar2(72) := g_package||'delete_validate';
3374 --
3375 Begin
3376 hr_utility.set_location('Entering:'|| v_proc, 5);
3377 --
3378 -- Check that the payroll_action has a null current_task.
3379 --
3380 chk_cur_task
3381 (p_current_task => p_rec.current_task
3382 );
3383 --
3384 -- The following call checks the delete is valid. If this QuickPay Run
3385 -- can be removed any child rows such as pay_run_results and
3386 -- pay_run_result_values will be deleted.
3387 --
3388 py_rollback_pkg.rollback_payroll_action(
3389 p_payroll_action_id => p_rec.payroll_action_id,
3390 p_rollback_mode => 'ROLLBACK',
3391 p_leave_base_table_row => TRUE);
3392 --
3393 hr_utility.set_location('Leaving:'|| v_proc, 10);
3394 End delete_validate;
3395 --
3396 -- ----------------------------------------------------------------------------
3397 -- |---------------------------------< ins >----------------------------------|
3398 -- ----------------------------------------------------------------------------
3399 --
3400 Procedure ins
3401 (p_rec in out nocopy g_rec_type
3402 ,p_assignment_id in number
3403 ,p_assignment_action_id out nocopy number
3404 ,p_a_object_version_number out nocopy number
3405 ,p_validate in boolean default false
3406 ) is
3407 --
3408 v_proc varchar2(72) := g_package||'ins';
3409 --
3410 -- These variables are set by insert_validate and past to insert_dml
3411 --
3412 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
3413 v_time_period_id pay_payroll_actions.time_period_id%TYPE;
3414 --
3415 -- These variables are set by pre_insert and past to insert_dml
3416 --
3417 v_action_type pay_payroll_actions.action_type%TYPE;
3418 v_action_population_status pay_payroll_actions.action_population_status%TYPE;
3419 --
3420 -- These variables are set by post_insert and returned from this procedure
3421 --
3422 v_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3423 v_a_object_version_number pay_assignment_actions.object_version_number%TYPE;
3424 --
3425 Begin
3426 hr_utility.set_location('Entering:'|| v_proc, 5);
3427 --
3428 -- Determine if the business process is to be validated.
3429 --
3430 If p_validate then
3431 --
3432 -- Issue the savepoint.
3433 --
3434 SAVEPOINT ins_pay_payact;
3435 End If;
3436 --
3437 -- Call the supporting insert validate operations
3438 --
3439 insert_validate
3440 (p_rec => p_rec
3441 ,p_assignment_id => p_assignment_id
3442 ,p_payroll_id => v_payroll_id
3443 ,p_time_period_id => v_time_period_id
3444 );
3445 --
3446 -- Call the supporting pre-insert operation
3447 --
3448 pre_insert
3449 (p_rec => p_rec
3450 ,p_action_type => v_action_type
3451 ,p_action_population_status => v_action_population_status
3452 );
3453 --
3454 -- Insert the row
3455 --
3456 insert_dml
3457 (p_rec => p_rec
3458 ,p_action_type => v_action_type
3459 ,p_payroll_id => v_payroll_id
3460 ,p_time_period_id => v_time_period_id
3461 ,p_action_population_status => v_action_population_status
3462 );
3463 --
3464 -- Call the supporting post-insert operation
3465 --
3466 post_insert
3467 (p_rec => p_rec
3468 ,p_assignment_id => p_assignment_id
3469 ,p_validate => p_validate
3470 ,p_assignment_action_id => v_assignment_action_id
3471 ,p_a_object_version_number => v_a_object_version_number
3472 );
3473 --
3474 -- Set output parameters
3475 --
3476 p_assignment_action_id := v_assignment_action_id;
3477 p_a_object_version_number := v_a_object_version_number;
3478 --
3479 -- If we are validating then raise the Validate_Enabled exception
3480 --
3481 if p_validate then
3482 raise hr_api.validate_enabled;
3483 end if;
3484 --
3485 hr_utility.set_location(' Leaving:'||v_proc, 10);
3486 exception
3487 when hr_api.validate_enabled then
3488 --
3489 -- As the Validate_Enabled exception has been raised
3490 -- we must rollback to the savepoint
3491 --
3492 ROLLBACK TO ins_pay_payact;
3493 end ins;
3494 --
3495 -- ----------------------------------------------------------------------------
3496 -- |---------------------------------< ins >----------------------------------|
3497 -- ----------------------------------------------------------------------------
3498 --
3499 Procedure ins
3500 (p_business_group_id in number
3501 ,p_assignment_id in number
3502 ,p_consolidation_set_id in number
3503 ,p_effective_date in date
3504 ,p_legislative_parameters in varchar2 default null
3505 ,p_run_type_id in number
3506 ,p_date_earned in date
3507 ,p_pay_advice_date in date default null
3508 ,p_pay_advice_message in varchar2 default null
3509 ,p_comments in varchar2 default null
3510 ,p_payroll_action_id out nocopy number
3511 ,p_p_object_version_number out nocopy number
3512 ,p_assignment_action_id out nocopy number
3513 ,p_a_object_version_number out nocopy number
3514 ,p_validate in boolean default false
3515 ) is
3516 --
3517 v_rec g_rec_type;
3518 v_proc varchar2(72) := g_package||'ins';
3519 --
3520 Begin
3521 hr_utility.set_location('Entering:'||v_proc, 5);
3522 --
3523 -- Call conversion function to turn arguments into the
3524 -- p_rec structure.
3525 --
3526 v_rec :=
3527 convert_args
3528 (p_payroll_action_id => null
3529 ,p_business_group_id => p_business_group_id
3530 ,p_consolidation_set_id => p_consolidation_set_id
3531 ,p_action_status => null
3532 ,p_effective_date => p_effective_date
3533 ,p_comments => p_comments
3534 ,p_current_task => null
3535 ,p_legislative_parameters => p_legislative_parameters
3536 ,p_run_type_id => p_run_type_id
3537 ,p_date_earned => p_date_earned
3538 ,p_pay_advice_date => p_pay_advice_date
3539 ,p_pay_advice_message => p_pay_advice_message
3540 ,p_object_version_number => null
3541 );
3542 hr_utility.set_location(v_proc, 6);
3543 --
3544 -- Having converted the arguments into the pay_payact_rec
3545 -- plsql record structure we call the corresponding record business process.
3546 --
3547 ins
3548 (p_rec => v_rec
3549 ,p_assignment_id => p_assignment_id
3550 ,p_assignment_action_id => p_assignment_action_id
3551 ,p_a_object_version_number => p_a_object_version_number
3552 ,p_validate => p_validate
3553 );
3554 hr_utility.set_location(v_proc, 7);
3555 --
3556 -- As the primary key argument(s)
3557 -- are specified as an OUT's we must set these values.
3558 -- (p_a_object_version_number and p_assignment_action_id have
3559 -- been set by the 'ins' procedure.)
3560 --
3561 p_payroll_action_id := v_rec.payroll_action_id;
3562 p_p_object_version_number := v_rec.object_version_number;
3563 --
3564 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3565 End ins;
3566 --
3567 -- ----------------------------------------------------------------------------
3568 -- |---------------------------------< upd >----------------------------------|
3569 -- ----------------------------------------------------------------------------
3570 --
3571 procedure upd
3572 (p_rec in out nocopy g_rec_type
3573 ,p_assignment_action_id in number
3574 ,p_a_object_version_number in number
3575 ,p_validate in boolean default false
3576 ) is
3577 --
3578 v_proc varchar2(72) := g_package||'upd';
3579 --
3580 Begin
3581 hr_utility.set_location('Entering:'|| v_proc, 5);
3582 --
3583 -- Determine if the business process is to be validated.
3584 --
3585 If p_validate then
3586 --
3587 -- Issue the savepoint.
3588 --
3589 SAVEPOINT upd_pay_payact;
3590 End If;
3591 --
3592 -- We must lock the row which we need to update.
3593 --
3594 lck
3595 (p_payroll_action_id => p_rec.payroll_action_id
3596 ,p_p_object_version_number => p_rec.object_version_number
3597 ,p_a_object_version_number => p_a_object_version_number
3598 );
3599 hr_utility.set_location(v_proc, 6);
3600 --
3601 -- 1. During an update system defaults are used to determine if
3602 -- arguments have been defaulted or not. We must therefore
3603 -- derive the full record structure values to be updated.
3604 --
3605 -- 2. Call the supporting update validate operations.
3606 --
3607 update_validate
3608 (p_rec => convert_defs(p_rec)
3609 );
3610 --
3611 -- Call the supporting pre-update operation
3612 --
3613 pre_update(p_rec);
3614 --
3615 -- Update the row.
3616 --
3617 update_dml(p_rec);
3618 --
3619 -- Call the supporting post-update operation
3620 --
3621 post_update(p_rec);
3622 --
3623 -- If we are validating then raise the Validate_Enabled exception
3624 --
3625 if p_validate then
3626 raise hr_api.validate_enabled;
3627 end if;
3628 --
3629 hr_utility.set_location(' Leaving:'||v_proc, 10);
3630 exception
3631 when hr_api.validate_enabled Then
3632 --
3633 -- As the Validate_Enabled exception has been raised
3634 -- we must rollback to the savepoint
3635 --
3636 ROLLBACK TO upd_pay_payact;
3637 end upd;
3638 --
3639 -- ----------------------------------------------------------------------------
3640 -- |---------------------------------< upd >----------------------------------|
3641 -- ----------------------------------------------------------------------------
3642 --
3643 procedure upd
3644 (p_payroll_action_id in number
3645 ,p_consolidation_set_id in number default hr_api.g_number
3646 ,p_legislative_parameters in varchar2 default hr_api.g_varchar2
3647 ,p_run_type_id in number default hr_api.g_number
3648 ,p_pay_advice_date in date default hr_api.g_date
3649 ,p_pay_advice_message in varchar2 default hr_api.g_varchar2
3650 ,p_action_status in varchar2 default hr_api.g_varchar2
3651 ,p_comments in varchar2 default hr_api.g_varchar2
3652 ,p_assignment_action_id in number
3653 ,p_p_object_version_number in out nocopy number
3654 ,p_a_object_version_number in number
3655 ,p_validate in boolean default false
3656 ) is
3657 --
3658 v_rec g_rec_type;
3659 v_proc varchar2(72) := g_package||'upd';
3660 --
3661 Begin
3662 hr_utility.set_location('Entering:'|| v_proc, 5);
3663 --
3664 -- Call conversion function to turn arguments into the
3665 -- l_rec structure.
3666 --
3667 v_rec :=
3668 convert_args
3669 (p_payroll_action_id => p_payroll_action_id
3670 ,p_business_group_id => hr_api.g_number
3671 ,p_consolidation_set_id => p_consolidation_set_id
3672 ,p_action_status => p_action_status
3673 ,p_effective_date => hr_api.g_date
3674 ,p_comments => p_comments
3675 ,p_current_task => hr_api.g_varchar2
3676 ,p_legislative_parameters => p_legislative_parameters
3677 ,p_run_type_id => p_run_type_id
3678 ,p_date_earned => hr_api.g_date
3679 ,p_pay_advice_date => p_pay_advice_date
3680 ,p_pay_advice_message => p_pay_advice_message
3681 ,p_object_version_number => p_p_object_version_number
3682 );
3683 hr_utility.set_location(v_proc, 6);
3684 --
3685 -- Having converted the arguments into the
3686 -- plsql record structure we call the corresponding record
3687 -- business process.
3688 --
3689 upd
3690 (p_rec => v_rec
3691 ,p_assignment_action_id => p_assignment_action_id
3692 ,p_a_object_version_number => p_a_object_version_number
3693 ,p_validate => p_validate
3694 );
3695 p_p_object_version_number := v_rec.object_version_number;
3696 --
3697 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3698 End upd;
3699 --
3700 -- ----------------------------------------------------------------------------
3701 -- |---------------------------------< del >----------------------------------|
3702 -- ----------------------------------------------------------------------------
3703 --
3704 procedure del
3705 (p_rec in g_rec_type
3706 ,p_a_object_version_number in number
3707 ,p_validate in boolean default false
3708 ) is
3709 --
3710 v_proc varchar2(72) := g_package||'del';
3711 --
3712 begin
3713 hr_utility.set_location('Entering:'|| v_proc, 5);
3714 --
3715 -- Determine if the business process is to be validated.
3716 --
3717 if p_validate then
3718 --
3719 -- Issue the savepoint.
3720 --
3721 SAVEPOINT del_pay_payact;
3722 end if;
3723 --
3724 -- We must lock the row which we need to delete.
3725 --
3726 lck
3727 (p_payroll_action_id => p_rec.payroll_action_id
3728 ,p_p_object_version_number => p_rec.object_version_number
3729 ,p_a_object_version_number => p_a_object_version_number
3730 );
3731 --
3732 -- Call the supporting delete validate operation
3733 --
3734 delete_validate(p_rec);
3735 --
3736 -- Call the supporting pre-delete operation
3737 --
3738 pre_delete(p_rec);
3739 --
3740 -- Delete the row.
3741 --
3742 delete_dml(p_rec);
3743 --
3744 -- Call the supporting post-delete operation
3745 --
3746 post_delete(p_rec);
3747 --
3748 -- If we are validating then raise the Validate_Enabled exception
3749 --
3750 if p_validate then
3751 raise hr_api.validate_enabled;
3752 end if;
3753 --
3754 hr_utility.set_location(' Leaving:'||v_proc, 10);
3755 exception
3756 when hr_api.validate_enabled Then
3757 --
3758 -- As the Validate_Enabled exception has been raised
3759 -- we must rollback to the savepoint
3760 --
3761 ROLLBACK TO del_pay_payact;
3762 end del;
3763 --
3764 -- ----------------------------------------------------------------------------
3765 -- |---------------------------------< del >----------------------------------|
3766 -- ----------------------------------------------------------------------------
3767 --
3768 procedure del
3769 (p_payroll_action_id in number
3770 ,p_p_object_version_number in number
3771 ,p_a_object_version_number in number
3772 ,p_validate in boolean default false
3773 ) is
3774 --
3775 v_rec g_rec_type;
3776 v_proc varchar2(72) := g_package||'del';
3777 --
3778 Begin
3779 hr_utility.set_location('Entering:'|| v_proc, 5);
3780 --
3781 -- As the delete procedure accepts a plsql record structure we do need to
3782 -- convert the arguments into the record structure.
3783 -- We don't need to call the supplied conversion argument routine as we
3784 -- only need a few attributes.
3785 --
3786 v_rec.payroll_action_id := p_payroll_action_id;
3787 v_rec.object_version_number := p_p_object_version_number;
3788 --
3789 -- Having converted the arguments into the pay_payact_rec
3790 -- plsql record structure we must call the corresponding entity
3791 -- business process
3792 --
3793 del
3794 (p_rec => v_rec
3795 ,p_a_object_version_number => p_a_object_version_number
3796 ,p_validate => p_validate
3797 );
3798 --
3799 hr_utility.set_location(' Leaving:'||v_proc, 10);
3800 End del;
3801 --
3802 -- ----------------------------------------------------------------------------
3803 -- |-----------------------------< default_values >---------------------------|
3804 -- ----------------------------------------------------------------------------
3805 --
3806 procedure default_values
3807 (p_assignment_id in pay_assignment_actions.assignment_id%TYPE
3808 ,p_df_effective_date out nocopy pay_payroll_actions.
3809 effective_date%TYPE
3810 ,p_df_date_earned out nocopy pay_payroll_actions.date_earned%TYPE
3811 ,p_period_name out nocopy per_time_periods.period_name%TYPE
3812 ,p_consolidation_set_id out nocopy pay_consolidation_sets.
3813 consolidation_set_id%TYPE
3814 ,p_consolidation_set_name out nocopy pay_consolidation_sets.
3815 consolidation_set_name%TYPE
3816 ,p_unprocessed_status out nocopy varchar2
3817 ,p_mark_for_retry_status out nocopy varchar2
3818 ,p_complete_status out nocopy varchar2
3819 ,p_in_error_status out nocopy varchar2
3820 ,p_start_run_prompt out nocopy varchar2
3821 ,p_start_pre_prompt out nocopy varchar2
3822 ,p_retry_run_prompt out nocopy varchar2
3823 ,p_rerun_pre_prompt out nocopy varchar2
3824 ,p_start_arc_prompt out nocopy varchar2
3825 ,p_retry_arc_prompt out nocopy varchar2
3826 ,p_qp_run_user_name out nocopy varchar2
3827 ) is
3828 --
3829 -- Cursor Definitions
3830 --
3831 cursor csr_eff_date is
3832 select effective_date
3833 from fnd_sessions ses
3834 where ses.session_id = userenv('sessionid');
3835 --
3836 cursor csr_payroll (v_cur_date date) is
3837 select /*+ ORDERED INDEX(asg PER_ASSIGNMENTS_F_PK) */
3838 asg.payroll_id
3839 , pro.consolidation_set_id
3840 , con.consolidation_set_name
3841 , bus.legislation_code
3842 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'U')
3843 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'M')
3844 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'C')
3845 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'E')
3846 , hr_general.decode_lookup('ACTION_TYPE', 'Q')
3847 from per_assignments_f asg
3848 , pay_payrolls_f pro
3849 , pay_consolidation_sets con
3850 , per_business_groups_perf bus
3851 where /* Assignment's Payroll details */
3852 asg.assignment_id = p_assignment_id
3853 and v_cur_date between asg.effective_start_date
3854 and asg.effective_end_date
3855 and pro.payroll_id = asg.payroll_id
3856 and v_cur_date between pro.effective_start_date
3857 and pro.effective_end_date
3858 /* Consolidation Set details */
3859 and con.consolidation_set_id = pro.consolidation_set_id
3860 /* Business group's legislation code details */
3861 and bus.business_group_id = asg.business_group_id;
3862 --
3863 cursor csr_per_dat(v_pay_id number
3864 ,v_cur_date date
3865 ) is
3866 select tim.regular_payment_date
3867 , tim.period_name
3868 from per_time_periods tim
3869 where tim.payroll_id = v_pay_id
3870 and v_cur_date between tim.start_date
3871 and tim.end_date;
3872 --
3873 --
3874 -- Local variables
3875 --
3876 -- The assignment's business group legislation_code:
3877 v_legislation_code per_business_groups.legislation_code%TYPE;
3878 --
3879 -- The assignment's payroll as of the session effective or trunc(sysdate).
3880 v_payroll_id per_assignments_f.payroll_id%TYPE;
3881 --
3882 -- Period name which exists as of v_eff_date.
3883 v_period_name per_time_periods.period_name%TYPE;
3884 --
3885 -- Period regular payment date.
3886 v_period_reg_date per_time_periods.regular_payment_date%TYPE;
3887 --
3888 -- The Form or Database session effective date.
3889 v_eff_date date;
3890 --
3891 -- Working value which is used to set the out argument p_df_effective_date.
3892 v_df_effective_date pay_payroll_actions.effective_date%TYPE;
3893 --
3894 -- Working value which is used to set the out argument p_df_date_earned.
3895 v_df_date_earned pay_payroll_actions.date_earned%TYPE;
3896 --
3897 -- Working value which is used to set the out argument p_period_name;
3898 v_df_period_name per_time_periods.period_name%TYPE;
3899 --
3900 -- Shows if the assignment is on the same payroll as of two dates.
3901 v_same_payroll boolean;
3902 --
3903 v_proc varchar2(72) := g_package||'default_values';
3904 --
3905 begin
3906 hr_utility.set_location('Entering:'|| v_proc, 5);
3907 --
3908 -- Work out which date to drive off. Try to use the Form's session effective
3909 -- date. If that has not been defined for this database session then
3910 -- use trunc(sysdate).
3911 --
3912 open csr_eff_date;
3913 fetch csr_eff_date into v_eff_date;
3914 if csr_eff_date%notfound then
3915 v_eff_date := trunc(sysdate);
3916 end if;
3917 close csr_eff_date;
3918 hr_utility.set_location(v_proc, 6);
3919 --
3920 -- Obtain the assignment's payroll, consolidation set details,
3921 -- action status meanings, QuickPay Run user description and
3922 -- the business group's legislation details.
3923 --
3924 open csr_payroll (v_eff_date);
3925 fetch csr_payroll into v_payroll_id
3926 , p_consolidation_set_id
3927 , p_consolidation_set_name
3928 , v_legislation_code
3929 , p_unprocessed_status
3930 , p_mark_for_retry_status
3931 , p_complete_status
3932 , p_in_error_status
3933 , p_qp_run_user_name;
3934 if csr_payroll%found then
3935 close csr_payroll;
3936 else
3937 close csr_payroll;
3938 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3939 hr_utility.set_message_token('PROCEDURE', v_proc);
3940 hr_utility.set_message_token('STEP', '7');
3941 hr_utility.raise_error;
3942 end if;
3943 hr_utility.set_location(v_proc, 8);
3944 --
3945 -- Obtain the button prompts from hr_lookups
3946 --
3947 p_start_run_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SR');
3948 p_start_pre_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SP');
3949 p_retry_run_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RR');
3950 p_rerun_pre_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RP');
3951 p_start_arc_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SA');
3952 p_retry_arc_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RA');
3953 if p_start_run_prompt is null or p_retry_run_prompt is null or
3954 p_start_pre_prompt is null or p_rerun_pre_prompt is null or
3955 p_start_arc_prompt is null or p_retry_arc_prompt is null then
3956 -- All six button labels could not be found in hr_lookups.
3957 -- (Where lookup_type = 'PAYWSRQP_BUTTONS'.)
3958 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3959 hr_utility.set_message_token('PROCEDURE', v_proc);
3960 hr_utility.set_message_token('STEP', '9');
3961 hr_utility.raise_error;
3962 end if;
3963 hr_utility.set_location(v_proc, 10);
3964 --
3965 -- Set the out arguments p_df_effective_date and p_df_date_earned
3966 -- depending on a payroll time period existing and the legislation.
3967 --
3968 -- Attempt to find a payroll time period for the session date.
3969 --
3970 open csr_per_dat (v_payroll_id, v_eff_date);
3971 fetch csr_per_dat into v_period_reg_date
3972 , v_period_name;
3973 if csr_per_dat%notfound then
3974 --
3975 -- No time period exists as of the session date. So do not
3976 -- default the date or period name out arguments.
3977 --
3978 close csr_per_dat;
3979 v_df_effective_date := null;
3980 v_df_date_earned := null;
3981 v_df_period_name := null;
3982 hr_utility.set_location(v_proc, 11);
3983 else
3984 --
3985 -- A time period does exist as of the session date. Set the
3986 -- period name out argument and set the date fields depending
3987 -- on the current legislation.
3988 --
3989 close csr_per_dat;
3990 hr_utility.set_location(v_proc, 12);
3991 --
3992 if v_legislation_code = 'GB' then
3993 --
3994 -- Only for GB legislation.
3995 --
3996 v_df_effective_date := v_period_reg_date;
3997 v_df_period_name := v_period_name;
3998 --
3999 -- Check the assignment is on the same payroll as of the specified
4000 -- effective_date and derived effective_date.
4001 --
4002 -- chk_eff_earned_payroll
4003 -- (p_effective_date => v_df_effective_date
4004 -- ,p_date_earned => v_eff_date
4005 -- ,p_assignment_id => p_assignment_id
4006 -- ,p_raise_error => false
4007 -- ,p_same_payroll => v_same_payroll
4008 -- );
4009 --if v_same_payroll then
4010 --
4011 -- The assignment is on the same payroll so attempt to derive
4012 -- the date earned value.
4013 --
4014 --v_df_date_earned := get_date_earned_for_gb
4015 -- (p_assignment_id => p_assignment_id
4016 -- ,p_effective_date => v_df_effective_date
4017 -- );
4018 --hr_utility.set_location(v_proc, 13);
4019 --
4020 -- If get_date_earned_for_gb returns a null value then a suitable
4021 -- default value could not be found or the assignment.
4022 --
4023 --if v_df_date_earned is null then
4024 -- v_df_effective_date := null;
4025 -- v_df_period_name := null;
4026 -- hr_utility.set_location(v_proc, 14);
4027 --else
4028 --
4029 -- Check the assignment is on the same payroll as of the specified
4030 -- default effective_date and default date_earned.
4031 --
4032 --chk_eff_earned_payroll
4033 -- (p_effective_date => v_df_effective_date
4034 -- ,p_date_earned => v_eff_date
4035 -- ,p_assignment_id => p_assignment_id
4036 -- ,p_raise_error => false
4037 -- ,p_same_payroll => v_same_payroll
4038 -- );
4039 --if not v_same_payroll then
4040 -- Payrolls as of v_df_effective_date and v_df_date_earned are
4041 -- different.
4042 --v_df_effective_date := null;
4043 --v_df_date_earned := null;
4044 --v_df_period_name := null;
4045 --hr_utility.set_location(v_proc, 16);
4046 --end if;
4047 --hr_utility.set_location(v_proc, 17);
4048 --end if;
4049 --hr_utility.set_location(v_proc, 18);
4050 --else -- payrolls as of v_eff_date and v_df_effective_date are different
4051 --v_df_effective_date := null;
4052 --v_df_date_earned := null;
4053 --v_df_period_name := null;
4054 --hr_utility.set_location(v_proc, 19);
4055 --end if;
4056 else
4057 --
4058 -- For all other legislations. i.e. non-GB.
4059 --
4060 v_df_effective_date := v_eff_date;
4061 v_df_date_earned := v_eff_date;
4062 v_df_period_name := v_period_name;
4063 hr_utility.set_location(v_proc, 20);
4064 end if;
4065 end if;
4066 --
4067 p_df_effective_date := v_df_effective_date;
4068 p_df_date_earned := v_df_date_earned;
4069 p_period_name := v_df_period_name;
4070 --
4071 hr_utility.set_location(' Leaving:'|| v_proc, 21);
4072 end default_values;
4073 --
4074 -- ----------------------------------------------------------------------------
4075 -- |-----------------------------< get_latest_status >------------------------|
4076 -- ----------------------------------------------------------------------------
4077 -- {Start Of Comments}
4078 --
4079 -- Description:
4080 -- Returns the latest statuses and display_run_number for a given QuickPay
4081 -- Run or QuickPay Pre-payment. Used to find out the updated Payroll and
4082 -- Assignment Process statuses after one of the C processes has been ran.
4083 --
4084 -- Pre Conditions:
4085 -- None.
4086 --
4087 -- In Arguments:
4088 -- p_payroll_action_id is the id of a QuickPay Run or QuickPay Pre-payment
4089 -- Payroll Process. This is a mandatory argument.
4090 --
4091 -- Post Success:
4092 -- p_p_action_status will be set to the Payroll Process action status.
4093 -- p_display_run_number will be set to Payroll Process display_run_number
4094 -- value. If the Payroll Process is a QuickPay Pre-payment
4095 -- p_display_run_number will always be null.
4096 -- p_a_action_status will be set to the Assignment Process action status.
4097 --
4098 -- Post Failure:
4099 -- The application error HR_7220_INVALID_PRIMARY_KEY will be raised if a
4100 -- Payroll Process does not exist with an id of p_payroll_action_id.
4101 -- p_p_action_status, p_display_run_number and p_action_status will have
4102 -- undefined values because the end of the procedure will not be reached.
4103 --
4104 -- {End Of Comments}
4105 --
4106 procedure get_latest_status
4107 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4108 ,p_p_action_status out nocopy pay_payroll_actions.action_status%TYPE
4109 ,p_display_run_number out nocopy pay_payroll_actions.
4110 display_run_number%TYPE
4111 ,p_a_action_status out nocopy pay_assignment_actions.action_status%TYPE
4112 ) is
4113 v_proc varchar2(72) := g_package||'get_latest_status';
4114 v_argument varchar2(30);
4115 --
4116 cursor cur_stat is
4117 select pya.action_status
4118 , pya.display_run_number
4119 , aga.action_status
4120 from pay_payroll_actions pya
4121 , pay_assignment_actions aga
4122 where pya.payroll_action_id = p_payroll_action_id
4123 and aga.payroll_action_id = pya.payroll_action_id;
4124 begin
4125 hr_utility.set_location('Entering:'|| v_proc, 5);
4126 --
4127 -- Check mandatory parameters have been set
4128 --
4129 hr_api.mandatory_arg_error
4130 (p_api_name => v_proc
4131 ,p_argument => 'payroll_action_id'
4132 ,p_argument_value => p_payroll_action_id
4133 );
4134 --
4135 -- Select the action_status and display_run_number
4136 -- for the payroll action.
4137 --
4138 open cur_stat;
4139 fetch cur_stat into p_p_action_status
4140 , p_display_run_number
4141 , p_a_action_status;
4142 if cur_stat%notfound then
4143 close cur_stat;
4144 -- Error: This primary key does not exist in the database.
4145 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
4146 hr_utility.raise_error;
4147 end if;
4148 close cur_stat;
4149 --
4150 hr_utility.set_location(' Leaving:'|| v_proc, 10);
4151 end get_latest_status;
4152 --
4153 -- ----------------------------------------------------------------------------
4154 -- |--------------------------< start_quickpay_process >----------------------|
4155 -- ----------------------------------------------------------------------------
4156 --
4157 procedure start_quickpay_process
4158 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4159 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
4160 ,p_a_object_version_number in pay_assignment_actions.
4161 object_version_number%TYPE
4162 ,p_status in out nocopy varchar2
4163 ) is
4164 v_proc varchar2(72) := g_package||'start_quickpay_process';
4165 v_request_id pay_payroll_actions.request_id%TYPE;
4166 v_action_type pay_payroll_actions.action_type%TYPE;
4167 v_a_action_status pay_assignment_actions.action_status%TYPE;
4168 v_leg_code per_business_groups.legislation_code%TYPE;
4169 v_process_name varchar2(30);
4170 v_first_arg varchar2(30);
4171 v_asactid varchar2(30);
4172 v_asgid varchar2(30);
4173 v_grossup pay_element_entries_f.element_entry_id%TYPE;
4174 v_displ_msg varchar2(30);
4175 --
4176 cursor get_leg_rule is
4177 select rule_mode
4178 from pay_legislative_field_info
4179 where legislation_code = v_leg_code
4180 and target_location = 'PAYWSRQP'
4181 and validation_type = 'DISPLAY'
4182 and rule_type = 'DISP_NTG_MSG';
4183
4184 --
4185 -- Enhancement 3368211
4186 -- get_grossup_entry modified to support the new QuickPay Exclusions
4187 -- model.
4188 --
4189 cursor get_grossup_entry (p_use_qpay_excl_model varchar2) is
4190 SELECT EE.element_entry_id
4191 FROM pay_element_types_f ET,
4192 pay_element_links_f EL,
4193 pay_element_entries_f EE,
4194 pay_assignment_actions ACT,
4195 pay_payroll_actions PACT
4196 WHERE ACT.assignment_action_id = v_asactid
4197 AND PACT.payroll_action_id = p_payroll_action_id
4198 AND EE.assignment_id = ACT.assignment_id
4199 AND ((p_use_qpay_excl_model = 'N'
4200 AND EXISTS (
4201 SELECT null
4202 FROM pay_quickpay_inclusions INC
4203 WHERE INC.assignment_action_id = ACT.assignment_action_id
4204 AND INC.element_entry_id = EE.element_entry_id)
4205 OR EXISTS (
4206 SELECT null
4207 FROM pay_quickpay_inclusions INC
4208 WHERE INC.assignment_action_id = ACT.assignment_action_id
4209 AND INC.element_entry_id = EE.target_entry_id)
4210 )
4211 OR
4212 (p_use_qpay_excl_model = 'Y'
4213 AND NOT (
4214 EXISTS (
4215 SELECT null
4216 FROM pay_quickpay_exclusions EXC
4217 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4218 AND EXC.element_entry_id = EE.element_entry_id)
4219 OR EXISTS (
4220 SELECT null
4221 FROM pay_quickpay_exclusions EXC
4222 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4223 AND EXC.element_entry_id = EE.target_entry_id))
4224 )
4225 )
4226 AND EE.entry_type <> 'B'
4227 AND PACT.date_earned BETWEEN EE.effective_start_date
4228 AND EE.effective_end_date
4229 AND EE.element_link_id = EL.element_link_id
4230 AND PACT.date_earned BETWEEN EL.effective_start_date
4231 AND EL.effective_end_date
4232 AND nvl(EE.date_earned, to_date('01/01/0001', 'DD/MM/YYYY')) <=
4233 PACT.date_earned
4234 AND EL.element_type_id = ET.element_type_id
4235 AND ET.grossup_flag = 'Y'
4236 AND PACT.date_earned BETWEEN ET.effective_start_date
4237 AND ET.effective_end_date
4238 AND ET.process_in_run_flag <> 'N'
4239 AND (ET.processing_type = 'N' OR EE.entry_type = 'D')
4240 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'P')
4241 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'R')
4242 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'RR')
4243 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'EE')
4244 UNION ALL
4245 SELECT EE.element_entry_id
4246 FROM pay_element_types_f ET,
4247 pay_element_links_f EL,
4248 pay_element_entries_f EE,
4249 pay_assignment_actions ACT,
4250 pay_payroll_actions PACT,
4251 pay_element_entry_values_f EEV
4252 WHERE PACT.payroll_action_id = p_payroll_action_id
4253 and PACT.payroll_action_id = ACT.payroll_action_id
4254 and ACT.action_status not in ('C', 'S')
4255 AND EE.assignment_id = ACT.assignment_id
4256 AND ((p_use_qpay_excl_model = 'N'
4257 AND EXISTS (
4258 SELECT null
4259 FROM pay_quickpay_inclusions INC
4260 WHERE INC.assignment_action_id = ACT.assignment_action_id
4261 AND INC.element_entry_id = EE.element_entry_id)
4262 OR EXISTS (
4263 SELECT null
4264 FROM pay_quickpay_inclusions INC
4265 WHERE INC.assignment_action_id = ACT.assignment_action_id
4266 AND INC.element_entry_id = EE.target_entry_id)
4267 )
4268 OR
4269 (p_use_qpay_excl_model = 'Y'
4270 AND NOT (
4271 EXISTS (
4272 SELECT null
4273 FROM pay_quickpay_exclusions EXC
4274 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4275 AND EXC.element_entry_id = EE.element_entry_id)
4276 OR EXISTS (
4277 SELECT null
4278 FROM pay_quickpay_exclusions EXC
4279 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4280 AND EXC.element_entry_id = EE.target_entry_id))
4281 )
4282 )
4283 AND EE.entry_type NOT IN ('B', 'D')
4284 AND EE.effective_start_date <= PACT.date_earned
4285 and EE.effective_end_date >=
4286 decode(ET.proration_group_id,
4287 null, PACT.date_earned,
4288 pay_interpreter_pkg.prorate_start_date (v_asactid, ET.proration_group_id))
4289 AND EE.element_link_id = EL.element_link_id
4290 AND PACT.date_earned BETWEEN EL.effective_start_date
4291 AND EL.effective_end_date
4292 AND EL.element_type_id = ET.element_type_id
4293 AND ET.grossup_flag = 'Y'
4294 AND PACT.date_earned BETWEEN ET.effective_start_date
4295 AND ET.effective_end_date
4296 AND ET.process_in_run_flag <> 'N'
4297 AND EEV.element_entry_id (+) = EE.element_entry_id
4298 AND EE.effective_start_date = nvl(EEV.effective_start_date,
4299 EE.effective_start_date)
4300 AND EE.effective_end_date = nvl(EEV.effective_end_date,
4301 EE.effective_end_date)
4302 AND ET.processing_type = 'R'
4303 AND EXISTS ( select ''
4304 from pay_payroll_actions ppa,
4305 per_time_periods ptp,
4306 pay_element_entries_f pee
4307 where pee.element_entry_id = EE.element_entry_id
4308 and ppa.payroll_action_id = ACT.payroll_action_id
4309 and ppa.payroll_id = ptp.payroll_id
4310 and PACT.date_earned between ptp.start_date
4311 and ptp.end_date
4312 and pee.effective_start_date <= ptp.end_date
4313 and pee.effective_end_date >= ptp.start_date
4314 )
4315 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'P')
4316 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'R')
4317 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'RR')
4318 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'EE');
4319 --
4320 begin
4321 hr_utility.set_location('Entering:'|| v_proc, 5);
4322 --
4323 -- Check mandatory parameters have been set
4324 --
4325 hr_api.mandatory_arg_error
4326 (p_api_name => v_proc
4327 ,p_argument => 'payroll_action_id'
4328 ,p_argument_value => p_payroll_action_id
4329 );
4330 --
4331 -- Lock the QuickPay Run or QuickPay Pre-payment action rows
4332 --
4333 lck_general
4334 (p_payroll_action_id => p_payroll_action_id
4335 ,p_p_object_version_number => p_p_object_version_number
4336 ,p_a_object_version_number => p_a_object_version_number
4337 ,p_only_action_type => null
4338 ,p_actual_action_type => v_action_type
4339 ,p_a_action_status => v_a_action_status
4340 );
4341 hr_utility.set_location(v_proc, 6);
4342 --
4343 select pbg.legislation_code
4344 into v_leg_code
4345 from per_business_groups_perf pbg,
4346 pay_payroll_actions ppa
4347 where ppa.payroll_action_id = p_payroll_action_id
4348 and pbg.business_group_id = ppa.business_group_id;
4349 --
4350 -- Bugfix for 2177986: Unable to process separate payment elements in QuickPay.
4351 -- Commenting out control_separate_check_entries.
4352 --
4353 -- if v_leg_code = 'US' or
4354 -- v_leg_code = 'CA' then
4355 -- control_separate_check_entries (pi_payroll_action_id => p_payroll_action_id);
4356 -- end if;
4357 --
4358 -- Work out which process to run.
4359 -- For a QuickPay Pre-payment always call the 'QPPREPAY' process, regardless
4360 -- of the action_status.
4361 -- For a QuickPay Run when the assignment_action action_status is
4362 -- Unprocessed call the 'QUICKPAY' process. For a QuickPay Run when the
4363 -- assignment_action action_status is Marked for Retry or In Error then call
4364 -- the 'RETRY' process.
4365 --
4366 if v_action_type = 'U' then
4367 v_process_name := 'QPPREPAY';
4368 v_first_arg := 'QPPREPAY';
4369 elsif v_action_type = 'Q' and
4370 v_a_action_status = 'U' then
4371 v_process_name := 'QUICKPAY';
4372 v_first_arg := 'QUICKPAY';
4373 elsif v_action_type = 'Q' and
4374 (v_a_action_status = 'M' or
4375 v_a_action_status = 'E') then
4376 v_process_name := 'RETRY';
4377 v_first_arg := 'RERUN';
4378 elsif v_action_type ='X' then
4379 v_process_name := 'RETRY';
4380 v_first_arg := 'RERUN';
4381 else
4382 -- Error: You have attempted to issue a Start or Retry operation, after
4383 -- querying this record but before another operation has finished.
4384 -- You need to query this record and issue the operation again.
4385 hr_utility.set_message(801, 'HR_7265_QPAY_STATUS_OUT_SYNC');
4386 hr_utility.raise_error;
4387 end if;
4388 hr_utility.set_location(v_proc, 8);
4389 --
4390 -- For quickpay, add extra check here to see if assignment has got any net
4391 -- to gross entries. If it has, then issue a warning message only if a
4392 -- legislation rule exists for DISP_NTG_MESG
4393 --
4394 if (v_process_name = 'QUICKPAY' or v_process_name = 'RETRY')
4395 then
4396 if (p_status = 'START') then
4397 open get_leg_rule;
4398 fetch get_leg_rule into v_displ_msg;
4399 if (get_leg_rule%found and v_displ_msg = 'Y') then
4400 SELECT assignment_action_id
4401 INTO v_asactid
4402 FROM pay_assignment_actions
4403 WHERE payroll_action_id = p_payroll_action_id;
4404
4405 --
4406 -- Enhancement 3368211
4407 -- Pass use_qpay_excl_model to get_grossup_entry to determine
4408 -- which QuickPay data model we should be using
4409 --
4410 open get_grossup_entry(use_qpay_excl_model);
4411 fetch get_grossup_entry into v_grossup;
4412 --
4413 if get_grossup_entry%found then
4414 p_status := 'WARNING';
4415 return;
4416 else p_status := 'CONTINUE';
4417 end if;
4418 else
4419 p_status := 'CONTINUE';
4420 end if;
4421 end if;
4422 end if;
4423
4424 if (p_status = 'CONTINUE') then
4425 --
4426 -- Submit request to AOL concurrent manager
4427 --
4428 v_request_id :=
4429 fnd_request.submit_request
4430 ('PAY'
4431 ,program => v_process_name
4432 ,description => null
4433 ,start_time => null
4434 ,sub_request => false
4435 ,argument1 => v_first_arg
4436 ,argument2 => to_char(p_payroll_action_id)
4437 );
4438 hr_utility.set_location(v_proc, 9);
4439 --
4440 -- Detect if the request was really submitted.
4441 -- If it has not then handle the error.
4442 --
4443 if v_request_id = 0 then
4444 fnd_message.raise_error;
4445 end if;
4446 hr_utility.set_location(v_proc, 11);
4447 --
4448 -- Request has been accepted update payroll_actions
4449 -- with the request details.
4450 --
4451 update pay_payroll_actions
4452 set request_id = v_request_id
4453 where payroll_action_id = p_payroll_action_id;
4454 --
4455 end if;
4456 hr_utility.set_location(' Leaving:'|| v_proc, 12);
4457 --
4458 return;
4459 --
4460 end start_quickpay_process;
4461 --
4462 -- ----------------------------------------------------------------------------
4463 -- |--------------------------< wait_quickpay_process >-----------------------|
4464 -- ----------------------------------------------------------------------------
4465 --
4466 procedure wait_quickpay_process
4467 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4468 ,p_display_run_number out nocopy pay_payroll_actions.
4469 display_run_number%TYPE
4470 ,p_a_action_status out nocopy pay_assignment_actions.action_status%TYPE
4471 ,p_process_info out nocopy varchar2
4472 ,p_request_id out nocopy pay_payroll_actions.request_id%TYPE
4473 ) is
4474 v_wait boolean;
4475 v_phase varchar2(255);
4476 v_status varchar2(255);
4477 v_dev_phase varchar2(255);
4478 v_dev_status varchar2(255);
4479 v_message varchar2(255);
4480 v_max_wait_sec number;
4481 v_interval_wait_sec number;
4482 v_request_id pay_payroll_actions.request_id%TYPE;
4483 v_p_action_status pay_payroll_actions.action_status%TYPE;
4484 v_proc varchar2(72) := g_package||'start_quickpay_run';
4485 --
4486 cursor cur_req is
4487 select request_id
4488 from pay_payroll_actions
4489 where payroll_action_id = p_payroll_action_id;
4490 --
4491 cursor cur_max is
4492 select fnd_number.canonical_to_number(parameter_value)
4493 from pay_action_parameters
4494 where parameter_name = 'QUICKPAY_MAX_WAIT_SEC';
4495 --
4496 cursor cur_intw is
4497 select fnd_number.canonical_to_number(parameter_value)
4498 from pay_action_parameters
4499 where parameter_name = 'QUICKPAY_INTERVAL_WAIT_SEC';
4500 --
4501 begin
4502 hr_utility.set_location('Entering:'|| v_proc, 5);
4503 --
4504 -- Check mandatory parameters have been set
4505 --
4506 hr_api.mandatory_arg_error
4507 (p_api_name => v_proc
4508 ,p_argument => 'payroll_action_id'
4509 ,p_argument_value => p_payroll_action_id
4510 );
4511 --
4512 -- Find out the concurrent request_id for this QuickPay
4513 --
4514 open cur_req;
4515 fetch cur_req into v_request_id;
4516 if cur_req%notfound then
4517 close cur_req;
4518 -- Error: This primary key does not exist in the database.
4519 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
4520 hr_utility.raise_error;
4521 end if;
4522 close cur_req;
4523 hr_utility.set_location(v_proc, 7);
4524 --
4525 -- Check the request_id has been set. A value of zero means the submit
4526 -- request was not accepted by the concurrent manager. (This value should
4527 -- not be in pay_payroll_actions in the first place.)
4528 --
4529 if (v_request_id is null) or (v_request_id = 0) then
4530 -- Error: You have attempted to wait for a request to finish, when no
4531 -- request has been submitted for this QuickPay Run or QuickPay
4532 -- Pre-payment.
4533 hr_utility.set_message(801, 'HR_7266_QPAY_WAIT_NO_REQUEST');
4534 hr_utility.raise_error;
4535 end if;
4536 hr_utility.set_location(v_proc, 9);
4537 --
4538 -- Attempt to find out the QuickPay Concurrent manager max wait time
4539 -- and polling interval time from pay_action_parameters. If values
4540 -- cannot be found in this table then default to a max wait of 300
4541 -- seconds and polling interval of 2 seconds.
4542 --
4543 open cur_max;
4544 fetch cur_max into v_max_wait_sec;
4545 if cur_max %notfound then
4546 close cur_max;
4547 -- Value not in table, set to the default
4548 v_max_wait_sec := 300;
4549 else
4550 close cur_max;
4551 end if;
4552 hr_utility.set_location(v_proc, 10);
4553 --
4554 open cur_intw;
4555 fetch cur_intw into v_interval_wait_sec;
4556 if cur_intw %notfound then
4557 close cur_intw;
4558 -- Value not in table, set to the default
4559 v_interval_wait_sec := 2;
4560 else
4561 close cur_intw;
4562 end if;
4563 hr_utility.set_location(v_proc, 11);
4564 --
4565 -- Waits for request to finish on the concurrent manager.
4566 -- Or gives up if the maximum wait time is reached.
4567 --
4568 v_wait := fnd_concurrent.wait_for_request
4569 (request_id => v_request_id
4570 ,interval => v_interval_wait_sec
4571 ,max_wait => v_max_wait_sec
4572 ,phase => v_phase
4573 ,status => v_status
4574 ,dev_phase => v_dev_phase
4575 ,dev_status => v_dev_status
4576 ,message => v_message
4577 );
4578 hr_utility.set_location(v_proc, 12);
4579 --
4580 -- N.B. This point in the code can be reached for one of two reasons.
4581 -- 1) The AOL process has reached a concurrent manager 'phase'
4582 -- value of 'COMPLETED'.
4583 -- 2) The wait has given up because the maximum wait time has been
4584 -- reached.
4585 -- In other words, the AOL process may have, or may not have, finished.
4586 --
4587 -- Find out the latest payroll and assignment action status
4588 --
4589 get_latest_status
4590 (p_payroll_action_id => p_payroll_action_id
4591 ,p_p_action_status => v_p_action_status
4592 ,p_display_run_number => p_display_run_number
4593 ,p_a_action_status => p_a_action_status
4594 );
4595 hr_utility.set_location(v_proc, 13);
4596 --
4597 -- Work out the process information for the caller
4598 -- (This assumes that when the payroll_action.action_status is 'Complete' or
4599 -- 'In Error' the payroll_action.current_task will not be null. At the
4600 -- time of writing this is an unfair assumption. But it will not be in
4601 -- the future.)
4602 --
4603 if v_p_action_status = 'U' or
4604 v_p_action_status = 'M' then
4605 p_process_info := 'PROCESS_NOT_STARTED';
4606 elsif v_p_action_status = 'P' then
4607 p_process_info := 'PROCESS_RUNNING';
4608 else -- (p_p_action_status is 'C' or 'E')
4609 p_process_info := 'PROCESS_FINISHED';
4610 end if;
4611 --
4612 p_request_id := v_request_id;
4613 --
4614 hr_utility.set_location(' Leaving:'|| v_proc, 14);
4615 --
4616 end wait_quickpay_process;
4617 --
4618 end pay_qpq_api;
[Home] [Help]