CREATE PROCEDURE MIG_MBC_RULES_Sp
AS
BEGIN

	DELETE FROM MIG_MBC_RULES
	INSERT INTO MIG_MBC_RULES
	(
		[moid],
		[rule_id],
		[dsca],
		[actn],
		[objt],
		[optr],
		[quan],
		[chld],
		[crev],
		[rplc],
		[rrev],
		[updt],
		[dmpo],
		[dmrd]
	)
	SELECT
		MBC_RULES.MBC_ID,
		(10 * ROW_NUMBER() OVER(PARTITION BY MBC_RULES.MBC_ID ORDER BY MBC_RULES.MBC_ID, MBC_RULES.POSITION_NUMBER, MBC_RULES.RULE_ID) ) AS NEW_RULE_ID,  -- Matching the PROCESS ORDER of RULES of 10.2.4 ( w.r.t server code).
		--MBC_RULES.RULE_ID,
		MBC_RULES.RULE_DESCRIPT,
		MBC_RULES.ACTION_TYPE, 
		1, 
		MBC_RULES.OPERATOR_TYPE, 
		MBC_RULES.QUANTITY, 
		OLD_CHILD_ITEM_KEY.CLASS_KEY AS OLD_ITEM_KEY,
		OLD_CHILD_ITEM_REVISION.CLASS_KEY AS OLD_ITEM_REV,
		NEW_CHILD_ITEM_KEY.CLASS_KEY AS NEW_ITEM_KEY,
		NEW_CHILD_ITEM_REVISION.CLASS_KEY AS NEW_ITEM_REV,
		MBC_RULES.UPDATED_ON,
		MBC_RULES.POSITION_NUMBER,
		MBC_RULES.RULE_ID
	FROM MBC_RULES
	LEFT JOIN MBC_RULES_OBJS OLD_CHILD
	  ON OLD_CHILD.MBC_ID = MBC_RULES.MBC_ID
	 AND OLD_CHILD.POSITION_NUMBER = MBC_RULES.RULE_ID
	 AND OLD_CHILD.REPLACEMENT = '0'
	LEFT JOIN OBJECTS_KEYS OLD_CHILD_ITEM_KEY
	  ON OLD_CHILD_ITEM_KEY.OBJECT_ID = OLD_CHILD.OBJECT_ID
	 AND OLD_CHILD_ITEM_KEY.ORDER_NO = '1'
	LEFT JOIN OBJECTS_KEYS OLD_CHILD_ITEM_REVISION
	  ON OLD_CHILD_ITEM_REVISION.OBJECT_ID = OLD_CHILD.OBJECT_ID
	 AND OLD_CHILD_ITEM_REVISION.ORDER_NO = '2'
	LEFT JOIN MBC_RULES_OBJS NEW_CHILD
	  ON NEW_CHILD.MBC_ID = MBC_RULES.MBC_ID
	 AND NEW_CHILD.POSITION_NUMBER = MBC_RULES.RULE_ID
	 AND NEW_CHILD.REPLACEMENT = '1'
	LEFT JOIN OBJECTS_KEYS NEW_CHILD_ITEM_KEY
	  ON NEW_CHILD_ITEM_KEY.OBJECT_ID = NEW_CHILD.OBJECT_ID
	 AND NEW_CHILD_ITEM_KEY.ORDER_NO = '1'
	LEFT JOIN OBJECTS_KEYS NEW_CHILD_ITEM_REVISION
	  ON NEW_CHILD_ITEM_REVISION.OBJECT_ID = NEW_CHILD.OBJECT_ID
	 AND NEW_CHILD_ITEM_REVISION.ORDER_NO = '2'	
	
END


