10/12/11

DATE and DATETIME, LONG and LOB fields in PeopleSoft and Oracle database

In PeopleTools 8.49 and earlier, Datetime and time fields are stored as DATE type in Oracle database. For example, you could see a DATETIME or a TIME field in Application designer might show as a DATE field in the Oracle database. Similar issues could be seen with LONG, CLOB and LOB fields as well.


From PeopleSoft PeopleTools 8.50 and higher, the TIMESTAMP data type is now supported for TIME and DATETIME field types. These data type changes are mandatory, and the DATE data type will no longer be used for the  DATETIME and DATE fields.


Updating the Database for Timestamp:
This is a separate step performed during an application or tools upgrade. This step invokes a SQL statement called UPGDBOPTIONS_ENABLETIMESTAMP.SQL from PS_HOME/scripts folder. This DMS script updates the database to indicate that the new TIMESTAMP data types are now enabled. If the upgrade is performed through PeopleSoft Change Assistant(as it should be), the step is displayed and can be run only if the upgrade is from PeopleSoft PeopleTools 8.49 or earlier.


What does this step do?
Why the DATE, TIME and DATETIME formats chosen in PeopleSoft application designer show as DATE field in the database and how is this controlled?
This feature is controlled by the DATABASE_OPTIONS field in  PSSTATUS record.
DATABASE_OPTIONS field can store four different values:
  1. 0 - PeopleSoft is using the old datatypes(Long fields and Legacy Unicode implementation).
  2. 2 - PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LOBs and CLS Unicode implementation).
  3. 32 - PeopleSoft is using old datatypes, (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
  4. 34 - PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
PeopleTools Version and Features supported:
  1. Datatypes supported with PT8.48 - LOBs and CLS Unicode implementation
  2. Datatypes supported with PT8.50 - TIMESTAMP enabled
Depending upon which version of PeopleTools is installed and what features are enabled priorly, an appropriate number from 0, 2, 32 and 34 will be updated while running this step.

1 comment:

  1. Hi im eva peoplesoft consultant. I was just browsing blogs there I found your blog is interesting.. thanks for posting… keep on posting mypeoplesoft

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month