[Home] [Help]
PACKAGE BODY: APPS.PAY_US_DB_PER_SETUP
Source
1 PACKAGE BODY pay_us_db_per_setup AS
2 /* $Header: pyusuelt.pkb 115.7 99/07/17 06:47:16 porting ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : pay_us_db_per_setup (BODY)
24 Description : This package defines the procedures required to
25 create all 'set up' entities in Personnel.
26 That is:
27 Business Groups,
28 HR Organizations,
29 Legal Companies,
30 Positions,
31 Jobs and
32 Grades.
33 Change List
34 -----------
35 Version Date Author ER/CR No. Description of Change
36 -------+---------+----------+---------+--------------------------
37 ( History of pesutupd.pkb, ie. db_per_setup :-
38 70.0 19-NOV-92 SZWILLIA Date Created
39 70.2 30-DEC-92 SZWILLIA Added Person Building blocks
40 70.3 04-JAN-93 SZWILLIA create_applicant and private
41 function insert_application
42 70.4 05-JAN-93 SZWILLIA Correction for applicant
43 assignments
44 70.5 11-JAN-93 SZWILLIA Corrected date defaulting
45 70.6 11-JAN-93 SZWILLIA Changed interface to create
46 business group to accept
47 structure names not numbers
48 70.9 20-JAN-93 SZWILLIA Corrected error handling.
49 70.12 09-MAR-93 SZWILLIA Made insert_org_information
50 public and added error checking.
51 70.13 11-MAR-93 NKHAN Added 'exit' to the end )
52
53 *** AUG-93 us_pesutupd.pkb created, ie. copied from pesutupd.pkb ***
54 40.1 ??-AUG-93 MSWANSON Date us_pesutupd.pkb created, ie.
55 copied from pesutupd.pkb and
56 altered for US testing.
57 40.2 20-AUG-93 RMAMGAIN Modified Create_business_group
58 Commented some stuff.
59 Added more insert in Leg. Company.
60 40.3 08-APR-94 AKELLY Added insert of 'Federal Tax Rules'
61 'State Tax Rules' and 'Work Schedule'
62 ddf info. for legal company.
63 ****
64 40.0 31-MAY-94 MGILMORE Renamed.
65 40.1 03-JUL-94 AROUSSEL Tidyied up for 10G install
66 40.2 05-OCT-94 RFINE Changed calls from us_ins_org_info to
67 pay_us_ins_org_info_pkg
68 40.3 24-NOV-94 RFINE Suppressed index on business_group_id
69 40.4 01-MAR-95 MSWANSON Add/Change for EEO and VETS100 system
70 test data creation:
71 - create_est_organization,
72 - create_eeo_hierarchy,
73 - create_eeo_hierarchy_version,
74 - create_eeo_hierarchy_element.
75 40.5 25-JUL-95 AMILLS Changed tokenised message 'PAY_6361_USER_
76 TABLE_UNIQUE' for the following (Hard coded):
77 'PAY_7687_USER_GRADE_TAB_UNIQUE',
81 40.7 28-SEP-95 AKELLY Added functions INSERT_WC_FUND, INSERT_WC_RATE,
78 'PAY_7688_USER_POS_TAB_UNIQUE',
79 'PAY_7686_USER_JOB_TAB_UNIQUE'
80 40.6 26-sep-95 AKELLY corrected create_est_organizatioN
82 CREATE_WC_CARRIER and CREATE_US_LOCATION.
83 40.9 01-NOV-95 JTHURING Removed error checking from end of script
84 110.2 11-SEP-97 khabibul added suffix _vl to fnd_id_flex_structures as
85 id_flex_structure_name col is obsolete.
86 110.3 19-JAN-99 nbristow insert into hr_all_organization_units rather
87 than hr_organization_units.
88 115.2 24-MAR-99 sdoshi Flexible Dates Conversion.
89 115.3 24-MAR-99 alogue MLS changes.
90 115.4 14-May-1999 mmillmor multi radix change to working_hours on position
91 ================================================================= */
92 --
93 --
94 FUNCTION insert_organization_unit
95 ( P_GROUP VARCHAR2
96 ,P_NAME VARCHAR2
97 ,P_BUSINESS_GROUP_ID NUMBER
98 ,P_COST_ALLOCATION_KEYFLEX_ID NUMBER
99 ,P_LOCATION_ID NUMBER
100 ,P_SOFT_CODING_KEYFLEX_ID NUMBER
101 ,P_DATE_FROM DATE
102 ,P_DATE_TO DATE
103 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2
104 ,P_INTERNAL_ADDRESS_LINE VARCHAR2
105 ,P_TYPE VARCHAR2
106 ) return NUMBER
107 IS
108 --
109 l_organization_id NUMBER;
110 language VARCHAR2(4);
111 --
112 CURSOR get_languages IS
113 SELECT language_code
114 from fnd_languages
115 where installed_flag in ('I','B');
116 --
117 begin
118 --
119 if p_group = 'Y' then null;
120 elsif p_business_group_id IS NULL then
121 hr_utility.set_message(801,'HR_MANDATORY_PARAMETER');
122 hr_utility.set_message_token('PARAMETER_NAME','Business Group');
123 hr_utility.raise_error;
124 end if;
125 --
126 hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',1);
127 --
128 SELECT hr_organization_units_s.nextval
129 INTO l_organization_id
130 FROM sys.dual ;
131 --
132 hr_organization.unique_name(p_business_group_id,l_organization_id,p_name);
133 --
134 --
135 hr_organization.date_range(nvl(p_date_from,trunc(SYSDATE)),p_date_to);
136 --
137 --
138 hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',2);
139 --
140 INSERT INTO HR_ALL_ORGANIZATION_UNITS
141 (organization_id
142 ,business_group_id
143 ,cost_allocation_keyflex_id
144 ,location_id
145 ,soft_coding_keyflex_id
146 ,date_from
147 ,name
148 ,date_to
149 ,internal_external_flag
150 ,internal_address_line
151 ,type
152 ,last_update_date
153 ,last_updated_by
154 ,last_update_login
155 ,created_by
156 ,creation_date)
157 SELECT l_organization_id
158 ,DECODE(p_group,'Y',l_organization_id,p_business_group_id)
159 ,p_cost_allocation_keyflex_id
160 ,p_location_id
161 ,p_soft_coding_keyflex_id
162 ,nvl(p_date_from,trunc(SYSDATE))
163 ,p_name
164 ,p_date_to
165 ,p_internal_external_flag
166 ,p_internal_address_line
167 ,p_type
168 ,SYSDATE
169 ,0
170 ,0
171 ,0
172 ,SYSDATE
173 FROM sys.dual ;
174 --
175 hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',3);
176 --
177 OPEN get_languages;
178 --
179 LOOP
180 --
181 FETCH get_languages
182 INTO language;
183 EXIT WHEN get_languages%NOTFOUND;
184 --
185 INSERT INTO HR_ALL_ORGANIZATION_UNITS_TL
186 (organization_id
187 ,language
188 ,source_lang
189 ,name
190 ,last_update_date
191 ,last_updated_by
192 ,last_update_login
193 ,created_by
194 ,creation_date)
195 SELECT l_organization_id
196 ,language
197 ,userenv('LANG')
198 ,p_name
199 ,SYSDATE
200 ,0
201 ,0
202 ,0
203 ,SYSDATE
204 FROM sys.dual ;
205 --
206 END LOOP;
207 --
208 hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',4);
209 --
210 return l_organization_id;
211 --
212 --
213 end insert_organization_unit;
214 --
215 --
216 FUNCTION create_business_group
217 ( P_GROUP VARCHAR2 DEFAULT 'Y'
218 ,P_NAME VARCHAR2
219 ,P_DATE_FROM DATE
220 ,P_DATE_TO DATE DEFAULT null
221 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
222 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
223 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
224 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
225 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
226 ,P_SHORT_NAME VARCHAR2
227 ,P_METHOD_OF_GENERATION_EMP_NUM VARCHAR2 DEFAULT 'A'
228 ,P_METHOD_OF_GENERATION_APL_NUM VARCHAR2 DEFAULT 'A'
229 ,P_GRADE_STRUCTURE VARCHAR2 DEFAULT 'Grade Flexfield'
230 ,P_PEOPLE_GROUP_STRUCTURE VARCHAR2
231 DEFAULT 'People Group Flexfield'
232 ,P_JOB_STRUCTURE VARCHAR2 DEFAULT 'Job Flexfield'
233 ,P_COST_ALLOCATION_STRUCTURE VARCHAR2
237 ,P_CURRENCY_CODE VARCHAR2 DEFAULT 'USD'
234 DEFAULT 'Cost Allocation Flexfield'
235 ,P_POSITION_STRUCTURE VARCHAR2 DEFAULT 'Position Flexfield'
236 ,P_LEGISLATION_CODE VARCHAR2 DEFAULT 'US'
238 ,P_FISCAL_YEAR_START VARCHAR2 DEFAULT null
239 ,P_ASSIGNMENT_STATUS_1 VARCHAR2 DEFAULT null
240 ,P_ASSIGNMENT_STATUS_2 VARCHAR2 DEFAULT null
241 ,P_EMPLOYMENT_CATEGORY_1 VARCHAR2 DEFAULT null
242 ,P_EMPLOYMENT_CATEGORY_2 VARCHAR2 DEFAULT null
243 ) return NUMBER
244 IS
245 --
246 l_business_group_id NUMBER;
247 l_org_information_id NUMBER;
248 l_position_num NUMBER;
249 l_grade_num NUMBER;
250 l_job_num NUMBER;
251 l_people_group_num NUMBER;
252 l_cost_allocation_num NUMBER;
253 --
254 begin
255 --
256 --
257 hr_utility.set_location('pay_us_db_per_setup.create_business_group',1);
258 SELECT id_flex_num
259 INTO l_position_num
260 FROM fnd_id_flex_structures_vl
261 WHERE id_flex_code = 'POS'
262 AND id_flex_structure_name = p_position_structure;
263 --
264 hr_utility.set_location('pay_us_db_per_setup.create_business_group',2);
265 SELECT id_flex_num
266 INTO l_grade_num
267 FROM fnd_id_flex_structures_vl
268 WHERE id_flex_code = 'GRD'
269 AND id_flex_structure_name = p_grade_structure;
270 --
271 hr_utility.set_location('pay_us_db_per_setup.create_business_group',3);
272 SELECT id_flex_num
273 INTO l_job_num
274 FROM fnd_id_flex_structures_vl
275 WHERE id_flex_code = 'JOB'
276 AND id_flex_structure_name = p_job_structure;
277 --
278 hr_utility.set_location('pay_us_db_per_setup.create_business_group',4);
279 SELECT id_flex_num
280 INTO l_people_group_num
281 FROM fnd_id_flex_structures_vl
282 WHERE id_flex_code = 'GRP'
283 AND id_flex_structure_name = p_people_group_structure;
284 --
285 hr_utility.set_location('pay_us_db_per_setup.create_business_group',5);
286 SELECT id_flex_num
287 INTO l_cost_allocation_num
288 FROM fnd_id_flex_structures_vl
289 WHERE id_flex_code = 'COST'
290 AND id_flex_structure_name = p_cost_allocation_structure;
291 --
292 hr_utility.set_location('pay_us_db_per_setup.create_business_group',6);
293 l_business_group_id := insert_organization_unit('Y'
294 ,p_name
295 ,null
296 ,null
297 ,null
298 ,null
299 ,p_date_from
300 ,p_date_to
301 ,'INT'
302 ,p_internal_address_line
303 ,null);
304 --
305 --
306 hr_utility.set_location('pay_us_db_per_setup.create_business_group',7);
307 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
308 l_business_group_id
309 ,'CLASS'
310 ,'HR_BG'
311 ,'Y'
312 ,null
313 ,null
314 ,null
315 ,null
316 ,null
317 ,null
318 ,null
319 ,null
320 ,null
321 ,null
322 ,null
323 ,null
324 ,null
325 ,null
326 ,null
327 ,null
328 ,null
329 ,null);
330 --
331 --
332 hr_utility.set_location('pay_us_db_per_setup.create_business_group',8);
333 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
334 l_business_group_id
335 ,'CLASS'
336 ,'HR_ORG'
337 ,'Y'
338 ,null
339 ,null
340 ,null
341 ,null
342 ,null
343 ,null
344 ,null
345 ,null
346 ,null
347 ,null
348 ,null
349 ,null
350 ,null
351 ,null
355 ,null);
352 ,null
353 ,null
354 ,null
356 --
357 --
358 hr_utility.set_location('pay_us_db_per_setup.create_business_group',9);
359 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
360 l_business_group_id
361 ,'Business Group Information'
362 ,p_short_name
363 ,p_method_of_generation_emp_num
364 ,p_method_of_generation_apl_num
365 ,l_grade_num
366 ,l_people_group_num
367 ,l_job_num
368 ,l_cost_allocation_num
369 ,l_position_num
370 ,p_legislation_code
371 ,p_currency_code
372 ,p_fiscal_year_start
373 ,null
374 ,null
375 ,null
376 ,null
377 ,null
378 ,null
379 ,null
380 ,null
381 ,null);
382 --
383 --
384 hr_utility.set_location('pay_us_db_per_setup.create_business_group',10);
385 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
386 l_business_group_id
387 ,'Work Day Information'
388 ,p_default_start_time
389 ,p_default_end_time
390 ,p_working_hours
391 ,p_frequency
392 ,null
393 ,null
394 ,null
395 ,null
396 ,null
397 ,null
398 ,null
399 ,null
400 ,null
401 ,null
402 ,null
403 ,null
404 ,null
405 ,null
406 ,null
407 ,null);
408 --
409 -- Reporting Categories and Reporting Statuses are for use in VETS-100
410 -- System Test
411 --
412 hr_utility.set_location('pay_us_db_per_setup.create_business_group',11);
413 --
414 if p_employment_category_1 is not null then
415 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
416 l_business_group_id
417 ,'Reporting Categories'
418 ,p_employment_category_1
419 ,null
420 ,null
421 ,null
422 ,null
423 ,null
424 ,null
425 ,null
426 ,null
427 ,null
428 ,null
429 ,null
430 ,null
431 ,null
432 ,null
433 ,null
434 ,null
435 ,null
436 ,null
437 ,null);
438 end if;
439 --
440 --
441 hr_utility.set_location('pay_us_db_per_setup.create_business_group',12);
442 if p_employment_category_2 is not null then
443 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
444 l_business_group_id
445 ,'Reporting Categories'
446 ,p_employment_category_2
447 ,null
448 ,null
449 ,null
450 ,null
451 ,null
452 ,null
453 ,null
457 ,null
454 ,null
455 ,null
456 ,null
458 ,null
459 ,null
460 ,null
461 ,null
462 ,null
463 ,null
464 ,null
465 ,null);
466 end if;
467 --
468 --
469 hr_utility.set_location('pay_us_db_per_setup.create_business_group',13);
470 if p_assignment_status_1 is not null then
471 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
472 l_business_group_id
473 ,'Reporting Statuses'
474 ,p_assignment_status_1
475 ,null
476 ,null
477 ,null
478 ,null
479 ,null
480 ,null
481 ,null
482 ,null
483 ,null
484 ,null
485 ,null
486 ,null
487 ,null
488 ,null
489 ,null
490 ,null
491 ,null
492 ,null
493 ,null);
494 end if;
495 --
496 --
497 hr_utility.set_location('pay_us_db_per_setup.create_business_group',14);
498 if p_assignment_status_2 is not null then
499 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
500 l_business_group_id
501 ,'Reporting Statuses'
502 ,p_assignment_status_2
503 ,null
504 ,null
505 ,null
506 ,null
507 ,null
508 ,null
509 ,null
510 ,null
511 ,null
512 ,null
513 ,null
514 ,null
515 ,null
516 ,null
517 ,null
518 ,null
519 ,null
520 ,null
521 ,null);
522 end if;
523 --
524 --
525 return l_business_group_id;
526 --
527 --
528 end create_business_group;
529 --
530 --
531 FUNCTION create_per_organization
532 ( P_NAME VARCHAR2
533 ,P_BUSINESS_GROUP VARCHAR2
534 ,P_DATE_FROM DATE
535 ,P_DATE_TO DATE DEFAULT null
536 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
537 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
538 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
539 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
540 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
541 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
542 ,P_TYPE VARCHAR2 DEFAULT null
543 ,P_LOCATION_ID NUMBER DEFAULT null
544 ) return NUMBER
545 IS
546 --
547 l_organization_id NUMBER;
548 l_org_information_id NUMBER;
549 l_business_group_id NUMBER;
550 --
551 begin
552 --
553 --
554 hr_utility.trace('Entered Create_Per_Organization ');
555 hr_utility.set_location('pay_us_db_per_setup.create_per_organization',1);
556 --
557 SELECT business_group_id
558 INTO l_business_group_id
559 FROM per_business_groups
560 WHERE name = p_business_group;
561 --
562 l_organization_id := insert_organization_unit('N'
563 ,p_name
564 ,l_business_group_id
565 ,null
566 ,p_location_id
567 ,null
568 ,p_date_from
569 ,p_date_to
573 hr_utility.trace('Called insert_information_unit from CPO');
570 ,p_internal_external_flag
571 ,p_internal_address_line
572 ,p_type);
574 --
575 --
576 --
577 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(l_organization_id
578 ,'CLASS'
579 ,'HR_ORG'
580 ,'Y'
581 ,null
582 ,null
583 ,null
584 ,null
585 ,null
586 ,null
587 ,null
588 ,null
589 ,null
590 ,null
591 ,null
592 ,null
593 ,null
594 ,null
595 ,null
596 ,null
597 ,null
598 ,null);
599 hr_utility.trace('Called insert_org_information once from CPO');
600 --
601 --
602 --
603 hr_utility.trace('p_default_start_time '||p_default_start_time);
604
605 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(l_organization_id
606 ,'Work Day Information'
607 ,p_default_start_time
608 ,p_default_end_time
609 ,p_working_hours
610 ,p_frequency
611 ,null
612 ,null
613 ,null
614 ,null
615 ,null
616 ,null
617 ,null
618 ,null
619 ,null
620 ,null
621 ,null
622 ,null
623 ,null
624 ,null
625 ,null
626 ,null);
627 hr_utility.trace('Called insert_org_information twice from CPO');
628 --
629 return l_organization_id;
630 --
631 end create_per_organization;
632 --
633 --
634 -- Creates an organization which is an Establishment Only (ie. not a GRE too)
635 --
636 --
637 FUNCTION create_est_organization
638 ( P_NAME VARCHAR2
639 ,P_BUSINESS_GROUP VARCHAR2
640 ,P_DATE_FROM DATE
641 ,P_DATE_TO DATE DEFAULT null
642 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
643 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
644 ,P_TYPE VARCHAR2 DEFAULT null
645 ,P_LOCATION_ID NUMBER DEFAULT null
646 ,P_EEO1_UNIT_NUMBER NUMBER DEFAULT null
647 ,P_VETS100_UNIT_NUMBER NUMBER DEFAULT null
648 ,P_REPORTING_NAME VARCHAR2 DEFAULT null
649 ,P_VETS100_REPORTING_NAME VARCHAR2 DEFAULT null
650 ,P_SIC NUMBER DEFAULT null
651 ,P_ACTIVITY_LINE1 VARCHAR2 DEFAULT null
652 ,P_ACTIVITY_LINE2 VARCHAR2 DEFAULT null
653 ,P_ACTIVITY_LINE3 VARCHAR2 DEFAULT null
654 ,P_ACTIVITY_LINE4 VARCHAR2 DEFAULT null
655 ,P_APPRENTICES_EMPLOYED VARCHAR2 DEFAULT null
656
657 ) return NUMBER
658 IS
659 --
660 l_organization_id NUMBER;
661 l_org_information_id NUMBER;
662 l_business_group_id NUMBER;
663 --
664 begin
665 --
666 --
667 hr_utility.trace('Entered Create_Est_Organization ');
668 hr_utility.set_location('pay_us_db_per_setup.create_per_organization',1);
669 --
670 SELECT business_group_id
671 INTO l_business_group_id
672 FROM per_business_groups
673 WHERE name = p_business_group;
674 --
675 l_organization_id := insert_organization_unit('N'
676 ,p_name
677 ,l_business_group_id
678 ,null
679 ,p_location_id
680 ,null
681 ,p_date_from
682 ,p_date_to
683 ,p_internal_external_flag
684 ,p_internal_address_line
685 ,p_type);
686 hr_utility.trace('Called insert_information_unit from CEO');
687 --
688 -- Add new classification : HR_ESTAB
692 ,'HR_ESTAB'
689 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
690 l_organization_id
691 ,'CLASS'
693 ,'Y'
694 ,null
695 ,null
696 ,null
697 ,null
698 ,null
699 ,null
700 ,null
701 ,null
702 ,null
703 ,null
704 ,null
705 ,null
706 ,null
707 ,null
708 ,null
709 ,null
710 ,null
711 ,null);
712 --
713 --
714 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
715 (l_organization_id
716 ,'CLASS'
717 ,'HR_ORG'
718 ,'Y'
719 ,null
720 ,null
721 ,null
722 ,null
723 ,null
724 ,null
725 ,null
726 ,null
727 ,null
728 ,null
729 ,null
730 ,null
731 ,null
732 ,null
733 ,null
734 ,null
735 ,null
736 ,null);
737 hr_utility.trace('Called insert_org_information once from CEO');
738 --
739 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
740 (l_organization_id
741 ,'Establishment EEO-1 Filing'
742 ,'Y'
743 ,p_eeo1_unit_number
744 ,p_reporting_name
745 ,p_sic
746 ,p_activity_line1
747 ,p_activity_line2
748 ,p_activity_line3
749 ,p_activity_line4
750 ,null
751 ,p_apprentices_employed
752 ,null
753 ,null
754 ,null
755 ,null
756 ,null
757 ,null
758 ,null
759 ,null
760 ,null
761 ,null);
762 if p_vets100_unit_number is not null then
763 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
764 (l_organization_id
765 ,'Establishment VETS-100 Filing'
766 ,p_vets100_unit_number
767 ,p_vets100_reporting_name
768 ,null
769 ,null
770 ,null
771 ,null
772 ,null
773 ,null
774 ,p_apprentices_employed
775 ,null
776 ,null
777 ,null
778 ,null
779 ,null
780 ,null
781 ,null
782 ,null
783 ,null
784 ,null
785 ,null);
786 end if;
787 --
788 return l_organization_id;
789 --
790 end create_est_organization;
791 --
792 --
793 FUNCTION create_pay_legal_company
797 ,P_DATE_TO DATE DEFAULT null
794 ( P_NAME VARCHAR2
795 ,P_BUSINESS_GROUP VARCHAR2
796 ,P_DATE_FROM DATE
798 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
799 ,P_LOCATION_ID NUMBER DEFAULT null
800 ,P_COMPANY_FEDERAL_IDENTIFIER VARCHAR2 DEFAULT null
801 ,P_NACHA_COMPANY_NAME VARCHAR2 DEFAULT null
802 ,P_NACHA_IDENTIFIER VARCHAR2 DEFAULT null
803 ,P_NACHA_DISCRETIONARY_CODE VARCHAR2 DEFAULT null
804 ,P_SS_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
805 ,P_MED_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
806 ,P_FUTA_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
807 ,P_TYPE_OF_EMPLOYMENT VARCHAR2 DEFAULT null
808 ,P_TAX_GROUP VARCHAR2 DEFAULT null
809 ,P_SUPPLEMENTAL_CALC_METHOD VARCHAR2 DEFAULT null
810 ,P_WORK_SCHEDULE_TABLE VARCHAR2 DEFAULT 'COMPANY WORK SCHEDULES'
811 ,P_WORK_SCHEDULE_NAME VARCHAR2 DEFAULT null
812 ,P_EEO1_UNIT_NUMBER VARCHAR2 DEFAULT null
813 ,P_VETS100_UNIT_NUMBER NUMBER DEFAULT null
814 ,P_REPORTING_NAME VARCHAR2 DEFAULT null
815 ,P_VETS100_REPORTING_NAME VARCHAR2 DEFAULT null
816 ,P_SIC VARCHAR2 DEFAULT null
817 ,P_ACTIVITY_LINE1 VARCHAR2 DEFAULT null
818 ,P_ACTIVITY_LINE2 VARCHAR2 DEFAULT null
819 ,P_ACTIVITY_LINE3 VARCHAR2 DEFAULT null
820 ,P_ACTIVITY_LINE4 VARCHAR2 DEFAULT null
821 ,P_APPRENTICES_EMPLOYED VARCHAR2 DEFAULT null
822 ,P_EEO1_IDENTIFICATION_NUMBER VARCHAR2 DEFAULT null
823 ,P_VETS100_COMPANY_NUMBER VARCHAR2 DEFAULT null
824 ,P_DUN_AND_BRADSTREET_NUMBER VARCHAR2 DEFAULT null
825 ,P_GRE_REPORTING_NAME VARCHAR2 DEFAULT null
826 ,P_AFFILIATED VARCHAR2 DEFAULT null
827 ,P_GOVERNMENT_CONTRACTOR VARCHAR2 DEFAULT null
828 ,P_ORG_TYPE VARCHAR2 DEFAULT null
829 ) return NUMBER
830 IS
831 --
832 l_legal_company_id NUMBER;
833 l_org_information_id NUMBER;
834 l_business_group_id NUMBER;
835 --
836 begin
837 --
838 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',1);
839 --
840 SELECT business_group_id
841 INTO l_business_group_id
842 FROM hr_all_organization_units
843 WHERE name = p_business_group
844 and business_group_id + 0 = organization_id;
845 --
846 l_legal_company_id := insert_organization_unit('N'
847 ,p_name
848 ,l_business_group_id
849 ,null
850 ,p_location_id
851 ,null
852 ,p_date_from
853 ,p_date_to
854 ,'INT'
855 ,p_internal_address_line
856 ,p_org_type);
857 --
858 --
859 --
860 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',2);
861 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
862 l_legal_company_id
863 ,'CLASS'
864 ,'HR_ORG'
865 ,'Y'
866 ,null
867 ,null
868 ,null
869 ,null
870 ,null
871 ,null
872 ,null
873 ,null
874 ,null
875 ,null
876 ,null
877 ,null
878 ,null
879 ,null
880 ,null
881 ,null
882 ,null
883 ,null);
884 --
885 --
886 --
887 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',3);
888 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
889 l_legal_company_id
890 ,'CLASS'
891 ,'HR_LEGAL'
892 ,'Y'
893 ,null
894 ,null
895 ,null
896 ,null
897 ,null
898 ,null
899 ,null
900 ,null
901 ,null
902 ,null
906 ,null
903 ,null
904 ,null
905 ,null
907 ,null
908 ,null
909 ,null
910 ,null);
911 --
912 --
913 if P_NAME = 'Swanson I.T.' then
914 --
915 -- Add new classification : HR_ESTAB this org is a GRE/EST combo
916 --
917 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
918 l_legal_company_id
919 ,'CLASS'
920 ,'HR_ESTAB'
921 ,'Y'
922 ,null
923 ,null
924 ,null
925 ,null
926 ,null
927 ,null
928 ,null
929 ,null
930 ,null
931 ,null
932 ,null
933 ,null
934 ,null
935 ,null
936 ,null
937 ,null
938 ,null
939 ,null);
940 --
941 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
942 l_legal_company_id
943 ,'Establishment EEO-1 Filing' -- context
944 ,null -- used to be Establishment flag
945 ,P_EEO1_UNIT_NUMBER
946 ,P_REPORTING_NAME
947 ,P_SIC
948 ,P_ACTIVITY_LINE1
949 ,P_ACTIVITY_LINE2
950 ,P_ACTIVITY_LINE3
951 ,P_ACTIVITY_LINE4
952 ,P_APPRENTICES_EMPLOYED
953 ,null
954 ,null
955 ,null
956 ,null
957 ,null
958 ,null
959 ,null
960 ,null
961 ,null
962 ,null
963 ,null);
964 -- Enter row for VETS-100
965 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
966 l_legal_company_id
967 ,'Establishment VETS-100 Filing' -- context
968 ,P_VETS100_UNIT_NUMBER
969 ,null
970 ,null
971 ,null
972 ,null
973 ,null
974 ,null
975 ,null
976 ,null
977 ,null
978 ,null
979 ,null
980 ,null
981 ,null
982 ,null
983 ,null
984 ,null
985 ,null
986 ,null
987 ,null);
988 end if;
989 --
990 -- Modified RMAMGAIN
991 -- Modified MSWANSON 'Nacha' to 'NACHA'.
992 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',4);
993 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
994 l_legal_company_id
995 ,'NACHA Rules'
996 ,P_NACHA_COMPANY_NAME
997 ,'220'
998 ,'PPD'
999 ,P_NACHA_IDENTIFIER
1000 ,P_NACHA_DISCRETIONARY_CODE
1001 ,null
1002 ,null
1003 ,null
1004 ,null
1005 ,null
1006 ,null
1007 ,null
1008 ,null
1009 ,null
1010 ,null
1011 ,null
1012 ,null
1013 ,null
1014 ,null
1015 ,null);
1016 --
1017 -- Inserted by RMAMGAIN
1018 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',5);
1022 ,'Y' -- transmitter_flag
1019 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1020 l_legal_company_id
1021 ,'Multiple Worksite Reporting' -- context
1023 ,'Contact Person1' -- position
1024 ,'415-506-7000' -- telephone
1025 ,'2807' -- extension
1026 ,null
1027 ,null
1028 ,null
1029 ,null
1030 ,null
1031 ,null
1032 ,null
1033 ,null
1034 ,null
1035 ,null
1036 ,null
1037 ,null
1038 ,null
1039 ,null
1040 ,null
1041 ,null);
1042 -- Inserted By RMAMGAIN
1043 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',6);
1044 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1045 l_legal_company_id
1046 ,'W2 Reporting Rules' -- context
1047 ,'Y' -- transmitter_flag
1048 ,'SEQUENT' -- computer
1049 ,'SL' -- internal_labelling
1050 ,'16' -- tape_density
1051 ,'ASC' -- tape_rec_code
1052 ,'512' -- blocking_factor
1053 ,null
1054 ,null
1055 ,null
1056 ,null
1057 ,null
1058 ,null
1059 ,null
1060 ,null
1061 ,null
1062 ,null
1063 ,null
1064 ,null
1065 ,null
1066 ,null);
1067 -- Inserted by RMAMGAIN
1068 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',7);
1069 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1070 l_legal_company_id
1071 ,'Employer Identification' -- context
1072 ,P_COMPANY_FEDERAL_IDENTIFIER
1073 ,P_COMPANY_FEDERAL_IDENTIFIER
1074 ,P_NAME
1075 ,'POSITION'
1076 ,'415-506-7000'
1077 ,'512'
1078 ,fnd_number.canonical_to_number(l_legal_company_id)
1079 ,'Address'
1080 ,'Regular'
1081 ,null
1082 ,null
1083 ,null
1084 ,null
1085 ,null
1086 ,null
1087 ,null
1088 ,null
1089 ,null
1090 ,null
1091 ,null);
1092 -- Inserted by AKELLY
1093 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',8);
1094 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1095 l_legal_company_id
1096 ,'Work Schedule' -- context
1097 ,P_WORK_SCHEDULE_TABLE
1098 ,P_WORK_SCHEDULE_TABLE
1099 ,null
1100 ,null
1101 ,null
1102 ,null
1103 ,null
1104 ,null
1105 ,null
1106 ,null
1107 ,null
1108 ,null
1109 ,null
1110 ,null
1111 ,null
1112 ,null
1113 ,null
1114 ,null
1115 ,null
1116 ,null);
1117 -- Inserted by AKELLY
1118 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',9);
1119 if P_SS_SELF_ADJUST_METHOD IS NULL
1120 and P_MED_SELF_ADJUST_METHOD IS NULL
1121 and P_FUTA_SELF_ADJUST_METHOD IS NULL
1122 and P_TYPE_OF_EMPLOYMENT IS NULL
1123 and P_TAX_GROUP IS NULL
1124 and P_SUPPLEMENTAL_CALC_METHOD IS NULL then
1125 null;
1126 else
1127 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1128 l_legal_company_id
1129 ,'Federal Tax Rules' -- context
1133 ,P_TYPE_OF_EMPLOYMENT
1130 ,P_SS_SELF_ADJUST_METHOD
1131 ,P_MED_SELF_ADJUST_METHOD
1132 ,P_FUTA_SELF_ADJUST_METHOD
1134 ,P_TAX_GROUP
1135 ,P_SUPPLEMENTAL_CALC_METHOD
1136 ,null
1137 ,null
1138 ,null
1139 ,null
1140 ,null
1141 ,null
1142 ,null
1143 ,null
1144 ,null
1145 ,null
1146 ,null
1147 ,null
1148 ,null
1149 ,null);
1150 end if;
1151 --
1152 -- Inserted by MSWANSON
1153 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',10);
1154 --
1155 -- Inserted by MSWANSON
1156 hr_utility.set_location('pay_us_db_per_setup.create_pay_legal_company',10);
1157 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1158 l_legal_company_id
1159 ,'EEO-1 Filing' -- context
1160 ,P_EEO1_IDENTIFICATION_NUMBER
1161 ,P_DUN_AND_BRADSTREET_NUMBER
1162 ,P_GRE_REPORTING_NAME
1163 ,P_AFFILIATED
1164 ,P_GOVERNMENT_CONTRACTOR
1165 ,null
1166 ,null
1167 ,null
1168 ,null
1169 ,null
1170 ,null
1171 ,null
1172 ,null
1173 ,null
1174 ,null
1175 ,null
1176 ,null
1177 ,null
1178 ,null
1179 ,null);
1180 --
1181 -- Add data for VETS-100
1182 --
1183 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1184 l_legal_company_id
1185 ,'VETS-100 Filing' -- context
1186 ,P_VETS100_COMPANY_NUMBER
1187 ,null
1188 ,null
1189 ,null
1190 ,null
1191 ,null
1192 ,null
1193 ,null
1194 ,null
1195 ,null
1196 ,null
1197 ,null
1198 ,null
1199 ,null
1200 ,null
1201 ,null
1202 ,null
1203 ,null
1204 ,null
1205 ,null);
1206 --
1207 return l_legal_company_id;
1208 --
1209 end create_pay_legal_company;
1210 --
1211 --
1212 -- Create EEO Reporting Hierarchies
1213 --
1214 --
1215 FUNCTION create_eeo_hierarchy
1216 (p_hierarchy_name VARCHAR2
1217 ,p_business_group_id NUMBER
1218 ,p_primary_structure_flag VARCHAR2
1219 ) RETURN NUMBER
1220 IS
1221 --
1222 l_organization_structure_id NUMBER;
1223 --
1224 begin
1225 --
1226 SELECT per_org_structure_versions_s.nextval
1227 INTO l_organization_structure_id
1228 FROM sys.dual;
1229 --
1230 INSERT INTO per_organization_structures
1231 (organization_structure_id
1232 ,business_group_id
1233 ,name
1234 ,primary_structure_flag
1235 )
1236 VALUES
1237 (l_organization_structure_id
1238 ,p_business_group_id
1239 ,p_hierarchy_name
1240 ,p_primary_structure_flag
1241 );
1242 --
1243 return l_organization_structure_id;
1244 end create_eeo_hierarchy;
1245 --
1246 --
1247 FUNCTION create_eeo_hierarchy_version
1248 (p_business_group_id NUMBER
1249 ,p_date_from VARCHAR2
1250 ,p_organization_structure_id NUMBER
1251 ,p_version_number NUMBER
1252 ) return NUMBER
1253 IS
1254 --
1255 l_org_structure_version_id NUMBER;
1256 --
1257 begin
1258 --
1259 --
1260 SELECT per_org_structure_versions_s.nextval
1261 INTO l_org_structure_version_id
1262 FROM sys.dual;
1263 --
1264 --
1265 INSERT INTO per_org_structure_versions
1266 (org_structure_version_id
1267 ,business_group_id
1268 ,organization_structure_id
1269 ,date_from
1270 ,version_number
1271 )
1272 VALUES
1273 (l_org_structure_version_id
1274 ,p_business_group_id
1275 ,p_organization_structure_id
1276 ,fnd_date.canonical_to_date(p_date_from)
1277 ,p_version_number
1278 );
1279 --
1280 return l_org_structure_version_id;
1281 end create_eeo_hierarchy_version;
1282 --
1283 --
1284 FUNCTION create_eeo_hierarchy_element
1285 (p_business_group_id NUMBER
1286 ,p_organization_id_parent NUMBER
1287 ,p_org_structure_version_id NUMBER
1288 ,p_organization_id_child NUMBER
1289 ) RETURN NUMBER
1293 begin
1290 IS
1291 --
1292 --
1294 --
1295 --
1296 INSERT INTO per_org_structure_elements
1297 (org_structure_element_id
1298 ,business_group_id
1299 ,organization_id_parent
1300 ,org_structure_version_id
1301 ,organization_id_child
1302 )
1303 VALUES
1304 (per_org_structure_elements_s.nextval
1305 ,p_business_group_id
1306 ,p_organization_id_parent
1307 ,p_org_structure_version_id
1308 ,p_organization_id_child
1309 );
1310 --
1311 return(1);
1312 end create_eeo_hierarchy_element;
1313 --
1314 --
1315 --
1316 --
1317 -- create_company_state_rules added by AKELLY
1318 --
1319 PROCEDURE create_company_state_rules
1320 (p_legal_company_id IN NUMBER
1321 ,p_state_code IN VARCHAR2
1322 ,p_sui_company_state_id IN VARCHAR2
1323 ,p_sit_company_state_id IN VARCHAR2
1324 ,p_sui_self_adjust_method IN VARCHAR2 DEFAULT null
1325 ,p_sdi_self_adjust_method IN VARCHAR2 DEFAULT null
1326 ,p_sui_er_experience_rate_1 IN VARCHAR2 DEFAULT null
1327 ,p_sui_er_experience_rate_2 IN VARCHAR2 DEFAULT null
1328 ,p_wc_carrier_name IN VARCHAR2 DEFAULT null
1329 ,p_employers_liability_rate IN VARCHAR2 DEFAULT null
1330 ,p_experience_modification_rate IN VARCHAR2 DEFAULT null
1331 ,p_premium_discount_rate IN VARCHAR2 DEFAULT null
1332 )
1333 IS
1334 l_org_information_id NUMBER;
1335 --
1336 begin
1337 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
1338 p_legal_company_id
1339 ,'State Tax Rules' -- context
1340 ,p_state_code
1341 ,p_sui_company_state_id
1342 ,p_sit_company_state_id
1343 ,p_sui_self_adjust_method
1344 ,p_sdi_self_adjust_method
1345 ,p_sui_er_experience_rate_1
1346 ,p_sui_er_experience_rate_2
1347 ,p_wc_carrier_name
1348 ,p_employers_liability_rate
1349 ,p_experience_modification_rate
1350 ,p_premium_discount_rate
1351 ,null
1352 ,null
1353 ,null
1354 ,null
1355 ,null
1356 ,null
1357 ,null
1358 ,null
1359 ,null);
1360 end create_company_state_rules;
1361 --
1362 --
1363 FUNCTION create_job
1364 (p_default VARCHAR2 DEFAULT 'Y'
1365 ,p_name VARCHAR2
1366 ,p_business_group VARCHAR2
1367 ,p_date_from DATE
1368 ,p_date_to DATE DEFAULT null
1369 ,p_segment1 VARCHAR2 DEFAULT null
1370 ,p_segment2 VARCHAR2 DEFAULT null
1371 ,p_segment3 VARCHAR2 DEFAULT null
1372 ,p_segment4 VARCHAR2 DEFAULT null
1373 ,p_segment5 VARCHAR2 DEFAULT null
1374 ,p_segment6 VARCHAR2 DEFAULT null
1375 ,p_segment7 VARCHAR2 DEFAULT null
1376 ,p_segment8 VARCHAR2 DEFAULT null
1377 ,p_segment9 VARCHAR2 DEFAULT null
1378 ,p_segment10 VARCHAR2 DEFAULT null
1379 ,p_segment11 VARCHAR2 DEFAULT null
1380 ,p_segment12 VARCHAR2 DEFAULT null
1381 ,p_segment13 VARCHAR2 DEFAULT null
1382 ,p_segment14 VARCHAR2 DEFAULT null
1383 ,p_segment15 VARCHAR2 DEFAULT null
1384 ,p_segment16 VARCHAR2 DEFAULT null
1385 ,p_segment17 VARCHAR2 DEFAULT null
1386 ,p_segment18 VARCHAR2 DEFAULT null
1387 ,p_segment19 VARCHAR2 DEFAULT null
1388 ,p_segment20 VARCHAR2 DEFAULT null
1389 ,p_segment21 VARCHAR2 DEFAULT null
1390 ,p_segment22 VARCHAR2 DEFAULT null
1391 ,p_segment23 VARCHAR2 DEFAULT null
1392 ,p_segment24 VARCHAR2 DEFAULT null
1393 ,p_segment25 VARCHAR2 DEFAULT null
1394 ,p_segment26 VARCHAR2 DEFAULT null
1395 ,p_segment27 VARCHAR2 DEFAULT null
1396 ,p_segment28 VARCHAR2 DEFAULT null
1397 ,p_segment29 VARCHAR2 DEFAULT null
1398 ,p_segment30 VARCHAR2 DEFAULT null
1399 ,p_context VARCHAR2 DEFAULT null
1400 ,p_eeo_category VARCHAR2 DEFAULT null
1401 ) return NUMBER
1402 --
1403 IS
1404 --
1405 -- local variables for create_job
1406 --
1407 l_job_id NUMBER;
1408 l_business_group_id NUMBER;
1409 l_structure_num NUMBER;
1410 l_segment1 VARCHAR2(60);
1411 l_job_definition_id NUMBER;
1412 --
1413 --
1414 PROCEDURE unique_name
1415 (p_business_group_id NUMBER, p_job_id VARCHAR2, p_job_name VARCHAR2)
1416 IS
1417 --
1418 job_check VARCHAR2(1);
1419 --
1420 begin
1421 --
1422 --
1423 SELECT 'Y'
1424 INTO job_check
1425 FROM per_jobs jb
1426 WHERE (jb.job_id <> p_job_id
1427 OR p_job_id IS NULL)
1428 AND p_job_name = jb.name
1429 AND p_business_group_id = jb.business_group_id + 0;
1430 --
1431 if job_check = 'Y' then
1435 --
1432 hr_utility.set_message(801,'PAY_7686_USER_JOB_TAB_UNIQUE');
1433 hr_utility.raise_error;
1434 end if;
1436 exception
1437 when NO_DATA_FOUND then null ;
1438 --
1439 --
1440 end unique_name;
1441 --
1442 FUNCTION insert_job_definition return NUMBER IS
1443 --
1444 l_job_definition_id NUMBER;
1445 --
1446 begin
1447 --
1448 hr_utility.set_location('pay_us_db_per_setup.insert_job_definition',1);
1449 --
1450 SELECT per_job_definitions_s.nextval
1451 INTO l_job_definition_id
1452 FROM sys.dual;
1453 --
1454 hr_utility.set_location('pay_us_db_per_setup.insert_job_definition',2);
1455 --
1456 INSERT INTO per_job_definitions
1457 (JOB_DEFINITION_ID
1458 ,ID_FLEX_NUM
1459 ,SUMMARY_FLAG
1460 ,ENABLED_FLAG
1461 ,START_DATE_ACTIVE
1462 ,END_DATE_ACTIVE
1463 ,SEGMENT1
1464 ,SEGMENT2
1465 ,SEGMENT3
1466 ,SEGMENT4
1467 ,SEGMENT5
1468 ,SEGMENT6
1469 ,SEGMENT7
1470 ,SEGMENT8
1471 ,SEGMENT9
1472 ,SEGMENT10
1473 ,SEGMENT11
1474 ,SEGMENT12
1475 ,SEGMENT13
1476 ,SEGMENT14
1477 ,SEGMENT15
1478 ,SEGMENT16
1479 ,SEGMENT17
1480 ,SEGMENT18
1481 ,SEGMENT19
1482 ,SEGMENT20
1483 ,SEGMENT21
1484 ,SEGMENT22
1485 ,SEGMENT23
1486 ,SEGMENT24
1487 ,SEGMENT25
1488 ,SEGMENT26
1489 ,SEGMENT27
1490 ,SEGMENT28
1491 ,SEGMENT29
1492 ,SEGMENT30)
1493 values
1494 (l_job_definition_id
1495 ,l_structure_num
1496 ,'Y'
1497 ,'N'
1498 ,fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_date_from))
1499 ,fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_date_to))
1500 ,l_segment1 -- p_name for default jobs
1501 ,p_segment2
1502 ,p_segment3
1503 ,p_segment4
1504 ,p_segment5
1505 ,p_segment6
1506 ,p_segment7
1507 ,p_segment8
1508 ,p_segment9
1509 ,p_segment10
1510 ,p_segment11
1511 ,p_segment12
1512 ,p_segment13
1513 ,p_segment14
1514 ,p_segment15
1515 ,p_segment16
1516 ,p_segment17
1517 ,p_segment18
1518 ,p_segment19
1519 ,p_segment20
1520 ,p_segment21
1521 ,p_segment22
1522 ,p_segment23
1523 ,p_segment24
1524 ,p_segment25
1525 ,p_segment26
1526 ,p_segment27
1527 ,p_segment28
1528 ,p_segment29
1529 ,p_segment30
1530 );
1531 --
1532 --
1533 return l_job_definition_id;
1534 --
1535 end insert_job_definition;
1536 --
1537 --
1538 FUNCTION insert_job RETURN NUMBER IS
1539 --
1540 l_job_id NUMBER;
1541 --
1542 begin
1543 --
1544 hr_utility.set_location('pay_us_db_per_setup.insert_job',1);
1545 --
1546 SELECT per_jobs_s.nextval
1547 INTO l_job_id
1548 FROM sys.dual;
1549 --
1550 hr_utility.set_location('pay_us_db_per_setup.insert_job',2);
1551 --
1552 unique_name(l_business_group_id,l_job_id, p_name);
1553 --
1554 hr_utility.set_location('pay_us_db_per_setup.insert_job',3);
1555 --
1556 INSERT INTO per_jobs
1557 (JOB_ID
1558 ,BUSINESS_GROUP_ID
1559 ,JOB_DEFINITION_ID
1560 ,DATE_FROM
1561 ,DATE_TO
1562 ,NAME
1563 ,LAST_UPDATE_DATE
1564 ,LAST_UPDATED_BY
1565 ,LAST_UPDATE_LOGIN
1566 ,CREATED_BY
1567 ,CREATION_DATE
1568 ,JOB_INFORMATION_CATEGORY
1569 ,JOB_INFORMATION1
1570 )
1571 values
1572 (l_job_id
1573 ,l_business_group_id
1574 ,l_job_definition_id
1575 ,p_date_from
1576 ,p_date_to
1577 ,p_name
1578 ,SYSDATE
1579 ,0
1580 ,0
1581 ,0
1582 ,SYSDATE
1583 ,p_context
1584 ,p_eeo_category
1585 );
1586 --
1587 --
1588 return l_job_id;
1589 --
1590 end insert_job;
1591 --
1592 begin -- FUNCTION create_job
1593 --
1594 --
1595 hr_utility.set_location('pay_us_db_per_setup.create_job',1);
1596 --
1597 SELECT business_group_id
1598 , job_structure
1599 INTO l_business_group_id
1600 , l_structure_num
1601 FROM per_business_groups
1602 WHERE name = p_business_group
1603 AND business_group_id + 0 <> 0;
1604 --
1605 --
1606 -- set SEGMENT1 to name if creating a default job
1607 --
1608 if p_default = 'Y' then
1609 l_segment1 := p_name ;
1610 else l_segment1 := p_segment1 ;
1611 end if;
1612 --
1613 l_job_definition_id := insert_job_definition;
1614 --
1615 l_job_id := insert_job;
1616 --
1617 return l_job_id;
1618 --
1619 --
1620 end create_job;
1621 --
1622 --
1623 FUNCTION create_position
1624 (p_default VARCHAR2 DEFAULT 'Y'
1625 ,p_name VARCHAR2
1626 ,p_business_group VARCHAR2
1627 ,p_date_effective DATE
1628 ,p_date_end DATE DEFAULT null
1629 ,p_job VARCHAR2
1630 ,p_organization VARCHAR2
1631 ,p_location VARCHAR2 DEFAULT null
1632 ,p_time_normal_start VARCHAR2 DEFAULT '08:00'
1636 ,p_probation_period VARCHAR2 DEFAULT null
1633 ,p_time_normal_finish VARCHAR2 DEFAULT '17:30'
1634 ,p_working_hours NUMBER DEFAULT 37.5
1635 ,p_frequency VARCHAR2 DEFAULT 'W'
1637 ,p_probation_units VARCHAR2 DEFAULT null
1638 ,p_relief_position VARCHAR2 DEFAULT null
1639 ,p_replacement_required VARCHAR2 DEFAULT 'N'
1640 ,p_successor_position VARCHAR2 DEFAULT null
1641 ,p_segment1 VARCHAR2 DEFAULT null
1642 ,p_segment2 VARCHAR2 DEFAULT null
1643 ,p_segment3 VARCHAR2 DEFAULT null
1644 ,p_segment4 VARCHAR2 DEFAULT null
1645 ,p_segment5 VARCHAR2 DEFAULT null
1646 ,p_segment6 VARCHAR2 DEFAULT null
1647 ,p_segment7 VARCHAR2 DEFAULT null
1648 ,p_segment8 VARCHAR2 DEFAULT null
1649 ,p_segment9 VARCHAR2 DEFAULT null
1650 ,p_segment10 VARCHAR2 DEFAULT null
1651 ,p_segment11 VARCHAR2 DEFAULT null
1652 ,p_segment12 VARCHAR2 DEFAULT null
1653 ,p_segment13 VARCHAR2 DEFAULT null
1654 ,p_segment14 VARCHAR2 DEFAULT null
1655 ,p_segment15 VARCHAR2 DEFAULT null
1656 ,p_segment16 VARCHAR2 DEFAULT null
1657 ,p_segment17 VARCHAR2 DEFAULT null
1658 ,p_segment18 VARCHAR2 DEFAULT null
1659 ,p_segment19 VARCHAR2 DEFAULT null
1660 ,p_segment20 VARCHAR2 DEFAULT null
1661 ,p_segment21 VARCHAR2 DEFAULT null
1662 ,p_segment22 VARCHAR2 DEFAULT null
1663 ,p_segment23 VARCHAR2 DEFAULT null
1664 ,p_segment24 VARCHAR2 DEFAULT null
1665 ,p_segment25 VARCHAR2 DEFAULT null
1666 ,p_segment26 VARCHAR2 DEFAULT null
1667 ,p_segment27 VARCHAR2 DEFAULT null
1668 ,p_segment28 VARCHAR2 DEFAULT null
1669 ,p_segment29 VARCHAR2 DEFAULT null
1670 ,p_segment30 VARCHAR2 DEFAULT null
1671 ,p_comments LONG DEFAULT NULL
1672 ) return NUMBER
1673 --
1674 IS
1675 --
1676 -- local variables for create_position
1677 --
1678 l_position_id NUMBER;
1679 l_business_group_id NUMBER;
1680 l_structure_num NUMBER;
1681 l_segment1 VARCHAR2(60);
1682 l_job_id NUMBER;
1683 l_organization_id NUMBER;
1684 l_location_id NUMBER;
1685 l_position_definition_id NUMBER;
1686 l_relief_position_id NUMBER;
1687 l_successor_position_id NUMBER;
1688 --
1689 --
1690 PROCEDURE unique_name
1691 (p_business_group_id NUMBER, p_position_id VARCHAR2, p_position_name VARCHAR2)
1692 IS
1693 --
1694 pos_check VARCHAR2(1);
1695 --
1696 begin
1697 --
1698 --
1699 SELECT 'Y'
1700 INTO pos_check
1701 FROM per_positions pos
1702 WHERE (pos.position_id <> p_position_id
1703 OR p_position_id IS NULL)
1704 AND p_position_name = pos.name
1705 AND p_business_group_id = pos.business_group_id + 0;
1706 --
1707 if pos_check = 'Y' then
1708 hr_utility.set_message(801,'PAY_7688_USER_POS_TAB_UNIQUE');
1709 hr_utility.raise_error;
1710 end if;
1711 --
1712 exception
1713 when NO_DATA_FOUND then null ;
1714 --
1715 --
1716 end unique_name;
1717 --
1718 FUNCTION insert_pos_definition return NUMBER IS
1719 --
1720 l_pos_definition_id NUMBER;
1721 --
1722 begin
1723 --
1724 hr_utility.set_location('pay_us_db_per_setup.insert_pos_definition',1);
1725 --
1726 SELECT per_position_definitions_s.nextval
1727 INTO l_pos_definition_id
1728 FROM sys.dual;
1729 --
1730 hr_utility.set_location('pay_us_db_per_setup.insert_pos_definition',2);
1731 --
1732 INSERT INTO per_position_definitions
1733 (POSITION_DEFINITION_ID
1734 ,ID_FLEX_NUM
1735 ,SUMMARY_FLAG
1736 ,ENABLED_FLAG
1737 ,START_DATE_ACTIVE
1738 ,END_DATE_ACTIVE
1739 ,SEGMENT1
1740 ,SEGMENT2
1741 ,SEGMENT3
1742 ,SEGMENT4
1743 ,SEGMENT5
1744 ,SEGMENT6
1745 ,SEGMENT7
1746 ,SEGMENT8
1747 ,SEGMENT9
1748 ,SEGMENT10
1749 ,SEGMENT11
1750 ,SEGMENT12
1751 ,SEGMENT13
1752 ,SEGMENT14
1753 ,SEGMENT15
1754 ,SEGMENT16
1755 ,SEGMENT17
1756 ,SEGMENT18
1757 ,SEGMENT19
1758 ,SEGMENT20
1759 ,SEGMENT21
1760 ,SEGMENT22
1761 ,SEGMENT23
1762 ,SEGMENT24
1763 ,SEGMENT25
1764 ,SEGMENT26
1765 ,SEGMENT27
1766 ,SEGMENT28
1767 ,SEGMENT29
1768 ,SEGMENT30)
1769 values
1770 (l_pos_definition_id
1771 ,l_structure_num
1772 ,'Y'
1773 ,'N'
1774 ,p_date_effective
1775 ,p_date_end
1776 ,l_segment1 -- p_name for a default position
1777 ,p_segment2
1778 ,p_segment3
1779 ,p_segment4
1780 ,p_segment5
1781 ,p_segment6
1782 ,p_segment7
1783 ,p_segment8
1784 ,p_segment9
1785 ,p_segment10
1786 ,p_segment11
1787 ,p_segment12
1788 ,p_segment13
1789 ,p_segment14
1790 ,p_segment15
1791 ,p_segment16
1792 ,p_segment17
1793 ,p_segment18
1794 ,p_segment19
1795 ,p_segment20
1796 ,p_segment21
1797 ,p_segment22
1798 ,p_segment23
1799 ,p_segment24
1803 ,p_segment28
1800 ,p_segment25
1801 ,p_segment26
1802 ,p_segment27
1804 ,p_segment29
1805 ,p_segment30
1806 );
1807 --
1808 --
1809 return l_pos_definition_id;
1810 --
1811 end insert_pos_definition;
1812 --
1813 --
1814 FUNCTION insert_position RETURN NUMBER IS
1815 --
1816 l_position_id NUMBER;
1817 --
1818 begin
1819 --
1820 hr_utility.set_location('pay_us_db_per_setup.insert_position',1);
1821 --
1822 SELECT per_positions_s.nextval
1823 INTO l_position_id
1824 FROM sys.dual;
1825 --
1826 hr_utility.set_location('pay_us_db_per_setup.insert_position',2);
1827 --
1828 unique_name(l_business_group_id, l_position_id, p_name);
1829 --
1830 hr_utility.set_location('pay_us_db_per_setup.insert_position',3);
1831 --
1832 INSERT INTO per_positions
1833 (POSITION_ID
1834 ,BUSINESS_GROUP_ID
1835 ,JOB_ID
1836 ,ORGANIZATION_ID
1837 ,LOCATION_ID
1838 ,POSITION_DEFINITION_ID
1839 ,DATE_EFFECTIVE
1840 ,DATE_END
1841 ,FREQUENCY
1842 ,NAME
1843 ,PROBATION_PERIOD
1844 ,PROBATION_PERIOD_UNITS
1845 ,RELIEF_POSITION_ID
1846 ,REPLACEMENT_REQUIRED_FLAG
1847 ,SUCCESSOR_POSITION_ID
1848 ,TIME_NORMAL_FINISH
1849 ,TIME_NORMAL_START
1850 ,WORKING_HOURS
1851 ,LAST_UPDATE_DATE
1852 ,LAST_UPDATED_BY
1853 ,LAST_UPDATE_LOGIN
1854 ,CREATED_BY
1855 ,CREATION_DATE
1856 ,COMMENTS)
1857 values
1858 (l_position_id
1859 ,l_business_group_id
1860 ,l_job_id
1861 ,l_organization_id
1862 ,l_location_id
1863 ,l_position_definition_id
1864 ,p_date_effective
1865 ,p_date_end
1866 ,p_frequency
1867 ,p_name
1868 ,p_probation_period
1869 ,p_probation_units
1870 ,l_relief_position_id
1871 ,p_replacement_required
1872 ,l_successor_position_id
1873 ,p_time_normal_finish
1874 ,p_time_normal_start
1875 ,p_working_hours
1876 ,SYSDATE
1877 ,0
1878 ,0
1879 ,0
1880 ,SYSDATE
1881 ,p_comments);
1882 --
1883 --
1884 return l_position_id;
1885 --
1886 end insert_position;
1887 --
1888 begin -- FUNCTION create_position
1889 --
1890 --
1891 --
1892 hr_utility.set_location('pay_us_db_per_setup.create_position',1);
1893 --
1894 SELECT business_group_id
1895 , position_structure
1896 INTO l_business_group_id
1897 , l_structure_num
1898 FROM per_business_groups
1899 WHERE name = p_business_group
1900 AND business_group_id + 0 <> 0;
1901 --
1902 hr_utility.set_location('pay_us_db_per_setup.create_position',2);
1903 --
1904 SELECT job_id
1905 INTO l_job_id
1906 FROM per_jobs
1907 WHERE name = p_job
1908 AND business_group_id + 0 = l_business_group_id;
1909 --
1910 hr_utility.set_location('pay_us_db_per_setup.create_position',3);
1911 --
1912 SELECT organization_id
1913 INTO l_organization_id
1914 FROM per_organization_units
1915 WHERE name = p_organization
1916 AND business_group_id + 0 = l_business_group_id;
1917 --
1918 begin
1919 --
1920 hr_utility.set_location('pay_us_db_per_setup.create_position',4);
1921 --
1922 SELECT location_id
1923 INTO l_location_id
1924 FROM hr_locations
1925 WHERE location_code = p_location;
1926 --
1927 hr_utility.set_location('pay_us_db_per_setup.create_position',5);
1928 --
1929 SELECT position_id
1930 INTO l_relief_position_id
1931 FROM per_positions
1932 WHERE name = p_relief_position;
1933 --
1934 hr_utility.set_location('pay_us_db_per_setup.create_position',6);
1935 --
1936 SELECT position_id
1937 INTO l_successor_position_id
1938 FROM per_positions
1939 WHERE name = p_successor_position;
1940 --
1941 exception when NO_DATA_FOUND then null;
1942 end;
1943 --
1944 -- set SEGMENT1 to name if creating a default position
1945 --
1946 if p_default = 'Y' then
1947 l_segment1 := p_name ;
1948 else l_segment1 := p_segment1 ;
1949 end if;
1950 --
1951 l_position_definition_id := insert_pos_definition;
1952 --
1953 l_position_id := insert_position;
1954 --
1955 return l_position_id;
1956 --
1957 --
1958 end create_position;
1959 --
1960 --
1961 FUNCTION create_grade
1962 (p_default VARCHAR2 DEFAULT 'Y'
1963 ,p_name VARCHAR2
1964 ,p_business_group VARCHAR2
1965 ,p_date_from DATE
1966 ,p_date_to DATE DEFAULT null
1967 ,p_sequence VARCHAR2 DEFAULT null
1968 ,p_segment1 VARCHAR2 DEFAULT null
1969 ,p_segment2 VARCHAR2 DEFAULT null
1970 ,p_segment3 VARCHAR2 DEFAULT null
1971 ,p_segment4 VARCHAR2 DEFAULT null
1972 ,p_segment5 VARCHAR2 DEFAULT null
1973 ,p_segment6 VARCHAR2 DEFAULT null
1974 ,p_segment7 VARCHAR2 DEFAULT null
1975 ,p_segment8 VARCHAR2 DEFAULT null
1976 ,p_segment9 VARCHAR2 DEFAULT null
1977 ,p_segment10 VARCHAR2 DEFAULT null
1981 ,p_segment14 VARCHAR2 DEFAULT null
1978 ,p_segment11 VARCHAR2 DEFAULT null
1979 ,p_segment12 VARCHAR2 DEFAULT null
1980 ,p_segment13 VARCHAR2 DEFAULT null
1982 ,p_segment15 VARCHAR2 DEFAULT null
1983 ,p_segment16 VARCHAR2 DEFAULT null
1984 ,p_segment17 VARCHAR2 DEFAULT null
1985 ,p_segment18 VARCHAR2 DEFAULT null
1986 ,p_segment19 VARCHAR2 DEFAULT null
1987 ,p_segment20 VARCHAR2 DEFAULT null
1988 ,p_segment21 VARCHAR2 DEFAULT null
1989 ,p_segment22 VARCHAR2 DEFAULT null
1990 ,p_segment23 VARCHAR2 DEFAULT null
1991 ,p_segment24 VARCHAR2 DEFAULT null
1992 ,p_segment25 VARCHAR2 DEFAULT null
1993 ,p_segment26 VARCHAR2 DEFAULT null
1994 ,p_segment27 VARCHAR2 DEFAULT null
1995 ,p_segment28 VARCHAR2 DEFAULT null
1996 ,p_segment29 VARCHAR2 DEFAULT null
1997 ,p_segment30 VARCHAR2 DEFAULT null
1998 ) return NUMBER
1999 --
2000 IS
2001 --
2002 -- local variables for create_grade
2003 --
2004 l_grade_id NUMBER;
2005 l_business_group_id NUMBER;
2006 l_structure_num NUMBER;
2007 l_segment1 VARCHAR2(60);
2008 l_grade_definition_id NUMBER;
2009 --
2010 --
2011 --
2012 PROCEDURE unique_name
2013 (p_business_group_id NUMBER, p_grade_id VARCHAR2, p_grade_name VARCHAR2)
2014 IS
2015 --
2016 grd_check VARCHAR2(1);
2017 --
2018 begin
2019 --
2020 --
2021 SELECT 'Y'
2022 INTO grd_check
2023 FROM per_grades grd
2024 WHERE (grd.grade_id <> p_grade_id
2025 OR p_grade_id IS NULL)
2026 AND p_grade_name = grd.name
2027 AND p_business_group_id = grd.business_group_id + 0;
2028 --
2029 if grd_check = 'Y' then
2030 hr_utility.set_message(801,'PAY_7687_USER_GRADE_TAB_UNIQUE');
2031 hr_utility.raise_error;
2032 end if;
2033 --
2034 exception
2035 when NO_DATA_FOUND then null ;
2036 --
2037 --
2038 end unique_name;
2039 --
2040 --
2041 FUNCTION insert_grade_definition return NUMBER IS
2042 --
2043 l_grade_definition_id NUMBER;
2044 --
2045 begin
2046 --
2047 hr_utility.set_location('pay_us_db_per_setup.insert_grade_definition',1);
2048 --
2049 SELECT per_grade_definitions_s.nextval
2050 INTO l_grade_definition_id
2051 FROM sys.dual;
2052 --
2053 hr_utility.set_location('pay_us_db_per_setup.insert_grade_definition',2);
2054 --
2055 INSERT INTO per_grade_definitions
2056 (GRADE_DEFINITION_ID
2057 ,ID_FLEX_NUM
2058 ,SUMMARY_FLAG
2059 ,ENABLED_FLAG
2060 ,START_DATE_ACTIVE
2061 ,END_DATE_ACTIVE
2062 ,SEGMENT1
2063 ,SEGMENT2
2064 ,SEGMENT3
2065 ,SEGMENT4
2066 ,SEGMENT5
2067 ,SEGMENT6
2068 ,SEGMENT7
2069 ,SEGMENT8
2070 ,SEGMENT9
2071 ,SEGMENT10
2072 ,SEGMENT11
2073 ,SEGMENT12
2074 ,SEGMENT13
2075 ,SEGMENT14
2076 ,SEGMENT15
2077 ,SEGMENT16
2078 ,SEGMENT17
2079 ,SEGMENT18
2080 ,SEGMENT19
2081 ,SEGMENT20
2082 ,SEGMENT21
2083 ,SEGMENT22
2084 ,SEGMENT23
2085 ,SEGMENT24
2086 ,SEGMENT25
2087 ,SEGMENT26
2088 ,SEGMENT27
2089 ,SEGMENT28
2090 ,SEGMENT29
2091 ,SEGMENT30)
2092 values
2093 (l_grade_definition_id
2094 ,l_structure_num
2095 ,'Y'
2096 ,'N'
2097 ,p_date_from
2098 ,p_date_to
2099 ,l_segment1 -- p_name for default grades
2100 ,p_segment2
2101 ,p_segment3
2102 ,p_segment4
2103 ,p_segment5
2104 ,p_segment6
2105 ,p_segment7
2106 ,p_segment8
2107 ,p_segment9
2108 ,p_segment10
2109 ,p_segment11
2110 ,p_segment12
2111 ,p_segment13
2112 ,p_segment14
2113 ,p_segment15
2114 ,p_segment16
2115 ,p_segment17
2116 ,p_segment18
2117 ,p_segment19
2118 ,p_segment20
2119 ,p_segment21
2120 ,p_segment22
2121 ,p_segment23
2122 ,p_segment24
2123 ,p_segment25
2124 ,p_segment26
2125 ,p_segment27
2126 ,p_segment28
2127 ,p_segment29
2128 ,p_segment30
2129 );
2130 --
2131 --
2132 return l_grade_definition_id;
2133 --
2134 end insert_grade_definition;
2135 --
2136 --
2137 FUNCTION insert_grade RETURN NUMBER IS
2138 --
2139 l_grade_id NUMBER;
2140 --
2141 begin
2142 --
2143 hr_utility.set_location('pay_us_db_per_setup.insert_grade',1);
2144 --
2145 SELECT per_grades_s.nextval
2146 INTO l_grade_id
2147 FROM sys.dual;
2148 --
2149 hr_utility.set_location('pay_us_db_per_setup.insert_grade',2);
2150 --
2151 unique_name(l_business_group_id, l_grade_id, p_name);
2152 --
2153 hr_utility.set_location('pay_us_db_per_setup.insert_grade',3);
2154 --
2155 INSERT INTO per_grades
2156 (GRADE_ID
2157 ,BUSINESS_GROUP_ID
2158 ,GRADE_DEFINITION_ID
2159 ,DATE_FROM
2160 ,DATE_TO
2161 ,NAME
2162 ,SEQUENCE
2163 ,LAST_UPDATE_DATE
2164 ,LAST_UPDATED_BY
2168 SELECT
2165 ,LAST_UPDATE_LOGIN
2166 ,CREATED_BY
2167 ,CREATION_DATE)
2169 l_grade_id
2170 ,l_business_group_id
2171 ,l_grade_definition_id
2172 ,p_date_from
2173 ,p_date_to
2174 ,p_name
2175 ,nvl(p_sequence,nvl(max(pg.sequence),0) + 1)
2176 ,SYSDATE
2177 ,0
2178 ,0
2179 ,0
2180 ,SYSDATE
2181 FROM per_grades pg
2182 WHERE pg.business_group_id + 0 = l_business_group_id ;
2183 --
2184 --
2185 return l_grade_id;
2186 --
2187 end insert_grade;
2188 --
2189 begin -- FUNCTION create_grade
2190 --
2191 --
2192 hr_utility.set_location('pay_us_db_per_setup.create_grade',1);
2193 --
2194 SELECT business_group_id
2195 , grade_structure
2196 INTO l_business_group_id
2197 , l_structure_num
2198 FROM per_business_groups
2199 WHERE name = p_business_group
2200 AND business_group_id + 0 <> 0;
2201 --
2202 --
2203 -- set SEGMENT1 to name if creating a default grade
2204 --
2205 if p_default = 'Y' then
2206 l_segment1 := p_name ;
2207 else l_segment1 := p_segment1 ;
2208 end if;
2209 --
2210 l_grade_definition_id := insert_grade_definition;
2211 --
2212 l_grade_id := insert_grade;
2213 --
2214 return l_grade_id;
2215 --
2216 --
2217 end create_grade;
2218 --
2219 FUNCTION INSERT_WC_FUND( P_BUSINESS_GROUP_ID NUMBER,
2220 P_CARRIER_ID NUMBER,
2221 P_LOCATION_ID NUMBER DEFAULT NULL,
2222 P_STATE_CODE VARCHAR2) return NUMBER IS
2223 --
2224 l_fund_id number(15);
2225 BEGIN
2226 --
2227 hr_utility.trace('Entered insert_wc_fund');
2228 hr_utility.set_location('scltstdat.insert_wc_fund',1);
2229 --
2230 SELECT PAY_WC_FUNDS_S.NEXTVAL
2231 INTO l_fund_id
2232 FROM DUAL;
2233 --
2234 hr_utility.set_location('scltstdat.insert_wc_fund',5);
2235 --
2236 IF (SQL%NOTFOUND) THEN
2237 RAISE NO_DATA_FOUND;
2238 END IF;
2239 --
2240 hr_utility.set_location('scltstdat.insert_wc_fund',10);
2241 --
2242 INSERT INTO PAY_WC_FUNDS
2243 (FUND_ID, BUSINESS_GROUP_ID, CARRIER_ID, LOCATION_ID, STATE_CODE)
2244 VALUES
2245 (L_FUND_ID, P_BUSINESS_GROUP_ID, P_CARRIER_ID, P_LOCATION_ID,
2246 P_STATE_CODE);
2247 --
2248 hr_utility.set_location('scltstdat.insert_wc_fund',15);
2249 --
2250 return l_fund_id;
2251 --
2252 END INSERT_WC_FUND;
2253 --
2254 --
2255 FUNCTION insert_wc_rate
2256 (p_fund_id NUMBER
2257 ,p_business_group_id NUMBER
2258 ,p_rate NUMBER
2259 ) return NUMBER
2260 IS
2261 --
2262 l_wc_code NUMBER;
2263 --
2264 BEGIN
2265 --
2266 hr_utility.trace('Entered insert_wc_rate');
2267 hr_utility.set_location('scltstdat.insert_wc_rate',1);
2268 --
2269 SELECT PAY_WC_RATES_S.NEXTVAL
2270 INTO l_wc_code
2271 FROM DUAL;
2272 --
2273 hr_utility.set_location('scltstdat.insert_wc_rate',5);
2274 --
2275 IF (SQL%NOTFOUND) THEN
2276 RAISE NO_DATA_FOUND;
2277 END IF;
2278 --
2279 hr_utility.set_location('scltstdat.insert_wc_rate',10);
2280 --
2281 INSERT INTO
2282 pay_wc_rates
2283 (wc_code
2284 ,fund_id
2285 ,business_group_id
2286 ,rate
2287 ,created_by
2288 ,creation_date
2289 ,last_updated_by
2290 ,last_update_date
2291 ,last_update_login )
2292 VALUES
2293 (l_wc_code
2294 ,p_fund_id
2295 ,p_business_group_id
2296 ,p_rate
2297 ,0
2298 ,sysdate
2299 ,0
2300 ,sysdate
2301 ,0 );
2302 --
2303 hr_utility.set_location('scltstdat.insert_wc_rate',15);
2304 --
2305 return l_wc_code;
2306 --
2307 end insert_wc_rate;
2308 --
2309 --
2310 FUNCTION create_us_location
2311 ( p_location_code VARCHAR2
2312 ,p_address_line_1 VARCHAR2
2313 ,p_address_line_2 VARCHAR2 default null
2314 ,p_address_line_3 VARCHAR2 default null
2315 ,p_town_or_city VARCHAR2 default null
2316 ,p_county VARCHAR2 default null
2317 ,p_state VARCHAR2 default null
2318 ,p_zip_code VARCHAR2 default null
2319 ,p_telephone VARCHAR2 default null
2320 ,p_fax VARCHAR2 default null
2321 ) return NUMBER
2322 IS
2323 --
2324 l_location_id NUMBER(15);
2325 --
2326 BEGIN
2327 --
2328 hr_utility.trace('Entered Create_us_location');
2329 hr_utility.set_location('scltstdat.create_us_location',1);
2330 --
2331 select hr_locations_s.nextval
2332 into l_location_id
2333 from sys.dual;
2334 --
2335 hr_utility.set_location('scltstdat.create_us_location',5);
2336 --
2337 INSERT INTO hr_locations
2338 (location_id
2339 ,entered_by
2340 ,location_code
2341 ,address_line_1
2342 ,address_line_2
2343 ,address_line_3
2344 ,town_or_city
2345 ,region_1
2346 ,region_2
2347 ,postal_code
2348 ,telephone_number_1
2349 ,telephone_number_2
2350 ,country
2351 ,style
2352 ,created_by
2353 ,creation_date
2354 ,last_updated_by
2355 ,last_update_date
2356 ,last_update_login
2357 )
2358 values
2359 (l_location_id
2360 ,0
2361 ,p_location_code
2362 ,p_address_line_1
2363 ,p_address_line_2
2364 ,p_address_line_3
2365 ,p_town_or_city
2366 ,p_county
2367 ,p_state
2368 ,p_zip_code
2369 ,p_telephone
2370 ,p_fax
2371 ,'US'
2372 ,'US'
2373 ,0
2374 ,sysdate
2375 ,0
2376 ,sysdate
2377 ,0 );
2378 --
2379 hr_utility.set_location('scltstdat.create_us_location',10);
2380 --
2381 return l_location_id;
2382 --
2383 end create_us_location;
2384 --
2385 --
2386 FUNCTION create_wc_carrier
2387 ( P_NAME VARCHAR2
2388 ,P_BUSINESS_GROUP VARCHAR2
2389 ,P_DATE_FROM DATE
2390 ,P_DATE_TO DATE DEFAULT null
2391 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
2392 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
2393 ,P_TYPE VARCHAR2 DEFAULT null
2394 ,P_LOCATION_ID NUMBER DEFAULT null
2395 ) return NUMBER
2396 IS
2397 --
2398 l_wc_carrier_id NUMBER;
2399 l_org_information_id NUMBER;
2400 l_business_group_id NUMBER;
2401 --
2402 begin
2403 --
2404 --
2405 hr_utility.trace('Entered Create_wc_carrier');
2406 hr_utility.set_location('scltstdat.create_wc_carrier',1);
2407 --
2408 SELECT business_group_id
2409 INTO l_business_group_id
2410 FROM per_business_groups
2411 WHERE name = p_business_group;
2412 --
2413 l_wc_carrier_id := pay_us_db_per_setup.insert_organization_unit
2414 ('N'
2415 ,p_name
2416 ,l_business_group_id
2417 ,null
2418 ,p_location_id
2419 ,null
2420 ,p_date_from
2421 ,p_date_to
2422 ,p_internal_external_flag
2423 ,p_internal_address_line
2424 ,p_type);
2425 hr_utility.trace('Called insert_information_unit from create_wc_carrier');
2426 --
2427 --
2428 l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
2429 (l_wc_carrier_id
2430 ,'CLASS'
2431 ,'US_WC_CARRIER'
2432 ,'Y'
2433 ,null
2434 ,null
2435 ,null
2436 ,null
2437 ,null
2438 ,null
2439 ,null
2440 ,null
2441 ,null
2442 ,null
2443 ,null
2444 ,null
2445 ,null
2446 ,null
2447 ,null
2448 ,null
2449 ,null
2450 ,null);
2451 hr_utility.trace('Called create_wc_carrier');
2452 --
2453 --
2454 return l_wc_carrier_id;
2455 --
2456 end create_wc_carrier;
2457 --
2458 --
2459 end pay_us_db_per_setup;