Search Results fa_asset_hist_web_v




Overview
Explore in AI Chat

Overview

The FA_ASSET_HIST_WEB_V view is a reporting object owned by the APPS schema within the Oracle E-Business Suite Fixed Assets (OFA) module. It exposes asset history information in a flattened, web-friendly format suitable for inquiry screens, reporting, and integration. The view is registered as VALID in the ETRM for both 12.1.1 and 12.2.2. Its principal role is to correlate asset additions with their transaction history, resolving foreign-key identifiers into descriptive, user-facing values such as the transaction type meaning, asset type meaning, and category concatenated segments. This makes it a convenient single source for asset transaction history without requiring joins to multiple lookup, category, and transaction header tables at query time.

Underlying Base Objects

The ETRM documents the following referenced base objects for the view: FA_ADDITIONS (synonym), FA_ASSET_HISTORY (synonym), FA_CATEGORIES_B_KFV (view), FA_LOOKUPS (synonym), and FA_TRANSACTION_HEADERS (synonym).

The view is defined as a UNION of two nearly identical SELECT statements over these objects. In each branch, FA_ASSET_HISTORY (aliased AH) is joined to FA_ADDITIONS (AD) on ASSET_ID, to FA_TRANSACTION_HEADERS (TH) on TRANSACTION_HEADER_ID_IN, and to FA_CATEGORIES_B_KFV (CK) on ASSET_CATEGORY_ID. Two aliases of FA_LOOKUPS (LO and LU) resolve the transaction type code (LOOKUP_TYPE = 'FAXOLTRX') and asset type code (LOOKUP_TYPE = 'ASSET TYPE'), respectively.

The UNION distinguishes rows where the ASSET_ID is the transaction header in versus out. The first branch sets KEY to TRANSACTION_HEADER_ID_IN and returns all rows; the second sets KEY to TRANSACTION_HEADER_ID_OUT and filters on AH.TRANSACTION_HEADER_ID_OUT IS NOT NULL. This produces a symmetric, keyed view of asset movement history in both directions.

Key Columns

  • TRANSACTION_HEADER_ID_IN — Header identifier for the transaction into which the asset entered.
  • TRANSACTION_HEADER_ID_OUT — Header identifier for the transaction from which the asset exited.
  • KEY — Synthetic key derived as either IN or OUT header ID, used as a unique row discriminator.
  • ASSET_ID — Surrogate identifier for the asset, joining to FA_ADDITIONS.
  • ASSET_NUMBER — Concatenation of asset number and description (ASSET_NUMBER || ' - ' || DESCRIPTION).
  • TRANSACTION_TYPE — Meaning of the transaction type code derived via FA_LOOKUPS.
  • UNITS — Number of units associated with the history record.
  • ASSET_TYPE — Meaning of the asset type code derived via FA_LOOKUPS.
  • CATEGORY — Concatenated category segments from FA_CATEGORIES_B_KFV.
  • CATEGORY_ID — Foreign key to the asset category.
  • COMMENTS — Transaction name from FA_TRANSACTION_HEADERS, used as descriptive text.

Common Use Cases and Queries

Typical usage includes asset history inquiry, reconciliation of asset movements, and integration feeds into custom reports.

Retrieve history for a specific asset:

SELECT asset_number, transaction_type, asset_type,
       category, units, comments
FROM   apps.fa_asset_hist_web_v
WHERE  asset_id = :p_asset_id
ORDER BY transaction_header_id_in;

List all asset movements by transaction type:

SELECT transaction_type, COUNT(*) movements
FROM   apps.fa_asset_hist_web_v
GROUP BY transaction_type
ORDER BY movements DESC;

Identify assets received in a specific transaction header:

SELECT asset_number, units, comments
FROM   apps.fa_asset_hist_web_v
WHERE  transaction_header_id_in = :p_header_id;

Because the view pre-joins lookups and category segments, it eliminates repetitive resolution logic and is well suited for embedded web inquiries and downstream ETL. Users should be aware of the UNION semantics to avoid double counting when both IN and OUT keys exist for the same asset movement.