tag:blogger.com,1999:blog-15537263250151533472024-03-13T10:50:46.403-07:00Michael StottMichael Stotthttp://www.blogger.com/profile/18123475200360133432noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-1553726325015153347.post-86476044915130235052014-08-11T16:10:00.000-07:002014-08-11T16:10:41.768-07:00Linking Linnworks multi channel software to Xero AccountsHi all<br /><br />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.<br /><br />It is based on;<br />Working Quickbooks Integration: Use at own risk<br />http://www.linnsystems.com/supportforum/viewtopic.php?f=8&t=3113&p=13933&hilit=quickbooks+export#p13933<br />Postby Joshua_Clark on 23 Apr 2012 16:41<br /><br />I have modified the Sql script to match into a CSV format that Xero accounts likes.<br /><br />It is also pieced together from other examples on the forums so thank you to everyone who has made it possible.<br /><br />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.<br /><br />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!!<br /><br />My next port of call is to create a script to export Purchase Orders<br /><br />Many Thanks<br /><br />Michael<br /><br /><br />DECLARE <br />@FromDate as datetime,<br />@ToDate as datetime<br /><br />SET @FromDate = '2014.06.26 00:00:00.000'<br />SET @ToDate = '2014.08.07 23:59:59.999'<br /><br />SELECT<br />o.nOrderId AS 'OrderId',<br />oi.ItemNumber AS 'SKU',<br />si.ItemTitle AS 'Title', <br />'' AS 'PostageCost',<br />oi.CostIncTax AS 'UnitCost',<br />oi.nQty AS 'Quantity',<br />c.cCountry AS 'Country',<br />c.TaxRate AS 'CountryTaxRate',<br />o.Source AS 'Source',<br />o.SubSource AS 'SubSource',<br />o.dProcessedOn AS 'ProcessedDate',<br />cFullName AS 'ContactName',<br />o.cEmailAddress AS 'EmailAddress',<br />Address1 AS 'POAddressLine1',<br />Address2 AS 'POAddressLine1',<br />Address3 AS 'POAddressLine3',<br />'' AS 'POAddressLine4',<br />Town AS 'POCity',<br />Region AS 'PORegion',<br />cpostcode AS 'POPostalCode',<br />c.cCountry AS 'POCountry',<br />o.nOrderId AS 'InvoiceNumber',<br />ExternalReference AS 'Reference',<br />o.dProcessedOn AS 'InvoiceDate',<br />o.dProcessedOn AS 'DueDate',<br />fTotalCharge AS 'Total',<br />'' AS 'InventoryItemCode',<br />si.ItemTitle AS 'Description',<br />oi.nQty AS 'Quantity',<br />(oi.CostIncTax / NULLIF( oi.nQty,0 ) ) AS 'UnitAmount',<br />'' AS 'Discount',<br />'205' AS 'AccountCode',<br />'No VAT' AS 'TaxType',<br />'' AS 'TaxAmount',<br />'' AS 'TrackingName1',<br />'' AS 'TrackingOption1',<br />'' AS 'TrackingName2',<br />'' AS 'TrackingOption2',<br />'' AS 'Currency'<br /><br /><br />FROM [Order] o<br />LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID<br />LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed<br />INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId<br /><br />WHERE <br />o.bProcessed = 1 <br />AND (o.dProcessedOn BETWEEN @FromDate AND @ToDate)<br /><br />UNION<br />SELECT<br />o.nOrderId AS 'OrderId',<br />'Shipping' AS 'SKU',<br />'' AS 'Title', <br />o.fPostageCost AS 'PostageCost',<br />'' AS 'UnitCost',<br />'1' AS 'Quantity',<br />c.cCountry AS 'Country',<br />c.TaxRate AS 'CountryTaxRate',<br />o.Source AS 'Source',<br />o.SubSource AS 'SubSource',<br />o.dProcessedOn AS 'ProcessedDate',<br />cFullName AS 'ContactName',<br />o.cEmailAddress AS 'EmailAddress',<br />Address1 AS 'POAddressLine1',<br />Address2 AS 'POAddressLine1',<br />Address3 AS 'POAddressLine3',<br />'' AS 'POAddressLine4',<br />Town AS 'POCity',<br />Region AS 'PORegion',<br />cpostcode AS 'POPostalCode',<br />c.cCountry AS 'POCountry',<br />o.nOrderId AS 'InvoiceNumber',<br />ExternalReference AS 'Reference',<br />o.dProcessedOn AS 'InvoiceDate',<br />o.dProcessedOn AS 'DueDate',<br />fTotalCharge AS 'Total',<br />'' AS 'InventoryItemCode',<br />'Shipping' AS 'Description',<br />'1' AS 'Quantity',<br />o.fPostageCost AS 'UnitAmount',<br />'' AS 'Discount',<br />'205' AS 'AccountCode',<br />'No VAT' AS 'TaxType',<br />'' AS 'TaxAmount',<br />'' AS 'TrackingName1',<br />'' AS 'TrackingOption1',<br />'' AS 'TrackingName2',<br />'' AS 'TrackingOption2',<br />'' AS 'Currency'<br /><br /><br />FROM [Order] o<br />LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID<br />LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed<br />INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId<br /><br />WHERE <br />o.bProcessed = 1 <br />AND (o.dProcessedOn BETWEEN @FromDate AND @ToDate)<br />ORDER BY <br />o.nOrderId <br /><br />SELECT<br />o.nOrderId AS 'OrderId',<br />oi.ItemNumber AS 'SKU',<br />si.ItemTitle AS 'Title', <br />'' AS 'PostageCost',<br />oi.CostIncTax AS 'UnitCost',<br />oi.nQty AS 'Quantity',<br />c.cCountry AS 'Country',<br />c.TaxRate AS 'CountryTaxRate',<br />o.Source AS 'Source',<br />o.SubSource AS 'SubSource',<br />o.dProcessedOn AS 'ProcessedDate',<br />'' AS 'ContactName',<br />'' AS 'EmailAddress',<br />'' AS 'POAddressLine1',<br />'' AS 'POAddressLine1',<br />'' AS 'POAddressLine3',<br />'' AS 'POAddressLine4',<br />'' AS 'POCity',<br />'' AS 'PORegion',<br />'' AS 'POPostalCode',<br />'' AS 'POCountry',<br />'' AS 'InvoiceNumber',<br />'' AS 'Reference',<br />'' AS 'InvoiceDate',<br />'' AS 'DueDate',<br />'' AS 'Total',<br />'' AS 'InventoryItemCode',<br />'' AS 'Description',<br />'' AS 'Quantity',<br />'' AS 'UnitAmount',<br />'' AS 'Discount',<br />'' AS 'AccountCode',<br />'' AS 'TaxType',<br />'' AS 'TaxAmount',<br />'' AS 'TrackingName1',<br />'' AS 'TrackingOption1',<br />'' AS 'TrackingName2',<br />'' AS 'TrackingOption2',<br />'' AS 'Currency'<br /><br /><br />FROM [Order] o<br />LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID<br />LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed<br />INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId<br /><br />Michael Stotthttp://www.blogger.com/profile/18123475200360133432noreply@blogger.com0