In today’s fast-paced business environment, the ability to migrate data between different ERP systems is a critical skill. Whether upgrading systems or consolidating data across platforms, ensuring data integrity and accuracy is paramount. Recently, I had the opportunity to develop a custom SQL query in Oracle Fusion Cloud to generate a comprehensive receipt report for a client. This report is pivotal in their migration process to SAP, another leading ERP system. In this blog post, I’ll walk you through the details of this query and its business significance, with a focus on key concepts in Oracle Fusion Accounts Receivables.

Understanding the Business Requirement for crafting SQL Query

The client needed a report encapsulating detailed receipt information over a specified period. This data is essential for verifying the completeness and accuracy of transactions during the migration to SAP. The report needed to include:  

• Receipt Number: Unique identifier for each receipt.  
• Receipt Date: Date when the receipt was generated.  
• Receipt Method: The method used to receive the payment.  
• Currency: The currency in which the transaction was made.  
• Applied Amount: Amount that has been applied to invoices.  
• Unapplied Amount: Amount that has not been applied to any invoice.  
• Unidentified Amount: Amount that cannot be identified against any customer or invoice.  
• Total Amount: Sum of applied, unapplied, and unidentified amounts.  
• Receipt Status: Current receipt status (e.g., Applied, Unapplied, Reversed).  
• Customer Name and Account Number: Identifying customer details.  
• Operating Unit: The business unit handling the transaction.  

Exploring a career path in SQL?
Get started with our FREE Overview of Oracle SQL Training

Key Functional Concepts in Fusion Accounts Receivables

Before diving into the query, it’s crucial to understand some key functional concepts in Oracle Fusion Accounts Receivables:  

• Receipts: In Accounts Receivables, a receipt represents the payment received from a customer. Each receipt has a unique identifier, date, method, currency, and status.  
• Receipt Methods: This defines how the payment was received, such as cash, check, or electronic transfer. Receipt methods are configured in the system to handle different payment channels.  
• Receipt Statuses: Receipts can have various statuses such as ‘Applied’, ‘Unapplied’, ‘Reversed’, etc. These statuses indicate the processing state of the receipt within the financial system.  
• Customer Accounts and Parties: Customer accounts are linked to parties (entities) within Oracle Fusion. This relationship is crucial for tracking payments and outstanding balances.  
• Operating Units: These represent different segments or divisions within an organization. Operating units manage their transactions separately but can consolidate data for reporting purposes.

The SQL Query Explained  

Below is the SQL query developed to meet these requirements: 

SELECT  
    arcr.receipt_number, 
    arcr.receipt_date, 
    arm.name AS receipt_method, 
    arcr.currency_code AS currency, 
    SUM(CASE WHEN araa.status = ‘UNAPP’ THEN araa.AMOUNT_APPLIED ELSE 0 END) AS unapp_amount, 
    SUM(CASE WHEN araa.status = ‘APP’ THEN araa.AMOUNT_APPLIED ELSE 0 END) AS app_amount, 
    SUM(CASE WHEN araa.status = ‘UNID’ THEN araa.AMOUNT_APPLIED ELSE 0 END) AS unid_amount, 
    SUM(araa.AMOUNT_APPLIED) AS total_amount, 
    CASE arcr.status 
        WHEN ‘APP’ THEN ‘Applied’ 
        WHEN ‘NSF’ THEN ‘Non-Sufficient Funds’ 
        WHEN ‘STOP’ THEN ‘Stop Payment’ 
        WHEN ‘UNAPP’ THEN ‘Unapplied’ 
        WHEN ‘CC_CHARGEBACK_REV’ THEN ‘Credit Card Chargeback Reversal’ 
        WHEN ‘REV’ THEN ‘Reversed’ 
        WHEN ‘UNID’ THEN ‘Unidentified’ 
        ELSE ‘Unknown Status’ 
    END AS status, 
    HCA.account_name AS customer_name, 
    HCA.account_number AS cust_account_num, 
    hou.name 

FROM  
    ar_cash_receipts_all arcr 
    JOIN AR_RECEIPT_METHODS arm ON arm.receipt_method_id = arcr.receipt_method_id 
    LEFT JOIN HZ_CUST_ACCOUNTS HCA ON arcr.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID 
    LEFT JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID 
    JOIN hr_operating_units hou ON hou.ORGANIZATION_ID = arcr.org_id 
    JOIN Ar_Receivable_Applications_All araa ON arcr.cash_receipt_id = araa.cash_receipt_id 

WHERE  
    hou.name IN (:bu_name) 
    AND arcr.receipt_date BETWEEN :start_receipt_date AND :end_receipt_date 

GROUP BY  
    arcr.receipt_number,  
    arcr.receipt_date,  
    arm.name,  
    arcr.currency_code,  
    arcr.status, 
    HCA.account_name, 
    HCA.account_number, 
    hou.name 

Detailed Breakdown of the Query

Selecting and Aggregating Data 

1. Receipt Information: The query begins by selecting crucial columns such as receipt_number, receipt_date, receipt_method, and currency. These columns provide the fundamental details of each receipt. 

2. Calculating Amounts: 

• Unapplied Amount: The sum of amounts where the status is ‘UNAPP’. 
• Applied Amount: The sum of amounts where the status is ‘APP’. 
• Unidentified Amount: The sum of amounts where the status is ‘UNID’. 
• Total Amount: The sum of all applied amounts, regardless of their status. 

These calculations ensure that the report accurately reflects the financial state of each receipt. 

Translating Status Codes 

3. Receipt Status: The CASE statement translates internal status codes into user-friendly descriptions such as ‘Applied’, ‘Unapplied’, ‘Reversed’, etc. This translation is crucial for end-users to quickly understand the status of each receipt. 

Joining Tables 

4. Joining Necessary Tables: 

• ar_cash_receipts_all (arcr): Main table containing receipt details. 
• AR_RECEIPT_METHODS (arm): Table with receipt methods. 
• HZ_CUST_ACCOUNTS (HCA): Table with customer account information. 
• HZ_PARTIES (HP): Table with party details linked to customer accounts. 
• hr_operating_units (hou): Table with operating unit details. 
• Ar_Receivable_Applications_All (araa): Table with applications of receipts. 

These joins ensure that the query pulls together all necessary data to provide a holistic view of each receipt. 

Filtering and Grouping Data 

5. Filtering by Business Unit and Date Range: 

• The WHERE clause filters receipts by business unit (hou.name IN (:bu_name)) and receipt date (arcr.receipt_date BETWEEN :start_receipt_date AND :end_receipt_date). This allows users to generate reports for specific time periods and business units. 

6. Grouping Results: 

• The GROUP BY clause groups the results by receipt number, date, method, currency, status, customer name, account number, and business unit. This grouping is essential for summarizing the data at the desired granularity.

Conclusion 

This detailed SQL query in Oracle Fusion Cloud is a powerful tool for generating comprehensive receipt reports. These reports are critical for ensuring data integrity during the migration to another ERP system like SAP. By meticulously selecting, aggregating, and translating data, the query provides a clear and accurate representation of each receipt’s status and financial details. This level of detail not only aids in the migration process but also ensures that the client can trust the accuracy and completeness of their data in the new system. 

Understanding and utilizing key functional concepts in Oracle Fusion Accounts Receivables, such as receipts, receipt methods, and statuses, enhances the ability to create effective queries that meet business needs. By sharing this process and the query itself, I hope to provide insights and practical solutions for professionals involved in similar ERP migration projects.

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices