Search Results fii_project_hook

Overview

FII_PROJECT_HOOK is an Oracle Applications (APPS) PL/SQL package that implements a collection hook for the Enterprise Data Warehouse (EDW) Project Dimension within Oracle E-Business Suite. The package belongs to the FII product schema used by EDW collection engine components, and its header carries the source control identifier FIIPA17S.pls 120.0, registered as a non-shipped (noship) development artifact. Its core purpose is to allow the EDW collection engine to hand control to product-specific code at defined stages of the collection process, and to return control to the engine on completion. Oracle explicitly characterizes this mechanism as analogous to message (event) handling, where the hook fires within the collection lifecycle rather than from a user-facing transaction.

In functional terms, FII_PROJECT_HOOK exists to keep the Project Dimension of the warehouse synchronized with a specific change in Oracle Projects: a change to the Task Owning Organization. Because the Task Owning Organization is denormalized into fact-level tables, detecting and propagating this change is essential for the accuracy of PA (Projects) fact data — cost, revenue, and budget — that feeds the Project Dimension.

Key Procedures and Functions

The ETRM metadata documents a single callable unit within the package specification:

• PRE_DIMENSION_COLL — the Project dimension pre-collection hook. It is declared as a function returning BOOLEAN, consistent with the collection engine convention that a hook signals success or failure of its work. PRE_DIMENSION_COLL detects whether the Task Owning Organization of a task has been changed in Oracle Projects. When such a change is found, the function writes notification records — one for each PA fact area (cost, revenue, and budget), so three records in total — into the FII_SYSTEM_EVENT_LOG table. These records inform the downstream PA fact collection processes that the task has changed its Task Owning Organization and that the Task Owning Organization foreign key in the PA fact tables must be updated accordingly. The denormalization path the hook protects runs from the Project Task level into the PROJECT_ORG_FK column of the PA fact tables, with the corresponding warehouse column DENORM_TASK_ORG_FK held in EDW_PROJ_TASK_LTC. The procedure list supplied in the metadata contains only this function; no other procedures or functions are enumerated for the package.

Tables Accessed

The documented table references, resolved through APPS synonyms, are:

• FII_SYSTEM_EVENT_LOG — the event log written by PRE_DIMENSION_COLL. The hook inserts three records here, one per PA fact (cost, revenue, budget), to notify the PA fact collection processes of the Task Owning Organization change.
• FII_SYSTEM_EVENT_LOG_S — the sequence object associated with the event log, used to generate unique identifiers for the inserted notification rows.
• EDW_PROJ_TASK_LTC — the Project Task dimension table in the warehouse that holds DENORM_TASK_ORG_FK, the denormalized Task Owning Organization foreign key whose currency the hook is protecting.
• EDW_PROJ_TASK_LSTG — the staging table associated with the Project Task dimension, used during the staging and load steps of dimension collection.

These objects frame the hook's operation: it detects a source-side change in Oracle Projects, logs an event to drive fact reprocessing, and supports the denormalized organization key carried in the Project Task dimension structure.

Usage Notes

FII_PROJECT_HOOK is not intended to be invoked directly by end users, forms, or reports. It is called by the EDW collection engine as part of dimension collection, specifically at the pre-collection stage for the Project Dimension, after which control is returned to the engine. The signature of PRE_DIMENSION_COLL — returning BOOLEAN — reflects this internal, engine-driven calling convention rather than a conventional concurrent program interface. The package is referenced by one other package in the ETRM inventory, indicating it participates as a dependent component in the broader collection framework. Customizations should avoid modifying the package in place, since it is a shipped, supportable collection hook; extensions are better directed at the product-specific change detection and event logging patterns the hook demonstrates. Because the hook depends on change detection in Oracle Projects and on the denormalized organization key in the warehouse dimension, any change to the underlying Projects date-tracked columns or to the EDW_PROJ_TASK_LTC structure must be evaluated against its behavior.