CREATE PROCEDURE MIG_UPDATE_MAPPING_RULES_NEW_PDM_INFO_Sp
(
    @ZZ_ID INT
   ,@MAPPING_RULES_PDM_OBJ_ID NVARCHAR(MAX)
   ,@MAPPING_RULES_PDM_FLD_ID NVARCHAR(MAX) 
)
AS
BEGIN
	DECLARE @PDM_OBJ_TITLE_1 NVARCHAR(MAX)
	DECLARE @PDM_LINK_TITLE_1 NVARCHAR(MAX)
	DECLARE @ATTR_NAME_1 NVARCHAR(MAX)
	DECLARE @SOURCE_TABLE_1 NVARCHAR(MAX)
	DECLARE @SOURCE_FIELD_1 NVARCHAR(MAX)
	DECLARE @TARGET_TABLE_1 NVARCHAR(MAX)
	DECLARE @TARGET_FIELD_1 NVARCHAR(MAX)
	
	DECLARE @PDM_OBJ_TITLE_2 NVARCHAR(MAX)
	DECLARE @PDM_LINK_TITLE_2 NVARCHAR(MAX)
	DECLARE @ATTR_NAME_2 NVARCHAR(MAX)
	DECLARE @SOURCE_TABLE_2 NVARCHAR(MAX)
	DECLARE @SOURCE_FIELD_2 NVARCHAR(MAX)
	DECLARE @TARGET_TABLE_2 NVARCHAR(MAX)
	DECLARE @TARGET_FIELD_2 NVARCHAR(MAX)

	SET @PDM_OBJ_TITLE_1 = NULL
	SET @PDM_LINK_TITLE_1 = NULL
	SET @ATTR_NAME_1 = NULL
	SET @SOURCE_TABLE_1 = NULL
	SET @SOURCE_FIELD_1 = NULL
	SET @TARGET_TABLE_1 = NULL
	SET @TARGET_FIELD_1 = NULL

	SET @PDM_OBJ_TITLE_2 = NULL
	SET @PDM_LINK_TITLE_2 = NULL
	SET @ATTR_NAME_2 = NULL
	SET @SOURCE_TABLE_2 = NULL
	SET @SOURCE_FIELD_2 = NULL
	SET @TARGET_TABLE_2 = NULL
	SET @TARGET_FIELD_2 = NULL

	IF @MAPPING_RULES_PDM_OBJ_ID = 'ITEM'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'ITEM' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'ITEM'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'DOC'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'DOC'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'DOC' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('DOC.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'DOC'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'FILE'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'FILE'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'FILE' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('FILE.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'DATASET'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'PROJECT'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'PROJECT'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'PROJECT' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('PROJECT.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'PROJECT'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'ITEM_VAULT_HISTORY'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM_VAULT_HISTORY'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'ITEM.ITEM_VAULT_HISTORY' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.ITEM_VAULT_HISTORY.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'CHECK_ITEM'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'DOC_VAULT_HISTORY'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'DOC_VAULT_HISTORY'
			SET @PDM_LINK_TITLE_1 = ''
			SET @ATTR_NAME_1 = 'DOC.DOC_VAULT_HISTORY' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('DOC.DOC_VAULT_HISTORY.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'CHECK_DOC'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'SUPPLIER' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('ITEM.SUPPLIER.')) = 'ITEM.SUPPLIER.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = 'ITEM_SUPPLIERS'
			SET @ATTR_NAME_1 = 'ITEM_SUPPLIERS' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.SUPPLIER.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'ITM_VND_MNF'

			SET @PDM_OBJ_TITLE_2 = 'SUPPLIER'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'SUPPLIER.' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.SUPPLIER.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'SUPPLIER'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'CUSTOMER' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('ITEM.CUSTOMER.')) = 'ITEM.CUSTOMER.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = 'RELATED_CUSTOMER'
			SET @ATTR_NAME_1 = 'RELATED_CUSTOMER' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.CUSTOMER.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'ITEM_CUSTOMER'

			SET @PDM_OBJ_TITLE_2 = 'CUSTOMER'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'CUSTOMER.' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.CUSTOMER.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'CUSTOMER'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'MANUFACTURERS' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('ITEM.MANUFACTURERS.')) = 'ITEM.MANUFACTURERS.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = 'ITEM_MNFS'
			SET @ATTR_NAME_1 = 'ITEM_MNFS' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.MANUFACTURERS.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'ITEM_MNF'

			SET @PDM_OBJ_TITLE_2 = 'MANUFACTURERS'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'MANUFACTURERS.' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.MANUFACTURERS.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'MANUFACTURERS'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'SUB_CONTRACTOR' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('ITEM.SUB_CONTRACTOR.')) = 'ITEM.SUB_CONTRACTOR.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = 'ITEM_SUB_CONTRACTOR'
			SET @ATTR_NAME_1 = 'ITEM_SUB_CONTRACTOR' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.SUB_CONTRACTOR.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'ITEM_SUB'

			SET @PDM_OBJ_TITLE_2 = 'SUB_CONTRACTOR'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'SUB_CONTRACTOR.' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.SUB_CONTRACTOR.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'VENDORS'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'EO' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('ITEM.EO.')) = 'ITEM.EO.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'ITEM'
			SET @PDM_LINK_TITLE_1 = 'RELATED_EO'
			SET @ATTR_NAME_1 = 'RELATED_EO' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.EO.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'EO_ITEM'

			SET @PDM_OBJ_TITLE_2 = 'EO'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'EO' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('ITEM.EO.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'EO'
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'EO' AND SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, 1, LEN('DOC.EO.')) = 'DOC.EO.'
		BEGIN
			SET @PDM_OBJ_TITLE_1 = 'DOC'
			SET @PDM_LINK_TITLE_1 = 'RELATED_EO'
			SET @ATTR_NAME_1 = 'RELATED_EO' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('DOC.EO.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_1 = 'EO_DOC'

			SET @PDM_OBJ_TITLE_2 = 'EO'
			SET @PDM_LINK_TITLE_2 = ''
			SET @ATTR_NAME_2 = 'EO' + SUBSTRING(@MAPPING_RULES_PDM_FLD_ID, LEN('DOC.EO.'), LEN(@MAPPING_RULES_PDM_FLD_ID))
			SET @SOURCE_TABLE_2 = 'EO'
		END

	-- First search based on attribute at link level
	IF @PDM_OBJ_TITLE_1 IS NOT NULL
	BEGIN
		SELECT
				@SOURCE_FIELD_1 = OBJ_DEF_ATTR.LEGACY_ATTR,
				@TARGET_TABLE_1 = MIG_PROV_TABLE_MAPPING.TAR_TABLE_NAME,
				@TARGET_FIELD_1 = MIG_PROV_TABLE_FIELD_MAPPING.TAR_FIELD_NAME
		FROM	OBJ_DEF_ATTR
		LEFT JOIN MIG_PROV_TABLE_MAPPING
		  ON MIG_PROV_TABLE_MAPPING.SRC_BASE_TBL_NAME = @SOURCE_TABLE_1
		LEFT JOIN MIG_PROV_TABLE_FIELD_MAPPING
		  ON MIG_PROV_TABLE_FIELD_MAPPING.SRC_BASE_TBL_NAME = MIG_PROV_TABLE_MAPPING.SRC_BASE_TBL_NAME
		 AND MIG_PROV_TABLE_FIELD_MAPPING.SRC_BASE_FIELD_NAME = OBJ_DEF_ATTR.LEGACY_ATTR
		WHERE OBJ_DEF_ATTR.OBJ_TITLE = @PDM_OBJ_TITLE_1
		 AND OBJ_DEF_ATTR.LINK_TITLE = @PDM_LINK_TITLE_1
		 AND OBJ_DEF_ATTR.ATTR_NAME = @ATTR_NAME_1
	END

	-- If not found, then search attribute based on linked object level
	IF @TARGET_FIELD_1 IS NULL AND @PDM_OBJ_TITLE_2 IS NOT NULL
	BEGIN
		SELECT
				@SOURCE_FIELD_2 = OBJ_DEF_ATTR.LEGACY_ATTR,
				@TARGET_TABLE_2 = MIG_PROV_TABLE_MAPPING.TAR_TABLE_NAME,
				@TARGET_FIELD_2 = MIG_PROV_TABLE_FIELD_MAPPING.TAR_FIELD_NAME
		FROM	OBJ_DEF_ATTR
		LEFT JOIN MIG_PROV_TABLE_MAPPING
		  ON MIG_PROV_TABLE_MAPPING.SRC_BASE_TBL_NAME = @SOURCE_TABLE_2
		LEFT JOIN MIG_PROV_TABLE_FIELD_MAPPING
		  ON MIG_PROV_TABLE_FIELD_MAPPING.SRC_BASE_TBL_NAME = MIG_PROV_TABLE_MAPPING.SRC_BASE_TBL_NAME
		 AND MIG_PROV_TABLE_FIELD_MAPPING.SRC_BASE_FIELD_NAME = OBJ_DEF_ATTR.LEGACY_ATTR
		WHERE	OBJ_DEF_ATTR.OBJ_TITLE = @PDM_OBJ_TITLE_2
		 AND	OBJ_DEF_ATTR.LINK_TITLE = @PDM_LINK_TITLE_2
		 AND OBJ_DEF_ATTR.ATTR_NAME = @ATTR_NAME_2
	END

	IF @TARGET_FIELD_1 IS NOT NULL
		BEGIN
			--SELECT 
			--	@SOURCE_TABLE_1 AS "SOURCE_TABLE",
			--	@SOURCE_FIELD_1 AS "SOURCE_FIELD", 
			--	@TARGET_TABLE_1 AS "TARGET_TABLE", 
			--	@TARGET_FIELD_1 AS "TARGET_FIELD"

			UPDATE MIG_MAPPING_RULES
			SET
				NEW_PDM_OBJ_ID = @TARGET_TABLE_1,
				NEW_PDM_FLD_ID = @TARGET_FIELD_1
			WHERE ZZ_ID = @ZZ_ID
		END
	ELSE IF @TARGET_FIELD_2 IS NOT NULL
		BEGIN
			--SELECT
			--	@SOURCE_TABLE_2 AS "SOURCE_TABLE",
			--	@SOURCE_FIELD_2 AS "SOURCE_FIELD", 
			--	@TARGET_TABLE_2 AS "TARGET_TABLE", 
			--	@TARGET_FIELD_2 AS "TARGET_FIELD"

			UPDATE MIG_MAPPING_RULES
			SET
				NEW_PDM_OBJ_ID = @TARGET_TABLE_2,
				NEW_PDM_FLD_ID = @TARGET_FIELD_2
			WHERE ZZ_ID = @ZZ_ID
		END
	ELSE IF @MAPPING_RULES_PDM_OBJ_ID = 'ITEM_CLASSIFICATION' OR @MAPPING_RULES_PDM_OBJ_ID = 'DOC_CLASSIFICATION'
		BEGIN
			UPDATE MIG_MAPPING_RULES
			SET
				NEW_PDM_OBJ_ID = @MAPPING_RULES_PDM_OBJ_ID,
				NEW_PDM_FLD_ID = @MAPPING_RULES_PDM_FLD_ID
			WHERE ZZ_ID = @ZZ_ID
		END
	ELSE 
		BEGIN
			--SELECT 
			--	NULL AS "TARGET_TABLE", 
			--	NULL AS "TARGET_FIELD"

			UPDATE MIG_MAPPING_RULES
			SET
				NEW_PDM_OBJ_ID = NULL,
				NEW_PDM_FLD_ID = NULL
			WHERE ZZ_ID = @ZZ_ID
		END
END
