Records

 

Report Manager tables

PS_CDM_LIST

PS_CDM_AUTH

PS_CDM_TEXT

PS_CDM_FILE_LIST

PS_CDM_TRANSFER

PS_CDM_TRNFR_RJCT

PS_CDM_LIST_ARCH

Process Request tables

PSPRCSRQST

PSPRCSQUE

PSPRCSCHLDINFO

PSPRCSPARMS

PSPRCSRQSTTEXT

PSPRCSRQSTXFER

PS_PRCSRQSTDIST

PS_MESSAGE_LOG

PS_MESSAGE_LOGPARM

PSPRCSRQSTFILE

PSPRCSRQSTMETA

PSPRCSRQSTSTRNG

PSPRCSRQSTTIME

PS_PRCSRQSTNOTIFY

Application Messaging queue data

 

--  core tables:

              PSAPMSGPUBHDR

              PSAPMSGPUBDATA

              PSAPMSGPUBCON

              PSAPMSGSUBCON

              PSAPMSGPUBERR

              PSAPMSGPUBERRP

              PSAPMSGPUBCERR

              PSAPMSGPUBCERRP

              PSAPMSGSUBCERR

              PSAPMSGSUBCERRP

              PSAPMSGPCONDATA

              PSAPMSGSCONDATA

              PSIBERR

              PSIBERRP

              PSIBDEBUGLOG

              PSAPMSGIBATTR

              PSIBAEATTR

              PSIBRELMSGSEQ

              PSIBRELMSGHDR

              PSIBRELMSGDATA

              PSAPMSGDOMSTAT

              PSAPMSGDSPSTAT

 

-- synchronous core tables:

              PSIBLOGHDR

              PSIBLOGDATA

              PSIBLOGERR

              PSIBLOGERRP

              PSIBLOGIBINFO

 

-- archive tables:

              PSAPMSGARCHPH

              PSAPMSGARCHPD

              PSAPMSGARCHPC

              PSAPMSGARCHSC

              PSAPMSGARCHPT

              PSAPMSGARCHST

              PSIBLOGHDRARCH

              PSIBLOGDATAARCH

              PSIBLOGIBINFOAR

 

-- lock tables:

              PSIBFCLOCK

              PSIBADSLOCK

 

-- node tables:

              PSNODESDOWN

PeopleTools security tables

 

-- ACCESS PROFILES

               PSACCESSPROFILE

               PSACCESSPRFL

 

-- USERS

               PSOPRDEFN

               PSOPRDEFN_LANG

               PSOPRALIAS

               PSROLEUSER

               PSUSERATTR

               PSUSEREMAIL

               PSUSERPRSNLOPTN

               PS_ROLEXLATOPR

               PS_RTE_CNTL_RUSER

 

-- ROLES

               PSROLEDEFN

               PSROLEDEFNLANG

               PSROLECANGRANT

               PSROLECLASS

               PS_PTROLENAMEALIAS

 

-- PERMISSION LISTS

               PSCLASSDEFN

               PSCLASSDEFN_LNG

               PSAUTHSIGNON

               PSAUTHITEM

               PSAUTHPRCS

               PSAUTHCUBE

               PSAUTHMP

               PSAUTHBUSCOMP

               PSAUTHQUEUEMON

               PSPRCSPRFL

               PS_SCRTY_QUERY

               PS_SCRTY_ACC_GRP

               PSPTSCRTY_ADS_A

               PSAUTHOPTN

               PSAUTHWS

               PSPTSCRTY_ADS_P

               PS_SCRTY_SRCHGRP

               PS_MC_OPR_SECURITY

               PS_MC_OPRID

               PS_PTCLASSIDALIAS

 

-- DEFINITION SECURITY

               PSPTDEFSEC_GRPS

               PSPTDEFSEC_GRP

               PSPTDEFSECINRL

               PS_APP_DES_OBJ_CST

               PSOPROBJ

 

-- PERSONALIZATIONS

               PSUSEROPTNDEFN

               PSUSEROPTNLANG

               PSOPTNCATGRPLNG

               PSOPTNCATGRPTBL

               PSOPTNCATTBL

               PSOPTNCATLANG

 

-- SECURITY OPTIONS

               PSSECOPTIONS

               PS_PTALIASOPTIONS

 

-- SECURITY LINKS

               PSUSEROTHER

               PSUSEROTHER_L

               PSUSERSELFOTHER

               PSUSERSELFOTH_L

               PSROLEOTHER

               PSROLEOTHER_LNG

               PSPERMLISTOTHER

               PSPERMLISTOTH_L

 

-- USER ID TYPES

               PSOPRALIASTYPE

               PSOPRALIASFIELD

 

-- DELETE USER BYPASS TABLE

               PS_BYPASS_TABLE

 

-- FORGOT EMAIL TEXT

               PSPSWDEMAIL

               PSPSWDEMAILLANG

 

-- PASSWORD HINTS

               PSPSWDHINT

               PSPSWDHINT_LANG

 

-- SIGNON PEOPLECODE

               PSSIGNONPPC

 

-- DIRECTORY

               PSDSDIR

               PSDSSRVR

               DSCONNECTID

               PSDSEXT_INSTALL

               PSDSSECMAPMAIN

               PSDSSECMAPSRVR

               DSUSRPRFLMAP

               PSDSUSERPRFL

               PSDSSECROLERULE

               DSSRCH_SBR

               DSSRCHATTR

               DSSECFILTER

               PT_WF_NOT_DSCFG

 Table PSINDEXDEFN - Index Definition

  Row in PSINDEXDEFN - Index Definition

    RECNAME - Record (Table) Name: TL_IPT2

    INDEXID - Index Identifier: Z

    INDEXTYPE - Index Type: 4

    UNIQUEFLAG - Index Unique: 0

    CLUSTERFLAG - Index Cluster Flag: 0

    ACTIVEFLAG - Index Active: 1

    CUSTKEYORDER - Custom Key Order: 1

    KEYCOUNT - Number of Keys: 1

    DDLCOUNT - Number of DDL Override Parms: 0

    PLATFORM_SBS - SQLBase: 0

    PLATFORM_DB2 - DB2/MVS: 1

    PLATFORM_ORA - Oracle: 1

    PLATFORM_INF - Informix: 0

    PLATFORM_DBX - DB2 for Unix: 0

    PLATFORM_ALB - AllBase: 0

    PLATFORM_SYB - Sybase Sql Server: 0

    PLATFORM_MSS - MicroSoft Sql Server: 1

    PLATFORM_DB4 - DB2/400: 0

    IDXCOMMENTS - INDEX COMMENTS: Index on emplid

 

Table PSIDXDDLPARM - Index DDL Parameters

  (No Rows)

 

Table PSKEYDEFN - Key in Index Definition

  Row in PSKEYDEFN - Key in Index Definition

    RECNAME - Record (Table) Name: TL_IPT2

    INDEXID - Index Identifier: Z

    KEYPOSN - Key Position: 1

    FIELDNAME - Field Name: EMPLID

    ASCDESC - Ascending/Descending: 1

 Set Up HCM  Common Definitions  Text Catalog and Notepad > Maintain Text Catalog

select * from ps_HR_SSTEXT_MSGID  order by 1,2,3,4,5,6;

OBJECTOWNERID, HR_SSTEXT_SUB_ID, TEXT_ID, HR_SSTEXT_USAGE, SYSTEM_DATA_FLG, DESCR254_MIXED

select * from ps_HR_SSTEXT_TEXT order by 1,2,3,4 desc,5,6,7,8;  

select -- p1.OBJECTVALUE1, p1.OBJECTVALUE2,p1.OBJECTVALUE3, p1.OBJECTVALUE4,p1.OBJECTVALUE5,OBJECTID6, OBJECTVALUE6,'~`~`~`', PCTEXT,'~`~`~`',OBJECTID1,OBJECTID2,OBJECTID3,OBJECTID4,OBJECTID5,

msg.*,

p1.OBJECTID1, p1.OBJECTVALUE1, p1.OBJECTID2, p1.OBJECTVALUE2, p1.OBJECTID3, p1.OBJECTVALUE3, p1.OBJECTID4, p1.OBJECTVALUE4, OBJECTID5, OBJECTVALUE5, OBJECTID6, OBJECTVALUE6, OBJECTID7, OBJECTVALUE7, p1.PROGSEQ, HASH_SIGNATURE,

'~`~`~`',PCTEXT,'~`~`~`'

from PSMSGCATDEFN msg left outer join pspcmtxt p1

 on p1.pctext like '%' || MESSAGE_SET_NBR || ', ' || MESSAGE_NBR || ',%'

where (MESSAGE_SET_NBR =1000 and MESSAGE_NBR in (1573,1574))

select * from PSMSGCATDEFN ;

select * from PSMSGSETDEFN    where message_set_nbr in (1000,18031,18137,2000,2001,2002,2003,3000,3001,4002,6568) order by 1,2,3,4;

select * from   PSMSGCATDEFN  where (MESSAGE_SET_NBR =1000 and MESSAGE_NBR in (1573,1574);

select * from PSMSGCATLANG          where message_set_nbr in (1000,18031,18137,2000,2001,2002,2003,3000,3001,4002,6568) order by 1,2,3,4;;

select * from PSMSGSETLANG          where message_set_nbr in (1000,18031,18137,2000,2001,2002,2003,3000,3001,4002,6568) order by 1,2,3,4;;

select * from PSMGRSHADOW          where message_set_nbr in (1000,18031,18137,2000,2001,2002,2003,3000,3001,4002,6568) order by 1,2,3,4;;;

select * from    PSMSGCATLANG where (MESSAGE_SET_NBR =1000 and MESSAGE_NBR in (1573,1574)

Project Items

Message Catalog in a Project

SELECT  B.PROJECTNAME,

  TO_CHAR(CAST((C.LAST_UPDATE_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), C.MESSAGE_SET_NBR, C.MESSAGE_NBR, C.MESSAGE_TEXT, C.MSG_SEVERITY, C.DESCRLONG

  FROM ((PSPROJECTDEFN A 

  INNER JOIN  PSPROJECTITEM B ON  A.PROJECTNAME = B.PROJECTNAME ) 

   INNER JOIN  PSMSGCATDEFN C ON B.OBJECTVALUE1 = TO_CHAR(C.MESSAGE_SET_NBR)

        AND  TO_CHAR(C.MESSAGE_NBR) = B.OBJECTVALUE2

  )

  WHERE A.PROJECTNAME like '%project_name%'; 

PeopleCode in a project

 select  PROJECTNAME ,p1.OBJECTVALUE1, p1.OBJECTVALUE2,p1.OBJECTVALUE3, 

 p1.OBJECTVALUE4,p1.OBJECTVALUE5, OBJECTID6, OBJECTVALUE6,

 '~`~`~`', PCTEXT,'~`~`~`' 

 from pspcmtxt p1,psprojectitem p2 

where 

PROJECTNAME like 'AF%ANA%' and

p1.OBJECTVALUE1 = p2.OBJECTVALUE1 and

p1.OBJECTVALUE2 = p2.OBJECTVALUE2 and

p1.OBJECTVALUE3 = p2.OBJECTVALUE3 ;

select * from psprojectitem p2 where PROJECTNAME like '%project_name%'and OBJECTTYPE = 30;

definition type (Message Catalog Entries) that will not import into releases prior to 8.58 (75,20)

definition type (File References) that will not import into releases prior to 8.44 (75,20)

definition type (Trees) that will not import into releases prior to 8.58 (75,20)

definition type (Access Groups) that will not import into releases prior to 8.58 (75,20)

definition type (Roles) that will not import into releases prior to 8.58 (75,20)

 freeform stylesheets that will import as blank in pre 8.50 databases (75,27)

definition type (File References) that will not import into releases prior to 8.44 (75,20)

definition type (File Type Codes) that will not import into releases prior to 8.44 (75,20)

definition type (Services) that will not import into releases prior to 8.48 (75,20)

definition type (Service Operations) that will not import into releases prior to 8.48 (75,20)

definition type (Service Operation Handlers) that will not import into releases prior to 8.48 (75,20)

definition type (Service Operation Versions) that will not import into releases prior to 8.48 (75,20)

definition type (BIP Template Definitions) that will not import into releases prior to 8.48 (75,20)

definition type (BIP Report Definitions) that will not import into releases prior to 8.48 (75,20)

definition type (BIP File Definitions) that will not import into releases prior to 8.48 (75,20)

definition type (BIP Data Source Definitions) that will not import into releases prior to 8.48 (75,20)

definition type (Message Schemas) that will not import into releases prior to 8.50 (75,20)

definition type (Data Set Definitions) that will not import into releases prior to 8.51 (75,20)

definition type (Integration Groups) that will not import into releases prior to 8.52 (75,20)

SELECT 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,

  

  

 case  SOURCESTATUS 

when  0 then 'Unknown'

when 1 then 'Absent'

when 2 then 'Changed'

when 3 then 'Unchanged'

when 4 then '*Changed'

when 5 then '*Unchanged'

when 6 then 'Same' ELSE ' ' END SOURCESTATUS,

case TARGETSTATUS 

when 0 then 'Unknown'

when 1 then 'Absent'

when 2 then 'Changed'

when 3 then 'Unchanged'

when 4 then '*Changed'

when 5 then '*Unchanged'

when 6 then 'Same' ELSE ' ' END TARGETSTATUS,

case UPGRADEACTION 

when 0 then 'Copy'

when 1 then 'Delete'

when 2 then 'None'

when 3 then 'Copy Properties' ELSE ' ' END UPGRADEACTION,

case TAKEACTION 

when 0 then 'No'

when 1 then 'Yes' ELSE ' ' END TAKEACTION,

case COPYDONE 

when 0 then 'No'

when 1 then 'Yes' ELSE ' ' END COPYDONE

  ,I.*

  

  

FROM PSPROJECTITEM I

WHERE 

projectname in ('proj_name') 

and not (UPGRADEACTION=2 and TARGETSTATUS = 6 and  SOURCESTATUS = 6 and TAKEACTION = 0) /* to find changes */

ORDER BY PROJECTNAME,I.OBJECTTYPE, 2, I.OBJECTVALUE1, I.OBJECTVALUE2, I.OBJECTVALUE3, I.OBJECTVALUE4;

Pagelets

 I am just exploring Pagelets and Fluid.

Sharing what I see.

Structure and Content

Home Page

Navigation Collection

Pagelets

 Pagelet Branding

Navigation Collection

Manage Dashboard

Dashboard --> Layout

Abs Cal Creation

 

To Find Queries run by a User

 To Find Queries run by a User

EXECCOUNT or count(PRCSINSTANCE) can give how many times it was run recently.

LASTEXECDTTM or EXECDTTM or begindttm can give when it was last executed.

oprid or runoprid can give who ran it.

Using the PSQRYSTATS (Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SELECT B.QRYNAME, B.DESCR,  A.EXECCOUNT,   TO_CHAR(CAST((A.LASTEXECDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI'),

A.OPRID,B.QRYTYPE, A.QRYNAME, A.AVGEXECTIME, A.AVGFETCHTIME, A.AVGNUMROWS, A.NUMKILLS

  FROM (PSQRYSTATS A 

  LEFT OUTER JOIN  PSQRYDEFN B ON  A.OPRID = B.OPRID AND A.QRYNAME = B.QRYNAME )

  where a.oprid = 'userId' and lastexecdttm >= '01-Jan-2021'

  order by execcount desc,a.lastexecdttm desc

  ;

Using the PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher).

  

  select B.QRYNAME, B.DESCR,0,EXECDTTM, a.RUNOPRID, a.OPRID,a.* 

  from PSQRYEXECLOG A 

  LEFT OUTER JOIN  PSQRYDEFN B ON -- A.OPRID = B.OPRID AND 

  A.QRYNAME = B.QRYNAME 

  where a.runoprid = 'userId'  and execdttm >= '01-Jan-2021'

  order by  a.execdttm desc;

Using the PSPRCSRQST

  

select B.QRYNAME, B.DESCR,count(PRCSINSTANCE) ,max(   TO_CHAR(CAST((a.begindttm) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI')),a.OPRID , a.RUNCNTLID

from psprcsrqst A 

 LEFT OUTER JOIN  PSQRYDEFN B ON -- A.OPRID = B.OPRID AND 

  A.RUNCNTLID = B.QRYNAME 

where   A.prcsname = 'PSQUERY' AND A.oprid = 'userId'

  -- and  a.PRCSINSTANCE =     (select MAX( C1.PRCSINSTANCE) from PSPRCSRQST C1 where C1.RUNCNTLID = a.RUNCNTLID)

 group by B.QRYNAME, B.DESCR, a.RUNCNTLID,a.OPRID

 order by 3 desc,1;

PS_PRCSRUNCNTLEOPT is another record which stores data of the email ids,  etc.

One more.. to pull to whom the query sends info

SELECT A.PRCSINSTANCE, TO_CHAR(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.RUNCNTLID

-- ,(COUNT(E.OPRID) + COUNT(F.OPRID))

,B.DISTID, B.DISTIDTYPE,   D.ROLENAME, D.DYNAMIC_SW

,E.OPRID, E.EMAILID, E.ACCTLOCK, F.OPRID, F.EMAILID, F.ACCTLOCK

  FROM PSPRCSRQST A,    -- Main Record 

  (((PS_PRCSRQSTDIST B  -- Dist Record

      LEFT OUTER JOIN  PSROLEUSER D ON  D.ROLENAME = B.DISTID )   -- Role User

      LEFT OUTER JOIN  PSOPRDEFN E ON  E.OPRID = D.ROLEUSER AND E.ACCTLOCK = 0 )  -- Role User & Email

      LEFT OUTER JOIN  PSOPRDEFN F ON  F.OPRID = B.DISTID AND F.ACCTLOCK = 0 )  -- Direct user hard code

  WHERE ( A.PRCSINSTANCE = B.PRCSINSTANCE AND A.RUNCNTLID = '~RUNCNTLID ~'

     AND A.PRCSINSTANCE = (SELECT MAX( C.PRCSINSTANCE)

      FROM PSPRCSRQST C

  WHERE C.RUNCNTLID = A.RUNCNTLID));

To Find Messages with Fields

To Find Messages with Fields 

select DISTINCT X.RECNAME,b.recdescr,

CASE b.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(b.RECTYPE)

END  

,a.fieldname

, x.msgname,x.apmsgver ,p.ib_operationname,P.PARAMETERNAME,versionname,h.handlername,h.version

,Y.PACKAGEROOT,Y.APPCLASSID,Y.APPCLASSMETHOD

,h.active_flag from

psdbfield db,psrecfield a,psrecdefn b,psmsgrec x

  left outer join  psoprverdfnparm p on p.msgname=x.msgname and p.ib_msgversion=x.apmsgver

  left outer join PSOPRHDLR H on  H.IB_OPERATIONNAME=P.IB_OPERATIONNAME

  left outer join psoperationAC Y on H.IB_OPERATIONNAME=Y.IB_OPERATIONNAME

where --x.recname in (select distinct a.recname from psrecfield a,psrecdefn b where

--a.fieldname in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA') and a.recname=b.recname

--and b.rectype in ('0','1','2')

--)

x.recname=a.recname

and a.fieldname=db.fieldname

and x.recname=b.recname

and a.recname=x.recname

and a.fieldname in ('flds')

-- and x.msgname in ('HIRE_JOB_DETAIL_RESPONSE_TIP','SPECL_PROJECT_SYNC','SPECL_PROJECT_SYNC')

and not exists (select 'y' from psmsgfldovr z

      where  z.msgname=x.msgname and

      z.fieldname in ('flds') ) ;

To Find Navigation of a process which has a Field in Temp Table in A.E.

To Find Navigation of a process which has a Field in Temp Table in A.E.

 SELECT fld.FIELDNAME,fld.DESCRLONG, CASE fld.FIELDTYPE

                WHEN 0 THEN 'Character'

                WHEN 1 THEN 'Long Character'

                WHEN 2 THEN 'Number'

                WHEN 3 THEN 'Signed Number'

                WHEN 4 THEN 'Date'

                WHEN 5 THEN 'Time'

                WHEN 6 THEN 'DateTime'

                WHEN 8 THEN 'Image'

                WHEN 9 THEN 'Image Reference'

                ELSE TO_CHAR(fld.FIELDTYPE)

        END  fld_FIELDTYPE, recFld.RECNAME, recFld.FIELDNUM, recFld.EDITTABLE, recFld.SUBRECORD, recDefn.RECTYPE, recDefn.RECDESCR, recDefn.DESCRLONG,CASE recDefn.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(recDefn.RECTYPE)

END  rec_RECTYPE,

  

  

   -- stRec.AE_APPLID, stRec.AE_STATE_RECNAME, stRec.AE_DEFAULT_STATE,  

   temp.AE_APPLID, temp.RECNAME, '',

   aeDef.AE_APPLID, aeDef.DESCR, aeDef.AE_APPLLIBRARY, aeDef.AE_DISABLE_RESTART, aeDef.AEPROGTYPE, aeDef.DESCRLONG, 

   aeRqst.OPRID, aeRqst.RUN_CNTL_ID, aeRqst.AE_PROCESS_STATUS, aeRqst.PROCESS_INSTANCE, 

   TO_CHAR(CAST((aeRqst.LAST_RUN_DTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

   prcsDefn.PRCSTYPE, prcsDefn.PNLGRPNAME, prcsNav.PORTAL_NAME, prcsNav.DESCR254, prcsNav.PORTAL_URI_SEG1, prcsNav.PORTAL_URI_SEG2, prcsNav.PORTAL_URI_SEG3, 

   prcsNav.PORTAL_URI_SEG4, prcsNav.FLUIDMODE, prcsNav.SMALLFFOPT, prcsNav.PORTAL_URLTEXT, prcsRun.PRCSINSTANCE, prcsRun.OPRID, 

   TO_CHAR(CAST((prcsRun.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

   TO_CHAR(CAST((prcsRun.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), prcsRun.RUNCNTLID

  FROM (((((((((PSDBFIELD fld 

  LEFT OUTER JOIN  PSRECFIELD B ON  fld.FIELDNAME = recFld.FIELDNAME )

  LEFT OUTER JOIN  PSRECDEFN C ON  recFld.RECNAME = recDefn.RECNAME )                                               

  LEFT OUTER JOIN  PSAEAPPLTEMPTBL temp ON  temp.RECNAME = recDefn.RECNAME )                                        

  LEFT OUTER JOIN  PSAEAPPLDEFN aeDef ON  temp.AE_APPLID = aeDef.AE_APPLID )                                        

  LEFT OUTER JOIN  PS_AEREQUESTTBL aeRqst ON  aeDef.AE_APPLID = aeRqst.AE_APPLID )                                  

  LEFT OUTER JOIN  PS_PRCSDEFNPNL prcsDefn ON  prcsDefn.PRCSNAME = aeDef.AE_APPLID )                                

  LEFT OUTER JOIN  PSPRSMDEFN prcsNav ON  prcsNav.PORTAL_URI_SEG2 = prcsDefn.PNLGRPNAME )                           

  LEFT OUTER JOIN  PSPRCSRQST prcsRun ON  prcsDefn.PRCSTYPE = prcsRun.PRCSTYPE AND prcsDefn.PRCSNAME = prcsRun.PRCSNAME AND prcsRun.OPRID = 'U' )     PSPRCSRQST prcsRun

  

  )

  WHERE ( fld.FIELDNAME in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA'))

To Find Navigation of a process which has a Field in State Record in A.E.

SELECT fld.FIELDNAME,fld.DESCRLONG, CASE fld.FIELDTYPE

                WHEN 0 THEN 'Character'

                WHEN 1 THEN 'Long Character'

                WHEN 2 THEN 'Number'

                WHEN 3 THEN 'Signed Number'

                WHEN 4 THEN 'Date'

                WHEN 5 THEN 'Time'

                WHEN 6 THEN 'DateTime'

                WHEN 8 THEN 'Image'

                WHEN 9 THEN 'Image Reference'

                ELSE TO_CHAR(fld.FIELDTYPE)

        END  fld_FIELDTYPE, recFld.RECNAME, recFld.FIELDNUM, recFld.EDITTABLE, recFld.SUBRECORD, recDefn.RECTYPE, recDefn.RECDESCR, recDefn.DESCRLONG,CASE recDefn.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(recDefn.RECTYPE)

END  rec_RECTYPE,

  

  

   stRec.AE_APPLID, stRec.AE_STATE_RECNAME, stRec.AE_DEFAULT_STATE, 

   aeDef.AE_APPLID, aeDef.DESCR, aeDef.AE_APPLLIBRARY, aeDef.AE_DISABLE_RESTART, aeDef.AEPROGTYPE, aeDef.DESCRLONG, 

   aeRqst.OPRID, aeRqst.RUN_CNTL_ID, aeRqst.AE_PROCESS_STATUS, aeRqst.PROCESS_INSTANCE, 

   TO_CHAR(CAST((aeRqst.LAST_RUN_DTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

   prcsDefn.PRCSTYPE, prcsDefn.PNLGRPNAME, prcsNav.PORTAL_NAME, prcsNav.DESCR254, prcsNav.PORTAL_URI_SEG1, prcsNav.PORTAL_URI_SEG2, prcsNav.PORTAL_URI_SEG3, 

   prcsNav.PORTAL_URI_SEG4, prcsNav.FLUIDMODE, prcsNav.SMALLFFOPT, prcsNav.PORTAL_URLTEXT, prcsRun.PRCSINSTANCE, prcsRun.OPRID, 

   TO_CHAR(CAST((prcsRun.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

   TO_CHAR(CAST((prcsRun.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), prcsRun.RUNCNTLID

  FROM (((((((((PSDBFIELD fld 

  LEFT OUTER JOIN  PSRECFIELD B ON  fld.FIELDNAME = recFld.FIELDNAME )

  LEFT OUTER JOIN  PSRECDEFN C ON  recFld.RECNAME = recDefn.RECNAME )                                           

  LEFT OUTER JOIN  PSAEAPPLSTATE stRec ON  stRec.AE_STATE_RECNAME = recDefn.RECNAME )                           

  LEFT OUTER JOIN  PSAEAPPLDEFN aeDef ON  stRec.AE_APPLID = aeDef.AE_APPLID )                                   

  LEFT OUTER JOIN  PS_AEREQUESTTBL aeRqst ON  aeDef.AE_APPLID = aeRqst.AE_APPLID )                              

  LEFT OUTER JOIN  PS_PRCSDEFNPNL prcsDefn ON  prcsDefn.PRCSNAME = aeDef.AE_APPLID )                            

  LEFT OUTER JOIN  PSPRSMDEFN prcsNav ON  prcsNav.PORTAL_URI_SEG2 = prcsDefn.PNLGRPNAME )                       

  LEFT OUTER JOIN  PSPRCSRQST prcsRun ON  prcsDefn.PRCSTYPE = prcsRun.PRCSTYPE AND prcsDefn.PRCSNAME = prcsRun.PRCSNAME AND prcsRun.OPRID = 'U' )     

  

  )

  WHERE ( fld.FIELDNAME in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA'))

To Find the Navigation of recently run processes.

To Find the Navigation of recently run processes - just the url. 

The PORTAL_URLTEXT can be used to paste in the browswer address bar and we can directly go to the component. We can get PORTAL_URI_SEG1 Menu, PORTAL_URI_SEG2 Component from the 

PSPRSMDEFN table.

SELECT prcsCmp.PNLGRPNAME,PORTAL_URLTEXT,prcsRun.prcstype,cmpNav.descr254,

prcsRun.* 

FROM psprcsrqst prcsRun,   

ps_PRCSDEFNPNL prcsCmp,    -- Process Components... can be more than one.

PSPRSMDEFN cmpNav             -- Component Path... navigation.

WHERE 

prcsRun.prcsname in 

('xxx')

AND 

(begindttm =(SELECT MAX(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)

OR  begindttm =(SELECT MIN(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)

)

AND prcsRun.prcsname = prcsCmp.prcsname 

and PORTAL_URI_SEG2 = prcsCmp.PNLGRPNAME;

With Navigation

This below query gives navigation as well as the url. The text in purple can be used to get navigation of any component. Since its not recursive, it can be used to fetch more than one component at a time.

SELECT distinct A.PRCSTYPE, A.PRCSNAME, B.PNLGRPNAME, c.oprid,

TO_CHAR(CAST((C.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 

TO_CHAR(CAST((C.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), C.RUNCNTLID, 

A.DESCR, dbms_lob.substr ( A.DESCRLONG,2000,1 ), A.RESTARTENABLED, D.DESCR254, D.PORTAL_URI_SEG1 Menu, 

D.PORTAL_URI_SEG2 Component, 

dbms_lob.substr ( d.PORTAL_URLTEXT,2000,1 )   -- without this we cant apply distinct as its a clob

,  C.PRCSINSTANCE

,h.PORTAL_LABEL || ' > ' || g.PORTAL_LABEL || ' > ' || f.PORTAL_LABEL || ' > '  

|| e.PORTAL_LABEL || ' > '  ||  d.PORTAL_LABEL PATH_TO_COMPONENT

  FROM (((

  ((((

  PS_PRCSDEFN A  -- gets the process

  LEFT OUTER JOIN  PS_PRCSDEFNPNL B     ON  A.PRCSTYPE = B.PRCSTYPE AND A.PRCSNAME = B.PRCSNAME )  -- gets the component

  LEFT OUTER JOIN  PSPRSMDEFN D     ON  D.PORTAL_URI_SEG2    = B.PNLGRPNAME -- component name

    

    LEFT OUTER JOIN  PSPRSMDEFN E ON  D.PORTAL_NAME = E.PORTAL_NAME AND D.PORTAL_PRNTOBJNAME  = E.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN F ON  E.PORTAL_NAME = F.PORTAL_NAME AND  E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN G ON  F.PORTAL_NAME = G.PORTAL_NAME AND  F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME ) 

    LEFT OUTER JOIN  PSPRSMDEFN H ON  G.PORTAL_NAME = H.PORTAL_NAME AND  G.PORTAL_PRNTOBJNAME = H.PORTAL_OBJNAME )  -- gets the navigation

        ) 

  LEFT OUTER JOIN  PSPRCSRQST C     ON  A.PRCSTYPE = C.PRCSTYPE AND A.PRCSNAME = C.PRCSNAME 

       and  C.PRCSINSTANCE =     (select MAX( C1.PRCSINSTANCE) from PSPRCSRQST C1 where C1.prcsname = c.prcsname) -- gets one per type

    )

where 

a.prcsname in ( :prcsname

)      

    

    order by 3,1,2

    ;

Get all processes run by a user id and the approx. navigation for the latest run.

SELECT distinct prcsRun.prcsname,prcsCmp.PNLGRPNAME,prcsRun.prcstype  ,to_char(cmpNav.PORTAL_URLTEXT) -- navigation url

--,cmpNav.descr254

 ,prcsRun.* 

FROM psprcsrqst prcsRun,   

ps_PRCSDEFNPNL prcsCmp    -- Process Components... can be more than one.

 ,PSPRSMDEFN cmpNav             -- Component Path... navigation.

WHERE 

prcsRun.oprid = '%user%' and

-- prcsRun.prcsname in ('xxx') -- if required by process

-- AND 

 (begindttm =(SELECT MAX(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid)  -- for the latest run

-- OR  begindttm =(SELECT MIN(begindttm) FROM psprcsrqst a1 WHERE prcsRun.prcsname = a1.prcsname AND prcsRun.oprid = a1.oprid) -- for the earliest run

 )

  AND

prcsRun.prcsname = prcsCmp.prcsname 

 and cmpNav.PORTAL_URI_SEG2 = prcsCmp.PNLGRPNAME

order by prcsRun.prcsname;