1. Peoplesoft tables of interest.......................................................................................... 2

2. PDF reports................................................................................................................. 2

3. FTP job to copy file to NT server in Dev environment........................................................ 2

4. PS Queries.................................................................................................................. 3

5. Translate table............................................................................................................. 3

6. Installing PeopleTools................................................................................................... 3

7. Scheduling/reporting..................................................................................................... 4

8. General Table Naming................................................................................................... 4

9. Adding Objects............................................................................................................ 4

10. Cloning pages and adding to menu............................................................................... 4

11. Effective Dating SQL................................................................................................... 5

12. Paycycle id’s............................................................................................................. 5

13. Peoplesoft Application Development............................................................................. 6

14. Peopletools Tables.................................................................................................... 16

15. Peoplesoft Architecture............................................................................................. 16

16. Peoplesoft Design Methodology................................................................................. 16

17. PeopleCode.............................................................................................................. 17

18. Security................................................................................................................... 23

19. Process Scheduler.................................................................................................... 23

21. Create Paysheet/Run Paycalc process....................................................................... 23

22. Pay Unsheet process................................................................................................ 24

23. Pages/components behind the web page.................................................................... 24


1. Peoplesoft tables of interest

 

 

 

PS_PAY_OTH_EARNS does not have emplid as part of the key. The way to find corresponding records in this table is:

 

Select *

From PS80PRD.PS_PAY_OTH_EARNS A, PS80PRD.PS_PAY_EARNINGS B

Where b.emplid = ‘100099999’

And b.pay_end_dt = ‘12/31/2005’

And a.pay_end_dt = b.pay_end_dt

And a.company = b.company

And a.paygroup = b.paygroup

And a.page_num = b.page_num

And a.line_num = b.line_num

 

Paysheet run id’s are in:

PS_PAYSHEET_RUNCTL

Pay calc run id’s are in:

PS_PAY_CALC_RUNCTL

Process scheduler status

PSSERVERSTAT

 

To see values for server status

select

*

from

ps89cnv.PSXLATITEM

where FIElDNAME = 'SERVERSTATUS'

 

 

Pay rates

PS_SAL_PLAN_TBL

    (SETID, SAL_ADMIN_PLAN, EFFDT)

    PS_SAL_GRADE_TBL

             (+  GRADE)

          PS_SAL_STEP_TBL

                  (+ STEP)

               PS_SAL_RATECD_TBL

                     (+COMP_EFFSEQ,

                                    COMP_RATECD)

 

PS_JOBCODE_TBL

      (SETID, JOBCODE, EFFDT – has SAL_ADMIN_PLAN to tie back to above tables)

 

 

 

 

 

 

2. PDF reports.

 

Some SQR’s are set up to generate PDF reports. They can only run on the server, not on the mainframe.

 

Some reports are set to only print to a specific printer. When testing check where the report may end up.

 

3. FTP job to copy file to NT server in Dev environment

Promotion of program via Endevor automatically copies program to QA NT directory.

 

    TESTPSFT.PS80DEV.NEESJCL(FTPTOFS) - 01.99       Columns 00001 00072

 ===>                                                  Scroll ===> CSR

***************************** Top of Data ******************************

//$FOERTSS JOB '13,99,IO5160,1734,0345,G1003','               ',

//  CLASS=T,MSGCLASS=Q,NOTIFY=$FOERTS

//*

//********************************************************************

//**     SEND THE FILES TO FILESERVER VIA FTP                      ***

//**     DO NOT MODIFY THIS JCL OTHER THAN JOBCARD AND PUT CMD(S)  ***

//********************************************************************

//*

//*

//FTP010   EXEC PGM=FTP,PARM='(EXIT'

//SYSPRINT DD SYSOUT=*

//* IP ADDRESS FOR WBROPS02 HAS HOME DIRECTORY PSFT80

//INPUT    DD *

PS80QA                     << note connect to QA

FTP80DEV

HAPPY123

LOCSITE FILETYPE=SEQ

CD PS80DEV                 << but sending to dev directory

CD SQR

PUT 'TESTPSFT.PS80DEV.SQRSRC(PHW410)' PHW410.SQR

CLOSE

QUIT

/*

 

4. PS Queries

 

The following tables are involved in queries

 

PSQRYDEFN

PSQRYSELECT

PSQRYRECORD

PSQRYFIELD

PSQRYCRITERIA

PSQRYEXPR

PSQRYBIND

 

All tables are keyed by oprid and qryname. To delete a query your could run

 

DELETE FROM PSQRYDEFN WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYSELECT WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYRECORD WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYFIELD WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYCRITERIA WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYEXPR WHERE OPRID = 'X' AND QRYNAME = 'Y'

DELETE FROM PSQRYBIND WHERE OPRID = 'X' AND QRYNAME = 'Y'

 

 

5. Translate table

 

You may be able to get a translation of a code by looking in the XLATTABLE. For example, to find codes for PS_JOB.EMPL_TYPE run the following

 

            Select * from ps80prd.xlattable

            Where fieldname= ‘EMPL_TYPE’

 

 

6. Installing PeopleTools

 

Connect to  server //nyhcbappd2v

Go to PS80DEV\wscfg directory

Click on one of the PS80DEV_from_x_drive.bat files.   (x is j,k or l)

 

7. Scheduling/reporting

 

Peoplesoft jobs in prod are set to be single threaded. Only one PP job runs at a time. This ensures, among other things, that the PSSQR100 and PSSQR614 procs using PSFTPRD.PPXJ0100.TEMP.PRTFILE/.BKUP report files do not get over overwritten by another job before the current one completes.

 

In test however, the PSSQR100 and PSSQR614 procs are different and expect a delete/define member the same name as the SQR in ‘TESTPSFT.PS80DEV/QA.UTIL.CNTRLIB’. The member is used by IDCAMS to delete/define the report files – that use the SQR name in report files to avoid testing conflicts.

 

8. General Table Naming

 

Tables starting with PS_ are Peoplesoft tables.

 

Tables starting with PS (no _) are PeopleTools tables

                        PSRECDEFN – record definition

                        PSRECFIELD – field definition

 

Views

            Summary page views …_VW

            Search record views   …_SRCH

            Prompt table views    …_VW

            Reporting views          … No common suffix

 

 

9. Adding Objects

 

Adding object to project

Click Insert/Current Object into Project

Field definitions

In Peoplesoft Application Designer

File/Open, select Field from Definition Dropdown

Type name (or part of name) in Name field, click Open

 

Select field name displayed in list, and click Open, or doubleclick on name to display field properties.

 

Select Edit, Find Definition references to see where field userid

 

To create a field

File/New, select Field from list – follow prompts as needed

Click File/Save to save definition

 

 

 

 

 

 

 

 

10. Cloning pages and adding to menu

 

Page NP_RUNCNTL_PHW430

 Opened page NP_RUNCNTL_PHW020 and did save as NP_RUNCNTL_PHW430.

Update page (text literals) as needed

Components NP_RUN_PHW430

Opened component NP_RUN_PHW020, save as NP_RUN_PHW430

Updated Item Label (displays as tab label on screen)

Right click on component line to display component properties

Changed values in General, Use, and Internet tab fields as needed.

Menu

Opened Administer Workforce (GBL) to add menu items

Update menu item properties

Security

Go/PeopleTools/Maintain Security

In new window

Use/Permission Lists/Pages/”Update/Display”

 

Display permission list for ALLPANLS

Clicked on Edit Component next to Administer_Workforce(GBL)

Found menu item under CSTM Reports found PHW430 and clicked on Edit Pages

Set page permissions same as PWH020

Process Scheduler Manager

Clicked on Go/Peopletools/Process Scheduler Manager

Scheduler App opened

Use/Process Definitions/Process Definition Options/”Add”

Process Type – SQL Report

Process Name – PHW430

Process Definition Tab – Type in description and long description

Process Definition Options tab – Run (Both), Component – NP_RUN_PHW430, Process Group – HRALL

Other tabs left as defaults.

 

 

 

 

11. Effective Dating SQL

 

…

FROM PS_JOB C

 WHERE C.EMPLID = &A.EMPLID

   AND C.EFFDT = (SELECT MAX(PJ.EFFDT)

                   FROM PS_JOB PJ

                   WHERE PJ.EMPLID = C.EMPLID

                     AND PJ.EMPL_RCD = C.EMPL_RCD

                     AND PJ.EFFDT < &B.EFFDT)

   AND C.EFFSEQ = (SELECT MAX(PJ.EFFSEQ)

                   FROM PS_JOB PJ

                   WHERE PJ.EMPLID = C.EMPLID

                     AND PJ.EMPL_RCD = C.EMPL_RCD

                     AND PJ.EFFDT = C.EFFDT)

 

12. Paycycle id’s

 

Montly payrolls are identified with a 2 digit year and a 1 letter month, eg 06D is 2006/April.

 

Weekly payrolls are identified with a 1 digit year and a 2 digit week, eg. 602 is for week 01/01/06 to 01/07/06 – with check date 01/12/06  (2nd paycheck of the year)

 

 

 

13. Peoplesoft Application Development

 

Starting with Peoplesoft 8, Peoplesoft Pure Internet Architecture has all activities accessible via the web browser

Designing and application

Plan the data structures

  1. Define fields – names, attributes, default values, edit values, prompt table
  2. Define records – names,  keys, search, master/child relationships
  3. Design pages, define records to be used on page, menu access
  4. Set test plan
  5. Create component definitions
  6. Register components

Key function in App Designer

Alt 0 (zero) – toggles project workspace show/hide

Alt  1  -  toggles  output window show/hide

Data validation strategies

Prompt table edits

Effective dated tables

Translate table edits

Yes/no edits

Tableset sharing prompts

Project options

Tools/Options

Reload project at startup

Code/setup Validation

Inserting objects into project

Undo/redo buffer depth

Font size

Image storage formats

 

Creating new fields

For any new fields gather the following

Field/Label Name  eg   Last_name

Type   eg. Char, Num, Date, ..

Length   eg 6 (for Char)  2.1 (for Num)  10 (Date)

Label (long/short)

Format     Upper, Mixed,  N/A

Translates     Y/ -

New      Y/-

 

Add field via  File/New/Field (from list)

 

Go to File/Object Properties with field selected to and text  and owner Id if needed.  If the field is a character field there will be an International Format and Translate Values tab

 

Creating translate values

Go to File/Object Properties with field selected  (field must be char)

Select Translate Values tab

Click on Add to add translate values

 

Translate values should end up in the owner.XLATTABLE table

 

Record Definitions

File/Open/Record , type in record (or partial key) hit Enter to display/select

 

With record displayed

View/Fields Display

View/Use Display

View/Edits Display

View/Peoplecode Display – display by field all places Peoplecode can be attached,  FieldDefault, Field Change, Field Edit,….

 

 

* Remember in App Designer the records are referenced w/o the PS_

 

Click on record field to show Use and Edit tabs where you can set

Key, Duplicate Key, Alternate Key, Prompt table for field value, data entry via checkbox, dropdown list, etc

Use above

 

Create table DDL by

Build/Current Object

Select build options

Check build script file

Click in settings to check

Click build to generate script

See build tab at bottom of App Designer for message

 

 

If you have record definition displayed, clicking on the print icon will print the definition, plus any Peoplecode, xlat values, etc.

 

Page Definitions

 

File/Open/Page , type in record (or partial key) hit Enter to display/select

 

Placing a field (with translate values) on page, double click on it, check show prompt button to (automagically) active field lookup

 

When a page is displayed on the web, pressing cntl/J will display a page detailing info on the page such as the page, the page component, menu, app server, etc.

 

Page fields

To create radio buttons that set value of one field

  1. Create as many radio buttons as possible values to be set
  2. Set assigned value to each button (value to assign if selected)
  3. Insert a group box and position it to surround the buttons
  4. Set record,field, and label properties of group box

 

Scroll Areas

  1. Select insert, scroll area
  2. Drag scroll area onto page and surround fields to be scrolled
  3. Set properties of scroll area , occurs level and occurs count
  4. Set title properties via page element type – Message catalog, message set and message number

 

The message catalog table should be used to avoid hard coding

 

Field order

  1. Select Order tab on page
  2. Set order as needed

 

Adding grid on page

To create a grid

  1. Add a grid control to the page
  2. Drag/drop fields into the grid
  3. Set grid properties
  4. Resize grid as needed

 

Multiple page levels

Create level 0

  1. Create new page
  2. Drag/drop level 0 fields to page

Create level 1

  1. Select Insert Grid
  2. Drag/drop grid
  3. Update grid properties

a.       Main record – level 0 record

b.       Page Field name – level 0 record

  1. Drag/drop selected level 1 record into the grid
  2. Resize record fields/grid as needed
  3. Add related fields (such as description for code)

a.       Click on field

b.       Add record/field to related record/field

c.       See other field properties as needed

7. Check field page order by clicking on Order tab

 

 

(Use message database for any messages)

Components

A page must be attached to a component.

A component can be attached to a menu

 

Create the component by

File/New,  select component from New Definition dialog

Expand pages folder in project workspace

Drag the page from the folder to the component

Enter info as needed in component properties

            Search record

            Actions – add, update/display, etc.

            Toolbar actions – save, cancel, spell check, view worklist, …

 

 

 

Registering components

Use Registration Wizard to

  1. Place component on menu definition
  2. Create a content reference in portal registry
  3. Place component in permission list

 

To start registration wizard

  1. Open the component in Application Designer
  2. Select Tools, Register Component
  3. Set ‘Add component to menu’, ‘Add component to portal registry’, ‘Add this component to a permission list’, click Next
  4. Respond to prompts

 

Security

Roles –

 

  1. Select PeopleTools, Maintain Security, Use , User Profiles, Enter userid or search to select
  2. Assign roles, permission lists, etc.

 

 

Permission Lists -

  1. Select PeopleTools, Maintain Security, Use , Permission Lists

 

 

To allow user to select menu item from menu

  1. Select PeopleTools, Maintain Security, Use , Permission Lists (Search -Pick permission list needed)
  2. Sselect Pages Tab, select menu that page is on and click edit components
  3. Click on Edit Pages for that component, check appropriate options

 

 

Portal Registry

Select Peopletools, Portal

 

Node Definitions – for default node

Portal Definitions – for default portal

 

PeopleTools, Portal Structure and Content

Click Edit link for folder (use select new parent to move folder)

Click Add Folder to …. Add a folder!

 

 

Examing breadcrumbs, folders and content references

 

  1. Peopletools, Structure and Content

 

Searching Site

Execute Verity search by

  1. Enter search criteria in search field

 

 

Update search registry index

  1. Peopletools, Portal, Build Registry Search Index
  2. 2. Add new run control id (if needed)

(Runs PS application engine process called PORTAL_INDEX)

  1. Run/OK
  2. Process Monitor/Refresh until success appears in Run Status

 

 

Data Audits

Create an audit record

  1. Open the record definition to be audited
  2. Do File/Save As AUDIT_.... (record name)
  3. Insert audit fields into AUDIT_ record

      Insert AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN as first 3       fields on AUDIT_ record

4. Delete unneeded fields on AUDIT_ record

5. Clear key, search, list, default value fields on AUDIT_ record

6. Set ‘Auto Update’ on AUDIT_STAMP

7. Do File/Save

8. Build table

9. Open original record to be audited

10. Select File, Definition Properties, Use tab

11. Enter AUDIT_... into record name field, set ADD, Selective, Delete, and/or Change

 

 

 

Set Field level audit

  1. Display record definition
  2. On Use table, check audit options, Field Add, Change, Delete in Audit group box

Export/Import

Exporting

  1. Open the project.
  2. Select the Upgrade tab
  3. Select Tools/Copy Project/ To File
  4. Make sure all definition types are selected
  5. Set the export directory
  6. Click copy button

 

Two files created - xxx.ini and xxx.xml

 

Importing

  1. Select Tools/Copy Project/From File
  2. Browse to folder containing xxx.ini and xxx.xml export files
  3. Click Open
  4. Make sure all definition types are selected
  5. Click Copy
  6. Select Tools/Validate Project
  7. Click No in Delete Objects Dialog box

 

 

Creating Peoplesoft views

 

SQL View option - If you want to write the SQL view definition

  1. Create new field definition
  2. Insert fields Save record definition
  3. Set the record field attributes
  4. Write the SQL statement (Order of the fields must match the order of corresponding fields in the view select statement
  5. Build the view

 

Query View – Create the view using Peoplesoft to prompt you through building the query  This process has 3 parts:

  1. Record definition
  2. Query definition
  3. Documentation

 

To create a Query View

  1. Create the record definition
  2. Click record type tab
  3. Select Query View option and ‘Click to launch Query’
  4. Save record definition
  5. Create query for view
  6. Test query
  7. Copy SQL and paste into Record Properties
  8. Save record definition
  9. Remove unnecessary field attributes
  10. Save record definition
  11. Build View

 

In the end there is no difference in the result between building the view on your own (SQL View option) vs having Peoplesoft (Query View) prompt you through it.

 

There is a potential problem with SQL View in that if the field order changes but view sql doesn’t you end up with a view field mismatch to the underlying view sql.

 

Summary Pages
  1. Summary pages summarize or display data in view only mode (no update)
  2. Usually do not contain related fields (use view if needed)
  3. Exceptions to related fields are:
    1. joins to effective-dated tables
    2. Including short/long descriptions from translate table

 

The key structure in views are used differently that tables. In views the component processor uses the ‘key’ to search and sort.

 

First key in a summary view should be the search key. Information depends on the search key at level 0.

 

Te level 1 view must have at least to key fields – the level 0 key and 1 more

Search records

Three questions for search records

1. Which table is source of high-level keys at level 0 on the page?

2. Which rows of data should be seen by user?

3. Which fields should user on the search page

 

 

Every component must have a search record

When user clicks a content reference, the corresponding component search records and actions are used to build search page

The search record of a component is used to request data from database server, and since only selected fields returned, data retrieval is minimized.

 

The search page is dynamically created page built by the system – not user defined.

 

Search keys appear as fields on the search page as they appear in the records.

 

Alternate search keys are not key fields but they appear as fields on the search page to help find existing records (really the primary key existing records)

 

Alternate search keys are not key fields but they appear as fields on the search page to help you find an existing key.

 

List box items are fields in search results list – they do not have to be the key or alt key fields

 

The main purpose of the search page is to find key value(s) that the system can insert into an SQL phrase for use as the primary key in the SQL where phrase. (The level 0 record is found and returned)

 

When in add mode the primary seach key is used to construct the initial Add page where the user enters the new (primary) key. The subsequent page displayed allows the user to enter the non-key fields values.

 

Search views (views ending with _SRCH) are created to provide additional search criteria, and create row-level security. So on component dialog window the search record can be the _SRCH view but the ‘Add search record’ would be the base record.

 

If _SRCH record is defined but an ‘Add search record’ is not a user could bypass the security

a.       When user selects add a new value, the component process first tries to select the value from the search record

b.        If record not found, the page processor takes over to load page

c.       If record in base table, the record is retrieved and displayed (uh-oh)