Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS PL/SQL Concurrent Program Generator: Package and FND_PROGRAM Registration

This generator produces a complete PL/SQL concurrent program for Oracle E-Business Suite 12.1 or 12.2: the package with the errbuf and retcode signature, a canonical date parameter, CSV output and error handling, plus the FND_PROGRAM API calls that register it. A forms navigation block is included for teams that register through the UI.

Fill in

Custom application short name

Package name

Procedure name

Program short name

Program user name

Parameter 1 name (date)

Parameter 1 value set

Parameter 2 name (text)

Parameter 2 value set

Request group name

Request group application short name

Output CSV

Label only: output type stays TEXT; the file content is CSV.

Incompatible program short name

Same as the program short name makes it incompatible with itself (one run at a time). Delete the INCOMPATIBILITY call if not needed.

Generated code

Download:

Block 1: package spec and body

CREATE OR REPLACE PACKAGE XXCUST_ITEM_EXTRACT_PKG AS
  PROCEDURE MAIN (errbuf    OUT NOCOPY VARCHAR2,
                    retcode   OUT NOCOPY VARCHAR2,
                    p_from_date  IN  VARCHAR2,
                    p_item_prefix  IN  VARCHAR2);
END XXCUST_ITEM_EXTRACT_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXCUST_ITEM_EXTRACT_PKG AS
  PROCEDURE MAIN (errbuf    OUT NOCOPY VARCHAR2,
                    retcode   OUT NOCOPY VARCHAR2,
                    p_from_date  IN  VARCHAR2,
                    p_item_prefix  IN  VARCHAR2) IS
    l_date  DATE;
    l_rows  NUMBER := 0;
  BEGIN
    retcode := '0';
    -- FND_STANDARD_DATE passes the canonical string YYYY/MM/DD HH24:MI:SS
    l_date := NVL(FND_DATE.CANONICAL_TO_DATE(p_from_date), TRUNC(SYSDATE) - 1);
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Started ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                      || ' date=' || TO_CHAR(l_date, 'DD-MON-YYYY') || ' text=' || p_item_prefix);

    -- CSV header (output CSV = Y)
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'ORGANIZATION_ID,ITEM,DESCRIPTION,LAST_UPDATE_DATE');

    -- Replace with your own query
    FOR r IN (SELECT msi.organization_id, msi.segment1, msi.description, msi.last_update_date
              FROM   mtl_system_items_b msi
              WHERE  msi.segment1 LIKE p_item_prefix || '%'
              AND    msi.last_update_date >= l_date
              ORDER  BY msi.segment1, msi.organization_id) LOOP
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
        r.organization_id || ',' || r.segment1 || ',"' || REPLACE(r.description, '"', '""') || '",'
        || TO_CHAR(r.last_update_date, 'YYYY-MM-DD HH24:MI:SS'));
      l_rows := l_rows + 1;
    END LOOP;

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows written: ' || l_rows);
    IF l_rows = 0 THEN
      retcode := '1';               -- completes with Warning
      errbuf  := 'No rows matched the parameters';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      retcode := '2';               -- completes with Error
      errbuf  := SUBSTR(SQLERRM, 1, 240);
      FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
      FND_FILE.PUT_LINE(FND_FILE.LOG, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END MAIN;
END XXCUST_ITEM_EXTRACT_PKG;
/

Block 2: check FND_PROGRAM parameter names on your instance

-- Named notation below follows the 12.x package spec. Confirm the names first.
SELECT object_name, overload, position, argument_name, in_out, defaulted
FROM   all_arguments
WHERE  owner = 'APPS'
AND    package_name = 'FND_PROGRAM'
AND    object_name IN ('EXECUTABLE', 'REGISTER', 'PARAMETER', 'ADD_TO_GROUP', 'INCOMPATIBILITY')
ORDER  BY object_name, overload, position;

Block 3: register executable, program, parameters, request group, incompatibility

BEGIN
  FND_PROGRAM.EXECUTABLE(
    executable          => 'XX Item Extract CSV',
    application         => 'XXCUST',
    short_name          => 'XX_ITEM_EXTRACT',
    description         => 'XX Item Extract CSV',
    execution_method    => 'PL/SQL Stored Procedure',
    execution_file_name => 'XXCUST_ITEM_EXTRACT_PKG.MAIN');

  FND_PROGRAM.REGISTER(
    program                => 'XX Item Extract CSV',
    application            => 'XXCUST',
    enabled                => 'Y',
    short_name             => 'XX_ITEM_EXTRACT',
    description            => 'XX Item Extract CSV',
    executable_short_name  => 'XX_ITEM_EXTRACT',
    executable_application => 'XXCUST',
    use_in_srs             => 'Y',
    output_type            => 'TEXT');

  FND_PROGRAM.PARAMETER(
    program_short_name            => 'XX_ITEM_EXTRACT',
    application                   => 'XXCUST',
    sequence                      => 10,
    parameter                     => 'P_from_date',
    description                   => 'from_date',
    enabled                       => 'Y',
    value_set                     => 'FND_STANDARD_DATE',
    required                      => 'N',
    display                       => 'Y',
    display_size                  => 11,
    description_size              => 50,
    concatenated_description_size => 25,
    prompt                        => 'from_date');

  FND_PROGRAM.PARAMETER(
    program_short_name            => 'XX_ITEM_EXTRACT',
    application                   => 'XXCUST',
    sequence                      => 20,
    parameter                     => 'P_item_prefix',
    description                   => 'item_prefix',
    enabled                       => 'Y',
    value_set                     => '15 Characters',
    required                      => 'Y',
    display                       => 'Y',
    display_size                  => 15,
    description_size              => 50,
    concatenated_description_size => 25,
    prompt                        => 'item_prefix');

  FND_PROGRAM.ADD_TO_GROUP(
    program_short_name  => 'XX_ITEM_EXTRACT',
    program_application => 'XXCUST',
    request_group       => 'All Reports',
    group_application   => 'SQLAP');

  -- Delete if no incompatibility is needed. scope is 'Set' or 'Program Only'.
  FND_PROGRAM.INCOMPATIBILITY(
    program_short_name   => 'XX_ITEM_EXTRACT',
    application          => 'XXCUST',
    inc_prog_short_name  => 'XX_ITEM_EXTRACT',
    inc_prog_application => 'XXCUST',
    scope                => 'Set');

  COMMIT;
END;
/

Block 4: the same setup through the forms

System Administrator (or Application Developer) > Concurrent > Program > Executable
  Executable: XX Item Extract CSV  Short Name: XX_ITEM_EXTRACT  Application: XXCUST
  Execution Method: PL/SQL Stored Procedure  Execution File Name: XXCUST_ITEM_EXTRACT_PKG.MAIN

System Administrator > Concurrent > Program > Define
  Program: XX Item Extract CSV  Short Name: XX_ITEM_EXTRACT  Application: XXCUST
  Executable Name: XX_ITEM_EXTRACT  Output Format: Text
  Parameters: 10 P_from_date (FND_STANDARD_DATE), 20 P_item_prefix (15 Characters)
  Incompatibilities: XX_ITEM_EXTRACT

System Administrator > Security > Responsibility > Request
  Group: All Reports  Application: SQLAP  add Program XX Item Extract CSV

Run Block 2 first. The Developer's Guide text for FND_PROGRAM lists some older parameter names (for example executable_name in REGISTER); the 12.x package spec uses executable_short_name and adds short_name to EXECUTABLE. Use whatever ALL_ARGUMENTS shows. Confirm the value set names in FND_FLEX_VALUE_SETS and that the request group exists under that application. The custom application must be registered and its schema granted access by APPS.

Have your concurrent program reviewed

Send the generated package and registration script; we check it against 12.2 online patching standards and your naming conventions.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the custom application short name (registered in Application Developer > Application > Register), package and procedure names, program short and user names, the two parameters with their value sets, and the request group the program should appear in. Compile Block 1 in the APPS schema or in the custom schema with a grant and synonym. Run Block 2 to confirm FND_PROGRAM argument names on your release, then run Block 3 once. Rerunning Block 3 fails because the executable already exists; use FND_PROGRAM.DELETE_PROGRAM and DELETE_EXECUTABLE in a development instance or edit in the forms instead.

errbuf and retcode must be the first two parameters, in that order
Parameter sequence numbers in FND_PROGRAM.PARAMETER fix the argument order
PL/SQL parameters are VARCHAR2 because the manager passes strings

How the manager reads retcode and errbuf

The concurrent manager calls the procedure positionally: errbuf, retcode, then each parameter in sequence order. On return, retcode '0' completes the request Normal, '1' Warning and '2' Error, and errbuf becomes the completion text shown in the Requests window. FND_FILE.PUT_LINE(FND_FILE.LOG, ...) writes to the log file and FND_FILE.OUTPUT to the output file. A date parameter on FND_STANDARD_DATE arrives as a canonical string, so convert it with FND_DATE.CANONICAL_TO_DATE rather than TO_DATE with a guessed mask. The WHEN OTHERS handler sets Error and writes DBMS_UTILITY.FORMAT_ERROR_BACKTRACE so the log shows the failing line.

Worked example

With the defaults, the program XX_ITEM_EXTRACT under XXCUST takes a from date and an item prefix, writes one CSV line per item updated since that date in MTL_SYSTEM_ITEMS_B, ends with Warning if nothing matched, and is incompatible with itself so two runs never overlap. It is added to the All Reports request group under the application you enter (change both defaults to a group your responsibility uses). Submit it from a responsibility using that group, open View Output and save it with a .csv extension. Descriptions are wrapped in double quotes with inner quotes doubled, so commas inside item descriptions do not shift columns.

Version differences and gotchas

Output type TEXT is the right choice for a CSV body: the viewer shows it as text and users save it as .csv. For a true Excel file, register output type XML, write XML to FND_FILE.OUTPUT and attach an XML Publisher template with an Excel output. In 12.2, compile custom objects in the run edition and follow the online patching standards (editioning views, no direct table DDL in APPS). FND_PROGRAM calls must run as APPS. Add the program to a request group the target responsibility actually uses, or users will not find it.

Frequently Asked Questions

What does retcode mean in an Oracle EBS concurrent program?

retcode is the second OUT parameter of a PL/SQL concurrent program. Set it to '0' for Normal completion, '1' for Warning and '2' for Error. errbuf, the first OUT parameter, carries the completion message shown next to the status. If the procedure raises an unhandled exception the request ends in Error with the Oracle error text.

How do I add a concurrent program to a responsibility?

Add it to the request group that the responsibility uses: System Administrator > Security > Responsibility > Request, or FND_PROGRAM.ADD_TO_GROUP with the program short name, program application, request group name and group application. Find the group from FND_RESPONSIBILITY_VL.REQUEST_GROUP_ID joined to FND_REQUEST_GROUPS.

How do I write CSV output from a concurrent program?

Register the program with output type TEXT, write a header and data lines with FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ...), quote text fields and double any inner quotes. Users save the output with a .csv extension. For a proper Excel workbook use XML output with an XML Publisher template instead.

How do I stop two runs of the same program overlapping?

Make the program incompatible with itself: FND_PROGRAM.INCOMPATIBILITY with the same short name as inc_prog_short_name, or add it on the Incompatibilities tab of the Concurrent Program form. The Conflict Resolution Manager then holds the second request in Pending/Standby until the first finishes.

If you want developers and support staff to ask your EBS instance how a program is defined and why a run failed, in plain English on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools