Lead Time flow 1 — First and second arrival into Sydney

What it reads, and where from.

One NetSuite export, run as a RESTlet against saved search 7973 on the transaction record, 1,000 rows per page, asynchronous. Rows are per transaction line, so one product appears once per open inbound line.

A column guard stops the flow rather than blanking the catalogue. The import does not discard empty values — a blank is a deliberate clear — so a column disappearing from the search would wipe the field on every product. The script checks the first real row for the columns it needs and throws a fatal error if any is missing. Verified present on 2026-09-21.

Columns the script looks for and the search does not supply. Each is a feature that is written, reachable and inert. They are listed in Known defects and Known limitations:

What it does with that, step by step.

for each row, grouped by item:
    if the row has no order line, keep it only for its item-level values
    otherwise, key it by order + line so the same line is not counted twice

for each item:
    for each line:
        sellable = max(0, remaining - allocated)
        if location is Sydney:
            source is a transfer order or a Sydney purchase order, dated as-is
        if location is Bonded and the line is a purchase order:
            source is a bonded purchase order, dated +35 days
            remember the earliest raw bonded date, including fully allocated lines

    drop sources with no sellable units or no date
    drop sources dated more than 14 days before today
    merge same-kind sources sharing a date, summing quantities,
        stage from the largest contributor
    sort by date, then transfer order → Sydney PO → bonded on hand → bonded PO

    first arrival  = first surviving source
    second arrival = the one after it
    write only if any of the six values differs from what the item carries

All date arithmetic runs on the Australia/Sydney date, held as UTC midnight so day counts are exact. The flow's own schedule is also set to Australia/Sydney.

Errors are caught per item: one bad product is returned unwritten with the error logged against the page, rather than failing the whole page.

Everything in this chain by name. The entry refers to these by name throughout; the ids are in links: and Source references. Using the id in a ticket is fine; using it in prose is how the last round of confusion started.

Retired but still active in NetSuite: SCRIPT | LEAD TIMES | INVENTORY ITEMS | V2 - ITEM NEXT AVAILABLE RECEIVE DATE *live (7173). No live flow reads it.

Configuration read from Celigo and NetSuite on 2026-09-21, re-read 2026-09-23.

Run-level validation, 2026-09-23. The first time this chain was checked at the job level rather than by reading configuration. Run of 02:05–02:06 Australia/Sydney.

Six consecutive runs over 2026-09-22/23: every one completed, zero errors, 1,250–1,397 records, 1,053–1,250 unchanged. The script has not thrown once.

The page count is the finding. numPagesGenerated: 2 at 1,000 rows a page means there is exactly one page boundary on every run, and the guard meant to protect against it is inert — LT1-D6. This was previously assumed theoretical. It is not.

An earlier estimate in review put this at 3,436 rows over four pages. That came from an approximate query written outside NetSuite, not from the search. The search returns 1,251 records over two pages. The exposure is smaller than estimated, and it is present on every run.

The September 2026 rebuild was specified in its own working rule set, LT-001 to LT-081, confirmed by the project owner on 2026-09-17. Those numbers are what the team says out loud and what Asana tickets cite; they are not registry ids and never become them (CONVENTIONS.md §4). This table is the bridge, and it covers all three lead-time flows — flow 2 and flow 3 point back here rather than repeating it.

51 confirmed rules · 48 carried by a rule · 3 open questions · 8 open defects.

This table is prose, not data. A reader can follow it; a script cannot query it — registry-index.json carries an entry's front-matter, not its body. Recording the crosswalk as external_rules: front-matter instead would make it queryable and let npm run check verify every arrow lands. That was reviewed on 2026-09-21 and deferred, not rejected.

The same logic as a path through the decisions, in the order they run.

Two decisions, taken in this order. Stage 1 runs once per inbound line; stage 2 runs once per product across all of that product's surviving lines. Confusing the two is the single easiest mistake to make here, because stage 1 reads like it decides the arrival and it does not — it only decides whether a line becomes a candidate.

Stage 1 — is this line a supply source, and when does it land?

line
 ├─ sellable = remaining − allocated  (floor 0)
 │
 ├─ sellable = 0 ──────────────────────────► NOT A SOURCE
 │                                            (bonded lines still set the raw bonded date)
 └─ sellable > 0
     ├─ location = Sydney (11)
     │   ├─ transfer order ─────────────────► SOURCE · type TO       · date = its own
     │   └─ purchase order ─────────────────► SOURCE · type SYD_PO   · date = its own
     ├─ location = Bonded (25) AND purchase order
     │                       ─────────────────► SOURCE · type BONDED_PO · date = its own + 35 days
     └─ anything else ──────────────────────► IGNORED

Stage 2 — which two sources become the first and second arrival?

all sources for the product
 ├─ no date? ──────────────────────────────► DROP
 ├─ dated more than 14 days before today? ─► DROP  (the next source is promoted up)
 │
 ├─ merge: same type AND same date → one arrival
 │     quantity = sum;  stage = from the largest single contributor
 │     different types on the same date never merge
 │
 ├─ sort: by date ascending
 │         tie → TO, then SYD_PO, then BONDED_OH, then BONDED_PO
 │
 ├─ [0] ───────────────────────────────────► FIRST ARRIVAL   (date, qty, stage)
 ├─ [1] ───────────────────────────────────► SECOND ARRIVAL  (date, qty)
 └─ none ──────────────────────────────────► blank, which CLEARS the field

Then, once per product: write only if any of the six values differs from what the product already carries. Blank and zero compare as the same value; dates are normalised on both sides before comparing, so 1/8/2026 and 01/08/2026 do not count as a change.

The two rules in one sentence each. LI-BL-INV-005 decides whether a line is supply at all and what day it lands. LI-BL-INV-014 decides which of those become the two arrivals a customer is told about.

What it writes, and who reads it afterwards.

One NetSuite import, updating the inventoryitem record, filtered to products flagged as changed.

Side effect: on completion the flow starts flow 2 (66e0e59d32b330462921d448), whether or not anything was written.

"Discard if empty" must stay off on all six mappings. A blank is a deliberate clear, and the column guard above is what makes that safe.

Real records carried end to end, so the logic can be checked against something that happened.

*Item 6208, Ari Fabric Dining Chair (Ash, Green).* Read live from NetSuite on 2026-09-23. This is the example to hand someone who asks what any single field means.

What the search returns — three open inbound lines:

Stage 1 — each line becomes a source (LI-BL-INV-005):

None is overdue, no two share a date, so nothing merges and nothing drops.

Stage 2 — sort by date, take the first two (LI-BL-INV-014): 25/9 → 2/10 → 9/11. The third source, P55436, is not published at all — only two arrivals are ever recorded.

Every written field, and where its value came from:

Read this against custitem_qty_on_order, which is 8 for this item. 3 + 1 = 4, not 8. That is correct and expected: Quantity On Order counts every unit on order across all three purchase orders, whereas the arrival quantities count only what is still free to sell on the two arrivals that get published. They answer different questions and should not be reconciled.

Purchase Order Stage — the full list. Read from NetSuite on 2026-09-23, closing open question 2. The stage is written as an internal id; these are the names behind the numbers.

Inactive and not expected on a live arrival: 1, 4, 7, 8, 12, 13.

Real records carried end to end, so the logic can be checked against something that happened.

Reference items confirmed by the project owner on 2026-09-17, re-checked against production on 2026-09-23. The Live column is what NetSuite holds now.

A worked example built on live purchase-order data expires. 6208 still matches because its orders have not moved; 332634's have. Neither is evidence of a defect. When re-verifying, check the arithmetic against the lines as they stand, not the stored answer against a date written weeks ago.

What to run to know the behaviour is intact. Expected values, never “should work”.

What a person checks, by hand, before it is trusted.

The inputs that sit at the boundary, and whether each is handled.

What breaks it, how that shows, and how to recover.

Scope that was deliberately chosen. A limitation is a decision.

• A product with no open inbound lines is never on the report. It therefore never has its arrival fields cleared, and keeps whatever it last carried. Clearing them needs a second export listing items with no inbound stock — designed, not built.

  Re-measured 2026-09-23, counting a product as stale when it holds any of the five arrival fields and has no open inbound line at Sydney or Bonded:

  	Live on Shopify	Not live	Total
  Inventory items	398	493	891
  Kits	280	41	321
  Total	678	534	1,212

  Kits are out of scope for this flow by design and are written by flow 3; the 398 live inventory items are this flow's. Consistent with the ~1,184 recorded previously.

  Correction. An intermediate check during the 2026-09-23 review reported only one stale inventory item. That check looked at the first-arrival date alone. Most stale products hold a stale bonded date or arrival quantity instead, which is why the honest figure is 398, not 1. A field-by-field test is the only valid one here.

• The bonded-on-hand path in this script is dead code. Confirmed by the project owner on 2026-09-23: bonded stock on hand belongs to flow 2's exports, and Lead Time: export po data carries only what sits on an order. The read of bondedQtyAvailable, the BONDED_OH candidate and its entry in the ordering table can all be removed; the script's header also claims a second export and a Shopify import that this flow does not have. The 35-day constant stays — the bonded purchase order uplift still uses it. Tidy-up, not a behaviour change.

• Unallocated backorders are not deducted. Confirmed as the intended behaviour: sellable quantity matches what NetSuite shows on the line. The alternative is coded and switched off.

• Quantities are in NetSuite base units (pieces), including items sold as a set. Confirmed.

• There is no override lead time. A transfer order's own expected receipt date is taken as correct.

• Kits are not handled here. Flow 3 covers them, and has not yet been aligned to these rules.

• 35 days of bonded transit is hard-coded, and now in two places — this script and flow 2's. Both must change together.

Where it does something other than what was decided. A defect is a mistake.

LT1-D1, the defect that cleared the other warehouse's date, belonged to the automation retired on 2026-09-18. It does not exist in the replacement — the six values are written together from one calculation, not one warehouse at a time.

Fixing LT1-D6 — two changes, not one

Change 1 — the column. Add to saved search 7973 as a Formula (Numeric) with the custom label itemLineCount exactly, because the script reads it by that name:

COUNT(*) OVER (PARTITION BY {item.internalid})

If NetSuite rejects {item.internalid}, try {item.id}.

This is a proposal, not a verified fact. The same expression was run successfully against this account in SuiteQL, which is a different engine path from saved-search formulas. Analytic functions are not officially supported in saved-search formulas and commonly fail with Invalid expression. It must be tested in the NetSuite UI before anyone relies on it.

The column must hold the item's total row count across the whole search. A per-page count makes the guard always true and silently disables it, which is the state it is in now.

Change 2 — the comparison. The guard compares it.rows, which counts every row collected for the item. It should compare the number of inbound lines. With one export they are the same number, so the bug is invisible today; add any non-line row and the guard starts passing when it should skip. Fixing the column without this leaves the guard wrong in a new way.

Ranked, as at 2026-09-23:

Recommendation changed 2026-09-23, and the reason matters. While the sort order was unknown the exposure could have been every multi-line product, which justified changing NetSuite. Confirmed sorting by item internal id caps it at one product per run, under-promising. At that size the right answer is the cheapest reliable one — option 1, raise the page size — not an untested formula. Keep the formula as the answer if the row count ever approaches whatever page size is set, because raising it is a ceiling, not a fix.

What could not be established. Recorded rather than guessed.

The code and searches this reading was written from.

Everything this entry describes lives in Celigo integration 5ad5bad381aef80b5ae1ec10, flow section 620d943fb8184a400655ed19. Assets are listed in code_refs. Behaviour cross-checked against NetSuite on the same date — saved search 7973 output and item field values.

Open convention question. CONVENTIONS §7 assumes repo + path + verified_at_commit. Celigo has no version control, so there is no commit to record. Rather than record one that does not exist, these code_refs carry exported_on: the date of the export they were read from.

What moved on this page, and when.

Inputs

Outputs