Skip to main content

Order Line Item object fields and Data Query

Zuora

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.
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.
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.

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