We have a meeting set up this week to review with the Finance team to get more details of scenarios when this happens to try to duplicate the issue. I tried multiple scenarios to duplicate the issue but could not replicate it. I tried running the SQL query below to narrow down specific scenarios but could not filter it to only Advanced Billing shipment invoices with zero dollar. The query brings back all zero dollar invoices with no GL accounts so it will take some time to go through all of them to find the advance billing invoices with no GL accounts. If anyone could help modify the sql query to filter for advanced billing with Null GL accounts, that would help with our testing scenarios.
select invchead.invoicenum, gljrndtl.journalnum, invchead.docinvoiceamt, invchead.applydate,
from invchead full outer join gljrndtl on invchead.invoicenum = gljrndtl.arinvoicenum
where gljrndtl.journalnum is NULL and invchead.applydate > ‘2018’ order by invchead.invoicenum
At first, I did notice that zero dollar invoices do not have GL accounts so I was wondering if these zero dollar advance billing shipment invoices fall into the same category as zero dollar invoices, thus no GL accounts would be created in both situations…?
select
i.InvoiceNum,
i.InvoiceDate,
g.JournalNum,
g.Description,
g.GLAccount,
g.SegValue1,
i.DocInvoiceAmt,
i.ApplyDate
from invchead i left outer join gljrndtl g on i.invoicenum = g.arinvoicenum
where i.FiscalYear = 2018 and g.GLAccount is NULL
order by i.invoicenum desc
Thanks for the query. I’m still trying to figure out a way to only bring in those shipment invoices that have a less advanced billed with a total of zero dollar. It looks like these are calculated fields on the header level. The query works but it also brings in all shipment invoices with zero dollar invoices. Trying to filter it to only shipment invoices with advanced billings.
I don’t have any invoices in my database to work with, but I think the following query should do the trick.
It lists (left table) ONLY Shipment Invoices that have an associated Advance Billing invoice. This relationship is made via the InvcHead.OrderNum field since both the shipment and the advance bill invoice would reference the sales order.
select
i.InvoiceNum,
i.InvoiceType,
i.InvoiceDate,
i.OrderNum,
i2.InvoiceNum,
i2.InvoiceType,
g.JournalNum,
g.Description,
g.GLAccount,
g.SegValue1,
i.DocInvoiceAmt,
i.ApplyDate
from invchead i inner join invchead i2 on i.company = i2.company and i.OrderNum = i2.OrderNum
left outer join gljrndtl g on i.invoicenum = g.arinvoicenum
where i.FiscalYear = '2018' and i.InvoiceType = 'SHP' and i2.InvoiceType = 'ADV'
order by i.invoicenum desc
Thanks! We are reviewing those invoices to see if there is a pattern as to why no GL accounts are being pulled in. Is the expected behavior that zero dollar advanced billed shipment invoices do not have GL accounts? Trying to find why there is a discrepancy based on the expected behavior. The only difference I can see between the highlighted and non-highlighted invoices is that the highlighted invoices have a Subtotal and Less Adv Billed amount but the invoice balance are all zero’s.
I’m afraid I don’t know enough about the Financial Module setup to be able to assist on why the GL isn’t being updated by these transactions.
I would run a few tests in your test environment, previewing edit lists, with each invoice just to see what GL impact the tests will have. I may also do this as I’m curious about what could be going on with your system versus what SHOULD be happening.
We don’t use advance billing invoices. I’ll probably create some, though, when time permits - in my test environment. I’d really like to figure out what’s supposed to happen. My Finance department may want to use this functionality one day and I want to be prepared.
Can’t say I’ve ever seen that, but I don’t generally perform these Finance functions.
It sounds like maybe there are no defined accounts for these transactions, so the Edit list doesn’t show anything. When posted, the transactions have to post somewhere - perhaps defaults? I don’t know.
Did the transactions post to the correct GL accounts?
Yes, the GL accounts did post correctly. How much of a lag time would you expect from the time you hit the Post to when the GL accounts gets posted to? Is this a fairly quick process for Posting?
