Search Results tplo_instance

Overview

POA.EDW_TRD_PARTNER_M is the denormalized star-schema table underpinning the Trading Partner dimension within the Oracle E-Business Suite Enterprise Data Warehouse (EDW) / Daily Business Intelligence reporting layer. Owned by the POA schema and registered under FND Design Data BIS.EDW_TRD_PARTNER_M, it stores one row for each lowest-level trading partner location together with its parentage rolled up through every hierarchy level. In practice this means a single physical row can carry the address, business classification, payment, purchasing, and receivables attributes of a supplier site, a customer site use, and each intermediate party or account level above it, so that reporting tools can slice transactional facts by any level of the trading partner hierarchy without additional joins.

The object resides in the APPS_TS_SUMMARY tablespace with PCT Free 30, characteristic of pre-aggregated summary objects that are rebuilt or refreshed by concurrent collection programs rather than updated transactionally. Consistent with a mined Data Vault classification of standalone, the table is a wholly denormalized reporting artifact with no inbound or outbound foreign keys enforced by the database; a Data Vault model would instead treat the natural trading partner location identifier as a hub, and the descriptive, time-bounded attributes as satellites. Note that the location identifier itself is physically of type VARCHAR2(320), a legacy EBS convention where numeric surrogate keys are stored as character strings.

Key Information Stored

The primary key is EDW_TRD_PARTNER_M_PK on TPLO_TPARTNER_LOC_PK_KEY, a NUMBER surrogate that serves as the system-generated unique identifier for the row. Two unique indexes act as business-key candidates: EDW_TRD_PARTNER_M_U1 covers TPLO_TPARTNER_LOC_PK together with TPLO_TPARTNER_LOC_PK_KEY, while EDW_TRD_PARTNER_M_U2 covers TPLO_TPARTNER_LOC_PK_KEY alone. TPLO_TPARTNER_LOC_PK and TPLO_TPARTNER_LOC_DP hold the natural trading partner location identifier and its descriptive name respectively.

• TPLO_NAME — the trading partner location name as displayed in EBS.
• TPLO_LEVEL_NAME — the hierarchy level at which the record sits.
• TPLO_BUSINESS_TYPE — whether the partner is a vendor or customer and the originating level; documented values include CUSTOMER, CUSTOMER ACCOUNT, CUSTOMER SITE USE, PARTY, VENDOR, and VENDOR SITE.
• TPLO_CUST_SIC_CODE — the Standard Industrial Classification code carried at the customer location level; this is the column most frequently sought when reporting on customer industry segmentation.
• TPLO_CUST_NUMBER / TPLO_CUST_STATUS / TPLO_CUST_TAX_CODE via the CUST attribute family — customer account identity and receivables configuration.
• TPLO_VNDR_PURCH_SITE, TPLO_VNDR_PAY_SITE, TPLO_VNDR_PAY_TERMS — supplier purchasing and payment behavior flags.
• TPLO_DATE_FROM / TPLO_DATE_TO — the validity window over which the trading partner location is active.
• TPLO_REQUEST_ID, TPLO_OPERATION_CODE, TPLO_ERROR_CODE, TPLO_COLLECTION_STATUS, TPLO_INSTANCE — the standard EBS concurrent collection audit columns marking which loader run populated the row and whether it succeeded.
• TPRT_*, PTP1_* through PTP4_*, and ALL_* — repeating prefixed column blocks representing successive ancestor levels and the top-level node, each carrying its own surrogate key, name, SIC code, and attributes.
• CREATION_DATE / LAST_UPDATE_DATE — audit timestamps for the summary row itself.

Common Use Cases and Queries

The principal use case is dimensional reporting on purchasing and order management facts where trading partner attributes are required at a chosen granularity. A query filtering on the searched column typically aggregates spend or revenue by industry:

• SELECT tplo_cust_sic_code, COUNT(*) tp_count FROM poa.edw_trd_partner_m WHERE tplo_business_type = 'CUSTOMER' GROUP BY tplo_cust_sic_code ORDER BY 2 DESC;
• Roll-up reporting that walks from a site to its account and party uses the repeating prefix blocks, for example selecting TPLO_NAME alongside PTP1_NAME and ALL_NAME to display site, account, and top-level party on one report line.
• Data-quality monitoring joins the collection audit columns, filtering on TPLO_COLLECTION_STATUS or TPLO_ERROR_CODE to identify stale or failed trading partner records.
• Supplier rationalization reports filter TPLO_VNDR_PURCH_SITE = 'Y' and TPLO_BUSINESS_TYPE = 'VENDOR SITE' to isolate sites from which goods may be purchased.
• Point-in-time reporting applies TPLO_DATE_FROM and TPLO_DATE_TO to exclude expired partner relationships from period comparisons.

Because the table is a summary object, queries should restrict the level through TPLO_LEVEL_NAME or TPLO_BUSINESS_TYPE before aggregating; failing to do so double-counts a partner at every level of its hierarchy.

Related Objects

The metadata records no enforced referential constraints, so dependencies are logical rather than declarative. The most significant related objects are:

• POA.EDW_TRD_PARTNER_M's own unique indexes EDW_TRD_PARTNER_M_U1 and EDW_TRD_PARTNER_M_U2, which must be maintained by any custom load into APPS_TS_SUMMARY.
• Trading partner fact tables in the POA schema, which join on TPLO_TPARTNER_LOC_PK_KEY or TPLO_TPARTNER_LOC_PK to attach partner attributes to transactional measures.
• Customer master tables (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL) as the operational source for TPLO_CUST_* columns such as TPLO_CUST_SIC_CODE.
• Supplier master tables (PO_VENDORS, PO_VENDOR_SITES_ALL) as the source for TPLO_VNDR_* and TPRT_VNDR_* columns.
• HZ_PARTIES, the source of the PARTY-level TPLO_BUSINESS_TYPE value and the top-level ALL_* attributes.
• FND concurrent program definitions and the BIS collection programs (FND Design Data BIS.EDW_TRD_PARTNER_M) that populate TPLO_REQUEST_ID, TPLO_INSTANCE, and the collection status columns.
• APPS.FND_APPLICATION / FND_TABLES registration entries, which govern how the object is exposed to the EBS data dictionary and to DBI reporting.