jump to navigation

Generating a Calendar View of Employee Absence November 24, 2014

Posted by Duncan in PeopleTools.
trackback

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
)
)
Advertisements
%d bloggers like this: