Jeff Stevenson's Technology Blog: View EnterpriseOne Objects & Versions Checked Out

This little script tells you in a single result set:

Objects Checked Out
Versions Checked Out in DV812, PY812, PD812
Objects created never checked in
Versions created never checked in
Objects with tokens held

I always run this just prior to an implementation team leaving the customer site to ensure that they have done the necessary cleanup of objects and projects. I also combine this with xp_sendmail in a SQL Agent to send a report out monthly to developers. More on xp_sendmail at a later date.

This script makes extensive use of the OPENDATASOURCE, a transact-sql command that Microsoft says "Provides ad hoc connection information as part of a four-part object name without using a linked server name." Its use allows you to query multiple SQL servers from a single Query Analyzer/Management Studio connection.

Note: In the OPENDATASOURCE section there is a variable that must be modified to specify your connection information. Change "Data Source" to your SQL Server:

Data Source=sqlserverhostingjde_dv812;integrated security=sspi'

The script is to be run in Query Analyzer or Management Studio with a connection to the SQL Server hosting the JDE812 database. Change the highlighted variables to valid values for your system.

--SQL Script begin
--Objects Checked Out
PRINT 'Objects Checked Out'
PRINT''use JDE812
select siuser as 'User ID', siobnm as 'Object Name ', simkey as 'Machine', sipathcd as 'Path Code'
from OL812.F9861 where SISTCE != '1'
order by siuser

--Versions Checked Out in DV812
PRINT''
PRINT''
PRINT 'Versions Checked Out in DV812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment' from  OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserverhostingjde_dv812;integrated security=sspi'
).jde_dv812.dv812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Versions Checked Out in PY812
PRINT ''
PRINT ''
PRINT 'Versions Checked Out in PY812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment' from
OPENDATASOURCE('SQLOLEDB','Data Source=sqlserverhostingjde_py812;integrated security=sspi').jde_py812.py812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Versions Checked Out in PD812
PRINT''
PRINT''
PRINT 'Versions Checked Out in PD812'
PRINT ''
select VRUSER as 'User' , VRPID as 'Object', VRVERS as 'Version', VRMKEY as 'Machine', VRENHV as 'Environment'
>from  OPENDATASOURCE('SQLOLEDB','Data Source=sqlserverhostingjde_pd812;integrated security=sspi').jde_pd812.pd812.f983051
where VRCHKOUTSTS='Y'
order by VRUSER

--Objects created never checked in
PRINT''
PRINT''
PRINT 'Objects created but never checked in'
PRINT''
use JDE812
Select POOMWUSER as 'User', cast(POOMWPRJID as char(20)) as 'Project', cast(POOMWOBJID as char(10)) as 'Object', POOMWMKEY as 'Machine', POPATHCD as 'Path Code '
from SY812.F98222
where POOMWOT !='UBEVER' and POOMWOST=01 and POOMWCHS='1'
order by POOMWUSER, POOMWOBJID, POPATHCD

--Versions created never checked in
PRINT''
PRINT''
use JDE812
PRINT 'Versions created but never checked in'
PRINT''
Select POOMWUSER as 'User', cast(POOMWPRJID as char(10)) as 'Project', cast(POOMWOBJID as char(20)) as 'Object', POOMWMKEY as 'Machine', POPATHCD as 'Path Code'
from SY812.F98222
where POOMWOT ='UBEVER' and POOMWOST=01 and POOMWCHS='1'
order by POOMWUSER, POOMWOBJID, POPATHCD

--Objects with tokens held
PRINT''
PRINT''
PRINT 'Objects with tokens held'
PRINT''
use JDE812
Select POOMWUSER as 'User', cast(POOMWOBJID as char(20)) as 'Object', POOMWPRJID as 'Project'
from SY812.F98222
where POOMWPOS1 != '0'
order by POOMWUSER, POOMWOBJID, POPATHCD
--SQL Script end