SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_VATDETAILS] @FromDate datetime,@ToDate datetime ,@SumFlag nvarchar(5),@BranchCode nvarchar(1000) AS begin if @SumFlag='D' begin SELECT INVOICE.INVOICEDATE AS DATE ,INVOICE.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,INVOICE.INVNO AS DOCNO, INVOICE.CUSTOMERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,INVOICE.VATNO,'SALES' AS SR,SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMT,'' as PurInvno ,ISNULL((SELECT ISNULL(NAMEAR,'') AS ARNAME FROM CUSTOMER WHERE CUSTOMER.CODE=INVOICE.CUSTOMERCODE),'') AS ARNAME,'Local Sales' as LF ,'' as purinvno FROM INVOICEITEM INNER JOIN INVOICE ON INVOICE.INVNO=INVOICEITEM.INVNO and INVOICE.SR=INVOICEITEM.SR WHERE INVOICE.SR='SALES' AND INVOICE.INVOICEDATE between @FromDate and @ToDate and isnull(INVOICE.deleted,'N')='N'and isnull(INVOICEITEM.deleted,'N')='N' and (INVOICE.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY INVOICE.INVNO,VATPER,INVOICE.INVOICEDATE,INVOICE.ENTRYDATE,INVOICE.CUSTOMERNAME,INVOICE.VATNO,INVOICE.CUSTOMERCODE UNION ALL SELECT InvoiceReturn.InvoiceRetDate AS DATE,InvoiceReturn.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,InvoiceReturn.INVOICERETNO AS DOCNO, InvoiceReturn.CUSTOMERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,InvoiceReturn.VATNO,'SALES RETURNS' AS SR,SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMT,'' as PurInvno ,ISNULL((SELECT ISNULL(NAMEAR,'') AS ARNAME FROM CUSTOMER WHERE CUSTOMER.CODE=InvoiceReturn.CUSTOMERCODE),'') AS ARNAME,'Local Sales Returns' as LF ,'' as purinvno FROM InvoiceReturnItem INNER JOIN InvoiceReturn ON InvoiceReturn.INVOICERETNO=InvoiceReturnItem.INVOICERETNO and InvoiceReturn.SR=InvoiceReturnItem.SR WHERE InvoiceReturn.SR='RETURNS' AND InvoiceReturn.EntryDate between @FromDate and @ToDate and isnull(InvoiceReturn.deleted,'N')='N'and isnull(InvoiceReturnItem.deleted,'N')='N' and (InvoiceReturn.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY InvoiceReturn.INVOICERETNO,VATPER,InvoiceReturn.InvoiceRetDate,InvoiceReturn.ENTRYDATE,InvoiceReturn.CUSTOMERNAME,InvoiceReturn.VATNO,InvoiceReturn.CUSTOMERCODE,InvoiceReturn.INVNO UNION ALL SELECT PURCHASE.ENTRYDATE AS DATE, PURCHASE.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,PURCHASE.DocumentNo AS DOCNO, PURCHASE.SUPPLIERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,PURCHASE.VATNO,'PURCHASE' AS SR,SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE)) AS TAXABLEAMT,PURCHASE.INVOICENO as PurInvno ,ISNULL((SELECT ISNULL(NAMEAR,'') AS ARNAME FROM SUPPLIER WHERE SUPPLIER.CODE=PURCHASE.SUPPLIERCODE),'') AS ARNAME,PURCHASE.purType + ' Purchase' as LF ,PURCHASE.InvoiceNo as purinvno FROM PURCHASEITEM INNER JOIN PURCHASE ON PURCHASE.DOCUMENTNO=PURCHASEITEM.DOCUMENTNO and PURCHASE.PR=PURCHASEITEM.PR WHERE PURCHASE.PR='Purchase' AND PURCHASE.ENTRYDATE between @FromDate and @ToDate and isnull(purchase.deleted,'N')='N'and isnull(purchaseitem.deleted,'N')='N' and (PURCHASE.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY PURCHASE.DOCUMENTNO,VATPER,PURCHASE.ENTRYDATE,PURCHASE.ENTRYDATE,PURCHASE.SUPPLIERNAME,pURCHASE.VATNO,PURCHASE.PR,PURCHASE.INVOICENO,PURCHASE.SUPPLIERCODE,PURCHASE.purType UNION ALL SELECT PurchaseReturn.ENTRYDATE AS DATE, PurchaseReturn.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,PurchaseReturn.DocumentNo AS DOCNO, PurchaseReturn.SUPPLIERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,PurchaseReturn.VATNO,'PURCHASE RETURNS' AS SR,SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE))AS TAXABLEAMT,'' as PurInvno ,ISNULL((SELECT ISNULL(NAMEAR,'') AS ARNAME FROM SUPPLIER WHERE SUPPLIER.CODE=PurchaseReturn.SUPPLIERCODE),'') AS ARNAME,'Local Purchase Returns' as LF ,'' as purinvno FROM PurchaseReturnItem INNER JOIN PurchaseReturn ON PurchaseReturn.DocumentNo=PurchaseReturnItem.DocumentNo and PurchaseReturn.PR=PurchaseReturnItem.PR WHERE PurchaseReturn.PR='RETURNS' AND PurchaseReturn.ENTRYDATE between @FromDate and @ToDate and isnull(PurchaseReturn.deleted,'N')='N'and isnull(PurchaseReturnItem.deleted,'N')='N' and (PurchaseReturn.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY PurchaseReturn.DocumentNo,VATPER,PurchaseReturn.ENTRYDATE,PurchaseReturn.ENTRYDATE,PurchaseReturn.SUPPLIERNAME,PurchaseReturn.VATNO,PurchaseReturn.PR,PurchaseReturn.SUPPLIERCODE UNION ALL SELECT CashEntryDetails.ENTRYDATE AS DATE, CashEntryDetails.ENTRYDATE AS INVDATE, ISNULL(CashEntryDetails.VATPER,0) as VATPER ,ISNULL(CashEntryDetails.vatamt,0) AS VATAMT,CashEntryDetails.InvoiceNo AS DOCNO, ISNULL(CashEntryDetails.NAME,'') as CNAME,CashEntryDetails.VATNO as VATNO, 'Expense' AS SR,CashEntryDetails.Amount AS TAXABLEAMT ,'' AS purinvno ,'' AS ARNAME, 'Expenses' as LF ,'' as purinvno FROM CashEntryDetails WHERE vatper<>0 AND convert(varchar(25),CashEntryDetails.entrydate,112) between @FromDate and @ToDate and (CashEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') and isnull(CashEntryDetails.deleted,'N')='N' UNION ALL SELECT purchaseexpensedetails.expdate AS DATE, purchaseexpensedetails.expdate AS INVDATE, ISNULL(purchaseexpensedetails.VATPER,0) as VATPER ,ISNULL(purchaseexpensedetails.vatamt,0) AS VATAMT,purchaseexpensedetails.docno AS DOCNO, purchaseexpensedetails.SUPPNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,ISNULL((SELECT ISNULL(vatno,'') AS vatno FROM SUPPLIER WHERE SUPPLIER.CODE=purchaseexpensedetails.SUPPCODE),'') as VATNO, 'PURCHASE' AS SR,purchaseexpensedetails.rate AS TAXABLEAMT ,ISNULL((SELECT ISNULL(PURCHASE.InvoiceNo,'') AS purinvno FROM purchase WHERE purchase.documentno=purchaseexpensedetails.docno and purchase.pr='Purchase'),'') as purinvno ,ISNULL((SELECT ISNULL(NAMEAR,'') AS ARNAME FROM SUPPLIER WHERE SUPPLIER.CODE=purchaseexpensedetails.SUPPCODE),'') AS ARNAME, 'Foreign Purchase Expenses' as LF ,'' as purinvno FROM purchaseexpensedetails WHERE purchaseexpensedetails.Purtype='Purchase' and vatper<>0 AND convert(varchar(25),purchaseexpensedetails.expdate,112) between @FromDate and @ToDate --and (purchaseexpensedetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') UNION ALL SELECT JournalEntryDetails.ENTRYDATE AS DATE, JournalEntryDetails.ENTRYDATE AS INVDATE, ISNULL(JournalEntryDetails.DebitVatper,0) as VATPER ,ISNULL(JournalEntryDetails.DebitVat,0) AS VATAMT,JournalEntryDetails.JournalNo AS DOCNO, ISNULL(JournalEntryDetails.NAME,'') as CNAME,JournalEntryDetails.VATNO as VATNO, 'Asset Purchase' AS SR,JournalEntryDetails.Debit AS TAXABLEAMT ,'' AS purinvno ,'' AS ARNAME, 'Asset Purchase' as LF ,'' as purinvno FROM JournalEntryDetails WHERE DebitVatper<>0 AND convert(varchar(25),JournalEntryDetails.entrydate,112) between @FromDate and @ToDate and (JournalEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') UNION ALL SELECT JournalEntryDetails.ENTRYDATE AS DATE, JournalEntryDetails.ENTRYDATE AS INVDATE, ISNULL(JournalEntryDetails.CreditVatper,0) as VATPER ,ISNULL(JournalEntryDetails.CreditVat,0) AS VATAMT,JournalEntryDetails.JournalNo AS DOCNO, ISNULL(JournalEntryDetails.NAME,'') as CNAME,JournalEntryDetails.VATNO as VATNO, 'RETURNS' AS SR,JournalEntryDetails.Credit AS TAXABLEAMT ,'' AS purinvno ,'' AS ARNAME, 'RETURNS' as LF ,'' as purinvno FROM JournalEntryDetails WHERE CreditVatper<>0 AND convert(varchar(25),JournalEntryDetails.entrydate,112) between @FromDate and @ToDate and (JournalEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') end else if @Sumflag='S' begin SELECT INVOICE.INVOICEDATE AS DATE ,INVOICE.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,INVOICE.INVNO AS DOCNO, INVOICE.CUSTOMERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,INVOICE.VATNO,'SALES' AS SR,SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMT ,SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMTn,SUM(VATAMT)AS VATAMTn,'Local Sales' as LF ,'' as purinvno FROM INVOICEITEM INNER JOIN INVOICE ON INVOICE.INVNO=INVOICEITEM.INVNO and INVOICE.SR=INVOICEITEM.SR WHERE INVOICE.SR='SALES' AND INVOICE.INVOICEDATE between @FromDate and @ToDate and isnull(INVOICE.deleted,'N')='N'and isnull(INVOICEITEM.deleted,'N')='N' and (INVOICE.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY INVOICE.INVNO,VATPER,INVOICE.INVOICEDATE,INVOICE.ENTRYDATE,INVOICE.CUSTOMERNAME,INVOICE.VATNO UNION ALL SELECT InvoiceReturn.InvoiceRetDate AS DATE,InvoiceReturn.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,InvoiceReturn.INVOICERETNO AS DOCNO, InvoiceReturn.CUSTOMERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,InvoiceReturn.VATNO,'SALES RETURNS' AS SR,SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMT ,-SUM(QTY*(ISNULL(RATE,0)-ISNULL(IDISCRATE,0)))AS TAXABLEAMTn,-SUM(VATAMT)AS VATAMTn,'Local Sales Returns' as LF ,'' as purinvno FROM InvoiceReturnItem INNER JOIN InvoiceReturn ON InvoiceReturn.INVOICERETNO=InvoiceReturnItem.INVOICERETNO and InvoiceReturn.SR=InvoiceReturnItem.SR WHERE InvoiceReturn.SR='RETURNS' AND InvoiceReturn.EntryDate between @FromDate and @ToDate and isnull(InvoiceReturn.deleted,'N')='N'and isnull(InvoiceReturnItem.deleted,'N')='N' and (InvoiceReturn.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY InvoiceReturn.INVOICERETNO,VATPER,InvoiceReturn.InvoiceRetDate,InvoiceReturn.ENTRYDATE,InvoiceReturn.CUSTOMERNAME,InvoiceReturn.VATNO UNION ALL SELECT PURCHASE.EntryDate AS DATE, PURCHASE.ENTRYDATE AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,PURCHASE.DOCUMENTNO AS DOCNO, PURCHASE.SUPPLIERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,PURCHASE.VATNO,'PURCHASE' AS SR,SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE))AS TAXABLEAMT ,-SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE))AS TAXABLEAMTn,-SUM(VATAMT)AS VATAMTn,PURCHASE.purType + ' Purchase' as LF ,PURCHASE.InvoiceNo as purinvno FROM PURCHASEITEM INNER JOIN PURCHASE ON PURCHASE.DOCUMENTNO=PURCHASEITEM.DOCUMENTNO and PURCHASE.PR=PURCHASEITEM.PR WHERE PURCHASE.PR='Purchase' AND PURCHASE.EntryDate between @FromDate and @ToDate and isnull(purchase.deleted,'N')='N'and isnull(purchaseitem.deleted,'N')='N' and (PURCHASE.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY PURCHASE.DOCUMENTNO,VATPER,PURCHASE.EntryDate,PURCHASE.ENTRYDATE,PURCHASE.SUPPLIERNAME,pURCHASE.VATNO,PURCHASE.PR,PURCHASE.purType,PURCHASE.InvoiceNo UNION ALL SELECT PurchaseReturn.ENTRYDATE AS DATE, PurchaseReturn.EntryDate AS INVDATE,VATPER,SUM(VATAMT)AS VATAMT,PurchaseReturn.DocumentNo AS DOCNO, PurchaseReturn.SUPPLIERNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,PurchaseReturn.VATNO,'PURCHASE RETURNS' AS SR,SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE))AS TAXABLEAMT ,SUM(isnull(QTY,0)*isnull(rateafterdisc,RATE))AS TAXABLEAMT,SUM(VATAMT)AS VATAMTn,'Local Purchase Returns' as LF,'' as purinvno FROM PurchaseReturnItem INNER JOIN PurchaseReturn ON PurchaseReturn.DocumentNo=PurchaseReturnItem.DocumentNo and PurchaseReturn.PR=PurchaseReturnItem.PR WHERE PurchaseReturn.PR='RETURNS' AND PurchaseReturn.ENTRYDATE between @FromDate and @ToDate and isnull(PurchaseReturn.deleted,'N')='N'and isnull(PurchaseReturnItem.deleted,'N')='N' and (PurchaseReturn.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY PurchaseReturn.DocumentNo,VATPER,PurchaseReturn.ENTRYDATE,PurchaseReturn.EntryDate,PurchaseReturn.SUPPLIERNAME,PurchaseReturn.VATNO,PurchaseReturn.PR UNION ALL SELECT CashEntryDetails.ENTRYDATE AS DATE, CashEntryDetails.ENTRYDATE AS INVDATE, ISNULL(CashEntryDetails.VATPER,0) as VATPER ,SUM(ISNULL(CashEntryDetails.vatamt,0)) AS VATAMT,CashEntryDetails.docno AS DOCNO, '' as CNAME,CashEntryDetails.VATNO as VATNO, 'Expense' AS SR,SUM(CashEntryDetails.Amount) AS TAXABLEAMT ,-1*SUM(isnull(CashEntryDetails.Amount,0)) AS TAXABLEAMTn,-1*SUM(ISNULL(CashEntryDetails.vatamt,0)) AS VATAMTn , 'Expenses' as LF ,'' as purinvno FROM CashEntryDetails WHERE vatper<>0 AND convert(varchar(25),CashEntryDetails.entrydate,112) between @FromDate and @ToDate and isnull(CashEntryDetails.deleted,'N')='N' and (CashEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY CashEntryDetails.docno,VATPER,CashEntryDetails.ENTRYDATE ,CashEntryDetails.VATNO UNION ALL SELECT purchaseexpensedetails.expdate AS DATE, purchaseexpensedetails.expdate AS INVDATE,ISNULL(purchaseexpensedetails.VATPER,0) as VATPER ,ISNULL(purchaseexpensedetails.vatamt,0) AS VATAMT,purchaseexpensedetails.docno AS DOCNO, purchaseexpensedetails.SUPPNAME COLLATE SQL_Latin1_General_CP1_CS_AS as CNAME,ISNULL((SELECT ISNULL(vatno,'') AS vatno FROM SUPPLIER WHERE SUPPLIER.CODE=purchaseexpensedetails.SUPPCODE),'') as VATNO, 'PURCHASE' AS SR,purchaseexpensedetails.rate AS TAXABLEAMT ,-1*purchaseexpensedetails.rate AS TAXABLEAMTn,-1*ISNULL(purchaseexpensedetails.vatamt,0) AS VATAMTn, 'Foreign Purchase Expenses' as LF ,'' as purinvno FROM purchaseexpensedetails WHERE purchaseexpensedetails.Purtype='Purchase' and vatper<>0 AND convert(varchar(25),purchaseexpensedetails.expdate,112) between @FromDate and @ToDate --and (purchaseexpensedetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') UNION ALL SELECT JournalEntryDetails.ENTRYDATE AS DATE, JournalEntryDetails.ENTRYDATE AS INVDATE, ISNULL(JournalEntryDetails.DebitVatper,0) as VATPER ,SUM(ISNULL(JournalEntryDetails.DebitVat,0)) AS VATAMT,JournalEntryDetails.JournalNo AS DOCNO, '' as CNAME,JournalEntryDetails.VATNO as VATNO, 'Asset Purchase' AS SR,SUM(JournalEntryDetails.Debit) AS TAXABLEAMT ,-1*SUM(isnull(JournalEntryDetails.Debit,0)) AS TAXABLEAMTn,-1*SUM(ISNULL(JournalEntryDetails.DebitVat,0)) AS VATAMTn , 'Asset Purchase' as LF ,'' as purinvno FROM JournalEntryDetails WHERE DebitVatper<>0 AND convert(varchar(25),JournalEntryDetails.entrydate,112) between @FromDate and @ToDate and (JournalEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY JournalEntryDetails.JournalNo,DebitVatper,JournalEntryDetails.ENTRYDATE ,JournalEntryDetails.VATNO UNION ALL SELECT JournalEntryDetails.ENTRYDATE AS DATE, JournalEntryDetails.ENTRYDATE AS INVDATE, ISNULL(JournalEntryDetails.CreditVatper,0) as VATPER ,SUM(ISNULL(JournalEntryDetails.CreditVat,0)) AS VATAMT,JournalEntryDetails.JournalNo AS DOCNO, '' as CNAME,JournalEntryDetails.VATNO as VATNO, 'RETURNS' AS SR,SUM(JournalEntryDetails.Credit) AS TAXABLEAMT ,SUM(isnull(JournalEntryDetails.Credit,0)) AS TAXABLEAMTn,SUM(ISNULL(JournalEntryDetails.CreditVat,0)) AS VATAMTn , 'RETURNS' as LF ,'' as purinvno FROM JournalEntryDetails WHERE CreditVatper<>0 AND convert(varchar(25),JournalEntryDetails.entrydate,112) between @FromDate and @ToDate and (JournalEntryDetails.BranchCode = @BranchCode OR @BranchCode IS NULL OR @BranchCode = '' or @BranchCode='1') GROUP BY JournalEntryDetails.JournalNo,CreditVatper,JournalEntryDetails.ENTRYDATE ,JournalEntryDetails.VATNO end END GO ALTER TABLE QUOTATIONITEM ALTER COLUMN QTY FLOAT