Oracle EBS中删除可执行、并发程序



1、定义删除的包

PROCEDURE delete_parameter(program_short_name          IN VARCHAR2 ,

                         application                   IN VARCHAR2 ,

                           parameter                   IN VARCHAR2 ) is

  program_appl_id

      fnd_application.application_id% TYPE;

  program_id

        fnd_concurrent_programs.concurrent_program_id% TYPE;

  desc_flex_name

      fnd_descriptive_flexs.descriptive_flexfield_name% TYPE;

  prog_appl_short_name fnd_application.application_short_name% TYPE;

BEGIN

  message_init;

  check_notnull(program_short_name);

  check_notnull(application);

  check_notnull(parameter);

  program_appl_id := application_id_f(application);

  prog_appl_short_name := application_short_name_f(application);

  desc_flex_name := '$SRS$.'||program_short_name;

  program_id := concurrent_program_id_f(program_appl_id, program_short_name);

  -- Delete param references in request sets

  delete from fnd_request_set_program_args a

   where (a.application_id, a.request_set_id, a.request_set_program_id)

         in (select sp.set_application_id,

                     sp.request_set_id,

                     sp.request_set_program_id

               from fnd_request_set_programs sp

               where sp.program_application_id = program_appl_id

                 and sp.concurrent_program_id = program_id)

     and (a.descriptive_flex_appl_id,

          a.descriptive_flexfield_name,

          a.application_column_name) in

         ( select u.application_id,

                 u.descriptive_flexfield_name,

                 u.application_column_name

            from fnd_descr_flex_column_usages u

           where u.application_id = program_appl_id

             and u.descriptive_flexfield_name = desc_flex_name

             and u.descriptive_flex_context_code = 'Global Data Elements'

             and u.end_user_column_name = parameter);

   -- Delete the param

   fnd_flex_dsc_api.delete_segment(appl_short_name=> prog_appl_short_name,

                           flexfield_name=>desc_flex_name,

                                   context=>'Global Data Elements' ,

                           segment=>parameter);

END delete_parameter;

-- Procedure

--   DELETE_EXECUTABLE

--

-- Purpose

--   Delete a concurrent program executable.

--

-- Arguments

--   executable_short_name  - Name of the executable. (e.g. FNDSCRMT)

--   application - Application of the executable.

--                 (e.g. 'Application Object Library')

--

PROCEDURE delete_executable(executable_short_name          IN VARCHAR2 ,

                      application            IN VARCHAR2 ) is

  exec_application_id

      fnd_application.application_id% TYPE;

  exec_id

        fnd_executables.executable_id% TYPE;

  dummy varchar2( 1);

BEGIN

  message_init;

  check_notnull(executable_short_name);

  check_notnull(application);

  exec_application_id := application_id_f(application);

  BEGIN

     SELECT executable_id

       INTO exec_id

       FROM fnd_executables

       WHERE application_id = exec_application_id

       AND executable_name = executable_short_name;

  EXCEPTION

      WHEN no_data_found THEN

       println( 'Could not find executable: '||executable_short_name);

       return;

  END;

  -- Can't delete an executable if it is in use

  BEGIN

    select 'x' into dummy

      from sys.dual

     where not exists

       ( select 1

          from fnd_concurrent_programs

         where executable_application_id = exec_application_id

           and executable_id = exec_id);

  EXCEPTION

    when no_data_found then

      message( 'Error - Executable is assigned to a concurrent program.');

      RAISE bad_parameter;

  END;

  delete from fnd_executables_tl

   where executable_id = exec_id

     and application_id = exec_application_id;

  delete from fnd_executables

   where executable_id = exec_id

     and application_id = exec_application_id;

END delete_executable;

2、在PL/SQL中调用定义的包删除可执行和并发程序

BEGIN

  --

  /*fnd_program.remove_from_group(program_short_name  => 'CUXWOAMRP',

                                program_application => 'CUX',

                                request_group       => 'All MRP Reports',

                                group_application   => 'MRP');*/

  fnd_program.delete_program(program_short_name => 'CUX_SHIP_DEL', application => 'CUX' );

  fnd_program.delete_executable(executable_short_name => 'CUX_SHIP_DEL', application => 'CUX' );

  --EXCEPTION WHEN OTHERS THEN ROLLBACK;

END;