​

📦 Inventory Module (inv_*)

​

1. Module Purpose

• Menyimpan material master (inv_mat).
• Menyimpan stok per branch/material (inv_stock).
• Menyimpan semua pergerakan stok (inv_move).
• Menyimpan hasil opname & penyesuaian (inv_adj, inv_adj_line).

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 inv_mat

• Fields: company_id, code, name, uom, category, is_active.
• Unik per company: UNIQUE(company_id, code).

​

3.2 inv_stock

• Fields: branch_id, mat_id, qty, last_updated_at.
• FK:
  • branch_id → core_branch.id
  • mat_id → inv_mat.id

​

3.3 inv_move

• Fields: branch_id, mat_id, move_date, type, qty, ref_type, ref_id, note.
• Contoh type: IN_PURCHASE, OUT_SALES, OUT_POS, ADJ_IN, ADJ_OUT, TRANSFER_IN, TRANSFER_OUT.

​

3.4 inv_adj & inv_adj_line

• inv_adj: branch_id, adj_date, reason, status.
• inv_adj_line:
  • FK: adj_id → inv_adj.id, mat_id → inv_mat.id.
  • Fields: system_qty, physical_qty, diff_qty.

​

4. Business Flows

​

4.1 Purchase Receipt → Stock In

1. Goods Receipt di po_gr/po_gr_line.
2. Untuk tiap line, sistem membuat inv_move dengan type = 'IN_PURCHASE'.
3. inv_stock diupdate berdasarkan agregasi movement.

​

4.2 Sales / POS → Stock Out

1. Penjualan di so_invoice_line atau so_pos_line.
2. Sistem membuat inv_move dengan type = 'OUT_SALES' atau OUT_POS.
3. inv_stock turun sesuai kuantitas.

​

4.3 Stock Opname & Adjustment

1. Opname fisik menghasilkan record di inv_adj + inv_adj_line.
2. Selisih antara physical_qty dan system_qty dihitung sebagai diff_qty.
3. Sistem membuat movement ADJ_IN atau ADJ_OUT di inv_move.

​

5. Example Reports (SQL)

​

5.1 Stock Card per Material & Branch

SELECT
  m.move_date,
  m.type,
  m.qty,
  m.ref_type,
  m.ref_id
FROM inv_move m
WHERE m.branch_id = :branch_id
  AND m.mat_id = :mat_id
  AND m.move_date BETWEEN :start_date AND :end_date
ORDER BY m.move_date, m.id;

​

5.2 Stock Snapshot per Branch

SELECT
  b.code AS branch_code,
  mat.code AS material_code,
  mat.name AS material_name,
  s.qty
FROM inv_stock s
JOIN core_branch b ON b.id = s.branch_id
JOIN inv_mat mat  ON mat.id = s.mat_id
WHERE b.company_id = :company_id
ORDER BY b.code, mat.code;

​

5.3 Selisih Stock dari Opname

SELECT
  a.branch_id,
  l.mat_id,
  mat.code AS material_code,
  l.system_qty,
  l.physical_qty,
  l.diff_qty
FROM inv_adj a
JOIN inv_adj_line l ON l.adj_id = a.id
JOIN inv_mat mat    ON mat.id = l.mat_id
WHERE a.branch_id = :branch_id
  AND a.adj_date BETWEEN :start_date AND :end_date;