robin wrote:1) Under sales orders when you hover over an items descriptions, if the column length is too short it creates a floating text box with the full description, can that be implemented into quotes?
I've taken a look and you cannot do this via breakout - it needs to be put into the code. I've logged this as bug #7243.
robin wrote:2)Under debtors, customer activities, quotes. Is it possible to get the reference field to show, as there is no option under grid manager.
I would assume I need to create a custom field and then assign that too reference.
It's actually quite easy. That grid's data is generated from XML from a stored procedure. Which stored procedure is used is also configurable.
The standard one used is usp_JIWA_Debtor_BuildGrid_OnQuotes. You just need to copy that (give it your own name) and customise the stored proc to suit, and then modify the system setting controlling which stored proc to use.
So, here's what you do :
1. Run this script to create your own custom stored procedure (I copied our standard usp_JIWA_Debtor_BuildGrid_OnQuotes one and just added the reference field).
- Code: Select all
CREATE PROCEDURE [dbo].[usp_NETSECURITY_Debtor_BuildGrid_OnQuotes]
@DebtorID CHAR(20),
@OpportunityStatus SmallInt,
@OpportunityStatusReasonID Char(36),
@SalesStageID Char(36),
@SalesStagePercentageStartingFrom SmallInt,
@FilterExpectedCloseDate SmallInt,
@ExpectedCloseDate DateTime,
@ShowDetail SmallInt
AS
SET NOCOUNT ON
/*
@OpportunityStatus = -1 = ALL
@OpportunityStatus = 0 = OnGoing
@OpportunityStatus = 1 = Won
@OpportunityStatus = 2 = Lost
@OpportunityStatusReasonID = Blank = ALL, otherwise use supplied ID
@SalesStageID = Blank = ALL, otherwise use supplied ID
@SalesStagePercentageStartingFrom = -1 = Ignore, use @SalesStageID value instead, otherwise return all where Percentage complete >= @SalesStagePercentageStartingFrom
@FilterExpectedCloseDate = -1 = No Filter
@FilterExpectedCloseDate = 0 Filter to Everyhing BEFORE @ExpectedCloseDate
@FilterExpectedCloseDate = 1 Filter to Everyhing AFTER @ExpectedCloseDate
@ShowDetail = 0 = Summary Only
@ShowDetail = 1 = Show Quote Lines (Detail)
*/
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 @InventoryID 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 @SQLString VARCHAR(5000)
CREATE TABLE #TEMP_QuoteTable
(
InvoiceHistoryID char(20) COLLATE Database_Default,
InvoiceInitDate DateTime,
ExpectedCloseDate DateTime,
OpportunityStatus TINYINT,
SalesStageID Char(36) COLLATE Database_Default,
PercentComplete SmallInt,
OpportunityStatusReasonID Char(36) COLLATE Database_Default
)
DECLARE @TempTable TABLE
(
LineNumber INTEGER IDENTITY,
XMLString TEXT
)
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 + 'QuoteID' + @NameClose + @KeyOpen + 'QuoteID' + @KeyClose + @VisibleOpen + 'FALSE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '0' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Quote No' + @NameClose + @KeyOpen + 'QuoteNo' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'FALSE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Quote Date' + @NameClose + @KeyOpen + 'QuoteDate' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeDate' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '8' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Order No' + @NameClose + @KeyOpen + 'OrderNo' + @KeyClose + @VisibleOpen + 'FALSE' + @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 + 'Reference' + @NameClose + @KeyOpen + 'Reference' + @KeyClose + @VisibleOpen + 'FALSE' + @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 + 'HistoryID' + @NameClose + @KeyOpen + 'HistoryID' + @KeyClose + @VisibleOpen + 'FALSE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '0' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'History No' + @NameClose + @KeyOpen + 'HistoryNo' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '5' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'FALSE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Quote Value' + @NameClose + @KeyOpen + 'QuoteValue' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'FALSE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Expected Close Date' + @NameClose + @KeyOpen + 'ExpectedCloseDate' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeDate' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '8' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + '% Complete' + @NameClose + @KeyOpen + 'PercentComplete' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '8' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'FALSE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Sales Stage' + @NameClose + @KeyOpen + 'SalesStage' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '20' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Opportunity Status' + @NameClose + @KeyOpen + 'OpportunityStatus' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '8' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Opportunity Status Reason' + @NameClose + @KeyOpen + 'OpportunityStatusReason' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '15' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Expiry Date' + @NameClose + @KeyOpen + 'ExpiryDate' + @KeyClose + @VisibleOpen + 'FALSE' + @VisibleClose + @TypeOpen + 'CellTypeDate' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '8' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
If @ShowDetail <> 0
BEGIN
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'InventoryID' + @NameClose + @KeyOpen + 'InventoryID' + @KeyClose + @VisibleOpen + 'FALSE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '0' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Part No' + @NameClose + @KeyOpen + 'PartNo' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeEdit' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '15' + @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 + '25' + @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 + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Decimal Places' + @NameClose + @KeyOpen + 'DecimalPlaces' + @KeyClose + @VisibleOpen + 'FALSE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '0' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'FALSE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
INSERT INTO @TempTable (XMLString)
SELECT @ColumnOpen + @NameOpen + 'Unit Price' + @NameClose + @KeyOpen + 'UnitPrice' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @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 + 'Line Total' + @NameClose + @KeyOpen + 'LineTotal' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @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 + 'Last Cost' + @NameClose + @KeyOpen + 'LastCost' + @KeyClose + @VisibleOpen + 'TRUE' + @VisibleClose + @TypeOpen + 'CellTypeNumber' + @TypeClose + @MaxLenOpen + '255' + @MaxLenClose + @WidthOpen + '10' + @WidthClose + @HiddenOpen + 'FALSE' + @HiddenClose + @AllowSortOpen + 'TRUE' + @AllowSortClose + @LockColumnOpen + 'TRUE' + @LockColumnClose + @ShowTotalOpen + 'FALSE' + @ShowTotalClose + @ColumnClose
END
INSERT INTO @TempTable (XMLString)
SELECT @ColumnsClose + @GridDefinitionClose + @DataOpen
SET @SQLString = 'INSERT INTO #TEMP_QuoteTable SELECT QO_History.InvoiceHistoryID, QO_Main.InvoiceInitDate,
QO_History.ExpectedCloseDate, QO_History.OpportunityStatus, QO_SalesStages.RecID, QO_SalesStages.PercentComplete,
QO_OpportunityStatusReasons.RecID
FROM QO_History
JOIN QO_Main ON (QO_Main.InvoiceID = QO_History.InvoiceID)
JOIN QO_SalesStages ON (QO_SalesStages.RecID = QO_History.SalesStageID)
JOIN QO_OpportunityStatusReasons ON (QO_OpportunityStatusReasons.RecID = QO_History.OpportunityStatusReasonID)
WHERE QO_Main.DebtorID = ' + char(39) + @DebtorID + char(39)
EXEC(@SQLString)
IF @OpportunityStatus <> -1
DELETE FROM #TEMP_QuoteTable WHERE OpportunityStatus <> @OpportunityStatus
IF @OpportunityStatusReasonID <> ''
DELETE FROM #TEMP_QuoteTable WHERE OpportunityStatusReasonID <> @OpportunityStatusReasonID
IF @SalesStageID <> ''
DELETE FROM #TEMP_QuoteTable WHERE SalesStageID <> @SalesStageID
IF @SalesStagePercentageStartingFrom <> -1
DELETE FROM #TEMP_QuoteTable WHERE PercentComplete < @SalesStagePercentageStartingFrom
If @FilterExpectedCloseDate = 0
DELETE FROM #TEMP_QuoteTable WHERE DATEDIFF(day, @ExpectedCloseDate, ExpectedCloseDate) > 1
If @FilterExpectedCloseDate = 1
DELETE FROM #TEMP_QuoteTable WHERE DATEDIFF(day, @ExpectedCloseDate, ExpectedCloseDate) < 1
IF @ShowDetail <> 0
BEGIN
INSERT INTO @TempTable (XMLString)
SELECT @RowOpen + '<QuoteID>' + QO_Main.InvoiceID + '</QuoteID>'
+ '<QuoteNo DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + QO_Main.InvoiceID + '", 450''><![CDATA[' + QO_Main.InvoiceNo + ']]></QuoteNo>'
+ '<QuoteDate>' + CAST(QO_Main.InvoiceInitDate AS VARCHAR(50)) + '</QuoteDate>'
+ '<HistoryID>' + QO_History.InvoiceHistoryID + '</HistoryID>'
+ '<HistoryNo>' + CAST(QO_History.HistoryNo AS VARCHAR(10)) + '</HistoryNo>'
+ '<QuoteValue DecimalPlaces="' + CAST(2 AS VARCHAR(10)) + '"> '
+ CAST(QO_History.HistoryTotal AS VARCHAR(20)) + '</QuoteValue>'
+ '<ExpiryDate>' + CAST(QO_History.ExpiryDate AS VARCHAR(50)) + '</ExpiryDate>'
+ '<ExpectedCloseDate>' + CAST(QO_History.ExpectedCloseDate AS VARCHAR(50)) + '</ExpectedCloseDate>'
+ '<OpportunityStatus><![CDATA[' + CASE QO_History.OpportunityStatus WHEN 0 THEN 'OnGoing' WHEN 1 THEN 'Won' WHEN 2 THEN 'Lost' END + ']]></OpportunityStatus>'
+ '<OpportunityStatusReason><![CDATA[' + QO_OpportunityStatusReasons.Description + ']]></OpportunityStatusReason>'
+ '<SalesStage><![CDATA[' + QO_SalesStages.Description + ']]></SalesStage>'
+ '<PercentComplete>' + CAST(QO_SalesStages.PercentComplete AS VARCHAR(3)) + '</PercentComplete>'
+ '<InventoryID>' + QO_Lines.InventoryID + '</InventoryID>'
+ '<PartNo DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + QO_Lines.InventoryID + '", 100''><![CDATA[' + QO_Lines.PartNo + ']]></PartNo>'
+ '<Description DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + QO_Lines.InventoryID + '", 100''><![CDATA[' + QO_Lines.Description + ']]></Description>'
+ '<Quantity DecimalPlaces="' + CAST(QO_Lines.DecimalPlaces AS VARCHAR(10))
+ '" DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + QO_Lines.InventoryID + '", 108,TRUE''>'
+ CAST(QO_Lines.QuantityOrdered AS VARCHAR(20)) + '</Quantity>'
+ '<UnitPrice DecimalPlaces="2 ' + '"> ' + CAST(QO_Lines.ItemPrice AS VARCHAR(20)) + '</UnitPrice>'
+ '<LineTotal DecimalPlaces="2 ' + '"> ' + CAST(QO_Lines.CurrentLineTotal AS VARCHAR(20)) + '</LineTotal>'
+ '<LastCost DecimalPlaces="2 ' + '"> ' + CAST(QO_Lines.CostIn AS VARCHAR(20)) + '</LastCost>'
+ @RowClose
FROM #TEMP_QuoteTable
JOIN QO_History ON (#TEMP_QuoteTable.InvoiceHistoryID = QO_History.InvoiceHistoryID)
JOIN QO_Main ON (QO_Main.InvoiceID = QO_History.InvoiceID)
JOIN QO_Lines ON (QO_Lines.InvoiceHistoryID = QO_History.InvoiceHistoryID)
JOIN QO_SalesStages ON (QO_SalesStages.RecID = QO_History.SalesStageID)
JOIN QO_OpportunityStatusReasons ON (QO_OpportunityStatusReasons.RecID = QO_History.OpportunityStatusReasonID)
WHERE QO_Main.DebtorID = @DebtorID
AND (QO_History.OpportunityStatus = @OpportunityStatus OR @OpportunityStatus = -1)
AND (QO_History.OpportunityStatusReasonID = @OpportunityStatusReasonID OR @OpportunityStatusReasonID = '')
AND (((QO_History.SalesStageID = @SalesStageID OR @SalesStageID = '') AND @SalesStagePercentageStartingFrom = -1) OR (@SalesStagePercentageStartingFrom <> -1 AND QO_SalesStages.PercentComplete >= @SalesStagePercentageStartingFrom))
AND QO_Lines.CommentLine = 0
AND QO_Lines.TypeKitRounding = 0
ORDER BY QO_Main.InvoiceInitDate
END
ELSE
BEGIN
INSERT INTO @TempTable (XMLString)
SELECT @RowOpen + '<QuoteID>' + QO_Main.InvoiceID + '</QuoteID>'
+ '<QuoteNo DrillDown=''TRUE'' DrillDownCode=''MDIParent.DrillDownByFormNumber "' + QO_Main.InvoiceID + '", 450''><![CDATA[' + QO_Main.InvoiceNo + ']]></QuoteNo>'
+ '<QuoteDate>' + CAST(QO_Main.InvoiceInitDate AS VARCHAR(50)) + '</QuoteDate>'
+ '<OrderNo>' + QO_Main.OrderNo + '</OrderNo>'
+ '<Reference>' + QO_Main.QOReference + '</Reference>'
+ '<HistoryID>' + QO_History.InvoiceHistoryID + '</HistoryID>'
+ '<HistoryNo>' + CAST(QO_History.HistoryNo AS VARCHAR(10)) + '</HistoryNo>'
+ '<QuoteValue DecimalPlaces="' + CAST(2 AS VARCHAR(10)) + '"> '
+ CAST(QO_History.HistoryTotal AS VARCHAR(20)) + '</QuoteValue>'
+ '<ExpiryDate>' + CAST(QO_History.ExpiryDate AS VARCHAR(50)) + '</ExpiryDate>'
+ '<ExpectedCloseDate>' + CAST(QO_History.ExpectedCloseDate AS VARCHAR(50)) + '</ExpectedCloseDate>'
+ '<OpportunityStatus><![CDATA[' + CASE QO_History.OpportunityStatus WHEN 0 THEN 'OnGoing' WHEN 1 THEN 'Won' WHEN 2 THEN 'Lost' END + ']]></OpportunityStatus>'
+ '<OpportunityStatusReason><![CDATA[' + QO_OpportunityStatusReasons.Description + ']]></OpportunityStatusReason>'
+ '<SalesStage><![CDATA[' + QO_SalesStages.Description + ']]></SalesStage>'
+ '<PercentComplete>' + CAST(QO_SalesStages.PercentComplete AS VARCHAR(3)) + '</PercentComplete>'
+ @RowClose
FROM #TEMP_QuoteTable
JOIN QO_History ON (#TEMP_QuoteTable.InvoiceHistoryID = QO_History.InvoiceHistoryID)
JOIN QO_Main ON (QO_Main.InvoiceID = QO_History.InvoiceID)
JOIN QO_SalesStages ON (QO_SalesStages.RecID = QO_History.SalesStageID)
JOIN QO_OpportunityStatusReasons ON (QO_OpportunityStatusReasons.RecID = QO_History.OpportunityStatusReasonID)
ORDER BY QO_Main.InvoiceInitDate
END
INSERT INTO @TempTable (XMLString)
SELECT @DataClose + @RootClose
DROP TABLE #TEMP_QuoteTable
SET NOCOUNT ON
SELECT XMLString FROM @TempTable ORDER By LineNumber
SET NOCOUNT OFF
GO
GRANT_ALL_USER_TABLES
GO
2. Go into system maintenance and on the debtors tab, alter the setting "StoredProcForDebtorOrderOnQuotesGrid" to be the one we created above usp_NETSECURITY_Debtor_BuildGrid_OnQuotes).
3. Open debtor maintenance and the the reference column should be visible on the quotes grid - if it isn't, you may need to go into the custom column management and turn it on and set the width.