The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hac.approval_comp_id, hac.object_version_number
FROM hxc_approval_styles has, hxc_approval_comps hac
WHERE has.NAME = p_approval_style_name
AND has.approval_style_id = hac.approval_style_id
AND hac.object_version_number = (SELECT MAX (object_version_number)
FROM hxc_approval_comps
WHERE approval_comp_id =hac.approval_comp_id);
SELECT hacn.comp_notification_id, hacn.object_version_number
FROM hxc_app_comp_notifications hacn
WHERE notification_number_retries = p_notification_number_retries
AND notification_timeout_value = p_notification_timeout_value
AND notification_action_code = p_notification_action_code
AND notification_recipient_code = p_notification_recipient_code
AND object_version_number = (SELECT MAX (object_version_number)
FROM hxc_app_comp_notifications
WHERE comp_notification_id =
hacn.comp_notification_id);
SELECT hac.approval_comp_id, hac.object_version_number
FROM hxc_approval_styles has, hxc_approval_comps hac, hxc_time_recipients htr
WHERE has.NAME = p_approval_style_name
AND htr.NAME = p_time_recipient_name
AND has.approval_style_id = hac.approval_style_id
AND htr.time_recipient_id = hac.time_recipient_id
AND hac.object_version_number = (SELECT MAX (object_version_number)
FROM hxc_approval_comps
WHERE approval_comp_id =
hac.approval_comp_id);
SELECT COUNT (1)
FROM hxc_app_comp_notif_usages
WHERE approval_comp_id = l_approval_comp_id
AND approval_comp_ovn = l_approval_comp_ovn
AND comp_notification_id = l_comp_notification_id
AND comp_notification_ovn = l_comp_notification_ovn
AND enabled_flag = 'N';
-- Insert into hxc_app_comp_notif_usages
insert into hxc_app_comp_notif_usages
(approval_comp_id
,approval_comp_ovn
,comp_notification_id
,comp_notification_ovn
,enabled_flag
) values
(l_approval_comp_id
,l_approval_comp_ovn
,l_comp_notification_id
,l_object_version_number
,l_enabled_flag
);
update hxc_app_comp_notif_usages
set
enabled_flag='Y'
where
approval_comp_id=l_approval_comp_id and
approval_comp_ovn=l_approval_comp_ovn and
comp_notification_id=l_comp_notification_id_exist and
comp_notification_ovn=l_object_version_number_exist;
insert into hxc_app_comp_notif_usages
(approval_comp_id
,approval_comp_ovn
,comp_notification_id
,comp_notification_ovn
,enabled_flag
) values
(l_approval_comp_id
,l_approval_comp_ovn
,l_comp_notification_id_exist
,l_object_version_number_exist
,l_enabled_flag
);
select approval_comp_id,object_version_number
into l_approval_comp_id,l_approval_comp_ovn
from hxc_approval_comps where
approval_comp_id=p_approval_component_id;
insert into hxc_app_comp_notif_usages
(approval_comp_id
,approval_comp_ovn
,comp_notification_id
,comp_notification_ovn
,enabled_flag
) values
(l_approval_comp_id
,l_approval_comp_ovn
,l_comp_notification_id
,l_object_version_number
,l_enabled_flag
);
insert into hxc_app_comp_notif_usages
(approval_comp_id
,approval_comp_ovn
,comp_notification_id
,comp_notification_ovn
,enabled_flag
) values
(l_approval_comp_id
,l_approval_comp_ovn
,l_comp_notification_id_exist
,l_object_version_number_exist
,l_enabled_flag
);
procedure update_app_comp_notification
(
p_comp_notification_id in number,
p_object_version_number in out nocopy number,
p_notification_number_retries in number default hr_api.g_number,
p_notification_timeout_value in number default hr_api.g_number
) is
--
-- Declare cursors and local variables
--
l_object_version_number hxc_app_comp_notifications.object_version_number%type :=p_object_version_number;
l_proc varchar2(72) := g_package||'update_app_comp_notification';
savepoint update_app_comp_notification;
hxc_app_comp_notifications_bk2.update_app_comp_notification_b
(p_comp_notification_id => p_comp_notification_id
,p_object_version_number => p_object_version_number
,p_notification_number_retries => p_notification_number_retries
,p_notification_timeout_value => p_notification_timeout_value
);
(p_module_name => 'update_app_comp_notification'
,p_hook_type => 'BP'
);
update hxc_app_comp_notif_usages
set
comp_notification_ovn = l_object_version_number
where comp_notification_id = p_comp_notification_id;
hxc_app_comp_notifications_bk2.update_app_comp_notification_a
(p_comp_notification_id => p_comp_notification_id
,p_object_version_number => p_object_version_number
,p_notification_number_retries => p_notification_number_retries
,p_notification_timeout_value => p_notification_timeout_value
);
(p_module_name => 'update_app_comp_notification'
,p_hook_type => 'AP'
);
rollback to update_app_comp_notification;
rollback to update_app_comp_notification;
end update_app_comp_notification;
procedure delete_app_comp_notification
(
p_comp_notification_id in number
,p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
l_in_out_parameter number;
l_proc varchar2(72) := g_package||'delete_app_comp_notification';
savepoint delete_app_comp_notification;
hxc_app_comp_notifications_bk3.delete_app_comp_notification_b
(p_comp_notification_id => p_comp_notification_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_app_comp_notification'
,p_hook_type => 'BP'
);
update hxc_app_comp_notif_usages
set
enabled_flag = 'N' where
comp_notification_id = p_comp_notification_id and
comp_notification_ovn = p_object_version_number;
hxc_app_comp_notifications_bk3.delete_app_comp_notification_a
(p_comp_notification_id => p_comp_notification_id
,p_object_version_number => p_object_version_number
); exception
(p_module_name => 'delete_app_comp_notification'
,p_hook_type => 'AP'
);
rollback to delete_app_comp_notification;
rollback to delete_app_comp_notification;
end delete_app_comp_notification;
delete from hxc_app_comp_notif_usages where
comp_notification_id = p_comp_notification_id and
comp_notification_ovn= p_object_version_number and
enabled_flag = 'N';
SELECT hacnu.approval_comp_id, hacnu.comp_notification_id
FROM hxc_app_comp_notifications hacn,
hxc_approval_styles has,
hxc_approval_comps hac,
hxc_app_comp_notif_usages hacnu
WHERE has.NAME = p_approval_style_name
AND has.approval_style_id = hac.approval_style_id
AND hac.approval_comp_id = hacnu.approval_comp_id
AND hac.object_version_number = hacnu.approval_comp_ovn
AND hacnu.comp_notification_id = hacn.comp_notification_id
AND hacnu.comp_notification_ovn = hacn.object_version_number
AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND';
update hxc_app_comp_notif_usages
set
enabled_flag = 'N'
where approval_comp_id = l_approval_comp_id and
comp_notification_id = l_comp_notification_id;
rollback to delete_timeout_notifications;