Database Guide - OpenAir
Database GuideApril 9, 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 ContentsIntroduction . 1Diagrams . 3Accounting Period . 3Agreement . 4Approval Process . 5Attachment . 6Authorization . 7Billing . 8Booking . 9Budget . 10Category . 11Cost . 12Customer . 13Custom Field . 15Deal . 16Envelope . 17Estimate . 18Expense Policy . 19Filter . 20Invoice . 21Issue . 22Item . 23Job Rate . 24Leave Accrual . 25Miscellaneous . 26NetSuite Connector . 27News Feed . 28Payment . 29Product . 30Project . 31Project Group . 32Project Pricing . 33Project Task . 34Proposal . 36Purchase Item . 37Purchase Order . 38Reporting . 40Request Item . 41Resource Profile . 42Resource Request . 43Revenue Container . 44Revenue Recognition . 45Role . 46Schedule . 47Script . 48Slip . 49Switch . 50Tag . 51Task . 52Tax . 53Ticket . 54Time . 55User . 57
Workhour . 58Workspace . 59Index . 61
IntroductionIntroductionOverviewThis guide provides a visual representation of the OpenAir database as viewed through the SOAP/XMLAPI.Important: This guide does not describe the physical database, and does not describe thefeatures provided to protect the security, privacy, and integrity of our customers' data.The diagrams in this guide provide an overview and you will need to refer to the OpenAir Data Dictionaryfor the details. Links to the data dictionary are provided as appropriate throughout the guide for thispurpose.Note: To view the OpenAir Data Dictionary, use the following URL: https:// account-domain /database/single user.html. account-domain is the account specific domain for your account. To view the details of a specific table, append a hash symbol # followed by the table name tothe end of the data dictionary URL. For example, use https:// account-domain /database/single user.html#project to view the details of the Project table. You can access the data dictionary from the OpenAir Help Center using the link in thenavigation bar if you have the View Help Center role permission.Note: Diagrams do not show all the fields for large entities (tables) nor do they show all therelationships, refer to the OpenAir Data Dictionary for the details.The guide is organized into three levels:1. Data Areas are used to categorize the diagrams by business area. This is intended to give a highlevel overview of the database.2. Diagrams are Entity Relationship Diagrams (ERD) using Crow's foot notation. A table is providedat the bottom of each diagram with links to the OpenAir Data Dictionary and links to relateddiagrams.Database Guide1
Data AreasNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain withyour account specific domain in the link URLs to access the OpenAir data dictionary. E.g. toview the data dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#project3. The final chapter contains an Index mapping each entity (table) to its diagram and data dictionaryentry.Data AreasCustomersProjectsRevenueAgreement, Category,Customer, Deal, ProposalIssue, Project, Project Group, ProjectPricing, Project TaskRevenue Container, nt, Booking, LeaveAccrual, Resource Profile,Resource Request, ScheduleUserAccounting Period, Billing, Budget, Cost,Envelope, Estimate, Expense Policy, Invoice,Item, Job Rate, Payment, Purchase Item,Purchase Order, Request Item, Slip, Task,Time, WorkhourApproval Process, Authorization,Custom Field, Filter, Miscellaneous,Product, Role Reporting, Script, Switch,Tag, Tax, WorkspaceDatabase Guide2
DiagramsDiagramsAccounting PeriodNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsaccounting period, current periodBilling, Revenue RecognitionDatabase Guide3
AgreementAgreementNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsagreement, agreement to projectBilling, Project, Revenue ContainerDatabase Guide4
Approval ProcessApproval ProcessNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsissue, issue category, issue severity, issue source,issue stage, issue statusAuthorization, Booking, Deal, Invoice, Proposal, PurchaseOrder, Revenue Container, Schedule, TimeDatabase Guide5
AttachmentAttachmentNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsattachment, attachment category, attachment revision, attachment textIssue, Script, Time, WorkspaceDatabase Guide6
AuthorizationAuthorizationNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsauthorization, authorization itemApproval ProcessDatabase Guide7
BillingBillingNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsauto bill, billing action, billing split, project billing rule, project billing transactionAccounting PeriodDatabase Guide8
BookingBookingNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsbooking, booking baseline, booking by day, booking request,booking type, pending booking, project booking gridApproval Process, Deal, Resource Profile,Resource RequestDatabase Guide9
BudgetBudgetNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsbudget, budget activity, budget allocation, budget categoryCustomer, Project, UserDatabase Guide10
CategoryCategoryNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramscategory, category 1, category 2, category 3, category 4, category 5, cc rateCustomerDatabase Guide11
CostCostNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsactual cost, cost category, cost center, cost center used, cost type,loaded costEstimate, Item, Revenue RecognitionDatabase Guide12
CustomerCustomerDatabase Guide13
CustomerNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsbus type, company size, customer, customer location,customerpo, customerpo to projectAgreement, Budget, Category, Expense Policy, Invoice,Issue, Item, Payment, Project Pricing, Project Task,Reporting, Resource Request, TagDatabase Guide14
Custom FieldCustom FieldNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData Dictionarycust field, custom detail, dependent custom field, valuelist custom fieldDatabase GuideRelated Diagrams15
DealDealNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsdeal, deal booking request, deal contact, deal schedule, deal stage, territoryApproval Process, ProposalDatabase Guide16
EnvelopeEnvelopeNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsenvelope, reimbursementApproval Process, Authorization, Customer, Project, Tax, Ticket, UserDatabase Guide17
EstimateEstimateNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsestimate, estimate adjustment, estimate expense, estimate labor, estimate markup,estimate phaseDealDatabase Guide18
Expense PolicyExpense PolicyNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsexpense policy, expense policy itemCustomer, Item, ProjectDatabase Guide19
FilterFilterNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsfilter, filter set, filter userReporting, UserDatabase Guide20
InvoiceInvoiceNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsinvoice, invoice layout, invoice tax ratesApproval ProcessDatabase Guide21
IssueIssueNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData Dictionaryissue, issue category, issue severity, issue source, issue stage, issue statusDatabase GuideRelated Diagrams22
ItemItemNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsalert, item, item to user location, item history, queueCost, Estimate, Expense Policy, ProposalDatabase Guide23
Job RateJob RateNote: Links to the data dictionary provided in this guide use the generic OpenAir domainwww.openair.com. In some instances, you may need to replace the generic domain with youraccount specific domain in the link URLs to access the OpenAir data dictionary. E.g. to view thedata dictionary description of the Project table on Production, use:https:// companyID .app.openair.com/database/single user.html#projectData DictionaryRelated Diagramsjob code, job code history, job code rate by task, job code used, rate card,rate card item, up rateProject PricingDatabase Guide24
Leave AccrualLeave AccrualNote: Links to the data dicti
and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information
Microsoft Excel or Filemaker Pro, Microsoft Access, a payroll system, or an accounting package. Refer to OpenAir Records to Export and Import for a list of OpenAir records you can export. . You can also import QuickBooks Invoices payments and Vendor Bill payments back into OpenAir. See Exporting OpenAir R
Changing your OpenAir Password 4 6. Enter your Single Sign-on details. If your identity is verified successfully using SSO, you will be redirected to OpenAir.
OpenAir Mobile Security 2 process in OpenAir. Approvers can review your timesheets using either the mobile app or the web application, and you can track the status of your submitted timesheets on the app dashboard at any time.
This guide provides a reference for using OpenAir REST API. This section provides a high level overview. Review Getting Started with OpenAir REST API for guidance about setting up and using the API. Other sections in this guide address specific conceptual topics, including Supported Resources, Methods and
Configure IM 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 account and into QuickBooks eli
Introduction 1 Introduction Overview OpenAir is a highly configurable system. Users can set personal settings to tailor the system according to the way the
Database Applications and SQL 12 The DBMS 15 The Database 16 Personal Versus Enterprise-Class Database Systems 18 What Is Microsoft Access? 18 What Is an Enterprise-Class Database System? 19 Database Design 21 Database Design from Existing Data 21 Database Design for New Systems Development 23 Database Redesign 23
Getting Started with Database Classic Cloud Service. About Oracle Database Classic Cloud Service1-1. About Database Classic Cloud Service Database Deployments1-2. Oracle Database Software Release1-3. Oracle Database Software Edition1-3. Oracle Database Type1-4. Computing Power1-5. Database Storage1-5. Automatic Backup Configuration1-6
