Has anyone come across an issue where data returned in column in a BAQ is empty when there is corresponding data in SQL for that Table/Column?
In my case I am trying to return the Columns WareHouse2 and BIn2 from PartTran.
As an example I have narrowed the criteria down to one inventory transaction for testing.
When I return the data in the BAQ WareHouse2 and BIn2 are empty, but when I run a SQL query in SSMS there is data present.
I can recreate this both in my Production and Test Environments 10.2.300.40
We did recent point release update to .40
I have regenerated the data model and cycle the application pool already.
The point release has been in production for a few weeks and we did a good amount of testing (for us) before we applied the update. Outside of this issue, nothing else has been brought to our attention.
I have contacted support and their suggestion was to run:
System Management > Rebuild Processes> Mfg/Distribution > Refresh Partbin QOH from Part tran
System Management > Rebuild Processes> Mfg/Distribution > Refresh Part Quantities and Allocations
I did these both for the part that is part of the Inventory Trans that I am testing, but the data is still not present in the BAQ.
My guess is that something is wrong in some ice table, but that is just a guess.
One more thing … Not sure when excatly when it happened, but some tables changed some fields from type Date, to DateTime.
I had a BAQ that used a date field as a criteria, (looking for a specific date), Worked fine when it was a Date, but not so much as a DateTime.
For example, say a BAQ has GLJrnDtl.PostedDate = PartTran.TranDate.
That worked in 10.1.400. But GLJrnDtl.PostedDate was changed to a DateTime some before 10.2.300.
Now that PostedDate has a time component, it would only find matches if the PostedDate happened to have a time of 12:00:00 AM. And GLJrnDtl records created back in 10.1.400 are assigned the time of 12:00:00. So those old records would still show up in the BAQ results.
There are no other tables in my test BAQ, just PartTran. When the user brought our attention to the Dashboard that was not showing the informaton, We traced back the BAQ, copied it and simplified it to the root table.
Also… I will take not of the Date/DateTime issue, but think the issue may be outside of that here.
In a BAQ, the BinNum2 column is always blank. In SSMS, I see values.
Perhaps this is a “favor” the Epicor is doing for you.
The Data dict says:
This value only exists for the warehouse transaction. TranSrc = “4”. For the Issue side of the transaction it identifies the Bin location that the quantity is transferred in to. On the Receipt side of the transaction it identifies the Bin location that the quantity was transferred from.
I have a strange thing happen… A BAQ of just the PartTran Table with a table criteria of: BinNum2 <> '' returns many rows (6977 to be exact), but they are all blank in the BAQ Designer.
in SSMS, a similar query returns 6977 records, but BinNum2 is always populated.
Thanks you both the information here. Maybe this was a change with our point release b/c our users indicate that they could see this data previously… some even had exported excel from the dashboard.
We do not have AMM and this explanation above appears to be right on from my point of view.
By the way, I hate that answer. That is such a scary-bad answer. It creates or removes inventory value with no GL activity behind it. And it’s just wrong anyway. If you have parts that flip back and forth from quantity-bearing to not and back again (PUR-STK, then PUR-UKN, then PUR-STK), then the part-tran records do not and should not add up to your current OH qty (except occasionally by dumb luck).
Not sure I agree. I had to fight for several years to get Accounting to accept that the value in the Inventory GL was more accurate than the what shows on a stock status report.
When PartBin’s onhand Qty gets out of wack, does the GL get out of wack too? Meaning that the GL acct agrees with the value derived from Cost and incorrect QOH.
Or do they diverge with the GL Acct maintaining the correct value, while the apparent value (cost and incorrect QOH) is wrong?
If it’s the latter, then the Refresh PartBin shouldn’t need to any accompanying GL trans when it updates the PartBin records.
I have only seen that be off once (it was a non-stock flag on PartPlant different than the one on Part, and we backflushed it). It was a week before our annual physical inventory and only $200 and it wasn’t worth the fight.
For the rest of what you said… I should probably PM you.
But I think your point is that it exists to correct real errors, and maybe so, but it is dead wrong to use it in every situation like Support asks you to do.
On the point about STK-MTL transactions, there won’t ever be a Whse2 or BinNum2 because it’s a transaction involving only 1 bin - no from and to, only a from,
