Understanding Spreadsheet Vouchers

This section discusses:

  • Spreadsheet voucher processing.

  • Spreadsheet voucher flow.

  • Spreadsheet voucher data fields.

  • Spreadsheet voucher process edits.

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.

Note: Currently, ExcelUploadforVoucher.xls spreadsheet upload is not supported on macOS.

To create spreadsheet vouchers:

  1. Enter the data into the spreadsheet voucher using a format that includes the minimum data requirements for a PeopleSoft Payables voucher.

  2. 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.

  3. 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. Upon validation, a message appears either with the number of vouchers loaded, or with that identified vouchers that are in error.

    Note: On the login page select the Dedicated Integration Broker Gateway check box if your Integration Broker gateway is dedicated to the database you are currently using. If you leave the check box unselected, the Local Node field appears. The system appends the local node value you enter to the URL when posting the XML to the Integration Broker.

After you generate the XML and post your file, PeopleSoft systems:

  1. Retrieves the data from the XML file and validates the data.

    If there are errors in the data the VOUCHER_BUILD_EXCEL_C message flags the data as being in error. You must correct the data, regenerate XML and Post before the data can be processed successfully.

  2. Copies the data into the voucher staging tables if there are no data errors.

  3. Selects the vouchers based on the Voucher Build process run control parameters and builds and edits them.

  4. Updates the voucher transaction tables.

  5. Deletes the record from the voucher staging tables.

Note: The user credentials are stored for an hour after you generate the XML so that you need not login again for next submission.

The following graphic illustrates the flow for processing spreadsheet vouchers.

 Spreadsheet voucher process flow

Most 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.

Note: SpeedCharts are not supported by Excel Voucher Upload.

However, certain fields must contain data in order to properly convert the data into an XML file.

The fields requiring data are:

Record Type

Field

Voucher Header

  • BUSINESS_UNIT

  • INVOICE_ID

  • INVOICE_DT

  • VENDOR_ID

  • GROSS_AMT

Voucher Line

  • BUSINESS_UNIT

  • VOUCHER_LINE_NUM

  • MERCHANDISE_AMT

Voucher Distribution

  • BUSINESS_UNIT

  • VOUCHER_LINE_NUM

  • DISTRIB_LINE_NUM

  • ACCOUNT

  • MERCHANDISE_AMT

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 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 XLS.

  • Voucher ID always defaults to NEXT.

  • Required fields contain data.

    See Spreadsheet Voucher Data Fields.

  • 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.

EXCELUPLOADFORVOUCHER Message Edits

You must correct EXCELUPLOADFORVOUCHER 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 EXCELUPLOADFORVOUCHER 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 supplier.

  • Valid default location for supplier.

  • Invoice ID required.

  • Invoice date required.

  • Voucher line information present.

  • Distribution information present.