Peoplesoft – Printing Balance sheet tree

with mytree as
  (select distinct tnode.tree_name, tnode.parent_node_num, tnode.tree_node_num,
          tnode.tree_node, tlvl.tree_level, glacct.account,
          glacct.account_type, tnode.parent_node_name, glacct.descr
     from sysadm.pstreenode tnode,
          sysadm.pstreeleaf tleaf,
          sysadm.ps_gl_account_tbl glacct,
          sysadm.pstreelevel tlvl
     where tnode.setid=tleaf.setid
       and tnode.tree_name=tleaf.tree_name
       and tnode.effdt=tleaf.effdt
       and tleaf.tree_node_num between tnode.tree_node_num and tnode.tree_node_num_end
       and glacct.account between tleaf.range_from and tleaf.range_to
       and tnode.setid=tlvl.setid
       and tnode.tree_name=tlvl.tree_name
       and tnode.tree_level_num=tlvl.tree_level_num
       and tnode.tree_name = 'XPRS_COA')
  --select tree_name, parent_node_name, tree_node, tree_level, account, account_type, level, lpad(descr,length(descr) + level * 2 - 2,'-')
  select lpad(descr,length(descr) + level * 2 - 2,'-')
    from mytree
    start with parent_node_num = 1250000000
    connect by prior tree_node_num = parent_node_num

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.