Rounding of the final qty in version 06.05.13 stored proc
Posted: Sun Feb 04, 2018 11:00 pmHi Mike,
I received an email back from Beth that issues in V6 will not be corrected.
I am going to update client soon to V7, BUT need a fix if possible.
This is one of Alistair's client that I am taking over and they have doubts the inventory system is robust.
In the stored proc below, it gets the decimal places fro the Inventory ID, but I am ending up occasionally having a result displaying.
I am happy yo have the final result display to 3 decimal places as that is the most in the DB.
Don't mind if we can just force it display to three decimal places for every item.
Hopefully it might just be a quick fix at the bottom where it states
+ '<Quantity DecimalPlaces="' + CAST(@DecimalPlaces AS VARCHAR(10)) + '">' + CAST(Quantity AS VARCHAR(20)) + '</Quantity>'
USE [MCS2014-2015]
GO
/****** Object: StoredProcedure [dbo].[usp_JIWA_Inventory_BuildGrid_TransactionHistory] Script Date: 04/02/2018 7:48:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_JIWA_Inventory_BuildGrid_TransactionHistory] @InventoryID CHAR(20), @IN_LogicalID CHAR(20), @StartingDate DATETIME, @EndingDate DATETIME,@TransactionType VARCHAR(255) AS
SET NOCOUNT ON
DECLARE @XMLHeader VARCHAR(50)
DECLARE @GridDefinitionOpen VARCHAR(20)
DECLARE @GridDefinitionClose VARCHAR(20)
DECLARE @ColumnsOpen VARCHAR(20)
DECLARE @ColumnsClose VARCHAR(20)
DECLARE @ColumnOpen VARCHAR(20)
DECLARE @ColumnClose VARCHAR(20)
DECLARE @NameOpen VARCHAR(20)
DECLARE @NameClose VARCHAR(20)
DECLARE @KeyOpen VARCHAR(20)
DECLARE @KeyClose VARCHAR(20)
DECLARE @VisibleOpen VARCHAR(20)
DECLARE @VisibleClose VARCHAR(20)
DECLARE @TypeOpen VARCHAR(20)
DECLARE @TypeClose VARCHAR(20)
DECLARE @MaxLenOpen VARCHAR(20)
DECLARE @MaxLenClose VARCHAR(20)
DECLARE @WidthOpen VARCHAR(20)
DECLARE @WidthClose VARCHAR(20)
DECLARE @HiddenOpen VARCHAR(20)
DECLARE @HiddenClose VARCHAR(20)
DECLARE @AllowSortOpen VARCHAR(20)
DECLARE @AllowSortClose VARCHAR(20)
DECLARE @LockColumnOpen VARCHAR(20)
DECLARE @LockColumnClose VARCHAR(20)
DECLARE @DrillDownOpen VARCHAR(20)
DECLARE @DrillDownClose VARCHAR(20)
DECLARE @ShowTotalOpen VARCHAR(20)
DECLARE @ShowTotalClose VARCHAR(20)
DECLARE @ColumnString VARCHAR(8000)
DECLARE @DataOpen VARCHAR(20)
DECLARE @DataClose VARCHAR(20)
DECLARE @OrdersOnBackID CHAR(20)
DECLARE @OrderNo VARCHAR(50)
DECLARE @InvoiceID CHAR(20)
DECLARE @TranDate DATETIME
DECLARE @Quantity FLOAT
DECLARE @PartNo VARCHAR(50)
DECLARE @Description VARCHAR(200)
DECLARE @InvoiceNo VARCHAR(20)
DECLARE @DecimalPlaces SMALLINT
DECLARE @RootOpen VARCHAR(20)
DECLARE @RootClose VARCHAR(20)
DECLARE @RowOpen VARCHAR(20)
DECLARE @RowClose VARCHAR(20)
DECLARE @PhysicalWarehouseDescription VARCHAR(50)
DECLARE @LogicalWarehouseDescription VARCHAR(50)
DECLARE @Today DATETIME
DECLARE @MoneyDecimalPlaces TINYINT
DECLARE @TempTable TABLE
(
LineNumber INTEGER IDENTITY,
XMLString TEXT
)
CREATE TABLE #TempDataTable
(
TranDate varchar(50) COLLATE Database_Default,
Description varchar(255) COLLATE Database_Default,
Reference varchar(255) COLLATE Database_Default,
Quantity Float,
TransactionType VARCHAR(255) collate database_default,
UnitCost Float,
UnitSaleValue VARCHAR(20) collate database_default,
LinkID CHAR(20) collate database_default,
LogicalWarehouseDescription VARCHAR(50) collate database_default,
PhysicalWarehouseDescription VARCHAR(50) collate database_default,
DrillDownID VARCHAR(36) collate database_default
)
CREATE TABLE #TempTable
(
TransactionType VARCHAR(255) collate database_default,
PartNo VARCHAR(50) collate database_default,
Description VARCHAR(200) collate database_default,
ReferenceNo VARCHAR(255) collate database_default,
Details VARCHAR(255) collate database_default,
Quantity DECIMAL(19,6),
TransactionDate DATETIME,
LogicalWarehouseDescription VARCHAR(50) collate database_default,
PhysicalWarehouseDescription VARCHAR(50) collate database_default,
InventoryID CHAR(20) collate database_default,
IN_LogicalID CHAR(20) collate database_default,
DisplayOrder INTEGER,
LinkID CHAR(20) collate database_default,
UnitCost DECIMAL(19,6),
LineCost DECIMAL(19,6),
DrillDownID VARCHAR(36) collate database_default,
QuantityDecimalPlaces SMALLINT
)
SELECT @DecimalPlaces = DecimalPlaces FROM IN_Main WHERE InventoryID = @InventoryID
SELECT @MoneyDecimalPlaces = Contents FROM SY_SysValues WHERE Section='System' AND IDKey='MoneyDecimalPlaces'
IF @IN_LogicalID=''
BEGIN
SET @LogicalWarehouseDescription='ALL'
SET @PhysicalWarehouseDescription='ALL'
END
ELSE
BEGIN
SELECT @LogicalWarehouseDescription = Description FROM IN_Logical WHERE IN_LogicalID=@IN_LogicalID
SELECT @PhysicalWarehouseDescription = IN_Physical.Description FROM IN_Logical INNER JOIN IN_Physical ON IN_Logical.IN_PhysicalID=IN_Physical.IN_PhysicalID WHERE IN_Logical.IN_LogicalID=@IN_LogicalID
END
SET @Today=(SELECT GetDate())
SELECT @PartNo = PartNo FROM IN_Main WHERE InventoryID=@InventoryID
INSERT INTO #TempTable
EXEC usp_JIWA_QuantityAsAt @StartingDate,@EndingDate, @PartNo, @PartNo, @LogicalWarehouseDescription, @PhysicalWarehouseDescription, 2, 1
INSERT INTO #TempDataTable
SELECT TransactionDate,Details,ReferenceNo,SUM(Quantity),TransactionType,0,'',LinkID,LogicalWarehouseDescription,PhysicalWarehouseDescription,DrillDownID
FROM #TempTable
WHERE TransactionType<>'Opening Balance' AND TransactionType<>'Closing Balance'
GROUP BY TransactionDate,Details,ReferenceNo,Quantity,TransactionType,LinkID,LogicalWarehouseDescription,PhysicalWarehouseDescription,DrillDownID
IF UPPER(@TransactionType) <> 'ALL'
DELETE FROM #TempDataTable WHERE TransactionType <> @TransactionType
--UnitCost
UPDATE #TempDataTable
SET UnitCost = IN_SOH.LCostIn
FROM
#TempDataTable
INNER JOIN IN_SOH ON #TempDataTable.LinkID=IN_SOH.LinkID
--UnitSaleValue
UPDATE #TempDataTable
SET UnitSaleValue=SO_Lines.ItemPrice
FROM
#TempDataTable
INNER JOIN IN_SOH ON (#TempDataTable.LinkID = IN_SOH.LinkID)
INNER JOIN SO_LineDetails ON (SO_LineDetails.SOHID = IN_SOH.LinkID)
INNER JOIN SO_Lines ON (SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID)
INNER JOIN SO_History ON (SO_Lines.InvoiceHistoryID = SO_History.InvoiceHistoryID AND SO_History.InvoiceID = #TempDataTable.DrillDownID)
WHERE
TransactionType LIKE '%Sales Orders Outwards%'
UPDATE #TempDataTable
SET Quantity=Quantity*-1
WHERE
TransactionType LIKE '%Sales Orders Outwards%' OR
TransactionType LIKE '%Stock Take Write-Offs%' OR
TransactionType LIKE '%Stock Transfers Outwards%' OR
TransactionType LIKE '%Warehouse Transfers Outwards%' OR
TransactionType LIKE '%Job Costing Materials Used%' OR
TransactionType LIKE '%Job Costing Goods Received%' OR
TransactionType LIKE '%Return Authority Outwards%' OR
TransactionType LIKE '%Return Authority Credits%' OR
TransactionType LIKE '%Manufacturing Requisitions Used%' OR
TransactionType LIKE '%Service Manager Taken%' OR
TransactionType LIKE '%Service Manager Outwards (Processed)%' OR
TransactionType LIKE '%Assembly Used%' OR
TransactionType LIKE '%Return Authority Written Off%' OR
TransactionType LIKE '%Return Authority Returned To Supplier%'
SET @XMLHeader='<?xml version="1.0"?>'
SET @GridDefinitionOpen='<GridDefinition>'
SET @GridDefinitionClose='</GridDefinition>'
SET @ColumnsOpen='<Columns>'
SET @ColumnsClose='</Columns>'
SET @ColumnOpen='<Column>'
SET @ColumnClose='</Column>'
SET @NameOpen ='<Name>'
SET @NameClose ='</Name>'
SET @VisibleOpen ='<Visible>'
SET @VisibleClose ='</Visible>'
SET @TypeOpen ='<Type>'
SET @TypeClose ='</Type>'
SET @MaxLenOpen ='<MaxLen>'
SET @MaxLenClose ='</MaxLen>'
SET @DataOpen ='<Data>'
SET @DataClose ='</Data>'
SET @RootOpen ='<Root>'
SET @RootClose ='</Root>'
SET @RowOpen ='<Row>'
SET @RowClose ='</Row>'
SET @WidthOpen ='<Width>'
SET @WidthClose ='</Width>'
SET @HiddenOpen ='<Hidden>'
SET @HiddenClose ='</Hidden>'
SET @AllowSortOpen ='<AllowSort>'
SET @AllowSortClose ='</AllowSort>'
SET @LockColumnOpen ='<LockColumn>'
SET @LockColumnClose ='</LockColumn>'
SET @ShowTotalOpen ='<ShowTotal>'
SET @ShowTotalClose ='</ShowTotal>'
SET @KeyOpen='<Key>'
SET @KeyClose ='</Key>'
INSERT INTO @TempTable (XMLString)
SELECT @XMLHeader + @RootOpen + @GridDefinitionOpen + @ColumnsOpen
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Date' + @NameClose + @KeyOpen + 'Date' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeDate' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Description' + @NameClose + @KeyOpen + 'Description' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '40' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Reference' + @NameClose + @KeyOpen + 'Reference' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Quantity' + @NameClose + @KeyOpen + 'Quantity' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Unit Cost' + @NameClose + @KeyOpen + 'UnitCost' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Unit Sale Value' + @NameClose + @KeyOpen + 'UnitSaleValue' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Logical Warehouse' + @NameClose + @KeyOpen + 'LogicalWarehouseDescription' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Physical Warehouse' + @NameClose + @KeyOpen + 'PhysicalWarehouseDescription' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Transaction Type' + @NameClose + @KeyOpen + 'TransactionType' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '30' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnsClose + @GridDefinitionClose + @DataOpen
INSERT INTO @TempTable (XMLString)
SELECT @RowOpen + '<Date>' + TranDate + '</Date>'
+ '<Description>' + '<![CDATA[' + LTRIM(RTRIM(Description)) + ']]>' + '</Description>'
+
CASE
WHEN TransactionType = 'Receive Ordered Goods' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 410''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Shipment Book-Ins' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 421''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Credit Notes' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Purchase Invoices' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 411''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Finished Goods' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1290"''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisition Creations' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Take Write-Ons' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 124''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Warehouse Transfers Inwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 141''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Transfers Inwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 101''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Inwards (Processing)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisitions Reversed' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Assembly Creations' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 123''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards (Processed Sales Orders)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards (Unprocessed Sales Orders)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Take Write-Offs' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 124''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Transfers Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 101''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Warehouse Transfers Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 140''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Materials Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1210"''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Goods Received' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 410''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Return Authority Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1502''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Return Authority Credits' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1504''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisitions Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Taken' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Holding' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Outwards (Processed)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Assembly Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 123''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Credit Notes' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 302''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
ELSE '<Reference><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
END
+ '<Quantity DecimalPlaces="' + CAST(@DecimalPlaces AS VARCHAR(10)) + '">' + CAST(Quantity AS VARCHAR(20)) + '</Quantity>'
+ '<UnitCost DecimalPlaces="' + CAST(@MoneyDecimalPlaces AS VARCHAR(10)) + '">' + CAST(UnitCost AS VARCHAR(20)) + '</UnitCost>'
+ '<UnitSaleValue DecimalPlaces="' + CAST(@MoneyDecimalPlaces AS VARCHAR(10)) + '">' + CAST(UnitSaleValue AS VARCHAR(20)) + '</UnitSaleValue>'
+ '<LogicalWarehouseDescription>' + '<![CDATA[' + LTRIM(RTRIM(LogicalWarehouseDescription)) + ']]>' + '</LogicalWarehouseDescription>'
+ '<PhysicalWarehouseDescription>' + '<![CDATA[' + LTRIM(RTRIM(PhysicalWarehouseDescription)) + ']]>' + '</PhysicalWarehouseDescription>'
+ '<TransactionType>' + '<![CDATA[' + LTRIM(RTRIM(TransactionType)) + ']]>' + '</TransactionType>'
+ @RowClose
FROM #TempDataTable
ORDER BY #TempDataTable.TranDate
INSERT INTO @TempTable (XMLString)
SELECT @DataClose + @RootClose
SET NOCOUNT ON
SELECT XMLString FROM @TempTable ORDER By LineNumber
SET NOCOUNT OFF
GO
I received an email back from Beth that issues in V6 will not be corrected.
I am going to update client soon to V7, BUT need a fix if possible.
This is one of Alistair's client that I am taking over and they have doubts the inventory system is robust.
In the stored proc below, it gets the decimal places fro the Inventory ID, but I am ending up occasionally having a result displaying.
I am happy yo have the final result display to 3 decimal places as that is the most in the DB.
Don't mind if we can just force it display to three decimal places for every item.
Hopefully it might just be a quick fix at the bottom where it states
+ '<Quantity DecimalPlaces="' + CAST(@DecimalPlaces AS VARCHAR(10)) + '">' + CAST(Quantity AS VARCHAR(20)) + '</Quantity>'
USE [MCS2014-2015]
GO
/****** Object: StoredProcedure [dbo].[usp_JIWA_Inventory_BuildGrid_TransactionHistory] Script Date: 04/02/2018 7:48:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_JIWA_Inventory_BuildGrid_TransactionHistory] @InventoryID CHAR(20), @IN_LogicalID CHAR(20), @StartingDate DATETIME, @EndingDate DATETIME,@TransactionType VARCHAR(255) AS
SET NOCOUNT ON
DECLARE @XMLHeader VARCHAR(50)
DECLARE @GridDefinitionOpen VARCHAR(20)
DECLARE @GridDefinitionClose VARCHAR(20)
DECLARE @ColumnsOpen VARCHAR(20)
DECLARE @ColumnsClose VARCHAR(20)
DECLARE @ColumnOpen VARCHAR(20)
DECLARE @ColumnClose VARCHAR(20)
DECLARE @NameOpen VARCHAR(20)
DECLARE @NameClose VARCHAR(20)
DECLARE @KeyOpen VARCHAR(20)
DECLARE @KeyClose VARCHAR(20)
DECLARE @VisibleOpen VARCHAR(20)
DECLARE @VisibleClose VARCHAR(20)
DECLARE @TypeOpen VARCHAR(20)
DECLARE @TypeClose VARCHAR(20)
DECLARE @MaxLenOpen VARCHAR(20)
DECLARE @MaxLenClose VARCHAR(20)
DECLARE @WidthOpen VARCHAR(20)
DECLARE @WidthClose VARCHAR(20)
DECLARE @HiddenOpen VARCHAR(20)
DECLARE @HiddenClose VARCHAR(20)
DECLARE @AllowSortOpen VARCHAR(20)
DECLARE @AllowSortClose VARCHAR(20)
DECLARE @LockColumnOpen VARCHAR(20)
DECLARE @LockColumnClose VARCHAR(20)
DECLARE @DrillDownOpen VARCHAR(20)
DECLARE @DrillDownClose VARCHAR(20)
DECLARE @ShowTotalOpen VARCHAR(20)
DECLARE @ShowTotalClose VARCHAR(20)
DECLARE @ColumnString VARCHAR(8000)
DECLARE @DataOpen VARCHAR(20)
DECLARE @DataClose VARCHAR(20)
DECLARE @OrdersOnBackID CHAR(20)
DECLARE @OrderNo VARCHAR(50)
DECLARE @InvoiceID CHAR(20)
DECLARE @TranDate DATETIME
DECLARE @Quantity FLOAT
DECLARE @PartNo VARCHAR(50)
DECLARE @Description VARCHAR(200)
DECLARE @InvoiceNo VARCHAR(20)
DECLARE @DecimalPlaces SMALLINT
DECLARE @RootOpen VARCHAR(20)
DECLARE @RootClose VARCHAR(20)
DECLARE @RowOpen VARCHAR(20)
DECLARE @RowClose VARCHAR(20)
DECLARE @PhysicalWarehouseDescription VARCHAR(50)
DECLARE @LogicalWarehouseDescription VARCHAR(50)
DECLARE @Today DATETIME
DECLARE @MoneyDecimalPlaces TINYINT
DECLARE @TempTable TABLE
(
LineNumber INTEGER IDENTITY,
XMLString TEXT
)
CREATE TABLE #TempDataTable
(
TranDate varchar(50) COLLATE Database_Default,
Description varchar(255) COLLATE Database_Default,
Reference varchar(255) COLLATE Database_Default,
Quantity Float,
TransactionType VARCHAR(255) collate database_default,
UnitCost Float,
UnitSaleValue VARCHAR(20) collate database_default,
LinkID CHAR(20) collate database_default,
LogicalWarehouseDescription VARCHAR(50) collate database_default,
PhysicalWarehouseDescription VARCHAR(50) collate database_default,
DrillDownID VARCHAR(36) collate database_default
)
CREATE TABLE #TempTable
(
TransactionType VARCHAR(255) collate database_default,
PartNo VARCHAR(50) collate database_default,
Description VARCHAR(200) collate database_default,
ReferenceNo VARCHAR(255) collate database_default,
Details VARCHAR(255) collate database_default,
Quantity DECIMAL(19,6),
TransactionDate DATETIME,
LogicalWarehouseDescription VARCHAR(50) collate database_default,
PhysicalWarehouseDescription VARCHAR(50) collate database_default,
InventoryID CHAR(20) collate database_default,
IN_LogicalID CHAR(20) collate database_default,
DisplayOrder INTEGER,
LinkID CHAR(20) collate database_default,
UnitCost DECIMAL(19,6),
LineCost DECIMAL(19,6),
DrillDownID VARCHAR(36) collate database_default,
QuantityDecimalPlaces SMALLINT
)
SELECT @DecimalPlaces = DecimalPlaces FROM IN_Main WHERE InventoryID = @InventoryID
SELECT @MoneyDecimalPlaces = Contents FROM SY_SysValues WHERE Section='System' AND IDKey='MoneyDecimalPlaces'
IF @IN_LogicalID=''
BEGIN
SET @LogicalWarehouseDescription='ALL'
SET @PhysicalWarehouseDescription='ALL'
END
ELSE
BEGIN
SELECT @LogicalWarehouseDescription = Description FROM IN_Logical WHERE IN_LogicalID=@IN_LogicalID
SELECT @PhysicalWarehouseDescription = IN_Physical.Description FROM IN_Logical INNER JOIN IN_Physical ON IN_Logical.IN_PhysicalID=IN_Physical.IN_PhysicalID WHERE IN_Logical.IN_LogicalID=@IN_LogicalID
END
SET @Today=(SELECT GetDate())
SELECT @PartNo = PartNo FROM IN_Main WHERE InventoryID=@InventoryID
INSERT INTO #TempTable
EXEC usp_JIWA_QuantityAsAt @StartingDate,@EndingDate, @PartNo, @PartNo, @LogicalWarehouseDescription, @PhysicalWarehouseDescription, 2, 1
INSERT INTO #TempDataTable
SELECT TransactionDate,Details,ReferenceNo,SUM(Quantity),TransactionType,0,'',LinkID,LogicalWarehouseDescription,PhysicalWarehouseDescription,DrillDownID
FROM #TempTable
WHERE TransactionType<>'Opening Balance' AND TransactionType<>'Closing Balance'
GROUP BY TransactionDate,Details,ReferenceNo,Quantity,TransactionType,LinkID,LogicalWarehouseDescription,PhysicalWarehouseDescription,DrillDownID
IF UPPER(@TransactionType) <> 'ALL'
DELETE FROM #TempDataTable WHERE TransactionType <> @TransactionType
--UnitCost
UPDATE #TempDataTable
SET UnitCost = IN_SOH.LCostIn
FROM
#TempDataTable
INNER JOIN IN_SOH ON #TempDataTable.LinkID=IN_SOH.LinkID
--UnitSaleValue
UPDATE #TempDataTable
SET UnitSaleValue=SO_Lines.ItemPrice
FROM
#TempDataTable
INNER JOIN IN_SOH ON (#TempDataTable.LinkID = IN_SOH.LinkID)
INNER JOIN SO_LineDetails ON (SO_LineDetails.SOHID = IN_SOH.LinkID)
INNER JOIN SO_Lines ON (SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID)
INNER JOIN SO_History ON (SO_Lines.InvoiceHistoryID = SO_History.InvoiceHistoryID AND SO_History.InvoiceID = #TempDataTable.DrillDownID)
WHERE
TransactionType LIKE '%Sales Orders Outwards%'
UPDATE #TempDataTable
SET Quantity=Quantity*-1
WHERE
TransactionType LIKE '%Sales Orders Outwards%' OR
TransactionType LIKE '%Stock Take Write-Offs%' OR
TransactionType LIKE '%Stock Transfers Outwards%' OR
TransactionType LIKE '%Warehouse Transfers Outwards%' OR
TransactionType LIKE '%Job Costing Materials Used%' OR
TransactionType LIKE '%Job Costing Goods Received%' OR
TransactionType LIKE '%Return Authority Outwards%' OR
TransactionType LIKE '%Return Authority Credits%' OR
TransactionType LIKE '%Manufacturing Requisitions Used%' OR
TransactionType LIKE '%Service Manager Taken%' OR
TransactionType LIKE '%Service Manager Outwards (Processed)%' OR
TransactionType LIKE '%Assembly Used%' OR
TransactionType LIKE '%Return Authority Written Off%' OR
TransactionType LIKE '%Return Authority Returned To Supplier%'
SET @XMLHeader='<?xml version="1.0"?>'
SET @GridDefinitionOpen='<GridDefinition>'
SET @GridDefinitionClose='</GridDefinition>'
SET @ColumnsOpen='<Columns>'
SET @ColumnsClose='</Columns>'
SET @ColumnOpen='<Column>'
SET @ColumnClose='</Column>'
SET @NameOpen ='<Name>'
SET @NameClose ='</Name>'
SET @VisibleOpen ='<Visible>'
SET @VisibleClose ='</Visible>'
SET @TypeOpen ='<Type>'
SET @TypeClose ='</Type>'
SET @MaxLenOpen ='<MaxLen>'
SET @MaxLenClose ='</MaxLen>'
SET @DataOpen ='<Data>'
SET @DataClose ='</Data>'
SET @RootOpen ='<Root>'
SET @RootClose ='</Root>'
SET @RowOpen ='<Row>'
SET @RowClose ='</Row>'
SET @WidthOpen ='<Width>'
SET @WidthClose ='</Width>'
SET @HiddenOpen ='<Hidden>'
SET @HiddenClose ='</Hidden>'
SET @AllowSortOpen ='<AllowSort>'
SET @AllowSortClose ='</AllowSort>'
SET @LockColumnOpen ='<LockColumn>'
SET @LockColumnClose ='</LockColumn>'
SET @ShowTotalOpen ='<ShowTotal>'
SET @ShowTotalClose ='</ShowTotal>'
SET @KeyOpen='<Key>'
SET @KeyClose ='</Key>'
INSERT INTO @TempTable (XMLString)
SELECT @XMLHeader + @RootOpen + @GridDefinitionOpen + @ColumnsOpen
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Date' + @NameClose + @KeyOpen + 'Date' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeDate' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Description' + @NameClose + @KeyOpen + 'Description' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '40' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Reference' + @NameClose + @KeyOpen + 'Reference' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Quantity' + @NameClose + @KeyOpen + 'Quantity' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Unit Cost' + @NameClose + @KeyOpen + 'UnitCost' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Unit Sale Value' + @NameClose + @KeyOpen + 'UnitSaleValue' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'TRUE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Logical Warehouse' + @NameClose + @KeyOpen + 'LogicalWarehouseDescription' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Physical Warehouse' + @NameClose + @KeyOpen + 'PhysicalWarehouseDescription' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '12' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Transaction Type' + @NameClose + @KeyOpen + 'TransactionType' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '30' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnsClose + @GridDefinitionClose + @DataOpen
INSERT INTO @TempTable (XMLString)
SELECT @RowOpen + '<Date>' + TranDate + '</Date>'
+ '<Description>' + '<![CDATA[' + LTRIM(RTRIM(Description)) + ']]>' + '</Description>'
+
CASE
WHEN TransactionType = 'Receive Ordered Goods' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 410''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Shipment Book-Ins' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 421''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Credit Notes' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Purchase Invoices' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 411''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Finished Goods' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1290"''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisition Creations' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Take Write-Ons' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 124''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Warehouse Transfers Inwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 141''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Transfers Inwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 101''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Inwards (Processing)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisitions Reversed' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Assembly Creations' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 123''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards (Processed Sales Orders)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards (Unprocessed Sales Orders)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 300''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Take Write-Offs' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 124''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Stock Transfers Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 101''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Warehouse Transfers Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 140''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Materials Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1210"''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Job Costing Goods Received' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 410''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Return Authority Outwards' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1502''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Return Authority Credits' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1504''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Manufacturing Requisitions Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1401''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Taken' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Holding' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Service Manager Outwards (Processed)' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 1000''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Assembly Used' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 123''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
WHEN TransactionType = 'Sales Orders Credit Notes' THEN '<Reference DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + DrillDownID + '", 302''><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
ELSE '<Reference><![CDATA[' + LTRIM(RTRIM(Reference)) + ']]></Reference>'
END
+ '<Quantity DecimalPlaces="' + CAST(@DecimalPlaces AS VARCHAR(10)) + '">' + CAST(Quantity AS VARCHAR(20)) + '</Quantity>'
+ '<UnitCost DecimalPlaces="' + CAST(@MoneyDecimalPlaces AS VARCHAR(10)) + '">' + CAST(UnitCost AS VARCHAR(20)) + '</UnitCost>'
+ '<UnitSaleValue DecimalPlaces="' + CAST(@MoneyDecimalPlaces AS VARCHAR(10)) + '">' + CAST(UnitSaleValue AS VARCHAR(20)) + '</UnitSaleValue>'
+ '<LogicalWarehouseDescription>' + '<![CDATA[' + LTRIM(RTRIM(LogicalWarehouseDescription)) + ']]>' + '</LogicalWarehouseDescription>'
+ '<PhysicalWarehouseDescription>' + '<![CDATA[' + LTRIM(RTRIM(PhysicalWarehouseDescription)) + ']]>' + '</PhysicalWarehouseDescription>'
+ '<TransactionType>' + '<![CDATA[' + LTRIM(RTRIM(TransactionType)) + ']]>' + '</TransactionType>'
+ @RowClose
FROM #TempDataTable
ORDER BY #TempDataTable.TranDate
INSERT INTO @TempTable (XMLString)
SELECT @DataClose + @RootClose
SET NOCOUNT ON
SELECT XMLString FROM @TempTable ORDER By LineNumber
SET NOCOUNT OFF
GO