Results for “lm_sprd_dtl_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

LM_SPRD_DTL is a table in the GMD (Process Manufacturing Product Development) schema that stores Lab Spreadsheet detail records. In Oracle EBS 12.1.1 and 12.2.2, it functions as the transactional line-level store for laboratory spreadsheet data, holding individual detail rows that belong to a lab spreadsheet header. Oracle's ETRM documentation classifies the table owner as GMD and its status as VALID, with 44 documented columns and a single unique index, LM_SPRD_DTL_PK, on LINE_ID.

Applying the heuristic Data Vault classification supplied in the metadata, LM_SPRD_DTL is modeled as a link. This is consistent with its structure: it is dominated by foreign key references to master and header entities (IC_ITEM_MST_B, IC_ITEM_MST, LM_LTYP_MST, LM_SPRD_FLS, LM_TEXT_HDR, SY_UOMS_MST, and MTL_RESERVATIONS) rather than by descriptive attributes alone. The link classification should be treated as a modeling suggestion, useful when mapping this table into an analytical or integration model, rather than as an Oracle-defined designation.

Key Information Stored

The surrogate primary key is LINE_ID, defined through the unique index LM_SPRD_DTL_PK. There is no separate documented business-key unique index beyond this primary key, so referential joins in practice are driven through the foreign key columns.

• SPRD_ID — foreign key to LM_SPRD_FLS, identifying the lab spreadsheet header to which the detail line belongs.
• LINE_ID — surrogate primary key and the unique identifier of the spreadsheet detail row.
• LINE_NO and LINE_TYPE — sequence and category of the detail line within its parent spreadsheet.
• LAB_TYPE — foreign key to LM_LTYP_MST, indicating the laboratory type or test classification.
• ITEM_ID — foreign key to IC_ITEM_MST and IC_ITEM_MST_B, identifying the item associated with the line.
• QTY, SECONDARY_QTY, and PROD_PERCENT — primary quantity, secondary quantity, and percentage component for the line.
• ITEM_UM, SECONDARY_UM, and DETAIL_UOM — units of measure; ITEM_UM is a foreign key to SY_UOMS_MST.
• LOT_ID, LOT_NUMBER, EXPIRATION_DATE, and QC_GRADE — lot and quality information for the referenced material.
• TEXT_CODE — foreign key to LM_TEXT_HDR, linking the line to associated text or notes.
• TPFORMULA_ID and FORMULALINE_ID — references tying the line back to formula and formula-line definitions.
• ORGANIZATION_ID, INVENTORY_ITEM_ID, REVISION, SUBINVENTORY_CODE, LOCATOR_ID, and WHSE_CODE — inventory and warehouse context for the line.
• RESERVATION_ID and TRANSACTION_ID — foreign key to MTL_RESERVATIONS and transactional reference columns.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, and CREATED_BY — standard Oracle who-columns for audit and incremental extraction.

Common Use Cases and Queries

LM_SPRD_DTL is primarily queried in the context of lab spreadsheet maintenance and reporting. A typical pattern joins the detail to its spreadsheet header and item master:

• Retrieve all detail lines for a spreadsheet: SELECT * FROM GMD.LM_SPRD_DTL WHERE SPRD_ID = :p_sprd_id ORDER BY LINE_NO;
• Enrich lines with item descriptions: join ITEM_ID to IC_ITEM_MST on ITEM_ID to display item number and description.
• Resolve laboratory type descriptions: join LAB_TYPE to LM_LTYP_MST to report the lab type for each line.
• Standard unit of measure reporting: join ITEM_UM to SY_UOMS_MST to obtain the UOM description.
• Incremental extraction for data warehousing: filter on LAST_UPDATE_DATE using a high-water-mark parameter to load changed detail rows.
• Reconciliation of reserved or transacted material: join RESERVATION_ID to MTL_RESERVATIONS to relate detail lines to reservations.

Reporting use cases include spreadsheet content reports, formula-to-line traceability, lot and grade analysis, and quantity reconciliation across primary and secondary UOMs.

Related Objects

• LM_SPRD_FLS — spreadsheet header; join on LM_SPRD_DTL.SPRD_ID = LM_SPRD_FLS.SPRD_ID.
• IC_ITEM_MST and IC_ITEM_MST_B — item master entities; join on ITEM_ID.
• LM_LTYP_MST — laboratory type master; join on LAB_TYPE.
• LM_TEXT_HDR — text header; join on TEXT_CODE.
• SY_UOMS_MST — unit of measure master; join on ITEM_UM.
• MTL_RESERVATIONS — inventory reservations; join on RESERVATION_ID.

These relationships reflect the documented foreign keys, and each join should be filtered by the appropriate organization or spreadsheet context to avoid cross-context data leakage in multi-organization implementations.