DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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