Search Results gms_funding_pattern_pk

Overview

GMS_FUNDING_PATTERNS_ALL is a Grants Accounting (GMS) base table that stores the rule definitions used to charge transactions of a sponsored project to one or more funding awards. In Oracle EBS 12.1.1 and 12.2.2, this table is central to the automatic funding allocation process: when expenditures are incurred against a project or task, the funding pattern determines the sequence in which funding awards absorb those costs, and whether retroactive redistribution is permitted. Without an active funding pattern, a sponsored project's costs cannot be systematically applied to awards, and the distribution engine has no rule set to follow.

The ETRM metadata classifies this object as hub-leaning under a heuristic Data Vault model. This is a modeling suggestion only: the table behaves as a relatively stable reference entity (a hub) whose surrogate key is referenced by multiple downstream transactional and distribution tables, rather than as a purely transactional satellite.

Key Information Stored

The table contains 16 documented columns. The primary key is defined by the constraint GMS_FUNDING_PATTERN_PK, enforced on the FUNDING_PATTERN_ID column. A unique index, GMS_FUNDING_PATTERNS_ALL_U1, is also defined on FUNDING_PATTERN_ID, which is documented as the business-key candidate. The most significant columns include:

• FUNDING_PATTERN_ID — surrogate primary key and business-key candidate; the value propagated to all dependent tables.
• ORG_ID — operating unit that owns the funding pattern, supporting multi-org data segregation.
• FUNDING_SEQUENCE — the ordered priority in which funding awards are charged.
• FUNDING_NAME — user-facing identifier for the pattern.
• RETROACTIVE_FLAG — indicates whether transactions already charged can be retroactively re-funded.
• PROJECT_ID and TASK_ID — the sponsored project and task to which the pattern applies.
• STATUS and FUNDS_STATUS — control the lifecycle/active state of the pattern and its funds checking state.
• START_DATE and END_DATE — the effective period during which the pattern governs funding.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns for audit and concurrency tracking.

Common Use Cases and Queries

Typical scenarios include validating that every active project has a corresponding funding pattern, auditing patterns by operating unit, and tracing how a distribution was charged back to its rule. A representative query listing active patterns for a project is:

• SELECT funding_pattern_id, funding_name, funding_sequence, status, start_date, end_date FROM gms_funding_patterns_all WHERE project_id = :project_id AND org_id = :org_id AND status = 'A' ORDER BY funding_sequence;
• Joining to distributions: SELECT p.funding_name, d.* FROM gms_fp_distributions d, gms_funding_patterns_all p WHERE d.funding_pattern_id = p.funding_pattern_id;
• Reconciling interface rows: SELECT t.* FROM gms_transaction_interface_all t, gms_funding_patterns_all p WHERE t.funding_pattern_id = p.funding_pattern_id AND p.status = 'A';

Related Objects

The FK metadata shows FUNDING_PATTERN_ID is referenced by several dependent objects. The most significant are:

• GMS_FP_DISTRIBUTIONS — joins on FUNDING_PATTERN_ID; holds the percentage/distribution detail of each pattern.
• GMS_BC_PACKETS — burden/charge packets that reference the funding pattern used.
• GMS_DISTRIBUTION_DETAILS — stores calculated distributions tied to the pattern.
• GMS_TRANSACTION_INTERFACE_ALL — inbound transactions carrying the funding pattern for processing.
• GMS_AWARD_DISTRIBUTIONS — award-level distributions referencing the pattern.

Together these objects form the funding-allocation chain, with GMS_FUNDING_PATTERNS_ALL acting as the defining hub.