PeopleSoft Enterprise Payables 9.1 PeopleBook

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

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

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.

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

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 PeopleSoft 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 Enterprise 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.
003 - voucher payment. The voucher payment fields are highlighted in green.

Voucher header, line, distribution, and payment 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 Microsoft 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.
Do Validations	Select this checkbox to enable validations of the comment length, date fields and ChartField values. If you do not select this check box, the spreadsheet will only validate that the required fields contain data.

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 PeopleSoft 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 Microsoft 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. 003 - voucher payment. The voucher payment fields are highlighted in green. Voucher header, line, distribution, and payment fields are color coded to differentiate the various sets of data.
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 Microsoft Excel Save As window appears when you click the Generate XML and Post button. Save the XML file with the default file name to the same folder where the actual spreadsheet resides to instantly post the XML file to the URL. Note. For the VB macro to post your file to the URL available to the Integration Broker, you must use the default file name and save the file in the same directory and folder that contains the source Microsoft Excel file (for example, the ExcelUploadforVoucher.xls file). 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 Microsoft 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 picks up the XML data. Note. You can save the file using a different file name and folder than the default file name and folder if you are using another process to post your file to the URL available to the Integration Broker.

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	Definition 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 Error Information.