Invoice Payments Table

Dataset ID: aa06f7c0-1258-4b10-9e4e-83f25234c9c7

Unique Composite Key(s): storeid, invoice_id, product_id, cost_id, accepted_date

Datetime Dataset Last Updated: last_retrieved_timestamp

Incremental Load Logic: Use the column accepted_date as data appends per new invoice line item

Sample Queries based on use case:

{"sql": "SELECT * FROM table ORDER BY storeid, accepted_date, invoice_id, cost_id"}

{"sql": "SELECT storeid, invoice_id, cost_id, product_id, invoice_number, accepted_date, productname, producttype, qty, total_cost, sum(payment_amount) as total_payments FROM dataset GROUP BY storeid, invoice_id, cost_id, product_id, invoice_number, accepted_date, productname, producttype, qty, total_cost ORDER BY storeid, accepted_date, invoice_id, cost_id"}

{"sql": "SELECT storeid, invoice_id, invoice_number, accepted_date, total_cost, payment_date, payment_amount, payment_user, payment_notes FROM dataset ORDER BY storeid, accepted_date, invoice_id, payment_date"}

*Pull Previous Day - Data-Raw Append Example:

{"sql": "SELECT * FROM table WHERE date(accepted_date) = date(date_add(now(), INTERVAL -1 day)) "}

*Pull Previous Successful Update - Data-Raw Append Example:

{"sql": "SELECT * FROM table WHERE date(accepted_date) >= $stored_last_update_datetime"}

stored_last_update_datetime is your stored datetime for last successful data pull

TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.

Column Name	Definition	Example Output
payment_date	Date–TIme Payment Was Completed	2020-07-17T18:21:22
payment_user	Employee ID	1
payment_amount	Amount Invoice Paid	13,632.60
payment_notes	Invoice Payment Note	Manually closed without payment before cash handling was enabled
transaction_id	Invoice Payment ID (if applicable)	7f6cf821-aeb0-11e9-9df1-026d10f0d0f0
invoice_id	Invoice ID	d6962ba7-f864-433a-aada-1dcbf05ac0fa
product_id	Dispensary Product ID	b34192da-ed7f-4dba-852f-dd563d250eca
cost_id	Cost ID	5a944365-75a5-43cf-a860-e0ef62997fe5
invoice_number	Invoice Number	8004
created_date	Date–Time Invoice Created	2020-07-17T18:21:22
due_date	Date–Time Invoice Payment Due	2020-07-17T18:30:31
accepted_date	Date–Time Invoice Accepted	2020-07-17T18:30:31
term_id	Term ID	636bc474-49b4-11e9-8035-774805423a5c
distributor_id	Distributor ID	8ef734ee-8955-468c-8380-1ce6967f3294
inventory_location	Location ID where Invoice was sent	b0c09f06-a878-11e9-936d-025c41018ccc
external_invoice_id	External ID	z728493
invoice_cost_id	Invoice Cost ID	fbb90ddc-b464-4fb0-aee5-041e00ad7639
productname	Product Name	BLUE DREAM .5G
producttype	Product Type	CARTRIDGE
productsubtype	Product Subtype	POD
unitofmeasure	Unit of Measure	GRAMS
discount	Discount	0
base_cost	Base Cost for Invoice Line	10
qty	Quantity for Invoice Line	200
total_cost	Total Cost For Invoice Line	2540
payment_transaction_type	Payment Transaction Type	CASH
fees_from_lines	Fees From Lines	0
unspread_fees	Invoice level fees that are not attached to any specific invoice lines	0
unspread_eligible_fees	n/a currently	0
terms	Terms	COD
storeid	Storeid	230
total_final_cost	Total cost of entire invoice after discounts and fees	47625
productbrand	Product Brand	STIIIZY
vendorname	Vendor Name	JBTB HOLDINGS INC
excise_tax	Tax	540
total_cost_final	Total Cost Final For Invoice	47625
datalake_date	Date that the store was added to the datalake.	2021-05-20
merged_product_id	selltreez product_id that product has been merged with. This will show up on future records following merger.	c08aff9b-9fd2-41ab-bffa-b01642323df8
payment_status	Status	OTHER
last_retrieved_timestamp	The DateTime we last retrieved data from SellTreez	2021-06-09T14:18:30