Coded Languages

Anatomy Of A Query

2011-03-30T01:58:00.000-07:00

The following tables are used to construct a Query in PeopleSoft.

PSQRYACCLSTAET Query Access List State Record
PSQRYACCLSTRECS Query Access Record List
PSQRYBIND Query Prompt
PSQRYBINDLANG Query Prompt Alternate Lang.
PSQRYCRITERIA Query Criteria
PSQRYDEFN Query Definition
PSQRYDEFNLANG Query Definition Alt. Language
PSQRYDEL Query Definition
PSQRYEXECLOG Query RunTime Log
PSQRYEXPR Query Expression
PSQRYFAVORITES Query Manager Favorites Table
PSQRYFIELD Query Field
PSQRYFIELDLANG Query Field Alternate Language
PSQRYFLAGS Query Global Flags Table
PSQRYRECORD Query Record
PSQRYSELECT Query Select
PSQRYSTATS Query RunTime Statistics

Finding a component in the Portal

2010-09-07T04:40:00.000-07:00

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

2010-07-19T03:52:00.000-07:00

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.

2010-06-26T18:40:00.001-07:00

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

2010-06-09T01:29:00.000-07:00

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

2010-06-07T23:29:00.000-07:00


select TL_RULE_ID
 from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'

Getting Details Of A Table's Columns

2010-05-05T04:06:00.000-07:00

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

2010-04-13T07:33:00.000-07:00

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

2010-01-26T07:27:00.000-08:00

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

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.
Add your rule steps to this section.
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).
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.
Add the new rule to any desired rule programs.
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.

PeopleSoft Hints

2009-10-05T07:15:00.000-07:00

The PeopleSoft password hints table, PSUSERATTR, is not encrypted, so avoid using any hint that you use elsewhere, such as What is your Mother's Maiden name?

If you are forced to use a standard question give a bogus answer not one which will compromise your personal security e.g. What is your favourite Sport? response WIBBLE.

Getting Oracle Table Information

2009-08-23T23:55:00.000-07:00

The following SQL gets useful information about tables in an Oracle Database, such as the tablespace used.

select TABLE_NAME,
OWNER,
INITIAL_EXTENT,
PCT_USED,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
TABLESPACE_NAME
from dba_tables
where TABLE_NAME in ( 'PS_TY_TL_RAP_H_HST',
'PS_TY_TL_RAP_T_HST',
'PS_TY_TL_RPT_T_HST',
'PS_TY_TL_PAY_T_HST',
'PS_TY_SCH_AD_D_HST',
'PS_TY_SCH_MN_S_HST',
'PS_TY_TL_TR_CO_HST' )

Getting the Mass Time Run Control Parameters

2009-05-26T07:25:00.000-07:00

Getting the mass time run-control parameters is a bit tricky as it has no run-control table as such. Rather there are a number of base tables that are populated by the run page and used to run the process. The following SQL allows you to examine these tables to allow you to extrapolate what was entered.

Get the process instances of the Mass time process.

To get at the data you need you require the process instance numbers of the ST_LIB process. This allows you to lookup the other details you need.


-- -------------------------------------
-- Get process instances for ST Lib job.
-- -------------------------------------
select PRCS.PRCSINSTANCE,
       PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended,
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as DURSEC
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
where PRCS.RUNSTATUS    = XLAT.FIELDVALUE
  and PRCS.PRCSNAME     = 'TL_ST_LIB'
  and XLAT.FIELDNAME    = 'RUNSTATUS'
  and XLAT.EFFDT        = (select max(XLAT1.EFFDT)
                             from XLATTABLE_VW XLAT1
                            where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                              and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                              and XLAT1.EFFDT <= SYSDATE)
  and XLAT.EFF_STATUS = 'A'
  and PRCS.BEGINDTTM  between to_date('2009-05-19-01.00.00','YYYY-MM-DD-HH24.MI.SS')
                          and to_date('2009-05-19-23.59.00','YYYY-MM-DD-HH24.MI.SS')
  and PRCS.ENDDTTM is not null
  and (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) > 500
-- order by PRCS.BEGINDTTM Desc
order by DURSEC Desc

Select Time Reporters

Next use your newly acquired process instance number to work out what was entered on the Select Time Reporters page.


-- -----------------------------------------------------------------------------
-- 1. Select Time Reporters Page
-- -----------------------------------------------------------------------------
-- Note that it is not possible to determine the parameters entered on this page
-- as  the  only  thing  stored  in  PeopleSoft is the employee IDs selected who
-- matched the criteria.
--
-- To  allow an educated guess to be made as to what they were the following SQL
-- dumps  the  mebers  prompt  field  details  out, so if for example you have a 
-- number of members returned who have the same group, then it stands a good
-- chance that this group was entered.
-- -----------------------------------------------------------------------------
-- Get Time Reporter(s) who were selected.
-- ------------------------------------------
select GDT.GROUP_ID,
       EDT.EMPLID,
       upper(NAM.LAST_NAME) as LAST_NAME,
       NAM.FIRST_NAME,
       JOB.BUSINESS_UNIT,
       JOB.JOBCODE,
       JOB.DEPTID,
       JOB.TY_LOCAL_ID
  from PS_TL_WRKGRP_TBL WRK,
       PS_TL_EMPL_DATA  EDT,
       PS_TL_MASS_TR    MAS,
       PS_JOB           JOB,
       PS_TL_GROUP_DTL  GDT,
       PS_NAMES         NAM
 where WRK.EFFDT            = ( select max(WRK1.EFFDT)
                                  from PS_TL_WRKGRP_TBL WRK1
                                 where WRK.WORKGROUP = WRK1.WORKGROUP
                                   and WRK1.EFFDT   <= SYSDATE )
   and WRK.WORKGROUP        = EDT.WORKGROUP
   and EDT.EFFDT            = ( select max(EDT1.EFFDT)
                                  from PS_TL_EMPL_DATA EDT1
                                 where EDT.EMPLID   = EDT1.EMPLID
                                   and EDT.EMPL_RCD = EDT1.EMPL_RCD
                                   and EDT1.EFFDT  <= SYSDATE )
  and EDT.TIME_RPTG_STATUS   = 'A'
  and EDT.EMPLID             = MAS.EMPLID
  and EDT.EMPL_RCD           = MAS.EMPL_RCD
  and MAS.ST_INSTANCE        = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                   from PSPRCSRQST PRCS
                                  where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
  and EDT.EMPLID             = JOB.EMPLID
  and EDT.EMPL_RCD           = JOB.EMPL_RCD
  and JOB.EFFDT              = (select max(JOB1.EFFDT)
                                  from PS_JOB JOB1
                                 where JOB.EMPLID   = JOB1.EMPLID
                                   and JOB.EMPL_RCD = JOB1.EMPL_RCD
                                   and JOB1.EFFDT  <= SYSDATE)
  and JOB.EFFSEQ             = (select max(JOB2.EFFSEQ)
                                  from PS_JOB JOB2
                                 where JOB.EMPLID   = JOB2.EMPLID
                                   and JOB.EMPL_RCD = JOB2.EMPL_RCD
                                   and JOB.EFFDT = JOB2.EFFDT)
  and JOB.EMPLID             = GDT.EMPLID
  and JOB.EMPL_RCD           = GDT.EMPL_RCD
  and GDT.GROUP_ID not like '%ALL'
  and GDT.GROUP_ID not like '%TST'
  and GDT.GROUP_ID not like 'MFAL%'
  and GDT.GROUP_ID not like 'MFTA%'
  and GDT.GROUP_ID       <> 'MFSEE'
  and GDT.EMPLID             = NAM.EMPLID
  and NAM.NAME_TYPE          = 'PRI'
  and NAM.EFFDT              = ( select max(NAM1.EFFDT)
                                   from PS_NAMES NAM1
                                  where NAM.EMPLID    = NAM1.EMPLID
                                    and NAM.NAME_TYPE = NAM1.NAME_TYPE
                                    and NAM.EFFDT    <= SYSDATE )
order by GDT.GROUP_ID,
         JOB.DEPTID,
         EDT.EMPLID

Select Time Reporting Method

Now get the dates entered and how the time was reported.


-- -----------------------------------------------------------------------------
-- 2. Select Time Reporting Method Page
-- -----------------------------------------------------------------------------
-- Get  the  dates  entered, how time was reported and whether Offdays should be
-- included in the schedule.
--
-- If just one date was selected then the End Date will be blank and start date
-- will  correspond to 'Date:'. If not then start date will be the 'From:' date
-- entered and end date will be the 'To:'.
-- ----------------------------------------------------------------------------
select SESS.START_DATE,
       SESS.END_DATE,
       XLAT.XLATLONGNAME as HOW_TO_REPORT_TIME,
       SESS.APPLY_OFFDAY_IND
  from PS_TL_MASS_SESSION SESS,
       XLATTABLE_VW       XLAT
 where SESS.ST_INSTANCE      = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                   from PSPRCSRQST PRCS
                                  where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
   and SESS.MASS_USER_ACTION = XLAT.FIELDVALUE
   and XLAT.FIELDNAME        = 'MASS_USER_ACTION'
   and XLAT.EFFDT            = (select max(XLAT1.EFFDT)
                                  from XLATTABLE_VW XLAT1
                                  where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                                    and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                                    and XLAT1.EFFDT <= SYSDATE)

And finally get the TRC details and Use Profile entered.


-- -----------------------------------------------------------------------------
-- 3. Report Time Page
-- -----------------------------------------------------------------------------

-- -----------------
-- 3.1 Get Taskgroup
-- -----------------
select MASS.TASKGROUP
  from PS_TL_MASS_SESSION MASS
where MASS.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
                             from PSPRCSRQST PRCS
                            where PRCS.PRCSINSTANCE = :PRCSINSTANCE )

-- --------------------------------------------------------------
-- 3.2 Get Time Reporting Code details entered and 'Use Profile'.
-- --------------------------------------------------------------
select MT.TRC,
       MT.TL_QUANTITY,
       MT.COUNTRY,
       MT.CURRENCY_CD,
       MT.STATE,
       MT.LOCALITY,
       MT.COMP_RATECD,
       MT.BILLABLE_IND,
       MT.OVERRIDE_RATE,
       XLAT.XLATLONGNAME as PROFILE
  from PS_TL_MASS_TRANS  MT,
       XLATTABLE_VW      XLAT
 where MT.ST_INSTANCE       = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                  from PSPRCSRQST PRCS
                                 where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
   and MT.USE_PROFILE_IND   = XLAT.FIELDVALUE
   and XLAT.FIELDNAME       = 'USE_PROFILE_IND'
   and XLAT.EFFDT           = (select max(XLAT1.EFFDT)
                                 from XLATTABLE_VW XLAT1
                                where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                                  and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                                  and XLAT1.EFFDT <= SYSDATE)

Now if only PeopleSoft had just used a good old fashioned run control table for this process...

Listing All PeopleSoft System tables

2009-05-25T03:27:00.000-07:00

All PeopleSoft system tables can be dumped with: -


select RECNAME,
       RECDESCR
  from PSRECDEFN
 where RECNAME like 'PS%'
   and RECDESCR <> ' '

Dumping an Application Engine's Temp Tables List

2009-05-25T03:19:00.000-07:00

Recently I had an issue to look at that required me to list the temporary tables used by the TL Admin application engine. To do this I used the following SQL: -


select *
  from PSAEAPPLTEMPTBL
where AE_APPLID = 'TL_TIMEADMIN'

Blankety blank, blankety blank...

2009-03-02T07:28:00.000-08:00

To make a cell blank when zero in Excel you could do so via conditional formatting, setting the font colour to be the same as the background colour when it equals zero.

This technique is a bit limited however, and hits problems if you need to use other conditional formatting or when the background colour varies from cell to cell.

Another more flexible method is to use the following formatting string for your cell.
Just go to Format > Cells > Custom and plonk it in to the Type box.

0;-0;;@

The above format doesn't show a thousands delimiter. To get that in use: -


#,##0;-0;;@

Why Microsoft made this expression so bloody complex I'll never know. But hey, Microsoft are Microsoft, they'd make the complete works of Shakespeare out of a crisis.

Have a play with varients of this for other effects.

Enjoy!

Peering into the depths of the PeopleSoft Process Scheduler Queue

2009-02-06T01:14:00.000-08:00

On occaision you need to be able to see what's been running in the PeopleSoft Batch queue at a given moment in time. At such times the following SQL comes in quite handy.


-- --------------------------
-- Dump Process Monitor Table
-- --------------------------
select PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as Dur_Sec
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
 where PRCS.BEGINDTTM   > to_date('2009-03-02-01.00.00','YYYY-MM-DD-HH24.MI.SS')
   and PRCS.RUNSTATUS    = XLAT.FIELDVALUE
   and XLAT.FIELDNAME    = 'RUNSTATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'
 order by PRCS.BEGINDTTM Desc

Be careful when you stare into the void however, the void might stare back at you.

One Step Beyond

2009-01-06T03:40:00.000-08:00

One of Time Admin's subprograms, TL_TA000800, calls TL_TA_RULES to apply rules in the order specified on the rule program for each batch. The sections in TL_TA_RULES correspond to each of the active time and labor rules.

When a rule is built the application engine definition tables underlying TL_TA_RULES are updated to reflect any changes made to the rule.

Unfortunately, for a particular rule, the section name and step of the app engine don't correspond to the rule name and step number of the rule itself. Which can make it a little tricky when debugging an abend. The following SQL solves this problem by giving you the rule ID and step for the app. engine section and step that the abend log will give you.



select *
  from ( select TL_RULE_ID,
                TL_RULE_STEP,
                rownum as AE_STEP,
                SQL_ID,
                DESCR100   
           from PS_TL_RULE_STEPS
          where TL_RULE_ID = (select TL_RULE_ID
                                from PS_TL_RULE_DEFN
                               where AE_SECTION = :AE_SECTION)
       )
 where AE_STEP = :STEP

Fun with Time and Labor Dates

2008-11-14T02:45:00.000-08:00

There's a useful little table in PeopleSoft Time and Labor called PS_TL_DATES_TBL. This holds a variety of date information, such as day number, julian date , day of week and month, that can save a bit of coding to derive these values yourself. The following example uses this table to get the start and end dates of the previous, current and next years, months and weeks.

select DAT.THE_DATE,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as START_OF_PREVIOUS_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as END_OF_PREVIOUS_YEAR,
       TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as START_OF_CURRENT_YEAR,
       TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as END_OF_CURRENT_YEAR,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as START_OF_NEXT_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as END_OF_NEXT_YEAR,
       ADD_MONTHS((TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1),-1)                    as START_OF_PREVIOUS_MONTH,
       DAT.THE_DATE - DAT.DAYOFMONTH                                                as END_OF_PREVIOUS_MONTH,
       TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1                                     as START_OF_CURRENT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),1)                                as END_OF_CURRENT_MONTH,
       ADD_MONTHS(TRUNC((DAT.THE_DATE - DAT.DAYOFMONTH) + 1),1)                     as START_OF_NEXT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),2)                                as END_OF_NEXT_MONTH,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 13
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 8
                         when DAT.DAYOFWEEK = 4 then 9
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 11
                         when DAT.DAYOFWEEK = 7 then 12
                       end                                                          as START_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 1
                         when DAT.DAYOFWEEK = 3 then 2
                         when DAT.DAYOFWEEK = 4 then 3
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 5
                         when DAT.DAYOFWEEK = 7 then 6
                       end                                                          as END_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 6
                         when DAT.DAYOFWEEK = 2 then 0
                         when DAT.DAYOFWEEK = 3 then 1
                         when DAT.DAYOFWEEK = 4 then 2
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 4
                         when DAT.DAYOFWEEK = 7 then 5
                       end                                                          as START_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 0
                         when DAT.DAYOFWEEK = 2 then 6
                         when DAT.DAYOFWEEK = 3 then 5
                         when DAT.DAYOFWEEK = 4 then 4
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 2
                         when DAT.DAYOFWEEK = 7 then 1
                       end                                                          as END_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 1
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 6
                         when DAT.DAYOFWEEK = 4 then 5
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 3
                         when DAT.DAYOFWEEK = 7 then 2
                       end                                                          as START_OF_NEXT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 13
                         when DAT.DAYOFWEEK = 3 then 12
                         when DAT.DAYOFWEEK = 4 then 11
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 9
                         when DAT.DAYOFWEEK = 7 then 8
                       end                                                          as END_OF_NEXT_WEEK
  from PS_TL_DATES_TBL DAT
 where DAT.THE_DATE = TRUNC(SYSDATE)
 order by DAT.THE_DATE desc

What day is it son?

If you need to know what day of the week it is you could get the day number then go on the principle that 1 is a sunday, 2 is a Monday etc, to work it out. The problem with this however, is that this number can vary depending on how the Oracle environment is configured, which makes this unreliable. A better way of finding out the day is to use the 'DAY' parameter of TO_CHAR. This returns the day as MONDAY, TUESDAY etc. The only thing to watch here is that the returned value has trailing spaces, so if you're comparing it then remember to trim it first. For example: -

select DAT.THE_DATE, to_char( DAT.THE_DATE, 'DAY' )
  from PS_TL_DATES_TBL DAT
where trim(to_char( DAT.THE_DATE, 'DAY' )) in ('SATURDAY','SUNDAY')
  and DAT.THE_DATE between '01-DEC-2011' and '31-DEC-2011'
order by DAT.THE_DATE Asc

Enjoy.

Finding a table name

2008-09-22T07:24:00.001-07:00

select *
from dba_tables
where table_name like '%<table_name>';

Creating a Database Link

2008-09-22T06:59:00.000-07:00

I always forget the syntax for this one...


CREATE DATABASE LINK 
CONNECT TO 
IDENTIFIED BY 
USING '';

For example: -


CREATE DATABASE LINK HR89TST
CONNECT TO FBLOGGS
IDENTIFIED BY FR3DD13
USING 'HR89TST';

Note that if you have special characters in your password, you should enclose it in double quotes or you may get an error message.

To list existing database links, use: -

select * from dba_db_links;
or

Select * from ALL_DB_LINKS;

To drop a database link use: -

DROP DATABASE LINK linkname;

Pump Up The Volume

2008-08-05T14:31:00.000-07:00

Recently I had a problem with the sound level on my EEE PC, which for no apparent reason went rather quiet. A trawl through the forumsfound the solution. What you need to do to remedy this grievous state of affairs is to take the following steps.

Enter <ctrl><alt>T to open a command console window.

Type alsamixer to bring up the sound card control panel.

Use the up arrow to increase the PCM level, which is the first bar displayed.

Celebrate by jacking in some power speakers and playing some Motorhead really loud.

An alternative solution would be to boost your hearing with the aid of some bionics, though you'll need to find Rudy Wells first. We have the technology.....

How to Install Wordpress and learn to stop Worrying and Love the Blog

2008-03-21T10:26:00.000-07:00

There are two ways to setup a blog. The first is the easiest and involves registering with an on-line blogging service such as Blogger, selecting a template, configuring your account then off you go. The second route is to install a blogging application on a server, configure it, customise it then your up and running. The first method is the quickest and can have you blogging within minutes. The second is a tad more time consuming but gives you far greater flexibility in terms of what you can do.

Wordpress
Having already setup 4 blogs using the first method, I thought it was high time I got down to some real hardcore blogging and setup my own hosted blog. After a few nights of on-line research and numerous coffees, I concluded that probably the best tool for the job was Wordpress. This uses a MySQL database to hold your blogs content then a combination of PHP, XHTML and CSS style sheets to present it.

Hosting
First off you need to have a place for Wordplace to hang its hat. The server that it's hosted on needs to have MySQL and PHP installed on it. The Wordpress site lists a number of recommended Hosts. Again after more surf and coffee sessions I plumped for AN Hosting as my host of choice. A good price, well recommended and bags of features, AN were the host with the most. I also toyed with installing a WAMP server and trying things out locally, but that's a different story. AN also offer automatic Wordpress installation, but for the sake of this post I'll describe how to install it old school.

Installation
Creating the Wordpress Database
Through your hosts control panel create a new database to hold your Wordpress tables. Give it a suitable name such as wordpress or an unsuitable one such as ziggy. Add any users to this database who you want to give access to (e.g. yourself, yourwife, the cat...).

Download Wordpress
First off, download a zip file of the latest stable Wordpress release from the Wordpress website to your PC. Then un-zip it to a suitable place.

Create the wp-config.php file

If you look in the wordpress folder you will find a file called wp-config-sample.php. Save this as wp-config.php then edit it using a text editor such as notepad or the developers PFE.

Find the line that says define('DB_NAME', 'wordpress') and replace wordpress with the name of the MySQL database you created earlier. On the next two lines enter the name of a user who you gave admin rights to your database and their password.

e.g.

define('DB_USER', 'dbrock'); // Your MySQL username define('DB_PASSWORD', 'hawkwind'); // ...and password

On the line that says: -

define('DB_HOST', 'localhost'); // 99% chance you won't need to change this value

I also had to replace local host with remotemysqlhost, as instructed on the AN hosting control panel. I guess I must be the 1%.

One neat trick you can do is to have several blogs setup in the same database, if you want to do this drop down to the line that says:-

$table_prefix = 'wp_'; // Only numbers, letters, and underscores please! this is the prefix that will be given to the name of each wordpress table that is created. For more than one table use different prefixes. For example you could change it to: -

$table_prefix = 'wp_mh_'; // Only numbers, letters, and underscores please!
for your Motorhead blog.

Copy Wordpress Files to Your Server

Now you've created a configuration file, it's time to copy your files up to the server. To do this use an FTP application such as FileZilla or CuteFTP. I personally use WS-FTP, which isn't free but is reliable.

Use your FTP software to copy the extracted Worpress directory to a directory under the directory that holds your web documents. I set one up in this called \blogs\ into which I'll copy any future wordpress directories.
Run install.php

Through a web browser, such as firefox, run the worpress install.php file. To do this enter the URL of your websites root directory followed by the path to this file, which is found in the wp-admin folder.

e.g.

http://www.kungfool.co.uk/blogs/wordpress/wp-admin/install.php You should now be prompted for the name of your blog, which you can always change later, and your e-mail address. Enter both and click on the Install Wordpress link.

In the blink of an eye you'll get the message: -

WordPress has been installed. Were you expecting more steps? Sorry to disappoint.

And given a user ID and password to administer your blog with. Make a note of them for future reference and hit Log In baby.

Congratulations, you've installed Wordpress, woo hoo, go away and have a nice cup of tea and a biscuit to celebrate why don't you.

To view your fledgling blog enter your root URL followed by the wordpress directory: -

e.g.
http://www.kungfool.co.uk/blogs/wordpress
To bring up the admin login page run the following script from your browser: -
http://www.kungfool.com/blogs/wordpress/wp-login.php

substituting your root web URL and Wordpress directory as appropriate.

By the way you can visit the blog I created in this exercise at

http://www.seishan.com/blogs/test25/

I'll be building on it over the next couple of weeks, so expect it to morph as time goes by.

By the way, the 25 in test25 has nothing to do with Hawkwind's - 25 Years On, I'm just checking out Release Candidate 1 of Wordpress 2.5. Wordpress 2.5 isn't on general release yet, but hey I thought I'd give it a whirl.

Enjoy.

Oracle 11g Release on You Tube

2008-01-14T03:59:00.001-08:00

Blogged with Flock

Analyze This

2007-11-21T04:29:00.000-08:00

In the good old days you analyzed a table using a statement along the lines of: -

ANALYZE TABLE tablename COMPUTE STATISTICS;

With the advent of Oracle 8i the DBMS_STATS utility was introduced to gather object statistics. This is Oracles preffered method of gathering the information.

Using it the equivalent of the above analyze statement would be: -

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE);

This should be executed after the table you need to analyze has been populated.

To cut back on the time it takes to execute, you can limit the number of rows it uses in its stats gathering by adding estimate_percent => nn (where nn is a number between 1 and 100) this will only use the given percentage of the total rows in the table when performing its anaysis.

e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE, estimate_percent => 20);

Will only use 20% of the tables rows. The higher the percentage, the better the analysis, but the longer it will take. When used Oracle randomly selects the rows it uses from the table to make them more representative.

N.B. This statement doesn't work from Oracle Developer (I've not tried it from Toad). As such execute it from SQL Plus instead.

Listing a tables Indexes

2007-11-19T06:41:00.000-08:00

select index_name
from dba_indexes
where table_name=<table_name>

Useful SQL Plus Settings

2007-11-14T09:30:00.000-08:00

On occaisions when you're working at an Oracle site with no access to sensible SQL development tools, such as Toad or Oracle Developer (available as a free download from the Oracle website, but no good if you can't download or install it), you are forced to use that most heinous of applications SQL Plus. To make life a bit more palatable, try using the following settings to brighten up its drab and dreary demeanor.

/* SQL Plus parameters required for each session */
select name from sys.v_$database;
alter session set current_schema=SYSADM;
set timing on;
set sqlprompt dbasename> ;
set linesize 150;
set null empty;
set pagesize 500;
set pause on;
set space 1;
set time on;
set trim on;
set wrap off;
set sqlnumber on;
set describe line on;

Enjoy !!!!

Specifying the report directory in PeopleSoft

2007-09-25T04:29:00.000-07:00

To change the directory that Process Monitor tries to open report output in PeopleSoft (logs, trace files etc.) navigate to the Report Node definition page: -

Home > PeopleTools > Process Scheduler > Report Nodes

From here you can specify the URL, URI Port and URI Resource used to open report output in PeopleSoft.

Changing the PS, Application Server Process Scheduler Password

2007-09-21T03:13:00.000-07:00

With a vanilla PeopleSoft installation, one of the first things you should do is change the password for the adminstrator account PS from the default. This post details what you need to do to accomplish this and offers a number of hints and tips along the way.

Backup First

Before making these changes in production I would advise taking a full backup of the system first. And making sure that the backup was successful.

Chosing a new PS Password

As this account gives you carte blanche access to the PeopleSoft system, care should be taken when changing it to ensure that the password chosen is as strong as possible. The new password should: -

Use a minimum of 7 characters and a maximum of 8 characters in length.

Use only mixed case alpha and numeric characters.

Avoid common names, words, and anything obvious, such as your Operator ID. It should also be significantly different from your most recent passwords.

For further suggestions regarding password selection please refer to my previous article The Art of Creating a Password.

Changing the Password

To change the password you need to: -

Change the PS Password through the web;

Change the Application Server password using PSADMIN;

Change the Process Scheduler Passwor using PSADMIN.

Changing the PS Password

Log in to PeopleSoft through the web.

Navigate to: -

Home > PeopleTools > Security > User Profiles > User Profiles

And change the password for PS.

Note that in some installations a seperate PSAPPS user account is used by the Application Server and Process Scheduler. To detemine if this is so, have a look at their configuration files psappsrv.cfg and psprcs.cfg respectively. These will contain the ID and password used to connect to PeopleSoft. If PSAPPS is used then you should change it in the same manner as you changed PS.

Change the Application Server Password

There are two ways you can change the App Server password. You could directly edit the configuration file, named above, or use PSADMIN. As direct editing can cause problems if you encrypt it, I would reccoment you use PSADMIN.

Configure the Application Server

First select Application Server from the main menu to got to the Application Server Administration menu.

Then select Administer a domain and pick the domain you want to change.

Now select configure this domain and answer 'y' when it says that 'this option will shutdown the domain do you want to continue' and 'y' when it asks if you want to change any config values.

The first thing it will ask is if you want to change any startup values, answer 'y' to this.

Continue to press enter untill you get to UserPswd then enter you new PS or PSAPPS password as apropriate.

When it asks if you want to encrypt this password answer 'y'. This will mask it in the config file and make it less easy to hack.

Then keep hitting return to take the default values until configuration is complete.

Restart the Application Server

To restart the saerver with the new password, go to the Domain Administration menu and select 'Boot this domain'.

Change the Process Scheduler Password

Configure the Process Scheduler

First select Process Scheduler from the main menu to got to the Process Scheduler Administration menu.

Select 'Configure a Process Scheduler' and choose the database you want to change from the list displayed.

When told 'This option will shutdown the process scheduler. Do you want to continue' answer 'y'.

As with the Application server the Startup section will appear early on. When asked, enter 'y' when asked 'Do you want to change any values'.

When UserPswd appears enter 'y' to change it and enter the new PS or PSAPPS password.

Again I would advise you to encrypt this password, so enter 'y' when asked.

Keep hitting return to take the default values until configuration is complete.

Restart the Process Scheduler

Got to the Process Scheduler Administration screen and select 'Start a Process Scheduler Server'.

Select the apropriate database from the list displayed.

SQL to find Rules and associated Application Engine Sections

2007-08-28T06:11:00.000-07:00

Get the AE Section name from the Rule ID.

select AE_SECTION
from PS_TL_RULE_DEFN
where TL_RULE_ID = 'XXX'

Get the Rule ID from the AE Section name.

select TL_RULE_ID
from PS_TL_RULE_DEFN
where AE_SECTION = 'XXX'

Nationality in PeopleSoft

2007-07-17T03:04:00.000-07:00

I was recently asked how you could store Nationality in PeopleSoft. Some companies use birth country to hold an employees nationality, which may or may not be the same, but PeopleSoft now allows you to hold this information under Citizenship.

Home > Workforce Administration > Personal Information > Citizenship, Identification Data > Citizenship/Passport

Under citizenship you can hold country and details of a persons passport. As an employee can have multiple countries on this page it also caters for the possibility of dual nationality.

Associated Tables: PS_CITIZENSHIP and PS_CITIZEN_PSSPRT

Note that the same tables are also used to hold citizenship details for dependants.

Current XLAT Values for a Field

2007-07-10T01:51:00.001-07:00

The following field selects current active values for a field from the XLAT table.


-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
       XLAT.XLATLONGNAME,
       XLAT.XLATSHORTNAME
  from XLATTABLE_VW XLAT
 where XLAT.FIELDNAME    = 'EMPL_STATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'

Finding The Current Database Name

2007-07-09T10:39:00.001-07:00

Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.

Oracle
In Oracle you can get the current database name by querying the table v_$database

i.e.

select name
 from sys.v_$database

SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.

e.g.

select DB_Name()

Finding a table name in SQL Server

2007-07-09T10:38:00.000-07:00

To find a table name in SQL Server query the table sysobjects.

For example to list all PeopleSoft tables on the system, you could use: -

select *
from sysobjects
where name like 'PS_%'

PeopleSoft Payroll Interface Timeout Issue

2007-07-06T02:57:00.000-07:00

I recently came across a problem where a vanilla payroll interface was failing due to a timeout occurring. The error message given was: -

13884 08.28.58 300.014 ErrorReturn-> -1 - Child program F:\psoft8\HR88\HR8DEV\CBLBINA\PIPINRUN REMOTEA/MICROSFT/HR8DEV/ASTOLDT/////0/people/%PSWD%/9aec1b63-2af2-11dc-86cf-cbed13adba68 (5568) did not complete in allowed time period (300 seconds). Killing process. (2,-1) At PI_INRUN.2EXPORT.GBL.default.1900-01-01.Step01.OnExecute PCPC:263 Statement:1

On investigation I found that the solution was to change two timeout values in two configuration tables.

psappsrv.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

psprcs.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

In both instances I changed the timeout value from 300 to 0 (no limit) before using PSADMIN to stop and restart the Application Server and Process Scheduler. The net result was that the interface ran to a successful completion when re-run.

Huzzah!!!

PeopleSoft Payroll Interface SQL

2007-07-05T04:46:00.000-07:00

/****************************************/
/*                                      */
/* Dump the Payroll Interface Run Table */
/*                                      */
/****************************************/
select *
from PS_PI_RUN_TBL
order by PI_RUN_NUM desc

/*************************/
/*                       */
/* Dump PI Partic table. */
/*                       */
/*************************/
select *
from PS_PI_PARTIC

/*************************************/
/*                                   */
/* Dump PI Partic table, latest run. */
/*                                   */
/*************************************/
select PIP.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC  PIP
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIP.PI_RUN_NUM

/*******************************/
/*                             */
/* Dump PI PARTIC detail table */
/*                             */
/*******************************/
select *
from PS_PI_PARTIC_EXPT

/********************************************/
/*                                          */
/* Dump PI Partic detail table, latest run. */
/*                                          */
/********************************************/
select PIE.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC_EXPT PIE
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIE.PI_RUN_NUM

/***************************************************/
/*                                                 */
/* Dump Pay Calendars associated with a particular */
/* Payroll Interface run.                          */
/*                                                 */
/***************************************************/
select RUN.PI_RUN_NUM,
     CAL.*
from PS_PI_RUN_TBL RUN,
     PS_PAY_CALENDAR CAL
where RUN.PI_RUN_NUM = 999
 and RUN.RUN_ID     = CAL.RUN_ID

/******************************/
/*                            */
/* Dump the PI Empl ID Table. */
/*                            */
/******************************/
select *
from PS_PI_EMPLID_TBL

/********************************************/
/*                                          */
/* List fields in a Payroll Interface File. */
/*                                          */
/********************************************/
select DEF.PI_FIELD_SEQ,
     DEF.PI_FIELD_ID,
     FLD.DESCR,
     FLD.PI_FIELD_LEN,
     FLD.PS_FIELD1_NM,
     DEF.PI_KEY_FIELD,
     DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
     PS_PI_FIELD_TBL  FLD
where DEF.PI_FILE_ID = 'XXX'
 and DEF.EFFDT        = (select max(DEF1.EFFDT)
                           from PS_PI_DEFN_FIELD DEF1
                          where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                            and DEF1.EFFDT    <= SYSDATE)    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID    and FLD.EFFDT        = (select max(FLD1.EFFDT)                              from PS_PI_FIELD_TBL FLD1                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID                               and FLD1.EFFDT      <= SYSDATE)

/***************************************************/ 
/*                                                 */
/* List interface files that contain a given Field */
/*                                                 */
/***************************************************/
select DEF.PI_FILE_ID,
        DEF.PI_FIELD_SEQ,
        DEF.PI_FIELD_ID,
        FLD.DESCR,
        FLD.PI_FIELD_LEN,
        FLD.PS_FIELD1_NM, 
        DEF.PI_KEY_FIELD,
        DEF.PI_GROUP_ID
   from PS_PI_DEFN_FIELD DEF,
        PS_PI_FIELD_TBL  FLD
  where DEF.PI_SYSTEM_ID = 'CHR'
    and DEF.PI_FIELD_ID IN ('E001')
    and DEF.EFFDT        = (select max(DEF1.EFFDT)
                              from PS_PI_DEFN_FIELD DEF1
                             where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                               and DEF1.EFFDT    <= SYSDATE)
    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID
    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID
    and FLD.EFFDT        = (select max(FLD1.EFFDT)
                              from PS_PI_FIELD_TBL FLD1
                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID
                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID
                               and FLD1.EFFDT      <= SYSDATE)

Pizza Pie

2007-07-04T09:56:00.001-07:00

After completing my two posts on password security (The Art of Creating a Password and the imaginatively titled The Art of Creating a Password Part 2), I was inspired to go through my own passwords and make them as strong as possible. Eventually I arrived at the one I use to login to the Dominos Pizza website for my occaisional fix of Pepperoni and Green Peppers, Mushrooms, Olives, Chives. Logging in to my account I attempted to change my existing password, only to be presented with an error message along the lines of you couldn't change your details once an order had been started. E-mailing Dominos technical support I was later told that the only way to change it was to get them to delete my account so that I could create it from scratch with a new password.

My suspicion is that Dominos not only sponsor the Simpsons, they also hired Homer to design their website.

Doh!!!

The Art of Creating a Password Part 2

2007-06-29T10:20:00.000-07:00

In my last post (cue trumpet solo) I described some techniques you can use to come up with a strong password. A strong password, as opposed to its weedy cousin the weak password who gets sand in his face when kicked to the ground, is very difficult for a hacker to crack and so ideal to protect your valuables on-line.

Unfortunately your man of steel password could be as safe as a woman of kleenex if your PC becomes infected by a keylogger. Not someone who cuts down trees and skips and jumps, a key logger is a piece of software that sits on your PC and transmits every keystroke you enter back to the criminal who wrote it. So as you smuggly enter your 15 character super password, with more special characters than an episode of Heroes, a vilain with an even smugger smile on their face could be receiving it on the other side of the world.

First and foremost, you should protect yourself from spyware and malware in general to the best of your ability (see my post Tinker, Taylor, Soldier, Spyware for details of how to do so). However, even with the best protection in the world, there is still the chance that something slips under your radar. So what do you do?

The solution to this problem is to use a password manager. A password manager can sidestep keylogging by storing all your passwords in an encrypted file on your PC then entering them via a virtual keyboard. As the keyboard is never used the key logger wouldn't get a sniff of the details you enter. Hurrah !!!

Reccomendation

A few years ago I spent a significant amount of time burning the midnight oil and researching password managers on-line, reading reviews, downloading and using trial versions and ending up with rings round my eyes so I looked like a panda. Finally I settled on a product from Siber Systems called Roboform.

Features

The features that sold me on Roboform initially and still have me using the product today are:

Security - Roboform stores your login details in an encrypted file using 256 bit encryption using AES, Blowfish, RC6, 3-DES or 1-DES algorithms. The Pentagon may be able to crack it, but your average hacker probably wont.

Single Click Web Page Entry - Roboform has the ability to login to a website with a single click, and to poplulate personal information fields, such as address and phone numbers, again with a single click. It can also fill in card details, warning you when it does so. Again this information is encrypted on your PC and again it wont fall foul of a keylogger.

Multi-Browser support - A Roboform plugin works with Firefox and IE. For unsupported browsers you can still use it by drag and dropping your information into the relevant fields on the webpage.

Safenotes - Another feature of Roboform is the safenote. A safenote is a free format note that is encrypted using the same algorith as everything else. This means, for example, if there was sensitive information you wished to keep on your PC you could enter it into a safenote to stop it being hacked.

One password to rule them all - Roboform has one master password which it uses as the encryption key for the data you store. As such you only need to remember a single password to access all your others. This password should be strong and I would reccomend you follow the techniques I previously outlined in The Art Of Creating A Password. If you just need to remember one password, make it a good one.

Automatic password generation - One useful utility included in Roboform is the facility to automatically generate a password. This allows you to specify the length of password you want and the type of characters you want to include (alpha, numeric, special). Press generate and a password is randomly generated to your specification. As you can imagine this tool can provide you with some very strong passwords. As you don't need to remember them you can generate the best possible password that meets the constraints of the account you are logging in to, thus giving you the maximum possible password security.

All in all I've found Roboform an excellent tool that definitely makes my A-List. There are other features available which you can check out on their website at http://www.roboform.com/. My advice would be to download it, try it and then buy it.

The Art of Creating a Password

2007-06-28T09:42:00.000-07:00

One area of security that hackers regularly exploit is your password. Most people struggle to remember long and complex passwords and so resort to either using short and simple ones, which are easy to crack, or more complex ones that they end up writing it on a postit note generally found attached to their computer monitor.

The Ten Password Commandments

Don't use repeated characters (e.g. aaaaaaa), sequences (123456 or abcdefg) or adjacent keys on your keyboard (e.g. qwerty) .
Don't use words found in the dictionary
Chose a password that is easy to remember to avoid your having to resort to variations of the post-it trick. Note that if you do resort to writing it down then do so in a secure fashion. Consider splitting it up and storing the fragments in different places. Store the written password using an easy to remember code such as adding an offset to the characters in it or reversing it.
Don't share a password between accounts. If a hacker cracks it for one they can use it for any others you've used it for. You may also inadvertently use it on a bogus website where it will be stored and systematically tried against your legitimate acounts.
Incorporate as many keyboard characters on the keyboard as possible (if possible include upper and lower case letters, numbers and special characters). The fewer you use the longer it needs to be to have the same strength.
Your password should be as long as possible (preferably 14 characters or more long but 8 as a minimum).
It should not the same as your user name, any part of your (or family members) name, birthday or other personal information. Hackers will try variations of these if they can get hold of this information.
Don't store it on the internet or on a network. Don't store it on your PC unless it is encrypted. If a hacker gets at the file containing them then they can use them.
Change your password regularly. The stronger the password you use the less frequently you need to change it.
Don't just use simple letter substitution on a weak password. P@$$W0rd may look strong but password cracking software will often incorporate this kind of letter substitution.

The third and sixth of these criteria would at first glance appear to be mutually exclusive. Most people struggle to remember a 5 or 6 character password, without traversing the heady realms of passwords that contain ten or more.

Johnny Mnemonic

One way around this is to base your password on mnemonic phrase, saying or the lyrics of a song that you remember. Take the first letter of each word in your key phrase, et voilla an instant long password that is easy to remember. Marvelous.

For example take the old Nancy Sinatra song "These Boots are Made for Walking". Taking the first 12 words you get "These boots are made for walking and thats just what they'll do". Taking the first letter of each word gives you the letters "TBAMFWATJWTD" which to all intents and purposes looks pretty random.

Substitute

To widen the range of characters used in your password you can now apply the rule that you use and upper case letter for the first letter of each word on your phrase that is greater than two characters long and lower case for the rest. You now get "TBAMFWATJWTd", not a huge improvement in this example but hey you can always choose a new key phrase. To widen the range of characters even further, consider substituting special characters for certain letters. For example: -

'@' for 'a'
'8' instead of 'B'
left bracket '(' instead of 'C'
'!' for an 'i' (invert ! and you get i)
zero for 'O'
'$' instead of 'S'
'+' for 't'.

Your password would now be "+8AMFWA+JW+d".

The Specials

Finally add one or more additional special characters to the mix at predetermined points in your password. For example you could always enter "~" as the third character of your password and "#" your last.. As long as you're consistent you should be able to remember as you key it in. Adding these in would give you "T8A~MFWATJWTd#" which is pretty strong. Just don't go humming the tune to yourself as you're entering it.

Remember that if you don't use a strong password, then one of these days these hackers are gonna walk all over you.

Just What the Daktari Ordered

2007-06-25T08:50:00.000-07:00

Apple has released Safari 3.02 this week. This new version of their beta Windows browser, includes a number of stability and security fixes.

So far I've been quite impressed with Safari. Loading times for web pages are as fast as a Gazelle in flight and its interface as uncluttered as a water hole with a Lion in residence. Watch this space, it'll soon have other Hippo like pieces of browsing bloatware in its sights.

Tinker, Tailor, Soldier, Spyware

2007-06-12T08:05:00.000-07:00

Combating the Insidious Threat to Your Privacy posed by Spyware

What Is Spyware?

Spyware is any software that uses your Internet connection in the background without your knowledge or explicit permission.

It has the ability to: -

Read cookies
Scan files on your hard drive
Monitor your keystrokes
Install other Spyware programs
Alter your default home page
Send information back to its creator

This can result in information theft of sensitive information such as PIN numbers and passwords, reduce the performance of your PC and cause it to become unstable (it is thought that Spyware is the cause of up to 50% of PC crashes). Spyware can also take over your PC using it as a Zombie for such illicit activity as denial of service attacks or propagating spam.

In general Spyware is produced by a group of people for profit whereas viruses were produced by an individual for kudos.

Network performance can also be adversely affected by a Spyware attack. For a business the act of tracking down and removing the offending software will lead to disruption and loss of productivity.

Types of Spyware

Spyware generally fall into one of two categories; Adware, or Malware.

Adware
Adware is generally benign and is usually produced by advertising companies. Adware generally generates annoying on-screen advertisements (normally pop-ups).

The worst forms hijack links on websites and take you to destinations of your choice. The reason for this is that by artificially inflating the amount of traffic to their websites, your friendly neighbourhood hijacker can command higher advertising revenues. This can be worrying if you've got a child who surfs the net as the destinations routed can be anywhere on the web and pornography companies do make use of Adware in this manner.

Adware uses cookies to hold details of your browsing habits which are periodically sent to the marketers.

Although not directly compromising your system in the way that the far more more malign Malware does (see below), such software can cause it to become unstable and have a detrimental effect on its performance.

Malware
Malware is an abbreviation of malicious software and is often written to harm your system, much in the way that a virus can. Other forms use key logging to send details of your typing to the perpetrators. This has implications of identity theft and other criminal acts being perpetrated on you. A hacker potentially could use these programs to get your credit or bank card details.

A Growing Threat

"Over the past three months, EarthLink and Webroot found more than 29.5 million instances of spyware. This figure equates to an average of nearly 28 spyware items per computer and demonstrates the broad proliferation of spyware." said Cobb. "While most spyware is Adware-related and relatively benign, it's disturbing that over 300,000 of the more serious System Monitors and Trojans were uncovered. This figure represents how real a threat identity theft or system corruption is for users."

Earthlink and Webroot

Spyware has been around for less time than viruses but is rapidly becoming a greater threat.

In a recent survey by Earthlink and Webroot an average of 28 pieces of spyware were found on infected PC's. Most were relatively benign Adware, but 300,000 cases of more serious Trojans and System Monitors were found (from 1,062,756 scans).

It is currently thought that 90% of all computers connected to the internet are infected.

The Legal Position on Spyware

While the legal position regarding viruses is pretty well laid out the legality of Spyware is not.

Direct advertising companies sometimes use the spurious argument that because a user agrees to the terms and conditions of a piece of software they download, which includes mention of dubious activities in the smallest of small print, they consent to having Spyware running on their machine. Furthermore they state that to legislate against these nefarious products would have a dire effect on the economy. I seem to remember similar arguments were used when the slave trade was abolished, which is ironic considering that the zombification of your PC by some Spyware constitutes a form of cyber slavery.

The legal position of Spyware in the United States is also murky, in some states it constitutes a criminal activity, in others it doesn’t.

How You Can Get Infected?

Spyware can be installed by clicking on a weblink, opening an attachment in an email or by the payload of a virus. In fact you only have to visit a website, known as drive-by installation, or view a HTML e-mail message to get infected.

Spyware can also piggyback on the back of a utility you install (such as a P2P file sharing system).

Symptoms of Spyware

As with any disease there a number of tell tale signs that you've probably got it. Some typical spyware symptoms include: -

Your browser being hijacked i.e., it takes you to sites other than the ones you type in

A repeated, or sudden change, of your browsers home page that you didn't make

Your being inundated by a plague of pop-up ads

New toolbars appearing in your browser you don't expect to see

Unexpected new icons appearing in your system tray

Keys that don’t work properly when pressed (e.g. the 'Tab' key doesn't work or is delayed when used on a Web form to move to the next field)

Poor system performance when saving files or opening programs

Random error messages appearing

Steps You Can Take To Avoid Infection

Take Care When Downloading Software
Be careful what software you download and install on your PC. Only download software from reputable sources.

Carefully read the End User License Agreement (EULA) of any software you plan to download. Often buried within the EULA will be a disclaimer stating something along the lines that

"...information about you and your browsing habits will be sent to the company's website."

Spyware mongers take your acceptance of this as giving them Carte Blanche to infect your PC, so be careful.

If the EULA is hard to find, or understand, or contains a clause like the one shown above, then seriously reconsider installing the software.

Be Wary of Gadgets
Use the active protection inherent in Anti-Spyware products to help protect your PC (see Anti-Spyware Products below).

With the advent of Windows Vista the use of Gadgets is becoming more and more prevalent. A gadget is an application that you can embed in a web page that delivers a piece of functionality to the user, such as showing the weather forecast for your area or displaying a digital clock on their webpage.

Quite often gadgets are created by hobbyists and delivered free of charge. Quite often they will also have a hidden payload of Spyware hidden in their code.

You can add gadgets to a website you administer or to a blog you post to. The revamped version of Google's homepage, iGoogle, also allows you to add gadgets.

In Windows Vista you can add gadgets to the windows sidebar. Unlike installing a gadget on a webpage where the Windows & Browser security will stop them accessing your file system, Vista gadgets have full access. That means that they can copy any embedded DLL, file or program to your hard drive. Vista gadgets execute with full system permissions and so can then execute those programs.

To quote the old saying, there's no such thing as a free lunch. In the case of gadgets the cost of your free lunch may end up being more than a Champagne and Caviar feast at the Ritz.

Take Care When Opening Attachments
If you receive an email with an attachment you are not expecting, the safest course of action is not to open it, and just delete it. If you later find that it was legitimate then you can always arrange to have it resent to you.

Ramp Up Your Browser Security
Ensure that the level of your browser security setting is sufficient to detect unauthorised downloads. In the case of Internet Explorer this should be set to at least 'Medium'.

This minimises the risk of drive-by downloads getting their teeth into your system.

Turn Off the Preview Pane in your Email Client
As previously mentioned the mere act of viewing HTML can cause Spyware to be installed. If you automatically view your current email in your email client this could potentially result in infection. To stop this from happening, turn off the preview pane. In MS Outlook on the View menu clicking Preview Pane toggles whether it is displayed or not.

Use a Browser Other Than Internet Explorer
The main way that Spyware is spread is through your web browser. A lot specifically target vulnerabilities in Internet Explorer, such as ActiveX, so one way of improving your security is to use an alternative web browser, such as Firefox, Opera or Safari. These browsers are not targeted as much as IE. Unfortunately a large number of websites only work using Internet Explorer so you may still need to use it. If this is the case make sure that you upgrade to at least IE7, security in this version was significantly improved.

Add Known Bad sites to IE's Restricted Sites Zone
If you are forced to use Internet Explorer it is worth considering blocking known bad websites. To avoid these you can install IE-SPYAD. This free program adds known bad sites to Internet Explorer's Restricted Sites Zone.

If you do use it then remember to update it on a regular basis to keep your list up to date.

Screen websites via a Browser Add-on

An alternative to blocking known bad sites via IE's restricted zone, which can be technically demanding, is to use a product such as Sitehound to alert you before you enter a known bad site.

Sitehound is a plug-in for Internet Explorer and Firefox. It works by using a list of known bad sites to check an entered URL against before you are directed there. The basic version of the product is free but requires you to manually update the bad site list. The pay for version does so automatically and also includes other features such as giving additional information about a suspect website.

Don't Click any Pop-Up Links
If a pop-up window appears don’t click any links within it. Doing so may cause the installation of Spyware on your computer. When a pop-up appears, close it by clicking the 'X' icon in its title bar.

Don't click on links in Spam
If you receive e-mail that claims to offer anti-spyware software, don't click on any of the links in it. Some of the Anti-Spyware products offered in spam actually install the spyware they claim to protect you from! If you want to install any Anti-Spyware products then a good place to start is with those listed later in this blog

Use Anti-Spyware Products
Use the active protection inherent in Anti-Spyware products to help protect your PC (see Anti-Spyware Products below).

Update Your System Regularly
Use Windows Update regularly to ensure that your operating system and web browser always has the latest patch or security update applied. Configure Windows Update to update automatically.

Use a Hosts File
A Hosts file allows you to specify an IP address that will be accessed when you enter a specific IP address in your web browser. By creating a file where the IP address for known malicious sites point back at your own computer, you can effectively make it impossible to visit them in the first place thus removing the chance of infection. Host files of this nature can be found on-line.

It is debatable however how effective this strategy is as Spyware can connect directly to the IP address, and thus circumvent this form of protection.

As an aside some Spyware modifies the host file as a means of redirecting you to sites of the authors choosing.

Consider Using a Program to Check Your E-Mail
As e-mail is one method that Spyware and viruses can infect your computer you could consider using a product to scan your e-mail for potential threats. One such product, Mail Washer Pro, is ostensibly a Spam removal tool but as the emails it screens for can also contain a virus or Spyware, it also provides another line of defence against infection. The preview pane it uses just shows the message in plain text so allows you to safely check your mail before you download it.

Detecting Spyware

Network and systems administrators can detect Spyware by: -

Capturing and studying network transmissions for suspicious packets using a network analyser.

Using the Netstat utility to monitor all ports. This is a TCP/IP application that reads network data structures. It can be used to find any suspicious ports open on your PC; they can then conduct a Web-based search on any suspect ports which may reveal the existence of Spyware.

There are also a number of third party products that can be used to scan your ports and provide a graphical interface (e.g. X-NetStat).

A more effective means of detecting Spyware is to use a dedicated Anti-Spyware application.

Anti-Spyware Applications
Some anti-virus products profess to also protect your computer against Spyware but the truth of the matter is that none of them do a very good job of it. To protect yourself properly from Spyware you should run a dedicated anti-Spyware product.

The pick of the crop of products at time of going to press are: -

Freeware

Commercial Products

Spyware Doctor - Spyware Doctor has consistently topped the ratings in reviews of anti-Spyware products and has a detection rate of around 97%. Another advantage of Spyware Doctor is that updates for new threats are made available for download within days (or in some cases hours) of the threat being identified. In the case of some well known products, it can be a week or more before countermeasures are made available.

As well as scanning for Spyware some products, generally the ones you have to pay for, also provide active defence to stop your PC getting infected in the first place. These active defences, although not 100% reliable, do provide some protection and are therefore recommended. The only downside to running active defence is you may find some degradation in performance.

One thing I would recommend is periodically scanning with more than one anti-Spyware tool. Even the best software won’t detect all current Spyware, so by scanning with more than one product you increase the chances of finding it. A combination of the current best commercial and the best of the free ones run on a regular basis will give you the best protection.

Keep Your Spyware Product Up-to-date
Update your Spyware signatures on a daily basis.

Scan Your PC for Spyware Often
You should perform a full Spyware Scan at least once or twice a week. In addition to this, if you have the option, configure your product to scan key areas on a daily basis, if possible on start-up.

Sometimes Spyware can mask itself during start-up making it difficult to detect and remove. To counter this, you should periodically run a full scan in Safe Mode. Safe Mode can be accessed by repeatedly pressing the F5 key (or on some systems F8) during the boot sequence.

False Alarms
Anti-Spyware programs use the following methods for detecting Spyware: -

They contain a list of known Spyware which the use to compare against;

They detect suspicious activity including Windows registry entries that are out of place, suspicious network connections and programs that exhibit suspicious behavior.

Sometimes they can falsely report a valid application as being Spyware. Always check any reports produced and make sure you don’t accidentally remove legitimate software.

Blocking Spyware Transmissions Using a Firewall
As previously mentioned, Spyware sends information back to its creator. Running a firewall, such as Zone Alarm, not only stops unwanted intrusions getting in, but can also stop unsolicited transmissions getting out.

By configuring your firewall to only give internet access to legitimate applications you run, you can deny any Spyware that has managed to evade your defences the ability to accomplish its objective.

Some Useful Web Sites

For further reading on the subject, check out the following websites.

Spyware Warrior - Spyware Warrior lists free and pay for software. It also lists bogus spyware that, when installed, spy on you.

Spywareinfo - this site has a number of spyware forums where experts in the area participate. As such it provides a good port of call should you have any questions.

Malware Removal - a site with a number of Malware related forums. It also has some useful downloads that help you remove some specific Malware infections.

Conclusion

Today most individuals and organizations measures in place to deal with the threat posed by viruses; they should also invest in separate countermeasures to combat the rise of Spyware.

Failure to do some can harm your efficiency, reputation, productivity and ultimately, your financial wellbeing.

Roles, Permission Lists & Components

2007-05-24T03:26:00.000-07:00

The following SQL identifies which roles/permission lists a user has to access a given component.


select RUSR.OPRID,
       RUSR.ROLENAME,
       RCL.CLASSID,
       MNU.MENUNAME,
       CMP.PNLGRPNAME
  from PSROLEUSER_VW RUSR,
       PSROLECLASS RCL,
       PS_PSACLMENU_VW2 MNU,
       PS_ACLCOMPONENT_V2 CMP
 where RUSR.ROLENAME  = RCL.ROLENAME
   and RUSR.OPRID     = 'oprid'
   and RCL.CLASSID    = MNU.CLASSID
   and MNU.MENUNAME   = CMP.MENUNAME
   and CMP.PNLGRPNAME = 'component'
 order by MNU.CLASSID

SQL to find out which roles have a particular permission list.


select distinct RCL.ROLENAME,
                RCL.CLASSID
           from PSROLEUSER_VW  RUSR,
                PSROLECLASS  RCL
          where RCL.CLASSID = 'TYALLPLS'
       order by RCL.ROLENAME

SQL to list users assigned to a particualr role.


select *
  from PSROLEUSER_VW RUSR
 where RUSR.ROLENAME = 'Rolename'

Current Job Row

2007-05-23T06:03:00.000-07:00

I know I can enter the job effective dating code in my sleep, but the following, with a few amendments as needed, should save some time in the future.

select JOB.EMPLID, JOB.EMPL_RCD, JOB.EFFDT, JOB.EFFSEQ, JOB.ACTION, JOB.ACTION_REASON, JOB.EMPL_STATUS, JOB.BUSINESS_UNIT, JOB.COMPANY, JOB.PAYGROUP from PS_JOB JOB where JOB.EFFDT = (select max(JOB1.EFFDT) from PS_JOB JOB1 where JOB.EMPLID = JOB1.EMPLID and JOB.EMPL_RCD = JOB1.EMPL_RCD and JOB1.EFFDT <= SYSDATE) and JOB.EFFSEQ = (select max(JOB2.EFFSEQ) from PS_JOB JOB2 where JOB.EMPLID = JOB2.EMPLID and JOB.EMPL_RCD = JOB2.EMPL_RCD and JOB.EFFDT = JOB2.EFFDT)

Note: The above example is for Oracle, if using SQL Server change SYSDATE to GetDate().

Query Security Record SQL

2007-05-21T05:33:00.000-07:00

The following SQL identifes which PeopleSoft records use a particular Query Security Record.

select RECNAME
from PSRECDEFN
where QRYSECRECNAME = 'query security record'

SQL to find a Field in PeopleSoft

2007-05-08T04:57:00.000-07:00

This script lists all the tables that contain a given field in PeopleSoft. Just replace fieldname with the name of the field you want to find.

SELECT A.RECNAME,
B.SQLTABLENAME
FROM PSRECFIELD A,
PSRECDEFN B
WHERE A.FIELDNAME = 'fieldname'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND B.RECNAME NOT LIKE 'AUDIT%'
AND B.RECNAME NOT LIKE '%TEO%'
AND B.RECNAME NOT LIKE '%AET'
AND B.RECNAME NOT LIKE '%TAO%'
AND B.RECNAME NOT LIKE '%PERS%'
AND B.RECNAME <> 'PSOPRALIAS'

How to Automatically Select Jobs on the Run Panel in PeopleSoft

2007-05-08T04:39:00.000-07:00

To automatically select a job for running on the run panel in PeopleSoft add some PeopleCode to the RowInit event PRCSRQSTDLG_WRK.SELECT_FLAG to set this field.

e.g.

Evaluate %Component
When "COMPONENT_NAME_1"
SELECT_FLAG = "Y"
When "COMPONENT_NAME_2"
SELECT_FLAG = "N"
End-Evaluate;

Things You Do In Your Lunch Break - How to get the Ratio between two numbers in Excel

2007-05-08T04:06:00.000-07:00

A while ago I was asked how to get the ratio between two numbers in Excel. One lunchbreak and two VBA functions later, this is what I came up with.

I ended up creating two functions. One returns the highest common denominator for two numbers HighDenom and the second, Ratio, returns a string of the ratio between two numbers (e.g. 5:1).

First off you need to add a couple of functions to your spreadsheet...

1. Open the workbook you're working on

2. Go into VBA (Alt+F11)

3. Insert a new module (Insert > Module)

4. Paste the following 2 functions into your module: -

Public Function HighDenom(intNum1 As Integer, intNum2 AsInteger) As Integer

' This procedure returns the Highest common denominator fortwo numbers.

If intNum1 > intNum2 Then
intMax = intNum1
Else intMax = intNum2
End If

For i = intMax To 1 Step -1
If (intNum1 / i = Int(intNum1 / i) And (intNum2 / i =Int(intNum2 / i))) Then
HighDenom = i
Exit Function
End If
Next

End Function

Public Function Ratio(intNum1 As Integer, intNum2 AsInteger) As String

' This procedure returns the Highest common denominator fortwo numbers.

intHD = HighDenom(intNum1, intNum2)
intDiv1 = intNum1 / intHDintDiv2 = intNum2 / intHD
Ratio = intDiv1 & ":" & intDiv2
End Function

5. Get out of VBA (Press Alt+Q)

6. Use the functions (They will appear in the Paste Function dialogbox (Shift+F3) under the "User Defined" category.

How to use %RECNAME_EDIT as a Prompt Table

2007-05-08T03:33:00.000-07:00

Sometimes in PeopleSoft you want to be able to vary the contents of a drop down list. For example you may want to only give the user the choice of selecting departments for a particular division.

You could do this by having a controlling field that is a high level key in the lookup table. PeopleSoft’s use of Set IDs utilises this method to restrict values based on their Set ID.

On occasion though, you may need to be able to dynamically change your values on the fly. For example you may have a radio button on a page that you use to control the values displayed in a drop down list. One way of doing this is to use %RECNAME_EDIT functionality.

In this technique you specify a field as the prompt then set its value in PeopleCode to the name of the table you want to use as your prompt table.

First off set the Prompt Table for the field you want to display values for to %fieldname where fieldname is the name of the field you are going to set in your PeopleCode. For example PeopleSoft often use %RECNAME_EDIT. RECNAME_EDIT is a field in the DERIVED table.

Second, add your edit field to the page or pages that contain your dropdown field making it display only and invisible. For example if you used a prompt of %RECNAME_EDIT you could add RECNAME from the DERIVED record to the page.

Finally add some PeopleCode to set the value of this field to the actual name of the prompt table.

e.g.

If (condition) Then
DERIVED.RECNAME_EDIT = "A_VIEW_VW";
Else
DERIVED.RECNAME_EDIT = "B_VIEW_VW";
End-If;

This can be done in Page PeopleCode, Component PeopleCode or FieldChange PeopleCode on a controlling field.

Thanks to Kevin Gavaghan for giving me a heads up on this technique a while back.

Add to Google

2007-05-04T09:17:00.000-07:00

One neat thing you can do with iGoogle is add a feed to a blog to your page. As a blogger, in order to facilitate this you need to add a button to your blog that the reader can press to add it.

To create this button on your blog: -

Go to the following page http://www.google.co.uk/webmasters/add.html and enter your feed URL(e.g. http://feeds.feedburner.com/TheGeorgeLiteZone?format=xml) in the box.

Press the Create HTML button.

Copy the HTML generated.

In Blogger add a new HTML/Javascript page element, or open an existing one.

Paste in the HTML you copied.

Est voilla, a nifty little Add To Google button will appear as though by magic on your blog. Now anyone visiting it can add your blog to their iGoogle page. Fantastic.

Now to follow my own instructions to add a button to Coded Languages.

Query Tree Security SQL

2007-05-04T05:51:00.000-07:00

Query Trees are assigned to a permission list in PeopleSoft under the Query tab of: -

PeopleTools > Security > Permissions and Roles > Permission Lists

Working out who's got access to what query trees can be a bit of a nightmare manually searching through each permission list in turn. To that end, the following piece of SQL is quite useful in establishing which roles have access to a particular query tree or trees.

select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME in (Query Tree List)

To see which query trees a particular user has access to, use: -

select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where USR.OPRID = 'Operator ID'
and USR.ROLENAME = ROL.ROLENAME
and ROL.CLASSID = SAG.CLASSID

To see which users have access to a particular query tree: -

select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME = 'QRY_TMUK_ALL'
and ROL.ROLENAME = USR.ROLENAME
and USR.OPRID = OPR.OPRID
order by ROLENAME, OPR.OPRDEFNDESC

How To Add A Feed to your Blogger Blog

2007-04-20T08:07:00.000-07:00

It's official, news aggregaters are great. They're a one stop place where you can check on a host of websites that are of interest to you, and quickly see updates that have been added.
Thus saving lots of time you can utilise by drinking cups of tea and randomly surfing other sites that aren't on your interest radar.

Later versions of browsers like IE and Firefox allow you to subscribe to a sites feeds from the browser itself and again get your info served up on a plate, with a couple of waffer thin mints on the side.

As such if you've got a blog yourself, then it's not a bad idea to give your readership (both of them) the opportunity to subscribe to your site by adding a link. The following simple bite sized instructions tell you how you can do this from blogger using Feedburner.
Go to your blog and copy its URL to the clipboard (Control+C is always a good bet for this).

Go to the Feedburner website at http://www.feedburner.com
In the shaded box under the imaginative heading "Start FeedBurning Now" paste your URL.
Click the Next>> button.
On the identify feed source page displayed select one of the sources displayed. As an RSS fan, I'm going with the RSS one for this example. For those of you who don't know their RSS from their elbow, RSS stands for Really Simple Syndication. No really, it does. You can also repeat the process for Atom later if you want.
Click Next>> again.
After tinkering with your feed description and address, or not if life's too short, do one of two things. Either, create an account by filling in a username, password and e-mail address; or login using an existing Feedburner account if you've already got one. If you've already got one you probably don't need to be reading these instructions, but hey.
Click Activate Feed>>
Click Skip directly to feed management. You can always refine your feed options later.
Click on the Publicize tab
Under Make it Easy to Subscribe click on the friendly graphic link (below the bullet point Chickletize your website).
Choose a feed icon (I always prefer the wee one)
Scroll to the bottom of the page
Under Use as a widget in select Blogger from the drop down list.
Press Go!
You are now directed to the Add Page Element page of your blog. If you have multiple blogs, select the one you are burning a feed for.
Press the Add Widget button.
Position your subscripew now widget where you want it, and press Save.

Congratulations, you've now got a feed on your blog. Have a cup of tea to celebrate.

Spooling Your Output

2007-04-18T16:08:00.000-07:00

When writing SQL scripts in a production environment it's always a good idea to spool the fruits of your labor to a log file when it is run. It also gives you the opportunity to wear your "I Told You So" teeshirt when someone denies you've run something.

Selecting the database name from sys.v_$database also gives you the warm feeling that you've run your deletion script in a development environment and not accidentally against production when you review your log file. Alternatively it could give you a more localised warm feeling down your trouser leg if you find out it's the other way round !

The set pagesize 0 command formats your SQL output better so you get one continuous page rather than giving you reams of headers.

The set timing on statement gives you timings for the SQL being run. This gives future runners of the script an idea of how long the SQL takes, which can bu useful, especially when running it in production.

whenever SQLERROR exit failure;

spool "/filename"

set echo on;
set feedback on;
set verify on;
set pagesize 0;
set termout on;
set timing on;

select name from sys.v_$database;

/************************/
/* Insert your SQL here */
/************************/

spool off;

Set Current Schema

2007-04-18T16:01:00.000-07:00

I always forget the syntax for setting the current schema in Oracle. Then again that's why the great Flying Spaghetti Monster created blogs.

alter session set current_schema = sysadm