Order.item_dates stage

by

Order.item_dates_stage captures the order lifecycle timeline as a status-history “item” table. Each row represents a single lifecycle event (a status entry) for an order, with the event date carried as a meta-date value and the event details stored as JSON metadata.

Use it when you need to answer questions like:

  • How long do orders spend in each stage / gate?
  • Where are our bottlenecks (cycle time between gates)?
  • Which changes were manual interventions vs system-driven?
  • Who changed what, when, and with what message/note?

Grain and keys

This is an item view (multiple rows per order).

  • OBJECT_SEQ (Order key)
    JSON string: {"COMPANY_ID":"...","ORDER_NO":"..."}
  • ITEM_KEY (History event key)
    JSON string: {"HISTORY_SEQ":"<HISTORY_NO>"}
    This is what allows multiple status changes on the same day (or even the same minute) to remain distinct.
  • ITEM_TYPE
    Constant: LIFECYCLE

Core columns

ColumnMeaning
OBJECT_SEQOrder identifier (Company + Order No)
ITEM_KEYLifecycle event identifier (History No)
ITEM_TYPEAlways LIFECYCLE
ATTRIBUTECurrently STATUS (meta-date attribute name)
CODE_VALUEEvent date (YYYY-MM-DD) parsed from hist.DATE_ENTERED
VALUE_METADATAJSON payload with event context (time, user, notes, state, origin)
CREATE_DATELoad date (GETDATE() as date)
EXPIRY_DATEHard-coded 2099-12-31 (no expiry logic applied here)
SOURCE_SYSTEM"ifs.QFACT_CUSTOMER_ORDER_CFV_OL" (as populated)
ACTIVE_FLAG1
CHECKSUMMD5 of OBJECT_SEQ + ITEM_KEY + ATTRIBUTE + CODE_VALUE

Metadata payload shape (VALUE_METADATA)

For each lifecycle event, VALUE_METADATA is emitted like:

{
  "TIME_ENTERED": "HH:MM:SS",
  "USER_ID": "…",
  "NOTES": "…",
  "STATE": "…",
  "ORIGIN": "System|Manual"
}

How origin is inferred

ORIGIN is derived as:

  • System when hist.HIST_STATE = hist.MESSAGE_TEXT
  • Manual otherwise

This is a useful heuristic, but it’s still a heuristic — treat it as “best-efforts” unless you have an explicit source flag in the history table.

Source and lineage

Source tables

  • ifs.QFACT_CUSTOMER_ORDER_CFV_OL (order header / current fact view)
  • ifs.QFACT_CUSTOMER_ORDER_HISTO_OL (order history)

Join logic

  • Joined by src.ORDER_NO = hist.ORDER_NO

Note: If order numbers are not globally unique across companies in your estate, it’s safer to join on both COMPANY + ORDER_NO (or the native history key that includes company).

Leave a Comment