building a multi statement table UDF

This topic is closed.

Stephen Patten
#1

building a multi statement table UDF

Jul 20 '05, 12:46 AM

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
Tags: None
Erland Sommarskog
#2

Jul 20 '05, 12:46 AM

Re: building a multi statement table UDF

Stephen Patten (stephenpatten@ hotmail.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.
Comment

Previous template Next

building a multi statement table UDF

building a multi statement table UDF

Comment