I have a need for a BAQ to emulate BackFlush behavior. I will be using this to generate issuing for an integration that I am working on. This will be used for warehouse and bin selection on the from part of the issue. Now if I need to, I can loop through the rows and do this all in C# when doing the issueing, (or I can make a post processing BPM on GetList that does the loop and sets the values that way ) , however, I would prefer that the BAQ returns the values that need to be issued right in the grid using SQL. And I think it’s an interesting problem to solve anyways.
What I want is all of the bins with that part number to be in the list, ordered by the smallest amount to the largest amount. I have that part. I am passing in a parameter that will be the total amount that needs to get issued. (that shows up in the picture as 200, it’s not 200 each, it’s 200 total). What I would like to have in the grid is for the first row to have the lesser of the two values (issued or in the bin) to show up in a column. This will be the amount that gets issued from that bin. In the second row, I need the lesser of the remainder of what’s left to be issued or what’s in the bin. This will continue until either I run out of material in bins, or the full amount is issued.
This is something that relatively easy in excel because I can have a different formula in the first cell to kick things off. I’ve tried playing around with Lead() and Lag() windowing functions (as shown in the crossed out cells) but I can’t quite think of how I would get this to work in SQL.
This is the calculated field based on what you suggested.
sum(@QtyToIssue - Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)
I also want to play around with the lag function, but the problem that I keep running into is that I can’t reference the same column in the lag, It has to be a different one. So if I do some math and then need that value to calculate the next row, I can’t reference it.
lag(Bins.PartBin_OnhandQty,1,0) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc
sum(Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)
then I subtract that number from by total input
sum(Bins.PartBin_OnhandQty) over (partition by Bins.PartBin_PartNum order by Bins.PartBin_OnhandQty asc)
Then do a case statement to figure out how much should be issued from each bin.
case when RunningSumBack > 0 then Bins.PartBin_OnhandQty
when RunningSumBack + Bins.PartBin_OnhandQty > 0 then RunningSumBack + Bins.PartBin_OnhandQty
else 0
end
) , however, I would prefer that the BAQ returns the values that need to be issued right in the grid using SQL. And I think it’s an interesting problem to solve anyways.
