To Purchase

The buying worksheet: it takes every pending Material Request, walks each item's suppliers cheapest-first until the required quantity is covered, and lets you raise supplier-grouped Purchase Orders (correctly allocated back to the originating Material Requests) directly from the grid.

Purpose

• Purchase managers / stock users use it to turn open Material Requests into Purchase Orders.
• It answers "for everything I still need to buy, which supplier (by rank/price) should I order from, how much, and at what price?"
• It allocates supplier stock to demand cheapest-first, shows monthly supplier offers (and read-only reference-supplier offers) side by side, and exposes per-item PO history.
• The Create Purchase Orders action raises one PO per supplier and links each PO line back to the Material Request it satisfies.

How to Access

Desk > Reports > To Purchase, or /app/query-report/To Purchase. Report type: Script Report. Reference DocType: Material Request. Roles: Purchase Manager, Stock Manager, Stock User, Purchase User.

Filters

Columns

Static columns are always present; offer columns are generated dynamically (one per month) for the chosen supplier and for any reference suppliers.

Static columns

Dynamic offer columns

get_month_label(i) maps index 0 → "This Month", 1 → "Next Month", i → "In {i} Months". The column count expands to the max number of offers found.

How It Works

flowchart TD
  MR[Material Requests\ntype=Purchase, status Pending/\nPartially Ordered/Received] -->|stock_qty - ordered_qty| NEED[items_needed:\nitem_code -> pending qty]
  NEED --> SUP
  SUP[Supplier Item Price and Stock\npurchase_blocked!=1, stock_qty>0\nsupplier not reference-only] -->|order by item, price ASC| LOOP
  LOOP[Greedy allocation:\nper item, add suppliers\ncheapest-first until need met] --> ROWS[Rows with allocated_qty\n& remaining_qty]
  OFF[Supplier Offer rows] --> ROWS
  REF[Reference-only suppliers' offers] --> ROWS
  LPR[Last PO rate per item+supplier] --> ROWS
  ROWS -->|select rows + edit allocated qty| ACT[Create Purchase Orders]
  ACT --> GRP[Group selected rows by supplier]
  GRP --> ALLOC[Re-fetch pending MRs per item,\nallocate qty across MRs by schedule_date]
  ALLOC --> POs[One submitted PO per supplier\nlines linked to material_request_item]
  ALLOC --> UPD[Decrement supplier stock_qty]

Demand (get_items_needed) — sums pending qty across all Purchase-type Material Requests in status Pending / Partially Ordered / Partially Received, filtered by custom_is_amazon_pending. Pending per MR line = max(0, stock_qty − ordered_qty). Items with zero pending are dropped.

Supplier candidates (get_supplier_list) — pulls Supplier Item Price and Stock rows where purchase_blocked ≠ 1, stock_qty > 0, the item is needed, and the supplier is not reference-only (custom_is_reference_only ≠ 1). Ordered by item, then unit price ascending.

Greedy allocation (get_data) — iterating cheapest-first per item: allocated = min(supplier_stock, remaining_need), then remaining_need −= allocated. Once an item's need hits 0, further (more expensive) supplier rows for it are skipped. This produces one or more rows per item — the cheapest supplier(s) that together cover demand.

Reference suppliers — suppliers flagged custom_is_reference_only = 1 never get allocated stock; their offers appear as extra ref{n}_offer_* columns purely for price comparison (sorted by display name).

Creating Purchase Orders (create_purchase_orders_from_report):

1. Group the selected rows by supplier → one PO each.
2. For each item, re-fetch its pending MRs (status as above) ordered by schedule_date, and allocate the row's allocated_qty across those MR lines, oldest schedule first.
3. Convert qty into the supplier UOM via get_conversion_factor, rounding up with ceil. Each PO line carries material_request + material_request_item so receipts flow back to the originating MR.
4. PO schedule_date = earliest MR schedule date (never before today). Company/warehouse come from Amazon SP API Settings.
5. After ordering, decrement the supplier's stock_qty by what was consumed (update_supplier_stock_after_po) so re-running the report reflects depleted supplier stock.
6. Each PO is inserted and submitted (ignore_permissions=True). Returns the list of created PO names.

PO History (get_po_history_for_item) — the History eye icon opens a dialog listing the last 50 submitted POs for the item (date, PO, supplier, status, qty, amount) with totals; status pills are color-coded in the JS.

Notes

• Qty edits in the grid live only in the browser (purchase_report_state) until you click Create Purchase Orders; editing above available stock prompts a confirm.
• Suppliers are ranked cheapest-first by price; rank = 1 is the cheapest (the same rank shown in the Forecast report's "Top Supplier").
• Supplier stock_qty is mutated on PO creation — this is intentional (the comment notes set_value doc_events can't be used here, so the doc is saved directly).
• Allocation is greedy by price, not optimal across items; sufficient for the typical "one reference supplier with ≤3 offer rows" scale noted in the code.
• Related: Navgold Forecast Report creates the Material Requests this report consumes.