[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
New procedure insert_user_tables to seed
user tables.
alogue 16-MAR-1999 - R11.5 Entity Horizon changes:
Outer join for per_phones in SEH_PER_PERSON_DETAILS
New ACCRUAL formula type.
Route ACCRUAL_PLAN_VALUES and its dbitems.
Route CARRIED_OVER_VALUES and its dbitems.
Route ACCRUAL_PLAN_SERVICE_DATES and its dbitems.
Route ACCRUAL_PLAN_DATES and its dbitems.
Added TEMPLATE_BIS_DAYS_TO_HOURS, TEMPLATE_FTE,
TEMPLATE_HEAD, EXAMPLE_BIS_OT_BAND1 to
ff_formulas_f.
Created insert_formula to insert PTO_PAYROLL_CARRYOVER,
PTO_PAYROLL_PERIOD_ACCRUAL, PTO_PAYROLL_CALCULATION,
PTO_ROLLING_CARRYOVER, PTO_ROLLING_PERIOD_ACCRUAL,
PTO_ROLLING_ACCRUAL, PTO_SIMPLE_CARRYOVER,
PTO_SIMPLE_PERIOD_ACCRUAL, PTO_SIMPLE_MULTIPLIER
into ff_formulas_f.
Created insert_functions to insert GET_HOURS_WORKED,
CURRENCY_CONVERT_AMOUNT, GET_RATE_TYPE,
CHECK_RATE_TYPE and accrual functions
into ff_functions.
alogue 11-JAN-1999 - R11.5 Entity Horizon changes:
OAB and other new ff_contexts, route
ASSIGNMENT_BUDGET_VALUES and dbitems,
ASSIGNMENT_FULL_TIME_CONDITIONS route
and dbitems.
R11.5 change whereby date contexts are passed
into routes as dates (and thus don't require
a to_date() on them).
Change to SEH_SADMIN_LAST_PERFORM_REV route.
New routes SEH_CON_PERSON_DETAILS_2,
SEH_ASS_PERSON_DETAILS_3, SEH_PER_PERSON_DETAILS_2
and dbitems.
alogue 13-JUL-1998 - Legislative Check formula type.
alogue 20-MAY-1998 - Performance fixes for SEH_ASS_PERSON_DETAILS and
SEH_PER_PERSON_DETAILS routes.
alogue 13-NOV-1997 - Rename PER_CONTACTS table to
PER_CONTACT_RELATIONSHIPS.
alogue 11-NOV-1997 - user entity PAY_PAYROLLS_ENTITY fix.
alogue 07-NOV-1997 - New database item EMP_PROJ_TERM_DATE.
rfine 13-OCT-97 110.5 563034 Changed parent table name from
PER_PEOPLE_F to PER_ALL_PEOPLE_F
alogue 07-OCT-1997 - fix of SEH_ASS_PERSON_DETAILS route.
alogue 12-AUG-1997 - per_phones put into various routes
alogue 07-AUG-1997 - ff_context TAX_GROUP.
alogue 05-AUG-1997 - Rename use of fnd_territories to
fnd_territories_vl.
alogue 29-MAY-1997 - Fix to PAY_PAYROLLS_ROUTE.
alogue 19-MAY-1997 - Renamed database item ARREARS_FLAG to
PAYROLL_ARREARS_FLAG.
alogue 07-APR-1997 - New routes: TARGET_PAYROLL_ACTION_ROUTE
(for action_type item) and PAY_PAYROLLS_ROUTE
(for arrears_flag item) for advance pay.
Also tidy up reflecting bug 374466
SEH_ASS_PERSON_DETAILS route fix.
mwcallag 01-SEP-1995 - sysdate and Session date routes and DB items
added.
mwcallag 24-APR-1995 - Cheque number DB items added (UK, US spelling).
mwcallag 31-MAR-1995 - Entity for PAY_PROC_PERIOD_NUMBER DB items now
has not_found_allowed = 'Y'.
mwcallag 06-JAN-1995 - Performance changes resulting from the DEC
Benchmark. These include:
--
A new route : SEH_ASS_PERSON_DETAILS_2 - this
holds DB items that were previously being slowed
down in route SEH_ASS_PERSON_DETAILS.
--
Index for pay_basis_id disabled in the route
SEH_SADMIN_SALARY_ELEMENT.
--
The following routes used to use the synonym
fnd_lookups. Originally this was a simple
table, but now is a complex view, and hence
these routes now refer to the view hr_lookups,
and also use the application id column (= 800)
for improved performance:
SEH_PER_PERSON_DETAILS
SEH_ASS_PERSON_DETAILS
SEH_CON_PERSON_DETAILS
SEH_REC_DETAILS
SEH_SUP_DETAILS
--
The following routes use the view hr_lookups.
They have been modified to also use the
application id column (= 800) for improved
performance:
SEH_EMP_PERSON_SERVICE_2
SEH_SADMIN_SALARY_BASIS
SEH_SADMIN_SALARY_PROPOSALS
SEH_ASSIGN_HR_ADDRESS_US
SEH_ASSIGN_ADDRESS_US
SEH_ASSIGN_HR_ADDRESS_UK
SEH_ASSIGN_ADDRESS_UK
--
mwcallag 24-OCT-1994 - New context of ORIGINAL_ENTRY_ID added to
payroll and element skip formual types
mwcallag 15-OCT-1994 - Various changes ready for US benchmark:
Date restriction added to Person and contact
address routes (bug no. 240009).
SEH_PAY_DETAILS route now uses the context of
payroll action rather than payroll id to improve
performance.
New DB item added to route SEH_PAY_DETAILS:
PAY_PROC_PERIOD_NUMBER
PAY_PROC_PERIOD_ID
PAY_PROC_PERIOD_NAME
--
'PAY_PROC_PERIOD_NUMBER' replaces the old
'ASG_PROC_PERIOD_NUMBER' DB item.
ASG_LAST_PROC_PERIOD_NUMBER route changed to use
the context of assignment id (the previous
change to use payroll action was wrong), also
uses the DB change of time_period_id on the
payroll actions table to improve performance.
New DB item added to this route:
ASG_LAST_PROC_PERIOD_NAME
ASG_LAST_PROC_PERIOD_ID
ASG_LAST_PROC_PAYROLL_NAME
--
mwcallag 28-JUL-1994 - Payroll period DB items added.
mwcallag 16-JUN-1994 - Route SEH_EMP_PERSON_SERVICE_2 altered, nvl
added.
This ensures a row is always returned.
mwcallag 29-APR-1994 - Route SEH_ASS_LAST_PER_NUM tuned, now uses the
context of payroll action id rather than
assignment id. (ASG_LAST_PROC_PERIOD_NUMBER).
Route SEH_EMP_PERSON_SERVICE split into 2 for
performance purposes.
mwcallag 25-MAR-1994 - Not found flag for all ASG_%_LAST% DB item
set to yes.
mwcallag 18-MAR-1994 - Not found flag for ASG_LAST_PROC_PERIOD_NUMBER
set to yes - there wont be an entry for the
first run.
mwcallag 28-FEB-1994 - Database name changed from 'ASS_%' to 'ASG_%'.
mwcallag 11-JAN-1994 - New context of Element type id added to
ff_contexts.
mwcallag 06-JAN-1994 - Contact routes changed to use contact_person_id.
mwcallag 15-DEC-1993 - HR location (general, GB and US) DB items added.
G417 ASS_SALARY changed to Number data type.
ASS_SALARY_BASIS_CODE DB item added.
mwcallag 09-DEC-1993 - G337 Payroll DB items added: PAY_PERIODS_PER_YEAR
and PAY_PROC_PERIOD_DATE_PAID.
mwcallag 02-DEC-1993 - Salary Admin Db items added.
mwcallag 02-NOV-1993 - 'User Table Validation' formula type added.
mwcallag 22-OCT-1993 - Further formula types and contexts added.
mwcallag 08-OCT-1993 - Payroll DB items : null allowed set to 'N'.
mwcallag 07-SEP-1993 - ********************************
* DIVERGENCE FROM FROZEN CODE *
********************************
More Static DB items added, together with
new routes for payroll processing.
mwcallag 31-AUG-1993 - Person type definition texts changed from
'system_person_type' to 'user_person_type'.
Payroll formula type changed to include fewer
context usages.
mwcallag 11-AUG-1993 - New contexts for payment formula type.
mwcallag 22-JUL-1993 - Routine split into 2 procedures for easier
re-building.
mwcallag 20-JUL-1993 - Some DB items changed from number to text type.
mwcallag 21-JUN-1993 - 'Element Input Validation' formula type added.
mwcallag 15-JUN-1993 - minor changes to routes to ensure a row is
always returned (outer joins added, etc).
mwcallag 04-JUN-1993 - general mods and application details route added
mwcallag 28-MAY-1993 - minor change to person assignment details route.
mwcallag 26-MAY-1993 - creator type changed following database change.
mwcallag 17-MAY-1993 - created.
*/
procedure insert_context is
--
-- +==================================================================+
-- | Insert FF contexts |
-- +==================================================================+
--
begin
declare
procedure do_insert (l_context_name varchar2, l_data_type varchar2) is
x number;
hr_utility.set_location('hrstrdbi.ff_context_do_insert', 1);
insert into ff_contexts
(context_id,
context_level,
context_name,
data_type)
values
(ff_contexts_s.nextval,
x,
l_context_name,
l_data_type);
do_insert ('BUSINESS_GROUP_ID', 'N');
do_insert ('PAYROLL_ID', 'N');
do_insert ('PAYROLL_ACTION_ID', 'N');
do_insert ('ASSIGNMENT_ID', 'N');
do_insert ('ASSIGNMENT_ACTION_ID', 'N');
do_insert ('DATE_EARNED', 'D');
do_insert ('ORG_PAY_METHOD_ID', 'N');
do_insert ('PER_PAY_METHOD_ID', 'N');
do_insert ('ORGANIZATION_ID', 'N');
do_insert ('TAX_UNIT_ID', 'N');
do_insert ('JURISDICTION_CODE', 'T');
do_insert ('BALANCE_DATE', 'D');
do_insert ('ELEMENT_ENTRY_ID', 'N');
do_insert ('ELEMENT_TYPE_ID', 'N');
do_insert ('ORIGINAL_ENTRY_ID', 'N');
do_insert ('TAX_GROUP', 'T');
do_insert ('PGM_ID', 'N');
do_insert ('PL_ID', 'N');
do_insert ('PL_TYP_ID', 'N');
do_insert ('OPT_ID', 'N');
do_insert ('LER_ID', 'N');
do_insert ('COMM_TYP_ID', 'N');
do_insert ('ACT_TYP_ID', 'N');
do_insert ('ACCRUAL_PLAN_ID', 'N');
do_insert ('PERSON_ID', 'N');
do_insert ('SOURCE_ID', 'N');
do_insert ('SOURCE_TEXT', 'T');
procedure do_insert
(
p_formula_type_name in varchar2
) is
begin
hr_utility.set_location('hrstrdbi.ff_type_do_insert', 1);
insert into ff_formula_types
(formula_type_id,
formula_type_name,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (ff_formula_types_s.nextval,
p_formula_type_name,
sysdate,
0,
0,
0,
sysdate);
end do_insert;
do_insert ('Oracle Payroll');
hr_utility.set_location('hrstrdbi.insert_context', 1);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'PAYROLL_ACTION_ID',
'ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED',
'TAX_UNIT_ID',
'JURISDICTION_CODE',
'BALANCE_DATE',
'ELEMENT_ENTRY_ID',
'ELEMENT_TYPE_ID',
'ORIGINAL_ENTRY_ID',
'SOURCE_ID',
'TAX_GROUP',
'SOURCE_TEXT');
do_insert ('Payment');
hr_utility.set_location('hrstrdbi.insert_context', 2);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'PAYROLL_ACTION_ID',
'ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED',
'ORG_PAY_METHOD_ID',
'PER_PAY_METHOD_ID',
'ORGANIZATION_ID',
'TAX_UNIT_ID',
'JURISDICTION_CODE');
do_insert ('Assignment Set');
hr_utility.set_location('hrstrdbi.insert_context', 3);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('DATE_EARNED', 'ASSIGNMENT_ID');
do_insert ('QuickPaint');
hr_utility.set_location('hrstrdbi.insert_context', 4);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('DATE_EARNED', 'ASSIGNMENT_ID');
do_insert ('Element Input Validation');
hr_utility.set_location('hrstrdbi.insert_context', 5);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('DATE_EARNED',
'ASSIGNMENT_ID', 'BUSINESS_GROUP_ID');
do_insert ('Element Skip');
hr_utility.set_location('hrstrdbi.insert_context', 6);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'PAYROLL_ACTION_ID',
'ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED',
'TAX_UNIT_ID',
'JURISDICTION_CODE',
'ELEMENT_ENTRY_ID',
'ELEMENT_TYPE_ID',
'ORIGINAL_ENTRY_ID',
'SOURCE_ID',
'TAX_GROUP');
do_insert ('Legislative Check');
hr_utility.set_location('hrstrdbi.insert_context', 7);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED');
do_insert ('Accrual');
hr_utility.set_location('hrstrdbi.insert_context', 8);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED',
'ACCRUAL_PLAN_ID',
'PAYROLL_ID',
'BUSINESS_GROUP_ID',
'PERSON_ID');
do_insert ('Accrual Subformula');
hr_utility.set_location('hrstrdbi.insert_context', 9);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'DATE_EARNED',
'ACCRUAL_PLAN_ID',
'PAYROLL_ID',
'BUSINESS_GROUP_ID');
do_insert ('Accrual Carryover');
hr_utility.set_location('hrstrdbi.insert_context', 10);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'DATE_EARNED',
'ACCRUAL_PLAN_ID',
'PAYROLL_ID',
'BUSINESS_GROUP_ID');
do_insert ('Accrual Ineligibility');
hr_utility.set_location('hrstrdbi.insert_context', 11);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'DATE_EARNED',
'ACCRUAL_PLAN_ID',
'PAYROLL_ID',
'BUSINESS_GROUP_ID');
do_insert ('Net to Gross');
hr_utility.set_location('hrstrdbi.insert_context', 12);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'PAYROLL_ACTION_ID',
'ASSIGNMENT_ID',
'DATE_EARNED',
'ELEMENT_ENTRY_ID',
'ASSIGNMENT_ACTION_ID',
'ELEMENT_TYPE_ID');
do_insert('CAGR');
hr_utility.set_location('hrstrdbi.insert_context', 13);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'ASSIGNMENT_ID',
'DATE_EARNED',
'ORGANIZATION_ID',
'TAX_UNIT_ID',
'PERSON_ID');
do_insert('Promotion');
hr_utility.set_location('hrstrdbi.insert_context', 14);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('ASSIGNMENT_ID',
'DATE_EARNED');
do_insert('Payroll Run Proration');
hr_utility.set_location('hrstrdbi.insert_context', 15);
insert into ff_ftype_context_usages (formula_type_id, context_id)
select ff_formula_types_s.currval, CON.context_id
from ff_contexts CON
where context_name in ('BUSINESS_GROUP_ID',
'PAYROLL_ID',
'PAYROLL_ACTION_ID',
'ASSIGNMENT_ID',
'ASSIGNMENT_ACTION_ID',
'DATE_EARNED',
'TAX_UNIT_ID',
'JURISDICTION_CODE',
'BALANCE_DATE',
'ELEMENT_ENTRY_ID',
'ELEMENT_TYPE_ID',
'ORIGINAL_ENTRY_ID',
'TAX_GROUP',
'SOURCE_ID',
'SOURCE_TEXT');
do_insert ('User Table Validation');
end insert_context;
PROCEDURE insert_routes_db_items is
l_text long;
procedure insert_route_parameters
(
p_parameter_name in varchar2,
p_data_type in varchar2,
p_sequence_no in number
) is
begin
hr_utility.set_location('hrstrdbi.insert_route_parameters', 1);
insert into ff_route_parameters
(route_id,
sequence_no,
parameter_name,
data_type,
route_parameter_id)
select ff_routes_s.currval,
p_sequence_no,
p_parameter_name,
p_data_type,
ff_route_parameters_s.nextval
from dual;
end insert_route_parameters;
procedure insert_route_parameter_values
(
p_route_parameter_id in number,
p_value in varchar2
) is
begin
hr_utility.set_location('hrstrdbi.insert_route_parameter_values', 1);
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value,
last_update_date,
creation_date)
values (p_route_parameter_id,
ff_user_entities_s.currval,
p_value,
sysdate,
sysdate);
end insert_route_parameter_values;
procedure insert_route_context_usages
(
p_context_id in number,
p_sequence_no in number
) is
begin
hr_utility.set_location('hrstrdbi.insert_route_context_usages', 1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
p_context_id,
p_sequence_no
from dual;
end insert_route_context_usages;
procedure insert_user_entity
(
p_user_entity_name in varchar2,
p_description in varchar2,
p_notfound_allowed_flag in varchar2 default 'N'
) is
begin
hr_utility.set_location('hrstrdbi.insert_user_entity', 1);
insert into ff_user_entities
(user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
--
values (ff_user_entities_s.nextval,
null,
null,
ff_routes_s.currval,
p_notfound_allowed_flag,
p_user_entity_name,
0,
'SEH',
p_description,
sysdate,
0,
0,
0,
sysdate);
end insert_user_entity;
procedure insert_database_item
(
p_user_name in varchar2,
p_definition_text in varchar2,
p_description in varchar2,
p_data_type in varchar2,
p_null_allowed_flag in varchar2
) is
begin
hr_utility.set_location('hrstrdbi.insert_database_item', 1);
insert into ff_database_items (
user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
--
values (p_user_name,
ff_user_entities_s.currval,
p_data_type,
p_definition_text,
p_null_allowed_flag,
p_description,
sysdate,
0,
0,
0,
sysdate);
end insert_database_item;
procedure insert_route
(
p_route_name in varchar2,
p_description in varchar2,
p_text in varchar2
) is
begin
hr_utility.trace ('creating route : ' || p_route_name);
hr_utility.set_location('hrstrdbi.insert_route', 1);
insert into ff_routes
(route_id,
route_name,
user_defined_flag,
description,
text,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values (ff_routes_s.nextval,
p_route_name,
'N',
p_description,
p_text,
sysdate,
0,
0,
0,
sysdate);
end insert_route;
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 1);
select context_id
into l_date_earned_context_id
from ff_contexts
where context_name = 'DATE_EARNED';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 2);
select context_id
into l_assign_id_context_id
from ff_contexts
where context_name = 'ASSIGNMENT_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 3);
select context_id
into l_payroll_action_id_context_id
from ff_contexts
where context_name = 'PAYROLL_ACTION_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 4);
select context_id
into l_assign_action_id_context_id
from ff_contexts
where context_name = 'ASSIGNMENT_ACTION_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 5);
select context_id
into l_payroll_id_context_id
from ff_contexts
where context_name = 'PAYROLL_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 6);
select context_id
into l_accrual_plan_id_context_id
from ff_contexts
where context_name = 'ACCRUAL_PLAN_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 7);
select context_id
into l_original_entry_id
from ff_contexts
where context_name = 'ORIGINAL_ENTRY_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 8);
select context_id
into l_element_entry_id
from ff_contexts
where context_name = 'ELEMENT_ENTRY_ID';
hr_utility.set_location('hrstrdbi.insert_routes_db_items', 9);
select context_id
into l_element_type_id
from ff_contexts
where context_name = 'ELEMENT_TYPE_ID';
insert_route ('SEH_PER_PERSON_DETAILS',
'person details route',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_DETAILS',
'person details for an assignment');
insert_route ('SEH_PER_PERSON_DETAILS_PERF',
'person details route (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_DETAILS_PERF',
'person details for an assignment (performant version)');
insert_database_item ('PER_PERSON_TYPE',
'PTYPE.user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'N');
insert_database_item ('PTU_PER_PERSON_TYPE',
'hr_person_type_usage_info.get_user_person_type(SES.effective_date, PEOPLE.person_id) user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'N');
insert_database_item ('PER_LAST_NAME',
'PEOPLE.last_name',
'The person''s last name',
'T',
'N');
insert_database_item ('PER_FIRST_NAME',
'PEOPLE.first_name',
'The person''s first name',
'T',
'Y');
insert_database_item ('PER_KNOWN_AS',
'PEOPLE.known_as',
'The person''s preferred name',
'T',
'Y');
insert_database_item ('PER_MIDDLE_NAMES',
'PEOPLE.middle_names',
'The person''s middle names',
'T',
'Y');
insert_database_item ('PER_FULL_NAME',
'PEOPLE.full_name',
'The person''s full name',
'T',
'Y');
insert_database_item ('PER_APPLICANT_NUMBER',
'PEOPLE.applicant_number',
'The person''s applicant number',
'T',
'Y');
insert_database_item ('PER_DATE_VERIFIED',
'PEOPLE.date_employee_data_verified',
'The date the employee last verified his/her personal data',
'D',
'Y');
insert_database_item ('PER_DATE_OF_BIRTH',
'PEOPLE.date_of_birth',
'The person''s date of birth',
'D',
'Y');
insert_database_item ('PER_EMP_NUMBER',
'PEOPLE.employee_number',
'The person''s employee number',
'T',
'Y');
insert_database_item ('PER_NATIONAL_IDENTIFIER',
'PEOPLE.national_identifier',
'The person''s national identifier',
'T',
'Y');
insert_database_item ('PER_PREV_LAST_NAME',
'PEOPLE.previous_last_name',
'The person''s previous last name',
'T',
'Y');
insert_database_item ('PER_WORK_PHONE',
'NVL(PHONE.phone_number,PEOPLE.work_telephone)',
'The person''s work telephone number',
'T',
'Y');
insert_database_item ('PER_MAIL_DESTINATION',
'PEOPLE.expense_check_send_to_address',
'The person''s mail destination',
'T',
'Y');
insert_database_item ('PER_AGE',
'TRUNC(MONTHS_BETWEEN(SES.EFFECTIVE_DATE, PEOPLE.date_of_birth)/12)',
'The person''s age',
'N',
'Y');
insert_database_item ('PER_CURRENT_APP',
'a.meaning',
'Whether the person is a current applicant (yes/no)',
'T',
'Y');
insert_database_item ('PER_CURRENT_EMP',
'c.meaning',
'Whether the person is a current employee (yes/no)',
'T',
'Y');
insert_database_item ('PER_DISABLED',
'd.meaning',
'Whether the person is disabled (yes/no)',
'T',
'Y');
insert_database_item ('PER_SEND_EXPENSES',
'hr_general.decode_lookup(''HOME_OFFICE'',
PEOPLE.expense_check_send_to_address)',
'Where to send the person''s expenses (home/office)',
'T',
'Y');
insert_database_item ('PER_MARITAL_STATUS',
'hr_general.decode_lookup(''MAR_STATUS'',
PEOPLE.marital_status)',
'The person''s maritial status',
'T',
'Y');
insert_database_item ('PER_NATIONALITY',
'hr_general.decode_lookup(''NATIONALITY'',
PEOPLE.nationality)',
'The person''s nationality',
'T',
'Y');
insert_database_item ('PER_SEX',
'hr_general.decode_lookup(''SEX'',PEOPLE.sex)',
'The person''s sex',
'T',
'Y');
insert_database_item ('PER_TITLE',
'hr_general.decode_lookup(''TITLE'',PEOPLE.title)',
'The person''s title',
'T',
'Y');
insert_database_item ('PER_PREFIX',
'PEOPLE.pre_name_adjunct',
'The employee''s name prefix',
'T',
'Y');
insert_database_item ('PER_SUFFIX',
'PEOPLE.suffix',
'The employee''s name suffix',
'T',
'Y');
insert_database_item ('PER_1099R_NAME',
'''TRANSFER_LN=''||people.last_name||''TRANSFER_FN=''||
people.first_name||''TRANSFER_MN=''|| people.middle_names||
''TRANSFER_SSN=''||people.national_identifier',
'Employee Details for 1099R',
'T',
'N');
insert_route ('SEH_PER_PERSON_DETAILS_2',
'person details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_DETAILS_2',
'person details 2');
insert_route ('SEH_PER_PERSON_DETAILS_2_PERF',
'person details (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_DETAILS_2_PERF',
'person details 2 (performant version)');
insert_database_item ('PER_DPDNT_ADOPTION_DATE',
'PEOPLE.DPDNT_ADOPTION_DATE',
'Dependents Adoption Date',
'D',
'Y');
insert_database_item ('PER_RECEIPT_OF_DEATH_CERT_DATE',
'PEOPLE.RECEIPT_OF_DEATH_CERT_DATE',
'Date of receipt of the persons death certificate',
'D',
'Y');
insert_database_item ('PER_ORIGINAL_DATE_OF_HIRE',
'PEOPLE.ORIGINAL_DATE_OF_HIRE',
'Date the person was first hired',
'D',
'Y');
insert_database_item ('PER_DATE_OF_DEATH',
'PEOPLE.DATE_OF_DEATH',
'Persons date of death',
'D',
'Y');
insert_database_item ('PER_COORD_BEN_MED_PLN_NO',
'PEOPLE.COORD_BEN_MED_PLN_NO',
'Coordination of Benefits Medical Plan Number',
'T',
'Y');
insert_database_item ('PER_COORD_BEN_NO_CVG_FLAG',
'hr_general.decode_lookup(''YES_NO'',
nvl(PEOPLE.coord_ben_no_cvg_flag,''N''))',
'Coordination of Benefits No Other Coverage',
'T',
'Y');
insert_database_item ('PER_DPDNT_VLNTRY_SVCE_FLAG',
'hr_general.decode_lookup(''YES_NO'',
nvl(PEOPLE.dpdnt_vlntry_svce_flag,''N''))',
'Dependant on voluntary service',
'T',
'Y');
insert_database_item ('PER_BENEFIT_GROUP_ID',
'PEOPLE.BENEFIT_GROUP_ID',
'Benefit Group ID',
'N',
'Y');
insert_database_item ('PER_USES_TOBACCO_FLAG',
'hr_general.decode_lookup(''TOBACCO_USER'',
PEOPLE.uses_tobacco_flag)',
'Uses Tobacco Flag',
'T',
'Y');
insert_route ('SEH_EMP_PERSON_SERVICE_1',
'employee person period of service route 1 (date details)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('EMPLOYEE_PERSON_SERVICE_DETAILS_1',
'employee person current service date details');
insert_database_item ('EMP_HIRE_DATE',
'SERVICE.date_start',
'The employee''s hire date',
'D',
'Y');
insert_database_item ('EMP_TERM_DATE',
'SERVICE.actual_termination_date',
'The employee''s termination date',
'D',
'Y');
insert_database_item ('EMP_LAST_PROCESS_DATE',
'SERVICE.last_standard_process_date',
'The date the employee was last processed',
'D',
'Y');
insert_database_item ('EMP_PROJ_TERM_DATE',
'SERVICE.projected_termination_date',
'The employee''s projected termination date',
'D',
'Y');
insert_route ('SEH_EMP_PERSON_SERVICE_2',
'employee person period of service route (other details)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('EMPLOYEE_PERSON_SERVICE_DETAILS_2',
'employee person current service other details');
insert_database_item ('EMP_LEAVING_REASON',
'LOOK1.meaning',
'The reason the employee left',
'T',
'Y');
insert_database_item ('EMP_TERM_ACCEPTED_BY',
'PEOPLE.full_name',
'The person who accepted the employee''s notice',
'T',
'Y');
insert_route ('SEH_PER_PERSON_ADDRESS',
'employee person address route',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('EMPLOYEE_PERSON_ADDRESS_DETAILS',
'employee person address details');
insert_database_item ('PER_ADR_DATE_FROM',
'ADDR.date_from',
'The first date on which the person can be contacted at this address',
'D',
'Y');
insert_database_item ('PER_ADR_DATE_TO',
'ADDR.date_to',
'The last date on which the person can be contacted at this address',
'D',
'Y');
insert_database_item ('PER_ADR_LINE_1',
'ADDR.address_line1',
'The first line of the person''s address',
'T',
'Y');
insert_database_item ('PER_ADR_LINE_2',
'ADDR.address_line2',
'The second line of the person''s address',
'T',
'Y');
insert_database_item ('PER_ADR_LINE_3',
'ADDR.address_line3',
'The third line of the person''s address',
'T',
'Y');
insert_database_item ('PER_ADR_CITY',
'ADDR.town_or_city',
'The name of the person''s town or city',
'T',
'Y');
insert_database_item ('PER_ADR_REGION_1',
'ADDR.region_1',
'The first line of the person''s region',
'T',
'Y');
insert_database_item ('PER_ADR_REGION_2',
'ADDR.region_2',
'The second line of the person''s region',
'T',
'Y');
insert_database_item ('PER_ADR_REGION_3',
'ADDR.region_3',
'The third line of the person''s region',
'T',
'Y');
insert_database_item ('PER_ADR_POSTAL_CODE',
'ADDR.postal_code',
'The person''s postal code',
'T',
'Y');
insert_database_item ('PER_ADR_PHONE_1',
'ADDR.TELEPHONE_NUMBER_1',
'The person''s first contact number',
'T',
'Y');
insert_database_item ('PER_ADR_PHONE_2',
'ADDR.TELEPHONE_NUMBER_2',
'The person''s second contact number',
'T',
'Y');
insert_database_item ('PER_ADR_PHONE_3',
'ADDR.TELEPHONE_NUMBER_3',
'The person''s third contact number',
'T',
'Y');
insert_database_item ('PER_ADR_COUNTRY',
'a.territory_short_name',
'The name of the person''s country',
'T',
'Y');
insert_database_item ('PER_ADR_COUNTRY_CODE',
'a.territory_code',
'** Person''s country code **',
'T',
'Y');
insert_route ('SEH_ASS_PERSON_DETAILS',
'person assignment route',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_ASSIGNMENT_DETAILS',
'person assignment details');
insert_route ('SEH_ASS_PERSON_DETAILS_PERF',
'person assignment route (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_ASSIGNMENT_DETAILS_PERF',
'person assignment details ( performant version )');
insert_database_item ('ASG_GRADE',
'GRADE.name',
'The employee''s grade',
'T',
'Y');
insert_database_item ('ASG_GRADE_DATE_FROM',
'GRADE.date_from',
'The date from which this assignment grade information is effective',
'D',
'Y');
insert_database_item ('ASG_GRADE_DATE_TO',
'GRADE.date_to',
'The date to which this assignment grade information is effective',
'D',
'Y');
insert_database_item ('ASG_JOB',
'JOB.name',
'The employee''s job',
'T',
'Y');
insert_database_item ('ASG_JOB_DATE_FROM',
'JOB.date_from',
'The date from which this assignment job information is effective',
'D',
'Y');
insert_database_item ('ASG_JOB_DATE_TO',
'JOB.date_to',
'The date to which this assignment job information is effective',
'D',
'Y');
insert_database_item ('ASG_STATUS',
'AST.user_status',
'The primary status for the assignment',
'T',
'N');
insert_database_item ('ASG_PAYROLL',
'PAYROLL.payroll_name',
'The employee''s payroll',
'T',
'Y');
insert_database_item ('ASG_LOCATION',
'LOC.location_code',
'The employee''s location',
'T',
'Y');
insert_database_item ('ASG_LOC_INACTIVE_DATE',
'LOC.inactive_date',
'The date to which the location information is effective',
'D',
'Y');
insert_database_item ('ASG_ORG',
'ORG.name',
'The employee''s organization',
'T',
'N');
insert_database_item ('ASG_ORG_DATE_FROM',
'ORG.date_from',
'The date from which assignment organization information is effective',
'D',
'N');
insert_database_item ('ASG_ORG_DATE_TO',
'ORG.date_to',
'The date to which assignment organization information is effective',
'D',
'Y');
insert_database_item ('ASG_GROUP',
'GRP.group_name',
'The employee''s group',
'T',
'Y');
insert_database_item ('ASG_VACANCY',
'VAC.name',
'The name of the vacancy applied for',
'T',
'Y');
insert_database_item ('ASG_TYPE',
'HR1.meaning',
'Whether this assignment is an employee or applicant',
'T',
'Y');
insert_database_item ('ASG_PROB_UNITS',
'hr_general.decode_lookup(''UNITS'',
ASSIGN.probation_unit)',
'The units of the assignment''s probation period',
'T',
'Y');
insert_database_item ('ASG_PRIMARY',
'FND1.meaning',
'Whether this is the employee''s primary assignment (yes/no)',
'T',
'N');
insert_database_item ('ASG_MANAGER',
'hr_general.decode_lookup(''YES_NO'',
ASSIGN.manager_flag)',
'Whether the assignment is a managerial assignment (yes/no)',
'T',
'Y');
insert_database_item ('ASG_POSITION',
'POS1.name',
'The employee''s position',
'T',
'Y');
insert_database_item ('ASG_POS_DATE_FROM',
'POS1.date_effective',
'The date from which this assignment position information is effective',
'D',
'Y');
insert_database_item ('ASG_POS_DATE_TO',
'POS1.date_end',
'The date to which this assignment position information is effective',
'D',
'Y');
insert_database_item ('ASG_POS_HOURS',
'POS1.working_hours',
'The standard number of working hours for the position',
'N',
'Y');
insert_database_item ('ASG_POS_START_TIME',
'POS1.time_normal_start',
'The standard start time for the assignment position',
'T',
'Y');
insert_database_item ('ASG_POS_END_TIME',
'POS1.time_normal_finish',
'The standard end time for the assignment position',
'T',
'Y');
insert_database_item ('ASG_POS_PROB_PERIOD',
'POS1.probation_period',
'The probation period for the assignment position',
'N',
'Y');
insert_database_item ('ASG_POS_FREQ',
'hr_general.decode_lookup(''FREQUENCY'',
POS1.frequency)',
'The frequency for which the assignment position''s hours is measured',
'T',
'Y');
insert_database_item ('ASG_SUCCESSOR',
'POS2.name',
'The position name that will succeed into this position',
'T',
'Y');
insert_database_item ('ASG_RELIEF',
'POS3.name',
'The relief position if the current position hoilder is absent',
'T',
'Y');
insert_database_item ('ASG_REC_FULL_NAME',
'PEOPLE1.full_name',
'The full name for the recruiter',
'T',
'Y');
insert_database_item ('ASG_SUP_FULL_NAME',
'PEOPLE2.full_name',
'The full name for the supervisor',
'T',
'Y');
insert_database_item ('ASG_EMPLOYMENT_CATEGORY',
'hr_general.decode_lookup(''EMP_CAT'',
ASSIGN.employment_category)',
'The employment category for the assignment',
'T',
'Y');
insert_database_item ('ASG_ASSIGNMENT_CATEGORY',
'decode(ASSIGN.employment_category
,''E'', hr_general.decode_lookup(''EMP_CAT'',
ASSIGN.employment_category)
,''C'', hr_general.decode_lookup(''CWK_ASG_CATEGORY'',
ASSIGN.employment_category))',
'The assignment category for the assignment',
'T',
'Y');
insert_database_item ('ASG_PERFORMANCE_REVIEW_FREQUENCY',
'hr_general.decode_lookup(''FREQUENCY'',
ASSIGN.perf_review_period_frequency)',
'The performance review frequency for the assignment',
'T',
'Y');
insert_database_item ('ASG_SALARY_REVIEW_FREQUENCY',
'hr_general.decode_lookup(''FREQUENCY'',
ASSIGN.sal_review_period_frequency)',
'The salary review frequency for the assignment',
'T',
'Y');
insert_database_item ('ASG_PRIMARY_CODE',
'FND1.lookup_code',
'Primary Code',
'T',
'N');
insert_user_entity ('BIS_PERSON_ASSIGNMENT_DETAILS_PERF',
'person assignment details',
'Y');
insert_database_item ('ASG_EMPLOYMENT_CATEGORY_CODE',
'ASSIGN.employment_category',
'Employment Category Code',
'T',
'Y');
insert_route ('SEH_ASS_PERSON_DETAILS_2',
'person assignment route 2',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_ASSIGNMENT_DETAILS_2',
'person assignment details 2');
insert_database_item ('ASG_DATE_FROM',
'ASSIGN.effective_start_date',
'The date from which this assignment information is effective',
'D',
'N');
insert_database_item ('ASG_DATE_TO',
'ASSIGN.effective_end_date',
'The date to which this assignment information is effective',
'D',
'N');
insert_database_item ('ASG_INT_ADDR_LINE',
'ASSIGN.internal_address_line',
'The internal address of the assignment',
'T',
'Y');
insert_database_item ('ASG_ASSIGNMENT_SEQUENCE',
'ASSIGN.assignment_sequence',
'This is used as a default for assignment number',
'N',
'N');
insert_database_item ('ASG_NUMBER',
'ASSIGN.assignment_number',
'The assignment number',
'T',
'Y');
insert_database_item ('ASG_PROB_END_DATE',
'ASSIGN.date_probation_end',
'The probation period end date',
'D',
'Y');
insert_database_item ('ASG_PROB_PERIOD',
'ASSIGN.probation_period',
'The assignment''s probation period',
'N',
'Y');
insert_database_item ('ASG_HOURS',
'ASSIGN.normal_hours',
'The standard number of working hours for the assignment',
'N',
'Y');
insert_database_item ('ASG_START_TIME',
'ASSIGN.time_normal_start',
'The standard start time for the assignment',
'T',
'Y');
insert_database_item ('ASG_END_TIME',
'ASSIGN.time_normal_finish',
'The standard end time for the assignment',
'T',
'Y');
insert_database_item ('ASG_PERFORMANCE_REVIEW_PERIOD',
'ASSIGN.perf_review_period',
'The performance review period for the assignment',
'N',
'Y');
insert_database_item ('ASG_SALARY_REVIEW_PERIOD',
'ASSIGN.sal_review_period',
'The salary review period for the assignment',
'N',
'Y');
insert_database_item ('ASG_FREQ',
'HR3.meaning',
'The frequency for which the assignment working hours are measured',
'T',
'Y');
insert_database_item ('ASG_CHANGE_REASON',
'HR4.meaning',
'The change reason for the assignment',
'T',
'Y');
insert_user_entity ('BIS_PERSON_ASSIGNMENT_DETAILS_2',
'person assignment details 2',
'Y');
insert_database_item ('ASG_FREQ_CODE',
'HR3.lookup_code',
'Assignment Working Hours Frequency Code',
'T',
'Y');
insert_route ('SEH_ASS_PERSON_DETAILS_3',
'person assignment route 3',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_ASSIGNMENT_DETAILS_3',
'person assignment details 3');
insert_route ('SEH_ASS_PERSON_DETAILS_3_PERF',
'person assignment route 3 (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('PERSON_ASSIGNMENT_DETAILS_3_PERF',
'person assignment details 3 (performant version)');
insert_database_item ('ASG_LABOUR_UNION_MEMBER_FLAG',
'hr_general.decode_lookup(''YES_NO'',
nvl(ASSIGN.LABOUR_UNION_MEMBER_FLAG,''N''))',
'Labour Union Member',
'T',
'Y');
insert_database_item ('ASG_BARGAINING_UNIT_CODE',
'hr_general.decode_lookup(''BARGAINING_UNIT_CODE'',
ASSIGN.BARGAINING_UNIT_CODE)',
'Bargaining Unit Code',
'T',
'Y');
insert_route ('SEH_CON_PERSON_DETAILS',
'assignment contact details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('ASSIGNMENT_CONTACT_DETAILS',
'assignment contact details');
insert_route ('SEH_CON_PERSON_DETAILS_PERF',
'assignment contact details (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('ASSIGNMENT_CONTACT_DETAILS_PERF',
'assignment contact details (performant version)');
insert_database_item ('CON_NATIONAL_IDENTIFIER',
'PEOPLE.national_identifier',
'The contacts national identifier',
'T',
'Y');
insert_database_item ('CON_START_DATE',
'PEOPLE.effective_start_date',
'The date from which this contact information is effective',
'D',
'Y');
insert_database_item ('CON_END_DATE',
'PEOPLE.effective_end_date',
'The date to which this contact information is effective',
'D',
'Y');
insert_database_item ('CON_FULL_NAME',
'PEOPLE.full_name',
'The contact''s full name',
'T',
'Y');
insert_database_item ('CON_LAST_NAME',
'PEOPLE.last_name',
'The contact''s last name',
'T',
'Y');
insert_database_item ('CON_FIRST_NAME',
'PEOPLE.first_name',
'The contact''s first name',
'T',
'Y');
insert_database_item ('CON_KNOWN_AS',
'PEOPLE.known_as',
'The contact''s preferred name',
'T',
'Y');
insert_database_item ('CON_MIDDLE_NAMES',
'PEOPLE.middle_names',
'The contact''s middle names',
'T',
'Y');
insert_database_item ('CON_APP_NUMBER',
'PEOPLE.applicant_number',
'The contact''s applicant number',
'T',
'Y');
insert_database_item ('CON_DATE_OF_BIRTH',
'PEOPLE.date_of_birth',
'The contact''s date of birth',
'D',
'Y');
insert_database_item ('CON_EMP_NUMBER',
'PEOPLE.employee_number',
'The contact''s employee number',
'T',
'Y');
insert_database_item ('CON_WORK_PHONE',
'NVL(PHONE.phone_number,PEOPLE.work_telephone)',
'The contact''s work telephone number',
'T',
'Y');
insert_database_item ('CON_AGE',
'TRUNC(MONTHS_BETWEEN(SES.EFFECTIVE_DATE, PEOPLE.date_of_birth)/12)',
'The contact''s age',
'N',
'Y');
insert_database_item ('CON_PERSON_TYPE',
'PTYPE.user_person_type',
'The contact''s person type, employee, etc.',
'T',
'Y');
insert_database_item ('PTU_CON_PERSON_TYPE',
'hr_person_type_usage_info.get_user_person_type(SES.effective_date,PEOPLE.person_id) user_person_type',
'The contact''s person type, employee, etc.',
'T',
'Y');
insert_database_item ('CON_CURRENT_APP',
'a.meaning',
'Whether the contact is a current applicant (yes/no)',
'T',
'Y');
insert_database_item ('CON_CURRENT_EMP',
'c.meaning',
'Whether the contact is a current employee (yes/no)',
'T',
'Y');
insert_database_item ('CON_DISABLED',
'd.meaning',
'Whether the contact is disabled (yes/no)',
'T',
'Y');
insert_database_item ('CON_MARITAL_STATUS',
'hr_general.decode_lookup(''MAR_STATUS'',
PEOPLE.marital_status)',
'The contact''s maritial status',
'T',
'Y');
insert_database_item ('CON_NATIONALITY',
'hr_general.decode_lookup(''NATIONALITY'',
PEOPLE.nationality)',
'The contact''s nationality',
'T',
'Y');
insert_database_item ('CON_SEX',
'hr_general.decode_lookup(''SEX'',
PEOPLE.sex)',
'The contact''s sex',
'T',
'Y');
insert_database_item ('CON_TITLE',
'hr_general.decode_lookup(''TITLE'',
PEOPLE.title)',
'The contact''s title',
'T',
'Y');
insert_database_item ('CON_RELATIONSHIP',
'hr_general.decode_lookup(''CONTACT'',
CONTACT.contact_type)',
'The relationship of the contact to the employee',
'T',
'Y');
insert_route ('SEH_CON_PERSON_DETAILS_2',
'contact details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('CONTACT_DETAILS',
'contact details');
insert_database_item ('CON_DATE_START',
'CONTACT.DATE_START',
'Start date of the contact relationship',
'D',
'Y');
insert_database_item ('CON_DATE_END',
'CONTACT.DATE_END',
'End date of the contact relationship',
'D',
'Y');
insert_database_item ('CON_START_LIFE_REASON_ID',
'BEN1.name',
'Reason for the start of the relationship',
'T',
'Y');
insert_database_item ('CON_END_LIFE_REASON_ID',
'BEN2.name',
'Reason for the end of the relationship',
'T',
'Y');
insert_database_item ('CON_RLTD_PER_RSDS_W_DSGNTR',
'a.meaning',
'Related person resides with designator',
'T',
'Y');
insert_database_item ('CON_PERSONAL_FLAG',
'b.meaning',
'Personal Relationship Flag',
'T',
'Y');
insert_database_item ('CON_THIRD_PARTY_PAY_FLAG',
'c.meaning',
'Third Party Payments Relationship Flag',
'T',
'Y');
insert_database_item ('CON_BENEFICIARY_FLAG',
'd.meaning',
'Beneficiary Flag',
'T',
'Y');
insert_database_item ('CON_DEPENDENT_FLAG',
'e.meaning',
'Dependent Flag',
'T',
'Y');
insert_database_item ('CON_SEQUENCE_NUMBER',
'CONTACT.SEQUENCE_NUMBER',
'Sequence number',
'N',
'Y');
insert_route ('SEH_CON_PERSON_ADDRESS',
'contact address details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('CONTACT_ADDRESS_DETAILS',
'contact address details');
insert_database_item ('CON_ADR_DATE_FROM',
'ADDR.date_from',
'The first date on which the contact can be contacted at this address',
'D',
'Y');
insert_database_item ('CON_ADR_DATE_TO',
'ADDR.date_to',
'The last date on which the contact can be contacted at this address',
'D',
'Y');
insert_database_item ('CON_ADR_LINE_1',
'ADDR.address_line1',
'The first line of the contact''s address',
'T',
'Y');
insert_database_item ('CON_ADR_LINE_2',
'ADDR.address_line2',
'The second line of the contact''s address',
'T',
'Y');
insert_database_item ('CON_ADR_LINE_3',
'ADDR.address_line3',
'The third line of the contact''s address',
'T',
'Y');
insert_database_item ('CON_ADR_CITY',
'ADDR.town_or_city',
'The name of the contact''s town or city',
'T',
'Y');
insert_database_item ('CON_ADR_REGION_1',
'ADDR.region_1',
'The first line of the contact''s region',
'T',
'Y');
insert_database_item ('CON_ADR_REGION_2',
'ADDR.region_2',
'The second line of the contact''s region',
'T',
'Y');
insert_database_item ('CON_ADR_REGION_3',
'ADDR.region_3',
'The third line of the contact''s region',
'T',
'Y');
insert_database_item ('CON_ADR_POSTAL_CODE',
'ADDR.postal_code',
'The contact''s postal code',
'T',
'Y');
insert_database_item ('CON_ADR_PHONE_1',
'ADDR.telephone_number_1',
'The contact''s first telephone number',
'T',
'Y');
insert_database_item ('CON_ADR_PHONE_2',
'ADDR.telephone_number_2',
'The contact''s second telephone number',
'T',
'Y');
insert_database_item ('CON_ADR_PHONE_3',
'ADDR.telephone_number_3',
'The contact''s third telephone number',
'T',
'Y');
insert_database_item ('CON_ADR_COUNTRY',
'a.territory_short_name',
'The name of the contact''s country',
'T',
'Y');
insert_route ('SEH_REC_DETAILS',
'recruiter details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('RECRUITER_DETAILS',
'recruiter details');
insert_route ('SEH_REC_DETAILS_PERF',
'recruiter details (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('RECRUITER_DETAILS_PERF',
'recruiter details (performant version)');
insert_database_item ('REC_EMP_NUMBER',
'PEOPLE.employee_number',
'The recruiter''s employee number',
'T',
'Y');
insert_database_item ('REC_WORK_PHONE',
'NVL(PHONE.phone_number,PEOPLE.work_telephone)',
'The recruiter''s work telephone number',
'T',
'Y');
insert_database_item ('REC_PERSON_TYPE',
'PTYPE.user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'Y');
insert_database_item ('PTU_REC_PERSON_TYPE',
'hr_person_type_usage_info.get_user_person_type(people.effective_end_date, people.person_id) user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'Y');
insert_database_item ('REC_CURRENT_APP',
'a.meaning',
'Whether the recruiter is a current applicant (yes/no)',
'T',
'Y');
insert_database_item ('REC_CURRENT_EMP',
'c.meaning',
'Whether the recruiter is a current employee (yes/no)',
'T',
'Y');
insert_database_item ('REC_GRADE',
'Grade.name',
'The grade of the recruiter',
'T',
'Y');
insert_database_item ('REC_JOB',
'JOB.name',
'The job of the recruiter',
'T',
'Y');
insert_database_item ('REC_LOCATION',
'LOC.location_code',
'The location of the recruiter',
'T',
'Y');
insert_database_item ('REC_ORG',
'ORG.name',
'The organization name of the recruiter',
'T',
'Y');
insert_database_item ('REC_INT_ADDR_LINE',
'RASSIGN.internal_address_line',
'The internal address of the recruiter',
'T',
'Y');
insert_database_item ('REC_MANAGER',
'hr_general.decode_lookup(''YES_NO'',
RASSIGN.manager_flag)',
'Whether the assignment is a managerial assignment (yes/no)',
'T',
'Y');
insert_database_item ('REC_POSITION',
'POS.name',
'The position name of the recruiter',
'T',
'Y');
insert_route ('SEH_SUP_DETAILS',
'supervisor details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SUPERVISOR_DETAILS',
'supervisor details');
insert_route ('SEH_SUP_DETAILS_PERF',
'supervisor details (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SUPERVISOR_DETAILS_PERF',
'supervisor details (performant version)');
insert_database_item ('SUP_EMP_NUMBER',
'PEOPLE.employee_number',
'The employee number of the supervisor',
'T',
'Y');
insert_database_item ('SUP_WORK_PHONE',
'NVL(PHONE.phone_number,PEOPLE.work_telephone)',
'The supervisor''s work telephone number',
'T',
'Y');
insert_database_item ('SUP_PERSON_TYPE',
'PTYPE.user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'Y');
insert_database_item ('PTU_SUP_PERSON_TYPE',
'hr_person_type_usage_info.get_user_person_type(PEOPLE.effective_end_date,PEOPLE.person_id) user_person_type',
'Type of person, eg. employee, applicant, etc.',
'T',
'Y');
insert_database_item ('SUP_DATE_FROM',
'SASSIGN.effective_start_date',
'The date from which this supervisor information is effective',
'D',
'Y');
insert_database_item ('SUP_DATE_TO',
'SASSIGN.effective_end_date',
'The date to which this supervisor information is effective',
'D',
'Y');
insert_database_item ('SUP_INT_ADDR_LINE',
'SASSIGN.internal_address_line',
'The internal address of the supervisor',
'T',
'Y');
insert_database_item ('SUP_GRADE',
'Grade.name',
'The grade of the supervisor',
'T',
'Y');
insert_database_item ('SUP_JOB',
'JOB.name',
'The job of the supervisor',
'T',
'Y');
insert_database_item ('SUP_LOCATION',
'LOC.location_code',
'The location of the the supervisor',
'T',
'Y');
insert_database_item ('SUP_ORG',
'ORG.name',
'The organization name of the supervisor',
'T',
'Y');
insert_database_item ('SUP_POSITION',
'POS.name',
'The position name of the supervisor',
'T',
'Y');
insert_database_item ('SUP_CURRENT_EMP',
'c.meaning',
'Whether the supervisor is a current employee (yes/no)',
'T',
'Y');
insert_database_item ('SUP_MANAGER',
'hr_general.decode_lookup(''YES_NO'',
SASSIGN.manager_flag)',
'Whether the assignment is a managerial assignment (yes/no)',
'T',
'Y');
insert_database_item ('SUP_EMAIL_ADDRESS',
'people.email_address',
'Persons email address',
'T',
'Y');
insert_route ('SEH_APL_DETAILS',
'application details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('APPLICATION_DETAILS',
'application details');
insert_database_item ('APL_DATE_RECEIVED',
'APPLIC.date_received',
'The date the application was received',
'D',
'Y');
insert_database_item ('APL_DATE_END',
'APPLIC.date_end',
'The date the application ended',
'D',
'Y');
insert_route ('SEH_ASS_PER_STATUS_DP',
'Personal status (Date paid context)',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_user_entity ('ASSIGNMENT_DP_STATUS',
'Personal status (Date paid context)');
insert_database_item ('ASG_PER_STATUS_DP',
'STYPE.per_system_status',
'Personal status for the assignment (Date paid context)',
'T',
'Y');
select max (ASSACT2.action_sequence)
from pay_payroll_actions PACT2
, pay_assignment_actions ASSACT2
where ASSACT2.assignment_id = &B1
and ASSACT2.action_status = ''C''
and PACT2.payroll_action_id = ASSACT2.payroll_action_id
and PACT2.action_type in (''R'', ''Q'')
)
and PACT.payroll_action_id = ASSACT.payroll_action_id
and PACT.time_period_id = TIMEP.time_period_id
and PAYROLL.payroll_id = PACT.payroll_id
and PACT.effective_date BETWEEN PAYROLL.effective_start_date
AND PAYROLL.effective_end_date';
insert_route ('SEH_ASS_LAST_PER_NUM',
'Period number the assignment last processed',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_user_entity ('LAST_PER_NUMBER',
'Period number the assignment last processed',
'Y');
insert_database_item ('ASG_LAST_PROC_PERIOD_NUMBER',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.period_num',
'The period number the assignment was last processed',
'N',
'N');
insert_database_item ('ASG_LAST_PROC_PERIOD_NAME',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.period_name',
'The period name the assignment was last processed',
'T',
'N');
insert_database_item ('ASG_LAST_PROC_PERIOD_ID',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.time_period_id',
'The Time period id the assignment was last processed',
'N',
'N');
insert_database_item ('ASG_LAST_PROC_PAYROLL_NAME',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ PAYROLL.payroll_name',
'The payroll name the assignment was last processed',
'T',
'N');
select max (ASSACT2.action_sequence)
from pay_payroll_actions PACT2
, pay_assignment_actions ASSACT2
where ASSACT2.assignment_id = &B1
and ASSACT2.action_status = ''C''
and PACT2.payroll_action_id = ASSACT2.payroll_action_id
and PACT2.action_type in (''R'', ''Q'')
)
and PACT.payroll_action_id = ASSACT.payroll_action_id
and PACT.time_period_id = TIMEP.time_period_id
and PAYROLL.payroll_id = PACT.payroll_id
and PACT.date_earned BETWEEN PAYROLL.effective_start_date
AND PAYROLL.effective_end_date';
insert_route ('SEH_ASG_LAST_EARNED_PER_NUM',
'Period number the assignment last processed as of date earned',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_user_entity ('LAST_EARNED_PER_NUMBER',
'Period number the assignment last processed as of date earned',
'Y');
insert_database_item ('ASG_LAST_EARNED_PERIOD_NUMBER',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.period_num',
'The period number the assignment was last processed as of date earned',
'N',
'N');
insert_database_item ('ASG_LAST_EARNED_PERIOD_NAME',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.period_name',
'The period name the assignment was last processed as of date earned',
'T',
'N');
insert_database_item ('ASG_LAST_EARNED_PERIOD_ID',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ TIMEP.time_period_id',
'The Time period id the assignment was last processed as of date earned',
'N',
'N');
insert_database_item ('ASG_LAST_EARNED_PAYROLL_NAME',
'/*+ INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS N1)*/ PAYROLL.payroll_name',
'The payroll name the assignment was last processed as of date earned',
'T',
'N');
insert_route ('SEH_ASS_START_DATE',
'Start date for the assignment',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_user_entity ('ASS_START_DATE',
'The start date of the assignment');
insert_database_item ('ASG_START_DATE',
'min (effective_start_date)',
'The start date of the assignment',
'D',
'Y');
insert_route ('SEH_ASS_ACTION',
'Assignment action route',
l_text);
insert_route_context_usages (l_assign_action_id_context_id, 1);
insert_user_entity ('SEH_ASS_ACTION',
'Assignment action route');
insert_database_item ('CHEQUE_UK_NUMBER',
'serial_number',
'The cheque number for the assignment action (UK spelling)',
'T',
'Y');
insert_database_item ('CHECK_US_NUMBER',
'serial_number',
'The cheque number for the assignment action (US spelling)',
'T',
'Y');
insert_route ('SEH_PAY_DETAILS',
'Payroll details',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('PAY_DETAILS',
'Payroll details',
'Y');
insert_database_item ('PAY_PROC_PERIOD_NUMBER',
'TPERIOD.period_num',
'The current period number for the payroll',
'N',
'N');
insert_database_item ('PAY_PROC_PERIOD_START_DATE',
'TPERIOD.start_date',
'The start date of the payroll period',
'D',
'N');
insert_database_item ('PAY_PROC_PERIOD_END_DATE',
'TPERIOD.end_date',
'The end date of the payroll period',
'D',
'N');
insert_database_item ('PAY_PROC_PERIOD_DIRECT_DEPOSIT_DATE',
'TPERIOD.default_dd_date',
'The direct deposit date for the payroll period',
'D',
'Y');
insert_database_item ('PAY_PROC_PERIOD_PAY_ADVICE_DATE',
'TPERIOD.pay_advice_date',
'The pay advice date for the payroll period',
'D',
'Y');
insert_database_item ('PAY_PROC_PERIOD_CUT_OFF_DATE',
'TPERIOD.cut_off_date',
'The cut off date for the payroll period',
'D',
'Y');
insert_database_item ('PAY_PROC_PERIOD_ID',
'TPERIOD.time_period_id',
'The id of the time period for the payroll',
'N',
'N');
insert_database_item ('PAY_PROC_PERIOD_NAME',
'TPERIOD.period_name',
'The period name for the payroll',
'T',
'N');
insert_database_item ('PAY_PERIODS_PER_YEAR',
'TPTYPE.number_per_fiscal_year',
'The number of payable periods in the year',
'N',
'N');
insert_route ('SEH_PAY_ACTION_DETAILS',
'Payroll action details',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('PAY_ACTION_DETAILS',
'Payroll action details');
insert_database_item ('PAY_PROC_PERIOD_DATE_PAID',
'PACTION.effective_date',
'The date the payroll was paid',
'D',
'N');
insert_route ('SEH_SADMIN_SALARY_BASIS',
'Salary basis route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SADMIN_SALARY_BASIS',
'Salary basis');
insert_route ('SEH_SADMIN_SALARY_BASIS_PERF',
'Salary basis route for salary admin (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SADMIN_SALARY_BASIS_PERF',
'Salary basis (performant version)');
insert_database_item ('ASG_SALARY_BASIS_NAME',
'BASES.name',
'The salary basis name for the assignment',
'T',
'Y');
insert_database_item ('ASG_SALARY_BASIS',
'hr_general.decode_lookup(''PAY_BASIS'',
BASES.pay_basis)',
'The payment basis (ie. frequency) for the assignment, eg. monthly',
'T',
'Y');
insert_database_item ('ASG_SALARY_BASIS_CODE',
'BASES.pay_basis',
'The payment basis lookup code for the assignment',
'T',
'Y');
insert_database_item ('ASG_SALARY_BASIS_ANNUALIZATION_FACTOR',
'BASES.pay_annualization_factor',
'The payment basis pay annualization factor for the assignment',
'N',
'Y');
insert_database_item ('ASG_SALARY_BASIS_GRADE_ANNUALIZATION_FACTOR',
'BASES.grade_annualization_factor',
'The payment basis grade annualization factor for the assignment',
'N',
'Y');
insert_database_item ('ASG_SALARY_ELEMENT',
'ETYPE.element_name',
'The display element name',
'T',
'Y');
insert_database_item ('ASG_SALARY_ELEMENT_VALUE_NAME',
'INPUTV.name',
'The display input value name',
'T',
'Y');
insert_database_item ('ASG_SALARY_GRADE_RATE',
'RATE.name',
'The display rate name',
'T',
'Y');
insert_database_item ('ASG_SALARY_RATE_BASIS',
'hr_general.decode_lookup(''PAY_BASIS'',
BASES.rate_basis)',
'The salary rate basis (ie. frequency)',
'T',
'Y');
insert_route ('SEH_SADMIN_SALARY_ELEMENT',
'Salary element route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SADMIN_SALARY_ELEMENT',
'Salary element',
'Y');
insert_database_item ('ASG_SALARY',
'fnd_number.canonical_to_number (EEV.screen_entry_value)',
'The current salary for an employee',
'N',
'Y');
(select max (PRO2.change_date)
from per_pay_proposals PRO2
where PRO2.change_date <= &B1
and PRO2.assignment_id = PRO.assignment_id)';
insert_route ('SEH_SADMIN_SALARY_PROPOSALS',
'Salary Proposals route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_SALARY_PROPOSALS',
'Salary element',
'Y');
(select max (PRO2.change_date)
from per_pay_proposals PRO2
where PRO2.change_date <= &B1
and PRO2.assignment_id = PRO.assignment_id)';
insert_route ('SEH_SADMIN_SALARY_PROPOSALS_PERF',
'Salary Proposals route for salary admin ( performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_SALARY_PROPOSALS_PERF',
'Salary element (performant version)',
'Y');
insert_database_item ('ASG_LAST_SALARY_CHANGE_APPROVED',
'decode (EE.element_entry_id, null, HRNO.meaning, HRYES.meaning)',
'Whether the last proposed salary change has been approved ',
'T',
'N');
(select max (PRO2.change_date)
from per_pay_proposals PRO2
where PRO2.change_date <= &B1
and PRO2.assignment_id = PRO.assignment_id)';
insert_route ('SEH_SADMIN_SALARY_PROPOSALS_2',
'Salary Proposals route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_SALARY_PROPOSALS_2',
'Salary element',
'Y');
(select max (PRO2.change_date)
from per_pay_proposals PRO2
where PRO2.change_date <= &B1
and PRO2.assignment_id = PRO.assignment_id)';
insert_route ('SEH_SADMIN_SALARY_PROPOSALS_2_PERF',
'Salary Proposals route for salary admin (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_SALARY_PROPOSALS_2_PERF',
'Salary element (performant version)',
'Y');
insert_database_item ('ASG_LAST_SALARY_DATE',
'PRO.change_date',
'The last salary change date',
'D',
'Y');
insert_database_item ('ASG_LAST_CHANGE_REASON',
'hr_general.decode_lookup(''PROPOSAL_REASON'',
PRO.proposal_reason)',
'The reason the salary was changed',
'T',
'Y');
insert_database_item ('ASG_NEXT_SALARY_DATE',
'PRO.next_sal_review_date',
'The date of the next salary change',
'D',
'Y');
(select max (PRO2.review_date)
from per_performance_reviews PRO2
where PRO2.person_id = PRO.person_id
and PRO2.review_date <= &B1)
and EVENT.event_id (+)= PRO.event_id
and HR1.lookup_code (+)= EVENT.type
and HR1.lookup_type (+)= ''EMP_INTERVIEW_TYPE''
and HR2.lookup_code (+)= PRO.performance_rating
and HR2.lookup_type (+)= ''PERFORMANCE_RATING''
and LOC.location_id (+)= EVENT.location_id';
insert_route ('SEH_SADMIN_LAST_PERFORM_REVIEW',
'Last performance review route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_LAST_REVIEW',
'Salary element',
'Y');
(select max (PRO2.review_date)
from per_performance_reviews PRO2
where PRO2.person_id = PRO.person_id
and PRO2.review_date <= &B1)
and EVENT.event_id (+)= PRO.event_id
and LOC.location_id (+)= EVENT.location_id';
insert_route ('SEH_SADMIN_LAST_PERFORM_REVIEW_PERF',
'Last performance review route for salary admin (performant version)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_LAST_REVIEW_PERF',
'Salary element (performant version)',
'Y');
insert_database_item ('ASG_LAST_PERFORMANCE_DATE',
'decode (PRO.event_id, null, PRO.review_date, EVENT.date_start)',
'Last performance review date',
'D',
'Y');
insert_database_item ('ASG_LAST_PERFORMANCE_TYPE',
'hr_general.decode_lookup(''EMP_INTERVIEW_TYPE'',
EVENT.type)',
'Last performance review type',
'T',
'Y');
insert_database_item ('ASG_LAST_PERFORMANCE_LOCATION',
'LOC.location_code',
'Last performance review location',
'T',
'Y');
insert_database_item ('ASG_LAST_PERFORMANCE_RATING',
'hr_general.decode_lookup(''PERFORMANCE_RATING'',
PRO.performance_rating)',
'Last performance review rating',
'T',
'Y');
insert_database_item ('ASG_NEXT_PERFORMANCE_DATE',
'PRO.next_perf_review_date',
'Next performance review date',
'D',
'Y');
(select max(PRO2.change_date)
from per_pay_proposals PRO2
where PRO2.change_date <= &B1
and PRO2.assignment_id = PRO.assignment_id)';
insert_route ('SEH_SADMIN_LAST_SALARY_CHANGE',
'Last salary change route for salary admin',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_SADMIN_LAST_SALARY_CHANGE',
'Last salary change',
'Y');
insert_database_item ('ASG_LAST_PROPOSED_SALARY_CHANGE',
'decode(BASES1.pay_basis, BASES2.pay_basis, (PRO.proposed_salary_n -
EEV.screen_entry_value), null)',
'The proposed salary change',
'N',
'Y');
insert_database_item ('ASG_LAST_PROPOSED_SALARY_PERCENT',
'decode(BASES1.pay_basis, BASES2.pay_basis, (((PRO.proposed_salary_n -
EEV.screen_entry_value) *100)/EEV.screen_entry_value), null)',
'The proposed salary change as a percentage',
'N',
'Y');
insert_route ('SEH_ASSIGN_HR_ADDRESS',
'The work address for the assignment (general legislation)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGN_HR_ADDRESS',
'The work address for the assignment (general legislation)',
'Y');
insert_database_item ('LOC_ADR_LINE_1',
'HRLOC.address_line_1',
'The first line of the assignment''s work address',
'T',
'Y');
insert_database_item ('LOC_ADR_LINE_2',
'HRLOC.address_line_2',
'The second line of the assignment''s work address',
'T',
'Y');
insert_database_item ('LOC_ADR_LINE_3',
'HRLOC.address_line_3',
'The third line of the assignment''s work address',
'T',
'Y');
insert_database_item ('LOC_ADR_POSTAL_CODE',
'HRLOC.postal_code',
'The postal code for the assignment''s work address',
'T',
'Y');
insert_database_item ('LOC_ADR_REGION_1',
'HRLOC.region_1',
'The first line of the assignment''s region',
'T',
'Y');
insert_database_item ('LOC_ADR_REGION_2',
'HRLOC.region_2',
'The second line of the assignment''s region',
'T',
'Y');
insert_database_item ('LOC_ADR_REGION_3',
'HRLOC.region_3',
'The third line of the assignment''s region',
'T',
'Y');
insert_database_item ('LOC_ADR_PHONE_1',
'HRLOC.telephone_number_1',
'The assignment''s first work telephone number',
'T',
'Y');
insert_database_item ('LOC_ADR_PHONE_2',
'HRLOC.telephone_number_2',
'The assignment''s second work telephone number',
'T',
'Y');
insert_database_item ('LOC_ADR_PHONE_3',
'HRLOC.telephone_number_3',
'The assignment''s third work telephone number',
'T',
'Y');
insert_database_item ('LOC_ADR_CITY',
'HRLOC.town_or_city',
'The town or city where the assignment works',
'T',
'Y');
insert_database_item ('LOC_ADR_COUNTRY',
'TER.territory_short_name',
'The country where the assignment works',
'T',
'Y');
insert_route ('SEH_ASSIGN_HR_ADDRESS_US',
'The work address for the assignment (US legislation)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGN_HR_ADDRESS_US',
'The work address for the assignment (US legislation)',
'Y');
insert_database_item ('LOC_ADR_US_COUNTY',
'HRLOC.region_1',
'The assignment''s work county (US only)',
'T',
'Y');
insert_database_item ('LOC_ADR_US_STATE',
'HR1.meaning',
'The assignment''s work state (US only)',
'T',
'Y');
insert_database_item ('LOC_ADR_US_STATE_CODE',
'HRLOC.region_2',
'The assignment''s work state code (US only)',
'T',
'Y');
insert_route ('SEH_ASSIGN_ADDRESS_US',
'The home address for the assignment (US legislation)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGN_ADDRESS_US',
'The home address for the assignment (US legislation)',
'Y');
insert_database_item ('PER_ADR_US_COUNTY',
'ADDR.region_1',
'The assignment''s county (US only)',
'T',
'Y');
insert_database_item ('PER_ADR_US_STATE',
'HR1.meaning',
'The assignment''s state (US only)',
'T',
'Y');
insert_database_item ('PER_ADR_US_STATE_CODE',
'ADDR.region_2',
'The assignment''s state code (US only)',
'T',
'Y');
insert_route ('SEH_ASSIGN_HR_ADDRESS_UK',
'The work address for the assignment (GB legislation)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGN_HR_ADDRESS_UK',
'The work address for the assignment (GB legislation)',
'Y');
insert_database_item ('LOC_ADR_UK_COUNTY',
'HR1.meaning',
'The assignment''s work county (UK only)',
'T',
'Y');
insert_route ('SEH_ASSIGN_ADDRESS_UK',
'The home address for the assignment (GB legislation)',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGN_ADDRESS_UK',
'The home address for the assignment (GB legislation)',
'Y');
insert_database_item ('PER_ADR_UK_COUNTY',
'HR1.meaning',
'The assignment''s home county (UK only)',
'T',
'Y');
insert_route ('SEH_SYSDATE',
'sysdate route',
l_text);
insert_user_entity ('SEH_SYSDATE',
'sysdate route');
insert_database_item ('SYSDATE',
'sysdate',
'The system date',
'D',
'N');
insert_route ('SEH_SESSION_DATE',
'session date route',
l_text);
insert_user_entity ('SEH_SESSION_DATE',
'session date route',
'Y');
insert_database_item ('SESSION_DATE',
'effective_date',
'The session date',
'D',
'N');
insert_route ('TARGET_PAYROLL_ACTION_ROUTE',
'Route for Target payroll action',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('TARGET_PAYROLL_ACTION_ENTITY',
'Entity for payroll target action',
'Y');
insert_database_item ('ACTION_TYPE',
'TARGET.ACTION_TYPE',
'Actual Value of Action_Type',
'T',
'N');
insert_route ('PAY_PAYROLLS_ROUTE',
'Route for pay_payrolls',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_payroll_id_context_id, 2);
insert_user_entity ('PAY_PAYROLLS_ENTITY',
'Entity for pay_payrolls',
'Y');
insert_database_item ('PAYROLL_ARREARS_FLAG',
'PAYROLL.ARREARS_FLAG',
'Actual Value of Arrears_Flag',
'T',
'Y');
insert_route ('ASSIGNMENT_BUDGET_VALUES',
'Route for Assignment Budget Values',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
insert_route_parameters ('UNIT', 'T', 1);
insert_user_entity ('ASSIGNMENT_FTE_BUDGET_VALUES',
'Entity for FTE Assignment Budget Values',
'Y');
select ff_route_parameters_s.currval
into l_route_parameters_id
from dual;
insert_route_parameter_values ( l_route_parameters_id,
'''FTE''');
insert_database_item ('ASG_FTE_VALUE',
'BUDGET.VALUE',
'Full Time Equivalent Budget Actual Value',
'N',
'N');
insert_user_entity ('ASSIGNMENT_HEAD_BUDGET_VALUES',
'Entity for Assignment HEAD Budget Values',
'Y');
insert_route_parameter_values ( l_route_parameters_id,
'''HEAD''');
insert_database_item ('ASG_HEAD_VALUE',
'BUDGET.VALUE',
'HEAD Budget Actual Value',
'N',
'N');
insert_user_entity ('ASSIGNMENT_MONEY_BUDGET_VALUES',
'Entity for Assignment Money Budget Values',
'Y');
insert_route_parameter_values ( l_route_parameters_id,
'''MONEY''');
insert_database_item ('ASG_MONEY_VALUE',
'BUDGET.VALUE',
'Money Budget Actual Value',
'N',
'N');
insert_user_entity ('ASSIGNMENT_PFT_BUDGET_VALUES',
'Entity for Assignment PFT Budget Values',
'Y');
insert_route_parameter_values ( l_route_parameters_id,
'''PFT''');
insert_database_item ('ASG_PFT_VALUE',
'BUDGET.VALUE',
'PFT Budget Actual Value',
'N',
'N');
insert_route ('ASSIGNMENT_FULL_TIME_CONDITIONS',
'Route for Assignment Full Time Conditions for pay_payrolls',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('ASSIGNMENT_FULL_TIME_CONDITIONS',
'Entity for Assignment Full Time Conditions',
'Y');
insert_database_item ('ASG_FULL_TIME_HOURS',
'nvl(POS.WORKING_HOURS,nvl(ORG.WORKING_HOURS,BUS.WORKING_HOURS))',
'Full Time Working Hours',
'N',
'N');
insert_database_item ('ASG_FULL_TIME_FREQ',
'nvl(POS.FREQUENCY,nvl(ORG.FREQUENCY,BUS.FREQUENCY))',
'Full Time Frequency',
'T',
'N');
insert_route ('ACCRUAL_PLAN_VALUES',
'Route for Accrual Plans',
l_text);
insert_route_context_usages (l_accrual_plan_id_context_id, 1);
insert_user_entity ('ACP_NAME',
'Entity for Accrual PLans',
'Y');
insert_database_item ('ACP_NAME',
'PAP.ACCRUAL_PLAN_NAME',
'Name of accrual plan',
'T',
'N');
insert_database_item ('ACP_CATEGORY',
'PAP.ACCRUAL_CATEGORY',
'Accrual Category - Sick, Vacation etc',
'T',
'N');
insert_database_item ('ACP_START',
'PAP.ACCRUAL_START',
'Accrual Start - BOY, HD etc',
'T',
'Y');
insert_database_item ('ACP_UOM',
'PAP.ACCRUAL_UNITS_OF_MEASURE',
'Unit of measure used for accrual plan',
'T',
'N');
insert_database_item ('ACP_INELIGIBILITY_PERIOD_TYPE',
'PAP.INELIGIBLE_PERIOD_TYPE',
'Type of period before eligibility commences',
'T',
'Y');
insert_database_item ('ACP_INELIGIBILITY_PERIOD_LENGTH',
'PAP.INELIGIBLE_PERIOD_LENGTH',
'Number of periods before eligibility is attained',
'N',
'Y');
and not exists (select 1
from pay_element_entry_values_f pev3,
pay_element_entries_f pee1
where pee1.element_entry_id = pev3.element_entry_id
and pev3.element_entry_id != pev1.element_entry_id
and pev3.input_value_id = pev1.input_value_id
and pev3.screen_entry_value <= fnd_date.date_to_canonical(&B3)
and ( (pev3.screen_entry_value > pev1.screen_entry_value)
or (pev3.screen_entry_value = pev1.screen_entry_value)
and pee1.entry_type = ''S''))';
insert_route ('CARRIED_OVER_VALUES',
'Route for Accrual Plans',
l_text);
insert_route_context_usages (l_accrual_plan_id_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_route_context_usages (l_date_earned_context_id, 3);
insert_user_entity ('ACP_CARRIED_OVER_PTO',
'Entity for carried over values',
'Y');
insert_database_item ('ACP_CARRIED_OVER_PTO',
'PEV2.SCREEN_ENTRY_VALUE',
'Amount of PTO carried over',
'N',
'N');
insert_database_item ('ACP_CARRIED_OVER_DATE',
'PEV1.SCREEN_ENTRY_VALUE',
'Date on which CO is effective',
'N',
'N');
insert_route ('ACCRUAL_PLAN_SERVICE_DATES',
'Route for Accrual Plan Service Dates',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
insert_user_entity ('ACP_SERVICE_DATES',
'Entity for accrual plan service dates',
'Y');
insert_database_item ('ACP_TERMINATION_DATE',
'PPS.ACTUAL_TERMINATION_DATE',
'Termination Date',
'D',
'Y');
insert_database_item ('ACP_SERVICE_START_DATE',
'PPS.DATE_START',
'Hire Date',
'D',
'N');
insert_route ('ACCRUAL_PLAN_ENROLLMENT_DATES',
'Route for Accrual Plan Dates',
l_text);
insert_route_context_usages (l_accrual_plan_id_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_route_context_usages (l_date_earned_context_id, 3);
insert_user_entity ('ACCRUAL_PLAN_ENROLLMENT_DATES',
'Entity for Date of enrollment in a plan',
'Y');
insert_database_item ('ACP_ENROLLMENT_START_DATE',
'LEAST(PEE.EFFECTIVE_START_DATE)',
'Enrollment Date',
'D',
'N');
insert_database_item ('ACP_ENROLLMENT_END_DATE',
'GREATEST(PEE.EFFECTIVE_END_DATE)',
'Enrollment Date',
'D',
'N');
insert_route ('ACCRUAL_PLAN_CONT_SERVICE_DATES',
'Route for Accrual Plan Dates',
l_text);
insert_route_context_usages (l_accrual_plan_id_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_route_context_usages (l_date_earned_context_id, 3);
insert_user_entity ('ACCRUAL_PLAN_CONT_SERVICE_DATES',
'Entity for CSD of person enrolled in plan',
'Y');
insert_database_item ('ACP_CONTINUOUS_SERVICE_DATE',
'FND_DATE.CANONICAL_TO_DATE(PEV.SCREEN_ENTRY_VALUE)',
'Continuous Service Date',
'D',
'Y');
insert_route ('ORIGINAL_ENTRY_ATTRIBUTES',
'Route for Original Element Entry attributes',
l_text);
insert_route_context_usages (l_original_entry_id, 1);
insert_user_entity ('ORIGINAL_ENTRY_ATTRIBUTES',
'Entity for original entry details');
insert_database_item ('ENTRY_START_DATE',
'min(TARGET.effective_start_date)',
'start date of the original entry',
'D',
'N');
insert_database_item ('ENTRY_END_DATE',
'max(TARGET.effective_end_date)',
'end date of the original entry',
'D',
'N');
insert_route ('PAY_EARNED_PERIOD_CORE',
'Route for pay earned period',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('PAY_PD_DETAILS_CORE',
'Entity for original entry details',
'Y');
insert_database_item ('PAY_EARNED_START_DATE',
'PTP.start_date',
'The start date of the earned period',
'D',
'N');
insert_database_item ('PAY_EARNED_END_DATE',
'PTP.end_date',
'The end date of the earned period',
'D',
'N');
insert_database_item ('PAY_EARNED_DIRECT_DEPOSIT_DATE',
'PTP.default_dd_date',
'The direct deposit date of the earned period',
'D',
'N');
insert_database_item ('PAY_EARNED_PAY_ADVICE_DATE',
'PTP.pay_advice_date',
'The pay advice date of the earned period',
'D',
'N');
insert_database_item ('PAY_EARNED_CUT_OFF_DATE',
'PTP.cut_off_date',
'The cut off date of the earned period',
'D',
'N');
insert_database_item ('PAY_EARNED_PERIOD_NAME',
'PTP.period_name',
'The period name for the earned period',
'T',
'N');
insert_database_item ('PAY_EARNED_PERIOD_NUMBER',
'PTP.period_num',
'The period number for the earned period',
'N',
'N');
insert_database_item ('PAY_EARNED_PERIOD_ID',
'PTP.time_period_id',
'The time period id for the earned period',
'N',
'N');
and not exists (select ''''
from pay_grossup_bal_exclusions pgb
where prr.source_id = pgb.source_id
and pgb.balance_type_id = pbt.balance_type_id
and pgb.source_type = ''EE''
)
and not exists (select ''''
from pay_grossup_bal_exclusions pgb
where prr.element_type_id = pgb.source_id
and pgb.balance_type_id = pbt.balance_type_id
and pgb.source_type = ''ET''
)';
insert_route ('GROSSUP_AMOUNT',
'Route for gross up amount',
l_text);
insert_route_context_usages (l_assign_action_id_context_id, 1);
insert_route_context_usages (l_element_entry_id, 2);
insert_user_entity ('GROSSUP_AMOUNT',
'Entity for gross up');
insert_database_item ('GROSSUP_AMOUNT',
'nvl(sum(pay_balance_pkg.get_value(pdb.defined_balance_id,paa.assignment_action_id)), 0)',
'Gross up amount to be added to the Net',
'N',
'N');
insert_route ('ASSIGNMENT_CONTRACTS_ROUTE',
'Route for Assignment Contracts',
l_text);
insert_route_context_usages (l_assign_id_context_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
insert_user_entity ('insert_route_context_usages',
'Entity for Assignment Contracts',
'Y');
insert_database_item ('CTR_TYPE',
'target.type',
'Contract Type (code)',
'T',
'Y');
insert_database_item ('CTR_STATUS',
'target.status',
'Contract Status (code)',
'T',
'Y');
insert_database_item ('CTR_TYPE_MEANING',
'hr_general.decode_lookup(''CONTRACT_TYPE'',target.type)',
'Contract Type (meaning)',
'T',
'Y');
insert_database_item ('CTR_STATUS_MEANING',
'hr_general.decode_lookup(''CONTRACT_STATUS'',target.status)',
'Contract Status (meaning)',
'T',
'Y');
insert_route ('PER_TIME_PERIODS_INFO',
'PER_TIME_PERIODS_INFO based on payroll_action_id and effective_date',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('PER_TIME_PERIOD_DETAILS',
'PER_TIME_PERIOD_DETAILS',
'Y');
insert_database_item ('PAY_NO_OF_SCHEDULED_PAYMENTS',
'Count(target.REGULAR_PAYMENT_DATE)',
'Number of Scheduled Payments Per Year',
'N',
'Y');
insert_route ('ELEMENT_TYPE_ATTRIBUTES',
'Route for Element Type attributes',
l_text);
insert_route_context_usages (l_element_type_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
insert_user_entity ('ELEMENT_TYPE_ATTRIBUTES',
'Entity for element type details');
insert_database_item ('ELEMENT_NAME',
'TARGET.element_name',
'element name of the element being processed',
'T',
'N');
insert_database_item ('CURRENT_ELEMENT_TYPE_ID',
'TARGET.element_type_id',
'The element type id of the element being processed',
'N',
'N');
insert_route ('ACCRUAL_PLAN_PAYROLL_PROCESS_1',
'Route for Accrual Plan ID',
l_text);
insert_route_context_usages (l_element_entry_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
insert_user_entity ('ACCRUAL_PLAN_PAYROLL_PROCESS_1',
'Entity for ID of a plan',
'Y');
insert_database_item ('PTO_ACCRUAL_PLAN_ID',
'DISTINCT PAP.ACCRUAL_PLAN_ID',
'Accrual Plan ID',
'N',
'N');
insert_route ('ACCRUAL_PLAN_PAYROLL_PROCESS_2',
'Route for Accrual Plan Dates',
l_text);
insert_route_context_usages (l_assign_action_id_context_id, 1);
insert_user_entity ('ACCRUAL_PLAN_PAYROLL_PROCESS_2',
'Entity for start and end calculation dates for pto',
'Y');
insert_database_item ('PTO_DATE_PAID_CALCULATION_DATE',
'PPA.EFFECTIVE_DATE',
'Date Paid for current period',
'D',
'Y');
insert_database_item ('PTO_DATE_EARNED_CALCULATION_DATE',
'PPA.DATE_EARNED',
'Date Earned for current period',
'D',
'Y');
and prr.element_type_id = (SELECT pap.balance_element_type_id
FROM pay_accrual_plans pap
WHERE pap.accrual_plan_element_type_id = &B2)
and ppa.action_type in (''R'',''Q'',''V'',''B'')';
insert_route ('ACCRUAL_PLAN_PAYROLL_PROCESS_3',
'Route for Accrual Plan Dates',
l_text);
insert_route_context_usages (l_assign_action_id_context_id, 1);
insert_route_context_usages (l_element_type_id, 2);
insert_user_entity ('ACCRUAL_PLAN_PAYROLL_PROCESS_3',
'Entity for start and end calculation dates for pto',
'Y');
insert_database_item ('PTO_DATE_PAID_START_DATE',
'MAX(PPA.EFFECTIVE_DATE) + 1',
'Date Paid for previous period',
'D',
'Y');
insert_database_item ('PTO_DATE_EARNED_START_DATE',
'MAX(PPA.DATE_EARNED) + 1',
'Date Earned for previous period',
'D',
'Y');
insert_route ('ASG_ORG_ROUTE',
'Route for Assignment Organizations',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('ASG_ORG',
'The Assignment Organization User Entity',
'Y');
insert_database_item ('ASG_ORG_LOCATION',
'loc.location_code',
'Assignment Organization Location',
'T',
'Y');
insert_database_item ('ASG_ORG_TYPE',
'org_type.meaning',
'Assignment Organization Type',
'T',
'Y');
insert_database_item ('ASG_ORG_INT_EXT',
'int_ext.meaning',
'Assignment Organization Internal External Flag',
'T',
'Y');
insert_route ('ASG_POS_ROUTE',
'Route for Assignment Positions',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('ASG_POS',
'The Assignment Position User Entity',
'Y');
insert_database_item ('ASG_POS_LOCATION',
'loc.location_code',
'Assignment Position Location',
'T',
'Y');
insert_database_item ('ASG_POS_FREQUENCY',
'frequency.meaning',
'Assignment Position Frequency',
'T',
'Y');
insert_database_item ('ASG_POS_WORKING_HOURS',
'target.working_hours',
'Assignment Position Working Hours',
'N',
'Y');
insert_database_item ('ASG_POS_PROBATION_PERIOD',
'target.probation_period',
'Assignment Position Probation Period',
'T',
'Y');
insert_database_item ('ASG_POS_PROBATION_PERIOD_UNITS',
'period.meaning',
'Assignment Position Probation Period Units',
'T',
'Y');
insert_database_item ('ASG_POS_FTE',
'target.fte',
'Assignment Position FTE',
'T',
'Y');
insert_route ('SEH_ASSIGNMENT_DETAILS',
'Assignment Details',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('SEH_ASSIGNMENT_DETAILS',
'Assignment Details',
'Y');
insert_database_item ('ASG_WORK_AT_HOME',
'ASSIGN.work_at_home',
'The work at home code for an assignment',
'T',
'Y');
insert_route ('HRI_ASG_DETAILS',
'HRI Assignment only',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('HRI_ASG_DETAILS',
'HRI Assignment Details',
'Y');
insert_database_item ('HRI_ASG_PRIMARY_CODE',
'asg.primary_flag',
'Primary Assignment Code',
'T',
'N');
insert_database_item ('HRI_ASG_EMPLOYMENT_CATEGORY_CODE',
'asg.employment_category',
'Employment Category Code',
'T',
'Y');
insert_database_item ('HRI_ASG_FREQ_CODE',
'asg.frequency',
'Assignment Working Hours Frequency Code',
'T',
'Y');
insert_database_item ('HRI_ASG_HOURS',
'asg.normal_hours',
'Assignment Normal Working Hours',
'N',
'Y');
insert_route ('HRI_ASG_INHRTD_WRK_CNDTNS',
'HRI Assignment Inherited Working Conditions',
l_text);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
insert_user_entity ('HRI_ASG_INHRTD_WRK_CNDTNS',
'HRI Assignment Inherited Working Conditions',
'Y');
insert_database_item ('HRI_ASG_FULL_TIME_HOURS',
'NVL(hpf.working_hours,NVL(oi_org.org_information3,oi_bus.org_information3))',
'Full Time Working Hours',
'N',
'Y');
insert_database_item ('HRI_ASG_FULL_TIME_FREQ',
'NVL(hpf.frequency,NVL(oi_org.org_information4,oi_bus.org_information4))',
'Full Time Frequency',
'T',
'N');
insert_route ('LAST_REGULAR_PAYMENT_PERIOD',
'Last Regular Payment Period',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('LAST_REGULAR_PAYMENT_PERIOD',
'Last Regular Payment Period',
'Y');
insert_database_item ('LAST_REG_PAYMENT_PERIOD',
'nvl(max(period_num), 1)',
'Last Regular Payment Period',
'N',
'N');
(select min (tp.regular_payment_date)
from pay_payroll_actions ppa,
per_time_periods tp
where ppa.payroll_action_id = &B1
and tp.payroll_id = ppa.payroll_id
and to_char(tp.regular_payment_date,''YYYY'') =
to_char(ppa.effective_date,''YYYY'')))';
insert_route ('LAST_REG_PAYMENT_PERIOD_START_DATE',
'Last Regular Payment Period Start Date',
l_text);
insert_route_context_usages (l_payroll_action_id_context_id, 1);
insert_user_entity ('LAST_REG_PAYMENT_PERIOD_START_DATE',
'Last Regular Payment Period Start Date',
'Y');
insert_database_item ('LAST_REG_PAYMENT_PERIOD_START_DATE',
'Max(target.START_DATE)',
'Last Regular Payment Period Start Date',
'D',
'N');
insert_route ('ASSIGNMENT_ACTION_DATES_ROUTE',
'Assignment Action Start Date',
l_text);
insert_route_context_usages (l_assign_action_id_context_id, 1);
insert_user_entity ('ASSIGNMENT_ACTION_DATES_UENT',
'Entity for the assignment Action Dates',
'Y');
insert_database_item ('ASSIGNMENT_ACTION_START_DATE',
'target.START_DATE',
'Assignment Action Start Date',
'D',
'Y');
insert_database_item ('ASSIGNMENT_ACTION_END_DATE',
'target.END_DATE',
'Assignment Action End Date',
'D',
'Y');
end insert_routes_db_items;
PROCEDURE insert_functions is
l_text long;
select ff_functions_s.nextval
into l_function_id
from dual;
insert into ff_functions
(FUNCTION_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,CLASS
,NAME
,ALIAS_NAME
,DATA_TYPE
,DEFINITION
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,DESCRIPTION
)
values(l_function_id
, null
, null
, 'E'
, p_name
, p_alias
, p_type
, p_definition
, sysdate
, -1
, -1
, -1
, sysdate
, p_description
);
insert into ff_function_parameters
(FUNCTION_ID
,SEQUENCE_NUMBER
,CLASS
,CONTINUING_PARAMETER
,DATA_TYPE
,NAME
,OPTIONAL
)
values
(p_function_id
,p_sequence_no
,'I'
,'N'
,p_data_type
,p_name
,'N'
);
select context_id
into l_context_id
from ff_contexts
where context_name = p_context_name;
insert into ff_function_context_usages
(FUNCTION_ID
,SEQUENCE_NUMBER
,CONTEXT_ID
)
values
(p_function_id
,p_sequence_no
,l_context_id
);
hr_utility.set_location ('hrstrdbi.insert_functions' , 10);
hr_utility.set_location ('hrstrdbi.insert_functions' , 11);
hr_utility.set_location ('hrstrdbi.insert_functions' , 12);
hr_utility.set_location ('hrstrdbi.insert_functions' , 10);
hr_utility.set_location ('hrstrdbi.insert_functions' , 11);
hr_utility.set_location ('hrstrdbi.insert_functions', 12);
hr_utility.set_location ('hrstrdbi.insert_functions', 13);
hr_utility.set_location ('hrstrdbi.insert_functions', 14);
hr_utility.set_location ('hrstrdbi.insert_functions', 15);
hr_utility.set_location ('hrstrdbi.insert_functions', 16);
hr_utility.set_location ('hrstrdbi.insert_functions', 17);
hr_utility.set_location ('hrstrdbi.insert_functions', 15);
hr_utility.set_location ('hrstrdbi.insert_functions', 16);
hr_utility.set_location ('hrstrdbi.insert_functions', 17);
hr_utility.set_location ('hrstrdbi.insert_functions', 18);
hr_utility.set_location ('hrstrdbi.insert_functions', 19);
hr_utility.set_location ('hrstrdbi.insert_functions', 20);
hr_utility.set_location ('hrstrdbi.insert_functions', 21);
end insert_functions;
PROCEDURE insert_formula is
l_text long;
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'TEMPLATE_BIS_DAYS_TO_HOURS',
'Template conversion from Days to Hours',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 114);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'TEMPLATE_FTE',
'Calculates Assignment Full Time Equivalent Value',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 115);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'TEMPLATE_HEAD',
'Calculates Assignment Headcount Value',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 116);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'EXAMPLE_BIS_OT_BAND1',
'Calculate Overtime',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 117);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Carryover';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_PAYROLL_CARRYOVER',
'Seeded carry over payroll formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 118);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Subformula';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_PAYROLL_PERIOD_ACCRUAL',
'Seeded looping payroll formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 119);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_PAYROLL_CALCULATION',
'Seeded top level payroll formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 120);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Carryover';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_ROLLING_CARRYOVER',
'Seeded carry over rolling formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 121);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Subformula';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_ROLLING_PERIOD_ACCRUAL',
'Seeded looping rolling formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 122);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_ROLLING_ACCRUAL',
'Seeded top level rolling formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 123);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Carryover';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_SIMPLE_CARRYOVER',
'Seeded simple carryover formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 124);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Subformula';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_SIMPLE_PERIOD_ACCRUAL',
'Seeded simple looping formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 125);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_SIMPLE_MULTIPLIER',
'Seeded simple top level formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 126);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Ineligibility';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_INELIGIBILITY_CALCULATION',
'Seeded formula allowing paymix to cope with PTO accruals upgrade',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 127);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'User Table Validation';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'CHECK_RATE_TYPE',
'Checks that conversion type exists',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 128);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'TEMPLATE_BIS_TRAINING_CONVERT_DURATION',
'Seeded formula to Calculate Training Duration',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 128);
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'TEMPLATE_ABSENCE_DURATION',
'formula template for absence duration calculation',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 129);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'CALC_GROSSUP_PAY_VALUE',
'This formula is used in the grossup calculations',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 130);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Net to Gross';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'DEFAULT_GROSSUP',
'This formula is used in the grossup calculations',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 131);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'QH_ASSIGNMENT_NAME',
'This formula is used to get a job assignment_name',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 132);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Element Skip';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_ORACLE_SKIP_RULE',
'Skips PTO elements that have already been processed in the same period',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 133);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_PAYROLL_BALANCE_CALCULATION',
'Seeded top level payroll formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 134);
but must be amended, according to the documentaion, by inserting the
name of the database item which was created with your defined balance.
This defined balance should have been created immediately after the
accrual plan.
---------------------------------------------------------------------*/
DEFAULT FOR ACP_INELIGIBILITY_PERIOD_TYPE IS ''CM''
DEFAULT FOR ACP_INELIGIBILITY_PERIOD_LENGTH IS 0
DEFAULT FOR ACP_CONTINUOUS_SERVICE_DATE IS ''4712/12/31 00:00:00'' (date)
DEFAULT FOR ACP_ENROLLMENT_END_DATE IS ''4712/12/31 00:00:00'' (date)
DEFAULT FOR ACP_TERMINATION_DATE IS ''4712/12/31 00:00:00'' (date)
DEFAULT FOR ACP_ENROLLMENT_START_DATE IS ''4712/12/31 00:00:00'' (date)
DEFAULT FOR ACP_SERVICE_START_DATE IS ''4712/12/31 00:00:00'' (date)
default for Accrual_Start_Date is ''4712/12/31 00:00:00'' (date)
default for Accrual_Latest_Balance is 0
INPUTS ARE
Calculation_Date (date),
Accrual_Start_Date (date),
Accrual_Latest_Balance
E = SET_NUMBER(''CEILING'', 20)
E = SET_NUMBER(''ACCRUAL_RATE'', 2)
Accruing_Frequency = ''M'' /* Month */
Accruing_Multiplier = 1
E = SET_TEXT(''ACCRUING_FREQUENCY'', Accruing_Frequency)
E = SET_NUMBER(''ACCRUING_MULTIPLIER'', Accruing_Multiplier)
Beginning_Of_Calculation_Year = to_date(''0106''||to_char(Calculation_Date,''YYYY''),
''DDMMYYYY'')
IF (Beginning_Of_Calculation_Year > Calculation_Date) THEN
(
Beginning_of_Calculation_Year = ADD_MONTHS(Beginning_Of_Calculation_Year, -12)
)
IF Accrual_Start_Date < Beginning_Of_Calculation_Year THEN
(
Accrual_Start_Date = Beginning_Of_Calculation_Year
)
E = SET_DATE(''BEGINNING_OF_CALCULATION_YEAR'', Beginning_Of_Calculation_Year)
E = GET_PERIOD_DATES(Beginning_of_Calculation_Year,
Accruing_Frequency,
Beginning_Of_Calculation_Year,
Accruing_Multiplier)
First_Period_SD = get_date(''PERIOD_START_DATE'')
First_Period_ED = get_date(''PERIOD_END_DATE'')
/* ------------------------------------------------------------------------
Set the Calculation_Date to the Termination Date if not null
-------------------------------------------------------------------------- */
IF NOT (ACP_TERMINATION_DATE WAS DEFAULTED) OR
NOT (ACP_ENROLLMENT_END_DATE WAS DEFAULTED) THEN
(
Early_End_Date = least(ACP_TERMINATION_DATE, ACP_ENROLLMENT_END_DATE)
IF (Early_End_Date < First_Period_ED) THEN
(
Total_Accrued_PTO = 0
E = PUT_MESSAGE(''HR_52794_PTO_FML_ASG_TER'')
)
IF (Early_End_Date < Calculation_Date) THEN
(
Calculation_Date = Early_End_Date
)
)
/* ------------------------------------------------------------------------
Get the last whole period prior to the Calculation Date and ensure that it is within the
Year (if the Calculation Date is the End of a Period then use that period)
------------------------------------------------------------------------ */
E = GET_PERIOD_DATES(Calculation_Date,
Accruing_Frequency,
Beginning_of_Calculation_Year,
Accruing_Multiplier)
Calculation_Period_SD = get_date(''PERIOD_START_DATE'')
Calculation_Period_ED = get_date(''PERIOD_END_DATE'')
IF (Calculation_Date <> Calculation_Period_ED) THEN
(
E = GET_PERIOD_DATES(ADD_DAYS(Calculation_Period_SD,-1),
Accruing_Frequency,
Beginning_of_Calculation_Year,
Accruing_Multiplier)
Calculation_Period_SD = get_date(''PERIOD_START_DATE'')
Calculation_Period_ED = get_date(''PERIOD_END_DATE'')
)
If (Calculation_Period_ED < First_Period_ED) THEN
(
Total_Accrued_PTO = 0
E = PUT_MESSAGE(''HR_52795_PTO_FML_CALC_DATE'')
)
/* ------------------------------------------------------------------------
Set the Continuous Service Global Variable, whilst also
ensuring that the continuous service date is before the Calculation Period
------------------------------------------------------------------------ */
IF (ACP_CONTINUOUS_SERVICE_DATE WAS DEFAULTED) THEN
(
E = set_date(''CONTINUOUS_SERVICE_DATE'', ACP_SERVICE_START_DATE)
)
ELSE IF(ACP_CONTINUOUS_SERVICE_DATE > Calculation_Period_SD) THEN
(
Total_Accrued_PTO = 0
E = PUT_MESSAGE(''HR_52796_PTO_FML_CSD'')
E = set_date(''CONTINUOUS_SERVICE_DATE'', ACP_CONTINUOUS_SERVICE_DATE)
)
ELSE
(
E = set_date(''CONTINUOUS_SERVICE_DATE'', ACP_CONTINUOUS_SERVICE_DATE)
)
Continuous_Service_Date = get_date(''CONTINUOUS_SERVICE_DATE'')
First_Eligible_To_Accrue_Date = Continuous_Service_Date
/*------------------------------------------------------------------------
Determine the date on which accrued PTo may first be registered, i.e the date on which the
Ineligibility Period expires
------------------------------------------------------------------------ */
Accrual_Ineligibility_Expired_Date = First_Eligible_To_Accrue_Date
IF (ACP_INELIGIBILITY_PERIOD_LENGTH > 0) THEN
(
IF ACP_INELIGIBILITY_PERIOD_TYPE = ''BM'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*2)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''F'' THEN
(
Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*14)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''CM'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''LM'' THEN
(
Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*28)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''Q'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*3)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''SM'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH/2)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''SY'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*6)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''W'' THEN
(
Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*7)
)
ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = ''Y'' THEN
(
Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,
ACP_INELIGIBILITY_PERIOD_LENGTH*12)
)
IF Accrual_Ineligibility_Expired_Date > First_Eligible_To_Accrue_Date
AND Calculation_Date < Accrual_Ineligibility_Expired_Date THEN
(
First_Eligible_To_Accrue_Date = Accrual_Ineligibility_Expired_Date
)
)
/* ------------------------------------------------------------------------
Get the first full period following the First_Eligible_To_Accrue_Date
(if it falls on the beginning of the period then use that period)
------------------------------------------------------------------------- */
Latest_Balance = 0
IF (not Accrual_Start_Date was defaulted) AND
((Calculation_Date < Accrual_Ineligibility_Expired_Date) OR
(Accrual_Start_Date > Accrual_Ineligibility_Expired_Date)) THEN
(
Adjusted_Start_Date = Get_Start_Date(Accrual_Start_Date,
Beginning_Of_Calculation_Year)
IF (Adjusted_Start_Date < Accrual_Start_Date) THEN
(
Process_Full_Term = ''Y''
)
ELSE
(
Process_Full_Term = ''N''
)
)
ELSE
(
Process_Full_Term = ''Y''
)
IF (Process_Full_Term = ''N'') AND
(Accrual_Start_Date >= First_Eligible_To_Accrue_Date) THEN
(
E = GET_PERIOD_DATES(Adjusted_Start_Date,
Accruing_Frequency,
Beginning_Of_Calculation_Year,
Accruing_Multiplier)
First_Eligible_To_Accrue_Period_SD = get_date(''PERIOD_START_DATE'')
First_Eligible_To_Accrue_Period_ED = get_date(''PERIOD_END_DATE'')
Latest_Balance = Accrual_Latest_Balance
Effective_Start_Date = Accrual_Start_Date
)
ELSE IF First_Eligible_To_Accrue_Date > Beginning_Of_Calculation_Year THEN
(
IF (not Accrual_Start_Date was defaulted) THEN
(
Latest_Balance = Accrual_Latest_Balance
)
ELSE
(
Latest_Balance = 0
)
E = GET_PERIOD_DATES(First_Eligible_To_Accrue_Date,
Accruing_Frequency,
Beginning_Of_Calculation_Year,
Accruing_Multiplier)
First_Eligible_To_Accrue_Period_SD = get_date(''PERIOD_START_DATE'')
First_Eligible_To_Accrue_Period_ED = get_date(''PERIOD_END_DATE'')
IF First_Eligible_To_Accrue_Date <> First_Eligible_To_Accrue_Period_SD THEN
(
E = GET_PERIOD_DATES(add_days(First_Eligible_To_Accrue_Period_ED,1),
Accruing_Frequency,
Beginning_Of_Calculation_Year,
Accruing_Multiplier)
First_Eligible_To_Accrue_Period_SD = get_date(''PERIOD_START_DATE'')
First_Eligible_To_Accrue_Period_ED = get_date(''PERIOD_END_DATE'')
)
IF (First_Eligible_To_Accrue_Period_SD > Calculation_Period_ED) THEN
(
Total_Accrued_PTO = 0
E = PUT_MESSAGE(''HR_52793_PTO_FML_ASG_INELIG'')
)
Effective_Start_Date = First_Eligible_To_Accrue_Date
)
ELSE
(
IF (not Accrual_Start_Date was defaulted) THEN
(
Latest_Balance = Accrual_Latest_Balance
)
ELSE
(
Latest_Balance = 0
)
First_Eligible_To_Accrue_Period_SD = First_Period_SD
First_Eligible_To_Accrue_Period_ED = First_Period_ED
Effective_Start_Date = Beginning_Of_Calculation_Year
)
/* ------------------------------------------------------------------------
Determine the date on which PTO actually starts accruing based on Hire Date,
Continuous Service Date and plan Enrollment Start Date. Remember, we have already determined
whether to user hire date or CSD earlier in the formula.
If this date is after the 1st period and the fisrt eligible date then establish the first full period
after this date (if the Actual Start Date falls on the beginning of a period then use this period)
------------------------------------------------------------------------ */
IF Continuous_Service_date = ACP_CONTINUOUS_SERVICE_DATE THEN
(
Actual_Accrual_Start_Date = Continuous_service_Date
)
ELSE
(
Actual_Accrual_Start_Date = greatest(Continuous_Service_Date,
ACP_ENROLLMENT_START_DATE,
First_Period_SD)
)
/* -------------------------------------------------------------------------
Determine the actual start of the accrual calculation
-------------------------------------------------------------------------*/
IF (Actual_Accrual_Start_Date > First_Period_SD AND
Actual_Accrual_Start_Date > First_Eligible_To_Accrue_Period_SD) THEN
(
E = GET_PERIOD_DATES(Actual_Accrual_Start_Date,
Accruing_Frequency,
Beginning_Of_Calculation_Year,
Accruing_Multiplier)
Accrual_Start_Period_SD = get_date(''PERIOD_START_DATE'')
Accrual_Start_Period_ED = get_date(''PERIOD_END_DATE'')
IF Actual_Accrual_Start_Date > Accrual_Start_Period_SD THEN
(
E = GET_PERIOD_DATES(add_days(Accrual_Start_Period_ED,1),
Accruing_Frequency,
Beginning_of_Calculation_Year,
Accruing_Multiplier)
Accrual_Start_Period_SD = get_date(''PERIOD_START_DATE'')
Accrual_Start_Period_ED = get_date(''PERIOD_END_DATE'')
)
/* -----------------------------------------------------------------
If the Actual Acrual Period is after the Calculation Period then end the processing.
----------------------------------------------------------------- */
IF (Accrual_Start_Period_SD > Calculation_Period_ED) THEN
(
Total_Accrued_PTO = 0
E = PUT_MESSAGE(''HR_52797_PTO_FML_ACT_ACCRUAL'')
)
)
ELSE IF (First_Eligible_To_Accrue_Period_SD > First_Period_SD) THEN
(
Accrual_Start_Period_SD = First_Eligible_To_Accrue_Period_SD
Accrual_Start_Period_ED = First_Eligible_To_Accrue_Period_ED
)
ELSE
(
Accrual_Start_Period_SD = First_Period_SD
Accrual_Start_Period_ED = First_Period_ED
)
/* -------------------------------------------------------------------
Now set up the information that will be used in when looping through the periods
--------------------------------------------------------------------- */
IF Calculation_Period_ED >= Accrual_Start_Period_ED THEN
(
E = set_date(''PERIOD_SD'',Accrual_Start_Period_SD)
E = set_date(''PERIOD_ED'',Accrual_Start_Period_ED)
E = set_date(''LAST_PERIOD_SD'',Calculation_Period_SD)
E = set_date(''LAST_PERIOD_ED'',Calculation_Period_ED)
IF (Process_Full_Term = ''N'') THEN
(
E = set_number(''TOTAL_ACCRUED_PTO'', Latest_Balance)
)
ELSE
(
E = set_number(''TOTAL_ACCRUED_PTO'', 0)
)
E = LOOP_CONTROL(''PTO_SIMPLE_PERIOD_ACCRUAL'')
Total_Accrued_PTO = get_number(''TOTAL_ACCRUED_PTO'') - Latest_Balance
)
ELSE
(
Total_Accrued_PTO = 0
)
IF Accrual_Start_Period_SD <= Calculation_Period_SD THEN
(
Accrual_end_date = Calculation_Period_ED
)
IF Process_Full_Term = ''Y'' AND
Effective_Start_Date > Actual_Accrual_Start_Date THEN
(
Effective_Start_Date = Actual_Accrual_Start_Date
)
Effective_End_Date = Calculation_Date
IF Effective_Start_Date >= Effective_End_Date THEN
(
Effective_Start_Date = Effective_End_Date
)
RETURN Total_Accrued_PTO, Effective_start_date, Effective_end_date, Accrual_end_date
';
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_SIMPLE_BALANCE_MULTIPLIER',
'Seeded top level formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 135);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_HD_ANNIVERSARY_BALANCE',
'Seeded simple top level formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 136);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Subformula';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_HD_ANNIVERSARY_PERIOD_ACCRUAL',
'Seeded hire date anniversary looping formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 137);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Accrual Carryover';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_HD_ANNIVERSARY_CARRYOVER',
'Seeded simple carryover formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 138);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PTO_TAGGING_FORMULA',
'Seeded top level payroll formula for PTO accruals',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 139);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'CAGR';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01-01-1951','DD-MM-YYYY'),
to_date('31-12-4712','DD-MM-YYYY'),
NULL,
NULL,
l_ftype_id,
'HR_CAGR_PYS_TEMPLATE',
'HR_CAGR_PYS_TEMPLATE',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 140);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'CAGR';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01-01-1951','DD-MM-YYYY'),
to_date('31-12-4712','DD-MM-YYYY'),
NULL,
NULL,
l_ftype_id,
'HR_CAGR_TEMPLATE',
'HR_CAGR_TEMPLATE',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 141);
select formula_type_id
into l_ftype_id
from ff_formula_types
where formula_type_name = 'Promotion';
INSERT INTO ff_formulas_f
(formula_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
formula_type_id,
formula_name,
description,
formula_text,
sticky_flag)
VALUES
(ff_formulas_s.nextval,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
NULL,
NULL,
l_ftype_id,
'PROMOTION_TEMPLATE',
'Seeded Promotions Formula',
l_text,
NULL);
hr_utility.set_location ('hrstrdbi.insert_formula', 142);
end insert_formula;
procedure insert_user_tables is
--
l_formula_id number;
hr_utility.set_location ('hrstrdbi.insert_user_tables', 1);
select formula_id into l_formula_id
FROM ff_formulas_f
WHERE formula_name = 'CHECK_RATE_TYPE'
AND EFFECTIVE_START_DATE = to_date('01-01-0001', 'DD-MM-YYYY')
AND EFFECTIVE_END_DATE = to_date('31-12-4712', 'DD-MM-YYYY');
insert into pay_user_tables (
USER_TABLE_ID
, BUSINESS_GROUP_ID
, LEGISLATION_CODE
, RANGE_OR_MATCH
, USER_KEY_UNITS
, USER_TABLE_NAME
, LEGISLATION_SUBGROUP
, USER_ROW_TITLE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
)
select pay_user_tables_s.nextval
, NULL
, NULL
, 'M'
, 'T'
, 'EXCHANGE_RATE_TYPES'
, NULL
, 'Processing Type'
, trunc(sysdate)
, NULL
, NULL
, NULL
, trunc(sysdate)
from sys.dual;
insert into pay_user_columns (
USER_COLUMN_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,USER_TABLE_ID
,FORMULA_ID
,USER_COLUMN_NAME
,LEGISLATION_SUBGROUP
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE )
select pay_user_columns_s.nextval
,''
,NULL
,udt.user_table_id
,l_formula_id
,'Conversion Rate Type'
,''
,sysdate
,1
,1
,1
,sysdate
from pay_user_tables udt
where udt.user_table_name = 'EXCHANGE_RATE_TYPES';
insert into pay_user_rows (
USER_ROW_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,USER_TABLE_ID
,ROW_LOW_RANGE_OR_NAME
,LEGISLATION_SUBGROUP
,ROW_HIGH_RANGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE )
select pay_user_rows_s.nextval
, to_date('01-01-0001', 'DD-MM-YYYY')
, to_date('31-12-4712', 'DD-MM-YYYY')
,NULL
,NULL
,udt.user_table_id
,'BIS'
,''
,''
,''
,1
,1
,1
,sysdate
from pay_user_tables udt
where udt.user_table_name = 'EXCHANGE_RATE_TYPES';
insert into pay_user_rows (
USER_ROW_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,USER_TABLE_ID
,ROW_LOW_RANGE_OR_NAME
,LEGISLATION_SUBGROUP
,ROW_HIGH_RANGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE )
select pay_user_rows_s.nextval
, to_date('01-01-0001', 'DD-MM-YYYY')
, to_date('31-12-4712', 'DD-MM-YYYY')
,NULL
,NULL
,udt.user_table_id
,'HRMS'
,''
,''
,''
,1
,1
,1
,sysdate
from pay_user_tables udt
where udt.user_table_name = 'EXCHANGE_RATE_TYPES';
insert into pay_user_rows (
USER_ROW_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,USER_TABLE_ID
,ROW_LOW_RANGE_OR_NAME
,LEGISLATION_SUBGROUP
,ROW_HIGH_RANGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE )
select pay_user_rows_s.nextval
, to_date('01-01-0001', 'DD-MM-YYYY')
, to_date('31-12-4712', 'DD-MM-YYYY')
,NULL
,NULL
,udt.user_table_id
,'PAY'
,''
,''
,''
,1
,1
,1
,sysdate
from pay_user_tables udt
where udt.user_table_name = 'EXCHANGE_RATE_TYPES';
hr_utility.set_location ('hrstrdbi.insert_user_tables', 2);
end insert_user_tables;
procedure insert_monetary_units is
TYPE eur_info_type is RECORD (name VARCHAR2(150), value NUMBER);
insert into pay_monetary_units
(MONETARY_UNIT_ID,
CURRENCY_CODE,
MONETARY_UNIT_NAME,
RELATIVE_VALUE,
COMMENTS,
CREATION_DATE)
values
(pay_monetary_units_s.nextval,
'EUR',
eur_info(i).name,
eur_info(i).value,
NULL,
sysdate);
end insert_monetary_units;
insert_context;
insert_routes_db_items;
insert_functions;
insert_formula;
insert_user_tables;
insert_monetary_units;