Results for “prim_amount_g”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

FII.FII_TOP_SPENDERS_STG is a staging table in the Oracle E-Business Suite Financial Intelligence (FII) product family. Its documented purpose is to store base summary stage records for the Top Spenders analytical subject area. FII products deliver pre-aggregated financial and procurement intelligence, and staging tables such as this one act as the intermediate landing zone between source transaction systems (typically Purchasing, Payables, and General Ledger) and the final FII summary/fact tables consumed by dashboards and reports. Records are typically loaded by FII concurrent programs and then validated, transformed, and merged into permanent summary tables.

From a data modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone. This suggests that, within a Data Vault layered architecture, the table is best treated as an independent staging area rather than as a conventional hub, link, or satellite. Its single documented foreign key (YEAR_ID referencing JAI_FA_AST_YEARS) is a reference/dimension pointer rather than the defining relationship of a link table. Any Data Vault implementation would therefore model this object as a transient staging entity that feeds proper hubs and satellites downstream.

Key Information Stored

The table carries 14 documented columns. The most significant groups are:

• Time/period dimensions: PERIOD_ID, QTR_ID, and YEAR_ID. YEAR_ID is the only documented foreign key, referencing JAI_FA_AST_YEARS. These columns drive the period-based aggregation of spend.
• Spender identity: PERSON_ID identifies the individual spender (employee/buyer), and CCC_ORG_ID identifies the organization or cost center context for that spender.
• Measures: PRIM_AMOUNT_G and SEC_AMOUNT_G hold the primary and secondary spend amounts (the _G suffix denotes the global/functional currency amount), while NO_OF_EXP_RPTS records the count of expense reports contributing to the spender's total.
• Classification: SLICE_TYPE_FLAG distinguishes the analytical slice or category of the staged row, enabling multiple rankings or breakdowns within the same load.
• Audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN provide standard WHO-column auditability.

The documented metadata does not expose a named surrogate primary key or unique index, so the natural business key is likely the composite of YEAR_ID, PERIOD_ID (or QTR_ID), PERSON_ID, CCC_ORG_ID, and SLICE_TYPE_FLAG. Because this is a staging table, a surrogate key, if present, would be implementation-defined rather than documented.

Common Use Cases and Queries

The primary use case is intra-load processing: an FII concurrent program populates the stage, applies validation and currency conversion, and then inserts or merges rows into the permanent Top Spenders summary. Typical reporting queries include:

• Ranking spenders within an organization for a period:

  SELECT person_id, SUM(prim_amount_g) total_spend
  FROM   fii.fii_top_spenders_stg
  WHERE  year_id = :p_year AND period_id = :p_period
  GROUP  BY person_id
  ORDER  BY total_spend DESC;

• Comparing primary versus secondary amounts to reconcile currency or category splits using PRIM_AMOUNT_G and SEC_AMOUNT_G.
• Analyzing expense report frequency alongside spend using NO_OF_EXP_RPTS, to distinguish high-value one-off spenders from high-volume submitters.
• Filtering by SLICE_TYPE_FLAG to reproduce specific dashboard slices.
• Validating staged data before final load by joining YEAR_ID to its parent year table and confirming amounts are non-null and periods are open.

Related Objects

The documented relationship set is limited, but the following objects are most relevant:

• JAI_FA_AST_YEARS — parent reference for YEAR_ID; the only documented foreign key.
• FII_TOP_SPENDERS (or equivalent permanent summary table) — the downstream target that the stage feeds; join on YEAR_ID, PERIOD_ID, PERSON_ID, and CCC_ORG_ID.
• PER_EMPLOYEES / PER_ALL_PEOPLE_F — source of PERSON_ID attributes for spender names and assignments.
• HR_OPERATING_UNITS or HR_ALL_ORGANIZATION_UNITS — resolves CCC_ORG_ID to an organization name.
• GL_PERIODS / FII_PERIODS — resolves PERIOD_ID and QTR_ID to calendar context.
• FII concurrent programs and the FII Top Spenders dashboard/report definitions that read and publish this staged data.