2.2 KiB
Stock Dashboard - MySQL Tables
Exported from Omni Accounts Firebird database (STARGASHO.FDB).
Tables
products.sql — STK_STOCKITEM
Product master table. One row per product/stock item.
Key columns: STOCKCODE, DESCRIPTION, CATEGORY, PRODUCTGROUP, UOM, COSTPRICE, SELLINGPRICE1, ISACTVE
categories.sql — STK_STOCKCATEGORY
Lookup table for product categories. Join STK_STOCKITEM.CATEGORY → STCKCTGRYCDE.
Key columns: STCKCTGRYCDE, STCKCTGRYDESC
suppliers.sql — PL_SUPPLIERACCOUNT
Supplier master table. One row per supplier account.
Key columns: SUPLCDE, SUPLNME, address fields, contact fields
supplier_invoices.sql — PL_BILL
Supplier invoice headers. One row per invoice received from a supplier.
Key columns: REFNO, ACCNO (→ SUPLCDE), DOCDTETME, SUPLNME
supplier_invoice_lines.sql — PL_BILLTRAN
Supplier invoice line items. One row per product line on a supplier invoice.
Key columns: REFNO (→ PL_BILL), STOCKCODE (→ STK_STOCKITEM), QTYTOINVOICE, COSTPRICE
customers.sql — SL_CUSTOMERACCOUNT
Customer/debtor master table. One row per customer account.
Key columns: DBTRCDE, DBTRNME, REGION, DBTRCTGRY, CRLMT
customer_invoices.sql — SL_SALESINVOICE
Customer invoice headers. One row per invoice issued to a customer.
Key columns: REFNO, ACCNO (→ DBTRCDE), DOCDTETME, DBTRNME
customer_invoice_lines.sql — SL_SALESINVOICETRAN
Customer invoice line items. One row per product line on a customer invoice.
Key columns: REFNO (→ SL_SALESINVOICE), STOCKCODE (→ STK_STOCKITEM), QTYTOINVOICE, SELLINGPRICE, COSTPRICE
Joining the Tables
Supplier purchases per product:
SELECT p.DESCRIPTION, s.SUPLNME, il.QTYTOINVOICE, il.COSTPRICE, i.DOCDTETME
FROM supplier_invoice_lines il
JOIN supplier_invoices i ON il.REFNO = i.REFNO
JOIN suppliers s ON i.ACCNO = s.SUPLCDE
JOIN products p ON il.STOCKCODE = p.STOCKCODE;
Customer sales per product:
SELECT p.DESCRIPTION, c.DBTRNME, il.QTYTOINVOICE, il.SELLINGPRICE, i.DOCDTETME
FROM customer_invoice_lines il
JOIN customer_invoices i ON il.REFNO = i.REFNO
JOIN customers c ON i.ACCNO = c.DBTRCDE
JOIN products p ON il.STOCKCODE = p.STOCKCODE;