Send Mail with Multiple Attachments using PT_MCF_MAIL

Using an Application Engine, I want to attach multiple attachments to an outgoing email.

Combine both for Record PeopleCode usage.

Setup Custom Page for File Upload

First, create a record.

From this point forward, we will refer to it as MYRECORD.

Next, create the page.

SQL - Extract Month, Day, or Year from Date Value

Happy Holidays!

SELECT 
extract(YEAR from SYSDATE) as Year, 
extract(MONTH from SYSDATE) as Month, 
extract(DAY from SYSDATE) as Day
FROM DUAL;

SQL - GRANT Access to Record

I want to grant SELECT, UPDATE, DELETE, or all of these to a record.

Fire up Data Mover:

1. Application Designer > Go > Data Mover
or
2. \Pt848\bin\client\winx86\psdmt.exe

SET LOG C:\TEMP\GRANTLOG2014.LOG;

GRANT ALL ON SYSADM.PS_MYRECORDNAME TO myusername;

SQL - Using OVER (PARTITION BY)

I want to list all phone numbers for a specific employee in one row.

Let's look at my progress with the OVER PARTITION command.

SELECT A.EMPLID, B.PHONE, ROW_NUMBER() OVER (ORDER BY A.EMPLID) as SEQ
FROM PS_PERSON A, PS_PERSONAL_PHONE B
WHERE B.EMPLID = A.EMPLID;

Results:

Now we add PARTITION BY EMPLID

DateTime Format

This is for SQL.

SELECT TO_CHAR(AUDIT_STAMP, 'yyyy-mm-dd hh24:mi:ss')
FROM PSAUDIT;

And this is using PeopleCode.

DateTimeToLocalizedString(%Date, "yyyyMMdd");

Find Portal Navigation Using Component Name

I want to find the navigation path using only a Component name.

Simple SQL

SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > '  || LEVEL2.PORTAL_LABEL || ' > '  ||  level3.PORTAL_LABEL PATH_TO_COMPONENT
FROM PSPRSMDEFN level3
, PSPRSMDEFN level2
, PSPRSMDEFN level1
, PSPRSMDEFN LEVEL0
WHERE level3.PORTAL_URI_SEG2 = "Your Component Name"
AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME
AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME
AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME
AND level3.PORTAL_NAME = level2.PORTAL_NAME
AND level2.PORTAL_NAME = level1.PORTAL_NAME
AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME;

The next one is actually better.

Application Package (Public vs Protected vs Private) Methods Part 2

From my previous post, the difference between public, protected and private methods were shown by calling them outside the application class.

Today, we will try extending the base class to another application class.
The new application class will be called Australian.

This is the code for our base class, Person.

Application Package (Public vs Protected vs Private) Methods Part 1

Today, we will check the difference between public, protected and private for Application Packages.

First, let's check this sample Application Package.

class Person
   method Person();
   method SayBarbecue();
   property string Property1;
protected
   method SayHello();
   property string Property2;
private
   method SayGoodbye();
   instance string &Property3;
end-class;

method Person
end-method;

method SayBarbecue
   MessageBox(0, "", 0, 0, "Barbecue!");
end-method;

method SayHello
   MessageBox(0, "", 0, 0, "Hello!");
end-method;

method SayGoodbye
   MessageBox(0, "", 0, 0, "Goodbye!");
end-method;

We have 3 methods.

Create a record without PS_

Check Record Type tab and enter the record name under "Non-Stand SQL Table Name"

Create Your Own Audit Record

So PSAUDIT is not cutting it and you need to log new, modified, or deleted rows of a specific record?

Then you need your own audit record.

First, identify your key fields.

Security Access Log For Any Component

aka How to trigger an SQLEXEC insert, update, delete in PostBuild.

First, create a function inside a derived/work record's FieldFormula.

Function InsertLog();
  
   Local Record &REC1 = CreateRecord(Record.MYRECORDNAME);
  
   For &i = 1 To &REC1.FieldCount
      Local Field &Field = &REC1.GetField(&i);
      &Field.SetDefault();
   End-For;
  
   &REC1.AUDIT_OPRID.Value = %OperatorId;
   &REC1.AUDIT_STAMP.Value = %Datetime;
   &REC1.MENUNAME.Value = %Menu;
   &REC1.PNLGRPNAME.Value = %Component;
   &REC1.PNLNAME.Value = %Page;
  
   SQLExec("BEGIN %Insert(:1); END;", &REC1);
  
End-Function;

Next, call the function in your component PostBuild.

Declare Function InsertLog PeopleCode MYDERIVEDRECORD.FIELD1 FieldFormula;
InsertUserLog();

What's the trick?
Adding BEGIN and END to your SQLEXEC statement will bypass the validation on PostBuild.

What's the catch?
It will only work on older versions of PeopleTools.

Loop Through Record Fields

I only need data in the record key fields, the rest can be defaulted.

So initially, I will do this.

Local record &MyRecord;
Local number &i;
&MyRecord = CreateRecord(Record.MYRECORDNAME); 

/* key fields */
&MyRecord.FIELD1.Value = &Var1;
&MyRecord.FIELD2.Value = &Var2;
&MyRecord.FIELD3.Value = &Var3; 

/* rest */
&MyRecord.FIELD4.SetDefault();
&MyRecord.FIELD5.SetDefault();
&MyRecord.FIELD6.SetDefault();
...
&MyRecord.FIELD9000.SetDefault();

It looks horrible.

Use this instead.

Alter Schema

No need to add SYSADM in front of every PS record.

ALTER SESSION SET CURRENT_SCHEMA = SYSADM;

Last used on Oracle SQL Developer Version 2.1.1.64.

SQL for Max Effective Dated Row from JOB table

SQL for max effective dated row from JOB table.

SELECT *
FROM PS_JOB J
WHERE J.EFFDT = (SELECT MAX (J_ED.EFFDT)
                 FROM PS_JOB J_ED
                 WHERE J_ED.EMPLID = J.EMPLID
                 AND J_ED.EMPL_RCD = J.EMPL_RCD
                 AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ = (SELECT MAX (J_ES.EFFSEQ)
                FROM PS_JOB J_ES
                WHERE J_ES.EMPLID = J.EMPLID
                AND J_ES.EMPL_RCD = J.EMPL_RCD
                AND J_ES.EFFDT = J.EFFDT)
ORDER BY J.EMPLID, J.EMPL_RCD, J.EFFDT, J.EFFSEQ;

If that returns multiple rows for a specific employee, that means he/she has additional assignments.
Use this SQL instead.