Search Results get_dtl_rpt_sql




Overview
Explore in AI Chat

Overview

The package APPS.ISC_FS_MTTR_RPT_PKG is a reporting package within the Oracle E-Business Suite (EBS) environment, owned by the APPS schema and classified as an OTHER API type in the ETRM metadata for releases 12.1.1 and 12.2.2. The acronym "MTTR" in the package name refers to Mean Time To Repair, and "FS" indicates Field Service context. This package therefore supports reporting on field service repair turnaround metrics, particularly in an Oracle TeleService / Field Service operational environment.

The package is a query-generation utility rather than a data-manipulation API. It exposes procedures that return dynamically constructed SQL statements and query attribute definitions, which are then consumed by the Oracle Business Intelligence (BI) Publisher / BIS (Business Intelligence System) framework used across EBS for interactive reporting pages. This design pattern is typical of EBS reporting packages that extend the "PMV" (Page Management View) infrastructure, evidenced by the bis_pmv_page_parameter_tbl parameter type and the bis_query_attributes_tbl output type.

Key Procedures and Functions

The package declares three public procedures. Each takes a PMV page parameter table as input and returns an SQL string and query attributes as outputs, following the standard EBS BIS query-generation convention.

  • GET_TBL_SQL — Generates the SQL used to populate the tabular (summary/table) view of the Mean Time To Repair report. It supplies the BIS framework with a dynamic query string and attribute definitions for the overview table.
  • GET_TRD_SQL — Produces the SQL supporting the trend view of the report, allowing users to analyze MTTR metrics over time. As with the other procedures, it returns a custom SQL statement and associated query attributes.
  • GET_DTL_RPT_SQL — This is the procedure explicitly referenced in the user's search term. It returns the SQL for the detailed report view, providing row-level repair records that underlie the aggregated MTTR figures. This detail query is the source from which drill-down reporting in the MTTR report page is rendered.

The source header comment ($Header: iscfsmttrrpts.pls 120.0 2005/08/28) indicates this is an early-generation file that has undergone minimal revision since its original creation.

Tables Accessed

The ETRM metadata identifies a single referenced table via APPS synonym: PLITBLM. This is the standard EBS "PL/SQL Interface Table" used by the BIS/PMV reporting framework to exchange query definitions, parameter values, and output column metadata between the reporting package and the Business Intelligence layer. The package itself does not appear to directly read transactional Field Service tables; instead, the SQL strings it returns are executed downstream by the BIS engine to retrieve the actual MTTR data from source tables.

Usage Notes

This package is not intended to be called from Oracle Forms or concurrent programs directly. It is invoked by the Oracle Business Intelligence / BIS reporting infrastructure when a user opens the Mean Time To Repair report page. At runtime, the framework passes page parameters through bis_pmv_page_parameter_tbl and calls the appropriate procedure—GET_TBL_SQL, GET_TRD_SQL, or GET_DTL_RPT_SQL—depending on which section of the report is being rendered. Custom code should not call these procedures directly, as they are tightly coupled to the PMV page context. Developers extending or debugging the MTTR report should trace get_dtl_rpt_sql to understand the detail query, and note that the package is referenced by no other packages per ETRM, indicating a leaf-level reporting component with no downstream dependents.