AR Customers and Sites – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Receivables · all Receivables reports

Overview

Master data report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information.

Payment terms are reported from each of their own sources: Account Profile Payment Term and Site Profile Payment Term from the customer profile at account and site level, and Site Use Payment Term from the site use Order Management terms.

Customer profiles are reported at Customer level, and at Site Use level for Bill To, Statements and Dunning site uses only. At Customer level, an account profile and a party profile are reported on separate rows. At Site Use level, the profile amounts come from the site use profile, else the account profile, else the party profile, which is also reported in the account profile columns when the account has no account profile. Profile Level shows the level of the profile amounts.

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Operating Unit, Customer Name, Customer Name not like, Account Number, Country, Show identifying Addresses only, Show active only, Show Profile Amounts, Profile Amount Currency, Show Receipt Methods, Show Bank Accounts, Show Debit Authorities, Show Contacts, Show Tax Registrations, Show Fiscal Classifications, Show trx count within x days, Show Latest Trx Date, Level, Site Use, DFF Display, Creation Date From, Creation Date To, Last Update Date From, Last Update Date To

Oracle EBS Tables Used

fnd_territories_vl, hz_role_responsibility, hz_cust_account_roles, hz_relationships, hz_parties, hz_org_contacts, hz_cust_accounts, hz_party_sites, hz_locations, hz_contact_points, iby_external_payers_all, iby_pmt_instr_uses_all, iby_ext_bank_accounts, ce_bank_branches_v, zx_registrations, zx_party_tax_profile, hz_code_assignments, zx_fc_types_b, zx_output_classifications_v, hr_locations, jtf_rs_salesreps, jtf_rs_resource_extns_tl, oe_transaction_types_tl, qp_list_headers_tl, hz_cust_site_uses_all, ra_terms_vl, ar_statement_cycles, ar_dunning_letter_sets, ar_collectors, hr_operating_units, gl_sets_of_books, hz_cust_acct_sites_all, fnd_territories_tl, hz_customer_profiles, hz_cust_profile_classes, hr_all_organization_units_vl, xxen_report_org_access, ra_territories_kfv, hz_cust_profile_amts, ra_cust_receipt_methods, ar_receipt_methods, iby_debit_authorizations

Report Categories

Enginatics, R12 only

AR Customer Upload, WSH Commercial Invoice, AP Supplier Upload

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output AR Customers and Sites 22-Jul-2023 155106.xlsx
Blitz Report™ XML Import AR_Customers_and_Sites.xml
SQL Source AR Customers and Sites.sql
Full Report on Enginatics www.enginatics.com/reports/ar-customers-and-sites/

AR Customers and Sites - Case Study & Technical Analysis

Executive Summary

The AR Customers and Sites report is a comprehensive master data audit tool for Oracle Receivables. It provides a detailed, flattened view of the complex customer hierarchy within the Oracle Trading Community Architecture (TCA). By consolidating information regarding parties, accounts, sites, business purposes, contacts, profiles, and banking details, this report empowers organizations to maintain high data quality, ensuring accurate billing, shipping, and tax compliance.

Business Challenge

In Oracle E-Business Suite, customer data is normalized across numerous tables (TCA model), making it challenging to extract a simple, holistic view of a customer. Common challenges include:

Solution

The AR Customers and Sites report solves these problems by offering a unified view of the customer master. Key features include:

Technical Architecture

The report navigates the complex Oracle TCA schema to retrieve and link data accurately.

Key Tables & Joins

Data Logic

The query logic ensures that:

  1. Hierarchy Traversal: It correctly traverses from Party -> Account -> Account Site -> Site Use.
  2. Level-Based Reporting: It can report at the Customer Account level or drill down to specific Site Uses.
  3. Outer Joins: It uses outer joins for related details (like Bank Accounts or Contacts) so that customers without these details are still listed.

Parameters

The report provides extensive filtering and display options:

Performance

The report is designed to be efficient even with large customer bases.

FAQ

Q: Why do I see the same customer name multiple times? A: The report typically outputs one row per “Site Use” (e.g., one row for the Bill-To address, another for the Ship-To address). If a customer has multiple sites, they will appear multiple times.

Q: How can I find customers created in the last month? A: Use the “Creation Date From” parameter and set it to the first day of the last month.

Q: Does this report show inactive sites? A: Yes, unless you set the “Show active only” parameter to ‘Yes’. Inactive sites will usually have a status of ‘I’ or ‘Inactive’.

© Enginatics GmbH