The sql will identify duplicate objects in different application designer projects to eliminate duplicate work by developers.
You will find the SQL very handy during the analysis phase of a peoplesoft upgrade project. You 1st need to identify your projects and run compare and report through application designer. This will flag objects in the source database that are different than the target database to be marked for upgrade.
Now run the SQL below, if you have rows coming back then you have objects that exists in more than on project. You will need to examine the MIN(PROKECTNAME) AND MAX(PROJECTNAME) columns and decide in which project to keep the duplicate object. To take an object off a project, just navigate to the "upgrade" tab, uncheck the upgrade flag and save. Re-run the sql and your object will not show as duplicate any more.
SELECT
(CASE objecttype
WHEN
0
THEN
'Record'
WHEN
1
THEN
'Index'
WHEN
2
THEN
'Field'
WHEN
3
THEN
'Field Format'
WHEN
4
THEN
'Translate Value'
WHEN
5
THEN
'Pages'
WHEN
6
THEN
'Menus'
WHEN
7
THEN
'Components'
WHEN
8
THEN
'Record PeopleCode'
WHEN
9
THEN
'Menu PeopleCode'
WHEN
10
THEN
'Query'
WHEN
11
THEN
'Tree Structures'
WHEN
12
THEN
'Trees'
WHEN
13
THEN
'Access group'
WHEN
14
THEN
'Color'
WHEN
15
THEN
'Style'
WHEN
16
THEN
'N/A'
WHEN
17
THEN
'Business process'
WHEN
18
THEN
'Activity'
WHEN
19
THEN
'Role'
WHEN
20
THEN
'Process Definition'
WHEN
21
THEN
'Server Definition'
WHEN
22
THEN
'Process Type Definition'
WHEN
23
THEN
'Job Definitions'
WHEN
24
THEN
'Recurrence Definition'
WHEN
25
THEN
'Message Catalog'
WHEN
26
THEN
'Dimension'
WHEN
27
THEN
'Cube Definitions'
WHEN
28
THEN
'Cube Instance Definitions'
WHEN
29
THEN
'Business Interlink'
WHEN
30
THEN
'SQL'
WHEN
31
THEN
'File Layout Definition'
WHEN
32
THEN
'Component Interfaces'
WHEN
33
THEN
'AE program'
WHEN
34
THEN
'AE section'
WHEN
35
THEN
'Message Node'
WHEN
36
THEN
'Message Channel'
WHEN
37
THEN
'Message'
WHEN
38
THEN
'Approval rule set'
WHEN
39
THEN
'Message PeopleCode'
WHEN
40
THEN
'Subscription PeopleCode'
WHEN
41
THEN
'N/A'
WHEN
42
THEN
'Component Interface PeopleCode'
WHEN
43
THEN
'AE PeopleCode'
WHEN
44
THEN
'Page PeopleCode'
WHEN
45
THEN
'Page Field PeopleCode'
WHEN
46
THEN
'Component PeopleCode'
WHEN
47
THEN
'Component Record PeopleCode'
WHEN
48
THEN
'Component Rec Fld PeopleCode'
WHEN
49
THEN
'Image'
WHEN
50
THEN
'Style sheet'
WHEN
51
THEN
'HTML'
WHEN
52
THEN
'Not used'
WHEN
53
THEN
'Permission List'
WHEN
54
THEN
'Portal Registry Definitions'
WHEN
55
THEN
'Portal Registry Structures'
WHEN
56
THEN
'URL Definitions'
WHEN
57
THEN
'Application Packages'
WHEN
58
THEN
'Application Package Peoplecode'
WHEN
59
THEN
'Portal Registry User Homepage'
WHEN
60
THEN
'Problem Type'
WHEN
61
THEN
'Archive Templates'
WHEN
62
THEN
'XSLT'
WHEN
63
THEN
'Portal Registry User Favorite'
WHEN
64
THEN
'Mobile Page'
WHEN
65
THEN
'Relationships'
WHEN
66
THEN
'Component Interface Property Peoplecode'
WHEN
67
THEN
'Optimization Models'
WHEN
68
THEN
'File References'
WHEN
69
THEN
'File Type Codes'
WHEN
70
THEN
'Archive Object Definitions'
WHEN
71
THEN
'Archive Templates (Type 2)'
WHEN
72
THEN
'Diagnostic Plug In'
WHEN
73
THEN
'Analytic Model'
ELSE
'UNKNOWN OBJECT TYPE'
END
)
AS objecttype,
MIN
(projectname
), MAX
(projectname
), objectvalue1, objectvalue2,
objectvalue3, objectvalue4
FROM psprojectitem
WHERE projectname
LIKE
'projectname_%'
-- or you can use projectname in ('project1', 'project2', 'project3', 'project4'
AND takeaction =
'1'
GROUP
BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4
HAVING COUNT
(*
) >
1
ORDER
BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4;
转载于:https://www.cnblogs.com/GoDevil/archive/2008/08/08/1263771.html
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)