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 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 |
Updated 5 months ago