Infinity Portal
Docs menu

Purchasing

Read Conventions first.

Document chain: Purchase Request → Purchase Order → GRN (Goods Receipt PO) → A/P Invoice, and separately Goods Return Request → Goods Return → A/P Debit Memo. Each consuming line carries a typed source_*_line_id FK (ON DELETE RESTRICT) and bumps a counter + row_status on the source line.

purchase_requests

Near-clone of inventory_transfer_requests, with no pricing.

  • id, business_id, branch_id NOT NULL, warehouse_id → warehouses NULL
  • document_no - UNIQUE (business_id, document_no)
  • request_date date DEFAULT current_date; remarks
  • status DEFAULT ‘open’ - CHECK IN (‘open’,‘closed’,‘cancelled’)
  • closed_at/closed_by, cancelled_at/cancelled_by, created_by, created_at

purchase_request_lines

id, purchase_request_id CASCADE, item_id → items NOT NULL; quantity numeric(14,3) NOT NULL; uom_id; required_date NULL; ordered_quantity numeric(14,3) DEFAULT 0 (consumed by POs); row_status CHECK IN (‘open’,‘closed’); created_at

purchase_orders

Pricing + tax + snapshotted supplier fields + persisted rollup totals.

  • id, business_id, document_no - UNIQUE (business_id, document_no)
  • branch_id NOT NULL, warehouse_id NOT NULL, supplier_id → business_partners NOT NULL
  • supplier_name, supplier_address, supplier_tax_id, supplier_contact_person - snapshots at creation
  • order_date, due_date, delivery_date - date
  • payment_term_id → payment_terms; price_list_id → price_lists
  • line_type DEFAULT ‘item’ - CHECK IN (‘item’,‘service’)
  • header_discount_type CHECK IN (‘percentage’,‘fixed’); header_discount_value numeric(14,2)
  • convenience_fee_id → convenience_fees
  • sscl_liable boolean; sscl_amount numeric(14,2)
  • Totals (numeric(14,2) DEFAULT 0): items_subtotal, header_discount_amount, taxable_value (pre-tax base after header discount), tax_total (recomputed on that reduced base), charges_total, charges_tax_amount, convenience_fee_amount, convenience_fee_tax_amount, grand_total
  • status DEFAULT ‘draft’ - CHECK IN (‘draft’,‘pending_approval’,‘posted’,‘closed’,‘cancelled’); auto-transitions to ‘closed’ when every line’s row_status = ‘closed’
  • remarks, posted_at/posted_by, cancelled_at/cancelled_by, created_by, created_at

purchase_order_lines

  • id, purchase_order_id CASCADE
  • item_id → items NULL (service lines have none); description - CHECK (item_id IS NOT NULL OR description <> ‘’)
  • quantity numeric(14,3) NOT NULL; uom_id; unit_price numeric(14,2)
  • discount_type CHECK IN (‘percentage’,‘fixed’); discount_value numeric(14,2)
  • tax_group_id/tax_component_id - CHECK: never both set
  • tax_amount, line_total numeric(14,2) - line_total is tax-inclusive
  • delivery_date
  • source_request_line_id → purchase_request_lines RESTRICT
  • received_quantity numeric(14,3) DEFAULT 0 - written by GRN posting
  • row_status CHECK IN (‘open’,‘closed’,‘cancelled’); created_at

purchase_order_charges

Ad-hoc additional charges (freight, delivery…), repeatable per PO.

  • id, purchase_order_id CASCADE, description NOT NULL, amount numeric(14,2)
  • is_taxable boolean DEFAULT false (deliberate default-off)
  • tax_group_id/tax_component_id - CHECK: not both; tax_amount numeric(14,2); created_at

goods_receipt_notes

“Goods Receipt PO” - receiving specifically against a Purchase Order. Distinct from the plain goods_receipts inventory document.

  • id, business_id, document_no - UNIQUE (business_id, document_no)
  • branch_id NOT NULL, warehouse_id NOT NULL
  • purchase_order_id → purchase_orders NULL
  • receipt_date, reference, notes
  • status CHECK IN (‘draft’,‘posted’,‘cancelled’)
  • items_subtotal, discount_total, tax_total, grand_total numeric(14,2)
  • posted_at/posted_by, cancelled_at/cancelled_by, created_by, created_at

goods_receipt_note_lines

Carries the PO’s pricing/tax/discount as a read-only snapshot at copy time - never live-recomputed.

  • id, goods_receipt_note_id CASCADE, item_id NOT NULL, gl_account_id NOT NULL
  • quantity numeric(14,3), unit_cost numeric(12,2)
  • discount_type DEFAULT ‘percentage’, discount_value numeric(14,2)
  • tax_group_id, tax_component_id, tax_code, tax_amount numeric(14,2)
  • net_amount numeric(14,2) - tax-exclusive; this is what GL/stock costing uses
  • line_total numeric(14,2) - tax-inclusive (net_amount + tax_amount)
  • batch_number, expiry_date, serial_numbers, admission_date - batch/serial capture
  • bin_location_id → bin_locations, uom_id, cost_center_id → cost_centers
  • source_po_line_id → purchase_order_lines RESTRICT
  • returned_quantity numeric(14,3) DEFAULT 0; created_at

Goods Return chain

  • goods_return_requests - id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NULL, request_date, remarks; status CHECK IN (‘open’,‘closed’,‘cancelled’); closed/cancelled stamps, created_by, created_at
  • goods_return_request_lines - id, goods_return_request_id CASCADE; source_receipt_line_id → goods_receipt_note_lines RESTRICT nullable (a return with no GRN history); item_id NOT NULL, quantity, uom_id, reason; returned_quantity numeric(14,3) DEFAULT 0; row_status CHECK IN (‘open’,‘closed’); created_at
  • goods_returns - posting one reverses the GRN’s stock/GL movement. id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NOT NULL; goods_return_request_id NULL; supplier_id NULL, supplier_name snapshot; return_date, reference, notes; status CHECK IN (‘draft’,‘posted’,‘cancelled’); posted/cancelled stamps, created_by, created_at
  • goods_return_lines - id, goods_return_id CASCADE, item_id NOT NULL, gl_account_id NOT NULL; quantity, unit_cost; discount_type, discount_value numeric(14,4), tax_group_id, tax_component_id, tax_code, tax_amount, net_amount, line_total; uom_id, cost_center_id; source_request_line_id → goods_return_request_lines RESTRICT; created_at. Note: batch_number/expiry_date/serial_numbers were dropped here - batch resolution goes via stock_movement_batch_allocations

ap_invoices

A/P Invoice and A/P Down Payment Invoice, both copied from a posted GRN - this is what records the real payable (a GRN only moves stock + a GR-IR clearing balance).

  • id, business_id, document_no - UNIQUE (business_id, document_no)
  • document_subtype NOT NULL - CHECK IN (‘invoice’,‘down_payment’)
  • branch_id NOT NULL, warehouse_id nullable (expense-only invoices)
  • source_grn_id → goods_receipt_notes NULL
  • supplier_id, supplier_name snapshot
  • invoice_date, reference, notes
  • payment_term_id → payment_terms
  • down_payment_percent numeric(5,2) NULL; down_payment_amount numeric(14,2)
  • status DEFAULT ‘initiated’ - CHECK IN (‘initiated’,‘pending_approval’,‘posted’,‘cancelled’)
  • items_subtotal, discount_total, tax_total, grand_total numeric(14,2)
  • paid_amount numeric(14,2) DEFAULT 0; payment_status CHECK IN (‘unpaid’,‘partial’,‘paid’)
  • Multi-currency: currency_id, exchange_rate, foreign_grand_total, foreign_paid_amount DEFAULT 0 - existing amount columns stay base-currency
  • terminal_id → pos_terminals NULL - only POS-originated rows set it
  • posted_at/posted_by, cancelled_at/cancelled_by, created_by, created_at
  • Index (business_id, invoice_date)

ap_invoice_lines

id, ap_invoice_id CASCADE; item_id NULL (expense lines); description DEFAULT ‘’; gl_account_id NOT NULL, quantity, unit_cost; discount_type, discount_value, tax_group_id, tax_component_id, tax_code, tax_amount, net_amount, line_total; uom_id, cost_center_id; source_grn_line_id → goods_receipt_note_lines RESTRICT; created_at

A/P Debit Memo (from Goods Return)

  • ap_debit_memos - copied from a posted Goods Return. id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NOT NULL; source_goods_return_id NULL; supplier_id, supplier_name, memo_date, reference, notes; status CHECK IN (‘draft’,‘posted’,‘cancelled’); posted/cancelled stamps, created_by, created_at
  • ap_debit_memo_lines - id, ap_debit_memo_id CASCADE, item_id NOT NULL, gl_account_id NOT NULL; quantity, unit_cost, discount_type, discount_value, tax_group_id, tax_component_id, tax_code, tax_amount, net_amount, line_total; uom_id, cost_center_id; source_goods_return_line_id → goods_return_lines RESTRICT; created_at

Landed Costs

Freight/duty allocated onto a posted GRN’s lines, raising unit cost.

  • landed_costs - id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, grn_id → goods_receipt_notes NOT NULL; cost_date NOT NULL; allocation_method DEFAULT ‘value’ CHECK IN (‘value’,‘quantity’); status DEFAULT ‘posted’ CHECK IN (‘posted’,‘cancelled’); remarks, cancelled_at/cancelled_by, created_by NOT NULL, created_at
  • landed_cost_charges - id, landed_cost_id CASCADE, description NOT NULL, amount numeric(14,2) NOT NULL, source_account_id → gl_accounts NOT NULL
  • landed_cost_allocations - id, landed_cost_id CASCADE, grn_line_id → goods_receipt_note_lines NOT NULL, item_id NOT NULL, gl_account_id NOT NULL, quantity, allocated_amount numeric(14,2), unit_cost_increase numeric(14,4)