couple of quick questions (hopefully)

[robin]

Hi Gents,

Since you were so helpful with my automatic emailing question last time (love you mike), just a couple of quick programing questions before I get to heavily involved into my bosses latest requests.

version 6.5.11

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?

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.

Thanks in advance

Robin Fleming

[Mike.Sheen]

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.

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.

[robin]

Thanks mike works a treat

Sorry for the late reply been in hospital for a couple weeks

Regards
Robin Fleming

[Mike.Sheen]

Thanks mike works a treat

Sorry for the late reply been in hospital for a couple weeks

Regards
Robin Fleming

Ah - you just need to wrap the contents with a call to udf_JIWA_XMLFormat.

eg: Where you see

Code: Select all

+ '<OrderNo>' + QO_Main.OrderNo + '</OrderNo>'

Replace that with

Code: Select all

+ '<OrderNo>' + dbo.udf_JIWA_XMLFormat(QO_Main.OrderNo) + '</OrderNo>'

Mike