Search Results edw_proj_prj_typ_lstg

Overview

EDW_PROJ_PRJ_TYP_LSTG is a staging table owned by the FII (Financial Intelligence) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as the landing and validation layer for project type level data extracted from source EBS applications before that data is transformed and loaded into the Financial Intelligence data warehouse. Project types are a foundational classification in Oracle Projects, controlling which project attributes, cost and billing configurations, and transaction processing rules apply to a given project. By isolating this dimension in a dedicated staging table, the warehouse ETL process can reconcile, de-duplicate, and conform project type reference data independently of the transactional project facts that depend on it.

Based on the heuristic Data Vault classification mined from the foreign key structure, this table is modeled as standalone, meaning it does not participate in a parent-child hub or link relationship within the mined constraint graph. As a modeling suggestion, such a table is most naturally treated as a reference or low-volume dimension source. The single mined foreign key, ROW_ID referencing CS_SYSTEMS_ALL_B_TEMP, is a technical staging artifact tied to the collection and load process rather than a true business relationship, which reinforces the standalone classification.

Key Information Stored

The documented physical schema contains 17 columns. The most significant are listed below.

• PRJ_TYP_PK — Surrogate identifier for the project type record; the primary candidate for the warehouse key.
• PROJECT_TYPE — The business-facing project type name or classification value. This, together with PRJ_TYP_PK, forms the principal business-key candidate.
• NAME — Descriptive name of the staged project type row, typically aligned with the source lookup or base table value.
• ROW_ID — Staging row identifier; the sole documented foreign key column, referencing CS_SYSTEMS_ALL_B_TEMP, used by the collection framework to tie a staged row back to its collection batch.
• ALL_FK_KEY and ALL_FK — Composite surrogate and alternate key values used to resolve the record against the unified warehouse key structure.
• REQUEST_ID — The concurrent request that produced the staged row; essential for lineage and re-run diagnostics.
• COLLECTION_STATUS — Indicates the processing state of the row (for example, staged, validated, or rejected).
• ERROR_CODE — Populated when validation or load fails; drives exception reporting.
• OPERATION_CODE — Identifies the operation applied to the row (insert, update, delete), supporting delta processing.
• INSTANCE — Source instance identifier, allowing multi-instance consolidation.
• DELETION_DATE — Soft-delete marker; non-null values indicate rows logically retired from the source.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — Five extensible descriptive flexfield-style columns reserved for supplementary or client-specific attributes.

Common Use Cases and Queries

Typical usage centers on ETL monitoring, exception resolution, and reference-data validation. Analysts frequently query the table to inspect load outcomes for a specific concurrent request, or to isolate rows that failed validation.

• Load status by request: SELECT REQUEST_ID, COLLECTION_STATUS, COUNT(*) FROM FII.EDW_PROJ_PRJ_TYP_LSTG GROUP BY REQUEST_ID, COLLECTION_STATUS;
• Exception extraction: SELECT PRJ_TYP_PK, PROJECT_TYPE, ERROR_CODE FROM FII.EDW_PROJ_PRJ_TYP_LSTG WHERE ERROR_CODE IS NOT NULL;
• Active project type reference list: SELECT PRJ_TYP_PK, PROJECT_TYPE, NAME FROM FII.EDW_PROJ_PRJ_TYP_LSTG WHERE DELETION_DATE IS NULL;
• Operation-level delta audit: SELECT OPERATION_CODE, INSTANCE, COUNT(*) FROM FII.EDW_PROJ_PRJ_TYP_LSTG GROUP BY OPERATION_CODE, INSTANCE;

Related Objects

• CS_SYSTEMS_ALL_B_TEMP — Referenced through ROW_ID; provides the collection-system context for each staged row.
• EDW_PROJ_PRJ_LSTG — Companion project-level staging table that consumes project type data during dimension build.
• EDW_PROJ_PRJ_F — Target project fact/outrigger that resolves PROJECT_TYPE via PRJ_TYP_PK.
• PA_PROJECT_TYPES_ALL — Source-of-record for project type definitions in Oracle Projects.
• FND_CONCURRENT_REQUESTS — Joins on REQUEST_ID to obtain request name, phase, and completion status.
• FII ETL control and error-log tables — Consume COLLECTION_STATUS and ERROR_CODE for reconciliation reporting.