[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_root_taskflow_form_sel varchar2(27) := 'ROOT_TASKFLOW_FORM_SELECTOR';
select *
from wf_process_activities wpa
where wpa.instance_id = c_instance_id;
select wpa.activity_name
from wf_process_activities wpa
where wpa.process_version =
(select max(wpa1.process_version)
from wf_process_activities wpa1
where wpa1.process_name = wpa.process_name
and wpa1.process_item_type = wpa.process_item_type)
and wpa.process_item_type = c_item_type
and wpa.activity_name = nvl(c_process_name, wpa.activity_name)
and wpa.process_name = g_root_activity_name
order by 1;
select wat1.display_name activity_display_name
,witt.display_name item_display_name
,wat2.display_name process_display_name
from wf_activities_tl wat1
,wf_activities_tl wat2
,wf_activities wa1
,wf_activities wa2
,wf_item_types_tl witt
,wf_process_activities wpa
where wpa.instance_id = p_instance_id
and witt.name = wpa.activity_item_type
and witt.language = g_language
and wa1.name = wpa.activity_name
and wa1.item_type = wpa.activity_item_type
and wa1.end_date is null
and wat1.item_type = wa1.item_type
and wat1.name = wa1.name
and wat1.version = wa1.version
and wat1.language = g_language
and wa2.name = wpa.process_name
and wa2.item_type = wpa.process_item_type
and wa2.end_date is null
and wat2.item_type = wa2.item_type
and wat2.name = wa2.name
and wat2.version = wa2.version
and wat2.language = g_language;
select wpa.instance_id
from wf_process_activities wpa
where wpa.process_version =
(select max(wpa1.process_version)
from wf_process_activities wpa1
where wpa1.process_name = wpa.process_name
and wpa1.process_item_type = wpa.process_item_type)
and wpa.process_item_type = p_process_item_type
and wpa.activity_name = p_root_process_name
and wpa.process_name = g_root_activity_name;
select wpa.instance_id
from wf_process_activities wpa
where wpa.process_version =
(select max(wpa1.process_version)
from wf_process_activities wpa1
where wpa1.process_name = wpa.process_name
and wpa1.process_item_type = wpa.process_item_type)
and wpa.process_item_type = p_process_item_type
and wpa.process_name = p_root_process_name
and wpa.activity_name = g_root_taskflow_form_sel;
select wat.to_process_activity
,wat.result_code
from wf_activity_transitions wat
where wat.result_code <> g_default_transition_value
and wat.from_process_activity = c_instance_id;
procedure insert_workflow
(p_process_name in varchar2) is
--
cursor l_csr_workflow_id is
select hw.workflow_id
from hr_workflows hw
where hw.workflow_name = p_process_name;
select hnnu.nav_node_usage_id
from hr_navigation_node_usages hnnu
where hnnu.workflow_id = c_workflow_id;
l_proc varchar2(72) := g_package||'insert_workflow';
select nav_path_id
from hr_navigation_paths hnp
where hnp.from_nav_node_usage_id = p_nav_id
or hnp.to_nav_node_usage_id = p_nav_id;
select hr_workflows_s.nextval
into g_workflow_id
from sys.dual;
insert into hr_workflows (workflow_id, workflow_name) values (g_workflow_id, p_process_name);
g_workflow_process_mode := 'INSERT';
g_workflow_process_mode := 'UPDATE';
/*select nav_path_id
into l_nav_path_id
from hr_navigation_paths hnp
where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id; */
delete from hr_navigation_paths_tl hnp
where nav_path_id = csr_paths.nav_path_id;
delete from hr_navigation_paths hnp
where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id;
delete from hr_navigation_paths_tl hnp
where nav_path_id = l_nav_path_id;
delete from hr_navigation_paths hnp
where hnp.from_nav_node_usage_id = csr_csr.nav_node_usage_id
or hnp.to_nav_node_usage_id = csr_csr.nav_node_usage_id;
delete from hr_navigation_node_usages hnnu
where hnnu.nav_node_usage_id = csr_csr.nav_node_usage_id;
end insert_workflow;
procedure insert_navigation_paths is
-- --------------------------------------------------------------------------
-- |-------------------------< private cursors >----------------------------|
-- --------------------------------------------------------------------------
cursor csr_process_start_transitions
(c_instance_id wf_process_activities.instance_id%type) is
-- select the START result codes
select wat.from_process_activity
,wat.to_process_activity
,wat.result_code
from wf_process_activities wpa1
,wf_process_activities wpa2
,wf_activity_transitions wat
where wpa1.instance_id = c_instance_id
and wpa2.process_name = wpa1.activity_name
and wpa2.process_item_type = wpa1.activity_item_type
and wpa2.start_end = g_activity_type_start
and wpa2.process_version =
(select max(wpa3.process_version)
from wf_process_activities wpa3
where wpa3.process_name = wpa2.process_name
and wpa3.process_item_type = wpa2.process_item_type)
and wat.from_process_activity = wpa2.instance_id;
select wat.from_process_activity
,wat.to_process_activity
,wat.result_code
from wf_process_activities wpa1
,wf_process_activities wpa2
,wf_activity_transitions wat
where wpa1.instance_id = c_instance_id
and wpa1.start_end = g_activity_type_end
and wpa2.activity_name = wpa1.process_name
and wpa2.activity_item_type = wpa1.process_item_type
and wpa2.process_version =
(select max(wpa3.process_version)
from wf_process_activities wpa3
where wpa3.process_name = wpa2.process_name
and wpa3.process_item_type = wpa2.process_item_type)
and wat.from_process_activity = wpa2.instance_id
and wat.result_code = c_result_code;
select wat.from_process_activity
,wat.to_process_activity
,wat.result_code
from wf_activity_transitions wat
where wat.from_process_activity = c_instance_id;
select wat.from_process_activity
,wat.to_process_activity
,wat.result_code
from wf_activity_transitions wat
where wat.from_process_activity = c_instance_id;
select wa.type
,wpa.start_end
from wf_activities wa
,wf_process_activities wpa
where wpa.instance_id = c_instance_id
and wa.item_type = wpa.activity_item_type
and wa.name = wpa.activity_name
and wa.end_date is null;
select L.language_code l_language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from HR_NAVIGATION_PATHS_TL T
where T.NAV_PATH_ID = T_NAV_PATH_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
l_proc varchar2(72) := g_package||'insert_navigation_paths';
type l_insert_path_rec is record
(from_nav_node_usage_id hr_navigation_paths.from_nav_node_usage_id%type
,to_nav_node_usage_id hr_navigation_paths.to_nav_node_usage_id%type
,nav_button_required hr_navigation_paths.nav_button_required%type
,sequence hr_navigation_paths.sequence%type
,override_label hr_navigation_paths.override_label%type
,result_code wf_activity_transitions.result_code%type
,insert_path boolean
);
type l_insert_path_tab is table of l_insert_path_rec
index by binary_integer;
l_insert_path_struct l_insert_path_tab;
l_result_stack_struct.delete(l_result_stack_struct.last);
l_result_stack_struct.delete;
l_visit_list_struct.delete;
select waav.name
,waav.text_value
from wf_activity_attr_values waav
where waav.process_activity_id = p_from_process_activity
and waav.name like 'DISPLAY_BUTTON_'
and (waav.text_value = c_form_name
or waav.text_value = g_parent_transition);
for j in l_insert_path_struct.first..l_insert_path_struct.last loop
if l_insert_path_struct(j).from_nav_node_usage_id =
l_parent_struct(i).to_nav_node_usage_id and
l_insert_path_struct(j).to_nav_node_usage_id =
l_parent_struct(i).from_nav_node_usage_id then
-- parent does exist
l_insert_path_struct(l_parent_struct(i).parent_index).sequence
:= l_parent_struct(i).sequence;
l_insert_path_struct(l_parent_struct(i).parent_index).nav_button_required
:= 'Y';
l_insert_path_struct(l_parent_struct(i).parent_index).nav_button_required
:= 'N';
procedure set_insert_path
(p_from_nav_node_usage_id in number
,p_from_process_activity in number
,p_to_process_activity in number
,p_result_code in varchar2) is
--
l_proc varchar2(72) := g_package||'set_insert_path';
l_insert_path boolean;
l_insert_path := false;
l_insert_path := true;
if l_insert_path_struct.count > 0 then
-- ensure that an entry does not already exist for this form and
-- result combination. also count the number of navigation paths
for i in l_insert_path_struct.first..l_insert_path_struct.last loop
if l_insert_path_struct(i).from_nav_node_usage_id =
p_from_nav_node_usage_id then
hr_utility.set_location(l_proc, 15);
if l_insert_path_struct(i).result_code = p_result_code or
l_insert_path_struct(i).to_nav_node_usage_id = l_to_nav_node_usage_id then
-- a serious error has occurred, you cannot have a 'FROM' form activity
-- have more than one path to the same form.
-- select the process details that is in error
get_item_act_display_names
(p_instance_id => p_from_process_activity
,p_item_type_display_name => g_item_type_display_name
,p_activity_display_name => g_activity_display_name
,p_process_display_name => g_process_display_name);
l_index := l_insert_path_struct.count + 1;
l_insert_path_struct(l_index).from_nav_node_usage_id := p_from_nav_node_usage_id;
l_insert_path_struct(l_index).to_nav_node_usage_id := l_to_nav_node_usage_id;
l_insert_path_struct(l_index).nav_button_required := l_nav_button_required;
l_insert_path_struct(l_index).sequence := l_button_sequence;
l_insert_path_struct(l_index).override_label := l_button_text;
l_insert_path_struct(l_index).result_code := p_result_code;
l_insert_path_struct(l_index).insert_path := l_insert_path;
end set_insert_path;
l_parent_struct.delete;
set_insert_path
(p_from_nav_node_usage_id =>
g_node_usage_tab(l_usage_index).nav_node_usage_id
,p_from_process_activity =>
g_node_usage_tab(l_usage_index).instance_id
,p_to_process_activity => l_result_csr.to_process_activity
,p_result_code => l_expected_sqlform);
set_insert_path
(g_node_usage_tab(l_usage_index).nav_node_usage_id
,g_node_usage_tab(l_usage_index).instance_id
,l_pop_to_process_activity
,l_expected_sqlform);
if l_insert_path_struct.count > 0 then
-- insert all the paths
for i in l_insert_path_struct.first..l_insert_path_struct.last loop
-- only insert paths where the boolean insert_path is true
if l_insert_path_struct(i).insert_path then
insert into hr_navigation_paths
(nav_path_id
,from_nav_node_usage_id
,to_nav_node_usage_id
,nav_button_required
,sequence
,override_label)
values
(hr_navigation_paths_s.nextval
,l_insert_path_struct(i).from_nav_node_usage_id
,l_insert_path_struct(i).to_nav_node_usage_id
,l_insert_path_struct(i).nav_button_required
,l_insert_path_struct(i).sequence
,l_insert_path_struct(i).override_label
);
select nav_path_id
into l_nav_path_id
from hr_navigation_paths
where from_nav_node_usage_id = l_insert_path_struct(i).from_nav_node_usage_id
and to_nav_node_usage_id = l_insert_path_struct(i).to_nav_node_usage_id;
insert into hr_navigation_paths_tl (
nav_path_id
,language
,source_lang
,override_label)
select b.nav_path_id
,i.l_language_code
,userenv('LANG')
,b.override_label
from hr_navigation_paths b
where not exists
(select '1'
from hr_navigation_paths_tl t
where t.nav_path_id = b.nav_path_id
and t.language = i.l_language_code);
select L.language_code
into l_current_language
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from HR_NAVIGATION_PATHS_TL T
where T.NAV_PATH_ID = L_NAV_PATH_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
insert into hr_navigation_paths_tl (
nav_path_id
,language
,source_lang
,override_label)
select b.nav_path_id
,l_current_language
,userenv('LANG')
,b.override_label
from hr_navigation_paths b
where not exists
(select '1'
from hr_navigation_paths_tl t
where t.nav_path_id = b.nav_path_id
and t.language = l_current_language);
end insert_navigation_paths;
procedure insert_navigation_node_usage
(p_nav_node_id in number
,p_instance_id in number
,p_sqlform in varchar2
,p_override_label in varchar2) is
--
l_top_node varchar2(1) := 'N';
l_proc varchar2(72) := g_package||'insert_navigation_node_usage';
select hr_navigation_node_usages_s.nextval
into l_nav_node_usage_id
from sys.dual;
insert into hr_navigation_node_usages
(nav_node_usage_id,
workflow_id,
nav_node_id,
top_node)
values
(l_nav_node_usage_id
,g_workflow_id
,p_nav_node_id
,l_top_node);
end insert_navigation_node_usage;
procedure insert_navigation_nodes
(p_process_item_type in varchar2
,p_process_name in varchar2) is
--
cursor l_csr_processes(c_instance_id wf_process_activities.instance_id%type) is
select wpa1.instance_id
from wf_activities wa
,wf_process_activities wpa1
,wf_process_activities wpa2
where wpa2.instance_id = c_instance_id
and wpa1.process_name = wpa2.activity_name
and wpa1.process_item_type = wpa2.activity_item_type
and wpa1.process_version =
(select max(wpa3.process_version)
from wf_process_activities wpa3
where wpa3.process_name = wpa1.process_name
and wpa3.process_item_type = wpa1.process_item_type)
and wa.name = wpa1.activity_name
and wa.item_type = wpa1.activity_item_type
and wa.type = g_activity_type_process
and wa.end_date is null;
select wpa1.instance_id
from wf_activities wa
,wf_process_activities wpa1
,wf_process_activities wpa2
,wf_activity_attr_values waav
,wf_activity_attributes waa
where wpa2.instance_id = c_instance_id
and wpa1.process_name = wpa2.activity_name
and wpa1.process_item_type = wpa2.activity_item_type
and wpa1.process_version =
(select max(wpa3.process_version)
from wf_process_activities wpa3
where wpa3.process_name = wpa1.process_name
and wpa3.process_item_type = wpa1.process_item_type)
and wa.name = wpa1.activity_name
and wa.item_type = wpa1.activity_item_type
and wa.type = g_activity_type_function
and wa.end_date is null
and waav.process_activity_id = wpa1.instance_id
and waav.name = waa.name
and waav.text_value = g_hrms_sqlform
and waa.activity_item_type = wa.item_type
and waa.activity_name = wa.name
and waa.activity_version = wa.version
and waa.name = g_taskflow_activity_type;
select waa.name
,waav.text_value
from wf_activities wa
,wf_process_activities wpa1
,wf_activity_attr_values waav
,wf_activity_attributes waa
where wpa1.instance_id = c_instance_id
and wa.name = wpa1.activity_name
and wa.item_type = wpa1.activity_item_type
and wa.type = g_activity_type_function
and wa.end_date is null
and waav.process_activity_id = wpa1.instance_id
and waav.name = waa.name
and waav.name in ('TASKFLOW_ACTIVITY_NAME'
,'CUSTOMIZATION_NAME'
,'BUTTON_TEXT'
,'HRMS_FORM_BLOCK_NAME')
and waa.activity_item_type = wa.item_type
and waa.activity_name = wa.name
and waa.activity_version = wa.version
and exists
(select 1
from wf_activity_attr_values waav1
where waav1.name = g_taskflow_activity_type
and waav1.text_value = g_hrms_sqlform
and waav1.process_activity_id = waav.process_activity_id);
select hnu.nav_unit_id
,hnul.default_label
from hr_navigation_units hnu,
hr_navigation_units_tl hnul
where hnu.form_name = c_form_name
and nvl(hnu.block_name, hr_api.g_varchar2) = nvl(c_block_name, hr_api.g_varchar2)
and hnu.nav_unit_id = hnul.nav_unit_id
and hnul.language=userenv('LANG');
select pcr.customized_restriction_id
from pay_customized_restrictions pcr
where pcr.form_name = c_form_name
and pcr.enabled_flag = 'Y'
and nvl(pcr.business_group_id, nvl(g_business_group_id, hr_api.g_number)) =
nvl(g_business_group_id, hr_api.g_number)
and nvl(pcr.legislation_code, nvl(g_legislation_code, hr_api.g_varchar2)) =
nvl(g_legislation_code, hr_api.g_varchar2)
and nvl(pcr.legislation_subgroup, nvl(g_legislation_subgroup, hr_api.g_varchar2)) =
nvl(g_legislation_subgroup, hr_api.g_varchar2)
and pcr.application_id between 800 and 899
and pcr.name = c_customization_name;
select hnn.nav_node_id
,hnn.name
from hr_navigation_nodes hnn
where hnn.nav_unit_id = c_nav_unit_id
and nvl(hnn.customized_restriction_id, hr_api.g_number) =
nvl(c_customized_restriction_id, hr_api.g_number);
l_proc varchar2(72) := g_package||'insert_navigation_nodes';
l_process_stack_struct.delete; -- clear the process stack
l_process_stack_struct.delete(l_process_stack_struct.last);
select hr_navigation_nodes_s.nextval
into l_nextval
from sys.dual;
insert into hr_navigation_nodes
(nav_node_id,
nav_unit_id,
name,
customized_restriction_id)
values
(l_nextval
,l_nav_unit_id
,l_navigation_node_name
,l_customized_restriction_id);
insert_navigation_node_usage
(p_nav_node_id => l_nav_node_id
,p_instance_id => l_csr_tf_function_activities.instance_id
,p_sqlform => l_taskflow_actvity_name
,p_override_label => nvl(l_override_label, l_default_label));
end insert_navigation_nodes;
select userenv('LANG')
into g_language
from sys.dual;
g_node_usage_tab.delete;
insert_workflow
(p_process_name => l_csr_root.activity_name);
insert_navigation_nodes
(p_process_item_type => l_item_type
,p_process_name => l_csr_root.activity_name);
insert_navigation_paths;
select 1
from wf_process_activities wpa
,wf_activity_attr_values waav
where wpa.instance_id = actid
and waav.process_activity_id = wpa.instance_id
and waav.name = g_taskflow_activity_type
and waav.text_value = g_hrms_sqlform;