Results for “rel_object_version_number”

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

Overview

AST_SUBJECT_RELATIONSHIP_V is an APPS-owned reporting view in the Oracle E-Business Suite TeleSales (AST) product. It presents a denormalized, query-friendly projection of party-to-party relationships maintained in the Oracle Trading Community Architecture (TCA) model, specifically those recorded in HZ_RELATIONSHIPS where both the subject and the object are parties (that is, where SUBJECT_TABLE_NAME and OBJECT_TABLE_NAME both resolve to HZ_PARTIES). The view is defined in the APPS schema, holds VALID status, and is documented for both EBS 12.1.1 and 12.2.2.

The view exists primarily to support TeleSales and customer-facing reporting, integration, and inquiry screens that need to resolve relationship direction, roles, party identifiers, and audit attributes without repeatedly joining the TCA base tables. It also decodes internal codes into user-facing meanings by way of AR_LOOKUPS, and it suppresses the TCA sentinel end date (31-12-4712) by converting it to NULL, so that "open-ended" relationships are presented cleanly to applications and reports.

Underlying Base Objects

The documented base objects referenced by the view are:

• HZ_RELATIONSHIPS (SYNONYM) — the primary source of relationship rows, aliased REL in the view text.
• HZ_PARTIES (SYNONYM) — referenced twice: once as OBJ to resolve the related party's name (PARTY_NAME), and once as RELPARTY to resolve the party number and originating system reference for the PARTY_ID side of the relationship.
• HZ_RELATIONSHIP_TYPES (SYNONYM) — aliased HZ, used to constrain the forward relationship code so that only the forward direction of each relationship type is returned.
• AR_LOOKUPS (VIEW) — joined twice (LKUP1 and LKUP2) as outer joins to translate OBJECT_TYPE into a PARTY_TYPE meaning and the relationship role into an HZ_RELATIONSHIP_ROLE meaning. Because these are outer joins (indicated by the (+) notation), rows are still returned when a lookup value is missing.

The join conditions restrict results to party-to-party relationships: REL.OBJECT_ID = OBJ.PARTY_ID and REL.PARTY_ID = RELPARTY.PARTY_ID. The relationship type join further requires that REL.OBJECT_TYPE, REL.SUBJECT_TYPE, REL.RELATIONSHIP_TYPE, and REL.RELATIONSHIP_CODE match the corresponding HZ_RELATIONSHIP_TYPES columns, effectively limiting the view to forward relationship codes.

Key Columns

• RELATIONSHIP_ID, SUBJECT_ID, OBJECT_ID — surrogate identifiers for the relationship, the subject party, and the related (object) party.
• SUBJECT_TYPE, OBJECT_TYPE, OBJECT_TYPE_MEANING — the party type code and its decoded meaning (for example, person or organization).
• OBJECT_NAME, PARTY_NAME — the party name of the related and subject parties respectively.
• PARTY_ID, PARTY_NUMBER, ORIG_SYSTEM_REFERENCE — the subject party's identifier, human-readable number, and originating system key.
• RELATIONSHIP_TYPE, RELATIONSHIP_CODE — the relationship classification and its specific code, with a decoded role meaning from the second lookup join.
• DIRECTIONAL_FLAG, START_DATE, END_DATE, STATUS — directionality of the relationship, its effective dates (with the 4712 sentinel decoded to NULL), and its status along with the related party's status.
• OBJECT_VERSION_NUMBER — exposed for both the relationship (REL) and the related party (RELPARTY); this is the column most directly associated with the user search term "party_object_version_number," and it is used for optimistic locking and change detection during integration.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE20 — the DFF (descriptive flexfield) context and segment values for the relationship.
• CREATED_BY_MODULE, APPLICATION_ID — also exposed for both the relationship and the related party, indicating the owning module and application.

Common Use Cases and Queries

Typical uses include TeleSales account-relationship inquiries, TCA relationship reconciliation, and integration extracts that need a flattened party-to-party relationship dataset. A representative query listing open professional relationships for a given party is:

SELECT party_number, party_name, object_name, relationship_code, object_type_meaning, start_date, end_date, object_version_number FROM apps.ast_subject_relationship_v WHERE party_id = :p_party_id AND status = 'A' ORDER BY object_name;

To detect records that changed since a prior extract, filter on the audit or version columns: SELECT relationship_id, object_version_number, last_update_date FROM apps.ast_subject_relationship_v WHERE last_update_date >= :p_since;. Because the view already resolves meanings and nullifies the end-date sentinel, it is generally preferable to the raw TCA joins when populating report or interface staging tables.