​

💰 Finance Module (fi_*)

​

1. Module Purpose

• Menyimpan AR & AP invoice secara terstruktur.
• Mengelola pembayaran piutang & hutang.
• Mengelola transaksi kas (cash in/out) di luar AR/AP.
• Menyediakan basis data untuk laporan aging, cashflow, dan analisa piutang/hutang.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 AR (Accounts Receivable)

• fi_ar_inv
  • Key fields: id, company_id, cust_id, inv_no, inv_date, due_date, currency, total, status.
  • FK: company_id → core_company.id, cust_id → so_customer.id.
• fi_ar_inv_line
  • FK: ar_id → fi_ar_inv.id.
  • Fields contoh: mat_id, description, qty, unit_price, amount, tax_id.
• fi_ar_pay
  • FK: ar_id → fi_ar_inv.id.
  • Fields: pay_date, amount, method, ref_no.

​

3.2 AP (Accounts Payable)

• fi_ap_inv
  • Key fields: id, company_id, vendor_id, inv_no, inv_date, due_date, currency, total, status.
  • FK: company_id → core_company.id, vendor_id → po_vendor.id.
• fi_ap_inv_line
  • FK: ap_id → fi_ap_inv.id.
• fi_ap_pay
  • FK: ap_id → fi_ap_inv.id.

​

3.3 Cash Transactions

• fi_cash_tx
  • Fields: company_id, tx_date, type (CASH_IN, CASH_OUT), amount, currency, note, ref_type, ref_id.

​

4. Business Flows

​

4.1 AR Flow (Invoice → Payment → GL)

1. Sales invoice dibuat di so_invoice/so_invoice_line.
2. Sistem membentuk AR invoice di fi_ar_inv/fi_ar_inv_line.
3. Ketika pembayaran diterima, catat di fi_ar_pay.
4. AR invoice & payment dipost ke gl_entry + gl_line (debit piutang, kredit revenue/kas).

​

4.2 AP Flow (Invoice → Payment → GL)

1. Goods Receipt dari procurement → basis fi_ap_inv.
2. AP invoice disimpan di fi_ap_inv/fi_ap_inv_line.
3. Pembayaran ke vendor di fi_ap_pay.
4. Dipost ke GL (debit hutang, kredit kas, dll.).

​

4.3 Cash Transaction Flow

1. Transaksi kas langsung (petty cash, biaya kecil, dll.) direkam di fi_cash_tx.
2. Setiap fi_cash_tx ditranslasikan ke gl_entry + gl_line sesuai mapping COA.

​

5. Example Reports (SQL)

​

5.1 AR Aging Report (Dasar)

SELECT
  a.company_id,
  a.inv_no,
  c.name                       AS customer_name,
  a.inv_date,
  a.due_date,
  a.total,
  DATEDIFF(CURDATE(), a.due_date) AS days_overdue,
  CASE
    WHEN DATEDIFF(CURDATE(), a.due_date) <= 0 THEN 'Not due'
    WHEN DATEDIFF(CURDATE(), a.due_date) <= 30 THEN '0-30'
    WHEN DATEDIFF(CURDATE(), a.due_date) <= 60 THEN '31-60'
    WHEN DATEDIFF(CURDATE(), a.due_date) <= 90 THEN '61-90'
    ELSE '>90'
  END AS aging_bucket
FROM fi_ar_inv a
JOIN so_customer c ON c.id = a.cust_id
WHERE a.company_id = :company_id
  AND a.status = 'open';

​

5.2 AP Aging Report

SELECT
  a.company_id,
  a.inv_no,
  v.name                       AS vendor_name,
  a.inv_date,
  a.due_date,
  a.total,
  DATEDIFF(CURDATE(), a.due_date) AS days_overdue
FROM fi_ap_inv a
JOIN po_vendor v ON v.id = a.vendor_id
WHERE a.company_id = :company_id
  AND a.status = 'open';

​

5.3 Cashflow Sederhana per Hari

SELECT
  tx_date,
  SUM(CASE WHEN type = 'CASH_IN'  THEN amount ELSE 0 END) AS cash_in,
  SUM(CASE WHEN type = 'CASH_OUT' THEN amount ELSE 0 END) AS cash_out,
  SUM(CASE WHEN type = 'CASH_IN'  THEN amount ELSE -amount END) AS net_cash
FROM fi_cash_tx
WHERE company_id = :company_id
  AND tx_date BETWEEN :start_date AND :end_date
GROUP BY tx_date
ORDER BY tx_date;