Search Results gms_fp_distributions




Overview
Explore in AI Chat

Overview

GMS_FP_DISTRIBUTIONS is a Grants Accounting (GMS) table in the Oracle E-Business Suite database, owned by the GMS schema. It stores the funding pattern distribution details for a defined funding pattern, allowing a single funding pattern to be allocated across one or more awards in specific proportions. In Grants Accounting, funding patterns define how sponsored project costs are shared among funding sources; this table captures the per-award distribution rows that make up each pattern.

From a dimensional modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table hangs off GMS_FUNDING_PATTERNS_ALL via FUNDING_PATTERN_ID, functioning as a detail satellite that records the distribution breakdown, while FUNDING_PATTERN_ID plus AWARD_ID act as the composite business key. It is not a standalone hub or a pure link table, but a dependent detail entity driven by the parent funding pattern.

Key Information Stored

The table contains nine documented columns in ETRM 12.2.2. The most significant are:

  • FUNDING_PATTERN_ID – Identifies the parent funding pattern. Part of the primary key and the foreign key to GMS_FUNDING_PATTERNS_ALL.
  • AWARD_ID – Identifies the award receiving a share of the distribution. Part of the primary key and unique business key.
  • DISTRIBUTION_NUMBER – Sequence number distinguishing multiple distribution rows within a pattern.
  • DISTRIBUTION_VALUE – The percentage or amount allocated to the associated award.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY – Audit columns recording the last modification.
  • CREATION_DATE, CREATED_BY – Audit columns recording row creation.
  • LAST_UPDATE_LOGIN – Login identifier of the updating session.

The surrogate primary key is GMS_FP_DIST_PK, defined over (FUNDING_PATTERN_ID, AWARD_ID). The unique index GMS_FP_DISTRIBUTIONS_U1 also covers (FUNDING_PATTERN_ID, AWARD_ID), making this pair the business-key candidate. Consequently, each award may appear only once per funding pattern.

Common Use Cases and Queries

Typical usage centers on reporting and validating how awards are funded within a pattern. A common query joins the distribution rows to the parent pattern to list the awards and their allocated values:

  • Retrieve all awards for a pattern: SELECT award_id, distribution_number, distribution_value FROM gms_fp_distributions WHERE funding_pattern_id = :pattern_id ORDER BY distribution_number;
  • Aggregate distributions to verify a pattern sums to 100 percent: SELECT funding_pattern_id, SUM(distribution_value) FROM gms_fp_distributions GROUP BY funding_pattern_id;
  • Reconcile against the parent pattern: SELECT d.funding_pattern_id, d.award_id, d.distribution_value FROM gms_fp_distributions d, gms_funding_patterns_all p WHERE d.funding_pattern_id = p.funding_pattern_id;
  • Audit recently changed rows using LAST_UPDATE_DATE.

These patterns support sponsor reporting, allocation verification, and Grants Accounting integrations that require knowing which awards share a funding pattern.

Related Objects

The most significant related objects, based on documented relationships, are:

  • GMS_FUNDING_PATTERNS_ALL – The parent table. Join on GMS_FP_DISTRIBUTIONS.FUNDING_PATTERN_ID = GMS_FUNDING_PATTERNS_ALL.FUNDING_PATTERN_ID. This is the only documented foreign key.
  • GMS_AWARDS_ALL – Referenced indirectly through AWARD_ID to resolve award names and numbers.
  • GMS_AWARD_FUNDING_SOURCES – Related funding-source detail that frequently accompanies pattern distribution data.
  • GMS_FP_DISTRIBUTIONS_U1 – Unique index enforcing the FUNDING_PATTERN_ID/AWARD_ID business key.
  • GMS_FP_DIST_PK – Primary key constraint on the same column pair.

Because the metadata documents only one explicit foreign key, award-level joins should be confirmed against the GMS data model before relying on them in production SQL.