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, Overdue) |
| 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 details for shipping and delivery (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 |
| InventoryByWarehouseLot | Contains the inventory quantities for each product, warehouse, and Lot/Serial |
| 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 | Includes reorder/purchasing specific data in addition to the ProductWarehouseRestock information |
| ProductWarehouseRestock | Includes all products and indicates when a restock is suggested, either by reordering or assembly. A suggested restock quantity in the product's stocking unit is calculated if a restock is suggested |
| 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 | Includes the shipment, package, and detail 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 (Status=1) 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 |
InventoryByWarehouseLot
Contains the inventory quantities for each product, warehouse, and Lot/Serial.
| Field | Description |
|---|---|
| Count | The number of posted transactions for this product, warehouse, lot/serial combination. |
Invoice
Invoice header information (Invoice number, dates, terms, bill to info, ship-to info, etc.).
| Field | Description |
|---|---|
| DeliveryMiles | This field is populated from the Distance field of Create Invoice window. |
| InvoiceLink | The URL link generated by QBMS (QuickBooks Merchant Services) for customers to pay their invoices online. Only available for Acctivate companies connected to QuickBooks Online companies integrated with QBMS. The Invoice Link will only be generated if the invoice is created in Acctivate and contains a valid contact email address. |
| LastShipmentDate | The shipment date from last shipment associated with the invoice's related sales order. If the Shipped Date in the Create Invoice window is entered then it will be considered along side the order's shipments. |
| NumberOfPackages | This field is populated from the Packages field of Create Invoice window. |
| PackageWeight | This field is populated from the weight field of the Create Invoice window. |
InvoiceDetail
Invoice details (product ID, warehouse, quantity ordered/ship, pricing, etc.).
| Field | Description |
|---|---|
| DisplayQtyShipped | The display quantity that has been invoiced. For example, an invoice may be created for one Master Case (a Master Case is made up of two Cases, the stocking unit) totaling 30 pounds, priced at $5.00/lb. In this example, 1 is the DisplayQtyShipped. |
| QtyInvoiced | The price quantity. For example, an invoice may be created for one Master Case (a Master Case is made up of two Cases, the stocking unit) totaling 30 pounds, priced at $5.00/lb. In this example, 30 is the QtyInvoiced. |
| QtyShipped | The stocking unit quantity that has been invoiced. For example, an invoice may be created for one Master Case (a Master Case is made up of two Cases, the stocking unit) totaling 30 pounds, priced at $5.00/lb. In this example, 2 (the quantity of stocking units invoiced) is the DisplayQtyShipped. |
INVTransaction
Transactions within a session listed in INVRegister (transaction header information).
| Field | Description |
|---|---|
| GLOffsetAccount | The offsetting account for used for a posted transaction. For an unposted landed cost, this will store the Accounts Payable account selected when more than one A/P account exist in an Acctivate company and the landed cost is not created from the Enter Purchase Invoice window. |
| GUIDOrderDetail | Only applies to Assembly transactions created from a sales order. The GUIDOrderDetail is the unique identifier for the related order detail line. |
| OrderNumber | Only applies to Assembly transactions created from a sales order. The order number of the related sales order. |
INVTransactionDetail
Details of transaction listed in INVTransaction.
| Field | Description |
|---|---|
| SubstituteForProductID | If the assembly product is a Custom or Process assembly, you can substitute components which have a valid substitution. The original product which has been substituted will appear in this field. |
LotSerialInventory
Detailed listing of Lot and Serial numbers.
| Field | Description |
|---|---|
| Location | The location from the last transaction that added this lot/serial number's quantity. Transactions include: Receipts, Assemblies, Counts, Adjustments, Components (added from negative assembly), in-bound transfers, and credit memos. |
| PONumber | If the last transaction that added quantity for this lot/serial number was an inventory receipt from a purchase order, the PO number will display here. See TransactionType for more information. |
| TransactionDate | The transaction date from the last transaction that added this lot/serial number's quantity. Transactions include: Receipts, Assemblies, Counts, Adjustments, Components (added from negative assembly), credit memos, Issues, in-bound transfers, and Balance Adjustments; intra-warehouse transfers (moving from one location to another in the same warehouse) are excluded. |
| TransactionType | The type of the last transaction that added this lot/serial number's quantity. Transactions include: Receipts, Assemblies, Counts, Adjustments, Components (added from negative assembly), credit memos, Issues, in-bound transfers, and Balance Adjustments; intra-warehouse transfers (moving from one location to another in the same warehouse) are excluded. |
LotSerialLocationSummary
Current inventory totals for each lot/serial number per warehouse location (Multiple Location Control).
| Field | Description |
|---|---|
| Available | The availability for lot/serial locations is calculated by getting the quantity on hand, subtracting the quantity on open order lot/serial lines are not on an open picklist, subtracting quantities on open picklists, subtracting quantities on unposted assemblies, and subtracting quantities on unposted transfers. |
OrderDetail
Order line details (product ID, warehouse, quantity ordered/ship, pricing, etc.).
| Field | Description |
|---|---|
| DisplayQtyShipped | The display unit quantity that has been invoiced. For example, an invoice may be created for one Master Case (a Master Case is made up of two Cases, the stocking unit) totaling 30 pounds, priced at $5.00/lb. In this example, 1 is the DisplayQtyShipped. |
| DisplayQtyInvoiced | The total price quantity. For example, an order may be created for two cases totaling 30 pounds, priced at $5.00/lb. In this example, 30 is the DisplayQtyInvoiced. Corresponds to the Pr Qty field on the order line. |
| QtyInvoiced | The price quantity per stocking unit that will be included on the next invoice. For example, an order may be created for two cases totaling 30 pounds, priced at $5.00/lb. In this example, 15 (the price qty per stocking unit) is the QtyInvoiced. Corresponds to the weight or length field on the order line. |
| QtyShipped | The stocking unit quantity that has been invoiced. For example, an invoice may be created for one Master Case (a Master Case is made up of two Cases, the stocking unit) totaling 30 pounds, priced at $5.00/lb. In this example, 2 (the quantity of stocking units invoiced) is the QtyShipped. |
Orders
Order header information (Order number, dates, terms, bill to info, ship-to info, etc.).
| Field | Description |
|---|---|
| DeliveryMiles | This field is used to temporarily store the delivery miles from the Distance field of Create Invoice window so that it can be copied to Invoice.DeliveryMiles. Once the invoice is successfully created, this field is cleared. |
| LastShipmentDate | The shipment date from last shipment associated with this sales order. Shipments created in the Package Shipment window must have their status set to Shipped to be considered. |
| NumberOfPackages | This field is used to temporarily store the package count from the Packages field of Create Invoice window so that it can be copied to Invoice.NumberOfPackages. Once the invoice is successfully created, this field is cleared. |
| PackageWeight | This field is used to temporarily store the weight from the Create Invoice window so that it can be copied to Invoice.PackageWeight. Once the invoice is successfully created, this field is cleared. If a single package shipment is created from the Create Invoice window this weight is also copied to Shipment.ShipmentGrossWeight and ShipmenPack.Weight |
| ScheduledWeight | The sum weight for the quantity scheduled on the order. The weight for each line is calculated as (OrderDetail.QtyScheduled * OrderDetail.Weight), then summarized for the entire order. |
Picklist
Picklist header information (Picklist number, orders included, warehouse, status, etc.).
| Field | Description |
|---|---|
| PickedBy | The user who changed the Picklist to Picked (PicklistStatus=C) in the desktop or mobile version of Acctivate. However, this field will not update if a Picklist is changed from an Invoiced or Canceled status to a Picked Status. |
| PickedDated | The date-time a Picklist was changed to Picked (PicklistStatus=C) in the desktop or mobile version of Acctivate. However, this field will not update if a Picklist is changed from an Invoiced or Canceled status to a Picked Status. |
PODetailSimple
A simplified version of PODetail.
| Field | Description |
|---|---|
| IsReceivable | Returns a 1 or 0 value.
|
| QuantityInvoicedOutstanding | The quantity ordered minus the quantity invoiced. |
| QuantityOutstanding | For receivable PO lines (PODetailSimple.IsReceivable = 1), this is calculated as the ordered qty minus the received qty. For non-receivable lines (PODetailSimple.IsReceivable = 0), this is calculated as the ordered qty minus the invoiced qty. |
| QuantityReceivedOutstanding | The quantity ordered minus the quantity received. |
POManagementSummary
The header level purchase order data displayed in the Purchase Order Manager window.
| Field | Description |
|---|---|
| PercentInvoiced | The total invoiced quantity divided by the total ordered qty. |
| PrecentReceived | For receivable products (see PODetailSimple.IsReceivable), the percent received is calculated as ReceivedQty / ReceivableOrderedQty) * 100. Otherwise, if the product is not receivable, then the percent received will show as 100 (percent). |
| ReceivableOrderedQty | The sum of the receivable line's ordered quantity. See PODetailSimple.IsReceivable. |
| ReceivedQty | The sum of all the purchase order line's received quantity. |
POOnOrder
Summarizes purchase orders quantities for products (ordered, outstanding, received).
| Field | Description |
|---|---|
| QuantityOnOrder | If a product is not receivable (PODetailSimple.IsReceivable = 0), then this will return 0. If a product is receivable (PODetailSimple.IsReceivable = 1), then this will return PODetail.QuantityReceivedOutstanding |
| QuantityOnReturn | If a product is not receivable (PODetailSimple.IsReceivable = 0), then this will return 0. If a product is receivable (PODetailSimple.IsReceivable = 1), then this will return PODetail.QuantityReceivedOutstanding |
Product
Product information (Description, Alternate ID, UPC, weight, etc.)
| Field | Description |
|---|---|
| AltUnitsPerPalletLayer | Packaging unit metrics can be assigned in the Edit Product window if a product is assigned a Packaging unit that is different than the stocking unit. This field stores the Package unit per pallet entered on the the Package unit metrics tab found in the Specs tab of the Edit Product window. |
| PalletLayers | Packaging unit metrics can be assigned in the Edit Product window if a product is assigned a Packaging unit that is different than the stocking unit. This field stores the Package unit pallet layers entered on the the Package unit metrics tab found in the Specs tab of the Edit Product window. |
ProductAvailabilityByTemplate
Products that are marked as Available on Web will have their availability included for each web store template.
| Field | Description |
|---|---|
| LastINVUpdatedDate | The most recent updated date from unposted assembly and transfer inventory sessions. |
| LastModifiedDate | The most recent of LastTransactionDate, LastVoidedDate, and LastINVUpdatedDate. This date is used to consider which products should be included in the web store inventory export. For more information, refer to the Integration page for your web store. |
| 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. |
ProductComponent
The relationship of components and parent products
| Field | Description |
|---|---|
| ComponentGUIDProduct | The GUIDProduct of the Component. |
| GUIDComponentWarehouse | The GUIDWarehouse for the component. |
| GUIDProduct | The GUIDProduct of the parent product (Kit/Assembly/Assortment product). |
| GUIDProductComponent | The GUID used to identify a specific parent and component relationship record in ProductComponent. |
ProductKitSummary
A summary of the number of kits that could be made based on component availability.
| Field | Description |
|---|---|
| LastINVUpdatedDate | The most recent updated date from unposted assembly and transfer inventory sessions. |
| LastTransactiondate | The most recent date of all sales order updated dates or posted inventory transaction dates for the kit or its components. |
| 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
Includes the unit factor relationship between a product's stocking unit and its primary sales, purchasing, and packaging units.
| Field | Description |
|---|---|
| PackageBasis | The factor basis description. |
| PackageFactorBasis | The factor basis code. |
| PackageUnit | The default package unit for this product. |
| PackageUnitFactor | The qty of stocking units in the default package unit. |
| PurchaseBasis | The factor basis description. |
| PurchaseFactorBasis | The factor basis code. |
| PurchaseUnit | The default purchase unit for this product. |
| PurchaseUnitFactor | The qty of stocking units in the default purchase unit. |
| SalesBasis | The factor basis description. |
| SalesFactorBasis | The factor basis code. |
| SalesUnit | The default sales unit for this product. |
| SalesUnitFactor | The qty of stocking units in the default sales unit. |
ProductWarehouseReorder
Includes reorder/purchasing specific data in addition to the ProductWarehouseRestock information. Fields in this view are used to set defaults and display user selections in the Create Reorders window.
| Field | Description |
|---|---|
| PreferredPrice | The price that is selected by default in the Create Reorders window. The hierarchy used is:
|
| PreferredUnit | The unit that is selected by default in the Create Reorders window. The hierarchy used is:
|
| Reorder | Indicates the product has been loaded into the Create Reorders window for reordering |
| ReorderAmount | The total reorder amount for the product/warehouse combination in the Create Reorders window |
| ReorderCost | The reorder cost entered in the Create Reorders window |
| ReorderIncludeInPO | Indicates if the record is checked-off in the Create Reorders window |
| ReorderPODisplayQty | The reorder quantity in the unit selected in the PO Unit field of the Create Reorders window |
| ReorderPOQty | The reorder quantity in stocking units. For example, if the SuggRestockQty is 11 ea, but the ReorderPODisplayQty is 1 Dz, the ReorderPOQty would be 12 Ea since 1 Dz = 12 Ea. |
| ReorderQty | The minimum order quantity from the product warehouse |
| ReorderUnit | The reorder unit selected in the Create Reorders window |
| ReorderVendorID | The vendor ID of the vendor selected for reorder in the Create Reorders window |
| ReorderVendorName | The vendor name of the vendor selected for reorder in the Create Reorders window |
| SuggPODisplayQty | The SuggestedRestockQty from the ProductWarehouseRestock view in the preferred vendor's unit. For example, if the SuggRestockQty is 11 ea, but the vendor's preferred unit is "Dz", the SuggPOQty would be 1 Dz. |
| SuggPOQty | The SuggestedRestockQty from the ProductWarehouseRestock view in stocking units based on the preferred vendor's unit. For example, if the SuggRestockQty is 11 ea, but the vendor's preferred unit is "Dz", the SuggPOQty would be 12 Ea since 1 Dz = 12 Ea. |
ProductWarehouseRestock
Includes all products and indicates when a restock is suggested, either by reordering or assembly. A suggested restock quantity in the product's stocking unit is calculated if a restock is suggested.
All quantities are in the stocking unit of the product.
| Field | Description |
|---|---|
| NetAvailable | Calculated for inventoried products which are not discontinued. Available quantity plus special order and inbound (assembly and PO) quantities. |
| RestockSuggested | Calculated for inventoried products which are not discontinued. When the ReorderPoint (min stock level) is greater than the NetAvailable, a restock is suggested. |
| SuggRestockQty | When a restock is suggested (RestockSuggested = 1), a suggested restock quantity is calculated. |
ProductWarehouseSummary
Summarizes warehouse information for specific product (Qty on hand, value, reorder, etc.).
| Field | Description |
|---|---|
| Allocated | Sales order scheduled qty + sales order B/O qty - unposted component qty |
| Available | Availability is calculated as: QtyOnHand -QtyScheduled -QtyBackordered -QtySpecialOrder -QtyReserved +UnpostedAssemblyQuantity -UnpostedTransferQuantity -QuantityOnReturn +QuantityOnPO (if the Inventory Configuration Option Included quantities on POs in available quantity is enabled) |
| Build | Products which have been added to the Create Assemblies window will have Build=1, even if the Product's row isn't checked-off in the Create Assemblies window |
| BuildChecked | Products in the Create Assemblies window which have their row checked-off for assembly will have BuildChecked=1 |
| BuildQty | Build qty from Create Assemblies window |
| LastTransactiondate | The most recent date of all sales order updated dates or posted inventory transaction dates for the product, in this warehouse. |
| PrimaryLocationStockingLevel | Primary Stocking location level |
| QtyBackordered | The quantity backordered on sales orders |
| QtyOrdered | The total ordered qty for open sales order lines, excluding order lines in a Booked order status |
| QtyReserved | Warehouse reserved qty |
| QtyRequired | QtyOrdered - qty shipped, excluding order lines in a Booked order status |
| QtyScheduled | The quantity scheduled on sales orders |
| QtySpecialOrder | The quantity backordered on sales order "S" (Special Order) lines |
| QtyToReorder | Min Order Qty |
| QuantityOnOrder | The sum of QuantityOnPO and UnpostedAssemblyQuantity for the product warehouse. This quantity is described as "Inbound Qty" in the Inventory Manager. |
| QuantityOnPO | The receivable outstanding quantity on an Issue standard Purchase Order (i.e., not Special Order or Drop Ship PO) |
| QuantityOnReturn | Vendor PO return qty (expressed as a positive number) |
| Reorder | Products which have been added to the Create Reorders window will have Reorder=1, even if the Product's row isn't checked-off in the Create Reorders window |
| ReorderCost | Unit cost from the Create Reorders window |
| ReorderIncludeInPO | Products in the Create Reorders window which have their row checked-off for reorder will have ReorderIncludeInPO=1 |
| ReorderPoint | Min Stock Qty |
| ReorderQty | Reorder qty from the Create Reorders window |
| ReorderUnit | Purchase Unit selected in the Create Reorders window |
| StockingLevel | Max Stock Qty |
| UnpostedAssemblyQuantity | The assembly quantity on an open/draft Assembly session |
| UnpostedTransferQuantity | The transfer quantity on an open/draft Transfer session |
TaxCode
The Tax Codes from QuickBooks.
| Field | Description |
|---|---|
| PurchaseRate | The purchase tax rate will only be displayed in VAT editions when the purchase tax rate is different than the sales tax rate |
| SalesRate | The sales tax rate will only be displayed in VAT editions when the sales tax rate is different than the purchase tax rate |