Author: digitaleagle

PeopleTools Bug

digitaleagle Application Packages, Bugs, Uncategorized Leave a comment

If you are coding an Application Package, and you get this error message:

PeopleTools Affirm Triggered
PSAFFIRM(!pItemFound) failed at e:\pt846-908-r1-retail\peopletools\src\inc\stringhash.h, line 157. See trace file. Press Cancel for debugger.

Here is the solution:

Basically, you have a property with the same name as one of your instance variables. For example:

class ExampleClass
method ExampleClass();
property String ExampleDescription get set;
private
instance String &ExampleDescription;
end-class;

To fix the problem you can change it to:

class ExampleClass
method ExampleClass();
property String ExampleDescription get set;
private
instance String &strExampleDescription;
end-class;

PeopleTools Reference: Common Meta-SQL

digitaleagle Meta-SQL, PeopleTools Reference, Uncategorized Leave a comment

If you were to learn just a select few Meta-SQL, these are the ones that I would recommend:
%SelectAll
%SelectAllByKey & %SelectAllByKeyEffdt
%Table
%DateIn & %DateOut
%Insert
%Update
%Delete

There are many others. Please refer to the PeopleTools PeopleBook for more information.

Update 5/7 — Fixed the links to point to the most resent version of PeopleTools

PeopleTools Reference: Meta-SQL %Delete

digitaleagle lete, Meta-SQL, PeopleTools Reference, Uncategorized Leave a comment

generates a delete statement using the values from a record object

This command will pull the record name, the key fields, and the values for those key fields to generate the delete statement. This will result in the statement deleting one row from the database since the statement includes all of the keys for the record.

Example

Local Record &recJob;
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = 0;
SqlExec(“%Delete(:1)”, &recJob);

PeopleTools Reference: Meta-SQL %Insert

digitaleagle %Insert, Meta-SQL, PeopleTools Reference, Uncategorized Leave a comment

generates an Insert statement to insert the data from a record object into the database.

Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write an insert statement: insert( … field list … ) values ( … options …).

Examples

Local Record &myRecord;
&myRecord.KEY1.value = “Key Value”;
&myRecord.FIELD1.value = “Field Value”;
SQLExec(“%Insert(:1)”, &myRecord);

PeopleTools Reference: Meta-SQL %SelectByKey

digitaleagle %SelectAll, %SelectAllByKey, %SelectAllByKeyEffdt, Meta-SQL, PeopleTools Reference, Uncategorized Leave a comment

generates a select statement based on the given record definition.

Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write a select statement: select … list of fields … from … table name … where … key field name … = … value for that field … and so on.

Related: %SelectByKeyEffdt

does the same thing except it adds the effective dating criteria to limit it to the most recent rows.

Examples

Local Record &recJob;
&recJob = CreateRecord(RECORD.JOB);
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = &effseq;
SqlExec(“%SelectByKey(:1)”, &recJob, &recJob);
WinMessage(“Job Code: ” | &recJob.JOBCODE.value);

Local Record &recNames, &recOutput;
&recNames = CreateRecord(RECORD.NAMES);
&recOutput = CreateRecord(RECORD.NAMES);
&recNames.EMPLID.value = &emplid;
&recNames.NAME_TYPE.value = “PRI”;
SqlExec(“%SelectByKeyEffdt(:1)”, &recNames, %Date, &recOutput);

PeopleTools Reference: Meta-SQL %SelectAll

digitaleagle %SelectAll, Meta-SQL, PeopleTools Reference, SQL, Uncategorized Leave a comment

Generates a select statement that selects all the fields from a chosen record.

Typically, when you use the %SelectAll, you would use a record object to store the results. That way you don’t have to know how many fields your result will return.

Examples

%SelectAll(JOB)

Local Record &myRecord;
&myRecord = CreateRecord(RECORD.MY_RECORD);
SQLExec(“%SelectAll(:1) WHERE MY_KEY = :2”, &myRecord, “My Key”, &myRecord);

Local Record &myInstallation;
&myRecord = CreateRecord(RECORD.INSTALLATION);
SQLExec(“%SelectAll(:1)”, &RECORD.INSTALLATION, &myInstallation);

PeopleTools Reference: Meta-SQL %Table

digitaleagle %Table, App Engine, Meta-SQL, PeopleTools Reference, SQL, Temp Tables, Uncategorized 2 Comments

%Table

Replaces with the actual table name of the given record.

Generally, this just means adding a “PS_” to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.

Two Uses:

  • Access a table with a reference or record object rather than embedding a table name in the SQL; PeopleTools will not index the table/record in a string literal.
  • Reference the Temporary table in an App Engine; %Table is the only way to reference the table because the table name is assign dynamically at run time.

Examples:

SELECT * FROM %Table(JOB) WHERE EMPLID = :1

SqlExec(“SELECT NAME FROM %Table(:1) WHERE EMPLID = :2”, Record.NAMES, &emplid, &name);

&sql = CreateSql(“SELECT * FROM %Table(:1) WHERE EMPLID = :2”, &MyRecord, &emplid);

INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE

PeopleTools Reference: Meta-SQL DateIn/DateOut

digitaleagle Meta-SQL, PeopleTools Reference, SQL, teIn, teOut, Uncategorized 2 Comments

DateIn

Description

Converts a date into the form the database uses.
Used for:

  • Where clauses
  • Insert/Update

Remember DateIn passes a date into the database.

DateOut

Description

Converts a date into the form that PeopleTools uses.
Used for:

  • Select clause

Examples

SELECT %DateOut(MAX(EFFDT))
FROM PS_JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND EFFDT

INSERT INTO PS_MY_RNCTL(OPRID, RUN_CONTROL_ID, AS_OF_DATE)
VALUES(:1, :2, %DateIn(:3))

UPDATE PS_MY_TABLE
SET MY_DATE = %DateIn(:1)
WHERE MY_DATE

PeopleTools Tip — Cloning a Component Interface

digitaleagle Component Interface, PeopleTools Tip, Uncategorized Leave a comment

Have you ever tried to recreate a component interface and allow PeopleSoft to default the properties and collections? The hard part is that you have to make sure that it uses the same names as your old one or you will have to change your code.

The following SQL helped me figure how to change my code to refer to the correct fields (ex. effdt or effdt0). I was cloning the delivered CI_JOB_DATA component interface with a brand new customized component interface.


SELECT A.BCNAME, A.BCTYPE || ‘:’ || A.BCSCROLL || ‘:’ || A.BCSCROLLNUM || ‘->’ || A.BCSCROLLNAME LOC, A.BCITEMPARENT, A.BCITEMNAME,
A.RECNAME, A.FIELDNAME,
B.BCNAME, B.BCTYPE || ‘:’ || B.BCSCROLL || ‘:’ || B.BCSCROLLNUM || ‘->’ || B.BCSCROLLNAME LOC, B.BCITEMPARENT, B.BCITEMNAME
FROM PSBCITEM A, PSBCITEM B, PSBCITEM AP, PSBCITEM BP
WHERE A.BCNAME = ‘<Old component interface name>‘
AND A.BCNAME = AP.BCNAME(+)
AND B.BCNAME = ‘<New component interface name>‘
AND B.BCNAME = BP.BCNAME(+)
AND A.BCITEMPARENT = AP.BCITEMNAME(+)
AND B.BCITEMPARENT = BP.BCITEMNAME(+)
AND A.RECNAME = B.RECNAME
AND A.FIELDNAME = B.FIELDNAME
AND (BP.RECNAME = AP.RECNAME OR (BP.RECNAME IS NULL AND AP.RECNAME IS NULL))
AND A.BCITEMPARENT = ‘<Parent collection name>‘
AND A.BCITEMNAME = ‘<Item name>‘

Examples:
<Old component interface name> — CI_JOB_DATA
<Parent collection name> — COLL_JOB_EARNS_DIST
<Item name> — KEYPROP_ERNCD