ALTER PROC dbo.CM_PROCESS_DT_Create
@p_ID VARCHAR(15) = NULL,
@p_REQ_ID VARCHAR(50)=NULL,
@p_TLNAME VARCHAR(200)=NULL
AS
BEGIN

DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20), @ROLE VARCHAR(20), @CURRENTSTATE VARCHAR(15), @CONDITION_STATUS VARCHAR(1), @DESCRIBE NVARCHAR(MAX), @COUNTDVCM INT, @TEMPDVCM_ID INT
DECLARE @TEMPDVCM TABLE (
  ID INT NOT NULL IDENTITY(1,1),
  COST_ID VARCHAR(15),
  REQ_ID VARCHAR(15)
  --,RANGE_PROCESS VARCHAR(15)
) 
BEGIN TRANSACTION

SET @CURRENTSTATE = (SELECT crp.[STATUS] FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND  PROCESS_ID=CONVERT(INT, @p_ID))
SET @CONDITION_STATUS = (SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID))    
SET @ROLE = (SELECT ROLE FROM CM_PROCESS cp WHERE CONDITION_STATUS IN (SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND CONDITION_STATUS<>[STATUS] )

INSERT INTO @TEMPDVCM SELECT trsc.COST_ID, trsc.REQ_ID FROM TR_REQUEST_SHOP_COSTCENTER trsc  
                      WHERE trsc.REQ_ID = @p_REQ_ID 
                      AND trsc.COST_ID NOT IN (SELECT crp.DEPT_ID FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND crp.STATUS = 'G' AND crp.PROCESS_ID = 32) 

SET @TEMPDVCM_ID = 1
SET @COUNTDVCM = (SELECT COUNT(*) FROM @TEMPDVCM WHERE [@TEMPDVCM].REQ_ID = @p_REQ_ID GROUP BY [@TEMPDVCM].REQ_ID)	


  INSERT INTO [dbo].[PL_PROCESS]
           ([REQ_ID]
           ,[PROCESS_ID]
           ,[CHECKER_ID]
           ,[APPROVE_DT]
           ,[PROCESS_DESC]
           ,[NOTES]
           ,[AUTH_STATUS])
    SELECT @p_REQ_ID
	,ID
	,@p_TLNAME
	,GETDATE()
	,UPPER(LEFT(cp.DESCRIPTION,1))+LOWER(SUBSTRING(cp.DESCRIPTION,2,LEN(cp.DESCRIPTION))) --uppercase/capitalize the first letter vs just wanted to change it only for displaying and do not need the actual data in table to change
	,cp.NOTES
	,[STATUS] FROM CM_PROCESS cp WHERE ID=CONVERT(INT, @p_ID)

	DECLARE @TABLE VARCHAR(50)=NULL
	--cắt chuỗi lấy PREFIX để xác định Thêm mới, Xuất, Điều chuyển, Thu hồi, Thanh lý,...
		DECLARE @SYS_PREFIX VARCHAR(20) =(SELECT LEFT(Val,PATINDEX('%[^a-z]%', Val+'0')-1) from(
		  SELECT SUBSTRING(@p_REQ_ID, PATINDEX('%[a-z]%', @p_REQ_ID), LEN(@p_REQ_ID)) Val
		)x)
	SET @TABLE=(SELECT sp.ID FROM SYS_PREFIX sp WHERE sp.Prefix = @SYS_PREFIX)

  SELECT @BRANCH_ID=BRANCH_ID,@DEP_ID=DEP_ID FROM ASS_LIQ_REQUEST ALR WHERE ALR.LIQ_REQ_ID = @p_REQ_ID

	--UPDATE STATUS DONE CM_REQUEST_PROCESS
	UPDATE CM_REQUEST_PROCESS SET DONE=1 
	WHERE REQ_ID=@p_REQ_ID 
	AND PROCESS_ID=CONVERT(INT, @p_ID)
	AND(DEPT_ID=(SELECT DEP_ID FROM TL_USER WHERE TLNANME=@p_TLNAME) OR DEPT_ID IS NULL OR DEPT_ID='')

	DELETE CM_REQUEST_PROCESS
	WHERE (DONE<>1 OR DONE IS NULL)
	AND REQ_ID=@p_REQ_ID 
	AND PROCESS_ID<>CONVERT(INT, @p_ID)
	AND CONDITION_STATUS=(SELECT TOP 1 CONDITION_STATUS FROM CM_REQUEST_PROCESS WHERE PROCESS_ID=CONVERT(INT, @p_ID) AND REQ_ID=@p_REQ_ID)
	AND(DEPT_ID=(SELECT DEP_ID FROM TL_USER WHERE TLNANME=@p_TLNAME) OR DEPT_ID IS NULL OR DEPT_ID='')
  
   IF @ROLE LIKE '%QLTS'
    BEGIN
      SET @BRANCH_ID = 'DV0001'
     SET @DEP_ID = 'DEP000000000048'
    END
  
  IF(@CURRENTSTATE = 'E' AND NOT EXISTS (SELECT * FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID=@p_REQ_ID))
  BEGIN
    SET @p_ID = (SELECT cp.ID FROM CM_PROCESS cp WHERE cp.[STATUS] = 'G')
    SET @BRANCH_ID = 'DV0001' 
    SET @DEP_ID = 'DEP000000000048'
    SET @CONDITION_STATUS = 'E'
    SET @ROLE = 'QLTS'
  END

   IF @ROLE LIKE '%DVCM' --AND @COUNTDVCM = 1     
   BEGIN  
    SET @BRANCH_ID = 'DV0001' 
    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)--(SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)
   END

--   IF @ROLE LIKE 'DVCM' AND @COUNTDVCM > 1 AND NOT EXISTS (SELECT * FROM CM_REQUEST_PROCESS crp WHERE crp.REQ_ID = @p_REQ_ID AND crp.STATUS = 'F' AND crp.ROLE LIKE 'DVCM')
--   BEGIN
--    SET @BRANCH_ID = 'DV0001' 
--    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)
--   END

   IF(@ROLE LIKE 'GDDVCM')
   BEGIN
    SET @ROLE = 'GDDV'
    SET @BRANCH_ID = 'DV0001'
    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)--(SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)
   END 
      
	--CHECK STEP DVCM IN PROCESS
	IF(EXISTS(SELECT * FROM CM_PROCESS WHERE  ID=CONVERT(INT,@p_ID) AND [STATUS] IN (SELECT CONDITION_STATUS FROM CM_PROCESS WHERE RANGE_PROCESS='DEPT'))
	AND NOT EXISTS (SELECT * FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID=@p_REQ_ID))
	BEGIN
		--CHANGE ID TO NEXT STEP
		SET @p_ID=(SELECT TOP(1) ID FROM CM_PROCESS WHERE [ORDER]>(SELECT [ORDER] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND (RANGE_PROCESS='DEPT') ORDER BY [ORDER] DESC)
    SET @DEP_ID = (SELECT TRS.COST_ID FROM TR_REQUEST_SHOP_COSTCENTER TRS WHERE TRS.REQ_ID=@p_REQ_ID)
	END
  
  
  IF(@CURRENTSTATE = 'G' AND EXISTS(SELECT * FROM @TEMPDVCM WHERE COST_ID IS NOT NULL AND REQ_ID = @p_REQ_ID))  
  BEGIN 
    SET @p_ID = (SELECT cp.ID FROM CM_PROCESS cp WHERE cp.[STATUS] = 'E') 
    SET @BRANCH_ID = 'DV0001'
    SET @DEP_ID = (SELECT COST_ID FROM @TEMPDVCM WHERE REQ_ID = @p_REQ_ID AND ID = 1)
    SET @CONDITION_STATUS = 'G'
    SET @ROLE = 'DVCM'
  END 
	--INSERT INTO PL_REQUEST_PROCESS
	IF(
	NOT EXISTS(SELECT * FROM CM_REQUEST_PROCESS WHERE PROCESS_ID=CONVERT(INT,@p_ID) AND REQ_ID=@p_REQ_ID AND (DONE<>1 OR DONE IS NULL OR DONE ='') AND [ACTION]='APPROVE')
--	AND
--	NOT EXISTS(SELECT * FROM CM_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND [STATUS] IN(SELECT [STATUS] FROM CM_PROCESS WHERE CONDITION_STATUS=(SELECT  [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID)) AND CONDITION_STATUS<>[STATUS] ) )
  )
	BEGIN
  
		INSERT INTO [dbo].[CM_REQUEST_PROCESS]
			   ([REQ_ID]
			   ,PROCESS_KEY
			   ,[PROCESS_ID]
			   ,[STATUS]
			   ,[ROLE]
			   ,[BRANCH_ID]
         ,[DEPT_ID]
			   ,[DEP_ID]
			   ,[NOTES]
			   ,[ORDER]
			   ,[ACTION]
			   ,[FROM_STATUS]
			   ,[FROM]
			   ,[CONDITION_STATUS]
			   ,[DESCRIBE]
			   ,[NAME_ACTION]
			   ,[RANGE_PROCESS]
         ,[MARKER_ID]
         ,CHECKER_ID
         ,APPROVE_DT)
			   SELECT 
			   @p_REQ_ID
			   ,CP.PROCESS_KEY
			   ,CP.ID
			   ,CP.[STATUS]
			   ,@ROLE
			   ,@BRANCH_ID
         ,[@TEMPDVCM].COST_ID --TRSC.COST_ID--
			   ,@DEP_ID
			   ,CP.NOTES
			   ,CP.[ORDER]
			   ,[ACTION]
			   ,FROM_STATUS
			   ,[STATUS]
			   ,@CONDITION_STATUS
			   ,DESCRIBE
			   ,NAME_ACTION
			   ,RANGE_PROCESS
         ,alr.MAKER_ID
         ,alr.CHECKER_ID
         ,CONVERT(DATETIME,GETDATE(),103)
			   FROM CM_PROCESS CP
         LEFT JOIN ASS_LIQ_REQUEST alr ON alr.LIQ_REQ_ID = @p_REQ_ID
         --LEFT JOIN TR_REQUEST_SHOP_COSTCENTER TRSC ON TRSC.REQ_ID=@p_REQ_ID AND CP.RANGE_PROCESS='DEPT'
         LEFT JOIN @TEMPDVCM ON [@TEMPDVCM].REQ_ID = @p_REQ_ID AND CP.RANGE_PROCESS='DEPT' AND [@TEMPDVCM].ID = 1--AND TRSC.COST_ID = [@TEMPDVCM].COST_ID   
			   WHERE CONDITION_STATUS IN (SELECT [STATUS] FROM CM_PROCESS WHERE ID=CONVERT(INT, @p_ID))
			   AND CONDITION_STATUS <> [STATUS] 
         --IF @@ERROR <> 0 GOTO ABORT
  
	END
   
	-- END INSERT PL_REQUEST_PROCESS

COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_ID  REQ_ID, '' ErrorDesc, @p_ID ID, @BRANCH_ID BRANCH_ID, @DEP_ID DEP_ID, @ROLE [ROLE], @CURRENTSTATE CURRENTSTATE, @COUNTDVCM COUNTDVCM, @TEMPDVCM_ID TEMPDVCM_ID  
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_ID, '' ErrorDesc
		RETURN '-1'
End
END