Search Results for

    Show / Hide Table of Contents

    Database Views

    The Acctivate database includes Data Views that are designed for Crystal Reports, Excel queries, and other read-only integrations.

    Common Views for Reporting

    Below are the most common database views used for reporting

    Data View Description
    Customer Primary customer information
    CustomerShip List of customer ship-to information (location ID, address, phone, email, tax category, misc note)
    Invoice Invoice header information (Invoice number, dates, terms, bill to info, ship to info, etc)
    InvoiceDetail Invoice details (product ID, warehouse, quantity ordered/ship, pricing, etc)
    OrderDetail Order line details (product ID, warehouse, quantity ordered/ship, pricing, etc)
    OrderDetailLot Lot/Serial number selected for each product in sales order detail
    Orders Order header information (Order number, dates, terms, bill to info, ship to info, etc)
    PO Purchase Order information (PO number, requested date, ship to information, etc)
    PODetail Purchase Order details (product ID, quantity, price, special instructions, etc)
    Product Product information (Description, Alternate ID, UPC, weight, etc)
    ProductWarehouseSummary Summarizes warehouse information for specific product (Qty on hand, value, reorder, etc)

    All Database Views

    The following list of data views are available for reporting and integrations.

    Data View Descriptions

    Data View Description
    Access Contains the ID, name and other attributes for each Acctivate user
    Branch Acctivate Branch details (address/phone, GL accounts and logo)
    BusinessActivity Business Activity header information (Activity number, type, status, description, etc.)
    BusinessActivityHistory Business Activity log history
    BusinessActivityRelatedItems The Related Items linked to a Business Activity
    BusinessActivityTimeAndMaterials The Time and Materials associated with a Business Activity
    Carrier Listing of shipping carriers; e.g., UPS, FedEx, etc.
    CarrierBillingOptions The carrier billing option values configured in the Configuration Manager. Used when importing in shipment information using Shipping Workstation integration
    CarrierInsuranceOptions The carrier insurance option values configured in the Configuration Manager. Used when importing in shipment information using Shipping Workstation integration
    CarrierService Listing of shipping carriers service types; e.g., Next Day Air, Ground
    CarrierVoidOptions The carrier void option values configured in the Configuration Manager. Used when importing in shipment information using Shipping Workstation integration
    Charge Customer statement charges entered in QuickBooks
    Class Distinct list of QuickBooks Classes used for Class Tracking
    ContractType Not used
    CostLayerIssues The quantity and amount removed from a layer due to inventory being removed
    CostLayerReceipts The quantity and amount added to a cost layer from inventory being added
    CostLayers The cost layers for FIFO or LIFO costed products
    Currency This list of currency codes, if multi-currency is enabled in QuickBooks
    CustomDataList The list items from custom lists created in the Custom Fields section of Configuration Manager
    Customer Primary customer information
    CustomerBalance The total balance due for a customer
    CustomerChargeTotal The total amount and count of charges per customer
    CustomerContact Customer contact information
    CustomerContract Customer service contract information
    CustomerCreditStatus Current credit status for customers (i.e., Credit Hold, Over Limit, etc)
    CustomerEmailAddressList Consolidates customer billing, shipping, and contact email addresses
    CustomerExport The data that can be included in an export when using the Customer Export utility
    CustomerFullName Full, concatenated customer name. E.g., Customer:Job Name
    CustomerInfo Additional customer header level information including Branch and reference fields
    CustomerInvoiceTotal The total number of invoices and invoice amounts for a customer
    CustomerMarketingListXref Legacy. Use MarketingListXRef instead
    CustomerPayment Payments made by a customer
    CustomerPaymentAppliedTotal The total amount and number of customer payments and credits applied to invoices
    CustomerPaymentChargeTotal The total amount and number of customer payments applied to charges
    CustomerPaymentInvoice Details of customer payments applied to invoices
    CustomerPaymentInvoiceTotal The total amount and number of customer payments applied to invoices
    CustomerPaymentTotal The total amount and number of payments made by a customer
    CustomerPaymentView Legacy. Use CustomerPayment instead
    CustomerProduct Cross reference of customers sales history by product
    CustomerShip List of customer ship-to information (location ID, address, phone, email, tax category, misc note)
    CustomerShipToInfo Customer Ship to Shipping/Delivery details (ship via, route, mapping, shipping defaults)
    CustomerShipView Legacy. Use CustomerShip instead
    CustomerStatement Consolidated view of all customer A/R transactions
    CustomerTypeInfo Contains the list of Customer Types
    CustomerView Legacy. Use Customer instead
    CustomField The list of custom fields configured in the Configuration Manager
    Document Note and Email attachments
    EDIAdditionalInformation Additional Information on a specific EDI transaction (e.g. Department Number)
    EDIOrderDetailEx Additional Information from the EDI Purchase Order for each sales order line
    EDIOrdersEx Additional Information from the EDI Purchase Order for each sales order
    Employee Employee Name list (includes job & payroll related information from CYMA version)
    FIFOCostLayers Detail list of cost layers for FIFO costed products
    FlattenedKitComponents Includes the Kit product and it's components in the same column. Parents and components are distinguished by a ComponentLevel value
    GLAccount GL Account number and description from QuickBooks
    GLAccountSegment Unused
    ImpExpDocumentColumn Import column names and their destination fields
    InventoryJournal Header information for journal entry which posts to QuickBooks
    InventoryJournalDetail Detail information connected to InventoryJournal which posts to QuickBooks
    InventoryJournalDetail_NoAccounts Same as InventoryJournalDetail, but excludes the user friendly account names
    InventoryJournalSummary Inventory journals posted to QuickBooks
    INVLotSerialTrace Header information for a Track and Trace session
    INVLotSerialTraceDetail Details of a Track and Trace session
    Invoice Invoice header information (Invoice number, dates, terms, bill to info, ship to info, etc)
    InvoiceAmtSummary Sum of the invoice line amounts
    InvoiceDetail Invoice details (product ID, warehouse, quantity ordered/ship, pricing, etc)
    InvoicesAndCharges Consolidated view of all customer invoices and statement charges
    INVRegister Session information for all inventory types, i.e., Adjustments, Issues, Receipts, Sales, Balance Adjustments, etc.
    INVTransaction Transactions within a session listed in INVRegister (transaction header information)
    INVTransactionDetail Details of transaction listed in INVTransaction
    INVTransactionDetailOnHandSummary Sum of the on hand quantity and value for a product and warehouse. Includes the count of posted transactions that make up the on hand totals.
    INVTransactionDetailSummary Sum of the quantity and amounts for a product across all posted transactions
    Issue Legacy. Use BusinessActivity instead
    IssueCode Contains the information for the Business Activity Codes list
    IssueLog Log of Business Activity status changes
    IssuePriorityCode Contains the information for the Business Activity Priority Codes list
    IssueResolutionCode Contains the information for the Business Activity Resolution Codes list
    IssueSchedulingClass Contains the information for the Business Activity Scheduling Class list
    IssueStatusCode Contains the information for the Business Activity Status Codes list
    IssueType Contains the information for the Business Activity Type list
    IssueTypeCodeXref The relationship between Activity Codes and Activity Types
    IssueTypePriorityXref The relationship between Activity Priority codes and Activity Types
    IssueTypeResolutionXref The relationship between Activity Resolution codes and Activity Types
    IssueTypeSchedulingClassXref The relationship between Activity Scheduling Classes and Activity Types
    IssueTypeStatusXref The relationship between Activity Statuses and Activity Types
    IssueXref Legacy. Use BusinessActivityRelatedItem instead.
    Journal Acctivate Journal entries created in QuickBooks
    JournalLine The Credit and Debit details of a journal created by Acctivate
    LIFOCostLayers Cost layers for LIFO costed products
    LocationSummary Current inventory totals per warehouse location (Multiple Location Control)
    LocationSummaryNoAvailability Current inventory totals per warehouse location without the Available quantity. Includes OnHand quantity (Multiple Location Control)
    LostBusiness List of reasons that a sales order or quote would be canceled
    LotSerialDefaultLocation One record per product/warehouse/Lot combination. The default location for a lot/serial # is the primary location from the ProductWarehouse record unless that location has no qty on hand, then the default location is the location from the last transaction for the lot/serial #.
    LotSerialInventory Detailed listing of Lot and Serial numbers
    LotSerialInventoryIssues The quantity and amount removed from a lot/serial due to inventory being removed
    LotSerialInventoryReceipts The quantity and amount added to a lot/serial from inventory being added
    LotSerialInventoryWithoutLastReceipt Same as LotSerialInventory, but with less inventory receipt details to improve performance
    LotSerialLocation A list of every lot/serial and location combination used in posted transactions
    LotSerialLocationSummary Current inventory totals for each lot/serial number per warehouse location (Multiple Location Control)
    LotSerialLocationSummaryNoAvailability Same as LotSerialLocationSummary, but does not include availability, only OnHand, for performance reasons
    LotSerialSummary Copy of LotSerialInventory
    MarketingCode Contains the list of Marketing Codes created in the Configuration Manager
    MarketingListCode Contains the list of Marketing Lists created in the Configuration Manager
    MarketingListXref The link between a customer and the Marketing Lists they belong to
    Messages Email messages sent from Acctivate
    NoteCategory Not used
    Notes Reminders/notes entered throughout Acctivate
    NoteStatus Not used
    NotesView Legacy, use Notes instead
    Numbers The number scheme for documents and transactions in Acctivate
    OrderDetail Order line details (product ID, warehouse, quantity ordered/ship, pricing, etc)
    OrderDetailLot Lot/Serial number selected for each product in sales order detail
    OrderDetailSummary Summarizes sales order product quantities (ordered, shipped, scheduled, backordered, etc)
    OrderDetailTopLevelComponentQuantity If a kit has sub-kits, this data view calculate the quantity of sub-kit components that make up one of the top most kit level.
    OrderManagementSummary The header level order data displayed in the Order Manager window
    OrderPackages Contains shipping information for sales orders
    OrderPendingAssemblies The assembly quantity needed for an order. This information is found in the Assemblies tab of the Alerts window
    Orders Order header information (Order number, dates, terms, bill to info, ship to info, etc)
    OrdersAvailableToShip Contains order information for open orders. Provided to an integrated shipping platform.
    OrderWorkFlowStatus List of workflow statuses available for orders
    PaymentMethod List of payment methods from QuickBooks (check, cash, etc)
    Picklist Header information for a Picklist
    PicklistDetail The details of a Picklist
    PicklistDetailLot the Lot/Serial details for a Picklist line
    PicklistOrder The relationship between a Picklist and it's order(s)
    PO Purchase Order information (PO number, requested date, ship to information, etc)
    PODetail Purchase Order details (product ID, quantity, price, special instructions, etc)
    PODetailInvoiced The invoiced quantity and amount for a PO detail line. Includes the count of purchase invoices that make up the received totals.
    PODetailInvoiceSummary Summary of PO invoice information per PO Detail line
    PODetailReceiptSummary Summary of PO receipt information per PO Detail line
    PODetailReceived The received quantity and amount for a PO detail line. Includes the count of posted receipts that make up the received totals.
    PODetailSimple A simplified version of PODetail
    POInvoice Vendor invoice entered against purchase order (Invoice number, Invoice date, PO info, etc)
    POInvoiceDetail Vendor invoice details (amount approved, other amounts)
    POManagementSummary  The header level purchase order data displayed in the Purchase Order Manager window
    POOnOrder Summarizes purchase orders quantities for products (ordered, outstanding, received)
    PriceCode Contains the list of Price Codes setup in Configuration Manager
    PrinterAssignment The printer assignments for specific reports. Printer assignments are configured in the Report section of the Configuration Manager
    Product Product information (Description, Alternate ID, UPC, weight, etc)
    ProductAlt All the UPC and Alternate Product ID's and their descriptions that are configured for a product
    ProductAltID The primary UPC and Alternate Product ID's and its description for a product.
    ProductAltUnit List of related alternate units for each product, including Standard and Product-Specific units
    ProductAnalysisByMonth Product activity (sales and purchases) summarized by month
    ProductAnalysisByWeek Product activity (sales and purchases) summarized by week
    ProductAvailabilityByTemplate Products that are marked as Available on Web will have their availability included for each web store template
    ProductClass The list of Product Classes configured in the Configuration Manager
    ProductComponent The relationship of components and parent products
    ProductControlType The distinct list of product control types
    ProductCostMethod The distinct list of product cost methods
    ProductCustomer List of customer specific Product IDs
    ProductDocumentHistory All sales and purchasing documents per product
    ProductExport The data that can be included in an export when using the Product Export utility
    ProductKitSummary A summary of the number of kits that could be made based on component availability
    ProductOpenOrders Contains all open Purchase Orders and Sales Orders for a product
    ProductPrice Relationship between products and their price codes, customer specific prices and price categories.
    ProductSalesCategory The distinct list of Price Categories setup in the Configuration Manager
    ProductSubstitutions List of products that are configured as substitutes for another product
    ProductSupplier Vendor product information (Vendor name, vendor product id, price, lead time, etc)
    ProductTransactions List of transaction information for a product
    ProductType The distinct list of Product Types configure din the Configuration Manager
    ProductUnit The list of product units and their relationships with other units
    ProductUnitFactor Includes the unit factor relationship between a product's stocking unit and its primary sales, purchasing, and packaging units.
    ProductWarehouse List of warehouses for each product is stocked in
    ProductWarehouseLastVoidedDate The last date a transaction was voided for a product in a specific warehouse
    ProductWarehouseLocation The list of locations a product is stocked in, per warehouse
    ProductWarehouseReorder The list of products meeting the criteria to be reordered. Can be found in the Reorders tab of the Order Manager
    ProductWarehouseSummary Summarizes warehouse information for specific product (Qty on hand, value, reorder, etc)
    ReportDefinition The list of reports and their definitions, including the name of the report and description
    ReportParameters The parameters assigned to each report
    SalespersonInfo Salesperson full name and associated link information to QuickBooks
    Shipment The header level information for a shipment
    ShipmentAssociatedOrderDetails Details the order lines that are associated with each shipment
    ShipmentItem Details of each item in a carton
    ShipmentOrder Link between shipment and sales order
    ShipmentOrderDetail Link between shipment and sales order lines (Pick/Pack)
    ShipmentOrderDetailToPack The quantity of each item that is to be packed into a carton
    ShipmentOrderSummary Summary of cartons and weight per shipment and order
    ShipmentOrderToPack Orders that have been approved or don't have a credit hold
    ShipmentPack Carton / Pack information, including Package ID and Tracking Number
    ShipmentPicklist The link between a picklist and it's associated shipments
    ShipmentPicklistDetail The link between the picklist details and it's shipment
    ShipmentSummary Summary of shipment (one record for each shipment)
    ShipToComments The distinct list of comments that have been setup in the Configuration Manager for use on sales documents
    ShipVia The distinct list of Ship Via's and their associations to carriers/carrier services
    System Company and system level configuration
    SystemOption System level options that have been configured
    TaxCatDefinition The link between a tax category and it's related tax item(s)
    TaxCategory The list of Tax Categories (Active records are tax groups or tax items that do not belong to a group)
    TaxCode The Tax Codes from QuickBooks
    TaxID Sales Tax Items from QuickBooks
    TermsCode The list of Terms Codes from QuickBooks
    UnpostedAssemblySummary Assembly products and their unposted assembly quantity (qty on an ope assembly)
    UnpostedComponentLocationSummary Assembly components and their unposted assembly quantity with location
    UnpostedComponentLotLocationSummary Assembly components and their unposted assembly quantity with location, by lot
    UnpostedComponentLotSummary Assembly components and their unposted lot quantity
    UnpostedComponentSummary Assembly components and their unposted component quantity
    UnpostedTransferLocationSummary The same as UnpostedTransferSummary, but includes the warehouse location as well
    UnpostedTransferLotLocationSummary The same as UnpostedTransferLocationSummary, but includes lot/serial numbers
    UnpostedTransferLotSummary The same as UnpostedTransferLocationSummary, but includes the lot/serial number
    UnpostedTransferSummary The product, quantity on the "From" side of an unposted transfer
    Vendor Contains the Vendor information
    VendorInfo Additional vendor information
    VendorType The distinct list of Vendor Types from QuickBooks
    Warehouse The information for each warehouse configured in Acctivate
    WarehouseLocation Warehouse Location List for Multiple Location Control module
    Year A list of years and their beginning and ending date
    YearMonth A list of years, their months, and the beginning and ending date for each month of that year
    YearWeek A list of years, their weeks, and the beginning and ending date for each week of that year

    ProductAvailabilityByTemplate

    Below are additional descriptions for some of the ProductAvailabilityByTemplate fields.

    Field Description
    LastTransactiondate The most recent date of all sales order updated dates or posted inventory transaction dates for the product.
    LastVoidedDate The most recent date of all canceled sales order updated dates or voided inventory transaction dates for the product.
    LastINVUpdatedDate The most recent updated date from unposted assembly and transfer inventory sessions.
    LastModifiedDate The most recent of LastTransactionDate, LastVoidedDate, and LastINVUpdatedDate.

    ProductKitSummary

    Below are additional descriptions for some of the ProductKitSummary fields.

    Field Description
    LastTransactiondate The most recent date of all sales order updated dates or posted inventory transaction dates for the kit or its components.
    LastINVUpdatedDate The most recent updated date from unposted assembly and transfer inventory sessions.
    LastVoidedDate The most recent date of all canceled sales order updated dates or voided inventory transaction dates for the kit or its components in this warehouse.

    ProductUnitFactor

    Below are additional descriptions for some of the ProductUnitFactor fields.

    Field Description
    SalesUnit The default sales unit for this product.
    SalesUnitFactor The qty of stocking units in the default sales unit.
    SalesFactorBasis The factor basis code.
    SalesBasis The factor basis description.
    PurchaseUnit The default purchase unit for this product.
    PurchaseUnitFactor The qty of stocking units in the default purchase unit.
    PurchaseFactorBasis The factor basis code.
    PurchaseBasis The factor basis description.
    PackageUnit The default package unit for this product.
    PackageUnitFactor The qty of stocking units in the default package unit.
    PackageFactorBasis The factor basis code.
    PackageBasis The factor basis description.

    ProductWarehouseSummary

    Below are additional descriptions for some of the ProductWarehouseSummary fields.

    Field Description
    LastTransactiondate The most recent date of all sales order updated dates or posted inventory transaction dates for the product, in this warehouse.
    QtyReserved Warehouse reserved qty
    PrimaryLocationStockingLevel Primary Stocking location level
    ReorderPoint Min Stock Qty
    StockingLevel Max Stock Qty
    QtyToReorder Min Order Qty
    QuantityOnReturn Vendor PO return qty (expressed as a positive number)
    QtyOrdered The total ordered qty for open sales order lines, excluding order lines in a Booked order status
    Allocated Sales order scheduled qty + sales order B/O qty - unposted component qty
    QtyRequired QtyOrdered - qty shipped, excluding order lines in a Booked order status
    ReorderUnit Purchase Unit selected in the Reorder Wizard
    ReorderCost Unit cost from the Reorder Wizard
    ReorderQty Reorder qty from the Reorder Wizard
    BuildQty Build qty from Assemblies tabs of the Business Alerts
    In This Article
    Back to top © 2023 Alterity, Inc. All rights reserved