Blogs

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.

Read a File Using File Layout

A sample code to read a file using file layouts and insert data read into a record.

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &FILE_DIRECTORY, &FileName;

&FileName = "MY_FILE_NAME.txt";
&FILE_DIRTORY = "/MYDIRECTORY/"
/*open file for reading*/
&MYFILE = GetFile(&FILE_DIRECTORY | &FileName, "R", %FilePath_Absolute);
/*create record object*/
&REC = CreateRecord(Record.MY_RECORD);
&ARRAY = CreateArrayRept("", 0);
/*check if file is open*/
If &MYFILE.IsOpen Then

The Milton Show - Singing The Man Song!

This is my first "fun" post on the site. I've been thinking about this and decided that too much technical stuff just hurts sometimes and you all techies out there need a break and a laugh! So here is a little movie that I found to be funny.

Email Users Under a Specific Role Name

Here is a quick Application Engine section that looks up user's email addresses under a certain ROLE and sends an email notification regarding, well, what ever you'd like.

send email for a specific role

First, you would need a "Do Select" step to loop through the email addresses (if more than one) and call the PeopleCode step below it to send the email. Your Do Select should have the following SQL:

-- you need to store the email address for the peoplecode step to use and thus the %Select(EMAILID).

Dynamic View and Drop Down Menu

This was kind of interesting to figure out. What I was going after is the following. I had a drop down box on a page that shows some Business Units. The values however need to be dynamic and change depending on the user viewing/accessing the page.

For example, user1 should only see 52024, 12456 but user2 should be allowed to see 52024, 12456, 45648, and 10235.

To accomplish this I've created a table to store values for each user (lets call it TABLE_1). TABLE_1 has 2 fields (oprid, and business_unit). So now, we have 2 users on the table along with their associated business units.

The 2nd step is to create the dynamic view to pull business units specific to each user. The view will only have one field and that is the business_unit field.

record view

Save as File Using Visual Basic Excel Macro

I have written this macro a while back and thought it would be of some benefit for some of you.
I've created a button on an excel sheet that when clicked will fire an excel macro to open the well familiar windows "Browse Save as" box and allow the user to save the file to his/her desired location.
The macro limits the "Save as type" to a .txt file format.

save as macro example

Here is the code:

Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

Check for User Role

A very handy PeopleCode function to check for a user role(s) and perform specific actions depending on the value returned.

Function RoleToMatch(&RoleToMatch) Returns boolean;
   
   &bFound = False;
/*Returns an array containing all roles associated to the current user*/
   &aRoles = %Roles;
/*Loop through the array*/
   For &I = 1 To &aRoles.Len
      &RoleName = &aRoles.Shift();
      If Upper(LTrim(RTrim(&RoleName))) = Upper(LTrim(RTrim(&RoleToMatch))) Then
         &bFound = True;
         Break;
      End-If;
   End-For;
   
   Return &bFound;
   

SQL to Generate a Sequence Number

Generating a sequence number isn't that hard. All what you need to type is the following:

CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.

SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?

SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;

Sequence created.

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         1

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         4

SQL DECODE to Order Months

The SQL below will order months from Jan to Dec (01 to 12).

SELECT short_month AS short,
  long_month AS long
FROM month_tbl
ORDER BY decode(UPPER(short_month),
  'JAN',   '01',  
  'FEB',   '02',  
  'MAR',   '03',  
  'APR',   '04',  
  'MAY',   '05',  
  'JUN',   '06',  
  'JUL',   '07',  
  'AUG',   '08',  
  'SEP',   '09',  
  'OCT',   '10',  
  'NOV',   '11',  
  'DEC',   '12');

short       long
JAN     JANUARY
FEB     FEBRUARY
MAR     MARCH
APR     APRIL
MAY     MAY
JUN     JUNE
JUL     JULY
AUG     AUGUST
SEP     SEPTEMBER
OCT     OCTOBER
NOV     NOVEMBER

SQL to Show Database Information

Here is a quick SQL snippet to show some Database information. I'm only pulling Database name, date created, database owner Id and database long name.

SELECT A.NAME,
  B.DBNAME,
  A.CREATED,
  B.OWNERID,
  C.LONGNAME
FROM V$DATABASE A,
  PS.PSDBOWNER B,
  PSOPTIONS C;