ALTER PROCEDURE [dbo].[TR_PO_MASTER_App]
@P_PO_ID VARCHAR(15),
@P_AUTH_STATUS VARCHAR(1),
@P_CHECKER_ID VARCHAR(12),
@P_APPROVE_DT VARCHAR(20)

AS
--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
  IF ( NOT EXISTS ( SELECT * FROM TR_PO_MASTER WHERE  PO_ID = @P_PO_ID))
	SET @ERRORSYS = 'PO-00001'
IF @ERRORSYS <> '' 
BEGIN
	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '0'
END 

BEGIN TRANSACTION
	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID AND AUTH_STATUS ='R'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'PO đang bị trả về! Vui lòng đợi nhân viên xử lý và gửi phê duyệt lại' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID AND AUTH_STATUS ='E'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'PO đang trong tình trạng lưu nháp! Vui lòng đợi nhân viên xử lý và gửi phê duyệt lại' ErrorDesc
			RETURN '-1'
	END
	UPDATE TR_PO_MASTER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	WHERE PO_ID = @P_PO_ID
	IF @@Error <> 0 GOTO ABORT
	DECLARE @L_TRADE_ID VARCHAR(15),
	 @L_QUANTITY INT,
	 @L_PRICE DECIMAL(18,0), @CONTRACT_DT VARCHAR(15)
	DECLARE @L_PLAN_ID VARCHAR(15)
	
	DECLARE @l_SUMQUANTITY DECIMAL(10,0), @l_SUMAMT DECIMAL(10,0)
	DECLARE @lst_PLANID TABLE(PLAN_ID VARCHAR(15))

	DECLARE TRADE_CURSOR CURSOR FOR
	SELECT A.TRADE_ID,A.PLAN_ID,A.QUANTITY,A.PRICE, A.CONTRACT_DT
	FROM TR_PO_DETAIL A WHERE A.PO_ID = @P_PO_ID

	OPEN TRADE_CURSOR
	PRINT 'DETAIL'
	FETCH NEXT FROM TRADE_CURSOR INTO @L_TRADE_ID,@L_PLAN_ID,@L_QUANTITY,@L_PRICE,@CONTRACT_DT
	WHILE @@FETCH_STATUS = 0
	BEGIN

		IF @L_TRADE_ID IS NOT NULL AND LEN(@L_TRADE_ID)  <>  0
		BEGIN
			PRINT 'UPDATE_TRADE'
			--DAO THEM DIEU KIEN RECORD STATUS CHO TH HUY PO
			SELECT @l_SUMQUANTITY = SUM(ISNULL(QUANTITY,0))
			FROM TR_PO_DETAIL WHERE PLAN_ID = @L_PLAN_ID AND TRADE_ID = @L_TRADE_ID AND RECORD_STATUS='1'
			--UPDATE PL_TRADEDETAIL SET QUANTITY_EXE = (QUANTITY_EXE + CONVERT(INT,@L_QUANTITY)) WHERE TRADE_ID = @L_TRADE_ID
			--UPDATE PL_TRADEDETAIL SET QUANTITY_EXE = @l_SUMQUANTITY	WHERE TRADE_ID = @L_TRADE_ID
		END

		--CAP NHAT SO LUONG DA MUA TRONG HOP DONG THIEUVQ 14092016
		IF @CONTRACT_DT <> '' AND @CONTRACT_DT IS NOT NULL
		BEGIN
			UPDATE TR_CONTRACT_DT SET QUANTITY_USE = (SELECT SUM(A.QUANTITY) FROM TR_PO_DETAIL A WHERE A.CONTRACT_DT = @CONTRACT_DT AND RECORD_STATUS='1')
			WHERE CD_ID = @CONTRACT_DT
		END

		--LUU LAI DANH SACH CAC KE HOACH DUOC GOI TRONG PO
		IF NOT EXISTS(SELECT * FROM @lst_PLANID WHERE PLAN_ID = @L_PLAN_ID)
		INSERT INTO @lst_PLANID VALUES(@L_PLAN_ID)

	-- TRU TIEN CHO KE HOACH
		--PRINT @L_PLAN_ID
		--PRINT @L_QUANTITY
		----UPDATE PL_MASTER SET TOTAL_AMT = (TOTAL_AMT - CONVERT(INT,@L_QUANTITY) * @L_PRICE) WHERE PLAN_ID = @L_PLAN_ID
		--UPDATE PL_MASTER SET TOTAL_AMT = (TOTAL_AMT - CONVERT(INT,@L_QUANTITY) * @L_PRICE) WHERE PLAN_ID = @L_PLAN_ID		
		--IF @@Error <> 0 GOTO ABORT
		FETCH NEXT FROM TRADE_CURSOR INTO @L_TRADE_ID,@L_PLAN_ID,@L_QUANTITY,@L_PRICE, @CONTRACT_DT
	END
	CLOSE TRADE_CURSOR
	DEALLOCATE TRADE_CURSOR

	--TINH TONG TIEN DA THUC HIEN TREN KE HOACH TRONG PO
	SET @L_PLAN_ID = '';
	DECLARE PLCUR CURSOR FOR SELECT * FROM @lst_PLANID
	OPEN PLCUR
	FETCH NEXT FROM PLCUR INTO @L_PLAN_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--UPDATE PL_MASTER SET TOTAL_AMT = (SELECT SUM(ISNULL(QUANTITY,0)*ISNULL(PRICE,0)) 
											--FROM TR_PO_DETAIL WHERE PLAN_ID = @L_PLAN_ID AND RECORD_STATUS='1')
		--WHERE PLAN_ID = @L_PLAN_ID

		FETCH NEXT FROM PLCUR INTO @L_PLAN_ID
	END
	CLOSE PLCUR
	DEALLOCATE PLCUR
	-- TRU SO LUONG TRONG CHI TIET KE HOACH
	-- INSERT VAO LOG
	INSERT INTO dbo.PL_PROCESS
	(
						REQ_ID,
						PROCESS_ID,
						CHECKER_ID,
						APPROVE_DT,
						PROCESS_DESC,NOTES
					)
					VALUES
					(   @p_PO_ID,        -- REQ_ID - varchar(15)
						'APP',        -- PROCESS_ID - varchar(10)
						@P_CHECKER_ID,        -- CHECKER_ID - varchar(15)
						GETDATE(), -- APPROVE_DT - datetime
					   N'Phê duyệt phiếu thành công' ,N'Phê duyệt PO')		
COMMIT TRANSACTION
SELECT '0' as Result, N'PO số: '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID)+N' đã được phê duyệt thành công. Bạn có thể thực hiện thanh toán hoặc tạm ứng cho PO' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		PRINT 'ERROR'
		ROLLBACK TRANSACTION
		CLOSE TRADE_CURSOR
		DEALLOCATE TRADE_CURSOR
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End