Skip navigation.

Duncan Davies

Syndicate content The PeopleSoft Tipster Blog
A PeopleSoft Tips and Tricks Blog
Updated: 16 hours 42 min ago

I’m William Reynolds and this is How I Work

Tue, 2015-03-03 09:00

William joined PeopleSoft in November of 1995 as a developer in the Inventory product. Since then he has worked in various roles within the FSCM pillar eventually landing in the Enterprise Architecture group where he straddles the world between PeopleTools and Applications. He currently works with other Architects in each of the pillars on various projects like security, accessibility and PUM.

William

Name: William Reynolds

Occupation: Senior Application Architect, Enterprise Architecture
Location: Home office, Chicago, IL
Current computer: ThinkPad W520 & T430
Current mobile devices: iPhone 6

What apps/software/tools can’t you live without?
Probably the two pieces of software I’d be lost without are LastPass and Xmarks. Keeping track of hundreds of bookmarks along with hundreds of passwords between work and home would be nearly impossible without them. Lately I’ve also been leaning heavily on Notepad++ for various projects.

Besides your phone and computer, what gadget can’t you live without?
When I’m not on my laptop I usually have my iPad handy for email, calendar and web access.

What’s your workspace like?
I’d like to say it’s a clean, modern, calm fortress of solitude but I work from home and have three kids under the age of 12 so its state of clutter really depends on the week. I built a treadmill desk a couple years ago but these days it’s more of a standing desk. Every week you read a new article about sitting versus standing so I try to stand as much as I can during the dozen or so conference calls throughout the week. Walking while working takes some getting used to. Email is pretty easy to get through while in motion but for something like SQL or coding I find I have to be at least standing still if not sitting to really concentrate.

My workspace

What do you listen to while you work?
My tastes run the gambit and usually it depends on what task I’m doing. For things that require some heads down concentration it’s either Brubeck or Sinatra. If I’m cranking through email or pulling together a slide deck then it’s something more peppy like top 40.

What PeopleSoft-related productivity apps do you use?
It really just depends on what project I’m working on at the moment.  I prefer Firefox as my browser at work, Notepad++ for text work, and SQL Developer.

What SQL/Code do you find yourself writing most often?
Lately SELECT * FROM PSPROJECTITEM;

What would be the one item you’d add to PeopleSoft if you could?
At the moment, my ideal change would be to get rid the Windows dependency in all our client tools.

What everyday thing are you better at than anyone else?
I would say I have a knack for looking at the big picture and being able to put myself in a customer’s shoes.

What’s the best advice you’ve ever received?
Not sure where it came from but “don’t build a Cadillac when what you need is a Honda.”


Cedar’s Oracle Cloud and PeopleSoft Day

Sat, 2015-02-28 18:36

Cedar held it’s annual Oracle Cloud and PeopleSoft Day in London on Friday, with almost a hundred people in attendance (about 80 customers, plus staff from Oracle and Cedar).

It was a great success, with a really positive vibe – customers are looking to do great things with both PeopleSoft and Oracle’s Cloud suite – and a privilege to be part of.

Here are some photos from the day:

Graham welcomes everyone
2015-02-27 10.05.59

 

Marc Weintraub gave a great keynote (from his office at 2:30am!)
Marc Weintraub - Keynote

 

Liz and I discuss the practical applications of the PeopleSoft RoadmapLiz and Duncan - PeopleSoft Roadmap and Cloud

 

Mike takes us through the upcoming Oracle Cloud Release 10 features
2015-02-27 12.26.53

 

Jo talks about ‘Taleo for PeopleSoft People’
Jo and Duncan - Taleo for PeopleSoft People

 

 Simon handles the prize draw2015-02-27 15.48.56

So, a fun event with lots of knowledge sharing. My absolute favourite part is being able to connect customers who can help each other though. I lost count of the number of times we were able to say “oh, you’re doing <some project> are you? In that case, let me introduce you to <another client> as they’ve just finished doing that very thing” and then being able to leave them to share their experiences.


I’m Alex Lightstone and this is how I work

Mon, 2015-02-16 08:00

First up in the ‘How I work‘ series for 2015 is Alex Lightstone. For those in the UK PeopleSoft marketplace Alex should need no introduction, for everyone else though here’s a brief bio:

Alex spent the 2000’s at Oracle, initially supporting, and then as Product Support Manager for PeopleSoft Global Payroll. When he left Oracle in 2010 there was a battle for his services and we’re very grateful that he selected Cedar where he forms part of our ‘trinity of UK-based GP gurus’ – alongside Bill and Gary. Alex can be frequently found sharing his knowledge at Cedar events, and UKOUG conferences and SIGS. His skills aren’t confined to GP however, and he’s already fixed a few bits of code that I’ve left half-completed (including the Field Watermarks).

Alex

Name: Alex Lightstone

Occupation: Lead Consultant at Cedar Consulting
Location: When not on client site, either home or the Cedar Office (Kings Cross, London)
Current computer: Dell Latitude E6430
Current mobile devices: HTC One (M7), Alcatel 4G USB Dongle
I work: best when I understand exactly what everyone else is doing so I can see the whole picture

What apps/software/tools can’t you live without?
As long as I have an internet connection I’m happy. I regularly change my browser but am reliant on Microsoft Office applications (yes, even Outlook). I’ve recently discovered Microsoft One Note and now wonder how I ever managed without it. Of course, App Designer, Data Mover and SQL Developer are a big part of my day to day work, as are Citrix, Remote Desktop and various flavours of VPN software when working remotely as needed to connect to client sites. Smartphones are useful but when space and battery power permit I’d rather be hooked up to my laptop with my 4G mobile broadband. Notepad and Paint are useful pieces of software in my opinion – I know there are alternatives but I like to keep it simple. And last but not least … Spotify.

Besides your phone and computer, what gadget can’t you live without?
Sat Nav is an absolute but is included with most smartphones these days. I’m happy with my phone and laptop. A smart-watch would be nice but I haven’t bought one yet.

What’s your workspace like?
They say “small is beautiful”, which is just as well in my case. Since the birth of my second child, my workspace has been relegated to a corner of my bedroom.

IMAG0260

What do you listen to while you work?
I have somewhat eclectic tastes when it comes to music. I often listen to music via Google Music or Spotify. Anything goes … rock, classical, dance – it’s all on my playlists.

What PeopleSoft-related productivity apps do you use?
I use App Designer, SQL Developer and Notepad (for SQR and COBOL). I occasionally use Firebug or the Chrome Developer Tools to resolve HTML issues. PeopleBooks are useful for reference.

Do you have a 2-line tip that some others might not know?
Global Payroll is just a programming language where you don’t write code. Approach it as a programmer and it will make a lot more sense.

What SQL/Code do you find yourself writing most often?
Working in the world of payroll, I spend a lot of time querying the payroll results tables (GP_RSLT_XXXX).

SELECT PIN_NUM FROM PS_GP_PIN WHERE PIN_NM=<Element Name> is SQL that I use a lot.

What would be the one item you’d add to PeopleSoft if you could?
Better error handling – there are too many generic error messages where your only option is to delve into the code to determine the reason for the error.

What everyday thing are you better at than anyone else?
Teaching myself – it’s how I learnt most of what I know.

What’s the best advice you’ve ever received?
“The PeopleSoft world is a small place, don’t upset people, you will have to work with them again” – Anonymous


Adding Watermarks to PeopleSoft fields

Wed, 2015-02-11 10:00

The Cedar tech team has recently discovered a great tweak to improve the end-user experience in PeopleSoft.

Many well designed websites use Watermark text to provide a visual hint to the user what they should put into a field. We felt that PS Self Service users would appreciate the enhancement.

screenshot

Head over to the Cedar Blog to find out more:

http://www.cedarconsulting.co.uk/news-details/February-2015-Adding-Watermarks-to-PeopleSoft-Fields/


The Year 2014 in Review

Sat, 2015-01-31 17:15

Once each year completes I try to do a run down of which posts have garnered the most attention during the last 12 months, both on this blog and on the PeopleSoft Weekly newsletter. Here are the most viewed stories from 2014.

PeopleSoft Weekly Newsletter

During 2014 the subscriber base grew from 602 to 914 (a net increase of just over 300), and this was despite being asked to remove over 50 subscribers when I left my previous employer. The open rate (i.e. the amount of people that view each email) continues to cover around the 45-50% mark – against an industry average of 17%.

There were nearly 800 links in the newsletters over the year. Here are the 30 that received the most clicks:

  1. Tools to increase productivity for PeopleSoft Developers and Users (124 unique visits)
  2. PeopleSoft 9.3 – A Clarification (120)
  3. How to know Record/Field names without opening Application Designer (120)
  4. PeopleSoft 8.53/9.2 Prototyping Tool (118)
  5. This is why you never end up hiring good developers (115)
  6. Compiling PeopleCode (108)
  7. It’s official, there is no PeopleSoft 9.3 (104)
  8. I’m Jim Marion and this is how I work (104)
  9. An Introduction to PeopleTools 8.54 (part 1) (101)
  10. PeopleTools 8.54 Sandbox (99)
  11. An Introduction to PeopleTools 8.54 (part 2) (93)
  12. How to Effectively Write PeopleSoft Queries (91)
  13. 17 Famous Logos That Have A Hidden Message (91)
  14. The PeopleSoft Roadshow 2014 – What’s coming next for PeopleSoft? (90)
  15. Oracle bests Rimini Street in latest lawsuit ruling (90)
  16. Google releases set of beautiful, freely usable icons (89)
  17. 10 Tricks to Appear Smart During Meetings (87)
  18. Disabling the back button in PeopleSoft (87)
  19. PeopleSoft Process Flow basics (86)
  20. 23 Evergreen Developer Skills to Keep you Employed Forever (85)
  21. I’m Simon Wilson and this is how I work (83)
  22. PeopleCode Coding Discipline (83)
  23. PeopleSoft Application Designer themeing (82)
  24. Make a Field Required using PeopleCode (82)
  25. I’m Anton de Weger and this is how I work (81)
  26. Unlimited Session Timeout (79)
  27. Upgrading PeopleTools with Zero Downtime (78)
  28. Creating a Launch Pad Experience with the PeopleSoft Interaction Hub (77)
  29. California payroll: Another failure waiting to happen? (76)
  30. PeopleCode Record Snapshot (75)

One of the most pleasing things about the above list is that there is a wide variety of blogs from many different bloggers and companies featured, which shows that the PeopleSoft eco-system is as active as ever. It is a good thing that too many of the less serious ‘and one more thing’ links didn’t manage to get into the top 30! I’m also rather pleased that all 3 of the 2014 ‘how i work’ posts made the top 30 … it goes to show that people are as important as software.

PeopleSoft Tipster Blog

In 2014 this blog had 144,203 views, coming from 80,833 visitors. The most visited posts are all above so I won’t repost another list, but to give you an idea of numbers the top one (about PeopleSoft 9.3) got more than 5,000 views in the year and the PeopleSoft Roadshow one more than 3,000.

Most of the visitors came from the US (79,200 views or 63%) followed by India (16%), Canada (6%) and the UK (5%).

I’m keen to do a few more ‘How I Work’ posts. If there’s someone that merits inclusion that hasn’t appeared so far please let me know. (My email is in the Get In Contact pane in the top right.)


Two Changes in PeopleTools Requirements

Tue, 2015-01-13 07:00

Oracle have just announced two changes to what they require customers to be running on.

PeopleTools 8.53 Patch 10 or above for PUM Patches

If you’re on PeopleSoft v9.2 and using the Update Images to select the patches to apply then Oracle ‘strongly advises’ customers to be on the .10 patch of PeopleTools 8.53 or higher.

From Oracle:

FSCM Update Image 9.2.010 and higher, HCM Update Image 9.2.009 and higher, and ELM Update 9.2.006 and higher all need PeopleTools 8.53.10 for many of the updates and fixes to be applied. Failure to update your PeopleTools patch level to PeopleTools 8.53.10 or higher will result in the inability to take these updates and fixes. It may also inhibit you from applying critical maintenance in the future.

New PeopleTools Requirements for PeopleSoft Interaction Hub

Oracle also announced that they’re changing the support policy for Interaction Hub and PeopleTools. Basically, if you use Interaction Hub you must upgrade to a PeopleTools release no later than 24 months after that PeopleTools release becomes generally available.

It was originally a little confusingly worded, but there’s now an example that made it clearer for me: For example, PeopleTools 8.53 was released in February 2013. Therefore, customers who use Interaction Hub will be required to upgrade to PeopleTools 8.53 (or newer, such as PeopleTools 8.54) no later than February 2015 (24 months after the General Availability date of PeopleTools 8.53). As of February 2015, product maintenance and new features may require PeopleTools 8.53. I suspect that this is going to impact quite a few of customers. Full details here: https://blogs.oracle.com/peopletools/entry/important_peopletools_requirements_for_peoplesoft

Cloud Outage Audit for 2014

Tue, 2014-12-30 08:00

Via Chris Kanaracus we heard of the Cloud Outage Audit for 2014 which compares the reliability figures for all of the major cloud vendors. One of the areas that Cedar specialises in is moving PeopleSoft to the Cloud so we pay particular attention when reports like this are released. It’s an interesting read (the full report can be found here) however here are the results:

 

2

David Linthicum, SVP Cloud Technology Partners pulls no punches:

Discounting the low-end public clouds, uptime has been much improved, with one major exception. Even though [public cloud providers] are expanding quickly, they seem to be smarter at operating their business, with the possible exception of Microsoft, which has made some dumb mistakes.

The underlying theme seems to be that some of the newer providers are encountering issues as they start to scale which Amazon has already been through (as they had a ~5 year headstart on many of their competitors).

The downtime figures are sourced from CloudHarmony, an independent, third-party company that conducts monitoring of cloud vendor uptime.


The UKOUG Apps14 Conference

Mon, 2014-12-15 08:00

Cedar had a strong presence at the recent Apps14 conference in the ACC, Liverpool. It is supposed to be the largest applications conference in Europe with over 800 attendees, and we were particularly interested in the PeopleSoft and Apps Innovation streams.

This year we’d decided not to have a stand (as the conference is mixed with the other applications and the database/tech community, it means the exhibition stands are much more expensive than at the PeopleSoft-only Roadshow earlier in the year, while the attendance of the PeopleSoft community is much lower) but we did support the conference with four of our team and three speaking slots.

It was nice to see a different city, although I’m pleased to see that it’s back in Birmingham next year as that is a lot more central for everyone. This is the view from my hotel room out over Albert Docks (the ACC is out to the left).

01 - Sunrise at Apps14

In terms of our speakers, our GP guru Alex spoke about Global Payroll upgrades to v9.2 (as Cedar has helped several clients either complete their move to v9.2 or with an upgrade in progress at the moment):

02 - Alex and Global Payroll 9.2

Several of us also took part in the Oracle Usability Feedback session. We have <ahem> quite strong opinions on how a UX should be so it was really interesting to be part of the process. We can’t talk about the product that was being tested, but it looks really nice. I can’t wait until it hits GA. Here’s a picture of Simon giving it a thorough test with the lovely Rhonda (the screen has been intentionally blurred):

03 - Simon on UX and Usability testing

Cedar’s tech guru Neville also spoke on a couple of topics. He covered PeopleSoft Selective Adoption / PeopleSoft Update Manager in one session (joint with Hays, who are using some of our upgraders at the moment on their massive upgrade – HCM, Fins, CRM, Portal, ELM, all at the same time!). He also spoke about Oracle Secure Enterprise Search (joint with Allen & Overy, who we helped to upgrade to 9.2 earlier in the year).

04 - Neville and PeopleSoft Selective Adoption

The evening in between the two PeopleSoft days was pretty fun too. It started off with a familiar looking (for Liverpool, especially) band called ‘The Cheatles’ (pic below) and then many from the PeopleSoft community sat down for ‘off the record’ chat over a decent meal.

05 - The Cheatles


The Hidden Benefit of PeopleSoft Selective Adoption

Fri, 2014-12-12 07:00

There has been a lot of talk over the last couple of weeks about PeopleSoft Selective Adoption, the recently-coined term for the PeopleSoft Update Manager delivery model. Much of this has been on the direct benefits to the customer, which is how it should be. Greg Parikh has linked to some of the posts on LinkedIn.

While discussing this with a colleague at the recent Apps14 conference we noticed that there is another implication that I’ve not seen anyone else call out yet. Although at first glance it seems an immediate advantage to Oracle it’s not difficult to see how the customer is also going to reap significant rewards.

Getting everyone onto 9.2, and then delivering innovation on that version means that PeopleSoft development can operate on a single codeline. Currently, a legislative update will have to be coded and applied for all supported releases (and each version might require the update to be different, depending upon the underlying pages), meaning a lot of extra complication and repeat work. A Global Payroll update might need to be created for v9.2, v9.1 and v9.0, for instance, which is a significant burden.

Once updates are only being created on the v9.2 codeline then they only have to be done once, saving development staff time (and support staff a lot of troubleshooting time also) and thereby freeing them up to concentrate much more time on delivering extra value to the customers in the way of faster updates and more innovative new functionality. This can only be a big plus in the long-run.


Cedar’s new website is live – get ready for the blog!

Tue, 2014-12-09 08:00

I’m really pleased that Cedar have got our new website live – just in time for UKOUG Apps 14. website As you would expect it highlights the services that Cedar provides – both Oracle Cloud (Fusion and Taleo) and obviously PeopleSoft implementation, hosting and support. It contains details of our people and locations (we’ve offices in Kings Cross, London, plus India, Switzerland and Australia).  It also contains case studies of some of the project successes that we’ve had, and some of the nice things that clients have said about us. One of the things I’m most excited about is the blog. Make sure you add it to your feed reader as we’re going to be sharing some good content there from all of the practices within our company (plus the occasional post of us doing fun things!).

The new website can be found here: http://www.cedarconsulting.co.uk/


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
)
)