Results for “pa_agreement_types_u1”

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

Overview

PA.PA_AGREEMENT_TYPES is an implementation-reference (setup) table in the Oracle Projects (PA) schema that stores the user-defined classifications of agreements used to fund projects. Two commonly cited examples of agreement types are Purchase Order and Service Agreement. Each row represents one classification, carrying the default attributes — revenue limit behavior, active date range, default payment term, descriptive flexfield context, and description — that the application applies to agreements of that type.

Because the table is a relatively narrow configuration object keyed by a business-meaningful code rather than by a system-generated surrogate, its heuristic Data Vault classification is satellite-leaning. In a Data Vault model it would most naturally be treated as a satellite attached to an agreement-type hub (or as a reference/type dimension), keyed on the agreement type code, holding the descriptive and defaulting attributes for that code over time. It is not itself a transactional or link table.

Key Information Stored

The table contains 27 documented columns in ETRM 12.2.2, of which the following are most significant:

• AGREEMENT_TYPE (VARCHAR2(30), mandatory) — the implementation-defined code that uniquely identifies the classification. This is the primary key (PA_AGREEMENT_TYPES_PK) and the single business-key candidate, enforced by the unique index PA_AGREEMENT_TYPES_U1. There is no separate numeric surrogate key.
• REVENUE_LIMIT_FLAG — the default flag indicating whether revenue and invoices for projects funded by the agreement may exceed the allocated funding amount. This is the central control attribute used during funding and revenue generation.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the first and last effective dates on which the agreement type can be selected and used.
• TERM_ID (NUMBER(15)) — the identifier of the payment term that defaults onto agreements of this type and determines the schedule on which invoices funded by the agreement are paid. This is a foreign key to RA_TERMS_B.
• DESCRIPTION (VARCHAR2(250)) — explanatory text for the agreement type, typically displayed as an LOV description.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield context and segment columns (ATTRIBUTE1–8 are VARCHAR2(150); the set continues through ATTRIBUTE15). These support client-specific extensions without schema change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Who columns maintained automatically by the application.

Common Use Cases and Queries

The most common use is resolving the agreement type code to its descriptive and defaulting attributes when reporting on agreements and funded projects. A typical query joins PA_AGREEMENTS_ALL to PA_AGREEMENT_TYPES on AGREEMENT_TYPE and checks the effective dates:

SELECT at.agreement_type, at.description, at.revenue_limit_flag, at.term_id, at.start_date_active, at.end_date_active FROM pa.pa_agreement_types at WHERE SYSDATE BETWEEN at.start_date_active AND NVL(at.end_date_active, SYSDATE + 1) ORDER BY at.agreement_type;

Other practical patterns include: identifying which agreement types permit revenue and invoicing beyond funding (REVENUE_LIMIT_FLAG); joining TERM_ID to RA_TERMS_B to report default payment terms by funding type; listing agreements grouped by type for funding portfolio reporting; validating that only active, valid agreement types are available in the LOV; and extracting the descriptive flexfield context (ATTRIBUTE_CATEGORY) plus segments for extension-based reporting. Because AGREEMENT_TYPE is a native unique index, it can be used directly as an LOV ID column.

Related Objects

• PA.PA_AGREEMENTS_ALL — the parent agreements table. Its AGREEMENT_TYPE column references PA_AGREEMENT_TYPES.AGREEMENT_TYPE; this is the principal consumer of the setup data.
• RA.RA_TERMS_B — referenced via TERM_ID for the default payment term, enabling join-based reporting of payment schedules by agreement type.
• PA_AGREEMENT_TYPES_PK — the primary key constraint on AGREEMENT_TYPE.
• PA_AGREEMENT_TYPES_U1 — the unique index (APPS_TS_TX_IDX tablespace) enforcing the single business-key candidate, AGREEMENT_TYPE. A search for "pa_agreement_types_u1" therefore resolves to this index rather than to a table.
• Descriptive flexfield definitions registered against PA_AGREEMENT_TYPES via ATTRIBUTE_CATEGORY and ATTRIBUTE1–15.

Because the table is a setup, satellite-leaning object, changes to its rows propagate into defaulting behavior for every agreement created thereafter and should be made through the appropriate Oracle Projects setup forms or concurrent programs rather than by direct DML.