CREATE TABLE	#Bin
		(
			Number VARCHAR(100) NOT NULL,
			Units INT NOT NULL,
			Bin_ID  INT NOT NULL,
			Notation VARCHAR(100) NOT NULL,
			UserID VARCHAR(100) NOT NULL
		);

MERGE	MailOrderManager.dbo.BIN AS tgt
USING	(
		SELECT	UPPER(Number) AS Number,
			WH_PHYS_STOCK - MOM_PHYS_STOCK AS Units,
			'WEB' AS UserID,
			'BRAINTREE Stock Sync' AS Notation,
			'BRAINTREE' AS BinDesc,
			'A' AS [Status],
			1 AS Sell_Prior
		FROM	@RES 
		WHERE	WH_PHYS_STOCK > MOM_PHYS_STOCK
	) AS src ON src.BinDesc = tgt.BinDesc 
		AND src.Number = tgt.Number
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					Number,
					BinDesc,
					[Status],
					Sell_Prior,
					Units
				)
			VALUES	(
					src.Number,
					src.BinDesc,
					src.[Status],
					src.Sell_Prior,
					src.Units
				)
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Units += src.Units
OUTPUT	src.Number,
	src.Units,
	inserted.Bin_ID,
	src.Notation,
	src.UserID
INTO	#Bin
	(
		Number,
		Units,
		Bin_ID,
		Notation,
		UserID
	);

CREATE TABLE	#Invent
		(
			Number VARCHAR(100) NOT NULL,
			Invent_ID INT NOT NULL,
			Units INT NOT NULL,
			Unit_Cost MONEY NOT NULL,
			Bin_ID INT NOT NULL,
			Notation VARCHAR(100) NOT NULL,
			UserID VARCHAR(100) NOT NULL
		);

MERGE	dbo.Inventor AS tgt
USING	(
		SELECT		s.Number,
				CAST(GETDATE() AS DATE) AS Deliv_Date,
				COALESCE(w.UnitCost, 0) AS Unit_Cost,
				s.Units AS Deliv_Qty,
				s.Units,
				s.Bin_ID,
				Notation,
				UserID
		FROM		#Stage AS s
		LEFT JOIN	(
					SELECT		Number,
							MAX(ColsCost) AS UnitCost
					FROM		dbo.ST_EXTRA
					GROUP BY	Number
				) AS w ON w.Number = s.Number
	) AS src ON src.Number = tgt.Number
		AND src.Bin_ID = tgt.Bin_ID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					Number,
					Deliv_Date,
					Unit_Cost,
					Deliv_Qty,
					Units,
					Bin_ID
				)
			VALUES	(
					src.Number,
					src.Deliv_Date,
					src.Unit_Cost,
					src.Deliv_Qty,
					src.Units,
					src.Bin_ID
				)
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Units += src.Units
OUTPUT	src.Number,
	inserted.Invent_ID,
	src.Units,
	src.Unit_Cost,
	src.Bin_ID,
	src.Notation,
	src.UserID
INTO	#Invent
	(
		Number,
		Invent_ID,
		Units,
		Unit_Cost,
		Bin_ID,
		Notation,
		UserID
	);

DROP TABLE	#Bin;

INSERT		dbo.INVTRANS 
		(
			Number,
			Invent_ID,
			Trans_Date,
			TransType,
			Quantity,
			Unit_Cost,
			Total_Amt,
			UserID,
			NewInvTot,
			Notation,
			Bin_ID
		)
OUTPUT		inserted.Trans_Date AS [Date],
		inserted.Number,
		inserted.Units,
		inserted.UserID,
		inserted.Notation
INTO		POP..MOM_SUSPENSE
		(
			[Date],
			NUMBER,
			UNITS,
			USERID,
			NOTATION
		)
SELECT		i.Number,
		i.Invent_ID,
		CAST(GETDATE() AS DATE) AS Trans_Date,
		'A' AS TransType,
		i.Units AS Quantity,
		i.Unit_Cost,
		i.Unit_Cost * i.Units AS Totalt_Amt,
		i.UserID AS UserID,
		COALESCE(q.NewInvTotal, 0) + i.Units AS NewInvTot,
		i.Notation AS Notation,
		i.Bin_ID
FROM		#Invent AS i
OUTER APPLY	(
			SELECT TOP(1)	x.NewInvTot AS NewInvTotal
			FROM		dbo.INVTRANS  AS x WITH (NOLOCK) 
			WHERE		x.Number = i.Number
			ORDER BY	x.Trans_ID DESC
		) AS q(NewInvTotal);

DROP TABLE	#Invent;