Results for “jtf_tr_source_denorm_v”
16 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
JTF_TR_SOURCE_DENORM_V is a denormalized view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the JTF (CRM Foundation) product family, which supplies the shared infrastructure used by Oracle Territory Manager, Oracle Sales, Oracle TeleSales, and related CRM modules. The view is documented as the "Territories Denormalized Source View" and carries a status of VALID in the ETRM repository.
Its purpose is to flatten the multi-valued relationship between a source record and its related identifiers. The base table JTF_SOURCES_ALL stores up to five separate related identifier slots (RELATED_ID1 through RELATED_ID5) on a single row. Territory qualification logic, however, frequently needs to evaluate each related identifier as an independent value. JTF_TR_SOURCE_DENORM_V performs this transformation by emitting one row per populated related identifier slot, paired with the owning SOURCE_ID. This makes it a convenient reporting and integration surface for queries keyed on a source identifier.
Underlying Base Objects
The view is defined solely over the single documented base object JTF_SOURCES_ALL, which is referenced in the ETRM metadata as a SYNONYM (resolving to the underlying APPS-owned source table). The view text is a five-way UNION of self-joins against that table:
- SELECT SOURCE_ID, RELATED_ID1 FROM JTF_SOURCES_ALL WHERE RELATED_ID1 IS NOT NULL
- UNION with SOURCE_ID, RELATED_ID2 ... WHERE RELATED_ID2 IS NOT NULL
- UNION with SOURCE_ID, RELATED_ID3 ... WHERE RELATED_ID3 IS NOT NULL
- UNION with SOURCE_ID, RELATED_ID4 ... WHERE RELATED_ID4 IS NOT NULL
- UNION with SOURCE_ID, RELATED_ID5 ... WHERE RELATED_ID5 IS NOT NULL
Because the view is a UNION (not UNION ALL), duplicate (SOURCE_ID, RELATED_ID) pairs are collapsed. Each branch filters out NULL related identifiers, so only populated slots contribute rows. A single source record with three populated related identifier slots therefore produces up to three rows. No joins to other entities are embedded in the view; consumers are expected to join the exposed RELATIVE_ID value to the appropriate entity table depending on context.
Key Columns
- SOURCE_ID — The identifier of the source record in JTF_SOURCES_ALL. This is the primary correlation key and the column most often used in WHERE predicates, including searches originating from a "source_id" lookup.
- RELATED_ID — The denormalized related identifier. This column is an alias produced by the UNION: it corresponds positionally to RELATED_ID1, RELATED_ID2, RELATED_ID3, RELATED_ID4, or RELATED_ID5 from the base table, whichever slot is non-null for that row.
The view exposes no other columns. Because RELATED_ID is a union of five distinct base columns, it is semantically heterogeneous — the meaning of any given value depends on the slot it originated from and the business context of the source record.
Common Use Cases and Queries
Typical uses include reporting on all related identifiers associated with a source, driving territory assignment and qualification processing, and resolving inbound references where a source_id must be traced to every related entity it points to. A basic lookup by source identifier:
- SELECT source_id, related_id FROM jtf_tr_source_denorm_v WHERE source_id = :p_source_id;
- SELECT related_id FROM jtf_tr_source_denorm_v WHERE source_id = :p_source_id ORDER BY related_id;
- SELECT source_id, COUNT(*) FROM jtf_tr_source_denorm_v GROUP BY source_id HAVING COUNT(*) > 1;
To enrich results, join RELATED_ID to the target entity table appropriate to the territory context (for example resource, customer, or organization tables), applying the join only after filtering on SOURCE_ID for performance. Because the view is read-only and defined on CRM Foundation data, it should be used strictly for query and reporting purposes, never for direct DML. All access is subject to standard EBS security and the APPS schema grants governing JTF objects.
-
View: JTF_TR_SOURCE_DENORM_V 12.1.1
Territories Denormalized Source View
APPS.JTF_TR_SOURCE_DENORM_V·↳ JTF_SOURCES_ALL·Explore JTF module →
-
View: JTF_TR_SOURCE_DENORM_V 12.2.2
Territories Denormalized Source View
APPS.JTF_TR_SOURCE_DENORM_V·↳ JTF_SOURCES_ALL·Explore JTF module →
-
12.2.2 DBA Data 12.2.2
-
12.1.1 FND Design Data 12.1.1
-
12.1.1 DBA Data 12.1.1
-
12.2.2 FND Design Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
eTRM - JTF Tables and Views 12.2.2
Interface table to store data that needs to be displayed in Excel
-
eTRM - JTF Tables and Views 12.1.1
Interface table to store data that needs to be displayed in Excel
-
eTRM - JTF Tables and Views 12.2.2
Interface table to store data that needs to be displayed in Excel
-
eTRM - JTF Tables and Views 12.1.1
Interface table to store data that needs to be displayed in Excel