PeopleSoft Learning

Coverage End date (COVERAGE_END_DT) in Benefit Enrollment tables.

The Coverage End Date is a legacy field from a very early release of PeopleSoft that is no longer used.  It is not populated by any online or batch process.

A new row inserted into the Health Benefit Enrollment with Coverage Elect of 'W' for Waive and 'T' for Terminated could indicated end of coverage and the Coverage Begin Date along with the Effective Date are used to identify the date coverage ended.

  When you insert a new Coverage Begin Date, the prior coverage is assumed to be ended the day before.

For example,

Single Coverage, Effective 1/1/2009
Family Coverage, Effective 3/1/2009 (assumed single coverage ended on 2/28/09)

How to change the file name of the BI/XML report in Peoplesoft?

In the report definition object set the value in the OutDestination Property using in the Set/Get methods. This field contains the full of the the file name of the generated report.

Report file by default will be the name of the Report ID used.
property OutDestination find and replace the ReportID value in the OutDestination. OutDestination - It contains the absolute path where report file has to be created not just the file name.

When Bursting option is selected on the field to create separate files then value of the bursting field will be file name.

How does find references work ?
How does find references search in Peoplecode ?

When ever you save an people code in a event and saved ,it parse the peoplecode and convert it in to binary format and store in the PROGTXT field of the PSPCMPROG. At the same time for every People soft object referred in People code there will be a row inserted in to the record PSPCMNAME. It stores information about the Peoplecode that refer particular object.
When find references for an object is requested it query this table to find the result.

It would have been better if peoplesoft extend this functionality to Application Packages, Message catalog , functions. It will be very useful for the developer to find the references to find these elements usage in different Peoplecode.

SQL to find the locked objects in the database.

select b.object_name,a.session_id,a.oracle_username,os_user_name ,
locked_mode from v$locked_object a, all_objects b where a.object_id = b.object_id order by b.object_name;

SQR - Get Sample Code Snippets , SQR Help , SQR Reference.

http://www.ontko.com/sqr/

http://www.sqrevangelism.com/index.html

http://www.sqr-info.com

http://www.sqrug.org/

http://groups.yahoo.com/group/brioportalusersgroup/

Bulk Insert - As the property name indicate this property is indication that SQL is doing Bulk data insert. This property is for Insert statements. When this property is enable buffering of the rows to be into the record. Buffering of the rows to be inserted will have considerable performance improvement in the Oracle, Microsoft SQLServer, and DB2 Database.

This property should be set when INSERT SQL is called multiple times in the absence of intervening COMMITs.

PeopleSoft Application Engine ignores the Bulk Insert setting in the following situations:

1) SQL is other than an INSERT/VALUES statement that inserts one row at a time. For
instance, the following statements would be ignored: INSERT/SELECT, UPDATE, or
DELETE.
2) SQL does not have a VALUES CLAUSE.
3) SQL does not have a field list before the VALUES clause.

Most of the databases has have SQL statement caching for performance improvement of the SQL execution.

This means that every time they receive SQL, they compare it against their cache of previously executed statements to see if they have seen it before. If so, they can reuse the old query plan. This works only if the SQL text matches exactly. This is unlikely with literals instead of bind variables. I,e SQL match will not consider standard bind parameters during the matching process.
Advantage of this is Database need not complie & create execution plan for every exectution and just re-use the existing one.


When to use it
1) When we AE program has Do Select/Do while / Do unitl looping construncts and you need to run the same SQL step mulitple times. It can be used even the same sql statments are present in different steps as well.
2) Use ReUse only if you do not use Bind variables for column names.
3) Use ReUse only if you have no %BINDs for Field names , Record names & logic condition.
if you have Bind for the column,Record name which will not change across multiple execution of the sql. i,e Bind value for the column , record name is same to make use of the ReUse
UPDATE PS_JOB
SET COMPRATE =
WHERE %BIND(COMP_RATECD, STATIC) = %BIND(COMPRATE)
COMPRATE value can change for every execution.
4) To re-prepare a ReUsed statement from scratch, because one of the static binds has
changed and the SQL has to reflect that, use %ClearCursor.

-Ganesh

Often we Come across a situation where in our program goes to No Success (one of the Reason can be Wrong Prompt Record Value) , if don't handle CI code properly in Application Engine, and we usually end up doing cosmetic changes like using Execute Edits ,SQL exec to resolve the issue, which often increases the CI time, below are the Tips/Step if followed can handle the CI Programs Efficiently.

The following are some important things to remember about CI behavior in AE programs.

1. When running inside of an AE program, a CI's Save() call will not actually commit the data to the database. The commits are under the AE's control. Therefore, if there is a problem with invalid data in a CI property, everything will be rolled back to the previous committed transaction.

2. Required fields in a CI that use a prompt table and have a default value appear to behave in a peculiar way. If an invalid value is entered for the field, the system will change the value to the default value, roll back the rows to the previously committed transaction, and then commit this row to the database. It is a good practice to write errors (such as CI errors) to a log file so that you can go back later and re-enter the record correctly.

3. When a CI fails due to invalid data open cursors are sometimes closed or are left in an invalid state, and subsequent calls to these cursors cause the AE to abend. Testing and debugging help determine what the appropriate solution might be. For example, in some cases the solution may be to ensure that your cursors are open before doing the next fetch (if appropriate). At other times (where the cursor is in an invalid state) you'll need to fetch the rows into an array of records first, and then process them using the CI. This is because even thought the cursor is open (IsOpen call returns true), it is unusable.

RESOLUTION:

To work around the above issues, do the following.

1. Use CommitWork() calls at appropriate intervals (every row or a set number of rows). Remember that to use CommitWork(), (a) "restart" must be disabled; and (b) don't use CallAppEngine() to call the AE program.

2. In the event of a CI error, perform (a) or (b) below.

(a) Check to see if your cursors are open, and if they're not, open them (if appropriate) before doing your next fetch. The following PeopleCode snippet shows an example of how this can be done.

If Not &MYSQL.IsOpen Then

&MYSQL.Open(SQL.MYSELECT);

End-if;

(b) If the cursor is still open but unusable, then you'll need to fetch the rows into an array of records first, and then process them using the CI.

3. Write errors to a log file.

The basic steps (using 2b to fetch the records into an array) would be as follows.

1. Disable restart (and not use CallAppEngine() to call AE)

2. Use a single step of People Code instead of a DoSelect

3. Fetch the records into an array of records and close the cursor

4. Run a For loop calling the CI for all the records in the record array.

5. Do a CommitWork() call after calling save() on the CI for every row (or several rows if performance may be an issue).

6. Do a cancel() call on each row (CI instance) after you called save() and CommitWork()

Warning; If the CommitWork() function is used, you will have to develop a plan in how to handle if your program aborts and may have data half committed.

When we are working on PIA we often feel that we are visiting the same areas within the system regularly.
So to make our life easier we can create a Pagelet based on a custom Navigation Collection containing our often used components. This greatly reduces the number of clicks required for day-to-day work and could be really useful for end users.

Point to be noted : Nothing shown here requires the Enterprise Portal product.

I will just explain this with an example -

Creating the Navigation Collection

First, let’s create the Navigation Collection. You can have whatever menus/components you want in your collection.

Go to PeopleTools > Portal > Portal Utilities > Navigation Collections Add a Collection with an appropriate Name and Description Click Add Folder Select Menu Folder, from the Employee Portal. Use the prompt to locate the folder to add, click the plus folder next to a high level menu to expand it and choose a child menu. Highlight the top level, and click Add Folder again. Repeat the process until you have added all your folders.
We can also add links directly to the Components we used most often, like Process Monitor, and moved them to the top of the list. Click Save (and remember the name of your new Collection). Publishing the Navigation Collection as a Pagelet Go to PeopleTools > Portal > Pagelet Wizard > Pagelet Wizard Add a New Pagelet and fill out the Title etc Choose a Data Type of Navigation Collection Choose a Portal of Employee and prompt to find your Navigation Collection. We normally reduce the Max Child Links to 5. I choose the Menu layout. I set the ‘Parent Images’ = Small, Turn off ‘Parent Descriptions’ and set the ‘Child Display Type’ = Simple List.

On the final page, check HomePage Pagelet, choose a folder, and set the security required.

That’s your Pagelet done. Now add it to your Homepage.
Adding a Pagelet to your Homepage From the PIA Homepage, select the ‘Personalize Content’ link. Place a Check in the Checkbox next to the Pagelet you want, then click Save. Click the Personalize Layout link. Select whether you want a 2 or 3 column layout, and move the new Pagelet to the column you wish it to appear in, click Save.

Thats how you can create your own Custom Navigation Collection and Pagelet.

Record level auditing allows you to have separate audit tables dedicated to one database record.

Below are the steps to turn on record level auditing

1) Clone the record you wish to edit
2) Add the audit fields: (Key Fields)
AUDIT_OPRID
AUDIT_STAMP
AUDIT_ACTN
And make sure you check off the "system maintained" check mark on the record field properties
3) Delete fields that won’t be audited
4) Remove all key structures
5) Save and build the record
6) Link the base record to the audit record
Open- original record definition
Now all of your changes for that record will be tracked in the new audit record you just created.

How to pass Dynamic parameter value to XML publisher Template using people code ?
How to pass Dynamic Parameter Value to XML Publisher from the runcontrol page ?

You need to pass these as runtime Properties through PeopleCode. The ReportDefn class has setRuntimeProperties() method that can be used for this purpose. So the PeopleCode behind your page will need to get the values from the page and pass them to the ReportDefn object.

Your PeopleCode will include something like this.

&asPropName = CreateArrayRept("", 0);
&asPropValue = CreateArrayRept("", 0);        
&asPropName.Push("xslt.ParameterName");
&asPropValue.Push("Parametervalue");

You can pass more parameters here

&oRptDefn.SetRuntimeProperties(&asPropName, &asPropValue);
Process and publisher/print your ReptDefn here 

In your template, you need to have something like:


Declares the parameter InvThresh

use the Parameter value by prefixing $ to the Parameter Name.

PeopleBooks includes an example on setting Runtime properties. 

PeopleTools passes the following parameters by default:

ReportID
ReportTitle
RunDate
RunTime

%TruncateTable is not just able just to truncate the table. It changes to Delete statement when it is used with the Temporary table When shared base table has been allocated, because no dedicated instances were available. PeopleSoft Application Engine performs a delete by process instance instead of performing a truncate.

You should always use %TruncateTable to perform a mass delete on dedicated temporary tables, especially if the Continue option is in effect.

Even if you have elected to terminate the program if a dedicated table cannot be allocated, you may still use %TruncateTable meta-SQL with dedicated temporary tables. %TruncateTable resolves to either a Truncate or a Delete by process instance, as needed.

The argument of %TruncateTable is a table name instead of a record name. As a result, you must code your SQL as shown in the following example:

%TruncateTable(%Table(recname)) 

When all the lines in the data have more than one file layout structure, it is difficult to read them and load them to different record. Record file ID can be used to simplify the task but it will work only when the one file record ID to one record mapping is present. If the file has the values where more than one file record ID needs to be mapped to one record.

When file type is fixed file.
There is one way to do a work around in which one record and one file layout will created on this record in which all the fields from all the different records in to which data needs to be loaded. In record should have all the fields as character fields later convert it in to number and date.
In the file layout define start position and end position of fields based on the layout that this field belongs to i,e more than one field will have the same position and position may be overlapping as well. This will work when the common fields in the different records in to which we need to load should have same position in the file.

In the Peoplecode after read rowset use the custom sql based on the field to identify the record that needs to be populated execute insert sql.

When file is not the fixed file.
Then read all the data in to one field of record. field length should be long enough to load the entire line of the data. Split the data based on the seperator character. Identify the record in to which data needs to be loaded based on the certain field and insert data in to that table.

Peoplesoft Application Trace.

Most professionals involved with PeopleSoft have seen a SQL trace file at one point in their career. Although extremely valuable, extracting the useful information from this file can be a significant hurdle. Loading the file in Pace-Trace, this information is made available to you in a clear format, even if you are not a programmer. Being able to continue focusing on the problem, and not the file, dramatically speeds the process toward your eventual solution.

Pace-Trace?
Pace-Trace is effectively a viewer for PeopleSoft SQL trace files. It reads a selected trace file and creates a grid that summarizes all the SQL actions contained in the file. This quick access to trace file information can be used by everyone from business analysts to developers, speeding report design, custom development, and troubleshooting.

Source :- http://www.pace-soft.com/

Auto populated Fields in the AET record. Fields that are populated in the AET record.

1) Process Instance
2)OPRID
3)RUN_CNTL_ID

are the fields that are auto populated in the AET record. Process Instance is mandatory key field in the AET record.PROCESS_INSTANCE field value cannot be changed by the Application Engine. OPRID & RUN_CNTL_ID field is optional fields in the AET record. These fields are auto populated in the AET record and need not use SQL and populate these fields.

In the First SQL step of the Application Engine we can use %Bind(OPRID) and %Bind(RUN_CNTL_ID) instead of the %operatorID and %Runcontrol.