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.
Best Regards,
Mahmoud M. AlSaadi
Tuesday, March 29, 2016
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
-----------------------------------------------------------------------------
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
Subscribe to:
Posts (Atom)