Search Results gms_event_intersect_u1

Overview

The GMS_EVENT_INTERSECT table is a core transactional table within Oracle E-Business Suite's Grants Accounting (GMS) module, specifically for releases 12.1.1 and 12.2.2. It serves as a critical junction table that tracks the processing of expenditure items for billing and revenue accrual under the cost distribution rule. When expenditure items are processed through concurrent requests for generating revenue or customer invoices, this table logs the relationship between the source expenditure, the resulting financial event, and the associated award project. Its primary role is to maintain an audit trail and prevent duplicate processing, ensuring that each expenditure item is correctly accounted for in the appropriate award and event context.

Key Information Stored

The table's columns are designed to link expenditure details with event generation data. The mandatory EXPENDITURE_ITEM_ID is the unique identifier from the Projects module. The AWARD_PROJECT_ID column, central to the user's search, stores the unique identifier for the award project against which the billing or revenue is being accrued. The EVENT_NUM and EVENT_TYPE (values 'Revenue' or 'Invoice') identify the resulting financial event. The AMOUNT column holds the billed value, while REQUEST_ID ties the record to a specific concurrent process run, which is crucial for batch control and updates. The ADL_LINE_NUM specifies the award distribution line, providing granular cost allocation detail.

Common Use Cases and Queries

A primary use case is reconciling processed expenditures and troubleshooting billing or revenue accrual runs. Analysts often query this table to identify which expenditure items have been processed for a specific award or within a particular concurrent request. Common SQL patterns include finding details for a specific award project or verifying processing status.

• To find all billing events for a specific award project:

  SELECT expenditure_item_id, event_num, amount, request_id
  FROM gms.gms_event_intersect
  WHERE award_project_id = &AWARD_PROJECT_ID
  AND event_type = 'Invoice';

• To check if an expenditure item has been processed for revenue:

  SELECT * FROM gms.gms_event_intersect
  WHERE expenditure_item_id = &EXP_ITEM_ID
  AND revenue_accumulated IS NOT NULL;

• To analyze all items processed by a specific concurrent request:

  SELECT award_project_id, event_type, COUNT(*), SUM(amount)
  FROM gms.gms_event_intersect
  WHERE request_id = &REQUEST_ID
  GROUP BY award_project_id, event_type;

Related Objects

The table's structure indicates strong relationships with other GMS and PA (Projects) tables, primarily through its unique and non-unique indexes. The unique index (GMS_EVENT_INTERSECT_U1) enforces integrity on the combination of EXPENDITURE_ITEM_ID, EVENT_NUM, REQUEST_ID, EVENT_TYPE, AWARD_PROJECT_ID, and ADL_LINE_NUM. Multiple non-unique indexes, particularly GMS_EVENT_INTERSECT_N1 and N2 which include AWARD_PROJECT_ID, optimize queries filtering on the award. This table is fundamentally linked to the PA_EXPENDITURE_ITEMS_ALL table via the EXPENDITURE_ITEM_ID column and to award-related tables via the AWARD_PROJECT_ID. The REQUEST_ID column links it to FND_CONCURRENT_REQUESTS, enabling process tracking.