Results for “user_added”

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

Overview

OZF_TP_TERRUSG_MAP is a Trade Management (OZF) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the mapping between territories and customer sites, tying individual customers, customer accounts, and party records to the trade territory structures defined in Oracle Trade Management. It functions as a resolution table: given a party, a customer account, or a specific account site, it identifies the territory to which that entity belongs. This underpins territory-based processing for trade promotions, accruals, fund allocation, and associated reporting within the OZF module.

Under the heuristic Data Vault classification mined from its foreign key structure, the table is treated as standalone, with no outgoing references captured by the vault heuristic. In Data Vault modeling terms, the table nonetheless behaves primarily as a link: it associates territory identifiers with customer and site master records rather than describing a single business entity. The suggested modeling interpretation is therefore a link between the territory dimension and the customer master hubs (accounts, account sites, and parties), carrying descriptive attributes such as dates, market qualifiers, and site-use codes.

Key Information Stored

The table is owned by the OZF schema and is documented in ETRM 12.2.2 with 20 physical columns. The surrogate primary key is OZF_TP_TERRUSG_MAP_ID, enforced by the constraint OZF_TP_TERRUSG_PK. The principal business-key columns and their content follow:

• TERRITORY_ID — the territory to which the mapped entity belongs; the core reference for territory resolution.
• CUST_ACCOUNT_ID — the customer account (HZ_CUST_ACCOUNTS) mapped to the territory.
• CUST_ACCT_SITE_ID — the specific customer account site (HZ_CUST_ACCT_SITES_ALL) mapped to the territory.
• PARTY_ID and ROLLUP_PARTY_ID — the trading party and its rollup (parent) party, supporting hierarchy-based territory assignment.
• SITE_USE_ID and BILL_TO_SITE_USE_ID — site-use locators, allowing mapping at bill-to or other site-use granularity.
• SITE_USE_CODE — the site-use purpose code associated with the mapped site.
• LOCATION_ID — the physical location reference.
• MARKET_QUALIFIER_TYPE and MARKET_QUALIFIER_REFERENCE — the qualifier classification and its value used to scope the territory mapping.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range during which the mapping is active.
• USER_ADDED — a flag indicating records entered manually rather than generated by territory assignment processes.
• Standard WHO audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage centers on resolving which territory governs a given customer or site, and on validating that mapped records fall within their active date window. A representative query retrieves active territory mappings for an account:

• Territory lookup by account: SELECT territory_id, cust_account_id, cust_acct_site_id, start_date_active, end_date_active FROM ozf.ozf_tp_terrusg_map WHERE cust_account_id = :p_account AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
• Site-level mapping joined to customer master: SELECT m.territory_id, s.site_use_code, s.location FROM ozf.ozf_tp_terrusg_map m, hz.hz_cust_acct_sites_all s WHERE m.cust_acct_site_id = s.cust_acct_site_id;
• Audit of manually added mappings using USER_ADDED = 'Y' to distinguish user entries from generated assignments.
• Rollup reporting grouped by ROLLUP_PARTY_ID to summarize territory coverage across a party hierarchy.

These patterns are commonly embedded in territory reporting, promotion eligibility checks, and reconciliation extracts for OZF trade processes.

Related Objects

The FK metadata documents two referenced objects, which form the primary join paths:

• HZ_CUST_ACCOUNTS — joined via CUST_ACCOUNT_ID; supplies customer account master details.
• HZ_CUST_ACCT_SITES_ALL — joined via CUST_ACCT_SITE_ID; supplies account site details.

Beyond the documented foreign keys, the table participates in the wider OZF territory model through TERRITORY_ID, which relates to the OZF territory definition structures (such as the trade territory and territory-qualifier tables), and through PARTY_ID and ROLLUP_PARTY_ID to the HZ party entities. Territory assignment and trade promotion setup depend on this mapping to determine eligible customers and sites.