I started developing plugins for WordPress almost eight years ago and moved to ClassicPress just over two years ago; I now have 47 plugins available for ClassicPress.
I am creating this series to show how certain aspects of development for ClassicPress plugins are done, partly to benefit others who take up developing for ClassicPress but also as a quick reference for myself including detailed examples.
This series index will automatically update to show new posts as they are published:
This post is part of a series on creating a simple audit for Microsoft Dynamics GP.
As I’ve shown over the last few posts, it is relatively easy to create a simple audit on a table in Dynamics GP, but this approach is not very scalable and requires someone to manually create the SQL triggers. For a client on a shoestring budget and who wanted to audit one table, this approach sufficed.
However, for a larger client who would want to audit more tables in more than one company and who would want to have non-technical users maintaining the audited information, I would recommend a solution like Assure Suite from Fastpath which I have implemented with a umber of clients previously.
What brought this approach to mind was a client had an issue with some incorrect data and we could not determine who or what was changing some data, so I amended this custom audit for the tables we needed to record for that issue. In this instance, there was no need for a full audit solution as it will only be in place temporarily while investigating a specific issue.
This post is part of a series on creating a simple audit for Microsoft Dynamics GP.
Once the audit table and triggers have been deployed, any changes made through the audit will be recorded and available for reviewing later to see who has been making changes and, more significantly, what was changed.
The easiest way of making this available to the client was to create a SmartList for them using SmartList Designer to select data from the new custom audit table. SmartList Designer can see either Dexterity tables or SQL views, but not custom SQL tables, I created a SQL view on the custom audit table, joining it to the Users Master (SY01400) table in the DYNAMICS table to get the username:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW uv_AZRCRV_Audit AS
SELECT
['Audit'].RecordType AS 'Record Type'
,['Audit'].RecordID AS 'Record ID'
,['Audit'].UpdateType AS 'Update Type'
,['Audit'].USERID AS 'User ID'
,['User Master'].Username AS 'Username'
,FORMAT(['Audit'].ChangeDateTime, 'yyyy-MM-dd') AS 'Change Date'
,FORMAT(['Audit'].ChangeDateTime, 'HH:ss') AS 'Change Time'
,['Audit'].OldData AS 'Old Data'
,['Audit'].NewData AS 'New Data'
FROM
ut_AZRCRV_Audit ['Audit']
LEFT JOIN
DYNAMICS..SY01400 AS ['User Master']
ON
['User Master'].USERID = ['Audit'].USERID
GO
GRANT SELECT ON uv_AZRCRV_Audit TO DYNGRP
GO
This post is part of a series on creating a simple audit for Microsoft Dynamics GP.
With the table created to store the audited information, the second step is to create the required triggers on the Address Electronic Funds Transfer Master (SY06000) table. For an Vendor EFT audit there are three triggers required:
These triggers will record all new Vendor EFT information added as well as that which is amended or deleted. The client for which this audit was created only dealt with vendors in the UK and only ever set three fields in the EFT Bank window:
Additional fields can be added to the audit if other fields need to be stored.
As the Address Electronic Funds Transfer Master (SY06000) table holds EFT Bank information for customers as well as vendors, the Record ID has been set to include the Series. Strictly speaking, this was not necessary as the client did not store bank details for their customers in Dynamics GP as none were making direct debit payments.
The audited data is being trimmed and cast as varchar so the extra whitespace held by Dynamics GP due to the columns being chars are removed.
The first trigger creates the trigger which runs when data is inserted:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TRIGGER utr_AZRCRV_SY06000_AuditInsert ON SY06000 AFTER INSERT AS
INSERT INTO
ut_AZRCRV_Audit
--VALUES
SELECT
'Vendor EFT'
,CAST(i.SERIES AS VARCHAR(100)) + ' | ' + CAST(RTRIM(i.CustomerVendor_ID) AS VARCHAR(100)) + ' | ' + CAST(RTRIM(i.ADRSCODE) AS VARCHAR(100))
,'Insert'
,SYSTEM_USER
,GETDATE_USER()
,''
,'BANKNAME = ' + CAST(RTRIM(i.BANKNAME) AS VARCHAR(100)) + ' | ' + 'EFTBankCode = ' + CAST(RTRIM(i.EFTBankCode) AS VARCHAR(100)) + ' | ' + 'EFTBankAcct = ' + CAST(RTRIM(i.EFTBankAcct) AS VARCHAR(100))
FROM
inserted i
GO
Continue reading “Simple Audit for Microsoft Dynamics GP: Create Triggers”
This post is part of a series on creating a simple audit for Microsoft Dynamics GP.
The first step in creating the audit is to create a table in the company database to hold the audited information. From the user point of view there was five pieces of information required:
To make reporting easier and to add an element of future proofing, I also added two other pieces of information:
Vendor EFT.INSERT, UPDATE or DELETEThe following SQL will create a table with the table with the seven columns mentioned above:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE ut_AZRCRV_Audit
(
RecordType VARCHAR(100)
,RecordID VARCHAR(100)
,UpdateType VARCHAR(10)
,USERID VARCHAR(150)
,ChangeDateTime DATETIME
,OldData NVARCHAR(MAX)
,NewData NVARCHAR(MAX)
)
GO
Every month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I’ll be delivering scheduled.
We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.
The upcoming webinars are:
 |
In March is Controls and Security in Microsoft Dynamics GP; see how to improve controls and security in Dynamics GP using a mix of standard and third party functionality..
Tue, March 16th, 2021 4:00 PM – 4:45 PM BST |
 |
In April is Powerful Document Generation for Dynamics GP; watch in this webinar with a live dox42 demo and we will show you how to design attractive document templates in MS Office and integrate data from MS Dynamics GP and many other data sources such as Microsoft 365, SharePoint or SAP.
Tue, April 20th, 2021 4:00 PM – 4:45 PM BST |
 |
In May is Controls and Security in Microsoft Dynamics GP; Discover how Business Intelligence can improve visibility and help you make better, more timely, decisions..
Tue, May 18th, 2021 4:00 PM – 4:45 PM BST |
This is part of the azurecurve ClassicPress Plugins which introduces the plugins I have available for ClassicPress.
The plugin I am going to cover in this post, is a brand new one written for ClassicPress; Check Plugin Status.
This plugin allows you to check the status of plugins on your site:
* Are they in the WordPress Repository.
* Do they use a 3rd party update mechanism such as Update Manager from Code Potent.
* Are they supported on ClassicPress or on WordPress 5+.
* Are they up-to-date.
Exception handling has been built in for Contact Form 7 and Google Sitemap Generator which report a different slug in the API to the slug on the Wordpress Repository.
The plugin can be downloaded via my Development site.
This post is part of a series on creating a simple audit for Microsoft Dynamics GP.
I created a simple audit a while ago when a client had a requiremnet to audit the change of EFT details onvendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expenseive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.
The requirement for the original user was very simple; an audit on changes to vendor EFT in the single company they had in Dynamics GP.
Many of the audit tools available for Dynamics GP work by creating triggers in the database, and this is the same approach I took. The first step in creating the audit was to create a table to hold the audit table, then to create the triggers on the table to be audited and then to provide a method of reporting for users to use.
Over the course of this series, each of these steps will be covered.
I created a simple audit a while ago when a client had a requirement to audit the change of EFT details on vendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expensive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.
In this series of posts, I will step through the process of creating the custom audit, using the vendor EFT one as the example. The index, below, will automatically update as the posts are published, unless you’re reading the post via syndication in which case check the original post.
| Simple Audit for Microsoft Dynamics GP |
|---|
| Introduction |
| Create Table |
| Create Triggers |
| Create Triggers for Audit of Customer Items |
| SQL View for Reporting |
| Conclusion |
Today’s script, like yesterdays, is an unusual one for me to post, as it is not a script which I have written; it is a script which returns the data for a Receivables HATB With Aging By Due Date (using GL Posting). It was written by Sivakumar Venkataramanback in 2011 and came up in conversation recently; the site he posted it to no longer exists and I had to hunt the script out from the wayback machine to retrieve a copy.
The script is below is Siva’s script which I am reposting “as is” as it may be useful to others.
/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging by due date and
picking transactions using the GL Posting Date.
Tables used:
- RM Open File Debit Copy (RM20101)
- RM History File (RM30101)
- RM Apply Open File (RM20201)
- RM Apply History File (RM30201)
- Document Type Setup File (RM40401)
- RM Period Setup (RM40201)
Revision History
No Date Reason for Change
1 12/12/2017 Updated the logic for due date for credits
******************************************************************/
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
SELECT W.CUSTNMBR AS CUSTOMERNO ,
W1.DOCDESCR AS DOCTYPE ,
W.DOCNUMBR ,
W.DOCDATE ,
W.TRXSORCE ,
W.GLPOSTDT AS POSTINGDATE ,
W.DUEDATE ,
W.AGINGBUCKET ,
W.DOCUMENTAMT ,
W.CURTRXAMT
FROM ( SELECT X.CUSTNMBR ,
X.RMDTYPAL ,
X.DOCNUMBR ,
X.DOCDATE ,
X.TRXSORCE ,
X.VOIDED ,
X.GLPOSTDT ,
X.DUEDATE ,
X.DAYSDUE ,
CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201
ORDER BY RMPEREND DESC
)
WHEN X.DAYSDUE < 0 THEN 'Not Due'
ELSE ISNULL(( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201 AG
WHERE X.DAYSDUE <= AG.RMPEREND
ORDER BY RMPEREND
), '')
END AS AGINGBUCKET ,
X.VOIDPDATE ,
X.DOCUMENTAMT ,
X.APPLIEDAMT ,
X.WRITEOFFAMT ,
X.DISCTAKENAMT ,
X.REALGAINLOSSAMT ,
CASE WHEN X.RMDTYPAL < 6
THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
END AS CURTRXAMT
FROM ( SELECT Z.CUSTNMBR ,
Z.RMDTYPAL ,
Z.DOCDATE ,
Z.DOCNUMBR ,
Z.ORTRXAMT AS DOCUMENTAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
WHEN RMDTYPAL > 6
AND RMDTYPAL <= 9
THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
GLPOSTDT ,
APPTOAMT AS APPLDAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
GLPOSTDT ,
APPTOAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyToGLPostDate <= @ASOFDATE
AND Y.ApplyToGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS APPLIEDAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS WRITEOFFAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS DISCTAKENAMT ,
CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
FROM ( SELECT CUSTNMBR ,
GLPOSTDT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate ,
RLGANLOS
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
GLPOSTDT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate ,
RLGANLOS
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyToGLPostDate <= @ASOFDATE
AND Y.ApplyToGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS REALGAINLOSSAMT ,
Z.TRXSORCE ,
Z.VOIDED ,
Z.GLPOSTDT ,
Z.DUEDATE ,
DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
Z.VOIDPDATE
FROM ( SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM20101
UNION
SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM30101
) Z
WHERE Z.GLPOSTDT <= @ASOFDATE
AND Z.VOIDED = 0
AND Z.RMDTYPAL <> 6
) X
UNION ALL
SELECT X.CUSTNMBR ,
X.RMDTYPAL ,
X.DOCNUMBR ,
X.DOCDATE ,
X.TRXSORCE ,
X.VOIDED ,
X.PSTGDATE ,
X.DUEDATE ,
X.DAYSDUE ,
CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201
ORDER BY RMPEREND DESC
)
WHEN X.DAYSDUE < 0 THEN 'Not Due'
ELSE ISNULL(( SELECT TOP 1
RMPERDSC
FROM dbo.RM40201 AG
WHERE X.DAYSDUE <= AG.RMPEREND
ORDER BY RMPEREND
), '')
END AS AGINGBUCKET ,
X.VOIDPDATE ,
X.DOCUMENTAMT ,
X.APPLIEDAMT ,
X.WRITEOFFAMT ,
X.DISCTAKENAMT ,
X.REALGAINLOSSAMT ,
CASE WHEN X.RMDTYPAL < 6
THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
END AS CURTRXAMT
FROM ( SELECT Z.CUSTNMBR ,
Z.RMDTYPAL ,
Z.DOCDATE ,
Z.DOCNUMBR ,
Z.ORTRXAMT AS DOCUMENTAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
APPTOAMT AS APPLDAMT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
WHEN RMDTYPAL > 6
AND RMDTYPAL <= 9
THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
FROM ( SELECT CUSTNMBR ,
GLPOSTDT ,
APPTOAMT AS APPLDAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
GLPOSTDT ,
APPTOAMT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyToGLPostDate <= @ASOFDATE
AND Y.ApplyToGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS APPLIEDAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
WROFAMNT
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS WRITEOFFAMT ,
CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
FROM ( SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
ApplyFromGLPostDate ,
GLPOSTDT ,
APTODCNM ,
APTODCTY ,
APFRDCNM ,
APFRDCTY ,
DISTKNAM
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyFromGLPostDate <= @ASOFDATE
AND Y.ApplyFromGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APTODCNM = Z.DOCNUMBR
AND Y.APTODCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS DISCTAKENAMT ,
CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
FROM ( SELECT CUSTNMBR ,
GLPOSTDT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate ,
RLGANLOS
FROM dbo.RM20201
WHERE POSTED = 1
AND APTODCTY <> 6
UNION
SELECT CUSTNMBR ,
GLPOSTDT ,
APFRDCNM ,
APFRDCTY ,
APTODCNM ,
APTODCTY ,
ApplyToGLPostDate ,
RLGANLOS
FROM dbo.RM30201
WHERE APTODCTY <> 6
) Y
WHERE Y.GLPOSTDT <= @ASOFDATE
AND Y.ApplyToGLPostDate <= @ASOFDATE
AND Y.ApplyToGLPostDate <> @EMPTYDATE
AND Y.CUSTNMBR = Z.CUSTNMBR
AND Y.APFRDCNM = Z.DOCNUMBR
AND Y.APFRDCTY = Z.RMDTYPAL
), 0)
ELSE 0
END AS REALGAINLOSSAMT ,
Z.TRXSORCE ,
Z.VOIDED ,
Z.PSTGDATE ,
Z.DUEDATE ,
DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
Z.VOIDPDATE
FROM ( SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT AS PSTGDATE ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM20101
UNION
SELECT CUSTNMBR ,
RMDTYPAL ,
DOCDATE ,
DOCNUMBR ,
ORTRXAMT ,
BACHNUMB ,
TRXSORCE ,
BCHSOURC ,
DISCDATE ,
VOIDSTTS AS VOIDED ,
GLPOSTDT ,
CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
ELSE [DUEDATE]
END AS DUEDATE ,
VOIDDATE AS VOIDPDATE
FROM dbo.RM30101
) Z
WHERE Z.PSTGDATE <= @ASOFDATE
AND Z.VOIDED = 1
AND Z.RMDTYPAL <> 6
AND Z.VOIDPDATE > @ASOFDATE
) X
) W
INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE W.CURTRXAMT <> 0