You’re offline. This is a read only version of the page.
75,730
*This post is locked for comments
That's absolutely precise, when either a payment or cash receipt is issues from either SOP or POP modules on a specific checkbook, the script above will fail to consider the matching.
I have just tested several case scenarios in which the matching was invalid. I will be working on it to cover these special cases,
I will keep you updated.
Mahmoud, I have found a series of transactions that took me a while to find out what they we doing but it breaks the link between the CM20200.SRCDOCNM and GL20000.ORCTRNUM fields. Apparently when they know they are going to be paying the payables transactions off by ACH when they enter the payables transaction, they enter the amount of the transaction in the cash field and then choose the checkbook. When they do that, the value now moves from the GL20000.ORCTRNUM into the GL20000.ORDOCNUM field. The problem I have with that is trying to locate the field that indicates this was done. I have not been able to locate that field.
Thank for this, Mahmoud. On my system, in two..CM20200 there are 2095 records. When I run this script it returns all 2095 record. I will play around with this and refine it to return only the missing transactions.
Sir Richard,
I have just done this on Fabrikam without thorough testing actually. It might need further revision;
SELECT A.CHEKBKID ,
A.TRXDATE ,
A.TRXAMNT ,
A.SOURCDOC ,
A.AUDITTRAIL ,
D.ACTNUMST ,
E.ACTDESCR ,
C.JRNENTRY ,
C.ORGNTSRC ,
C.ORCTRNUM ,
CASE WHEN C.DEBITAMT = 0 THEN C.CRDTAMNT
ELSE C.DEBITAMT
END AS GLAmount ,
C.DEBITAMT ,
C.CRDTAMNT ,
ISNULL(CASE WHEN C.DEBITAMT = 0 THEN C.CRDTAMNT
ELSE C.DEBITAMT
END, 0) - A.TRXAMNT AS VARIANCE
FROM CM20200 AS A
LEFT OUTER JOIN CM00100 AS B ON A.CHEKBKID = B.CHEKBKID
LEFT OUTER JOIN dbo.GL20000 AS C ON A.AUDITTRAIL = C.ORGNTSRC
AND A.SRCDOCNUM = C.ORCTRNUM
AND B.ACTINDX = C.ACTINDX
LEFT OUTER JOIN GL00105 AS D ON C.ACTINDX = D.ACTINDX
LEFT OUTER JOIN dbo.GL00100 AS E ON E.ACTINDX = D.ACTINDX
Yes, that is correct. My script is now failing at these customized relationships. I have opened a case with MS. I am sure they have already written this script. No sense reinventing the wheel. We have thousands of transactions to go through.
Looks like you could start with
GL20000.ORGNTSRC matching to CM20200.AUDITTRAIL
The relationships with GL20000 are dependant on Series and Type. When GP "backtracks" from GL to source, it figures out where the transaction started and uses a customized relationship.
Tim
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,104
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (