​

📒 General Ledger Module (gl_*)

​

1. Module Purpose

• Menyimpan Chart of Accounts (COA) per company.
• Menyimpan header journal (gl_entry) sebagai dokumen keuangan.
• Menyimpan detail baris jurnal (gl_line) sebagai universal journal line.
• Menjadi single source of truth untuk laporan keuangan dan audit.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 gl_coa

• Fields utama:
  • company_id → core_company.id
  • code (unique per company)
  • name
  • type (asset, liability, equity, revenue, expense)
  • is_postable (boleh dipost langsung atau hanya header)

​

3.2 gl_entry

• Fields utama:
  • company_id → core_company.id
  • entry_date
  • ref_type (mis. AR_INV, AP_INV, CASH_TX, PAYROLL)
  • ref_id (FK logis ke tabel sumber)
  • note

​

3.3 gl_line

• Fields utama:
  • entry_id → gl_entry.id
  • coa_id → gl_coa.id
  • debit, credit
  • branch_id (opsional, untuk segmentasi per cabang)
  • cost_center_id (opsional jika ada dimensi tambahan)
• Aturan kunci:
  • Untuk setiap entry_id, total SUM(debit) = SUM(credit).

​

4. Business Flows

​

4.1 Posting AR Invoice

1. AR invoice dibuat di fi_ar_inv.
2. Sistem generate gl_entry dengan ref_type = 'AR_INV', ref_id = fi_ar_inv.id.
3. Sistem generate gl_line:
   • Debit: Piutang usaha (COA AR)
   • Kredit: Pendapatan (COA revenue)

​

4.2 Posting AP Invoice

1. AP invoice dibuat di fi_ap_inv.
2. Sistem generate gl_entry (ref_type = 'AP_INV').
3. gl_line:
   • Debit: Beban/Inventory
   • Kredit: Hutang usaha.

​

4.3 Posting Payment

• Payment AR/AP atau cash transaction memicu gl_entry baru dengan kombinasi debit/kredit sesuai mapping COA.

​

5. Example Reports (SQL)

​

5.1 Trial Balance per Periode

SELECT
  c.company_id,
  c.code          AS account_code,
  c.name          AS account_name,
  c.type,
  SUM(l.debit)  AS total_debit,
  SUM(l.credit) AS total_credit,
  SUM(l.debit - l.credit) AS balance
FROM gl_line l
JOIN gl_coa c   ON c.id = l.coa_id
JOIN gl_entry e ON e.id = l.entry_id
WHERE e.company_id = :company_id
  AND e.entry_date BETWEEN :start_date AND :end_date
GROUP BY c.company_id, c.code, c.name, c.type
ORDER BY c.code;

​

5.2 General Ledger Detail per Account

SELECT
  e.entry_date,
  e.ref_type,
  e.ref_id,
  e.note,
  l.debit,
  l.credit
FROM gl_line l
JOIN gl_entry e ON e.id = l.entry_id
JOIN gl_coa c   ON c.id = l.coa_id
WHERE e.company_id = :company_id
  AND c.code = :account_code
  AND e.entry_date BETWEEN :start_date AND :end_date
ORDER BY e.entry_date, e.id;

​

5.3 Balance Sheet Snapshot (Sederhana)

SELECT
  c.type,
  SUM(l.debit - l.credit) AS balance
FROM gl_line l
JOIN gl_coa c   ON c.id = l.coa_id
JOIN gl_entry e ON e.id = l.entry_id
WHERE e.company_id = :company_id
  AND e.entry_date <= :as_of_date
  AND c.type IN ('asset', 'liability', 'equity')
GROUP BY c.type;