Sunday, August 17, 2008

PeopleSoft/Calendar Integration

Last week three different people asked me if it was possible to integrate PeopleSoft with a calendar management program (Microsoft Outlook, to be specific). Whether you are trying to create appointments for HRMS Enterprise Learning classes, ELM training classes, or eRecruiting interviews, the solution is the same. Since most calendar programs support the iCalendar (ics) format and since iCalendar files are text files, we can generate appointments from PeopleSoft IScripts and serve those as file downloads. As a starting point, we can copy the event example from the iCalendar RFC:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//hacksw/handcal//NONSGML v1.0//EN
BEGIN:VEVENT
DTSTART:19970714T170000Z
DTEND:19970715T035959Z
SUMMARY:Bastille Day Party
END:VEVENT
END:VCALENDAR

Modifying this a little, we can convert it to an HTML object with bind parameters:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//My Company//PeopleCode vCal 1.0//EN
BEGIN:VEVENT
DTSTART:%Bind(:1)
DTEND:%Bind(:2)
SUMMARY:%Bind(:3)
END:VEVENT
END:VCALENDAR

We can serve this text to a client browser using an IScript that looks something like:

Function IScript_GetICalendarEvent
Local DateTime &startTime;
Local DateTime &endTime;
Local DateTime &tempTime;
Local string &startTimeUTC;
Local string &endTimeUTC;
Local string &eventTitle;

REM ** TODO: Initialize date and title variables from database;

REM ** change time zone to your time zone;
&tempTime = DateTimeToTimeZone(&startTime, "PST", "UTC");
&startTimeUTC = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'");

&tempTime = DateTimeToTimeZone(&endTime, "PST", "UTC");
&endTimeUTC = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'");

%Response.SetContentType("text/calendar");
%Response.WriteLine(GetHTMLText(HTML.ICAL_EVT, &startTimeUTC, &endTimeUTC, &eventTitle);
End-Function;

All you have to do is fetch your event data from the database and provide your users with a means to access this IScript. If you want your users to be able to access this IScript from a workflow event, then modify your e-mail workflow template to include a link to this IScript. Likewise, if you want your users to be able to download a calendar event from a page, add a link for this IScript to that page. When creating a link to this IScript, be sure to include all the keys required to fetch the event's data from your database.

If you study the iCalendar RFC, you will notice that it also includes a specification for tasks. You could modify this example to add tasks for voucher due dates, etc. I'll leave the possibilities and implementation to your imagination.

This example is only meant to be a starting point. Since iCalendar support has many potential uses in PeopleSoft, I would create a reusable App Class API for rendering iCalendar (ics) files.

44 comments:

Angela Yaohua Chen said...

Jim, do you know if it is possible from iScript in PS Enterprise Portal to access Component interface of another PS module for example HRMS? Given that SSO is set up from PS Enterprise Portal to direct access HRMS portal. I am wondering if we could just construct url to point to the component interface in HRMS from EP iScript. Thanks,

-Angela

Jim Marion said...

@Angela, that is an interesting question. If you are asking if it is possible to create an instance of an HRMS component interface from PeopleCode written in the Enterprise Portal database, then, no. Unfortunately, an IScript in EP cannot access a component interface in HRMS. This is because the meta-data required to create the component interface and the data required to populate the component interface exist in the HRMS database, not in the EP database.

If you are asking if an HRMS CI run in an IScript created in HRMS can be accessed by a user logged into your Enterprise Portal, then, yes, as long as you have SSO setup between EP and HRMS.

Are you trying to combine data from multiple DB's into a single UI or are you trying give access for HRMS components through EP? Both can be done. To combine UI's, you can use Ajax served from IScripts. Giving access can be done through the Portal Registry, etc.

Given this information, please write back and let me know what you had in mind.

Angela Yaohua Chen said...

Jim, Thanks for the reply! We are exploring ways for Enterprise Portal to serve up the information from other modules and also compare the approaches for developers to use if they would like to enhance the user interface or mashup with external information.

There are a couple of routes I am exploring. One way is: EP pagelet form post <-> iScript <-> BPELUtil <-> HRMS CI-based WS wrapped in BPEL. With this approach, we have an ability to mash up information in BPEL layer.

Another way we are wondering about is that if there is an entry point using the CREF for the remote component/CI if we configured SSO between instances. EP pagelet form post <-> iScript <-> access remote CI in HRMS via url? You mentioned using Ajax served from iScripts. I guess what you mean is to have EP pagelet using Ajax to post XML to iScript in the target system, which then can access the local CI object. That might be an approach we could take instead of what we were thinking.

What do you think? Thanks,

-Angela

Jim Marion said...

@Angela, all of your approaches sound feasible. When mashing up external data, if that data is in a different domain, and is not under your control, then using BPELUtil may be a good way to validate that external data prior to combining it with your own data. If the data is under your control (like your HRMS system), and if your HRMS system is in the same domain as your Enterprise Portal, then you can perform a client side mashup using Ajax.

When you configure SSO, you will setup the HRMS node in EP and the EP node in HRMS. When you setup these nodes, you will specify the content and portal URL's for each node. After configuring the nodes, you can use the function GenerateScriptContentURL in your Enterprise Portal's Pagelet PeopleCode to get the URL for an HRMS IScript. You don't need a CREF for this, you just need a permission list in HRMS that grants you access to the HRMS IScript and a Pagelet in EP that can consume the HRMS XML data.

You want your homepage to load quickly. One way to speed up homepage loading is to create Pagelets in EP that contain JavaScript and display logic (JavaScript, HTML, and CSS), but no application data. Your pagelets can retrieve application data from your content providers (HRMS) asynchronously using Ajax. For consistency, it would make sense to use the same approach for both PeopleSoft and non-PeopleSoft data. However, since the BPELUtil method will require additional processing, for performance reasons, you may want to use the EP pagelet/HRMS Ajax IScript method for PeopleSoft data and use the BPELUtil method for non-PeopleSoft data.

That was for displaying data. Now, saving data... When using Ajax, you can choose whether to send your Ajax request synchronously (sync) or asynchronously (async). Likewise, depending on your target (IScript, BPELUtil, or Integration Broker), you can process the data synchronously or asynchronously. So, it is possible to send an async Ajax request to a sync IScript/Service, an async request to an async service, a sync request to a sync IScript/Service, or a sync request to an async service. An Ajax Async request will not block the UI whereas a sync Ajax request will block the UI. Usually, you send Ajax requests asynchronously. IScripts always process data synchronously, meaning, they don't return a response until the data is processed. BPEL and Integration Broker (IB), however, can process data synchronously or asynchronously. This means that you can actually send an async Ajax request to BPEL or IB and get a response before the data is processed. If you don't need to display feedback to the user, then this is a great option because it frees up the UI and Ajax sockets for other work. If you need to validate input, however, then a sync service or an IScript is a better alternative. Using a sync service or an IScript, you can process the posted data through a CI and report back invalid prompt values, etc, allowing the user to fix the data before continuing. In instances where you need to process data synchronously, then an IScript may be a better solution than BPEL or IB.

Now, what about security? If you are using an ESB like BPEL or IB, do you have a method for validating the user's credentials? If your BPEL end point is IB (end processor is PeopleCode), then you can send the PS_TOKEN cookie with your post request and use the PeopleCode SwitchUser function to authenticate the user. If you send a request directly to IB from your user's browser (Ajax), then IB may authenticate the user automatically based on the PS token cookie. You will want to test this. Be very careful about passing around PS_TOKEN cookies. You don't want to send them to an external system, store them, or make them visible in an unsecure log file. A major advantage of IScripts is that they run as the user that logged into EP. This means standard PeopleSoft permission lists control which IScripts and CI's a user can access. Likewise, views containing row level security will only display data accessible to the logged in user. You don't need to do anything to make this work. It is just the way PeopleSoft works. BPEL and IB, however, will require additional authentication because they don't use the credentials passed to them by the browser (IB might use the PS_TOKEN cookie, I can't remember).

A word of caution: Never trust your users! When processing or displaying data, don't use EMPLID, PERSON_ID, OPRID, etc as request parameters. Instead, use %OperatorID in your PeopleCode to determine the user and then query your system's meta-data to get EMPLID, PERSON_ID, etc. Otherwise, you may inadvertently make a way for users to view and update data they shouldn't be able to access.

Angela Yaohua Chen said...

Jim, in considering what approach to take, we also have other considerations in mind. Having iScript in target system adds another modification that needs to be migrated with production, in addition to having to be synchronized with EP. We are an institution with limited resources and in house PeopleSoft knowledge. Given that reason, we might want to avoid using iScript in the target systems entirely. That leaves us to favor the EP pagelet -> post -> iScript to BPELUtil for both PeopleSoft and non-PeopleSoft data.

For security, we are looking to leveage Oracle Web Service Manager to centralize the access control, but that is yet to be flushed out...

-Angela

Andrew said...

Hi Jim,

Do you know if it is possible to use the content type (text/calendar) within the Sendmail function, or in the PT_MCF_MAIL:MCFOutboundEmail class?

We are going to use this and I can see how I can just generate the text file and include it as an (.ics) attachment. Then when the users get the email they can open the attachment. But am curious if it can be pushed into the email body directly. I did a fair bit of experimenting but wasn't able to get it to work.

As an aside do you know if SendMail is using an exe or a java class?

Thanks,

Andrew

Jim Marion said...

@Andrew, As far as I know, the only way to embed an attachment is to write the attachment to a file and then include it using the attachment parameters of the SendMail function. This still embeds the attachment, but requires an additional step and additional IO.

I don't know the inner details of the e-mail implementations.

Rather than attach iCal files to e-mails, I've found it easier to include a link to an IScript and have the IScript serve the iCal file. Of course, this requires the recipient to have access to the PeopleSoft server.

avi said...

Jim, Is it possible to use send method as provided in new Mail APIs, came with PeopleSoft 9. My expertise area is Correspondence Management. I found the similar blog at www.itwisesolutions.com

Jim Marion said...

@Avi, I assume you are referring to the MCFOutboundEmail send method. Yes, you can use the MCF classes. These are documented in the PeopleCode API PeopleBook.

Jiju Vengal said...

Hi Jim,
Your blog is a mine of PS information! Well, with the help of your post of iCalendar I had tried out implementing this integration successfully. As a deviation I tried using the XML Link Registry function to serve the content to an external site. You can find the details
here

Jim Marion said...

@JijuVengal thank you for the compliments and your additional documentation. I read your post. Keep up the good work! By the way, I have the iCalendar stuff in an AppClass framework and will be publishing it soon.

Unknown said...

Jim

where do i find the microsoft add-in for 2007 outlook calendar integration? the desktop integration peoplebooks says to install it..is it available in metalink?

this is for PS 9.1 TAM - interview scheduling..

can you please help?

thanks

Jim Marion said...

@Psft, I am not familiar with that add-in. I suggest you open a case with Oracle Support. They will either provide you with the add-in or enter a ticket for information development to fix PeopleBooks.

Neeraj Kholiya said...

Hi Jim

I was going through Microsoft OAF application ( I am from PS Finance background) and there I saw a code to integrate Outlook ( through C#) with Peoplesoft . In newer version this is included in HR 9.1 for some modules .. That was one kewl utility too..

Sumit Agarwal said...

Hi Jim,
My integration of calendar is working fine in Poplesoft. I am having two issues.
1. when ever user click on Add to calendar button. User need to run ActivX from browser and then page come back and again need to click Add to calendar button.I want to get rid of running ActivX

2. When user open iCal file page hangs and need user to refresh from menu.

Let me know if you have any idea?. I'll really apprecaite your help.

Thanks
Sumit

Sumit Agarwal said...

Hi Jim,

I am using add to calendar functionality it is working fine but recently when user click on Add to outlook link file download but give message. The file "PSEvent.ics" is not a valid Internet Calendar file. I'll really appreciate if you can comment on it or have any idea how to resolve it.

Thanks
Sumit

Jim Marion said...

@Sumit, next time you click the link, save the PSEvent.ics file instead of opening it. Then open it with Notepad so you can see the contents. This will allow you to find the error in the file.

If you have your browser set to automatically open your calendar, another option besides saving is to launch fiddler. The PSEvent.ics file response will be logged in fiddler for you to view there instead.

Anonymous said...

Jim, this information has been very helpful. I was able to create a google calendar event using Peoplecode. I have two questions. 1. Can you force the calendar event to auto populate on the calendar so that the user does not have to add to the calendar from the e-mail? Is there any PeopleCode that can do this? 2. Is there a way using PeopleCode to remove the google calendar event?

Jim Marion said...

@Ron, the answer to each of your questions is Yes, but not as easy or generic. Probably the best generic approach to reading/writing to calendars is through CalDAV, an extension of WebDAV. Some "group ware" providers have API's for communicating with calendars, but CalDAV is probably the most generic.

Bijay Bhushan Singh said...

hi Jim,
really Your iscript calender code is helpfyll. i have a problem when i was calling iscript using url everything is getting populated whatever i passed in iac but only start date and end date is not getting populated when i open that file in outlook it showing current date and time so i am not able to set the meeting on that date.can u please tell me what i shld do if i open that iac in outlook it open passed bind date.

Jim Marion said...

@singh, save the icalendar file and open it with a text editor (notepad) and review the date fields. Make sure they have properly formatted values.

Bijay Bhushan Singh said...

thanku jim for reply.that problem got solved.now everything is working fine.but in my requirement mainly two active user are there in which one is admin who will create the appointment
and other is attendee so in that whenever admin will schedule any appointment that ll affect to the attendee calender without asking the permission accept or decline.because he does not have permission to decline.is there any method to populate event in attendee calender without his permission.
thanks in advance

Jim Marion said...

@singh, If you know the back-end calendar system, then, yes, you can write an integration for that calendar system. It is not proper to add events to someone's calendar without notifying that person, so even if you use a back end calendar integration, then be sure to send a notification to the user.

The way I would implement this is to send an e-mail notification (doesn't have to be workflow) with a link to the iScript containing the event. This gives the recipient control over the event.

Bijay Bhushan Singh said...

thanks for ur reply.i am doing integration with outlook.yes i will send notification to attendee but does not have rights to decline like example admin assign some class to some faculty.faculty ll get notification but he cant decline to take class so is it possible to do integration in outlook.
thanks in advance

Jim Marion said...

@singh, yes it is possible. Most Personal Information Management systems have a "back end" API that allows you to create events, contacts, etc.

Phani said...

Hi Jim,
we followed your blog to create the outlook calendar using the ICalender Script and it is working fine.Can you please let us know how to include that ICalendar as an attachemnt on clicking that it should open with out asking for login.
Thanks,
Phani

Jim Marion said...

@Phani, glad you have it working. To include it as an e-mail attachment, you would have to write the iCalendar contents to a file and then specify the file location when calling the SendMail function or the AddAttachment method of the MCFOutboundEmail class.

Denise said...

Jim,
Hopefully you are still responding to comments on this topic :) I have incorporated links into ELM for users to put a training activity into their Outlook calendar. The iscript is working fine, however, when the users click on the link it is opening a new IE tab and then they are asked to open the file. This new tab stays open after they open and save the calendar event. Is there anyway to supress it opening a new IE tab or window with the link?

Thanks!

Jim Marion said...

@Denise, I am glad you are using this solution. I think it is a very simple way to offer calendar integration. For your link, do you have the target attribute set? If so, that will open a new window. If you don't set the target, it should try to load in the current window, but since the response content type is registered to Outlook, it should force the content into Outlook (download/open), leaving you on your current transaction page. Other than that, I'm not sure what to suggest.

Have you tried connecting your iScript calendar download to a Related Action? You may still get a new window, but it seems like a good way to avoid modifying the delivered page.

Unknown said...

Jim can you tell me if I can use tools 849 and integration broker to connect to office365 imapi4 mail server? I see the office365 site uses port 993, for ssl, but don't see any mention of this port usage in peoplebooks for the mcf. I'm trying to connect using the mcf mail page and seeing that the web server isn't able to connect via the log file...Any ideas on how i can log the java on the gateway or the communication going out from that host? I'm kinda stuck as I'm not sure exactly how that connection is being made or getting any descent error info...great book btw..

Jim Marion said...

@Andy, thank you for the compliment. I do not have experience with Office365 directly. Are you using the MCF_GETMAIL node with the GETMAILTARGET connector? You can turn up the log level in your integrationGateway.properties file to see more debug and error information. Since you are communicating over a secure channel, it will be hard to use anything in the middle to review the content. I would use wireshark on the PeopleSoft server or some other network packet sniffer to see if you are connecting. If you have any proxy servers, make sure that they allow connections through that port and that your Integration Broker is configured to use the proxy server.

Unknown said...

Thank you for all the information. I don't understand how to put the pieces of this together. I have created the html and iscript and retrieved the fields I need. I have very little experience with iscripts. I don't understand how to create the link in the email that allows the user to create the calendar entry. Can you provide any guidance?

Jim Marion said...

@Margaret, With a web browser, are you able to test the iScript URL for the iCalendar entry? If so, the next step is getting it into a workflow e-mail.

Unknown said...

I am so sorry to be so late getting back to you. I feel that I'm very close, but am getting an error when trying to generate the url.

In the PeopleCode that generates the email, I have this code:

&URLString = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_PT_NAV, Field.ISCRIPT1, "FieldFormula", "MKGetICalendarEvent", &startTime, &endTime, &eventTitle);

The calendar event iscript is copied from your example, GetICalendarEvent.

&startTime and &endTime are datetime fields, &eventTitle is the course title. All fields appear to be populated properly. The error is:

Invalid parameter 7 for function GenerateScriptContentURL. (2,116) COURSE_ENROLLMENT1.GBL.Workflow PCPC:9255 Statement:85

I'm not sure what I'm doing incorrectly here, any guidance would be greatly appreciated.

Unknown said...

I was able to create the URL and add it to an email, so I'm getting a little closer. When I copy the link into a browser, I get this message.

Missing function IScript_MKGetICalendarEvent&start_Time=2016-03-21-13 in PeopleCode program ???.???-Other. (180,127)

I think I must be something very simple that I'm missing. This is the statement to build the URL:

&URLString = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_PT_NAV, Field.ISCRIPT1, "FieldFormula", "IScript_MKGetICalendarEvent");

I've tried a couple of variations, but always get the same error.

As always, I really appreciate your guidance.

Jim Marion said...

@Margaret, you are getting very close. It looks like the problem is the delimiter between the iScript function name and the parameter. It should be a question mark (?), not an ampersand (&). The ampersand goes between parameters, but the question mark tells the server where the function name stops and the parameters start.

Unknown said...

Hi Jim,
We have implemented calendar outlook integration for elm classes.

We were using email type = HTML and using HTML tags to make the emails more presentable. Once after the integration, since type is changed to Ics our HTML templates looks weird.

Any solution/ suggestion

Thanks,
Rashmi.

Jim Marion said...

@Rashmi, adding ICS integration through iScripts should have no impact on your HTML e-mails, except for adding a link to download the ICS file.

Unknown said...

Because of an upgrade, we did not implement this when I contacted you earlier, but now our users are wanting to do this. I am able to create calendar entries, but I am uncertain how to pass variables to an iScript. I am creating the URLstring in Workflow PeopleCode, where the variables are determined. How can I access those variables in the iScript? I've no experience with this. I haven't been able to find an example and would appreciate any help.

Jim Marion said...

@Margaret,

We pass variables to an iScript through the URL as query string parameters (the part after the question mark as key/value pairs). From the iScript, you can access these query string parameters using %Request.GetParameter.

How you populate the iScript's URL depends on where you are using the URL. E-mail notification templates use SQL queries for parameters. If this is part of an e-mail notification template, then I would generate the full URL in the query itself. That way the URL will be dynamic enough to change with dev, test, prod, etc. If I am using PeopleCode to display the URL on a page, then I would use GenerateScriptContentURL to create the URL.

John M said...

Hello Jim,

I've created an iScript to download a students calendar for the current semester in Campus Solutions. I cannot find a way to control the name of the file though. Currently the file is created with WEBLIB_NAME.FIELDNAME.FieldFormula.ics as the name. I'd like to name the file the students name.

Thanks for your awesome blog.

-John

Jim Marion said...

@John great job! Yes, you can change the name, and I recommend doing so. You can read more in this blog post: https://jjmpsj.blogspot.com/2009/01/exporting-attachments-part-2.html. Look for the content-disposition header.

Manish said...

Hi Jim,

I have copied the same code but I am calling from a push button.

However, the file being generated is .txt file and not an .ics file. How do I achieve it so tha file that is generated in the correct format?

&tempTime = DateTimeToTimeZone(&startTime, "PST", "UTC");
&startTimeUTC = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'");

&tempTime = DateTimeToTimeZone(&endTime, "PST", "UTC");
&endTimeUTC = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'");

%Response.SetContentType("text/calendar");
%Response.WriteLine(GetHTMLText(HTML.ICAL_EVT, %Date, %Date + 3, "Test11"));

Kindly help.

Regards,
Manish

Jim Marion said...

@Manish, the SetContentType("text/calendar") should do this, but another line that could be helpful is the content-disposition header. Take a look at https://blog.jsmpros.com/2009/01/exporting-attachments-part-2.html for an example.