|
| |
NOTE - Use your Browsers BACK Button to return to prior page or
CLICK here.
| PSAUDIT Analyzer - Determine
Field Level Audits |
 |
Here's a simple utility listing the PSAUDIT attributes required for field
level audits (ADD, CHG, DEL, KEY). Simply enter the RECNAME at the prompt. Sample output
and source code are included below. There's also some sample code showing how to
incorporate field level audits into a conversion or interface program. And of course more
pictures - tortoises and kangaroos! |
NOTE: You can download TDAUDIT.SQR along with the supporting custom SQC
files - TDSUB.SQC (Subrecord Analyzer) and TDFUNC.SQC (Library). The
method used to isolate the PSAUDIT bit settings is contained in the BOOL-Func
routine. To some this approach may appear a 'bit' different than the usual method
which appears in may PeopleSoft utility reports.
| Sample Output Listing - Condensed
for web page |
Report ID: TDAUDIT PSAUDIT ANALYZER Page No. 1
Run Date 06/27/2001
Run Time 10:22:12
======================================================================
Recname Fieldname Type Key? Add? Chg? Del? Key Mapping
======================================================================
JOB EMPLID Char Y KEY1
JOB EMPL_RCD# Nbr Y KEY2
JOB EFFDT Date Y Y Y Y KEY3
JOB EFFSEQ Nbr Y KEY4
JOB DEPTID Char Y Y Y
JOB JOBCODE Char Y Y
JOB POSITION_NBR Char
JOB POSITION_OVERRIDE Char
JOB POSN_CHANGE_RECORD Char
JOB EMPL_STATUS Char Y Y Y
JOB ACTION Char Y Y Y
JOB ACTION_DT Date Y Y Y
JOB ACTION_REASON Char Y Y Y
JOB LOCATION Char Y Y
JOB TAX_LOCATION_CD Char
JOB JOB_ENTRY_DT Date
JOB DEPT_ENTRY_DT Date Y Y Y
JOB POSITION_ENTRY_DT Date
JOB SHIFT Char Y Y
JOB REG_TEMP Char Y Y
JOB FULL_PART_TIME Char Y Y
JOB COMPANY Char Y Y Y
JOB PAYGROUP Char Y Y Y
JOB PIN_NUM Nbr
JOB PAY_BASIS_NUM Nbr
JOB BAS_GROUP_ID Char
JOB ELIG_CONFIG1 Char
JOB ELIG_CONFIG2 Char
JOB ELIG_CONFIG3 Char
JOB ELIG_CONFIG4 Char
JOB ELIG_CONFIG5 Char
JOB ELIG_CONFIG6 Char
JOB ELIG_CONFIG7 Char
JOB ELIG_CONFIG8 Char
JOB ELIG_CONFIG9 Char
JOB BEN_STATUS Char
JOB BAS_ACTION Char
JOB COBRA_ACTION Char
JOB EMPL_TYPE Char Y Y Y
JOB HOLIDAY_SCHEDULE Char
JOB STD_HOURS Nbr
JOB OFFICER_CD Char
JOB EMPL_CLASS Char
JOB SAL_ADMIN_PLAN Char
JOB GRADE Char
======================================================================
PSAUDIT Analyzer by Tony DeLia tdelia@erols.com
|
| TDAUDIT.SQR - Source Code |
!**********************************************************************
!* *
!* MODULE: TDAUDIT.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 10/10/2000. *
!* SYSTEM: TD SQR UTILITY SERIES. *
!* DESC: PSAUDIT ANALYZER. *
!* *
!**********************************************************************
!* *
!* TABLES: psrecfield - Select *
!* psdbfield - Select *
!* *
!**********************************************************************
!* *
!* INPUT: A) Enter RECNAME: *
!* *
!* The PeopleSoft Record Definition. *
!* *
!* B) Enter Another Record? (Y/N): *
!* *
!* Repeats process starting with Step A. *
!* *
!**********************************************************************
!* *
!* LEGAL: CONFIDENTIALITY INFORMATION. *
!* *
!* This module is the original work of Tony DeLia. It *
!* can be considered ShareWare under the following *
!* conditions. *
!* *
!* A - The author's name (Tony DeLia) remains on any *
!* and all versions of this module. *
!* B - Any modifications must be clearly identified. *
!* C - A "vanilla" copy of this module must be kept *
!* alongside any revised versions. *
!* *
!**********************************************************************
!* *
!* WEBSITE: http://www.sqrtools.com *
!* *
!* Questions/Comments: tdelia@erols.com *
!* *
!**********************************************************************
!* REVISIONS: *
!**********************************************************************
!* *
!* DATE PROGRAMMER DESCRIPTION *
!* ---------- --------------- --------------------------------------- *
!* *
!* 10/10/2000 DELIA,TONY ORIGINAL CODING. *
!* *
!**********************************************************************
#include 'setenv.sqc' ! Set environment
!**********************************************************************
!* Setup Procedure *
!**********************************************************************
begin-setup
#Include 'setup02a.sqc' ! Printer and page-size init landscape
#define AUDsize 500 ! Override Audit Matrix Size
end-setup
!**********************************************************************
!* Headings *
!**********************************************************************
begin-heading 08
#include 'stdhdg01.sqc'
print '=' ( +1, 1,175 ) fill
print ' ' ( +1, 1, 0 )
print 'Recname' ( 0, 1, 15 )
print 'Fieldname' ( 0, +2, 18 )
print 'Type' ( 0, +2, 4 )
print 'Key?' ( 0, +2, 4 )
print 'Add?' ( 0, +2, 4 )
print 'Chg?' ( 0, +2, 4 )
print 'Del?' ( 0, +2, 4 )
print 'Subrecord' ( 0, +2, 15 )
print 'Key Mapping' ( 0, +2, 18 )
print '=' ( +1, 1,175 ) fill
end-heading
!**********************************************************************
!* Footing *
!**********************************************************************
begin-footing 04
print ' ' ( +1, 1, 1 )
print '=' ( +1, 1,175 ) fill
print 'PSAUDIT Analyzer' ( +1, 1, 0)
print 'by Tony DeLia' ( 0, +2, 0)
print 'tdelia@answerthink.com' ( 0, +5, 0)
end-footing
!**********************************************************************
!* Mainline Processing *
!**********************************************************************
begin-report
do Init-DateTime
do Get-Current-DateTime
move $AsOfToday to $AsOfDate
do Stdapi-Init
do Set-Defaults
do Process-Main
do Stdapi-Term
end-report
!**********************************************************************
!* Set Defaults *
!**********************************************************************
begin-procedure Set-Defaults
let $ReportId = 'TDAUDIT'
let $ReportTitle = 'PSAUDIT Analyzer'
display ' '
display $ReportId noline
display ' ' noline
display $ReportTitle
display ' '
end-procedure
!**********************************************************************
!* Process Main *
!**********************************************************************
begin-procedure Process-Main
let $sw = 'Y'
while upper($sw) = 'Y'
let $sw = 'N'
input $RECname maxlen=15 'Enter RECNAME'
uppercase $RECname
display $RECname
do REC-Func($RECname, 'Y', #O_ctr)
if #O_ctr > 0
do AUD-Func-Build($RECname, #O_ctr)
end-if
input $sw maxlen=1 'Enter Another Record? (Y/N)'
end-while
end-procedure
!**********************************************************************
!* Audit Substitution Variables *
!**********************************************************************
#define FLD_TYPES 'CharLongNbr SignDateTimeDtTm****'
#define AUD_KEY 1
#define AUD_ADD 8
#define AUD_CHG 128
#define AUD_DEL 1024
#define AUD_ANY 1161
#ifndef AUDsize
#define AUDsize 100 ! Default AUDmtx Size
#endif
!**********************************************************************
!* Define Audit Array *
!**********************************************************************
begin-procedure AUD-Array
create-array name=AUDmtx size={AUDsize} field=AUDrec:char -
field=AUDfld:char -
field=AUDsub:char -
field=AUDtyp:char -
field=AUDkey:char -
field=AUDadd:char -
field=AUDchg:char -
field=AUDdel:char
let #AUDmax = {AUDsize} - 1
let #AUDctr = 0
end-procedure
!**********************************************************************
!* Build Audit Array *
!**********************************************************************
begin-procedure AUD-Func-Build($I_main, #I_ctr)
clear-array name=AUDmtx
if #_AUDmax = 0
do AUD-Array
end-if
let #_AUDctr = 0
let #idx = 0
while #idx < #I_ctr
do REC-Func-Get(#idx, $rec, $fld, $sub, #lev)
if $sub <> 'Y'
do AUD-Func-Entry($I_main, $rec, $fld)
end-if
let #idx = #idx + 1
end-while
if #_AUDctr > 0
do AUD-Func-Print
end-if
end-procedure
!**********************************************************************
!* Build Audit Entry *
!**********************************************************************
begin-procedure AUD-Func-Entry($I_main, $I_rec, $I_fld)
begin-select
aud.recname
aud.fieldnum
aud.fieldname
aud.useedit
fld.fieldtype
let #pos = (&fld.fieldtype * 4) + 1
let $AUD_typ = rtrim(substr({FLD_TYPES}, #pos, 4),' ')
let $AUD_rec = $I_main
let $AUD_fld = $I_fld
let #AUD_opt = &aud.useedit
let $AUD_sub = ' '
if $I_main <> $I_rec
let $AUD_sub = $I_rec
end-if
do AUD-Process($AUD_rec, $AUD_fld, $AUD_sub, $AUD_typ, #AUD_opt)
from psrecfield aud,
psdbfield fld
where aud.fieldname = fld.fieldname
and aud.recname = $I_rec
and aud.fieldname = $I_fld
end-select
end-procedure
!**********************************************************************
!* Process Audit Entry *
!**********************************************************************
begin-procedure AUD-Process($AUD_rec, $AUD_fld, $AUD_sub, $AUD_typ, #AUD_opt)
do BOOL-Func('AND', {AUD_ANY}, #AUD_opt, #AUD_res)
let $AUD_key = 'N'
let $AUD_add = 'N'
let $AUD_chg = 'N'
let $AUD_del = 'N'
if #AUD_res > 0
! Test option bit - DELETE
if #AUD_res >= {AUD_DEL}
let $AUD_del = 'Y'
let #AUD_res = #AUD_res - {AUD_DEL}
end-if
! Test option bit - CHANGE
if #AUD_res >= {AUD_CHG}
let $AUD_chg = 'Y'
let #AUD_res = #AUD_res - {AUD_CHG}
end-if
! Test option bit - ADD
if #AUD_res >= {AUD_ADD}
let $AUD_add = 'Y'
let #AUD_res = #AUD_res - {AUD_ADD}
end-if
! Test option bit - KEY
if #AUD_res >= {AUD_KEY}
let $AUD_key = 'Y'
let #AUD_res = #AUD_res - {AUD_KEY}
end-if
end-if
! Insert into Array (REC, FLD, SUB, TYP, KEY, ADD, CHG, DEL)
let AUDmtx.AUDrec (#_AUDctr) = $AUD_rec
let AUDmtx.AUDfld (#_AUDctr) = $AUD_fld
let AUDmtx.AUDsub (#_AUDctr) = $AUD_sub
let AUDmtx.AUDtyp (#_AUDctr) = $AUD_typ
let AUDmtx.AUDkey (#_AUDctr) = $AUD_key
let AUDmtx.AUDadd (#_AUDctr) = $AUD_add
let AUDmtx.AUDchg (#_AUDctr) = $AUD_chg
let AUDmtx.AUDdel (#_AUDctr) = $AUD_del
let #_AUDctr = #_AUDctr + 1
end-procedure
!**********************************************************************
!* Print Audit Array (LOG File) *
!**********************************************************************
begin-procedure AUD-Func-Print LOCAL
let #AUD_key = 0
let #idx = 0
while #idx < #_AUDctr
let $AUD_rec = AUDmtx.AUDrec (#idx)
let $AUD_fld = AUDmtx.AUDfld (#idx)
let $AUD_sub = AUDmtx.AUDsub (#idx)
let $AUD_typ = AUDmtx.AUDtyp (#idx)
let $AUD_key = AUDmtx.AUDkey (#idx)
let $AUD_add = AUDmtx.AUDadd (#idx)
let $AUD_chg = AUDmtx.AUDchg (#idx)
let $AUD_del = AUDmtx.AUDdel (#idx)
let $KEY_fld = ' '
if $AUD_key = 'Y'
let #AUD_key = #AUD_key + 1
let $KEY_fld = 'KEY' || to_char(#AUD_key)
end-if
let $AUD_key = rtrim($AUD_key,'N')
let $AUD_add = rtrim($AUD_add,'N')
let $AUD_chg = rtrim($AUD_chg,'N')
let $AUD_del = rtrim($AUD_del,'N')
print ' ' ( +1, 1, 1 )
print $AUD_rec ( 0, 1, 15 )
print $AUD_fld ( 0, +2, 18 )
print $AUD_typ ( 0, +2, 4 )
print $AUD_key ( 0, +2, 4 )
print $AUD_add ( 0, +2, 4 )
print $AUD_chg ( 0, +2, 4 )
print $AUD_del ( 0, +2, 4 )
print $AUD_sub ( 0, +2, 15 )
print $KEY_fld ( 0, +2, 18 )
let #idx = #idx + 1
end-while
new-page
end-procedure
!**********************************************************************
!* Display Procedure *
!**********************************************************************
begin-procedure Display-Proc($SQR_proc)
display 'SQR Procedure: ' noline
display $SQR_proc
end-procedure
!**********************************************************************
!* Stdapi Init *
!**********************************************************************
begin-procedure Stdapi-Init
do Define-Prcs-Vars
do Get-Run-Control-Parms
end-procedure
!**********************************************************************
!* Stdapi Term *
!**********************************************************************
begin-procedure Stdapi-Term
if #prcs_process_instance > 0
do Update-Prcs-Run-Status
end-if
end-procedure
!**********************************************************************
!* Include Members: *
!**********************************************************************
#include 'tdfunc.sqc' !TD Custom SQR Function Library
#Include 'tdsub.sqc' !TD SubRecord Analyzer
#include 'prcsapi.sqc' !Update Process Request API
#include 'prcsdef.sqc' !Update Process Request variable declare
!Include 'stdapi.sqc' !Routines to Update Run Status
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
!Init-DateTime procedure
!**********************************************************************
!* End of Program *
!**********************************************************************
|
The sample code below shows how to implement field level audits in your program.
Both the update and insert routine initialize the audit variables and then Insert
into the PSAUDIT table. For updates only the 'changed' fields will be inserted
(this logic is held in the Insert-PSAUDIT routine itself). The $AUD_audit_actn
field is set to 'C' for Updates and 'A' for Inserts. The Inserts (Adds) do not use
the old value variable. Pretty basic stuff... some coding has been removed from the
routines for simplicity's sake...
| Sample - Incorporating
Field-Level Audits into your program |
!**********************************************************************
!* Update Dependent Beneficiary *
!**********************************************************************
begin-procedure Update-Dependent-Benef
let $SQL_proc = 'Update-Dependent-Benef'
let $SQL_action = 'Update'
let $SQL_table = 'DEPENDENT_BENEF'
if $B_birthdate_aud <> $V_birthdate_aud
or $B_relationship <> $D_relationship
or $B_student <> $V_student
or $B_sex <> $V_sex
begin-sql On-Error=Error-Routine
update ps_dependent_benef u
set u.relationship = $D_relationship,
u.sex = $V_sex,
u.birthdate = $V_birthdate,
u.student = $V_student
where u.rowid = $B_rowid;
end-sql
do Init-PSAUDIT
let $AUD_audit_actn = 'C'
let $AUD_recname = 'DEPENDENT_BENEF'
let $AUD_key1 = $V_emplid
let $AUD_key2 = $V_dependent_benef
let $AUD_fieldname = 'BIRTHDATE'
let $AUD_oldvalue = $B_birthdate_aud
let $AUD_newvalue = $V_birthdate_aud
do Insert-PSAUDIT
let $AUD_fieldname = 'RELATIONSHIP'
let $AUD_oldvalue = $B_relationship
let $AUD_newvalue = $D_relationship
do Insert-PSAUDIT
... etc ...
end-if
end-procedure
!**********************************************************************
!* Insert Dependent Beneficiary *
!**********************************************************************
begin-procedure Insert-Dependent-Benef
... INSERT goes here ...
do Init-PSAUDIT
let $AUD_audit_actn = 'A'
let $AUD_recname = 'DEPENDENT_BENEF'
let $AUD_key1 = $V_emplid
let $AUD_key2 = $V_dependent_benef
let $AUD_oldvalue = ' '
let $AUD_fieldname = 'BIRTHDATE'
let $AUD_newvalue = $V_birthdate_aud
do Insert-PSAUDIT
let $AUD_fieldname = 'RELATIONSHIP'
let $AUD_newvalue = $D_relationship
do Insert-PSAUDIT
... etc ...
end-procedure
!**********************************************************************
!* Init PSAUDIT *
!**********************************************************************
begin-procedure Init-PSAUDIT
let $AUD_audit_oprid = 'TDELIA'
let $AUD_audit_stamp = ' '
let $AUD_audit_actn = ' '
let $AUD_recname = ' '
let $AUD_fieldname = ' '
let $AUD_oldvalue = ' '
let $AUD_newvalue = ' '
let $AUD_key1 = ' '
let $AUD_key2 = ' '
let $AUD_key3 = ' '
let $AUD_key4 = ' '
let $AUD_key5 = ' '
let $AUD_key6 = ' '
let $AUD_key7 = ' '
let $AUD_key8 = ' '
let $AUD_key9 = ' '
let $AUD_key10 = ' '
let $AUD_key11 = ' '
let $AUD_key12 = ' '
let $AUD_key13 = ' '
let $AUD_key14 = ' '
let $AUD_key15 = ' '
end-procedure
!**********************************************************************
!* Insert PSAUDIT *
!**********************************************************************
begin-procedure Insert-PSAUDIT
let $SQL_proc = 'Insert-PSAUDIT'
let $SQL_action = 'Insert'
let $SQL_table = 'PSAUDIT'
if $AUD_audit_actn = 'A'
or $AUD_audit_actn = 'C'
and $AUD_newvalue <> $AUD_oldvalue
begin-sql On-Error=Error-Routine
insert into psaudit
(audit_oprid,
audit_stamp,
audit_actn,
recname,
fieldname,
oldvalue,
newvalue,
key1,
key2,
key3,
key4,
key5,
key6,
key7,
key8,
key9,
key10,
key11,
key12,
key13,
key14,
key15)
values ('TDELIA', ! AUDIT_OPRID
sysdate, ! AUDIT_STAMP
$AUD_audit_actn, ! AUDIT_ACTN
$AUD_recname, ! RECNAME
$AUD_fieldname, ! FIELDNAME
$AUD_oldvalue, ! OLDVALUE
$AUD_newvalue, ! NEWVALUE
$AUD_key1, ! KEY1
$AUD_key2, ! KEY2
$AUD_key3, ! KEY3
$AUD_key4, ! KEY4
$AUD_key5, ! KEY5
$AUD_key6, ! KEY6
$AUD_key7, ! KEY7
$AUD_key8, ! KEY8
$AUD_key9, ! KEY9
$AUD_key10, ! KEY10
$AUD_key11, ! KEY11
$AUD_key12, ! KEY12
$AUD_key13, ! KEY13
$AUD_key14, ! KEY14
$AUD_key15); ! KEY15
end-sql
end-if
end-procedure
!**********************************************************************
|
| Download TDAUDIT.SQR (and custom
SQC files) |
 |
I would appreciate any feedback you may have on this site.
Send mail to tdelia@erols.com or click on the
Octopus. |
 |
Please report any technical difficulties you may encounter to the address
above OR click on the Octopus. Thanks. |
NOTE - Use your Browsers BACK Button to return to prior page.
Tony DeLia - Updated July 21, 2001
|
