Vendor Pending Transactions

A quick function that will allow you to verify if a specific vendor has any pending transaction(s).

Function vendor_pnding_transactions();

      SQLExec("select 'X' from ps_voucher a, ps_pymnt_vchr_xref b where a.voucher_id = b.voucher_id and a.business_unit = b.business_unit and a.vendor_id = :1 and a.entry_status in ('P', 'R')and a.close_status = 'O' and b.pymnt_selct_status in ('N', 'D', 'R')", &VENDOR, &EXISTS);
      If All(&EXISTS) Then
         Error Messagebox(0,"",0,0,("Vendor has pending transaction(s).");

Send Emails from PeopleCode (SendMail Function)

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

Local number &MAIL_FLAGS;

&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";

Pass Dynamic Value to a Grid Label

A grid label is the blue (normally) colored grid header that you see on PeopleSoft pages. The grid text label can be easily edited using the Grid Properties in app designer. Once you have the grid properties box open, click the Label tab > Properties and there you can see the Label Text area. You can choose between a Static Text type or Message Catalog. I would almost always go with Message Catalog type as this could be easily updated online.

Send User to a Portal Folder

Sometimes you would want to give users the option to click a button on the page and send them back to the Portal Folder they came from, or may be couple folders back! Here is how you go about doing that.

You can actually use the PeopleSoft delivered function NavPageURL to accomplish this! So, lets get started!

On your page, go ahead and add a button. Place the following PeopleCode at the Component Record Field level under FieldChange event.

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

Try and Catch Built-in Function

This is a simple example on when and how to use the Try and Catch built-in function. The code below will fetch a field value from a scroll area on a page and delete the row depending on the value. If the value is >= 0 or if it is a "charachter" value, then go ahead and mark the row for deletion.

See code comments to get a better idea of what the code is doing.

Local number &someValue;
Local string &strSomeValue;
Local Rowset &RS;

/*fetch result set;*/
&RS = GetLevel0()(1).GetRowset(Scroll.scroll_table);

%Join Meta-SQL Function

I’m all about finding ways to type less and less code. If there is a way to shrink 4 lines of code down to 1 line, I'm all for it.

Here is a %join meta-sql that will do just that. Syntax and description from PeopleBooks are as follow:

%Join({COMMON_KEYS | COMMON_FIELDS}, join_recname  
[ correlation_id1], to_recname [ correlation_id2]  
[, override_field_list])

/*Use the %Join meta-SQL construct to dynamically build a Where clause joining one table to another. At runtime, the entire construct is replaced with a character string. */

Meta-SQL Reference

This section discusses meta-SQL elements that I have used and found useful.

Built-in Functions

PeopleCode built-in functions.


Some snippets are just syntax, and others are examples, functions and so on. You can submit your own page or review and enhance ones already submitted by using the comment form at the end of each post.

HideAllRows Hides Grid on a Page

I have been used to turn to HideScroll and UnHideScroll functions to hide/unhide grid on a page. These two functions however, remain for backward compatibility only.

The functions have been replaced by HideAllRows and ShowAllRows methods. The methods are equivalent to a loop setting the visible propertly of each row of the rowset to false.
For the code to work though, you need to place your peoplecode in a parent rowset and execute it against a child rowset.