Vamsi Oracle Technical Blog

When working with Oracle HCM / Fusion Applications , administrators and developers often need to fetch details about users, their assigned roles, and their effective dates. This helps in audits, access reviews, and troubleshooting authorization issues. In this post, we’ll walk through a SQL query that retrieves user-role information from Oracle HCM tables. SQL Query:                          SELECT     pus.user_id             "User ID",     pus.username            "Username",     ppnaf.full_name         "Full Name",     prdn.role_common_name   "Role Common Name",     prdtl.role_name         "Role Name",     to_char(purs.start_date, 'DD-MON-YYYY') "Role Start Date",     to_char(purs.end_date, 'DD-MON-YYYY')   "...

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...