[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update value_name, rather than
insert a new row.
25-JUL-2001 nbristow 115.4 Added enable_functional_area and
gen_functional_area
25-JUL-2001 nbristow 115.3 Now nvl the protected flag.
12-APR-2001 exjones 115.2 1731598 Take out code in create_trg_compon-
ents which blindly updates the
enabled flag to what's in the ldt
and replace with code to check if
the component is already there, if
so, do nothing otherwise create the
component.
Need to replace with code using who
columns to check for and update
seeded data, but not that changed
by anyone other than seed, to allow
us to update seeded data.
28-JUN-2000 nbristow 115.1 Added proctected flag.
27-JUN-2000 nbristow 115.0 Created.
*/
/* Global definitions */
--
----------------------------- enable_functional_area --------------------------
/*
NAME
enable_functional_area
NOTES
Generates and enables all the triggers in a functional area.
*/
procedure enable_functional_area(p_short_name varchar2)
is
cursor get_trg is
select pte.short_name,
pte.event_id
from pay_functional_areas pfa,
pay_functional_triggers pft,
pay_trigger_events pte
where pte.event_id = pft.event_id
and pft.area_id = pfa.area_id
and pfa.short_name = p_short_name;
update pay_trigger_components
set enabled_flag = 'Y'
where event_id = trgrec.event_id;
update pay_trigger_events
set generated_flag = 'Y',
enabled_flag = 'Y'
where event_id = trgrec.event_id;
select distinct pte.short_name
from pay_functional_areas pfa,
pay_functional_triggers pft,
pay_trigger_events pte
where pte.event_id = pft.event_id
and pft.area_id = pfa.area_id
and pfa.short_name like p_short_name;
select pte.generated_flag,
pte.enabled_flag,
pte.event_id,
pte.table_name,
pte.triggering_action,
nvl(pte.protected_flag, 'N'),
nvl(pdt.dyn_trigger_type,'T'),
pdt.dated_table_id
into l_generated_flag,
l_enabled_flag,
l_event_id,
l_table_name,
l_triggering_action,
l_protected_flag,
l_dyt_type,
l_tab_id
from pay_trigger_events pte,
pay_dated_tables pdt
where pte.table_name = pdt.table_name(+)
and pte.short_name = p_short_name;
Inserts/Updates the PAY_TRIGGER_EVENTS table.
*/
procedure create_trigger_event (
p_short_name varchar2,
p_table_name varchar2,
p_description varchar2,
p_generated_flag varchar2,
p_enabled_flag varchar2,
p_triggering_action varchar2,
p_owner varchar2,
p_protected_flag varchar2 default 'N'
)
is
l_generated_flag pay_trigger_events.generated_flag%TYPE;
select generated_flag,
enabled_flag,
event_id
into l_generated_flag,
l_enabled_flag,
l_event_id
from pay_trigger_events
where short_name = p_short_name;
update pay_trigger_events
set
table_name = p_table_name,
triggering_action = p_triggering_action,
description = p_description,
generated_flag = p_generated_flag,
enabled_flag = p_enabled_flag,
protected_flag = p_protected_flag
where short_name = p_short_name;
insert into pay_trigger_events
(
event_id,
table_name,
description,
generated_flag,
enabled_flag,
triggering_action,
short_name,
protected_flag
)
select
pay_trigger_events_s.nextval,
p_table_name,
p_description,
p_generated_flag,
p_enabled_flag,
p_triggering_action,
p_short_name,
p_protected_flag
from sys.dual;
Inserts/Updates the PAY_TRIGGER_DECLARATIONS table.
*/
procedure create_trg_declaration (
p_short_name varchar2,
p_variable_name varchar2,
p_data_type varchar2,
p_variable_size number,
p_owner varchar2
)
is
l_event_id number;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
update pay_trigger_declarations
set data_type = p_data_type,
variable_size = p_variable_size
where event_id = l_event_id
and variable_name = p_variable_name;
insert into pay_trigger_declarations
(
declaration_id,
event_id,
variable_name,
data_type,
variable_size
)
select
pay_trigger_declarations_s.nextval,
l_event_id,
p_variable_name,
p_data_type,
p_variable_size
from sys.dual;
Inserts/Updates the PAY_TRIGGER_INITIALISATIONS table.
*/
procedure create_trg_initialisation (
p_short_name varchar2,
p_process_order varchar2,
p_plsql_code varchar2,
p_process_type varchar2,
p_owner varchar2
)
is
l_event_id number;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
update pay_trigger_initialisations
set plsql_code = p_plsql_code,
process_type = p_process_type
where event_id = l_event_id
and process_order = p_process_order;
insert into pay_trigger_initialisations
(
initialisation_id,
event_id,
process_order,
plsql_code,
process_type
)
select
pay_trigger_initialisations_s.nextval,
l_event_id,
p_process_order,
p_plsql_code,
p_process_type
from sys.dual;
Inserts/Updates the PAY_TRIGGER_COMPONENTS table.
*/
procedure create_trg_components (
p_short_name varchar2,
p_legislative_code varchar2,
p_business_group varchar2,
p_payroll_name varchar2,
p_module_name varchar2,
p_enabled_flag varchar2,
p_owner varchar2
)
is
l_event_id number;
select business_group_id
into l_bus_grp_id
from per_business_groups
where name = p_business_group;
select distinct payroll_id
into l_payroll_id
from pay_payrolls_f
where business_group_id = l_bus_grp_id
and payroll_name = p_payroll_name;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
select enabled_flag
into l_enabled
from pay_trigger_components
where event_id = l_event_id
and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
and p_module_name = module_name;
update pay_trigger_components
set enabled_flag = p_enabled_flag
where event_id = l_event_id
and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
and p_module_name = module_name;
insert into pay_trigger_components
(
component_id,
event_id,
legislation_code,
business_group_id,
payroll_id,
module_name,
enabled_flag
)
select
pay_trigger_components_s.nextval,
l_event_id,
p_legislative_code,
l_bus_grp_id,
l_payroll_id,
p_module_name,
p_enabled_flag
from sys.dual;
Inserts/Updates the PAY_TRIGGER_PARAMETERS table.
*/
procedure create_trg_parameter (p_short_name varchar2,
p_process_order varchar2,
p_legislative_code varchar2,
p_business_group varchar2,
p_payroll_name varchar2,
p_module_name varchar2,
p_usage_type varchar2,
p_parameter_type varchar2,
p_parameter_name varchar2,
p_value_name varchar2,
p_automatic varchar2,
p_owner varchar2
)
is
l_event_id number;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
select business_group_id
into l_bus_grp_id
from per_business_groups
where name = p_business_group;
select distinct payroll_id
into l_payroll_id
from pay_payrolls_f
where business_group_id = l_bus_grp_id
and payroll_name = p_payroll_name;
select component_id
into l_usage_id
from pay_trigger_components
where event_id = l_event_id
and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
and p_module_name = module_name;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
select initialisation_id
into l_usage_id
from pay_trigger_initialisations
where event_id = l_event_id
and process_order = p_process_order;
select dated_table_id
into l_usage_id
from pay_dated_tables
where table_name = p_short_name;
update pay_trigger_parameters
set automatic = p_automatic,
value_name = p_value_name
where usage_id = l_usage_id
and usage_type = p_usage_type
and parameter_type = p_parameter_type
and upper(nvl(parameter_name, 'NULL')) =
upper(nvl(p_parameter_name, 'NULL'));
insert into pay_trigger_parameters
(
parameter_id,
usage_type,
usage_id,
parameter_type,
parameter_name,
value_name,
automatic
)
select
pay_trigger_parameters_s.nextval,
p_usage_type,
l_usage_id,
p_parameter_type,
p_parameter_name,
p_value_name,
p_automatic
from sys.dual;
Inserts/Updates the PAY_FUNCTIONAL_AREAS table.
*/
procedure create_func_area (p_area_name varchar2,
p_description varchar2
)
is
begin
--
update pay_functional_areas
set
description = p_description
where short_name = p_area_name;
insert into pay_functional_areas
(
area_id,
short_name,
description
)
select
pay_functional_areas_s.nextval,
p_area_name,
p_description
from sys.dual;
Inserts/Updates the PAY_FUNCTIONAL_TRIGGERS table.
*/
procedure create_func_trigger (p_area_name varchar2
,p_short_name varchar2
,p_owner varchar2
)
is
--
l_event_id number;
select event_id
into l_event_id
from pay_trigger_events
where short_name = p_short_name;
select area_id
into l_area_id
from pay_functional_areas
where short_name = p_area_name;
insert into pay_functional_triggers
(
trigger_id,
area_id,
event_id
)
select
pay_functional_triggers_s.nextval,
l_area_id,
l_event_id
from sys.dual
where not exists (select ''
from pay_functional_triggers
where area_id = l_area_id
and event_id = l_event_id);
create_event_update
NOTES
Inserts/Updates the PAY_EVENT_UPDATES table.
*/
procedure create_event_update (p_table_name varchar2,
p_column_name varchar2,
p_business_group_name varchar2,
p_legislation_code varchar2,
p_change_type varchar2
)
is
l_business_group_id number;
select business_group_id
into l_business_group_id
from per_business_groups
where name = p_business_group_name;
insert into pay_event_updates
(event_update_id,
table_name,
column_name,
business_group_id,
legislation_code,
change_type
)
select pay_event_updates_s.nextval,
p_table_name,
p_column_name,
l_business_group_id,
p_legislation_code,
p_change_type
from sys.dual
where not exists (select ''
from pay_event_updates
where table_name = p_table_name
and column_name = p_column_name
and nvl(business_group_id, -999) = nvl(l_business_group_id, -999)
and nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE')
and p_change_type = change_type);
end create_event_update;
Inserts/Updates the PAY_FUNCTIONAL_USAGES table.
*/
procedure create_func_usage (p_area_name varchar2
,p_usage_id varchar2
,p_business_group_name varchar2
,p_legislation_code varchar2
,p_payroll_name varchar2
,p_owner varchar2
)
is
--
cursor csr_sel_area_id(p_area_name varchar2) is
select area_id
from pay_functional_areas
where upper(short_name) = upper(p_area_name);
select payroll_id
from pay_payrolls_f
where upper(payroll_name)= upper(p_payroll_name);
select business_group_id
from per_business_groups
where upper(name) = upper(p_business_group_name);
insert into pay_functional_usages
(
usage_id,
area_id,
business_group_id,
legislation_code,
payroll_id
)
select
to_number(p_usage_id),
l_area_id,
l_business_group_id,
p_legislation_code,
l_payroll_id
from sys.dual
where not exists( select 'X'
from pay_functional_usages
where area_id = l_area_id
and usage_id =to_number(p_usage_id)
);
select table_name
from pay_dated_tables
where dated_table_id = p_dated_table_id;