F4602

-- ============================================================
-- Table  : F4602 Warehouse-managed contents of each location: one row per item/lot/UOM-structure detail with on-hand, committed, inbound/outbound quantities, and the cube and weight consumed.
-- Purpose: Column-selected read of F4602 — auto-generated from field metadata
-- Grain  : One row per IFITM + IFMCU + IFLOCN + IFLOTN + IFLDSQ
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  f.IFITM AS "Short item number stored in the location.",
  TRIM(f.IFMCU) AS "Branch/plant (warehouse) the location belongs to.",
  f.IFLOCN AS "Warehouse location (bin) this row describes.",
  f.IFLOTN AS "Lot/serial number of the stock in the location.",
  f.IFLDSQ AS "Sequence distinguishing multiple detail rows for the same item/location/lot — the same key F4600/F4611 carry in their from/to sequence fields.",
  f.IFSTUN AS "Storage unit number identifying a pallet/license plate within the location.",
  f.IFUML1 AS "Unit of measure at structure level 1 (the largest, e.g., pallet).",
  f.IFEUL1 AS "How the level-1 UOM is used for capacity and container logic.",
  f.IFPPL1 AS "Quantity of one level-1 unit expressed in primary UOM.",
  f.IFTQL1 AS "Total quantity held at structure level 1.",
  f.IFEQL1 AS "Container code at level 1.",
  f.IFCPL1 / POWER(10, 2) AS "Cube per level-1 unit.",  -- implied decimals: 2 (verify in F9210)
  f.IFEPL1 / POWER(10, 2) AS "Weight per level-1 unit.",  -- implied decimals: 2 (verify in F9210)
  f.IFUOM1 AS "Primary unit of measure of the item.",
  f.IFTQTY AS "Total quantity on hand in this location-detail row, primary UOM.",
  f.IFHCOM AS "Quantity hard-committed to open pick suggestions.",
  f.IFQTRI AS "Quantity inbound to this location on open putaway/replenishment suggestions.",
  f.IFQTRO AS "Quantity outbound on open pick suggestions.",
  f.IFCUBE / POWER(10, 2) AS "Cubic volume this row occupies in the location.",  -- implied decimals: 2 (verify in F9210)
  f.IFRCUB / POWER(10, 2) AS "Cube reserved by open suggestions not yet confirmed.",  -- implied decimals: 2 (verify in F9210)
  f.IFVUMD AS "UOM used to display volume figures.",
  f.IFWEIT / POWER(10, 2) AS "Weight this row places on the location.",  -- implied decimals: 2 (verify in F9210)
  f.IFRWET / POWER(10, 2) AS "Weight reserved by open suggestions.",  -- implied decimals: 2 (verify in F9210)
  f.IFUWUM AS "UOM used for weight figures.",
  CASE WHEN f.IFRCDJ IS NULL OR f.IFRCDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IFRCDJ AS INT) DIV 1000, 1, 1), CAST(f.IFRCDJ AS INT) % 1000 - 1) END AS "Date the stock was received into the location; the FIFO driver.",  -- CYYDDD Julian → DATE
  CASE WHEN f.IFMMEJ IS NULL OR f.IFMMEJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IFMMEJ AS INT) DIV 1000, 1, 1), CAST(f.IFMMEJ AS INT) % 1000 - 1) END AS "Lot expiration date.",  -- CYYDDD Julian → DATE
  f.IFDOCO AS "Order number of the document that last touched this detail row.",
  f.IFDCTO AS "Order type of that document.",
  f.IFKCOO AS "Company portion of that order key.",
  f.IFLNID / POWER(10, 3) AS "Line number of that order.",  -- implied decimals: 3 (verify in F9210)
  f.IFUOM2 AS "Secondary UOM for dual-UOM items.",
  f.IFDQTY AS "Quantity in secondary UOM for dual-UOM items."
FROM <catalog>.<schema_data>.f4602 f
WHERE
  f.IFITM = <ITM>
  -- AND f.IFMCU = '<MCU>'
ORDER BY f.IFITM;