Cowmaster's Corner

Tuesday, September 3, 2019

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 )

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

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

--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)

Friday, August 30, 2019

NAV - A Flow field is part of a query column list

This error occurs when you try to write a value into a flowfield.
Change your code to not write to that field, or switch the field to be a normal field.

This also happens if you use a flowfield as a source for another flowfield.

change the formula to use the original logic, or write the data to a temp table first.

NAV - How to set a default freeze pane on a form

https://docs.microsoft.com/en-us/dynamics-nav/freezecolumnid-property

Design>Page>Repeater>Properties
Choose FreezeColumnID
All columns up to that column will be frozen

Thursday, August 29, 2019

GP - How to check if users are deleting tax details from SOP documents

SOP10100 - SOP Header
SOP10200 - SOP Line
sop10105 - Tax Line Details
LNITMSEQ - Tax summary

Put a trigger on the SOP10105 to detect insert,delete,modify

Or, put a trigger on SOP10200 TaxAmnt changing

GP SQL View - Historical Inventory Aging

-------------------------------------------------------------------------------------------

Use this view to build a historical inventory aging by filtering and calculating age of stock in each cost layer. Need to combine with inventory aging logic.

-------------------------------------------------------------------------------------------

SELECT CASE A.RCPTSOLD

WHEN 1 THEN 'Closed'

WHEN 0 THEN 'Open'

ELSE 'NA'

END AS 'Cost Layer Status' ,

A.RCPTNMBR AS 'In Receipt Number' ,

CASE A.PCHSRCTY

WHEN 1 THEN 'Adjustment'

WHEN 2 THEN 'Variance'

WHEN 3 THEN 'Transfer'

WHEN 4 THEN 'Override'

WHEN 5 THEN 'Receipt'

WHEN 6 THEN 'Return'

WHEN 7 THEN 'Assembly'

WHEN 8 THEN 'In-Transit'

ELSE 'NA'

END AS 'In Transaction Type' ,

A.DATERECD AS 'In Date Received' ,

A.ITEMNMBR AS 'In Item Number' ,

A.TRXLOCTN AS 'In Transaction Location' ,

A.QTYRECVD AS 'In Quantity Received' ,

A.QTYSOLD AS 'In Quantity Sold' ,

A.UNITCOST AS 'In Unit Cost' ,

A.RCTSEQNM AS 'In Receipt Sequence Number' ,

ISNULL(B.ORIGInDOCID, ' ') AS 'Out Document Number' ,

ISNULL(B.DOCDATE, ' ') AS 'Out Document Date' ,

ISNULL(B.ITEMNMBR, ' ') AS 'Out Item Number' ,

ISNULL(B.TRXLOCTN, ' ') AS 'Out Transaction Location' ,

ISNULL(B.QTYSOLD, 0) AS 'Out Quantity Sold' ,

ISNULL(B.UNITCOST, 0) AS 'Out Unit Cost' ,

ISNULL(B.SRCRCTSEQNM, ' ') AS 'Out Source Receipt Sequence Number' ,

ISNULL(C.SOPTYPE, ' ') AS 'SLS SOP Type' ,

ISNULL(C.SOPNUMBE, ' ') AS 'SLS SOP Number' ,

ISNULL(C.UNITPRCE, 0) AS 'SLS SOP Unit Price'

FROM IV10200 AS A

LEFT OUTER JOIN IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR

AND A.TRXLOCTN = B.TRXLOCTN

AND A.RCTSEQNM = B.SRCRCTSEQNM

LEFT OUTER JOIN ( SELECT CASE SOPTYPE

WHEN 1 THEN 'Quote'

WHEN 2 THEN 'Order'

WHEN 3 THEN 'Invoice'

WHEN 4 THEN 'Return'

WHEN 5 THEN 'Back Order'

WHEN 6 THEN 'Fulfillment Order'

ELSE 'NA'

END AS SOPTYPE ,

SOPNUMBE ,

ITEMNMBR ,

LOCNCODE ,

UNITCOST ,

UNITPRCE

FROM SOP30300

) AS C ON B.ORIGInDOCID = C.SOPNUMBE

AND B.ITEMNMBR = C.ITEMNMBR

AND B.TRXLOCTN = C.LOCNCODE