1 PACKAGE BODY hr_organization AS
2 /* $Header: peorganz.pkb 120.1 2008/02/06 12:01:45 pchowdav 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
24 Name : hr_organization (BODY)
25
26 Description : Contains the definition of organization procedures
27 as declared in the hr_organization package header
28
29 Uses : hr_utility
30
31 Change List
32 -----------
33
34 Version Date Author ER/CR No. Description of Change
35 -------+---------+----------+---------+--------------------------
36 70.0 17-NOV-92 SZWILLIA Date Created
37 70.1 17-NOV-92 SZWILLIA Corrected Message Calls.
38 70.2 17-NOV-92 SZWILLIA Corrected Message Names.
39 70.3 02-DEC-92 SZWILLIA Added Procedure to insert
40 Business Group Details
41 from hr_org_info_ari.
42 70.4 12-JAN-93 SZWILLIA Removed insert into status
43 types from insert bg details.
44 70.6 20-JAN-93 SZWILLIA Corrected error handling.
45 70.7 26-JAN-93 SZWILLIA Changed INSERT into
46 PER_SECURITY_PROFILES.
47 70.9 01-MAR-93 TMATHERS Added Procedure to execute
48 pre-delete checks(org_predel_check).
49 70.10 01-MAR-93 TMATHERS Allowed user to delete a business group
50 if the only organization it contained
51 was itself.
52 70.11 04-MAR-93 SZWILLIA Changed parameters to DATE
53 70.12 11-MAR-93 NKHAn Added 'exit' to the end
54 70.13 17-MAR-93 SZWILLIA Changed insert into security_profiles
55 70.14 31-MAR-93 TMATHERS Removed org_predel_check to seperate
56 package.
57 70.15 01-APR-93 TMATHERS Added shared organization predelete
58 checks procedure for CBB.
59 70.16 01-APR-93 TMATHERS Corrected mistake made by previous
60 Change.
61 70.17 05-APR-93 TMATHERS Took out Jobs and Positions references
62 and placed in a separate procedure.
63 In order that the applications who
64 choose not to use J and P's when
65 they have the Org CBB, can still have
66 pre-delete validation.
67 70.18 05-APR-93 TMATHERS Didn't like the ampersand in above
68 comment changed to and.
69 70.19 22-APR-93 TMATHERS Added hr_weak_bg_chk, fixed
70 unique_name to include checks
71 for business groups.
72 70.20 30-APR-93 TMATHERS Added exists clause to
73 unique name check to stop
74 TOO_MANY_ROWS exception firing.
75 When a BG and an org in the current
76 BG have the same name.
77 70.21 04-MAY-93 TMATHERS changed HR_ORGANIZATION_UNITS
78 to HR_ALL_ORGANIZATION_UNITS in
79 unique name check
80 70.22 06-MAY-93 SZWILLIA Changed above back as security does
81 not apply in packages.(Always created
82 in the base user).
83 Changed insert_bus_grp_details to
84 insert 'user' if running in install
85 mode (ie no rows in
86 FND_PRODUCT_INSTALLATIONS.
87 -------------------------------------------------------------------------------
88 70.25 04-AUG-93 TMATHERS removed reference to
89 product_security_type in
90 per_security_profiles.
91 70.26 02-JUN-94 TMathers Added get_flex_msg as a result of
92 using FND PLSQL in PERORDOR.
93 70.29 23-NOV-94 Rfine Suppressed index on business_group_id
94 70.30 15-DEC-94 Rfine Added code to insert a row into
95 pay_consolidation_sets for the new
96 business group.
97 70.31 17-Apr-95 SDesai/JThuringer Added ins_si_type procedure and code
98 for enabling ADA flex structures for
99 US business groups.
100 70.32 25-Apr-95 SDesai Code to enable OSHA flex structure;
101 use p_org_information9 to check if
102 it is a US business group.
103 70.33 25-JUL-95 AForte Changed tokenised message
104 PAY_6361_USER_TABLE_UNIQUE
105 to hard coded message
106 PAY_7682_USER_ORG_TABLE_UNIQUE
107 70.34 02-AUG-95 JThuringer Change the way in which View All
108 security profiles are created.
109 If running against 10.5, set the
110 secure_oracle_username to the HR Oracle
111 ID. If running against 10.6 or later use
112 the APPS Oracle ID.
113 70.35 11-Sep-95 SDesai Check that the organization is not a
114 beneficiary (org_predel_check).
115 110.1 05-aug-97 mstewart Removed reference to now obsolete
116 secure_oracle_username column
117 110.2 19-AUG-97 DKerr Check hr_all_organization_units rather
118 than view when inserting sec. profile.
119 110.3 19-SEP-97 DKerr Ensure that the security profile id
120 for the setup business group is 0.
121 Removed obsolete code from earlier
122 releases.
123 110.4 25-JAN-98 GPerry Added in benefits installation check
124 in order to seed life events for
125 a business_group. This consisted of
126 alterations to insert_bus_grp_details.
127 Written in dynamic PLSQL as benefits
128 is still BETA.
129 110.5 16-JUN-98 GPerry Changed name of benefits special
130 procedure for use when seeding all
131 benefits data. Written in dynamic
132 PLSQL as product is BETA. Procedure
133 seed_life_events =>
134 becomes seed_benefit_data.
135 Added call to seed person types.
136 110.6 16-JUN-98 GPerry Didn't dual maintain first time.
137 110.7 28-OCT-98 STee Added call to seed action types
138 and communication types.
139 115.3 10-DEC-98 VTreiger MLS modications for New Business Group.
140 115.4 11-DEC-98 MStewart Added code in insert_bus_grp_details
141 to create a security group and
142 populate the org_information14 column.
143 115.5 29-DEC-98 VTreiger Modified per_person_types table population,
144 because we support only one table
145 per_startup_person_types_tl.
146
147 115.6 13-JAN-99 MStewart Removed code to create security group
148 and populate the org_info14 column
149 since this is now done elsewhere.
150 115.7 20-JAN-99 VTreiger Modified per_person_types table population,
151 because the latest version of translated table
152 PER_STARTUP_PERSON_TYPES_TL now has column
153 DEFAULT_FLAG.
154 115.11 11-JUN-99 MElori-M Added cursor get_usr_rows and code to
155 insert rows into pay_user_column_instances_f
156 whenever a new business group is created.
157 Added commit statement at the end of package
158 script.
159 115.12 03-NOV-99 STee Added call to seed regulations.
160 115.15 14-Dec-99 Tmathers Moved seed benefits data
161 out of US only code.
162 115.16 05-Jun-00 CCarter Added parameter p_org_information6
163 to insert_bus_grp_details in order to
164 perform an insert to PER_JOB_GROUPS to
165 create a Job Group everytime a Business
166 Group is created.
167 115.17 30-Oct-00 VTreiger Added check for api_dml call to provide
168 processing when using Org APIs.
169 115.18 06-jun-01 Tmathers Changed check for ben install
170 prod 805 to prod 800 HR so OSB users
171 automatically get seed data created on
172 Busines group creation. DOes nothing
173 if HR is in shared mode( as no benefits
174 Advanced, Standard or Basic exist then.)
175 WWBUG 1771423.
176 115.19 07-Dec-01 DCasemor Bug 2140866.
177 Passsed org_security_mode to the insert
178 of the default security profile for a
179 new Business Group.
180 115.20 11-Dec-01 DCasemor Fixed GSCC compliance errors.
181
182 115.21 170Apr-02 ACowan Added view_all_cwk column to
183 default security profile insert
184
185 ACowan Added insert to per_number_generation
186 _controls for CWK type.
187 115.24 09-JUN-02 ACowan Added view_all_contacts column to
188 default security profile insert.
189 115.25 18-Jun-02 M Bocutt 2407927 Use first 60 characters for the name
190 of the default consolidation set created
191 when BG info is saved. Cope with
192 duplicates by not creating the set
193 although this should never happen.
194 115.28 09-DEC-03 S Nukala 3303179 Used l_row_count instead of using
195 SQL%ROWCOUNT multiple times.
196 115.29 09-DEC-03 S Nukala 3303179 Corrected another scenario along
197 with the 2 places mentioned above.
198 115.30 10-FEB-04 D Casemore 3346940 Included the 3 columns added to
199 the security profiles table as
200 part of the Assignment and User
201 security enhancements.
202 115.31 21-JUN-04 adudekul 3648765 Performance fixes.
203 115.32 24-NOV-04 kjagadee 4029500 Modified proc insert_bus_grp_details
204 to include the new column
205 view_all_candidates_flag as part of
206 Candidate Security enhancements.
207 115.33 06-FEB-08 pchowdav 6792619 Modified procedure insert_bus_grp_details.
208 =================================================================
209 */
210
211 ---------------------- ins_si_type -------------------------
212 procedure ins_si_type ( p_business_group_id NUMBER,
213 p_creation_date DATE,
214 p_created_by NUMBER,
215 p_last_update_date DATE,
216 p_last_updated_by NUMBER,
217 p_last_update_login NUMBER,
218 p_flex_num NUMBER,
219 p_flex_category VARCHAR2 ) is
220 /*
221 NAME:
222 ins_si_type
223 DESCRIPTION
224 Enable ADA Special Information Types for a US business group.
225 PARAMETERS
226 */
227 --
228 l_special_info_type_id number;
229 begin
230 --
231 hr_utility.set_location('hr_organization.ins_si_type', 1);
232 select per_special_info_types_s.nextval
233 into l_special_info_type_id
234 from dual;
235 --
236 insert into per_special_info_types
237 (special_information_type_id,
238 business_group_id,
239 id_flex_num,
240 enabled_flag,
241 creation_date,
242 created_by,
243 last_update_date,
244 last_updated_by,
245 last_update_login)
246 values (l_special_info_type_id,
247 p_business_group_id,
248 p_flex_num,
249 'Y',
250 p_creation_date,
251 p_created_by,
252 p_last_update_date,
253 p_last_updated_by,
254 p_last_update_login);
255 --
256 insert into per_special_info_type_usages
257 (special_info_category,
258 special_information_type_id,
259 last_update_date,
260 last_updated_by,
261 last_update_login,
262 created_by,
263 creation_date)
264 values
265 (p_flex_category,
266 l_special_info_type_id,
267 p_last_update_date,
268 p_last_updated_by,
269 p_last_update_login,
270 p_created_by,
271 p_creation_date);
272 --
273 --
274 end ins_si_type;
275 --
276 procedure seed_benefit_data(p_business_group_id in number) is
277 --
278 l_cursor_handle integer;
279 l_dbms_sql_feedback integer;
280 --
281 begin
282 --
283 hr_utility.set_location('Entering hr_organization.seed_benefit_data',1);
284 --
285 -- Seed benefit life events
286 --
287 l_cursor_handle := dbms_sql.open_cursor;
288 dbms_sql.parse(l_cursor_handle,
289 'begin ben_seed_life_events.seed_life_events(:business_group_id); end;',
290 dbms_sql.v7);
291 dbms_sql.bind_variable(l_cursor_handle,':business_group_id',p_business_group_id);
292 l_dbms_sql_feedback := dbms_sql.execute(l_cursor_handle);
293 dbms_sql.close_cursor(l_cursor_handle);
294 --
295 -- Seed benefit person types
296 --
297 /*
298 l_cursor_handle := dbms_sql.open_cursor;
299 dbms_sql.parse(l_cursor_handle,
300 'begin ben_seed_person_types.seed_person_types(:business_group_id); end;',
301 dbms_sql.v7);
302 dbms_sql.bind_variable(l_cursor_handle,':business_group_id',p_business_group_id);
303 l_dbms_sql_feedback := dbms_sql.execute(l_cursor_handle);
304 dbms_sql.close_cursor(l_cursor_handle);
305 */
306 --
307 -- Seed enrollment action item types.
308 --
309 l_cursor_handle := dbms_sql.open_cursor;
310 dbms_sql.parse(l_cursor_handle,
311 'begin ben_seed_action_item_types.seed_action_item_types(:business_group_id); end;',
312 dbms_sql.v7);
313 dbms_sql.bind_variable(l_cursor_handle,':business_group_id',p_business_group_id);
314 l_dbms_sql_feedback := dbms_sql.execute(l_cursor_handle);
315 dbms_sql.close_cursor(l_cursor_handle);
316 --
317 -- Seed communication types.
318 --
319 l_cursor_handle := dbms_sql.open_cursor;
320 dbms_sql.parse(l_cursor_handle,
321 'begin ben_seed_communication_types.seed_communication_types(:business_group_id); end;',
322 dbms_sql.v7);
323 dbms_sql.bind_variable(l_cursor_handle,':business_group_id',p_business_group_id);
324 l_dbms_sql_feedback := dbms_sql.execute(l_cursor_handle);
325 dbms_sql.close_cursor(l_cursor_handle);
326 --
327 -- Seed regulations.
328 --
329 l_cursor_handle := dbms_sql.open_cursor;
330 dbms_sql.parse(l_cursor_handle,
331 'begin ben_seed_regulations.seed_regulations(:business_group_id); end;',
332 dbms_sql.v7);
333 dbms_sql.bind_variable(l_cursor_handle,':business_group_id',p_business_group_id);
334 l_dbms_sql_feedback := dbms_sql.execute(l_cursor_handle);
335 dbms_sql.close_cursor(l_cursor_handle);
336 --
337 hr_utility.set_location('Leaving hr_organization.seed_benefit_data',1);
338 --
339 end seed_benefit_data;
340 ----------------------- insert_bus_grp_details -------------------
341 -- Called by hr_org_info_ari
342 --
343 PROCEDURE insert_bus_grp_details (p_organization_id NUMBER
344 ,p_org_information9 VARCHAR2
345 ,p_org_information6 VARCHAR2
346 ,p_last_update_date DATE
347 ,p_last_updated_by NUMBER
348 ,p_last_update_login NUMBER
349 ,p_created_by NUMBER
350 ,p_creation_date DATE)
351 IS
352 --
353 cursor chk_ada_enabled is
354 select 'Y'
355 from fnd_id_flex_structures
356 where enabled_flag = 'Y'
357 and id_flex_num in (50129, 50130)
358 and id_flex_code = 'PEA';
359 --
360 cursor chk_osha_enabled is
361 select 'Y'
362 from fnd_id_flex_structures
363 where enabled_flag = 'Y'
364 and id_flex_num = 50131
365 and id_flex_code = 'PEA';
366 --
367 cursor chk_hr_installed is
368 select null
369 from fnd_product_installations
370 where application_id = 800
371 and status = 'I';
372 --
373 cursor sel_id_flex_num (p_flex_type varchar2) is
374 select to_number(rule_mode)
375 from pay_legislation_rules
376 where legislation_code = 'US'
377 and rule_type = p_flex_type;
378 --
379 cursor sel_startup_per_types is
380 select system_person_type,user_person_type
381 ,default_flag
382 from per_startup_person_types_tl
383 where userenv('LANG') = language
384 order by system_person_type,user_person_type;
385 --
386 cursor get_usr_rows is
387
388 select distinct
389 ur.user_row_id, uc.user_column_id
390 from
391 pay_user_columns uc,
392 pay_user_tables ut,
393 pay_user_rows_f ur
394 where
395 ut.user_table_name = 'EXCHANGE_RATE_TYPES'
396 and
397 ur.user_table_id = ut.user_table_id
398 and
399 uc.user_table_id = ur.user_table_id
400 and ut.user_table_id = uc.user_table_id -- Added for bug 3648765
401 and
402 uc.user_column_name = 'Conversion Rate Type';
403 --
404 l_apps_account VARCHAR2(1) := null;
405 l_install_mode VARCHAR2(1) := 'N';
406 l_ada_enabled VARCHAR2(1) := 'N';
407 l_osha_enabled VARCHAR2(1) := 'N';
408 l_dummy VARCHAR2(1);
409 l_disability_id_flex_num NUMBER;
410 l_disability_acc_id_flex_num NUMBER;
411 l_osha_id_flex_num NUMBER;
412 l_security_group_name hr_all_organization_units.name%TYPE;
413 l_security_group_id NUMBER;
414 --
415 l_system_person_type VARCHAR2(30);
416 l_system_person_type_old VARCHAR2(30) := ' ';
417 l_user_person_type VARCHAR2(80);
418 l_default_flag VARCHAR2(30);
419 l_row_count NUMBER := 0;
420 l_person_type_id NUMBER;
421 --
422 l_usr_row_id NUMBER(9);
423 l_usr_col_id NUMBER(9);
424 --
425 c_consolidation_set_name_len NUMBER(9) := 60;
426 begin
427 --
428 --
429 hr_utility.set_location('hr_organization.insert_bus_grp_details',1);
430 -- MLS modification
431 -- we have only one table per_startup_person_types_tl !!!!
432 INSERT INTO per_person_types
433 (seeded_person_type_key
434 ,person_type_id
435 ,active_flag
436 ,business_group_id
437 ,default_flag
438 ,system_person_type
439 ,user_person_type
440 )
441 SELECT
442 seeded_person_type_key
443 ,per_person_types_s.nextval
444 ,'Y'
445 ,p_organization_id
446 ,psp.default_flag
447 ,psp.system_person_type
448 ,psp.user_person_type
449 FROM per_startup_person_types_tl psp
450 WHERE psp.language = userenv('LANG');
451 --
452 -- Bug Number: 3303179: Used l_ow_count instead of using SQL%ROWCOUNT multiple times.
453 --
454 l_row_count := SQL%ROWCOUNT ;
455 hr_utility.set_location('rows : '||to_number(l_row_count),99);
456 --
457 if l_row_count = 0 then
458 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
459 hr_utility.set_message_token('PROCEDURE',
460 'hr_organization.insert_bus_grp_details');
461 hr_utility.set_message_token('STEP','1');
462 hr_utility.raise_error;
463 end if;
464 --
465 l_row_count := 0;
466 --
467 -- MLS
468 --
469 hr_utility.set_location('hr_organization.insert_bus_grp_details',101);
470 INSERT INTO per_person_types_tl
471 (person_type_id
472 ,user_person_type
473 ,language
474 ,source_lang
475 )
476 SELECT
477 ppt.person_type_id
478 ,pptl.user_person_type
479 ,pptl.language
480 ,pptl.source_lang
481 FROM PER_PERSON_TYPES ppt,
482 PER_STARTUP_PERSON_TYPES_TL pptl
483 WHERE ppt.business_group_id = p_organization_id
484 AND ppt.seeded_person_type_key = pptl.seeded_person_type_key;
485 --
486 -- Bug Number: 3303179: Used l_ow_count instead of using SQL%ROWCOUNT multiple times.
487 --
488 l_row_count := SQL%ROWCOUNT;
489 hr_utility.set_location('rows : '||to_number(l_row_count),100);
490 --
491 if l_row_count = 0 then
492 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
493 hr_utility.set_message_token('PROCEDURE',
494 'hr_organization.insert_bus_grp_details');
495 hr_utility.set_message_token('STEP','101');
496 hr_utility.raise_error;
497 end if;
498 --
499 l_row_count := 0;
500 --
501 -- MLS end
502 --
503 hr_utility.set_location('hr_organization.insert_bus_grp_details',2);
504 INSERT INTO per_number_generation_controls
505 (business_group_id
506 ,type
507 ,next_value)
508 VALUES
509 (p_organization_id
510 ,'EMP'
511 ,1);
512 --
513 --
514 hr_utility.set_location('hr_organization.insert_bus_grp_details',3);
515 INSERT INTO per_number_generation_controls
516 (business_group_id
517 ,type
518 ,next_value)
519 values
520 (p_organization_id
521 ,'APL'
522 ,1);
523 --
524 --
525 hr_utility.set_location('hr_organization.insert_bus_grp_details',4);
526 INSERT INTO per_number_generation_controls
527 (business_group_id
528 ,type
529 ,next_value)
530 values
531 (p_organization_id
532 ,'CWK'
533 ,1);
534 --
535 begin
536 hr_utility.set_location('hr_organization.insert_bus_grp_details',5);
537 SELECT 'Y'
538 INTO l_install_mode
539 FROM sys.dual
540 WHERE NOT EXISTS (SELECT null
541 FROM fnd_product_installations
542 WHERE application_id = 800
543 AND status IN ('I','S'));
544 --
545 exception when NO_DATA_FOUND then null;
546 end;
547 --
548 -- If AOL product version is 6.0.27 then we have a 10.5 install and the APPS
549 -- account does not exist.
550 -- If AOL product version is not 6.0.27 we have an install of 10.6 or higher,
551 -- and the APPS account will exist.
552 --
553 begin
554 hr_utility.set_location('hr_organization.insert_bus_grp_details',6);
555 SELECT 'N'
556 INTO l_apps_account
557 FROM fnd_product_installations
558 WHERE application_id = 0
559 AND product_version = '6.0.27';
560 --
561 exception when NO_DATA_FOUND then l_apps_account := 'Y' ;
562 end;
563 --
564
565 if ( l_apps_account = 'Y' ) then
566
567 -- We are running 10.6 or later and the APPS account exists
568 --
569 -- if l_install_mode = 'Y' we have no way of accessing the APPS oracle ID.
570 -- So we have to create the View All security profile by running a script
571 -- that takes, as a parameter, the AOL username. We can derive the APPS
572 -- Oracle ID if we know the AOL username.
573 --
574 if l_install_mode = 'N' then
575 --
576 -- Set the secure_oracle_username for the View All security profile to the
577 -- Oracle ID for the APPS account. This can be derived by looking in
578 -- fnd_oracle_userid f1r an oracle_username with oracle_id = 900
579 --
580 hr_utility.set_location('hr_organization.insert_bus_grp_details',8);
581 INSERT INTO per_security_profiles
582 (security_profile_id
583 ,business_group_id
584 ,include_top_organization_flag
585 ,include_top_position_flag
586 ,security_profile_name
587 ,view_all_flag
588 ,view_all_organizations_flag
589 ,view_all_payrolls_flag
590 ,view_all_positions_flag
591 ,view_all_applicants_flag
592 ,view_all_employees_flag
593 ,view_all_cwk_flag
594 ,view_all_contacts_flag
595 ,view_all_candidates_flag
596 ,reporting_oracle_username
597 ,last_update_date
598 ,last_updated_by
599 ,last_update_login
600 ,created_by
601 ,creation_date
602 ,org_security_mode
603 ,restrict_on_individual_asg
604 ,top_organization_method
605 ,top_position_method)
606 SELECT decode(p_organization_id,0,0,per_security_profiles_s.nextval)
607 ,p_organization_id
608 ,'Y'
609 ,'Y'
610 ,hou.name
611 ,'Y'
612 ,'Y'
613 ,'Y'
614 ,'Y'
615 ,'Y'
616 ,'Y'
617 ,'Y'
618 ,'Y'
619 ,'Y'
620 ,null
621 ,p_last_update_date
622 ,p_last_updated_by
623 ,p_last_update_login
624 ,p_created_by
625 ,p_creation_date
626 ,'NONE'
627 ,'N'
628 ,'S'
629 ,'S'
630 FROM hr_all_organization_units hou,
631 fnd_oracle_userid o
632 WHERE hou.organization_id = p_organization_id
633 AND o.oracle_id = 900;
634 --
635 if SQL%ROWCOUNT = 0 then
636 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
637 hr_utility.set_message_token('PROCEDURE',
638 'hr_organization.insert_bus_grp_details');
639 hr_utility.set_message_token('STEP','8');
640 hr_utility.raise_error;
641 end if;
642 --
643 end if;
644 --
645 end if;
646 --
647 insert into PER_JOB_GROUPS
648 (job_group_id
649 ,business_group_id
650 ,legislation_code
651 ,internal_name
652 ,displayed_name
653 ,id_flex_num
654 ,master_flag
655 ,object_version_number
656 ,creation_date
657 ,created_by
658 ,last_update_date
659 ,last_updated_by
660 ,last_update_login)
661 values (per_job_groups_s.nextval
662 ,p_organization_id
663 ,null
664 ,'HR_'||to_char(p_organization_id)
665 ,'HR_'||to_char(p_organization_id)
666 ,p_org_information6
667 ,'N'
668 ,1
669 ,p_creation_date
670 ,p_created_by
671 ,p_last_update_date
672 ,p_last_updated_by
673 ,p_last_update_login);
674 --
675 hr_utility.set_location('hr_organization.insert_bus_grp_details',9);
676 begin
677 INSERT INTO pay_consolidation_sets
678 (consolidation_set_id
679 ,business_group_id
680 ,consolidation_set_name
681 ,comments
682 ,last_update_date
683 ,last_updated_by
684 ,last_update_login
685 ,created_by
686 ,creation_date)
687 SELECT pay_consolidation_sets_s.nextval
688 ,p_organization_id
689 ,substr(hou.name,1,c_consolidation_set_name_len)
690 ,null
691 ,p_last_update_date
692 ,p_last_updated_by
693 ,p_last_update_login
694 ,p_created_by
695 ,p_creation_date
696 FROM hr_all_organization_units hou
697 WHERE hou.organization_id = p_organization_id;
698 --
699 --
700 -- Bug Number: 3303179: Used l_ow_count instead of using SQL%ROWCOUNT after the hr_utility call.
701 --
702 l_row_count := SQL%ROWCOUNT;
703 --
704 hr_utility.set_location('hr_organization.insert_bus_grp_details',91);
705
706 if l_row_count = 0 then
707 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
708 hr_utility.set_message_token('PROCEDURE',
709 'hr_organization.insert_bus_grp_details');
710 hr_utility.set_message_token('STEP','9');
711 hr_utility.raise_error;
712 end if;
713 exception
714 when no_data_found then
715 hr_utility.set_location('hr_organization.insert_bus_grp_details',92);
716 null;
717 when others then
718 hr_utility.set_location('hr_organization.insert_bus_grp_details',93);
719 raise;
720 end;
721 --
722 l_row_count := 0;
723 --
724 open get_usr_rows;
725 loop
726 --
727 fetch get_usr_rows into l_usr_row_id, l_usr_col_id;
728 exit when get_usr_rows%NOTFOUND;
729 --
730 hr_utility.set_location('hr_organization.insert_bus_grp_details',10);
731 -- VT added condition for Org API call
732 IF NOT hr_ori_shd.return_api_dml_status THEN
733 INSERT INTO pay_user_column_instances_f
734 (user_column_instance_id
735 ,effective_start_date
736 ,effective_end_date
737 ,user_row_id
738 ,user_column_id
739 ,business_group_id
740 ,legislation_code
741 ,legislation_subgroup
742 ,value
743 ,last_update_date
744 ,last_updated_by
745 ,last_update_login
746 ,created_by
747 ,creation_date)
748 SELECT
749 pay_user_column_instances_s.nextval
750 ,fnd_sessions.effective_date
751 ,hr_general.end_of_time
752 ,l_usr_row_id
753 ,l_usr_col_id
754 ,p_organization_id
755 ,null
756 ,null
757 ,'Corporate'
758 ,p_last_update_date
759 ,p_last_updated_by
760 ,p_last_update_login
761 ,p_created_by
762 ,p_creation_date
763 FROM fnd_sessions
764 WHERE session_id = userenv('sessionid');
765 ELSE
766 INSERT INTO pay_user_column_instances_f
767 (user_column_instance_id
768 ,effective_start_date
769 ,effective_end_date
770 ,user_row_id
771 ,user_column_id
772 ,business_group_id
773 ,legislation_code
774 ,legislation_subgroup
775 ,value
776 ,last_update_date
777 ,last_updated_by
778 ,last_update_login
779 ,created_by
780 ,creation_date)
781 SELECT
782 pay_user_column_instances_s.nextval
783 ,hr_general.start_of_time
784 ,hr_general.end_of_time
785 ,l_usr_row_id
786 ,l_usr_col_id
787 ,p_organization_id
788 ,null
789 ,null
790 ,'Corporate'
791 ,p_last_update_date
792 ,p_last_updated_by
793 ,p_last_update_login
794 ,p_created_by
795 ,p_creation_date
796 FROM sys.dual;
797 END IF;
798 --
799 --fix for bug 6792619.
800 l_row_count := SQL%ROWCOUNT;
801 --
802 hr_utility.set_location('hr_organization.insert_bus_grp_details',101);
803
804 if l_row_count = 0 then
805 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
806 hr_utility.set_message_token('PROCEDURE',
807 'hr_organization.insert_bus_grp_details');
808 hr_utility.set_message_token('STEP','9');
809 hr_utility.raise_error;
810 end if;
811 hr_utility.set_location('hr_organization.insert_bus_grp_details',102);
812
813 end loop;
814 close get_usr_rows;
815 --
816 --
817 -- Enable ADA flex structures for US business groups.
818 --
819 -- NOTE: This procedure assumes that 2 special info type key flex structures
820 -- (id_flex_code = 'PEA') have been seeded :
821 --
822 -- ID_FLEX_STRUCTURE_NAME ID_FLEX_NUM
823 -- ---------------------- -----------
824 -- ADA Disabilities 50129
825 -- ADA Disability Accomodations 50130
826 --
827 -- This procedure cannot rely on the id_flex_nums being present in
828 -- pay_legislation_rules because pay_legislation_rules might
829 -- not been seeded with data at the time that this procedure is run.
830 -- pay_legislation_rules is seeded as a post-install step as part of
831 -- US startup data delivery. This procedure is run by Autoinstall
832 -- during creation of the Setup Business Group.
833 --
834 --
835 hr_utility.set_location('hr_organization.insert_bus_grp_details', 8);
836 --
837 -- Ideally we would create ADA special info type rows if business group being
838 -- inserted is a US business group. But cannot check legislation_code on
839 -- per_business_groups due to mutating table error.
840 -- Hence checking to see if ADA key flex structures enabled for PEA key flex.
841 --
842 --
843 --
844 if p_org_information9 = 'US' then
845 --
846 open chk_ada_enabled;
847 fetch chk_ada_enabled into l_ada_enabled;
848 close chk_ada_enabled;
849 --
850 if l_ada_enabled = 'Y' then
851 --
852 l_disability_id_flex_num := 50129;
853 l_disability_acc_id_flex_num := 50130;
854 --
855 open sel_id_flex_num('ADA_DIS');
856 fetch sel_id_flex_num into l_disability_id_flex_num;
857 close sel_id_flex_num;
858 --
859 open sel_id_flex_num('ADA_DIS_ACC');
860 fetch sel_id_flex_num into l_disability_acc_id_flex_num;
861 close sel_id_flex_num;
862 --
863 ins_si_type (p_business_group_id => p_organization_id,
864 p_creation_date => p_creation_date,
865 p_created_by => p_created_by,
866 p_last_update_date => p_last_update_date,
867 p_last_updated_by => p_last_updated_by,
868 p_last_update_login => p_last_update_login,
869 p_flex_num => l_disability_id_flex_num,
870 p_flex_category => 'ADA');
871 --
872 ins_si_type (p_business_group_id => p_organization_id,
873 p_creation_date => p_creation_date,
874 p_created_by => p_created_by,
875 p_last_update_date => p_last_update_date,
876 p_last_updated_by => p_last_updated_by,
877 p_last_update_login => p_last_update_login,
878 p_flex_num => l_disability_acc_id_flex_num,
879 p_flex_category => 'ADA');
880 --
881 end if;
882 --
883 --
884 -- Enable OSHA flex structure for US business groups.
885 --
886 -- NOTE: This procedure assumes that 1 special info type key flex structures
887 -- (id_flex_code = 'PEA') has been seeded :
888 --
889 -- ID_FLEX_STRUCTURE_NAME ID_FLEX_NUM
890 -- ---------------------- -----------
891 -- OSHA-reportable Incident 50131
892 --
893 --
894 open chk_osha_enabled;
895 fetch chk_osha_enabled into l_osha_enabled;
896 close chk_osha_enabled;
897 --
898 if l_osha_enabled = 'Y' then
899 --
900 l_osha_id_flex_num := 50131;
901 --
902 open sel_id_flex_num('OSHA');
903 fetch sel_id_flex_num into l_osha_id_flex_num;
904 close sel_id_flex_num;
905 --
906 ins_si_type (p_business_group_id => p_organization_id,
907 p_creation_date => p_creation_date,
908 p_created_by => p_created_by,
909 p_last_update_date => p_last_update_date,
910 p_last_updated_by => p_last_updated_by,
911 p_last_update_login => p_last_update_login,
912 p_flex_num => l_osha_id_flex_num,
913 p_flex_category => 'OSHA');
914 --
915 end if;
916 --
917 -- For US OSHA, Default case numbers have to be populated
918 -- Added the following code for US OSHA specific changes
919 --
920 for x in 1900 .. 2200 loop
921 --
922 insert into per_us_osha_numbers(
923 case_year,
924 business_group_id,
925 next_value,
926 last_update_date,
927 last_updated_by,
928 last_update_login,
929 created_by,
930 creation_date)
931 values (x,
932 p_organization_id,
933 1,
934 p_last_update_date,
935 p_last_updated_by,
936 p_last_update_login,
937 p_created_by,
938 p_creation_date
939 );
940 --
941 end loop;
942 --
943 --
944 end if; -- enabling US specific flex structures.
945 --
946 -- Check if HR fully installed if not dont create ben data.
947 -- used to be a check for BEN fully but OSB requires the data also.
948 -- tm 06/12/01
949 -- 1771423.
950 --
951 open chk_hr_installed;
952 --
953 fetch chk_hr_installed into l_dummy;
954 if chk_hr_installed%found then
955 --
956 seed_benefit_data(p_business_group_id => p_organization_id);
957 --
958 end if;
959 --
960 close chk_hr_installed;
961 --
962
963 end insert_bus_grp_details;
964 --
965 --
966 --
967 PROCEDURE unique_name
968 (p_business_group_id NUMBER,
969 p_organization_id NUMBER,
970 p_organization_name VARCHAR2)
971 --
972 IS
973 --
974 org_check VARCHAR2(1);
975 --
976 begin
977 --
978 --
979 hr_utility.set_location('hr_organization.unique_name',1);
980 SELECT 'Y'
981 INTO org_check
982 FROM sys.dual
983 WHERE exists
984 (
985 SELECT 'Name Already Exists'
986 FROM hr_organization_units org
987 WHERE (org.organization_id <> p_organization_id
988 OR p_organization_id IS NULL)
989 AND p_organization_name = org.name
990 AND (org.business_group_id + 0 = p_business_group_id
991 or p_organization_id + 0 = p_business_group_id)
992 );
993 --
994 if org_check = 'Y' then
995 hr_utility.set_message(801,'PAY_7682_USER_ORG_TABLE_UNIQUE');
996 hr_utility.raise_error;
997 end if;
998 --
999 exception
1000 when NO_DATA_FOUND then null ;
1001 --
1002 end unique_name;
1003 --
1004 --
1005 --
1006 PROCEDURE date_range
1007 (p_date_from DATE,
1008 p_date_to DATE)
1009 --
1010 IS
1011 --
1012 l_eot DATE := to_date('31/12/4712','DD/MM/YYYY');
1013 --
1014 begin
1015 --
1016 hr_utility.set_location('hr_organization.date_range',1);
1017 --
1018 if p_date_from is null then
1019 hr_utility.set_message(801,'HR_6021_ALL_START_END_DATE');
1020 hr_utility.raise_error;
1021 elsif p_date_from > nvl(p_date_to, l_eot) then
1022 hr_utility.set_message(801,'HR_6021_ALL_START_END_DATE');
1023 hr_utility.raise_error;
1024 end if;
1025 --
1026 --
1027 end date_range;
1028 --
1029 --
1030 --------------------- BEGIN: org_predel_check --------------------------------
1031 procedure org_predel_check(p_organization_id INTEGER
1032 ,p_business_group_id INTEGER) is
1033 /*
1034 NAME
1035 org_predel_check
1036 DESCRIPTION
1037 Battery of tests to see if an organization may be deleted.
1038 PARAMETERS
1039 p_organization_id : Organization Id of Organization to be deleted.
1040 p_business_group_id : Business Group id of rganization to be deleted.
1041 */
1042 --
1043 -- Storage Variable.
1044 --
1045 l_test_func varchar2(60);
1046 --
1047 begin
1048 -- If the organization id equals the business group id then
1049 -- it is a business group and so do all relavant checks for Business group.
1050 if p_organization_id = p_business_group_id then
1051 begin
1052 begin
1053 -- Do Any rows Exist in PER_PEOPLE_F.
1054 hr_utility.set_location('hr_organization.org_predel_check',1);
1055 select '1'
1056 into l_test_func
1057 from sys.dual
1058 where exists ( select 1
1059 from PER_PEOPLE_F x
1060 where x.business_group_id = p_business_group_id);
1061 --
1062 if SQL%ROWCOUNT >0 THEN
1063 hr_utility.set_message(801,'HR_6130_ORG_PEOPLE_EXIST');
1064 hr_utility.raise_error;
1065 end if;
1066 exception
1067 when NO_DATA_FOUND THEN
1068 null;
1069 end;
1070 --
1071 begin
1072 -- Do Any rows Exist in HR_ORGANIZATION_UNITS.
1073 hr_utility.set_location('hr_organization.org_predel_check',2);
1074 select '1'
1075 into l_test_func
1076 from sys.dual
1077 where exists ( select 1
1078 from HR_ORGANIZATION_UNITS x
1079 where x.business_group_id = p_business_group_id
1080 and x.organization_id <> p_business_group_id);
1081 --
1082 if SQL%ROWCOUNT >0 THEN
1083 hr_utility.set_message(801,'HR_6571_ORG_ORG_EXIST');
1084 hr_utility.raise_error;
1085 end if;
1086 exception
1087 when NO_DATA_FOUND THEN
1088 null;
1089 end;
1090 --
1091 --
1092 begin
1093 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
1094 hr_utility.set_location('hr_organization.org_predel_check',3);
1095 select '1'
1096 into l_test_func
1097 from sys.dual
1098 where exists ( select 1
1099 from PER_ORG_STRUCTURE_ELEMENTS x
1100 where x.business_group_id = p_business_group_id);
1101 --
1102 if SQL%ROWCOUNT >0 THEN
1103 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
1104 hr_utility.raise_error;
1105 end if;
1106 exception
1107 when NO_DATA_FOUND THEN
1108 null;
1109 end;
1110 end;
1111 end if;
1112 --
1113 -- Now do all Organization specific checks.
1114 --
1115 begin
1116 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
1117 hr_utility.set_location('hr_organization.org_predel_check',4);
1118 select '1'
1119 into l_test_func
1120 from sys.dual
1121 where exists ( select 1
1122 from PER_ORG_STRUCTURE_ELEMENTS x
1123 where x.organization_id_child = p_organization_id);
1124 --
1125 if SQL%ROWCOUNT >0 THEN
1126 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
1127 hr_utility.raise_error;
1128 end if;
1129 exception
1130 when NO_DATA_FOUND THEN
1131 null;
1132 end;
1133 --
1134 begin
1135 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
1136 hr_utility.set_location('hr_organization.org_predel_check',5);
1137 select '1'
1138 into l_test_func
1139 from sys.dual
1140 where exists ( select 1
1141 from PER_ORG_STRUCTURE_ELEMENTS x
1142 where x.organization_id_parent = p_organization_id);
1143 --
1144 if SQL%ROWCOUNT >0 THEN
1145 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
1146 hr_utility.raise_error;
1147 end if;
1148 exception
1149 when NO_DATA_FOUND THEN
1150 null;
1151 end;
1152 --
1153 begin
1154 -- Do any rows exist in BEN_BENEFICIARIES_F
1155 hr_utility.set_location('hr_organization.org_predel_check', 6);
1156 select '1'
1157 into l_test_func
1158 from sys.dual
1159 where exists (select 1
1160 from BEN_BENEFICIARIES_F x
1161 where x.source_id = p_organization_id
1162 and x.source_type = 'O');
1163 --
1164 if SQL%ROWCOUNT >0 THEN
1165 hr_utility.set_message(801,'HR_7994_ORG_BENEFICIARY_EXISTS');
1166 hr_utility.raise_error;
1167 end if;
1168 exception
1169 when NO_DATA_FOUND THEN
1170 null;
1171 end;
1172 --
1173 end org_predel_check;
1174 --------------------- END: org_predel_check -----------------------------------
1175 --
1176 ------------------- BEGIN: hr_weak_bg_chk -----------------------------------
1177 procedure hr_weak_bg_chk(p_organization_id INTEGER) is
1178 /*
1179 NAME
1180 hr_weak_bg_chk
1181 DESCRIPTION
1182 Tests to see whether a business group may be created from an existing
1183 organization.
1184 PARAMETERS
1185 p_organization_id : Identifier of the organization.
1186 */
1187 --
1188 -- Local Storage Variable.
1189 l_test_func varchar2(60);
1190 --
1191 begin
1192 --
1193 begin
1194 -- Doing check on PER_ASSIGNMENTS_F.
1195 hr_utility.set_location('hr_organization.hr_weak_bg_chk',1);
1196 select '1'
1197 into l_test_func
1198 from sys.dual
1199 where exists ( select 1
1200 from PER_ASSIGNMENTS_F x
1201 where x.SOURCE_ORGANIZATION_ID = p_organization_id);
1202 --
1203 if SQL%ROWCOUNT >0 THEN
1204 hr_utility.set_message(801,'HR_6718_BG_ASS_EXIST');
1205 hr_utility.raise_error;
1206 end if;
1207 exception
1208 when NO_DATA_FOUND THEN
1209 null;
1210 end;
1211 --
1212 begin
1213 -- Doing check on PER_ASSIGNMENTS_F.
1214 hr_utility.set_location('hr_organization.hr_weak_bg_chk',2);
1215 select '1'
1216 into l_test_func
1217 from sys.dual
1218 where exists ( select 1
1219 from PER_ASSIGNMENTS_F x
1220 where x.ORGANIZATION_ID = p_organization_id);
1221 --
1222 if SQL%ROWCOUNT >0 THEN
1223 hr_utility.set_message(801,'HR_6718_BG_ASS_EXIST');
1224 hr_utility.raise_error;
1225 end if;
1226 exception
1227 when NO_DATA_FOUND THEN
1228 null;
1229 end;
1230 --
1231 begin
1232 -- Doing check on PER_ORG_STRUCTURE_ELEMENTS.
1233 hr_utility.set_location('hr_organization.hr_weak_bg_chk',3);
1234 select '1'
1235 into l_test_func
1236 from sys.dual
1237 where exists ( select 1
1238 from PER_ORG_STRUCTURE_ELEMENTS x
1239 where x.ORGANIZATION_ID_PARENT = p_organization_id);
1240 --
1241 if SQL%ROWCOUNT >0 THEN
1242 hr_utility.set_message(801,'HR_6722_BG_ORG_HIER');
1243 hr_utility.raise_error;
1244 end if;
1245 exception
1246 when NO_DATA_FOUND THEN
1247 null;
1248 end;
1249 --
1250 begin
1251 -- Doing check on PER_ORG_STRUCTURE_ELEMENTS.
1252 hr_utility.set_location('hr_organization.hr_weak_bg_chk',4);
1253 select '1'
1254 into l_test_func
1255 from sys.dual
1256 where exists ( select 1
1257 from PER_ORG_STRUCTURE_ELEMENTS x
1258 where x.ORGANIZATION_ID_CHILD = p_organization_id);
1259 --
1260 if SQL%ROWCOUNT >0 THEN
1261 hr_utility.set_message(801,'HR_6722_BG_ORG_HIER');
1262 hr_utility.raise_error;
1263 end if;
1264 exception
1265 when NO_DATA_FOUND THEN
1266 null;
1267 end;
1268 --
1269 --
1270 begin
1271 -- Doing check on PER_SECURITY_PROFILES.
1272 hr_utility.set_location('hr_organization.hr_weak_bg_chk',5);
1273 select '1'
1274 into l_test_func
1275 from sys.dual
1276 where exists ( select 1
1277 from PER_SECURITY_PROFILES x
1278 where x.ORGANIZATION_ID = p_organization_id);
1279 --
1280 if SQL%ROWCOUNT >0 THEN
1281 hr_utility.set_message(801,'HR_6724_BG_SEC_PROF_EXIST');
1282 hr_utility.raise_error;
1283 end if;
1284 exception
1285 when NO_DATA_FOUND THEN
1286 null;
1287 end;
1288 --
1289 end hr_weak_bg_chk;
1290 --------------------- END: hr_weak_bg_chk -----------------------------------
1291 --
1292 -- Procedure required due to FND PLSQL not being able to handle hr_message
1293 --
1294 procedure get_flex_msg is
1295 begin
1296 hr_utility.set_message(801,'HR_FLEX_DISPLAY_MSG');
1297 hr_utility.raise_error;
1298 end;
1299 ----------------------------------- End of get_flex_msg ----------------------
1300
1301 END hr_organization;
[Home] [Help]