Copy/Replicate Concurrent program including executable, parameter and request groups

Script to copy concurrent program including executable, parameter and request groups

Usage: 1) Replace l_prefix value with desired value
            2) Replace 'Financial Tax Register' with the program that you want to copy or replicate

DECLARE

    l_prefix   VARCHAR2 (30) := 'XX ';                  -- new program pre-fix

    CURSOR c_program_details IS

        SELECT (SELECT APPLICATION_NAME

                  FROM apps.fnd_application_vl

                 WHERE application_id = e.APPLICATION_ID)

                   exec_application,

               e.EXECUTABLE_NAME,

               e.USER_EXECUTABLE_NAME,

               e.DESCRIPTION

                   exec_description,

               (SELECT meaning

                  FROM apps.fnd_lookup_values_vl

                 WHERE     1 = 1

                       AND lookup_code = e.EXECUTION_METHOD_CODE

                       AND lookup_type = 'CP_EXECUTION_METHOD_CODE'

                       AND enabled_flag = 'Y')

                   exec_method,

               e.EXECUTION_FILE_NAME,

               e.SUBROUTINE_NAME,

               e.EXECUTION_FILE_PATH,

               (SELECT APPLICATION_NAME

                  FROM apps.fnd_application_vl

                 WHERE application_id = P.APPLICATION_ID)

                   prog_application,

               p.CONCURRENT_PROGRAM_NAME,

               p.USER_CONCURRENT_PROGRAM_NAME,

               p.description

                   prog_description,

               p.PRINT_FLAG,

               p.RUN_ALONE_FLAG,

               p.SRS_FLAG,

               p.SAVE_OUTPUT_FLAG,

               p.REQUIRED_STYLE,

               p.OUTPUT_FILE_TYPE,

               p.enable_trace,

               p.restart,

               p.nls_compliant,

               (SELECT application_short_name

                  FROM apps.fnd_application_vl

                 WHERE application_id = p.application_id)

                   prog_appl_short_name

          FROM apps.fnd_executables_vl e, apps.fnd_concurrent_programs_vl p

         WHERE     1 = 1

               AND p.USER_CONCURRENT_PROGRAM_NAME = 'Financial Tax Register'

               --and e.EXECUTABLE_NAME='XX..'

               AND p.EXECUTABLE_ID = e.EXECUTABLE_ID

               AND p.EXECUTABLE_APPLICATION_ID = e.application_id;

BEGIN

    FOR r_program IN c_program_details

    LOOP

        -- Register Executable

        BEGIN

            FND_PROGRAM.executable (

                executable            => l_prefix || r_program.USER_EXECUTABLE_NAME -- user executable name

                                                                                   ,

                application           => r_program.EXEC_APPLICATION -- application

                                                                   ,

                short_name            => l_prefix || r_program.EXECUTABLE_NAME -- short_name

                                                                              ,

                description           => l_prefix || r_program.EXEC_DESCRIPTION -- description

                                                                               ,

                execution_method      => r_program.exec_method -- execution_method

                                                              ,

                execution_file_name   => r_program.EXECUTION_FILE_NAME -- execution_file_name

                                                                      ,

                subroutine_name       => r_program.SUBROUTINE_NAME -- subroutine_name

                                                                  ,

                icon_name             => ''                            -- icon

                                           ,

                language_code         => 'US'                 -- language_code

                                             ,

                execution_file_path   => r_program.EXECUTION_FILE_PATH --execution_file_path

                                                                      );

            --COMMIT;

            DBMS_OUTPUT.put_line ('Executable created');

        END;

        -- Register CP

        BEGIN

            -- Register Concurrent program

            APPS.FND_PROGRAM.register (

                program                    =>

                    l_prefix || r_program.USER_CONCURRENT_PROGRAM_NAME,

                application                => r_program.PROG_APPLICATION,

                enabled                    => 'Y',

                short_name                 => l_prefix || r_program.CONCURRENT_PROGRAM_NAME,

                description                => l_prefix || r_program.PROG_DESCRIPTION,

                executable_short_name      =>

                    l_prefix || r_program.EXECUTABLE_NAME,

                executable_application     => r_program.EXEC_APPLICATION,

                execution_options          => '',

                priority                   => NULL,

                save_output                => r_program.SAVE_OUTPUT_FLAG,

                PRINT                      => r_program.PRINT_FLAG,

                cols                       => NULL,

                rows                       => NULL,

                style                      => NULL,

                style_required             => r_program.REQUIRED_STYLE,

                printer                    => NULL,

                request_type               => NULL,

                request_type_application   => NULL,

                use_in_srs                 => r_program.SRS_FLAG,

                allow_disabled_values      => 'N',

                run_alone                  => r_program.RUN_ALONE_FLAG,

                output_type                => r_program.OUTPUT_FILE_TYPE,

                enable_trace               => r_program.ENABLE_TRACE,

                restart                    => r_program.RESTART,

                nls_compliant              => r_program.NLS_COMPLIANT,

                icon_name                  => NULL,

                language_code              => 'US',

                mls_function_short_name    => NULL,

                mls_function_application   => NULL,

                incrementor                => NULL,

                refresh_portlet            => NULL);

            --COMMIT;

            DBMS_OUTPUT.put_line ('CP created');

        END;

        -- Copy Parameters

        FOR param_rec

            IN (SELECT params.column_seq_num

                           sequence,

                       params.end_user_column_name

                           parameter,

                       params.description,

                       params.enabled_flag

                           enabled,

                       vs.flex_value_set_name

                           value_set,

                       params.default_type,

                       params.DEFAULT_VALUE,

                       params.required_flag

                           required,

                       params.security_enabled_flag

                           enable_security,

                       params.range_code

                           range,

                       params.display_flag

                           display,

                       params.display_size,

                       params.maximum_description_len

                           description_size,

                       params.concatenation_description_len

                           concatenated_description_size,

                       params.form_left_prompt

                           prompt,

                       params.srw_param

                           token,

                       'N'

                           cd_parameter                      --cp.cd_parameter

                  FROM FND_DESCR_FLEX_COL_USAGE_VL  params,

                       fnd_flex_value_sets          vs

                 WHERE     1 = 1

                       AND descriptive_flexfield_name =

                           '$SRS$.' || r_program.CONCURRENT_PROGRAM_NAME

                       AND vs.flex_value_set_id = params.flex_value_set_id(+))

        LOOP

            BEGIN

                fnd_program.parameter (

                    program_short_name              =>

                        l_prefix || r_program.CONCURRENT_PROGRAM_NAME, -- program short name

                    application                     => r_program.prog_appl_short_name, -- application short name

                    sequence                        => param_rec.sequence,

                    parameter                       => param_rec.parameter,

                    --parameter             =>  param_rec.token,

                    description                     => param_rec.description,

                    enabled                         => param_rec.enabled,

                    value_set                       => param_rec.value_set,

                    default_type                    => param_rec.default_type,

                    DEFAULT_VALUE                   => param_rec.DEFAULT_VALUE,

                    required                        => param_rec.required,

                    enable_security                 => param_rec.enable_security,

                    range                           => param_rec.range,

                    display                         => param_rec.display,

                    display_size                    => param_rec.display_size,

                    description_size                => param_rec.description_size,

                    concatenated_description_size   =>

                        param_rec.concatenated_description_size,

                    prompt                          => param_rec.prompt,

                    token                           => param_rec.token,

                    --token                        =>  null,

                    cd_parameter                    => param_rec.cd_parameter);

            END;

        END LOOP;

        DBMS_OUTPUT.put_line ('Parameters copied');

        -- Assign program to request group(s)

        -- Copy request groups

        FOR r_req_group

            IN (SELECT rg.REQUEST_GROUP_NAME,

                       (SELECT APPLICATION_NAME

                          FROM apps.fnd_application_vl

                         WHERE application_id = rg.application_id)

                           rg_application

                  FROM apps.fnd_request_groups          rg,

                       apps.Fnd_Request_Group_Units     u,

                       apps.fnd_concurrent_programs_vl  p

                 WHERE     1 = 1

                       AND p.USER_CONCURRENT_PROGRAM_NAME =

                           r_program.USER_CONCURRENT_PROGRAM_NAME

                       AND u.REQUEST_UNIT_TYPE = 'P'

                       AND u.UNIT_APPLICATION_ID = p.application_id

                       AND u.REQUEST_UNIT_ID = p.concurrent_program_id

                       AND u.REQUEST_GROUP_ID = rg.request_group_id

                       AND u.application_id = rg.application_id)

        LOOP

            BEGIN

                FND_PROGRAM.add_to_group (

                    l_prefix || r_program.CONCURRENT_PROGRAM_NAME,

                    r_program.PROG_APPLICATION                  -- application

                                              ,

                    r_req_group.REQUEST_GROUP_NAME       -- Request Group Name

                                                  ,

                    r_req_group.rg_application);    --Report Group Application

            --COMMIT;

            END;

        END LOOP;

        DBMS_OUTPUT.put_line ('Assignment completed');

        EXIT;                                  -- exit after one round of loop

    END LOOP;

--commit;

EXCEPTION

    WHEN OTHERS

    THEN

        ROLLBACK;

        NULL;

END;

/