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;