USE [AL_QALAM_LIVE]
GO

/****** Object:  View [dbo].[StockSummary]    Script Date: 8/2/2020 9:59:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


   ALTER view  [dbo].[StockSummary]
   as
   select i.ID as ItemID,ids.TransactionID as DetailID,i.Name as Item,i.Attr1,i.Attr2,i.Under as CategoryID,i.StandardPrice1,i.StandardPrice2 as SalePrice,i.StandardPrice3 as CostPrice,
   i.AdditionalInfo1 as partnumber,i.AdditionalInfo2 as specs,i.Barcode as Barcode,im.shipperid as DepartID,dept.name as Department
   ,ic.name as Category ,im.GodownID as Godownid,g.AdditionalInfo1 as Company,ids.QTY as qty,ids.Attr1 as note,
   im.InvoiceType,im.InvoiceID,i.ProductBaseType as ProductBaseType,im.InvoiceDate as InvoiceDate from 
   invoicedetail as ids
   inner join InvoiceMaster as im on ids.InvoiceID=im.InvoiceID
   inner join inventory as i on i.ID=ids.ItemID
   inner join Godowns as g on g.Godownid=im.godownid
   left outer join Godowns as dept on dept.Godownid=im.ShipperID
   inner join Inventory as ic on i.under=ic.id
   where i.ProductBaseType='ITEM' and InvoiceType in(2,3,4,5,6,7,8,9)
GO

