CREATE PROCEDURE Generate_Std_Costs @cost_date DateTime, @rmc Bit, @fga Bit AS

DECLARE @part_no Char(8), @opening_stock Numeric(9,3), @last_cost_date Datetime, @last_cost Money, @new_cost Money, @update Bit

SET NOCOUNT ON

BEGIN TRANSACTION

	DECLARE Cursor_Item_Master CURSOR 
	LOCAL FAST_FORWARD 
	FOR 
	SELECT Item_Master.part_no, Item_Master.opening_stock, StdCost_Master.last_cost_date, StdCost_Master.last_cost 
		FROM Item_Master LEFT JOIN 
		( SELECT A.part_no, A.cost_date AS last_cost_date, A.cost AS last_cost FROM StdCost_Master A 
			INNER JOIN 
			( SELECT part_no, MAX(cost_date) AS last_cost_date 
				FROM StdCost_Master 
				WHERE cost_date < @cost_date 
				GROUP BY part_no ) B 
			ON A.part_no = B.part_no AND A.cost_date = B.last_cost_date ) StdCost_Master 
		ON Item_Master.part_no = StdCost_Master.part_no 
		ORDER BY Item_Master.group_code

	OPEN Cursor_Item_Master

	FETCH NEXT FROM Cursor_Item_Master INTO @part_no, @opening_stock, @last_cost_date, @last_cost

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @new_cost = 0

		IF EXISTS ( SELECT rm_part_no, qty FROM Parts_List WHERE part_no = @part_no )
		BEGIN

			IF @fga = 1
				EXECUTE Std_Cost_Derived @part_no, @rmc, @cost_date, @new_cost = @new_cost OUTPUT

			SET @update = @fga

		END

		ELSE
		BEGIN

			IF @rmc = 1
				EXECUTE Std_Cost_Purchase_Based @part_no, @cost_date, @opening_stock, @last_cost_date, @last_cost, 
					@new_cost = @new_cost OUTPUT

			SET @update = @rmc

		END

		IF @update = 1
		BEGIN
			IF EXISTS ( SELECT cost FROM StdCost_Master WHERE part_no = @part_no AND cost_date = @cost_date )
				UPDATE StdCost_Master 
					SET cost = @new_cost 
					WHERE part_no = @part_no AND cost_date = @cost_date
			ELSE
				INSERT INTO StdCost_Master VALUES ( @part_no, @cost_date, @new_cost )
		END

		FETCH NEXT FROM Cursor_Item_Master INTO @part_no, @opening_stock, @last_cost_date, @last_cost

	END

	CLOSE Cursor_Item_Master
	DEALLOCATE Cursor_Item_Master

	IF EXISTS( SELECT cost_date FROM StdCost_Dates WHERE cost_date = @cost_date )
		UPDATE StdCost_Dates 
			SET rmc = CASE rmc WHEN 1 THEN 1 ELSE @rmc END, 
				fga = CASE fga WHEN 1 THEN 1 ELSE @fga END
	ELSE
		INSERT INTO StdCost_Dates VALUES ( @cost_date, @rmc, @fga )

COMMIT TRANSACTION

SET NOCOUNT OFF

RETURN

CREATE PROCEDURE Std_Cost_Derived @part_no Char(8), @rmc Bit, @cost_date Datetime, @new_cost Money OUTPUT AS

DECLARE @rm_part_no Char(8), @qty Numeric(9,3), @rm_new_cost Money, 
		@opening_stock Numeric(9,3), @last_cost_date Datetime, @last_cost Money

DECLARE Cursor_Parts_List CURSOR 
LOCAL FAST_FORWARD 
FOR 
SELECT rm_part_no, qty 
	FROM Parts_List 
	WHERE part_no = @part_no

OPEN Cursor_Parts_List

FETCH NEXT FROM Cursor_Parts_List INTO @rm_part_no, @qty

IF @@FETCH_STATUS = 0
BEGIN

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @rm_new_cost = 0

		EXECUTE Std_Cost_Derived @rm_part_no, @rmc, @cost_date, @new_cost = @rm_new_cost OUTPUT

		SET @new_cost = @new_cost + ( @rm_new_cost * @qty )

		FETCH NEXT FROM Cursor_Parts_List INTO @rm_part_no, @qty

	END

END

ELSE
BEGIN

	IF @rmc = 1
	BEGIN
		SELECT @opening_stock = Item_Master.opening_stock, @last_cost_date = StdCost_Master.last_cost_date, 
				@last_cost = StdCost_Master.last_cost 
			FROM Item_Master 
			LEFT JOIN 
			( SELECT A.part_no, A.cost_date AS last_cost_date, A.cost AS last_cost FROM StdCost_Master A 
				INNER JOIN 
				( SELECT part_no, MAX(cost_date) AS last_cost_date 
					FROM StdCost_Master 
					WHERE cost_date < @cost_date 
					GROUP BY part_no ) B 
				ON A.part_no = B.part_no AND A.cost_date = B.last_cost_date ) StdCost_Master 
			ON Item_Master.part_no = StdCost_Master.part_no 
			WHERE Item_Master.part_no = @part_no
		EXECUTE Std_Cost_Purchase_Based @part_no, @cost_date, @opening_stock, @last_cost_date, @last_cost, @new_cost = @new_cost OUTPUT
	END

END

RETURN

	IF EXISTS ( SELECT rm_part_no, qty FROM Parts_List WHERE part_no = @part_no )