Search Results assay_pk_rowid

Overview

The view EDW_PMI_PASY_PASY_LCV is a reporting and integration construct belonging to the Process Manufacturing Intelligence (PMI) product family within Oracle E-Business Suite. The suffix _LCV denotes a "Local Currency View" style interface object, part of the Enterprise Data Warehouse (EDW) extraction layer that feeds PMI analytics, dashboards, and downstream operational reporting. In EBS 12.1.1 and 12.2.2, such views act as denormalized, publication-ready projections of operational data, shielding extract processes from the physical schema of the transactional tables.

The view exposes quality assay type definitions from Process Manufacturing Quality Management, joining the assay definition to the local OPM (Oracle Process Manufacturing) instance. Its role is to present assay metadata in a flat, query-friendly structure suitable for ETL into the EDW staging area. The presence of the EDW_ prefix, the surrogate ALL_FK literal column, and the placeholder USER_ATTRIBUTE columns confirms its ETL staging purpose: it normalizes keys and reserves attribute slots for extension columns in the target warehouse model.

The object is documented as obsolete, and the metadata explicitly states it is "Not implemented in this database." It should therefore be treated as a historical artifact of the PMI schema rather than an active interface in current releases.

Underlying Base Objects

The view is defined over two documented base objects:

• QC_ASSY_TYP (aliased ASSAY) — the OPM Quality Management assay type definition table, supplying assay class, code, description, organization code, and audit dates.
• EDW_LOCAL_INSTANCE (aliased INST) — the EDW local-instance reference table, supplying the INSTANCE_CODE that identifies the specific OPM instance from which data is sourced.

No other base objects are documented in the ETRM metadata. The join between the two tables is expressed as a cartesian-style selection in the documented view text, which combines each assay definition with the local instance record to produce a single instance-qualified row per assay.

Key Columns

• ALL_FK — a literal 'ALL' value, used as a constant conformed dimension key for the "all instances" aggregate.
• ASSAY_CLASS — the classification of the assay type.
• ASSAY_CODE — the assay type identifier; exposed twice, once as the raw code and once as NAME.
• ASSAY_DP — a composite display key concatenating organization code, assay code, instance code, and the literal 'OPM'.
• ASSAY_PK — the primary-key expression, formed as ORGN_CODE||':'||ASSAY_CODE.
• ASSAY_PK_ROWID — a placeholder column emitted as NULL, reserved for a rowid-based surrogate key but never populated.
• CREATION_DATE, LAST_UPDATE_DATE — audit timestamps inherited from QC_ASSY_TYP.
• DESCRIPTION — assay description text.
• INSTANCE_CODE — the OPM instance identifier from EDW_LOCAL_INSTANCE.
• ORGANIZATION — the operating organization code.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — five reserved descriptor columns, all emitted as NULL, intended as extension slots for the warehouse model.

Common Use Cases and Queries

The view supports assay-dimension extracts for PMI quality analytics. The user search term assay_pk_rowid corresponds directly to the ASSAY_PK_ROWID column. Because that column is hard-coded to NULL, any query filtering or joining on it will return no rows or produce null-key joins — a common source of confusion when this obsolete view is mistaken for an active interface.

A typical extract to enumerate available assay types per instance:

SELECT assay_pk, assay_class, assay_code, name, description,
       instance_code, organization
FROM   edw_pmi_pasy_pasy_lcv
WHERE  organization = :org_code;

To confirm the key strategy before relying on rowid semantics:

SELECT assay_pk, assay_pk_rowid, assay_dp
FROM   edw_pmi_pasy_pasy_lcv;

In practice, implementations on 12.1.1 or 12.2.2 should validate whether this view exists in their schema before use; the ETRM metadata records it as obsolete and not implemented, so equivalent data is normally obtained directly from QC_ASSY_TYP or from the current PMI extraction views.