Search Results isc_depot_backlog_etl_pkg




Overview
Explore in AI Chat

Overview

ISC_DEPOT_BACKLOG_ETL_PKG is an Oracle E-Business Suite package body owned by the APPS schema and classified as OTHER within the ETRM repository. Its principal business function is to extract, transform, and load depot repair backlog data into the Supply Chain Intelligence (ISC) reporting layer, specifically into the fact table ISC_DR_REPAIR_ORDERS_F. The package populates this fact from the operational depot repair tables—CSD_REPAIRS and CS_INCIDENTS_ALL_B—so that depot repair backlog and cycle-time analytics can be reported through Oracle Supply Chain Intelligence dashboards. It is not an end-user API; it is an internal ETL engine that supports both a full initial load and subsequent incremental loads, driven by a global start date maintained in setup.

Key Procedures and Functions

  • INITIAL_LOAD — Executes the full (initial) load of depot repair order data into the ISC fact table. It performs the bulk transformation and population required when the reporting schema is first seeded or rebuilt.
  • INCR_LOAD — Executes the incremental load, capturing only changed or newly created depot repair records since the last successful run. This keeps the ISC fact aligned with day-to-day repair activity.
  • GET_LAST_RUN_DATE — Returns the date of the last processing run. It provides the high-water mark used to bound the incremental load window, ensuring each incremental pass processes only new activity.
  • ERR_MESG — A common error-reporting utility that formats an incoming message together with the procedure name and statement identifier into a truncated string (bounded by the c_errbuf_size constant of 300 characters) suitable for the concurrent manager error buffer. It is used by all common procedures to produce consistent, diagnosable error output.
  • CHECK_INITIAL_LOAD_SETUP — Validates that the required global setup exists before any load is attempted. It retrieves the global start date (GSD), resolves the ISC schema name, and returns a status and message indicating whether setup is complete. The package header's global variables (g_global_start_date, g_isc_schema) are populated through this routine; if setup is missing or invalid, the load is rejected with an error message.

The package also declares global constants for concurrent manager return codes: c_error (-1), c_warning (1), and c_ok (0), which standardize how the calling concurrent program interprets completion status.

Tables Accessed

  • CSD_REPAIRS — The principal depot repair transaction table; the source of repair order activity loaded into the ISC fact.
  • CS_INCIDENTS_ALL_B — The service incident base table, joined to repairs to supply incident context for the backlog fact.
  • ISC_DR_REPAIR_ORDERS_F — The Supply Chain Intelligence fact table that this package populates; the object explicitly named in the package global g_object_name.
  • ISC_DR_INC — The ISC incremental staging/high-water-mark table used to track which records have already been extracted, supporting GET_LAST_RUN_DATE and the incremental load window.
  • MTL_PARAMETERS — Inventory organization parameters, referenced to resolve the master organization and organization context required to scope the extract correctly.

Usage Notes

ISC_DEPOT_BACKLOG_ETL_PKG is invoked from concurrent programs that drive the Supply Chain Intelligence depot repair backlog collection. The initial load is submitted as a one-time or rebuild activity, while the incremental load is scheduled periodically (typically nightly) to refresh the ISC fact. Because the only documented interface is through the load programs and the package is referenced by two other packages, it is not intended for direct ad hoc invocation; custom code should call the concurrent program rather than the internal procedures, since parameter lists are not exposed. The user search term "get_master_organization_id" reflects the common EBS pattern of resolving the master organization ID from MTL_PARAMETERS; in this package, organization context is derived through the setup and load logic rather than a separately documented public function, so developers seeking a master organization lookup should confirm the exact routine at runtime and rely on MTL_PARAMETERS as the underlying source.