Results for “system_number”

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

Overview

AST_LM_CKEY_CSI_V is an APPS-owned database view within the Oracle E-Business Suite TeleSales (AST) product family. Its name reflects its function: it resolves "CKEY" (contact key) information for Customer Service Item instances ("CSI"), exposing a consolidated, reporting-friendly projection of installed-base assets joined to their owning parties and accounts. The view sits in the Oracle TeleSales / Customer Intelligence layer, where asset and installed-base data must be enriched with customer, contact, and account attributes for use in telesales scripts, lead qualification, and service workflows.

The view is documented as VALID in the ETRM metadata for both EBS 12.1.1 and 12.2.2, with no changes in owner or referenced base objects between releases. Because it is a view rather than a table, it stores no data of its own; it is a read-only logical construct that joins multiple operational tables at query time. It is therefore consumed primarily for reporting, integration extracts, and embedded lookups rather than for transactional DML. The presence of the SYSTEM_NUMBER column — the term users most often search for — indicates the view surfaces the system identifier associated with a CSI item instance, enabling asset-to-system correlation.

Underlying Base Objects

The view is defined over a documented set of base objects, most of which are accessed through APPS synonyms. The ETRM metadata lists the following referenced objects:

• CSI_ITEM_INSTANCES (synonym) — the driving table, alias CSII, supplying INSTANCE_ID, SERIAL_NUMBER, EXTERNAL_REFERENCE, SYSTEM_ID, and INSTANCE_DESCRIPTION.
• CSI_I_PARTIES (synonym) — alias CSIIP, providing PARTY_ID, RELATIONSHIP_TYPE_CODE, and the instance-party join key.
• CSI_IP_ACCOUNTS (synonym) — alias CSIAC, linking instance parties to customer accounts.
• HZ_CUST_ACCOUNTS (synonym) — alias HCA, supplying ACCOUNT_NUMBER and ACCOUNT_NAME.
• HZ_PARTIES (synonym) — alias HP, the master party record providing PARTY_NAME, address, status, and contact attributes.
• HZ_CONTACT_POINTS (synonym) — alias HCP, restricted to phone contact points with PRIMARY_FLAG = 'Y'.
• FND_TERRITORIES_TL (synonym) — alias FND, resolving COUNTRY to TERRITORY_SHORT_NAME in the user's language.
• AR_LOOKUPS (view) — aliased twice (ALK and ALK1) to translate PARTY_TYPE and STATUS lookup codes into their MEANING values.

All account and contact-point joins are outer joins, so assets without a linked account or primary phone are still returned. The joins to AR_LOOKUPS and HZ_PARTIES, by contrast, are inner joins filtered on ENABLED_FLAG = 'Y'.

Key Columns

The view exposes the following columns of note:

• INSTANCE_ID — primary identifier of the CSI item instance; the anchor of the view.
• SERIAL_NUMBER and EXTERNAL_REFERENCE — manufacturer or external identifiers for the asset.
• SYSTEM_NUMBER — the system identifier linked to the instance. Note that although the documented view text selects CSII.SYSTEM_ID, the column list exposes the attribute as SYSTEM_NUMBER; this is the field users search for when correlating assets to systems.
• PARTY_ID, PERSON_ID, RELATIONSHIP_ID, RELATIONSHIP_CODE — party and relationship context; PERSON_ID and RELATIONSHIP_ID are hard-coded to 0 in the view text.
• ACCOUNT_NUMBER, ACCOUNT_NAME — customer account attributes from HZ_CUST_ACCOUNTS.
• PARTY_NAME, PARTY_TYPE, PARTY_TYPE_CODE — the owning party's name and decoded party type.
• Address columns (ADDRESS1–4, CITY, STATE, PROVINCE, POSTAL_CODE, COUNTY, COUNTRY) — party address details, with COUNTRY resolved via FND_TERRITORIES_TL.
• EMAIL_ADDRESS, PHONE_NUMBER — contact details; PHONE_NUMBER is a concatenation of country code, area code, number, and extension.
• STATUS, STATUS_CODE — decoded and raw party status values.
• INSTANCE_NAME — the instance description label.

Common Use Cases and Queries

Typical uses include querying installed-base assets by system number, extracting customer-facing asset reports, and feeding telesales or service agents with contact and account context.

• Retrieve all assets for a given system: SELECT instance_id, serial_number, system_number, party_name FROM ast_lm_ckey_csi_v WHERE system_number = :p_system;
• List assets by customer account: SELECT account_number, serial_number, instance_name, status FROM ast_lm_ckey_csi_v WHERE account_number = :p_acct ORDER BY serial_number;
• Extract contactable assets with phone and email: SELECT party_name, phone_number, email_address, serial_number FROM ast_lm_ckey_csi_v WHERE phone_number IS NOT NULL;
• Report asset counts by party type: SELECT party_type, COUNT(*) FROM ast_lm_ckey_csi_v GROUP BY party_type;

Because joins to accounts and contact points are outer joins, filters on those columns implicitly restrict results to assets that have the corresponding data. Queries should therefore apply such filters deliberately.