Show Zoho Tables

Show Dear Analytics Table Definitions

Related tables

The schema here does not currently show it, but both the Zoho and Postgresql tables have relationships between GUID fields.

Contents

sales_fact

sales_lines_lifecycle

sales_order_inventory_transactions

sales_shipments

dear_sales_header

sales_order_payments

sales_order_lines

dear_customer

dear_customer_addresses

dear_customer_contacts

product_movements

dear_customer_tag

dear_product

dear_product_family

dear_metadata

dear_product_availability

dear_product_tag

dear_supplier

dear_po_header

purchase_order_lines

purchases_fact

purchase_order_payments

purchases_lines_lifecycle

avg_ex_rate

shippit_transactions

table_update_status

Table name: sales_fact

Table description: Based on authorised invoice lines; this table should agree with the data in Xero or QBO

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
source_dearPLAIN
document_modifiedDATE
customer_guidPLAIN
transaction_dateDATE invoice date. A date with no timezone
inv_due_dateDATE Invoice due date, A date with no timezone
product_guidPLAIN
line_descriptionPLAIN
revenue_quantityDECIMAL_NUMBER quantity which affects revenue accounts (invoice or credited amount)
dropship_quantityDECIMAL_NUMBER this is included in revenue quantity, not in addition to it
movement_quantityDECIMAL_NUMBER quantity which affects inventory accounts (shipments or returns)
transaction_typePLAIN
order_referencePLAIN
global_sale_statusPLAIN see Dear API reference
document_referencePLAIN
task_idPLAIN Invoice task id (guid unique to invoice header)
document_currencyPLAIN
unit_before_discountCURRENCY unit price in document currency before discount, , always tax exclusive
unit_netCURRENCY unit price in document currency after discount, always tax exclusive
tax_rulePLAIN inclusive or exclusive on the order, but tax is never included in revenue or unit prices
unit_taxCURRENCY Unit tax (document currency)
warehousePLAIN order header location, not the location of the pick
sales_repPLAIN sales rep, from the order header
unit_cogs_average_costCURRENCY average cost price in base currency at the time of the sales order
unit_cogs_actualCURRENCY actual cost price in base currency. Only available if there is a shipment
document_exrateDECIMAL_NUMBER
revenue_accountPLAIN
cogs_accountPLAIN Not available at present
base_currencyPLAIN
attribute1PLAIN
attribute2PLAIN
attribute3PLAIN
attribute4PLAIN
attribute5PLAIN
attribute6PLAIN
attribute7PLAIN
attribute8PLAIN
attribute9PLAIN
attribute10PLAIN
shipping_company_namePLAIN
shipping_addr1PLAIN
shipping_addr2PLAIN
shipping_postalcodePLAIN
shipping_regionPLAIN
shipping_townPLAIN
shipping_countryPLAIN
year_month_currencyPLAIN
line_revenue_base_currencyCURRENCY Line revenue in base currency, without tax
line_cogs_base_currencyCURRENCY Based on average cost information
line_margin_base_currencyCURRENCY Line margin in base currency, based on average cogs. In future, this may have actual costs if available
line_estimated_invoice_marginCURRENCY Line margin in base currency, based on average cost

Return to top of page

Table name: sales_lines_lifecycle

Table description: Shows the lifecycle status of lines on the sales order: quantity and value picked, shipped, invoiced etc

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
source_dearPLAIN
line_typePLAIN
product_guidPLAIN
skuPLAIN
quoted_qtyDECIMAL_NUMBER
ordered_qtyDECIMAL_NUMBER
shipped_qtyDECIMAL_NUMBER
shipped_with_tracking_qtyDECIMAL_NUMBER
in_fulfilment_qtyDECIMAL_NUMBER The qty is in auth picking but not yet auth shipped
not_yet_processed_qtyDECIMAL_NUMBER Has not entered auth picking
not_yet_shipped_qtyDECIMAL_NUMBER
never_to_be_fulfilled_qtyDECIMAL_NUMBER If a back order is cancelled because the order is marked as Fulfilled
invoiced_qtyDECIMAL_NUMBER
drop_ship_qtyDECIMAL_NUMBER
credited_qtyDECIMAL_NUMBER
returned_qtyDECIMAL_NUMBER Not implemented yet
avg_unit_price_baseCURRENCY
quoted_basecurCURRENCY
ordered_basecurCURRENCY
shipped_basecurCURRENCY valued at order price, not cogs, so that is is comparable
shipped_with_tracking_basecurCURRENCY
in_fulfilment_basecurCURRENCY
not_yet_processed_basecurCURRENCY
not_yet_shipped_basecurCURRENCY
never_to_be_fulfilled_basecurCURRENCY
invoiced_basecurCURRENCY
drop_ship_basecurCURRENCY
actual_cogs_per_unitCURRENCY
avg_cost_per_unitCURRENCY At the time this row was last updated: this nbr can change
credited_basecurCURRENCY
returned_basecurCURRENCY Not implemented yet

Return to top of page

Table name: sales_order_inventory_transactions

Table description: Inventory movements by date and value associated with the sales order

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
source_dearPLAIN
transaction_typePLAIN
transaction_dateDATE Date, No timezone
product_guidPLAIN
accountPLAIN
quantityDECIMAL_NUMBER
signed_revenue_quantityDECIMAL_NUMBER positive means invoice, negative is a credit,
signed_inventory_quantityDECIMAL_NUMBER positive means shipment, negative is a return,
unit_value_baseCURRENCY
rev_unit_value_baseCURRENCY
cogs_unit_value_baseCURRENCY

Return to top of page

Table name: sales_shipments

Table description: Information specifically about shipments, similar to the sales_order_inventory_transactions table, but only for shipments

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
source_dearPLAIN
shipment_dateDATE DateTime, no timezone
product_guidPLAIN
warehousePLAIN
warehouse_guidPLAIN
fulfilment_nbrNUMBER
boxPLAIN
shipment_quantityDECIMAL_NUMBER
cogsCURRENCY
cogs_dateDATE Date field, no timezone
shipped_value_baseCURRENCY
task_idPLAIN
base_currencyPLAIN

Return to top of page

Table name: dear_sales_header

Table description: Sales Header data

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
order_guidPLAIN
global_sale_statusPLAIN SO Header Sale Status, see Available Sale Statues https://dearinventory.docs.apiary.io/#reference/sale
order_statusPLAIN Sale Order status, one of NOT AVAILABLE,DRAFT,AUTHORISED,VOIDED,AUTH_NO_ALLOC,FULFILLED,CLOSED. See Available Order Statuses at https://dearinventory.docs.apiary.io/#reference/sale
combined_picking_statusPLAIN Possible Values are VOIDED, NOT AVAILABLE, PICKED, PICKING , NOT PICKED , PARTIALLY PICKED
combined_packing_statusPLAIN Possible Values are VOIDED, NOT AVAILABLE, PACKED, PACKING, NOT PACKED, PARTIALLY PACKED
combined_shipping_statusPLAIN Possible Values are VOIDED, NOT AVAILABLE, SHIPPED, SHIPPING , NOT SHIPPED , PARTIALLY SHIPPED
fulfillment_statusPLAIN Possible Values are FULFILLED, PARTIALLY FULFILLED, NOT AVAILABLE, NOT FULFILLED, VOIDED
combined_invoice_statusPLAIN Possible Values are VOIDED, DRAFT, AUTHORISED, NOT AVAILABLE, PAID
combined_payment_statusPLAIN NOT REFUNDED, PREPAID, PARTIALLY PAID, UNPAID, PAID, VOIDED
combined_tracking_numbersPLAIN
order_referencePLAIN Sale Order Number
sale_order_dateDATE Date when order was created (no timezone)
document_modifiedDATE
customer_guidPLAIN
sales_repPLAIN
source_channelPLAIN Source of the order
document_currencyPLAIN
document_exrateDECIMAL_NUMBER
base_currencyPLAIN
warehousePLAIN
attribute_setPLAIN
attribute1PLAIN
attribute2PLAIN
attribute3PLAIN
attribute4PLAIN
attribute5PLAIN
attribute6PLAIN
attribute7PLAIN
attribute8PLAIN
attribute9PLAIN
attribute10PLAIN
ship_byDATE Date, no timezone provided
customer_referencePLAIN
quoted_total_ex_tax_baseCURRENCY Includes everything
quoted_total_incl_tax_baseCURRENCY Includes everything
ordered_total_ex_tax_baseCURRENCY Includes everything on the order
ordered_total_incl_tax_baseCURRENCY Includes everything on the order
ordered_stock_ex_tax_baseCURRENCY Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included
ordered_stock_incl_tax_baseCURRENCY Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included
invoiced_total_ex_tax_baseCURRENCY Includes everything on the invoice
invoiced_total_incl_tax_baseCURRENCY Includes everything on the invoice
credited_total_ex_tax_baseCURRENCY Includes everything on the CN
credited_total_incl_tax_baseCURRENCY Includes everything on the CN
paid_total_baseCURRENCY Payments can't be split by item type, so payment will include additional charges and non-inventory items
paid_total_ex_tax_baseCURRENCY Tax is excluded from this total to make it more comparable with the other values, but it is total payment including for non-inventory items and additional charges and including tax
shipped_ex_tax_baseCURRENCY At selling price, not cogs, so it is comparable with the other columns. Drop shipped items are valued as fully shipped
refunded_total_baseCURRENCY
in_fulfilment_ex_tax_baseCURRENCY Covers orders with an authorised pick (or pack) but not yet shipped. Only stock items are included, no additional charges, no non-inventory items
dear_urlURL This URL takes you to the Dear order, as long as you are logged into Dear
backorder_analysis_urlURL This URL takes you a GrowthPath backorder and fulfilment summary of the order
order_memoMULTI_LINE
sale_noteMULTI_LINE
sale_contactPLAIN
sale_contact_phonePLAIN
sale_contact_emailPLAIN
shipping_company_namePLAIN
shipping_addr1PLAIN
shipping_addr2PLAIN
shipping_postalcodePLAIN
shipping_regionPLAIN
shipping_townPLAIN
shipping_countryPLAIN
shipping_notesPLAIN
sale_carrierPLAIN
billing_addr1PLAIN
billing_addr2PLAIN
billing_postalcodePLAIN
billing_regionPLAIN
billing_townPLAIN
billing_countryPLAIN

Return to top of page

Table name: sales_order_payments

Table description: Payments and deposits (prepayments) known to Dear for sales orders

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
order_guidPLAIN
order_referencePLAIN
doc_guidPLAIN GUID of the Task, ie the Invoice or Credit Note
doc_numberPLAIN Document number of the Task, ie the Invoice or Credit Note
payment_guidPLAIN
payment_typePLAIN Prepayment,InvoicePayment,Refund
payment_referencePLAIN
payment_amountCURRENCY
payment_dateDATE No timezone
payment_accountPLAIN
base_currencyPLAIN
payment_currencyPLAIN
payment_exrateDECIMAL_NUMBER

Return to top of page

Table name: sales_order_lines

Table description: Sales order lines, including for draft orders and orders which are not yet invoiced

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
document_exrateDECIMAL_NUMBER From PO Header
document_currencyPLAIN
base_currencyPLAIN
source_dearPLAIN
line_typePLAIN
product_guidPLAIN
descriptionPLAIN
order_quantityDECIMAL_NUMBER
commentPLAIN
backorder_quantityDECIMAL_NUMBER
line_average_costCURRENCY Average cost (doc currency) used by Dear to estimate margin before picking
unit_price_before_discountCURRENCY
unit_price_netCURRENCY
line_totalCURRENCY
line_tax_totalCURRENCY
unit_price_before_discount_base_curCURRENCY
unit_price_net_base_curCURRENCY
unit_actual_cogs_base_curCURRENCY Actual cogs (base cur) based on shipment, averaged across all shipments, if no shipments, then 0
line_total_base_curCURRENCY
line_tax_total_base_curCURRENCY
line_estimated_order_margin_base_curCURRENCY
line_tax_rulePLAIN
dropshipBOOLEAN

Return to top of page

Table name: dear_customer

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
document_modifiedDATE
customer_guidPLAIN
customer_namePLAIN
statusPLAIN
tagsPLAIN
customer_sales_repPLAIN
source_dearPLAIN
attribute_setPLAIN
cust_attribute1PLAIN
cust_attribute2PLAIN
cust_attribute3PLAIN
cust_attribute4PLAIN
cust_attribute5PLAIN
cust_attribute6PLAIN
cust_attribute7PLAIN
cust_attribute8PLAIN
cust_attribute9PLAIN
cust_attribute10PLAIN

Return to top of page

Table name: dear_customer_addresses

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
address_guidPLAIN
customer_guidPLAIN
line1PLAIN
line2PLAIN
cityPLAIN
statePLAIN
postcodePLAIN
countryPLAIN Must be an ISO standard country name
typePLAIN
default_for_typeBOOLEAN

Return to top of page

Table name: dear_customer_contacts

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
contact_guidPLAIN
customer_guidPLAIN
namePLAIN
phonePLAIN
mobile_phonePLAIN
faxPLAIN
emailPLAIN
websitePLAIN
commentPLAIN
defaultBOOLEAN
include_in_emailBOOLEAN

Return to top of page

Table name: product_movements

Table description: All stock movements with quantity and date

Column NameDatatypeDescription
idAUTO_NUMBER
product_guidPLAIN
document_modifiedDATE
source_dearPLAIN
movement_typePLAIN
movement_taskidPLAIN Link to the source documents, such as a PO
movement_dateDATE No timezone
movement_numberPLAIN A document reference, such as a sales order number
movement_statusPLAIN Currently documented in the API but not used by Dear
quantityDECIMAL_NUMBER This is a signed number, > 0 means increase in SOH
locationPLAIN Name of the location, not the ID
batch_snPLAIN
expiry_dateDATE No timezone
from_toPLAIN
amountCURRENCY Base currency value of the transaction

Return to top of page

Table name: dear_customer_tag

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
document_modifiedDATE
customer_guidPLAIN
source_dearPLAIN Link to Dear instance which is the source of the data
tag_valuePLAIN

Return to top of page

Table name: dear_product

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
document_modifiedDATE
source_dearPLAIN
product_guidPLAIN
product_guid_dup1PLAIN
product_guid_dup2PLAIN
product_guid_dup3PLAIN
typePLAIN
skuPLAIN
product_namePLAIN
product_categoryPLAIN
brandPLAIN
average_costCURRENCY
tagsPLAIN
uomPLAIN
statusPLAIN
default_supplier_guidPLAIN
costing_methodPLAIN
default_locationPLAIN
barcodePLAIN
stock_locatorPLAIN
purchase_tax_rulePLAIN
sale_tax_rulePLAIN
weightDECIMAL_NUMBER
weight_unitsPLAIN
attribute_setPLAIN
prod_attribute1PLAIN
prod_attribute2PLAIN
prod_attribute3PLAIN
prod_attribute4PLAIN
prod_attribute5PLAIN
prod_attribute6PLAIN
prod_attribute7PLAIN
prod_attribute8PLAIN
prod_attribute9PLAIN
prod_attribute10PLAIN
price_tier1CURRENCY
price_tier2CURRENCY
price_tier3CURRENCY
price_tier4CURRENCY
price_tier5CURRENCY
price_tier6CURRENCY
price_tier7CURRENCY
price_tier8CURRENCY
price_tier9CURRENCY
price_tier10CURRENCY

Return to top of page

Table name: dear_product_family

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
product_family_guidPLAIN
document_modifiedDATE
source_dearPLAIN
product_guidPLAIN
family_namePLAIN
short_descriptionPLAIN
family_option1_namePLAIN
family_option1_valuePLAIN
family_option2_namePLAIN
family_option2_valuePLAIN
family_option3_namePLAIN
family_option3_valuePLAIN

Return to top of page

Table name: dear_metadata

Table description: Dear Company Name: this table is useful if you have multiple Dear instances in the GrowthPath Analytics Connector

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN Link to Dear instance which is the source of the data
dear_company_namePLAIN
last_updateDATE

Return to top of page

Table name: dear_product_availability

Table description: Product availability is a snapshot. It may potentially be several hours out of date.

Column NameDatatypeDescription
idAUTO_NUMBER
id_product_availabilityPLAIN
source_dearPLAIN
product_guidPLAIN
skuPLAIN
namePLAIN
barcodePLAIN
locationPLAIN
binPLAIN
batchPLAIN
expiry_dateDATE A Date field (no timezone)
onhandDECIMAL_NUMBER
allocatedDECIMAL_NUMBER
hard_allocatedDECIMAL_NUMBER
availableDECIMAL_NUMBER
onorderDECIMAL_NUMBER
stockonhand_base_currencyDECIMAL_NUMBER

Return to top of page

Table name: dear_product_tag

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
document_modifiedDATE
product_guidPLAIN
source_dearPLAIN
tag_valuePLAIN

Return to top of page

Table name: dear_supplier

Table description:

Column NameDatatypeDescription
idAUTO_NUMBER
document_modifiedDATE
supplier_guidPLAIN
supplier_namePLAIN
statusPLAIN
tagsPLAIN
source_dearPLAIN
attribute_setPLAIN
supplier_attribute1PLAIN
supplier_attribute2PLAIN
supplier_attribute3PLAIN
supplier_attribute4PLAIN
supplier_attribute5PLAIN
supplier_attribute6PLAIN
supplier_attribute7PLAIN
supplier_attribute8PLAIN
supplier_attribute9PLAIN
supplier_attribute10PLAIN

Return to top of page

Table name: dear_po_header

Table description: Purchase Order header

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
order_guidPLAIN
drop_ship_sale_guidPLAIN
global_purchase_statusPLAIN PO Header status
combined_receiving_statusPLAIN
combined_invoice_statusPLAIN
combined_payment_statusPLAIN
order_numberPLAIN
order_dateDATE Date field when PO was created, no timezone
order_memoMULTI_LINE
dear_urlURL This URL takes you to the Dear purchase order, as long as you are logged into Dear
po_noteMULTI_LINE
required_by_dateDATE Date field, no timezone
document_modifiedDATE
supplier_guidPLAIN
document_currencyPLAIN
termsPLAIN
contactPLAIN
document_exrateDECIMAL_NUMBER
base_currencyPLAIN
warehousePLAIN
attribute_setPLAIN
attribute1PLAIN
attribute2PLAIN
attribute3PLAIN
attribute4PLAIN
attribute5PLAIN
attribute6PLAIN
attribute7PLAIN
attribute8PLAIN
attribute9PLAIN
attribute10PLAIN
ordered_total_ex_tax_baseCURRENCY
ordered_total_incl_tax_baseCURRENCY
invoiced_total_ex_tax_baseCURRENCY
invoiced_total_incl_tax_baseCURRENCY
credited_total_ex_tax_baseCURRENCY
credited_total_incl_tax_baseCURRENCY
paid_total_baseCURRENCY This includes tax
refunded_total_baseCURRENCY includes tax
received_total_ex_tax_baseCURRENCY Received and Put Away
never_to_be_received_total_ex_tax_baseCURRENCY If PO is marked as received, this is what was cancelled
ordered_total_ex_taxCURRENCY
ordered_total_incl_taxCURRENCY
invoiced_total_ex_taxCURRENCY
invoiced_total_incl_taxCURRENCY
credited_total_ex_taxCURRENCY
credited_total_incl_taxCURRENCY
paid_totalCURRENCY This includes tax
refunded_totalCURRENCY include tax
received_total_ex_taxCURRENCY Received and Put Away
never_to_be_received_total_ex_taxCURRENCY If PO is marked as received, this is what was cancelled
shipping_company_namePLAIN
shipping_addr1PLAIN
shipping_addr2PLAIN
shipping_postalcodePLAIN
shipping_regionPLAIN
shipping_townPLAIN
shipping_countryPLAIN

Return to top of page

Table name: purchase_order_lines

Table description: PO lines includng draft and not yet invoiced POs

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
source_dearPLAIN
line_typePLAIN
commentPLAIN
product_guidPLAIN Link to the product table
descriptionPLAIN
order_quantityDECIMAL_NUMBER
unit_price_before_discountCURRENCY unit price before row discount, document currency
unit_price_netCURRENCY Unit price with tax removed, even if tax inclusive, document currency
unit_taxCURRENCY unit tax, document currency
unit_price_before_discount_base_curCURRENCY unit price before row discount, base currency
unit_price_net_base_curCURRENCY Net unit price in base currency
document_exrateDECIMAL_NUMBER From PO Header
document_currencyPLAIN
base_currencyPLAIN
line_totalCURRENCY Line total, document currency. Includes tax if order is tax inclusive
line_tax_totalCURRENCY Line tax total, document currency
line_total_base_curCURRENCY Line total, base currency. Includes tax if order is tax inclusive
line_tax_total_base_curCURRENCY Line tax total, base currency
line_tax_rulePLAIN

Return to top of page

Table name: purchases_fact

Table description: based on authorised PO invoice lines

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN links to dear_po_header table
source_dearPLAIN links to Dear company name
document_modifiedDATE
supplier_guidPLAIN links to supplier table
order_dateDATE A Date field with no timezone, date when PO was created
transaction_dateDATE Invoice data, a Date field with no timezone
inv_due_dateDATE Invoice due date, a Date with no timezone
product_guidPLAIN
commentPLAIN
line_descriptionPLAIN
document_exrateDECIMAL_NUMBER
document_currencyPLAIN
base_currencyPLAIN
invoice_quantityDECIMAL_NUMBER qty associated with revenue, such as invoice or credit note
movement_quantityDECIMAL_NUMBER quantity associated with stock movement, >0 for receipt
transaction_typePLAIN
order_referencePLAIN
global_purchase_statusPLAIN Refer to Dear API documentation
order_numberPLAIN
invoice_numberPLAIN
discount_percentageDECIMAL_NUMBER Percentage discount
unit_before_discountCURRENCY in document currency
unit_netCURRENCY Unit price with tax removed, even if tax inclusive, document currency
unit_taxCURRENCY unit tax, document currency
warehousePLAIN location from PO header
accountPLAIN
cogs_accountPLAIN
attribute1PLAIN
attribute2PLAIN
attribute3PLAIN
attribute4PLAIN
attribute5PLAIN
attribute6PLAIN
attribute7PLAIN
attribute8PLAIN
attribute9PLAIN
attribute10PLAIN
year_month_currencyPLAIN Used to find average monthly exrate
line_invoiceCURRENCY Line total, document currency. Always without tax, even if the order is tax inclusive
line_taxCURRENCY Tax in the line, document currency
line_invoice_base_currencyCURRENCY Line total, base currency. Always without tax, even if the order is tax inclusive
line_tax_base_currencyCURRENCY Tax in the line, base currency

Return to top of page

Table name: purchase_order_payments

Table description: Payments relating to POs, including deposits and invoice payments

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
order_guidPLAIN
order_referencePLAIN
doc_guidPLAIN GUID of the Task, ie the Invoice or Credit Note
doc_numberPLAIN Document number of the Task, ie the Invoice or Credit Note
payment_guidPLAIN
payment_typePLAIN Prepayment,InvoicePayment,Refund
payment_referencePLAIN
payment_amountCURRENCY
payment_dateDATE Payment date, no timezone
payment_accountPLAIN
payment_currencyPLAIN
base_currencyPLAIN
payment_exrateDECIMAL_NUMBER

Return to top of page

Table name: purchases_lines_lifecycle

Table description: Shows the status of a PO line (what's been invoiced, received etc)

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidPLAIN
order_referencePLAIN
source_dearPLAIN
line_typePLAIN
product_guidPLAIN
skuPLAIN
ordered_qtyDECIMAL_NUMBER In the case of a Blind Receipt, this is the invoice qty
received_and_putaway_qtyDECIMAL_NUMBER Total received or putaway
not_yet_received_qtyDECIMAL_NUMBER Qty which has not been received or putaway, that is, stock which has not arrived at all
never_to_be_received_qtyDECIMAL_NUMBER If an order is marked as Received, then this is the quantity ordered but which is now cancelled
invoiced_qtyDECIMAL_NUMBER
credited_qtyDECIMAL_NUMBER
returned_qtyDECIMAL_NUMBER
avg_unit_price_baseCURRENCY
ordered_basecurCURRENCY
received_and_putaway_basecurCURRENCY
not_yet_received_basecurCURRENCY
never_to_be_received_basecurCURRENCY
invoiced_basecurCURRENCY
credited_basecurCURRENCY
returned_basecurCURRENCY

Return to top of page

Table name: avg_ex_rate

Table description: Monthly average exchanges rates from the ECB. Can be used to support data from different Dear instances in difference base currencies

Column NameDatatypeDescription
idAUTO_NUMBER
year_month_currencyPLAIN
in_reporting_currencyCURRENCY Historical monthly average exchange rate from ECB, multiple from currency by this to get to reporting currency
value_dateDATE Always 15th of the month
from_currencyPLAIN

Return to top of page

Table name: shippit_transactions

Table description: This table supports Shippit transactional data, but this must be separately enabled.

Column NameDatatypeDescription
idAUTO_NUMBER
order_guidNUMBER
storePLAIN
shippit_referencePLAIN
receiver_namePLAIN
recipient_contact_numberPLAIN
recipient_emailPLAIN
carrierPLAIN
manifest_idPLAIN
carrier_job_idPLAIN
retailer_invoicePLAIN
destination_addressPLAIN
destination_suburbPLAIN
destination_postcodePLAIN
destination_statePLAIN
destination_countryPLAIN
destination_latitudeDECIMAL_NUMBER
destination_longitudeDECIMAL_NUMBER
special_instructionsPLAIN
atlPLAIN
shipping_priceCURRENCY
billed_byPLAIN
countDECIMAL_NUMBER
lengthDECIMAL_NUMBER
widthDECIMAL_NUMBER
depthDECIMAL_NUMBER
weightDECIMAL_NUMBER
current_statePLAIN
delivery_country_codePLAIN
service_levelPLAIN
last_updateDATE
order_placedDATE
despatch_in_progressDATE
ready_for_pickupDATE
in_transitDATE
with_driverDATE
delivery_attemptedDATE
awaiting_collectionDATE
completedDATE
expected_delivery_dateDATE
carrier_quotesPLAIN
ratingPLAIN
commentPLAIN
casePLAIN

Return to top of page

Table name: table_update_status

Table description: During a data update with the Zoho connector, this table will show that a data update is in progress

Column NameDatatypeDescription
idAUTO_NUMBER
source_dearPLAIN
zoho_table_namePLAIN
commentPLAIN
time_of_comment_utcDATE

Return to top of page