Search Results jtf_changed_terr

Overview

JTF_CHANGED_TERR is a CRM Foundation (JTF) view that exposes the change-history staging data captured by the Oracle Territory Manager when territory definitions are created, updated, or deleted. Rather than representing the current state of a territory, the view presents paired "old" and "new" column sets that allow a consumer to compare a territory or territory-qualifier row before and after a modification, together with the trigger mode, the action performed, and the concurrent request that drove the change.

The object is defined over a single base table, JTF_CHANGED_TERR_ALL, and its name and structure indicate that it is a descriptive (non-multi-org) view over an _ALL table. In the ETRM metadata supplied for this object, the view is documented as "Not implemented in this database," and no owner is recorded; consequently, availability of the view depends on whether the Territory Manager change-tracking feature has been deployed in the target environment. Where it exists, it is used during territory compilation and change propagation, and it serves reporting and integration requirements that need to identify what specifically changed on a territory between two points in time.

Underlying Base Objects

The view is defined over JTF_CHANGED_TERR_ALL. The documented view text performs a straightforward projection of every column of that table, with no joins, unions, or computed expressions:

SELECT ... FROM JTF_CHANGED_TERR_ALL

Because the definition is a flat projection, the view inherits all rows and all columns of the base table, and no additional filtering, row-level security, or org-context predicate is applied inside the view. Any restriction by operating unit (ORG_ID) or by concurrent request (REQUEST_ID) must therefore be applied by the calling query. The metadata records no documented referenced base objects beyond JTF_CHANGED_TERR_ALL, and no database owner is listed, which is consistent with the "not implemented in this database" annotation.

Key Columns

• TERR_ID, TERR_VALUE_ID — Identify the territory and, where applicable, the territory value row affected by the change.
• TRIGGER_MODE, ACTION — Indicate how the change was initiated and what operation (insert, update, delete) was applied.
• OLD_* / NEW_* pairs — The core of the view. Territory attributes such as START_DATE_ACTIVE, END_DATE_ACTIVE, RANK, UPDATE_FLAG, and PARENT_TERRITORY_ID are exposed twice, once for the pre-change value and once for the post-change value. A query seeking a changed expiry date, for example, compares OLD_END_DATE_ACTIVE with NEW_END_DATE_ACTIVE.
• Qualifier columns — OLD_/NEW_ TERR_QUAL_ID, INCLUDE_FLAG, COMPARISON_OPERATOR, ID_USED_FLAG, LOW/HIGH_VALUE_CHAR, LOW/HIGH_VALUE_NUMBER, VALUE_SET, INTEREST_TYPE_ID, PRIMARY/SECONDARY_INTEREST_CODE_ID, and CURRENCY_CODE describe the territory qualifier before and after the change.
• Resource columns — OLD_/NEW_ RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, FULL_ACCESS_FLAG, and GROUP_ID capture changes to territory resource assignments, keyed by TERR_RSC_ID and TERR_RSC_ACCESS_ID.
• Audit columns — OLD_/NEW_ LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, and LAST_UPDATE_LOGIN provide who/when information for each side of the change.
• Control columns — TRANSFER_ONLY_FLAG, REQUEST_ID, ORG_ID, and TERR_QTYPE_USG_ID link the change record to the concurrent program run and operating unit that produced it.

Common Use Cases and Queries

Typical uses include auditing territory date changes, isolating changes generated by a specific concurrent request, and feeding downstream systems with only the attributes that actually changed.

To find territories whose active end date was modified in a given request:

SELECT terr_id, terr_value_id, old_end_date_active, new_end_date_active, request_id
FROM jtf_changed_terr
WHERE request_id = :p_request_id
  AND NVL(old_end_date_active, DATE '1900-01-01')
       <> NVL(new_end_date_active, DATE '1900-01-01');

To review all automated changes within an operating unit over a period:

SELECT terr_id, action, trigger_mode, old_rank, new_rank,
       new_last_update_date
FROM jtf_changed_terr
WHERE org_id = :p_org_id
  AND new_last_update_date >= :p_from_date;

Because the view is a complete projection of JTF_CHANGED_TERR_ALL, reports should always constrain on REQUEST_ID, ORG_ID, or date columns to avoid scanning the full change history. Where the view is not deployed, equivalent information must be obtained from the base table or from Territory Manager concurrent program logs.