Get PeopleSoft Navigation Lists (Component Based & Non Component Based)

-- SQL TO GET LIST OF ALL NAVIGATION MENU/COMPONENT EXCLUDING HIDDEN FOR ALL USER EXCEPT INCLUDED IN EXCEPTION LIST

 SELECT DISTINCT O.ROLEUSER,O.ROLENAME,R.CLASSID,
   A.PORTAL_NAME
  , A.PORTAL_OBJNAME
   , A.PORTAL_URI_SEG1 MENU_NAME
 , A.PORTAL_URI_SEG2 COMPONENT_NAME
 , A.PORTAL_URI_SEG3  MARKET
 , A.PORTAL_URI_SEG4
 , TRIM(((CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT( (CASE WHEN RTRIM(H.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(H.PORTAL_LABEL)
 ,' > ') ELSE ' ' END)
 , (CASE WHEN RTRIM(G.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(G.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(F.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(F.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(E.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(E.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(D.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(D.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(C.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(C.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(B.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(B.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(A.PORTAL_LABEL) <> 'Root' THEN ( (CASE WHEN A.PORTAL_CREF_USGT = 'LINK' THEN DECODE(RTRIM(A.PORTAL_LABEL)
 , ''
 , (  SELECT DISTINCT MAX(RTRIM(A1.PORTAL_LABEL))   FROM PSPRSMDEFN A1 WHERE A1.PORTAL_NAME = A.PORTAL_LINK_PORTAL
   AND A1.PORTAL_OBJNAME = A.PORTAL_LINKOBJNAME
   AND A1.PORTAL_NAME = A.PORTAL_NAME) , RTRIM(A.PORTAL_LABEL)) ELSE RTRIM(A.PORTAL_LABEL) END ) ) END) ) ))) AS NAVIGATION
  FROM
    (((((((PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME= A.PORTAL_NAME
   AND B.PORTAL_REFTYPE = 'F'
   AND B.PORTAL_OBJNAME= A.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME= B.PORTAL_NAME
   AND C.PORTAL_REFTYPE = 'F'
   AND C.PORTAL_OBJNAME= B.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME= C.PORTAL_NAME
   AND D.PORTAL_REFTYPE = 'F'
   AND D.PORTAL_OBJNAME= C.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME= D.PORTAL_NAME
   AND E.PORTAL_REFTYPE = 'F'
   AND E.PORTAL_OBJNAME= D.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN F ON F.PORTAL_NAME= E.PORTAL_NAME
   AND F.PORTAL_REFTYPE = 'F'
   AND F.PORTAL_OBJNAME= E.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN G ON G.PORTAL_NAME= F.PORTAL_NAME
   AND G.PORTAL_REFTYPE = 'F'
   AND G.PORTAL_OBJNAME= F.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN H ON H.PORTAL_NAME= G.PORTAL_NAME
   AND H.PORTAL_REFTYPE = 'F'
   AND H.PORTAL_OBJNAME= G.PORTAL_PRNTOBJNAME) ,PSROLECLASS R , PSAUTHITEM P,PSMENUITEM M, PSROLEUSER O
 WHERE  A.PORTAL_CREF_URLT NOT IN ('USCR','UGEN')
      AND NOT EXISTS (
 SELECT 'X'
  FROM PSPRSMSYSATTRVL ATTR
 WHERE ATTR.PORTAL_NAME = A.PORTAL_NAME
   AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
   AND ATTR.PORTAL_OBJNAME = A.PORTAL_OBJNAME)
   AND A.PORTAL_NAME = 'EMPLOYEE'
   AND R.CLASSID = P.CLASSID
   AND R.ROLENAME = O.ROLENAME
   AND P.MENUNAME = M.MENUNAME AND P.BARITEMNAME = M.ITEMNAME
AND M.MENUNAME = A.PORTAL_URI_SEG1
   AND M.PNLGRPNAME = A.PORTAL_URI_SEG2
   AND O.ROLEUSER  NOT IN ('000004'); --EXCLUDE SUPER/BATCH USER IDS


-- SQL TO GET LIST OF ALL NAVIGATION ISCRIPT AND OTHER (EXCEPT MENU/COMPONENT ) EXCLUDING HIDDEN FOR ALL USER EXCEPT INCLUDED IN EXCEPTION LIST

SELECT DISTINCT O.ROLEUSER,O.ROLENAME,R.CLASSID,
   A.PORTAL_NAME
  , A.PORTAL_OBJNAME
, A.PORTAL_URI_SEG1  RECORD_NAME
 , A.PORTAL_URI_SEG2  FIELD_NAME
 , A.PORTAL_URI_SEG3  PEOPLECODE_EVENT
, A.PORTAL_URI_SEG4  ISCRIPT_FUNCTION
 , TRIM(((CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT( (CASE WHEN RTRIM(H.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(H.PORTAL_LABEL)
 ,' > ') ELSE ' ' END)
 , (CASE WHEN RTRIM(G.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(G.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(F.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(F.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(E.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(E.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(D.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(D.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(C.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(C.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(B.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(B.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(A.PORTAL_LABEL) <> 'Root' THEN ( (CASE WHEN A.PORTAL_CREF_USGT = 'LINK' THEN DECODE(RTRIM(A.PORTAL_LABEL)
 , ''
 , (  SELECT DISTINCT MAX(RTRIM(A1.PORTAL_LABEL))   FROM PSPRSMDEFN A1 WHERE A1.PORTAL_NAME = A.PORTAL_LINK_PORTAL
   AND A1.PORTAL_OBJNAME = A.PORTAL_LINKOBJNAME
   AND A1.PORTAL_NAME = A.PORTAL_NAME) , RTRIM(A.PORTAL_LABEL)) ELSE RTRIM(A.PORTAL_LABEL) END ) ) END) ) ))) AS NAVIGATION
  FROM PSPRSMPERM S, (((((((PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME= A.PORTAL_NAME
   AND B.PORTAL_REFTYPE = 'F'
   AND B.PORTAL_OBJNAME= A.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME= B.PORTAL_NAME
   AND C.PORTAL_REFTYPE = 'F'
   AND C.PORTAL_OBJNAME= B.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME= C.PORTAL_NAME
   AND D.PORTAL_REFTYPE = 'F'
   AND D.PORTAL_OBJNAME= C.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME= D.PORTAL_NAME
   AND E.PORTAL_REFTYPE = 'F'
   AND E.PORTAL_OBJNAME= D.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN F ON F.PORTAL_NAME= E.PORTAL_NAME
   AND F.PORTAL_REFTYPE = 'F'
   AND F.PORTAL_OBJNAME= E.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN G ON G.PORTAL_NAME= F.PORTAL_NAME
   AND G.PORTAL_REFTYPE = 'F'
   AND G.PORTAL_OBJNAME= F.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN H ON H.PORTAL_NAME= G.PORTAL_NAME
   AND H.PORTAL_REFTYPE = 'F'
   AND H.PORTAL_OBJNAME= G.PORTAL_PRNTOBJNAME) ,PSROLECLASS R , PSROLEUSER O
 WHERE A.PORTAL_NAME = S.PORTAL_NAME
   AND A.PORTAL_REFTYPE = S.PORTAL_REFTYPE
   AND A.PORTAL_OBJNAME = S.PORTAL_OBJNAME
   AND A.PORTAL_CREF_URLT IN ('USCR','UGEN')
   AND ((S.PORTAL_PERMTYPE = 'P'
   AND R.CLASSID = S.PORTAL_PERMNAME)
    OR (S.PORTAL_PERMTYPE = 'R'
   AND R.ROLENAME = S.PORTAL_PERMNAME))
   AND NOT EXISTS (
 SELECT 'X'
  FROM PSPRSMSYSATTRVL ATTR
 WHERE ATTR.PORTAL_NAME = A.PORTAL_NAME
   AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
   AND ATTR.PORTAL_OBJNAME = A.PORTAL_OBJNAME)
   AND A.PORTAL_NAME = 'EMPLOYEE'
   AND R.ROLENAME = O.ROLENAME
   AND O.ROLEUSER NOT IN ('000004'); --EXCLUDE SUPER/BATCH USER IDS

White Paper on "PS Query Categorization Framework"- Hexaware technologies Solution Website

With the use of conventional PS Query viewer/Manager, users spend as much as 5 to 6 hours for generating month-end & year-end reports. At the same time, year-end-processing and audits are too time consuming and exhausting for user due to the need of running multiple queries in a predefined sequence which is hard to remember.

The PS Query Categorization Framework helps users to store queries in categorization according to functionality with a predefined sequence of execution, which eliminates the need of remembering the query name sequence while actual processing. This indeed reduces the timeline for month-end and year-end processing by a significant margin with a savings of 60%-70% of user time in executing and processing queries.

This functionality also gives an added advantage to business by providing flexibility of work sharing without actually creating lot of private queries, when a certain user is not available and his counterpart needs to continue with processing in order to access Category or group of queries created by the other user. This creates a significant amount of flexibility.

Following is link for Hexaware Technologies official site for the published white paper:
PS Query Categorization Framework White Paper

Disclaimer Contents of this whitepaper are the exclusive property of Hexaware Technologies and may not be reproduced in any form without the prior written consent of Hexaware Technologies.

Generate Excel file Using SQR

Sometime client needed report to be generated into excel sheet. we can create excel report using PeopleCode and using SQR both.

But there is a limitation to generate excel report using PeopleCode is that, it calls COM objects for Excel.Application which can be used in Window server only where code will get execute. so if user want to generate Excel report using PeopleCode (Including this PeopleCode in Application Engine) then that process needs to be run in NT server only.

Whereas if we will create excel report using SQR it can be executed in any server.

PeopleSoft already provides various procedures which can be used while generating excel using SQR. i have incorporated those procedures in SQC "ExclWrite.SQC" (Find below Code for this SQC) which have been used in the sample excel file generation SQR Report "EXCLFILE.SQR" (Find below Code for this SQR) .

This topic might help people who are going to write excel file using SQR first time. all the procedures i have included in the SQC file are provided by PeopleSoft only, i have just created new SQC and attached here for better understanding and use of each procedures.

1) ExclWrite.SQC

! xml_cell_char($character, #column_adjust, #style_id)
! xml_cell_comment($Author,$Font,$Comment)
! xml_cell_datetime($datetime, #column_adjust, #style_id,#MergeAcross)
! xml_cell_formula($formula, #column_adjust, #style_id,#MergeAcross)
! xml_cell_number(#number, $edit, #column_adjust, #style_id,#MergeAcross)
! xml_cell_premovement(#column_adjust, #style_id, #mergeacross, :$xml_cell_pre_attr, :$xml_cell_post_attr)
! xml_character_cleaner($character, :$character)
! xml_closing_cell()
! xml_closing_row()
! xml_closing_table()
! xml_closing_worksheet()
! xml_closing_worksheetoptions()
! xml_finalize_autofilter()
! xml_finalize_pagebreak()
! xml_finalize_style()
! xml_finalize_validationlist($Range,$Type,$List,$InputTitle,$InputMessage)
! xml_finalize_workbook()
! xml_finalize_worksheet_allowoptions($Allow_Options)
! xml_finalize_worksheet_pagesetup($Orientation, $Center_Options, #Margin_Top, #Margin_Right, #Margin_Bottom, #Margin_Left, $Header, $Footer)
! xml_finalize_worksheet_print(#FitWidth, #FitHeight, #Scale, #PageBreakZoom)
! xml_finalize_worksheet_split(#SplitHorizontal, #SplitVertical, #Freeze)
! xml_init_col(#column, #autofitwidth, #width, #hidden, #style_id, #Span)
! xml_init_environment()
! xml_init_file(#File, $file, #ProtectStructure, #ProtectWindows, $Encoding_F)
! xml_init_row(#row_adjust, #autofitheight, #height, #hidden, #style_id)
! xml_init_style(#style_id)
! xml_init_worksheet($worksheet_name, #Protected, #NameRangesAreComing)
! xml_pagebreak()
! xml_style_alignment($Horizontal, $Vertical, #ShrinkToFit, #WrapText)
! xml_style_border($Position, $LineStyle, #Weight)
! xml_style_border_end()
! xml_style_font($FontName, #Size, #Bold_andor_Italics)
! xml_style_interior($Color, $Pattern)
! xml_style_numberformat($Format)
! xml_style_protection(#Protected, #Hideformula)
! xml_style_protection(#Protected, #Hideformula)


!***********************************************************************
! Procedure: xml_init_environment
! Purpose  : Initialization  for the xml file writing to generate excel file
!***********************************************************************

begin-procedure xml_init_environment()

   Create-Array Name=XML_Memory_Global Size=99999  !#i = File Number
      Field=Worksheet:Number=1
      Field=Column:Number=1
      Field=Row:Number=1
      Field=Filename:Char
      Field=Worksheet1_Name:Char

   Create-Array Name=XML_PageBreak Size=99999
      Field=Row:Number=1

end-procedure


!***********************************************************************
! Procedure: xml_init_row
! Purpose  : Initialization of row where data will be written
!***********************************************************************
begin-procedure xml_init_row(#row_adjust, #autofitheight, #height, #hidden, #style_id)
   do xml_closing_row()

   let $xml_row_index = ''
   let $xml_autofit = ''
   let $xml_height = ''
   let $xml_hidden = ''
   let $xml_style = ''

!If the Row needs adjusting (or this is our first row...
!   Check to see if this we've ever written a before
!      if so, write the

!   Now write the new
!   Set the #_xml_first_row to false
   if #row_adjust or #_xml_first_row
      add 1 to #_xml_current_row
      if #row_adjust > 1
         let #_xml_current_row = #_xml_current_row + #row_adjust - 1
         let $xml_row_index = ' ss:Index="' || rtrim(ltrim(edit(#_xml_current_row,'9999999'),' '),' ') || '"'
      end-if
      if #autofitheight > 0
         let $xml_autofit = ' ss:AutoFitHeight="1"'
      end-if
      if #height > 0
         let $xml_height = ' ss:Height="' || rtrim(ltrim(edit(#height,'9999999.99'),' '),' ') || '"'
      end-if
      if #hidden > 0
         let $xml_hidden = ' ss:Hidden="1"'
      end-if
      if #style_id > 0
         let $xml_style = ' ss:StyleID="s' || rtrim(ltrim(edit(#style_id, '99999'),' '),' ') || '"'
      end-if

      let #_XML_Close_Row = 1
      write #_XML_File_Number from '   '
      let #_xml_current_col = 0
      let #_xml_first_row = 0
   end-if

end-procedure




!***********************************************************************
! Procedure: xml_closing_row
! Purpose  : To close row where current data is being written
!***********************************************************************
begin-procedure xml_closing_row()
   do xml_closing_cell()
   if #_XML_Close_Row
      write #_XML_File_Number from '  
'      let #_XML_Close_Row = 0
   end-if
end-procedure


!***********************************************************************
! Procedure: xml_closing_cell
! Purpose  : To close cell where current data is being written
!***********************************************************************
begin-procedure xml_closing_cell()
   if #_XML_Close_cell
      write #_XML_File_Number from '  
'      let #_XML_Close_Cell = 0
   end-if
end-procedure

!***********************************************************************
! Procedure: xml_init_file
! Purpose  : Open xml file for writing
!***********************************************************************
begin-procedure xml_init_file(#File, $file, #ProtectStructure, #ProtectWindows, $Encoding_F)
! Open XML file.
   let #_XML_File_Number = #File
   open $file as #_XML_File_Number For-Writing Record=10000 Status=#FileStatus Encoding = $Encoding_F

!Write Preliminary Stuff
   Let $First_line_F = ''
   write #_XML_File_Number from $First_line_F
   write #_XML_File_Number from ''
   write #_XML_File_Number from '   write #_XML_File_Number from ' xmlns:o="urn:schemas-microsoft-com:office:office"'
   write #_XML_File_Number from ' xmlns:x="urn:schemas-microsoft-com:office:excel"'
   write #_XML_File_Number from ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'
   write #_XML_File_Number from ' xmlns:html="http://www.w3.org/TR/REC-html40">'

!Hardcode in defaults for now
   write #_XML_File_Number from ' '
   write #_XML_File_Number from '  8700'
   write #_XML_File_Number from '  11355'
   write #_XML_File_Number from '  120'
   write #_XML_File_Number from '  60'
   write #_XML_File_Number from '  815'

   if #ProtectStructure = 0
      write #_XML_File_Number from '  False'
   else
      write #_XML_File_Number from '  True'
   end-if
   if #ProtectWindows = 0
      write #_XML_File_Number from '  False'
   else
      write #_XML_File_Number from '  True'
   end-if
   write #_XML_File_Number from '
'

   let #_xml_first_style = 1             !Set this to True, until the first '
   else
      write #_XML_File_Number from ' '
      let #_xml_first_style = 0
   end-if

   write #_XML_File_Number from '  '
      write #_XML_File_Number from '
'   end-if
end-procedure




!***********************************************************************
! Procedure: xml_finalize_worksheet_pagesetup
! Purpose  : use to finalize worksheet page
!***********************************************************************
begin-procedure xml_finalize_worksheet_pagesetup($Orientation, $Center_Options, #Margin_Top, #Margin_Right, #Margin_Bottom, #Margin_Left, $Header, $Footer)
   do xml_closing_table()

   if substr($Orientation, 1, 1) = 'L' or substr($Orientation, 1, 1) = 'l'
      let $XML_Orientation = ' x:Orientation="Landscape"'
   else
      let $XML_Orientation = ''
   end-if

   let #loc = 0
   find 'H' in $Center_Options 0 #loc
   if #loc <> -1
      let $XML_CenterHor = ' x:CenterHorizontal="1"'
   else
      let $XML_CenterHor = ''
   end-if
   let #loc = 0
   find 'V' in $Center_Options 0 #loc
   if #loc <> -1
      let $XML_CenterVer = ' x:CenterVertical="1"'
   else
      let $XML_CenterVer = ''
   end-if

   let $M1 = rtrim(ltrim(rtrim(edit(#Margin_Bottom,'99.9999'), ' '), ' '), '0')
   let $M2 = rtrim(ltrim(rtrim(edit(#Margin_Left  ,'99.9999'), ' '), ' '), '0')
   let $M3 = rtrim(ltrim(rtrim(edit(#Margin_Right ,'99.9999'), ' '), ' '), '0')
   let $M4 = rtrim(ltrim(rtrim(edit(#Margin_Top   ,'99.9999'), ' '), ' '), '0')

   write #_XML_File_Number from '   '
   write #_XML_File_Number from '    '
   If $Header <> ''
      write #_XML_File_Number from '    

'

   End-If
   If $Footer <> ''
      write #_XML_File_Number from '    

'

   End-If
   write #_XML_File_Number from '    '
   write #_XML_File_Number from '  
'
end-procedure

!***********************************************************************
! Procedure: xml_finalize_worksheet_print
! Purpose  : use to finalize worksheet print
!***********************************************************************
begin-procedure xml_finalize_worksheet_print(#FitWidth, #FitHeight, #Scale, #PageBreakZoom)
   do xml_closing_table()

   if #Scale > 0
      let $XML_Scale = ltrim(rtrim(edit(#scale, '9999'), ' '), ' ')
   else
      let $XML_Scale = '100'
   end-if  

   if #PageBreakZoom > 0
      let $XML_PBZoom = ltrim(rtrim(edit(#PageBreakZoom, '9999'), ' '), ' ')
   else
      let $XML_PBZoom = '100'
   end-if  

   If #FitWidth > 0 Or #FitHeight > 0
      If #Scale = 0
         write #_XML_File_Number from '   '
      End-If
   End-If

   write #_XML_File_Number from '   '
   if #FitWidth > 0
      let $FitWidth = ltrim(rtrim(edit(#FitWidth, '999999'), ' '), ' ')
      write #_XML_File_Number from '    ' $FitWidth ''
   end-if
   if #FitHeight > 0
      let $FitHeight = ltrim(rtrim(edit(#FitHeight, '999999'), ' '), ' ')
      write #_XML_File_Number from '    ' $FitHeight ''
   end-if
   write #_XML_File_Number from '    '
   write #_XML_File_Number from '    ' $XML_Scale ''
   write #_XML_File_Number from '    600'
   write #_XML_File_Number from '    600'
   write #_XML_File_Number from '  
'   write #_XML_File_Number from '   ' $XML_PBZoom ''
   write #_XML_File_Number from '   ' $XML_PBZoom ''
   write #_XML_File_Number from '   '

!   if #ProtectObjects > 0
!      write #_XML_File_Number from '   False'
!   else
!      write #_XML_File_Number from '   True'
!   end-if
!   if #ProtectScenarios > 0
!      write #_XML_File_Number from '   False'
!   else
!      write #_XML_File_Number from '   True'
!   end-if

end-procedure


!***********************************************************************
! Procedure: xml_finalize_worksheet_split
! Purpose  : use to finalize worksheet split
!***********************************************************************
begin-procedure xml_finalize_worksheet_split(#SplitHorizontal, #SplitVertical, #Freeze)
   do xml_closing_table()

   if #Freeze
      write #_XML_File_Number from '   '
   end-if

   if #SplitHorizontal > 0
      let $SplitHorizontalData     = ltrim(rtrim(edit(#SplitHorizontal, '9999999999'), ' '),' ')
      let $TopRowBottomPaneData    = ltrim(rtrim(edit(1, '9999999999'), ' '),' ')
      write #_XML_File_Number from '   ' $SplitHorizontalData ''
      write #_XML_File_Number from '   ' $TopRowBottomPaneData ''
   end-if

   if #SplitVertical > 0
      let $SplitVerticalData       = ltrim(rtrim(edit(#SplitVertical, '9999999999'), ' '),' ')
      let $LeftColumnRightPaneData = ltrim(rtrim(edit(1, '9999999999'), ' '),' ')
      write #_XML_File_Number from '   ' $SplitVerticalData ''
      write #_XML_File_Number from '   ' $LeftColumnRightPaneData ''
   end-if

   if #SplitHorizontal or #SplitVertical
      if #SplitHorizontal and #SplitVertical
         write #_XML_File_Number from '   0'
      else
         if #SplitVertical
            write #_XML_File_Number from '   1'
         else
            write #_XML_File_Number from '   2'
         end-if    
      end-if    
   else
      write #_XML_File_Number from '   3'
   end-if    

   if #SplitHorizontal or #SplitVertical
      write #_XML_File_Number from '   '
      write #_XML_File_Number from '    '
      write #_XML_File_Number from '     3'
      write #_XML_File_Number from '  
'   end-if    
   if #SplitVertical
      write #_XML_File_Number from '    '
      write #_XML_File_Number from '     1'
      write #_XML_File_Number from '  
'   end-if    
   if #SplitHorizontal
      write #_XML_File_Number from '    '
      write #_XML_File_Number from '     2'
      write #_XML_File_Number from '  
'   end-if    
   if #SplitHorizontal and #SplitVertical
      write #_XML_File_Number from '    '
      write #_XML_File_Number from '     0'
      write #_XML_File_Number from '  
'   end-if    
   if #SplitHorizontal or #SplitVertical
      write #_XML_File_Number from '  
'   end-if
end-procedure


!***********************************************************************
! Procedure: xml_finalize_worksheet_allowoptions
! Purpose  : use to finalize worksheet options
!***********************************************************************
begin-procedure xml_finalize_worksheet_allowoptions($Allow_Options)
   do xml_closing_table()

   let $Allow_Options = rtrim(ltrim($Allow_Options,' '),' ')
   if $Allow_Options <> ''
      let #start = 1
      let #location = 0
      while #location <> -1
         let #start = #location + 1
         Find '^' In $Allow_Options #start #location
         if #location = -1
            let #size = (length($Allow_Options) - #start) + 1
         else
            let #size = (#location - #start) + 1
         end-if
         let $Data = ltrim(rtrim(substr($Allow_Options,#start,#size),'^'),'^')
         write #_XML_File_Number from '   '
      end-while
   end-if

   do xml_finalize_pagebreak()
   do xml_finalize_autofilter()
 
end-procedure


!***********************************************************************
! Procedure: xml_finalize_pagebreak
! Purpose  : use to finalize page break
!***********************************************************************
begin-procedure xml_finalize_pagebreak()
!Developer doesn't need to call this function.
!Procedure changed to reset #_xml_pagebreak_entry back to zero near the end
!   Change was for version 1.02
   do xml_closing_worksheetoptions()
   !PageBreak Logic
   if #_xml_pagebreak_entry > 0
      write #_XML_File_Number from '  '
      write #_XML_File_Number from '   '
      let #i = 0
      while #i < #_xml_pagebreak_entry
         get #k from XML_PageBreak(#i) Row
         let $k = rtrim(ltrim(edit(#k, '9999999'),' '),' ')
         write #_XML_File_Number from '    ' $k ''
         add 1 to #i
      end-while
      write #_XML_File_Number from '  
'      write #_XML_File_Number from '
'      let #_xml_pagebreak_entry = 0             !Added April 28, 2005 for Version 1.02
   end-if

end-procedure


!***********************************************************************
! Procedure: xml_finalize_validationlist
! Purpose  : use to finalize validation list
!***********************************************************************
begin-procedure xml_finalize_validationlist($Range,$Type,$List,$InputTitle,$InputMessage)
   do xml_closing_worksheetoptions()

   write #_XML_File_Number from '  '
   write #_XML_File_Number from '   ' $Range ''
   write #_XML_File_Number from '   ' $Type ''
   write #_XML_File_Number from '   '
   write #_XML_File_Number from '   "' $List '"'
   if $InputTitle <> ''
      write #_XML_File_Number from '   ' $InputTitle ''
   end-if
   if $InputMessage <> ''
      write #_XML_File_Number from '   ' $InputMessage ''
   end-if


   write #_XML_File_Number from '
'
end-procedure


!***********************************************************************
! Procedure: xml_finalize_autofilter
! Purpose  : use to finalize autofilter
!***********************************************************************
begin-procedure xml_finalize_autofilter()
!Developer doesn't need to call this function.
   do xml_closing_worksheetoptions()
   do xml_finalize_pagebreak()
   if $_xml_Remember_Auto_Filter_Range <> ''
      write #_XML_File_Number from '        write #_XML_File_Number from '   xmlns="urn:schemas-microsoft-com:office:excel">'
      write #_XML_File_Number from '  '
      let $_xml_Remember_Auto_Filter_Range = ''
   end-if

end-procedure


!***********************************************************************
! Procedure: xml_finalize_workbook
! Purpose  : use to finalize workbook
!***********************************************************************
begin-procedure xml_finalize_workbook()
   do xml_closing_worksheet()
   write #_XML_File_Number from '
'   close #_XML_File_Number
end-procedure

! End of SQC

2) EXCLFILE.SQR

!********************Start of  EXCLFILE.SQR************************

#include 'setenv.sqc'
#include 'setup32.sqc' !Printer and Page size initialization

Begin-setup

End-Setup

!**********************************************************************
! Procedure: Begin-Program
! Purpose  : This Procedure is used to call all required procedures
!***********************************************************************

BEGIN-PROGRAM

  DO Init-DateTime
  DO Init-Number
  DO Get-Current-DateTime
  DO Define-Prcs-Vars
  DO Get-Run-Control-Parms
  Do xml_init_environment () !Initialization  for the xml file writing to generate excel file
  DO Init-Report

  Do Wrap-Up

SHOW 'Program Ended at: ' $SysDateTime


END-PROGRAM

!**********************************************************************





!***********************************************************************
! Procedure: Init-Report
! Purpose  : Initialize the Report
! Called by procedure : Begin-Program
!***********************************************************************

BEGIN-PROCEDURE Init-Report


#debug do Fin-Debug-Msg('Init-Report')



MOVE $SysDateTime to $ReportDateTime
SHOW 'Program started at: ' $SysDateTime
SHOW ' '
Let $Quote = ''''
LET #Flag = 0

do Get-Directory

LET $CHKFILE_NAME = $CHECK_DIR || 'RECORD_Report_Detail'|| '.xls'
Let $WorkSheet_name = 'RECORD_Report_Detail'

Begin-Select on-error = Sql-Error
A.RECNAME &Recname
A.FIELDNAME &FldName
CASE WHEN bitand(A.USEEDIT,1) = 0 THEN ' ' WHEN bitand(A.USEEDIT,1) = 1 THEN 'Y' ELSE ' ' END &KeyFld
A.FIELDNUM &FieldNum
B.FIELDTYPE &FldType
B.LENGTH &FldLength
CASE WHEN bitand(A.USEEDIT,256) = 0 THEN ' ' WHEN bitand(A.USEEDIT,256) = 256 THEN 'Y' ELSE ' ' END  &UseEdit
A.EDITTABLE &Editable
A.DEFRECNAME &DefRecName
A.DEFFIELDNAME &DefFldName


let $Recname = &Recname
  let $FldName = &FldName
let #FieldNum = &FieldNum
let $KeyFld = &KeyFld
Evaluate &FldType
WHEN = 0
let $FldType = 'CHAR'
break
        WHEN = 1
let $FldType = 'LONG CHAR'
break
        WHEN = 2
let $FldType = 'NBR'
break
        WHEN = 3
let $FldType = 'SIGN'
break
        WHEN = 4
let $FldType = 'DATE'
break
        WHEN = 5
let $FldType = 'TIME'
break
        WHEN = 6
let $FldType = 'DTTM'
break
        WHEN = 7
let $FldType = 'IMG'
break
        WHEN = 8
let $FldType = 'VERS'
break
        when-other
let $FldType = ''
break
End-Evaluate
  let #FldLength = &FldLength
let $UseEdit   = &UseEdit
let $Editable  = &Editable
let $DefRecName = &DefRecName
let $DefFldName  = &DefFldName

If #Flag = 0
Do Open-File
Let #Flag = 1
End-If


Do xml_init_row (1,0,0,0,0) !Initialization of row where data will be written

!Write values in cells in the row initialized above
Do xml_cell_char($Recname, 0.01, 1,0)
Do xml_cell_char($FldName, 0.01, 1,0)
Do xml_cell_char($KeyFld, 0.01,1,0)
Do xml_cell_number(#FieldNum,'999,999,999,999.99', 0.01, 27,0)
Do xml_cell_char($FldType, 0.01, 1,0)
Do xml_cell_number(#FldLength,'999,999,999,999.99', 0.01, 27,0)
Do xml_cell_char($UseEdit, 0.01, 1,0)
Do xml_cell_char($Editable, 0.01, 1,0)
Do xml_cell_char($DefRecName, 0.01, 1,0)
Do xml_cell_char($DefFldName, 0.01, 1,0)

FROM   PSRECFIELDDB A, PSDBFIELD B
WHERE  A.RECNAME = 'VOUCHER'
       AND A.FIELDNAME = B.FIELDNAME
ORDER  BY 1,4
end-select

Do xml_finalize_workbook()

END-PROCEDURE !Init-Report

!***********************************************************************






!***********************************************************************
! Procedure: Open-File
! Purpose  : Open File for writing.
!***********************************************************************
BEGIN-PROCEDURE Open-File

  #debug do Fin-Debug-Msg('Open-File')

Let $Encoding_F = 'UTF-8'


Do xml_init_file(10,$CHKFILE_NAME,0,0,$Encoding_F) !Open xml file for writing
Do Setup_XML_styles !Setup styles for cells to write data
Do xml_Init_Worksheet ($WorkSheet_name,0,0) !Initialization for the worksheet where data will be write

!Initialize columns where data will be written
Do Xml_Init_Col(1,0,15,0,0,0)
Do Xml_Init_Col(2,0,18,0,0,0)
Do Xml_Init_Col(3,0,3,0,0,0)
Do Xml_Init_Col(4,0,8,0,0,0)
Do Xml_Init_Col(5,0,10,0,0,0)
Do Xml_Init_Col(6,0,6,0,0,0)
Do Xml_Init_Col(7,0,10,0,0,0)
Do Xml_Init_Col(8,0,15,0,0,0)
Do Xml_Init_Col(9,0,15,0,0,0)
Do Xml_Init_Col(10,0,18,0,0,0)


Do xml_init_row (1,0,0,0,0) !Initialization of row where data will be written

!Write char values for heading in cells in the row initialized above
Do xml_cell_char('Record', 0.01, 20,0)
Do xml_cell_char('Field', 0.01, 20,0)
Do xml_cell_char('Key', 0.01, 20,0)
Do xml_cell_char('FieldNum', 0.01, 20,0)
Do xml_cell_char('Type', 0.01, 20,0)
Do xml_cell_char('Length', 0.01, 20,0)
Do xml_cell_char('Required', 0.01,20,0)
Do xml_cell_char('PromptRecord', 0.01, 20,0)
Do xml_cell_char('DefaultRec', 0.01,20,0)
Do xml_cell_char('DefaultField', 0.01,20,0)

END-PROCEDURE !Open-File
!***********************************************************************



!***********************************************************************
! Procedure: Get-Directory
! Purpose  : Get Directory for placing file.
!***********************************************************************
begin-procedure Get-Directory
#debug do Fin-Debug-Msg('Get-Directory')

begin-select
ORIGOUTDEST
PRCSOUTPUTDIR

 if rtrim (upper(&origoutdest), ' ') = '%%LOG/OUTPUT DIRECTORY%%'
     let $CHECK_DIR = rtrim(&prcsoutputdir, ' ') || '\'
 else
     let $CHECK_DIR = rtrim(&origoutdest, ' ')
 end-if
 from PSPRCSPARMS where
  PRCSINSTANCE = #prcs_process_instance
  end-select


 if isblank($CHECK_DIR)
  let $CHECK_DIR ='{FILEPREFIX}'
 end-if

  #debuge show '$directory: ' $CHECK_DIR

end-procedure !Get-Directory



Begin-Procedure Setup_XML_styles

#debug do Fin-Debug-Msg('Setup_XML_styles')
Let $Format = 'Fixed'
Let $Format1 = 'Standard'
Let $Format2 = '@'
Let $Format3 = 'Short date'

DO xml_init_style (0)
Do xml_style_alignment('Center', 'Bottom', 0, 0)
Do xml_style_border('Top','Continuous',1)
Do xml_style_font('Times New Roman', 12, 1)
do xml_style_interior('#C0C0C0', 'Solid')
Do xml_style_numberformat($Format)
Do xml_style_protection(#Protected, #Hideformula)

DO xml_init_style (1) !Title
Do xml_style_alignment('Center', 'Bottom', 0, 0)
Do xml_style_border('Bottom','Continuous',1)
Do xml_style_border('Left','Continuous',1)
Do xml_style_border('Right','Continuous',1)
Do xml_style_border('Top','Continuous',1)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#FFFFCC', 'Solid')


DO xml_init_style (2)
Do xml_style_alignment('Left', 'Bottom', 0, 0)
Do xml_style_border('Bottom','Continuous',1)
Do xml_style_border('Left','Continuous',1)
Do xml_style_border('Right','Continuous',1)
Do xml_style_border('Top','Continuous',1)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#FFFFFF', 'Solid')

DO xml_init_style (3)
Do xml_style_alignment('Center', 'Bottom', 0, 0)
Do xml_style_border('Bottom','Continuous',1)
Do xml_style_border('Left','Continuous',1)
Do xml_style_border('Right','Continuous',1)
Do xml_style_border('Top','Continuous',1)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#FFFFFF', 'Solid')





DO xml_init_style (16)
Do xml_style_interior('#FFFFCC', 'Solid')


DO xml_init_style (17)
Do xml_style_numberformat($Format3)
Do xml_style_font('Calibri', 11, 0)

DO xml_init_style (18)
Do xml_style_numberformat($Format1)
Do xml_style_border('Bottom','Double',3)
Do xml_style_border('Left','Double',3)
Do xml_style_border('Right','Double',3)
Do xml_style_border('Top','Double',3)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#FFFFCC', 'Solid')


DO xml_init_style (19)
Do xml_style_numberformat($Format2)
Do xml_style_font('Calibri', 11, 0)

DO xml_init_style (20)
Do xml_style_border('Bottom','Double',3)
Do xml_style_border('Left','Double',3)
Do xml_style_border('Right','Double',3)
Do xml_style_border('Top','Double',3)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#C0C0C0', 'Solid')
Do xml_style_alignment('Right', 'Bottom', 0, 0)

DO xml_init_style (21)
Do xml_style_numberformat($Format1)
Do xml_style_font('Calibri', 11, 0)

DO xml_init_style (27)
Do xml_style_alignment('Right', 'Bottom', 0, 0)
Do xml_style_font('Calibri', 11, 0)
Do xml_style_interior('#FFFFCC', 'Solid')
Do xml_style_numberformat($Format)

DO xml_init_style (28)
Do xml_style_alignment('Left', 'Bottom', 0, 0)
Do xml_style_font('Calibri', 11, 0)

DO xml_init_style (29)
Do xml_style_alignment('Right', 'Bottom', 0, 0)
Do xml_style_font('Calibri', 11, 0)
Do xml_style_numberformat($Format)

DO xml_init_style (30)
Do xml_style_interior('#CCFFCC', 'Solid')

DO xml_init_style (32)
Do xml_style_alignment('Right', 'Bottom', 0, 0)
Do xml_style_font('Calibri', 11, 0)
Do xml_style_interior('#CCFFCC', 'Solid')
Do xml_style_numberformat($Format)


DO xml_init_style (39)
Do xml_style_alignment('Center', 'Bottom', 0, 0)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#00B0F0', 'Solid')


DO xml_init_style (46)
Do xml_style_font('Calibri', 11, 1)
Do xml_style_interior('#CCFFCC', 'Solid')


Do xml_finalize_style()

End-Procedure !Setup_XML_styles
!***********************************************************************


!***********************************************************************
! Procedure: Wrap-Up
! Purpose  : Update the Process Run Status
!***********************************************************************

Begin-Procedure Wrap-Up
 #debug do Fin-Debug-Msg('Wrap-Up')

  Display '*** End Of Report ***'

  if $prcs_process_instance <> ''
     do Update-Prcs-Run-Status
  end-if


end-procedure
!***********************************************************************


!***********************************************************************
! Procedure: Fin-Debug-Msg
! Purpose  : Fin-Debug-Msg
!***********************************************************************

begin-procedure Fin-Debug-Msg($procedure_name)
   display ' '
   display '----------------------------------'
   display $procedure_name
   #debugt date-time () {Native-DateTime} &SysDateTime
   #debugt move &SysDateTime to $SysDateTime
   #debugt show 'TIMING, ' $procedure_name ', ' $SysDateTime
   display ' '
end-procedure ! Fin-Debug-Msg


!***********************************************************************

#include 'prcsapi.sqc'
#include 'prcsdef.sqc'
#include 'reset.sqc'    !reset printer
#include 'curdttim.sqc' !Get-Current-DateTime procedure
#include 'datetime.sqc' !Routines for date and time formatting
#include 'number.sqc'   !Routines to format numbers
#include 'datemath.sqc' !Converts dates into correct format
#include 'validdt.sqc'  !Checks a date in native format for validity
#include 'eoj.sqc'      !End-Of-Job
#include 'sqrtrans.sqc'
#include 'readxlat.sqc'
#include 'ExclWrite.sqc'  !To write date in Excel format
!***********************************************************************
!************************End of EXCLFILE.SQR**********************************

Discover Financials 9.2 using the new features of PS Query

Discover Financials 9.2 using the new features of PS Query
 

Asynchronous Integration broker service operation handler PeopleCode

Following is a sample PeopleCode for the Asynchronous Integration broker service operation handler, which loads message data into target record. In this Sample PeopleCode “TEST_DATA_TBL” is the record name which is target record and same used in the IB Message. This is simple example of integration broker service handler which takes care of only zero level data.

 /* Receive Message */

import PS_PT:Integration:INotificationHandler;

class TEST_HANDLER implements PS_PT:Integration:INotificationHandler
method OnNotify(&_Msg As Message);
private
method CopyFieldsToFunc(&SourceRec As Record, &TargetRec As Record);
end-class;



method OnNotify

/+ &_Msg as Message +/

/+ Extends/implements PS_PT:Integration:INotificationHandler.OnNotify +/

Local integer &i;
Local Rowset &RsMsgHdr, &RsRecHdr;
Local Row &RwMsgHdr;
Local Record &RcMsgHdr;
Local Record &RcStgHdr;
Local Message &Msg;


&Msg = &_Msg;
&RsMsgHdr = &Msg.GetRowset();
&RsRecHdr = CreateRowset(Record.TEST_DATA_TBL);
&RsMsgHdr.CopyTo(&RsRecHdr);

For &i = 1 To &RsRecHdr.ActiveRowCount

&RwMsgHdr = &RsRecHdr.GetRow(&i);
&RcMsgHdr = &RwMsgHdr.GetRecord(Record.TEST_DATA_TBL);
&RcStgHdr = CreateRecord(Record.TEST_DATA_TBL);
%This.CopyFieldsToFunc(&RcMsgHdr, &RcStgHdr);

End-For;

end-method;


method CopyFieldsToFunc

/+ &SourceRec as Record, +/

/+ &TargetRec as Record +/

Local integer &i, &j;
Local string &Empl, &EmplFound;

For &i = 1 To &SourceRec.FieldCount

For &j = 1 To &TargetRec.FieldCount

If &SourceRec.GetField(&i).Name = &TargetRec.GetField(&j).Name Then

   &TargetRec.GetField(&j).Value = &SourceRec.GetField(&i).Value;

End-If;

End-For;

End-For;



&Empl = &TargetRec.GetField(Field.EMPLID).Value;

SQLExec("SELECT 'Y' FROM PS_TEST_DATA_TBL WHERE EMPLID = :1 ", &Empl, &EmplFound);


If All(&EmplFound) Then

&TargetRec.Update();

Else

&TargetRec.Insert();

End-If;

end-method;

Generate Excel file using PeopleCode/Application engine

To generate Excel file using PeopleCode make sure that any Microsoft excel template file exists in the excel folder. For example in the below PeopleCode I am using “template.xlt” Microsoft excel template file, if you are not using any formula in the Excel sheet then in the template file data type of all the cell should be TEXT to avoide eliminating leading zero from any number which should be treated as string. And one more thing while generating excel file make sure that code is being executed in the server where Microsoft excel is installed. For example if you are generating excel file using application engine then make sure process should be executed in PSNT process scheduler server where Microsoft excel has been already installed as in Unix server it will not work and process error out. Similarly if using normal PeopleCode then make sure your Application server is in PSNT and Microsoft excel is already installed.

----------------SAMPLE CODE----------------------

Local object &oWorkApp, &oWorkBook;

Local PostReport &RPTINFO;

Local number &RPTINSTANCE;

&PrcsInstace = TEST_TREEDT_AET.PROCESS_INSTANCE;
&SetID = TEST_TREEDT_AET.SETID;
&TreeName = TEST_TREEDT_AET.TREE_NAME;
&EffDt = String(TEST_TREEDT_AET.EFFDT);
&Field = TEST_TREEDT_AET.FIELDNAME;


SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist WHERE prcsinstance = :1", &PrcsInstace, &Server);


&find = 0;
&find = Find("NT", &Server);

If &find <> 0 Then
&reportPath = GetEnv("PS_HOME") |"\excel\";
&reportOutPutPath = GetEnv("PS_SERVDIR") |"\log_output\AE_TEST_TREEDTL_" |&PrcsInstace |"\" |&SetID |"-" |&TreeName|"-" |&EffDt |"-" |&PrcsInstace |".xls";
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open(&reportPath |"template.xlt");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs(&reportOutPutPath);


&oWorkSheet.Cells(1, 1).Value = "Set ID";
&oWorkSheet.Cells(1, 1).Font.Bold = True;
&oWorkSheet.Cells(1, 2).Value = &SetID;
&oWorkSheet.Cells(1, 2).Font.Bold = True;


&oWorkSheet.Cells(2, 1).Value = "Tree Name";
&oWorkSheet.Cells(2, 1).Font.Bold = True;
&oWorkSheet.Cells(2, 2).Value = &TreeName;
&oWorkSheet.Cells(2, 2).Font.Bold = True;

&oWorkSheet.Cells(3, 1).Value = "Effective Date";
&oWorkSheet.Cells(3, 1).Font.Bold = True;
&oWorkSheet.Cells(3, 2).Value = &EffDt;
&oWorkSheet.Cells(3, 2).Font.Bold = True;



&SQL1 = CreateSQL("SELECT DISTINCT DESCR254||'>' , CHARTFIELD_VALUE ,DESCR,Length(DESCR254)-Length(replace(DESCR254,'>',null))+1 FROM %TABLE(TEST_TRDT_TMP) WHERE PROCESS_INSTANCE = :1 ORDER BY 1,2", &PrcsInstace);

SQLExec("SELECT DISTINCT B.LONGNAME FROM PSDBFLDLABL A, PSDBFLDLABLLANG B WHERE A.FIELDNAME = B.FIELDNAME AND A.LABEL_ID = B.LABEL_ID AND A.DEFAULT_LABEL = '1' AND A.FIELDNAME = :1", &Field, &FieldLabel);

SQLExec("SELECT Max(Length(DESCR254)-Length(replace(DESCR254,'>',null)))+1 FROM %TABLE(TEST_TRDT_TMP) WHERE PROCESS_INSTANCE = :1 ", &PrcsInstace, &MaxNodeLevel);

If None(&FieldLabel) Then
     &FieldLabel = "Tree leaf";
End-If;


For &j = 1 To &MaxNodeLevel
   &oWorkSheet.Cells(5, &j).Value = "Node Number " |String(&j);
   &oWorkSheet.Cells(5, &j).Font.Bold = True;
End-For;

&oWorkSheet.Cells(5, &MaxNodeLevel + 1).Value = &FieldLabel;
&oWorkSheet.Cells(5, &MaxNodeLevel + 1).Font.Bold = True;
&oWorkSheet.Cells(5, &MaxNodeLevel + 2).Value = "Description";
&oWorkSheet.Cells(5, &MaxNodeLevel + 2).Font.Bold = True;

/*Write detail information*/
&i = 6;
While &SQL1.Fetch(&Path, &ChartField, &Decr, &CurrNodeLevel)

For &j = 1 To &CurrNodeLevel
&PathLength = Len(&Path);
&find = Find(">", &Path);
&NodeLevel = Left(&Path, &find - 1);
&Path = Right(&Path, &PathLength - &find);
&oWorkSheet.Cells(&i, &j).Value = &NodeLevel;
End-For;

&oWorkSheet.Cells(&i, &MaxNodeLevel + 1).VALUE = &ChartField;
&oWorkSheet.Cells(&i, &MaxNodeLevel + 2).Value = &Decr;
&i = &i + 1;

End-While;

/* Save Excel file and quit */

&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.Quit();

Else

MessageBox(0, "", 0, 0, "Process is not being executed in NT Server");

End-If;

----------------------------------------------------------------------------------------