How to create concurrent programs from database in oracle apps

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.

/* Formatted on 3/27/2017 4:02:08 PM (QP5 v5.114.809.3010) */
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 Front end:








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.

/* Formatted on 3/27/2017 4:04:29 PM (QP5 v5.114.809.3010) */
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 Front end:



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.

/* Formatted on 3/27/2017 4:06:15 PM (QP5 v5.114.809.3010) */
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.