Oracle SQL Query for Extracting Invoice Approval History in Oracle Fusion

-

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_number,

    aia.invoice_date invoice_date,

    aiaha.AMOUNT_APPROVED,

    aia.INVOICE_CURRENCY_CODE CURRENCY_CODE,

    aiaha.APPROVER_ID APPROVER_NAME,

    aiaha.RESPONSE,

    aiaha.CREATED_BY,

    aiaha.CREATION_DATE,

    aiaha.LAST_UPDATE_DATE,

    aiaha.LAST_UPDATED_BY,

    aiaha.action_date,

    aila.LINE_NUMBER line_num,

    aila.LINE_TYPE_LOOKUP_CODE line_type,

    ppaa.segment1 project_number,

    ppaa2.TASK_NUMBER,

    ppaa1.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE,

    ppaa3.name expenditure_organization

FROM


    ap_invoices_all aia,

    ap_invoice_lines_all aila,

    poz_suppliers ps,

    hz_parties hp,

    (   

        select distinct ppa.segment1,

               ppa.PROJECT_ID

        from PJF_PROJECTS_ALL_B ppa,

             ap_invoice_lines_all aila

        where ppa.PROJECT_ID = aila.PJC_PROJECT_ID

    ) ppaa,

                

    (   

        select distinct PET.EXPENDITURE_TYPE_NAME,

               PET.EXPENDITURE_TYPE_ID

        from PJF_EXP_TYPES_TL PET,

             ap_invoice_lines_all aila

        where aila.PJC_EXPENDITURE_TYPE_ID = PET.EXPENDITURE_TYPE_ID

    ) ppaa1,

        

    (   

        select distinct PTV.TASK_NUMBER,

               PTV.TASK_ID

        from PJF_TASKS_V PTV,

             ap_invoice_lines_all aila

        where aila.PJC_TASK_ID = PTV.TASK_ID

    ) ppaa2,

        

    (   

        select distinct hou.ORGANIZATION_ID,

               hou.name

        from HR_ORGANIZATION_UNITS hou,

             ap_invoice_lines_all aila

        where aila.PJC_ORGANIZATION_ID = hou.ORGANIZATION_ID

    ) ppaa3,

            

    (   

        SELECT distinct

                max(aiah.amount_approved) amount_approved,

                max(aiah.approver_id) approver_id,

                max(aiah.invoice_id) invoice_id,

                max(aiah.action_date) action_date,

                max(aiah.response) response,

                max(aiah.CREATED_BY) CREATED_BY,

                max(aiah.CREATION_DATE) CREATION_DATE,

                max(aiah.LAST_UPDATE_DATE) LAST_UPDATE_DATE,

                max(aiah.LAST_UPDATED_BY) LAST_UPDATED_BY,

                aia.invoice_id aia_invoice_id

        FROM ap_inv_aprvl_hist_all aiah,

             ap_invoices_all aia

        WHERE 1 = 1

            AND aia.invoice_id = aiah.invoice_id

            AND response = 'APPROVED'

            AND approver_id = 'give here approver_id'

        GROUP BY aia.invoice_id

    ) aiaha

WHERE 1 = 1

    AND ppaa.PROJECT_ID(+) = aila.PJC_PROJECT_ID

    AND ppaa1.EXPENDITURE_TYPE_ID(+) = aila.PJC_EXPENDITURE_TYPE_ID

    AND ppaa2.TASK_ID(+) = aila.PJC_TASK_ID

    AND ppaa3.ORGANIZATION_ID(+) = aila.PJC_ORGANIZATION_ID

    AND aia.invoice_id = aila.invoice_id

    AND ps.VENDOR_ID(+) = aia.VENDOR_ID

    AND aia.party_id = hp.party_id(+)

    AND aila.LINE_TYPE_LOOKUP_CODE = 'ITEM'

    AND aiaha.invoice_id = aia.invoice_id

ORDER BY 

    aia.invoice_id,

    (CASE 

        WHEN aila.LINE_TYPE_LOOKUP_CODE = 'ITEM' THEN '1' 

        WHEN aila.LINE_TYPE_LOOKUP_CODE = 'TAX' THEN '2' 

        ELSE '3' 

    END)


Location: Hyderabad, Telangana, India

Comments

Post a Comment

Popular posts from this blog

Query to find Responsibility and Request Group from Concurrent Program

-
 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,   ...
Read more »»»»

How to Extract User Role Details with SQL in Oracle Fusion

-
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')   "...
Read more »»»»

3 Way PO Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

-
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  ...
Read more »»»»