Search Results week_number




Overview
Explore in AI Chat

Overview

FII.EDW_TIME_WEEK_LSTG is an interface (staging) table in the Oracle E-Business Suite warehouse layer, owned by the FII schema and registered in FND Design Data as FII.EDW_TIME_WEEK_LSTG. Its documented purpose is to serve as the interface table for the week-level time dimension. Data from multiple transactional source systems is staged here for verification and cleansing before being loaded into the corresponding dimension-level table. The object resides in the APPS_TS_INTERFACE tablespace with a PCTFREE of 10, and its status is VALID in the ETRM 12.1.1 physical schema, which documents 21 columns.

Heuristic Data Vault classification of this object is standalone, mined from its foreign-key structure. In Data Vault modeling terms this suggests the table behaves neither as a classic hub nor as a link, but rather as an independent staging construct that carries descriptive attributes for a single business concept (the calendar week) prior to dimensional load. It is not a satellite of an existing hub in the modeled relationships; it functions as a source-aligned staging structure whose rows are validated and then promoted.

Key Information Stored

The table captures one row per week definition submitted by a source system. The most significant columns are:

  • WEEK_PK (VARCHAR2(20)) — the unique identifier for the week. This is the business-key candidate, and it is the only column indexed (via the non-unique normal index EDW_TIME_WEEK_LSTG_N1 on WEEK_PK).
  • WEEK_NUMBER (NUMBER) — the number of the week within a year, the attribute most commonly searched for by users.
  • NAME (VARCHAR2(2000)) and WEEK (VARCHAR2(80)) — week name/descriptive label.
  • START_DATE and END_DATE (DATE) — the boundaries of the week period.
  • TIMESPAN (NUMBER) — the duration of the time period.
  • PERIOD_445_FK_KEY (NUMBER) and PERIOD_445_FK (VARCHAR2(20)) — foreign key linkage to the 445 period level.
  • REQUEST_ID (NUMBER) — the concurrent request that last updated the row (FK to FND_CONCURRENT_REQUESTS.REQUEST_ID).
  • OPERATION_CODE, COLLECTION_STATUS, and ERROR_CODE — control columns used by the collection program to determine insert/update/delete intent and to store validation errors.
  • INSTANCE (VARCHAR2(40)) — source instance identifier.
  • DELETION_DATE (DATE) — deletion date of the record.
  • ROW_ID (ROWID) — physical row identifier, with a documented FK to CS_SYSTEMS_ALL_B_TEMP.
  • USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — user-defined extensibility columns.

Common Use Cases and Queries

Typical use of this table is to inspect or reconcile week data before it is promoted to the dimension level table. Common scenarios include validating that week numbers fall within expected ranges, checking collection status, and reviewing rows that raised errors. A representative query for a specific week number is:

SELECT WEEK_PK, WEEK_NUMBER, NAME, START_DATE, END_DATE,
       COLLECTION_STATUS, OPERATION_CODE, ERROR_CODE, REQUEST_ID
FROM   FII.EDW_TIME_WEEK_LSTG
WHERE  WEEK_NUMBER = :week_number;

To find rows that failed validation:

SELECT WEEK_PK, WEEK_NUMBER, ERROR_CODE
FROM   FII.EDW_TIME_WEEK_LSTG
WHERE  COLLECTION_STATUS = 'ERROR';

To correlate the staging load with the concurrent program that touched the rows, join REQUEST_ID to FND_CONCURRENT_REQUESTS. Reporting uses include auditing week-to-445-period alignment through PERIOD_445_FK and confirming that start/end dates are contiguous across the calendar.

Related Objects

  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to identify the concurrent program that last updated each staging row.
  • CS_SYSTEMS_ALL_B_TEMP — referenced from ROW_ID, linking the staged row to the collection system context.
  • 445 period dimension / period-level table — reached via PERIOD_445_FK and PERIOD_445_FK_KEY.
  • Week-level dimension target table — the destination into which cleansed rows from this interface table are loaded.
  • EDW_TIME_WEEK_LSTG_N1 — the index supporting WEEK_PK lookups during the load and verification process.