Search Results get_join_info

Overview

ISC_DEPOT_RPT_UTIL_PKG is a Depot Repair reporting utility package in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 environment, owned by the APPS schema and classified as an OTHER API in the ETRM repository. Its documented purpose is to serve as a shared reporting infrastructure for the Depot Repair module, providing a uniform mechanism through which Depot Repair reports select their source data. The package centers on materialized view (MV) sets, each of which is mapped to one or more Depot Repair reports, including Repair Order Backlog, Repair Order Completion, Repair Order Cost Summary, Repair Order Charges Summary, Repair Order Margin, Mean Time To Repair (MTTR), and Repair Order Service Code Summary, along with their trend and detail variants.

The package body contains internal helper routines such as init_dim_map, get_join_info, get_mv, and bind_low_high, which together allow the reporting layer to resolve a user-selected "view by" dimension into the correct materialized view, join conditions, and filter predicates. The header comment ($Header: iscdepotutilb.pls 120.1 2006/01/26) indicates the utility has been stable since the 11i timeframe and continues to be used in the 12.x report set.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions exposed by this package body. The following are described at the level of purpose only; parameter lists are not enumerated here.

• PROCESS_PARAMETERS — Central entry point for interpreting user-supplied report parameters (such as view-by dimension, product category, and repair type flags) and preparing the internal state needed by the report query builder.
• GET_BUCKET_WHERE_CLAUSE — Returns the WHERE clause fragment used to restrict bucket-based reporting (for example, aging or backlog buckets) to the buckets selected by the user.
• GET_AGG_FLAG — Determines whether the report should aggregate at a summary level or return detail rows, based on the selected view-by and category context.
• GET_VIEWBY_SELECT_CLAUSE — Produces the SELECT clause fragment corresponding to the chosen "view by" dimension, so the report displays the correct column set.
• WRITE — Emits the assembled output for the report, coordinating the fragments produced by the other routines.
• GET_REPAIR_ORDER_URL — Builds the navigation URL used to drill from a report row into the associated Repair Order.
• GET_SERVICE_REQUEST_URL — Builds the navigation URL used to drill from a report row into the associated Service Request.

Tables Accessed

Per the documented metadata, the package references the following objects via APPS synonyms:

• BIS_BUCKET — The Business Intelligence System bucket definition table. It supplies the bucket ranges (low/high limits) that drive bucket-based WHERE clauses used in backlog, past-due, late completion, and aging reports.
• PLITBLM — Used for localized or lookup value resolution in the reporting context.

Additional MV objects referenced by name in the package header (for example, BKLG, BKLDTL1, CMPDTL1, COSTS, CHARGES, MARGIN, MTTR, SRVC) are the reporting materialized views resolved dynamically by the package, not directly documented tables.

Usage Notes

ISC_DEPOT_RPT_UTIL_PKG is invoked by Depot Repair reports and by the surrounding BI reporting framework rather than by end users directly. It is referenced by four other packages in the ETRM repository, indicating it is a shared utility dependency. Typical invocation paths include:

• Concurrent programs that submit Depot Repair reports, which pass category and repair-type flags into PROCESS_PARAMETERS to obtain the correct MV and join information.
• Oracle Forms or HTML-based report launchers that rely on the drill-down URL functions to navigate to Repair Orders and Service Requests.
• Custom reporting code that reuses the same parameter processing and clause-generation logic instead of embedding hard-coded SQL fragments.

Because the package resolves MV names and join clauses dynamically based on user input, callers must supply consistent combinations of view-by, category flag, and repair-type flag to avoid generating a SQL statement that references an undefined MV set.