res 3700 pm_waste_dtl purge?

[cobbler9]

I am trying to clean up our menu items and deleting old items that we no longer sell. I have waited until the last sales of the items have passed our historical date range set for retention and I have been able to delete all items that were never posted as wasted items. I get the following error when trying to delete any item that was posted to a wasted item check.

Key violation.
[Sybase][ODBC Driver]{Adaptive Server Anywhere]Primary key for row in table 'mi_def' is referenced by foreign key
General SQL error.
'CFK2PM_WASTE_DTL' in table 'pm_waste_dtl'

Our historical totals are set for 396 days and when I run a Wasted Item Report with a start date of 11/22/2003 (when we opened) through 11/22/2013 none of the items I am trying to delete show up on the report.

Is there a waste detail that needs to be purged? If so, how do I do that? If not, any ideas of how to delete the items?

 

[Netvoid]

It's probably that the purge routine isn't hitting these tables, the PM purge definition has some items in the PM parameters screen, check that first. If that fails to work out.

This sample query in dbisql will show you the entries that are not allowing you to delete in mi_def.

SELECT * FROM micros.pm_waste_dtl 
WHERE business_date < '2013/01/01'
ORDER BY business_date;

You may want to contact Micros and get support. If you want to clear the tables yourself this will work. You will need to login as DBA or a login that you have defined with rights to do this.

BEGIN TRANSACTION;
DELETE FROM 
    micros.pm_waste_trans_dtl 
WHERE 
    waste_seq IN (SELECT waste_seq FROM micros.pm_waste_dtl WHERE business_date < '2013/01/01');

DELETE FROM 
    micros.pm_waste_trans_dtl 
WHERE 
    business_date < '2013/01/01';
COMMIT;

Good idea to shut the system down to DB level before doing this if you decide to do it.

 

[cobbler9]

Thanks, Netvoid. It looks like we have waste transactions dating back to 2005. This may be related to the fact that we do not use, nor are we licensed for, the PM module. Therefore I cannot even get into the PM parameters section to look at the purge settings. MICROS allows the use of the waste item function and the running of the wasted item reports as standard functionality without the PM license.

Should I be deleting from "micros.pm_waste_dtl" instead of "micros.pm_waste_trans_dtl"? Attempting to delete from "micros.pm_waste_trans_dtl" does not seem to work. I receive two errors, one similar to the previous error of a foreign key, and one that it cannot find the "business_date". I am not currently at the location and do not have the exact errors, sorry.

 

[Netvoid]

Yeah the second one was supposed to be,

DELETE FROM 
    micros.pm_waste_dtl 
WHERE 
    business_date < '2013/01/01';
COMMIT;

Sorry about that...

 

[cobbler9]

No problem, I was sure that was the case so I went ahead and deleted. Solved the problem and I have been able to clean up the menu file.

I'm now working on why the waste_dtl is not being purged automatically.

Thank you very much for the help!!

 

[Wildbar]

I think Micros actually forgot to include these tables into the purging stored procedures....