Obsolete Inventory in SedonaOffice

04/29/2024 10:41 am EDT

The following script can be run to find any parts that have had no issues and no receipts since the date you specified in the highlighted area. The date format is year-month-day.

Declare @Warehouse VarChar(30),
             @ReceiptOlderThan DateTime,
             @IssueOlderThan DateTime,
             @IncludeInactive NVarChar(1),
             @SkipZeroParts NVarChar(1)   

Set @Warehouse = 'All' 
Set @IssueOlderThan = '2021-01-1' 
Set @ReceiptOlderThan = '2021-01-1' 
Set @IncludeInactive = 'N' 
Set @SkipZeroParts = 'N'   

select P.Part_Code, P.Description, B.Branch_Code, W.Warehouse_Code, 
Inv.On_Hand_New, inv.Value_On_Hand,  
ISNULL(Convert(varchar, LastIssue,101),'Never') as LastIssued,  
ISNULL(Convert(varchar, LastReceipt,101),'Never') as LastReceived, 
P.Inactive 
from IN_Inventory Inv 
Inner join IN_Part P on P.Part_Id = Inv.Part_Id 
Inner join IN_Warehouse W on W.Warehouse_Id = Inv.Warehouse_Id 
Inner join AR_Branch B on B.Branch_Id = W.Branch_Id 
Left Outer Join 
(Select Part_Id, Warehouse_Id, MAX(Date) as LastIssue 
from IN_Journal  
Where Inventory_Activity_Id in (6,7,8,10) and Multiplier = -1 and Warehouse_Id > 1 
Group by Part_Id, Warehouse_Id) Issues on Issues.Part_Id = Inv.Part_Id and Issues.Warehouse_Id = inv.Warehouse_Id 
Left Outer Join  
(Select P.Part_Id, R.Warehouse_Id, MAX(R.received_date) as LastReceipt 
from IN_Receipt_Parts P 
Inner join IN_Receipt R on R.Receipt_Id = P.Receipt_Id 
Where r.Is_Return = 'N' and Warehouse_Id > 1 
group by P.Part_Id, R.Warehouse_Id) Receipts on Receipts.Part_Id = Inv.Part_Id and Receipts.Warehouse_Id = Inv.Warehouse_Id  
Where inv.Part_Id > 1 and inv.Warehouse_Id > 1 and (Warehouse_Code = @Warehouse or @Warehouse = 'All') and 
ISNULL (LastReceipt, @ReceiptOlderThan) <= @ReceiptOlderThan and  
ISNULL (LastIssue, @IssueOlderThan) <= @IssueOlderThan and (Inv.Value_On_Hand <> 0 or Inv.On_Hand_New <> 0 or @SkipZeroParts = 'N') and (P.Inactive = 'N' or @IncludeInactive = 'Y')

Was this article helpful?