​

🧾 Sales & POS Module (so_*)

​

1. Module Purpose

• Menyimpan master customer.
• Menangani lifecycle SO → Invoice → (AR) dan POS.
• Menjadi sumber data penjualan untuk laporan penjualan, margin, dan performa cabang.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 so_customer

• Fields: company_id, code, name, phone, email, address, is_active.
• FK: company_id → core_company.id.
• Dipakai oleh: fi_ar_inv.cust_id, so_order.cust_id.

​

3.2 Sales Order & Invoice

• so_order
  • Fields: company_id, branch_id, cust_id, so_no, so_date, status.
• so_order_line
  • FK: so_id → so_order.id.
  • Fields: mat_id → inv_mat.id, qty, unit_price, discount, amount.
• so_invoice
  • Fields: company_id, branch_id, cust_id, inv_no, inv_date, status, total.
• so_invoice_line
  • FK: so_inv_id → so_invoice.id.
  • Fields mirip dengan so_order_line.

​

3.3 POS

• so_pos
  • Fields: company_id, branch_id, pos_no, tx_date, cashier_id, total, payment_method.
• so_pos_line
  • FK: pos_id → so_pos.id.
  • Fields: mat_id, qty, unit_price, discount, amount.
• Integrasi:
  • Transaksi so_invoice akan memicu AR (fi_ar_inv).
  • so_invoice_line / so_pos_line memicu movement stok di inv_move.

​

4. Business Flows

​

4.1 SO → Invoice → AR → GL

1. Sales Order dibuat di so_order + so_order_line.
2. Setelah dikonfirmasi/fulfilled, sistem buat so_invoice + so_invoice_line.
3. Berdasarkan invoice, sistem membentuk fi_ar_inv (AR) dan posting ke GL.
4. so_invoice_line memicu inv_move (OUT_SALES).

​

4.2 POS Flow

1. Kasir membuat transaksi di so_pos + so_pos_line.
2. Sistem langsung mengurangi stok via inv_move (OUT_POS).
3. Revenue bisa dicatat via fi_cash_tx dan di-post ke GL.

​

5. Example Reports (SQL)

​

5.1 Laporan Penjualan per Hari (Invoice + POS)

SELECT
  d.tx_date,
  SUM(d.total_amount) AS total_sales
FROM (
  SELECT
    DATE(i.inv_date) AS tx_date,
    i.total          AS total_amount
  FROM so_invoice i
  WHERE i.company_id = :company_id

  UNION ALL

  SELECT
    DATE(p.tx_date) AS tx_date,
    p.total         AS total_amount
  FROM so_pos p
  WHERE p.company_id = :company_id
) d
WHERE d.tx_date BETWEEN :start_date AND :end_date
GROUP BY d.tx_date
ORDER BY d.tx_date;

​

5.2 Top 10 Produk Terlaris (Quantity)

SELECT
  mat.code AS material_code,
  mat.name AS material_name,
  SUM(l.qty) AS total_qty
FROM so_invoice_line l
JOIN so_invoice i ON i.id = l.so_inv_id
JOIN inv_mat mat  ON mat.id = l.mat_id
WHERE i.company_id = :company_id
  AND i.inv_date BETWEEN :start_date AND :end_date
GROUP BY mat.id
ORDER BY total_qty DESC
LIMIT 10;

​

5.3 Penjualan per Branch

SELECT
  b.code AS branch_code,
  SUM(i.total) AS total_sales
FROM so_invoice i
JOIN core_branch b ON b.id = i.branch_id
WHERE i.company_id = :company_id
  AND i.inv_date BETWEEN :start_date AND :end_date
GROUP BY b.id
ORDER BY total_sales DESC;