ALTER PROCEDURE [dbo].[TR_REQUEST_PROCESS_App]
    @p_REQ_ID VARCHAR(15) = NULL,
	@p_AUTH_STATUS VARCHAR(1) = NULL,
	@p_CHECKER_ID varchar(15)  = NULL,
	@p_APPROVE_DT DATETIME = NULL,
	@p_ROLE_LOGIN VARCHAR(50) = NULL,
	@p_BRANCH_LOGIN VARCHAR(15),
	@p_PROCESS_DESC NVARCHAR(MAX),
	@p_XMLDATA XML
	
AS

--SET @p_APPROVE_DT= CAST(@p_APPROVE_DT AS DATE)
	--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
  IF ( NOT EXISTS ( SELECT * FROM TR_REQUEST_DOC WHERE  REQ_ID = @p_REQ_ID))
	SET @ERRORSYS = 'REQ-00002'
IF @ERRORSYS <> '' 
BEGIN
   ROLLBACK TRANSACTION
	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '0'
END 

BEGIN TRANSACTION
-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC
	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
	--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))
	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 'REQ-00001' Result, '' ROLE_NOTIFI, N'File đinh kèm bắt buộc đọc' ErrorDesc 
			RETURN '0'
 END




	 DECLARE
	@Result VARCHAR(5),
	@PROCESS_CURR VARCHAR(10),
	@STEP_CURR INT,
	@STEP_NEXT INT,
	@PROCESS_NEXT VARCHAR(10),
	@ROLE_USER_NOTIFI VARCHAR(50),
	@DEP_ID VARCHAR(15),
	@IS_LEAF VARCHAR(1),
	@NOTES NVARCHAR(50)

		SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_CHECKER_ID)
		SET @PROCESS_CURR= (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')
		SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
		SET @IS_LEAF=(SELECT TOP 1 IS_LEAF FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
		
		

		SET @NOTES =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   
		--SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@p_ROLE_LOGIN)	



		UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='P',NOTES=@NOTES+N' đã phê duyệt',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND ROLE_USER=@p_ROLE_LOGIN
			
		
		INSERT INTO dbo.PL_PROCESS
				(
					REQ_ID,
					PROCESS_ID,
					CHECKER_ID,
					APPROVE_DT,
					PROCESS_DESC,NOTES
				)
				VALUES
				(   @p_REQ_ID,        -- REQ_ID - varchar(15)
					@PROCESS_CURR,        -- PROCESS_ID - varchar(10)
					@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
					GETDATE(), -- APPROVE_DT - datetime
					@p_PROCESS_DESC,
					@NOTES+N' đã phê duyệt'     -- PROCESS_DESC - nvarchar(1000)
				)
		IF(EXISTS(SELECT PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@PROCESS_CURR AND [STATUS] ='P'))
		BEGIN	
				UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PARENT_PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID
				UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID

		END	
	
	IF(@PROCESS_NEXT='APPROVE')	
	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)


		IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND REQ_PARENT_ID IS NOT NULL AND REQ_PARENT_ID <>''))
		BEGIN
			DECLARE @PARENT_ID VARCHAR(20)
			SET @PARENT_ID =(SELECT REQ_PARENT_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
			IF(EXISTS(SELECT PO_ID FROM dbo.TR_PO_MASTER WHERE REQ_DOC_ID=@PARENT_ID AND AUTH_STATUS='A'))
			BEGIN
				DECLARE lstPO  CURSOR FOR
				SELECT PO_ID FROM dbo.TR_PO_MASTER WHERE REQ_DOC_ID=@PARENT_ID
				OPEN lstPO

				DECLARE @PO_ID VARCHAR(20),@TOTAL_ADD DECIMAL(18,2)

				FETCH NEXT FROM lstPO INTO @PO_ID
				WHILE @@FETCH_STATUS =0
				BEGIN
					
					SET @TOTAL_ADD = (SELECT SUM(TOTAL_AMT) AS TOTAL FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND HANGHOA_ID IN (SELECT GOODS_ID  FROM dbo.TR_PO_DETAIL WHERE PO_ID=@PO_ID ))
					
					UPDATE dbo.TR_PO_MASTER SET AMT_ADD =@TOTAL_ADD WHERE PO_ID=@PO_ID
						
						

					DECLARE @TOTAL_PERCENT INT,@TOTAL_AMT_REMAIN DECIMAL(18,2)


					SELECT @TOTAL_PERCENT= SUM([PERCENT]) ,@TOTAL_AMT_REMAIN=SUM(ISNULL(AMOUNT,0)) FROM TR_PO_PAYMENT WHERE PO_ID=@PO_ID AND NOT EXISTS (SELECT PAY_ID  FROM dbo.TR_REQ_PAY_SCHEDULE WHERE TR_REQ_PAY_SCHEDULE.PAY_ID =TR_PO_PAYMENT.PAY_ID AND PO_ID =@PO_ID AND AUTH_STATUS_KT='A' AND (TRN_TYPE='PAY' OR TRN_TYPE='ADV') 
																									GROUP BY TR_REQ_PAY_SCHEDULE.PAY_ID 
																									HAVING SUM(ISNULL(AMT_PAY_REAL,0) + ISNULL(AMT_ADVANCE,0)) >= TR_PO_PAYMENT.AMOUNT)
					IF(EXISTS(SELECT PAY_ID FROM TR_PO_PAYMENT WHERE PO_ID=@PO_ID AND NOT EXISTS (SELECT PAY_ID  FROM dbo.TR_REQ_PAY_SCHEDULE WHERE TR_REQ_PAY_SCHEDULE.PAY_ID =TR_PO_PAYMENT.PAY_ID AND PO_ID =@PO_ID AND AUTH_STATUS_KT='A' AND (TRN_TYPE='PAY' OR TRN_TYPE='ADV') 
																									GROUP BY TR_REQ_PAY_SCHEDULE.PAY_ID 
																									HAVING SUM(ISNULL(AMT_PAY_REAL,0) + ISNULL(AMT_ADVANCE,0)) >= TR_PO_PAYMENT.AMOUNT)))

					BEGIN
					UPDATE dbo.TR_PO_PAYMENT SET AMOUNT = ((@TOTAL_AMT_REMAIN + @TOTAL_ADD)/@TOTAL_PERCENT ) * [PERCENT]  WHERE PO_ID=@PO_ID AND NOT EXISTS (SELECT PAY_ID  FROM dbo.TR_REQ_PAY_SCHEDULE WHERE TR_REQ_PAY_SCHEDULE.PAY_ID =TR_PO_PAYMENT.PAY_ID AND PO_ID =@PO_ID AND AUTH_STATUS_KT='A' AND (TRN_TYPE='PAY' OR TRN_TYPE='ADV') 
																									GROUP BY TR_REQ_PAY_SCHEDULE.PAY_ID 
																									HAVING SUM(ISNULL(AMT_PAY_REAL,0) + ISNULL(AMT_ADVANCE,0)) >= TR_PO_PAYMENT.AMOUNT)
					END
					FETCH NEXT FROM lstPO INTO @PO_ID
				END

			END


		END
		ELSE 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)
						
		SET @Result='0'
	END
	ELSE
		SET @Result='1'
	


	IF @@Error <> 0 GOTO ABORT
			
COMMIT TRANSACTION
SELECT @Result as Result , @ROLE_USER_NOTIFI AS  ROLE_NOTIFI, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
	
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ROLE_NOTIFI ,'' ErrorDesc
		RETURN '-1'
End