File Transfer Utility - Simplicity of Design

XXFDXFR.SQR - Generic Multi-Purpose File Transfer Utility

The secret to effective program design is isolating exactly what the requirements are (this includes anticipating future needs), streamlining the process by eliminating unnecessary complexity & delivering a solid product that is 100% reliable & very simple to use.

Here's a basic example. Our organization has a need to transfer different files via email from our file server to users/groups. They can be external files from vendors such as invoice information, security permissions for the administrator distribution group, output from other processes, monthly data extracts, etc. Regardless, the task at hand is to identify file X at folder location Y and email it to distribution group Z.

For any circumstance I'll need - a Transfer Definition, Email Distribution, Run Control & File Watcher/Interface Component - and finally, a generic transfer program (XXFDXFR) to utilize all of these components to fulfill the requirement.

Note - The File Watcher/Interface Component is our custom 24/7 Interface/Process Request Loader. It's extremely powerful - in general, within 5 minutes (or less) it links a file with a process & launches it. It can handle hundreds of files in multiple folder locations in an instant (part of our pre-Go-Live stress test). I developed the PeopleSoft/Oracle/API Components in 2004 (with Chuck Hamilton writing the underlying Unix shell scripts & cron job). Still running strong...

File Transfer Definition - Defines Key/Email Information:

Xfer Key: SPO_ERR
 Subject: ADP/SharePoint Security *ERRORS* <YMD> - [<DBASE>]
    Text: Please review the attached ADP/SharePoint ERROR file for <MDY>^^File: <FFULL>^^

Email Distribution - Directs output to PeopleSoft Oprid OR Email Address:

 Pgm Key: XXFDXFR     XXFDXFR
Xfer Key: SPO_ERR     SPO_ERR
    Type: P           E
   Value: TDELIA      user@email.com


Process Run Control - Passes Transfer Key to Process:

   Oprid: PSBATCH
Run Cntl: FD_XFR_SPOERR
Xfer Key: SPO_ERR

File Watcher Configuration - Detects File & Launches Process/Run Control:

    File: FDXSPO*.*
 Process: XXFDXFR
   Oprid: PSBATCH
Run Cntl: FD_XFR_SPOERR
  Folder: /app/iface/<DBASE>/ALL/AP/inbound
Day/Time: 24/7

XXFDXFR.SQR - File Transfer Program:

...
do IDB_Init('I', $RC_entity, 'AP', $RC_file)

do Process_Main

do IDB_Term
...

#include 'xx_tdf.sqc'    !TD Custom SQR Function Library
#include 'xx_idb.sqc'    !TD Interface Dashboard API
 

The XXFDXFR program (above) is launched via the File Watcher/Interface mechanism... i.e. minutes after the file is dropped in the folder. The core of the program is as follows:

IDB_Init() - Accepts the file that was detected via the File Watcher definition.
Process_Main - Reads the run control, assigns the Xfer Key & emails to the distribution.
IDB_Term - Automatically archives the input file.

File Watcher Configuration Page:

Above is the actual File Watcher Configuration page. It is also known as the IDB Scheduler (Interface Dashboard Scheduler) due to it's extensive interface audit capabilities.

Above is a sample screenshot of XXFDXFR on Process Monitor. Process instance 5106203 at 6:40AM contains the SPO_ERR Transfer we're using as an example.

The email with the file attachment has been successfully sent.

The subject & email body have been derived from the initial File Transfer Defintion for the key SPO_ERR. Notice the embedded tokens have been resolved in the Subject & Email Body:

<YMD> set to 2019-03-28
<DBASE> set to NSFSPRD
<MDY> set to 03-28-2019
<FFULL> set to Full Path/Filename of the attached file.
"^" Character set to CRLF.

Any additional file transfers that may be required are simply a matter of configuration without unnecessary development to engage in.

WinBatch - SQR Background Processor/Analyzer

This blog post highlights the features and usage of a WinBatch Utility I created years ago - TDSQRW - SQR Background Processor / Analyzer. It provides an Easy-to-Use GUI Interface that selects an SQR program & easily executes it directly via the SQRW.exe background executable. This is an alternate means of running a program outside of PeopleSoft Process Scheduler. The utility is also useful for analyzing SQR source code (including embedded SQC routines) & formatting the output in Excel. Perfect for documenting large, complex processes. Here's some of the features:

SQR Background Processor Functions:

1 – Extract all PeopleSoft profiles (Configuration Manager) for selection.

2 – Ability to alter Configuration settings (temporary for application only).

3 – Execute SQRW utilizing correct set of parameters. 

4 – Analyze SQR Source Code (Includes, Procedures, Logic Flow, SQL)

Analyzer Functions include:

1 – INCLUDE – All Include files are expanded and listed.

2 – ROUTINES -  Every Procedure is listed with Module of origin and called routines.

3 – FLOWCHART – Process Flowchart created highlighting procedural logic.

4 – SQL – SQL statements extracted and listed with Procedure/Module of origin (i.e. SQR,SQC).

All sheets are automatically generated with appropriate Report Headings, Freeze Pane, AutoFilter, Colorization, etc…

System Requirements and Assumptions:

A directory named C:\TEMP is assumed to exist (All generated EXCEL output is placed in this folder). PeopleSoft is installed via client setup to utilize profile selection (process may still run without this in place). Microsoft Excel must be installed for the Analyzer - an Excel session opens up automatically & populates four worksheet tabs with the analysis output.

MAIN Screen:

Use the File List Box to select the SQR source code file. To execute via <SUBMIT> the ID, Password and Database are required. Select a Profile and hit <REFRESH> or <EDIT> to update manually – this will ensure the appropriate SQR search variables and executable location used. <ANALYZE> will evaluate the selected SQR program (no ID/Password/Database required).

The buttons labeled SQR1 thru SQR4 are navigation shortcuts to the directories specified by PSSQR1 thru PSSQR4 (for the selected profile).

The fish graphic is a watercolor I painted a while ago (it can be replaced with anything named TDSQRW.BMP). At work this utility was simply known as "The Fish"... as in "run through Process Scheduler or the Fish".

Configuration (via Refresh or Edit):

The environment variables set according to the configuration manager profile may be altered using the Configuration Screen (Accessed by using the <REFRESH> or <EDIT> functions.

View Command Line:

You may view the SQRW command line by pressing the <VIEW> button. In this example you can see the USERID and PASSWORD have not been selected yet.

Online Help:

Press the <HELP> button for some basic assistance…

SQR Analyzer:

 INCLUDE Phase:

All #Include files are expanded utilizing the PSSQR1 thru PSSQR4 file search settings. All expanded files are listed on the first worksheet – an expanded source code file will reside in the C:\TEMP directory with a file extension of .SQZ.

ROUTINES Phase:

The ROUTINES phase will create a new worksheet with all Procedures listed along with any called routines. The module of origin is also listed. AutoFilter is automatically incorporated into the sheet.

 FLOWCHART Phase:

The FLOWCHART phase produces an indented flowchart of the procedural logic of the program. Any repeated procedures will be terminated with <R> - this avoids redundancy and potential endless loops when deciphering the process logic.

SQL Phase:

All SQL statements are extracted, parsed appropriately and displayed on the SQL worksheet. AutoFilter is very useful for pinpointing the exact location of a SQL statement.

TDSQRW.WBT - Source Code Snippets:

Below you'll find some WinBatch Code snippets to illustrate how the application is written. Looks like I'll need to update the PeopleTools Registry key address since we're on a higher release. This allows you to access the various Configuration Profiles. Further down in the code you can see how the Button Selections are handled.

;**********************************************************************
;*       PROCESS MAIN                                                 *
;**********************************************************************

gosub Define_Dialog_TD

; No Listbox Selection Required
IntControl(4,0,0,0,0)

; Restrict Multiple Listbox Selection (only one allowed)
IntControl(33,0,0,0,0)

ErrorMode(@OFF)
Profiles      = ""
Rpath         = "SOFTWARE\PeopleSoft\PeopleTools\Release8.40\Profiles"
Rkey          = RegOpenKey(@REGMACHINE, Rpath)
Rerr          = LastError()
if Rerr      == 0
   Profiles   = RegQueryKeys(Rkey)
   RegCloseKey(Rkey)
endif
ErrorMode(@CANCEL)

gosub Custom_Init

IDXdir        = xSQR1
ErrorMode(@OFF)
DirChange(IDXdir)
ErrorMode(@CANCEL)

zMsg          = "[Enter Parameters please]"

sw            = @YES

while sw     == @YES

   zListBox   = Profiles
   zFileBox   = DirGet()
   zMsg       = StrCat("PATH: ", zFileBox, "  ", zMsg)

   ButtonPushed=Dialog("TD")

   zMsg       = ""

   Select ButtonPushed

      Case 0

         sw   = @NO            ; Documentation Only (See :CANCEL)

         break

      Case 1                   ; Submit

         gosub Process_Submit
         break

      Case 2                   ; Refresh

         gosub Process_Refresh
         gosub Process_Edit
         break

      Case 3                   ; Edit

         gosub Process_Edit
         break

      Case 4                   ; Analyze

         gosub Process_Analyze
         break

      Case 5                   ; Help

         gosub Process_Help
         break

      Case 6
      Case 7
      Case 8
      Case 9

         sqrX = ButtonPushed - 5
         sqrD = xSQR%sqrX%
         ErrorMode(@OFF)
         DirChange(sqrD)
         ErrorMode(@CANCEL)

    EndSelect

endwhile

:CANCEL

exit

;**********************************************************************


The Process_Analyze routine is the fun part of the program. This is where the source module text is sliced, diced, reassembled, parsed & flowcharted.

;**********************************************************************
;*      Process Analyze                                               *
;**********************************************************************

:Process_Analyze

wPGM                    = zFileBox

if wPGM                == "" | wPGM == "NOFILESELECTED"
   zMsg                 = "[Please select a program]"
else

   timeRaw              = StrReplace(TimeYmdHms(),":","")
   timeYmd              = StrSub(timeRaw,1,8)
   timeHms              = StrSub(timeRaw,9,6)
   TDdate               = StrCat(timeYmd,"_",timeHms)

   XLobj                = ObjectOpen("Excel.Application")

   XLobj.DisplayAlerts  = @FALSE
   XLobj.Visible        = @TRUE
   XLobj.UserControl    = @TRUE

   WBS                  = XLobj.Workbooks
   XLS                  = WBS.add
   WS                   = XLobj.Worksheets
   WKS                  = WS.add
   WKS                  = WS.add
   WKS                  = WS.add

   PRTname              = pGetDefPrtInf(1)    ; Name Request
   PRTport              = pGetDefPrtInf(3)    ; Port Request

   PRTactive            = "%PRTname% on %PRTport%"

   wROOT                = FileRoot(wPGM)

   gosub Expand_Source
   gosub Analyze_Routines
   gosub SQR_Flowchart
   gosub SQL_Extract

   XLobj.UserControl    = @FALSE

   XLS.SaveAs("c:\temp\tdsqrw_%wROOT%_%TDdate%.xls")
   XLobj.Quit
   ObjectClose(WKS)
   ObjectClose(WS)
   ObjectClose(XLS)
   ObjectClose(WBS)
   ObjectClose(XLobj)

endif

Return

;**********************************************************************

The most interesting part of the Analyzer is creating the Process Flowchart. This is an indented Tree Structure that visually displays the Parent/Child relationship between a procedure or function & the procedures/functions that they may call.

;**********************************************************************
;*      SQR Flowchart                                                 *
;**********************************************************************

:SQR_Flowchart

ctr                    = 1

TDXLS_SheetName(XLobj, 3, "FLOWCHART")
TDXLS_SheetActivate(XLobj,3)

TDXLS_FormatColumn(XLobj,  1,  30,  "@")
col                    = 2
while col             <= 20
   TDXLS_FormatColumn(XLobj,  col,   3,  "@")
   col                 = col + 1
endwhile

TDXLS_WriteCell(XLobj, ctr, 1, @TRUE, @TRUE,  0, "V", "Module")
TDXLS_WriteCell(XLobj, ctr, 3, @TRUE, @TRUE,  0, "V", "Process Flowchart")

TDXLS_ColorRow(XLobj,  ctr, 1, 23, 36)

ctr                    = ctr + 2

; Flowchart BEG...

S_ptr                  = ArrDimension(30)
S_sub                  = ArrDimension(30)
S_idx                  = 0
while S_idx            < 30
   S_ptr [S_idx]       = 0
   S_sub [S_idx]       = ""
   S_idx               = S_idx + 1
endwhile

S_idx                  = 1

while S_idx            > 0

   Z_idx               = S_ptr  [S_idx]
   Z_sub               = S_sub  [S_idx]

   Y_proc              = A_proc [Z_idx]
   Y_sub               = A_sub  [Z_idx]
   Y_tag               = A_tag  [Z_idx]
   Y_type              = A_type [Z_idx]
   Y_mod               = A_mod  [Z_idx]

   Z_ptr               = Z_idx
   Z_idx               = Z_idx + 1

   S_ptr [S_idx]       = Z_idx

   Y_procU             = StrUpper(Y_proc)
   Z_subU              = StrUpper(Z_sub)

   Z_go                = @FALSE

   if Y_procU         == PROC_S & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_H & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_F & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_R & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == Z_subU & Y_proc <> ""
      Z_go             = @TRUE
   endif

   if Z_go            == @TRUE

      if Y_tag        == 0

         I_sub         = Y_sub
         if Y_type    == K_error
            I_sub      = StrCat("On-Error=", I_sub)
         endif
         col           = S_idx + 2
         TDXLS_WriteCell(XLobj, ctr,   1, @FALSE, @FALSE,  0, "V", Y_mod)
         TDXLS_WriteCell(XLobj, ctr, col, @FALSE, @FALSE,  0, "V", I_sub)
         SVrow         = ctr
         SVcol         = col
         ctr           = ctr + 1

         S_idx         = S_idx + 1
         Z_idx         = 0

         S_ptr [S_idx] = Z_idx
         S_sub [S_idx] = Y_sub

      else

         if Y_tag      > 0
            I_sub      = TDXLS_GetValue(XLobj, SVrow, SVcol, @FALSE)
            I_sub      = StrCat(I_sub, " <R>")
            TDXLS_WriteCell(XLobj, SVrow, SVcol, @FALSE, @FALSE,  0, "V", I_sub)
         endif

         Z_idx         = A_idx + 1

      endif

      A_tag [Z_ptr]    = Y_tag + 1

   endif

   if Z_idx            > A_idx
      S_idx            = S_idx - 1
   endif

endwhile

; Flowchart END

TDXLS_Zoom(XLobj, 70)
TDXLS_Orientation(XLobj, "P")
TDXLS_GridLines(XLobj, @TRUE)
TDXLS_PaperSize(XLobj, 1)                  ; Letter (Default)
TDXLS_AutoFilter(XLobj, 2, 1, ctr, 1)
TDXLS_FreezePane(XLobj, 3, 1)
TDXLS_TitleRows(XLobj, 1, 2)

TDXLS_HeaderFooter(XLobj, "H", "L", "TDSQRW - %wROOT% (%TDdate%) - FLOWCHART")
TDXLS_HeaderFooter(XLobj, "H", "R", "NSS/Advance Publications [TD]")
TDXLS_HeaderFooter(XLobj, "F", "L", "Page &P of &N")
TDXLS_HeaderFooter(XLobj, "F", "R", "&D &T")

Return

;**********************************************************************

The Excel Integration is handled by a custom copybook I wrote - TDXLS.WBC. It includes routines to perform various XLS functions - each starting with the prefix TDXLS_%. Functions are listed below with a short description:

;**********************************************************************
;*                                                                    *
;*       MODULE:  TDXLS.WBC                                           *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH MICROSOFT EXCEL SUPPORT.                   *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE CONTAINS ROUTINES TO ACCESS MICROSOFT   *
;*                EXCEL OBJECTS/PROPERTIES/METHODS. THIS INCLUDES THE *
;*                RETRIEVAL/ASSIGNMENT OF CELL VALUES, CELL XLATION,  *
;*                COLORIZATION AND CELL/COLUMN FORMATTING. ADDITIONAL *
;*                FUNCTIONALITY WILL BE IMPLEMENTED SOON.             *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  TDXLS_GetValue()      - Extract Cell Value.         *
;*                TDXLS_GetFormula()    - Extract Cell Formula.       *
;*                TDXLS_WriteCell()     - Write Cell Value/Formula.   *
;*                TDXLS_ColumnXlate()   - Numeric-to-Alpha Col Xlt.   *
;*                TDXLS_ColorRow()      - Color Row.                  *
;*                TDXLS_FormatCell()    - Format Cell.                *
;*                TDXLS_FormatColWrap() - Format Column.              *
;*                TDXLS_AutoFit()       - Autofit Column(s).          *
;*                TDXLS_AutoFitRows()   - Autofit Row(s).             *
;*                TDXLS_TitleRows()     - Title Rows Page Setup.      *
;*                TDXLS_Orientation()   - Landscape/Portrait.         *
;*                TDXLS_Zoom()          - Zoom Page Size (%).         *
;*                TDXLS_HeaderFooter()  - Set Header/Footer.          *
;*                TDXLS_PrintArea()     - Set PrintArea.              *
;*                TDXLS_GridLines()     - Print Grid Lines.           *
;*                TDXLS_PaperSize()     - Set Paper Size.             *
;*                TDXLS_ActivePrinter() - Set Active Printer.         *
;*                TDXLS_FreezePane()    - Freeze Window Pane.         *
;*                TDXLS_SheetName()     - Assign Sheet Name.          *
;*                TDXLS_SheetActivate() - Activate Sheet.             *
;*                TDXLS_AutoFilter()    - Set AutoFilter for Row.     *
;*                                                                    *
;**********************************************************************

Below is an example of one of the XLS functions - TDXLS_AutoFilter(). Each function is concisely documented listing the Input/Output parameters.

;**********************************************************************
;*       AUTOFILTER FUNCTION                                          *
;**********************************************************************
;*                                                                    *
;*        INPUT: I_XLS      - XL Object                               *
;*               I_rowX     - X-Coordinate Row                        *
;*               I_colX     - X-Coordinate Column                     *
;*               I_rowY     - Y-Coordinate Row                        *
;*               I_colY     - Y-Coordinate Column                     *
;*       OUTPUT: n/a        - None (Set to @TRUE)                     *
;*                                                                    *
;**********************************************************************

#DefineFunction TDXLS_AutoFilter(I_XLS, I_rowX, I_colX, I_rowY, I_colY)

colX             = TDXLS_ColumnXlate(I_colX)
colY             = TDXLS_ColumnXlate(I_colY)

myRange          = I_XLS.Range("$%colX%$%I_rowX%:$%colY%$%I_rowY%")
myRange.Select

mySel            = I_XLS.Selection
mySel.AutoFilter

ObjectClose(mySel)
ObjectClose(myRange)

return @TRUE

#EndFunction

;**********************************************************************

Notice all code is streamlined, clean & crisp, well-documented & efficient... I do not permit a single line of sloppy code... not under any circumstance... ever! We'll end this post on that note...