1 Package Body pay_ppm_bus as
2 /* $Header: pyppmrhi.pkb 120.3.12010000.2 2008/08/06 08:14:13 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ppm_bus.'; -- Global package name
9 --
10 -- ---------------------------------------------------------------------------
11 -- |------------------< balance_remunerative >--------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 -- Description:
15 -- Checks if a balance type is remunerative
16 --
17 -- Pre-conditions:
18 -- The in arguments must exist and must not be null
19 --
20 -- In Arguments:
21 -- p_org_payment_method_id
22 -- p_effective_date
23 --
24 -- Post Success:
25 -- If the related balance type is remunerative then
26 -- returns true, then processing continues.
27 --
28 -- If the related balance type is non-remunerative then
29 -- returns false, then processing continues.
30 --
31 -- Post Failure:
32 -- None
33 --
34 -- Access Status:
35 -- Internal Table Handler Use Only.
36 --
37 function balance_remunerative
38 (p_org_payment_method_id in
39 pay_personal_payment_methods_f.org_payment_method_id%type
40 ,p_effective_date in date
41 )
42 return boolean is
43 --
44 l_exists varchar2(1);
45 l_proc varchar2(72) := g_package||'balance_remunerative';
46
47 -- Check if the related balance type is remunerative
48
49 cursor csr_chk_blt is
50 select null
51 from pay_balance_types blt,
52 pay_defined_balances dfb,
53 pay_org_payment_methods_f opm
54 where blt.assignment_remuneration_flag = 'Y'
55 and blt.balance_type_id = dfb.balance_type_id
56 and dfb.defined_balance_id = opm.defined_balance_id
57 and opm.org_payment_method_id = p_org_payment_method_id
58 and p_effective_date between opm.effective_start_date
59 and opm.effective_end_date;
60 --
61 begin
62 --
63 -- Check mandatory parameters have been set
64 --
65 hr_api.mandatory_arg_error
66 (p_api_name => l_proc
67 ,p_argument => 'org payment method id'
68 ,p_argument_value => p_org_payment_method_id
69 );
70 --
71 hr_api.mandatory_arg_error
72 (p_api_name => l_proc
73 ,p_argument => 'effective date'
74 ,p_argument_value => p_effective_date
75 );
76 --
77 -- Check if the related balance type is remunerative
78 --
79 open csr_chk_blt;
80 fetch csr_chk_blt into l_exists;
81 if csr_chk_blt%found then
82 close csr_chk_blt;
83 return true;
84 else
85 close csr_chk_blt;
86 return false;
87 end if;
88 end balance_remunerative;
89 --
90 -- ---------------------------------------------------------------------------
91 -- |-----------------< return_effective_end_date >--------------------------|
92 -- ---------------------------------------------------------------------------
93 --
94 function return_effective_end_date
95 (p_datetrack_mode in varchar2,
96 p_effective_date in date,
97 p_personal_payment_method_id in number,
98 p_org_payment_method_id in number,
99 p_assignment_id in number,
100 p_run_type_id in number default null,
101 p_priority in number,
102 p_business_group_id in number,
103 p_payee_id in number default null,
104 p_payee_type in varchar2 default null,
105 p_validation_start_date in date,
106 p_validation_end_date in date)
107 return date is
108 --
109 l_proc varchar2(72) := g_package||'return_effective_end_date';
110 l_rtn_date date;
111 --
112 -- select the maximum eligibility date for the payment method
113 -- taking into account the assignment (which could change payrolls)
114 -- and organization payment method usages. to do this we must for
115 -- each assignment row which is to a payroll ensure that the row
116 -- has a corresponding usage which will last for either the
117 -- duration or part duration of the assignment. if a usage only
118 -- lasts for part of the assignment duration then the usage end
119 -- date will be the last eligible row. if for the assignment
120 -- duration a usage doesn't exist at all then the previous
121 -- assignment eligible row must be used. Consistency is ensured because
122 -- pay_org_pay_method_usages_f must have a table SHARE lock on it before
123 -- this routine is called.
124 --
125 function return_usage_date
126 return date is
127 --
128 cursor csr_check_org_method is
129 select 1
130 from pay_org_payment_methods_f opm
131 where opm.org_payment_method_id = p_org_payment_method_id
132 and p_effective_date
133 between opm.effective_start_date
134 and opm.effective_end_date;
135 --
136 cursor asg_sel is
137 select pa.payroll_id,
138 pa.effective_start_date,
139 pa.effective_end_date
140 from per_all_assignments_f pa
141 where pa.assignment_id = p_assignment_id
142 and pa.business_group_id + 0 = p_business_group_id
143 and pa.effective_end_date >= p_effective_date
144 order by pa.effective_start_date;
145 --
146 cursor popmu_sel(l_payroll_id in number,
147 l_start_range in date,
148 l_end_range in date) is
149 select min(popmu.effective_start_date),
150 max(popmu.effective_end_date)
151 from pay_org_pay_method_usages_f popmu
152 where popmu.payroll_id = l_payroll_id
153 and popmu.org_payment_method_id = p_org_payment_method_id
154 and popmu.effective_start_date <= l_end_range
155 and popmu.effective_end_date >= l_start_range;
156 --
157 l_previous_payroll_id pay_payrolls_f.payroll_id%TYPE;
158 l_start_range date;
159 l_end_range date;
160 l_popmu_start_date date;
161 l_popmu_end_date date;
162 l_popmu_date date;
163 l_dummy number;
164 l_proc varchar2(72) := g_package||'return_usage_date';
165 --
166 begin
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 l_previous_payroll_id := null;
169 -- get assignment rows in effective order for processing
170 <<loop1>>
171 for sel1 in asg_sel loop
172 if sel1.payroll_id is not null then
173 hr_utility.set_location(l_proc, 10);
174 l_previous_payroll_id := nvl(l_previous_payroll_id, sel1.payroll_id);
175 -- as a payroll exists for the assignment we must set the
176 -- working range dates
177 l_start_range := greatest(p_effective_date,
178 sel1.effective_start_date);
179 l_end_range := sel1.effective_end_date;
180 -- select the min and max usage dates which overlap the
181 -- assignment range
182 open popmu_sel(sel1.payroll_id, l_start_range, l_end_range);
183 fetch popmu_sel into l_popmu_start_date, l_popmu_end_date;
184 -- the fetch will always return a row because of the
185 -- min/max functions used. if a row isn't found then the
186 -- l_popmu_start_date and l_popmu_end_date variables will
187 -- contain null
188 close popmu_sel;
189 --
190 if (l_popmu_start_date <= l_start_range and
191 l_popmu_end_date >= l_end_range) then
192 -- the usage exists for the duration of the assignment
193 -- therefore we set the date to the current assignment end
194 -- range date
195 l_popmu_date := l_end_range;
196 else
197 -- the usage does not exist for duration of the assignment
198 -- range
199 if (l_popmu_start_date <= l_start_range) then
200 -- the usage exists at the start of the assignment range
201 -- therefore we set the date to the end of the usage
202 l_popmu_date := l_popmu_end_date;
203 else
204 -- the usage does not exist at the start of the
205 -- assignment range therefore it must of existed for
206 -- the previous assignment range. set the date to the
207 -- end of the last assignment range
208 if (l_start_range > p_validation_start_date) then
209 l_popmu_date := l_start_range - 1;
210 else
211 l_popmu_date := null;
212 end if;
213 end if;
214 exit loop1;
215 end if;
216 if (sel1.payroll_id <> l_previous_payroll_id) then
217 -- the payroll has changed.
218 l_previous_payroll_id := sel1.payroll_id;
219 end if;
220 else
221 --
222 -- as an employee assignment row has been selected but is not
223 -- to a payroll we must determine if this is the first returned
224 -- row (we can determine the first row by examining the value of
225 -- l_previous_payroll_id). if it is the first row then we must
226 -- error as the assignment is NOT to a payroll. if a previous
227 -- payroll exists then the employee assignment has been for a
228 -- payroll and we must just exit the loop.
229 --
230 if l_previous_payroll_id is null or
231 (sel1.effective_start_date = p_validation_start_date) then
232 hr_utility.set_message(801, 'HR_6500_ASS_NO_PAYROLL');
233 hr_utility.raise_error;
234 else
235 exit loop1;
236 end if;
237 end if;
238 end loop loop1;
239 --
240 -- check to see if the assignment exists
241 --
242 if l_previous_payroll_id is null then
243 -- the assignment doesn't exists
244 hr_utility.set_message(801, 'HR_7348_PPM_ASSIGNMENT_INVALID');
245 hr_utility.raise_error;
246 end if;
247 --
248 -- if the returning date is null then we must error as either a
249 -- usage or method does not exist
250 --
251 if l_popmu_date is null then
252 -- check to see if the method exists
253 open csr_check_org_method;
254 fetch csr_check_org_method into l_dummy;
255 if csr_check_org_method%notfound then
256 -- an organization method does not exist
257 close csr_check_org_method;
258 hr_utility.set_message(801, 'HR_7347_PPM_INVALID_PAY_TYPE');
259 hr_utility.raise_error;
260 end if;
261 close csr_check_org_method;
262 -- usages cannot exist
263 hr_utility.set_message(801, 'HR_7869_PPM_USAGE_INVALID');
264 hr_utility.raise_error;
265 end if;
266 hr_utility.set_location(' Leaving:'||l_proc, 20);
267 return(l_popmu_date);
268 end return_usage_date;
269 --
270 -- if the payee_id is not null and the payee_type = 'P' then
271 -- the max(eed) of person where the person_id = payee_id.
272 -- need to lock the row selected (i.e. use select..for update).
273 -- Note: this violates lock ladder order upon INSERT as person should
274 -- be locked before assignment. However it's highly unlikely that this
275 -- combination of assignment and person would be locked elsewhere.
276 function return_payee_date
277 return date is
278 --
279 cursor pp_sel is
280 select pp1.effective_end_date
281 from per_people_f pp1
282 where pp1.person_id = p_payee_id
283 and pp1.effective_start_date >= p_effective_date
284 and pp1.effective_end_date =
285 (select max(pp2.effective_end_date)
286 from per_people_f pp2
287 where pp2.person_id = p_payee_id
288 and pp2.effective_start_date >= p_effective_date)
289 for update nowait;
290 --
291 l_proc varchar2(72) := g_package||'return_payee_date';
292 l_payee_date date := hr_api.g_eot;
293 --
294 begin
295 --
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 if (p_payee_id is not null and p_payee_type = 'P') then
299 open pp_sel;
300 fetch pp_sel into l_payee_date;
301 if pp_sel%notfound then
302 -- person doesn't exist for the p_payee_id therefore error
303 close pp_sel;
304 hr_utility.set_message(801, 'HR_7846_PPM_INV_PERSON');
305 hr_utility.raise_error;
306 end if;
307 close pp_sel;
308 end if;
309 hr_utility.set_location(' Leaving:'||l_proc, 15);
310 return(l_payee_date);
311 --
312 end return_payee_date;
313 --
314 -- the (esd - 1) of the earliest future (remunerative) row in PPM which has the same
315 -- priority. Lock the selected row.
316 --
317 function return_priority_date
318 return date is
319 --
320 cursor ppm_sel is
321 select ppm.effective_start_date -1
322 from pay_personal_payment_methods_f ppm
323 where ppm.assignment_id = p_assignment_id
324 and ppm.priority = p_priority
325 and nvl(ppm.run_type_id,-9999) = nvl(p_run_type_id,-9999)
326 and (ppm.priority <> 1
327 or exists
328 (select null
329 from pay_org_payment_methods_f opm
330 , pay_defined_balances db
331 , pay_balance_types bt
332 where opm.org_payment_method_id = ppm.org_payment_method_id
333 and p_effective_date between
334 opm.effective_start_date and opm.effective_end_date
335 and db.defined_balance_id = opm.defined_balance_id
336 and bt.balance_type_id = db.balance_type_id
337 and bt.assignment_remuneration_flag = 'Y'
338 )
339 )
340 and (ppm.personal_payment_method_id <>
341 p_personal_payment_method_id
342 or p_personal_payment_method_id is null)
343 and ppm.effective_start_date =
344 (select min(ppm2.effective_start_date)
345 from pay_personal_payment_methods_f ppm2
346 where (ppm2.personal_payment_method_id <>
347 p_personal_payment_method_id
348 or p_personal_payment_method_id is null)
349 and ppm2.assignment_id = p_assignment_id
350 and ppm2.priority = p_priority
351 and nvl(ppm2.run_type_id,-9999) = nvl(p_run_type_id,-9999)
355 from pay_org_payment_methods_f opm
352 and (ppm2.priority <> 1
353 or exists
354 (select null
356 , pay_defined_balances db
357 , pay_balance_types bt
358 where opm.org_payment_method_id = ppm2.org_payment_method_id
359 and p_effective_date between
360 opm.effective_start_date and opm.effective_end_date
361 and db.defined_balance_id = opm.defined_balance_id
362 and bt.balance_type_id = db.balance_type_id
363 and bt.assignment_remuneration_flag = 'Y'
364 )
365 )
366 and (ppm2.effective_start_date >= p_effective_date or
367 p_effective_date between
368 ppm2.effective_start_date and ppm2.effective_end_date
369 ))
370 for update nowait;
371 --
372 l_proc varchar2(72) := g_package||'return_priority_date';
373 l_priority_date date := hr_api.g_eot;
374 --
375 begin
376 --
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 --
379 if p_priority is null then
380 hr_utility.set_message(801, 'HR_7357_PPM_PRIORITY_NULL');
381 hr_utility.raise_error;
382 end if;
383 if balance_remunerative(p_org_payment_method_id, p_effective_date) then
384 open ppm_sel;
385 fetch ppm_sel into l_priority_date;
386 close ppm_sel;
387 if (l_priority_date < p_validation_start_date) then
388 hr_utility.set_message(801, 'HR_6225_PAYM_DUP_PRIORITY');
389 hr_utility.raise_error;
390 end if;
391 end if;
392 hr_utility.set_location(' Leaving:'||l_proc, 10);
393 return(l_priority_date);
394 --
395 end return_priority_date;
396 --
397 -- Check that the assignment is for an employee before inserting a new
398 -- personal payment method.
399 --
400 procedure chk_assignment_type
401 (p_assignment_id in number
402 ,p_effective_date in date) is
403 --
404 l_type per_all_assignments_f.assignment_type%type;
405 l_proc varchar2(72) := g_package||'chk_assignment_type';
406 --
407 cursor csr_ass_type is
408 select asg.assignment_type
409 from per_all_assignments_f asg
410 where asg.assignment_id = p_assignment_id
411 and p_effective_date between asg.effective_start_date
412 and asg.effective_end_date;
413 --
414 begin
415 hr_utility.set_location('Entering:'|| l_proc, 1);
416 --
417 hr_utility.set_location(l_proc, 2);
418 open csr_ass_type;
419 fetch csr_ass_type
420 into l_type;
421 if l_type <> 'E' then
422 close csr_ass_type;
423 hr_utility.set_message(801, 'HR_7951_PPM_ASS_TYPE_NOT_EMP');
424 hr_utility.raise_error;
425 end if;
426 close csr_ass_type;
427 --
428 hr_utility.set_location(' Leaving:'|| l_proc, 5);
429 end chk_assignment_type;
430 --
431 begin
432 hr_utility.set_location('Entering:'||l_proc, 5);
433 --
434 -- check mandatory arguments
435 --
436 hr_api.mandatory_arg_error
437 (p_api_name => l_proc,
438 p_argument => 'assignment_id',
439 p_argument_value => p_assignment_id);
440 hr_api.mandatory_arg_error
441 (p_api_name => l_proc,
442 p_argument => 'business_group_id',
443 p_argument_value => p_business_group_id);
444 hr_api.mandatory_arg_error
445 (p_api_name => l_proc,
446 p_argument => 'org_payment_method_id',
447 p_argument_value => p_org_payment_method_id);
448 hr_api.mandatory_arg_error
449 (p_api_name => l_proc,
450 p_argument => 'datetrack_mode',
451 p_argument_value => p_datetrack_mode);
452 hr_api.mandatory_arg_error
453 (p_api_name => l_proc,
454 p_argument => 'effective_date',
455 p_argument_value => p_effective_date);
456 hr_api.mandatory_arg_error
457 (p_api_name => l_proc,
458 p_argument => 'validation_start_date',
459 p_argument_value => p_validation_start_date);
460 hr_api.mandatory_arg_error
461 (p_api_name => l_proc,
462 p_argument => 'validation_end_date',
463 p_argument_value => p_validation_end_date);
464 --
465 if (p_datetrack_mode = 'INSERT') then
466 chk_assignment_type
467 (p_assignment_id => p_assignment_id
468 ,p_effective_date => p_effective_date);
469 end if;
470 --
471 if (p_datetrack_mode = 'INSERT' or
472 p_datetrack_mode = 'DELETE_NEXT_CHANGE' or
473 p_datetrack_mode = 'FUTURE_CHANGE') then
474 hr_utility.set_location(' Leaving:'||l_proc, 10);
475 -- determine the least date
476 l_rtn_date := least(return_usage_date,
477 return_payee_date,
478 return_priority_date,
482 --
479 p_validation_end_date);
480 --
481 -- ensure that the returning date is not null
483 if l_rtn_date is null then
484 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
485 hr_utility.set_message_token('PROCEDURE', l_proc);
486 hr_utility.set_message_token('STEP','10');
487 hr_utility.raise_error;
488 end if;
489 hr_utility.set_location(' Leaving:'||l_proc, 15);
490 return(l_rtn_date);
491 else
492 hr_utility.set_location(' Leaving:'||l_proc, 20);
493 return(p_validation_end_date);
494 end if;
495 end return_effective_end_date;
496 --
497 -- ---------------------------------------------------------------------------
498 -- |-------------------< chk_org_payment_method_id >-----------------------|
499 -- ---------------------------------------------------------------------------
500 --
501 -- Description:
502 -- Checks the validity of the org_payment_method_id entered by carrying
503 -- out the following:
504 -- - check that the organisation payment method is valid for the
505 -- related payment type
506 -- Note this is an insert only procedure.
507 --
508 -- Pre-conditions:
509 -- None
510 --
511 -- In Arguments:
512 -- p_business_group_id
513 -- p_personal_payment_method_id
514 -- p_org_payment_method_id
515 -- p_assignment_id
516 -- p_effective_date
517 -- p_object_version_number
518 --
519 -- Post Success:
520 -- If the org_payment_method_id is valid then
521 -- processing continues
522 --
523 -- Post Failure:
524 -- If any of the following cases are true then
525 -- an application error will be raised and processing is terminated
526 --
527 -- a) the organization payment method is not valid for the related payment
528 -- type where the territory code matches the legislation of the business
529 -- group or where no territory code is specified (currently just
530 -- Cash) then
531 --
532 -- Access Status:
533 -- Internal Table Handler Use Only.
534 --
535 procedure chk_org_payment_method_id
536 (p_business_group_id in number
537 ,p_org_payment_method_id in number
538 ,p_effective_date in date) is
539 --
540 l_exists varchar2(1);
541 l_proc varchar2(72) := g_package||'chk_org_payment_method_id';
542 --
543 -- Bug 4644507. Removed the usage of per_business_groups from the cursor.
544 cursor csr_is_valid is
545 select null
546 from pay_org_payment_methods_f opm,
547 pay_payment_types ppt
548 where opm.org_payment_method_id = p_org_payment_method_id
549 and p_effective_date
550 between opm.effective_start_date
551 and opm.effective_end_date
552 and ppt.payment_type_id = opm.payment_type_id;
553 --
554 begin
555 --
556 hr_utility.set_location('Entering:'|| l_proc, 1);
557 --
558 -- Check mandatory parameters have been set
559 --
560 hr_api.mandatory_arg_error
561 (p_api_name => l_proc
562 ,p_argument => 'org_payment_method_id'
563 ,p_argument_value => p_org_payment_method_id);
564 --
565 hr_api.mandatory_arg_error
566 (p_api_name => l_proc
567 ,p_argument => 'effective_date'
568 ,p_argument_value => p_effective_date);
569 --
570 hr_utility.set_location(l_proc, 2);
571 open csr_is_valid;
572 fetch csr_is_valid into l_exists;
573 if csr_is_valid%notfound then
574 close csr_is_valid;
575 hr_utility.set_message(801, 'HR_7347_PPM_INVALID_PAY_TYPE');
576 hr_utility.raise_error;
577 end if;
578 close csr_is_valid;
579 --
580 hr_utility.set_location(' Leaving:'|| l_proc, 6);
581 end chk_org_payment_method_id;
582 --
583 -- ---------------------------------------------------------------------------
584 -- |----------------------< chk_defined_balance_id >-----------------------|
585 -- ---------------------------------------------------------------------------
586 --
587 procedure chk_defined_balance_id
588 (p_business_group_id in number,
589 p_assignment_id in number,
590 p_personal_payment_method_id in number,
591 p_org_payment_method_id in number,
592 p_effective_date in date,
593 p_object_version_number in number,
594 p_payee_type in varchar2,
595 p_payee_id in number) is
596 --
597 l_proc varchar2(72) := g_package||'chk_defined_balance_id';
598 l_exists varchar2(1);
599 l_api_updating boolean;
600 --
601 -- Check if the personal payment method is a garnishment.
602 --
603 cursor csr_chk_garnishment is
604 select null
605 from pay_org_payment_methods_f opm
606 where opm.org_payment_method_id = p_org_payment_method_id
607 and p_effective_date between opm.effective_start_date
608 and opm.effective_end_date
609 and opm.defined_balance_id is null;
610 --
611 -- Check the category of the payment.
612 --
613 cursor csr_chk_pay_type is
614 select pyt.category
615 from pay_org_payment_methods_f opm
616 , pay_payment_types pyt
620 and opm.effective_end_date;
617 where p_org_payment_method_id = opm.org_payment_method_id
618 and opm.payment_type_id = pyt.payment_type_id
619 and p_effective_date between opm.effective_start_date
621 --
622 -- Local Variables
623 --
624 l_category pay_payment_types.category%type;
625 --
626 --
627 -- -------------------------------------------------------------------------
628 -- |---------------------< chk_payee_id_and_type >-----------------------|
629 -- -------------------------------------------------------------------------
630 --
631 -- Description:
632 -- This procedure checks that, for garnshments, if PAYEE_TYPE is 'O', then
633 -- PAYEE_ID refers to a valid and active organization, in the same business
634 -- group as the personal payment method. If PAYEE_TYPE is 'P', then the
635 -- procedure checks that PAYEE_ID refers to a valid person and that this
636 -- person is a contact with a contact relationship to the owner of the
637 -- personal payment method, of the correct type for garnishments.
638 --
639 -- Pre-conditions:
640 -- None
641 --
642 -- In Arguments:
643 --
644 -- Post Success:
645 -- If PAYEE_TYPE is 'O' and PAYEE_ID refers to a valid organization which
646 -- is active and in the same business group as the personal payment
647 -- method, then processing continues.
648 -- If PAYEE_TYPE is 'P' and PAYEE_ID refers to a valid person, who is a
649 -- contact with a contact relationship to the owner of the personal
650 -- payment method, and of the correct type for garnishments, then
651 -- processing continues.
652 --
653 -- Post Failure:
654 -- If any of the following cases are true then an application error will
655 -- be raised and processing terminated:
656 --
657 -- a) PAYEE_TYPE is 'O' and PAYEE_ID is not a valid organization, or
658 -- a non-active organization or an organization not in the correct
659 -- business group.
660 --
661 -- b) PAYEE_TYPE is 'P' and PAYEE_ID is not a valid person or not a
662 -- contact with a contact relationship of the correct type.
663 --
664 -- c) PAYEE_TYPE is neither 'O' nor 'P'.
665 --
666 -- Access Status:
667 -- Internal Table Handler Use Only.
668 -- -------------------------------------------------------------------------
669 procedure chk_payee_id_and_type is
670 --
671 l_proc varchar2(72) := g_package||'chk_payee_id_and_type';
672 l_business_group_id number(15);
673 l_valid varchar2(1);
674 --
675 -- Check that the organization is valid and is in the same business group
676 -- as the personal payment method.
677 --
678 -- Bug 6617741 : Changed the cursor to fetch record from table
679 -- hr_all_organization_units instead of view hr_organization_units
680 -- to bypass the check for valid organization.
681
682 cursor csr_chk_organization is
683 select oru.business_group_id
684 from hr_all_organization_units oru
685 where oru.organization_id = p_payee_id
686 and p_effective_date between oru.date_from and
687 nvl(oru.date_to, hr_api.g_eot);
688 --
689 -- Check that the organization is active and of type 'HR_PAYEE'
690 --
691 cursor csr_chk_organization_active is
692 select null
693 from hr_organization_information ori
694 where ori.organization_id = p_payee_id
695 and ori.org_information_context = 'CLASS'
696 and ori.org_information1 = 'HR_PAYEE'
697 and ori.org_information2 = 'Y';
698 --
699 -- Check that the person is valid
700 --
701 cursor csr_chk_person is
702 select null
703 from per_people_f per
704 where per.person_id = p_payee_id
705 and p_effective_date between per.effective_start_date
706 and per.effective_end_date;
707 --
708 -- Check that the person is a contact, with a valid contact relationship
709 -- type
710 --
711 cursor csr_chk_contact is
712 select null
713 from per_contact_relationships ctr,
714 per_all_assignments_f asg
715 where ctr.contact_person_id = p_payee_id
716 and ctr.person_id = asg.person_id
717 and asg.assignment_id = p_assignment_id
718 and p_effective_date between asg.effective_start_date
719 and asg.effective_end_date
720 and ctr.third_party_pay_flag = 'Y';
721 --
722 begin
723 hr_utility.set_location('Entering:'||l_proc, 5);
724 --
725 -- Ensure that all mandatory arguments are not null.
726 --
727 hr_api.mandatory_arg_error
728 (p_api_name => l_proc
729 ,p_argument => 'business_group_id'
730 ,p_argument_value => p_business_group_id
731 );
732 --
733 hr_api.mandatory_arg_error
734 (p_api_name => l_proc
735 ,p_argument => 'assignment_id'
736 ,p_argument_value => p_assignment_id
737 );
738 --
739 -- If PAYEE_TYPE is 'O' then check that PAYEE_ID refers to a valid
740 -- organization, and that this organization is in the same business group
741 -- as the personal payment method.
742 --
746 fetch csr_chk_organization into l_business_group_id;
743 if p_payee_type = 'O' then
744 hr_utility.set_location(l_proc, 10);
745 open csr_chk_organization;
747 if csr_chk_organization%notfound then
748 -- Error: invalid organization
749 close csr_chk_organization;
750 hr_utility.set_message(801, 'HR_7839_PPM_INV_ORG');
751 hr_utility.raise_error;
752 end if;
753 close csr_chk_organization;
754 -- check the business group
755 if l_business_group_id <> p_business_group_id then
756 -- Error: organization is not in the correct business group
757 hr_utility.set_message(801, 'HR_7844_ORG_INV_BUS_GRP');
758 hr_utility.raise_error;
759 end if;
760 --
761 -- check that the organization is active
762 --
763 open csr_chk_organization_active;
764 fetch csr_chk_organization_active into l_valid;
765 if csr_chk_organization_active%notfound then
766 -- Error: the organization is not active
767 close csr_chk_organization_active;
768 hr_utility.set_message(801, 'HR_7843_PPM_ORG_NOT_ACTIVE');
769 hr_utility.raise_error;
770 end if;
771 close csr_chk_organization_active;
772 --
773 -- If PAYEE_TYPE is 'P' then ensure that PAYEE_ID refers to a valid
774 -- person and that this person is a contact with a contact relationship
775 -- to the owner of the personal payment method, and with the third party
776 -- pay flag set to 'Yes'.
777 --
778 elsif p_payee_type = 'P' then
779 hr_utility.set_location(l_proc, 15);
780 open csr_chk_person;
781 fetch csr_chk_person into l_valid;
782 if csr_chk_person%notfound then
783 close csr_chk_person;
784 hr_utility.set_message(801, 'HR_7846_INV_PERSON');
785 hr_utility.raise_error;
786 end if;
787 close csr_chk_person;
788 --
789 open csr_chk_contact;
790 hr_utility.set_location(l_proc, 20);
791 fetch csr_chk_contact into l_valid;
792 if csr_chk_contact%notfound then
793 close csr_chk_contact;
794 hr_utility.set_message(801, 'HR_7847_PPM_INV_CONTACT');
795 hr_utility.raise_error;
796 end if;
797 close csr_chk_contact;
798 --
799 -- PAYEE_TYPE is invalid
800 --
801 else
802 hr_utility.set_message(801, 'HR_7848_PPM_INV_PAYEE_TYPE');
803 hr_utility.raise_error;
804 end if;
805 hr_utility.set_location('Leaving:'||l_proc, 25);
806 end chk_payee_id_and_type;
807 --
808 begin
809 hr_utility.set_location('Entering:'||l_proc, 5);
810 --
811 -- Ensure that all mandatory arguments are not null.
812 --
813 hr_api.mandatory_arg_error
814 (p_api_name => l_proc
815 ,p_argument => 'org_payment_method_id'
816 ,p_argument_value => p_org_payment_method_id
817 );
818 --
819 hr_api.mandatory_arg_error
820 (p_api_name => l_proc
821 ,p_argument => 'effective_date'
822 ,p_argument_value => p_effective_date
823 );
824 --
825 -- If the personal payment method is a garnishment then check that
826 -- PAYEE_ID and PAYEE_TYPE are both not null.
827 -- If the personal payment method is not a garnishment (i.e. if the cursor
828 -- returns zero rows) then ensure that PAYEE_ID and PAYEE_TYPE are both
829 -- null.
830 --
831 l_api_updating := pay_ppm_shd.api_updating
832 (p_personal_payment_method_id => p_personal_payment_method_id
833 ,p_effective_date => p_effective_date
834 ,p_object_version_number => p_object_version_number);
835 --
836 if (l_api_updating and
837 nvl(pay_ppm_shd.g_old_rec.payee_id, hr_api.g_number) <>
838 nvl(p_payee_id, hr_api.g_number)) or
839 (l_api_updating and
840 nvl(pay_ppm_shd.g_old_rec.payee_type, hr_api.g_varchar2) <>
841 nvl(p_payee_type, hr_api.g_varchar2)) or
842 (not l_api_updating) then
843 open csr_chk_garnishment;
844 fetch csr_chk_garnishment into l_exists;
845 if csr_chk_garnishment%notfound then
846 close csr_chk_garnishment;
847 -- a garnishment does not exist therefore we must ensure that
848 -- the p_payee_id and p_payee_type argument are both null
849 if NOT (p_payee_id is null and
850 p_payee_type is null) then
851 hr_utility.set_message(801, 'HR_7820_PPM_INV_PAYEE_DETAILS');
852 hr_utility.raise_error;
853 end if;
854 else
855 close csr_chk_garnishment;
856 -- a garnishment does exist therefore we must ensure that
857 -- the p_payee_id and p_payee_type argument are both not null
858 if (p_payee_id is not null and
859 p_payee_type is not null) then
860 -- check the payee_id and type
861 chk_payee_id_and_type;
862 else
863 -- The error message is restricted for magnetic type payments
864 -- Bug 6439573
865 -- and cheque payment type : Bug 6928340
866 open csr_chk_pay_type;
867 fetch csr_chk_pay_type into l_category;
868 close csr_chk_pay_type;
869 if (l_category <> 'MT' and l_category <> 'CH') then
870 hr_utility.set_message(801, 'HR_7822_PPM_NO_PAYEE_DETAILS');
874 end if;
871 hr_utility.raise_error;
872 end if;
873 end if;
875 end if;
876 --
877 hr_utility.set_location('Leaving:'||l_proc, 10);
878 end chk_defined_balance_id;
879 --
880 -- ---------------------------------------------------------------------------
881 -- |---------------------< chk_amount_percent >----------------------------|
882 -- ---------------------------------------------------------------------------
883 --
884 procedure chk_amount_percent
885 (p_amount in
886 pay_personal_payment_methods_f.amount%TYPE
887 ,p_percentage in
888 pay_personal_payment_methods_f.percentage%TYPE
889 ,p_personal_payment_method_id in
890 pay_personal_payment_methods_f.personal_payment_method_id%TYPE
891 ,p_org_payment_method_id in
892 pay_personal_payment_methods_f.org_payment_method_id%TYPE
893 ,p_effective_date in date
894 ,p_object_version_number in
895 pay_personal_payment_methods_f.object_version_number%TYPE) is
896 --
897 l_exists varchar2(1);
898 l_proc varchar2(72) := g_package||'chk_amount_percent';
899 l_api_updating boolean;
900 l_amount number(38);
901 l_percentage number(10);
902 l_curcode varchar2(15);
903 --
904 -- Currency code for monetary amount comes from the balance.
905 cursor get_curcode is
906 select bt.currency_code
907 from pay_org_payment_methods_f opm
908 , pay_defined_balances db
909 , pay_balance_types bt
910 where org_payment_method_id = p_org_payment_method_id
911 and p_effective_date between
912 opm.effective_start_date and opm.effective_end_date
913 and db.defined_balance_id = opm.defined_balance_id
914 and bt.balance_type_id = db.balance_type_id
915 ;
916 --
917 begin
918 hr_utility.set_location('Entering:'|| l_proc, 1);
919 --
920 -- Check mandatory parameters have been set
921 --
922 hr_api.mandatory_arg_error
923 (p_api_name => l_proc
924 ,p_argument => 'org payment method id'
925 ,p_argument_value => p_org_payment_method_id
926 );
927 --
928 hr_api.mandatory_arg_error
929 (p_api_name => l_proc
930 ,p_argument => 'effective date'
931 ,p_argument_value => p_effective_date
932 );
933 --
934 -- Only proceed with validation if:
935 -- a) The current g_old_rec is current and
936 -- b) The amount or percentage values have changed
937 --
938 l_api_updating := pay_ppm_shd.api_updating
939 (p_personal_payment_method_id => p_personal_payment_method_id
940 ,p_effective_date => p_effective_date
941 ,p_object_version_number => p_object_version_number);
942 --
943 if ((l_api_updating and nvl(pay_ppm_shd.g_old_rec.amount,hr_api.g_number)
944 <> nvl(p_amount,hr_api.g_number)) or
945 (l_api_updating and nvl(pay_ppm_shd.g_old_rec.percentage,hr_api.g_number)
946 <> nvl(p_percentage,hr_api.g_number)) or
947 (NOT l_api_updating)) then
948 hr_utility.set_location(l_proc, 2);
949 --
950 -- Check the related balance type
951 --
952 if not balance_remunerative(p_org_payment_method_id, p_effective_date) then
953 --
954 -- the related balance type is non-remunerative
955 --
956 if p_amount is not null then
957 -- Error: Amount not enterable
958 hr_utility.set_message(801, 'HR_7349_PPM_AMOUNT_NOT_NULL');
959 hr_utility.raise_error;
960 end if;
961 if p_percentage <> 100 then
962 -- Error: Percentage error
963 hr_utility.set_message(801, 'HR_7354_PPM_PERCENT_NOT_100');
964 hr_utility.raise_error;
965 end if;
966 else
967 --
968 hr_utility.set_location(l_proc, 3);
969 --
970 -- When the related balance type is remunerative
971 --
972 -- Check that percentage and amount are not both not null or both null
973 --
974 if p_percentage is not null then
975 if p_amount is not null then
976 -- Error: One and only one of amount or percentage need to be entered
977 hr_utility.set_message(801, 'HR_6221_PAYM_INVALID_PPM');
978 hr_utility.raise_error;
979 end if;
980 elsif p_amount is null then
981 -- Error: Either amount or percentage need to be entered
982 hr_utility.set_message(801, 'HR_6680_PPM_AMT_PERC');
983 hr_utility.raise_error;
984 end if;
985 --
986 hr_utility.set_location(l_proc, 5);
987 --
988 -- Check if the amount is less than 0
989 --
990 if p_amount < 0 then
991 -- Error: Amount less than 0
992 hr_utility.set_message(801, 'HR_7355_PPM_AMOUNT_NEGATIVE');
993 hr_utility.raise_error;
994 end if ;
995 --
996 hr_utility.set_location(l_proc, 6);
997 --
998 -- Check if the percentage is between 0 and 100
999 --
1000 if p_percentage not between 0 and 100 then
1001 -- Error: Percentage must be between 0 and 100
1002 hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
1003 hr_utility.raise_error;
1004 end if ;
1005 --
1009 --
1006 end if ;
1007 --
1008 hr_utility.set_location(l_proc, 7);
1010 if p_amount is not null then
1011 --
1012 -- Check that Amount has a money format
1013 --
1014 l_amount := to_char(p_amount);
1015 open get_curcode;
1016 fetch get_curcode into l_curcode;
1017 close get_curcode;
1018 --
1019 hr_dbchkfmt.is_db_format
1020 (p_value => l_amount,
1021 p_arg_name => 'AMOUNT',
1022 p_format => 'M',
1023 p_curcode => l_curcode);
1024 else
1025 hr_utility.set_location(l_proc, 8);
1026 --
1027 -- p_percentage is not null so check that format is decimal with
1028 -- 2 decimal places
1029 --
1030 l_percentage := to_char(p_percentage);
1031 --
1032 hr_dbchkfmt.is_db_format
1033 (p_value => l_percentage,
1034 p_arg_name => 'PERCENTAGE',
1035 -- p_format => 'INTEGER');
1036 p_format => 'H_DECIMAL2');
1037 end if;
1038 end if ;
1039 --
1040 hr_utility.set_location(' Leaving:'|| l_proc, 9);
1041 end chk_amount_percent;
1042 --
1043 -- ---------------------------------------------------------------------------
1044 -- |---------------------< chk_external_account_id >------------------------|
1045 -- ---------------------------------------------------------------------------
1046 --
1047 procedure chk_external_account_id
1048 (p_personal_payment_method_id in
1049 pay_personal_payment_methods_f.personal_payment_method_id%TYPE
1050 ,p_org_payment_method_id in
1051 pay_personal_payment_methods_f.org_payment_method_id%TYPE
1052 ,p_external_account_id in number
1053 ,p_effective_date in date
1054 ,p_object_version_number in
1055 pay_personal_payment_methods_f.object_version_number%TYPE) is
1056 --
1057 l_exists varchar2(1);
1058 l_proc varchar2(72) := g_package||'chk_external_account_id';
1059 l_api_updating boolean;
1060 --
1061 -- Check if related payment type is Magnetic Tape
1062 --
1063 cursor csr_chk_pay_type is
1064 select null
1065 from pay_org_payment_methods_f opm
1066 , pay_payment_types pyt
1067 where p_org_payment_method_id = opm.org_payment_method_id
1068 and opm.payment_type_id = pyt.payment_type_id
1069 and p_effective_date between opm.effective_start_date
1070 and opm.effective_end_date
1071 and pyt.category = 'MT';
1072 --
1073 -- Check if external_account_id exists on pay_external_accounts
1074 --
1075 cursor csr_chk_ext_acct_id is
1076 select null
1077 from pay_external_accounts pea
1078 where pea.external_account_id = p_external_account_id;
1079 --
1080 -- Check that the flex structure for the external account matches
1081 -- the flex structure already defined for the opm.
1082 --
1083 cursor chk_org_flex_struct is
1084 select null
1085 from pay_external_accounts pea1,
1086 pay_external_accounts pea2,
1087 pay_org_payment_methods_f opm
1088 where pea1.external_account_id = p_external_account_id
1089 and opm.org_payment_method_id = p_org_payment_method_id
1090 and opm.external_account_id = pea2.external_account_id
1091 and pea1.id_flex_num = pea2.id_flex_num
1092 and exists
1093 (select null
1094 from pay_legislation_rules
1095 where to_char(pea1.id_flex_num) = rule_mode
1096 and rule_type ='E');
1097 --
1098 begin
1099 hr_utility.set_location('Entering:'|| l_proc, 1);
1100 --
1101 -- Check mandatory parameters have been set
1102 --
1103 hr_api.mandatory_arg_error
1104 (p_api_name => l_proc
1105 ,p_argument => 'org payment method id'
1106 ,p_argument_value => p_org_payment_method_id
1107 );
1108 --
1109 hr_api.mandatory_arg_error
1110 (p_api_name => l_proc
1111 ,p_argument => 'effective date'
1112 ,p_argument_value => p_effective_date
1113 );
1114 --
1115 -- Only proceed with validation if:
1116 -- a) The current g_old_rec is current and
1117 -- b) The external account id value has changed
1118 --
1119 l_api_updating := pay_ppm_shd.api_updating
1120 (p_personal_payment_method_id => p_personal_payment_method_id
1121 ,p_effective_date => p_effective_date
1122 ,p_object_version_number => p_object_version_number);
1123 --
1124 if ((l_api_updating and nvl(pay_ppm_shd.g_old_rec.external_account_id,
1125 hr_api.g_number)
1126 <> nvl(p_external_account_id,hr_api.g_number)) or
1127 (NOT l_api_updating)) then
1128 hr_utility.set_location(l_proc, 2);
1129 --
1130 -- Check if related payment type is Magnetic Tape
1131 --
1132 open csr_chk_pay_type;
1133 fetch csr_chk_pay_type into l_exists;
1134 if csr_chk_pay_type%found then
1135 --
1136 -- related payment type is Magnetic Tape
1137 --
1138 if p_external_account_id is null then
1139 close csr_chk_pay_type;
1140 -- Error: Bank details needed for magnetic tape payment types
1141 hr_utility.set_message(801, 'HR_6678_PPM_MT_BANK');
1142 hr_utility.raise_error;
1143 end if;
1144 else
1145 --
1149 close csr_chk_pay_type;
1146 -- related payment type is not Magnetic Tape
1147 --
1148 if p_external_account_id is not null then
1150 -- Error: External account not enterable
1151 hr_utility.set_message(801, 'HR_7356_PPM_EXT_ACC_NOT_NULL');
1152 hr_utility.raise_error;
1153 end if;
1154 end if;
1155 close csr_chk_pay_type;
1156 --
1157 hr_utility.set_location(l_proc, 3);
1158 --
1159 -- Check if foreign key constraint error is violated
1160 --
1161 if p_external_account_id is not null then
1162 open csr_chk_ext_acct_id;
1163 fetch csr_chk_ext_acct_id into l_exists;
1164 if csr_chk_ext_acct_id%notfound then
1165 close csr_chk_ext_acct_id;
1166 pay_ppm_shd.constraint_error
1167 (p_constraint_name => 'PAY_PERSONAL_PAYMENT_METHO_FK2');
1168 else
1169 close csr_chk_ext_acct_id;
1170 end if;
1171 end if;
1172 --
1173 -- Check that the flex structure for the external account matches
1174 -- the flex structure already defined for the opm.
1175 --
1176 if p_external_account_id is not null then
1177 --
1178 open chk_org_flex_struct;
1179 fetch chk_org_flex_struct into l_exists;
1180 if chk_org_flex_struct%notfound then
1181 close chk_org_flex_struct;
1182 -- Error: PPM external account structure does not exist
1183 hr_utility.set_message(801, 'HR_51350_PPM_EXT_ACC_STRUC');
1184 hr_utility.raise_error;
1185 else
1186 close chk_org_flex_struct;
1187 end if;
1188 --
1189 end if;
1190 --
1191 end if;
1192 --
1193 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1194 end chk_external_account_id;
1195 --
1196 -- ---------------------------------------------------------------------------
1197 -- |---------------------------< chk_priority >----------------------------|
1198 -- ---------------------------------------------------------------------------
1199 --
1200 procedure chk_priority
1201 (p_priority in
1202 pay_personal_payment_methods_f.priority%TYPE
1203 ,p_personal_payment_method_id in
1204 pay_personal_payment_methods_f.personal_payment_method_id%TYPE
1205 ,p_org_payment_method_id in
1206 pay_personal_payment_methods_f.org_payment_method_id%TYPE
1207 ,p_assignment_id in
1208 pay_personal_payment_methods_f.assignment_id%TYPE
1209 ,p_run_type_id in
1210 pay_personal_payment_methods_f.run_type_id%TYPE
1211 ,p_effective_date in date
1212 ,p_object_version_number in
1213 pay_personal_payment_methods_f.object_version_number%TYPE
1214 ,p_validation_start_date in date
1215 ,p_validation_end_date in date) is
1216 --
1217 l_exists varchar2(1);
1218 l_proc varchar2(72) := g_package||'chk_priority';
1219 l_api_updating boolean;
1220
1221 -- Check if the related priority is unique within validation start date
1222 -- and validation end date. Note: the SQL only includes remunerative pay
1223 -- methods in the check.
1224
1225 cursor csr_check_unique is
1226 select null
1227 from pay_personal_payment_methods_f ppm
1228 where ppm.priority = p_priority
1229 and ppm.assignment_id = p_assignment_id
1230 and nvl(ppm.run_type_id,-9999) = nvl(p_run_type_id,-9999)
1231 and (ppm.personal_payment_method_id <> p_personal_payment_method_id
1232 or p_personal_payment_method_id is null)
1233 and (ppm.priority <> 1
1234 or exists
1235 (select null
1236 from pay_org_payment_methods_f opm
1237 , pay_defined_balances db
1238 , pay_balance_types bt
1239 where opm.org_payment_method_id = ppm.org_payment_method_id
1240 and p_effective_date between
1241 opm.effective_start_date and opm.effective_end_date
1242 and db.defined_balance_id = opm.defined_balance_id
1243 and bt.balance_type_id = db.balance_type_id
1244 and bt.assignment_remuneration_flag = 'Y'
1245 )
1246 )
1247 and ppm.effective_start_date <= p_validation_end_date
1248 and ppm.effective_end_date >= p_validation_start_date;
1249 --
1250 begin
1251 hr_utility.set_location('Entering:'|| l_proc, 1);
1252 --
1253 -- Check mandatory parameters have been set
1254 --
1255 hr_api.mandatory_arg_error
1256 (p_api_name => l_proc
1257 ,p_argument => 'org_payment_method_id'
1258 ,p_argument_value => p_org_payment_method_id
1259 );
1260 --
1261 hr_api.mandatory_arg_error
1262 (p_api_name => l_proc
1263 ,p_argument => 'assignment_id'
1264 ,p_argument_value => p_assignment_id
1265 );
1266 --
1267 hr_api.mandatory_arg_error
1268 (p_api_name => l_proc
1269 ,p_argument => 'effective_date'
1270 ,p_argument_value => p_effective_date
1271 );
1272 --
1273 -- Only proceed with validation if:
1274 -- a) The current g_old_rec is current and
1275 -- b) The priority value has changed
1276 --
1277 l_api_updating := pay_ppm_shd.api_updating
1281 --
1278 (p_personal_payment_method_id => p_personal_payment_method_id
1279 ,p_effective_date => p_effective_date
1280 ,p_object_version_number => p_object_version_number);
1282 if ((l_api_updating and nvl(pay_ppm_shd.g_old_rec.priority,hr_api.g_number)
1283 <> nvl(p_priority,hr_api.g_number)) or
1284 (NOT l_api_updating)) then
1285 hr_utility.set_location(l_proc, 2);
1286 --
1287 -- Check if priority is null
1288 --
1289 if p_priority is null then
1290 -- Error: Priority required
1291 hr_utility.set_message(801, 'HR_7357_PPM_PRIORITY_NULL');
1292 hr_utility.raise_error;
1293 end if;
1294 --
1295 -- Check if the related balance type is remunerative
1296 --
1297 if balance_remunerative(p_org_payment_method_id, p_effective_date) then
1298 --
1299 -- Check that priority is between 1 and 99
1300 -- note: this could be coded using the API version of checkformat
1301 --
1302 if p_priority not between 1 and 99 then
1303 -- Error: Priority out of range
1304 hr_utility.Set_message(801, 'HR_7358_PPM_PRIORITY_RANGE');
1305 hr_utility.raise_error;
1306 end if;
1307 --
1308 hr_utility.set_location(l_proc, 3);
1309 if l_api_updating then
1310 --
1311 -- As we are updating we validate the priority.
1312 -- We do not need to do this for INSERT because the
1313 -- process: pay_ppm_bus.return_effective_date has already
1314 -- completed the check
1315 --
1316 open csr_check_unique;
1317 fetch csr_check_unique into l_exists;
1318 if csr_check_unique%found then
1319 close csr_check_unique;
1320 -- Error: A payment method with this priority exists for this
1321 -- assignment
1322 hr_utility.set_message(801, 'HR_6225_PAYM_DUP_PRIORITY');
1323 hr_utility.raise_error;
1324 end if;
1325 close csr_check_unique;
1326 end if;
1327 else
1328 --
1329 hr_utility.set_location(l_proc, 4);
1330 --
1331 -- Balance Type is non_remunerative
1332 --
1333 if p_priority <> 1 then
1334 -- Error: Priority must be 1
1335 hr_utility.set_message(801, 'HR_7359_PPM_MUST_BE_1');
1336 hr_utility.raise_error;
1337 end if;
1338 end if;
1339 --
1340 end if;
1341 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1342 end chk_priority;
1343 --
1344 -- ---------------------------------------------------------------------------
1345 -- |------------------------< chk_delete >----------------------------|
1346 -- ---------------------------------------------------------------------------
1347 --
1348 -- Description:
1349 -- Validates that rows may be deleted from pay_personal_payment_methods_f
1350 --
1351 -- Pre-conditions:
1352 -- None
1353 --
1354 -- In Arguments:
1355 -- p_personal_payment_method_id
1356 -- p_effective_date
1357 -- p_datetrack_mode
1358 --
1359 -- Post Success:
1360 -- Processing continues
1361 --
1362 -- Post Failure:
1363 -- If any of the following cases are true then
1364 -- an application error will be raised and processing is terminated
1365 --
1366 -- a) If delete mode is DELETE (ie: set end date)
1367 -- and rows exist in PAY_PRE_PAYMENTS for PAY_PAYROLL_ACTION
1368 -- effective dates that are effective beyond the session date
1369 --
1370 -- b) If delete mode is ZAP (ie: remove all records)
1371 -- and rows exist in PAY_PRE_PAYMENTS
1372 --
1373 -- c) If delete mode is DELETE, the personal payment method is to a
1374 -- third party payee and the new end date is earlier than any element
1375 -- entry that references it.
1376 --
1377 -- d) If delete mode is ZAP, the personal payment method is to a third
1378 -- party payee and is used by an element entry.
1379 --
1380 -- Access Status:
1381 -- Internal Table Handler Use Only.
1382 --
1383 procedure chk_delete
1384 (p_personal_payment_method_id in
1385 pay_personal_payment_methods_f.personal_payment_method_id%TYPE
1386 ,p_effective_date in date
1387 ,p_datetrack_mode in varchar2
1388 ,p_validation_start_date in date
1389 ,p_validation_end_date in date) is
1390 --
1391 l_exists varchar2(1);
1392 l_proc varchar2(72) := g_package||'chk_delete';
1393 --
1394 -- check if rows exist in PAY_PRE_PAYMENTS for PAY_PAYROLL_ACTION
1395 -- effective dates that are effective beyond the session date
1396 cursor csr_date_eff is
1397 select null
1398 from pay_pre_payments ppy
1399 , pay_assignment_actions asa
1400 , pay_payroll_actions pra
1401 where p_personal_payment_method_id = ppy.personal_payment_method_id
1402 and ppy.assignment_action_id = asa.assignment_action_id
1403 and asa.payroll_action_id = pra.payroll_action_id
1404 and pra.effective_date > p_effective_date;
1405 --
1406 -- check if rows exist in PAY_PRE_PAYMENTS
1407 --
1408 cursor csr_del is
1409 select null
1410 from pay_pre_payments ppy
1411 where p_personal_payment_method_id = ppy.personal_payment_method_id;
1412 --
1416 -- For garnishments, disallow a date-effective delete or zap if the personal
1413 procedure check_garnishment_delete is
1414 l_proc varchar2(72) := g_package||'check_garnishment_delete';
1415 --
1417 -- payment method is referenced by at least one element entry.
1418 --
1419 cursor csr_del_garnishment is
1420 select null
1421 from pay_personal_payment_methods_f ppm,
1422 pay_element_entries_f ele,
1423 pay_org_payment_methods_f opm
1424 where ppm.personal_payment_method_id = p_personal_payment_method_id
1425 and p_effective_date between ppm.effective_start_date
1426 and ppm.effective_end_date
1427 and ppm.org_payment_method_id = opm.org_payment_method_id
1428 and p_effective_date between opm.effective_start_date
1429 and opm.effective_end_date
1430 and opm.defined_balance_id is null
1431 and ele.personal_payment_method_id = ppm.personal_payment_method_id
1432 and ele.effective_start_date <= p_validation_start_date
1433 and ele.effective_end_date >= p_validation_end_date;
1434 --
1435 begin
1436 hr_utility.set_location('Entering:'|| l_proc, 5);
1437 open csr_del_garnishment;
1438 fetch csr_del_garnishment into l_exists;
1439 if csr_del_garnishment%found then
1440 close csr_del_garnishment;
1441 -- Error: Delete not allowed
1442 hr_utility.set_message(801, 'HR_7849_PPM_ELE_DELETE');
1443 hr_utility.raise_error;
1444 end if;
1445 close csr_del_garnishment;
1446 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1447 end check_garnishment_delete;
1448 --
1449 begin
1450 hr_utility.set_location('Entering:'|| l_proc, 1);
1451 --
1452 -- Check mandatory parameters have been set
1453 --
1454 hr_api.mandatory_arg_error
1455 (p_api_name => l_proc
1456 ,p_argument => 'effective_date'
1457 ,p_argument_value => p_effective_date
1458 );
1459 --
1460 hr_api.mandatory_arg_error
1461 (p_api_name => l_proc
1462 ,p_argument => 'datetrack_mode'
1463 ,p_argument_value => p_datetrack_mode
1464 );
1465 --
1466 hr_utility.set_location(l_proc, 2);
1467 --
1468 -- If delete mode is DELETE (ie: set end date) then
1469 -- check if rows exist in PAY_PRE_PAYMENTS for PAY_PAYROLL_ACTION
1470 -- effective dates that are effective beyond the session date
1471 --
1472 if p_datetrack_mode = 'DELETE' then
1473 open csr_date_eff;
1474 fetch csr_date_eff into l_exists;
1475 if csr_date_eff%found then
1476 close csr_date_eff;
1477 -- Error: Delete not allowed
1478 hr_utility.set_message(801, 'HR_7360_PPM_DEL_NOT_ALLOWED');
1479 hr_utility.raise_error;
1480 end if;
1481 close csr_date_eff;
1482 --
1483 check_garnishment_delete;
1484 end if;
1485 --
1486 hr_utility.set_location(l_proc, 3);
1487 --
1488 -- If delete mode is ZAP (ie: remove all records) then
1489 -- check if rows exist in PAY_PRE_PAYMENTS
1490 --
1491 if p_datetrack_mode = 'ZAP' then
1492 open csr_del;
1493 fetch csr_del into l_exists;
1494 if csr_del%found then
1495 close csr_del;
1496 -- Error:Cannot delete. Pre payments exist
1497 hr_utility.set_message(801, 'HR_6679_PPM_PRE_PAY');
1498 hr_utility.raise_error;
1499 end if;
1500 close csr_del;
1501 --
1502 check_garnishment_delete;
1503 end if;
1504 --
1505 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1506 end chk_delete;
1507 --
1508 -- ----------------------------------------------------------------------------
1509 -- |----------------------< check_non_updateable_args >-----------------------|
1510 -- ----------------------------------------------------------------------------
1511 --
1512 Procedure check_non_updateable_args(p_rec in pay_ppm_shd.g_rec_type
1513 ,p_effective_date in date) is
1514 --
1515 l_proc varchar2(72) := g_package||'check_non_updateable_args';
1516 l_error exception;
1517 l_argument varchar2(30);
1518 --
1519 Begin
1520 hr_utility.set_location('Entering:'||l_proc, 5);
1521 --
1522 -- Only proceed with validation if a row exists for
1523 -- the current record in the HR Schema
1524 --
1525 if not pay_ppm_shd.api_updating
1526 (p_personal_payment_method_id => p_rec.personal_payment_method_id
1527 ,p_effective_date => p_effective_date
1528 ,p_object_version_number => p_rec.object_version_number) then
1529 hr_utility.set_message(801, 'HR_51351_PPM_UPD_ROW_NOT_EXIST');
1530 hr_utility.set_message_token('PROCEDURE', l_proc);
1531 hr_utility.set_message_token('STEP', '5');
1532 end if;
1533 --
1534 hr_utility.set_location(l_proc, 6);
1535 --
1536 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1537 nvl(pay_ppm_shd.g_old_rec.business_group_id, hr_api.g_number) then
1538 l_argument := 'business_group_id';
1539 raise l_error;
1540 end if;
1541 hr_utility.set_location(l_proc, 7);
1542 --
1543 if p_rec.assignment_id <> pay_ppm_shd.g_old_rec.assignment_id then
1544 l_argument := 'assignment_id';
1545 raise l_error;
1546 end if;
1547 hr_utility.set_location(l_proc, 8);
1551 l_argument := 'org_payment_method_id';
1548 --
1549 if nvl(p_rec.org_payment_method_id, hr_api.g_number) <>
1550 nvl(pay_ppm_shd.g_old_rec.org_payment_method_id, hr_api.g_number) then
1552 raise l_error;
1553 end if;
1554 hr_utility.set_location(l_proc, 9);
1555 --
1556 exception
1557 when l_error then
1558 hr_api.argument_changed_error
1559 (p_api_name => l_proc
1560 ,p_argument => l_argument);
1561 when others then
1562 raise;
1563 hr_utility.set_location(' Leaving:'||l_proc, 10);
1564 end check_non_updateable_args;
1565 --
1566 -- ----------------------------------------------------------------------------
1567 -- |--------------------------< dt_update_validate >--------------------------|
1568 -- ----------------------------------------------------------------------------
1569 -- {Start Of Comments}
1570 --
1571 -- Description:
1572 -- This procedure is used for referential integrity of datetracked
1573 -- parent entities when a datetrack update operation is taking place
1574 -- and where there is no cascading of update defined for this entity.
1575 --
1576 -- Pre Conditions:
1577 -- This procedure is called from the update_validate.
1578 --
1579 -- In Arguments:
1580 --
1581 -- Post Success:
1582 -- Processing continues.
1583 --
1584 -- Post Failure:
1585 --
1586 -- Developer Implementation Notes:
1587 -- This procedure should not need maintenance unless the HR Schema model
1588 -- changes.
1589 --
1590 -- Access Status:
1591 -- Internal Table Handler Use Only.
1592 --
1593 -- {End Of Comments}
1594 -- ----------------------------------------------------------------------------
1595 Procedure dt_update_validate
1596 (p_org_payment_method_id in number default hr_api.g_number,
1597 p_assignment_id in number default hr_api.g_number,
1598 p_datetrack_mode in varchar2,
1599 p_validation_start_date in date,
1600 p_validation_end_date in date) Is
1601 --
1602 l_proc varchar2(72) := g_package||'dt_update_validate';
1603 l_integrity_error Exception;
1604 l_table_name all_tables.table_name%TYPE;
1605 --
1606 Begin
1607 hr_utility.set_location('Entering:'||l_proc, 5);
1608 --
1609 -- Ensure that the p_datetrack_mode argument is not null
1610 --
1611 hr_api.mandatory_arg_error
1612 (p_api_name => l_proc,
1613 p_argument => 'datetrack_mode',
1614 p_argument_value => p_datetrack_mode);
1615 --
1616 -- Only perform the validation if the datetrack update mode is valid
1617 --
1618 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1619 --
1620 --
1621 -- Ensure the arguments are not null
1622 --
1623 hr_api.mandatory_arg_error
1624 (p_api_name => l_proc,
1625 p_argument => 'validation_start_date',
1626 p_argument_value => p_validation_start_date);
1627 --
1628 hr_api.mandatory_arg_error
1629 (p_api_name => l_proc,
1630 p_argument => 'validation_end_date',
1631 p_argument_value => p_validation_end_date);
1632 --
1633 If ((nvl(p_org_payment_method_id, hr_api.g_number) <> hr_api.g_number) and
1634 NOT (dt_api.check_min_max_dates
1635 (p_base_table_name => 'pay_org_payment_methods_f',
1636 p_base_key_column => 'org_payment_method_id',
1637 p_base_key_value => p_org_payment_method_id,
1638 p_from_date => p_validation_start_date,
1639 p_to_date => p_validation_end_date))) Then
1640 l_table_name := 'org payment methods';
1641 Raise l_integrity_error;
1642 End If;
1643 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
1644 NOT (dt_api.check_min_max_dates
1645 (p_base_table_name => 'per_all_assignments_f',
1646 p_base_key_column => 'assignment_id',
1647 p_base_key_value => p_assignment_id,
1648 p_from_date => p_validation_start_date,
1649 p_to_date => p_validation_end_date))) Then
1650 l_table_name := 'assignments';
1651 Raise l_integrity_error;
1652 End If;
1653 --
1654 End If;
1655 --
1656 hr_utility.set_location(' Leaving:'||l_proc, 10);
1657 Exception
1658 When l_integrity_error Then
1659 --
1660 -- A referential integrity check was violated therefore
1661 -- we must error
1662 --
1663 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1664 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1665 hr_utility.raise_error;
1666 When Others Then
1667 --
1668 -- An unhandled or unexpected error has occurred which
1669 -- we must report
1670 --
1671 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1672 hr_utility.set_message_token('PROCEDURE', l_proc);
1673 hr_utility.set_message_token('STEP','15');
1674 hr_utility.raise_error;
1675 End dt_update_validate;
1676 --
1677 -- ----------------------------------------------------------------------------
1678 -- |--------------------------< dt_delete_validate >--------------------------|
1679 -- ----------------------------------------------------------------------------
1680 -- {Start Of Comments}
1681 --
1682 -- Description:
1686 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1683 -- This procedure is used for referential integrity of datetracked
1684 -- child entities when either a datetrack DELETE or ZAP is in operation
1685 -- and where there is no cascading of delete defined for this entity.
1687 -- datetracked child rows exist between the validation start and end
1688 -- dates.
1689 --
1690 -- Pre Conditions:
1691 -- This procedure is called from the delete_validate.
1692 --
1693 -- In Arguments:
1694 --
1695 -- Post Success:
1696 -- Processing continues.
1697 --
1698 -- Post Failure:
1699 -- If a row exists by determining the returning Boolean value from the
1700 -- generic dt_api.rows_exist function then we must supply an error via
1701 -- the use of the local exception handler l_rows_exist.
1702 --
1703 -- Developer Implementation Notes:
1704 -- This procedure should not need maintenance unless the HR Schema model
1705 -- changes.
1706 --
1707 -- Access Status:
1708 -- Internal Table Handler Use Only.
1709 --
1710 -- {End Of Comments}
1711 -- ----------------------------------------------------------------------------
1712 Procedure dt_delete_validate
1713 (p_personal_payment_method_id in number,
1714 p_datetrack_mode in varchar2,
1715 p_validation_start_date in date,
1716 p_validation_end_date in date) Is
1717 --
1718 l_proc varchar2(72) := g_package||'dt_delete_validate';
1719 l_rows_exist Exception;
1720 l_future_change Exception;
1721 l_table_name all_tables.table_name%TYPE;
1722 --
1723 Begin
1724 hr_utility.set_location('Entering:'||l_proc, 5);
1725 --
1726 -- Ensure that the p_datetrack_mode argument is not null
1727 --
1728 hr_api.mandatory_arg_error
1729 (p_api_name => l_proc,
1730 p_argument => 'datetrack_mode',
1731 p_argument_value => p_datetrack_mode);
1732 --
1733 -- Raise error if date_track mode is FUTURE_CHANGE
1734 --
1735 If (p_datetrack_mode ='FUTURE_CHANGE') then
1736 raise l_future_change;
1737 -- hr_utility.set_message(801, 'PAY_6209_ELEMENT_NO_FC_DEL');
1738 -- hr_utility.raise_error;
1739 end if;
1740 --
1741 -- Only perform the validation if the datetrack mode is either
1742 -- DELETE or ZAP
1743 --
1744 If (p_datetrack_mode = 'DELETE' or
1745 p_datetrack_mode = 'ZAP') then
1746 --
1747 --
1748 -- Ensure the arguments are not null
1749 --
1750 hr_api.mandatory_arg_error
1751 (p_api_name => l_proc,
1752 p_argument => 'validation_start_date',
1753 p_argument_value => p_validation_start_date);
1754 --
1755 hr_api.mandatory_arg_error
1756 (p_api_name => l_proc,
1757 p_argument => 'validation_end_date',
1758 p_argument_value => p_validation_end_date);
1759 --
1760 hr_api.mandatory_arg_error
1761 (p_api_name => l_proc,
1762 p_argument => 'personal_payment_method_id',
1763 p_argument_value => p_personal_payment_method_id);
1764 --
1765 End If;
1766 --
1767 hr_utility.set_location(' Leaving:'||l_proc, 10);
1768 Exception
1769 When l_rows_exist Then
1770 --
1771 -- A referential integrity check was violated therefore
1772 -- we must error
1773 --
1774 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1775 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1776 hr_utility.raise_error;
1777 When l_future_change then
1778 hr_utility.set_message(801, 'PAY_6209_ELEMENT_NO_FC_DEL');
1779 hr_utility.raise_error;
1780 When Others Then
1781 --
1782 -- An unhandled or unexpected error has occurred which
1783 -- we must report
1784 --
1785 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1786 hr_utility.set_message_token('PROCEDURE', l_proc);
1787 hr_utility.set_message_token('STEP','15');
1788 hr_utility.raise_error;
1789 End dt_delete_validate;
1790 --
1791 -- ----------------------------------------------------------------------------
1792 -- |---------------------------< insert_validate >----------------------------|
1793 -- ----------------------------------------------------------------------------
1794 Procedure insert_validate
1795 (p_rec in pay_ppm_shd.g_rec_type,
1796 p_effective_date in date,
1797 p_datetrack_mode in varchar2,
1798 p_validation_start_date in date,
1799 p_validation_end_date in date) is
1800 --
1801 l_proc varchar2(72) := g_package||'insert_validate';
1802 --
1803 Begin
1804 hr_utility.set_location('Entering:'||l_proc, 5);
1805 --
1806 -- Call all supporting business operations. Mapping to the appropriate
1807 -- Business Rules in payppm.bru is provided.
1808 --
1809 -- Validate Business Group ID
1810 --
1811 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1812 --
1813 -- Validate Org Payment Method ID
1814 --
1815 chk_org_payment_method_id
1816 (p_business_group_id => p_rec.business_group_id
1817 ,p_org_payment_method_id => p_rec.org_payment_method_id
1818 ,p_effective_date => p_effective_date
1819 );
1820 --
1821 -- Validate payee id and payee type, depending on whether the personal
1822 -- payment method is a garnishment or not.
1823 --
1824 chk_defined_balance_id
1825 (p_business_group_id => p_rec.business_group_id,
1826 p_assignment_id => p_rec.assignment_id,
1827 p_personal_payment_method_id => p_rec.personal_payment_method_id,
1828 p_org_payment_method_id => p_rec.org_payment_method_id,
1829 p_effective_date => p_effective_date,
1830 p_object_version_number => p_rec.object_version_number,
1831 p_payee_type => p_rec.payee_type,
1832 p_payee_id => p_rec.payee_id
1833 );
1834 --
1835 -- Validate Amount and Percentage
1836 --
1837 chk_amount_percent
1838 (p_amount => p_rec.amount
1839 ,p_percentage => p_rec.percentage
1840 ,p_personal_payment_method_id => p_rec.personal_payment_method_id
1841 ,p_org_payment_method_id => p_rec.org_payment_method_id
1842 ,p_effective_date => p_effective_date
1843 ,p_object_version_number => p_rec.object_version_number
1844 );
1845 --
1846 -- Validate External Account ID
1847 --
1848 chk_external_account_id
1849 (p_personal_payment_method_id => p_rec.personal_payment_method_id
1850 ,p_org_payment_method_id => p_rec.org_payment_method_id
1851 ,p_external_account_id => p_rec.external_account_id
1852 ,p_effective_date => p_effective_date
1853 ,p_object_version_number => p_rec.object_version_number
1854 );
1855 --
1856 -- Validate Priority
1857 --
1858 chk_priority
1859 (p_priority => p_rec.priority
1860 ,p_personal_payment_method_id => p_rec.personal_payment_method_id
1861 ,p_org_payment_method_id => p_rec.org_payment_method_id
1862 ,p_assignment_id => p_rec.assignment_id
1863 ,p_run_type_id => p_rec.run_type_id
1864 ,p_effective_date => p_effective_date
1865 ,p_object_version_number => p_rec.object_version_number
1866 ,p_validation_start_date => p_validation_start_date
1867 ,p_validation_end_date => p_validation_end_date
1868 );
1869 --
1870 -- Validate DDF
1871 --
1872 pay_ppm_bus.chk_ddf(p_rec => p_rec);
1873 --
1874 -- DF external hook
1875 --
1876 pay_ppm_bus.chk_df(p_rec => p_rec);
1877 --
1878 hr_utility.set_location(' Leaving:'||l_proc, 10);
1879 End insert_validate;
1880 --
1881 -- ----------------------------------------------------------------------------
1882 -- |---------------------------< update_validate >----------------------------|
1883 -- ----------------------------------------------------------------------------
1884 Procedure update_validate
1885 (p_rec in pay_ppm_shd.g_rec_type,
1886 p_effective_date in date,
1887 p_datetrack_mode in varchar2,
1888 p_validation_start_date in date,
1889 p_validation_end_date in date) is
1890 --
1891 l_proc varchar2(72) := g_package||'update_validate';
1892 --
1893 Begin
1894 hr_utility.set_location('Entering:'||l_proc, 5);
1895 --
1896 -- Call all supporting business operations. Mapping to the appropriate
1897 -- Business Rules in payppm.bru is provided.
1898 --
1899 -- Check that the columns which cannot be updated have not changed
1900 --
1901 check_non_updateable_args(p_rec => p_rec
1902 ,p_effective_date => p_effective_date);
1903 --
1904 -- Validate payee id and payee type, depending on whether the personal
1905 -- payment method is a garnishment or not.
1906 --
1907 chk_defined_balance_id
1908 (p_business_group_id => p_rec.business_group_id,
1909 p_assignment_id => p_rec.assignment_id,
1910 p_personal_payment_method_id => p_rec.personal_payment_method_id,
1914 p_payee_type => p_rec.payee_type,
1911 p_org_payment_method_id => p_rec.org_payment_method_id,
1912 p_effective_date => p_effective_date,
1913 p_object_version_number => p_rec.object_version_number,
1915 p_payee_id => p_rec.payee_id
1916 );
1917 --
1918 -- Validate Amount and Percentage
1919 --
1920 chk_amount_percent
1921 (p_amount => p_rec.amount
1922 ,p_percentage => p_rec.percentage
1923 ,p_personal_payment_method_id => p_rec.personal_payment_method_id
1924 ,p_org_payment_method_id => p_rec.org_payment_method_id
1925 ,p_effective_date => p_effective_date
1926 ,p_object_version_number => p_rec.object_version_number
1927 );
1928 --
1929 -- Validate External Account ID
1930 --
1931 chk_external_account_id
1932 (p_personal_payment_method_id => p_rec.personal_payment_method_id
1933 ,p_org_payment_method_id => p_rec.org_payment_method_id
1934 ,p_external_account_id => p_rec.external_account_id
1935 ,p_effective_date => p_effective_date
1936 ,p_object_version_number => p_rec.object_version_number
1937 );
1938 --
1939 -- Validate Priority
1940 --
1941 chk_priority
1942 (p_priority => p_rec.priority
1943 ,p_personal_payment_method_id => p_rec.personal_payment_method_id
1944 ,p_org_payment_method_id => p_rec.org_payment_method_id
1945 ,p_assignment_id => p_rec.assignment_id
1946 ,p_run_type_id => p_rec.run_type_id
1947 ,p_effective_date => p_effective_date
1948 ,p_object_version_number => p_rec.object_version_number
1949 ,p_validation_start_date => p_validation_start_date
1950 ,p_validation_end_date => p_validation_end_date
1951 );
1952 --
1953 -- Validate DDF
1954 --
1955 pay_ppm_bus.chk_ddf(p_rec => p_rec);
1956 --
1957 -- DF external hook
1958 --
1959 pay_ppm_bus.chk_df(p_rec => p_rec);
1960 --
1961 -- Call the datetrack update integrity operation
1962 --
1963 dt_update_validate
1964 (p_org_payment_method_id => p_rec.org_payment_method_id,
1965 p_assignment_id => p_rec.assignment_id,
1966 p_datetrack_mode => p_datetrack_mode,
1967 p_validation_start_date => p_validation_start_date,
1968 p_validation_end_date => p_validation_end_date);
1969 --
1970 hr_utility.set_location(' Leaving:'||l_proc, 10);
1971 End update_validate;
1972 --
1973 -- ----------------------------------------------------------------------------
1974 -- |---------------------------< delete_validate >----------------------------|
1975 -- ----------------------------------------------------------------------------
1976 Procedure delete_validate
1977 (p_rec in pay_ppm_shd.g_rec_type,
1978 p_effective_date in date,
1979 p_datetrack_mode in varchar2,
1980 p_validation_start_date in date,
1981 p_validation_end_date in date) is
1982 --
1983 l_proc varchar2(72) := g_package||'delete_validate';
1984 --
1985 Begin
1986 hr_utility.set_location('Entering:'||l_proc, 5);
1987 --
1988 -- Call all supporting business operations. Mapping to the appropriate
1989 -- business rules on paypm.bru is provided
1990 --
1991 -- check if delete operations are allowed
1992 --
1993 chk_delete
1994 (p_personal_payment_method_id => p_rec.personal_payment_method_id
1995 ,p_effective_date => p_effective_date
1996 ,p_datetrack_mode => p_datetrack_mode
1997 ,p_validation_start_date => p_validation_start_date
1998 ,p_validation_end_date => p_validation_end_date
1999 );
2000 --
2001 dt_delete_validate
2002 (p_datetrack_mode => p_datetrack_mode,
2003 p_validation_start_date => p_validation_start_date,
2004 p_validation_end_date => p_validation_end_date,
2005 p_personal_payment_method_id => p_rec.personal_payment_method_id);
2006 --
2007 hr_utility.set_location(' Leaving:'||l_proc, 10);
2008 End delete_validate;
2009 --
2010 -- ---------------------------------------------------------------------------
2011 -- |---------------------< return_legislation_code >-------------------------|
2012 -- ---------------------------------------------------------------------------
2013 --
2014 function return_legislation_code
2015 (p_personal_payment_method_id in number
2016 ) return varchar2 is
2017 --
2018 -- Declare cursor
2019 --
2020 cursor csr_leg_code is
2021 select pbg.legislation_code
2022 from per_business_groups pbg
2023 , pay_personal_payment_methods_f ppm
2024 where ppm.personal_payment_method_id = p_personal_payment_method_id
2025 and pbg.business_group_id = ppm.business_group_id;
2026 --
2027 -- Declare local variables
2028 --
2029 l_legislation_code varchar2(150);
2030 l_proc varchar2(72) := g_package||'return_legislation_code';
2031 begin
2032 hr_utility.set_location('Entering:'|| l_proc, 10);
2033 --
2034 -- Ensure that all the mandatory parameter are not null
2035 --
2036 hr_api.mandatory_arg_error(p_api_name => l_proc,
2037 p_argument => 'personal_payment_method_id',
2041 fetch csr_leg_code into l_legislation_code;
2038 p_argument_value => p_personal_payment_method_id);
2039 --
2040 open csr_leg_code;
2042 if csr_leg_code%notfound then
2043 close csr_leg_code;
2044 --
2045 -- The primary key is invalid therefore we must error
2046 --
2047 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2048 hr_utility.raise_error;
2049 end if;
2050 --
2051 close csr_leg_code;
2052 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2053 --
2054 return l_legislation_code;
2055 end return_legislation_code;
2056 --
2057 -- ----------------------------------------------------------------------------
2058 -- |-----------------------------< chk_ddf >----------------------------------|
2059 -- ----------------------------------------------------------------------------
2060 procedure chk_ddf
2061 (p_rec in pay_ppm_shd.g_rec_type
2062 ) is
2063 --
2064 l_proc varchar2(72) := g_package || 'chk_ddf';
2065 --
2066 begin
2067 hr_utility.set_location('Entering:'||l_proc,10);
2068 --
2069 if ((p_rec.personal_payment_method_id is not null) and (
2070 nvl(pay_ppm_shd.g_old_rec.ppm_information_category, hr_api.g_varchar2) <>
2071 nvl(p_rec.ppm_information_category, hr_api.g_varchar2) or
2072 nvl(pay_ppm_shd.g_old_rec.ppm_information1, hr_api.g_varchar2) <>
2073 nvl(p_rec.ppm_information1, hr_api.g_varchar2) or
2074 nvl(pay_ppm_shd.g_old_rec.ppm_information2, hr_api.g_varchar2) <>
2075 nvl(p_rec.ppm_information2, hr_api.g_varchar2) or
2076 nvl(pay_ppm_shd.g_old_rec.ppm_information3, hr_api.g_varchar2) <>
2077 nvl(p_rec.ppm_information3, hr_api.g_varchar2) or
2078 nvl(pay_ppm_shd.g_old_rec.ppm_information4, hr_api.g_varchar2) <>
2079 nvl(p_rec.ppm_information4, hr_api.g_varchar2) or
2080 nvl(pay_ppm_shd.g_old_rec.ppm_information5, hr_api.g_varchar2) <>
2081 nvl(p_rec.ppm_information5, hr_api.g_varchar2) or
2082 nvl(pay_ppm_shd.g_old_rec.ppm_information6, hr_api.g_varchar2) <>
2083 nvl(p_rec.ppm_information6, hr_api.g_varchar2) or
2084 nvl(pay_ppm_shd.g_old_rec.ppm_information7, hr_api.g_varchar2) <>
2085 nvl(p_rec.ppm_information7, hr_api.g_varchar2) or
2086 nvl(pay_ppm_shd.g_old_rec.ppm_information8, hr_api.g_varchar2) <>
2087 nvl(p_rec.ppm_information8, hr_api.g_varchar2) or
2088 nvl(pay_ppm_shd.g_old_rec.ppm_information9, hr_api.g_varchar2) <>
2089 nvl(p_rec.ppm_information9, hr_api.g_varchar2) or
2090 nvl(pay_ppm_shd.g_old_rec.ppm_information10, hr_api.g_varchar2) <>
2091 nvl(p_rec.ppm_information10, hr_api.g_varchar2) or
2092 nvl(pay_ppm_shd.g_old_rec.ppm_information11, hr_api.g_varchar2) <>
2093 nvl(p_rec.ppm_information11, hr_api.g_varchar2) or
2094 nvl(pay_ppm_shd.g_old_rec.ppm_information12, hr_api.g_varchar2) <>
2095 nvl(p_rec.ppm_information12, hr_api.g_varchar2) or
2096 nvl(pay_ppm_shd.g_old_rec.ppm_information13, hr_api.g_varchar2) <>
2097 nvl(p_rec.ppm_information13, hr_api.g_varchar2) or
2098 nvl(pay_ppm_shd.g_old_rec.ppm_information14, hr_api.g_varchar2) <>
2099 nvl(p_rec.ppm_information14, hr_api.g_varchar2) or
2100 nvl(pay_ppm_shd.g_old_rec.ppm_information15, hr_api.g_varchar2) <>
2101 nvl(p_rec.ppm_information15, hr_api.g_varchar2) or
2102 nvl(pay_ppm_shd.g_old_rec.ppm_information16, hr_api.g_varchar2) <>
2103 nvl(p_rec.ppm_information16, hr_api.g_varchar2) or
2104 nvl(pay_ppm_shd.g_old_rec.ppm_information17, hr_api.g_varchar2) <>
2105 nvl(p_rec.ppm_information17, hr_api.g_varchar2) or
2106 nvl(pay_ppm_shd.g_old_rec.ppm_information18, hr_api.g_varchar2) <>
2107 nvl(p_rec.ppm_information18, hr_api.g_varchar2) or
2108 nvl(pay_ppm_shd.g_old_rec.ppm_information19, hr_api.g_varchar2) <>
2109 nvl(p_rec.ppm_information19, hr_api.g_varchar2) or
2110 nvl(pay_ppm_shd.g_old_rec.ppm_information20, hr_api.g_varchar2) <>
2111 nvl(p_rec.ppm_information20, hr_api.g_varchar2) or
2112 nvl(pay_ppm_shd.g_old_rec.ppm_information21, hr_api.g_varchar2) <>
2113 nvl(p_rec.ppm_information21, hr_api.g_varchar2) or
2114 nvl(pay_ppm_shd.g_old_rec.ppm_information22, hr_api.g_varchar2) <>
2115 nvl(p_rec.ppm_information22, hr_api.g_varchar2) or
2116 nvl(pay_ppm_shd.g_old_rec.ppm_information23, hr_api.g_varchar2) <>
2117 nvl(p_rec.ppm_information23, hr_api.g_varchar2) or
2118 nvl(pay_ppm_shd.g_old_rec.ppm_information24, hr_api.g_varchar2) <>
2119 nvl(p_rec.ppm_information24, hr_api.g_varchar2) or
2120 nvl(pay_ppm_shd.g_old_rec.ppm_information25, hr_api.g_varchar2) <>
2121 nvl(p_rec.ppm_information25, hr_api.g_varchar2) or
2122 nvl(pay_ppm_shd.g_old_rec.ppm_information26, hr_api.g_varchar2) <>
2123 nvl(p_rec.ppm_information26, hr_api.g_varchar2) or
2124 nvl(pay_ppm_shd.g_old_rec.ppm_information27, hr_api.g_varchar2) <>
2125 nvl(p_rec.ppm_information27, hr_api.g_varchar2) or
2126 nvl(pay_ppm_shd.g_old_rec.ppm_information28, hr_api.g_varchar2) <>
2127 nvl(p_rec.ppm_information28, hr_api.g_varchar2) or
2128 nvl(pay_ppm_shd.g_old_rec.ppm_information29, hr_api.g_varchar2) <>
2129 nvl(p_rec.ppm_information29, hr_api.g_varchar2) or
2130 nvl(pay_ppm_shd.g_old_rec.ppm_information30, hr_api.g_varchar2) <>
2131 nvl(p_rec.ppm_information30, hr_api.g_varchar2) ))
2132 or (p_rec.personal_payment_method_id is null) then
2133 --
2134 -- Only execute the validation if absolutely necessary:
2135 -- a) During update, the structure column value or any
2136 -- of the attribute values have actually changed.
2137 -- b) During insert.
2138 --
2142 ,p_attribute_category => p_rec.ppm_information_category
2139 hr_dflex_utility.ins_or_upd_descflex_attribs
2140 (p_appl_short_name => 'PAY'
2141 ,p_descflex_name => 'Personal PayMeth Developer DF'
2143 ,p_attribute1_name => 'PPM_INFORMATION1'
2144 ,p_attribute1_value => p_rec.ppm_information1
2145 ,p_attribute2_name => 'PPM_INFORMATION2'
2146 ,p_attribute2_value => p_rec.ppm_information2
2147 ,p_attribute3_name => 'PPM_INFORMATION3'
2148 ,p_attribute3_value => p_rec.ppm_information3
2149 ,p_attribute4_name => 'PPM_INFORMATION4'
2150 ,p_attribute4_value => p_rec.ppm_information4
2151 ,p_attribute5_name => 'PPM_INFORMATION5'
2152 ,p_attribute5_value => p_rec.ppm_information5
2153 ,p_attribute6_name => 'PPM_INFORMATION6'
2154 ,p_attribute6_value => p_rec.ppm_information6
2155 ,p_attribute7_name => 'PPM_INFORMATION7'
2156 ,p_attribute7_value => p_rec.ppm_information7
2157 ,p_attribute8_name => 'PPM_INFORMATION8'
2158 ,p_attribute8_value => p_rec.ppm_information8
2159 ,p_attribute9_name => 'PPM_INFORMATION9'
2160 ,p_attribute9_value => p_rec.ppm_information9
2161 ,p_attribute10_name => 'PPM_INFORMATION10'
2162 ,p_attribute10_value => p_rec.ppm_information10
2163 ,p_attribute11_name => 'PPM_INFORMATION11'
2164 ,p_attribute11_value => p_rec.ppm_information11
2165 ,p_attribute12_name => 'PPM_INFORMATION12'
2166 ,p_attribute12_value => p_rec.ppm_information12
2167 ,p_attribute13_name => 'PPM_INFORMATION13'
2168 ,p_attribute13_value => p_rec.ppm_information13
2169 ,p_attribute14_name => 'PPM_INFORMATION14'
2170 ,p_attribute14_value => p_rec.ppm_information14
2171 ,p_attribute15_name => 'PPM_INFORMATION15'
2172 ,p_attribute15_value => p_rec.ppm_information15
2173 ,p_attribute16_name => 'PPM_INFORMATION16'
2174 ,p_attribute16_value => p_rec.ppm_information16
2175 ,p_attribute17_name => 'PPM_INFORMATION17'
2176 ,p_attribute17_value => p_rec.ppm_information17
2177 ,p_attribute18_name => 'PPM_INFORMATION18'
2178 ,p_attribute18_value => p_rec.ppm_information18
2179 ,p_attribute19_name => 'PPM_INFORMATION19'
2180 ,p_attribute19_value => p_rec.ppm_information19
2181 ,p_attribute20_name => 'PPM_INFORMATION20'
2182 ,p_attribute20_value => p_rec.ppm_information20
2183 ,p_attribute21_name => 'PPM_INFORMATION21'
2184 ,p_attribute21_value => p_rec.ppm_information21
2185 ,p_attribute22_name => 'PPM_INFORMATION22'
2186 ,p_attribute22_value => p_rec.ppm_information22
2187 ,p_attribute23_name => 'PPM_INFORMATION23'
2188 ,p_attribute23_value => p_rec.ppm_information23
2189 ,p_attribute24_name => 'PPM_INFORMATION24'
2190 ,p_attribute24_value => p_rec.ppm_information24
2191 ,p_attribute25_name => 'PPM_INFORMATION25'
2192 ,p_attribute25_value => p_rec.ppm_information25
2193 ,p_attribute26_name => 'PPM_INFORMATION26'
2194 ,p_attribute26_value => p_rec.ppm_information26
2195 ,p_attribute27_name => 'PPM_INFORMATION27'
2196 ,p_attribute27_value => p_rec.ppm_information27
2197 ,p_attribute28_name => 'PPM_INFORMATION28'
2198 ,p_attribute28_value => p_rec.ppm_information28
2199 ,p_attribute29_name => 'PPM_INFORMATION29'
2200 ,p_attribute29_value => p_rec.ppm_information29
2201 ,p_attribute30_name => 'PPM_INFORMATION30'
2202 ,p_attribute30_value => p_rec.ppm_information30
2203 );
2204 end if;
2205 --
2206 hr_utility.set_location(' Leaving:'||l_proc,20);
2207 end chk_ddf;
2208 --
2209 -- -----------------------------------------------------------------------
2210 -- |------------------------------< chk_df >-----------------------------|
2211 -- -----------------------------------------------------------------------
2212 procedure chk_df
2213 (p_rec in pay_ppm_shd.g_rec_type
2214 ) is
2215 l_proc varchar2(2000) := g_package||'chk_df';
2216 l_rec pay_ppm_shd.g_rec_type;
2217 begin
2218 hr_utility.set_location('Entering:'||l_proc, 10);
2219 l_rec := pay_ppm_shd.g_old_rec;
2220 --
2221 -- Only do the validation if inserting or if values have changed on
2222 -- update.
2223 --
2224 if (p_rec.personal_payment_method_id is not null and (
2225 nvl(p_rec.attribute_category, hr_api.g_varchar2) <>
2226 nvl(l_rec.attribute_category, hr_api.g_varchar2) or
2227 nvl(p_rec.attribute1, hr_api.g_varchar2) <>
2228 nvl(l_rec.attribute1, hr_api.g_varchar2) or
2229 nvl(p_rec.attribute2, hr_api.g_varchar2) <>
2230 nvl(l_rec.attribute2, hr_api.g_varchar2) or
2231 nvl(p_rec.attribute3, hr_api.g_varchar2) <>
2232 nvl(l_rec.attribute3, hr_api.g_varchar2) or
2233 nvl(p_rec.attribute4, hr_api.g_varchar2) <>
2234 nvl(l_rec.attribute4, hr_api.g_varchar2) or
2235 nvl(p_rec.attribute5, hr_api.g_varchar2) <>
2236 nvl(l_rec.attribute5, hr_api.g_varchar2) or
2237 nvl(p_rec.attribute6, hr_api.g_varchar2) <>
2238 nvl(l_rec.attribute6, hr_api.g_varchar2) or
2239 nvl(p_rec.attribute7, hr_api.g_varchar2) <>
2240 nvl(l_rec.attribute7, hr_api.g_varchar2) or
2241 nvl(p_rec.attribute8, hr_api.g_varchar2) <>
2242 nvl(l_rec.attribute8, hr_api.g_varchar2) or
2243 nvl(p_rec.attribute9, hr_api.g_varchar2) <>
2244 nvl(l_rec.attribute9, hr_api.g_varchar2) or
2245 nvl(p_rec.attribute10, hr_api.g_varchar2) <>
2246 nvl(l_rec.attribute10, hr_api.g_varchar2) or
2247 nvl(p_rec.attribute11, hr_api.g_varchar2) <>
2248 nvl(l_rec.attribute11, hr_api.g_varchar2) or
2249 nvl(p_rec.attribute12, hr_api.g_varchar2) <>
2250 nvl(l_rec.attribute12, hr_api.g_varchar2) or
2251 nvl(p_rec.attribute13, hr_api.g_varchar2) <>
2252 nvl(l_rec.attribute13, hr_api.g_varchar2) or
2253 nvl(p_rec.attribute14, hr_api.g_varchar2) <>
2254 nvl(l_rec.attribute14, hr_api.g_varchar2) or
2255 nvl(p_rec.attribute15, hr_api.g_varchar2) <>
2256 nvl(l_rec.attribute15, hr_api.g_varchar2) or
2257 nvl(p_rec.attribute16, hr_api.g_varchar2) <>
2258 nvl(l_rec.attribute16, hr_api.g_varchar2) or
2259 nvl(p_rec.attribute17, hr_api.g_varchar2) <>
2260 nvl(l_rec.attribute17, hr_api.g_varchar2) or
2261 nvl(p_rec.attribute18, hr_api.g_varchar2) <>
2262 nvl(l_rec.attribute18, hr_api.g_varchar2) or
2263 nvl(p_rec.attribute19, hr_api.g_varchar2) <>
2264 nvl(l_rec.attribute19, hr_api.g_varchar2) or
2265 nvl(p_rec.attribute20, hr_api.g_varchar2) <>
2266 nvl(l_rec.attribute20, hr_api.g_varchar2))) or
2267 p_rec.personal_payment_method_id is null
2268 then
2269 hr_utility.set_location(l_proc, 20);
2270 hr_dflex_utility.ins_or_upd_descflex_attribs
2271 (p_appl_short_name => 'PAY'
2272 ,p_descflex_name => 'PAY_PERSONAL_PAYMENT_METHODS'
2273 ,p_attribute_category => p_rec.attribute_category
2274 ,p_attribute1_name => 'ATTRIBUTE1'
2275 ,p_attribute1_value => p_rec.attribute1
2276 ,p_attribute2_name => 'ATTRIBUTE2'
2277 ,p_attribute2_value => p_rec.attribute2
2278 ,p_attribute3_name => 'ATTRIBUTE3'
2279 ,p_attribute3_value => p_rec.attribute3
2280 ,p_attribute4_name => 'ATTRIBUTE4'
2281 ,p_attribute4_value => p_rec.attribute4
2282 ,p_attribute5_name => 'ATTRIBUTE5'
2283 ,p_attribute5_value => p_rec.attribute5
2284 ,p_attribute6_name => 'ATTRIBUTE6'
2285 ,p_attribute6_value => p_rec.attribute6
2286 ,p_attribute7_name => 'ATTRIBUTE7'
2287 ,p_attribute7_value => p_rec.attribute7
2288 ,p_attribute8_name => 'ATTRIBUTE8'
2289 ,p_attribute8_value => p_rec.attribute8
2290 ,p_attribute9_name => 'ATTRIBUTE9'
2291 ,p_attribute9_value => p_rec.attribute9
2292 ,p_attribute10_name => 'ATTRIBUTE10'
2293 ,p_attribute10_value => p_rec.attribute10
2294 ,p_attribute11_name => 'ATTRIBUTE11'
2295 ,p_attribute11_value => p_rec.attribute11
2296 ,p_attribute12_name => 'ATTRIBUTE12'
2297 ,p_attribute12_value => p_rec.attribute12
2298 ,p_attribute13_name => 'ATTRIBUTE13'
2299 ,p_attribute13_value => p_rec.attribute13
2300 ,p_attribute14_name => 'ATTRIBUTE14'
2301 ,p_attribute14_value => p_rec.attribute14
2302 ,p_attribute15_name => 'ATTRIBUTE15'
2303 ,p_attribute15_value => p_rec.attribute15
2304 ,p_attribute16_name => 'ATTRIBUTE16'
2305 ,p_attribute16_value => p_rec.attribute16
2306 ,p_attribute17_name => 'ATTRIBUTE17'
2307 ,p_attribute17_value => p_rec.attribute17
2308 ,p_attribute18_name => 'ATTRIBUTE18'
2309 ,p_attribute18_value => p_rec.attribute18
2310 ,p_attribute19_name => 'ATTRIBUTE19'
2311 ,p_attribute19_value => p_rec.attribute19
2312 ,p_attribute20_name => 'ATTRIBUTE20'
2313 ,p_attribute20_value => p_rec.attribute20
2314 );
2315 end if;
2316 hr_utility.set_location('Leaving:'||l_proc, 30);
2317 end chk_df;
2318 --
2319 end pay_ppm_bus;
[Home] [Help]