F4311

-- ============================================================
-- Table  : F4311 Purchase order detail - one row per PO line with item, branch, quantities, unit/extended costs, and the status flow
-- Purpose: Column-selected read of F4311 — auto-generated from field metadata
-- Grain  : One row per PDKCOO + PDDOCO + PDDCTO + PDSFXO + PDLNID
-- 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
  d.PDKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  d.PDDOCO AS "Order number",
  d.PDDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  d.PDSFXO AS "Order suffix distinguishing multiple documents under one order number",
  d.PDLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  TRIM(d.PDMCU) AS "Branch/plant business unit; right-justified, space-padded to 12",
  d.PDCO AS "Company code",
  d.PDOKCO AS "Company key of the originating order",
  d.PDOORN AS "Order number of the originating document this record came from",
  d.PDOCTO AS "Document type of the originating order",
  d.PDRKCO AS "Company key of the related order",
  d.PDRORN AS "Cross-referenced related order number (PO, SO, or work order)",
  d.PDRCTO AS "Document type of the related order",
  d.PDAN8 AS "Supplier address book number",
  d.PDSHAN AS "Ship-to address book number",
  d.PDANBY AS "Buyer address book number",
  d.PDITM AS "Short internal item number - joins to F4101/F4102",
  d.PDLITM AS "Second item number, the long code users normally key",
  d.PDAITM AS "Third item number (catalog or alternate identifier)",
  d.PDLOCN AS "Warehouse storage location",
  d.PDLOTN AS "Lot or serial number",
  d.PDDSC1 AS "Line description",
  d.PDLNTY AS "Line type controlling G/L, inventory, and A/P-A/R interfaces",
  d.PDLTTR AS "Last completed status in the order activity flow",
  d.PDNXTR AS "Next expected status in the order activity flow",
  CASE WHEN d.PDTRDJ IS NULL OR d.PDTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDTRDJ AS INT) DIV 1000, 1, 1), CAST(d.PDTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN d.PDDRQJ IS NULL OR d.PDDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDDRQJ AS INT) DIV 1000, 1, 1), CAST(d.PDDRQJ AS INT) % 1000 - 1) END AS "Requested date",  -- CYYDDD Julian → DATE
  CASE WHEN d.PDPDDJ IS NULL OR d.PDPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDPDDJ AS INT) DIV 1000, 1, 1), CAST(d.PDPDDJ AS INT) % 1000 - 1) END AS "Scheduled/promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN d.PDOPDJ IS NULL OR d.PDOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDOPDJ AS INT) DIV 1000, 1, 1), CAST(d.PDOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN d.PDADDJ IS NULL OR d.PDADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDADDJ AS INT) DIV 1000, 1, 1), CAST(d.PDADDJ AS INT) % 1000 - 1) END AS "Actual date goods shipped or were delivered",  -- CYYDDD Julian → DATE
  CASE WHEN d.PDDGL IS NULL OR d.PDDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.PDDGL AS INT) DIV 1000, 1, 1), CAST(d.PDDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  d.PDUOM AS "Transaction unit of measure",
  d.PDUORG AS "Ordered quantity in the transaction unit of measure",
  d.PDUOPN AS "Open quantity not yet received or shipped",
  d.PDUREC AS "Quantity received to date",
  d.PDUCHG AS "Quantity currently on hold",
  d.PDPRRC / POWER(10, 4) AS "Unit cost in domestic currency (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  d.PDAEXP / POWER(10, 2) AS "Extended price of the line in domestic currency",  -- implied decimals: 2 (verify in F9210)
  d.PDAOPN / POWER(10, 2) AS "Open amount remaining on the line",  -- implied decimals: 2 (verify in F9210)
  d.PDAREC / POWER(10, 2) AS "Amount received to date"  -- implied decimals: 2 (verify in F9210)
  -- … plus 8 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f4311 d
WHERE
  d.PDKCOO = '<KCOO>'
  -- AND d.PDDOCO = <DOCO>
  -- AND d.PDDCTO = '<DCTO>'
  -- AND d.PDTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND d.PDTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY d.PDKCOO;