F4600

-- ============================================================
-- Table  : F4600 One row per requested warehouse movement — the demand side of WMS: a putaway, pick, or replenishment waiting for the system to decide where stock should move.
-- Purpose: Column-selected read of F4600 — auto-generated from field metadata
-- Grain  : One row per R1RQBT + R1RQSQ
-- 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.R1RQBT AS "Batch number grouping requests created in the same run; first half of the request key.",
  f.R1RQSQ / POWER(10, 3) AS "Line sequence of the request within its batch; second half of the request key.",  -- implied decimals: 3 (verify in F9210)
  f.R1TYFL AS "Movement type of the request: 1 = putaway, 2 = pick, 3 = replenishment. Hard-coded values, not UDC-validated — this is the field to split warehouse work by process.",
  f.R1OCDE AS "Code for where the request originated (manual entry, sales order shipping, PO receipt, replenishment run, etc.).",
  f.R1KCOO AS "Company portion of the originating order key.",
  f.R1DOCO AS "Originating order/document number (sales order, purchase order, work order) that drove this movement.",
  f.R1DCTO AS "Order type of the originating document.",
  f.R1LNID / POWER(10, 3) AS "Line number on the originating order this movement serves.",  -- implied decimals: 3 (verify in F9210)
  f.R1SFXO AS "Suffix portion of the originating order key.",
  f.R1AN8 AS "Address book number of the customer or supplier on the originating order.",
  f.R1SHAN AS "Ship-to address number on the originating order.",
  TRIM(f.R1MCU) AS "Branch/plant (warehouse) where the movement happens.",
  f.R1ITM AS "Short item number being moved.",
  f.R1LITM AS "Second (external) item number.",
  f.R1OGRP AS "Order group used to batch requests through process selection.",
  f.R1QTRQ AS "Quantity requested to move, in the item's primary UOM.",
  f.R1UOM AS "Unit of measure the request quantity was entered in.",
  f.R1LDFS AS "Sequence of the F4602 location-detail row stock moves from.",
  f.R1FLOC AS "Location stock moves from; blank on a putaway request until a suggestion fills it in.",
  f.R1FLOT AS "Lot/serial number moving from.",
  f.R1LPNUF AS "License plate stock moves from.",
  f.R1LDTS AS "Sequence of the F4602 location-detail row stock moves to.",
  f.R1TLOC AS "Location stock moves to; blank on pick/replenishment requests until suggested.",
  f.R1TLOT AS "Lot/serial number at the destination.",
  f.R1LPNUT AS "License plate at the destination.",
  f.R1PRIO AS "Processing priority of the request.",
  f.R1ZON AS "Delivery zone carried from the originating order.",
  f.R1COMM AS "Hard/soft commitment flag inherited from the order line.",
  f.R1LCOD AS "Location tax status code restricting which locations qualify.",
  f.R1PACK AS "Packing code controlling carton/repack handling.",
  f.R1PSTB AS "Lifecycle status of the request (UDC 46/PS; requests move through the 2xx range — created, suggested, confirmed, closed). The field to filter open warehouse work on.",
  CASE WHEN f.R1RCDJ IS NULL OR f.R1RCDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.R1RCDJ AS INT) DIV 1000, 1, 1), CAST(f.R1RCDJ AS INT) % 1000 - 1) END AS "Date the stock was received; drives FIFO-based putaway and pick sequencing.",  -- CYYDDD Julian → DATE
  CASE WHEN f.R1TRDJ IS NULL OR f.R1TRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.R1TRDJ AS INT) DIV 1000, 1, 1), CAST(f.R1TRDJ AS INT) % 1000 - 1) END AS "Date of the originating order or transaction.",  -- CYYDDD Julian → DATE
  CASE WHEN f.R1PDDJ IS NULL OR f.R1PDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.R1PDDJ AS INT) DIV 1000, 1, 1), CAST(f.R1PDDJ AS INT) % 1000 - 1) END AS "Scheduled pick date from the order.",  -- CYYDDD Julian → DATE
  CASE WHEN f.R1DRQJ IS NULL OR f.R1DRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.R1DRQJ AS INT) DIV 1000, 1, 1), CAST(f.R1DRQJ AS INT) % 1000 - 1) END AS "Requested date from the originating order.",  -- CYYDDD Julian → DATE
  f.R1CNFR AS "Flag to confirm suggestions automatically without operator review.",
  CASE WHEN f.R1DTCR IS NULL OR f.R1DTCR = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.R1DTCR AS INT) DIV 1000, 1, 1), CAST(f.R1DTCR AS INT) % 1000 - 1) END AS "Date the request was created.",  -- CYYDDD Julian → DATE
  f.R1UKID AS "Internal unique key; ties the request to its suggestions and tag records.",
  f.R1SHPN AS "Shipment number for outbound pick requests.",
  f.R1LDNM AS "Load number for outbound pick requests."
FROM <catalog>.<schema_data>.f4600 f
WHERE
  f.R1TRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.R1TRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.R1RQBT;