Results for “cs_resource_details_v”

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

Overview

CS_RESOURCE_DETAILS_V is a Service (CS) module view in Oracle E-Business Suite 12.1.1 and 12.2.2 that presents a unified, denormalized list of resources available for assignment to service requests, tasks, and field service activities. Rather than exposing the raw structure of the Trading Community Architecture (TCA) party model, the view flattens the relevant party attributes — name, number, identifier, type, and a concatenated postal address — into columns that map directly to the "resource" concept used throughout Oracle Service and Field Service. Its principal role is to serve as a lookup and validation source: pickers, LOVs, and integration extracts query it to resolve a resource identifier to a display name, or to enumerate parties eligible to be treated as resources.

The view is documented as not implemented in the source database, meaning it is defined as a view object rather than materialized. Note that the view text carries an explicit /*+ RULE */ hint, forcing the rule-based optimizer regardless of the database optimizer mode. This reflects its vintage, and it has performance implications for large party populations.

Underlying Base Objects

Although the ETRM metadata for 12.2.2 records no documented base objects, the embedded view text shows the definition is a UNION of two queries over the following objects:

• HZ_PARTIES (HP, aliased in both branches) — the TCA master party table supplying party name, party number, party ID, party type, and the individual address component columns (ADDRESS1 through ADDRESS4, CITY, STATE, POSTAL_CODE, PROVINCE, COUNTY).
• HZ_PARTY_RELATIONSHIPS (HPR) — used only in the second branch to identify contacts via the 'CONTACT_OF' relationship type.
• AR_LOOKUPS (ARL) — outer-joined on LOOKUP_TYPE = 'PARTY_TYPE' in both branches; the join is decorative in the sense that no ARL column is projected, but it constrains the lookup domain.

The two branches are joined by UNION (not UNION ALL), which forces a sort/distinct operation and can eliminate duplicate rows where a party satisfies both branches. The first branch returns parties directly; the second returns parties that participate as subjects in contact relationships, reporting the relationship's OBJECT_ID as ORIGINAL_PARTY_ID so the contact can be traced back to the owning party.

Key Columns

• RESOURCE_NAME — sourced from HP.PARTY_NAME; the display name of the resource.
• RESOURCE_NUMBER — sourced from HP.PARTY_NUMBER; the human-readable party number.
• RESOURCE_ID — sourced from HP.PARTY_ID; the surrogate identifier used to link back to TCA.
• RESOURCE_TYPE — the party type (e.g., PERSON, ORGANIZATION, PARTNER), driving the sort and display categorization.
• ADDRESS — a concatenation of the party's address columns into a single string. Note the second branch drops one delimiter before COUNTY.
• ORIGINAL_PARTY_ID — the party ID itself in the first branch, or the relationship OBJECT_ID in the second; identifies the owning party for contacts.
• RESOURCE_CATEGORY — a literal, always 'PARTY', indicating every row originates from the party model.
• SORT_ORDER — this is the column users most commonly search for. It is a computed DECODE(HP.PARTY_TYPE, 'PERSON', 'AAAA', HP.PARTY_TYPE), substituting the literal 'AAAA' for persons so that person resources sort ahead of other party types in alphabetical ordering.

Common Use Cases and Queries

Typical usages include resolving a resource ID to a name, listing assignable resources in a picker, and extracting party-based resource data for integration. A representative query ordering results as the view intends:

• SELECT resource_id, resource_name, resource_type, sort_order FROM cs_resource_details_v WHERE resource_id = :party_id;
• SELECT resource_name FROM cs_resource_details_v WHERE UPPER(resource_name) LIKE UPPER(:name||'%') ORDER BY sort_order, resource_name;
• SELECT resource_category, COUNT(*) FROM cs_resource_details_v GROUP BY resource_category;

Because of the UNION-driven distinct and the RULE hint, queries should be narrowly filtered, especially against large HZ_PARTIES volumes, to avoid full scans and expensive sorts.