LASTINVNUM in OEORDH oddities

[kyariexcel]

Hello all!

I have been working on a web report that pulls from our SAGE 300 database and I have run into a weird situation. We have an order 0272122 that shows a LASTINVNUM of 20240201. But, if you go to the last invoice shown in Order Entry, the date shows 20240117. This report pulls all invoices within 01/01 to 01/31 by looking in table OEINVH, but then in the table of my report, pulling OEORDH.LASTINVNUM is showing us this other date for some reason and I can't figure out why. I thought it might be a bug with credit notes, but there are no credit notes on any invoice tied to this order except for the one created on 01/17 alongside the second (and most recent) invoice.

All of my totals seem accurate here in my report, but it's puzzling and a little worrying to me that it's not showing the right date, as if something might be wrong in my query (which is very large with many subqueries).

Any thoughts?

Thanks,
- Cary A.

 

[ettienne]

Something is off in your query

Sage 300 Certifiable Consultant

 

[kyariexcel]

Hello ettienne,

Thank you for your input

We have set up something for us to be able to throw SELECT queries at the database to help us explore it and create reports.

If you could take a look at the following two queries and their results:

Query #1:
SELECT ordnumber, invdate FROM oeinvh WHERE ordnumber = '0272122'

Results:
ordnumber invdate
0272122 20240116
0272122 20240117

-------------------

Query #2:
SELECT ordnumber, invdate FROM oeordh WHERE ordnumber = '0272122'

Results:
ordnumber invdate
0272122 20240201

------------------

I guess my question is, why or how is this possible?

Thanks,
- Cary A.

 

[Anoli]

Do you create shipments from multiple orders?
I believe when multiple orders are shipped together using the "From Multiple Orders" checkmark and the resulting shipment is invoiced the OEINVH table will not contain all the order numbers that were shipped together, it will contain only the first order that is part of the "multiple order" shipment
Instead of OEINVH you need to use the ORDNUMBER field in the OEINVD table which contains all the details of the multiple orders shipped together.
Could it be this is what causes your discrepancy?

 

[kyariexcel]

Hello Anoli,

Thank you for your input

So, without going into too much detail, let me elucidate the purpose of this report so you can understand why I am pulling from OEINVH.

Our sales people are looking for a monthly report containing all invoicing activity on all orders, as well as all credit or debit notes, sorted by order. This means that I must find all invoices for a given order within that date range, as well as all credit and debit notes for a given order within that date range. The totals are established per order and then put in a table that can be sorted by order number, invoice date, invoice number (or credit / debit note #), invoice total, etc.

Regarding the invoice date, I could pull from OEINVH (or OECRDH, depending on which came most recently) but I talked to our salespeople who are using the report and they said they were fine with using the invoice date even if a credit note came later. They can look at a zeroed (or negative) total for that order and peer into it to see the credit note for context if necessary.

Thus, I thought "if OEORDH is collecting the invoice date, I can simply pull from OEORDH.INVDATE as I perform a JOIN from OEORDH to OEINVH (technically it's being JOINed to a query within which OEINVH is being used as a sub query, not really important)" which is where I realized that, on this order, OEORDH.INVDATE doesn't match the latest OEINVH.INVDATE, which leads me to 'why?'

Sorry if this is seeming kind of jumbled, I don't know how comfortable everyone is with analyzing big SQL queries here, or if that was unwelcome.

Thank you to the two of you who have contributed so far!

 

[Anoli]

You did not answer if you ship multiple orders together; I would check if this orders is part of a multiple order shipment to try to explain the discrepancy

If you confirm the discrepancy is caused by the order being a secondary order (part of a shipment that covers multiple orders and the order in question is not the first order in the shipment) then it is time to change the query to read from the details table and compare the order number parameter not with ORDNUMBER field from OEINVH but with ORDNUMBER from OEINVD

My suggestion was based on the discrepancy you described

The bottom line is that if you have invoices/shipments that cover multiple orders you will have an invoices that covers multiple orders
Say one invoice covers order ORD123, ORD124, ORD125
ORDNUMBER field in OEINVH will have a value ORD123
But each detail line on the order will have different values; the first couple of lines on the invoice will have ORD123 because they originated on order ORD123, the next couple will have ORD124 because they originate on that order etc.
In that case your link from OEORDH.ORDNUMBER to OEINVH.ORDNUMBER is incorrect
Take a look at Invoice action report OEINACTS.rpt in Sage, you will see that when you link a shipment to an order by order number you do not link from OESHIH.ORDNUMBER to OEORDH.ORDNUMBER but from OESHID.ORDNUMBER (detail) to OEORDH.ORDNUMBER

Also, the OEORDH.INVDATE field kind of works hand in hand with the OEORDH.LASTINVNUM; meaning that if you ship an order in three separate batches those fields will be updated each time you generate a new invoice for that order

On another hand, you could try to see if other tables might give you easier access to Invoices-Debit Notes-Credit notes by date / order number, quantity , amounts, salesperson
For example OESHDT (I believe it is sales history table) gives you detail line info and contains all invoices, credit note and debit notes and that table has most of the fields you describe: doc #, document date, order number, salesperson
It does not contain taxes

Look at the OE sales history report in Sage. It is based on this OESHDT table (although it is grouped/sorted/totaled differently than what you want) . Might give you an idea if this table is a good candidate for your query.

 

[kyariexcel]

Good morning Anoli,

Thank you for the explanation, it is much appreciated - unfortunately I have had many other things on my plate this week so I have not been able to so much as touch anything regarding this report.

That's kind of how my job is here as the sole help desk and junior IT admin So I will have an opportunity soon where my projects fall off, and I will be able to revisit this. Maybe next week or sooner. I will investigate the sales history table and report you mentioned in Sage and see where that takes me.

Thanks,
- Cary A.