Sep 06 2010

SQL to retrieve all Objects & Scope in an OMW Project

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Technical people have to let off a big amount of time writing the documentations, and for that they do need to have the list of all objects in the project. At times its a hassle when you have more than 20 objects in an OMW Project.

Here’s a simple SQL to retrieve the list of all objects, versions and its scope in an OMW Project.

SELECT ProjectID,  ProjDesc, ObjectType, Objects, NVL(ObjectDesc,TRIM(VrJD)) AS OBJDesc, ObjScope, CreatedDate, LastModified
  (SELECT trim(pmomwprjid) AS ProjectID, trim(pmomwdesc) AS ProjDesc, trim(poomwobjid) AS Objects, poomwot AS ObjectType, trim(simd) AS ObjectDesc,
    poomwcrtdt AS CreatedDate,
      WHEN poomwcrtdt=0 THEN 'MOD'
      WHEN poomwcrtdt>0 THEN 'NEW'
    END AS ObjScope, poupmt AS LastModified
  FROM SYS.F98220, SYS.F98222, OBJ.F9860
  WHERE pmomwprjid=poomwprjid
  AND poomwobjid  = siobnm(+)
  AND pmomwprjid  ='MYTESTPROJECT'
ON (trim(Objects) = (trim(vrpid)
  || '|'
  || trim(vrvers)))
ORDER BY Objects;

All that you have to change is the Project ID (MYTESTPROJECT), with your omw project id, and run it.

Following is the output in Oracle SQL Developer

Published under the license.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
(Visited 333 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.


Skip to comment form

  1. Sergii Kurian

    Just Perfect !

    VN:F [1.9.22_1171]
    Rating: +1 (from 1 vote)
  2. Manish Sathe

    When I saw the title, I thought about a need that CNC’s have while assembling an update package for project that contains only Versions..:-) We can not use the Project Name to select the versions..So something similar would help CNC’s also!

    Any case, this is a cool SQL to use..

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      I have a left join to the F983051 for the versions, and so the SQL lists the versions & its names too. Dunno if this answers your scenario

      VN:F [1.9.22_1171]
      Rating: +1 (from 1 vote)
  3. onyx78

    T-SQL script is here

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

Leave a Reply