Skip to main content

Query the Order Line Item object through Data Query

Zuora

Query the Order Line Item object through Data Query

You can query 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
accountingCode String The accounting code of the order line item. 
amount BigDecimal The gross amount of the order line item.
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.
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.

billingRule Enum The value can be TriggerWithoutFulfillment or TriggerAsFulfillmentOccurs. The value indicates the condition to trigger invoice generation for an order line item, that is, triggering invoice generation for an order line item without setting the fulfillment or with setting the fulfillment.
billTargetDate Date Indicates when the order line item will be invoiced. 
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.
deferredRevenueAccountingCode String The accounting code for deferred revenue.
deleted Boolean Indicates whether this record has been removed.
description String The description of the order line item for reference purposes.
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.
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.
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.
id String The ID of the order line item.
itemCategory Enum The value can be sale or return, which indicates the sale or return of an order line item.
itemName String The order line item name that is displayed in the UI, invoices, and reports. This is a required field.
itemNumber String The number of the order line item.
itemState String The state of the order line item. This is a required field. Valid values are Executing, SentToBilling, Complete, and Canceled.
itemType String The category of the order line item. Valid values are Fee, Product, and Services. This is a required field.
inlineDiscountType String Use this field to specify the inline discount type, which can be PercentageFixedAmount, or None.
inlineDiscountPerUnit BigDecimal Use this field in accordance with the inlineDiscountTypefield, in the following manner:
  • If the inlineDiscountType field is set as Percentage, this field specifies the discount percentage for each unit of the order line item.
  • If the inlineDiscountType field is set as FixedAmount, this field specifies the discount amount on each unit of the order line item.
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.
originalOrderLineItemNumber String The field is only available if the value of the itemCategory field is return. The field indicates the order line item number of the order line item that is returned.
originalOrderNumber String The field is only available if the value of the itemCategory field is return. The field indicates the order number of the order in which the order line item is returned.
productCode String The product code that is associated with the order line item. This field is used as an identifier for the downstream system.
productRatePlanChargeId String The ID of the product rate plan charge. An order line item can refer to a one-time product rate plan charge.
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.
quantityAvailableForReturn BigDecimal The quantity is available for return.
quantityFulfilled BigDecimal The quantity of fulfillment items that are delivered or returned. 
quantityPendingFulfillment BigDecimal The quantity of fulfillment items that are to be delivered or returned.
recognizedRevenueAccountingCode String The accounting code for recognized revenue.
requiresFulfillment Boolean Indicates whether the order line item requires a fulfillment.
revenueRecognitionRule String The name of the applicable revenue recognition rule.
soldTo String The ID of a contact that belongs to the billing account of the order line item.
soldToOrderContactId String The ID of the Sold To contact.
SoldToSnapshotId String A snapshot of the ID for an account used as the sold-to contact of an order line item.
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.
UOM String The unit of measure. The UOM is defined in the Billing settings.
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.

SQL examples for querying the Order Line Item object

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