Invoices

Create, update, delete, and query the available Invoices in Stripe.

Table Specific Information

Select

Server-Side Query Support

The 本製品 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 本製品.

Id, CustomerId, CollectionMethod, Upcoming, AutoAdvance, Currency, Metadata, Number, ReceiptNumber, Status, and Subscription support the following operator: =.
Created and Total support the following operators: <, >, >=, <, <=, =.

You can select:

An Invoice by specifying its Id:

SELECT * FROM Invoices WHERE Id = 'or_12345678'

An Invoice created after a specific date (Created may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range):

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

Invoices for a given Customer:

SELECT * FROM Invoices WHERE CustomerId = 'cus_12345678'

Invoices by AutoAdvance:

SELECT * FROM Invoices WHERE AutoAdvance = True

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. The columns that are not read-only can be inserted (optional). 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. The columns that are not read-only can be inserted (optional). 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		Final amount due at this time for this invoice.
AmountPaid	Int	True		The amount, in cents, that was paid.
AmountRemaining	Int	True		The amount remaining, in cents, that is due.
Application	String	True		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		Number of payment attempts made for this invoice, from the perspective of the payment retry schedule.
Attempted	Boolean	True		Whether an attempt has been made to pay the invoice.
AutomaticTaxEnabled	Boolean	False		Whether Stripe automatically computes tax on this invoice.
AutomaticTaxStatus	String	True		The status of the most recent automated tax calculation for this invoice.
BillingReason	String	True		Indicates the reason why the invoice was created.
CollectionMethod	String	False		Either charge_automatically, or send_invoice. When charging automatically, Stripe will attempt to pay this invoice using the default source attached to the customer. When sending an invoice, Stripe will email this invoice to the customer with payment instructions. 使用できる値は次のとおりです。charge_automatically, send_invoice
Charge	String	True		ID of the latest charge generated for this invoice, if any.
AutoAdvance	Boolean	False		Controls whether Stripe will perform automatic collection of the invoice. When false, the invoice's state will not automatically advance without an explicit action.
Currency	String	False		Three-letter ISO currency code, in lowercase.
Created	Datetime	True		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. Referenced as 'memo' in the Dashboard.
DefaultPaymentMethod	String	False		ID of the default payment method for the invoice.
DefaultSource	String	False		ID of the default payment source for the invoice.
DefaultTaxRates	String	False		The tax rates applied to this invoice.
DiscountName	String	True		Name of the coupon.
DiscountAmount	String	True		Amount (in the currency specified) that is taken off the subtotal of any invoices for this customer.
CouponId	String	True		Id of the coupon.
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=charge_automatically.
EndingBalance	Int	True		Ending customer balance after the invoice is finalized.
InvoicePdf	String	True		The link to download the PDF for the invoice. If the invoice has not been finalized yet, this is null.
Footer	String	False		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		Has the value true if the object exists in live mode or the value false if the object exists in test mode.
MetadataAggregate	String	False		The set of key/value pairs that you can attach to a subscription object.
CustomFieldsAggregate	String	False		Custom Fields object.
NextPaymentAttempt	Datetime	True		The time at which payment will next be attempted.
Number	String	True		A 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		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		Returns true if the invoice was manually marked paid, returns false if the invoice hasn't been paid yet or was paid on Stripe.
PaymentIntent	String	True		The PaymentIntent associated with this invoice.
PaymentMethodOptions	String	True		Payment-method-specific configuration to provide to the invoice's PaymentIntent.
PaymentMethodTypes	String	False		The list of payment method types (e.g. card) to provide to the invoice's PaymentIntent.
PostPaymentCreditNotesAmount	Int	True		Total amount of all post-payment credit notes issued for this invoice.
PrePaymentCreditNotesAmount	Int	True		Total amount of all pre-payment credit notes issued for this invoice.
Quote	String	True		The quote Id this invoice was generated from.
PeriodEnd	Datetime	True		End of the usage period during which invoice items were added to this invoice.
PeriodStart	Datetime	True		Start of the usage period during which invoice items were added to this invoice.
ReceiptNumber	String	True		This is the transaction number that appears on email receipts sent for this invoice.
StartingBalance	Int	True		Starting customer balance before the invoice is finalized. If the invoice has not been finalized yet, this will be the current customer balance.
StatementDescriptor	String	False		If provided, each invoice created by this subscription will apply the tax rate, increasing the amount billed to the customer.
Status	String	True		The status of the invoice, one of 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. 使用できる値は次のとおりです。draft, open, paid, uncollectible, void
Subscription	String	False		The subscription that this invoice was prepared for, if any.
StatusTransitionsFinalizedAt	Datetime	True		The time that the invoice draft was finalized.
StatusTransitionsMarkedUncollectibleAt	Datetime	True		The time that the invoice was marked uncollectible.
StatusTransitionsPaidAt	Datetime	True		The time that the invoice was paid.
StatusTransitionsVoidedAt	Datetime	True		The time that the invoice was voided.
Subtotal	Int	True		Total of all subscriptions, invoice items, and prorations on the invoice before any invoice level discount or tax is applied.
Tax	Int	True		The amount of tax on this invoice. This is the sum of all the tax amounts on this invoice.
TestClock	String	True		ID of the test clock this invoice item belongs to.
Total	Int	True		Total after discounts and taxes.
TotalDiscountAmounts	String	True		The aggregate amounts calculated per discount across all line items.
TotalTaxAmounts	String	True		The aggregate amounts calculated per tax rate for all line items.
TransferDataAmount	Int	False		The amount that will be transferred to the destination account when the invoice is paid.
TransferDataDestination	String	False		The account where funds from the payment will be transferred to upon payment success.
WebhooksDeliveredAt	Datetime	True		Invoices are automatically paid or sent 1 hour after webhooks are delivered, or until all webhook delivery attempts have been exhausted.
AmountShipping	Int	True		This is the sum of all the shipping amounts.
ShippingDetails	String	True		Shipping details for the invoice. The Invoice PDF will use the shipping_details value if it is set, otherwise the PDF will render the shipping address from the customer.
ShippingCost	String	False		The details of the cost of shipping, including the ShippingRate 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 when 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 connected account being referenced when type is account.
AutomaticTaxLiabilityType	String	False		Type of the account referenced. 使用できる値は次のとおりです。account, self
FromInvoiceAction	String	False		The relation between this invoice and the cloned invoice.
FromInvoiceInvoice	String	False		The invoice that was cloned. For insertion use the id of the invoice that will be cloned.
IssuerAccount	String	False		The connected account being referenced when IssuerType is account.
IssuerType	String	False		Type of the account referenced. 使用できる値は次のとおりです。account, self
RenderingAmountTaxDisplay	String	False		How line-item prices and amounts will be displayed with respect to tax on invoice PDFs. 使用できる値は次のとおりです。exclude_tax, include_inclusive_tax
RenderingPdfPageSize	String	False		Page size of invoice pdf. Options include a4, letter, and auto. If set to auto, page size will be switched to a4 or letter based on customer locale. 使用できる値は次のとおりです。a4, auto, letter
RenderingTemplate	String	False		ID of the rendering template that the invoice is formatted by.
RenderingTemplateVersion	Integer	False		Version of the rendering template that the invoice is using.
RenderingOptions	String	True		The rendering options of the invoice.
SubscriptionDetailsMetadata	String	True		Set of key-value pairs defined as subscription metadata when an invoice is created. Becomes an immutable snapshot of the subscription metadata at the time of invoice finalization. Note: This attribute is populated only for invoices created on or after June 29, 2023.
PaymentSettingsDefaultMandate	String	False		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 integer 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 integer 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.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


Name	Type	Description
PendingInvoiceItemsBehavior	String	For Insert this column is required 使用できる値は次のとおりです。exclude, include
Upcoming	Boolean	Determines if the select is for upcoming invoices.
DaysUntilDue	Int	The number of days from which the invoice is created until it is due. Only valid for invoices where billing=send_invoice.
Forgive	Boolean	Determines if invoice should be forgiven if source has insufficient funds to fully pay the invoice.
Source	String	A 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 to get invoices for

SSIS Components for Stripe