Lepa's blog

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;

FTP File from Windows to Unix Server

Here is how you can FTP a file from your windows client machine to a Unix server box.

Open the command line by going to Start > Run and type cmd.

You should see something link this:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\compshack>

Now, navigate to the location of the file you would like to FTP. Mine is located under c:\temp\
cd c:\temp

Lets type the ftp command:
ftp database_name

Check What Record Has Your Fields

This is another quick SQL snippet that I find useful. When would you use it?
Well, you forgot a certain record name, but you know that the record has EMPLID, NAME, EMPL_REC fields on it.
You can query the database to find out what records have the fields you are looking for.

SELECT RECNAME, COUNT(FIELDNAME)
FROM PSRECFIELDALL
WHERE FIELDNAME IN ('EMPLID', 'NAME', 'EMPL_RCD')
GROUP BY RECNAME
HAVING COUNT(FIELDNAME) = 3;