Search Results pos_bus_class_attr_u1

Overview

POS.POS_BUS_CLASS_ATTR is a transactional table in the Oracle E-Business Suite Procurement (POS) schema that stores supplier business classification attributes. In the TCA/Procurement model, business classifications describe diversity and small-business designations such as Women Owned, Minority Owned, Small Business, Veteran Owned, and similar categories. Each row associates a supplier, identified by its TCA party identifier, with one classification identified by a lookup type and lookup code combination.

The table also records administrative details that support verification of the classification, including the effective date range, certificate number, certifying agency, certificate expiration date, and an internal buyer review status (PENDING, APPROVED, or REJECTED). Under the heuristic Data Vault classification provided in the ETRM metadata, this object is described as satellite-leaning. That is a modeling suggestion: the table behaves as a descriptive satellite attached to supplier party and classification keys, rather than as an independent hub or a pure linking table, because it carries changing descriptive attributes keyed to a party and a classification.

Key Information Stored

The surrogate primary key is CLASSIFICATION_ID (NUMBER, 15), enforced by POS_BUS_CLASS_ATTR_PK1 and the unique index POS_BUS_CLASS_ATTR_U1. Two business-key candidates are documented as unique indexes:

• POS_BUS_CLASS_ATTR_U1 on CLASSIFICATION_ID — the surrogate key candidate.
• POS_BUS_CLASS_ATTR_U2 — a function-based unique index built from a DECODE expression over STATUS, PARTY_ID, LOOKUP_TYPE, LOOKUP_CODE, and, for non-active rows, CLASSIFICATION_ID. This construction allows multiple historical or inactive rows for the same supplier-classification pairing while permitting only one active row. The user query, pos_bus_class_attr_u2, refers directly to this index.

The most significant data columns include:

• PARTY_ID — the TCA supplier party identifier; joins to HZ_PARTIES.
• LOOKUP_TYPE and LOOKUP_CODE — the classification category and value.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective period of the classification.
• STATUS — the row-level active/inactive indicator used by the U2 index logic.
• CLASS_STATUS — buyer verification result (PENDING, APPROVED, REJECTED).
• CERTIFICATE_NUMBER, CERTIFYING_AGENCY, and EXPIRATION_DATE — certification evidence.
• VENDOR_ID, PARTY_SITE_ID, and VENDOR_SITE_ID — supplier and site context.
• EXT_ATTR_1 and ATTRIBUTE1–ATTRIBUTE5 — extensibility and descriptive flexfields.
• Audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Reporting on supplier diversity spend, validating certification currency, and surfacing pending approvals are common scenarios. A typical query retrieves active classifications for a supplier:

• SELECT classification_id, lookup_type, lookup_code, class_status FROM pos.pos_bus_class_attr WHERE party_id = :p_party_id AND status = 'A';
• Expired or near-expiry certificates: WHERE expiration_date < SYSDATE or a date-range filter.
• Verification queues: WHERE class_status = 'PENDING' for buyer review.

Related Objects

The FK relationship data identifies significant dependencies:

• HZ_PARTIES — referenced via POS_BUS_CLASS_ATTR.PARTY_ID; the primary supplier dimension join.
• POS_BUS_CLASS_REQS — references this table via CLASSIFICATION_ID, linking classification requests to their resulting attributes.

Supplementary joins typically extend to supplier site tables via PARTY_SITE_ID/VENDOR_SITE_ID and to lookup views keyed on LOOKUP_TYPE and LOOKUP_CODE.