OTIF — On Time In Full

One order line, three questions: did the goods move on time (against a date somebody committed to), in full (the complete quantity, not most of it), and does the line count at all (or was it canceled out of the denominator). A line passes OTIF only when it passes the date test and the quantity test together — jointly, on the same line, not as two separate percentages that get multiplied later.

Also answers to OTIF · DIFOT (delivered in full, on time) · OTIF-D — same joint test, different acronym communities.

The core thing to internalize: there is no standard OTIF. Every definition in the wild is a configuration of six decisions — grain, date basis, ship vs arrival, first attempt vs cumulative, tolerance, and exclusions. Change one switch and the same month of shipments moves by double digits. That makes the number meaningless in isolation and only comparable to itself, computed identically over time. It also makes "our OTIF is 94%" an incomplete sentence until the six settings are stated.

Three metrics get conflated under this banner, and they are not the same measure:

• Fill rate tests quantity only — shipped over ordered, usually cumulative, with no time test at all. A line that ships complete three weeks late has a 100% fill rate.
• OTD (on-time delivery) tests time only. A line that ships one unit of a hundred on the promised date is on time.
• OTIF tests both, jointly, at a declared grain. Under matching definitions it can never exceed either component: OTIF ≤ min(fill rate, OTD).

The trap. Dashboards that compute on_time_pct × in_full_pct are assuming the two failure modes are independent. They never are — late lines are disproportionately also short lines — so the product misstates the joint metric, and there is no correction factor that fixes it. Compute the joint pass flag per line and aggregate the flag. If a scorecard shows OTIF above either of its own components, the joint test was never computed.

Every OTIF definition — yours, your customer's, the one in the board deck — is a setting of these six switches. Each links to its own section below.

The practice default, stated once: line grain, first-confirmed date, arrival basis where the customer measures arrival, first-attempt, zero-early/zero-late window unless contracted, cancels excluded and returns tracked separately. And one override that beats all of it: if a customer scores you, their scorecard is the definition — model their configuration first and argue with it second.

The grain decides what one "pass" means. Ten orders, forty lines, five hundred units; four orders carry six problem lines totalling twenty units. The same month scores 60% at order grain (six of ten orders fully clean), 85% at line grain (thirty-four of forty lines pass), and 96% unit-weighted (four hundred eighty of five hundred units on passing lines). Nobody lied — the switch moved.

Order grain is the customer's experience of the order — and it structurally punishes large orders, since one short line fails twenty clean ones. Unit or case weighting is what retail compliance programs effectively apply (charges scale with the non-compliant volume), and it smooths order-size mix out of trend lines. Line grain is where the data lives: the sales line is the grain of VBAP, F4211, SalesLine, and OOLINE alike — compute there and both other grains are roll-ups of the same fact.

"On time" against what date? Four candidates, and they tell different stories: the date the customer asked for, the date you first committed to, the date you currently promise (after every reschedule), and the date the customer requires goods to arrive by (MABD — theirs, not yours).

JD Edwards is the clearest teaching example because it keeps all three of its dates side by side on the sales line: SDDRQJ (the customer's requested date), SDOPDJ (the original promised date — JDE keeps it precisely for on-time measurement), and SDPPDJ (the current promise, updated as the order reschedules). Measure against SDPPDJand every reschedule launders lateness: the order that slipped three times arrives "on time" against its third promise.

The other systems split the same idea across tables. SAP: VBAK.VDATU is the requested date; the first confirmed date lives on the VBEP schedule lines — the earliest schedule line with a confirmed quantity (BMENG> 0) carries the confirmed date in EDATU, with WMENG as the ordered quantity beside it. D365 makes the switch a column swap: SalesLine.ShippingDateRequested vs ShippingDateConfirmed on the ship side, ReceiptDateRequested (line) vs SalesTable.ReceiptDateConfirmed (header) on the arrival side. M3 splits by level: OOHEAD.OARLDT is the header-level requested date, OOLINE.OBDWDTthe line's planned delivery date.

"On time" measured where? Ship basis compares the date goods left the dock; arrival basis compares the date they reached the customer. The gap between the two is transit time — and retail compliance fines live in that gap: a shipment that left on schedule and sat two days at a carrier hub fails the customer's arrival test while passing your ship test.

Where each lives. Ship basis is well-recorded everywhere: SAP posts actual goods issue in LIKP.WADAT_IST; JDE writes SDADDJat ship confirm; D365's warehouse-true timestamp is WHSShipmentTable.ShipConfirmUTCDateTime; M3 issues stock against the delivery. Arrival basis is where ERPs get thin, because arrival happens off your systems:

• SAP: LIKP.LFDAT is the planned customer-arrival date — the promise side of an arrival test. The actual side needs proof-of-delivery data (POD confirmation or carrier events), which base SD does not capture for you.
• JDE: F4201.SHADLJ is the header-level actual delivery date — note the grain mismatch against line-level measurement, and that it stays blank unless transportation or delivery confirmation actually populates it. The richer path is F4941 shipment routing steps, which carry scheduled vs actual delivery timestamps per leg, joined to order lines through F4942 and anchored on the F4215 shipment header.
• D365: SalesTable.ReceiptDateConfirmed carries the promised arrival; actual arrival needs POD or carrier integration, same as SAP.
• M3: deliveries ride the MHDISH / MHDISLstructure; this reference's curated field set carries no verified actual-departure or arrival timestamp, so the M3 SQL below states its proxy explicitly.

If the customer scores arrival and you can only measure ship, say so on the dashboard — an unlabeled ship-basis OTIF quietly overstates performance by the transit time.

When a line ships in pieces, which piece gets judged? Splits are not an edge case — every ERP models them as first-class records: LIPS writes one row per delivery split against the same order line (VGBEL/VGPOS), JDE spawns suffix records (SDSFXO) and tracks the backordered remainder in SDSOBK, M3 subdivides lines with OBPOSX and writes one MHDISL row per delivery, and D365 posts multiple packing-slip lines against one sales line.

First-attempt is the discipline metric — it asks whether the promise was kept as made. Eventually-complete is nearly information-free: with enough months, almost everything eventually ships. Cumulative-by-cutoff ("complete within the tolerance window") is the honest middle for operational review. Whichever you pick, publish the split-shipment rate next to OTIF — the SQL below carries a split_line_pct column for exactly that.

How close is close enough? A zero-early/zero-late window is the strictest reading of the promise. Grace windows (ship within three days early, zero days late) are common — and every grace day quietly raises the score, so the window belongs in the metric's label, not in a footnote. Delivery-window programs flip the intuition: under a must-arrive-by regime, early is also a failure, because the receiving DC plans labor and dock capacity against the window.

Two mechanical traps once windows get tight. Time of day: a date-grain comparison calls 11:59 PM on the due date on-time — fine, as long as it's deliberate. Timezone: D365 stores datetimes in UTC, so WHSShipmentTable.ShipConfirmUTCDateTime must go through from_utc_timestamp()before its date is taken — otherwise every shipment confirmed after 4–5 PM Pacific lands on tomorrow's date and fails a zero-window test it actually passed (see the UTC datetimes quirk).

What leaves the denominator decides what the number can hide. Three populations to rule on:

• Canceled lines. Standard practice excludes them — a canceled ask is no longer a promise — but track the cancel rate beside OTIF, because cancellation is also how demand quietly exits a struggling order book. The markers: SAP VBAP.ABGRU(rejection reason set = rejected), JDE's canceled quantity SDSOCN with its cancel date SDCNDJ, D365 SalesStatus = 4 (Canceled), M3's OBORST = '99' — completed without delivery.
• Returns. A line that delivered on time, in full, and came back a week later passed OTIF — the fulfillment promise was kept; the product failed some other promise. Exclude returns from OTIF and measure the return rate as its own metric.
• Rebooked dates — the integrity switch. When a customer agrees to move a slipping order's date, does the line get scored against the new date? Honor renegotiated dates and lateness converts into paperwork: service looks stable while promises quietly move. If the business decides to honor them (sometimes legitimately — the customer asked for the move), publish the rebooking rate next to OTIF so the movement is visible.

JDE hands you the forensic trail for that last switch: F42199 writes a ledger row every time an order line changes — including every promise-date edit — and F4209 records which orders sat on hold, who released them, and when. Date-change frequency is a queryable fact, not a suspicion.

The reason none of this is academic: large North American retailers run OTIF as a compliance program with money attached. Walmart's program is the best-documented public example — suppliers are scored against a must-arrive-by date (MABD) at the purchase-order level, with delivery windows measured in days, early arrivals counted as non-compliant alongside late ones, compliance targets in the high 90s, and charges assessed per non-compliant case as a percentage of the cost of goods. Other large retail and grocery programs follow the same shape with different constants.

Read that against the switchboard: a retail program is a customer-imposed configuration of all six switches — their grain (PO and case), their date basis (MABD), their measurement point (arrival), their tolerance (a window where early fails), their exclusions. That is why no standard OTIF exists: any supplier serving two scorecard customers is already running two definitions, plus their own. The architecture answer is in the landing pattern: store components, compute every configuration as a view.

Table set. Order lines from VBAK / VBAP (requested date, ordered quantity, rejection status), deliveries from LIKP / LIPS (actual goods issue, delivered quantity), confirmed dates from the VBEP schedule lines, ship-to master data from KNA1. You do not need VBFA — LIPS carries the order reference itself in VGBEL/VGPOS (why: the deliveries guide).

Where each switch lives. Date basis: VDATU requested vs first confirmed VBEP schedule line. Ship vs arrival: WADAT_IST actual goods issue vs LFDAT planned arrival. Splits: multiple LIPS rows per order line. Exclusions: ABGRU.

The trap. LIPS hits the same order line once per delivery split — joining VBAP to LIPS without aggregating first duplicates every split line and inflates ordered quantity by the split count. Aggregate deliveries to the order-line grain, then compare. And SAP DATS columns are never NULL — unfilled is 00000000, which must be guarded before any date comparison.

-- OTIF at order-line grain: VBAK/VBAP order lines vs LIKP/LIPS deliveries.
-- Every switch is a labeled, editable expression — change the switch, not
-- the pipeline. DATS columns assumed landed as 'yyyyMMdd' strings.
WITH order_lines AS (
  SELECT
    o.mandt,
    o.vbeln,
    i.posnr,
    i.matnr,
    o.kunnr                                          AS sold_to,
    -- SWITCH date_basis: VBAK.VDATU = customer-requested date. The first
    -- CONFIRMED date is the earliest confirmed VBEP schedule line (EDATU
    -- where BMENG > 0) — swap it in here to measure promise-keeping.
    to_date(nullif(o.vdatu, '00000000'), 'yyyyMMdd') AS promise_date,
    i.kwmeng                                         AS ordered_qty_su
  FROM bronze_sap.vbak AS o
  JOIN bronze_sap.vbap AS i
    ON  i.mandt = o.mandt
    AND i.vbeln = o.vbeln
  WHERE o.mandt = '100'                    -- always pin the client
    -- SWITCH exclusions: ABGRU set = rejected/canceled line, out of the
    -- denominator (track the rejection rate separately)
    AND i.abgru  = ''
    AND i.kwmeng > 0
),
-- LIPS hits one order line once per delivery split — aggregate FIRST
delivered AS (
  SELECT
    d.mandt,
    d.vgbel                                          AS order_vbeln,
    d.vgpos                                          AS order_posnr,
    sum(d.lfimg)                                     AS delivered_qty_su,
    count(DISTINCT d.vbeln)                          AS delivery_count,
    -- SWITCH ship_vs_arrival: WADAT_IST = actual goods issue (ship basis).
    -- Arrival basis needs proof-of-delivery data; LIKP.LFDAT carries the
    -- planned customer-arrival date to compare a POD against.
    -- SWITCH first_attempt_vs_cumulative: min() = first attempt,
    -- max() = when the line finally completed
    min(to_date(nullif(h.wadat_ist, '00000000'), 'yyyyMMdd')) AS first_ship_date,
    max(to_date(nullif(h.wadat_ist, '00000000'), 'yyyyMMdd')) AS last_ship_date
  FROM bronze_sap.lips AS d
  JOIN bronze_sap.likp AS h
    ON  h.mandt = d.mandt
    AND h.vbeln = d.vbeln
  WHERE d.mandt = '100'
    AND h.wadat_ist <> '00000000'          -- goods issue actually posted
  GROUP BY d.mandt, d.vgbel, d.vgpos
),
scored AS (
  SELECT
    o.vbeln,
    o.posnr,
    o.promise_date,
    o.ordered_qty_su,
    coalesce(d.delivered_qty_su, 0)                  AS delivered_qty_su,
    coalesce(d.delivery_count, 0)                    AS delivery_count,
    -- SWITCH tolerance: zero-early / zero-late — widen the window with
    -- date_add(promise_date, n) on either side
    (d.last_ship_date IS NOT NULL
      AND d.last_ship_date <= o.promise_date)        AS on_time,
    coalesce(d.delivered_qty_su, 0) >= o.ordered_qty_su AS in_full
  FROM order_lines AS o
  LEFT JOIN delivered AS d
    ON  d.mandt       = o.mandt
    AND d.order_vbeln = o.vbeln
    AND d.order_posnr = o.posnr
)
-- SWITCH grain: line. Order grain = every line of the order must pass;
-- unit grain = weight each line by ordered_qty_su.
SELECT
  date_trunc('month', promise_date)                       AS promise_month,
  count(*)                                                AS order_lines,
  round(100.0 * count_if(on_time AND in_full) / count(*), 1) AS otif_pct,
  round(100.0 * count_if(on_time) / count(*), 1)          AS on_time_pct,
  round(100.0 * count_if(in_full) / count(*), 1)          AS in_full_pct,
  round(100.0 * count_if(delivery_count > 1) / count(*), 1) AS split_line_pct
FROM scored
WHERE promise_date IS NOT NULL
GROUP BY date_trunc('month', promise_date)
ORDER BY promise_month;

Deeper on these tables: did we ship on time, what's still open (including the ECC vs S/4HANA status-table fork), and proving goods actually left →

Table set. Sales lines from F4211 plus F42119 — lines move to history at sales update, so the union is mandatory, not optional. Headers in F4201. The change ledger F42199 and held orders F4209feed the exclusions switch; transportation's F4215 / F4941 / F4942 carry the arrival-basis dates.

Where each switch lives. Date basis is JDE's party trick — three dates on every line: SDDRQJ requested, SDOPDJ original promise, SDPPDJ current promise. Ship vs arrival: SDADDJ actual ship (written at ship confirm) vs F4941 routing-step delivery dates. Splits: suffix records in SDSFXO, backorder quantity in SDSOBK. Exclusions: SDSOCN canceled quantity, SDCNDJ cancel date, the SDLTTR/SDNXTR status pair.

The trap. Everything numeric is encoded: dates are Julian CYYDDD integers, the line number carries three implied decimals (SDLNID / 1000), and the branch plant is space-padded. The conversions are baked into the SQL below and covered in the Julian dates and implied decimals quirks.

-- OTIF at order-line grain: F4211 open lines plus F42119 sales history.
-- Julian CYYDDD dates converted inline; the line number carries 3 implied
-- decimals; MCU is space-padded — TRIM before grouping or joining.
WITH sales_lines AS (
  -- SWITCH universe: lines leave F4211 for F42119 at sales update —
  -- measure over both or you forget everything already shipped
  SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
         SDDRQJ, SDOPDJ, SDPPDJ, SDADDJ, SDUORG, SDSOQS, SDSOCN
  FROM proddta.f4211
  WHERE SDDCTO IN ('SO')                 -- your sales order types
  UNION ALL
  SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU,
         SDDRQJ, SDOPDJ, SDPPDJ, SDADDJ, SDUORG, SDSOQS, SDSOCN
  FROM proddta.f42119
  WHERE SDDCTO IN ('SO')
),
lines_converted AS (
  SELECT
    SDKCOO, SDDOCO, SDDCTO,
    SDLNID / 1000.0                      AS line_no,        -- 3 implied decimals
    SDSFXO,
    TRIM(SDMCU)                          AS branch_plant,
    -- SWITCH date_basis: SDOPDJ = original promised date, kept by JDE for
    -- on-time measurement. SDDRQJ = customer request; SDPPDJ = current
    -- promise — measure against SDPPDJ and every reschedule launders
    -- lateness into compliance.
    CASE WHEN SDOPDJ IS NULL OR SDOPDJ = 0 THEN NULL
         ELSE DATE_ADD(MAKE_DATE(1900 + CAST(SDOPDJ AS INT) DIV 1000, 1, 1),
                       CAST(SDOPDJ AS INT) % 1000 - 1) END AS promise_date,
    -- SWITCH ship_vs_arrival: SDADDJ = actual ship date (ship basis),
    -- written at ship confirm. Arrival basis lives in transportation —
    -- F4941 routing steps carry scheduled vs actual delivery; header-level
    -- F4201.SHADLJ is often blank unless delivery confirmation runs.
    CASE WHEN SDADDJ IS NULL OR SDADDJ = 0 THEN NULL
         ELSE DATE_ADD(MAKE_DATE(1900 + CAST(SDADDJ AS INT) DIV 1000, 1, 1),
                       CAST(SDADDJ AS INT) % 1000 - 1) END AS actual_ship_date,
    SDUORG, SDSOQS, SDSOCN
  FROM sales_lines
),
-- Suffix rows (SDSFXO) are the split trail: the base suffix carries the
-- ordered quantity; shipped and canceled sum across all suffixes
line_grain AS (
  SELECT
    SDKCOO, SDDOCO, SDDCTO, line_no,
    MAX(CASE WHEN SDSFXO = '000' THEN SDUORG END)  AS ordered_qty,
    SUM(SDSOQS)                                    AS shipped_qty,
    -- SWITCH exclusions: canceled quantity (SDSOCN) shrinks the ask —
    -- the cancel dates live in SDCNDJ if you track the cancel rate
    SUM(SDSOCN)                                    AS canceled_qty,
    MIN(promise_date)                              AS promise_date,
    -- SWITCH first_attempt_vs_cumulative: MIN = first attempt,
    -- MAX = when the line finally completed
    MIN(actual_ship_date)                          AS first_ship_date,
    MAX(actual_ship_date)                          AS last_ship_date,
    COUNT(*)                                       AS suffix_rows
  FROM lines_converted
  GROUP BY SDKCOO, SDDOCO, SDDCTO, line_no
)
SELECT
  DATE_TRUNC('month', promise_date)                AS promise_month,
  COUNT(*)                                         AS order_lines,
  ROUND(100.0 * COUNT_IF(
        last_ship_date IS NOT NULL
    AND last_ship_date <= promise_date             -- SWITCH tolerance: zero-window
    AND shipped_qty >= ordered_qty - canceled_qty
  ) / COUNT(*), 1)                                 AS otif_pct,
  ROUND(100.0 * COUNT_IF(suffix_rows > 1) / COUNT(*), 1) AS split_line_pct
FROM line_grain
WHERE promise_date IS NOT NULL
  AND ordered_qty  > 0
GROUP BY DATE_TRUNC('month', promise_date)
ORDER BY promise_month;

Background: the F4211 → F42119 sales-history quirk →

Table set. Orders in SalesTable / SalesLine; posted shipments in the packing-slip pair — CustPackingSlipJour (header) and CustPackingSlipTrans(lines, not yet in this reference's catalog; fields verified against the Microsoft CDM reference); invoices in CustInvoiceJour / CustInvoiceTrans; warehouse-execution timestamps in WHSShipmentTable with WHSLoadTable / WHSLoadLine tying loads back to source lines.

Where each switch lives. Date basis is a column swap: ShippingDateRequested vs ShippingDateConfirmed on the line, ReceiptDateRequested (line) vs ReceiptDateConfirmed (header) for arrival. Ship vs arrival: packing-slip DeliveryDate vs the receipt pair, with ShipConfirmUTCDateTime as the warehouse-true instant. Splits: multiple packing-slip lines per sales line, joined on InventTransId. Exclusions: SalesStatus = 4 (Canceled). Backlog cross-check: RemainSalesPhysicalis the line's still-open quantity.

The trap. Two silent ones: unset dates are the 1900-01-01sentinel, not NULL — a "confirmed" test that skips the NULL-wrap counts unconfirmed lines as promised for 1900 — and every application table is company-partitioned, so dataareaid belongs in every filter and every join. Both covered in the sentinel dates and DataAreaId quirks.

-- OTIF at order-line grain: salesline promises vs packing-slip lines.
-- Synapse Link / Fabric Link land lowercase column names; the company
-- (dataareaid) is pinned in every CTE and carried in the join.
WITH order_lines AS (
  SELECT
    sl.dataareaid,
    sl.salesid,
    sl.inventtransid,
    sl.itemid,
    sl.salesqty                                    AS ordered_qty,
    -- SWITCH date_basis: confirmed vs requested is a column swap —
    -- shippingdaterequested / shippingdateconfirmed on the line, and
    -- receiptdaterequested (line) / receiptdateconfirmed (header) for the
    -- arrival-side pair. 1900-01-01 is the never-confirmed sentinel.
    CASE WHEN CAST(sl.shippingdateconfirmed AS DATE) = DATE '1900-01-01'
         THEN NULL
         ELSE CAST(sl.shippingdateconfirmed AS DATE) END AS promise_date
  FROM salesline sl
  WHERE sl.dataareaid = 'usmf'
    -- SWITCH exclusions: 4 = Canceled in the SalesStatus enum — decode
    -- extended enums via GlobalOptionsetMetadata before trusting integers
    AND sl.salesstatus <> 4
    AND sl.salesqty    > 0
),
-- One packing-slip line per shipment per order line — the split trail.
-- CustPackingSlipTrans is not yet in this reference's table catalog; its
-- fields are verified against the Microsoft CDM reference.
shipped AS (
  SELECT
    pt.dataareaid,
    pt.inventtransid,
    -- SUM, not MAX: packing-slip corrections/cancellations post offsetting
    -- reversal rows, and summing nets them out of the delivered quantity
    SUM(pt.qty)                                    AS shipped_qty,
    COUNT(*)                                       AS shipments,
    -- SWITCH ship_vs_arrival: the packing-slip deliverydate is the
    -- posting-side (ship) date. The warehouse-true timestamp is
    -- whsshipmenttable.shipconfirmutcdatetime — stored in UTC, so take
    -- CAST(from_utc_timestamp(shipconfirmutcdatetime, '<your_tz>') AS DATE)
    -- before comparing, or shipments near midnight land on the wrong day.
    -- SWITCH first_attempt_vs_cumulative: MIN = first attempt, MAX = last
    MIN(CAST(pt.deliverydate AS DATE))             AS first_ship_date,
    MAX(CAST(pt.deliverydate AS DATE))             AS last_ship_date
  FROM custpackingsliptrans pt
  WHERE pt.dataareaid = 'usmf'
  GROUP BY pt.dataareaid, pt.inventtransid
)
SELECT
  DATE_TRUNC('month', ol.promise_date)             AS promise_month,
  COUNT(*)                                         AS order_lines,
  ROUND(100.0 * COUNT_IF(
        s.last_ship_date IS NOT NULL
    AND s.last_ship_date <= ol.promise_date        -- SWITCH tolerance: zero-window
    AND COALESCE(s.shipped_qty, 0) >= ol.ordered_qty
  ) / COUNT(*), 1)                                 AS otif_pct,
  ROUND(100.0 * COUNT_IF(COALESCE(s.shipments, 0) > 1) / COUNT(*), 1)
                                                   AS split_line_pct
FROM order_lines ol
LEFT JOIN shipped s
  ON  s.dataareaid    = ol.dataareaid
  AND s.inventtransid = ol.inventtransid
WHERE ol.promise_date IS NOT NULL
GROUP BY DATE_TRUNC('month', ol.promise_date)
ORDER BY promise_month;

Background: UTC datetimes, enum decoding, and InventDim (needed the moment you cut OTIF by site or warehouse) →

Table set. Orders in OOHEAD / OOLINE; the delivery trail in MHDISH (delivery header) and MHDISL (one row per order line per delivery); the invoiced-delivery ledger in ODHEAD / ODLINE.

Where each switch lives. Date basis: OARLDT header requested date vs OBDWDT line planned delivery date. In-full: OBORQT ordered vs OBDLQT cumulative delivered (with OBIVQT invoiced beside them); a first-attempt test scores the first MHDISL delivery's URTRQT instead. Exclusions: the OBORST status ladder — 66 delivered, 77 invoiced, 99 completed withoutdelivery, M3's canceled-line path.

The traps. MHDISH carries both directions — inbound and outbound deliveries share the structure, discriminated by INOU — so an unfiltered join counts receipts as shipments. Every join must carry CONOor it fans out across companies. And this reference's curated field set carries no verified actual-departure timestamp for the delivery header, so the SQL below uses the invoice date (ODHEAD.UAIVDT) as an explicit, stated proxy for the actual side — the status ladder guarantees delivery precedes invoicing, making it a defensible latest-bound — joined down to line grain through ODLINEso one line's late delivery never stamps its date onto the order's other lines. If your site populates delivery-departure dates, verify them against your data and tighten the query.

-- OTIF at order-line grain: OOLINE promises vs the MHDISL delivery trail.
-- CONO pinned in every CTE and carried in every join; numeric YYYYMMDD
-- dates 0-guarded before TO_DATE.
WITH order_lines AS (
  -- OOLINE splits carry a POSX suffix — collapse suffixes to the line
  SELECT
    ol.OBCONO,
    ol.OBORNO,
    ol.OBPONR,
    SUM(ol.OBORQT)                       AS ordered_qty,
    -- OBDLQT is cumulative delivered — the eventually-complete measure
    SUM(ol.OBDLQT)                       AS delivered_qty,
    -- SWITCH date_basis: OBDWDT = the line's planned delivery date; the
    -- customer-requested date is header-level (OOHEAD.OARLDT)
    MIN(CASE WHEN ol.OBDWDT = 0 THEN NULL
             ELSE TO_DATE(CAST(CAST(ol.OBDWDT AS BIGINT) AS STRING),
                          'yyyyMMdd') END) AS promise_date
  FROM OOLINE ol
  WHERE ol.OBCONO = 100
    -- SWITCH exclusions: '99' = completed WITHOUT delivery — M3's
    -- canceled-line path on the OBORST status ladder
    AND ol.OBORST <> '99'
  GROUP BY ol.OBCONO, ol.OBORNO, ol.OBPONR
),
-- MHDISL: one row per order line per delivery — the split trail.
-- SWITCH first_attempt_vs_cumulative: delivery_count > 1 means the line
-- split; a strict first-attempt test scores only the first delivery's
-- URTRQT against the full ordered quantity.
deliveries AS (
  SELECT
    dl.URCONO,
    dl.URRIDN,
    dl.URRIDL,
    COUNT(*)                             AS delivery_count,
    SUM(dl.URTRQT)                       AS qty_on_deliveries
  FROM MHDISL dl
  WHERE dl.URCONO = 100
    AND dl.URINOU = 1   -- outbound only; MHDISH/MHDISL carry both directions
  GROUP BY dl.URCONO, dl.URRIDN, dl.URRIDL
),
-- SWITCH ship_vs_arrival: the curated M3 reference carries no verified
-- actual-departure timestamp, so the invoice date (ODHEAD.UAIVDT) stands in
-- as the latest defensible bound — delivery precedes invoicing on the
-- status ladder ('66' Delivered → '77' Invoiced). Verify your site's
-- delivery-date fields before tightening this to a true ship date.
-- ODLINE keeps this at LINE grain: one row per invoiced order line per
-- delivery, so an early-invoiced line is not failed by a sibling line's
-- later delivery on the same order.
invoiced AS (
  SELECT
    odl.UBCONO,
    odl.UBORNO,
    odl.UBPONR,
    MAX(CASE WHEN odh.UAIVDT = 0 THEN NULL
             ELSE TO_DATE(CAST(CAST(odh.UAIVDT AS BIGINT) AS STRING),
                          'yyyyMMdd') END) AS last_invoice_date
  FROM ODLINE odl
  JOIN ODHEAD odh
    ON  odh.UACONO = odl.UBCONO
    AND odh.UAORNO = odl.UBORNO
    AND odh.UADLIX = odl.UBDLIX
    AND odh.UATEPY = odl.UBTEPY
  WHERE odl.UBCONO = 100
  GROUP BY odl.UBCONO, odl.UBORNO, odl.UBPONR
)
SELECT
  DATE_TRUNC('month', ol.promise_date)   AS promise_month,
  COUNT(*)                               AS order_lines,
  ROUND(100.0 * COUNT_IF(
        ol.delivered_qty >= ol.ordered_qty
    AND iv.last_invoice_date IS NOT NULL
    AND iv.last_invoice_date <= ol.promise_date  -- SWITCH tolerance: zero-window
  ) / COUNT(*), 1)                       AS otif_pct,
  ROUND(100.0 * COUNT_IF(COALESCE(dv.delivery_count, 0) > 1) / COUNT(*), 1)
                                         AS split_line_pct
FROM order_lines ol
LEFT JOIN deliveries dv
  ON  dv.URCONO = ol.OBCONO
  AND dv.URRIDN = ol.OBORNO
  AND dv.URRIDL = ol.OBPONR
LEFT JOIN invoiced iv
  ON  iv.UBCONO = ol.OBCONO
  AND iv.UBORNO = ol.OBORNO
  AND iv.UBPONR = ol.OBPONR
WHERE ol.promise_date IS NOT NULL
  AND ol.ordered_qty  > 0
GROUP BY DATE_TRUNC('month', ol.promise_date)
ORDER BY promise_month;

Background: CONO partitioning, numeric YYYYMMDD dates, status ladders, and column prefixes & field aliases →

The switchboard has an architectural consequence: never store the ratio — store the components.

• Bronze: the source tables as extracted — all companies, dates still in their native encodings (DATS strings, Julian integers, numeric YYYYMMDD, UTC datetimes), CDC flags kept.
• Silver: one company, encodings resolved exactly once — zero-dates and sentinels become real NULLs, Julian and YYYYMMDD become DATE, UTC becomes local. Conformed order-line and delivery-line entities per source.
• Gold: one fct_order_line_fulfillment at order-line grain carrying the components: requested / first-confirmed / current promise dates, first-ship and last-ship (and, where captured, arrival) dates, ordered / shipped / canceled quantities, the delivery count, and a first-attempt-complete flag — plus the usual conformed customer, product, and date dimensions. No OTIF column anywhere.

Every OTIF variant then becomes a SELECT: your line grain, first-confirmed, zero-window view; the customer's PO-grain MABD-window view; last year's definition for continuity — all reading the same fact. When the definition argument arrives (it always arrives), switching definitions is a view change, not a pipeline re-engineering. That one fact serves fill rate and OTD on the way, since both are projections of the same components. This is the medallion / Kimball pattern the rest of the library builds toward — the same shape as the deliveries guide's landing pattern, extended with the promise-date components OTIF needs. And it is the fact the customer service dashboard pattern is built on.