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_order_inventory_transactions
Table name: omni_contacts
Table description: Contacts from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
contact_type | PLAIN | Type of contact (Customer/Supplier) |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Contact ID |
sales_person_id | NUMBER | |
account_number | PLAIN | |
billing_id | NUMBER | |
billing_company | PLAIN | |
accounts_first_name | PLAIN | |
accounts_last_name | PLAIN | |
billing_email | PLAIN | |
accounts_phone | PLAIN | |
billing_cost_center | PLAIN | |
cost_center | PLAIN | |
price_column | PLAIN | |
percentage_off | CURRENCY | |
payment_terms | PLAIN | |
tax_status | PLAIN | |
tax_number | PLAIN | |
credit_limit | CURRENCY | |
balance_owing | CURRENCY | Read-only from API |
on_hold | BOOLEAN | |
group | PLAIN | |
sub_group | PLAIN | |
stages | PLAIN | |
created_date | DATE | |
modified_date | DATE | |
is_active | BOOLEAN | |
company | PLAIN | |
first_name | PLAIN | |
last_name | PLAIN | |
job_title | PLAIN | |
PLAIN | ||
website | URL | |
phone | PLAIN | |
fax | PLAIN | |
mobile | PLAIN | |
address1 | PLAIN | |
address2 | PLAIN | |
city | PLAIN | |
state | PLAIN | |
post_code | PLAIN | |
country | PLAIN | |
postal_address1 | PLAIN | |
postal_address2 | PLAIN | |
postal_city | PLAIN | |
postal_state | PLAIN | |
postal_post_code | PLAIN | |
postal_country | PLAIN | |
notes | MULTI_LINE | |
integration_ref | PLAIN |
Table name: omni_branches
Table description: Branches from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Branch ID |
created_date | DATE | |
modified_date | DATE | |
is_active | BOOLEAN | |
company | PLAIN | |
first_name | PLAIN | |
last_name | PLAIN | |
job_title | PLAIN | |
PLAIN | ||
website | URL | |
phone | PLAIN | |
fax | PLAIN | |
mobile | PLAIN | |
address1 | PLAIN | |
address2 | PLAIN | |
city | PLAIN | |
state | PLAIN | |
post_code | PLAIN | |
country | PLAIN | |
postal_address1 | PLAIN | |
postal_address2 | PLAIN | |
postal_city | PLAIN | |
postal_state | PLAIN | |
postal_post_code | PLAIN | |
postal_country | PLAIN | |
notes | MULTI_LINE | |
integration_ref | PLAIN | |
branch_type | PLAIN | |
stock_control_options | PLAIN | |
tax_status | PLAIN | |
account_number | PLAIN | |
branch_locations | MULTI_LINE | |
custom_fields | MULTI_LINE | |
secondary_contacts | MULTI_LINE |
Table name: omni_product_categories
Table description: Product Categories from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Category ID |
parent_id | NUMBER | ID of the parent category |
is_active | BOOLEAN | |
sort | NUMBER | Sort order of the category |
name | PLAIN | |
description | MULTI_LINE | |
image | MULTI_LINE |
Table name: omni_products
Table description: Products from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Product ID |
status | PLAIN | |
created_date | DATE | |
modified_date | DATE | |
style_code | PLAIN | |
name | PLAIN | |
description | MULTI_LINE | |
tags | MULTI_LINE | |
pdf_upload | PLAIN | |
pdf_description | PLAIN | |
supplier_id | NUMBER | |
brand | PLAIN | |
category | PLAIN | |
sub_category | PLAIN | |
channels | PLAIN | |
weight | CURRENCY | |
height | CURRENCY | |
width | CURRENCY | |
length | CURRENCY | |
volume | CURRENCY | |
stock_control | NUMBER | Possible values: Undefined (0), Batch (1), Machine (2), Serial (3), Labour (4), FIFO (5). |
order_type | PLAIN | |
product_type | PLAIN | |
product_subtype | PLAIN | |
project_name | PLAIN | |
option_label1 | PLAIN | |
option_label2 | PLAIN | |
option_label3 | PLAIN | |
sales_account | PLAIN | |
purchases_account | PLAIN | |
import_customs_duty | PLAIN | |
size_range_id | NUMBER | |
images | MULTI_LINE | |
category_id_array | MULTI_LINE | |
custom_fields | MULTI_LINE | |
product_options | MULTI_LINE |
Table name: omni_sales_orders
Table description: Sales Orders from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Sales Order ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
member_id | NUMBER | |
first_name | PLAIN | |
last_name | PLAIN | |
company | PLAIN | |
PLAIN | ||
phone | PLAIN | |
mobile | PLAIN | |
fax | PLAIN | |
delivery_first_name | PLAIN | |
delivery_last_name | PLAIN | |
delivery_company | PLAIN | |
delivery_address1 | PLAIN | |
delivery_address2 | PLAIN | |
delivery_city | PLAIN | |
delivery_state | PLAIN | |
delivery_postal_code | PLAIN | |
delivery_country | PLAIN | |
billing_first_name | PLAIN | |
billing_last_name | PLAIN | |
billing_company | PLAIN | |
billing_address1 | PLAIN | |
billing_address2 | PLAIN | |
billing_city | PLAIN | |
billing_postal_code | PLAIN | |
billing_state | PLAIN | |
billing_country | PLAIN | |
branch_id | NUMBER | |
branch_email | PLAIN | |
project_name | PLAIN | |
tracking_code | PLAIN | |
internal_comments | MULTI_LINE | |
product_total | CURRENCY | |
freight_total | CURRENCY | |
freight_description | PLAIN | |
surcharge | CURRENCY | |
surcharge_description | PLAIN | |
discount_total | CURRENCY | |
discount_description | PLAIN | |
total | CURRENCY | |
currency_code | PLAIN | |
currency_rate | CURRENCY | |
currency_symbol | PLAIN | |
tax_status | PLAIN | |
tax_rate | CURRENCY | |
source | PLAIN | |
custom_fields | MULTI_LINE | |
is_void | BOOLEAN | |
accounting_attributes | MULTI_LINE | |
member_email | PLAIN | |
member_cost_center | PLAIN | |
member_alternative_tax_rate | PLAIN | |
cost_center | PLAIN | |
alternative_tax_rate | PLAIN | |
estimated_delivery_date | DATE | |
sales_person_id | NUMBER | |
payment_terms | PLAIN | |
customer_order_no | PLAIN | |
voucher_code | PLAIN | |
delivery_instructions | MULTI_LINE | |
cancellation_date | DATE | |
modified_cogs_date | DATE | |
status | PLAIN | Status of the order. Can be Draft, Approved, or Void. |
stage | PLAIN | |
invoice_date | DATE | |
invoice_number | NUMBER | |
dispatched_date | DATE | |
logistics_carrier | PLAIN | |
logistics_status | NUMBER | |
edi_status | NUMBER | |
distribution_branch_id | NUMBER | |
department_number | PLAIN | |
store_location_number | PLAIN | |
distribution_center | PLAIN | |
line_items | MULTI_LINE |
Table name: omni_sales_order_lines
Table description: Sales Order Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Sales Order Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Sales Order |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
style_code | PLAIN | |
barcode | PLAIN | |
size_codes | PLAIN | |
line_comments | MULTI_LINE | |
unit_cost | CURRENCY | |
unit_price | CURRENCY | |
uom_price | CURRENCY | |
net_line_total | CURRENCY | qty*unit_price less discount |
net_unit_price | CURRENCY | (qty*unit_price - discount)/qty |
discount | CURRENCY | Total line item discount |
uom_qty_ordered | DECIMAL_NUMBER | |
uom_qty_shipped | DECIMAL_NUMBER | |
uom_size | CURRENCY | |
qty_shipped | DECIMAL_NUMBER | |
holding_qty | DECIMAL_NUMBER | |
account_code | PLAIN | |
stock_control | PLAIN | |
stock_movements | MULTI_LINE | |
sizes | MULTI_LINE |
Table name: omni_payments
Table description: Payments from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Payment ID |
created_date | DATE | |
modified_date | DATE | |
payment_date | DATE | |
amount | CURRENCY | |
method | PLAIN | |
is_authorized | BOOLEAN | |
transaction_ref | PLAIN | |
comments | PLAIN | |
order_id | NUMBER | |
order_ref | PLAIN | |
payment_imported_ref | PLAIN | |
batch_reference | PLAIN | |
reconcile_date | DATE | |
branch | NUMBER | The branch associated with the payment |
order_type | PLAIN | Type of order (e.g., SalesOrder, PurchaseOrder, Undefined) |
Table name: omni_quotes
Table description: Quotes from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Quote ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
member_id | NUMBER | |
first_name | PLAIN | |
last_name | PLAIN | |
company | PLAIN | |
PLAIN | ||
phone | PLAIN | |
mobile | PLAIN | |
fax | PLAIN | |
delivery_first_name | PLAIN | |
delivery_last_name | PLAIN | |
delivery_company | PLAIN | |
delivery_address1 | PLAIN | |
delivery_address2 | PLAIN | |
delivery_city | PLAIN | |
delivery_state | PLAIN | |
delivery_postal_code | PLAIN | |
delivery_country | PLAIN | |
billing_first_name | PLAIN | |
billing_last_name | PLAIN | |
billing_company | PLAIN | |
billing_address1 | PLAIN | |
billing_address2 | PLAIN | |
billing_city | PLAIN | |
billing_postal_code | PLAIN | |
billing_state | PLAIN | |
billing_country | PLAIN | |
branch_id | NUMBER | |
branch_email | PLAIN | |
project_name | PLAIN | |
tracking_code | PLAIN | |
internal_comments | MULTI_LINE | |
product_total | CURRENCY | |
freight_total | CURRENCY | |
freight_description | PLAIN | |
surcharge | CURRENCY | |
surcharge_description | PLAIN | |
discount_total | CURRENCY | |
discount_description | PLAIN | |
total | CURRENCY | |
currency_code | PLAIN | |
currency_rate | CURRENCY | |
currency_symbol | PLAIN | |
tax_status | PLAIN | |
tax_rate | CURRENCY | |
source | PLAIN | |
custom_fields | MULTI_LINE | |
member_email | PLAIN | |
member_cost_center | PLAIN | |
member_alternative_tax_rate | PLAIN | |
cost_center | PLAIN | |
alternative_tax_rate | PLAIN | |
estimated_delivery_date | DATE | |
sales_person_id | NUMBER | |
payment_terms | PLAIN | |
customer_order_no | PLAIN | |
voucher_code | PLAIN | |
delivery_instructions | MULTI_LINE | |
cancellation_date | DATE | |
modified_cogs_date | DATE | |
status | PLAIN | Status of the quote. Can be Draft, Approved, or Void. |
stage | PLAIN | |
probability | CURRENCY | |
expected_order_date | DATE | |
acceptance_date | DATE | |
line_items | MULTI_LINE |
Table name: omni_quote_lines
Table description: Quote Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Quote Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Quote |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
style_code | PLAIN | |
barcode | PLAIN | |
size_codes | PLAIN | |
line_comments | MULTI_LINE | |
unit_cost | CURRENCY | |
unit_price | CURRENCY | |
discount | CURRENCY | |
qty_shipped | DECIMAL_NUMBER | |
holding_qty | DECIMAL_NUMBER |
Table name: omni_purchase_orders
Table description: Purchase Orders from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Purchase Order ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
member_id | NUMBER | The Supplier ID |
first_name | PLAIN | |
last_name | PLAIN | |
company | PLAIN | |
PLAIN | ||
phone | PLAIN | |
mobile | PLAIN | |
fax | PLAIN | |
delivery_first_name | PLAIN | |
delivery_last_name | PLAIN | |
delivery_company | PLAIN | |
delivery_address1 | PLAIN | |
delivery_address2 | PLAIN | |
delivery_city | PLAIN | |
delivery_state | PLAIN | |
delivery_postal_code | PLAIN | |
delivery_country | PLAIN | |
billing_first_name | PLAIN | |
billing_last_name | PLAIN | |
billing_company | PLAIN | |
billing_address1 | PLAIN | |
billing_address2 | PLAIN | |
billing_city | PLAIN | |
billing_postal_code | PLAIN | |
billing_state | PLAIN | |
billing_country | PLAIN | |
branch_id | NUMBER | |
branch_email | PLAIN | |
project_name | PLAIN | |
tracking_code | PLAIN | |
internal_comments | MULTI_LINE | |
product_total | CURRENCY | |
freight_total | CURRENCY | |
freight_description | PLAIN | |
surcharge | CURRENCY | |
surcharge_description | PLAIN | |
discount_total | CURRENCY | |
discount_description | PLAIN | |
total | CURRENCY | |
currency_code | PLAIN | |
currency_rate | CURRENCY | |
currency_symbol | PLAIN | |
tax_status | PLAIN | |
tax_rate | CURRENCY | |
source | PLAIN | |
custom_fields | MULTI_LINE | |
is_void | BOOLEAN | |
accounting_attributes | MULTI_LINE | |
member_email | PLAIN | |
member_cost_center | PLAIN | |
member_alternative_tax_rate | PLAIN | |
cost_center | PLAIN | |
alternative_tax_rate | PLAIN | |
estimated_delivery_date | DATE | |
sales_person_id | NUMBER | |
payment_terms | PLAIN | |
customer_order_no | PLAIN | |
voucher_code | PLAIN | |
delivery_instructions | MULTI_LINE | |
cancellation_date | DATE | |
modified_cogs_date | DATE | |
status | PLAIN | Status of the order. Can be Draft, Approved, or Void. |
stage | PLAIN | |
supplier_invoice_reference | PLAIN | |
supplier_acceptance_date | DATE | |
port | PLAIN | |
estimated_arrival_date | DATE | |
fully_received_date | DATE | |
invoice_date | DATE | |
logistics_carrier | PLAIN | |
logistics_status | NUMBER | |
line_items | MULTI_LINE |
Table name: omni_purchase_order_lines
Table description: Purchase Order Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni PO Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Purchase Order |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
style_code | PLAIN | |
barcode | PLAIN | |
size_codes | PLAIN | |
line_comments | MULTI_LINE | |
unit_price | CURRENCY | |
discount | CURRENCY | |
qty_shipped | DECIMAL_NUMBER | |
holding_qty | DECIMAL_NUMBER | |
account_code | PLAIN |
Table name: omni_stock
Table description: Stock Levels from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
product | NUMBER | The product associated with this stock level |
product_option_id | NUMBER | The Product Option ID |
modified_date | DATE | |
style_code | PLAIN | |
code | PLAIN | The unique code, i.e., SKU |
barcode | PLAIN | |
branch | NUMBER | The branch where this stock is located |
branch_name | PLAIN | |
product_name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
size | PLAIN | |
available | CURRENCY | |
stock_on_hand | CURRENCY | |
open_sales | CURRENCY | |
incoming | CURRENCY | |
virtual | CURRENCY | |
holding | CURRENCY |
Table name: omni_branch_transfers
Table description: Branch Transfers from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Branch Transfer ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
approved_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
branch_email | PLAIN | |
delivery_instructions | MULTI_LINE | |
internal_comments | MULTI_LINE | |
product_total | CURRENCY | |
first_name | PLAIN | |
last_name | PLAIN | |
company | PLAIN | |
PLAIN | ||
phone | PLAIN | |
mobile | PLAIN | |
fax | PLAIN | |
project_name | PLAIN | |
tracking_code | PLAIN | |
source | PLAIN | |
source_branch | NUMBER | The source branch of the transfer |
destination_branch | NUMBER | The destination branch of the transfer |
approval_date | DATE | |
dispatched_date | DATE | |
received_date | DATE | |
stage | PLAIN | |
custom_fields | MULTI_LINE | |
line_items | MULTI_LINE |
Table name: omni_branch_transfer_lines
Table description: Branch Transfer Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Branch Transfer Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Branch Transfer |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
qty_transferred | DECIMAL_NUMBER | |
unit_cost | CURRENCY | |
sizes | MULTI_LINE |
Table name: omni_adjustments
Table description: Stock Adjustments from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Adjustment ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
branch | NUMBER | The branch where the adjustment occurred |
completed_date | DATE | |
adjust_in_accounting_system | DATE | |
adjustment_reason | MULTI_LINE | |
alternative_account_code | PLAIN | |
product_total | CURRENCY | |
source | PLAIN | |
line_items | MULTI_LINE |
Table name: omni_adjustment_lines
Table description: Stock Adjustment Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Adjustment Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Adjustment |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
qty_adjusted | DECIMAL_NUMBER | |
holding_qty | DECIMAL_NUMBER | |
account | PLAIN | |
unit_cost | CURRENCY |
Table name: omni_credit_notes
Table description: Credit Notes from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Credit Note ID |
created_date | DATE | |
modified_date | DATE | |
created_by | NUMBER | |
processed_by | NUMBER | |
is_approved | BOOLEAN | |
reference | PLAIN | |
member_id | NUMBER | |
first_name | PLAIN | |
last_name | PLAIN | |
company | PLAIN | |
PLAIN | ||
phone | PLAIN | |
mobile | PLAIN | |
fax | PLAIN | |
delivery_first_name | PLAIN | |
delivery_last_name | PLAIN | |
delivery_company | PLAIN | |
delivery_address1 | PLAIN | |
delivery_address2 | PLAIN | |
delivery_city | PLAIN | |
delivery_state | PLAIN | |
delivery_postal_code | PLAIN | |
delivery_country | PLAIN | |
billing_first_name | PLAIN | |
billing_last_name | PLAIN | |
billing_company | PLAIN | |
billing_address1 | PLAIN | |
billing_address2 | PLAIN | |
billing_city | PLAIN | |
billing_postal_code | PLAIN | |
billing_state | PLAIN | |
billing_country | PLAIN | |
branch_id | NUMBER | |
branch_email | PLAIN | |
project_name | PLAIN | |
tracking_code | PLAIN | |
internal_comments | MULTI_LINE | |
product_total | CURRENCY | |
freight_total | CURRENCY | |
freight_description | PLAIN | |
surcharge | CURRENCY | |
surcharge_description | PLAIN | |
discount_total | CURRENCY | |
discount_description | PLAIN | |
total | CURRENCY | |
currency_code | PLAIN | |
currency_rate | CURRENCY | |
currency_symbol | PLAIN | |
tax_status | PLAIN | |
tax_rate | CURRENCY | |
source | PLAIN | |
custom_fields | MULTI_LINE | |
status | PLAIN | Status of the credit note. Can be Draft, Approved, or Void. |
member_email | PLAIN | |
sales_reference | PLAIN | |
credit_note_number | NUMBER | |
credit_note_date | DATE | |
completed_date | DATE | |
customer_report | MULTI_LINE | |
alternative_account_code | PLAIN | |
sales_person_id | NUMBER | |
logistics_carrier | PLAIN | |
logistics_status | NUMBER | |
accounting_attributes | MULTI_LINE | |
modified_cogs_date | DATE | |
line_items | MULTI_LINE |
Table name: omni_credit_note_lines
Table description: Credit Note Lines from Cin7 Omni
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_omni | PLAIN | |
api_id | NUMBER | Cin7 Omni Credit Note Line ID |
created_date | DATE | |
transaction | NUMBER | The parent Credit Note |
parent_id | NUMBER | |
product | NUMBER | The product on this line |
product_option_id | NUMBER | |
integration_ref | PLAIN | |
sort | NUMBER | |
code | PLAIN | |
name | PLAIN | |
option1 | PLAIN | |
option2 | PLAIN | |
option3 | PLAIN | |
qty | DECIMAL_NUMBER | |
style_code | PLAIN | |
barcode | PLAIN | |
size_codes | PLAIN | |
line_comments | MULTI_LINE | |
unit_cost | CURRENCY | |
unit_price | CURRENCY | |
discount | CURRENCY | |
qty_shipped | DECIMAL_NUMBER | |
account_code | PLAIN |
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_number | PLAIN | |
source_dear | PLAIN | |
line_type | PLAIN | Product type, one of Stock, Non Inventory or Service |
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 |
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_number | PLAIN | 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 |
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_number | 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 | |
tracking_nbr | PLAIN | tracking number |
tracking_url | URL | tracking url |
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_number | 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 | Type of sale: Simple Sale or Advanced Sale, Service Sale |
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 | |
financial_hash | PLAIN | hash value of financial transactions |
Table name: dear_sales_invoice
Table description: Sales Invoices and Credit notes header: invoices and credit notes.
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
task_id | PLAIN | 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 |
source_dear | PLAIN | |
order_guid | PLAIN | |
invoice_number | PLAIN | invoice or credit note number |
sales_order_number | PLAIN | here as a convenience. To join to sales order header, use order_guid |
memo | PLAIN | Invoice memo |
invoice_status | PLAIN | Invoice status, one of NOT AVAILABLE,DRAFT,AUTHORISED,VOIDED,PAID see https://dearinventory.docs.apiary.io/#reference/sale |
invoice_date | DATE | Serves as the transaction date for the invoice or credit note. There is no timezone for this date |
invoice_due_date | DATE | There is no timezone for this date |
document_modified | DATE | |
customer_guid | PLAIN | |
document_currency | PLAIN | |
document_exrate | DECIMAL_NUMBER | |
base_currency | PLAIN | |
linked_fulfilments | PLAIN | only present if this invoice was linked to one or more fulfilments |
Table name: sales_fact
Table description: Based on authorised invoice lines; the revenue figures in this table should agree with the data in Xero or QBO and are the same as the Cin7 Core Report 'Sale Invoices & Credit Notes' (they should be the same to the cent). Margins in this table are for management accounting purposes: Actual COGS from shipments is used if available, but if it not available (a line on the invoice which has not yet shipped), the margin figure falls back to average cost or supplier price as a place holder margin. Each row shows the COGS source.
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_number | PLAIN | sale order number |
global_sale_status | PLAIN | see Dear API reference |
document_reference | PLAIN | associated document number, e.g. invoice number |
document_currency | PLAIN | |
invoice_section | PLAIN | Product or Additional Charge: Whether this is in the product line part of the invoice or in additional charges |
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 |
ad_chg_spread | CURRENCY | 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 |
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 | CURRENCY | Line revenue in document currency, without tax |
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 | Deprecated field. Use line_margin_base_currency instead. |
cogs_source | PLAIN | Source of margin: AverageCost or Actual |
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_number | 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 |
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_number | 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 |
Table name: dear_customer
Table description: dear customer table
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
document_modified | DATE | |
customer_guid | PLAIN | |
customer_name | MULTI_LINE | |
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 |
Table name: dear_customer_addresses
Table description: Addresses associated with Customers
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 |
Table name: dear_customer_contacts
Table description: Contacts associated with Customers
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 | |
PLAIN | ||
website | PLAIN | |
comment | PLAIN | |
default | BOOLEAN | |
include_in_email | BOOLEAN |
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 |
Table name: dear_customer_tag
Table description: Tags associated with Customers
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
document_modified | DATE | |
customer_guid | PLAIN | |
source_dear | PLAIN | |
tag_value | PLAIN |
Table name: dear_product
Table description: Product details from Cin7 Core (Dear)
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
document_modified | DATE | |
source_dear | PLAIN | |
product_guid | 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 | |
drop_ship_mode | PLAIN | No Drop Ship, Optional Drop Ship, Always Drop Ship |
sellable | BOOLEAN | |
hscode | PLAIN | |
country_of_origin | PLAIN | |
country_of_origin_code | PLAIN | |
bom_type | PLAIN | read only. Assembly, Production, Make to Order, None |
auto_assembly | BOOLEAN | |
auto_disassembly | BOOLEAN | |
qty_to_produce | CURRENCY | |
cogs_account | PLAIN | |
revenue_account | PLAIN | |
expense_account | PLAIN | |
inventory_account | PLAIN |
Table name: dear_supplier_product
Table description: Products linked to Suppliers
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_dear | PLAIN | |
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 |
Table name: dear_product_family
Table description: Product Family details
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 |
Table name: dear_product_custom_prices
Table description: Custom prices for products per customer.
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_dear | PLAIN | |
product_guid | PLAIN | |
customer_guid | PLAIN | |
price | CURRENCY | The custom price |
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 |
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 |
Table name: dear_product_tag
Table description: Tags associated with Products
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
document_modified | DATE | |
product_guid | PLAIN | |
source_dear | PLAIN | |
tag_value | PLAIN |
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 |
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 |
Table name: dear_stock_adjustment_header
Table description: Stock Adjustment header
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_dear | PLAIN | |
task_guid | PLAIN | |
stocktake_number | PLAIN | |
status | PLAIN | |
effective_date | DATE | |
reference | PLAIN | |
document_modified | DATE | |
account | PLAIN |
Table name: dear_stock_adjustment_lines
Table description: Stock Adjustment Lines
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_dear | PLAIN | |
task_guid | PLAIN | |
product_guid | PLAIN | |
line_type | PLAIN | new or existing |
quantity_on_hand | DECIMAL_NUMBER | before transfer |
quantity_available | DECIMAL_NUMBER | before transfer |
adjustment_quantity | DECIMAL_NUMBER | before transfer |
unit_cost | CURRENCY | only used in New Stock Lines |
location | PLAIN | |
location_bin | PLAIN | |
batch_sn | PLAIN | |
expiry_date | DATE | |
comments | PLAIN | |
document_modified | DATE |
Table name: dear_supplier
Table description: Supplier details from Cin7 Core (Dear)
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 |
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 | |
order_type | PLAIN | one of Simple Purchase, Advanced Purchase , Service Purchase |
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 |
Table name: dear_financial_transactions
Table description: Financial transactions from Cin7 Core (Dear). These are not auto updated.
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
id_guid | PLAIN | |
task_guid | PLAIN | |
source_dear | PLAIN | |
reference | PLAIN | |
debit_account_code | PLAIN | |
credit_account_code | PLAIN | |
amount | DECIMAL_NUMBER | |
original_amount | DECIMAL_NUMBER | |
currency_rate | DECIMAL_NUMBER | |
effective_date | DATE | Payment date, no timezone |
last_modified | DATE | This is not from Dear, it is a experimental helper field for sync to analytics backend. |
transaction | PLAIN | |
type | PLAIN | Transaction Type. Available values are Purchase,Sale,MoneySpend,MoneyReceive,BankTransfer,ExpenseClaimTask,FinishedGoods,InventoryWriteOff,StockTake,StockAdjustment,Journal,Disassembly,Depreciation |
Table name: dear_finished_goods_header
Table description: Finished Goods Header (Not fully implemented)
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
source_dear | PLAIN |
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_number | PLAIN | |
source_dear | PLAIN | |
line_type | PLAIN | ITEM or ADDITIONAL_CHARGE |
comment | PLAIN | |
product_guid | PLAIN | will be empty for adhoc service items |
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 |
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 | For adhoc service lines, there is no product and this is empty |
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 | |
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 |
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_number | 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 |
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_number | 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 |
Table name: avg_ex_rate
Table description: Monthly average exchange rates from the ECB.
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
year_month_currency | PLAIN | |
in_reporting_currency | CURRENCY | Historical monthly average exchange rate from ECB, multiple the from-currency by this to get to reporting currency |
value_date | DATE | Always 15th of the month |
from_currency | PLAIN | |
to_currency | PLAIN |
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 |
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 |
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) |
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 |
Table name: shipping_subscriptions
Table description: OceanInsights Project44 data for containers on ships
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
subscription_id | PLAIN | |
origin_name | PLAIN | |
destination_name | PLAIN | |
origin_latitude | DECIMAL_NUMBER | |
origin_longitude | DECIMAL_NUMBER | |
destination_latitude | DECIMAL_NUMBER | |
destination_longitude | DECIMAL_NUMBER | |
latitude | DECIMAL_NUMBER | |
longitude | DECIMAL_NUMBER | |
eta | DATE | |
url | PLAIN | |
container_details | PLAIN | |
status | PLAIN | |
shipping_reference | PLAIN | |
subscription_name | PLAIN |
Table name: mainfreight_events
Table description: Mainfreight events
Column Name | Datatype | Description |
---|---|---|
id | AUTO_NUMBER | |
unique_key | PLAIN | The ID field |
event_type | PLAIN | |
event_datetime | DATE | |
last_modified | DATE | |
order_number | PLAIN | Dear Sales Number |
fulfilment_number | NUMBER | |
event_text | MULTI_LINE | |
source_dear | PLAIN |