Spreadsheet Invoice Import

This topic provides an overview of spreadsheet invoice import, lists prerequisites, and discusses:

Page Name

Definition Name

Usage

Spreadsheet Invoice - Data Sheet

Not applicable

Create and import invoice data.

Review Pending Transactions Page

INTFC_BI

View data for interface transactions in the INTFC_BI table.

Process Billing Interface Page

RUN_BI_INTFC

Run the Billing Interface process.

Message Log Page

PMN_BAT_MSGLOG

Check if any of the data returned an error for the particular process instance.

Review Interface Completions Page

INTFC_BI_CMP

Review data imported from the Billing Interface process.

Correct Interface Errors Page

INTFC_BI

Correct errors from the Billing Interface process.

See also Example: Setting Up the Billing Invoice EIP

The Spreadsheet Import to Billing Interface is used to enter invoice data in a worksheet format and have an xml file generated automatically. The xml file is posted into the PeopleSoft Billing Interface tables using Integration Broker or it can be used with a third-party application.

The worksheet can be personalized to display only the specific fields needed for data entry.

Process Flow

The following diagram illustrates the process flow to create an invoice:

This example illustrates the Process flow to create an invoice.

 Process flow

Before you can enter and import invoice information, you must:

  • Copy the Excel workbook to a folder on your workstation.

  • Set up your Microsoft Excel to accept macros.

  • Configure Integration Broker and Excel workbook.

  • Use Microsoft Office Excel 2007 version or higher.

This section discusses the configurations required for the Excel workbook and the PeopleSoft Integration Broker to communicate with each other.

Configuring the Integration Broker

Follow the given steps to set up the Integration Broker:

  1. Access the Quick Configuration page (PeopleTools, Integration Broker, Configuration, Quick Configuration). This page displays the information on the gateway and the domains. Verify a ping is successful by clicking the Ping Gateway button. Ensure that the domains to be used are in the Active status.

  2. Access the Service Operations page (PeopleTools, Integration Broker, Integration Setup, Service Operations). Select the Active checkbox on the General tab to activate the Service Operation EXCELUPLOADFORBILLING. Set the status as Active on the Handlers tab and verify that the status for the EXCELUPLOADFORBILLING service operation is active on the Routings tab. Add appropriate permission list using the Service Operation Security hyperlink on the General tab. This web service enhances the security and will prevent the user from bypassing the login validation.

Configuring the Excel Workbook

An Excel workbook is used to generate the XML file that is passed to Integration Broker. The Excel book is comprised of three worksheets: Template, Data Sheet, and Example.

The configuration information entered into the Excel workbook is case sensitive. Each row can hold data for only one table. If a row contains data from two or more tables, it is highlighted in red.

Service Operation, Routings, Nodes, Gateways and Domains must be configured and be active. Security privileges must be set for Service Operations. The Service Operation manager can be used to review processed data.

Use the Spreadsheet Invoice - Template worksheet to configure defaults and set up invoice spreadsheets to import into PeopleSoft Billing.

Navigation

Click the BillingInterfaceExcelUpload.xlsm file delivered with your PeopleSoft product.

This example illustrates the fields and controls on the Template worksheet. You can find definitions for the fields and controls later on this page.

 Spreadsheet - Template

The Template worksheet in the Spreadsheet Invoice workbook contains all of the available fields that you can use on the Data Sheet worksheet to enter invoice data. However, since customers typically require a subset of fields based on the business requirements and the user role, this sheet can be customized to restrict the visibility of certain fields.

The order of the interface tables is:

  • Billing Interface Table (INTFC_BI)

  • Billing Interface Table 2 (INTFC_BI2)

  • Billing Interface Header (INTFC_BI_HDR)

  • Notes (INTFC_BI_NOTE)

  • Tax Information (INTFC_BI_TAX)

  • Excise Tax Details (INTFC_BI_EXSD)

  • Accounting Entry & Discount/Surcharge (INTFC_BI_AEDS)

Field or Control

Description

Record

Displays the Billing Interface table name associated with the field.

Record Type

Displays the record type associated with the field. Values are:

  • 000: Billing Interface Information. Record Type 000 (INTFC_BI) is the parent billing interface table used to enter most invoice data. The billing interface information is highlighted in cyan.

  • 001: Extended Tax and Address Override Information. Record Type 001 (INTFC_BI2) is used for extended tax information, such as prepaid taxes, ship-to address overrides, and India transactions. The tax and address information are highlighted in green.

  • 002: Additional Bill Header Information. Record Type 002 (INTFC_BI_HDR) is used to enter bill header information for VAT, credit card, AR, and miscellaneous data. The bill header information is highlighted in slate grey.

  • 003: Note and Comments Information. Record Type 003 (INTFC_BI_NOTE) is used to enter the text of nonstandard notes and comments. The notes and comments information is highlighted in pink.

  • 004: Tax Detail Information. Record Type 004 (INTFC_BI_TAX) is used to report the details of sales and use taxes paid for fully-paid invoices. The tax detail information is highlighted in lilac.

  • 005: Excise Tax Detail Information. Record Type 005 (INTFC_BI_EXSD) is used to enter India excise, sales tax, and VAT detail. The excise tax detail information is highlighted in yellow.

  • 006: Accounting and Discount/Surcharge Information. Record Type 006 (INTFC_BI_AEDS) is used to enter accounting and discount/surcharge information. The accounting and discount/surcharge fields are highlighted in orange.

Field Type

Displays the type of the field. Types include Character, Date, and Number.

Field Visibility on Data Sheet

Selected fields will be visible on the Data Sheet worksheet.

Select All

Click this button to select all the fields on the Template sheet. All the check boxes on the Field Visibility on Data Sheet row will be selected.

Deselect All

Click this button to deselect all the fields on the Template sheet. All the check boxes on the Field Visibility on Data Sheet row will be deselected. You can also manually select or deselect individual fields based on the requirement.

Update Data Sheet

Click to update the Data Sheet worksheet with the selected fields.

Hide Template

Select to hide the Template worksheet. The Template Visibility Button also acts in union with the Hide Template button.

Template Visibility

If the Template Visibility Button option is Visible, clicking Hide Template button will hide the template and only the data sheet will be visible. The user can click Display Template from the Data Sheet to redisplay the template.

If the Template Visibility Button option is Hidden, clicking Hide Template button will hide the template and only the data sheet will be visible. The user does not have an option to redisplay the template.

If the template is visible to the users, they can make changes to the fields available on the data sheet. An administrator should determine which fields are to be available and whether users should be granted access to change those fields if required.

Use the Spreadsheet Invoice - Data Sheet worksheet to create and import invoice data.

Navigation

Click the BillingInterfaceExcelUpload.xlsm file delivered with your PeopleSoft products and select the Data Sheet tab.

This example illustrates the fields and controls on the Data Sheet worksheet. You can find definitions for the fields and controls later on this page.

 Spreadsheet - Data Sheet

The order of the interface tables allows for quick data entry:

  • Billing Interface Table (INTFC_BI)

  • Billing Interface Table 2 (INTFC_BI2)

  • Billing Interface Header (INTFC_BI_HDR)

  • Notes (INTFC_BI_NOTE)

  • Tax Information (INTFC_BI_TAX)

  • Excise Tax Details (INTFC_BI_EXSD)

  • Accounting Entry & Discount/Surcharge (INTFC_BI_AEDS)

Note these points about buttons:

  • The top 13 rows are frozen so that the interface table buttons are always displayed.

  • The buttons in column A allow you to navigate to the associate columns.

    For example, if you select the Billing Interface Header button, the system takes you to column JM for the same row. This allows you to quickly move around the spreadsheet. If you then select the Accounting Entry & Discount/Surcharge button, the system takes you to column OF for the same row.

  • To view the buttons across the top, select the Billing Interface Table button.

Note these points about entering data:

  • Data that is related to multiple tables should be entered on the same row with the exception of Notes.

    However, you can continue to use the older format of entering data that is related to multiple tables on separate rows.

  • When entering a note,

    • You must manually move to a new row.

    • Use a new header (transaction type Note) in the INFC_BI column/record name.

  • When data is related to multiple tables, the system guides you to enter data on a single row.

  • When entering data for a new line, you must manually move to a new row.

    For example, if line 1 has 8 accounting entry rows, then line 2 must begin in row 9.

  • Tax Information, Excise Tax Details and Account Entry & Discount/Surcharge can contain multiple rows per transaction.

Field or Control

Description

Generate XML

Click to create an XML file. This XML file can be edited and transferred to the PeopleSoft system via a third-party application.

The Excel Save As window appears when you click the Generate XML button. Indicate the file name and the location where you want to save it.

For example, you can write the data to an FTP site and create another process to post the XML file to the URL. When Integration Broker is active, it posts the XML data to the system making the data available for processing by the batch Billing Interface Excel Upload process.

Generate XML & Post

Click to create, save, and post an XML file to PeopleSoft database.

A Login window appears when you click the Generate XML and Post button. Enter your User ID, Password, and Address. If the IB Gateway is not a dedicated gateway server, then the Default Local Node name is also required as part of the URL construction.

Display Template

Click to display the Template sheet if it is hidden. This button is displayed only if the administrator has set the Template Visibility Button option to Visible on the template.

Spreadsheet Invoice - Example

The Example worksheet acts as a guideline on how to enter data.

This example illustrates the fields and controls on the Example worksheet.

 Spreadsheet - Example

Follow the given steps to post an invoice:

To post an invoice:

  1. Enter data for parent record and the child record on the respective rows on the data sheet.

  2. If data is entered in an incorrect location, the entire row will be highlighted to indicate the problem.

  3. Click Generate XML & Post button to populate the Billing Interface tables.

  4. Enter your User ID and Password and click OK.

  5. Click OK to the message displayed.

  6. Save the XML file.

    Each time a new file is saved, the previous file is overwritten.

    Data is entered into the interface tables and an Import Log tab is displayed in Excel.

  7. A confirmation message that the data has posted with the interface ID is displayed after the xml file is saved.

  8. Use the Process Billing Interface Page to run the Billing Interface process.

  9. Use the Review Interface Completions Page to review data imported from the Billing Interface process.

  10. Use the Correct Interface Errors Page to correct errors from the Billing Interface process.

Use the Review Pending Transactions page (INTFC_BI) to view data for interface transactions in the INTFC_BI table.

Navigation:

Billing > Interface Transactions > Review Pending Interface Txn

Review the data posted by either querying the tables or viewing it online.

Use the Process Billing Interface page (RUN_BI_INTFC) to run the Billing interface (BIIF0001).

Navigation:

Billing > Interface Transactions > Process Billing Interface

This example illustrates the fields and controls on the Process Billing Interface page.

 Process Billing Interface

Create the invoice by running the Billing Interface to post the data.

Field or Control

Description

Range Selection

Select one option:

  • Interface ID - select to process all interface IDs contained in the selected interface ID range.

    The From Interface ID and To Interface ID fields represent a range of interface identifiers that you want to include in the interface process.

    • If interface activity within the interface ID range has been previously processed successfully, it is not available to be selected for processing again.

    • If transactions that reference the same interface key field values exist in the completed tables, you receive an error message indicating that you need to update the keys to your transaction in order to avoid a duplicate insert error on the step that moves the completed transactions into the completed tables.

  • Business Unit - select to processes all interface ID’s contained in the selected business unit, or business unit range.

    When running the interface by business unit, if an interface ID contains multiple business units, the system does not process that interface ID. In addition, you cannot use the Business Unit option if you have interface transactions in process.

  • Bill Source - select to process all interface Id’s for a specific bill source within a range of business units.

On the Process Scheduler page, select the Interface & VAT Defaulting (interface and value-added tax defaulting) job if the transactions are liable for Value Added Tax (VAT), which contains both jobs in one. If you decide to run the processes individually, you must do so one at a time.

Use the Message Log page (PMN_BAT_MSGLOG) to check if any of the data returned an error for the particular process instance.

Navigation:

PeopleTools > Process Scheduler > Process Monitor

This example illustrates the fields and controls on the Message log page.

 Message log page

Use the Correct Interface Errors page (Billing, Interface Transaction, Correct Interface Errors) to rectify any errors. Update the data and rerun the Billing Interface.

An alternate method to rectify errors is to update the Data Sheet on the workbook with correct data and repost the XML file.

Note: This method should be used during testing phase only as a new interface ID will be generated and data for the previous interface ID will remain in the tables.

Use the Review Interface Completions page (INTFC_BI_CMP) to review data imported from the Billing Interface process.

Navigation:

Billing > Interface Transactions > Review Interface Completions

This example illustrates the fields and controls on the Review Interface Completions page.

 Review Interface Completions

The tabs that appear on this page are determined by the data entered in the spreadsheet.