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

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.

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.

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.

Steps to activate User Preferred Time Zone Support

From release 11.5.10 CU2, EBS introduced new feature called User Preferred Time Zone Support. Ie each user can specify their time zone preference, and the system will honor this preference for display and entry of date with time fields. Before 11.5.10 CU2, end users had to interact with the system in time zone set at database level.

To activate this feature following steps to be true or setup properly.

• Database should be Oracle RDBMS 9i or higher
• Environment variable ORA_TZFILE must be configured to use the time zone file timezlrg.dat rather than timezone.dat (Note: database needs to be restarted if this environment is changed).
• The database must be started in the standard corporate time zone
• Profile Server Timezone (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database
• Profile Client Timezone (CLIENT_TIMEZONE_ID) must be set at the user level
• Profile Enable Timezone Conversions (ENABLE_TIMEZONE_CONVERSIONS) must be set to Yes at the Site level
• Profile Concurrent: Multiple Time Zones (CONC_MULTI_TZ) should be set to No at the Site level
• Environment variable FORMS60_APPSLIBS must be set in the Forms tier with APPS standards libraries like APPCORE FNDSQF APPDAYPK...

For more details, refer User Preferred Time Zone Support Documents and metalink notes 330075.1 & 340512.1.

JDeveloper Patch with OA Framework Extension

We know JDeveloper is used for OA Framework (OAF) extensions and customizations for self service forms. This note gives the instruction on how to find right JDeveloper patch for creating OA Extensions with EBS Release 11i and 12.

EBS has lots of versions like 11.5.9, 11.5.10, 12 etc. Developer should use the correct version of JDeveloper with OAF extension. When you create extensions or customize EBS OA Framework pages, you must use the version of JDeveloper shipped by the EBS product team. The version of JDeveloper is specific to the ATG patch level, so there is a new version of JDeveloper for each release of ATG patch-set.

To determine which patch to use, you can check the framework version in your instance by using http://(host):(port)/OA_HTML/OAInfo.jsp, then choose the matched JDeveloper patch.

For Example if the instance base URL is http://vis.oracle.com:8080/ then type http://vis.oracle.com:8080/OA_HTML/OAInfo.jsp. Note down OA Framework Version. Go to Metalink Note 416708.1, find out the JDeveloper patch matching with OAF version and download. Unzip the downloaded file.

We can also find useful informations in metalink.oracle.com.

Export from APPS

In Apps existing export function can be used to export the information in the current block. The following solution can be used to export not only from the specific block, can be used to based the information available in the form or not available in the form.

Approach:
- Build a new package using few web toolkit APIs (refer the sample below)
- This package has to be registered as Web Plsql to access as web toolkit.
- Create new function to call the above package as type "SSWA plsql function" and HTML Call as above API.
- Execute the created new function from wherever export needed. It can be from button in a form or can be a special menu

Sample API:
CREATE OR REPLACE PACKAGE BODY EXPORT IS

PROCEDURE main (
p_user_id NUMBER
) IS

lc_line VARCHAR2(4000);
lc_mime_type VARCHAR2(100) := 'text/csv';
lc_char_set VARCHAR2(100) := NULL;
ln_length NUMBER := 0;
lc_blob BLOB;
lc_new_line VARCHAR2(10) := CHR(10);

CURSOR lcu_users IS
SELECT user_name
,start_date
,end_date
,email_address
FROM fnd_user
WHERE user_id = p_user_id;

BEGIN

FND_GLOBAL.apps_initialize(FND_PROFILE.value('USER_ID')
,FND_PROFILE.value('RESP_ID')
,FND_PROFILE.value('RESP_APPL_ID')
);

DBMS_LOB.CreateTemporary(lc_blob, TRUE, DBMS_LOB.SESSION);

FOR lr_users IN lcu_users
LOOP

lc_line := lr_users.user_name ','
lr_users.start_date ','
lr_users.end_date ','
lr_users.email_address lc_new_line;

-- Covert into RAW
lc_line := utl_raw.cast_to_raw(lc_line);
ln_length := utl_raw.length(lc_line);

-- Write the data into BLOB
DBMS_LOB.WriteAppend(lc_blob, ln_length, lc_line);

END LOOP;

-- Set Mime Type
OWA_UTIL.mime_header(lc_mime_type, FALSE, lc_char_set);
htp.p( 'Content-length: ' DBMS_LOB.getlength(lc_blob));
OWA_UTIL.http_header_close;

-- Download it as CSV
WPG_DOCLOAD.download_file(lc_blob);

EXCEPTION
WHEN OTHERS THEN
HTP.htmlOpen;
HTP.headOpen;
HTP.title('404 Not Found');
HTP.headClose;
HTP.bodyOpen;
HTP.hr;
HTP.header(nsize=>1, cheader=>SQLERRM);
HTP.hr;
HTP.p(FND_MESSAGE.Get_String('GMD','LM_BAD_FILENAME'));
HTP.bodyClose;
HTP.htmlClose;
END main;
END EXPORT;

- Role vs Responsibility -

The Responsibility has been used not only to define the application navigation menus, but also to define privileges and permissions. Using this definition of responsibility, it is necessary to create several similar responsibilities in order to effectively carve out data and functional security access for a group of users.

Going forward (from EBS 11.5.10 onwards), Users will no longer need to be directly assigned the lower level permissions and responsibilities, as these can be implicitly inherited based upon the roles assigned to the user. Roles can now be defined to consolidate responsibilities and other roles through role inheritance, as well as lower level permissions (functions) and data security policies.

The benefits of implementing Role Based Access Control (RBAC) are
- Structured user access control
- Reduced cost of administering user access control system
- Streamlined setup and implementation of security policies and rules

Refer System Administrator's Guide – Security document for more details.

- Invoking BPEL Process using Pl/SQL -

BPEL process can be invoked from Oracle database in different ways. Following sample can be used to invoke BPEL process using SOAP and pl/sql APIs.

- Change Provider URL, WS name in the sample code given below
- This program uses only standard String Input parameter. Make the necessary changes if WSDL has different parameter(s).

DECLARE
lc_soap_request VARCHAR2(3000);
lc_soap_respond VARCHAR2(3000);

lc_input VARCHAR2(5) := 11; -- Changes this as per BPEL Process (WSDL) definition
lc_namespace VARCHAR2(128) := 'xmlns="http://xmlns.oracle.com/<WS Name>"'; -- Replace <WS Name> with BPEL Process
lc_ws_request VARCHAR2(50) := <BPEL Process Request> --Get it from WSDL file

lc_http_req UTL_HTTP.req;
lc_http_resp UTL_HTTP.resp;
BEGIN

lc_soap_request := '<?xml version = "1.0" encoding = "UTF-8"?>'
||'<SOAP-ENV:Envelope '
||'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" '
||'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" '
||'xmlns:xsd="http://www.w3.org/1999/XMLSchema">'
||'<SOAP-ENV:Body>'
||'<'||lc_ws_request||' '||lc_namespace||'>'
||'<input '||lc_namespace||'>'||lc_input||'</input>'
||'</'||lc_ws_request||'>'
||'</SOAP-ENV:Body>'
||'</SOAP-ENV:Envelope>';

lc_http_req := UTL_HTTP.begin_request(
'<URL>' -- Get this from BPEL Processes->WSDL->Endpoint Location OR this can be get it from WS ESB definition tab
,'POST'
,'HTTP/1.1'
);

UTL_HTTP.set_header (
lc_http_req
, 'Content-Type'
, 'text/xml'
);

UTL_HTTP.set_header (
lc_http_req
, 'Content-Length'
, LENGTH(lc_soap_request)
);

UTL_HTTP.set_header (
lc_http_req
, 'SOAPAction'
, 'process'
);

UTL_HTTP.write_text(lc_http_req, lc_soap_request);

lc_http_resp := UTL_HTTP.get_response(lc_http_req);

UTL_HTTP.read_text(lc_http_resp, lc_soap_respond);

UTL_HTTP.end_response(lc_http_resp);

DBMS_OUTPUT.put_line(substr(lc_soap_respond,1 ,250));
DBMS_OUTPUT.put_line(substr(lc_soap_respond,251,500));

END;

Limitations:
- Exceptions/Fault handling should be controlled manually
- Program will just initiate the Web Service. Auditing or back tracking should be handled manually
- This approach might not be as per Oracle EBS standards. Please check EBS and BPEL documents for more information related to standards

- R12 Installation with External Hard Disk & XP -

Installed R12 in my laptop recently, thought of sharing my experience to everyone.

I followed Hsawwan’s thread from beginning and installed successfully in few hours.

Hardware & Software Specifications:

• Intel Core 2 Duo, 2GHz
• 3GB of RAM
• 160 GB Hard Drive + 80 GB External hard drive
• Windows XP Professional with Service Pack 2

Challenges Faced:

• Not able to ping my machine either using domain name or IP address. After couple of hour’s investigation, found it was because of enabled Norton Internet Security. I disabled it and followed the installation steps.
• Installation System check failed when I entered UNIX Toolkit base path of cygwin. It should include bin directory as well. Eg C:/cygwin/bin. Whereas for VC++ we should not include bin, it should be upto VC base path.
• As I had only 160 GB HDD, used external HDD. Modified the DB data file path to use my external HDD for Archive, Log. Make sure to keep all system related files in internal HDD to improve the performance.

Installation took around 3 hours and 30 min to see login screen. Performance is very good so far.

Ref: http://forums.oracle.com/forums/thread.jspa?threadID=494790&tstart=0

- Projects R12 New Features & Enhancements -

Following are highlights of Oracle Projects new features and enhancements in Oracle E-Business Suite (EBS) Release 12. Most of the features listed here are available from Oracle Projects Family Pack M.

Project Costing

• Labor Cost Processing for Contingent Workers - Timecard and PO can be linked
• Changes with respect to Oracle Payables Invoice Lines new feature
• Adjust supplier cost in Oracle Project Costing under certain conditions
• New mass adjust concurrent process
• Oracle Payables now provides a drilldown to the Project Expenditure Inquiry window from the invoice workbench.
• Integration with Oracle Project Manufacturing

Project Billing

• Integration with Oracle E-Business Tax
• Customers can now be attached at Top Task
• Invoice Method at Top Task: Enables to invoice customers with different methods such as, fixed price and time and materials, within the same project.
• Invoice Write-Off at line level
• Invoice Concessions for credit memos
• Project functional currency conversion attributes
• Enhanced Billing Review with New Interface

Project Foundation

• Integration with Oracle Sub-ledger Accounting
• Multi-Org Access Control
• Separation of Reporting and Planning Resources
• Increased granularity for planning and reporting against inventory items and manufacturing resources through increased integration with SCM.
• Resource Breakdown Structure enhancements
• Define and utilize contingent workers on projects in the same capacities and manner as employees.
• Enhanced Configurability of Project Home, Project List, Workbench and Page Layouts
• Element level options while coping a project to create another project

Oracle Project Management

• Supports dependencies between tasks within a single project or across projects.
• Now can assign planning resources to tasks, including people, equipment, material items, and financial resources.
• Can associate workflow to tasks that can be configured to send notifications depending on various businesses.
• Expanded Integration with Microsoft Project
• Can define the deliverables for a project, including all the associated actions needed to complete each deliverable.

Oracle Project Portfolio Analysis

Project Portfolio Analysis is a new product helps companies align their portfolios of projects and programs with their organizational objectives by taking advantage of Oracle Project’s standard project criteria, project scoring and ranking, and what-if portfolio scenarios.