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_location

dear_product

dear_supplier_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

threepl_stock_analysis

dear_stock_transfer_header

dear_stock_transfer_lines

Table name: sales_fact

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

Column Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
source_dear	PLAIN
task_id	PLAIN	Invoice task id (guid unique to invoice header)
document_modified	DATE
customer_guid	PLAIN
transaction_date	DATE	invoice date. A date with no timezone
inv_due_date	DATE	Invoice due date, A date with no timezone
product_guid	PLAIN	Note: This field will show the line description for ad-hoc additional charges (which do not have a real product ID)
line_description	PLAIN
revenue_quantity	DECIMAL_NUMBER	quantity which affects revenue accounts (invoice or credited amount)
dropship_quantity	DECIMAL_NUMBER	this is included in revenue quantity, not in addition to it
movement_quantity	DECIMAL_NUMBER	quantity which affects inventory accounts (shipments or returns)
transaction_type	PLAIN
order_reference	PLAIN
global_sale_status	PLAIN	see Dear API reference
document_reference	PLAIN
document_currency	PLAIN
unit_before_discount	CURRENCY	unit price in document currency before discount, , always tax exclusive
unit_net	CURRENCY	unit price in document currency after discount, always tax exclusive
tax_rule	PLAIN	inclusive or exclusive on the order, but tax is never included in revenue or unit prices
unit_tax	CURRENCY	Unit tax (document currency)
warehouse	PLAIN	order header location, not the location of the pick
sales_rep	PLAIN	sales rep, from the order header
unit_cogs_average_cost	CURRENCY	average cost price in base currency at the time of the sales order
unit_cogs_actual	CURRENCY	actual cost price in base currency. Only available if there is a shipment
unit_cogs_best_available	CURRENCY	is actual cost if available, else average cost
document_exrate	DECIMAL_NUMBER
revenue_account	PLAIN
cogs_account	PLAIN	Not available at present
base_currency	PLAIN
attribute1	PLAIN
attribute2	PLAIN
attribute3	PLAIN
attribute4	PLAIN
attribute5	PLAIN
attribute6	PLAIN
attribute7	PLAIN
attribute8	PLAIN
attribute9	PLAIN
attribute10	PLAIN
shipping_company_name	PLAIN
shipping_addr1	PLAIN
shipping_addr2	PLAIN
shipping_postalcode	PLAIN
shipping_region	PLAIN
shipping_town	PLAIN
shipping_country	PLAIN
year_month_currency	PLAIN
line_revenue_base_currency	CURRENCY	Line revenue in base currency, without tax
line_cogs_base_currency	CURRENCY	Actual cost if available else average cost
line_margin_base_currency	CURRENCY	Line margin in base currency, based on actual cogs if available, else average cogs.
line_estimated_invoice_margin	CURRENCY	Identical to line_margin_base_currency, present for historical reasons
cogs_source	PLAIN	Source of margin: AverageCost or Actual

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 Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN
source_dear	PLAIN
line_type	PLAIN
product_guid	PLAIN
sku	PLAIN
quoted_qty	DECIMAL_NUMBER
ordered_qty	DECIMAL_NUMBER
shipped_qty	DECIMAL_NUMBER	dropships are included in this total
shipped_with_tracking_qty	DECIMAL_NUMBER
in_fulfilment_qty	DECIMAL_NUMBER	The qty is in auth picking but not yet auth shipped
not_yet_processed_qty	DECIMAL_NUMBER	Has not entered auth picking
not_yet_shipped_qty	DECIMAL_NUMBER	not yet shipped or dropshipped
never_to_be_fulfilled_qty	DECIMAL_NUMBER	If a back order is cancelled because the order is marked as Fulfilled
invoiced_qty	DECIMAL_NUMBER
drop_ship_qty	DECIMAL_NUMBER
credited_qty	DECIMAL_NUMBER
returned_qty	DECIMAL_NUMBER	Not implemented yet
avg_unit_price_base	CURRENCY
quoted_basecur	CURRENCY
ordered_basecur	CURRENCY
shipped_basecur	CURRENCY	valued at order price, not cogs, so that is is comparable
shipped_with_tracking_basecur	CURRENCY
in_fulfilment_basecur	CURRENCY
not_yet_processed_basecur	CURRENCY	quantity which has not even started fulfilment
not_yet_shipped_basecur	CURRENCY	The counts drop-shipped qty as shipped
never_to_be_fulfilled_basecur	CURRENCY	Will not be fulfilled as the order is marked as closed
invoiced_basecur	CURRENCY
drop_ship_basecur	CURRENCY
actual_cogs_per_unit	CURRENCY
avg_cost_per_unit	CURRENCY	At the time this row was last updated: this nbr can change
unit_cogs_best_available	CURRENCY	Actual COGS if available, otherwise average cost
cogs_source	PLAIN	Source of margin: AverageCost or Actual
credited_basecur	CURRENCY
returned_basecur	CURRENCY	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, for detailed revenue and cogs analysis. Covers orders, shipments, credit notes and restocks

Column Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN	dear sale order number
document_reference	PLAIN	invoice or credit note number
source_dear	PLAIN
transaction_type	PLAIN
transaction_date	DATE	Date, No timezone
product_guid	PLAIN
account	PLAIN
quantity	DECIMAL_NUMBER
signed_revenue_quantity	DECIMAL_NUMBER	positive means invoice, negative is a credit,
signed_inventory_quantity	DECIMAL_NUMBER	positive means shipment (decrease in inventory value), negative is a return. Adjustments such as landed costs are included too. The quantity is not a physical movement qty, but an accounting quantity.
unit_value_base	CURRENCY
rev_unit_value_base	CURRENCY	The unit cost if this transaction affects a revenue-style/AR account
cogs_unit_value_base	CURRENCY	The unit costs if this transaction affects the value of inventory/cogs
rev_line_value_base	CURRENCY	The line cost if this transaction affects a revenue-style/AR account
cogs_line_value_base	CURRENCY	The line costs if this transaction affects the value of inventory/cogs

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 Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN
source_dear	PLAIN
shipment_date	DATE	DateTime, no timezone
shipment_carrier	PLAIN	The carrier used on the shipment line
shipping_notes	PLAIN
product_guid	PLAIN
warehouse	PLAIN
warehouse_guid	PLAIN
fulfilment_nbr	NUMBER
box	PLAIN
shipment_quantity	DECIMAL_NUMBER
cogs	CURRENCY
cogs_date	DATE	Date field, no timezone
shipped_value_base	CURRENCY
task_id	PLAIN	Invoice task id (guid unique to invoice header)
base_currency	PLAIN

Return to top of page

Table name: dear_sales_header

Table description: Sales Header data

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
order_guid	PLAIN
global_sale_status	PLAIN	SO Header Sale Status, see Available Sale Statues https://dearinventory.docs.apiary.io/#reference/sale
order_status	PLAIN	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_status	PLAIN	Possible Values are VOIDED, NOT AVAILABLE, PICKED, PICKING , NOT PICKED , PARTIALLY PICKED
combined_packing_status	PLAIN	Possible Values are VOIDED, NOT AVAILABLE, PACKED, PACKING, NOT PACKED, PARTIALLY PACKED
combined_shipping_status	PLAIN	Possible Values are VOIDED, NOT AVAILABLE, SHIPPED, SHIPPING , NOT SHIPPED , PARTIALLY SHIPPED
fulfillment_status	PLAIN	Possible Values are FULFILLED, PARTIALLY FULFILLED, NOT AVAILABLE, NOT FULFILLED, VOIDED
combined_invoice_status	PLAIN	Possible Values are VOIDED, DRAFT, AUTHORISED, NOT AVAILABLE, PAID
combined_payment_status	PLAIN	NOT REFUNDED, PREPAID, PARTIALLY PAID, UNPAID, PAID, VOIDED
combined_tracking_numbers	PLAIN
order_reference	PLAIN	Sale Order Number
sale_order_date	DATE	Date when order was created (no timezone)
document_modified	DATE
customer_guid	PLAIN
sales_rep	PLAIN
source_channel	PLAIN	Source of the order
document_currency	PLAIN
document_exrate	DECIMAL_NUMBER
base_currency	PLAIN
warehouse	PLAIN
order_memo	MULTI_LINE
sale_note	MULTI_LINE
sale_contact	PLAIN
sale_contact_phone	PLAIN
sale_contact_email	PLAIN
order_type	PLAIN
attribute_set	PLAIN
attribute1	PLAIN
attribute2	PLAIN
attribute3	PLAIN
attribute4	PLAIN
attribute5	PLAIN
attribute6	PLAIN
attribute7	PLAIN
attribute8	PLAIN
attribute9	PLAIN
attribute10	PLAIN
ship_by	DATE	Date, no timezone provided
customer_reference	PLAIN
quoted_total_ex_tax_base	CURRENCY	Includes everything
quoted_total_incl_tax_base	CURRENCY	Includes everything
ordered_total_ex_tax_base	CURRENCY	Includes everything on the order
ordered_total_incl_tax_base	CURRENCY	Includes everything on the order
ordered_stock_ex_tax_base	CURRENCY	Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included
ordered_stock_incl_tax_base	CURRENCY	Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included
invoiced_total_ex_tax_base	CURRENCY	Includes everything on the invoice
invoiced_total_incl_tax_base	CURRENCY	Includes everything on the invoice
credited_total_ex_tax_base	CURRENCY	Includes everything on the CN
credited_total_incl_tax_base	CURRENCY	Includes everything on the CN
paid_total_base	CURRENCY	Payments can't be split by item type, so payment will include additional charges and non-inventory items
paid_total_ex_tax_base	CURRENCY	Estimated 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_base	CURRENCY	At selling price, not cogs, so it is comparable with the other columns. Drop shipped items are valued as fully shipped
refunded_total_base	CURRENCY
refunded_ex_tax_base	CURRENCY
prepayment_total_incl_tax_base	CURRENCY	Deposit paid on the quote (incl tax).
prepayment_total_ex_tax_base	CURRENCY	Deposit paid on the quote (estimated tax removed for comaparison)
prepayment_applied_incl_tax_base	CURRENCY	If the customer credit accounts is setup in Dear Instance Settings, this records payments made using customer credits, including deposits
prepayment_applied_ex_tax_base	CURRENCY	Records payments made using customer credits, including deposits (estimated tax removed for comparison)
in_fulfilment_ex_tax_base	CURRENCY	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_url	URL	This URL takes you to the Dear order, as long as you are logged into Dear
backorder_analysis_url	URL	This URL takes you a GrowthPath backorder and fulfilment summary of the order
shipping_company_name	PLAIN
shipping_addr1	PLAIN
shipping_addr2	PLAIN
shipping_postalcode	PLAIN
shipping_region	PLAIN
shipping_town	PLAIN
shipping_country	PLAIN
shipping_notes	PLAIN
sale_carrier	PLAIN
billing_addr1	PLAIN
billing_addr2	PLAIN
billing_postalcode	PLAIN
billing_region	PLAIN
billing_town	PLAIN
billing_country	PLAIN

Return to top of page

Table name: sales_order_payments

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

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
order_guid	PLAIN
order_reference	PLAIN
doc_guid	PLAIN	GUID of the Task, ie the Invoice or Credit Note
doc_number	PLAIN	Document number of the Task, ie the Invoice or Credit Note
payment_guid	PLAIN
payment_type	PLAIN	Prepayment,InvoicePayment,Refund
payment_reference	PLAIN
payment_amount	CURRENCY	Always positive even for refunds
payment_amount_signed	CURRENCY	negative for refunds
payment_date	DATE	No timezone
payment_account	PLAIN
base_currency	PLAIN
payment_currency	PLAIN
payment_exrate	DECIMAL_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 Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN
document_exrate	DECIMAL_NUMBER	From PO Header
document_modified	DATE
document_currency	PLAIN
base_currency	PLAIN
source_dear	PLAIN
line_type	PLAIN
product_guid	PLAIN
description	PLAIN
order_quantity	DECIMAL_NUMBER
comment	PLAIN
backorder_quantity	DECIMAL_NUMBER
line_average_cost	CURRENCY	Average cost (doc currency) used by Dear to estimate margin before picking
unit_price_before_discount	CURRENCY
unit_price_net	CURRENCY
unit_actual_cogs_base_cur	CURRENCY	Actual cogs (base cur) based on shipment, averaged across all shipments, if no shipments, then 0
unit_average_cost_base_cur	CURRENCY	Average cost (base cur)
unit_cogs_best_available	CURRENCY	Actual COGS if available, otherwise average cost
cogs_source	PLAIN	Source of margin: AverageCost or Actual
line_total	CURRENCY
line_tax_total	CURRENCY
unit_price_before_discount_base_cur	CURRENCY
unit_price_net_base_cur	CURRENCY
line_total_base_cur	CURRENCY
line_tax_total_base_cur	CURRENCY
line_estimated_order_margin_base_cur	CURRENCY
line_tax_rule	PLAIN
dropship	BOOLEAN

Return to top of page

Table name: dear_customer

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
document_modified	DATE
customer_guid	PLAIN
customer_name	PLAIN
status	PLAIN
tags	PLAIN
payment_term	PLAIN	Customer default payment term
customer_sales_rep	PLAIN	Customer default sales rep
price_tier	PLAIN	Customer default price tier
location	PLAIN	Customer default location
discount	PLAIN	Customer default discount
comments	PLAIN	Customer comments (2000 chars)
carrier	PLAIN	Customer default carrier
credit_limit	NUMBER	value of 0 means no credit limit
is_on_credit_hold	BOOLEAN	True if customer is on credit hold
tax_rule	PLAIN
tax_number	PLAIN
source_dear	PLAIN
attribute_set	PLAIN
cust_attribute1	PLAIN
cust_attribute2	PLAIN
cust_attribute3	PLAIN
cust_attribute4	PLAIN
cust_attribute5	PLAIN
cust_attribute6	PLAIN
cust_attribute7	PLAIN
cust_attribute8	PLAIN
cust_attribute9	PLAIN
cust_attribute10	PLAIN

Return to top of page

Table name: dear_customer_addresses

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
address_guid	PLAIN
customer_guid	PLAIN
line1	PLAIN
line2	PLAIN
city	PLAIN
state	PLAIN
postcode	PLAIN
country	PLAIN	Must be an ISO standard country name
type	PLAIN
default_for_type	BOOLEAN

Return to top of page

Table name: dear_customer_contacts

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
contact_guid	PLAIN
customer_guid	PLAIN
name	PLAIN
phone	PLAIN
mobile_phone	PLAIN
fax	PLAIN
email	PLAIN
website	PLAIN
comment	PLAIN
default	BOOLEAN
include_in_email	BOOLEAN

Return to top of page

Table name: product_movements

Table description: All stock movements with quantity and date

Column Name	Datatype	Description
id	AUTO_NUMBER
product_guid	PLAIN
document_modified	DATE
source_dear	PLAIN
movement_type	PLAIN
movement_taskid	PLAIN	Link to the source documents, such as a PO
movement_date	DATE	No timezone
movement_number	PLAIN	A document reference, such as a sales order number
movement_status	PLAIN	Currently documented in the API but not used by Dear
quantity	DECIMAL_NUMBER	This is a signed number, > 0 means increase in SOH
location	PLAIN	Name of the location, not the ID
batch_sn	PLAIN
expiry_date	DATE	No timezone
from_to	PLAIN
amount	CURRENCY	Base currency value of the transaction

Return to top of page

Table name: dear_customer_tag

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
document_modified	DATE
customer_guid	PLAIN
source_dear	PLAIN	Link to Dear instance which is the source of the data
tag_value	PLAIN

Return to top of page

Table name: dear_location

Table description: Dear Locations (that is, warehouses, not bins)

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
location_guid	PLAIN
location_name	PLAIN
deprecated	BOOLEAN	True means Deprecated (not active)

Return to top of page

Table name: dear_product

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
document_modified	DATE
source_dear	PLAIN
product_guid	PLAIN
product_guid_dup1	PLAIN
product_guid_dup2	PLAIN
product_guid_dup3	PLAIN
type	PLAIN
sku	PLAIN
product_name	PLAIN
product_category	PLAIN
brand	PLAIN
average_cost	CURRENCY
tags	PLAIN
uom	PLAIN
status	PLAIN	same value as Dear Status
default_supplier_guid	PLAIN	Default supplier
costing_method	PLAIN
default_location	PLAIN
barcode	PLAIN
stock_locator	PLAIN
purchase_tax_rule	PLAIN
sale_tax_rule	PLAIN
weight	DECIMAL_NUMBER
weight_units	PLAIN
attribute_set	PLAIN
prod_attribute1	PLAIN
prod_attribute2	PLAIN
prod_attribute3	PLAIN
prod_attribute4	PLAIN
prod_attribute5	PLAIN
prod_attribute6	PLAIN
prod_attribute7	PLAIN
prod_attribute8	PLAIN
prod_attribute9	PLAIN
prod_attribute10	PLAIN
price_tier1	CURRENCY
price_tier2	CURRENCY
price_tier3	CURRENCY
price_tier4	CURRENCY
price_tier5	CURRENCY
price_tier6	CURRENCY
price_tier7	CURRENCY
price_tier8	CURRENCY
price_tier9	CURRENCY
price_tier10	CURRENCY
carton_length	CURRENCY
carton_width	CURRENCY
carton_height	CURRENCY
carton_quantity	CURRENCY
carton_inner_quantity	CURRENCY

Return to top of page

Table name: dear_supplier_product

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
product_guid	PLAIN
supplier_guid	PLAIN
supplier_sku	PLAIN
supplier_product_name	PLAIN
currency	PLAIN
latest_purchase_cost	CURRENCY
fixed_cost	CURRENCY
last_supplied	DATE

Return to top of page

Table name: dear_product_family

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
product_family_guid	PLAIN
document_modified	DATE
source_dear	PLAIN
product_guid	PLAIN
family_name	PLAIN
short_description	PLAIN
family_option1_name	PLAIN
family_option1_value	PLAIN
family_option2_name	PLAIN
family_option2_value	PLAIN
family_option3_name	PLAIN
family_option3_value	PLAIN

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 Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN	Link to Dear instance which is the source of the data
dear_company_name	PLAIN
last_update	DATE
base_currency_code	PLAIN	Three character currency code

Return to top of page

Table name: dear_product_availability

Table description: Product availability from Dear, plus hard allocations (qty reserved for a specfic order)

Column Name	Datatype	Description
id	AUTO_NUMBER
id_product_availability	MULTI_LINE
source_dear	PLAIN
product_guid	PLAIN
sku	PLAIN
name	PLAIN
barcode	PLAIN
location	PLAIN	This is the location name, forced to uppercase
bin	PLAIN
batch	PLAIN
expiry_date	DATE	A Date field (no timezone)
onhand	DECIMAL_NUMBER	Quantity on hand
allocated	DECIMAL_NUMBER	Quantity allocated
hard_allocated	DECIMAL_NUMBER	Quantity locked to an authorised pick
available	DECIMAL_NUMBER	Quantity available
in_transit	DECIMAL_NUMBER	In-transit (between locations)
onorder	DECIMAL_NUMBER	Quantity on an authorised PO
stockonhand_base_currency	CURRENCY	value of stock on hand, base currency

Return to top of page

Table name: dear_product_tag

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
document_modified	DATE
product_guid	PLAIN
source_dear	PLAIN
tag_value	PLAIN

Return to top of page

Table name: dear_supplier

Table description:

Column Name	Datatype	Description
id	AUTO_NUMBER
document_modified	DATE
supplier_guid	PLAIN
supplier_name	PLAIN
status	PLAIN
tags	PLAIN
source_dear	PLAIN
attribute_set	PLAIN
supplier_attribute1	PLAIN
supplier_attribute2	PLAIN
supplier_attribute3	PLAIN
supplier_attribute4	PLAIN
supplier_attribute5	PLAIN
supplier_attribute6	PLAIN
supplier_attribute7	PLAIN
supplier_attribute8	PLAIN
supplier_attribute9	PLAIN
supplier_attribute10	PLAIN

Return to top of page

Table name: dear_po_header

Table description: Purchase Order header

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
order_guid	PLAIN
drop_ship_sale_guid	PLAIN
global_purchase_status	PLAIN	PO Header status
combined_receiving_status	PLAIN
combined_invoice_status	PLAIN
combined_payment_status	PLAIN
order_number	PLAIN
order_date	DATE	Date field when PO was created, no timezone
order_memo	MULTI_LINE
blind_receipt	BOOLEAN
dear_url	URL	This URL takes you to the Dear purchase order, as long as you are logged into Dear
po_note	MULTI_LINE
required_by_date	DATE	Date field, no timezone
document_modified	DATE
supplier_guid	PLAIN
document_currency	PLAIN
terms	PLAIN
contact	PLAIN
document_exrate	DECIMAL_NUMBER
base_currency	PLAIN
warehouse	PLAIN
attribute_set	PLAIN
attribute1	PLAIN
attribute2	PLAIN
attribute3	PLAIN
attribute4	PLAIN
attribute5	PLAIN
attribute6	PLAIN
attribute7	PLAIN
attribute8	PLAIN
attribute9	PLAIN
attribute10	PLAIN
ordered_total_ex_tax_base	CURRENCY	Translated at document exrate, not payment exrate
ordered_total_incl_tax_base	CURRENCY	Translated at document exrate, not payment exrate
invoiced_total_ex_tax_base	CURRENCY	Translated at document exrate, not payment exrate
invoiced_total_incl_tax_base	CURRENCY	Translated at document exrate, not payment exrate
credited_total_ex_tax_base	CURRENCY	Translated at document exrate, not payment exrate
credited_total_incl_tax_base	CURRENCY	Translated at document exrate, not payment exrate
paid_total_base	CURRENCY	This includes tax. Translated at document exrate, not payment exrate
deposits_total_base	CURRENCY	This includes tax. Translated at document exrate, not payment exrate
deposits_applied_base	CURRENCY	This includes tax. Translated at document exrate, not payment exrate
refunded_total_base	CURRENCY	includes tax. Translated at document exrate, not payment exrate
received_total_ex_tax_base	CURRENCY	Received and Put Away. Translated at document exrate, not payment exrate
never_to_be_received_total_ex_tax_base	CURRENCY	If PO is marked as received, this is what was cancelled. Translated at document exrate, not payment exrate
ordered_total_ex_tax	CURRENCY
ordered_total_incl_tax	CURRENCY
invoiced_total_ex_tax	CURRENCY
invoiced_total_incl_tax	CURRENCY
credited_total_ex_tax	CURRENCY
credited_total_incl_tax	CURRENCY
paid_total	CURRENCY	This includes tax
deposits_total	CURRENCY	This includes tax
deposits_applied	CURRENCY	This includes tax
refunded_total	CURRENCY	include tax
received_total_ex_tax	CURRENCY	Received and Put Away
never_to_be_received_total_ex_tax	CURRENCY	If PO is marked as received, this is what was cancelled
shipping_company_name	PLAIN
shipping_addr1	PLAIN
shipping_addr2	PLAIN
shipping_postalcode	PLAIN
shipping_region	PLAIN
shipping_town	PLAIN
shipping_country	PLAIN

Return to top of page

Table name: purchase_order_lines

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

Column Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN
source_dear	PLAIN
line_type	PLAIN
comment	PLAIN
product_guid	PLAIN
description	PLAIN
order_quantity	DECIMAL_NUMBER
unit_price_before_discount	CURRENCY	unit price before row discount, document currency
unit_price_net	CURRENCY	Unit price with tax removed, even if tax inclusive, document currency
unit_tax	CURRENCY	unit tax, document currency
unit_price_before_discount_base_cur	CURRENCY	unit price before row discount, base currency
unit_price_net_base_cur	CURRENCY	Net unit price in base currency
document_exrate	DECIMAL_NUMBER	From PO Header
document_currency	PLAIN
base_currency	PLAIN
line_total	CURRENCY	Line total, document currency. Includes tax if order is tax inclusive
line_tax_total	CURRENCY	Line tax total, document currency
line_total_base_cur	CURRENCY	Line total, base currency. Includes tax if order is tax inclusive
line_tax_total_base_cur	CURRENCY	Line tax total, base currency
line_tax_rule	PLAIN

Return to top of page

Table name: purchases_fact

Table description: based on authorised PO invoice lines

Column Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN	links to dear_po_header table
source_dear	PLAIN	links to Dear company name
document_modified	DATE
supplier_guid	PLAIN
order_date	DATE	A Date field with no timezone, date when PO was created
transaction_date	DATE	Invoice data, a Date field with no timezone
inv_due_date	DATE	Invoice due date, a Date with no timezone
product_guid	PLAIN
comment	PLAIN
line_description	PLAIN
document_exrate	DECIMAL_NUMBER
document_currency	PLAIN
base_currency	PLAIN
invoice_quantity	DECIMAL_NUMBER	qty associated with revenue, such as invoice or credit note
movement_quantity	DECIMAL_NUMBER	quantity associated with stock movement, >0 for receipt
transaction_type	PLAIN
order_reference	PLAIN
global_purchase_status	PLAIN	Refer to Dear API documentation
order_number	PLAIN
invoice_number	PLAIN
discount_percentage	DECIMAL_NUMBER	Percentage discount
unit_before_discount	CURRENCY	in document currency
unit_net	CURRENCY	Unit price with tax removed, even if tax inclusive, document currency
unit_tax	CURRENCY	unit tax, document currency
warehouse	PLAIN	location from PO header
account	PLAIN
cogs_account	PLAIN
attribute1	PLAIN
attribute2	PLAIN
attribute3	PLAIN
attribute4	PLAIN
attribute5	PLAIN
attribute6	PLAIN
attribute7	PLAIN
attribute8	PLAIN
attribute9	PLAIN
attribute10	PLAIN
year_month_currency	PLAIN	Used to find average monthly exrate
line_invoice	CURRENCY	Line total, document currency. Always without tax, even if the order is tax inclusive
line_tax	CURRENCY	Tax in the line, document currency
line_invoice_base_currency	CURRENCY	Line total, base currency. Always without tax, even if the order is tax inclusive
line_tax_base_currency	CURRENCY	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 Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
order_guid	PLAIN
order_reference	PLAIN
doc_guid	PLAIN	GUID of the Task, ie the Invoice or Credit Note
doc_number	PLAIN	Document number of the Task, ie the Invoice or Credit Note
payment_guid	PLAIN
payment_type	PLAIN	Prepayment,InvoicePayment,Refund
payment_reference	PLAIN
payment_amount	CURRENCY
payment_date	DATE	Payment date, no timezone
payment_account	PLAIN
payment_currency	PLAIN
base_currency	PLAIN
payment_exrate	DECIMAL_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 Name	Datatype	Description
id	AUTO_NUMBER
order_guid	PLAIN
order_reference	PLAIN
source_dear	PLAIN
line_type	PLAIN
product_guid	PLAIN
sku	PLAIN
ordered_qty	DECIMAL_NUMBER	In the case of a Blind Receipt, this is the invoice qty
received_and_putaway_qty	DECIMAL_NUMBER	Total received or putaway
not_yet_received_qty	DECIMAL_NUMBER	Qty which has not been received or putaway, that is, stock which has not arrived at all
never_to_be_received_qty	DECIMAL_NUMBER	If an order is marked as Received, then this is the quantity ordered but which is now cancelled
invoiced_qty	DECIMAL_NUMBER
credited_qty	DECIMAL_NUMBER
returned_qty	DECIMAL_NUMBER
avg_unit_price_base	CURRENCY
ordered_basecur	CURRENCY
received_and_putaway_basecur	CURRENCY
not_yet_received_basecur	CURRENCY
never_to_be_received_basecur	CURRENCY
invoiced_basecur	CURRENCY
credited_basecur	CURRENCY
returned_basecur	CURRENCY

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 Name	Datatype	Description
id	AUTO_NUMBER
year_month_currency	PLAIN
in_reporting_currency	CURRENCY	Historical monthly average exchange rate from ECB, multiple from currency by this to get to reporting currency
value_date	DATE	Always 15th of the month
from_currency	PLAIN
to_currency	PLAIN

Return to top of page

Table name: shippit_transactions

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

Column Name	Datatype	Description
id	AUTO_NUMBER
order_guid	NUMBER
store	PLAIN
shippit_reference	PLAIN
receiver_name	PLAIN
recipient_contact_number	PLAIN
recipient_email	PLAIN
carrier	PLAIN
manifest_id	PLAIN
carrier_job_id	PLAIN
retailer_invoice	PLAIN
destination_address	PLAIN
destination_suburb	PLAIN
destination_postcode	PLAIN
destination_state	PLAIN
destination_country	PLAIN
destination_latitude	DECIMAL_NUMBER
destination_longitude	DECIMAL_NUMBER
special_instructions	PLAIN
atl	PLAIN
shipping_price	CURRENCY
billed_by	PLAIN
count	DECIMAL_NUMBER
length	DECIMAL_NUMBER
width	DECIMAL_NUMBER
depth	DECIMAL_NUMBER
weight	DECIMAL_NUMBER
current_state	PLAIN
delivery_country_code	PLAIN
service_level	PLAIN
last_update	DATE
order_placed	DATE
despatch_in_progress	DATE
ready_for_pickup	DATE
in_transit	DATE
with_driver	DATE
delivery_attempted	DATE
awaiting_collection	DATE
completed	DATE
expected_delivery_date	DATE
carrier_quotes	PLAIN
rating	PLAIN
comment	PLAIN
case	PLAIN

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 Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
zoho_table_name	PLAIN
comment	PLAIN
time_of_comment_utc	DATE
schema_version	NUMBER	Reserved for future use

Return to top of page

Table name: threepl_stock_analysis

Table description: Stores information about the stock levels of 3PLs. The table is unique by source_dear, stock_date, dear_location_od and product_guid

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
three_pl_name	PLAIN
dear_location_guid	PLAIN
threepl_location_name	PLAIN
product_guid	PLAIN
stock_date	DATE	This is the date when the report is run.
dear_timestamp	DATE	time when the Dear stock level was recorded
dear_stock_oh	DECIMAL_NUMBER	Dear Stock On Hand
threepl_timestamp	DATE	time when the 3PL stock level was recorded
threepl_stock_oh	DECIMAL_NUMBER	3PL Stock On Hand
dear_available	DECIMAL_NUMBER	Dear Available stock
threepl_available	DECIMAL_NUMBER	3PL Available stock
comment	PLAIN

Return to top of page

Table name: dear_stock_transfer_header

Table description: Stock Transfer

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
task_guid	PLAIN
from_location	PLAIN
from_location_bin	PLAIN
to_location	PLAIN
to_location_bin	PLAIN
status	PLAIN
number	PLAIN
cost_distribution_type	PLAIN
in_transit_account	PLAIN
departure_date	DATE
completion_date	DATE
required_by_date	DATE
reference	PLAIN
skip_order	BOOLEAN
document_modified	DATE

Return to top of page

Table name: dear_stock_transfer_lines

Table description: Stock Transfer Lines

Column Name	Datatype	Description
id	AUTO_NUMBER
source_dear	PLAIN
task_guid	PLAIN
product_guid	PLAIN
quantity_on_hand	DECIMAL_NUMBER	before transfer
quantity_available	DECIMAL_NUMBER	before transfer
transfer_quantity	DECIMAL_NUMBER
batch_sn	PLAIN
expiry_date	DATE
comments	PLAIN
document_modified	DATE

Return to top of page