InventItemPrice

-- ============================================================
-- Table  : InventItemPrice Item price records — activated cost, purchase, and sales prices per item, costing version, and dimension; the source for standard-cost analysis
-- Purpose: Column-selected read of InventItemPrice — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + ItemId + VersionId
-- Notes  : Auto-generated skeleton for Synapse Link / Fabric Link-landed F&O data (lowercase column names). Enums decoded inline where verified; datetimes stored in UTC; 1900-01-01 dates are sentinels mapped to NULL. System/audit columns omitted — see the quirks guide.
-- ============================================================
SELECT
  i.itemid AS "Item the price record is for",
  i.versionid AS "Costing version the price was activated under",
  i.costingtype AS "Which price this record carries — cost, purchase price, or sales price",  -- enum: decode i.costingtype via GlobalOptionsetMetadata join — see quirks guide #enums
  i.activationdate AS "Date the price became active — the primary analysis date for cost history",
  i.price AS "The activated price amount",
  i.priceunit AS "Number of units the price applies to (price per 1, 100, 1000…) — divide before comparing",
  i.priceqty AS "Default order quantity used when the price was calculated",
  i.markup AS "Charge amount included on top of the price",
  i.priceallocatemarkup AS "Whether the charge is spread over the price quantity",  -- enum: decode i.priceallocatemarkup via GlobalOptionsetMetadata join — see quirks guide #enums
  i.pricetype AS "Price model behind the record (standard cost vs planned cost calculation)",  -- enum: decode i.pricetype via GlobalOptionsetMetadata join — see quirks guide #enums
  i.pricecalcid AS "BOM calculation the price came from, when it was calculated rather than entered",
  CASE WHEN CAST(i.stdcosttransdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.stdcosttransdate AS DATE) END AS "Date the standard-cost revaluation posted; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  i.stdcostvoucher AS "Voucher of the standard-cost revaluation posting",
  i.unitid AS "Unit of measure the price is expressed in",
  i.inventdimid AS "Dimension combination the price is specific to (typically the site); join to InventDim"
FROM <catalog>.<schema>.inventitemprice i
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = i.inventdimid AND id.dataareaid = i.dataareaid
WHERE
  i.dataareaid = '<company>'
  -- AND i.itemid = '<ItemId>'
  -- AND i.activationdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND i.activationdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY i.itemid;