[Home] [Help]
PACKAGE BODY: APPS.PER_DB_PER_SETUP
Source
1 PACKAGE BODY per_db_per_setup AS
2 /* $Header: pesetupd.pkb 115.6 1999/11/16 06:47:46 pkm 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 : per_db_per_setup (BODY)
24
25 Description : This package defines the procedures required to
26 create all 'set up' entities in Personnel.
27 That is:
28
29 Business Groups,
30 HR Organizations,
31 Legal Companies,
32 Positions,
33 Jobs and
34 Grades.
35
36 It is used during regression testing
37
38 Change List
39 -----------
40
41 Version Date Author ER/CR No. Description of Change
42 -------+---------+----------+---------+--------------------------
43 70.0 19-NOV-92 SZWILLIA Date Created
44 70.2 30-DEC-92 SZWILLIA Added Person Building blocks
45 70.3 04-JAN-93 SZWILLIA create_applicant and private
46 function insert_application
47 70.4 05-JAN-93 SZWILLIA Correction for applicant
48 assignments
49 70.5 11-JAN-93 SZWILLIA Corrected date defaulting
50 70.6 11-JAN-93 SZWILLIA Changed interface to create
51 business group to accept
52 structure names not numbers
53 70.9 20-JAN-93 SZWILLIA Corrected error handling.
54 70.12 09-MAR-93 SZWILLIA Made insert_org_information
55 public and added error checking.
56 70.13 11-MAR-93 NKHAN Added 'exit' to the end
57 70.17 25-AUG-93 PGOLDTHO Reference to ORG_CLASSIFICATIONS
58 changed to read ORG_CLASS.
59 70.20 23-NOV-94 RFINE Suppressed index on business_group_id
60 70.20 25-JUL-95 AForte Changed tokenised message
61 PAY_6361_USER_TABLE_UNIQUE
62 to hard coded message
63 PAY_7686_USER_JOB_TAB_UNIQUE,
64 PAY_7683_USER_POS_TABLE_UNIQUE,
65 PAY_7687_USER_GRADE_TAB_UNIQUE
66 70.23 17-NOV-95 JTHURING Removed ampersand from change history -
67 this was causing an
68 "expected symbol name is missing" error
69 70.24 02-JUL-96 DKERR Year2000:Made date_from/date_to
70 parameters on insert_organization_unit
71 dates instead of varchar2
72 110.1 05-AUG-97 ALOGUE Change fnd_id_flex_structures
73 to fnd_id_flex_structures_vl
74 115.1 19-NOV-98 ALOGUE Change to insert_organization_unit
75 to insert into hr_all_organization_units
76 and hr_all_organization_units_tl.
77 115.2 30-NOV-98 ALOGUE Amend change to insert_organization_unit
78 as business_group_id not in
79 HR_ALL_ORGANIZATION_UNITS_TL.
80 115.4 14-May-19099 mmillmor multi-radix fix to working_hours on position
81 115.5 05-Oct-1999 SCNair Date track position related changes
82 115.6 16-Nov-1999 pzwalker changed call
83 hr_dt_position_api.create_dt_position
84 to hr_position_api.create_position
85 ================================================================= */
86 --
87 --
88 FUNCTION insert_organization_unit
89 ( P_GROUP VARCHAR2
90 ,P_NAME VARCHAR2
91 ,P_BUSINESS_GROUP_ID NUMBER
92 ,P_COST_ALLOCATION_KEYFLEX_ID NUMBER
93 ,P_LOCATION_ID NUMBER
94 ,P_SOFT_CODING_KEYFLEX_ID NUMBER
95 ,P_DATE_FROM DATE
96 ,P_DATE_TO DATE
97 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2
98 ,P_INTERNAL_ADDRESS_LINE VARCHAR2
99 ,P_TYPE VARCHAR2
100 ) return NUMBER
101 IS
102 --
103 l_organization_id NUMBER;
104 language VARCHAR2(4);
105 --
106 CURSOR get_languages IS
107 SELECT language_code
108 from fnd_languages
109 where installed_flag in ('I','B');
110 --
111 begin
112 --
113 if p_group = 'Y' then null;
114 elsif p_business_group_id IS NULL then
115 hr_utility.set_message(801,'HR_MANDATORY_PARAMETER');
116 hr_utility.set_message_token('PARAMETER_NAME','Business Group');
117 hr_utility.raise_error;
118 end if;
119 --
120 hr_utility.set_location('per_db_per_setup.insert_organization_unit',1);
121 --
122 SELECT hr_organization_units_s.nextval
123 INTO l_organization_id
124 FROM sys.dual ;
125 --
126 hr_organization.unique_name(p_business_group_id,l_organization_id,p_name);
127 --
128 --
129 hr_organization.date_range(nvl(p_date_from,trunc(SYSDATE)),p_date_to);
130 --
131 --
132 --
133 hr_utility.set_location('per_db_per_setup.insert_organization_unit',2);
134 --
135 INSERT INTO HR_ALL_ORGANIZATION_UNITS
136 (organization_id
137 ,business_group_id
138 ,cost_allocation_keyflex_id
139 ,location_id
140 ,soft_coding_keyflex_id
141 ,date_from
142 ,name
143 ,date_to
144 ,internal_external_flag
145 ,internal_address_line
146 ,type
147 ,last_update_date
148 ,last_updated_by
149 ,last_update_login
150 ,created_by
151 ,creation_date)
152 SELECT l_organization_id
153 ,DECODE(p_group,'Y',l_organization_id,p_business_group_id)
154 ,p_cost_allocation_keyflex_id
155 ,p_location_id
156 ,p_soft_coding_keyflex_id
157 ,nvl(p_date_from,trunc(SYSDATE))
158 ,p_name
159 ,p_date_to
160 ,p_internal_external_flag
161 ,p_internal_address_line
162 ,p_type
163 ,SYSDATE
164 ,0
165 ,0
166 ,0
167 ,SYSDATE
168 FROM sys.dual ;
169 --
170 OPEN get_languages;
171 --
172 LOOP
173 --
174 FETCH get_languages
175 INTO language;
176 EXIT WHEN get_languages%NOTFOUND;
177 --
178 INSERT INTO HR_ALL_ORGANIZATION_UNITS_TL
179 (organization_id
180 ,language
181 ,source_lang
182 ,name
183 ,last_update_date
184 ,last_updated_by
185 ,last_update_login
186 ,created_by
187 ,creation_date)
188 SELECT l_organization_id
189 ,language
190 ,userenv('LANG')
191 ,p_name
192 ,SYSDATE
193 ,0
194 ,0
195 ,0
196 ,SYSDATE
197 FROM sys.dual ;
198 --
199 END LOOP;
200 --
201 --
202 return l_organization_id;
203 --
204 --
205 end insert_organization_unit;
206 --
207 --
208 FUNCTION insert_org_information
209 ( P_ORGANIZATION_ID NUMBER
210 ,P_ORG_INFORMATION_CONTEXT VARCHAR2
211 ,P_ORG_INFORMATION1 VARCHAR2
212 ,P_ORG_INFORMATION2 VARCHAR2 DEFAULT null
213 ,P_ORG_INFORMATION3 VARCHAR2 DEFAULT null
214 ,P_ORG_INFORMATION4 VARCHAR2 DEFAULT null
215 ,P_ORG_INFORMATION5 VARCHAR2 DEFAULT null
216 ,P_ORG_INFORMATION6 VARCHAR2 DEFAULT null
217 ,P_ORG_INFORMATION7 VARCHAR2 DEFAULT null
218 ,P_ORG_INFORMATION8 VARCHAR2 DEFAULT null
219 ,P_ORG_INFORMATION9 VARCHAR2 DEFAULT null
220 ,P_ORG_INFORMATION10 VARCHAR2 DEFAULT null
221 ,P_ORG_INFORMATION11 VARCHAR2 DEFAULT null
222 ,P_ORG_INFORMATION12 VARCHAR2 DEFAULT null
223 ,P_ORG_INFORMATION13 VARCHAR2 DEFAULT null
224 ,P_ORG_INFORMATION14 VARCHAR2 DEFAULT null
225 ,P_ORG_INFORMATION15 VARCHAR2 DEFAULT null
226 ,P_ORG_INFORMATION16 VARCHAR2 DEFAULT null
227 ,P_ORG_INFORMATION17 VARCHAR2 DEFAULT null
228 ,P_ORG_INFORMATION18 VARCHAR2 DEFAULT null
229 ,P_ORG_INFORMATION19 VARCHAR2 DEFAULT null
230 ,P_ORG_INFORMATION20 VARCHAR2 DEFAULT null
231 ) return NUMBER
232 IS
233 --
234 l_org_information_id NUMBER;
235 l_organization_id NUMBER;
236 l_org_information1 VARCHAR2(150);
237 l_org_information_type VARCHAR2(40);
238 l_check VARCHAR2(1);
239 --
240 begin
241 --
242 hr_utility.trace('Entered insert_org_information');
243 --
244 hr_utility.set_location('per_db_per_setup.insert_org_information',1);
245 if p_organization_id IS NULL
246 OR p_org_information_context IS NULL
247 OR p_org_information1 IS NULL
248 then
249 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
250 hr_utility.set_message_token('PROCEDURE','insert_org_information');
251 hr_utility.set_message_token('STEP','1');
252 hr_utility.raise_error;
253 end if;
254 --
255 hr_utility.set_location('per_db_per_setup.insert_org_information',5);
256 SELECT organization_id
257 INTO l_organization_id
258 FROM hr_organization_units
259 WHERE organization_id = p_organization_id;
260 --
261 hr_utility.set_location('per_db_per_setup.insert_org_information',8);
262 if p_org_information_context = 'CLASS' then
263 SELECT lookup_code
264 INTO l_org_information1
265 FROM hr_lookups
266 WHERE lookup_type = 'ORG_CLASS'
267 AND lookup_code = p_org_information1;
268 --
269 l_org_information_type := p_org_information_context;
270 else
271 l_org_information1 := p_org_information1;
272 begin
273 hr_utility.set_location('per_db_per_setup.insert_org_information',10);
274 SELECT oit.org_information_type
275 INTO l_org_information_type
276 FROM hr_organization_information hoi
277 , hr_org_info_types_by_class tbc
278 , hr_org_information_types oit
279 WHERE oit.org_information_type = p_org_information_context
280 AND oit.org_information_type = tbc.org_information_type
281 AND tbc.org_classification = hoi.org_information1
282 AND hoi.org_information_context = 'CLASS'
283 AND hoi.organization_id = l_organization_id;
284 --
285 exception when NO_DATA_FOUND then
286 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
287 hr_utility.set_message_token('PROCEDURE','insert_org_information');
288 hr_utility.set_message_token('STEP','10');
289 hr_utility.raise_error;
290 end;
291 --
292 end if;
293 --
294 begin
295 hr_utility.set_location('per_db_per_setup.insert_org_information',15);
296 SELECT 'Y'
297 INTO l_check
298 FROM hr_organization_information
299 WHERE organization_id = l_organization_id
300 AND org_information_context = l_org_information_type
301 AND org_information1 = l_org_information1;
302 --
303 if l_check = 'Y' then
304 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
305 hr_utility.set_message_token('PROCEDURE','insert_org_information');
306 hr_utility.set_message_token('STEP','15');
307 hr_utility.raise_error;
308 end if;
309 --
310 exception when NO_DATA_FOUND then null;
311 end;
312 --
313 hr_utility.set_location('per_db_per_setup.insert_org_information',20);
314 SELECT hr_organization_information_s.nextval
315 INTO l_org_information_id
316 FROM sys.dual;
317 --
318 hr_utility.set_location('per_db_per_setup.insert_org_information',25);
319 INSERT INTO HR_ORGANIZATION_INFORMATION
320 (org_information_id
321 ,organization_id
322 ,attribute_category
323 ,org_information_context
324 ,org_information1
325 ,org_information2
326 ,org_information3
327 ,org_information4
328 ,org_information5
329 ,org_information6
330 ,org_information7
331 ,org_information8
332 ,org_information9
333 ,org_information10
334 ,org_information11
335 ,org_information12
336 ,org_information13
337 ,org_information14
338 ,org_information15
339 ,org_information16
340 ,org_information17
341 ,org_information18
342 ,org_information19
343 ,org_information20
344 ,last_update_date
345 ,last_updated_by
346 ,last_update_login
347 ,created_by
348 ,creation_date)
349 SELECT l_org_information_id
350 ,hou.organization_id
351 ,p_org_information_context
352 ,p_org_information_context
353 ,p_org_information1
354 ,p_org_information2
355 ,p_org_information3
356 ,p_org_information4
357 ,p_org_information5
358 ,p_org_information6
359 ,p_org_information7
360 ,p_org_information8
361 ,p_org_information9
362 ,p_org_information10
363 ,p_org_information11
364 ,p_org_information12
365 ,p_org_information13
366 ,p_org_information14
367 ,p_org_information15
368 ,p_org_information16
369 ,p_org_information17
370 ,p_org_information18
371 ,p_org_information19
372 ,p_org_information20
373 ,hou.last_update_date
374 ,hou.last_updated_by
375 ,hou.last_update_login
376 ,hou.created_by
377 ,hou.creation_date
378 FROM hr_organization_units hou
379 WHERE hou.organization_id = p_organization_id;
380 --
381 return l_org_information_id;
382 --
383 --
384 end insert_org_information;
385 --
386 --
387 FUNCTION create_business_group
388 ( P_GROUP VARCHAR2 DEFAULT 'Y'
389 ,P_NAME VARCHAR2
390 ,P_DATE_FROM DATE
391 ,P_DATE_TO DATE DEFAULT null
392 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
393 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
394 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
395 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
396 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
397 ,P_SHORT_NAME VARCHAR2
398 ,P_METHOD_OF_GENERATION_EMP_NUM VARCHAR2 DEFAULT 'A'
399 ,P_METHOD_OF_GENERATION_APL_NUM VARCHAR2 DEFAULT 'A'
400 ,P_GRADE_STRUCTURE VARCHAR2 DEFAULT 'Grade Volume Data'
401 ,P_PEOPLE_GROUP_STRUCTURE VARCHAR2
402 DEFAULT 'People Group Volume Data'
403 ,P_JOB_STRUCTURE VARCHAR2 DEFAULT 'Job Volume Data'
404 ,P_COST_ALLOCATION_STRUCTURE VARCHAR2
405 DEFAULT 'Cost Allocation Volume Data'
406 ,P_POSITION_STRUCTURE VARCHAR2 DEFAULT 'Position Volume Data'
407 ,P_LEGISLATION_CODE VARCHAR2 DEFAULT 'GB'
408 ,P_CURRENCY_CODE VARCHAR2 DEFAULT 'GBP'
409 ) return NUMBER
410 IS
411 --
412 l_business_group_id NUMBER;
413 l_org_information_id NUMBER;
414 l_position_num NUMBER;
415 l_grade_num NUMBER;
416 l_job_num NUMBER;
417 l_people_group_num NUMBER;
418 l_cost_allocation_num NUMBER;
419 --
420 begin
421 --
422 --
423 hr_utility.set_location('per_db_per_setup.create_business_group',1);
424 SELECT id_flex_num
425 INTO l_position_num
426 FROM fnd_id_flex_structures_vl
427 WHERE id_flex_code = 'POS'
428 AND id_flex_structure_name = p_position_structure;
429 --
430 hr_utility.set_location('per_db_per_setup.create_business_group',2);
431 SELECT id_flex_num
432 INTO l_grade_num
433 FROM fnd_id_flex_structures_vl
434 WHERE id_flex_code = 'GRD'
435 AND id_flex_structure_name = p_grade_structure;
436 --
437 hr_utility.set_location('per_db_per_setup.create_business_group',3);
438 SELECT id_flex_num
439 INTO l_job_num
440 FROM fnd_id_flex_structures_vl
441 WHERE id_flex_code = 'JOB'
442 AND id_flex_structure_name = p_job_structure;
443 --
444 hr_utility.set_location('per_db_per_setup.create_business_group',4);
445 SELECT id_flex_num
446 INTO l_people_group_num
447 FROM fnd_id_flex_structures_vl
448 WHERE id_flex_code = 'GRP'
449 AND id_flex_structure_name = p_people_group_structure;
450 --
451 hr_utility.set_location('per_db_per_setup.create_business_group',5);
452 SELECT id_flex_num
453 INTO l_cost_allocation_num
454 FROM fnd_id_flex_structures_vl
455 WHERE id_flex_code = 'COST'
456 AND id_flex_structure_name = p_cost_allocation_structure;
457 --
458 hr_utility.set_location('per_db_per_setup.create_business_group',6);
459 l_business_group_id := insert_organization_unit('Y'
460 ,p_name
461 ,null
462 ,null
463 ,null
464 ,null
465 ,p_date_from
466 ,p_date_to
467 ,'INT'
468 ,p_internal_address_line
469 ,null);
470 --
471 --
472 hr_utility.set_location('per_db_per_setup.create_business_group',7);
473 l_org_information_id := per_db_per_setup.insert_org_information(
474 l_business_group_id
475 ,'CLASS'
476 ,'HR_BG'
477 ,'Y'
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 ,null
495 ,null);
496 --
497 --
498 -- hr_utility.set_location('per_db_per_setup.create_business_group',8);
499 -- l_org_information_id := per_db_per_setup.insert_org_information(
500 -- l_business_group_id
501 -- ,'CLASS'
502 -- ,'HR_ORG'
503 -- ,'Y'
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 --
523 --
524 hr_utility.set_location('per_db_per_setup.create_business_group',9);
525 l_org_information_id := per_db_per_setup.insert_org_information(
526 l_business_group_id
527 ,'Business Group Information'
528 ,p_short_name
529 ,p_method_of_generation_emp_num
530 ,p_method_of_generation_apl_num
531 ,l_grade_num
532 ,l_people_group_num
533 ,l_job_num
534 ,l_cost_allocation_num
535 ,l_position_num
536 ,p_legislation_code
537 ,p_currency_code
538 ,null
539 ,null
540 ,null
541 ,null
542 ,null
543 ,null
544 ,null
545 ,null
546 ,null
547 ,null);
548 --
549 --
550 hr_utility.set_location('per_db_per_setup.create_business_group',10);
551 l_org_information_id := per_db_per_setup.insert_org_information(
552 l_business_group_id
553 ,'Work Day Information'
554 ,p_default_start_time
555 ,p_default_end_time
556 ,p_working_hours
557 ,p_frequency
558 ,null
559 ,null
560 ,null
561 ,null
562 ,null
563 ,null
564 ,null
565 ,null
566 ,null
567 ,null
568 ,null
569 ,null
570 ,null
571 ,null
572 ,null
573 ,null);
574 --
575 return l_business_group_id;
576 --
577 --
578 end create_business_group;
579 --
580 --
581 FUNCTION create_per_organization
582 ( P_NAME VARCHAR2
583 ,P_BUSINESS_GROUP VARCHAR2
584 ,P_DATE_FROM DATE
585 ,P_DATE_TO DATE DEFAULT null
586 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
587 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
588 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
589 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
590 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
591 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
592 ,P_TYPE VARCHAR2 DEFAULT null
593 ,P_LOCATION_ID NUMBER DEFAULT null
594 ) return NUMBER
595 IS
596 --
597 l_organization_id NUMBER;
598 l_org_information_id NUMBER;
599 l_business_group_id NUMBER;
600 --
601 begin
602 --
603 --
604 hr_utility.set_location('per_db_per_setup.create_per_organization',1);
605 --
606 SELECT business_group_id
607 INTO l_business_group_id
608 FROM per_business_groups
609 WHERE name = p_business_group;
610 --
611 l_organization_id := insert_organization_unit('N'
612 ,p_name
613 ,l_business_group_id
614 ,null
615 ,p_location_id
616 ,null
617 ,p_date_from
618 ,p_date_to
619 ,p_internal_external_flag
620 ,p_internal_address_line
621 ,p_type);
622 --
623 --
624 --
625 l_org_information_id := per_db_per_setup.insert_org_information(l_organization_id
626 ,'CLASS'
627 ,'HR_ORG'
628 ,'Y'
629 ,null
630 ,null
631 ,null
632 ,null
633 ,null
634 ,null
635 ,null
636 ,null
637 ,null
638 ,null
639 ,null
640 ,null
641 ,null
642 ,null
643 ,null
644 ,null
645 ,null
646 ,null);
647 --
648 --
649 --
650 l_org_information_id := per_db_per_setup.insert_org_information(l_organization_id
651 ,'Work Day Information'
652 ,p_default_start_time
653 ,p_default_end_time
654 ,p_working_hours
655 ,p_frequency
656 ,null
657 ,null
658 ,null
659 ,null
660 ,null
661 ,null
662 ,null
663 ,null
664 ,null
665 ,null
666 ,null
667 ,null
668 ,null
669 ,null
670 ,null
671 ,null);
672 --
673 --
674 --
675 return l_organization_id;
676 --
677 end create_per_organization;
678 --
679 --
680 FUNCTION create_pay_legal_company
681 ( P_NAME VARCHAR2
682 ,P_BUSINESS_GROUP VARCHAR2
683 ,P_DATE_FROM DATE
684 ,P_DATE_TO DATE DEFAULT null
685 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
686 ,P_LOCATION_ID NUMBER DEFAULT null
687 ,P_COMPANY_FEDERAL_IDENTIFIER VARCHAR2 DEFAULT null
688 ,P_NACHA_COMPANY_NAME VARCHAR2 DEFAULT null
689 ,P_NACHA_IDENTIFIER VARCHAR2 DEFAULT null
690 ,P_NACHA_DISCRETIONARY_CODE VARCHAR2 DEFAULT null
691 ) return NUMBER
692 IS
693 --
694 l_legal_company_id NUMBER;
695 l_org_information_id NUMBER;
696 l_business_group_id NUMBER;
697 --
698 begin
699 --
700 hr_utility.set_location('per_db_per_setup.create_pay_legal_company',1);
701 --
702 SELECT business_group_id
703 INTO l_business_group_id
704 FROM per_business_groups
705 WHERE name = p_business_group;
706 --
707 l_legal_company_id := insert_organization_unit('N'
708 ,p_name
709 ,l_business_group_id
710 ,null
711 ,p_location_id
712 ,null
713 ,p_date_from
714 ,p_date_to
715 ,null
716 ,p_internal_address_line
717 ,null);
718 --
719 --
720 --
721 l_org_information_id := per_db_per_setup.insert_org_information(
722 l_legal_company_id
723 ,'CLASS'
724 ,'HR_ORG'
725 ,'Y'
726 ,null
727 ,null
728 ,null
729 ,null
730 ,null
731 ,null
732 ,null
733 ,null
734 ,null
735 ,null
736 ,null
737 ,null
738 ,null
739 ,null
740 ,null
741 ,null
742 ,null
743 ,null);
744 --
745 --
746 --
747 l_org_information_id := per_db_per_setup.insert_org_information(
748 l_legal_company_id
749 ,'CLASS'
750 ,'HR_LEGAL'
751 ,'Y'
752 ,null
753 ,null
754 ,null
755 ,null
756 ,null
757 ,null
758 ,null
759 ,null
760 ,null
761 ,null
762 ,null
763 ,null
764 ,null
765 ,null
766 ,null
767 ,null
768 ,null
769 ,null);
770 --
771 --
772 l_org_information_id := per_db_per_setup.insert_org_information(
773 l_legal_company_id
774 ,'Legal Company Information'
775 ,p_company_federal_identifier
776 ,p_nacha_company_name
777 ,p_nacha_identifier
778 ,p_nacha_discretionary_code
779 ,null
780 ,null
781 ,null
782 ,null
783 ,null
784 ,null
785 ,null
786 ,null
787 ,null
788 ,null
789 ,null
790 ,null
791 ,null
792 ,null
793 ,null
794 ,null);
795 --
796 --
797 --
798 return l_legal_company_id;
799 --
800 end create_pay_legal_company;
801 --
802 --
803 FUNCTION create_job
804 (p_default VARCHAR2 DEFAULT 'Y'
805 ,p_name VARCHAR2
806 ,p_business_group VARCHAR2
807 ,p_date_from DATE
808 ,p_date_to DATE DEFAULT null
809 ,p_segment1 VARCHAR2 DEFAULT null
810 ,p_segment2 VARCHAR2 DEFAULT null
811 ,p_segment3 VARCHAR2 DEFAULT null
812 ,p_segment4 VARCHAR2 DEFAULT null
813 ,p_segment5 VARCHAR2 DEFAULT null
814 ,p_segment6 VARCHAR2 DEFAULT null
815 ,p_segment7 VARCHAR2 DEFAULT null
816 ,p_segment8 VARCHAR2 DEFAULT null
817 ,p_segment9 VARCHAR2 DEFAULT null
818 ,p_segment10 VARCHAR2 DEFAULT null
819 ,p_segment11 VARCHAR2 DEFAULT null
820 ,p_segment12 VARCHAR2 DEFAULT null
821 ,p_segment13 VARCHAR2 DEFAULT null
822 ,p_segment14 VARCHAR2 DEFAULT null
823 ,p_segment15 VARCHAR2 DEFAULT null
824 ,p_segment16 VARCHAR2 DEFAULT null
825 ,p_segment17 VARCHAR2 DEFAULT null
826 ,p_segment18 VARCHAR2 DEFAULT null
827 ,p_segment19 VARCHAR2 DEFAULT null
828 ,p_segment20 VARCHAR2 DEFAULT null
829 ,p_segment21 VARCHAR2 DEFAULT null
830 ,p_segment22 VARCHAR2 DEFAULT null
831 ,p_segment23 VARCHAR2 DEFAULT null
832 ,p_segment24 VARCHAR2 DEFAULT null
833 ,p_segment25 VARCHAR2 DEFAULT null
834 ,p_segment26 VARCHAR2 DEFAULT null
835 ,p_segment27 VARCHAR2 DEFAULT null
836 ,p_segment28 VARCHAR2 DEFAULT null
837 ,p_segment29 VARCHAR2 DEFAULT null
838 ,p_segment30 VARCHAR2 DEFAULT null
839 ) return NUMBER
840 --
841 IS
842 --
843 -- local variables for create_job
844 --
845 l_job_id NUMBER;
846 l_business_group_id NUMBER;
847 l_structure_num NUMBER;
848 l_segment1 VARCHAR2(60);
849 l_job_definition_id NUMBER;
850 --
851 --
852 PROCEDURE unique_name
853 (p_business_group_id NUMBER, p_job_id VARCHAR2, p_job_name VARCHAR2)
854 IS
855 --
856 job_check VARCHAR2(1);
857 --
858 begin
859 --
860 --
861 SELECT 'Y'
862 INTO job_check
863 FROM per_jobs jb
864 WHERE (jb.job_id <> p_job_id
865 OR p_job_id IS NULL)
866 AND p_job_name = jb.name
867 AND p_business_group_id = jb.business_group_id + 0;
868 --
869 if job_check = 'Y' then
870 hr_utility.set_message(801,'PAY_7686_USER_JOB_TAB_UNIQUE');
871 hr_utility.raise_error;
872 end if;
873 --
874 exception
875 when NO_DATA_FOUND then null ;
876 --
877 --
878 end unique_name;
879 --
880 FUNCTION insert_job_definition return NUMBER IS
881 --
882 l_job_definition_id NUMBER;
883 --
884 begin
885 --
886 hr_utility.set_location('per_db_per_setup.insert_job_definition',1);
887 --
888 SELECT per_job_definitions_s.nextval
889 INTO l_job_definition_id
890 FROM sys.dual;
891 --
892 hr_utility.set_location('per_db_per_setup.insert_job_definition',2);
893 --
894 INSERT INTO per_job_definitions
895 (JOB_DEFINITION_ID
896 ,ID_FLEX_NUM
897 ,SUMMARY_FLAG
898 ,ENABLED_FLAG
899 ,START_DATE_ACTIVE
900 ,END_DATE_ACTIVE
901 ,SEGMENT1
902 ,SEGMENT2
903 ,SEGMENT3
904 ,SEGMENT4
905 ,SEGMENT5
906 ,SEGMENT6
907 ,SEGMENT7
908 ,SEGMENT8
909 ,SEGMENT9
910 ,SEGMENT10
911 ,SEGMENT11
912 ,SEGMENT12
913 ,SEGMENT13
914 ,SEGMENT14
915 ,SEGMENT15
916 ,SEGMENT16
917 ,SEGMENT17
918 ,SEGMENT18
919 ,SEGMENT19
920 ,SEGMENT20
921 ,SEGMENT21
922 ,SEGMENT22
923 ,SEGMENT23
924 ,SEGMENT24
925 ,SEGMENT25
926 ,SEGMENT26
927 ,SEGMENT27
928 ,SEGMENT28
929 ,SEGMENT29
930 ,SEGMENT30)
931 values
932 (l_job_definition_id
933 ,l_structure_num
934 ,'Y'
935 ,'N'
936 ,to_date(p_date_from,'DD-MON-YYYY')
937 ,to_date(p_date_to,'DD-MON-YYYY')
938 ,l_segment1 -- p_name for default jobs
939 ,p_segment2
940 ,p_segment3
941 ,p_segment4
942 ,p_segment5
943 ,p_segment6
944 ,p_segment7
945 ,p_segment8
946 ,p_segment9
947 ,p_segment10
948 ,p_segment11
949 ,p_segment12
950 ,p_segment13
951 ,p_segment14
952 ,p_segment15
953 ,p_segment16
954 ,p_segment17
955 ,p_segment18
956 ,p_segment19
957 ,p_segment20
958 ,p_segment21
959 ,p_segment22
960 ,p_segment23
961 ,p_segment24
962 ,p_segment25
963 ,p_segment26
964 ,p_segment27
965 ,p_segment28
966 ,p_segment29
967 ,p_segment30
968 );
969 --
970 --
971 return l_job_definition_id;
972 --
973 end insert_job_definition;
974 --
975 --
976 FUNCTION insert_job RETURN NUMBER IS
977 --
978 l_job_id NUMBER;
979 --
980 begin
981 --
982 hr_utility.set_location('per_db_per_setup.insert_job',1);
983 --
984 SELECT per_jobs_s.nextval
985 INTO l_job_id
986 FROM sys.dual;
987 --
988 hr_utility.set_location('per_db_per_setup.insert_job',2);
989 --
990 unique_name(l_business_group_id,l_job_id, p_name);
991 --
992 hr_utility.set_location('per_db_per_setup.insert_job',3);
993 --
994 INSERT INTO per_jobs
995 (JOB_ID
996 ,BUSINESS_GROUP_ID
997 ,JOB_DEFINITION_ID
998 ,DATE_FROM
999 ,DATE_TO
1000 ,NAME
1001 ,LAST_UPDATE_DATE
1002 ,LAST_UPDATED_BY
1003 ,LAST_UPDATE_LOGIN
1004 ,CREATED_BY
1005 ,CREATION_DATE)
1006 values
1007 (l_job_id
1008 ,l_business_group_id
1009 ,l_job_definition_id
1010 ,p_date_from
1011 ,p_date_to
1012 ,p_name
1013 ,SYSDATE
1014 ,0
1015 ,0
1016 ,0
1017 ,SYSDATE);
1018 --
1019 --
1020 return l_job_id;
1021 --
1022 end insert_job;
1023 --
1024 begin -- FUNCTION create_job
1025 --
1026 --
1027 hr_utility.set_location('per_db_per_setup.create_job',1);
1028 --
1029 SELECT business_group_id
1030 , job_structure
1031 INTO l_business_group_id
1032 , l_structure_num
1033 FROM per_business_groups
1034 WHERE name = p_business_group
1035 AND business_group_id <> 0;
1036 --
1037 --
1038 -- set SEGMENT1 to name if creating a default job
1039 --
1040 if p_default = 'Y' then
1041 l_segment1 := p_name ;
1042 else l_segment1 := p_segment1 ;
1043 end if;
1044 --
1045 l_job_definition_id := insert_job_definition;
1046 --
1047 l_job_id := insert_job;
1048 --
1049 return l_job_id;
1050 --
1051 --
1052 end create_job;
1053 --
1054 --
1055 FUNCTION create_position
1056 (p_default VARCHAR2 DEFAULT 'Y'
1057 ,p_name VARCHAR2
1058 ,p_business_group VARCHAR2
1059 ,p_date_effective DATE
1060 ,p_date_end DATE DEFAULT null
1061 ,p_job VARCHAR2
1062 ,p_organization VARCHAR2
1063 ,p_location VARCHAR2 DEFAULT null
1064 ,p_time_normal_start VARCHAR2 DEFAULT '08:00'
1065 ,p_time_normal_finish VARCHAR2 DEFAULT '17:30'
1066 ,p_working_hours NUMBER DEFAULT 37.5
1067 ,p_frequency VARCHAR2 DEFAULT 'W'
1068 ,p_probation_period VARCHAR2 DEFAULT null
1069 ,p_probation_units VARCHAR2 DEFAULT null
1070 ,p_relief_position VARCHAR2 DEFAULT null
1071 ,p_replacement_required VARCHAR2 DEFAULT 'N'
1072 ,p_successor_position VARCHAR2 DEFAULT null
1073 ,p_segment1 VARCHAR2 DEFAULT null
1074 ,p_segment2 VARCHAR2 DEFAULT null
1075 ,p_segment3 VARCHAR2 DEFAULT null
1076 ,p_segment4 VARCHAR2 DEFAULT null
1077 ,p_segment5 VARCHAR2 DEFAULT null
1078 ,p_segment6 VARCHAR2 DEFAULT null
1079 ,p_segment7 VARCHAR2 DEFAULT null
1080 ,p_segment8 VARCHAR2 DEFAULT null
1081 ,p_segment9 VARCHAR2 DEFAULT null
1082 ,p_segment10 VARCHAR2 DEFAULT null
1083 ,p_segment11 VARCHAR2 DEFAULT null
1084 ,p_segment12 VARCHAR2 DEFAULT null
1085 ,p_segment13 VARCHAR2 DEFAULT null
1086 ,p_segment14 VARCHAR2 DEFAULT null
1087 ,p_segment15 VARCHAR2 DEFAULT null
1088 ,p_segment16 VARCHAR2 DEFAULT null
1089 ,p_segment17 VARCHAR2 DEFAULT null
1090 ,p_segment18 VARCHAR2 DEFAULT null
1091 ,p_segment19 VARCHAR2 DEFAULT null
1092 ,p_segment20 VARCHAR2 DEFAULT null
1093 ,p_segment21 VARCHAR2 DEFAULT null
1094 ,p_segment22 VARCHAR2 DEFAULT null
1095 ,p_segment23 VARCHAR2 DEFAULT null
1096 ,p_segment24 VARCHAR2 DEFAULT null
1097 ,p_segment25 VARCHAR2 DEFAULT null
1098 ,p_segment26 VARCHAR2 DEFAULT null
1099 ,p_segment27 VARCHAR2 DEFAULT null
1100 ,p_segment28 VARCHAR2 DEFAULT null
1101 ,p_segment29 VARCHAR2 DEFAULT null
1102 ,p_segment30 VARCHAR2 DEFAULT null
1103 ) return NUMBER
1104 --
1105 IS
1106 --
1107 cursor c0 (p_name VARCHAR2) is
1108 SELECT position_id
1109 FROM hr_positions_f
1110 WHERE name = p_name
1111 AND effective_end_date = to_date('31124712', 'ddmmyyyy');
1112 -- local variables for create_position
1113 --
1114 l_position_id NUMBER;
1115 l_business_group_id NUMBER;
1116 l_structure_num NUMBER;
1117 l_segment1 VARCHAR2(60);
1118 l_name VARCHAR2(255);
1119 l_job_id NUMBER;
1120 l_organization_id NUMBER;
1121 l_effective_start_date DATE;
1122 l_effective_end_date DATE;
1123 l_object_version_number NUMBER;
1124 l_location_id NUMBER;
1125 l_position_definition_id NUMBER;
1126 l_relief_position_id NUMBER;
1127 l_successor_position_id NUMBER;
1128 --
1129 --
1130 PROCEDURE unique_name
1131 (p_business_group_id NUMBER, p_position_id VARCHAR2, p_position_name VARCHAR2)
1132 IS
1133 --
1134 pos_check VARCHAR2(1);
1135 --
1136 begin
1137 --
1138 -- Changed 05-Oct-99 SCNair (per_positions to hr_all_positions_f) Date tracked Position Req
1139 --
1140 SELECT 'Y'
1141 INTO pos_check
1142 FROM hr_all_positions_f pos
1143 WHERE (pos.position_id <> p_position_id
1144 OR p_position_id IS NULL)
1145 AND p_position_name = pos.name
1146 AND p_business_group_id = pos.business_group_id + 0;
1147 --
1148 if pos_check = 'Y' then
1149 hr_utility.set_message(801,'PAY_7683_USER_POS_TABLE_UNIQUE');
1150 hr_utility.raise_error;
1151 end if;
1152 --
1153 exception
1154 when NO_DATA_FOUND then null ;
1155 --
1156 --
1157 end unique_name;
1158 --
1159 FUNCTION insert_pos_definition return NUMBER IS
1160 --
1161 l_pos_definition_id NUMBER;
1162 --
1163 begin
1164 --
1165 hr_utility.set_location('per_db_per_setup.insert_pos_definition',1);
1166 --
1167 SELECT per_position_definitions_s.nextval
1168 INTO l_pos_definition_id
1169 FROM sys.dual;
1170 --
1171 hr_utility.set_location('per_db_per_setup.insert_pos_definition',2);
1172 --
1173 INSERT INTO per_position_definitions
1174 (POSITION_DEFINITION_ID
1175 ,ID_FLEX_NUM
1176 ,SUMMARY_FLAG
1177 ,ENABLED_FLAG
1178 ,START_DATE_ACTIVE
1179 ,END_DATE_ACTIVE
1180 ,SEGMENT1
1181 ,SEGMENT2
1182 ,SEGMENT3
1183 ,SEGMENT4
1184 ,SEGMENT5
1185 ,SEGMENT6
1186 ,SEGMENT7
1187 ,SEGMENT8
1188 ,SEGMENT9
1189 ,SEGMENT10
1190 ,SEGMENT11
1191 ,SEGMENT12
1192 ,SEGMENT13
1193 ,SEGMENT14
1194 ,SEGMENT15
1195 ,SEGMENT16
1196 ,SEGMENT17
1197 ,SEGMENT18
1198 ,SEGMENT19
1199 ,SEGMENT20
1200 ,SEGMENT21
1201 ,SEGMENT22
1202 ,SEGMENT23
1203 ,SEGMENT24
1204 ,SEGMENT25
1205 ,SEGMENT26
1206 ,SEGMENT27
1207 ,SEGMENT28
1208 ,SEGMENT29
1209 ,SEGMENT30)
1210 values
1211 (l_pos_definition_id
1212 ,l_structure_num
1213 ,'Y'
1214 ,'N'
1215 ,p_date_effective
1216 ,p_date_end
1217 ,l_segment1 -- p_name for a default position
1218 ,p_segment2
1219 ,p_segment3
1220 ,p_segment4
1221 ,p_segment5
1222 ,p_segment6
1223 ,p_segment7
1224 ,p_segment8
1225 ,p_segment9
1226 ,p_segment10
1227 ,p_segment11
1228 ,p_segment12
1229 ,p_segment13
1230 ,p_segment14
1231 ,p_segment15
1232 ,p_segment16
1233 ,p_segment17
1234 ,p_segment18
1235 ,p_segment19
1236 ,p_segment20
1237 ,p_segment21
1238 ,p_segment22
1239 ,p_segment23
1240 ,p_segment24
1241 ,p_segment25
1242 ,p_segment26
1243 ,p_segment27
1244 ,p_segment28
1245 ,p_segment29
1246 ,p_segment30
1247 );
1248 --
1249 --
1250 return l_pos_definition_id;
1251 --
1252 end insert_pos_definition;
1253 --
1254 --
1255 FUNCTION insert_position RETURN NUMBER IS
1256 --
1257 l_position_id NUMBER;
1258 --
1259 begin
1260 --
1261 hr_utility.set_location('per_db_per_setup.insert_position',1);
1262 --
1263 -- Changed 05-Oct-99 SCNair change insert as an API call
1264 --
1265 -- SELECT per_positions_s.nextval
1266 -- INTO l_position_id
1267 -- FROM sys.dual;
1268 --
1269 hr_utility.set_location('per_db_per_setup.insert_position',2);
1270 --
1271 unique_name(l_business_group_id, l_position_id, p_name);
1272 --
1273 hr_utility.set_location('per_db_per_setup.insert_position',3);
1274 --
1275 -- INSERT INTO per_positions
1276 -- (POSITION_ID
1277 -- ,BUSINESS_GROUP_ID
1278 -- ,JOB_ID
1279 -- ,ORGANIZATION_ID
1280 -- ,LOCATION_ID
1281 -- ,POSITION_DEFINITION_ID
1282 -- ,DATE_EFFECTIVE
1283 -- ,DATE_END
1284 -- ,FREQUENCY
1285 -- ,NAME
1286 -- ,PROBATION_PERIOD
1287 -- ,PROBATION_PERIOD_UNITS
1288 -- ,RELIEF_POSITION_ID
1289 -- ,REPLACEMENT_REQUIRED_FLAG
1290 -- ,SUCCESSOR_POSITION_ID
1291 -- ,TIME_NORMAL_FINISH
1292 -- ,TIME_NORMAL_START
1293 -- ,WORKING_HOURS
1294 -- ,LAST_UPDATE_DATE
1295 -- ,LAST_UPDATED_BY
1296 -- ,LAST_UPDATE_LOGIN
1297 -- ,CREATED_BY
1298 -- ,CREATION_DATE)
1299 -- values
1300 -- (l_position_id
1301 -- ,l_business_group_id
1302 -- ,l_job_id
1303 -- ,l_organization_id
1304 -- ,l_location_id
1305 -- ,l_position_definition_id
1306 -- ,p_date_effective
1307 -- ,p_date_end
1308 -- ,p_frequency
1309 -- ,p_name
1310 -- ,p_probation_period
1311 -- ,p_probation_units
1312 -- ,l_relief_position_id
1313 -- ,p_replacement_required
1314 -- ,l_successor_position_id
1315 -- ,p_time_normal_finish
1316 -- ,p_time_normal_start
1317 -- ,p_working_hours
1318 -- ,SYSDATE
1319 -- ,0
1320 -- ,0
1321 -- ,0
1322 -- ,SYSDATE);
1323 --
1324 --
1325 hr_position_api.create_position(
1326 p_position_id => l_position_id,
1327 p_effective_start_date => l_effective_start_date,
1328 p_effective_end_date => l_effective_end_date,
1329 p_position_definition_id => l_position_definition_id,
1330 p_name => l_name,
1331 p_object_version_number => l_object_version_number,
1332 p_position_type => 'NONE',
1333 p_job_id => l_job_id,
1334 p_organization_id => l_organization_id ,
1335 p_effective_date => p_date_effective,
1336 p_date_effective => p_date_effective,
1337 p_validate => false,
1338 p_business_group_id => l_business_group_id,
1339 p_location_id => l_location_id,
1340 p_relief_position_id => l_relief_position_id,
1341 p_successor_position_id => l_successor_position_id,
1342 p_date_end => p_date_end,
1343 p_frequency => p_frequency,
1344 p_probation_period => p_probation_period,
1345 p_probation_period_unit_cd => p_probation_units,
1346 p_replacement_required_flag => p_replacement_required,
1347 p_working_hours => p_working_hours,
1348 /*
1349 p_status => l_status,
1350 p_comments => l_comments,
1351 p_attribute_category => l_attribute_category,
1352 p_attribute1 => l_attribute1,
1353 p_attribute2 => l_attribute2,
1354 p_attribute3 => l_attribute3,
1355 p_attribute4 => l_attribute4,
1356 p_attribute5 => l_attribute5,
1357 p_attribute6 => l_attribute6,
1358 p_attribute7 => l_attribute7,
1359 p_attribute8 => l_attribute8,
1360 p_attribute9 => l_attribute9,
1361 p_attribute10 => l_attribute10,
1362 p_attribute11 => l_attribute11,
1363 p_attribute12 => l_attribute12,
1364 p_attribute13 => l_attribute13,
1365 p_attribute14 => l_attribute14,
1366 p_attribute15 => l_attribute15,
1367 p_attribute16 => l_attribute16,
1368 p_attribute17 => l_attribute17,
1369 p_attribute18 => l_attribute18,
1370 p_attribute19 => l_attribute19,
1371 p_attribute20 => l_attribute20,
1372 p_attribute21 => l_attribute21,
1373 p_attribute22 => l_attribute22,
1374 p_attribute23 => l_attribute23,
1375 p_attribute24 => l_attribute24,
1376 p_attribute25 => l_attribute25,
1377 p_attribute26 => l_attribute26,
1378 p_attribute27 => l_attribute27,
1379 p_attribute28 => l_attribute28,
1380 p_attribute29 => l_attribute29,
1381 p_attribute30 => l_attribute30,
1382 p_concat_segments => l_segment,
1383 p_request_id => l_request_id,
1384 p_program_application_id => l_program_application_id,
1385 p_program_id => l_program_id,
1386 p_program_update_date => l_program_update_date
1387 */
1388 p_segment1 => l_segment1,
1389 p_segment2 => p_segment2,
1390 p_segment3 => p_segment3,
1391 p_segment4 => p_segment4,
1392 p_segment5 => p_segment5,
1393 p_segment6 => p_segment6,
1394 p_segment7 => p_segment7,
1395 p_segment8 => p_segment8,
1396 p_segment9 => p_segment9,
1397 p_segment10 => p_segment10,
1398 p_segment11 => p_segment11,
1399 p_segment12 => p_segment12,
1400 p_segment13 => p_segment13,
1401 p_segment14 => p_segment14,
1402 p_segment15 => p_segment15,
1403 p_segment16 => p_segment16,
1404 p_segment17 => p_segment17,
1405 p_segment18 => p_segment18,
1406 p_segment19 => p_segment19,
1407 p_segment20 => p_segment20,
1408 p_segment21 => p_segment21,
1409 p_segment22 => p_segment22,
1410 p_segment23 => p_segment23,
1411 p_segment24 => p_segment24,
1412 p_segment25 => p_segment25,
1413 p_segment26 => p_segment26,
1414 p_segment27 => p_segment27,
1415 p_segment28 => p_segment28,
1416 p_segment29 => p_segment29,
1417 p_segment30 => p_segment30
1418 );
1419
1420 return l_position_id;
1421 --
1422 end insert_position;
1423 --
1424 begin -- FUNCTION create_position
1425 --
1426 --
1427 --
1428 hr_utility.set_location('per_db_per_setup.create_position',1);
1429 --
1430 SELECT business_group_id
1431 , position_structure
1432 INTO l_business_group_id
1433 , l_structure_num
1434 FROM per_business_groups
1435 WHERE name = p_business_group
1436 AND business_group_id <> 0;
1437 --
1438 hr_utility.set_location('per_db_per_setup.create_position',2);
1439 --
1440 SELECT job_id
1441 INTO l_job_id
1442 FROM per_jobs
1443 WHERE name = p_job
1444 AND business_group_id + 0 = l_business_group_id;
1445 --
1446 hr_utility.set_location('per_db_per_setup.create_position',3);
1447 --
1448 SELECT organization_id
1449 INTO l_organization_id
1450 FROM per_organization_units
1451 WHERE name = p_organization
1452 AND business_group_id + 0 = l_business_group_id;
1453 --
1454 begin
1455 --
1456 hr_utility.set_location('per_db_per_setup.create_position',4);
1457 --
1458 SELECT location_id
1459 INTO l_location_id
1460 FROM hr_locations
1461 WHERE location_code = p_location;
1462 --
1463 hr_utility.set_location('per_db_per_setup.create_position',5);
1464 --
1465 --
1466 open c0(p_relief_position);
1467 fetch c0 into l_relief_position_id;
1468 close c0;
1469 hr_utility.set_location('per_db_per_setup.create_position',6);
1470 open c0(p_successor_position);
1471 fetch c0 into l_successor_position_id;
1472 close c0;
1473 --
1474 -- SELECT position_id
1475 -- INTO l_successor_position_id
1476 -- FROM per_positions
1477 -- WHERE name = p_successor_position;
1478 --
1479 exception when NO_DATA_FOUND then null;
1480 end;
1481 --
1482 -- set SEGMENT1 to name if creating a default position
1483 --
1484 if p_default = 'Y' then
1485 l_segment1 := p_name ;
1486 else l_segment1 := p_segment1 ;
1487 end if;
1488 --
1489 l_position_definition_id := insert_pos_definition;
1490 --
1491 l_position_id := insert_position;
1492 --
1493 return l_position_id;
1494 --
1495 --
1496 end create_position;
1497 --
1498 --
1499 FUNCTION create_grade
1500 (p_default VARCHAR2 DEFAULT 'Y'
1501 ,p_name VARCHAR2
1502 ,p_business_group VARCHAR2
1503 ,p_date_from DATE
1504 ,p_date_to DATE DEFAULT null
1505 ,p_sequence VARCHAR2 DEFAULT null
1506 ,p_segment1 VARCHAR2 DEFAULT null
1507 ,p_segment2 VARCHAR2 DEFAULT null
1508 ,p_segment3 VARCHAR2 DEFAULT null
1509 ,p_segment4 VARCHAR2 DEFAULT null
1510 ,p_segment5 VARCHAR2 DEFAULT null
1511 ,p_segment6 VARCHAR2 DEFAULT null
1512 ,p_segment7 VARCHAR2 DEFAULT null
1513 ,p_segment8 VARCHAR2 DEFAULT null
1514 ,p_segment9 VARCHAR2 DEFAULT null
1515 ,p_segment10 VARCHAR2 DEFAULT null
1516 ,p_segment11 VARCHAR2 DEFAULT null
1517 ,p_segment12 VARCHAR2 DEFAULT null
1518 ,p_segment13 VARCHAR2 DEFAULT null
1519 ,p_segment14 VARCHAR2 DEFAULT null
1520 ,p_segment15 VARCHAR2 DEFAULT null
1521 ,p_segment16 VARCHAR2 DEFAULT null
1522 ,p_segment17 VARCHAR2 DEFAULT null
1523 ,p_segment18 VARCHAR2 DEFAULT null
1524 ,p_segment19 VARCHAR2 DEFAULT null
1525 ,p_segment20 VARCHAR2 DEFAULT null
1526 ,p_segment21 VARCHAR2 DEFAULT null
1527 ,p_segment22 VARCHAR2 DEFAULT null
1528 ,p_segment23 VARCHAR2 DEFAULT null
1529 ,p_segment24 VARCHAR2 DEFAULT null
1530 ,p_segment25 VARCHAR2 DEFAULT null
1531 ,p_segment26 VARCHAR2 DEFAULT null
1532 ,p_segment27 VARCHAR2 DEFAULT null
1533 ,p_segment28 VARCHAR2 DEFAULT null
1534 ,p_segment29 VARCHAR2 DEFAULT null
1535 ,p_segment30 VARCHAR2 DEFAULT null
1536 ) return NUMBER
1537 --
1538 IS
1539 --
1540 -- local variables for create_grade
1541 --
1542 l_grade_id NUMBER;
1543 l_business_group_id NUMBER;
1544 l_structure_num NUMBER;
1545 l_segment1 VARCHAR2(60);
1546 l_grade_definition_id NUMBER;
1547 --
1548 --
1549 --
1550 PROCEDURE unique_name
1551 (p_business_group_id NUMBER, p_grade_id VARCHAR2, p_grade_name VARCHAR2)
1552 IS
1553 --
1554 grd_check VARCHAR2(1);
1555 --
1556 begin
1557 --
1558 --
1559 SELECT 'Y'
1560 INTO grd_check
1561 FROM per_grades grd
1562 WHERE (grd.grade_id <> p_grade_id
1563 OR p_grade_id IS NULL)
1564 AND p_grade_name = grd.name
1565 AND p_business_group_id = grd.business_group_id + 0;
1566 --
1567 if grd_check = 'Y' then
1568 hr_utility.set_message(801,'PAY_7687_USER_GRADE_TAB_UNIQUE');
1569 hr_utility.raise_error;
1570 end if;
1571 --
1572 exception
1573 when NO_DATA_FOUND then null ;
1574 --
1575 --
1576 end unique_name;
1577 --
1578 --
1579 FUNCTION insert_grade_definition return NUMBER IS
1580 --
1581 l_grade_definition_id NUMBER;
1582 --
1583 begin
1584 --
1585 hr_utility.set_location('per_db_per_setup.insert_grade_definition',1);
1586 --
1587 SELECT per_grade_definitions_s.nextval
1588 INTO l_grade_definition_id
1589 FROM sys.dual;
1590 --
1591 hr_utility.set_location('per_db_per_setup.insert_grade_definition',2);
1592 --
1593 INSERT INTO per_grade_definitions
1594 (GRADE_DEFINITION_ID
1595 ,ID_FLEX_NUM
1596 ,SUMMARY_FLAG
1597 ,ENABLED_FLAG
1598 ,START_DATE_ACTIVE
1599 ,END_DATE_ACTIVE
1600 ,SEGMENT1
1601 ,SEGMENT2
1602 ,SEGMENT3
1603 ,SEGMENT4
1604 ,SEGMENT5
1605 ,SEGMENT6
1606 ,SEGMENT7
1607 ,SEGMENT8
1608 ,SEGMENT9
1609 ,SEGMENT10
1610 ,SEGMENT11
1611 ,SEGMENT12
1612 ,SEGMENT13
1613 ,SEGMENT14
1614 ,SEGMENT15
1615 ,SEGMENT16
1616 ,SEGMENT17
1617 ,SEGMENT18
1618 ,SEGMENT19
1619 ,SEGMENT20
1620 ,SEGMENT21
1621 ,SEGMENT22
1622 ,SEGMENT23
1623 ,SEGMENT24
1624 ,SEGMENT25
1625 ,SEGMENT26
1626 ,SEGMENT27
1627 ,SEGMENT28
1628 ,SEGMENT29
1629 ,SEGMENT30)
1630 values
1631 (l_grade_definition_id
1632 ,l_structure_num
1633 ,'Y'
1634 ,'N'
1635 ,p_date_from
1636 ,p_date_to
1637 ,l_segment1 -- p_name for default grades
1638 ,p_segment2
1639 ,p_segment3
1640 ,p_segment4
1641 ,p_segment5
1642 ,p_segment6
1643 ,p_segment7
1644 ,p_segment8
1645 ,p_segment9
1646 ,p_segment10
1647 ,p_segment11
1648 ,p_segment12
1649 ,p_segment13
1650 ,p_segment14
1651 ,p_segment15
1652 ,p_segment16
1653 ,p_segment17
1654 ,p_segment18
1655 ,p_segment19
1656 ,p_segment20
1657 ,p_segment21
1658 ,p_segment22
1659 ,p_segment23
1660 ,p_segment24
1661 ,p_segment25
1662 ,p_segment26
1663 ,p_segment27
1664 ,p_segment28
1665 ,p_segment29
1666 ,p_segment30
1667 );
1668 --
1669 --
1670 return l_grade_definition_id;
1671 --
1672 end insert_grade_definition;
1673 --
1674 --
1675 FUNCTION insert_grade RETURN NUMBER IS
1676 --
1677 l_grade_id NUMBER;
1678 --
1679 begin
1680 --
1681 hr_utility.set_location('per_db_per_setup.insert_grade',1);
1682 --
1683 SELECT per_grades_s.nextval
1684 INTO l_grade_id
1685 FROM sys.dual;
1686 --
1687 hr_utility.set_location('per_db_per_setup.insert_grade',2);
1688 --
1689 unique_name(l_business_group_id, l_grade_id, p_name);
1690 --
1691 hr_utility.set_location('per_db_per_setup.insert_grade',3);
1692 --
1693 INSERT INTO per_grades
1694 (GRADE_ID
1695 ,BUSINESS_GROUP_ID
1696 ,GRADE_DEFINITION_ID
1697 ,DATE_FROM
1698 ,DATE_TO
1699 ,NAME
1700 ,SEQUENCE
1701 ,LAST_UPDATE_DATE
1702 ,LAST_UPDATED_BY
1703 ,LAST_UPDATE_LOGIN
1704 ,CREATED_BY
1705 ,CREATION_DATE)
1706 SELECT
1707 l_grade_id
1708 ,l_business_group_id
1709 ,l_grade_definition_id
1710 ,p_date_from
1711 ,p_date_to
1712 ,p_name
1713 ,nvl(p_sequence,nvl(max(pg.sequence),0) + 1)
1714 ,SYSDATE
1715 ,0
1716 ,0
1717 ,0
1718 ,SYSDATE
1719 FROM per_grades pg
1720 WHERE pg.business_group_id + 0 = l_business_group_id ;
1721 --
1722 --
1723 return l_grade_id;
1724 --
1725 end insert_grade;
1726 --
1727 begin -- FUNCTION create_grade
1728 --
1729 --
1730 hr_utility.set_location('per_db_per_setup.create_grade',1);
1731 --
1732 SELECT business_group_id
1733 , grade_structure
1734 INTO l_business_group_id
1735 , l_structure_num
1736 FROM per_business_groups
1737 WHERE name = p_business_group
1738 AND business_group_id <> 0;
1739 --
1740 --
1741 -- set SEGMENT1 to name if creating a default grade
1742 --
1743 if p_default = 'Y' then
1744 l_segment1 := p_name ;
1745 else l_segment1 := p_segment1 ;
1746 end if;
1747 --
1748 l_grade_definition_id := insert_grade_definition;
1749 --
1750 l_grade_id := insert_grade;
1751 --
1752 return l_grade_id;
1753 --
1754 --
1755 end create_grade;
1756 --
1757 --
1758 end per_db_per_setup;