USE [AL_QALAM_LIVE]
GO

/****** Object:  View [dbo].[view_jobcard_list]    Script Date: 8/1/2020 1:01:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








ALTER VIEW [dbo].[view_jobcard_list]
AS
SELECT        ROW_NUMBER() OVER (PARTITION BY [InvoiceType]
ORDER BY [InvoiceType]) row_num, c.AdditionalInfo1 AS CompanyShortForm, CustomerName AS JC_CustomerName, MobileNumber AS JC_MobileNumber, PlateNumber AS JC_PlateNumber, ChassisNumber AS JC_ChassisNumber, InvoiceDate, 
i.KeyField AS KeyField, '' AS CreateByUserName, InvoiceID, DeliveryDate, i.Narration AS Narration, g.Name AS Department, g.godownid AS supplier, c.Name AS Location, InvoiceStatus, '' AS col_Action, i.godownid AS GodownID, 
InvoiceType,i.CreateLog as CreateLog,(select 
' Bill:'+convert(varchar,isnull(sum(NetAmount),0))+' || Paid:'+convert(varchar,isnull(sum(paid),0))+' || Balance:'+convert(varchar,isnull(sum(Balance),0)) from InvoiceMaster where InvoiceType=5 
and InvoiceID in (select invoiceid from invoicemaster where ReferenceInvoiceID=i.InvoiceID) 
group by ReferenceInvoiceID ) as Balance,i.ItemID as ItemID
FROM            InvoiceMaster AS i LEFT OUTER JOIN
                         godowns AS g ON i.Shipperid = g.GodownID left outer JOIN
                         godowns AS c ON c.GodownID = i.GodownID where i.Active=1 








GO

