Results for “op_cust_con”

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

Overview

OP_CUST_CON is a transactional master table in the Oracle E-Business Suite Process Manufacturing Logistics (GML) module. It stores customer contact information — the individuals, phone numbers, fax numbers, and electronic mail addresses associated with a customer record — that is consumed by order and proposal processing. The table resides in the GML schema and is marked VALID in the ETRM 12.2.2 data dictionary. Its physical definition includes 54 columns, encompassing a small set of business attributes and a wide block of descriptive flexfield segments (ATTRIBUTE1 through ATTRIBUTE30 plus ATTRIBUTE_CATEGORY), which reflects the standard OPM/Process Manufacturing design convention for extensibility.

From a relational modeling perspective, the mined FK structure suggests this object is hub-leaning. It is a candidate hub because it owns a single-column surrogate primary key, CONTACT_ID, that carries no dependent business measures — the classic hallmark of a hub entity in a Data Vault model. It also participates as a link target, since downstream transactional tables reference CONTACT_ID. This classification is offered as a modeling suggestion rather than a documented Oracle attribute.

Key Information Stored

The most significant columns are:

• CONTACT_ID — the surrogate primary key, enforced by OP_CUST_CON_PK. It uniquely identifies each contact row and is the column referenced by all foreign keys pointing to this table.
• CONTACT_NAME — the business-facing name of the contact. Together with CUST_ID it forms the unique business key enforced by OP_CUST_CON_U1, making it the principal alternate (natural) key candidate.
• CUST_ID — the owning customer, joined to OP_CUST_MST.
• OPM_CUST_ID — the Process Manufacturing customer identifier, relevant in OPM-integrated environments.
• ADDR_ID — the address record associated with the contact, joined to SY_ADDR_MST.
• CONTACT_TYPE — classifies the contact (for example, order contact versus remit-to contact).
• CONTACT_PHONE, CONTACT_FAX, and EMAIL_ADDRESS — the communication channels used on order documents and correspondence.
• ACCEPT_ORDR_IND — an indicator controlling whether the contact is eligible to receive order acknowledgements.
• DELETE_MARK — the logical deletion flag used throughout OPM tables.
• TRANS_CNT — an optimistic locking / transaction counter.
• OF_CONTACT_ID and TEXT_CODE — an originating contact reference and a text/lookup code.

Audit columns include CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN, while the concurrent program columns PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID track batch maintenance. Note that CONTACT_ID is the surrogate key, whereas the unique index OP_CUST_CON_U1 (CONTACT_NAME plus CUST_ID) is the business-key candidate.

Common Use Cases and Queries

Typical scenarios include retrieving the active contacts for a customer, listing contacts eligible for order acknowledgements, and resolving the contact name printed on a sales order. A representative query:

SELECT c.contact_id, c.contact_name, c.contact_phone, c.email_address FROM gml.op_cust_con c WHERE c.cust_id = :cust_id AND NVL(c.delete_mark,0) = 0 AND c.accept_ordr_ind = 'Y';

Resolving a contact from an order header uses the FK relationship: SELECT h.order_no, c.contact_name FROM gml.op_ordr_hdr h, gml.op_cust_con c WHERE h.contact_id = c.contact_id;. Reporting use cases include contact-phone directories by customer, completeness audits of email addresses, and reconciliation of OP_ORDR_HDR, OP_ORDR_DTL, OP_PRSL_HDR, and OP_PRSL_DTL against the contacts they cite.

Related Objects

OP_CUST_CON sits between the customer and address masters and the OPM order and proposal tables. The principal relationships, using documented join columns, are:

• OP_CUST_MST — parent customer master; joined via OP_CUST_CON.CUST_ID = OP_CUST_MST.CUST_ID.
• SY_ADDR_MST — address master; joined via OP_CUST_CON.ADDR_ID = SY_ADDR_MST.ADDR_ID.
• OP_ORDR_HDR — references this table through OP_ORDR_HDR.CONTACT_ID.
• OP_ORDR_DTL — references this table through OP_ORDR_DTL.CONTACT_ID.
• OP_PRSL_HDR — references this table through OP_PRSL_HDR.CONTACT_ID.
• OP_PRSL_DTL — references this table through OP_PRSL_DTL.CONTACT_ID.

These four dependent tables make OP_CUST_CON a referenced hub for OPM order and proposal processing; any purge or merge of contact rows must respect those inbound foreign keys.