Responsibilities using Concurrent Program

We know to use any Concurrent Program first it needs to be added to the Request Group and Request Group needs to be attached to Responsibility. Then we can able to submit the program using program request form.

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...

SELECT
FRS.responsibility_key
,FRS.responsibility_name
,FRG.request_group_name
,FCP.concurrent_program_name
,FCP.user_concurrent_program_name
FROM
fnd_concurrent_programs_vl FCP
,fnd_request_group_units FRU
,fnd_request_groups FRG
,fnd_responsibility_vl FRS
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

SQL to get list of Scheduled Concurrent Programs

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
,FCP.concurrent_program_name PROGRAM_SHORT_NAME
,FCP.user_concurrent_program_name PROGRAM_NAME
,FNU.user_name SUBMITTED_BY
,TO_CHAR(FCR.requested_start_date
,'DD-MON-YYYY HH24:MM:SS'
) REQUEST_START_DATE
,'Every '|| DECODE(LENGTH(FCL.class_info)
,39,FCL.class_info
,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)
,'N','Minute(s) '
,'D','Day(s) '
,'H','Hour(s) '
,'M','Month(s) '
)
|| 'after '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)
,'S','Start '
,'C','Completion '
)
|| 'of prior request'
) SCHEDULED_INTERVAL
,NVL(TO_CHAR(FCL.end_date_active
,'DD-MON-YYYY'),'forever'
) ENDING_ON
FROM APPS.fnd_concurrent_requests FCR
,APPS.fnd_concurrent_programs_vl FCP
,APPS.fnd_user FNU
,APPS.fnd_conc_release_classes FCL
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
, FCR.requested_start_date

Submitting XML Publisher Report Concurrent Program from PL/SQL

XML Publisher template will be attached as layout when we submit concurrent program using request form based on the program short name. Whereas, if we submit the program using FND_REQUEST.submit_request API then we will find only XML output, template won’t be used.

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.

Object Types in Integration PLSQL API using OA Adapter

If you are planning to use plsql API with plsql collection parameters for any integration using OA Adapter / DB Adapter then use Object Types rather than plsql collections (ie tables and records).

Advantages are

1. 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.
2. 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

This is about DBMS Change Notification, enhanced feature introduced from version 10g.

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.

How BPEL OA Adapter connect to Oracle EBS?

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.

1. JDBC Resource Setups
2. AppsAdapter (ie OA Adapter) Connection Factory Setups

JDBC setups need to be completed first…

1. Get Database TNS Entry and APPS schema password
2. Login as Application Server Enterprise Manager (eg: oc4jadmin) and click oc4j_soa (default OC4J Instance)
3. Go to Administrator tab and click JDBC Resources Go to Task icon
4. Create new Connection Pool by giving name and URL. Follow the URL syntax carefully
5. Enter Credential user as APPS and its password and then test the connection
6. If test connection is success then create Data Source.
7. 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.
8. Note down the JNDI Name, it will be used while defining Connection Factory.
9. Select the above created Connection Pool and test the connection.

Now we can to use this JDBC Resource in OA Adapter Connection Factory Setups.

1. Go to Applications tab in oc4j_soa OC4J Instance
2. Select view as Modules (Default is Applications)
3. Select AppsAdapter Module and go to Connection Factories tab
4. Create new Connection Factory. Give meaningful JNDI Location. It can start with eis/Apps/NAME. For example eis/Apps/EBS.
5. 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.

Java Stored Procedure in Oracle

Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.

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.

Single BPEL Process for Multiple Operations using Pick Activity

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.

1. Get target system WSDL file
2. Create Empty Project
3. Create Partner Link for the WSDL file
4. Select Pick Activity and drag and drop into Process
5. Don’t forget to select Create Instance check box in Pick activity
6. Add OnMessage Branch as much as the number of Operations as you have
7. Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.
8. Add activities and partner links you wanted for the selected operation
   Finally add Reply/Callback activity based on type of Operation.
9. Repeat this for all remaining Operations
10. Deploy into BPEL Server
11. 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.

Validate & Get Values from XML using PL/SQL

There are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using DBMS_XMLDOM API. This sample will read employee name and print it in the console.

DECLARE
lc_return_msg VARCHAR2(1000);
lc_xml_buf VARCHAR2(2000);
lc_xml_value VARCHAR2(2000);
lc_resp_xml XMLType;
lc_xml_doc DBMS_XMLDOM.DOMDocument;
lc_xml_ndoc DBMS_XMLDOM.DOMNode;
lc_xml_docelem DBMS_XMLDOM.DOMElement;
lc_xml_node DBMS_XMLDOM.DOMNode;
lc_xml_childnode DBMS_XMLDOM.DOMNode;
lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;
BEGIN
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Code>001</Code>
<Name>Sales</Name>
<Employees>
<Employee>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<DoB>01/01/1975</DoB>
</Employee>
<Employee>
<FirstName>Adam</FirstName>
<LastName>Ford</LastName>
<DoB>12/03/1983</DoB>
</Employee>
</Employees>
</Department>';

-- 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);

DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);

-- 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
DBMS_OUTPUT.put_line(lc_xml_value);
END;

APPS Context in BPEL OA Adapter

Oracle Application Adapter (OA Adapter) connects to EBS Database as user SYSADMIN and responsibility System Administrator. This needs to be changed for different reasons like Security, Using Multi Operating Units.

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:

1. Create new Message Type Variable for Header_msg in AppsContextHeader.wsdl file.
2. Assign Username, Responsibility & ORG_ID variables
3. Go to Adapters tab in OA Adapter Partner Link Invoke activity and Select the above created new variable as Input Header Variable.