Oracle EBS: How to Create Oracle Concurrent Requests using PL/SQL Scripts (Using FND_PROGRAM)

This was driving me mad. At the moment we tend to configure the Oracle e-Business Suite through the standard Java interface. The problem is that with big changes (like the i-Expenses implementation I'm currently working on) are first done on a development server, then on a test server, and finally on a live server. Each time the change has to be repeated and validated. This takes an age.

It turns out that Oracle has a perfectly serviceable API for this, the FND_PROGRAM package.

Running the script below setup a concurrent program, a request, and configured it so that it could only be running once at any given time:

-- Setting up Notify Users of New Credit Card Transactions
  begin
    fnd_program.delete_incompatibility(program_short_name   => 'NUNCCT',
                                       application          => 'Payables',
                                       inc_prog_short_name  => 'NUNCCT',
                                       inc_prog_application => 'Payables');
  exception
    when others then
      null;
  end;
  begin
    fnd_program.delete_program(program_short_name => 'NUNCCT',
                               application        => 'Payables');
  exception
    when others then
      null;
  end;
  begin
    fnd_program.delete_executable(executable_short_name => 'NUNCCT',
                                  application           => 'Payables');
  exception
    when others then
      null;
  end;
  fnd_program.executable(executable          => 'Notify Users of New Credit Card Transactions',
                         application         => 'Payables',
                         short_name          => 'NUNCCT',
                         description         => 'Notify Users of New Credit Card Transactions',
                         execution_method    => 'PL/SQL Stored Procedure',
                         execution_file_name => 'APPS.CCL_MANAGER.NotifyUsersOfNewTransactions');
  fnd_program.register(program                => 'Notify Users of New Credit Card Transactions',
                       application            => 'Payables',
                       enabled                => 'Y',
                       short_name             => 'NUNCCT',
                       description            => 'Notify Users of New Credit Card Transactions',
                       executable_short_name  => 'NUNCCT',
                       executable_application => 'Payables',
                       print                  => 'N',
                       use_in_srs             => 'Y');
  fnd_program.incompatibility(program_short_name   => 'NUNCCT',
                              application          => 'Payables',
                              inc_prog_short_name  => 'NUNCCT',
                              inc_prog_application => 'Payables',
                              scope                => 'Set',
                              inc_type             => 'G');
  commit;

This script sets up a executable using FND_PROGRAM.EXECUTABLE, then uses FND_PROGRAM.REGISTER to setup a new concurrent request, and finally FND_PROGRAM.INCOMPATIBILITY to tell the system that only one copy of the concurrent request should be run at any given time.

The begin ... end blocks at the start are just there to delete any data already configured from previous runs of the script (useful in a test environment). The reason for putting them in exception blocks is that the first time you run you would be passing in invalid parameters (the program short code) because it doesn't exist yet.

In case you were wondering this was a concurrent request that would check to see if the user had any new credit card transactions following a load and send them a nice e-mail reminding them to fill in an expense claim (via Oracle i-Expenses).