Analytics Tables Documentation

This page documents all the analytics tables available in the system.

Table of Contents

avg_ex_rate

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

Model: Avg_ex_rate

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
year_month_currency CharField Nullable Optional
in_reporting_currency DecimalField Historical monthly average exchange rate from ECB, multiple from currency by this to get to reporting currency Nullable Optional
value_date DateTimeField Always 15th of the month Nullable Optional
from_currency CharField Nullable Optional
to_currency CharField Nullable Optional

dear_customer

Model: Dear_customer

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
customer_guid CharField Unique
customer_name CharField Nullable Optional
status CharField Nullable Optional
tags CharField Nullable Optional
payment_term CharField Customer default payment term Nullable Optional
customer_sales_rep CharField Customer default sales rep Nullable Optional
price_tier CharField Customer default price tier Nullable Optional
location CharField Customer default location Nullable Optional
discount CharField Customer default discount Nullable Optional
comments CharField Customer comments (2000 chars) Nullable Optional
carrier CharField Customer default carrier Nullable Optional
credit_limit IntegerField value of 0 means no credit limit Nullable Optional
is_on_credit_hold BooleanField True if customer is on credit hold
tax_rule CharField Nullable Optional
tax_number CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
attribute_set CharField Nullable Optional
cust_attribute1 CharField Nullable Optional
cust_attribute2 CharField Nullable Optional
cust_attribute3 CharField Nullable Optional
cust_attribute4 CharField Nullable Optional
cust_attribute5 CharField Nullable Optional
cust_attribute6 CharField Nullable Optional
cust_attribute7 CharField Nullable Optional
cust_attribute8 CharField Nullable Optional
cust_attribute9 CharField Nullable Optional
cust_attribute10 CharField Nullable Optional

dear_customer_addresses

Model: Dear_customer_addresses

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
address_guid CharField Nullable Optional
customer_guid CharField Nullable Optional
line1 CharField Nullable Optional
line2 CharField Nullable Optional
city CharField Nullable Optional
state CharField Nullable Optional
postcode CharField Nullable Optional
country CharField Must be an ISO standard country name Nullable Optional
type CharField Nullable Optional
default_for_type BooleanField

dear_customer_contacts

Model: Dear_customer_contacts

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
contact_guid CharField Nullable Optional
customer_guid CharField Nullable Optional
name CharField Nullable Optional
phone CharField Nullable Optional
mobile_phone CharField Nullable Optional
fax CharField Nullable Optional
email CharField Nullable Optional
website CharField Nullable Optional
comment CharField Nullable Optional
default BooleanField
include_in_email BooleanField

dear_customer_tag

Model: Dear_customer_tag

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
customer_guid CharField Nullable Optional
source_dear CharField Link to Dear instance which is the source of the data Nullable Optional
tag_value CharField Nullable Optional

dear_financial_transactions

Financial transactions from Cin7 Core (Dear). These are not auto updated.

Model: Financial_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
id_guid CharField Unique
task_guid CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
reference CharField Nullable Optional
debit_account_code CharField Nullable Optional
credit_account_code CharField Nullable Optional
amount FloatField Nullable Optional
original_amount FloatField Nullable Optional
currency_rate FloatField Nullable Optional
effective_date DateField Payment date, no timezone Nullable Optional
last_modified DateTimeField This is not from Dear, it is a experimental helper field for sync to analytics backend.
transaction CharField Nullable Optional
type CharField Transaction Type. Available values are Purchase,Sale,MoneySpend,MoneyReceive,BankTransfer,ExpenseClaimTask,FinishedGoods,InventoryWriteOff,StockTake,StockAdjustment,Journal,Disassembly,Depreciation Nullable Optional

dear_finished_goods_header

Not implemented yet

Model: Dear_fingoods_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata

dear_location

Dear Locations (that is, warehouses, not bins)

Model: Dear_location

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
location_guid CharField Primary Key Unique
location_name CharField Nullable Optional
deprecated BooleanField True means Deprecated (not active)

dear_metadata

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

Model: Dear_metadata

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear CharField Link to Dear instance which is the source of the data Unique
dear_company_name CharField Unique Nullable Optional
last_update DateTimeField Nullable Optional
base_currency_code CharField Three character currency code Nullable Optional

dear_po_header

Purchase Order header

Model: Dear_po_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid CharField Unique
drop_ship_sale_guid ForeignKey
FK to dear_sales_header 		Nullable
global_purchase_status CharField PO Header status Nullable Optional
combined_receiving_status CharField Nullable Optional
combined_invoice_status CharField Nullable Optional
combined_payment_status CharField Nullable Optional
order_number CharField Nullable Optional
order_date DateField Date field when PO was created, no timezone Nullable Optional
order_memo TextField Nullable Optional
blind_receipt BooleanField Nullable Optional
dear_url CharField This URL takes you to the Dear purchase order, as long as you are logged into Dear Nullable Optional
po_note TextField Nullable Optional
required_by_date DateField Date field, no timezone Nullable Optional
document_modified DateTimeField Nullable Optional
supplier_guid ForeignKey
FK to dear_supplier 		Nullable Optional
document_currency CharField Nullable Optional
terms CharField Nullable Optional
contact CharField Nullable Optional
document_exrate FloatField Nullable Optional
base_currency CharField Nullable Optional
warehouse CharField Nullable Optional
attribute_set CharField Nullable Optional
attribute1 CharField Nullable Optional
attribute2 CharField Nullable Optional
attribute3 CharField Nullable Optional
attribute4 CharField Nullable Optional
attribute5 CharField Nullable Optional
attribute6 CharField Nullable Optional
attribute7 CharField Nullable Optional
attribute8 CharField Nullable Optional
attribute9 CharField Nullable Optional
attribute10 CharField Nullable Optional
ordered_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
ordered_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
invoiced_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
invoiced_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
credited_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
credited_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
paid_total_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
deposits_total_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
deposits_applied_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
refunded_total_base DecimalField includes tax. Translated at document exrate, not payment exrate Nullable Optional
received_total_ex_tax_base DecimalField Received and Put Away. Translated at document exrate, not payment exrate Nullable Optional
never_to_be_received_total_ex_tax_base DecimalField If PO is marked as received, this is what was cancelled. Translated at document exrate, not payment exrate Nullable Optional
ordered_total_ex_tax DecimalField Nullable Optional
ordered_total_incl_tax DecimalField Nullable Optional
invoiced_total_ex_tax DecimalField Nullable Optional
invoiced_total_incl_tax DecimalField Nullable Optional
credited_total_ex_tax DecimalField Nullable Optional
credited_total_incl_tax DecimalField Nullable Optional
paid_total DecimalField This includes tax Nullable Optional
deposits_total DecimalField This includes tax Nullable Optional
deposits_applied DecimalField This includes tax Nullable Optional
refunded_total DecimalField include tax Nullable Optional
received_total_ex_tax DecimalField Received and Put Away Nullable Optional
never_to_be_received_total_ex_tax DecimalField If PO is marked as received, this is what was cancelled Nullable Optional
shipping_company_name CharField Nullable Optional
shipping_addr1 CharField Nullable Optional
shipping_addr2 CharField Nullable Optional
shipping_postalcode CharField Nullable Optional
shipping_region CharField Nullable Optional
shipping_town CharField Nullable Optional
shipping_country CharField Nullable Optional

dear_product

Model: Dear_product

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
product_guid CharField Unique
product_guid_dup1 CharField Unique
product_guid_dup2 CharField Unique
product_guid_dup3 CharField Unique
type CharField Nullable Optional
sku CharField Nullable Optional
product_name CharField Nullable Optional
product_category CharField Nullable Optional
brand CharField Nullable Optional
average_cost DecimalField Nullable Optional
tags CharField Nullable Optional
uom CharField Nullable Optional
status CharField same value as Dear Status Nullable Optional
default_supplier_guid ForeignKey
FK to dear_supplier 		Default supplier Nullable Optional
costing_method CharField Nullable Optional
default_location CharField Nullable Optional
barcode CharField Nullable Optional
stock_locator CharField Nullable Optional
purchase_tax_rule CharField Nullable Optional
sale_tax_rule CharField Nullable Optional
weight FloatField Nullable Optional
weight_units CharField Nullable Optional
attribute_set CharField Nullable Optional
prod_attribute1 CharField Nullable Optional
prod_attribute2 CharField Nullable Optional
prod_attribute3 CharField Nullable Optional
prod_attribute4 CharField Nullable Optional
prod_attribute5 CharField Nullable Optional
prod_attribute6 CharField Nullable Optional
prod_attribute7 CharField Nullable Optional
prod_attribute8 CharField Nullable Optional
prod_attribute9 CharField Nullable Optional
prod_attribute10 CharField Nullable Optional
price_tier1 DecimalField Nullable Optional
price_tier2 DecimalField Nullable Optional
price_tier3 DecimalField Nullable Optional
price_tier4 DecimalField Nullable Optional
price_tier5 DecimalField Nullable Optional
price_tier6 DecimalField Nullable Optional
price_tier7 DecimalField Nullable Optional
price_tier8 DecimalField Nullable Optional
price_tier9 DecimalField Nullable Optional
price_tier10 DecimalField Nullable Optional
carton_length DecimalField Nullable Optional
carton_width DecimalField Nullable Optional
carton_height DecimalField Nullable Optional
carton_quantity DecimalField Nullable Optional
carton_inner_quantity DecimalField Nullable Optional
drop_ship_mode CharField No Drop Ship, Optional Drop Ship, Always Drop Ship Nullable Optional
sellable BooleanField
hscode CharField Nullable Optional
country_of_origin CharField Nullable Optional
country_of_origin_code CharField Nullable Optional
bom_type CharField read only. Assembly, Production, Make to Order, None Nullable Optional
auto_assembly BooleanField
auto_disassembly BooleanField
qty_to_produce DecimalField Nullable Optional
cogs_account CharField Nullable Optional
revenue_account CharField Nullable Optional
expense_account CharField Nullable Optional
inventory_account CharField Nullable Optional

dear_product_availability

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

Model: Dear_product_availability

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
id_product_availability TextField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField Nullable Optional
name CharField Nullable Optional
barcode CharField Nullable Optional
location CharField This is the location name, forced to uppercase Nullable Optional
bin CharField Nullable Optional
batch CharField Nullable Optional
expiry_date DateField A Date field (no timezone) Nullable Optional
onhand FloatField Quantity on hand Nullable Optional
allocated FloatField Quantity allocated Nullable Optional
hard_allocated FloatField Quantity locked to an authorised pick Nullable Optional
available FloatField Quantity available Nullable Optional
in_transit FloatField In-transit (between locations) Nullable Optional
onorder FloatField Quantity on an authorised PO Nullable Optional
stockonhand_base_currency DecimalField value of stock on hand, base currency Nullable Optional

dear_product_family

Model: Dear_product_family

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
product_family_guid CharField Nullable Optional
document_modified DateTimeField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
product_guid ForeignKey
FK to dear_product 		Nullable Optional
family_name CharField Nullable Optional
short_description CharField Nullable Optional
family_option1_name CharField Nullable Optional
family_option1_value CharField Nullable Optional
family_option2_name CharField Nullable Optional
family_option2_value CharField Nullable Optional
family_option3_name CharField Nullable Optional
family_option3_value CharField Nullable Optional

dear_product_tag

Model: Dear_product_tag

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
source_dear ForeignKey
FK to dear_metadata
tag_value CharField Nullable Optional

dear_sales_header

Sales Header data

Model: Dear_sales_header

Fields

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

dear_sales_invoice

Sales Invoices and Credit notes header: invoices and credit notes.

Model: Dear_invoice_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
task_id CharField Unique invoice id, this is present in sales_fact_table for joining lines but is not set up as a foreign key yet. Note that sometimes invoice and credit notes have the same task ID, for sure if the sale started as a simple sale Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_sales_header 		Nullable
invoice_number CharField invoice or credit note number Nullable Optional
sales_order_number CharField here as a convenience. To join to sales order header, use order_guid Nullable Optional
memo CharField Invoice memo Nullable Optional
invoice_status CharField Invoice status, one of NOT AVAILABLE,DRAFT,AUTHORISED,VOIDED,PAID see https://dearinventory.docs.apiary.io/#reference/sale Nullable Optional
invoice_date DateField Serves as the transaction date for the invoice or credit note. There is no timezone for this date Nullable Optional
invoice_due_date DateField There is no timezone for this date Nullable Optional
document_modified DateTimeField Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
document_currency CharField Nullable Optional
document_exrate FloatField Nullable Optional
base_currency CharField Nullable Optional
linked_fulfilments CharField only present if this invoice was linked to one or more fulfilments Nullable Optional

dear_stock_adjustment_header

Stock Adjustment header

Model: Dear_stock_adjustment_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
task_guid CharField Primary Key Unique
stocktake_number CharField Nullable Optional
status CharField
effective_date DateTimeField Nullable Optional
reference CharField Nullable Optional
document_modified DateTimeField Nullable Optional
account CharField Nullable Optional

dear_stock_adjustment_lines

Stock Adjustment Lines

Model: Dear_stock_adjustment_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
task_guid ForeignKey
FK to dear_stock_adjustment_header
product_guid ForeignKey
FK to dear_product 		Nullable Optional
line_type CharField new or existing Nullable Optional
quantity_on_hand FloatField before transfer Nullable Optional
quantity_available FloatField before transfer Nullable Optional
adjustment_quantity FloatField before transfer Nullable Optional
unit_cost DecimalField only used in New Stock Lines Nullable Optional
location CharField Nullable Optional
location_bin CharField Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateTimeField Nullable Optional
comments CharField Nullable Optional
document_modified DateTimeField Nullable Optional

dear_stock_transfer_header

Stock Transfer

Model: Dear_stock_transfer_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
task_guid CharField Primary Key Unique
from_location CharField Nullable Optional
from_location_bin CharField Nullable Optional
to_location CharField Nullable Optional
to_location_bin CharField Nullable Optional
status CharField
number CharField Nullable Optional
cost_distribution_type CharField
in_transit_account CharField Nullable Optional
departure_date DateTimeField Nullable Optional
completion_date DateTimeField Nullable Optional
required_by_date DateTimeField Nullable Optional
reference CharField Nullable Optional
skip_order BooleanField
document_modified DateTimeField Nullable Optional

dear_stock_transfer_lines

Stock Transfer Lines

Model: Dear_stock_transfer_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
task_guid ForeignKey
FK to dear_stock_transfer_header
product_guid ForeignKey
FK to dear_product 		Nullable Optional
quantity_on_hand FloatField before transfer Nullable Optional
quantity_available FloatField before transfer Nullable Optional
transfer_quantity FloatField Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateTimeField Nullable Optional
comments CharField Nullable Optional
document_modified DateTimeField Nullable Optional

dear_supplier

Model: Dear_supplier

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
supplier_guid CharField Unique
supplier_name CharField Nullable Optional
status CharField Nullable Optional
tags CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
attribute_set CharField Nullable Optional
supplier_attribute1 CharField Nullable Optional
supplier_attribute2 CharField Nullable Optional
supplier_attribute3 CharField Nullable Optional
supplier_attribute4 CharField Nullable Optional
supplier_attribute5 CharField Nullable Optional
supplier_attribute6 CharField Nullable Optional
supplier_attribute7 CharField Nullable Optional
supplier_attribute8 CharField Nullable Optional
supplier_attribute9 CharField Nullable Optional
supplier_attribute10 CharField Nullable Optional

dear_supplier_product

Model: Dear_supplier_product

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
product_guid CharField
supplier_guid CharField
supplier_sku CharField Nullable Optional
supplier_product_name CharField Nullable Optional
currency CharField Nullable Optional
latest_purchase_cost DecimalField Nullable Optional
fixed_cost DecimalField Nullable Optional
last_supplied DateTimeField Nullable Optional

mainfreight_events

Mainfreight events

Model: Mainfreight_events

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
unique_key CharField The ID field
event_type CharField
event_datetime DateTimeField
last_modified DateTimeField
order_reference CharField Dear Sales Number Nullable Optional
fulfilment_number IntegerField
event_text TextField Nullable Optional
source_dear ForeignKey
FK to dear_metadata

product_movements

All stock movements with quantity and date

Model: Product_movements

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
product_guid ForeignKey
FK to dear_product 		Nullable Optional
document_modified DateTimeField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
movement_type CharField Nullable Optional
movement_taskid CharField Link to the source documents, such as a PO Nullable Optional
movement_date DateField No timezone Nullable Optional
movement_number CharField A document reference, such as a sales order number Nullable Optional
movement_status CharField Currently documented in the API but not used by Dear Nullable Optional
quantity FloatField This is a signed number, > 0 means increase in SOH Nullable Optional
location CharField Name of the location, not the ID Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateField No timezone Nullable Optional
from_to CharField Nullable Optional
amount DecimalField Base currency value of the transaction Nullable Optional

purchase_order_lines

PO lines includng draft and not yet invoiced POs

Model: Purchase_order_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
comment CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
description CharField Nullable Optional
order_quantity FloatField Nullable Optional
unit_price_before_discount DecimalField unit price before row discount, document currency Nullable Optional
unit_price_net DecimalField Unit price with tax removed, even if tax inclusive, document currency Nullable Optional
unit_tax DecimalField unit tax, document currency Nullable Optional
unit_price_before_discount_base_cur DecimalField unit price before row discount, base currency Nullable Optional
unit_price_net_base_cur DecimalField Net unit price in base currency Nullable Optional
document_exrate FloatField From PO Header Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
line_total DecimalField Line total, document currency. Includes tax if order is tax inclusive Nullable Optional
line_tax_total DecimalField Line tax total, document currency Nullable Optional
line_total_base_cur DecimalField Line total, base currency. Includes tax if order is tax inclusive Nullable Optional
line_tax_total_base_cur DecimalField Line tax total, base currency Nullable Optional
line_tax_rule CharField Nullable Optional

purchase_order_payments

Payments relating to POs, including deposits and invoice payments

Model: Purchase_order_payments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
doc_guid CharField GUID of the Task, ie the Invoice or Credit Note Nullable Optional
doc_number CharField Document number of the Task, ie the Invoice or Credit Note Nullable Optional
payment_guid CharField Nullable Optional
payment_type CharField Prepayment,InvoicePayment,Refund Nullable Optional
payment_reference CharField Nullable Optional
payment_amount DecimalField Nullable Optional
payment_date DateField Payment date, no timezone Nullable Optional
payment_account CharField Nullable Optional
payment_currency CharField Nullable Optional
base_currency CharField Nullable Optional
payment_exrate FloatField Nullable Optional

purchases_fact

based on authorised PO invoice lines

Model: Purchases_fact

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header 		links to dear_po_header table
source_dear ForeignKey
FK to dear_metadata 		links to Dear company name
document_modified DateTimeField Nullable Optional
supplier_guid ForeignKey
FK to dear_supplier 		Nullable Optional
order_date DateField A Date field with no timezone, date when PO was created Nullable Optional
transaction_date DateField Invoice data, a Date field with no timezone Nullable Optional
inv_due_date DateField Invoice due date, a Date with no timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
comment CharField Nullable Optional
line_description CharField Nullable Optional
document_exrate FloatField Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
invoice_quantity FloatField qty associated with revenue, such as invoice or credit note Nullable Optional
movement_quantity FloatField quantity associated with stock movement, >0 for receipt Nullable Optional
transaction_type CharField Nullable Optional
global_purchase_status CharField Refer to Dear API documentation Nullable Optional
order_number CharField Nullable Optional
invoice_number CharField Nullable Optional
discount_percentage FloatField Percentage discount Nullable Optional
unit_before_discount DecimalField in document currency Nullable Optional
unit_net DecimalField Unit price with tax removed, even if tax inclusive, document currency Nullable Optional
unit_tax DecimalField unit tax, document currency Nullable Optional
warehouse CharField location from PO header Nullable Optional
account CharField Nullable Optional
cogs_account CharField Nullable Optional
attribute1 CharField Nullable Optional
attribute2 CharField Nullable Optional
attribute3 CharField Nullable Optional
attribute4 CharField Nullable Optional
attribute5 CharField Nullable Optional
attribute6 CharField Nullable Optional
attribute7 CharField Nullable Optional
attribute8 CharField Nullable Optional
attribute9 CharField Nullable Optional
attribute10 CharField Nullable Optional
year_month_currency CharField Used to find average monthly exrate Nullable Optional
line_invoice DecimalField Line total, document currency. Always without tax, even if the order is tax inclusive Nullable Optional
line_tax DecimalField Tax in the line, document currency Nullable Optional
line_invoice_base_currency DecimalField Line total, base currency. Always without tax, even if the order is tax inclusive Nullable Optional
line_tax_base_currency DecimalField Tax in the line, base currency Nullable Optional

purchases_lines_lifecycle

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

Model: Purchases_lines_lifecycle

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField Nullable Optional
ordered_qty FloatField In the case of a Blind Receipt, this is the invoice qty Nullable Optional
received_and_putaway_qty FloatField Total received or putaway Nullable Optional
not_yet_received_qty FloatField Qty which has not been received or putaway, that is, stock which has not arrived at all Nullable Optional
never_to_be_received_qty FloatField If an order is marked as Received, then this is the quantity ordered but which is now cancelled Nullable Optional
invoiced_qty FloatField Nullable Optional
credited_qty FloatField Nullable Optional
returned_qty FloatField Nullable Optional
avg_unit_price_base DecimalField Nullable Optional
ordered_basecur DecimalField Nullable Optional
received_and_putaway_basecur DecimalField Nullable Optional
not_yet_received_basecur DecimalField Nullable Optional
never_to_be_received_basecur DecimalField Nullable Optional
invoiced_basecur DecimalField Nullable Optional
credited_basecur DecimalField Nullable Optional
returned_basecur DecimalField Nullable Optional

sales_fact

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

Model: Sales_fact

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header 		Nullable Optional
source_dear ForeignKey
FK to dear_metadata
task_id ForeignKey
FK to dear_sales_invoice 		Invoice task id (guid unique to invoice header) Nullable Optional
document_modified DateTimeField Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
transaction_date DateField invoice date. A date with no timezone Nullable Optional
inv_due_date DateField Invoice due date, A date with no timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		Note: This field will show the line description for ad-hoc additional charges (which do not have a real product ID) Nullable Optional
line_description CharField Nullable Optional
revenue_quantity FloatField quantity which affects revenue accounts (invoice or credited amount) Nullable Optional
dropship_quantity FloatField this is included in revenue quantity, not in addition to it Nullable Optional
movement_quantity FloatField quantity which affects inventory accounts (shipments or returns) Nullable Optional
transaction_type CharField Nullable Optional
order_reference CharField sale order number Nullable Optional
global_sale_status CharField see Dear API reference Nullable Optional
document_reference CharField associated document number, e.g. invoice number Nullable Optional
document_currency CharField Nullable Optional
invoice_section CharField Whether this is in the product line part of the invoice or in additional charges Nullable Optional
unit_before_discount DecimalField unit price in document currency before discount, , always tax exclusive Nullable Optional
unit_net DecimalField unit price in document currency after discount, always tax exclusive Nullable Optional
tax_rule CharField inclusive or exclusive on the order, but tax is never included in revenue or unit prices Nullable Optional
unit_tax DecimalField Unit tax (document currency) Nullable Optional
warehouse CharField order header location, not the location of the pick Nullable Optional
sales_rep CharField sales rep, from the order header Nullable Optional
unit_cogs_average_cost DecimalField average cost price in base currency at the time of the sales order Nullable Optional
unit_cogs_actual DecimalField actual cost price in base currency. Only available if there is a shipment Nullable Optional
unit_cogs_best_available DecimalField is actual cost if available, else average cost Nullable Optional
ad_chg_spread DecimalField Document currency. Spreads invoice additional charges if they are < 0 to product lines to apportion discounts to product lines; this value is not part of margin fields in this table, you must manually use this value to adjust the line margin Nullable Optional
document_exrate FloatField Nullable Optional
revenue_account CharField Nullable Optional
cogs_account CharField Not available at present Nullable Optional
base_currency CharField Nullable Optional
attribute1 CharField Nullable Optional
attribute2 CharField Nullable Optional
attribute3 CharField Nullable Optional
attribute4 CharField Nullable Optional
attribute5 CharField Nullable Optional
attribute6 CharField Nullable Optional
attribute7 CharField Nullable Optional
attribute8 CharField Nullable Optional
attribute9 CharField Nullable Optional
attribute10 CharField Nullable Optional
shipping_company_name CharField Nullable Optional
shipping_addr1 CharField Nullable Optional
shipping_addr2 CharField Nullable Optional
shipping_postalcode CharField Nullable Optional
shipping_region CharField Nullable Optional
shipping_town CharField Nullable Optional
shipping_country CharField Nullable Optional
year_month_currency CharField Nullable Optional
line_revenue DecimalField Line revenue in document currency, without tax Nullable Optional
line_revenue_base_currency DecimalField Line revenue in base currency, without tax Nullable Optional
line_cogs_base_currency DecimalField Actual cost if available else average cost Nullable Optional
line_margin_base_currency DecimalField Line margin in base currency, based on actual cogs if available, else average cogs. Nullable Optional
line_estimated_invoice_margin DecimalField Deprecated field. Use line_margin_base_currency instead. Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional

sales_lines_lifecycle

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

Model: Sales_lines_lifecycle

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField Nullable Optional
quoted_qty FloatField Nullable Optional
ordered_qty FloatField Nullable Optional
shipped_qty FloatField dropships are included in this total Nullable Optional
shipped_with_tracking_qty FloatField Nullable Optional
in_fulfilment_qty FloatField The qty is in auth picking but not yet auth shipped Nullable Optional
not_yet_processed_qty FloatField Has not entered auth picking Nullable Optional
not_yet_shipped_qty FloatField not yet shipped or dropshipped Nullable Optional
never_to_be_fulfilled_qty FloatField If a back order is cancelled because the order is marked as Fulfilled Nullable Optional
invoiced_qty FloatField Nullable Optional
drop_ship_qty FloatField Nullable Optional
credited_qty FloatField Nullable Optional
returned_qty FloatField Not implemented yet Nullable Optional
avg_unit_price_base DecimalField Nullable Optional
quoted_basecur DecimalField Nullable Optional
ordered_basecur DecimalField Nullable Optional
shipped_basecur DecimalField valued at order price, not cogs, so that is is comparable Nullable Optional
shipped_with_tracking_basecur DecimalField Nullable Optional
in_fulfilment_basecur DecimalField Nullable Optional
not_yet_processed_basecur DecimalField quantity which has not even started fulfilment Nullable Optional
not_yet_shipped_basecur DecimalField The counts drop-shipped qty as shipped Nullable Optional
never_to_be_fulfilled_basecur DecimalField Will not be fulfilled as the order is marked as closed Nullable Optional
invoiced_basecur DecimalField Nullable Optional
drop_ship_basecur DecimalField Nullable Optional
actual_cogs_per_unit DecimalField Nullable Optional
avg_cost_per_unit DecimalField At the time this row was last updated: this nbr can change Nullable Optional
unit_cogs_best_available DecimalField Actual COGS if available, otherwise average cost Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional
credited_basecur DecimalField Nullable Optional
returned_basecur DecimalField Not implemented yet Nullable Optional

sales_order_inventory_transactions

Inventory movements by date and value associated with the sales order, for detailed revenue and cogs analysis. Covers orders, shipments, credit notes and restocks

Model: Sales_order_inventory_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField sale order number Nullable Optional
document_reference CharField invoice or credit note number Nullable Optional
source_dear ForeignKey
FK to dear_metadata
transaction_type CharField Nullable Optional
transaction_date DateField Date, No timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
account CharField Nullable Optional
quantity FloatField Nullable Optional
signed_revenue_quantity FloatField positive means invoice, negative is a credit, Nullable Optional
signed_inventory_quantity FloatField 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. Nullable Optional
unit_value_base DecimalField Nullable Optional
rev_unit_value_base DecimalField The unit cost if this transaction affects a revenue-style/AR account Nullable Optional
cogs_unit_value_base DecimalField The unit costs if this transaction affects the value of inventory/cogs Nullable Optional
rev_line_value_base DecimalField The line cost if this transaction affects a revenue-style/AR account Nullable Optional
cogs_line_value_base DecimalField The line costs if this transaction affects the value of inventory/cogs Nullable Optional

sales_order_lines

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

Model: Sales_order_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header 		Nullable
order_reference CharField Nullable Optional
document_exrate FloatField From PO Header Nullable Optional
document_modified DateTimeField Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
description CharField Nullable Optional
order_quantity FloatField Nullable Optional
comment CharField Nullable Optional
backorder_quantity FloatField Nullable Optional
line_average_cost DecimalField Average cost (doc currency) used by Dear to estimate margin before picking Nullable Optional
unit_price_before_discount DecimalField Nullable Optional
unit_price_net DecimalField Nullable Optional
unit_actual_cogs_base_cur DecimalField Actual cogs (base cur) based on shipment, averaged across all shipments, if no shipments, then 0 Nullable Optional
unit_average_cost_base_cur DecimalField Average cost (base cur) Nullable Optional
unit_cogs_best_available DecimalField Actual COGS if available, otherwise average cost Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional
line_total DecimalField Nullable Optional
line_tax_total DecimalField Nullable Optional
unit_price_before_discount_base_cur DecimalField Nullable Optional
unit_price_net_base_cur DecimalField Nullable Optional
line_total_base_cur DecimalField Nullable Optional
line_tax_total_base_cur DecimalField Nullable Optional
line_estimated_order_margin_base_cur DecimalField Nullable Optional
line_tax_rule CharField Nullable Optional
dropship BooleanField

sales_order_payments

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

Model: Sales_order_payments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Nullable Optional
doc_guid CharField GUID of the Task, ie the Invoice or Credit Note Nullable Optional
doc_number CharField Document number of the Task, ie the Invoice or Credit Note Nullable Optional
payment_guid CharField Nullable Optional
payment_type CharField Prepayment,InvoicePayment,Refund Nullable Optional
payment_reference CharField Nullable Optional
payment_amount DecimalField Always positive even for refunds Nullable Optional
payment_amount_signed DecimalField negative for refunds Nullable Optional
payment_date DateField No timezone Nullable Optional
payment_account CharField Nullable Optional
base_currency CharField Nullable Optional
payment_currency CharField Nullable Optional
payment_exrate FloatField Nullable Optional

sales_shipments

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

Model: Sales_shipments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
shipment_date DateField DateTime, no timezone Nullable Optional
shipment_carrier CharField The carrier used on the shipment line Nullable Optional
shipping_notes CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
warehouse CharField Nullable Optional
warehouse_guid CharField Nullable Optional
fulfilment_nbr IntegerField Nullable Optional
box CharField Nullable Optional
shipment_quantity FloatField Nullable Optional
cogs DecimalField Nullable Optional
cogs_date DateField Date field, no timezone Nullable Optional
shipped_value_base DecimalField Nullable Optional
task_id ForeignKey
FK to dear_sales_invoice 		Invoice task id (guid unique to invoice header) Nullable Optional
base_currency CharField Nullable Optional

shipping_subscriptions

OceanInsights Project44 data for containers on ships

Model: Shipping_Subscriptions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
subscription_id CharField Unique
origin_name CharField Nullable Optional
destination_name CharField Nullable Optional
origin_latitude FloatField Nullable Optional
origin_longitude FloatField Nullable Optional
destination_latitude FloatField Nullable Optional
destination_longitude FloatField Nullable Optional
latitude FloatField Nullable Optional
longitude FloatField Nullable Optional
eta DateTimeField Nullable Optional
url CharField Nullable Optional
container_details CharField Nullable Optional
status CharField Nullable Optional
shipping_reference CharField Nullable Optional
subscription_name CharField Nullable Optional

shippit_transactions

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

Model: Shippit_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid IntegerField Nullable Optional
store CharField Nullable Optional
shippit_reference CharField Nullable Optional
receiver_name CharField Nullable Optional
recipient_contact_number CharField Nullable Optional
recipient_email CharField Nullable Optional
carrier CharField Nullable Optional
manifest_id CharField Nullable Optional
carrier_job_id CharField Nullable Optional
retailer_invoice CharField Nullable Optional
destination_address CharField Nullable Optional
destination_suburb CharField Nullable Optional
destination_postcode CharField Nullable Optional
destination_state CharField Nullable Optional
destination_country CharField Nullable Optional
destination_latitude FloatField Nullable Optional
destination_longitude FloatField Nullable Optional
special_instructions CharField Nullable Optional
atl CharField Nullable Optional
shipping_price DecimalField Nullable Optional
billed_by CharField Nullable Optional
count FloatField Nullable Optional
length FloatField Nullable Optional
width FloatField Nullable Optional
depth FloatField Nullable Optional
weight FloatField Nullable Optional
current_state CharField Nullable Optional
delivery_country_code CharField Nullable Optional
service_level CharField Nullable Optional
last_update DateTimeField Nullable Optional
order_placed DateTimeField Nullable Optional
despatch_in_progress DateTimeField Nullable Optional
ready_for_pickup DateTimeField Nullable Optional
in_transit DateTimeField Nullable Optional
with_driver DateTimeField Nullable Optional
delivery_attempted DateTimeField Nullable Optional
awaiting_collection DateTimeField Nullable Optional
completed DateTimeField Nullable Optional
expected_delivery_date DateTimeField Nullable Optional
carrier_quotes CharField Nullable Optional
rating CharField Nullable Optional
comment CharField Nullable Optional
case CharField Nullable Optional

table_update_status

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

Model: Table_update_status

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear CharField Nullable Optional
zoho_table_name CharField Nullable Optional
comment CharField Nullable Optional
time_of_comment_utc DateTimeField Nullable Optional
schema_version IntegerField Reserved for future use Nullable Optional

threepl_stock_analysis

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

Model: ThreePL_stock_analysis

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
three_pl_name CharField Nullable Optional
dear_location_guid ForeignKey
FK to dear_location
threepl_location_name CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
stock_date DateField This is the date when the report is run. Nullable Optional
dear_timestamp DateTimeField time when the Dear stock level was recorded Nullable Optional
dear_stock_oh FloatField Dear Stock On Hand Nullable Optional
threepl_timestamp DateTimeField time when the 3PL stock level was recorded Nullable Optional
threepl_stock_oh FloatField 3PL Stock On Hand Nullable Optional
dear_available FloatField Dear Available stock Nullable Optional
threepl_available FloatField 3PL Available stock Nullable Optional
comment CharField Nullable Optional
Back to Top