Help with script - adding isBusinessOrder (or notes) (DESKTOP)
Fedor Dzjuba
said
over 3 years ago
- Whether the order is Amazon B2B is stored in [order].[order_identifier] table.
- The actual Id for Amazon B2B is stored in [order].[identifier] You can get the pkIdentifierId with Sub Query
(SELECTTOP1 pkIdentifierId FROM [order].[identifier] orderIdentifier WHERE orderIdentifier.Tag='AMAZON_BUSINESS') - Then its a matter of joining dbo.[order] table to [order].[order_identifier] and if record exists its a B2B order:
LEFT OUTER JOIN (SELECT
norderid
FROM
[order].[order_identifier]
WHERE fkIdentifierId = (SELECT TOP 1 pkIdentifierId FROM [order].[identifier] orderIdentifier WHERE orderIdentifier.Tag='AMAZON_BUSINESS')
GROUP BY norderid
) as b2b ON b2b.norderid= o.nOrderId
I have taken the liberty and went into your system and added Custom Query called Order Details With Amazon B2B, its the same query as Order Details but with additional column IsB2B. You can see the syntax of the query by Editing the query.
Please use Linnworks.net to run such queries, it has proper schema separation and controls the load on the queries properly. Linnworks Desktop doesn't and the query might not run.
Jacqui Connor
Can anyone help me tweak this report for Desktop data custum script. I need to add a column to show if an order is an Amazon B2B business order because the tax is being reported wrong otherwise.
I know the column exists in a .net export so I know it can be done but it doesn't have a field for 'Country Tax Rate' and I need both.
This is the report that works but without the extra 'isbusinessorder' column that I need (with true/false output). Without the business order column the other option I tried was adding notes because it does add a note to each business order but I just got 'The multi-part identifier could not be bound'
DECLARE
@FromDate as datetime,
@ToDate as datetime
SET @FromDate = '2013.01.01 00:00:00.000'
SET @ToDate = '2200.12.31 23:59:59.999'
SELECT
o.nOrderId AS 'OrderId',
OrderItemSKU = isnull(si.ItemNumber,oi.ChannelSKU),
si.ItemTitle AS 'Title',
o.fPostageCost AS 'PostageCost',
oi.CostIncTax AS 'UnitCost',
oi.Taxrate AS 'VATRate',
oi.CostIncTax - oi.Cost AS 'Tax amount',
Total = o.fTotalCharge,
oi.nQty AS 'Quantity',
pc.CategoryName AS 'ProductCategory',
c.cCountry AS 'Country',
o.cCurrency AS 'Currency',
c.TaxRate AS 'CountryTaxRate',
o.cFullName AS 'Customer Name',
o.Source AS 'Source',
o.SubSource AS 'SubSource',
o.dProcessedOn AS 'ProcessedDate'
FROM [Order] o
LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed
LEFT OUTER JOIN ProductCategories pc on pc.CategoryId = si.CategoryId
INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId
WHERE
o.bProcessed = 1
AND (o.dProcessedOn BETWEEN '07/01/2019' AND '08/01/2019')
ORDER BY
o.nOrderId