Results for “ozf_cust_prod_cont_all”

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

Overview

OZF_CUST_PROD_CONT_ALL is a Trade Management (OZF) transactional table that stores customer–product contact information. It associates a specific contact — either an organization contact or a person — with a customer account, a customer account site, and an optional product level within the Trade Management product hierarchy. The table therefore acts as the operational bridge between Oracle Receivables trading entities and the contacts that participate in trade promotion and customer planning activities.

The heuristic Data Vault classification mined from the foreign-key structure is link. This is a modeling suggestion rather than a documented EBS fact: the table carries a single-column surrogate primary key (CUST_PROD_CONTACT_ID) and resolves several many-to-many relationships — between customer accounts, account sites, party site uses, contact roles, and product hierarchy levels. Its descriptive and who-columns (LAST_UPDATE_DATE, CREATED_BY, CREATED_FROM, ATTRIBUTE1–15) also exhibit satellite-like characteristics, so a hybrid link-with-satellite treatment is reasonable when mapping to a Data Vault model.

Key Information Stored

• CUST_PROD_CONTACT_ID — surrogate primary key and the column behind the unique index OZF_CUST_PROD_CONT_ALL_U1 and constraint OZF_CUST_PROD_CONT_ALL_PK. It is the only business-key candidate documented; there is no separate natural unique key exposed in the metadata.
• CUSTOMER_ID — identifies the customer (party) to which the contact assignment belongs.
• CUST_ACCOUNT_ID — FK to HZ_CUST_ACCOUNTS, the customer account header.
• CUST_ACCT_SITE_ID — FK to HZ_CUST_ACCT_SITES_ALL, the specific account site for the assignment.
• PARTY_SITE_ID and PARTY_SITE_USE_ID — the underlying party site and its site use, the latter being an FK to HZ_PARTY_SITE_USES.
• ORG_CONTACT_ID and PERSON_ID — the organization contact and the person record representing the contact individual.
• ORG_CONTACT_ROLE_ID — FK to HZ_ORG_CONTACT_ROLES, defining the contact's role (for example, buyer, planner, or promotions contact).
• PRODUCT_HIERARCHY, PRODUCT_LEVEL, and PRODUCT_LEVEL_VALUE — define the product scope at which the contact is relevant, allowing varying levels of the Trade Management product hierarchy.
• ORG_ID and SECURITY_GROUP_ID — multi-org operating unit and security group context; SECURITY_GROUP_ID is an FK to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER and the WHO/audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) — standard concurrency control and audit trail.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, CREATED_FROM — concurrent program and record-origin metadata.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — DFF-enabled descriptive flexfield storage.

Common Use Cases and Queries

The primary use case is determining which contacts are associated with a customer or account site for a given product level, supporting trade promotion setup, customer planning, and contact reporting. A representative join resolves the contact to its Oracle Trading Community Architecture (TCA) parties:

• Reporting the contacts tied to an account site: SELECT c.cust_prod_contact_id, c.org_contact_id, c.person_id, c.org_contact_role_id FROM ozf.ozf_cust_prod_cont_all c WHERE c.cust_acct_site_id = :site_id;
• Resolving role descriptions by joining ORG_CONTACT_ROLE_ID to HZ_ORG_CONTACT_ROLES.
• Listing assignments for a product level: filter on PRODUCT_HIERARCHY, PRODUCT_LEVEL, and PRODUCT_LEVEL_VALUE.
• Data-quality checks for orphaned or duplicate contact assignments per account, site, and product level, including auditing via ATTRIBUTE columns and ORG_ID for multi-org reporting.

Related Objects

• HZ_CUST_ACCOUNTS — joined on CUST_ACCOUNT_ID; provides the customer account header.
• HZ_CUST_ACCT_SITES_ALL — joined on CUST_ACCT_SITE_ID; provides the account site.
• HZ_PARTY_SITE_USES — joined on PARTY_SITE_USE_ID; provides the party site use.
• HZ_ORG_CONTACT_ROLES — joined on ORG_CONTACT_ROLE_ID; supplies role definitions.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; supplies security group context.
• HZ_PARTIES / HZ_PERSON_PROFILES (via PERSON_ID and ORG_CONTACT_ID) — supplies party and person details for the contact.
• OZF product hierarchy and promotion tables — consume CUSTOMER_ID and the PRODUCT_* columns to scope trade promotion contacts.

Because the FK graph resolves cleanly into TCA, any query joining OZF_CUST_PROD_CONT_ALL to HZ_* tables should filter on ORG_ID and SECURITY_GROUP_ID to respect multi-org and security-group access in Release 12.1.1 and 12.2.2.