Results for “item_organization_id”

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

Overview

APPS.AHL_SEARCH_TEMPLATES_V is a reporting and integration view within the Oracle E-Business Suite Complex Maintenance, Repair and Overhaul (AHL) module. Its documented purpose is to store the query used by the "search template" screen to retrieve template information. In practice, the view is the data-access layer behind the template search functionality: it presents a filtered, already-joined result set of visit records that are flagged as templates, augmented with the concatenated inventory item description and the decoded visit type meaning. Because it is owned by APPS and exposed as a view, it can be queried directly from PL/SQL, concurrent programs, OAF/Forms-based search pages, or external integration layers without rebuilding the joins each time.

A key behavior is the implicit filter TEMPLATE_FLAG = 'Y', which restricts the output to template visits only. Non-template visits in the base visit table are never returned. This makes the view unsuitable as a general-purpose visit listing; it is specifically a template catalog.

Underlying Base Objects

The view is defined over three referenced base objects, all of which are themselves views:

• AHL_VISITS_VL — the primary source, supplying visit identity, numbering, name, object version, template flag, item and organization identifiers, visit type code, and the last update date. The _VL suffix indicates a translated (language) view, so visit name and related descriptive fields are returned in the session's language.
• MTL_SYSTEM_ITEMS_B_KFV — the key flexfield (KFV) view over system items, joined on inventory item and organization to provide the concatenated item segments.
• FND_LOOKUP_VALUES_VL — the translated lookup values view, joined with an outer ((+)) join on lookup type AHL_PLANNING_VISIT_TYPE and the matching visit type code, to resolve the code to its displayed meaning.

The join to system items is standard equality on both INVENTORY_ITEM_ID and ORGANIZATION_ID, so a template whose item/organization combination does not resolve will be excluded. The lookup join is outer-joined, so a template with a visit type code that has no active lookup entry still appears, with a null visit type meaning.

Key Columns

• VISIT_ID — primary visit identifier; the join key back to the visit entity.
• VISIT_NUMBER / VISIT_NAME — human-readable template identification from the translated visit view.
• OBJECT_VERSION_NUMBER — optimistic locking version, useful for integration calls that update the underlying visit.
• TEMPLATE_FLAG — always 'Y' in this view given the defining filter; retained for schema compatibility.
• INVENTORY_ITEM_ID / ITEM_ORGANIZATION_ID — the item and organization context of the template.
• ITEM_DESCRIPTION — the concatenated item flexfield segments from MTL_SYSTEM_ITEMS_B_KFV; this is the concatenated segment string, not the long description.
• VISIT_TYPE_CODE — the raw code stored on the visit (e.g., the coded value on AHL_VISITS_VL).
• VISIT_TYPE_MEAN — the decoded meaning of the visit type code via lookup type AHL_PLANNING_VISIT_TYPE. This is the column most commonly requested when users search for "visit_type_mean", since it translates the stored code into a readable label.
• LAST_UPDATE_DATE — audit timestamp of the visit record; useful for incremental extraction.

Common Use Cases and Queries

The most frequent scenario is populating a template search page or LOV, or feeding a downstream report that needs readable visit type labels. A typical query resolving the visit type meaning for a given item context:

• SELECT visit_id, visit_number, visit_name, visit_type_code, visit_type_mean, item_description FROM apps.ahl_search_templates_v WHERE inventory_item_id = :p_item_id AND item_organization_id = :p_org_id ORDER BY visit_number;

To list all templates grouped by visit type for operational review:

• SELECT visit_type_mean, COUNT(*) FROM apps.ahl_search_templates_v GROUP BY visit_type_mean ORDER BY 1;

For integration delta extraction keyed on the audit column:

• SELECT visit_id, visit_number, visit_type_code, visit_type_mean, last_update_date FROM apps.ahl_search_templates_v WHERE last_update_date >= :p_since;

Because the view is read-only and pre-filtered, it should not be used for inserts or updates; DML must target AHL_VISITS_VL or its base tables with the appropriate AHL APIs. When a template is missing from the result set, the first checks are whether TEMPLATE_FLAG is 'Y', whether the item and organization resolve in MTL_SYSTEM_ITEMS_B_KFV, and whether a null VISIT_TYPE_MEAN is masking an inactive or unseeded AHL_PLANNING_VISIT_TYPE lookup.