Use Dynamic SQL for Prompts - SqlText

Normally you can not use dynamically generated SQL at runtime as your prompt record. You can use %EDITTABLE to specify the prompt table you want to use, but it has to be predefined. There is this little known property SqlText for Field Class, which allows you to do exactly that.

Here is the description and sample code taken from peoplebooks.

This property is valid only for fields that have a dynamic view as their prompt record. If you set SqlText to a non-null value, that text is used instead of the dynamic view's normal text used for prompting.

Suppose you wanted to have a different prompt table depending on the settings of other fields in the row. Normally you could use %EDITTABLE to dynamically specify the prompt table you want. However in this case there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in PeopleCode based on what the user enters.

If you use a dynamic view as the prompt table, and have the dynamic view contain a SQL object that is updated from PeopleCode, you could achieve this functionality. However, a SQL object is a shared object, so if multiple users used the same page, they overwrite each other's settings and the SQL object contains the SQL for the most recent user. Similarly if a single user had multiple rows on a page, the SQL object is valid only for the most recent row. This means if the user went to another row and did a prompt, they would get the wrong values again.

The purpose of this property is to enable you to specify the generated SQL text independently for each occurrence in each transaction. It enables you to override the text of a dynamic view being used as a prompt table on a field by field basis.

It is up to the developer to verify that the text specified for this property is valid, that is, that it selects the correct number of fields for the record definition, and so on.

This property is read-write.

Local string &SQLSTRING;

Function set_jrnl_id_prompt();
&SQLSTRING = "SELECT DISTINCT JOURNAL_ID, BUSINESS_UNIT_IU, JOURNAL_DATE, LEDGER_GROUP, SOURCE, SYSTEM_SOURCE, PROC_PART_ID, JRNL_HDR_STATUS, DESCR FROM PS_JRNL_HEADER WHERE JRNL_HDR_STATUS IN ('N','E','V')"
If All(JRNL_EDIT_REQ.BUSINESS_UNIT) Then
&SQLSTRING = &SQLSTRING | " AND BUSINESS_UNIT_IU='" | JRNL_EDIT_REQ.BUSINESS_UNIT | "'"
End-If;
If All(JRNL_EDIT_REQ.LEDGER_GROUP) Then
&SQLSTRING = &SQLSTRING | " AND LEDGER_GROUP='" | JRNL_EDIT_REQ.LEDGER_GROUP | "'"
End-If;
If All(JRNL_EDIT_REQ.SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SOURCE='" | JRNL_EDIT_REQ.SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.SYSTEM_SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SYSTEM_SOURCE='" | JRNL_EDIT_REQ.SYSTEM_SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.PROC_PART_ID) Then
&SQLSTRING = &SQLSTRING | " AND PROC_PART_ID='" | JRNL_EDIT_REQ.PROC_PART_ID | "'"
End-If;
GetRecord().GetField(Field.JOURNAL_ID_FROM).SqlText = &SQLSTRING;
GetRecord().GetField(Field.JOURNAL_ID_TO).SqlText = &SQLSTRING;
End-Function;

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;

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.

• Enterprise Tools : Spreadsheet of All Tools Solutions
  • Core Tools : Application Engine, Cobol, Workflow, PIA, Enterprise Portal
  • App Development : Application Designer, LDAP, Security, Peoplecode, Panel Processor, Mobile
  • Install and Upgrade : Change Assistant, Install, Upgrade, Certification
  • Reporting Tools : Query, Tree Manager, Crystal, nVision, Report distribution, Ren Server, Process scheduler, ACE, Cube Manager, BAM
  • Server Tools : Application Server, Web Server, Tuxedo, Verity
  • Performance : Performance, Performance Monitor
  • Integration Tools : Integration Broker, Component Interface, Business Interlink, Multi Channel Framework
  • Updates and Fixes
  • Documentation and Training Tools : User Productivity Kit, PeopleBooks, End User Training
• Financial Management
• Human Capital Management
• Campus Solutions
• Customer Relationship Management
• Enterprise Learning Management
• Enterprise Performance Management

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.

Search Customer Connection Effectively

Type following Keyword : SPOTLIGHT and any other keyword to find most important resolutions. For e.g. To search for Crystal Relate issues, search for
SPOTLIGHT crystal

These resolutions are specially created to address specific issues. I just learned it today in Advisor webcast : PeopleTools 8.48 : Integration broker overview.

Impact of U.S. Daylight Saving Time Changes in 2007 on peoplesoft

US DST changes may require updating the JRE used in Web/App/Batch servers. More information can be found in these links

http://java.sun.com/developer/technicalArticles/In...

http://www.hp.com/products1/unix/java/DST-US.html

Do you think it will also impact peopletools release as well. I do not see any documents on this on customer connection. Please share your thoughts on this issue.

I think this PeopleSoft Page and associated data stored in PSTIMEZONE and PSDSTTIME must be updated. I have seen one other record PSTZOFFSET which may need to be updated, if you are using this in your environment. The data in this table is not required by peoplesoft and can be used in query to simplify the access to timezone data. This makes the time zone information available in a format that can be easily accessed with SQL. (Exception : May be used by Time and Labor. Check your installation). You may also want to update only for the period 2007 and onwards after applying the updated timezone information.

PeopleTools -> Utilities -> International -> TimeZones and Hit the button Generate Query Offsets and enter the start and end date for which you want to generate the information. This will delete the old data and enter the new data. You may want to update the data only for the year 2007 onwards only.

Here is a link to peoplebooks -> Unserstanding Time Zones

Navigation for PeopleTools 8.4x

PeopleTools -> Utilities -> International -> Time zones
Menu: Utilities
Component: TIMEZONEPNLGRP
Page: TIMEZONEDATA, DSTDATA
Record: PSTIMEZONE, PSDSTTIME

Here is the SQL to update the record.

INSERT INTO PSDSTTIME VALUES ( '2FirstSunNov', 'N', '11', 1, '0', 2, 0, 'First Sunday in Nov, 2:00am' )
INSERT INTO PSDSTTIME VALUES ( '22ndSunMar', 'N', '3', 2, '0', 2, 0, 'Second Sunday in March, 2:00am' )
UPDATE PSTIMEZONE SET DSTSTART='22ndSunMar' WHERE TIMEZONE IN ('AKST','CST','EST','MST','PST');
UPDATE PSTIMEZONE SET DSTEND='2FirstSunNov' WHERE TIMEZONE IN ('AKST','CST','EST','MST','PST');

Define Optional Query Prompt/Criteria in PeopleSoft Query

To create a optional query criteria, in Peoplesoft Query create a prompt as expression as shown below
:1 OR :1 = ' '
Note their is a single space between two quotes. This will make query run with or without prompt. User can either provide the prompt value to return results for a specific value or leave it blank to return rows for all values.