BAQ for Stock Status

Prakash_Bankar · February 24, 2017, 6:43am

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

ckrusen · February 24, 2017, 4:39pm

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.

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.
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.

jdtrent · February 24, 2017, 6:12pm

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

Joe

Kenadian · February 24, 2017, 6:18pm

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

ckrusen · February 24, 2017, 6:27pm

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.

Prakash_Bankar · February 25, 2017, 6:38am

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…

Rowley150 · February 25, 2017, 9:40pm

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.

Prakash_Bankar · February 27, 2017, 3:37am

@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

bordway · February 28, 2017, 5:14pm

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…

Prakash_Bankar · March 1, 2017, 11:16am

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.

Randy · March 1, 2017, 1:48pm

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.

Prakash_Bankar · March 2, 2017, 10:05am

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

Randy · March 2, 2017, 1:03pm

No worries, I’m sure we’ve all had our share of BAQ quirks to hunt down and
correct.

Prakash_Bankar · March 3, 2017, 5:50am

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

jhawt · March 3, 2017, 8:46pm

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.

Prakash_Bankar · March 4, 2017, 10:52am

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)

Prakash_Bankar · March 6, 2017, 11:14am

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

Randy · March 6, 2017, 5:21pm

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

Prakash_Bankar · March 7, 2017, 10:47am

No other way to show group by partnum and Warehouse ? i am stucked on group by…

bordway · March 7, 2017, 5:44pm

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.