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.
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.
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
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 |
|
Voucher Line |
|
Voucher Distribution |
|
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 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 vendor.
Valid default location for vendor.
Invoice ID required.
Invoice date required.
Voucher line information present.
Distribution information present.
See Also
Understanding Voucher Build Processing
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.
See Also
Running the Voucher Build Process and Reviewing Messages
PeopleTools PeopleBook: Integration Broker
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 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:
|
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. |
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 should not enter the same voucher header data for multiple voucher lines. Also, you should not enter the same voucher line data for multiple distribution lines.
Record Type (line 2) |
Displays the record type associated with the field:
|
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. |
This section provides an overview of how to review spreadsheet vouchers and lists the pages used 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.
See Also
Reviewing Voucher Build Process Errors
Reviewing Voucher, Payment, and Vendor Information
Page Name |
Definition Name |
Navigation |
Usage |
VCHR_HEADER_QV |
|
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. |
|
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. |
|
VCHR_ERRORSMMRY_PG |
Accounts Payable, Vouchers, Add/Update, Regular Entry, Error Summary |
View any Voucher Build processing errors. |