SQL To Find out the Tables added to Record Groups
-------------------------------------------------------------------------------
SELECT *
FROM db2t07fi.ps_REC_GROUP_REC
WHERE rec_group_id NOT IN ('SC_01','RB_01' ,'SO_01' ,'SO_02' ,'SO_03' )
AND (rec_group_id, recname) NOT IN
(SELECT * FROM db2d03fi.ps_REC_GROUP_REC
)
ORDER BY 1,2;
----------------------------------------------------------------------------------------
SQL to Findout the navigation of a Component in PeopleSoft
----------------------------------------------------------------------------------------
select distinct rtrim(reverse
(sys_connect_by_path(reverse
(portal_label), ' > ')), ' > ') path
from psprsmdefn
where portal_name = 'EMPLOYEE'
and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 = :1
connect by prior
portal_prntobjname = portal_objname--------------------------------------------------------------------------------
SQL To Findout in whose Worklist a item is pending
--------------------------------------------------------------------------------
select a.oprid from psworklist a , ps_vchr_wl1 b
where a.WORKLISTNAME = b.WORKLISTNAME and a.transactionid = b.transactionid
and a.INSTSTATUS < 2
and b.business_unit = ''
and b.voucher_id = ''
SELECT * FROM ps_rolexlatopr a WHERE ROLEUSER = ;
SELECT * FROM ps_rte_cntl_ruser b WHERE ROLEUSER = ;
SELECT * FROM ps_rte_cntl_ln c;
--------------------------------------------------------------------------------
Message
--------------------------------------------------------------------------------
SELECT * FROM PSMSGDEFN
--------------------------------------------------------------------------------
CI
--------------------------------------------------------------------------------
select * from psbcdefn
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Queries
--------------------------------------------------------------------------------
select * from psqrydefn
SELECT * FROM SYSADM.PSPCMPROG
SELECT * FROM PSMSGCATDEFN WHERE MESSAGE_NBR = 864 -- FOR MESSAGES FROM MESSAGE CATELOG
SELECT * FROM PSPNLGROUP WHERE PNLGRPNAME = 'AP_PORTAL_LVL1'
--------------------------------------------------------------------------------
PAGES IN A COMPONENT
--------------------------------------------------------------------------------
SELECT * FROM PSPNLGRPDEFN WHERE PNLGRPNAME = 'AP_PORTAL_LVL1'
SELECT * FROM PSPNLGRPDEFN WHERE PNLGRPNAME = 'AP_PORTAL_LVL1'
--------------------------------------------------------------------------------
DEFINITION OF COMPONENT
--------------------------------------------------------------------------------
SELECT * FROM PSPNLGRPDEL
SELECT * FROM PSPRDMDEFN WHERE PORTAL_URI_SEG2 = ' '
SELECT * FROM PSPNLGRPDEL
SELECT * FROM PSPRDMDEFN WHERE PORTAL_URI_SEG2 = ' '
--------------------------------------------------------------------------------
PORTAL DEFINITION
PORTAL DEFINITION
--------------------------------------------------------------------------------
SELECT * FROM PSPRSMSYSATTRVL WHERE PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' AND PORTAL_OBJNAME = 'AP_PORTAL_LVL1'
SELECT * FROM PS_PORTAL_FLDRP
--------------------------------------------------------------------------------
MENU DEFINITION
SELECT * FROM PSPRSMSYSATTRVL WHERE PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' AND PORTAL_OBJNAME = 'AP_PORTAL_LVL1'
SELECT * FROM PS_PORTAL_FLDRP
--------------------------------------------------------------------------------
MENU DEFINITION
--------------------------------------------------------------------------------
SELECT * FROM PSMENUDEFN
SELECT * FROM PSMENUDEFN
--------------------------------------------------------------------------------
PAGES IN A MENU
PAGES IN A MENU
--------------------------------------------------------------------------------
SELECT * FROM PSMENUITEM
***************************************************************
*SQL FOR FINDING ITEMS FROM COMPARE WHICH WOULD BE COPIED OVER*
***************************************************************
SELECT OBJECTVALUE1,OBJECTVALUE2,OBJECTVALUE3,OBJECTVALUE4
FROM PSPROJECTITEM
WHERE PROJECTNAME='XXXX'
AND SOURCESTATUS NOT IN (0,1,2,3,6)
AND OBJECTTYPE IN ('34')
AND OBJECTVALUE1 NOT LIKE 'XXX%'
AND OBJECTVALUE1 NOT LIKE 'XW%';
***************************************************
*Find out objects which are not compiled in oracle*
***************************************************
select owner,object_type, count(*) from sys.dba_objects where status = 'INVALID' group by owner, object_type
SQL to find out Objects in PeopleSoft
select DECODE( a.objecttype,
0 ,'Record'
,1 ,'Index'
,2 ,'Field'
,3 ,'Field Format'
,4 ,'Translate'
,5 ,'Panel'
,6 ,'Menu'
,7 ,'Panel Group'
,8 ,'Record PeopleCode'
,9 ,'Menu PeopleCode'
,10 ,'Query'
,11 ,'Tree Structure'
,12 ,'Tree'
,13 ,'Access Group'
,14 ,'Color'
,15 ,'Style'
,16 ,'Business Process Map'
,17 ,'Business Process'
,18 ,'Activity'
,19 ,'Role'
,20 ,'Process Definition'
,21 ,'Process Server'
,22 ,'Process Type'
,23 ,'Process Job'
,24 ,'Process Recurrence'
,25 ,'Message'
,26 ,'Dimension'
,27 ,'Analysis Mode'
,28 ,'Cube Template'
,29 ,'Business Interlink'
,30 ,'SQL'
,31 ,'File Layout'
,32 ,'Component Interface'
,33 ,'App Engine'
,34 ,'App Engine App Section'
,35 ,'Message Node'
,36 ,'Message Channel'
,37 ,'Message'
,38 ,'Approval Rule'
,39 ,'Message PeopleCode OnSubscribeTransform'
,40 ,'Message PeopleCode Subscription'
,41 ,'Message PeopleCode Subscription'
,42 ,'Component Interface Method People Code '
,43 ,'App Engine People Code '
,44 ,'Page Activate People Code '
,45 ,'Page Field People Code Not Used in CS'
,46 ,'Component People Code'
,47 ,'Component People Code'
,48 ,'Component People Code'
,49 ,'Image'
,50 ,'Style Sheet'
,51 ,'HTML Definition'
,52 ,'FILE Reference '
,53 ,'Permission List'
,54 ,'PORTAL Registry DEFINITION '
,55 ,'PORTAL Registry STRUCTURE '
,56 ,'URL Definitions '
, a.objecttype ) ObjectType,
DECODE(a.OBJECTID1,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID2,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID3,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID4,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
a.objectvalue1
,a.objectvalue2
,a.objectvalue3
,a.objectvalue4
, DECODE (SOURCESTATUS
,0 ,'Unknown'
,1 ,'Absent'
,2 ,'Changed'
,3 ,'Unchanged'
,4 ,'Custom Changed'
,5 ,'Custom Unchanged'
,6 ,'Same'
,'What the ???') SourceStatus
, DECODE (TARGETSTATUS
,0 ,'Unknown'
,1 ,'Absent'
,2 ,'Changed'
,3 ,'Unchanged'
,4 ,'Custom Changed'
,5 ,'Custom Unchanged'
,6 ,'Same'
,'What the ???') TargetStatus
,DECODE( TAKEACTION , 0, 'Don''t Upgrade', 'Upgrade') ACTION_TAKEN
,DECODE(UPGRADEACTION
,0 ,'Copy'
,1 ,'Delete'
,2 ,'None'
,3 ,'CopyProperties' ) UpgradeAction
,a.*
from psprojectitem a
where projectname like 'XXX'
and sourcestatus <> 6 and targetstatus <> 6
order by 1
SELECT * FROM PSMENUITEM
***************************************************************
*SQL FOR FINDING ITEMS FROM COMPARE WHICH WOULD BE COPIED OVER*
***************************************************************
SELECT OBJECTVALUE1,OBJECTVALUE2,OBJECTVALUE3,OBJECTVALUE4
FROM PSPROJECTITEM
WHERE PROJECTNAME='XXXX'
AND SOURCESTATUS NOT IN (0,1,2,3,6)
AND OBJECTTYPE IN ('34')
AND OBJECTVALUE1 NOT LIKE 'XXX%'
AND OBJECTVALUE1 NOT LIKE 'XW%';
***************************************************
*Find out objects which are not compiled in oracle*
***************************************************
select owner,object_type, count(*) from sys.dba_objects where status = 'INVALID' group by owner, object_type
SQL to find out Objects in PeopleSoft
select DECODE( a.objecttype,
0 ,'Record'
,1 ,'Index'
,2 ,'Field'
,3 ,'Field Format'
,4 ,'Translate'
,5 ,'Panel'
,6 ,'Menu'
,7 ,'Panel Group'
,8 ,'Record PeopleCode'
,9 ,'Menu PeopleCode'
,10 ,'Query'
,11 ,'Tree Structure'
,12 ,'Tree'
,13 ,'Access Group'
,14 ,'Color'
,15 ,'Style'
,16 ,'Business Process Map'
,17 ,'Business Process'
,18 ,'Activity'
,19 ,'Role'
,20 ,'Process Definition'
,21 ,'Process Server'
,22 ,'Process Type'
,23 ,'Process Job'
,24 ,'Process Recurrence'
,25 ,'Message'
,26 ,'Dimension'
,27 ,'Analysis Mode'
,28 ,'Cube Template'
,29 ,'Business Interlink'
,30 ,'SQL'
,31 ,'File Layout'
,32 ,'Component Interface'
,33 ,'App Engine'
,34 ,'App Engine App Section'
,35 ,'Message Node'
,36 ,'Message Channel'
,37 ,'Message'
,38 ,'Approval Rule'
,39 ,'Message PeopleCode OnSubscribeTransform'
,40 ,'Message PeopleCode Subscription'
,41 ,'Message PeopleCode Subscription'
,42 ,'Component Interface Method People Code '
,43 ,'App Engine People Code '
,44 ,'Page Activate People Code '
,45 ,'Page Field People Code Not Used in CS'
,46 ,'Component People Code'
,47 ,'Component People Code'
,48 ,'Component People Code'
,49 ,'Image'
,50 ,'Style Sheet'
,51 ,'HTML Definition'
,52 ,'FILE Reference '
,53 ,'Permission List'
,54 ,'PORTAL Registry DEFINITION '
,55 ,'PORTAL Registry STRUCTURE '
,56 ,'URL Definitions '
, a.objecttype ) ObjectType,
DECODE(a.OBJECTID1,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID2,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID3,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
DECODE(a.OBJECTID4,
0, ' '
, 1, 'CLASS_RECORD '
, 2, 'CLASS_FIELD '
, 3, 'CLASS_MENU '
, 4, 'CLASS_MENUBAR '
, 5, 'CLASS_MENUITEM '
, 6, 'CLASS_DBFIELD '
, 7, 'CLASS_BUSINESSPROCESS '
, 8, 'CLASS_BUSINESSPROCESSMAP '
, 9, 'CLASS_PANEL '
, 10, 'CLASS_PANELGROUP '
, 11, 'CLASS_PROJECT '
, 12, 'CLASS_METHOD '
, 13, 'CLASS_FUNCTION '
, 14, 'CLASS_SOURCETOKEN '
, 15, 'CLASS_SOURCELINE '
, 16, 'CLASS_LANGUAGECODE '
, 17, 'CLASS_ACCESS_GROUP '
, 18, 'CLASS_ACTIVITYNAME '
, 19, 'CLASS_COLORNAME '
, 20, 'CLASS_DBTYPE '
, 21, 'CLASS_EFFDT '
, 22, 'CLASS_FIELDVALUE '
, 23, 'CLASS_FORMATFAMILY '
, 24, 'CLASS_INDEXID '
, 25, 'CLASS_OPRID '
, 26, 'CLASS_OPSYS '
, 27, 'CLASS_PRCSJOBNAME '
, 28, 'CLASS_PRCSNAME '
, 29, 'CLASS_PRCSTYPE '
, 30, 'CLASS_QRYNAME '
, 31, 'CLASS_RECURNAME '
, 32, 'CLASS_ROLENAME '
, 33, 'CLASS_SERVERNAME '
, 34, 'CLASS_SETID '
, 35, 'CLASS_STYLENAME '
, 36, 'CLASS_TREE_NAME '
, 37, 'CLASS_TREE_STRCT_ID '
, 38, 'CLASS_LONG_NAME '
, 39, 'CLASS_MARKET '
, 40, 'CLASS_PANELREF '
, 41, 'CLASS_PANELGROUPREF '
, 42, 'CLASS_SYSCOLOR '
, 43, 'CLASS_STYLE '
, 44, 'CLASS_FIELD_FORMAT '
, 45, 'CLASS_TOOLBAR '
, 46, 'CLASS_FILEREF '
, 47, 'CLASS_TABLESPACE '
, 48, 'CLASS_MESSAGE_SET_NBR '
, 49, 'CLASS_MESSAGE_NBR '
, 50, 'CLASS_MESSAGE_DESCR '
, 51, 'CLASS_DIMENSION_ID '
, 52, 'CLASS_DIMENSION_TYPE '
, 53, 'CLASS_DIMENSION_DESCR '
, 54, 'CLASS_ANALYSIS_MODEL_ID '
, 55, 'CLASS_ANALYSIS_MODEL_DESCR '
, 56, 'CLASS_ANALYSIS_DB_ID '
, 57, 'CLASS_CUBE_TEMPLATE_DESCR '
, 58, 'CLASS_BUSINESSPROCESSREF '
, 59, 'CLASS_ACTIVITYREF '
, 60, 'CLASS_MESSAGE '
, 61, 'CLASS_CHANNEL '
, 62, 'CLASS_MESSAGE_NODE '
, 63, 'CLASS_MESSAGE_FILTER '
, 64, 'CLASS_INTERFACEOBJECT '
, 65, 'CLASS_SQL '
, 66, 'CLASS_AEAPPLICATIONID '
, 67, 'CLASS_PANELFIELD '
, 68, 'CLASS_SETCNTRLVALUE '
, 69, 'CLASS_OLDBUSPROC '
, 70, 'CLASS_OLDACTIVITY '
, 71, 'CLASS_FILELAYOUT '
, 72, 'CLASS_PRINT '
, 73, 'CLASS_PRINTFILEREF '
, 74, 'CLASS_BUSINESSCOMPONENT '
, 75, 'CLASS_BCINTERFACE '
, 76, 'CLASS_BCPROPERTY '
, 77, 'CLASS_AESECTIONNODE '
, 78, 'CLASS_AESTEPNODE '
, 79, 'CLASS_AEACTIONNODE '
, 80, 'CLASS_RULE '
, 81, 'CLASS_SQLTYPE '
, 82, 'CLASS_PCDEBUGGER '
, 83, 'CLASS_SCROLL '
, 84, 'CLASS_EXESTATEMENT '
, 85, 'CLASS_APPRRULESET '
, 86, 'CLASS_REPORT '
, 87, 'CLASS_SUBSCRIPTION '
, 88, 'CLASS_LANGTRANS '
, 89, 'CLASS_CLASS '
, 90, 'CLASS_HTMLCATALOG '
, 91, 'CLASS_IMAGE '
, 92, 'CLASS_ALTCONTNUM '
, 93, 'CLASS_DYNAMICPAGE '
, 94, 'CLASS_STYLESHEET '
, 95, 'CLASS_CONTTYPE '
, 96, 'CLASS_PATHREF '
, 97, 'CLASS_FIELDTYPE '
, 98, 'CLASS_PORTALDEFINITION '
, 99, 'CLASS_PORTALSTRUCTURE '
, 100, 'CLASS_PORTALREFTYPE '
, 101, 'CLASS_PORTALOBJNAME '
, 102, 'CLASS_FIELDLABEL '
, 103, 'CLASS_URL '
, 104, 'CLASS_APPLICATIONPACKAGE '
, 105, 'CLASS_APPLICATIONPACKAGE1 '
, 106, 'CLASS_APPLICATIONPACKAGE2 '
, 107, 'CLASS_APPLICATIONCLASS '
, 108, 'CLASS_PORTALUSERHOMEPAGE '
, 109, 'CLASS_PROBTYPE ' ,'Not Defined' ) ObjectType,
a.objectvalue1
,a.objectvalue2
,a.objectvalue3
,a.objectvalue4
, DECODE (SOURCESTATUS
,0 ,'Unknown'
,1 ,'Absent'
,2 ,'Changed'
,3 ,'Unchanged'
,4 ,'Custom Changed'
,5 ,'Custom Unchanged'
,6 ,'Same'
,'What the ???') SourceStatus
, DECODE (TARGETSTATUS
,0 ,'Unknown'
,1 ,'Absent'
,2 ,'Changed'
,3 ,'Unchanged'
,4 ,'Custom Changed'
,5 ,'Custom Unchanged'
,6 ,'Same'
,'What the ???') TargetStatus
,DECODE( TAKEACTION , 0, 'Don''t Upgrade', 'Upgrade') ACTION_TAKEN
,DECODE(UPGRADEACTION
,0 ,'Copy'
,1 ,'Delete'
,2 ,'None'
,3 ,'CopyProperties' ) UpgradeAction
,a.*
from psprojectitem a
where projectname like 'XXX'
and sourcestatus <> 6 and targetstatus <> 6
order by 1
******************************************************************************************
* SQL for Finding Mismatch between Tools Indexes and Oracle Database Indexes*
******************************************************************************************
Scenario 1: Possibility of sequencing on the indexed columns being incorrect
SELECT A.TABLE_NAME,A.INDEX_NAME,A.COLUMN_NAME,A.COLUMN_POSITION, B.FIELDNAME,B.KEYPOSN FROM USER_IND_COLUMNS A,PSKEYDEFN B, PSRECDEFN C WHERE A.TABLE_NAME = 'PS_'||B.RECNAME AND A.INDEX_NAME = 'PS'|| B.INDEXID || B.RECNAME AND B.RECNAME = C.RECNAME AND A.COLUMN_NAME = B.FIELDNAME AND A.COLUMN_POSITION <> B.KEYPOSN AND C.OBJECTOWNERID IN ('FAP', ' ') AND C.LASTUPDOPRID <> 'PPLSOFT' ORDER BY 1,2,3
Scenario 2: Count of columns on the indexes between Database & PeopleTools don't match.
SELECT A.TABLE_NAME,A.INDEX_NAME,B.KEYCOUNT,COUNT(A.COLUMN_NAME) FROM USER_IND_COLUMNS A,PSINDEXDEFN B, PSRECDEFN C WHERE A.TABLE_NAME = 'PS_'||B.RECNAME AND A.INDEX_NAME = 'PS'|| B.INDEXID || B.RECNAME AND B.RECNAME = C.RECNAME AND C.OBJECTOWNERID IN ('FAP', ' ') AND C.LASTUPDOPRID <> 'PPLSOFT' GROUP BY A.TABLE_NAME,A.INDEX_NAME,B.KEYCOUNT HAVING COUNT(A.COLUMN_NAME) <> B.KEYCOUNT ORDER BY 1,2
Scenario 3: PeopleTools Indexes not on DB
SELECT B.RECNAME, B.INDEXID, B.INDEXTYPE, B.UNIQUEFLAG, B.KEYCOUNT FROM PSINDEXDEFN B, PSRECDEFN C WHERE B.RECNAME = C.RECNAME AND C.OBJECTOWNERID IN ('FAP', ' ') AND C.LASTUPDOPRID <> 'PPLSOFT' AND B.INDEXID NOT IN (SELECT SUBSTR (A.INDEX_NAME, 3, 1) FROM USER_INDEXES A WHERE A.TABLE_NAME = 'PS_' || B.RECNAME)
Scenario 4: DB Indexes not on PeopleTools
SELECT REPLACE(c.TABLE_NAME, 'PS_', ''),SUBSTR(c.INDEX_NAME,3,1) FROM psrecdefn b, user_indexes c
WHERE 'PS_' || b.recname = c.table_name AND b.objectownerid IN ('FAP', ' ') AND b.lastupdoprid <> 'PPLSOFT'
MINUS SELECT B.RECNAME, B.INDEXID FROM PSINDEXDEFN B, PSRECDEFN C WHERE B.RECNAME = C.RECNAME AND C.OBJECTOWNERID IN ('FAP', ' ') AND C.LASTUPDOPRID <> 'PPLSOFT'