select RECNAME,
RECDESCR
from PSRECDEFN
where RECNAME like 'PS%'
and RECDESCR <> ' '
Monday, 25 May 2009
Listing All PeopleSoft System tables
All PeopleSoft system tables can be dumped with: -
Dumping an Application Engine's Temp Tables List
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'
Monday, 2 March 2009
Blankety blank, blankety blank...
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.
The above format doesn't show a thousands delimiter. To get that in use: -
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!
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!
Friday, 6 February 2009
Peering into the depths of the PeopleSoft Process Scheduler Queue
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.
Be careful when you stare into the void however, the void might stare back at you.
-- --------------------------
-- 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
Tuesday, 6 January 2009
One Step Beyond
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.
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
Friday, 14 November 2008
Fun with Time and Labor Dates
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.
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: -
Enjoy.
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.
Monday, 22 September 2008
Subscribe to:
Posts (Atom)
