PurchaseOrders

Return all purchase order types for an AccountRight company file.

Table Specific Information

Select

MYOB allows a subset of columns to be used in the WHERE clause of a SELECT query. These columns may be used with the following operators: =, !=, >, <, >=, <=. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison types for server side filtering: ID, AppliedToDate, Date, Freight, BalanceDueAmount, IsReportable, IsTaxInclusive, JournalMemo, Number, ShipToAddress, Status, SupplierInvoiceNumber, Subtotal, TotalAmount, TotalTax, CategoryID, CategoryDisplayID, CategoryName, FreightTaxCodeID, FreightTaxCodeCode, SupplierID, SupplierDisplayID, SupplierName, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsPaymentIsDue. All the other columns and operators are processed client side. OrderType supports only equality comparison.

SELECT * FROM PurchaseOrders WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"
SELECT * FROM PurchaseOrders WHERE AppliedToDate = 40
SELECT * FROM PurchaseOrders WHERE OrderType = "Service"

Update

To update an existing order, along with its Lines, we can either pass a JSON string to the Lines value or use a temporary table like below. Note: this will replace all the current Lines with the ones below.

INSERT INTO PurchaseOrderItems#TEMP(Total, AccountID, TaxCodeID) values (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b")
UPDATE PurchaseOrders SET OrderType = "Service", Lines = "PurchaseOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"

Insert

The following attributes are required when performing an insert: OrderType, Date, FreightTaxCodeID, SupplierID.

INSERT INTO PurchaseOrders(OrderType, Date, FreightTaxCodeID, SupplierID) VALUES ("Service", "01/01/2019", "446d2226-254b-4821-acc2-7031842e0166", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd")

Columns


Name	Type	ReadOnly	Description
ID [KEY]	Uuid	True	Unique identifier in the form of a guid.
AppliedToDate	Double	True	Amount currently applied to the purchase order.
Date	Datetime	False	The date of the entry.
Freight	Decimal	False	Tax inclusive freight amount applicable to the purchase order.
BalanceDueAmount	Decimal	True	Amount still payable on the purchase order.
IsReportable	Boolean	False	ONLY applicable for AU region. True indicates the transaction is reportable taxable payment. Falseindicates the transaction is not reportable taxable payment.
IsTaxInclusive	Boolean	False	True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive.
JournalMemo	String	False	Memo text for the object.
Number	String	False	Purchase order number.
OrderType	String	False	Type of the order. One of: Item,Service,Professional,Miscellaneous
ShipToAddress	String	False	ShipTo address of the purchase order.
Status	String	True	Purchase Order status: Open, ConvertedToBill.
SupplierInvoiceNumber	String	False	Supplier invoice number.
Subtotal	Decimal	True	If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts.
TotalAmount	Decimal	True	Total amount of the purchase order.
TotalTax	Decimal	True	Total of all tax amounts applicable to the purchase order.
RowVersion	String	True	Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time.
URI	String	True	Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.
CategoryID	Uuid	True	Unique category identifier in the form of a guid.
CategoryDisplayID	String	False	Display id for the category.
CategoryName	String	True	Name of the category.
CategoryURI	String	True	Uniform resource identifier associated with the category object.
FreightTaxCodeID	Uuid	True	Unique guid identifier belonging to the assigned tax code.
FreightTaxCodeCode	String	False	3 digit tax code.
FreightTaxCodeURI	String	True	Uniform resource identifier associated with the tax code object.
Comment	String	False	Purchase Order Comment
ShippingMethod	String	False	Shipping Method
PromisedDate	Datetime	False	Transaction Promised Date.
SupplierID	Uuid	False	Unique guid identifier belonging to the assigned supplier contact.
SupplierDisplayID	String	True	Customer contact Card ID, can also be used as a unique supplier contact identifier.
SupplierName	String	True	Name of the supplier contact.
SupplierURI	String	True	Uniform resource identifier associated with the supplier contact object.
TermsBalanceDueDate	Integer	False	If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices.
TermsDiscount	Decimal	True	Discount amount that will apply if payment is made in full by the discount date.
TermsDiscountDate	Integer	True	If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts.
TermsDiscountExpiryDate	Datetime	True	Date in which payment must be paid in full in quote to receive discount.
TermsDiscountForEarlyPayment	Double	True	% discount for early payment.
TermsDueDate	Datetime	True	Date in which payment is due.
TermsPaymentIsDue	String	False	Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM.
Lines	String	False	An array of line order information.
CompanyFileId	String	True	The ID of the company file. Takes precedence over the CompanyFileId connection property.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.

Build 22.0.8462