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 ;