• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

SQL to identify list of Custom, Modified and Cloned Objects for JDE Upgrade

We are looking to upgrade from JDE 9.1 to JDE 9.2
Wanted to find out if any of you had a SQL to identify the list of Custom, Modified and Cloned Objects.
Any help would be greatly appreciated
 

Gopal_Kistasami

Active Member
Hi Joshua,

Chapter 2 in the upgrade manual does all this for you. You need to do this in the source 9.1 environment. I hope this answers your question.
 
Hi Gopal,

Thanks for your response. I could not find any SQLs for this in the Upgrade Manual. I was just looking to get an object listing.

Regards,
Joshua
 

brother_of_karamazov

Legendary Poster
We are looking to upgrade from JDE 9.1 to JDE 9.2
Wanted to find out if any of you had a SQL to identify the list of Custom, Modified and Cloned Objects.
Any help would be greatly appreciated
Some stuff I've slopped together over the years:


--Objects Modified in PD not by OMW promotion
use JDE920
select OL920.F9861.SIPATHCD as "Path Code", OL920.F9861.SIOBNM as "Object Number", OL920.F9860.SIMD as "Object Name",
OL920.F9861.SIMKEY as "Workstation", OL920.F9861.SIUSER as "User", OL920.F9861.SIMRGMOD as "Mod Type",
DATEADD(ss,CONVERT(int,right(OL920.F9861.SIUPMT,2)),
DATEADD(mi,CONVERT(int,left(right(OL920.F9861.SIUPMT,4),2)),
DATEADD(hh,CONVERT(int,left(OL920.F9861.SIUPMT,len(OL920.F9861.SIUPMT)-4)),
DATEADD( dd, CONVERT(int,OL920.F9861.SIUPMJ) % 1000,
CONVERT(datetime, '12/31/' + CONVERT(varchar, left((OL920.F9861.SIUPMJ/ 1000 + 1899),4) )))))) as "Date Modified"
FROM OL920.F9861
JOIN OL920.F9860 on OL920.F9861.SIOBNM=OL920.F9860.SIOBNM
where SIMRGMOD in ('C', 'A', 'D')
and SIPATHCD = 'PD920'
and OL920.F9861.SIOBNM NOT IN
(SELECT POOMWOBJID FROM JDE920.SY920.F98222 WHERE POOMWPRJID IN
(SELECT LHOMWPRJID FROM JDE920.SY920.F98210 WHERE LHOMWAC='38'))


ORDER BY 7 DESC




--Objects Modified
use JDE920
select OL920.F9861.SIPATHCD as "Path Code", OL920.F9861.SIOBNM as "Object Number", OL920.F9860.SIMD as "Object Name",
OL920.F9861.SIMKEY as "Workstation", OL920.F9861.SIUSER as "User", OL920.F9861.SIMRGMOD as "Mod Type",
DATEADD(ss,CONVERT(int,right(OL920.F9861.SIUPMT,2)),
DATEADD(mi,CONVERT(int,left(right(OL920.F9861.SIUPMT,4),2)),
DATEADD(hh,CONVERT(int,left(OL920.F9861.SIUPMT,len(OL920.F9861.SIUPMT)-4)),
DATEADD( dd, CONVERT(int,OL920.F9861.SIUPMJ) % 1000,
CONVERT(datetime, '12/31/' + CONVERT(varchar, left((OL920.F9861.SIUPMJ/ 1000 + 1899),4) )))))) as "Date Modified"
FROM OL920.F9861
JOIN OL920.F9860 on OL920.F9861.SIOBNM=OL920.F9860.SIOBNM
where SIMRGMOD in ('C', 'A', 'D')
ORDER BY 1, 2




--Versions Modified since 2003 in PY812 not including those by user 'JDE' (May eliminate ESU's)
use JDE_PY812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)", VRUSER as 'User' from PY812.F983051
where VRVCD > 103000
and VRUSER ! = 'JDE'
order by VRPID, VRVERS


--Versions Modified since 2003 in DV812 not including those by user 'JDE' (May eliminate ESU's)
use JDE_DV812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)", VRUSER as 'User' from DV812.F983051
where VRVCD > 103000
and VRUSER ! = 'JDE'
order by VRPID, VRVERS


--Versions Modified since 2003 in PD812 not including those by user 'JDE' (May eliminate ESU's)
use JDE_PD812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)", VRUSER as 'User' from PD812.F983051
where VRVCD > 103000
and VRUSER ! = 'JDE'
order by VRPID, VRVERS






--Includes JDE user modified objects


--Objects Modified
use JDE920
select OL920.F9861.SIPATHCD as "Path Code", OL920.F9861.SIOBNM as "Object Number", OL920.F9860.SIMD as "Object Name",
OL920.F9861.SIMKEY as "Workstation", OL920.F9861.SIUSER as "User", OL920.F9861.SIMRGMOD as "Mod Type",
DATEADD(ss,CONVERT(int,right(OL920.F9861.SIUPMT,2)),
DATEADD(mi,CONVERT(int,left(right(OL920.F9861.SIUPMT,4),2)),
DATEADD(hh,CONVERT(int,left(OL920.F9861.SIUPMT,len(OL920.F9861.SIUPMT)-4)),
DATEADD( dd, CONVERT(int,OL920.F9861.SIUPMJ) % 1000,
CONVERT(datetime, '12/31/' + CONVERT(varchar, left((OL920.F9861.SIUPMJ/ 1000 + 1899),4) )))))) as "Date Modified"
FROM OL920.F9861
JOIN OL920.F9860 on OL920.F9861.SIOBNM=OL920.F9860.SIOBNM
where SIMRGMOD in ('C', 'A', 'D')
ORDER BY 1, 2




--Versions Modified since 2003 in PY812
use JDE_PY812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)" from PY812.F983051 where VRVCD > 103000
order by VRPID, VRVERS


--Versions Modified since 2003 in DV812
use JDE_DV812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)" from DV812.F983051 where VRVCD > 103000
order by VRPID, VRVERS


--Versions Modified since 2003 in PD812
use JDE_PD812
select VRPID as "Object Name", VRVERS as "Version Number", VRJD as "Version Name", VRVCD as "Modified Date (E1 Julian)" from PD812.F983051 where VRVCD > 103000
order by VRPID, VRVERS
 
Top