Power BI Connector for Sage 50 UK

Build 24.0.9062

PurchaseOrders

Create, Update, Delete, and Query PurchaseOrders within Sage UK 50 Accounts.

Table Specific Information

The PurchaseOrders table allows you to select, insert, update, and delete purchase orders within a Sage 50 UK company dataset.

Select

The PurchaseOrders table returns the purchase orders for a Sage 50 UK company dataset.

SELECT * FROM PurchaseOrders WHERE Date >= '1/1/2015' AND Date < '2/1/2015'

Insert

To create a new PurchaseOrder record, the TradingAccountUUID field is required.

Purchase Order Line Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PurchaseOrderLineItems table.

The following example demonstrates how to insert a new PurchaseOrder with two line items:

INSERT INTO PurchaseOrders (TradingAccountUUID, DeliveryDate, PostalAddressName, PostalAddressType, PostalAddressAddress1, PostalAddressTownCity, PostalAddressStateRegion, ItemsAggregate) 
VALUES ('b83c940f-9d2b-4a35-bf42-28bbe07ef994', '05/05/2015', 'Purchase Order Billing Address', 'Billing', '123 Main St', 'Chapel Hill', 'NC', 
'<PurchaseOrderLineItems>
<Row><CommodityUUID>0a47b43d-7115-4863-9689-1a53123e67c8</CommodityUUID><Quantity>1</Quantity><ActualPrice>1.99</ActualPrice></Row>
<Row><CommodityUUID>586df2bb-7702-4228-b464-e8d509148547</CommodityUUID><Quantity>4</Quantity><ActualPrice>4.99</ActualPrice></Row>
</PurchaseOrderLineItems>')

Update

Any field that is not read-only can be updated.

When updating a PurchaseOrder record, PostalAddressUUID is required when updating a postal address. Note that purchase order line items cannot be updated nor deleted.


UPDATE PurchaseOrders SET PostalAddressZipPostCode = '12345'
WHERE PurchaseOrderUUID = '1b47b43a-7115-4863-9679-1a78636e67c1' AND PostalAddressUUID = '28915e09-b2a1-47e1-b193-741b32ba8ba0'

Delete

Deleting a PurchaseOrder record will also delete all line items and postal addresses associated with the PurchaseOrder.


DELETE FROM PurchaseOrders WHERE PurchaseOrderUUID = '1b47b43a-7115-4863-9679-1a78636e67c1'

Columns

Name Type ReadOnly Description
PurchaseOrderUUID [KEY] String True

PurchaseOrder UUID

TradingAccountUUID String False

Trading Account UUID associated with the PurchaseOrder

ContactUUID String True

PurchaseOrder Seller Contact UUID

PurchaseOrderNumber String False

PurchaseOrder Reference

Reference2 String False

PurchaseOrder Reference2

Status String False

PurchaseOrder Status

StatusFlagText String False

PurchaseOrder Status Flag Text

SupplierReference String False

PurchaseOrder Supplier Reference

CopyFlag Boolean False

PurchaseOrder Copy Flag

DeliveryDate Date False

PurchaseOrder Delivery Date

CarrierNetPrice Decimal False

PurchaseOrder Carrier Net Price

CarrierTaxPrice Decimal False

PurchaseOrder Carrier Tax Price

CarrierTotalPrice Decimal False

PurchaseOrder Carrier Gross Price

CarrierReference String False

PurchaseOrder Carrier reference

Currency String False

PurchaseOrder Currency

OCCurrencyExchangeRate Decimal False

PurchaseOrder Operating Company Currency Exchange Rate

OCCurrencyExchangeRateOperator String False

PurchaseOrder Operating Company Currency Exchange Rate Operator

Date Date False

PurchaseOrder Date

User String False

PurchaseOrder User

LineCount Decimal True

PurchaseOrder Line Count

Text1 String False

PurchaseOrder Text1

Text2 String False

PurchaseOrder Text2

NetTotal Decimal True

PurchaseOrder Net Total

TaxTotal Decimal True

PurchaseOrder Tax Total

GrossTotal Decimal True

PurchaseOrder Gross Total

OnOrder String False

PurchaseOrder On Order Status

ItemsAggregate String False

An aggregate of the line item data, which is used for adding a purchase order and its line items.

PostalAddressUUID String True

PurchaseOrder PostalAddress UUID

PostalAddressActive Boolean False

PurchaseOrder PostalAddress Active Flag

PostalAddressReference String False

PurchaseOrder PostalAddress Reference

PostalAddressName String False

PurchaseOrder PostalAddress Name

PostalAddressDescription String False

PurchaseOrder PostalAddress Description

PostalAddressAddress1 String False

PurchaseOrder PostalAddress Address 1

PostalAddressAddress2 String False

PurchaseOrder PostalAddress Address 2

PostalAddressAddress3 String False

PurchaseOrder PostalAddress Address 3

PostalAddressAddress4 String False

PurchaseOrder PostalAddress Address 4

PostalAddressTownCity String False

PurchaseOrder PostalAddress TownCity

PostalAddressCounty String False

PurchaseOrder PostalAddress County

PostalAddressStateRegion String False

PurchaseOrder PostalAddress StateRegion

PostalAddressZipPostCode String False

PurchaseOrder PostalAddress ZipPost Code

PostalAddressCountry String False

PurchaseOrder PostalAddress Country

PostalAddressPrimacyIndicator Boolean False

PurchaseOrder PostalAddress Primacy Indicator

PostalAddressType String False

PurchaseOrder PostalAddress Type. Sample values: Billing, Shipping.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Updated Datetime

An input used internally to filter records updated at a specific datetime.

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