Finding a component in the Portal

The following SQL allows you to avoid the usual dog and pony chase when trying to find where a component is located in the PeopleSoft Portal.

SELECT --P.PORTAL_NAME,
       --P.PORTAL_REFTYPE,
       --P.PORTAL_OBJNAME,
       A.*, 
       P.PORTAL_LABEL,
       R.PATH,
       P.PORTAL_URLTEXT
FROM   
(
SELECT DISTINCT
       U.ROLEUSER  ,
       C.MENUNAME  ,
       C.PNLGRPNAME,
       C.MARKET
FROM   PSROLEUSER  U
JOIN   PSROLECLASS A
ON     A.ROLENAME = U.ROLENAME
JOIN   PSAUTHITEM  B
ON     B.CLASSID = A.CLASSID
JOIN   PSMENUITEM  C
ON     C.MENUNAME = B.MENUNAME
AND    C.BARNAME  = B.BARNAME
AND    C.ITEMNAME = B.BARITEMNAME 
) A
JOIN   PSPRSMDEFN P
ON     P.PORTAL_URI_SEG1 = A.MENUNAME
AND    P.PORTAL_URI_SEG2 = A.PNLGRPNAME
AND    P.PORTAL_URI_SEG3 = A.MARKET
JOIN   
(
SELECT CONNECT_BY_ROOT(PORTAL_NAME)    ROOT_NAME,
       CONNECT_BY_ROOT(PORTAL_REFTYPE) ROOT_REFTYPE,
       CONNECT_BY_ROOT(PORTAL_OBJNAME) ROOT_OBJNAME,
       PORTAL_OBJNAME,
       RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL),' > ')),' > ') PATH
FROM   PSPRSMDEFN
CONNECT 
BY NOCYCLE PRIOR PORTAL_NAME        = PORTAL_NAME
       AND PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME
)      R
ON     R.ROOT_NAME    = P.PORTAL_NAME
AND    R.ROOT_REFTYPE = P.PORTAL_REFTYPE
AND    R.ROOT_OBJNAME = P.PORTAL_OBJNAME
WHERE  A.ROLEUSER = 'username'
AND    P.PORTAL_NAME     = 'EMPLOYEE'
AND    P.PORTAL_REFTYPE  = 'C'
AND    R.PORTAL_OBJNAME  = 'PORTAL_ROOT_OBJECT'
AND    A.PNLGRPNAME      = 'component'

Thanks to Rob for this one.

Oracle Date Display Format

To change the format that a date is displayed in Oracle use the set NLS_DATE_FORMAT command. For example, if you wanted to show hours minutes and seconds with a date time stamp you could use: -

alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI'

before executing your query.

Gentleman Of Verona were amazing tonight. One of the best Belgian bands I've heard for years. Come to think of it, one of the best bands, period, I've heard in years.

High octane music delivered with cruise missile precision, by a band who were tight as a kangeroo's khyber, with a female vocalist who kicked ass bigtime.

Reminiscent of L7, Hole early Yeah, Yeah, Yeahs and Susie And The Banshees this is a band to watch out for in the future. Their new album Brutally Honest is out now on CD and, to be brutally honest, it's bloody brilliant. Their first album is available for download from iTunes and is also of a high quality. If you like good music, buy them!

Get Table Details

Sometimes when you're documenting you need to get a list of fields, fieldnames, types and their length. This little piece of SQL marries an Oracle system table and two PeopleSoft tables to furnish this information.

select ATC.COLUMN_ID,
       ATC.COLUMN_NAME,
       LAB.LONGNAME,
       CASE
         WHEN FLD.FIELDTYPE = 0 THEN 'Character'
         WHEN FLD.FIELDTYPE = 1 THEN 'Long Character'
         WHEN FLD.FIELDTYPE = 2 THEN 'Number'
         WHEN FLD.FIELDTYPE = 3 THEN 'Signed Number'
         WHEN FLD.FIELDTYPE = 4 THEN 'Date'
         WHEN FLD.FIELDTYPE = 5 THEN 'Time'
         WHEN FLD.FIELDTYPE = 6 THEN 'Date Time'
         WHEN FLD.FIELDTYPE = 8 THEN 'Image or Attachment'
         WHEN FLD.FIELDTYPE = 9 THEN 'ImageReference'
         ELSE                        'Unknown'
       END as TYPE,
       FLD.LENGTH
  from ALL_TAB_COLUMNS ATC,
       PSDBFLDLABL     LAB,
       PSDBFIELD       FLD
 where ATC.TABLE_NAME    = 'PS_JOB'
   and ATC.COLUMN_NAME   = LAB.FIELDNAME
   and LAB.DEFAULT_LABEL = 1
   and LAB.FIELDNAME     = FLD.FIELDNAME
 order by ATC.COLUMN_ID Asc

SQL to Find the Rules that SQL contain a Given SQL Object

select TL_RULE_ID
 from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'

Getting Details Of A Table's Columns

The following Oracle SQL gets the columns in a table.

-- -------------------
-- Get Table's Columns
-- -------------------
select COLUMN_NAME,
       DATA_TYPE
  from ALL_TAB_COLUMNS
 where TABLE_NAME = 'PS_TY_TL_SDF_EVT'

Changing PeopleSoft Passwords by the Back End

If the same password key is used across multiple PeopleSoft environments you can set the password in one account equal to that in another by creating a database link between the two and using the following SQL: -

update PSOPRDEFN@HR89XXX OPXXX
   set OPXXX.OPERPSWD = ( select OP.OPERPSWD
                            from PSOPRDEFN OP
                           where OPXXX.OPRID = OP.OPRID
                        ),
       OPXXX.ACCTLOCK = 0,
       OPXXX.LASTPSWDCHANGE = SYSDATE
 where OPXXX.OPRID = 'USERNAME'

Along with changing the password you also need to set the last password change date, LASTPSWDCHANGE equal to the current date. Otherwise you could change your password, but have it expire on you immediately.

It also makes sense to reset the account locked flag, ACCTLOCK, in case the account has been locked.

Last User Exit To Brooklyn

T&L rules can include PeopleCode steps by making them User Exit rules. To create a user exit rule, do the following.

1. Open the TL_TA_RULES application engine and create a new section for your rule. For this section you must check the Access Pulblic check box.
2. Add your rule steps to this section.
3. Navigate to Home > Setup HRMS > Product Realted > Time and Labor > Validation Criteria > AE Section Definition and define your newly created section. The program name is TL_TA_RULES and theprocess type should be Rule (User Exit).
4. Navigate to Home > Setup HRMS >System Administration > Utilities > Build Time And Labor Rules > Rules and create a rule definition, checking the User Exit box and entering the name of the AE section you created earlier.
5. Add the new rule to any desired rule programs.
6. In the DMS script you use to migrate your rule you will need to include the table PS_TL_AE_SECTION which contains user exit section details. The migration project will need to contain your AE Section and any associated steps.