gistfile1.txt

Here's a report that show the weight of material currently being worked on...

SELECT  CASE WHEN @@LANGID = 0 THEN cat.Description ELSE cat.Description_2 END AS ChargeAccountType,
  	ca.Code AS ChargeAccount_Code,
		ca.Description AS ChargeAccount_Description, 
		ROUND(pw.TotalWeight / 2000.0,2) AS TotalWeight, 
		ROUND(pw.BoltWeight /2000.0,2) AS BoltWeight, 
		ROUND(pw.ReleasedWeight / 2000.0,2) AS ReleasedWeight, 
		ROUND(pw.FabricatedWeight / 2000.0,2) AS FabricatedWeight, 
		ROUND(sw.ShippedMaterialWeight / 2000.0,2) AS ShippedMaterialWeight,
		ROUND(sw.ShippedBoltWeight / 2000.0,2) AS ShippedBoltWeight
FROM tblChargeAccount ca
	JOIN tblChargeAccountType cat ON cat.Id = ca.ChargeAccountType_Id	
	JOIN tblJob jo ON jo.ChargeACcount_Id = ca.Id
	JOIN dbo.GetJobWeights({AsOfDate}) pw ON jo.Id = pw.Job_Id
	LEFT JOIN dbo.GetOpenJobShipWeights2({AsOfDate}) sw ON sw.Job_Id = jo.Id
WHERE jo.Id IN (SELECT cwo.job_Id FROM tblCustomWO cwo WHERE (Date_Completed IS NULL OR Date_Completed > {AsOfDate}))
ORDER BY cat.Code, ca.Code

It uses a SQL function to calculate those weights for all jobs.


CREATE FUNCTION GetOpenJobShipWeights2(@AsOfDate DateTime) 
RETURNS @Weights TABLE (Job_Id int, 
  				ShippedMaterialWeight Numeric(18,6), 
					ShippedBoltWeight Numeric(18,6)) 
BEGIN
INSERT INTO @Weights
SELECT	ar.Job_Id, 
		SUM(CASE WHEN jat.Code <> 'B' THEN ar.ExtendedQuantity * dbo.GetAssemblyStandardWeightWithoutChildren(ar.Job_Id, ar.ChildJobAssembly_Id,1) ELSE 0 END) AS ShippedMaterialWeight,
		SUM(CASE WHEN jat.Code = 'B' THEN ar.ExtendedQuantity * dbo.GetAssemblyStandardWeightWithoutChildren(ar.Job_Id, ar.ChildJobAssembly_Id,1) ELSE 0 END) AS ShippedBoltWeight
FROM (SELECT DISTINCT ps.Id 
		FROM tblWOBillOfLading bol 
			JOIN tblWOBillOfLadingPSAssign psa ON bol.Id = psa.WOBillOfLading_Id
			JOIN tblWOPackingSlip ps ON psa.WOPackingSlip_Id = ps.Id
			JOIN tblCustomWO cwo ON cwo.Id = ps.CustomWO_Id
		WHERE bol.PrintedFlag = 1 AND bol.WOBillOfLadingDate <= @AsOfDate
		AND (cwo.Date_Completed IS NULL OR cwo.Date_Completed > @AsOfDate) AND cwo.Date_Created < @AsOfDate) ps2 
	CROSS APPLY dbo.GetShippedAssemblies(ps2.Id) ar
	JOIN tblJobAssembly ja ON ja.Id = ar.ChildJobAssembly_Id
	LEFT JOIN tblJobAssemblyType jat ON jat.Id = ja.JobAssemblyType_Id

GROUP BY ar.Job_Id  
RETURN
END

If needs to find out the weight of each job,

CREATE FUNCTION GetJobWeights(@AsOfDate DateTime) 
RETURNS @Weights TABLE (Job_Id int, 
  				TotalWeight Numeric(18,6), 
					BoltWeight Numeric(18,6),
					ReleasedWeight Numeric(18,6),
					FabricatedWeight Numeric(18,6)) 
BEGIN
	INSERT INTO @weights
	SELECT ta.Job_id, 
		ta.TotalWeight,
		ta.BoltWeight,
		ca.ReleasedWeight,
		ca.FabricatedWeight 
	FROM (	SELECT ja.Job_Id,
			SUM(CASE WHEN jat.Code <> 'B' THEN jar.ExtendedQuantity * ISNULL(aw.StandardWeight,0) ELSE 0 END) AS TotalWeight, -- Not coated and not including bolts
			SUM(CASE WHEN jat.Code = 'B' THEN jar.ExtendedQuantity * ISNULL(aw.StandardWeight,0) ELSE 0 END) AS BoltWeight -- Not coated and not including bolts
			FROM dbo.GetOpenWorkorderAssemblyRollup2(@AsOfDate) jar 
				JOIN tblJobAssembly ja ON ja.Id = jar.ChildJobAssembly_Id
				JOIN tblJobAssemblyType jat ON jat.Id = ja.JobAssemblyType_Id
				LEFT JOIN dbo.GetAssemblyStandardWeights(1) aw ON jar.ChildJobAssembly_Id = aw.JobAssembly_Id 
			GROUP BY ja.Job_Id) ta
	LEFT JOIN (
		SELECT pt.Job_Id,
			SUM(CASE pt.ProductionTransactionType_Id WHEN 1 THEN CompletedQuantity * StandardUnitWeight ELSE 0 END) AS ReleasedWeight, 
			SUM(CASE pt.ProductionTransactionType_Id WHEN 2 THEN CompletedQuantity * StandardUnitWeight ELSE 0 END) AS FabricatedWeight
			FROM tblProductionTransaction pt 
				JOIN tblJobAssembly ja ON ja.Id = pt.JobAssembly_Id
				JOIN tblJobAssemblyType jat ON ja.JobAssemblyType_Id = jat.Id
		WHERE pt.ProductionTransactionType_Id IN (1,2)
			-- Only include Marks, and submarks!!!
			AND jat.Code IN ('M','SM')
			AND pt.TransactionDate <= @AsOfDate
		GROUP BY pt.Job_Id ) ca ON ca.Job_id = ta.Job_Id
		
		RETURN
END
GO


and it needs to find out what assemblies where already shipped out hence the following queries


CREATE FUNCTION GetShippedAssemblies(@WOPackingSlipId int) 
RETURNS @Assy TABLE (  id int, 
			Job_Id int, 
			JobAssembly_Id int,
			ChildJobAssembly_Id int, 
			WOPackingSlipItem_Id int,
			Quantity int,
			ExtendedQuantity int,
			Step int ) 

BEGIN
	INSERT INTO @Assy
	SELECT  jar.Id, 
			jar.Job_Id, 
			jar.JobAssembly_Id, 
			jar.ChildJobAssembly_Id, 
			psib.id AS WOPackingSlipItem_Id,
			SUM(bui.Quantity), 
			SUM(bui.Quantity), 
			0  
	FROM tblWOPackingSlipItem psib
		JOIN tblBulkBundle bu ON bu.id = psib.BulkBundle_Id
		JOIN tblBulkBundleItem bui ON bui.BulkBundle_Id = bu.id
		JOIN tblJobAssemblyRelation jar ON jar.id = bui.JobAssemblyRelation_Id
	WHERE psib.WOPackingSlip_Id = @WOPackingSlipID
	GROUP BY jar.Id, jar.Job_Id, jar.JobAssembly_Id, jar.ChildJobAssembly_Id, psib.id
	UNION
	SELECT jar.Id, 
			jar.Job_Id, 
			jar.JobAssembly_Id, 
			jar.ChildJobAssembly_Id, 
			psip.id AS WOPackingSlipItem_Id,
			SUM(psip.Quantity * jbi.Quantity), 
			SUM(psip.Quantity * jbi.Quantity), 
			0
	FROM tblWOPackingSlipItem psip
		JOIN tblJobBundle jbu ON jbu.id = psip.JobBundle_Id
		JOIN tblJobBundleItem jbi ON jbi.JobBundle_Id = jbu.id
		JOIN tblJobAssemblyRelation jar ON jar.id = jbi.JobAssemblyRelation_Id
	WHERE psip.WOPackingSlip_Id = @WOPackingSlipID
	GROUP BY jar.Id, jar.Job_Id, jar.JobAssembly_Id, jar.ChildJobAssembly_Id, psip.Id


	DECLARE @Continue bit
	SELECT @Continue = 1

	-- While we are still finding more rows to add, loop.
	WHILE  @Continue = 1
	BEGIN
		-- Add to temporary table all assemblies who have a parent assembly 
		-- already in the temporary table, unless the assembly is already there.

		UPDATE @Assy SET Step = 1 WHERE Step = 0
		INSERT INTO @Assy SELECT 	ar.id,
						ar.job_id, 
						ar.JobAssembly_id, 
						ar.ChildJobAssembly_Id,
						fa.WOPackingSlipItem_Id,
						ar.quantity, 
						ar.Quantity  * fa.ExtendedQuantity  as ExtendedQuantity,
						0 AS Step
					FROM tblJobAssemblyRelation ar 
						JOIN @Assy fa ON fa.ChildJobAssembly_id = ar.JobAssembly_id
					WHERE fa.Step = 1
--					WHERE  ar.id NOT IN (SELECT id FROM @Assy)
		IF @@ROWCOUNT = 0 SELECT @Continue = 0
		UPDATE @Assy SET Step = 2 WHERE Step = 1

	END

	RETURN
END



CREATE FUNCTION dbo.GetAssemblyStandardWeightWithoutChildren(@JobId int, @JobAssemblyId int, @CoatedWeight bit) RETURNS Numeric(9,2)
AS
BEGIN
  DECLARE @NetWeight numeric(9,2)

  SELECT  @NetWeight = SUM(CASE WHEN Inventory_Flag = 1 
								THEN ROUND((pp.PartQty - (pp.QtyPieces * pp.DropArea)) * pa.Qty2Weight_Factor,2) 
								ELSE jrni.UnitWeight * pp.qty END 
								* CASE WHEN @CoatedWeight = 1 AND NOT Coating_Id IS NULL
									THEN (1 + (CoatingPercent / 100)) ELSE 1 END ) 
	FROM tblJobPlanPart pp
		LEFT JOIN tblPart pa ON pa.id = pp.Part_Id
		LEFT JOIN tblJobReqNonInventory jrni ON pp.JobReqNonInventory_Id = jrni.id
	WHERE pp.Job_Id = @JobId AND pp.Assembly_Id = @JobAssemblyId

RETURN @NetWeight
END