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.