[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function insert_application
70.4 05-JAN-93 SZWILLIA Correction for applicant
assignments
70.5 11-JAN-93 SZWILLIA Corrected date defaulting
70.6 11-JAN-93 SZWILLIA Changed interface to create
business group to accept
structure names not numbers
70.9 20-JAN-93 SZWILLIA Corrected error handling.
70.12 09-MAR-93 SZWILLIA Made insert_org_information
public and added error checking.
70.13 11-MAR-93 NKHAN Added 'exit' to the end )
*** AUG-93 us_pesutupd.pkb created, ie. copied from pesutupd.pkb ***
40.1 ??-AUG-93 MSWANSON Date us_pesutupd.pkb created, ie.
copied from pesutupd.pkb and
altered for US testing.
40.2 20-AUG-93 RMAMGAIN Modified Create_business_group
Commented some stuff.
Added more insert in Leg. Company.
40.3 08-APR-94 AKELLY Added insert of 'Federal Tax Rules'
'State Tax Rules' and 'Work Schedule'
ddf info. for legal company.
****
40.0 31-MAY-94 MGILMORE Renamed.
40.1 03-JUL-94 AROUSSEL Tidyied up for 10G install
40.2 05-OCT-94 RFINE Changed calls from us_ins_org_info to
pay_us_ins_org_info_pkg
40.3 24-NOV-94 RFINE Suppressed index on business_group_id
40.4 01-MAR-95 MSWANSON Add/Change for EEO and VETS100 system
test data creation:
- create_est_organization,
- create_eeo_hierarchy,
- create_eeo_hierarchy_version,
- create_eeo_hierarchy_element.
40.5 25-JUL-95 AMILLS Changed tokenised message 'PAY_6361_USER_
TABLE_UNIQUE' for the following (Hard coded):
'PAY_7687_USER_GRADE_TAB_UNIQUE',
'PAY_7688_USER_POS_TAB_UNIQUE',
'PAY_7686_USER_JOB_TAB_UNIQUE'
40.6 26-sep-95 AKELLY corrected create_est_organizatioN
40.7 28-SEP-95 AKELLY Added functions INSERT_WC_FUND, INSERT_WC_RATE,
CREATE_WC_CARRIER and CREATE_US_LOCATION.
40.9 01-NOV-95 JTHURING Removed error checking from end of script
110.2 11-SEP-97 khabibul added suffix _vl to fnd_id_flex_structures as
id_flex_structure_name col is obsolete.
110.3 19-JAN-99 nbristow insert into hr_all_organization_units rather
than hr_organization_units.
115.2 24-MAR-99 sdoshi Flexible Dates Conversion.
115.3 24-MAR-99 alogue MLS changes.
115.4 14-May-1999 mmillmor multi radix change to working_hours on position
================================================================= */
--
--
FUNCTION insert_organization_unit
( P_GROUP VARCHAR2
,P_NAME VARCHAR2
,P_BUSINESS_GROUP_ID NUMBER
,P_COST_ALLOCATION_KEYFLEX_ID NUMBER
,P_LOCATION_ID NUMBER
,P_SOFT_CODING_KEYFLEX_ID NUMBER
,P_DATE_FROM DATE
,P_DATE_TO DATE
,P_INTERNAL_EXTERNAL_FLAG VARCHAR2
,P_INTERNAL_ADDRESS_LINE VARCHAR2
,P_TYPE VARCHAR2
) return NUMBER
IS
--
l_organization_id NUMBER;
SELECT language_code
from fnd_languages
where installed_flag in ('I','B');
hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',1);
SELECT hr_organization_units_s.nextval
INTO l_organization_id
FROM sys.dual ;
hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',2);
INSERT INTO HR_ALL_ORGANIZATION_UNITS
(organization_id
,business_group_id
,cost_allocation_keyflex_id
,location_id
,soft_coding_keyflex_id
,date_from
,name
,date_to
,internal_external_flag
,internal_address_line
,type
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT l_organization_id
,DECODE(p_group,'Y',l_organization_id,p_business_group_id)
,p_cost_allocation_keyflex_id
,p_location_id
,p_soft_coding_keyflex_id
,nvl(p_date_from,trunc(SYSDATE))
,p_name
,p_date_to
,p_internal_external_flag
,p_internal_address_line
,p_type
,SYSDATE
,0
,0
,0
,SYSDATE
FROM sys.dual ;
hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',3);
INSERT INTO HR_ALL_ORGANIZATION_UNITS_TL
(organization_id
,language
,source_lang
,name
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
SELECT l_organization_id
,language
,userenv('LANG')
,p_name
,SYSDATE
,0
,0
,0
,SYSDATE
FROM sys.dual ;
hr_utility.set_location('pay_us_db_per_setup.insert_organization_unit',4);
end insert_organization_unit;
SELECT id_flex_num
INTO l_position_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_code = 'POS'
AND id_flex_structure_name = p_position_structure;
SELECT id_flex_num
INTO l_grade_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_code = 'GRD'
AND id_flex_structure_name = p_grade_structure;
SELECT id_flex_num
INTO l_job_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_code = 'JOB'
AND id_flex_structure_name = p_job_structure;
SELECT id_flex_num
INTO l_people_group_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_code = 'GRP'
AND id_flex_structure_name = p_people_group_structure;
SELECT id_flex_num
INTO l_cost_allocation_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_code = 'COST'
AND id_flex_structure_name = p_cost_allocation_structure;
l_business_group_id := insert_organization_unit('Y'
,p_name
,null
,null
,null
,null
,p_date_from
,p_date_to
,'INT'
,p_internal_address_line
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'CLASS'
,'HR_BG'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'CLASS'
,'HR_ORG'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Business Group Information'
,p_short_name
,p_method_of_generation_emp_num
,p_method_of_generation_apl_num
,l_grade_num
,l_people_group_num
,l_job_num
,l_cost_allocation_num
,l_position_num
,p_legislation_code
,p_currency_code
,p_fiscal_year_start
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Work Day Information'
,p_default_start_time
,p_default_end_time
,p_working_hours
,p_frequency
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Reporting Categories'
,p_employment_category_1
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Reporting Categories'
,p_employment_category_2
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Reporting Statuses'
,p_assignment_status_1
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_business_group_id
,'Reporting Statuses'
,p_assignment_status_2
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = p_business_group;
l_organization_id := insert_organization_unit('N'
,p_name
,l_business_group_id
,null
,p_location_id
,null
,p_date_from
,p_date_to
,p_internal_external_flag
,p_internal_address_line
,p_type);
hr_utility.trace('Called insert_information_unit from CPO');
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(l_organization_id
,'CLASS'
,'HR_ORG'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
hr_utility.trace('Called insert_org_information once from CPO');
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(l_organization_id
,'Work Day Information'
,p_default_start_time
,p_default_end_time
,p_working_hours
,p_frequency
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
hr_utility.trace('Called insert_org_information twice from CPO');
SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = p_business_group;
l_organization_id := insert_organization_unit('N'
,p_name
,l_business_group_id
,null
,p_location_id
,null
,p_date_from
,p_date_to
,p_internal_external_flag
,p_internal_address_line
,p_type);
hr_utility.trace('Called insert_information_unit from CEO');
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_organization_id
,'CLASS'
,'HR_ESTAB'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
(l_organization_id
,'CLASS'
,'HR_ORG'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
hr_utility.trace('Called insert_org_information once from CEO');
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
(l_organization_id
,'Establishment EEO-1 Filing'
,'Y'
,p_eeo1_unit_number
,p_reporting_name
,p_sic
,p_activity_line1
,p_activity_line2
,p_activity_line3
,p_activity_line4
,null
,p_apprentices_employed
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
(l_organization_id
,'Establishment VETS-100 Filing'
,p_vets100_unit_number
,p_vets100_reporting_name
,null
,null
,null
,null
,null
,null
,p_apprentices_employed
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
SELECT business_group_id
INTO l_business_group_id
FROM hr_all_organization_units
WHERE name = p_business_group
and business_group_id + 0 = organization_id;
l_legal_company_id := insert_organization_unit('N'
,p_name
,l_business_group_id
,null
,p_location_id
,null
,p_date_from
,p_date_to
,'INT'
,p_internal_address_line
,p_org_type);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'CLASS'
,'HR_ORG'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'CLASS'
,'HR_LEGAL'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'CLASS'
,'HR_ESTAB'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Establishment EEO-1 Filing' -- context
,null -- used to be Establishment flag
,P_EEO1_UNIT_NUMBER
,P_REPORTING_NAME
,P_SIC
,P_ACTIVITY_LINE1
,P_ACTIVITY_LINE2
,P_ACTIVITY_LINE3
,P_ACTIVITY_LINE4
,P_APPRENTICES_EMPLOYED
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Establishment VETS-100 Filing' -- context
,P_VETS100_UNIT_NUMBER
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'NACHA Rules'
,P_NACHA_COMPANY_NAME
,'220'
,'PPD'
,P_NACHA_IDENTIFIER
,P_NACHA_DISCRETIONARY_CODE
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Multiple Worksite Reporting' -- context
,'Y' -- transmitter_flag
,'Contact Person1' -- position
,'415-506-7000' -- telephone
,'2807' -- extension
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'W2 Reporting Rules' -- context
,'Y' -- transmitter_flag
,'SEQUENT' -- computer
,'SL' -- internal_labelling
,'16' -- tape_density
,'ASC' -- tape_rec_code
,'512' -- blocking_factor
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Employer Identification' -- context
,P_COMPANY_FEDERAL_IDENTIFIER
,P_COMPANY_FEDERAL_IDENTIFIER
,P_NAME
,'POSITION'
,'415-506-7000'
,'512'
,fnd_number.canonical_to_number(l_legal_company_id)
,'Address'
,'Regular'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Work Schedule' -- context
,P_WORK_SCHEDULE_TABLE
,P_WORK_SCHEDULE_TABLE
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'Federal Tax Rules' -- context
,P_SS_SELF_ADJUST_METHOD
,P_MED_SELF_ADJUST_METHOD
,P_FUTA_SELF_ADJUST_METHOD
,P_TYPE_OF_EMPLOYMENT
,P_TAX_GROUP
,P_SUPPLEMENTAL_CALC_METHOD
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'EEO-1 Filing' -- context
,P_EEO1_IDENTIFICATION_NUMBER
,P_DUN_AND_BRADSTREET_NUMBER
,P_GRE_REPORTING_NAME
,P_AFFILIATED
,P_GOVERNMENT_CONTRACTOR
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
l_legal_company_id
,'VETS-100 Filing' -- context
,P_VETS100_COMPANY_NUMBER
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);
SELECT per_org_structure_versions_s.nextval
INTO l_organization_structure_id
FROM sys.dual;
INSERT INTO per_organization_structures
(organization_structure_id
,business_group_id
,name
,primary_structure_flag
)
VALUES
(l_organization_structure_id
,p_business_group_id
,p_hierarchy_name
,p_primary_structure_flag
);
SELECT per_org_structure_versions_s.nextval
INTO l_org_structure_version_id
FROM sys.dual;
INSERT INTO per_org_structure_versions
(org_structure_version_id
,business_group_id
,organization_structure_id
,date_from
,version_number
)
VALUES
(l_org_structure_version_id
,p_business_group_id
,p_organization_structure_id
,fnd_date.canonical_to_date(p_date_from)
,p_version_number
);
INSERT INTO per_org_structure_elements
(org_structure_element_id
,business_group_id
,organization_id_parent
,org_structure_version_id
,organization_id_child
)
VALUES
(per_org_structure_elements_s.nextval
,p_business_group_id
,p_organization_id_parent
,p_org_structure_version_id
,p_organization_id_child
);
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information(
p_legal_company_id
,'State Tax Rules' -- context
,p_state_code
,p_sui_company_state_id
,p_sit_company_state_id
,p_sui_self_adjust_method
,p_sdi_self_adjust_method
,p_sui_er_experience_rate_1
,p_sui_er_experience_rate_2
,p_wc_carrier_name
,p_employers_liability_rate
,p_experience_modification_rate
,p_premium_discount_rate
,null
,null
,null
,null
,null
,null
,null
,null
,null);
SELECT 'Y'
INTO job_check
FROM per_jobs jb
WHERE (jb.job_id <> p_job_id
OR p_job_id IS NULL)
AND p_job_name = jb.name
AND p_business_group_id = jb.business_group_id + 0;
FUNCTION insert_job_definition return NUMBER IS
--
l_job_definition_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_job_definition',1);
SELECT per_job_definitions_s.nextval
INTO l_job_definition_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_job_definition',2);
INSERT INTO per_job_definitions
(JOB_DEFINITION_ID
,ID_FLEX_NUM
,SUMMARY_FLAG
,ENABLED_FLAG
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,SEGMENT21
,SEGMENT22
,SEGMENT23
,SEGMENT24
,SEGMENT25
,SEGMENT26
,SEGMENT27
,SEGMENT28
,SEGMENT29
,SEGMENT30)
values
(l_job_definition_id
,l_structure_num
,'Y'
,'N'
,fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_date_from))
,fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_date_to))
,l_segment1 -- p_name for default jobs
,p_segment2
,p_segment3
,p_segment4
,p_segment5
,p_segment6
,p_segment7
,p_segment8
,p_segment9
,p_segment10
,p_segment11
,p_segment12
,p_segment13
,p_segment14
,p_segment15
,p_segment16
,p_segment17
,p_segment18
,p_segment19
,p_segment20
,p_segment21
,p_segment22
,p_segment23
,p_segment24
,p_segment25
,p_segment26
,p_segment27
,p_segment28
,p_segment29
,p_segment30
);
end insert_job_definition;
FUNCTION insert_job RETURN NUMBER IS
--
l_job_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_job',1);
SELECT per_jobs_s.nextval
INTO l_job_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_job',2);
hr_utility.set_location('pay_us_db_per_setup.insert_job',3);
INSERT INTO per_jobs
(JOB_ID
,BUSINESS_GROUP_ID
,JOB_DEFINITION_ID
,DATE_FROM
,DATE_TO
,NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,JOB_INFORMATION_CATEGORY
,JOB_INFORMATION1
)
values
(l_job_id
,l_business_group_id
,l_job_definition_id
,p_date_from
,p_date_to
,p_name
,SYSDATE
,0
,0
,0
,SYSDATE
,p_context
,p_eeo_category
);
end insert_job;
SELECT business_group_id
, job_structure
INTO l_business_group_id
, l_structure_num
FROM per_business_groups
WHERE name = p_business_group
AND business_group_id + 0 <> 0;
l_job_definition_id := insert_job_definition;
l_job_id := insert_job;
SELECT 'Y'
INTO pos_check
FROM per_positions pos
WHERE (pos.position_id <> p_position_id
OR p_position_id IS NULL)
AND p_position_name = pos.name
AND p_business_group_id = pos.business_group_id + 0;
FUNCTION insert_pos_definition return NUMBER IS
--
l_pos_definition_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_pos_definition',1);
SELECT per_position_definitions_s.nextval
INTO l_pos_definition_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_pos_definition',2);
INSERT INTO per_position_definitions
(POSITION_DEFINITION_ID
,ID_FLEX_NUM
,SUMMARY_FLAG
,ENABLED_FLAG
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,SEGMENT21
,SEGMENT22
,SEGMENT23
,SEGMENT24
,SEGMENT25
,SEGMENT26
,SEGMENT27
,SEGMENT28
,SEGMENT29
,SEGMENT30)
values
(l_pos_definition_id
,l_structure_num
,'Y'
,'N'
,p_date_effective
,p_date_end
,l_segment1 -- p_name for a default position
,p_segment2
,p_segment3
,p_segment4
,p_segment5
,p_segment6
,p_segment7
,p_segment8
,p_segment9
,p_segment10
,p_segment11
,p_segment12
,p_segment13
,p_segment14
,p_segment15
,p_segment16
,p_segment17
,p_segment18
,p_segment19
,p_segment20
,p_segment21
,p_segment22
,p_segment23
,p_segment24
,p_segment25
,p_segment26
,p_segment27
,p_segment28
,p_segment29
,p_segment30
);
end insert_pos_definition;
FUNCTION insert_position RETURN NUMBER IS
--
l_position_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_position',1);
SELECT per_positions_s.nextval
INTO l_position_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_position',2);
hr_utility.set_location('pay_us_db_per_setup.insert_position',3);
INSERT INTO per_positions
(POSITION_ID
,BUSINESS_GROUP_ID
,JOB_ID
,ORGANIZATION_ID
,LOCATION_ID
,POSITION_DEFINITION_ID
,DATE_EFFECTIVE
,DATE_END
,FREQUENCY
,NAME
,PROBATION_PERIOD
,PROBATION_PERIOD_UNITS
,RELIEF_POSITION_ID
,REPLACEMENT_REQUIRED_FLAG
,SUCCESSOR_POSITION_ID
,TIME_NORMAL_FINISH
,TIME_NORMAL_START
,WORKING_HOURS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,COMMENTS)
values
(l_position_id
,l_business_group_id
,l_job_id
,l_organization_id
,l_location_id
,l_position_definition_id
,p_date_effective
,p_date_end
,p_frequency
,p_name
,p_probation_period
,p_probation_units
,l_relief_position_id
,p_replacement_required
,l_successor_position_id
,p_time_normal_finish
,p_time_normal_start
,p_working_hours
,SYSDATE
,0
,0
,0
,SYSDATE
,p_comments);
end insert_position;
SELECT business_group_id
, position_structure
INTO l_business_group_id
, l_structure_num
FROM per_business_groups
WHERE name = p_business_group
AND business_group_id + 0 <> 0;
SELECT job_id
INTO l_job_id
FROM per_jobs
WHERE name = p_job
AND business_group_id + 0 = l_business_group_id;
SELECT organization_id
INTO l_organization_id
FROM per_organization_units
WHERE name = p_organization
AND business_group_id + 0 = l_business_group_id;
SELECT location_id
INTO l_location_id
FROM hr_locations
WHERE location_code = p_location;
SELECT position_id
INTO l_relief_position_id
FROM per_positions
WHERE name = p_relief_position;
SELECT position_id
INTO l_successor_position_id
FROM per_positions
WHERE name = p_successor_position;
l_position_definition_id := insert_pos_definition;
l_position_id := insert_position;
SELECT 'Y'
INTO grd_check
FROM per_grades grd
WHERE (grd.grade_id <> p_grade_id
OR p_grade_id IS NULL)
AND p_grade_name = grd.name
AND p_business_group_id = grd.business_group_id + 0;
FUNCTION insert_grade_definition return NUMBER IS
--
l_grade_definition_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_grade_definition',1);
SELECT per_grade_definitions_s.nextval
INTO l_grade_definition_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_grade_definition',2);
INSERT INTO per_grade_definitions
(GRADE_DEFINITION_ID
,ID_FLEX_NUM
,SUMMARY_FLAG
,ENABLED_FLAG
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,SEGMENT21
,SEGMENT22
,SEGMENT23
,SEGMENT24
,SEGMENT25
,SEGMENT26
,SEGMENT27
,SEGMENT28
,SEGMENT29
,SEGMENT30)
values
(l_grade_definition_id
,l_structure_num
,'Y'
,'N'
,p_date_from
,p_date_to
,l_segment1 -- p_name for default grades
,p_segment2
,p_segment3
,p_segment4
,p_segment5
,p_segment6
,p_segment7
,p_segment8
,p_segment9
,p_segment10
,p_segment11
,p_segment12
,p_segment13
,p_segment14
,p_segment15
,p_segment16
,p_segment17
,p_segment18
,p_segment19
,p_segment20
,p_segment21
,p_segment22
,p_segment23
,p_segment24
,p_segment25
,p_segment26
,p_segment27
,p_segment28
,p_segment29
,p_segment30
);
end insert_grade_definition;
FUNCTION insert_grade RETURN NUMBER IS
--
l_grade_id NUMBER;
hr_utility.set_location('pay_us_db_per_setup.insert_grade',1);
SELECT per_grades_s.nextval
INTO l_grade_id
FROM sys.dual;
hr_utility.set_location('pay_us_db_per_setup.insert_grade',2);
hr_utility.set_location('pay_us_db_per_setup.insert_grade',3);
INSERT INTO per_grades
(GRADE_ID
,BUSINESS_GROUP_ID
,GRADE_DEFINITION_ID
,DATE_FROM
,DATE_TO
,NAME
,SEQUENCE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
SELECT
l_grade_id
,l_business_group_id
,l_grade_definition_id
,p_date_from
,p_date_to
,p_name
,nvl(p_sequence,nvl(max(pg.sequence),0) + 1)
,SYSDATE
,0
,0
,0
,SYSDATE
FROM per_grades pg
WHERE pg.business_group_id + 0 = l_business_group_id ;
end insert_grade;
SELECT business_group_id
, grade_structure
INTO l_business_group_id
, l_structure_num
FROM per_business_groups
WHERE name = p_business_group
AND business_group_id + 0 <> 0;
l_grade_definition_id := insert_grade_definition;
l_grade_id := insert_grade;
FUNCTION INSERT_WC_FUND( P_BUSINESS_GROUP_ID NUMBER,
P_CARRIER_ID NUMBER,
P_LOCATION_ID NUMBER DEFAULT NULL,
P_STATE_CODE VARCHAR2) return NUMBER IS
--
l_fund_id number(15);
hr_utility.trace('Entered insert_wc_fund');
hr_utility.set_location('scltstdat.insert_wc_fund',1);
SELECT PAY_WC_FUNDS_S.NEXTVAL
INTO l_fund_id
FROM DUAL;
hr_utility.set_location('scltstdat.insert_wc_fund',5);
hr_utility.set_location('scltstdat.insert_wc_fund',10);
INSERT INTO PAY_WC_FUNDS
(FUND_ID, BUSINESS_GROUP_ID, CARRIER_ID, LOCATION_ID, STATE_CODE)
VALUES
(L_FUND_ID, P_BUSINESS_GROUP_ID, P_CARRIER_ID, P_LOCATION_ID,
P_STATE_CODE);
hr_utility.set_location('scltstdat.insert_wc_fund',15);
END INSERT_WC_FUND;
FUNCTION insert_wc_rate
(p_fund_id NUMBER
,p_business_group_id NUMBER
,p_rate NUMBER
) return NUMBER
IS
--
l_wc_code NUMBER;
hr_utility.trace('Entered insert_wc_rate');
hr_utility.set_location('scltstdat.insert_wc_rate',1);
SELECT PAY_WC_RATES_S.NEXTVAL
INTO l_wc_code
FROM DUAL;
hr_utility.set_location('scltstdat.insert_wc_rate',5);
hr_utility.set_location('scltstdat.insert_wc_rate',10);
INSERT INTO
pay_wc_rates
(wc_code
,fund_id
,business_group_id
,rate
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
VALUES
(l_wc_code
,p_fund_id
,p_business_group_id
,p_rate
,0
,sysdate
,0
,sysdate
,0 );
hr_utility.set_location('scltstdat.insert_wc_rate',15);
end insert_wc_rate;
select hr_locations_s.nextval
into l_location_id
from sys.dual;
INSERT INTO hr_locations
(location_id
,entered_by
,location_code
,address_line_1
,address_line_2
,address_line_3
,town_or_city
,region_1
,region_2
,postal_code
,telephone_number_1
,telephone_number_2
,country
,style
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
values
(l_location_id
,0
,p_location_code
,p_address_line_1
,p_address_line_2
,p_address_line_3
,p_town_or_city
,p_county
,p_state
,p_zip_code
,p_telephone
,p_fax
,'US'
,'US'
,0
,sysdate
,0
,sysdate
,0 );
SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = p_business_group;
l_wc_carrier_id := pay_us_db_per_setup.insert_organization_unit
('N'
,p_name
,l_business_group_id
,null
,p_location_id
,null
,p_date_from
,p_date_to
,p_internal_external_flag
,p_internal_address_line
,p_type);
hr_utility.trace('Called insert_information_unit from create_wc_carrier');
l_org_information_id := pay_us_ins_org_info_pkg.insert_org_information
(l_wc_carrier_id
,'CLASS'
,'US_WC_CARRIER'
,'Y'
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null);