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,
@MaterialEffectiveDate nvarchar(10),
@LaborEffectiveDate nvarchar(10),
@AreaTypeID int,
@NailingParam int,
@TapingParam int
)
/*
@MixHeaderID int = 2,
@MaterialEffectiveDate nvarchar(10) = '2003-01-01',
@LaborEffectiveDate 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.JobMasterID,
MixLineItem.MixHeaderID, 1 AS BidSubTypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity *
(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity *
(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 *
MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialScale.Price
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 1 AS StepID, MixHeader.JobMasterID,
MixHeader.MixHeaderID, 1 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area /
1000) AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Dimension.Area / 1000 *
MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectivedate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''), MixHeader.MixHeaderID,
MixHeader.JobMasterID,
MixLineItem.FloorPlanQuantity,
MaterialScale.Price
HAVING (MixHeader.MixHeaderID = @MixHeaderID)
/*
STEP -2
STOCKING
Scale * Total Wallboard sq ft
*/
INSERT INTO @table_variable
SELECT -2, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, N'STOCKING', SUM(ItemQuantity),
dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,
@MaterialEffectiveDate), SUM(ItemQuantity) *
dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,
@MaterialEffectiveDate), 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(((TotalSquareFoot)/4000 + .4999), 0)
SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(ItemQuantity)/4) + .4999,
0), dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
ROUND((SUM(ItemQuantity)/4) + .4999, 0) *
dbo.BidContract_GetMaterialScaleValue(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(ItemQuantity)/4), 0),
dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
ROUND((SUM(ItemQuantity)/4), 0) *
dbo.BidContract_GetMaterialScaleValue(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(@WALLBOARD/250,0) + isnull(@FIRETAPING/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(@MixHeaderID,
UnitName), dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,
@MaterialEffectiveDate), dbo.BidContract_GetMudValue(@MixHeaderID,
UnitName) * dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,
@MaterialEffectiveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 2
/*
TAPE
Step -5
ROUND(SUM(ItemQuantity)/1100, 0)
*/
INSERT INTO @table_variable
SELECT - 5, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'TAPE', dbo.BidContract_GetTapeValue(@MixHeaderID,
UnitName), dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,
@MaterialEffectiveDate), dbo.BidContract_GetTapeValue(@MixHeaderID,
UnitName) * dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,
@MaterialEffectiveDate), 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.MixHeaderID, 2 AS BidSubtype, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialItemMaster.MaterialItemMasterDescription, MaterialScale.Price
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, (FloorPlanLineItem.Quantity * 1.1) / 1000 AS
ItemQuantity, MaterialScale.Price AS ScaleValue,
(FloorPlanLineItem.Quantity * 1.1) / 1000 *
MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MaterialItemMaster.MaterialItemMasterDescription,
MaterialScale.Price, FloorPlanLineItem.Quantity,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
MISC MATERIAL
Step -7
*/
INSERT INTO @table_variable
SELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtype,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NOT NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2))) OR
(MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2)))
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialScale.Price, FloorPlanLineItem.WorkTypeID
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
UNION ALL
SELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'') + '~' + ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, FloorPlanLineItem.Quantity AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity *
MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID IS
NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2)))
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MaterialScale.Price,
FloorPlanLineItem.WorkTypeID, MixLineItem.FloorPlanQuantity,
FloorPlanLineItem.Quantity
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
/*
COUNT OF LIVING UNITS
Step -8
****HOUSE***
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
****HOUSE***
*/
INSERT INTO @table_variable
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'SUNDRIES' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit
= '1') OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL)
GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingDescription, MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription,
1 AS ItemQuantity,
dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(1 *
dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
FIRE HAULT - CHECK FOR FIREPROOFING
Step -9
*/
IF EXISTS (
SELECT 'true' AS Expr1
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MaterialItemMaster.MaterialItemMasterDescription = N'FIRETAPING') AND
(JobMaster.ProjectTypeID <> 1))
BEGIN
INSERT INTO @table_variable
SELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidsubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'FIRE HAULT' AS
ItemDescription,
ISNULL(SUM(MixBuilding.MixBuildingQuantity *
MixLineItem.FloorPlanQuantity) / 2, 0) AS ItemQuantity,
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS ScaleValue,
ISNULL(SUM(MixBuilding.MixBuildingQuantity *
MixLineItem.FloorPlanQuantity) / 2, 0) *
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit
= '1') OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity, N'FIRE
HAULT' AS ItemDescription,
ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2,
0) AS ItemQuantity, dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID,
@MaterialEffectiveDate) AS ScaleValue,
ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2, 0) *
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID
IS NULL) AND (FloorPlanHeader.IsLivingUnit = '1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @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.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity, RTRIM(NailingLabor.ItemDescription) + N' '
+ dbo.Height.HeightDescription AS ItemDescription,
NailingLabor.ItemQuantity, Scale.Price +
dbo.Height.NailingRate + NailingLabor.ProductionNailingLaborIncrease
AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
dbo.Height.NailingRate + NailingLabor.ProductionNailingLaborIncrease)
AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract_NailingLabor_Wallboard_Production( @MixHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.HeightID = dbo.Height.HeightID INNER JOIN
dbo.BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =
@LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' +
dbo.Height.HeightDescription
INSERT INTO @table_variable
SELECT - 10.1 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity, RTRIM(NailingLabor.ItemDescription) + N' '
+ dbo.Height.HeightDescription AS ItemDescription,
NailingLabor.ItemQuantity, Scale.Price +
dbo.Height.NailingRateGarage +
NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
dbo.Height.NailingRateGarage +
NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract_NailingLabor_Wallboard_Production_ Garage(@MixHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.HeightID =
dbo.Height.HeightID INNER JOIN
dbo.BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =
@LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' +
dbo.Height.HeightDescription
END
IF (@NailingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @table_variable
SELECT - 10.2 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
AS ItemDescription, NailingLabor.ItemQuantity,
Scale.Price +
NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_NailingLabor_Wallboard_Production(@Mix HeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.HeightID =
Height.HeightID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =
@LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
--TODO: add garage
END
IF (@NailingParam = -3) --Straight
BEGIN
INSERT INTO @table_variable
SELECT - 10.3 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
AS ItemDescription, NailingLabor.ItemQuantity,
NailingLabor.ProductionNailingLaborStraight AS
ScaleValue,
NailingLabor.ItemQuantity *
NailingLabor.ProductionNailingLaborStraight AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_NailingLabor_Wallboard_Production(@Mix HeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.HeightID =
Height.HeightID
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
--TODO: add garage
END
/*
'ALL OTHER MATERIAL
Step -11
*/
INSERT INTO @table_variable
SELECT - 11 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
AS ItemQuantity,
BidContract_NailingLabor_Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity * BidContract_NailingLabor_Scale.Price) AS
ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale()
BidContract_NailingLabor_Scale ON
MaterialItemMaster.MaterialItemMasterDescription
= BidContract_NailingLabor_Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND
(BidContract_NailingLabor_Scale.EffectiveDate =
@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND
(BidContract_NailingLabor_Scale.EffectiveDate =
@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =
@AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID, MixBuilding.MixBuildingDescription,
MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, BidContract_NailingLabor_Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 11 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, SUM(MixLineItem.FloorPlanQuantity) AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND (Scale.EffectiveDate =
@LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MixHeader.JobMasterID, MixLineItem.MixHeaderID, Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @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.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.UnitQuantity * Scale.Price AS ExtendedPrice,
VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *
Dimension.Area)) AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1)
AND
(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND (MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList)))
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantity
HAVING (FloorPlanLineItem.WorkTypeID = 1)
AND (MixLineItem.MixHeaderID = @MixHeaderID)) VT INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
VT.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
UNION ALL
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.UnitQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName
+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription AS ItemDescription,
SUM(FloorPlanLineItem.Quantity * Dimension.Area) AS ItemQuantity, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL)
AND (MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList)))
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName
+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3,
''), JobMaster.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (FloorPlanLineItem.WorkTypeID = 1)
AND (MixLineItem.MixHeaderID = @MixHeaderID)) VT INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
VT.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
PRELIM SPECIAL BOARD
Step -13
*/
INSERT INTO @table_variable
SELECT - 13 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity *
(FloorPlanLineItem.Quantity * Dimension.Area)) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *
Dimension.Area) * Scale.Price) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON FloorPlanLineItem.MaterialItemMasterID
= MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY FloorPlanLineItem.WorkTypeID,
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @MixHeaderID) AND
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))
UNION ALL
SELECT - 13 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area)
AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Dimension.Area * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID = JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID =
WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY FloorPlanLineItem.WorkTypeID,
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
JobMaster.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity, Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @MixHeaderID) AND
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))
/*
'ALL OTHER PRELIM MATERIAL
Step -14
*/
INSERT INTO @table_variable
SELECT - 14 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity)
AS ItemQuantity, Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity *
Scale.Price) AS ExtendedPrice,
0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID, MixBuilding.MixBuildingDescription,
JobMaster.JobMasterID,
MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantity, Scale.Price
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 14 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
JobMaster.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity, Scale.Price
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @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(ItemQuantity) AS ItemQuantity,
(SELECT
SUM(T2.ExtendedPrice)
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.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, Scale.Price +
TapingLabor.TapingLaborIncrease + Height.TapingRate AS ScaleValue,
TapingLabor.ItemQuantity * (Scale.Price +
TapingLabor.TapingLaborIncrease + Height.TapingRate) AS ExtendedPrice,
TapingLabor.IsVisible,
TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
IF (@TapingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @table_variable
SELECT - 16.2 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, Scale.Price +
TapingLabor.TapingLaborIncrease AS ScaleValue,
TapingLabor.ItemQuantity * (Scale.Price +
TapingLabor.TapingLaborIncrease) AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
IF (@TapingParam = -3) --Straight
BEGIN
INSERT INTO @table_variable
SELECT - 16.3 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, TapingLabor.TapingLaborStraight AS
ScaleValue,
TapingLabor.ItemQuantity *
TapingLabor.TapingLaborStraight AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID) TapingLabor
INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON TapingLabor.ItemDescription
= Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
/*
METAL AND MISC ITEMS
StepID -17
*/
INSERT INTO @table_variable
SELECT - 17 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity)
AS ItemQuantity, Scale.Price AS ScaleValue,
SUM((MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
* (Scale.Price + JobMaster.TapingLaborIncrease))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795,
589, 584, 586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589,
584, 586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY JobMaster.JobMasterID,
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, Scale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 17 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * (Scale.Price +
JobMaster.TapingLaborIncrease)) AS ExtendedPrice, 0 AS IsVisible, 0 AS
WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID = MixHeader.MixHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589, 584,
586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY JobMaster.JobMasterID,
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'') + '~' + ISNULL(FloorPlanHeader.Attribute2,
'') + '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity, Scale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
BRACKETS
StepID -18
*/
INSERT INTO @table_variable
SELECT Brackets.StepID, Brackets.JobMasterID,
Brackets.MixHeaderID, Brackets.BidSubtypeID, Brackets.WorkTypeID,
Brackets.UnitName,
Brackets.UnitQuantity, Brackets.ItemDescription,
Brackets.ItemQuantity, Scale.Price AS Price, Brackets.ItemQuantity *
Scale.Price AS ExtendedPrice,
Brackets.IsVisible, Brackets.WSQtyAdj
FROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'BRACKETS' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS ItemQuantity, 0 AS IsVisible, 0
AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID =
@MixHeaderID)) Brackets INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
Brackets.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
UNION ALL
SELECT Brackets.StepID, Brackets.JobMasterID,
Brackets.MixHeaderID, Brackets.BidSubtypeID, Brackets.WorkTypeID,
Brackets.UnitName,
Brackets.UnitQuantity, Brackets.ItemDescription,
Brackets.ItemQuantity, Scale.Price AS Price, Brackets.ItemQuantity *
Scale.Price AS ExtendedPrice,
Brackets.IsVisible, Brackets.WSQtyAdj
FROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity, N'BRACKETS' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL)
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, ''), FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID))
Brackets INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
Brackets.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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(ItemQuantity) AS ItemQuantity,
(SELECT
SUM(T2.ExtendedPrice)
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.ItemDescription,
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(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescription 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.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3)) AND (NOT
(ItemDescription LIKE N'% 8%')) AND (NOT (ItemDescription LIKE N'%
9%')) AND
(NOT (ItemDescription LIKE
N'%GARAGE%'))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
WINDOWS
StepID -22
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 22 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialItemMasterID = 802) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND (MaterialItemMaster.MaterialItemMasterID = 802)
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID =
@MixHeaderID)) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
UNION ALL
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price AS ScaleValue, VT.ItemQuantity *
Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 22 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription AS
ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID = 802)
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' +
ISNULL(FloorPlanHeader.Attribute2, '') + '~' +
ISNULL(FloorPlanHeader.Attribute3, ''), FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)) VT
INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
CAULKING
StepID -23
*/
INSERT INTO @table_variable
SELECT Caulking.StepID, Caulking.JobMasterID,
Caulking.MixHeaderID, Caulking.BidSubtypeID, Caulking.WorkTypeID,
Caulking.UnitName,
Caulking.UnitQuantity, Caulking.ItemDescription,
Caulking.ItemQuantity, Scale.Price, Caulking.ItemQuantity *
Scale.Price AS ExtendedPrice,
Caulking.IsVisible, Caulking.WSQtyAdj
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 (ItemDescription = N'SUNDRIES'))
Caulking INNER JOIN
PickupScale Scale ON Caulking.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescription 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.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 1000 AS ItemQuantity,
IsVisible, WSQtyAdj
FROM @table_variable T1
WHERE (NOT (ItemDescription 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.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 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.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////
FRAMING
SUBTYPE 8
//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -27
*/
INSERT INTO @table_variable
SELECT - 27 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 8 AS BidSubTypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity)
AS ItemQuantity, FramingScale.Price AS
ScaleValue, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity * FramingScale.Price)
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
FramingScale ON
MaterialItemMaster.MaterialItemMasterDescription =
FramingScale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FramingScale.AreaTypeID =
@AreaTypeID) AND
(FramingScale.EffectiveDate =
@MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FramingScale.AreaTypeID =
@AreaTypeID) AND
(FramingScale.EffectiveDate =
@MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, FramingScale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @MixHeaderID) AND
(FloorPlanLineItem.WorkTypeID = 3)
UNION ALL
SELECT - 27 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 8 AS BidSubTypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
FramingScale.Price AS ScaleValue,
FloorPlanLineItem.Quantity * FramingScale.Price AS ExtendedPrice,
SUM(0) AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
FramingScale ON
MaterialItemMaster.MaterialItemMasterDescription =
FramingScale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FramingScale.AreaTypeID =
@AreaTypeID) AND
(FramingScale.EffectiveDate =
@MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FramingScale.AreaTypeID =
@AreaTypeID) AND
(FramingScale.EffectiveDate =
@MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity,
FramingScale.Price, FloorPlanLineItem.WorkTypeID,
FloorPlanLineItem.Quantity * FramingScale.Price
HAVING (MixLineItem.MixHeaderID = @MixHeaderID) AND
(FloorPlanLineItem.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.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
SCRAP
STEP -29
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
WINDOWS
STEP -31
*/
INSERT INTO @table_variable
SELECT Window.StepID, Window.JobMasterID, Window.MixHeaderID,
Window.BidSubtypeID, Window.WorkTypeID, Window.UnitName,
Window.UnitQuantity, Window.ItemDescription,
Window.ItemQuantity, Scale.Price, Window.ItemQuantity * Scale.Price AS
ExtendedPrice,
Window.IsVisible, 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 (ItemDescription = N'SUNDRIES'))
Window INNER JOIN
SubcontractorScale Scale ON
Window.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
TOOLS
STEP -32
*/
INSERT INTO @table_variable
SELECT Tool.StepID, Tool.JobMasterID, Tool.MixHeaderID,
Tool.BidSubtypeID, Tool.WorkTypeID, Tool.UnitName,
Tool.UnitQuantity, Tool.ItemDescription,
Tool.ItemQuantity, Scale.Price, Tool.ItemQuantity * 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 (ItemDescription = N'SUNDRIES'))
Tool INNER JOIN
SubcontractorScale Scale ON Tool.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
SPRAYING
STEP -33
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
SUBSISTENCE
STEP -38 (NAILING LABOR FOREMAN / 1000)
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 6
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
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 (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SubcontractorScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
MASKING
STEP -39
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 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
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////
SUPERVISION
SUBTYPE 7
//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
NAILING
STEP -34
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 7
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
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 (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
TAPING
STEP -35
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
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(ItemQuantity) / 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
SupervisionScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.ItemDescription,
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(ItemQuantity) / 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
SupervisionScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
/*
PROJ MGR
STEP -37
*/
INSERT INTO @table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 7
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
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 (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @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.JobMasterID AND BidSubtypeID =
ScaleChange.BidSubtypeID AND ItemDescription =
ScaleChange.ItemDescription)
where exists (select * from ScaleChange
where JobMasterID = ScaleChange.JobMasterID AND BidSubtypeID =
ScaleChange.BidSubtypeID AND ItemDescription =
ScaleChange.ItemDescription)
*/
RETURN
END