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