Results for “org_status”

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

Overview

The AST_LM_CKEY_ACCT_V view, owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, is a TeleSales (AST) component within the Oracle Marketing and CRM family. It exposes a consolidated listing of customer accounts and their associated parties, customer account roles, and party relationships, and serves as a lookup or selection source for the Lead Management (LM) "CKEY" (customer key) account functionality. In practical terms, the view answers the question "which party, account, role, or relationship should be associated with this lead or campaign record," while normalizing data from the Trading Community Architecture (TCA) model into a single flat row structure.

Because the view is defined in the APPS schema and is a simple (non-materialized) database view, it is consumed at runtime by TeleSales and related CRM flows rather than persisted. It participates in reporting and integration scenarios where external processes, custom reports, or concurrent programs need a unified account/party/relationship projection, particularly those that must resolve the PARTY_RELATIONSHIP party type as a distinct value.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following referenced objects:

• HZ_CUST_ACCOUNTS (synonym) — the customer account header; supplies account number, name, status, and customer account ID.
• HZ_PARTIES (synonym) — the party master (organization or person); supplies party ID, party name, party type, and status.
• HZ_CUST_ACCOUNT_ROLES (synonym) — roles assigned to parties for a given account; drives ROLE_TYPE.
• HZ_RELATIONSHIPS (synonym) — subject/object party links that model relationships between parties.
• AR_LOOKUPS (view) — Receivables lookup definitions; supplies the meaning and lookup code for party types and account role types.

The view is a UNION of two projected queries. The first returns organization parties on accounts where the party type lookup is enabled. The second, richer branch joins HZ_PARTIES P, HZ_RELATIONSHIPS RELATE, HZ_CUST_ACCOUNT_ROLES ROLES, HZ_CUST_ACCOUNTS ACCT, and AR_LOOKUPS, resolving relationship parties via RELATE.SUBJECT_ID = P.PARTY_ID and matching RELATE.OBJECT_ID = O.PARTY_ID. The PARTY_RELATIONSHIP lookup code is substituted for the party type whenever a relationship party exists, which is the value users encounter when searching on "party_relationship."

Key Columns

• ACCOUNT_NUMBER, ACCOUNT_NAME, CUST_ACCOUNT_ID — identifying attributes of the customer account; CUST_ACCOUNT_ID is returned as a character string.
• O_PARTY_ID, O_PARTY_NAME, O_PARTY_TYPE, O_PARTY_TYPE_CODE, ORG_STATUS — "owner" or object party; type is resolved through AR_LOOKUPS with lookup type PARTY_TYPE.
• PERSON_ID — person party ID (0 in the organization branch of the UNION).
• RELATIONSHIP_ID — the related party ID derived from HZ_RELATIONSHIPS; 0 or NULL when no relationship exists.
• ROLE_TYPE, MEANING — the customer account role and its lookup meaning (lookup type ACCT_ROLE_TYPE).
• R_PARTY_ID, R_PARTY_NAME, R_PARTY_TYPE — the related party's identifier, name, and type.
• ACCT_STATUS, PER_STATUS — account and person status flags used for filtering active records.

The O_PARTY_TYPE = 'PARTY_RELATIONSHIP' value is the specific marker surfaced when a party is participating through a relationship, and is the relevant condition when users search for "party_relationship."

Common Use Cases and Queries

Typical consumers include TeleSales lead/customer selection pages, custom CRM reports, and integration extracts that need a denormalized account/party/relationship listing. A representative query to retrieve only relationship-based rows follows:

• SELECT account_number, account_name, o_party_name, r_party_name, role_type, meaning FROM apps.ast_lm_ckey_acct_v WHERE o_party_type_code = 'PARTY_RELATIONSHIP';
• Filtering active accounts by combining ACCT_STATUS = 'A' with party status checks for lead candidate selection.
• Joining the view to AST lead or campaign tables on CUST_ACCOUNT_ID to resolve the correct party/account key for a TeleSales record.
• Extracting account role assignments by grouping on ROLE_TYPE and MEANING for role-coverage reporting.

Because the view performs joins across TCA entities, queries benefit from indexed predicates on CUST_ACCOUNT_ID and party identifiers. DBAs should account for the view's UNION and scalar subqueries against AR_LOOKUPS when tuning high-volume reports.