How to locate invoice date mis-matches between INVOICE and LEDGERTRANS
This article is a sort of investigation tool that was created as a result of a bug present in Build 392 (that has since been fixed). Invoices that were affected by this bug should be rare, but when the problem did occur, it altered the invoice date after the invoice was already posted. So, usually, the invoice date no longer matched the original transaction date that was sent to the GL. Because there is not a direct tie between the invoice and the GL transaction, the problem is more difficult to investigate than it may initially seem.
The following SQL statement will compare invoice detail lines with detail lines in the GL to determine how many matches for the invoice date it has found (for that particular Sales Order). It's not a tied, one-to-one relationship but it's close. The statement will return a column showing the count (for matched dates). Anything 1 and up is probably not affected by the bug mentioned above. Any lines that have a 0 count, though, should be investigated because the invoice detail line is not finding a matching date for that invoice in the GL.
Here's the query statement (it's lengthy and may take a while to run):
select
J.JOBNO,
I.SERNO, I.INVUNINO, I.INVDATE,
ID.EXTPRICE, ID.INVDETLNO,
(select
count(*)
from
LEDGERTRANS LT
where
(LT.SOURCEID = ID.INVDETLNO)
and (LT.TRANSTYPE = 'SalesInvoices')
and (LT.DATETRANS = I.INVDATE)
and (LT.TRANSPOSTED = 'T')
) GL_TRXNS
from
jobs j
join
INVOICE I
on (
(I.JOBNO = J.JOBNO)
and ((I.STATUS is not null) and (I.STATUS in ('Printed', 'Credited')))
and (I.TRIGGERRUN = 'T')
)
join INVDETL ID on ((ID.JOBNO = I.JOBNO) and (ID.SERNO = I.SERNO))
where
(select
JT.CLASSGROUP
from
JOBTYPE JT
where
JT.JOBTYPE = J.JOBTYPE
and JT.CLASSGROUP = 'Sales Orders'
) is not null
order by
J.JOBNO, I.INVUNINO