This will give you inventory counts as of whatever parameter date you pass
it (among st other parameters) I hope it helps you get started it also
compares it to current QtyOn hand but that's unrelated
CREATE PROCEDURE [dbo].[InventoryStockReport]
-- Add the parameters for the stored procedure here
@PartNum AS VARCHAR(MAX),
@Plant AS VARCHAR(MAX),
@BinNum AS VARCHAR(MAX),
@WhseCode AS VARCHAR(MAX),
@TranDate As VARCHAR(MAX),
@Company AS VARCHAR(MAX),
@DifffOnly As VARCHAR(MAX)
AS
BEGIN
WITH InvQty (Company, Plant, PartNum, WareHouseCode, BinNum, LotNum, Qty)
AS
(
SELECT Company, Plant, PartNum, WareHouseCode,BinNum, LotNum,
SUM(CASE WHEN TranType
IN('KIT-CUS','STK-CUS','STK-INS','STK-KIT','STK-MTL','STK-PLT','STK-UKN')
THEN TranQty * -1 ELSE TranQty END)
FROM Mfgsys803..PartTran WITH(NOLOCK)
WHERE TranType NOT IN
('ADJ-CST','ADJ-PUR','DMR-MTL','DMR-REJ','INS-DMR','INS-MTL','INS-REJ','MFG-VAR','PUR-INS','PUR-UKN','RMA-INS')
AND (TranDate <=@TranDate AND WareHouseCode LIKE IsNull(@WhseCode,'%') AND
BinNum LIKE IsNull(@BinNum,'%')
AND PartNum LIKE IsNull(@PartNum,'%') AND Plant LIKE IsNull(@Plant,'%')
AND Company LIKE IsNull(@Company,'%'))
GROUP BY Company, Plant, PartNum, WareHouseCode, BinNum, LotNum
)
SELECT InvQty.*, Qty * (IsNull(PartCost.StdLaborCost,0) +
IsNull(PartCost.StdBurdenCost,0) + IsNull(PartCost.StdMaterialCost,0) +
IsNull(PartCost.StdSubContCost,0) + IsNull(PartCost.StdMtlBurCost,0)) As
Value ,P.PartDescription, WH.Description AS WareHouseDescription,
IsNull(PB.OnHandQty,0) As OnHandQty, (IsNull(PB.OnHandQty,0) - Qty) As
Difference
FROM InvQty
LEFT OUTER JOIN Mfgsys803..PartCost WITH(NOLOCK) ON InvQty.Company =
PartCost.Company AND InvQty.PartNum = PartCost.PartNum AND PartCost.CostID=1
INNER JOIN Mfgsys803..Part P WITH(NOLOCK) ON InvQty.Company = P.Company
AND InvQty.PartNum = P.PartNum
LEFT OUTER JOIN Mfgsys803..PartBin PB on InvQty.Company = PB.Company AND
InvQty.PartNum = PB.PartNum AND InvQty.WareHouseCode= PB.WareHouseCode AND
InvQty.BinNum = PB.BinNum AND InvQty.LotNum = PB.LotNum
LEFT OUTER JOIN Mfgsys803..Warehse WH WITH(NOLOCK) ON InvQty.Company =
WH.Company AND InvQty.WareHouseCode=WH.WareHouseCode AND
InvQty.Plant=WH.Plant
WHERE InvQty.Qty <> 0 AND ((IsNull(PB.OnHandQty,0) - Qty) <> 0 OR
'YES'<>@DifffOnly) AND P.NonStock<>1
Order By InvQty.Company, InvQty.PartNum, InvQty.Plant,
InvQty.WareHouseCode, InvQty.BinNum, InvQty.LotNum
END
*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E:
jose@...
http://www.josecgomez.com
<
http://www.linkedin.com/in/josecgomez> <
http://www.facebook.com/josegomez>
<
http://www.google.com/profiles/jose.gomez> <
http://www.twitter.com/joc85>
<
http://www.josecgomez.com/professional-resume/>
<
http://www.josecgomez.com/feed/>
<
http://www.usdoingstuff.com>
*Quis custodiet ipsos custodes?*
On Fri, Aug 30, 2013 at 9:22 AM, Vic Drecchio
<
vic.drecchio@...>wrote:
> **
>
>
> You're spot on; that's the only way. And it's not fun.
>
> I worked with someone in the past and this was a real important metric for
> them to have. I ended up utilizing one of the UD tables and since they were
> on SQL I created a simple script that ran nightly that populated UD01 with
> today's date, part number, qty on hand and total cost. So at any point in
> time, we could look back to March 23rd and see what we had in stock and
> what
> the value was.
>
> So if you're a lucky SQL customer, perhaps that's an idea for moving
> forward.
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of
> bildo564
> Sent: Friday, August 30, 2013 9:16 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] On-hand quantity - 905.701
>
>
> Is there a way to create a report to find out an on-hand quantity from a
> date in the past? The only way I could think of was to look at the part we
> are questioning and dissect the Part Transaction History Tracker.
>
> Thanks,
>
> Billy
>
> [Non-text portions of this message have been removed]
>
>
>
[Non-text portions of this message have been removed]