Search Results fa_asset_trace_pvt




Overview
Explore in AI Chat

Overview

FA_ASSET_TRACE_PUB is a public PL/SQL package in the Oracle E-Business Suite Fixed Assets (FA) module, owned by APPS and classified as a PUB (public) API. Its business purpose is to provide a generic, metadata-driven tracing and diagnostic engine for Fixed Assets data. Rather than hard-coding queries against specific FA tables, the package accepts a table of "trace options" that describe each source table, its schema, leading columns, ordering, column formatting rules, and additional WHERE or FROM clauses. From these descriptors the package dynamically generates and executes select statements, formats the results as HTML output, and writes the trace to a log or file. The package is therefore an internal diagnostic and reporting utility used to investigate asset-related data relationships, transaction flows, and reconciliation issues. The header comment identifier ($Header: faxtrcps.pls 120.3) confirms it is a shipped, versioned source file in the FA product family. Because it is an AUTHID CURRENT_USER package, it executes with the privileges of the calling user, which is important when it is invoked from an APPS session against tables owned by other schemas.

Key Procedures and Functions

  • RUN_TRACE — The principal driver procedure. It accepts a table of trace options, a table of column exclusions, a dynamic heading, a statement identifier, an optional system-option table, a flag controlling UTL_FILE output, a debug flag, and the calling program name, returning a completion code and an optional log-level record. It orchestrates the trace: building the generated SQL from the option descriptors, executing it, applying exclusions and formatting, and emitting the HTML result.
  • WAIT_FOR_REQ — This is the procedure matching the user's search term. Its name and context indicate that it suspends processing until a submitted concurrent request completes. It provides a synchronous barrier so that the trace does not proceed against data that is still being populated by a prior request.
  • SET_TEMP_HEAD — Sets or replaces the temporary heading text applied to the trace output, using the supplied log-level record for message control.
  • DO_TOP_SECTION — Renders the initial or top-level section of the trace report, establishing the structural header before detailed table output is generated.
  • START_QUEUE — Initializes the advanced-queue infrastructure used to publish trace messages or events for asynchronous consumption.
  • ADD_SUBSCRIBER — Registers a subscriber with that queue so that generated trace information is delivered to the intended recipient.
  • LOG — Records diagnostic messages at the requested log level, governed by the FA_API_TYPES.log_level_rec_type record.

The package also declares public types: t_col_exclusions_rec and t_excl_tbl for exclusions; t_tbl_rec and t_options_tbl for table descriptors; and t_vchar_tbl and t_num_tbl for generic string and numeric collections. Global elements include g_req_tbl, g_submit_subreq, and g_submit_counter, which together track submitted requests and support the WAIT_FOR_REQ coordination logic.

Tables Accessed

The ETRM metadata identifies three referenced objects, all accessed through APPS synonyms: DBMS_AQADM, DBMS_LOCK, and PLITBLM. DBMS_AQADM is the Advanced Queuing administration package used by START_QUEUE and ADD_SUBSCRIBER to create and configure queues and subscribers. DBMS_LOCK provides the locking and sleep primitives that underpin WAIT_FOR_REQ, allowing the process to pause and re-check request status without busy-waiting. PLITBLM is a standard Oracle-supplied inter-session or table-lock management utility leveraged by the package's coordination logic. The Fixed Assets tables actually traced by RUN_TRACE are not statically bound; they are supplied at runtime through the t_options_tbl descriptor collection, which is why the documented table list contains only infrastructure objects.

Usage Notes

FA_ASSET_TRACE_PUB is not a business-transaction API and should not be used to create or modify assets. It is a diagnostic utility, typically invoked from FA diagnostic forms, from internal FA programs, or from custom troubleshooting scripts that need to dump the contents and relationships of asset tables in a readable HTML format. The ETRM metadata records that it is referenced by two other packages, indicating it is consumed internally by the Fixed Assets product rather than called directly by end users. Developers invoking it should supply a correctly populated t_options_tbl and t_excl_tbl, set the p_use_utl_file flag when server-side file output is required, and pass an appropriate FA_API_TYPES.log_level_rec_type for consistent logging. WAIT_FOR_REQ is relevant when the trace depends on output from a previously submitted concurrent request; it is called before RUN_TRACE, or between trace phases, to enforce that dependency. Because the package is AUTHID CURRENT_USER and dynamically generates SQL from caller-supplied metadata, callers must ensure that supplied table names, schemas, and clauses are trusted, and that the invoking user holds the required privileges on both the traced tables and the APPS synonyms for DBMS_AQADM, DBMS_LOCK, and PLITBLM.