Hi,
Boy negative 4,000,000, sort of reminds me of my checking account :).
First, is your General Ledger correct? The most obvious way to adjust this is to take a physical inventory and adjust the quantity. I'm concerned about the other things that may have been affected. If you look at SOP history, what does it show for quantities? Are the SOP tables messed up? If you do a 'return' transaction on the SOP document, does it back out the errant quantity. If it's an order, can you void the order? Have you backed up your system and run Reconcile on inventory or checklinks on Sales and Inventory? I'm trying to determine how many tables were impacted by this transaction. I have a query I got from somebody, I don't remember the creator, that returned information about how inventory reconciled to itself. It won't change anything, it's just a select statement. If you run this, what does it return? This same person also wrote a script to balance everything, but that one actually updates the tables. Here's the script:
DECLARE @ITEMFROM VARCHAR(33)
DECLARE @ITEMTO VARCHAR(33)
SET @ITEMFROM = ''
SET @ITEMTO = ''
IF @ITEMFROM = ''
SET @ITEMTO = REPLICATE('Z', 33)
SELECT ITEMNMBR
, Location
, CASE
WHEN (SUM(ONHAND) + SUM(OnHandNeg)) < 0
THEN (SUM(ONHAND) + SUM(OnHandNeg)) * - 1
ELSE (SUM(ONHAND) + SUM(OnHandNeg))
END AS OnHand
, CASE
WHEN (SUM(InUse) + SUM(InUseNeg)) < 0
THEN (SUM(InUse) + SUM(InUseNeg)) * - 1
ELSE (SUM(InUse) + SUM(InUseNeg))
END AS InUse
, CASE
WHEN (SUM(Damaged) + SUM(DamagedNeg)) < 0
THEN (SUM(Damaged) + SUM(DamagedNeg)) * - 1
ELSE (SUM(Damaged) + SUM(DamagedNeg))
END AS Damaged
FROM (
SELECT ITEMNMBR
, DOCNUMBR
, TRXQTY
, Location
, QtyType
, SiteType
, OnHand = CASE
WHEN SiteType = 1
AND QtyType IN (
0
, 1
)
THEN TRXQTY
ELSE 0
END
, OnHandNeg = CASE
WHEN (
QtyType = 1
AND SiteType = 2
)
OR (
QtyType = 0
AND SiteType = 2
)
THEN CASE
WHEN TRXQTY < 0
THEN TRXQTY
ELSE TRXQTY * - 1
END
ELSE 0
END
, InUse = CASE
WHEN QtyType = 3
AND SiteType = 1
THEN TRXQTY
ELSE 0
END
, InUseNeg = CASE
WHEN QtyType = 3
AND SiteType = 2
THEN TRXQTY * - 1
ELSE 0
END
, Damaged = CASE
WHEN QtyType = 5
AND SiteType = 2
THEN TRXQTY
ELSE 0
END
, DamagedNeg = CASE
WHEN QtyType = 5
AND SiteType = 1
THEN TRXQTY * - 1
ELSE 0
END
FROM (
SELECT IV30300.ITEMNMBR
, LNSEQNBR
, DOCNUMBR
, TRXQTY
, TRXLOCTN AS location
, TRFQTYTY AS qtytype
, SiteType = CASE
WHEN (IV30300.DOCTYPE = 3)
OR (
IV30300.DOCTYPE = 2
AND TRXQTY < 0
)
OR (TRFQTYTY = 3)
THEN 2
ELSE 1
END
FROM dbo.IV30300
INNER JOIN dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE IV30300.ITEMNMBR BETWEEN @ITEMFROM
AND @ITEMTO
AND UOMSCHDL = 'NO'
UNION
SELECT IV30300.ITEMNMBR
, LNSEQNBR
, DOCNUMBR
, TRXQTY
, TRNSTLOC
, TRTQTYTY
, SiteType = 1
FROM dbo.IV30300
INNER JOIN dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE IV30300.ITEMNMBR BETWEEN @ITEMFROM
AND @ITEMTO
AND UOMSCHDL = 'NO'
AND IV30300.DOCTYPE = 3
AND TRXLOCTN <> TRNSTLOC
) HISTORY
) SETQTY
GROUP BY ITEMNMBR
, LOCATION
