CREATE PROCEDURE MIG_SKIPPED_FILE_FRMTS_Sp
AS
BEGIN
	
	DELETE FROM MIG_SKIPPED_FILE_FRMTS

	; WITH CTE_PROJ_CONFIG AS (
		-- GET PROJECTS with PREF DOC_RELATED_FILE_FORMAT NOT SET and take value from '*' PROJECT 
		SELECT DISTINCT PROJ.PROJECT_ID, PC.PREF_NAME, PC.PREF_VALUE, PC.CLASS_ID, PC.ZZ_ID  
		FROM PROJ_CONFIG PC
		CROSS JOIN PROJECT PROJ
		WHERE PC.PROJECT_ID = '*' AND
		PC.PREF_NAME = 'DOC_RELATED_FILE_FORMAT' AND 
		-- Ignore projects with DOC_RELATED_FILE_FORMAT PREFERENCE is SET
		PROJ.PROJECT_ID NOT IN (	SELECT PROJECT_ID 
									FROM PROJ_CONFIG 
									WHERE PROJECT_ID <> '*' AND
										  PREF_NAME = 'DOC_RELATED_FILE_FORMAT'
								)
	
		UNION
	
		-- GET PROJECTS with PREF DOC_RELATED_FILE_FORMAT IS SET OTHER than '*' PROJECT 

		SELECT PC.PROJECT_ID, PC.PREF_NAME, PC.PREF_VALUE, PC.CLASS_ID, PC.ZZ_ID  
		FROM PROJ_CONFIG PC 
		WHERE PC.PROJECT_ID <> '*' AND
		PC.PREF_NAME = 'DOC_RELATED_FILE_FORMAT'


	)

	INSERT INTO MIG_SKIPPED_FILE_FRMTS

	SELECT PROJ.PROJECT_ID,
		   ZZ_UDCDTYPE.VALUE_ID,
		   ZZ_UDCDTYPE.SHORT_DESCR
	FROM   ZZ_UDCDTYPE
		   LEFT JOIN CTE_PROJ_CONFIG PC
				  ON  PC.PREF_NAME = 'DOC_RELATED_FILE_FORMAT'
					 AND PC.CLASS_ID = ZZ_UDCDTYPE.SHORT_DESCR
			INNER JOIN PROJECT PROJ
				  ON	PROJ.PROJECT_ID = PC.PROJECT_ID	 
	WHERE  ZZ_UDCDTYPE.TYPE_NAME = 'DSET_FORMAT'
		   AND ( PC.ZZ_ID IS NOT NULL
					   AND PC.PREF_VALUE = '0' ) 
	ORDER  BY ZZ_UDCDTYPE.VALUE_ID; 

END