1 package body hr_pre_pay as
2 /* $Header: pyprepyt.pkb 120.10.12020000.6 2012/11/01 06:25:52 vmaripal 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 /*Bug 10212578 */
946 if substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2' then
947 g_adjust_ee_source := 'T';
948 else
949 g_adjust_ee_source := 'A';
950 end if;
951 end;
952 --
953 hr_utility.set_location('HR_PRE_PAY.Initialise',3);
954 --
955 end initialise;
956 --
957 --
958 -- OVERRIDE PAYMENTS --
959 --
960 --
961 -------------------------- init_override ------------------------------
962 /*
963 NAME
964 init_override
965 DESCRIPTION
966 initialise for an override payment method.
967 NOTES
968 Fetch details of the required override payment into memory. Generate an
969 error if this is not a valid method. Return the name of the balance
970 database item to 'C'
971 */
972 --
973 procedure init_override(action_id in out nocopy varchar2,
974 override_method in out nocopy varchar2) is
975 --
976 pay_bg_id number(16);
977 begin
978 --
979 hr_utility.set_location('HR_PRE_PAY.INIT_OVERRIDE',1);
980 --
981 payroll_action := to_number(action_id);
982 override.payment_method_id := to_number(override_method);
983 --
984 begin
985 --
986 select ppt.category,
987 UPPER(translate(pbt.balance_name,' ','_') ||
988 pbd.database_item_suffix),
989 pbt.currency_code,
990 hr_pre_pay.set_cash_rule(ppt.category,
991 opm.pmeth_information1),
992 opm.currency_code,
993 ppa.payroll_id,
994 pp.negative_pay_allowed_flag,
995 ppa.effective_date,
996 ppa.business_group_id
997 into override.category,
998 override.dbase_item,
999 balance_currency,
1000 override.cash_rule,
1001 override.currency,
1002 payroll,
1003 negative_pay,
1004 pre_payment_date,
1005 pay_bg_id
1006 from pay_all_payrolls_f pp,
1007 pay_balance_dimensions pbd,
1008 pay_balance_types pbt,
1009 pay_defined_balances pdb,
1010 pay_payment_types ppt,
1011 pay_org_pay_method_usages_f pmu,
1012 pay_org_payment_methods_f opm,
1013 pay_payroll_actions ppa
1014 where ppa.payroll_action_id = payroll_action
1015 and ppa.payroll_id = pmu.payroll_id
1016 and ppa.payroll_id = pp.payroll_id
1017 and pmu.org_payment_method_id = override_method
1018 and pmu.org_payment_method_id = opm.org_payment_method_id
1019 and opm.payment_type_id = ppt.payment_type_id
1020 and ppt.category <> 'MT'
1021 and opm.defined_balance_id = pdb.defined_balance_id
1022 and pdb.balance_type_id = pbt.balance_type_id
1023 and pdb.balance_dimension_id = pbd.balance_dimension_id
1024 and pbd.payments_flag = 'Y'
1025 and pbt.assignment_remuneration_flag = 'Y'
1026 -- and opm.pmeth_information1 = hlu.lookup_code (+)
1027 -- and NVL(hlu.lookup_type ,'CASH_ANALYSIS') = 'CASH_ANALYSIS'
1028 -- and NVL(hlu.application_id, 800) = 800
1029 and ppa.effective_date between
1030 pp.effective_start_date and pp.effective_end_date
1031 and ppa.effective_date between
1032 pmu.effective_start_date and pmu.effective_end_date
1033 and ppa.effective_date between
1034 opm.effective_start_date and opm.effective_end_date;
1035 --
1036 pay_currency_type:=hr_currency_pkg.get_rate_type(pay_bg_id,pre_payment_date,'P');
1037 exception
1038 --
1039 when others then
1040 --
1041 hr_utility.set_message(801,'HR_6239_PAYM_INVALID_OVERRIDE');
1042 hr_utility.raise_error;
1043 --
1044 end;
1045 --
1046 begin
1047 select plr.rule_mode
1048 into g_adjust_ee_source
1049 from pay_legislation_rules plr,
1050 per_business_groups_perf pbg,
1051 pay_payroll_actions ppa
1052 where ppa.payroll_action_id = payroll_action
1053 and ppa.business_group_id = pbg.business_group_id
1054 and pbg.legislation_code = plr.legislation_code
1055 and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
1056 --
1057 exception
1058 when no_data_found then
1059 g_adjust_ee_source := 'A';
1060 end;
1061 --
1062 --
1063 end init_override;
1064 --
1065 -------------------------- get_ren_balance ----------------------------------
1066 /*
1067 NAME
1068 get_ren_balance
1069 DESCRIPTION
1070 Gets the renumeration balance details that have to be paid in this run.
1071 */
1072 --
1073 procedure get_ren_balance(p_bus_grp in number,
1074 p_def_bal_id in out nocopy number) is
1075 def_bal number;
1076 bus_leg pay_balance_types.legislation_code%type;
1077 bal_found boolean;
1078 begin
1079 --
1080 bal_found := FALSE;
1081 begin
1082 select distinct pdb.defined_balance_id,
1083 pbt.currency_code
1084 into def_bal,
1085 balance_currency
1086 from pay_balance_types pbt,
1087 pay_defined_balances pdb,
1088 pay_balance_dimensions pbd
1089 where pbt.business_group_id = p_bus_grp
1090 and pbt.assignment_remuneration_flag = 'Y'
1091 and pbt.balance_type_id = pdb.balance_type_id
1092 and pdb.balance_dimension_id = pbd.balance_dimension_id
1093 and pbd.payments_flag = 'Y';
1094 --
1095 bal_found := TRUE;
1096 exception
1097 when NO_DATA_FOUND then
1098 bal_found := FALSE;
1099 end;
1100 --
1101 if (bal_found = FALSE) then
1102 select distinct legislation_code
1103 into bus_leg
1104 from per_business_groups_perf
1105 where business_group_id = p_bus_grp;
1106 --
1107 select distinct pdb.defined_balance_id,
1108 pbt.currency_code
1109 into def_bal,
1110 balance_currency
1111 from pay_balance_types pbt,
1112 pay_defined_balances pdb,
1113 pay_balance_dimensions pbd
1114 where pbt.business_group_id is null
1115 and pbt.legislation_code = bus_leg
1116 and pbt.assignment_remuneration_flag = 'Y'
1117 and pbt.balance_type_id = pdb.balance_type_id
1118 and pdb.balance_dimension_id = pbd.balance_dimension_id
1119 and pbd.payments_flag = 'Y';
1120 end if;
1121 --
1122 p_def_bal_id := def_bal;
1123 --
1124 exception
1125 when NO_DATA_FOUND then
1126 hr_utility.set_message(801,'HR_XXXX_PAYM_NO_RENUMERATION');
1127 hr_utility.raise_error;
1128 end get_ren_balance;
1129 -------------------------- get_third_party_details --------------------------
1130 /*
1131 NAME
1132 get_third_party_details
1133 DESCRIPTION
1134 Gets the third party details that have to be paid for a given assignment
1135 action.
1136 NOTES
1137 This is called from a C procedure thus details are returned to the C
1138 process, however some are also held in global variables to be used
1139 later in the run.
1140 */
1141 --
1142 procedure get_third_party_details (p_assignment_action in number,
1143 p_effective_date in varchar2,
1144 p_element_id out nocopy number,
1145 p_run_result out nocopy number,
1146 p_assignment_id out nocopy number
1147 ) is
1148 --
1149 element_id number;
1150 run_result number;
1151 assignment_id number;
1152 --
1153 begin
1154 if (not g_third_party%ISOPEN) then
1155 open g_third_party(p_assignment_action);
1156 end if;
1157 --
1158 fetch g_third_party into balance_currency, element_id, run_result,assignment_id;
1159 --
1160 if (g_third_party%NOTFOUND) then
1161 close g_third_party;
1162 raise NO_DATA_FOUND;
1163 end if;
1164 --
1165 p_element_id := element_id;
1166 p_run_result := run_result;
1167 p_assignment_id := assignment_id;
1168 --
1169 return;
1170 --
1171 exception
1172 when NO_DATA_FOUND then
1173 if (g_third_party%ISOPEN) then
1174 close g_third_party;
1175 end if;
1176 raise;
1177 when others then
1178 if (g_third_party%ISOPEN) then
1179 close g_third_party;
1180 end if;
1181 raise;
1182 end get_third_party_details;
1183 --
1184 -------------------------- get_balance_value ---------------------------------
1185 /*
1186 NAME
1187 get_balance_value
1188 DESCRIPTION
1189 Gets the balance value to be paid for a specific assignment action
1190 assignment action.
1191 NOTES
1192 The value is returned to the C process.
1193 */
1194 procedure get_balance_value(p_def_balance in number,
1195 p_assignment_actions in number,
1196 p_balance_value in out nocopy number,
1197 p_org_id in number default null) is
1198 ass_act_id number;
1199 begin
1200 --
1201 if (p_org_id is not null) then
1202 pay_balance_pkg.set_context('ORGANIZATION_ID', p_org_id);
1203 end if;
1204 --
1205 p_balance_value := pay_balance_pkg.get_value(p_def_balance,
1206 p_assignment_actions);
1207 return;
1208 end get_balance_value;
1209 --
1210 -------------------------- adjust_payments ------------------------------
1211 /*
1212 NAME
1213 adjust_payments
1214 DESCRIPTION
1215 This procedure is called if prepayments detects at the end of processing
1216 an amount that still hasn't been paid. This procedure searches the
1217 payments for a method that can pay it.
1218 */
1219 procedure adjust_payments(pay_left in out nocopy number,
1220 p_src_action_id in number)
1221 --
1222 is
1223 pp_ptr number;
1224 --
1225 begin
1226 --
1227 hr_utility.trace('Enter adjust_payments');
1228 pp_ptr := g_pre_payments.count;
1229 while (pp_ptr > 0) loop
1230 --
1231 hr_utility.trace('Compare '||g_pre_payments(pp_ptr).category);
1232 hr_utility.trace('Compare '||
1233 nvl(g_pre_payments(pp_ptr).payment_currency, 'NULL')||
1234 ' with '||
1235 nvl(g_pre_payments(pp_ptr).base_currency, 'NULL'));
1236 hr_utility.trace('Compare '||
1237 nvl(g_pre_payments(pp_ptr).source_action_id, -999)||
1238 ' with '||
1239 nvl(p_src_action_id, -999));
1240 --
1241 if (g_pre_payments(pp_ptr).category <> 'CA'
1242 and g_pre_payments(pp_ptr).payment_currency =
1243 g_pre_payments(pp_ptr).base_currency
1244 and nvl(g_pre_payments(pp_ptr).source_action_id, -999)
1245 = nvl(p_src_action_id, -999))
1246 then
1247 --
1248 g_pre_payments(pp_ptr).base_currency_value :=
1249 g_pre_payments(pp_ptr).base_currency_value + pay_left ;
1250 g_pre_payments(pp_ptr).value :=
1251 g_pre_payments(pp_ptr).base_currency_value;
1252 pay_left := 0;
1253 --
1254 end if;
1255 --
1256 pp_ptr := pp_ptr - 1;
1257 end loop;
1258 hr_utility.trace('Exit adjust_payments');
1259 --
1260 end adjust_payments;
1261 --
1262 -------------------------- pay_per_payment_methods ------------------------------
1263 /*
1264 NAME
1265 pay_per_payment_methods
1266 DESCRIPTION
1267 This distributes the payable amount over the personal payment methods
1268 as directed. If it is to be paid by magnetic tape do the required
1269 validation check. If there is not a payment method specified use the
1270 default method.
1271 */
1272 procedure pay_per_payment_methods(p_assignment_action in number,
1273 p_assignment in number,
1274 p_effective_date in varchar2,
1275 p_def_balance in number,
1276 p_total_pay in number,
1277 p_pay_left in out nocopy number,
1278 p_src_act_id in number default null,
1279 p_prepayment_action_id in number default null)
1280 is
1281 --
1282 base_payment number;
1283 payment boolean;
1284 valid_date date;
1285 valid_method boolean;
1286 last_method g_pp_methods%rowtype;
1287 exchange_rate number;
1288 leg_code varchar2(150);
1289 prenote_default varchar2(1);
1290 found boolean;
1291 l_org_method_id pay_org_payment_methods.org_payment_method_id%TYPE;
1292 --
1293 begin
1294 --
1295 hr_utility.trace('Enter pay_per_payment_methods');
1296 payment := FALSE;
1297
1298 -- check for legilsation rule
1299 select org.legislation_code
1300 into leg_code
1301 from pay_assignment_Actions act,
1302 pay_payroll_actions pact,
1303 per_business_groups_perf org
1304 where act.assignment_action_id=p_assignment_action
1305 and pact.payroll_action_id=act.payroll_action_id
1306 and org.business_group_id=pact.business_group_id;
1307
1308 pay_core_utils.get_legislation_rule(
1309 'PRENOTE_DEFAULT',
1310 leg_code,
1311 prenote_default,found);
1312 --
1313 for payments in g_pp_methods(p_assignment, p_effective_date,
1314 p_def_balance) loop
1315 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1316 payments.valid_days);
1317 --
1318 -- put code here to derive override org payment method
1319 -- when external account id is null
1320 --
1321 --
1322 if (payment = TRUE) then
1323 if valid_method = TRUE then
1324 create_payment(base_payment,
1325 balance_currency,
1326 last_method.payment_currency,
1327 last_method.personal_method,
1328 last_method.org_method,
1329 p_assignment_action,
1330 last_method.category,
1331 last_method.cash_rule,
1332 p_src_act_id,
1333 p_prepayment_action_id,
1334 p_pay_left);
1335 else
1336 create_payment(base_payment,
1337 balance_currency,
1338 default_method.currency,
1339 null,
1340 default_method.payment_method_id,
1341 p_assignment_action,
1342 default_method.category,
1343 default_method.cash_rule,
1344 p_src_act_id,
1345 p_prepayment_action_id,
1346 p_pay_left);
1347 end if;
1348 payment := FALSE;
1349 end if;
1350 --
1351 -- Pay this method. Find out the amount of this payment and the
1352 -- pay left.
1353 --
1354 if p_pay_left > 0
1355 or (negative_pay = 'Y' and p_pay_left <> 0 and
1356 payments.category IN ('MT', 'CA')) then
1357 --
1358 -- Check for magnetic tape payments. Check they have been
1359 -- prenoted.
1360 --
1361 if payments.category = 'MT' and
1362 payments.validation_required = 'Y' then
1363 --
1364 valid_method := validate_magnetic(
1365 payments.personal_method,
1366 valid_date,
1367 payments.prenote_date,
1368 payments.org_method,
1369 p_assignment_action,
1370 payments.validation_value);
1371
1372 --
1373 else
1374 --
1375 valid_method := true;
1376 --
1377 end if;
1378 --
1379 if valid_method = true then
1380 --
1381 pay_method(p_pay_left,
1382 p_total_pay,
1383 base_payment,
1384 payments.percentage,
1385 payments.amount,
1386 payments.payment_currency);
1387 --
1388 -- Now if set up the correct exchange rate if required
1389 --
1390 --
1391 -- Now if there is anything to pay, create a payment record
1392 -- in the correct currency. Also if required perform cash
1393 -- analysis.
1394 --
1395 if base_payment > 0
1396 or (negative_pay = 'Y' and
1397 payments.category IN ('MT', 'CA')) then
1398 --
1399 payment := TRUE;
1400 end if;
1401 last_method := payments;
1402 else
1403 if (found=true and upper(prenote_default)='Y')
1404 then
1405 -- pay in default method
1406 pay_method(p_pay_left,
1407 p_total_pay,
1408 base_payment,
1409 payments.percentage,
1410 payments.amount,
1411 payments.payment_currency);
1412 if base_payment > 0
1413 or (negative_pay = 'Y' and
1414 default_method.category IN ('MT', 'CA')) then
1415 payment := TRUE;
1416 end if;
1417 end if;
1418 end if;
1419 end if;
1420 --
1421 end loop;
1422 if (payment = TRUE) then
1423 base_payment := base_payment + p_pay_left;
1424 p_pay_left := 0;
1425 if ( valid_method=TRUE) then
1426 create_payment(base_payment,
1427 balance_currency,
1428 last_method.payment_currency,
1429 -- exchange_rate,
1430 last_method.personal_method,
1431 last_method.org_method,
1432 p_assignment_action,
1433 last_method.category,
1434 last_method.cash_rule,
1435 p_src_act_id,
1436 p_prepayment_action_id,
1437 p_pay_left);
1438 else
1439 create_payment(base_payment,
1440 balance_currency,
1441 default_method.currency,
1442 null,
1443 default_method.payment_method_id,
1444 p_assignment_action,
1445 default_method.category,
1446 default_method.cash_rule,
1447 p_src_act_id,
1448 p_prepayment_action_id,
1449 p_pay_left);
1450 end if;
1451 -- Check to see if there is any money left.
1452 -- If there is then the last payment must have been cash
1453 -- that could not be paid due to the denominations
1454 if (p_pay_left > 0) then
1455 adjust_payments(p_pay_left, p_src_act_id);
1456 --
1457 if p_pay_left > 0 then
1458 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1459 hr_utility.raise_error;
1460 end if;
1461 end if;
1462 --
1463 end if ;
1464 --
1465 hr_utility.trace('Exit pay_per_payment_methods');
1466 --
1467 end pay_per_payment_methods;
1468 --
1469 -------------------------- pay_run_type_methods ----------------------------- -
1470 /*
1471 NAME
1472 pay_run_type_methods
1473 DESCRIPTION
1474 This distributes the payable amount over the run type methods
1475 as directed. If it is to be paid by magnetic tape do the required
1476 validation check. If there is not a payment method specified use the
1477 default method.
1478 */
1479 procedure pay_run_type_methods(p_assignment_action in number,
1480 p_effective_date in varchar2,
1481 p_def_balance in number,
1482 p_pay_left in out nocopy number,
1483 p_master_aa_id in number) is
1484 cursor chdact is
1485 select paa_chd.assignment_action_id,
1486 paa_chd.run_type_id,
1487 prt.run_method,paa_chd.assignment_id
1488 from pay_assignment_actions paa_par,
1489 pay_action_interlocks pai,
1490 pay_assignment_actions paa_chd,
1491 pay_payroll_actions ppa_chd,
1492 pay_run_types_f prt
1493 where paa_par.assignment_action_id = p_assignment_action
1494 and paa_par.assignment_action_id = pai.locking_action_id
1495 and pai.locked_action_id = paa_chd.assignment_action_id
1496 and paa_chd.run_type_id is not null
1497 and paa_chd.run_type_id = prt.run_type_id
1498 and prt.run_method in ('N','S','P')
1499 and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
1500 and ppa_chd.effective_date between prt.effective_start_date
1501 and prt.effective_end_date
1502 order by paa_chd.action_sequence;
1503 --
1504 cursor rt_org_methods (p_run_type in number,
1505 p_effective_date in varchar2,
1506 p_def_balance in number,
1507 p_assignment_action in number) is
1508 select ppt.category category,
1509 null personal_method,
1510 null prenote_date,
1511 ppt.validation_days valid_days,
1512 rtom.percentage percentage,
1513 rtom.amount amount,
1514 opm.org_payment_method_id org_method,
1515 hr_pre_pay.set_cash_rule(ppt.category,
1516 opm.pmeth_information1) cash_rule,
1517 opm.currency_code payment_currency,
1518 ppt.pre_validation_required validation_required,
1519 ppt.validation_value validation_value,
1520 opm.external_account_id external_account_id
1521 from pay_payment_types ppt,
1522 pay_org_payment_methods_f opm,
1523 pay_run_type_org_methods_f rtom,
1524 pay_org_pay_method_usages_f opmu,
1525 pay_payroll_actions ppa,
1526 pay_assignment_actions paa
1527 where rtom.run_type_id = p_run_type
1528 and rtom.org_payment_method_id = opm.org_payment_method_id
1529 and opm.payment_type_id = ppt.payment_type_id
1530 and opm.defined_balance_id = p_def_balance
1531 and paa.assignment_action_id = p_assignment_action
1532 and paa.payroll_action_id = ppa.payroll_action_id
1533 and ppa.payroll_id = opmu.payroll_id
1534 and opmu.org_payment_method_id = opm.org_payment_method_id
1535 and fnd_date.canonical_to_date(p_effective_date) between
1536 opmu.effective_start_date and opmu.effective_end_date
1537 and fnd_date.canonical_to_date(p_effective_date) between
1538 rtom.effective_start_date and rtom.effective_end_date
1539 and fnd_date.canonical_to_date(p_effective_date) between
1540 opm.effective_start_date and opm.effective_end_date
1541 order by rtom.priority;
1542
1543
1544 cursor rt_personal_pay_methods ( p_run_type in number,
1545 p_effective_date in varchar2,
1546 p_def_balance in number,
1547 p_assignment_action in number) is
1548 select ppt.category category,
1549 ppm.personal_payment_method_id personal_method,
1550 pea.prenote_date prenote_date,
1551 ppt.validation_days valid_days,
1552 ppm.percentage percentage,
1553 ppm.amount amount,
1554 opm.org_payment_method_id org_method,
1555 hr_pre_pay.set_cash_rule(ppt.category,
1556 opm.pmeth_information1) cash_rule,
1557 opm.currency_code payment_currency,
1558 ppt.pre_validation_required validation_required,
1559 ppt.validation_value validation_value,
1560 opm.external_account_id external_account_id
1561 from pay_external_accounts pea,
1562 pay_payment_types ppt,
1563 pay_org_payment_methods_f opm,
1564 pay_personal_payment_methods_f ppm,
1565 pay_assignment_actions act
1566 where act.assignment_action_id=p_assignment_action
1567 and ppm.assignment_id = act.assignment_id
1568 and ppm.run_type_id = p_run_type
1569 and ppm.org_payment_method_id = opm.org_payment_method_id
1570 and opm.payment_type_id = ppt.payment_type_id
1571 and opm.defined_balance_id = p_def_balance
1572 and ppm.external_account_id = pea.external_account_id (+)
1573 and fnd_date.canonical_to_date(p_effective_date) between
1574 ppm.effective_start_date and ppm.effective_end_date
1575 and fnd_date.canonical_to_date(p_effective_date) between
1576 opm.effective_start_date and opm.effective_end_date
1577 order by ppm.person_id,ppm.priority;
1578 --
1579 -- Coinage cursor. fetch all monetary units of a currency in order of value
1580
1581 --
1582 --
1583 /*
1584 pay_left number;
1585 payment boolean;
1586 ren_method_payment boolean;
1587 last_method g_pp_methods%rowtype;
1588 exchange_rate number;
1589 */
1590 valid_date date;
1591 base_payment number;
1592 valid_method boolean;
1593 total_pay number;
1594 pay_left number;
1595 master_action_id number;
1596 child_action_id number;
1597 assign_id number;
1598 leg_code varchar2(150);
1599 prenote_default varchar2(1);
1600 found boolean;
1601 l_org_method_id pay_org_payment_methods.org_payment_method_id%TYPE;
1602 got_payment_amount boolean;
1603 begin
1604 --
1605 hr_utility.trace('Enter pay_run_type_methods');
1606 -- check for legilsation rule
1607 select org.legislation_code
1608 into leg_code
1609 from pay_assignment_Actions act,
1610 pay_payroll_actions pact,
1611 per_business_groups_perf org
1612 where act.assignment_action_id=p_assignment_Action
1613 and pact.payroll_action_id=act.payroll_action_id
1614 and org.business_group_id=pact.business_group_id;
1615
1616 pay_core_utils.get_legislation_rule(
1617 'PRENOTE_DEFAULT',
1618 leg_code,
1619 prenote_default,found);
1620
1621 for chdrec in chdact loop
1622 got_payment_amount := FALSE;
1623 assign_id := chdrec.assignment_id;
1624
1625 child_action_id := p_assignment_action;
1626 master_action_id := p_master_aa_id;
1627
1628 for payments in rt_personal_pay_methods(chdrec.run_type_id, p_effective_date,
1629 p_def_balance, chdrec.assignment_action_id)
1630 loop
1631 --
1632 if (got_payment_amount = FALSE) then
1633 get_balance_value(p_def_balance,
1634 chdrec.assignment_action_id,
1635 total_pay);
1636 pay_left := total_pay;
1637 got_payment_amount := TRUE;
1638 end if;
1639 --
1640 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1641 payments.valid_days);
1642 --
1643 -- Pay this method. Find out the amount of this payment and the
1644 -- pay left.
1645 --
1646 if pay_left > 0
1647 or (negative_pay = 'Y' and pay_left <> 0 and
1648 payments.category IN ('MT', 'CA')) then
1649 --
1650 -- Check for magnetic tape payments. Check they have been
1651 -- prenoted.
1652 --
1653 if payments.category = 'MT' and
1654 payments.validation_required = 'Y' then
1655 --
1656 valid_method := validate_magnetic(
1657 payments.personal_method,
1658 valid_date,
1659 payments.prenote_date,
1660 payments.org_method,
1661 p_assignment_action,
1662 payments.validation_value);
1663
1664 --
1665 else
1666 --
1667 valid_method := true;
1668 --
1669 end if;
1670 --
1671 if valid_method = true then
1672 --
1673 pay_method(pay_left,
1674 total_pay,
1675 base_payment,
1676 payments.percentage,
1677 payments.amount,
1678 payments.payment_currency);
1679 --
1680 -- Now if set up the correct exchange rate if required
1681 --
1682 --
1683 -- Now if there is anything to pay, create a payment record
1684 -- in the correct currency. Also if required perform cash
1685 -- analysis.
1686 --
1687 if base_payment > 0
1688 or (negative_pay = 'Y' and
1689 payments.category IN ('MT', 'CA')) then
1690 --
1691 create_payment(base_payment,
1692 balance_currency,
1693 payments.payment_currency,
1694 -- exchange_rate,
1695 payments.personal_method,
1696 payments.org_method,
1697 child_action_id,
1698 payments.category,
1699 payments.cash_rule,
1700 chdrec.assignment_action_id,
1701 master_action_id,
1702 pay_left);
1703 -- Subtract the amount that was actually taken.
1704 p_pay_left := p_pay_left -
1705 g_pre_payments(g_pre_payments.count).base_currency_value;
1706
1707 end if;
1708 else
1709 if (found=true and upper(prenote_default)='Y')
1710 then
1711 -- pay in default method
1712 pay_method(pay_left,
1713 total_pay,
1714 base_payment,
1715 payments.percentage,
1716 payments.amount,
1717 payments.payment_currency);
1718 --
1719 -- Now if there is anything to pay, create a payment record
1720 -- in the correct currency. Also if required perform cash
1721 -- analysis.
1722 --
1723 if base_payment > 0
1724 or (negative_pay = 'Y' and
1725 default_method.category IN ('MT', 'CA')) then
1726 create_payment(base_payment,
1727 balance_currency,
1728 default_method.currency,
1729 null,
1730 default_method.payment_method_id,
1731 child_action_id,
1732 default_method.category,
1733 default_method.cash_rule,
1734 chdrec.assignment_action_id,
1735 master_action_id,
1736 pay_left);
1737 -- Subtract the amount that was actually taken.
1738 p_pay_left := p_pay_left -
1739 g_pre_payments(g_pre_payments.count).base_currency_value;
1740
1741 end if;
1742 end if;
1743 end if;
1744 end if;
1745 end loop;
1746
1747
1748
1749
1750 --
1751 for payments in rt_org_methods(chdrec.run_type_id, p_effective_date,
1752 p_def_balance, chdrec.assignment_action_id)
1753 loop
1754 --
1755 if (got_payment_amount = FALSE) then
1756 get_balance_value(p_def_balance,
1757 chdrec.assignment_action_id,
1758 total_pay);
1759 pay_left := total_pay;
1760 got_payment_amount := TRUE;
1761 end if;
1762 --
1763 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
1764 payments.valid_days);
1765 --
1766 -- Pay this method. Find out the amount of this payment and the
1767 -- pay left.
1768 --
1769 if pay_left > 0
1770 or (negative_pay = 'Y' and pay_left <> 0 and
1771 payments.category IN ('MT', 'CA')) then
1772 --
1773 -- Check for magnetic tape payments. Check they have been
1774 -- prenoted.
1775 --
1776 if payments.category = 'MT' and
1777 payments.validation_required = 'Y' then
1778 --
1779 valid_method := validate_magnetic(
1780 payments.personal_method,
1781 valid_date,
1782 payments.prenote_date,
1783 payments.org_method,
1784 p_assignment_action,
1785 payments.validation_value);
1786
1787 --
1788 else
1789 --
1790 valid_method := true;
1791 --
1792 end if;
1793 --
1794 if valid_method = true then
1795 --
1796 pay_method(pay_left,
1797 total_pay,
1798 base_payment,
1799 payments.percentage,
1800 payments.amount,
1801 payments.payment_currency);
1802 --
1803 -- Now if set up the correct exchange rate if required
1804 --
1805 --
1806 -- Now if there is anything to pay, create a payment record
1807 -- in the correct currency. Also if required perform cash
1808 -- analysis.
1809 --
1810 if base_payment > 0
1811 or (negative_pay = 'Y' and
1812 payments.category IN ('MT', 'CA')) then
1813 --
1814 create_payment(base_payment,
1815 balance_currency,
1816 payments.payment_currency,
1817 -- exchange_rate,
1818 payments.personal_method,
1819 payments.org_method,
1820 child_action_id,
1821 payments.category,
1822 payments.cash_rule,
1823 chdrec.assignment_action_id,
1824 master_action_id,
1825 pay_left);
1826 -- Subtract the amount that was actually taken.
1827 p_pay_left := p_pay_left -
1828 g_pre_payments(g_pre_payments.count).base_currency_value;
1829 end if;
1830 else
1831 if (found=true and upper(prenote_default)='Y')
1832 then
1833 -- pay in default method
1834 pay_method(pay_left,
1835 total_pay,
1836 base_payment,
1837 payments.percentage,
1838 payments.amount,
1839 payments.payment_currency);
1840 --
1841 -- Now if there is anything to pay, create a payment record
1842 -- in the correct currency. Also if required perform cash
1843 -- analysis.
1844 --
1845 if base_payment > 0
1846 or (negative_pay = 'Y' and
1847 default_method.category IN ('MT', 'CA')) then
1848 create_payment(base_payment,
1849 balance_currency,
1850 default_method.currency,
1851 null,
1852 default_method.payment_method_id,
1853 child_action_id,
1854 default_method.category,
1855 default_method.cash_rule,
1856 chdrec.assignment_action_id,
1857 master_action_id,
1858 pay_left);
1859 -- Subtract the amount that was actually taken.
1860 p_pay_left := p_pay_left -
1861 g_pre_payments(g_pre_payments.count).base_currency_value;
1862
1863 end if;
1864 end if;
1865 end if;
1866 end if;
1867 end loop;
1868 --
1869 -- OK, we've checked the run type payment methods now
1870 -- if this is a separate payment run, and if there is
1871 -- money left to be paid then use either the personal
1872 -- payment methods or the default payment method.
1873 if chdrec.run_method = 'S' then
1874 --
1875 if (got_payment_amount = FALSE) then
1876 get_balance_value(p_def_balance,
1877 chdrec.assignment_action_id,
1878 total_pay);
1879 pay_left := total_pay;
1880 got_payment_amount := TRUE;
1881 end if;
1882 --
1883 if (pay_left > 0 or
1884 (negative_pay = 'Y' and default_method.category in ('CA', 'MT'))) then
1885 --
1886 declare
1887 --
1888 payment_amount number;
1889 begin
1890 --
1891 payment_amount := pay_left;
1892 -- Process the personal payment methods that are left.
1893 pay_per_payment_methods(child_action_id,
1894 assign_id,
1895 p_effective_date,
1896 p_def_balance,
1897 total_pay,
1898 pay_left,
1899 chdrec.assignment_action_id,
1900 master_action_id);
1901 --
1902 p_pay_left := p_pay_left - (payment_amount - pay_left);
1903 --
1904 if pay_left > 0
1905 or (negative_pay = 'Y' and default_method.category = 'CA') then
1906 --
1907 base_payment := pay_left;
1908 pay_left := 0;
1909 --
1910 create_payment(base_payment,
1911 balance_currency,
1912 default_method.currency,
1913 -- default_method.exchange_rate,
1914 null,
1915 default_method.payment_method_id,
1916 child_action_id,
1917 default_method.category,
1918 default_method.cash_rule,
1919 chdrec.assignment_action_id,
1920 master_action_id,
1921 pay_left);
1922 --
1923 if pay_left > 0 then
1924 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1925 hr_utility.raise_error;
1926 end if;
1927 --
1928 p_pay_left := p_pay_left - base_payment;
1929 end if;
1930 end;
1931 end if;
1932 end if;
1933 --
1934 end loop;
1935 hr_utility.trace('Exit pay_run_type_methods');
1936 end pay_run_type_methods;
1937 --
1938 procedure process_per_payment(p_assignment_action in number,
1939 p_assignment in number,
1940 p_effective_date in varchar2,
1941 p_def_balance in number,
1942 p_pay_left in out nocopy number)
1943 is
1944 base_payment number;
1945 total_pay number;
1946 begin
1947 total_pay := p_pay_left;
1948 -- Process the personal payment methods that are left.
1949 pay_per_payment_methods(p_assignment_action,
1950 p_assignment,
1951 p_effective_date,
1952 p_def_balance,
1953 total_pay,
1954 p_pay_left);
1955 --
1956 if p_pay_left > 0
1957 or (negative_pay = 'Y' and default_method.category = 'CA') then
1958 --
1959 base_payment := p_pay_left;
1960 p_pay_left := 0;
1961 --
1962 create_payment(base_payment,
1963 balance_currency,
1964 default_method.currency,
1965 -- default_method.exchange_rate,
1966 null,
1967 default_method.payment_method_id,
1968 p_assignment_action,
1969 default_method.category,
1970 default_method.cash_rule,
1971 null,
1972 null,
1973 p_pay_left);
1974 --
1975 if p_pay_left > 0 then
1976 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
1977 hr_utility.raise_error;
1978 end if;
1979 --
1980 end if;
1981 --
1982 end process_per_payment;
1983 --
1984 -------------------------- pay_personal_methods ------------------------------
1985 /*
1986 NAME
1987 pay_personal_methods
1988 DESCRIPTION
1989 This distributes the payable amount over the personal payment methods
1990 as directed. If it is to be paid by magnetic tape do the required
1991 validation check. If there is not a payment method specified use the
1992 default method. If the amount is for a third party then get the payment
1993 method required and pay the value.
1994 */
1995 procedure pay_personal_methods(p_assignment_action in number,
1996 p_assignment in number,
1997 p_effective_date in varchar2,
1998 p_def_balance in number,
1999 p_balance_value in varchar2,
2000 p_master_aa_id in number) is
2001
2002 base_payment number;
2003 total_pay number;
2004 pay_left number;
2005 begin
2006 --
2007 hr_utility.trace('Enter pay_personal_methods');
2008 g_pre_payments.delete;
2009 total_pay := fnd_number.canonical_to_number(p_balance_value);
2010 pay_left := total_pay;
2011 --
2012 -- Process any payment methods associated with the
2013 -- Run Types
2014 pay_run_type_methods(p_assignment_action,
2015 p_effective_date,
2016 p_def_balance,
2017 pay_left,
2018 p_master_aa_id);
2019 --
2020 process_per_payment(p_assignment_action,
2021 p_assignment,
2022 p_effective_date,
2023 p_def_balance,
2024 pay_left);
2025 --
2026 flush_payments;
2027 --
2028 hr_utility.trace('Exit pay_personal_methods');
2029 end pay_personal_methods;
2030 --
2031 -------------------------- pay_run_type_override ----------------------------- -
2032 /*
2033 NAME
2034 pay_run_type_override
2035 DESCRIPTION
2036 This distributes the payable amount using the override method
2037 over the pay separately run types.
2038 */
2039 procedure pay_run_type_override(p_assignment_action in number,
2040 p_def_balance in number,
2041 p_pay_left in out nocopy number,
2042 p_master_aa_id in number) is
2043
2044 cursor chdact is
2045 select paa_chd.assignment_action_id, paa_chd.run_type_id, prt.run_method,paa_chd.assignment_id
2046 from pay_assignment_actions paa_par,
2047 pay_action_interlocks pai,
2048 pay_assignment_actions paa_chd,
2049 pay_payroll_actions ppa_chd,
2050 pay_run_types_f prt
2051 where paa_par.assignment_action_id = p_assignment_action
2052 and paa_par.assignment_action_id = pai.locking_action_id
2053 and pai.locked_action_id = paa_chd.assignment_action_id
2054 and paa_chd.run_type_id is not null
2055 and paa_chd.run_type_id = prt.run_type_id
2056 and ppa_chd.payroll_action_id = paa_chd.payroll_action_id
2057 and ppa_chd.effective_date between prt.effective_start_date
2058 and prt.effective_end_date
2059 order by paa_chd.action_sequence;
2060 --
2061 total_pay number;
2062 pay_left number;
2063 master_action_id number;
2064 child_action_id number;
2065 assign_id number;
2066 --
2067 begin
2068 --
2069 hr_utility.trace('Enter pay_run_type_override');
2070 --
2071 for chdrec in chdact loop
2072 --
2073 if chdrec.run_method = 'S' then
2074 --
2075 get_balance_value(p_def_balance,
2076 chdrec.assignment_action_id,
2077 total_pay);
2078 pay_left := 0;
2079 --
2080 hr_utility.trace('Found Child Type '||chdrec.run_method);
2081 --
2082 -- OK, we've checked the run type payment methods now
2083 -- if this is a separate payment run, and if there is
2084 -- money left to be paid then use either the personal
2085 -- payment methods or the default payment method.
2086 --
2087 if
2088 (total_pay > 0
2089 or (negative_pay = 'Y' and total_pay <> 0 and
2090 override.category = 'CA')) then
2091 --
2092 hr_utility.trace('Paying Run Override of '||total_pay);
2093 --
2094 child_action_id := p_assignment_action;
2095 master_action_id := p_master_aa_id;
2096 --
2097 create_payment(total_pay,
2098 balance_currency,
2099 override.currency,
2100 -- override.exchange_rate,
2101 null,
2102 override.payment_method_id,
2103 child_action_id,
2104 override.category,
2105 override.cash_rule,
2106 chdrec.assignment_action_id,
2107 master_action_id,
2108 pay_left);
2109 --
2110 if pay_left > 0 then
2111 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2112 hr_utility.raise_error;
2113 end if;
2114 --
2115 p_pay_left := p_pay_left - total_pay;
2116 --
2117 end if;
2118 end if;
2119 --
2120 end loop;
2121 hr_utility.trace('Exit pay_run_type_override');
2122 --
2123 end pay_run_type_override;
2124 --
2125 procedure process_run_types(p_override_method in number,
2126 p_assignment_action in number,
2127 p_def_balance in number,
2128 p_effective_date in varchar2,
2129 p_master_aa_id in number,
2130 p_pay_left in out nocopy number)
2131 is
2132 begin
2133 --
2134 g_pre_payments.delete;
2135 --
2136 if (p_override_method is not null) then
2137 pay_run_type_override(p_assignment_action,
2138 p_def_balance,
2139 p_pay_left,
2140 p_master_aa_id);
2141 else
2142 pay_run_type_methods(p_assignment_action,
2143 p_effective_date,
2144 p_def_balance,
2145 p_pay_left,
2146 p_master_aa_id);
2147 end if;
2148 --
2149 flush_payments;
2150 end process_run_types;
2151 --
2152 procedure process_ovr_payment(p_assignment_action in number,
2153 p_pay_left in out nocopy number)
2154 is
2155 base_value number;
2156 begin
2157 base_value := p_pay_left;
2158 p_pay_left := 0;
2159 --
2160 -- If the base_value is non-zero then insert a p ayment record in the
2161 -- payment currency. Also if the category is cash then perform cash
2162 -- analysis.
2163 --
2164 --
2165 if base_value > 0
2166 or (negative_pay = 'Y' and base_value <> 0 and
2167 override.category = 'CA') then
2168 create_payment(base_value,
2169 balance_currency,
2170 override.currency,
2171 -- override.exchange_rate,
2172 null,
2173 override.payment_method_id,
2174 p_assignment_action,
2175 override.category,
2176 override.cash_rule,
2177 null,
2178 null,
2179 p_pay_left);
2180 --
2181 if p_pay_left > 0 then
2182 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2183 hr_utility.raise_error;
2184 end if;
2185 end if;
2186 end process_ovr_payment;
2187 --
2188 procedure process_normal_payments(p_override_method in number,
2189 p_assignment_action in number,
2190 p_assignment in number,
2191 p_def_balance in number,
2192 p_effective_date in varchar2,
2193 p_pay_left in out nocopy number)
2194 is
2195 begin
2196 --
2197 hr_utility.trace('Enter process_normal_payments');
2198 --
2199 g_pre_payments.delete;
2200 if (p_override_method is not null) then
2201 process_ovr_payment(p_assignment_action,
2202 p_pay_left);
2203 else
2204 process_per_payment(p_assignment_action,
2205 p_assignment,
2206 p_effective_date,
2207 p_def_balance,
2208 p_pay_left);
2209 end if;
2210 --
2211 flush_payments;
2212 --
2213 hr_utility.trace('Exit process_normal_payments');
2214 end process_normal_payments;
2215 --
2216 -------------------------- pay_override_method -------------------------------
2217 /*
2218 NAME
2219 pay_override_method
2220 DESCRIPTION
2221 This uses the overiding payment method to pay the payment amount unless
2222 it is a third party payment, in which case it will pay by the personal
2223 payment method specified for the payment.
2224 */
2225 procedure pay_override_method(p_assignment_action in number,
2226 p_balance_value in varchar2,
2227 p_def_balance in number,
2228 p_master_aa_id in number) is
2229
2230 base_value number;
2231 pay_left number;
2232
2233 begin
2234 --
2235 g_pre_payments.delete;
2236 --
2237 --
2238 -- First pay the pay separate runs to the override method
2239 --
2240 pay_left := fnd_number.canonical_to_number(p_balance_value);
2241 pay_run_type_override(p_assignment_action,
2242 p_def_balance,
2243 pay_left,
2244 p_master_aa_id);
2245 --
2246 -- The remaining amount should be paid by the override payment methods.
2247 --
2248 process_ovr_payment(p_assignment_action,
2249 pay_left);
2250 --
2251 flush_payments;
2252 --
2253 end pay_override_method;
2254 --
2255 -------------------------- get_run_result_value ------------------------------
2256 /*
2257 NAME
2258 get_run_result_value
2259 DESCRIPTION
2260 This retrieves the value of a third party payment and the element entry
2261 id that it is based on.
2262 */
2263 procedure get_run_result_value(p_run_result in number,
2264 p_effective_date in varchar2,
2265 p_run_result_value out nocopy varchar2,
2266 p_entry out nocopy number) is
2267 res_value number;
2268 entry number;
2269 status varchar2(2);
2270 adj_value number;
2271 assign_id number;
2272 assign_action_id number;
2273 pay_act_id number;
2274 run_date_earned date;
2275 begin
2276 --
2277 -- Bug 1849996. This code now assumes that a no data found
2278 -- means that a zero payment is to be made. Legislation
2279 -- teams have been warned that this may have to change
2280 -- in future. They should explicitly set the Pay Value
2281 -- to zero for a non pament.
2282 --
2283 begin
2284 select prr.source_id,
2285 fnd_number.canonical_to_number(rrv.result_value),
2286 prr.status,
2287 paa.assignment_id,
2288 prr.assignment_action_id,
2289 paa.payroll_action_id,
2290 ppa.date_earned
2291 into entry,
2292 res_value,
2293 status,
2294 assign_id,
2295 assign_action_id,
2296 pay_act_id,
2297 run_date_earned
2298 from
2299 pay_input_values_f piv,
2300 pay_element_types_f pet,
2301 pay_run_result_values rrv,
2302 pay_run_results prr2,
2303 pay_run_results prr,
2304 pay_assignment_actions paa,
2305 pay_payroll_actions ppa
2306 where prr.run_result_id = p_run_result
2307 and paa.assignment_action_id= prr.assignment_action_id
2308 and ppa.payroll_action_id = paa.payroll_action_id
2309 and pet.element_type_id = prr2.element_type_id
2310 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2311 and prr.source_id = prr2.source_id
2312 and prr.assignment_action_id = prr2.assignment_action_id
2313 and prr2.source_type in ('E','I')
2314 and prr2.entry_type not in ('R','A')
2315 and prr2.run_result_id = rrv.run_result_id
2316 and rrv.input_value_id = piv.input_value_id
2317 and rrv.result_value is not null
2318 and piv.name = 'Pay Value'
2319 and ppa.date_earned between piv.effective_start_date
2320 and piv.effective_end_date
2321 and ppa.date_earned between pet.effective_start_date
2322 and pet.effective_end_date
2323 and (( prr.source_type = prr2.source_type and
2324 decode (pet.proration_group_id, null, 1, prr.run_result_id) =
2325 decode (pet.proration_group_id, null, 1, prr2.run_result_id))
2326 or ( prr.source_type <> prr2.source_type)) ;
2327 exception
2328 when no_data_found then
2329 entry:= null;
2330 res_value := 0;
2331 status := null;
2332 assign_id := null;
2333 assign_action_id := null;
2334 pay_act_id := null;
2335 end;
2336 --
2337 if status in ('PA', 'R')
2338 then
2339 --
2340 /* Does the RR source Id point to the target or the adjustment */
2341 if (g_adjust_ee_source = 'T') then
2342 select fnd_number.canonical_to_number(rrv.result_value)
2343 into adj_value
2344 from
2345 pay_input_values_f piv,
2346 pay_element_types_f pet,
2347 pay_run_result_values rrv,
2348 pay_run_results prr2,
2349 pay_run_results prr
2350 where prr.run_result_id = p_run_result
2351 and prr.assignment_action_id = assign_action_id
2352 and pet.element_type_id = prr2.element_type_id
2353 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2354 and prr.source_id = prr2.source_id
2355 and prr.assignment_action_id = prr2.assignment_action_id
2356 and prr2.source_type in ('E','I')
2357 and prr2.entry_type in ('R','A')
2358 and prr.entry_type not in ('R','A')
2359 and prr2.run_result_id = rrv.run_result_id
2360 and rrv.input_value_id = piv.input_value_id
2361 and rrv.result_value is not null
2362 and piv.name = 'Pay Value'
2363 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2364 piv.effective_start_date and piv.effective_end_date
2365 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2366 pet.effective_start_date and pet.effective_end_date;
2367 else
2368 select fnd_number.canonical_to_number(rrv.result_value)
2369 into adj_value
2370 from
2371 pay_element_entries_f pee,
2372 pay_input_values_f piv,
2373 pay_element_types_f pet,
2374 pay_run_result_values rrv,
2375 pay_run_results prr2,
2376 pay_run_results prr
2377 where pee.target_entry_id=entry
2378 and pee.assignment_id = assign_id
2379 and prr.source_id=pee.element_entry_id
2380 and prr.assignment_action_id = assign_action_id
2381 and pet.element_type_id = prr2.element_type_id
2382 and pet.THIRD_PARTY_PAY_ONLY_FLAG = 'Y'
2383 and prr.source_id = prr2.source_id
2384 and prr.assignment_action_id = prr2.assignment_action_id
2385 and prr2.source_type in ('E','I')
2386 and prr2.run_result_id = rrv.run_result_id
2387 and rrv.input_value_id = piv.input_value_id
2388 and rrv.result_value is not null
2389 and piv.name = 'Pay Value'
2390 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2391 piv.effective_start_date and piv.effective_end_date
2392 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2393 pet.effective_start_date and pet.effective_end_date
2394 and nvl(run_date_earned, fnd_date.canonical_to_date(p_effective_date)) between
2395 pee.effective_start_date and pee.effective_end_date;
2396 end if;
2397 end if;
2398 --
2399 if (status = 'R')
2400 then
2401 p_run_result_value := fnd_number.number_to_canonical(adj_value);
2402 elsif (status = 'PA')
2403 then
2404 p_run_result_value := fnd_number.number_to_canonical(adj_value)+fnd_number.number_to_canonical(res_value);
2405 else
2406 p_run_result_value := fnd_number.number_to_canonical(res_value);
2407 end if;
2408 p_entry := entry;
2409 end get_run_result_value;
2410 --
2411 -------------------------- pay_third_party -----------------------------------
2412 /*
2413 NAME
2414 pay_third_party
2415 DESCRIPTION
2416 This retrieves a single payment method that is to be used for this third
2417 party payment and then pays the this method the full payable amount.
2418 */
2419 procedure pay_third_party(p_run_results in number,
2420 p_assignment_action in number,
2421 p_effective_date in varchar2,
2422 p_bal_value in varchar2,
2423 p_master_aa_id in number
2424 ) is
2425
2426 payment_details g_pp_methods%rowtype;
2427 valid_method boolean;
2428 exchange_rate number;
2429 pay_left number;
2430 valid_date date;
2431 child_action_id number;
2432 master_action_id number;
2433 l_max_eff_end_date date;
2434 begin
2435 hr_utility.trace('Entered pay third party');
2436 --
2437 g_pre_payments.delete;
2438 pay_left := 0;
2439 --
2440
2441 /*Added for bug 13428575*/
2442 /*Here calculating the max effective end date of the element entry for a particular run result*/
2443
2444 select max(pee.effective_end_date)
2445 into l_max_eff_end_date
2446 from pay_run_results prr,
2447 pay_element_entries_f pee
2448 where prr.run_result_id = p_run_results
2449 and prr.source_id = pee.element_entry_id ;
2450
2451 /*if the thirdparty element is end dated
2452 verifying if the enddate is falling in the current payroll action*/
2453
2454 select ppt.category category,
2455 ppm.personal_payment_method_id personal_method,
2456 pea.prenote_date prenote_date,
2457 ppt.validation_days valid_days,
2458 ppm.percentage percentage,
2459 ppm.amount amount,
2460 opm.org_payment_method_id org_method,
2461 hr_pre_pay.set_cash_rule(ppt.category,
2462 opm.pmeth_information1) cash_rule,
2463 opm.currency_code payment_currency,
2464 ppt.pre_validation_required validation_required,
2465 ppt.validation_value validation_value,
2466 ppm.external_account_id external_account_id
2467 into payment_details
2468 from pay_run_results prr,
2469 pay_element_entries_f pee,
2470 pay_personal_payment_methods_f ppm,
2471 pay_external_accounts pea,
2472 pay_org_payment_methods_f opm,
2473 pay_payment_types ppt,
2474 pay_assignment_actions paa,
2475 pay_payroll_actions ppa
2476 where prr.run_result_id = p_run_results
2477 /* Bug 12364433*/
2478 and nvl(prr.element_entry_id,prr.source_id) = pee.element_entry_id
2479 and paa.assignment_action_id = prr.assignment_action_id
2480 and paa.payroll_action_id = ppa.payroll_action_id
2481 and pee.personal_payment_method_id = ppm.personal_payment_method_id
2482 and ppm.org_payment_method_id = opm.org_payment_method_id
2483 and opm.payment_type_id = ppt.payment_type_id
2484 and ppm.external_account_id = pea.external_account_id (+)
2485 -- and opm.pmeth_information1 = hlu.lookup_code (+)
2486 -- and NVL(hlu.lookup_type, 'CASH_ANALYSIS') = 'CASH_ANALYSIS'
2487 -- and NVL(hlu.application_id, 800) = 800
2488 and fnd_date.canonical_to_date(p_effective_date) between
2489 ppm.effective_start_date and ppm.effective_end_date
2490 and fnd_date.canonical_to_date(p_effective_date) between
2491 opm.effective_start_date and opm.effective_end_date
2492 and ( (ppa.date_earned between
2493 pee.effective_start_date and pee.effective_end_date)
2494 OR exists
2495 (
2496 SELECT 1
2497 FROM per_time_periods ptp
2498 WHERE ptp.time_period_id = ppa.time_period_id
2499 AND pee.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
2500 and pee.effective_end_date = l_max_eff_end_date
2501 )
2502 )
2503 order by ppm.priority;
2504 --
2505 hr_utility.trace('Entered pay third party location 1 ');
2506 if fnd_number.canonical_to_number(p_bal_value) <> 0 then
2507 valid_date := (fnd_date.canonical_to_date(p_effective_date) -
2508 payment_details.valid_days);
2509 --
2510 -- Check magnetic tape payments for prenote.
2511 --
2512 if payment_details.category = 'MT' and
2513 payment_details.validation_required = 'Y' then
2514 --
2515 valid_method := validate_magnetic(payment_details.personal_method,
2516 valid_date,
2517 payment_details.prenote_date,
2518 payment_details.org_method,
2519 p_assignment_action,
2520 payment_details.validation_value);
2521 --
2522 else
2523 --
2524 valid_method := true;
2525 --
2526 end if;
2527 --
2528 if valid_method = true then
2529 --
2530 --
2531 if fnd_number.canonical_to_number(p_bal_value) > 0
2532 or (negative_pay = 'Y' and
2533 payment_details.category IN ('MT', 'CA')) then
2534 --
2535 declare
2536 rt_method pay_run_types_f.run_method%type;
2537 sep_chq_aa_id pay_assignment_actions.assignment_action_id%type;
2538 begin
2539 --
2540 -- Get the run method
2541 select nvl(prt.run_method, 'N'), paa.assignment_action_id
2542 into rt_method,
2543 sep_chq_aa_id
2544 from pay_run_types_f prt,
2545 pay_assignment_actions paa,
2546 pay_run_results prr,
2547 pay_payroll_actions ppa
2548 where prr.run_result_id = p_run_results
2549 and prr.assignment_action_id = paa.assignment_action_id
2550 and paa.payroll_action_id = ppa.payroll_action_id
2551 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
2552 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
2553 and nvl(prt.effective_end_date, ppa.effective_date);
2554 --
2555 if (rt_method <> 'S' ) then
2556 sep_chq_aa_id := null;
2557 end if;
2558 --
2559 child_action_id := p_assignment_action;
2560 master_action_id := p_master_aa_id;
2561 --
2562 create_payment(fnd_number.canonical_to_number(p_bal_value),
2563 balance_currency,
2564 payment_details.payment_currency,
2565 -- exchange_rate,
2566 payment_details.personal_method,
2567 payment_details.org_method,
2568 child_action_id,
2569 payment_details.category,
2570 payment_details.cash_rule,
2571 sep_chq_aa_id,
2572 master_action_id,
2573 pay_left);
2574 --
2575 if pay_left > 0 then
2576 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2577 hr_utility.raise_error;
2578 end if;
2579 --
2580 end;
2581 end if;
2582 else
2583 -- invalid pay method.
2584 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2585 hr_utility.raise_error;
2586 --
2587 end if;
2588 --
2589 end if;
2590 --
2591 flush_payments;
2592 --
2593 exception
2594 when NO_DATA_FOUND then
2595 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2596 hr_utility.raise_error;
2597 end pay_third_party;
2598 --
2599 ------------------- get_dynamic_org_method -------------------
2600 /*
2601 NAME
2602 get_dynamic_org_method
2603 DESCRIPTION
2604 Given a legislative procedure name and the assignment action,
2605 this procedure calculated the organisation method id when
2606 the external account is null.
2607 */
2608 --
2609 procedure get_dynamic_org_method (p_plsql_proc in varchar2,
2610 p_assignment_action in number,
2611 p_effective_date in date,
2612 p_org_meth in number,
2613 p_org_method_id out nocopy number )
2614 is
2615 l_def_rt_str varchar2(2000); -- used with dynamic pl/sql
2616 sql_cursor integer;
2617 l_rows integer;
2618 l_org_method_id number;
2619 l_paytype number;
2620 l_ext_acc number(16);
2621 begin
2622 l_def_rt_str := 'begin '||p_plsql_proc||' (';
2623 l_def_rt_str := l_def_rt_str || ':p_assignment_action, ';
2624 l_def_rt_str := l_def_rt_str || ':p_effective_date, ';
2625 l_def_rt_str := l_def_rt_str || ':p_org_meth, ';
2626 l_def_rt_str := l_def_rt_str || ':l_org_method_id); end; ';
2627 --
2628 sql_cursor := dbms_sql.open_cursor;
2629 --
2630 dbms_sql.parse(sql_cursor, l_def_rt_str, dbms_sql.v7);
2631 --
2632 dbms_sql.bind_variable(sql_cursor, 'p_assignment_action', p_assignment_action);
2633 --
2634 dbms_sql.bind_variable(sql_cursor, 'p_effective_date', p_effective_date);
2635 --
2636 dbms_sql.bind_variable(sql_cursor, 'p_org_meth', p_org_meth);
2637 --
2638 dbms_sql.bind_variable(sql_cursor, 'l_org_method_id', l_org_method_id);
2639 --
2640 l_rows := dbms_sql.execute (sql_cursor);
2641 --
2642 if (l_rows = 1) then
2643 dbms_sql.variable_value(sql_cursor, 'l_org_method_id',
2644 l_org_method_id);
2645 dbms_sql.close_cursor(sql_cursor);
2646 --
2647 -- Check that procedure returns a payment method with the same
2648 -- payment type as the original payment method.
2649 -- Also currency needs to be the same.
2650 --
2651 select count(*)
2652 into l_paytype
2653 from pay_org_payment_methods_f opm1
2654 ,pay_org_payment_methods_f opm2
2655 where opm1.org_payment_method_id = p_org_meth
2656 and opm2.org_payment_method_id = l_org_method_id
2657 and opm1.payment_type_id = opm2.payment_type_id
2658 and opm1.currency_code = opm2.currency_code
2659 and p_effective_date between opm1.effective_start_date
2660 and opm1.effective_end_date
2661 and p_effective_date between opm2.effective_start_date
2662 and opm2.effective_end_date;
2663 --
2664 -- Check that the new payment method does not have a null bank account
2665 --
2666 select count(*)
2667 into l_ext_acc
2668 from pay_org_payment_methods_f opm
2669 where opm.org_payment_method_id = l_org_method_id
2670 and opm.external_account_id is not null
2671 and p_effective_date between opm.effective_start_date
2672 and opm.effective_end_date;
2673
2674 if ((l_paytype = 0) or (l_ext_acc = 0)) then
2675 hr_utility.set_message(801,'HR_50412_INVALID_ORG_PAYMETH');
2676 hr_utility.raise_error;
2677 end if;
2678 --
2679 else
2680 dbms_sql.close_cursor(sql_cursor);
2681 hr_utility.set_message(801,'HR_50412_INVALID_ORG_PAYMETH');
2682 hr_utility.raise_error;
2683 end if;
2684 --
2685 p_org_method_id := l_org_method_id;
2686 --
2687 end get_dynamic_org_method;
2688 --
2689 --
2690 procedure close_cursors is
2691 begin
2692 if(g_third_party%ISOPEN) then
2693 close g_third_party;
2694 end if;
2695 if (coin_cursor%ISOPEN) then
2696 close coin_cursor;
2697 end if;
2698 end close_cursors;
2699 --
2700 procedure process_org_third_party(p_asg_act in number,
2701 p_eff_date in varchar2,
2702 p_master_aa_id in number)
2703 is
2704 --
2705 cursor get_opms(p_asg_action in number,
2706 p_org_id in number)
2707 is
2708 select
2709 ppt.category category,
2710 null personal_method,
2711 pea.prenote_date prenote_date,
2712 ppt.validation_days valid_days,
2713 100 percentage,
2714 null amount,
2715 popm_par.org_payment_method_id org_method,
2716 hr_pre_pay.set_cash_rule(ppt.category,
2717 popm_par.pmeth_information1) cash_rule,
2718 popm_par.currency_code payment_currency,
2719 ppt.pre_validation_required validation_required,
2720 ppt.validation_value validation_value,
2721 popm_par.external_account_id external_account_id,
2722 popm.defined_balance_id defined_balance_id,
2723 popm.org_payment_method_id payee_org_method,
2724 popm.time_definition_id time_def_id,
2725 decode(popm.time_definition_id,
2726 null, null,
2727 pay_core_dates.get_time_definition_date(
2728 popm.time_definition_id,
2729 ppa.effective_date,
2730 ppa.business_group_id)) payment_date
2731 from
2732 pay_external_accounts pea,
2733 pay_payment_types ppt,
2734 pay_org_payment_methods_f popm,
2735 pay_org_payment_methods_f popm_par,
2736 pay_org_pay_method_usages_f popmu,
2737 pay_assignment_actions paa,
2738 pay_payroll_actions ppa
2739 where paa.assignment_action_id = p_asg_action
2740 and paa.payroll_action_id = ppa.payroll_action_id
2741 and popm.organization_id = p_org_id
2742 and popm.type = 'PAYEE'
2743 and popm.parent_org_payment_method_id = popm_par.org_payment_method_id
2744 and popm_par.org_payment_method_id = popmu.org_payment_method_id
2745 and ppa.payroll_id = popmu.payroll_id
2746 and popm.external_account_id = pea.external_account_id (+)
2747 and popm_par.payment_type_id = ppt.payment_type_id
2748 and ppa.effective_date between popmu.effective_start_date
2749 and popmu.effective_end_date
2750 and ppa.effective_date between popm.effective_start_date
2751 and popm.effective_end_date
2752 and ppa.effective_date between popm_par.effective_start_date
2753 and popm_par.effective_end_date;
2754 --
2755 -- Bug 8262632
2756 -- Removed optimizer hints from the cursor get_orgs for performance concern
2757 --
2758
2759 cursor get_orgs (p_asg_action in number,
2760 p_org_context_name in varchar2)
2761 is
2762 select distinct (prrv.result_value) organization_id
2763 from pay_run_result_values prrv,
2764 pay_input_values_f piv,
2765 pay_run_results prr,
2766 pay_assignment_actions paa,
2767 pay_payroll_actions ppa,
2768 pay_action_interlocks pai
2769 where pai.locking_action_id = p_asg_action
2770 and pai.locked_action_id = paa.assignment_action_id
2771 and paa.assignment_action_id = prr.assignment_action_id
2772 and paa.payroll_action_id = ppa.payroll_action_id
2773 and prr.run_result_id = prrv.run_result_id
2774 and prrv.input_value_id = piv.input_value_id
2775 and ppa.effective_date between piv.effective_start_date
2776 and piv.effective_end_date
2777 and piv.name = p_org_context_name;
2778 --
2779 valid_method boolean;
2780 exchange_rate number;
2781 pay_left number;
2782 valid_date date;
2783 child_action_id number;
2784 master_action_id number;
2785 l_bal_value number;
2786 l_org_context_name pay_legislation_contexts.input_value_name%type;
2787 --
2788 begin
2789 --
2790 hr_utility.trace('Enter process_org_third_party');
2791 --
2792 g_pre_payments.delete;
2793 pay_left := 0;
2794 --
2795 l_org_context_name := pay_core_utils.get_context_iv_name(p_asg_act,
2796 'ORGANIZATION_ID');
2797 --
2798 for orgrec in get_orgs (p_asg_act, l_org_context_name) loop
2799 --
2800 hr_utility.trace('Process Org '||orgrec.organization_id);
2801 --
2802 for payments in get_opms(p_asg_act, orgrec.organization_id) loop
2803 --
2804 get_balance_value(p_def_balance => payments.defined_balance_id,
2805 p_assignment_actions => p_asg_act,
2806 p_balance_value => l_bal_value,
2807 p_org_id => orgrec.organization_id);
2808 --
2809 hr_utility.trace('Amount to Pay '||l_bal_value);
2810 hr_utility.trace('Time Def '||payments.time_def_id);
2811 hr_utility.trace('Payment Date '||payments.payment_date);
2812 --
2813 if fnd_number.canonical_to_number(l_bal_value) <> 0 then
2814 valid_date := (fnd_date.canonical_to_date(p_eff_date) -
2815 payments.valid_days);
2816 --
2817 -- Check magnetic tape payments for prenote.
2818 --
2819 if payments.category = 'MT' and
2820 payments.validation_required = 'Y' then
2821 --
2822 valid_method := validate_magnetic(payments.personal_method,
2823 valid_date,
2824 payments.prenote_date,
2825 payments.org_method,
2826 p_asg_act,
2827 payments.validation_value,
2828 orgrec.organization_id,
2829 payments.payee_org_method);
2830 --
2831 else
2832 --
2833 valid_method := true;
2834 --
2835 end if;
2836 --
2837 --
2838 if valid_method = true then
2839 --
2840 --
2841 if fnd_number.canonical_to_number(l_bal_value) > 0
2842 or (negative_pay = 'Y' and
2843 payments.category IN ('MT', 'CA')) then
2844 --
2845 child_action_id := p_asg_act;
2846 master_action_id := p_master_aa_id;
2847 create_payment(fnd_number.canonical_to_number(l_bal_value),
2848 balance_currency,
2849 payments.payment_currency,
2850 -- exchange_rate,
2851 payments.personal_method,
2852 payments.org_method,
2853 child_action_id,
2854 payments.category,
2855 payments.cash_rule,
2856 null,
2857 master_action_id,
2858 pay_left,
2859 orgrec.organization_id,
2860 payments.payee_org_method,
2861 payments.payment_date);
2862 --
2863 if pay_left > 0 then
2864 hr_utility.set_message(801,'HR_6442_PAYM_MISSING_UNITS');
2865 hr_utility.raise_error;
2866 end if;
2867 --
2868 end if;
2869 else
2870 -- invalid pay method.
2871 hr_utility.set_message(801,'HR_7723_PAYM_NO_PAY_METHOD');
2872 hr_utility.raise_error;
2873 --
2874 end if;
2875 --
2876 end if;
2877 --
2878 flush_payments;
2879 --
2880 --
2881 end loop;
2882 --
2883 end loop;
2884 --
2885 hr_utility.trace('Exit process_org_third_party');
2886 --
2887 end process_org_third_party;
2888 --
2889 procedure process_third_party(p_asg_act in number,
2890 p_eff_date in varchar2,
2891 p_master_aa_id in number)
2892 is
2893 do_process boolean;
2894 l_element_id number;
2895 l_run_result number;
2896 l_bal_value varchar2(60);
2897 l_entry number;
2898 l_assignment_id number;
2899 begin
2900 --
2901 hr_utility.trace('Enter process_third_party');
2902 --
2903 do_process := TRUE;
2904 while (do_process = TRUE) loop
2905 begin
2906 get_third_party_details(p_asg_act,
2907 p_eff_date,
2908 l_element_id,
2909 l_run_result,
2910 l_assignment_id);
2911 exception
2912 when no_data_found then
2913 do_process := FALSE;
2914 end;
2915 --
2916 if (do_process = TRUE) then
2917 --
2918 get_run_result_value(l_run_result,
2919 p_eff_date,
2920 l_bal_value,
2921 l_entry);
2922 pay_third_party(l_run_result,
2923 p_asg_act,
2924 p_eff_date,
2925 l_bal_value,
2926 p_master_aa_id
2927 );
2928 --
2929 end if;
2930 end loop;
2931 --
2932 process_org_third_party(p_asg_act => p_asg_act,
2933 p_eff_date => p_eff_date,
2934 p_master_aa_id => p_master_aa_id
2935 );
2936 --
2937 hr_utility.trace('Exit process_third_party');
2938 --
2939 end process_third_party;
2940 --
2941 procedure process_action(p_asg_act in number,
2942 p_eff_date in varchar2,
2943 p_ma_flag in number,
2944 p_def_bal_id in number,
2945 p_asg_id in number,
2946 p_override_meth in number,
2947 p_master_aa_id in number,
2948 p_pay_left in out nocopy number)
2949 is
2950 l_bal_value number;
2951 begin
2952 --
2953 process_third_party(p_asg_act,
2954 p_eff_date,
2955 p_master_aa_id);
2956 --
2957 get_balance_value(p_def_bal_id,
2958 p_asg_act,
2959 l_bal_value);
2960 --
2961 -- OK about to remove this amount from the pay left
2962 p_pay_left := p_pay_left - l_bal_value;
2963 --
2964 if (p_override_meth is not null) then
2965 pay_override_method(p_asg_act,
2966 l_bal_value,
2967 p_def_bal_id,
2968 p_master_aa_id);
2969 else
2970 pay_personal_methods(p_asg_act,
2971 p_asg_id,
2972 p_eff_date,
2973 p_def_bal_id,
2974 l_bal_value,
2975 p_master_aa_id
2976 );
2977 end if;
2978 end process_action;
2979 --
2980 procedure create_child_interlocks(p_run_act_id in number,
2981 p_pre_action_id in number
2982 )
2983 is
2984 cursor get_child_actions(p_run_act_id number)
2985 is
2986 select chld.assignment_action_id
2987 from pay_assignment_actions chld,
2988 pay_assignment_actions mas
2989 where mas.assignment_action_id = p_run_act_id
2990 and mas.assignment_action_id = chld.source_action_id
2991 and mas.payroll_action_id = chld.payroll_action_id
2992 and mas.assignment_id = chld.assignment_id;
2993 begin
2994 for chdrec in get_child_actions(p_run_act_id) loop
2995 hr_nonrun_asact.insint(p_pre_action_id,chdrec.assignment_action_id);
2996 create_child_interlocks(chdrec.assignment_action_id, p_pre_action_id);
2997 end loop;
2998 end create_child_interlocks;
2999 --
3000 procedure create_child_actions(p_asg_act in number,
3001 p_ma_flag in number,
3002 p_multi_gre_payment in varchar2,
3003 p_multi_gre out nocopy boolean)
3004 is
3005 --
3006 /* Return all the tax unit assignment actions of the Run when there
3007 are more than one tax unit interlocked
3008 */
3009 cursor get_tu_child (p_action in number) is
3010 select paa.assignment_action_id,
3011 paa.assignment_id,
3012 paa_mas.payroll_action_id pre_payroll_action_id,
3013 paa.tax_unit_id run_tax_unit_id,
3014 paa_mas.chunk_number pre_chunk_number
3015 from pay_action_interlocks pai,
3016 pay_assignment_actions paa,
3017 pay_assignment_actions paa_mas
3018 where pai.locking_action_id = paa_mas.assignment_action_id
3019 and paa_mas.assignment_action_id = p_action
3020 and pai.locked_action_id = paa.assignment_action_id
3021 and exists (select ''
3022 from pay_action_interlocks pai2,
3023 pay_assignment_actions paa2
3024 where pai2.locking_action_id = paa_mas.assignment_action_id
3025 and pai2.locked_action_id = paa2.assignment_action_id
3026 and paa2.tax_unit_id <> paa.tax_unit_id)
3027 order by paa.tax_unit_id;
3028 --
3029 cursor get_mlt_asg (p_action in number) is
3030 select paa.assignment_action_id,
3031 paa.assignment_id,
3032 paa_mas.payroll_action_id pre_payroll_action_id,
3033 paa.tax_unit_id run_tax_unit_id,
3034 paa_mas.chunk_number pre_chunk_number,
3035 paa.start_date start_date,
3036 paa.end_date end_date
3037 from pay_action_interlocks pai,
3038 pay_assignment_actions paa,
3039 pay_assignment_actions paa_mas
3040 where pai.locking_action_id = paa_mas.assignment_action_id
3041 and paa_mas.assignment_action_id = p_action
3042 and pai.locked_action_id = paa.assignment_action_id
3043 --
3044 -- North America can only handle child actions on
3045 -- multiple assignment payrolls
3046 --
3047 --and exists (select ''
3048 -- from pay_action_interlocks pai2,
3049 -- pay_assignment_actions paa2
3050 -- where pai2.locking_action_id = paa_mas.assignment_action_id
3051 -- and pai2.locked_action_id = paa2.assignment_action_id
3052 -- and paa2.assignment_id <> paa.assignment_id)
3053 order by paa.assignment_id;
3054 --
3055 prev_tu number;
3056 prev_asg_id number;
3057 found_tu boolean;
3058 pre_tu_actid number;
3059 pre_asg_actid number;
3060 --
3061 begin
3062 --
3063 hr_utility.trace('Enter create_child_actions');
3064 --
3065 prev_tu := -1;
3066 --
3067 found_tu := FALSE;
3068 --
3069 -- Does the legislation or payroll allow multi gre's
3070 -- to be paid together.
3071 --
3072 if (p_multi_gre_payment = 'N') then
3073 for turec in get_tu_child(p_asg_act) loop
3074 --
3075 hr_utility.trace('Found Pro Asg '||turec.assignment_id||' '||
3076 'TU ='||turec.run_tax_unit_id);
3077 --
3078 found_tu := TRUE;
3079 --
3080 if (prev_tu <> turec.run_tax_unit_id) then
3081 --
3082 select pay_assignment_actions_s.nextval
3083 into pre_tu_actid
3084 from dual;
3085 --
3086 hr_nonrun_asact.insact(pre_tu_actid,
3087 turec.assignment_id,
3088 turec.pre_payroll_action_id,
3089 turec.pre_chunk_number,
3090 turec.run_tax_unit_id,
3091 null,
3092 'C',
3093 p_asg_act,
3094 null,
3095 null,
3096 null,
3097 null);
3098 --
3099 hr_nonrun_asact.insint(pre_tu_actid,turec.assignment_action_id);
3100 --
3101 prev_tu := turec.run_tax_unit_id;
3102 --
3103 else
3104 hr_nonrun_asact.insint(pre_tu_actid,turec.assignment_action_id);
3105 end if;
3106 end loop;
3107 end if;
3108 --
3109 /* The code is not setup to handle multi tax units and multi assignments
3110 */
3111 if (found_tu = TRUE
3112 and p_ma_flag = 1) then
3113 hr_general.assert_condition(FALSE);
3114 end if;
3115
3116 if (found_tu = FALSE
3117 and p_ma_flag = 1) then
3118 --
3119 prev_asg_id := -1;
3120 --
3121 for asgrec in get_mlt_asg(p_asg_act) loop
3122 --
3123 hr_utility.trace('Found Non Pro Asg '||asgrec.assignment_id);
3124 --
3125 if (prev_asg_id <> asgrec.assignment_id) then
3126 select pay_assignment_actions_s.nextval
3127 into pre_asg_actid
3128 from dual;
3129 --
3130 hr_nonrun_asact.insact(pre_asg_actid,
3131 asgrec.assignment_id,
3132 asgrec.pre_payroll_action_id,
3133 asgrec.pre_chunk_number,
3134 asgrec.run_tax_unit_id,
3135 null,
3136 'C',
3137 p_asg_act,
3138 null,
3139 null,
3140 null,
3141 null);
3142 --
3143 hr_nonrun_asact.insint(pre_asg_actid,asgrec.assignment_action_id);
3144 --
3145 prev_asg_id := asgrec.assignment_id;
3146 else
3147 hr_nonrun_asact.insint(pre_asg_actid,asgrec.assignment_action_id);
3148 end if;
3149 end loop;
3150 --
3151 end if;
3152 --
3153 p_multi_gre := found_tu;
3154 --
3155 hr_utility.trace('Exit create_child_actions');
3156 --
3157 end create_child_actions;
3158 --
3159 procedure do_prepayment(p_asg_act in number,
3160 p_effective_date varchar2,
3161 p_ma_flag in number,
3162 p_def_bal_id in number,
3163 p_asg_id in number,
3164 p_override_meth in number,
3165 p_multi_gre_payment in varchar2)
3166 is
3167 --
3168 cursor get_pp_chld (p_asg_act number) is
3169 select paa_chd.assignment_action_id,
3170 paa_chd.start_date
3171 from pay_assignment_actions paa_chd,
3172 pay_assignment_actions paa_mas
3173 where paa_mas.assignment_action_id = p_asg_act
3174 and paa_mas.assignment_action_id = paa_chd.source_action_id
3175 and paa_mas.payroll_action_id = paa_chd.payroll_action_id
3176 and paa_mas.chunk_number = paa_chd.chunk_number;
3177 --
3178 cursor get_legislation_rule is
3179 select validation_name, rule_type
3180 from pay_legislative_field_info
3181 where field_name = 'MULTI_TAX_UNIT_PAYMENT'
3182 and validation_type = 'ITEM_PROPERTY'
3183 and target_location = 'PAYWSDOR'
3184 and rule_mode = 'Y'
3185 and legislation_code =
3186 (select hr_api.return_legislation_code(ppa.business_group_id)
3187 from pay_payroll_actions ppa, pay_assignment_actions paa
3188 where paa.payroll_action_id = ppa.payroll_action_id
3189 and paa.assignment_action_id = p_asg_act);
3190 --
3191 l_eff_date date;
3192 l_pay_left number;
3193 child_processed boolean;
3194 l_multi_gre boolean;
3195 --
3196 begin
3197 --
3198 hr_utility.trace('Enter do_prepayment');
3199 l_eff_date := fnd_date.canonical_to_date(p_effective_date);
3200 child_processed := FALSE;
3201 --
3202 create_child_actions(p_asg_act,
3203 p_ma_flag,
3204 p_multi_gre_payment,
3205 l_multi_gre);
3206 --
3207 get_balance_value(p_def_bal_id,
3208 p_asg_act,
3209 l_pay_left);
3210 --
3211 for chdrec in get_pp_chld(p_asg_act) loop
3212 --
3213 child_processed := TRUE;
3214 if (l_multi_gre = TRUE) then
3215 --
3216 -- OK it must be a prorate
3217 -- Process it completely separately
3218 process_action(chdrec.assignment_action_id,
3219 p_effective_date,
3220 p_ma_flag,
3221 p_def_bal_id,
3222 p_asg_id,
3223 p_override_meth,
3224 p_asg_act,
3225 l_pay_left);
3226 else
3227 --
3228 -- OK This must be multiple assignments, hence
3229 -- just process the run type methods
3230 --
3231 process_third_party(chdrec.assignment_action_id,
3232 p_effective_date,
3233 p_asg_act);
3234 process_run_types(p_override_meth,
3235 chdrec.assignment_action_id,
3236 p_def_bal_id,
3237 p_effective_date,
3238 p_asg_act,
3239 l_pay_left);
3240 end if;
3241 --
3242 end loop;
3243 --
3244 -- Each child action should have dealt with its own run types
3245 -- however, if there were no children we need to deal with them.
3246 if (child_processed = FALSE) then
3247 process_third_party(p_asg_act,
3248 p_effective_date,
3249 null);
3250 process_run_types(p_override_meth,
3251 p_asg_act,
3252 p_def_bal_id,
3253 p_effective_date,
3254 null,
3255 l_pay_left);
3256 end if;
3257 --
3258 process_normal_payments(p_override_meth,
3259 p_asg_act,
3260 p_asg_id,
3261 p_def_bal_id,
3262 p_effective_date,
3263 l_pay_left);
3264
3265 hr_utility.trace('Exit do_prepayment');
3266 --
3267 end do_prepayment;
3268 --
3269 --------------------------------- get_trx_date ------------------------------
3270 /*
3271 NAME
3272 get_trx_date - derives the payment date.
3273 DESCRIPTION
3274 Returns the payment date (e.g. cheque date).
3275 NOTES
3276 <none>
3277 */
3278 function get_trx_date
3279 (
3280 p_business_group_id in number,
3281 p_payroll_action_id in number,
3282 p_assignment_action_id in number default null,
3283 p_payroll_id in number default null,
3284 p_consolidation_set_id in number default null,
3285 p_org_payment_method_id in number default null,
3286 p_effective_date in date default null,
3287 p_date_earned in date default null,
3288 p_override_date in date default null,
3289 p_pre_payment_id in number default null
3290 ) return date is
3291 --
3292 l_trx_date date;
3293 statem varchar2(1000);
3294 rows_processed integer;
3295 l_leg_code per_business_groups_perf.legislation_code%type;
3296 l_cur_id number;
3297 --
3298 begin
3299 --
3300 l_trx_date := to_date(null);
3301 --
3302 if (p_business_group_id = p_bg_id) then
3303 l_leg_code := p_leg_code;
3304 l_cur_id := p_cur_id;
3305 else
3306 select legislation_code
3307 into l_leg_code
3308 from per_business_groups_perf
3309 where business_group_id = p_business_group_id;
3310 --
3311 if l_leg_code = p_leg_code then
3312 --
3313 p_bg_id := p_business_group_id;
3314 l_cur_id := p_cur_id;
3315 --
3316 else
3317 --
3318 p_leg_code := l_leg_code;
3319 p_bg_id := p_business_group_id;
3320 --
3321 if dbms_sql.is_open(p_cur_id) then
3322 dbms_sql.close_cursor(p_cur_id);
3323 end if;
3324 --
3325 begin
3326 --
3327 statem := 'BEGIN
3328 :trx_date := pay_'||lower(l_leg_code)||'_payment_pkg.get_trx_date(
3329 :business_group_id,
3330 :payroll_action_id,
3331 :assignment_action_id,
3332 :payroll_id,
3333 :consolidation_set_id,
3334 :org_payment_method_id,
3335 :effective_date,
3336 :date_earned,
3337 :override_date,
3338 :pre_payment_id); END;';
3339 --
3340 l_cur_id := dbms_sql.open_cursor;
3341 --
3342 dbms_sql.parse(l_cur_id,
3343 statem,
3344 dbms_sql.v7);
3345 --
3346 -- First attempt to retrieve the value.
3347 --
3348 dbms_sql.bind_variable(l_cur_id, 'trx_date', l_trx_date);
3349 dbms_sql.bind_variable(l_cur_id, 'business_group_id', p_business_group_id);
3350 dbms_sql.bind_variable(l_cur_id, 'payroll_action_id', p_payroll_action_id);
3351 dbms_sql.bind_variable(l_cur_id, 'assignment_action_id', p_assignment_action_id);
3352 dbms_sql.bind_variable(l_cur_id, 'payroll_id', p_payroll_id);
3353 dbms_sql.bind_variable(l_cur_id, 'consolidation_set_id', p_consolidation_set_id);
3354 dbms_sql.bind_variable(l_cur_id, 'org_payment_method_id', p_org_payment_method_id);
3355 dbms_sql.bind_variable(l_cur_id, 'effective_date', p_effective_date);
3356 dbms_sql.bind_variable(l_cur_id, 'date_earned', p_date_earned);
3357 dbms_sql.bind_variable(l_cur_id, 'override_date', p_override_date);
3358 dbms_sql.bind_variable(l_cur_id, 'pre_payment_id', p_pre_payment_id);
3359 --
3360 rows_processed := dbms_sql.execute(l_cur_id);
3361 --
3362 dbms_sql.variable_value(l_cur_id, 'trx_date', l_trx_date);
3363 --
3364 --
3365 exception
3366 --
3367 when others then
3368 --
3369 if dbms_sql.is_open(l_cur_id) then
3370 dbms_sql.close_cursor(l_cur_id);
3371 end if;
3372 --
3373 l_cur_id := NULL;
3374 --
3375 end;
3376 --
3377 end if;
3378 --
3379 p_cur_id := l_cur_id;
3380 --
3381 end if;
3382 --
3383 if l_trx_date is not null then
3384 return l_trx_date;
3385 end if;
3386 --
3387 if l_cur_id is not null then
3388 --
3389 dbms_sql.bind_variable(l_cur_id, 'trx_date', l_trx_date);
3390 dbms_sql.bind_variable(l_cur_id, 'business_group_id', p_business_group_id);
3391 dbms_sql.bind_variable(l_cur_id, 'payroll_action_id', p_payroll_action_id);
3392 dbms_sql.bind_variable(l_cur_id, 'assignment_action_id', p_assignment_action_id);
3393 dbms_sql.bind_variable(l_cur_id, 'payroll_id', p_payroll_id);
3394 dbms_sql.bind_variable(l_cur_id, 'consolidation_set_id', p_consolidation_set_id);
3395 dbms_sql.bind_variable(l_cur_id, 'org_payment_method_id', p_org_payment_method_id);
3396 dbms_sql.bind_variable(l_cur_id, 'effective_date', p_effective_date);
3397 dbms_sql.bind_variable(l_cur_id, 'date_earned', p_date_earned);
3398 dbms_sql.bind_variable(l_cur_id, 'override_date', p_override_date);
3399 dbms_sql.bind_variable(l_cur_id, 'pre_payment_id', p_pre_payment_id);
3400 --
3401 rows_processed := dbms_sql.execute(l_cur_id);
3402 --
3403 dbms_sql.variable_value(l_cur_id, 'trx_date', l_trx_date);
3404 --
3405 else
3406 --
3407 l_trx_date := nvl(nvl(p_override_date,p_date_earned),p_effective_date);
3408 --
3409 end if;
3410 --
3411 --
3412 return l_trx_date;
3413 --
3414 end get_trx_date;
3415 --
3416 procedure process_asg_rollup(p_assignment_action in number)
3417 is
3418 --
3419 type t_pre_payment_id is table of pay_pre_payments.pre_payment_id%type
3420 index by binary_integer;
3421 type t_assignment_action_id is table of pay_pre_payments.assignment_action_id%type
3422 index by binary_integer;
3423 type t_payroll_action_id is table of pay_payroll_actions.payroll_action_id%type
3424 index by binary_integer;
3425 --
3426 f_pre_payment_id t_pre_payment_id;
3427 f_assignment_action_id t_assignment_action_id;
3428 f_payroll_action_id t_payroll_action_id;
3429 --
3430 cursor get_payment_details(p_asg_act number
3431 )
3432 is
3433 select
3434 ppp.pre_payment_id,
3435 paa_pru.assignment_action_id,
3436 ppa_pru.payroll_action_id
3437 from pay_payroll_actions ppa_pru,
3438 pay_payroll_actions ppa_pre,
3439 pay_assignment_actions paa_pru,
3440 pay_assignment_actions paa_pre,
3441 pay_action_interlocks pai,
3442 pay_pre_payments ppp
3443 where
3444 paa_pru.assignment_action_id = p_asg_act
3445 and ppa_pru.payroll_action_id = paa_pru.payroll_action_id
3446 and paa_pru.assignment_action_id = pai.locking_action_id
3447 and pai.locked_action_id = paa_pre.assignment_action_id
3448 and paa_pre.assignment_action_id = ppp.assignment_action_id
3449 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
3450 and ppp.organization_id is not null
3451 and nvl(ppp.effective_date , ppa_pre.effective_date)
3452 <= ppa_pru.effective_date
3453 and not exists (select ''
3454 from pay_contributing_payments pcp
3455 where ppp.pre_payment_id = pcp.contributing_pre_payment_id)
3456 order by ppp.org_payment_method_id, ppp.payees_org_payment_method_id
3457 for update of ppp.pre_payment_id;
3458 --
3459 begin
3460 --
3461 open get_payment_details(p_assignment_action);
3462 loop
3463 fetch get_payment_details
3464 bulk collect into f_pre_payment_id,
3465 f_assignment_action_id,
3466 f_payroll_action_id
3467 limit 1000;
3468 --
3469 forall i in 1..f_pre_payment_id.count
3470 insert into pay_contributing_payments
3471 (assignment_action_id,
3472 payroll_action_id,
3473 contributing_pre_payment_id
3474 )
3475 values (
3476 f_assignment_action_id(i),
3477 f_payroll_action_id(i),
3478 f_pre_payment_id(i)
3479 );
3480 --
3481 exit when get_payment_details%notfound;
3482 --
3483 end loop;
3484 --
3485 close get_payment_details;
3486 --
3487 end process_asg_rollup;
3488 --
3489 procedure process_pact_rollup(p_pactid in number)
3490 is
3491 --
3492 cursor get_totals(p_pactid in number)
3493 is
3494 select ppp.org_payment_method_id,
3495 ppp.payees_org_payment_method_id,
3496 ppp.organization_id,
3497 sum(nvl(base_currency_value, 0)) base_currency_value,
3498 sum(nvl(value,0)) value
3499 from pay_contributing_payments pcp,
3500 pay_pre_payments ppp
3501 where pcp.payroll_action_id = p_pactid
3502 and pcp.contributing_pre_payment_id = ppp.pre_payment_id
3503 group by ppp.org_payment_method_id,
3504 ppp.payees_org_payment_method_id,
3505 ppp.organization_id;
3506 --
3507 l_pre_pay_id pay_pre_payments.pre_payment_id%type;
3508 --
3509 begin
3510 --
3511 for totrec in get_totals(p_pactid) loop
3512 --
3513 select pay_pre_payments_s.nextval
3514 into l_pre_pay_id
3515 from dual;
3516 --
3517 insert into pay_pre_payments
3518 (pre_payment_id,
3519 org_payment_method_id,
3520 value,
3521 base_currency_value,
3522 organization_id,
3523 payees_org_payment_method_id,
3524 payroll_action_id)
3525 values
3526 (l_pre_pay_id,
3527 totrec.org_payment_method_id,
3528 totrec.value,
3529 totrec.base_currency_value,
3530 totrec.organization_id,
3531 totrec.payees_org_payment_method_id,
3532 p_pactid);
3533 --
3534 update pay_contributing_payments pcp
3535 set pre_payment_id = l_pre_pay_id
3536 where pcp.payroll_action_id = p_pactid
3537 and exists (select ''
3538 from pay_pre_payments ppp
3539 where ppp.pre_payment_id = pcp.contributing_pre_payment_id
3540 and ppp.org_payment_method_id = totrec.org_payment_method_id
3541 and ppp.payees_org_payment_method_id = totrec.payees_org_payment_method_id
3542 and ppp.organization_id = totrec.organization_id
3543 );
3544 --
3545 end loop;
3546 --
3547 end process_pact_rollup;
3548
3549 --
3550 end hr_pre_pay;
[Home] [Help]