Search Results gms_billing_cancellations

Overview

GMS_BILLING_CANCELLATIONS is a table within the GMS (Grants Accounting) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as a journal that captures deleted billing event distributions. Specifically, the table records distributions that have been removed from the billing process as a result of deletions, cancelled revenue, or cancelled invoices. This object is therefore an audit and reconciliation artifact: rather than allowing billing distributions to vanish silently when revenue is reversed or an invoice is voided, the application preserves the distribution detail in GMS_BILLING_CANCELLATIONS for traceability and downstream analysis.

The documented physical schema for 12.2.2 reports an owner of GMS with 14 columns, and the object is flagged as VALID. Its role is closely tied to the Grants Accounting billing lifecycle, where award and project expenditures are accumulated into billing events and eventually distributed to invoices. When those events or their resulting invoices are backed out, the corresponding expenditure distributions are written to this table.

The heuristic Data Vault classification mined from the foreign key structure is standalone, meaning the table has no documented parent references other than a single outgoing relationship. From a modeling perspective, this suggests treating the object as a self-contained record of cancellation activity rather than as a hub, link, or satellite within a conventional Data Vault model. In practice, it behaves as an event journal whose grain is one cancelled distribution line per row.

Key Information Stored

The table stores 14 documented columns. The most significant for identification, reconciliation, and reporting are:

• EXPENDITURE_ITEM_ID — the expenditure item whose billing distribution was cancelled. This is the sole documented foreign key, referencing PA_EXPENDITURE_ITEMS_ALL, and is the principal join key back into Projects and Grants expenditure data.
• EVENT_NUM — the billing event number associated with the cancelled distribution.
• AWARD_PROJECT_ID and ACTUAL_PROJECT_ID — the award-facing and actual project identifiers, distinguishing sponsored award context from the execution project.
• ACTUAL_TASK_ID — the task on the actual project against which the expenditure was recorded.
• EXPENDITURE_ORG_ID — the organization that owns the expenditure, supporting organizational reporting and security.
• BILL_AMOUNT — the monetary amount of the cancelled billing distribution, central to financial reconciliation.
• BURDEN_EXP_TYPE and BURDEN_COST_CODE — burden (indirect cost) classification attributes for the cancelled line.
• ADL_LINE_NUM — the award distribution line number, tying the cancellation to a specific award line.
• RESOURCE_LIST_MEMBER_ID — identifies the resource list member used in the billing calculation.
• CALLING_PROCESS — indicates the process or program that triggered the cancellation, useful for diagnosing the cause.
• CREATION_DATE and DELETION_DATE — the timestamps of record insertion and of the underlying deletion, enabling time-based auditing.

The documented metadata does not expose an explicit surrogate primary key column, nor does it list unique indexes. In the absence of a documented surrogate key, candidate business keys for identifying a row would reasonably include the combination of EXPENDITURE_ITEM_ID, EVENT_NUM, DELETION_DATE, and ADL_LINE_NUM, though this should be validated against the actual database constraints rather than assumed. Analysts should verify the real primary key and unique index definitions directly in the GMS schema before relying on any assumed key.

Common Use Cases and Queries

Typical uses of this table center on reconciliation, audit, and root-cause analysis of reversed billing. Common scenarios include:

• Cancellation audit trail — reporting which expenditure items were cancelled, by whom, and when, using CALLING_PROCESS and the date columns.
• Financial reconciliation — summing BILL_AMOUNT by project, award, or organization to quantify cancelled billing.
• Trend analysis — grouping cancellations by EVENT_NUM or CALLING_PROCESS to identify recurring reversal patterns.
• Grants compliance — confirming that reversed revenue on sponsored awards is properly documented.

A representative query joining the cancellation journal back to expenditure items is:

• SELECT c.EXPENDITURE_ITEM_ID, c.EVENT_NUM, c.BILL_AMOUNT, c.CALLING_PROCESS, c.CREATION_DATE, c.DELETION_DATE FROM GMS.GMS_BILLING_CANCELLATIONS c JOIN PA.PA_EXPENDITURE_ITEMS_ALL e ON c.EXPENDITURE_ITEM_ID = e.EXPENDITURE_ITEM_ID WHERE c.DELETION_DATE >= :start_date;

Aggregations over BILL_AMOUNT grouped by AWARD_PROJECT_ID or EXPENDITURE_ORG_ID support management reporting, while filtering on CALLING_PROCESS isolates cancellations caused by specific batch or concurrent programs.

Related Objects

The documented foreign key defines the principal relationship. The most significant related objects are:

• PA_EXPENDITURE_ITEMS_ALL — the referenced parent table. The join is GMS_BILLING_CANCELLATIONS.EXPENDITURE_ITEM_ID = PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID, linking each cancellation to the originating expenditure item and its project, task, and organization context.
• GMS_BILLING_EVENTS — the billing event header that the cancelled distribution belonged to, joined via EVENT_NUM, providing the event-level context for the cancellation.
• GMS_BILLING_DISTRIBUTIONS — the active distribution records from which cancelled lines are removed, enabling before-and-after comparison.
• GMS_AWARD_DISTRIBUTIONS / award line tables — related through ADL_LINE_NUM, tying cancellations to specific award distribution lines.
• PA_PROJECTS_ALL and PA_TASKS — joined via AWARD_PROJECT_ID, ACTUAL_PROJECT_ID, and ACTUAL_TASK_ID to resolve project and task names for reporting.
• GMS_REVENUE / invoice-related tables — the invoices and revenue records whose reversal drives cancellations, relevant when reconciling cancelled revenue to invoice adjustments.

Because the table is classified heuristically as standalone, it is best treated as a leaf-level journal. Reporting solutions typically wrap it in views that join to the expenditure, project, and award tables above to reconstruct the full billing and reversal story.