162 lines
7.1 KiB
JavaScript
162 lines
7.1 KiB
JavaScript
// routes/batchNoTransaction.js
|
|
// Mirrors SAP B1's own standard "Batch Number Transactions Report" (Inventory
|
|
// → Inventory Reports → Batch Number Transactions Report): a master grid of
|
|
// batches (from OIBT, SAP's per-batch/per-warehouse stock table) and, for a
|
|
// selected batch, a detail grid of every stock-affecting document that ever
|
|
// touched it (from IBT1, SAP's batch-transaction-log table).
|
|
'use strict';
|
|
const express = require('express');
|
|
const router = express.Router();
|
|
const { verifyToken } = require('../middleware/auth');
|
|
const { getPool } = require('../services/sqlPool');
|
|
|
|
const cq = (req) => req.query?.company || req.body?.company || null;
|
|
const sqlEsc = (v) => String(v).replace(/'/g, "''");
|
|
|
|
// GET / — the "Batches" master grid: one row per Item/Batch/Warehouse,
|
|
// exactly the fields SAP's own report shows (Item No, Item Description,
|
|
// Batch, Whse, Quantity, Expiration Date, Manufacturing Date, Project,
|
|
// Status, COA_Status, Sale Type — all native OIBT columns), plus Committed/
|
|
// OnOrder/Available as extra value-add columns (same formula as Inventory
|
|
// Status Report) since the data is already on hand.
|
|
router.get('/', verifyToken, async (req, res) => {
|
|
try {
|
|
const co = cq(req);
|
|
const pool = await getPool(co);
|
|
|
|
const itemFrom = (req.query.itemFrom || '').trim();
|
|
const itemTo = (req.query.itemTo || '').trim();
|
|
const batchNo = (req.query.batchNo || '').trim();
|
|
const itemGroup = (req.query.itemGroup || '').trim();
|
|
const includeZeroQty = req.query.includeZeroQty === '1';
|
|
const warehouses = (req.query.warehouses || '').split(',').map((w) => w.trim()).filter(Boolean);
|
|
|
|
const where = [];
|
|
if (itemFrom) where.push(`T0."ItemCode" >= '${sqlEsc(itemFrom)}'`);
|
|
if (itemTo) where.push(`T0."ItemCode" <= '${sqlEsc(itemTo)}'`);
|
|
if (batchNo) where.push(`T0."BatchNum" LIKE '%${sqlEsc(batchNo)}%'`);
|
|
if (itemGroup) where.push(`T2."ItmsGrpCod" = ${parseInt(itemGroup)}`);
|
|
if (warehouses.length) where.push(`T0."WhsCode" IN (${warehouses.map((w) => `'${sqlEsc(w)}'`).join(',')})`);
|
|
if (!includeZeroQty) where.push(`T0."Quantity" <> 0`);
|
|
|
|
const query = `
|
|
SELECT T0."ItemCode", T0."ItemName", T0."BatchNum", T0."WhsCode",
|
|
T0."Quantity", T0."IsCommited", T0."OnOrder",
|
|
T0."PrdDate", T0."ExpDate",
|
|
T0."U_Project", T0."U_Status", T0."U_COA_Status", T0."U_SaleType",
|
|
T2."InvntryUom" AS "Uom"
|
|
FROM [dbo].[OIBT] T0
|
|
LEFT JOIN [dbo].[OITM] T2 ON T2."ItemCode" = T0."ItemCode"
|
|
${where.length ? `WHERE ${where.join(' AND ')}` : ''}
|
|
ORDER BY T0."ItemCode", T0."BatchNum", T0."WhsCode"`;
|
|
|
|
const result = await pool.request().query(query);
|
|
const data = result.recordset.map((r) => {
|
|
const qty = Number(r.Quantity) || 0;
|
|
const committed = Number(r.IsCommited) || 0;
|
|
const onOrder = Number(r.OnOrder) || 0;
|
|
return {
|
|
itemCode: r.ItemCode,
|
|
itemName: r.ItemName || '',
|
|
batchNo: r.BatchNum,
|
|
warehouse: r.WhsCode,
|
|
inStock: qty,
|
|
committed,
|
|
ordered: onOrder,
|
|
available: qty - committed + onOrder,
|
|
mfgDate: r.PrdDate,
|
|
expDate: r.ExpDate,
|
|
project: r.U_Project || '',
|
|
status: r.U_Status || '',
|
|
coaStatus: r.U_COA_Status || '',
|
|
saleType: r.U_SaleType || '',
|
|
uom: r.Uom || '',
|
|
};
|
|
});
|
|
res.json({ success: true, data });
|
|
} catch (err) {
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
// BaseType (IBT1) → the SAP document table/label that actually recorded the
|
|
// stock movement. Confirmed by cross-checking real BaseEntry values against
|
|
// each candidate table in this database (e.g. BaseType 18 entries' BaseEntry
|
|
// values are found in OPCH, with matching CardName/DocDate — not any other
|
|
// table), not guessed from memory of SAP's BoObjectTypes enum alone.
|
|
const DOC_TYPES = {
|
|
13: { label: 'A/R Invoice', table: 'OINV' },
|
|
14: { label: 'A/R Credit Memo', table: 'ORIN' },
|
|
15: { label: 'Delivery', table: 'ODLN' },
|
|
16: { label: 'Return', table: 'ORDN' },
|
|
17: { label: 'Sales Order', table: 'ORDR' },
|
|
18: { label: 'A/P Invoice', table: 'OPCH' },
|
|
19: { label: 'A/P Credit Memo', table: 'ORPC' },
|
|
20: { label: 'Goods Receipt PO', table: 'OPDN' },
|
|
21: { label: 'Goods Return', table: 'ORPD' },
|
|
22: { label: 'Purchase Order', table: 'OPOR' },
|
|
59: { label: 'Goods Receipt', table: 'OIGN' },
|
|
60: { label: 'Goods Issue', table: 'OIGE' },
|
|
67: { label: 'Inventory Transfer', table: 'OWTR' },
|
|
310000001: { label: 'Production Order', table: 'OWOR' },
|
|
};
|
|
|
|
// GET /transactions?itemCode=X&batchNo=Y — the "Transactions for Batch" grid.
|
|
// Reads IBT1, SAP's own batch-transaction log, one row per stock-affecting
|
|
// document line that ever touched this exact Item+Batch.
|
|
//
|
|
// Sign/direction rule (reverse-engineered against this database's real IBT1
|
|
// data, not assumed from documentation): Direction 0 and 1 ALWAYS store
|
|
// Quantity as a positive magnitude — 0 means the document increased batch
|
|
// stock (In), 1 means it decreased it (Out), so the sign has to be applied
|
|
// here. Direction 2 (used by AR/Sales-side docs — Invoice/Credit
|
|
// Memo/Return/Sales Order) stores Quantity ALREADY signed correctly, so it's
|
|
// used as-is. Verified against BaseType 60 (Goods Issue: always Direction 1,
|
|
// Quantity always ≥0) and BaseType 15 (Delivery: Direction 0/1/2 all appear,
|
|
// but only Direction 2 rows have negative Quantity stored).
|
|
router.get('/transactions', verifyToken, async (req, res) => {
|
|
try {
|
|
const co = cq(req);
|
|
const pool = await getPool(co);
|
|
const itemCode = (req.query.itemCode || '').trim();
|
|
const batchNo = (req.query.batchNo || '').trim();
|
|
if (!itemCode || !batchNo) return res.json({ success: true, data: [] });
|
|
|
|
const query = `
|
|
SELECT T0."ItemCode", T0."BatchNum", T0."WhsCode", T1."WhsName",
|
|
T0."LineNum", T0."BaseType", T0."BaseNum", T0."BaseLinNum",
|
|
T0."DocDate", T0."Quantity", T0."Direction", T0."CardName"
|
|
FROM [dbo].[IBT1] T0
|
|
LEFT JOIN [dbo].[OWHS] T1 ON T1."WhsCode" = T0."WhsCode"
|
|
WHERE T0."ItemCode" = '${sqlEsc(itemCode)}' AND T0."BatchNum" = '${sqlEsc(batchNo)}'
|
|
ORDER BY T0."DocDate", T0."LineNum"`;
|
|
|
|
const result = await pool.request().query(query);
|
|
const data = result.recordset.map((r, i) => {
|
|
const rawQty = Number(r.Quantity) || 0;
|
|
const dir = Number(r.Direction);
|
|
const qty = dir === 1 ? -Math.abs(rawQty) : dir === 0 ? Math.abs(rawQty) : rawQty;
|
|
const docType = DOC_TYPES[Number(r.BaseType)] || { label: `Doc Type ${r.BaseType}`, table: '' };
|
|
return {
|
|
no: i + 1,
|
|
docTypeLabel: docType.label,
|
|
docNum: r.BaseNum,
|
|
docRow: r.BaseLinNum,
|
|
date: r.DocDate,
|
|
warehouse: r.WhsCode,
|
|
warehouseName: r.WhsName || '',
|
|
bpName: r.CardName || '',
|
|
qty,
|
|
direction: qty >= 0 ? 'In' : 'Out',
|
|
itemCode: r.ItemCode,
|
|
batchNo: r.BatchNum,
|
|
};
|
|
});
|
|
res.json({ success: true, data });
|
|
} catch (err) {
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|