E1: WRKFLW: Frequently Asked Questions on Work Center (Doc ID 626538.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=626538.1
E1: WRKFLW: Frequently Asked Questions on Purging Work Center Messages (Doc ID 1329669.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1329669.1
E1: RDA: How to Disable the UBE Job Completion Message (Doc ID 645319.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=645319.1
E1: UBE: How to Troubleshoot Error Messages in Work Center When There Are No Valid Logs to Analyze (Doc ID 1058052.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=1058052.1
F01131.ZZSERK->F0311M.ZMPSRK->F00165.GDTXKY
------------
**RUN IN PRODUCTION**
------------
R01131P **RUN IN PRODUCTION**
R01131M? Only with matching R01131P date data selection
https://support.oracle.com/epmos/faces/BugDisplay?id=12776607
R98860P (May not be many records)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=626602.1
All records with delta:
USE JDE_PRODUCTION
IF OBJECT_ID('tempdb..#TempCount') IS NOT NULL
DROP TABLE #TempCount
SELECT DISTINCT id=IDENTITY (int, 1,1), GETDATE() 'Date/Time',
(SELECT COUNT(*) FROM jde_production.proddta.F01131M) AS 'F0113M Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131) AS 'F01131 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131T) AS 'F01131T Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01133) AS 'F01133 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F00165) AS 'F00165 Records'
INTO #TempCount
WAITFOR DELAY '00:00:05'
GO
INSERT INTO #TempCount
SELECT GETDATE() 'Date/Time',
(SELECT COUNT(*) FROM jde_production.proddta.F01131M) AS 'F0113M Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131) AS 'F01131 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01131T) AS 'F01131T Records',
(SELECT COUNT(*) FROM jde_production.proddta.F01133) AS 'F01133 Records',
(SELECT COUNT(*) FROM jde_production.proddta.F00165) AS 'F00165 Records'
GO
with v as (select * from #TempCount)
select b.[Date/Time],
b.[F0113M Records], b.[F0113M Records] - a.[F0113M Records] Delta,
b.[F01131 Records], b.[F01131 Records] - a.[F01131 Records] Delta,
b.[F01131T Records], b.[F01131T Records] - a.[F01131T Records] Delta,
b.[F01133 Records], b.[F01133 Records] - a.[F01133 Records] Delta,
b.[F00165 Records], b.[F00165 Records] - a.[F00165 Records] Delta
FROM v a, v b
WHERE a.id = b.id-1
then more than 30 days (should be zero when finished?):
SELECT
(select COUNT (*) 'F0113M' from [JDE_PRODUCTION].[PRODDTA].[F01131M] where ZMDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F0113M Records > 30 Days',
(select COUNT (*) 'F01131' from [JDE_PRODUCTION].[PRODDTA].[F01131] where ZZDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01131 Records > 30 Days',
(select COUNT (*) 'F01131T' from [JDE_PRODUCTION].[PRODDTA].[F01131T] where ZCUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01131T Records > 30 Days',
(select COUNT (*) 'F01133' from [JDE_PRODUCTION].[PRODDTA].[F01133] where ZTDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F01133 Records > 30 Days',
(select COUNT (*) 'F00165' from [JDE_PRODUCTION].[PRODDTA].[F00165] where GDUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)) AS 'F00165 Records > 30 Days'
To show remaining records after purge:
select jde920.dbo.fn_extractdatefromjulian(ZMDTI) as 'Date', * from JDE_PRODUCTION.PRODDTA.F01131M
order by JDE_PRODUCTION.PRODDTA.F01131M.ZMDTI
select jde920.dbo.fn_extractdatefromjulian(ZZDTI) as 'Date', * from jde_production.proddta.F01131
order by ZZDTI desc
select jde920.dbo.fn_extractdatefromjulian(ZCUPMJ) as 'Date', * from jde_production.proddta.F01131T
order by ZCUPMJ desc
select jde920.dbo.fn_extractdatefromjulian(ZTDTI) as 'Date', * from jde_production.proddta.F01133
order by ZTDTI desc
select jde920.dbo.fn_extractdatefromjulian(GDUPMJ) as 'Date', * from jde_production.proddta.F00165
order by GDUPMJ desc
--If records don't delete:
--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131M] where ZMDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END
--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131] where ZZDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END
--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01131T] where ZCUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END
--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F01133] where ZTDTI < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30)
-- IF @@ROWCOUNT < 1 BREAK
--END
-- **Careful with F00165, there's some important stuff in there
--WHILE (1=1)
--BEGIN
-- DELETE TOP(1000) FROM [JDE_PRODUCTION].[PRODDTA].[F00165] where GDUPMJ < (SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())- 30) and GDOBNM = 'GT01131'
-- IF @@ROWCOUNT < 1 BREAK
--END