Search Results first_rows




Overview
Explore in AI Chat

Overview

The CS_SYSTEM_CONTACTS_RG_V view is a Service (CS) module reporting object in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a unified, de-normalized list of "system contacts" by combining parties defined directly in the Trading Community Architecture (TCA) registry with parties that participate in a contact relationship. The view is intended to drive list-of-values (LOV), search, and reference screens where a user must select a person or organization that can act as a contact. The _RG_V naming convention indicates a reporting/retrieval-oriented view rather than a base transactional entity. The ETRM metadata records the view as "Not implemented in this database" for that particular instance, and no base objects are formally documented as references; the definition is nonetheless supplied in full text and is reproduced conceptually below.

Underlying Base Objects

The view is defined over three underlying TCA objects:

The two branches are combined with UNION ALL. The first branch returns parties directly (with the party's own ID as ORIGINAL_PARTY_ID); the second returns contacts joined through HZ_PARTY_RELATIONSHIPS, where the relationship's OBJECT_ID becomes ORIGINAL_PARTY_ID. Both branches use an outer join to AR_LOOKUPS and a PARTY_ID > 0 filter, and both are hint-driven with FIRST_ROWS. Documented references to these base objects are absent from the ETRM metadata, but the view text confirms the dependencies.

Key Columns

  • PARTY_NAME — display name of the party or contact.
  • PARTY_NUMBER — the TCA-generated unique party number.
  • PARTY_TYPE — the raw party type code (e.g., PERSON, ORGANIZATION).
  • PARTY_ID — the TCA party identifier; the join key to HZ_PARTIES.
  • MEANING — the decoded lookup meaning for the party type from AR_LOOKUPS.
  • SORT_ORDER — a derived sort key; the DECODE maps PERSON to the literal 'AAAA' so that persons sort ahead of other types in an ascending LOV ordering.
  • ORIGINAL_PARTY_ID — for direct parties, the party's own ID; for contact relationships, the owning party's OBJECT_ID. This column anchors the contact back to its parent party.

Common Use Cases and Queries

Typical uses include populating contact LOVs, driving service-request assignment, and reconciling contacts against their originating parties. A representative query retrieving contacts with their parent party is:

  • SELECT party_name, party_number, party_type, meaning, original_party_id FROM cs_system_contacts_rg_v WHERE party_id = :p_party_id ORDER BY sort_order, party_name;
  • Contact LOV: SELECT party_name, party_id FROM cs_system_contacts_rg_v WHERE party_type = 'PERSON' ORDER BY sort_order, party_name;
  • Filtering by parent: SELECT * FROM cs_system_contacts_rg_v WHERE original_party_id = :p_parent_party_id;

Because both branches are UNION ALL, callers should apply appropriate filters and consider the deduplication implications when a party appears both as a direct party and as a contact. The FIRST_ROWS hints favor interactive retrieval over bulk extraction.