Performing Data Conversion

This chapter provides an overview 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

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.

  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 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 Receivables. The “Developing Interfaces for Customers and Pending Items” chapter discusses this 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 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:

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

There are a number of issues related to converting items to consider in planning a conversion effort and 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 choose to 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 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 Receivables only for planned, ongoing use.

The disadvantages of converting only open items may include:

• No historical data available in 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 Receivables, you have the option of converting the item with its current balance or of bringing 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_ACTIVITY0, one for each pending item. In other words, 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. 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, it is helpful to understand how payment processing works in 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, there is no real value in creating deposits and payments, and then reapplying the payments to create groups to send through the Receivable Update process. It is more efficient for you to directly create the pending items reflecting the result of your current application process than to use 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

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 comprise a group, you use entry types that you specifically enable for use as item entry types. There are two types of item entry types: 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 behind the scenes 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, it is important to disable their use as item entry types once 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 Receivables records.

After you complete the conversion activities, you may want to inactivate PY as an item entry type. Alternatively, you could decide to use a different entry type, such as CP for converted payments, to distinguish converted data from data that 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 if the line item may need 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 following occurs:

• 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 choose to use item lines for an invoice that contain 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

Two important date fields on the PS_PENDING_ITEM table have significant conversion implications:

Two conversion approaches using these date fields are:

• 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 has the effect of recording the converted items in one fiscal year and accounting period only, but maintaining 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 also involves establishing an accounting calendar that spans all the possible converted accounting dates and ensuring that the open period range on the business unit options table is broad enough to span all accounting periods. This ensures that 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. We 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 list above 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 that are listed above rather than a user field to hold critical application reference information.

One additional consideration when processing subsequent debits and credits from a billing system is 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

We provide 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:

There are two types of modifications you may want to make 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 the value with which 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 should 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 Enterprise Application Designer.

Below are 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

Enterprise PeopleTools PeopleBook: PeopleSoft Application Designer

Your Accounting Entry Approach

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

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

• Whether you create accounting entries for pending items that are interfaced from your billing system directly in your billing interface program or enable 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

There are three common patterns for interfacing 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. 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 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.

  Billing does not send accounting entry information to general ledger. The information goes directly to the 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 Receivables stores is to support the creation of subsequent accounting entries.

If you use only one receivables ChartField combination for each pending item, you may 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 Receivables will not distribute the accounting entries to the general ledger.

If you require more than one receivables ChartField combination for each pending item, 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 Receivables should not distribute the accounting entries to the general ledger. Because Receivables does not distribute the accounting entries that you create, you can interface 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 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 may 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 Receivables distributes the accounting entries to General Ledger.

If you require more than one receivables ChartField combination for each pending item, 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 Receivables distributes the accounting entries to the general ledger. In this case, you must provide a fully balanced set of accounting entries.

Method 3: Billing to Receivables to General Ledger

If you use this approach, the accounting entries that 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 may 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, it is unlikely that you will 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, Receivables cannot generate these accounting entries for you. Populate PS_PENDING_DST directly as part of your billing interface program, indicating that 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 interfacing ongoing billing information, you do not have to establish different entry types. Specify that 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 general ledger.

Populating PS_PENDING_DST

You may 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 interfacing system.

Note. If you decide to create accounting entries within 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 relates to how the group control and status information enables the operator to tie back to the source system for either conversion or ongoing interface balancing.

There are two fields on PS_GROUP_CONTROL that you use 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.

There is no optimal size for a group in terms of how many pending items the group should contain. After balancing back to the source system is taken in consideration, 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 it may appear from this example that a group should never contain more than 255 pending items, there are several reasons that a larger group works quite well. 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. It is unlikely that each of the items has 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 only have a few remaining rows to correct.

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

Multiple Currencies

If you're converting or interfacing activity by using a foreign currency, you need to consider how the groups are composed. You may 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 interfacing.

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

Understanding the DC_PENDITEM_CI Component Interface

When you implement the Receivables system, there is existing information that you want to convert to the 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, 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. This is done by using the ExceltoCI.xls Excel spreadsheet to map the data and move the data into the 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 Excel’s 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

Enterprise 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 Receivables

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

See Also

Running Receivable Update