USE [dimaldxb_meatshop]
GO
/****** Object:  View [dbo].[GeneralLedger1]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














CREATE VIEW [dbo].[GeneralLedger1]
AS
SELECT        vm.TotalAmount, acc.COAName,acc.Attr13,acc.KeyField as KeyField, acc.GLCode,acc.NatureID as NatureID, acc.Under, vd.LedgerID, 0 AS Debit,vd.ReciveCurrency as Received, vd.Credit, vd.ReferenceInvoiceID, vd.VoucherID, vd.Narration, vd.DebitCostCenterID, vd.CostCenterID, vd.CreditLedgerID, vm.VoucherDate, 
                         vm.Active, vd.IsDelete, vm.LocationID, vd.EmployeeID, vm.PeriodID, vm.VoucherTypeID, vm.VoucherNo, g.AdditionalInfo1 AS CostCenter,vd.BranchID,
                             (SELECT   GLCode
                               FROM    dbo.Accounts AS ac
                               WHERE        (COAID = acc.Under)) AS ParentCode, vd.ChequeNo, vm.PONO, vm.DD AS ddno, vm.TC AS tcno, vm.ClientBankAccountName AS clientbankname, vm.IsAuto, vm.ReferenceVoucherID, vm.ChequeDate, 
                         vm.ChequeStatus, vm.PDCPostHoldDate, vm.PDCPostDate, vm.PostDatedCh, vm.IsVoucherReturnCheque, vd.TransactionType,(SELECT   COAName 
                               FROM    dbo.Accounts AS ac
                               WHERE        (COAID = acc.Under)) AS ParentName
FROM            dbo.VoucherDetails AS vd INNER JOIN
                         dbo.VoucherMaster AS vm ON vm.VoucherID = vd.VoucherID INNER JOIN
                         dbo.Accounts AS acc ON acc.COAID = vd.CreditLedgerID LEFT OUTER JOIN
                         dbo.Godowns AS g ON g.GodownID = vd.CostCenterID
WHERE        (vm.Active = 1) AND (vm.Posted = 1) AND (vd.Credit >= 0)  and vm.VoucherTypeID <>0

-- 


















GO
/****** Object:  View [dbo].[GeneralLedger2]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO













CREATE VIEW [dbo].[GeneralLedger2]
AS
SELECT        vm.TotalAmount, acc.COAName,acc.Attr13,acc.KeyField as KeyField, acc.GLCode,acc.NatureID as NatureID, acc.Under, vd.LedgerID, vd.Debit,vd.ReciveCurrency as Received, 0 AS Credit, vd.ReferenceInvoiceID, vd.VoucherID, vd.Narration, vd.DebitCostCenterID, vd.CostCenterID, vd.CreditLedgerID, vm.VoucherDate, 
                         vm.Active, vd.IsDelete, vm.LocationID, vd.EmployeeID, vm.PeriodID, vm.VoucherTypeID, vm.VoucherNo, g.AdditionalInfo1 AS CostCenter,vd.BranchID,
                             (SELECT        GLCode
                               FROM            dbo.Accounts AS ac
                               WHERE        (COAID = acc.Under)) AS ParentCode, vd.ChequeNo, vm.PONO, vm.DD AS ddno, vm.TC AS tcno, vm.ClientBankAccountName AS clientbankname, vm.IsAuto, vm.ReferenceVoucherID, vm.ChequeDate, 
                         vm.ChequeStatus, vm.PDCPostHoldDate, vm.PDCPostDate, vm.PostDatedCh, vm.IsVoucherReturnCheque, vd.TransactionType,(SELECT        COAName 
                               FROM            dbo.Accounts AS ac
                               WHERE        (COAID = acc.Under)) AS ParentName
FROM            dbo.VoucherDetails AS vd INNER JOIN
                         dbo.VoucherMaster AS vm ON vm.VoucherID = vd.VoucherID INNER JOIN
                         dbo.Accounts AS acc ON acc.COAID = vd.LedgerID LEFT OUTER JOIN
                         dbo.Godowns AS g ON g.GodownID = vd.CostCenterID
WHERE        (vm.Active = 1) AND (vm.Posted = 1) AND (vd.Debit >= 0) and vm.VoucherTypeID <>0
--  















GO
/****** Object:  View [dbo].[openingbalance]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[openingbalance] 
as 
select TotalAmount,acc.COAName as COAName,acc.Attr13,acc.KeyField as KeyField,acc.GLCode as GLCode,acc.NatureID as NatureID,acc.Under as Under,vd.LedgerID as LedgerID,vd.Debit  as Debit,vd.ReciveCurrency as Received, abs(vd.Credit) as Credit,vd.ReferenceInvoiceID as ReferenceInvoiceID,vd.VoucherID as VoucherID,
vd.Narration as Narration,DebitCostCenterID,vd.CostCenterID as CostCenterID,CreditLedgerID,
vm.voucherDate as VoucherDate,vm.Active as Active,IsDelete,vm.LocationID as LocationID,vd.EmployeeID as EmployeeID,vm.PeriodID as PeriodID,
vm.VoucherTypeID as VoucherTypeID,vm.VoucherNo as VoucherNo,g.AdditionalInfo1 as CostCenter,vd.BranchID,(select GLCode from accounts as  ac where ac.COAID=acc.under) as ParentCode,
vd.chequeno as chequeno,vd.pono as pono,vm.DD as ddno ,vm.TC as tcno,ClientBankAccountName as clientbankname,
isauto,referencevoucherid,ChequeDate,ChequeStatus,PDCPostHoldDate,PDCPostDate,PostDatedCh,isVoucherReturnCheque,TransactionType,(select COAName  from accounts as  ac where ac.COAID=acc.under) as ParentName
 from VoucherDetails as vd 
inner join VoucherMaster as vm on vm.VoucherID=vd.VoucherID
inner join accounts as acc on acc.COAID=vd.LedgerID
left outer join Godowns as g on g.GodownID=vd.CostCenterID
where   vm.active=1 and vm.posted=1 and (Credit<>0 or debit<>0) and VoucherTypeID=0 



GO
/****** Object:  View [dbo].[GeneralLedger]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[GeneralLedger]
as
select *,LedgerID as COAID,GLCode as GL from openingbalance
union  all
select *,LedgerID as COAID,GLCode as GL  from GeneralLedger2  
union all
select *,CreditLedgerID as COAID,GLCode as GL from GeneralLedger1 

GO
/****** Object:  View [dbo].[list_receivable]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[list_receivable] 
as 
--select ROW_NUMBER() OVER (ORDER BY [InvoiceID])  as row_num,InvoiceID,InvoiceID as Checkbox,g.AdditionalInfo1 as Company,g.Name as FullCompany,g.GodownID as GodownID,invoicemaster.KeyField as KeyField,
--InvoiceDate,DeliveryDate,NetAmount,
--Paid as TotalPaid,(NetAmount-Paid) as TotalPending,Balance,SupplierID,(select GLCode from Accounts where COAID =Invoicemaster.SupplierID) as GLCode,
--(select COAName from Accounts where COAID =Invoicemaster.SupplierID) as SupplierName,(select COAName from Accounts where COAID =
--(select Under from accounts where COAID=Invoicemaster.SupplierID)) as ParentHead from invoicemaster 
--inner join godowns as g on InvoiceMaster.GodownID=g.GodownID where  InvoiceType in(5,9)
--union
--select * from (
select ROW_NUMBER() OVER (ORDER BY VoucherID)  as row_num,VoucherID,VoucherID as Checkbox,g.AdditionalInfo1 as Company,g.GodownID as GodownID,ReferenceInvoiceID,
g.Name as FullCompany,VoucherNo as KeyField,convert(date,VoucherDate) as VoucherDate,ChequeDate,sum(Debit) as Debit,sum(Credit) as Credit,case when isnull(
(select Received from SalesInvoiceMaster where invoiceid=GeneralLedger.ReferenceInvoiceID and Invoicetype=3),(sum(Credit)-isnull(
(select sum(Received) from SalesInvoiceMaster where CustomerID=COAID and Invoicetype=3),0)))<0 then sum(Credit) else isnull(
(select Received from SalesInvoiceMaster where invoiceid=GeneralLedger.ReferenceInvoiceID and Invoicetype=3),(sum(Credit)-isnull(
(select sum(Received) from SalesInvoiceMaster where CustomerID=COAID and Invoicetype=3),0))) end   as Paid,(sum(Debit)-isnull(sum(Received),0)) as TotalPending,
0 as Balance,isnull((select isnull(sum(Debit),0) from GeneralLedger as gl where VoucherTypeID=0 and GL.COAID=GeneralLedger.COAID),0) as Opening,
COAID as SupplierID,GLCode,COAName as SupplierName,(select COAName from Accounts where COAID =
(select Under from accounts where COAID=GeneralLedger.COAID)) as ParentHead,generalledger.Under as Under,VoucherTypeID
from GeneralLedger 
left outer join godowns as g on GeneralLedger.LocationID=g.GodownID where  VoucherTypeID in(8)
group by VoucherID,g.AdditionalInfo1,g.GodownID,ReferenceInvoiceID,g.name,voucherno,Voucherdate,ChequeDate,COAID,GLCode,COAName,generalledger.under,VoucherTypeID


GO
/****** Object:  View [dbo].[ViewStockLedger]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ViewStockLedger]
AS
SELECT        dbo.StockLedger.*, dbo.Inventory.Name, dbo.Inventory.StandardPrice2, dbo.Inventory.ProductBaseType, 
dbo.Inventory.CompanyID, dbo.Godowns.Name AS Department,uom.Abbreviation as UnitName
FROM            dbo.StockLedger INNER JOIN
                         dbo.Inventory ON dbo.StockLedger.ItemID = dbo.Inventory.ID LEFT OUTER JOIN
                         dbo.Godowns ON dbo.StockLedger.GodownID = dbo.Godowns.GodownID
						 left outer join UnitOfMeasure as uom on uom.ID=convert(Numeric(5),case when StockLedger.Attr8='' then 1 else isnull(StockLedger.Attr8,0) end )






GO
/****** Object:  View [dbo].[MaterialSummaryReport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE view [dbo].[MaterialSummaryReport]
as
select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,ide.GodownID as GodownID,g.Name as Company,sum(ide.qty) as Qty,sum(ide.StandardPrice2) as CostPrice,
  sum(ide.Rate) as price,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,

  sum((ide.qty*-1)*ide.rate) as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from InvoiceMaster as im
 left outer join accounts as acc on im.SupplierID=acc.COAID
 
 left outer join ViewStockLedger as ide on ide.InvoiceID=im.InvoiceID
 left outer join Godowns as g on ide.GodownID=g.GodownID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(55,56,57) and ide.qty<0 and ide.GodownID>0 and  
  --Received=im.NetAmount and 
  im.Active=1 group by inv.Name,inv.ID,convert(date,im.InvoiceDate),
  ide.GodownID,g.Name,inv.ProductBaseType,im.CreateLog,inv2.Name,inv2.ID 


  






GO
/****** Object:  View [dbo].[trialtrans]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[trialtrans] 
as 
select Debit,Credit,LocationID,VoucherDate,COAID
from generalLEdger where Active=1 and voucherTypeid>0



GO
/****** Object:  View [dbo].[VatLedger]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[VatLedger]
as 
select gl.*,g.Name as Location from generalledger  as gl
inner join Godowns as g on gl.LocationID=g.GodownID
where coaid in (24,27) 

GO
/****** Object:  View [dbo].[vw_VoucherDetails]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[vw_VoucherDetails]
as

SELECT     vm.VoucherID, 0 AS 'ID', vm.LedgerID, vm.AmountDebit AS 'Debit', vm.AmountCredit AS 'Credit', '' AS Narration, vm.ReferenceInvoiceID AS 'ReferenceInvoiceID', 
                      vm.LocationID, vm.EmployeeID, vm.ChequeNo AS ChequeNoD, vm.Postdate AS 'InstrumentDate', NULL AS 'ChequeAmount', vm.CostCenterID,vm.LedgerID as CreditLedgerID, 0 AS 'dInvoiceTypeID' 
FROM         VoucherMaster vm
WHERE     LedgerID IS NOT NULL
UNION ALL
SELECT     vd.VoucherID, vd.ID, vd.LedgerID, vd.Debit, vd.Credit, vd.Narration, vd.ReferenceInvoiceID, vd.DetailLocationID, vd.EmployeeID, vd.ChequeNo, vd.InstrumentDate, 
                      vd.ChequeAmount, vd.CostCenterID,vd.CreditLedgerID, vd.dInvoiceTypeID
FROM         VoucherDetails vd

GO
/****** Object:  View [dbo].[vw_Aging_Receivable]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Aging_Receivable]
AS
SELECT vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, Debit AS ReceivedAmount

FROM vw_VoucherDetails vd
INNER JOIN VoucherMaster vm ON vm.VoucherID = vd.VoucherID
LEFT JOIN InvoiceMaster im ON im.InvoiceID = vd.ReferenceInvoiceID
WHERE 1=1
AND vm.VoucherTypeID IN (1,2)
AND vm.Posted = 1 AND vm.Active = 1


GO
/****** Object:  View [dbo].[dailyreport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[dailyreport]
as 
select ROW_NUMBER() OVER (partition by im.[GodownID] ORDER BY im.[GodownID]) as row_num,im.GodownID as BranchID,
(select name from inventory where id=(select top 1 Itemid from InvoiceDetail where InvoiceDetail.InvoiceID=im.InvoiceiD)) as ItemName,
im.InvoiceDate as today,im.InvoiceID,
   g.AdditionalInfo1 as Company,g.Name as FullCompany ,AutoVoucherID,0 as VehicleBrand,
   0 as VehicleModel,
   convert(date,InvoiceDate) as InvoiceDate,vm.VoucherNo as VoucherNo,
    convert(date,vm.VoucherDate) as VoucherDate,im.KeyField as KeyField,im.KeyField as TransNumber,'' as TokenNo,
(select sum(debit) from voucherdetails where LedgerID=90 and ReferenceInvoiceID=im.InvoiceID and 
 voucherid in(select voucherid from vouchermaster as vm1 where ReferenceInvoiceID=im.invoiceid and vm1.Active=1)) as Cash,
 IM.CashRefund AS cashrefund, (select sum(debit) from voucherdetails where LedgerID=21 and ReferenceInvoiceID=im.InvoiceID and 
 voucherid in(select voucherid from vouchermaster as vm1 where ReferenceInvoiceID=im.invoiceid and vm1.Active=1)) as Card1,im.TotalAmount,
TotalCurrencyAmount,im.TotalCreditCard as Card,TotalTaxAmount,NetAmount,im.GodownID as GodownID, case when (im.CustomerID=96 or im.CustomerID=0 or im.CustomerID=33)
 then CustomerName else (select ISNULL(COAName,'') from Accounts where COAID=im.CustomerID) end as CustomerName,MobileNumber,
'' as  PlateNumber,vm.CreateLog as CreateLog,0 as symReceived,im.Received as paid,im.EditLog as symCashier,im.CheckedBy as CheckedBy,
 0 as Cheque,0 as Voucher,0 as Credit,0 as Staff,0 as CompanyCredit
from SalesInvoiceMaster as im 
inner join vouchermaster as vm on im.invoiceid=vm.ReferenceInvoiceID
inner join godowns as g on im.GodownID=g.GodownID
where (InvoiceType in (3,8)  or VoucherTypeID in(7,8)) and (vm.Active=1 and im.Active=1) and VoucherNo not like 'j%'  
GO
/****** Object:  View [dbo].[dailyreport2]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE view [dbo].[dailyreport2] 
 as
select * from (
  select InvoiceType,LastModificationTimeStamp as today,invoicedate,replace(CustomerName,'_',' ') as Customer,ParkingNo as TransactionID,Keyno as Token,
 CreateLog as TokenBy,case when EditLog IS null then CreateLog else EditLog end as UserName,
 KeyField,isnull(Paid,0) as Paid,TotalAmount,TotalTaxAmount,NetAmount,InvoiceID,
 --isnull((select sum(credit) from VoucherDetails where ReferenceInvoiceID=InvoiceMaster.InvoiceID and LedgerID=22 and VoucherID in  (select VoucherID from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1 and VoucherTypeID=8) ),0) 
 (NetAmount*-1) as cash,
ISNULL(CreditCard,0) as Creditcard,chequeamt as Cheque,evoucherreceived as Voucher1,Credit,Staff,Company as CompanyCredit,
 isnull(SymReceived,0) as MutakamelaCard,SymCardNo ,GodownID,BranchID
  from InvoiceMaster 
  where  InvoiceStatus not in(3) and Active=1 and GodownID>0  and InvoiceType in (61)
Union All 
 select InvoiceType,InvoiceDate as today,invoicedate,replace(CustomerName,'_',' ') as Customer,'' as TransactionID,0 as Token,
 CreateLog as TokenBy,case when EditLog IS null then CreateLog else EditLog end as UserName,
 KeyField,isnull(Received,0) as Paid,TotalAmount,TotalTaxAmount,NetAmount,InvoiceID,
 isnull((select sum(debit) from VoucherDetails where ReferenceInvoiceID=SIM.InvoiceID and LedgerID=22 and VoucherID in 
 (select VoucherID from VoucherMaster where ReferenceInvoiceID=SIM.InvoiceID and Active=1 and VoucherTypeID=8) ),0) as cash,
isnull((select sum(debit) from VoucherDetails where ReferenceInvoiceID=SIM.InvoiceID and LedgerID=21 and VoucherID in 
 (select VoucherID from VoucherMaster where ReferenceInvoiceID=SIM.InvoiceID and Active=1 and VoucherTypeID=8) ),0) as Creditcard,0 as Cheque,0 as Voucher1,0 as Credit,0 Staff,0 as CompanyCredit,
 isnull(0,0) as MutakamelaCard,'' as SymCardNo ,GodownID,0 as BranchID
  from SalesInvoiceMaster as SIM
  where  InvoiceStatus not in(3) and Active=1 and GodownID>0 ) t




GO
/****** Object:  View [dbo].[dailyreport3]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 CREATE view [dbo].[dailyreport3]
as 
 select *,u.NetAmount-(u.MutakamelaCard+u.cash+u.Card) as Balance from(
 select SupplierID,
		InvoiceID,
		LastModificationTimeStamp as today,
        Invoicedate,
		replace(CustomerName,'_',' ') as Customer,
		ParkingNo as TransactionID,Keyno as Token,
		CreateLog as TokenBy,
		CustomerName,
		MobileNumber,
		PlateNumber,
		case when EditLog IS null then CreateLog else EditLog end as UserName,
		(select top 1 Name from godowns where GodownID=InvoiceMaster.GodownID and Active=1) as FullCompany,
		(select top 1 AdditionalInfo1 from godowns where GodownID=InvoiceMaster.GodownID and Active=1)  as Company,
		(select top 1 VoucherNo from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1)  as VoucherNo,
		case when SymReceived>0 then EditLog else (select top 1 CreateLog from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1) end  as CreateLog,

		KeyField,isnull(Paid,0) as Paid,
		TotalAmount,
		TotalTaxAmount,
		NetAmount,
		isnull((select sum(debit) from VoucherDetails where ReferenceInvoiceID=InvoiceMaster.InvoiceID and LedgerID=90 and VoucherID in (select VoucherID from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1) ),0) as cash,
		isnull((select sum(debit) from VoucherDetails where ReferenceInvoiceID=InvoiceMaster.InvoiceID and LedgerID=98 and VoucherID in (select VoucherID from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1)),0) as Card,
		isnull(SymReceived,0) as MutakamelaCard,SymCardNo ,GodownID 
  
  from InvoiceMaster where  InvoiceStatus=4 and Active=1 and GodownID>0
  ) as u



GO
/****** Object:  View [dbo].[dailyreportEtraffic]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE view [dbo].[dailyreportEtraffic] 
 as
 select InvoiceDate as today,invoicedate,replace(CustomerName,'_',' ') as Customer,0 as TransactionID,0 as Token,
 CreateLog as TokenBy,case when EditLog IS null then CreateLog else EditLog end as UserName,
 KeyField,isnull(received,0) as Paid,TotalAmount,TotalTaxAmount,NetAmount,InvoiceID,0 as BranchID,
 isnull((select sum(credit) from VoucherDetails where ReferenceInvoiceID=InvoiceMaster.InvoiceID and CreditLedgerID=22 and 
 VoucherID in (select VoucherID from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1) ),0) as cash,
0 as Creditcard,
 0 as MutakamelaCard,'' as SymCardNo ,GodownID 
  from SalesInvoiceMaster as InvoiceMaster where   Active=1 and GodownID>0 






GO
/****** Object:  View [dbo].[dailyreportEtraffic1]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[dailyreportEtraffic1] 
 as
 select InvoiceDate as today,invoicedate,replace(CustomerName,'_',' ') as Customer,0 as TransactionID,0 as Token,
 CreateLog as TokenBy,case when EditLog IS null then CreateLog else EditLog end as UserName,
 KeyField,isnull(received,0) as Paid,TotalAmount,TotalTaxAmount,NetAmount,InvoiceID,0 as BranchID,
 isnull((select sum(credit) from VoucherDetails where ReferenceInvoiceID=InvoiceMaster.InvoiceID and CreditLedgerID=22 and 
 VoucherID in (select VoucherID from VoucherMaster where ReferenceInvoiceID=InvoiceMaster.InvoiceID and Active=1) ),0) as cash,
0 as Creditcard,
 0 as MutakamelaCard,'' as SymCardNo ,GodownID 
  from SalesInvoiceMaster as InvoiceMaster where
  Active=1 and GodownID>0 

GO
/****** Object:  View [dbo].[ProductionSummaryReport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






CREATE view [dbo].[ProductionSummaryReport]
as
select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,g.GodownID as GodownID,g.Name as Company,sum(ide.qty) as Qty,sum(ide.TaxAmount) as Vat,
  inv.StandardPrice1 as price,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,

  sum(ide.NetAmount) as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from InvoiceMaster as im
 left outer join accounts as acc on im.SupplierID=acc.COAID
 left outer join Godowns as g on im.GodownID=g.GodownID
 left outer join InvoiceDetail as ide on ide.InvoiceID=im.InvoiceID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(55,56,57) and 
  --Received=im.NetAmount and 
  im.Active=1 group by inv.Name,inv.ID,convert(date,im.InvoiceDate),inv.StandardPrice1,
  g.GodownID,g.Name,inv.ProductBaseType,im.CreateLog,inv2.Name,inv2.ID 








GO
/****** Object:  View [dbo].[PurchaseSummaryReport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE view [dbo].[PurchaseSummaryReport]
as
select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,g.GodownID as GodownID,g.Name as Company,sum(ide.qty) as Qty,sum(ide.TaxAmount) as Vat,
  ide.UnitPrice as price,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,

  sum(ide.NetAmount) as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from InvoiceMaster as im
 left outer join accounts as acc on im.SupplierID=acc.COAID
 left outer join Godowns as g on im.GodownID=g.GodownID
 left outer join InvoiceDetail as ide on ide.InvoiceID=im.InvoiceID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(3,4) and 
  --Received=im.NetAmount and 
  im.Active=1 group by inv.Name,inv.ID,convert(date,im.InvoiceDate),ide.UnitPrice,
  g.GodownID,g.Name,inv.ProductBaseType,im.CreateLog,inv2.Name,inv2.ID 






GO
/****** Object:  View [dbo].[salesdetailreport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





Create view [dbo].[salesdetailreport]
as
select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,g.Name as Company,(ide.qty) as Qty,
 (ide.TaxAmount) as Vat,g.GodownID as GodownID,
  ide.UnitPrice as price,im.KeyField as KeyField,im.InvoiceDate as VoucherDateTime,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,im.EditLog as Cashier,im.GodownID as BranchID,
  (ide.NetAmount) as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from SalesInvoiceMaster as im
 left outer join accounts as acc on im.CustomerID=acc.COAID
 left outer join Godowns as g on im.GodownID=g.GodownID
 left outer join SalesInvoiceDetail as ide on ide.InvoiceID=im.InvoiceID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(3,8) 
  --and im.ItemID<>5 and im.NetAmount=Paid 
  and im.Active=1







GO
/****** Object:  View [dbo].[salessummaryreport]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE view [dbo].[salessummaryreport]
as
select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,g.GodownID as GodownID,g.Name as Company,sum(ide.qty) as Qty,sum(ide.TaxAmount) as Vat,
  ide.UnitPrice as price,case when ide.StandardPrice3=0 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)) else  case when count(inv.ID)>1 then
   ((isnull(ide.StandardPrice3,0)/sum(qty))*sum(qty))
   else (isnull(ide.StandardPrice3,0)) end  end as CostPrice,case when sum(ide.qty)>0
  then   ((ide.UnitPrice*sum(ide.qty))-((case when ide.StandardPrice3=0 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)) else  (isnull(ide.StandardPrice3,0)*sum(qty)) end))) else
   ((ide.UnitPrice*sum(ide.qty))+((case when ide.StandardPrice3=0 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)) else  (isnull(ide.StandardPrice3,0)*sum(qty)) end))) end  as GP,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,

  sum(ide.NetAmount) as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from SalesInvoiceMaster as im
 left outer join accounts as acc on im.CustomerID=acc.COAID
 left outer join Godowns as g on im.GodownID=g.GodownID
 left outer join SalesInvoiceDetail as ide on ide.InvoiceID=im.InvoiceID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(3,8) and 
  --Received=im.NetAmount and 
  im.Active=1 group by inv.Name,inv.ID,convert(date,im.InvoiceDate),ide.UnitPrice,
  g.GodownID,g.Name,inv.ProductBaseType,im.CreateLog,inv2.Name,inv2.ID,ide.StandardPrice3

union all 

select 
 inv2.ID as under,inv2.Name as Category,inv.ID as ItemID, inv.Name as service,g.GodownID as GodownID,g.Name as Company,sum(ide.qty*-1) as Qty,sum(ide.TaxAmount) as Vat,
  ide.UnitPrice as price,
  case when inv.StandardPrice3=0 or Inv.ID<>1038 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)) 
  else  case when count(inv.ID)>1 then    (case when inv.ID=1038 or ide.Standardprice3>0 then ide.Standardprice3    else isnull(inv.StandardPrice3,0)*sum(qty) end )
   else ( case when inv.ID=1038 then ide.Standardprice3 else isnull((inv.StandardPrice3*-1),0) end) end  end as CostPrice,
   case when sum(ide.qty)>0
  then   ((ide.UnitPrice*sum(ide.qty)*-1)-((case when inv.StandardPrice3=0 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)*-1) else 
   (isnull(inv.StandardPrice3,0)*sum(qty)*-1) end))) else
   ((ide.UnitPrice*sum(ide.qty)*-1)+((case when inv.StandardPrice3=0 then ((select StandardPrice3 from inventory where ID=Inv.ID)*sum(ide.qty)*-1) else
     (isnull((inv.StandardPrice3),0)*sum(qty*-1)) end))) end  as GP,
  inv.ProductBaseType as ProductType,im.CreateLog as UserName,

  sum(ide.NetAmount)*-1 as NetAmount,convert(date,im.InvoiceDate) as InvoiceDate from InvoiceMaster as im
 left outer join accounts as acc on im.SupplierID=acc.COAID
 left outer join Godowns as g on im.GodownID=g.GodownID
 left outer join InvoiceDetail as ide on ide.InvoiceID=im.InvoiceID
  left outer join Inventory as inv on ide.ItemID=inv.ID
  left outer join Inventory as inv2 on inv.under=inv2.ID

  where  
  --InvoiceStatus=4 and 
  InvoiceType in(61) and 
  --Received=im.NetAmount and 
  im.Active=1 group by inv.Name,inv.ID,convert(date,im.InvoiceDate),ide.UnitPrice,ide.Standardprice3,
  g.GodownID,g.Name,inv.ProductBaseType,im.CreateLog,inv2.Name,inv2.ID,inv.StandardPrice3





GO
/****** Object:  View [dbo].[view_account_list]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[view_account_list]
AS
SELECT        ROW_NUMBER() OVER (ORDER BY [COAID]) AS row_num, COAName, Hierarchy, Under, CostCenterID, COAID, GLCode, NatureID, acc.[Group] AS GroupID,
    (SELECT        COAName
      FROM            accounts AS c
      WHERE        c.COAID = acc.Under) AS L3,
    (SELECT        COAName
      FROM            accounts AS c
      WHERE        c.COAID =
                                    (SELECT        Under
                                      FROM            accounts AS cc
                                      WHERE        cc.COAID = acc.Under)) AS L2,
    (SELECT        COAName
      FROM            accounts AS c
      WHERE        c.COAID =
                                    (SELECT        Under
                                      FROM            accounts AS cc
                                      WHERE        cc.COAID =
                                                                    (SELECT        Under
                                                                      FROM            accounts AS ccc
                                                                      WHERE        ccc.COAID = acc.Under))) AS COAHead, Degree, '' AS col_Action, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6, Attr7, Attr8, Attr9, Attr10, Attr11, Attr12, Attr13, Attr14, Attr15, Attr16, Attr17, Attr18, Attr19, Attr20,Attr21,Attr22,Attr23,Attr24, 
ContactPerson1, ContactDesignation1, ContactPerson2, ContactDesignation2, ContactPerson3, ContactDesignation3,Active
FROM            accounts AS acc





GO
/****** Object:  View [dbo].[view_jobcard_list]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[view_jobcard_list]
AS
SELECT        ROW_NUMBER() OVER (PARTITION BY [InvoiceType]
ORDER BY [InvoiceType]) row_num,i.Email as Email, c.AdditionalInfo1 AS CompanyShortForm,i.SupplierID as CustomerID, CustomerName AS JC_CustomerName, MobileNumber AS JC_MobileNumber, 
PlateNumber AS JC_PlateNumber, ChassisNumber AS JC_ChassisNumber, InvoiceDate,NetAmount, 
i.KeyField AS KeyField, i.CreateLog 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,i.Paid as paid,i.CheckedBy as Manager,
InvoiceType,i.ReferenceInvoiceID as ReferenceInvoiceID, 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  InvoiceID in (select invoiceid from invoicemaster where ReferenceInvoiceID=i.InvoiceID) 
group by ReferenceInvoiceID ) as Balance,i.ItemID as ItemID,i.KeyNo as KeyNo,CheckedBy,i.BranchID as BranchID,convert(date,i.LastModificationTimeStamp) as PaymentDate
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
/****** Object:  View [dbo].[view_jobcard_list_special]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_jobcard_list_special]
AS
SELECT        0  as row_num,i.Email as Email, c.AdditionalInfo1 AS CompanyShortForm,i.CustomerID as CustomerID, CustomerName AS JC_CustomerName,
 MobileNumber AS JC_MobileNumber, 
0 AS JC_PlateNumber,0  AS JC_ChassisNumber, i.InvoiceDate as InvoiceDate,NetAmount, 
i.KeyField AS KeyField, i.CreateLog AS CreateByUserName, i.InvoiceID as InvoiceID,i.InvoiceDate as  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,i.Received as paid,i.CheckedBy as Manager,
InvoiceType,i.ReferenceInvoiceID as ReferenceInvoiceID, i.CreateLog as CreateLog,(select 
' Bill:'+convert(varchar,isnull(sum(NetAmount),0))+' || Paid:'+convert(varchar,isnull(sum(Received),0))+' || Balance:'+convert(varchar,isnull(sum(Balance),0))
 from SalesInvoiceMaster SIM 
where  SIM.InvoiceID in (select SI.invoiceid from SalesInvoiceMaster AS SI where SI.ReferenceInvoiceID=i.InvoiceID) 
group by SIM.ReferenceInvoiceID ) as Balance,0 as ItemID,0 as KeyNo,CheckedBy,0 as BranchID,convert(date,i.InvoiceDate) as PaymentDate
FROM            SalesInvoiceMaster 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
/****** Object:  View [dbo].[view_jobcard_list1]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[view_jobcard_list1]
AS
SELECT        ROW_NUMBER() OVER (PARTITION BY [InvoiceType]
ORDER BY [InvoiceType]) row_num,i.Email as Email, c.AdditionalInfo1 AS CompanyShortForm,i.SupplierID as CustomerID, CustomerName AS JC_CustomerName, MobileNumber AS JC_MobileNumber, 
PlateNumber AS JC_PlateNumber, ChassisNumber AS JC_ChassisNumber, InvoiceDate, 
i.KeyField AS KeyField, i.CreateLog 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,i.Paid as paid,i.CheckedBy as Manager,
InvoiceType,i.ReferenceInvoiceID as ReferenceInvoiceID, i.CreateLog as CreateLog,'' as Balance,i.ItemID as ItemID,i.KeyNo as KeyNo,CheckedBy,i.BranchID as BranchID
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
/****** Object:  View [dbo].[view_list_pendingSupplies]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE 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
/****** Object:  View [dbo].[view_MRQ_LIST]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[view_MRQ_LIST]
AS
SELECT        ROW_NUMBER() OVER (PARTITION BY [InvoiceType]
ORDER BY [InvoiceType]) row_num, c.AdditionalInfo1 AS CompanyShortForm,c.Name as Company, invoicedate,i.Shipperid as shipperid,
 i.KeyField AS keyfield, i.CreateLog AS CreateByUserName, InvoiceType,invoiceid, deliverydate,Notes, 
narration, g.Name AS Department, g.godownid AS supplier, 
c.godownid AS godownid, InvoiceStatus,(select KeyField from InvoiceMaster im2 where im2.InvoiceID=i.ReferenceInvoiceID) as JobCardNo,
(Case when InvoiceStatus=1 then 'Pending' when InvoiceStatus=2 then 'Accepted' when InvoiceStatus=3 then 'Rejected' when InvoiceStatus=4 then 'Closed'  else '' end) as OrderStatus,
 '' AS col_Action, billno, totalamount, 
discountamount, convert(numeric(7,2), i.TotalTaxAmount) AS taxamount, netamount, shippingmethod, ispriceinclusivetax, 
ispriceperunit, vatoption, ReferenceInvoiceID,
    (SELECT        Keyfield
      FROM            InvoiceMaster AS im
      WHERE        im.InvoiceID = i.ReferenceInvoiceID) AS ReferenceInvoiceNo, PurchaseLedgerID, VATLedgerID, i.SupplierID AS supplierid,(select COAName from accounts where Coaid=i.supplierid) as SupplierName,
	  i.CreateLog as CreateLog,i.EditLog as EditLog,i.DeleteLog as DeleteLog,i.Active as Active
FROM            InvoiceMaster AS i LEFT OUTER JOIN
                         godowns AS g ON i.Shipperid = g.GodownID INNER JOIN
                         godowns AS c ON c.GodownID = i.GodownID














GO
/****** Object:  View [dbo].[view_MRQ_LIST2]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE view [dbo].[view_MRQ_LIST2]
AS 
   select ROW_NUMBER() OVER (
      PARTITION BY [InvoiceType]
      ORDER BY [InvoiceType]
   ) row_num,c.Name as Company,(select Coaname from accounts where coaid=i.SupplierID) as Supplier,(select Coaname from accounts where coaid=i.SupplierID) as SupplierName ,i.ShipperID as shipperid,c.AdditionalInfo1 as CompanyShortForm,
   convert(date,InvoiceDate) as InvoiceDate,
   i.KeyField as KeyField,i.CreateLog as CreateByUserName,InvoiceID,convert(date,DeliveryDate) as DeliveryDate,Narration,g.Name as Department,i.BillNO as Billno,
   InvoiceStatus,(select KeyField from InvoiceMaster im2 where im2.InvoiceID=i.ReferenceInvoiceID) as JobCardNo,
   (select CreateLog from InvoiceMaster im2 where im2.InvoiceID=i.ReferenceInvoiceID) as Advisor,
   (Case when InvoiceStatus=1 then 'Pending' when InvoiceStatus=2 then 'Accepted' when InvoiceStatus=3 then 'Rejected' when InvoiceStatus=4 then 'Closed'  else '' end) as OrderStatus,
   '' as col_Action ,c.godownid as godownid,totalamount,discountamount,totaltaxamount as taxamount,netamount,InvoiceType,i.Active as Active,
   i.CreateLog as CreateLog,i.EditLog as EditLog,i.DeleteLog as DeleteLog
   from InvoiceMaster as i left outer join godowns  as g on i.Shipperid=g.GodownID
   inner join godowns as c on c.GodownID=i.GodownID






GO
/****** Object:  View [dbo].[vw_Attributes]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_Attributes]

AS

SELECT DISTINCT Attribute FROM 
(
   SELECT Attr1 AS Attribute FROM Inventory
   UNION ALL
   SELECT Attr2 FROM Inventory
   UNION ALL
   SELECT Attr3 FROM Inventory
   UNION ALL
   SELECT Attr4 FROM Inventory
   UNION ALL
   SELECT Attr5 FROM Inventory
   UNION ALL
   SELECT Attr6 FROM Inventory
   UNION ALL
   SELECT Attr7 FROM Inventory
   UNION ALL
   SELECT Attr8 FROM Inventory
   UNION ALL
   SELECT Attr9 FROM Inventory
   UNION ALL
   SELECT Attr10 FROM Inventory
   UNION ALL
   SELECT Attr11 FROM Inventory
   UNION ALL
   SELECT Attr12 FROM Inventory
   UNION ALL
   SELECT Attr13 FROM Inventory
   UNION ALL
   SELECT Attr14 FROM Inventory
   UNION ALL
   SELECT Attr15 FROM Inventory
   UNION ALL
   SELECT Attr16 FROM Inventory
   UNION ALL
   SELECT Attr17 FROM Inventory
   UNION ALL
   SELECT Attr18 FROM Inventory
   UNION ALL
   SELECT Attr19 FROM Inventory
   UNION ALL
   SELECT Attr20 FROM Inventory
) AS u
WHERE LTRIM(RTRIM(ISNULL(u.Attribute,''))) <> '' 



GO
/****** Object:  View [dbo].[vw_Customer_Receivable_Detail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Customer_Receivable_Detail]

AS

SELECT     0 AS SortOrder, aob.LedgerID AS VendorID, - 1 AS VoucherID, - 1 AS InvoiceID, ISNULL(aob.OpeningBalancedebit, 0) AS 'Due'
FROM         AccountsOpeningBalance aob INNER JOIN
                      Accounts a ON a.COAID = aob.LedgerID
WHERE     1 = 1 AND a.Active = 1 AND a.NatureID = 14 AND aob.OpeningBalanceDebit <> 0
UNION ALL
SELECT     1 AS SortOrder, vm.LedgerID AS VendorID, vm.VoucherID, vm.ReferenceInvoiceID AS InvoiceID, vm.Amountdebit AS 'Due'
FROM         VoucherMaster vm INNER JOIN
                      Accounts a ON a.COAID = vm.LedgerID
WHERE     vm.Amountdebit <> 0 AND vm.Active = 1 AND vm.Posted = 1 AND a.NatureID = 14
UNION ALL
SELECT     1 AS SortOrder, vd.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, vd.debit AS 'Due'
FROM         VoucherDetails vd INNER JOIN
                      Accounts a ON vd.LedgerID = a.COAID INNER JOIN
                      VoucherMaster vm ON vm.VoucherID = vd.VoucherID
WHERE     vd.debit <> 0 AND vm.Active = 1 AND vm.Posted = 1 AND a.NatureID IN (14)


GO
/****** Object:  View [dbo].[vw_Customer_Received_Detail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_Customer_Received_Detail]

AS

SELECT     0 AS SortOrder, aob.LedgerID AS VendorID, - 1 AS VoucherID, - 1 AS InvoiceID, 0 AS ReceivedAccountID, ISNULL(aob.OpeningBalanceCredit, 0) AS 'Recid'
FROM AccountsOpeningBalance aob INNER JOIN
Accounts a ON a.COAID = aob.LedgerID
WHERE     1 = 1 AND a.Active = 1 AND a.NatureID = 14 AND aob.OpeningBalanceDebit <> 0
UNION ALL
SELECT     1 AS SortOrder, vm.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, vd.LedgerID AS ReceivedAccountID, vd.Debit AS 'Recid'
FROM VoucherDetails vd INNER JOIN
VoucherMaster vm ON vm.VoucherID = vd.VoucherID
WHERE     vd.Debit <> 0 AND vm.Active = 1 AND vm.Posted = 1 AND vd.VoucherID IN
(SELECT vm.VoucherID
FROM VoucherMaster vm INNER JOIN
Accounts a ON vm.LedgerID = a.COAID
WHERE      vm.AmountCredit <> 0 AND vm.Active = 1 AND vm.Posted = 1 AND a.NatureID IN (14))
UNION ALL
SELECT     1 AS SortOrder, vd.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, 0 AS ReceivedAccountID, vd.Credit AS 'Recid'
FROM VoucherDetails vd INNER JOIN
Accounts a ON vd.LedgerID = a.COAID INNER JOIN
VoucherMaster vm ON vm.VoucherID = vd.VoucherID
WHERE     vd.Credit <> 0 AND vm.Active = 1 AND vm.Posted = 1 AND a.NatureID IN (14)

GO
/****** Object:  View [dbo].[vw_EmployeeLeaves]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_EmployeeLeaves] 
AS

SELECT 1 AS LeaveNo,SalesManID AS EmployeeID,Leave1ID AS LeaveID,Leave1Allocated AS Allocated,Leave1Utilized AS Utilized,Leave1Balance AS Balance FROM SalesManInformation
UNION ALL
SELECT 2 AS LeaveNo,SalesManID,Leave2ID,Leave2Allocated,Leave2Utilized,Leave2Balance FROM SalesManInformation
UNION ALL
SELECT 3 AS LeaveNo,SalesManID,Leave3ID,Leave3Allocated,Leave3Utilized,Leave3Balance FROM SalesManInformation
UNION ALL
SELECT 4 AS LeaveNo,SalesManID,Leave4ID,Leave4Allocated,Leave4Utilized,Leave4Balance FROM SalesManInformation
UNION ALL
SELECT 5 AS LeaveNo,SalesManID,Leave5ID,Leave5Allocated,Leave5Utilized,Leave5Balance FROM SalesManInformation



GO
/****** Object:  View [dbo].[vw_InvoiceDetail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_InvoiceDetail] AS

SELECT 

id.TransactionID,
id.InvoiceID,
id.ItemID,
CASE WHEN (im.InvoiceType=2 AND id.Qty<0) THEN 'PurchaseReturn'
WHEN im.InvoiceType=3 THEN 'PurchaseReturn'
WHEN (im.InvoiceType=2 AND id.Qty>=0) THEN 'PurchaseInvoice'
WHEN im.InvoiceType=0 THEN 'PurchaseQuotation'
WHEN im.InvoiceType=1 THEN 'PurchaseOrder'
END AS InvoiceTypeDesc,

CASE WHEN (im.InvoiceType=2 AND id.Qty<0) THEN id.Qty -- PurchaseReturn via PurchaseInvoice
WHEN im.InvoiceType=3 THEN -ABS(id.Qty) --PurchaseReturn
WHEN (im.InvoiceType=2 AND id.Qty>=0) THEN id.Qty --Purchases
ELSE id.Qty END AS SQty

FROM InvoiceDetail id
INNER JOIN InvoiceMaster im ON id.InvoiceID=im.InvoiceID



GO
/****** Object:  View [dbo].[vw_InvoiceDiscount]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--Corrections Made
--April - 14, 2010
--Jan-20,2013 Added levelno=0 check
CREATE  VIEW [dbo].[vw_InvoiceDiscount]
AS

SELECT InvoiceID,SUM(Amount) AS 'vw_Amount',SUM(DiscountAmount) AS 'vw_Discount',SUM(Amount)-SUM(DiscountAmount) AS 'vw_NetAmount' FROM
(
SELECT InvoiceID,Amount,DiscountAmount
FROM SalesInvoiceDetail WHERE ISNULL(BOM_LevelNo,0)=0
UNION ALL
SELECT InvoiceID,0,DiscountAmount-Rounding
--SELECT InvoiceID,0,DiscountAmount+Rounding
FROM SalesInvoiceMaster
) AS u
GROUP BY InvoiceID



GO
/****** Object:  View [dbo].[vw_LedgerBalance]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_LedgerBalance]

AS

SELECT     LedgerID, Debit, Credit, Balance
FROM         (SELECT     LedgerID, SUM(AmountDebit) AS Debit, SUM(AmountCredit) AS Credit, SUM(AmountDebit) - SUM(AmountCredit) AS Balance
                       FROM          (SELECT     LedgerID, AmountDebit, AmountCredit
                                               FROM          dbo.VoucherMaster
                                               UNION ALL
                                               SELECT     LedgerID, Debit, Credit
                                               FROM         dbo.VoucherDetails
                                               UNION ALL
                                               SELECT     LedgerID, OpeningBalanceDebit, OpeningBalanceCredit
                                               FROM         dbo.AccountsOpeningBalance) AS v
                       GROUP BY LedgerID) AS LedgerBalance
WHERE     (Balance <> 0)

GO
/****** Object:  View [dbo].[vw_POS_DataTerminalInputDetailStatusSetup]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_POS_DataTerminalInputDetailStatusSetup]
AS
SELECT 0 AS StatusID, 'Barcode Mismatch' AS Status
UNION ALL
SELECT 1 AS StatusID, 'Ok' AS Status
UNION ALL
SELECT 2 AS StatusID, 'Multiple Barcodes Found' AS Status



GO
/****** Object:  View [dbo].[vw_Sales_and_SalesReturn]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Sales_and_SalesReturn]

AS

--Sales

/*Debug portion start
DECLARE @PeriodID bigint SET @PeriodID = 1
DECLARE @FromDate smalldatetime SET @FromDate = '2011-04-03 00:00:00'
DECLARE @TillDate smalldatetime SET @TillDate = NULL -- '2011-04-03 00:00:00'
Debug portion end*/

/*This will also be transactional */
SELECT sim.InvoiceID, sim.InvoiceDate, sim.PeriodID, sid.ItemID,sid.Qty FROM SalesInvoiceDetail sid
INNER JOIN SalesInvoiceMaster sim ON sid.InvoiceID=sim.InvoiceID
/*Must see invoicetype check (sales invoice)
3=sales invoice */

WHERE sim.Active=1 AND sim.InvoiceType=3
/*AND sim.PeriodID=@PeriodID 
AND (@FromDate IS NULL OR sim.InvoiceDate>=@FromDate) 
AND (@TillDate IS NULL OR sim.InvoiceDate<=@TillDate)*/
UNION ALL

/*Less: Sales Returns */
SELECT sim.InvoiceID, sim.InvoiceDate, sim.PeriodID, sid.ItemID,-sid.Qty FROM SalesInvoiceDetail sid
INNER JOIN SalesInvoiceMaster sim ON sid.InvoiceID=sim.InvoiceID

/*Must see invoicetype check (sales invoice)
3=sales invoice*/
WHERE sim.Active=1 AND sim.InvoiceType=4
/*
AND sim.PeriodID=@PeriodID 
AND (@FromDate IS NULL OR sim.InvoiceDate>=@FromDate) 
AND    (@TillDate IS NULL OR sim.InvoiceDate<=@TillDate)
*/

GO
/****** Object:  View [dbo].[vw_SalesInvoiceDetail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_SalesInvoiceDetail]
AS
SELECT     sid.TransactionID, sid.InvoiceID, sid.ItemID, CASE WHEN (sim.InvoiceType IN (3, 17) AND sid.Qty < 0) THEN 'SalesReturn' WHEN sim.InvoiceType = 4 THEN 'SalesReturn' WHEN (sim.InvoiceType IN (3, 17)  AND
                       sid.Qty >= 0) 
                      THEN 'Sales' WHEN sim.InvoiceType = 0 THEN 'SalesEnquiry' WHEN sim.InvoiceType = 1 THEN 'SalesQuotation' WHEN sim.InvoiceType = 2 THEN 'SalesOrder' WHEN sim.InvoiceType = 5 THEN 'EmloyeeeTimeCard' 
                       WHEN sim.InvoiceType = 6 THEN 'ServiceClaim' WHEN sim.InvoiceType = 7 THEN 'DineIn' WHEN sim.InvoiceType = 8 THEN 'TakeAway' WHEN sim.InvoiceType = 9 THEN 'HomeDelivery' END AS InvoiceTypeDesc,
                       CASE WHEN (sim.InvoiceType IN (3, 17)  AND sid.Qty < 0) THEN sid.Qty WHEN sim.InvoiceType = 4 THEN - ABS(sid.Qty) WHEN (sim.InvoiceType IN (3, 17)  AND sid.Qty >= 0) 
                      THEN sid.Qty ELSE sid.Qty END AS SQty 
FROM         dbo.SalesInvoiceDetail AS sid INNER JOIN 
             dbo.SalesInvoiceMaster AS sim ON sid.InvoiceID = sim.InvoiceID AND sim.Active = 1 


GO
/****** Object:  View [dbo].[vw_SalesInvoiceType]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_SalesInvoiceType]
AS

SELECT    'Enquiry' AS InvoiceTypeDesc,0 AS InvoiceTypeID
UNION ALL
SELECT    'Quotation' AS InvoiceTypeDesc,1 AS InvoiceTypeID
UNION ALL
SELECT    'SalesOrder' AS InvoiceTypeDesc,2 AS InvoiceTypeID
UNION ALL
SELECT    'SalesInvoice' AS InvoiceTypeDesc,3 AS InvoiceTypeID
UNION ALL
SELECT    'SalesReturn' AS InvoiceTypeDesc,4 AS InvoiceTypeID
UNION ALL
SELECT    'EmployeeTimeCard' AS InvoiceTypeDesc,5 AS InvoiceTypeID
UNION ALL
SELECT    'ServiceClaim' AS InvoiceTypeDesc,6 AS InvoiceTypeID
UNION ALL
SELECT    'DineIn' AS InvoiceTypeDesc,7 AS InvoiceTypeID
UNION ALL
SELECT    'TakeAway' AS InvoiceTypeDesc,8 AS InvoiceTypeID
UNION ALL
SELECT    'HomeDelivery' AS InvoiceTypeDesc,9 AS InvoiceTypeID



GO
/****** Object:  View [dbo].[vw_SalesManCommissionTypes]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_SalesManCommissionTypes]
AS

--Enquiry
SELECT SalesManID, 
0 AS InvoiceType, 
ISNULL(SaleEnquiry_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(SaleEnquiry_CommissionPercentOnTotalAmount*NULLIF(SaleEnquiry_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(SaleEnquiry_CommissionPercentOnContributionAmount*NULLIF(SaleEnquiry_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(SaleEnquiry_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleEnquiry_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(SaleEnquiry_CommissionAmount*NULLIF(SaleEnquiry_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(SaleEnquiry_CommissionType,0) AS CommissionType,
SaleEnquiry_LocationGroupID AS LocationGroupID

FROM SalesManInformation

UNION ALL

--Quotation
SELECT SalesManID, 
1 AS InvoiceType, 
ISNULL(SaleQuotation_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(SaleQuotation_CommissionPercentOnTotalAmount*NULLIF(SaleQuotation_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(SaleQuotation_CommissionPercentOnContributionAmount*NULLIF(SaleQuotation_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(SaleQuotation_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleQuotation_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(SaleQuotation_CommissionAmount*NULLIF(SaleQuotation_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(SaleQuotation_CommissionType,0) AS CommissionType,
SaleQuotation_LocationGroupID AS LocationGroupID

FROM SalesManInformation

UNION ALL

--SalesOrder
SELECT SalesManID, 
2 AS InvoiceType, 
ISNULL(SaleOrder_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(SaleOrder_CommissionPercentOnTotalAmount*NULLIF(SaleOrder_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(SaleOrder_CommissionPercentOnContributionAmount*NULLIF(SaleOrder_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(SaleOrder_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleOrder_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(SaleOrder_CommissionAmount*NULLIF(SaleOrder_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(SaleOrder_CommissionType,0) AS CommissionType,
SaleOrder_LocationGroupID AS LocationGroupID

FROM SalesManInformation

UNION ALL

--SalesInvoice
SELECT SalesManID, 
3 AS InvoiceType, 
ISNULL(SaleInvoice_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(SaleInvoice_CommissionPercentOnTotalAmount*NULLIF(SaleInvoice_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(SaleInvoice_CommissionPercentOnContributionAmount*NULLIF(SaleInvoice_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(SaleInvoice_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleInvoice_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(SaleInvoice_CommissionAmount*NULLIF(SaleInvoice_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(SaleInvoice_CommissionType,0) AS CommissionType,
SaleInvoice_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

UNION ALL

--SalesReturn
SELECT SalesManID, 
4 AS InvoiceType, 
ISNULL(SaleReturn_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(SaleReturn_CommissionPercentOnTotalAmount*NULLIF(SaleReturn_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(SaleReturn_CommissionPercentOnContributionAmount*NULLIF(SaleReturn_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(SaleReturn_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleReturn_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(SaleReturn_CommissionAmount*NULLIF(SaleReturn_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(SaleReturn_CommissionType,0) AS CommissionType,
SaleReturn_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

UNION ALL

--SELECT SalesManID, 
--EmployeeTimeCard
--5 AS InvoiceType, 
--ISNULL(EmployeeTimeCard_IsCommissionApplicable,0) AS IsApplicable, 
--ISNULL(EmployeeTimeCard_CommissionPercentOnTotalAmount*NULLIF(SaleEmployeeTimeCard_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
--ISNULL(EmployeeTimeCard_CommissionPercentOnContributionAmount*NULLIF(SaleEmployeeTimeCard_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
--ISNULL(EmployeeTimeCard_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(SaleEmployeeTimeCard_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
--ISNULL(EmployeeTimeCard_CommissionAmount*NULLIF(SaleEmployeeTimeCard_IsCommissionApplicable,0),0) AS CommissionByAmount
--FROM SalesManInformation WHERE Active=1

--ServiceClaim
SELECT SalesManID, 
6 AS InvoiceType, 
ISNULL(ServiceClaim_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(ServiceClaim_CommissionPercentOnTotalAmount*NULLIF(ServiceClaim_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(ServiceClaim_CommissionPercentOnContributionAmount*NULLIF(ServiceClaim_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(ServiceClaim_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(ServiceClaim_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(ServiceClaim_CommissionAmount*NULLIF(ServiceClaim_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(ServiceClaim_CommissionType,0) AS CommissionType,
ServiceClaim_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

UNION ALL

--DineIn
SELECT SalesManID, 
7 AS InvoiceType, 
ISNULL(DineIn_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(DineIn_CommissionPercentOnTotalAmount*NULLIF(DineIn_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(DineIn_CommissionPercentOnContributionAmount*NULLIF(DineIn_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(DineIn_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(DineIn_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(DineIn_CommissionAmount*NULLIF(DineIn_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(DineIn_CommissionType,0) AS CommissionType,
DineIn_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

UNION ALL

--TakeAway
SELECT SalesManID, 
8 AS InvoiceType, 
ISNULL(TakeAway_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(TakeAway_CommissionPercentOnTotalAmount*NULLIF(TakeAway_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(TakeAway_CommissionPercentOnContributionAmount*NULLIF(TakeAway_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(TakeAway_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(TakeAway_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(TakeAway_CommissionAmount*NULLIF(TakeAway_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(TakeAway_CommissionType,0) AS CommissionType,
TakeAway_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

UNION ALL

--HomeDelivery
SELECT SalesManID, 
9 AS InvoiceType, 
ISNULL(HomeDelivery_IsCommissionApplicable,0) AS IsApplicable, 
ISNULL(HomeDelivery_CommissionPercentOnTotalAmount*NULLIF(HomeDelivery_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmount,
ISNULL(HomeDelivery_CommissionPercentOnContributionAmount*NULLIF(HomeDelivery_IsCommissionApplicable,0),0) AS CommissionPercentOnContributionAmount,
ISNULL(HomeDelivery_CommissionPercentOnTotalAmountExcludingContributionAmount*NULLIF(HomeDelivery_IsCommissionApplicable,0),0) AS CommissionPercentOnTotalAmountExcludingContributionAmount,
ISNULL(HomeDelivery_CommissionAmount*NULLIF(HomeDelivery_IsCommissionApplicable,0),0) AS CommissionByAmount
--4.9 : Added
,ISNULL(HomeDelivery_CommissionType,0) AS CommissionType,
HomeDelivery_LocationGroupID AS LocationGroupID

FROM SalesManInformation WHERE Active=1

--GO
--
--IF EXISTS (SELECT 1 FROM sysobjects WHERE name='vw_SalesManPerformance' AND xtype='V')
--BEGIN
-- DROP VIEW vw_SalesManPerformance
--END
--
--GO
--
--CREATE VIEW vw_SalesManPerformance
--AS
--
--SELECT sim.InvoiceDate AS DayDate,sim.GodownID AS LocationID,sim.SalesManID AS EmployeeID,
--(
-- --Condition#1 :: Bring all applicable commissions types only
-- --Condition#2 :: During timing check
-- SELECT 
-- SUM(sub.NetAmount)
-- FROM vw_SalesInvoiceMasterWithEmployeeTITO sub
-- WHERE sub.InvoiceType IN (SELECT InvoiceType FROM vw_SalesManCommissionTypes sub_smc WHERE sub_smc.IsApplicable=1 AND sub.SalesManID=sub_smc.SalesManID AND sub.InvoiceType=sub_smc.InvoiceType)
-- AND sub.InvoiceDate=sim.InvoiceDate AND sub.GodownID=sim.GodownID
--)AS TotalAmountOnDate,
--
--SUM(NetAmount) AS Contribution FROM vw_SalesInvoiceMasterWithEmployeeTITO sim
--INNER JOIN vw_SalesManCommissionTypes smc ON (sim.SalesManID=smc.SalesManID AND sim.InvoiceType=smc.InvoiceType AND smc.IsApplicable=1)
--
--GROUP BY sim.InvoiceDate,sim.GodownID,sim.SalesManID
-- 



GO
/****** Object:  View [dbo].[vw_salesregdetail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_salesregdetail]
as
SELECT ItemIDUnder ,InvoiceID FROM SalesInvoiceDetail id   GROUP BY ItemIDUnder,InvoiceID

GO
/****** Object:  View [dbo].[vw_Supplier_Payable_Detail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_Supplier_Payable_Detail]
AS

/********************************Payable*******************************************/
/*This is where payable is generated, (Supplier credit)*/

/* 1) Through Opening Balance */

SELECT 0 AS SortOrder, aob.LedgerID AS VendorID, -1 AS VoucherID, -1 AS InvoiceID, ISNULL(aob.OpeningBalanceCredit,0) AS 'Due'
FROM AccountsOpeningBalance aob
INNER JOIN Accounts a ON a.COAID=aob.LedgerID

WHERE 
1=1
AND a.Active=1
AND a.NatureID=15 --Accounts Payable
AND aob.OpeningBalanceCredit<>0

UNION ALL

/* 2) Through Auto generated voucher */
SELECT 1 AS SortOrder, vm.LedgerID AS VendorID, vm.VoucherID, vm.ReferenceInvoiceID AS InvoiceID, vm.AmountCredit AS 'Due'
FROM VoucherMaster vm
INNER JOIN Accounts a ON a.COAID=vm.LedgerID

WHERE vm.AmountCredit <> 0 
AND vm.Active = 1
AND vm.Posted = 1
AND a.NatureID=15 --Accounts Payable

UNION ALL

/* 2) Through JV & Other vouchers*/
SELECT 1 AS SortOrder, vd.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, vd.Credit AS 'Due'
FROM VoucherDetails vd
INNER JOIN Accounts a ON vd.LedgerID = a.COAID
INNER JOIN VoucherMaster vm ON vm.VoucherID=vd.VoucherID
WHERE vd.Credit <> 0 
AND vm.Active = 1
AND vm.Posted = 1
AND a.NatureID IN (15)

/********************************Payable End***************************************/


GO
/****** Object:  View [dbo].[vw_Supplier_Payment_Detail]    Script Date: 29/01/2026 8:21:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Supplier_Payment_Detail]
AS

/********************************Payment*******************************************/
/*This is where payment is made, (Supplier debit)*/

/* 1) Through Opening Balance */

SELECT 0 AS SortOrder, aob.LedgerID AS VendorID, -1 AS VoucherID, -1 AS InvoiceID, 0 AS PaymentAccountID, ISNULL(aob.OpeningBalanceDebit,0) AS 'Paid'
FROM AccountsOpeningBalance aob
INNER JOIN Accounts a ON a.COAID=aob.LedgerID

WHERE 
1=1
AND a.Active=1
AND a.NatureID=15 --Accounts Payable
AND aob.OpeningBalanceCredit<>0

UNION ALL

/* 2) Through debit voucher cash / debit voucher bank */

SELECT 1 AS SortOrder, vm.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, vd.LedgerID AS PaymentAccountID, vd.Credit AS 'Paid'
FROM VoucherDetails vd
INNER JOIN VoucherMaster vm ON vm.VoucherID=vd.VoucherID
WHERE vd.Credit <> 0 
AND vm.Active = 1
AND vm.Posted = 1
AND vd.VoucherID IN
(
   SELECT vm.VoucherID
   FROM VoucherMaster vm
   INNER JOIN Accounts a ON vm.LedgerID = a.COAID
   WHERE vm.AmountDebit <> 0 
   AND vm.Active = 1
   AND vm.Posted = 1
   AND a.NatureID IN (15)
)

UNION ALL

/* 3) Through JV & Other vouchers*/
SELECT 1 AS SortOrder, vd.LedgerID AS VendorID, vd.VoucherID, vd.ReferenceInvoiceID AS InvoiceID, 0 AS PaymentAccountID, vd.Debit AS 'Paid'
FROM VoucherDetails vd
INNER JOIN Accounts a ON vd.LedgerID = a.COAID
INNER JOIN VoucherMaster vm ON vm.VoucherID=vd.VoucherID
WHERE vd.Debit <> 0 
AND vm.Active = 1
AND vm.Posted = 1
AND a.NatureID IN (15)
/********************************Payment End***************************************/


GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_account_list'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_account_list'
GO