Pages

Monday, December 22, 2014

Pure “Bank Transactions and Transfers” with General Ledger Journal Details – SQL Script

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.

Dynamics GP Modules

Pure Bank Transactions with their associated Journals –
>> Related Article: ERP Modules Data Flow

 

The data set view is shown below:

Data Set view

 

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

2 comments:


  1. it is a great contribution, thanks for your effort,

    ReplyDelete
  2. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you 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

    ReplyDelete