Results for “op_cust_asc”

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

Overview

OP_CUST_ASC is a table owned by the GML schema within the Oracle E-Business Suite Process Manufacturing Logistics (OPM) module. Its documented purpose is to store customer relationships, providing the associative structure that links one customer record to another customer record for a defined relationship type. In this capacity, the table does not hold a customer master record itself; rather it records the existence and nature of a relationship between two entries in the customer master table, OP_CUST_MST.

The ETRM documentation classifies OP_CUST_ASC as a Data Vault link, derived from its foreign key structure. This classification is a modeling suggestion rather than a physical attribute of the table. A link construct is appropriate because the table resolves a many-to-many association between customer entities, and its primary key is a composite of the participating business keys plus a discriminator column. The table is marked VALID in the ETRM metadata and consists of 17 documented columns in the 12.2.2 physical schema.

Key Information Stored

The primary key, OP_CUST_ASC_PK, is a composite surrogate defined over CUST_ID, ASSOC_TYPE, and ASSOCCUST_ID. The foreign keys disclose the business semantics: CUST_ID references OP_CUST_MST and identifies the primary customer in the relationship, while ASSOCCUST_ID also references OP_CUST_MST and identifies the associated customer. ASSOC_TYPE is the discriminator that qualifies the nature of the linkage and participates in the primary key, allowing multiple relationship types between the same pair of customers.

The foreign key CUST_ID → OP_CUST_MST and the parallel ASSOCCUST_ID → OP_CUST_MST are the two most operationally significant columns because they carry the full relationship graph. TEXT_CODE references OP_TEXT_HDR and attaches descriptive text or a text header to the relationship. OPM_CUST_ID and OPM_ASSOCCUST_ID provide parallel identifiers used within the OPM context. Standard WHO columns, including CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, capture audit lineage. DELETE_MARK supports soft deletion, TRANS_CNT provides optimistic locking for concurrent update control, and the PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID columns record the concurrent program context that last touched the row. No unique index beyond the primary key is documented, so the composite PK serves as the sole business-key candidate.

Common Use Cases and Queries

Typical use cases include retrieving the full relationship network for a customer, validating that an association is not self-referential, and reporting relationships by type. A representative query resolves both customer ends and filters active rows:

• Join both customer references back to OP_CUST_MST using CUST_ID and ASSOCCUST_ID to obtain customer names and status.
• Filter DELETE_MARK = 0 to exclude soft-deleted relationships and constrain by ASSOC_TYPE for type-specific reporting.
• Aggregate counts by ASSOC_TYPE to identify relationship distribution across the customer base.
• Join TEXT_CODE to OP_TEXT_HDR to retrieve descriptive commentary attached to each relationship.

A sample pattern is: SELECT a.CUST_ID, a.ASSOCCUST_ID, a.ASSOC_TYPE FROM GML.OP_CUST_ASC a WHERE a.DELETE_MARK = 0 AND a.CUST_ID = :p_cust_id. Incremental extracts should key on LAST_UPDATE_DATE combined with TRANS_CNT for change detection.

Related Objects

The most significant related objects are those joined through the documented foreign keys:

• OP_CUST_MST — referenced twice, via CUST_ID and ASSOCCUST_ID; the customer master supplying both ends of the relationship.
• OP_TEXT_HDR — referenced via TEXT_CODE; supplies associated textual descriptions.
• OP_CUST_ASC_PK — the composite primary key constraint over CUST_ID, ASSOC_TYPE, ASSOCCUST_ID.

Because both customer references converge on OP_CUST_MST, any report of customer relationships must join that table twice, once for each alias. This dual dependency makes OP_CUST_MST the single most important companion object to OP_CUST_ASC.