-- EXEC MIG_DOC_Sp
CREATE OR ALTER PROCEDURE MIG_DOC_Sp
AS
BEGIN
										
	--	pdlcst (DOMAIN) LIFE CYCLE STATUS		|	-- UNCH_STATUS (10.2.4)		pdstat (DOMAIN)
	-- =====================================	|	-- ==========================================
	--5			Created							|	--0	 	UNDEFINED				Draft
	--10		Editing							|	--7	 	RELEASED				Released
	--15		Pending Approval				|	--8	 	UNDER_CHANGE			Under Change
	--20		Limited Available				|	--10	UNDER_TOP				
	--25		General Available				|	--===========================================
	--30		Superseded						|
	--35		Withdrawn						|
	--40		Not Implemented					|
	-- =====================================	|

	-- SELECT COUNT(*) FROM DOC -- 537370

	TRUNCATE TABLE MIG_DOC

	--INSERT INTO MIG_DOC
	--SELECT	DISTINCT	DOC.ZZ_ID, 
	--					--DOC.DOC_KEY, 
	--					--DOC.REVISION,
	--					--DOC.STATUS,
	--					CASE
	--						-- IF OBSOLETE = 1 then Withdrawn
	--						WHEN DOC.OBSOLETE = 1 THEN 35  -- Withdrawn
	--						-- Draft / UNDEFINED 
	--						-- If Status is "Draft" with "BP linked" then LCS should be "Pending Approval"
	--						WHEN DOC.STATUS = 0 AND EOD.DOC_KEY IS NOT NULL AND EO.STATUS IN ('1', '4')	THEN 15  -- "Pending Approval"
	--						-- If Status is "Draft" and "No Linked File" then LCS should be "Created"
	--						WHEN DOC.STATUS = 0 AND FIL.DOC_KEY IS NULL THEN 5 -- "Created"
	--						-- If Status is "Draft" with "Linked File" then LCS should be "Editing"
	--						WHEN DOC.STATUS = 0 AND FIL.DOC_KEY IS NOT NULL	THEN 10 -- "Editing"
	--						-- Released and IsLatest -- Limited Available
	--						WHEN DOC.STATUS = 7 AND SEQ.DOC_KEY IS NOT NULL	THEN 20 -- Limited Available
	--						-- Released and NOT IsLatest -- Superseded
	--						WHEN DOC.STATUS = 7 AND SEQ.DOC_KEY IS NULL	THEN 30   -- Superseded
	--						-- UNDER_CHANGE  -- Limited Available
	--						WHEN DOC.STATUS = 8 THEN 20  -- Limited Available
						
	--						ELSE 5 -- "Created"
	--				   END AS LIFE_CYCLE_STATUS
	--FROM DOC 
	--LEFT JOIN MIG_DOC_SEQ_INFO SEQ
	--ON DOC.ZZ_ID = SEQ.ZZ_ID 
	--LEFT JOIN DOC_DATASET FIL 
	--ON DOC.DOC_KEY = FIL.DOC_KEY AND DOC.REVISION = FIL.REVISION 
	--LEFT JOIN EO_DOC EOD
	--ON DOC.DOC_KEY = EOD.DOC_KEY AND DOC.REVISION = EOD.REVISION 
	--LEFT JOIN EO 
	--ON EO.EO_ID = EOD.EO_ID
	----WHERE FIL.DOC_KEY IS NULL AND EOD.DOC_KEY IS NULL


	INSERT INTO MIG_DOC
	SELECT	DISTINCT	
		DOC.ZZ_ID, 
		--DOC.DOC_KEY, 
		--DOC.REVISION,
		--DOC.STATUS,
		CASE
			-- IF OBSOLETE = 1 then Withdrawn
			WHEN DOC.OBSOLETE = 1 THEN 35  -- Withdrawn
		
			-- If Status is "Draft / UNDEFINED" with "BP linked" then LCS should be "Pending Approval"
			WHEN DOC.STATUS = 0 AND D_EO.ZZ_ID IS NOT NULL THEN 15  -- "Pending Approval"
		
			-- If Status is "Draft / UNDEFINED" with "Linked File" then LCS should be "Editing"
			WHEN DOC.STATUS = 0 AND D_FIL.ZZ_ID IS NOT NULL	THEN 10 -- "Editing"
		
			-- If Status is "Draft / UNDEFINED" and "No Linked File" then LCS should be "Created"
			WHEN DOC.STATUS = 0 THEN 5 -- "Created"
		
			-- Released and IsLatest -- Limited Available
			WHEN DOC.STATUS = 7 AND SEQ.DOC_KEY IS NOT NULL	THEN 20 -- Limited Available
		
			-- Released and NOT IsLatest -- Superseded
			WHEN DOC.STATUS = 7 AND SEQ.DOC_KEY IS NULL	THEN 30   -- Superseded
		
			-- UNDER_CHANGE  -- Limited Available
			WHEN DOC.STATUS = 8 THEN 20  -- Limited Available
		
			-- Default -- Created
			ELSE 5 -- "Created"
		END AS LIFE_CYCLE_STATUS
	FROM DOC 
	LEFT JOIN MIG_DOC_SEQ_INFO SEQ
	  ON DOC.ZZ_ID = SEQ.ZZ_ID 
	 AND SEQ.IS_LATEST = '1'
	LEFT JOIN
	(
		SELECT DISTINCT 
			 D.ZZ_ID
		FROM DOC D
		JOIN DOC_DATASET D_DS
		  ON D_DS.DOC_KEY = D.DOC_KEY 
		 AND D_DS.REVISION = D.REVISION 
	) D_FIL
	ON D_FIL.ZZ_ID = DOC.ZZ_ID
	LEFT JOIN 
	(
		SELECT DISTINCT
			D.ZZ_ID
		FROM DOC D
		JOIN EO_DOC EOD
		  ON EOD.DOC_KEY = D.DOC_KEY 
		 AND EOD.REVISION = D.REVISION 
		JOIN EO 
		  ON EO.EO_ID = EOD.EO_ID
		  AND EO.STATUS IN ('1', '4')
	) D_EO
	ON D_EO.ZZ_ID = DOC.ZZ_ID

END
