InvoiceItems

Returns the available invoice items in Stripe and supports creating, updating, and deleting them.

Table-Specific Information

SELECT

The connector uses the Stripe API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the connector.


Column	Supported Operators
Id	=
InvoiceId	=
Customer	=
AccountId	=
Date	<, >, >=, <, <=, =

You can select:

An invoice item by specifying its Id:

SELECT * FROM InvoiceItems WHERE Id = 'or_12345678'

Invoice items for a given customer:

SELECT * FROM InvoiceItems WHERE Customer = 'cus_12345678'

Invoice items created after a specific date:

SELECT * FROM InvoiceItems WHERE Date > '2018-11-16 10:59:00.0'

Date can also be used twice to specify a range:

SELECT * FROM InvoiceItems WHERE Date >= '2018-01-01 00:00:00.0' AND Date <= '2018-12-31 23:59:59.0'

INSERT

To create a new invoice item, the Customer, Currency, and Amount fields are required:

INSERT INTO InvoiceItems (Customer, Currency, Amount) VALUES ('cus_NBs3z63zmfCJy1', 'USD', '225')

INSERT can be executed by specifying the DiscountsAggregate column. The columns that are not read-only can be inserted (optional). DiscountsAggregate is an aggregate column.

To insert using this column as Temp table:

INSERT INTO InvoiceItemDiscounts#TEMP (Coupon) VALUES ('Pqx2fHKt')

INSERT INTO InvoiceItems (Customer, Amount, Currency, DiscountsAggregate) VALUES ('cus_O20ZOZfrjjJeGj', 25000, 'USD', 'InvoiceItemDiscounts#TEMP')

To insert using this column as JSON:

INSERT INTO InvoiceItems (Customer, currency, MetadataAggregate, PeriodEnd, PeriodStart, DiscountsAggregate, PriceDataProduct, PriceDataCurrency) VALUES ('cus_PARXAI77xUsWw1', 'USD', '[{\"key\":\"test2\",\"type\":\"text\",\"test\":\"value\"}]', '2023-11-03 14:00:09.000000', '2023-11-03 14:00:08.000000', '[{\"discount\":\"text\"},{\"discount\":\"value\"}]', 'product1', 'usd')

INSERT can be executed by specifying the TaxRatesAggregate column. Columns that are not read-only can be optionally inserted:

INSERT INTO InvoiceItems (Customer, Currency, Amount, TaxRatesAggregate) VALUES ('cus_9s6XKzkNRiz8i3', 'USD', 125614, '[\"txr_1OL7oy2eZvKYlo2CLylDtkiI\",\"txr_1OL7oo2eZvKYlo2CHliiOEZT\"]')

UPDATE

To update an invoice item, specify an Id:

UPDATE InvoiceItems SET PeriodStart = '2023-11-03 14:00:06.000000', PeriodEnd = '2023-11-03 14:00:08.000000' WHERE Id = 'ii_1OIS6s2eZvKYlo2COIxLnMms'

DELETE

To delete an invoice item, specify an Id:

DELETE FROM InvoiceItems WHERE Id = 'ii_1OP2II2eZvKYlo2CLc0nHFnR'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the invoice item.
InvoiceId	String	False	Invoices.Id	The Id of the invoice.
Amount	Int	False		The amount, in cents.
Customer	String	False	Customers.Id	The Id of the customer who is billed when this invoice item is billed.
Currency	String	False		Three-letter ISO currency code, in lowercase. Must be a supported currency.
Date	Datetime	False		The date and time at which the object was created, measured in seconds since the Unix epoch.
Description	String	False		An arbitrary string attached to the object. Often useful for displaying to users. The description field on invoice line items has a maximum character length limit of 500.
Discountable	Boolean	False		If true, discounts apply to this line item. Always false for prorations.
LiveMode	Boolean	False		Indicates whether the object exists in live mode. The value is true in live mode and false in test mode.
PeriodStart	Datetime	False		The start of the period.
PeriodEnd	Datetime	False		The end of the period, which must be greater than or equal to the start.
PlanId	String	False		Unique identifier for the plan.
PlanAggregate	String	True		The plan of the subscription, if the line item is a subscription or a proration.
PriceId	String	False		Unique identifier for the price.
PriceAggregate	String	True		The price of the invoice item.
Proration	Boolean	True		Whether this is a proration.
Quantity	Int	False		The quantity of the subscription, if the line item is a subscription or a proration. [DEPRECATED] Use QuantityDecimal instead.
Subscription	String	False		The Id of the subscription the item pertains to.
SubscriptionItems	String	False		The subscription item that this invoice item has been created for, if any.
TestClock	String	False		The Id of the test clock this invoice item belongs to.
UnitAmount	Integer	False		Unit amount (in the currency specified) of the invoice item.
UnitAmountDecimal	Decimal	False		Decimal value of Unit amount (in the currency specified) of the invoice item.
MetadataAggregate	String	False		The metadata object.
DiscountsAggregate	String	False		The discounts which apply to the invoice item.
TaxRatesAggregate	String	False		The tax rates which apply to the invoice item. When set, the default_tax_rates on the invoice do not apply to this invoice item.
QuantityDecimal	String	False		Non-negative decimal with at most 12 decimal places. The quantity of units for the line item.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
PriceDataProduct	String	The ID of the product that this price will belong to. This is a pseudocolumn to support insert operation.
PriceDataCurrency	String	Three-letter ISO currency code, in lowercase. Must be a supported currency. This is a pseudocolumn to support insert operation.
PriceDataTaxBehavior	String	Only required if a default tax behavior was not provided in the Stripe Tax settings. Specifies whether the price is considered inclusive of taxes or exclusive of taxes. One of inclusive, exclusive, or unspecified. Once specified as either inclusive or exclusive, it cannot be changed. This is a pseudocolumn to support insert operation.
PriceDataUnitAmount	Int	A positive integer in cents (or 0 for a free price) representing how much to charge. This is a pseudocolumn to support insert operation.
PriceDataUnitAmountDecimal	String	Same as unit_amount, but accepts a decimal value in cents with at most 12 decimal places. Only one of unit_amount and unit_amount_decimal can be set. This is a pseudocolumn to support insert operation.
TaxBehavior	String	Only required if a default tax behavior was not provided in the Stripe Tax settings. Specifies whether the price is considered inclusive of taxes or exclusive of taxes. One of inclusive, exclusive, or unspecified. Once specified as either inclusive or exclusive, it cannot be changed. This is a pseudocolumn to support insert operation.
TaxCode	String	A tax code Id.
AccountId	String	The Id of the connected account to get invoice line items for.

CData Python Connector for Stripe