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.

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.

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.

Bulk Load Single Payment Vouchers Using CIU

Load Single Payment Vouchers

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.

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

  1. Select the Template tab.
  2. Select Add-Ins > New Template.
  3. A Login Prompt displays.  Input:
    • User ID
    • Password  
    • Component Interface Name "AP_SGLP_CI"
  4. Select Add-ins > Select All Input Cells.
  5. Select Add-ins > New Data Input.  This button copies the selected input cells into the Data Input sheet.  The Data Input sheet becomes active.
Connect Information Sheet

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.

  1. At prompt to delete existing data, select Yes.
  2. 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
  3. Voucher Style is SGLP.
  4. Voucher Source is XML.
Data Input screen

Staging & Submission sheet - the last step is to format the data and submit to the database.

  1. Select Add-ins > Stage Data for Submission.
  2. Staged Voucher data will display on the Staging & Submission tab.
  3. Select Add-ins > Submit Data.
Staging & Submission tab
  1. After selecting the Submit Data button, the Login window displays.
  2. Enter the User ID.
  3. Enter the Password.
Login screen
  1. 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.
  2. The single payment voucher data is moved to the Voucher Staging table and will be pulled in to AP during the Voucher Build process.
Perform Voucher Build

Navigation: Accounts Payable > Batch Processes > Vouchers > Voucher Build

For detailed instructions, see QRG: Performing Voucher Build

  1. The Voucher Build tab displays. Enter the Process Option or use lookup icon to select from list.
  2. Enter the Business Unit or use lookup icon to select from list.
  3. Select the "XML Invoice" Voucher Build Interfaces option from the drop-down menu.  
Voucher build request page
  1. Select the Run button. The Process Scheduler Request window displays. Select OK to run the Voucher Build process.
  2. The Voucher Search Results display.
Process scheduler request page

Use the Process Messages tab to view Request Status. Select the Refresh Log button to refresh the page.

Process Messages tab

Use the Build Errors tab to view/correct Errors.

Build Errors tab

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.