If I was approaching doing this the the way I would avoid the doubling up on the quote is add a custom field to the to do to link in the quote and create the quote as a place holder at the same time that the to do gets created, you could even then go as far as on the save of the quote to marking the to do off as completed.
And if you want to get a bit fancy you could introduce a form to show the details of outstanding to dos and quotes together which is done something like the below, obviously you wouldn't need a parameter to the form or stored procedure and could just add the form to the menu but what JiwaFinancials.Jiwa.JiwaApplication.NavigationListUI.NavigationList does is shows a grid in the form that loads the stored procedure data into based upon the two result sets and can have drill downs to other forms as needed in this case it would be to quote form and to do form:
- Code: Select all
CGBackOrdersForm form;
form = this.Manager.FormFactory.CreateForm<CGBackOrdersForm>("Back Orders" );
form.SetParams(gInventoryID);
form.Start();
form.Show();
public class CGBackOrdersForm : JiwaFinancials.Jiwa.JiwaApplication.NavigationListUI.NavigationList
{
public CGBackOrdersForm()
{
base.StoredProcName = "usp_JIWA_NavigationList_CGBackOrders";
base.Text = "Back Orders";
}
public void SetParams(string InventoryID)
{
SqlParameter newSQLParameter3 = new SqlParameter("@InventoryID", SqlDbType.Char ) {Value = InventoryID};
base.SQLParameters.Add(newSQLParameter3);
}
public override void Start()
{
base.Start();
}
}
and the stored procedure like this where it returns two result sets one for the schema and one for data
- Code: Select all
CREATE PROCEDURE [dbo].[usp_JIWA_NavigationList_CGBackOrders] ( @InventoryID as varchar(20))
AS
SET NOCOUNT ON
DECLARE @Schema TABLE
(
id INT IDENTITY NOT NULL,
Name VARCHAR(50) NOT NULL,
Caption VARCHAR(50) NOT NULL,
IsVisible BIT NOT NULL,
DefaultWidth INT NOT NULL,
IsDrillDown BIT NOT NULL,
DrillDownSourceIDColumnName VARCHAR(50) NULL,
DrillDownClassNameColumnName VARCHAR(50) NULL,
FormatString VARCHAR(255) NULL,
DecimalPlacesColumnName VARCHAR(50) NULL
)
-- First Result defines the schema
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'InvoiceID', 'InvoiceID', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'InvoiceNo', 'InvoiceNo', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'InvHistoryNo', 'InvHistoryNo', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'FullInvNo', 'Invoice', 1, 7, 1, 'InvoiceID', 'SY_Forms_ClassNameOrd', NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'InvDate', 'Date', 1, 8, 0, 0, NULL, 'dd/MM/yyyy', NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'DebtorID', 'DebtorID', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'AccountNo', 'Account', 1, 7, 1, 'DebtorID', 'SY_Forms_ClassNameDB', NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'Name', 'Name', 1, 20, 1, 'DebtorID', 'SY_Forms_ClassNameDB', NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'InventoryID', 'InventoryID', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'PartNo', 'Part', 1, 5, 1, 'InventoryID', 'SY_Forms_ClassNameInv', NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'Description', 'Description', 1, 20, 1, 'InventoryID', 'SY_Forms_ClassNameInv', NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'QuantityThisDel', 'Quantity', 1, 7, 0, NULL, NULL, '+#,##0;-#,##0', 'DecimalPlacesQty'
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'QuantityBackOrd', 'Back Ord', 1, 7, 0, NULL, NULL, '+#,##0;-#,##0', 'DecimalPlacesQty'
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'DemandQuantity', 'Demand', 1, 7, 0, NULL, NULL, '+#,##0;-#,##0', 'DecimalPlacesQty'
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'CurrentLineTotal', 'CurrentLineTotal', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'TaxToCharge', 'TaxToCharge', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'NetLinetotal', 'Total', 1, 7, 0, NULL, NULL, '{0:$#,#.00;$-#,#.00}', 'DecimalPlacesTotal'
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'SY_Forms_ClassNameOrd', 'SY_Forms_ClassNameOrd', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'SY_Forms_ClassNameDB', 'SY_Forms_ClassNameDB', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'SY_Forms_ClassNameInv', 'SY_Forms_ClassNameInv', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'DecimalPlacesQty', 'DecimalPlacesQty', 0, 0, 0, NULL, NULL, NULL, NULL
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'DecimalPlacesTotal', 'DecimalPlacesTotal', 0, 0, 0, NULL, NULL, NULL, NULL
SELECT Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName
FROM @Schema
ORDER BY id
-- Second Result returns the data
SELECT SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_History.HistoryNo as InvHistoryNo, SO_Main.InvoiceNo + '-D' + RIGHT('00' + CAST(SO_History.HistoryNo AS varchar(2)), 2) AS FullInvNo, SO_History.RecordDate AS InvDate,
SO_Main.DebtorID, dbo.DB_Main.AccountNo, dbo.DB_Main.Name, SO_Lines.InventoryID, SO_Lines.PartNo, SO_Lines.Description, SO_Lines.QuantityThisDel, SO_Lines.QuantityBackOrd,
SO_Lines.DemandQuantity, SO_Lines.CurrentLineTotal, SO_Lines.TaxToCharge, SO_Lines.CurrentLineTotal - SO_Lines.TaxToCharge AS NetLinetotal,
'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm' AS SY_Forms_ClassNameOrd, 'JiwaFinancials.Jiwa.JiwaDebtorsUI.frmDebtor' AS SY_Forms_ClassNameDB,
'JiwaFinancials.Jiwa.JiwaInventoryUI.InventoryMaintenanceForm' AS SY_Forms_ClassNameInv, 0 AS DecimalPlacesQty, 2 AS DecimalPlacesTotal
FROM dbo.SO_Main AS SO_Main INNER JOIN
dbo.SO_History AS SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID AND SO_Main.CurrentHistoryNo = SO_History.HistoryNo INNER JOIN
dbo.SO_Lines AS SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID INNER JOIN
dbo.DB_Main ON SO_Main.DebtorID = dbo.DB_Main.DebtorID
WHERE ((SO_Lines.QuantityBackOrd <> 0) OR
(SO_Main.Status = 0 OR
SO_Main.Status = 3) AND (SO_Lines.QuantityThisDel <> 0) OR
(SO_Main.Status = 0 OR
SO_Main.Status = 3) AND (ISNULL(SO_Lines.DemandQuantity, 0) <> 0))
and InventoryID = @InventoryID
order by InvDate desc
GO
