Monday 11 August 2014

Linking Linnworks multi channel software to Xero Accounts

Hi all

If you are using Xero and are looking for a way to import your sales invoices into Xero then its worth having a look at this.

It is based on;
Working Quickbooks Integration: Use at own risk
http://www.linnsystems.com/supportforum/viewtopic.php?f=8&t=3113&p=13933&hilit=quickbooks+export#p13933
Postby Joshua_Clark on 23 Apr 2012 16:41

I have modified the Sql script to match into a CSV format that Xero accounts likes.

It is also pieced together from other examples on the forums so thank you to everyone who has made it possible.

Feel free to use it and improve it and you may need to modify it slightly for your own use as its been tailored to things like the account codes I use etc.

If you do make any improvements please share as I would be interested to see and also I can't take any responsibility for how well it works so please test it for yourself!!

My next port of call is to create a script to export Purchase Orders

Many Thanks

Michael


DECLARE
@FromDate as datetime,
@ToDate as datetime

SET @FromDate = '2014.06.26 00:00:00.000'
SET @ToDate = '2014.08.07 23:59:59.999'

SELECT
o.nOrderId AS 'OrderId',
oi.ItemNumber AS 'SKU',
si.ItemTitle AS 'Title',
'' AS 'PostageCost',
oi.CostIncTax AS 'UnitCost',
oi.nQty AS 'Quantity',
c.cCountry AS 'Country',
c.TaxRate AS 'CountryTaxRate',
o.Source AS 'Source',
o.SubSource AS 'SubSource',
o.dProcessedOn AS 'ProcessedDate',
cFullName AS 'ContactName',
o.cEmailAddress AS 'EmailAddress',
Address1 AS 'POAddressLine1',
Address2 AS 'POAddressLine1',
Address3 AS 'POAddressLine3',
'' AS 'POAddressLine4',
Town AS 'POCity',
Region AS 'PORegion',
cpostcode AS 'POPostalCode',
c.cCountry AS 'POCountry',
o.nOrderId AS 'InvoiceNumber',
ExternalReference AS 'Reference',
o.dProcessedOn AS 'InvoiceDate',
o.dProcessedOn AS 'DueDate',
fTotalCharge AS 'Total',
'' AS 'InventoryItemCode',
si.ItemTitle AS 'Description',
oi.nQty AS 'Quantity',
(oi.CostIncTax / NULLIF( oi.nQty,0 ) ) AS 'UnitAmount',
'' AS 'Discount',
'205' AS 'AccountCode',
'No VAT' AS 'TaxType',
'' AS 'TaxAmount',
'' AS 'TrackingName1',
'' AS 'TrackingOption1',
'' AS 'TrackingName2',
'' AS 'TrackingOption2',
'' AS 'Currency'


FROM [Order] o
LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed
INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId

WHERE
o.bProcessed = 1
AND (o.dProcessedOn BETWEEN @FromDate AND @ToDate)

UNION
SELECT
o.nOrderId AS 'OrderId',
'Shipping' AS 'SKU',
'' AS 'Title',
o.fPostageCost AS 'PostageCost',
'' AS 'UnitCost',
'1' AS 'Quantity',
c.cCountry AS 'Country',
c.TaxRate AS 'CountryTaxRate',
o.Source AS 'Source',
o.SubSource AS 'SubSource',
o.dProcessedOn AS 'ProcessedDate',
cFullName AS 'ContactName',
o.cEmailAddress AS 'EmailAddress',
Address1 AS 'POAddressLine1',
Address2 AS 'POAddressLine1',
Address3 AS 'POAddressLine3',
'' AS 'POAddressLine4',
Town AS 'POCity',
Region AS 'PORegion',
cpostcode AS 'POPostalCode',
c.cCountry AS 'POCountry',
o.nOrderId AS 'InvoiceNumber',
ExternalReference AS 'Reference',
o.dProcessedOn AS 'InvoiceDate',
o.dProcessedOn AS 'DueDate',
fTotalCharge AS 'Total',
'' AS 'InventoryItemCode',
'Shipping' AS 'Description',
'1' AS 'Quantity',
o.fPostageCost AS 'UnitAmount',
'' AS 'Discount',
'205' AS 'AccountCode',
'No VAT' AS 'TaxType',
'' AS 'TaxAmount',
'' AS 'TrackingName1',
'' AS 'TrackingOption1',
'' AS 'TrackingName2',
'' AS 'TrackingOption2',
'' AS 'Currency'


FROM [Order] o
LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed
INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId

WHERE
o.bProcessed = 1
AND (o.dProcessedOn BETWEEN @FromDate AND @ToDate)
ORDER BY
o.nOrderId

SELECT
o.nOrderId AS 'OrderId',
oi.ItemNumber AS 'SKU',
si.ItemTitle AS 'Title',
'' AS 'PostageCost',
oi.CostIncTax AS 'UnitCost',
oi.nQty AS 'Quantity',
c.cCountry AS 'Country',
c.TaxRate AS 'CountryTaxRate',
o.Source AS 'Source',
o.SubSource AS 'SubSource',
o.dProcessedOn AS 'ProcessedDate',
'' AS 'ContactName',
'' AS 'EmailAddress',
'' AS 'POAddressLine1',
'' AS 'POAddressLine1',
'' AS 'POAddressLine3',
'' AS 'POAddressLine4',
'' AS 'POCity',
'' AS 'PORegion',
'' AS 'POPostalCode',
'' AS 'POCountry',
'' AS 'InvoiceNumber',
'' AS 'Reference',
'' AS 'InvoiceDate',
'' AS 'DueDate',
'' AS 'Total',
'' AS 'InventoryItemCode',
'' AS 'Description',
'' AS 'Quantity',
'' AS 'UnitAmount',
'' AS 'Discount',
'' AS 'AccountCode',
'' AS 'TaxType',
'' AS 'TaxAmount',
'' AS 'TrackingName1',
'' AS 'TrackingOption1',
'' AS 'TrackingName2',
'' AS 'TrackingOption2',
'' AS 'Currency'


FROM [Order] o
LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed
INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId