[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_delete_mode constant varchar2(10) default 'DELETE';
select parameter_name, parameter_in_out, parameter_type
from wms_api_hook_signatures
where module_hook_id = p_module_hook_id;
l_selected_cntr number;
select wahe.module_hook_id, wahe.module_type_id, wahe.business_process_id,
wahe.short_name_id, wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
wahe.hooked_package, wahe.hooked_procedure, wahe.current_package_cntr
from wms_api_hooked_entities wahe;
select hook_call_id, enabled_flag, called_package, called_procedure,
effective_from_date, effective_to_date
from wms_api_hook_calls
where module_hook_id = l_module_hook_id
and enabled_flag = 'Y'
and (effective_to_date >= sysdate or effective_to_date is null)
order by hook_call_id;
select sysgen_custom_package, current_package_cntr
from wms_api_hooked_entities
where module_hook_id = l_module_hook_id;
-- the other one can be updated.
if v_api_hooked_entities.current_package_cntr is null then
l_current_package_cntr := 1;
-- In all other cses, only one set needs to be updated.
for i in 1..l_no_of_packages
loop
l_middle_loop := l_middle_loop + 1;
-- Update wms_api_hooked_entities to indicate which is the current package in use
-- to avoid being updated when in use.
begin
update wms_api_hooked_entities
set current_package_cntr = l_current_package_cntr
where module_hook_id = v_api_hooked_entities.module_hook_id;
trace(l_proc ||' Update wms_api_hooked_entities failed with error = ' || sqlerrm(sqlcode));
Procedure create_delete_api_call(
p_hook_short_name_id in number
, p_call_package in varchar2
, p_call_procedure in varchar2
, p_call_description in varchar2
, p_effective_to_date in date
, p_mode in varchar2
, x_retcode out nocopy number
, x_errbuf out nocopy varchar2
) is
l_module_hook_id number;
l_proc varchar2(72) := 'CREATE_DELETE_API_CALL :';
select wahe.module_hook_id, wahe.hooked_package, wahe.hooked_procedure,
wahe.sysgen_custom_package, wahe.sysgen_custom_procedure,
wahc.called_package, wahc.called_procedure, wahc.hook_call_id,
wahc.enabled_flag, wahc.seed_flag
from wms_api_hooked_entities wahe,
wms_api_hook_calls wahc
where wahe.module_hook_id = wahc.module_hook_id(+)
and wahe.short_name_id = p_hook_short_name_id
and wahc.called_package(+) = p_call_package
and wahc.called_procedure(+) = p_call_procedure;
-- Delete Section. Separated from Create on August 18th 2003. Makes it more simpler.
--
if (l_called_package = p_call_package and l_called_procedure = p_call_procedure) then
l_prog := 41;
elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag = 'Y') then
--
-- Seeded Hook Calls are not allowed to be deleted...
--
l_prog := 43;
trace(l_proc||' Mode is :' || c_delete_mode);
trace(l_proc||' Delete prohibited, Attempted to Delete Seeded Call.. Aborting ' || p_mode ||' Module Hook ID :' || l_module_hook_id);
elsif (p_mode = c_delete_mode and l_enabled_flag = 'Y' and l_seed_flag <> 'Y')
then
--
-- For deletion, the combination should pre-exist.
--
l_prog := 44;
delete from wms_api_hook_calls
where module_hook_id = l_module_hook_id
and hook_call_id = l_hook_call_id
and called_package = p_call_package
and called_procedure = p_call_procedure;
trace(l_proc||' Before Calling create_wms_system_objects within DELETE mode', 4);
trace(l_proc||' After Calling create_wms_system_objects within DELETE mode', 4);
trace(l_proc||' Package Generation Failed after Delete', 4);
trace(l_proc||' Package Generation Successfull after Delete ', 4);
-- Create Section. Separated from Delete on August 18th 2003. Makes it more simpler.
--
l_prog := 50;
-- Validate the signature of the call procedure before inserting records in the
-- wms_api_hooks_table.
l_prog := 53;
select wms_api_hook_calls_s.nextval
into l_hook_call_id_seq
from dual;
trace(l_proc||' Hook Call ID sequence to be inserted : ' || l_hook_call_id_seq);
trace(l_proc||' Inserting records into the wms_api_hook_calls table....', 4);
insert into wms_api_hook_calls(
hook_call_id
, module_hook_id
, enabled_flag
, called_package
, called_procedure
, effective_from_date
, effective_to_date
, last_updated_by
, last_update_date
, last_update_login
, creation_date
, created_by
, description
, seed_flag)
values(
l_hook_call_id_seq
, l_module_hook_id
, 'Y'
, p_call_package
, p_call_procedure
, sysdate
, p_effective_to_date
, 1
, sysdate
, 1
, sysdate
, 1
, p_call_description
, 'N');
trace(l_proc||' After Calling Insert into wms_api_hook_calls..', 4);
trace(l_proc||' Record Inserted into wms_api_hook_calls successfully.....', 4);
-- Taking care of Other Miscellaneous Delete Situations...
--
if (l_called_package is null and l_called_procedure is null) and p_mode = c_delete_mode then
--
-- Takes care of deleting a non-existent relationship.
--
l_prog := 70;
elsif l_enabled_flag = 'N' and p_mode = c_delete_mode then
--
-- Relationship is already disabled.
--
l_prog := 80;
trace(l_proc||' Insert into wms_api_hook_calls failed with ' || sqlerrm(sqlcode), 4);
trace(l_proc||' Select from Sequence wms_api_hook_calls_s failed with ' || sqlerrm(sqlcode), 4);
end create_delete_api_call;