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
-----------------------------------------------------------------------------
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);
----------------------------------------------------------------------------------
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
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?
ReplyDeleteBE 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