Search Results edw_time_pkg




Overview
Explore in AI Chat

Overview

EDW_TIME_PKG is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite, classified under the "OTHER" API category within the ETRM repository. The package forms part of the Enterprise Data Warehouse (EDW) calendar and time dimension infrastructure, providing the utility routines that translate operational Oracle EBS date values into surrogate key references used by the EDW star schema and its supporting calendar dimensions. Its role is centered on calendar resolution: given a transactional or accounting date, the package returns the corresponding warehouse key for a calendar day, a calendar period, or a derived period reference that supports fiscal and reporting-time aggregations.

The package is a leaf dependency rather than a called API — ETRM records that EDW_TIME_PKG is not referenced by any database object, which confirms that it is invoked downward through its own internal callers and external jobs rather than being re-exposed as a dependency of other packages within the database. It is, however, a heavy consumer of shared EBS reference data, relying on APPS synonyms for its table access and on standard database utilities (DBMS_STANDARD, STANDARD) for its procedural scaffolding.

Key Procedures and Functions

Four documented program units exist in EDW_TIME_PKG, each oriented toward a specific calendar-key resolution task:

  • CAL_DAY_FK — Returns the foreign key for a calendar day. This is the most granular resolution routine, mapping an input date to the surrogate day key of the EDW calendar dimension.
  • CAL_PERIOD_FK — Returns the foreign key for a calendar period. This routine resolves a date to the broader fiscal or accounting period key used for periodic reporting and aggregation.
  • CAL_DAY_TO_CAL_PERIOD_FK — Performs a direct conversion from a calendar day reference to its corresponding calendar period key, allowing callers that already hold a day key to obtain the parent period without re-parsing the original date.
  • PA_CAL_DAY_FK — A Projects-oriented variant that returns the calendar day foreign key using the Projects implementation calendar, honoring the Project Accounting calendar context where applicable.

Each routine is designed to be a deterministic mapping utility; the naming convention (FK suffix) reflects the surrogate-key output rather than any modification of source data.

Tables Accessed

The package reads from three documented tables via APPS synonyms:

  • EDW_LOCAL_INSTANCE — Identifies the local warehouse instance context, establishing which calendar and dimension configuration applies to the current session.
  • GL_DATE_PERIOD_MAP — The General Ledger date-to-period mapping table, providing the authoritative relationship between accounting dates and their GL period names and numbers used by CAL_PERIOD_FK and CAL_DAY_TO_CAL_PERIOD_FK.
  • PA_IMPLEMENTATIONS_ALL — The Project Accounting implementations table, used by PA_CAL_DAY_FK to resolve the correct Projects calendar context for the operating unit.

Additional referenced objects include GL_SETS_OF_BOOKS, which supplies set-of-books-level calendar attributes, and the EDW_TIME_PKG specification itself, which declares the callable interface. No write operations are indicated; the package is a read-only resolver.

Usage Notes

EDW_TIME_PKG is typically invoked from EDW extraction, transformation, and load routines as well as from custom reporting logic that must align EBS transactional dates with warehouse calendar keys. It is called at high frequency during dimension staging and fact loading, and its determinism makes it suitable for use inside SQL-based transformations as well as PL/SQL. ETRM confirms that 24 other packages reference this package, indicating broad internal reuse across the EDW codebase. Because the package is VALID in both 12.1.1 and 12.2.2, it is considered stable across the 12.1.x and 12.2.x application releases. Implementers extending the EDW should treat EDW_TIME_PKG as the canonical entry point for date-to-key resolution rather than replicating calendar logic locally.