One program can be attached to many Request Groups. Again Request Group can be attached to many Responsibilities. Because of One-Many-Many relationship, it is very difficult to find out responsibilities using particular concurrent program in UI forms.
But we can get the list from database using the following SQL...
WHERE FCP.concurrent_program_name = (Program_Name)
AND FRU.request_unit_id = FCP.concurrent_program_id
AND FRG.application_id = FRU.application_id
AND FRG.request_group_id = FRU.request_group_id
AND FRS.group_application_id = FRG.application_id
AND FRS.request_group_id = FRG.request_group_id
Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.
SELECT FCR.request_id REQUEST_ID
,'Every '|| DECODE(LENGTH(FCL.class_info)
|| 'after '
|| 'of prior request'
FROM APPS.fnd_concurrent_requests FCR
WHERE FCR.phase_code = 'P'
AND FCR.status_code IN ('I','Q')
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCR.requested_by = FNU.user_id
AND FCR.release_class_app_id = FCL.application_id
AND FCR.release_class_id = FCL.release_class_id
ORDER BY FCP.concurrent_program_name
To avoid this issue, program layout needs to be set using FND_REQUEST.add_layout API with template application, code, language, territory and output format before submit request.
- Adapter can not use plsql tables and records directly. It will create similar database Object Type and use that in xsd creation, transformation etc. It also creates package to assign plsql collection into object types. This can be avoided if we use database object.
- If there are any changes then just we need to change the object type and respective xsd. If we use plsql tables and records then we have to make changes in too many places. Some time it creates major issues in particular if we have complex transformations and assignments.
DBMS Change Notification can only be used for tables those are having length (including schema name) less than 30 characters. For example, Oracle Project module PA.PA_STD_BILL_RATE_SCHEDULES_ALL table length is more than 30, so this table can not be used for DBMS Change Notification.
Also DBMS Change Notification treats particular change as bulk if numbers of changed records are more than 80. In this case ROWID will be empty. So we won’t know the affected records.
In this article, I listed the steps to setup the connection factory for OA Adapter to connect to EBS. There are two major portions in this setup.
- JDBC Resource Setups
- AppsAdapter (ie OA Adapter) Connection Factory Setups
JDBC setups need to be completed first…
- Get Database TNS Entry and APPS schema password
- Login as Application Server Enterprise Manager (eg: oc4jadmin) and click oc4j_soa (default OC4J Instance)
- Go to Administrator tab and click JDBC Resources Go to Task icon
- Create new Connection Pool by giving name and URL. Follow the URL syntax carefully
- Enter Credential user as APPS and its password and then test the connection
- If test connection is success then create Data Source.
- Select Data Source Type as Managed Data Source. Enter data source name and JNDI location. JNDI Location can be start with jdbc/NAME to maintain the consistency.
- Note down the JNDI Name, it will be used while defining Connection Factory.
- Select the above created Connection Pool and test the connection.
Now we can to use this JDBC Resource in OA Adapter Connection Factory Setups.
- Go to Applications tab in oc4j_soa OC4J Instance
- Select view as Modules (Default is Applications)
- Select AppsAdapter Module and go to Connection Factories tab
- Create new Connection Factory. Give meaningful JNDI Location. It can start with eis/Apps/NAME. For example eis/Apps/EBS.
- Enter xADataSourceName as JDBC JNDI Name just created.
Now OA Adapter can be used in BPEL Process. Make sure same JNDI Name is entered when Partner Link is created for OA Adapter in JDeveloper.
Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.
Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.
Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.
CREATE DIRECTORY Bfile_dir AS '/usr/temp';
Store the class file into database using following syntax.
CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');
loadjava executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.
CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Addition.get(int,int) return int';
I used Oracle 10g Release 2. Refer Oracle Database Java Developer’s Guide for more details.
Usually we will create individual BPEL Processes for each Operation in either synchronous or asynchronous. This blog will help to build single BPEL Process for more than one Operation for exceptional cases like requesting system might have restricted number of adapters or ports to connect to other systems. This can be done using Pick activity.
- Get target system WSDL file
- Create Empty Project
- Create Partner Link for the WSDL file
- Select Pick Activity and drag and drop into Process
- Don’t forget to select Create Instance check box in Pick activity
- Add OnMessage Branch as much as the number of Operations as you have
- Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.
- Add activities and partner links you wanted for the selected operation
Finally add Reply/Callback activity based on type of Operation.
- Repeat this for all remaining Operations
- Deploy into BPEL Server
- Initiate the process from where ever you want. Note that this process can not be initiated from 10g BPEL Console. May be this is a bug.
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
-- Validate XML
lc_resp_xml := XMLType(lc_return_msg);
-- Create DOMDocument Handle
lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);
lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);
-- Get all elements
lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);
-- Get Result Elemement
lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');
-- Get Second Employee Name
lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);
lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);
lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);
-- Print second employee name
If user and responsibility are know and won’t change then this can be hardcoded in OA Adapter Partner Link Created. Search for element Responsibility, change the Username, Responsibility and save the file.
If Contexts are dynamic then follow these steps:
- Create new Message Type Variable for Header_msg in AppsContextHeader.wsdl file.
- Assign Username, Responsibility & ORG_ID variables
- Go to Adapters tab in OA Adapter Partner Link Invoke activity and Select the above created new variable as Input Header Variable.