Search Results pa_expend_typ_sys_links_u1

Overview

PA.PA_EXPEND_TYP_SYS_LINKS is a foundational setup table in the Oracle Projects (PA) schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores implementation-defined relationships between expenditure types and system linkages, which are also referred to as expenditure type classes. Expenditure type classes are system-defined categories that classify Oracle Projects transactions and determine how individual expenditure items are processed during costing, billing, and accounting. By allowing a single expenditure type to be associated with multiple system linkage functions, the table provides the flexibility needed to route the same expenditure classification through several processing paths without duplicating the expenditure type definition. The object resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index lives in APPS_TS_TX_IDX. Based on the foreign key structure, the record behaves as a link (junction) between expenditure types and system linkages, and the heuristic Data Vault modeling suggestion is to treat it as a link entity. The primary key is PA_EXPEND_TYP_SYS_LINKS_PK, defined on (EXPENDITURE_TYPE, SYSTEM_LINKAGE_FUNCTION).

Key Information Stored

• EXPENDITURE_TYPE (VARCHAR2, 30): The implementation-defined name of the expenditure type. It is a mandatory component of the composite business key.
• SYSTEM_LINKAGE_FUNCTION (VARCHAR2): The system linkage that classifies the expenditure type to drive expenditure processing for items classified by that expenditure type. It is the second component of the composite business key.
• START_DATE_ACTIVE and END_DATE_ACTIVE: Effective dating columns controlling when the expenditure type/system linkage association is active.
• ATTRIBUTE_CATEGORY (VARCHAR2, 30) and ATTRIBUTE1 through ATTRIBUTE15 (VARCHAR2, 150 each): Descriptive flexfield context and segment columns, enabling clients to capture additional implementation-specific information on each association.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN: Standard Oracle Applications who-columns that audit record creation and modification.

The documented unique index PA_EXPEND_TYP_SYS_LINKS_U1 on (EXPENDITURE_TYPE, SYSTEM_LINKAGE_FUNCTION) is the business-key candidate that prevents duplicate associations between the same expenditure type and the same system linkage. Because both key columns are stored as natural identifiers rather than generated numeric surrogates, the primary key also serves as the business key in this table.

Common Use Cases and Queries

The table is most frequently queried to determine which expenditure type classes an expenditure type is assigned to, and conversely to list all expenditure types associated with a given system linkage function. A representative query joins the table to PA_EXPENDITURE_TYPES to resolve descriptive names:

• Identify class assignments for an expenditure type: SELECT SYSTEM_LINKAGE_FUNCTION FROM PA_EXPEND_TYP_SYS_LINKS WHERE EXPENDITURE_TYPE = :p_expenditure_type;
• List all expenditure types tied to a system linkage: SELECT EXPENDITURE_TYPE FROM PA_EXPEND_TYP_SYS_LINKS WHERE SYSTEM_LINKAGE_FUNCTION = :p_linkage;
• Detect duplicate setup: group by EXPENDITURE_TYPE, SYSTEM_LINKAGE_FUNCTION having count(*) > 1, verifying no conflicting assignments exist against the unique index.
• Validate effective dating: filter WHERE START_DATE_ACTIVE <= SYSDATE AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE >= SYSDATE) when auditing current configuration.

Reporting use cases include setup validation reports, migration audits between environments, and diagnostics where an expenditure item failed processing because the expenditure type lacked the expected system linkage assignment.

Related Objects

• PA.PA_EXPENDITURE_TYPES: referenced by PA_EXPEND_TYP_SYS_LINKS.EXPENDITURE_TYPE; join key is EXPENDITURE_TYPE. Holds the master expenditure type definitions.
• PA.PA_SYSTEM_LINKAGES: referenced by PA_EXPEND_TYP_SYS_LINKS.SYSTEM_LINKAGE_FUNCTION; join key is SYSTEM_LINKAGE_FUNCTION. Defines the expenditure type classes.
• PA.PA_EXPENDITURE_ITEMS: transactions whose processing depends on the system linkage assigned to their expenditure type.
• PA.PA_IMPLEMENTATION_COSTINGS and related costing/billing setup tables that consume expenditure type classification.
• Oracle Projects setup APIs used to create and maintain expenditure type assignments, which write to this table.