SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--
/*declare @Lines varchar(200)
select @Lines = char(39) + '001' + char(39) + char(44) + char(39) + '003' + char(39)
exec GetPropData 'N314999',@Lines,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0*/
ALTER   PROC GetPropData
@txtSONo varchar(7),		--Sales Order
@RecNos varchar(200),		--3/24/08 New Proposal - Select Lines to include for options
@Col1Mfg bit,			--Whether to include Manufacturing in Col1.
@Col1Crt bit,			--Whether to include Crate in Col1.
@Col1Frt bit,			--Whether to include Freight in Col1.	
@Col1Instl bit,			--Whether to include Install in Col1.
@Col2Mfg bit,			--Whether to include Manufacturing in Col2.
@Col2Crt bit,			--Whether to include Crate in Col2.
@Col2Frt bit,			--Whether to include Freight in Col2.	
@Col2Instl bit,			--Whether to include Install in Col2.
@Col3Mfg bit,			--Whether to include Manufacturing in Col3.
@Col3Crt bit,			--Whether to include Crate in Col3.
@Col3Frt bit,			--Whether to include Freight in Col3.	
@Col3Instl bit,			--Whether to include Install in Col3.
@Col4Mfg bit,			--Whether to include Manufacturing in Col4.
@Col4Crt bit,			--Whether to include Crate in Col4.
@Col4Frt bit,			--Whether to include Freight in Col4.	
@Col4Instl bit,			--Whether to include Install in Col4.
@Col5Mfg bit,			--Whether to include Manufacturing in Col5.
@Col5Crt bit,			--Whether to include Crate in Col5.
@Col5Frt bit,			--Whether to include Freight in Col5.	
@Col5Instl bit			--Whether to include Install in Col5.
AS
DECLARE @SQL varchar(8000)	--Main variable to include built select statement.
DECLARE @AllMfg money
DECLARE @AllCrate money
DECLARE @AllFreight money
DECLARE @AllInstall money
DECLARE @Col1 varchar(3000)
DECLARE @Col2 varchar(3000)
DECLARE @Col3 varchar(3000)
DECLARE @Col4 varchar(3000)
DECLARE @Col5 varchar(3000)
DECLARE @TtlLaborPrice money
DECLARE @TtlMaterialPrice money

--Assign values to @All variables, as well as values to totals for Texas New Construction.
SELECT @AllMfg = SUM(curManufMaterialPrice) + SUM(curManufLaborPrice) + SUM(curOtherMaterialPrice),
	@AllCrate = SUM(curCrateLaborPrice) + SUM(curCrateMaterialPrice),
	@AllFreight = SUM(curFreightMaterialPrice),
	@AllInstall = SUM(curLocalInstallLaborPrice) + SUM(curLocalInstallMaterialPrice) 
		+ SUM(curOutsideInstallLaborPrice) + SUM(curOutsideInstallMaterialPrice),
	@TtlLaborPrice = SUM(curManufLaborPrice) + SUM(curCrateLaborPrice) + SUM(curLocalInstallLaborPrice)
		+ SUM(curOutsideInstallLaborPrice),
	@TtlMaterialPrice = SUM(curManufMaterialPrice) + SUM(curCrateMaterialPrice) + SUM(curFreightMaterialPrice)
		+ SUM(curLocalInstallMaterialPrice) + SUM(curOutsideInstallMaterialPrice) + SUM(curOtherMaterialPrice)  
	FROM tblSOLineItems WHERE txtSONo = @txtSONo AND txtRecNo IN (@RecNos)
--HERE IS WHERE IT MESSES UP IR I HARD CODE "'001','003'" IT WORKS FINE, BUT THE VARIABLE WON'T WORK
--Assign values to @Col variables.
SELECT @Col1 = '0'
IF @Col1Mfg = 1 
	SELECT @Col1 = @Col1 + ' + s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice'
IF @Col1Crt = 1
	SELECT @Col1 = @Col1 + ' + s.curCrateLaborPrice + s.curCrateMaterialPrice'
IF @Col1Frt = 1
	SELECT @Col1 = @Col1 + ' + s.curFreightMaterialPrice'
IF @Col1Instl = 1
	SELECT @Col1 = @Col1 + ' + s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
		+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice'
SELECT @Col1 = @Col1 + ' as Col1'
SELECT @Col2 = '0'
IF @Col2Mfg = 1 
	SELECT @Col2 = @Col2 + ' + s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice'
IF @Col2Crt = 1
	SELECT @Col2 = @Col2 + ' + s.curCrateLaborPrice + s.curCrateMaterialPrice'
IF @Col2Frt = 1
	SELECT @Col2 = @Col2 + ' + s.curFreightMaterialPrice'
IF @Col2Instl = 1
	SELECT @Col2 = @Col2 + ' + s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
		+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice'
SELECT @Col2 = @Col2 + ' as Col2'
SELECT @Col3 = '0'
IF @Col3Mfg = 1 
	SELECT @Col3 = @Col3 + ' + s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice'
IF @Col3Crt = 1
	SELECT @Col3 = @Col3 + ' + s.curCrateLaborPrice + s.curCrateMaterialPrice'
IF @Col3Frt = 1
	SELECT @Col3 = @Col3 + ' + s.curFreightMaterialPrice'
IF @Col3Instl = 1
	SELECT @Col3 = @Col3 + ' + s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
		+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice'
SELECT @Col3 = @Col3 + ' as Col3'
SELECT @Col4 = '0'
IF @Col4Mfg = 1 
	SELECT @Col4 = @Col4 + ' + s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice'
IF @Col4Crt = 1
	SELECT @Col4 = @Col4 + ' + s.curCrateLaborPrice + s.curCrateMaterialPrice'
IF @Col4Frt = 1
	SELECT @Col4 = @Col4 + ' + s.curFreightMaterialPrice'
IF @Col4Instl = 1
	SELECT @Col4 = @Col4 + ' + s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
		+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice'
SELECT @Col4 = @Col4 + ' as Col4'
SELECT @Col5 = '0'
IF @Col5Mfg = 1 
	SELECT @Col5 = @Col5 + ' + s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice'
IF @Col5Crt = 1
	SELECT @Col5 = @Col5 + ' + s.curCrateLaborPrice + s.curCrateMaterialPrice'
IF @Col5Frt = 1
	SELECT @Col5 = @Col5 + ' + s.curFreightMaterialPrice'
IF @Col5Instl = 1
	SELECT @Col5 = @Col5 + ' + s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
		+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice'
SELECT @Col5 = @Col5 + ' as Col5'
--Final data retrieval.
SELECT @SQL = 'SELECT s.dblQuantity, s.txtRecNo, p.txtPartDesc, l.txtLILongDesc, 
	s.curManufMaterialPrice + s.curManufLaborPrice + s.curOtherMaterialPrice as Mfg,
	s.curCrateLaborPrice + s.curCrateMaterialPrice as Crt, s.curFreightMaterialPrice as Frt,
	s.curLocalInstallLaborPrice + s.curLocalInstallMaterialPrice 
	+ s.curOutsideInstallLaborPrice + s.curOutsideInstallMaterialPrice as Instl, '
	+ @Col1 + ', ' + @Col2 + ', ' + @Col3 + ', ' + @Col4 + ', ' + @Col5 + ', ' +
	CONVERT(varchar(20), @AllMfg) + ' as AllMfg, ' + CONVERT(varchar(20), @AllCrate) + ' as AllCrt, ' 
	+ CONVERT(varchar(20), @AllFreight) + ' as AllFrt, ' + CONVERT(varchar(20), @AllInstall) + ' as AllInstl, '
	+ CONVERT(varchar(20), @TtlLaborPrice) + ' as TtlLaborPrice, ' 
	+ CONVERT(varchar(20), @TtlMaterialPrice) + ' as TtlMaterialPrice, ' + 
	' AC.txtActionDesc as ActionCode, Ext.Field8 as ItemID 
	FROM tblSOLineItems s LEFT JOIN tblPartLookup p ON s.txtPartNo = p.txtPartNo
	LEFT JOIN tblSOLineItemsLongDesc l ON s.txtSONo = l.txtSONo AND s.txtRecNo = l.txtRecNo 
	LEFT JOIN (tblSOLineItemsExt Ext INNER JOIN tblActionID AC ON Ext.Field7 = AC.txtActionCode)  on s.txtSoNo = Ext.txtSoNo AND s.txtRecNo = Ext.txtRecNo 
	WHERE s.txtSONo = ' + char(39) + @txtSONo + char(39) + ' AND s.txtStatus NOT Like ' + char(39) + 'Delete%' + char(39) + 
	'AND s.txtRecNo IN ('  + @RecNos +  --HERE IT WORKS FINE 
	') ORDER BY s.txtRecNO'

--Execute select statement.
--print @SQL 
EXECUTE (@SQL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO