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 criteria 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, 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 | References | 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 |
TaxCodes.ID |
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 |
Contacts.ID |
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. |