In Dynamics GP, the bank is affected by other modules such as Receivables and Payables. Pure bank transactions include increase adjustment, decrease adjustment, check, withdrawal and Bank Transfer. In this post, an SQL script is provided to drill link the “pure” bank transactions with their associated general ledger journals.
To simplify the overall target, here is a chart illustrating the basic Dynamics GP modules, it shows how any other module related to the “Bank Reconciliation” is excluded, only transactions entered on the Bank Reconciliation module is retrieved.
Pure Bank Transactions with their associated Journals –
>> Related Article: ERP Modules Data Flow
The data set view is shown below:
--Tables Included:
- CM00100 | CM Check Book Master
- CM20200 | CM Transactions
- CM20300 | CM Receipts
- GL20000 | Year to Date Transactions Open
- GL30000 | Account Transaction History
SELECT X.CHEKBKID 'Checkbook ID',
CMRECNUM 'CM ReconNumber' ,
CMTrxNum 'CM Transaction Number',
TRXDATE AS 'CM Transactin Date',
CASE X.VOIDED
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS 'Voided',
CMTRXTPE 'CM Transaction Type',
paidtorcvdfrom 'Paid To/Received From',
DSCRIPTN 'Description',
JRNENTRY 'Journal Entry',
DEBITAMT 'Debit Amount',
CRDTAMNT 'Credit Amount'
FROM
( SELECT A.CHEKBKID ,
B.ACTINDX ,
A.CMRECNUM ,
A.sRecNum ,
A.CMTrxNum ,
A.TRXDATE ,
CASE A.CMTrxType
WHEN 1 THEN 'Deposit'
WHEN 3 THEN 'Check'
WHEN 4 THEN 'Withdrawal'
WHEN 5 THEN 'Increase Adjustment'
WHEN 6 THEN 'Decrease Adjustment'
WHEN 7 THEN 'Transfer'
ELSE ''
END AS CMTRXTPE ,
A.paidtorcvdfrom ,
CASE
WHEN A.DSCRIPTN = ' '
AND A.CMTrxType <> 7
THEN 'Bank Transaction Entry'
WHEN A.DSCRIPTN = ' '
AND A.CMTrxType = 7
THEN 'Bank Transfer Entry'
ELSE A.DSCRIPTN
END AS DSCRIPTN ,
A.AUDITTRAIL ,
A.SRCDOCNUM ,
A.VOIDED
FROM CM20200 AS A
LEFT OUTER JOIN dbo.CM00100 AS B
ON A.CHEKBKID = B.CHEKBKID
UNION ALL
SELECT A.CHEKBKID ,
B.ACTINDX ,
A.CMRECNUM ,
A.sRecNum ,
A.RCPTNMBR ,
A.RECEIPTDATE ,
CASE A.RcpType
WHEN 1 THEN 'ReceiptCheck'
WHEN 2 THEN 'ReceiptCash'
WHEN 3 THEN 'ReceiptCreditCard'
ELSE ''
END AS ReceiptType ,
A.RcvdFrom ,
CASE A.DSCRIPTN
WHEN ' ' THEN 'Bank Transaction Entry'
ELSE A.DSCRIPTN
END AS DSCRIPTN ,
A.AUDITTRAIL ,
A.SRCDOCNUM ,
A.VOIDED
FROM dbo.CM20300 AS A
LEFT OUTER JOIN dbo.CM00100 AS B
ON A.CHEKBKID = B.CHEKBKID
) AS X
LEFT OUTER JOIN
( SELECT A.JRNENTRY ,
A.DEBITAMT ,
A.CRDTAMNT ,
A.ACTINDX ,
B.CHEKBKID ,
A.REFRENCE ,
A.SOURCDOC ,
A.ORGNTSRC ,
A.ORMSTRNM ,
A.ORMSTRID ,
A.ORDOCNUM ,
A.ORTRXSRC ,
A.VOIDED
FROM
( SELECT JRNENTRY ,
DEBITAMT ,
CRDTAMNT ,
ACTINDX ,
REFRENCE ,
SOURCDOC ,
ORGNTSRC ,
ORMSTRNM ,
ORMSTRID ,
ORDOCNUM ,
ORTRXSRC ,
VOIDED
FROM dbo.GL20000
UNION ALL
SELECT JRNENTRY ,
DEBITAMT ,
CRDTAMNT ,
ACTINDX ,
REFRENCE ,
SOURCDOC ,
ORGNTSRC ,
ORMSTRNM ,
ORMSTRID ,
ORDOCNUM ,
ORTRXSRC ,
VOIDED
FROM dbo.GL30000
) AS A
LEFT OUTER JOIN dbo.CM00100 AS B
ON A.ACTINDX = B.ACTINDX
) AS Y
ON (
X.ACTINDX = Y.ACTINDX
AND X.DSCRIPTN = Y.REFRENCE
AND X.CHEKBKID = Y.ORMSTRID
AND X.AUDITTRAIL = Y.ORGNTSRC
AND X.AUDITTRAIL = Y.ORTRXSRC
AND X.CMTrxNum = Y.ORDOCNUM
AND X.CMTRXTPE IN
( 'Increase Adjustment',
'Decrease Adjustment',
'Check', 'Withdrawal',
'ReceiptCheck','ReceiptCash',
'ReceiptCreditCard'
)
)
OR
(
X.ACTINDX = Y.ACTINDX
AND X.DSCRIPTN = Y.REFRENCE
AND X.AUDITTRAIL = Y.ORTRXSRC
AND X.CMTrxNum = Y.ORDOCNUM
AND X.CMTRXTPE = 'Transfer'
)
WHERE
ISNULL(X.AUDITTRAIL, 0) LIKE 'CMT%' OR
ISNULL(X.AUDITTRAIL, 0) LIKE 'CMX%'
Download Link >>>> Click here to download the SQL script.
Previous related article Reconcile Checkbooks to General Ledger – SQL Script
Best Regards,
Mahmoud M. AlSaadi
ReplyDeleteit is a great contribution, thanks for your effort,
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about
this BLANK ATM CARD when I was searching for job online about a month
ago..It has really changed my life for good and now I can say I'm rich and
I can never be poor again. The least money I get in a day with it is about
$50,000.(fifty thousand USD) Every now and then I keeping pumping money
into my account. Though is illegal,there is no risk of being caught
,because it has been programmed in such a way that it is not traceable,it
also has a technique that makes it impossible for the CCTVs to detect
you..For details on how to get yours today, email the hackers on : (
atmmachinehackers1@gmail.com ). Tell your
loved once too, and start to live large. That's the simple testimony of how
my life changed for good...Love you all ...the email address again is ;
atmmachinehackers1@gmail.com