PurchaseOrders

Stores purchase orders issued to vendors, tracking delivery status, billing terms, and related inventory; unavailable in free or standard editions.

Table-Specific Information

The PurchaseOrders table is only accessible when connected to a ZohoCRM Enterprise Edition account. If using a different edition, this table may not be available.

Select

This table supports COQL for filtering, meaning that most filters comparing columns to values are submitted server-side.

Insert

The following example demonstrates a basic insert operation with line item details:

-- Insert a new purchase order with a subject and product line items
INSERT INTO PurchaseOrders (Subject, VendorName_Id, PurchaseItems) VALUES (
  -- This is the value for the Subject column
  'Purchase Order',
  -- This is the value for the VendorName_Id column
  '1021046000000513014', 
  -- This is the JSON string for PurchaseItems
  '[
  {
    "Product_Name": {
      "id": "1021046000000414457"
    },
    "Quantity": 1024,
    "Discount": 99,
    "Tax": 13,
    "List_Price": 176.21,
    "Description": "test description 1"
  }
]'
)

Required fields for REST API: Subject and PurchaseItems are required. These provide the order summary and line item data needed to create a purchase order.

Required fields for BULK API: Subject, VendorName_Id and PurchaseItems are required. These provide the order summary, the vendor's identifier and line item data needed to create a purchase order.

Any field which is not read-only (ReadOnly = false in the table schema) can be inserted.

Please note that the REST API honors only the 'id' field of the 'Product_Name' aggregate in the PurchaseItems column. The BULK API does not accept any other field in this aggregate.

Delete

You must specify the Id column in the WHERE clause to delete a record. The following example demonstrates a delete operation:

-- Delete a purchase order by its unique ID
DELETE FROM PurchaseOrders WHERE Id = '3152079000000153079'

Update

You must specify the Id column in the WHERE clause to update a record. The following example demonstrates an update operation:

-- Update the description for a specific purchase order using its ID
UPDATE PurchaseOrders SET Description = 'Changed from the API.' WHERE Id = '3152079000000492001'

Required fields: Id is required. The update must target a specific record.

Any field which is not read-only (ReadOnly = false in the table schema) can be updated.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier for the purchase order record.
RequisitionNumber	String	False	Requisition number associated with the purchase order.
TrackingNumber	String	False	Tracking number for the shipment related to the purchase order.
PONumber	String	False	Unique purchase order number.
Subject	String	False	Subject or title of the purchase order.
VendorName_Id	String	False	Unique identifier of the associated vendor.
VendorName_Name	String	True	Name of the associated vendor.
ContactName_Id	String	False	Unique identifier of the associated contact.
ContactName_Name	String	True	Name of the associated contact.
PODate	Date	False	Date when the purchase order was issued.
DueDate	Date	False	Date by which the goods or services should be delivered.
Carrier	String	False	Name of the shipping carrier handling the order.
ExciseDuty	Decimal	False	Excise duty applied to the order.
SalesCommission	Decimal	False	Sales commission associated with the order.
Status	String	False	Current status of the purchase order.
PurchaseOrderOwner_Id	String	False	Unique identifier of the purchase order owner.
PurchaseOrderOwner_FirstName	String	True	First name of the purchase order owner.
PurchaseOrderOwner_LastName	String	True	Last name of the purchase order owner.
PurchaseOrderOwner_Email	String	True	Email address of the purchase order owner.
PurchaseOrderOwner_Name	String	True	Full name of the purchase order owner.
CreatedBy_Id	String	False	Unique identifier of the user who created the purchase order.
CreatedBy_FirstName	String	True	First name of the user who created the purchase order.
CreatedBy_LastName	String	True	Last name of the user who created the purchase order.
CreatedBy_Email	String	True	Email address of the user who created the purchase order.
CreatedBy_Name	String	True	Full name of the user who created the purchase order.
ModifiedBy_Id	String	False	Unique identifier of the user who last modified the purchase order.
ModifiedBy_FirstName	String	True	First name of the user who last modified the purchase order.
ModifiedBy_LastName	String	True	Last name of the user who last modified the purchase order.
ModifiedBy_Email	String	True	Email address of the user who last modified the purchase order.
ModifiedBy_Name	String	True	Full name of the user who last modified the purchase order.
CreatedTime	Datetime	False	Date and time when the purchase order was created.
ModifiedTime	Datetime	False	Date and time when the purchase order was last modified.
Tag	String	False	Tags associated with the purchase order.
BillingStreet	String	False	Billing street address for the order.
ShippingStreet	String	False	Shipping street address for the order.
BillingCity	String	False	Billing city for the order.
ShippingCity	String	False	Shipping city for the order.
BillingState	String	False	Billing state for the order.
ShippingState	String	False	Shipping state for the order.
BillingCode	String	False	Billing postal code for the order.
ShippingCode	String	False	Shipping postal code for the order.
BillingCountry	String	False	Billing country for the order.
ShippingCountry	String	False	Shipping country for the order.
SubTotal	String	True	Subtotal amount before discounts and taxes.
Discount	Decimal	False	Discount applied to the purchase order.
Tax	Decimal	False	Tax applied to the purchase order.
Adjustment	Decimal	False	Manual adjustments made to the total.
GrandTotal	String	True	Total amount after discounts, taxes, and adjustments.
TermsandConditions	String	False	Terms and conditions related to the purchase order.
Description	String	False	Description or additional details of the purchase order.
PurchaseItems	String	False	List of items associated with the purchase order.
RecordId	Long	True	Internal record identifier.
Locked	Bool	True	Indicates whether the record is locked from editing.
LastActivityTime	Datetime	False	Date and time of the last activity associated with the record.

Pseudo-Columns

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


Name	Type	Description
DuplicateCheckFields	String	Fields used to check for duplicate records during an upsert operation. The REST API allows multiple fields; the BULK API allows only one.
Trigger	String	Specifies whether to trigger associated rules when inserting this record into the CRM account.
CustomViewId	Long	Custom view identifier used to filter this record when the useCOQL parameter is set to false.

SSIS Components for Zoho CRM