PO Status H 1

[ChrisP520]

Greetings:

I am running Progression 7.6.300C on MSSQL 2000.

My PO system is a mess. There are open orders showing back to our start with Macola in 1995. This causes all sorts of issues that are too numerous to list as I'm sure all here know.

What I would like to do is close those orders using the H status flag in the PO Order Header. My question is, can this be done in a mass operation using an Update query? Or does it have to be done through the Macola GUI?

My fear is that the process does more than simply change a flag on the PO Order Header. Can someone confirm if this is doable through an Update Query?

Regards,

Christopher Poulin

 

[ChrisP520]

Hello, again.

Can anyone weigh in on this? My ulitmate goal is to make the orders that are being switched to a status of H disappear from the PO Cash Requirements report and at the same time allow the PO to be viewed by Item Number in PO Views.

I did a test in a test company where I ran a scrip that change a single order to a status of H and my needs were met.

However, I would like to confirm that some other table updates will not go undone if I take this approach.

Any help would be appreciated.

Chris Poulin

 

[dgillz]

I don't think there is an issue. Here is a snippet cut and pasted from Exacts website:

With the release of Progression 7.6.300a Purchase Orders will have a new status available, H = History. This enhancement enables the reset on order process to ignore any items that have not been fully received on purchase orders with the status of H = History. The reset on order will reduce the on order quantity while allowing visibility through purchase order view to the order and items. Previously, there was no way to mark an order so that it would not be used in the on order calculations and still have visibility through purchase order view. When an order was closed it had to be purged to get the on order to decrease but the purge process removed the purchase order from the PO header and line tables so it could no longer be seen using view in purchase order.

Unlike a purchase order that’s set to C = Closed a purchase order set to a status of H = History can not have the status changed again. In addition, it’s important to note that simply changing the status to H = History does not automatically reduce the quantity on order of any lines on the purchase order that have not been fully received, the reset on order process has to be run for the on order to be reduced. Providing the on order quantity was not previously reduced by the reset on order process, when the purchase order is purged to history (removed from the PO header and line tables) the on order for any items not fully received will be reduced.

The entire document can be founfd on the Exact portal at 08.316.123.



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]

You will have many issues doing this externally with only this update query. Better to do it in the macola user interface, print the POs, which will update QOO, etc, write the trx records, allow you to close and purge, etc. Sounds like you are overdue for major data cleanup. Unless you know all the tables affected by doing a generic macola process and which fields are affected, using queries like this will make such a mess of your data that you will not be able to properly clean it up when you finally decide to do it. I've been doing this for almost 20 years, and I find I have to test the dickens out of such a venture before I'm willing to do it on live data. You may have no idea what other reports and processes would be affected by running one simple query. Just off the top of my head, what did you do about the POLINHST, POORDHST, POORDLIN, IMRECHST, IMINVTRX, received not invoiced report, purchasing line history, etc..... You get my drift?

 

[ChrisP520]

Hello MacolaHelp:

Thank you for the reply. You post pretty much addresses my fears. I DON'T know what other tables and reports this change would hit and I wanted to make sure that I didn't irrevocably trash my live data.

My hope was that someone DID know for sure if this would work well or not. In the face of uncertainty, my plan is to do this one at a time through the PO module.

Again, thanks.

Chris Poulin

 

[dgillz]

The document I suggested covers this pretty well, but just to put it to the test I changes a PO to status H and ran SQL delta gainst it. SQL Delta is a utility that (amongst other things) takes a before and after snapshot of a SQL data base and tells you what changed.

SQL Profiler can do this, but SQL Delta is a lot easier to use.

Here are my findings:

POORDHDR_SQL.Status is changed to "H"
POORDHDR_SQL.Updt_in_progress is changed to <<Null>> from "N"

Thats it. So I do not believe this poses a threat at all. Let me know if you have any questions.

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

 

[ChrisP520]

Thank you, Don. That's good news.

 

[MacolaHelp]

Don, have you used SQL delta often enough to feel confident that it tracks everything? If so, it sounds like something everyone should have in their macola tool kit.

 

[dgillz]

I've been using it for about 3 years now. I'd be helpless without it.

It has one weakness versus SQL Profiler that I can see (especially in an ES environment). In Profiler I can see various stored procedures as the execute in ES. I cannot do this with SQL Delta.

There are other tools like this. Scott Pickens at Exact has a tool he uses that is cheaper. We discussed it briefly via email once. So I think a tool like this is a must, but I would check out the market, especially since it has been 3 years.

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

 

[tpllc]

AFter you do what Don suggested don't forget to run the reset quantity on Order process in IM and select the reset transaction box

Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.