​

🌍 Global / Region & Policy Module (rg_*)

​

1. Module Purpose

• Menyimpan master region (negara/wilayah).
• Menyimpan master currency.
• Menyimpan exchange rate per tanggal.
• Menyimpan kebijakan (policy) dan assignment-nya.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 Region & Currency

• rg_region:
  • Fields: code, name, type (country, state, city), parent_region_id (optional self-FK).
• rg_currency:
  • Fields: code (ISO), name, symbol, is_default.
• rg_rate:
  • Fields: from_currency, to_currency, rate_date, rate.
  • from_currency, to_currency → rg_currency.code (logical FK).

​

3.2 Policy & Assignment

• rg_policy:
  • Fields: code, name, description, category.
• rg_policy_assign:
  • Fields: policy_id, target_type, target_id, effective_start, effective_end.
  • FK: policy_id → rg_policy.id.

​

4. Business Flows

​

4.1 Konversi Mata Uang (Konseptual)

1. Transaksi dicatat dalam currency transaksi (mis. USD).
2. Laporan konsolidasi membutuhkan currency lain (mis. IDR).
3. Aplikasi menggunakan rg_rate untuk konversi saat reporting.

​

4.2 Policy Assignment

1. Policy dibuat di rg_policy (mis. POLICY_APPROVAL_LIMIT).
2. Policy di-assign ke target tertentu (company/branch/role) lewat rg_policy_assign.

​

5. Example Reports (SQL)

​

5.1 Effective Exchange Rate per Hari

SELECT
  r.from_currency,
  r.to_currency,
  r.rate_date,
  r.rate
FROM rg_rate r
WHERE r.rate_date = :rate_date
  AND r.from_currency = :from_currency
  AND r.to_currency   = :to_currency;

​

5.2 Policy Aktif per Company

SELECT
  p.code,
  p.name,
  a.target_type,
  a.target_id,
  a.effective_start,
  a.effective_end
FROM rg_policy p
JOIN rg_policy_assign a ON a.policy_id = p.id
WHERE a.target_type = 'company'
  AND a.target_id   = :company_id
  AND (a.effective_start IS NULL OR a.effective_start <= CURDATE())
  AND (a.effective_end   IS NULL OR a.effective_end   >= CURDATE());