Results for “agent_site_id”

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

Overview

OKX_INSURANCE_AGENT_V is a reporting and integration view owned by the APPS schema within the OKX – Contracts Integration product family in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description indicates, the view exposes insurance agent records: third-party parties registered in the Trading Community Architecture (TCA) model whose category code is INSURANCE_AGENT, joined to their associated party sites. The view presents a denormalized, flattened projection of agent identity, address, and site data, which allows downstream contract-integration components, concurrent programs, and custom reports to resolve insurance agents without navigating the multi-table TCA party model directly.

Because the view is a stored database object rather than an interface table, it carries no transactional state of its own. Its role is read-only: it furnishes a stable, query-friendly surface over HZ_PARTIES and HZ_PARTY_SITES for any consumer that must identify, display, or select an insurance agent, including selection of the specific agent site that the caller searched for under the column name AGENT_SITE_ID.

Underlying Base Objects

The view is defined over two TCA base objects, both referenced through APPS synonyms:

• HZ_PARTIES — the master registry of persons, organizations, and groups, supplying party identity, party number, category, name components, and address attributes.
• HZ_PARTY_SITES — the association between a party and a physical location, supplying PARTY_SITE_ID.

The join is an inner join on PARTY_ID, restricted by the predicate PRT.CATEGORY_CODE = 'INSURANCE_AGENT'. Consequently, only parties explicitly categorized as insurance agents appear, and only where at least one party site exists. The view text also emits two literal expressions: a constant '#' aliased as ID2 and the constant string 'THIRD_PARTY' exposed through the AGENT_TYPE column. These literals are presentation conveniences consumed by the integration layer rather than data derived from TCA.

Key Columns

• PARTY_ID / PARTY_NAME / PARTY_NUMBER — primary identifiers and display name of the agent party.
• CATEGORY_CODE — always INSURANCE_AGENT in this view; useful as a filter anchor in ad hoc queries.
• ADDRESS1 through ADDRESS4, CITY, STATE, PROVINCE, COUNTRY, POSTAL_CODE, COUNTY — the individual address attributes sourced from HZ_PARTIES.
• ADDRESS — a concatenated single-line rendering of the address attributes, suitable for display in LOVs and reports.
• PERSON_FIRST_NAME, PERSON_MIDDLE_NAME, PERSON_LAST_NAME, PERSON_TITLE, PERSON_ACADEMIC_TITLE, EMAIL_ADDRESS — person-type agent detail; note that these columns appear in the documented column list under the abbreviated names FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE, and ACADEMIC_TITLE.
• STATUS, THIRD_PARTY_FLAG — party status and third-party indicator, enabling filtering to active or external agents.
• AGENT_SITE_ID — the party site identifier from HZ_PARTY_SITES (PARTY_SITE_ID). This is the column targeted by the search term "agent_site_id" and serves as the link between the agent and a specific address site.
• AGENT_TYPE — the constant literal 'THIRD_PARTY' identifying the classification of rows returned by this view.

Common Use Cases and Queries

Typical scenarios include populating insurance-agent selection lists in contracts integration flows, resolving an agent's site for a given contract, and validating agent existence by site identifier. A representative query retrieving active third-party agents and their sites follows:

• SELECT party_id, party_name, party_number, agent_site_id, address, email_address FROM okx_insurance_agent_v WHERE status = 'A' ORDER BY party_name;
• SELECT party_name, agent_site_id FROM okx_insurance_agent_v WHERE agent_site_id = :p_site_id;
• SELECT party_id, party_name, agent_type FROM okx_insurance_agent_v WHERE category_code = 'INSURANCE_AGENT' AND third_party_flag = 'Y';

Because the view performs the category restriction internally, callers need not re-filter on CATEGORY_CODE, though joining back to HZ_PARTY_SITES on AGENT_SITE_ID remains the standard method for retrieving additional site-level attributes such as location identifiers or site-use assignments.