Vamsi Oracle Technical Blog

What Is Invoice Approval History? Invoice approval history refers to the trail of events and actions taken during the approval process of an invoice. This includes data such as: Who approved the invoice, The approval status, The date and time of approval, Any responses or feedback related to the approval, The amount approved, and The relevant projects or expenditure types associated with the invoice. Tracking approval history helps ensure compliance with organizational policies, provides transparency, and aids in the audit process. Below is the SQL query that retrieves invoice approval history data in oracle fusion. The query joins multiple tables within Oracle's AP (Accounts Payable) module and related modules to provide comprehensive approval history details for invoices. SQL Query:      SELECT     aia.invoice_id,     hp.PARTY_NAME supplier_name,     ps.segment1 supplier_number,     aia.invoice_num invoice_numbe...

Introduction                                  Extracting General Ledger (GL) journal data is one of the most common and essential tasks in both Oracle E-Business Suite (EBS) and Oracle Fusion environments. Whether you're supporting reconciliation, audit reporting, or building a custom data integration, this detailed SQL helps you fetch journal entries, ledger information, accounting amounts, project attributes, and line-level details in one go. Use Case This GL journal extraction query is helpful when: Users want detailed journal information including batch, header, line, and segment details. Auditors or finance teams request transactional history within a specific ledger or period. Custom reporting or reconciliation processes are being built in BI Publisher, OTBI, or any third-party reporting tools. SQL Query: SELECT     gl.ledger_id,     TO_CHAR(gjh.de...

 In this blog, we’ll break down a query that helps you trace a specific Concurrent Program through its Application , Request Group , and associated Responsibilities in Oracle EBS R12 Objective The goal of this query is to find: The User-Friendly Name of a Concurrent Program The Technical Concurrent Program Name The Application to which it belongs The Request Group under which it is registered The Responsibility that grants access to it This is especially useful for ensuring correct program registration or for debugging access issues. SQL Query:      SELECT DISTINCT      fcpl.user_concurrent_program_name,     fcp.concurrent_program_name,     fapp.application_name,     frg.request_group_name,     fnrtl.responsibility_name FROM      apps.fnd_request_groups         frg,     apps.fnd_application_tl         fapp,   ...

Oracle stores passwords in encrypted format, and this SQL script demonstrates how you can decrypt user passwords using standard EBS API functions such as get_pwd.decrypt . Important Note : This script is for educational and internal system diagnostics only. Never use it in production environments without proper security authorization. Use Case This SQL is particularly useful for: Testing internal password decryption functionality Validating password migrations during EBS clone or refresh Technical support or audit trails in development/test environments SQL Query:      SELECT usr.user_name,        get_pwd.decrypt           ((SELECT (SELECT get_pwd.decrypt                               (fnd_web_sec.get_guest_username_pwd,                                usertable.e...

3 Way Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation In any procurement system, ensuring the accuracy of supplier payments is critical. One of the most robust methods for validating supplier invoices is the 3-way matching process. This blog breaks down the 3-way matching concept in Oracle E-Business Suite (EBS) or Oracle Fusion, explains its benefits, and presents a detailed SQL query that helps extract and reconcile PO, Receipt, and Invoice data. What is 3-Way Matching ? 3-Way Matching is a process that compares the following three documents before payment is released: Purchase Order (PO) – What you ordered Receipt – What you received Invoice – What the supplier billed This comparison ensures that you only pay for goods that were ordered and received , avoiding overpayments, duplicates, and fraud. Matching Criteria   Element              PO      Receipt  ...