/****** 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
Dynamics GP - SQL Job to delete empty batches nightly
Run nightly when users are not using the system, and there is no risk of deleting a new batch someone is actively working on
------------------------------------------------------------------------------------------------------
--SELECT *
DELETE FROM SY00500
WHERE NUMOFTRX = 0 and SERIES = 3 and BCHSOURC = 'Sales Entry' and
bachnumb not in ( select bachnumb from SOP10100 )
------------------------------------------------------------------------------------------------------
--SELECT *
DELETE FROM SY00500
WHERE NUMOFTRX = 0 and SERIES = 3 and BCHSOURC = 'Sales Entry' and
bachnumb not in ( select bachnumb from SOP10100 )
Monday, September 2, 2019
Dynamics GP - Trigger to track if VAT is changed on an SOP transaction
--USE COMPANY DATABASE
CREATE TABLE [dbo].[BI_SOP10105_Tracking](
[SOPTYPE] [smallint] NOT NULL,
[SOPNUMBE] [char](21) NOT NULL,
[LNITMSEQ] [int] NOT NULL,
[TAXDTLID] [char](15) NOT NULL,
[ChangeType] [char](50) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL,
[OLDSTAXAMNT] [numeric](19, 5) NOT NULL,
[NEWSTAXAMNT] [numeric](19, 5) NOT NULL,
[USERID] [char](50) NOT NULL,
[RowID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_BI_SOP10105_Tracking] PRIMARY KEY CLUSTERED
(
[SOPTYPE] ASC,
[SOPNUMBE] ASC,
[LNITMSEQ] ASC,
[TAXDTLID] ASC,
[ChangeDateTime] ASC,
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT DELETE, INSERT, SELECT, UPDATE ON [dbo].[BI_SOP10105_Tracking] TO [DYNGRP]
GO
--USE COMPANY DATABASE
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[BI_SOP10105_D]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[BI_SOP10105_D]
GO
CREATE TRIGGER BI_SOP10105_D ON dbo.SOP10105
FOR DELETE
AS
BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
SOPTYPE,
SOPNUMBE,
LNITMSEQ,
TAXDTLID,
'Delete',
GETDATE(),
STAXAMNT,
0,
USER_NAME()
FROM deleted
END TRY
BEGIN CATCH
-- exit
END CATCH
GO
--USE COMPANY DATABASE
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[BI_SOP10105_I_U]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[BI_SOP10105_I_U]
GO
CREATE TRIGGER BI_SOP10105_I_U ON dbo.SOP10105
FOR INSERT, UPDATE
AS
BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
i.SOPTYPE,
i.SOPNUMBE,
i.LNITMSEQ,
i.TAXDTLID,
CASE WHEN d.SOPTYPE IS NULL THEN 'Insert' ELSE 'Update' END,
GETDATE(),
CASE WHEN d.SOPTYPE IS NULL THEN 0 ELSE d.STAXAMNT END,
i.STAXAMNT,
USER_NAME()
FROM inserted i
LEFT OUTER JOIN deleted d ON i.SOPTYPE = d.SOPTYPE AND i.SOPNUMBE = d.SOPNUMBE AND i.LNITMSEQ = d.LNITMSEQ AND i.TAXDTLID = d.TAXDTLID
WHERE d.SOPTYPE IS NULL
OR (NOT d.SOPTYPE IS NULL AND d.STAXAMNT <> i.STAXAMNT)
END TRY
BEGIN CATCH
-- exit
END CATCH
GO
----------------------------------------------------------------------------------
View to display changes
-----------------------------------------------------------------------------------
/****** Object: View [dbo].[BI_AUDIT_SOP10105_1] Script Date: 9/2/2019 7:15:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_1]
AS
SELECT TOP (100) PERCENT SOPNUMBE, OLDSTAXAMNT AS OldTaxAmt, NEWSTAXAMNT AS NewTaxAmt, LNITMSEQ, MIN(RowID) AS RowID, USERID, MAX(OLDSTAXAMNT) AS OrigOldTaxAmt, COUNT(RowID) AS Count
FROM dbo.DAV_SOP10105_Tracking
GROUP BY SOPNUMBE, LNITMSEQ, USERID, OLDSTAXAMNT, NEWSTAXAMNT
HAVING (LNITMSEQ > 0)
ORDER BY RowID
GO
/****** Object: View [dbo].[BI_AUDIT_SOP10105_2] Script Date: 9/2/2019 7:15:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_2]
AS
SELECT dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, MAX(dbo.BI_AUDIT_SOP10105_1.OrigOldTaxAmt) AS OrigOldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.OldTaxAmt) AS OldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.NewTaxAmt) AS NewTaxAmt,
dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384 AS LNITMSEQ, MAX(dbo.BI_AUDIT_SOP10105_1.RowID) AS RowID, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME
FROM dbo.BI_AUDIT_SOP10105_1 LEFT OUTER JOIN
(SELECT SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
FROM dbo.SOP10100
UNION
SELECT SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
FROM dbo.SOP30200) AS allsop ON dbo.BI_AUDIT_SOP10105_1.SOPNUMBE = allsop.SOPNUMBE
GROUP BY dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME
GO
/****** Object: View [dbo].[BI_AUDIT_SOP10105_3] Script Date: 9/2/2019 7:15:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_3]
AS
SELECT SOPNUMBE, OrigOldTaxAmt, CASE WHEN LEFT(sopnumbe, 2) = 'OM' THEN NewTaxamt ELSE NewTaxAmt - OldTaxAmt END AS NewTaxAmt,
CASE WHEN newtaxamt <> oldtaxamt THEN 'Tax Changed' ELSE '' END AS [Tax Changed], LNITMSEQ, RowID, USERID, SOPTYPE, DOCID, CUSTNMBR, CUSTNAME, OldTaxAmt
FROM dbo.BI_AUDIT_SOP10105_2
GO
CREATE TABLE [dbo].[BI_SOP10105_Tracking](
[SOPTYPE] [smallint] NOT NULL,
[SOPNUMBE] [char](21) NOT NULL,
[LNITMSEQ] [int] NOT NULL,
[TAXDTLID] [char](15) NOT NULL,
[ChangeType] [char](50) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL,
[OLDSTAXAMNT] [numeric](19, 5) NOT NULL,
[NEWSTAXAMNT] [numeric](19, 5) NOT NULL,
[USERID] [char](50) NOT NULL,
[RowID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_BI_SOP10105_Tracking] PRIMARY KEY CLUSTERED
(
[SOPTYPE] ASC,
[SOPNUMBE] ASC,
[LNITMSEQ] ASC,
[TAXDTLID] ASC,
[ChangeDateTime] ASC,
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT DELETE, INSERT, SELECT, UPDATE ON [dbo].[BI_SOP10105_Tracking] TO [DYNGRP]
GO
--USE COMPANY DATABASE
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[BI_SOP10105_D]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[BI_SOP10105_D]
GO
CREATE TRIGGER BI_SOP10105_D ON dbo.SOP10105
FOR DELETE
AS
BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
SOPTYPE,
SOPNUMBE,
LNITMSEQ,
TAXDTLID,
'Delete',
GETDATE(),
STAXAMNT,
0,
USER_NAME()
FROM deleted
END TRY
BEGIN CATCH
-- exit
END CATCH
GO
--USE COMPANY DATABASE
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[BI_SOP10105_I_U]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[BI_SOP10105_I_U]
GO
CREATE TRIGGER BI_SOP10105_I_U ON dbo.SOP10105
FOR INSERT, UPDATE
AS
BEGIN TRY
INSERT INTO BI_SOP10105_Tracking
SELECT
i.SOPTYPE,
i.SOPNUMBE,
i.LNITMSEQ,
i.TAXDTLID,
CASE WHEN d.SOPTYPE IS NULL THEN 'Insert' ELSE 'Update' END,
GETDATE(),
CASE WHEN d.SOPTYPE IS NULL THEN 0 ELSE d.STAXAMNT END,
i.STAXAMNT,
USER_NAME()
FROM inserted i
LEFT OUTER JOIN deleted d ON i.SOPTYPE = d.SOPTYPE AND i.SOPNUMBE = d.SOPNUMBE AND i.LNITMSEQ = d.LNITMSEQ AND i.TAXDTLID = d.TAXDTLID
WHERE d.SOPTYPE IS NULL
OR (NOT d.SOPTYPE IS NULL AND d.STAXAMNT <> i.STAXAMNT)
END TRY
BEGIN CATCH
-- exit
END CATCH
GO
----------------------------------------------------------------------------------
View to display changes
-----------------------------------------------------------------------------------
/****** Object: View [dbo].[BI_AUDIT_SOP10105_1] Script Date: 9/2/2019 7:15:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_1]
AS
SELECT TOP (100) PERCENT SOPNUMBE, OLDSTAXAMNT AS OldTaxAmt, NEWSTAXAMNT AS NewTaxAmt, LNITMSEQ, MIN(RowID) AS RowID, USERID, MAX(OLDSTAXAMNT) AS OrigOldTaxAmt, COUNT(RowID) AS Count
FROM dbo.DAV_SOP10105_Tracking
GROUP BY SOPNUMBE, LNITMSEQ, USERID, OLDSTAXAMNT, NEWSTAXAMNT
HAVING (LNITMSEQ > 0)
ORDER BY RowID
GO
/****** Object: View [dbo].[BI_AUDIT_SOP10105_2] Script Date: 9/2/2019 7:15:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_2]
AS
SELECT dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, MAX(dbo.BI_AUDIT_SOP10105_1.OrigOldTaxAmt) AS OrigOldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.OldTaxAmt) AS OldTaxAmt, SUM(dbo.BI_AUDIT_SOP10105_1.NewTaxAmt) AS NewTaxAmt,
dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384 AS LNITMSEQ, MAX(dbo.BI_AUDIT_SOP10105_1.RowID) AS RowID, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME
FROM dbo.BI_AUDIT_SOP10105_1 LEFT OUTER JOIN
(SELECT SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
FROM dbo.SOP10100
UNION
SELECT SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CUSTNAME
FROM dbo.SOP30200) AS allsop ON dbo.BI_AUDIT_SOP10105_1.SOPNUMBE = allsop.SOPNUMBE
GROUP BY dbo.BI_AUDIT_SOP10105_1.SOPNUMBE, dbo.BI_AUDIT_SOP10105_1.LNITMSEQ / 16384, dbo.BI_AUDIT_SOP10105_1.USERID, allsop.SOPTYPE, allsop.DOCID, allsop.CUSTNMBR, allsop.CUSTNAME
GO
/****** Object: View [dbo].[BI_AUDIT_SOP10105_3] Script Date: 9/2/2019 7:15:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AUDIT_SOP10105_3]
AS
SELECT SOPNUMBE, OrigOldTaxAmt, CASE WHEN LEFT(sopnumbe, 2) = 'OM' THEN NewTaxamt ELSE NewTaxAmt - OldTaxAmt END AS NewTaxAmt,
CASE WHEN newtaxamt <> oldtaxamt THEN 'Tax Changed' ELSE '' END AS [Tax Changed], LNITMSEQ, RowID, USERID, SOPTYPE, DOCID, CUSTNMBR, CUSTNAME, OldTaxAmt
FROM dbo.BI_AUDIT_SOP10105_2
GO
Dynamics GP - VAT Period Report does not match GL account balances
Reports>Company>Taxes>Tax period report
- TX30000 - Tax History Detail
- Tax period report uses Tax Date field
- GL20000 – Open Year Posted Transactions
- GL20000 - Open year transactions
- GL30000 – Historical Year Transactions
- SOP30200 - SOP document Header
------------------------------------------------------------------------------------------------------------
Compare TX30000 to SOP30200 for specific days or documents to confirm if transactions are missing from the TX30000.
Use this script to rebuild the TX30000 table as required
http://dynamicsgpblogster.blogspot.com/2009/01/rebuilding-tax-history.html
Compare TX30000 to SOP30200 for specific days or documents to confirm if transactions are missing from the TX30000.
Use this script to rebuild the TX30000 table as required
http://dynamicsgpblogster.blogspot.com/2009/01/rebuilding-tax-history.html
--Adjust doctype for other docs /* 2009. Created by Mariano Gomez, MVP This code is provided "AS IS" with no warranties expressed or implied To be executed against your company database */ WITH SOPDocs(SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR) AS ( SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP10100 WHERE SOPTYPE = 3 UNION ALL SELECT SOPNUMBE, SOPTYPE, DOCDATE, Tax_Date, GLPOSTDT, DOCAMNT, ECTRX, VOIDSTTS, CUSTNMBR FROM SOP30200 WHERE SOPTYPE = 3 ) INSERT INTO TX30000 ( DOCNUMBR,DOCTYPE,SERIES,RCTRXSEQ,SEQNUMBR,TAXDTLID,TXDTLPCT,TXDTLAMT,ACTINDX,DOCDATE,Tax_Date,PSTGDATE,TAXAMNT,ORTAXAMT,Taxable_Amount, Originating_Taxable_Amt,DOCAMNT,ORDOCAMT,ECTRX,VOIDSTTS,CustomerVendor_ID,CURRNIDX,Included_On_Return,Tax_Return_ID,TXORGN,TXDTLTYP, TRXSTATS,RETNUM,YEAR1,INVATRET,VATCOLCD,VATRPTID,Revision_Number,PERIODID,ISGLTRX) SELECT a.SOPNUMBE, a.SOPTYPE, 1, 0, ROW_NUMBER() OVER(PARTITION BY a.SOPNUMBE ORDER BY a.TAXDTLID), a.TAXDTLID, b.TXDTLPCT, b.TXDTLAMT, a.ACTINDX, c.DOCDATE, c.Tax_Date, c.GLPOSTDT, a.STAXAMNT, a.ORSLSTAX, a.TAXDTSLS, a.ORTXSLS, c.DOCAMNT, a.ORTOTSLS, c.ECTRX, c.VOIDSTTS, c.CUSTNMBR, a.CURRNIDX, 0, '', 1, 1, '', '', 0, 0, '', '', 0, 0, 0 FROM SOP10105 a LEFT OUTER JOIN TX00201 b ON (a.TAXDTLID = b.TAXDTLID) LEFT OUTER JOIN SOPDocs c on (a.SOPNUMBE = c.SOPNUMBE) and (a.SOPTYPE = c.SOPTYPE) LEFT OUTER JOIN TX30000 d on (a.SOPNUMBE = d.DOCNUMBR) and (a.SOPTYPE = d.DOCTYPE) WHERE (a.SOPTYPE = 3) and (a.LNITMSEQ = 0) and (d.DOCNUMBR IS NULL)
Subscribe to:
Posts (Atom)