Performing Data Conversion

This chapter provides overviews of customer data conversion, pending data conversion, posted customer history data conversion, and item and payment data conversion and discusses how to perform data conversion using the DC_PENDITEM_CI component interface.

Understanding Customer Data Conversion

PeopleSoft Receivables stores customer setup information—address, contact information, credit information, and processing options—on tables that are keyed by setID. Customer receivable information such as balances, aging information, and payment history is stored on tables keyed by business unit.

This architecture may be very different from that of your existing system and affects conversion activities in two ways:

• During conversion, you populate only the customer setup tables that are keyed by setID.

  The Receivable Update Application Engine process (ARUPDATE) creates and updates the customer information that is stored in the tables that are keyed by business unit.

• You do not convert customer-level historical information directly.

  PeopleSoft Receivables calculates business unit balance and history information for customers based on the converted receivable data.

Understanding Pending Data Conversion

The Receivable Update process is the only method that PeopleSoft Receivables uses to update posted information: customer balance and history information, corresponding item information, item activity, item value-added tax (VAT) activity, and item accounting entries.

The following tables contain pending item information that the Receivable Update process processes:

• Group Control (PS_GROUP_CONTROL)

• Pending Item (PS_PENDING_ITEM)

• Pending VAT (PS_PENDING_VAT)

• Pending Tax (PS_PENDING_TAX)

• Pending Tax Details (PS_PENDING_TAX_DTL)

• Pending Distribution Lines (PS_PENDING_DST)

In this chapter, we discuss approaches and considerations that are important when you convert accounts receivable from your existing system to PeopleSoft Receivables. The "Developing Interfaces for Customers and Pending Items" chapter discusses this topic in more detail.

See Developing Interfaces for Customers and Pending Items.

The PS_GROUP_CONTROL and PS_PENDING_ITEM tables that contain pending information are populated by interface programs that you supply to convert receivables from your existing system and bring in information from billing systems.

The PS_PENDING_VAT table has a specialized use for VAT processing, and the interface program populates it during conversion. You generally do not need to populate the PS_PENDING_DST table, as we discuss later in this section.

The PS_PENDING_TAX and PS_PENDING_TAX_DTL tables are used for tax processing in India. The interface program populates the tables during conversion.

Conversion Example

You decide to keep all customer setup information in one TableSet. Five business units share the customer information in this TableSet. Some customers may have balances in all five business units; others may have a balance in only one or two business units.

The interface program that you create to add customer information to PeopleSoft Receivables populates the necessary customer tables for the TableSet. Meanwhile, the interface program that you create to bring in existing receivables creates groups of pending items. The pending item records contain the relevant business unit and customer combinations.

For each open item a customer has in a business unit, the interface program creates a pending item with an assigned business unit, customer ID, and item ID. When the Receivable Update process processes these pending items, it determines if it can use the customer ID that is established in the TableSet for this business unit. It then creates the customer balance and history information for the customer in the business unit.

Understanding Posted Customer History Data Conversion

The Receivable Update process updates the following tables with posted data at the customer level:

PeopleSoft Receivables does not support updating these four tables outside of the Receivable Update process. Therefore, the amount of item data that you choose to convert determines the amount and type of customer history information you will have after conversion before ongoing processing begins.

Understanding Item and Payment Conversion

You must consider a number of issues related to converting items when planning a conversion and the ongoing use of the system.

This section discusses:

• Conversion of open and closed items.

• Transaction detail for items.

• Payment conversion.

• Line item feature.

• Key dates.

• Reference fields.

• User-defined fields.

• Your accounting entry approach.

• Group design and size.

• Multiple currencies.

Conversion of Open and Closed Items

Convert only open items or convert both open and closed items. Make this decision based on the requirements of your system, the quality of data in your existing system, the amount of historical data that you want in PeopleSoft Receivables after conversion (before ongoing processing begins), and the level of effort that you can devote to the conversion process.

The advantages of converting only open items include:

• Less complex interface programming requirements.

• Smaller conversion effort, in terms of the amount of data and related balancing activity.

• Simplified table setup for areas such as entry type and entry reason definition.

  If the codes that are used in your existing system have changed over time, it may be simpler to create corresponding codes in PeopleSoft Receivables only for planned, ongoing use.

The disadvantages of converting only open items may include:

• No historical data available in PeopleSoft Receivables for history areas such as average days late.

• Research into closed items through your existing system, either online or through reports, during whatever period of time the accounts receivable and credit personnel need the closed item information.

Transaction Detail for Items

For converting open items, evaluate whether to bring in only the current balance on the item or to also include transaction detail that may be in the balance.

For example, suppose in your existing system you have an invoice with an open balance. The balance is a result of the original invoice, minus a credit memo that is applied against it. With PeopleSoft Receivables, you can convert the item with its current balance or bring in both the original invoice and the credit memo.

To bring in the balance only, the conversion interface program creates one pending item. When you bring in both the invoice and the credit memo, you create two pending items, each containing the same business unit, customer ID, and item ID. You assign an appropriate entry type to each item as well as an amount and a number of other values. The Receivable Update process creates one row in the Item Table (PS_ITEM) for the item and two rows in the Item Activity table (PS_ITEM_ACTIVITY), one for each pending item. That is, after you post both pending items, PS_ITEM contains the balance for the item, and PS_ITEM_ACTIVITY contains all rows that affected the balance.

For converting closed items, create two pending items with the same amount: one to open the item and the other to close it. PeopleSoft Receivables does not support the direct entry of a closed item; a pending item cannot have an amount of zero.

Payment Conversion

To understand the alternatives for converting payment information, you should understand how payment processing works in PeopleSoft Receivables.

The goal of all payment application methods—express deposit, the payment worksheet, and the Payment Predictor Application Engine process (ARPREDCT)—is to create pending groups for posting. These payment application methods enable the cash applier to identify what is paid and to create necessary adjustments without having to construct the group directly. In this way, you can process deposits and payments that you received from the bank from the perspective of their source documents.

The end result of payment application, regardless of the method used, is one pending group for each payment: a row in PS_GROUP_CONTROL and one or more rows in PS_PENDING_ITEM, depending on how many items were paid by the payment.

If you have already entered the deposit information and applied the payment in a zero balance form in your current system, no real value exists in creating deposits and payments, and then reapplying the payments to create groups to send through the Receivable Update process. Directly creating the pending items that reflect the result of your current application process is more efficient than using our online or background processes to create the items, reenter these payments, and then reapply them to items. You have already accomplished this matching in your current system.

To understand how to construct these pending items, you need to understand the use of item entry types and automatic entry types.

Item Entry Types

PeopleSoft Receivables uses item entry types to identify pending items that are created during online item entry or by an external interface. When you enter or build pending items that make up a group, you use entry types that you specifically enable for use as item entry types. Two types of item entry types are available: those with positive amounts (associated with the system function IT-01) and those with negative amounts (IT-02).

Automatic Entry Types

Automatic entry types work in the background to translate instructions for overdue charging, payments, maintenance, draft and direct debit processing, and transfers into pending items. When you initiate an online or background process for these types of groups, such as selecting an item on one of the worksheets or running the Payment Predictor process, the system creates the necessary pending item by using the information that is defined on the automatic entry type for that action.

For example, every time you select an item for payment on the payment worksheet, the system uses the entry type, entry reason, and accounting entry information from the WS-01 (Pay An Item) automatic entry type to create the pending item.

Automatic entry types fall into six categories:

• Overdue charges (prefaced by FC).

• Maintenance (prefaced by MT).

• Payment worksheet (prefaced by WS).

• Transfer worksheet (prefaced by TR).

• Direct debit management (prefaced by DD).

• Draft management (prefaced by DM).

Entry Types

The program that converts existing items should create the necessary pending items to represent the level of detail that you want to see after you post the converted items. These pending items include payments that are applied to items and any form of debit or credit memo that you use in your existing system.

You establish entry types and entry reasons, if necessary, to represent the different types of entries that you convert. You then qualify these entry types for use as item entry types on the setup tables. Entry types that are expected to have a positive amount are associated with the IT-01 system function; entry types that are expected to have a negative amount are associated with the IT-02 system function.

On the pending item itself, supply the entry type and entry reason values, and place a value in the entry use ID field—either IT-01 or IT-02—that is associated with the entry type.

The entry types that you use for conversion can be the same as or different from the entry types that you use on an ongoing basis. By using a different entry type, you can clearly identify the entry as created during conversion. If you use the same entry types, you should disable their use as item entry types when conversion is complete to prevent entry of an item with this entry type into the group entry environment.

For example, you decide to convert open receivables and any receivables that are closed within the last 30 days. You must convert transaction detail at least for closed items. You decide to use the PY entry type to represent payments that are made in your existing system, and you enable it as an item entry type of IT-02. You also enabled PY as an automatic entry type to represent payments that PeopleSoft Receivables records.

After you complete the conversion activities, you may want to inactivate PY as an item entry type. Alternatively, you could use a different entry type, such as CP for converted payments, to distinguish converted data from data that PeopleSoft Receivables processes.

You may want to use a similar approach for write-offs, deductions, or other types of transactions that you convert.

Line Item Feature

Use of the line item feature is an important implementation decision that affects conversion planning and scope. The conversion implications of using line items include:

• Deciding whether the line item needs a different entry type and, optionally, an entry reason, to categorize it.

• Developing the pending items that are required to open and close each line item after the closed items are converted.

• Developing the pending items that are required to support transaction details for converted line items, whether open or closed.

What Happens During Line Item Conversion

During conversion:

• The combination of business unit, customer ID, item ID, and item line identify an item in the system.

• If you do not use the line item feature, the item line field contains a zero.

  If you use the line item feature, item lines contain a number other than zero.

• Qualifying the item ID with an item line does not change the amount of data that the system stores on the item or the way that the system handles it.

• The system treats items with an item line the same as items without an item line.

• The system does not combine item lines into a total for inquiry or cash application purposes.

  For example, if you use item lines for an invoice that contains six lines, six items appear on customer item inquiry and worksheet pages.

• If you use line items and also bring in pending VAT information, the option of treating the VAT as a line item instead of recording it separately for each line item is available.

Key Dates

This table describes the significance of two important date fields on the PS_PENDING_ITEM table:

Two conversion approaches using these date fields are available:

• When converting only open items:

  • Choose a common conversion date and place this date in the Accounting Date field.

  • Place the invoice date (or accounting date equivalent field) in the As Of Date field, and base payment terms and aging rules on the As Of Date field value.

    This approach records the converted items in one fiscal year and accounting period only, but maintains the original invoice date for terms and aging. The Accounting Date and As Of Date fields on the pending items from billing usually contain the same value if you choose this method.

• When converting open and closed items and developing history data within the PeopleSoft system from the converted activity, use the appropriate date that is associated with the activity in the existing system as the accounting date on the related pending items.

  This approach also establishes an accounting calendar that spans all the possible converted accounting dates and ensures that the open period range in the business unit options table is broad enough to span all accounting periods. Thus, the system records all converted activity in the fiscal year and accounting period in which it occurred.

Reference Fields

Eight fields in the PS_PENDING_ITEM table contain related document information that the system uses for processing or page displays:

• DOCUMENT (and DOCUMENT_LINE)

• PO_REF

• BILL_OF_LADING

• ORDER_NO

• CONTRACT_NUM

• INVOICE

• LC_ID

• AG_REF_NBR

The maintenance worksheet uses the Document field to facilitate matching unless you enter your own matching criteria. When you specify on the worksheet that the system match items, it matches by comparing the item ID and item line number with the document and document line number of each item in the worksheet. Oracle designed this matching capability for instances in which subsequent activity against an invoice, such as a debit or credit memo, is assigned its own item ID by the originating billing system, with the original item ID supplied as reference information.

If you have this requirement in your application and want to use the matching feature in the maintenance worksheet, you should use the Document field—and line item, if necessary—to hold this information when you create pending items for debit memo and credit memo transactions.

You can display all of the fields on the preceding list without customization on the Item List inquiry page and on the worksheet application pages (payment, draft, maintenance, and transfer). If you have reference-type information for your business that requires this type of display, you may want to use one of the reference fields on the preceding list rather than a user field to hold critical application reference information.

One additional consideration when you are processing subsequent debits and credits from a billing system is whether to swap the document reference and item ID as part of your interface program. For example, your billing system assigns subsequent debit and credit memos their own item ID, but also supplies the original item ID in a reference field. Consider setting up your interface so that it can detect this condition, and have it place the document reference in the Item ID field (with a line number if appropriate) and the item ID in the Document reference field.

The Receivable Update process automatically matches the subsequent activity to the original item and saves you online maintenance activity.

User-Defined Fields

Oracle provides 22 user-defined fields on the PS_PENDING_ITEM table in the delivered system that are exclusively for your use. These fields are known as user fields. The following table lists the fields:

You may want to make two types of modifications to user fields:

• Changing the manner in which the fields are updated.

• Adding more fields.

Changing User Fields

Currently, the system processes all character user fields the same way. Note that the system takes the RP_I_USER values from PS_ITEM USER1 if PS_ITEM is updated. The following statement rolls forward an existing value if none is present on the PENDING_ITEM record:

UPDATE %Table(RP_USER_TAO) 
SET USER1 = RP_I_USER1 
WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE) 
AND USER1 = ' '

You can change the way that you handle these fields on a case-by-case basis. You may want to specify that certain user fields not be overwritten if they have a value, regardless of the value on subsequent transactions. The following statement rolls forward an existing value even if a new value is present on the PENDING_ITEM that is to be processed:

UPDATE %Table(RP_USER_TAO) 
SET USER1 = RP_I_USER1 
WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE) 
AND RP_I_USER1 <> ' '

You can change the way that the system processes numerics and dates in a similar fashion. To make modifications, open the AR_POSTING Application Engine program in PeopleSoft Application Designer.

The following list shows the names of the sections that you need to modify based on the type of user field. You can modify the existing code or add a new effective-dated section with the changes.

• Section USER_ALP (for alpha fields)

• Section USER_NUM (for numeric fields)

• Section USER_DAT (for date fields)

See Also

PeopleTools PeopleBook: PeopleSoft Application Designer Developer's Guide

Your Accounting Entry Approach

You need to make several decisions about how you use the accounting entry capabilities of PeopleSoft Receivables, including:

• How your billing system and PeopleSoft Receivables work together or separately to send accounting entries to your general ledger system.

• Whether you create accounting entries for pending items that are imported from your billing system directly in your billing interface program or enable PeopleSoft Receivables to create them for you.

• How you provide accounting entry information for items that you convert from your existing system.

These decisions depend on whether you use more than one accounts receivable line for each pending item.

General Ledger Interface Approaches

Three common patterns are available for importing receivables-type accounting entry information.

• Method 1: Billing to general ledger.

  Your billing system creates detailed accounting entries for receivables and revenue-related ChartField combinations and distributes them to your general ledger. PeopleSoft Receivables stores enough information about the receivable and VAT accounting entries that are sent to the general ledger to be able to support the creation of accounting entries during subsequent processing.

• Method 2: Control accounting.

  The billing system creates and distributes detailed revenue-related accounting entries to your general ledger, but offsets these entries using a receivables control ChartField combination. Then PeopleSoft Receivables creates accounting entries for billing-related transactions from the offset to the control account and updates the receivables ChartField combination.

• Method 3: Billing to receivables to general ledger.

  PeopleSoft Billing does not send accounting entry information to general ledger. The information goes directly to the PeopleSoft Receivables system, which distributes accounting entries to the general ledger and also uses them for subsequent processing.

Method 1: Billing to General Ledger

If you use this approach, the purpose of the accounting entries that PeopleSoft Receivables stores is to support the creation of subsequent accounting entries.

If you use only one receivables ChartField combination for each pending item, you can choose to have the Pending Group Generator Application Engine process (AR_PGG_SERV) generate these accounting entries for you by using an item entry template. You establish the baseline for subsequent processing, but prevent distribution by indicating on the item entry template that PeopleSoft Receivables will not distribute the accounting entries to the general ledger.

If you require more than one receivables ChartField combination for each pending item, PeopleSoft Receivables will not generate these accounting entries for you. Instead, you must populate PS_PENDING_DST with one or more lines that correspond to the open balance that is in each receivables ChartField combination to offset those amounts at payment or maintenance time. Use a flag on PS_PENDING_DST to indicate that PeopleSoft Receivables should not distribute the accounting entries to the general ledger. Because PeopleSoft Receivables does not distribute the accounting entries that you create, you can import only the lines that you need. A balanced entry is not required.

Method 2: Control Accounting

If you use this approach, the accounting entries that PeopleSoft Receivables stores have two purposes:

• To be distributed to the general ledger to clear the receivables control ChartField combination.

• To support the creation of subsequent accounting entries.

If you use only one receivables ChartField combination for each pending item, you can choose to have the Pending Group Generator process create these accounting entries for you through the use of an item entry template. The accounting template limits you to only two lines that are used for a balanced entry. Indicate on the template that PeopleSoft Receivables distributes the accounting entries to PeopleSoft General Ledger.

If you require more than one receivables ChartField combination for each pending item, PeopleSoft Receivables does not generate these accounting entries for you. You must populate PS_PENDING_DST directly as part of your billing interface program, indicating that PeopleSoft Receivables distributes the accounting entries to the general ledger. In this case, you must provide a fully balanced set of accounting entries.

Method 3: PeopleSoft Billing to PeopleSoft Receivables to PeopleSoft General Ledger

If you use this approach, the accounting entries that PeopleSoft Receivables stores have two purposes:

• To be distributed to the general ledger.

• To support the creation of subsequent accounting entries.

If you use only one receivables ChartField combination for each pending item and you have only one offsetting line, you can choose to have the Pending Group Generator process create these accounting entries for you through the use of an item entry template. Because you are limited to only two lines on the accounting entry template, you will not likely use this approach to create accounting entries that have the level of detail that the billing system usually provides. Indicate on the template that the accounting entries are distributed to the general ledger.

If you require more than one receivables ChartField combination for each pending item or more than one offsetting line, PeopleSoft Receivables cannot generate these accounting entries for you. Populate PS_PENDING_DST directly as part of your billing interface program, indicating that PeopleSoft Receivables distributes the accounting entries to the general ledger. In this case, you must provide a fully balanced set of accounting entries.

Conversion Implications

When you convert open or closed items from your current receivables system, you must establish the accounting entries that are required for subsequent processing, but you will not distribute them to the general ledger. This is similar to the ongoing processing that occurs in method 1, billing to general ledger.

If you do not distribute the accounting entries for converted items but you want to distribute them when importing ongoing billing information, you do not have to establish different entry types. Specify that PeopleSoft Receivables distributes accounting entries to the general ledger. Then for your conversion groups only, set values to prevent the resulting accounting entries from being distributed to the general ledger.

Populating PS_PENDING_DST

You can choose to have your interface programs populate PS_PENDING_DST concurrently with PS_GROUP_CONTROL and PS_PENDING_ITEM. This gives you control of the accounting entries so that entries that are too complicated for the templates to generate can be brought in from an interacting system.

Note. If you decide to create accounting entries within PeopleSoft Receivables as part of your background conversion or interface processing, you must supply additional field values on the pending item to support this processing. We discuss these fields in the "Developing Interfaces" chapter.

See Also

Developing Interfaces for Customers and Pending Items

Group Design and Size

Because your interface program builds groups for conversion and ongoing interface purposes, you must take several things into consideration in terms of group size and group structure. The most important consideration is how the group control and status information enables the operator to tie back to the source system for either conversion or ongoing interface balancing.

You use two fields on PS_GROUP_CONTROL to categorize groups: Origin and Group Type. Origin typically refers back to the source system, and Group Type represents the type of activity. Possible uses of these fields include:

• Establishing different origins for conversion groups from ongoing billing interface groups.

• Establishing different group types for conversion groups.

• Establishing a different group type for open and closed items and limiting a group to containing either open or closed items.

• When converting open and closed items with the intent of establishing history, creating one group for each month of activity that you convert and using the Group ID field to indicate the time frame.

No optimal size exist for a group in terms of how many pending items the group should contain. After balancing back to the source system is considered, other issues include:

• Slight processing overhead that is associated with a group, meaning that fewer groups with more pending items in each will then process more quickly.

• An online environment that contains some restrictions for the number of rows that can appear on a page.

  You can use two page formats to view external groups online. For the first page type, you view items in a scroll and scroll up or down to see all the items. In this type of page group, the number of pending items that you can display without receiving a page processor error is between 50 and 100, depending on how many pending distribution rows exist.

  For the second type of page, one pending item appears at a time. For example, PeopleTools limits the number of pending items in the list to 255 for some lists.

  Although you may think from this example that a group should never contain more than 255 pending items, a larger group works quite well for several reasons. For example, you have a group containing 5,000 pending items. The first time the group is posted, none of the 5,000 pending items are posted. Each of the items is unlikely to have a unique error. When you bring up the first 255 rows on an error correction page, you may discover that most of them have the same error, such as an invalid payment term. You correct the value on the setup table and post the group again. Then you find that most of the pending items are posted and you have only a few remaining rows to correct.

  After you work out setup-related conversion issues and have your ongoing interface established, the number of errors that occur, if any, should be minimal. To facilitate this process, you will find helpful the task of first developing test conversion and interface groups that contain a sample of the data that you process. This helps to eliminate setup issues and enables you to process the group size for conversion and ongoing interfaces that best suit business control and balancing.

Multiple Currencies

If you are converting or importing activity by using a foreign currency, you need to consider how the groups are composed. You can choose to bring in more than one currency within the same group or to limit groups to a single currency.

If you plan to create accounting entries during background processing, you must perform currency conversion in advance on the pending items and any pending VAT information or pending taxes for India that you are converting or importing.

Performing Data Conversion Using the DC_PENDITEM_CI Component Interface

This section provides an overview of the DC_PENDITEM_CI component interface and discusses how to:

• Run the component interface conversion process.

• Verify that the data conversion is successful.

• Post the items in PeopleSoft Receivables.

Understanding the DC_PENDITEM_CI Component Interface

When you implement the PeopleSoft Receivables system, you will want to convert existing information to the PeopleSoft Receivables system. Use the DC_PENDITEM_CI component interface and the Excel to Component Interface utility to populate existing PeopleSoft tables with data from third-party applications. The interface uses a combination of technologies such as iScript, Microsoft Excel spreadsheets, and Visual Basic for Application programs to convert data.

The DC_PENDITEM_CI component interface is a PeopleTools object that is created in PeopleSoft Application Designer that enables you to access a PeopleSoft component from another application. The component interface uses business logic to update the data in the Pending Item tables. You do this by using the ExceltoCI.xls Excel spreadsheet to map the data and move the data into the PeopleSoft Receivables tables.

The component interface populates these tables:

• PS_GROUP_CONTROL

• PS_PENDING_ITEM

• PS_PENDING_VAT

• PS_PENDING_TAX

• PS_PENDING_TAX_DTL

• PS_PENDING_DST

Note. Excel has a physical limitation of 256 columns and 65k rows. To work around these limitations, you may need to limit the import data so that the number of rows on the Data Input page does not exceed 65k.

The user must have privileges to the DC_PENDITEM_CI component interface with full access to create methods to run the component interface.

Running the Component Interface Conversion Process

Use the Excel to Component Interface utility to run the component interface. When you build the worksheet template, select the DC_PENDITEM_CI component interface.

The PeopleTools documentation describes how to use the Excel to Component Interface utility in detail.

See Also

PeopleTools PeopleBook: PeopleSoft Component Interfaces

Verifying That the Data Conversion Is Successful

Use Application Designer to access PeopleSoft Query (Go, Query) where you can search for records in the PS_PENDING_ITEM table and the other tables that the interface populates.

Posting the Items in PeopleSoft Receivables

Run the Receivable Update process to post the pending items and update customer balances.

See Also

Running Receivable Update