473,785 Members | 2,411 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

building a multi statement table UDF

Hi All,
While in the process of building my table (40 or so Insert statments)
can I then query ("select * from @Table_variable ") and use the results
up to theat point for another insert into @Table_varible? If you look
for stepID -15 I have commented that section out due to it not
retuning the correct values.

Thank you in advance
Stephen Patten

Table Code:

ALTER FUNCTION dbo.BidContract
(
@MixHeaderID int,
@MaterialEffect iveDate nvarchar(10),
@LaborEffective Date nvarchar(10),
@AreaTypeID int,
@NailingParam int,
@TapingParam int
)

/*
@MixHeaderID int = 2,
@MaterialEffect iveDate nvarchar(10) = '2003-01-01',
@LaborEffective Date nvarchar(10) = '2003-01-01',
@AreaTypeID int = 1,
@NailingParam int = -1,
@TapingParam int = -1
*/
RETURNS @table_variable TABLE (
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StepID decimal (18,1) NOT NULL ,
JobMasterID int NOT NULL ,
MixHeaderID int NOT NULL ,
BidSubtypeID int NOT NULL ,
WorkTypeID int NOT NULL ,
UnitName nvarchar (64) NOT NULL ,
UnitQuantity int NOT NULL ,
ItemDescription nvarchar (256) NOT NULL ,
ItemQuantity decimal(18, 4) NOT NULL ,
ScaleValue decimal(18, 4) NOT NULL ,
ExtendedPrice decimal (18,4) NOT NULL ,
IsVisible bit NULL ,
WSQtyAdj decimal (18,4) NULL)
AS
BEGIN
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MATERIAL
SUBTYPE 1

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -1
WALLBOARD
ALL MATERIAL THAT HAS A MATERIAL CATEGORY OF 1 (WALLBOARD)
NOTE: THIS WILL ALSO GIVE YOU THE TOTAL SQUARE FEET TO BE USED IN
LATER CALCULATIONS
*/
INSERT INTO @table_variable
SELECT - 1 AS StepID, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID, 1 AS BidSubTypeID, 0 AS WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity *
(FloorPlanLineI tem.Quantity * Dimension.Area) ) / 1000 AS ItemQuantity,
MaterialScale.P rice AS ScaleValue,
SUM(MixLineItem .FloorPlanQuant ity *
(FloorPlanLineI tem.Quantity * Dimension.Area) ) / 1000 *
MaterialScale.P rice AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect iveDate) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect iveDate)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y,
MaterialScale.P rice
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 1 AS StepID, MixHeader.JobMa sterID,
MixHeader.MixHe aderID, 1 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity * Dimension.Area /
1000) AS ItemQuantity,
MaterialScale.P rice AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity * Dimension.Area / 1000 *
MaterialScale.P rice) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect ivedate)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'')
+ '~' + ISNULL(FloorPla nHeader.Attribu te2, '') +
'~' + ISNULL(FloorPla nHeader.Attribu te3, ''), MixHeader.MixHe aderID,
MixHeader.JobMa sterID,
MixLineItem.Flo orPlanQuantity,
MaterialScale.P rice
HAVING (MixHeader.MixH eaderID = @MixHeaderID)

/*
STEP -2
STOCKING
Scale * Total Wallboard sq ft
*/
INSERT INTO @table_variable
SELECT -2, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, N'STOCKING', SUM(ItemQuantit y),
dbo.BidContract _GetMaterialSca leValue(830, @AreaTypeID,
@MaterialEffect iveDate), SUM(ItemQuantit y) *
dbo.BidContract _GetMaterialSca leValue(830, @AreaTypeID,
@MaterialEffect iveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MISC MATERIAL
SUBTYPE 2

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -3
NAILS AND SCREWS
Scale * Total Wallboard sq ft
1 box covers 4000 sq ft of wallboard
This makes sure we are dealing with whole boxes of nails:
ROUND(((TotalSq uareFoot)/4000 + .4999), 0)

SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(Item Quantity)/4) + .4999,
0), dbo.BidContract _GetMaterialSca leValue(829, 1, '2003-01-01'),
ROUND((SUM(Item Quantity)/4) + .4999, 0) *
dbo.BidContract _GetMaterialSca leValue(829, 1, '2003-01-01'),
IsVisible, WSQtyAdj
FROM bidunit
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
*/
INSERT INTO @table_variable
SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(Item Quantity)/4), 0),
dbo.BidContract _GetMaterialSca leValue(829, 1, '2003-01-01'),
ROUND((SUM(Item Quantity)/4), 0) *
dbo.BidContract _GetMaterialSca leValue(829, 1, '2003-01-01'),
IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
MUD
Step -4
Select just a subset of the already inserted
data to give us a distinct list of UNITS to pass to the MUD function
NOTE: this type of select will be used a couple of more times, always
use StepID = -2 (STOCKING)
@THE_VALUE = isnull(@WALLBOA RD/250,0) + isnull(@FIRETAP ING/500,0) +
isnull(@METAL/125,0) + isnull(@CEIL_SQ _FT/900,0)
*/
INSERT INTO @table_variable
SELECT - 4, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'MUD', dbo.BidContract _GetMudValue(@M ixHeaderID,
UnitName), dbo.BidContract _GetMaterialSca leValue(828, @AreaTypeID,
@MaterialEffect iveDate), dbo.BidContract _GetMudValue(@M ixHeaderID,
UnitName) * dbo.BidContract _GetMaterialSca leValue(828, @AreaTypeID,
@MaterialEffect iveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 2
/*
TAPE
Step -5
ROUND(SUM(ItemQ uantity)/1100, 0)
*/
INSERT INTO @table_variable
SELECT - 5, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'TAPE', dbo.BidContract _GetTapeValue(@ MixHeaderID,
UnitName), dbo.BidContract _GetMaterialSca leValue(832, @AreaTypeID,
@MaterialEffect iveDate), dbo.BidContract _GetTapeValue(@ MixHeaderID,
UnitName) * dbo.BidContract _GetMaterialSca leValue(832, @AreaTypeID,
@MaterialEffect iveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 2
/*
METAL
Step -6
SUM(Z395*1.1)/1000
*/
INSERT INTO @table_variable
SELECT - 6 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtype, 0 AS WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , (ISNULL(SUM(Mix LineItem.FloorP lanQuantity *
FloorPlanLineIt em.Quantity), 0) * 1.1) / 1000
AS ItemQuantity, MaterialScale.P rice AS
ScaleValue, (ISNULL(SUM(Mix LineItem.FloorP lanQuantity *
FloorPlanLineIt em.Quantity), 0) * 1.1) / 1000
* MaterialScale.P rice AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 2) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect iveDate) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 2) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect iveDate)
GROUP BY MixBuilding.Mix BuildingDescrip tion,
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y,

MaterialItemMas ter.MaterialIte mMasterDescript ion, MaterialScale.P rice
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 6 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , (FloorPlanLineI tem.Quantity * 1.1) / 1000 AS
ItemQuantity, MaterialScale.P rice AS ScaleValue,
(FloorPlanLineI tem.Quantity * 1.1) / 1000 *
MaterialScale.P rice AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 2) AND
(MaterialScale. AreaTypeID = @AreaTypeID) AND
(MaterialScale. EffectiveDate = @MaterialEffect iveDate)
GROUP BY FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'')
+ '~' + ISNULL(FloorPla nHeader.Attribu te2, '') +
'~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
MaterialItemMas ter.MaterialIte mMasterDescript ion,
MaterialScale.P rice, FloorPlanLineIt em.Quantity,
MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
/*
MISC MATERIAL
Step -7
*/
INSERT INTO @table_variable
SELECT - 7 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtype,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , ISNULL(SUM(MixL ineItem.FloorPl anQuantity *
FloorPlanLineIt em.Quantity), 0)
AS ItemQuantity, MaterialScale.P rice AS
ScaleValue, ISNULL(SUM(MixL ineItem.FloorPl anQuantity *
FloorPlanLineIt em.Quantity), 0)
* MaterialScale.P rice AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(MixLineItem.Mi xBuildingID IS NOT NULL) AND (MixLineItem.Mi xLevelID IS
NOT NULL) AND
(NOT (MaterialItemMa ster.MaterialCa tegoryID IN
(1, 2))) OR
(MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(MixLineItem.Mi xBuildingID IS NOT NULL) AND (MixLineItem.Mi xLevelID IS
NULL) AND
(NOT (MaterialItemMa ster.MaterialCa tegoryID IN
(1, 2)))
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y,
MaterialScale.P rice, FloorPlanLineIt em.WorkTypeID
HAVING (NOT (MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidMiscMater ialExemptionLis tDescription
FROM
clsBidMiscMater ialExemptionLis t))) AND (FloorPlanLineI tem.WorkTypeID
<> 3)
UNION ALL
SELECT - 7 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'') + '~' + ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , FloorPlanLineIt em.Quantity AS ItemQuantity,
MaterialScale.P rice AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity *
MaterialScale.P rice) AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
MaterialScale ON
MaterialItemMas ter.MaterialIte mMasterID =
MaterialScale.M aterialItemMast erID LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(MixLineItem.Mi xBuildingID IS NULL) AND (MixLineItem.Mi xLevelID IS
NULL) AND
(NOT (MaterialItemMa ster.MaterialCa tegoryID IN
(1, 2)))
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'')
+ '~' + ISNULL(FloorPla nHeader.Attribu te2, '') +
'~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MaterialScale.P rice,
FloorPlanLineIt em.WorkTypeID, MixLineItem.Flo orPlanQuantity,
FloorPlanLineIt em.Quantity
HAVING (NOT (MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidMiscMater ialExemptionLis tDescription
FROM
clsBidMiscMater ialExemptionLis t))) AND (FloorPlanLineI tem.WorkTypeID
<> 3)
/*
COUNT OF LIVING UNITS
Step -8

****HOUSE***
SELECT - 8 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity) AS
ItemQuantity, dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate)
AS ScaleValue, SUM(MixLineItem .FloorPlanQuant ity
* dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND (FloorPlanHeade r.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
****HOUSE***
*/
INSERT INTO @table_variable
SELECT - 8 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity, N'SUNDRIES' AS
ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity) AS
ItemQuantity, dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate)
AS ScaleValue, SUM(MixLineItem .FloorPlanQuant ity
* dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND (FloorPlanHeade r.IsLivingUnit
= '1') OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL)
GROUP BY FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingDescrip tion, MixBuilding.Mix BuildingQuantit y
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 8 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription ,
1 AS ItemQuantity,
dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate)
AS ScaleValue, SUM(1 *
dbo.BidContract _GetMaterialSca leValue(843, @AreaTypeID,
@MaterialEffect iveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND (FloorPlanHeade r.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
/*
FIRE HAULT - CHECK FOR FIREPROOFING
Step -9
*/
IF EXISTS (
SELECT 'true' AS Expr1
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON
FloorPlanHeader .FloorPlanHeade rID = MixLineItem.Flo orPlanHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID
WHERE (MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(MaterialItemMa ster.MaterialIt emMasterDescrip tion = N'FIRETAPING') AND
(JobMaster.Proj ectTypeID <> 1))
BEGIN
INSERT INTO @table_variable
SELECT - 9 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidsubtypeID, 0 AS WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity, N'FIRE HAULT' AS
ItemDescription ,
ISNULL(SUM(MixB uilding.MixBuil dingQuantity *
MixLineItem.Flo orPlanQuantity) / 2, 0) AS ItemQuantity,
dbo.BidContract _GetMaterialSca leValue(579,
@AreaTypeID, @MaterialEffect iveDate) AS ScaleValue,
ISNULL(SUM(MixB uilding.MixBuil dingQuantity *
MixLineItem.Flo orPlanQuantity) / 2, 0) *
dbo.BidContract _GetMaterialSca leValue(579,
@AreaTypeID, @MaterialEffect iveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON
FloorPlanHeader .FloorPlanHeade rID = MixLineItem.Flo orPlanHeaderID
INNER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND (FloorPlanHeade r.IsLivingUnit
= '1') OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL)
GROUP BY MixBuilding.Mix BuildingDescrip tion,
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 9 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity, N'FIRE
HAULT' AS ItemDescription ,
ISNULL(SUM(MixL ineItem.FloorPl anQuantity) / 2,
0) AS ItemQuantity, dbo.BidContract _GetMaterialSca leValue(579,
@AreaTypeID,
@MaterialEffect iveDate) AS ScaleValue,
ISNULL(SUM(MixL ineItem.FloorPl anQuantity) / 2, 0) *
dbo.BidContract _GetMaterialSca leValue(579,
@AreaTypeID, @MaterialEffect iveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID =
MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND (MixLineItem.Mi xLevelID
IS NULL) AND (FloorPlanHeade r.IsLivingUnit = '1')
GROUP BY FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
END
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

NAILING LABOR
SUBTYPE 3

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
PRODUCTION WALLBOARD
Step -10.x
*/
IF (@NailingParam = -1) --Progressive
BEGIN
INSERT INTO @table_variable
SELECT - 10.1 AS StepID, NailingLabor.Jo bMasterID,
NailingLabor.Mi xHeaderID, 3 AS BidSubtypeID, NailingLabor.Wo rkTypeID,
NailingLabor.Un itName,
NailingLabor.Un itQuantity, RTRIM(NailingLa bor.ItemDescrip tion) + N' '
+ dbo.Height.Heig htDescription AS ItemDescription ,
NailingLabor.It emQuantity, Scale.Price +
dbo.Height.Nail ingRate + NailingLabor.Pr oductionNailing LaborIncrease
AS ScaleValue,
NailingLabor.It emQuantity * (Scale.Price +
dbo.Height.Nail ingRate + NailingLabor.Pr oductionNailing LaborIncrease)
AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract _NailingLabor_W allboard_Produc tion(@MixHeader ID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.He ightID = dbo.Height.Heig htID INNER JOIN
dbo.BidContract _NailingLabor_S cale() Scale ON
NailingLabor.It emDescription = Scale.ItemDescr iption
WHERE (Scale.AreaType ID = @AreaTypeID) AND (Scale.Effectiv eDate =
@LaborEffective Date)
ORDER BY NailingLabor.Wo rkTypeID DESC,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' +
dbo.Height.Heig htDescription

INSERT INTO @table_variable
SELECT - 10.1 AS StepID, NailingLabor.Jo bMasterID,
NailingLabor.Mi xHeaderID, 3 AS BidSubtypeID, NailingLabor.Wo rkTypeID,
NailingLabor.Un itName,
NailingLabor.Un itQuantity, RTRIM(NailingLa bor.ItemDescrip tion) + N' '
+ dbo.Height.Heig htDescription AS ItemDescription ,
NailingLabor.It emQuantity, Scale.Price +
dbo.Height.Nail ingRateGarage +
NailingLabor.Pr oductionNailing LaborIncrease AS ScaleValue,
NailingLabor.It emQuantity * (Scale.Price +
dbo.Height.Nail ingRateGarage +
NailingLabor.Pr oductionNailing LaborIncrease) AS ExtendedPrice, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract _NailingLabor_W allboard_Produc tion_Garage(@Mi xHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.He ightID =
dbo.Height.Heig htID INNER JOIN
dbo.BidContract _NailingLabor_S cale() Scale ON
NailingLabor.It emDescription = Scale.ItemDescr iption
WHERE (Scale.AreaType ID = @AreaTypeID) AND (Scale.Effectiv eDate =
@LaborEffective Date)
ORDER BY NailingLabor.Wo rkTypeID DESC,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' +
dbo.Height.Heig htDescription

END
IF (@NailingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @table_variable
SELECT - 10.2 AS StepID, NailingLabor.Jo bMasterID,
NailingLabor.Mi xHeaderID, 3 AS BidSubtypeID, NailingLabor.Wo rkTypeID,
NailingLabor.Un itName,
NailingLabor.Un itQuantity,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' + Height.HeightDe scription
AS ItemDescription , NailingLabor.It emQuantity,
Scale.Price +
NailingLabor.Pr oductionNailing LaborIncrease AS ScaleValue,
NailingLabor.It emQuantity * (Scale.Price +
NailingLabor.Pr oductionNailing LaborIncrease) AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_Nai lingLabor_Wallb oard_Production (@MixHeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.He ightID =
Height.HeightID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
NailingLabor.It emDescription = Scale.ItemDescr iption
WHERE (Scale.AreaType ID = @AreaTypeID) AND (Scale.Effectiv eDate =
@LaborEffective Date)
ORDER BY NailingLabor.Wo rkTypeID DESC,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' + Height.HeightDe scription

--TODO: add garage

END
IF (@NailingParam = -3) --Straight
BEGIN
INSERT INTO @table_variable
SELECT - 10.3 AS StepID, NailingLabor.Jo bMasterID,
NailingLabor.Mi xHeaderID, 3 AS BidSubtypeID, NailingLabor.Wo rkTypeID,
NailingLabor.Un itName,
NailingLabor.Un itQuantity,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' + Height.HeightDe scription
AS ItemDescription , NailingLabor.It emQuantity,
NailingLabor.Pr oductionNailing LaborStraight AS
ScaleValue,
NailingLabor.It emQuantity *
NailingLabor.Pr oductionNailing LaborStraight AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_Nai lingLabor_Wallb oard_Production (@MixHeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.He ightID =
Height.HeightID
ORDER BY NailingLabor.Wo rkTypeID DESC,
RTRIM(NailingLa bor.ItemDescrip tion) + N' ' + Height.HeightDe scription

--TODO: add garage

END
/*
'ALL OTHER MATERIAL
Step -11
*/
INSERT INTO @table_variable
SELECT - 11 AS StepID, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion AS ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity * FloorPlanLineIt em.Quantity)
AS ItemQuantity,
BidContract_Nai lingLabor_Scale .Price AS ScaleValue,
SUM(MixLineItem .FloorPlanQuant ity *
FloorPlanLineIt em.Quantity * BidContract_Nai lingLabor_Scale .Price) AS
ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
BidContract_Nai lingLabor_Scale ()
BidContract_Nai lingLabor_Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion
= BidContract_Nai lingLabor_Scale .ItemDescriptio n LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.MaterialIt emMasterID <> 606)
AND (MaterialItemMa ster.Attribute2 = N'1') AND
(BidContract_Na ilingLabor_Scal e.EffectiveDate =
@LaborEffective Date) AND (BidContract_Na ilingLabor_Scal e.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.MaterialIt emMasterID <> 606)
AND (MaterialItemMa ster.Attribute2 = N'1') AND
(BidContract_Na ilingLabor_Scal e.EffectiveDate =
@LaborEffective Date) AND (BidContract_Na ilingLabor_Scal e.AreaTypeID =
@AreaTypeID)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanLineIt em.WorkTypeID, MixBuilding.Mix BuildingDescrip tion,
MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y, BidContract_Nai lingLabor_Scale .Price
HAVING (FloorPlanLineI tem.WorkTypeID = 2) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 11 AS StepID, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, SUM(MixLineItem .FloorPlanQuant ity) AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity ) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.MaterialIt emMasterID <> 606)
AND (MaterialItemMa ster.Attribute2 = N'1') AND (Scale.Effectiv eDate =
@LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
MixHeader.JobMa sterID, MixLineItem.Mix HeaderID, Scale.Price
HAVING (FloorPlanLineI tem.WorkTypeID = 2) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID)
/*
'PRELIM WALLBOARD LESS SPECIAL
Step -12
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.UnitQuantity * Scale.Price AS ExtendedPrice,
VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,

MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,

MaterialItemMas ter.MaterialIte mMasterDescript ion + N' ' +
WorkLocation.Wo rkLocationDescr iption AS ItemDescription ,

SUM(MixLineItem .FloorPlanQuant ity * (FloorPlanLineI tem.Quantity *
Dimension.Area) ) AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON
FloorPlanHeader .FloorPlanHeade rID = MixLineItem.Flo orPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.Mix HeaderID = MixHeader.MixHe aderID INNER JOIN
Dimension ON
FloorPlanLineIt em.DimensionID = Dimension.Dimen sionID INNER JOIN
JobMaster ON
FloorPlanHeader .JobMasterID = JobMaster.JobMa sterID AND
MixHeader.JobMa sterID =
JobMaster.JobMa sterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineIt em.WorkLocation ID = WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NOT
NULL) AND (MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1)
AND
(MaterialItemMa ster.Attribute2 = N'1') AND (NOT
(MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidNailingLa borExemptionLis tDescription
FROM
clsBidNailingLa borExemptionLis t))) OR

(MixLineItem.Mi xBuildingID IS NOT NULL) AND (MixLineItem.Mi xLevelID IS
NULL) AND (MaterialItemMa ster.MaterialCa tegoryID = 1) AND

(MaterialItemMa ster.Attribute2 = N'1') AND (NOT
(MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidNailingLa borExemptionLis tDescription
FROM
clsBidNailingLa borExemptionLis t)))
GROUP BY
MaterialItemMas ter.MaterialIte mMasterDescript ion + N' ' +
WorkLocation.Wo rkLocationDescr iption, FloorPlanLineIt em.WorkTypeID,

MixBuilding.Mix BuildingDescrip tion, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, MixBuilding.Mix BuildingQuantit y
HAVING (FloorPlanLineI tem.WorkTypeID = 1)
AND (MixLineItem.Mi xHeaderID = @MixHeaderID)) VT INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
VT.ItemDescript ion = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
UNION ALL
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.UnitQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName
+ '~' + ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' +
ISNULL(FloorPla nHeader.Attribu te3, '') AS UnitName,
MixLineItem.Flo orPlanQuantity AS UnitQuantity,

MaterialItemMas ter.MaterialIte mMasterDescript ion + N' ' +
WorkLocation.Wo rkLocationDescr iption AS ItemDescription ,

SUM(FloorPlanLi neItem.Quantity * Dimension.Area) AS ItemQuantity, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON
FloorPlanHeader .FloorPlanHeade rID = MixLineItem.Flo orPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.Mix HeaderID = MixHeader.MixHe aderID INNER JOIN
Dimension ON
FloorPlanLineIt em.DimensionID = Dimension.Dimen sionID INNER JOIN
JobMaster ON
FloorPlanHeader .JobMasterID = JobMaster.JobMa sterID AND
MixHeader.JobMa sterID =
JobMaster.JobMa sterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineIt em.WorkLocation ID = WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NULL)
AND (MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND

(MaterialItemMa ster.Attribute2 = N'1') AND (NOT
(MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidNailingLa borExemptionLis tDescription
FROM
clsBidNailingLa borExemptionLis t)))
GROUP BY
MaterialItemMas ter.MaterialIte mMasterDescript ion + N' ' +
WorkLocation.Wo rkLocationDescr iption, FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName
+ '~' + ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3,
''), JobMaster.JobMa sterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity
HAVING (FloorPlanLineI tem.WorkTypeID = 1)
AND (MixLineItem.Mi xHeaderID = @MixHeaderID)) VT INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
VT.ItemDescript ion = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
PRELIM SPECIAL BOARD
Step -13
*/
INSERT INTO @table_variable
SELECT - 13 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity *
(FloorPlanLineI tem.Quantity * Dimension.Area) ) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(MixLineItem .FloorPlanQuant ity * (FloorPlanLineI tem.Quantity *
Dimension.Area) * Scale.Price) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON FloorPlanLineIt em.MaterialItem MasterID
= MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID AND MixHeader.JobMa sterID =
JobMaster.JobMa sterID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineIt em.WorkLocation ID
= WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY FloorPlanLineIt em.WorkTypeID,
MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y, Scale.Price
HAVING (FloorPlanLineI tem.WorkTypeID = 1) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID) AND

(MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidNailingLa borExemptionLis tDescription
FROM
clsBidNailingLa borExemptionLis t))
UNION ALL
SELECT - 13 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity * Dimension.Area)
AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity * Dimension.Area * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID = JobMaster.JobMa sterID AND
MixHeader.JobMa sterID = JobMaster.JobMa sterID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineIt em.WorkLocation ID =
WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID = 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY FloorPlanLineIt em.WorkTypeID,
MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
JobMaster.JobMa sterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity, Scale.Price
HAVING (FloorPlanLineI tem.WorkTypeID = 1) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID) AND

(MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(SELECT
clsBidNailingLa borExemptionLis tDescription
FROM
clsBidNailingLa borExemptionLis t))
/*
'ALL OTHER PRELIM MATERIAL
Step -14
*/
INSERT INTO @table_variable
SELECT - 14 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(MixLineItem .FloorPlanQuant ity *
FloorPlanLineIt em.Quantity)
AS ItemQuantity, Scale.Price AS ScaleValue,
SUM(MixLineItem .FloorPlanQuant ity * FloorPlanLineIt em.Quantity *
Scale.Price) AS ExtendedPrice,
0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID AND MixHeader.JobMa sterID =
JobMaster.JobMa sterID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineIt em.WorkLocation ID
= WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanLineIt em.WorkTypeID, MixBuilding.Mix BuildingDescrip tion,
JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, MixBuilding.Mix BuildingQuantit y, Scale.Price
HAVING (NOT (MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineI tem.WorkTypeID = 1) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 14 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 3 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity ) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID AND MixHeader.JobMa sterID =
JobMaster.JobMa sterID INNER JOIN
BidContract_Nai lingLabor_Scale () Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineIt em.WorkLocation ID
= WorkLocation.Wo rkLocationID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialCa tegoryID <> 1) AND
(MaterialItemMa ster.Attribute2 = N'1') AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
JobMaster.JobMa sterID, MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity, Scale.Price
HAVING (NOT (MaterialItemMa ster.MaterialIt emMasterDescrip tion IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineI tem.WorkTypeID = 1) AND
(MixLineItem.Mi xHeaderID = @MixHeaderID)
/*
'FOREMAN
Step -15
INSERT INTO @table_variable
SELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, ItemDescription , ItemQuantity, ExtendedPrice /
ItemQuantity AS ScaleValue,
ExtendedPrice, IsVisible, WSQtyAdj
FROM ( SELECT
- 15 AS StepID,
JobMasterID,
MixHeaderID,
BidSubtypeID,
0 AS WorkTypeID,
UnitName,
UnitQuantity,
N'FOREMAN' AS ItemDescription ,
SUM(ItemQuantit y) AS ItemQuantity,
(SELECT
SUM(T2.Extended Price)
FROM @table_variable T2
WHERE T2.UnitName = T1.UnitName AND T2.BidSubtypeID = 3) * .08
AS ExtendedPrice,
IsVisible,
WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 10.1, - 10.2, - 10.3, - 12, -
13))
GROUP BY UnitName, JobMasterID, MixHeaderID,
BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT
*/

/*
/////////////////////////////////////////////////////////////////////////////////////////////////////////

TAPING LABOR
SUBTYPE 4

/////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
WALLLBOARD W/ HEIGHT
Step - 16.x
*/
IF (@TapingParam = -1) --Progressive
BEGIN
INSERT INTO @table_variable
SELECT - 16.1 AS StepID, TapingLabor.Job MasterID,
TapingLabor.Mix HeaderID, TapingLabor.Bid SubtypeID,
TapingLabor.Wor kTypeID, TapingLabor.Uni tName,
TapingLabor.Uni tQuantity,
CASE TapingLabor.Ite mDescription WHEN
'WALLBOARD' THEN Height.HeightDe scription ELSE
TapingLabor.Ite mDescription + ' ' + Height.HeightDe scription
END AS ItemDescription ,
TapingLabor.Ite mQuantity, Scale.Price +
TapingLabor.Tap ingLaborIncreas e + Height.TapingRa te AS ScaleValue,
TapingLabor.Ite mQuantity * (Scale.Price +
TapingLabor.Tap ingLaborIncreas e + Height.TapingRa te) AS ExtendedPrice,
TapingLabor.IsV isible,
TapingLabor.WSQ tyAdj
FROM BidContract_Tap ingLabor_Wallbo ard(@MixHeaderI D)
TapingLabor INNER JOIN
Height ON TapingLabor.Hei ghtID = Height.HeightID
INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
TapingLabor.Ite mDescription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
END
IF (@TapingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @table_variable
SELECT - 16.2 AS StepID, TapingLabor.Job MasterID,
TapingLabor.Mix HeaderID, TapingLabor.Bid SubtypeID,
TapingLabor.Wor kTypeID, TapingLabor.Uni tName,
TapingLabor.Uni tQuantity,
CASE TapingLabor.Ite mDescription WHEN
'WALLBOARD' THEN Height.HeightDe scription ELSE
TapingLabor.Ite mDescription + ' ' + Height.HeightDe scription
END AS ItemDescription ,
TapingLabor.Ite mQuantity, Scale.Price +
TapingLabor.Tap ingLaborIncreas e AS ScaleValue,
TapingLabor.Ite mQuantity * (Scale.Price +
TapingLabor.Tap ingLaborIncreas e) AS ExtendedPrice,
TapingLabor.IsV isible, TapingLabor.WSQ tyAdj
FROM BidContract_Tap ingLabor_Wallbo ard(@MixHeaderI D)
TapingLabor INNER JOIN
Height ON TapingLabor.Hei ghtID = Height.HeightID
INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
TapingLabor.Ite mDescription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
END
IF (@TapingParam = -3) --Straight
BEGIN
INSERT INTO @table_variable
SELECT - 16.3 AS StepID, TapingLabor.Job MasterID,
TapingLabor.Mix HeaderID, TapingLabor.Bid SubtypeID,
TapingLabor.Wor kTypeID, TapingLabor.Uni tName,
TapingLabor.Uni tQuantity,
CASE TapingLabor.Ite mDescription WHEN
'WALLBOARD' THEN Height.HeightDe scription ELSE
TapingLabor.Ite mDescription + ' ' + Height.HeightDe scription
END AS ItemDescription ,
TapingLabor.Ite mQuantity, TapingLabor.Tap ingLaborStraigh t AS
ScaleValue,
TapingLabor.Ite mQuantity *
TapingLabor.Tap ingLaborStraigh t AS ExtendedPrice,
TapingLabor.IsV isible, TapingLabor.WSQ tyAdj
FROM BidContract_Tap ingLabor_Wallbo ard(@MixHeaderI D) TapingLabor
INNER JOIN
Height ON TapingLabor.Hei ghtID = Height.HeightID
INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON TapingLabor.Ite mDescription
= Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
END
/*
METAL AND MISC ITEMS
StepID -17
*/
INSERT INTO @table_variable
SELECT - 17 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 4 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(MixLineItem .FloorPlanQuant ity *
FloorPlanLineIt em.Quantity)
AS ItemQuantity, Scale.Price AS ScaleValue,
SUM((MixLineIte m.FloorPlanQuan tity * FloorPlanLineIt em.Quantity)
* (Scale.Price + JobMaster.Tapin gLaborIncrease) )
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND
(MaterialItemMa ster.MaterialIt emMasterID IN (605, 795,
589, 584, 586, 583, 585, 587, 582, 588)) AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialIt emMasterID IN (605, 795, 589,
584, 586, 583, 585, 587, 582, 588)) AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY JobMaster.JobMa sterID,
MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y, Scale.Price,
FloorPlanLineIt em.WorkTypeID
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
UNION ALL
SELECT - 17 AS StepID, JobMaster.JobMa sterID,
MixLineItem.Mix HeaderID, 4 AS BidSubtypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' + ISNULL(FloorPla nHeader.Attribu te3, '')
AS UnitName, MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity ) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLi neItem.Quantity * (Scale.Price +
JobMaster.Tapin gLaborIncrease) ) AS ExtendedPrice, 0 AS IsVisible, 0 AS
WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID = MixHeader.MixHe aderID
INNER JOIN
JobMaster ON FloorPlanHeader .JobMasterID =
JobMaster.JobMa sterID INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
Scale.ItemDescr iption LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialIt emMasterID IN (605, 795, 589, 584,
586, 583, 585, 587, 582, 588)) AND
(Scale.Effectiv eDate = @LaborEffective Date) AND (Scale.AreaType ID =
@AreaTypeID)
GROUP BY JobMaster.JobMa sterID,
MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'') + '~' + ISNULL(FloorPla nHeader.Attribu te2,
'') + '~' + ISNULL(FloorPla nHeader.Attribu te3, ''),
MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity, Scale.Price,
FloorPlanLineIt em.WorkTypeID
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)
/*
BRACKETS
StepID -18
*/
INSERT INTO @table_variable
SELECT Brackets.StepID , Brackets.JobMas terID,
Brackets.MixHea derID, Brackets.BidSub typeID, Brackets.WorkTy peID,
Brackets.UnitNa me,
Brackets.UnitQu antity, Brackets.ItemDe scription,
Brackets.ItemQu antity, Scale.Price AS Price, Brackets.ItemQu antity *
Scale.Price AS ExtendedPrice,
Brackets.IsVisi ble, Brackets.WSQtyA dj
FROM (SELECT - 18 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,

MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity, N'BRACKETS' AS
ItemDescription ,

SUM(MixLineItem .FloorPlanQuant ity) AS ItemQuantity, 0 AS IsVisible, 0
AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.Flo orPlanHeaderID = FloorPlanHeader .FloorPlanHeade rID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT
NULL) AND (MixLineItem.Mi xLevelID IS NOT NULL) OR

(MixLineItem.Mi xBuildingID IS NOT NULL) AND (MixLineItem.Mi xLevelID IS
NULL)
GROUP BY MixBuilding.Mix BuildingDescrip tion,
FloorPlanHeader .JobMasterID, MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y
HAVING (MixLineItem.Mi xHeaderID =
@MixHeaderID)) Brackets INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
Brackets.ItemDe scription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
UNION ALL
SELECT Brackets.StepID , Brackets.JobMas terID,
Brackets.MixHea derID, Brackets.BidSub typeID, Brackets.WorkTy peID,
Brackets.UnitNa me,
Brackets.UnitQu antity, Brackets.ItemDe scription,
Brackets.ItemQu antity, Scale.Price AS Price, Brackets.ItemQu antity *
Scale.Price AS ExtendedPrice,
Brackets.IsVisi ble, Brackets.WSQtyA dj
FROM (SELECT - 18 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' +
ISNULL(FloorPla nHeader.Attribu te3, '') AS UnitName,
MixLineItem.Flo orPlanQuantity AS UnitQuantity, N'BRACKETS' AS
ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.Flo orPlanHeaderID = FloorPlanHeader .FloorPlanHeade rID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL)
GROUP BY FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' +
ISNULL(FloorPla nHeader.Attribu te3, ''), FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID))
Brackets INNER JOIN
BidContract_Tap ingLabor_Scale( ) Scale ON
Brackets.ItemDe scription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
FOREMAN
StepID -19
*/
INSERT INTO @table_variable
SELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, ItemDescription , ItemQuantity, ExtendedPrice /
ItemQuantity AS ScaleValue,
ExtendedPrice, IsVisible, WSQtyAdj
FROM (SELECT - 19 AS StepID, JobMasterID, MixHeaderID, BidSubtypeID,
0 AS WorkTypeID, UnitName, UnitQuantity, N'FOREMAN' AS
ItemDescription ,
SUM(ItemQuantit y) AS ItemQuantity,
(SELECT
SUM(T2.Extended Price)
FROM @table_variable
T2
WHERE T2.UnitName =
T1.UnitName AND T2.BidSubtypeID = 4) * .06 AS ExtendedPrice,
IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

PICKUP
SUBTYPE 5

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
CUT & SCRAPE
StepID -20
Same as Total Taping Labor Wallboard less the Garages
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 20 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'CUT &
SCRAPE' AS ItemDescription ,
SUM(ItemQuantit y) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescriptio n LIKE N'%Garage%')) AND
(StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
SANDING / HIGH
StepID -21
Wallboard and Round 10 feet and above from taping labor
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 21 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'SANDING'
AS ItemDescription ,
SUM(ItemQuantit y) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3)) AND (NOT
(ItemDescriptio n LIKE N'% 8%')) AND (NOT (ItemDescriptio n LIKE N'%
9%')) AND
(NOT (ItemDescriptio n LIKE
N'%GARAGE%'))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
WINDOWS
StepID -22
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 22 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,

MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,

MaterialItemMas ter.MaterialIte mMasterDescript ion AS ItemDescription ,
SUM(MixLineItem .FloorPlanQuant ity * FloorPlanLineIt em.Quantity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID =
MixLineItem.Flo orPlanHeaderID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NOT
NULL) AND (MixLineItem.Mi xLevelID IS NOT NULL) AND

(MaterialItemMa ster.MaterialIt emMasterID = 802) OR

(MixLineItem.Mi xBuildingID IS NOT NULL) AND (MixLineItem.Mi xLevelID IS
NULL) AND (MaterialItemMa ster.MaterialIt emMasterID = 802)
GROUP BY
MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y
HAVING (MixLineItem.Mi xHeaderID =
@MixHeaderID)) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
UNION ALL
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 22 AS StepID, FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader .PlanName + '~' +
ISNULL(FloorPla nHeader.Attribu te1, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te2, '')
+ '~' +
ISNULL(FloorPla nHeader.Attribu te3, '') AS UnitName,
MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion AS
ItemDescription , SUM(FloorPlanLi neItem.Quantity ) AS ItemQuantity, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON
FloorPlanHeader .FloorPlanHeade rID = MixLineItem.Flo orPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.Mix BuildingID = MixBuilding.Mix BuildingID
WHERE (MixLineItem.Mi xBuildingID IS NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND
(MaterialItemMa ster.MaterialIt emMasterID = 802)
GROUP BY
MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName + '~' + ISNULL(FloorPla nHeader.Attribu te1,
'')
+ '~' +
ISNULL(FloorPla nHeader.Attribu te2, '') + '~' +
ISNULL(FloorPla nHeader.Attribu te3, ''), FloorPlanHeader .JobMasterID,
MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID)) VT
INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
CAULKING
StepID -23
*/
INSERT INTO @table_variable
SELECT Caulking.StepID , Caulking.JobMas terID,
Caulking.MixHea derID, Caulking.BidSub typeID, Caulking.WorkTy peID,
Caulking.UnitNa me,
Caulking.UnitQu antity, Caulking.ItemDe scription,
Caulking.ItemQu antity, Scale.Price, Caulking.ItemQu antity *
Scale.Price AS ExtendedPrice,
Caulking.IsVisi ble, Caulking.WSQtyA dj
FROM (SELECT - 23 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'CAULKING'
AS ItemDescription ,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescriptio n = N'SUNDRIES'))
Caulking INNER JOIN
PickupScale Scale ON Caulking.ItemDe scription =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
PRE-PAINT
StepID -24
NOTE: COPIED FROM CUT & SCRAPE
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 24 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'PRE-PAINT'
AS ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescriptio n LIKE
N'%Garage%')) AND (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
FINALS
StepID -25
NOTE: COPIED FROM CUT & SCRAPE
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 25 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'FINALS' AS
ItemDescription ,
SUM(ItemQuantit y) / 1000 AS ItemQuantity,
IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescriptio n LIKE
N'%Garage%')) AND (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
CAPITAL
StepID -26
NOTE: SAME TOTAL ITEMVALUE AS TAPING LABOR FOREMAN
BUT WITH A SCALE THAT IS PULLED FORM THE PICKUP SCALE
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 26 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'CAPITAL'
AS ItemDescription ,
SUM(ItemQuantit y) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

FRAMING
SUBTYPE 8

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -27
*/
INSERT INTO @table_variable
SELECT - 27 AS StepID, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID, 8 AS BidSubTypeID,
FloorPlanLineIt em.WorkTypeID,
MixBuilding.Mix BuildingDescrip tion AS UnitName,
MixBuilding.Mix BuildingQuantit y AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(MixLineItem .FloorPlanQuant ity *
FloorPlanLineIt em.Quantity)
AS ItemQuantity, FramingScale.Pr ice AS
ScaleValue, SUM(MixLineItem .FloorPlanQuant ity *
FloorPlanLineIt em.Quantity * FramingScale.Pr ice)
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
FramingScale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
FramingScale.It emDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND (FramingScale.A reaTypeID =
@AreaTypeID) AND
(FramingScale.E ffectiveDate =
@MaterialEffect iveDate) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND (FramingScale.A reaTypeID =
@AreaTypeID) AND
(FramingScale.E ffectiveDate =
@MaterialEffect iveDate)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
MixBuilding.Mix BuildingDescrip tion, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID,
MixBuilding.Mix BuildingQuantit y, FramingScale.Pr ice,
FloorPlanLineIt em.WorkTypeID
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(FloorPlanLineI tem.WorkTypeID = 3)
UNION ALL
SELECT - 27 AS StepID, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID, 8 AS BidSubTypeID,
FloorPlanLineIt em.WorkTypeID,
FloorPlanHeader .PlanName AS UnitName,
MixLineItem.Flo orPlanQuantity AS UnitQuantity,
MaterialItemMas ter.MaterialIte mMasterDescript ion
AS ItemDescription , SUM(FloorPlanLi neItem.Quantity ) AS ItemQuantity,
FramingScale.Pr ice AS ScaleValue,
FloorPlanLineIt em.Quantity * FramingScale.Pr ice AS ExtendedPrice,
SUM(0) AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineIt em INNER JOIN
MaterialItemMas ter ON
FloorPlanLineIt em.MaterialItem MasterID =
MaterialItemMas ter.MaterialIte mMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineIt em.FloorPlanHea derID =
FloorPlanHeader .FloorPlanHeade rID INNER JOIN
MixLineItem ON FloorPlanHeader .FloorPlanHeade rID
= MixLineItem.Flo orPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.Mix HeaderID =
MixHeader.MixHe aderID INNER JOIN
FramingScale ON
MaterialItemMas ter.MaterialIte mMasterDescript ion =
FramingScale.It emDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.Mix BuildingID =
MixBuilding.Mix BuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineIt em.DimensionID =
Dimension.Dimen sionID
WHERE (MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NOT NULL) AND (FramingScale.A reaTypeID =
@AreaTypeID) AND
(FramingScale.E ffectiveDate =
@MaterialEffect iveDate) OR
(MixLineItem.Mi xBuildingID IS NOT NULL) AND
(MixLineItem.Mi xLevelID IS NULL) AND (FramingScale.A reaTypeID =
@AreaTypeID) AND
(FramingScale.E ffectiveDate =
@MaterialEffect iveDate)
GROUP BY MaterialItemMas ter.MaterialIte mMasterDescript ion,
FloorPlanHeader .PlanName, MixHeader.JobMa sterID,
MixLineItem.Mix HeaderID,
MixLineItem.Flo orPlanQuantity,
FramingScale.Pr ice, FloorPlanLineIt em.WorkTypeID,
FloorPlanLineIt em.Quantity * FramingScale.Pr ice
HAVING (MixLineItem.Mi xHeaderID = @MixHeaderID) AND
(FloorPlanLineI tem.WorkTypeID = 3)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SUBCONTRACTOR
SUBTYPE 6

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
PRELIM SCRAP
STEP -28
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 28 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.1 AS WorkTypeID, UnitName, UnitQuantity,
N'PRELIM SCRAP' AS
ItemDescription , SUM(ItemQuantit y) / 1000 AS ItemQuantity, IsVisible,
WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 12, - 13))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
SCRAP
STEP -29
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 29 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.2 AS WorkTypeID, UnitName, UnitQuantity,
N'SCRAP' AS ItemDescription , SUM(ItemQuantit y) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
CLEAN-UP
STEP -30
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 30 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.3 AS WorkTypeID, UnitName, UnitQuantity,
N'CLEAN-UP' AS ItemDescription ,
SUM(ItemQuantit y) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
WINDOWS
STEP -31
*/
INSERT INTO @table_variable
SELECT Window.StepID, Window.JobMaste rID, Window.MixHeade rID,
Window.BidSubty peID, Window.WorkType ID, Window.UnitName ,
Window.UnitQuan tity, Window.ItemDesc ription,
Window.ItemQuan tity, Scale.Price, Window.ItemQuan tity * Scale.Price AS
ExtendedPrice,
Window.IsVisibl e, Window.WSQtyAdj
FROM (SELECT - 31 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.4 AS WorkTypeID, UnitName, UnitQuantity, 'WINDOWS'
AS ItemDescription ,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescriptio n = N'SUNDRIES'))
Window INNER JOIN
SubcontractorSc ale Scale ON
Window.ItemDesc ription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
TOOLS
STEP -32
*/
INSERT INTO @table_variable
SELECT Tool.StepID, Tool.JobMasterI D, Tool.MixHeaderI D,
Tool.BidSubtype ID, Tool.WorkTypeID , Tool.UnitName,
Tool.UnitQuanti ty, Tool.ItemDescri ption,
Tool.ItemQuanti ty, Scale.Price, Tool.ItemQuanti ty * Scale.Price AS
ExtendedPrice,
Tool.IsVisible, Tool.WSQtyAdj
FROM (SELECT - 32 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, 'TOOLS' AS
ItemDescription ,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescriptio n = N'SUNDRIES'))
Tool INNER JOIN
SubcontractorSc ale Scale ON Tool.ItemDescri ption =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
SPRAYING
STEP -33
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS ExtendedPrice,
VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 33 AS StepID, JobMasterID, MixHeaderID, 6 AS
BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'SPRAYING' AS
ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
SUBSISTENCE
STEP -38 (NAILING LABOR FOREMAN / 1000)
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMast erID, Nailing.MixHead erID, 6
AS BidSubtypeID, Nailing.WorkTyp eID, Nailing.UnitNam e,
Nailing.UnitQua ntity,
Nailing.ItemDes cription, Nailing.ItemQua ntity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQu antity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisib le,
Nailing.WSQtyAd j
FROM (SELECT - 38 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, 'SUBSISTENCE'
AS ItemDescription ,
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription ,
BidSubtypeID
HAVING (ItemDescriptio n = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SubcontractorSc ale Scale ON
Nailing.ItemDes cription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
MASKING
STEP -39
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 39 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'MASKING'
AS ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
PREP COAT
STEP -40
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 40 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'PREP
COAT' AS ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorSc ale Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SUPERVISION
SUBTYPE 7

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
NAILING
STEP -34
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMast erID, Nailing.MixHead erID, 7
AS BidSubtypeID, Nailing.WorkTyp eID, Nailing.UnitNam e,
Nailing.UnitQua ntity,
Nailing.ItemDes cription, Nailing.ItemQua ntity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQu antity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisib le,
Nailing.WSQtyAd j
FROM (SELECT - 34 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'NAILING' AS
ItemDescription ,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription ,
BidSubtypeID
HAVING (ItemDescriptio n = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScal e Scale ON
Nailing.ItemDes cription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
TAPING
STEP -35
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 35 AS StepID, JobMasterID, MixHeaderID, 7 AS
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'TAPING' AS
ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
SupervisionScal e Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
PICK-UP
STEP -36
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID ,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity , VT.ItemDescript ion,
VT.ItemQuantity ,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 36 AS StepID, JobMasterID, MixHeaderID, 7
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'PICK-UP'
AS ItemDescription ,
SUM(ItemQuantit y) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SupervisionScal e Scale ON VT.ItemDescript ion =
Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID)
/*
PROJ MGR
STEP -37
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMast erID, Nailing.MixHead erID, 7
AS BidSubtypeID, Nailing.WorkTyp eID, Nailing.UnitNam e,
Nailing.UnitQua ntity,
Nailing.ItemDes cription, Nailing.ItemQua ntity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQu antity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisib le,
Nailing.WSQtyAd j
FROM (SELECT - 37 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'PROJ MGR' AS
ItemDescription ,
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription ,
BidSubtypeID
HAVING (ItemDescriptio n = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScal e Scale ON
Nailing.ItemDes cription = Scale.ItemDescr iption
WHERE (Scale.Effectiv eDate = @LaborEffective Date) AND
(Scale.AreaType ID = @AreaTypeID);
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SCALE CHANGES ---- NOT USED--- PERFORMED FROM INSIDE OF WRAPPER PROC

//////////////////////////////////////////////////////////////////////////////////////////////////
In this case, we needed two subqueries: one to pick up the values that
we wanted and another to apply these to the correct rows on the outer
table.
*/
/*
update @table_variable
set scalevalue =(select scalevalue from ScaleChange
where JobMasterID = ScaleChange.Job MasterID AND BidSubtypeID =
ScaleChange.Bid SubtypeID AND ItemDescription =
ScaleChange.Ite mDescription)
where exists (select * from ScaleChange
where JobMasterID = ScaleChange.Job MasterID AND BidSubtypeID =
ScaleChange.Bid SubtypeID AND ItemDescription =
ScaleChange.Ite mDescription)
*/
RETURN
END
Jul 20 '05 #1
1 3427
Stephen Patten (st***********@ hotmail.com) writes:
While in the process of building my table (40 or so Insert statments)
can I then query ("select * from @Table_variable ") and use the results
up to theat point for another insert into @Table_varible? If you look
for stepID -15 I have commented that section out due to it not
retuning the correct values.


Could I ask you that next time you post a question, to be kind to be a
little more elaborate about what your problem is, and include any error
messages you get? At the same time, could you please trim down the
amount of code you post to the relevant parts? 2000 lines of code is a
little wee bit too much.

I have to decline to answer your actual question, because I don't see
what you are getting at. I can only give the recommendation that
you always specify which columns you are inserting into. This makes
the code easier to read, less sensitive to changes in the table
definition.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2343
by: Jenta | last post by:
A World Beyond Capitalism 2005, An Annual International Multiracial Alliance Building Peace Conference Is Accepting Proposals... ...and Online Registration is now available if you plan to table and participate in the International Grassroots Exhibition: http://www.lfhniivaaaa.info/awbcgrassrootsofpeace We would greatly like some proposals from all people worldwide, especially
3
3891
by: Patrice | last post by:
Hi, I need to do multi-conditional statements like below, but this error is displayed : Expected 'End' /myFilepath, line x else response.write(arrCorpo(sparam,sdiv)) end if I don't understand why this 'Expected 'End'' error is called! Here is my code: <%
7
2096
by: MLH | last post by:
Building Applications with Microsoft Access 97 is a publication I think I need. Is it available in book form? Is MicroSoft the sole vendor? Anybody got a copy they wanna sell???
4
3185
by: Andy_Khosravi | last post by:
I'm trying to build a search utility for users to find 'inquiries' in my database that involves several tables. This is normally easy to do with the query builder, but I have a unique situation involving a multi select listbox. Unfortunatly, my SQL skills are somewhat limited, so I'm not sure if there is an easy way around it. To simplify the explanation, I'll simplify the table/field setup to get at the meat of the question. I have a...
1
4372
zachster17
by: zachster17 | last post by:
Hello all, I'm trying to write a multi-statement table function that returns a table of addresses from a remote database (Oracle) using OpenQuery and I'm having a hard time getting it to work with the 1 variable constraint it has (provider ID) The code is below: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
1
1831
by: mansi sharma | last post by:
Three types of Functions are there- 1)Simple Scalar function-It returns a scalar value. 2)Inline-Table Valued UDF- It allows us to return the table. Am I right or wrong?? 3) Multi-statement table valued UDF- Can somebody tell me the definition of it. create table studentp(roll int,name varchar,marks int,address varchar(10))
0
2569
by: Mark C. Stock | last post by:
"Mark C. Stock" <mcstockX@Xenquery .comwrote in message news:... | | "Berend" <Berend.Brinkhuis@evatone.comwrote in message | news:bdd9ac20.0401271301.22cdb65e@posting.google.com... | | I am trying to pass multi values into a where clause with an in clause | | in a store procedure to use in a Crystal report. This can change | | depending on the user. Maybe there is another way to pass multi | | values. | | | |
1
4887
by: KrazyKasper | last post by:
Access 2003 – Multi-Column List Box – Select Multiple Items I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended). I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the...
7
7158
Curtis Rutland
by: Curtis Rutland | last post by:
Building A Silverlight (2.0) Multi-File Uploader All source code is C#. VB.NET source is coming soon. Note: This project requires Visual Studio 2008 SP1 or Visual Web Developer 2008 SP1 and Silverlight 2.0. To get these tools please visit this page Get Started : The Official Microsoft Silverlight Site and follow Step 1. Occasionally you find the need to have users upload multiple files at once. You could use multiple FileUpload...
0
9646
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9483
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10096
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9956
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6742
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5386
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5514
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4055
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2887
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.