1 Package Body pay_qpq_api as
2 /* $Header: pyqpqrhi.pkb 120.1.12010000.3 2010/03/29 11:42:15 phattarg ship $ */
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' or p_legislation_code <> 'GB') 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 ,p_payroll_id in out nocopy pay_payroll_actions.payroll_id%TYPE
1185 ,p_time_period_id in out nocopy pay_payroll_actions.time_period_id%TYPE
1186 ,p_period_name in out nocopy per_time_periods.period_name%TYPE
1187 ) is
1188 --
1189 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
1190 v_time_period_id per_time_periods.time_period_id%TYPE;
1191 v_time_period_name per_time_periods.period_name%TYPE;
1192 v_proc varchar2(72) := g_package||'chk_date_earned';
1193 v_argument varchar2(30);
1194 v_rule_mode pay_legislation_rules.rule_mode%type;
1195 --
1196 cursor sel_pay is
1197 select asg.payroll_id
1198 from per_assignments_f asg
1199 where /* Payroll as of date_earned */
1200 asg.assignment_id = p_assignment_id
1201 and asg.payroll_id is not null
1202 and p_date_earned between asg.effective_start_date
1203 and asg.effective_end_date;
1204 --
1205 cursor get_leg_rule is
1206 select rule_mode
1207 from pay_legislation_rules
1208 where rule_type = 'ENABLE_QP_OFFSET'
1209 and legislation_code = p_legislation_code;
1210 --
1211 begin
1212 hr_utility.set_location('Entering:'|| v_proc, 5);
1213 --
1214 -- Check mandatory parameters have been set
1215 --
1216 hr_api.mandatory_arg_error
1217 (p_api_name => v_proc
1218 ,p_argument => 'date_earned'
1219 ,p_argument_value => p_date_earned
1220 );
1221 hr_api.mandatory_arg_error
1222 (p_api_name => v_proc
1223 ,p_argument => 'assignment_id'
1224 ,p_argument_value => p_assignment_id
1225 );
1226 hr_api.mandatory_arg_error
1227 (p_api_name => v_proc
1228 ,p_argument => 'legislation_code'
1229 ,p_argument_value => p_legislation_code
1230 );
1231 if p_legislation_code = 'GB' then
1232 hr_api.mandatory_arg_error
1233 (p_api_name => v_proc
1234 ,p_argument => 'effective_date'
1235 ,p_argument_value => p_effective_date
1236 );
1237 end if;
1238 --
1239 -- Ensure the assignment is on a payroll as of the date_earned.
1240 --
1241 open sel_pay;
1242 fetch sel_pay into v_payroll_id;
1243 if sel_pay%notfound then
1244 close sel_pay;
1245 -- Error: You have tried to define QuickPay for an assignment that has no
1246 -- payroll component defined at Date Earned.
1247 hr_utility.set_message(801, 'HR_7249_QPAY_ASG_PAY_D_EARNED');
1248 hr_utility.raise_error;
1249 end if;
1250 close sel_pay;
1251 hr_utility.set_location(v_proc, 6);
1252 --
1253 -- A time period must exist for the payroll
1254 -- as of date earned.
1255 --
1256 -- Possible error to check for: You have tried to define QuickPay for an
1257 -- assignment that has no payroll period defined at Date Earned.
1258 chk_period_exists
1259 (p_date => p_date_earned
1260 ,p_payroll_id => v_payroll_id
1261 ,p_message => 'HR_7250_QPAY_NO_PERIOD_D_EARN'
1262 ,p_time_period_id => v_time_period_id
1263 ,p_period_name => v_time_period_name
1264 );
1265 hr_utility.set_location(v_proc, 7);
1266 --
1267 -- For GB, carry out legislation specific checks.
1268 --
1269 if p_legislation_code = 'GB' then
1270 chk_date_earned_for_gb
1271 (p_date_earned => p_date_earned
1272 ,p_assignment_id => p_assignment_id
1273 ,p_effective_date => p_effective_date
1274 );
1275 end if;
1276 --
1277 p_payroll_id := v_payroll_id;
1278 open get_leg_rule;
1279 fetch get_leg_rule into v_rule_mode;
1280 if (v_rule_mode = 'Y') then
1281 p_time_period_id := v_time_period_id;
1282 p_period_name := v_time_period_name;
1283 end if;
1284 close get_leg_rule;
1285 --
1286 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1287 end chk_date_earned;
1288 --
1289 -- ----------------------------------------------------------------------------
1290 -- |-----------------------< chk_eff_earned_payroll >-------------------------|
1291 -- ----------------------------------------------------------------------------
1292 -- {Start Of Comments}
1293 --
1294 -- Description:
1295 -- Validates the assignment is on the same payroll as of the effective_date
1296 -- and date_earned.
1297 --
1298 -- Pre Conditions:
1299 -- The assignment (p_assignment_id) is known to exist on a payroll as of
1300 -- p_effective_date and on a payroll as of p_date_earned.
1301 --
1302 -- In Arguments:
1303 -- p_date_earned is the QuickPay date earned.
1304 -- p_effective_date is the QuickPay date paid.
1305 -- p_assignment_id is the assignment to check.
1306 -- p_raise_error indicates if the an application error message should be
1307 -- raised if the assignment is on different payrolls as of the two dates.
1308 --
1309 -- Post Success:
1310 -- p_same_payroll is set to true if the assignment is on the same payroll
1311 -- as of date earned and date paid. Otherwise it is set to false.
1312 --
1313 -- Post Failure:
1314 -- If p_same_payroll will be set to false and p_raise_error is true an
1315 -- application error is raised.
1316 --
1317 -- Access Status:
1318 -- Internal Development Use Only.
1319 --
1320 -- {End Of Comments}
1321 --
1322 procedure chk_eff_earned_payroll
1323 (p_effective_date in pay_payroll_actions.effective_date%TYPE
1324 ,p_date_earned in pay_payroll_actions.date_earned%TYPE
1325 ,p_assignment_id in pay_assignment_actions.assignment_id%TYPE
1326 ,p_raise_error in boolean
1327 ,p_same_payroll out nocopy boolean
1328 ) is
1329 --
1330 v_exists varchar2(1);
1331 v_proc varchar2(72) := g_package||'chk_eff_earned_payroll';
1332 --
1333 cursor sel_same is
1334 select 'Y'
1335 from per_assignments_f asg1
1336 , per_assignments_f asg2
1337 where /* Payroll as of date_earned */
1338 asg1.assignment_id = p_assignment_id
1339 and p_date_earned between asg1.effective_start_date
1340 and asg1.effective_end_date
1341 and asg1.payroll_id is not null
1342 /* Payroll as of the effective date */
1343 and asg2.assignment_id = p_assignment_id
1344 and p_effective_date between asg2.effective_start_date
1345 and asg2.effective_end_date
1346 and asg2.payroll_id is not null
1347 /* Payrolls are the same */
1348 and asg1.payroll_id = asg2.payroll_id;
1349 --
1350 begin
1351 hr_utility.set_location('Entering:'|| v_proc, 5);
1352 --
1353 -- Ensure the assignment is the same payroll
1354 -- as of the effective_date and date_earned.
1355 --
1356 open sel_same;
1357 fetch sel_same into v_exists;
1358 if sel_same%notfound then
1359 close sel_same;
1360 p_same_payroll := false;
1361 if p_raise_error then
1362 -- Error: You have tried to define QuickPay for an assignment that has
1363 -- different payroll components for Date Earned and Date Paid. The
1364 -- assignment must be to the same payroll for both dates.
1365 hr_utility.set_message(801, 'HR_7251_QPAY_DIFF_PAYROLLS');
1366 hr_utility.raise_error;
1367 end if;
1368 else
1369 close sel_same;
1370 p_same_payroll := true;
1371 end if;
1372 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1373 end chk_eff_earned_payroll;
1374 --
1375 -- ----------------------------------------------------------------------------
1376 -- |-------------------------< chk_action_status >----------------------------|
1377 -- ----------------------------------------------------------------------------
1378 -- {Start Of Comments}
1379 --
1380 -- Description:
1381 -- This procedure is used check the action_status has only been updated
1382 -- from 'C' (for Complete) to 'M' (for Mark for Retry). This is the only
1383 -- update which the user is allowed to do. All other action_status updates
1384 -- are only allowed from the Pre-payment process code.
1385 --
1386 -- Pre Conditions:
1387 -- None.
1388 --
1389 -- In Arguments:
1390 -- p_payroll_action_id is the id of the QuickPay Run being updated.
1391 -- p_old_action_status is set to the existing action_status in the database.
1392 -- p_new_action_status is set to the user's proposed new action_status value.
1393 --
1394 -- Post Success:
1395 -- Processing continues if the update is 'C' to 'M' and there are no other
1396 -- action interlocks preventing the update. Any run result details will be
1397 -- deleted.
1398 --
1399 -- Post Failure:
1400 -- An application error is raised if the user is trying to do any other
1401 -- update (i.e. not 'C' to 'M'). Also an error is raised if there are any
1402 -- action interlocks preventing the update.
1403 --
1404 -- Access Status:
1405 -- Internal Development Use Only.
1406 --
1407 -- {End Of Comments}
1408 --
1409 procedure chk_action_status
1410 (p_payroll_action_id in number
1411 ,p_old_action_status in varchar2
1412 ,p_new_action_status in varchar2
1413 ) is
1414 --
1415 v_proc varchar2(72) := g_package||'chk_action_status';
1416 --
1417 begin
1418 hr_utility.set_location('Entering:'||v_proc, 5);
1419 --
1420 -- Check the updated status is from 'C'omplete to 'M'ark for Retry
1421 --
1422 if (p_old_action_status not in ('C', 'S')) and (p_new_action_status <> 'M') then
1423 -- Error: You have tried to enter an invalid status for a completed
1424 -- QuickPay run. You can only update a completed assignment process
1425 -- status to Marked for Retry.
1426 hr_utility.set_message(801, 'HR_7252_QPAY_ONLY_MARK_RETRY');
1427 hr_utility.raise_error;
1428 end if;
1429 hr_utility.set_location(v_proc, 6);
1430 --
1431 -- Check that this QuickPay Run can have
1432 -- a status of Mark for Retry
1433 --
1434 py_rollback_pkg.rollback_payroll_action(
1435 p_payroll_action_id => p_payroll_action_id,
1436 p_rollback_mode => 'RETRY',
1437 p_leave_base_table_row => TRUE);
1438 --
1439 hr_utility.set_location(' Leaving:'||v_proc, 10);
1440 end chk_action_status;
1441 --
1442 -- KKAWOl : Ok, commenting chk_legislative_parameters out as this is no
1443 -- longer required. We pass run type id to the run instead.
1444 -- Will need a chk_run_type procedure.
1445 /*
1446 -- ----------------------------------------------------------------------------
1447 -- |----------------------< chk_legislative_parameters >----------------------|
1448 -- ----------------------------------------------------------------------------
1449 -- {Start Of Comments}
1450 --
1451 -- Description:
1452 -- This procedure validates the legislative_parameters attribute has been
1453 -- set correctly.
1454 --
1455 -- Pre Conditions:
1456 -- The p_business_group_id has already been validated as a business group
1457 -- which exists in the database.
1458 --
1459 -- In Arguments:
1460 -- p_legislation_code the current business group's legislation.
1461 -- p_action_status is set to the current Assignment Process action status.
1462 -- During insert p_action_status should be set to 'U', the code for
1463 -- Unprocessed.
1464 -- p_legislative_parameters is set to the legislative_parameters for the
1465 -- QuickPay Run.
1466 --
1467 -- Post Success:
1468 -- Processing will continue if the business group is for a US legislation
1469 -- and p_legislative_parameters has been set to 'R' (for Regular) or 'S'
1470 -- (for Supplemental) and p_action_status is not 'C' for Complete. Or the
1471 -- business group is for a non-US legislation and legislation_parameters
1472 -- is null.
1473 --
1474 -- Post Failure:
1475 -- An error will be raised if any of the following conditions are found:
1476 -- 1) The business group is for a US legislation and
1477 -- p_legislative_parameters is not set to 'R' or 'S'.
1478 -- 2) The business group is for a US legislation and p_action_status
1479 -- is set to 'C'. (This is because legislation_parameters cannot be
1480 -- updated if the status is Complete.)
1481 -- 3) The business group is for any non-US legislation and
1482 -- p_legislative_parameters is not null.
1483 --
1484 -- Access Status:
1485 -- Internal Development Use Only.
1486 --
1487 -- {End Of Comments}
1488 --
1489 Procedure chk_legislative_parameters
1490 (p_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE
1491 ,p_action_status varchar2
1492 ,p_legislation_code per_business_groups.legislation_code%TYPE
1493 ) Is
1494 --
1495 v_proc varchar2(72) := g_package||'chk_legislative_parameters';
1496 --
1497 begin
1498 hr_utility.set_location('Entering:'|| v_proc, 5);
1499 --
1500 if p_legislation_code = 'US' or
1501 p_legislation_code = 'CA' then
1502 --
1503 -- For a US legislation legislative_parameters cannot be updated
1504 -- when the action_status is Complete.
1505 --
1506 if p_action_status = 'C' then
1507 -- Error: You have tried to alter the Regular/Supplemental flag for a
1508 -- completed QuickPay run. You can only change this flag before a run
1509 -- commences.
1510 hr_utility.set_message(801, 'HR_7253_QPAY_REG_SUPP_NO_UPD');
1511 hr_utility.raise_error;
1512 end if;
1513 --
1514 -- When the legislation is US then legislative_parameters
1515 -- must be set to 'R' or 'S'.
1516 --
1517 if p_legislation_code = 'US' then
1518 if ((p_legislative_parameters is null) or
1519 (p_legislative_parameters <> 'R') and
1520 (p_legislative_parameters <> 'S')) then
1521 -- Error: For US Business Groups the payroll process
1522 -- legislative_parameters must be set to 'R' (for Regular) or 'S'
1523 -- (for Supplemental).
1524 hr_utility.set_message(801, 'HR_7254_QPAY_LEG_PAR_R_OR_S');
1525 hr_utility.raise_error;
1526 end if;
1527
1528 elsif p_legislation_code = 'CA' then
1529
1530 if ((p_legislative_parameters is null) or
1531 (p_legislative_parameters <> 'R') and
1532 (p_legislative_parameters <> 'N') and
1533 (p_legislative_parameters <> 'L')) then
1534
1535 -- Error: For Canadian Business Groups the payroll process
1536 -- legislative_parameters must be set to 'R' (for Regular) or
1537 -- 'N' (for Non Periodic) or 'L' (for Lump Sum).
1538
1539 hr_utility.set_message(801,'HR_7254_QPAY_LEG_PAR_R_OR_S');
1540 hr_utility.raise_error;
1541 end if;
1542
1543 end if;
1544 else
1545 --
1546 -- When the legislation is not US,CA legislative_parameters
1547 -- must be null.
1548 --
1549 if p_legislative_parameters is not null then
1550 -- Error: For this legislation, you must set the payroll process
1551 -- legislative_parameters attribute to null.
1552 hr_utility.set_message(801, 'HR_7255_QPAY_LEG_PAR_NULL');
1553 hr_utility.raise_error;
1554 end if;
1555 end if;
1556 --
1557 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1558 End chk_legislative_parameters;
1559 --
1560 */
1561 -- ----------------------------------------------------------------------------
1562 -- |-----------------------------< chk_run_type >-----------------------------|
1563 -- ----------------------------------------------------------------------------
1564 -- {Start Of Comments}
1565 --
1566 -- Description:
1567 -- This procedure is used in update and delete validation to check the
1568 -- run type value.
1569 -- a) If row exists on pay_legislative_field_info with
1570 -- field_name = 'TAX_PROCESSING_TYPE'
1571 -- target_location = 'PAYWSRQP'
1572 -- rule_type = 'RUN_TYPE_FLAG'
1573 -- rule_mode = 'Y'
1574 -- validation_name = 'DISPLAY'
1575 -- validation_type = 'ITEM_PROPERTY'
1576 -- then tax_processing_type can be not null.
1577 -- b) Foreign key to pay_run_types_f.
1578 --
1579 Procedure chk_run_type
1580 ( p_run_type_id in pay_run_types_f.run_type_id%type
1581 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
1582 ,p_business_group_id in pay_payroll_actions.business_group_id%TYPE
1583 ,p_legislation_code in per_business_groups.legislation_code%TYPE
1584 )
1585 --
1586 IS
1587 v_proc varchar2(72) := g_package||'chk_run_type';
1588 l_rule_mode pay_legislative_field_info.rule_mode%type;
1589 l_rt_exists pay_run_types_f.run_type_id%type;
1590 --
1591 cursor get_leg_rule is
1592 select lfi.rule_mode
1593 from pay_legislative_field_info lfi
1594 where lfi.field_name = 'TAX_PROCESSING_TYPE'
1595 and lfi.target_location = 'PAYWSRQP'
1596 and lfi.rule_type = 'RUN_TYPE_FLAG'
1597 and lfi.validation_type = 'ITEM_PROPERTY'
1598 and lfi.validation_name = 'DISPLAY' ;
1599 --
1600 cursor get_rt_exists is
1601 select rt.run_type_id
1602 from pay_run_types_f rt
1603 where rt.run_type_id = p_run_type_id
1604 and (rt.legislation_code = p_legislation_code
1605 or (rt.legislation_code is null
1606 and rt.business_group_id = p_business_group_id)
1607 or (rt.legislation_code is null and rt.business_group_id is null))
1608 and not exists
1609 ( select null
1610 from pay_legislative_field_info lfi
1611 where lfi.validation_type = 'EXCLUDE'
1612 and lfi.rule_type = 'DATA_VALIDATION'
1613 and lfi.field_name = 'TAX_PROCESSING_TYPE'
1614 and lfi.target_location = 'PAYWSRQP'
1615 and lfi.legislation_code = p_legislation_code
1616 and upper(lfi.validation_name) = upper(rt.run_type_name))
1617 and p_effective_date between rt.effective_start_date
1618 and rt.effective_end_date;
1619 --
1620 begin
1621 hr_utility.set_location('Entering:'|| v_proc, 5);
1622 --
1623 -- Cannot have a run type id as not null if the row on
1624 -- pay_legislative_field_info does not exist.
1625 --
1626 if p_run_type_id is not null then
1627 open get_leg_rule;
1628 fetch get_leg_rule into l_rule_mode;
1629 if ((get_leg_rule%notfound) OR
1630 (get_leg_rule%found and l_rule_mode <> 'Y')) then
1631 close get_leg_rule;
1632 hr_utility.set_message(801, 'PAY_52380_INVALID_RUN_TYPE');
1633 hr_utility.raise_error;
1634 end if;
1635 --
1636 close get_leg_rule;
1637 --
1638 -- Now check the run type is valid, i.e. exists on pay_run_types_f
1639 -- and is either global or belongs to bix grp or leg code.
1640 --
1641 open get_rt_exists;
1642 fetch get_rt_exists into l_rt_exists;
1643 if (get_rt_exists%notfound) then
1644 close get_rt_exists;
1645 hr_utility.set_message(801, 'PAY_52380_INVALID_RUN_TYPE');
1646 hr_utility.raise_error;
1647 end if;
1648 --
1649 close get_rt_exists;
1650 --
1651 end if;
1652 --
1653 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1654 End chk_run_type;
1655 --
1656 -- ----------------------------------------------------------------------------
1657 -- |-----------------------------< chk_cur_task >-----------------------------|
1658 -- ----------------------------------------------------------------------------
1659 -- {Start Of Comments}
1660 --
1661 -- Description:
1662 -- This procedure is used in update and delete validation to check the
1663 -- payroll_action current_task value. The user should not be allowed to
1664 -- update any QuickPay Run attribute or delete a QuickPay Run when the
1665 -- current_task is not null. (A not null value means a C process is
1666 -- still processing the action.)
1667 --
1668 -- Pre Conditions:
1669 -- None.
1670 --
1671 -- In Arguments:
1672 -- p_current_task set to the pay_payroll_actions.current_task of the
1673 -- current payroll_action.
1674 --
1675 -- Post Success:
1676 -- The current_task for this QuickPay Run is null. (Update or delete can
1677 -- be allowed to continue, subject to other validation.)
1678 --
1679 -- Post Failure:
1680 -- An application error is raised if the current_task value is not null.
1681 --
1682 -- {End Of Comments}
1683 --
1684 procedure chk_cur_task
1685 (p_current_task in pay_payroll_actions.current_task%TYPE
1686 ) is
1687 --
1688 v_proc varchar2(72) := g_package||'chk_cur_task';
1689 --
1690 begin
1691 hr_utility.set_location('Entering:'|| v_proc, 5);
1692 --
1693 if p_current_task is not null then
1694 -- Error: You have tried to update a field or to delete the QuickPay run
1695 -- while the run is processing.
1696 hr_utility.set_message(801, 'HR_7241_QPAY_Q_PROCESSING');
1697 hr_utility.raise_error;
1698 end if;
1699 --
1700 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1701 end chk_cur_task;
1702 --
1703 -- ----------------------------------------------------------------------------
1704 -- |-------------------------< chk_for_con_request >--------------------------|
1705 -- ----------------------------------------------------------------------------
1706 --
1707 procedure chk_for_con_request
1708 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
1709 ) is
1710 --
1711 v_request_id pay_payroll_actions.request_id%TYPE;
1712 v_bl boolean;
1713 v_phase varchar2(255);
1714 v_status varchar2(255);
1715 v_dev_phase varchar2(255);
1716 v_dev_status varchar2(255);
1717 v_message varchar2(255);
1718 v_proc varchar2(72) := g_package||'chk_for_con_request';
1719 --
1720 cursor sel_reqt is
1721 select request_id
1722 from pay_payroll_actions
1723 where payroll_action_id = p_payroll_action_id;
1724 begin
1725 hr_utility.set_location('Entering:'|| v_proc, 5);
1726 --
1727 -- Try to obtain the AOL request_id for this payroll process.
1728 --
1729 open sel_reqt;
1730 fetch sel_reqt into v_request_id;
1731 if sel_reqt%notfound then
1732 close sel_reqt;
1733 -- A row could not be found in pay_payroll_actions
1734 -- with an id of p_payroll_action_id.
1735 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1736 hr_utility.raise_error;
1737 end if;
1738 close sel_reqt;
1739 hr_utility.set_location(v_proc, 7);
1740 --
1741 -- Only need to find out the concurrent request status
1742 -- if the request_id is set and is non-zero.
1743 --
1744 if (v_request_id is not null) and (v_request_id <> 0) then
1745 hr_utility.set_location(v_proc, 8);
1746 --
1747 v_bl := fnd_concurrent.get_request_status
1748 (request_id => v_request_id
1749 ,phase => v_phase
1750 ,status => v_status
1751 ,dev_phase => v_dev_phase
1752 ,dev_status => v_dev_status
1753 ,message => v_message
1754 );
1755 hr_utility.set_location(v_proc, 9);
1756 --
1757 -- The process is still waiting or running if the
1758 -- concurrent phase value is not Completed.
1759 --
1760 if v_dev_phase <> 'COMPLETE' then
1761 -- Error: You cannot update or delete a QuickPay definition when a
1762 -- request is still running or waiting to run on the AOL concurrent
1763 -- manager. You may need to contact your system administrator to resolve
1764 -- this problem. Please quote AOL concurrent request_id *REQUEST_ID.
1765 hr_utility.set_message(801, 'HR_7264_QPAY_CON_REQ_STILL_RUN');
1766 hr_utility.set_message_token('REQUEST_ID', to_char(v_request_id));
1767 hr_utility.raise_error;
1768 end if;
1769 end if;
1770 --
1771 hr_utility.set_location(' Leaving:'|| v_proc, 15);
1772 end chk_for_con_request;
1773 --
1774 -- ----------------------------------------------------------------------------
1775 -- |------------------------< get_legislation_code >--------------------------|
1776 -- ----------------------------------------------------------------------------
1777 -- {Start Of Comments}
1778 --
1779 -- Description:
1780 -- Returns the legislation_code for a specified business group.
1781 --
1782 -- Pre Conditions:
1783 -- p_business_group_id is known to be a business group in the HR schema.
1784 --
1785 -- In Arguments:
1786 -- p_business_group_id is mandatory.
1787 --
1788 -- Post Success:
1789 -- Returns the legislation code for p_business_group_id.
1790 --
1791 -- Post Failure:
1792 -- Raises an error if the legislation code for p_business_group_id cannot
1793 -- be found.
1794 --
1795 -- Access Status:
1796 -- Internal Development Use Only.
1797 --
1798 -- {End Of Comments}
1799 --
1800 function get_legislation_code
1801 (p_business_group_id in pay_payroll_actions.business_group_id%TYPE
1802 ) return varchar2 is
1803 --
1804 cursor cur_leg is
1805 select legislation_code
1806 from per_business_groups_perf
1807 where business_group_id = p_business_group_id;
1808 --
1809 v_legislation_code per_business_groups.legislation_code%TYPE;
1810 v_proc varchar2(72) := g_package||'get_legislation_code';
1811 --
1812 Begin
1813 hr_utility.set_location('Entering:'|| v_proc, 5);
1814 --
1815 -- Find out the legislation code for the current business group
1816 --
1817 open cur_leg;
1818 fetch cur_leg into v_legislation_code;
1819 if cur_leg%notfound then
1820 close cur_leg;
1821 -- The legislation code could not be found for current business group.
1822 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1823 hr_utility.set_message_token('PROCEDURE', v_proc);
1824 hr_utility.set_message_token('STEP', '6');
1825 hr_utility.raise_error;
1826 end if;
1827 close cur_leg;
1828 --
1829 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1830 --
1831 return v_legislation_code;
1832 end get_legislation_code;
1833 --
1834 -- ----------------------------------------------------------------------------
1835 -- |------------------------< return_api_dml_status >-------------------------|
1836 -- ----------------------------------------------------------------------------
1837 --
1838 function return_api_dml_status Return Boolean Is
1839 --
1840 v_proc varchar2(72) := g_package||'return_api_dml_status';
1841 --
1842 begin
1843 hr_utility.set_location('Entering:'||v_proc, 5);
1844 --
1845 Return (nvl(g_api_dml, false));
1846 --
1847 hr_utility.set_location(' Leaving:'||v_proc, 10);
1848 end return_api_dml_status;
1849 --
1850 -- ----------------------------------------------------------------------------
1851 -- |---------------------------< constraint_error >---------------------------|
1852 -- ----------------------------------------------------------------------------
1853 -- {Start Of Comments}
1854 --
1855 -- Description:
1856 -- This procedure is called when a constraint has been violated (i.e.
1857 -- The exception hr_api.check_integrity_violated,
1858 -- hr_api.parent_integrity_violated or hr_api.child_integrity_violated has
1859 -- been raised).
1860 -- The exceptions can only be raised as follows:
1861 -- 1) A check constraint can only be violated during an INSERT or UPDATE
1862 -- dml operation.
1863 -- 2) A parent integrity constraint can only be violated during an
1864 -- INSERT or UPDATE dml operation.
1865 -- 3) A child integrity constraint can only be violated during an
1866 -- DELETE dml operation.
1867 --
1868 -- Pre Conditions:
1869 -- Either hr_api.check_integrity_violated, hr_api.parent_integrity_violated
1870 -- or hr_api.child_integrity_violated has been raised with the subsequent
1871 -- stripping of the constraint name from the generated error message text.
1872 --
1873 -- In Arguments:
1874 -- p_constraint_name is in upper format and is just the constraint name
1875 -- (e.g. not prefixed by brackets, schema owner etc).
1876 --
1877 -- Post Success:
1878 -- Development dependant.
1879 --
1880 -- Post Failure:
1881 -- Development dependant.
1882 --
1883 -- Developer Implementation Notes:
1884 -- For each constraint being checked the hr system package failure message
1885 -- has been generated as a template only. These system error messages should
1886 -- be modified as required (i.e. change the system failure message to a user
1887 -- friendly defined error message).
1888 --
1889 -- Access Status:
1890 -- Public.
1891 --
1892 -- {End Of Comments}
1893 Procedure constraint_error
1894 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
1895 --
1896 v_proc varchar2(72) := g_package||'constraint_error';
1897 --
1898 Begin
1899 hr_utility.set_location('Entering:'||v_proc, 5);
1900 --
1901 If (p_constraint_name = 'PAY_PAYRACT_ACTION_POPULAT_CHK') 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','5');
1905 hr_utility.raise_error;
1906 elsif (p_constraint_name = 'PAY_PAYRACT_ACTION_STATUS_CHK') 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','10');
1910 hr_utility.raise_error;
1911 elsif (p_constraint_name = 'PAY_PAYRACT_ACTION_TYPE_CHK') 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','15');
1915 elsif (p_constraint_name = 'PAY_PAYRACT_CURRENT_TASK_CHK') Then
1916 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1917 hr_utility.set_message_token('PROCEDURE', v_proc);
1918 hr_utility.set_message_token('STEP','20');
1919 hr_utility.raise_error;
1920 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK1') Then
1921 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1922 hr_utility.set_message_token('PROCEDURE', v_proc);
1923 hr_utility.set_message_token('STEP','25');
1924 hr_utility.raise_error;
1925 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK2') Then
1926 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1927 hr_utility.set_message_token('PROCEDURE', v_proc);
1928 hr_utility.set_message_token('STEP','30');
1929 hr_utility.raise_error;
1930 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK5') Then
1931 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1932 hr_utility.set_message_token('PROCEDURE', v_proc);
1933 hr_utility.set_message_token('STEP','35');
1934 hr_utility.raise_error;
1935 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK6') Then
1936 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1937 hr_utility.set_message_token('PROCEDURE', v_proc);
1938 hr_utility.set_message_token('STEP','40');
1939 hr_utility.raise_error;
1940 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK7') Then
1941 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1942 hr_utility.set_message_token('PROCEDURE', v_proc);
1943 hr_utility.set_message_token('STEP','45');
1944 hr_utility.raise_error;
1945 elsif (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK8') Then
1946 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1947 hr_utility.set_message_token('PROCEDURE', v_proc);
1948 hr_utility.set_message_token('STEP','50');
1949 hr_utility.raise_error;
1950 else
1951 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1952 hr_utility.set_message_token('PROCEDURE', v_proc);
1953 hr_utility.set_message_token('STEP','55');
1954 hr_utility.raise_error;
1955 end if;
1956 --
1957 hr_utility.set_location(' Leaving:'||v_proc, 10);
1958 End constraint_error;
1959 --
1960 -- ----------------------------------------------------------------------------
1961 -- |-------------------------< check_mandatory_args >-------------------------|
1962 -- ----------------------------------------------------------------------------
1963 -- {Start Of Comments}
1964 --
1965 -- Description:
1966 -- This procedure checks all known mandatory arguments to ensure that they
1967 -- are not null. This check does not include system generated attributes
1968 -- such as primary keys or object version number because usually, these
1969 -- arguments are system maintained.
1970 --
1971 -- Pre Conditions:
1972 -- None.
1973 --
1974 -- In Arguments:
1975 --
1976 -- Post Success:
1977 -- Processing continues if p_rec.business_group_id and p_rec.effective_date
1978 -- are not null.
1979 --
1980 -- Post Failure:
1981 -- If p_rec.business_group_id or p_rec.effective_date are null then an
1982 -- application error will be raised and processing is terminated.
1983 --
1984 -- Access Status:
1985 -- Internal Development Use Only.
1986 --
1987 -- {End Of Comments}
1988 --
1989 Procedure check_mandatory_args(p_rec in g_rec_type) Is
1990 --
1991 v_proc varchar2(72) := g_package||'check_mandatory_args';
1992 v_argument varchar2(30);
1993 --
1994 Begin
1995 hr_utility.set_location('Entering:'||v_proc, 5);
1996 --
1997 -- This procedure does not check action_type, action_population_status,
1998 -- action_status because they are all set at by the pre-insert procedure.
1999 -- Consolidation_set_id is has been added to this procedure because it is
2000 -- mandatory for QuickPay Runs.
2001 --
2002 hr_api.mandatory_arg_error
2003 (p_api_name => v_proc
2004 ,p_argument => 'business_group_id'
2005 ,p_argument_value => p_rec.business_group_id
2006 );
2007 hr_api.mandatory_arg_error
2008 (p_api_name => v_proc
2009 ,p_argument => 'effective_date'
2010 ,p_argument_value => p_rec.effective_date
2011 );
2012 hr_api.mandatory_arg_error
2013 (p_api_name => v_proc
2014 ,p_argument => 'consolidation_set_id'
2015 ,p_argument_value => p_rec.consolidation_set_id
2016 );
2017 --
2018 hr_utility.set_location(' Leaving:'||v_proc, 10);
2019 End check_mandatory_args;
2020 --
2021 -- ----------------------------------------------------------------------------
2022 -- |----------------------< check_non_updateable_args >-----------------------|
2023 -- ----------------------------------------------------------------------------
2024 --
2025 -- {Start Of Comments}
2026 --
2027 -- Description:
2028 -- This procedure is used to ensure that non updateable attributes have
2029 -- not been updated. If an attribute has been updated an error is generated.
2030 --
2031 -- Pre Conditions:
2032 -- g_old_rec has been populated with details of the values currently in
2033 -- the database.
2034 --
2035 -- In Arguments:
2036 -- p_rec has been populated with the updated values the user would like the
2037 -- record set to.
2038 --
2039 -- Post Success:
2040 -- Processing continues if all the non updateable attributes have not
2041 -- changed.
2042 --
2043 -- Post Failure:
2044 -- An application error is raised if any of the non updatable attributes
2045 -- have been altered.
2046 --
2047 -- {End Of Comments}
2048 --
2049 Procedure check_non_updateable_args(p_rec in g_rec_type) is
2050 --
2051 v_proc varchar2(72) := g_package||'check_non_updateable_args';
2052 v_argument varchar2(30);
2053 --
2054 Begin
2055 hr_utility.set_location('Entering:'||v_proc, 5);
2056 if nvl(p_rec.business_group_id, hr_api.g_number) <>
2057 nvl(g_old_rec.business_group_id, hr_api.g_number) then
2058 v_argument := 'business_group_id';
2059 raise hr_api.argument_changed;
2060 end if;
2061 hr_utility.set_location(v_proc, 6);
2062 --
2063 if nvl(p_rec.effective_date, hr_api.g_date) <>
2064 nvl(g_old_rec.effective_date, hr_api.g_date) then
2065 v_argument := 'effective_date';
2066 raise hr_api.argument_changed;
2067 end if;
2068 hr_utility.set_location(v_proc, 7);
2069 --
2070 if nvl(p_rec.current_task, hr_api.g_varchar2) <>
2071 nvl(g_old_rec.current_task, hr_api.g_varchar2) then
2072 v_argument := 'current_task';
2073 raise hr_api.argument_changed;
2074 end if;
2075 hr_utility.set_location(v_proc, 8);
2076 --
2077 if nvl(p_rec.date_earned, hr_api.g_date) <>
2078 nvl(g_old_rec.date_earned, hr_api.g_date) then
2079 v_argument := 'date_earned';
2080 raise hr_api.argument_changed;
2081 end if;
2082 --
2083 hr_utility.set_location(' Leaving:'||v_proc, 10);
2084 Exception
2085 When hr_api.argument_changed Then
2086 --
2087 -- A non updatetable attribute has been changed therefore we
2088 -- must report this error
2089 --
2090 hr_api.argument_changed_error
2091 (p_api_name => v_proc
2092 ,p_argument => v_argument
2093 );
2094 --
2095 End check_non_updateable_args;
2096 --
2097 -- ----------------------------------------------------------------------------
2098 -- |------------------------------< pre_insert >------------------------------|
2099 -- ----------------------------------------------------------------------------
2100 -- {Start Of Comments}
2101 --
2102 -- Description:
2103 -- This private procedure contains any processing which is required before
2104 -- the insert dml. Presently, if the entity has a corresponding primary
2105 -- key which is maintained by an associating sequence, the primary key for
2106 -- the entity will be populated with the next sequence value in
2107 -- preparation for the insert dml.
2108 --
2109 -- Pre Conditions:
2110 -- This is an internal procedure which is called from the ins procedure.
2111 --
2112 -- In Arguments:
2113 -- A Pl/Sql record structure.
2114 --
2115 -- Post Success:
2116 -- p_rec.payroll_action_id is set with the primary key value.
2117 -- p_rec.action_status is set to 'U', the code for Unprocessed.
2118 -- p_action_type is set to 'Q', the code for QuickPay Run.
2119 -- p_action_population_status is to 'U', the code for unpopulated.
2120 --
2121 -- Post Failure:
2122 -- If an error has occurred, an error message and exception will be raised
2123 -- but not handled.
2124 --
2125 -- Access Status:
2126 -- Internal Development Use Only.
2127 --
2128 -- {End Of Comments}
2129 --
2130 Procedure pre_insert
2131 (p_rec in out nocopy g_rec_type
2132 ,p_action_type out nocopy pay_payroll_actions.action_type%TYPE
2133 ,p_action_population_status out nocopy
2134 pay_payroll_actions.action_population_status%TYPE
2135 ) is
2136 --
2137 v_proc varchar2(72) := g_package||'pre_insert';
2138 --
2139 Cursor C_Sel1 is select pay_payroll_actions_s.nextval from sys.dual;
2140 --
2141 Begin
2142 hr_utility.set_location('Entering:'|| v_proc, 5);
2143 --
2144 -- Set the following attributes to there insert values.
2145 -- (payroll_id and time_period_id are during the insert_validate logic.)
2146 --
2147 --
2148 -- Set the initial action_status to unprocessed
2149 --
2150 p_rec.action_status := 'U';
2151 --
2152 -- Set pay_payroll_action columns which are only
2153 -- set by non-process code at insert time.
2154 --
2155 -- Set action_type to QuickPay Run
2156 --
2157 p_action_type := 'Q';
2158 --
2159 -- Set action_population_status to unpopulated
2160 --
2161 p_action_population_status := 'U';
2162 --
2163 -- Select the next sequence number
2164 --
2165 open C_Sel1;
2166 fetch C_Sel1 into p_rec.payroll_action_id;
2167 close C_Sel1;
2168 --
2169 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2170 End pre_insert;
2171 --
2172 -- ----------------------------------------------------------------------------
2173 -- |------------------------------< pre_update >------------------------------|
2174 -- ----------------------------------------------------------------------------
2175 -- {Start Of Comments}
2176 --
2177 -- Description:
2178 -- This private procedure contains any processing which is required before
2179 -- the update dml.
2180 --
2181 -- Pre Conditions:
2182 -- This is an internal procedure which is called from the upd procedure.
2183 --
2184 -- In Arguments:
2185 -- A Pl/Sql record structure.
2186 --
2187 -- Post Success:
2188 -- Processing continues.
2189 --
2190 -- Post Failure:
2191 -- If an error has occurred, an error message and exception will be raised
2192 -- but not handled.
2193 --
2194 -- Access Status:
2195 -- Internal Development Use Only.
2196 --
2197 Procedure pre_update(p_rec in g_rec_type) is
2198 --
2199 v_proc varchar2(72) := g_package||'pre_update';
2200 --
2201 Begin
2202 hr_utility.set_location('Entering:'|| v_proc, 5);
2203 --
2204 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2205 End pre_update;
2206 --
2207 -- ----------------------------------------------------------------------------
2208 -- |------------------------------< pre_delete >------------------------------|
2209 -- ----------------------------------------------------------------------------
2210 -- {Start Of Comments}
2211 --
2212 -- Description:
2213 -- This private procedure contains any processing which is required before
2214 -- the delete dml.
2215 --
2216 -- Pre Conditions:
2217 -- This is an internal procedure which is called from the del procedure.
2218 --
2219 -- In Arguments:
2220 -- A Pl/Sql record structure.
2221 --
2222 -- Post Success:
2223 -- Processing continues.
2224 --
2225 -- Post Failure:
2226 -- If an error has occurred, an error message and exception will be raised
2227 -- but not handled.
2228 --
2229 -- Access Status:
2230 -- Internal Development Use Only.
2231 --
2232 -- {End Of Comments}
2233 --
2234 Procedure pre_delete(p_rec in g_rec_type) is
2235 --
2236 v_proc varchar2(72) := g_package||'pre_delete';
2237 --
2238 Begin
2239 hr_utility.set_location('Entering:'|| v_proc, 5);
2240 --
2241 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2242 End pre_delete;
2243 --
2244 -- ----------------------------------------------------------------------------
2245 -- |-----------------------------< post_insert >------------------------------|
2246 -- ----------------------------------------------------------------------------
2247 -- {Start Of Comments}
2248 --
2249 -- Description:
2250 -- This private procedure contains the processing which is required after the
2251 -- insert dml. It inserts an Assignment Process row for a QuickPay Payroll
2252 -- Process and all the default QuickPay inclusions.
2253 --
2254 -- Pre Conditions:
2255 -- This is an internal procedure which is called from the ins procedure.
2256 -- All business rule validation has been done of p_assignment_id.
2257 --
2258 -- In Arguments:
2259 -- p_rec contains the details of the insert QuickPay Payroll Process.
2260 -- p_assignment_id is the assignment the Assignment Process is to be created
2261 -- for.
2262 -- p_validate should be set to the same value as the ins procedure for
2263 -- the QuickPay Run ins procedure.
2264 --
2265 -- Post Success:
2266 -- p_assignment_action_id is set to the primary key id of the created
2267 -- Assignment Process.
2268 -- p_a_object_version_number is set to the object version number for the
2269 -- Assignment Process.
2270 -- The default QuickPay inclusions have been created.
2271 --
2272 -- Post Failure:
2273 -- If an error has occurred, an error message and exception will be raised
2274 -- but not handled.
2275 --
2276 -- Access Status:
2277 -- Internal Development Use Only.
2278 --
2279 -- {End Of Comments}
2280 --
2281 procedure post_insert
2282 (p_rec in g_rec_type
2283 ,p_assignment_id in number default null
2284 ,p_validate in boolean default false
2285 ,p_assignment_action_id out nocopy number
2286 ,p_a_object_version_number out nocopy number
2287 ) is
2288 --
2289 v_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
2290 v_object_version_number pay_assignment_actions.object_version_number%TYPE;
2291 v_proc varchar2(72) := g_package||'post_insert';
2292 --
2293 begin
2294 hr_utility.set_location('Entering:'|| v_proc, 5);
2295 --
2296 -- For a QuickPay insert the assignment action
2297 -- and quickpay inclusion rows. (The payroll action and
2298 -- assignment action must be inserted in the same commit unit.
2299 -- Either would be invalid without the other.)
2300 --
2301 -- Insert assignment action
2302 --
2303 hrassact.qpassact
2304 (p_payroll_action_id => p_rec.payroll_action_id
2305 ,p_assignment_id => p_assignment_id
2306 ,p_assignment_action_id => v_assignment_action_id
2307 ,p_object_version_number => v_object_version_number
2308 );
2309 hr_utility.set_location(v_proc, 6);
2310 --
2311 -- Insert default quickpay inclusions
2312 --
2313 -- Enhancement 3368211
2314 -- Check that we are not using the new QuickPay Exclusions model before
2315 -- doing the bulk insert of QuickPay Inclusions
2316 --
2317 if use_qpay_excl_model = 'N' then
2318 pay_qpi_api.bulk_default_ins
2319 (p_assignment_action_id => v_assignment_action_id
2320 ,p_validate => p_validate
2321 );
2322 end if;
2323 --
2324 hr_utility.set_location(v_proc, 7);
2325 --
2326 p_assignment_action_id := v_assignment_action_id;
2327 p_a_object_version_number := v_object_version_number;
2328 --
2329 hr_utility.set_location('Leaving:'|| v_proc, 10);
2330 end post_insert;
2331 --
2332 -- ----------------------------------------------------------------------------
2333 -- |-----------------------------< post_update >------------------------------|
2334 -- ----------------------------------------------------------------------------
2335 -- {Start Of Comments}
2336 --
2337 -- Description:
2338 -- This private procedure contains any processing which is required after the
2339 -- update dml.
2340 --
2341 -- Pre Conditions:
2342 -- This is an internal procedure which is called from the upd procedure.
2343 --
2344 -- In Arguments:
2345 -- A Pl/Sql record structure.
2346 --
2347 -- Post Success:
2348 -- Processing continues.
2349 --
2350 -- Post Failure:
2351 -- If an error has occurred, an error message and exception will be raised
2352 -- but not handled.
2353 --
2354 -- Access Status:
2355 -- Internal Development Use Only.
2356 --
2357 -- {End Of Comments}
2358 --
2359 Procedure post_update(p_rec in g_rec_type) is
2360 --
2361 v_proc varchar2(72) := g_package||'post_update';
2362 --
2363 Begin
2364 hr_utility.set_location('Entering:'|| v_proc, 5);
2365 --
2366 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2367 End post_update;
2368 --
2369 -- ----------------------------------------------------------------------------
2370 -- |-----------------------------< post_delete >------------------------------|
2371 -- ----------------------------------------------------------------------------
2372 -- {Start Of Comments}
2373 --
2374 -- Description:
2375 -- This private procedure contains any processing which is required after the
2376 -- delete dml.
2377 --
2378 -- Pre Conditions:
2379 -- This is an internal procedure which is called from the del procedure.
2380 --
2381 -- In Arguments:
2382 -- A Pl/Sql record structure.
2383 --
2384 -- Post Success:
2385 -- Processing continues.
2386 --
2387 -- Post Failure:
2388 -- If an error has occurred, an error message and exception will be raised
2389 -- but not handled.
2390 --
2391 -- Access Status:
2392 -- Internal Development Use Only.
2393 --
2394 -- {End Of Comments}
2395 --
2396 Procedure post_delete(p_rec in g_rec_type) is
2397 --
2398 v_proc varchar2(72) := g_package||'post_delete';
2399 --
2400 Begin
2401 hr_utility.set_location('Entering:'|| v_proc, 5);
2402 --
2403 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2404 End post_delete;
2405 --
2406 -- ----------------------------------------------------------------------------
2407 -- |------------------------------< insert_dml >------------------------------|
2408 -- ----------------------------------------------------------------------------
2409 -- {Start Of Comments}
2410 --
2411 -- Description:
2412 -- This procedure controls the actual dml insert logic. The functions of this
2413 -- procedure are as follows:
2414 -- 1. Initialise the object_version_number to 1.
2415 -- 2. To set and unset the g_api_dml status as required (as we are about to
2416 -- perform dml).
2417 -- 3. To insert the row into the schema.
2418 -- 4. To trap any constraint violations that may have occurred.
2419 -- 5. To raise any other errors.
2420 --
2421 -- Pre Conditions:
2422 -- This is an internal private procedure which must be called from the ins
2423 -- procedure and must have all mandatory arguments set (except the
2424 -- object_version_number which is initialised within this procedure).
2425 --
2426 -- In Arguments:
2427 -- p_rec contains all the details of the QuickPay Run, Payroll Process.
2428 --
2429 -- Post Success:
2430 -- The QuickPay Run, Payroll Process row will be inserted into the schema.
2431 --
2432 -- Post Failure:
2433 -- On the insert dml failure it is important to note that we always reset the
2434 -- g_api_dml status to false.
2435 -- If a check or parent integrity constraint violation is raised the
2436 -- constraint_error procedure will be called.
2437 -- If any other error is reported, the error will be raised after the
2438 -- g_api_dml status is reset.
2439 --
2440 -- Access Status:
2441 -- Internal Development Use Only.
2442 --
2443 -- {End Of Comments}
2444 Procedure insert_dml
2445 (p_rec in out nocopy g_rec_type
2446 ,p_action_type in pay_payroll_actions.action_type%TYPE
2447 ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
2448 ,p_time_period_id in pay_payroll_actions.time_period_id%TYPE
2449 ,p_action_population_status in
2450 pay_payroll_actions.action_population_status%TYPE
2451 ) is
2452 --
2453 v_proc varchar2(72) := g_package||'insert_dml';
2454 --
2455 Begin
2456 hr_utility.set_location('Entering:'|| v_proc, 5);
2457 p_rec.object_version_number := 1; -- Initialise the object version
2458 --
2459 g_api_dml := true; -- Set the api dml status
2460 --
2461 -- Insert the row into: pay_payroll_actions
2462 --
2463 insert into pay_payroll_actions
2464 (payroll_action_id
2465 ,action_type
2466 ,business_group_id
2467 ,consolidation_set_id
2468 ,payroll_id
2469 ,action_population_status
2470 ,action_status
2471 ,effective_date
2472 ,comments
2473 ,current_task
2474 ,legislative_parameters
2475 ,run_type_id
2476 ,date_earned
2477 ,pay_advice_date
2478 ,pay_advice_message
2479 ,object_version_number
2480 ,time_period_id
2481 )
2482 values
2483 (p_rec.payroll_action_id
2484 ,p_action_type
2485 ,p_rec.business_group_id
2486 ,p_rec.consolidation_set_id
2487 ,p_payroll_id
2488 ,p_action_population_status
2489 ,p_rec.action_status
2490 ,p_rec.effective_date
2491 ,p_rec.comments
2492 ,null
2493 ,p_rec.legislative_parameters
2494 ,p_rec.run_type_id
2495 ,p_rec.date_earned
2496 ,p_rec.pay_advice_date
2497 ,p_rec.pay_advice_message
2498 ,p_rec.object_version_number
2499 ,p_time_period_id
2500 );
2501 --
2502 g_api_dml := false; -- Unset the api dml status
2503 --
2504 hr_utility.set_location(' Leaving:'||v_proc, 10);
2505 Exception
2506 When hr_api.check_integrity_violated Then
2507 --
2508 -- A check constraint has been violated
2509 --
2510 -- Unset the api dml status
2511 g_api_dml := false;
2512 constraint_error
2513 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2514 When hr_api.parent_integrity_violated then
2515 --
2516 -- Parent integrity has been violated
2517 --
2518 -- Unset the api dml status
2519 g_api_dml := false;
2520 constraint_error
2521 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2522 When hr_api.unique_integrity_violated Then
2523 --
2524 -- Unique integrity has been violated
2525 --
2526 g_api_dml := false; -- Unset the api dml status
2527 constraint_error
2528 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2529 When Others Then
2530 --
2531 -- Unset the api dml status
2532 --
2533 g_api_dml := false;
2534 Raise;
2535 End insert_dml;
2536 --
2537 -- ----------------------------------------------------------------------------
2538 -- |------------------------------< update_dml >------------------------------|
2539 -- ----------------------------------------------------------------------------
2540 -- {Start Of Comments}
2541 --
2542 -- Description:
2543 -- This procedure controls the actual dml update logic. The functions of this
2544 -- procedure are as follows:
2545 -- 1. Increment the object_version_number by 1.
2546 -- 2. To set and unset the g_api_dml status as required (as we are about to
2547 -- perform dml).
2548 -- 3. To update the specified row in the schema using the primary key in
2549 -- the predicates.
2550 -- 4. To trap any constraint violations that may have occurred.
2551 -- 5. To raise any other errors.
2552 --
2553 -- Pre Conditions:
2554 -- This is an internal private procedure which must be called from the upd
2555 -- procedure.
2556 --
2557 -- In Arguments:
2558 -- p_rec should contain all the value as they are going to be set on the
2559 -- database. (Expect for object_version_number.)
2560 --
2561 -- Post Success:
2562 -- The QuickPay Run will be updated in the schema.
2563 --
2564 -- Post Failure:
2565 -- On the update dml failure it is important to note that we always reset the
2566 -- g_api_dml status to false.
2567 -- If a check or parent integrity constraint violation is raised the
2568 -- constraint_error procedure will be called.
2569 -- If any other error is reported, the error will be raised after the
2570 -- g_api_dml status is reset.
2571 --
2572 -- Access Status:
2573 -- Internal Development Use Only.
2574 --
2575 -- {End Of Comments}
2576 --
2577 Procedure update_dml(p_rec in out nocopy g_rec_type) is
2578 --
2579 v_proc varchar2(72) := g_package||'update_dml';
2580 --
2581 Begin
2582 hr_utility.set_location('Entering:'|| v_proc, 5);
2583 --
2584 -- Increment the object version
2585 --
2586 p_rec.object_version_number := p_rec.object_version_number + 1;
2587 --
2588 -- Set the api dml status
2589 --
2590 g_api_dml := true;
2591 --
2592 -- Update the pay_payroll_actions Row
2593 --
2594 update pay_payroll_actions set
2595 business_group_id = p_rec.business_group_id
2596 ,consolidation_set_id = p_rec.consolidation_set_id
2597 ,action_status = p_rec.action_status
2598 ,effective_date = p_rec.effective_date
2599 ,comments = p_rec.comments
2600 ,legislative_parameters = p_rec.legislative_parameters
2601 ,run_type_id = p_rec.run_type_id
2602 ,date_earned = p_rec.date_earned
2603 ,pay_advice_date = p_rec.pay_advice_date
2604 ,pay_advice_message = p_rec.pay_advice_message
2605 ,object_version_number = p_rec.object_version_number
2606 where payroll_action_id = p_rec.payroll_action_id;
2607 --
2608 -- Unset the api dml status
2609 --
2610 g_api_dml := false;
2611 --
2612 hr_utility.set_location(' Leaving:'||v_proc, 10);
2613 --
2614 Exception
2615 When hr_api.check_integrity_violated Then
2616 --
2617 -- A check constraint has been violated
2618 --
2619 -- Unset the api dml status
2620 g_api_dml := false;
2621 constraint_error
2622 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2623 When hr_api.parent_integrity_violated then
2624 --
2625 -- Parent integrity has been violated
2626 --
2627 -- Unset the api dml status
2628 g_api_dml := false;
2629 constraint_error
2630 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2631 When hr_api.unique_integrity_violated Then
2632 --
2633 -- Unique integrity has been violated
2634 --
2635 g_api_dml := false; -- Unset the api dml status
2636 constraint_error
2637 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2638 When Others Then
2639 --
2640 -- Unset the api dml status
2641 --
2642 g_api_dml := false;
2643 Raise;
2644 end update_dml;
2645 --
2646 -- ----------------------------------------------------------------------------
2647 -- |------------------------------< delete_dml >------------------------------|
2648 -- ----------------------------------------------------------------------------
2649 -- {Start Of Comments}
2650 --
2651 -- Description:
2652 -- This procedure controls the actual dml delete logic. The functions of this
2653 -- procedure are as follows:
2654 -- 1. To set and unset the g_api_dml status as required (as we are about to
2655 -- perform dml).
2656 -- 2. To delete the specified row from the schema using the primary key in
2657 -- the predicates.
2658 -- 3. To ensure that the row was deleted.
2659 -- 4. To trap any constraint violations that may have occurred.
2660 -- 5. To raise any other errors.
2661 --
2662 -- Pre Conditions:
2663 -- This is an internal private procedure which must be called from the del
2664 -- procedure.
2665 --
2666 -- In Arguments:
2667 -- p_rec has the primary key details set. i.e. p_rec.payroll_action_id is
2668 -- not null.
2669 --
2670 -- Post Success:
2671 -- The QuickPay Run row will be delete from the schema.
2672 --
2673 -- Post Failure:
2674 -- On the delete dml failure it is important to note that we always reset the
2675 -- g_api_dml status to false.
2676 -- If a child integrity constraint violation is raised the
2677 -- constraint_error procedure will be called.
2678 -- If any other error is reported, the error will be raised after the
2679 -- g_api_dml status is reset.
2680 --
2681 -- Access Status:
2682 -- Internal Development Use Only.
2683 --
2684 -- {End Of Comments}
2685 Procedure delete_dml(p_rec in g_rec_type) is
2686 --
2687 v_proc varchar2(72) := g_package||'delete_dml';
2688 --
2689 Begin
2690 hr_utility.set_location('Entering:'|| v_proc, 5);
2691 --
2692 -- Set the api dml status
2693 --
2694 g_api_dml := true;
2695 --
2696 -- Delete the pay_payroll_actions row.
2697 --
2698 delete from pay_payroll_actions
2699 where payroll_action_id = p_rec.payroll_action_id;
2700 --
2701 -- Unset the api dml status
2702 --
2703 g_api_dml := false;
2704 --
2705 hr_utility.set_location(' Leaving:'||v_proc, 10);
2706 --
2707 Exception
2708 When hr_api.child_integrity_violated then
2709 --
2710 -- Child integrity has been violated
2711 --
2712 -- Unset the api dml status
2713 g_api_dml := false;
2714 constraint_error
2715 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
2716 When Others Then
2717 --
2718 -- Unset the api dml status
2719 --
2720 g_api_dml := false;
2721 Raise;
2722 End delete_dml;
2723 --
2724 -- ----------------------------------------------------------------------------
2725 -- |------------------------------< lck_general >-----------------------------|
2726 -- ----------------------------------------------------------------------------
2727 -- {Start Of Comments}
2728 --
2729 -- Description:
2730 -- This lock procedure can be called in two ways.
2731 -- 1) To lock a QuickPay Run or QuickPay Pre-payment.
2732 -- 2) To lock a Payroll Process of a specified type.
2733 -- This procedure will attempt to lock the Payroll Process and the associated
2734 -- Assignment Process. The row locking will only be successful if the
2735 -- rows are not currently locked by another user, the specified object
2736 -- version numbers match and there is no AOL request waiting or still
2737 -- running on the concurrent manager for the Payroll Process. If the lock is
2738 -- successfully taken, the Payroll Process row will be selected into the
2739 -- g_old_rec data structure.
2740 --
2741 -- Pre Conditions:
2742 -- None.
2743 --
2744 -- In Arguments:
2745 -- p_payroll_action_id is set to the id of the Payroll Process to be locked.
2746 -- p_p_object_version_number is set object version number of the Payroll
2747 -- Process.
2748 -- p_a_object_version_number is set object version number of the Assignment
2749 -- Process.
2750 -- p_only_action_type if the lck is to be taken out using method 2 then
2751 -- this argument should be set to the action_type of the Payroll Process.
2752 -- Otherwise p_only_action_type should be null.
2753 --
2754 -- Post Success:
2755 -- On successful completion of the Lck process the rows to be updated or
2756 -- deleted will be locked and the g_old_rec data structure will be set
2757 -- with the Payroll Process details. p_a_action_status will be set to the
2758 -- action_status of the Assignment Process.
2759 --
2760 -- Post Failure:
2761 -- The Lck process can fail for six reasons:
2762 -- 1) When attempting to lock the row the row could already be locked by
2763 -- another user. This will raise the HR_7165_OBJECT_LOCKED error.
2764 -- 2) A Payroll Process with id p_payroll_action_id doesn't exist in the HR
2765 -- Schema. This error is trapped and reported using the message name
2766 -- 'HR_7155_OBJECT_INVALID'.
2767 -- 3) The rows although existing in the HR Schema have different object
2768 -- version numbers than the object version numbers specified.
2769 -- This error is trapped and reported using the message name
2770 -- 'HR_7155_OBJECT_INVALID'.
2771 -- 4) An error is raised if an AOL concurrent request is waiting to run or
2772 -- still running on the concurrent manager for the Payroll Process.
2773 -- 5) If p_only_action_type is null the lock will fail if the Payroll
2774 -- Process is not a QuickPay Run or QuickPay Pre-payment.
2775 -- 6) If p_only_action_type has been set the lock will fail if the Payroll
2776 -- Process is not of that type.
2777 --
2778 -- Access Status:
2779 -- Internal Development Use Only.
2780 --
2781 -- {End Of Comments}
2782 --
2783 procedure lck_general
2784 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
2785 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
2786 ,p_a_object_version_number in
2787 pay_assignment_actions.object_version_number%TYPE
2788 ,p_only_action_type in pay_payroll_actions.action_type%TYPE
2789 ,p_actual_action_type out nocopy pay_payroll_actions.action_type%TYPE
2790 ,p_a_action_status out nocopy pay_assignment_actions.
2791 action_status%TYPE
2792 ) is
2793 v_a_object_version_number pay_assignment_actions.object_version_number%TYPE;
2794 --
2795 -- Cursor selects the 'current' row from the HR Schema
2796 -- (Locks pay_payroll_actions first, then pay_assignment_actions.)
2797 --
2798 Cursor C_Sel1 is
2799 select pya.payroll_action_id
2800 , pya.business_group_id
2801 , pya.consolidation_set_id
2802 , pya.action_status
2803 , pya.effective_date
2804 , pya.comments
2805 , pya.current_task
2806 , pya.legislative_parameters
2807 , pya.run_type_id
2808 , pya.date_earned
2809 , pya.pay_advice_date
2810 , pya.pay_advice_message
2811 , pya.object_version_number
2812 , pya.action_type
2813 , aga.action_status
2814 , aga.object_version_number
2815 from pay_payroll_actions pya
2816 , pay_assignment_actions aga
2817 where /* Payroll action lock */
2818 pya.payroll_action_id = p_payroll_action_id
2819 and ( pya.action_type = p_only_action_type
2820 or ( p_only_action_type is null
2821 and pya.action_type in ('Q', 'U','X')--Code added for archive
2822 )
2823 )
2824 /* Assignment action lock */
2825 and aga.payroll_action_id = pya.payroll_action_id
2826 for update nowait;
2827 --
2828 v_proc varchar2(72) := g_package||'lck_general';
2829 --
2830 Begin
2831 hr_utility.set_location('Entering:'|| v_proc, 5);
2832 --
2833 -- Check the mandatory args have been set
2834 --
2835 hr_api.mandatory_arg_error
2836 (p_api_name => v_proc
2837 ,p_argument => 'payroll_action_id'
2838 ,p_argument_value => p_payroll_action_id
2839 );
2840 hr_api.mandatory_arg_error
2841 (p_api_name => v_proc
2842 ,p_argument => 'p_object_version_number'
2843 ,p_argument_value => p_p_object_version_number
2844 );
2845 hr_api.mandatory_arg_error
2846 (p_api_name => v_proc
2847 ,p_argument => 'a_object_version_number'
2848 ,p_argument_value => p_a_object_version_number
2849 );
2850 hr_utility.set_location(v_proc, 6);
2851 --
2852 -- Additional logic specific to this entity:
2853 -- Do not allow the lock to be taken out if there is an AOL concurrent
2854 -- request waiting to run or still running on a concurrent manager.
2855 --
2856 chk_for_con_request
2857 (p_payroll_action_id => p_payroll_action_id);
2858 hr_utility.set_location(v_proc, 7);
2859 --
2860 -- If the primary key exists, we must now attempt to lock the
2861 -- row and check the object version numbers.
2862 --
2863 open C_Sel1;
2864 Fetch C_Sel1 Into g_old_rec.payroll_action_id
2865 , g_old_rec.business_group_id
2866 , g_old_rec.consolidation_set_id
2867 , g_old_rec.action_status
2868 , g_old_rec.effective_date
2869 , g_old_rec.comments
2870 , g_old_rec.current_task
2871 , g_old_rec.legislative_parameters
2872 , g_old_rec.run_type_id
2873 , g_old_rec.date_earned
2874 , g_old_rec.pay_advice_date
2875 , g_old_rec.pay_advice_message
2876 , g_old_rec.object_version_number
2877 , p_actual_action_type
2878 , p_a_action_status
2879 , v_a_object_version_number;
2880 --
2881 If C_Sel1%notfound then
2882 Close C_Sel1;
2883 --
2884 -- The primary key is invalid therefore we must error
2885 --
2886 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2887 hr_utility.raise_error;
2888 End If;
2889 Close C_Sel1;
2890 If (p_p_object_version_number <> g_old_rec.object_version_number) or
2891 (p_a_object_version_number <> v_a_object_version_number) Then
2892 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
2893 hr_utility.raise_error;
2894 End If;
2895 --
2896 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2897 --
2898 -- We need to trap the ORA LOCK exception
2899 --
2900 Exception
2901 When HR_api.Object_Locked then
2902 --
2903 -- The object is locked therefore we need to supply a meaningful
2904 -- error message.
2905 --
2906 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
2907 hr_utility.set_message_token('TABLE_NAME', 'pay_payroll_actions');
2908 hr_utility.raise_error;
2909 End lck_general;
2910 --
2911 -- ----------------------------------------------------------------------------
2912 -- |---------------------------------< lck >----------------------------------|
2913 -- ----------------------------------------------------------------------------
2914 --
2915 procedure lck
2916 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
2917 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
2918 ,p_a_object_version_number in
2919 pay_assignment_actions.object_version_number%TYPE
2920 ) is
2921 v_unwanted_type pay_payroll_actions.action_type%TYPE;
2922 v_unwanted_status pay_assignment_actions.action_status%TYPE;
2923 begin
2924 --
2925 -- Lock the row only if it is a QuickPay Run action
2926 --
2927 lck_general
2928 (p_payroll_action_id => p_payroll_action_id
2929 ,p_p_object_version_number => p_p_object_version_number
2930 ,p_a_object_version_number => p_a_object_version_number
2931 ,p_only_action_type => 'Q'
2932 ,p_actual_action_type => v_unwanted_type
2933 ,p_a_action_status => v_unwanted_status
2934 );
2935 end lck;
2936 --
2937 -- ----------------------------------------------------------------------------
2938 -- |-----------------------------< convert_args >-----------------------------|
2939 -- ----------------------------------------------------------------------------
2940 -- {Start Of Comments}
2941 --
2942 -- Description:
2943 -- This function is used to turn attribute arguments into the record
2944 -- structure g_rec_type.
2945 --
2946 -- Pre Conditions:
2947 -- This is a private function and can only be called from the ins or upd
2948 -- attribute processes.
2949 --
2950 -- In Arguments:
2951 -- The arguments should be set to the individual attributes of the QuickPay
2952 -- Run Process.
2953 --
2954 -- Post Success:
2955 -- The individual attributes are returned in a record structure.
2956 --
2957 -- Post Failure:
2958 -- No direct error handling is required within this function. Any possible
2959 -- errors within this function will be a PL/SQL value error due to conversion
2960 -- of datatypes or data lengths.
2961 --
2962 -- Access Status:
2963 -- Internal Development Use Only.
2964 --
2965 -- {End Of Comments}
2966 --
2967 function convert_args
2968 (p_payroll_action_id in number
2969 ,p_business_group_id in number
2970 ,p_consolidation_set_id in number
2971 ,p_action_status in varchar2
2972 ,p_effective_date in date
2973 ,p_comments in varchar2
2974 ,p_current_task in varchar2
2975 ,p_legislative_parameters in varchar2
2976 ,p_run_type_id in number
2977 ,p_date_earned in date
2978 ,p_pay_advice_date in date
2979 ,p_pay_advice_message in varchar2
2980 ,p_object_version_number in number
2981 ) return g_rec_type is
2982 --
2983 v_rec g_rec_type;
2984 v_proc varchar2(72) := g_package||'convert_args';
2985 --
2986 Begin
2987 --
2988 hr_utility.set_location('Entering:'|| v_proc, 5);
2989 --
2990 -- Convert arguments into local l_rec structure.
2991 --
2992 v_rec.payroll_action_id := p_payroll_action_id;
2993 v_rec.business_group_id := p_business_group_id;
2994 v_rec.consolidation_set_id := p_consolidation_set_id;
2995 v_rec.action_status := p_action_status;
2996 v_rec.effective_date := p_effective_date;
2997 v_rec.comments := p_comments;
2998 v_rec.legislative_parameters := p_legislative_parameters;
2999 v_rec.run_type_id := p_run_type_id;
3000 v_rec.date_earned := p_date_earned;
3001 v_rec.pay_advice_date := p_pay_advice_date;
3002 v_rec.pay_advice_message := p_pay_advice_message;
3003 v_rec.object_version_number := p_object_version_number;
3004 --
3005 -- Return the plsql record structure.
3006 --
3007 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3008 return(v_rec);
3009 --
3010 End convert_args;
3011 --
3012 -- ----------------------------------------------------------------------------
3013 -- |-----------------------------< convert_defs >-----------------------------|
3014 -- ----------------------------------------------------------------------------
3015 -- {Start Of Comments}
3016 --
3017 -- Description:
3018 -- The Convert_Defs function has one very important function:
3019 -- It must return the record structure for the row with all system defaulted
3020 -- values converted into its corresponding argument value for update. When
3021 -- we attempt to update a row through the Upd business process, certain
3022 -- arguments can be defaulted which enables flexibility in the calling of
3023 -- the upd process (e.g. only attributes which need to be updated need to be
3024 -- specified). For the upd business process to determine which attributes
3025 -- have NOT been specified we need to check if the argument has a reserved
3026 -- system default value. Therefore, for all attributes which have a
3027 -- corresponding reserved system default mechanism specified we need to
3028 -- check if a system default is being used. If a system default is being
3029 -- used then we convert the defaulted value into its corresponding attribute
3030 -- value held in the g_old_rec data structure.
3031 --
3032 -- Pre Conditions:
3033 -- This private function can only be called from the upd process.
3034 --
3035 -- In Arguments:
3036 -- A Pl/Sql record structure.
3037 --
3038 -- Post Success:
3039 -- The record structure will be returned with all system defaulted argument
3040 -- values converted into its current row attribute value.
3041 --
3042 -- Post Failure:
3043 -- No direct error handling is required within this function. Any possible
3044 -- errors within this function will be a PL/SQL value error due to
3045 -- conversion of datatypes or data lengths.
3046 --
3047 -- Access Status:
3048 -- Internal Development Use Only.
3049 --
3050 -- {End Of Comments}
3051 --
3052 function convert_defs(p_rec in out nocopy g_rec_type) return g_rec_type is
3053 --
3054 v_proc varchar2(72) := g_package||'convert_defs';
3055 --
3056 Begin
3057 --
3058 hr_utility.set_location('Entering:'|| v_proc, 5);
3059 --
3060 -- We must now examine each argument value in the
3061 -- p_rec plsql record structure
3062 -- to see if a system default is being used. If a system default
3063 -- is being used then we must set to the 'current' argument value.
3064 --
3065 If (p_rec.payroll_action_id = hr_api.g_number) then
3066 p_rec.payroll_action_id := g_old_rec.payroll_action_id;
3067 End If;
3068 If (p_rec.business_group_id = hr_api.g_number) then
3069 p_rec.business_group_id := g_old_rec.business_group_id;
3070 End If;
3071 If (p_rec.consolidation_set_id = hr_api.g_number) then
3072 p_rec.consolidation_set_id := g_old_rec.consolidation_set_id;
3073 End If;
3074 If (p_rec.action_status = hr_api.g_varchar2) then
3075 p_rec.action_status := g_old_rec.action_status;
3076 End If;
3077 If (p_rec.effective_date = hr_api.g_date) then
3078 p_rec.effective_date := g_old_rec.effective_date;
3079 End If;
3080 If (p_rec.comments = hr_api.g_varchar2) then
3081 p_rec.comments := g_old_rec.comments;
3082 End If;
3083 If (p_rec.current_task = hr_api.g_varchar2) then
3084 p_rec.current_task := g_old_rec.current_task;
3085 End If;
3086 If (p_rec.legislative_parameters = hr_api.g_varchar2) then
3087 p_rec.legislative_parameters := g_old_rec.legislative_parameters;
3088 End If;
3089 If (p_rec.date_earned = hr_api.g_date) then
3090 p_rec.date_earned := g_old_rec.date_earned;
3091 End If;
3092 If (p_rec.pay_advice_date = hr_api.g_date) then
3093 p_rec.pay_advice_date := g_old_rec.pay_advice_date;
3094 End If;
3095 If (p_rec.pay_advice_message = hr_api.g_varchar2) then
3096 p_rec.pay_advice_message := g_old_rec.pay_advice_message;
3097 End If;
3098 If (p_rec.object_version_number = hr_api.g_number) then
3099 p_rec.object_version_number := g_old_rec.object_version_number;
3100 End If;
3101 If (p_rec.run_type_id = hr_api.g_number) then
3102 p_rec.run_type_id := g_old_rec.run_type_id;
3103 End If;
3104 --
3105 -- Return the plsql record structure.
3106 --
3107 hr_utility.set_location(' Leaving:'||v_proc, 10);
3108 Return(p_rec);
3109 --
3110 End convert_defs;
3111 --
3112 -- ----------------------------------------------------------------------------
3113 -- |---------------------------< insert_validate >----------------------------|
3114 -- ----------------------------------------------------------------------------
3115 -- {Start Of Comments}
3116 --
3117 -- Description:
3118 -- This procedure controls the execution of all insert business rules
3119 -- validation.
3120 --
3121 -- Pre Conditions:
3122 -- This private procedure is called from ins procedure.
3123 --
3124 -- In Arguments:
3125 -- p_rec should contain details of the QuickPay Run to validate.
3126 -- p_assignment_id should be set to the id which will be used to create
3127 -- the associated Assignment Process.
3128 --
3129 -- Post Success:
3130 -- p_payroll_id is set assignment's payroll details as of the QuickPay Run's
3131 -- p_rec.effective_date.
3132 -- p_time_period_id is set to the time period which exists as of the
3133 -- QuickPay Run's p_rec.effective_date for the payroll p_payroll_id.
3134 --
3135 -- Post Failure:
3136 -- If a business rules fails the error will not be handled by this procedure
3137 -- unless explicitly coded.
3138 --
3139 -- Access Status:
3140 -- Internal Development Use Only.
3141 --
3142 -- {End Of Comments}
3143 --
3144 procedure insert_validate
3145 (p_rec in out nocopy g_rec_type
3146 ,p_assignment_id in number
3147 ,p_payroll_id out nocopy pay_payroll_actions.payroll_id%TYPE
3148 ,p_time_period_id out nocopy pay_payroll_actions.time_period_id%TYPE
3149 ) is
3150 --
3151 v_unused_return_b boolean;
3152 v_unused_return_d date;
3153 v_legislation_code per_business_groups.legislation_code%TYPE;
3154 v_unused_return_nam per_time_periods.period_name%TYPE;
3155 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
3156 v_time_period_id pay_payroll_actions.time_period_id%TYPE;
3157 v_proc varchar2(72) := g_package||'insert_validate';
3158 --
3159 Begin
3160 hr_utility.set_location('Entering:'|| v_proc, 5);
3161 --
3162 -- Call all supporting business operations
3163 --
3164 --
3165 -- Check mandatory values have been set
3166 --
3167 check_mandatory_args(p_rec => p_rec);
3168 hr_utility.set_location(v_proc, 6);
3169 --
3170 -- Validate business group id
3171 --
3172 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3173 hr_utility.set_location(v_proc, 7);
3174 --
3175 -- Validate the assignment is in the same business group
3176 -- (Some other validate checks are done when the assignment action is
3177 -- inserted as part of the post-insert logic.)
3178 --
3179 chk_assignment
3180 (p_assignment_id => p_assignment_id
3181 ,p_business_group_id => p_rec.business_group_id
3182 ,p_effective_date => p_rec.effective_date
3183 );
3184 hr_utility.set_location(v_proc, 8);
3185 --
3186 -- Validate the consolidation set exists and it is in
3187 -- the same business group as the QuickPay.
3188 --
3189 chk_consol_set
3190 (p_consolidation_set_id => p_rec.consolidation_set_id
3191 ,p_business_group_id => p_rec.business_group_id
3192 );
3193 hr_utility.set_location(v_proc, 9);
3194 --
3195 -- Find out the legislation code for the current business group
3196 -- (Then it does not have to be derived many times in different
3197 -- business rule checks.)
3198 --
3199 v_legislation_code := get_legislation_code(p_rec.business_group_id);
3200 --
3201 -- Check the assignment is on a payroll as of the effective date.
3202 -- Check that a time period exists for the assignment's payroll as of
3203 -- the effective date.
3204 --
3205 chk_eff_date
3206 (p_effective_date => p_rec.effective_date
3207 ,p_assignment_id => p_assignment_id
3208 ,p_legislation_code => v_legislation_code
3209 ,p_recal_date_earned => false
3210 ,p_payroll_id => v_payroll_id
3211 ,p_time_period_id => v_time_period_id
3212 ,p_period_name => v_unused_return_nam
3213 ,p_new_date_earned => v_unused_return_d
3214 );
3215 hr_utility.set_location(v_proc, 11);
3216 --
3217 -- Validate the assignment is on a payroll as of date earned.
3218 -- Check that a time period exists for the assignment's payroll as of
3219 -- date earned.
3220 --
3221 chk_date_earned
3222 (p_date_earned => p_rec.date_earned
3223 ,p_assignment_id => p_assignment_id
3224 ,p_legislation_code => v_legislation_code
3225 ,p_effective_date => p_rec.effective_date
3226 ,p_payroll_id => v_payroll_id
3227 ,p_time_period_id => v_time_period_id
3228 ,p_period_name => v_unused_return_nam
3229 );
3230 hr_utility.set_location(v_proc, 12);
3231 --
3232 -- Validate the assignment is on the same payroll
3233 -- as of the effective_date and date_earned.
3234 --
3235 chk_eff_earned_payroll
3236 (p_effective_date => p_rec.effective_date
3237 ,p_date_earned => p_rec.date_earned
3238 ,p_assignment_id => p_assignment_id
3239 ,p_raise_error => true
3240 ,p_same_payroll => v_unused_return_b
3241 );
3242 hr_utility.set_location(v_proc, 13);
3243 --
3244 -- Validate the run_type - if not null, check it exists on
3245 -- pay_run_types_f.
3246 --
3247 chk_run_type
3248 (p_run_type_id => p_rec.run_type_id
3249 ,p_effective_date => p_rec.effective_date
3250 ,p_business_group_id => p_rec.business_group_id
3251 ,p_legislation_code => v_legislation_code
3252 );
3253 --
3254 -- Set out parameters
3255 --
3256 p_payroll_id := v_payroll_id;
3257 p_time_period_id := v_time_period_id;
3258 --
3259 hr_utility.set_location(' Leaving:'|| v_proc, 14);
3260 End insert_validate;
3261 --
3262 -- ----------------------------------------------------------------------------
3263 -- |---------------------------< update_validate >----------------------------|
3264 -- ----------------------------------------------------------------------------
3265 -- {Start Of Comments}
3266 --
3267 -- Description:
3268 -- This procedure controls the execution of all update business rules
3269 -- validation.
3270 --
3271 -- Pre Conditions:
3272 -- This private procedure is called from upd procedure.
3273 --
3274 -- In Arguments:
3275 -- p_rec contains the details of the proposed QuickPay Run values.
3276 --
3277 -- Post Success:
3278 -- Processing continues.
3279 --
3280 -- Post Failure:
3281 -- If a business rules fails the error will not be handled by this procedure
3282 -- unless explicitly coded.
3283 --
3284 -- Access Status:
3285 -- Internal Development Use Only.
3286 --
3287 -- {End Of Comments}
3288 --
3289 procedure update_validate
3290 (p_rec in g_rec_type
3291 ) is
3292 --
3293 v_legislation_code per_business_groups.legislation_code%TYPE;
3294 v_proc varchar2(72) := g_package||'update_validate';
3295 --
3296 Begin
3297 hr_utility.set_location('Entering:'|| v_proc, 5);
3298 --
3299 -- Call all supporting business operations
3300 --
3301 -- Check mandatory values have been set
3302 --
3303 check_mandatory_args(p_rec => p_rec);
3304 --
3305 -- Check that the payroll_action has a null current_task.
3306 --
3307 chk_cur_task
3308 (p_current_task => p_rec.current_task
3309 );
3310 --
3311 -- Check that the fields which cannot be
3312 -- updated have not be changed.
3313 --
3314 check_non_updateable_args(p_rec => p_rec);
3315 --
3316 -- If the consolidation set has changed, validate it exists
3317 -- and it is in the same business group as the QuickPay.
3318 --
3319 if p_rec.consolidation_set_id <> g_old_rec.consolidation_set_id then
3320 chk_consol_set
3321 (p_consolidation_set_id => p_rec.consolidation_set_id
3322 ,p_business_group_id => p_rec.business_group_id
3323 );
3324 end if;
3325 --
3326 -- If the action_status has changed, validate it has only been
3327 -- changed to 'M'ark for Retry and that action is allowed to have
3328 -- a 'M'ark for Retry status. (Ensure this is always the last validation
3329 -- step because the chk_action_status procedure will update child rows.)
3330 --
3331 if p_rec.action_status <> g_old_rec.action_status then
3332 chk_action_status
3333 (p_payroll_action_id => p_rec.payroll_action_id
3334 ,p_old_action_status => g_old_rec.action_status
3335 ,p_new_action_status => p_rec.action_status
3336 );
3337 end if;
3338 --
3339 -- If run type has changed, validate the new value is correct.
3340 --
3341 if nvl(p_rec.run_type_id, hr_api.g_number) <>
3342 nvl(g_old_rec.run_type_id, hr_api.g_number) then
3343 --
3344 -- Find out the legislation code for the current business group.
3345 v_legislation_code := get_legislation_code(p_rec.business_group_id);
3346 --
3347 -- Call business rule validation.
3348 --
3349 -- Validate the run_type - if not null, check it exists on
3350 -- pay_run_types_f.
3351 --
3352 chk_run_type
3353 (p_run_type_id => p_rec.run_type_id
3354 ,p_effective_date => p_rec.effective_date
3355 ,p_business_group_id => p_rec.business_group_id
3356 ,p_legislation_code => v_legislation_code
3357 );
3358 end if;
3359 --
3360 -- The user is allowed to update the pay advice date and
3361 -- the pay advice message. No validation is required.
3362 --
3363 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3364 End update_validate;
3365 --
3366 -- ----------------------------------------------------------------------------
3367 -- |---------------------------< delete_validate >----------------------------|
3368 -- ----------------------------------------------------------------------------
3369 -- {Start Of Comments}
3370 --
3371 -- Description:
3372 -- This procedure controls the execution of all delete business rules
3373 -- validation.
3374 --
3375 -- Pre Conditions:
3376 -- This private procedure is called from del procedure.
3377 --
3378 -- In Arguments:
3379 -- A Pl/Sql record structure.
3380 --
3381 -- Post Success:
3382 -- Processing continues.
3383 --
3384 -- Post Failure:
3385 -- If a business rules fails the error will not be handled by this procedure
3386 -- unless explicitly coded.
3387 --
3388 -- Access Status:
3389 -- Internal Development Use Only.
3390 --
3391 -- {End Of Comments}
3392 --
3393 Procedure delete_validate(p_rec in g_rec_type) is
3394 --
3395 v_proc varchar2(72) := g_package||'delete_validate';
3396 --
3397 Begin
3398 hr_utility.set_location('Entering:'|| v_proc, 5);
3399 --
3400 -- Check that the payroll_action has a null current_task.
3401 --
3402 chk_cur_task
3403 (p_current_task => p_rec.current_task
3404 );
3405 --
3406 -- The following call checks the delete is valid. If this QuickPay Run
3407 -- can be removed any child rows such as pay_run_results and
3408 -- pay_run_result_values will be deleted.
3409 --
3410 py_rollback_pkg.rollback_payroll_action(
3411 p_payroll_action_id => p_rec.payroll_action_id,
3412 p_rollback_mode => 'ROLLBACK',
3413 p_leave_base_table_row => TRUE);
3414 --
3415 hr_utility.set_location('Leaving:'|| v_proc, 10);
3416 End delete_validate;
3417 --
3418 -- ----------------------------------------------------------------------------
3419 -- |---------------------------------< ins >----------------------------------|
3420 -- ----------------------------------------------------------------------------
3421 --
3422 Procedure ins
3423 (p_rec in out nocopy g_rec_type
3424 ,p_assignment_id in number
3425 ,p_assignment_action_id out nocopy number
3426 ,p_a_object_version_number out nocopy number
3427 ,p_validate in boolean default false
3428 ) is
3429 --
3430 v_proc varchar2(72) := g_package||'ins';
3431 --
3432 -- These variables are set by insert_validate and past to insert_dml
3433 --
3434 v_payroll_id pay_payroll_actions.payroll_id%TYPE;
3435 v_time_period_id pay_payroll_actions.time_period_id%TYPE;
3436 --
3437 -- These variables are set by pre_insert and past to insert_dml
3438 --
3439 v_action_type pay_payroll_actions.action_type%TYPE;
3440 v_action_population_status pay_payroll_actions.action_population_status%TYPE;
3441 --
3442 -- These variables are set by post_insert and returned from this procedure
3443 --
3444 v_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3445 v_a_object_version_number pay_assignment_actions.object_version_number%TYPE;
3446 --
3447 Begin
3448 hr_utility.set_location('Entering:'|| v_proc, 5);
3449 --
3450 -- Determine if the business process is to be validated.
3451 --
3452 If p_validate then
3453 --
3454 -- Issue the savepoint.
3455 --
3456 SAVEPOINT ins_pay_payact;
3457 End If;
3458 --
3459 -- Call the supporting insert validate operations
3460 --
3461 insert_validate
3462 (p_rec => p_rec
3463 ,p_assignment_id => p_assignment_id
3464 ,p_payroll_id => v_payroll_id
3465 ,p_time_period_id => v_time_period_id
3466 );
3467 --
3468 -- Call the supporting pre-insert operation
3469 --
3470 pre_insert
3471 (p_rec => p_rec
3472 ,p_action_type => v_action_type
3473 ,p_action_population_status => v_action_population_status
3474 );
3475 --
3476 -- Insert the row
3477 --
3478 insert_dml
3479 (p_rec => p_rec
3480 ,p_action_type => v_action_type
3481 ,p_payroll_id => v_payroll_id
3482 ,p_time_period_id => v_time_period_id
3483 ,p_action_population_status => v_action_population_status
3484 );
3485 --
3486 -- Call the supporting post-insert operation
3487 --
3488 post_insert
3489 (p_rec => p_rec
3490 ,p_assignment_id => p_assignment_id
3491 ,p_validate => p_validate
3492 ,p_assignment_action_id => v_assignment_action_id
3493 ,p_a_object_version_number => v_a_object_version_number
3494 );
3495 --
3496 -- Set output parameters
3497 --
3498 p_assignment_action_id := v_assignment_action_id;
3499 p_a_object_version_number := v_a_object_version_number;
3500 --
3501 -- If we are validating then raise the Validate_Enabled exception
3502 --
3503 if p_validate then
3504 raise hr_api.validate_enabled;
3505 end if;
3506 --
3507 hr_utility.set_location(' Leaving:'||v_proc, 10);
3508 exception
3509 when hr_api.validate_enabled then
3510 --
3511 -- As the Validate_Enabled exception has been raised
3512 -- we must rollback to the savepoint
3513 --
3514 ROLLBACK TO ins_pay_payact;
3515 end ins;
3516 --
3517 -- ----------------------------------------------------------------------------
3518 -- |---------------------------------< ins >----------------------------------|
3519 -- ----------------------------------------------------------------------------
3520 --
3521 Procedure ins
3522 (p_business_group_id in number
3523 ,p_assignment_id in number
3524 ,p_consolidation_set_id in number
3525 ,p_effective_date in date
3526 ,p_legislative_parameters in varchar2 default null
3527 ,p_run_type_id in number
3528 ,p_date_earned in date
3529 ,p_pay_advice_date in date default null
3530 ,p_pay_advice_message in varchar2 default null
3531 ,p_comments in varchar2 default null
3532 ,p_payroll_action_id out nocopy number
3533 ,p_p_object_version_number out nocopy number
3534 ,p_assignment_action_id out nocopy number
3535 ,p_a_object_version_number out nocopy number
3536 ,p_validate in boolean default false
3537 ) is
3538 --
3539 v_rec g_rec_type;
3540 v_proc varchar2(72) := g_package||'ins';
3541 --
3542 Begin
3543 hr_utility.set_location('Entering:'||v_proc, 5);
3544 --
3545 -- Call conversion function to turn arguments into the
3546 -- p_rec structure.
3547 --
3548 v_rec :=
3549 convert_args
3550 (p_payroll_action_id => null
3551 ,p_business_group_id => p_business_group_id
3552 ,p_consolidation_set_id => p_consolidation_set_id
3553 ,p_action_status => null
3554 ,p_effective_date => p_effective_date
3555 ,p_comments => p_comments
3556 ,p_current_task => null
3557 ,p_legislative_parameters => p_legislative_parameters
3558 ,p_run_type_id => p_run_type_id
3559 ,p_date_earned => p_date_earned
3560 ,p_pay_advice_date => p_pay_advice_date
3561 ,p_pay_advice_message => p_pay_advice_message
3562 ,p_object_version_number => null
3563 );
3564 hr_utility.set_location(v_proc, 6);
3565 --
3566 -- Having converted the arguments into the pay_payact_rec
3567 -- plsql record structure we call the corresponding record business process.
3568 --
3569 ins
3570 (p_rec => v_rec
3571 ,p_assignment_id => p_assignment_id
3572 ,p_assignment_action_id => p_assignment_action_id
3573 ,p_a_object_version_number => p_a_object_version_number
3574 ,p_validate => p_validate
3575 );
3576 hr_utility.set_location(v_proc, 7);
3577 --
3578 -- As the primary key argument(s)
3579 -- are specified as an OUT's we must set these values.
3580 -- (p_a_object_version_number and p_assignment_action_id have
3581 -- been set by the 'ins' procedure.)
3582 --
3583 p_payroll_action_id := v_rec.payroll_action_id;
3584 p_p_object_version_number := v_rec.object_version_number;
3585 --
3586 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3587 End ins;
3588 --
3589 -- ----------------------------------------------------------------------------
3590 -- |---------------------------------< upd >----------------------------------|
3591 -- ----------------------------------------------------------------------------
3592 --
3593 procedure upd
3594 (p_rec in out nocopy g_rec_type
3595 ,p_assignment_action_id in number
3596 ,p_a_object_version_number in number
3597 ,p_validate in boolean default false
3598 ) is
3599 --
3600 v_proc varchar2(72) := g_package||'upd';
3601 --
3602 Begin
3603 hr_utility.set_location('Entering:'|| v_proc, 5);
3604 --
3605 -- Determine if the business process is to be validated.
3606 --
3607 If p_validate then
3608 --
3609 -- Issue the savepoint.
3610 --
3611 SAVEPOINT upd_pay_payact;
3612 End If;
3613 --
3614 -- We must lock the row which we need to update.
3615 --
3616 lck
3617 (p_payroll_action_id => p_rec.payroll_action_id
3618 ,p_p_object_version_number => p_rec.object_version_number
3619 ,p_a_object_version_number => p_a_object_version_number
3620 );
3621 hr_utility.set_location(v_proc, 6);
3622 --
3623 -- 1. During an update system defaults are used to determine if
3624 -- arguments have been defaulted or not. We must therefore
3625 -- derive the full record structure values to be updated.
3626 --
3627 -- 2. Call the supporting update validate operations.
3628 --
3629 update_validate
3630 (p_rec => convert_defs(p_rec)
3631 );
3632 --
3633 -- Call the supporting pre-update operation
3634 --
3635 pre_update(p_rec);
3636 --
3637 -- Update the row.
3638 --
3639 update_dml(p_rec);
3640 --
3641 -- Call the supporting post-update operation
3642 --
3643 post_update(p_rec);
3644 --
3645 -- If we are validating then raise the Validate_Enabled exception
3646 --
3647 if p_validate then
3648 raise hr_api.validate_enabled;
3649 end if;
3650 --
3651 hr_utility.set_location(' Leaving:'||v_proc, 10);
3652 exception
3653 when hr_api.validate_enabled Then
3654 --
3655 -- As the Validate_Enabled exception has been raised
3656 -- we must rollback to the savepoint
3657 --
3658 ROLLBACK TO upd_pay_payact;
3659 end upd;
3660 --
3661 -- ----------------------------------------------------------------------------
3662 -- |---------------------------------< upd >----------------------------------|
3663 -- ----------------------------------------------------------------------------
3664 --
3665 procedure upd
3666 (p_payroll_action_id in number
3667 ,p_consolidation_set_id in number default hr_api.g_number
3668 ,p_legislative_parameters in varchar2 default hr_api.g_varchar2
3669 ,p_run_type_id in number default hr_api.g_number
3670 ,p_pay_advice_date in date default hr_api.g_date
3671 ,p_pay_advice_message in varchar2 default hr_api.g_varchar2
3672 ,p_action_status in varchar2 default hr_api.g_varchar2
3673 ,p_comments in varchar2 default hr_api.g_varchar2
3674 ,p_assignment_action_id in number
3675 ,p_p_object_version_number in out nocopy number
3676 ,p_a_object_version_number in number
3677 ,p_validate in boolean default false
3678 ) is
3679 --
3680 v_rec g_rec_type;
3681 v_proc varchar2(72) := g_package||'upd';
3682 --
3683 Begin
3684 hr_utility.set_location('Entering:'|| v_proc, 5);
3685 --
3686 -- Call conversion function to turn arguments into the
3687 -- l_rec structure.
3688 --
3689 v_rec :=
3690 convert_args
3691 (p_payroll_action_id => p_payroll_action_id
3692 ,p_business_group_id => hr_api.g_number
3693 ,p_consolidation_set_id => p_consolidation_set_id
3694 ,p_action_status => p_action_status
3695 ,p_effective_date => hr_api.g_date
3696 ,p_comments => p_comments
3697 ,p_current_task => hr_api.g_varchar2
3698 ,p_legislative_parameters => p_legislative_parameters
3699 ,p_run_type_id => p_run_type_id
3700 ,p_date_earned => hr_api.g_date
3701 ,p_pay_advice_date => p_pay_advice_date
3702 ,p_pay_advice_message => p_pay_advice_message
3703 ,p_object_version_number => p_p_object_version_number
3704 );
3705 hr_utility.set_location(v_proc, 6);
3706 --
3707 -- Having converted the arguments into the
3708 -- plsql record structure we call the corresponding record
3709 -- business process.
3710 --
3711 upd
3712 (p_rec => v_rec
3713 ,p_assignment_action_id => p_assignment_action_id
3714 ,p_a_object_version_number => p_a_object_version_number
3715 ,p_validate => p_validate
3716 );
3717 p_p_object_version_number := v_rec.object_version_number;
3718 --
3719 hr_utility.set_location(' Leaving:'|| v_proc, 10);
3720 End upd;
3721 --
3722 -- ----------------------------------------------------------------------------
3723 -- |---------------------------------< del >----------------------------------|
3724 -- ----------------------------------------------------------------------------
3725 --
3726 procedure del
3727 (p_rec in g_rec_type
3728 ,p_a_object_version_number in number
3729 ,p_validate in boolean default false
3730 ) is
3731 --
3732 v_proc varchar2(72) := g_package||'del';
3733 --
3734 begin
3735 hr_utility.set_location('Entering:'|| v_proc, 5);
3736 --
3737 -- Determine if the business process is to be validated.
3738 --
3739 if p_validate then
3740 --
3741 -- Issue the savepoint.
3742 --
3743 SAVEPOINT del_pay_payact;
3744 end if;
3745 --
3746 -- We must lock the row which we need to delete.
3747 --
3748 lck
3749 (p_payroll_action_id => p_rec.payroll_action_id
3750 ,p_p_object_version_number => p_rec.object_version_number
3751 ,p_a_object_version_number => p_a_object_version_number
3752 );
3753 --
3754 -- Call the supporting delete validate operation
3755 --
3756 delete_validate(p_rec);
3757 --
3758 -- Call the supporting pre-delete operation
3759 --
3760 pre_delete(p_rec);
3761 --
3762 -- Delete the row.
3763 --
3764 delete_dml(p_rec);
3765 --
3766 -- Call the supporting post-delete operation
3767 --
3768 post_delete(p_rec);
3769 --
3770 -- If we are validating then raise the Validate_Enabled exception
3771 --
3772 if p_validate then
3773 raise hr_api.validate_enabled;
3774 end if;
3775 --
3776 hr_utility.set_location(' Leaving:'||v_proc, 10);
3777 exception
3778 when hr_api.validate_enabled Then
3779 --
3780 -- As the Validate_Enabled exception has been raised
3781 -- we must rollback to the savepoint
3782 --
3783 ROLLBACK TO del_pay_payact;
3784 end del;
3785 --
3786 -- ----------------------------------------------------------------------------
3787 -- |---------------------------------< del >----------------------------------|
3788 -- ----------------------------------------------------------------------------
3789 --
3790 procedure del
3791 (p_payroll_action_id in number
3792 ,p_p_object_version_number in number
3793 ,p_a_object_version_number in number
3794 ,p_validate in boolean default false
3795 ) is
3796 --
3797 v_rec g_rec_type;
3798 v_proc varchar2(72) := g_package||'del';
3799 --
3800 Begin
3801 hr_utility.set_location('Entering:'|| v_proc, 5);
3802 --
3803 -- As the delete procedure accepts a plsql record structure we do need to
3804 -- convert the arguments into the record structure.
3805 -- We don't need to call the supplied conversion argument routine as we
3806 -- only need a few attributes.
3807 --
3808 v_rec.payroll_action_id := p_payroll_action_id;
3809 v_rec.object_version_number := p_p_object_version_number;
3810 --
3811 -- Having converted the arguments into the pay_payact_rec
3812 -- plsql record structure we must call the corresponding entity
3813 -- business process
3814 --
3815 del
3816 (p_rec => v_rec
3817 ,p_a_object_version_number => p_a_object_version_number
3818 ,p_validate => p_validate
3819 );
3820 --
3821 hr_utility.set_location(' Leaving:'||v_proc, 10);
3822 End del;
3823 --
3824 -- ----------------------------------------------------------------------------
3825 -- |-----------------------------< default_values >---------------------------|
3826 -- ----------------------------------------------------------------------------
3827 --
3828 procedure default_values
3829 (p_assignment_id in pay_assignment_actions.assignment_id%TYPE
3830 ,p_df_effective_date out nocopy pay_payroll_actions.
3831 effective_date%TYPE
3832 ,p_df_date_earned out nocopy pay_payroll_actions.date_earned%TYPE
3833 ,p_period_name out nocopy per_time_periods.period_name%TYPE
3834 ,p_consolidation_set_id out nocopy pay_consolidation_sets.
3835 consolidation_set_id%TYPE
3836 ,p_consolidation_set_name out nocopy pay_consolidation_sets.
3837 consolidation_set_name%TYPE
3838 ,p_unprocessed_status out nocopy varchar2
3839 ,p_mark_for_retry_status out nocopy varchar2
3840 ,p_complete_status out nocopy varchar2
3841 ,p_in_error_status out nocopy varchar2
3842 ,p_start_run_prompt out nocopy varchar2
3843 ,p_start_pre_prompt out nocopy varchar2
3844 ,p_retry_run_prompt out nocopy varchar2
3845 ,p_rerun_pre_prompt out nocopy varchar2
3846 ,p_start_arc_prompt out nocopy varchar2
3847 ,p_retry_arc_prompt out nocopy varchar2
3848 ,p_qp_run_user_name out nocopy varchar2
3849 ) is
3850 --
3851 -- Cursor Definitions
3852 --
3853 cursor csr_eff_date is
3854 select effective_date
3855 from fnd_sessions ses
3856 where ses.session_id = userenv('sessionid');
3857 --
3858 cursor csr_payroll (v_cur_date date) is
3859 select /*+ ORDERED INDEX(asg PER_ASSIGNMENTS_F_PK) */
3860 asg.payroll_id
3861 , pro.consolidation_set_id
3862 , con.consolidation_set_name
3863 , bus.legislation_code
3864 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'U')
3865 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'M')
3866 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'C')
3867 , hr_general.decode_lookup('ASSIGNMENT_ACTION_STATUS', 'E')
3868 , hr_general.decode_lookup('ACTION_TYPE', 'Q')
3869 from per_assignments_f asg
3870 , pay_payrolls_f pro
3871 , pay_consolidation_sets con
3872 , per_business_groups_perf bus
3873 where /* Assignment's Payroll details */
3874 asg.assignment_id = p_assignment_id
3875 and v_cur_date between asg.effective_start_date
3876 and asg.effective_end_date
3877 and pro.payroll_id = asg.payroll_id
3878 and v_cur_date between pro.effective_start_date
3879 and pro.effective_end_date
3880 /* Consolidation Set details */
3881 and con.consolidation_set_id = pro.consolidation_set_id
3882 /* Business group's legislation code details */
3883 and bus.business_group_id = asg.business_group_id;
3884 --
3885 cursor csr_per_dat(v_pay_id number
3886 ,v_cur_date date
3887 ) is
3888 select tim.regular_payment_date
3889 , tim.period_name
3890 from per_time_periods tim
3891 where tim.payroll_id = v_pay_id
3892 and v_cur_date between tim.start_date
3893 and tim.end_date;
3894 --
3895 --
3896 -- Local variables
3897 --
3898 -- The assignment's business group legislation_code:
3899 v_legislation_code per_business_groups.legislation_code%TYPE;
3900 --
3901 -- The assignment's payroll as of the session effective or trunc(sysdate).
3902 v_payroll_id per_assignments_f.payroll_id%TYPE;
3903 --
3904 -- Period name which exists as of v_eff_date.
3905 v_period_name per_time_periods.period_name%TYPE;
3906 --
3907 -- Period regular payment date.
3908 v_period_reg_date per_time_periods.regular_payment_date%TYPE;
3909 --
3910 -- The Form or Database session effective date.
3911 v_eff_date date;
3912 --
3913 -- Working value which is used to set the out argument p_df_effective_date.
3914 v_df_effective_date pay_payroll_actions.effective_date%TYPE;
3915 --
3916 -- Working value which is used to set the out argument p_df_date_earned.
3917 v_df_date_earned pay_payroll_actions.date_earned%TYPE;
3918 --
3919 -- Working value which is used to set the out argument p_period_name;
3920 v_df_period_name per_time_periods.period_name%TYPE;
3921 --
3922 -- Shows if the assignment is on the same payroll as of two dates.
3923 v_same_payroll boolean;
3924 --
3925 v_proc varchar2(72) := g_package||'default_values';
3926 --
3927 begin
3928 hr_utility.set_location('Entering:'|| v_proc, 5);
3929 --
3930 -- Work out which date to drive off. Try to use the Form's session effective
3931 -- date. If that has not been defined for this database session then
3932 -- use trunc(sysdate).
3933 --
3934 open csr_eff_date;
3935 fetch csr_eff_date into v_eff_date;
3936 if csr_eff_date%notfound then
3937 v_eff_date := trunc(sysdate);
3938 end if;
3939 close csr_eff_date;
3940 hr_utility.set_location(v_proc, 6);
3941 --
3942 -- Obtain the assignment's payroll, consolidation set details,
3943 -- action status meanings, QuickPay Run user description and
3944 -- the business group's legislation details.
3945 --
3946 open csr_payroll (v_eff_date);
3947 fetch csr_payroll into v_payroll_id
3948 , p_consolidation_set_id
3949 , p_consolidation_set_name
3950 , v_legislation_code
3951 , p_unprocessed_status
3952 , p_mark_for_retry_status
3953 , p_complete_status
3954 , p_in_error_status
3955 , p_qp_run_user_name;
3956 if csr_payroll%found then
3957 close csr_payroll;
3958 else
3959 close csr_payroll;
3960 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3961 hr_utility.set_message_token('PROCEDURE', v_proc);
3962 hr_utility.set_message_token('STEP', '7');
3963 hr_utility.raise_error;
3964 end if;
3965 hr_utility.set_location(v_proc, 8);
3966 --
3967 -- Obtain the button prompts from hr_lookups
3968 --
3969 p_start_run_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SR');
3970 p_start_pre_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SP');
3971 p_retry_run_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RR');
3972 p_rerun_pre_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RP');
3973 p_start_arc_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','SA');
3974 p_retry_arc_prompt := hr_general.decode_lookup ('PAYWSRQP_BUTTONS','RA');
3975 if p_start_run_prompt is null or p_retry_run_prompt is null or
3976 p_start_pre_prompt is null or p_rerun_pre_prompt is null or
3977 p_start_arc_prompt is null or p_retry_arc_prompt is null then
3978 -- All six button labels could not be found in hr_lookups.
3979 -- (Where lookup_type = 'PAYWSRQP_BUTTONS'.)
3980 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3981 hr_utility.set_message_token('PROCEDURE', v_proc);
3982 hr_utility.set_message_token('STEP', '9');
3983 hr_utility.raise_error;
3984 end if;
3985 hr_utility.set_location(v_proc, 10);
3986 --
3987 -- Set the out arguments p_df_effective_date and p_df_date_earned
3988 -- depending on a payroll time period existing and the legislation.
3989 --
3990 -- Attempt to find a payroll time period for the session date.
3991 --
3992 open csr_per_dat (v_payroll_id, v_eff_date);
3993 fetch csr_per_dat into v_period_reg_date
3994 , v_period_name;
3995 if csr_per_dat%notfound then
3996 --
3997 -- No time period exists as of the session date. So do not
3998 -- default the date or period name out arguments.
3999 --
4000 close csr_per_dat;
4001 v_df_effective_date := null;
4002 v_df_date_earned := null;
4003 v_df_period_name := null;
4004 hr_utility.set_location(v_proc, 11);
4005 else
4006 --
4007 -- A time period does exist as of the session date. Set the
4008 -- period name out argument and set the date fields depending
4009 -- on the current legislation.
4010 --
4011 close csr_per_dat;
4012 hr_utility.set_location(v_proc, 12);
4013 --
4014 if v_legislation_code = 'GB' then
4015 --
4016 -- Only for GB legislation.
4017 --
4018 v_df_effective_date := v_period_reg_date;
4019 v_df_period_name := v_period_name;
4020 --
4021 -- Check the assignment is on the same payroll as of the specified
4022 -- effective_date and derived effective_date.
4023 --
4024 -- chk_eff_earned_payroll
4025 -- (p_effective_date => v_df_effective_date
4026 -- ,p_date_earned => v_eff_date
4027 -- ,p_assignment_id => p_assignment_id
4028 -- ,p_raise_error => false
4029 -- ,p_same_payroll => v_same_payroll
4030 -- );
4031 --if v_same_payroll then
4032 --
4033 -- The assignment is on the same payroll so attempt to derive
4034 -- the date earned value.
4035 --
4036 --v_df_date_earned := get_date_earned_for_gb
4037 -- (p_assignment_id => p_assignment_id
4038 -- ,p_effective_date => v_df_effective_date
4039 -- );
4040 --hr_utility.set_location(v_proc, 13);
4041 --
4042 -- If get_date_earned_for_gb returns a null value then a suitable
4043 -- default value could not be found or the assignment.
4044 --
4045 --if v_df_date_earned is null then
4046 -- v_df_effective_date := null;
4047 -- v_df_period_name := null;
4048 -- hr_utility.set_location(v_proc, 14);
4049 --else
4050 --
4051 -- Check the assignment is on the same payroll as of the specified
4052 -- default effective_date and default date_earned.
4053 --
4054 --chk_eff_earned_payroll
4055 -- (p_effective_date => v_df_effective_date
4056 -- ,p_date_earned => v_eff_date
4057 -- ,p_assignment_id => p_assignment_id
4058 -- ,p_raise_error => false
4059 -- ,p_same_payroll => v_same_payroll
4060 -- );
4061 --if not v_same_payroll then
4062 -- Payrolls as of v_df_effective_date and v_df_date_earned are
4063 -- different.
4064 --v_df_effective_date := null;
4065 --v_df_date_earned := null;
4066 --v_df_period_name := null;
4067 --hr_utility.set_location(v_proc, 16);
4068 --end if;
4069 --hr_utility.set_location(v_proc, 17);
4070 --end if;
4071 --hr_utility.set_location(v_proc, 18);
4072 --else -- payrolls as of v_eff_date and v_df_effective_date are different
4073 --v_df_effective_date := null;
4074 --v_df_date_earned := null;
4075 --v_df_period_name := null;
4076 --hr_utility.set_location(v_proc, 19);
4077 --end if;
4078 else
4079 --
4080 -- For all other legislations. i.e. non-GB.
4081 --
4082 v_df_effective_date := v_eff_date;
4083 v_df_date_earned := v_eff_date;
4084 v_df_period_name := v_period_name;
4085 hr_utility.set_location(v_proc, 20);
4086 end if;
4087 end if;
4088 --
4089 p_df_effective_date := v_df_effective_date;
4090 p_df_date_earned := v_df_date_earned;
4091 p_period_name := v_df_period_name;
4092 --
4093 hr_utility.set_location(' Leaving:'|| v_proc, 21);
4094 end default_values;
4095 --
4096 -- ----------------------------------------------------------------------------
4097 -- |-----------------------------< get_latest_status >------------------------|
4098 -- ----------------------------------------------------------------------------
4099 -- {Start Of Comments}
4100 --
4101 -- Description:
4102 -- Returns the latest statuses and display_run_number for a given QuickPay
4103 -- Run or QuickPay Pre-payment. Used to find out the updated Payroll and
4104 -- Assignment Process statuses after one of the C processes has been ran.
4105 --
4106 -- Pre Conditions:
4107 -- None.
4108 --
4109 -- In Arguments:
4110 -- p_payroll_action_id is the id of a QuickPay Run or QuickPay Pre-payment
4111 -- Payroll Process. This is a mandatory argument.
4112 --
4113 -- Post Success:
4114 -- p_p_action_status will be set to the Payroll Process action status.
4115 -- p_display_run_number will be set to Payroll Process display_run_number
4116 -- value. If the Payroll Process is a QuickPay Pre-payment
4117 -- p_display_run_number will always be null.
4118 -- p_a_action_status will be set to the Assignment Process action status.
4119 --
4120 -- Post Failure:
4121 -- The application error HR_7220_INVALID_PRIMARY_KEY will be raised if a
4122 -- Payroll Process does not exist with an id of p_payroll_action_id.
4123 -- p_p_action_status, p_display_run_number and p_action_status will have
4124 -- undefined values because the end of the procedure will not be reached.
4125 --
4126 -- {End Of Comments}
4127 --
4128 procedure get_latest_status
4129 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4130 ,p_p_action_status out nocopy pay_payroll_actions.action_status%TYPE
4131 ,p_display_run_number out nocopy pay_payroll_actions.
4132 display_run_number%TYPE
4133 ,p_a_action_status out nocopy pay_assignment_actions.action_status%TYPE
4134 ) is
4135 v_proc varchar2(72) := g_package||'get_latest_status';
4136 v_argument varchar2(30);
4137 --
4138 cursor cur_stat is
4139 select pya.action_status
4140 , pya.display_run_number
4141 , aga.action_status
4142 from pay_payroll_actions pya
4143 , pay_assignment_actions aga
4144 where pya.payroll_action_id = p_payroll_action_id
4145 and aga.payroll_action_id = pya.payroll_action_id;
4146 begin
4147 hr_utility.set_location('Entering:'|| v_proc, 5);
4148 --
4149 -- Check mandatory parameters have been set
4150 --
4151 hr_api.mandatory_arg_error
4152 (p_api_name => v_proc
4153 ,p_argument => 'payroll_action_id'
4154 ,p_argument_value => p_payroll_action_id
4155 );
4156 --
4157 -- Select the action_status and display_run_number
4158 -- for the payroll action.
4159 --
4160 open cur_stat;
4161 fetch cur_stat into p_p_action_status
4162 , p_display_run_number
4163 , p_a_action_status;
4164 if cur_stat%notfound then
4165 close cur_stat;
4166 -- Error: This primary key does not exist in the database.
4167 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
4168 hr_utility.raise_error;
4169 end if;
4170 close cur_stat;
4171 --
4172 hr_utility.set_location(' Leaving:'|| v_proc, 10);
4173 end get_latest_status;
4174 --
4175 -- ----------------------------------------------------------------------------
4176 -- |--------------------------< start_quickpay_process >----------------------|
4177 -- ----------------------------------------------------------------------------
4178 --
4179 procedure start_quickpay_process
4180 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4181 ,p_p_object_version_number in pay_payroll_actions.object_version_number%TYPE
4182 ,p_a_object_version_number in pay_assignment_actions.
4183 object_version_number%TYPE
4184 ,p_status in out nocopy varchar2
4185 ) is
4186 v_proc varchar2(72) := g_package||'start_quickpay_process';
4187 v_request_id pay_payroll_actions.request_id%TYPE;
4188 v_action_type pay_payroll_actions.action_type%TYPE;
4189 v_a_action_status pay_assignment_actions.action_status%TYPE;
4190 v_leg_code per_business_groups.legislation_code%TYPE;
4191 v_process_name varchar2(30);
4192 v_first_arg varchar2(30);
4193 v_asactid varchar2(30);
4194 v_asgid varchar2(30);
4195 v_grossup pay_element_entries_f.element_entry_id%TYPE;
4196 v_displ_msg varchar2(30);
4197 --
4198 cursor get_leg_rule is
4199 select rule_mode
4200 from pay_legislative_field_info
4201 where legislation_code = v_leg_code
4202 and target_location = 'PAYWSRQP'
4203 and validation_type = 'DISPLAY'
4204 and rule_type = 'DISP_NTG_MSG';
4205
4206 --
4207 -- Enhancement 3368211
4208 -- get_grossup_entry modified to support the new QuickPay Exclusions
4209 -- model.
4210 --
4211 cursor get_grossup_entry (p_use_qpay_excl_model varchar2) is
4212 SELECT EE.element_entry_id
4213 FROM pay_element_types_f ET,
4214 pay_element_links_f EL,
4215 pay_element_entries_f EE,
4216 pay_assignment_actions ACT,
4217 pay_payroll_actions PACT
4218 WHERE ACT.assignment_action_id = v_asactid
4219 AND PACT.payroll_action_id = p_payroll_action_id
4220 AND EE.assignment_id = ACT.assignment_id
4221 AND ((p_use_qpay_excl_model = 'N'
4222 AND EXISTS (
4223 SELECT null
4224 FROM pay_quickpay_inclusions INC
4225 WHERE INC.assignment_action_id = ACT.assignment_action_id
4226 AND INC.element_entry_id = EE.element_entry_id)
4227 OR EXISTS (
4228 SELECT null
4229 FROM pay_quickpay_inclusions INC
4230 WHERE INC.assignment_action_id = ACT.assignment_action_id
4231 AND INC.element_entry_id = EE.target_entry_id)
4232 )
4233 OR
4234 (p_use_qpay_excl_model = 'Y'
4235 AND NOT (
4236 EXISTS (
4237 SELECT null
4238 FROM pay_quickpay_exclusions EXC
4239 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4240 AND EXC.element_entry_id = EE.element_entry_id)
4241 OR EXISTS (
4242 SELECT null
4243 FROM pay_quickpay_exclusions EXC
4244 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4245 AND EXC.element_entry_id = EE.target_entry_id))
4246 )
4247 )
4248 AND EE.entry_type <> 'B'
4249 AND PACT.date_earned BETWEEN EE.effective_start_date
4250 AND EE.effective_end_date
4251 AND EE.element_link_id = EL.element_link_id
4252 AND PACT.date_earned BETWEEN EL.effective_start_date
4253 AND EL.effective_end_date
4254 AND nvl(EE.date_earned, to_date('01/01/0001', 'DD/MM/YYYY')) <=
4255 PACT.date_earned
4256 AND EL.element_type_id = ET.element_type_id
4257 AND ET.grossup_flag = 'Y'
4258 AND PACT.date_earned BETWEEN ET.effective_start_date
4259 AND ET.effective_end_date
4260 AND ET.process_in_run_flag <> 'N'
4261 AND (ET.processing_type = 'N' OR EE.entry_type = 'D')
4262 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'P')
4263 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'R')
4264 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'RR')
4265 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'EE')
4266 UNION ALL
4267 SELECT EE.element_entry_id
4268 FROM pay_element_types_f ET,
4269 pay_element_links_f EL,
4270 pay_element_entries_f EE,
4271 pay_assignment_actions ACT,
4272 pay_payroll_actions PACT,
4273 pay_element_entry_values_f EEV
4274 WHERE PACT.payroll_action_id = p_payroll_action_id
4275 and PACT.payroll_action_id = ACT.payroll_action_id
4276 and ACT.action_status not in ('C', 'S')
4277 AND EE.assignment_id = ACT.assignment_id
4278 AND ((p_use_qpay_excl_model = 'N'
4279 AND EXISTS (
4280 SELECT null
4281 FROM pay_quickpay_inclusions INC
4282 WHERE INC.assignment_action_id = ACT.assignment_action_id
4283 AND INC.element_entry_id = EE.element_entry_id)
4284 OR EXISTS (
4285 SELECT null
4286 FROM pay_quickpay_inclusions INC
4287 WHERE INC.assignment_action_id = ACT.assignment_action_id
4288 AND INC.element_entry_id = EE.target_entry_id)
4289 )
4290 OR
4291 (p_use_qpay_excl_model = 'Y'
4292 AND NOT (
4293 EXISTS (
4294 SELECT null
4295 FROM pay_quickpay_exclusions EXC
4296 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4297 AND EXC.element_entry_id = EE.element_entry_id)
4298 OR EXISTS (
4299 SELECT null
4300 FROM pay_quickpay_exclusions EXC
4301 WHERE EXC.assignment_action_id = ACT.assignment_action_id
4302 AND EXC.element_entry_id = EE.target_entry_id))
4303 )
4304 )
4305 AND EE.entry_type NOT IN ('B', 'D')
4306 AND EE.effective_start_date <= PACT.date_earned
4307 and EE.effective_end_date >=
4308 decode(ET.proration_group_id,
4309 null, PACT.date_earned,
4310 pay_interpreter_pkg.prorate_start_date (v_asactid, ET.proration_group_id))
4311 AND EE.element_link_id = EL.element_link_id
4312 AND PACT.date_earned BETWEEN EL.effective_start_date
4313 AND EL.effective_end_date
4314 AND EL.element_type_id = ET.element_type_id
4315 AND ET.grossup_flag = 'Y'
4316 AND PACT.date_earned BETWEEN ET.effective_start_date
4317 AND ET.effective_end_date
4318 AND ET.process_in_run_flag <> 'N'
4319 AND EEV.element_entry_id (+) = EE.element_entry_id
4320 AND EE.effective_start_date = nvl(EEV.effective_start_date,
4321 EE.effective_start_date)
4322 AND EE.effective_end_date = nvl(EEV.effective_end_date,
4323 EE.effective_end_date)
4324 AND ET.processing_type = 'R'
4325 AND EXISTS ( select ''
4326 from pay_payroll_actions ppa,
4327 per_time_periods ptp,
4328 pay_element_entries_f pee
4329 where pee.element_entry_id = EE.element_entry_id
4330 and ppa.payroll_action_id = ACT.payroll_action_id
4331 and ppa.payroll_id = ptp.payroll_id
4332 and PACT.date_earned between ptp.start_date
4333 and ptp.end_date
4334 and pee.effective_start_date <= ptp.end_date
4335 and pee.effective_end_date >= ptp.start_date
4336 )
4337 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'P')
4338 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'R')
4339 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'RR')
4340 AND NOT (ACT.action_status = 'B' AND EE.creator_type = 'EE');
4341 --
4342 begin
4343 hr_utility.set_location('Entering:'|| v_proc, 5);
4344 --
4345 -- Check mandatory parameters have been set
4346 --
4347 hr_api.mandatory_arg_error
4348 (p_api_name => v_proc
4349 ,p_argument => 'payroll_action_id'
4350 ,p_argument_value => p_payroll_action_id
4351 );
4352 --
4353 -- Lock the QuickPay Run or QuickPay Pre-payment action rows
4354 --
4355 lck_general
4356 (p_payroll_action_id => p_payroll_action_id
4357 ,p_p_object_version_number => p_p_object_version_number
4358 ,p_a_object_version_number => p_a_object_version_number
4359 ,p_only_action_type => null
4360 ,p_actual_action_type => v_action_type
4361 ,p_a_action_status => v_a_action_status
4362 );
4363 hr_utility.set_location(v_proc, 6);
4364 --
4365 select pbg.legislation_code
4366 into v_leg_code
4367 from per_business_groups_perf pbg,
4368 pay_payroll_actions ppa
4369 where ppa.payroll_action_id = p_payroll_action_id
4370 and pbg.business_group_id = ppa.business_group_id;
4371 --
4372 -- Bugfix for 2177986: Unable to process separate payment elements in QuickPay.
4373 -- Commenting out control_separate_check_entries.
4374 --
4375 -- if v_leg_code = 'US' or
4376 -- v_leg_code = 'CA' then
4377 -- control_separate_check_entries (pi_payroll_action_id => p_payroll_action_id);
4378 -- end if;
4379 --
4380 -- Work out which process to run.
4381 -- For a QuickPay Pre-payment always call the 'QPPREPAY' process, regardless
4382 -- of the action_status.
4383 -- For a QuickPay Run when the assignment_action action_status is
4384 -- Unprocessed call the 'QUICKPAY' process. For a QuickPay Run when the
4385 -- assignment_action action_status is Marked for Retry or In Error then call
4386 -- the 'RETRY' process.
4387 --
4388 if v_action_type = 'U' then
4389 v_process_name := 'QPPREPAY';
4390 v_first_arg := 'QPPREPAY';
4391 elsif v_action_type = 'Q' and
4392 v_a_action_status = 'U' then
4393 v_process_name := 'QUICKPAY';
4394 v_first_arg := 'QUICKPAY';
4395 elsif v_action_type = 'Q' and
4396 (v_a_action_status = 'M' or
4397 v_a_action_status = 'E') then
4398 v_process_name := 'RETRY';
4399 v_first_arg := 'RERUN';
4400 elsif v_action_type ='X' then
4401 v_process_name := 'RETRY';
4402 v_first_arg := 'RERUN';
4403 else
4404 -- Error: You have attempted to issue a Start or Retry operation, after
4405 -- querying this record but before another operation has finished.
4406 -- You need to query this record and issue the operation again.
4407 hr_utility.set_message(801, 'HR_7265_QPAY_STATUS_OUT_SYNC');
4408 hr_utility.raise_error;
4409 end if;
4410 hr_utility.set_location(v_proc, 8);
4411 --
4412 -- For quickpay, add extra check here to see if assignment has got any net
4413 -- to gross entries. If it has, then issue a warning message only if a
4414 -- legislation rule exists for DISP_NTG_MESG
4415 --
4416 if (v_process_name = 'QUICKPAY' or v_process_name = 'RETRY')
4417 then
4418 if (p_status = 'START') then
4419 open get_leg_rule;
4420 fetch get_leg_rule into v_displ_msg;
4421 if (get_leg_rule%found and v_displ_msg = 'Y') then
4422 SELECT assignment_action_id
4423 INTO v_asactid
4424 FROM pay_assignment_actions
4425 WHERE payroll_action_id = p_payroll_action_id;
4426
4427 --
4428 -- Enhancement 3368211
4429 -- Pass use_qpay_excl_model to get_grossup_entry to determine
4430 -- which QuickPay data model we should be using
4431 --
4432 open get_grossup_entry(use_qpay_excl_model);
4433 fetch get_grossup_entry into v_grossup;
4434 --
4435 if get_grossup_entry%found then
4436 p_status := 'WARNING';
4437 return;
4438 else p_status := 'CONTINUE';
4439 end if;
4440 else
4441 p_status := 'CONTINUE';
4442 end if;
4443 end if;
4444 end if;
4445
4446 if (p_status = 'CONTINUE') then
4447 --
4448 -- Submit request to AOL concurrent manager
4449 --
4450 v_request_id :=
4451 fnd_request.submit_request
4452 ('PAY'
4453 ,program => v_process_name
4454 ,description => null
4455 ,start_time => null
4456 ,sub_request => false
4457 ,argument1 => v_first_arg
4458 ,argument2 => to_char(p_payroll_action_id)
4459 );
4460 hr_utility.set_location(v_proc, 9);
4461 --
4462 -- Detect if the request was really submitted.
4463 -- If it has not then handle the error.
4464 --
4465 if v_request_id = 0 then
4466 fnd_message.raise_error;
4467 end if;
4468 hr_utility.set_location(v_proc, 11);
4469 --
4470 -- Request has been accepted update payroll_actions
4471 -- with the request details.
4472 --
4473 update pay_payroll_actions
4474 set request_id = v_request_id
4475 where payroll_action_id = p_payroll_action_id;
4476 --
4477 end if;
4478 hr_utility.set_location(' Leaving:'|| v_proc, 12);
4479 --
4480 return;
4481 --
4482 end start_quickpay_process;
4483 --
4484 -- ----------------------------------------------------------------------------
4485 -- |--------------------------< wait_quickpay_process >-----------------------|
4486 -- ----------------------------------------------------------------------------
4487 --
4488 procedure wait_quickpay_process
4489 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
4490 ,p_display_run_number out nocopy pay_payroll_actions.
4491 display_run_number%TYPE
4492 ,p_a_action_status out nocopy pay_assignment_actions.action_status%TYPE
4493 ,p_process_info out nocopy varchar2
4494 ,p_request_id out nocopy pay_payroll_actions.request_id%TYPE
4495 ) is
4496 v_wait boolean;
4497 v_phase varchar2(255);
4498 v_status varchar2(255);
4499 v_dev_phase varchar2(255);
4500 v_dev_status varchar2(255);
4501 v_message varchar2(255);
4502 v_max_wait_sec number;
4503 v_interval_wait_sec number;
4504 v_request_id pay_payroll_actions.request_id%TYPE;
4505 v_p_action_status pay_payroll_actions.action_status%TYPE;
4506 v_proc varchar2(72) := g_package||'start_quickpay_run';
4507 --
4508 cursor cur_req is
4509 select request_id
4510 from pay_payroll_actions
4511 where payroll_action_id = p_payroll_action_id;
4512 --
4513 cursor cur_max is
4514 select fnd_number.canonical_to_number(parameter_value)
4515 from pay_action_parameters
4516 where parameter_name = 'QUICKPAY_MAX_WAIT_SEC';
4517 --
4518 cursor cur_intw is
4519 select fnd_number.canonical_to_number(parameter_value)
4520 from pay_action_parameters
4521 where parameter_name = 'QUICKPAY_INTERVAL_WAIT_SEC';
4522 --
4523 begin
4524 hr_utility.set_location('Entering:'|| v_proc, 5);
4525 --
4526 -- Check mandatory parameters have been set
4527 --
4528 hr_api.mandatory_arg_error
4529 (p_api_name => v_proc
4530 ,p_argument => 'payroll_action_id'
4531 ,p_argument_value => p_payroll_action_id
4532 );
4533 --
4534 -- Find out the concurrent request_id for this QuickPay
4535 --
4536 open cur_req;
4537 fetch cur_req into v_request_id;
4538 if cur_req%notfound then
4539 close cur_req;
4540 -- Error: This primary key does not exist in the database.
4541 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
4542 hr_utility.raise_error;
4543 end if;
4544 close cur_req;
4545 hr_utility.set_location(v_proc, 7);
4546 --
4547 -- Check the request_id has been set. A value of zero means the submit
4548 -- request was not accepted by the concurrent manager. (This value should
4549 -- not be in pay_payroll_actions in the first place.)
4550 --
4551 if (v_request_id is null) or (v_request_id = 0) then
4552 -- Error: You have attempted to wait for a request to finish, when no
4553 -- request has been submitted for this QuickPay Run or QuickPay
4554 -- Pre-payment.
4555 hr_utility.set_message(801, 'HR_7266_QPAY_WAIT_NO_REQUEST');
4556 hr_utility.raise_error;
4557 end if;
4558 hr_utility.set_location(v_proc, 9);
4559 --
4560 -- Attempt to find out the QuickPay Concurrent manager max wait time
4561 -- and polling interval time from pay_action_parameters. If values
4562 -- cannot be found in this table then default to a max wait of 300
4563 -- seconds and polling interval of 2 seconds.
4564 --
4565 open cur_max;
4566 fetch cur_max into v_max_wait_sec;
4567 if cur_max %notfound then
4568 close cur_max;
4569 -- Value not in table, set to the default
4570 v_max_wait_sec := 300;
4571 else
4572 close cur_max;
4573 end if;
4574 hr_utility.set_location(v_proc, 10);
4575 --
4576 open cur_intw;
4577 fetch cur_intw into v_interval_wait_sec;
4578 if cur_intw %notfound then
4579 close cur_intw;
4580 -- Value not in table, set to the default
4581 v_interval_wait_sec := 2;
4582 else
4583 close cur_intw;
4584 end if;
4585 hr_utility.set_location(v_proc, 11);
4586 --
4587 -- Waits for request to finish on the concurrent manager.
4588 -- Or gives up if the maximum wait time is reached.
4589 --
4590 v_wait := fnd_concurrent.wait_for_request
4591 (request_id => v_request_id
4592 ,interval => v_interval_wait_sec
4593 ,max_wait => v_max_wait_sec
4594 ,phase => v_phase
4595 ,status => v_status
4596 ,dev_phase => v_dev_phase
4597 ,dev_status => v_dev_status
4598 ,message => v_message
4599 );
4600 hr_utility.set_location(v_proc, 12);
4601 --
4602 -- N.B. This point in the code can be reached for one of two reasons.
4603 -- 1) The AOL process has reached a concurrent manager 'phase'
4604 -- value of 'COMPLETED'.
4605 -- 2) The wait has given up because the maximum wait time has been
4606 -- reached.
4607 -- In other words, the AOL process may have, or may not have, finished.
4608 --
4609 -- Find out the latest payroll and assignment action status
4610 --
4611 get_latest_status
4612 (p_payroll_action_id => p_payroll_action_id
4613 ,p_p_action_status => v_p_action_status
4614 ,p_display_run_number => p_display_run_number
4615 ,p_a_action_status => p_a_action_status
4616 );
4617 hr_utility.set_location(v_proc, 13);
4618 --
4619 -- Work out the process information for the caller
4620 -- (This assumes that when the payroll_action.action_status is 'Complete' or
4621 -- 'In Error' the payroll_action.current_task will not be null. At the
4622 -- time of writing this is an unfair assumption. But it will not be in
4623 -- the future.)
4624 --
4625 if v_p_action_status = 'U' or
4626 v_p_action_status = 'M' then
4627 p_process_info := 'PROCESS_NOT_STARTED';
4628 elsif v_p_action_status = 'P' then
4629 p_process_info := 'PROCESS_RUNNING';
4630 else -- (p_p_action_status is 'C' or 'E')
4631 p_process_info := 'PROCESS_FINISHED';
4632 end if;
4633 --
4634 p_request_id := v_request_id;
4635 --
4636 hr_utility.set_location(' Leaving:'|| v_proc, 14);
4637 --
4638 end wait_quickpay_process;
4639 --
4640 end pay_qpq_api;
[Home] [Help]