Using Mass Maintenance for Interunit Pairs
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
IU_PAIRS_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 Page |
URL_TABLE |
As an option, you might want to change the storage location of the file attachment to another location. See Setting Up Your System for Importing the Excel Worksheets. See also the product documentation for PeopleTools: System and Server Administration, “Using Administration Utilities.” |
|
IU_PAIRS_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. |
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. You can do the following:
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.
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.
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.
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:
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.
Field or Control |
Description |
|---|---|
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:
|
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.
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.
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:
.
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.
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:
.
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.
Field or Control |
Description |
|---|---|
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:
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.