[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
70.3 02-DEC-92 SZWILLIA Added Procedure to insert
Business Group Details
from hr_org_info_ari.
70.4 12-JAN-93 SZWILLIA Removed insert into status
types from insert bg details.
70.6 20-JAN-93 SZWILLIA Corrected error handling.
70.7 26-JAN-93 SZWILLIA Changed INSERT into
PER_SECURITY_PROFILES.
70.9 01-MAR-93 TMATHERS Added Procedure to execute
pre-delete checks(org_predel_check).
70.10 01-MAR-93 TMATHERS Allowed user to delete a business group
if the only organization it contained
was itself.
70.11 04-MAR-93 SZWILLIA Changed parameters to DATE
70.12 11-MAR-93 NKHAn Added 'exit' to the end
70.13 17-MAR-93 SZWILLIA Changed insert into security_profiles
70.14 31-MAR-93 TMATHERS Removed org_predel_check to seperate
package.
70.15 01-APR-93 TMATHERS Added shared organization predelete
checks procedure for CBB.
70.16 01-APR-93 TMATHERS Corrected mistake made by previous
Change.
70.17 05-APR-93 TMATHERS Took out Jobs and Positions references
and placed in a separate procedure.
In order that the applications who
choose not to use J and P's when
they have the Org CBB, can still have
pre-delete validation.
70.18 05-APR-93 TMATHERS Didn't like the ampersand in above
comment changed to and.
70.19 22-APR-93 TMATHERS Added hr_weak_bg_chk, fixed
unique_name to include checks
for business groups.
70.20 30-APR-93 TMATHERS Added exists clause to
unique name check to stop
TOO_MANY_ROWS exception firing.
When a BG and an org in the current
BG have the same name.
70.21 04-MAY-93 TMATHERS changed HR_ORGANIZATION_UNITS
to HR_ALL_ORGANIZATION_UNITS in
unique name check
70.22 06-MAY-93 SZWILLIA Changed above back as security does
not apply in packages.(Always created
in the base user).
Changed insert_bus_grp_details to
insert 'user' if running in install
mode (ie no rows in
FND_PRODUCT_INSTALLATIONS.
-------------------------------------------------------------------------------
70.25 04-AUG-93 TMATHERS removed reference to
product_security_type in
per_security_profiles.
70.26 02-JUN-94 TMathers Added get_flex_msg as a result of
using FND PLSQL in PERORDOR.
70.29 23-NOV-94 Rfine Suppressed index on business_group_id
70.30 15-DEC-94 Rfine Added code to insert a row into
pay_consolidation_sets for the new
business group.
70.31 17-Apr-95 SDesai/JThuringer Added ins_si_type procedure and code
for enabling ADA flex structures for
US business groups.
70.32 25-Apr-95 SDesai Code to enable OSHA flex structure;
than view when inserting sec. profile.
110.3 19-SEP-97 DKerr Ensure that the security profile id
for the setup business group is 0.
Removed obsolete code from earlier
releases.
110.4 25-JAN-98 GPerry Added in benefits installation check
in order to seed life events for
a business_group. This consisted of
alterations to insert_bus_grp_details.
Written in dynamic PLSQL as benefits
is still BETA.
110.5 16-JUN-98 GPerry Changed name of benefits special
procedure for use when seeding all
benefits data. Written in dynamic
PLSQL as product is BETA. Procedure
seed_life_events =>
becomes seed_benefit_data.
Added call to seed person types.
110.6 16-JUN-98 GPerry Didn't dual maintain first time.
110.7 28-OCT-98 STee Added call to seed action types
and communication types.
115.3 10-DEC-98 VTreiger MLS modications for New Business Group.
115.4 11-DEC-98 MStewart Added code in insert_bus_grp_details
to create a security group and
populate the org_information14 column.
115.5 29-DEC-98 VTreiger Modified per_person_types table population,
because we support only one table
per_startup_person_types_tl.
115.6 13-JAN-99 MStewart Removed code to create security group
and populate the org_info14 column
since this is now done elsewhere.
115.7 20-JAN-99 VTreiger Modified per_person_types table population,
because the latest version of translated table
PER_STARTUP_PERSON_TYPES_TL now has column
DEFAULT_FLAG.
115.11 11-JUN-99 MElori-M Added cursor get_usr_rows and code to
insert rows into pay_user_column_instances_f
whenever a new business group is created.
Added commit statement at the end of package
script.
115.12 03-NOV-99 STee Added call to seed regulations.
115.15 14-Dec-99 Tmathers Moved seed benefits data
out of US only code.
115.16 05-Jun-00 CCarter Added parameter p_org_information6
to insert_bus_grp_details in order to
perform an insert to PER_JOB_GROUPS to
create a Job Group everytime a Business
Group is created.
115.17 30-Oct-00 VTreiger Added check for api_dml call to provide
processing when using Org APIs.
115.18 06-jun-01 Tmathers Changed check for ben install
prod 805 to prod 800 HR so OSB users
automatically get seed data created on
Busines group creation. DOes nothing
if HR is in shared mode( as no benefits
Advanced, Standard or Basic exist then.)
WWBUG 1771423.
115.19 07-Dec-01 DCasemor Bug 2140866.
Passsed org_security_mode to the insert
of the default security profile for a
new Business Group.
115.20 11-Dec-01 DCasemor Fixed GSCC compliance errors.
115.21 170Apr-02 ACowan Added view_all_cwk column to
default security profile insert
ACowan Added insert to per_number_generation
_controls for CWK type.
115.24 09-JUN-02 ACowan Added view_all_contacts column to
default security profile insert.
115.25 18-Jun-02 M Bocutt 2407927 Use first 60 characters for the name
of the default consolidation set created
when BG info is saved. Cope with
duplicates by not creating the set
although this should never happen.
115.28 09-DEC-03 S Nukala 3303179 Used l_row_count instead of using
SQL%ROWCOUNT multiple times.
115.29 09-DEC-03 S Nukala 3303179 Corrected another scenario along
with the 2 places mentioned above.
115.30 10-FEB-04 D Casemore 3346940 Included the 3 columns added to
the security profiles table as
part of the Assignment and User
security enhancements.
115.31 21-JUN-04 adudekul 3648765 Performance fixes.
115.32 24-NOV-04 kjagadee 4029500 Modified proc insert_bus_grp_details
to include the new column
view_all_candidates_flag as part of
Candidate Security enhancements.
115.33 06-FEB-08 pchowdav 6792619 Modified procedure insert_bus_grp_details.
=================================================================
*/
---------------------- ins_si_type -------------------------
procedure ins_si_type ( p_business_group_id NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_flex_num NUMBER,
p_flex_category VARCHAR2 ) is
/*
NAME:
ins_si_type
DESCRIPTION
Enable ADA Special Information Types for a US business group.
PARAMETERS
*/
--
l_special_info_type_id number;
select per_special_info_types_s.nextval
into l_special_info_type_id
from dual;
insert into per_special_info_types
(special_information_type_id,
business_group_id,
id_flex_num,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values (l_special_info_type_id,
p_business_group_id,
p_flex_num,
'Y',
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login);
insert into per_special_info_type_usages
(special_info_category,
special_information_type_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(p_flex_category,
l_special_info_type_id,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date);
PROCEDURE insert_bus_grp_details (p_organization_id NUMBER
,p_org_information9 VARCHAR2
,p_org_information6 VARCHAR2
,p_last_update_date DATE
,p_last_updated_by NUMBER
,p_last_update_login NUMBER
,p_created_by NUMBER
,p_creation_date DATE)
IS
--
cursor chk_ada_enabled is
select 'Y'
from fnd_id_flex_structures
where enabled_flag = 'Y'
and id_flex_num in (50129, 50130)
and id_flex_code = 'PEA';
select 'Y'
from fnd_id_flex_structures
where enabled_flag = 'Y'
and id_flex_num = 50131
and id_flex_code = 'PEA';
select null
from fnd_product_installations
where application_id = 800
and status = 'I';
select to_number(rule_mode)
from pay_legislation_rules
where legislation_code = 'US'
and rule_type = p_flex_type;
select system_person_type,user_person_type
,default_flag
from per_startup_person_types_tl
where userenv('LANG') = language
order by system_person_type,user_person_type;
select distinct
ur.user_row_id, uc.user_column_id
from
pay_user_columns uc,
pay_user_tables ut,
pay_user_rows_f ur
where
ut.user_table_name = 'EXCHANGE_RATE_TYPES'
and
ur.user_table_id = ut.user_table_id
and
uc.user_table_id = ur.user_table_id
and ut.user_table_id = uc.user_table_id -- Added for bug 3648765
and
uc.user_column_name = 'Conversion Rate Type';
hr_utility.set_location('hr_organization.insert_bus_grp_details',1);
INSERT INTO per_person_types
(seeded_person_type_key
,person_type_id
,active_flag
,business_group_id
,default_flag
,system_person_type
,user_person_type
)
SELECT
seeded_person_type_key
,per_person_types_s.nextval
,'Y'
,p_organization_id
,psp.default_flag
,psp.system_person_type
,psp.user_person_type
FROM per_startup_person_types_tl psp
WHERE psp.language = userenv('LANG');
'hr_organization.insert_bus_grp_details');
hr_utility.set_location('hr_organization.insert_bus_grp_details',101);
INSERT INTO per_person_types_tl
(person_type_id
,user_person_type
,language
,source_lang
)
SELECT
ppt.person_type_id
,pptl.user_person_type
,pptl.language
,pptl.source_lang
FROM PER_PERSON_TYPES ppt,
PER_STARTUP_PERSON_TYPES_TL pptl
WHERE ppt.business_group_id = p_organization_id
AND ppt.seeded_person_type_key = pptl.seeded_person_type_key;
'hr_organization.insert_bus_grp_details');
hr_utility.set_location('hr_organization.insert_bus_grp_details',2);
INSERT INTO per_number_generation_controls
(business_group_id
,type
,next_value)
VALUES
(p_organization_id
,'EMP'
,1);
hr_utility.set_location('hr_organization.insert_bus_grp_details',3);
INSERT INTO per_number_generation_controls
(business_group_id
,type
,next_value)
values
(p_organization_id
,'APL'
,1);
hr_utility.set_location('hr_organization.insert_bus_grp_details',4);
INSERT INTO per_number_generation_controls
(business_group_id
,type
,next_value)
values
(p_organization_id
,'CWK'
,1);
hr_utility.set_location('hr_organization.insert_bus_grp_details',5);
SELECT 'Y'
INTO l_install_mode
FROM sys.dual
WHERE NOT EXISTS (SELECT null
FROM fnd_product_installations
WHERE application_id = 800
AND status IN ('I','S'));
hr_utility.set_location('hr_organization.insert_bus_grp_details',6);
SELECT 'N'
INTO l_apps_account
FROM fnd_product_installations
WHERE application_id = 0
AND product_version = '6.0.27';
hr_utility.set_location('hr_organization.insert_bus_grp_details',8);
INSERT INTO per_security_profiles
(security_profile_id
,business_group_id
,include_top_organization_flag
,include_top_position_flag
,security_profile_name
,view_all_flag
,view_all_organizations_flag
,view_all_payrolls_flag
,view_all_positions_flag
,view_all_applicants_flag
,view_all_employees_flag
,view_all_cwk_flag
,view_all_contacts_flag
,view_all_candidates_flag
,reporting_oracle_username
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,org_security_mode
,restrict_on_individual_asg
,top_organization_method
,top_position_method)
SELECT decode(p_organization_id,0,0,per_security_profiles_s.nextval)
,p_organization_id
,'Y'
,'Y'
,hou.name
,'Y'
,'Y'
,'Y'
,'Y'
,'Y'
,'Y'
,'Y'
,'Y'
,'Y'
,null
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_created_by
,p_creation_date
,'NONE'
,'N'
,'S'
,'S'
FROM hr_all_organization_units hou,
fnd_oracle_userid o
WHERE hou.organization_id = p_organization_id
AND o.oracle_id = 900;
'hr_organization.insert_bus_grp_details');
insert into PER_JOB_GROUPS
(job_group_id
,business_group_id
,legislation_code
,internal_name
,displayed_name
,id_flex_num
,master_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
values (per_job_groups_s.nextval
,p_organization_id
,null
,'HR_'||to_char(p_organization_id)
,'HR_'||to_char(p_organization_id)
,p_org_information6
,'N'
,1
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login);
hr_utility.set_location('hr_organization.insert_bus_grp_details',9);
INSERT INTO pay_consolidation_sets
(consolidation_set_id
,business_group_id
,consolidation_set_name
,comments
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT pay_consolidation_sets_s.nextval
,p_organization_id
,substr(hou.name,1,c_consolidation_set_name_len)
,null
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_created_by
,p_creation_date
FROM hr_all_organization_units hou
WHERE hou.organization_id = p_organization_id;
hr_utility.set_location('hr_organization.insert_bus_grp_details',91);
'hr_organization.insert_bus_grp_details');
hr_utility.set_location('hr_organization.insert_bus_grp_details',92);
hr_utility.set_location('hr_organization.insert_bus_grp_details',93);
hr_utility.set_location('hr_organization.insert_bus_grp_details',10);
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT
pay_user_column_instances_s.nextval
,fnd_sessions.effective_date
,hr_general.end_of_time
,l_usr_row_id
,l_usr_col_id
,p_organization_id
,null
,null
,'Corporate'
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_created_by
,p_creation_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT
pay_user_column_instances_s.nextval
,hr_general.start_of_time
,hr_general.end_of_time
,l_usr_row_id
,l_usr_col_id
,p_organization_id
,null
,null
,'Corporate'
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_created_by
,p_creation_date
FROM sys.dual;
hr_utility.set_location('hr_organization.insert_bus_grp_details',101);
'hr_organization.insert_bus_grp_details');
hr_utility.set_location('hr_organization.insert_bus_grp_details',102);
hr_utility.set_location('hr_organization.insert_bus_grp_details', 8);
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_flex_num => l_disability_id_flex_num,
p_flex_category => 'ADA');
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_flex_num => l_disability_acc_id_flex_num,
p_flex_category => 'ADA');
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_flex_num => l_osha_id_flex_num,
p_flex_category => 'OSHA');
insert into per_us_osha_numbers(
case_year,
business_group_id,
next_value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (x,
p_organization_id,
1,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date
);
end insert_bus_grp_details;
SELECT 'Y'
INTO org_check
FROM sys.dual
WHERE exists
(
SELECT 'Name Already Exists'
FROM hr_organization_units org
WHERE (org.organization_id <> p_organization_id
OR p_organization_id IS NULL)
AND p_organization_name = org.name
AND (org.business_group_id + 0 = p_business_group_id
or p_organization_id + 0 = p_business_group_id)
);
Battery of tests to see if an organization may be deleted.
PARAMETERS
p_organization_id : Organization Id of Organization to be deleted.
p_business_group_id : Business Group id of rganization to be deleted.
*/
--
-- Storage Variable.
--
l_test_func varchar2(60);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_PEOPLE_F x
where x.business_group_id = p_business_group_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from HR_ORGANIZATION_UNITS x
where x.business_group_id = p_business_group_id
and x.organization_id <> p_business_group_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ORG_STRUCTURE_ELEMENTS x
where x.business_group_id = p_business_group_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ORG_STRUCTURE_ELEMENTS x
where x.organization_id_child = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ORG_STRUCTURE_ELEMENTS x
where x.organization_id_parent = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists (select 1
from BEN_BENEFICIARIES_F x
where x.source_id = p_organization_id
and x.source_type = 'O');
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ASSIGNMENTS_F x
where x.SOURCE_ORGANIZATION_ID = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ASSIGNMENTS_F x
where x.ORGANIZATION_ID = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ORG_STRUCTURE_ELEMENTS x
where x.ORGANIZATION_ID_PARENT = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_ORG_STRUCTURE_ELEMENTS x
where x.ORGANIZATION_ID_CHILD = p_organization_id);
select '1'
into l_test_func
from sys.dual
where exists ( select 1
from PER_SECURITY_PROFILES x
where x.ORGANIZATION_ID = p_organization_id);