Results for “route_end_date”

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

Overview

ENG_CHANGE_TYPE_ROUTES_V is a reporting view owned by the APPS schema in Oracle E-Business Suite Engineering (ENG). It exposes the relationship between engineering change types and the routing definitions assigned to them, denormalizing the association into a single queryable structure that joins route assignment records with route header details. Because the view surfaces descriptive attributes such as ROUTE_NAME alongside foreign keys like CHANGE_TYPE_ID and ROUTE_ID, it is well suited to report authoring, concurrent program logic, Oracle Forms LOV population, and integration interfaces that must resolve a change type to its applicable routes without navigating the underlying normalized model. In release 12.1.1 and 12.2.2 the object retains the same definition and is documented as VALID in ETRM metadata. Users searching on route_name typically reach this view to map a human-readable route name back to the change type configuration that references it.

Underlying Base Objects

The view text is defined over two objects in the APPS schema:

• ENG_CHANGE_TYPE_ROUTES (referenced through its APPS synonym, aliased ECTR) — the transactional association table holding change type to route assignments, default flags, audit columns, and the lifecycle status reference.
• ENG_CHANGE_ROUTES_VL (aliased ECRVL) — the multi-language route validation view supplying the translated descriptive attributes ROUTE_NAME and ROUTE_DESCRIPTION as well as the route effective dating columns ROUTE_START_DATE and ROUTE_END_DATE.

The join is an equi-join on ROUTE_ID (ECRVL.ROUTE_ID = ECTR.ROUTE_ID). There is no outer join, so only change type route assignments with a matching route definition are returned; orphaned assignments are excluded. ECTR.ROWID is projected as ROW_ID to provide row identity for Forms-based updates.

Key Columns

• CHANGE_TYPE_ROUTE_ID — primary key of the underlying association record.
• CHANGE_TYPE_ID — foreign key to the engineering change type.
• ROUTE_ID — foreign key to the route definition.
• ROUTE_NAME — the translated route name from ENG_CHANGE_ROUTES_VL; the primary search attribute.
• ROUTE_DESCRIPTION — translated route description.
• DEFAULT_ROUTE_FLAG — indicates whether this route is the default for the change type.
• ROUTE_START_DATE / ROUTE_END_DATE — effective dating of the route header; the ETRM column listing shows ROUTE_START_DATE rendered as ROTUE_START_DATE, a documented typographical artifact.
• CHANGE_LIFECYCLE_STATUS_ID — lifecycle status reference governing the assignment.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns inherited from ENG_CHANGE_TYPE_ROUTES.

Common Use Cases and Queries

Typical usages include resolving route names for a given change type, identifying default routes, and reconciling assignments against route effective dates.

• List all routes for a change type:

SELECT change_type_route_id, route_id, route_name,
       default_route_flag, route_start_date, route_end_date
  FROM apps.eng_change_type_routes_v
 WHERE change_type_id = :p_change_type_id
 ORDER BY default_route_flag DESC, route_name;

• Search by route name, the reported user scenario:

SELECT change_type_id, route_id, route_name, default_route_flag
  FROM apps.eng_change_type_routes_v
 WHERE route_name LIKE :p_route_name||'%';

• Report default route assignments only:

SELECT change_type_id, route_name, change_lifecycle_status_id
  FROM apps.eng_change_type_routes_v
 WHERE default_route_flag = 'Y';

Because the view performs no filtering beyond the inner join, date-range or status filtering should be applied explicitly in the calling query using ROUTE_START_DATE, ROUTE_END_DATE, and CHANGE_LIFECYCLE_STATUS_ID.