Results for “gms_project_fundings_v”

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

Overview

GMS_PROJECT_FUNDINGS_V is a secured reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the GMS – Grants Accounting product. It presents project funding records that link awarded grant installments to specific projects and, optionally, to tasks. The view exposes the surrogate key GMS_PROJECT_FUNDING_ID, which is the primary identifier users typically search for when tracing a funding allocation back to its source transaction.

The view's distinguishing characteristic is its enforcement of project-level security. Rather than exposing project numbers, names, task numbers, and task names directly, the view calls PA_SECURITY.ALLOW_QUERY(P.PROJECT_ID) for each row. When the current user is authorized to query the project, the actual PA_PROJECTS_ALL and PA_TASKS values are returned; when the user is not authorized, a masked value derived from the PA_LOOKUPS lookup type 'TRANSLATION' with lookup code 'SECURED_DATA' is substituted. This design makes the view suitable for forms, reports, and integrations that must respect Oracle Projects security rules without reimplementing them.

Underlying Base Objects

The view is defined over six documented objects: GMS_PROJECT_FUNDINGS (synonym to the base funding table), GMS_INSTALLMENTS (synonym), PA_PROJECTS_ALL (synonym), PA_PROJECT_TYPES (synonym), PA_TASKS (synonym), PA_LOOKUPS (view), and the PA_SECURITY package. Joins are established as follows:

• GMS_PROJECT_FUNDINGS F is the driving table; each row represents one funding allocation.
• PA_PROJECTS_ALL P joins on P.PROJECT_ID = F.PROJECT_ID, supplying project attributes.
• PA_TASKS T is outer-joined on T.TASK_ID = F.TASK_ID, so funding at the project level without a task is still returned.
• GMS_INSTALLMENTS I joins on I.INSTALLMENT_ID = F.INSTALLMENT_ID, supplying the installment number and award reference.
• PA_PROJECT_TYPES PT joins on PT.PROJECT_TYPE = P.PROJECT_TYPE and PT.ORG_ID = P.ORG_ID, supplying the project type class code and allowable funding level code.
• PA_LOOKUPS LK is a fixed join on lookup_type = 'TRANSLATION' and lookup_code = 'SECURED_DATA', returning the masked display value.

Key Columns

• GMS_PROJECT_FUNDING_ID / PROJECT_FUNDING_ID — Surrogate identifiers for the funding record; the former is the GMS key, the latter the underlying Projects funding key.
• PROJECT_ID, TASK_ID, INSTALLMENT_ID, AWARD_ID — Foreign keys to the project, task, installment, and award entities.
• PROJECT_NUMBER, PROJECT_NAME, TASK_NUMBER, TASK_NAME — Descriptive attributes subject to PA_SECURITY masking.
• FUNDING_AMOUNT and DATE_ALLOCATED — The monetary amount allocated and the date the allocation was made.
• INSTALLMENT_NUM — The installment sequence from GMS_INSTALLMENTS.
• ALLOWABLE_FUNDING_LEVEL_CODE / PROJECT_TYPE_CLASS_CODE — From PA_PROJECT_TYPES; these govern whether funding is controlled at project or task level.
• ORG_ID — Operating unit identifier, critical for multi-org filtering.
• ROW_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO and row-identification columns.

Common Use Cases and Queries

Typical uses include award-to-project funding reconciliations, installment-to-funding traceability, and secured ad hoc reporting for grants administrators. Because the view returns one row per funding allocation, it is well suited to amount aggregation by project, task, or award.

Trace a funding record by its primary key:

• SELECT gms_project_funding_id, project_number, project_name, task_number, funding_amount, date_allocated FROM gms_project_fundings_v WHERE gms_project_funding_id = :funding_id;

Summarize funding by project within an operating unit:

• SELECT project_number, project_name, SUM(funding_amount) total_funded FROM gms_project_fundings_v WHERE org_id = :org_id GROUP BY project_number, project_name;

List all funding allocations for a given award:

• SELECT award_id, installment_num, project_number, project_name, task_name, funding_amount FROM gms_project_fundings_v WHERE award_id = :award_id ORDER BY installment_num, project_number;

Note that PROJECT_NUMBER, PROJECT_NAME, TASK_NUMBER, and TASK_NAME are truncated or replaced for unauthorized projects; report designs should accommodate masked values. Query performance is best when filters use GMS_PROJECT_FUNDING_ID, PROJECT_ID, INSTALLMENT_ID, AWARD_ID, or ORG_ID, since these are driven directly from the base funding table.