[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_delete_group boolean default false)
is
--
-- Get all the parent groups for grp p_grp_id
--
cursor c_parent_grps(p_grp_id number) is
select distinct approval_group_id
from ame_approval_group_items
where parameter_name = 'OAM_group_id'
and sysdate >= start_date and sysdate < end_date
start with parameter = to_char(p_grp_id)
connect by prior to_char(approval_group_id) = parameter
union
select to_number(p_grp_id) from dual;
select group_id
,param_name
,param
,ord_no
from (select distinct
approval_group_id group_id
,parameter_name param_name
,parameter param
,order_number ord_no
from (select *
from ame_approval_group_items
where sysdate between start_date
and nvl(end_date - (1/86400), sysdate)
)
start with approval_group_id = p_grp_id
connect by prior parameter = to_char(approval_group_id)
)
where not exists ( select approval_group_id
from ame_approval_groups
where approval_group_id = param
and param_name = 'OAM_group_id'
and is_static = 'Y'
and sysdate between start_date
and nvl(end_date - (1/86400), sysdate)
)
union
select approval_group_id
,'OAM_group_id'
,to_char(approval_group_id)
,1
from ame_approval_Groups
where approval_group_id=p_grp_id
and is_static = 'N'
and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
For each parent group, delete the rows from group_members table and populate them again
with correct info.
*/
l_count :=1;
if p_delete_group = true then
delete
from ame_approval_group_items
where parameter = to_char(p_approval_group_id)
and parameter_name = 'OAM_group_id'
and sysdate >= start_date and sysdate < end_date;
if p_delete_group = false or l_grp_id_list(idx) <> p_approval_group_id then
update ame_approval_group_members
set approval_group_id = hr_api.g_number
where approval_group_id = l_grp_id_list(idx);
select orig_system, orig_system_id
into l_orig_system, l_orig_system_id
from wf_roles
where name = r2.param
and status = 'ACTIVE'
and (expiration_date is null or
sysdate < expiration_date)
and rownum < 2;
select orig_system, orig_system_id
into l_orig_system, l_orig_system_id
from ame_approval_group_members
where approval_group_id = hr_api.g_number
and parameter_name = 'wf_roles_name'
and parameter =r2.param
and rownum <2;
select query_string
into l_query_string
from ame_approval_groups
where to_char(approval_group_id) = r2.param
and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
insert into ame_approval_group_members
(
approval_group_id
,parameter_name
,parameter
,query_string
,order_number
,security_group_id
,orig_system
,orig_system_id
) values
(
l_grp_id_list(idx)
,r2.param_name
,r2.param
,l_query_string
,r2.ord_no
,null
,l_orig_system
,l_orig_system_id
);
delete from ame_approval_group_members where approval_group_id = hr_api.g_number;
delete from ame_approval_group_members where approval_group_id = l_grp_id_list(idx);
select action_type_id
,name
from ame_action_types
where name in ( ame_util.preApprovalTypeName
,ame_util.postApprovalTypeName
,ame_util.groupChainApprovalTypeName
)
and sysdate >= start_date and sysdate < end_date;
procedure update_ame_approver_group
(p_validate in boolean default false
,p_approval_group_id in number
,p_language_code in varchar2 default
hr_api.userenv_lang
,p_description in varchar2 default hr_api.g_varchar2
,p_is_static in varchar2 default hr_api.g_varchar2
,p_query_string in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_ame_approver_group';
select is_static
from ame_approval_groups
where approval_group_id = p_approval_group_id
and sysdate >= start_date and sysdate < end_date;
select approval_group_item_id
,object_version_number
from ame_approval_group_items
where approval_group_id = p_approval_group_id
and sysdate >= start_date and sysdate < end_date;
savepoint update_ame_approver_group;
ame_approver_group_bk2.update_ame_approver_group_b
(p_approval_group_id => p_approval_group_id
,p_language_code => p_language_code
,p_description => p_description
,p_is_static => p_is_static
,p_query_string => p_query_string
,p_object_version_number => p_object_version_number
);
(p_module_name => 'update_ame_approver_group'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_update
,p_approval_group_id => p_approval_group_id
,p_object_version_number => p_object_version_number
,p_description => p_description
,p_is_static => p_is_static
,p_query_string => p_query_string
,p_security_group_id => hr_api.g_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
,p_datetrack_mode => hr_api.g_delete
,p_approval_group_item_id => rec.approval_group_item_id
,p_object_version_number => l_object_version_number_item
,p_start_date => l_start_date_item
,p_end_date => l_end_date_item
);
ame_approver_group_bk2.update_ame_approver_group_a
(p_approval_group_id => p_approval_group_id
,p_language_code => p_language_code
,p_description => p_description
,p_is_static => p_is_static
,p_query_string => p_query_string
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'update_ame_approver_group'
,p_hook_type => 'AP'
);
rollback to update_ame_approver_group;
rollback to update_ame_approver_group;
end update_ame_approver_group;
procedure delete_ame_approver_group
(p_validate in boolean default false
,p_approval_group_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) is
--
-- Declare cursors and local variables
Cursor C_Sel2 is
select act.action_id
,act.action_type_id
,act.object_version_number
from ame_actions act
,ame_action_types aty
where aty.name in ( ame_util.preApprovalTypeName
,ame_util.postApprovalTypeName
,ame_util.groupChainApprovalTypeName
)
and act.action_type_id = aty.action_type_id
and act.parameter = to_char(p_approval_group_id)
and sysdate >= act.start_date and sysdate < act.end_date
and sysdate >= aty.start_date and sysdate < aty.end_date;
select approval_group_item_id
,object_version_number
from ame_approval_group_items
where approval_group_id = p_approval_group_id
and sysdate >= start_date and sysdate < end_date;
select count(*)
from ame_action_usages actu
,ame_actions act
,ame_action_types acty
where act.parameter = to_char(p_approval_group_id)
and actu.action_id = act.action_id
and act.action_type_id = acty.action_type_id
and acty.name in (
'approval-group chain of authority'
,'pre-chain-of-authority approvals'
,'post-chain-of-authority approvals')
and sysdate between actu.start_date and
nvl(actu.end_Date,sysdate)
and sysdate between act.start_date and
nvl(act.end_date-(1/86400),sysdate)
and sysdate between acty.start_date and
nvl(acty.end_date-(1/86400),sysdate);
l_proc varchar2(72) := g_package||'delete_ame_approver_group';
savepoint delete_approver_group;
ame_approver_group_bk3.delete_ame_approver_group_b
(p_approval_group_id => p_approval_group_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_ame_approver_group'
,p_hook_type => 'BP'
);
select count(*)
into l_config_count
from ame_approval_group_config a
,ame_calling_apps aca
where a.approval_group_id = p_approval_group_id
and a.application_id = aca.application_id
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
and sysdate between a.start_date and nvl(a.end_date - (1/86400), sysdate);
ame_action_api.delete_ame_action
(p_action_id => rec.action_id
,p_action_type_id => rec.action_type_id
,p_object_version_number => rec.object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
,p_datetrack_mode => hr_api.g_delete
,p_approval_group_item_id => rec.approval_group_item_id
,p_object_version_number => l_object_version_number_conf
,p_start_date => l_start_date_conf
,p_end_date => l_end_date_conf
);
,p_datetrack_mode => hr_api.g_delete
,p_approval_group_id => p_approval_group_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
,p_delete_group => true
);
ame_approver_group_bk3.delete_ame_approver_group_a
(p_approval_group_id => p_approval_group_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'delete_ame_approver_group'
,p_hook_type => 'AP'
);
rollback to delete_approver_group;
rollback to delete_approver_group;
end delete_ame_approver_group;
procedure delete_approver_group_config
(
p_validate in boolean default false
,p_approval_group_id in number
,p_application_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) IS
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_approver_group_config';
ame_approver_group_bk5.delete_approver_group_config_b
(p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_approver_group_config'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_delete
,p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
ame_approver_group_bk5.delete_approver_group_config_a
(p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'delete_approver_group_config'
,p_hook_type => 'AP'
);
end delete_approver_group_config;
procedure update_approver_group_config
(
p_validate in boolean default false
,p_approval_group_id in number
,p_application_id in number
,p_voting_regime in varchar2 default hr_api.g_varchar2
,p_order_number in varchar2 default hr_api.g_number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) IS
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_approver_group_config';
savepoint update_approver_group_config;
ame_approver_group_bk6.update_approver_group_config_b
(p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_voting_regime => p_voting_regime
,p_order_number => p_order_number
,p_object_version_number =>p_object_version_number
);
(p_module_name => 'update_approver_group_config'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_update
,p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_voting_regime => p_voting_regime
,p_order_number => p_order_number
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
ame_approver_group_bk6.update_approver_group_config_a
(p_approval_group_id => p_approval_group_id
,p_application_id => p_application_id
,p_voting_regime => p_voting_regime
,p_order_number => p_order_number
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'update_approver_group_config'
,p_hook_type => 'AP'
);
rollback to update_approver_group_config;
rollback to update_approver_group_config;
end update_approver_group_config;
procedure delete_approver_group_item
(
p_validate in boolean default false
,p_approval_group_item_id in number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) IS
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_approver_group_item';
select approval_group_id
from ame_approval_group_items
where approval_group_item_id = p_approval_group_item_id
and sysdate >= start_date and sysdate < end_date;
ame_approver_group_bk8.delete_approver_group_item_b
(p_approval_group_item_id => p_approval_group_item_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_approver_group_item'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_delete
,p_approval_group_item_id => p_approval_group_item_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
ame_approver_group_bk8.delete_approver_group_item_a
(p_approval_group_item_id => p_approval_group_item_id
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'delete_approver_group_item'
,p_hook_type => 'AP'
);
end delete_approver_group_item;
procedure update_approver_group_item
(
p_validate in boolean default false
,p_approval_group_item_id in number
,p_order_number in varchar2 default hr_api.g_number
,p_object_version_number in out nocopy number
,p_start_date out nocopy date
,p_end_date out nocopy date
) IS
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_approver_group_item';
select approval_group_id
from ame_approval_group_items
where approval_group_item_id = p_approval_group_item_id
and sysdate between start_date and end_date;
savepoint update_approver_group_item;
ame_approver_group_bk9.update_approver_group_item_b
(p_approval_group_item_id => p_approval_group_item_id
,p_order_number => p_order_number
,p_object_version_number =>p_object_version_number
);
(p_module_name => 'update_approver_group_item'
,p_hook_type => 'BP'
);
,p_datetrack_mode => hr_api.g_update
,p_approval_group_item_id => p_approval_group_item_id
,p_order_number => p_order_number
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
ame_approver_group_bk9.update_approver_group_item_a
(p_approval_group_item_id => p_approval_group_item_id
,p_order_number => p_order_number
,p_object_version_number => p_object_version_number
,p_start_date => l_start_date
,p_end_date => l_end_date
);
(p_module_name => 'update_approver_group_item'
,p_hook_type => 'AP'
);
rollback to update_approver_group_item;
rollback to update_approver_group_item;
end update_approver_group_item;