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.
