Tuesday, December 19, 2006

Data being added conflicts with existing data

If users are encountering this error, chances are that your sequence no. table is out of synch with your main transaction table. For e.g. this can happen while hiring a new employee and a new employee id needs to be generated. PeopleSoft delivered code uses SQLExec approach to

  • First Update the Sequence to next number
  • Selecting the sequence from the table
  • Assign this to page field

This method works fine, except where you are calling this function conditionally in Saveprechange event and Subsequent workflow or savepostchange event errors out and PeopleSoft issues rollback and all changes to database are rolled back. This results in Sequence no table not being updated to next value. However the actual page field is updated with this new value. If the user is able to correct the error and save the page again, the peoplecode to update the sequence does not fire due to it's conditional execution. (for e.g. EMPLID = 'NEW' as emplid is already equal to a new value). Remember, error does not clear the page values only rolls back database changes. This results in your emplid in person table higher than last employee id in installation table. Now when the users trying to hire new employees it gives the above error.

The best way to avoid this is to use GetNextNumberWithGaps peoplecode function, which automatically increments the counter and commits the value to database. This also improves the database locking.

If for some reason you can not use this function, you can still use GetNextNumber or SQLExec and make sure that this gets executed all the time, even after the error has occurred in Workflow or savepostchange event. Instead of evaluating the current value of emplid = 'NEW' , store the value when the user enters the component in a component variable and use that variable for comparison. This will ensure that your update logic is firing all the time.

Here is the Old PeopleCode

Function assign_employee_id(&EMPLID);
&LENGTH = INSTALLATION.EMPLID_LENGTH;
&CHECK = Rept("9", &LENGTH);
SQLExec("Update PS_INSTALLATION Set EMPLID_LAST_EMPL = EMPLID_LAST_EMPL + 1");
SQLExec("Select EMPLID_LAST_EMPL From PS_INSTALLATION", &EMPLID);
If Value(&EMPLID) > Value(&CHECK) Then
SQLExec("Update PS_INSTALLATION Set EMPLID_LAST_EMPL = EMPLID_LAST_EMPL - 1");
Error MsgGet(1000, 74, "The maximum Employee ID of %1 has been assigned.", &CHECK);
Else
&EMPLID = Rept("0", &LENGTH - Len(&EMPLID)) | &EMPLID;
End-If;
End-Function;

Here is the new PeopleCode

Function assign_employee_id(&EMPLID);
&LENGTH = INSTALLATION.EMPLID_LENGTH;
&CHECK = Rept("9", &LENGTH);
&EMPLID = GetNextNumberWithGaps(INSTALLATION.EMPLID_LAST_EMPL,&CHECK,1);
Evaluate &EMPLID
When = %GetNextNumber_SQLFailure
/* Do Error processing */
break;
When = %GetNextNumber_TooBig
Error MsgGet(1000, 74, "The maximum Employee ID of %1 has been assigned.", &CHECK);
break;
When = %GetNextNumber_NotFound
/* Do Error processing */
break;
When-other
&EMPLID = Rept("0", &LENGTH - Len(&EMPLID)) | &EMPLID;
End-Evaluate;
End-Function;

Friday, December 15, 2006

Search PeopleSoft Customer Connection for Solutions to most common issues

PeopleSoft customer connection website has been recently enhanced with a new feature : Knowledge browser. This will allow you to look for most common resolutions easily. You can also download a spreadsheet containing solutions by product area.

Navigation : Support -> Online Support -> Knowledge Browser -> Peoplesoft Enterprise

Direct URL : http://urlic.com/knowledgebrowser (Enter your customer connection Userid and password). Following High level topics are available.

Monday, December 11, 2006

Download Oracle OpenWorld Presentations for Peoplesoft

You can download Oracle OpenWorld Presentations at http://urlic.com/openworld2006

Enter the following in the popup dialog box.
Username: cboracle
Password: oraclec6

There are lot of useful presentations on PeopleSoft Upgrade. To search for peopleSoft technology related presentations, use following
Search In: Sessions
Key Topics: All
Track: Application Technology
Special Interests: PeopleSoft Enterprise

or go to this URL at PeopleSoft Technology Presentations - Oracle Open World 2006

This returns total of 59 records.