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

Tuesday, March 29, 2016

20 Best Dynamics GP Blogs of 2016


It is an honor for me to have Dynamics GP Essentials listed among the 20 Best Dynamics GP Blogs pf 2016, the list includes great and rich Dynamics GP blogs which has been supporting this product forever. 
 

https://www.dynamics101.com/20-best-dynamics-gp-blogs-of-2016/


Best Regards, 
Mahmoud M. AlSaadi

Monday, March 14, 2016

Error while Entering or Posting General Ledger Journals | Subquery Error resulted from the Stored Procedure aagCreateWorkDist



We have seen a frequent error in several environments of our clients, in which the Analytical Accounting module is activated and heavily used. This error interrupts either the data entry or the posting process of General Ledger journal entries. In this post, I am sharing the error root-causes and proper solution in order to completely resolve and prevent this error. 

The error occurred in environment in which the GP version is GP 2013 or later. While entering the Journal Entry, and precisely after entering each line (account, debit and credit amounts), the system responds with an error message stating the following:

"Sub-query returned more than 1 value. This is not permitted when the sub-query follows =,!, <, <=, >, >= or 
when the sub query is used as an expression"

General Ledger - Sub query Error

Another error follows up stating the following:

"The stored procedure aagCreateGLWorkDist returned the following results: DBMS: 512, Microsoft Dynamics GP: 0 "
aagGLCreateGLWorkDist -Stored Procedure

Troubleshooting

Following on the SQL Profiler, several stored procedures were called through the aagCreateGLWorkDist procedure considering several parameters, which are:
  • aagValidateGLBatch
  • aagUpdateGLEntryOpt
  • aagCreateGLWorkSLRealTimePostForApply
  • aagGLAssignUpdate
  • aagGLWorkWorkDistUpdate
  • aagGLWorkHdrUpdate
Practically, the error which results from any of the sub procedures called by aagGLCreateGLWorkDis, will return that the Stored Procedure aagCreateGLWorkDist returned an error, which seems quite puzzling and misleading.Comprehensive follow-up of those stored procedure showed the error results from "aagGLAssignUpdate" specifically, at the end of which there is an embedded SQL statement (@sqlstatement) being executed as part of the stored procedure, as shown below:

-----------------------------------------------------------------------------

select  @sqlstmt = ' select  x.aaglworkhdrid,
                                  x.aaglworkdistid,
                                  y.aasubledgerhdrid,
                                  y.aasubledgerdistid into [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '] from aag10001 x   left outer join aag20001 y 
                     on x.actindx=y.actindx 
                     where x.aaglworkhdrid='
        + convert(varchar(12), @aaglworkhdrid)
        + ' and   y.aasubledgerhdrid =
                 (select aasubledgerhdrid from aag20000
                     where docnumbr='''
        + @orgtrxnumber + ''' ' + @lstr
        + ' and series <> 0) and  
                  ((y.disttype=2 and y.crdtamnt=0) or
                     (y.disttype=1 and y.debitamt=0)
                     or (y.disttype<>2 and y.disttype<>1))
                     order by x.dex_row_id  if (select count(*) from [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '])>0  begin  update aag10003
                 set aatrxcodeid = aag20003.aatrxcodeid from [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '] left outer join aag20003  
                on aag20003.aasubledgerhdrid=[##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aasubledgerhdrid and 
                 aag20003.aasubledgerdistid=[##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aasubledgerdistid   where
                 aag20003.aatrxdimid=aag10003.aatrxdimid and [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aaglworkdistid = aag10003.aaglworkdistid  
                 and  aag10003.aaglworkhdrid='
        + convert(varchar(12), @aaglworkhdrid)
        + '   end   drop table [##temp' + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid) + ']';

exec(@sqlstmt);

----------------------------------------------------------------------------------

The embedded SQL statement within the parameter (@SQL Statement), is shown below:



 SELECT x.aaGLWorkHdrID ,
        x.aaGLWorkDistID ,
        y.aaSubLedgerHdrID ,
        y.aaSubLedgerDistID
 INTO   [##temp10009888]
 FROM   AAG10001 x
        LEFT OUTER JOIN AAG20001 y ON x.ACTINDX = y.ACTINDX
 WHERE  x.aaGLWorkHdrID = @aaGLWorkHdrID
        AND y.aaSubLedgerHdrID = ( SELECT   aaSubLedgerHdrID
                                   FROM     AAG20000
                                   WHERE    DOCNUMBR = ''
                                            AND SERIES <> 0
                                 )
        AND ( ( y.DISTTYPE = 2
                AND y.CRDTAMNT = 0
              )
              OR ( y.DISTTYPE = 1
                   AND y.DEBITAMT = 0
                 )
              OR ( y.DISTTYPE <> 2
                   AND y.DISTTYPE <> 1
                 )
            )
 ORDER BY x.DEX_ROW_ID;
 IF ( SELECT    COUNT(*)
      FROM      [##temp10009888]
    ) > 0
    BEGIN
        UPDATE  AAG10003
        SET     aaTrxCodeID = AAG20003.aaTrxCodeID
        FROM    [##temp10009888]
                LEFT OUTER JOIN AAG20003 ON
                           AAG20003.aaSubLedgerHdrID = [##temp10009888].aaSubLedgerHdrID
                     AND AAG20003.aaSubLedgerDistID = [##temp10009888].aaSubLedgerDistID
        WHERE   AAG20003.aaTrxDimID = AAG10003.aaTrxDimID
                AND [##temp10009888].aaGLWorkDistID = AAG10003.aaGLWorkDistID
                AND AAG10003.aaGLWorkHdrID = @aaGLWorkHdrID;
    END;
 DROP TABLE [##temp10009888]


The cause of the error is that the sub-query in the script above may return more than one value, as illustrated below:

The cause of sub query error




Here are the corrupted records within the AAG20000. Therefore, retrieving all records with empty document number may retrieve more than one line, and therefore, the error occurs. 


Corrupted records within AAG20000


Resolution

In order to resolve this error, it is required to modify the stored procedure "aagGLAssignUpdate". Alter the procedure to include a (TOP 1) statement within the sub-query so that only one record is retrieved. Here is the part to be modified:


-----------------------------------------------------------------------------


select  @sqlstmt = ' select  x.aaglworkhdrid,
                             x.aaglworkdistid,
                            y.aasubledgerhdrid,
                             y.aasubledgerdistid
          into [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '] from aag10001 x   left outer join aag20001 y 
                     on x.actindx=y.actindx 
                     where x.aaglworkhdrid='
        + convert(varchar(12), @aaglworkhdrid)
        + ' and   y.aasubledgerhdrid =
      
                     (select TOP 1 aasubledgerhdrid from aag20000
                     where docnumbr='''


        + @orgtrxnumber + ''' ' + @lstr
        + ' and series <> 0) and  
                  ((y.disttype=2 and y.crdtamnt=0) or
                     (y.disttype=1 and y.debitamt=0)
                     or (y.disttype<>2 and y.disttype<>1))
                     order by x.dex_row_id  if (select count(*) from [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '])>0  begin  update aag10003
                 set aatrxcodeid = aag20003.aatrxcodeid from [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '] left outer join aag20003  
                on aag20003.aasubledgerhdrid=[##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aasubledgerhdrid and 
                 aag20003.aasubledgerdistid=[##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aasubledgerdistid   where
                 aag20003.aatrxdimid=aag10003.aatrxdimid and [##temp'
        + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid)
        + '].aaglworkdistid = aag10003.aaglworkdistid  
                 and  aag10003.aaglworkhdrid='
        + convert(varchar(12), @aaglworkhdrid)
        + '   end   drop table [##temp' + rtrim(@companyid)
        + convert(varchar(12), @sqlsessionid) + ']';
exec(@sqlstmt);
----------------------------------------------------------------------------------



Best Regards, 
Mahmoud M. AlSaadi