Friday, March 27, 2015

Oracle User Hook Registration & Deletion handy Scripts

-- To Delete OAB User Hook 

declare
    l_api_hook_call_id number;
    l_object_version_number number;
begin
    --
    select  api_hook_call_id
    ,       object_version_number
    into    l_api_hook_call_id
    ,       l_object_version_number
    from    hr_api_hook_calls
    where   call_package='XX_OAB_USER_HOOK_PKG'
    and call_procedure='CREATE_PRTT_ENRT_RESULT_A';
    --
    hr_api_hook_call_api.delete_api_hook_call(false,l_api_hook_call_id,l_object_version_number);
    --
    dbms_output.put_line('Delete User Hook '||sqlcode||'-'||sqlerrm);
    --
exception when others then
    dbms_output.put_line('Error Deleting User Hook '||sqlcode||' - '||sqlerrm);  
end;

declare
  ln_api_module_id  number;
begin
  --
  begin
      select api_module_id
      into   ln_api_module_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API MODULE ID '||ln_api_module_id);
      --
  exception when others then
     ln_api_module_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_module_id);
  end;
  if ln_api_module_id <> 0 then
      --
      -- Create all hook package body source code for one API module
      --
      hr_api_user_hooks_utility.create_hooks_one_module(ln_api_module_id);
      --
      -- Build the report text
      --
      hr_api_user_hooks_utility.write_one_errors_report(ln_api_module_id);
      --
      dbms_output.put_line('User Hook Pre-Process Registration Success');
      --  
  else
     --
     dbms_output.put_line('User Hook Pre-Process Registration Failed');
     --       
  end if; 
  --
end;

--To Register the usershook

declare
ln_api_hook_id              hr_api_hooks.api_hook_id%type;     
ln_api_hook_call_id         number;
ln_object_version_number    number;
begin
--
  begin
      select api_hook_id
      into   ln_api_hook_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API HOOK ID '||ln_api_hook_id);
      --
  exception when others then
     ln_api_hook_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_hook_id);
  end;
--
hr_api_hook_call_api.create_api_hook_call
  (p_validate                     => false,
   p_effective_date               => trunc(sysdate),
   p_api_hook_id                  => ln_api_hook_id, --1390,
   p_api_hook_call_type           => 'PP',
   p_sequence                     => 3000,
   p_enabled_flag                 => 'Y',
   p_call_package                 => 'XXS_OAB_USER_HOOK_PKG',
   p_call_procedure               => 'CREATE_PRTT_ENRT_RESULT_A',
   p_api_hook_call_id             => ln_api_hook_call_id,
   p_object_version_number        => ln_object_version_number);
 --
 dbms_output.put_line('p_api_hook_call_id '||ln_api_hook_call_id);
 dbms_output.put_line('p_object_version_number '||ln_object_version_number);
 --
 commit;
 --
exception when others then
    dbms_output.put_line('API Excepiton '||sqlcode||sqlerrm);
end;

declare
  ln_api_module_id  number;
begin
  --
  begin
      select api_module_id
      into   ln_api_module_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API MODULE ID '||ln_api_module_id);
      --
  exception when others then
     ln_api_module_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_module_id);
  end;
  if ln_api_module_id <> 0 then
      --
      -- Create all hook package body source code for one API module
      --
      hr_api_user_hooks_utility.create_hooks_one_module(ln_api_module_id);
      --
      -- Build the report text
      --
      hr_api_user_hooks_utility.write_one_errors_report(ln_api_module_id);
      --
      dbms_output.put_line('User Hook Pre-Processor Registration Success');
      --  
  else
     --
     dbms_output.put_line('User Hook Pre-Processor Registration Failed');
     --       
  end if; 
  --
end;

No comments:

Post a Comment