[Home] [Help]
PACKAGE BODY: APPS.HR_PPVOL
Source
1 package body hr_ppvol as
2 /* $Header: pyvpymnt.pkb 120.0 2006/03/06 04:29:38 pgongada noship $ */
3 --
4 /*
5 -- Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6 /*
7 PRODUCT
8 Oracle*Payroll
9 --
10 NAME
11 pyvpymnt.pkb
12 MODIFIED (DD-MON-YYYY)
13 mwcallag 29-SEP-1993 - Cash analysis parameter removed, following change
14 to pay_org_payment_methods_f in CASE.
15 amills 06-DEC-1995 - Changed all date referencing to DDMMYYYY format
16 so that it can be translated.
17 WMcVeagh 19-MAR-1998 - Change create or replace 'as' not 'is'
18 tbattoo 09-APR-1999 - changed pay_exchange_rates to use gl_daily_rates
19 alogue 04-JUN-2001 - amend reference to gl_daily_rates to
20 utilise an index.
21 mreid 03-OCT-2002 - Ensured call to create_balance_type passed params
22 pgongada 25-NOV-2005 - Changed the ins_pmu function. Added dbdrv lines.
23 */
24 --
25 --------------------------- ins_opm -----------------------------------
26 /*
27 NAME
28 ins_opm
29 DESCRIPTION
30 Function to insert an Org_payment_method.
31 NOTES
32 Called using...
33 --
34 --opm_id := ins_opm(to_date(start_date,'DD-MM-YYYY'),
35 -- to_date(end_date,'DD-MM-YYYY'),
36 -- bg_id,
37 -- exa_id,
38 -- currency,
39 -- payment_type,
40 -- name);
41 --
42 */
43 --
44 function ins_opm(effective_start_date in date,
45 effective_end_date in date,
46 p_business_group_id in number,
47 external_account_id in number,
48 currency_code in varchar2,
49 payment_type_id in number,
50 name in varchar2) return number is
51 method_id number(16);
52 defined_balance number(16);
53 legislation_code varchar2(30);
54 begin
55 --
56 -- Get the legislation code for the business group
57 --
58 select bg.legislation_code
59 into legislation_code
60 from per_business_groups bg
61 where bg.business_group_id = p_business_group_id;
62 --
63 -- Get the defined balance for payments
64 --
65 defined_balance := hr_payments.gen_balance(leg_code => legislation_code);
66 --
67 if defined_balance = 0 then
68 --
69 hr_utility.set_message(801,'HR_6390_PAYM_ILLEGAL_BALANCE');
70 return 0;
71 --
72 end if;
73 --
74 -- Get the ID of the new method
75 --
76 hr_utility.set_location('HR_PPVOL.ins_opm',1);
77 --
78 select pay_org_payment_methods_s.nextval
79 into method_id
80 from dual;
81 --
82 -- Now do the insert
83 --
84 hr_utility.set_location('HR_PPVOL.ins_opm',2);
85 --
86 insert into pay_org_payment_methods_f
87 (ORG_PAYMENT_METHOD_ID,
88 EFFECTIVE_START_DATE,
89 EFFECTIVE_END_DATE,
90 BUSINESS_GROUP_ID,
91 EXTERNAL_ACCOUNT_ID,
92 CURRENCY_CODE,
93 PAYMENT_TYPE_ID,
94 ORG_PAYMENT_METHOD_NAME,
95 DEFINED_BALANCE_ID)
96 values
97 (method_id,
98 effective_start_date,
99 effective_end_date,
100 p_business_group_id,
101 external_account_id,
102 currency_code,
103 payment_type_id,
104 name,
105 defined_balance);
106 --
107 insert into pay_org_payment_methods_f_tl
108 (ORG_PAYMENT_METHOD_ID,
109 ORG_PAYMENT_METHOD_NAME,
110 LANGUAGE,
111 SOURCE_LANG)
112 values
113 (method_id,
114 name,
115 userenv('LANG'),
116 userenv('LANG'));
117 --
118 return method_id;
119 --
120 end ins_opm;
121 --
122 --
123 --------------------------- ins_exa -----------------------------------
124 /*
125 NAME
126 ins_exa
127 DESCRIPTION
128 insert an external account
129 NOTES
130 Called as...
131 --
132 account_id := ins_exa(territory);
133 --
134 --
135 */
136 --
137 function ins_exa(territory_code in varchar2) return number is
138 account_id number(16);
139 begin
140 --
141 -- First get the UID
142 --
143 select pay_external_accounts_s.nextval
144 into account_id
145 from dual;
146 --
147 -- Now insert the new row
148 --
149 insert into pay_external_accounts(
150 external_account_id,
151 territory_code,
152 id_flex_num,
153 summary_flag,
154 enabled_flag)
155 values(
156 account_id,
157 territory_code,
158 1,
159 'N',
160 'Y');
161 --
162 return account_id;
163 --
164 end ins_exa;
165 --
166 --------------------------- ins_pmu -----------------------------------
167 /*
168 NAME
169 ins_pmu
170 DESCRIPTION
171 Insert pay_method_usages
172 NOTES
173 Called as
174 --
175 pmu := ins_pmu(to_date(start,'DD-MM-YYYY'),
176 to_date(end,'DD-MM-YYYY'),
177 payroll_id,
178 opm_id);
179 --
180 */
181 --
182 function ins_pmu(start_date in date,
183 end_date in date,
184 payroll in number,
185 payment_method in number) return number is
186
187
188 cursor csr_payroll_opmu is
189 select opmu.effective_start_date,
190 opmu.effective_end_date,
191 org_pay_method_usage_id
192 from pay_org_pay_method_usages_f opmu
193 where opmu.payroll_id = payroll
194 and opmu.org_payment_method_id = payment_method
195 and opmu.effective_start_date <= end_date
196 and opmu.effective_end_date >= start_date
197 order by opmu.effective_start_date
198 for update;
199
200 pmu_id number(16);
201 existing boolean;
202 v_insert_record boolean := TRUE;
203 begin
204 --
205 -- First check the pmu isn't already there.
206 --
207
208 existing := false;
209 --
210 --Commented the below code because it's not creating valid payment methods
211 --correctly.
212 /*begin
213 select org_pay_method_usage_id
214 into pmu_id
215 from pay_org_pay_method_usages_f
216 where payroll_id = payroll
217 and org_payment_method_id = payment_method
218 and effective_start_date < end_date
219 and effective_end_date > start_date;
220 --
221 exception
222 --
223 when no_data_found then
224 --
225 existing := true;
226 --
227 end;
228 --
229 if existing = false then
230 --
231 select pay_org_pay_method_usages_s.nextval
232 into pmu_id
233 from sys.dual;
234 --
235 insert into pay_org_pay_method_usages_f
236 (org_pay_method_usage_id,
237 effective_start_date,
238 effective_end_date,
239 org_payment_method_id,
240 payroll_id)
241 values
242 (pmu_id,
243 start_date,
244 end_date,
245 payment_method,
246 payroll);
247 --
248 end if;
249 --
250 return pmu_id;*/
251
252 --
253 --This code works fine for all the cases.
254 for v_opmu_rec in csr_payroll_opmu loop
255 --
256 -- An existing opmu already represents the default so do nothing.
257 -- current opmu |------------------------------------|
258 -- required opmu |----------------------------|
259 if v_opmu_rec.effective_start_date <= start_date and
260 v_opmu_rec.effective_end_date >= end_date then
261 --
262 v_insert_record := FALSE;
263 pmu_id := v_opmu_rec.org_pay_method_usage_id;
264 --
265 -- opmu overlaps with start of required opmu so need to shorten it ie.
266 -- current opmu |--------|
267 -- required opmu . |----------------------------|
268 -- . . .
269 -- adjust opmu |--|. .
270 -- insert new opmu |----------------------------| (see below)
271 elsif v_opmu_rec.effective_start_date < start_date then
272 --
273 update pay_org_pay_method_usages_f opmu
274 set opmu.effective_end_date = start_date - 1
275 where current of csr_payroll_opmu;
276 --
277 -- opmu overlaps with end of required opmu so need to shorten it ie.
278 -- current opmu |--------|
279 -- required opmu |----------------------------| .
280 -- . . .
281 -- adjust opmu . .|----|
282 -- insert new opmu |----------------------------| (see below)
283 elsif v_opmu_rec.effective_end_date > end_date then
284 --
285 update pay_org_pay_method_usages_f opmu
286 set opmu.effective_start_date = end_date + 1
287 where current of csr_payroll_opmu;
288 --
289 -- opmu overlaps within required opmu so need to remove it ie.
290 -- current opmu |----)
291 -- required opmu |----------------------------|
292 -- . .
293 -- remove opmu . .
294 -- insert new opmu |----------------------------| (see below)
295 else
296 --
297 delete from pay_org_pay_method_usages_f
298 where current of csr_payroll_opmu;
299 --
300 end if;
301
302 end loop;
303 --
304 if v_insert_record then
305 --
306 select pay_org_pay_method_usages_s.nextval into pmu_id from sys.dual;
307 -- Create opmu to represent the default payment method selected for the
308 -- payroll.
309 insert into pay_org_pay_method_usages_f
310 (org_pay_method_usage_id,
311 effective_start_date,
312 effective_end_date,
313 payroll_id,
314 org_payment_method_id,
315 last_update_date,
316 last_updated_by,
317 last_update_login,
318 created_by,
319 creation_date)
320 values
321 (pmu_id,
322 start_date,
323 end_date,
324 payroll,
325 payment_method,
326 trunc(sysdate),
327 0,
328 0,
329 0,
330 trunc(sysdate));
331 --
332 end if;
333 return pmu_id;
334 end ins_pmu;
335 --
336 --------------------------- ins_ppm -----------------------------------
337 /*
338 NAME
339 ins_ppm
340 DESCRIPTION
341 insert personal payment method
342 NOTES
343 Call as...
344 --
345 ppm_id := ins_ppm(to_date(start,'DD-MM-YYYY'),
346 to_date(end,'DD-MM-YYYY'),
347 bg_id,
348 account,
349 assignment,
350 opm_id,
351 amount,
352 percentage,
353 priority);
354 --
355 */
356 --
357 function ins_ppm(start_date in date,
358 end_date in date,
359 business_group_id in number,
360 external_account in number,
361 assignment_id in number,
362 opm_id in number,
363 amount in number,
364 percentage in number,
365 priority in number) return number is
366 ppm_id number(16);
367 begin
368 --
369 select pay_personal_payment_methods_s.nextval
370 into ppm_id
371 from dual;
372 --
373 insert into pay_personal_payment_methods_f(
374 personal_payment_method_id,
375 effective_start_date,
376 effective_end_date,
377 business_group_id,
378 external_account_id,
379 assignment_id,
380 org_payment_method_id,
381 amount,
382 percentage,
383 priority)
384 values(
385 ppm_id,
386 start_date,
387 end_date,
388 business_group_id,
389 external_account,
390 assignment_id,
391 opm_id,
392 amount,
393 percentage,
394 priority);
395 --
396 return ppm_id;
397 --
398 end ins_ppm;
399 --
400 --------------------------- ins_exr -----------------------------------
401 /*
402 NAME
403 ins_exr
404 DESCRIPTION
405 insert exchaneg rates to and from two currencies
406 NOTES
407 --
408 */
409 --
410 function ins_exr(start_date in date,
411 end_date in date,
412 base_currency in varchar2,
413 other_currency in varchar2,
414 rate_type in varchar2 default 'Payroll',
415 rate in number,
416 inverse_rate in number) return number is
417 exchange_rate number(16);
418 begin
419
420 insert into gl_daily_rates_interface
421 (from_currency,
422 to_currency,
423 from_conversion_date,
424 to_conversion_date,
425 user_conversion_type,
426 conversion_rate,
427 inverse_conversion_rate,
428 mode_flag)
429 VALUES (
430 base_currency,
431 other_currency,
432 start_date ,
433 end_date,
434 rate_type,
435 rate,
436 inverse_rate,
437 'I');
438
439 --
440 return exchange_rate;
441 --
442 end ins_exr;
443 --
444 --------------------------- ins_ppa -----------------------------------
445 /*
446 NAME
447 ins_ppa
448 DESCRIPTION
449 insert payroll action
450 NOTES
451 Fill in other params as required, but do not default!
452 */
453 function ins_ppa(action_type varchar2,
454 business_group number,
455 consolidation_set number,
456 payroll number,
457 pop_status varchar2,
458 action_status varchar2,
459 action_date date,
460 parameters varchar2) return number is
461 action_id number(16);
462 begin
463 --
464 select pay_payroll_actions_s.nextval
465 into action_id
466 from dual;
467 --
468 -- Now do the insert
469 --
470 insert into pay_payroll_actions(
471 payroll_action_id,
472 action_type,
473 business_group_id,
474 consolidation_set_id,
475 payroll_id,
476 action_population_status,
477 action_status,
478 effective_date,
479 legislative_parameters)
480 values(
481 action_id,
482 action_type,
483 business_group,
484 consolidation_set,
485 payroll,
486 pop_status,
487 action_status,
488 action_date,
489 parameters);
490 --
491 return action_id;
492 --
493 end ins_ppa;
494 --
495 --------------------------- ins_paa -----------------------------------
496 /*
497 NAME
498 ins_paa
499 DESCRIPTION
500 insert assignment_actions
501 NOTES
502 --
503 */
504 --
505 function ins_paa(assignment number,
506 payroll_action number,
507 status varchar2 default 'U',
508 chunk number default null,
509 sequence number default null,
510 pre_payment number default null,
511 serial_no varchar2 default null) return number is
512 action_id number(16);
513 begin
514 --
515 select pay_assignment_actions_s.nextval
516 into action_id
517 from dual;
518 --
519 insert into pay_assignment_actions(
520 assignment_action_id,
521 assignment_id,
522 payroll_action_id,
523 action_status,
524 chunk_number,
525 action_sequence,
526 pre_payment_id,
527 serial_number)
528 values(
529 action_id,
530 assignment,
531 payroll_action,
532 status,
533 chunk,
534 sequence,
535 pre_payment,
536 serial_no);
537 --
538 return action_id;
539 --
540 end ins_paa;
541 --
542 --------------------------- ins_payroll -----------------------------------
543 /*
544 NAME
545 ins_payroll
546 DESCRIPTION
547 insert a payroll
548 NOTES
549 Note that pay_db_pay_setup.create_payroll should be used. This is just a
550 temporary measure
551 */
552 FUNCTION ins_payroll(payroll_name varchar2,
553 number_of_years number,
554 period_type varchar2,
555 first_period_end_date date,
556 dflt_payment_method number,
557 pay_date_offset number default 0,
558 direct_deposit_date_offset number default 0,
559 pay_advice_date_offset number default 0,
560 cut_off_date_offset number default 0,
561 consolidation_set number,
562 negative_pay_allowed_flag varchar2 default 'N',
563 organization number default NULL,
564 midpoint_offset number default 0,
565 workload_shifting_level varchar2 default 'N',
566 effective_start_date date,
567 effective_end_date date,
568 business_group number)
569 RETURN number is
570
571 payroll_id number(16);
572 begin
573 --
574 hr_utility.set_location('HR_PPVOL.ins_payroll',1);
575 --
576 select pay_payrolls_s.nextval
577 into payroll_id
578 from dual;
579 --
580 hr_utility.set_location('HR_PPVOL.ins_payroll',2);
581 --
582 insert into pay_payrolls_f
583 (PAYROLL_ID,
584 EFFECTIVE_START_DATE,
585 EFFECTIVE_END_DATE,
586 DEFAULT_PAYMENT_METHOD_ID,
587 BUSINESS_GROUP_ID,
588 CONSOLIDATION_SET_ID,
589 ORGANIZATION_ID,
590 PERIOD_TYPE,
591 CUT_OFF_DATE_OFFSET,
592 DIRECT_DEPOSIT_DATE_OFFSET,
593 FIRST_PERIOD_END_DATE,
594 MIDPOINT_OFFSET,
595 NEGATIVE_PAY_ALLOWED_FLAG,
596 NUMBER_OF_YEARS,
597 PAY_ADVICE_DATE_OFFSET,
598 PAY_DATE_OFFSET,
599 PAYROLL_NAME,
600 WORKLOAD_SHIFTING_LEVEL)
601 values
602 (payroll_id,
603 effective_start_date,
604 effective_end_date,
605 dflt_payment_method,
606 business_group,
607 consolidation_set,
608 nvl(organization,business_group),
609 period_type,
610 cut_off_date_offset,
611 direct_deposit_date_offset,
612 first_period_end_date,
613 midpoint_offset,
614 negative_pay_allowed_flag,
615 number_of_years,
616 pay_advice_date_offset,
617 pay_date_offset,
618 payroll_name,
619 workload_shifting_level);
620 --
621 return payroll_id;
622 --
623 end ins_payroll;
624 --
625 --------------------------- testpay -----------------------------------
626 /*
627 NAME
628 testpay
629 DESCRIPTION
630 Set up data for payments testing.
631 NOTES
632 --
633 */
634 --
635 procedure testpay(p_business_group in varchar2) is
636 --
637 new_bg boolean;
638 input_bg_name varchar2(80);
639 business_group number;
640 name_prefix varchar2(20);
641 --
642 account1 number;
643 account2 number;
644 --
645 cash_type number;
646 check_type number;
647 cheque_type number;
648 nacs_type number;
649 --
650 pay_type number;
651 pay_dimension varchar2(80);
652 --
653 cash_opm1 number;
654 cash_opm2 number;
655 check_opm1 number;
656 check_opm2 number;
657 cheque_opm number; -- NB This is paid in UKL not NVS
658 nacs_opm1 number;
659 nacs_opm2 number;
660 --
661 cset number;
662 payroll1 number;
663 payroll2 number;
664 exchange_rate number;
665 --
666 person number;
667 assignment1 number;
668 assignment2 number;
669 assignment3 number;
670 assignment4 number;
671 assignment5 number;
672 assignment6 number;
673 --
674 ppm1 number;
675 ppm2 number;
676 ppm3 number;
677 --
678 payroll_action number;
679 action1 number;
680 action2 number;
681 action3 number;
682 action4 number;
683 action5 number;
684 action6 number;
685 --
686 err_msg varchar2(80);
687 --
688 dummy number;
689 column_id number;
690 row_id number;
691 table_id number;
692 rate_type_found boolean := TRUE;
693
694
695 begin
696 --
697 input_bg_name := p_business_group;
698 --
699 -- Set up external accounts. Two will suffice.
700 -- NB No flex info initially. Only here to keep DB correct.
701 --
702 account1 := ins_exa('NV');
703 --
704 account2 := ins_exa('NV');
705 --
706 -- Create a new business group to test in if named one doesn't exist.
707 --
708 new_bg := false;
709 --
710 begin
711 --
712 select business_group_id
713 into business_group
714 from per_organization_units
715 where name = input_bg_name;
716 --
717 exception
718 --
719 when no_data_found then
720 --
721 new_bg := true;
722 --
723 end;
724 --
725 if new_bg = true then
726 --
727 business_group := per_db_per_setup.create_business_group(p_group => 'Y',
728 p_name => input_bg_name,
729 p_short_name => 'PAYM',
730 p_date_from => to_date('01-01-1990',
731 'DD-MM-YYYY'),
732 p_date_to => to_date('31-12-4712',
733 'DD-MM-YYYY'),
734 p_legislation_code => 'NV',
735 p_currency_code => 'NVS');
736 --
737 end if;
738 --
739 -- set up exchange rates in user tables for this business group
740 --
741
742 select c.user_column_id,
743 r.user_row_id,
744 t.user_table_id
745 into column_id,row_id,table_id
746 from pay_user_columns C
747 ,pay_user_rows_f R
748 ,pay_user_tables T
749 where t.user_table_name='EXCHANGE_RATE_TYPES'
750 and t.USER_ROW_TITLE='Processing Type'
751 and r.user_table_id=t.user_table_id
752 and c.user_table_id=t.user_table_id
753 and c.user_column_name='Conversion Rate Type'
754 and r.row_low_range_or_name='PAY';
755 --
756 --
757 insert into pay_user_column_instances_f (
758 user_column_instance_id,
759 effective_start_date,
760 effective_end_date,
761 user_row_id,
762 user_column_id,
763 business_group_id,
764 value)
765 values (pay_user_column_instances_s.nextval,
766 to_date('01-01-1990', 'DD-MM-YYYY'),
767 to_date('31/12/4712','DD/MM/YYYY'),
768 row_id,
769 column_id,
770 business_group,
771 'Payroll');
772
773 --
774 -- All names prefixed by BGID for uniquness
775 --
776 name_prefix := 'paym ' || to_char(business_group) || ' ';
777 --
778 -- Now generate some OPM's (two of each type)
779 -- First set up the payments balance. Firs get the dimension.
780 --
781 select dimension_name
782 into pay_dimension
783 from pay_balance_dimensions
784 where payments_flag = 'Y'
785 and legislation_code is NULL;
786 --
787 -- Now set up a balance type
788 --
789 pay_type := pay_db_pay_setup.create_balance_type(
790 p_balance_name => name_prefix || 'Payments',
791 p_uom => 'Money',
792 p_currency_code => 'NVS',
793 p_reporting_name => 'Payments',
794 p_business_group_name => input_bg_name,
795 p_legislation_code => 'NV');
796 --
797 -- Now insert the defined balance
798 --
799 pay_db_pay_setup.create_defined_balance(
800 p_balance_name => name_prefix || 'Payments',
801 p_balance_dimension => pay_dimension,
802 p_frce_ltst_balance_flag => 'Y',
803 p_business_group_name => input_bg_name,
804 p_legislation_code => 'NV');
805 --
806 -- First do cash methods
807 --
808 select payment_type_id
809 into cash_type
810 from pay_payment_types
811 where payment_type_name = 'Cash';
812 --
813 -- Now insert the methods
814 --
815 cash_opm1 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
816 to_date('31-12-4712','DD_MM-YYYY'),
817 business_group,
818 account1,
819 'NVS',
820 cash_type,
821 name_prefix || 'Cash OPM 1');
822 --
823 cash_opm2 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
824 to_date('31-12-4712','DD_MM-YYYY'),
825 business_group,
826 account2,
827 'NVS',
828 cash_type,
829 name_prefix || 'Cash OPM 2');
830 --
831 -- Now do check methods
832 --
833 select payment_type_id
834 into check_type
835 from pay_payment_types
836 where payment_type_name = 'Check';
837 --
838 -- Now insert the methods
839 --
840 check_opm1 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
841 to_date('31-12-4712','DD_MM-YYYY'),
842 business_group,
843 account1,
844 'NVS',
845 check_type,
846 name_prefix || 'Check OPM 1');
847 --
848 check_opm2 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
849 to_date('31-12-4712','DD_MM-YYYY'),
850 business_group,
851 account2,
852 'NVS',
853 check_type,
854 name_prefix || 'Check OPM 2');
855 --
856 -- Now do NACS methods
857 --
858 select payment_type_id
859 into nacs_type
860 from pay_payment_types
861 where payment_type_name = 'NACS';
862 --
863 -- Now insert the methods
864 --
865 nacs_opm1 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
866 to_date('31-12-4712','DD_MM-YYYY'),
867 business_group,
868 account1,
869 'NVS',
870 nacs_type,
871 name_prefix || 'NACS OPM 1');
872 --
873 nacs_opm2 := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
874 to_date('31-12-4712','DD_MM-YYYY'),
875 business_group,
876 account2,
877 'NVS',
878 nacs_type,
879 name_prefix || 'NACS OPM 2');
880 --
881 -- Now put in a cheque method paid in GBP
882 --
883 select payment_type_id
884 into cheque_type
885 from pay_payment_types
886 where payment_type_name = 'Cheque';
887 --
888 -- Now insert the methods
889 --
890 cheque_opm := hr_ppvol.ins_opm(to_date('01-01-1990','DD-MM-YYYY'),
891 to_date('31-12-4712','DD_MM-YYYY'),
892 business_group,
893 account1,
894 'GBP',
895 cheque_type,
896 name_prefix || 'Cheque OPM 1');
897 --
898 -- Now a couple of payrolls. (First a consolidation set)
899 --
900 cset := pay_db_pay_setup.create_consolidation_set(
901 p_consolidation_set_name => name_prefix || 'Payments set',
902 p_business_group_name => input_bg_name);
903 --
904 payroll1 := hr_ppvol.ins_payroll(
905 payroll_name=>name_prefix || 'Paym Test Payroll',
906 number_of_years=>5,
907 period_type=>'Calendar Month',
908 first_period_end_date=>to_date('31-01-1990',
909 'DD-MM-YYYY'),
910 dflt_payment_method=>cash_opm1,
911 consolidation_set=>cset,
912 effective_start_date=>to_date('01-01-1990',
913 'DD-MM-YYYY'),
914 effective_end_date=>to_date('31-12-4712',
915 'DD-MM-YYYY'),
916 business_group=>business_group);
917 --
918 payroll2 := hr_ppvol.ins_payroll(
919 payroll_name=>name_prefix || 'Paym Dummy Payroll',
920 number_of_years=>5,
921 period_type=>'Calendar Month',
922 first_period_end_date=>to_date('31-01-1990',
923 'DD-MM-YYYY'),
924 dflt_payment_method=>cash_opm2,
925 consolidation_set=>cset,
926 effective_start_date=>to_date('01-01-1990',
927 'DD-MM-YYYY'),
928 effective_end_date=>to_date('31-12-4712',
929 'DD-MM-YYYY'),
930 business_group=>business_group);
931 --
932 -- Insert some exchange rates
933 --
934
935 begin
936 select count(*)
937 into dummy
938 from gl_daily_rates
939 where conversion_type = 'Payroll'
940 and from_currency = 'NVS'
941 and to_currency = 'GBP';
942
943 exception
944 when no_data_found then
945 rate_type_found := FALSE;
946 end;
947
948 if (rate_type_found = FALSE) then
949 insert into gl_daily_conversion_types
950 (CONVERSION_TYPE,
951 USER_CONVERSION_TYPE,
952 LAST_UPDATE_DATE,
953 LAST_UPDATED_BY)
954 values (
955 'Payroll',
956 'Payroll',
957 to_date('01-01-1990','DD-MM-YYYY'),
958 0);
959 end if;
960
961 exchange_rate := ins_exr(to_date('01-01-1990','DD-MM-YYYY'),
962 to_date('31-12-1990','DD-MM-YYYY'),
963 'NVS',
964 'GBP',
965 'Payroll',
966 2,
967 0.5);
968 exchange_rate := ins_exr(to_date('01-01-1991','DD-MM-YYYY'),
969 to_date('31-12-1991','DD-MM-YYYY'),
970 'NVS',
971 'GBP',
972 'Payroll',
973 2,
974 0.5);
975 exchange_rate := ins_exr(to_date('01-01-1992','DD-MM-YYYY'),
976 to_date('31-12-1992','DD-MM-YYYY'),
977 'NVS',
978 'GBP',
979 'Payroll',
980 2,
981 0.5);
982 exchange_rate := ins_exr(to_date('01-01-1993','DD-MM-YYYY'),
983 to_date('31-12-1993','DD-MM-YYYY'),
984 'NVS',
985 'GBP',
986 'Payroll',
987 2,
988 0.5);
989 exchange_rate := ins_exr(to_date('01-01-1994','DD-MM-YYYY'),
990 to_date('31-12-1994','DD-MM-YYYY'),
991 'NVS',
992 'GBP',
993 'Payroll',
994 2,
995 0.5);
996 exchange_rate := ins_exr(to_date('01-01-1995','DD-MM-YYYY'),
997 to_date('31-12-1995','DD-MM-YYYY'),
998 'NVS',
999 'GBP',
1000 'Payroll',
1001 2,
1002 0.5);
1003
1004 --
1005 --
1006 -- Now add people... For each employee created, add in some personal
1007 -- payment methods. NB Have to find assignment number via a select.
1008 --
1009 person := per_db_per_additional.create_employee(
1010 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1011 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1012 p_business_group=>input_bg_name,
1013 p_last_name=>'Payments 1',
1014 p_national_identifier=>'XXXXXXX1',
1015 p_employee_number=> name_prefix || '-1',
1016 p_payroll=>name_prefix || 'Paym Test Payroll',
1017 p_date_of_birth=>sysdate-8000);
1018 --
1019 select assignment_id
1020 into assignment1
1021 from per_all_assignments_f
1022 where person_id = person;
1023 --
1024 -- Now add people with personal payment methods
1025 --
1026 person := per_db_per_additional.create_employee(
1027 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1028 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1029 p_business_group=>input_bg_name,
1030 p_last_name=>'Payments 2',
1031 p_national_identifier=>'XXXXXXX2',
1032 p_employee_number=> name_prefix || '-2',
1033 p_payroll=>name_prefix || 'Paym Test Payroll',
1034 p_date_of_birth=>sysdate-8000);
1035 --
1036 -- Get the employee's assignment_id
1037 --
1038 select assignment_id
1039 into assignment2
1040 from per_all_assignments_f
1041 where person_id = person;
1042 --
1043 -- Now insert the PPMs
1044 --
1045 ppm1 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1046 to_date('31-12-4712','DD-MM-YYYY'),
1047 business_group,
1048 null,
1049 assignment2,
1050 cash_opm1,
1051 100,
1052 null,
1053 1);
1054 --
1055 ppm2 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1056 to_date('31-12-4712','DD-MM-YYYY'),
1057 business_group,
1058 null,
1059 assignment2,
1060 check_opm1,
1061 400,
1062 null,
1063 2);
1064 --
1065 -- Third assignment, one (forgein) PPM (100%)
1066 --
1067 person := per_db_per_additional.create_employee(
1068 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1069 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1070 p_business_group=>input_bg_name,
1071 p_last_name=>'Payments 3',
1072 p_national_identifier=>'XXXXXXX3',
1073 p_employee_number=> name_prefix || '-3',
1074 p_payroll=>name_prefix || 'Paym Test Payroll',
1075 p_date_of_birth=>sysdate-8000);
1076 --
1077 -- Get the employee's assignment_id
1078 --
1079 select assignment_id
1080 into assignment3
1081 from per_all_assignments_f
1082 where person_id = person;
1083 --
1084 -- Now insert the PPMs
1085 --
1086 ppm1 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1087 to_date('31-12-4712','DD-MM-YYYY'),
1088 business_group,
1089 null,
1090 assignment3,
1091 cheque_opm,
1092 null,
1093 100,
1094 1);
1095 --
1096 -- Fourth assignment, two x percent add to 100
1097 --
1098 person := per_db_per_additional.create_employee(
1099 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1100 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1101 p_business_group=>input_bg_name,
1102 p_last_name=>'Payments 4',
1103 p_national_identifier=>'XXXXXXX4',
1104 p_employee_number=> name_prefix || '-4',
1105 p_payroll=>name_prefix || 'Paym Test Payroll',
1106 p_date_of_birth=>sysdate-8000);
1107 --
1108 -- Get the employee's assignment_id
1109 --
1110 select assignment_id
1111 into assignment4
1112 from per_all_assignments_f
1113 where person_id = person;
1114 --
1115 -- Now insert the PPMs
1116 --
1117 ppm1 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1118 to_date('31-12-4712','DD-MM-YYYY'),
1119 business_group,
1120 null,
1121 assignment4,
1122 cash_opm1,
1123 null,
1124 40,
1125 1);
1126 --
1127 ppm2 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1128 to_date('31-12-4712','DD-MM-YYYY'),
1129 business_group,
1130 null,
1131 assignment4,
1132 check_opm2,
1133 null,
1134 60,
1135 2);
1136 --
1137 -- Fifth assignment: one amount, two percent add to 100
1138 --
1139 person := per_db_per_additional.create_employee(
1140 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1141 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1142 p_business_group=>input_bg_name,
1143 p_last_name=>'Payments 5',
1144 p_national_identifier=>'XXXXXXX5',
1145 p_employee_number=> name_prefix || '-5',
1146 p_payroll=>name_prefix || 'Paym Test Payroll',
1147 p_date_of_birth=>sysdate-8000);
1148 --
1149 -- Get the employee's assignment_id
1150 --
1151 select assignment_id
1152 into assignment5
1153 from per_all_assignments_f
1154 where person_id = person;
1155 --
1156 -- Now insert the PPMs
1157 --
1158 ppm1 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1159 to_date('31-12-4712','DD-MM-YYYY'),
1160 business_group,
1161 null,
1162 assignment5,
1163 cash_opm2,
1164 50,
1165 null,
1166 1);
1167 --
1168 ppm2 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1169 to_date('31-12-4712','DD-MM-YYYY'),
1170 business_group,
1171 null,
1172 assignment5,
1173 check_opm1,
1174 null,
1175 50,
1176 2);
1177 --
1178 -- NB This is a forgein payment
1179 --
1180 ppm3 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1181 to_date('31-12-4712','DD-MM-YYYY'),
1182 business_group,
1183 null,
1184 assignment5,
1185 cheque_opm,
1186 null,
1187 50,
1188 3);
1189 --
1190 --
1191 -- Last assignment, one percentage payment
1192 --
1193 person := per_db_per_additional.create_employee(
1194 p_effective_start_date=>to_date('01-01-1990','DD-MM-YYYY'),
1195 p_effective_end_date=>to_date('31-12-4712','DD-MM-YYYY'),
1196 p_business_group=>input_bg_name,
1197 p_last_name=>'Payments 6',
1198 p_national_identifier=>'XXXXXXX6',
1199 p_employee_number=> name_prefix || '-6',
1200 p_payroll=>name_prefix || 'Paym Test Payroll',
1201 p_date_of_birth=>sysdate-8000);
1202 --
1203 -- Get the employee's assignment_id
1204 --
1205 select assignment_id
1206 into assignment6
1207 from per_all_assignments_f
1208 where person_id = person;
1209 --
1210 -- Now insert the PPMs
1211 --
1212 ppm1 := ins_ppm(to_date('01-01-1990','DD-MM-YYYY'),
1213 to_date('31-12-4712','DD-MM-YYYY'),
1214 business_group,
1215 null,
1216 assignment6,
1217 check_opm2,
1218 null,
1219 40,
1220 1);
1221 --
1222 -- Now populate a payroll action and two chunks (three assignments each) of
1223 -- assignment actions for the payments process to run off. Chunk 1 will
1224 -- have asg1-3, while chunk2 will have asg 4-6.
1225 --
1226 payroll_action := hr_ppvol.ins_ppa(action_type=>'P',
1227 business_group=>business_group,
1228 consolidation_set=>cset,
1229 payroll=>payroll1,
1230 pop_status=>'C',
1231 action_status=>'U',
1232 action_date=>to_date('31-01-1990',
1233 'DD-MM-YYYY'),
1234 parameters=>null);
1235 --
1236 -- Insert the first chunk
1237 --
1238 action1 := hr_ppvol.ins_paa(assignment=>assignment1,
1239 payroll_action=>payroll_action,
1240 chunk=>1);
1241 --
1242 action2 := hr_ppvol.ins_paa(assignment=>assignment2,
1243 payroll_action=>payroll_action,
1244 chunk=>1);
1245 --
1246 action3 := hr_ppvol.ins_paa(assignment=>assignment3,
1247 payroll_action=>payroll_action,
1248 chunk=>1);
1249 --
1250 -- Insert the second chunk
1251 --
1252 action4 := hr_ppvol.ins_paa(assignment=>assignment4,
1253 payroll_action=>payroll_action,
1254 chunk=>2);
1255 --
1256 action5 := hr_ppvol.ins_paa(assignment=>assignment5,
1257 payroll_action=>payroll_action,
1258 chunk=>2);
1259 --
1260 action6 := hr_ppvol.ins_paa(assignment=>assignment6,
1261 payroll_action=>payroll_action,
1262 chunk=>2);
1263 --
1264 -- End loading of standars pre-payments test data
1265 --
1266 end;
1267 --
1268 end hr_ppvol;