Search Results pa_cal_day_fk

Overview

EDW_TIME_PKG is a PL/SQL package owned by the APPS schema that supports Oracle E-Business Suite data extraction, transformation, and loading into the Enterprise Data Warehouse (EDW). Its primary business function is to generate the foreign key (surrogate key) strings required to join operational EBS transactions to the Time dimension of the warehouse. The Time dimension organizes calendar and fiscal data at multiple levels of granularity — day, period, and instance — so that transactional facts drawn from General Ledger, Projects, and other source modules can be consistently dated and aggregated in analytical reporting.

The package is classified as OTHER in the ETRM repository, meaning it is an internal utility rather than a public, supported integration API. Its header ($Header: FIICATMS.pls ... noship) indicates it originated in the Financial Intelligence (FII) product family and has been shipped as a nonshipped, internal component. Despite this status, the package is referenced by 24 other packages within the EBS data warehouse schema, making it a central dependency in the ETL layer. The user-supplied search term cal_day_fk corresponds directly to the most heavily used function in this package.

Key Procedures and Functions

The package exposes four documented program units:

• CAL_DAY_FK — Returns the foreign key to the fiscal calendar day level in the Time dimension for a given calendar date and set of books. It accepts a date and a set of books identifier, with an optional instance code. The returned key is a concatenated string in the form Period Set – 'FIN' – Set of book id – instance – 'CD', representing the day-level grain.
• CAL_PERIOD_FK — Returns the foreign key to the fiscal period level, pushed down to the fiscal calendar day level. It accepts a fiscal period name and a set of books identifier. The output follows the form Period Set – 'FIN' – Fiscal period – Instance – 'GL'. This variant is intended primarily for General Ledger data, which is stored at the fiscal period rather than the day level.
• CAL_DAY_TO_CAL_PERIOD_FK — Provides a mapping or conversion between a calendar day foreign key and the corresponding calendar period foreign key, allowing day-level source data to be aggregated or aligned to period-level warehouse records.
• PA_CAL_DAY_FK — A Projects (PA) specific variant of the calendar day foreign key function, producing the day-level Time dimension key for transaction data originating in Oracle Projects.

Notably, the functions do not validate that a supplied date falls within a valid fiscal period or that a period name is legitimate. Such records are expected to be rejected later during warehouse load processing.

Tables Accessed

The package reads from three documented tables via APPS synonyms:

• EDW_LOCAL_INSTANCE — Resolves the instance code component embedded in every returned foreign key string, ensuring keys are scoped to the correct warehouse instance.
• GL_DATE_PERIOD_MAP — Supplies the mapping between calendar dates and fiscal periods, enabling CAL_DAY_FK and the period conversion function to translate operational dates into fiscal calendar foreign keys.
• PA_IMPLEMENTATIONS_ALL — Used by PA_CAL_DAY_FK to determine the correct set of books and implementation context for Projects-sourced data.

Usage Notes

EDW_TIME_PKG is invoked programmatically during ETL execution rather than interactively. Its functions are called from within other warehouse-loading packages (the 24 documented dependents) as transactions are extracted from source modules and stamped with Time dimension keys. It is not exposed through standard EBS forms or concurrent program parameters. Custom code extending the EBS data warehouse should call CAL_DAY_FK or CAL_PERIOD_FK to obtain Time dimension keys, and must handle the exceptions raised when the set of books identifier is invalid or SQL errors occur. Callers should also be aware that no fiscal period validation is performed, so downstream rejection handling in the warehouse is essential.