Interface Utilities User Guide

Back to User Guides

Interface Utilities

The P2 IDEAS Interface Utilities module (referred to sometimes as ASCII File Interface and Interface/Consolidation module) is used for two purposes:

1. To import data from other financial database systems to P2 IDEAS. The ASCII File Definition function is used to set up parameters for importing the data, then, the ASCII File Import function is used to bring the data into automated journals within the General Ledger module. The automated journals are then processed, validated, and posted according to the procedures described in General Ledger module documentation.

2. To consolidate data from one P2 IDEAS site to another, where the sites do not share a common database. For example, remote locations with distinct installations may need to be consolidated at a central location. The consolidation functions in the Interface Utilities module provide a secure, easy method to accomplish this with a clear audit trail.

The Consolidation Export function is used to generate export files from the remote or source site(s) for import to the destination or target site(s). The Site Definition function is used to create site codes and descriptions to identify each source site from which data will be consolidated. Finally, the Consolidation Import function is used to import such data into the destination or target site.

Modules to Set Up Before Interface Utilities

P2 IDEAS requires that the following modules be set up before defining settings or configurations in the Interface Utilities module:

• The General LedgerModule
• The Currency Conversion Module (for Multiple Currencies)

Note: Several flags, which impact the way the Interface Utilities module performs, are stored in the initialization file used each time the module is launched.

Interface Utilities Setup Procedure

To set up P2 IDEAS to operate the Interface Utilities module, complete the following steps in the order listed.

Screen-by-Screen Instructions

P2 IDEAS organizes functions into menus. The Interface Utilities module has one primary menu, the Functions menu.  The following table lists the functions that fall under this menu:

P2 IDEAS also provides quick and easy access to the following functions by clicking on associated shortcut buttons:

The sections to follow discuss each of the functions individually.

Functions Menu

The Functions menu includes all of the processes users will perform in the Interface Utilities module. Each function is discussed in detail in the sections that follow, based on the order in which it appears on the menu. 

ASCII File Definition

This function is used to define the parameters that P2 IDEAS will use to import data from third party systems, including the location and name of the source file for the interface, the ledger, company, and journal to which the file will be imported, the maximum number of lines that any single journal batch may contain, and, if the source file is so large that it will result in multiple batches in P2 IDEAS, the Fully Qualified Account (FQA) that will be used to balance each batch.

When creating interfaces for the first time, users may opt to create text files in the Source folder to set up the interface paths in advance of the first anticipated use. Once the File Name is selected and saved, these text files may be deleted.

Processing the ASCII File Definition Screen

Select ASCII File Definition from the Functions menu or click the AD shortcut button.

• Process Name – Create a new process name by entering it in the space provided. Select an existing process for review or edit it using the drop-down list.
• File Name – Enter the file name of the source file. Users may also click the ellipsis button (…) to open Windows Explorer to browse for the source file.
• Ledger Name – Select the ledger from the drop-down list. If only one ledger is defined, it displays automatically.
• Company – Select the company to which the interface file will be imported. If only one company is defined, it displays automatically.
• Journal – Select the journal to which the interface file will be imported.
• Max Lines – Enter the maximum number of lines that may be imported to any single batch for the journal selected. Source files exceeding that line total will be broken into additional batches as necessary, with each batch containing a balancing FQA entry.
• Balance FQA – Enter the FQA that will be used to balance batches. This is normally an FQA created specifically for this purpose. This FQA is required if an interface source file exceeds the maximum allowed line length. 

Once all selections have been made, click the Save button to save the process.

ASCII File Import

This function imports ASCII files created by third-party systems into P2 IDEAS as automated journals. The automated journals are then processed, validated, and posted within the General Ledger module using the processes described in the General Ledger manual.

Processing the ASCII File Import Screen

Select ASCII File Import from the Functions menu or click the AI shortcut button.

• The left side of the screen is used to select and display information about the interface process being executed. It is also used to select a period list from which an appropriate period is then selected.
• The right side of the screen is used to indicate if FQAs will validate during import, print batch reports during the import process, verify and/or change the location of the source file for the import, and execute the process or exit the screen.
• The status bar at the bottom of the screen will show the progress of an import process during execution.

• Process Name – Choose an interface process from the drop-down list. Users may also choose to execute all processes by checking the All box.
• File Name – This file name is defined in ASCII File Definition. Use the ellipsis button to browse for a new file, if needed.
• Ledger Name – This field displays the ledger selected in ASCII File Definition.
• Company – This field displays the company selected in ASCII File Definition.
• Journal – This field displays the journal selected in ASCII File Definition.
• Max Lines – This field displays the max lines entered in ASCII File Definition.
• Period List – Select the desired period list from the drop-down list.
• Period – Select the desired period from the drop-down list.
• Validate FQAs – Select this checkbox to prompt P2 IDEAS to validate FQAs during the import process. 
• Print Batch Report – Select this checkbox to generate an interface batch control report once the import process has completed.  Use this report to verify successful completion of the import and check for any errors.
• Source File Location – Use this section to review the location of the folder where the source interface file is stored.  Make changes as necessary.

Once all selections have been made, click the Execute button. This will import the ASCII file and generate the Print Batch Report (if selected) in a Crystal Reports viewer.

The import file name will be altered to include entity, period, and batch references. Its content will be altered to include date, time, batch references and other information, and it will be physically moved to a processed file location (see the appendix for further discussion of file location flags).

Consolidation Export

This function is used to export detailed journal information from one P2 IDEAS site for import to another, where each site is a distinct database. Since the function is designed to export finalized information from the source site, only posted or inactive journals may be exported.

When executed, the Consolidation Export function creates a Microsoft Access database, which can then be imported to another P2 IDEAS system. The Microsoft Access database is password-protected to prevent alteration prior to import to the destination P2 IDEAS database.

Processing the Consolidation Export Screen

Select  Consolidation Export from the Functions menu or click the CE shortcut button.

• Ledger ID – Click the down arrow to the right and select the ledger from which data will be exported.
• Entity (or Company) – Enter the desired entity or use the Spyglass  button to search. The Entity Search window includes a checkbox to select all entities.

• Transaction Code – Enter one or more transaction codes or use the Spyglass  button to search. The Transaction Code search window includes a checkbox to select all transaction codes.

• Status – There are two status checkboxes: Posted and Inactive. Select one checkbox or both.
• Period – Enter the period from which consolidation data will be exported or click the Spyglass  button search.
• Save As – Enter the path and filename to which the consolidation will be exported or click the Spyglass  button to open a Windows Explorer search screen and browse for the proper path and file name.

Once all selections have been made, select the Preview Export button to generate the Consolidation Export preview in a Crystal Reports viewer. This report should be reviewed for any errors or omissions. The Execute  button will not activate until the report has been reviewed.

Once the preview has been reviewed and closed, the Execute Export button will be active. 

Click this button to run the selected consolidation export process. The export process will generate a Consolidation Batch Export Control report in a Crystal Reports viewer.

Click the Print Export History button to generate a Consolidation Batch Export Listing report in a Crystal Reports viewer.

Use Entity and Transaction Code look-ups to select multiple options by clicking the desired items and using the Shift + Control buttons for final selections.

Consolidation Import

This function imports detailed journal information from MS Access files into a destination or target P2 IDEAS site. Consolidation imports may be performed in several ways depending on the conversion option selected, with the screen inactivating certain parameters and displaying others based on the parameter selected.

Processing the Consolidation Import Screen

Select Consolidation Import from the Functions menu or click the CI shortcut button.

• Source Site – Select a source site from the drop-down list. If none exist in the list, they must be defined using site definitions, which will be discussed below.
• Ledger ID – Select the ledger to which data will be imported. If only one is defined, it displays automatically.
• Open File – Enter the path and file name of the Microsoft Access database that will be imported or use the Spyglass  button to open a Windows Explorer search screen and browse for/select the proper path and file name.

• Period – Enter the period to which consolidation data will be imported or use the Spyglass  button to search.
• Convert – Select from the three options available for converting source data from the import file to an alternate currency. The three alternatives are Y, N, and R. Each is discussed below.

Select N to indicate that source data will not be converted. The Conversion Date section at the bottom-left of the screen will be inactive.

Select Y to indicate that source data will be converted at the time it is posted in the General Ledger module using conversion rates stored in the Currency Conversion module. The Conversion Date section at the bottom-left of the screen will be active and must be populated. There are three options within the Conversion Date section: Imported Conv Date, Journal Entry Date, or Specified Date.

• With Imported Conv Date, the journal will be converted using conversion rates associated with the import date. 
• With the Journal Entry Date option, the journal will be converted using conversion rates associated with the dates of individual journal lines. 
• With Specified Date, the journal will be converted using rates associated with the date entered.

Select R to convert the data as the consolidation import is processed, resulting in an imported journal in the converted currency. The Conversion Date section will be inactivated. Instead, a Fixed Rate section will display requiring entry of an appropriate operator (multiply “*”, or divide “/”) and a conversion rate.

P2 IDEAS will convert amounts from the source site by performing the mathematical process indicated on the import file using the conversion rate indicated. 

This option is typically used when the consolidating site uses a different currency than the originating site.  It allows import to the consolidating site journals in that site’s currency.

Once all selections have been made, use the Preview Import button to generate the consolidation import preview in a Crystal Reports viewer. Review the report for any omissions or errors in the import file.

After the Consolidation Import preview has been reviewed and closed, the Execute Import button will activate. Click the Execute Import button to begin the import process. Once the process completes, the Consolidation Batch Import Control report will be generated in a Crystal Reports viewer. Journals can then be processed in the General Ledger module.

Site Definition

The Site Definition function creates site codes and descriptions that identify the source sites from which information will be consolidated.

Source sites must be defined using the Site Definition function before the Consolidation Import function can be used. Once defined, these sites become available for selection using the Consolidation Import function, allowing users to complete processing.

Processing the Site Definition Screen

Select Site Definition from the Functions menu.

• Site Code – Select an existing code from the drop-down list or create a new code by entering the desired code.
• Site Description – For existing codes, the description will display automatically. When creating a new code, enter the appropriate description.

Year End

If the Year End function appears in the Interface Utilities module, executing it is a required year-end processing step. If this function is not in your menu, this step does not need to be performed. 

Caution: Before executing Year End, refer to the main Year-End Processing document to ensure all other required steps are performed in the correct order. To obtain a copy of this document, please contact P2 IDEAS Customer Support.

Note: Clients that have the Year End function have a ConsFlag= entry under the IDEAS section in their IDEAS ini file. The available options for the flag are N, Y, or R. These values make the Year End Consolidation function visible in the Interface Utilities menu.

To execute the Consolidation Year End process, select Year End from the Functions menu, then click the Yes button on the message box that appears. This clears the Batch_Import and Batch_Export tables used by the Year End (Consolidation) function. This allows the import and export of batches in the new year.

Once complete, a dialog box appears. Click OK.

Year End can be processed multiple times.

Auto Interface Parameters

Processing the Auto Interface Parameters Screen

Select Auto Interface Params from the Functions menu.

• Period – Enter in the period or use the Spyglass  button to search. 

Once the desired period is selected, click the Save button.

ASCII Summarization Export

Use the ASCII Summarization Export feature to export an ASCII file.

Processing the ASCII Summarization Export Screen

Select ASCII Summarization Export from the Features menu or use the  button on the toolbar.

• Ledger ID – Select the appropriate ledger ID from the list.  If only one ledger is defined, it displays automatically.
• Entity (or Company) – Enter the appropriate entity or use the Spyglass  button to search.
• Period – Enter the period to use for the export.
• Currency Conversion – Based on the selection in the Convert field, additional fields in this box may activate. The default selection is N. Additional fields will activate when Y is selected.

• • Convert – Select Y or N from the drop-down list.  N indicates that the system should not convert the currency during the export. Y indicates that the system should convert the currency during the export. Selecting Y activates the three radio buttons in this box:  Use Rate, Use Date, Use Transaction Date.
  • Use Rate – Select this radio button to enter a rate to use for the conversion.
  • Use Date – Select this radio button to enter a date for the conversion. The system will run the conversion calculation with the rate for the date specified.
  • Use Transaction Date – Select this radio button to prompt the system to use the transaction dates of individual transactions for the currency conversion. P2 IDEAS will run the conversion calculation with the rate for the date of the transaction(s).

Once all selections have been made, click the Execute  button to start the export process. The Export process will use the options specified in ASCII Export Summarization Parameter Maintenance.

Clear ASCII Summarization Export Parameters

Use the Clear ASCII Summarization Export Parameters feature to clear the summarization data for a selected period. As exports occur, the files may become extremely large. This can lead to a slowing of system performance. Clearing the summarization files can speed system performance.

Processing the Clear ASCII Export Summarization Screen

Select Clear ASCII Export Summarization from the Features menu.

• Period – Enter the period for which to clear the summarization data. To clear data for all periods, select the All checkbox.

Once the desired period has been selected, click the Execute  button. The message below will display. Click the appropriate answer. Yes will proceed with the delete process. No or Cancel will take the user back to the Clear ASCII Summarization Export Parameters window.

ASCII Export Summarization Parameter Maintenance

Use the ASCII Export Summarization Parameter Maintenance function to set parameters for ASCII export summarization processes. These parameters are set up by ledger. Each ledger defined in P2 IDEAS can have different parameters, as well as different file locations and success messaging.

Processing ASCII Export Summarization Parameter Maintenance

Select ASCII Export Summarization Parameter Maintenance from the Functions menu. The ASCII Export Summarization Parameter Maintenance window consists of a header and a tab section. The tab section contains four tabs:  Journal, Element, Exception FQA, and Exclusions.

• Source Ledger ID – Select the appropriate ledger from the drop-down list. If parameters have already been defined for the selected ledger, they display automatically.

Journal Tab

Use the Journal tab to designate some basic information. This information includes the file and its path, as well as the e-mail address to use for success messages.

• Target Path – Enter the location to which the file will be exported. Use the binoculars button to open a browse window and make a selection.

• ASCII File Name – Enter the desired name for the ASCII file. This will be the file name for every export from the selected ledger. The file will be overwritten each time the process is run.
• Target Email – Enter the email address to which a success message should be sent.
• Email Message – Enter the desired text for the success message.
• Trans Type – Enter the transaction types to export or use the Spyglass button to search. Users may select more than one type. If a transaction type is added to the parameters in error, use the  button to make a correction.

Element Tab

Use the Element tab to select elements for the export. Up to three elements can be defined.

• Target – The fields in the target column display the target element names.
• Source – The fields in the Source column allow the user to select the source of the element. The number of options available in the drop-down list depends on system configurations.
• Selection – The fields in the Selection column allow the user to select what value will represent the element. Options include Element ID and User fields 1 – 3.

Exception FQA Tab

If an FQA should be included in the selection criteria contrary to the selections made in the Element tab, use the Exception FQA tab to designate the addition.

• Source FQA – Enter the source FQA.
• Target FQA – Enter the target FQA. This is what the FQA number to which the source FQA will be converted.

Exclusions Tab

The Exclusions tab can be used to exclude FQAs that would normally be included in the export based on the criteria in the Element tab.

• FQA Mask – Enter the FQA to exclude from the export.

Once all selections have been made, click the Save  button. Users may also generate a listing of the parameters in a Crystal Reports viewer by clicking the Print Preview  button.

P2 IDEAS Support

If questions remain after reading this user guide, feel free to contact P2 IDEAS support for assistance:

E-mail: IDEASsupport@p2energysolutions.com

Phone: 

• North America - 1-844-REACHP2 [1-844-732-2472]
• Asia-Pacific - +61 8-9241-0314