Excel Add-In for MYOB

Build 24.0.9062

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.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062