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


Did this page help you?