Search Results fii_system_event_log_s

Overview

The APPS.FII_PROJECT_HOOK package body is a component of the Oracle E-Business Suite (EBS) Enterprise Data Warehouse (EDW) / Business Intelligence (BI) integration layer, specifically belonging to the FII (Financial Intelligence) product family. Its business function is to support incremental (change-based) data collection for the Projects domain within the FII/EDW staging architecture. The package detects tasks whose organization-related denormalized attribute has changed since the last collection cycle within the EDW project task staging table, and then publishes "system event" records that signal downstream fact-collection processes to reprocess affected tasks.

In EBS 12.1.1 and 12.2.2, the FII collection framework relies on event log records to drive the incremental refresh of Project Cost, Project Revenue, and Project Budget facts. FII_PROJECT_HOOK acts as the hook that translates detected dimensional changes on the task level into the appropriate fact-level refresh events.

Key Procedures and Functions

• PRE_DIMENSION_COLL — The single documented function in this package. Its purpose is to run before dimension collection and identify the "most recently changed tasks" by comparing the current denormalized task organization value against the previously collected value. It returns a boolean success indicator, committing on success and rolling back on error. The procedure does not take external parameters as exposed in the excerpt.

The function name indicates that it executes as a pre-step (pre-dimension collection hook) prior to the main dimensional load routines, ensuring that dependent facts are queued for reprocessing whenever the underlying task organization denormalization changes.

Tables Accessed

• EDW_PROJ_TASK_LSTG — The Projects task "listing" staging table. The package reads it to find tasks whose most recent record has a collection status of READY and a record type of ORACLE, grouped by task_pk to isolate the latest creation date per task.
• EDW_PROJ_TASK_LTC — The Projects task "last collected" table. It is joined to the listing table on task_pk to compare the stored denorm_task_org_fk against the newly staged value; differing values constitute the change scope.
• FII_SYSTEM_EVENT_LOG — Written by the package. One log row is inserted for each changed task, per affected fact, with the event type indicating the target fact (DNRM:FII_PA_COST_F, DNRM:FII_PA_REVENUE_F, DNRM:FII_PA_BUDGET_F), the event object identifying the changed attribute, and the task primary key carried in ATTRIBUTE1, all set to READY.
• FII_SYSTEM_EVENT_LOG_S — The sequence supplying EVENT_ID values for the log inserts.

Usage Notes

FII_PROJECT_HOOK is an "OTHER"-classified API and is referenced by one other package in the FII collection chain, confirming it is invoked programmatically by the EDW/FII collection framework rather than directly by end users. It is typically called during the FII dimensional collection workflow — specifically as a pre-dimension-collection step — before Project Cost, Revenue, and Budget fact loads execute.

Because it relies on the presence of READY records in EDW_PROJ_TASK_LSTG, it only becomes active after upstream extraction and staging jobs have populated task data. The commit is issued inside the loop's enclosing scope after all event records are inserted, and any exception triggers a full rollback and a false return, signalling failure to the calling driver process. The task_pk column is central to the logic, serving as the grouping and comparison key that determines the change scope and is propagated into the event log as ATTRIBUTE1. This design ensures change detection remains task-granular, driving targeted rather than full fact reprocessing.