Chuck Oracle Engineer

   PROCEDURE get_program_create_sql
   (
      p_program_name VARCHAR2
   )IS

      p_conc_prog_rec_type  fnd_concurrent_programs_vl%ROWTYPE;
      p_executable_rec_type fnd_executables_form_v%ROWTYPE;
      
      v_application_name VARCHAR2(240);
      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_concurrent_program_name,
                application_id,
                enabled_flag,
                concurrent_program_name,
                description,
                save_output_flag,
                print_flag,
                output_print_style,
                required_style,
                srs_flag,
                run_alone_flag,
                output_file_type,
                enable_trace,
                restart,
                nls_compliant,
                icon_name,
                executable_id
           INTO p_conc_prog_rec_type.user_concurrent_program_name,
                p_conc_prog_rec_type.application_id,
                p_conc_prog_rec_type.enabled_flag,
                p_conc_prog_rec_type.concurrent_program_name,
                p_conc_prog_rec_type.description,
                p_conc_prog_rec_type.save_output_flag,
                p_conc_prog_rec_type.print_flag,
                p_conc_prog_rec_type.output_print_style,
                p_conc_prog_rec_type.required_style,
                p_conc_prog_rec_type.srs_flag,
                p_conc_prog_rec_type.run_alone_flag,  
                p_conc_prog_rec_type.output_file_type,  
                p_conc_prog_rec_type.enable_trace,    
                p_conc_prog_rec_type.restart,
                p_conc_prog_rec_type.nls_compliant,
                p_conc_prog_rec_type.icon_name,
                p_conc_prog_rec_type.executable_id             
           FROM fnd_concurrent_programs_vl
          WHERE concurrent_program_name = p_program_name;
      EXCEPTION
         WHEN no_data_found THEN
            v_err_flag := 'Y';
            dbms_output.put_line('Program not found');   
      END;
       
      BEGIN
         SELECT application_name
           INTO v_application_name
           FROM fnd_application_vl
          WHERE application_id = p_conc_prog_rec_type.application_id;
      EXCEPTION
         WHEN no_data_found THEN
            v_err_flag := 'Y';
            dbms_output.put_line('Application not found');   
      END;
      
      BEGIN
         SELECT executable_name,
                application_name
           INTO p_executable_rec_type.executable_name,
                p_executable_rec_type.application_name
           FROM fnd_executables_form_v
          WHERE executable_id = p_conc_prog_rec_type.executable_id;
      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.REGISTER
                                  (
                                     PROGRAM                    => ''' || p_conc_prog_rec_type.user_concurrent_program_name || ''',
                                     application                => ''' || v_application_name || ''',
                                     enabled                    => ''' || p_conc_prog_rec_type.enabled_flag || ''',
                                     short_name                 => ''' || p_conc_prog_rec_type.concurrent_program_name || ''',
                                     description                => ''' || p_conc_prog_rec_type.description || ''',
                                     executable_short_name      => ''' || p_executable_rec_type.executable_name || ''',
                                     executable_application     => ''' || p_executable_rec_type.application_name || ''',
                                     execution_options          => NULL,
                                     priority                   => NULL,
                                     save_output                => ''' || p_conc_prog_rec_type.save_output_flag || ''',
                                     PRINT                      => ''' || p_conc_prog_rec_type.print_flag || ''',
                                     cols                       => NULL,
                                     ROWS                       => NULL,
                                     style                      => ''' || p_conc_prog_rec_type.output_print_style || ''',
                                     style_required             => ''' || p_conc_prog_rec_type.required_style || ''',
                                     printer                    => NULL,
                                     request_type               => NULL,
                                     request_type_application   => NULL,
                                     use_in_srs                 => ''' || p_conc_prog_rec_type.srs_flag || ''',
                                     allow_disabled_values      => ''N'',
                                     run_alone                  => ''' || p_conc_prog_rec_type.run_alone_flag || ''',
                                     output_type                => ''' || p_conc_prog_rec_type.output_file_type || ''',
                                     enable_trace               => ''' || p_conc_prog_rec_type.enable_trace || ''',
                                     restart                    => ''' || p_conc_prog_rec_type.restart || ''',
                                     nls_compliant              => ''' || p_conc_prog_rec_type.nls_compliant || ''',
                                     icon_name                  => ''' || p_conc_prog_rec_type.icon_name || ''',
                                     language_code              => ''US'',
                                     mls_function_short_name    => NULL,
                                     mls_function_application   => NULL,
                                     incrementor                => NULL,
                                     refresh_portlet            => NULL
                                  );'
                             );
                             
         dbms_output.put_line('
                                  SELECT count(1)
                                    INTO v_cnt
                                    FROM fnd_concurrent_programs_vl
                                   WHERE concurrent_program_name = ''' || p_conc_prog_rec_type.concurrent_program_name || ''';
                                                                
                                  IF nvl(v_cnt, 0) != 0 THEN
                                     COMMIT;
                                     dbms_output.put_line(''Creation program completed'');
                                  ELSE
                                     ROLLBACK;
                                     dbms_output.put_line(''Creation program failed'');
                                  END IF;'              
                             );   

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