Results for “cst_pac_eam_wo_est_statuses”

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

Overview

CST_PAC_EAM_WO_EST_STATUSES is a transactional tracking table owned by the BOM schema in Oracle E-Business Suite (validated in both 12.1.1 and 12.2.2). Its documented description — "eAM workOrder Estimation Status for PAC Cost Type" — identifies its purpose: it records the estimation state of Enterprise Asset Management (eAM) work orders as processed by the Period Average Cost (PAC) cost type. In practice, the table acts as a coordination point between the maintenance execution world (work orders created in eAM) and the costing engine that must estimate and periodically roll up those work order costs under a PAC cost type. The CST_PAC_* prefix, despite the BOM ownership, indicates it is consumed primarily by the Cost Management (CST) programs that drive Period Average Costing.

Under the heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link table. It sits at the intersection of several reference entities — organization, cost type, legal entity, cost group, and WIP entity — and its rows represent the association of a specific work order with a specific costing context plus the resulting estimation status. Treating it as a link rather than a hub or satellite aligns with the absence of a natural business key and the presence of multiple incoming foreign-key relationships.

Key Information Stored

The documented physical schema contains twelve columns. The most operationally significant are:

• ORGANIZATION_ID — the inventory/maintenance organization context; foreign key to MTL_PARAMETERS.
• WIP_ENTITY_ID — the eAM work order (WIP entity) being tracked. This is the primary business identifier alongside organization and cost type.
• COST_TYPE_ID — foreign key to CST_COST_TYPES, identifying the PAC cost type under which estimation is performed.
• COST_GROUP_ID — foreign key to CST_COST_GROUPS, the cost group assigned to the estimation.
• LEGAL_ENTITY_ID — foreign key to FV_LEGAL_ENTITIES, the legal entity used for accounting/period context.
• ESTIMATION_STATUS — the state flag describing where the work order stands in the estimation lifecycle (e.g., pending, estimated, error).
• LAST_ESTIMATION_REQ_ID — the request ID of the concurrent program execution that last performed estimation.
• LAST_ESTIMATION_DATE — timestamp of the most recent estimation pass.
• Audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY.

The metadata does not document a single-column surrogate primary key or named unique index. The practical business-key candidate is the composite of ORGANIZATION_ID + WIP_ENTITY_ID + COST_TYPE_ID, since estimation status is qualified by all three. Because no PK is documented, referential integrity is enforced through the four foreign keys rather than an intrinsic row identifier.

Common Use Cases and Queries

Typical scenarios include auditing which work orders failed estimation, monitoring the freshness of estimation for a period close, and reconciling estimation request IDs back to concurrent program logs.

A representative query identifying unestimated or stale work orders:

• Select WIP_ENTITY_ID, COST_TYPE_ID, ESTIMATION_STATUS, LAST_ESTIMATION_DATE from CST_PAC_EAM_WO_EST_STATUSES where ORGANIZATION_ID equals the target org and ESTIMATION_STATUS is not the completed value.
• Join to CST_COST_TYPES on COST_TYPE_ID to translate the cost type into a readable name for reporting.
• Filter on LAST_ESTIMATION_DATE to surface records untouched during the current accounting period.

For period-close dashboards, aggregating counts by ESTIMATION_STATUS per LEGAL_ENTITY_ID provides a quick health check of the PAC estimation workload before cost rollup.

Related Objects

• MTL_PARAMETERS — joined on ORGANIZATION_ID; supplies organization defaults.
• CST_COST_TYPES — joined on COST_TYPE_ID; defines the PAC cost type.
• CST_COST_GROUPS — joined on COST_GROUP_ID; resolves the estimation cost group.
• FV_LEGAL_ENTITIES — joined on LEGAL_ENTITY_ID; provides legal-entity accounting context.
• WIP/eAM work order tables (e.g., WIP_ENTITIES) — related through WIP_ENTITY_ID to retrieve work order detail.

These relationships confirm the table's role as a lightweight status link reconciling eAM maintenance work orders with Period Average Costing.