What Environment Am I in?

 Development, PeopleSoft, PeopleTools  9 Responses »
Jul 102009
 

As I was going through some of the sessions on the QUEST NORTHEAST CONFERENCE I noticed on of the presenters mentioning customizing Test environments Main PeopleSoft CSS PSSTYLEDEF so that the users are always able to distinguish between environments.

The solution that I have employ to solve this problem is to modify the PT_BRANDING:BrandingBase class to dynamically display environment name based on the URL, like this:

captured_Image.png

PT_BRANDING Application package was meant to be used to dynamically customize the look and feel of you application and of course you can do a lot more with this but this is just a very simple example of how easy this can be done.

In order to achieve the above effect all you need is to add code to 2 methods in the class PT_BRANDING:BrandingBase

method GetUniHeaderHTML
method GetUniHeaderHTML_PIA

These methods, as the name suggests are responsible for rendering you Application Portal Header.

/*Iouri Chadour - Added to put Test Environment notification in the header&Request.ContentURI - returns your portal URL in form http://server:port/psp/PSPRC/Left(Right(&Request.ContentURI, 4), 3) - extracts the significant piece to displayon the header*/
&zEnvName = Left(Right(&Request.ContentURI, 4), 3);
&Greeting = &zEnvName | " Environment " | ", UserID:" | %UserId | ", Name: " | %UserDescription;
/* End Mod */

/* This code below is delivered checks for client browser to ensure that Header is displayed correctly */

If &navTheme.isNS4x() Then    &UniHeaderHTMLPIA = GetHTMLText(HTML.PORTAL_UNI_HEADER_NS4X, &Response.GetImageURL(Image.NEW_PS_LOGO), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBRCRV), " ", " ", &FavoritesHTML, &AddToFavoritesHTML, &HelpHTML, "", &Response.GetImageURL(Image.NEW_PORTAL_HDR_CRV), &Response.GetImageURL(Image.NEW_PORTAL_HDR_BG), &Response.GetImageURL(Image.NEW_PORTAL_HDR_BG), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBRCRV), &Response.GetImageURL(Image.NEW_PORTAL_HDR_SHD), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBR), &styleSheet, &homeURL, &startURL, &AddToFavFormHTML, &LogoutURL, MsgGetText(95, 400, "Return Home"), /*20*/MsgGetText(95, 401, "Home"), MsgGetText(95, 402, "Return to Menu"), MsgGetText(95, 403, "Menu"), MsgGetText(95, 408, "Sign out"), &Greeting, &SearchHTML, &ColSpanHTML, &PersonalizeHTML, "", "", &HelpJSHTML, &homepageJS, "", &TabHTML, &WLHTML, &domainScript, &Response.GetJavaScriptURL(HTML.PT_SAVEWARNINGSCRIPT), "", &SaveWarnCrossDomainScript, &Response.GetImageURL(Image.PT_HOME_TAB_ACTIVE_CENTER), &Response.GetImageURL(Image.PT_HOME_TAB_INACTIVE_CENTER), /*40*/&Response.GetImageURL(Image.PT_HOME_TAB_LINE), %Request.ExpireMeta, &CTIHTML, &MCFHTML, &PPMHTML, &Charset);ElseElse    &UniHeaderHTMLPIA = GetHTMLText(HTML.PORTAL_UNI_HEADER_NNS, &Response.GetImageURL(Image.NEW_PS_LOGO), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBRCRV), "", "", &FavoritesHTML, &AddToFavoritesHTML, &HelpHTML, "", &Response.GetImageURL(Image.NEW_PORTAL_HDR_CRV), &Response.GetImageURL(Image.NEW_PORTAL_HDR_BG), &Response.GetImageURL(Image.NEW_PORTAL_HDR_BG), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBRCRV), &Response.GetImageURL(Image.NEW_PORTAL_HDR_SHD), &Response.GetImageURL(Image.NEW_PORTAL_HDR_TBR), &styleSheet, &homeURL, &startURL, &AddToFavFormHTML, &LogoutURL, MsgGetText(95, 400, "Return Home"), /*20*/MsgGetText(95, 401, "Home"), MsgGetText(95, 402, "Return to Menu"), MsgGetText(95, 403, "Menu"), MsgGetText(95, 408, "Sign out"), &Greeting, &SearchHTML, &ColSpanHTML, &PersonalizeHTML, "", "", &HelpJSHTML, &homepageJS, MsgGetText(95, 138, "Tool Bar Header"), &TabHTML, &WLHTML, &domainScript, &Response.GetJavaScriptURL(HTML.PT_SAVEWARNINGSCRIPT), "", &SaveWarnCrossDomainScript, &Response.GetImageURL(Image.PT_HOME_TAB_ACTIVE_CENTER), &Response.GetImageURL(Image.PT_HOME_TAB_INACTIVE_CENTER), /*40*/&Response.GetImageURL(Image.PT_HOME_TAB_LINE), %Request.ExpireMeta, &CTIHTML, &MCFHTML, &PPMHTML, &Charset); End-If;
 Posted by Iouri Chadour at 10:44 pm  Tagged with: , ,

PeopleTools 8.50 Overview

 PeopleTools  2 Responses »
Jun 122009
 

Here is a link to an excellent presentation from Oracle of PeopleTools 8.50 features. PeopleSoft is really going Web 2.0!

peoplesoft_workspace_and_pt850.swf (application/x-shockwave-flash Object)

Also please see our previous post about Tools 8.50 overview

 Posted by David Quintman at 11:23 pm  Tagged with: ,

DB2 Escape characters in queries

 DB2  No Responses »
Feb 182009
 

Recently I had to use escape characters in DB2 queries and after looking through documentation here is a simple way to do this. You can define you escape character using 

{escape '\'}

 

right after the statement your are trying to use it, here is a sample statement:

select oprid, oprdefndesc
from psoprdefn
where oprid like '%\_%' {escape '\'}
 Posted by Iouri Chadour at 12:05 am  Tagged with: ,

Getting Information about PeopleSoft Components from Database

 PeopleSoft, PeopleTools  No Responses »
Jun 032008
 

SQLs below have proved to be great while working behind the scenes and creating report about PeopleSoft customizations. These are quick ways to get behind the scenes and see the objects the way PeopleSoft sees it.

Getting fields and records for a page:

select OCCURSLEVEL as level ,FIELDNUM as taborder, LBLTEXT as label ,
RECNAME as record ,FIELDNAME as field
from PSFSSYS.PSPNLFIELD
where PNLNAME ='PORTAL_HPCOMP'order by OCCURSLEVEL, FIELDNUM

Get information about Project Compares :

SELECT PROJECTNAME, VERSION, PROJECTDESCR, TGTSERVERNAME, TGTDBNAME, TGTOPRID,
TGTOPRACCT, COMPRELEASE, SRCCOMPRELDTTM, TGTCOMPRELDTTM, COMPRELDTTM, KEEPTGT,
TGTORIENTATION, COMPARETYPE,
COMMITLIMIT, REPORTFILTER, MAINTPROJ, LASTUPDDTTM, LASTUPDOPRID, RELEASELABEL,
RELEASEDTTM, OBJECTOWNERID, DESCRLONG FROM PSPROJECTDEFN

Get Record Audit Flags:

SELECT RECNAME, FIELDNUM, FIELDNAME,
INTEGER(USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 as "ADD", 
INTEGER(USEEDIT / 128) - INTEGER(USEEDIT / 256)*2 as "CHANGE", 
INTEGER(USEEDIT / 1024) - INTEGER(USEEDIT/ 2048)*2  as "DELETE"
FROM PSRECFIELD 
     WHERE  RECNAME = 'PSOPRDEFN' 
             and USEEDIT <> 0       
             AND (INTEGER (USEEDIT / 128) - INTEGER (USEEDIT / 256) * 2 <> 0
             OR INTEGER (USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 <> 0
             OR INTEGER (USEEDIT / 1024) - INTEGER (USEEDIT / 2048) * 2 <> 0)
            ORDER BY RECNAME, FIELDNUM;

Get Record Field Types and Definitions:

-- GET RECORD NAME DEFINITION
  SELECT A.RECNAME, A.FIELDNAME,
         CASE
            WHEN B.FIELDTYPE = 0  THEN 'CHAR'
            WHEN B.FIELDTYPE = 1       THEN             'LONG CHAR'          
            WHEN B.FIELDTYPE = 2       THEN             'NUMBER'
            WHEN B.FIELDTYPE = 3       THEN             'SIGNED NBR'
            WHEN B.FIELDTYPE = 4       THEN             'DATE'
            WHEN B.FIELDTYPE = 5       THEN             'TIME'
            WHEN B.FIELDTYPE = 6       THEN             'DATETIME'
            WHEN B.FIELDTYPE = 7 OR B.FIELDTYPE = 8       THEN   'IMAGE'          
            ELSE  NULL       
         END  AS FIELDTYPE,
         CASE          
             WHEN B.FIELDTYPE = 2       
                  OR B.FIELDTYPE = 3  THEN  RTRIM (CHAR (B.LENGTH))|| '.'||CHAR (B.DECIMALPOS)          
             ELSE CHAR (B.LENGTH)       
         END  AS FLDLEN,
         CASE          
            WHEN (A.USEEDIT/ 256)/ 2 <>  (A.USEEDIT/ 256)/ 2.0       
                  THEN 'YES' ELSE 'NO'
         END  AS REQ,
         CASE          
             WHEN A.USEEDIT/ 2 <> A.USEEDIT/ 2.0       
                  THEN             'KEY'          
             WHEN (A.USEEDIT/ 2)/ 2 <> (A.USEEDIT/ 2)/ 2.0       
                  THEN             'DUP'
             WHEN (A.USEEDIT/ 16)/ 2 <>(A.USEEDIT/16)/ 2.0
                  THEN 'ALT'          
             ELSE NULL       
        END  AS KEY_TYPE, 
        CASE
            WHEN (A.USEEDIT / 64) / 2 <> (A.USEEDIT/64)/2.0       
                  THEN             'DESC'
            WHEN (A.USEEDIT / 2 <> A.USEEDIT / 2.0       
                  OR (A.USEEDIT / 2) / 2 <> (A.USEEDIT / 2) / 2.0       
                  OR (A.USEEDIT / 16) / 2 <> (A.USEEDIT / 16) / 2.0)       
                  AND (A.USEEDIT / 64) / 2 = (A.USEEDIT / 64) / 2.0       
                  THEN             'ASC'
           ELSE             NULL       
        END  AS DIR,
        CASE
            WHEN (A.USEEDIT / 2048) / 2 <> (A.USEEDIT / 2048) / 2.0       
                 THEN             'YES'
            ELSE             'NO'
        END  AS SRCH,
        CASE
           WHEN (A.USEEDIT / 32) / 2 <> (A.USEEDIT / 32) / 2.0
                THEN             'YES'
           ELSE             'NO'
        END  AS LIST,
        CASE          WHEN (A.USEEDIT / 4)/ 2 <>(A.USEEDIT / 4)/ 2.0       
            THEN             'YES'
            ELSE             'NO'
        END  AS SYS, 
        CASE
            WHEN A.DEFRECNAME = ''       
             THEN             A.DEFFIELDNAME
             ELSE             RTRIM(A.DEFRECNAME)|| '.'|| A.DEFFIELDNAME
        END  AS DEFAULT_VALUE, 
        CASE
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0       
                 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0       
            THEN             'A'
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0       
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0
            THEN             'AC'
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0
                  AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0       
            THEN             'ACD'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0
            THEN             'C'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0
                 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0
                 THEN             'CD'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0       
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0
           THEN             'D'
           ELSE             NULL       
           END  AS AUDT, CASE          WHEN (A.USEEDIT / 16384) / 2 <>          (A.USEEDIT / 16384) / 2.0       THEN             'PROMPT'          WHEN (A.USEEDIT / 512) / 2 <>          (A.USEEDIT / 512) / 2.0       THEN             'XLAT'          WHEN (A.USEEDIT / 8192) / 2 <>          (A.USEEDIT / 8192) / 2.0       THEN             'Y/N'          ELSE             NULL       END  AS EDIT, A.EDITTABLE  AS PROMPT_TABLE, A.SETCNTRLFLD  AS       SET_CONTROL_FLD, CASE          WHEN (A.USEEDIT / 4096) / 2 <>          (A.USEEDIT / 4096) / 2.0       THEN             'YES'          ELSE             'NO'       END  AS REASONABLE_DT, CASE          WHEN (A.USEEDIT       / 32768)       / 2 <>          (A.USEEDIT       / 32768)       / 2.0       THEN             'YES'          ELSE             'NO'       END  AS AUTO_UPDT, CASE          WHEN (A.USEEDIT / 262144) / 2 <> (A.USEEDIT / 262144) / 2.0       THEN             'FROM'          WHEN (A.USEEDIT / 524288) / 2 <> (A.USEEDIT / 524288) / 2.0       THEN             'THROUGH'          ELSE             NULL       END  AS SEARCH_FIELD, CASE          WHEN A.SUBRECORD = 'Y' THEN 'YES'          ELSE 'NO'       END  AS SUBRECORD, A.LASTUPDDTTM, A.LASTUPDOPRID  FROM PSRECFIELD A, PSDBFIELD B WHERE A.RECNAME = 'PSOPRDEFN' AND A.FIELDNAME = B.FIELDNAMEORDER BY FIELDNUM

Get Field Translate Values:

SELECT fieldvalue, eff_status, xlatlongname     FROM psxlatitem i     WHERE fieldname = 'RUNSTATUS'     AND effdt = (         SELECT max(effdt)         FROM   psxlatitem i1         WHERE  i1.fieldname = i.fieldname         AND    i1.fieldvalue = i.fieldvalue         AND    i1.effdt <= CURRENT DATE)    ORDER BY INTEGER(fieldvalue)

Get information about PS Queries:

-- Get Query DefinitionSELECT OPRID, QRYNAME, DESCR, VERSION, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID,       CREATEDTTM,  QRYDISABLED,       QRYFOLDER, DESCRLONG  FROM PSFSSYS.PSQRYDEFN    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Bind VariablesSELECT OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE       , LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT  FROM PSFSSYS.PSQRYBIND    where QRYNAME like '?%'    and oprid = '?'    with ur;

-- Get Query CriteriaSELECT OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL,       LCRTSELNUM, LCRTFLDNUM, CONDTYPE, EXPRTYPE, R1CRTSELNUM, R1CRTFLDNUM,       R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL,       QRYOJSELNUM  FROM PSFSSYS.PSQRYCRITERIA    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Output Field DefinitionsSELECT OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME,       FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING, COLUMNNUM, GROUPBYNUM,       ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE,       XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC  FROM PSFSSYS.PSQRYFIELD    where QRYNAME  like '?%'     with ur;

If you have more information about this kind of handy SQLs – please comment and I will try and keep this post up to date.

 Posted by Iouri Chadour at 5:20 pm  Tagged with: , ,

DB2 Space Management

 DB2  No Responses »
Mar 122008
 

Here are some very useful utility SQLs that will allow you to understand your space usage, system and other useful stuff:

DB2 Space Usage & Status

   1: -- to find tablespace status, type , usage run following SQL

   2: select substr(tablespace_name,1,20) Tablespace_name, 

   3:     case t.TBSPACETYPE 

   4:         when 'S' then 'System Managed'

   5:         when 'D' then 'Database managed'

   6:     end as TBSManaged, 

   7:     (used_pages/total_pages ) * 100 usedPCT, 

   8:     s.total_pages Total_pages, 

   9:     s.used_pages Used_pages, 

  10:     (s.total_pages - s.USABLE_PAGES ) Overhead_pgs, 

  11:     s.NUM_CONTAINERS No_of_containers,

  12:     s.free_pages Free_pages,

  13:     case t.DATATYPE 

  14:         when 'A' then ' ALL typ perm '

  15:         when 'L'  then ' Large '

  16:         when 'T'  then ' System Temp'

  17:         when 'U'  then ' User Temp '

  18:     end as TBStype, 

  19:     case s.TABLESPACE_STATE 

  20:         when 0 then ' Normal ' 

  21:         when 1 then ' Quiesced: SHARE ' 

  22:         when 2 then ' Quiesced: UPDATE ' 

  23:         when 4 then ' Quiesced: EXCLUSIVE ' 

  24:         when 8 then ' Load pending ' 

  25:         when 16 then ' Delete pending ' 

  26:         when 32 then ' Backup pending ' 

  27:         when 64 then ' Roll forward in progress ' 

  28:         when 128 then ' Roll forward pending ' 

  29:         when 256 then ' Restore pending ' 

  30:         when 512 then ' Disable pending ' 

  31:         when 1024 then ' Reorg in progress ' 

  32:         when 2048 then ' Backup in progress ' 

  33:         when 4096 then ' Storage must be defined ' 

  34:         when 8192 then ' Restore in progress ' 

  35:         when 16384 then ' Offline and not accessible ' 

  36:         when 32768 then ' Drop pending ' 

  37:         when 33554432 then ' Storage may be defined ' 

  38:         when 67108864 then ' Storage Definition is in (final) state ' 

  39:         when 134217728 then ' Storage Definition was changed prior to rollforward ' 

  40:         when 268435456 then ' DMS rebalancer is active ' 

  41:         when 536870912 then ' TBS deletion in progress ' 

  42:         when 1073741824 then ' TBS creation in progress ' end TABLESPACE_STATE 

  43: from table(snapshot_tbs_cfg(' ',-1)) as s, sysibm.systablespaces t where s.tablespace_name = t.tbspace ;

DB2 Unused Space

   1: -- GETTING WASTED SPACE

   2: select TBSPACE, ((80 - usedPCT) *  total_pages/100)*4/1000 SPACE_WASTED

   3: FROM

   4:     ( select substr(tablespace_name,1,20) TBSPACE,

   5:         total_pages,

   6:         used_pages,

   7:         free_pages, 

   8:         (used_pages*100/total_pages )  usedPCT

   9:     from table(snapshot_tbs_cfg(' ',-1)) as tbs ) 

  10: AS TABLE_SPACE

  11: WHERE usedPCT < 60

 Posted by Iouri Chadour at 4:52 pm  Tagged with: ,
 Older Entries
© 2011 WorkingScripts Suffusion theme by Sayontan Sinha

Switch to our mobile site