Dynamic Views as Prompt Tables

Dynamic Views are an excellent object type provided by Peoplesoft - a pseudo view that can take any shape! They are excellent candidates where the SQL condition of a view changes dynamically according and are not even a database object.
But as many might already know, one has to be extremely careful while using dynamic views in prompts. There are two major limitations I have come across when you use a DV as a prompt:

1. If the SQL of the dynamic view contains joins and if the search fields are used in the join.
2. If the SQL of the dynamic view contains joins.

What causes the failure is that the component processor takes the search fields from the prompt and appends to the SQL statement like 'and = '. Now, unfortunately the processor does not do any aliasing which causes the common 'ambiguous column definition' SQL error.

To overcome this, either put the SQL in a view and query from the view or better, treat the SQL as an inline view and query from the inline view.
For example, if the initial SQL of the DV was something like:
SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN, for this to work in a dynamic view, it has to be called from an inline view like:
SELECT EMPLID FROM (SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN)

Peoplesoft-Microsoft Outlook Calendar integration

This was one piece of work I wanted to try out for a long time. With all the makeover that Enterprise Products are having with the introduction of Web2.0 features, SOA capabilities etc.- it is only natural that a synergy between desktop applications and ERPs are introduced. A lot of pioneering work has already gone into this area and I noticed couple of blogs and discussion forums mulling over the integration of Outlook Calendars with Peoplesoft. Considering the central role played by a mail client in scheduling meetings and events, it is an exciting possibility to send notifications from Peoplesoft as meeting invites to a mail client and extend the boundary of the application to the user's desktop.
Jim Marion has an excellent post here which gives the framework for the integration. It's a great place to start off from. (You can get some tip off from this forum also.)
These discussions throw up couple of options to implement this feature. Jim talks about providing the users a link to view the invite while another forum talked about creating an invite file and sending it as an attachment to the e-mail of the user. I piloted both these features and believe that in a real time environment, the latter option of sending the invite to the mail is a much better option than Jim's approach.
The implementation per se is pretty simple with very minimal coding, but I had two very important learnings during this pilot.
> The standard protocol used in the exchange of calendar invites is called iCalendar. This is an inter-operable protocol accepted by most calendar/scheduling systems. A working paper on the iCalendar framework can be found here. iCalendar files contain all the necessary information for the scheduling of an event and can be send across applications as a '.ics file. To test this, create a new appointment in MS Outlook calendar and save the appointment in the iCalendar format as shown below:


Open the iCalendar file is a text editor and you can see the details of the protocol. Thus, any invite that is sent out from Peoplesoft to a mail client has to be in the iCalendar format. This makes the task pretty clear - we need to build a mechanism that will generate a file in the iCalendar format with the correct details of the meeting.

> The content type of the file/response sent to the mail client should be "text/calendar". This means that before sending the iCalendar response to the mail client, the content type of the response has to be set to "text/calendar".

The fundamentals of my approach are heavily drawn from the blog of Jim Marion, yet I believe he did not give the actual implementation details which I intent to furnish here. The steps involved in this integration are as follows:

1. Create an HTML object with the iCalendar framework skeleton and assign the begin time, end time, summary and attendees tag to HTML bind variables.

2. Create a funtion or an iScript (create an iScript if you want the users to view the ics file from a link) which will call the iCalendar HTML object and pass the necessary bind variables using the GetHTMLText function. Jim has given details regarding the iScript and that can be directly implemented.

3. Jim's iScript is designed to show the ics file on the click of a link. But, I was turned on by a better approach of sending the ics file as an attachment to the e-mail of the correct users. To do this, bind the iCalendar HTML object with the correct parameters and save it as a .ics file and send that as an attachment. One point to be kept in mind for this approach is that the content type of the attachment has to be set as "text/calendar". If you are on tools 8.48 or above, use the delivered mail class functions which can be found in the App Package - PT_MCF_MAIL. The SetAttachmentContent or AddAttachment methods in this package has the capability to override the content-type of the attachment.

All discussions regarding this topic talks about only the above mentioned approaches, but I tried the same integration using the XML Link Registry utility. This can be useful when the users need to view the meeting invite through a link from an external (non-peoplesoft) site. I first came across the XML Link Registry utility while reading about running a PS Query from the desktop without signing into PIA. You can find details of that here. An XML Link Registry function is a mechanism whereby we can expose the methods in our web libraries (the iScript functions) to a non-peoplesoft system and the beauty of it is that the methods can be called from a URL. It's a pretty phenomenal utility to execute Peoplesoft functions from an external system if IB is not being used. Any iScript function can be exposed as an XML Link Registry function - for this, an XML Link Registry function need to be defined in: Peopletools --> Utilities --> Administration --> XML Link Registry.
I exposed the iScript created to trigger the invite as an XML Link Registry function and invoked the XML Link Registry service from the external URL.
So, there are multiple approaches for this implementation:
1. Send the ics file as an attachment to the users.
2. Within peoplesoft, show a link to the users from where they can access the invite.
3. Expose the invite as a link in a third party site and invoke the invite on click of the link.

Choose the best approach for the integration based on the current notification mechanism that is in place in your system.