Search Results pa_rep_seq_number_u1

Overview

PA.PA_REP_SEQ_NUMBER is a utility table in the Oracle E-Business Suite Projects (PA) schema. It stores a controlled range of integer sequence values, from -100 through 20000, that serve as the numeric backbone for reporting and capacity calculations across the Projects application. Its documented purpose is to generate capacity values for resources and to supply the month, year, and quarter numbering used within a calendar year. The table is classified as VALID and is shipped as part of the FND Design Data for the PA product.

From a heuristic Data Vault modeling perspective, PA_REP_SEQ_NUMBER is best treated as a standalone reference structure. It carries no foreign key dependencies on other database objects and is not itself a transactional or descriptive entity; it functions as a static or semi-static number generator rather than a hub, link, or satellite in the conventional sense. Any Data Vault implementation would typically model it as a reference table loaded alongside the reporting calendar and resource capacity constructs it supports.

Key Information Stored

The documented physical schema for this object is deliberately minimal, comprising a single column:

• SEQ_NUMBER (NUMBER) — the sole documented column, holding integer sequence values spanning -100 to 20000. This column is mandatory and is both the primary key (PA_REP_SEQ_NUMBER_PK) and the column of the unique index PA_REP_SEQ_NUMBER_U1.

Because SEQ_NUMBER is the only column, the distinction between the surrogate primary key and the business-key candidate collapses onto the same attribute. PA_REP_SEQ_NUMBER_PK enforces row uniqueness at the physical level, while PA_REP_SEQ_NUMBER_U1 enforces the same uniqueness through a separate unique index on SEQ_NUMBER. The inclusion of negative values (beginning at -100) is significant: it allows time-phased reporting models to represent offset periods — for example, prior-period or negative-offset buckets — without requiring a separate offset column. The upper bound of 20000 accommodates long-horizon sequences such as daily or weekly buckets across multi-year reporting windows.

Storage is configured in the APPS_TS_TX_DATA tablespace with PCT Free of 10, while the unique index PA_REP_SEQ_NUMBER_U1 resides in APPS_TS_TX_IDX, following standard EBS separation of transaction data from index storage.

Common Use Cases and Queries

The table is most commonly joined into reporting SQL to produce a dense, gap-free series of period numbers. A typical pattern is a cross join or range join against a resource capacity or calendar query so that every resource receives a row for every month, quarter, or year bucket, including periods with no activity. The simplest retrieval is:

SELECT SEQ_NUMBER FROM PA.PA_REP_SEQ_NUMBER;

In practice, the query is filtered to the range of interest. For monthly reporting within a single calendar year, a predicate such as SEQ_NUMBER BETWEEN 1 AND 12 isolates the month numbers; quarter-level reporting uses BETWEEN 1 AND 4; and offset scenarios use the negative portion of the range, for example SEQ_NUMBER BETWEEN -12 AND 0. When populating capacity reports, the table is joined to project resource assignments and period calendars so that utilization can be displayed against a complete time axis. Reverse-engineering sequences in this manner is a standard technique for building matrix-style Project reporting layouts where resources form rows and time periods form columns.

Related Objects

The documented dependency metadata states that PA.PA_REP_SEQ_NUMBER does not reference any other database object. It is referenced by the APPS synonym PA_REP_SEQ_NUMBER, which allows application code and reports to address the table without schema qualification. Beyond that documented link, the table is consumed programmatically by the Projects reporting and capacity-generation logic rather than through declared foreign keys. Its effective relationships are therefore functional rather than referential:

• APPS.PA_REP_SEQ_NUMBER — the public synonym through which the table is queried by application code.
• PA.PA_REP_SEQ_NUMBER_PK — the primary key constraint on SEQ_NUMBER.
• PA.PA_REP_SEQ_NUMBER_U1 — the unique index on SEQ_NUMBER in APPS_TS_TX_IDX.
• Project resource capacity and reporting calendar constructs — the principal consumers, joining on SEQ_NUMBER to generate month, quarter, and year numbering.

Because no foreign keys are declared, integrators should treat PA_REP_SEQ_NUMBER as a self-contained lookup and avoid assuming enforced relational integrity to period or resource tables. Any join is driven by value range rather than by constraint.