Results for “customer_key”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGI_AR_CUSTOMERS_V is a seeded, VALID database view owned by the APPS schema and shipped as part of the IGI – Public Sector Financials International product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose in the ETRM documentation is to display single third party customer information. In practice, the view joins Oracle Receivables' legacy customer table (RA_HCUSTOMERS) to the Trading Community Architecture customer account table (HZ_CUST_ACCOUNTS) and enriches the result with Straight-Through Processing (STP) attributes held in the IGI extension table IGI_RA_CUSTOMERS, decoding the STP type through the IGI_LOOKUPS view. It therefore acts as a reporting and integration facade that presents a customer master record together with the IGI-specific payment or processing controls attached to that customer. Because it is a view rather than a table, it carries no storage of its own and remains synchronized with the underlying transactional data at query time.

Underlying Base Objects

The documented view text exposes four referenced objects. HZ_CUST_ACCOUNTS (accessed through a synonym) is the primary driver table; the view joins CUST.CUSTOMER_ID = CUST_ACCOUNT.CUST_ACCOUNT_ID to link the Receivables customer to its TCA customer account. RA_HCUSTOMERS (synonym) supplies the customer name, number, status flag, and other legacy party attributes. IGI_RA_CUSTOMERS (synonym) is joined with an outer join (IGI.CUSTOMER_ID(+)) so that customers lacking an IGI extension row are still returned, with null STP values. IGI_LOOKUPS (view) is likewise outer-joined on LOOKUP_TYPE = 'IGI_STP_TYPE', providing the decoded meaning of the STP type code. The documented dependencies also list HZ_PARTIES (synonym), reflecting the party relationship behind the customer account. The view's ROW_ID column is populated from HZ_CUST_ACCOUNTS.ROWID, giving consumers a direct handle to the underlying account row.

Key Columns

• CUSTOMER_ID – Primary identifier of the customer, joined to the TCA customer account.
• CUSTOMER_NAME, CUSTOMER_NUMBER, CUSTOMER_KEY – Identification attributes used for search and display.
• ORIG_SYSTEM_REFERENCE – Source-system cross reference, relevant when customer data is imported from external systems.
• STATUS, CUSTOMER_PROSPECT_CODE – Lifecycle state of the customer record.
• JGZZ_FISCAL_CODE, TAX_REFERENCE – Fiscal and tax identifiers, particularly relevant in public sector localization scenarios.
• CUSTOMER_NAME_PHONETIC – Phonetic search key for name matching.
• STP_TYPE, STP_TYPE_MEANING, STP_ENFORCE_THRESHOLD – IGI straight-through processing configuration; the code comes from IGI_RA_CUSTOMERS and the meaning from the IGI_STP_TYPE lookup.
• WAREHOUSE_ID – Warehouse associated with the customer record.
• ROW_ID – Rowid of the corresponding HZ_CUST_ACCOUNTS row.
• Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include customer master extracts, reconciliation between Receivables customers and TCA accounts, and reporting on STP enablement for public sector collections. The join to IGI_LOOKUPS removes the need for callers to decode IGI_STP_TYPE themselves.

Retrieve a single customer by ID:

SELECT customer_id, customer_name, customer_number, stp_type_meaning
FROM apps.igi_ar_customers_v
WHERE customer_id = :p_customer_id;

List all customers with STP enforced above a threshold:

SELECT customer_number, customer_name, stp_type_meaning, stp_enforce_threshold
FROM apps.igi_ar_customers_v
WHERE stp_enforce_threshold IS NOT NULL
ORDER BY customer_name;

Locate customers by fiscal code or tax reference during reconciliation:

SELECT customer_id, customer_name, jgzz_fiscal_code, tax_reference, status
FROM apps.igi_ar_customers_v
WHERE jgzz_fiscal_code = :p_fiscal_code OR tax_reference = :p_tax_ref;

Because the IGI joins are outer joins, queries must expect null STP_TYPE, STP_TYPE_MEANING, and STP_ENFORCE_THRESHOLD values for customers without an IGI extension record. Custom code should be built on this view rather than on the base tables so that IGI and localization logic remains encapsulated.