USE [AL_QALAM_LIVE]
GO

/****** Object:  View [dbo].[stockposition]    Script Date: 8/2/2020 9:57:37 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[stockposition]
as
select ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,(isNull((select sum(qty) from stocksummary
 where ItemID=parent.ItemID and InvoiceType=2 and    godownid=parent.godownid and departid=parent.departid),0 )-
   -isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=3 and    godownid=parent.godownid and departid=parent.departid),0)-
   isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and (InvoiceType=5 or Invoicetype=8) and godownid=parent.godownid and departid=parent.departid),0)-
   isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=7  and  godownid=parent.godownid and departid=parent.departid),0)
   -isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=6  and godownid=parent.godownid and departid=parent.departid),0)) as Stock
   ,  company,departid,department,
isNull((select sum(qty) from InventoryOpeningBalance where ID=parent.ItemID  and godownid=parent.godownid),0) as Opening ,
godownid ,0 as DepartRet,0 as StoreRet,0 as SalesRet
   from StockSummary as parent 
   group by ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,godownid,company,departid,department

GO

