-- EXEC MIG_MIG_MASKS_SEQ_CACHE_Sp
CREATE OR ALTER PROCEDURE MIG_MIG_MASKS_SEQ_CACHE_Sp 
AS
BEGIN
	DELETE FROM MIG_MASKS_SEQ_CACHE

	CREATE TABLE #CTE_MASK_CONST_VALUE      
	(      
		mask nvarchar(15) collate database_default,      
		CONST_VALUE nvarchar(100) collate database_default
	);

	INSERT INTO #CTE_MASK_CONST_VALUE(mask, CONST_VALUE)
	SELECT  
		mask, 
		MIN(SEG.sval) CONST_VALUE FROM MIG_MASKS_SEGMENTS SEG 
	GROUP BY mask, sgty, PRIMARY_SIGN, sgno 
	HAVING SEG.sgty = 20 AND PRIMARY_SIGN = 1 AND MAX(sgno) = (sgno) --AND mask = 'DOC-1'

	INSERT INTO MIG_MASKS_SEQ_CACHE
	SELECT MASK_ID, MAP.LN_TABLE, MAP.LN_FIELD,  --CLASS_NAME, KEY_NAME, 
			 pfix, -- USE ONLY CONSTANT from MASK
			 isfu, -- IF CURRENT_KEY = MAX-MAX_MASK then 'YES' ELSE 'NO'
			 seqn,
			'' nrgr,
			''seri,
			UPDATED_ON
	FROM 
	(
		SELECT DISTINCT 
			MASK_ID, 
			CLASS_NAME, 
			KEY_NAME, 
			(
				SELECT TOP 1 SEG.sval 
				FROM MIG_MASKS_SEGMENTS SEG 
				WHERE SEG.mask = MSK.MASK_ID 
				AND sgty = 20 
				--AND SEG.sgno = 10) 
			) AS pfix, -- USE ONLY CONSTANT from MASK
			CASE 
				WHEN MSK.CURRENT_KEY = MAX_MASK THEN
					1 
				ELSE 
					2 
			END AS isfu, -- IF CURRENT_KEY = MAX-MAX_MASK then 'YES' ELSE 'NO'
			--(CURRENT_KEY + 1 ) as seqn, -- WITH OUT CONSTANT eg: DOC-00000350 as 350 OR 00000350
			CASE 
				WHEN ISNUMERIC(REPLACE( MSK.CURRENT_KEY, CNST.CONST_VALUE, '')) = 1 THEN 
					(CAST( REPLACE( MSK.CURRENT_KEY, CNST.CONST_VALUE, '')  as INT ) + 1) 
				ELSE 
					'' 
			END AS seqn,
			'' nrgr,
			''seri,
			UPDATED_ON
		FROM ZZ_MASK MSK 
		LEFT JOIN #CTE_MASK_CONST_VALUE CNST
		ON CNST.mask = MSK.MASK_ID
		WHERE PRIMARY_SIGN = 1  -- Only for KEYS NOT for REVISION fields
	) AS SEQ
	JOIN MIG_MASK_FLD_MAPPING MAP
	  ON MAP.CLASS_NAME = SEQ.CLASS_NAME 
	 AND MAP.KEY_NAME = SEQ.KEY_NAME
	WHERE pfix IS NOT NULL
END
--EXEC MIG_MIG_MASKS_SEQ_CACHE_Sp
--SELECT * FROM MIG_MASKS_SEQ_CACHE
