[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_route_context_usages
(
p_context_id in number,
p_sequence_no in number
) is
begin -- [
hr_utility.set_location('gbstrdbi.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
) is
begin -- [
hr_utility.set_location('gbstrdbi.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,
'US',
ff_routes_s.currval,
'N',
p_user_entity_name ,
0,
'SEH', /* SEH */
p_description,
sysdate,
0,
0,
0,
sysdate);
end insert_user_entity; -- ]
procedure insert_curr_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('gbstrdbi.insert_curr_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_curr_database_item; -- ]
procedure insert_database_item
(
p_user_name in varchar2,
p_user_entity_id in number,
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('gbstrdbi.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,
p_user_entity_id,
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('gbstrdbi.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; -- ]
select context_id
into l_tax_unit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select context_id
into l_context1
from ff_contexts
where context_name = 'PAYROLL_ACTION_ID';
select context_id
into l_context2
from ff_contexts
where context_name = 'TAX_UNIT_ID';
insert_route
('SEH_SQWL_NUM_EMPS_US',
'Derives the number of employees before you run the report',
'pay_assignment_actions paa
where paa.payroll_action_id = B1
and paa.tax_unit_id = B2');
insert_route_context_usages
(l_context1,1);
insert_route_context_usages
(l_context2,2);
insert_user_entity
('SEH_SQWL_NUM_EMPS_US',
'Number of State Employees for a specific Quarter');
insert_curr_database_item
('SQWL_NUM_EMPS_GRE_PACTID',
'count(*)',
'Number of State Employees for a specific Quarter',
'N',
'Y');
select 'Y'
from ff_route_context_usages frcu
where route_id = p_route_id
and context_id = p_context_id;
select context_id
into l_tax_unit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select context_id
into l_assignment_action_context_id
from ff_contexts
where context_name = 'ASSIGNMENT_ACTION_ID';
select context_id
into l_payroll_action_context_id
from ff_contexts
where context_name = 'PAYROLL_ACTION_ID';
select context_id
into l_jurisdiction_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';
hr_utility.trace('selecting the route_id for EMPLOYER_ARCHIVE');
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_context_id,
2 );
hr_utility.trace('inserting ff_routes for EMPLOYER_ARCHIVE');
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,
'EMPLOYER_ARCHIVE',
'N',
'sql to retrieve GRE based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
hr_utility.trace('inserting ff_route_parameters for EMPLOYER_ARCHIVE');
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_ARCHIVE_DATE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_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)
values (ff_routes_s.nextval,
'EMPLOYER_ARCHIVE_DATE',
'N',
'sql to retrieve GRE based date archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_ARCHIVE_NUMBER';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values ( l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_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)
values (ff_routes_s.nextval,
'EMPLOYER_ARCHIVE_NUMBER',
'N',
'sql to retrieve GRE based numeric archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
hr_utility.trace('selecting route id for EMPLOYER_TAX_UNIT_ARCHIVE_DATE');
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_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)
values (ff_routes_s.nextval,
'EMPLOYER_TAX_UNIT_ARCHIVE_DATE',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
hr_utility.trace('selecting route_id for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_context_id,
2 );
hr_utility.trace('inserting ff_routes for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
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,
'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
hr_utility.trace('selecting route_id for EMPLOYER_TAX_UNIT_ARCHIVE');
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE');
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_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)
values (ff_routes_s.nextval,
'EMPLOYER_TAX_UNIT_ARCHIVE',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_jurisdiction_context_id,
3 );
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,
'EMPLOYER_JURSD_ARCHIVE',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_jurisdiction_context_id,
3
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE_DATE';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_jurisdiction_context_id,
3 );
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,
'EMPLOYER_JURSD_ARCHIVE_DATE',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_jurisdiction_context_id,
3
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE_NUMBER';
update ff_routes
set text = l_text
where route_id = l_route_id;
/* delete ff_route_context_usages
where route_id = l_route_id; */
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_payroll_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (l_route_id,
l_jurisdiction_context_id,
3 );
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,
'EMPLOYER_JURSD_ARCHIVE_NUMBER',
'N',
'sql to retrieve GRE based employer archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_payroll_action_context_id,
1
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_tax_unit_context_id,
2
from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_jurisdiction_context_id,
3
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_ARCHIVE',
'N',
'sql to retrieve Assignment based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_assignment_action_context_id,
1
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE_DATE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_ARCHIVE_DATE',
'N',
'sql to retrieve Assignment based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_assignment_action_context_id,
1
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_ARCHIVE_NUMBER', 'N',
'sql to retrieve Assignment based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
select ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1 from dual;
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
l_assignment_action_context_id,
1
from dual;
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_GRE_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_GRE_ARCHIVE',
'N',
'sql to retrieve Assignment and GRE based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_tax_unit_context_id,
2);
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_JD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_JD_ARCHIVE',
'N',
'sql to retrieve Assignment and JD based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_jurisdiction_context_id,
2);
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_GRE_CITY_JD_ARCHIVE',
'N',
'sql to retrieve Assignment,GRE and city JD based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_jurisdiction_context_id,
3);
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE',
'N',
'sql to retrieve Assignment,GRE and county JD based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_jurisdiction_context_id,
3);
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_GRE_STATE_JD_ARCHIVE',
'N',
'sql to retrieve Assignment,GRE and state JD based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_jurisdiction_context_id,
3);
select route_id into l_route_id
from ff_routes where route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
update ff_routes
set text = l_text
where route_id = l_route_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)
values (ff_routes_s.nextval,
'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE',
'N',
'sql to retrieve Assignment,GRE and school JD based archived items',
l_text,
sysdate,
0,
0,
0,
sysdate);
insert into ff_route_parameters
(ROUTE_PARAMETER_ID,
ROUTE_ID,
DATA_TYPE,
PARAMETER_NAME,
SEQUENCE_NO )
values (ff_route_parameters_s.nextval,
ff_routes_s.currval,
'N',
'User Entity ID',
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_assignment_action_context_id,
1);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_tax_unit_context_id,
2);
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
values (ff_routes_s.currval,
l_jurisdiction_context_id,
3);
select ue.notfound_allowed_flag,
ue.creator_type,
ue.entity_description,
ue.route_id,
dbi.null_allowed_flag,
dbi.description ,
dbi.data_type
into l_ue_notfound_allowed_flag,
l_ue_creator_type,
l_ue_entity_description,
l_live_route_id,
l_dbi_null_allowed_flag,
l_dbi_description,
l_dbi_data_type
from ff_database_items dbi,
ff_user_entities ue
where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
and dbi.user_entity_id = ue.user_entity_id;
select count(1) into l_asg_count from ff_route_context_usages rc,
ff_contexts c
where rc.context_id = c.context_id
and rc.route_id = l_live_route_id
and context_name like 'ASSIGNMENT%';
select ff_user_entities_s.nextval into l_user_entity_seq
from dual;
select route_id into l_er_archive_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE';
select route_id into l_er_archive_date_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE_DATE';
select route_id into l_er_archive_number_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE_NUMBER';
select route_id into l_ass_archive_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE';
select route_id into l_ass_archive_date_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE_DATE';
select route_id into l_ass_archive_number_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
select ROUTE_PARAMETER_ID into l_route_parameter_id
from ff_route_parameters
where parameter_name = 'User Entity ID'
and route_id = l_route_id;
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( l_user_entity_seq, /* user_entity_id */
null, /* business_group_id */
'US', /* legislation_code */
l_route_id, /* route_id */
l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
p_item_name, /* user_entity_name */
0, /* creator_id */
'X', /* archive extract creator_type */
substr('Archive of '||l_ue_creator_type||' entity '||
l_ue_entity_description,1,240),/* entity_description */
sysdate, /* last_update_date */
0, /* last_updated_by */
0, /* last_update_login */
0, /* created_by */
sysdate); /* creation_date */
insert into ff_route_parameter_values (
route_parameter_id,
user_entity_id,
value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
--
values( l_route_parameter_id,
l_user_entity_seq,
l_user_entity_seq,
sysdate,
0,
0,
0,
sysdate);
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_item_name,
l_user_entity_seq,
l_dbi_data_type,
l_definition_text,
l_dbi_null_allowed_flag,
substr('Archive of item '||l_dbi_description,1,240),
sysdate,
0,
0,
0,
sysdate);
select ue.notfound_allowed_flag,
ue.creator_type,
ue.entity_description,
ue.route_id,
dbi.null_allowed_flag,
dbi.description ,
dbi.data_type
from ff_database_items dbi,
ff_user_entities ue
where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
and dbi.user_entity_id = ue.user_entity_id;
select c.context_name,
rc.context_id,
rc.sequence_no
from ff_route_context_usages rc,
ff_contexts c
where rc.context_id = c.context_id
and rc.route_id= l_live_route_id
order by 3;
select user_entity_id,
route_id
from ff_user_entities
where user_entity_name = p_item_name;
select jurisdiction_level
from pay_balance_types pbt,
pay_defined_balances pdb
where pbt.balance_type_id = pdb.balance_type_id
and pdb.defined_balance_id = p_defined_balance_id;
select route_id into l_er_archive_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE';
select route_id into l_er_archive_date_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE_DATE';
select route_id into l_er_archive_number_route_id
from ff_routes where
route_name = 'EMPLOYER_ARCHIVE_NUMBER';
select route_id into l_er_tax_unit_arch_rid
from ff_routes where
route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE';
select route_id into l_er_tax_unit_arch_date_rid
from ff_routes where
route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE';
select route_id into l_er_tax_unit_arch_number_rid
from ff_routes where
route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER';
select route_id into l_er_jursd_arch_rid
from ff_routes where
route_name = 'EMPLOYER_JURSD_ARCHIVE';
select route_id into l_er_jursd_arch_date_rid
from ff_routes where
route_name = 'EMPLOYER_JURSD_ARCHIVE_DATE';
select route_id into l_er_jursd_arch_number_rid
from ff_routes where
route_name = 'EMPLOYER_JURSD_ARCHIVE_NUMBER';
select route_id into l_ass_archive_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE';
select route_id into l_ass_archive_date_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE_DATE';
select route_id into l_ass_archive_number_route_id
from ff_routes where
route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
select route_id into l_ass_gre_archive_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_ARCHIVE';
select route_id into l_ass_jd_archive_route_id
from ff_routes where
route_name = 'ASSIGNMENT_JD_ARCHIVE';
select route_id into l_ass_gre_jd_archive_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_JD_ARCHIVE';
select route_id into l_ass_gre_ct_jd_arch_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
select route_id into l_ass_gre_cn_jd_arch_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
select route_id into l_ass_gre_st_jd_arch_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
select route_id into l_ass_gre_sd_jd_arch_route_id
from ff_routes where
route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
select ROUTE_PARAMETER_ID into l_route_parameter_id
from ff_route_parameters
where parameter_name = 'User Entity ID'
and route_id = l_route_id;
/* Update the route id if required */
if l_route_id <> l_exist_route_id then
hr_utility.trace ('Existing Route id : '||
to_char(l_exist_route_id));
update ff_user_entities
set route_id = l_route_id
where user_entity_id = l_user_entity_id;
update ff_route_parameter_values
set route_parameter_id = l_route_parameter_id
where user_entity_id = l_user_entity_id;
select ff_user_entities_s.nextval
into l_user_entity_seq
from dual;
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( l_user_entity_seq, /* user_entity_id */
null, /* business_group_id */
'US', /* legislation_code */
l_route_id, /* route_id */
l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
p_item_name, /* user_entity_name */
0, /* creator_id */
'X', /* archive extract creator_type */
substr('Archive of '||l_ue_creator_type||' entity '||
l_ue_entity_description,1,240),/* entity_description */
sysdate, /* last_update_date */
0, /* last_updated_by */
0, /* last_update_login */
0, /* created_by */
sysdate); /* creation_date */
insert into ff_route_parameter_values (
route_parameter_id,
user_entity_id,
value,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values( l_route_parameter_id,
l_user_entity_seq,
l_user_entity_seq,
sysdate,
0,
0,
0,
sysdate);
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_item_name,
l_user_entity_seq,
l_dbi_data_type,
l_definition_text,
l_dbi_null_allowed_flag,
substr('Archive of item '||l_dbi_description,1,240),
sysdate,
0,
0,
0,
sysdate);