Stopping the same order from being picked twice

The window

A picking Suitelet lists open orders. A user selects a batch and submits; the Suitelet writes a batch record and hands it to a Map/Reduce, which prints pick tickets and creates fulfillments. On a busy morning the job takes a minute or two to work through a batch.

During that minute, nothing about the orders themselves has changed. They’re still open and unfulfilled, and they still appear on the screen. A second user refreshes, sees them, and submits them in their own batch. Two pick tickets, two people walking the same aisle, and sometimes two fulfillments.

The first attempt

The first fix kept the order list honest by excluding any order mentioned in a batch that was still pending or processing. The batch stores its order IDs as JSON, so the check looked like this:

AND NOT EXISTS (
    SELECT 1 FROM pick_batch b
    WHERE b.status IN ('PENDING', 'PROCESSING')
      AND b.order_ids LIKE '%"' || t.id || '"%'
)

It was correct, and it made the screen take more than a minute to load. A LIKE with a leading wildcard can’t use an index, so for every candidate order the database scanned the JSON text of batch records. That’s open orders times batches, on a long-text field, every load — and it got slower as batches accumulated.

The fix that holds

Put the fact on the order, where the query is already looking. At submit time, before handing off to the job, the Suitelet stamps each order with an in-flight flag:

orderIds.forEach(id => record.submitFields({
    type: record.Type.SALES_ORDER,
    id,
    values: { custbody_example_queued: true },
    options: { enableSourcing: false, ignoreMandatoryFields: true }
}));

The list query excludes flagged orders with a plain column filter that costs nothing. The Map/Reduce clears the flag as it finishes each order, success or failure.

Don’t strand orders when a job dies

A flag set by one process and cleared by another has a failure mode: if the job crashes, the flag never clears, and those orders vanish from the screen for good. So the exclusion also respects age:

AND (t.custbody_example_queued = 'F'
     OR t.lastmodifieddate < CURRENT_TIMESTAMP - (1/48))   -- 30 minutes

An order marked in flight for more than thirty minutes is treated as abandoned and reappears. Tune the window to the longest a healthy batch takes, with margin.

Test it the way it fails

The test is the race itself: submit a batch, refresh the screen on a second session within a few seconds, and confirm those orders have dropped off. Then kill a batch on purpose and confirm the orders come back after the window. A double-pick fix that has only been tested with one user hasn’t been tested.

Related: MAP_REDUCE_ALREADY_RUNNING is not an error, and SuiteQL doesn’t accept INTERVAL ’30’ MINUTE.