Orders Not Showing in views

[ChrisP520]

Greetings, All:

I am running Progression SQL 7.6.300C on SQL Server 2000.

I'm having a very strange issue. For one particular item, I cannot see the open OE Orders in any view when looking for it by item number. For instance, I cannot see the open orders for this item when I look in Multi Level Item View.

However, the quantity allocated and backordered are all correct. The only orders I can see are B type blanket orders.

When I look at Order View by Item Number, the orders will also not appear. Only B type orders. However, if I check the "Include Closed Orders" checkbox, the open orders and quotes will appear.

The Exact Knowledgebase had an article (Doc ID 00.662.127) that said this might have to do with orphaned OEORDLIN_SQL records. However, I ran a query in EM and found that all of the orders have a matching Order Header.

To further check this out, I placed a new order for this particular item. The allocation increased in Multi Level Item View, but the open order didn't show up when I clicked the Open Cust Orders button. It also didn't show up in Order View by Item Number unless the "Include Closed Orders" checkbox was checked.

This is happening on regular O type orders including O type orders created by OE Blanket Releases.

This is Bizarre. Any help would be appreciated.

Chris

 

[MacolaHelp]

You need to check the oeordhdr against oeordlin and vice versa. Then initialize the oeinqord and load the oe master view again. Does this help?

Did this start after an update, or just out of the blue? Did you link the header and line files by type as well as number? And do you use order header comments, line comments, or item notes?

 

[ChrisP520]

Hi MacolaHelp:

Thanks for the reply. This started out of the blue. When I queried the Header and Line tables I joined on the order number. But I did a left outer join on the order number from the line to header table so that I would see the missing headers. I repeated the process with the left outer join going from the Header to Line table on the order number.

I loaded the OE View Master this morning but I didn't initialize the OEINQORD table. I will do that tonight and let you know what happenes.

We do use Header Comments for UPS shipping information (Starship). We use Line and Item notes but infrequently.

 

[MacolaHelp]

Remember, you need both type and number linked because you can identical numbering sequences in B, M, Q, and O type orders. A long time ago, the oelincmt didn't purge properly, so if you've been on macola for a long time you might have old records in there that are orphans.

Also, if you have a test company... Try to put your item master and location files in there that are current. Initialize the oe files related to the view that is a problem. Reset allocations and on orders. Then trying adding the item to an order. If it works, you have a data problem in your live data. It could be many different things. Do you need to purge closed orders, quotes, etc? Are the status flags messed up on some orders so you only see closed? Did you check the item master and location audit trails for this item in inventory? Maybe someone changed one of the flags. It probably is somewhere in the inventory files since it is only happening with one item, but you never know.

Also, upon rereading your post, the oeinqord affects only the consolidated oe view, so it might not be the way to go. It won't hurt to initialize and reload, but may not help.

 

[ChrisP520]

Good morning, MacolaHelp:

Thank you very much for providing a second set of eyes. The problem turned out to be an orphaned Blanket order. I was concentrating on O type orders.

By joining on both the order number and order type and using a full outer join, I was able to find the missing order header. I simply went into Order Entry and created the header there.

Everything shows up now as it is supposed to.

Again, Thanks.

Chris Poulin

 

[ChrisP520]

Hello Again, MacolaHelp:

Now that I have fixed this issue, I did some looking and found that I have an issue with Orphaned OE Order Headers rather than the other way around. I have 295 Order Headers that do not have associated Order Line records.

The really weird thing here is that for each of these Order Headers, the Order Type is NULL. How do I get rid of these orders? To the best of my knowledge, there is no Purge Function for Open Orders. Can I just use the CLOSE ORDERS application in OE-Processes?

These orders have various statuses. Some show NO status, the majority are Status 1 (Booked Order), there are a few Status 9 (Invoiced Printed) and one Status 1 (Incomplete).

The common thread here, though, is that there are no matching line records and the Order Type is Null.

The query that I ran to determine this is below:

SELECT     OEORDLIN_SQL.ord_type, OEORDHDR_SQL.cus_no, OEORDHDR_SQL.ord_no AS HdrOrdNo, OEORDLIN_SQL.ord_no AS LinOrdNo, 
                      OEORDHDR_SQL.ord_dt, OEORDHDR_SQL.status
FROM         OEORDLIN_SQL OEORDLIN_SQL RIGHT OUTER JOIN
                      OEORDHDR_SQL OEORDHDR_SQL ON OEORDLIN_SQL.ord_type = OEORDHDR_SQL.ord_type AND 
                      OEORDLIN_SQL.ord_no = OEORDHDR_SQL.ord_no
WHERE     (OEORDLIN_SQL.ord_no IS NULL)
ORDER BY OEORDHDR_SQL.status

Thank you.

Chris Poulin

 

[dgillz]

Chris,

I question how these orphans were created in the first place.

Having said that, if you want to get rid of these, change your select query to a delete query.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[MacolaHelp]

There are probably several ways to get the orphaned records, but I think it is usually from an aborted posting process, workstation lockup, etc. You can sometimes pull up these records in the OE enter orders screen and then they will delete themselves. However, if the order type is null, you'll probably need to use the delete query. I always like to use that as a last resort since there is no audit trail if you later have a problem.

I would encourage you to check the oelincmt file for orphans, as well as the options and captured bill file if you use feature or kit items. As a precaution, I would also check all the history files associated with those active order files to be sure that the problem didn't propagate into history after invoices were posted. The order in status 9: is it in history? If so, delete from the order file. I wonder if you have orphaned null header records that may have something to do with blanket order releases that didn't clean up after themselves properly, or a blankets that were fully fulfilled, but the original headers were left behind? Sometimes we don't find these clean up issues until long after because of an update or change to the software program.