9.2 Bulk Load Single Payment Vouchers Using Excel to CI Utility
Purpose: The purpose of this workbook is to upload data from Excel into PeopleSoft using the Component Interface to execute business logic for each transaction. This source workbook contains both worksheets and Excel VBA code modules. The Worksheets can be copied to other workbooks for distribution without copying the code modules.
Audience: Finance staff.
Enhanced capability to bulk load Single Payment Vouchers to the Voucher Staging table using the Excel to CI utility. Use the Excel to CI workbook to stage single payment vouchers that will be loaded into the system.
You must have at least one of these local college managed security roles:
- ZZ Voucher Processing
You must also set these User Preference Definitions:
If you need assistance with the above security roles or User Preference Definitions, please contact your local college supervisor or IT Admin to request role access.
Bulk Load Single Payment Vouchers Using CIU
Connection Information Sheet - The information provided on this sheet is required to create a new template or submit data to the database. You will need to specify environment information as well as information in regard to how each transaction should be handled. The Action will be filled in automatically. By default, character fields which are left blank or only contain blanks will not be submitted for update. There is an option to override this setting.
- Begin by opening the ExcelToCI workbook. On the Connect Information tab, input connection information. You will need:
- PeopleSoft web server name for your specific environment
- HTTP port
- Portal name
- Default local Node
Template Sheet - Here you create the template that you are going to use. The template is based upon the structure of a Component Interface on the PeopleSoft database.
- Select the Template tab.
- Select Add-Ins > New Template.
- A Login Prompt displays. Input:
- User ID
- Component Interface Name "AP_SGLP_CI"
- Select Add-ins > Select All Input Cells.
- Select Add-ins > New Data Input. This button copies the selected input cells into the Data Input sheet. The Data Input sheet becomes active.
Data Input Sheet - Here you enter data values for submission to the PeopleSoft database. The data entered on this sheet is then staged in hierarchical form in preparation for submission to the database.
- At prompt to delete existing data, select Yes.
- Input Voucher data. Required data entry fields (each row must have a unique value in at least one of these four fields):
- Voucher Build Key Char 1
- Voucher Build Key Char 2
- Voucher Build Key Num 1
- Voucher Build Key Num 2
- Voucher Style is SGLP.
- Voucher Source is XML.
Staging & Submission sheet - the last step is to format the data and submit to the database.
- Select Add-ins > Stage Data for Submission.
- Staged Voucher data will display on the Staging & Submission tab.
- Select Add-ins > Submit Data.
- After selecting the Submit Data button, the Login window displays.
- Enter the User ID.
- Enter the Password.
- Review the Staging & Submission tab, Column A. Each submitted row is marked with the reply from the database. The values can be: OK, Warning or Error. Status should be OK. Any errors will be shown on the Errors worksheet.
- The single payment voucher data is moved to the Voucher Staging table and will be pulled in to AP during the Voucher Build process.
Navigation: NavBar > Navigator > Accounts Payable > Batch Processes > Vouchers > Voucher Build
For detailed instructions, see QRG: 9.2 Performing Voucher Build
- The Voucher Build tab displays.
- Enter the Process Option or use lookup icon to select from list.
- Enter the Business Unit or use lookup icon to select from list.
- Select the "XML Invoice" Voucher Build Interfaces option from the drop-down menu.
- The Voucher Search Results display.
- You have successfully uploaded multiple Single Payment vouchers.
- End of procedure.