Search Results date_tbl

Overview

FII_FA_CAT_C is an Oracle EBS APPS-owned PL/SQL package classified as OTHER within the ETRM repository. Its declared public interface is small: a single Boolean function, NEW_CAT_IN_FA, and a driver procedure, MAIN. The package is tied to the Fixed Assets category subsystem and, more specifically, to the Oracle Financials Intelligence (FII) dimensional model that captures Fixed Assets category metadata for analytic reporting. The package header carries the identifier FIIFACATS.pls, version 120.1, dated 2005/10/30, which places its origin in the 11i era; the code remains present and callable in EBS 12.1.1 and 12.2.2 alongside later FII packages. Its principal responsibility is to determine whether new asset categories exist in the Fixed Assets application and, where they do, to propagate the resulting category and segment information into the FII staging and dimension tables used by the Financials Intelligence data warehouse.

Key Procedures and Functions

• NEW_CAT_IN_FA — A Boolean-returning function whose stated purpose is to report whether new categories have been created in Fixed Assets. It functions as a detection gate for the driver, allowing MAIN to skip the full extraction workload when no change has occurred since the last synchronization. In the FII model, new categories must be reflected in the category dimension before fact data referencing them can be loaded correctly.
• MAIN — The package driver. Its signature includes the standard concurrent program out parameters errbuf and retcode, both NOCOPY VARCHAR2, plus a pmode VARCHAR2 parameter used to select the processing mode. MAIN orchestrates the end-to-end category synchronization: reading the current Fixed Assets category definitions, resolving the associated key flexfield structure and segment values, and writing or updating the corresponding FII dimension records. The errbuf and retcode parameters confirm that MAIN is designed to be registered and executed as an Oracle concurrent program rather than called only from client code.

Tables Accessed

The referenced tables fall into two groups. The first group supplies Fixed Assets source data: FA_CATEGORIES and FA_CATEGORIES_B hold the category identifiers and descriptive attributes, while FA_SYSTEM_CONTROLS provides the system-level settings and defaults that govern category behavior, including the asset key flexfield configuration. The second group comprises the FII target and reference objects. FII_FA_CAT_DIMENSIONS, FII_FA_CAT_DIMENSIONS_S, and FII_FA_CAT_DIMENSIONS_S1 hold the category dimension and its staging or snapshot counterparts; FII_FA_CAT_SEGMENTS stores the per-segment breakdown of each category; and FII_CHANGE_LOG records the change history that supports incremental ETL. Flexfield metadata is resolved through FND_ID_FLEX_STRUCTURES_TL, FND_ID_FLEX_SEGMENTS, FND_FLEX_VALIDATION_TABLES, FND_FLEX_VALUE_SETS, and FND_SEGMENT_ATTRIBUTE_VALUES, which together allow the package to translate the asset key flexfield structure into named segments and their value sets. DUAL supports statement-level evaluation, and PLITBLM is the PL/SQL integer-to-Boolean utility table used by the FII extraction framework.

Usage Notes

FII_FA_CAT_C is normally invoked during Financials Intelligence setup, incremental ETL, and after additions or changes to Fixed Assets categories that must appear in the FII category dimension. Because MAIN accepts errbuf and retcode, the standard invocation path is a concurrent program registered in APPS; the package is also referenced by one other package, indicating it participates in a coordinated FII processing chain rather than standing alone. The pmode argument permits the caller to vary behavior between initial and incremental runs. The date_tbl, num_tbl, and v30_tbl collection types declared in the header are generic PL/SQL table types used internally by FII packages to pass arrays of dates, numbers, and thirty-character strings between subprograms; date_tbl in particular supports date-range and effective-date comparisons during extraction. As with any APPS package, changes should be made only through Oracle-supplied patches, and callers should treat the declared interface as stable across 12.1.1 and 12.2.2.