Results for “dep_2fields_1char_1id”
13 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
APPS.JTF_TERR_VALUES_DESC_V is a descriptive (denormalized) view in the Oracle E-Business Suite Territory Management (ETRM) schema. It presents the range and value records stored in the territory qualifier value table, but augments each low- and high-value attribute with a human-readable description resolved at query time. The description logic is driven by the qualifier's display type (DISPLAY_TYPE) and is centralized in the JTF_TERRITORY_PVT package, which exposes the GET_TERR_VALUE_DESC function. Rather than persisting redundant descriptive text, the view derives descriptions dynamically so that reporting tools and integration interfaces can display meaningful values without replicating ETRM decoding rules.
The view is significant because territory qualifiers can be numeric, character-based, interest-type based, or compound identifiers. The view abstracts these distinctions and returns a single LOW_VALUE_CHAR_DESC and HIGH_VALUE_CHAR_DESC column pair that consumers can present directly. In Oracle EBS 12.1.1 and 12.2.2, this view supports territory administration inquiries, assignment/qualifier reports, and custom integrations that need to expose qualifier ranges in a user-friendly form.
Underlying Base Objects
The view is defined by joining several ETRM base objects, documented as follows:
- JTF_TERR_VALUES_ALL (synonym) — the primary source of territory qualifier value rows, supplying TERR_VALUE_ID, TERR_QUAL_ID, INCLUDE_FLAG, COMPARISON_OPERATOR, LOW_VALUE_CHAR, HIGH_VALUE_CHAR, numeric value columns, VALUE_SET, INTEREST_TYPE_ID, and related identifier columns.
- JTF_TERR_QUAL (synonym) — the qualifier definition table, aliased here as JQU and providing DISPLAY_TYPE, CONVERT_TO_ID_FLAG, COLUMN_COUNT, DISPLAY_SQL1, and DISPLAY_SQL2, which control how descriptions are resolved.
- JTF_QUAL_USGS, JTF_TERR_CNR_GROUPS (synonyms) — supporting qualifier usage and territory container grouping references within the ETRM model.
- JTF_TERRITORY_PVT (package) — the PL/SQL package whose GET_TERR_VALUE_DESC function translates stored values into display descriptions.
The join between JTF_TERR_VALUES_ALL and JTF_TERR_QUAL on TERR_QUAL_ID links each stored value to its qualifier definition, allowing the view to select the appropriate decoding logic per row.
Key Columns
- TERR_VALUE_ID — primary identifier for the territory value row.
- TERR_QUAL_ID — foreign key to the qualifier definition that governs the value.
- LOW_VALUE_CHAR / HIGH_VALUE_CHAR — the stored low and high character values of the range.
- LOW_VALUE_CHAR_DESC / HIGH_VALUE_CHAR_DESC — the derived, readable descriptions. The DECODE logic branches on DISPLAY_TYPE: 'CHAR' rows either convert an ID via id_used_flag or return the raw value; 'DEP_2FIELDS_1CHAR_1ID' rows combine two fields using DISPLAY_SQL1 (low) and DISPLAY_SQL2 (low value char ID) for the high value; other types return NULL where not applicable.
- LOW_VALUE_NUMBER / HIGH_VALUE_NUMBER — numeric range bounds.
- COMPARISON_OPERATOR / INCLUDE_FLAG — define how values are compared and whether the qualifier is inclusive.
- INTEREST_TYPE_ID — populated with a description only when DISPLAY_TYPE equals 'INTEREST_TYPE', using DISPLAY_SQL1.
- PRIMARY_INTEREST_CODE / SECONDARY_INTEREST_CODE — interest-code descriptions resolved via DISPLAY_SQL2 when DISPLAY_TYPE is 'INTEREST_TYPE'.
- VALUE_SET and audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide value-set context and standard EBS audit tracking.
Common Use Cases and Queries
Typical usage includes territory qualifier review, reporting on assignment ranges, and integrations that must present qualifier values in decoded form.
List all values for a qualifier:
SELECT TERR_VALUE_ID, LOW_VALUE_CHAR_DESC, HIGH_VALUE_CHAR_DESC, COMPARISON_OPERATOR, INCLUDE_FLAG FROM APPS.JTF_TERR_VALUES_DESC_V WHERE TERR_QUAL_ID = :p_qual_id;
Retrieve interest-type qualifier descriptions:
SELECT INTEREST_TYPE, PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE FROM APPS.JTF_TERR_VALUES_DESC_V WHERE TERR_QUAL_ID = :p_qual_id AND INTEREST_TYPE IS NOT NULL;
Because descriptions are computed in SQL, queries should filter on indexed identifiers such as TERR_QUAL_ID rather than on description columns to avoid full scans. For high-volume extracts, restrict by TERR_VALUE_ID ranges and select only the required columns.