PeopleSoft Crazy

In PeopleSoft, one can create either multiple employment instances or multiple employment assignments or both. The difference is as follows:

• An Instance is the parent of assignments under it. This means that it is possible to allocate multiple employee assignments under one instance.
• The employee record number is incremented as one creates multiple instances and/or assignments.
• The employee record numbers assigned to each record will differ based on the order in which they are created. For e.g. let’s say that there are 3 instances created initially for an employee. Each of these instances have only one assignment. This means instance 1 will have employee record number 0 (zero), instance 2 will have employee record number 1, instance 3 will have employee record number 2.
• Considering the above scenario, if I create 2 additional assignments in that order for the instance 2, then for the first additional assignment pertaining to the second instance, the employee record number will be 3 and for the second additional assignment, the employee record number will be 4.
• Now consider the following scenario – Initially 2 instances are created. Then 1 additional assignment is created for the 2nd instance. Then the 3rd instance is created followed by the creation of 2nd additional assignment for the 2nd instance. Now the employee record numbers will be assigned by the PeopleSoft system as follows: 
• Empl Rcd 0 (Zero) ——  Instance 1
• Empl Rcd 1 —————  Instance 2
• Empl Rcd 2 ————— Additional Assgn 1 for instance 2
• Empl Rcd 3 —————  Instance 3
• Empl Rcd 4 —————  Additional Assgn 2 for instance 2
• It has to be noted that the record for which the employee record number is 0 (zero) is considered as “Primary” Job and all others will be considered as “Secondary” Job.
• Also for multiple assignments, the first record will have the action as “Additional Job”.
• For the multiple instances, the first record will have the action as “Hire”.

I plan to devote this week to nVision. The reason being that I have to produce an income statement report in Finance. The report looks demanding to me and it’s also a drill down report. There are two options for me : Using PeopleSoft nVision to do the report or using Microsoft Reporting Services to do the report. I initially thought I can do the report in reporting services. But I thought it will be rather better to look into the features of nVision and then decide whether to go with nVision or with Reporting Services. The first thing that I wanted to know is how will I know what is the SQL that is being used to generate the data in the nVision layout.

Well ,here is my first novice step of knowing nVision:

Go to the nVision report layout. In the tool bar, you will notice something called “nVision”. Click that.

Now you will notice a variety of options. One of them will be “Options”. Click that. A dialog box will open. One of the options will be “Show Report SQL”. Check this.

Now when you run the report using “Report Request”, every SQL that is used will be displayed one by one as the report is run and the data is being fetched.

I will be posting more of my basics findings in the subsequent postings. Also do remember that I got to tell you about “Add Attachment” feature. I will be visiting on this topic soon.

There are 2 ways I know that can directly take you to the page without displaying you the search page.

1. You can use a PS_INSTALLATION record on the “Search Record” field of the component properties. This works because INSTALLATION table doesn’t have any key structure. If there is no key structure defined, then obviously the user cannot search on anything.
2. The other way is to use Peoplecode in SearchInit event. You can use SetSearchDialogBehaviour(0). This works provided all the keys required for the component is given. If only partial keys are provided, then the search dialog is still displayed inspite of a code written in the SearchInit peoplecode event. On the other hand, if you want to force display the search page, even if all the keys are available for the component, then use SetSearchDialogBehaviour(1) instead.

It’s a common scenario that a group of 3 or 4 members in a team will be working on the same “project” present in the Application Designer. In such scenarios, it is possible for a team member to erase the changes made by another team member on an object. For e.g. if a team member “EMP0001″ is working on a SQL view or in a step in an Application Engine, made some changes to it and saved it, there is every possibility that another team member “EMP0002″ accessing the same object to which “EMP0001″ made the change and making some changes to it.

A classic scenario is “EMP0001″ adds a step called “STEP01″ into an application engine and saves it. Now the team member “EMP0002″ deletes that step. How will the team member EMP0001 ensure that his changes are tracked and are protected from any extraneous changes without his/her knowledge.

This is how we can do it. There is something called “Change Control” present in the App Designer. It will be available under the “Options” tab in the toolbar. If you click Change Control for the first time, you can see two options – View History, Administrator.

If you click on the Administrator, you will notice three options that are primarily checkboxes – Change Control Locking, Change Control History and Lock All Definitions. By Default they will be unchecked and the third option will be grayed out.

Check both the options namely Change Control Locking as well as Change Control History. Once you do it, the system will ask you to log out and login again. I assume here that your login has Adminstrator access. All other users of the App Designer should also logout and login again. If you logout and login again, you can see some extra symbols like “LOCK”, “UNLOCK” etc in the toolbar now.

Now whatever object that you access will be read only. If you want to make changes to the object, you will have to lock the object. This can be done by right-clicking on the object and selecting “Lock”. Once you have locked the object, you can make changes to the object and save it. During locking, a display window will be prompted asking you to enter your comments. Through this, a history is maintained as to who made changes to the object, when and why?

The best part is yet to come. Say if team member EMP0001 locked an object, if EMP0002 logs into the same project, he can see a “Lock” symbol on the object locked by EMP0001 along with the userid who locked it in brackets, which in our case is (EMP0001). So EMP0002 will know that EMP0001 is working on it. It is possible for EMP0002 to unlock the object locked by EMP0001. You can ask me whats the big idea. The idea is the changes are tracked now. Now EMP0002 can check the history log before he/she makes the changes.

In the classic scenario (App Engine Step example) that I explained before, EMP0001 can lock the project itself. So whatever changes EMP0002 makes to the project, actually wont get reflected into the project unless and until EMP0002 unlocks the project. So even if EMP0002 deletes the step STEP01 inserted by EMP0001, the system will allow him/her to delete. But actually the change will not be reflected in the project. Through this, the user EMP0001 protects his/her object from unnecessary encroachment.

I am sure you would have come across situations, where you will have to build views, records etc… However, let’s take this scenario, whereby you have two views ABC_VW and XYZ_VW… Now the XYZ_VW is used in the SQL written in ABC_VW. Obviously, this makes XYZ_VW as parent view and ABC_VW as child view. Even without building XYZ_VW, if you are going to save the SQL of ABC_VW, the App Designer sweetly saves the SQL. But the trouble comes later. When you are building the ABC_VW without building XYZ_VW, the App Designer faithfully writes an error in the log file quoting “Table or view doesn’t exist”. 

How will you tackle this problem? Some of you would think that you will ensure manually that XYZ_VW gets built before ABC_VW. But what if you are doing an upgrade and you want the system to take care of this automatically for all the parent and child views? Is there a way?

There is a way. Apparently, when you build records or views or even query views you will find a box called “Build Sequence No” in the record type tab. By default this is 1. So if we want the system to take care of build sequences automatically for our scenario, enter 1 for XYZ_VW and 2 for ABC_VW.

Now when you build the project containing these views, the system automatically builds XYZ_VW first followed by ABC_VW. This should clear off another misconception that the records get built in the order that is mentioned in the upgrade tab of the App Designer. If we see our example, ABC_VW gets listed first followed by XYZ_VW. But XYZ_VW gets built first. In the upgrade tab, every object is arranged in the alphabetical order and has no significance related to build sequences.

See you tomorrow~

In some rare occasions, we will come across a scenario, where we will have to write an AE Program that can work across platforms. Let’s say that the development environment is on SQL Server. You are writing your AE program with multiple steps. Let’s say in one of the steps, you are to fetch the system date. So you use a getdate() functionality to do the same. But what if the production environment runs on Oracle? You will have to obviously use “sysdate” in your SQL to fetch the date. How do you tackle this problem???

You may argue that you can use the MetaSQLs %CurrentDateIn() or %CurrentDateOut() (as the case may be), to get the dates. But what if there is a restriction that you are not to use any MetaSQLs at all.

You may argue that you can create two SQL objects one for SQL Server and the other for Oracle and use a %DbType Peoplecode. But again, you are using a MetaSQL here. Oopsy Daisies!!!

So here comes the importance of AE Section Properties.

Some of us tend to overlook what are present as “Properties” in an AE Section

If you carefully observe, there is something called “Platform” mentioned there. All you have to do is create a section “SECABC” with the platform as Microsoft. Now write your SQL Server specific SQL there. Now create another section with the same name “SECABC” but with the platform as Oracle. Now write your oracle specific SQLs here. That’s it. You are done.

Cool isn’t it?? I will see you with some more findings tomorrow.

Recently I found an interesting article on how to find out whether a particular database is on UNICODE or not. Here is it.

PeopleTools 8.46, 8.47, 8.48, and 8.49 – How Do You find out if Oracle Database has been installed as Unicode Unicode DB must be using a supported Oracle/PeopleSoft unicode character set. (UTF8) You can check what is the value by running the following query in the DB instance in question.

SQL> SELECT PARAMETER, VALUE from V$NLS_PARAMETERS where PARAMETER=’NLS_CHARACTERSET’;

PARAMETER VALUE
—————
NLS_CHARACTERSET UTF8

In addition, PeopleTools must be told that it is utilizing a UNICODE db and that is done by the setting of the UNICODE_ENABLED boolean flag on the PSSTATUS table. A value of 1 indicates this DB is to be treated by tools as a UNICODE DB.

SQL> SELECT UNICODE_ENABLED from PSSTATUS;
UNICODE_ENABLED
—————
1

When do you exactly use DoSave()?.. when do you DoSaveNow()?.. Well the difference is how soon you want to trigger the save processing event. Suppose, let’s say that you have assigned a value to a field in the row and the processing further down in the present peoplecode event depends upon the value stored in this field in database, then it makes sense to call the save processing events(SaveEdit, SavePreChange, Workflow & SavePostChange) immediately. In such a scenario, DoSaveNow() should be used which triggers the save processing events immediately and returns the control back to the present peoplecode event.

In certain cases, we can defer this triggering of save processing events till the present peoplecode event finishes. In such cases you can use DoSave().

I will come up with more examples sometime on DoSave() and DoSaveNow().

Recently I found an article in IT ToolBox that pointed out the advantages & disadvantages of Application Engine and SQR. I thought, publishing it here might help.

AE advantages:

• AE can be called by Peoplecode from a page.
• Built-in tools (meta-SQL) allow you to write SQL statements that refer to “all the fields of this record” or “join all the keys shared by these two tables.” This is handy when record layouts change; you don’t have to edit every select or insert.
• Built-in tools (temp tables) to run the same program many times on separate portions of your data. Processing takes less clock time.
• Repeatability and restartability – this takes a special style of programming. If you do all your processing in temp tables, not only should your SQL run faster, but your output doesn’t reach your permanent tables until you’re sure the program was successful. So, if it abends, you don’t have to worry about half-updated tables. Using state records for your internal variables aids debugging abends and restarting in mid-stream after an abend.
• You can write different versions of a section of code, and AE will choose among them based on current date, type of database, or market. This feature is obviously for developers at Peoplesoft Corp to ship the same products worldwide, but it could be useful for us.
• Built-in debugger.
• Easy communication with the process monitor using the message catalog.

AE disadvantages:

• Programming technique is very restrictive. Think of Peoplecode, where you’re only supposed to do certain things in FieldEdit and other things in FieldChange. And it’s easy to access fields in the record of the Peoplecode, harder to access fields in other records or rows of the buffer, still harder to access data outside the buffer. AE can be worse.
• You can only view one SQL statement or one block of Peoplecode at a time. It’s like programming or debugging through a keyhole. And be careful printing your program – the print routine leaves things out.
• Because it’s awkward to use global variables and tempting to use meta-SQL, you’ll want to do as much as possible in each SQL statement. If you do, your SQL will get very complex, hard to understand and maintain, and potentially buggy. We don’t always remember all the effects of a join – not only do we bring in columns from other tables, but we also restrict our selection and/or multiply our rows based on the matches (0, 1, or many) from those other tables.
• Awkward control structures make it hard to follow program logic. 

SQR advantages:

• Wonderful printing functions and barely adequate file I/O – both absent from AE.
• Easy to use internal variables and even arrays.
• The option to choose our own editor, source control system, etc. Control over the format of our code (AE editors tend to rearrange things).
• If SQR runs on the client or the command line, it can communicate with the user in free form, including taking user input.

SQR disadvantages:

• No debugger.
• A program must be completely within its source file. Include files are merged in by the compiler. AE can call subroutines within other programs.