1/4/10

SQL to get list of objects along with modules for a Upgrade Compare Project

SELECT (CASE WHEN A.OBJECTID4 <> 0 THEN
(A.OBJECTVALUE1
|| ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3 || ' -> ' ||
A.OBJECTVALUE4) ELSE (CASE WHEN A.OBJECTID3 <> 0
THEN (A.OBJECTVALUE1
|| ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3) ELSE (CASE WHEN
A.OBJECTID2 <> 0 THEN (A.OBJECTVALUE1
|| ' -> ' || A.OBJECTVALUE2) ELSE A.OBJECTVALUE1 END) END )END) ,
B.OBJECTOWNERID,
X.XLATLONGNAME
FROM
psprojectitem A
,PSrecDEFN B--2
,PSXLATITEM X
WHERE A.projectname = 'PROJECT_NAME'--Name of the project to be analyzed
AND A.COPYDONE = 1
AND A.OBJECTTYPE = 4 --3
AND A.OBJECTVALUE1 = B.recname--1
AND X.FIELDNAME(+) = 'OBJECTOWNERID'
AND X.FIELDVALUE(+)=B.OBJECTOWNERID
AND X.EFF_STATUS(+) = 'A'
AND (X.EFFDT IS NULL OR (X.EFFDT = (SELECT MAX(X1.EFFDT) FROM PSXLATITEM
X1 WHERE X.FIELDNAME = X1.FIELDNAME
AND X.FIELDVALUE = X1.FIELDVALUE
AND X.EFFDT <= SYSDATE)))
/

No comments:

Post a Comment

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month