Understanding Spreadsheet Vouchers
This section discusses:
Spreadsheet voucher processing.
Spreadsheet voucher flow.
Spreadsheet voucher data fields.
Spreadsheet voucher process edits.
Spreadsheet Voucher Processing
The Spreadsheet Voucher workbook lets you enter vouchers offline using Microsoft Excel and then import the vouchers into your PeopleSoft database. It supports regular voucher additions. Once you import the spreadsheet vouchers, the Voucher Build Application Engine process (AP_VCHRBLD) builds and edits the vouchers.
Note: The spreadsheet voucher process supports Microsoft Excel formats as input. If you use a non-Excel spreadsheet, you must convert the data file to a Microsoft Excel format before importing.
PeopleSoft Voucher Workbook: ExcelUploadforVoucher.xls
The ExcelUploadforVoucher.xls workbook is the PeopleSoft Spreadsheet Voucher Import user interface. You use it to prepare and enter vouchers and import them into your PeopleSoft database.
The ExcelUploadforVoucher.xls workbook comprises two worksheets, the Template worksheet and the Data Sheet worksheet. The Template worksheet contains all of the available fields that you can configure for data entry. You can configure the spreadsheet for simple one line express vouchers to complex multiple line purchase order (PO) related vouchers. You enter the transaction data on the Data Sheet worksheet. You can create multiple versions of the Microsoft Excel spreadsheet, all with different templates if required.
Note: Users uploading vouchers with Microsoft Excel 2007 must use the file with the .xlsm file type.
Spreadsheet Voucher Flow
To create spreadsheet vouchers:
Enter the data into the spreadsheet voucher using a format that includes the minimum data requirements for a PeopleSoft Payables voucher.
Generate the XML and post the file from the spreadsheet voucher options.
A Visual Basic (VB) macro built in the spreadsheet converts the spreadsheet data into an XML format that is readable by the Integration Broker. Also, a VB macro posts the file to a URL available to PeopleSoft systems.
Enter a valid user ID and password.
Users are prompted to enter a valid user ID and password before the macro posts the file to the PeopleSoft system.
After you generate the XML and post your file, PeopleSoft systems:
Retrieves the data from the XML file and validates the data.
If there are errors in the data the VOUCHER_BUILD message flags the data as being in error. You must correct the data using the application messaging error correction feature before the data can be processed successfully.
Copies the data into the voucher staging tables if there are no data errors.
Selects the vouchers based on the Voucher Build process run control parameters and builds and edits them.
Updates the voucher transaction tables.
Deletes the record from the voucher staging tables.
Image: Spreadsheet voucher process flow
The following graphic illustrates the flow for processing spreadsheet vouchers.
Spreadsheet Voucher Data Fields
All data fields in the regular voucher entry process are included in the spreadsheet voucher. You can configure the voucher spreadsheet for the appropriate data entry required. However, certain fields must contain data in order to properly convert the data into an XML file.
The fields requiring data are:
Note: You do not have to enter voucher line numbers or distribution line numbers. VB macros generate that data based on the location of the data.
Spreadsheet Voucher Process Edits
Spreadsheet voucher edits exist in the spreadsheet, the VOUCHER_BUILD application message, and the Voucher Build process. Valid data must exist in each stage of the process before further processing is performed.
Spreadsheet Voucher Edits
The spreadsheet voucher does not perform major editing. In addition to basic Microsoft Excel spreadsheet validation edits, the spreadsheet voucher edits include:
Control Group ID always defaults to NEXT.
Voucher Style always defaults to REG (regular).
Voucher Source always defaults to XML.
Voucher ID always defaults to NEXT.
Required fields contain data.
Voucher comments are 254 characters or less. (optional)
All date fields are either empty or contain a valid date. (optional)
Chartfields do not have a trailing space. For example, "DEPT ". (optional)
If one of these validations fails, the generate XML will stop immediately, and the error message will indicate you where and why the validation failed. The optional validations can be turned on or off on the Template page.
VOUCHER_BUILD Message Edits
You must correct VOUCHER_BUILD message errors before the data passes to the Voucher Build process. Edits include data type validations (for example, format, field characters, and file length). The VOUCHER_BUILD message also validates that the business unit entered on the spreadsheet is a valid PeopleSoft Payables business unit.
Voucher Build Process Edits
The Voucher Build process can result in pre-edit errors or vouchers in recycle status. Pre-edit errors prohibit the transaction from being processed by voucher edit processing. Vouchers without pre-edit errors pass to the voucher edit subprocess. The voucher edits are the same edits performed by the Voucher component (VCHR_EXPRESS).
Some of the pre-edits include:
Valid business unit.
Valid default location for supplier.
Invoice ID required.
Invoice date required.
Voucher line information present.
Distribution information present.