[Home] [Help]
PACKAGE BODY: APPS.HR_PRE_PAY
Source
1 package body hr_pre_pay as
2 /* $Header: pyprepyt.pkb 120.3.12010000.1 2008/07/27 23:26:49 appldev ship $ */
3 --
4 -- Payment details record type
5 --
6 type pay_method_type is record
7 (category varchar2(16),
8 currency varchar2(16),
9 dbase_item varchar2(160),
10 exchange_rate number,
11 cash_rule varchar2(40),
12 payment_method_id number(15)
13 );
14 --
15 -- Cash analysis record type
16 --
17 type cash_type is record
18 (cash_left number,
19 pre_payment number(16),
20 currency varchar2(8),
21 precision number,
22 cash_paid number,
23 val_mode varchar2(20),
24 a_action_id number
25 );
26 --
27 type pre_pay_rec is record
28 (pre_payment_id pay_pre_payments.pre_payment_id%type,
29 personal_payment_method_id pay_pre_payments.personal_payment_method_id%type,
30 assignment_action_id pay_pre_payments.assignment_action_id%type,
31 org_payment_method_id pay_pre_payments.org_payment_method_id%type,
32 value pay_pre_payments.value%type,
33 base_currency_value pay_pre_payments.base_currency_value%type,
34 source_action_id pay_pre_payments.source_action_id%type,
35 prepayment_action_id pay_pre_payments.prepayment_action_id%type,
36 organization_id pay_pre_payments.organization_id%type,
37 payees_org_payment_method_id pay_pre_payments.payees_org_payment_method_id%type,
38 effective_date pay_pre_payments.effective_date%type,
39 category pay_payment_types.category%type,
40 cash_rule varchar2(40),
41 payment_currency varchar2(16),
42 base_currency varchar2(16)
43 );
44 --
45 type pre_pay_tab is table of pre_pay_rec index by binary_integer;
46 --
47 cursor g_third_party (p_assignment_action in number) is
48 select pet2.output_currency_code,
49 pet2.element_type_id,
50 prr2.run_result_id,
51 paa.assignment_id
52 from pay_action_interlocks pai2,
53 pay_run_results prr2,
54 pay_element_types_f pet2,
55 pay_payroll_actions ppa,
56 pay_assignment_actions paa
57 where pai2.locking_action_id = p_assignment_action
58 and pai2.locked_action_id = prr2.assignment_action_id
59 and paa.assignment_action_id = pai2.locked_action_id
60 and ppa.payroll_action_id = paa.payroll_action_id
61 and prr2.element_type_id = pet2.element_type_id
62 and prr2.source_type = 'E'
63 and pet2.third_party_pay_only_flag = 'Y'
64 and ppa.effective_date between
65 pet2.effective_start_date and pet2.effective_end_date
66 and prr2.entry_type NOT IN ('R','A');
67
68 --
69 cursor g_pp_methods (p_assignment in number,
70 p_effective_date in varchar2,
71 p_def_balance in number) is
72 select ppt.category category,
73 ppm.personal_payment_method_id personal_method,
74 pea.prenote_date prenote_date,
75 ppt.validation_days valid_days,
76 ppm.percentage percentage,
77 ppm.amount amount,
78 opm.org_payment_method_id org_method,
79 hr_pre_pay.set_cash_rule(ppt.category,
80 opm.pmeth_information1) cash_rule,
81 opm.currency_code payment_currency,
82 ppt.pre_validation_required validation_required,
83 ppt.validation_value validation_value,
84 opm.external_account_id external_account_id
85 -- from hr_lookups hlu,
86 from pay_external_accounts pea,
87 pay_payment_types ppt,
88 pay_org_payment_methods_f opm,
89 pay_personal_payment_methods_f ppm
90 where ppm.assignment_id = p_assignment
91 and ppm.run_type_id is null
92 and ppm.org_payment_method_id = opm.org_payment_method_id
93 and opm.payment_type_id = ppt.payment_type_id
94 and opm.defined_balance_id = p_def_balance
95 and ppm.external_account_id = pea.external_account_id (+)
96 -- and opm.pmeth_information1 = hlu.lookup_code (+)
97 -- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
98 -- and NVL(hlu.application_id, 800) = 800
99 and fnd_date.canonical_to_date(p_effective_date) between
100 ppm.effective_start_date and ppm.effective_end_date
101 and fnd_date.canonical_to_date(p_effective_date) between
102 opm.effective_start_date and opm.effective_end_date
103 order by ppm.person_id,ppm.priority;
104 --
105 -- Coinage cursor. fetch all monetary units of a currency in order of value
106 --
107 cursor coin_cursor(currency in varchar2,
108 ass_act in number) is
109 select pmu.monetary_unit_id,
110 pmu.relative_value
111 from pay_monetary_units pmu,
112 per_business_groups_perf pbg,
113 pay_payroll_actions pac,
114 pay_assignment_actions pas
115 where pmu.currency_code = currency
116 and pbg.business_group_id = pac.business_group_id
117 and pac.payroll_action_id = pas.payroll_action_id
118 and pas.assignment_action_id = ass_act
119 and (pmu.business_group_id = pbg.business_group_id
120 or (pmu.business_group_id is null
121 and pmu.legislation_code = pbg.legislation_code)
122 or (pmu.business_group_id is null
123 and pmu.legislation_code is null)
124 )
125 order by pmu.relative_value desc;
126 --
127 got_renumerate boolean;
128 balance_currency varchar2(16);
129 payroll_action number(16);
130 payroll number(16);
131 negative_pay varchar2(30);
132 pre_payment_date date;
133 default_method pay_method_type;
134 cash_detail cash_type;
135 effective_date date;
136 override pay_method_type;
137 pay_currency_type varchar2(30);
138 g_adjust_ee_source varchar2(1);
139 g_pre_payments pre_pay_tab;
140 --
141 function set_cash_rule(p_type in varchar2, p_seg1 in varchar2)
142 return varchar2 is
143 cash_rule hr_lookups.description%type;
144 begin
145 if (p_type = 'CA') then
146 select hlu.description
147 into cash_rule
148 from hr_lookups hlu
149 where p_seg1 = hlu.lookup_code
150 and hlu.lookup_type = 'CASH_ANALYSIS'
151 and NVL(hlu.application_id, 800) = 800 ;
152 --
153 return cash_rule;
154 else
155 return null;
156 end if;
157 --
158 exception
159 when no_data_found then
160 return null;
161 end set_cash_rule;
162 --
163 --
164 -- PROCEDURES --
165 --
166 --
167 -- UTILITY ROUTINES --
168 --
169 --
170 --------------------------- ins_coin_el -----------------------------------
171 /*
172 NAME
173 ins_coin_el
174 DESCRIPTION
175 pay the given number of monetary units
176 NOTES
177 This is the base unit for inserting coin anal elements for a payment.
178 The monetary unit is supplied and the routine pays as specified. When the
179 user calls it, it is possible to leave monetary unit null, and the
180 routine will determine the correct value. NB all operations on the
181 amount of cash left are protected from the user.
182 */
183 procedure ins_coin_el(monetary_unit in number,
184 no_of_units in number,
185 factor in number) is
186 amount_to_pay number;
187 no_units number;
188 begin
189 --
190 -- Calculate the amount of cash to pay
191 --
192 amount_to_pay := factor * no_of_units;
193 --
194 -- See if there is enough to pay
195 --
196 if amount_to_pay <= cash_detail.cash_left then
197 --
198 no_units := no_of_units;
199 cash_detail.cash_left := cash_detail.cash_left - amount_to_pay;
200 cash_detail.cash_paid := cash_detail.cash_paid + amount_to_pay;
201 --
202 else
203 --
204 no_units := floor(cash_detail.cash_left/factor);
205 cash_detail.cash_left := cash_detail.cash_left - (no_of_units * factor);
206 cash_detail.cash_paid := cash_detail.cash_paid + (no_of_units * factor);
207 --
208 end if;
209 --
210 cash_detail.cash_left := round(cash_detail.cash_left, cash_detail.precision);
211 --
212 if (cash_detail.val_mode = 'TRANSFER') then
213 --
214 -- Now make the payment
215 --
216 hr_utility.set_location('HR_PRE_PAY.INS_COIN_EL',2);
217 --
218 insert into pay_coin_anal_elements(
219 coin_anal_element_id,
220 pre_payment_id,
221 monetary_unit_id,
222 number_of_monetary_units)
223 values(
224 pay_coin_anal_elements_s.nextval,
225 cash_detail.pre_payment,
226 monetary_unit,
227 no_units);
228 --
229 end if;
230 --
231 end ins_coin_el;
232 --
233 --------------------------- pay_coin -----------------------------------
234 /*
235 NAME
236 pay_coin
237 DESCRIPTION
238 pay the given number of monetary units
239 NOTES
240 This is called from the user cash analysis routine. It pays the number of
241 monetary units specifed for the payment currency where the value of the unit
242 is given relative to the base value (eg dollar, pound)
243 */
244 procedure pay_coin(no_of_units in number, factor in number) is
245 monetary_unit number(16);
246 begin
247 --
248 -- Firstly get the monetary unit of the payment.
249 --
250 hr_utility.set_location('HR_PRE_PAY.PAY_COIN',1);
251 --
252 select pmu.monetary_unit_id
253 into monetary_unit
254 from pay_monetary_units pmu,
255 pay_assignment_actions pas,
256 pay_payroll_actions pac,
257 per_business_groups_perf pbg
258 where cash_detail.a_action_id = pas.assignment_action_id
259 and pac.payroll_action_id = pas.payroll_action_id
260 and pbg.business_group_id = pac.business_group_id
261 and pmu.currency_code = cash_detail.currency
262 and (pmu.business_group_id = pbg.business_group_id
263 or (pmu.business_group_id is null
264 and pmu.legislation_code = pbg.legislation_code)
265 or (pmu.business_group_id is null
266 and pmu.legislation_code is null)
267 )
268 and pmu.relative_value = factor;
269 --
270 -- Now calculate the amount of cash to pay
271 --
272 ins_coin_el(monetary_unit, no_of_units, factor);
273 --
274 end pay_coin;
275 --
276 -------------------------- do_cash_analysis -----------------------------
277 /*
278 NAME
279 do_cash_analysis
280 DESCRIPTION
281 Perform cash analysis
282 NOTES
283 Cash analysis is performed to divide a payment down into constituent
284 monetary units. The user may wish to specify certain payments, eg
285 3 five dollar notes in each pay packet. This can be accomplished in
286 hr_cash_rules.user_rule which the user can alter. This is called
287 with the cash_rule parameter. After it returns the rest of the payment
288 (or all of it if no rule was specified) is paid using the default method
289 (ie use the highest denomination note possible). Note all inserts to
290 pay_coin_anal_elements are made through pay_coin
291 */
292 --
293 procedure do_cash_analysis(payment in number,
294 cash_rule in varchar2,
295 payment_id in number,
296 pay_currency in varchar2,
297 action_id in number,
298 val_mode in varchar2 default 'TRANSFER',
299 pay_left in out nocopy number) is
300 monetary_unit number(16);
301 no_units number(6);
302 factor number;
303 precision number;
304 begin
305 --
306 -- First set up the details of the payment in the cash record. Note this
307 -- is unavailable to the user cash_rule.
308 --
309 cash_detail.pre_payment := payment_id;
310 cash_detail.cash_left := payment;
311 cash_detail.cash_paid := 0;
312 cash_detail.val_mode := val_mode;
313 cash_detail.currency := pay_currency;
314 cash_detail.a_action_id := action_id;
315 --
316 -- Get the number of decimal places used by the currency
317 --
318 hr_utility.set_location('HR_PRE_PAY.DO_CASH_ANALYSIS',1);
319 --
320 select cur.precision
321 into cash_detail.precision
322 from fnd_currencies cur
323 where cur.currency_code = pay_currency;
324 --
325 -- Call The user accessible cash rule function
326 --
327 if cash_rule is not null then
328 --
329 hr_cash_rules.user_rule(cash_rule);
330 --
331 end if;
332 --
333 -- At this point, if the user has impleneted any rules they will be
334 -- represeneted in pay_coin_anal_elements already. Cash_left will
335 -- represent what hasn't been assigned a monetary unit.
336 -- Now pay this remainder by default.
337 --
338 hr_utility.set_location('HR_PRE_PAY.DO_CASH_ANALYSIS',2);
339 --
340 open coin_cursor(pay_currency, action_id);
341 --
342 -- Cursor orders by value (hi-lo). While there is cash left pay it.
343 --
344 while cash_detail.cash_left > 0 loop
345 --
346 hr_utility.set_location('HR_PRE_PAY.DO_CASH_ANALYSIS',3);
347 --
348 fetch coin_cursor into monetary_unit, factor;
349 --
350 if coin_cursor%notfound then
351 --
352 if (cash_detail.val_mode = 'TRANSFER') then
353 close coin_cursor;
354 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
355 hr_utility.raise_error;
356 else
357 close coin_cursor;
358 --
359 declare
360 pp_ptr number;
361 begin
362 --
363 pp_ptr := g_pre_payments.count;
364 --
365 -- We cant pay total amount by cash, check the currencies.
366 if (g_pre_payments(pp_ptr).base_currency <>
367 g_pre_payments(pp_ptr).payment_currency)
368 then
369 --
370 -- Since the payment currency is not the same as base
371 -- error out. Lets not get into exchange rate conversions.
372 --
373 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
374 hr_utility.raise_error;
375 --
376 end if;
377 --
378 -- Lets take money off the payment amount and put it back
379 -- in the general payment pot.
380 g_pre_payments(pp_ptr).value := cash_detail.cash_paid;
381 g_pre_payments(pp_ptr).base_currency_value := cash_detail.cash_paid;
382 pay_left := pay_left + cash_detail.cash_left;
383 cash_detail.cash_left := 0;
384 --
385 end;
386 end if;
387 --
388 else
389 --
390 no_units := floor(cash_detail.cash_left / factor);
391 --
392 -- insert the units if there are any
393 --
394 if no_units <> 0 then
395 --
396 ins_coin_el(monetary_unit,no_units,factor);
397 --
398 end if;
399 --
400 end if;
401 --
402 end loop;
403 --
404 hr_utility.set_location('HR_PRE_PAY.DO_CASH_ANALYSIS',4);
405 --
406 if (coin_cursor%ISOPEN) then
407 hr_utility.set_location('HR_PRE_PAY.DO_CASH_ANALYSIS',5);
408 close coin_cursor;
409 end if;
410 --
411 end do_cash_analysis;
412 --
413 -------------------------- flush_payments ------------------------------
414 /*
415 NAME
416 flush_payments
417 DESCRIPTION
418 Creates pre-payment records for the contents of the pre Payments
419 buffer.
420 NOTES
421 This procedure creates a new rows in the pre-payments table. It does
422 any currency conversion required and calls cash analysis if this is
423 required.
424 */
425 --
426 procedure flush_payments
427 is
428 cnt number;
429 pay_left number := 0;
430 payment_id number;
431 begin
432 --
433 for cnt in 1..g_pre_payments.count loop
434 --
435 if (g_pre_payments(cnt).value <> 0
436 or g_pre_payments(cnt).category = 'MT') then
437 --
438 select pay_pre_payments_s.nextval
439 into payment_id
440 from dual;
441 --
442 g_pre_payments(cnt).pre_payment_id := payment_id;
443 --
444 insert into pay_pre_payments
445 (pre_payment_id,
446 personal_payment_method_id,
447 org_payment_method_id,
448 value,
449 base_currency_value,
450 assignment_action_id,
451 source_action_id,
452 prepayment_action_id,
453 organization_id,
454 payees_org_payment_method_id,
455 effective_date)
456 values (g_pre_payments(cnt).pre_payment_id,
457 g_pre_payments(cnt).personal_payment_method_id,
458 g_pre_payments(cnt).org_payment_method_id,
459 g_pre_payments(cnt).value,
460 g_pre_payments(cnt).base_currency_value,
461 g_pre_payments(cnt).assignment_action_id,
462 g_pre_payments(cnt).source_action_id,
463 g_pre_payments(cnt).prepayment_action_id,
464 g_pre_payments(cnt).organization_id,
465 g_pre_payments(cnt).payees_org_payment_method_id,
466 g_pre_payments(cnt).effective_date);
467 --
468 -- If the category of the payment type is CASH, do cash analysis.
469 --
470 if g_pre_payments(cnt).category = 'CA'
471 and g_pre_payments(cnt).value > 0 then
472 --
473 do_cash_analysis(g_pre_payments(cnt).value,
474 g_pre_payments(cnt).cash_rule,
475 g_pre_payments(cnt).pre_payment_id,
476 g_pre_payments(cnt).payment_currency,
477 g_pre_payments(cnt).assignment_action_id,
478 'TRANSFER',
479 pay_left);
480 --
481 end if;
482 end if;
483 --
484 end loop;
485 --
486 g_pre_payments.delete;
487 --
488 end flush_payments;
489 --
490 ------------------- override_mult_tax_unit_payment --------------------
491 /*
492 NAME
493 override_mult_tax_unit_payment
494 DESCRIPTION
495 For legislations that accumulate multi GRE payments, check the
496 override at the business group level.
497 NOTES
498 This procedure creates a new row in the pre-payments table. It does
499 any currency conversion required and calls cash analysis if this is
500 required.
501 */
502 procedure override_mult_tax_unit_payment(p_business_group_id in number,
503 p_multi_tax_unit_payment in out nocopy varchar2)
504 is
505 leg_code per_business_groups_perf.legislation_code%type;
506 statem varchar2(2000);
507 sql_cursor integer;
508 l_rows integer;
509 begin
510 --
511 select legislation_code
512 into leg_code
513 from per_business_groups_perf
514 where business_group_id = p_business_group_id;
515 --
516 statem := 'begin pay_'||leg_code||'_rules.get_multi_tax_unit_pay_flag(';
517 statem := statem||':bus_grp, :mtup_flag); end;';
518 --
519 sql_cursor := dbms_sql.open_cursor;
520 --
521 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
522 --
523 --
524 dbms_sql.bind_variable(sql_cursor, 'bus_grp', p_business_group_id);
525 --
526 dbms_sql.bind_variable(sql_cursor, 'mtup_flag', p_multi_tax_unit_payment);
527 --
528 l_rows := dbms_sql.execute (sql_cursor);
529 --
530 if (l_rows = 1) then
531 dbms_sql.variable_value(sql_cursor, 'mtup_flag',
532 p_multi_tax_unit_payment);
533 dbms_sql.close_cursor(sql_cursor);
534 --
535 else
536 p_multi_tax_unit_payment := 'Y';
537 dbms_sql.close_cursor(sql_cursor);
538 end if;
539 --
540 end override_mult_tax_unit_payment;
541 --
542 -------------------------- create_payment ------------------------------
543 /*
544 NAME
545 create_payment
546 DESCRIPTION
547 Create a pre-payment record for this assignment action and pay method.
548 NOTES
549 This procedure creates a new row in the pre-payments table. It does
550 any currency conversion required and calls cash analysis if this is
551 required.
552 */
553 --
554 procedure create_payment (base_value in number,
555 base_currency in varchar2,
556 pay_currency in varchar2,
557 -- exchange_rate in number,
558 personal_method_id in number,
559 org_method_id in number,
560 action_id in number,
561 category in varchar2,
562 cash_rule in varchar2,
563 src_act_id in number default null,
564 prepayment_action_id in number default null,
565 pay_left in out nocopy number,
566 p_org_id in number default null,
567 p_payee_opm_id in number default null,
568 p_effective_date in date default null) is
569 payment_id number(16);
570 payment number;
571 lgcode varchar2(30);
572 l_ext_acc number(16);
573 l_effective_date date;
574 l_org_method_id number(16);
575 --
576 begin
577 --
578 hr_utility.trace('Enter create_payment');
579 if base_currency <> pay_currency then
580 --
581 begin
582 if (pay_currency_type is NULL)
583 then
584 hr_utility.set_message(801,'HR_52349_NO_RATE_TYPE');
585 hr_utility.raise_error;
586 end if;
587 payment:=hr_currency_pkg.convert_amount(base_currency,
588 pay_currency,
589 pre_payment_date,
590 base_value,
591 pay_currency_type);
592
593 exception
594 --
595 when no_data_found then
596 hr_utility.set_message(801,'HR_6405_PAYM_NO_EXCHANGE_RATE');
597 hr_utility.set_message_token('RATE1', base_currency);
598 hr_utility.set_message_token('RATE2', pay_currency);
599 hr_utility.raise_error;
600
601 when gl_currency_api.NO_RATE then
602 --
603 hr_utility.set_message(801,'HR_6405_PAYM_NO_EXCHANGE_RATE');
604 hr_utility.set_message_token('RATE1', base_currency);
605 hr_utility.set_message_token('RATE2', pay_currency);
606 hr_utility.raise_error;
607
608 when gl_currency_api.INVALID_CURRENCY then
609 hr_utility.set_message(801,'HR_52350_INVALID_CURRENCY');
610 hr_utility.set_message_token('RATE1', base_currency);
611 hr_utility.set_message_token('RATE2', pay_currency);
612 hr_utility.raise_error;
613
614 --
615 end;
616 --
617 else
618 --
619 payment := base_value;
620 --
621 end if;
622 --
623 -- Now derive override org payment method when external account id is null.
624 --
625 select opm.external_account_id
626 into l_ext_acc
627 from pay_org_payment_methods_f opm,
628 pay_assignment_actions paa,
629 pay_payroll_actions ppa
630 where paa.assignment_action_id = action_id
631 and ppa.payroll_action_id = paa.payroll_action_id
632 and opm.org_payment_method_id = org_method_id
633 and ppa.effective_date between
634 opm.effective_start_date and opm.effective_end_date;
635 --
636 if (l_ext_acc is null) then
637 select pbg.legislation_code, ppa.effective_date
638 into lgcode, l_effective_date
639 from pay_assignment_actions asg,
640 per_business_groups_perf pbg,
641 pay_payroll_actions ppa
642 where ppa.payroll_action_id = asg.payroll_action_id
643 and asg.assignment_action_id = action_id
644 and ppa.business_group_id = pbg.business_group_id;
645 --
646 get_dynamic_org_method('pay_' || lgcode || '_rules.get_dynamic_org_meth',
647 action_id,
648 l_effective_date,
649 org_method_id,
650 l_org_method_id);
651 --
652 else l_org_method_id := org_method_id;
653 --
654 end if;
655 --
656 -- Now insert the pre-payment record. NB Only if it is non-zero
657 --
658 if payment <> 0 then
659 --
660 -- Save the ID for cash analysis
661 --
662 hr_utility.set_location('HR_PRE_PAY.CREATE_PAYMENT',1);
663 --
664 payment_id := null;
665 declare
666 ins_cnt number;
667 begin
668 --
669 ins_cnt := g_pre_payments.count + 1;
670 g_pre_payments(ins_cnt).pre_payment_id := payment_id;
671 g_pre_payments(ins_cnt).personal_payment_method_id := personal_method_id;
672 g_pre_payments(ins_cnt).org_payment_method_id := l_org_method_id;
673 g_pre_payments(ins_cnt).value := payment;
674 g_pre_payments(ins_cnt).base_currency_value := base_value;
675 g_pre_payments(ins_cnt).assignment_action_id := action_id;
676 g_pre_payments(ins_cnt).source_action_id := src_act_id;
677 g_pre_payments(ins_cnt).prepayment_action_id := prepayment_action_id;
678 g_pre_payments(ins_cnt).category := category;
679 g_pre_payments(ins_cnt).cash_rule := cash_rule;
680 g_pre_payments(ins_cnt).payment_currency := pay_currency;
681 g_pre_payments(ins_cnt).base_currency := base_currency;
682 g_pre_payments(ins_cnt).organization_id := p_org_id;
683 g_pre_payments(ins_cnt).payees_org_payment_method_id
684 := p_payee_opm_id;
685 g_pre_payments(ins_cnt).effective_date := p_effective_date;
686 end;
687 --
688 --
689 -- If the category of the payment type is 'CASH', do cash analysis validation
690 --
691 if category = 'CA' and payment > 0 then
692 --
693 do_cash_analysis(payment, cash_rule, payment_id,
694 pay_currency,
695 action_id, 'VALIDATE', pay_left);
696 hr_utility.trace('pay_left = '||pay_left);
697 --
698 end if;
699 end if;
700 --
701 hr_utility.trace('Exit create_payment');
702 end create_payment;
703 --
704 -------------------------- pay_method ------------------------------
705 /*
706 NAME
707 pay_method
708 DESCRIPTION
709 Calculate the base amount for this payment.
710 NOTES
711 If the PPM has a percentage calculate this, otherwise pay the amount
712 specified. Calculate the new pay left value. If there is not enough to
713 pay the required amount pay as much as possible.
714 */
715 --
716 procedure pay_method(pay_left in out nocopy number,
717 total_pay in number,
718 base_pay out nocopy number,
719 percentage in number,
720 amount in number,
721 pay_currency in varchar2) is
722 value number;
723 --
724 begin
725 --
726 -- Get the percentage or amount to be paid
727 --
728 hr_utility.set_location('HR_PRE_PAY.PAY_METHOD',1);
729 --
730 select cur.precision
731 into cash_detail.precision
732 from fnd_currencies cur
733 where cur.currency_code = pay_currency;
734 --
735 if percentage >= 0 then
736 --
737 value := total_pay * (percentage / 100);
738 value := ceil(value * to_number(rpad('1',
739 cash_detail.precision + 1, '0')));
740 value := value /to_number(rpad('1',
741 cash_detail.precision + 1, '0'));
742 --
743 if value <= pay_left then
744 --
745 base_pay := value;
746 pay_left := pay_left - value;
747 --
748 else
749 --
750 base_pay := pay_left;
751 pay_left := 0;
752 --
753 end if;
754 --
755 else
756 --
757 -- percent not specified so there must be an amount
758 --
759 value := pay_left - amount;
760 --
761 if value >= 0 then
762 --
763 base_pay := amount;
764 pay_left := value;
765 --
766 else
767 --
768 base_pay := pay_left;
769 pay_left := 0;
770 --
771 end if;
772 --
773 end if;
774 -- --
775 end pay_method;
776 --
777 -------------------------- validate_magnetic ------------------------------
778 /*
779 NAME
780 validate_magnetic
781 DESCRIPTION
782 Return true if this magnetic payment method is valid to be used, if it has
783 been pre-validated (if required).
784 NOTES
785 If this is a valid method return true. Otherwise determine if a
786 pre-validation record is required, and if so insert one into
787 pre-payments.
788 */
789 --
790 function validate_magnetic(personal_method in number,
791 valid_date in date,
792 prenote_date in date,
793 org_method in number,
794 action_id in number,
795 validation_value in number,
796 p_org_id in number default null,
797 p_payee_opm in number default null,
798 p_effdate in date default null) return boolean is
799 --
800 begin
801 --
802 hr_utility.set_location('HR_PRE_PAY.VALIDATE_MAGNETIC',1);
803 --
804 if prenote_date is null then
805 --
806 -- insert a prenote entry.
807 --
808 hr_utility.set_location('HR_PRE_PAY.VALIDATE_MAGNETIC',2);
809 insert into pay_pre_payments
810 (pre_payment_id,
811 personal_payment_method_id,
812 org_payment_method_id,
813 value,
814 base_currency_value,
815 assignment_action_id,
816 organization_id,
817 payees_org_payment_method_id,
818 effective_date)
819 values (pay_pre_payments_s.nextval,
820 personal_method,
821 org_method,
822 validation_value,
823 0,
824 action_id,
825 p_org_id,
826 p_payee_opm,
827 p_effdate);
828 --
829 -- return false as method not yet valid.
830 --
831 return false;
832 --
833 end if;
834 hr_utility.set_location('HR_PRE_PAY.VALIDATE_MAGNETIC',3);
835 if valid_date >= prenote_date then
836 --
837 return true;
838 --
839 else
840 --
841 return false;
842 --
843 end if;
844 --
845 end validate_magnetic;
846 --
847 --
848 -- NORMAL PAYMENTS --
849 --
850 -------------------------- initialise ------------------------------
851 /*
852 NAME
853 initialise
854 DESCRIPTION
855 Initialise global data for normal payments
856 NOTES
857 Return the database_item name for assignment remuneration payments balance.
858 Save global data such as payroll action for future refernce
859 Set up the default payment method in memory ready to be accessed.
860 */
861 --
862 procedure initialise(action_id in out nocopy varchar2) is
863 pay_bg_id number(16);
864 --
865 begin
866 --
867 hr_utility.set_location('HR_PRE_PAY.Initialise',1);
868 --
869 payroll_action := to_number(action_id);
870 --
871 begin
872 --
873 select ppt.category,
874 UPPER(translate(pbt.balance_name,' ','_') ||
875 pbd.database_item_suffix),
876 pbt.currency_code,
877 hr_pre_pay.set_cash_rule(ppt.category,
878 opm.pmeth_information1),
879 opm.currency_code,
880 pp.default_payment_method_id,
881 ppa.effective_date,
882 pp.negative_pay_allowed_flag,
883 ppa.payroll_id,
884 ppa.business_group_id
885 into default_method.category,
886 default_method.dbase_item,
887 balance_currency,
888 default_method.cash_rule,
889 default_method.currency,
890 default_method.payment_method_id,
891 pre_payment_date,
892 negative_pay,
893 payroll,
894 pay_bg_id
895 from pay_balance_dimensions pbd,
896 pay_balance_types pbt,
897 pay_defined_balances pdb,
898 pay_payment_types ppt,
899 pay_all_payrolls_f pp,
900 pay_org_payment_methods_f opm,
901 pay_payroll_actions ppa
902 where ppa.payroll_action_id = payroll_action
903 and ppa.payroll_id = pp.payroll_id
904 and pp.default_payment_method_id = opm.org_payment_method_id
905 and opm.payment_type_id = ppt.payment_type_id
906 and opm.defined_balance_id = pdb.defined_balance_id
907 and pdb.balance_type_id = pbt.balance_type_id
908 and pdb.balance_dimension_id = pbd.balance_dimension_id
909 and pbd.payments_flag = 'Y'
910 and pbt.assignment_remuneration_flag = 'Y'
911 -- and opm.pmeth_information1 = hlu.lookup_code (+)
912 -- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
913 -- and NVL(hlu.application_id, 800) = 800
914 and ppa.effective_date between
915 pp.effective_start_date and pp.effective_end_date
916 and ppa.effective_date between
917 opm.effective_start_date and opm.effective_end_date;
918
919 pay_currency_type:=hr_currency_pkg.get_rate_type(pay_bg_id,pre_payment_date,'P');
920 exception
921 --
922 when others then
923 --
924 hr_utility.set_message(801,'HR_6238_PAYM_NO_DEFAULT');
925 hr_utility.raise_error;
926 --
927 end;
928 --
929 --
930 hr_utility.set_location('HR_PRE_PAY.Initialise',2);
931 --
932 begin
933 select plr.rule_mode
934 into g_adjust_ee_source
935 from pay_legislation_rules plr,
936 per_business_groups_perf pbg,
937 pay_payroll_actions ppa
938 where ppa.payroll_action_id = payroll_action
939 and ppa.business_group_id = pbg.business_group_id
940 and pbg.legislation_code = plr.legislation_code
941 and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
942 --
943 exception
944 when no_data_found then
945 g_adjust_ee_source := 'A';
946 end;
947 --
948 hr_utility.set_location('HR_PRE_PAY.Initialise',3);
949 --
950 end initialise;
951 --
952 --
953 -- OVERRIDE PAYMENTS --
954 --
955 --
956 -------------------------- init_override ------------------------------
957 /*
958 NAME
959 init_override
960 DESCRIPTION
961 initialise for an override payment method.
962 NOTES
963 Fetch details of the required override payment into memory. Generate an
964 error if this is not a valid method. Return the name of the balance
965 database item to 'C'
966 */
967 --
968 procedure init_override(action_id in out nocopy varchar2,
969 override_method in out nocopy varchar2) is
970 --
971 pay_bg_id number(16);
972 begin
973 --
974 hr_utility.set_location('HR_PRE_PAY.INIT_OVERRIDE',1);
975 --
976 payroll_action := to_number(action_id);
977 override.payment_method_id := to_number(override_method);
978 --
979 begin
980 --
981 select ppt.category,
982 UPPER(translate(pbt.balance_name,' ','_') ||
983 pbd.database_item_suffix),
984 pbt.currency_code,
985 hr_pre_pay.set_cash_rule(ppt.category,
986 opm.pmeth_information1),
987 opm.currency_code,
988 ppa.payroll_id,
989 pp.negative_pay_allowed_flag,
990 ppa.effective_date,
991 ppa.business_group_id
992 into override.category,
993 override.dbase_item,
994 balance_currency,
995 override.cash_rule,
996 override.currency,
997 payroll,
998 negative_pay,
999 pre_payment_date,
1000 pay_bg_id
1001 from pay_all_payrolls_f pp,
1002 pay_balance_dimensions pbd,
1003 pay_balance_types pbt,
1004 pay_defined_balances pdb,
1005 pay_payment_types ppt,
1006 pay_org_pay_method_usages_f pmu,
1007 pay_org_payment_methods_f opm,
1008 pay_payroll_actions ppa
1009 where ppa.payroll_action_id = payroll_action
1010 and ppa.payroll_id = pmu.payroll_id
1011 and ppa.payroll_id = pp.payroll_id
1012 and pmu.org_payment_method_id = override_method
1013 and pmu.org_payment_method_id = opm.org_payment_method_id
1014 and opm.payment_type_id = ppt.payment_type_id
1015 and ppt.category <> 'MT'
1016 and opm.defined_balance_id = pdb.defined_balance_id
1017 and pdb.balance_type_id = pbt.balance_type_id
1018 and pdb.balance_dimension_id = pbd.balance_dimension_id
1019 and pbd.payments_flag = 'Y'
1020 and pbt.assignment_remuneration_flag = 'Y'
1021 -- and opm.pmeth_information1 = hlu.lookup_code (+)
1022 -- and NVL(hlu.lookup_type ,'CASH_ANALYSIS') = 'CASH_ANALYSIS'
1023 -- and NVL(hlu.application_id, 800) = 800
1024 and ppa.effective_date between
1025 pp.effective_start_date and pp.effective_end_date
1026 and ppa.effective_date between
1027 pmu.effective_start_date and pmu.effective_end_date
1028 and ppa.effective_date between
1029 opm.effective_start_date and opm.effective_end_date;
1030 --
1031 pay_currency_type:=hr_currency_pkg.get_rate_type(pay_bg_id,pre_payment_date,'P');
1032 exception
1033 --
1034 when others then
1035 --
1036 hr_utility.set_message(801,'HR_6239_PAYM_INVALID_OVERRIDE');
1037 hr_utility.raise_error;
1038 --
1039 end;
1040 --
1041 begin
1042 select plr.rule_mode
1043 into g_adjust_ee_source
1044 from pay_legislation_rules plr,
1045 per_business_groups_perf pbg,
1046 pay_payroll_actions ppa
1047 where ppa.payroll_action_id = payroll_action
1048 and ppa.business_group_id = pbg.business_group_id
1049 and pbg.legislation_code = plr.legislation_code
1050 and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
1051 --
1052 exception
1053 when no_data_found then
1054 g_adjust_ee_source := 'A';
1055 end;
1056 --
1057 --
1058 end init_override;
1059 --
1060 -------------------------- get_ren_balance ----------------------------------
1061 /*
1062 NAME
1063 get_ren_balance
1064 DESCRIPTION
1065 Gets the renumeration balance details that have to be paid in this run.
1066 */
1067 --
1068 procedure get_ren_balance(p_bus_grp in number,
1069 p_def_bal_id in out nocopy number) is
1070 def_bal number;
1071 bus_leg pay_balance_types.legislation_code%type;
1072 bal_found boolean;
1073 begin
1074 --
1075 bal_found := FALSE;
1076 begin
1077 select distinct pdb.defined_balance_id,
1078 pbt.currency_code
1079 into def_bal,
1080 balance_currency
1081 from pay_balance_types pbt,
1082 pay_defined_balances pdb,
1083 pay_balance_dimensions pbd
1084 where pbt.business_group_id = p_bus_grp
1085 and pbt.assignment_remuneration_flag = 'Y'
1086 and pbt.balance_type_id = pdb.balance_type_id
1087 and pdb.balance_dimension_id = pbd.balance_dimension_id
1088 and pbd.payments_flag = 'Y';
1089 --
1090 bal_found := TRUE;
1091 exception
1092 when NO_DATA_FOUND then
1093 bal_found := FALSE;
1094 end;
1095 --
1096 if (bal_found = FALSE) then
1097 select distinct legislation_code
1098 into bus_leg
1099 from per_business_groups_perf
1100 where business_group_id = p_bus_grp;
1101 --
1102 select distinct pdb.defined_balance_id,
1103 pbt.currency_code
1104 into def_bal,
1105 balance_currency
1106 from pay_balance_types pbt,
1107 pay_defined_balances pdb,
1108 pay_balance_dimensions pbd
1109 where pbt.business_group_id is null
1110 and pbt.legislation_code = bus_leg
1111 and pbt.assignment_remuneration_flag = 'Y'
1112 and pbt.balance_type_id = pdb.balance_type_id
1113 and pdb.balance_dimension_id = pbd.balance_dimension_id
1114 and pbd.payments_flag = 'Y';
1115 end if;
1116 --
1117 p_def_bal_id := def_bal;
1118 --
1119 exception
1120 when NO_DATA_FOUND then
1121 hr_utility.set_message(801,'HR_XXXX_PAYM_NO_RENUMERATION');
1122 hr_utility.raise_error;
1123 end get_ren_balance;
1124 -------------------------- get_third_party_details --------------------------
1125 /*
1126 NAME
1127 get_third_party_details
1128 DESCRIPTION
1129 Gets the third party details that have to be paid for a given assignment
1130 action.
1131 NOTES
1132 This is called from a C procedure thus details are returned to the C
1133 process, however some are also held in global variables to be used
1134 later in the run.
1135 */
1136 --
1137 procedure get_third_party_details (p_assignment_action in number,
1138 p_effective_date in varchar2,
1139 p_element_id out nocopy number,
1140 p_run_result out nocopy number,
1141 p_assignment_id out nocopy number
1142 ) is
1143 --
1144 element_id number;
1145 run_result number;
1146 assignment_id number;
1147 --
1148 begin
1149 if (not g_third_party%ISOPEN) then
1150 open g_third_party(p_assignment_action);
1151 end if;
1152 --
1153 fetch g_third_party into balance_currency, element_id, run_result,assignment_id;
1154 --
1155 if (g_third_party%NOTFOUND) then
1156 close g_third_party;
1157 raise NO_DATA_FOUND;
1158 end if;
1159 --
1160 p_element_id := element_id;
1161 p_run_result := run_result;
1162 p_assignment_id := assignment_id;
1163 --
1164 return;
1165 --
1166 exception
1167 when NO_DATA_FOUND then
1168 if (g_third_party%ISOPEN) then
1169 close g_third_party;
1170 end if;
1171 raise;
1172 when others then
1173 if (g_third_party%ISOPEN) then
1174 close g_third_party;
1175 end if;
1176 raise;
1177 end get_third_party_details;
1178 --
1179 -------------------------- get_balance_value ---------------------------------
1180 /*
1181 NAME
1182 get_balance_value
1183 DESCRIPTION
1184 Gets the balance value to be paid for a specific assignment action
1185 assignment action.
1186 NOTES
1187 The value is returned to the C process.
1188 */
1189 procedure get_balance_value(p_def_balance in number,
1190 p_assignment_actions in number,
1191 p_balance_value in out nocopy number,
1192 p_org_id in number default null) is
1193 ass_act_id number;
1194 begin
1195 --
1196 if (p_org_id is not null) then
1197 pay_balance_pkg.set_context('ORGANIZATION_ID', p_org_id);
1198 end if;
1199 --
1200 p_balance_value := pay_balance_pkg.get_value(p_def_balance,
1201 p_assignment_actions);
1202 return;
1203 end get_balance_value;
1204 --
1205 -------------------------- adjust_payments ------------------------------
1206 /*
1207 NAME
1208 adjust_payments
1209 DESCRIPTION
1210 This procedure is called if prepayments detects at the end of processing
1211 an amount that still hasn't been paid. This procedure searches the
1212 payments for a method that can pay it.
1213 */
1214 procedure adjust_payments(pay_left in out nocopy number,
1215 p_src_action_id in number)
1216 --
1217 is
1218 pp_ptr number;
1219 --
1220 begin
1221 --
1222 hr_utility.trace('Enter adjust_payments');
1223 pp_ptr := g_pre_payments.count;
1224 while (pp_ptr > 0) loop
1225 --
1226 hr_utility.trace('Compare '||g_pre_payments(pp_ptr).category);
1227 hr_utility.trace('Compare '||
1228 nvl(g_pre_payments(pp_ptr).payment_currency, 'NULL')||
1229 ' with '||
1230 nvl(g_pre_payments(pp_ptr).base_currency, 'NULL'));
1231 hr_utility.trace('Compare '||
1232 nvl(g_pre_payments(pp_ptr).source_action_id, -999)||
1233 ' with '||
1234 nvl(p_src_action_id, -999));
1235 --
1236 if (g_pre_payments(pp_ptr).category <> 'CA'
1237 and g_pre_payments(pp_ptr).payment_currency =
1238 g_pre_payments(pp_ptr).base_currency
1239 and nvl(g_pre_payments(pp_ptr).source_action_id, -999)
1240 = nvl(p_src_action_id, -999))
1241 then
1242 --
1243 g_pre_payments(pp_ptr).base_currency_value :=
1244 g_pre_payments(pp_ptr).base_currency_value + pay_left ;
1245 g_pre_payments(pp_ptr).value :=
1246 g_pre_payments(pp_ptr).base_currency_value;
1247 pay_left := 0;
1248 --
1249 end if;
1250 --
1251 pp_ptr := pp_ptr - 1;
1252 end loop;
1253 hr_utility.trace('Exit adjust_payments');
1254 --
1255 end adjust_payments;
1256 --
1257 -------------------------- pay_per_payment_methods ------------------------------
1258 /*
1259 NAME
1260 pay_per_payment_methods
1261 DESCRIPTION
1262 This distributes the payable amount over the personal payment methods
1263 as directed. If it is to be paid by magnetic tape do the required
1264 validation check. If there is not a payment method specified use the
1265 default method.
1266 */
1267 procedure pay_per_payment_methods(p_assignment_action in number,
1268 p_assignment in number,
1269 p_effective_date in varchar2,
1270 p_def_balance in number,
1271 p_total_pay in number,
1272 p_pay_left in out nocopy number,
1273 p_src_act_id in number default null,
1274 p_prepayment_action_id in number default null)
1275 is
1276 --
1277 base_payment number;
1278 payment boolean;
1279 valid_date date;
1280 valid_method boolean;
1281 last_method g_pp_methods%rowtype;
1282 exchange_rate number;
1283 leg_code varchar2(150);
1284 prenote_default varchar2(1);
1285 found boolean;
1286 l_org_method_id pay_org_payment_methods.org_payment_method_id%TYPE;
1287 --
1288 begin
1289 --
1290 hr_utility.trace('Enter pay_per_payment_methods');
1291 payment := FALSE;
1292
1293 -- check for legilsation rule
1294 select org.legislation_code
1295 into leg_code
1296 from pay_assignment_Actions act,
1297 pay_payroll_actions pact,
1298 per_business_groups_perf org
1299 where act.assignment_action_id=p_assignment_action
1300 and pact.payroll_action_id=act.payroll_action_id
1301 and org.business_group_id=pact.business_group_id;
1302
1303 pay_core_utils.get_legislation_rule(
1304 'PRENOTE_DEFAULT',
1305 leg_code,
1306 prenote_default,found);
1307 --
1308 for payments in g_pp_methods(p_assignment, p_effective_date,
1309 p_def_balance) loop
1310 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1311 payments.valid_days);
1312 --
1313 -- put code here to derive override org payment method
1314 -- when external account id is null
1315 --
1316 --
1317 if (payment = TRUE) then
1318 if valid_method = TRUE then
1319 create_payment(base_payment,
1320 balance_currency,
1321 last_method.payment_currency,
1322 last_method.personal_method,
1323 last_method.org_method,
1324 p_assignment_action,
1325 last_method.category,
1326 last_method.cash_rule,
1327 p_src_act_id,
1328 p_prepayment_action_id,
1329 p_pay_left);
1330 else
1331 create_payment(base_payment,
1332 balance_currency,
1333 default_method.currency,
1334 null,
1335 default_method.payment_method_id,
1336 p_assignment_action,
1337 default_method.category,
1338 default_method.cash_rule,
1339 p_src_act_id,
1340 p_prepayment_action_id,
1341 p_pay_left);
1342 end if;
1343 payment := FALSE;
1344 end if;
1345 --
1346 -- Pay this method. Find out the amount of this payment and the
1347 -- pay left.
1348 --
1349 if p_pay_left > 0
1350 or (negative_pay = 'Y' and p_pay_left <> 0 and
1351 payments.category IN ('MT', 'CA')) then
1352 --
1353 -- Check for magnetic tape payments. Check they have been
1354 -- prenoted.
1355 --
1356 if payments.category = 'MT' and
1357 payments.validation_required = 'Y' then
1358 --
1359 valid_method := validate_magnetic(
1360 payments.personal_method,
1361 valid_date,
1362 payments.prenote_date,
1363 payments.org_method,
1364 p_assignment_action,
1365 payments.validation_value);
1366
1367 --
1368 else
1369 --
1370 valid_method := true;
1371 --
1372 end if;
1373 --
1374 if valid_method = true then
1375 --
1376 pay_method(p_pay_left,
1377 p_total_pay,
1378 base_payment,
1379 payments.percentage,
1380 payments.amount,
1381 payments.payment_currency);
1382 --
1383 -- Now if set up the correct exchange rate if required
1384 --
1385 --
1386 -- Now if there is anything to pay, create a payment record
1387 -- in the correct currency. Also if required perform cash
1388 -- analysis.
1389 --
1390 if base_payment > 0
1391 or (negative_pay = 'Y' and
1392 payments.category IN ('MT', 'CA')) then
1393 --
1394 payment := TRUE;
1395 end if;
1396 last_method := payments;
1397 else
1398 if (found=true and upper(prenote_default)='Y')
1399 then
1400 -- pay in default method
1401 pay_method(p_pay_left,
1402 p_total_pay,
1403 base_payment,
1404 payments.percentage,
1405 payments.amount,
1406 payments.payment_currency);
1407 if base_payment > 0
1408 or (negative_pay = 'Y' and
1409 default_method.category IN ('MT', 'CA')) then
1410 payment := TRUE;
1411 end if;
1412 end if;
1413 end if;
1414 end if;
1415 --
1416 end loop;
1417 if (payment = TRUE) then
1418 base_payment := base_payment + p_pay_left;
1419 p_pay_left := 0;
1420 if ( valid_method=TRUE) then
1421 create_payment(base_payment,
1422 balance_currency,
1423 last_method.payment_currency,
1424 -- exchange_rate,
1425 last_method.personal_method,
1426 last_method.org_method,
1427 p_assignment_action,
1428 last_method.category,
1429 last_method.cash_rule,
1430 p_src_act_id,
1431 p_prepayment_action_id,
1432 p_pay_left);
1433 else
1434 create_payment(base_payment,
1435 balance_currency,
1436 default_method.currency,
1437 null,
1438 default_method.payment_method_id,
1439 p_assignment_action,
1440 default_method.category,
1441 default_method.cash_rule,
1442 p_src_act_id,
1443 p_prepayment_action_id,
1444 p_pay_left);
1445 end if;
1446 -- Check to see if there is any money left.
1447 -- If there is then the last payment must have been cash
1448 -- that could not be paid due to the denominations
1449 if (p_pay_left > 0) then
1450 adjust_payments(p_pay_left, p_src_act_id);
1451 --
1452 if p_pay_left > 0 then
1453 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1454 hr_utility.raise_error;
1455 end if;
1456 end if;
1457 --
1458 end if ;
1459 --
1460 hr_utility.trace('Exit pay_per_payment_methods');
1461 --
1462 end pay_per_payment_methods;
1463 --
1464 -------------------------- pay_run_type_methods ----------------------------- -
1465 /*
1466 NAME
1467 pay_run_type_methods
1468 DESCRIPTION
1469 This distributes the payable amount over the run type methods
1470 as directed. If it is to be paid by magnetic tape do the required
1471 validation check. If there is not a payment method specified use the
1472 default method.
1473 */
1474 procedure pay_run_type_methods(p_assignment_action in number,
1475 p_effective_date in varchar2,
1476 p_def_balance in number,
1477 p_pay_left in out nocopy number,
1478 p_master_aa_id in number) is
1479 cursor chdact is
1480 select paa_chd.assignment_action_id,
1481 paa_chd.run_type_id,
1482 prt.run_method,paa_chd.assignment_id
1483 from pay_assignment_actions paa_par,
1484 pay_action_interlocks pai,
1485 pay_assignment_actions paa_chd,
1486 pay_payroll_actions ppa_chd,
1487 pay_run_types_f prt
1488 where paa_par.assignment_action_id = p_assignment_action
1489 and paa_par.assignment_action_id = pai.locking_action_id
1490 and pai.locked_action_id = paa_chd.assignment_action_id
1491 and paa_chd.run_type_id is not null
1492 and paa_chd.run_type_id = prt.run_type_id
1493 and prt.run_method in ('N','S','P')
1494 and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
1495 and ppa_chd.effective_date between prt.effective_start_date
1496 and prt.effective_end_date
1497 order by paa_chd.action_sequence;
1498 --
1499 cursor rt_org_methods (p_run_type in number,
1500 p_effective_date in varchar2,
1501 p_def_balance in number,
1502 p_assignment_action in number) is
1503 select ppt.category category,
1504 null personal_method,
1505 null prenote_date,
1506 ppt.validation_days valid_days,
1507 rtom.percentage percentage,
1508 rtom.amount amount,
1509 opm.org_payment_method_id org_method,
1510 hr_pre_pay.set_cash_rule(ppt.category,
1511 opm.pmeth_information1) cash_rule,
1512 opm.currency_code payment_currency,
1513 ppt.pre_validation_required validation_required,
1514 ppt.validation_value validation_value,
1515 opm.external_account_id external_account_id
1516 from pay_payment_types ppt,
1517 pay_org_payment_methods_f opm,
1518 pay_run_type_org_methods_f rtom,
1519 pay_org_pay_method_usages_f opmu,
1520 pay_payroll_actions ppa,
1521 pay_assignment_actions paa
1522 where rtom.run_type_id = p_run_type
1523 and rtom.org_payment_method_id = opm.org_payment_method_id
1524 and opm.payment_type_id = ppt.payment_type_id
1525 and opm.defined_balance_id = p_def_balance
1526 and paa.assignment_action_id = p_assignment_action
1527 and paa.payroll_action_id = ppa.payroll_action_id
1528 and ppa.payroll_id = opmu.payroll_id
1529 and opmu.org_payment_method_id = opm.org_payment_method_id
1530 and fnd_date.canonical_to_date(p_effective_date) between
1531 opmu.effective_start_date and opmu.effective_end_date
1532 and fnd_date.canonical_to_date(p_effective_date) between
1533 rtom.effective_start_date and rtom.effective_end_date
1534 and fnd_date.canonical_to_date(p_effective_date) between
1535 opm.effective_start_date and opm.effective_end_date
1536 order by rtom.priority;
1537
1538
1539 cursor rt_personal_pay_methods ( p_run_type in number,
1540 p_effective_date in varchar2,
1541 p_def_balance in number,
1542 p_assignment_action in number) is
1543 select ppt.category category,
1544 ppm.personal_payment_method_id personal_method,
1545 pea.prenote_date prenote_date,
1546 ppt.validation_days valid_days,
1547 ppm.percentage percentage,
1548 ppm.amount amount,
1549 opm.org_payment_method_id org_method,
1550 hr_pre_pay.set_cash_rule(ppt.category,
1551 opm.pmeth_information1) cash_rule,
1552 opm.currency_code payment_currency,
1553 ppt.pre_validation_required validation_required,
1554 ppt.validation_value validation_value,
1555 opm.external_account_id external_account_id
1556 from pay_external_accounts pea,
1557 pay_payment_types ppt,
1558 pay_org_payment_methods_f opm,
1559 pay_personal_payment_methods_f ppm,
1560 pay_assignment_actions act
1561 where act.assignment_action_id=p_assignment_action
1562 and ppm.assignment_id = act.assignment_id
1563 and ppm.run_type_id = p_run_type
1564 and ppm.org_payment_method_id = opm.org_payment_method_id
1565 and opm.payment_type_id = ppt.payment_type_id
1566 and opm.defined_balance_id = p_def_balance
1567 and ppm.external_account_id = pea.external_account_id (+)
1568 and fnd_date.canonical_to_date(p_effective_date) between
1569 ppm.effective_start_date and ppm.effective_end_date
1570 and fnd_date.canonical_to_date(p_effective_date) between
1571 opm.effective_start_date and opm.effective_end_date
1572 order by ppm.person_id,ppm.priority;
1573 --
1574 -- Coinage cursor. fetch all monetary units of a currency in order of value
1575
1576 --
1577 --
1578 /*
1579 pay_left number;
1580 payment boolean;
1581 ren_method_payment boolean;
1582 last_method g_pp_methods%rowtype;
1583 exchange_rate number;
1584 */
1585 valid_date date;
1586 base_payment number;
1587 valid_method boolean;
1588 total_pay number;
1589 pay_left number;
1590 master_action_id number;
1591 child_action_id number;
1592 assign_id number;
1593 leg_code varchar2(150);
1594 prenote_default varchar2(1);
1595 found boolean;
1596 l_org_method_id pay_org_payment_methods.org_payment_method_id%TYPE;
1597 got_payment_amount boolean;
1598 begin
1599 --
1600 hr_utility.trace('Enter pay_run_type_methods');
1601 -- check for legilsation rule
1602 select org.legislation_code
1603 into leg_code
1604 from pay_assignment_Actions act,
1605 pay_payroll_actions pact,
1606 per_business_groups_perf org
1607 where act.assignment_action_id=p_assignment_Action
1608 and pact.payroll_action_id=act.payroll_action_id
1609 and org.business_group_id=pact.business_group_id;
1610
1611 pay_core_utils.get_legislation_rule(
1612 'PRENOTE_DEFAULT',
1613 leg_code,
1614 prenote_default,found);
1615
1616 for chdrec in chdact loop
1617 got_payment_amount := FALSE;
1618 assign_id := chdrec.assignment_id;
1619
1620 child_action_id := p_assignment_action;
1621 master_action_id := p_master_aa_id;
1622
1623 for payments in rt_personal_pay_methods(chdrec.run_type_id, p_effective_date,
1624 p_def_balance, chdrec.assignment_action_id)
1625 loop
1626 --
1627 if (got_payment_amount = FALSE) then
1628 get_balance_value(p_def_balance,
1629 chdrec.assignment_action_id,
1630 total_pay);
1631 pay_left := total_pay;
1632 got_payment_amount := TRUE;
1633 end if;
1634 --
1635 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1636 payments.valid_days);
1637 --
1638 -- Pay this method. Find out the amount of this payment and the
1639 -- pay left.
1640 --
1641 if pay_left > 0
1642 or (negative_pay = 'Y' and pay_left <> 0 and
1643 payments.category IN ('MT', 'CA')) then
1644 --
1645 -- Check for magnetic tape payments. Check they have been
1646 -- prenoted.
1647 --
1648 if payments.category = 'MT' and
1649 payments.validation_required = 'Y' then
1650 --
1651 valid_method := validate_magnetic(
1652 payments.personal_method,
1653 valid_date,
1654 payments.prenote_date,
1655 payments.org_method,
1656 p_assignment_action,
1657 payments.validation_value);
1658
1659 --
1660 else
1661 --
1662 valid_method := true;
1663 --
1664 end if;
1665 --
1666 if valid_method = true then
1667 --
1668 pay_method(pay_left,
1669 total_pay,
1670 base_payment,
1671 payments.percentage,
1672 payments.amount,
1673 payments.payment_currency);
1674 --
1675 -- Now if set up the correct exchange rate if required
1676 --
1677 --
1678 -- Now if there is anything to pay, create a payment record
1679 -- in the correct currency. Also if required perform cash
1680 -- analysis.
1681 --
1682 if base_payment > 0
1683 or (negative_pay = 'Y' and
1684 payments.category IN ('MT', 'CA')) then
1685 --
1686 create_payment(base_payment,
1687 balance_currency,
1688 payments.payment_currency,
1689 -- exchange_rate,
1690 payments.personal_method,
1691 payments.org_method,
1692 child_action_id,
1693 payments.category,
1694 payments.cash_rule,
1695 chdrec.assignment_action_id,
1696 master_action_id,
1697 pay_left);
1698 -- Subtract the amount that was actually taken.
1699 p_pay_left := p_pay_left -
1700 g_pre_payments(g_pre_payments.count).base_currency_value;
1701
1702 end if;
1703 else
1704 if (found=true and upper(prenote_default)='Y')
1705 then
1706 -- pay in default method
1707 pay_method(pay_left,
1708 total_pay,
1709 base_payment,
1710 payments.percentage,
1711 payments.amount,
1712 payments.payment_currency);
1713 --
1714 -- Now if there is anything to pay, create a payment record
1715 -- in the correct currency. Also if required perform cash
1716 -- analysis.
1717 --
1718 if base_payment > 0
1719 or (negative_pay = 'Y' and
1720 default_method.category IN ('MT', 'CA')) then
1721 create_payment(base_payment,
1722 balance_currency,
1723 default_method.currency,
1724 null,
1725 default_method.payment_method_id,
1726 child_action_id,
1727 default_method.category,
1728 default_method.cash_rule,
1729 chdrec.assignment_action_id,
1730 master_action_id,
1731 pay_left);
1732 -- Subtract the amount that was actually taken.
1733 p_pay_left := p_pay_left -
1734 g_pre_payments(g_pre_payments.count).base_currency_value;
1735
1736 end if;
1737 end if;
1738 end if;
1739 end if;
1740 end loop;
1741
1742
1743
1744
1745 --
1746 for payments in rt_org_methods(chdrec.run_type_id, p_effective_date,
1747 p_def_balance, chdrec.assignment_action_id)
1748 loop
1749 --
1750 if (got_payment_amount = FALSE) then
1751 get_balance_value(p_def_balance,
1752 chdrec.assignment_action_id,
1753 total_pay);
1754 pay_left := total_pay;
1755 got_payment_amount := TRUE;
1756 end if;
1757 --
1758 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1759 payments.valid_days);
1760 --
1761 -- Pay this method. Find out the amount of this payment and the
1762 -- pay left.
1763 --
1764 if pay_left > 0
1765 or (negative_pay = 'Y' and pay_left <> 0 and
1766 payments.category IN ('MT', 'CA')) then
1767 --
1768 -- Check for magnetic tape payments. Check they have been
1769 -- prenoted.
1770 --
1771 if payments.category = 'MT' and
1772 payments.validation_required = 'Y' then
1773 --
1774 valid_method := validate_magnetic(
1775 payments.personal_method,
1776 valid_date,
1777 payments.prenote_date,
1778 payments.org_method,
1779 p_assignment_action,
1780 payments.validation_value);
1781
1782 --
1783 else
1784 --
1785 valid_method := true;
1786 --
1787 end if;
1788 --
1789 if valid_method = true then
1790 --
1791 pay_method(pay_left,
1792 total_pay,
1793 base_payment,
1794 payments.percentage,
1795 payments.amount,
1796 payments.payment_currency);
1797 --
1798 -- Now if set up the correct exchange rate if required
1799 --
1800 --
1801 -- Now if there is anything to pay, create a payment record
1802 -- in the correct currency. Also if required perform cash
1803 -- analysis.
1804 --
1805 if base_payment > 0
1806 or (negative_pay = 'Y' and
1807 payments.category IN ('MT', 'CA')) then
1808 --
1809 create_payment(base_payment,
1810 balance_currency,
1811 payments.payment_currency,
1812 -- exchange_rate,
1813 payments.personal_method,
1814 payments.org_method,
1815 child_action_id,
1816 payments.category,
1817 payments.cash_rule,
1818 chdrec.assignment_action_id,
1819 master_action_id,
1820 pay_left);
1821 -- Subtract the amount that was actually taken.
1822 p_pay_left := p_pay_left -
1823 g_pre_payments(g_pre_payments.count).base_currency_value;
1824 end if;
1825 else
1826 if (found=true and upper(prenote_default)='Y')
1827 then
1828 -- pay in default method
1829 pay_method(pay_left,
1830 total_pay,
1831 base_payment,
1832 payments.percentage,
1833 payments.amount,
1834 payments.payment_currency);
1835 --
1836 -- Now if there is anything to pay, create a payment record
1837 -- in the correct currency. Also if required perform cash
1838 -- analysis.
1839 --
1840 if base_payment > 0
1841 or (negative_pay = 'Y' and
1842 default_method.category IN ('MT', 'CA')) then
1843 create_payment(base_payment,
1844 balance_currency,
1845 default_method.currency,
1846 null,
1847 default_method.payment_method_id,
1848 child_action_id,
1849 default_method.category,
1850 default_method.cash_rule,
1851 chdrec.assignment_action_id,
1852 master_action_id,
1853 pay_left);
1854 -- Subtract the amount that was actually taken.
1855 p_pay_left := p_pay_left -
1856 g_pre_payments(g_pre_payments.count).base_currency_value;
1857
1858 end if;
1859 end if;
1860 end if;
1861 end if;
1862 end loop;
1863 --
1864 -- OK, we've checked the run type payment methods now
1865 -- if this is a separate payment run, and if there is
1866 -- money left to be paid then use either the personal
1867 -- payment methods or the default payment method.
1868 if chdrec.run_method = 'S' then
1869 --
1870 if (got_payment_amount = FALSE) then
1871 get_balance_value(p_def_balance,
1872 chdrec.assignment_action_id,
1873 total_pay);
1874 pay_left := total_pay;
1875 got_payment_amount := TRUE;
1876 end if;
1877 --
1878 if (pay_left > 0 or
1879 (negative_pay = 'Y' and default_method.category in ('CA', 'MT'))) then
1880 --
1881 declare
1882 --
1883 payment_amount number;
1884 begin
1885 --
1886 payment_amount := pay_left;
1887 -- Process the personal payment methods that are left.
1888 pay_per_payment_methods(child_action_id,
1889 assign_id,
1890 p_effective_date,
1891 p_def_balance,
1892 total_pay,
1893 pay_left,
1894 chdrec.assignment_action_id,
1895 master_action_id);
1896 --
1897 p_pay_left := p_pay_left - (payment_amount - pay_left);
1898 --
1899 if pay_left > 0
1900 or (negative_pay = 'Y' and default_method.category = 'CA') then
1901 --
1902 base_payment := pay_left;
1903 pay_left := 0;
1904 --
1905 create_payment(base_payment,
1906 balance_currency,
1907 default_method.currency,
1908 -- default_method.exchange_rate,
1909 null,
1910 default_method.payment_method_id,
1911 child_action_id,
1912 default_method.category,
1913 default_method.cash_rule,
1914 chdrec.assignment_action_id,
1915 master_action_id,
1916 pay_left);
1917 --
1918 if pay_left > 0 then
1919 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1920 hr_utility.raise_error;
1921 end if;
1922 --
1923 p_pay_left := p_pay_left - base_payment;
1924 end if;
1925 end;
1926 end if;
1927 end if;
1928 --
1929 end loop;
1930 hr_utility.trace('Exit pay_run_type_methods');
1931 end pay_run_type_methods;
1932 --
1933 procedure process_per_payment(p_assignment_action in number,
1934 p_assignment in number,
1935 p_effective_date in varchar2,
1936 p_def_balance in number,
1937 p_pay_left in out nocopy number)
1938 is
1939 base_payment number;
1940 total_pay number;
1941 begin
1942 total_pay := p_pay_left;
1943 -- Process the personal payment methods that are left.
1944 pay_per_payment_methods(p_assignment_action,
1945 p_assignment,
1946 p_effective_date,
1947 p_def_balance,
1948 total_pay,
1949 p_pay_left);
1950 --
1951 if p_pay_left > 0
1952 or (negative_pay = 'Y' and default_method.category = 'CA') then
1953 --
1954 base_payment := p_pay_left;
1955 p_pay_left := 0;
1956 --
1957 create_payment(base_payment,
1958 balance_currency,
1959 default_method.currency,
1960 -- default_method.exchange_rate,
1961 null,
1962 default_method.payment_method_id,
1963 p_assignment_action,
1964 default_method.category,
1965 default_method.cash_rule,
1966 null,
1967 null,
1968 p_pay_left);
1969 --
1970 if p_pay_left > 0 then
1971 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1972 hr_utility.raise_error;
1973 end if;
1974 --
1975 end if;
1976 --
1977 end process_per_payment;
1978 --
1979 -------------------------- pay_personal_methods ------------------------------
1980 /*
1981 NAME
1982 pay_personal_methods
1983 DESCRIPTION
1984 This distributes the payable amount over the personal payment methods
1985 as directed. If it is to be paid by magnetic tape do the required
1986 validation check. If there is not a payment method specified use the
1987 default method. If the amount is for a third party then get the payment
1988 method required and pay the value.
1989 */
1990 procedure pay_personal_methods(p_assignment_action in number,
1991 p_assignment in number,
1992 p_effective_date in varchar2,
1993 p_def_balance in number,
1994 p_balance_value in varchar2,
1995 p_master_aa_id in number) is
1996
1997 base_payment number;
1998 total_pay number;
1999 pay_left number;
2000 begin
2001 --
2002 hr_utility.trace('Enter pay_personal_methods');
2003 g_pre_payments.delete;
2004 total_pay := fnd_number.canonical_to_number(p_balance_value);
2005 pay_left := total_pay;
2006 --
2007 -- Process any payment methods associated with the
2008 -- Run Types
2009 pay_run_type_methods(p_assignment_action,
2010 p_effective_date,
2011 p_def_balance,
2012 pay_left,
2013 p_master_aa_id);
2014 --
2015 process_per_payment(p_assignment_action,
2016 p_assignment,
2017 p_effective_date,
2018 p_def_balance,
2019 pay_left);
2020 --
2021 flush_payments;
2022 --
2023 hr_utility.trace('Exit pay_personal_methods');
2024 end pay_personal_methods;
2025 --
2026 -------------------------- pay_run_type_override ----------------------------- -
2027 /*
2028 NAME
2029 pay_run_type_override
2030 DESCRIPTION
2031 This distributes the payable amount using the override method
2032 over the pay separately run types.
2033 */
2034 procedure pay_run_type_override(p_assignment_action in number,
2035 p_def_balance in number,
2036 p_pay_left in out nocopy number,
2037 p_master_aa_id in number) is
2038
2039 cursor chdact is
2040 select paa_chd.assignment_action_id, paa_chd.run_type_id, prt.run_method,paa_chd.assignment_id
2041 from pay_assignment_actions paa_par,
2042 pay_action_interlocks pai,
2043 pay_assignment_actions paa_chd,
2044 pay_payroll_actions ppa_chd,
2045 pay_run_types_f prt
2046 where paa_par.assignment_action_id = p_assignment_action
2047 and paa_par.assignment_action_id = pai.locking_action_id
2048 and pai.locked_action_id = paa_chd.assignment_action_id
2049 and paa_chd.run_type_id is not null
2050 and paa_chd.run_type_id = prt.run_type_id
2051 and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
2052 and ppa_chd.effective_date between prt.effective_start_date
2053 and prt.effective_end_date
2054 order by paa_chd.action_sequence;
2055 --
2056 total_pay number;
2057 pay_left number;
2058 master_action_id number;
2059 child_action_id number;
2060 assign_id number;
2061 --
2062 begin
2063 --
2064 hr_utility.trace('Enter pay_run_type_override');
2065 --
2066 for chdrec in chdact loop
2067 --
2068 if chdrec.run_method = 'S' then
2069 --
2070 get_balance_value(p_def_balance,
2071 chdrec.assignment_action_id,
2072 total_pay);
2073 pay_left := 0;
2074 --
2075 hr_utility.trace('Found Child Type '||chdrec.run_method);
2076 --
2077 -- OK, we've checked the run type payment methods now
2078 -- if this is a separate payment run, and if there is
2079 -- money left to be paid then use either the personal
2080 -- payment methods or the default payment method.
2081 --
2082 if
2083 (total_pay > 0
2084 or (negative_pay = 'Y' and total_pay <> 0 and
2085 override.category = 'CA')) then
2086 --
2087 hr_utility.trace('Paying Run Override of '||total_pay);
2088 --
2089 child_action_id := p_assignment_action;
2090 master_action_id := p_master_aa_id;
2091 --
2092 create_payment(total_pay,
2093 balance_currency,
2094 override.currency,
2095 -- override.exchange_rate,
2096 null,
2097 override.payment_method_id,
2098 child_action_id,
2099 override.category,
2100 override.cash_rule,
2101 chdrec.assignment_action_id,
2102 master_action_id,
2103 pay_left);
2104 --
2105 if pay_left > 0 then
2106 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2107 hr_utility.raise_error;
2108 end if;
2109 --
2110 p_pay_left := p_pay_left - total_pay;
2111 --
2112 end if;
2113 end if;
2114 --
2115 end loop;
2116 hr_utility.trace('Exit pay_run_type_override');
2117 --
2118 end pay_run_type_override;
2119 --
2120 procedure process_run_types(p_override_method in number,
2121 p_assignment_action in number,
2122 p_def_balance in number,
2123 p_effective_date in varchar2,
2124 p_master_aa_id in number,
2125 p_pay_left in out nocopy number)
2126 is
2127 begin
2128 --
2129 g_pre_payments.delete;
2130 --
2131 if (p_override_method is not null) then
2132 pay_run_type_override(p_assignment_action,
2133 p_def_balance,
2134 p_pay_left,
2135 p_master_aa_id);
2136 else
2137 pay_run_type_methods(p_assignment_action,
2138 p_effective_date,
2139 p_def_balance,
2140 p_pay_left,
2141 p_master_aa_id);
2142 end if;
2143 --
2144 flush_payments;
2145 end process_run_types;
2146 --
2147 procedure process_ovr_payment(p_assignment_action in number,
2148 p_pay_left in out nocopy number)
2149 is
2150 base_value number;
2151 begin
2152 base_value := p_pay_left;
2153 p_pay_left := 0;
2154 --
2155 -- If the base_value is non-zero then insert a p ayment record in the
2156 -- payment currency. Also if the category is cash then perform cash
2157 -- analysis.
2158 --
2159 --
2160 if base_value > 0
2161 or (negative_pay = 'Y' and base_value <> 0 and
2162 override.category = 'CA') then
2163 create_payment(base_value,
2164 balance_currency,
2165 override.currency,
2166 -- override.exchange_rate,
2167 null,
2168 override.payment_method_id,
2169 p_assignment_action,
2170 override.category,
2171 override.cash_rule,
2172 null,
2173 null,
2174 p_pay_left);
2175 --
2176 if p_pay_left > 0 then
2177 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2178 hr_utility.raise_error;
2179 end if;
2180 end if;
2181 end process_ovr_payment;
2182 --
2183 procedure process_normal_payments(p_override_method in number,
2184 p_assignment_action in number,
2185 p_assignment in number,
2186 p_def_balance in number,
2187 p_effective_date in varchar2,
2188 p_pay_left in out nocopy number)
2189 is
2190 begin
2191 --
2192 hr_utility.trace('Enter process_normal_payments');
2193 --
2194 g_pre_payments.delete;
2195 if (p_override_method is not null) then
2196 process_ovr_payment(p_assignment_action,
2197 p_pay_left);
2198 else
2199 process_per_payment(p_assignment_action,
2200 p_assignment,
2201 p_effective_date,
2202 p_def_balance,
2203 p_pay_left);
2204 end if;
2205 --
2206 flush_payments;
2207 --
2208 hr_utility.trace('Exit process_normal_payments');
2209 end process_normal_payments;
2210 --
2211 -------------------------- pay_override_method -------------------------------
2212 /*
2213 NAME
2214 pay_override_method
2215 DESCRIPTION
2216 This uses the overiding payment method to pay the payment amount unless
2217 it is a third party payment, in which case it will pay by the personal
2218 payment method specified for the payment.
2219 */
2220 procedure pay_override_method(p_assignment_action in number,
2221 p_balance_value in varchar2,
2222 p_def_balance in number,
2223 p_master_aa_id in number) is
2224
2225 base_value number;
2226 pay_left number;
2227
2228 begin
2229 --
2230 g_pre_payments.delete;
2231 --
2232 --
2233 -- First pay the pay separate runs to the override method
2234 --
2235 pay_left := fnd_number.canonical_to_number(p_balance_value);
2236 pay_run_type_override(p_assignment_action,
2237 p_def_balance,
2238 pay_left,
2239 p_master_aa_id);
2240 --
2241 -- The remaining amount should be paid by the override payment methods.
2242 --
2243 process_ovr_payment(p_assignment_action,
2244 pay_left);
2245 --
2246 flush_payments;
2247 --
2248 end pay_override_method;
2249 --
2250 -------------------------- get_run_result_value ------------------------------
2251 /*
2252 NAME
2253 get_run_result_value
2254 DESCRIPTION
2255 This retrieves the value of a third party payment and the element entry
2256 id that it is based on.
2257 */
2258 procedure get_run_result_value(p_run_result in number,
2259 p_effective_date in varchar2,
2260 p_run_result_value out nocopy varchar2,
2261 p_entry out nocopy number) is
2262 res_value number;
2263 entry number;
2264 status varchar2(2);
2265 adj_value number;
2266 assign_id number;
2267 assign_action_id number;
2268 pay_act_id number;
2269 run_date_earned date;
2270 begin
2271 --
2272 -- Bug 1849996. This code now assumes that a no data found
2273 -- means that a zero payment is to be made. Legislation
2274 -- teams have been warned that this may have to change
2275 -- in future. They should explicitly set the Pay Value
2276 -- to zero for a non pament.
2277 --
2278 begin
2279 select prr.source_id,
2280 fnd_number.canonical_to_number(rrv.result_value),
2281 prr.status,
2282 paa.assignment_id,
2283 prr.assignment_action_id,
2284 paa.payroll_action_id,
2285 ppa.date_earned
2286 into entry,
2287 res_value,
2288 status,
2289 assign_id,
2290 assign_action_id,
2291 pay_act_id,
2292 run_date_earned
2293 from
2294 pay_input_values_f piv,
2295 pay_element_types_f pet,
2296 pay_run_result_values rrv,
2297 pay_run_results prr2,
2298 pay_run_results prr,
2299 pay_assignment_actions paa,
2300 pay_payroll_actions ppa
2301 where prr.run_result_id = p_run_result
2302 and paa.assignment_action_id= prr.assignment_action_id
2303 and ppa.payroll_action_id = paa.payroll_action_id
2304 and pet.element_type_id = prr2.element_type_id
2305 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2306 and prr.source_id = prr2.source_id
2307 and prr.assignment_action_id = prr2.assignment_action_id
2308 and prr2.source_type in ('E','I')
2309 and prr2.entry_type not in ('R','A')
2310 and prr2.run_result_id = rrv.run_result_id
2311 and rrv.input_value_id = piv.input_value_id
2312 and rrv.result_value is not null
2313 and piv.name = 'Pay Value'
2314 and ppa.date_earned between piv.effective_start_date
2315 and piv.effective_end_date
2316 and ppa.date_earned between pet.effective_start_date
2317 and pet.effective_end_date;
2318 exception
2319 when no_data_found then
2320 entry:= null;
2321 res_value := 0;
2322 status := null;
2323 assign_id := null;
2324 assign_action_id := null;
2325 pay_act_id := null;
2326 end;
2327 --
2328 if status in ('PA', 'R')
2329 then
2330 --
2331 /* Does the RR source Id point to the target or the adjustment */
2332 if (g_adjust_ee_source = 'T') then
2333 select fnd_number.canonical_to_number(rrv.result_value)
2334 into adj_value
2335 from
2336 pay_input_values_f piv,
2337 pay_element_types_f pet,
2338 pay_run_result_values rrv,
2339 pay_run_results prr2,
2340 pay_run_results prr
2341 where prr.run_result_id = p_run_result
2342 and prr.assignment_action_id = assign_action_id
2343 and pet.element_type_id = prr2.element_type_id
2344 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2345 and prr.source_id = prr2.source_id
2346 and prr.assignment_action_id = prr2.assignment_action_id
2347 and prr2.source_type in ('E','I')
2348 and prr2.entry_type in ('R','A')
2349 and prr.entry_type not in ('R','A')
2350 and prr2.run_result_id = rrv.run_result_id
2351 and rrv.input_value_id = piv.input_value_id
2352 and rrv.result_value is not null
2353 and piv.name = 'Pay Value'
2354 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2355 piv.effective_start_date and piv.effective_end_date
2356 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2357 pet.effective_start_date and pet.effective_end_date;
2358 else
2359 select fnd_number.canonical_to_number(rrv.result_value)
2360 into adj_value
2361 from
2362 pay_element_entries_f pee,
2363 pay_input_values_f piv,
2364 pay_element_types_f pet,
2365 pay_run_result_values rrv,
2366 pay_run_results prr2,
2367 pay_run_results prr
2368 where pee.target_entry_id=entry
2369 and pee.assignment_id = assign_id
2370 and prr.source_id=pee.element_entry_id
2371 and prr.assignment_action_id = assign_action_id
2372 and pet.element_type_id = prr2.element_type_id
2373 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2374 and prr.source_id = prr2.source_id
2375 and prr.assignment_action_id = prr2.assignment_action_id
2376 and prr2.source_type in ('E','I')
2377 and prr2.run_result_id = rrv.run_result_id
2378 and rrv.input_value_id = piv.input_value_id
2379 and rrv.result_value is not null
2380 and piv.name = 'Pay Value'
2381 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2382 piv.effective_start_date and piv.effective_end_date
2383 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2384 pet.effective_start_date and pet.effective_end_date
2385 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2386 pee.effective_start_date and pee.effective_end_date;
2387 end if;
2388 end if;
2389 --
2390 if (status = 'R')
2391 then
2392 p_run_result_value := fnd_number.number_to_canonical(adj_value);
2393 elsif (status = 'PA')
2394 then
2395 p_run_result_value := fnd_number.number_to_canonical(adj_value)+fnd_number.number_to_canonical(res_value);
2396 else
2397 p_run_result_value := fnd_number.number_to_canonical(res_value);
2398 end if;
2399 p_entry := entry;
2400 end get_run_result_value;
2401 --
2402 -------------------------- pay_third_party -----------------------------------
2403 /*
2404 NAME
2405 pay_third_party
2406 DESCRIPTION
2407 This retrieves a single payment method that is to be used for this third
2408 party payment and then pays the this method the full payable amount.
2409 */
2410 procedure pay_third_party(p_run_results in number,
2411 p_assignment_action in number,
2412 p_effective_date in varchar2,
2413 p_bal_value in varchar2,
2414 p_master_aa_id in number
2415 ) is
2416
2417 payment_details g_pp_methods%rowtype;
2418 valid_method boolean;
2419 exchange_rate number;
2420 pay_left number;
2421 valid_date date;
2422 child_action_id number;
2423 master_action_id number;
2424 begin
2425 --
2426 g_pre_payments.delete;
2427 pay_left := 0;
2428 --
2429 select ppt.category category,
2430 ppm.personal_payment_method_id personal_method,
2431 pea.prenote_date prenote_date,
2432 ppt.validation_days valid_days,
2433 ppm.percentage percentage,
2434 ppm.amount amount,
2435 opm.org_payment_method_id org_method,
2436 hr_pre_pay.set_cash_rule(ppt.category,
2437 opm.pmeth_information1) cash_rule,
2438 opm.currency_code payment_currency,
2439 ppt.pre_validation_required validation_required,
2440 ppt.validation_value validation_value,
2441 ppm.external_account_id external_account_id
2442 into payment_details
2443 from pay_run_results prr,
2444 pay_element_entries_f pee,
2445 pay_personal_payment_methods_f ppm,
2446 pay_external_accounts pea,
2447 pay_org_payment_methods_f opm,
2448 pay_payment_types ppt,
2449 pay_assignment_actions paa,
2450 pay_payroll_actions ppa
2451 where prr.run_result_id = p_run_results
2452 and prr.source_id = pee.element_entry_id
2453 and paa.assignment_action_id = prr.assignment_action_id
2454 and paa.payroll_action_id = ppa.payroll_action_id
2455 and pee.personal_payment_method_id = ppm.personal_payment_method_id
2456 and ppm.org_payment_method_id = opm.org_payment_method_id
2457 and opm.payment_type_id = ppt.payment_type_id
2458 and ppm.external_account_id = pea.external_account_id (+)
2459 -- and opm.pmeth_information1 = hlu.lookup_code (+)
2460 -- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
2461 -- and NVL(hlu.application_id, 800) = 800
2462 and fnd_date.canonical_to_date(p_effective_date) between
2463 ppm.effective_start_date and ppm.effective_end_date
2464 and fnd_date.canonical_to_date(p_effective_date) between
2465 opm.effective_start_date and opm.effective_end_date
2466 and ppa.date_earned between
2467 pee.effective_start_date and pee.effective_end_date
2468 order by ppm.priority;
2469 --
2470 if fnd_number.canonical_to_number(p_bal_value) <> 0 then
2471 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
2472 payment_details.valid_days);
2473 --
2474 -- Check magnetic tape payments for prenote.
2475 --
2476 if payment_details.category = 'MT' and
2477 payment_details.validation_required = 'Y' then
2478 --
2479 valid_method := validate_magnetic(payment_details.personal_method,
2480 valid_date,
2481 payment_details.prenote_date,
2482 payment_details.org_method,
2483 p_assignment_action,
2484 payment_details.validation_value);
2485 --
2486 else
2487 --
2488 valid_method := true;
2489 --
2490 end if;
2491 --
2492 if valid_method = true then
2493 --
2494 --
2495 if fnd_number.canonical_to_number(p_bal_value) > 0
2496 or (negative_pay = 'Y' and
2497 payment_details.category IN ('MT', 'CA')) then
2498 --
2499 declare
2500 rt_method pay_run_types_f.run_method%type;
2501 sep_chq_aa_id pay_assignment_actions.assignment_action_id%type;
2502 begin
2503 --
2504 -- Get the run method
2505 select nvl(prt.run_method, 'N'), paa.assignment_action_id
2506 into rt_method,
2507 sep_chq_aa_id
2508 from pay_run_types_f prt,
2509 pay_assignment_actions paa,
2510 pay_run_results prr,
2511 pay_payroll_actions ppa
2512 where prr.run_result_id = p_run_results
2513 and prr.assignment_action_id = paa.assignment_action_id
2514 and paa.payroll_action_id = ppa.payroll_action_id
2515 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
2516 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
2517 and nvl(prt.effective_end_date, ppa.effective_date);
2518 --
2519 if (rt_method <> 'S' ) then
2520 sep_chq_aa_id := null;
2521 end if;
2522 --
2523 child_action_id := p_assignment_action;
2524 master_action_id := p_master_aa_id;
2525 --
2526 create_payment(fnd_number.canonical_to_number(p_bal_value),
2527 balance_currency,
2528 payment_details.payment_currency,
2529 -- exchange_rate,
2530 payment_details.personal_method,
2531 payment_details.org_method,
2532 child_action_id,
2533 payment_details.category,
2534 payment_details.cash_rule,
2535 sep_chq_aa_id,
2536 master_action_id,
2537 pay_left);
2538 --
2539 if pay_left > 0 then
2540 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2541 hr_utility.raise_error;
2542 end if;
2543 --
2544 end;
2545 end if;
2546 else
2547 -- invalid pay method.
2548 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2549 hr_utility.raise_error;
2550 --
2551 end if;
2552 --
2553 end if;
2554 --
2555 flush_payments;
2556 --
2557 exception
2558 when NO_DATA_FOUND then
2559 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2560 hr_utility.raise_error;
2561 end pay_third_party;
2562 --
2563 ------------------- get_dynamic_org_method -------------------
2564 /*
2565 NAME
2566 get_dynamic_org_method
2567 DESCRIPTION
2568 Given a legislative procedure name and the assignment action,
2569 this procedure calculated the organisation method id when
2570 the external account is null.
2571 */
2572 --
2573 procedure get_dynamic_org_method (p_plsql_proc in varchar2,
2574 p_assignment_action in number,
2575 p_effective_date in date,
2576 p_org_meth in number,
2577 p_org_method_id out nocopy number )
2578 is
2579 l_def_rt_str varchar2(2000); -- used with dynamic pl/sql
2580 sql_cursor integer;
2581 l_rows integer;
2582 l_org_method_id number;
2583 l_paytype number;
2584 l_ext_acc number(16);
2585 begin
2586 l_def_rt_str := 'begin '||p_plsql_proc||' (';
2587 l_def_rt_str := l_def_rt_str || ':p_assignment_action, ';
2588 l_def_rt_str := l_def_rt_str || ':p_effective_date, ';
2589 l_def_rt_str := l_def_rt_str || ':p_org_meth, ';
2590 l_def_rt_str := l_def_rt_str || ':l_org_method_id); end; ';
2591 --
2592 sql_cursor := dbms_sql.open_cursor;
2593 --
2594 dbms_sql.parse(sql_cursor, l_def_rt_str, dbms_sql.v7);
2595 --
2596 dbms_sql.bind_variable(sql_cursor, 'p_assignment_action', p_assignment_action);
2597 --
2598 dbms_sql.bind_variable(sql_cursor, 'p_effective_date', p_effective_date);
2599 --
2600 dbms_sql.bind_variable(sql_cursor, 'p_org_meth', p_org_meth);
2601 --
2602 dbms_sql.bind_variable(sql_cursor, 'l_org_method_id', l_org_method_id);
2603 --
2604 l_rows := dbms_sql.execute (sql_cursor);
2605 --
2606 if (l_rows = 1) then
2607 dbms_sql.variable_value(sql_cursor, 'l_org_method_id',
2608 l_org_method_id);
2609 dbms_sql.close_cursor(sql_cursor);
2610 --
2611 -- Check that procedure returns a payment method with the same
2612 -- payment type as the original payment method.
2613 -- Also currency needs to be the same.
2614 --
2615 select count(*)
2616 into l_paytype
2617 from pay_org_payment_methods_f opm1
2618 ,pay_org_payment_methods_f opm2
2619 where opm1.org_payment_method_id = p_org_meth
2620 and opm2.org_payment_method_id = l_org_method_id
2621 and opm1.payment_type_id = opm2.payment_type_id
2622 and opm1.currency_code = opm2.currency_code
2623 and p_effective_date between opm1.effective_start_date
2624 and opm1.effective_end_date
2625 and p_effective_date between opm2.effective_start_date
2626 and opm2.effective_end_date;
2627 --
2628 -- Check that the new payment method does not have a null bank account
2629 --
2630 select count(*)
2631 into l_ext_acc
2632 from pay_org_payment_methods_f opm
2633 where opm.org_payment_method_id = l_org_method_id
2634 and opm.external_account_id is not null
2635 and p_effective_date between opm.effective_start_date
2636 and opm.effective_end_date;
2637
2638 if ((l_paytype = 0) or (l_ext_acc = 0)) then
2639 hr_utility.set_message(801,'HR_50412_INVALID_ORG_PAYMETH');
2640 hr_utility.raise_error;
2641 end if;
2642 --
2643 else
2644 dbms_sql.close_cursor(sql_cursor);
2645 hr_utility.set_message(801,'HR_50412_INVALID_ORG_PAYMETH');
2646 hr_utility.raise_error;
2647 end if;
2648 --
2649 p_org_method_id := l_org_method_id;
2650 --
2651 end get_dynamic_org_method;
2652 --
2653 --
2654 procedure close_cursors is
2655 begin
2656 if(g_third_party%ISOPEN) then
2657 close g_third_party;
2658 end if;
2659 if (coin_cursor%ISOPEN) then
2660 close coin_cursor;
2661 end if;
2662 end close_cursors;
2663 --
2664 procedure process_org_third_party(p_asg_act in number,
2665 p_eff_date in varchar2,
2666 p_master_aa_id in number)
2667 is
2668 --
2669 cursor get_opms(p_asg_action in number,
2670 p_org_id in number)
2671 is
2672 select
2673 ppt.category category,
2674 null personal_method,
2675 pea.prenote_date prenote_date,
2676 ppt.validation_days valid_days,
2677 100 percentage,
2678 null amount,
2679 popm_par.org_payment_method_id org_method,
2680 hr_pre_pay.set_cash_rule(ppt.category,
2681 popm_par.pmeth_information1) cash_rule,
2682 popm_par.currency_code payment_currency,
2683 ppt.pre_validation_required validation_required,
2684 ppt.validation_value validation_value,
2685 popm_par.external_account_id external_account_id,
2686 popm.defined_balance_id defined_balance_id,
2687 popm.org_payment_method_id payee_org_method,
2688 popm.time_definition_id time_def_id,
2689 decode(popm.time_definition_id,
2690 null, null,
2691 pay_core_dates.get_time_definition_date(
2692 popm.time_definition_id,
2693 ppa.effective_date,
2694 ppa.business_group_id)) payment_date
2695 from
2696 pay_external_accounts pea,
2697 pay_payment_types ppt,
2698 pay_org_payment_methods_f popm,
2699 pay_org_payment_methods_f popm_par,
2700 pay_org_pay_method_usages_f popmu,
2701 pay_assignment_actions paa,
2702 pay_payroll_actions ppa
2703 where paa.assignment_action_id = p_asg_action
2704 and paa.payroll_action_id = ppa.payroll_action_id
2705 and popm.organization_id = p_org_id
2706 and popm.type = 'PAYEE'
2707 and popm.parent_org_payment_method_id = popm_par.org_payment_method_id
2708 and popm_par.org_payment_method_id = popmu.org_payment_method_id
2709 and ppa.payroll_id = popmu.payroll_id
2710 and popm.external_account_id = pea.external_account_id (+)
2711 and popm_par.payment_type_id = ppt.payment_type_id
2712 and ppa.effective_date between popmu.effective_start_date
2713 and popmu.effective_end_date
2714 and ppa.effective_date between popm.effective_start_date
2715 and popm.effective_end_date
2716 and ppa.effective_date between popm_par.effective_start_date
2717 and popm_par.effective_end_date;
2718 --
2719 cursor get_orgs (p_asg_action in number,
2720 p_org_context_name in varchar2)
2721 is
2722 select /*+ use_nl(prrv piv)
2723 index(piv pay_input_values_f_pk) */
2724 distinct (prrv.result_value) organization_id
2725 from pay_run_result_values prrv,
2726 pay_input_values_f piv,
2727 pay_run_results prr,
2728 pay_assignment_actions paa,
2729 pay_payroll_actions ppa,
2730 pay_action_interlocks pai
2731 where pai.locking_action_id = p_asg_action
2732 and pai.locked_action_id = paa.assignment_action_id
2733 and paa.assignment_action_id = prr.assignment_action_id
2734 and paa.payroll_action_id = ppa.payroll_action_id
2735 and prr.run_result_id = prrv.run_result_id
2736 and prrv.input_value_id = piv.input_value_id
2737 and ppa.effective_date between piv.effective_start_date
2738 and piv.effective_end_date
2739 and piv.name = p_org_context_name;
2740 --
2741 valid_method boolean;
2742 exchange_rate number;
2743 pay_left number;
2744 valid_date date;
2745 child_action_id number;
2746 master_action_id number;
2747 l_bal_value number;
2748 l_org_context_name pay_legislation_contexts.input_value_name%type;
2749 --
2750 begin
2751 --
2752 hr_utility.trace('Enter process_org_third_party');
2753 --
2754 g_pre_payments.delete;
2755 pay_left := 0;
2756 --
2757 l_org_context_name := pay_core_utils.get_context_iv_name(p_asg_act,
2758 'ORGANIZATION_ID');
2759 --
2760 for orgrec in get_orgs (p_asg_act, l_org_context_name) loop
2761 --
2762 hr_utility.trace('Process Org '||orgrec.organization_id);
2763 --
2764 for payments in get_opms(p_asg_act, orgrec.organization_id) loop
2765 --
2766 get_balance_value(p_def_balance => payments.defined_balance_id,
2767 p_assignment_actions => p_asg_act,
2768 p_balance_value => l_bal_value,
2769 p_org_id => orgrec.organization_id);
2770 --
2771 hr_utility.trace('Amount to Pay '||l_bal_value);
2772 hr_utility.trace('Time Def '||payments.time_def_id);
2773 hr_utility.trace('Payment Date '||payments.payment_date);
2774 --
2775 if fnd_number.canonical_to_number(l_bal_value) <> 0 then
2776 valid_date := (fnd_date.canonical_to_date(p_eff_date) -
2777 payments.valid_days);
2778 --
2779 -- Check magnetic tape payments for prenote.
2780 --
2781 if payments.category = 'MT' and
2782 payments.validation_required = 'Y' then
2783 --
2784 valid_method := validate_magnetic(payments.personal_method,
2785 valid_date,
2786 payments.prenote_date,
2787 payments.org_method,
2788 p_asg_act,
2789 payments.validation_value,
2790 orgrec.organization_id,
2791 payments.payee_org_method);
2792 --
2793 else
2794 --
2795 valid_method := true;
2796 --
2797 end if;
2798 --
2799 --
2800 if valid_method = true then
2801 --
2802 --
2803 if fnd_number.canonical_to_number(l_bal_value) > 0
2804 or (negative_pay = 'Y' and
2805 payments.category IN ('MT', 'CA')) then
2806 --
2807 child_action_id := p_asg_act;
2808 master_action_id := p_master_aa_id;
2809 create_payment(fnd_number.canonical_to_number(l_bal_value),
2810 balance_currency,
2811 payments.payment_currency,
2812 -- exchange_rate,
2813 payments.personal_method,
2814 payments.org_method,
2815 child_action_id,
2816 payments.category,
2817 payments.cash_rule,
2818 null,
2819 master_action_id,
2820 pay_left,
2821 orgrec.organization_id,
2822 payments.payee_org_method,
2823 payments.payment_date);
2824 --
2825 if pay_left > 0 then
2826 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2827 hr_utility.raise_error;
2828 end if;
2829 --
2830 end if;
2831 else
2832 -- invalid pay method.
2833 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2834 hr_utility.raise_error;
2835 --
2836 end if;
2837 --
2838 end if;
2839 --
2840 flush_payments;
2841 --
2842 --
2843 end loop;
2844 --
2845 end loop;
2846 --
2847 hr_utility.trace('Exit process_org_third_party');
2848 --
2849 end process_org_third_party;
2850 --
2851 procedure process_third_party(p_asg_act in number,
2852 p_eff_date in varchar2,
2853 p_master_aa_id in number)
2854 is
2855 do_process boolean;
2856 l_element_id number;
2857 l_run_result number;
2858 l_bal_value varchar2(60);
2859 l_entry number;
2860 l_assignment_id number;
2861 begin
2862 --
2863 hr_utility.trace('Enter process_third_party');
2864 --
2865 do_process := TRUE;
2866 while (do_process = TRUE) loop
2867 begin
2868 get_third_party_details(p_asg_act,
2869 p_eff_date,
2870 l_element_id,
2871 l_run_result,
2872 l_assignment_id);
2873 exception
2874 when no_data_found then
2875 do_process := FALSE;
2876 end;
2877 --
2878 if (do_process = TRUE) then
2879 --
2880 get_run_result_value(l_run_result,
2881 p_eff_date,
2882 l_bal_value,
2883 l_entry);
2884 pay_third_party(l_run_result,
2885 p_asg_act,
2886 p_eff_date,
2887 l_bal_value,
2888 p_master_aa_id
2889 );
2890 --
2891 end if;
2892 end loop;
2893 --
2894 process_org_third_party(p_asg_act => p_asg_act,
2895 p_eff_date => p_eff_date,
2896 p_master_aa_id => p_master_aa_id
2897 );
2898 --
2899 hr_utility.trace('Exit process_third_party');
2900 --
2901 end process_third_party;
2902 --
2903 procedure process_action(p_asg_act in number,
2904 p_eff_date in varchar2,
2905 p_ma_flag in number,
2906 p_def_bal_id in number,
2907 p_asg_id in number,
2908 p_override_meth in number,
2909 p_master_aa_id in number,
2910 p_pay_left in out nocopy number)
2911 is
2912 l_bal_value number;
2913 begin
2914 --
2915 process_third_party(p_asg_act,
2916 p_eff_date,
2917 p_master_aa_id);
2918 --
2919 get_balance_value(p_def_bal_id,
2920 p_asg_act,
2921 l_bal_value);
2922 --
2923 -- OK about to remove this amount from the pay left
2924 p_pay_left := p_pay_left - l_bal_value;
2925 --
2926 if (p_override_meth is not null) then
2927 pay_override_method(p_asg_act,
2928 l_bal_value,
2929 p_def_bal_id,
2930 p_master_aa_id);
2931 else
2932 pay_personal_methods(p_asg_act,
2933 p_asg_id,
2934 p_eff_date,
2935 p_def_bal_id,
2936 l_bal_value,
2937 p_master_aa_id
2938 );
2939 end if;
2940 end process_action;
2941 --
2942 procedure create_child_interlocks(p_run_act_id in number,
2943 p_pre_action_id in number
2944 )
2945 is
2946 cursor get_child_actions(p_run_act_id number)
2947 is
2948 select chld.assignment_action_id
2949 from pay_assignment_actions chld,
2950 pay_assignment_actions mas
2951 where mas.assignment_action_id = p_run_act_id
2952 and mas.assignment_action_id = chld.source_action_id
2953 and mas.payroll_action_id = chld.payroll_action_id
2954 and mas.assignment_id = chld.assignment_id;
2955 begin
2956 for chdrec in get_child_actions(p_run_act_id) loop
2957 hr_nonrun_asact.insint(p_pre_action_id,chdrec.assignment_action_id);
2958 create_child_interlocks(chdrec.assignment_action_id, p_pre_action_id);
2959 end loop;
2960 end create_child_interlocks;
2961 --
2962 procedure create_child_actions(p_asg_act in number,
2963 p_ma_flag in number,
2964 p_multi_gre_payment in varchar2,
2965 p_multi_gre out nocopy boolean)
2966 is
2967 --
2968 /* Return all the tax unit assignment actions of the Run when there
2969 are more than one tax unit interlocked
2970 */
2971 cursor get_tu_child (p_action in number) is
2972 select paa.assignment_action_id,
2973 paa.assignment_id,
2974 paa_mas.payroll_action_id pre_payroll_action_id,
2975 paa.tax_unit_id run_tax_unit_id,
2976 paa_mas.chunk_number pre_chunk_number
2977 from pay_action_interlocks pai,
2978 pay_assignment_actions paa,
2979 pay_assignment_actions paa_mas
2980 where pai.locking_action_id = paa_mas.assignment_action_id
2981 and paa_mas.assignment_action_id = p_action
2982 and pai.locked_action_id = paa.assignment_action_id
2983 and exists (select ''
2984 from pay_action_interlocks pai2,
2985 pay_assignment_actions paa2
2986 where pai2.locking_action_id = paa_mas.assignment_action_id
2987 and pai2.locked_action_id = paa2.assignment_action_id
2988 and paa2.tax_unit_id <> paa.tax_unit_id)
2989 order by paa.tax_unit_id;
2990 --
2991 cursor get_mlt_asg (p_action in number) is
2992 select paa.assignment_action_id,
2993 paa.assignment_id,
2994 paa_mas.payroll_action_id pre_payroll_action_id,
2995 paa.tax_unit_id run_tax_unit_id,
2996 paa_mas.chunk_number pre_chunk_number,
2997 paa.start_date start_date,
2998 paa.end_date end_date
2999 from pay_action_interlocks pai,
3000 pay_assignment_actions paa,
3001 pay_assignment_actions paa_mas
3002 where pai.locking_action_id = paa_mas.assignment_action_id
3003 and paa_mas.assignment_action_id = p_action
3004 and pai.locked_action_id = paa.assignment_action_id
3005 --
3006 -- North America can only handle child actions on
3007 -- multiple assignment payrolls
3008 --
3009 --and exists (select ''
3010 -- from pay_action_interlocks pai2,
3011 -- pay_assignment_actions paa2
3012 -- where pai2.locking_action_id = paa_mas.assignment_action_id
3013 -- and pai2.locked_action_id = paa2.assignment_action_id
3014 -- and paa2.assignment_id <> paa.assignment_id)
3015 order by paa.assignment_id;
3016 --
3017 prev_tu number;
3018 prev_asg_id number;
3019 found_tu boolean;
3020 pre_tu_actid number;
3021 pre_asg_actid number;
3022 --
3023 begin
3024 --
3025 hr_utility.trace('Enter create_child_actions');
3026 --
3027 prev_tu := -1;
3028 --
3029 found_tu := FALSE;
3030 --
3031 -- Does the legislation or payroll allow multi gre's
3032 -- to be paid together.
3033 --
3034 if (p_multi_gre_payment = 'N') then
3035 for turec in get_tu_child(p_asg_act) loop
3036 --
3037 hr_utility.trace('Found Pro Asg '||turec.assignment_id||' '||
3038 'TU ='||turec.run_tax_unit_id);
3039 --
3040 found_tu := TRUE;
3041 --
3042 if (prev_tu <> turec.run_tax_unit_id) then
3043 --
3044 select pay_assignment_actions_s.nextval
3045 into pre_tu_actid
3046 from dual;
3047 --
3048 hr_nonrun_asact.insact(pre_tu_actid,
3049 turec.assignment_id,
3050 turec.pre_payroll_action_id,
3051 turec.pre_chunk_number,
3052 turec.run_tax_unit_id,
3053 null,
3054 'C',
3055 p_asg_act,
3056 null,
3057 null,
3058 null,
3059 null);
3060 --
3061 hr_nonrun_asact.insint(pre_tu_actid,turec.assignment_action_id);
3062 --
3063 prev_tu := turec.run_tax_unit_id;
3064 --
3065 else
3066 hr_nonrun_asact.insint(pre_tu_actid,turec.assignment_action_id);
3067 end if;
3068 end loop;
3069 end if;
3070 --
3071 /* The code is not setup to handle multi tax units and multi assignments
3072 */
3073 if (found_tu = TRUE
3074 and p_ma_flag = 1) then
3075 hr_general.assert_condition(FALSE);
3076 end if;
3077
3078 if (found_tu = FALSE
3079 and p_ma_flag = 1) then
3080 --
3081 prev_asg_id := -1;
3082 --
3083 for asgrec in get_mlt_asg(p_asg_act) loop
3084 --
3085 hr_utility.trace('Found Non Pro Asg '||asgrec.assignment_id);
3086 --
3087 if (prev_asg_id <> asgrec.assignment_id) then
3088 select pay_assignment_actions_s.nextval
3089 into pre_asg_actid
3090 from dual;
3091 --
3092 hr_nonrun_asact.insact(pre_asg_actid,
3093 asgrec.assignment_id,
3094 asgrec.pre_payroll_action_id,
3095 asgrec.pre_chunk_number,
3096 asgrec.run_tax_unit_id,
3097 null,
3098 'C',
3099 p_asg_act,
3100 null,
3101 null,
3102 null,
3103 null);
3104 --
3105 hr_nonrun_asact.insint(pre_asg_actid,asgrec.assignment_action_id);
3106 --
3107 prev_asg_id := asgrec.assignment_id;
3108 else
3109 hr_nonrun_asact.insint(pre_asg_actid,asgrec.assignment_action_id);
3110 end if;
3111 end loop;
3112 --
3113 end if;
3114 --
3115 p_multi_gre := found_tu;
3116 --
3117 hr_utility.trace('Exit create_child_actions');
3118 --
3119 end create_child_actions;
3120 --
3121 procedure do_prepayment(p_asg_act in number,
3122 p_effective_date varchar2,
3123 p_ma_flag in number,
3124 p_def_bal_id in number,
3125 p_asg_id in number,
3126 p_override_meth in number,
3127 p_multi_gre_payment in varchar2)
3128 is
3129 --
3130 cursor get_pp_chld (p_asg_act number) is
3131 select paa_chd.assignment_action_id,
3132 paa_chd.start_date
3133 from pay_assignment_actions paa_chd,
3134 pay_assignment_actions paa_mas
3135 where paa_mas.assignment_action_id = p_asg_act
3136 and paa_mas.assignment_action_id = paa_chd.source_action_id
3137 and paa_mas.payroll_action_id = paa_chd.payroll_action_id
3138 and paa_mas.chunk_number = paa_chd.chunk_number;
3139 --
3140 cursor get_legislation_rule is
3141 select validation_name, rule_type
3142 from pay_legislative_field_info
3143 where field_name = 'MULTI_TAX_UNIT_PAYMENT'
3144 and validation_type = 'ITEM_PROPERTY'
3145 and target_location = 'PAYWSDOR'
3146 and rule_mode = 'Y'
3147 and legislation_code =
3148 (select hr_api.return_legislation_code(ppa.business_group_id)
3149 from pay_payroll_actions ppa, pay_assignment_actions paa
3150 where paa.payroll_action_id = ppa.payroll_action_id
3151 and paa.assignment_action_id = p_asg_act);
3152 --
3153 l_eff_date date;
3154 l_pay_left number;
3155 child_processed boolean;
3156 l_multi_gre boolean;
3157 --
3158 begin
3159 --
3160 hr_utility.trace('Enter do_prepayment');
3161 l_eff_date := fnd_date.canonical_to_date(p_effective_date);
3162 child_processed := FALSE;
3163 --
3164 create_child_actions(p_asg_act,
3165 p_ma_flag,
3166 p_multi_gre_payment,
3167 l_multi_gre);
3168 --
3169 get_balance_value(p_def_bal_id,
3170 p_asg_act,
3171 l_pay_left);
3172 --
3173 for chdrec in get_pp_chld(p_asg_act) loop
3174 --
3175 child_processed := TRUE;
3176 if (l_multi_gre = TRUE) then
3177 --
3178 -- OK it must be a prorate
3179 -- Process it completely separately
3180 process_action(chdrec.assignment_action_id,
3181 p_effective_date,
3182 p_ma_flag,
3183 p_def_bal_id,
3184 p_asg_id,
3185 p_override_meth,
3186 p_asg_act,
3187 l_pay_left);
3188 else
3189 --
3190 -- OK This must be multiple assignments, hence
3191 -- just process the run type methods
3192 --
3193 process_third_party(chdrec.assignment_action_id,
3194 p_effective_date,
3195 p_asg_act);
3196 process_run_types(p_override_meth,
3197 chdrec.assignment_action_id,
3198 p_def_bal_id,
3199 p_effective_date,
3200 p_asg_act,
3201 l_pay_left);
3202 end if;
3203 --
3204 end loop;
3205 --
3206 -- Each child action should have dealt with its own run types
3207 -- however, if there were no children we need to deal with them.
3208 if (child_processed = FALSE) then
3209 process_third_party(p_asg_act,
3210 p_effective_date,
3211 null);
3212 process_run_types(p_override_meth,
3213 p_asg_act,
3214 p_def_bal_id,
3215 p_effective_date,
3216 null,
3217 l_pay_left);
3218 end if;
3219 --
3220 process_normal_payments(p_override_meth,
3221 p_asg_act,
3222 p_asg_id,
3223 p_def_bal_id,
3224 p_effective_date,
3225 l_pay_left);
3226
3227 hr_utility.trace('Exit do_prepayment');
3228 --
3229 end do_prepayment;
3230 --
3231 --------------------------------- get_trx_date ------------------------------
3232 /*
3233 NAME
3234 get_trx_date - derives the payment date.
3235 DESCRIPTION
3236 Returns the payment date (e.g. cheque date).
3237 NOTES
3238 <none>
3239 */
3240 function get_trx_date
3241 (
3242 p_business_group_id in number,
3243 p_payroll_action_id in number,
3244 p_assignment_action_id in number default null,
3245 p_payroll_id in number default null,
3246 p_consolidation_set_id in number default null,
3247 p_org_payment_method_id in number default null,
3248 p_effective_date in date default null,
3249 p_date_earned in date default null,
3250 p_override_date in date default null,
3251 p_pre_payment_id in number default null
3252 ) return date is
3253 --
3254 l_trx_date date;
3255 statem varchar2(1000);
3256 rows_processed integer;
3257 l_leg_code per_business_groups_perf.legislation_code%type;
3258 l_cur_id number;
3259 --
3260 begin
3261 --
3262 l_trx_date := to_date(null);
3263 --
3264 if (p_business_group_id = p_bg_id) then
3265 l_leg_code := p_leg_code;
3266 l_cur_id := p_cur_id;
3267 else
3268 select legislation_code
3269 into l_leg_code
3270 from per_business_groups_perf
3271 where business_group_id = p_business_group_id;
3272 --
3273 if l_leg_code = p_leg_code then
3274 --
3275 p_bg_id := p_business_group_id;
3276 l_cur_id := p_cur_id;
3277 --
3278 else
3279 --
3280 p_leg_code := l_leg_code;
3281 p_bg_id := p_business_group_id;
3282 --
3283 if dbms_sql.is_open(p_cur_id) then
3284 dbms_sql.close_cursor(p_cur_id);
3285 end if;
3286 --
3287 begin
3288 --
3289 statem := 'BEGIN
3290 :trx_date := pay_'||lower(l_leg_code)||'_payment_pkg.get_trx_date(
3291 :business_group_id,
3292 :payroll_action_id,
3293 :assignment_action_id,
3294 :payroll_id,
3295 :consolidation_set_id,
3296 :org_payment_method_id,
3297 :effective_date,
3298 :date_earned,
3299 :override_date,
3300 :pre_payment_id); END;';
3301 --
3302 l_cur_id := dbms_sql.open_cursor;
3303 --
3304 dbms_sql.parse(l_cur_id,
3305 statem,
3306 dbms_sql.v7);
3307 --
3308 -- First attempt to retrieve the value.
3309 --
3310 dbms_sql.bind_variable(l_cur_id, 'trx_date', l_trx_date);
3311 dbms_sql.bind_variable(l_cur_id, 'business_group_id', p_business_group_id);
3312 dbms_sql.bind_variable(l_cur_id, 'payroll_action_id', p_payroll_action_id);
3313 dbms_sql.bind_variable(l_cur_id, 'assignment_action_id', p_assignment_action_id);
3314 dbms_sql.bind_variable(l_cur_id, 'payroll_id', p_payroll_id);
3315 dbms_sql.bind_variable(l_cur_id, 'consolidation_set_id', p_consolidation_set_id);
3316 dbms_sql.bind_variable(l_cur_id, 'org_payment_method_id', p_org_payment_method_id);
3317 dbms_sql.bind_variable(l_cur_id, 'effective_date', p_effective_date);
3318 dbms_sql.bind_variable(l_cur_id, 'date_earned', p_date_earned);
3319 dbms_sql.bind_variable(l_cur_id, 'override_date', p_override_date);
3320 dbms_sql.bind_variable(l_cur_id, 'pre_payment_id', p_pre_payment_id);
3321 --
3322 rows_processed := dbms_sql.execute(l_cur_id);
3323 --
3324 dbms_sql.variable_value(l_cur_id, 'trx_date', l_trx_date);
3325 --
3326 --
3327 exception
3328 --
3329 when others then
3330 --
3331 if dbms_sql.is_open(l_cur_id) then
3332 dbms_sql.close_cursor(l_cur_id);
3333 end if;
3334 --
3335 l_cur_id := NULL;
3336 --
3337 end;
3338 --
3339 end if;
3340 --
3341 p_cur_id := l_cur_id;
3342 --
3343 end if;
3344 --
3345 if l_trx_date is not null then
3346 return l_trx_date;
3347 end if;
3348 --
3349 if l_cur_id is not null then
3350 --
3351 dbms_sql.bind_variable(l_cur_id, 'trx_date', l_trx_date);
3352 dbms_sql.bind_variable(l_cur_id, 'business_group_id', p_business_group_id);
3353 dbms_sql.bind_variable(l_cur_id, 'payroll_action_id', p_payroll_action_id);
3354 dbms_sql.bind_variable(l_cur_id, 'assignment_action_id', p_assignment_action_id);
3355 dbms_sql.bind_variable(l_cur_id, 'payroll_id', p_payroll_id);
3356 dbms_sql.bind_variable(l_cur_id, 'consolidation_set_id', p_consolidation_set_id);
3357 dbms_sql.bind_variable(l_cur_id, 'org_payment_method_id', p_org_payment_method_id);
3358 dbms_sql.bind_variable(l_cur_id, 'effective_date', p_effective_date);
3359 dbms_sql.bind_variable(l_cur_id, 'date_earned', p_date_earned);
3360 dbms_sql.bind_variable(l_cur_id, 'override_date', p_override_date);
3361 dbms_sql.bind_variable(l_cur_id, 'pre_payment_id', p_pre_payment_id);
3362 --
3363 rows_processed := dbms_sql.execute(l_cur_id);
3364 --
3365 dbms_sql.variable_value(l_cur_id, 'trx_date', l_trx_date);
3366 --
3367 else
3368 --
3369 l_trx_date := nvl(nvl(p_override_date,p_date_earned),p_effective_date);
3370 --
3371 end if;
3372 --
3373 --
3374 return l_trx_date;
3375 --
3376 end get_trx_date;
3377 --
3378 procedure process_asg_rollup(p_assignment_action in number)
3379 is
3380 --
3381 type t_pre_payment_id is table of pay_pre_payments.pre_payment_id%type
3382 index by binary_integer;
3383 type t_assignment_action_id is table of pay_pre_payments.assignment_action_id%type
3384 index by binary_integer;
3385 type t_payroll_action_id is table of pay_payroll_actions.payroll_action_id%type
3386 index by binary_integer;
3387 --
3388 f_pre_payment_id t_pre_payment_id;
3389 f_assignment_action_id t_assignment_action_id;
3390 f_payroll_action_id t_payroll_action_id;
3391 --
3392 cursor get_payment_details(p_asg_act number
3393 )
3394 is
3395 select
3396 ppp.pre_payment_id,
3397 paa_pru.assignment_action_id,
3398 ppa_pru.payroll_action_id
3399 from pay_payroll_actions ppa_pru,
3400 pay_payroll_actions ppa_pre,
3401 pay_assignment_actions paa_pru,
3402 pay_assignment_actions paa_pre,
3403 pay_action_interlocks pai,
3404 pay_pre_payments ppp
3405 where
3406 paa_pru.assignment_action_id = p_asg_act
3407 and ppa_pru.payroll_action_id = paa_pru.payroll_action_id
3408 and paa_pru.assignment_action_id = pai.locking_action_id
3409 and pai.locked_action_id = paa_pre.assignment_action_id
3410 and paa_pre.assignment_action_id = ppp.assignment_action_id
3411 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
3412 and ppp.organization_id is not null
3413 and nvl(ppp.effective_date , ppa_pre.effective_date)
3414 <= ppa_pru.effective_date
3415 and not exists (select ''
3416 from pay_contributing_payments pcp
3417 where ppp.pre_payment_id = pcp.contributing_pre_payment_id)
3418 order by ppp.org_payment_method_id, ppp.payees_org_payment_method_id
3419 for update of ppp.pre_payment_id;
3420 --
3421 begin
3422 --
3423 open get_payment_details(p_assignment_action);
3424 loop
3425 fetch get_payment_details
3426 bulk collect into f_pre_payment_id,
3427 f_assignment_action_id,
3428 f_payroll_action_id
3429 limit 1000;
3430 --
3431 forall i in 1..f_pre_payment_id.count
3432 insert into pay_contributing_payments
3433 (assignment_action_id,
3434 payroll_action_id,
3435 contributing_pre_payment_id
3436 )
3437 values (
3438 f_assignment_action_id(i),
3439 f_payroll_action_id(i),
3440 f_pre_payment_id(i)
3441 );
3442 --
3443 exit when get_payment_details%notfound;
3444 --
3445 end loop;
3446 --
3447 close get_payment_details;
3448 --
3449 end process_asg_rollup;
3450 --
3451 procedure process_pact_rollup(p_pactid in number)
3452 is
3453 --
3454 cursor get_totals(p_pactid in number)
3455 is
3456 select ppp.org_payment_method_id,
3457 ppp.payees_org_payment_method_id,
3458 ppp.organization_id,
3459 sum(nvl(base_currency_value, 0)) base_currency_value,
3460 sum(nvl(value,0)) value
3461 from pay_contributing_payments pcp,
3462 pay_pre_payments ppp
3463 where pcp.payroll_action_id = p_pactid
3464 and pcp.contributing_pre_payment_id = ppp.pre_payment_id
3465 group by ppp.org_payment_method_id,
3466 ppp.payees_org_payment_method_id,
3467 ppp.organization_id;
3468 --
3469 l_pre_pay_id pay_pre_payments.pre_payment_id%type;
3470 --
3471 begin
3472 --
3473 for totrec in get_totals(p_pactid) loop
3474 --
3475 select pay_pre_payments_s.nextval
3476 into l_pre_pay_id
3477 from dual;
3478 --
3479 insert into pay_pre_payments
3480 (pre_payment_id,
3481 org_payment_method_id,
3482 value,
3483 base_currency_value,
3484 organization_id,
3485 payees_org_payment_method_id,
3486 payroll_action_id)
3487 values
3488 (l_pre_pay_id,
3489 totrec.org_payment_method_id,
3490 totrec.value,
3491 totrec.base_currency_value,
3492 totrec.organization_id,
3493 totrec.payees_org_payment_method_id,
3494 p_pactid);
3495 --
3496 update pay_contributing_payments pcp
3497 set pre_payment_id = l_pre_pay_id
3498 where pcp.payroll_action_id = p_pactid
3499 and exists (select ''
3500 from pay_pre_payments ppp
3501 where ppp.pre_payment_id = pcp.contributing_pre_payment_id
3502 and ppp.org_payment_method_id = totrec.org_payment_method_id
3503 and ppp.payees_org_payment_method_id = totrec.payees_org_payment_method_id
3504 and ppp.organization_id = totrec.organization_id
3505 );
3506 --
3507 end loop;
3508 --
3509 end process_pact_rollup;
3510
3511 --
3512 end hr_pre_pay;