Chuck Oracle Engineer

   PROCEDURE get_executables_create_sql
   (
      p_executable_name VARCHAR2
   )IS

      p_executable_rec_type fnd_executables_form_v%ROWTYPE;

      v_execution_method_code VARCHAR2(200);
      v_err_flag              VARCHAR2(1) := 'N';
     
      v_resp_id      NUMBER;
      v_resp_appl_id NUMBER;
   BEGIN

      BEGIN
         SELECT responsibility_id,
                application_id
           INTO v_resp_id,
                v_resp_appl_id
           FROM fnd_responsibility_vl
          WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
      EXCEPTION
         WHEN no_data_found THEN
            v_err_flag := 'Y';
            dbms_output.put_line('Responsibility not found');  
      END;

      BEGIN
         SELECT user_executable_name,
                application_name,
                executable_name,
                description,
                decode(execution_method_code, 'H', 'HOST',
                                              'S', 'IMMEDIATE',
                                              'J', 'JAVA Stored PROCEDURE',
                                              'K', 'JAVA Concurrent PROGRAM',
                                              'M', 'Multi LANGUAGE FUNCTION',
                                              'P', 'Oracle Reports',
                                              'I', 'PL/SQL Stored PROCEDURE',
                                              'B', 'Request SET Stage Funtion',
                                              'A', 'Spawned',
                                              'L', 'SQL*Loader',
                                              'Q', 'SQL*Plus',
                                              'E', 'Perl Concurrent PROGRAM') execution_method_code,
                execution_file_name                              
           INTO p_executable_rec_type.user_executable_name,
                p_executable_rec_type.application_name,
                p_executable_rec_type.executable_name,
                p_executable_rec_type.description,
                v_execution_method_code,
                p_executable_rec_type.execution_file_name
           FROM fnd_executables_form_v
          WHERE executable_name = p_executable_name; 
      EXCEPTION
         WHEN no_data_found THEN
            v_err_flag := 'Y';
            dbms_output.put_line('Executable not found');  
      END;
     
      IF nvl(v_err_flag, 'N') = 'N' THEN
     
         dbms_output.put_line('
                               DECLARE
                                  v_cnt NUMBER := 0;
                               BEGIN'
                             );
        
         dbms_output.put_line('
                                  fnd_global.apps_initialize(
                                                             user_id      => ' || fnd_profile.value('USER_ID') || ',  --user_id,
                                                             resp_id      => ' || v_resp_id || ' , --responsibility id,
                                                             resp_appl_id => ' || v_resp_appl_id || '
                                                            );'
                             );                       
        
         dbms_output.put_line('
                                  fnd_program.executable(
                                                         executable          => ''' || p_executable_rec_type.user_executable_name || ''',
                                                         application         => ''' || p_executable_rec_type.application_name || ''',
                                                         short_name          => ''' || p_executable_rec_type.executable_name || ''',
                                                         description         => ''' || p_executable_rec_type.description || ''',
                                                         execution_method    => ''' || v_execution_method_code || ''',
                                                         execution_file_name => ''' || p_executable_rec_type.execution_file_name || ''',
                                                         subroutine_name     =>  NULL,
                                                         icon_name           =>  NULL,
                                                         language_code       =>  ''US'',
                                                         execution_file_path =>  NULL
                                                        );'
                             );
                              
         dbms_output.put_line('
                                  SELECT count(1)
                                    INTO v_cnt
                                    FROM fnd_executables_form_v
                                   WHERE executable_name = ''' || p_executable_rec_type.executable_name || ''';
                                                               
                                  IF nvl(v_cnt, 0) != 0 THEN
                                     COMMIT;
                                     dbms_output.put_line(''Creation executable completed'');
                                  ELSE
                                     ROLLBACK;
                                     dbms_output.put_line(''Creation executable failed'');
                                  END IF;'             
                             ); 

         dbms_output.put_line('
                               END;'
                             ); 
      END IF;                         
   END get_executables_create_sql;