Skip navigation.

Duncan Davies

Syndicate content The PeopleSoft Tipster Blog
A PeopleSoft Tips and Tricks Blog
Updated: 11 hours 33 min ago

Why we won’t need a PeopleSoft v9.3

Tue, 2014-11-25 09:00

3291330534_84cc20eac9_z[1]I caught up with Paco Aubrejuan’s “PeopleSoft Townhall” webinar from Quest the other day. Paco is Senior VP of Development for the PeopleSoft product line and it was a really interesting listen. The session can be found here, although you need to sign-up with Quest to view it. It’s an hour long and he discusses the future direction of the PeopleSoft product family plus the new simplified and mobile user experience for PeopleSoft, the new Fluid User Interface (UI) and the delivery model of more frequent, customer-driven product enhancements which is enabled by PeopleSoft Update Manager.

Most interestingly for me though, was the Q&A section at the end. Paco tackled the v9.3 question head on. I’ve transcribed his words, and I think it’s a strong and positive message for those with an interest in the PeopleSoft product line. Here are the ‘best bits':

On PUM:

We’re calling our model PeopleSoft Selective Adoption … and let me be specific about what it means, we’re going to deliver new capabilities about 2 to 3 times a year (and may deliver some functionality more frequent than that). Once you’re on 9.2 you can get this functionality without upgrading ever.

On PeopleSoft v9.3:

Should I upgrade to PeopleSoft 9.2 or should I wait for 9.3? There is no 9.3. We don’t have a 9.3 codeline, there’s no 9.3 plan, our plan is to never do a 9.3 and we’re going to continuously deliver on 9.2 using the PeopleSoft Selective Adoption and so you should not be waiting for a 9.3. … We’re just going to continue extending the timelines for PeopleSoft 9.2 so the idea is that there is no more upgrade and premier support will just continue.

On why a 9.3 isn’t needed:

The risk we take with saying that there’s no 9.3 is that people read into that and say that PeopleSoft is dead. … That’s not true. The investment level that we’re making in the product does not change with this delivery model at all. … We’re delivering all the Fluid functionality without a new release. We’ve never done that before. The only thing that this is comparable to is the 8.0 version when we moved from client-server to the internet, and that was a major release. We’re now doing something equivalent to that without even a minor release. It’s now just selective features that you can take as long as you’re on 8.54. So PeopleSoft is not dead, and having no PeopleSoft 9.3 does not mean that PeopleSoft is dead.

So, we now have a definitive answer to the v9.3 question. I think it’s a strong and positive message which is backed up with evidence of the investment that Oracle are putting in to the product family, and a nod to the fact that PeopleSoft is adapting its model to the changing needs of the customer.


Generating a Calendar View of Employee Absence

Mon, 2014-11-24 05:00

This blog post has been kindly contributed by Richard Yip. Richard is a PeopleSoft HCM & Payroll Technical Consultant at Santander Bank here in the UK. He is a long-term PeopleSoft techie and a regular at the UKOUG conferences.

We have posted two guest posts from Richard already, they can be found here:

Generating a custom Org Chart in PeopleSoft

Interacting client-side JavaScript with server-side PeopleCode

Introduction

In PeopleSoft HCM 9.0, an employee’s absence history is displayed in the usual tabular format (a grid with rows and columns). However, we felt the presentation was a bit uninspiring, and needed a better look to it. So we custom built a transaction whereby absences are displayed on a calendar used in both employee and manager self-service transactions.

[Duncan: this is actually a customisation that has been repeated in one shape or form by a number of customers, however I’m not aware of anyone else openly sharing the code behind their calendar. I’m very grateful to Richard’s generosity in sharing this and hope that others find it useful.

Richard’s code was written on the Oracle Database, however it could be translated to work in a similar manner on SQL Server.

I particularly like Richard’s clever trick of storing SQL Objects in HTML (as storing SQL in SQL Objects messes up the formatting).]

This is the end result:

calendar

Absence and other data comes from the following PeopleSoft tables:

  • GP_ABS_EVENT,
  • GP_ABS_EVT_JR,
  • GPGB_ABS_CODE,
  • EMPLOYEES

I will take you through the steps involved. If you want to skip this, the full listing is enclosed at the end.

3.    SQL 3.1.      Generating the calendar

This is part of the WITH clause. The %Bind(2) variable comes from user input on the page. This uses the “connect by level” as rows generator, depending on the number of days in a year.


WITH calendar AS
select ROWNUM ,
to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM – 1 daily ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'DAY') day ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'D') dow ,
to_number(to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'DD')) dom ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'WW') week ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'MM') mm ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'Month') month ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'YYYY') year
FROM   dual
CONNECT BY LEVEL <=(
SELECT
/* test for leap year */
decode(to_number(to_char(last_day(to_date('01-feb-'|| '%Bind(:2)' ,'DD-mon-yyyy')) ,'DD')) ,
29 ,366 ,365)
FROM   dual ))

3.2.      Extracting absences from the PeopleSoft absence tables

This is also part of the WITH clause. %Bind(:1) comes from the search page. The oracle hints stop parallel execution as these tables are partitioned at our site, and lead to better performance (as observed from  v$active_session_history)


abs_hist AS
SELECT
/*+ no_parellel(a) no_parallel(b) no_parallel(c) */
a.emplid,
a.bgn_dt,
a.end_dt,
a.gpgb_absence_type,
a.gpgb_absence_code,
c.descr
FROM   ps_gpgb_abs_evt_jr a ,
ps_gp_abs_event b,
ps_gpgb_abs_code c
WHERE  a.emplid = '%Bind(:1)'
AND    a.emplid = b.emplid
AND    a.empl_rcd = b.empl_rcd
AND    a.pin_take_num = b.pin_take_num
AND    a.bgn_dt = b.bgn_dt
AND    a.end_dt = b.end_dt
AND    a.gpgb_absence_type = c.gpgb_absence_type
AND    a.gpgb_absence_code = c.gpgb_absence_code
AND    b.voided_ind = 'N'
AND    b.bgn_dt <= to_date('3112%Bind(:2)', 'ddmmyyyy')
AND    b.end_dt >= to_date('0101%Bind(:2)','ddmmyyyy')

3.3.      Joining steps 1 and 2

The lines from 16 to 28 play an important part later.

SELECT mm,
Lead (mm) over(ORDER BY mm, dom) NEXT_MM,
dom,
dow,
Ltrim (Rtrim (day, ' '), ' ') DAY,
>Coalesce ((SELECT gpgb_absence_type
FROM   abs_hist
WHERE  daily BETWEEN bgn_dt AND end_dt), ' ') TYPE
/*abs_hist from 2*/
,
Coalesce((SELECT descr
FROM   abs_hist
WHERE  daily BETWEEN bgn_dt AND end_dt), ' ') descr
/*abs_hist from 2*/
-- the rest of columns are for html purpose
, '<table style="width: 100%" class="table_heading"> <tr>' first_row,
'</tr> </table> </td> </tr> <tr>' break_on_month_group,
'</tr> </table> </td>' break_on_month,
'(''04'',''07'',''10'')' month_group_value,
'<td valign="top"> <table style="width: 100%" class="sub_tab_heading" border="1"> <tr> <td colspan="7" class="month_heading"> ' || month || '</td> </tr> <tr> <td class="day_heading">Mon</td> <td class="day_heading">Tue</td> <td class="day_heading">Wed</td> <td class="day_heading">Thu</td> <td class="day_heading">Fri</td> <td class="day_heading">Sat</td> <td class="day_heading">Sun</td> </tr> <tr>' month_cell,
'<td class="default_cell">' normal_cell,
'<td></td>' blank_cell1,
'<td></td> <td></td>' blank_cell2,
'<td></td> <td></td> <td></td>' blank_cell3,
'<td></td> <td></td> <td></td> <td></td>' blank_cell4,
'<td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell5,
'<td></td> <td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell6,
'<td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell7
FROM   calendar /*from step 1*/

3.4.      Adding in CSS and applying logic

Now we add Styling and some logic to the results from step 3.3 to produce the desired html for the page. The 1st row is when rownum =1 and last row is when NEXT_MM is null. DOW stands for day of week and DOM stands for day of month.

</pre>
SELECT
CASE
WHEN ROWNUM = 1 THEN '<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Mon</title> <style type="text/css">
.month_heading { color: #FFFFFF; background-color: red; font-weight:bold; }
.day_heading { color: black; background-color: #C0C0C0; font-weight:bold; font-family:Arial,sans-serif;font-size:12pt; }
.table_heading { border: 0px solid #000000; }
.sub_tab_heading { border-style: solid; border-width: 1px; border-collapse:collapse; }
.style_mat { font-family:Arial,sans-serif;font-size:10pt; background-color: purple; color: white; font-style: italic; font-weight:bold; }
.style_sck { font-family:Arial,sans-serif;font-size:10pt; background-color: yellow; color: black; font-style: italic; font-weight: bold; width: 40px; }
.style_flu  { font-family:Arial,sans-serif;font-size:10pt; background-color: red; color: black; font-style: italic; font-weight: bold; width: 40px; }
.style_oth_sck { background-color: orange;  color: #FFFFFF; font-weight:bold; }
.default_cell { width: 40px;  font-family:Arial,sans-serif;font-size:10pt; } </style> </head> <body>' || '<table width="500px"> <tr><td class="style_sck" style="width:20px"></td> <td>Sickness</td> <td class="style_mat" style="width:20px"></td> <td>Maternity</td> <td class="style_flu" style="width:20px"></td> <td>Flu Pandemic</td> <td class="style_oth_sck" style="width:20px"></td> <td>Other</td> </tr> <tr><td colspan="8" style="font-family: Arial,sans-serif; font-size: 10pt; font-style: italic;">**Place mouse over highlighted day to see details</tr> </table>'
|| first_row
|| month_cell
||
CASE
WHEN dow = '1' THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '2' THEN blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '3' THEN blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '4' THEN blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '5' THEN blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '6' THEN blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
ELSE
CASE
WHEN dom = '1' THEN
CASE
WHEN dow = '1' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '2' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '3' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '4' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '5' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '6' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '7' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td></tr>'
END
ELSE normal_cell
END
WHEN dow = '7'
AND    mm = next_mm THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td></tr>'
WHEN dow = '7'
AND    mm <> next_mm THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '1' THEN '<tr>'
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
||
CASE
WHEN next_mm IS NULL THEN '</tr></table></td>'
|| <strong>Chr</strong>(10)
|| '</tr></table></body></html>'
END
END html_data ,
TYPE ,
descr
FROM ...

3.5.      Adding in more logic to produce the desired colour for the absences.
</pre>
SELECT
CASE
WHEN TYPE = 'MAT' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_mat">')
WHEN TYPE = 'SCK' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_sck" title="'
|| descr
|| '">')
WHEN TYPE = 'FLU' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_flu">')
ELSE html_data
END
FROM ...

3.6.      Integrating the SQL with PeopleSoft.

This is the peoplecode from YEAR_CODE.RowInit. The complete SQL is stored in the HTML object as RY_ABS_CAL_HTM. I dislike storing SQL in the SQL object as it distorts the formatting and render is unreadable.


Local SQL

If %Page = Page.AN_EMPL_ABS_CAL Or

%Page = Page.AN_ABS_CAL_ESS Then

&year_code = Year(%Date);

DERIVED_ABS_AN.YEAR_CODE = &year_code;

&emplid = PERSON.EMPLID;

DERIVED_ABS_AN.HTMLAREA1 = "";

&html = GetHTMLText(HTML.RY_ABS_CAL_HTM, &emplid, &year_code);

&sql = CreateSQL(&html);

While &sql.Fetch(&html_data)

DERIVED_ABS_AN.HTMLAREA1 = DERIVED_ABS_AN.HTMLAREA1 | &html_data;

End-While;

End-If;

4.      Conclusion

I have not outlined the page with all its attributes nor have I enclosed all the peoplecode as I feel these are easily be accomplished by a developer.

In “HTMLising” the SQL, I am always mindful of “ORA-01489: result of string concatenation is too long”. I could have keep the CSS out of the SQL and inject the CSS part on the page using a static html object. However, this demonstrates the combine power of SQL with embedded HTML.

5.      Full Listing
select
case
when type = 'MAT' then replace(html_data, '<td class="default_cell">', '<td class="style_mat">')
when type = 'SCK' then replace(html_data, '<td class="default_cell">', '<td class="style_sck" title="' || descr || '">')
when type = 'FLU' then replace(html_data, '<td class="default_cell">', '<td class="style_flu">')
else html_data
end
from
(
--
-- generate HTML and CSS for calendars
--
select
case
when rownum = 1 then
'<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mon</title>
<style type="text/css">
.month_heading {
color: #FFFFFF;
background-color: red;
font-weight:bold;
}
.day_heading {
color: black;
background-color: #C0C0C0;
font-weight:bold;
font-family:Arial,sans-serif;font-size:12pt;
}
.table_heading {
border: 0px solid #000000;
}
.sub_tab_heading {
border-style: solid;
border-width: 1px;
border-collapse:collapse;
}
.style_mat {
font-family:Arial,sans-serif;font-size:10pt;
background-color: purple;
color: white;
font-style: italic;
font-weight:bold;
}
.style_sck {
font-family:Arial,sans-serif;font-size:10pt;
background-color: yellow;
color: black;
font-style: italic;
font-weight: bold;
width: 40px;
}
.style_flu
{
font-family:Arial,sans-serif;font-size:10pt;
background-color: red;
color: black;
font-style: italic;
font-weight: bold;
width: 40px;
}
.style_oth_sck
{
background-color: orange;
color: #FFFFFF;
font-weight:bold;
}
.default_cell
{
width: 40px;
font-family:Arial,sans-serif;font-size:10pt;
}
</style>
</head>
<body>' ||
'<table width="500px"><tr><td class="style_sck" style="width:20px"></td><td>Sickness</td>
<td class="style_mat" style="width:20px"></td><td>Maternity</td>
<td class="style_flu" style="width:20px"></td><td>Flu Pandemic</td>
<td class="style_oth_sck" style="width:20px"></td><td>Other</td>
</tr>
<tr><td colspan="8" style="font-family:Arial,sans-serif;font-size:10pt;font-style: italic;">**Place mouse over highlighted day to see details</tr>
</table>' ||
first_row || month_cell ||
case
when DOW = '1' then normal_cell || to_char(DOM) || '</td>'
when DOW = '2' then blank_cell1 || normal_cell || to_char(DOM) || '</td>'
when DOW = '3' then blank_cell2 || normal_cell || to_char(DOM) || '</td>'
when DOW = '4' then blank_cell3 || normal_cell || to_char(DOM) || '</td>'
when DOW = '5' then blank_cell4 || normal_cell || to_char(DOM) || '</td>'
when DOW = '6' then blank_cell5 || normal_cell || to_char(DOM) || '</td>'
else                blank_cell6 || normal_cell || to_char(DOM) || '</td>'
end
else
case
when DOM = '1' then
case
when DOW = '1' then
case when MM in ('05','09') then break_on_month_group || month_cell || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '2' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell1 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell1 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '3' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell2 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell2 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '4' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell3 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell3 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '5' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell4 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell4 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '6' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell5 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell5 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '7' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell6 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell6 || normal_cell || to_char(DOM) || '</td></tr>'
end
else normal_cell
end
when DOW = '7' and MM = NEXT_MM  then normal_cell || to_char(DOM) || '</td></tr>'
when DOW = '7' and MM <> NEXT_MM then normal_cell || to_char(DOM) || '</td>'
when DOW = '1' then '<tr>' || normal_cell || to_char(DOM) || '</td>'
else                            normal_cell || to_char(DOM) || '</td>'
end
|| case when NEXT_MM is null then '</tr></table></td>' || chr(10) || '</tr></table></body></html>' end
end html_data
, type
, descr
from
(
with
/* Stage 1  Creating calendar using Dual with connect by level testing for leap year. */
calendar as ( SELECT rownum
,  to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1                           DAILY
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'DAY')           DAY
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'D')             DOW
,  to_number(to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'DD')) DOM
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'WW')            WEEK
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'MM')            MM
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'Month')         MONTH
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'YYYY')          YEAR
FROM dual
connect by level  <= (  SELECT /* test for leap year */ decode(to_number(to_char(last_day(to_date('01-feb-' || '%Bind(:2)'  ,'DD-mon-yyyy'))  ,'DD'))  , 29  ,366  ,365)   FROM dual ))
/*Extract absences for the appropriate time span*/
, abs_hist as
( SELECT /*+ no_parellel(a) no_parallel(b) no_parallel(c) */
a.emplid, a.bgn_dt, a.end_dt, a.gpgb_absence_type, a.gpgb_absence_code, c.descr
from ps_gpgb_abs_evt_jr a   , ps_gp_abs_event b, ps_gpgb_abs_code c
where a.emplid            = '%Bind(:1)'
and a.emplid            = b.emplid
and a.empl_rcd          = b.empl_rcd
and a.pin_take_num      = b.pin_take_num
and a.bgn_dt            = b.bgn_dt
and a.end_dt            = b.end_dt
and a.gpgb_absence_type = c.gpgb_absence_type
and a.gpgb_absence_code = c.gpgb_absence_code
and b.voided_ind        = 'N'
and b.bgn_dt            <= to_date('3112%Bind(:2)', 'ddmmyyyy')
and b.end_dt            >= to_date('0101%Bind(:2)','ddmmyyyy')
)
/*Stage2. Joining data from Stage1
Start with generated yearly calendar(above) and merge in absence details (criteria DAILY between bgn_dt and end_dt)
*/
select
MM
, lead(mm) over(order by mm, dom) NEXT_MM
, DOM
, DOW
, LTRIM(RTRIM(DAY,' '),' ') DAY
, coalesce((select gpgb_absence_type from abs_hist where DAILY between bgn_dt and end_dt),' ') type
, coalesce((select descr from abs_hist where DAILY between bgn_dt and end_dt),' ') descr
-- rest of columns are for html purpose
, '<table style="width: 100%" class="table_heading"><tr>'           first_row
,'</tr></table></td></tr><tr>'                               break_on_month_group
,'</tr></table></td>'                                        break_on_month
, '(''04'',''07'',''10'')'                            month_group_value
,'<td valign="top">
<table style="width: 100%" class="sub_tab_heading" border="1">
<tr>
<td colspan="7" class="month_heading">' || MONTH || '</td>
</tr>
<tr>
<td class="day_heading">Mon</td>
<td class="day_heading">Tue</td>
<td class="day_heading">Wed</td>
<td class="day_heading">Thu</td>
<td class="day_heading">Fri</td>
<td class="day_heading">Sat</td>
<td class="day_heading">Sun</td>
</tr>
<tr>'                                                    month_cell
, '<td class="default_cell">'                                       normal_cell
, '<td></td>'                                                       blank_cell1
, '<td></td><td></td>'                                              blank_cell2
, '<td></td><td></td><td></td>'                              blank_cell3
, '<td></td><td></td><td></td><td></td>'                     blank_cell4
, '<td></td><td></td><td></td><td></td><td></td>'                   blank_cell5
, '<td></td><td></td><td></td><td></td><td></td><td></td>'          blank_cell6
, '<td></td><td></td><td></td><td></td><td></td><td></td><td></td>' blank_cell7
from calendar
)
)

Cedar Wins Gold – PeopleSoft Partner of the Year

Tue, 2014-10-28 08:00

If you follow any number of those in the Partner community on LinkedIn you’ll have seen many of us asking that you vote for us in the UKOUG’s annual Partner of the Year competition. All of the partners are really grateful for your votes as winning an award selected by end-users carries significant prestige.

I’m delighted that the company which I now work for – Cedar Consulting – were awarded 1st place (Gold) for PeopleSoft Partner of the Year for 2014/2015.

Simon cropped

Simon (right), collecting the award from David Warburton-Broadhurst – the UKOUG’s President

Cedar were also thrilled to win Silver in the Fusion Partner of the Year awards, further establishing our reputation as the go-to partner for Fusion/Taleo for all existing PeopleSoft customers.

SimonWr (normal)Simon Wragg, Director at Cedar Consulting said, “We are honoured to receive both the PeopleSoft and Fusion Partner of the Year awards amongst such a strong group of finalists. Cedar Consulting are delighted to be recognised as one of the leading partners within the UK Oracle User Group community. Winning these awards and knowing that so many votes were cast from Oracle customers is a real testament to the service we have provided over the last 12 months”  

We’d like to thank all of you who took the time to vote for us, we’re very grateful for your support.

 


Error unzipping PeopleSoft Images

Fri, 2014-10-03 18:14

The new PUM images are a boon for anyone wanting to get a PeopleSoft instance up and running quickly. Once you’ve downloaded the zip archives however, you might find that the delivered zip file doesn’t work by default for everyone.

The line:

unzip HCM-920-UPD-008_OVA_2of11.zip

gives me the following error:

'unzip' is not recognized as an internal or external command

I’m not sure where the unzip utility is supposed to be from, but it’s not delivered as part of Windows 8.1. I typically use the excellent 7zip utility for my zip/archiving needs, so I need to amend the script slightly.

I add the following line near the top:

set PATH=%PATH%;C:\Program Files\7-Zip\

so that I can reference the extraction tool with just the filename, then I change each archive line to use 7zip instead, thus:

7z e HCM-920-UPD-008_OVA_2of11.zip

PeopleSoft and Shellshock

Mon, 2014-09-29 15:23

As many will have no doubt heard, there’s a new vulnerability that has been spotted, and there are already exploits for it in the wild.

The vulnerable systems are those running Bash – so Windows machines are safe, it’s just Unix/Linux and MacOSX.

Security Researcher Kasper Lindegaard from Secunia rates this as a bigger issue than the Heartbleed exploit discovered in April this year. “Heartbleed only enabled hackers to extract information, Bash enables hackers to execute commands to take over your servers and systems.”

The US government has rated this 10 out of 10 from severity point of view.

Oracle have been quick to react to this threat, and have issued a security alert here. It includes this chilling text:

This vulnerability may be remotely exploitable without authentication, i.e. it may be exploited over a network without the need for a username and password. A remote user can exploit this vulnerability to execute arbitrary code on systems that are running affected versions of Bash.


PeopleSoft and Web Browsers – The Guide

Mon, 2014-09-22 08:00

browsersThe topic of PeopleSoft/PeopleTools versions and web browsers is often a complicated one, yet it’s an issue that every client will face when they either upgrade PeopleTools or move to a new Application version that contains a Tools increase.

Cedar have recently been asked by a client for some assistance to get a definitive answer to the important questions and we thought it would be useful to share this information. We’ve put together a white paper that shows you the relevant browser versions for PeopleTools 8.54 and PeopleTools 8.53 (i.e. the versions that customers are likely to be upgrading to over the next year or so):

Cedar Consulting White Paper – PeopleSoft and Web Browsers

We hope that it saves you some time during your next upgrade.