SQL to get the PeopleSoft Compare Report




SELECT projectname,
objecttype,
DECODE(objecttype, '0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate Value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'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 Definition',
'22', 'Process Type Definition',
'23', 'Process Job Definition',
'24', 'Process Recurrence Definition',
'25', 'Message Catalog',
'26', 'Dimension',
'27', 'Cube Definition',
'28', 'Cube Instance Definition',
'29', 'Business Interlink',
'30', 'SQL Object',
'31', 'File Layout',
'32', 'Component Interface',
'33', 'Application Engine Program',
'34', 'Application Engine Section',
'35', 'Message Node',
'36', 'Message Channel',
'37', 'Message',
'38', 'Approval rule set',
'39', 'Message PeopleCode',
'40', 'Subscription PeopleCode',
'41', 'N/A',
'42', 'Component Interface PeopleCode',
'43', 'Application Engine PeopleCode',
'44', 'Page PeopleCode',
'45', 'Page Field PeopleCode',
'46', 'Component PeopleCode',
'47', 'Component Record PeopleCode',
'48', 'Component Record Field PeopleCode',
'49', 'Image',
'50', 'Style sheet',
'51', 'HTML',
'52', 'Not used',
'53', 'Permission List',
'54', 'Portal Registry Definitions',
'55', 'Portal Registry Structure',
'56', 'URL Definitions',
'57', 'Application Packages',
'58', 'Application Package PeopleCode',
'59', 'Portal Registry User Homepage',
'60', 'Problem Type',
'61', 'Archive Templates',
'62', 'XSLT',
'63', 'Portal Registry User Favorite',
'64', 'Mobile Page',
'65', 'Relationships',
'66', 'Component Interface Property PeopleCode',
'67', 'Optimization Models',
'68', 'File References',
'69', 'File Type Codes',
'70', 'Archive Object Definitions',
'71', 'Archive Templates (Type 2)',
'72', 'Diagnostic Plug In',
'73', 'Analytic Model',
'79', 'Service',
'80', 'Service Operation',
'81', 'Service Operation Handler',
'82', 'Service Operation Version',
'83', 'Service Operation Routing',
'84', 'Info Broker Queues',
'85', 'XML Template Definition',
'86', 'xml Report Definition',
'87', 'xml File Definition',
'88', 'xML Data Source Definition') object_type,
( objectvalue1 || '.' || objectvalue2 || '.' || objectvalue3 )
objectname ,
Decode (SOURCESTATUS,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same') SOURCESTATUS,
Decode (TARGETSTATUS,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same') TARGETSTATUS,
Decode (UPGRADEACTION,
0, 'Copy',
1, 'Delete',
2, 'None',
3, 'CopyProp') UPGRADEACTION
FROM psprojectitem
WHERE projectname IN ('PUM017','ALL_59995','PUM017VW','PUM017PTM')
; 

Comments

Post a Comment

Popular posts from this blog

PeopleSoft AP Matching Process Key Tables for troubleshooting purpose

SELECT * FROM PS_MTCH_CNTRL; -- Match Control Details SELECT * FROM PS_MTCH_LOG_HDR_VW; -- Matching Log SELECT * FROM ps_vchr_mtch_detls; -- Voucher Details selected for Matching SELECT * FROM PS_MTCH_RULE_OVRD; -- If any Voucher has been match Over-Ridden SELECT * FROm PS_MTCH_VCHR_SRCH; -- Vouchers Selected for Matching SELECT * FROM PS_AP_MATCH_EXC_VW; -- Match Exception SELECT * FROM PSXLATITEM where FIELDNAME = 'MTCH_EXCPTN_TYPE'; SELECT * FROM PS_AP_MTCH_RULECFG; -- Match Rule SELECT * FROM PS_AP_MTCH_RULES; -- Match Rule SELECT * FROm PS_AP_MTCH_WB_FLD; -- Match Rule SELECT * FROm PS_AP_MTH_RULETYPE; -- Match Rule SELECT * FROm PS_AP_VCHR_HDR_SUM; -- Header Details that stores and used for Match Rule SELECT * FROm PS_AP_VCHR_LN_SUM; -- Line Details that stores and used for Match Rule SELECT * FROM PS_AP_VCHR_MTCH_VW; SELECT * FROm PS_AP_MTCH_WB_VW; SELECT * FROm PS_AP_WB_OTH_VW; SELECT * FROM PS_MATCH_OBJECT;
Read more

How to Set a Default Password in PeopleSoft

How to set the default password for all users or a specific user in PeopleSoft? 1)       Run the below SQL to set the default password for all User IDs: UPDATE PSOPRDEFN SET OPERPSWD = ‘New Password’ , ENCRYPTED = ‘0’; 2)       Run the below SQL to set the default password for a specific User ID  or group of user ids: UPDATE PSOPRDEFN SET OPERPSWD = ‘New Password’ , ENCRYPTED = ‘0’ WHERE OPRID IN (‘ User ID List’ ); 3)       Run the below Script using data mover to Encrypt the Password: Here's an example of how to encrypt a single user password (FS) already listed in PSOPRDEFN: ENCRYPT_PASSWORD FS ; To encrypt all user passwords in PSOPRDEFN, enter: ENCRYPT_PASSWORD *; Both Update Password and Encrypt Password script can be run together using Data Mover. For All Users: UPDATE PSOPRDEFN SET OPERPSWD = ‘New Password’ , ENCRYPTED = ‘0’; ENCRYPT_PASSWORD *; For Single Us...
Read more

PS Query Security Tree

--SQL will show the Query Security Tree Name and Permission it is associated with for a specific Table Name. SELECT A.TREE_NAME, A.DESCR, B.PARENT_NODE_NAME, B.TREE_NODE, B.TREE_NODE_TYPE, C.CLASSID, B.TREE_NODE_NUM, B.PARENT_NODE_NUM, C.ACCESS_GROUP, C.ACCESSIBLE FROM PSTREEDEFN A, PSTREENODE B, PS_SCRTY_ACC_GRP C WHERE ( A.SETID = ' ' AND A.TREE_STRCT_ID = 'ACCESS_GROUP' AND A.SETID = B.SETID AND A.SETCNTRLVALUE = B.SETCNTRLVALUE AND A.TREE_NAME = B.TREE_NAME AND B.TREE_NODE = :1 AND B.TREE_NAME = C.TREE_NAME ) ORDER BY 7, 8, 6 ; --Below SQL will give the List of Queries that is exist in the system, but some of the Records used in Query in not available in the Query Security Tree SELECT A.QRYNAME, A.DESCR   FROM PSQRYDEFN A, PSQRYRECORD B   WHERE ( A.OPRID = B.OPRID      AND A.QRYNAME = B.QRYNAME      AND NOT EXISTS (SELECT 'X'   FROM PSTREEDEFN C, PSTREENODE D   WHERE C.EFFDT =       ...
Read more