Monday, December 19, 2011

HTML based notifications

PeopleSoft provides functions and procedures to send email notifications. Most of the times we send simple text messages and attach reports, generally in PDF format to these notifications. The following post, explores the possibility of generating a report via XMLP and then sending a notification to an email client like MS Outlook in HTML format. The distribution type of "email" that is natively available via XMLP just doesn't cut it. Its very tedious to change the subject/body of the notification and then the report is sent as an attachment and not the body the notification.

For this POC, I am using a HR 9.1 PeopleSoft system running on PT 8.51.10 and my email client is MS Outlook 2007. The exchange server is running MS Exchange 2003. I am executing the following code via an AE program. Some changes needed if using it via an online trigger like FieldChange.
I will be using the SendMail function instead of PeopleSoft MultiChannel Framework mail classes. To do the same via MCF mail classes is another post, some other day.

/* this section details the steps to create the report in HTM format and store it in a folder location */
&REPORTDEFN.OutDestination = &dir | "\"; /* This will create the output under the temp directory used by the process scheduler */
&REPORTDEFN.ProcessReport(&template, "", %Date, "HTM"); /* generating the report in HTM format */

/* the following section, sets up the parameters of the SendMail function and sends the notification */
&EmailSQL = CreateSQL("select EMAIL_ADDR from PS_EMAIL_ADDRESSES where EMPLID = :1 and PREF_EMAIL_FLAG = 'Y'", &EmplID);
If All(&email_addr) Then
      &MAIL_FLAGS = 0;
      &MAIL_TO = &email_addr;
      &MAIL_CC = "";
      &MAIL_BCC = "";
      &MAIL_SUBJECT = "PeopleSoft Report";
/* here I am reading the htm report file in UTF-8 mode and then use that to populate the "body" variable */
      &fullfile = "";
      &reportfilepath = &dir | "\N_REPORT.htm";
      &html_file = GetFile(&reportfilepath, "R", "UTF-8", %FilePath_Absolute);
      While &html_file.ReadLine(&Text)
         &fullfile = &fullfile | &Text;
      &MAIL_TEXT = &fullfile;

      /* this will takre care of only one image */
      &imgpath = &dir | "\*.png";
      &FileList = FindFiles(&imgpath, %FilePath_Absolute);
      &filepath = &FileList.Shift();
      &MAIL_FILES = &filepath;
      &pos = Find("xdo", &filepath);
      &MAIL_TITLES = Substring(&filepath, &pos, Len(&filepath) - &pos + 1);
      &CONTTYPE = "Content-type: text/html; charset=utf8";
      &MAIL_SENDER = "";
      If Not (&RET = 0) Then
         Warning ("Return status from mail = " | &RET);
      Warning ("No Email Addresses found for " | &EmplID);

Thats it. It produces a nice and chic looking HTML notification.

Tuesday, December 13, 2011

XMLP Output Options

Following are the different ways to display a XMLP report, via peoplecode.

/* initialize */
&reportdefn = create PSXP_RTPDEFNMANAGER:ReportDefn(&report);
&template = &reportdefn.GetDefaultTemplateID();

/* this will create the PDF report output under c:\temp\ */
/* useful when generating report via an AE program */
&reportdefn.OutDestination = "c:\temp\";

/* without setting &reportdefn.OutDestination as mentioned above the PDF report will be written to the report respository and available via the Report Manager */

/* this will open the output in a new browser window. In this case code is executed on the appserver as against process scheduler in the above two scenarios */

Based on the above scenarios here is cool trick to show the report under process monitor (view/log trace) instead of the report manager.
&reportdefn.OutDestination = &dir | "\";

Monday, November 28, 2011

Embedded Help

PeopleTools 8.51.x provides a feature called as Embedded Help for scroll areas, grids, group boxes etc. Help icons now appear embedded in the headers of group boxes, scroll areas, and grids. Users click the Help icon on the page control to access pop-up Help messages that are stored in the Message Catalog. Embedded Help enables application users to view more granular, feature-specific help text that you create to be contextual to the page control or group of controls. The addition of embedded Help enables you quickly to add instructions, anecdotal observations from power users, reminders, or any type of message that supports your business processes. The addition of this type of Help can serve to keep users engaged in the task and to increase productivity.

Here is how it works.

%PBCTXT() - translates to the Help URL that is configured in the system.
Other commands provided by PeopleSoft are %PBURLTBL and %PBLNK. Refer to Peoplebooks for more information on these commands.

After entering a message, you associate the message to a page control. To associate help messages to scroll areas and grids:
  1. Open a page definition.
  2. Double-click the scroll area or grid to access the properties dialog box.
  3. Access the Label tab.
  4. In the Embedded Help Popup section, select the Message Catalog Popup option. 
  5. Enter the Message Set/Number values of the message that you want to appear in the embedded help pop-up page.
    The message should appear in the Text field.
  6. Verify that the message in the Text field is correct.
  7. Click the OK button.
  8. Save the page definition.
  9. In the browser, access the page and test the embedded help.

Wednesday, November 23, 2011

Drill Down Queries

PS Query in PT 8.51 now supports a new expression type called as Drilling URL. This expression type allows a user to drill down on values displayed in the result. Drill down can be done to a child query, external URL, component or attachment.
Following steps illustrate the drilling url feature using parent - child queries.

  1. Create two queries, a parent query (MENU) and a child query (PANEL), so as to speak. My parent query, queries the PSMENUDEFN and PSMENUITEM tables to list the menuname, component etc and then child query lists the pages in a component (from PSPNLGROUP).
  2. I will be using the drilling url expression and join Panelgroup between the two queries.
  3. My child query (PANEL) takes a prompt value for the field PNLGRPNAME
  4. In my parent query (MENU) I add a new expression of the type Drilling URL and choose the "Query URL" link, which takes me to the Query URL Widget
  5. Specify the child query name which is PANEL, and then assign the prompt key, which is
  6. Under "Map URL to Query Columns" grid I selected the PNLGRPNAME column as I want this column to be hyperlinked.
  7. The expression text will look like below.

Now save and run the parent query and the results will be displayed like so.

 Clicking on the component hyperlink will open the child query results like so.

Drilling URLs are supported only in RTF XMLP templates.

To use a drilling URL in a XML Publisher report:
  1. Create the query with the drilling URL defined as a field. When you run the query the URL to the child query should be displayed in the results like so. 
    2. Create an RTF template. In the RTF template map the field that contain drilling URL.
  • Highlight the field where you want to place the drilling URL.
  • Select Insert (from the Word menu), Hyperlink or use Ctrl+K.
  • In the Insert Hyperlink dialog box, enter the URL link in Address field.
    Each URL link should be defined as {URL_FIELD}, where URL_FIELD is the unique field name for the expression that contains a specific drilling URL.

  • Use the Target Frame push button to select how this URL link will be opened, either in the same window or in the new window
    Note. The URL value does not need to be added to the report, as long as the unique field name (data file that contains the URL) is mapped to a field in the report.

Tuesday, November 22, 2011

Native sFTP transmission in PeopleSoft

Running Peopletools 8.51.x. A lot has been mentioned about sFTP support in PT 8.51, so today decided to give that a shot. For the POC I am trying to send a extract ascii text file (developed using XMLP eTEXT) via sFTP to a target location.

  1. Setup URL Definition.
    1. My URL has the form sftp://servername/destination_directory. Did not specify the port number, which generally is 22.
    2. Setup URL properties. In my case AUTHTYPE is password, as I am using a basic userid/password type of authentication. Other types like using private/public keys is also available, but I haven't tried that yet. (I did try it here)
    3. I encrypted the password using the password encryption option available on this page.
      1. Note: Though the password is encrypted online, the actual value is stored in a plain text field in the db, so can be queried using a query tool (PS_PT_URL_PROPS, is the name of the table where the properties are stored)
  2. Now test this via the PeopleTools Test Utility page. Once you this working proceed to the next step.
  3. Develop an AE program and add a peoplecode step.
  4. The following code will do the trick
 &returncode = PutAttachment(URL.MY_URL, &filename, &fullpathtofile);

where MY_URL is the URL definition created above
&filename is the filename as in test.txt
and &fullpathtofile is the complete path as in C:\temp\test.txt

That's all, works like a charm, so no more 3rd party products.