Write a purchase order from what you’ve used

Why QuickBooks cannot write it

QuickBooks knows what you sold, because the sales lines are there, and what is on order, because the open purchase orders are there. It does not know the supplier’s lead time, it does not trust its own quantity on hand on most files, and the supplier’s price sheet is a PDF in someone’s email.

So the purchase order is written by hand, from memory of what moves, on a Friday afternoon, and it is either too big or too small.

What the sentence needs

The sales lines are already in the database from the weekly QuickBooks export. The stock count is the weekly count. The open purchase orders are a QuickBooks report. The supplier’s price sheet, with the lead time and the unit cost, is loaded as they sent it. From there the question is the one an owner would say out loud, and the answer is the purchase order.

It is the last rung of the demo because it is the thing an owner cannot get from QuickBooks, a spreadsheet, or a bookkeeper by Friday.

The purchase order, from a fictional company

Asked in one sentence of the window supplier’s items. The supplier’s price sheet in the database also holds the increase that takes effect in August, which is why the order is priced today and not next month.

with usage as (
    select i.canonical_id as item_id, sum(l.qty) as used_12_weeks
    from txn_lines l
    join txns t on t.id = l.txn_id
    join items i on i.id = l.item_id
    where t.date >= '2026-05-04' and t.date < '2026-07-27'
    group by i.canonical_id),
stock as (
    select item_id, sum(on_hand) as on_hand, sum(on_order) as on_order
    from stock_snapshots
    where week = '2026-07-27'
    group by item_id),
sheet as (
    select c.item_id, c.vendor_sku, c.pack_size, c.unit_cost, c.lead_time_days
    from vendor_catalog c
    join vendors v on v.id = c.vendor_id
    where v.name = 'Vantera Window Mfg'
      and c.effective_date = (select max(effective_date) from vendor_catalog x
                              where x.item_id = c.item_id and x.effective_date <= '2026-07-27'))
select s.vendor_sku, i.name as item,
       u.used_12_weeks,
       round(u.used_12_weeks / 12.0 * (s.lead_time_days / 7.0 + 4)) as need_thru_lead_plus_4wk,
       st.on_hand, st.on_order,
       round(u.used_12_weeks / 12.0 * (s.lead_time_days / 7.0 + 4)) - st.on_hand - st.on_order as order_qty,
       s.unit_cost,
       (round(u.used_12_weeks / 12.0 * (s.lead_time_days / 7.0 + 4)) - st.on_hand - st.on_order) * s.unit_cost as line_total
from sheet s
join items i on i.id = s.item_id
join stock st on st.item_id = s.item_id
left join usage u on u.item_id = s.item_id
where round(u.used_12_weeks / 12.0 * (s.lead_time_days / 7.0 + 4)) - st.on_hand - st.on_order > 0
order by line_total desc

Northgale Building Products is a fictional company. Every name, figure and supplier on this page is made up, built to behave like a real three-and-a-half-year QuickBooks Desktop file so we can show real screens without showing anyone’s real numbers. Data as of the 2026-07-27 refresh; the last invoice in the file is dated 2026-07-24.

Short answers

• Does the purchase order go into QuickBooks?No. Nothing we build writes into QuickBooks. The order comes back as a document. Someone keys it, the same as today.
• What if we have more than one supplier for the same item?The price sheet says which supplier the item is ordered from. Where it is both, the question names the supplier.
• Does this need the supplier’s price sheet?Yes, for the price and the lead time. Most suppliers send one as a spreadsheet or a PDF. It is loaded as they send it.

Related questions

• What should I reorder this week?
• Are we paying more for windows than last year?
• Your systems, joined