Search Results customer_status_code

Overview

AMS_PARTY_CUSTOMERS_V is a marketing-module view owned by the APPS schema in Oracle E-Business Suite. In the ETRM 12.2.2 metadata it is registered as a VALID VIEW with the description stating that it "returns all the parties (both Persons and Organizations)." Practically, it is a denormalized projection over the trading community model that presents party records in a customer-oriented column layout, exposing identifiers, names, classification data, and descriptive flexfield attributes in a single queryable object.

Because the view carries the AMS (Marketing) product tag, it is principally referenced by Oracle Marketing and adjacent CRM components that need to enumerate parties for segmentation, list building, campaign targeting, and audience analysis. It also serves reporting and integration scenarios where a flat, customer-shaped result set is preferable to joining the underlying party, location, and account structures directly. Note that the view is documented as a read-only projection; Oracle does not publish it as a supported maintenance interface, and direct DML against it is not part of the documented behavior.

Underlying Base Objects

The documented base object for this view is HZ_PARTIES, referenced through a synonym. The view text shows a simple filter, selecting from HZ_PARTIES (aliased as CUST) where PARTY_TYPE is restricted to 'PERSON' or 'ORGANIZATION'. All other party types stored in HZ_PARTIES are excluded, which is why the description emphasizes persons and organizations.

Consequently, the view inherits one row per qualifying party. No joins to HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, or order tables are present in the documented definition, so the "customer" semantics are party-level rather than account-level or site-level. Reporting requirements that need bill-to/ship-to detail, account numbers, or site usage must be satisfied by additional joins outside this view.

Key Columns

• ROW_ID / PARTY_ID / CUSTOMER_ID — Row identifier and the party identifier, passed through as both PARTY_ID and CUSTOMER_ID for compatibility with customer-centric consumers.
• PARTY_TYPE — Always 'PERSON' or 'ORGANIZATION' given the view's filter.
• CUSTOMER_NAME / PARTY_NAME, CUSTOMER_NUMBER / PARTY_NUMBER — Party display name and the system-assigned party number (the "customer number" convention in TCA).
• CUSTOMER_STATUS_CODE — The status of the party record (typically A for active, I for inactive), surfaced in the view under the CUSTOMER_STATUS_CODE name even though the underlying column in HZ_PARTIES is STATUS. This is the column most commonly used to exclude inactive parties from marketing output.
• PERSON_FIRST_NAME, PERSON_LAST_NAME and the phonetic counterparts — Name components for person parties, useful for merge, deduplication, and phonetic matching.
• ORGANIZATION_NAME_PHONETIC — Phonetic representation for organization parties.
• TAX_REFERENCE, SIC_CODE, CUSTOMER_CATEGORY_CODE, EMPLOYEES_TOTAL, CUSTOMER_KEY, VALIDATED_FLAG — Classification and verification attributes carried from the party record.
• TOTAL_NUM_OF_ORDERS, TOTAL_ORDERED_AMOUNT, LAST_ORDERED_DATE — Order-history derived measures exposed for segmentation and value ranking.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield context and segment values, allowing client-specific party attributes to be reported without joining the flexfield tables.

Common Use Cases and Queries

Typical uses include marketing list extraction, customer status auditing, and phonetic duplicate detection. A frequent pattern filters on the status column the user searched for:

• Active party count by type: SELECT party_type, COUNT(*) FROM ams_party_customers_v WHERE customer_status_code = 'A' GROUP BY party_type;
• Organization listing: SELECT customer_number, customer_name FROM ams_party_customers_v WHERE party_type = 'ORGANIZATION' AND customer_status_code = 'A';
• High-value segmentation: SELECT customer_number, customer_name, total_ordered_amount FROM ams_party_customers_v WHERE total_ordered_amount > 100000 ORDER BY total_ordered_amount DESC;
• Phonetic matching for deduplication: query PERSON_FIRST_NAME_PHONETIC and PERSON_LAST_NAME_PHONETIC for person rows and ORGANIZATION_NAME_PHONETIC for organizations.

Because no account or site joins are embedded, any query requiring account numbers, addresses, or contact points must join to HZ_CUST_ACCOUNTS and related TCA tables on PARTY_ID. Queries should also expect duplicate party names across party types and should not assume CUSTOMER_STATUS_CODE is never null, since the underlying STATUS column permits nulls.