Oracle Fusion cloud - Audit History Setup and Audit Data Extraction Methods

 Applies To:

Oracle Fusion Cloud

Objective Summary:

Audit History in Oracle Fusion Cloud tracks all user actions and system changes within the application. It provides transparency by recording details such as who made the change, when it was made, and the nature of the modification. This feature helps organizations maintain compliance, troubleshoot issues, and enhance security by monitoring critical activities. 

This article provides steps to enable and different ways of accessing audit history. Audit includes both application and object level audit on create, update and delete.

Important Notes:

1) Audit will not support the view or read event.

2) Enable only the most relevant and useful modules, entities, child entities ,attributes for auditing for better performance of the overall application.

3) No Limit on enabling on enabling entities or attributes.

4) Audit history data will never be purged, but you can access only specific date range of data. eg audit data between 30 days.

5) P2T can wipe the data in lower instances.

Privileges Required:

1) To manage and setup audit history user should have FND_MANAGE_AUDIT_POLICIES_PRIV.

2) To view or access the audit history user should have FND_VIEW_AUDIT_HISTORY_PRIV.

3) Application Implementation Consultant or Sales Administrator like roles will have these privileges by default.

Setup Audit History:

1) Navigate to setup and maintenance , search for the task Manage Audit Policies.

2) Enable audit for Oracle Fusion Application by selecting value as auditing. The default value is none.

3) Choose the module and entity to enable the audit. make sure parent nodes are selected to enable child nodes.

4) Enable the by selecting the object.

 5) Similarly enable audit at SOA level, security, ESS etc by choosing the audit level. Choose Audit Level as Low - Critical Event Only

View Audit History:

1) View Audit History From UI:

1) Navigate to Tools > Audit Reports.

2) Choose the filter criteria to view audit.

3) Enable the checkboxes to view the additional details like record keys, new values , old value and event.

Tables and Views for SQL:

Query to get Audit Enabled Objects:

Use the below query in BI Publisher which shows what attributes, belonging to which objects  are enabled for audit on your Fusion environment.

 select   
 VIEW_OBJECT, VIEW_ATTRIBUTE, AUDIT_SWITCH   
 from   
 fusion.fnd_audit_attributes   
 where   
 ENABLED_FLAG='Y' order by 1 

2) Audit Tables where Audit Data is stored:

Each of the product tables which are audited will have a "shadow" table. This table will have an underscore (_) at the end of the name.

For example table MOO_OPTY will use table MOO_OPTY_

Audit table will contain additional columns like:

- AUDIT_ACTION_TYPE_ - Action Type - have values like INSERT, UPDATE and DELETE.
- AUDIT_CHANGE_BIT_MAP_ - Used to store a bit map of 1's and 0s for each row. 1 will be stored if the value has changed and 0 will be stored if the value hasn't.

3) Use the ESS Scheduled Process for Audit Extraction Report:

For large volume of data where you are unable to view from UI, use ESS process and get the Excel formatted file as output.

Process Name: Generate Audit Report

Choose all the relevant filter criteria for generating a report. if it is erroring because of large data adjust criteria to limit.

4) Use REST APIs:

Endpoint: https://abcd-dev2.fa.us2.oraclecloud.com/fscmRestApi/fndAuditRESTService/audittrail/getaudithistory

Method: POST

Content-Type: application/json

Body:

1) Sample payload for getting opportunity audit history

{ "fromDate":"2025-01-13",

"toDate":"2025-01-13",

"productId":"47110F64ABF508E2E040449823C60DB6",

"eventType":"ALL",

"businessObjectType":"oracle.apps.sales.opptyMgmt.opportunities.opportunityService.view.OpportunityVO",

"timeZone":"UTC",

"includeExtendedObjectIdentiferColumns":"true",

"includeAttributes":"true",

"attributeDetailMode":"true",

"includeChildObjects":"true",

"includeImpersonator":"false"

}

 

2) Sample payload for getting Service Request audit history

{

    "objectId":"SR298492",

    "attributeDetailMode": true

    "businessObjectType": "oracle.apps.crm.service.svcMgmt.srMgmt.srMgmtService.view.ServiceRequestVO",

    "includeAttributes": true,

    "includeChildObjects": false,

    "pkParametersList": "SrId=300000288712981",

    "product": "ORA_SERVICE",

    "toDate": "2025-01-15",

    "fromDate": "2024-11-19"

    }          

The product values like

Sales  - sales

Servie - ORA_SERVICE

ERP    - FinancialCommon

HCM    - hcmCore

Ledger - Ledger

other possible values including "ODI", "ESS", "MDS", "SOA", "ADF" and "OPSS"

sample function

CREATE OR REPLACE FUNCTION APPS.GRCPCG_TWOGLPER
--   v_period_name IN VARCHAR2)
   RETURN NUMBER
IS
   v_period_cnt   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_period_cnt
     FROM (SELECT RPAD (
                     fat.application_name,
                       (20 - LENGTH (fat.application_name))
                     + LENGTH (fat.application_name),
                     ' ')
                     application_name,
                  gps.show_status,
                  gps.period_name
             FROM gl_period_statuses_v gps,
                  fnd_application_tl fat,
                  gl_sets_of_books gsb
            WHERE     1 = 1
                  --AND gps.period_name = v_period_name
                  AND fat.application_id = gps.application_id
                  AND gps.set_of_books_id = gsb.set_of_books_id
                  AND gps.application_id IN (0000)
                  AND gps.set_of_books_id = 0000) test
    WHERE bmstest.show_status = 'Open';

   IF v_period_cnt > 2
   THEN
      RETURN (v_period_cnt);
   ELSE
      RETURN (0);
   END IF;
END;

------------------------------------------------

GRANT ALL ON apps.GRCPCG_TWOGLPER TO XXLAAPPS

-------------------------------------------------

How to create the dummy table and find the value in Oracle R12

CREATE TABLE TAB_DUMMY ("SNO"  NUMBER, "VALUE" VARCHAR2 (4000 BYTE));


CREATE SEQUENCE SNO_DUMMY
   MINVALUE 1
   MAXVALUE 9999999999999999999999999999
   INCREMENT BY 1
   START WITH 1
   CACHE 20
   NOORDER
   NOCYCLE;


CREATE OR REPLACE PROCEDURE PRC_DUMMY (P_PARA VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO TAB_DUMMY
     VALUES   (SNO_DUMMY.NEXTVAL, P_PARA);

   COMMIT;
END;


-- prc_dummy ('lv_sysdate=' || lv_sysdate);

Employee Bank Account Script oracle R12

SELECT

aps.vendor_id,

apss.vendor_site_id,

aps.vendor_name,

apss.vendor_site_code,

ieb.bank_name,

ieb.country,

iebb.bank_branch_name,

iebb.eft_swift_code,

iebb.branch_number,

ieba.bank_account_num,

ieba.bank_account_name

FROM ap.ap_suppliers aps,

per_all_people_f papf,

ap.ap_supplier_sites_all apss,

apps.iby_ext_bank_accounts ieba,

apps.iby_account_owners iao,

apps.iby_ext_banks_v ieb,

apps.iby_ext_bank_branches_v iebb

WHERE aps.vendor_id = apss.vendor_id

AND iao.account_owner_party_id = aps.party_id

AND ieba.ext_bank_account_id = iao.ext_bank_account_id

AND ieb.bank_party_id = iebb.bank_party_id

AND ieba.branch_id = iebb.branch_party_id

AND ieba.bank_id = ieb.bank_party_id

AND aps.employee_id = papf.person_id

AND TRUNC(sysdate) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;

How to set PO Match Options in Oracle Apps?

 [PO] Matching Setting in Purchase Order Shipment Line

We can Set Invoice matching in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.

1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region

2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region

3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way

4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way

5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region

  Invoice Match to PO and Invoice Match to Receipt

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received. If you are billed for an item over the amount and quantity tolerances you define in the Invoice Tolerance window, during Approval, Oracle Payables applies a hold to the invoice, which prevents payment.
Oracle Payables supports three levels of matching which verify that purchase order and
invoice information match within defined tolerances.

Match Approval Level: 2-Way , 3-Way, 4-Way.

In 2-way: what ever you have ordered for the PO you will make the payment for the suppliers in 2- way i.e we will compare two documents PO and Invoice.
2-way matching verifies that Purchase order and invoice information match within your tolerances:
Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
Eg:Suppose we Had given PO for 100 items ,for that we will receive invoice for 100 items. so that we will make payment for that 100 items.

In 3-Way you will compare 3 documents i.e PO+reciept+Invoice.
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received.
Eg:Suppose we have ordered 100 items in PO. But we had received only 75 items ,But we had received invoice for 100 items. so, we will make payment for only 75 items.

In 4-Way you will compare 4 documents i.e PO+Receipt+Invoice+Inspection.
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.
Eg:Suppose we have 100 items in PO. Suppers send us 75 items We will do inspection on those items what ever we have received, If 15items got damaged. finally, we are going to make payment to the 60 items only.

When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_

ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching). Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.
Invoice Match Option:
The Invoice Match Option determines whether or not you intend to match invoices for this supplier against purchase orders or receipts.
Invoice Match option to PO Match:
--------------------------------------------
•Payables must match the invoice to the purchase order.
•If the Invoice is matched to a PO rather than to the Receipt when the AP team do a match they have the full PO available to match rather than just the specific lines on the PO that were received.
•There is a possibility someone in payables matching to the wrong distribution if they use Match to PO.

•The accrual is valued at the PO exchange rate date.

Invoice Match option to PO Match:

----------------------------------------------
•Payables must match the invoice to the receipt.
•Receipt Match Option is recommended if you want accounting to use exchange rate information based on the receipt date or if you want to update exchange rate information on the receipt.
•If you use the match to receipt option AP team can't match until the goods are received. If the receiving doesn't happen there is no way to associate the invoice with the PO.
•Invoice processing will be on hold till a receipt is entered into the system.
•Receipt Match option determines the cost with more accuracy i.e. Match to Material item receipt and link other invoice charges to receipt and You can now also associate freight, tax, and miscellaneous charges from invoices to the related receipt.
•Run the Matching Detail Report from Other -> Request -> Run. This report will show you detail of how an invoice, purchase order, or receipt was matched. This report is especially helpful when an invoice is on hold and you are trying determine why the hold was placed.

The Invoice Match Option defaults from the Supplier Sites window. You can change the Invoice Match Option on the shipment until you receive against the shipment.

Financial Options - Invoice Match Option:
For purchase order shipments, indicate whether you want to match invoices to purchase orders or to purchase order receipts. If the supplier was created automatically during Expense Report Import, the default value is Purchase Order, and you can change it to Receipts as appropriate.

The Business Needs To Take decision to set the Invoice match option to Receipt or Purchase.
•The main advantage is the Exchange Rate factor i.e. Receipt/Current Date instead of Purchaser Order date.
•If you are using Multi Currency Functionality for Purchase Order and if like to have the rate based on       Receipt date you need to go with Receipt Match at the invoice match option.
•Receipt Match gives much closer control of the matching process,Particularly where you have multiple receipts of large purchase order.

If a Business is using Invoice Match option Purhcase Order and like to change from Purchase Order to Receipt Match, then what would be the impact?

•All Supplier sites need to be updated with Invoice Match option to Receipt.
•Purchase orders which are already approved with Invoice Match Option of Purchase order should continue with the same existing process.
•All existing Open PO, which need a change from Purchase Order to Receipt, should be corrected by changing the invoice match option in the purchase order shipment to Receipt. Payables team should match invoices to Receipt for all Purchase Orders created after the cut-off date.
•Training to AP/PO users.

In order for the receipt to use the current exchange rate and not the PO exchange rate, the Invoice Match Option at the system level and on the suppliers should be Receipt, not Purchase Order.

Supplier and Site bank detail query in R12

/* Formatted on 2/15/2018 2:46:46 PM (QP5 v5.114.809.3010) */
SELECT   /*Supplier Information*/
         aps.segment1 oracle_supplier_number,
         aps.vendor_id,
         aps.vendor_name supplier_name,
         aps.party_id supplier_party_id,
         iepa.remit_advice_fax remit_advice_fax,
         iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
         ,
         assa.vendor_site_id,
         assa.party_site_id supplier_party_site_id,
         assa.vendor_site_code vendor_site_code,
         assa.pay_site_flag pay_site_flag,
         assa.purchasing_site_flag purchasing_site_flag,
         assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
         ,
         ieba.ext_bank_account_id,
         hp.party_name Bank_party_name,
         ieba.bank_account_num bank_account_num,
         ieba.bank_account_name bank_account_name,
         ieba.country_code bank_acct_country_code,
         ieba.currency_code bank_acct_currency_code/* Bank Address */
         ,
         hp.address1 bank_address_line1,
         hp.address2 bank_address_line2,
         hp.address3 bank_address_line3,
         hp.city bank_address_city,
         hp.state bank_address_state,
         hp.postal_code bank_address_zip,
         hp.country bank_address_country/* Bank Branch Address */
         ,
         hp1.address1 branch_address_line1,
         hp1.address2 branch_address_line2,
         hp1.address3 branch_address_line3,
         hp1.city branch_address_city,
         hp1.state branch_address_state,
         hp1.postal_code branch_address_zip,
         hp1.country branch_address_country
  FROM   ap_supplier_sites_all assa,
         hz_parties hp,
         iby_ext_bank_accounts ieba,
         iby_external_payees_all iepa,
         iby_pmt_instr_uses_all ipiua,
         ap_suppliers aps,
         hz_parties hp1
 WHERE       assa.vendor_site_id = iepa.supplier_site_id
         AND hp.party_id = ieba.bank_id
         AND ipiua.instrument_id = ieba.ext_bank_account_id
         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
         AND assa.vendor_id = aps.vendor_id
         AND ieba.branch_id = hp1.party_id
         AND ipiua.instrument_type = 'BANKACCOUNT'
         AND aps.vendor_name LIKE '3M%'
         AND assa.vendor_site_id = 6916
         AND ipiua.payment_flow = 'DISBURSEMENTS'
         AND ipiua.order_of_preference = 1;

Supplier / Supplier Site Details:-

SELECT   assa.vendor_site_id
                  FROM   ap_supplier_sites_all assa,
                         hz_parties hp,
                         iby_ext_bank_accounts ieba,
                         iby_external_payees_all iepa,
                         iby_pmt_instr_uses_all ipiua,
                         ap_suppliers aps,
                         hz_parties hp1
                 WHERE       assa.vendor_site_id = nvl(iepa.supplier_site_id,assa.vendor_site_id)
                         AND hp.party_id = ieba.bank_id
                         AND ipiua.instrument_id = ieba.ext_bank_account_id
                         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
                         AND assa.vendor_id = aps.vendor_id
                         AND ieba.branch_id = hp1.party_id
                         AND ipiua.instrument_type = ''BANKACCOUNT''
                         --and assa.vendor_site_id = 6916
                         AND ieba.ext_bank_account_id = : Bank Id
                         AND assa.org_id = fnd_profile.VALUE (''org_id'')


        ********************  ##################  ********************

SELECT  aps.vendor_name "VERDOR NAME",

        apss.vendor_site_code "VENDOR SITE CODE",

        ieb.bank_name "BANK NAME",

        iebb.bank_branch_name "BANK BRANCH NAME",

        iebb.branch_number "BRANCH NUMBER",

        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"

FROM    ap.ap_suppliers aps,

        ap.ap_supplier_sites_all apss,

        apps.iby_ext_bank_accounts ieba,

        apps.iby_account_owners iao,

        apps.iby_ext_banks_v ieb,

        apps.iby_ext_bank_branches_v iebb

WHERE   aps.vendor_id = apss.vendor_id

        and iao.account_owner_party_id = aps.party_id

        and ieba.ext_bank_account_id = iao.ext_bank_account_id

        and ieb.bank_party_id = iebb.bank_party_id

        and ieba.branch_id = iebb.branch_party_id

        and ieba.bank_id = ieb.bank_party_id;

Query to get the Profile Option Configurations

/* Formatted on 1/15/2018 9:40:58 AM (QP5 v5.114.809.3010) */
  SELECT   ot.user_profile_option_name,
           TO_CHAR (v.level_id) level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, 'Responsibility',
                   10004, 'User ID',
                   v.level_id)
              level_meaning,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, apl.application_name,
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id)
              level_name,
           v.profile_option_value,
           o.profile_option_name,
           v.creation_date value_creation_date,
           v.created_by value_created_by,
           v.last_update_date value_last_updated_date,
           v.last_updated_by value_last_updated_by
    FROM   applsys.fnd_profile_options_tl ot,
           applsys.fnd_profile_options o,
           applsys.fnd_profile_option_values v,
           applsys.fnd_responsibility_tl frt,
           apps.fnd_application_vl apl,
           fnd_user u
   WHERE       v.level_value = frt.responsibility_id(+)
           AND v.profile_option_id = o.profile_option_id
           AND o.profile_option_name = ot.profile_option_name
           AND ot.language = 'US'
           AND NVL (frt.language, 'US') = 'US'
           AND v.level_value = apl.application_id(+)
           AND u.user_id(+) = v.level_value
ORDER BY   ot.user_profile_option_name,
           v.level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id);

APP-SQLAP-10199

Setup an Invoice Batch

Advantages of Invoices Batches

1. Enter invoice defaults at the batch level that override system or supplier site defaults for all invoices in the batch.

2. Maximize accuracy by tracking variances between the control invoice count and total and the actual invoice count and total resulting from your invoice entry.

3. Easily locate a batch online and review the name of the person who created the batch and the date it was created.

Responsibility: Payables, Vision Operations (USA)
Navigation: Invoices > Entry > Invoice Batches

APP-SQLAP-10199: You cannot navigate to the Invoice Batches window because batch control is not enabled for your system.

If you want to enable invoice batch control for your system, then ask your system administrator to enable the profile option AP: Use Invoice Batch Controls in the System Profile Options window.

Profile Option

AP: Use Invoice Batch Controls

YES -> Create the Invoice with batch
NO  -> Create the Invoice without batch.

To enter an Invoice Batch

1. In the Invoice Batches window enter a unique value in the Invoice Batch Name field. This name will appear on your reports and will help you locate the batch online.

2. Enter the number of invoices in the batch in the Control Count field.

Enter the sum of invoice amounts in the batch in the Control Total field. Payables tracks variances between the Control Count and Total and the Actual Count and Total as you enter invoices.

Note: If there is a discrepancy between the invoice amount and batch amount, Payables warns you when you exit a batch but it does not prevent Invoice Validation and payment of the individual invoices within a batch. You can make a correction immediately, or you can adjust the invoice batch later.

3. Enter any Invoice Defaults you want for the invoices. Defaults include: Currency, Type, Document Category, Hold Name, Liability Account, Payment Terms, Pay Group, GL Date, and Hold Reason.

These values you enter for defaults override any system and supplier site defaults for the invoices. For example, if you want the purchase order to provide the default value for Payment Terms on the invoice, then leave the Payment Terms field here blank. When you enter individual invoices you can override any values that default from the batch.

4. Choose the Invoices button and enter the invoices.

 
Responsibility: Payables, Vision Operations (USA)
Navigation: Invoices > Entry > Invoice Batches
 
Batch Name: ABC Batch

How to cancel Scheduled Concurrent Programs in Oracle apps

From Front End:

1) Login with the same user, who scheduled the concurrent program
2) Go to view requests from the responsibility it's been scheduled -->View Requests--> Query for the program
3) You should get one record in scheduled state
4) Click on view details end date the schedule and cancel the request.


Select * from fnd_concurrent_programs
WHERE CONCURRENT_PROGRAM_ID = ----------------

Select * from fnd_concurrent_programs_VL
WHERE USER_CONCURRENT_PROGRAM_NAME = 'Name'

Select * from fnd_concurrent_requests
WHERE CONCURRENT_PROGRAM_ID = ----------------




From Back End
Use below query to cancel scheduled concurrent program.

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=&P_CONCURRENT_PROGRAM_ID;

COMMIT;