Invoice Payments Table

The invoice payments dataset displays a chronological list of all invoice line items for a given invoice and all associated payments. Since invoice payments are not attributed to specific line items, multiple payments on a given invoice will duplicate the product line items. The cost_id column is a unique identifier for a given line item. So, if there are multiple payments, you can remove duplicates by grouping the product_id and cost_id columns. Below are some sample queries to help based on the use case for the 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 NameDefinitionExample Output
payment_dateDate–TIme Payment Was Completed2020-07-17T18:21:22
payment_userEmployee ID1
payment_amountAmount Invoice Paid13,632.60
payment_notesInvoice Payment NoteManually closed without payment before cash handling was enabled
transaction_idInvoice Payment ID (if applicable)7f6cf821-aeb0-11e9-9df1-026d10f0d0f0
invoice_idInvoice IDd6962ba7-f864-433a-aada-1dcbf05ac0fa
product_idDispensary Product IDb34192da-ed7f-4dba-852f-dd563d250eca
cost_idCost ID5a944365-75a5-43cf-a860-e0ef62997fe5
invoice_numberInvoice Number8004
created_dateDate–Time Invoice Created2020-07-17T18:21:22
due_dateDate–Time Invoice Payment Due2020-07-17T18:30:31
accepted_dateDate–Time Invoice Accepted2020-07-17T18:30:31
term_idTerm ID636bc474-49b4-11e9-8035-774805423a5c
distributor_idDistributor ID8ef734ee-8955-468c-8380-1ce6967f3294
inventory_locationLocation ID where Invoice was sentb0c09f06-a878-11e9-936d-025c41018ccc
external_invoice_idExternal IDz728493
invoice_cost_idInvoice Cost IDfbb90ddc-b464-4fb0-aee5-041e00ad7639
productnameProduct NameBLUE DREAM .5G
producttypeProduct TypeCARTRIDGE
productsubtypeProduct SubtypePOD
unitofmeasureUnit of MeasureGRAMS
base_costBase Cost for Invoice Line10
qtyQuantity for Invoice Line200
total_costTotal Cost For Invoice Line2540
payment_transaction_typePayment Transaction TypeCASH
fees_from_linesFees From Lines0
unspread_feesInvoice level fees that are not attached to any specific invoice lines0
unspread_eligible_feesn/a currently0
total_final_costTotal cost of entire invoice after discounts and fees47625
productbrandProduct BrandSTIIIZY
vendornameVendor NameJBTB HOLDINGS INC
total_cost_finalTotal Cost Final For Invoice47625
datalake_dateDate that the store was added to the datalake.2021-05-20
merged_product_idselltreez product_id that product has been merged with. This will show up on future records following merger.c08aff9b-9fd2-41ab-bffa-b01642323df8
last_retrieved_timestampThe DateTime we last retrieved data from SellTreez2021-06-09T14:18:30