AP Received not invoiced -

Epicor ERP 9
1

I am trying to reconcile the received not invoiced account for AP.

I usually created a query where the Invoice Date is in 2017 but, the goods were received in 2018.

I have one invoice - where when it was received the extended price was 1,160.

When the Invoice was matched - 1,310 was relieved from the account. It included a miscellaneous charge of 150.

Usually these miscellaneous charges go to the account that is setup for the charge.

If fact, the second line on the invoice, the miscellaneous charge did go to the correct account.

I am trying to find more transactions - GLJrnDet table has the AP Invoice but not the AP invoice line.

Has anyone created a query to match the journal detail to the AP invoice?

2

Hi Cathy,

I’m assuming you have sql backend, if not the table links are the same. Change YOURDATABASE to your actual database and YourCompany to your actual company and enter the gl account details and period as required. I hope this helps. Essentially you need to join the GLJrnDtl to the TranGLC.

SELECT GLPeriodBal.FiscalYear, GLPeriodBal.FiscalPeriod, GLPeriodBal.SegValue1, GLPeriodBal.SegValue3, GLPeriodBal.SegValue2,
GLPeriodBal.DebitAmt, GLPeriodBal.CreditAmt, GLJrnDtl.JournalNum, GLJrnDtl.JournalLine, GLJrnDtl.Description, GLJrnDtl.JEDate, GLJrnDtl.PostedBy, GLJrnDtl.PostedDate, GLJrnDtl.APInvoiceNum, GLJrnDtl.DebitAmount, GLJrnDtl.CreditAmount, GLJrnDtl.BookDebitAmount, GLJrnDtl.BookCreditAmount, TranGLC.RelatedToFile, TranGLC.Key1 as ‘Vendor #’, TranGLC.Key2 as ‘Inv #’, TranGLC.Key3 as ‘Inv Line’

FROM YOURDATABASE.Erp.TranGLC RIGHT OUTER JOIN YOURDATABASE.Erp.GLJrnDtl ON TranGLC.Company = GLJrnDtl.Company AND TranGLC.BookID = GLJrnDtl.BookID AND TranGLC.FiscalYear = GLJrnDtl.FiscalYear AND TranGLC.FiscalYearSuffix = GLJrnDtl.FiscalYearSuffix AND TranGLC.JournalCode = GLJrnDtl.JournalCode AND TranGLC.JournalNum = GLJrnDtl.JournalNum AND TranGLC.JournalLine = GLJrnDtl.JournalLine RIGHT OUTER JOIN YOURDATABASE.Erp.GLPeriodBal ON GLJrnDtl.Company = GLPeriodBal.Company AND GLJrnDtl.BookID = GLPeriodBal.BookID AND GLJrnDtl.SegValue1 = GLPeriodBal.SegValue1 AND GLJrnDtl.SegValue2 = GLPeriodBal.SegValue2 AND GLJrnDtl.SegValue3 = GLPeriodBal.SegValue3 AND GLJrnDtl.FiscalYear = GLPeriodBal.FiscalYear AND GLJrnDtl.FiscalYearSuffix = GLPeriodBal.FiscalYearSuffix AND GLJrnDtl.FiscalPeriod = GLPeriodBal.FiscalPeriod

where GLPeriodBal.company = ‘YourCompany’ and GLPeriodBal.SegValue1 = ‘xxxx’ and GLPeriodBal.SegValue2 = ‘xx’ and GLPeriodBal.SegValue3 = ‘xxx’ and GLPeriodBal.FiscalYear = ‘2018’ and GLPeriodBal.FiscalPeriod >= ‘8’ and GLPeriodBal.FiscalPeriod <= ‘8’ and TranGLC.RelatedToFile = ‘APInvExp’

cheers,
Paul;

3

Cathy,

I think you want to use the TranGLC table to support your needs here. The RelatedToFile entry you are looking for is APInvExp. You will use the other fields labeled Key1-4 to support your links back to the AP invc table.

Hope this helps!

Josh

4

When I post I will try to remember to remember that we have progress.

Looks like TranGLC to AP tables will work - Thank you