Search Results assay_dp

Overview

EDW_PMI_PASY_PASY_LTC_IV is a reporting view within the Oracle EBS Process Manufacturing Intelligence (PMI) module. PMI is the analytical and data-warehouse-oriented layer that ships with Oracle Process Manufacturing (OPM), and its views are designed to expose cleaned, denormalized, and consistently named data to downstream reporting, extract-transform-load (ETL), and business intelligence consumers. The suffix _IV conventionally denotes an "interface view" — an intermediate object intended to flatten source structures and present a stable column contract for external or warehouse-bound consumption rather than for transactional processing.

In the EBS 12.1.1 and 12.2.2 release contexts, objects of this naming pattern are typically deployed by the PMI/EDW patch family and are catalogued under the PMI product. Importantly, the ETRM metadata records this object as "Not implemented in this database" in the documented environment, meaning the view definition exists in the PMI data model but is not created in every EBS instance. The presence of the view is therefore a function of whether the PMI warehouse components were installed and patched on that specific database.

Underlying Base Objects

The documented view text selects from a single source object: EDW_PMI_PASY_PASY_LTC. The view is a thin projection layer over that table, largely restating and lightly aliasing its columns rather than joining additional tables. This one-to-one orientation is consistent with the "_IV" interface-view pattern, where a base EDW staging table is re-exposed with a stable, consumer-facing column list.

The naming convention decomposes as follows: EDW identifies the Enterprise Data Warehouse prefix used across PMI objects; PMI denotes the product; and PASY refers to the Process Analytical/Laboratory subsystem domain that stores assay (quality specification) definitions. The base table EDW_PMI_PASY_PASY_LTC thus serves as the assay-master staging structure for the laboratory and quality module. No other referenced base objects are documented, so the view's lineage terminates at that single staging table.

Key Columns

• ASSAY_CODE — The business identifier for an assay definition. This is the column most commonly used as a search or join key when locating quality specifications, and it is the term associated with the user's query. In OPM, assay codes identify the quality test or specification grouping assigned to items and organizations.
• ASSAY_CLASS — Classifies the assay, supporting segregation of quality tests into logical groupings such as chemical, physical, or microbiological categories.
• ASSAY_DP — The assay data point, representing an attribute or detail record associated with the assay definition.
• ASSAY_PK / ASSAY_PK_KEY / ASSAY_PK_ROWID (ROW_ID) — Primary-key components and the rowid of the source assay record, enabling reliable joins back to the operational tables and change detection in incremental loads.
• ALL_FK / ALL_FK_KEY — Foreign-key surrogate and key columns, used to relate the assay record to parent entities within the warehouse model.
• DESCRIPTION and NAME — Human-readable identifiers for the assay or assay class, supporting report labeling.
• INSTANCE_CODE and ORGANIZATION — Instance and organization context, critical for multi-org and multi-instance consolidation in the warehouse.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — The standard Oracle descriptive-flexfield columns, exposed for customer-specific extensions.
• OPERATION_CODE — Aliased from USER_ATTRIBUTE5 in the view text; distinguishes the change or operation type, which drives incremental ETL logic (insert, update, delete detection).

Common Use Cases and Queries

The primary use case is populating or refreshing the PMI analytical layer with assay master data, and answering quality-reporting questions that require assay codes by organization. Because the view flattens the staging table and supplies surrogate and row-identifier keys, it is well suited to incremental extraction.

A representative query retrieves all assays for a given organization and operation context:

• SELECT ASSAY_CODE, ASSAY_CLASS, DESCRIPTION, ORGANIZATION, OPERATION_CODE FROM EDW_PMI_PASY_PASY_LTC_IV WHERE ORGANIZATION = :org_id AND ASSAY_CODE = :assay_code;
• SELECT ASSAY_PK, ASSAY_PK_KEY, ROW_ID, NAME FROM EDW_PMI_PASY_PASY_LTC_IV WHERE OPERATION_CODE = 'U';

Practitioners integrating PMI should confirm that the view exists in their instance, since the ETRM metadata indicates it is not universally implemented. When present, it should be treated as a read-only interface object; all maintenance belongs to the underlying EDW_PMI_PASY_PASY_LTC table and the PMI load processes.