WinBatch PeopleSoft Adapter - Custom Made

Here's another of one my interesting technical achievements - this time from 2004 - I mentioned this briefly in my initial blogpost.

Configuring WinBatch as an Executable PeopleSoft Process Type:

PeopleSoft is configured to run process types like SQR, Application Engine, COBOL, Crystal Reports, BI Publisher, etc... but I decided to add a new one to fulfill a business requirement (integrated with MS Excel). WinBatch is a robust windows based scripting language that can be compiled & executed. Perfect for this situation & many more. I created a WinBatch PeopleSoft adapter that connects & executes just as smoothly as the delivered process types. My custom package contains the primary Adapter Module (FTPSWBT) along with functions for Command Line Argument Parsing (FTPSARG), Oracle Connectivity/SQL (FTPSORA), Process Scheduler Integration (FTPSAPI), Excel Functionality (FTPSXLS) & Log File Support (FTPSLOG).

NOTE - The sample pages are from 2004 using PeopleSoft 8.0 run via Citrix. They have the client/server look & feel as opposed to the PIA aesthetics.


Custom Process Type Definition

The WinBatch process type is defined using my FTPSWBT.exe driver module and associated command flags. All flags are delivered PeopleSoft-maintained environment variables and provide seamless and secure access to PeopleSoft.

Above: Process Type Definition - WinBatch using FTPSWBT.exe driver module.

Full Parameter List:

-WW%%PS_HOME%%\USER\WBX         -WX%%PRCSNAME%%
-WD%%DBNAME%%                   -WU%%ACCESSID%%
-WP%%ACCESSPSWD%%               -WI%%INSTANCE%%
-WR%%RUNCNTLID%%                -WO%%OPRID%%
-WL%OutputDirectory%

Custom Process Definition(s):

The individual WinBatch process definitions are no different than other delivered process definitions (SQL, Application Engine, COBOL, etc). The only exception to this would be the specification of WinBatch command line flags on the Override Options page.

Above: The Process Definition Panel - WinBatch Process FTAPLOAD.


WinBatch Adapter In Action:

All WinBatch processes are regulated by the FTPSWBT Adapter. A log file is written to the %OutputDirectory% that displays the results of the control transfer. The format of the main module log is: WB_FTPSWBT_<process_instance>.log. All other logs (actual WinBatch application) will be named WB_<processname>_<process_instance>.log by default.

Above: When a process is launched control is initially assumed by the FTPSWBT driver. Once all parameters have been extracted, validated & connectivity established the FTAPLOAD process is launched.

The process FTAPLOAD takes control & uses the same series of WinBatch API routines as the FTPSWBT driver to communicate with Process Scheduler.

Above: The FTAPLOAD version of the log file with specific application level messages..

The new WinBatch Process Type is fully integrated with PeopleSoft Process Scheduler & leverages all functionality & security.

Above: Process Monitor showing the FTAPLOAD WinBatch Process Instance - Success.

WinBatch Script Example - FTAPLOAD (Using API Modules)

Below is a portion of the WinBatch script developed for the AP Spreadsheet Upload. The required API Copybooks are included at the beginning of the program (after program documentation). Next the standard API integration functions are performed in the logical sequence. FTPSARG_Execute() to assign PeopleSoft environment variables. FTPSLOG_Init(), FTPSLOG_Open() and FTPSLOG_Write() for Log Output. FTPSORA_SignON() to connect to the Oracle database. FTPSAPI_Init() and FTPSAPI_Update() to initialize Process Monitor and set the status to “Processing”. Next the specific Application Routines are coded for the AP Spreadsheet upload. Once complete the API routines are called to set the process status to complete (FTPSAPI_Update()), sign-off Oracle (FTPSORA_SignOff() and complete Log file generation (FTPSLOG_Write() and FTPSLOG_Close()).

FTAPLOAD.WBT - AP XLS Upload Test

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTAPLOAD.WBT                                        *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH - ACE AP SPREADSHEET UPLOAD.               *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  AP SPREADSHEET UPLOAD.                              *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*    COMMAND LINE ARGUMENTS/FLAGS (VIA PROCESS SCHEDULER)            *
;*    ============================================================    *
;*                                                                    *
;*    -WW%%PS_HOME%%\USER\WBX  - Executable Location -> WB_dir        *
;*    -WX%%PRCSNAME%%          - Process Name        -> WB_pgm        *
;*    -WD%%DBNAME%%            - Database            -> WB_dbase      *
;*    -WU%%ACCESSID%%          - Userid              -> WB_user       *
;*    -WP%%ACCESSPSWD%%        - Password            -> WB_pswd       *
;*    -WI%%INSTANCE%%          - Process Instance    -> WB_pi         *
;*    -WR%%RUNCNTLID%%         - Run Control ID      -> WB_rc         *
;*    -WO%%OPRID%%             - Operator ID         -> WB_oprid      *
;*    -WL%OutputDirectory%     - Log Output          -> WB_log        *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*       TABLES:  ps_ft_ap_xls_rc    - Select                         *
;*                ps_ft_ap_xls       - Insert                         *
;*                ps_ft_ap_xls_dtl   - Insert                         *
;*                ps_speedchart_hdr  - Select                         *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    EXTENDERS:                                                      *
;**********************************************************************
;*                                                                    *
;*    NO EXTENDERS REQUIRED.                                          *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    REVISIONS:                                                      *
;**********************************************************************
;*                                                                    *
;*   DATE     PROGRAMMER      DESCRIPTION                             *
;* ---------- --------------- --------------------------------------- *
;*                                                                    *
;* 03/18/2004 DELIA,TONY      ORIGINAL CODING.                        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*       UDF/SUBROUTINES                                              *
;**********************************************************************

#include "FTPSARG.WBC"
#include "FTPSORA.WBC"
#include "FTPSAPI.WBC"
#include "FTPSLOG.WBC"
#include "FTPSXLS.WBC"

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

FTPSARG_Execute()

FTPSLOG_Init()
FTPSLOG_Open("%WB_pgm%")
FTPSLOG_Write("%WB_pgm% - Procedure Initiated")

if FTPSORA_SignOn()

   FTPSLOG_Write("Connected to Oracle Database (%WB_dbase%)")

   FTPSAPI_Init()

   PRCS_stat      = PRCS_stat_process
   PRCS_msg_parm1 = "FTAPLOAD Processing"
   FTPSAPI_Update()

;  MAIN Process - Begin

   gosub Get_Run_Controls
   gosub Construct_SQL
   gosub Process_Main

;  MAIN Process - End

   PRCS_stat      = PRCS_stat_success
   PRCS_msg_parm1 = "FTAPLOAD Complete"
   FTPSAPI_Update()

   FTPSLOG_Write("Process completed successfully")

   FTPSORA_SignOff()

else

   Message("Oracle Sign-On Failure", ORA_msg)
   FTPSLOG_Write("Oracle Sign-On Failure")

endif

FTPSLOG_Close()

Return

;**********************************************************************
;*       GET RUN CONTROLS                                             *
;**********************************************************************

<SNIP>

WinBatch Adapter / API Modules

The WinBatch Adapter FTPSWBT module is invoked (among other methods) when a user clicks RUN on the run control panel. The purpose of this module is to collect all command line arguments (PeopleSoft environment variables) and pass control to the process linked to the run control panel as defined by the underlying Process Definition. It provides capabilities similar to PSAE.exe (Application Engine), SQRW.exe (SQR) or PSNVS.exe (nVision).

Not all source code revealed for full set of API's...


FTPSWBT.WBT - Main Module

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSWBT.WBT                                         *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH/PEOPLESOFT ADAPTER (PROCESS SCHEDULER).    *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE SERVES AS THE ADAPTER FOR PEOPLESOFT    *
;*                PROCESS SCHEDULER. ALL PROCESS DEFINITIONS LINKED   *
;*                TO THE 'WINBATCH' PROCESS TYPE WILL INVOKE THIS     *
;*                PROCEDURE TO ESTABLISH COMMUNICATION WITH THE       *
;*                PROCESS SCHEDULER AND PASS ALL REQUIRED COMMAND     *
;*                PARAMETERS (SEE BELOW).                             *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*    COMMAND LINE ARGUMENTS/FLAGS (VIA PROCESS SCHEDULER)            *
;*    ============================================================    *
;*                                                                    *
;*    -WW%%PS_HOME%%\USER\WBX  - Executable Location -> WB_dir        *
;*    -WX%%PRCSNAME%%          - Process Name        -> WB_pgm        *
;*    -WD%%DBNAME%%            - Database            -> WB_dbase      *
;*    -WU%%ACCESSID%%          - Userid              -> WB_user       *
;*    -WP%%ACCESSPSWD%%        - Password            -> WB_pswd       *
;*    -WI%%INSTANCE%%          - Process Instance    -> WB_pi         *
;*    -WR%%RUNCNTLID%%         - Run Control ID      -> WB_rc         *
;*    -WO%%OPRID%%             - Operator ID         -> WB_oprid      *
;*    -WL%%OUTDEST%%           - Log Output Location -> WB_log        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    EXTENDERS:                                                      *
;**********************************************************************
;*                                                                    *
;*    NO EXTENDERS REQUIRED.                                          *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    REVISIONS:                                                      *
;**********************************************************************
;*                                                                    *
;*   DATE     PROGRAMMER      DESCRIPTION                             *
;* ---------- --------------- --------------------------------------- *
;*                                                                    *
;* 03/18/2004 DELIA,TONY      ORIGINAL CODING.                        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*       UDF/SUBROUTINES                                              *
;**********************************************************************

#include "FTPSARG.WBC"
#include "FTPSORA.WBC"
#include "FTPSAPI.WBC"
#include "FTPSLOG.WBC"

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

FTPSARG_Execute()

FTPSLOG_Init()
FTPSLOG_Open("FTPSWBT")

FTPSLOG_Write("FTPSWBT: PeopleSoft Adapter - Invoking WinBatch Pocess Type")

if WB_dir              <> ""  & WB_pgm <> ""

   WB_call              = StrCat(WB_dir, "\", WB_pgm, ".exe")

   FTPSLOG_Write("FTPSWBT: Validating call [%WB_call%]")

   if FileExist(WB_call)

      FTPSLOG_Write("FTPSWBT: Initiating [%WB_call%]")

      RunShell(WB_call, ALL_parms, "", @NORMAL, @NOWAIT)

      FTPSLOG_Write("FTPSWBT: Adapter relinquished control to module [%WB_pgm%]")

   else

      FTPSLOG_Write("FTPSWBT: Error calling module [%WB_call%]")
      FTPSLOG_Write("FTPSWBT: Connecting to Oracle (Process Monitor Update)")

      if FTPSORA_SignOn()

         FTPSLOG_Write("FTPSWBT: Successful Connection to Oracle Database")

         FTPSAPI_Init()

         PRCS_stat      = PRCS_stat_nosuccess
         PRCS_msg_parm1 = "ERROR: %WB_pgm% Not Found"

         FTPSAPI_Update()

         Message("FTPSWBT - Executable Not Found", ALL_parms)
         FTPSLOG_Write("FTPSWBT: Process Monitor Status Updated [ERROR]")

         FTPSORA_SignOff()

      else

         Message("FTPSWBT - Oracle Connection Failed", ORA_msg)
         FTPSLOG_Write("FTPSWBT: Oracle Connection Failed")

      endif

   endif

else

   FTPSLOG_Write("FTPSWBT: Argument List Invalid [Missing Parameters]")

endif

FTPSLOG_Write("FTPSWBT: PeopleSoft Adapter - Closing")

FTPSLOG_Close()

Exit

;**********************************************************************
;*       END OF PROGRAM                                               *
;**********************************************************************

FTPSARG.WBC - Command Line Argument API

The Command Line Argument API parses all flags sent thru Process Scheduler. These arguments are defined within the WinBatch Process Type Definition (or Process Definition Override Option page). The API assigns the values to appropriate WinBatch variables that are used by additional API routines as well as the application program. All WinBatch processes should begin with a call to FTPSARG_Execute() to populate the WinBatch argument variables.

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSARG.WBC (COPYBOOK).                             *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH COMMAND-LINE ARGUMENT PROCESSOR.           *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE PROCESSES ALL COMMAND LINE ARGUMENTS    *
;*                SENT THRU PROCESS SCHEDULER AND ASSIGNS THEM TO THE *
;*                APPROPRIATE GLOBAL VARIABLES (SEE BELOW). THIS      *
;*                COPYBOOK MUST BE INCLUDED IN ALL WINBATCH PROGRAMS  *
;*                WITH THE APPROPRIATE CALL (FTPSARG_Execute()) AT    *
;*                THE START OF THE PROGRAM.                           *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  FTPSARG_Execute() - Parses all arguments/flags.     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*    COMMAND LINE ARGUMENTS/FLAGS (VIA PROCESS SCHEDULER)            *
;*    ============================================================    *
;*                                                                    *
;*    -WW%%PS_HOME%%\USER\WBX  - Executable Location -> WB_dir        *
;*    -WX%%PRCSNAME%%          - Process Name        -> WB_pgm        *
;*    -WD%%DBNAME%%            - Database            -> WB_dbase      *
;*    -WU%%ACCESSID%%          - Userid              -> WB_user       *
;*    -WP%%ACCESSPSWD%%        - Password            -> WB_pswd       *
;*    -WI%%INSTANCE%%          - Process Instance    -> WB_pi         *
;*    -WR%%RUNCNTLID%%         - Run Control ID      -> WB_rc         *
;*    -WO%%OPRID%%             - Operator ID         -> WB_oprid      *
;*    -WL%OutputDirectory%     - Log Output          -> WB_log        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    EXTENDERS:                                                      *
;**********************************************************************
;*                                                                    *
;*    NO EXTENDERS REQUIRED.                                          *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    REVISIONS:                                                      *
;**********************************************************************
;*                                                                    *
;*   DATE     PROGRAMMER      DESCRIPTION                             *
;* ---------- --------------- --------------------------------------- *
;*                                                                    *
;* 03/18/2004 DELIA,TONY      ORIGINAL CODING.                        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*       PARSE ARGUMENTS / ASSIGN VARIABLES                           *
;**********************************************************************

#DefineSubroutine FTPSARG_Execute()

WB_dir            = ""
WB_pgm            = ""
WB_dbase          = ""
WB_user           = ""
WB_pswd           = ""
WB_pi             = 0
WB_rc             = ""
WB_oprid          = ""
WB_log            = "C:\TEMP\"

ALL_flags         = "-WW-WX-WD-WU-WP-WI-WR-WO-WL"
ALL_parms         = ""

idx               = 0

while idx         < param0

   idx            = idx + 1

   ALL_parms      = StrCat(ALL_parms, param%idx%, " ")

   aLen           = StrLen(param%idx%)
   aFlag          = StrSub(param%idx%, 1,3)
   aVal           = StrSub(param%idx%, 4,aLen - 3)
   aPos           = StrIndex(ALL_flags, aFlag, 1, @FWDSCAN)
   aPos           = Int(Ceiling((aPos + 2) / 3))
  
   Select aPos

      Case 1
         WB_dir   = aVal
         break
      Case 2
         WB_pgm   = aVal
         break
      Case 3
         WB_dbase = aVal
         break
      Case 4
         WB_user  = aVal
         break
      Case 5
         WB_pswd  = aVal
         break
      Case 6
         WB_pi    = aVal
         break
      Case 7
         WB_rc    = aVal
         break
      Case 8
         WB_oprid = aVal
         break
      Case 9
         WB_log   = aVal
         break

   EndSelect

endwhile

Return

#EndSubroutine

;**********************************************************************
;*       END OF COPYBOOK                                              *
;**********************************************************************

FTPSORA.WBC - Oracle Connectivity / Support API

The Oracle Connectivity/Support API allows a connection to be made without explicitly coding the Database, Oracle Access ID (SYSADM) or Production Password. A simple call to the FTPSORA_SignOn() procedure (without parameters) establishes the connection utilizing the PeopleSoft environment variables sent thru standard Process Scheduler conventions. Additional routines for centralized Error Handling and Dynaset Column Selection are provided. More routines will be added in the future.

*** NOTE – WinBatch API routines utilize the installed Oracle Objects component. ***

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSORA.WBC                                         *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH ORACLE CONNECTIVITY SUPPORT.               *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE CONTROLS ORACLE DATABASE ACTIVITY WHICH *
;*                INCLUDES CONNECT AND DISCONNECT ROUTINES. SEVERAL   *
;*                ADDITIONAL FUNCTIONS ARE INCLUDED TO VALIDATE SQL   *
;*                RESULTS AND SELECT SPECIFIC COLUMNS FROM DYNASETS.  *
;*                DATABASE LAYER IS ACCESSED USING ORACLE OBJECTS.    *
;*                THE SIGN-ON IS TRANSPARENT TO THE USER SINCE ALL    *
;*                CONNECTION INFORMATION IS PASSED VIA THE PROCESS    *
;*                SCHEDULER COMMAND LINE (SEE FTPSARG.WBC).           *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  FTPSORA_SignOn()      - Connect to Oracle.          *
;*                FTPSORA_SignOff()     - Disconnect from Oracle.     *
;*                FTPSORA_ValidSQL()    - Validate Last Oracle Stmt.  *
;*                FTPSORA_SelectValue() - Select Dynaset Column.      *
;*                                                                    *
;**********************************************************************

FTPSAPI.WBC – Process Scheduler API

The Process Scheduler API provides a standard mechanism to update the Process monitor status and allow messages to be written to the message log. It is almost identical in functionality to the delivered PRCSAPI.SQC utilized by SQR processes. In addition, any failure to connect to the WinBatch process definition will automatically update the Process Status to ERROR with the appropriate message.

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSAPI.WBC                                         *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH PROCESS SCHEDULER API.                     *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE CONTROLS COMMUNICATION WITH PROCESS     *
;*                SCHEDULER. PROCESS REQUEST INFORMATION SUCH AS THE  *
;*                RUN STATUS, END TIME, MESSAGE LOG ENTRIES, ETC. ARE *
;*                CONTROLLED THROUGH THIS SET OF API FUNCTIONS.       *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  FTPSAPI_Init()   - API Initialization.              *
;*                FTPSAPI_Log()    - Update Message Log/Parms.        *
;*                FTPSAPI_Update() - Update Process Status/Queue.     *
;*                                                                    *
;**********************************************************************

FTPSLOG.WBC – Log File Support API

The Log File Support API provides a centralized location to trace and monitor WinBatch applications. Files are written to the location contained in the %OutputDirectory% variable. :The default filename is WB_<processname>_<process_instance>.log. The main module FTPSWBT always produces a log file listing control transfer information. The application log will display similar parameter information along with application specific messages. The current datetime stamp is included on every line written to assist in analysis and tuning exercises.

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSLOG.WBC                                         *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH LOG FILE SUPPORT.                          *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE CONTROLS ALL LOG FILE PROCESSING. ALL   *
;*                LOG FILE FUNCTIONALITY IS ENCAPSULATED WITHIN THE   *
;*                COPYBOOK. EACH LOG FILE WILL CONTAIN STANDARD HIGH  *
;*                LEVEL INFORMATION (PARAMETERS, DTTM_STAMP, ETC.)    *
;*                ALONG WITH ANY APPLICATION MESSAGES REQUIRED USING  *
;*                THE FTPSLOG_Write() ROUTINE.                        *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  FTPSLOG_Init()   - Log Initialization.              *
;*                FTPSLOG_Write()  - Write Log Entry.                 *
;*                FTPSLOG_Open()   - Open Log File.                   *
;*                FTPSLOG_Close()  - Close Log File.                  *
;*                                                                    *
;**********************************************************************

FTPSXLS.WBC – Microsoft Excel Support API

The MicroSoft Excel Support API provides a consistent means of accessing the Excel Object layer. It is NOT utilized in the WinBatch Adapter Module – it is listed here since it was developed to be used as a standardized component for WinBatch development. Routines are provided for direct cell access (Select/Update), Cell/Column Formatting, etc. This module will be enhanced periodically to provide more and more capability.

;**********************************************************************
;*                                                                    *
;*       MODULE:  FTPSXLS.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:  FTPSXLS_GetValue()      - Extract Cell Value.       *
;*                FTPSXLS_GetFormula()    - Extract Cell Formula.     *
;*                FTPSXLS_WriteCell()     - Write Cell Value/Formula. *
;*                FTPSXLS_ColumnXlate()   - Numeric-to-Alpha Col Xlt. *
;*                FTPSXLS_ColorRow()      - Color Row.                *
;*                FTPSXLS_FormatCell()    - Format Cell.              *
;*                FTPSXLS_FormatColumn()  - Format Column.            *
;*                FTPSXLS_AutoFit()       - Autofit Column(s).        *
;*                FTPSXLS_TitleRows()     - Title Rows Page Setup.    *
;*                FTPSXLS_Orientation()   - Landscape/Portrait.       *
;*                FTPSXLS_Zoom()          - Zoom Page Size (%).       *
;*                FTPSXLS_HeaderFooter()  - Set Header/Footer.        *
;*                FTPSXLS_PrintArea()     - Set PrintArea.            *
;*                FTPSXLS_GridLines()     - Print Grid Lines.         *
;*                FTPSXLS_PaperSize()     - Set Paper Size.           *
;*                FTPSXLS_ActivePrinter() - Set Active Printer.       *
;*                FTPSXLS_FreezePane()    - Freeze Window Pane.       *
;*                FTPSXLS_SheetName()     - Assign Sheet Name.        *
;*                                                                    *
;**********************************************************************

The design/development of my WinBatch Adapter was accomplished within a very short timeframe but there wasn't the slightest sacrifice of quality. Creating & testing the various integrated components was a fun exercise... I hope you enjoyed this glimpse into the development.

Spencer Gifts - Point of Sale Interface to PeopleSoft HR/Payroll

Halloween season is here... the time for pumpkins, ghosts, candy & other fun. It's also a time that always reminds me of a project I worked on over 20 years ago for Spencer Gifts.  In addition to the retail stores you see in the mall they also run the Spirit Halloween shops that are popping up all over the country. Everyone loves those stores... at least everyone I know. In June 1998, Spencer Gifts was owned by Universal Studios which was owned by Seagram's Corporation. The project was to integrate Spencers with the parent Seagram PeopleSoft HRMS/Payroll system while adhering to their existing business model. Due to the nature of the business - seasonal staff, high turnover rate, retirees, students, extraordinary volume, etc. - it was a condition of Go-Live to be able to automatically maintain employees/benefits/payroll via the on-site store managers & the sales registers. For a store that is known for marketing silly, quirky & outright absurd merchandise their enterprise is highly sophisticated & complex. This was the perfect project for my "particular set of skills"...

From my LinkedIn Projects List:

Remote Point-of-Sale Integration with PeopleSoft HR/Benefits/Payroll - Spencer Gifts/Spirit Halloween - June 1998

Designed/developed hands-free link from Point of Sale registers to corporate PeopleSoft HR/Benefits/Payroll system. Over 600 permanent (& transient seasonal) store locations enter employee data/hours through their remote sales registers. The process automatically updates PeopleSoft including adding/updating benefit enrollments. Due to the nature of the business & high turnover rate (returning students/retirees/holiday help) Spencer Gift's could not have gone live without this complex automation.

In addition to retail sales information the store terminals are used by managers to maintain their employee information - Hires, Rehires, Transfers, Promotions, Terminations, Leave of Absence, Tax Changes & general Name/Address updates. Time/Attendance information is also entered.

All the information, spread across North America, is polled each night & stored in the Spencer legacy database. The Employee Data & Hours, in their native layouts, would now be diverted & interfaced into PeopleSoft. My interface process SEAPOS01.SQR must create/update all employee tables, track job activity & automatically enroll the employees into the proper benefit programs. Data entry errors are identified, stored for online correction (by corporate personnel) & recycled into the interface process. (SEAPOS02.SQR handles the Hours portion).

Without question SQR was the hands down tool for this job - it was then & frankly, it would be now. The process, despite various business changes, reorganizations and version upgrades, is still running.


!**********************************************************************
!*                                                                    *
!*       MODULE:  SEAPOS01.SQR                                        *
!*       AUTHOR:  TONY DELIA.                                         *
!*         DATE:  06/30/1998.                                         *
!*       SYSTEM:  SPENCER GIFTS - POS EMPLOYEE DATA INTERFACE.        *
!*         DESC:  POINT OF SALE EMPLOYEE DATA INTERFACE.              *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*       TABLES:  MISCELLANEOUS TABLES                                *
!*                                                                    *
!*                ps_sea_run_cntl_sg  - Select                        *
!*                ps_installation     - Select, Update                *
!*                psdbfield           - Select                        *
!*                ps_pay_calendar     - Select                        *
!*                ps_company_tbl      - Select                        *
!*                ps_jobcode_tbl      - Select                        *
!*                ps_sal_grade_tbl    - Select                        *
!*                ps_state_names_tbl  - Select                        *
!*                ps_dept_tbl         - Select                        *
!*                                                                    *
!*                POS PROCESSING/DEFINITION TABLES                    *
!*                                                                    *
!*                ps_sea_pos_data     - Select, Insert, Update        *
!*                ps_sea_pos_errs     - Delete, Insert                *
!*                ps_sea_pos_log      - Select, Insert                *
!*                ps_sea_pos_jobcd    - Select                        *
!*                ps_sea_pos_req      - Select                        *
!*                ps_sea_pos_ben      - Select                        *
!*                ps_sea_pos_ben_pln  - Select                        *
!*                                                                    *
!*                EMPLOYEE TABLES                                     *
!*                                                                    *
!*                ps_personal_data    - Select, Insert, Update        *
!*                ps_job              - Select, Insert, Update        *
!*                ps_employment       - Select, Insert, Update        *
!*                                                                    *
!*                TAX TABLES                                          *
!*                                                                    *
!*                ps_fed_tax_data     - Select, Insert, Update        *
!*                ps_state_tax_data   - Select, Insert, Update        *
!*                ps_local_tax_data   - Select, Insert, Update        *
!*                ps_tax_distrib      - Select, Insert, Update        *
!*                ps_tax_dist_effdt   - Select, Insert, Update        *
!*                                                                    *
!*                EMPLOYEE BENEFIT TABLES                             *
!*                                                                    *
!*                ps_ben_prog_partic  - Select, Insert                *
!*                ps_benefit_partic   - Select, Insert                *
!*                ps_health_benefit   - Select, Insert                *
!*                ps_life_add_ben     - Select, Insert                *
!*                ps_disability_ben   - Select, Insert                *
!*                ps_savings_plan     - Select, Insert                *
!*                ps_leave_plan       - Select, Insert                *
!*                ps_fsa_benefit      - Select, Insert                *
!*                ps_rtrmnt_plan      - n/a                           *
!*                ps_pension_plan     - Select, Insert                *
!*                ps_vacation_ben     - n/a                           *
!*                                                                    *
!**********************************************************************

Breakdown of sub-modules within MAIN processing program:


#Include 'seaposer.sqc'  !POS Error Message Routines
#Include 'seaposcv.sqc'  !POS Conversion Matrix Routines
#Include 'seaposdb.sqc'  !POS Database Activity (Inserts,Updates,etc.)
#Include 'seaposrq.sqc'  !POS Required Field Routines
#Include 'seaposjm.sqc'  !POS Jobcode Matrix Routines
#Include 'seapostx.sqc'  !POS Tax Data Processing
#Include 'seaposbn.sqc'  !POS Benefit Data Processing
#Include 'seapospr.sqc'  !POS Print Routines

Sub-modules within seaposbn.sqc - Benefit Data Processing:

#Include 'seaposb0.sqc'  !POS Benefit Processing - PROG/PARTIC
#Include 'seaposb1.sqc'  !POS Benefit Processing - HEALTH_BENEFIT
#Include 'seaposb2.sqc'  !POS Benefit Processing - LIFE_ADD_BEN
#Include 'seaposb3.sqc'  !POS Benefit Processing - DISABILITY_BEN
#Include 'seaposb4.sqc'  !POS Benefit Processing - SAVINGS_PLAN
#Include 'seaposb5.sqc'  !POS Benefit Processing - LEAVE_PLAN
#Include 'seaposb6.sqc'  !POS Benefit Processing - FSA_BENEFIT
#Include 'seaposb7.sqc'  !POS Benefit Processing - RTRMNT_PLAN
#Include 'seaposb8.sqc'  !POS Benefit Processing - PENSION_PLAN
#Include 'seaposb9.sqc'  !POS Benefit Processing - VACATION_BEN

When I was assigned to this project I met with a very helpful & highly proficient Spencer HRMS team, collected the OS/Polling documentation/test files & built a complete functioning prototype on my laptop using the SQL*Base version of PeopleSoft. This was completed before the initial meetings with Seagram...

Before I stray off the Halloween & Spirit theme - here's a very quick 3D character montage I put together using DAZ Studio 4.11. I'm sure you can find all these costumes, props & more at Spirit Halloween shops.

The big nut to crack when creating a complete automated interface for PeopleSoft HRMS is how to regulate benefits enrollment. At Spencers, all benefit programs were issued by jobcode. I created a custom jobcode default table to hold the benefit program (and other attributes)...

...then another custom table that holds the benefit plans within each program. I also defined the methods to assign effective dates. The methods dictate the proper date to begin benefits, terminate or transfer them. When transferring from one jobcode to another the table entries are compared & a delta assigned indicating if benefits were added, dropped or the same (+, -, =). The program responds accordingly.

The routine Build-Benefit-Matrix performs the aforementioned delta comparison & creates two arrays - one that holds the Benefit Program New/Old values & the other as a sub-array to map the required Enrollment/Termination (based on the indicator +, - or =). The first entry below shows the blueprint for a benefit program upgrade to Z06 from Z07. All plans are = except for type 10 LTD which needs to be added. Conversely, when switching from Z06 to Z07 the type 10 LTD would be terminated. All "=" benefit plans remain untouched.

Idx    Mode   New   Old   BegX   EndX
----   ----   ---   ---   ----   ----
0000   X      Z06   Z07   0000   0013

Idx    Mode   Ind   New   Old   Type   Plan     Action   Beg   Trm   Xfr
----   ----   ---   ---   ---   ----   ----     ------   ---   ---   ---
0000   X      =     Z06   Z07   10              T              T4    X4
0001   X      =     Z06   Z07   11              T              T4    X4
0002   X      =     Z06   Z07   20     LIF25    I        B1    T1    X1
0003   X      =     Z06   Z07   22     ADD25    I        B1    T1    X1
0004   X      =     Z06   Z07   27     TRV1X    I        B1    T1    X1
0005   X      =     Z06   Z07   30     STD67%   I        B2    T1    X1
0006   X      +     Z06   Z07   31     LTD      I        B2    T1
0007   X      =     Z06   Z07   40              T              T3
0008   X      =     Z06   Z07   50              T              T3    X1
0009   X      =     Z06   Z07   51              T              T3    X1
0010   X      =     Z06   Z07   5Z     HOL      I        B1    T3
0011   X      =     Z06   Z07   60              T              T3    X1
0012   X      =     Z06   Z07   61              T              T3    X1
0013   X      =     Z06   Z07   82              T              T1

Idx    Mode   New   Old   BegX   EndX
----   ----   ---   ---   ----   ----
0001   X      Z06   Z08   0014   0027

Idx    Mode   Ind   New   Old   Type   Plan     Action   Beg   Trm   Xfr
----   ----   ---   ---   ---   ----   ----     ------   ---   ---   ---
0014   X      +     Z06   Z08   10              T              T4
0015   X      +     Z06   Z08   11              T              T4
0016   X      +     Z06   Z08   20     LIF25    I        B1    T1
0017   X      +     Z06   Z08   22     ADD25    I        B1    T1
0018   X      +     Z06   Z08   27     TRV1X    I        B1    T1
0019   X      +     Z06   Z08   30     STD67%   I        B2    T1
0020   X      +     Z06   Z08   31     LTD      I        B2    T1
0021   X      =     Z06   Z08   40              T              T3
0022   X      +     Z06   Z08   50              T              T3
0023   X      +     Z06   Z08   51              T              T3
0024   X      +     Z06   Z08   5Z     HOL      I        B1    T3
0025   X      +     Z06   Z08   60              T              T3
0026   X      +     Z06   Z08   61              T              T3
0027   X      =     Z06   Z08   82              T              T1

Idx    Mode   New   Old   BegX   EndX
----   ----   ---   ---   ----   ----
0002   X      Z07   Z06   0028   0041

Idx    Mode   Ind   New   Old   Type   Plan     Action   Beg   Trm   Xfr
----   ----   ---   ---   ---   ----   ----     ------   ---   ---   ---
0028   X      =     Z07   Z06   10              T              T4    X4
0029   X      =     Z07   Z06   11              T              T4    X4
0030   X      =     Z07   Z06   20     LIF25    I        B1    T1    X1
0031   X      =     Z07   Z06   22     ADD25    I        B1    T1    X1
0032   X      =     Z07   Z06   27     TRV1X    I        B1    T1    X1
0033   X      =     Z07   Z06   30     STD67%   I        B2    T1    X1
0034   X      -     Z07   Z06   31     LTD      I        B2    T1    X1
0035   X      =     Z07   Z06   40              T              T3
0036   X      =     Z07   Z06   50              T              T3    X1
0037   X      =     Z07   Z06   51              T              T3    X1
0038   X      =     Z07   Z06   5Z     HOL      I        B1    T3
0039   X      =     Z07   Z06   60              T              T3    X1
0040   X      =     Z07   Z06   61              T              T3    X1
0041   X      =     Z07   Z06   82              T              T1

<SNIP>

The Process-Benefit-Pointer routine coordinates the adding/removing of benefits for the employee using the Benefit Matrix.

!**********************************************************************
!*       Process Benefit Pointer                                      *
!**********************************************************************

begin-procedure Process-Benefit-Pointer

let #PTidx            = 0
let $PTsw             = 'N'

while #PTidx          < #PTctr

   let $PTmode        = PTmtx.PTmode (#PTidx)
   let $PTnew         = PTmtx.PTnew  (#PTidx)
   let $PTold         = PTmtx.PTold  (#PTidx)
   let #PTbeg         = PTmtx.PTbeg  (#PTidx)
   let #PTend         = PTmtx.PTend  (#PTidx)

   if  $PTmode        = $BENmode
   and $PTnew         = $BENnew
   and $PTold         = $BENold

       let #PTidx     = #PTctr
       let $PTsw      = 'Y'

   end-if

   let #PTidx         = #PTidx + 1

end-while

if $PTsw             <> 'Y'

   let $ERRtxt        = 'Benefit Matrix Error - Key(s) not found: '
   let $ERRtxt        = $ERRtxt || $BENmode || '/' || $BENnew || '/' || $BENold
   do Build-Error-Matrix

else

   do Calc-Benefit-Dates

   let #BNidx         = #PTbeg

   while #BNidx      <= #PTend

      let $BNmode     = BNmtx.BNmode (#BNidx)
      let $BNind      = BNmtx.BNind  (#BNidx)
      let $BNnew      = BNmtx.BNnew  (#BNidx)
      let $BNold      = BNmtx.BNold  (#BNidx)
      let $BNtype     = BNmtx.BNtype (#BNidx)
      let $BNplan     = BNmtx.BNplan (#BNidx)
      let $BNactn     = BNmtx.BNactn (#BNidx)
      let $BNmthB     = BNmtx.BNmeth (#BNidx, #idxBEG)  ! Method - Begin
      let $BNmthT     = BNmtx.BNmeth (#BNidx, #idxTRM)  ! Method - Term
      let $BNmthX     = BNmtx.BNmeth (#BNidx, #idxXFR)  ! Method - Xfer

      let $BNmeth     = BNmtx.BNmeth (#BNidx, #BENidx)  ! Method - Specific

      let $BNproc     = 'Y'

      !   Bypass Conditions - Benefit Matrix

      !   A - No Begin/Terminate Method (Non-Transfer)
      if  $BNmode     = 'Z'
      and $BNmeth     = ' '
      !   B - Begin Method without Benefit Plan (Non-Transfer)
      or  $BNmode     = 'Z'
      and $BNmeth     = $BNmthB
      and $BNplan     = ' '
      !   C - Transfer New/Old yields EQUAL Benefit Attributes
      or  $BNind      = '='
      !   D - Gain in Benefits without Begin Method (Non-Auto)
      or  $BNind      = '+'
      and $BNmthB     = ' '
      !   E - Loss in Benefits without Terminate Method (Non-Auto)
      or  $BNind      = '-'
      and $BNmthX     = ' '
          let $BNproc = 'N'
      end-if

      if  $BNproc     = 'Y'
          do Process-Benefit-Matrix
      end-if

      let #BNidx      = #BNidx + 1

   end-while

end-if

end-procedure

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

In a prior blog post I used the SEAPOS01.SQR program as the basis for my SQR Background Processor. You can see a portion of the underlying Process Flowchart for the Benefits Handling.

This was a complex, well-designed interface I built from scratch. Despite supporting another client simultaneously in PeopleSoft Financials (AR, GL, AM, AP) and splitting my on-site time during the week (King of Prussia/Egg Harbor Township), it was always ahead of schedule in development & testing with the Go-Live being a smooth anticlimactic event.


Now let's pause for some more scary stuff... another scene I created with DAZ Studio.

I'll take a moment to acknowledge one of the outstanding resources at Spencer Gifts - Dave Bell (currently Sr. HRIS Manager). He's extremely knowledgeable & a valuable asset to his organization - and has the sense of humor you would expect from a Spencer Gifts employee. Working with Dave we also added the capability to enter online transactions so they may be processed in a similar fashion as the nightly polled items. Then he took that one step further with mass data inserts from his MS Access database into the POS staging tables for Annual Salary Planning. By utilizing the SEAPOS01 process he invoked the same logic & consistency for all updates.

The company headquarters is located in Egg Harbor Township, NJ just outside of Atlantic City - between my house & the shore. The commute from Marlton was one of the best I've ever had... Also, I was able to attend the Annual Spencer Gifts Sample Sale - all the vendor prototypes are put up for grabs to the employees (and guests). I picked up a talking alien doll for 50 cents (which I still have), alien blow-up doll for 25 cents (not what you think), alien in a capsule for a buck, a big styrofoam gargoyle for another buck... and a couple free lava lamps! To commemorate that bountiful day I created another DAZ 3D Studio scene - an alien with a lava lamp from Spencer Gifts.

HAPPY HALLOWEEN!