BAQ for Stock Status

Epicor ERP 9
1

Hi experts,

I am trying to create a BAQ for the stock status like system report Stock Status. How i can apply the logic ? I am thinking of subtracting all issued qty from total reciepts by transactions type from partran table. Will it make sense? anybody done this before ?

Thanks

2

I tried doing one, and it turns out to be pretty difficult. Iā€™m no BAQ guru so I gave up on it.

But from what Iā€™ve tried, it seems you are going to need to sub-queries and have some unique relationships.

  1. Need a query the totals the inventory Tran qtys for TranDate >= target date. This will be all the trans you need to ā€œback-outā€ from the current QOH.

  2. The results from #1 need to be applied against ALL parts. Not just ones currently with a a non-zero QOH.

Because the built-in SSR only compensates for qty changes, not cost changes,
I was going to back calculate the cost at the target date, by finding the last PartTran on or before the target date, and use that cost as the cost on the particular target date.
But just making the queries that get the QOH correct for the target date was too much for me.

Hopeful an angel will answer our payers. :pray:

3

Iā€™ve heard of people backing up PartCost daily to have a date-sensitive
cost.

Joe

1 Like
4

Several have tried this in the past and nobody that I know of has ever been truly successful.

Part of the issue is that there are all sorts of ā€œextraā€ transactions on the Part Tran table. Also, what you would think should be a negative number (Stk-Cus) is a positive number on the Part Tran and it is the program that subtracts this from the running total.

Ken

5

Thanks Ken.

Iā€™ll occasional go back to this, just for the challenge. Even if I donā€™t need to do it, I find doing these types of things are the best learning tools.

And I was aware of the TranType affecting the sign of the TranQty. But Iā€™m sure there are even more demons around every corner.

1 Like
6

guys, i tried for BAQ, half doneā€¦but as @ckrusen saidā€¦there are even more demons around every cornerā€¦there are so many difficulties and confusions with summerizing total inward and total issues. Need to rectify all transaction typesā€¦

7

Forgive me if I have the wrong end of the stick but:

Partbin - you can get partbin, onhandqty, warehouse code,

Part - you can get part description,

Partcost - you can get the STD costs.

Join all 3 together and youā€™ll get a mirror of the stock status.

I built the above in Excel through SQL.

If you want it more detailed by transactionā€¦Yes Iā€™d suggest you use parttran.

8

@Rowley150 :
Good idea, but partbin shows the current stock of till date. If i want to retrieve stock from parttran, how we will get the stock on CutOff date like Stock Status ? Now here, tranDate comes into picture. Now assume that on 1st of feb, the part XYZ had null stock, and today it is 200 Nos onHandQty. Its not possible to retrieve the stock of Part XYZ for 1st Feb by partbin,part,partcost, becoz the main link of tranDate is not there. So the only option is PartTran, so we should have to make BAQ for PartTran, am i correct ?

Thanks

9

Yes - PartTrans needed to duplicate the ā€œAs Ofā€ feature for the Stock Status report.
I think a BAQ filtered by PartTran.InventoryTran = True should get you pretty close to start with.
Beyond that Iā€™m pretty sure I ran into a few TranTypes/exceptions the last time I looked at this in E9.
But canā€™t remember specifics right nowā€¦

10

Hey guys,
Yuhhhhhhhhhhhā€¦finally a BAQ is ready for Stock Status . I have applied a tracker view for cutoff date also.
Thank you very much guys for your suggestions.

Thanks.

11

Prakash,

Can you share what you did then? We have a BAQ like Markā€™s using Part -
PartBin - PartCost tables. But would be interested to compare it to yours.

12

Hey randy,
I will be happy to share BAQ with all of you, i just finalizing the ā€˜Cut Off Dateā€™ functionality. It is not working as as expected, still working on it. I will post the BAQ as soon as i finshed.

Thanks

13

No worries, Iā€™m sure weā€™ve all had our share of BAQ quirks to hunt down and
correct. :slight_smile:

14

Hello guys,
Still fighting with ā€˜Cut off Dateā€™ . Criteria / parameter in BAQ, Date Range filter in Tracker view not working. Tried all available tricksā€¦but still BAQ is incomplete without ā€˜Cut off dateā€™ā€¦

I think , i have used 4 instatnces in BAQ ,screenshot is attached. Is it the problematic ? If i had not used,then i cant apply crieteria for 3 instances at a time.
Anybody has any suggestions ? please shareā€¦

Thanks

15

Have you tried adding the CutOff data as a criteria on all 4 PartTran tables/aliases and having the those 4 criteria reference a single mandatory BAQ parameter.

When the BAQ is called from the dashboard it will automatically call popup the cutoff date which will also make sure they query isnā€™t run wide open.

16

Hi guys,
Sharing the Stock status BAQ and Dashboard with all of u, please make modifications as per your requirements as i displayed only Part number, Description, OnHandQty, UOM. In BAQ there are 6 more columns reffered for calcualting final OnHandQty as multiple IF-ELSE statements are not allowed in calculated filed. You can hide rest of the columns in trackerview.

Have a nice weekend.

Thanks guysā€¦

Stock Status by cutofDate Tracker.dbd (156.4 KB)

2 Likes
17

Hi Guys,
I am trying to make this dashboard more specific like warehouse wise, lot wise. But version 9.05.702A doesnā€™t support group by clause. I am facing some difficulties while displaying qtys warehouse wise. Now i can display total on hand qty on those warehouses, whichever was on stock on cut of date. But it displaying in totality. I want to bifurcate it warehouse wiseā€¦ Any help,suggession will be appreciated.

The test part has 48 qtys in MAIN warehouse and 2 Qtys in WIP warehouse, and results coming in below format,it means sum is not calculated warehouse wise. How to bifurcate it ?

Thanks

18

E9 series being Progress based is more limited than E10 on the grouping. For reports, we ended up doing the final groupings in Crystal.

Dashboards weā€™d use the Group By on the list/grid views or copy/paste it into excel

1 Like
19

No other way to show group by partnum and Warehouse ? i am stucked on group byā€¦

20

Thanks for sharing your dashboard
Just an FYI
I noticed some On Hand Qtys were off when I tested your example here.
One part has quantities in two different plants and a transfer qty.
I want to take look thru the design details butā€¦ not sure when Iā€™ll get the time.
Will let you know when/if I see anythingā€¦ if itā€™s related to your warehouse issue.

How to bifurcate it
Maybe view(s) to aggregate some values beforehand, and then an external BAQ?
Or maybe executive query(s)?
Sometimes it can be tough to get everything you want with just E9 BAQ phrase builder.