Order Line Item object fields and Data Query
You can perform queries from the Order Line Item object through Data Query. You can also perform join queries from the Order Line Item object, Order object, and other objects. For information about the basic usage of Data Query, see Constructing SQL Queries in Data Query.
Order Line Item fields
The following table lists all the fields that are defined on the order line item object. You can access the Orders Line Item object and all its fields through Data Query and the Orders API.
Field name | Format | Description |
---|---|---|
amountPerUnit | BigDecimal | The amount that is charged per unit excluding discounts. This value can be tax inclusive or tax exclusive. This is a required field. |
deferredRevenueAccountingCode | String | The accounting code for deferred revenue. |
description | String | The description of the order line item for reference purposes. |
itemName | String | The order line item name that is displayed in the UI, invoices, and reports. This is a required field. |
itemType | String | The category of the order line item. Valid values are Fee, Product, and Services. This is a required field. |
listPrice | BigDecimal | The extended list price for an order line item, calculated by the formula: listPrice = listPricePerUnit * quantity |
listPricePerUnit | BigDecimal | The list price per unit at the time of the transaction. This field is used for reporting purposes. |
productCode | String | The product code that is associated with the order line item. This field is used as an identifier for the downstream system. |
purchaseOrderNumber | String | The associated purchase order number. |
quantity | BigDecimal | The total number of the order line items to be purchased. When this field is used, each unit must be identical and you cannot modify a unit individually. To have a different unit, create another order line item. |
recognizedRevenueAccountingCode | String | The accounting code for recognized revenue. |
revenueRecognitionRule | String | The name of the applicable revenue recognition rule. |
taxCode | String | The tax code that is used to identify the appropriate tax rules and rates to apply to this order. |
taxMode | String | Indicates whether the charge amount is tax inclusive or tax exclusive. |
transactionDate | Date | The date of the transaction. If this field is not specified, the order date will be used as the transaction date. |
itemState | String | The state of the order line item. This is a required field. Valid values are Executing, SentToBilling, Complete, and Canceled. |
billTargetDate | Date | Indicates when the order line item will be invoiced. |
itemNumber | String | The number of the order line item. |
amount | BigDecimal | The gross amount of the order line item. |
amountWithoutTax | BigDecimal |
The calculated gross amount for an order line item excluding tax. If the tax mode is tax exclusive, the value of this field equals that of the amount field. If the tax mode of an order line item is not set, the system treats it as tax exclusive by default. The value of the amountWithoutTax field equals that of the amount field. If you create an order line item from the product catalog, the tax mode and tax code of the product rate plan charge are used for the order line item by default. You can still overwrite this default set-up by setting the tax mode and tax code of the order line item. |
accountingCode | String | The accounting code of the order line item. |
UOM | String | The unit of measure. The UOM is defined in the Billing settings. |
productRatePlanChargeId | String | The ID of the product rate plan charge. An order line item can refer to a one-time product rate plan charge. |
soldToOrderContactId | String | The ID of the Sold To contact. |
soldTo | String | The ID of the existing billing contact when the order line item is created. |
id | String | The ID of the order line item. |
deleted | Boolean | Indicates whether this record has been removed. |
createdById | String | The ID of the Zuora user who created this order line item. |
createdDate | DateTime | The date and time when the order line item is created. |
updatedById | String | The ID of the Zuora user who updated the order line item. |
updatedDate | DateTime | The date and time when the order line item is updated. |
excludeItemBookingFromRevenue | Boolean | Specifies whether to exclude non-revenue related rate plan charges and order line items from syncing to Zuora Revenue. This field is only available if you have enabled Zuora Billing - Revenue Integration. |
excludeItemBillingFromRevenue | Boolean | Specifies whether to exclude non-revenue related invoice items, invoice item adjustments, credit memo items, and debit memo items from syncing to Zuora Revenue. This field is only available if you have enabled Zuora Billing - Revenue Integration. |
inlineDiscountType | String | Use this field to specify the inline discount type, which can be Percentage , FixedAmount , or None . |
inlineDiscountPerUnit | BigDecimal | Use this field in accordance with the inlineDiscountType field, in the following manner:
|
discount | BigDecimal | This field shows the total discount amount that is applied to an order line item after the inlineDiscountType and inlineDiscountPerUnit fields are set. |
Common use cases
The following query examples demonstrate how to use Data Query to access the Order Line Item fields.
-
Join the Order Line Item to an Order and then join the Order to another object such as Account.
SELECT Account.Name, Orders.OrderNumber, OrderLineItem.ItemName, OrderLineItem.AmountPerUnit,Account.AccountNumber From Account Join Orders on Account.ID = Orders.AccountId Join OrderLineItem on Orders.Id = OrderLineItem.OrderID where Account.AccountNumber='A00000013'
The query result is as follows:
Name orderNumber ItemName AmountPerUnit AccountNumber Test-Name O-00000089 Delivery Fee 15.99 A00000013
-
Query the Order Line Item by using the Order Number.
SELECT oi.ItemName,oi.ItemNumber,oi.ItemState,oi.ItemType,oi.ListPricePerUnit, oi.Quantity,oi.AmountPerUnit,oi.Amount, oi.TransactionDate from OrderLineItem oi join Orders o on oi.OrderId=o.id where o.OrderNumber='O-00000091'
The query result is as follows:
ItemName ItemNumber ItemState ItemType ListPricePerUnit Quantity AmountPerUnit Amount TransactionDate Delivery Fee 2 Complete Fee 1 5.99 1 15.99 15.99 1/1/2021
-
Retrieve Order Line Item by using its own ID.
SELECT oi.ItemName,oi.ItemNumber,oi.ItemState,oi.ItemType,oi.ListPricePerUnit, oi.Quantity,oi.AmountPerUnit,oi.Amount, oi.TransactionDate from OrderLineItem oi where oi.Id='2c92c8fb7a31c032017a32b66e554fc7'
The query result is as follows:
ItemName ItemNumber ItemState ItemType ListPricePerUnit Quantity AmountPerUnit Amount TransactionDate Delivery Fee 2 Complete Fee 1 5.99 1 15.99 15.99 1/1/2021