Entering and Importing Spreadsheet Vouchers

This chapter provides an overview of spreadsheet vouchers, lists prerequisites, and discusses how to:

Set up spreadsheet vouchers.
Enter and import spreadsheet vouchers.
Review spreadsheet vouchers.

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 an 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 Excel spreadsheet, all with different templates if required.

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 Payables voucher.
Generate 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.

After you generate 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 Application Engine process (AP_VCHRBLD) run control parameters and builds and edits them.
Updates the voucher transaction tables.
Deletes the record from the voucher staging tables.

The following graphic illustrates the flow for processing spreadsheet vouchers.

Spreadsheet voucher process flow

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:

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 Process Edits

Spreadsheet voucher edits exist in the spreadsheet, the VOUCHER_BUILD 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 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.

See Spreadsheet Voucher Data Fields.

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 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 vendor.
Valid default location for vendor.
Invoice ID required.
Invoice date required.
Voucher line information present.
Distribution information present.

Prerequisites

Before you can enter and import spreadsheet vouchers, you must:

Copy the voucher workbook file (ExcelUploadforVoucher.xls) to a folder on your workstation.

The Excel file is located in the core\build\excel folder of the shipped PeopleSoft software.
Install MicroSoft delivered XML library file MSXML.DLL on your workstation.
Set up your Microsoft Excel to accept macros.
Configure Integration Broker for the Voucher EIP (enterprise integration point).

The web server and the application server should be configured for setting up the Integration Gateway. Also, the application server should be configured to have the Pub/Sub servers established before bringing up the application server.

Determine your default local node for the Integration Broker to receive the data and run the application message. PeopleSoft defines the default local node as PSFT_EP, but you can change it to your default local node.

Configure your browser to automatically detect LAN settings. From your browser, navigate to Tools > Internet Options. Select the Connections tab and click the LAN Settings button. Select Automatically detect settings in the Automatic configuration group box.
Create a Voucher Build process run control and request a run of the Voucher Build process.

Setting Up Spreadsheet Vouchers

This section discusses how to set up spreadsheet vouchers.

Access the Spreadsheet Voucher - Template worksheet by clicking the ExcelUploadforVoucher.xls file delivered with your PeopleSoft products. You set the defaults and set up your voucher spreadsheets for importing into Payables on the Template worksheet.

The Template worksheet contains all the available fields that you can input to populate the voucher data.

Machine Name, Integration Gateway, From Node, and To Node

Enter machine name, integration gateway, and the from and to nodes. The data entered in these fields create a URL string.

Note. PeopleSoft delivers the From Node as PSFT_AP_VCHR_XML.The Integration Broker subscribes only to a single default local node. PeopleSoft delivers the To Node as PSFT_EP. You can change it to your default local node.

See PeopleSoft PeopleTools PeopleBook: Integration Broker

Generated URL	Displays URL generated from the host name, integration gateway, and node entered. The XML file created is posted to the defined URL.
Record	Displays the staging table record associated with the field.
Record Type	Displays the record type associated with the field: 000 - voucher header. The voucher header fields are highlighted in blue. 001 - voucher line. The voucher line fields are highlighted in yellow. 002 - voucher distribution. The voucher distribution fields are highlighted in purple. Voucher header, line, and distribution fields are color coded to differentiate the various sets of data.
Field	Displays the name of the field.
Label	Displays the label of the field.
Field Type	Displays the type of the field. Types include Character, Date, and Number.
Field Length	Displays the length of the field.
Required	Displays a value of Yes or No depending on if the field is required during data entry.
Default Value	Enter the default value for the field. The value entered in the Template worksheet will be the field value displayed on the Data Sheet worksheet. You can override the default value on the Data Sheet worksheet. Note. You can enter default values for fields not included in the Data Sheet worksheet. The default values will become part of the transaction data, but will not be visible on the Data Sheet worksheet.
Add to Data Sheet	Select the check box to include the field on the Data page. Note. You must enter minimal information, such as the business unit, vendor ID, invoice ID, invoice date, and merchandise amount in order to properly convert the data to XML and build the voucher in the Voucher Build process.
Update Data Sheet	Click to update the Data Sheet worksheet with the selected fields.
Set Password	Click to enter or change a password associated with this spreadsheet file.
Hide Template	Click to hide the Template worksheet for this spreadsheet file. You will be prompted to enter a password if one has not been defined. You can navigate back to the Template worksheet by selecting from your Excel Tools menu, Macro, Macros. A window pops up listing all the macros. Select the ShowAdminSheet macro and click Run. The system prompts you for a password before displaying the Template worksheet.

Entering and Importing Spreadsheet Vouchers

This section discusses how to enter and import spreadsheet vouchers

Access the Spreadsheet Voucher - Data Sheet worksheet by clicking the ExcelUploadforVoucher.xls file delivered with your PeopleSoft products. You use the Data Sheet worksheet to create and import voucher data.

Enter voucher data and import the voucher data into Payables using the Data Sheet worksheet. The Data Sheet worksheet is designed to display only the fields selected in the Template worksheet. You can use Excel functionality to populate data in the Data Sheet worksheet. For example, you can enter a formula in the gross amount field in the voucher header that sums up the amount fields on the voucher distribution line.

Note. You are required to enter data in at least one row in the voucher header and the voucher distribution line. If no data is entered in the voucher line, VB macros create data from the fields entered in the voucher header. You are not required to enter the same voucher header data for multiple voucher lines. Also, you are not required to enter the same voucher line data for multiple distribution lines.

Record Type (line 2)	Displays the record type associated with the field. 000 - voucher header. The voucher header fields are highlighted in blue. 001 - voucher line. The voucher line fields are highlighted in yellow. 002 - voucher distribution. The voucher distribution fields are highlighted in purple.
Description (line 3)	Displays the description of the field.
Generate XML and Post	Click to create an XML file and instantly post the file to the URL defined in the Template worksheet. The Excel Save As window appears when you click the Generate XML and Post button. Save the XML file to the same folder where the actual spreadsheet resides to instantly post the XML file to the URL. Note. The URL string must be valid to post the XML data. The VOUCHER_BUILD message retrieves the XML data from the URL site once it is posted.
Generate XML	Click to create an XML file. The Excel Save As window appears when you click the Generate XML button. Indicate the file name and where you want to save it. As an example, you can write the data to an FTP site and create another process to post the XML file to the URL separately. When the Integration Broker is active, the VOUCHER_BUILD message will pick up the XML data.

Reviewing Spreadsheet Vouchers

This section provides an overview of how to review spreadsheet vouchers and lists the pages used to review spreadsheet vouchers.

Understanding How to Review Spreadsheet Vouchers

Use the Quick Invoice Entry component (VCHR_QUICK_PNL) to review and update vouchers staged for review by the Voucher Build process, as well as to correct vouchers that have Voucher Build process pre-edit errors.

Use the Voucher component to review and update spreadsheet vouchers with no pre-edit errors and recycle errors. Correct any errors resulting from voucher edit processing in the Voucher component. Once imported, you handle spreadsheet vouchers similarly to regular vouchers entered in the Voucher component.

You can view all corrected spreadsheet vouchers using the various online inquires and reports.

Pages Used to Review Spreadsheet Vouchers

Page Name	Object Name	Navigation	Usage
Quick Invoice	VCHR_HEADER_QV	Accounts Payable, Vouchers, Add/Update, Quick Invoice Entry, Quick Invoice Entry Accounts Payable, Vouchers, Maintain, Voucher Build Error Detail Click the Correct Errors link on the Voucher Build Error Detail page. Accounts Payable, Batch Processes, Vouchers, Voucher Build, Build Errors Click the Correct Errors button on the Build Errors page.	Review and update voucher header, line, and distribution information. When you enter the page in update/display mode from the Add Search page, you can search for vouchers by build status, including vouchers that have been staged for review by the Voucher Build process and vouchers with Voucher Build process pre-edit errors. See Reviewing and Updating Quick Invoices, Staged Vouchers, and Vouchers with Pre-Edit Errors.
Voucher - Summary	VCHR_SUMMARY_PG	Accounts Payable, Vouchers, Add/Update, Regular Entry, Summary	View voucher information, summarized from the three primary pages, such as header information, various statuses, and payment terms. Also view voucher entry information, such as the voucher source, the date entered, the user ID, and the date that the voucher was last updated. See Viewing Voucher Summary Information.
Voucher - Error Summary	VCHR_ERRORSMMRY_PG	Accounts Payable, Vouchers, Add/Update, Regular Entry, Error Summary	View any Voucher Build processing errors. See Viewing Voucher Errors.