SaleOrders
Returns all sale 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, BalanceDueAmount, Date, IsTaxInclusive, JournalMemo, Number, ReferralSource, Status, Subtotal, TotalAmount, TotalTax, LastPaymentDate, CustomerID, CustomerDisplayID, CustomerName, Freight, FreightTaxCodeCode, FreightTaxCodeID, CategoryID, CategoryDisplayID, CategoryName, SalespersonID, SalespersonDisplayID, SalespersonName, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsFinanceCharge, TermsMonthlyChargeForLatePayment, TermsPaymentIsDue. All the other columns and operators are processed client side.
OrderType supports only equality comparison.
SELECT * FROM SaleOrders WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71" SELECT * FROM SaleOrders WHERE BalanceDueAmount = 13 SELECT * FROM SaleOrders WHERE IsTaxInclusive = true
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 SaleOrderItems#TEMP (Total, AccountID, TaxCodeID) VALUES (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b") UPDATE SaleOrders SET OrderType = "Service", Lines = "SaleOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"
Insert
The following attribute is required when performing an insert: OrderType, CustomerID.
INSERT INTO SaleOrders (OrderType, CustomerID) VALUES ("item", "4635fd9e-82a7-4612-ae15-32c464ef7c48")
Columns
Name | Type | ReadOnly | References | Description |
ID [KEY] | Uuid | True |
Unique identifier in the form of a guid. | |
AppliedToDate | Decimal | True |
The date of the entry. | |
BalanceDueAmount | Decimal | True |
Amount still payable on the sales order. | |
Date | Datetime | True |
The date of the entry. | |
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 |
Sales Order number. | |
OrderType | String | False |
Type of the order. One of: Item,Service,Professional,TimeBilling,Miscellaneous. | |
ReferralSource | String | False |
Referral Source selected on the sale 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. | |
Status | String | True |
Order status can consist of the following: Open, ConvertedToInvoice. | |
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. | |
URI | String | True |
Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. | |
TotalAmount | Decimal | True |
Total amount of the sale order. | |
TotalTax | Decimal | True |
Total of all tax amounts applicable to the sale order. | |
LastPaymentDate | String | True |
The date of the entry. | |
CustomerID | Uuid | False |
Contacts.ID |
Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True |
Customer contact Card ID, can also be used as a unique customer contact identifier. | |
CustomerName | String | True |
Name of the customer contact. | |
CustomerURI | String | True |
Uniform resource identifier associated with the customer contact object. | |
Freight | Decimal | False |
Tax inclusive freight amount applicable to the sale order. | |
FreightTaxCodeCode | String | True |
3 digit tax code. | |
FreightTaxCodeID | Uuid | True |
TaxCodes.ID |
Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeURI | String | True |
Uniform resource identifier associated with the tax code object. | |
CategoryID | Uuid | False |
Unique category identifier in the form of a guid. | |
CategoryDisplayID | String | True |
Display id for the category. | |
CategoryName | String | True |
Name of the category. | |
CategoryURI | String | True |
Uniform resource identifier associated with the category object. | |
SalespersonID | Uuid | False |
Contacts.ID |
Unique identifier in the form of a guid. |
SalespersonDisplayID | String | True |
Employee contact Card ID. | |
SalespersonName | String | True |
Selected employee contact name. | |
SalespersonURI | String | True |
Uniform resource identifier associated with the employee. | |
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 | False |
% discount for early payment. | |
TermsDueDate | Datetime | True |
Date in which payment is due. | |
TermsFinanceCharge | Decimal | False |
Late payment fee to be charged if payment is not made in full by the due date. | |
TermsMonthlyChargeForLatePayment | Double | False |
% monthly charge for late payment. | |
TermsPaymentIsDue | String | False |
Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. | |
Lines | String | False |
An array of sale line order information. | |
CustomerPurchaseOrderNumber | String | False |
Customer PO number. |