1 Package Body pay_qpi_api as
2 /* $Header: pyqpirhi.pkb 115.5 2004/05/20 07:26:29 nbristow ship $ */
3 --
4 -- Current record structure definition
5 --
6 g_old_rec pay_quickpay_inclusions%ROWTYPE;
7 --
8 -- Global package name
9 --
10 g_package varchar2(30) := ' pay_qpi_api.';
11 --
12 -- ----------------------------------------------------------------------------
13 -- |-------------------------< validate_asg_act_id >--------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- Checks the assignment_action_id is valid. To be used in insert
19 -- validation.
20 --
21 -- Pre Conditions:
22 -- None.
23 --
24 -- In Arguments:
25 -- p_assignment_action_id is the id to validate. This is a mandatory
26 -- argument.
27 --
28 -- Post Success:
29 -- Ends successfully if a matching assignment_action_id exists in
30 -- pay_assignment_actions and it is for a QuickPay Run process.
31 --
32 -- Post Failure:
33 -- Raises an error if at least one of the following is true:
34 -- 1) An Assignment Process with an id of p_assignment_action_id does
35 -- not exist.
36 -- 2) The Assignment Process does exist but it is not for a 'QuickPay Run'
37 -- Payroll Process.
38 --
39 -- Access Status:
40 -- Private to this package.
41 --
42 -- {End Of Comments}
43 --
44 procedure validate_asg_act_id
45 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
46 ) is
47 --
48 v_exists varchar2(1);
49 v_proc varchar2(72) := g_package||'validate_asg_act_id';
50 --
51 cursor sel_exists is
52 select 'Y'
53 from pay_assignment_actions
54 where assignment_action_id = p_assignment_action_id;
55 --
56 cursor sel_for_qpr is
57 select 'Y'
58 from pay_payroll_actions pya
59 , pay_assignment_actions asa
60 where pya.action_type = 'Q'
61 and pya.payroll_action_id = asa.payroll_action_id
62 and asa.assignment_action_id = p_assignment_action_id;
63 begin
64 hr_utility.set_location('Entering:'|| v_proc, 5);
65 --
66 -- Check mandatory parameters have been set
67 --
68 hr_api.mandatory_arg_error
69 (p_api_name => v_proc
70 ,p_argument => 'assignment_action_id'
71 ,p_argument_value => p_assignment_action_id
72 );
73 --
74 -- Check the assignment action exists
75 --
76 Open sel_exists;
77 fetch sel_exists into v_exists;
78 if sel_exists%notfound then
79 close sel_exists;
80 -- Error: You have tried to include an element entry in QuickPay for an
81 -- assignment process that does not exist. The p_rec.assignment_action_id
82 -- (or p_assignment_action_id) argument has been set to an incorrect value.
83 hr_utility.set_message(801, 'HR_7224_QPAY_NO_ASG_ACT');
84 hr_utility.raise_error;
85 end if;
86 close sel_exists;
87 hr_utility.set_location(v_proc, 6);
88 --
89 -- Check the assignment action is for a 'QuickPay Run' payroll action
90 --
91 Open sel_for_qpr;
92 fetch sel_for_qpr into v_exists;
93 if sel_for_qpr%notfound then
94 close sel_for_qpr;
95 -- Error: You have tried to include an element entry in QuickPay for an
96 -- invalid type of payroll process. The payroll process corresponding to
97 -- the assignment process (p_rec.assignment_action_id) must be of the type
98 -- QuickPay Run.
99 hr_utility.set_message(801, 'HR_7230_QPAY_NOT_ACT_QP');
100 hr_utility.raise_error;
101 end if;
102 close sel_for_qpr;
103 hr_utility.set_location(' Leaving:'|| v_proc, 10);
104 end validate_asg_act_id;
105 --
106 -- ----------------------------------------------------------------------------
107 -- |-------------------------< validate_ele_ent_id >--------------------------|
108 -- ----------------------------------------------------------------------------
109 -- {Start Of Comments}
110 --
111 -- Description:
112 -- Checks the element_entry_id is validate. Used in insert validation.
113 --
114 -- Pre Conditions:
115 -- None.
116 --
117 -- In Arguments:
118 -- p_rec is the QuickPay Inclusion record which contains the element entry
119 -- to check.
120 --
121 -- Post Success:
122 -- End successfully if it is valid to include the element entry for this
123 -- QuickPay Run assignment process.
124 --
125 -- Post Failure:
126 -- Raises an error if at least one of the following is true:
127 -- 1) No element entry exists with an id of p_rec.element_entry_id.
128 -- 2) The element entry does not exist as of the QuickPay Run date_earned.
129 -- 3) The element entry is not for the assignment as defined on the
130 -- Assignment Action.
131 -- 4) The element type for the element entry cannot be processed in a run.
132 -- 5) The entry type is balance adjustment, replacement adjustment or
133 -- additive adjustment.
134 --
135 -- Access Status:
136 -- Private to this package.
137 --
138 -- {End Of Comments}
139 --
140 procedure validate_ele_ent_id
141 (p_rec in pay_quickpay_inclusions%ROWTYPE
142 ) is
143 --
144 v_exists varchar2(1);
145 v_date_earned pay_payroll_actions.date_earned%TYPE;
146 v_proc varchar2(72) := g_package||'validate_ele_ent_id';
147 --
148 cursor cur_exists is
149 select 'Y'
150 from dual
151 where exists (select 'Y'
152 from pay_element_entries_f
153 where element_entry_id = p_rec.element_entry_id);
154 --
155 cursor cur_date is
156 select pya.date_earned
157 from
158 pay_element_types_f et
159 , pay_element_links_f el
160 , pay_element_entries_f ent
161 , pay_payroll_actions pya
162 , pay_assignment_actions aga
163 where ent.element_entry_id = p_rec.element_entry_id
164 and ent.effective_start_date <= pya.date_earned
165 and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
166 pay_interpreter_pkg.prorate_start_date
167 (aga.assignment_action_id,
168 et.proration_group_id
169 ))
170 and ent.element_link_id = el.element_link_id
171 and el.element_type_id = et.element_type_id
172 and pya.date_earned between el.effective_start_date
173 and el.effective_end_date
174 and pya.date_earned between et.effective_start_date
175 and et.effective_end_date
176 and pya.payroll_action_id = aga.payroll_action_id
177 and aga.assignment_action_id = p_rec.assignment_action_id;
178 --
179 cursor cur_same is
180 select 'Y'
181 from
182 pay_element_types_f et
183 , pay_element_links_f el
184 , pay_element_entries_f ent
185 , pay_payroll_actions pya
186 , pay_assignment_actions aga
187 where ent.element_entry_id = p_rec.element_entry_id
188 and ent.assignment_id = aga.assignment_id
189 and ent.effective_start_date <= pya.date_earned
190 and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
191 pay_interpreter_pkg.prorate_start_date
192 (aga.assignment_action_id,
193 et.proration_group_id
194 ))
195 and ent.element_link_id = el.element_link_id
196 and el.element_type_id = et.element_type_id
197 and pya.date_earned between el.effective_start_date
198 and el.effective_end_date
199 and pya.date_earned between et.effective_start_date
200 and et.effective_end_date
201 and pya.payroll_action_id = aga.payroll_action_id
202 and aga.assignment_action_id = p_rec.assignment_action_id;
203 --
204 cursor cur_inrun (p_date_earned date) is
205 select 'Y'
206 from pay_element_types_f ety
207 , pay_element_links_f elk
208 , pay_element_entries_f ent
209 where /* Element Types */
210 ety.process_in_run_flag = 'Y'
211 and ety.element_type_id = elk.element_type_id
212 and p_date_earned between ety.effective_start_date
213 and ety.effective_end_date
214 /* Element Links */
215 and elk.element_link_id = ent.element_link_id
216 and p_date_earned between elk.effective_start_date
217 and elk.effective_end_date
218 /* Element Entries */
219 and ent.effective_start_date <= p_date_earned
220 and ent.effective_end_date >= decode(ety.proration_group_id, null, p_date_earned,
221 pay_interpreter_pkg.prorate_start_date
222 (p_rec.assignment_action_id,
223 ety.proration_group_id
224 ))
225 and ent.element_entry_id = p_rec.element_entry_id;
226 --
227 cursor cur_entry (p_date_earned date) is
228 select 'Y'
229 from pay_element_entries_f
230 where p_date_earned between effective_start_date
231 and effective_end_date
232 and entry_type in ('B', 'A', 'R')
233 and element_entry_id = p_rec.element_entry_id;
234 --
235 cursor cur_fur_ele (p_date_earned date) is
236 select 'Y'
237 from pay_element_types_f ety
238 , pay_element_links_f elk
239 , pay_element_entries_f ent
240 where /*
241 * Element Types
242 */
243 ety.element_type_id = elk.element_type_id
244 and p_date_earned between ety.effective_start_date
245 and ety.effective_end_date
246 /*
247 * Element Links
248 */
249 and elk.element_link_id = ent.element_link_id
250 and p_date_earned between elk.effective_start_date
251 and elk.effective_end_date
252 /*
253 * Element Entries, further checks
254 */
255 and ent.element_entry_id = p_rec.element_entry_id
256 and ent.effective_start_date <= p_date_earned
257 and ent.effective_end_date >= decode(ety.proration_group_id, null, p_date_earned,
258 pay_interpreter_pkg.prorate_start_date
259 (p_rec.assignment_action_id,
260 ety.proration_group_id
261 ))
262 /*
263 * Non-recurring entries can only be included if they have not
264 * been processed.
265 */
266 and ( ( ( (ety.processing_type = 'N'
267 )
268 /*
269 * Recurring, additional or override entries can only be
270 * included if they have not been processed. (These types of
271 * recurring entry are handled as if they were non-recurring.)
272 */
273 or ( ety.processing_type = 'R'
274 and ent.entry_type <> 'E'
275 )
276 )
277 and (not exists (select null
278 from pay_run_results pr1
279 where pr1.source_id = ent.element_entry_id
280 and pr1.source_type = 'E'
281 and pr1.status in ('P', 'PA')
282 and not exists (select ''
283 from pay_run_results pr2
284 where pr2.source_id = pr1.run_result_id
285 and pr2.source_type = 'R'
286 )
287 )
288 )
289 )
290 /*
291 * Include other recurring entries.
292 * i.e. Those which are not additional or overrides entries.
293 */
294 or ( ety.processing_type = 'R'
295 and ent.entry_type = 'E'
296 )
297 );
298 --
299 begin
300 hr_utility.set_location('Entering:'|| v_proc, 5);
301 --
302 -- Check that the element entry exists with id p_element_entry_id
303 --
304 open cur_exists;
305 fetch cur_exists into v_exists;
306 if cur_exists%notfound then
307 close cur_exists;
308 -- Error: You have tried to include a non-existent element entry for
309 -- QuickPay. The p_rec.element_entry_id (or element_entry_id) argument has
310 -- been set to an incorrect value.
311 hr_utility.set_message(801, 'HR_7232_QPAY_NO_ELE_ENT');
312 hr_utility.raise_error;
313 end if;
314 close cur_exists;
315 hr_utility.set_location(v_proc, 6);
316 --
317 -- Check the element entry exists as of the QuickPay Run
318 -- date_earned.
319 --
320 open cur_date;
321 fetch cur_date into v_date_earned;
322 if cur_date%notfound then
323 close cur_date;
324 -- Error: You have tried to include an element entry for QuickPay that
325 -- does not exist at Date Earned. You can only include element entries
326 -- that are date effective at this date.
327 hr_utility.set_message(801, 'HR_7233_QPAY_NO_ELE_D_EARN');
328 hr_utility.raise_error;
329 end if;
330 close cur_date;
331 hr_utility.set_location(v_proc, 7);
332 --
333 -- Check element entry must be for the same
334 -- assignment as the assignment action
335 --
336 open cur_same;
337 fetch cur_same into v_exists;
338 if cur_same%notfound then
339 close cur_same;
340 -- Error: You have tried to include an element entry for QuickPay that is
341 -- not for the same assignment as defined for the QuickPay run.
342 hr_utility.set_message(801, 'HR_7234_QPAY_ELE_NOT_ASG');
343 hr_utility.raise_error;
344 end if;
345 close cur_same;
346 hr_utility.set_location(v_proc, 8);
347 --
348 -- Check that the element type, for the element entry,
349 -- can be processed in the run.
350 --
351 open cur_inrun(v_date_earned);
352 fetch cur_inrun into v_exists;
353 if cur_inrun%notfound then
354 close cur_inrun;
355 -- Error: You have tried to include an element entry for QuickPay for an
356 -- element that cannot be processed in a payroll run.
357 hr_utility.set_message(801, 'HR_7236_QPAY_ELE_NOT_PRO');
358 hr_utility.raise_error;
359 end if;
360 close cur_inrun;
361 hr_utility.set_location(v_proc, 9);
362 --
363 -- Check the element entry does not have a entry_type of
364 -- balance adjustment, replacement adjustment or additive adjustment.
365 --
366 open cur_entry(v_date_earned);
367 fetch cur_entry into v_exists;
368 if cur_entry%found then
369 close cur_entry;
370 -- Error: You have tried to include an element entry in a QuickPay run
371 -- that can only be used for a balance adjustment, replacement adjustment
372 -- or additive adjustment.
373 hr_utility.set_message(801, 'HR_7237_QPAY_ELE_WRG_CAT');
374 hr_utility.raise_error;
375 end if;
376 close cur_entry;
377 hr_utility.set_location(v_proc, 10);
378 --
379 -- Further element entry checks:
380 -- A non-recurring entry cannot be included if it has already
381 -- been processed.
382 -- A recurring entry which is an 'Additional entry' or 'Override' cannot
383 -- be included if it has already been processed. (These types of recurring
384 -- entry are handled like a non-recurring entries.)
385 --
386 open cur_fur_ele(v_date_earned);
387 fetch cur_fur_ele into v_exists;
388 if cur_fur_ele%notfound then
389 close cur_fur_ele;
390 -- Error: You have tried to include an element entry in a QuickPay run
391 -- that has already been processed. It is either a processed non-recurring
392 -- entry or a processed recurring, additional or override entry.
393 hr_utility.set_message(801, 'HR_7284_QPAY_ELE_ARY_PRO');
394 hr_utility.raise_error;
395 end if;
396 close cur_fur_ele;
397 hr_utility.set_location(' Leaving:'|| v_proc, 11);
398 --
399 end validate_ele_ent_id;
400 --
401 -- ----------------------------------------------------------------------------
402 -- |-------------------------< validate_row_exists >--------------------------|
403 -- ----------------------------------------------------------------------------
404 -- {Start Of Comments}
405 --
406 -- Description:
407 -- Validates that a Quickpay Inclusion does not exist. Used in insert
408 -- validation.
409 --
410 -- Pre Conditions:
411 -- None.
412 --
413 -- In Arguments:
414 -- p_rec is the QuickPay Inclusion record to check for.
415 --
416 -- Post Success:
417 -- This procedure ends successfully if the the QuickPay Inclusion does
418 -- not already exist in the pay_quickpay_inclusions table.
419 --
420 -- Post Failure:
421 -- Raises an error if the Quickpay Inclusion p_rec already exists in the
422 -- database.
423 --
424 -- Access Status:
425 -- Private to this package.
426 --
427 -- {End Of Comments}
428 --
429 procedure validate_row_exists
430 (p_rec in pay_quickpay_inclusions%ROWTYPE
431 ) is
432 --
433 v_exists varchar2(1);
434 v_proc varchar2(72) := g_package||'validate_row_exists';
435 --
436 cursor cur_exists is
437 select 'Y'
438 from pay_quickpay_inclusions
439 where element_entry_id = p_rec.element_entry_id
440 and assignment_action_id = p_rec.assignment_action_id;
441 begin
442 hr_utility.set_location('Entering:'|| v_proc, 5);
443 --
444 -- Check that quickpay inclusion does not exist.
445 --
446 open cur_exists;
447 fetch cur_exists into v_exists;
448 if cur_exists%found then
449 close cur_exists;
450 -- Error: You have tried to include the same element entry more than once
451 -- in the QuickPay run.
452 hr_utility.set_message(801, 'HR_7238_QPAY_ELE_INCED');
453 hr_utility.raise_error;
454 end if;
455 close cur_exists;
456 hr_utility.set_location(' Leaving:'|| v_proc, 10);
457 end validate_row_exists;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |--------------------------< validate_act_cmp >----------------------------|
461 -- ----------------------------------------------------------------------------
462 -- {Start Of Comments}
463 --
464 -- Description:
465 -- Checks that the assignment action associated with this quickpay inclusion
466 -- does not have a status of complete and the payroll action does not have
467 -- a current task set to not null. This procedure is used in insert and
468 -- delete validation.
469 --
470 -- Pre Conditions:
471 -- A QuickPay Run assignment process already exists in pay_assignment_actions
472 -- with an id of p_assignment_action_id.
473 --
474 -- In Arguments:
475 -- p_assignment_action_id is the assignment_action_id on the Quickpay
476 -- Inclusion row.
477 --
478 -- Post Success:
479 -- Completes successfully if the assignment action does not have an
480 -- action_status of complete and the corresponding payroll process has a
481 -- null current_task.
482 --
483 -- Post Failure:
484 -- Raises an error if the Assignment Action has a status of complete or the
485 -- payroll process current_task is not null.
486 --
487 -- Access Status:
488 -- Private to this package.
489 --
490 -- {End Of Comments}
491 --
492 procedure validate_act_cmp
493 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
494 ) is
495 --
496 v_exists varchar2(1);
497 v_proc varchar2(72) := g_package||'validate_act_cmp';
498 --
499 cursor cur_aga_act is
500 select 'Y'
501 from pay_assignment_actions
502 where assignment_action_id = p_assignment_action_id
503 and action_status in ('C', 'S');
504 --
505 cursor cur_pya_act is
506 select 'Y'
507 from pay_payroll_actions pya
508 , pay_assignment_actions aga
509 where aga.assignment_action_id = p_assignment_action_id
510 and pya.payroll_action_id = aga.payroll_action_id
511 and pya.current_task is not null;
512 begin
513 hr_utility.set_location('Entering:'|| v_proc, 5);
514 --
515 -- Check that assignment_action does not have a status of Complete.
516 --
517 open cur_aga_act;
518 fetch cur_aga_act into v_exists;
519 if cur_aga_act%found then
520 close cur_aga_act;
521 -- Error: You have tried to include or exclude an element entry for a
522 -- QuickPay run that has completed successfully. To do this you must
523 -- change the assignment process status to Marked for Retry.
524 hr_utility.set_message(801, 'HR_7239_QPAY_ELE_MARK');
525 hr_utility.raise_error;
526 end if;
527 close cur_aga_act;
528 hr_utility.set_location(v_proc, 6);
529 --
530 -- Check that payroll_action does not have
531 -- current_task set to a not null value.
532 --
533 open cur_pya_act;
534 fetch cur_pya_act into v_exists;
535 if cur_pya_act%found then
536 close cur_pya_act;
537 -- Error: You have tried to include or exclude an element entry from a
538 -- QuickPay run while the run is processing.
539 hr_utility.set_message(801, 'HR_7240_QPAY_ELE_RUNNING');
540 hr_utility.raise_error;
541 end if;
542 close cur_pya_act;
543 hr_utility.set_location(' Leaving:'|| v_proc, 10);
544 end validate_act_cmp;
545 --
546 -- ----------------------------------------------------------------------------
547 -- |------------------------------< insert_dml >------------------------------|
548 -- ----------------------------------------------------------------------------
549 -- {Start Of Comments}
550 --
551 -- Description:
552 -- This procedure controls the actual dml insert logic. The function of this
553 -- procedure is to insert the row into the schema.
554 --
555 -- Pre Conditions:
556 -- This is an internal private procedure which must be called from the ins
557 -- procedure and must have all mandatory arguments set.
558 --
559 -- In Arguments:
560 -- A Pl/Sql record structure.
561 --
562 -- Post Success:
563 -- The specified row will be inserted into the schema
564 -- (pay_quickpay_inclusions table).
565 --
566 -- Post Failure:
567 -- If an error occurs a standard Oracle error will be raised.
568 --
569 -- Developer Implementation Notes:
570 -- None.
571 --
572 -- Access Status:
573 -- Internal Development Use Only.
574 --
575 -- {End Of Comments}
576 --
577 procedure insert_dml(p_rec in out nocopy pay_quickpay_inclusions%ROWTYPE) is
578 --
579 v_proc varchar2(72) := g_package||'insert_dml';
580 --
581 begin
582 hr_utility.set_location('Entering:'||v_proc, 5);
583 --
584 -- Insert the row into: pay_quickpay_inclusions
585 --
586 insert into pay_quickpay_inclusions
587 (element_entry_id
588 ,assignment_action_id
589 )
590 values
591 (p_rec.element_entry_id
592 ,p_rec.assignment_action_id
593 );
594 --
595 hr_utility.set_location(' Leaving:'||v_proc, 10);
596 end insert_dml;
597 --
598 -- ----------------------------------------------------------------------------
599 -- |------------------------------< delete_dml >------------------------------|
600 -- ----------------------------------------------------------------------------
601 -- {Start Of Comments}
602 --
603 -- Description:
604 -- This procedure controls the actual dml delete logic. The function of this
605 -- procedure is to delete the specified row from the schema using the
606 -- primary key.
607 --
608 -- Pre Conditions:
609 -- This is an internal private procedure which must be called from the del
610 -- procedure.
611 --
612 -- In Arguments:
613 -- A Pl/Sql record structure.
614 --
615 -- Post Success:
616 -- The specified row will be delete from the schema.
617 --
618 -- Post Failure:
619 -- If an error occurs a standard Oracle error will be raised.
620 --
621 -- Developer Implementation Notes:
622 -- None.
623 --
624 -- Access Status:
625 -- Internal Development Use Only.
626 --
627 -- {End Of Comments}
628 --
629 Procedure delete_dml(p_rec in pay_quickpay_inclusions%ROWTYPE) is
630 --
631 v_proc varchar2(72) := g_package||'delete_dml';
632 --
633 Begin
634 hr_utility.set_location('Entering:'|| v_proc, 5);
635 --
636 -- Delete the pay_quickpay_inclusions row.
637 --
638 delete from pay_quickpay_inclusions
639 where element_entry_id = p_rec.element_entry_id
640 and assignment_action_id = p_rec.assignment_action_id;
641 --
642 If sql%NOTFOUND then
643 --
644 -- The row to be deleted was NOT found therefore a serious
645 -- error has occurred which MUST be reported.
646 --
647 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
648 hr_utility.set_message_token('PROCEDURE', v_proc);
649 hr_utility.set_message_token('STEP','5');
650 hr_utility.raise_error;
651 End If;
652 --
653 hr_utility.set_location(' Leaving:'|| v_proc, 10);
654 End delete_dml;
655 --
656 -- ----------------------------------------------------------------------------
657 -- |---------------------------------< lck >----------------------------------|
658 -- ----------------------------------------------------------------------------
659 --
660 procedure lck
661 (p_element_entry_id in number
662 ,p_assignment_action_id in number
663 ) is
664 --
665 -- Cursor selects the 'current' row from the HR Schema
666 --
667 Cursor C_Sel1 is
668 select *
669 from pay_quickpay_inclusions
670 where element_entry_id = p_element_entry_id
671 and assignment_action_id = p_assignment_action_id
672 for update nowait;
673 --
674 v_proc varchar2(72) := g_package||'lck';
675 --
676 Begin
677 hr_utility.set_location('Entering:'|| v_proc, 5);
678 --
679 -- The primary key exists therefore we must now attempt to lock the
680 -- row and check the object version numbers.
681 --
682 Open C_Sel1;
683 Fetch C_Sel1 Into g_old_rec;
684 If C_Sel1%notfound then
685 Close C_Sel1;
686 --
687 -- The primary key is invalid therefore we must error
688 --
689 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
690 hr_utility.raise_error;
691 End If;
692 Close C_Sel1;
693 --
694 hr_utility.set_location(' Leaving:'|| v_proc, 10);
695 --
696 -- We need to trap the ORA LOCK exception
697 --
698 Exception
699 When HR_api.Object_Locked then
700 --
701 -- The object is locked therefore we need to supply a meaningful
702 -- error message.
703 --
704 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
705 hr_utility.set_message_token('TABLE_NAME', 'pay_quickpay_inclusions');
706 hr_utility.raise_error;
707 End lck;
708 --
709 -- ----------------------------------------------------------------------------
710 -- |-----------------------------< convert_args >-----------------------------|
711 -- ----------------------------------------------------------------------------
712 -- {Start Of Comments}
713 --
714 -- Description:
715 -- This function is used to turn attribute arguments into the record
716 -- structure.
717 --
718 -- Pre Conditions:
719 -- This is a private function and can only be called from the ins or upd
720 -- attribute processes.
721 --
722 -- In Arguments:
723 -- The individual attributes of the QuickPay Inclusion. p_element_entry_id
724 -- and p_assignment_action_id.
725 --
726 -- Post Success:
727 -- A record structure will be returned of type
728 -- pay_quickpay_inclusions%ROWTYPE.
729 --
730 -- Post Failure:
731 -- No direct error handling is required within this function. Any possible
732 -- errors within this function will be a PL/SQL value error due to conversion
733 -- of datatypes or data lengths.
734 --
735 -- Developer Implementation Notes:
736 -- None.
737 --
738 -- Access Status:
739 -- Internal Development Use Only.
740 --
741 -- {End Of Comments}
742 --
743 function convert_args
744 (p_element_entry_id in number
745 ,p_assignment_action_id in number
746 ) return pay_quickpay_inclusions%ROWTYPE is
747 --
748 v_rec pay_quickpay_inclusions%ROWTYPE;
749 v_proc varchar2(72) := g_package||'convert_args';
750 --
751 Begin
752 --
753 hr_utility.set_location('Entering:'|| v_proc, 5);
754 --
755 -- Convert arguments into local l_rec structure.
756 --
757 v_rec.element_entry_id := p_element_entry_id;
758 v_rec.assignment_action_id := p_assignment_action_id;
759 --
760 -- Return the plsql record structure.
761 --
762 hr_utility.set_location(' Leaving:'|| v_proc, 10);
763 Return(v_rec);
764 --
765 End convert_args;
766 --
767 -- ----------------------------------------------------------------------------
768 -- |---------------------------< insert_validate >----------------------------|
769 -- ----------------------------------------------------------------------------
770 -- {Start Of Comments}
771 --
772 -- Description:
773 -- This procedure controls the execution of all insert business rules
774 -- validation.
775 --
776 -- Pre Conditions:
777 -- This private procedure is called from ins procedure.
778 --
779 -- In Arguments:
780 -- A Pl/Sql record structure.
781 --
782 -- Post Success:
783 -- Processing continues if no validation errors are found.
784 --
785 -- Post Failure:
786 -- If a business rules fails the error will not be handled by this procedure
787 -- unless explicitly coded.
788 --
789 -- Access Status:
790 -- Internal Development Use Only.
791 --
792 -- {End Of Comments}
793 --
794 procedure insert_validate(p_rec in pay_quickpay_inclusions%ROWTYPE) is
795 --
796 v_proc varchar2(72) := g_package||'insert_validate';
797 --
798 begin
799 hr_utility.set_location('Entering:'|| v_proc, 5);
800 --
801 -- Call all supporting business operations
802 --
803 -- Validate that the assignment action actually exists and it
804 -- is for a 'QuickPay Run' Payroll Process.
805 --
806 validate_asg_act_id(p_assignment_action_id => p_rec.assignment_action_id);
807 hr_utility.set_location(v_proc, 6);
808 --
809 -- Validate that corresponding assignment action
810 -- does not have a status of Complete and the corresponding
811 -- Payroll Process is not being processed.
812 --
813 validate_act_cmp(p_assignment_action_id => p_rec.assignment_action_id);
814 hr_utility.set_location(v_proc, 7);
815 --
816 -- Validate the element entry.
817 --
818 validate_ele_ent_id(p_rec => p_rec);
819 hr_utility.set_location(v_proc, 8);
820 --
821 -- Validate that this quickpay inclusion does not already exist.
822 --
823 validate_row_exists(p_rec => p_rec);
824 hr_utility.set_location(v_proc, 9);
825 --
826 hr_utility.set_location(' Leaving:'|| v_proc, 10);
827 end insert_validate;
828 --
829 -- ----------------------------------------------------------------------------
830 -- |---------------------------< delete_validate >----------------------------|
831 -- ----------------------------------------------------------------------------
832 -- {Start Of Comments}
833 --
834 -- Description:
835 -- This procedure controls the execution of all delete business rules
836 -- validation.
837 --
838 -- Pre Conditions:
839 -- This private procedure is called from del procedure.
840 --
841 -- In Arguments:
842 -- A Pl/Sql record structure.
843 --
844 -- Post Success:
845 -- Processing continues is no delete validation errors.
846 --
847 -- Post Failure:
848 -- If a business rules fails the error will not be handled by this procedure
849 -- unless explicitly coded.
850 --
851 -- Access Status:
852 -- Internal Development Use Only.
853 --
854 -- {End Of Comments}
855 --
856 procedure delete_validate(p_rec in pay_quickpay_inclusions%ROWTYPE) is
857 --
858 v_proc varchar2(72) := g_package||'delete_validate';
859 --
860 begin
861 hr_utility.set_location('Entering:'|| v_proc, 5);
862 --
863 -- Validate that corresponding assignment action
864 -- does not have a status of Complete and the corresponding
865 -- payroll action does not have a status of Processing.
866 --
867 validate_act_cmp(p_assignment_action_id => p_rec.assignment_action_id);
868 --
869 hr_utility.set_location(' Leaving:'|| v_proc, 10);
870 end delete_validate;
871 --
872 -- ----------------------------------------------------------------------------
873 -- |---------------------------------< ins >----------------------------------|
874 -- ----------------------------------------------------------------------------
875 --
876 Procedure ins
877 (
878 p_rec in out nocopy pay_quickpay_inclusions%ROWTYPE,
879 p_validate in boolean default false
880 ) is
881 --
882 v_proc varchar2(72) := g_package||'ins';
883 --
884 Begin
885 hr_utility.set_location('Entering:'|| v_proc, 5);
886 --
887 -- Determine if the business process is to be validated.
888 --
889 If p_validate then
890 --
891 -- Issue the savepoint.
892 --
893 SAVEPOINT ins_pay_qpi;
894 End If;
895 --
896 -- Call the supporting insert validate operations
897 --
898 insert_validate(p_rec);
899 --
900 -- Insert the row
901 --
902 insert_dml(p_rec);
903 --
904 -- If we are validating then raise the Validate_Enabled exception
905 --
906 if p_validate then
907 raise hr_api.validate_enabled;
908 end if;
909 --
910 hr_utility.set_location(' Leaving:'||v_proc, 10);
911 exception
912 when hr_api.validate_enabled then
913 --
914 -- As the Validate_Enabled exception has been raised
915 -- we must rollback to the savepoint
916 --
917 ROLLBACK TO ins_pay_qpi;
918 end ins;
919 --
920 -- ----------------------------------------------------------------------------
921 -- |---------------------------------< ins >----------------------------------|
922 -- ----------------------------------------------------------------------------
923 --
924 procedure ins
925 (p_element_entry_id in number
926 ,p_assignment_action_id in number
927 ,p_validate in boolean default false
928 ) is
929 --
930 v_rec pay_quickpay_inclusions%ROWTYPE;
931 v_proc varchar2(72) := g_package||'ins';
932 --
933 Begin
934 hr_utility.set_location('Entering:'|| v_proc, 5);
935 --
936 -- Call conversion function to turn arguments into the
937 -- pl/sql record structure.
938 --
939 v_rec :=
940 convert_args
941 (p_element_entry_id => p_element_entry_id
942 ,p_assignment_action_id => p_assignment_action_id
943 );
944 --
945 -- Having converted the arguments into a pl/sql record structure
946 -- call the entity version of ins.
947 --
948 ins(v_rec, p_validate);
949 --
950 hr_utility.set_location(' Leaving:'|| v_proc, 10);
951 End ins;
952 --
953 -- ----------------------------------------------------------------------------
954 -- |-------------------------< bulk_default_ins >-----------------------------|
955 -- ----------------------------------------------------------------------------
956 --
957 procedure bulk_default_ins
958 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
959 ,p_validate in boolean default false
960 ) is
961 --
962 v_proc varchar2(72) := g_package||'bulk_default_ins';
963 --
964 begin
965 hr_utility.set_location('Entering:'|| v_proc, 5);
966 --
967 -- Determine if the business process is to be validated.
968 --
969 if p_validate then
970 --
971 -- Issue the savepoint.
972 --
973 savepoint bulk_default_ins_pay_qpi;
974 end if;
975 --
976 -- Validate the assignment_action exists and the corresponding
977 -- payroll_action is for a 'QuickPay Run'.
978 --
979 validate_asg_act_id(p_assignment_action_id => p_assignment_action_id);
980 hr_utility.set_location(v_proc, 6);
981 --
982 -- Validate that corresponding assignment action
983 -- does not have a status of Complete and the corresponding
984 -- payroll action does not have a status of Processing.
985 --
986 validate_act_cmp(p_assignment_action_id => p_assignment_action_id);
987 hr_utility.set_location(v_proc, 7);
988 --
989 -- Insert the default quickpay inclusions
990 --
991 insert into pay_quickpay_inclusions
992 (element_entry_id
993 ,assignment_action_id)
994 select distinct
995 ent.element_entry_id
996 , asa.assignment_action_id
997 from pay_element_types_f ety
998 , pay_element_links_f elk
999 , pay_element_entries_f ent
1000 , pay_payroll_actions pya
1001 , pay_assignment_actions asa
1002 where /*
1003 * Element Type:
1004 * Only include those which can be processed in the run.
1005 */
1006 ety.process_in_run_flag = 'Y'
1007 and ety.element_type_id = elk.element_type_id
1008 and pya.date_earned between ety.effective_start_date
1009 and ety.effective_end_date
1010 /*
1011 * Element Link:
1012 * Only include those that exist as of QuickPay date earned.
1013 */
1014 and elk.element_link_id = ent.element_link_id
1015 and pya.date_earned between elk.effective_start_date
1016 and elk.effective_end_date
1017 /*
1018 * Element Entry:
1019 * Do not include balance adjustment, replacement adjustment
1020 * or additive adjustment.
1021 */
1022 and ent.entry_type not in ('B', 'A', 'R')
1023 and ent.assignment_id = asa.assignment_id
1024 and ent.effective_start_date <= pya.date_earned
1025 and ent.effective_end_date >= decode(ety.proration_group_id, null, pya.date_earned,
1026 pay_interpreter_pkg.prorate_start_date
1027 (asa.assignment_action_id,
1028 ety.proration_group_id
1029 ))
1030 /*
1031 * Non-recurring entries can only be included if they have not
1032 * been processed.
1033 */
1034 and ( ( ( (ety.processing_type = 'N'
1035 )
1036 /*
1037 * Recurring, additional or override entries can only be
1038 * included if they have not been processed. (These types of
1039 * recurring entry are handled as if they were non-recurring.)
1040 */
1041 or ( ety.processing_type = 'R'
1042 and ent.entry_type <> 'E'
1043 )
1044 )
1045 and (not exists (select null
1046 from pay_run_results pr1
1047 where pr1.source_id = ent.element_entry_id
1048 and pr1.source_type = 'E'
1049 and pr1.status in ('P', 'PA')
1050 and not exists (select ''
1051 from pay_run_results pr2
1052 where pr2.source_id = pr1.run_result_id
1053 and pr2.source_type = 'R'
1054 )
1055 )
1056 )
1057 )
1058 /*
1059 * Include other recurring entries.
1060 * i.e. Those which are not additional or overrides entries.
1061 */
1062 or ( ety.processing_type = 'R'
1063 and ent.entry_type = 'E'
1064 )
1065 )
1066 /*
1067 * Payroll Action:
1068 * Ensure the action is for a QuickPay Run.
1069 */
1070 and pya.action_type = 'Q'
1071 and pya.payroll_action_id = asa.payroll_action_id
1072 /*
1073 * Assignment Action:
1074 */
1075 and asa.assignment_action_id = p_assignment_action_id;
1076 --
1077 hr_utility.set_location(v_proc, 7);
1078 --
1079 -- If we are validating then perform the rollback.
1080 --
1081 if p_validate then
1082 --
1083 -- Issue the rollback.
1084 --
1085 rollback to bulk_default_ins_pay_qpi;
1086 end if;
1087 --
1088 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1089 end bulk_default_ins;
1090 --
1091 -- ----------------------------------------------------------------------------
1092 -- |---------------------------------< del >----------------------------------|
1093 -- ----------------------------------------------------------------------------
1094 --
1095 procedure del
1096 (p_rec in pay_quickpay_inclusions%ROWTYPE
1097 ,p_validate in boolean default false
1098 ) is
1099 --
1100 v_proc varchar2(30) := g_package||'del';
1101 --
1102 Begin
1103 hr_utility.set_location('Entering:'|| v_proc, 5);
1104 --
1105 -- We are deleting using the primary key therefore
1106 -- we must ensure that the argument value is NOT null.
1107 --
1108 if (p_rec.element_entry_id is null or
1109 p_rec.assignment_action_id is null
1110 ) then
1111 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1112 hr_utility.set_message_token('PROCEDURE', v_proc);
1113 hr_utility.set_message_token('STEP','5');
1114 hr_utility.raise_error;
1115 else
1116 --
1117 -- Determine if the business process is to be validated.
1118 --
1119 if p_validate then
1120 --
1121 -- Issue the savepoint.
1122 --
1123 SAVEPOINT del_pay_qpi;
1124 end if;
1125 --
1126 -- We must lock the row which we need to delete.
1127 --
1128 lck
1129 (p_rec.element_entry_id
1130 ,p_rec.assignment_action_id
1131 );
1132 --
1133 -- Call the supporting delete validate operation
1134 --
1135 delete_validate(p_rec);
1136 --
1137 -- Delete the row.
1138 --
1139 delete_dml(p_rec);
1140
1141 --
1142 -- If we are validating then raise the Validate_Enabled exception
1143 --
1144 if p_validate then
1145 raise hr_api.validate_enabled;
1146 end if;
1147 end if;
1148 --
1149 hr_utility.set_location(' Leaving:'||v_proc, 10);
1150 exception
1151 when hr_api.validate_enabled Then
1152 --
1153 -- As the Validate_Enabled exception has been raised
1154 -- we must rollback to the savepoint
1155 --
1156 ROLLBACK TO del_pay_qpi;
1157 end del;
1158 --
1159 -- ----------------------------------------------------------------------------
1160 -- |---------------------------------< del >----------------------------------|
1161 -- ----------------------------------------------------------------------------
1162 --
1163 procedure del
1164 (p_element_entry_id in number
1165 ,p_assignment_action_id in number
1166 ,p_validate in boolean default false
1167 ) is
1168 --
1169 v_rec pay_quickpay_inclusions%ROWTYPE;
1170 v_proc varchar2(72) := g_package||'del';
1171 --
1172 begin
1173 hr_utility.set_location('Entering:'|| v_proc, 5);
1174 --
1175 -- As the delete procedure accepts a plsql record structure we do need to
1176 -- convert the arguments into the record structure.
1177 -- We don't need to call the supplied conversion argument routine as we
1178 -- only need a few attributes.
1179 --
1180 v_rec.element_entry_id := p_element_entry_id;
1181 v_rec.assignment_action_id := p_assignment_action_id;
1182 --
1183 -- Having converted the arguments into a plsql record
1184 -- structure we must call the entity version of del.
1185 --
1186 del(v_rec, p_validate);
1187 --
1188 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1189 end del;
1190 --
1191 end pay_qpi_api;
[Home] [Help]