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 |