Transcription
Integration Manager User GuideVersion 6.6 — April 6, 2022
Copyright 2013, 2022, Oracle and/or its affiliates.This software and related documentation are provided under a license agreement containing restrictionson use and disclosure and are protected by intellectual property laws. Except as expressly permittedin your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast,modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by anymeans. Reverse engineering, disassembly, or decompilation of this software, unless required by law forinteroperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be errorfree. If you find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing iton behalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of suchprograms) and Oracle computer documentation or other Oracle data delivered to or accessed byU.S. Government end users are "commercial computer software" or "commercial computer softwaredocumentation" pursuant to the applicable Federal Acquisition Regulation and agency-specificsupplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure,modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including anyoperating system, integrated software, any programs embedded, installed or activated on deliveredhardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) otherOracle data, is subject to the rights and limitations specified in the license contained in the applicablecontract. The terms governing the U.S. Government's use of Oracle cloud services are defined by theapplicable contract for such services. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information managementapplications. It is not developed or intended for use in any inherently dangerous applications, includingapplications that may create a risk of personal injury. If you use this software or hardware in dangerousapplications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damagescaused by use of this software or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarksof their respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarksare used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is aregistered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content,products, and services from third parties. Oracle Corporation and its affiliates are not responsible for andexpressly disclaim all warranties of any kind with respect to third-party content, products, and servicesunless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation andits affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or useof third-party content, products, or services, except as set forth in an applicable agreement between youand Oracle.If this document is in public or private pre-General Availability status:This documentation is in pre-General Availability status and is intended for demonstration and preliminaryuse only. It may not be specific to the hardware on which you are using the software. Oracle Corporationand its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect tothis documentation and will not be responsible for any loss, costs, or damages incurred due to the use ofthis documentation.
If this document is in private pre-General Availability status:The information contained in this document is for informational sharing purposes only and should beconsidered in your capacity as a customer advisory board member or pursuant to your pre-GeneralAvailability trial agreement only. It is not a commitment to deliver any material, code, or functionality, andshould not be relied upon in making purchasing decisions. The development, release, timing, and pricingof any features or functionality described in this document may change and remains at the sole discretionof Oracle.This document in any form, software or printed matter, contains proprietary information that is theexclusive property of Oracle. Your access to and use of this confidential material is subject to the termsand conditions of your Oracle Master Agreement, Oracle License and Services Agreement, OraclePartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which hasbeen executed by you and Oracle and with which you agree to comply. This document and informationcontained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oraclewithout prior written consent of Oracle. This document is not part of your license agreement nor can it beincorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program websiteat http://www.oracle.com/pls/topic/lookup?ctx acc&id docaccAccess to Oracle SupportOracle customers that have purchased support have access to electronic support through My OracleSupport. For information, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id info or visit http://www.oracle.com/pls/topic/lookup?ctx acc&id trsif you are hearing impaired.Sample CodeOracle may provide sample code in SuiteAnswers, the Help Center, User Guides, or elsewhere throughhelp links. All such sample code is provided "as is” and “as available”, for use only with an authorizedNetSuite Service account, and is made available as a SuiteCloud Technology subject to the SuiteCloudTerms of Service at www.netsuite.com/tos, where the term “Service” shall mean the OpenAir Service.Oracle may modify or remove sample code at any time without notice.No Excessive Use of the ServiceAs the Service is a multi-tenant service offering on shared databases, Customer may not use the Servicein excess of limits or thresholds that Oracle considers commercially reasonable for the Service. If Oraclereasonably concludes that a Customer’s use is excessive and/or will cause immediate or ongoingperformance issues for one or more of Oracle’s other customers, Oracle may slow down or throttleCustomer’s excess use until such time that Customer’s use stays within reasonable limits. If Customer’sparticular usage pattern requires a higher limit or threshold, then the Customer should procure asubscription to the Service that accommodates a higher limit and/or threshold that more effectively alignswith the Customer’s actual usage pattern.
Table of ContentsOverview . 1Getting Started . 1OpenAir Integration Manager . 3OpenAir Account Settings . 6Installation . 8Installing OpenAir Integration Manager . 8Installing a New Version . 9Uninstalling OpenAir Integration Manager . 9Using Text CSV Files . 10OpenAir Records to Export and Import . 10Exporting Data to a Text CSV File . 13Field Mapping for Export . 15Exporting Using External Field Lookup . 17Exporting Calculated Fields . 19Exporting Custom Fields . 20Formatting Data for Export . 21Combining Data on Export . 26Adding Constant Headers . 28Filtering Export Data . 29Accounting Settings . 33Importing CSV Data to OpenAir . 36Field Mapping for Import . 39Importing Using External ID Lookup . 41Formatting Data for Import . 44Importing Calculated Fields . 47Importing Custom Fields . 50Creating and Editing Text CSV Shortcuts . 52Creating a Text CSV Shortcut . 53Creating a Text CSV Shortcut for Import . 55Using a Text CSV Shortcut . 59Editing a Text CSV Shortcut . 61Upgrading a Text CSV Shortcut . 63Configuring the QuickBooks Integration . 65Overview of the Configuration . 65Setting Up OpenAir for QuickBooks . 66OpenAir Integration Manager Requirements . 67OpenAir Internal Switches . 67Setting Up Taxes in OpenAir . 68Setting Up Invoices in OpenAir . 69Setting Up Projects in OpenAir . 69Setting Up Custom Fields in OpenAir . 70Running OpenAir Integration Manager . 73Setting Up QuickBooks Settings in OpenAir Integration Manager . 74Checking External IDs . 81Setting Up QuickBooks for OpenAir Integration Manager . 82Using QuickBooks . 86Overview of the Integration . 86Exporting OpenAir Data to QuickBooks . 87Importing QuickBooks Data to OpenAir . 89Configuring Optional Features . 90Setting AR/AP Tax Codes in the Canadian edition of QuickBooks . 90Posting AP Taxes to Dedicated Expense Accounts . 91Exporting Non-Reimbursable Expenses . 92
Creating and Editing QuickBooks Shortcuts . 94Creating a QuickBooks Shortcut . 95Using a QuickBooks Shortcut . 96Editing a QuickBooks Shortcut . 97Upgrading a QuickBooks Shortcut . 99Frequently Asked Questions . 101General OpenAir Integration Manager FAQs . 101Text CSV FAQs . 101QuickBooks FAQs . 102References . 108Terminology . 108Creating a Backup . 108Restoring From Backup . 109Troubleshooting . 110
OverviewOverviewThis guide documents the OpenAir Integration Manager.OpenAir Integration Manager is a quick and efficient way of exporting data from or importing data to anOpenAir account. It facilitates an exchange of data with other software applications such as accountingsystems and payroll programs. OpenAir Integration Manager is an import, export, and data updateapplication specifically designed for use with the OpenAir application.Export from or import to your OpenAir account. You can: Export OpenAir data to and create a comma separated values (CSV) flat file. Then use the CSV file inMicrosoft Excel or Filemaker Pro, Microsoft Access, a payroll system, or an accounting package. Referto OpenAir Records to Export and Import for a list of OpenAir records you can export. Import data, properly formatted, from a CSV file to an OpenAir account. Refer to OpenAir Records toExport and Import for a list of OpenAir records you can import. Configure OpenAir Integration Manager to facilitate an integration with QuickBooks and your OpenAiraccount. This integration includes accounts payable, accounts receivable, cost center, customer,expense item, product, project, service, and user data. Export the information from your OpenAiraccount and into QuickBooks eliminating the need for double data entry. Use features in QuickBooksto manage your financial operations. You can also import QuickBooks Invoices payments and VendorBill payments back into OpenAir. See Exporting OpenAir Records and Importing QuickBooks Records.OpenAir Integration Manager allows you to import and export data from a CSV text file in almost anydelimited format such as a tab, comma, colon, pipe, tilde, semicolon, or other custom delimiters. Thisallows users in both the United States and other countries to select the desired delimiter. Internationalusers can open files automatically in other software applications without having to first convert the file toanother format. Also useful are the multiple date and time formats.Use the custom fields in your account in both file export and import as well as filter sets for data security.Column naming, adding calculated fields, and concatenating multiple fields help you better meet youroperational needs.OpenAir Integration Manager allows you to do bulk updates of records in your OpenAir account.For example, as you make changes to project information, update all affected projects in OpenAir. Iftimesheet or expense report approvers change for one or more projects, update all related OpenAirrecords.Export transactions associated with accounts payable and accounts receivable and use them inQuickBooks for accounting purposes. Enter bills, track expenses, create invoices, receive payments, andkeep track of inventory and revenue. To gain a better understanding of the fields that are available for an item, refer to the OpenAir DataDictionary. It lists all fields in an item or database table, as well as the fields belonging to foreigntables, and provides a description of field contents. For more information, refer toWhile OpenAir Professional Services staff configure OpenAir Integration Manager as part of the businessanalysis and account setup, users can maintain and add to the integration as enhancements becomeavailable.Getting StartedWith an account in OpenAir, you can download the OpenAir Integration Manager software and beginexchanging information between your OpenAir account and a Text CSV file. To exchange information withQuickBooks, you must also load the QuickBooks software.Integration Manager User Guide1
Getting StartedImportant: Only users who have received training on using OpenAir Integration Managershould have access to the integration. Having an understanding of the OpenAir application andhow its database is structured is critical.System Requirements and RecommendationsOpenAir Integration Manager can be installed on the following platforms: Microsoft Windows Server 2012 or later versions Microsoft Windows 8 and 10.Other requirements include: Microsoft .NET Framework Version 3.5 or above. RAM size of at least 1 GB. For large integration jobs, we recommend 2 GBNote: While it is not necessary to run OpenAir Integration Manager on a dedicated server, otheroperations running in parallel might hinder the performance of OpenAir Integration Manager.Therefore, we recommend that you run shortcuts in time slots separate from other CPU-intensiveoperations to maximize the performance of OpenAir Integration Manager.Step 1: Download OpenAir Integration ManagerProfessional Services provides you with a link for downloading OpenAir Integration Manager. Werecommend that customers do not use OpenAir Integration Manager until after they have participated inrelated training. When ready to download, the wizard guides you through the installation process. Referto Installing OpenAir Integration Manager.Step 2: Grant Full User Access Privileges to Users Who RunOpenAir Integration ManagerIn Windows 8 and 10, non-administrator user access to files and folders is restricted. An error can occurwhen a user does not have full access privileges. Ensure users who run OpenAir Integration Manager cancreate, modify, and delete files in the OpenAir Integration Manager installation folder. To do this, go to C:\Program Files(x86)\OpenAir\IntegrationManager.Important: OpenAir Integration Manager does not support a multi-user setup. IntegrationManager and Integration Manager shortcuts should be created and launched using the samesingle Windows account as specified above. Running Integration Manager from different Windowsuser accounts can lead to inconsistent application behavior.Step 3: Determine How You are Going to Use OpenAirIntegration ManagerThere are a number of ways you can use OpenAir Integration Manager. Each affects how you set up youraccounts and exchange information. You can export from your OpenAir account to QuickBooks. Or, youIntegration Manager User Guide2
Getting Startedcan export from OpenAir to a text CSV file. You can also import from a CSV file to your OpenAir account.Refer to the following: Using Text CSV Files Using QuickBooksNote: Before you can use OpenAir Integration Manager with QuickBooks, you must set upa company in the QuickBooks application. Both applications guide you through the process ofsetting up a company.OpenAir Integration ManagerWhen you install OpenAir Integration Manager, the default setting is to install an icon on the desktopof the PC that is holding the OpenAir Integration Manager. At your discretion, you can install theOpenAir Integration Manager icon in another location. If you click on theicon, you launch the OpenAirIntegration Manager window.It contains the menu and submenu selections shown above. The selections available to you depend onthe operation you are performing. The following examples appear when you choose Select System Text CSV File. Other options appear when you choose Select System QuickBooks. Each is described asfollows.File — When you select the File menu, the options available depend on the system you select under theSelect System menu. While Create Backup and Restore From Backup are available for each system, CreateShortcut, Open Shortcut, and Update Shortcut are only available when you select either Text CSV File orQuickBooks. Refer to the following file menu selections.Integration Manager User Guide3
OpenAir Integration ManagerOptions — When you select the Options menu, the OA Account Settings and Logging options areavailable. If you are using OpenAir Integration Manager 6.6 or later version, the CSV Encoding option isavailable. There are two options: ANSI — ANSI (codepage 1252, Western Europe) is the default character encoding scheme forCSV imports and exports to ensure backward compatibility with existing infrastructures. OpenAirIntegration Manager 6.5.3 and earlier versions use ANSI encoding exclusively. UTF-8 — OpenAir Integration Manager 6.6 or later version supports UTF-8 character encoding forCSV imports and exports.Important: Review the following guidelines: The files you provide for import must be encoded using the character encoding schemeselected in OpenAir Integration Manager. For example, if CSV Encoding is set to UTF-8, CSVfiles you provide for import must be UTF-8 encoded — If the CSV files is not UTF-8 encoded,data may be corrupted during import. Existing shortcuts are not updated automatically when you change the CSV encodingoption. Make sure you edit each CSV import and export shortcut where applicable. The additional options are available depending on the selection you make under the Select Systemmenu and items you select to export or import. If you select an item from a Text CSV File to import to OpenAir, i.e., it displays blue highlighting,Field Mapping and Formatting are available. If you select an OpenAir item to export to a Text CSV File, i.e., it displays blue highlighting, FieldMapping, Filtering, and Formatting are available. Ιf you select QuickBooks, the QuickBooks Settings option is available. If you highlight AP Update orAR Update for export, the Field Mapping option is also available. Field Mapping is not available forother items.Integration Manager User Guide4
OpenAir Integration ManagerSelect System — A check mark displays next to the system you select and the items displayed underOpenAir and the Destination/Source or company name reflect the system you select.Help — When you select the Help menu, you can choose one of the following: User Guide (pdf) - displays the most recent version of the OpenAir Integration Manager User Guide. Contents (Release notes) — displays the most recent readme.txt file. OpenAir Web Site — opens https://www.openair.com in your default Web browser window. Display Log — displays the OpenAir Integration Manager activity log in your default text editor. It listsactions on specific dates and times. If you are having problems, you can send a copy of this file toOpenAir Support. See Troubleshooting. About OpenAir Integration Manager — displays version information.Integration Manager User Guide5
OpenAir Integration ManagerNote: If you select an item to import or export and right-click on the highlighted row,available items from the options menu also display. They include the option to Create Shortcutas well as Field Mapping, Filtering, and Formatting when applicable.OpenAir Account SettingsBefore you can use OpenAir Integration Manager and exchange information between OpenAir and textCSV files or QuickBooks, you need to log into your OpenAir account to enter your account credentials.To enter your OpenAir account credentials:1. Click theicon to open the OpenAir Integration Manager.2. Click Options OA Account Settings.The following window appears.3. Enter your Company ID, User ID, and Password.If you select the check box to Remember Password, you do not have to enter it again.4. Server — Enter the URL for your OpenAir Account.Integration Manager User Guide6
OpenAir Account SettingsNote: The server URL includes the domain for your OpenAir account account-domain . Itis the first part of the URL visible in the address bar of your browser after you log into theOpenAir web application.https:// account-domain The URL must start with https:// The domain name contains a unique account identifier company-id . The accountidentifier is typically based on your OpenAir Company ID. The domain name depends on the account type: Production account-specific domain: company-id .app.openair.com Sandbox account-specific domain: company-id .app.sandbox.openair.com Demo account-specific domain: company-id .app.demo.openair.com5. Click OK. In most cases, leave Account ID blank.Note: By clicking the OK button, you understand and agree that the use of Oracle'sapplication is subject to the Oracle.com Terms of Use. Additional details regarding Oracle’scollection and use of your personal information, including information about access,retention, rectification, deletion, security, cross-border transfers and other topics, isavailable in the Oracle Privacy Policy.You are connected to your OpenAir account. Any operations you perform with OpenAir IntegrationManager will now take place using these account credentials. Also note that any updates youperform to records in your OpenAir account will display this user ID in the audit trail recorddocumenting these changes.Integration Manager User Guide7
InstallationInstallationThis section includes installation information on the following: Installing OpenAir Integration Manager Installing a New Version Uninstalling OpenAir Integration ManagerInstalling OpenAir Integration ManagerAfter you have completed OpenAir training for OpenAir Integration Manager, you can download it andinstall it.Note: Microsoft .NET framework 3.5 or above is required to run OpenAir Integration Manager6.3. If it is not installed on your system, download it from work and install it before installing OpenAir Integration Manager.To download and install OpenAir Integration Manager:1. Click on the link provided by Professional Services.2. Save and run the OpenAir Integration Manager Setup application.The “OpenAir Integration Manager Setup Wizard” appears.3. Click Next and accept the License Agreement to continue.4. Click Next and set the location to install OpenAir Integration Manager.5. Click Next and select if to create a desktop icon.6. Click Next and review your installation settings.7. Click Install.The installation progress is displayed and then the README.TXT appears.Integration Manager User Guide8
Installing OpenAir Integration Manager8. Review the README and then click Next.The “Completing the OpenAir Integration Manager Setup Wizard” appears.9. Click Finish.Installing a New VersionBe aware that when you download a new version of OpenAir Integration Manager, it affects all shortcutsyou have installed on your machine. As with all business-critical software upgrades, you should exerciseappropriate responsibility. If you have created shortcuts for business-critical processes such asaccounting system integrations, you
Configure OpenAir Integration Manager to facilitate an integration with QuickBooks and your OpenAir account. This integration includes accounts payable, accounts receivable, cost center, customer, expense item, product, project, service, and user data. Export the information from your OpenAir
