JDBC Driver for MYOB

Build 22.0.8462

SaleInvoices

Return all sale invoice 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, BalanceDueAmount, Comment, Date, InvoiceDeliveryStatus, IsTaxInclusive, JournalMemo, LastPaymentDate, Number, PromisedDate, ReferralSource, ShipToAddress, ShippingMethod, Status, Subtotal, Freight, TotalTax, TotalAmount, CustomerPurchaseOrderNumber, CategoryID, CategoryDisplayID, CategoryName, SalespersonID, SalespersonDisplayID, SalespersonName, CustomerID, CustomerDisplayID, CustomerName, FreightTaxCodeID, FreightTaxCodeCode, OrderID, OrderNumber, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsFinanceCharge, TermsMonthlyChargeForLatePayment, TermsPaymentIsDue. All the other columns and operators are processed client side. InvoiceType supports only equality comparison. 

SELECT * FROM SaleInvoices WHERE ID = "fa024423-e61a-44cd-8a8b-4d52a2f9fc04"
SELECT * FROM SaleInvoices WHERE BalanceDueAmount = 24.3766206457717
SELECT * FROM SaleInvoices WHERE Comment = "commect"
SELECT * FROM SaleInvoices WHERE InvoiceType = "Service"

Update

To update an existing invoice, 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 InvoiceType = "Service", Lines = "PurchaseOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"

Insert

The following attributes are required when performing an insert: InvoiceType, Date, CustomerID. 

INSERT INTO SaleInvoices(InvoiceType, Date, CustomerID) VALUES ("item", "01/01/2019", "4635fd9e-82a7-4612-ae15-32c464ef7c48")

Columns

Name Type ReadOnly Description
ID [KEY] Uuid True

Unique identifier in the form of a guid.

BalanceDueAmount Decimal True

Amount still payable on the sale invoice.

Comment String False

Sale invoice comment.

Date Datetime False

The date of the entry.

InvoiceDeliveryStatus String False

Invoice delivery status assigned: Print = ToBePrinted, Email = ToBeEmailed, PrintAndEmail = ToBePrintedAndEmailed, Nothing = AlreadyPrintedOrSent.

InvoiceType String False

Type of the invoice. One of: Item,Service,Professional,TimeBilling,Miscellaneous.

IsTaxInclusive Boolean False

True indicates the transaction is tax inclusive with Total values to be keyed in tax-inclusive. False indicates the transaction is not tax inclusive with Total values to be keyed in tax-exclusive.

JournalMemo String False

Memo text for the object.

LastPaymentDate Datetime True

The date of the entry.

LastModified Datetime True

LastModified date of the entry.

Number String False

Sale invoice number.

PromisedDate Datetime False

The date of the entry.

ReferralSource String False

Referral Source selected on the sale invoice.

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.

ShipToAddress String False

ShipTo address of the sale invoice.

ShippingMethod String False

Shipping method text.

Status String True

Invoice status: Open, Closed, Credit.

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.

Freight Decimal True

Freight applicable to the sale invoice.

TotalTax Decimal True

Total of all tax amounts applicable to the sale invoice.

TotalAmount Decimal True

Total amount of the sale invoice.

URI String True

Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.

CustomerPurchaseOrderNumber String False

Customer PO number.

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

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.

CustomerID Uuid False

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.

FreightTaxCodeID Uuid False

Unique guid identifier belonging to the assigned tax code.

FreightTaxCodeCode String True

3 digit tax code.

FreightTaxCodeURI String True

Uniform resource identifier associated with the tax code object.

OrderID Uuid False

Unique identifier in the form of a guid.

OrderNumber String True

The order number.

OrderURI String True

Uniform resource identifier associated with the order.

TermsBalanceDueDate Integer False

The date of the entry.

TermsDiscount Decimal True

Discount amount that will apply if payment is made in full by the discount date.

TermsDiscountDate Integer True

The date of the entry.

TermsDiscountExpiryDate Datetime True

The date of the entry.

TermsDiscountForEarlyPayment Double False

% discount for early payment.

TermsDueDate Datetime True

The date of the entry.

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 line invoice 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