FYI Changes to PTools v7.03 and Oracle Databases

[Bon Conway <bonway@USA.NET>]

We're going through a "minor" upgrade from PeopleTools 7.01
up to 7.03.  We did not expect to find any major differences,
as it was mostly a tools upgrade.  However, we did find
that a number of the homegrown SQRs were terminating abnormally
without much more of an explanation that Oracle was having
a problem interpretting our dates.

I distributed the following notes to our development team members
and I include it here for your review in hope that I can save
you a step or two if/when you get to the v7.03 platform.

-BConway
Conway Consulting Services, Inc.

TO:  PeopleSoft Development Team Members
RE:  FYI SQR Changes With PTools 7.03 Upgrade


With the new release of PeopleTools 7.03, we have
noticed some changes made to the SETENV.SQC copybook
member.  Among the modifications was a somewhat
critical change to the date and time formats used
when operating within an Oracle database platform.


The old #define commands were set as follows:
  #define Native-DateTime DD-MON-YYYY_HH:MI:SS_AM
  #define Native-Date     DD-MON-YYYY
  #define Native-Time     HH:MI:SS_AM


The new #define commands are set as follows:
  #define Native-DateTime    YYYY-MM-DD_HH:MI:SS
  #define Native-Date        YYYY-MM-DD
  #define Native-DateTime24  YYYY-MM-DD_HH24:MI:SS
  #define Native-Time        HH24:MI:SS


How does this impact your program?
  - Using dates in your join conditions
  - Using dates in SQL Insert and Update commands
  - Using substring variable manipulation

If you've coded your program to "expect" an Oracle
formatted date - under any circumstances - chances
are that you'll have to revisit your code to make
it function properly with the new set of tools.

As you may know, PeopleSoft will perform an "Alter
Session" command if you invoke the standard date
initialization routines.  Traditionally, this meant
that it altered the NLS_DATE_FORMAT='DD-MON-YYYY' to
ensure we would be working with the century portion
of the date fields.  However, this new release will
alter the NLS_DATE_FORMAT parameter to appear as
'YYYY-MM-DD'.

If you attempt to use any date pulled out of the database
in a subsequent join condition or for an insert or update,
then you'll receive an error.  Oracle will not be able
to recognize the date format.

PeopleSoft wants us to use their DateIn and DateOut
logicals when working with dates.
  {DateIn-Prefix}  and {DateIn-Suffix}
  {DateOut-Prefix} and {DateOut-Suffix}
When you look at the SETENV.SQC routine and see how
these are defined within an Oracle platform, you'll
find that these logicals are simply using the Oracle
"TO_DATE" and "TO_CHAR" functions.

Personally, I've always tried to code TO_DATE functions
around all of the dates I use under the situations we've
described here.  Whenever I pull a date out of the
database, I'll toss it through a PeopleSoft reformatting
routine to store it in YYYYMMDD format.  This way, I
know exactly what I'm dealing with and I can code all
of my TO_DATE commands such as: to_date($effdt,'YYYYMMDD').

Although this approach saved me from having to do alot of
re-work of my programs (I wasn't entirely unscathed!)
with this new tools release, by hardcoding TO_DATE into
my programs, I wasn't being very database-generic.
Therefore, I will adopt the recommended use of these
logicals in all of my future SQR development efforts.

By the way, I don't think we need to bother with
the DateOut prefix and suffix so much, since we are
getting the benefits of the ALTER SESSION instruction
which means that any date we pull out of the database
will be in YYYY-MM-DD format.

Here are some examples of using these logicals in
basic join conditions and insert/update commands.


!-------------------
! SQL INSERT COMMAND
!-------------------

begin-sql on-error=err-insert
insert into ps_pi_emplid_tbl
(emplid,
empl_rcd#,
pi_system_id,
effdt,
pi_emplid)
values
($emplid_in,
#empl_rcd_in,
$pi_system_id_in,
{DateIn-Prefix}$effdt_in{DateIn-Suffix},
$pi_emplid_in)
end-sql


!-------------------
! SQL SELECT COMMAND
!-------------------

begin-select
pers.per_type
J.paygroup
J.company
J.deptid
J.eeo_class
  let $per_type = &pers.per_type
  let $Paygroup = &J.paygroup
  let $Company = &J.company
  let $EMP_Deptid = &J.deptid
  let $Labor_Type = &J.eeo_class
  let $JobFound = 'Y'
from ps_job j, ps_personal_data pers
where pers.emplid = $emplid
and j.emplid = $Emplid
and j.empl_rcd# = #empl_rcd
and j.effdt =
  (select max(j2.effdt) from ps_job j2
   where j2.emplid = j.emplid
   and j2.empl_rcd# = j.empl_rcd#
   and j2.effdt <= {DateIn-Prefix}$period_prcs_dt{DateIn-Suffix})
AND J.EFFSEQ =
      (SELECT MAX(j3.EFFSEQ)
       FROM PS_JOB j3
       WHERE j3.EMPLID = J.EMPLID
         AND j3.EMPL_RCD# = J.EMPL_RCD#
         AND j3.EFFDT = J.EFFDT)
end-select


!-------------------
! SQL SELECT COMMAND
!-------------------

begin-select
p.emplid        (+1,{c1})
p.name          (0,{c2})
{DateOut-Prefix}p.orig_hire_dt{DateOut-Suffix} &p.orig_hire_dt (0,{c3})
  let $emplid = &p.emplid
  let $name = &p.name
  let $orig_hire_dt = &p.orig_hire_dt
  do Convert-To-DTU-Date(&p.orig_hire_dt, $orig_hire_dt_dtu)
  do dtu-add-months($orig_hire_dt_dtu, -1, $wrkdt1_dtu)
  do Convert-From-DTU-Date($wrkdt1_dtu, $orig_hire_dt_Back1Mo)
  print $orig_hire_dt_Back1Mo (0,{c4})
from ps_personal_data p
where p.emplid like '2537%'
end-select



!
! Set up a DEFAULT DATE to be used if necessary.
!
  let $defdate = '19800101'
  do Format-DateTime($defdate, $defdate_ntv, {DEFCMP}, '', 'native')
  let $Default_Date = $defdate_ntv


!
! Sample usage of substring commands, working off a date
! format of YYYYMMDD.
!

begin-select
p.emplid
p.name
p.orig_hire_dt
  let $orig_hire_dt = &p.orig_hire_dt
  do Format-DateTime($orig_hire_dt, $orig_hire_dt_cymd, {DEFCMP}, '', '')
  let $orig_hire_CCYY = substr($orig_hire_dt_cymd,1,4)
  let $orig_hire_MM   = substr($orig_hire_dt_cymd,5,2)
  let $orig_hire_DD   = substr($orig_hire_dt_cymd,7,2)
from ps_personal_data p
where p.emplid like '2537%'
end-select



____________________________________________________________________
Get free e-mail and a permanent address at http://www.netaddress.com/?N=1