Using Mass Maintenance for Interunit Pairs

This topic provides an overview and discusses how to:

Export interunit pairs for mass maintenance.
Make changes using Excel worksheets.
Set up your system for import the Excel worksheets.
Preview your changes and update the database.

Pages Used to Perform Interunit Pair Mass Maintenance

Page Name	Definition Name	Navigation	Usage
Export	IU_PAIRS_EXPORT	Set Up Financials/Supply Chain, Common Definitions, Inter/Intra Unit, InterUnit Pair Mass Maint, Export	Enter criteria to query the InterUnit Pairs Billing and Transfer Options and, if you choose, the ChartField Values tables to generate a Microsoft Excel worksheet to do interunit pairs mass maintenance.
URL Maintenance	URL_TABLE	PeopleTools, Utilities, Administration, URLs	As an option, you might want to change the storage location of the file attachment to another location.
Import	IU_PAIRS_IMPORT	Set Up Financials/Supply Chain, Common Definitions, Inter/Intra Unit, InterUnit Pair Mass Maint, Import	Preview your interunit pairs mass maintenance for actions to be taken. Import the final revision of your interunit pairs mass maintenance files to update the database.

Understanding Mass Maintenance for Interunit Pairs

When the sheer number of GL business unit pairs makes it impractical to update interunit pairs online, PeopleSoft provides mass maintenance capabilities using Microsoft Excel worksheets.

Using the Export to Excel feature you can export from one or both of the following InterUnit Pairs tables to Excel worksheets in the .xls file format to do your maintenance:

IU_INTER_PR_TR contains interunit pairs with billing and transfer options.
IU_INTER_PR_CF contains associated ChartField values.

After making your changes, save the worksheets in the CSV (comma delimited) file format. You can then preview the actions to be taken before updating your database tables using the Preview feature.

When you are ready to update your database, the changes are incorporated directly to the interunit pairs tables using the Application Engine process, IU_PAIRS_MAINT.

Warning! If a business unit pair has been exported for mass maintenance but not yet imported, no updates should be made to the pair using the online Interunit Pair page . Online updates made after the export are subsequently overwritten when data is imported with the same key values.

Prerequisites

Microsoft Excel 97 or a later version must be installed to use interunit pairs mass maintenance. Excel displays an error message when a worksheet exceeds 64k rows.

Because the Export page is subject to business unit row level security, you must have the appropriate level of security to access interunit pairs for mass maintenance.

Interunit Pair Mass Maintenance - Export Page

Use the Interunit Pair Mass Maintenance - Export page (IU_PAIRS_EXPORT) to enter criteria to query the InterUnit Pairs Billing and Transfer Options and, if you choose, the ChartField Values tables to generate a Microsoft Excel worksheet to do interunit pairs mass maintenance.

Navigation

select Set Up Financials/Supply Chain, then select Common Definitions, then select Inter/Intra Unit, then select InterUnit Pair Mass Maint, then select Export

Image: InterUnit Pair Mass Maintenance - Export page

This example illustrates the fields and controls on the InterUnit Pair Mass Maintenance - Export page. You can find definitions for the fields and controls later on this page.

Using the Business Unit Pairs Selection criteria you can create one or more files that contain the interunit pairs, associated transaction codes, billing options, and transfer options.

For each of the files created by your Business Unit Pairs Selection criteria, you can use the ChartField Selection criteria to optionally create an additional file to maintain the associated ChartField values.

Selection ID

Enter a user-defined value to uniquely identify the set of selection criteria you specify for interunit pairs and associated ChartFields to be exported. One or two Excel worksheets are produced for each Selection ID depending on whether you elect to Include ChartField Values in the mass maintenance. You can affect the size of the resulting Excel worksheets by limiting the number of business unit pairs specified for a particular Selection ID.

Do not define multiple Selection IDs which select the same pairs data or you may inadvertently overwrite previous updates when you import your data.

From GL Unit , To GL Unit , and Transaction Code

Using the following selection methods, enter a set or subset of your interunit pair criteria to produce a manageable worksheet.

In the initial drop down edit box, if you select these fields:

equal to or not equal to- Select a specific value in the associated prompt edit box. If you leave this field blank, the system selects all values.
In or Not In – Enter a comma delimited list in the free form edit box. You cannot enter a range of values. Enter each value and separate it from any following value by a comma. If you leave this field blank, the system selects all values.
Like or Not Like – Enter a wildcard value in the free form edit box, for example FRA% to include all business units beginning with FRA. If you leave this field blank, the system selects all values.

Process Instance

After the export to Excel is completed, the process instance for the file produced from the Business Unit Pairs Selection criteria for this Selection ID is displayed here and is also incorporated into the Excel file name for ease of identification.

Include ChartField Values

If associated ChartField values are to be included in your pairs maintenance, select this check box and provide ChartField Selection criteria to produce a second worksheet for the same Selection ID and sharing the criteria specified in your Business Unit Pairs Selection criteria.

Account Balancing Group

Select or enter criteria to identify any associated Account Balancing Group. If you leave this field blank, the system selects all values.

Entry Type

Select or enter an associated Entry Type. If you leave this field blank, the system selects all values.

Process Instance

After the export to Excel is completed, a process instance for the file produced from the ChartField Selection criteria for this Selection ID is displayed here and is also incorporated into the Excel file name for ease of identification.

Export to Excel

When you select the Export to Excel button, the system returns an error message if the criteria you specify would result in a worksheet that exceeds the 64,000 row limit for Excel. This requires adjustment of the selection criteria to achieve the reduced worksheet size. One or two (if Include ChartField Values is selected) queries are run for each Selection ID specified. Each query generates a spreadsheet (.xls) that is posted to the Report Repository. Each field on the source pairs tables has an equivalent column on the worksheet. An additional column, labeled Delete, is added to each worksheet. Use it to specify that a row is to be deleted by the import process. Use Excel functionality to make other changes and additions to the data.

Pairs Maintenance Reports

Click to access the Excel worksheets in the report repository. The process instance is incorporated in the worksheet file name for ready recognition of specific worksheets.

Process Monitor

Access the Process Monitor to see the progress or status of the export process.

If your exported worksheet is empty this indicates no data met your selection criteria.

Note: Although data values may appear in fields that do not apply to particular transaction codes, these fields should be ignored for those transaction codes. For example, the Print Invoice field should be ignored for the GENERAL transaction code since it is not mapped to the Billing Invoice System Transaction.

Making Changes Using Excel Worksheets

Using the Pairs Maintenance Reports link on the Export page to access the report repository to locate and open the Excel (.xls) worksheet in which you want to make changes. You can manipulate the data using Excel worksheet functionality; however, to delete a row you must enter a Y in the Delete column, located to the far right of the worksheet after the interunit pairs data columns. Do not delete rows loaded from the interunit pairs source tables using the Excel row delete functionality.

Worksheets containing billing and transfer options (from the IU_INTER_PR_TR table) cannot be combined with worksheets containing ChartField values (from the IU_INTER_PR_CF table). If you select to create a Chartfield value worksheet, each row must have a corresponding row in a billing and transfer options worksheet with the same values for:

From GL Unit
To GL Unit
Transaction Code

The following fields in the billing and transfer options worksheet apply to a Transaction Code only if it is mapped to the Billing Invoice System Transaction:

Print Invoice
Generate AR Open Item
Generate AP Voucher
AP Unit
Supplier
Location

The following fields in the billing and transfer options worksheet apply to a Transaction Code only if it is mapped to the Cost Management InterUnit Transfer System Transaction:

Ownership Unit
InterCompany Processing
BI Unit
Customer

If values are specified in the fields above for Transaction Codes to which they do not apply, the import process will overwrite these values with either the field default (if one is defined) or blanks.

You can open, work on and save the worksheets in the file repository or copy the file to your local drive to do your changes in the .xls file format.

When you have completed your changes, save the worksheet either in the file repository or on your local drive as a CSV (comma delimited) Excel file.

Your can then either preview or immediately update your database using the Import page.

If further changes need to be made as a result of your preview, open the worksheet in the .xls format, make your changes and save the file in the CSV (comma delimited) format. You can again preview your changes and actions to be taken before updating your database.

Note: Do not modify column headings or insert/delete columns in the worksheet. The Import process requires that this column information remain unchanged.

URL Maintenance Page

Use the File Locations component (FILE_LOC) to setup file locations in conjunction with URL maintenance.

Use the URL Maintenance page (URL_TABLE) to maintain URL information; for example, change the storage location of the file attachment to another location.

Navigation

select PeopleTools, then select Utilities, then select Administration, then select URLs, then select URL Maintenance.

Image: URL Maintenance page

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

The interunit pairs text file import process (IU_PR_IMPORT) may require the following setup:

The storage location of the file attachment is defined by the URL definition IU_PAIRS_IMPORT. By default, it points to a database record. You may want to change the storage location of the file attachment to another location, such as an FTP server. This is optional.
You are required to define an environmental variable, PS_FILEDIR. This variable defines the temporary flat file location on the process scheduler that runs the file import process. If you have a Unix or OS390 process scheduler, you define this in the psconfig.sh file. If you have an NT process scheduler, you define this in the control panel. Refer to the PeopleTools description for GetFile() PeopleCode for additional details, or consult your system administrator.
If you are using Unicode, the CSV file needs to be in a Unicode format. Excel by default does not save CSV as a Unicode file. You can open in Notepad and save as a Unicode.
If the file contains duplicate values, it does not process any information for that business unit.
If there is an error in the import excel file, you need an error message indication.

See “Understanding File Attachments and PeopleCode”, PeopleTools: PeopleCode Developer's Guide.

InterUnit Pair Mass Maintenance – Import Page

Use the Import page (IU_PAIRS_IMPORT) to preview your interunit pairs mass maintenance for actions to be taken.

Import the final revision of your interunit pairs mass maintenance files to update the database.

Navigation

select Set Up Financials/Supply Chain, then select Common Definitions, then select Inter/Intra Unit, then select InterUnit Pair Mass Maint, then select Import.

Image: InterUnit Pair Mass Maintenance – Import page

This example illustrates the fields and controls on the InterUnit Pair Mass Maintenance – Import page. You can find definitions for the fields and controls later on this page.

Preview

Select to preview the actions to be taken by the system as the result of your changes before actually updating the interunit pairs tables in your database. Preview loads your worksheets, which you attach in CSV format, to a worktable when you click the Import button.

The system inserts an Action column to the right of the Delete column and assigns one of the following Action values to each row based on your changes:

Insert – The row does not exist in the original interunit source table and will be inserted.
Update – The row exists in the original interunit source table and the values in your worksheet will replace existing values in the source table.
Duplicate – One or more rows in your worksheet have the same key values. Duplicate rows are errors and must be resolved before importing to the database.
DUMMY TEXT for completeness check.

The Action column is for information purposes only. The import process ignores this column. Rows to be deleted are identified by a 'Y' in the Delete column and are not identified in the Action column.

When you are ready to update your database, deselect the Preview check box prior to selecting the Import button.

Attached Files

Attach worksheets in the CSV (comma delimited) file format. Enter a value or click Add Attachment to find your files. You can add multiple files to be processed in a single Import.

Note: If you have already imported a CSV file in a previous run and are re-importing it, you must delete the previous CSV file row from the Import page and insert a new row for the updated file.

Import Text Files

When the Preview check box is not selected and you have attached files in the CSV (comma delimited) format, select this button to update the interunit pairs tables with your changes using the PeopleSoft Application Engine process, IU_PR_IMPORT.

Pairs Maintenance Reports

Click to access the system generated Excel worksheets in the report repository. The process instance is incorporated in the worksheet file name for ready recognition of specific worksheets.

Process Monitor

Access the Process Monitor to see the progress or status of the import process.

The import process (IU_PR_IMPORT) will produce an Excel worksheet as output for each worksheet imported. This worksheet will list up to 5 error messages for each row of data imported. These errors must be corrected before the data can be successfully imported. If no errors are listed for a row and the Preview check box was not checked for the import run, that row successfully updated the database.

The error message 'InterUnit Pairs transaction does not exist' occurs when importing a ChartField value worksheet row for a GL BU pair/Transaction Code that does not exist in the billing and transfer options (header) table nor in a billing and transfer options worksheet being imported.