Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Thursday, August 14, 2014

TSQL Fun (Pivot Function) – Analytical Accounting Dimensions

As part of a personal project I have been working on, I needed to change the structure of the data set retrieved by a simple join TSQL query, I ended up creating a dynamic code to automatically change the data structure for Analytical Accounting dimensions.

Data Structure

Data Structure

 

Methodology

To get the first result on the left, a simple join is required between AAG00400 (Transaction Dimension Master) and AAG00401(Transaction Dimension Code Setup).

SELECT  A.aaTrxDim ,
       
B.aaTrxDimCode
        FROM    AAG00400 AS A
       
LEFT OUTER JOIN AAG00401 AS B ON A.aaTrxDimID = b.aaTrxDimID

 

JoinDimensions

I will review two methods to switch the rows into columns, a static Pivot and dynamic one.

Static Method

In this case, I should include a hard-coded values within the SQL statement in order to switch the rows into columns. Which means, when a new dimension is added, it will not “automatically” appear in the result unless the code is modified.

SELECT  *
FROM
( SELECT *
 
FROM    ( SELECT 
              
ROW_NUMBER() OVER
              
( PARTITION BY A.aaTRXDIM ORDER BY B.aaTrxDimCode ) 
                 AS DimensionCodeRank,
                 A.aaTrxDim ,
                 B.aaTrxDimCode
                
FROM AAG00400 AS A
                 LEFT OUTER JOIN AAG00401 AS B
                 ON A.aaTrxDimID = b.aaTrxDimID
                 ) P PIVOT 
    
( MAX(aaTRXDIMCode) FOR aaTRXDIM IN 
                          
( [COSTCENTER], [EXPENSETYPE],
                            
[LOCATION], [ORGANAZIONAL LEVEL],
                            
[PROJECT], [REGION], [SECTOR] ) ) AS PVT
          
) AS AB

As applied above, the dimension codes are hard-coded into the SQL statement, which makes this method improper for dynamic solution. A similar PIVOT function has been used in a previous post to manipulate inventory balances, IV Available Quantity Report – SQL Pivot Function

Dynamic Method

Through this method, any dimension added on the Analytical Accounting module will be automatically retrieved, there is no need at all to modify the statement.

DECLARE @columns NVARCHAR(MAX) ,
       
@columns_n NVARCHAR(MAX) ,
       
@Statement NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT  @columns += N', X.' + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
         
FROM      dbo.AAG00400
        
) AS Y;
SELECT  @columns_n +=
        N', ISNULL(' + N'X.' + QUOTENAME(aaTrxDim)
+ N', '''' ) AS'
        + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
FROM      dbo.AAG00400
) AS Y;

SET
@Statement = N'
SELECT ' + STUFF(@columns_n, 1, 2, '') + '
FROM
(
    SELECT   ROW_NUMBER()
    over(partition by  A.aaTRXDIM order by B.aaTrxDimCode) AS DimensionCodeRank,
    A.aaTrxDim,B.aaTrxDimCode FROM AAG00400 AS A LEFT OUTER JOIN AAG00401 AS B
    ON A.aaTrxDimID = b.aaTrxDimID
) AS j

PIVOT
(
MAX(aaTRXDIMCode) FOR aaTRXDIM IN  (' + STUFF(REPLACE(@columns, ', X.[', ',['),1, 1, '') + ')) AS X;';

PRINT @Statement;
EXEC sp_executesql @Statement;

Here is the result for both of the method above,

Manipulated Data Structure

 

Best Regards,
Mahmoud M. AlSaadi

Monday, August 11, 2014

Analytical Accounting Budget Import Trap

While importing Analytical Accounting budget, after being exported from another machine with the most recent backup of the Live GP Company, I got stuck with an “Unhandled Object Exception”, it keeps on popping up right after choosing the file, it is not even checking or validating the data within the Excel.

After several tries, I have discovered that someone else has been there before, not too long ago Mr. Mark Polino has gone through the same issue, and found out that you’ve got to have Excel installed on the machine, so simple. I shall keep credit for DynamicAccounting on this one.

Since this is not documented, I am writing this post to further spread the remedy, hopefully it will save someone in the near future from spending hours away from home, with a great companion “Analytical Accounting” !

Error Message:

Unhandled object exception:
Invalid class string

EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147221005
Unhandled object exception:
Invalid class string

EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147221005

Resolution:

Install Microsoft Excel on the machine

In this essence, I would like to shed a light on another common import error with Analytical Accounting. After you import the file, you get a report on the screen stating “ The import failed because the spreadsheet is in an incorrect format ”. There are three possible reasons for this:

  • In most cases when you export the AA budget, the budge tree ID field is empty. Sometimes, when you manually fill in the template, it doesn’t match the one configured in Dynamics GP. To get this resolved, fill in the correct Budget Tree ID.

Budget Tree ID

  • In case you fill in the template manually, double check the totals entered on each level of the tree
  • In addition to the above, in case of manual entry as well, The final total column should be the total of all period amount and the opening balance.

Fortunately, this is documented in knowledge base Article ID 2815557

 

Best Regards,

Mahmoud M. AlSaadi

Wednesday, August 6, 2014

Pay attention through Initial Assessment , when it Comes to Inventory Items and Currency Decimals

After an implementation of Dynamics GP for one of our clients, I have received a critical and weird case stating that all the inventory related transactions (Shipment, Inventory Adjustment and Sales) are recorded with a “Zero” unit cost, although, they are entering the correct unit cost when receiving the shipment. Here is the starting point of the case”

SmartList view                                                               >>  All unit cost fields are zero, on both In and Out transactions

Troubleshooting:

The item number is defined as a sales inventory, FIFP perpetual valuation method and assigned to a KG unit of measurement. Currency decimals on the system level and distribution modules is set to (3) for this selected currency used for this item. The following is the UOM setup of KG:

UOM Schedule 

Here is the interesting part, starting with the first transaction on this item (which is a shipment), the following are the transaction details:

  • Quantity shipped: 50
  • UOM: KG
  • Unit Cost (for the KG): 0.44

Receiving Line Items

Now, on the inventory module, the system will break down the unit cost into the smallest unit of measurement, and record the cost layer in the base unit of measurement (IV10200). In case of rounding, the system will consider splitting the cost layers into two cost layers >> Reference: unit cost and currency decimal setup

  • Quantity in Base UOM: 50 * (1000) = 50,000 Gram
  • Unit cost (for the Gram): 0.44/1000 = 0.00044

The result that the Inventory Module will split into two cost layers, (49,999 Gram) and (1 Gram), the one gram will have the variance which is the total cost in our scenario (22). Here is the purchase receipt smart list again:

Original Receipt

By now, it is clear that any (out) transaction withdrawing from this cost layer will have a zero unit cost, which is incorrect. Until, all 49,999 Gram are consumed, the unit cost will be zero. Then when withdrawing the final one gram, the total cost of the whole 50 KG will be consumed.

Correction Criteria

  1. Post or delete all pending “saved” transactions
  2. Change the currency decimals (on the distribution level, not the system level) to encompass more than the current number of decimals for this specific item, not necessarily all the items.
  3. Adjust cost for the historical transactions

 

In order to change currency decimals, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Change Decimal Places

Change Decimal Places

Helping Note !

  • Before running the change decimal places utilities, no user in the company should be working on any of the inventory related transactions (Such as:Inventory Adjustment, Transfer, Receiving, Sales …etc).
  • No transaction should be saved or pending on a purchase order, receiving …etc.
  • One pressing on “Process”, you will be warned that Manufacturing isn’t updated when you change currency decimals. It’s fine, just proceed

In order to adjust the cost, go to Microsoft Dynamics GP > Tools > Utilities > Inventory > Adjust Cost

Adjust Cost Utility

After processing the cost adjustment, a report will show all the journal entries created to adjust the cost on the “General Ledger” as well. Please be noted that not only the current cost layer; which is being adjusted, will be affected. ALL related “out” transaction which withdraws from this specific cost layer will be affected accordingly.

The affected transactions will be listed in the Purchase Receipt Update Detail report, after processing the cost adjustment.

Adjust Cost Report

Here is the journal entries created on the General Ledger, it will affect inventory and inventory offset accounts (COGS)

Cost Adjustment - Journals

 

Best Regards,

Mahmoud M. AlSaadi

Saturday, August 2, 2014

Reconcile Check books to General Ledger – SQL Script

 

On a routine bases, sub ledgers should be reconciled with the General Ledger module, it is part of the accounting best practices that should be applied throughout the monthly financial closing. Now with GP 2013, the Reconcile to GL utility has made it simple for accountants to reconcile AP, AR, CM and Inventory with the General Ledger. Although, with previous versions it was a nightmare.

I have been working recently on developing a reconcile utility for previous GP versions. In this post, a script to reconcile check books to general ledger is provided (CM To GL). The script is still under testing as further revision version are required to stabilize its performance. Despite of its yet weak performance, it could still deliver a bit of an added value for those who are still living with the old versions of Dynamics GP. Of course, this is not to encourage them in any way to stay with old fashioned versions, but still better than nothing.

The script output in general is illustrated below;

Script Output

Digging deeper in the CM and GL parts, the following is more in depths output:

In details output

 

Helping Notes:

  • The script reconciles only one check book at a time, you can not reconcile more than one.
  • In order to run the script, pass the parameters required on the (Where) section which are
    • Checkbook ID
    • Month (Optional but recommended)
    • Year (Optional but recommended)
  • The script may have weak performance on large company database since it brings data from several GP modules (CM, AP, AR and GL)
  • Several additional GP modules have not been tested such as Payment Document Management (PDC), Schedule Payments and others.
  • As an initial test, the script below will run on (TWO) database (Fabrikam) and “Petty Cash” checkbook.

/*----------------------------------------------------------------------------------------

Creation Date: 3rd of August, 2014

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to reconcile CM Transactions resulting from (CM, APAR) to General Ledger

The script has been tested on a very limited sample data. Several modules were not tested such as Schedule Payments, Payment Document Management ...etc.

Revision History:

Revision No.            RevisionDate    Description

1                       03/08/2014      Original Version

------------------------------------------------------------------------------------------ */

 

USE TWO

SELECT  CASE VARIANCE

WHEN 0 THEN 'Matched Transaction'

ELSE 'Unmatched Transaction'

END AS ReconciliationStatus ,

ISNULL(TRXDATE,GLTRXDATE) AS ReconDate,

*

FROM    ( SELECT    CMS.CHEKBKID ,

CMS.ACTINDX ,

CMS.CMTrxNum ,

TRXDATE ,

CASE CMS.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 ,

CMS.TRXAMNT ,

CMS.VOIDED ,

CMS.AUDITTRAIL ,

CMS.SRCDOCNUM ,

CMS.Original_DOCNMBR ,

CMS.CM_TRXSOURCE ,

CMS.Original_DOCTYPE ,

CMS.Original_DOCAMNT ,

CMS.Original_CHEKBKID ,

CMS.Original_DESPOSITNMBR ,

CMS.Original_RCPTNMBR ,

CMS.Original_RCPTAMNT ,

CMS.Original_VOIDED ,

CMS.TRX_STATUS ,

CMS.Original_TRXSOURCE ,

CMS.APPLIEDTODCNM ,

CMS.PMAPLTODOC,

CMS.CashReceiptType ,

GLS.ACTDESCR ,

GLS.ACTNUMST ,

GLS.GLTRXDATE,

GLS.JRNENTRY ,

GLS.DEBITAMT ,

GLS.CRDTAMNT ,

GLS.ORGNTSRC ,

GLS.ORTRXSRC ,

GLS.ORCTRNUM ,

GLS.ORDOCNUM ,

GLS.DEBITAMT - GLS.CRDTAMNT AS GLBalance,

ABS(GLS.DEBITAMT - GLS.CRDTAMNT)

( CASE Original_DOCAMNT

WHEN 0 THEN TRXAMNT

ELSE Original_DOCAMNT

END

                              ) AS Variance

FROM      (

 

SELECT X.CHEKBKID ,

X.CMTrxNum ,

CX.ACTINDX ,

X.CMTrxType ,

X.TRXAMNT ,

X.VOIDED ,

X.TRXDATE ,

X.AUDITTRAIL ,

X.SRCDOCNUM ,

ISNULL(Y.TRX_STATUS, ISNULL(Z.TRX_STATUS, '')) AS TRX_STATUS ,

ISNULL(Y.DOCNUMBR, ISNULL(Z.DOCNUMBR, 0)) AS Original_DOCNMBR ,

ISNULL(Y.TRXSORCE, ISNULL(Z.TRXSORCE, 0)) AS CM_TRXSOURCE ,

ISNULL(Y.RMDTYPAL, ISNULL(Z.DOCTYPE, 0)) AS Original_DOCTYPE ,

ISNULL(Y.ORTRXAMT, ISNULL(Z.DOCAMNT, 0)) AS Original_DOCAMNT ,

--ISNULL(Y.VOIDSTTS,ISNULL(Z.VOIDED,0)) AS Original_DOCNMBR,

ISNULL(Y.MSCSCHID, ISNULL(Z.CHEKBKID, 0)) AS Original_CHEKBKID ,

ISNULL(Y.depositnumber, 0) AS Original_DESPOSITNMBR ,

ISNULL(Y.RCPTNMBR, 0) AS Original_RCPTNMBR ,

ISNULL(Y.RCPTAMT, 0) AS Original_RCPTAMNT ,

ISNULL(Y.APTODCNM, ISNULL(APTVCHNM,'')) AS APPLIEDTODCNM ,

Y.CSHRCTYP AS CashReceiptType ,

ISNULL(Y.VOIDED, ISNULL(Z.VOIDED, 0)) AS Original_VOIDED ,

ISNULL(Z.APTODCNM,'') PMAPLTODOC,

ISNULL(Y.SRCDOCNUM, ISNULL(Z.TRXSORCE, 0)) AS Original_TRXSOURCE

FROM      dbo.CM20200 AS X

LEFT OUTER JOIN CM00100 AS CX ON X.CHEKBKID = CX.CHEKBKID

LEFT OUTER JOIN (

SELECT 

A.DOCNUMBR ,

A.TRX_STATUS ,

A.APTODCNM ,

A.TRXSORCE ,

A.RMDTYPAL ,

A.ORTRXAMT ,

A.SLSAMNT ,

A.VOIDSTTS ,

A.MSCSCHID ,

A.CSHRCTYP ,

B.depositnumber ,

B.RCPTNMBR ,

B.RCPTAMT ,

B.VOIDED ,

B.SRCDOCNUM ,

B.AUDITTRAIL ,

B.CHEKBKID

FROM     

(

SELECT

*

FROM

( SELECT

'Open' AS TRX_STATUS,

CUSTNMBR ,

DOCNUMBR ,

CSHRCTYP ,

TRXSORCE ,

RMDTYPAL ,

ORTRXAMT ,

SLSAMNT ,

VOIDSTTS ,

MSCSCHID

FROM

dbo.RM20101

WHERE

RMDTYPAL = 9

UNION ALL

SELECT

'History' AS TRX_STATUS,

CUSTNMBR ,

DOCNUMBR ,

CSHRCTYP ,

TRXSORCE ,

RMDTYPAL ,

ORTRXAMT ,

SLSAMNT ,

VOIDSTTS ,

MSCSCHID

FROM dbo.RM30101

WHERE RMDTYPAL = 9

) AS RMTRX

LEFT OUTER JOIN

( SELECT

CUSTNMBR AS CUST ,

TRXSORCE AS TRXSRC ,

APTODCNM ,

APFRDCNM

FROM

dbo.RM20201

UNION ALL

SELECT

CUSTNMBR ,

TRXSORCE ,

APTODCNM ,

APFRDCNM

FROM

dbo.RM30201

) AS RMAPL ON RMTRX.CUSTNMBR = RMAPL.CUST

AND RMTRX.DOCNUMBR = RMAPL.APFRDCNM

AND RMTRX.TRXSORCE = RMAPL.TRXSRC

) AS A

LEFT OUTER JOIN dbo.CM20300

AS B ON A.DOCNUMBR = B.RCPTNMBR

AND A.MSCSCHID = B.CHEKBKID

) AS Y ON X.CMTrxNum = Y.depositnumber

AND X.CHEKBKID = Y.CHEKBKID

AND CMTrxType = 1

LEFT OUTER JOIN

( SELECT    TRX_STATUS ,

PMTRX.VENDORID ,

PMTRX.VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED ,

ISNULL(APTVCHNM,'') AS APTVCHNM ,

ISNULL(APTODCNM,'')AS APTODCNM

FROM      (

SELECT

'Open' AS TRX_STATUS ,

VENDORID ,

VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED

FROM

dbo.PM20000

WHERE

DOCTYPE = 6

UNION ALL

SELECT

'History' AS TRX_STATUS ,

VENDORID ,

VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED

FROM

dbo.PM30200

WHERE

DOCTYPE = 6

) AS PMTRX

LEFT OUTER JOIN

( SELECT

VENDORID ,

VCHRNMBR ,

APTVCHNM ,

APTODCNM

FROM

PM10200

UNION ALL

SELECT

VENDORID ,

VCHRNMBR ,

APTVCHNM ,

APTODCNM

FROM

PM30300

) AS PMAPL ON PMTRX.VENDORID = PMAPL.VENDORID

AND PMTRX.VCHRNMBR = PMAPL.VCHRNMBR

) AS Z ON Z.DOCNUMBR = X.CMTrxNum

AND Z.CHEKBKID = X.CHEKBKID

AND Z.VENDORID = X.CMLinkID

AND Z.TRXSORCE = X.AUDITTRAIL

) AS CMS

FULL OUTER JOIN (

SELECT    H.CHEKBKID ,

GLA.ACTDESCR ,

GLM.ACTNUMST ,

GLM.ACTINDX ,

GL.TRXDATE AS GLTRXDATE,

GL.SOURCDOC ,

GL.JRNENTRY ,

GL.DEBITAMT ,

GL.CRDTAMNT ,

GL.ORGNTSRC ,

GL.ORCTRNUM ,

ORTRXSRC ,

GL.ORDOCNUM

FROM      CM00100 AS H

LEFT OUTER JOIN dbo.GL20000 AS GL ON H.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00100 AS GLA ON GLA.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00105 AS GLM ON GLM.ACTINDX = GL.ACTINDX

WHERE     SOURCDOC <> 'BBF'

UNION ALL

SELECT    H.CHEKBKID ,

GLA.ACTDESCR ,

GLM.ACTNUMST ,

GLM.ACTINDX ,

GL.TRXDATE AS GLTRXDATE,

GL.SOURCDOC ,

GL.JRNENTRY ,

GL.DEBITAMT ,

GL.CRDTAMNT ,

GL.ORGNTSRC ,

GL.ORCTRNUM ,

ORTRXSRC ,

GL.ORDOCNUM

FROM      CM00100 AS H

LEFT OUTER JOIN dbo.GL30000 AS GL ON H.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00100 AS GLA ON GLA.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00105 AS GLM ON GLM.ACTINDX = GL.ACTINDX

WHERE     SOURCDOC <> 'BBF'

--WHERE H.CHEKBKID = 'CASHBOOK' --AND ORTRXSRC = 'RMCSH00000002' ORDER BY JRNENTRY

                                     

) AS GLS ON

( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORGNTSRC)

AND CONVERT(VARCHAR(32), CMS.SRCDOCNUM) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND CMS.CMTrxType IN ( 4, 5,6, 7 )

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

AND CMS.CMTrxType IN ( 1 )

AND CONVERT(VARCHAR(32), CMS.Original_DOCNMBR) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType = 1

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.APPLIEDTODCNM) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

OR CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType = 0

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType IS NULL

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CMTrxNum) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND ( CONVERT(VARCHAR(32), CMS.SRCDOCNUM) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

OR CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORGNTSRC)                                                          

)

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CMS.CMTrxType IN ( 4 )

AND CMS.CM_TRXSOURCE IS NOT NULL

)

OR ( GLS.ACTINDX = CMS.ACTINDX

AND CONVERT(VARCHAR(32), GLS.SOURCDOC) = 'GJ'

AND CMS.CHEKBKID IS NULL

AND CMS.CHEKBKID = GLS.CHEKBKID

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND ( CONVERT(VARCHAR(32), CMS.APPLIEDTODCNM) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

AND ( CONVERT(VARCHAR(32), CMS.PMAPLTODOC) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND CMS.CMTrxType IN ( 3 )

                                                    

)

)

)

 

WHERE    

(

   CMS.CHEKBKID = 'PETTY CASH'

OR CMS.CHEKBKID IS NULL

)

AND

(

   GLS.CHEKBKID = 'PETTY CASH'

OR GLS.CHEKBKID IS NULL

)

AND MONTH(ISNULL(TRXDATE,GLTRXDATE)) IN ('04','05')

AND YEAR(ISNULL(TRXDATE,GLTRXDATE)) IN ('2017','2016')

) AS REC

ORDER BY RECONCILIATIONSTATUS

 

Click here to download the CM Reconcile to GL SQL Script.

Best Regards,

Mahmoud M. AlSaadi