If you want to register a Concurrent Program from the backend (without using the System Administrator forms), Oracle EBS provides APIs in the FND_PROGRAM package.
Backend Registration Steps
- Register Executable
- Register Concurrent Program
- Define Parameters
- Add Program to Request Group
- Compile Security Cache
Step 1: Create the Concurrent Program Executable
Use the FND_PROGRAM.executable procedure to register your source script or database procedure.
BEGIN
FND_PROGRAM.executable(
executable => 'XXX_EMAIL_VENDOR', -- Executable Name (User visible)
application => 'XXNICSI', -- Application Short Name
short_name => 'XXX_EMAIL_VENDOR', -- Executable Short Name
description => '', -- Description
execution_method => 'PL/SQL Stored Procedure', -- Execution Method (e.g., Host, SQL*Plus, PL/SQL Stored Procedure)
execution_file_name => 'XXX_EMAIL_VENDOR', -- Database Package.Procedure or physical filename
subroutine_name => NULL,
icon_name => NULL,
language_code => 'US'
);
COMMIT;
DBMS_OUTPUT.put_line('Executable Created Successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error creating executable: ' || SQLERRM);
ROLLBACK;
END;
/
Note: Change execution_method according to your requirement, such as 'SQL*Plus', 'Host', or 'Oracle Reports'.
Step 2: Register the Concurrent Program
Link the executable you created in Step 1 to a newly defined concurrent program using FND_PROGRAM.register.
BEGIN
FND_PROGRAM.register(
program => 'NICSI EMAIL VENDOR', -- Program User Name
application => 'XXNICSI', -- Application Short Name
enabled => 'Y', -- Enabled status (Y/N)
short_name => 'XXX_EMAIL_VENDOR', -- Program Short Name
description => 'Custom Program Registered from Backend',
executable_short_name => 'XXX_EMAIL_VENDOR', -- Short name from Step 1
executable_application => 'XXNICSI', -- Executable Application Short Name
execution_options => NULL,
priority => NULL,
save_output => 'Y', -- Save Output (Y/N)
print => 'Y', -- Allow Printing (Y/N)
cols => NULL,
rows => NULL,
style => NULL,
style_required => 'N',
printer => NULL,
request_type => NULL,
request_type_application => NULL,
use_in_srs => 'Y', -- Visible in Submit Request Screen (Y/N)
allow_disabled_values => 'N',
run_alone => 'N',
output_type => 'XML' -- Output format (TEXT, PDF, HTML, XML)
);
COMMIT;
DBMS_OUTPUT.put_line('Concurrent Program Registered Successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error registering program: ' || SQLERRM);
ROLLBACK;
END;
/
Step 3: Adding Parameters from Backend (Optional)
If your concurrent program requires run-time input parameters, you can add them dynamically via FND_PROGRAM.parameter:
BEGIN
FND_PROGRAM.parameter(
program_short_name => 'XXX_EMAIL_VENDOR',
program_application => 'XXNICSI',
sequence => 10, -- Parameter Sequence Number
parameter => 'p_input_date', -- Parameter Token Name
description => 'Reporting Date', -- Parameter Description
value_set => 'Standard_Date', -- Value Set Name
default_type => 'Current Date', -- Default Type (Constant, Current Date, Profile, etc.)
default_value => NULL,
required => 'Y', -- Is Mandatory? (Y/N)
enable => 'Y',
display => 'Y' -- Visible to User? (Y/N)
);
COMMIT;
DBMS_OUTPUT.put_line('Parameter added successfully.');
END;
/
Step 4: Add Concurrent Program to a Request Group
To make the concurrent program accessible via the front-end Oracle Standard Request Submission (SRS) window, assign it to a designated Request Group using FND_PROGRAM.add_to_group.
BEGIN
FND_PROGRAM.add_to_group(
program_short_name => 'XXX_EMAIL_VENDOR', -- Program Short Name from Step 2
program_application => 'XXNICSI', -- Program Application Short Name
request_group => 'NICSI Reports', -- Target Request Group Name
group_application => 'XXNICSI Custom' -- Request Group Application Short Name
);
COMMIT;
DBMS_OUTPUT.put_line('Program Successfully Added to Request Group.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error assigning to request group: ' || SQLERRM);
ROLLBACK;
END;
/
Verification Query
Verify your backend registration details instantly by executing this SQL select statement:
select * from FND_APPLICATION_VL where APPLICATION_NAME='XXNICSI Custom';
SELECT fcp.user_concurrent_program_name, fcp.concurrent_program_name, fe.execution_file_name, fe.execution_method_code
FROM fnd_concurrent_programs_vl fcp, fnd_executables fe
WHERE fcp.executable_id = fe.executable_id
AND fcp.concurrent_program_name = 'XX_CUSTOM_PROG_SHORT';
Useful Backend Tables
| Object | Table |
|---|---|
| Executable | FND_EXECUTABLES |
| Concurrent Program | FND_CONCURRENT_PROGRAMS |
| Program Translation | FND_CONCURRENT_PROGRAMS_TL |
| Parameters | FND_DESCR_FLEX_COL_USAGE_VL |
| Request Groups | FND_REQUEST_GROUPS |
| Request Group Units | FND_REQUEST_GROUP_UNITS |
Migration Script (Common in EBS)
Many Apps DBAs and Technical Consultants create deployment scripts like:
BEGIN
-- Register Executable
FND_PROGRAM.EXECUTABLE(...);
-- Register Concurrent Program
FND_PROGRAM.REGISTER(...);
-- Add Parameters
FND_PROGRAM.PARAMETER(...);
-- Add to Request Group
FND_PROGRAM.ADD_TO_GROUP(...);
COMMIT;
END;
/
This approach is commonly used during Oracle EBS R12.2.x migrations, cloning, and deployment of custom concurrent programs between DEV, TEST, and PROD environments.
No comments:
Post a Comment