How to create concurrent programs from database?

Here are couple of APIs useful for creating any concurrent programs from the backend database.

1)    Registering the Executable from back-end:

Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

BEGIN
  FND_PROGRAM.executable(executable => 'XXFIN TEST EXECUTABLE' , -- Executable Name
  application=>'XXFIN' , -- Application Short Name
  short_name=>'XXFINTSTEXE' , -- Executable Short Name
  description=>'Test Executable created from Backend' ,     -- Description,DEFAULT NULL
  execution_method=>'PL/SQL Stored Procedure',              -- Execution Method
  execution_file_name=>'XXFIN_TEST_PROC' ,                  -- Execution File Name,DEFAULT NULL
  subroutine_name=>NULL ,                                   -- Subroutine Name,DEFAULT NULL
  icon_name=>NULL ,                                         -- Icon Name,DEFAULT NULL
  language_code=>'US' ,                                     -- Language Code,DEFAULT 'US'
  execution_file_path=>NULL                                 -- Execution File Path, DEFAULT NULL
  );
  COMMIT;
END;

View from Frontend:

Notes:

1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.

2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”

FROM fnd_lookup_values

WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’

AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:     

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

BEGIN
  FND_PROGRAM.register(program =>'Test CP from DB', -- CP Name
  application =>'XXFIN' , -- Application Short Name
  enabled =>'Y',                                    -- Flag to Enable/Disable a CP
  short_name =>'XXFINTSTCPDB', -- CP Short Name
  description =>'Test CP created from Backend' ,    -- Description,DEFAULT NULL
  executable_short_name =>'XXFINTSTEXE', -- Executable Short Name
  executable_application =>'XXFIN' , -- Executable Application Short Name
  execution_options => NULL,                        -- Execution Options,DEFAULT NULL,
  priority => NULL,                                 -- Priority,DEFAULT NULL,
  save_output =>'Y',                                -- Save Output,DEFAULT 'Y',
  PRINT =>'Y' ,                                     -- Print,DEFAULT 'Y',
  cols => NULL, -- DEFAULT NULL,
  rows => NULL, -- DEFAULT NULL,
  style => NULL,                                    -- DEFAULT NULL,
  style_required =>'N' ,                            -- DEFAULT 'N',
  printer => NULL,                                  -- DEFAULT NULL,
  request_type => NULL,                             -- DEFAULT NULL,
  request_type_application => NULL,                 -- DEFAULT NULL,
  use_in_srs =>'N' ,                                -- DEFAULT 'N',
  allow_disabled_values =>'N' ,                     -- DEFAULT 'N',
  run_alone =>'N' ,                                 -- DEFAULT 'N',
  output_type =>'TEXT',                             -- DEFAULT 'TEXT'
  enable_trace =>'N' ,                              -- DEFAULT 'N',
  restart =>'Y' ,                                   -- DEFAULT 'Y',
  nls_compliant =>'Y' ,                             -- DEFAULT 'Y',
  icon_name => NULL,                                -- DEFAULT NULL,
  language_code => 'US',                            -- DEFAULT 'US',
  mls_function_short_name => NULL,                  -- DEFAULT NULL,
  mls_function_application => NULL,                 -- DEFAULT NULL,
  incrementor => NULL, -- DEFAULT NULL,
  refresh_portlet => NULL                           -- DEFAULT NULL,
  );
  COMMIT;
END;

View from Frontend:

Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.

2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

BEGIN
  FND_PROGRAM.add_to_group('XXFINTSTCPDB', -- Concurrent Program Short Name
  'XXFIN' , -- Application Short Name
  'All Reports',                           -- Report Group Name
  'SQLAP'); -- Report Group Application
  COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all.