| Description |
This issue relates to i21 Help Desk ticket. Click the link below to go to Help Desk ticket.
[HDTN-234198 - Open Clearing Detail report gives inaccurate results|https://helpdesk.irely.com/iRelyi21live//#/HD/Ticket/?ticket=HDTN-234198]
Use this query to determine all the settle storage transactions that have differences between Report and GL.
[Storage_ClearingDiff.sql|http://jira.irelyserver.com/secure/attachment/61829/Storage_ClearingDiff.sql]
The query will have this format result.
!pastedImage_d149045_0.png|height=109,width=746!
Here's how the initial investigation to do.
Example on *STR-3174/1* that has a difference of _250_ in *GL* but in *Report* it is _0_.
We can use the query below to check the report why it is 0 difference.
{noformat}SELECT * FROM vyuAPGrainClearing WHERE strTransactionNumber = 'STR-3174/1'{noformat}
Here's the result of the query.
!pastedImage_d149045_1.png|height=118,width=746!
If we sum the result by *strTransactionNumber* and calculate the clearing amount by *dblSettleStorageAmount - dblVoucherTotal* the result is 0.
From the result, we can also see the transaction numbers we need to query on GL and check the AP Clearing.
The transactions are *STR-3174/1* and *BL-27234* .
By these transaction numbers, we can check why there is a difference in GL for the amount of *250*
We can use this query in GL to find out the difference.
{noformat}SELECT A.*
FROM tblGLDetail A
INNER JOIN vyuGLAccountDetail B ON A.intAccountId = B.intAccountId
WHERE
B.intAccountCategoryId = 45
AND A.ysnIsUnposted = 0
AND A.strTransactionId = 'STR-3174/1'
SELECT A.*
FROM tblGLDetail A
INNER JOIN vyuGLAccountDetail B ON A.intAccountId = B.intAccountId
WHERE
B.intAccountCategoryId = 45
AND A.ysnIsUnposted = 0
AND A.strTransactionId = 'BL-27234'{noformat}
!pastedImage_d149045_2.png|height=283,width=746!
From the result of the query, we can see why there is a difference. It is because, in the voucher, there is an AP Clearing GL entry for 250 *but not exists in settle storage GL entries* .
With this issue Grain Dev can start creating generic data fixes and run it to see the remaining difference.
The goal is the Storage_ClearingDiff.sql should have no result.
|