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

PeopleTools Tip: Searching for Records

digitaleagle Fields, PeopleTools Tip, Records, Uncategorized Leave a comment

You can find records that contain a specific field with the find definition references tool in App Designer, but what if you want to know what record contains field1 and field2?

You can go to the database to file that answer:

SELECT A.RECNAME
FROM PSRECFIELDALL A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.FIELDNAME = ‘FIELD1’
AND B.FIELDNAME = ‘FIELD2’

PeopleTools Reference: Meta-Data — Object Type

digitaleagle Uncategorized Leave a comment

The PeopleTools project-related tables contain a field called OBJECTTYPE. The field is a number field, which means that it cannot have translate values. This list shows what the numbers mean.

You can build the list yourself by running a compare report in Application Designer. The report files contain both the number and the description of the object type. But, this list should save you the time.

00 Records
01 Indexes
02 Fields
03 Field Formats
04 Translate Values
05 Pages
06 Menus
07 Components
08 Record People Code
09 Menu People Code
10 Queries
11 Tree Structures
14 Colors
15 Styles
17 Business Processes
18 Activities
20 Process Definitions
21 Server Definitions
22 Process Type Definitions
23 Job Definitions
24 Recurrence Definitions
29 Business Interlink
30 SQL
31 File Layout Definitions
32 Component Interfaces
33 Application Engine Programs
34 Application Engine Sections
35 Message Nodes
36 Message Channels
37 Messages
38 Approval Rule Sets
39 Message People Code
40 Subscription People Code
42 Comp Interface People Code
43 Application Engine People Co
44 Page People Code
45 Page Field People Code
46 Component People Code
47 Component Record People Code
48 Component Record Field People Code
49 Images
50 Style Sheets
51 HTML
53 Permission Lists
54 Portal Registry Definitions
55 Portal Registry Structures
56 URL Definitions
57 Application Packages
58 Application Package People Code
59 Portal Registry User Homepag
60 Analytic Types
61 Archive Templates
62 XS LT
63 Portal Registry User Favorit
64 Mobile Pages
65 Relationships
66 CI Property People Code
67 Optimization Models
68 File References
69 File Type Codes
70 Archive Object Definitions
71 Archive Templates( Type2)
72 Diagnostic Plug- Ins
73 Analytic Models
75 Java Portlet User Preference
76 WSRP Remote Producers
77 WSRP Remote Portlets
78 WSRP Cloned Portlet Handles

PeopleSoft Information on the Internet

digitaleagle PeopleTools, Resources, Uncategorized 1 Comment

There is more information on the Internet than you might expect. Here are some links that you might find handy.

PeopleBooks

Instructions

WebSites

Blogs (updated 2/19/2007)

Miscellaneous Info(updated 2/19/2007)