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.