Power BI Connector for Sage 200

Build 20.0.7587

SalesOrderLines

When making larger requests for sales order information, the Sales Order Line Views resource is a faster alternative to the 'Sales Orders' resource. The Sales Order Line Views resource returns more information than the 'Sales Orders' resource, and therefore reduces the number of API requests required to return the same information from multiple resources. This resource returns a view of sales orders, sales order lines, customers, products, warehouses, tax codes and currencies that can be queried on any field. The default sort (orderby) field are by 'sop_order_id' and 'sop_order_line_id' descending.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process all search criteria.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM SalesOrderLines WHERE SalesOrderId=42101
SELECT * FROM SalesOrderLines WHERE CurrencyId=2343234543
SELECT * FROM SalesOrderLines WHERE CurrencyName='Sample'

Columns

Name Type References Description
SalesOrderId [KEY] Long Unique Id of the sales order. See sop_orders
CustomerDocumentNo String Customer sales order document number.
CurrencyId Long The Id for the currency used for the sales order. See currencies
CurrencyName String The name for the currency used for the sales order.
CurrencySymbol String The symbol of the currency used for the sales order.
DocumentDiscountPercent Decimal Document discount percent value, between -99.99 and 99.99. A negative value is treated as a surcharge (e.g. -10 is a 10% surcharge), and a positive value is treated as a discount.
DocumentCreatedBy String The person who created the SOP Order
RequestedDeliveryDate Date Requested delivery date.
PromisedDeliveryDate Date Promised delivery date.
LineId Long Associated sales order line Id. See sop_order_lines
LineNumber Short Sales order line number.
LineType String Sales order line type. See order_return_line_types
LineProductCode String Sales order line product code.
LineProductDescription String Sales order line product description. This could be stock item description, free text, additional charge or comment.
LineQuantity Decimal The quantity ordered.
LineAllocatedQuantity Decimal The quantity allocated.
LineDespatchReceiptQuantity Decimal The quantity despatched.
LineInvoiceCreditQuantity Decimal The quantity invoiced.
LineSellingUnitDescription String The selling unit description.
LineSellingUnitPrice Decimal The selling price per item.
LineUnitDiscountPercent Decimal The percentage discount per item.
LineUnitDiscountValue Decimal The discount value per item.
LineCostPrice Decimal The cost price.
LineTotalValue Decimal The total value of the line.
LineLineTaxValue Decimal The tax value of the line.
LineDiscountedUnitPrice Decimal The discounted unit price of the line. This is calculated by subtracting the sop_order_line_unit_discount_value from the sop_order_line_selling_unit_price.
LineRequestedDeliveryDate Date Requested delivery date.
LinePromisedDeliveryDate Date Promised delivery date.
LineAnalysisCode1 String Analysis code 1.
LineAnalysisCode2 String Analysis code 2.
LineAnalysisCode3 String Analysis code 3.
LineAnalysisCode4 String Analysis code 4.
LineAnalysisCode5 String Analysis code 5.
LineAnalysisCode6 String Analysis code 6.
LineAnalysisCode7 String Analysis code 7.
LineAnalysisCode8 String Analysis code 8.
LineAnalysisCode9 String Analysis code 9.
LineAnalysisCode10 String Analysis code 10.
LineAnalysisCode11 String Analysis code 11.
LineAnalysisCode12 String Analysis code 12.
LineAnalysisCode13 String Analysis code 13.
LineAnalysisCode14 String Analysis code 14.
LineAnalysisCode15 String Analysis code 15.
LineAnalysisCode16 String Analysis code 16.
LineAnalysisCode17 String Analysis code 17.
LineAnalysisCode18 String Analysis code 18.
LineAnalysisCode19 String Analysis code 19.
LineAnalysisCode20 String Analysis code 20.
LineDateTimeUpdated Datetime The date and time the sop order line was last updated (UTC).
DeliveryAddrAddress1 String SOP delivery address line 1.
DeliveryAddrAddress2 String SOP delivery address line 2.
DeliveryAddrAddress3 String SOP delivery address line 3.
DeliveryAddrAddress4 String SOP delivery address line 4.
DeliveryAddrCity String SOP delivery address city.
DeliveryAddrCounty String SOP delivery address county.
DeliveryAddrPostCode String SOP delivery address post code
DeliveryAddrCountry String SOP delivery address country.
DeliveryAddrContact String SOP delivery address contact
DeliveryAddrTelephone String SOP delivery address telephone number
DeliveryAddrFax String SOP delivery address line fax number...
DeliveryAddrEmail String SOP delivery address line email.
CustomerId Long The associated customer record Id. See customers
CustomerReference String The associated customer account reference.
CustomerName String The associated customer name.
ProductId Long The associated product record Id. Only returned where sop_order_line_type = EnumLineTypeStandard. See products
ProductCode String The code of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductName String The name of the product ordered. Only returned where sop_order_line_type = EnumLineTypeStandard
ProductDescription String The associated product description. Only returned where sop_order_line_type = EnumLineTypeStandard
WarehouseId Long The id of the warehouse the product is stored in. Only returned where sop_order_line_type = EnumLineTypeStandard. See warehouses
WarehouseName String The name of the warehouse. Only returned where sop_order_line_type = EnumLineTypeStandard
TaxCodeId Long The id of the tax code set on the line. See tax_codes
TaxCodeCode Short The associated tax code.
TaxCodeName String The associated tax code name.
TaxCodeRate Decimal The associated tax rate.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587