ODBC Driver for Zoho Books

Build 25.0.9434

PurchaseOrderDetails

To list, add, update and delete details of a purchase order.

Table Specific Information

Select

The driver uses the Zoho Books API to process WHERE clause conditions built with the following column and operator:

  • PurchaseorderId supports the '=' and IN operators.

NOTE: PurchaseorderId is required to query PurchaseOrderDetails.

The rest of the filter is executed client-side in the driver.

For example: 

    SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId = '1894553000000087078'
	SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId IN (SELECT PurchaseorderId FROM PurchaseOrders)
	SELECT * FROM PurchaseOrderDetails WHERE PurchaseorderId IN ('1894553000000087078','1894553000000087079')

Insert

INSERT can be executed by specifying the Vendorid or lineitems columns. The columns that are not read-only can be inserted optionally. The following is an example of how to insert into this table. 

INSERT INTO PurchaseorderLineItems#TEMP (Name, itemid, rate, quantity, accountid) VALUES ('Cloth-Jeans', '3285934000000104097', '1700', '1', '3285934000000034001')

INSERT INTO PurchaseorderDetails (Vendorid, lineitems) VALUES ('3285934000000104023', PurchaseorderLineItems#Temp)

INSERT can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to insert using JSON array into this table. 

INSERT INTO PurchaseorderDetails (VendorId, LineItems) VALUES ('3255827000000081003', '[{"Name":"Cloth-Jeans3", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1", "AccountId":"3285934000000034001"}]')

Update

UPDATE can be executed by specifying the PurchaseorderId in the WHERE Clause. The columns that are not read-only can be updated. For example: 

INSERT INTO PurchaseorderLineItems#TEMP (Name, itemid, rate, quantity, accountid) VALUES ('Cloth-Jeans', '3285934000000104097', '1700', '1', '3285934000000034001')

UPDATE PurchaseOrderDetails SET Vendorid = '3285934000000104002', lineitems = 'PurchaseorderLineItems#Temp' WHERE PurchaseorderId = '3285934000000264005'

UPDATE can also be executed by specifying the LineItems column as a JSON array. The following is an example of how to update using JSON array into this table. 

UPDATE PurchaseOrderDetails SET Vendorid = '3285934000000104002', LineItems = '[{"Name":"Cloth-Jeans", "ItemId":"3285934000000104097", "Rate":"1700", "Quantity":"1", "AccountId":"3285934000000034001"}]' WHERE PurchaseorderId = '3285934000000264005'

Delete

DELETE can be executed by specifying the PurchaseorderId in the WHERE Clause For example: 

DELETE FROM PurchaseOrderDetails WHERE PurchaseOrderId = '3350895000000089001'

Columns

Name Type ReadOnly References SupportedOperators Description
PurchaseorderId [KEY] String True

PurchaseOrders.PurchaseorderId

Id of a purchase order.

PurchaseorderNumber String False

Number of purchase order.

ReferenceNumber String False

Reference number of purchase order.

Adjustment Decimal True

Adjustments made to the purchase order.

AdjustmentDescription String True

Description of adjustments made to the purchase order.

ApproverId String True

Users.UserId

Id of an approver.

ApproversList String True

List of approvers.

AttachmentName String True

Name of the attachment.

Attention String False

Name of a person in purchase order.

BilledStatus String True

Status of bill.

BillingAddressId Long False

Id of the Billing Address.

BillingAddress String True

Billing address of a purchase order.

BillingAddressAttention String True

Name of the person of bill order.

BillingAddressCity String True

City of billing address.

BillingAddressCountry String True

Country of billing address.

BillingAddressFax String True

Fax number of billing address.

BillingAddressPhone String True

Phone number of billing address.

BillingAddressState String True

State of billing address.

BillingAddressStreet2 String True

Street two of billing address.

BillingAddressZip String True

Zip code of billing address.

Bills String True

Bills.

CanMarkAsBill Boolean True

Check if purchase order can be mark as bill.

CanMarkAsUnbill Boolean True

Check if purhcase order can be mark as unbill.

CanSendInMail Boolean True

Check if purchase order can be sent in mail.

ClientViewedTime Datetime True

Last time when client viewed the purchase order.

ColorCode String True

Color code.

ContactCategory String True

Category of contacts.

CreatedById String True

Users.UserId

Contact Id who have created this purchase order.

CreatedTime Datetime True

Time at which the purchase order was created.

CurrencyCode String True

Currency code of the customer's currency.

CurrencyId String False

Currencies.CurrencyId

Currency Id of the customer's currency.

CurrencySymbol String True

Currency symbol of the customer's currency.

CurrentSubStatus String True

Current sub status of a purchase order.

CurrentSubStatusId String True

Current sub status Id of a purchase order.

ContactPersons String False

Contact persons of a contact.

CustomFields String False

Custom Fields defined for Journal

Date Date False

Purchase order date.

Documents String False

List of files to be attached to a particular transaction.

DeliveryAddress String True

Delivery address.

DeliveryAddress1 String True

Delivery address one.

DeliveryAddress2 String True

Delivery address two.

DeliveryAddressCity String True

City of delivery address.

DeliveryAddressCountry String True

Country of delivery address.

DeliveryAddressOrganizationAddressId String True

Id or organization address of delivery address.

DeliveryAddressPhone String True

Phone number of delivery address.

DeliveryAddressState String True

State of delivery address.

DeliveryAddressZip String True

Zip code of delivery address.

DeliveryCustomerId String False

Contacts.ContactId

Id of a customer of delivery address.

DueDate Date False

Delivery date of purchase order..

DeliveryDate Date False

Date of delivery.

DeliveryOrgAddressId String False

Delivery address Id of an organization.

Discount String False

Discount given to specific item in purchase order.

DiscountAccountId String False

BankAccounts.AccountId

Account Id of discount.

DiscountAmount Decimal True

Amount of discount.

DiscountAppliedOnAmount Double True

Discount applied on amount.

ExchangeRate Decimal False

Exchange rate of the currency.

ExpectedDeliveryDate Date True

Expected delivery date of purchased product.

HasQtyCancelled Boolean True

Check if the quantity of a purchase order has been cancelled.

IsDiscountBeforeTax Boolean False

Check if purchase order applied discount before tax.

IsDropShipment Boolean True

Check if purchase order have drop shipment.

IsEmailed Boolean True

Check if purchase order is emailed or not.

IsInclusiveTax Boolean False

Check if the purchase order is inclusive tax.

IsPreGst Boolean True

Check if purchase order includes pre GST.

IsViewedByClient Boolean True

Check if purchase order is viewed by client.

IsUpdateCustomer Boolean False

Check if customer should be updated.

LastModifiedTime Datetime True

The time of last modification of the purchase order.

Notes String False

Notes for this purchase order.

OrderStatus String True

Status of order.

Orientation String True

Orientation of the page.

PageHeight String True

Height of the page.

PageWidth String True

Width of the page.

PricePrecision Integer True

The precision for the price

PricebookId String False

Id of the pricebook.

SalesorderId String False

SalesOrders.SalesorderId

Id of the Sales Order.

SalesOrders String True

SalesOrders.

ShipVia String False

Mode of shipping the item.

ShipViaId String True

Id of mode through which shipping was done of items.

Status String True

Status of the purchase order

SubTotal Decimal True

Sub total of Purhcase order.

SubTotalInclusiveOfTax Decimal True

Subtotal amount which are inclusive of tax.

SubmittedBy String True

Detail of the user who has submitted the purchase order.

SubmittedDate Date True

Date of the submission.

SubmitterId String True

Users.UserId

Id of the submitter.

TaxTotal Decimal True

Total amount of tax.

Taxes String True

Taxes.

TemplateId String False

Id of the template.

TemplateName String True

Name of the template

TemplateType String True

Type of template.

Terms String False

Terms and Conditions apply of a purchase order.

Total Decimal True

Total of purchase orders.

TotalQuantity Integer True

TotalQuantity.

VendorId String False

Id of the vendor the purchase order has been made.

VendorName String True

Name of the vendor the purchase order has been made.

GstTreatment String False

Choose whether the vendor credit is GST registered/unregistered/consumer/overseas.

VatTreatment String False

VAT treatment for the vendor credit.

TaxTreatment String False

VAT treatment for the Vendor Credit.

GstNo String False

GST number.

SourceOfSupply String False

Source of supply.

PlaceOfSupply String False

The place of supply is where a transaction is considered to have occurred for VAT purposes.

DestinationOfSupply String False

Place where the goods/services are supplied to.

LineItems String False

Line items of an estimate.

ContactPersonsAssociated String True

List of associated contact persons (contacts linked to the purchase order), in JSON format.

DeliveryAddressEmail String True

Email address for delivery address (if present in API response).

DeliveryAddressIsPrimary Boolean True

Whether this delivery address is the primary address.

DeliveryAddressIsValid Boolean True

Whether the delivery address is valid.

DeliveryAddressIsVerifiable Boolean True

Whether the delivery address can be verified.

DeliveryAddressIsVerified Boolean True

Whether the delivery address has been verified.

DeliveryCustomerAddressId String True

Address ID of the delivery customer on the purchase order.

DiscountAccountName String True

Name of the account used for discount in this purchase order.

DiscountType String True

Type of discount applied (e.g., entity_level, item_level, etc.).

IsAdvTrackingInReceive Boolean True

Advanced tracking applied in receive operation.

IsTcsAmountInPercent Boolean True

Whether the TCS amount is a percentage value.

PaymentTerms Integer True

Numeric value representing the payment terms.

PaymentTermsLabel String True

Label for the payment terms (e.g., 'Due end of the month').

SubStatuses String True

List of sub-statuses for the purchase order, in JSON format.

SubmittedByEmail String True

Email of the user who submitted the purchase order.

SubmittedByName String True

Name of the user who submitted the purchase order.

SubmittedByPhotoUrl String True

Photo URL of the user who submitted the purchase order.

TaxOverridePreference String True

Preference setting for tax override on the purchase order.

TaxRounding String True

Tax rounding method applied to the purchase order (e.g., entity_level).

TdsAmount Decimal True

TDS (Tax Deducted at Source) amount for the purchase order.

TdsCalculationType String True

Calculation type for TDS, like 'tds_entity_level'.

TdsOverridePreference String True

Override preference for TDS on this purchase order.

TdsSection String True

TDS section applied, e.g., '194'.

TdsSummary String True

Summary of TDS applied, in JSON format.

TdsTaxId String True

ID of the TDS tax applied.

TdsTaxName String True

Name of the TDS tax applied.

TcsAmount Decimal True

TCS (Tax Collected at Source) amount for the purchase order.

TcsPercent Decimal True

TCS Percent value, if applicable.

TcsSection String True

TCS section code for purchase order.

TcsTaxId String True

ID of the TCS tax applied.

TcsTaxName String True

Name of the TCS tax applied.

CrmOwnerId String False

ID of the CRM Owner.

CrmCustomReferenceId Long False

ID of the CRM custom Reference.

NotesDefault String False

Default notes for the purchase order.

TermsDefault String False

Default terms for the purchase order.

Attachment String True

Attachment object/details, e.g. supporting documents (JSON format if object/array).

IgnoreAutoNumberGeneration Boolean False

Set to true to ignore automatic number generation when creating or updating this purchase order.

Copyright (c) 2025 CData Software, Inc. - All rights reserved.
Build 25.0.9434