/****** 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
Tuesday, September 3, 2019
GP AUDIT View - VAT details and totals for custom VAT smartlist and VAT to GL reconciliation
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment