Macola history report missing data

[csge]

Hello all,

I'm running macola progression on pervasive sql. When we run history repots, there is data missing from random customers on random dates.

Is there a way to tell if this information is still there and fix it, or should I restore from backup ? ?

note: One user had her hard drive fill up and often locked up while running history reports. I don't know if the missing data is the data she was working with when she was locking up.

Thanks for your help.

Jamie

 

[crystalreporting]

It's highly unlikely that a local workstation's hard drive 'filling up' would not cause the problem you describe - particularly if she was simply running reports.

What data is 'missing'?

Peter Shirley

http://www.linkcorp.com

 

[csge]

When checking customer purchase history, some customers are mising all 2003 and 2004 data. Other are just missing a month or two of purchase hisory.

I do think it is unlikely that this information was deleted.

I was thinking maybe this data was somehow locked or corrupted, and hoping there may be some utility I could run to reindex or check the integrity of our database.

Thanks again for your help
Jamie

 

[kscheeler]

If you are looking for sales history for a customer, there are two places in Macola it's stored. The order header history (OEHDRHST) and order line history (OELINHST) table holds the detailed information for the invoices. You can view them in Macola by going to order entry | view | order history. For reports, you can go to order entry | reports/graphs | sales history | by invoice date by customer. This reports reads the data from the two files listed above.

There is also a summary table that you post to after posting invoices. The posting function is in order entry | processess | sales history | post sales history trx. You choose the date range to post and then what period and year you want to post to. The table that this is stored in is (OESLSHST). The reports that read this data is under order entry | reports/graphs | sales comparisons.

Based on the reports you've been running will tell you which sets of tables the information is in. You can also write a crystal report against the tables that I've been listing to see if it's an issue with the reports or if the information just isn't there.

Kevin Scheeler

 

[dgillz]

Kevin made a great point. If you do not post your sales history transactions at the end of every month, there will NEVER be any data in this table (OESLSHST_SQL), and what you are experiencing can be the result of your own procedures.

To determine if this is the case, query the OEHDRHST_SQL.Trx_posted_fg for nulls. If you have nulls in these records from the dates you mentioned above, you can be pretty sure no one ever posted sales history transactions. If you cannot find ny transactions with those dates, then someone has purged OEHDRHST and OELINHST.

Please post your findings and hopefully we can get this solved for you.

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

http://www.gainfocus.biz

 

[MacolaHelp]

You should also be able to look in PCC or with a query tool such as msquery into excel, crystal, ms access, etc. to see if the records you think are missing are actually in the history files. If they are, you may just need to rebuild the files you've got.

 

[csge]

Hi Guys,

Thanks again for your help.

I have looked in the OEHDRHST and found records that match what we are missing.

I guess doing a rebuild of the database is the next step.

I'm still not sure if this will actually fix the problem as there seems to be no common date or event that I can say definitively caused the problem in the first place.

Anyway...rebuild it is...

wish me luck

Jamie

 

[MacolaHelp]

be sure to do oehdrhst, oelinhst, oecmthst. When done, you'll want to load oe history view under processes, as this file in the combined order header & history files. I've seen this problem in sales history when the files get large & you start running out of room on servers or running big reports on underpowered workstations, or from lighting strikes or electrical events. You will want to do a mismatch query of some type to be sure all headers have lines & vice versa & that all comments are linked to valid headers and lines. I've also seen odd things happen when there is a lot of history and the next order number field is adjusted, or when quotes and orders begin to overlap. In addition, you'll need to build the oepdshdr if you are running 76300 or >.

 

[dgillz]

Rebuilding is not he answer, IMHO. Please see my earlier post about the trx_posted_fg to see if these have ever been posted to Sales History.

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

http://www.gainfocus.biz

 

[kscheeler]

I guess it goes back to our original question. WHICH
report(s) are you trying to run. (ie: Order Entry / Reports and Graphs / Sales History / By Invoice Date by Customer

Kevin Scheeler

 

[csge]

example:

In Macola, when viewing sales history for company A, the data stops in November 2004. There is not data shown for 2005 at all.

I opened the OEHDHST_SQL table in PCC and did see an entry for Company A from this year.

Does this not prove the info is there, but just not being read by Macola?

Please pardon my ingnorance with databases in general. I'm probably not the best one in the world to be doing this to begin with, but here I am.

Is there more I should do before I go for the rebuild, or are there any "gotchas" a newbie like myself should know about before I begin?

Thanks again for your help.

 

[dgillz]

The table you want to look at is the OEHDRHST table. Check 2 things, in this order:

1) Confirm that there are records in the OEHDRHST_SQL table with invoice dates that are newer than Nov 2004. Note: If these do not exist, you are SOL - your only choice is to manually enter Sales History from OE, Processes, Sales History, Sales History Load.

2) If the records in the OEHDRHST_SQL table are there, confirm the the trx_posted_fg is blank for the records in question (invoice date newer than Nov 2004). If it is blank, then all you need to do is go to OE, Processes, Sales History, Post Sales History Trx, one month at a time, until the OESLSHST_SQL table is fully populated.

This is normally a part of the month end processes, but if no one did it for a period of time, of course the records will not exist in the OESLSHST_SQL table. It is fairly likely this is your situation, and there is nothing wrong with Macola or any of its data tables at all.

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

http://www.gainfocus.biz

 

[kscheeler]

WHAT menu choice are you using to try to view/report on sales history.

Kevin Scheeler

 

[csge]

To view the sales history, we choose:
1. order entry
2. view
3. order history
4. by customer

When I check oehdrhst and oehdrhst_sql, both show data that is missing in the report. The trx_posted_fg field in these rows have a Y.

Thanks again for your patience and advice.

Jamie

 

[MacolaHelp]

Jamie is on pervasive, she has seen the missing data in pcc, therefore she needs to rebuild the files first (please make a backup copy first of oehdrhst, oelinhst, oecmthst), then reload the oe consolidated view & the power inquiry view if > 76300. There are a number of reasons this can go whacko including electrical events. The sales history summary file (oeslshst) does not appear to be the problem here, as that is only used in reporting functions, not in generic macola view functions. If the order numbering or invoice numbering were duplicated, we might also have a problem.

 

[csge]

reload the oe consolidated view ?

power inquiry view if >76300 ?

Sorry... 100% newbie here

 

[MacolaHelp]

see my Jun 9 post where I tell you what files to rebuild & how to reload the consolidated oe view under processes. To rebuild the peak power inquiry view, you go to system manager, initialize distribution, oe, page 3 oepdshdr file. You need to have everyone out of distribution when you rebuild the history files, otherwise you will get 0 records recovered. Be sure to make a backup first & I'm talking on a hard drive, not a tape!! You also need to identify if you have a mismatch of order header & line history records & remove the offending components if so.

Here's another stupid thing: did you realize if there are more than 150 records to display, that the arrows are activated above the search window to show you additional screens of info? And, if you run a report, oe\reports\sales history\by invoice date/customer, do you get the records for 2004 if you select 01/01/90 to present?

 

[csge]

I will most likely give the rebuild a whirl this weekend. I was thinking it would be done through pervasive, but from your posts it sounds like this can be done just as well through Macola.

How should i go about identifying mismatch information?
I'm assuming I'll need printed records to reference?
Or would it be obvious?

And if I do find that it is hosed, how do I edit the records?

Thanks for holding my hand here. I'm a little freaked out by this whole thing and expect it will be less than graceful.

All ideas and detail are greatly appreciated.

 

[SusanD5320]

In reviewing our Sales History files, we have several periods of data that were never posted. While checking posted vs. not posted data I discovered that we have data that I think is posted in the wrong Sales History period.

Our fiscal year runs April thru May. So data for calendar month August 2000 would be period 4, fiscal year 2001. Checking the OEHDRHST table, there are no records posted for that period. However, when I run the Sales Comparison report for that period, it shows several thousand $$ of sales posted. I checked one of the customers and the sales were actually invoiced in October. I ran the Order Audit on the orders in question and they were added in October.

How do I get the sales into the correct Sales History periods?

Also, should I post the unposted data or wait until we get the wrong data fixed before I post anything else?

 

[dgillz]

Susan,

How can a fiscal year run April thru May? Thats 13 months.

Please clarify.

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

http://www.gainfocus.biz