PSRECFIELDALL

The table stores all the fields that are part of a record. A subrecord is decomposed into its fields and show up as fields rather than subrecord.

The PSRECFIELD table contains the subrecord as added to a record without decomposing it into fields.

USEEDIT in RECFIELD

The value is really a sum of certain values based on the edits checked by the user when the record field is created:

The values added up based on how the record field is configured are:


Value
== ================
1 Key
2 Duplicate Order Key
4 System Maintained
8 Audit Field Add
16 Alternate Search Key
32 List Box Item
64 Descending Key
128 Audit Field Change
256 Required
512 Translate Table Edit
1024 Audit Field Delete
2048 Search Key
4096 Reasonable Date
8192 Yes/No Table Edit
16384 Prompt Table Edit
32768 Auto-Update
65536 Identifies field as Field in SubRecord
131072 (Reserved / Not Used)
262144 From Search Field
524288 Through Search Field

National ID Validation

With all national ids being stored on the PERS_NID table in one field NATIONAL_ID, the format checking is performed on the field DERIVED_HD.NID_SPECIAL_CHAR by calling the function Check_NID. This in turns calls addition function based on which contry has been selected by the used.

For Canada, CheckNID_CAN is called and for US . Both are functions on FUNCLIB_HR.NATIONAL_ID ( Field Formula )

Running Application Engine Programmes

1. Thru Peoplecode on one of the events
1. Synchronously using CallAppEngine.
Commits in the AE program are ignored.


2. Async/thru process scheduler : Using CreateProcessRequest.

Since the AE runs in its own session, commits in the AE are fired.

2. Thru Process Scheduler:
1. Submitting a request using a specials page created for the purpose.
2. Using the AE request Page. ( Used only for testing. ) However, you can indicate the MARKET. Helpful if you have a single application engine with sections catering to different markets.

3. In 2 Tier thru Application Designer. ( Cannot set values for staterecords. )

4. Command line:
1. When developing and testing.
2. For restarting.
3. Debugging

%ReturnCode

Return code of last AE step performed.
value is 0 for successful operation.

%Execute

Used to execute multiple statements in an sql action in Application engine.

for e.g.

%execute(/)
INSERT INTO mytbl1 values (1,2,3);
INSERT INTO mytbl1 values (1,2,4);
/

In above example, the separator is changed to "/" for the default separator of ";" in peoplecode.

How can values be passed between a Page and a AE called thru Application engine

While a state record can be initialized with values before calling the application engine program, the parameters passed by value and not reference.

This means that values of the state record set in the AE will not be available to the calling peoplecode on the page..
Use global variables instead.

Restartable Application engine and Global Variables in Peopleocode

In addition to state record, variables defined in Peoplecode as Global variables are also saved in the database.

Application Engine Commits

At my work, I realized that while application engines were being created, people did not know exactly how commits work in application engine.

I therefore set out to undertand application engine commits better. Here is what I have figured out so far:


If you create a new App. Engine program and do nothing for handling commits, the ae is automatically configured to commit after the application engine completes. This means that if the program aborts, everything is rolled back. It also means that there is additional burden on the database to keep track of uncommitted data.

However, there are settings at the section and step level that can be used to commit at specific points in time in the program.

An understanding of the commits is helpful.


1. At Section Level :
1. Turn on commit after step to get AE to commit after every step in that section.
2. Commit even n rows: Foe sections that contain do while, do until and do select, set the
commit frequency to commit after every n rows. The commit fired here is in addition to
commits fired in the loop.

Restriction: For Action of Do Select of type select/fetch where the restart is enabled, the
commits within the loop is ignored.

2. At Step
1. Default: based on the setting at the section.
2. After Step : To override the setting at section and commit after the step completes.
3. Later: To override the setting at section and not commit after step. ( when at section, you
have defined for commit after step. )

Additional Pay

Continuing my previous post, when does P/S give addional pay ?

1. EFFDT should be less than or equal to the PAY END DT on pay calendaer.
2. END DATE should fall after the Pay begin date. Note that this is different from general deduction where the deduction end date is compared against the pay end dt.

When to take general deduction

There are so many dates in peoplesoft that it is difficult to understand the relationship between all those dates.

For e.g. when does P/S take general deductions configured for en employee?

This depends on the following:

1. effective date of the general deduction record setup.
2. whether the general deduction has a deduction end dt ornot.
3. pay end dt of the pay calendar for which the employee isbeing paid.

Payrollprocess compares only the following dates

1. Pay end dt on the Pay calendar.
2. Effdt on the general deduction table.
3.deduction end date on the generaldeduction table is it is populated.

Itlooks for a gen deduction table maexeffdtd row less than or equal to the pay end dt.
If the row does not have a deduciton end dt or the deduction end dt falls after or on the pay end dt,it takes the general deduction.Deduction is not prorated.

Other dates like pay period begin date and check date do not matter.


However, it is worth mentioning that if an employee is terminated during a pay period ( a job row wiht effdt ( and action which terminates the employee ) between the pay begin date and pay end dt , then based on whether the dedcd has been setup to stop deduction when employee is terminated, the deduction is stopped or taken.

Inactivating Work Flows

Recently, I was required to inactivate workflow that were delivered ACTIVE.Since we are not using workflow, we will eventually have to inactivate all the events.

While I got to manually inactivate a few that were causing errors, I identified a peopletools table that could potentially be used for the purpose.

PSEVENTDEFN stored this information.

Instead of manually opening up each workflow, you could update the ACTIVE field to 0. But be sure that you are not really using thr work flow.

If you have a better way of doing it, please share with me and I will thank you for it!

Till next time.

How do you get an AE program to abort?

In certain situation, you want to stop processing and fail the program ?

There are certain places where you can accomplish this:

1. SQL Action with On Error set to Abort.
Other values are
Continue: Continue processing. It will continue processing the next action within the step.

Skip step: Skip this step and go to the next.
Section break: Stop processing the current section and pass control back to calling section.


2. Peoplecode Action with OnReturn set to abort and exit(1) fired within the peoplecode on encuntering the error condition.
3. SQL Action which returns no rows can be flagged to Abort.


Other possible value for Peoplecode On Return are Skip Step, Break.
With break, subsequent steps in section are not processed and control is returned to calling section.
With skip step, the existing step and ( subsequent actions ) are skipped and control is passed on to the subsequent step.

%TruncateTable

%TruncateTable

While we know that oracle implicitly commits after the execution of a Truncate statement, one might wonder the impact of using this in an AE program and causing issues.

Per peoplebooks, AE is smart enough to recognize that and converted a %TruncateTable to a delete!

Wondering whether it will add a where clause ( where process_instance = %processinstance ) ?
Anyone who has worked on it can comment ?

ApplicationEngine Action

A few things about Actions.





1. There is a specific order in with Actions get executed. The order in which you create them does not matter.



1. DO when ( like a if statement.) If the sql returns 1 or more rows, subsequent actions are executed just once.

2. Do While ( Loop. Will exceute all subsequent actions once for every row returned by query.

3. Do SELECT ( Like a for loop. Will execute once for every row returned. )

4. Peoplecode

5. Call Section:

6. SQL

7. Log Message

8. Do Until: Will execute subsequent actions till no rows are returned by the query. Will executed at least one. If if no rows returned the first time, it will executed actions at least one time.



2. Only one SQL action can exist in a Step.

3. SQL Action and Call section are mutually exclusive in a step.

AE State Record

A state record used to store values to e shared across sections/steps can be a
1. Record ( with an underlying database table)
2. derived work record

If you do decide to use a derived work record always disable restart. If not so disabled, after every commit the derived work record will be reinitialized resulting in unexpected behaviour.

An AE program can have multiple state records. In this case only one can be defined as a default state record. The fields of the default state record can be accessed w/o prefixing the record name. For non default state records, you have to prefix the record name.


To set a value of a state record's field use %SELECT ( FIELD name or RECORD.FIELDNAME)
To retrieve values, use %BIND(fieldname/ recordname.FIELDNAME )

AE Tables

This is an attemp to track all the tables used in Application engine programs


1. PS_MESAGE_LOG: ( Messages logs are inserted into this table)
2. PS_MESAGE_LOGPARM: ( Messages logs are inserted into this table)
3. PS_AERUNCONTROL: This table stores the point upto which a restartable AE program has completed.

If there is a dedicated temporary table that has been locked, you can unlock it by going to PeopleTools, Application Engine, Manage Abends and clear the locks.

Application Engine Commit/Controlling flow of program.

One common issue faces by developers as they create an application engine program is committing of data.

Unlike Sqr, where you can fire the commit yourself, it is also possible to define commit strategy in an App engine program and let that do the work for you. In fact, in app engine, to take benefit of restartability, that is what you should do.


CONTROLLIING COMMITS
=====================

First of by default, ( if you do not explicity change the settings and simply create your section, steps and actions ), the default behaviour is to commit at the completion of the AE program.

While this may seem to be the best alternative. Consider a very long running program which runs for 5 hours. If the program aborts everything is rolled back and you have to run again after rectifying your problem.

In this respect, it makes sense to understand how you can control commits in an AE progam.


Here are the different places where you can configure commit:

At Section: You can specify that the commit will happen after every step.

This will control the behaviour of commits after each step in that section is executed.
However, since each step can have its own unique requirement, you can overide this behaviour at a specific step.



At Step:

Various options include:
1. After Step: ( After the step completes. )
2. Later: Do not commit now. If you have flagged the Section to commit after step, use this to override and not commit after this specific step.
3. Default: use the value supplied at the section level. ( If at section not checked. Commit will occur for the AE at the end of the progam. If checked, commit will be after the step completes.
You can set the frquency option which is handy if you had do select, do while or do until action.


Exceptions
=======
When the program is restartable ( AE program is flagged as restartable ), the commits defined within a DO Select of type Select/Fetch are ignored.

For e.g. if there is an action wich calls a section with commit after step defined, the commit is ignored.




CONTROLLING FLOW ( when errors are encountered )


1. Step's On error property:

Depending on whether the above property is set to Abort, Ignore or suppress, the AE program will behave:
If Abort is selected, AE program will terminate. It will also rollback uncommitted changes.
If Ignore: Error will be logged but program will continue. No rollback issued.
If suppress: AE will not log and will continue. No rollback issued.

There is no property at the Section level that can control program flow when errror are encountered.

2. SQL Action:

You can control behaviour when No Rows are affected:
1. Set to Abort to rollback and terminate the AE progam.
2. Section Break: Break execution of the existing section.l Any subsequent steps in the section that sql action is part of are not executed. Returns control to the calling section.
3. Continue: Continue processing.
4. Skip Step: Seusequent actions in th step are skipped and control goes back to the next step.


Finally, if the application engine program is called synchronously on an event during component rocessing, all commits are ignored and the commit occurs after save psot change as part of the component commit.

Types of Handlers in Application messaging

We are used to hearing about only one type of handlers in Application Messaging where in fact there 3 types of handlers

1. Application Packages.
2. Component Interface.
3. Data Mover.

I need to explore the functionality of using the other two.

Application Engine Restart and Commit

Restart feature and commit work hand in hand. Every time a restartable AE program, commits, a check point is issued and the following is saved to the database to facilitate restarts.

1. State record.
2. Local and Global variables.

However, if an AE porgram with restart enabled calls a Section in another AE program with restart disabled and a commit is fired in the that section, no check point is issued.

Restart is suspended till cntrol is transferred back to the calling AE program.

Careful out there! Else you might find that your program behaves irrationally.

How to determine whether a component interface exists for a component ?

Given a component , how do you determine whether a component interface has been created ? While there may be other methods to query the component interface.
The answer is simple.

Simply query the peopletools table.
PSBCDEFN The field BCPNAME contains the component name.

DoModal

Do Modal:



Used to transfer user from existing page to a modal page. User cannot come back to the calling page until, the modal page is dismissed by selecting the ok or cancel button.



The function returns a integer representing the button clicked by the user ( 1 = ok and 0 = cancel )



The calling page can access the records of the modal page. But the modal page cannot access the records of the calling page. To overcome this limiation, you can create component/global variable assign values to it before calling the modal page.

%SelectInit

The Meta SQL %SelectInit ia used in App engine programs to assign values to the state record. But there is a slight difference when rows are not retrieved by the select statement.

While %Select will retain the existin value is no rows are returned, %SelectInit intialized those state record fields to defaults if the select statement does not return any rows.

HTML Area

HTML Area is a page control which is populates with static html code or dynamic html code.

If static, the html code is assigned to the property at design time.
If dynamic, the page is associated with a record (drived /work record ) and a field. The field is populated with the html text. Generally, the html text is stored as an html object and retrived and displayed.

Style Sheets

Stylesheet is a definition like any other object definition. It consists of
classes with attributes that control the appearance/formatting of objects it is applied.

You define a style sheet in Application designer.

Each class is a collection of the above properties. The attributes include font size, color, border width etc.

To make common classes reusable, peoplesoft supports creating of substyles which can be added to multiple stylesheet definitions thus making it reusable.

Each page control defaults a specific class. If that page control's syle drop down list shows use default style, that page control will be applied the default class's attributes.
This can be changed to a custom class that you create provided it is associated with a style sheet linked to the page the page control sits on.

A page can be associated with just one syle sheet. To change the default style sheet for a page, access the page objetc's properties. In the use table change the style sheet.

Thus the following hierarchy emerges
Style Sheet associated with a page.
--> Classes
-> Sub style sheets
--> classes

The class can be associated with the page control and/or the label. ( depends on the type of page control.

This association can be at design time or changed dynamically at run time using peoplecode.

To dynamically associate a class to a page control, assign the class to the Style property of the page control. The class is assigned as a string object in double quotes.

Be carful to avoid makinf spelling mistakes.

Peoplesoft delivers a default stylesheet PSSTYLEDEF which is in turn associated with a number os sub styles.

To use your own style sheet change the Style sheet edit box value in
People Tools > Administration > People Tools Options to your style sheet.
A style sheet has the following properties

1. Type: Standard or sub style sheet.
2. Parent Style sheet: Defalt is the one specified in the People Tools Option page.

You will have to play with the various attributes to see the impact of changins specific attribute s on classes associated with pae control.

Avoiding the Data has changed warning

Often time, you might create pages with scrolls/grids that you populate thru people code.
for e.g. you invoke the insert row function and then set the values of various fields.
If this is done in the component pre and post build events, then these rows are not flagged as new/changed.
However, if these rowsets are populated after the page is dsiplayed( for e.g. whne the user clicks a button ), the rows are flagged as new and changed. Consequently, when the user moves away from the page, the data has changed warning will be issued.

To avoid that(assuming that you do not want to display that warning message) you can set the property on the component. In the Use tab of the components properties, uncheck the Disable Saving Page. This will do the trick.

I bet there might be other options.

To hide the add /delete icons in the Grid

Ever wondered how one can hide the add and the delete buttons in a grid.

It is not enough that you unselect no row insert and no row delete property in the Use tab of the grid. A user can still delete/insert a row from the grid by clicking the + or the - button that gets added.

To hide this button, go to the properties of the grid, go to Label tab click the properties button in the Body area and click the invisible check box.

Overiding the default flagging of records as new/modified

Generally, changes made to a row including a row insert ( even thru peoplecode ) caused the row to be flagged as changed or new as the case maybe.

The only exception is changed mage to a field in the Field Default and Field Formula.


This behaviour can be overriden in the following circumstances:

1. When a row is inserted, you can prevent that row from being flagged as new.
2. Changed made in the rowinit: Component processor can be instructed to not flag that as a change.

This is accomplished by setting the ChangeOnInit property of Rowset class to false.