Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, September 26, 2015

How to Setup and Verify Audit Changes to Orders using Audit History

In Order Management, Audit History tracks record Update, Cancel and Split.
Note: When a processing constraint is defined for "update" operation, it is applicable only for changes done on an already saved order. If user is changing any attribute before the Order / Line is even saved, system considers all those changes to be part of "create" operation only.

Note: Delete is not tracked through version Audit History. This is not an issue as instead of deleting, users can cancel the order line, which is tracked through Audit.

Setup Steps
The following example illustrates how to audit when a change is made to the Payment Terms of an order that is booked with a reason as to why it was changed. The steps to initiate Audit History are:
1. Turn Audit History on at the site level.
     a) Navigation: Order Management Super User > Setup > System Parameters > Values
     b) Change "Audit Trail" from Disable.
There are two choices:
Enable When Order is Entered: This will initiate auditing when a change is made to a specified field after the order has been Saved.
Enable When Order is Booked: This will initiate auditing when a change is made to a specified field after the order has been Booked.
2.  Identify a specified field when changed that will be audited.      

     a) Navigation: Order Management Super User > Setup > Rules > Security > Processing Constraints
     b) Setup Processing Constraints for Order header or Line attribute for UPDATE or CANCEL operation that requires
          1) Required Reason and History - a reason window will always appear and a reason with optional comments must be entered. History will be captured.
          2) Require History - a reason window will not appear when making a change and history will be captured. There is always the flexibility of entering a reason by navigating to Tools > Change Reason.
          3) Require Reason, History and Raise Integration Events
     c) Position your cursor on the field to be audited in the constraints block and click the plus icon in the toolbar. This will add a new row where information can be entered.
     d) You can then change to the Conditions tab in the form and select: Scope, Order Validation template.
     e) Next navigate to the applicable tab. The audit can be associated with a specific responsibility.  "All Responsibilities" can be selected.

3. Run Generate Constraints Validation Packages

     a) Navigation: Order Management Super User > Setup > Rules > Security > Generate Constraints Validation Packages 
4. Now test the Audit Trail defined. 
     a) Enter Sales Order header and Line Information and Save as usual.
     b) After you saved the Sales Order, try to change the value of the field on which Processing Constraints.

5. In order to obtain data in the Audit History Report it is necessary to run on a regularly scheduled basis a consolidation program that will extract the audit information and add it to a history file.
     a) Navigation: Order Management Super User > Reports,Request > Run Requests > Select Audit History Consolidator (OEXAUGEN).
     b) Data can be extracted based on History Date From and To, Order Number From and To, or Changes in last N days.
     c) Enter 1 in the Changes in last N days.
     d) To view the data either an audit report. Navigation: Order Management Super User > Reports,Requests > Reports > Select Audit History.
     e) Report can be created based upon History Date From and To, Order Number From and To, Entity Name, Attribute, User, and Responsibility.
6. To view history online, Navigation: Order Management Super User > Orders, Returns > View Audit History and select the data you want to view.

How to track Comments field In Sales Order Form > Line tab
Comments and Reason fields on the Lines are never stored in OE_ORDER_LINES_ALL table. They are used by Processing Constraints (Auditing) frame work to store the reason/comments for each change being done by users.  Hence when ever a Line is saved, these fields are always cleared and made as blank, to again capture the reason/comments for any new changes.  This is intended behavior. Users will have to use DFF to store their comments.

You can see Comment field via View Audit History form:
Run: Audit History Consolidator
Order Management Responsibility:
Orders, Returns -> View Audit History
Query on Order Number: 
Go to Lines Tab
Put Cursor on each line..
You can see , bottom of the form,
Reason : Item Description: Comments:...

Audit Trail Setup Document
You can download the Audit Trail Setup document.  The document goes through each step, along with screen shots of each step.  It shows the setup of capturing Audit Trails on the Schedule Ship Date, but you can use these same steps for any field.

How to Verify Audit History changes:
Check the data in attribute change in Tables
When OM system parameter: Audit Trial is enabled and proper processing constraints are defined for the auditable attributes, the relevant history tables are populated with the history data.
         OE_ORDER_HEADER_HISTORY - Table that stores history of attribute change for Order Headers
         OE_ORDER_LINES_HISTORY  - Table that stores history of attribute change for Order Lines
         OE_PRICE_ADJS_HISTORY   - Table that stores history of attribute changes of Entity Price.
         OE_SALES_CREDIT_HISTORY - Table which stores history of attribute changes of Entity Sales Credits

     For example: If the processing constraint is setup for the
         Application: Order Management.
         Entity: Order Line and
        Attribute: Sales Person

     Select  *  from OE_ORDER_LINES_HISTORY 
     Where header_id = &Enter Order header for  which the changes were made.
     and line_id = &Enter line_id of the Order on which changes were made.

Similarly if the processing constraint was set up for the attributes on the ‘Order header’  or ‘Price adjustments’ window or ‘Sales Credits’ entity, the data for the attribute change can be checked in tables
         OE_ORDER_HEADER_HISTORY - Table that stores history of attribute change for Order Headers
         OE_PRICE_ADJS_HISTORY - Table that stores history of attribute changes of Entity Price.
         OE_SALES_CREDIT_HISTORY - Table which stores history of attribute changes of Entity Sales Credits

Check the data in Audit Attribute History Tables
Check the data OE_AUDIT_ATTR_HISTORY - Table which holds the consolidated history data in a format needed for viewing and reporting history. This table is populated with the history data by Audit History Consolidator concurrent program.

Select * from OE_AUDIT_ATTR_HISTORY  
Where ORDER_NUMBER = &Enter Order number for which the history data is consolidated


Investigating issues with Audit History
Important: In the case the Audit History is not captured even though Require History constraint is defined, check
a) If there is a versioning constraint for the same operation. Versioning takes precedence and version is captured instead of the audit record.
          Example: You have processing constraints defined for:
              • Update of Salesperson with action Require History
              • Update of Salesperson with action Generate Version
            The versioning will take precedence.

b) Check processing constraint is setup for the 'All responsibilities' or the Responsibility used for making the changes the is listed in 'Constrained Responsibilities'
     Navigation: Navigation: Order Management Super User > Setup > Rules > Security > Processing Constraints
     Check under ‘Applicable To’ tab

Purging of History data
     a) We have standard 'Order Purge' functionality which purges the closed order from OE_ORDER_HEADERS_ALL/OE_ORDER_LINES_ALL table, as well as the following:
         related records in OE_ORDER_HEADER_HISTORY, OE_ORDER_LINES_HISTORY,OE_PRICE_ADJS_HISTORY and OE_SALES_CREDIT_HISTORY.

     b) There is no purge program to purge the oe_audit_attr_history table.
         This is currently an open ER 
Bug 5729119 HOW TO PURGE OM AUDIT HISTORY TABLE?

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect