1 Package Body pay_qpa_api as
2 /* $Header: pyqpxrhi.pkb 115.5 2002/12/06 15:10:04 swinton noship $ */
3 --
4 -- Current record structure definition
5 --
6 g_old_rec g_rec_type;
7 --
8 -- Global package name
9 --
10 g_package varchar2(33) := ' pay_qpa_api.';
11 --
12 -- Global api dml status
13 --
14 g_api_dml boolean;
15
16
17 ---------------- < qppsassact > ----------------------
18 /*
19
20 NAME
21 qpppassact - Insert a QuickPay Pay-slip assg action.
22 DESCRIPTION
23 Process a QuickPay Payslip action.
24 NOTES
25 This procedure is meant to be called via the QuickPay form.
26 */
27 procedure qppsassact
28 (
29 p_payroll_action_id in number, -- of QuickPay Payslip.
30 p_assignment_action_id out nocopy number,
31 p_object_version_number out nocopy number
32 ) is
33
34 l_assignment_id number;
35 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
36 l_locking_action_id number;
37 l_qpp_locked_action_id number;
38 l_qp_locked_action_id number;
39 l_locked_action_id number;
40 l_tax_unit_id number;
41
42 cursor c_qpaction is
43 select act.assignment_action_id
44 from pay_payroll_actions pac,
45 pay_assignment_actions act
46 where pac.payroll_action_id = p_payroll_action_id
47 and act.payroll_action_id = pac.target_payroll_action_id
48 and act.source_action_id is null;
49
50 cursor c_asgid is
51 select act.assignment_id
52 from pay_assignment_actions act,
53 pay_payroll_actions pac
54 where pac.payroll_action_id = p_payroll_action_id
55 and act.payroll_action_id = pac.target_payroll_action_id
56 and act.source_action_id is null;
57
58 cursor c_qppaction is
59 select pac.assignment_action_id,pac.tax_unit_id
60 from pay_assignment_actions pac
61 where pac.payroll_action_id = (select ppa.payroll_action_id
62 from pay_payroll_actions ppa
63 where ppa.target_payroll_action_id = (select ppa.target_payroll_action_id
64 from pay_payroll_actions ppa
65 where ppa.payroll_action_id = p_payroll_action_id)
66 and ppa.action_type = 'U'
67 and ppa.action_status = 'C');
68
69 cursor c_qparcassact is
70 select pac.assignment_action_id
71 from pay_assignment_actions pac
72 where pac.payroll_action_id =p_payroll_action_id;
73
74 cursor c_next_assact is
75 select pay_assignment_actions_s.nextval
76 from sys.dual;
77
78 begin
79 --
80 -- We have to get the assignment_id
81 -- of the assignment that is going
82 -- to be archived, so it may be passed on.
83 --
84 hr_utility.set_location('Entering :qppsassact',10);
85
86
87 open c_asgid;
88 fetch c_asgid into l_assignment_id;
89 if l_assignment_id is not null then
90 hr_utility.set_location('qppsassact',30);
91 --First find the tax unit id of the pre-payment process
92 open c_qppaction;
93 fetch c_qppaction into l_qpp_locked_action_id,l_tax_unit_id;
94 open c_next_assact;
95 fetch c_next_assact into l_assignment_action_id;
96
97 --Insert the new assignment action
98 if c_next_assact%found then
99 if c_qppaction%found then
100 hr_nonrun_asact.insact(l_assignment_action_id,l_assignment_id,p_payroll_action_id,1,l_tax_unit_id,null,'M',null);
101 end if;
102 end if;
103 end if;
104 close c_asgid;
105 --
106
107 open c_qparcassact;
108 fetch c_qparcassact into l_locking_action_id;
109
110 if c_qparcassact%found then
111
112 --Then insert the quick-pay action rows into pay_action_interlocks
113 hr_utility.set_location(' qppsassact :',40);
114 open c_qpaction;
115 fetch c_qpaction into l_qp_locked_action_id;
116 if c_qpaction%found then
117 -- We can now insert interlock row.
118 hr_utility.set_location('qppsassact',50);
119 hr_nonrun_asact.insint(l_locking_action_id,l_qp_locked_action_id);
120 end if;
121 close c_qpaction;
122
123 --Then insert the quick-pay pre-payment action rows
124 hr_utility.set_location(' qppsassact ',55);
125 hr_nonrun_asact.insint(l_locking_action_id,l_qpp_locked_action_id);
126 end if;
127
128
129 hr_utility.set_location(' qppsassact :',60);
130 -- Update the payroll actions table with the
131 -- appropriate date_earned value.
132 hr_utility.set_location('qppsassact',70);
133 update pay_payroll_actions pac
134 set pac.date_earned = (
135 select pa2.date_earned
136 from pay_payroll_actions pa2,
137 pay_assignment_actions act
138 where act.assignment_action_id = l_qpp_locked_action_id
139 and pa2.payroll_action_id = act.payroll_action_id)
140
141 where pac.payroll_action_id =p_payroll_action_id;
142 --
143
144 p_assignment_action_id := l_locking_action_id;
145 p_object_version_number := 1;
146
147 --Close all open cursors
148 close c_qparcassact;
149 close c_next_assact;
150 close c_qppaction;
151
152 --
153 -- update the action_population_status to indicate
154 -- an action has been successfully inserted.
155 hr_utility.set_location('qppsassact',75);
156 update pay_payroll_actions pac
157 set pac.action_population_status = 'C'
158 where pac.payroll_action_id = p_payroll_action_id;
159
160 hr_utility.set_location('Leaving : qppsassact',80);
161
162 end qppsassact;
163 --
164
165 -- ----------------------------------------------------------------------------
166 -- |-------------------------< chk_action_status >----------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This procedure is used check the action_status has only been updated
172 -- from 'C' (for Complete) to 'M' (for Mark for Retry). This is the only
173 -- update which the user is allowed to do.
174 --
175 -- Pre Conditions:
176 -- None.
177 --
178 -- In Arguments:
179 -- p_payroll_action_id is the id of the QuickPay Archival being updated.
180 -- p_old_action_status is set to the existing action_status in the database.
181 -- p_new_action_status is set to the user's proposed new action_status value.
182 --
183 -- Post Success:
184 -- Processing continues if the action_status update is valid and there are
185 -- no restrictive action interlocks.
186 --
187 -- Post Failure:
188 -- An application error is raised if the user is trying to do any other
189 -- update (i.e. not 'C' to 'M'). Also an error is raised if there are any
190 -- action interlocks preventing the update.
191 --
192 -- Access Status:
193 -- Private.
194 --
195 -- {End Of Comments}
196 --
197 procedure chk_action_status
198 (p_payroll_action_id in number
199 ,p_old_action_status in varchar2
200 ,p_new_action_status in varchar2
201 ) is
202 --
203 l_proc varchar2(72) := g_package||'chk_action_status';
204 --
205 begin
206 hr_utility.set_location('Entering:'||l_proc, 5);
207 --
208 -- Check the updated status is from 'C'omplete to 'M'ark for Retry
209 --
210 if (p_old_action_status not in ('C', 'S')) and (p_new_action_status <> 'M') then
211 -- Error: You have tried to enter an invalid status for a completed
212 -- QuickPay run. You can only update a completed assignment process
213 -- status to Marked for Retry.
214 hr_utility.set_message(801, 'HR_7252_QPAY_ONLY_MARK_RETRY');
215 hr_utility.raise_error;
216 end if;
217 hr_utility.set_location(l_proc, 6);
218 --
219 -- Check that this QuickPay Archival can have
220 -- a status of Mark for Retry
221 --
222 py_rollback_pkg.rollback_payroll_action(
223 p_payroll_action_id => p_payroll_action_id,
224 p_rollback_mode => 'RETRY',
225 p_leave_base_table_row => TRUE);
226 --
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 end chk_action_status;
229 --
230 -- ----------------------------------------------------------------------------
231 -- |-----------------------------< chk_cur_task >-----------------------------|
232 -- ----------------------------------------------------------------------------
233 -- {Start Of Comments}
234 --
235 -- Description:
236 -- This procedure is used in update and delete validation to check the
237 -- payroll_action current_task value. The user should not be allowed to
238 -- update any QuickPay Run attribute or delete a QuickPay Run when the
239 -- current_task is not null. (A not null value means a C process is
240 -- still processing the action.)
241 --
242 -- Pre Conditions:
243 -- p_payroll_action_id has already been validated as an id of a Payroll
244 -- Process which exists in the HR schema.
245 --
246 -- In Arguments:
247 -- p_payroll_action_id set to the id of an existing payroll action.
248 --
249 -- Post Success:
250 -- The current_task for this QuickPay Run is null. (Update or delete can
251 -- be allowed to continue, subject to other validation.)
252 --
253 -- Post Failure:
254 -- An application error is raised if the current_task value is not null.
255 --
256 -- Access Status:
257 -- Private.
258 --
259 -- {End Of Comments}
260 --
261 procedure chk_cur_task
262 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
263 ) is
264 --
265 v_exists varchar2(1);
266 l_proc varchar2(72) := g_package||'chk_cur_task';
267 --
268 cursor sel_task is
269 select 'Y'
270 from pay_payroll_actions
271 where payroll_action_id = p_payroll_action_id
272 and current_task is not null;
273 begin
274 hr_utility.set_location('Entering:'|| l_proc, 5);
275 --
276 open sel_task;
277 fetch sel_task into v_exists;
278 if sel_task%found then
279 close sel_task;
280 -- Error: You have tried to update or delete a QuickPay Archival while
281 -- the corresponding payroll process has a status of Processing.
282 hr_utility.set_message(801, 'HR_7256_QPAY_U_PROCESSING');
283 hr_utility.raise_error;
284 end if;
285 close sel_task;
286 --
287 hr_utility.set_location(' Leaving:'|| l_proc, 10);
288 end chk_cur_task;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |----------------------< check_non_updateable_args >-----------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This procedure is used to ensure that non updateable attributes have
297 -- not been updated.
298 --
299 -- Pre Conditions:
300 -- g_old_rec has been populated with details of the values currently in
301 -- the database.
302 --
303 -- In Arguments:
304 -- p_rec has been populated with the updated values the user would like the
305 -- record set to.
306 --
307 -- Post Success:
308 -- Processing continues if all the non updateable attributes have not
309 -- changed.
310 --
311 -- Post Failure:
312 -- An application error is raised if any of the non updatable attributes
313 -- (business_group_id, org_payment_method_id, target_payroll_action_id or
314 -- effective_date) have been altered.
315 --
316 -- Access Status:
317 -- Private.
318 --
319 -- {End Of Comments}
320 --
321 Procedure check_non_updateable_args(p_rec in g_rec_type) is
322 --
323 l_proc varchar2(72) := g_package||'check_non_updateable_args';
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 if nvl(p_rec.business_group_id, hr_api.g_number) <>
329 nvl(g_old_rec.business_group_id, hr_api.g_number) then
330 hr_api.argument_changed_error
331 (p_api_name => l_proc
332 ,p_argument => 'business_group_id'
333 );
334 end if;
335 hr_utility.set_location(l_proc, 6);
336 --
337 if nvl(p_rec.org_payment_method_id, hr_api.g_number) <>
338 nvl(g_old_rec.org_payment_method_id, hr_api.g_number) then
339 hr_api.argument_changed_error
340 (p_api_name => l_proc
341 ,p_argument => 'org_payment_method_id'
342 );
343 end if;
344 hr_utility.set_location(l_proc, 7);
345 --
346 if nvl(p_rec.target_payroll_action_id, hr_api.g_number) <>
347 nvl(g_old_rec.target_payroll_action_id, hr_api.g_number) then
348 hr_api.argument_changed_error
349 (p_api_name => l_proc
350 ,p_argument => 'target_payroll_action_id'
351 );
352 end if;
353 hr_utility.set_location(l_proc, 8);
354 --
355 if nvl(p_rec.effective_date, hr_api.g_date) <>
356 nvl(g_old_rec.effective_date, hr_api.g_date) then
357 hr_api.argument_changed_error
358 (p_api_name => l_proc
359 ,p_argument => 'effective_date'
360 );
361 end if;
362 --
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 end check_non_updateable_args;
365 --
366
367 -- ----------------------------------------------------------------------------
371 --
368 -- |--------------------< chk_target_payroll_action_id >----------------------|
369 -- ----------------------------------------------------------------------------
370 -- {Start Of Comments}
372 -- Description:
373 -- Validates target_payroll_action_id and business_group_id for a QuickPay
374 -- Archival Run Process. Used for insert validation.
375 --
376 -- Pre Conditions:
377 -- None.
378 --
379 -- In Arguments:
380 -- p_target_payroll_action_id is set to the payroll_action_id of QuickPay Run
381 -- associated with the QuickPay Archival.
382 -- p_business_group_id is set to the business group for the QuickPay
383 -- Archival.
384 -- p_effective_date is set to the effective_date for the QuickPay
385 -- Archival.
386 --
387 -- Post Success:
388 -- Processing continues if p_target_payroll_action_id, p_business_group_id
389 -- and p_effective_date are valid.
390 --
391 -- Post Failure:
392 -- An error is raised if any of the following conditions are found:
393 -- 1) target_payroll_action_id, business_group_id or effective_date are
394 -- not null.
395 -- 2) target_payroll_action_id does not exists in pay_payroll_actions for a
396 -- QuickPay Run.
397 -- 3) The associated Assignment Process has a complete status.
398 -- 4) Another QuickPay Archival run already
399 -- interlocks to the QuickPay Run Assignment Process.
400 -- 5) The QuickPay Archival run business_group_id and date_paid
401 -- (effective_date) are not the same as the QuickPay Run
402 -- business_group_id and date_paid.
403 --
404 -- Access Status:
405 -- Private.
406 --
407
408 -- {End Of Comments}
409 --
410 procedure chk_target_payroll_action_id
411 (p_target_payroll_action_id in
412 pay_payroll_actions.target_payroll_action_id%TYPE
413 ,p_business_group_id in pay_payroll_actions.business_group_id%TYPE
414 ,p_effective_date in pay_payroll_actions.effective_date%TYPE
415 ) is
416 --
417 l_exists varchar2(1);
418 l_proc varchar2(72) := g_package||'chk_target_payroll_action_id';
419 --
420 cursor csr_pay_act is
421
422 select 'Y'
423 from pay_payroll_actions pya
424 , pay_assignment_actions aga
425 where pya.payroll_action_id = p_target_payroll_action_id
426 and pya.action_type = 'Q'
427 and aga.payroll_action_id = pya.payroll_action_id
428 and aga.action_status in ('C', 'S');
429 --
430
431 cursor csr_bus_grp is
432 select 'Y'
433 from pay_payroll_actions pya
434 where pya.payroll_action_id = p_target_payroll_action_id
435 and pya.business_group_id = p_business_group_id
436 and pya.effective_date = p_effective_date;
437 --
438 begin
439 hr_utility.set_location('Entering:'|| l_proc, 5);
440 --
441 -- Check mandatory parameters have been set
442 --
443 hr_api.mandatory_arg_error
444
445 (p_api_name => l_proc
446 ,p_argument => 'target_payroll_action_id'
447 ,p_argument_value => p_target_payroll_action_id
448 );
449 hr_api.mandatory_arg_error
450 (p_api_name => l_proc
451 ,p_argument => 'business_group_id'
452 ,p_argument_value => p_business_group_id
453 );
454 hr_api.mandatory_arg_error
455 (p_api_name => l_proc
456 ,p_argument => 'effective_date'
457 ,p_argument_value => p_effective_date
458
459 );
460 hr_utility.set_location(l_proc, 6);
461 --
462 -- Check the target_payroll_action_id exists in pay_payroll_actions
463 -- and it is for a completed QuickPay Run assignment action.
464 --
465 open csr_pay_act;
466 fetch csr_pay_act into l_exists;
467 if csr_pay_act%notfound then
468 close csr_pay_act;
469 -- Error: You have tried to create a QuickPay Archival for a
470 -- target_payroll_action_id that does not exist, or for a QuickPay run
471 -- that does not have the status Complete.
472
473 hr_utility.set_message(801, 'HR_7257_QPAY_U_QRUN_NOT_EXIST');
474 hr_utility.raise_error;
475 end if;
476 close csr_pay_act;
477 hr_utility.set_location(l_proc, 7);
478 --
479 --
480 -- Check the QuickPay Archival run business group and effective_date are the
481 -- same as the QuickPay Run business group and effective_date (date_paid).
482
483 --
484 open csr_bus_grp;
485 fetch csr_bus_grp into l_exists;
486 if csr_bus_grp%notfound then
487 close csr_bus_grp;
488 -- Error: The business_group and date_paid attributes for the QuickPay
489 -- Archival must have the same values as the business_group and
490 -- date_paid for the QuickPay run.
491 hr_utility.set_message(801, 'HR_7260_QPAY_U_SAME_AS_Q');
492 hr_utility.raise_error;
493 end if;
494 close csr_bus_grp;
495 --
496
497 hr_utility.set_location(' Leaving:'|| l_proc, 9);
498 end chk_target_payroll_action_id;
499 --
500 -- ----------------------------------------------------------------------------
501 -- |------------------------< return_api_dml_status >-------------------------|
502 -- ----------------------------------------------------------------------------
503 --
504
505 Function return_api_dml_status Return Boolean Is
506 --
507 l_proc varchar2(72) := g_package||'return_api_dml_status';
508 --
509 Begin
510 hr_utility.set_location('Entering:'||l_proc, 5);
511 --
512 Return (nvl(g_api_dml, false));
513 --
514 hr_utility.set_location(' Leaving:'||l_proc, 10);
515 End return_api_dml_status;
516 --
517 -- ----------------------------------------------------------------------------
518
519 -- |---------------------------< constraint_error >---------------------------|
520 -- ----------------------------------------------------------------------------
521 -- {Start Of Comments}
522 --
523 -- Description:
524 -- This procedure is called when a constraint has been violated (i.e.
525 -- The exception hr_api.check_integrity_violated,
526 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
527 -- hr_api.unique_integrity_violated has been raised).
528 -- The exceptions can only be raised as follows:
529 -- 1) A check constraint can only be violated during an INSERT or UPDATE
530 -- dml operation.
531 -- 2) A parent integrity constraint can only be violated during an
532
533 -- INSERT or UPDATE dml operation.
534 -- 3) A child integrity constraint can only be violated during an
535 -- DELETE dml operation.
536 -- 4) A unique integrity constraint can only be violated during INSERT or
537 -- UPDATE dml operation.
538 --
539 -- Pre Conditions:
540 -- 1) Either hr_api.check_integrity_violated,
541 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
542 -- hr_api.unique_integrity_violated has been raised with the subsequent
543 -- stripping of the constraint name from the generated error message
544 -- text.
545 -- 2) Standalone validation test which correspond with a constraint error.
546
547 --
548 -- In Arguments:
549 -- p_constraint_name is in upper format and is just the constraint name
550 -- (e.g. not prefixed by brackets, schema owner etc).
551 --
552 -- Post Success:
553 -- Development dependant.
554 --
555 -- Post Failure:
556 -- Development dependant.
557 --
558 -- Developer Implementation Notes:
559 -- For each constraint being checked the hr system package failure message
560
561 -- has been generated as a template only. These system error messages should
562 -- be modified as required (i.e. change the system failure message to a user
563 -- friendly defined error message).
564 --
565 -- Access Status:
566 -- Public.
567 --
568 -- {End Of Comments}
569 --
570 Procedure constraint_error
571 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
572 --
573 l_proc varchar2(72) := g_package||'constraint_error';
574
575 --
576 Begin
577 hr_utility.set_location('Entering:'||l_proc, 5);
578 --
579 If (p_constraint_name = 'PAY_PAYRACT_ACTION_POPULAT_CHK') Then
580 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
581 hr_utility.set_message_token('PROCEDURE', l_proc);
582 hr_utility.set_message_token('STEP','5');
583 hr_utility.raise_error;
584 ElsIf (p_constraint_name = 'PAY_PAYRACT_ACTION_STATUS_CHK') Then
585 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
586 hr_utility.set_message_token('PROCEDURE', l_proc);
587 hr_utility.set_message_token('STEP','10');
588
589 hr_utility.raise_error;
590 ElsIf (p_constraint_name = 'PAY_PAYRACT_ACTION_TYPE_CHK') Then
591 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
592 hr_utility.set_message_token('PROCEDURE', l_proc);
593 hr_utility.set_message_token('STEP','15');
594 hr_utility.raise_error;
595 ElsIf (p_constraint_name = 'PAY_PAYRACT_CURRENT_TASK_CHK') Then
596 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
597 hr_utility.set_message_token('PROCEDURE', l_proc);
598 hr_utility.set_message_token('STEP','20');
599 hr_utility.raise_error;
600 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK1') Then
601 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
602
603 hr_utility.set_message_token('PROCEDURE', l_proc);
604 hr_utility.set_message_token('STEP','25');
605 hr_utility.raise_error;
606 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK2') Then
607 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
608 hr_utility.set_message_token('PROCEDURE', l_proc);
609 hr_utility.set_message_token('STEP','30');
610 hr_utility.raise_error;
611 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK5') Then
612 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
613 hr_utility.set_message_token('PROCEDURE', l_proc);
614 hr_utility.set_message_token('STEP','35');
615 hr_utility.raise_error;
616
617 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK6') Then
618 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
619 hr_utility.set_message_token('PROCEDURE', l_proc);
620 hr_utility.set_message_token('STEP','40');
621 hr_utility.raise_error;
622 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK7') Then
623 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
624 hr_utility.set_message_token('PROCEDURE', l_proc);
625 hr_utility.set_message_token('STEP','45');
626 hr_utility.raise_error;
627 ElsIf (p_constraint_name = 'PAY_PAYROLL_ACTIONS_FK8') Then
628 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
629 hr_utility.set_message_token('PROCEDURE', l_proc);
630
631 hr_utility.set_message_token('STEP','50');
632 hr_utility.raise_error;
633 Else
634 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
635 hr_utility.set_message_token('PROCEDURE', l_proc);
636 hr_utility.set_message_token('STEP','55');
637 hr_utility.raise_error;
638 End If;
639 --
640 hr_utility.set_location(' Leaving:'||l_proc, 10);
641 End constraint_error;
642 --
643 -- ----------------------------------------------------------------------------
644 -- |---------------------------------< lck >----------------------------------|
645 -- ----------------------------------------------------------------------------
646 --
647 procedure lck
648 (p_payroll_action_id in number
649 ,p_p_object_version_number in number
650 ,p_a_object_version_number in number
651 ) is
652 l_p_object_version_number number;
653 --
654 -- Cursor selects the 'current' row from the HR Schema
655 --
656 Cursor C_lock is
657 select pya.payroll_action_id
658 , pya.business_group_id
659 , pya.org_payment_method_id
660 , pya.action_status
661 , pya.effective_date
662 , pya.target_payroll_action_id
663 , aga.object_version_number
664 , pya.object_version_number
665 from pay_payroll_actions pya
666 , pay_assignment_actions aga
667 where /* Payroll action lock */
668 pya.payroll_action_id = p_payroll_action_id
669 and pya.action_type = 'X'
670 /* Assignment action lock */
671 and aga.payroll_action_id = pya.payroll_action_id
672 for update nowait;
673 --
674 l_proc varchar2(72) := g_package||'lck';
675 --
676 Begin
677 hr_utility.set_location('Entering:'||l_proc, 5);
678 --
679 -- Check the mandatory args have been set
680 --
681 hr_api.mandatory_arg_error
682 (p_api_name => l_proc
683 ,p_argument => 'payroll_action_id'
684 ,p_argument_value => p_payroll_action_id
685 );
686 hr_api.mandatory_arg_error
687 (p_api_name => l_proc
688 ,p_argument => 'p_object_version_number'
689 ,p_argument_value => p_p_object_version_number
690 );
691 hr_api.mandatory_arg_error
692 (p_api_name => l_proc
693 ,p_argument => 'a_object_version_number'
694 ,p_argument_value => p_a_object_version_number
695 );
696 hr_utility.set_location(l_proc, 6);
697 --
698 -- Additional logic specific to this entity:
699 -- Do not allow the lock to be taken out if there is an AOL concurrent
700 -- request waiting to run or still running on a concurrent manager.
701 --
702 pay_qpq_api.chk_for_con_request
703 (p_payroll_action_id => p_payroll_action_id);
704 hr_utility.set_location(l_proc, 7);
705 --
706 open C_lock;
707 Fetch C_lock Into g_old_rec.payroll_action_id
708 , g_old_rec.business_group_id
709 , g_old_rec.org_payment_method_id
710 , g_old_rec.action_status
711 , g_old_rec.effective_date
712 , g_old_rec.target_payroll_action_id
713 , g_old_rec.object_version_number
714 , l_p_object_version_number;
715 If C_lock%notfound then
716 Close C_lock;
717 --
718 -- The primary key is invalid therefore we must error
719 --
720 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
721 hr_utility.raise_error;
722 End If;
723 Close C_lock;
724 If (p_a_object_version_number <> g_old_rec.object_version_number) or
725 (p_p_object_version_number <> l_p_object_version_number) Then
726 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
727 hr_utility.raise_error;
728 End If;
729 --
730 hr_utility.set_location(' Leaving:'||l_proc, 10);
731 --
735 When HR_Api.Object_Locked then
732 -- We need to trap the ORA LOCK exception
733 --
734 Exception
736 --
737 -- The object is locked therefore we need to supply a meaningful
738 -- error message.
739 --
740 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
741 hr_utility.set_message_token('TABLE_NAME', 'pay_payroll_actions');
742 hr_utility.raise_error;
743 End lck;
744 --
745 -- ----------------------------------------------------------------------------
746 -- |-----------------------------< convert_defs >-----------------------------|
747 -- ----------------------------------------------------------------------------
748 -- {Start Of Comments}
749 --
750 -- Description:
751 -- The Convert_Defs function has one very important function:
752 -- It must return the record structure for the row with all system defaulted
753 -- values converted into its corresponding argument value for update. When
754 -- we attempt to update a row through the Upd business process , certain
755 -- arguments can be defaulted which enables flexibility in the calling of
756 -- the upd process (e.g. only attributes which need to be updated need to be
757 -- specified). For the upd business process to determine which attributes
758 -- have NOT been specified we need to check if the argument has a reserved
759 -- system default value. Therefore, for all attributes which have a
760 -- corresponding reserved system default mechanism specified we need to
761 -- check if a system default is being used. If a system default is being
762 -- used then we convert the defaulted value into its corresponding attribute
763 -- value held in the g_old_rec data structure.
764 --
765 -- Pre Conditions:
766 -- This private function can only be called from the upd process.
767 --
768 -- In Arguments:
769 -- A Pl/Sql record structure.
770 --
771 -- Post Success:
772 -- The record structure will be returned with all system defaulted argument
773 -- values converted into its current row attribute value.
774 --
775 -- Post Failure:
776 -- No direct error handling is required within this function. Any possible
777 -- errors within this function will be a PL/SQL value error due to
778 -- conversion of datatypes or data lengths.
779 --
780 -- Access Status:
781 -- Internal Development Use Only.
782 --
783 -- {End Of Comments}
784 --
785 Function convert_defs(p_rec in out nocopy g_rec_type)
786 Return g_rec_type is
787 --
788 l_proc varchar2(72) := g_package||'convert_defs';
789 --
790 Begin
791 --
792 hr_utility.set_location('Entering:'||l_proc, 5);
793 --
794 -- We must now examine each argument value in the
795 -- p_rec plsql record structure
796 -- to see if a system default is being used. If a system default
797 -- is being used then we must set to the 'current' argument value.
798 --
799 If (p_rec.business_group_id = hr_api.g_number) then
800 p_rec.business_group_id := g_old_rec.business_group_id;
801 End If;
802 If (p_rec.org_payment_method_id = hr_api.g_number) then
803 p_rec.org_payment_method_id := g_old_rec.org_payment_method_id;
804 End If;
805 If (p_rec.action_status = hr_api.g_varchar2) then
806 p_rec.action_status := g_old_rec.action_status;
807 End If;
808 If (p_rec.effective_date = hr_api.g_date) then
809 p_rec.effective_date := g_old_rec.effective_date;
810 End If;
811 If (p_rec.target_payroll_action_id = hr_api.g_number) then
812 p_rec.target_payroll_action_id := g_old_rec.target_payroll_action_id;
813 End If;
814 --
815 -- Return the plsql record structure.
816 --
817 hr_utility.set_location(' Leaving:'||l_proc, 10);
818 Return(p_rec);
819 --
820 End convert_defs;
821 --
822 -- ----------------------------------------------------------------------------
823 -- |------------------------------< update_dml >------------------------------|
824 -- ----------------------------------------------------------------------------
825 -- {Start Of Comments}
826 --
827 -- Description:
828 -- This procedure controls the actual dml update logic. The functions of
829 -- this procedure are as follows:
830 -- 1. Increment the object_version_number by 1.
831 -- 2. To set and unset the g_api_dml status as required (as we are about to
832 -- perform dml).
833 -- 3. To update the specified row in the schema using the primary key in
834 -- the predicates.
835 -- 4. To trap any constraint violations that may have occurred.
836 -- 5. To raise any other errors.
837 --
838 -- Pre Conditions:
839 -- This is an internal private procedure which must be called from the upd
840 -- procedure.
841 --
842 -- In Arguments:
843 -- A Pl/Sql record structure.
844 --
845 -- Post Success:
846 -- The specified row will be updated in the schema.
847 --
848 -- Post Failure:
849 -- On the update dml failure it is important to note that we always reset
850 -- the g_api_dml status to false.
851 -- If a check or parent integrity constraint violation is raised the
852 -- constraint_error procedure will be called.
853 -- If any other error is reported, the error will be raised after the
854 -- g_api_dml status is reset.
855 --
856 -- Access Status:
857 -- Internal Development Use Only.
858 --
859 -- {End Of Comments}
860 --
861 Procedure update_dml(p_rec in out nocopy g_rec_type) is
862 --
863 l_proc varchar2(72) := g_package||'update_dml';
864 --
865 Begin
866 hr_utility.set_location('Entering:'||l_proc, 5);
867 --
868 -- Increment the object version
869 --
870 p_rec.object_version_number := p_rec.object_version_number + 1;
871 --
872 -- Set the api dml status
873 --
874 g_api_dml := true;
875 --
876 -- Update the pay_payroll_actions Row
877 --
878 update pay_payroll_actions
879 set action_status = p_rec.action_status
880 , object_version_number = p_rec.object_version_number
881 where payroll_action_id = p_rec.payroll_action_id;
882 --
883 -- Unset the api dml status
884 --
885 g_api_dml := false;
886 --
887 hr_utility.set_location(' Leaving:'||l_proc, 10);
888 --
889 Exception
890 When hr_api.check_integrity_violated Then
891 -- A check constraint has been violated
892 g_api_dml := false; -- Unset the api dml status
893 constraint_error
894 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
895 When hr_api.parent_integrity_violated then
896 -- Parent integrity has been violated
897 g_api_dml := false; -- Unset the api dml status
898 constraint_error
899 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
900 When hr_api.unique_integrity_violated Then
901 -- Unique integrity has been violated
902 g_api_dml := false; -- Unset the api dml status
903 constraint_error
904 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
905 When Others Then
906 g_api_dml := false; -- Unset the api dml status
907 Raise;
908 End update_dml;
909 --
910 -- ----------------------------------------------------------------------------
911 -- |------------------------------< insert_dml >------------------------------|
912 -- ----------------------------------------------------------------------------
913 -- {Start Of Comments}
914 --
915 -- Description:
916 -- This procedure controls the actual dml insert logic. The functions of
917 -- this procedure are as follows:
918 -- 1. Initialise the object_version_number to 1.
919 -- 2. To set and unset the g_api_dml status as required (as we are about to
920 -- perform dml).
921 -- 3. To insert the row into the schema.
922 -- 4. To trap any constraint violations that may have occurred.
923 -- 5. To raise any other errors.
924
925 --
926 -- Pre Conditions:
927 -- This is an internal private procedure which must be called from the ins
928 -- procedure and must have all mandatory arguments set (except the
929 -- object_version_number which is initialised within this procedure).
930 --
931 -- In Arguments:
932 -- p_rec
933 -- Contains the main attributes to be inserted for the Payroll Process row.
934 -- p_action_type
935 -- Will be used to populate pay_payroll_action.action_type
936 -- p_payroll_id
937 -- Will be used to populate pay_payroll_action.payroll_id
938
939 -- p_consolidation_set_id
940 -- Will be used to populate pay_payroll_action.consolidation_set_id
941 -- p_action_population_status
942 -- Will be used to populate pay_payroll_action.action_population_status
943 --
944 -- Post Success:
945 -- The specified row will be inserted into the schema.
946 --
947 -- Post Failure:
948 -- On the insert dml failure it is important to note that we always reset
949 -- the g_api_dml status to false.
950 -- If a check or parent integrity constraint violation is raised the
951 -- constraint_error procedure will be called.
952
953 -- If any other error is reported, the error will be raised after the
954 -- g_api_dml status is reset.
955 --
956 -- Developer Implementation Notes:
957 -- None.
958 --
959 -- Access Status:
960 -- Internal Development Use Only.
961 --
962 -- {End Of Comments}
963 --
964 Procedure insert_dml
965 (p_rec in out nocopy g_rec_type
966 ,p_action_type in pay_payroll_actions.action_type%TYPE
967 ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
968 ,p_consolidation_set_id in
969 pay_payroll_actions.consolidation_set_id%TYPE
970 ,p_action_population_status in
971 pay_payroll_actions.action_population_status%TYPE
972 ) is
973 --
974 l_proc varchar2(72) := g_package||'insert_dml';
975 --
976 Begin
977 hr_utility.set_location('Entering:'||l_proc, 5);
978 --
979
980 -- Initialise the object version
981 --
982 p_rec.object_version_number := 1;
983 --
984 -- Set the api dml status
985 --
986 g_api_dml := true;
987 --
988 -- Insert the row into: pay_payroll_actions
989 --
990 insert into pay_payroll_actions
991 (payroll_action_id
992 ,business_group_id
993 ,org_payment_method_id
994 ,action_status
995 ,effective_date
996 ,target_payroll_action_id
997 ,action_type
998 ,payroll_id
999 ,consolidation_set_id
1000 ,action_population_status
1001 ,object_version_number
1002 ,legislative_parameters
1003 ,report_type
1004 ,report_qualifier
1005 ,report_category
1006 )
1007 values
1008 (p_rec.payroll_action_id
1009 ,p_rec.business_group_id
1010 ,p_rec.org_payment_method_id
1011 ,p_rec.action_status
1012 ,p_rec.effective_date
1013 ,p_rec.target_payroll_action_id
1014 ,p_action_type
1015 ,p_payroll_id
1016 ,p_consolidation_set_id
1017 ,p_action_population_status
1018 ,p_rec.object_version_number
1019 ,p_rec.legislative_parameters
1020 ,p_rec.report_type
1021 ,p_rec.report_qualifier
1022 ,p_rec.report_category
1023 );
1024 --
1025 -- Unset the api dml status
1026 --
1027
1028 g_api_dml := false;
1029 --
1030 hr_utility.set_location(' Leaving:'||l_proc, 10);
1031 Exception
1032 When hr_api.check_integrity_violated Then
1033 -- A check constraint has been violated
1034 g_api_dml := false; -- Unset the api dml status
1035 constraint_error
1036 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1037 When hr_api.parent_integrity_violated then
1038 -- Parent integrity has been violated
1039 g_api_dml := false; -- Unset the api dml status
1040 constraint_error
1041
1042 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1043 When hr_api.unique_integrity_violated Then
1044 -- Unique integrity has been violated
1045 g_api_dml := false; -- Unset the api dml status
1046 constraint_error
1047 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1048 When Others Then
1049 g_api_dml := false; -- Unset the api dml status
1050 hr_utility.set_location('Others exception '||l_proc,-999);
1051 Raise;
1052 End insert_dml;
1053 --
1054 -- ----------------------------------------------------------------------------
1055 -- |------------------------------< pre_update >------------------------------|
1056 -- ----------------------------------------------------------------------------
1057 -- {Start Of Comments}
1058 --
1059 -- Description:
1060 -- This private procedure contains any processing which is required before
1061 -- the update dml.
1062 --
1063 -- Pre Conditions:
1064 -- This is an internal procedure which is called from the upd procedure.
1065 --
1066 -- In Arguments:
1067 -- A Pl/Sql record structure.
1068 --
1069 -- Post Success:
1070 -- Processing continues.
1071 --
1072 -- Post Failure:
1073 -- If an error has occurred, an error message and exception will be raised
1074 -- but not handled.
1075 --
1076 -- Access Status:
1077 -- Internal Development Use Only.
1078 --
1079 -- {End Of Comments}
1080 --
1081 Procedure pre_update(p_rec in g_rec_type) is
1082 --
1083 l_proc varchar2(72) := g_package||'pre_update';
1087 --
1084 --
1085 Begin
1086 hr_utility.set_location('Entering:'||l_proc, 5);
1088 hr_utility.set_location(' Leaving:'||l_proc, 10);
1089 End pre_update;
1090 --
1091
1092 -- ----------------------------------------------------------------------------
1093 -- |------------------------------< pre_insert >------------------------------|
1094 -- ----------------------------------------------------------------------------
1095 -- {Start Of Comments}
1096
1097 --
1098 -- Description:
1099 -- This private procedure contains any processing which is required before
1100 -- the insert dml. Presently, if the entity has a corresponding primary
1101 -- key which is maintained by an associating sequence, the primary key for
1102 -- the entity will be populated with the next sequence value in
1103 -- preparation for the insert dml.
1104 --
1105 -- Pre Conditions:
1106 -- This is an internal procedure which is called from the ins procedure.
1107 --
1108 -- In Arguments:
1109 -- A Pl/Sql record structure.
1110
1111 --
1112 -- Post Success:
1113 -- p_rec.action_status
1114 -- Set to 'M', the code for Mark for Retry
1115 -- p_action_type
1116 -- Set to 'X', the code for a Archive
1117 -- p_payroll_id
1118 -- Set to the payroll_id attribute as defined on the corresponding
1119 -- QuickPay Run payroll action.
1120 -- p_consolidation_set_id
1121 -- Set to the consolidation_set_id attribute as defined on the
1122 -- corresponding QuickPay Run payroll action.
1123 -- p_action_population_status
1124 -- Set to 'U', the code for Unpopulated.
1125 --
1126 -- Post Failure:
1127 -- If an error has occurred, an error message and exception will be raised
1128 -- but not handled.
1129 --
1130 -- Access Status:
1131 -- Internal Development Use Only.
1132 --
1133 -- {End Of Comments}
1134 --
1135 Procedure pre_insert
1136 (p_rec in out nocopy g_rec_type
1137 ,p_action_type out nocopy varchar2
1138 ,p_payroll_id out nocopy number
1139 ,p_consolidation_set_id out nocopy pay_payroll_actions.
1140 consolidation_set_id%TYPE
1141 ,p_action_population_status out nocopy varchar2
1142 ) is
1143 --
1144 l_proc varchar2(72) := g_package||'pre_insert';
1145 --
1146 Cursor C_next_id is select pay_payroll_actions_s.nextval from sys.dual;
1147 --
1148 cursor csr_qpq_details (v_target_payroll_action_id number) is
1149 select pya.payroll_id
1150 , pya.consolidation_set_id
1151 from pay_payroll_actions pya
1152 where pya.payroll_action_id = v_target_payroll_action_id;
1153 Begin
1154 hr_utility.set_location('Entering:'||l_proc, 5);
1155 --
1156 -- Select the next sequence number
1157 --
1158 Open C_next_id;
1159 Fetch C_next_id Into p_rec.payroll_action_id;
1160 Close C_next_id;
1161 hr_utility.set_location(l_proc, 6);
1162 --
1163 -- Set the action_status to Mark for Retry
1164 --
1165 p_rec.action_status := 'M';
1166 --
1167 -- Set pay_payroll_action columns which are only
1168 -- set by non-process code at insert time.
1169 --
1170 -- Set action_type to Archive
1171 --
1172 p_action_type := 'X';
1173 --
1174 -- Set action_population_status to unpopulated
1175 --
1176 p_action_population_status := 'U';
1177
1178 --
1179 -- Set columns which are derived from the QuickPay Run action
1180 --
1181 open csr_qpq_details(p_rec.target_payroll_action_id);
1182 fetch csr_qpq_details into p_payroll_id
1183 , p_consolidation_set_id;
1184 if csr_qpq_details%notfound then
1185 close csr_qpq_details;
1186 -- Internal error
1187 -- The corresponding QuickPay Run payroll action has been deleted
1188 -- between validating it exists in insert_validate and pre_insert.
1189 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1190 hr_utility.set_message_token('PROCEDURE', l_proc);
1191
1192 hr_utility.set_message_token('STEP', '1');
1193 hr_utility.raise_error;
1194 end if;
1195 close csr_qpq_details;
1196 --
1197 hr_utility.set_location(' Leaving:'||l_proc, 10);
1198 End pre_insert;
1199 --
1200 -- ----------------------------------------------------------------------------
1201 -- |-----------------------------< post_update >------------------------------|
1202 -- ----------------------------------------------------------------------------
1203 -- {Start Of Comments}
1204 --
1205 -- Description:
1206 -- This private procedure contains any processing which is required after
1207 -- the update dml.
1208 --
1209 -- Pre Conditions:
1210 -- This is an internal procedure which is called from the upd procedure.
1211 --
1212 -- In Arguments:
1213 -- A Pl/Sql record structure.
1214 --
1215 -- Post Success:
1216 -- Processing continues.
1217 --
1218 -- Post Failure:
1219 -- If an error has occurred, an error message and exception will be raised
1220 -- but not handled.
1221 --
1222 -- Access Status:
1223 -- Internal Development Use Only.
1224 --
1225 -- {End Of Comments}
1226 --
1227 Procedure post_update(p_rec in g_rec_type) is
1228 --
1229 l_proc varchar2(72) := g_package||'post_update';
1230 --
1231 Begin
1232 hr_utility.set_location('Entering:'||l_proc, 5);
1233 --
1234 hr_utility.set_location(' Leaving:'||l_proc, 10);
1235 End post_update;
1236 --
1237 -- ----------------------------------------------------------------------------
1241 --
1238 -- |-----------------------------< post_insert >------------------------------|
1239 -- ----------------------------------------------------------------------------
1240 -- {Start Of Comments}
1242 -- Description:
1243 -- This private procedure contains any processing which is required after
1244 -- the insert dml.
1245 --
1246
1247 -- Pre Conditions:
1248 -- This is an internal procedure which is called from the ins procedure.
1249 --
1250 -- In Arguments:
1251 -- A Pl/Sql record structure.
1252 --
1253 -- Post Success:
1254 -- Processing continues.
1255 --
1256 -- Post Failure:
1257 -- If an error has occurred, an error message and exception will be raised
1258 -- but not handled.
1259 --
1260
1261 -- Access Status:
1262 -- Internal Development Use Only.
1263 --
1264 -- {End Of Comments}
1265 --
1266 Procedure post_insert
1267 (p_rec in g_rec_type
1268 ,p_assignment_action_id out nocopy number
1269 ,p_a_object_version_number out nocopy number
1270 ) is
1271 --
1272 l_proc varchar2(72) := g_package||'post_insert';
1273 --
1274
1275 -- These variables are set by inserting the assignment action.
1276 -- The values are returned from this procedure.
1277 --
1278 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1279 l_object_version_number pay_assignment_actions.object_version_number%TYPE;
1280 --
1281 Begin
1282 hr_utility.set_location('Entering:'||l_proc, 5);
1283 --
1284 -- Insert the QuickPay Archival Run assignment action and the action interlock
1285 --
1286 qppsassact
1287 (p_payroll_action_id => p_rec.payroll_action_id
1288 ,p_assignment_action_id => l_assignment_action_id
1289 ,p_object_version_number => l_object_version_number
1290 );
1291
1292 hr_utility.set_location(l_proc, 6);
1293 --
1294 p_assignment_action_id := l_assignment_action_id;
1295 p_a_object_version_number := l_object_version_number;
1296 --
1297 hr_utility.set_location(' Leaving:'||l_proc, 10);
1298 End post_insert;
1299 --
1300 -- ----------------------------------------------------------------------------
1301 -- |-----------------------------< convert_args >-----------------------------|
1302 -- ----------------------------------------------------------------------------
1303 -- {Start Of Comments}
1304 --
1305 -- Description:
1306 -- This function is used to turn attribute arguments into the record
1307 -- structure g_rec_type.
1308 --
1309 -- Pre Conditions:
1310 -- This is a private function and can only be called from the ins or upd
1311 -- attribute processes.
1312 --
1313 -- In Arguments:
1314 -- The individual attributes of a QuickPay Archival.
1315 --
1316 -- Post Success:
1317 -- A returning record structure will be returned.
1318 --
1319 -- Post Failure:
1320 -- No direct error handling is required within this function. Any possible
1321 -- errors within this function will be a PL/SQL value error due to
1322 -- conversion of datatypes or data lengths.
1323 --
1324 -- Access Status:
1325 -- Internal Development Use Only.
1326 --
1327 -- {End Of Comments}
1328 --
1329 function convert_args
1330 (p_payroll_action_id in number
1331 ,p_business_group_id in number
1332 ,p_org_payment_method_id in number
1333 ,p_action_status in varchar2
1334 ,p_effective_date in date
1335 ,p_legislative_parameters in varchar2
1336 ,p_target_payroll_action_id in number
1337 ,p_object_version_number in number
1338 ,p_report_type in varchar2
1339 ,p_report_qualifier in varchar2
1340 ,p_report_category in varchar2
1341 ) Return g_rec_type is
1342 --
1343 l_rec g_rec_type;
1344 l_proc varchar2(72) := g_package||'convert_args';
1345 --
1346 Begin
1347 --
1348 hr_utility.set_location('Entering:'||l_proc, 5);
1349 --
1350 -- Convert arguments into local l_rec structure.
1351
1352 --
1353 l_rec.payroll_action_id := p_payroll_action_id;
1354 l_rec.business_group_id := p_business_group_id;
1355 l_rec.org_payment_method_id := p_org_payment_method_id;
1356 l_rec.action_status := p_action_status;
1357 l_rec.effective_date := p_effective_date;
1358 l_rec.legislative_parameters := p_legislative_parameters;
1359 l_rec.target_payroll_action_id := p_target_payroll_action_id;
1360 l_rec.object_version_number := p_object_version_number;
1361 l_rec.report_type := p_report_type;
1362 l_rec.report_qualifier := p_report_qualifier;
1363 l_rec.report_category := p_report_category;
1364 --
1365 -- Return the plsql record structure.
1366 --
1367 hr_utility.set_location(' Leaving:'||l_proc, 10);
1368 Return(l_rec);
1369
1370 --
1371 End convert_args;
1372 --
1373 --
1374 -- ----------------------------------------------------------------------------
1375 -- |---------------------------< update_validate >----------------------------|
1376 -- ----------------------------------------------------------------------------
1377 -- {Start Of Comments}
1378 --
1379 -- Description:
1380 -- This procedure controls the execution of all update business rules
1381 -- validation.
1382 --
1383 -- Pre Conditions:
1384 -- This private procedure is called from upd procedure.
1385 --
1386 -- In Arguments:
1390 -- Processing continues.
1387 -- A Pl/Sql record structure.
1388 --
1389 -- Post Success:
1391 --
1392 -- Post Failure:
1393 -- If a business rules fails the error will not be handled by this procedure
1394 -- unless explicitly coded.
1395 --
1396 -- Access Status:
1397 -- Internal Development Use Only.
1398 --
1399 -- {End Of Comments}
1400 --
1401 Procedure update_validate(p_rec in g_rec_type) is
1402 --
1403 l_proc varchar2(72) := g_package||'update_validate';
1404 --
1405 Begin
1406 hr_utility.set_location('Entering:'||l_proc, 5);
1407 --
1408 -- Call all supporting business operations
1409 --
1410 -- Check that the columns which cannot be
1411 -- updated have not be changed.
1412 --
1413 check_non_updateable_args(p_rec => p_rec);
1414 hr_utility.set_location(l_proc, 6);
1415 --
1416 -- Check that the payroll_action has a null current_task.
1417 --
1418 chk_cur_task
1419 (p_payroll_action_id => p_rec.payroll_action_id
1420 );
1421 hr_utility.set_location(l_proc, 7);
1422 --
1423 -- If the action_status has changed, validate it has only been
1424 -- changed to 'M'ark for Retry and that action is allowed to have
1425 -- a 'M'ark for Retry status. (Ensure this is always the last validation
1426 -- step because the chk_action_status procedure will update child rows.)
1427 --
1428
1429 if p_rec.action_status <> g_old_rec.action_status then
1430 chk_action_status
1431 (p_payroll_action_id => p_rec.payroll_action_id
1432 ,p_old_action_status => g_old_rec.action_status
1433 ,p_new_action_status => p_rec.action_status
1434 );
1435 end if;
1436 --
1437 hr_utility.set_location(' Leaving:'||l_proc, 10);
1438 End update_validate;
1439 --
1440 -- ----------------------------------------------------------------------------
1441 -- |---------------------------< insert_validate >----------------------------|
1442 -- ----------------------------------------------------------------------------
1443 -- {Start Of Comments}
1444 --
1445 -- Description:
1446 -- This procedure controls the execution of all insert business rules
1447 -- validation.
1448 --
1449 -- Pre Conditions:
1450 -- This private procedure is called from ins procedure.
1451
1452 --
1453 -- In Arguments:
1454 -- A Pl/Sql record structure.
1455 --
1456 -- Post Success:
1457 -- Processing continues.
1458 --
1459 -- Post Failure:
1460 -- If a business rules fails the error will not be handled by this procedure
1461 -- unless explicitly coded.
1462 --
1463 -- Access Status:
1464 -- Internal Development Use Only.
1465
1466 --
1467 -- {End Of Comments}
1468 --
1469 Procedure insert_validate(p_rec in g_rec_type) is
1470 --
1471 l_proc varchar2(72) := g_package||'insert_validate';
1472 --
1473 Begin
1474 hr_utility.set_location('Entering:'||l_proc, 5);
1475 --
1476 -- Call all supporting business operations
1477 --
1478 --
1479 -- Validate target_payroll_action_id, business_group_id and effective_date
1480 --
1481 chk_target_payroll_action_id
1482 (p_target_payroll_action_id => p_rec.target_payroll_action_id
1483 ,p_business_group_id => p_rec.business_group_id
1484 ,p_effective_date => p_rec.effective_date
1485 );
1486 --
1487 hr_utility.set_location(' Leaving:'||l_proc, 10);
1488 End insert_validate;
1489 --
1490 -- ----------------------------------------------------------------------------
1491 -- |---------------------------------< upd >----------------------------------|
1492 -- ----------------------------------------------------------------------------
1493 --
1494 procedure upd
1495 (p_rec in out nocopy g_rec_type
1496 ,p_assignment_action_id in number
1497 ,p_a_object_version_number in number
1498 ,p_validate in boolean default false
1499 ) is
1500 --
1501 l_proc varchar2(72) := g_package||'upd';
1502 --
1503 Begin
1504 hr_utility.set_location('Entering:'||l_proc, 5);
1505 --
1506 -- Determine if the business process is to be validated.
1507 --
1508 If p_validate then
1509 --
1510 -- Issue the savepoint.
1511 --
1512 SAVEPOINT upd_pay_qpu;
1513 End If;
1514 --
1515 -- We must lock the row which we need to update.
1516 --
1517 lck
1518 (p_payroll_action_id => p_rec.payroll_action_id
1519 ,p_p_object_version_number => p_rec.object_version_number
1520 ,p_a_object_version_number => p_a_object_version_number
1521 );
1522 --
1523 -- 1. During an update system defaults are used to determine if
1524 -- arguments have been defaulted or not. We must therefore
1525 -- derive the full record structure values to be updated.
1526 --
1527 -- 2. Call the supporting update validate operations.
1528 --
1529 update_validate(convert_defs(p_rec));
1530 --
1531 -- Call the supporting pre-update operation
1532 --
1533 pre_update(p_rec);
1534 --
1535 -- Update the row.
1536 --
1537 update_dml(p_rec);
1538 --
1539 -- Call the supporting post-update operation
1540 --
1541 post_update(p_rec);
1542 --
1543 -- If we are validating then raise the Validate_Enabled exception
1544 --
1545 If p_validate then
1546 Raise HR_Api.Validate_Enabled;
1550 Exception
1547 End If;
1548 --
1549 hr_utility.set_location(' Leaving:'||l_proc, 10);
1551 When HR_Api.Validate_Enabled Then
1552 --
1553 -- As the Validate_Enabled exception has been raised
1554 -- we must rollback to the savepoint
1555 --
1556 ROLLBACK TO upd_pay_qpu;
1557 End upd;
1558 --
1559 -- ----------------------------------------------------------------------------
1560 -- |---------------------------------< ins >----------------------------------|
1561 -- ----------------------------------------------------------------------------
1562 --
1563 Procedure ins
1564 (p_rec in out nocopy g_rec_type
1565 ,p_assignment_action_id out nocopy number
1566 ,p_a_object_version_number out nocopy number
1567 ,p_validate in boolean default false
1568 ) is
1569 --
1570 l_proc varchar2(72) := g_package||'ins';
1571 --
1572 -- These variables are set by pre_insert and past to insert_dml
1573 --
1574 l_action_type pay_payroll_actions.action_type%TYPE;
1575 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
1576 l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
1577 l_action_population_status pay_payroll_actions.action_population_status%TYPE;
1578
1579 --
1580 -- These variables are set by post_insert and returned from this procedure
1581 --
1582 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1583 l_a_object_version_number pay_assignment_actions.object_version_number%TYPE;
1584 --
1585 Begin
1586 hr_utility.set_location('Entering:'||l_proc, 5);
1587 --
1588 -- Determine if the business process is to be validated.
1589 --
1590 If p_validate then
1591 --
1592 -- Issue the savepoint.
1593 --
1594 SAVEPOINT ins_pay_qpu;
1595 End If;
1596 --
1597 -- Call the supporting insert validate operations
1598 --
1599 insert_validate(p_rec);
1600 --
1601 -- Call the supporting pre-insert operation
1602 --
1603 pre_insert
1604 (p_rec => p_rec
1605 ,p_action_type => l_action_type
1606 ,p_payroll_id => l_payroll_id
1607 ,p_consolidation_set_id => l_consolidation_set_id
1608 ,p_action_population_status => l_action_population_status
1609 );
1610 --
1611 -- Insert the row
1612 --
1613 insert_dml
1614 (p_rec => p_rec
1615 ,p_action_type => l_action_type
1616 ,p_payroll_id => l_payroll_id
1617 ,p_consolidation_set_id => l_consolidation_set_id
1618 ,p_action_population_status => l_action_population_status
1619 );
1620 --
1621 -- Call the supporting post-insert operation
1622 --
1623 post_insert
1624 (p_rec => p_rec
1625 ,p_assignment_action_id => l_assignment_action_id
1626 ,p_a_object_version_number => l_a_object_version_number
1627 );
1628 --
1629 -- Set output parameters
1630 --
1631
1632 p_assignment_action_id := l_assignment_action_id;
1633 p_a_object_version_number := l_a_object_version_number;
1634 --
1635 -- If we are validating then raise the Validate_Enabled exception
1636 --
1637 If p_validate then
1638
1639 Raise HR_Api.Validate_Enabled;
1640 End If;
1641 --
1642 hr_utility.set_location(' Leaving:'||l_proc, 10);
1643 Exception
1644 When HR_Api.Validate_Enabled Then
1645 --
1646
1647 -- As the Validate_Enabled exception has been raised
1648 -- we must rollback to the savepoint
1649 --
1650 hr_utility.set_location(' Calling Rollback now'||l_proc, 99);
1651 ROLLBACK TO ins_pay_qpu;
1652 end ins;
1653 --
1654 -- ----------------------------------------------------------------------------
1655 -- |---------------------------------< upd >----------------------------------|
1656 -- ----------------------------------------------------------------------------
1657 --
1658 procedure upd
1659 (p_payroll_action_id in number
1660 ,p_assignment_action_id in number
1661 ,p_action_status in varchar2 default hr_api.g_varchar2
1662 ,p_p_object_version_number in out nocopy number
1663 ,p_a_object_version_number in number
1664 ,p_validate in boolean default false
1665 ) is
1666 --
1667 l_rec g_rec_type;
1668 l_proc varchar2(72) := g_package||'upd';
1669 --
1670 Begin
1671
1672 hr_utility.set_location('Entering:'||l_proc, 5);
1673 --
1674 -- Call conversion function to turn arguments into the
1675 -- l_rec structure.
1676 --
1677 l_rec :=
1678 convert_args
1679 (p_payroll_action_id
1680 ,hr_api.g_number
1681 ,hr_api.g_number
1682 ,p_action_status
1683 ,hr_api.g_date
1684 ,hr_api.g_varchar2
1685 ,hr_api.g_number
1686 ,p_p_object_version_number
1687 ,hr_api.g_varchar2
1688 ,hr_api.g_varchar2
1689 ,hr_api.g_varchar2
1690 );
1691 --
1692 -- Having converted the arguments into the
1693 -- plsql record structure we call the corresponding record
1694 -- business process.
1695 --
1696 upd
1697 (p_rec => l_rec
1698 ,p_assignment_action_id => p_assignment_action_id
1699 ,p_a_object_version_number => p_a_object_version_number
1700 ,p_validate => p_validate
1701 );
1702 p_p_object_version_number := l_rec.object_version_number;
1703 --
1704 hr_utility.set_location(' Leaving:'||l_proc, 10);
1705 End upd;
1706 --
1707 -- ----------------------------------------------------------------------------
1708 -- |---------------------------------< ins >----------------------------------|
1709 -- ----------------------------------------------------------------------------
1710 --
1711 Procedure ins
1712 (p_business_group_id in number
1713 ,p_org_payment_method_id in number default null
1714 ,p_effective_date in date
1715 ,p_target_payroll_action_id in number default null
1716 ,p_legislative_parameters in varchar2
1717 ,p_report_type in varchar2
1718 ,p_report_qualifier in varchar2
1719 ,p_report_category in varchar2
1720 ,p_payroll_action_id out nocopy number
1721 ,p_action_status out nocopy varchar2
1722 ,p_p_object_version_number out nocopy number
1723 ,p_assignment_action_id out nocopy number
1724 ,p_a_object_version_number out nocopy number
1725 ,p_validate in boolean default false
1726 ) is
1727 --
1728 l_rec g_rec_type;
1729 l_proc varchar2(72) := g_package||'ins';
1730 --
1731
1732 Begin
1733 hr_utility.set_location('Entering:'||l_proc, 5);
1734 --
1735 -- Call conversion function to turn arguments into the
1736 -- p_rec structure.
1737 --
1738 l_rec :=
1739 convert_args
1740 (null
1741 ,p_business_group_id
1742 ,p_org_payment_method_id
1743 ,null
1744 ,p_effective_date
1745 ,p_legislative_parameters
1746 ,p_target_payroll_action_id
1747 ,null
1748 ,p_report_type
1749 ,p_report_qualifier
1750 ,p_report_category
1751 );
1752 --
1753 -- Having converted the arguments into the pay_qpa_rec
1754 -- plsql record structure we call the corresponding record business process.
1755 --
1756 ins
1757 (p_rec => l_rec
1758 ,p_assignment_action_id => p_assignment_action_id
1759 ,p_a_object_version_number => p_a_object_version_number
1760 ,p_validate => p_validate
1761 );
1762
1763 --
1764 -- As the primary key argument(s)
1765 -- are specified as an OUT's we must set these values.
1766 --
1767 p_payroll_action_id := l_rec.payroll_action_id;
1768 p_action_status := l_rec.action_status;
1769 p_p_object_version_number := l_rec.object_version_number;
1770 --
1771 hr_utility.set_location(' Leaving:'||l_proc, 10);
1772 End ins;
1773 --
1774
1775 --
1776
1777 end pay_qpa_api;
[Home] [Help]