The following lines contain the word 'select', 'insert', 'update' or 'delete':
mwcallag 13-OCT-1994 - Route PAYROLL_ACTION_FLEXFIELD_ROUTE deleted.
rfine 05-OCT-1994 - Renamed package to pay_dbi_startup_pkg.
mwcallag 29-APR-1994 Route INPUT_VALUE_ENTRY_LEVEL tuned.
mwcallag 07-DEC-1993 - G291 Change to Legal Company route, now uses the
passed in context parameter.
mwcallag 01-DEC-1993 - inserts for User Defined Tables added.
mwcallag 03-NOV-1993 - Assignment Developer Descriptive flex addded.
mwcallag 02-NOV-1993 - Join to benefit_classifications added to
element_type_at_type_level route.
mwcallag 01-SEP-1993 - Date paid routes added for element types and
input values.
mwcallag 12-AUG-1993 - Minor change to Legal Company route.
mwcallag 11-AUG-1993 - Organization Payment Methods DF, External Account
Keyflex, and Legal company DF routes added.
mwcallag 09-AUG-1993 - More routes for Descriptive flexfields added.
mwcallag 03-AUG-1993 - Organization Developer DF and SCL routes added.
mwcallag 21-JUN-1993 - date earned removed from absence type route.
mwcallag 24-MAY-1993 - grade rate route shortened following removal of
rate_type database item.
mwcallag 07-MAY-1993 - spine and key flex routes added.
mwcallag 30-APR-1993 - absence and descriptive flex routes added.
mwcallag 27-APR-1993 - created.
*/
--
PROCEDURE create_dbi_startup 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('pay_dbi_startup_pkg.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_context_usages
(
p_context_id in number,
p_sequence_no in number
) is
begin
hr_utility.set_location('pay_dbi_startup_pkg.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;
select context_id
into l_date_earned_context_id
from ff_contexts
where context_name = 'DATE_EARNED';
select context_id
into l_assign_id_context_id
from ff_contexts
where context_name = 'ASSIGNMENT_ID';
select context_id
into l_payroll_context_id
from ff_contexts
where context_name = 'PAYROLL_ID';
select context_id
into l_payroll_action_context_id
from ff_contexts
where context_name = 'PAYROLL_ACTION_ID';
select context_id
into l_org_pay_method_id
from ff_contexts
where context_name = 'ORG_PAY_METHOD_ID';
select context_id
into l_per_pay_method_id
from ff_contexts
where context_name = 'PER_PAY_METHOD_ID';
select context_id
into l_tax_unit_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select context_id
into l_assignment_action_id
from ff_contexts
where context_name = 'ASSIGNMENT_ACTION_ID';
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)
select ff_routes_s.nextval,
'ELEMENT_TYPE_AT_TYPE_LEVEL',
'N',
'simple element type route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Element Type ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 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)
select ff_routes_s.nextval,
'ELEMENT_TYPE_AT_TYPE_LEVEL_DP',
'N',
'route for date paid element type at type level',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Element Type ID', 'N', 1);
(SELECT 1
FROM pay_assignment_link_usages_f PAL
WHERE PAL.assignment_id = &B2
AND PAL.element_link_id = ELINK.element_link_id
AND &B1 BETWEEN PAL.effective_start_date
AND PAL.effective_end_date))
AND QULOOK_LINK.lookup_code (+)= ELINK.qualifying_units
AND QULOOK_LINK.lookup_type (+)= ''QUALIFYING_UNITS''
AND QULOOK_LINK.application_id (+)= 800
AND QULOOK_TYPE.lookup_code (+)= ETYPE.qualifying_units
AND QULOOK_TYPE.lookup_type (+)= ''QUALIFYING_UNITS''
AND QULOOK_TYPE.application_id (+)= 800
AND SLLOOK_LINK.lookup_code (+)= ELINK.standard_link_flag
AND SLLOOK_LINK.lookup_type (+)= ''YES_NO''
AND SLLOOK_LINK.application_id (+)= 800
AND SLLOOK_TYPE.lookup_code (+)= ETYPE.standard_link_flag
AND SLLOOK_TYPE.lookup_type (+)= ''YES_NO''
AND SLLOOK_TYPE.application_id (+)= 800
AND ELINK.costable_type = CTLOOK.lookup_code
AND CTLOOK.application_id = 800
AND CTLOOK.lookup_type = ''COSTABLE_TYPE''';
select ff_routes_s.nextval
into l_temp
from dual;
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.currval,
'ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL',
'N',
'element type information with partial matching to the element link table',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert_route_parameters ('Element Type ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
(SELECT 1
FROM pay_assignment_link_usages_f PAL
WHERE PAL.assignment_id = &B1
AND PAL.element_link_id = ELINK.element_link_id
AND SES.effective_date BETWEEN PAL.effective_start_date
AND PAL.effective_end_date))
AND QULOOK_LINK.lookup_code (+)= ELINK.qualifying_units
AND QULOOK_LINK.lookup_type (+)= ''QUALIFYING_UNITS''
AND QULOOK_LINK.application_id (+)= 800
AND QULOOK_TYPE.lookup_code (+)= ETYPE.qualifying_units
AND QULOOK_TYPE.lookup_type (+)= ''QUALIFYING_UNITS''
AND QULOOK_TYPE.application_id (+)= 800
AND SLLOOK_LINK.lookup_code (+)= ELINK.standard_link_flag
AND SLLOOK_LINK.lookup_type (+)= ''YES_NO''
AND SLLOOK_LINK.application_id (+)= 800
AND SLLOOK_TYPE.lookup_code (+)= ETYPE.standard_link_flag
AND SLLOOK_TYPE.lookup_type (+)= ''YES_NO''
AND SLLOOK_TYPE.application_id (+)= 800
AND ELINK.costable_type = CTLOOK.lookup_code
AND CTLOOK.application_id = 800
AND CTLOOK.lookup_type = ''COSTABLE_TYPE''';
select ff_routes_s.nextval
into l_temp
from dual;
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.currval,
'ELEMENT_TYPE_AT_ASSIGNMENT_LEVEL_DP',
'N',
'Date paid element type info with partial matching to the element link table',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert_route_parameters ('Element Type ID', 'N', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES',
'N',
'route to element entry table for given assignment and element type',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Element Type ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'ELEMENT_TYPE_COUNT_OF_ELEMENT_ENTRIES_DP',
'N',
'route for DP, element entry table for given assignment and element type',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Element Type ID', 'N', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'INPUT_VALUE_FROM_INPUT_VALUE_TABLE',
'N',
'route to input value table',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Input value ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 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)
select ff_routes_s.nextval,
'INPUT_VALUE_FROM_INPUT_VALUE_TABLE_DP',
'N',
'Date paid route to input value table',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Input value ID', 'N', 1);
(SELECT 1
FROM pay_assignment_link_usages_f PAL
WHERE PAL.assignment_id = &B2
AND PAL.element_link_id = ELINK.element_link_id
AND &B1 BETWEEN PAL.effective_start_date
AND PAL.effective_end_date))
AND ELINK.element_link_id = LIV.element_link_id
AND &B1 BETWEEN LIV.effective_start_date
AND LIV.effective_end_date';
select ff_routes_s.nextval
into l_temp
from dual;
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.currval,
'INPUT_VALUE_USING_PARTIAL_MATCHING',
'N',
'route for input value for given assignment id',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert_route_parameters ('Input value ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
(SELECT 1
FROM pay_assignment_link_usages_f PAL
WHERE PAL.assignment_id = &B1
AND PAL.element_link_id = ELINK.element_link_id
AND SES.effective_date BETWEEN PAL.effective_start_date
AND PAL.effective_end_date))
AND ELINK.element_link_id = LIV.element_link_id
AND SES.effective_date BETWEEN LIV.effective_start_date
AND LIV.effective_end_date';
select ff_routes_s.nextval
into l_temp
from dual;
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.currval,
'INPUT_VALUE_USING_PARTIAL_MATCHING_DP',
'N',
'route for input value for given assignment id',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert_route_parameters ('Input value ID', 'N', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'INPUT_VALUE_ENTRY_LEVEL',
'N',
'route for input value to element entry level',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Input value ID', 'N', 1);
insert_route_parameters ('Element Type ID', 'N', 2);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'INPUT_VALUE_ENTRY_LEVEL_DP',
'N',
'route for input value to element entry level',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Input value ID', 'N', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'GRADE_RATE_ROUTE',
'N',
'route for grade rates',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Grade Rate ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'PEOPLE_FLEXFIELD_ROUTE',
'N',
'people group flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'PAYROLL_FLEXFIELD_ROUTE',
'N',
'payroll flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_payroll_context_id, 1);
insert_route_context_usages (l_date_earned_context_id, 2);
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)
select ff_routes_s.nextval,
'ASSIGNMENT_FLEXFIELD_ROUTE',
'N',
'assignment flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'GRADE_FLEXFIELD_ROUTE',
'N',
'grade flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
(select max (absence_attendance_id)
from per_absence_attendances
where person_id = ASSIGN.person_id
and date_start <= &B1
)';
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)
select ff_routes_s.nextval,
'ABSENCE_DESC_FLEX_ROUTE',
'N',
'Absence flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
(select max (absence_attendance_id)
from per_absence_attendances
where person_id = ASSIGN.person_id
and date_start <= &B1
)';
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)
select ff_routes_s.nextval,
'ABSENCE_TYPE_DESC_FLEX_ROUTE',
'N',
'Absence type flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'EMPLOYEE_ADDRESSES_DESC_FLEX_ROUTE',
'N',
'Employee Addresses flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
(select max (event_id)
from per_events
where assignment_id = &B2
and &B1 between date_start
and nvl (date_end, to_date (''4712/12/31'',''YYYY/MM/DD''))
)';
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)
select ff_routes_s.nextval,
'EVENTS_DESC_FLEX_ROUTE',
'N',
'Events flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'JOBS_DESC_FLEX_ROUTE',
'N',
'Job flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'CONTACTS_DESC_FLEX_ROUTE',
'N',
'Contacts flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'PERIODS_OF_SERVICE_DESC_FLEX_ROUTE',
'N',
'periods of service flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'RECRUITMENT_ACTIVITIES_DESC_FLEX_ROUTE',
'N',
'recruitment activities flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'POSITION_DESC_FLEX_ROUTE',
'N',
'Positions flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'APPLICATIONS_DESC_FLEX_ROUTE',
'N',
'Applications flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'ORGANIZATION_DESC_FLEX_ROUTE',
'N',
'Organization flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'DEVELOPER_ORG_DESC_FLEX_ROUTE',
'N',
'route for Organization Developer Descriptive Flexfield',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Organization Information Context', 'T', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'DEVELOPER_ASS_DESC_FLEX_ROUTE',
'N',
'route for Assignment Developer Descriptive Flexfield',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Assignment Information Context', 'T', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'ORG_PAY_METHOD_DESC_FLEX_ROUTE',
'N',
'route for Organization Payment Method Descriptive Flex',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Payment Type id', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_org_pay_method_id, 2);
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)
select ff_routes_s.nextval,
'EXT_ACCOUNT_PER_KEYFLEX_ROUTE',
'N',
'route for Personal External Account Keyflex',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_per_pay_method_id, 2);
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)
select ff_routes_s.nextval,
'EXT_ACCOUNT_ORG_KEYFLEX_ROUTE',
'N',
'route for Organization External Account Keyflex',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_org_pay_method_id, 2);
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)
select ff_routes_s.nextval,
'LEGAL_CO_DESC_FLEX_ROUTE',
'N',
'route for Legal Company Descriptive Flex',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Organization Information Context', 'T', 1);
insert_route_context_usages (l_tax_unit_id, 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)
select ff_routes_s.nextval,
'ABSENCE_SUM_OF_ELEMENT_ENTRY_VALUES',
'N',
'absence route to element entry values',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Absence Type ID', 'N', 1);
insert_route_context_usages (l_assign_id_context_id, 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)
select ff_routes_s.nextval,
'SPINE_RATE_ROUTE',
'N',
'route for grade rates',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('Spine Rate ID', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'KEY_FLEXFIELD_ROUTE',
'N',
'key flexfield route',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'SCL_ASS_FLEX_ROUTE',
'N',
'route for SCL assignment level Flexfield',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('ID flex number', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'SCL_PAY_FLEX_ROUTE',
'N',
'route for SCL payroll level Flexfield',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('ID flex number', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'SCL_ORG_FLEX_ROUTE',
'N',
'route for SCL organization level Flexfield',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('ID flex number', 'N', 1);
insert_route_context_usages (l_date_earned_context_id, 1);
insert_route_context_usages (l_assign_id_context_id, 2);
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)
select ff_routes_s.nextval,
'RETROADJ_RUN_BAL_ROUTE',
'N',
'route for Retroadjusted Run To Date Balance',
l_text,
sysdate,
0,
0,
0,
sysdate
from dual;
insert_route_parameters ('ID balance type', 'N', 1);
insert_route_context_usages (l_assignment_action_id, 1);
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)
values(l_function_id,
null,
null,
'U',
'GET_TABLE_VALUE',
null,
'T',
'hruserdt.get_table_value',
sysdate,
0,
0,
0,
sysdate);
select context_id
into l_business_group_id
from ff_contexts
where context_name = 'BUSINESS_GROUP_ID';
insert into ff_function_context_usages
(function_id,
sequence_number,
context_id)
values (l_function_id,
1,
l_business_group_id);
insert into ff_function_parameters
(function_id,
sequence_number,
class,
continuing_parameter,
data_type,
name,
optional)
values (l_function_id,
1,
'I',
'N',
'T',
'table_name',
'N');
insert into ff_function_parameters
(function_id,
sequence_number,
class,
continuing_parameter,
data_type,
name,
optional)
values (l_function_id,
2,
'I',
'N',
'T',
'column_name',
'N');
insert into ff_function_parameters
(function_id,
sequence_number,
class,
continuing_parameter,
data_type,
name,
optional)
values (l_function_id,
3,
'I',
'N',
'T',
'row_value',
'N');
insert into ff_function_parameters
(function_id,
sequence_number,
class,
continuing_parameter,
data_type,
name,
optional)
values (l_function_id,
4,
'I',
'N',
'D',
'effective_date',
'Y');