BAQ for Stock Status


(Prakash6738) #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


(Calvin Krusen) #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:


(Joe D Trent) #3

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

Joe


(Ken Adams) #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


(Calvin Krusen) #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.


(Prakash6738) #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…


(Mark Rowley) #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.


(Prakash6738) #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


(Bruce Ordway) #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…


(Prakash6738) #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.


(Randy Stulce) #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.


(Prakash6738) #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


(Randy Stulce) #13

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


(Prakash6738) #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


(Jason Hawthorne) #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.


(Prakash6738) #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)


(Prakash6738) #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


(Randy Stulce) #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


(Prakash6738) #19

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


(Bruce Ordway) #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.