Search Results fv_dms_code_definitions_pk

Overview

FV_DMS_CODE_DEFINITIONS_ALL is a Federal Financials (FV) table that stores program code assignment information required by the Delinquent Invoice Referral process. In Oracle EBS 12.1.1 and 12.2.2, the Delinquent Management System (DMS) uses this table to map internal program code identifiers to the codes recognized by referring agencies, thereby enabling delinquent invoices to be categorized correctly when referred to Treasury or other collecting agencies.

The table is owned by the FV schema, carries a VALID status in the ETRM metadata, and contains 26 documented columns. Its multiplicity tag (_ALL) indicates that it is organization-enabled and supports multi-org partitioning through the ORG_ID column, consistent with other Federal Financials reference tables. From a Data Vault modeling perspective, the metadata heuristic classifies this table as satellite-leaning: it extends a parent entity (the agency definition) with descriptive attributes rather than acting as a pure hub or link. The single foreign key relationship reinforces this interpretation, as the table provides context around the agency-level record.

Key Information Stored

The most significant columns documented in the ETRM schema are:

• PROGRAM_CODE_ID — the surrogate primary key, defined by FV_DMS_CODE_DEFINITIONS_PK. It uniquely identifies each program code definition row.
• DMS_PROGRAM_CODE — the business-facing program code value required by the Delinquent Invoice Referral process; the principal business-key candidate for external reference.
• DESCRIPTION — the human-readable name or explanation of the program code.
• AGENCY_ID — the foreign key to FV_DMS_AGENCY_DEFINITIONS_ALL, indicating the agency to which the program code belongs.
• ORG_ID — the operating unit (multi-org) identifier, scoping the record to a specific organization.
• LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns capturing audit and accountability metadata.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (Descriptive Flexfield) block for customer-specific extensions.

The surrogate key PROGRAM_CODE_ID is distinct from the business key candidates (DMS_PROGRAM_CODE combined with AGENCY_ID and ORG_ID), which together provide the natural uniqueness constraint for the reference data.

Common Use Cases and Queries

Typical use cases include validating program code values during DMS referral processing, reporting on program code assignments by agency, and configuring the multi-org setup for Federal agencies. A representative query joining to the parent agency table is:

SELECT c.PROGRAM_CODE_ID,
       c.DMS_PROGRAM_CODE,
       c.DESCRIPTION,
       a.AGENCY_NAME
FROM   FV.FV_DMS_CODE_DEFINITIONS_ALL c,
       FV.FV_DMS_AGENCY_DEFINITIONS_ALL a
WHERE  c.AGENCY_ID = a.AGENCY_ID
AND    c.ORG_ID = :p_org_id;

Because the table is referenced by integration interfaces such as IGR_EPU_CATEGORY_UPG, IGR_I_LINES_INT, and IGS_AD_INQ_PRG_INT, reporting frequently joins on PROGRAM_CODE_ID to resolve the code description when analyzing referred or upgraded invoice lines.

Related Objects

The following are the most significant related objects based on documented FK relationships:

• FV_DMS_AGENCY_DEFINITIONS_ALL — parent table; joined via AGENCY_ID to retrieve agency context.
• IGR_EPU_CATEGORY_UPG — references PROGRAM_CODE_ID; used in the EPU category upgrade process.
• IGR_I_LINES_INT — references PROGRAM_CODE_ID; holds invoice line interface data.
• IGS_AD_INQ_PRG_INT — references PROGRAM_CODE_ID; supports admissions inquiry program integration.

These dependencies confirm that the table acts as a central reference for program code definitions consumed across Federal Financials and connected interface modules.