Purge Bulk Work Center using SQL :-
Purging WorkCenter Message first time is too time consuming as there are millions of record. Use below select statement to count records and then using delete purge them.
Note: -The Order of SQL is important, because data are inter-dependent on each other.
SELECT COUNT(*) from PRODDTA.F00166 where GTOBNM='GT01131'
AND GTTXKY IN (SELECT ZZSERK from PRODDTA.F01131 where ZZDTI lt 113170);
SELECT COUNT(*) from PRODDTA.F00165 where GDOBNM='GT01131'
AND GDTXKY IN (SELECT ZZSERK from PRODDTA.F01131 where ZZDTI lt 113170);
SELECT COUNT(*) from proddta.F01131T where
ZCSERK in (SELECT zzserk)from PRODDTA.F01131 where ZZDTI lt 113170);
SELECT COUNT(*) from PRODDTA.F01131M where ZMDTI lt 113170;
SELECT COUNT(*) from proddta.F01133 where
ZTSERK in (SELECT zzserk from PRODDTA.F01131 where ZZDTI lt 113170);
SELECT COUNT(*) from PRODDTA.F01131 where ZZDTI lt 113170;
Clear Orphaned Record R01131M with Data Selection on ZMDTI
Now do your regular maintenance to Purge Work Center message with R01131P
Question 1: In what tables are the Work Center Messages stored?
Answer 1: The Work Center is the hub of all messages within EnterpriseOne. Work Center messages are stored in the following tables:
- F01131 PPAT Message Control File
- F01133 PPAT Message Detail File
- F01131M JDEM Multi Level Message
- F01131T Message Attachment Tag File
- F00165 Media Object Storage (identified under GT01131)
- F00166 Media Object Categories (identified under GT01131)
