Oracle Applications Forms R12

Components involved??

Oracle forms server works based on 3 Tier Architecture. A tier is a logical collection of services running on more than one machine.

Client Tier:

 The client browser connects to the form server using the forms client Applet which is running on the local machine. Forms client applet is a collection of Java Archive files (JAR).

Application Tier:

Application tier mainly has 2 components web listener and forms server.

·         Forms server :

o   Maintains all user interfaces and database connection for thin client.

o   Caches data which is needed by forms thin client.

o   Uses HTTP, HTTPS and TCP/IP protocols to communicate with the thin client.

·         Web Listener

o   Sends and receives request for the browser which is running the Jinitiator.

Database Tier:

·         Stores all the data required for the forms thin client.

How it works??

1.       Browser sends request to HTTP Listener.

2.       Web server receives the request.

o   If request is referring to a static file then the file will be retrieved from storage.

o   If request is referring to CGI Script then

§  Static CGI : Retrieve from storage and execute the file.

§  Dynamic CGI : Checks for the available server from load balancing server. Load balancing server basically monitors the current load in all forms server. Forms server runs a thin client of load balancing server to keep the load balancing server informed about the load.

3.       HTTP Listener sends a HTML page to the browser.

o   HTML file has a tag <APPLET> which contains all the information including form server name to be used.

4.       Browser requests for Java Applet to HTTP Listener.

5.       HTTP Listener sends Applet in JAR Files as they can be downloaded more quickly.

6.       Browser receives the JAR files and uses the JVM JInitiator to execute the JAR file.

7.       Browser signs off as the JVM takes care of the Java Applet.

8.       Applet connects to the forms listener which starts a Forms Runtime Engine.

9.       Forms listener passes the command line parameters and any form builder parameters to Forms Runtime Engine.

10.   Forms listener signs off as the Applet is now directly connected to Forms Runtime Engine.

11.   Java applet passes the form information with the required parameters to the Forms Runtime Engine.

12.   Forms Runtime Engine interacts with the database tier to open a database connection to get the data.

Client browser and JRE Version

Below table lists out the JRE version for the corresponding client browser and the information is available in the Oracle support “Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12 (Doc ID 393931.1)”. Also any form related issues are addressed in the same Oracle Doc ID.

64 Bit version

Browser Version	Windows 8(64 Bit)	Windows 7 (64 Bit)
IE 10	JRE 1.6.0_37 (64-bit) and higher JRE 1.7.0_10 (64-bit) and higher	JRE 1.6.0_32 (64-bit) and higher JRE 1.7.0_10 (64-bit) and higher
IE 9	Not Certified	JRE 1.6.0_32 (64-bit) and higher JRE 1.7.0_10 (64-bit) and higher
IE 8	Not Certified	JRE 1.6.0_32 (64-bit) and higher JRE 1.7.0_10 (64-bit) and higher

 32 Bit Version

Browser Version	Windows 7 (64-bit)1	Windows 7 (32-bit)	Windows Vista (32-bit)	Windows XP (32-bit)
IE 10	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	Not Certified	Not Certified
IE 9	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	Not Certified
IE 8	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher
IE 7	Not Certified	Not Certified	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher
IE 6	Not Certified	Not Certified	Not Certified	JRE 1.6.0_03 and higher JRE 1.7.0_10 and higher

WebADI as Automation Tool

Overview

WebADI is a web based browser application which helps in integrating the desktop applications MS Word, MS Excel and MS Projects with Oracle E-Business. WebADI increases the productivity as the user need not change the user interface which are familiar to the user. Scope of this blog is to highlight 2 scenarios which helped users in reducing manual tasks using WebADI.

Advantages of WebADI

·         Data exchange between the server and client application happens through HTML.

·         MS Excel features like writing formulas, generating graphs and altering data can be used.

·         Data values can be validated against Oracle EBS.

·         Need not install any software in the client machines.

·         Multiple sheets also supported from R12.

·         Supports Single Sign on.

Web ADI Profile Options

·         BNE Allow Set Parameters:  If set to “NO” then users will not be able to use the upload parameters window.

·         BNE Disable: Set to “Yes” to disable Web ADI functionality.

·         BNE Document Lifetime: Specifies the time frame the Web ADI spreadsheet can be reused again to connect to E-Business Suite.

Scenario 1

Problem Description:

Client was using Oracle Financial 11i version for all of their core financial business process. Testing team was having issue with adding the list of responsibility after every clone. Testing team users hardly have 2 or 3 responsibilities in production compared to the development environment where they have around 30 responsibilities. Issue was not only with the testing team but with the complete development team.

Solution:

1.       Define Integrator using the package BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR

2.       Associate Form Functions with Integrator. Web ADI will check if the user has access to the form functions using package BNE_SECURITY_UTILS_PKG

3.       Create interface using BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_API to attach a PLSQL procedure which calls the FND_USER_RESP_GROUPS_API to load the responsibility.

4.       Create Interface Columns for every parameter used in the PLSQL procedure.

5.       Define the layout for the user with the required information like “Responsibility Name”, “Start Date”.

6.       Define the mapping to map the columns between the Spreadsheet and the PLSQL procedure.

7.       Save the work.

Result:

Users can now maintain an excel sheet with the list of responsibilities with the “Start Date” and “End date”.  After every clone users can run the webADI to get back their responsibilities in no time (reduces manual/repeated task).

Scenario 2

Problem Description:

Client is a big online transaction company where we have around 30 Order types in sales order. Every month there will be some changes on the order process program, OM Order workflow or the reports which are sent to customers. Functional users have to take the pain of creating sample orders for all the 30 order types to make sure the existing business functionality is not disturbed due to the change in the code done by technical team.

Solution:

1.       Define Integrator using the package BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR

2.       Associate Form Functions with Integrator. Web ADI will check if the user has access to the form functions using package BNE_SECURITY_UTILS_PKG

3.       Create interface using BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_API to attach a PLSQL procedure which calls the custom package to validate and load the data in the Sales Order interface.

a.       PLSQL procedure will move the data to the staging table.

b.      After successful validation of the data. Add a new row in excel with “Record Type” as “I” to call the standard oracle order import process.

4.       Define the layout.

5.       Define the mapping to map the columns between the Spreadsheet and the PLSQL procedure.

6.       Save the work.

Result:

Functional user team work was reduced by 80% as they were able to create the orders using the webADI and check the business process much quickly.

Custom Top Automation Script

Custom Top Automation Script
We had requirement to create a new custom top in a new instance. CUSTOM Top includes reports and shell scripts to be migrated into the new instance. Also we need to move the specific users with their responsibilities who will be accessing the CUSTOM Top.
Below program layout gives the flow of the shell script which needs to be executed in the destination instance.
Program Layout.
1. Get the apps credentials for logging into the database.
2. Create required directories for the custom TOP
3. Copy the relevant files into the custom TOP with necessary permissions.
4. Load the Application using FNLOAD program.
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscapp.lct <<application_ldt_file>>
5. Load the Concurrent programs using FNLOAD program
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct <<concurrent_Program_ldt_file>>
6. Load the custom profiles if any...
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct <<profile_ldt_file>>
7. Load the custom request group
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct <<custom_request_group_ldt_file>>
8. Load the custom Menu
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <<Custom_menu_ldt_file>>
9. Load the custom responsibility
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <<Custom_responsibility_ldt_file>>
10. Load the custom user
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <<custom_user>>
11. Create Symoblic links for the shell programs if any
ln -s $FND_TOP/bin/fndcpesr <<shell_program>>
12. If there are any BI Reports then load the BI Definitions
FNDLOAD $APPS_NAME/$APPS_PASSWORD 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
13. Load the templates for the corresponding BI Reports
java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME $APPS_NAME \
-DB_PASSWORD $APPS_PASSWORD \
-JDBC_CONNECTION $HOST_NAME:$DB_PORT:$DB_SID \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME <<APPL_SHORT_NAME>> \
-LOB_CODE <<LOB_CODE>> \
-LANGUAGE en \
-TERRITORY 00 \
-XDO_FILE_TYPE RTF \
-FILE_NAME <<FILE_PATH>> \
-NLS_LANG AMERICAN \
-CUSTOM_MODE FORCE
15. Install PLSQL packages if any
sqlplus $APPS_NAME/$APPS_PASSWORD @<<SQL_FILE_NAME>>

RTF and RDF Encryption Techniques

This Blog describes the approach adopted to encrypt the RTF Template of BI Publisher stored in the Oracle Database. This Blog explains more on the RTF encryption assuming the audience has the working knowledge of Oracle BI Publisher.

Descriptions

Oracle BI Publisher is a reporting solution introduced by Oracle for better management and delivery of the reports. Oracle BI Publisher is a Java based reporting tool that comes within the technology stack of Oracle E-Business Suite. BI Publisher separates the Data layer and presentation layer which is something different from the Traditional Oracle reporting tool. Oracle BI Publisher Uses the Oracle Report for data query portion and ignores the report layout.

Requirement:

Oracle BI Publisher report has 2 components which are oracle report and template definition for the XML output generated from the Oracle Report. Oracle report was secured using the rwconverter.exe which converts the rdf files to rep files using the command below

Rwconvertor userid=<<apps_username>>/<<Password>> batch=yes source=<<rdf_path>> stype= rdffile dtype=repfile overwrite=yes compile_all=yes

Figure 1:  Command for rwconvertor

Templates which are defined in the Template Definition screen as shown below

     Above screenshot displays a sample template defined in the Payments module. All the rtf templates are stored in the XDO_LOBS.file_data column in the database which is of datatype CLOB. Oracle 11i didn’t have the features of encrypting the data which are of type CLOB but was capable of encrypting VARCHAR datatype using the public API DBMS_OBFUSCATION_TOOLKIT. Oracle 10g introduced a new package DBMS_CRYPTO which was installed in the sys schema. DBMS_CRYPTO main features are listed below and complete details on the package can be viewed on http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm

DBMS_CRYPTO Features:

• Supports RAW,CLOB and BLOB Dataypes
• Advanced Encryption Standards
• Random generation of encryption keys.
• VARCHAR2 can also be encrypted by converting the data into RAW.

 

Implementation Method:

DBMS_CRYPTO.ENCRYPT procedure was used to encrypt the source clob stored in the XDO_LOBS table. Below steps explains the flow of the code logic.

• Get the clob data for the rtf template to be encrypted.

SELECT file_data

INTO   lb_srcblob

FROM   xdo_lobs

WHERE  file_name = <<RTF File name>>

AND    lob_code  = <<Concurrent program shortname>>

• Create a temporary clob to store the encrypted clob in the XDO_LOBS Table.

DBMS_LOB.CREATETEMPORARY(<<variable for encrypted clob>>,

                           TRUE,

                           DBMS_LOB.CALL);

  DBMS_LOB.OPEN(<<variable for encrypted clob>>,

                DBMS_LOB.LOB_READWRITE);

• Call the DBMS_CRYPTO.ENCRYPT procedure to encrypt the source clob.

DBMS_CRYPTO.ENCRYPT(<<variable for encrypted clob>>,

                    <<variable for source clob>>,

                        DBMS_CRYPTO.AES_CBC_PKCS5,àEncryption Technique

                        hextoraw ('101112131415161718191A1B1C1D1E1F00010203040123413123AECCAEBF') à Encryption key,

                        hextoraw('00000000000000000000000000000000') à optional parameter for block ciphers

);

• Update the encrypted clob value in the XDO_LOBS.file_data table.
• Download the encrypted rtf from the Template Definition window and try to open the same in the Word document. RTF template will not be able to open as the template is encrypted

Troubleshooting methods for Oracle Workflow

This post describes various methods of debugging oracle workflow. Oracle workflow is generally used to integrate the ERP business process into Oracle applications

Descriptions

Oracle workflow is a complete solution for integrating End to End business process within ERP. Oracle workflow lets people receive emails about their activities and also sends remainder mails to the document approvers. Oracle workflow builder helps to define the workflow process and integrate all the processes to define the business process.

Initial Level Checks:

1. Oracle workflow requires the Workflow Background process to be scheduled for every 10 minutes in the system with the following parameters:

• Y,N,N
• N,Y,N
• N,N,Y

1. First get the concurrent program ID:

SELECT concurrent_program_id

FROM fnd_concurrent_programs_tl

WHERE user_concurrent_program_name  = 'Workflow Background Process';

2. Check for the programs frequency of execution:

SELECT request_date,actual_date,actual_completion_date,phase_code,status_code

FROM fnd_concurrent_requests

Where concurrent_program_id = <concurrent_program_id>

AND argument_text = ', , , N, Y, N'

AND resubmit_interval = 10

AND resubmit_interval_unit_code = ‘MINUTES’

Order By 1 Desc

3. Check whether the program is scheduled and running for every 10 mins

4. Similarly repeat step 2 for other arguments.

Figure 1:  Queries for checking the workflow Background Process

2. Check whether all the agent listeners are up and running as shown below:

     Navigation Path:

a.      Go to ‘Workflow Administrator Web Applications’ responsibility and click on ‘Workflow Manager’ as shown below.

Figure 1: Navigation to Oracle Workflow Manager

b.      You will be guided to the following page and here all of the highlighted must be up.

Figure 2:  Workflow Components Dashboard

After undergoing the initial level checks we need to categorize the workflow issue to any one of the below category:

1. Workflow not running
2. Notifications not being fired.

Workflow not running:

 

We assume that the workflow has been initiated and it’s not running further. We need to get the workflow name and itemkey for the workflow not running. Itemkey is a key to identify the workflow instances below are some of the examples for Itemkey

 OM Header workflow    

SELECT header_id FROM oe_order_headers_all

WHERE order_number = <order_number>

AND   org_id = <Organization of the order>;

OM Line level workflow

SELECT line_id FROM oe_order_lines_all

WHERE header_id = <order header_id>

AND   org_id = <Organization of the order>;

PO Approval Workflow

SELECT wf_item_key FROM po_headers_all

WHERE segment1 = <PO Number>

AND   org_id = <Organization of the order>;

 

Requisition Workflow   -

SELECT wf_item_key FROM po_requisition_headers_all

WHERE segment1 = <REQ Number>

AND   org_id = <Organization of the order>;

After getting the workflow name and itemkey for the workflow which is not running follow the below steps:

·         Go to workflow status monitor.

Figure 3:  Navigation to Workflow Status Monitor

·         Enter the Workflow type and Itemkey of the workflow

·         Workflow may be in Active/Error/Complete/Defferred status

Ø       Complete - Workflow has successfully completed.

Ø       Error       - Workflow has error out. 

Ø       Active      - Workflow is still active.

Ø       Defferred - Workflow is waiting to be picked up by workflow

                       background engine.

        

      Active:

·         Select the workflow which we need to troubleshoot and click on the activity history.

·         Ensure that the recent activity is not in deferred status if so run the workflow background process.

·         Click on the activity to get the details

Figure 4:  Activity Details of Workflow Activity

·         If the function type is PL/SQL then debug the package mentioned in the Function column (e.g OE_STANDARD_WF.STANDARD_BLOCK).

Retry

·         Select the recent activity and click on the retry button if the activity shows as error to restart the workflow.

·         If the error still exists then click on the error to debug the package as done for the active status.

Deferred

·         If the recent status is deferred then check the workflow background engine status. Also check the deferred queue in the wf_deferred_table_m for the specific workflow

Select * from wf_deferred_table_m where corrid = ‘APPS’ + <item_type>;

Notifications not getting fired:

  All the workflow notifications are stored in the WF_NOTIFICATIONS table. 

Select * from WF_Notifications where subject = <Subject of the notification>;

Column Descriptions:

• Mail_status:

ü       Sent: - Mails are sent to the recipients.

ü       Error: - Mails are not delivered to the recipient may be due to invalid email address.

• Status:

ü       Open: - Mails have been sent to the recipient and not yet viewed by the user.

ü       Closed: - Mail has been viewed by the recipient.

ü       Error: - Mail server is unable to deliver the message.

ü       Cancelled :- Workflow got cancelled

ü       Timeout :- Notification got timed out

• Context: It has the itemtype and itemkey separated by Colons.
• Recipient Role : It has the recipient roles

Select * from WF_roles where name = <recipient_role>;

Select * from wf_user_roles where role_name = <recipient_role>;

After analyzing the columns of the wf_notifications, wf_roles, & wf_user_roles we can get the reason for the notifications not getting fired.

Common Reasons:

• Recipient has left the company so the role has expired.
• Invalid email address.
• Email address may be different from the HR tables in such cases run the concurrent program “WF Synchronize Local Tables”
• If emails got struck in the mail server contact the DBA team.
• If emails are not sent to the correct recipients then check the setups.
• Notification preference of the recipients may be Query/Summary which will not send email notification but can be viewed from the recipient notification worklist.

Other workflow Tips:

• Always clear the cache if you are not able to open the notifications listed in the notification worklist.
• If you are not able to view the workflow status diagram of the workflows owned by other users then set the workflow Administrator privilege to “*”.

Figure 5:  Workflow Configuration Page

• “Purge Obsolete Workflow runtime data” concurrent program should be running every day to improve the performance of the workflow.
• Use the Set Override address in Test instances to check the email notifications.