USE [AL_QALAM_LIVE]
GO

/****** Object:  View [dbo].[view_list_pendingSupplies]    Script Date: 8/1/2020 11:39:34 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER view [dbo].[view_list_pendingSupplies]
as 
select ROW_NUMBER() over (order by TransactionID) as row_num, InvoiceID,Qty,ReceivedQty,ItemID,inv.Name as Product,inv.barcode as Barcode,cat.Name as Category,cat.ID as CategoryID,
(select KeyField from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID) as KeyField,
(select InvoiceType from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID) as InvoiceType,
(select InvoiceDate from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID) as InvoiceDate,
(select ReferenceInvoiceID from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID) as ReferenceInvoiceID,
(select COAName from accounts where COAID =(select SupplierID from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID)) as SupplierName,
(select COAID from accounts where COAID =(select SupplierID from invoicemaster where InvoiceMaster.InvoiceID=ids.InvoiceID)) as SupplierID,
(select Name from Godowns as g where g.GodownID=(select GodownID   from InvoiceMaster where InvoiceMaster.InvoiceID=ids.InvoiceID )) as Company,
(select GodownID from Godowns as g where g.GodownID=(select GodownID   from InvoiceMaster where InvoiceMaster.InvoiceID=ids.InvoiceID )) as CompanyID   
 from InvoiceDetail as ids
inner join inventory as inv on inv.ID=ids.ItemID
inner join inventory as cat on cat.ID=inv.Under
where InvoiceID in (
select InvoiceID from invoicemaster)


GO

