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 |