1 package body hr_payments as
2 /* $Header: pypaymnt.pkb 120.4 2011/09/22 09:38:37 kskoduri ship $ */
3 /*
4 * ---------------------------------------------------------------------------
5 Copyright (c) Oracle Corporation (UK) Ltd 1992.
6 All Rights Reserved.
7 --
8 --
9 PRODUCT
10 Oracle*Payroll
11 NAME
12 hr_payments (hrppm.pkb)
13 NOTES
14 per-payments procedures. Used for validation of DDL on:
15 PAY_PAYMENT_TYPES
16 PAY_ORG_PAYMENT_METHODS
17 PAY_ORG_PAY_METHOD_USAGES
18 PAY_PERSONAL_PAYMENT_METHODS
19 MODIFIED
20 amcinnes 16-NOV-1992 Created with ppt_brui
21 amcinnes 20-DEC-1992 Added all other payments procedures
22 amcinnes 07-JAN-1993 GEN_BALANCE now called from forms
23 amcinnes 11-JAN-1993 Corrected operation of gen balance
24 amcinnes 21-JAN-1993 Changes for new standards
25 amcinnes 14-JUN-1993 Changes for change to PPT that whoever
26 changed it should have done.
27 Removed all refs to formula/formula_id
28 afrith 29-SEP-1993 Change gen-balance to use remuneration flag
29 rneale 20-JUN-1994 Added match_currency for use by PAYWSDPM.
30 rfine 40.4 21-MAR-1995 Fixed fnd_message.set_name calls in
31 rfine 40.5 22-MAR-1995 Changed check_pp so it now checks for
32 pre-payments after the date passed in only,
33 not 'on or after' that date. WWbug 264094.
34 sdoshi 115.1 30-MAR-1999 Flexible Dates Conversion
35 tbattoo 115.2 10-JAN-2001 Modified check_currency function. Added
36 an nvl in the IF statement.
37 kskoduri 115.4 14-JUN-2011 Added the procedure check_ppm to validate
38 if prepayments exist before updating a
39 personal payment method via selfservice
40 kskoduri 115.6 27-JUN-2011 Added NOCOPY hint to remove the GSCC fail
41 kskoduri 115.7 22-SEP-2011 Added NOCOPY hint to remove the GSCC fail
42 --
43 * ---------------------------------------------------------------------------
44 */
45 --
46 /*--------------------------- validate_magnetic ------------------------------
47
48 NAME
49 validate_magnetic
50 DESCRIPTION
51 Validate business rules for pay_payment_types
52 NOTES
53 Category is magnetic if this is called.
54 Check pre-validation_requied flag is set.
55 If pre-validation, check that the values and days are set.
56 */
57 function validate_magnetic(validate in varchar2,
58 validation_days in number,
59 validation_value in varchar2) return boolean is
60 --
61 begin
62 --
63 hr_utility.set_location('HR_PAYMENTS.VALIDATE_MAGNETIC',1);
64 --
65 -- Check mand values for mag method are there.
66 --
67 if validate is null then
68 --
69 hr_utility.set_message(801,'HR_6227_PAYM_MAND_MAG_DETAILS');
70 hr_utility.raise_error;
71 return(false);
72 --
73 end if;
74 --
75 -- Check pre-validation flag is set
76 --
77 if validate = 'Y' then
78 --
79 -- Magnetic validation required. Days and values must be set
80 --
81 if validation_days is null or validation_value is null then
82 --
83 hr_utility.set_message(801,'HR_6228_PAYM_NO_VALIDATION');
84 hr_utility.raise_error;
85 return(false);
86 --
87 end if;
88 --
89 end if;
90 --
91 return(true);
92 --
93 end validate_magnetic;
94 --
95 /*--------------------------- check_ok_default----------------------------------
96
97 NAME
98 check_ok_default
99 DESCRIPTION
100 Check the payment type is allowed as a default
101 NOTES
102 CASH and CHECK allowed.
103 */
104 function check_ok_default(category in varchar2) return boolean is
105 begin
106 --
107 hr_utility.set_location('HR_PAYMENTS.CHECK_DEFAULT',1);
108 --
109 if category = 'CA' or category = 'CH' then
110 --
111 return(true);
112 --
113 else
114 --
115 hr_utility.set_message(801,'HR_6229_PAYM_BAD_DEFAULT_TYPE');
116 hr_utility.raise_error;
117 return(false);
118 --
119 end if;
120 --
121 end check_ok_default;
122 --
123 /*--------------------------- ppt_brui -----------------------------------
124 NAME
125 ppt_brui
126 DESCRIPTION
127 pay_payment_types trigger
128 NOTES
129 Calls various validation routines on new/updated row
130 */
131 procedure ppt_brui(allow_as_default in varchar2,
132 category in varchar2,
133 pre_validation_required in varchar2,
134 validation_days in number,
135 validation_value in varchar2) is
136 status boolean;
137 begin
138 --
139 if allow_as_default = 'Y' then
140 --
141 status := check_ok_default(category);
142 --
143 end if;
144 --
145 if category = 'MT' then
146 --
147 -- Mag Tape so do the required checks
148 --
149 status := validate_magnetic(pre_validation_required,
150 validation_days,
151 validation_value);
152 --
153 end if;
154 --
155 end ppt_brui;
156 --------------------------- check_account -----------------------------------
157 /*
158 NAME
159 Check_account
160 DESCRIPTION
161 Checks that the OPM has an external account in the required territory.
162 NOTES
163 If the payment type of the OPM requires an account in a certain terrritory
164 then this must be checked.
165 Returns true or false (with application error)
166 */
167 function check_account(account in varchar2,
168 type in varchar2) return boolean is
169 required_territory varchar2(3);
170 actual_territory varchar2(3);
171 begin
172 --
173 -- One row each from EXA and PPT.
174 --
175 hr_utility.set_location('HR_PAYMENTS.CHECK_ACCOUNT',1);
176 --
177 select ppt.territory_code,
178 exa.territory_code
179 into required_territory,
180 actual_territory
181 from pay_payment_types ppt,
182 pay_external_accounts exa
183 where exa.external_account_id = account
184 and ppt.payment_type_id = type;
185 --
186 -- Check that they are the same and generate an error if not
187 --
188 hr_utility.set_location('HR_PAYMENTS.CHECK_ACCOUNT',2);
189 --
190 if required_territory <> actual_territory then
191 --
192 hr_utility.set_message(801,'HR_6220_PAYM_INVALID_ACT');
193 hr_utility.set_message_token('TERITORY',required_territory);
194 hr_utility.raise_error;
195 return(false);
196 --
197 else
198 --
199 return(true);
200 --
201 end if;
202 --
203 exception
204 --
205 when no_data_found then
206 --
207 hr_utility.set_message(801,'HR_6230_PAYM_NO_ACCT_TYPE');
208 hr_utility.raise_error;
209 return(false);
210 --
211 --
212 end check_account;
213 --
214 --------------------------- check_currency -----------------------------------
215 /*
216 NAME
217 Check_currency
218 DESCRIPTION
219 Checks that the currency required by the type is met by the OPM
220 NOTES
221 If the types of this OPM requires that payments be made in a specific
222 currency, check that this is the case.
223 Returns true or false (with application error)
224 */
225 function check_currency(type in varchar2,
226 opm_currency in varchar2) return boolean is
227 required_currency varchar2(16);
228 begin
229 --
230 hr_utility.set_location('HR_PAYMENTS.CHECK_CURRENCY',1);
231 --
232 select ppt.currency_code
233 into required_currency
234 from pay_payment_types ppt
235 where ppt.payment_type_id = type;
236 --
237 -- Check that they are the same. If not report the error
238 --
239 if nvl(required_currency,opm_currency) <> opm_currency then
240 --
241 hr_utility.set_message(801,'HR_6231_PAYM_INVALID_CURRENCY');
242 hr_utility.set_message_token('CURRENCY',required_currency);
243 hr_utility.raise_error;
244 return(false);
245 --
246 else
247 --
248 return(true);
249 --
250 end if;
251 --
252 exception
253 --
254 when no_data_found then
255 --
256 hr_utility.set_message(801,'HR_6232_PAYM_NO_TYPE');
257 hr_utility.raise_error;
258 return(false);
259 --
260 --
261 end check_currency;
262 --
263 ---------------------------match_currency---------------------------------
264 /*
265 NAME
266 match_currency
267 DESCRIPTION
268 ensures the currency for the OPM matches that required by the balance
269 type and the payment type.
270 NOTES
271 Checks if the payment_type has a required currency and that the OPM's
272 currency matches this via check_currency. Then ensures that the currency
273 for the balance type matches this currency.
274 */
275 procedure match_currency(type in varchar2,
276 opm_currency in varchar2,
277 def_balance in varchar2) is
278 bal_type varchar2(9);
279 bal_currency varchar2(16);
280 begin
281 --
282 hr_utility.set_location('HR_PAYMENTS.MATCH_CURRENCY',1);
283 --
284 if check_currency(type,opm_currency) then
285 --
286 select pdb.balance_type_id
287 into bal_type
288 from pay_defined_balances pdb
289 where defined_balance_id = def_balance;
290 --
291 select pbt.currency_code
292 into bal_currency
293 from pay_balance_types pbt
294 where balance_type_id = bal_type;
295 --
296 if opm_currency <> bal_currency then
297 fnd_message.set_name('PAY','HR_7132_PAY_ORG_PAY_CURRENCY');
298 fnd_message.raise_error;
299 end if;
300 --
301 end if;
302 --
303 exception
304 --
305 when no_data_found then
306 fnd_message.set_name('PAY','HR_7133_PAY_ORG_PAYM_NO_BAL');
307 fnd_message.raise_error;
308 --
309 end match_currency;
310 --
311 --------------------------- gen_balance -----------------------------------
312 /*
313 NAME
314 gen_balance
315 DESCRIPTION
316 Generate the defined balance for pre-payments.
317 NOTES
318 There will be one balance dimension which has the attribute payments_flag
319 set to 'Y'. This is the 'payments dimesion. There will be one defined
320 balance with this balance dimension for each legislation. This
321 defined balance id must be inserted into the defined_balance_id column
322 of the OPM.
323 --
324 */
325 function gen_balance(leg_code in varchar2) return number is
326 no_payments_balance exception;
327 defined_balance number(16);
328 begin
329 --
330 -- Get it then (if its there)
331 --
332 hr_utility.set_location('HR_PAYMENTS.GEN_BALANCE',1);
333 --
334 select pdb.defined_balance_id
335 into defined_balance
336 from pay_balance_types pbt,
337 pay_balance_dimensions pbd,
338 pay_defined_balances pdb
339 where pdb.legislation_code = leg_code
340 and pdb.business_group_id is null
341 and pdb.balance_dimension_id = pbd.balance_dimension_id
342 and pbt.balance_type_id = pdb.balance_type_id
343 and pbd.payments_flag = 'Y'
344 and pbt.assignment_remuneration_flag = 'Y';
345 --
346 -- Return the defined balance
347 --
348 return(defined_balance);
349 --
350 exception
351 --
352 when no_data_found then
353 --
354 hr_utility.set_message(801,'HR_6233_PAYM_NO_PAY_BALANCE');
355 hr_utility.raise_error;
356 return(0);
357 --
358 end gen_balance;
359 --
360 --------------------------- check_prepay -----------------------------------
361 /*
362 NAME
363 check_prepay
364 DESCRIPTION
365 Check there are no pre-payment records for this OPM.
366 NOTES
367 The OPM may only be updated or deleted if it has no pre-payments with an
368 effective date after the validation start date.
369 */
370 function check_prepay(opm_id in number,
371 val_start_date in varchar2) return boolean is
372 dummy varchar2(2);
373 begin
374 --
375 hr_utility.set_location('HR_PAYMENTS.CHECK_PREPAY',1);
376 --
377 select 1
378 into dummy
379 from dual
380 where not exists(
381 select 1
382 from pay_pre_payments pp
383 where pp.org_payment_method_id = opm_id
384 and exists(
385 select 1
386 from pay_assignment_actions aa,
387 pay_payroll_actions pa
388 where pp.assignment_action_id = aa.assignment_action_id
389 and aa.payroll_action_id = pa.payroll_action_id
390 and pa.effective_date >=
391 fnd_date.canonical_to_date(val_start_date)));
392 --
393 -- A row returned means no pre-payments after the validation date...OK
394 --
395 return(true);
396 --
397 exception
398 --
399 when no_data_found then
400 --
401 hr_utility.set_message(801,'HR_6234_PAYM_ENTRIES_EXIST');
402 hr_utility.raise_error;
403 return(false);
404 --
405 end check_prepay;
406 --
407 --------------------------- check_ppm -----------------------------------
408 /*
409 NAME
410 check_ppm
411 DESCRIPTION
412 On delete check no PPMs depend on the OPM
413 NOTES
414 An OPM may not be deleted when there is a PPM which is dependant upon it.
415 Here DE deletes are what we are talking about.
416 eg
417 |-------------PPM-------------------|
418 |-------------OPM---------|
419 ^
420 DE Del Invalid
421 --
422 */
423 function check_ppm(val_start_date in varchar2,
424 opm_id in varchar2) return boolean is
425 dummy number;
426 begin
427 --
428 -- If there is a ppm which finishes after the validation start date, then
429 -- the (DE) delete is invalid. This case is given if no rows are returned
430 -- and is picked up in the error handler.
431 --
432 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',1);
433 --
434 select 1
435 into dummy
436 from dual
437 where not exists(
438 select 1
439 from pay_personal_payment_methods_f ppm
440 where ppm.org_payment_method_id = opm_id
441 and ppm.effective_end_date > fnd_date.canonical_to_date(val_start_date));
442 --
443 if dummy = 1 then
444 --
445 return(true);
446 --
447 end if;
448 --
449 return(false); -- Never should get here
450 --
451 exception
452 --
453 when no_data_found then
454 --
455 hr_utility.set_message(801,'HR_6235_PAYM_EXISTING_PPMS');
456 hr_utility.raise_error;
457 return(false);
458 --
459 end check_ppm;
460 --
461 --------------------------- check_default -----------------------------------
462 /*
463 NAME
464 check_default
465 DESCRIPTION
466 Check that on delete there are no payrolls using this method as default.
467 NOTES
468 Each payroll must have a default payment method. If this OPM is used as
469 default by any payrolls (DE mode) then it cannot be deleted.
470 */
471 function check_default(opm_id in varchar2,
472 val_start_date in varchar2) return boolean is
473 valid_del varchar2(2);
474 begin
475 --
476 -- Check if any payrolls use this OPM whichare valid after the delete date.
477 --
478 hr_utility.set_location('HR_PAYMENTS.CHECK_DEFAULT',1);
479 --
480 select 'Y'
481 into valid_del
482 from dual
483 where not exists(
484 select 1
485 from pay_payrolls_f pp
486 where pp.default_payment_method_id = opm_id
487 and pp.effective_end_date > fnd_date.canonical_to_date(val_start_date));
488 --
489 if valid_del = 'Y' then
490 --
491 return(true);
492 --
493 end if;
494 --
495 return(false); -- Should never be here
496 --
497 exception
498 --
499 when no_data_found then
500 --
501 hr_utility.set_message(801,'HR_6236_PAYM_USED_AS_DEFAULT');
502 hr_utility.raise_error;
503 return(false);
504 --
505 end check_default;
506 --
507 --------------------------- check_amt -----------------------------------
508 /*
509 NAME
510 check_amt
511 DESCRIPTION
512 Check the method is defined with a valid amount or percentage
513 NOTES
514 The PPM must be paid as an amount or percentage of original pay. Check that
515 one (and only one) of these has been specified for the method.
516 */
517 function check_amt(percent in varchar2, amount in varchar2) return boolean is
518 --
519 begin
520 --
521 if amount is null and percent is not null then
522 --
523 return(true);
524 --
525 elsif amount is not null and percent is null then
526 --
527 return(true);
528 --
529 else
530 --
531 hr_utility.set_message(801,'HR_6221_PAYM_INVALID_PPM');
532 hr_utility.raise_error;
533 return(false);
534 --
535 end if;
536 --
537 end;
538 --
539 --------------------------- mt_checks -----------------------------------
540 /*
541 NAME
542 mt_checks
543 DESCRIPTION
544 Check if the PPT category is 'MT'. If so do relevant checks.
545 NOTES
546 If MT then must have a valid external account to pay into.
547 */
548 function mt_checks(opm_id in varchar2,
549 val_start_date in varchar2,
550 account_id in varchar2) return boolean is
551 required_territory varchar2(16);
552 actual_territory varchar2(16);
553 category varchar2(20);
554 begin
555 --
556 -- Check if an account is specified and if so get the territory
557 --
558 hr_utility.set_location('HR_PAYMENTS.MT_CHECKS',1);
559 --
560 if account_id is null then
561 --
562 actual_territory := 'NONE';
563 --
564 else
565 --
566 begin
567 --
568 -- Check the account is valid
569 --
570 select exa.territory_code
571 into actual_territory
572 from pay_external_accounts exa
573 where exa.external_account_id = account_id;
574 --
575 exception
576 --
577 when no_data_found then
578 --
579 hr_utility.set_message(801,'HR_6223_PAYM_BAD_ACCT');
580 hr_utility.raise_error;
581 --
582 end;
583 --
584 end if;
585 --
586 -- Now make sure its in the right territory (if required)
587 --
588 hr_utility.set_location('HR_PAYMENTS.MT_CHECKS',2);
589 --
590 select nvl(ppt.territory_code, 'NONE'),
591 ppt.category
592 into required_territory,
593 category
594 from pay_payment_types ppt,
595 pay_org_payment_methods_f opm
596 where opm.org_payment_method_id = opm_id
597 and opm.payment_type_id = ppt.payment_type_id
598 and fnd_date.canonical_to_date(val_start_date) between
599 opm.effective_start_date and opm.effective_end_date;
600 --
601 -- Check the category. If it is 'MT' do the checks...
602 --
603 hr_utility.set_location('HR_PAYMENTS.MT_CHECKS',3);
604 --
605 if category = 'MT' then
606 --
607 if actual_territory <> required_territory then
608 --
609 if required_territory <> 'NONE' then
610 --
611 hr_utility.set_message(801,'HR_6220_PAYM_INVALID_ACT');
612 hr_utility.set_message_token('TERITORY',required_territory);
613 hr_utility.raise_error;
614 return(false);
615 --
616 end if;
617 end if;
618 --
619 end if;
620 --
621 return(true);
622 --
623 exception
624 --
625 when no_data_found then
626 --
627 hr_utility.set_message(801,'HR_6224_PAYM_NO_OPM');
628 hr_utility.raise_error;
629 return(false);
630 --
631 end mt_checks;
632 --
633 --------------------------- unique_priority -----------------------------------
634 /*
635 NAME
636 unique_priority
637 DESCRIPTION
638 Check the priority is unique at all times fo the assignment.
639 NOTES
640 Two PPMs for the same assignment must have different priorities, or the
641 results of pre-payments will be unrepeatable. This is called from the form
642 and as an update/insert trigger.
643 */
644 function unique_priority(in_priority in varchar2,
645 val_start_date in varchar2,
646 val_end_date in varchar2,
647 assignment in varchar2) return boolean is
648 duplicate varchar2(2);
649 begin
650 --
651 hr_utility.set_location('HR_PAYMENTS.UNIQUE_PRIORITY',1);
652 --
653 select 'N'
654 into duplicate
655 from sys.dual
656 where not exists(
657 select 1
658 from pay_personal_payment_methods_f ppm
659 where ppm.assignment_id = assignment
660 and ppm.priority = in_priority
661 and fnd_date.canonical_to_date(val_start_date) < ppm.effective_end_date
662 and fnd_date.canonical_to_date(val_end_date) > ppm.effective_start_date);
663 --
664 -- See how we did
665 --
666 if duplicate = 'N' then
667 --
668 return true;
669 --
670 end if;
671 --
672 exception
673 --
674 when no_data_found then
675 --
676 hr_utility.set_message(801,'HR_6225_PAYM_DUP_PRIORITY');
677 hr_utility.raise_error;
678 return false;
679 --
680 end unique_priority;
681 --
682 --------------------------- check_pp -----------------------------------
683 /*
684 NAME
685 check_pp
686 DESCRIPTION
687 Checks there are no outstanding pre-payments on delete
688 NOTES
689 Before deleting the PPM must make sure there are no PPs which use it after
690 val_start_date.
691 */
692 function check_pp(ppm_id in varchar2,
693 val_start_date in varchar2) return boolean is
694 status varchar2(2);
695 begin
696 --
697 hr_utility.set_location('HR_PAYMENTS.CHECK_PP',1);
698 --
699 -- WWbug 264094. Changed below from effective_date >= val_start_date to
700 -- effective_date > val_start_date. This has the effect of allowing the
701 -- delete if there are pre-payments on the deleteion date, providing there
702 -- aren't any after the date.
703 --
704 select 'Y'
705 into status
706 from sys.dual
707 where not exists(
708 select 1
709 from pay_payroll_actions pa,
710 pay_assignment_actions aa,
711 pay_pre_payments pp
712 where pp.personal_payment_method_id = ppm_id
713 and pp.assignment_action_id = aa.assignment_action_id
714 and aa.payroll_action_id = pa.payroll_action_id
715 and pa.effective_date > fnd_date.canonical_to_date(val_start_date));
716 --
717 -- If there is a row, then all is cool, but check anyhow to be defensive.
718 --
719 hr_utility.set_location('HR_PAYMENTS.CHECK_PP',2);
720 --
721 if status = 'Y' then
722 --
723 return true;
724 --
725 end if;
726 --
727 exception
728 --
729 when no_data_found then
730 --
731 -- This means future PPs exist so flag it.
732 --
733 hr_utility.set_message(801,'HR_6226_PAYM_PPS_EXIST');
734 hr_utility.raise_error;
735 return false;
736 --
737 end check_pp;
738 --
739
740
741 --------------------------- check_ppm -----------------------------------
742 /*
743 NAME
744 check_ppm used for selfservice pages
745 DESCRIPTION
746 Checks there are no outstanding pre-payments on update
747 NOTES
748 Before updating the PPM must make sure there are no PPs for the particular
749 transaction Added for bug 10392383
750 */
751
752 procedure check_ppm(trans_step_id in varchar2,
753 status out nocopy varchar2) is
754 ppm_id Number(9);
755 eff_date date;
756 begin
757 --
758 status := 'Y';
759 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',1);
760 hr_utility.trace('trans_step_id is '||trans_step_id);
761
762 /*Handled the No Data Found for the bug 13010568 */
763
764 begin
765 SELECT personal_payment_method_id
766 ,effective_date
767 INTO ppm_id, eff_date
768 FROM pay_pss_transaction_steps
769 WHERE transaction_step_id = trans_step_id;
770 exception
771 --
772 when no_data_found then
773 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',11);
774 status := 'Y';
775 return;
776 end;
777
778 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',2);
779
780 select 'Y'
781 into status
782 from sys.dual
783 where not exists(
784 select 1
785 from pay_payroll_actions pa,
786 pay_assignment_actions aa,
787 pay_pre_payments pp
788 where pp.personal_payment_method_id = ppm_id
789 and pp.assignment_action_id = aa.assignment_action_id
790 and aa.payroll_action_id = pa.payroll_action_id
791 and pa.effective_date > eff_date);
792
793 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',4);
794 --
795 if status = 'Y' then
796 status := 'Y';
797 --
798 end if;
799 --
800 exception
801 --
802 when no_data_found then
803 hr_utility.set_location('HR_PAYMENTS.CHECK_PPM',5);
804 status := 'N';
805 --
806 end check_ppm;
807 --
808 end hr_payments;
[Home] [Help]