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

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


2 comments:

  1. Editing the SP has not corrected the issue in 18.3. The error is occurring the SP aagCreateRecordsinaaGL. What is the solution for 18.3?

    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