​

🛒 Procurement Module (po_*)

​

1. Module Purpose

• Menyimpan master vendor per company.
• Menangani lifecycle PR → PO → GR.
• Memberikan data dasar untuk pembentukan AP invoice (fi_ap_inv) dan movement stok (inv_move).

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 po_vendor

• Fields: company_id, code, name, address, tax_id, is_active.
• FK: company_id → core_company.id.

​

3.2 PR & PO

• po_req
  • Fields: company_id, branch_id, req_no, req_date, status, requester_id.
• po_req_line
  • FK: req_id → po_req.id.
  • Fields: mat_id → inv_mat.id, qty, uom, note.
• po_order
  • Fields: company_id, branch_id, vendor_id, po_no, po_date, status, currency.
  • FK: vendor_id → po_vendor.id.
• po_order_line
  • FK: po_id → po_order.id.
  • Fields: mat_id, qty, unit_price, amount, tax_id.

​

3.3 Goods Receipt

• po_gr
  • Fields: company_id, branch_id, vendor_id, gr_no, gr_date, status.
• po_gr_line
  • FK: gr_id → po_gr.id.
  • Fields: po_line_id, mat_id, qty_received, qty_rejected.
• Integrasi ke modul lain:
  • Setiap po_gr_line akan memicu inv_move dengan type = 'IN_PURCHASE'.
  • GR dapat menjadi basis pembentukan fi_ap_inv.

​

4. Business Flows

​

4.1 PR → PO

1. User cabang membuat PR (po_req + po_req_line).
2. Buyer melakukan review dan approval PR.
3. PR yang approved dikonversi menjadi PO (po_order + po_order_line).

​

4.2 PO → GR → Inventory & AP

1. Barang datang, staf gudang membuat GR (po_gr + po_gr_line).
2. Sistem:
   • Membuat movement IN_PURCHASE di inv_move.
   • Mengupdate inv_stock.
   • Menyiapkan data dasar untuk fi_ap_inv (AP invoice).

​

5. Example Reports (SQL)

​

5.1 Outstanding PR (Belum di-PO)

SELECT
  r.req_no,
  r.req_date,
  b.code  AS branch_code,
  u.email AS requester,
  r.status
FROM po_req r
JOIN core_branch b ON b.id = r.branch_id
JOIN core_user   u ON u.id = r.requester_id
WHERE r.company_id = :company_id
  AND r.status IN ('open', 'approved')
  AND NOT EXISTS (
    SELECT 1
    FROM po_order o
    WHERE o.company_id = r.company_id
      AND o.ref_req_id = r.id
  );

​

5.2 PO vs GR (Monitoring Penerimaan Barang)

SELECT
  o.po_no,
  o.po_date,
  v.name        AS vendor_name,
  l.mat_id,
  mat.code      AS material_code,
  l.qty         AS qty_ordered,
  IFNULL(SUM(grl.qty_received), 0) AS qty_received
FROM po_order o
JOIN po_order_line l ON l.po_id = o.id
JOIN po_vendor v     ON v.id = o.vendor_id
JOIN inv_mat mat     ON mat.id = l.mat_id
LEFT JOIN po_gr g    ON g.po_id = o.id
LEFT JOIN po_gr_line grl ON grl.gr_id = g.id AND grl.mat_id = l.mat_id
WHERE o.company_id = :company_id
GROUP BY o.id, l.id;

​

5.3 Lead Time PO (PO Date → GR Date)

SELECT
  o.po_no,
  o.po_date,
  MIN(g.gr_date) AS first_gr_date,
  DATEDIFF(MIN(g.gr_date), o.po_date) AS lead_time_days
FROM po_order o
JOIN po_gr g ON g.po_id = o.id
WHERE o.company_id = :company_id
GROUP BY o.id;