125 lines
7.4 KiB
SQL
125 lines
7.4 KiB
SQL
INSERT INTO roles (code, name) VALUES
|
|
('OWNER', 'Owner'),
|
|
('PURCHASING', 'Admin Purchasing'),
|
|
('WAREHOUSE', 'Admin Gudang'),
|
|
('QC', 'Tim Sortasi'),
|
|
('SALES', 'Admin Sales');
|
|
|
|
INSERT INTO users (role_id, name, email, phone) VALUES
|
|
(1, 'Owner Walet', 'owner@walet.local', '081200000001'),
|
|
(2, 'Purchasing Walet', 'purchasing@walet.local', '081200000002'),
|
|
(3, 'Warehouse Walet', 'warehouse@walet.local', '081200000003'),
|
|
(4, 'QC Walet', 'qc@walet.local', '081200000004'),
|
|
(5, 'Sales Walet', 'sales@walet.local', '081200000005');
|
|
|
|
INSERT INTO units (code, name) VALUES
|
|
('KG', 'Kilogram'),
|
|
('PCS', 'Pieces');
|
|
|
|
INSERT INTO item_types (code, name, description) VALUES
|
|
('JNS-A', 'Jenis A', 'Jenis sarang walet A'),
|
|
('JNS-B', 'Jenis B', 'Jenis sarang walet B'),
|
|
('JNS-C', 'Jenis C', 'Jenis sarang walet C');
|
|
|
|
INSERT INTO item_grades (code, name, rank_order, description) VALUES
|
|
('GRD-A', 'Grade A', 1, 'Grade tertinggi'),
|
|
('GRD-B', 'Grade B', 2, 'Grade menengah'),
|
|
('GRD-C', 'Grade C', 3, 'Grade bawah'),
|
|
('REJECT', 'Reject', 99, 'Tidak layak jual');
|
|
|
|
INSERT INTO warehouses (code, name, address) VALUES
|
|
('WH-PUSAT', 'Gudang Pusat', 'Jl. Gudang Pusat'),
|
|
('WH-CBG1', 'Gudang Cabang 1', 'Jl. Cabang 1');
|
|
|
|
INSERT INTO warehouse_locations (warehouse_id, code, name, location_type) VALUES
|
|
(1, 'A1', 'Rak A1', 'RACK'),
|
|
(1, 'A2', 'Rak A2', 'RACK'),
|
|
(1, 'SORT', 'Area Sortasi', 'PROCESS'),
|
|
(2, 'B1', 'Rak B1', 'RACK');
|
|
|
|
INSERT INTO adjustment_reasons (code, name, category) VALUES
|
|
('SHRINK', 'Shrinkage', 'SHRINKAGE'),
|
|
('DMG', 'Damage', 'DAMAGE'),
|
|
('REGRADE', 'Regrade', 'REGRADE'),
|
|
('OPNAME', 'Stock Opname Selisih', 'ADJUSTMENT');
|
|
|
|
INSERT INTO suppliers (code, name, phone, email, bank_name, bank_account_number, address) VALUES
|
|
('SUP-A', 'Supplier A', '081310000001', 'sup-a@walet.local', 'BCA', '1234567890', 'Bogor'),
|
|
('SUP-B', 'Supplier B', '081310000002', 'sup-b@walet.local', 'Mandiri', '9876543210', 'Bandung');
|
|
|
|
INSERT INTO customers (code, name, phone, email, bank_name, bank_account_number, address) VALUES
|
|
('CUST-A', 'Customer A', '081320000001', 'cust-a@walet.local', 'BRI', '111222333444', 'Jakarta'),
|
|
('CUST-B', 'Customer B', '081320000002', 'cust-b@walet.local', 'BNI', '555666777888', 'Surabaya');
|
|
|
|
INSERT INTO purchases (purchase_no, supplier_id, purchase_date, supplier_invoice_no, status, created_by)
|
|
VALUES
|
|
('PO-20260428-001', 1, '2026-04-28', 'INV-SUP-A-001', 'SUBMITTED', 2),
|
|
('PO-20260428-002', 2, '2026-04-28', 'INV-SUP-B-001', 'SUBMITTED', 2);
|
|
|
|
INSERT INTO purchase_lines (purchase_id, item_type_id, item_grade_id, qty_ordered, unit_id, unit_price, subtotal, classification_status)
|
|
VALUES
|
|
(1, 1, 1, 50.000, 1, 18000000, 900000000, 'FINAL'),
|
|
(1, 1, 2, 20.000, 1, 16000000, 320000000, 'FINAL'),
|
|
(2, 1, NULL, 40.000, 1, 17500000, 700000000, 'PROVISIONAL');
|
|
|
|
INSERT INTO receipts (receipt_no, purchase_id, supplier_id, receipt_date, status, received_by)
|
|
VALUES
|
|
('RCV-20260428-001', 1, 1, '2026-04-28', 'FINALIZED', 3),
|
|
('RCV-20260428-002', 2, 2, '2026-04-28', 'FINALIZED', 3);
|
|
|
|
INSERT INTO receipt_lines (receipt_id, purchase_line_id, item_type_id, item_grade_id, qty_received, qty_accepted, qty_rejected, unit_id, unit_cost)
|
|
VALUES
|
|
(1, 1, 1, 1, 50.000, 50.000, 0, 1, 18000000),
|
|
(1, 2, 1, 2, 20.000, 20.000, 0, 1, 16000000),
|
|
(2, 3, 1, NULL, 40.000, 40.000, 0, 1, 17500000);
|
|
|
|
INSERT INTO inventory_lots (
|
|
lot_code, parent_lot_id, source_type, source_ref_id, supplier_id, purchase_id, purchase_line_id, receipt_id, receipt_line_id,
|
|
item_type_id, item_grade_id, warehouse_id, warehouse_location_id, original_qty, available_qty, unit_id, unit_cost, received_at, status, qr_code_value, barcode_value
|
|
) VALUES
|
|
('LOT-260428-SUPA-001', NULL, 'PURCHASE', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 50.000, 30.000, 1, 18000000, '2026-04-28 09:00:00', 'ACTIVE', 'LOT-260428-SUPA-001', 'LOT-260428-SUPA-001'),
|
|
('LOT-260428-SUPA-002', NULL, 'PURCHASE', 2, 1, 1, 2, 1, 2, 1, 2, 1, 2, 20.000, 20.000, 1, 16000000, '2026-04-28 09:10:00', 'ACTIVE', 'LOT-260428-SUPA-002', 'LOT-260428-SUPA-002'),
|
|
('LOT-260428-SUPB-001', NULL, 'PURCHASE', 3, 2, 2, 3, 2, 3, 1, 2, 1, 3, 40.000, 0.000, 1, 17500000, '2026-04-28 10:00:00', 'CLOSED', 'LOT-260428-SUPB-001', 'LOT-260428-SUPB-001'),
|
|
('LOT-260428-SUPB-001-S1', 3, 'SORTING', 1, 2, 2, 3, 2, 3, 1, 1, 1, 1, 18.000, 8.000, 1, 17500000, '2026-04-28 11:00:00', 'ACTIVE', 'LOT-260428-SUPB-001-S1', 'LOT-260428-SUPB-001-S1'),
|
|
('LOT-260428-SUPB-001-S2', 3, 'SORTING', 1, 2, 2, 3, 2, 3, 1, 2, 1, 2, 12.000, 12.000, 1, 17500000, '2026-04-28 11:00:00', 'ACTIVE', 'LOT-260428-SUPB-001-S2', 'LOT-260428-SUPB-001-S2'),
|
|
('LOT-260428-SUPB-001-S3', 3, 'SORTING', 1, 2, 2, 3, 2, 3, 2, 1, 1, 2, 7.000, 7.000, 1, 17500000, '2026-04-28 11:00:00', 'ACTIVE', 'LOT-260428-SUPB-001-S3', 'LOT-260428-SUPB-001-S3');
|
|
|
|
INSERT INTO sorting_sessions (sorting_no, source_lot_id, sorting_date, input_qty, output_qty, shrinkage_qty, notes, sorted_by)
|
|
VALUES
|
|
('SRT-20260428-001', 3, '2026-04-28 11:00:00', 40.000, 37.000, 3.000, 'Sortasi batch campuran supplier B', 4);
|
|
|
|
INSERT INTO sorting_results (sorting_session_id, result_lot_id, item_type_id, item_grade_id, qty_result, unit_cost)
|
|
VALUES
|
|
(1, 4, 1, 1, 18.000, 17500000),
|
|
(1, 5, 1, 2, 12.000, 17500000),
|
|
(1, 6, 2, 1, 7.000, 17500000);
|
|
|
|
INSERT INTO sales (sales_no, customer_id, sales_date, status, created_by)
|
|
VALUES
|
|
('SLS-20260428-001', 1, '2026-04-28', 'CONFIRMED', 5);
|
|
|
|
INSERT INTO sales_lines (sales_id, item_type_id, item_grade_id, qty_sold, unit_id, selling_price, subtotal, costing_total, gross_margin)
|
|
VALUES
|
|
(1, 1, 1, 30.000, 1, 22000000, 660000000, 550000000, 110000000);
|
|
|
|
INSERT INTO sales_allocations (sales_line_id, inventory_lot_id, qty_allocated, unit_cost, total_cost, allocated_at, allocated_by)
|
|
VALUES
|
|
(1, 1, 20.000, 18000000, 360000000, '2026-04-28 13:00:00', 5),
|
|
(1, 4, 10.000, 19000000, 190000000, '2026-04-28 13:00:00', 5);
|
|
|
|
INSERT INTO inventory_movements (movement_no, movement_type, inventory_lot_id, related_lot_id, warehouse_id, warehouse_location_id, qty_in, qty_out, balance_after, unit_cost, reference_type, reference_id, movement_date, created_by, notes)
|
|
VALUES
|
|
('MOV-0001', 'RECEIPT', 1, NULL, 1, 1, 50.000, 0.000, 50.000, 18000000, 'RECEIPT', 1, '2026-04-28 09:00:00', 3, 'Receipt lot supplier A grade A'),
|
|
('MOV-0002', 'RECEIPT', 2, NULL, 1, 2, 20.000, 0.000, 20.000, 16000000, 'RECEIPT', 1, '2026-04-28 09:10:00', 3, 'Receipt lot supplier A grade B'),
|
|
('MOV-0003', 'RECEIPT', 3, NULL, 1, 3, 40.000, 0.000, 40.000, 17500000, 'RECEIPT', 2, '2026-04-28 10:00:00', 3, 'Receipt lot provisional supplier B'),
|
|
('MOV-0004', 'SORT_OUT', 3, NULL, 1, 3, 0.000, 40.000, 0.000, 17500000, 'SORTING', 1, '2026-04-28 11:00:00', 4, 'Lot sumber keluar untuk sortasi'),
|
|
('MOV-0005', 'SORT_IN', 4, 3, 1, 1, 18.000, 0.000, 18.000, 17500000, 'SORTING', 1, '2026-04-28 11:00:00', 4, 'Hasil sortasi grade A'),
|
|
('MOV-0006', 'SORT_IN', 5, 3, 1, 2, 12.000, 0.000, 12.000, 17500000, 'SORTING', 1, '2026-04-28 11:00:00', 4, 'Hasil sortasi grade B'),
|
|
('MOV-0007', 'SORT_IN', 6, 3, 1, 2, 7.000, 0.000, 7.000, 17500000, 'SORTING', 1, '2026-04-28 11:00:00', 4, 'Hasil sortasi jenis B grade A'),
|
|
('MOV-0008', 'SALE_OUT', 1, NULL, 1, 1, 0.000, 20.000, 30.000, 18000000, 'SALE', 1, '2026-04-28 13:00:00', 5, 'Alokasi penjualan dari lot supplier A'),
|
|
('MOV-0009', 'SALE_OUT', 4, NULL, 1, 1, 0.000, 10.000, 8.000, 19000000, 'SALE', 1, '2026-04-28 13:00:00', 5, 'Alokasi penjualan dari lot hasil sortasi supplier B');
|
|
|
|
INSERT INTO lot_labels (inventory_lot_id, label_type, label_value, printed_at, printed_by, print_count, status)
|
|
VALUES
|
|
(1, 'QR', 'LOT-260428-SUPA-001', '2026-04-28 09:01:00', 3, 1, 'ACTIVE'),
|
|
(4, 'QR', 'LOT-260428-SUPB-001-S1', '2026-04-28 11:01:00', 4, 1, 'ACTIVE'); |