SQL TO SEE ALL THE OBJECTS IN PROJECT BY PROJECT NAME (PEOPLESOFT)

SELECT PROJECTNAME

 ,OBJECTTYPE   

 , CASE OBJECTTYPE WHEN 0 THEN CASE NVL((   

 SELECT RECTYPE   

  FROM PSRECDEFN   

 WHERE RECNAME = I.OBJECTVALUE1), 99) WHEN 0 THEN 'SQL Table in DB' WHEN 1 THEN 'SQL View in DB' WHEN 2 THEN 

'Work Record' WHEN 3 THEN 'Sub Record' WHEN 5 THEN 'Dynamic View' WHEN 6 THEN 'Query View' WHEN 7 THEN 

'Temporary Table' ELSE 'Other Record or Deleted' END WHEN 1 THEN 'Index' WHEN 2 THEN 'Field' WHEN 3 THEN 

'Field Format' WHEN 4 THEN 'Translate Value' WHEN 5 THEN 'Page' WHEN 6 THEN 'Menu' WHEN 7 THEN 'Component' 

WHEN 8 THEN 'Record PeopleCode' WHEN 9 THEN 'Menu PeopleCode' WHEN 10 THEN 'Query' WHEN 11 THEN 'Tree 

Structure' WHEN 12 THEN 'Tree' WHEN 13 THEN 'Access Group' WHEN 14 THEN 'Color' WHEN 15 THEN 'Style' WHEN 16 

THEN 'Business Process Map' WHEN 17 THEN 'Business Process' WHEN 18 THEN 'Activity' WHEN 19 THEN 'Role' WHEN 

20 THEN 'Process Definition' WHEN 21 THEN 'Process Server Definition' WHEN 22 THEN 'Process Type Definition' 

WHEN 23 THEN 'Process Job Definition' WHEN 24 THEN 'Process Recurrence Definition' WHEN 25 THEN 'Message 

Catalog' WHEN 26 THEN 'Dimension' WHEN 27 THEN 'Cube Definition' WHEN 28 THEN 'Cube Instance Definition' WHEN 

29 THEN 'Business Interlink' WHEN 30 THEN CASE OBJECTVALUE2 WHEN '0' THEN 'SQL Object' WHEN '1' THEN 'App 

Engine SQL' WHEN '2' THEN 'Record View SQL' WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT' WHEN '6' THEN 'App 

Engine XML SQL' ELSE 'SQL' END WHEN 31 THEN 'File Layout' WHEN 32 THEN 'Component Interface' WHEN 33 THEN 

'Application Engine Program' WHEN 34 THEN 'Application Engine 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 'Application Engine 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 Record Field 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 Structure' 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' WHEN 79 THEN 'Service' WHEN 80 THEN 'Service Operation' WHEN 81 THEN 'Service 

Operation Handler' WHEN 82 THEN 'Service Operation Version' WHEN 83 THEN 'Service Operation Routing' WHEN 84 

THEN 'Info Broker Queues' WHEN 85 THEN 'XLMP Template Definition' WHEN 86 THEN 'XLMP Report Definition' WHEN 

87 THEN 'XMLP File Definition' WHEN 88 THEN 'XMPL Data Source Definition' ELSE 'Unknown ' || OBJECTTYPE END 

AS Object_Type, CASE OBJECTTYPE WHEN 12 THEN OBJECTVALUE3 WHEN 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN 

OBJECTVALUE1 /* SQL Object */ WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12) WHEN OBJECTVALUE2 = 2 

THEN OBJECTVALUE1 /* Record View SQL */ ELSE ' ' END WHEN 34 THEN TRIM(OBJECTVALUE1) || '.' || 

TRIM(OBJECTVALUE2) WHEN 62 THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12)) ELSE OBJECTVALUE1 END AS NAME, CASE WHEN 

OBJECTTYPE = 1 THEN 'Index: ' || OBJECTVALUE2 WHEN OBJECTTYPE = 4 THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' 

|| OBJECTVALUE3 || '; ' || NVL((   

 SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' || XLATLONGNAME || '; Status: ' || EFF_STATUS   

  FROM PSXLATITEM   

 WHERE FIELDNAME = I.OBJECTVALUE1   

   AND FIELDVALUE = I.OBJECTVALUE2   

   AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD') ), 'XLAT Deleted') WHEN OBJECTTYPE = 7 THEN 'Market: ' 

|| OBJECTVALUE2 WHEN OBJECTTYPE = 8 THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN 

OBJECTTYPE = 9 THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4 WHEN OBJECTTYPE = 12 THEN 

'EFFDT: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 20 THEN 'Process Name: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(22, 

40) THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN OBJECTTYPE = 25 THEN 'Message: ' || OBJECTVALUE2 || ' 

(Message Set Descr: ' || OBJECTVALUE3 || ')' WHEN OBJECTTYPE = 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* 

SQL Object */ WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' || 

SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' || 

DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL', 'D', 'Do Select', 'W', 'Do While', 'H', 'Do When', 'N', 'Do 

Until', SUBSTR(OBJECTVALUE1, 29, 1)) WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */ ELSE ' ' END WHEN 

OBJECTTYPE = 38 THEN 'EFFDT: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(39, 42, 44) THEN OBJECTVALUE2 WHEN 

OBJECTTYPE = 43 THEN CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN 'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 

8) || '; Step: ' || OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) || '; Database: ' || 

TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) || '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10)) ELSE 'Section: ' || 

OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3 || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) || '; 

EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END WHEN OBJECTTYPE = 46 THEN 'Market: ' || OBJECTVALUE2 || '; 

Event: ' || OBJECTVALUE3 WHEN OBJECTTYPE = 47 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3 

|| '; Event: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 48 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || 

OBJECTVALUE3 || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18)) || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 

19, 16)) WHEN OBJECTTYPE = 55 THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3 

WHEN OBJECTTYPE = 57 THEN CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN 'Subclass: ' || 

TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) || ':' || TRIM(OBJECTVALUE4) ELSE CASE WHEN 

TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) 

ELSE CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) ELSE ' ' END 

END END WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN 

TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) || '.' || TRIM(OBJECTVALUE4) ELSE CASE WHEN 

TRIM(OBJECTVALUE3) IS NOT NULL THEN TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) ELSE CASE WHEN 

TRIM(OBJECTVALUE2) IS NOT NULL THEN TRIM(OBJECTVALUE2) ELSE ' ' END END END WHEN OBJECTTYPE = 59 THEN 

TRIM(OBJECTVALUE2) WHEN OBJECTTYPE = 62 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' || 

SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) ELSE ' ' END AS EXTENDED_OBJ_NAME, 

CASE OBJECTTYPE WHEN 0 THEN NVL((   

 SELECT RECDESCR   

  FROM PSRECDEFN   

 WHERE RECNAME = I.OBJECTVALUE1), ' ') WHEN 1 THEN NVL((   

 SELECT IDXCOMMENTS   

  FROM PSINDEXDEFN   

 WHERE RECNAME = I.OBJECTVALUE1   

   AND INDEXID = I.OBJECTVALUE2), ' ') WHEN 3 THEN NVL((   

 SELECT DESCR   

  FROM PSFMTDEFN   

 WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ') WHEN 5 THEN NVL((   

 SELECT DESCR   

  FROM PSPNLDEFN   

 WHERE PNLNAME = I.OBJECTVALUE1), ' ') WHEN 6 THEN NVL((   

 SELECT DESCR   

  FROM PSMENUDEFN   

 WHERE MENUNAME = I.OBJECTVALUE1), ' ') WHEN 7 THEN NVL((   

 SELECT DESCR   

  FROM PSPNLGRPDEFN   

 WHERE PNLGRPNAME = I.OBJECTVALUE1   

   AND MARKET = I.OBJECTVALUE2), ' ') WHEN 20 THEN NVL((   

 SELECT DESCR   

  FROM PS_PRCSDEFN   

 WHERE PRCSTYPE = I.OBJECTVALUE1   

   AND PRCSNAME = I.OBJECTVALUE2), ' ') WHEN 32 THEN NVL((   

 SELECT DESCR   

  FROM PSBCDEFN   

 WHERE BCNAME = I.OBJECTVALUE1), ' ') WHEN 33 THEN NVL((   

 SELECT DESCR   

  FROM PSAEAPPLDEFN   

 WHERE AE_APPLID = I.OBJECTVALUE1), ' ') ELSE ' ' END AS DESCR   

  FROM PSPROJECTITEM I   

  WHERE I.PROJECTNAME='LIST_OF_PROJECT_ITEMS' --REPLACE WITH YOUR PROJECT NAME

  ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4

Comments

Post a Comment

Popular posts from this blog

BIP (BIPUBLISHER) Using PS_Query

HOW TO CREATE BI-PUBLISHER REPORT CREATE PS QUERY BASED ON REQUIRMENTS OF OUR REPORT REGISTER BIP DATA SOURCE STEP:1 MAINMENU>REPORTING TOOLS> BI PUBLISHER> REGISTER BIP DATA SOURCE ADD NEW VALUE (+)  SOURCE TYPE=QUERY SOURCE ID= PS-QUERY NAME GENERATE XML FOR OUR QUERY DOWNLOAD THAT XML FILE UPLOAD THAT FILE IN WORD BI PUBLISHER MODIFY THE WORF FILE BASED ON OUR REQUIRMENTS SAVE THAT FILE AS RTCH TEXT FORMATE(RTF) CREATING BIP REPORT DEFINITION STEP:2 MAINMENU>REPORTING TOOLS> BI PUBLISHER> CREATE BIP REPORT DEFINITION ADD NEW VALUE (+)  REPORT NAME= NEW NAME FOR REPORT DATA SOURCR TYPE=QUERY DATA SOURCT ID=PS-QUERY NAME INSIDE TABS 1.DEFINITION TAB DESCR= CREATE DESCR REPORT STATUS ACTIVE MAKE IT ALLUSERS 2.TEMPLATE TAB CHANGE STATUS TO ACTIVE UPLODE RTF FILE 3.OUTPUT TAB CHANGE OUT PUT TYPE AS PER YOUR REQUIRMENTS MAKE DEFAULT AS PDF BIP QUERY REPORT VIEWER STEP:3 MAINMENU>REPORTING TOOLS> BI PUBLISHER> BIP QUERY REPORT VIEWER REPORT NAME= (WHAT...
Read more

BASIC GLOBAL SEARCH IN PEOPLESOFT

  HOW TO CREATE GLOBAL SEARCH MAINMENU>REPORTINGTOOLS>QUERY MANAGER STEP:1 MAINMENU>REPORTING TOOLS > QUERY MANAGER> 1.LAST EFFDATE COLUMN 2.CRETRIA FOR EFFDT  IS NOT LESS THAN CREATE A PSQUERY   OR (USE CONNECTED QUERY)  OR (USE DOCUMENTS ALSO) PEOPLETOOLS>SEARCH FRAME WORK>DESIGNER> DEFINE SEARCH DEFINITION STEP:1 PEOPLETOOLS>SEARCHFRAMEWORK> DESIGNER>  DEFINE SEARCH DEFNITIONS 1.MAP DATE ,LINK COLUMN 2.ADD TITLE,SUMMARY 3.MAP SEARCH ATTRIBUTE IN TOP (SELECT FIELDS FOR GLOBAL SEARCH DISPAY)&FACETED ADD NEW VALUE CREATE SEARCH DEFINITION NAME*   SOURCE TYPPE =QUERY ADD+ SOURCE NAME=PS_QUERY NAME DEFINE SEARCH CATAGORY STEP:2 PEOPLETOOLS>SEARCH FRAMEWORK>  DESIGNER>   DEFINE SEARCH CATEGORIES CHECK BOX (SEARCH GROUP) MAP SEARCH  DEFINITION NAME BELOW AND SAVE ADD NEW VALUE SEARCH CATEGUARY=SAME NAME SEARCH DEFNITION NAME PEOPLETOOLS>SEARCH FRAME WORK>ADMINISTRATOR> DEFINE SEARCH CONT...
Read more