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