InventBatch

-- ============================================================
-- Table  : InventBatch Batch master — one row per item and batch number, carrying expiry and manufacturing dates plus vendor batch details; the record behind the inventBatchId dimension
-- Purpose: Column-selected read of InventBatch — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + inventBatchId + itemId
-- 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.inventbatchid AS "The batch number — unique within the item, referenced by InventDim.inventBatchId",
  i.itemid AS "Item the batch belongs to — batch numbers are scoped to an item, so joins need both columns",
  i.description AS "Free-text description of the batch",
  CASE WHEN CAST(i.expdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.expdate AS DATE) END AS "Expiration date of the batch; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(i.proddate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.proddate AS DATE) END AS "Manufacturing date of the batch; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(i.pdsbestbeforedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.pdsbestbeforedate AS DATE) END AS "Best-before date (process manufacturing); unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(i.pdsshelfadvicedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.pdsshelfadvicedate AS DATE) END AS "Shelf-advice date (process manufacturing); unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  i.pdsdispositioncode AS "Batch disposition code controlling whether the batch is usable, blocked, or restricted",
  i.pdsvendbatchid AS "The vendor's own batch number for a purchased batch",
  CASE WHEN CAST(i.pdsvendbatchdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.pdsvendbatchdate AS DATE) END AS "Vendor batch (manufacturing) date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(i.pdsvendexpirydate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.pdsvendexpirydate AS DATE) END AS "Vendor expiry date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  i.pdscountryoforigin1 AS "Country of origin recorded for the batch",
  CASE WHEN CAST(i.pdsfinishedgoodsdatetested AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.pdsfinishedgoodsdatetested AS DATE) END AS "Date the finished-goods batch was tested; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  i.manufacturerid AS "RecId of the manufacturer's global-address-book party in DirPartyTable"
FROM <catalog>.<schema>.inventbatch i
-- Resolve DirPartyTable (via ManufacturerId): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dirpartytable dir ON dir.recid = i.manufacturerid
WHERE
  i.dataareaid = '<company>'
  -- AND i.itemid = '<ItemId>'
ORDER BY i.inventbatchid;