1 Package Body pay_qpe_api as
2 /* $Header: pyqperhi.pkb 115.2 2004/04/22 07:16 swinton noship $ */
3 --
4 -- Current record structure definition
5 --
6 g_old_rec pay_quickpay_exclusions%ROWTYPE;
7 --
8 -- Global package name
9 --
10 g_package varchar2(30) := ' pay_qpe_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'
64 hr_utility.set_location('Entering:'|| v_proc, 5);
61 and pya.payroll_action_id = asa.payroll_action_id
62 and asa.assignment_action_id = p_assignment_action_id;
63 begin
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 exclude 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, 'PAY_33738_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 exclude 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, 'PAY_33696_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 Exclusion 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) A run result exists for the element entry id/assignment action id
132 --
133 -- Access Status:
134 -- Private to this package.
135 --
136 -- {End Of Comments}
137 --
138 procedure validate_ele_ent_id
139 (p_rec in pay_quickpay_exclusions%ROWTYPE
140 ) is
141 --
142 v_exists varchar2(1);
143 v_date_earned pay_payroll_actions.date_earned%TYPE;
144 v_proc varchar2(72) := g_package||'validate_ele_ent_id';
145 --
146 cursor cur_exists is
147 select 'Y'
148 from dual
149 where exists (select 'Y'
150 from pay_element_entries_f
151 where element_entry_id = p_rec.element_entry_id);
152 --
153 cursor cur_date is
154 select pya.date_earned
155 from
156 pay_element_types_f et
157 , pay_element_links_f el
158 , pay_element_entries_f ent
159 , pay_payroll_actions pya
160 , pay_assignment_actions aga
161 where ent.element_entry_id = p_rec.element_entry_id
162 and ent.effective_start_date <= pya.date_earned
163 and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
164 pay_interpreter_pkg.prorate_start_date
165 (aga.assignment_action_id,
166 et.proration_group_id
167 ))
168 and ent.element_link_id = el.element_link_id
169 and el.element_type_id = et.element_type_id
170 and pya.date_earned between el.effective_start_date
171 and el.effective_end_date
172 and pya.date_earned between et.effective_start_date
173 and et.effective_end_date
174 and pya.payroll_action_id = aga.payroll_action_id
175 and aga.assignment_action_id = p_rec.assignment_action_id;
176 --
177 cursor cur_same is
178 select 'Y'
179 from
180 pay_element_types_f et
181 , pay_element_links_f el
182 , pay_element_entries_f ent
183 , pay_payroll_actions pya
184 , pay_assignment_actions aga
185 where ent.element_entry_id = p_rec.element_entry_id
186 and ent.assignment_id = aga.assignment_id
187 and ent.effective_start_date <= pya.date_earned
188 and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
189 pay_interpreter_pkg.prorate_start_date
190 (aga.assignment_action_id,
194 and el.element_type_id = et.element_type_id
191 et.proration_group_id
192 ))
193 and ent.element_link_id = el.element_link_id
195 and pya.date_earned between el.effective_start_date
196 and el.effective_end_date
197 and pya.date_earned between et.effective_start_date
198 and et.effective_end_date
199 and pya.payroll_action_id = aga.payroll_action_id
200 and aga.assignment_action_id = p_rec.assignment_action_id;
201 --
202 cursor cur_rr_exists (
203 p_assignment_action_id in number,
204 p_element_entry_id in number
205 ) is
206 select 'Y'
207 from pay_assignment_actions asgt_act,
208 pay_run_results rr
209 where asgt_act.assignment_action_id = rr.assignment_action_id
210 and rr.source_id = p_element_entry_id
211 and rr.source_type in ('E','I')
212 and (asgt_act.assignment_action_id = p_assignment_action_id
213 or asgt_act.source_action_id = p_assignment_action_id);
214 --
215 begin
216 hr_utility.set_location('Entering:'|| v_proc, 5);
217 --
218 -- Check that the element entry exists with id p_element_entry_id
219 --
220 open cur_exists;
221 fetch cur_exists into v_exists;
222 if cur_exists%notfound then
223 close cur_exists;
224 -- Error: You have tried to exclude a non-existent element entry for
225 -- QuickPay. The p_rec.element_entry_id (or element_entry_id) argument has
226 -- been set to an incorrect value.
227 hr_utility.set_message(801, 'PAY_34808_QPAY_NO_ELE_ENT');
228 hr_utility.raise_error;
229 end if;
230 close cur_exists;
231 hr_utility.set_location(v_proc, 6);
232 --
233 -- Check the element entry exists as of the QuickPay Run
234 -- date_earned.
235 --
236 open cur_date;
237 fetch cur_date into v_date_earned;
238 if cur_date%notfound then
239 close cur_date;
240 -- Error: You have tried to exclude an element entry for QuickPay that
241 -- does not exist at Date Earned. You can only exclude element entries
242 -- that are date effective at this date.
243 hr_utility.set_message(801, 'PAY_34637_QPAY_NO_ELE_D_EARN');
244 hr_utility.raise_error;
245 end if;
246 close cur_date;
247 hr_utility.set_location(v_proc, 7);
248 --
249 -- Check element entry must be for the same
250 -- assignment as the assignment action
251 --
252 open cur_same;
253 fetch cur_same into v_exists;
254 if cur_same%notfound then
255 close cur_same;
256 -- Error: You have tried to exclude an element entry for QuickPay that is
257 -- not for the same assignment as defined for the QuickPay run.
258 hr_utility.set_message(801, 'PAY_34517_QPAY_ELE_NOT_ASG');
259 hr_utility.raise_error;
260 end if;
261 close cur_same;
262 hr_utility.set_location(v_proc, 8);
263 hr_utility.set_location(v_proc, 9);
264 hr_utility.set_location(v_proc, 10);
265 --
266 -- Do not allow an entry to be excluded from a QP if the QP has been
267 -- processed and a run result exists for that assignment action/element entry
268 -- id
269 --
270 open cur_rr_exists(
271 p_rec.assignment_action_id,
272 p_rec.element_entry_id
273 );
274 fetch cur_rr_exists into v_exists;
275 if cur_rr_exists%found then
276 -- A run result exists for this element entry/assignment action
277 -- Therefore, this element entry cannot be excluded for the QuickPay
278 hr_utility.set_message(801, 'PAY_33922_QPAY_EXCL_RR_EXISTS');
279 hr_utility.raise_error;
280 end if;
281 close cur_rr_exists;
282 --
283 hr_utility.set_location(' Leaving:'|| v_proc, 11);
284 --
285 end validate_ele_ent_id;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |-------------------------< validate_row_exists >--------------------------|
289 -- ----------------------------------------------------------------------------
290 -- {Start Of Comments}
291 --
292 -- Description:
293 -- Validates that a Quickpay Exclusion does not exist. Used in insert
294 -- validation.
295 --
296 -- Pre Conditions:
297 -- None.
298 --
299 -- In Arguments:
300 -- p_rec is the QuickPay Exclusion record to check for.
301 --
302 -- Post Success:
303 -- This procedure ends successfully if the the QuickPay Exclusion does
304 -- not already exist in the pay_quickpay_exclusions table.
305 --
306 -- Post Failure:
307 -- Raises an error if the Quickpay Exclusion p_rec already exists in the
308 -- database.
309 --
310 -- Access Status:
311 -- Private to this package.
312 --
313 -- {End Of Comments}
314 --
315 procedure validate_row_exists
316 (p_rec in pay_quickpay_exclusions%ROWTYPE
317 ) is
318 --
319 v_exists varchar2(1);
320 v_proc varchar2(72) := g_package||'validate_row_exists';
321 --
322 cursor cur_exists is
323 select 'Y'
324 from pay_quickpay_exclusions
325 where element_entry_id = p_rec.element_entry_id
326 and assignment_action_id = p_rec.assignment_action_id;
327 begin
328 hr_utility.set_location('Entering:'|| v_proc, 5);
329 --
330 -- Check that quickpay exclusion does not exist.
331 --
335 close cur_exists;
332 open cur_exists;
333 fetch cur_exists into v_exists;
334 if cur_exists%found then
336 -- Error: You have tried to exclude the same element entry more than once
337 -- from the QuickPay run.
338 hr_utility.set_message(801, 'PAY_33373_QPAY_ELE_EXCLUDED');
339 hr_utility.raise_error;
340 end if;
341 close cur_exists;
342 hr_utility.set_location(' Leaving:'|| v_proc, 10);
343 end validate_row_exists;
344 --
345 -- ----------------------------------------------------------------------------
346 -- |--------------------------< validate_act_cmp >----------------------------|
347 -- ----------------------------------------------------------------------------
348 -- {Start Of Comments}
349 --
350 -- Description:
351 -- Checks that the payroll action does not have a current task set to not
352 -- null. This procedure is used in insert and delete validation.
353 --
354 -- Pre Conditions:
355 -- A QuickPay Run assignment process already exists in pay_assignment_actions
356 -- with an id of p_assignment_action_id.
357 --
358 -- In Arguments:
359 -- p_assignment_action_id is the assignment_action_id on the Quickpay
360 -- Exclusion row.
361 --
362 -- Post Success:
363 -- Completes successfully if the payroll process has a null current_task.
364 --
365 -- Post Failure:
366 -- Raises an error if the payroll process current_task is not null.
367 --
368 -- Access Status:
369 -- Private to this package.
370 --
371 -- {End Of Comments}
372 --
373 procedure validate_act_cmp
374 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
375 ) is
376 --
377 v_exists varchar2(1);
378 v_proc varchar2(72) := g_package||'validate_act_cmp';
379 --
380 -- Enhancement 3368211
381 -- No longer require cur_aga_act for quickpay exclusions...
382 /*
383 cursor cur_aga_act is
384 select 'Y'
385 from pay_assignment_actions
386 where assignment_action_id = p_assignment_action_id
387 and action_status in ('C', 'S');
388 */
389 --
390 --
391 cursor cur_pya_act is
392 select 'Y'
393 from pay_payroll_actions pya
394 , pay_assignment_actions aga
395 where aga.assignment_action_id = p_assignment_action_id
396 and pya.payroll_action_id = aga.payroll_action_id
397 and pya.current_task is not null;
398 begin
399 hr_utility.set_location('Entering:'|| v_proc, 5);
400 --
401 -- Check that assignment_action does not have a status of Complete.
402 --
403 -- Enhancement 3368211
404 -- Removed this check, no longer needed for quickpay exclusions...
405 /*
406 open cur_aga_act;
407 fetch cur_aga_act into v_exists;
408 if cur_aga_act%found then
409 close cur_aga_act;
410 -- Error: You have tried to include or exclude an element entry for a
411 -- QuickPay run that has completed successfully. To do this you must
412 -- change the assignment process status to Marked for Retry.
413 hr_utility.set_message(801, 'HR_7239_QPAY_ELE_MARK');
414 hr_utility.raise_error;
415 end if;
416 close cur_aga_act;
417 --
418 */
419 hr_utility.set_location(v_proc, 6);
420 --
421 -- Check that payroll_action does not have
422 -- current_task set to a not null value.
423 --
424 open cur_pya_act;
425 fetch cur_pya_act into v_exists;
426 if cur_pya_act%found then
427 close cur_pya_act;
428 -- Error: You have tried to include or exclude an element entry from a
429 -- QuickPay run while the run is processing.
430 hr_utility.set_message(801, 'HR_7240_QPAY_ELE_RUNNING');
431 hr_utility.raise_error;
432 end if;
433 close cur_pya_act;
434 hr_utility.set_location(' Leaving:'|| v_proc, 10);
435 end validate_act_cmp;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |------------------------------< insert_dml >------------------------------|
439 -- ----------------------------------------------------------------------------
440 -- {Start Of Comments}
441 --
442 -- Description:
443 -- This procedure controls the actual dml insert logic. The function of this
444 -- procedure is to insert the row into the schema.
445 --
446 -- Pre Conditions:
447 -- This is an internal private procedure which must be called from the ins
448 -- procedure and must have all mandatory arguments set.
449 --
450 -- In Arguments:
451 -- A Pl/Sql record structure.
452 --
453 -- Post Success:
454 -- The specified row will be inserted into the schema
455 -- (pay_quickpay_exclusions table).
456 --
457 -- Post Failure:
458 -- If an error occurs a standard Oracle error will be raised.
459 --
460 -- Developer Implementation Notes:
461 -- None.
462 --
463 -- Access Status:
464 -- Internal Development Use Only.
465 --
466 -- {End Of Comments}
467 --
468 procedure insert_dml(p_rec in out nocopy pay_quickpay_exclusions%ROWTYPE) is
469 --
470 v_proc varchar2(72) := g_package||'insert_dml';
471 --
472 begin
473 hr_utility.set_location('Entering:'||v_proc, 5);
474 --
475 -- Insert the row into: pay_quickpay_exclusions
476 --
477 insert into pay_quickpay_exclusions
478 (element_entry_id
479 ,assignment_action_id
480 ,created_by
481 ,creation_date
482 ,last_update_date
483 ,last_updated_by
484 ,last_update_login
485 )
486 values
490 ,sysdate
487 (p_rec.element_entry_id
488 ,p_rec.assignment_action_id
489 ,fnd_global.user_id
491 ,sysdate
492 ,fnd_global.user_id
493 ,fnd_global.login_id
494 );
495 --
496 hr_utility.set_location(' Leaving:'||v_proc, 10);
497 end insert_dml;
498 --
499 -- ----------------------------------------------------------------------------
500 -- |------------------------------< delete_dml >------------------------------|
501 -- ----------------------------------------------------------------------------
502 -- {Start Of Comments}
503 --
504 -- Description:
505 -- This procedure controls the actual dml delete logic. The function of this
506 -- procedure is to delete the specified row from the schema using the
507 -- primary key.
508 --
509 -- Pre Conditions:
510 -- This is an internal private procedure which must be called from the del
511 -- procedure.
512 --
513 -- In Arguments:
514 -- A Pl/Sql record structure.
515 --
516 -- Post Success:
517 -- The specified row will be delete from the schema.
518 --
519 -- Post Failure:
520 -- If an error occurs a standard Oracle error will be raised.
521 --
522 -- Developer Implementation Notes:
523 -- None.
524 --
525 -- Access Status:
526 -- Internal Development Use Only.
527 --
528 -- {End Of Comments}
529 --
530 Procedure delete_dml(p_rec in pay_quickpay_exclusions%ROWTYPE) is
531 --
532 v_proc varchar2(72) := g_package||'delete_dml';
533 --
534 Begin
535 hr_utility.set_location('Entering:'|| v_proc, 5);
536 --
537 -- Delete the pay_quickpay_exclusions row.
538 --
539 delete from pay_quickpay_exclusions
540 where element_entry_id = p_rec.element_entry_id
541 and assignment_action_id = p_rec.assignment_action_id;
542 --
543 If sql%NOTFOUND then
544 --
545 -- The row to be deleted was NOT found therefore a serious
546 -- error has occurred which MUST be reported.
547 --
548 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
549 hr_utility.set_message_token('PROCEDURE', v_proc);
550 hr_utility.set_message_token('STEP','5');
551 hr_utility.raise_error;
552 End If;
553 --
554 hr_utility.set_location(' Leaving:'|| v_proc, 10);
555 End delete_dml;
556 --
557 -- ----------------------------------------------------------------------------
558 -- |---------------------------------< lck >----------------------------------|
559 -- ----------------------------------------------------------------------------
560 --
561 procedure lck
562 (p_element_entry_id in number
563 ,p_assignment_action_id in number
564 ) is
565 --
566 -- Cursor selects the 'current' row from the HR Schema
567 --
568 Cursor C_Sel1 is
569 select *
570 from pay_quickpay_exclusions
571 where element_entry_id = p_element_entry_id
572 and assignment_action_id = p_assignment_action_id
573 for update nowait;
574 --
575 v_proc varchar2(72) := g_package||'lck';
576 --
577 Begin
578 hr_utility.set_location('Entering:'|| v_proc, 5);
579 --
580 -- The primary key exists therefore we must now attempt to lock the
581 -- row and check the object version numbers.
582 --
583 Open C_Sel1;
584 Fetch C_Sel1 Into g_old_rec;
585 If C_Sel1%notfound then
586 Close C_Sel1;
587 --
588 -- The primary key is invalid therefore we must error
589 --
590 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
591 hr_utility.raise_error;
592 End If;
593 Close C_Sel1;
594 --
595 hr_utility.set_location(' Leaving:'|| v_proc, 10);
596 --
597 -- We need to trap the ORA LOCK exception
598 --
599 Exception
600 When HR_api.Object_Locked then
601 --
602 -- The object is locked therefore we need to supply a meaningful
603 -- error message.
604 --
605 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
606 hr_utility.set_message_token('TABLE_NAME', 'PAY_QUICKPAY_EXCLUSIONS');
607 hr_utility.raise_error;
608 End lck;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |-----------------------------< convert_args >-----------------------------|
612 -- ----------------------------------------------------------------------------
613 -- {Start Of Comments}
614 --
615 -- Description:
616 -- This function is used to turn attribute arguments into the record
617 -- structure.
618 --
619 -- Pre Conditions:
620 -- This is a private function and can only be called from the ins or upd
621 -- attribute processes.
622 --
623 -- In Arguments:
624 -- The individual attributes of the QuickPay Exclusion. p_element_entry_id
625 -- and p_assignment_action_id.
626 --
627 -- Post Success:
628 -- A record structure will be returned of type
629 -- pay_quickpay_exclusions%ROWTYPE.
630 --
631 -- Post Failure:
632 -- No direct error handling is required within this function. Any possible
633 -- errors within this function will be a PL/SQL value error due to conversion
634 -- of datatypes or data lengths.
635 --
636 -- Developer Implementation Notes:
637 -- None.
638 --
639 -- Access Status:
640 -- Internal Development Use Only.
641 --
642 -- {End Of Comments}
643 --
644 function convert_args
645 (p_element_entry_id in number
646 ,p_assignment_action_id in number
650 v_proc varchar2(72) := g_package||'convert_args';
647 ) return pay_quickpay_exclusions%ROWTYPE is
648 --
649 v_rec pay_quickpay_exclusions%ROWTYPE;
651 --
652 Begin
653 --
654 hr_utility.set_location('Entering:'|| v_proc, 5);
655 --
656 -- Convert arguments into local l_rec structure.
657 --
658 v_rec.element_entry_id := p_element_entry_id;
659 v_rec.assignment_action_id := p_assignment_action_id;
660 --
661 -- Return the plsql record structure.
662 --
663 hr_utility.set_location(' Leaving:'|| v_proc, 10);
664 Return(v_rec);
665 --
666 End convert_args;
667 --
668 -- ----------------------------------------------------------------------------
669 -- |---------------------------< insert_validate >----------------------------|
670 -- ----------------------------------------------------------------------------
671 -- {Start Of Comments}
672 --
673 -- Description:
674 -- This procedure controls the execution of all insert business rules
675 -- validation.
676 --
677 -- Pre Conditions:
678 -- This private procedure is called from ins procedure.
679 --
680 -- In Arguments:
681 -- A Pl/Sql record structure.
682 --
683 -- Post Success:
684 -- Processing continues if no validation errors are found.
685 --
686 -- Post Failure:
687 -- If a business rules fails the error will not be handled by this procedure
688 -- unless explicitly coded.
689 --
690 -- Access Status:
691 -- Internal Development Use Only.
692 --
693 -- {End Of Comments}
694 --
695 procedure insert_validate(p_rec in pay_quickpay_exclusions%ROWTYPE) is
696 --
697 v_proc varchar2(72) := g_package||'insert_validate';
698 --
699 begin
700 hr_utility.set_location('Entering:'|| v_proc, 5);
701 --
702 -- Call all supporting business operations
703 --
704 -- Validate that the assignment action actually exists and it
705 -- is for a 'QuickPay Run' Payroll Process.
706 --
707 validate_asg_act_id(p_assignment_action_id => p_rec.assignment_action_id);
708 hr_utility.set_location(v_proc, 6);
709 --
710 -- Validate that corresponding assignment action's Payroll Process is not
711 -- being processed.
712 --
713 validate_act_cmp(p_assignment_action_id => p_rec.assignment_action_id);
714 hr_utility.set_location(v_proc, 7);
715 --
716 -- Validate the element entry.
717 --
718 validate_ele_ent_id(p_rec => p_rec);
719 hr_utility.set_location(v_proc, 8);
720 --
721 -- Validate that this quickpay exclusion does not already exist.
722 --
723 validate_row_exists(p_rec => p_rec);
724 hr_utility.set_location(v_proc, 9);
725 --
726 hr_utility.set_location(' Leaving:'|| v_proc, 10);
727 end insert_validate;
728 --
729 -- ----------------------------------------------------------------------------
730 -- |---------------------------< delete_validate >----------------------------|
731 -- ----------------------------------------------------------------------------
732 -- {Start Of Comments}
733 --
734 -- Description:
735 -- This procedure controls the execution of all delete business rules
736 -- validation.
737 --
738 -- Pre Conditions:
739 -- This private procedure is called from del procedure.
740 --
741 -- In Arguments:
742 -- A Pl/Sql record structure.
743 --
744 -- Post Success:
745 -- Processing continues is no delete validation errors.
746 --
747 -- Post Failure:
748 -- If a business rules fails the error will not be handled by this procedure
749 -- unless explicitly coded.
750 --
751 -- Access Status:
752 -- Internal Development Use Only.
753 --
754 -- {End Of Comments}
755 --
756 procedure delete_validate(p_rec in pay_quickpay_exclusions%ROWTYPE) is
757 --
758 v_proc varchar2(72) := g_package||'delete_validate';
759 --
760 begin
761 hr_utility.set_location('Entering:'|| v_proc, 5);
762 --
763 -- Validate that the corresponding assignment action's payroll action does
764 -- not have a status of Processing.
765 --
766 validate_act_cmp(p_assignment_action_id => p_rec.assignment_action_id);
767 --
768 hr_utility.set_location(' Leaving:'|| v_proc, 10);
769 end delete_validate;
770 --
771 -- ----------------------------------------------------------------------------
772 -- |---------------------------------< ins >----------------------------------|
773 -- ----------------------------------------------------------------------------
774 --
775 Procedure ins
776 (
777 p_rec in out nocopy pay_quickpay_exclusions%ROWTYPE,
778 p_validate in boolean default false
779 ) is
780 --
781 v_proc varchar2(72) := g_package||'ins';
782 --
783 Begin
784 hr_utility.set_location('Entering:'|| v_proc, 5);
785 --
786 -- Determine if the business process is to be validated.
787 --
788 If p_validate then
789 --
790 -- Issue the savepoint.
791 --
792 SAVEPOINT ins_pay_qpe;
793 End If;
794 --
795 -- Call the supporting insert validate operations
796 --
797 insert_validate(p_rec);
798 --
799 -- Insert the row
800 --
801 insert_dml(p_rec);
802 --
803 -- If we are validating then raise the Validate_Enabled exception
804 --
805 if p_validate then
806 raise hr_api.validate_enabled;
807 end if;
808 --
809 hr_utility.set_location(' Leaving:'||v_proc, 10);
810 exception
811 when hr_api.validate_enabled then
812 --
813 -- As the Validate_Enabled exception has been raised
814 -- we must rollback to the savepoint
815 --
816 ROLLBACK TO ins_pay_qpe;
817 end ins;
818 --
819 -- ----------------------------------------------------------------------------
820 -- |---------------------------------< ins >----------------------------------|
821 -- ----------------------------------------------------------------------------
822 --
823 procedure ins
824 (p_element_entry_id in number
825 ,p_assignment_action_id in number
826 ,p_validate in boolean default false
827 ) is
828 --
829 v_rec pay_quickpay_exclusions%ROWTYPE;
830 v_proc varchar2(72) := g_package||'ins';
831 --
832 Begin
833 hr_utility.set_location('Entering:'|| v_proc, 5);
834 --
835 -- Call conversion function to turn arguments into the
836 -- pl/sql record structure.
837 --
838 v_rec :=
839 convert_args
840 (p_element_entry_id => p_element_entry_id
841 ,p_assignment_action_id => p_assignment_action_id
842 );
843 --
844 -- Having converted the arguments into a pl/sql record structure
845 -- call the entity version of ins.
846 --
847 ins(v_rec, p_validate);
848 --
849 hr_utility.set_location(' Leaving:'|| v_proc, 10);
850 End ins;
851 --
852 -- ----------------------------------------------------------------------------
853 -- |---------------------------------< del >----------------------------------|
854 -- ----------------------------------------------------------------------------
855 --
856 procedure del
857 (p_rec in pay_quickpay_exclusions%ROWTYPE
858 ,p_validate in boolean default false
859 ) is
860 --
861 v_proc varchar2(30) := g_package||'del';
862 --
863 Begin
864 hr_utility.set_location('Entering:'|| v_proc, 5);
865 --
866 -- We are deleting using the primary key therefore
867 -- we must ensure that the argument value is NOT null.
868 --
869 if (p_rec.element_entry_id is null or
870 p_rec.assignment_action_id is null
871 ) then
872 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
873 hr_utility.set_message_token('PROCEDURE', v_proc);
874 hr_utility.set_message_token('STEP','5');
875 hr_utility.raise_error;
876 else
877 --
878 -- Determine if the business process is to be validated.
879 --
880 if p_validate then
881 --
882 -- Issue the savepoint.
883 --
884 SAVEPOINT del_pay_qpe;
885 end if;
886 --
887 -- We must lock the row which we need to delete.
888 --
889 lck
890 (p_rec.element_entry_id
891 ,p_rec.assignment_action_id
892 );
893 --
894 -- Call the supporting delete validate operation
895 --
896 delete_validate(p_rec);
897 --
898 -- Delete the row.
899 --
900 delete_dml(p_rec);
901
902 --
903 -- If we are validating then raise the Validate_Enabled exception
904 --
905 if p_validate then
906 raise hr_api.validate_enabled;
907 end if;
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 del_pay_qpe;
918 end del;
919 --
920 -- ----------------------------------------------------------------------------
921 -- |---------------------------------< del >----------------------------------|
922 -- ----------------------------------------------------------------------------
923 --
924 procedure del
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_exclusions%ROWTYPE;
931 v_proc varchar2(72) := g_package||'del';
932 --
933 begin
934 hr_utility.set_location('Entering:'|| v_proc, 5);
935 --
936 -- As the delete procedure accepts a plsql record structure we do need to
937 -- convert the arguments into the record structure.
938 -- We don't need to call the supplied conversion argument routine as we
939 -- only need a few attributes.
940 --
941 v_rec.element_entry_id := p_element_entry_id;
942 v_rec.assignment_action_id := p_assignment_action_id;
943 --
944 -- Having converted the arguments into a plsql record
945 -- structure we must call the entity version of del.
946 --
947 del(v_rec, p_validate);
948 --
949 hr_utility.set_location(' Leaving:'|| v_proc, 10);
950 end del;
951 --
952 end pay_qpe_api;
[Home] [Help]