Returns invoice items, including their amount, customer, billing period, and pricing details.

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) 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\"}]')

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 of the invoice item, in cents.
Customer	String	False	Customers.Id	The Id of the customer who is billed when this invoice item is billed.
Currency	String	False		The three-letter ISO currency code, in lowercase. It must be a supported currency.
Date	Datetime	True		The time at which the object was created.
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		Indicates whether discounts apply to this line item. This is always false for prorations.
LiveMode	Boolean	True		Indicates whether the object exists in live mode, which 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.
Proration	Boolean	True		Indicates whether this invoice item is a proration.
TestClock	String	True		The Id of the test clock this invoice item belongs to.
MetadataAggregate	String	False		The set of key-value pairs attached to the invoice item for storing additional structured information.
DiscountsAggregate	String	False		The discounts that apply to the invoice item.
TaxRatesAggregate	String	False		The tax rates that apply to the invoice item. When set, the default_tax_rates on the invoice do not apply to this invoice item.
QuantityDecimal	String	False		The quantity of units for the line item, expressed as a non-negative decimal with at most 12 decimal places.
CustomerAccount	String	False		The Id of the account representing the customer to bill.
ParentSubscriptionItemDetails	String	True		DEPRECATED. The parent subscription item, if applicable.
PricingUnitAmount	Integer	True		DEPRECATED. The unit amount of the pricing for this invoice item.
PricingPriceId	String	False		The Id of the price used for this invoice item.
NetAmount	Integer	True		The net amount after discounts for this invoice item.
ProrationDetailsInvoice	String	True		The invoice for which the proration was calculated.
PricingUnitAmountDecimal	String	True		The unit amount of the pricing, in decimal, for this invoice item.
PricingPriceDetailsProduct	String	True		The Id of the product that this price belongs to.
PricingType	String	True		The pricing type for this invoice item.
ParentSubscriptionDetails	String	True		Details about the subscription that generated this invoice 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
TaxBehavior	String	Specifies whether the price is considered inclusive of taxes or exclusive of taxes, such as inclusive, exclusive, or unspecified. This is only required if a default tax behavior was not provided in the Stripe Tax settings. Once specified as either inclusive or exclusive, it cannot be changed. This is a pseudocolumn to support the insert operation.
TaxCode	String	The Id of the tax code applied to the invoice item.
AccountId	String	The Id of the connected account from which to retrieve invoice items.