Search Results edw_trd_partner_m_pk

Overview

EDW_TRD_PARTNER_M is a Trading Partner dimension table owned by the POA schema, delivered as part of the Purchasing Intelligence (POA) product within Oracle E-Business Suite 12.1.1 and 12.2.2. In the Oracle EBS architecture, POA provides the extract, transform, and load infrastructure that populates the Oracle Purchasing Intelligence data warehouse from operational purchasing, payables, and order management source systems. EDW_TRD_PARTNER_M serves as the conformed trading partner dimension within that warehouse, consolidating supplier and customer master attributes into a single reusable dimension that star-schema fact tables can join to.

The object is documented as VALID with a physical schema of 255 columns. The mined Data Vault classification is standalone, meaning no foreign key relationships to other objects were detected in the FK metadata. As a modeling suggestion, this classifies EDW_TRD_PARTNER_M as a dimension/satellite-style structure centered on the trading partner key rather than a link table between multiple hubs. Its role is descriptive and denormalized: it carries the attributes used for slicing and grouping purchasing and spend measures.

Key Information Stored

The table exposes two primary key constructs. The documented unique indexes are EDW_TRD_PARTNER_M_U1 (on TPLO_TPARTNER_LOC_PK and TPLO_TPARTNER_LOC_PK_KEY) and EDW_TRD_PARTNER_M_U2 (on TPLO_TPARTNER_LOC_PK_KEY alone). The PK constraint EDW_TRD_PARTNER_M_PK is built on TPLO_TPARTNER_LOC_PK_KEY, making that column the surrogate primary key. The business-key candidate is the composite of TPLO_TPARTNER_LOC_PK and TPLO_TPARTNER_LOC_PK_KEY as captured in U1.

The most significant columns include:

• TPLO_TPARTNER_LOC_PK_KEY — surrogate primary key for the trading partner location row.
• TPLO_TPARTNER_LOC_PK — source trading partner location identifier and part of the unique business key.
• TPLO_TPARTNER_LOC_DP — data presentation/natural descriptor for the location.
• TPLO_LEVEL_NAME, TPLO_NAME — descriptive name of the trading partner and its level.
• TPLO_BUSINESS_TYPE — classifies the partner as vendor, customer, or both.
• TPLO_ADDRESS_LINE1 through TPLO_POSTAL_CODE, TPLO_CITY, TPLO_STATE, TPLO_COUNTRY, TPLO_PROVINCE — the geographic address hierarchy for the location.
• TPLO_DATE_FROM, TPLO_DATE_TO — effective dating for the dimension row.
• TPLO_VNDR_PURCH_SITE, TPLO_VNDR_PAY_SITE, TPLO_VNDR_PAY_TERMS — vendor purchasing and payment site attributes.
• TPLO_CUST_SITE_USE, TPLO_CUST_PRIMARY_FLAG, TPLO_CUST_STATUS, TPLO_CUST_SIC_CODE — customer site attributes.
• TPRT_NAME, TPRT_VNDR_NUMBER, TPRT_CUST_NUMBER — trade partner level identifiers.
• ALL_ID, ALL_NAME, ALL_ALL_PK — final all-level identifier columns.
• CREATION_DATE, LAST_UPDATE_DATE — warehouse audit columns.

The presence of PTP1_, PTP2_, PTP3_, and PTP4_ column groups indicates denormalized secondary trading partner levels retained within the same row, alongside TPRT_ prefix columns for the primary trade partner level.

Common Use Cases and Queries

Principal use is as a dimension in Purchasing Intelligence spend and procurement reporting. Analysts join EDW_TRD_PARTNER_M to POA fact tables on the surrogate key to retrieve supplier or customer descriptions, addresses, and classifications for aggregation by partner, region, or business type.

• Spend analysis by supplier: aggregate fact measures joined to TPLO_NAME, TPLO_BUSINESS_TYPE, and TPLO_COUNTRY.
• Vendor master validation: query TPLO_VNDR_NUMBER, TPLO_VNDR_PAY_SITE, and TPLO_VNDR_PAY_TERMS to reconcile warehouse dimensions against AP_SUPPLIERS.
• Customer segmentation: filter on TPLO_CUST_SIC_CODE, TPLO_CUST_STATUS, and TPLO_CUST_PRIMARY_FLAG.
• Effective-dated snapshots: constrain on TPLO_DATE_FROM and TPLO_DATE_TO to reproduce historical reporting.

A representative pattern is: SELECT p.TPLO_NAME, SUM(f.amount) FROM poa_fact f JOIN poa.edw_trd_partner_m p ON f.tplo_tpartner_loc_pk_key = p.tplo_tpartner_loc_pk_key WHERE p.tplo_business_type = 'VENDOR' GROUP BY p.TPLO_NAME;

Related Objects

Although the mined classification is standalone, the dimension is populated and consumed through the standard POA warehouse pipeline. The most significant dependencies are:

• POA fact tables in the Purchasing Intelligence star schema, joined on TPLO_TPARTNER_LOC_PK_KEY.
• AP_SUPPLIERS / AP_SUPPLIER_SITES_ALL, the source of TPLO_VNDR_NUMBER and vendor site attributes.
• HZ_CUST_ACCOUNTS / HZ_CUST_ACCT_SITES_ALL, the source of TPLO_CUST_NUMBER and customer site attributes.
• PO_VENDORS and PO_VENDOR_SITES_ALL, the Purchasing source of vendor site flags such as TPLO_VNDR_PURCH_SITE.
• POA collection/interface tables that carry TPLO_REQUEST_ID, TPLO_OPERATION_CODE, TPLO_ERROR_CODE, and TPLO_COLLECTION_STATUS during load.
• Purchasing Intelligence concurrent programs and the POA ETL packages that refresh EDW_TRD_PARTNER_M.

These relationships are functional rather than enforced by declarative foreign keys, consistent with the standalone Data Vault heuristic recorded in the ETRM metadata.