Stock Status Report Qty doesn't match db tables

Epicor ERP 10
1

I’ve got a part in inventory that won’t show up on the Stock Status Report.

Well, it does show up. But its QOH is zero, while the Part Tracker, PartTran History and PartBin table shows a qty of 2.

Part Tracker
image

Part Tran History
image

PartBin query
image

When I run the SSR (limiting it to just the part in question), I get “No Records”. So I uncheck the “Exclude Parts with Zero Qty’s”, and it runs. But shows:

image

I’ve tried:

  • Every combo of SSR options, including setting the date way out, but it always comes up as zero.
  • Ran the “Refresh PartBin QOH from Part Tran”, it found no discrepancies.
  • Checked the settings of the bin - It’s active and not set as non-nettable.

Where should I look next?

2

Hmmm … more info.

There was a transfer order shipment on 01-21-2019, from plant MfgSys to plant GUTH, but I see no TO receipt.

This would explain why SSR shows zero. But I would have thought that the PartBin would have been zero until received.

3

What version are you on? It looks like there’s a bug with this report where it’s case sensitive when SQL isn’t, so it doesn’t necessarily see all the stock on hand.

4

That doesn’t seem to be it. The PartBin.BinNumis in the same case as the BinNum in the PartTran table.

I’ve also checked the cases of the plant and warehouse codes. All seem to match.

5

Try the Refresh PartBin QOH from PartTran or Refresh Part Quantities and Allocations? They’re both in the System Management / Rebuild Processes / Mfg/Distribution

6

I had already tried the “Refresh PartBin QOH from PartTran”

Running the “Refresh Part Quantities and Allocations” now.

7

Running the “Refresh Part Quantities and Allocations” (in Report only mode) for just the part in question, shows:

  • QOH - No changes (0 -> 0)
  • Non-Nettable - No changes (0 -> 0)
  • Demand - Change of (1 -> 0)
  • TO Demand - Change of (1 -> 0)
  • All other TO Qty’s (Reserve, Alloc, Picking, and Picked) - No change of (0 -> 0)

I was about to run it in Report and Update mode, but get the following.

image

So I’ll wait until after hours…

8

@ckrusen - we have seen a similar issue with Stock Status report due to upper/lower case as mentioned. Tables are all fine the issue was a hard code in the report to only look for whse code or bin numbers with same case. I see from your screen shots your whse code - Main. That was our problem.

As mentioned this is fixed going forward in .300 and I believe also reto back to .200 something.

As a side note - we see the same issue on Slow Moving Stock and we have an active case open to prove it and get that report corrected as well.

9

I hope this is the issue, but still don’t see it.

The site ID is GUTH
The site desc is GUTHRIE
The warehouse ID is Guth
The warehouse desc is Main, GUTHRIE
The BinNum is I-0001

All related fields of PartTran, PartBin, PartPlant have the same case

10

I have seen a similar issue due to DATES… the stock status report is date sensitive, showing the “Current” on hand balance as of the date you entered. SO you can run the report as of 1/31/2019, or 12/31/2018 and get two different results…
Where THIS problem comes into play is when someone accidentally enters a transaction with a future date that has not yet come into view. To find out if this is the case here… create a BAQ that searches for PartTran transactions with a future date.

11

I’m very aware about transaction dates and the SSR (I learned the hard way)

12

Turned out the problem was with trailing spaces in the PartNum field of some table records.

Support gave me two SQL programs to run:

  • FX_Upd_Field_Case.sql

    • DATA FIX FOR CHANGE REQUEST 39940MPS - - Fix Field Case Sensitivity issues

  • FX_Upd_TrailingSpaces.sql

    • DATA FIX FOR CHANGE REQUEST 40883MPS - - Trim trailing spaces
1 Like
13

Hi, did that work for you?

14

Yes. After running FX_Upd_Field_Case.sql and FX_Upd_TrailingSpaces.sql the SSR matched the expected results.

15

Do we know if there is still an issue with the Stock Status report or was that specific just to that version and was corrected in more recent versions? I’m trying to understand some discrepancies between Stock Status and a BAQ we created to show all values in PartBin table.

16

Did you get an answer on this, Dan? I’m seeing the same issue with a query I’ve build. A handful of parts that show quantities in Part Tracker/Time Phase but aren’t on the Stock Status report.

17

Check some of the option on the Stock Status report. One of the things that ended up biting us was we had stock on hand of some parts that are considered non-stock. By default, I don’t think it shows those. There are a few different options that might be contributing like that. I didn’t have any trailing spaces issues.

1 Like