Invoices

Returns invoices, including their amounts, status, billing, payment, and rendering 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	=
CustomerId	=
CollectionMethod	=
Currency	=
MetadataAggregate	=
Number	=
ReceiptNumber	=
Status	=
Subscription	=
Created	<, >, >=, <, <=, =
Total	<, >, >=, <, <=, =

You can select:

An invoice by specifying its Id:

SELECT * FROM Invoices WHERE Id = 'or_12345678'

An invoice created after a specific date:

SELECT * FROM Invoices WHERE Created > '2023-07-21 19:00:27.0'

Created can also be used twice to specify a range:

SELECT * FROM Invoices WHERE Created >= '2023-01-01 00:00:00.0' AND Created <= '2023-12-31 23:59:59.0'

Invoices for a given customer:

SELECT * FROM Invoices WHERE CustomerId = 'cus_12345678'

Invoices by Subscription:

SELECT * FROM Invoices WHERE Subscription = 'sub_12345678'

INSERT

To create a new invoice, the CustomerId, CollectionMethod, DaysUntilDue, and PendingInvoiceItemsBehavior fields are required. CustomFieldsAggregate is an aggregate column.

To insert using this column:

INSERT INTO Invoices (CustomerId, CollectionMethod, DaysUntilDue, PendingInvoiceItemsBehavior, CustomFieldsAggregate) VALUES ('cus_MDxevmmMzKidZc', 'send_invoice', 30, 'exclude', '{\"name\":\"cf_test\",\"value\":\"mycfvalue\"}')

INSERT can be executed by specifying the AccountTaxRates column. Columns that are not read-only can be optionally inserted. AccountTaxRates expects TaxRateIds as an array of strings.

INSERT INTO Invoices (CustomerID, Subscription, AccountTaxRates) VALUES ('cus_9s6XKzkNRiz8i3', 'sub_1OHM2S2eZvKYlo2C8dDGfEdN', '[\"txr_1OL7oy2eZvKYlo2CLylDtkiI\",\"txr_1OL7oo2eZvKYlo2CHliiOEZT\"]')

INSERT can be executed by specifying the Discounts column. Columns that are not read-only can be optionally inserted. Discounts is an aggregate column.

To insert using this column:

INSERT INTO InvoiceDiscounts#TEMP (Coupon) VALUES ('dsocsdc')

INSERT INTO Invoices (CustomerId, Discounts) VALUES ('cus_Oscvdvdw3fcd', 'InvoiceDiscounts#TEMP')

UPDATE

To update an invoice, specify an Id:

UPDATE Invoices SET DefaultTaxRates = '[\"txr_1OL7oy2eZvKYlo2CLylDtkiI\",\"txr_1OL7oo2eZvKYlo2CHliiOEZT\"]' WHERE Id = 'in_1OMk0u2eZvKYlo2CGzA4kQ5A'

DELETE

To delete an invoice, specify the Id of the invoice. Only draft invoices can be deleted.

DELETE FROM Invoices WHERE Id = 'in_1MOZcISC4snQ4WkOaa5LZmkj'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the invoice.
CustomerId	String	False	Customers.Id	The Id of the customer to be billed.
AccountCountry	String	True		The country of the business associated with this invoice, most often the business creating the invoice.
AccountName	String	True		The name of the business associated with this invoice, most often the business creating the invoice.
AccountTaxRates	String	False		The account tax Ids associated with the invoice.
AmountDue	Int	True		The final amount due at this time for this invoice.
AmountPaid	Int	True		The amount, in cents, that was paid for this invoice.
AmountRemaining	Int	True		The amount remaining, in cents, that is due.
Application	String	True		The Id of the Connect application that created the invoice.
ApplicationFeeAmount	Int	False		The fee in cents that is applied to the invoice and transferred to the application owner's Stripe account when the invoice is paid.
AttemptCount	Int	True		The number of payment attempts made for this invoice, from the perspective of the payment retry schedule.
Attempted	Boolean	True		Indicates whether an attempt has been made to pay the invoice.
AutomaticTaxEnabled	Boolean	False		Indicates whether Stripe automatically computes tax on this invoice.
AutomaticTaxStatus	String	True		The status of the most recent automatic tax calculation for this invoice.
BillingReason	String	True		Indicates the reason why the invoice was created.
CollectionMethod	String	False		The collection method for this invoice, such as charge_automatically or send_invoice. When charging automatically, Stripe attempts to pay this invoice using the default source attached to the customer. When sending an invoice, Stripe emails this invoice to the customer with payment instructions. The allowed values are charge_automatically, send_invoice.
Charge	String	True	Charges.Id	DEPRECATED. The Id of the latest charge generated for this invoice, if any.
AutoAdvance	Boolean	False		Indicates whether Stripe performs automatic collection of the invoice. When false, the invoice's state does not automatically advance without an explicit action.
Currency	String	False		The three-letter ISO currency code, in lowercase.
Created	Datetime	True		The 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. It is referenced as 'memo' in the Dashboard.
DefaultPaymentMethod	String	False		The Id of the default payment method for the invoice.
DefaultSource	String	False		The Id of the default payment source for the invoice.
DefaultTaxRates	String	False		The tax rates applied to this invoice.
DiscountName	String	True		DEPRECATED. The name of the coupon applied to the invoice.
DiscountAmount	String	True		DEPRECATED. The amount, in the specified currency, that is taken off the subtotal of any invoices for this customer.
CouponId	String	True		DEPRECATED. The Id of the coupon applied to the invoice.
Discounts	String	False		The discounts applied to the invoice.
DueDate	Datetime	False		The date on which payment for this invoice is due. This value is null for invoices where collection_method is charge_automatically.
EndingBalance	Int	True		The ending customer balance after the invoice is finalized.
InvoicePdf	String	True		The link to download the PDF for the invoice. This is null if the invoice has not been finalized yet.
Footer	String	False		The footer displayed on the invoice.
LastFinalizationError	String	True		The error encountered during the previous attempt to finalize the invoice. This field is cleared when the invoice is successfully finalized.
HostedInvoiceUrl	String	True		The URL for the hosted invoice page, which allows customers to view and pay an invoice.
Livemode	Boolean	True		Indicates whether the object exists in live mode, which is true in live mode and false in test mode.
MetadataAggregate	String	False		The set of key-value pairs that you can attach to the invoice object.
CustomFieldsAggregate	String	False		The custom fields displayed on the invoice.
NextPaymentAttempt	Datetime	True		The time at which payment is next attempted.
Number	String	False		The unique, identifying string that appears on emails sent to the customer for this invoice.
OnBehalfOf	String	False		The account, if any, for which the funds of the invoice payment are intended.
Paid	Boolean	True		DEPRECATED. Indicates whether payment was successfully collected for this invoice. An invoice can be paid, most commonly, with a charge or with credit from the customer's account balance.
PaidOutOfBand	Boolean	True		DEPRECATED. Indicates whether the invoice was manually marked paid, which is true if it was manually marked paid and false if it has not been paid yet or was paid on Stripe.
PaymentIntent	String	True	PaymentIntent.Id	DEPRECATED. The Id of the payment intent associated with this invoice.
PaymentMethodOptions	String	False		The payment-method-specific configuration to provide to the invoice's payment intent.
PaymentMethodTypes	String	False		The list of payment method types, such as card, to provide to the invoice's payment intent.
PostPaymentCreditNotesAmount	Int	True		The total amount of all post-payment credit notes issued for this invoice.
PrePaymentCreditNotesAmount	Int	True		The total amount of all pre-payment credit notes issued for this invoice.
Quote	String	True		DEPRECATED. The Id of the quote this invoice was generated from.
PeriodEnd	Datetime	True		The end of the usage period during which invoice items were added to this invoice.
PeriodStart	Datetime	True		The start of the usage period during which invoice items were added to this invoice.
ReceiptNumber	String	True		The transaction number that appears on email receipts sent for this invoice.
StartingBalance	Int	True		The starting customer balance before the invoice is finalized. If the invoice has not been finalized yet, this is the current customer balance.
StatementDescriptor	String	False		The statement descriptor for this invoice. If provided, each invoice created by this subscription applies the tax rate, increasing the amount billed to the customer.
Status	String	True		The status of the invoice, such as draft, open, paid, uncollectible, or void. Instead of checking the forgiven field on an invoice, check for the uncollectible status. Instead of setting the forgiven field on an invoice, mark it as uncollectible. The allowed values are draft, open, paid, uncollectible, void.
Subscription	String	False	Subscriptions.Id	The subscription that this invoice was prepared for, if any.
StatusTransitionsFinalizedAt	Datetime	True		The time at which the invoice draft was finalized.
StatusTransitionsMarkedUncollectibleAt	Datetime	True		The time at which the invoice was marked uncollectible.
StatusTransitionsPaidAt	Datetime	True		The time at which the invoice was paid.
StatusTransitionsVoidedAt	Datetime	True		The time at which the invoice was voided.
Subtotal	Int	True		The total of all subscriptions, invoice items, and prorations on the invoice before any invoice-level discount or tax is applied.
Tax	Int	True		DEPRECATED. The amount of tax on this invoice, which is the sum of all the tax amounts on this invoice.
TestClock	String	True		The Id of the test clock this invoice belongs to.
Total	Int	True		The total after discounts and taxes.
TotalDiscountAmounts	String	True		The aggregate amounts calculated per discount across all line items.
TotalTaxAmounts	String	True		DEPRECATED. The aggregate amounts calculated per tax rate for all line items.
TransferDataAmount	Int	False		The amount that is transferred to the destination account when the invoice is paid.
TransferDataDestination	String	False		The account to which funds from the payment are transferred upon payment success.
WebhooksDeliveredAt	Datetime	True		The time at which webhooks were delivered for this invoice. Invoices are automatically paid or sent one hour after webhooks are delivered, or until all webhook delivery attempts have been exhausted.
AmountShipping	Int	True		The sum of all the shipping amounts on this invoice.
ShippingDetails	String	False		The shipping details for the invoice. The invoice PDF uses the shipping_details value if it is set; otherwise, the PDF renders the shipping address from the customer.
ShippingCost	String	False		The details of the cost of shipping, including the shipping rate applied on the invoice.
CustomerAddress	String	True		The customer's address.
CustomerEmail	String	True		The customer's email.
CustomerName	String	True		The customer's name.
CustomerPhone	String	True		The customer's phone number.
CustomerShipping	String	True		The customer's shipping information.
CustomerTaxExempt	String	True		The customer's tax-exempt status.
CustomerTaxIds	String	True		The customer's tax Ids.
EffectiveAt	Datetime	False		The date on which this invoice is in effect. When defined, this value replaces the system-generated 'Date of issue' printed on the invoice PDF and receipt.
Lines	String	False		The individual line items that make up the invoice.
AutomaticTaxLiabilityAccount	String	False		The Id of the connected account referenced when the tax liability type is account.
AutomaticTaxLiabilityType	String	False		The type of the tax liability account referenced. The allowed values are account, self.
FromInvoiceAction	String	False		The relationship between this invoice and the cloned invoice.
FromInvoiceInvoice	String	False		The Id of the invoice that was cloned. For insertion, use the Id of the invoice to be cloned.
IssuerAccount	String	False		The Id of the connected account referenced when IssuerType is account.
IssuerType	String	False		The type of the issuer account referenced. The allowed values are account, self.
RenderingAmountTaxDisplay	String	False		Specifies how line-item prices and amounts are displayed with respect to tax on invoice PDFs. The allowed values are exclude_tax, include_inclusive_tax.
RenderingPdfPageSize	String	False		The page size of the invoice PDF, such as a4, letter, or auto. If set to auto, the page size is switched to a4 or letter based on the customer locale. The allowed values are a4, auto, letter.
RenderingTemplate	String	False		The Id of the rendering template by which the invoice is formatted.
RenderingTemplateVersion	Integer	False		The version of the rendering template that the invoice is using.
SubscriptionDetailsMetadata	String	True		DEPRECATED. The set of key-value pairs defined as subscription metadata when an invoice is created. It becomes an immutable snapshot of the subscription metadata at the time of invoice finalization. This attribute is populated only for invoices created on or after June 29, 2023.
PaymentSettingsDefaultMandate	String	False		The Id of the mandate to be used for this invoice. It must correspond to the payment method used to pay the invoice, including the invoice's default_payment_method or default_source, if set.
SubtotalExcludingTax	Integer	True		The amount, in cents, representing the subtotal of the invoice before any invoice-level discount or tax is applied. Item discounts are already incorporated.
TotalExcludingTax	Integer	True		The amount, in cents, representing the total amount of the invoice including all discounts but excluding all tax.
LatestRevision	String	True		The Id of the most recent non-draft revision of this invoice.
AutomaticTaxDisabledReason	String	True		The reason that Stripe disabled automatic tax, if it was disabled. The allowed values are finalization_requires_location_inputs, finalization_system_error.
CustomerAccount	String	False		The Id of the account representing the customer to bill.
ParentQuoteDetailsQuote	String	True		The quote details if this invoice was generated from a quote.
ParentSubscriptionDetailsSubscription	String	True		The subscription details if this invoice was generated from a subscription.
PaymentsAggregate	String	True		The payments for this invoice.
AmountOverpaid	Integer	True		The amount overpaid on the invoice.
ConfirmationSecretClientSecret	String	True		The client secret of the payment intent for invoice finalization.
AutomaticallyFinalizesAt	Datetime	False		The time at which the invoice is automatically finalized.
TotalPretaxCreditAmountsAggregate	String	True		The aggregate amounts calculated per pretax credit across all line items.
TotalTaxesAggregate	String	True		The aggregate tax amounts by tax rate for all line items.

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
PendingInvoiceItemsBehavior	String	Specifies how pending invoice items are handled when the invoice is created. This column is required for insert operations. The allowed values are exclude, include.
DaysUntilDue	Int	The number of days from the date the invoice is created until it is due. This is only valid for invoices where billing is send_invoice.
Forgive	Boolean	Indicates whether the invoice should be forgiven if the source has insufficient funds to fully pay the invoice.
Source	String	The payment source to be charged. The source must be the Id of a source belonging to the customer associated with the invoice being paid.
AccountId	String	The Id of the connected account from which to retrieve invoices.

Power BI Connector for Stripe