Costing Internals

You are the finance person at GadgetSphere who signs off gross margin, and you have three questions the stock reports do not answer: which cost did that cash bill actually carry?, why does the FIFO column disagree with the moving-average column on the same line?, and where is the cost-of-sales journal for last week? This page answers them from the code. It is an explanation, not a procedure — the one procedure it walks is correcting a wrong average with a Reset Moving Average document — and it is written for someone who can read a formula. Twenty minutes.

Meet GadgetSphere

GS-KV-01 receives 40 units of an ultraportable laptop on a purchase invoice at RM 3,000 each, then 60 more at RM 3,300. Over the month the branch sells 70. The stock report shows a moving-average cost of RM 3,180 and a FIFO cost of RM 3,300 on the last cash bill, and the month-end cost-of-sales journal is RM 219,000. All three numbers are right, and they come from three different mechanisms.

The ledger everything hangs off

Every stock-moving document line becomes one ledger line in bl_inv_txn_line when the document goes FINAL — a signed quantity, the transaction amount, and a set of cost columns. Nothing on this page happens at the moment you press FINAL: the document is queued, the ledger line is written by one job, its cost by the next, its FIFO by a third, and the balance you see is the last link of a chain. The stock balance page explains the chain and which document types move quantity; here we follow what happens to cost.

    flowchart LR
  DL["bl_fi_generic_doc_line<br/>the document line"]
  TL["bl_inv_txn_line<br/>the ledger line: qty, amount, cost_ma_*, cost_fifo_*"]
  TL2["bl_inv_txn_line<br/>next line for the same item"]
  SL["bl_inv_txn_sub_line<br/>one row per serial / batch / bin"]
  BK["bl_inv_fifo_lifo_register_basket<br/>a receipt still on hand: qty, unit price, remainder"]
  MV["bl_inv_fifo_movement<br/>an issue consuming part of a receipt"]
  RM["bl_inv_reset_moving_average_cost<br/>a Reset MA document"]
  CB["bl_inv_current_company_stock_balance<br/>qty_ledger, cost_ma_price, cost_fifo_price"]
  AT["bl_inv_stock_balance_applied_txn"]
  TL -->|guid_generic_doc_line| DL
  TL2 -->|prev_company_txn_guid / prev_location_txn_guid| TL
  SL -->|guid_inv_txn_line| TL
  MV -->|basket_guid| BK
  BK -.->|inv_txn_line_guid| TL
  MV -.->|inv_txn_line_guid| TL2
  TL -->|inv_reset_ma_cost_guid| RM
  AT -->|inv_txn_line_guid| TL
  CB -.->|the chain tip, re-projected| TL2

Solid arrows are foreign keys in the schema, read as “holds the key of”. The two dashed arrows from the FIFO tables are real columns with no foreign key declared, and the balance row is not linked to the ledger at all — it is recomputed from the newest line in the chain, which is why it can be refreshed and why a backdated line can move it.

Moving average — the cost every screen shows

The moving-average price on a line is computed by the balance job from the previous line in the chain, twice: once per location (cost_ma_price, cost_ma_amount, bal_qty) and once per company (…_company). Only the company figures are copied back onto the document line. The formula, as coded:

New quantity = previous quantity + (this line’s base quantity × its signum).
New value depends on the document type:
- For the value-changing types — purchase invoice, purchase return, stock adjustment, GRN stock-in, GIN stock-out, consignment GRN and GIN, purchase trade-in, and the calibration line — new value = previous value + this line’s amount_txn × signum. A purchase invoice adds what you paid. A purchase return relieves at its own price, not at the average.
- For everything else — sales invoice, cash bill, sales return, transfers, delivery orders — the line is cost-neutral: new value = previous value + previous value × (signed quantity ÷ previous quantity). A stock-out relieves at the current average; a sales return comes back at the current average, not at the price it left.
- Special case: if the previous value is (near) zero and the new quantity is positive, the value is re-seeded as previous price × new quantity — the “sticky price”.
New price = new value ÷ new quantity when both are non-zero. When the quantity or the value hits zero, the previous price is carried forward so the next receipt does not start from nothing. A negative balance is not guarded against: the proportional formula runs on it.

GadgetSphere’s laptop: after the two receipts, value RM 318,000 over 100 units → RM 3,180. Each of the 70 sales relieves 1/100th, then 1/99th … of the remaining value at RM 3,180; the price does not move until the next receipt. That is why the average is stable between purchases and jumps on each one.

Two more columns come from the same job: weighted average (cost_wa_*) is a monthly figure derived from the month’s brought-forward row, and last purchase cost is updated only by purchase invoices, from unit_price_net. LIFO, manual and replacement cost are never written — they are null on every ledger line in every tenant.

Backdating. If a document’s date puts its line before an existing line, the balance job sees it has a successor and queues a recalculation that re-walks the chain from the line’s predecessor with the same rules (a SQL procedure installed by the backend; a Java mirror of it exists only for tests) and then re-projects both balance tables from the new tip. Until that runs, the balance shows the pre-recalculation figure.

FIFO — a register of receipts, consumed oldest first

FIFO is not a column derived from the average; it is a second book:

Every receipt — purchase invoice, sales return, or a positive stock adjustment — creates a basket (bl_inv_fifo_lifo_register_basket): the quantity received, its unit price and amount, the first day of its month, and a running remainder (fifo_bal_qty, fifo_bal_amount). Nothing else creates one: a GRN stock-in, a transfer in or an opening balance loaded any other way is invisible to FIFO.
Every issue — sales invoice, cash bill, purchase return, or a negative adjustment — walks the item’s open baskets for the company (remainder > 0), oldest transaction date first, and consumes them: a movement row (bl_inv_fifo_movement) per basket touched, with the quantity taken (negative) and that basket’s unit price. A basket can be part-consumed; 60% of baskets on the platform are fully consumed. Transfers and delivery orders are not issues here either.
After each issue the line gets four figures: cost_fifo_price_per_txn_company and …amount_per_txn_company — what this line’s units actually cost under FIFO — and cost_fifo_price_company and …amount_company, the average of what is still on hand under FIFO. The same four are copied to the document line and the price to the company balance row.

GadgetSphere’s 70th sale: baskets are 40 @ RM 3,000 (consumed by sale 40) and 60 @ RM 3,300; sale 70 takes one from the second basket, so its per-transaction FIFO cost is RM 3,300 while the moving average on the same line says RM 3,180. Both are right; they answer different questions.

Three things to know before you rely on the FIFO columns:

Shortfall is silent. If an issue exceeds what the baskets hold, the walk stops when they run out; the uncovered units get no movement and the line’s per-transaction amount covers only the consumed part. No error is raised anywhere — there is no negative-stock or missing-cost validation in the backend.
FIFO runs where it is switched on, and it is rebuilt rather than trusted. The per-line FIFO job is a subscriber of the balance job, enabled per tenant in the job-template configuration; no code path queues it by name. When a backdated line lands, the balance job marks the item dirty and a separate rebuild job replays the item’s whole FIFO history — deleting its baskets and movements, rewriting the chain links and running balances on the ledger, and regenerating everything from monthly snapshots forward. That rebuild is the only writer of the location-level cost_fifo_price and the only place a sales return is costed at the original sale’s price. On 28 of the 90 tenants the register holds rows; it is populated in every one of them entirely by issues consuming receipts, which is what you would expect.
LIFO does not exist. The register carries lifo_bal_* columns and a LIFO helper is in the code, but its call is commented out, and the helper writes the FIFO columns anyway. The LIFO balances on a basket are set at creation and then only ever overwritten with the FIFO remainder by the rebuild.

Cost of sales — a period job, not a document posting

The cash bill’s journal has no cost line. The SALES posting handler writes debtor, sales, discount, return, tax and exchange lines only; the stock account is touched by a GRN stock-in and by a stock adjustment, and by nothing sales-side. The amount_cogs column on the ledger line is copied from whatever the document line carried — the POS or the API put it there — and the backend never calculates it.

Cost of goods sold is a period-end journal created on demand from the Financial Report applet: it deletes any earlier COGS journal in the range and posts opening stock + purchases + purchase returns − closing stock between the company’s default COGS and STOCK_BALANCE GL codes (with raw-material, work-in-progress, finished-goods and trade-in variants). Opening and closing stock are, per item, the last ledger line before the date: quantity on hand × a cost price. Which cost price is the one setting on this whole page that you choose: inventory_closing_base_on on the company — MA_COST (the default; 84 of 90 tenants’ companies leave it empty and get this), WA_COST, FIFO_COST (one tenant), LIFO_COST, REPLACEMENT_COST or MANUAL_COST. Choosing FIFO_COST values closing stock from the FIFO columns, so it is only meaningful where the FIFO job is enabled and rebuilt; the last three value it from columns nothing writes. A manual month-end closing stock figure, if entered, overrides the computed one.

GadgetSphere’s RM 219,000: opening 0 + purchases RM 318,000 − closing 30 units × RM 3,180 = RM 95,400 → RM 222,600 under MA; under FIFO closing is 30 × RM 3,300 = RM 99,000 → RM 219,000. The difference between the two is the whole reason the setting exists.

Serial numbers, batches and bins — the sub-line

Under each ledger line, bl_inv_txn_sub_line holds one row per serial number (txn_type SERIAL_NUMBER, quantity ±1), per batch (BATCH_NUMBER, quantity = the batch quantity with the line’s sign, issue and expiry dates) or per bin (BIN_NUMBER, the container quantity). The identifier is the text in reference_key_1 — the table’s guid_serial_number_hdr, guid_batch_hdr and guid_bin_hdr columns are never written — and a serial’s or batch’s balance is the sum of quantity_base over rows with the same text. Eleven million such rows exist on 28 tenants; the serial-number applets and the batch-expiry reports read them, and the balance job maintains the serial and batch header balances from them. A serial number typed with a different case or a trailing space is a different serial.

Correcting a wrong average — Reset Moving Average

By the end of this procedure the laptop’s moving average at GS is what it should be, the balance sheet reflects the change, and you know which journal did it. Ten minutes, finance only — the stock-adjustment applet page explains how to hide the menus from everyone else.

Stock Adjustment → Reset MA (company-wide) or Reset MA By Location → select company and item → date, quantity on hand, current cost, new cost → Save → Final.

What the document does at FINAL, in order:

It writes one informational line per location with that location’s share.
It queues the inventory job, which appends a RESET_MA ledger line: quantity zero, amount = the adjustment (new total value − current value), typed as a stock adjustment so the moving-average formula treats it as value-changing. New value = old value + adjustment over the same quantity, so the price lands on your new cost; if the quantity on hand is zero the price is pinned to the new unit cost directly.
It posts the journal at once: two lines for the adjustment amount between the company’s STOCK_BALANCE default GL code and its RESET_MA default GL code (or a GL code you chose on the header). Positive adjustment: debit stock, credit the reset account; negative: the reverse. If the company has no RESET_MA default, FINAL fails with COMPANY_DEFAULT_GL_CODE_NOT_EXIST; a second FINAL fails with RESET_MA_HAS_BEEN_POSTED.

Three statuses record it — posting_status (FINAL), posting_inventory (POSTED once the ledger line exists) and posting_journal (POSTED once the journal exists) — and the ledger line carries the reset’s key, so you can always find which reset moved a cost. For a batch of items use File Import Reset MA; a document line flagged for an automatic rebate adjustment creates and finalises a reset on its own (txn_type AUTO_ADJUSTED_REBATE).

What success looks like

Open Stock Balance for the laptop at GS: the MA price is your new cost. Open the ledger (Stock Card) and find the RESET_MA line with quantity 0 and the adjustment amount. Open the journal for the date: two lines, STOCK-… against your reset account, for the same amount. Run the Financial Report’s cost of goods sold for the month and see closing stock move by quantity × the change. Five minutes.

Common mistakes

Mistake	What you see	Fix
Expecting a cost line on the cash bill journal	None; margin reports look wrong until month end	Run the period COGS journal from the Financial Report applet; cost of sales is periodic
Loading opening stock with no cost	Average 0; the first sales relieve nothing; the price is re-seeded by the first receipt	Reset MA after the load (see the stock management guide)
Choosing `FIFO_COST` for closing stock on a tenant where the FIFO job is not enabled	Closing stock 0, COGS equals all purchases	Leave `inventory_closing_base_on` empty (MA) or have the FIFO job enabled and rebuilt first
Choosing `LIFO_COST`, `MANUAL_COST` or `REPLACEMENT_COST`	Closing stock 0	Nothing writes those columns; use MA, WA or FIFO
Selling more than the FIFO baskets hold (an opening balance loaded without a purchase invoice)	FIFO per-transaction cost covers part of the line, silently	Load opening stock as a receipt FIFO recognises (purchase invoice or positive stock adjustment)
Backdating a purchase invoice and reading the average immediately	Old average; FIFO not yet rebuilt	Wait for the recalculation and rebuild jobs; check the stock card again
Returning goods and expecting the original cost back	Return valued at today’s average (MA)	That is the rule; FIFO restores the original price only when the return is linked to the sale and the register is rebuilt
Finalising a Reset MA in a company with no `RESET_MA` default GL code	COMPANY_DEFAULT_GL_CODE_NOT_EXIST	Add the default GL code in the Organisation applet, then FINAL again