!**********************************************************************
!* *
!* MODULE: TDTREE99.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 09/29/1999. *
!* SYSTEM: TD SQR UTILITY SERIES. *
!* DESC: TREE STRUCTURE RE-NUMBERING. *
!* *
!**********************************************************************
!* *
!* TABLES: pstreenode - Select, Insert *
!* pstreeleaf - Select, Insert *
!* pstreebranch - Select, Insert *
!* pstreelevel - Select, Insert *
!* pstreedefn - Select, Insert *
!* pslock - Select, Update *
!* *
!**********************************************************************
!* *
!* INPUT: SETID - Setid of the Tree (or blank) *
!* TREE_NAME - Name of the Tree *
!* EFFDT - New Effective Date for Tree *
!* *
!**********************************************************************
!* *
!* 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 *
!* *
!**********************************************************************
#include 'setenv.sqc' ! Set environment
!**********************************************************************
!* Setup Procedure *
!**********************************************************************
begin-setup
#Include 'setup02a.sqc' ! Printer and page-size init landscape
#define MAXNODENUM 2000000000 ! Maximum Node Number (See PTUGAPTR)
!define VERSION_60 ! PeopleSoft Version 6.0
!define VERSION_70 ! PeopleSoft Version 7.0
#define VERSION_75 ! PeopleSoft Version 7.5
end-setup
!**********************************************************************
!* Headings *
!**********************************************************************
begin-heading 09
#include 'stdhdg01.sqc'
print ' Tree: ' ( 4, 1, 0 )
print $TreeHdg ( 0, 0, 0 )
print '=' ( +1, 1,175) fill
print 'Tree' ( +1, 1, 0 )
print 'Tree' ( 0, 21, 0 )
print ' NEW' ( 0, 26, 0 )
print ' NEW' ( 0, 38, 0 )
print ' NEW' ( 0, 50, 0 )
print ' OLD' ( 0, 62, 0 )
print ' OLD' ( 0, 74, 0 )
print ' OLD' ( 0, 86, 0 )
print 'Tree' ( 0,103, 0 )
print ' OLD Lf' ( 0,123, 0 )
print ' OLD Br' ( 0,135, 0 )
print ' OLD Br' ( 0,147, 0 )
print ' OLD Br' ( 0,159, 0 )
print 'Node' ( +1, 1, 0 )
print 'Level' ( 0, 21, 0 )
print ' Begin' ( 0, 26, 0 )
print ' End' ( 0, 38, 0 )
print ' Parent' ( 0, 50, 0 )
print ' Begin' ( 0, 62, 0 )
print ' End' ( 0, 74, 0 )
print ' Parent' ( 0, 86, 0 )
print 'Branch' ( 0,103, 0 )
print ' Begin' ( 0,123, 0 )
print ' Begin' ( 0,135, 0 )
print ' End' ( 0,147, 0 )
print ' Parent' ( 0,159, 0 )
print '=' ( +1, 1,175) fill
end-heading
!**********************************************************************
!* Footing *
!**********************************************************************
begin-footing 04
print ' ' ( +1, 1, 1)
print '=' ( +1, 1,175) fill
print 'Tree Re-Numbering Utility' ( +1, 1, 0)
print 'by Tony DeLia' ( 0, +2, 0)
print 'tdelia@erols.com' ( 0, +5, 0)
end-footing
!**********************************************************************
!* Mainline Processing *
!**********************************************************************
begin-report
do Init-DateTime
do Get-Current-DateTime
move $AsOfToday to $AsOfDate
do Set-Defaults
if $prcs_process_instance = ''
do Enter-Prompts
if $TreeExists = 'Y'
do Init-Routine
do Process-Phase1 ! Load Tree Array
do Process-Phase2 ! Build Tree Structure
else
display 'NO action performed - ' noline
display 'Tree does not exist'
end-if
else
display 'NO action performed - ' noline
display 'Process must be run outside PeopleSoft'
end-if
end-report
!**********************************************************************
!* Set Defaults *
!**********************************************************************
begin-procedure Set-Defaults
let $ReportId = 'TDTREE99'
let $ReportTitle = 'Tree Re-numbering Utility'
display $ReportId noline
display ' ' noline
display $ReportTitle
display ' '
end-procedure
!**********************************************************************
!* Enter Prompts *
!**********************************************************************
begin-procedure Enter-Prompts
input $Setid maxlen=5 'Enter SETID (or enter)'
uppercase $Setid
let $Setid = lpad(rtrim($Setid,' '),1,' ')
display ' Setid: ' noline
display $Setid
input $TreeName maxlen=18 'Enter TREE Name'
uppercase $TreeName
let $TreeName = lpad(rtrim($TreeName,' '),1,' ')
display 'TreeName: ' noline
display $TreeName
input $Effdt maxlen=11 'Enter New Effdt (YYYY-MM-DD)'
display ' Effdt: ' noline
display $Effdt
input $Updt maxlen=1 'Update Database? (Y/N)'
uppercase $Updt
if $Updt <> 'Y'
let $Updt = 'N'
end-if
display $Updt
! Select Latest Version of Tree Definition
let $TreeExists = 'N'
begin-select
t.setid
t.tree_name
t.effdt
let $TreeExists = 'Y'
let $TreeDate = &t.effdt
from pstreedefn t
where t.setid = $Setid
and t.tree_name = $TreeName
and t.effdt =
(select max(t2.effdt)
from pstreedefn t2
where t2.setid = t.setid
and t2.tree_name = t.tree_name
and t2.effdt < $Effdt)
end-select
! Report Heading Details
if rtrim($Setid,' ') <> ''
let $TreeHdg = $setid || '.' || $TreeName
else
let $TreeHdg = $TreeName
end-if
let $TreeHdg = $TreeHdg || ' ( ' || $Effdt || ' )'
display ' '
display $TreeHdg
display ' '
! Convert Date (where applicable) - Add routines if needed
! Assumes a valid date has been entered...
#ifdef ORACLE
begin-select
to_date($Effdt,'YYYY-MM-DD') &effdt
let $Effdt = &effdt
from dual
end-select
#endif
end-procedure
!**********************************************************************
!* Initialization Routine *
!**********************************************************************
begin-procedure Init-Routine
! TREE Data
create-array name=TREEtab size=4096 field=TREEnode:char -
field=TREElev:number -
field=TREEbrch:char -
field=TREEtype:char -
field=TREEold:char -
field=TREEbeg:number:2 -
field=TREEend:number:2 -
field=TREEpar:number:2
create-array name=LEVtab size=64 field=LEVbeg:number
let #old = 0
let #new = 1
end-procedure
!**********************************************************************
!* Phase 1 - Load Tree Node Array *
!**********************************************************************
begin-procedure Process-Phase1
let #TREEctr = 0
begin-select
n.tree_node
n.tree_branch
n.tree_level_num
n.tree_node_type
n.old_tree_node_num
n.tree_node_num
n.tree_node_num_end
n.parent_node_num
! Load Tree Array
let TREEtab.TREEnode (#TREEctr) = &n.tree_node
let TREEtab.TREElev (#TREEctr) = &n.tree_level_num
let TREEtab.TREEbrch (#TREEctr) = &n.tree_branch
let TREEtab.TREEtype (#TREEctr) = &n.tree_node_type
let TREEtab.TREEold (#TREEctr) = &n.old_tree_node_num
let TREEtab.TREEbeg (#TREEctr, #old) = &n.tree_node_num
let TREEtab.TREEend (#TREEctr, #old) = &n.tree_node_num_end
let TREEtab.TREEpar (#TREEctr, #old) = &n.parent_node_num
let #TREEctr = #TREEctr + 1
from pstreenode n
where n.setid = $Setid
and n.tree_name = $TreeName
and n.effdt = $TreeDate
order by n.tree_node_num
end-select
end-procedure
!**********************************************************************
!* Phase 2 - Assign Node Numbers (Build Tree) *
!**********************************************************************
begin-procedure Process-Phase2
do Select-Version
! Assign Begin/Parent Node Numbers
let #nodes = #TREEctr
let #gap = floor({MAXNODENUM}/#nodes)
let #ctr = 0
while #ctr < #nodes
let #beg = #gap * #ctr
if #beg = 0
let #beg = 1
end-if
let TREEtab.TREEbeg (#ctr, #new) = #beg
let #lev = TREEtab.TREElev (#ctr)
let #par = 0
! No guarantee that interim level is present...
! TREE_LEVEL_NUM could jump from 1 to 3 (level 2 may not exist)...
! if #lev > 1
! let #par = LEVtab.LEVbeg (#lev - 1)
! end-if
! If prior level missing back into last parent node number....
if #lev > 1
let #idx = #lev - 1
while #par = 0
and #idx >= 0
let #par = LEVtab.LEVbeg (#idx)
let #idx = #idx - 1
end-while
end-if
let LEVtab.LEVbeg (#lev) = #beg
let TREEtab.TREEpar (#ctr, #new) = #par
let #ctr = #ctr + 1
end-while
! Assign End Node Numbers
let #ctr = 0
while #ctr < #nodes
let #end = {MAXNODENUM}
if #ctr <> 0
let #lev = TREEtab.TREElev (#ctr)
let #ptr = #ctr + 1
while #ptr < #nodes
let #lev2 = TREEtab.TREElev (#ptr)
if #lev2 <= #lev
let #end = TREEtab.TREEbeg (#ptr, #new)
let #end = #end - 1
let #ptr = #nodes
end-if
let #ptr = #ptr + 1
end-while
end-if
let TREEtab.TREEend (#ctr, #new) = #end
let #ctr = #ctr + 1
end-while
! Build New Tree
let #lev_max = 0
let #ctr = 0
while #ctr < #nodes
let #N_tree_level_num = TREEtab.TREElev (#ctr)
let $N_tree_node = TREEtab.TREEnode (#ctr)
let $N_tree_branch = TREEtab.TREEbrch (#ctr)
let $N_tree_node_type = TREEtab.TREEtype (#ctr)
let $N_old_tree_node_num = TREEtab.TREEold (#ctr)
let #N_tree_node_num = TREEtab.TREEbeg (#ctr, #new)
let #N_tree_node_num_end = TREEtab.TREEend (#ctr, #new)
let #N_parent_node_num = TREEtab.TREEpar (#ctr, #new)
let #O_tree_node_num = TREEtab.TREEbeg (#ctr, #old)
let #O_tree_node_num_end = TREEtab.TREEend (#ctr, #old)
let #O_parent_node_num = TREEtab.TREEpar (#ctr, #old)
print ' ' ( +1, 1, 0 )
print $N_tree_node ( 0, 1, 0 )
print #N_tree_level_num ( 0, 21, 0 ) edit B999
print #N_tree_node_num ( 0, 26, 0 ) edit B9999999999
print #N_tree_node_num_end ( 0, 38, 0 ) edit B9999999999
print #N_parent_node_num ( 0, 50, 0 ) edit B9999999999
print #O_tree_node_num ( 0, 62, 0 ) edit B9999999999
print #O_tree_node_num_end ( 0, 74, 0 ) edit B9999999999
print #O_parent_node_num ( 0, 86, 0 ) edit B9999999999
print $N_tree_branch ( 0,103, 0 )
if $Updt = 'Y'
do Insert-Node
end-if
do Process-Leaf
if $N_tree_node = $N_tree_branch
do Process-Branch
end-if
if #lev_max < #lev
let #lev_max = #lev
end-if
let #ctr = #ctr + 1
end-while
do Process-Level
do Process-Defn
if $Updt = 'Y'
do Update-Version
end-if
end-procedure
!**********************************************************************
!* Insert Tree Node *
!**********************************************************************
begin-procedure Insert-Node
begin-sql
insert into pstreenode
(setid,
tree_name,
effdt,
tree_branch,
tree_node_num,
tree_node,
tree_node_num_end,
tree_level_num,
tree_node_type,
parent_node_num,
old_tree_node_num)
values ($Setid, ! SETID
$TreeName, ! TREE_NAME
$Effdt, ! EFFDT
$N_tree_branch, ! TREE_BRANCH
#N_tree_node_num, ! TREE_NODE_NUM
$N_tree_node, ! TREE_NODE
#N_tree_node_num_end, ! TREE_NODE_NUM_END
#N_tree_level_num, ! TREE_LEVEL_NUM
$N_tree_node_type, ! TREE_NODE_TYPE
#N_parent_node_num, ! PARENT_NODE_NUM
$N_old_tree_node_num); ! OLD_TREE_NODE_NUM
end-sql
end-procedure
!**********************************************************************
!* Process Tree Leaf *
!**********************************************************************
begin-procedure Process-Leaf
begin-select
l.tree_branch
l.tree_node_num
l.range_from
l.range_to
l.dynamic_range
l.old_tree_node_num
if $Updt = 'Y'
do Insert-Leaf
end-if
print ' ' ( +1, 1, 1 )
print &l.tree_node_num ( 0,123, 0 ) edit B9999999999
from pstreeleaf l
where l.setid = $Setid
and l.tree_name = $TreeName
and l.effdt = $TreeDate
and l.tree_branch = $N_tree_branch
and l.tree_node_num = #O_tree_node_num
end-select
end-procedure
!**********************************************************************
!* Insert Tree Leaf *
!**********************************************************************
begin-procedure Insert-Leaf
begin-sql
insert into pstreeleaf
(setid,
tree_name,
effdt,
tree_branch,
tree_node_num,
range_from,
range_to,
dynamic_range,
old_tree_node_num)
values ($Setid, ! SETID
$TreeName, ! TREE_NAME
$Effdt, ! EFFDT
&l.tree_branch, ! TREE_BRANCH
#N_tree_node_num, ! TREE_NODE_NUM
&l.range_from, ! RANGE_FROM
&l.range_to, ! RANGE_TO
&l.dynamic_range, ! DYNAMIC_RANGE
&l.old_tree_node_num); ! OLD_TREE_NODE_NUM
end-sql
end-procedure
!**********************************************************************
!* Process Tree Branch *
!**********************************************************************
begin-procedure Process-Branch
begin-select
b.tree_branch
b.version
b.parent_branch
b.branch_level_num
b.parent_node_num
b.tree_level_num
b.tree_node_num
b.tree_node_num_end
b.node_count
b.leaf_count
if $Updt = 'Y'
do Insert-Branch
end-if
print ' ' ( +1, 1, 1 )
print &b.tree_node_num ( 0,135, 0 ) edit B9999999999
print &b.tree_node_num_end ( 0,147, 0 ) edit B9999999999
print &b.parent_node_num ( 0,159, 0 ) edit B9999999999
from pstreebranch b
where b.setid = $Setid
and b.tree_name = $TreeName
and b.effdt = $TreeDate
and b.tree_branch = $N_tree_node
end-select
end-procedure
!**********************************************************************
!* Insert Tree Branch *
!**********************************************************************
begin-procedure Insert-Branch
begin-sql
insert into pstreebranch
(setid,
tree_name,
effdt,
tree_branch,
version,
parent_branch,
branch_level_num,
parent_node_num,
tree_level_num,
tree_node_num,
tree_node_num_end,
node_count,
leaf_count)
values ($Setid, ! SETID
$TreeName, ! TREE_NAME
$Effdt, ! EFFDT
&b.tree_branch, ! TREE_BRANCH
#versiontdm, ! VERSION
&b.parent_branch, ! PARENT_BRANCH
&b.branch_level_num, ! BRANCH_LEVEL_NUM
#N_parent_node_num, ! PARENT_NODE_NUM
&b.tree_level_num, ! TREE_LEVEL_NUM
#N_tree_node_num, ! TREE_NODE_NUM
#N_tree_node_num_end, ! TREE_NODE_NUM_END
&b.node_count, ! NODE_COUNT
&b.leaf_count) ! LEAF_COUNT
end-sql
end-procedure
!**********************************************************************
!* Process Tree Definition *
!**********************************************************************
begin-procedure Process-Defn
begin-select
d.eff_status
d.version
d.tree_strct_id
d.descr
d.all_values
d.use_levels
d.valid_tree
d.level_count
d.node_count
d.leaf_count
d.tree_has_ranges
d.duplicate_leaf
#ifndef VERSION_60
d.tree_category
#ifdef VERSION_75
d.tree_acc_method
d.tree_acc_selector
d.tree_acc_sel_opt
#endif
#endif
if $Updt = 'Y'
do Insert-Defn
end-if
from pstreedefn d
where d.setid = $Setid
and d.tree_name = $TreeName
and d.effdt = $TreeDate
end-select
end-procedure
!**********************************************************************
!* Insert Tree Definition *
!**********************************************************************
begin-procedure Insert-Defn
begin-sql
insert into pstreedefn
(setid,
tree_name,
effdt,
eff_status,
version,
tree_strct_id,
descr,
all_values,
use_levels,
valid_tree,
level_count,
node_count,
leaf_count,
tree_has_ranges,
#ifndef VERSION_60
tree_category,
#ifdef VERSION_75
tree_acc_method,
tree_acc_selector,
tree_acc_sel_opt,
#endif
#endif
duplicate_leaf)
values ($Setid, ! SETID
$TreeName, ! TREE_NAME
$Effdt, ! EFFDT
&d.eff_status, ! EFF_STATUS
#versiontdm, ! VERSION
&d.tree_strct_id, ! TREE_STRCT_ID
&d.descr, ! DESCR
&d.all_values, ! ALL_VALUES
&d.use_levels, ! USE_LEVELS
&d.valid_tree, ! VALID_TREE
&d.level_count, ! LEVEL_COUNT
&d.node_count, ! NODE_COUNT
&d.leaf_count, ! LEAF_COUNT
&d.tree_has_ranges, ! TREE_HAS_RANGES
#ifndef VERSION_60
&d.tree_category, ! TREE_CATEGORY
#ifdef VERSION_75
&d.tree_acc_method, ! TREE_ACC_METHOD
&d.tree_acc_selector, ! TREE_ACC_SELECTOR
&d.tree_acc_sel_opt, ! TREE_SEL_OPT
#endif
#endif
&d.duplicate_leaf); ! DUPLICATE_LEAF
end-sql
end-procedure
!**********************************************************************
!* Process Tree Level *
!**********************************************************************
begin-procedure Process-Level
begin-select
v.tree_level
v.tree_level_num
v.all_values
if $Updt = 'Y'
do Insert-Level
end-if
from pstreelevel v
where v.setid = $Setid
and v.tree_name = $TreeName
and v.effdt = $TreeDate
end-select
end-procedure
!**********************************************************************
!* Insert Tree Level *
!**********************************************************************
begin-procedure Insert-Level
begin-sql
insert into pstreelevel
(setid,
tree_name,
effdt,
tree_level,
tree_level_num,
all_values)
values ($Setid, ! SETID
$TreeName, ! TREE_NAME
$Effdt, ! EFFDT
&v.tree_level, ! TREE_LEVEL
&v.tree_level_num, ! TREE_LEVEL_NUM
&v.all_values); ! ALL_VALUES
end-sql
end-procedure
!**********************************************************************
!* Select Version (TDM=Tree Definition / TSM=Tree Structure) *
!**********************************************************************
begin-procedure Select-Version
begin-select
u.versiontdm
let #versiontdm = &u.versiontdm + 1
from pslock u
end-select
end-procedure
!**********************************************************************
!* Update Version (TDM=Tree Definition / TSM=Tree Structure) *
!**********************************************************************
begin-procedure Update-Version
begin-sql
update pslock u
set u.versiontdm = #versiontdm
end-sql
end-procedure
!**********************************************************************
!* Include Members: *
!**********************************************************************
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
!Init-DateTime procedure
!**********************************************************************
!* End of Program *
!**********************************************************************
|
