ALTER PROCEDURE [dbo].[TR_REQ_PROCESS_CHILD_App]
@p_REQ_ID VARCHAR(20),
@p_PROCESS_ID VARCHAR(20),
@p_TLNAME VARCHAR(20),
@p_MAKER_ID VARCHAR(20),
@p_TYPE_JOB VARCHAR(20),
@p_PROCESS_DES NVARCHAR(500),
@p_REF_ID INT,
@p_XMLDATA XML
AS
BEGIN TRANSACTION
-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC

--IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID=@p_PROCESS_ID))
--BEGIN
--	SELECT 1 as Result, '' ErrorDesc
--	RETURN 0
--END


IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))
OR(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='R')))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc
		RETURN -1
END
Declare @hdoc INT
	EXEC sp_xml_preparedocument @hdoc Output,@p_XMLDATA
	DECLARE @lstFILE TABLE(
	ATTACH_ID  VARCHAR(20),
	IS_READ	BIT 
	)
	INSERT INTO @lstFILE
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ATTACH_FILE',2)
	WITH 
	(
		ATTACH_ID  VARCHAR(20),
		IS_READ	BIT  
	)

IF(EXISTS(SELECT TR_REQUEST_DOC_FILE_ID FROM dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REQ_ID AND IS_VIEW=1
 AND EXISTS(SELECT ATTACH_ID FROM @lstFILE WHERE [@lstFILE].ATTACH_ID=TR_REQUEST_DOC_FILE.ATTACH_ID AND IS_READ=0)))
 BEGIN
		ROLLBACK TRANSACTION
			SELECT -1 Result, N'File đinh kèm bắt buộc đọc' ErrorDesc 
			RETURN 0
 END
DECLARE @LEVEL INT,	@TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)
			
		UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='P'
		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB

		SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB 
		FROM dbo.PL_REQUEST_PROCESS_CHILD 
		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB
		ORDER BY LEVEL_JOB DESC),0)

		UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C'
		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND LEVEL_JOB=(@LEVEL-1)
		
			SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@p_TYPE_JOB)
			SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME )
			
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,NOTES
				)
				VALUES
				(   @p_REQ_ID,        -- REQ_ID - varchar(15)
					@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)
					@p_MAKER_ID,        -- CHECKER_ID - varchar(15)
					GETDATE() , -- APPROVE_DT - datetime
					@p_PROCESS_DES ,
					@TYPE_JOB_NAME+ N' đã phê duyệt'       -- PROCESS_DESC - nvarchar(1000)
				)
		

		IF(NOT EXISTS (SELECT * FROM  dbo.PL_REQUEST_PROCESS_CHILD 
		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND STATUS_JOB <> 'P'))
		BEGIN
				IF(@p_PROCESS_ID='DMMS')
				BEGIN
				DECLARE @PROCESS_PARENT VARCHAR(20)
				SET @PROCESS_PARENT='DMMS'

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE(),NOTES=N'Đầu mối mua sắm đã phê duyệt' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_PROCESS_ID
		
				DECLARE @LIMIT_VALUE_KT DECIMAL(18,0),@ROLE_KT VARCHAR(20),@DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(200),@TOTAL_AMT_REQ DECIMAL(18,0)

				SET @LIMIT_VALUE_KT = (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
				SET @ROLE_KT=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
				
				SET @DVDM_KT=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
				
				SET @TOTAL_AMT_REQ =(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT 
				WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' OR NOTES ='CDT'))
				IF(@TOTAL_AMT_REQ > @LIMIT_VALUE_KT AND 1 > 2)
				BEGIN
					DECLARE @l_REQ_COST_ID VARCHAR(15)
					EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
					INSERT dbo.TR_REQUEST_COSTCENTER
					(
					    REQ_COST_ID,
					    COST_ID,
					    REQ_ID,
					    NOTES,
					    AUTH_STATUS,
					    MAKER_ID,
					    CREATE_DT,
					    CHECKER_ID,
					    APPROVE_DT
					)
					VALUES
					(   @l_REQ_COST_ID,        -- REQ_COST_ID - varchar(15)
						@DVDM_KT,        -- COST_ID - varchar(15)			    
						@p_REQ_ID,        -- REQ_ID - varchar(15)
					    N'',       -- NOTES - nvarchar(500)
					    'U',        -- AUTH_STATUS - varchar(1)
					    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
					    GETDATE(), -- CREATE_DT - datetime
					    '',        -- CHECKER_ID - varchar(15)
					   NULL -- APPROVE_DT - datetime
					    )
				END
				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID) AND EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' )))
					BEGIN
					DECLARE @COST_ID VARCHAR(20)
					DECLARE lstCostCenter CURSOR FOR
					SELECT COST_ID  FROM dbo.TR_REQUEST_COSTCENTER
					WHERE REQ_ID=@p_REQ_ID
					 OPEN lstCostCenter
		 			FETCH NEXT FROM lstCostCenter INTO @COST_ID
					WHILE @@FETCH_STATUS = 0 
					BEGIN 

					IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND (ROLE_USER='GDDV' OR ROLE_USER IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') )AND ( DVDM_ID=@COST_ID OR @COST_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
				LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
				WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
				BEGIN

					IF(@COST_ID=@DVDM_KT AND 1> 2)
					BEGIN
						INSERT INTO dbo.PL_REQUEST_PROCESS
						(
							REQ_ID,
							PROCESS_ID,
							STATUS,
							ROLE_USER,
							BRANCH_ID,
							CHECKER_ID,
							APPROVE_DT,
							PARENT_PROCESS_ID,
							IS_LEAF,
							COST_ID,
							DVDM_ID,
							NOTES,
							IS_HAS_CHILD
						)
						VALUES
						(   @p_REQ_ID,        -- REQ_ID - varchar(15)
							'DVCM',        -- PROCESS_ID - varchar(10)
							'U',        -- STATUS - varchar(5)
							'KSV',        -- ROLE_USER - varchar(50)
							'',        -- BRANCH_ID - varchar(15)
							'',        -- CHECKER_ID - varchar(15)
							NULL, -- APPROVE_DT - datetime
						   @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							'N',        -- IS_LEAF - varchar(1)
							'',        -- COST_ID - varchar(15)
							@COST_ID ,
							N'Chờ đơn vị chuyên môn xác nhận' ,
							1       -- DVDM_ID - varchar(15)
						 )
					END
					ELSE
					BEGIN
				    INSERT INTO dbo.PL_REQUEST_PROCESS
				    (
				        REQ_ID,
				        PROCESS_ID,
				        STATUS,
				        ROLE_USER,
				        BRANCH_ID,
				        CHECKER_ID,
				        APPROVE_DT,
				        PARENT_PROCESS_ID,
				        IS_LEAF,
				        COST_ID,
				        DVDM_ID,
						NOTES,
						IS_HAS_CHILD
				    )
				    VALUES
				    (   @p_REQ_ID,        -- REQ_ID - varchar(15)
				        'DVCM',        -- PROCESS_ID - varchar(10)
				        'U',        -- STATUS - varchar(5)
				        'GDDV',        -- ROLE_USER - varchar(50)
				        '',        -- BRANCH_ID - varchar(15)
				        '',        -- CHECKER_ID - varchar(15)
						NULL, -- APPROVE_DT - datetime
				       @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
				        'N',        -- IS_LEAF - varchar(1)
				        '',        -- COST_ID - varchar(15)
				        @COST_ID ,
						N'Chờ đơn vị chuyên môn xác nhận' ,
						1       -- DVDM_ID - varchar(15)
				     )
					 END
				END
					FETCH NEXT FROM lstCostCenter INTO @COST_ID
					END
					CLOSE lstCostCenter
					DEALLOCATE lstCostCenter
					
				END
				DECLARE @TOTAL_AMT DECIMAL(18,2),@ROLE_PDTH VARCHAR(20)	,@LIMIT_VALUE DECIMAL(18,2)	,@IS_NEXT BIT,@PROCESS_ID  VARCHAR(5),@ROLE_ID VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTES NVARCHAR(50),@DVDM_NAME NVARCHAR(200)

				


				IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
					SET @PROCESS_PARENT='DVCM'
				
				SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))
				--SET @IS_NEXT=1
				IF(@IS_NEXT =1 AND EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' ))) 
				BEGIN
				SET @DVDM_ID=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')
				SET @ROLE_ID=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')
				SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
				SET @DVDM_NAME= (SELECT DVDM_NAME FROM dbo.CM_DVDM WHERE DVDM_ID=@DVDM_ID)
				SET @NOTES = @NOTES + ' ' + @DVDM_NAME

				INSERT INTO dbo.PL_REQUEST_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    STATUS,
					    ROLE_USER,
					    BRANCH_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PARENT_PROCESS_ID,
					    IS_LEAF,
					    COST_ID,
					    DVDM_ID,
					    NOTES,
					    IS_HAS_CHILD
					)
					VALUES
					(  @p_REQ_ID,        -- REQ_ID - varchar(15)
					   'GDK_PYC',        -- PROCESS_ID - varchar(10)
					    'U',        -- STATUS - varchar(5)
					    @ROLE_ID,        -- ROLE_USER - varchar(50)
					    '',        -- BRANCH_ID - varchar(15)
					    '',        -- CHECKER_ID - varchar(15)
					    NULL, -- APPROVE_DT - datetime
					    @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
					    '',        -- IS_LEAF - varchar(1)
					    '',        -- COST_ID - varchar(15)
					    @DVDM_ID,        -- DVDM_ID - varchar(15)
					    N'Chờ ' +@NOTES+N' phê duyệt',       -- NOTES - nvarchar(500)
					    0       -- IS_HAS_CHILD - bit
					    )
					SET @PROCESS_PARENT= 'GDK_PYC'

				
                END





				INSERT INTO dbo.PL_REQUEST_PROCESS
				(
				    REQ_ID,
				    PROCESS_ID,
				    STATUS,
				    ROLE_USER,
				    BRANCH_ID,
				    CHECKER_ID,
				    APPROVE_DT,
				    PARENT_PROCESS_ID,
				    IS_LEAF,
				    COST_ID,
				    DVDM_ID,
				    NOTES,
				    IS_HAS_CHILD
				)
				VALUES
				(   @p_REQ_ID,        -- REQ_ID - varchar(15)
				    'APPROVE',        -- PROCESS_ID - varchar(10)
				    'U',        -- STATUS - varchar(5)
				    '',        -- ROLE_USER - varchar(50)
				    '',        -- BRANCH_ID - varchar(15)
				    '',        -- CHECKER_ID - varchar(15)
				    NULL, -- APPROVE_DT - datetime
				    @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
				    'N',        -- IS_LEAF - varchar(1)
				    '',        -- COST_ID - varchar(15)
				    '',        -- DVDM_ID - varchar(15)
				    N'Hoàn tất',       -- NOTES - nvarchar(500)
				    NULL       -- IS_HAS_CHILD - bit
				    )
				DECLARE @PROCESS_NEXT_ID VARCHAR(10)
				SET @PROCESS_NEXT_ID = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)

				
				
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID
				UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT_ID WHERE REQ_ID=@p_REQ_ID
				
	END
				ELSE IF(@p_PROCESS_ID='DVCM')
				BEGIN

						DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@PROCESS_NEXT VARCHAR(10)

							SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
							SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
							SET @ROLE_ID=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
							-- LUCTV 07082020 KIEM TRA NEU ROLE KHAC ROLE GDDV THI PHAI CHUYEN ROLE THANH GDDV
							--IF(@ROLE_ID ='KTT')
							--BEGIN
							--	SET @ROLE_ID ='GDDV'
							--END
							SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)
						

						UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='A',APPROVE_DT=GETDATE(),CHECKER_ID=@p_MAKER_ID WHERE REQ_ID=@p_REQ_ID AND COST_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN
																																	dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)
						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,
						APPROVE_DT=GETDATE() ,NOTES=N'Đơn vị chuyên môn xác nhận' 
						WHERE REQ_ID=@p_REQ_ID AND( ROLE_USER=@ROLE_ID OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))AND DVDM_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN
																																	dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
																																	WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)
						
						IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_REQ_ID AND STATUS='C'))
						BEGIN
						
										UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID=@PROCESS_NEXT AND REQ_ID=@p_REQ_ID
										UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
						
						END
				END

			--IF(@PROCESS_NEXT='APPROVE')
			IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE PROCESS_ID ='APPROVE' AND REQ_ID =@p_REQ_ID))
			BEGIN
				DECLARE @TempTB TABLE
				(
					TOTAL_AMT DECIMAL(18,2),
					TRADE_ID VARCHAR(20),
					PLAN_ID VARCHAR(20)
				)
				INSERT INTO @TempTB			
				SELECT SUM(DT.TOTAL_AMT) TOTAL_AMT,PLDT.TRADE_ID,PLDT.PLAN_ID FROM dbo.TR_REQUEST_DOC_DT DT 
				LEFT JOIN dbo.PL_REQUEST_DOC_DT PLDT ON DT.PL_REQDT_ID=PLDT.REQDT_ID
				WHERE TRAN_TYPE_ID  IN (SELECT TRAN_TYPE_ID FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') AND DT.REQ_DOC_ID=@p_REQ_ID
				GROUP BY	PLDT.TRADE_ID,PLDT.PLAN_ID
				UPDATE dbo.PL_TRADEDETAIL SET AMT_EXE =AMT_EXE + (SELECT TOTAL_AMT FROM @TempTB WHERE [@TempTB].TRADE_ID=PL_TRADEDETAIL.TRADE_ID AND PL_TRADEDETAIL.PLAN_ID=[@TempTB].PLAN_ID) WHERE PL_TRADEDETAIL.TRADE_ID IN (SELECT TRADE_ID FROM @TempTB)

				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND  TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK')))
					EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @p_REQ_ID -- varchar(15)
				
			END
		END
				
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT 0 as Result, '' ErrorDesc
RETURN 0
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Result, '' ErrorDesc
		RETURN -1
End