# Oracle EBS PL/SQL Concurrent Program Generator: Package and FND_PROGRAM Registration > Source: https://netray.co/tools/oracle-ebs-plsql-concurrent-program-generator > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Generates a PL/SQL concurrent program package (errbuf, retcode, date parameter, CSV output, error backtrace) and the FND_PROGRAM calls that register the executable, program, parameters, request group and incompatibility. ## Category developer ## Erp Oracle E-Business Suite ## Audience - PL/SQL developer - Oracle Apps technical consultant - EBS administrator ## Tool **Kind:** template **Output:** code **Language:** sql **Fields:** #### Custom application short name **Key:** app **Type:** text **Default:** XXCUST #### Package name **Key:** pkg **Type:** text **Default:** XXCUST_ITEM_EXTRACT_PKG #### Procedure name **Key:** proc **Type:** text **Default:** MAIN #### Program short name **Key:** prog_short **Type:** text **Default:** XX_ITEM_EXTRACT #### Program user name **Key:** prog_user **Type:** text **Default:** XX Item Extract CSV #### Parameter 1 name (date) **Key:** p1 **Type:** text **Default:** from_date #### Parameter 1 value set **Key:** vs1 **Type:** text **Default:** FND_STANDARD_DATE #### Parameter 2 name (text) **Key:** p2 **Type:** text **Default:** item_prefix #### Parameter 2 value set **Key:** vs2 **Type:** text **Default:** 15 Characters #### Request group name **Key:** group **Type:** text **Default:** All Reports #### Request group application short name **Key:** group_app **Type:** text **Default:** SQLAP #### Output CSV **Key:** csv **Type:** select **Options:** - Y - N **Default:** Y **Help:** Label only: output type stays TEXT; the file content is CSV. #### Incompatible program short name **Key:** inc_prog **Type:** text **Default:** XX_ITEM_EXTRACT **Help:** Same as the program short name makes it incompatible with itself (one run at a time). Delete the INCOMPATIBILITY call if not needed. **Blocks:** #### Block 1: package spec and body **Body:** CREATE OR REPLACE PACKAGE {{pkg}} AS PROCEDURE {{proc}} (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_{{p1}} IN VARCHAR2, p_{{p2}} IN VARCHAR2); END {{pkg}}; / CREATE OR REPLACE PACKAGE BODY {{pkg}} AS PROCEDURE {{proc}} (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_{{p1}} IN VARCHAR2, p_{{p2}} 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_{{p1}}), 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_{{p2}}); -- CSV header (output CSV = {{csv}}) 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_{{p2}} || '%' 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 {{proc}}; END {{pkg}}; / #### Block 2: check FND_PROGRAM parameter names on your instance **Body:** -- 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 **Body:** BEGIN FND_PROGRAM.EXECUTABLE( executable => '{{prog_user}}', application => '{{app}}', short_name => '{{prog_short}}', description => '{{prog_user}}', execution_method => 'PL/SQL Stored Procedure', execution_file_name => '{{pkg}}.{{proc}}'); FND_PROGRAM.REGISTER( program => '{{prog_user}}', application => '{{app}}', enabled => 'Y', short_name => '{{prog_short}}', description => '{{prog_user}}', executable_short_name => '{{prog_short}}', executable_application => '{{app}}', use_in_srs => 'Y', output_type => 'TEXT'); FND_PROGRAM.PARAMETER( program_short_name => '{{prog_short}}', application => '{{app}}', sequence => 10, parameter => 'P_{{p1}}', description => '{{p1}}', enabled => 'Y', value_set => '{{vs1}}', required => 'N', display => 'Y', display_size => 11, description_size => 50, concatenated_description_size => 25, prompt => '{{p1}}'); FND_PROGRAM.PARAMETER( program_short_name => '{{prog_short}}', application => '{{app}}', sequence => 20, parameter => 'P_{{p2}}', description => '{{p2}}', enabled => 'Y', value_set => '{{vs2}}', required => 'Y', display => 'Y', display_size => 15, description_size => 50, concatenated_description_size => 25, prompt => '{{p2}}'); FND_PROGRAM.ADD_TO_GROUP( program_short_name => '{{prog_short}}', program_application => '{{app}}', request_group => '{{group}}', group_application => '{{group_app}}'); -- Delete if no incompatibility is needed. scope is 'Set' or 'Program Only'. FND_PROGRAM.INCOMPATIBILITY( program_short_name => '{{prog_short}}', application => '{{app}}', inc_prog_short_name => '{{inc_prog}}', inc_prog_application => '{{app}}', scope => 'Set'); COMMIT; END; / #### Block 4: the same setup through the forms **Body:** System Administrator (or Application Developer) > Concurrent > Program > Executable Executable: {{prog_user}} Short Name: {{prog_short}} Application: {{app}} Execution Method: PL/SQL Stored Procedure Execution File Name: {{pkg}}.{{proc}} System Administrator > Concurrent > Program > Define Program: {{prog_user}} Short Name: {{prog_short}} Application: {{app}} Executable Name: {{prog_short}} Output Format: Text Parameters: 10 P_{{p1}} ({{vs1}}), 20 P_{{p2}} ({{vs2}}) Incompatibilities: {{inc_prog}} System Administrator > Security > Responsibility > Request Group: {{group}} Application: {{group_app}} add Program {{prog_user}} **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use it **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What does retcode mean in an Oracle EBS concurrent program? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms how to create concurrent program in oracle ebs, oracle ebs concurrent program retcode, oracle ebs concurrent program csv output, oracle ebs concurrent program date parameter, oracle ebs add concurrent program to responsibility, oracle ebs concurrent program incompatibilities, fnd_program.register example, errbuf retcode oracle apps --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json