Release 3.00
Feb 26, 2004
|
Sybernet / Sybernet Standards
Release 3.00 Feb 26, 2004 |
|
This paper will eventually become the Sybernet Standards Document. For the moment it addresses those standards that are important in our PeopleSoft move from Sybase to Oracle. Some things are not new (like the standard heading), but are emphasized again because of their importance. Some things are new (like W3C compliance) which need to be addressed or reviewed.
| Standard | Description |
|---|---|
STANDARD HEADER |
All procedures should contain the standard PeopleSoft header. The header includes the current version number, description, and author. It also includes a one-line description of every change made to this procedure. |
SCHEMA NAME |
Avoid explicit references to the current schema. |
SP_AUDIT |
All registered procedure should call HTTP.SP_AUDIT. |
SP_HTML_FILTER |
Use if you can. |
TRANSLATOR |
You should try to rewrite how the translator handles cursors. |
W3C |
All stored procedures should be W3C compliant. |
PACKAGES |
Let's discuss. |
PEOPLETOOLS |
Some generic routines you may find useful. |
All procedures should contain the standard PeopleSoft header. The header includes the current version number, description, and author. It also includes a one-line description of every change made to this procedure.
Every change (no matter how trivial) should contain a one line comment in the standard header. Now that we are moving to Oracle, this is even more important since the creation date in Oracle does not always reflect the last date this procedure was modified. For the move to Oracle, I am including "Move to Oracle" in the procedure source. The version number is 8.30. This is important if you use HTTP.MYSELF.VERSION to retrieve the version number.
The following example illustrates the PeopleSoft Standard Header:
/****************************************************************************** ** ** ** ** Version: 8.30 ** ** ееее еееее еее ееее е еееее ееее еее еееее еееее ** е е е е е е е е е е е е е е ** ееее еее е е ееее е еее еее е е ееее е ** е е е е е е е е е е е е ** е еееее еее е еееее еееее ееее еее е е ** ** ** Title: "Pay Calendar" ** ** Description: Interogate and set flags in calendar ** ** Author: Denis D. Workman ** ** ** *******************************************************************************/ /* */ /* PATCH 001 Initial clean release (01/10/2000) */ /* PATCH 002 Add PAY_OFF_CYCLE (06/22/2000) */ /* PATCH 003 What if all the paygroups are not present? (01/08/2001) */ /* PATCH 004 Grant read-only access to E23720 and E24821 (04/01/2002) */ /* PATCH 005 Show error messages (06/25/2002) */ /* PATCH 006 Include EMPLID and Message Data for PATCH 005 (05/27/2003) */ /* PATCH 007 Move to Oracle (02/24/2004) */ /* */ /******************************************************************************/
You should never reference the current schema name. Doing so makes it impossible to copy this database to another instance. In most cases, all this means is that you omit the reference to the schema. The example below illustrates how to retrieve the current schema name dynamically.
The MYSELF package includes the subprogram MYSELF which returns your procedure name prepended by the current schema name.
The following example illustrates how to reference a schema name dynamically:
SELECT HTTP.DB_NAME() FROM DUAL
All registered procedure should call HTTP.SP_AUDIT. This allows us to track the usage of your stored procedure. Sometimes procedures can deprecate themselves. This happens when no one bothers to use it. The audit file will reveal such conditions.
Do not call SP_AUDIT if your procedure also uses HTTP.SP_HTML_FRAME. SP_HTML_FRAME calls SP_AUDIT automatically for your procedure.
The following example illustrates how to call SP_AUDIT:
HTTP.SP_AUDIT(HTTP.MYSELF.NAME);
Not all stored procedures can use SP_HTML_FILTER, but many can. If your procedure creates a form screen and is asking for generic information like EMPLID or DEPTID, you should change it to use SP_HTML_FILTER. In almost all cases, you should include the OFFLINE option.
The Sybase version of SP_HTML_FILTER allowed BALANCE_ID to be TRUE or FALSE or a real balance ID (FY or CY). That was pretty clumsy. The DELTEK version of SP_HTML_FILTER allows you to specify EFFECT_DT (TRUE/FALSE) and you could specify the actual EFFDT as a date value. The latter caused the list of orgs, groups, and divisions to reflect the specified date.
The PeopleSoft version of SP_HTML_FILTER tries to address both of these features. The convention now is to specify BALANCE_ID or EFFDT as TRUE or FALSE (consistent with everything else) and if you want to specify a default value, you would include that too surrounded by parantheses:
BALANCE_ID => 'FALSE (FY)'
EFFDT => 'TRUE (Jan 1, 2000)'
All TRUE / FALSE parameters passed to SP_HTML_FILTER can now specify a default value using this syntax.
The following parameter names recognized by Sybase are now deprecated in Oracle:
In addition, if there are any procedures still calling SP_NUM_OBJECTS, those procedures should omit this reference. In this case, just call SP_HTML_FILTER directly.
The following example illustrates how to call SP_HTML_FILTER:
sp_html_filter
(
PROCNAME => HTTP.MYSELF.NAME -- Name of your procedure (required)
, VERSION => HTTP.MYSELF.VERSION -- Version (required)
, TITLE => HTTP.MYSELF.TITLE -- Title (required)
, BUTTON => 'REPORT' -- Where to go (required)
, SORT => 'ORG' -- ORG / DEPARTMENT / GROUP / DIVISION
, TARGET => 'CENTER' -- Name of your frame
, SECURITYTYPE => 'PUBLIC' -- PRIVATE / SECURED / PUBLIC
, STYLE => '107' -- Default datetime style
, onLoad => NULL -- Javascript:onLoad
, COMPANY => 'FALSE' -- TRUE / FALSE
, EMPLID => 'FALSE' -- TRUE / FALSE
, DEPTID => 'FALSE' -- TRUE / FALSE
, YEAR_ACQUIRED => 'FALSE' -- TRUE / FALSE
, HRS_ADJUST_UNPROC => 'FALSE' -- TRUE / FALSE
, PLAN_TYPE => 'FALSE' -- TRUE / FALSE
, ACCOMPLISHMENT => 'FALSE' -- TRUE / FALSE
, SCHOOL_CODE => 'FALSE' -- TRUE / FALSE
, DEPARTMENT => 'FALSE' -- TRUE / FALSE
, "GROUP" => 'FALSE' -- TRUE / FALSE
, DIVISION => 'FALSE' -- TRUE / FALSE
, CONTEXTTYPE => 'FALSE' -- TRUE / FALSE
, CONTENTTYPE => 'FALSE' -- TRUE / FALSE
, RUN_ID => 'FALSE' -- TRUE / FALSE
, PERIOD_END_DT => 'FALSE' -- TRUE / FALSE
, SUB_PERIOD_END_DT => 'FALSE' -- TRUE / FALSE
, START_DATE => 'FALSE' -- TRUE / FALSE
, STOP_DATE => 'FALSE' -- TRUE / FALSE
, EFFDT => 'FALSE' -- TRUE / FALSE (EFFDT)
, DEVICENAME => 'FALSE' -- TRUE / FALSE
, BALANCE_YEAR => 'FALSE' -- TRUE / FALSE
, BALANCE_QTR => 'FALSE' -- TRUE / FALSE
, BALANCE_PERIOD => 'FALSE' -- TRUE / FALSE
, BALANCE_ID => 'FALSE' -- TRUE / FALSE (BALANCE_ID)
, PAYGROUP => 'FALSE' -- TRUE / FALSE
, OFFLINE => 'FALSE' -- TRUE / FALSE
, POSTSCRIPT => 'FALSE' -- TRUE / FALSE
, SHELLSCRIPT => NULL -- shell script
, CONFIGURATION => NULL -- configuration file (-f).
, BORDER => '1' -- Table Border
, BORDERCOLOR => 'black' -- Table BorderColor
, CELLPADDING => '2' -- Table CellPadding
, CELLSPACING => '5' -- Table CellSpacing
, BGCOLOR => NULL -- Table bgColor
, WIDTH => NULL -- Table Width
) ;
The Sybase to Oracle translator makes Sybase cursors look like Sybase cursors. This is not the Oracle way of doing things. Many of the Sybase procedures in Sybase declare their cursors conditionally because it needs the same cursor for each order by clause. The order by clause can be specified dynamically in Oracle for the same cursor declaration.
When creating a drop-down list in Sybase, you can usually accomplish this with three simple select statements. When passed through the translator, you end up with three cursors because the translator doesn't know what you are trying to accomplish. You do, and one cursor in Oracle can do all of this work. You can also make SP_HTML_INPUT do this work.
The following example illustrates how to create a drop-down list in Oracle:
http.sp_html_input
(
type => 'SELECT'
, name => 'DEPTID'
, value => 'SELECT DEPTID FROM PS_DEPT_TBL_CURRENT'
, checked => '023'
) ;
All stored procedures should be W3C compliant. This means that all output needs to be sent through the Sybernet HTML validator, and every HTML document should include a DOCTYPE. Without a DOCTYPE, your procedure is not W3C compliant.
I would like to discuss with you why you want to create a package. Packages are great, and the purpose here is not to discourage you from making them. Instead, I just want to know why you think you need one. Maybe your routines are generic enough to include in the PEOPLETOOLS package, or maybe they already do. No reason to reinvent the wheel, you see.
The PEOPLETOOLS package is new. It contains constants and subprograms that can make your coding easier; for example, I can never remember if PTO is 50 or 51. Now it's a constant that can be referenced as PEOPLETOOLS.PTO. All I ask is that you become familiar with this pacakge in case there is something you can use or in case there is something you would like to add. The PEOPLETOOLS documentation is located in the usual place.