In's and Out's of Received Not Invoiced Report, and GL Variance Issue

Kinetic ERP
, , , , , , ,
1

Happy new year to all of you Epi problem solvers out there.

Our end of year seemed to be going very smoothly, so smoothly in fact that I was dubious. Sure enough, not so smooth after all!

As it turns out, we are having an issue where our Received Not Invoiced (RNI) Report as of EOM for period 12 2023 no longer aligns with our RNI GL Account balance. According to accounting, something changed in the month of December which threw our RNI to GL completely off ($$$,$$$). End of Month for November-- apparently things were lining up fine.

We’ve gone down every road we could think of, and what we could find online (this forum, various posts), and have reviewed Epicor’s suggested KB articles. Nothing so far appears to explain the difference we are seeing between the RNI Report and the GL Account Balance.

Where I’m at is: I would like to produce a BAQ dataset that will show us all of the “In’s and Out’s” so to speak, of the December transactions that would have made up the RNI Report each day, with their respective timestamps / dates, and $ amts. This would be reviewed against the current transactions posted in the GL Account to determine if some transactions are “missing” from the GL, which is currently what we think might be happening since we expect the balance of the GL Account to be a larger (negative) number than it is.

  1. I would appreciate any help or input from anyone on this issue, regardless of the BAQ I am trying to produce.

  2. Regarding the BAQ, if anyone has a similar BAQ export they would be willing to share that would be awesome! What I’m thinking is I should be able to use either the PartTran table, or the RcvDtl table to create a sort of historical listing of transactions/events that either would have RAISED the RNI balance during December, or LOWERED the balance.

My understanding so far of the RNI is that it’s essentially just RcvDtl (and dropship) data that is marked Received, and where Invoiced = False. This, in theory, would be enough to go on to produce a table of data showing the “in” (receipt that wasn’t invoiced), and then the “out” (the date/time the receipt was invoiced).

However, what about cases where receipts might have been un-received, or even deleted? I’ve read a bit on other posts where users saw RNI issues or variance in the GL caused by this, but how can you determine (Data-wise) that this is the case? My thought is that there would still be traceable Part Transactions in those cases (is this correct?), so that is where I am going to start by determining the transaction types and other flags that I need to look for.

Thanks so much for reading.

2

Some have found that the System Date differs from the Apply Date and that can cause discrepancies.

2 Likes
3

So I built out a BAQ to try to help paint a picture of what went in and what came out of the RNI report throughout December. I used the GLJrnDtl.PostedDate tied to the AP Invoice record as the “Out Date”, although there are a few cases (unposted invoices) where this record/date is null. We are leaning towards date/timing differences as a main culprit of variance at this point.

4

I just found that the System time is Central, which is 2 hours off from Pacific time which is what the plant is actually set to. I wonder if this 2 hour difference could have contributed to the variance if transactions were done after 10pm (Pacific Time) 12.31.23.

1 Like
5

Dylan, I also found that closing the poheader makes receipts fall off the RNI in 10.2.500… not sure if that’s still the case for you.

EDIT: No idea why the one I did fell off and now I can’t replicate.

1 Like
6

That was part of the variance in our case, but only a small portion. We were not able to fully reconcile the difference though lol.

7

yeah I am sort of in the same boat.

1 Like
8

Sink Or Swim Omg GIF by DefyTV

1 Like
9

The issue is that the report allows you to back date, but the logic in the report is not correct.
Scenario - you run RNI on June 30th at midnight - you get a number.

On July 8th you enter in an invoice dated July 3rd
Run the RNI report back dated to June 30th - the value of the invoice you just entered is not included.

I have created a dashboard in Vantage that I have used since 2009 that allows you to back date - works well.

Epicor should fix the report…

4 Likes
10

@LarsonSolutions Can you share that dashboard? I am digging for 64K from month end and have not found any suspects yet.

2 Likes
11

Me too! Did you take my 19K ?! :rofl:

3 Likes
12

@gpayne @utaylor
Sorry - I can’t put up a product that my company sells on this site.
I can give you the picture of what is looks like and how it works.

Received Not Invoiced Dashboard.pdf (350.6 KB)

2 Likes
13

This was a nice writeup, thank you for the demonstration.

14

Thanks for posting the process.

1 Like
15

@utaylor Well I found mine. It went into the future and not in a DeLorean.

16

Greg, maybe that’s what happened to mine… did you have a query that helped you?

17

We were looking everywhere and unrelated AP was looking for an invoice that was not showing on the aging. When I looked at AP Tracker I noticed the apply date was 7/27/24 and not 6/27/24 and when I ran the aging as of 8/1 it showed up.

I stash a ME copy, so I ran a GL report for all future periods from there and had some transactions in PD 7 and some in PD 8.

In a query I would start with PartTran records with the SysDate in the current period and TranDate in a future period. This assumes that Earliest Apply date is stopping the reverse from happening.

1 Like
18

Thanks Greg!

1 Like