/****** Object: View [dbo].[BI_VATP] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATP]
AS
SELECT PMDocs.doctype,
PMDocs.docnumbr,
PMDocs.vchnumwk,
Cast(Year(PMDocs.tax_date) AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(Month(PMDocs.tax_date) AS VARCHAR), 2) AS Period,
PMDocs.tax_date,
PMDocs.posted,
PMTax.taxdtlid,
PMTax.taxamnt,
PMTax.txdttpur,
PMDocs.vendorid,
PMDocs.docstatus,
'PM' AS Source,
PMDocs.bchsourc,
CASE
WHEN pmdocs.doctype >= 4 THEN -1
ELSE 1
END AS Factor,
dbo.pm00200.vndclsid,
dbo.pm00200.vendname
FROM (SELECT vchnumwk,
docnumbr,
doctype,
prchamnt,
taxamnt,
tax_date,
'Unposted' AS Posted,
vendorid,
'Work' AS DocStatus,
bchsourc
FROM dbo.pm10000
UNION
SELECT vchrnmbr,
docnumbr,
doctype,
prchamnt,
taxamnt,
tax_date,
'Posted' AS Posted,
vendorid,
'Open' AS DocStatus,
bchsourc
FROM dbo.pm20000
WHERE ( voided = 0 )
UNION
SELECT vchrnmbr,
docnumbr,
doctype,
prchamnt,
taxamnt,
tax_date,
'Posted' AS Posted,
vendorid,
'History' AS DocStatus,
bchsourc
FROM dbo.pm30200
WHERE ( voided = 0 )) AS PMDocs
INNER JOIN dbo.pm00200
ON PMDocs.vendorid = dbo.pm00200.vendorid
LEFT OUTER JOIN (SELECT vchrnmbr,
doctype,
taxdtlid,
taxamnt,
ortaxamt,
txdttpur,
ortotpur
FROM dbo.pm10500
UNION
SELECT vchrnmbr,
doctype,
taxdtlid,
taxamnt,
ortaxamt,
txdttpur,
ortotpur
FROM dbo.pm30700) AS PMTax
ON PMDocs.vchnumwk = PMTax.vchrnmbr
AND PMDocs.doctype = PMTax.doctype
WHERE ( NOT ( PMDocs.bchsourc IN ( 'Rcvg Trx Entry', 'Rcvg Trx Ivc',
'Ret Trx Entry' )
) )
go
/****** Object: View [dbo].[BI_VATP2] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATP2]
AS
SELECT RctDoc.poprctnm,
RctTax.taxdtlid,
RctTax.taxamnt,
RctTax.ortaxamt,
RctTax.totpurch,
RctTax.ortotpur,
RctDoc.tax_date,
Cast(Year(RctDoc.tax_date) AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(Month(RctDoc.tax_date) AS VARCHAR), 2) AS Period,
RctDoc.poptype,
RctDoc.posted,
RctDoc.vendorid,
RctDoc.docstatus,
'RCV' AS Source,
CASE
WHEN poptype = 5 THEN -1
ELSE 1
END AS Factor,
dbo.pm00200.vndclsid,
dbo.pm00200.vendname
FROM dbo.pm00200
INNER JOIN (SELECT poprctnm,
vnddocnm,
poptype,
subtotal,
taxamnt,
tax_date,
'Unposted' AS Posted,
vendorid,
'Work' AS DocStatus
FROM dbo.pop10300
UNION
SELECT poprctnm,
vnddocnm,
poptype,
subtotal,
taxamnt,
tax_date,
'Posted' AS Posted,
vendorid,
'History' AS DocStatus
FROM dbo.pop30300
WHERE ( voidstts = 0 )) AS RctDoc
ON dbo.pm00200.vendorid = RctDoc.vendorid
LEFT OUTER JOIN (SELECT poprctnm,
taxdtlid,
taxamnt,
ortaxamt,
totpurch,
ortotpur
FROM dbo.pop10360
WHERE ( rcptlnnm = 0 )
UNION
SELECT poprctnm,
taxdtlid,
taxamnt,
ortaxamt,
totpurch,
ortotpur
FROM dbo.pop30360
WHERE ( rcptlnnm = 0 )) AS RctTax
ON RctDoc.poprctnm = RctTax.poprctnm
go
/****** Object: View [dbo].[BI_VATS] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATS]
AS
SELECT dbo.sop10105.taxdtlid,
SOPDocs.soptype,
SOPDocs.sopnumbe,
Isnull(dbo.sop10105.taxdtsls, SOPDocs.xtndprce) AS TAXDTSLS
,
SOPDocs.tax_date,
Cast(Year(SOPDocs.tax_date) AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(Month(SOPDocs.tax_date) AS VARCHAR), 2) AS Period,
Isnull(SOPDocs.lnitmseq, 0) AS LNITMSEQ
,
SOPDocs.posted,
SOPDocs.docstatus,
SOPDocs.custnmbr,
'SOP' AS Source,
Isnull(dbo.sop10105.staxamnt, 0) AS TaxAmt,
CASE
WHEN sopdocs.soptype = 4 THEN -1
ELSE 1
END AS Factor,
dbo.rm00101.custclas,
dbo.rm00101.custname,
SOPDocs.taxamnt,
SOPDocs.xtndprce,
DocTax.taxdoctaxamt,
DocTax.taxdocamt
FROM (SELECT sopnumbe AS TaxDocNum,
soptype AS TaxDocType,
docdate,
tax_date,
glpostdt,
docamnt,
ectrx,
voidstts,
custnmbr,
'Unposted' AS Posted,
'Work' AS DocStatus,
taxamnt AS TaxDocTaxamt,
subtotal AS TaxDocAmt
FROM dbo.sop10100
WHERE ( voidstts = 0 )
AND ( soptype IN ( 3, 4 ) )
UNION ALL
SELECT sopnumbe,
soptype,
docdate,
tax_date,
glpostdt,
docamnt,
ectrx,
voidstts,
custnmbr,
'Posted' AS Posted,
'History' AS DocStatus,
taxamnt,
subtotal
FROM dbo.sop30200
WHERE ( voidstts = 0 )
AND ( soptype IN ( 3, 4 ) )) AS DocTax
RIGHT OUTER JOIN dbo.rm00101
INNER JOIN (SELECT SOP10100_1.sopnumbe,
SOP10100_1.soptype,
SOP10100_1.docdate,
SOP10100_1.tax_date,
SOP10100_1.glpostdt,
SOP10100_1.docamnt,
SOP10100_1.ectrx,
SOP10100_1.voidstts,
SOP10100_1.custnmbr,
'Unposted' AS Posted,
'Work' AS DocStatus,
dbo.sop10200.lnitmseq,
dbo.sop10200.taxamnt,
dbo.sop10200.xtndprce
FROM dbo.sop10100 AS SOP10100_1
INNER JOIN dbo.sop10200
ON SOP10100_1.soptype =
dbo.sop10200.soptype
AND SOP10100_1.sopnumbe
=
dbo.sop10200.sopnumbe
WHERE ( SOP10100_1.voidstts = 0 )
AND ( SOP10100_1.soptype IN ( 3, 4 )
)
UNION ALL
SELECT SOP30200_1.sopnumbe,
SOP30200_1.soptype,
SOP30200_1.docdate,
SOP30200_1.tax_date,
SOP30200_1.glpostdt,
SOP30200_1.docamnt,
SOP30200_1.ectrx,
SOP30200_1.voidstts,
SOP30200_1.custnmbr,
'Posted' AS Posted,
'History' AS DocStatus,
dbo.sop30300.lnitmseq,
dbo.sop30300.taxamnt,
dbo.sop30300.xtndprce
FROM dbo.sop30200 AS SOP30200_1
INNER JOIN dbo.sop30300
ON SOP30200_1.soptype =
dbo.sop30300.soptype
AND SOP30200_1.sopnumbe =
dbo.sop30300.sopnumbe
WHERE ( SOP30200_1.voidstts = 0 )
AND ( SOP30200_1.soptype IN ( 3, 4 )
)) AS
SOPDocs
ON dbo.rm00101.custnmbr = SOPDocs.custnmbr
ON DocTax.taxdocnum = SOPDocs.sopnumbe
AND DocTax.taxdoctype = SOPDocs.soptype
LEFT OUTER JOIN dbo.sop10105
ON SOPDocs.lnitmseq = dbo.sop10105.lnitmseq
AND SOPDocs.sopnumbe = dbo.sop10105.sopnumbe
AND SOPDocs.soptype = dbo.sop10105.soptype
go
/****** Object: View [dbo].[BI_VATS2] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATS2]
AS
SELECT RMDocs.rmdtypal,
RMDocs.docnumbr,
Cast(Year(RMDocs.tax_date) AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(Month(RMDocs.tax_date) AS VARCHAR), 2) AS Period,
RMDocs.tax_date,
RMDocs.posted,
RMTax.taxdtlid,
Isnull(RMTax.taxamnt, 0) AS TAXAMNT,
Isnull(RMTax.taxdtsls, RMDocs.slsamnt) AS TAXDTSLS,
RMDocs.docstatus,
RMDocs.custnmbr,
'RM' AS Source,
RMDocs.taxschid,
CASE
WHEN rmdocs.rmdtypal >= 7 THEN -1
ELSE 1
END AS Factor,
RMDocs.bchsourc,
dbo.rm00101.custclas,
dbo.rm00101.custname
FROM (SELECT taxschid,
rmdtypal,
docnumbr,
slsamnt,
taxamnt,
tax_date,
voidstts,
custnmbr,
'Posted' AS Posted,
'Open' AS DocStatus,
bchsourc
FROM dbo.rm20101
WHERE ( voidstts = 0 )
UNION
SELECT taxschid,
rmdtypal,
docnumbr,
slsamnt,
taxamnt,
tax_date,
voidstts,
custnmbr,
'Posted' AS Posted,
'Open' AS DocStatus,
bchsourc
FROM dbo.rm30101
WHERE ( voidstts = 0 )) AS RMDocs
INNER JOIN dbo.rm00101
ON RMDocs.custnmbr = dbo.rm00101.custnmbr
LEFT OUTER JOIN (SELECT docnumbr,
rmdtypal,
taxdtlid,
taxamnt,
taxdtsls
FROM dbo.rm10601
UNION
SELECT docnumbr,
rmdtypal,
taxdtlid,
taxamnt,
taxdtsls
FROM dbo.rm30601) AS RMTax
ON RMDocs.docnumbr = RMTax.docnumbr
AND RMDocs.rmdtypal = RMTax.rmdtypal
WHERE ( NOT ( RMDocs.rmdtypal IN ( 9 ) ) )
AND ( NOT ( RMDocs.bchsourc IN ( 'Sales Entry' ) ) )
go
/****** Object: View [dbo].[BI_VATALL] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATALL]
AS
SELECT taxdtlid,
Sum(taxdtsls * factor) AS Amount,
period,
sopnumbe AS DocNum,
tax_date,
posted,
custnmbr,
docstatus,
soptype AS DocType,
voucher,
source,
Sum(taxamt * factor) AS Tax,
factor,
custclas AS Class,
CASE
WHEN custclas IN ( 'STAFF', 'STAFF LOAN', 'FOREIGN' ) THEN 'Exclude'
ELSE 'Include'
END AS InVATReport,
doctypedesc,
custname AS NAME,
dochdrtax * factor AS DocHdrTax,
dochdramt * factor AS DocHdrAmt
FROM (SELECT taxdtlid,
soptype,
CASE
WHEN soptype = 3 THEN 'Invoice'
WHEN soptype = 4 THEN 'Return'
END AS DocTypeDesc,
sopnumbe,
taxdtsls,
taxamt,
tax_date,
period,
lnitmseq,
posted,
custnmbr,
custname,
docstatus,
sopnumbe AS Voucher,
source,
factor,
custclas,
taxdoctaxamt AS DocHdrTax,
taxdocamt AS DocHdrAmt
FROM dbo.bi_vats
UNION
SELECT taxdtlid,
rmdtypal,
CASE
WHEN rmdtypal = 1 THEN 'Invoice'
WHEN rmdtypal = 3 THEN 'Debit Memo'
WHEN rmdtypal = 7 THEN 'Credit Memo'
END AS DocTypeDesc,
docnumbr,
taxdtsls,
taxamnt,
tax_date,
period,
0 AS LNITMSEQ,
posted,
custnmbr,
custname,
docstatus,
docnumbr AS Voucher,
source,
factor,
custclas,
taxamnt AS DocHdrTax,
taxdtsls AS DocHdrAmt
FROM dbo.bi_vats2
UNION
SELECT taxdtlid,
doctype,
CASE
WHEN doctype = 1 THEN 'Invoice'
WHEN doctype = 5 THEN 'Credit Memo'
END AS DocTypeDesc,
docnumbr,
txdttpur,
taxamnt,
tax_date,
period,
0 AS Lnitmseq,
posted,
vendorid,
vendname,
docstatus,
vchnumwk,
source,
factor,
vndclsid,
taxamnt AS DocHdrTax,
txdttpur AS DocHdrAmt
FROM dbo.bi_vatp
UNION
SELECT taxdtlid,
poptype,
CASE
WHEN poptype = 2 THEN 'Invoice'
WHEN poptype = 3 THEN 'Shipment/Invoice'
WHEN poptype = 5 THEN 'Return w/Credit'
END AS DocTypeDesc,
poprctnm,
totpurch,
taxamnt,
tax_date,
period,
0 AS Lnitmseq,
posted,
vendorid,
vendname,
docstatus,
poprctnm AS Voucher,
source,
factor,
vndclsid,
taxamnt AS DocHdrTax,
totpurch AS DocHdrAmt
FROM dbo.bi_vatp2) AS AllVAT
GROUP BY taxdtlid,
period,
sopnumbe,
tax_date,
posted,
custnmbr,
docstatus,
soptype,
voucher,
source,
factor,
custclas,
CASE
WHEN custclas IN ( 'STAFF', 'STAFF LOAN', 'FOREIGN' ) THEN
'Exclude'
ELSE 'Include'
END,
doctypedesc,
custname,
dochdrtax * factor,
dochdramt * factor
go
/****** Object: View [dbo].[BI_VATGL] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATGL]
AS
SELECT dbo.gl00105.actnumst,
dbo.tx00201.taxdtlid,
AllGL.trxdate,
Sum(AllGL.debitamt) AS Debit,
Sum(AllGL.crdtamnt) AS Credit,
Sum(AllGL.debitamt - AllGL.crdtamnt) AS Amount,
Cast(Year(AllGL.trxdate) AS VARCHAR) + '-'
+ RIGHT('00' + Cast(Month(AllGL.trxdate) AS VARCHAR), 2) AS Period,
AllGL.ordocnum AS DocNum,
dbo.tx00201.txdtlpct,
AllGL.jrnentry,
AllGL.posted,
AllGL.ortrxtyp,
AllGL.orctrnum,
AllGL.sourcdoc
FROM dbo.tx00201
INNER JOIN dbo.gl00105
ON dbo.tx00201.actindx = dbo.gl00105.actindx
INNER JOIN (SELECT dbo.gl10001.jrnentry,
dbo.gl10000.trxdate,
dbo.gl10001.actindx,
dbo.gl10001.ordocnum,
dbo.gl10001.debitamt,
dbo.gl10001.crdtamnt,
'Unposted' AS Posted,
dbo.gl10001.ortrxtyp,
dbo.gl10001.orctrnum,
dbo.gl10000.sourcdoc
FROM dbo.gl10001
INNER JOIN dbo.gl10000
ON dbo.gl10001.jrnentry =
dbo.gl10000.jrnentry
UNION
SELECT jrnentry,
trxdate,
actindx,
ordocnum,
debitamt,
crdtamnt,
'Posted' AS Posted,
ortrxtyp,
orctrnum,
sourcdoc
FROM dbo.gl20000 AS GL20000_1
UNION
SELECT jrnentry,
trxdate,
actindx,
ordocnum,
debitamt,
crdtamnt,
'Posted' AS Posted,
ortrxtyp,
orctrnum,
sourcdoc
FROM dbo.gl30000) AS AllGL
ON dbo.tx00201.actindx = AllGL.actindx
GROUP BY dbo.gl00105.actnumst,
dbo.tx00201.taxdtlid,
AllGL.trxdate,
Cast(Year(AllGL.trxdate) AS VARCHAR) + '-'
+ RIGHT('00' + Cast(Month(AllGL.trxdate) AS VARCHAR), 2),
AllGL.ordocnum,
dbo.tx00201.txdtlpct,
AllGL.jrnentry,
AllGL.posted,
AllGL.ortrxtyp,
AllGL.orctrnum,
AllGL.sourcdoc
HAVING ( dbo.tx00201.txdtlpct > 0 )
AND ( NOT ( AllGL.sourcdoc IN ( 'pmvvr' ) ) )
go
/****** Object: View [dbo].[BI_VATALLGL] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATALLGL]
AS
SELECT dbo.bi_vatall.source,
dbo.bi_vatall.taxdtlid AS SLTaxDtl
,
dbo.bi_vatall.amount
AS SLAmt,
dbo.bi_vatall.tax AS SLTax,
dbo.bi_vatall.period AS SLPeriod
,
dbo.bi_vatall.docnum
AS SLDocnum,
dbo.bi_vatall.posted AS SLPosted
,
dbo.bi_vatgl.taxdtlid
AS GLTaxDtl,
dbo.bi_vatgl.amount AS GLAmt,
dbo.bi_vatgl.period AS GLPeriod
,
dbo.bi_vatgl.docnum
AS GLDocNum,
Isnull(dbo.bi_vatall.period, dbo.bi_vatgl.period) AS Period,
dbo.bi_vatgl.jrnentry AS
GLJournal,
dbo.bi_vatgl.posted AS GLPosted
,
dbo.bi_vatall.custnmbr
AS CustVend,
dbo.bi_vatall.docstatus,
dbo.bi_vatall.doctype,
dbo.bi_vatall.voucher,
Rtrim(dbo.bi_vatall.taxdtlid)
+ Rtrim(dbo.bi_vatgl.taxdtlid) AS
BlankCheck
,
Isnull(dbo.bi_vatall.taxdtlid, dbo.bi_vatgl.taxdtlid) AS
TaxDtl,
LEFT(Isnull(dbo.bi_vatall.period, dbo.bi_vatgl.period), 4) AS Year,
RIGHT(Isnull(dbo.bi_vatall.period, dbo.bi_vatgl.period), 2) AS Month,
dbo.bi_vatall.class,
dbo.bi_vatall.invatreport,
dbo.bi_vatall.doctypedesc,
dbo.bi_vatall.NAME,
dbo.bi_vatall.tax_date,
Isnull(dbo.bi_vatall.docnum, dbo.bi_vatgl.docnum) AS
DocNumSLGL
,
dbo.bi_vatall.amount + dbo.bi_vatall.tax
+ dbo.bi_vatgl.amount AS
ZeroCheck,
dbo.bi_vatall.dochdrtax,
dbo.bi_vatall.dochdramt
FROM dbo.bi_vatall
FULL OUTER JOIN dbo.bi_vatgl
ON dbo.bi_vatall.voucher = dbo.bi_vatgl.orctrnum
AND dbo.bi_vatall.doctype = dbo.bi_vatgl.ortrxtyp
AND dbo.bi_vatall.taxdtlid = dbo.bi_vatgl.taxdtlid
AND dbo.bi_vatall.docnum = dbo.bi_vatgl.docnum
go
/****** Object: View [dbo].[BI_VATALLGL_Doc0] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATALLGL_Doc0]
AS
SELECT dbo.bi_vatallgl.period,
dbo.bi_vatallgl.docnumslgl,
Sum(dbo.bi_vatallgl.slamt) AS SLAmt,
Sum(dbo.bi_vatallgl.sltax) AS SLTax,
dbo.bi_vatallgl.dochdramt AS
SLDocHdrAmt,
dbo.bi_vatallgl.dochdrtax AS
SLDocHdrTax,
dbo.bi_vatallgl.slposted,
Sum(dbo.bi_vatallgl.glamt) AS GLAmt,
Max(dbo.bi_vatallgl.glposted) AS
GLPosted,
Max(dbo.gl00105.actnumst) AS
ACTNUMST,
Max(dbo.gl00100.actdescr) AS
ACTDESCR,
Max(dbo.tx00201.actindx) AS ACTINDX
,
dbo.bi_fyperiods.fperiod,
dbo.bi_fyperiods.fyr,
Cast(dbo.bi_fyperiods.fyr AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(dbo.bi_fyperiods.fperiod AS VARCHAR), 2) AS
FyPeriod,
dbo.bi_vatallgl.invatreport,
dbo.bi_vatallgl.source,
dbo.bi_vatallgl.doctypedesc
FROM dbo.gl00100
INNER JOIN dbo.tx00201
ON dbo.gl00100.actindx = dbo.tx00201.actindx
INNER JOIN dbo.gl00105
ON dbo.tx00201.actindx = dbo.gl00105.actindx
RIGHT OUTER JOIN dbo.bi_fyperiods
INNER JOIN dbo.bi_vatallgl
ON dbo.bi_fyperiods.pyr = dbo.bi_vatallgl.year
AND dbo.bi_fyperiods.pmth0 =
dbo.bi_vatallgl.month
ON dbo.tx00201.taxdtlid = dbo.bi_vatallgl.taxdtl
GROUP BY dbo.bi_vatallgl.period,
dbo.bi_vatallgl.slposted,
dbo.bi_fyperiods.fperiod,
dbo.bi_fyperiods.fyr,
Cast(dbo.bi_fyperiods.fyr AS VARCHAR)
+ '-'
+ RIGHT('00' + Cast(dbo.bi_fyperiods.fperiod AS VARCHAR), 2),
dbo.bi_vatallgl.invatreport,
dbo.bi_vatallgl.source,
dbo.bi_vatallgl.doctypedesc,
dbo.bi_vatallgl.dochdramt,
dbo.bi_vatallgl.dochdrtax,
dbo.bi_vatallgl.docnumslgl
go
/****** Object: View [dbo].[BI_VATALLGL_Doc] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATALLGL_Doc]
AS
SELECT period,
docnumslgl,
Sum(slamt) AS SLAmt,
Sum(sltax) AS SLTax,
Sum(sldochdramt) AS SLDocHdrAmt,
Sum(sldochdrtax) AS SLDocHdrTax,
Max(slposted) AS SLPosted,
Sum(glamt) AS GLAmt,
Max(glposted) AS GLPosted,
Max(actnumst) AS ACTNUMST,
Max(actdescr) AS ACTDESCR,
Max(actindx) AS ACTINDX,
fperiod,
fyr,
fyperiod,
Max(invatreport) AS InVATReport,
Max(source) AS Source,
Max(doctypedesc) AS DocTypeDesc
FROM dbo.bi_vatallgl_doc0
GROUP BY period,
docnumslgl,
fperiod,
fyr,
fyperiod
go
/****** Object: View [dbo].[BI_VATALLGL_Smry] Script Date: 10/15/2019 10:13:06 AM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE VIEW [dbo].[BI_VATALLGL_Smry]
AS
SELECT period,
sltaxdtl,
Sum(slamt) AS SLAmt,
Sum(sltax) AS SLTax,
Sum(sldochdramt) AS SLDocHdrAmt,
Sum(sldochdrtax) AS SLDocHdrTax,
slposted,
gltaxdtl,
Sum(glamt) AS GLAmt,
glposted,
actnumst,
actdescr,
actindx,
fperiod,
fyr,
fyperiod,
invatreport,
source,
doctypedesc,
Sum(slamt + sltax - sldochdramt - sldochdrtax) AS Variance
FROM dbo.bi_vatallgl_doc
GROUP BY period,
sltaxdtl,
slposted,
gltaxdtl,
glposted,
actnumst,
actdescr,
actindx,
fperiod,
fyr,
fyperiod,
invatreport,
source,
doctypedesc
go
