Column default values in PeopleTools 8.48 DDL

PeopleSoft’s Mobile Synchronization Framework is designed to support off-line access to a subset of the PeopleSoft application. Application Designer has been able to generate database triggers to maintain Timestamp and System ID fields on tables that support synchronization of mobile clients since PeopleTools 8.44. In 8.48 it is now possible to set column defaults on character and numeric fields via Application Designer. However, this has potential for much wider application in PeopleSoft applications.

There are two new checkboxes on the record properties dialogue box.

If the User -> Server box is checked, Application Designer will add column defaults to the fields in the generated create and alter table DDL. As an example, I created the following record in Application Designer.



And this was the DDL that Application Designer generated.

CREATE TABLE PS_DMK (
   EMPLID VARCHAR2(11)  DEFAULT ' ' NOT NULL,
   EFFDT DATE,
   SEX VARCHAR2(1)  DEFAULT 'U' NOT NULL,
   ACTION_DT DATE  NOT NULL,
   ARRIVAL_TIME DATE  NOT NULL,
   ACTION_DTTM DATE,
   AA_PLAN_YR SMALLINT  DEFAULT 42 NOT NULL,
   ACCRUED_AMOUNT DECIMAL(15, 2)  DEFAULT 0 NOT NULL) 
… 

• All of the numeric and character columns now have default values, but none of the date columns have defaults.
• If a literal value is specified in Application Designer it is carried forward to the DDL, otherwise a character field defaults to a single space and a numeric field to zero
• Whether a field is required or not does not affect the default value,
It is a pity that the system variables to specify current date or time do not get converted to SYSDATE in the DDL, thus

…
   ACTION_DT DATE  DEFAULT TRUNC(SYSDATE) NOT NULL,
   ARRIVAL_TIME DATE DEFAULT SYSDATE NOT NULL,
…

I assume that this is because it is platform specific, but then so is the rest of the field list.

There is another problem to using the Up Sync check box. When checked, you are prevented you from making a field required. The unchecked box is greyed out, but fields that are already required remain so.

I think that the ability to specify column defaults could have advantages during batch processing. All numeric and character columns in PeopleSoft (with minor exceptions) are NOT NULL in the database. Thus, if a process is inserting a row into that table, it must provide a value for every mandatory column. Otherwise, you generate an Oracle error.

ORA-01400: cannot insert NULL into ("SYSADM"."table name"."field name")

Specifying a default value will prevent this error. It would also save developers from having to add this default values to their code, thus simplifying development and maintenance.

Many batch processes in PeopleSoft process working storage or reporting tables. Often they insert rows, filling in key and some other columns, and then update other columns later on. If these columns have defaults they do not need to be referenced in the insert statement.

When a new column is added to a table, it is necessary to find every place where a row is inserted, and add a value. Specifying a column default would save adding a space or zero to those inserts.