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

CREATE PROCEDURE [dbo].[PL_REQUEST_DOC_SendApp]  

@p_REQ_ID VARCHAR(20),  

@p_PROCESS_ID VARCHAR(20),  

@p_TLNAME VARCHAR(20),  

@p_MAKER_ID VARCHAR(20)  

AS  

BEGIN TRANSACTION  

IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (PL_BASED_ID IS NULL OR PL_BASED_ID ='')))  

BEGIN  

--- KIEM TRA NGAN SACH LUCTV 18052020  

DECLARE @ERROR BIT ,@EROOR_DES NVARCHAR(500)  

SELECT @ERROR=ERROR, @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_TLNAME,@p_PROCESS_ID)  

IF(@ERROR=1)  

BEGIN  

ROLLBACK TRANSACTION;  

SELECT '-1' Result, @EROOR_DES ErrorDesc  

RETURN '-1';  

END  

-----  

DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10),  

@BRANCH_CREATE_TYPE VARCHAR(10)  

IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND ( CAST(REQ_DT AS DATE) > CAST(GETDATE() AS DATE))) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ngày tạo không được lớn hơn ngày hiện tại' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_NAME IS NULL OR REQ_NAME='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Tên tờ trình bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_CONTENT IS NULL OR REQ_CONTENT='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Nội dung tờ trình bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND ( REQ_REASON IS NULL OR REQ_REASON='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Lý do bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(NOT EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID ) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Danh sách hàng hóa bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PL_BASED_ID IS NOT NULL AND PL_BASED_ID <>''))  

BEGIN  

DECLARE @PL_BASED_ID VARCHAR(20)  

SET @PL_BASED_ID=(SELECT PL_BASED_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID )  

IF( EXISTS(SELECT DT.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT DT WHERE DT.REQ_ID=@p_REQ_ID AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT DTB WHERE DTB.REQ_ID=@PL_BASED_ID AND DTB.TRADE_ID=DT.TRADE_ID AND DTB.HANGHOA_ID=DT.HANGHOA_ID)))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Danh sách hàng hóa không có trong tờ trình căn cứ' ErrorDesc  

RETURN '-1'  

END  

END  

IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (HANGHOA_ID IS NULL OR HANGHOA_ID ='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Hàng hóa bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (GOODS_ID IS NULL OR GOODS_ID ='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Hạng mục ngân sách bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND (TOTAL_AMT IS NULL OR TOTAL_AMT = 0)) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Vui lòng nhập số tiền điều chuyển' ErrorDesc  

RETURN '-1'  

END  

SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID  

-- KIEM TRA NEU HINH THUC CHI DINH THAU LA THEO QUY DINH VCCB THI KHONG CHO PHEP CHON NHA CUNG CAP  

IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE TRADE_TYPE ='VCCB' AND SUP_ID <>'' AND SUP_ID IS NOT NULL AND REQ_ID =@p_REQ_ID))  

BEGIN  

SELECT 'REQ-00001' Result, '' REQ_ID, N'Nếu hình thức mua sắm theo quy định VCCB thì bạn không được phép chọn nhà cung cấp. Vui lòng để trống nhà cung cấp' ErrorDesc  

ROLLBACK TRANSACTION  

RETURN '-1'  

END  

--  

IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE TRADE_TYPE ='CDT' AND ( SUP_ID ='' OR SUP_ID IS NULL) AND REQ_ID =@p_REQ_ID))  

BEGIN  

SELECT 'REQ-00001' Result, '' REQ_ID, N'Nếu hình thức mua sắm chỉ định thầu thì bạn không được phép bỏ trống nhà cung cấp. Vui lòng chọn nhà cung cấp' ErrorDesc  

ROLLBACK TRANSACTION  

RETURN '-1'  

END  

----  

IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND FR_BRN_ID=@BRANCH_CREATE AND (FR_GOOD_ID IS NULL OR FR_GOOD_ID='')  

AND ((FR_DEP_ID=@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') AND (FR_DEP_ID IS NULL OR FR_DEP_ID=''))) ))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Bạn là đơn vị chuyển ngân sách, vui lòng chọn hạn mục ngân sách chuyển' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT Temp.TRADE_ID FROM (  

SELECT TB.TRADE_ID ,SUM(TB.TOTAL_AMT) AS TOTAL_AMT_EXE,  

SUM(ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0)) AS TOTAL_AMT_REMAIN  

FROM dbo.PL_REQUEST_DOC_DT TB  

LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.TRADE_ID  

WHERE TB.REQ_ID=@p_REQ_ID AND TB.REQDT_TYPE='I'  

GROUP BY TB.TRADE_ID  

)Temp  

WHERE Temp.TOTAL_AMT_EXE > Temp.TOTAL_AMT_REMAIN))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS RESULT , '' REQ_CODE,'' REQ_ID, N'Số tiền sử dụng lớn hơn số tiền dự kiến còn lại của hạng mục trong ngân sách' ErrorDesc  

RETURN '-1'  

END  

IF( EXISTS(SELECT GOODS_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND REQDT_TYPE='O') AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND TO_GOOD_ID IN (SELECT GOODS_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ 

_ID=@p_REQ_ID AND REQDT_TYPE='O')))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS RESULT ,'' REQ_CODE, '' REQ_ID, N'Hạng mục ngoài ngân sách, vui lòng thêm danh sách điều chuyển ngân sách' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (PRICE IS NULL OR PRICE=0 OR QUANTITY IS NULL OR QUANTITY=0)) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Số luợng, đơn giá của hàng hóa bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (DESCRIPTION IS NULL OR DESCRIPTION='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Quy cách kỹ thuật bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

IF(EXISTS(SELECT REQDT_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (DESCRIPTION IS NULL OR DESCRIPTION='')) )  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Quy cách kỹ thuật bắt buộc nhập' ErrorDesc  

RETURN '-1'  

END  

--DECLARE lstTransfer CURSOR FOR  

--SELECT FR_GOOD_ID,TO_GOOD_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID  

--OPEN lstTransfer  

--DECLARE @FR_GOOD_ID VARCHAR(20),@TO_GOOD_ID VARCHAR(20)  

--FETCH NEXT FROM lstTransfer INTO @FR_GOOD_ID,@TO_GOOD_ID  

--WHILE @@FETCH_STATUS=0  

--BEGIN  

-- IF(@FR_GOOD_ID IS NOT NULL AND @FR_GOOD_ID<>'')  

-- BEGIN  

-- DECLARE @FR_GD_TYPE VARCHAR(20),@TO_GD_TYPE VARCHAR(20)  

-- SET @FR_GD_TYPE =(SELECT GT.GD_TYPE_ID FROM  

-- (  

-- SELECT GD_CODE FROM dbo.CM_GOODS WHERE GD_ID=@FR_GOOD_ID  

-- ) CG  

-- LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%')  

-- SET @TO_GD_TYPE =(SELECT GT.GD_TYPE_ID FROM  

-- (  

-- SELECT GD_CODE FROM dbo.CM_GOODS WHERE GD_ID=@TO_GOOD_ID  

-- ) CG  

-- LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%')  

-- IF(NOT EXISTS(SELECT * FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID= @FR_GD_TYPE AND @TO_GD_TYPE IN (SELECT value FROM dbo.wsiSplit(NOTES,';')) ) )  

-- BEGIN  

-- DECLARE @FR_GD_TYPE_NAME NVARCHAR(100),@TO_GD_TYPE_NAME NVARCHAR(100)  

-- SET @FR_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@FR_GD_TYPE)  

-- SET @TO_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@TO_GD_TYPE)  

-- ROLLBACK TRANSACTION  

-- SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Không được điều chuyển từ ' + @FR_GD_TYPE_NAME +N' sang ' + @TO_GD_TYPE_NAME ErrorDesc  

-- RETURN '-1'  

-- CLOSE lstTransfer  

-- DEALLOCATE lstTransfer  

-- END  

-- END  

-- FETCH NEXT FROM lstTransfer INTO @FR_GOOD_ID,@TO_GOOD_ID  

--END  

--CLOSE lstTransfer  

--DEALLOCATE lstTransfer  

END  

UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID  

SELECT @BRANCH_ID =BRANCH_ID,@DEP_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID  

SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)  

SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)  

--IF(@BRANCH_TYPE='PGD')  

-- SET @BRANCH_ID=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)  

DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  

---KIEM TRA XEM CO CAP PHE DUYET TRUNG GIAN HAY KHONG 20-05-2020 LUCTV  

IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>''))  

BEGIN  

DECLARE @BRANCH_SIGN_ID VARCHAR(20),@DEP_SIGN_ID VARCHAR(20),@BRANCH_SIGN_TYPE VARCHAR(10),@ROLE_SIGN VARCHAR(20)  

SELECT @BRANCH_SIGN_ID=TLSUBBRID,@DEP_SIGN_ID=SECUR_CODE,@BRANCH_SIGN_TYPE=BRANCH_TYPE,@ROLE_SIGN=RoleName FROM dbo.TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)  

UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID  

INSERT INTO dbo.PL_REQUEST_PROCESS  

(  

REQ_ID,  

PROCESS_ID,  

STATUS,  

ROLE_USER,  

BRANCH_ID,  

DEP_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)  

'SIGN', -- PROCESS_ID - varchar(10)  

'C', -- STATUS - varchar(5)  

@ROLE_SIGN, -- ROLE_USER - varchar(50)  

@BRANCH_SIGN_ID,  

CASE WHEN @BRANCH_SIGN_TYPE='HS' THEN @DEP_SIGN_ID ELSE '' END, -- BRANCH_ID - varchar(15)  

'', -- CHECKER_ID - varchar(15)  

NULL, -- APPROVE_DT - datetime  

'', -- PARENT_PROCESS_ID - varchar(10)  

'N', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

'', -- DVDM_ID - varchar(15)  

N'Chờ cấp phê duyệt trung gian xác nhận phiếu', -- NOTES - nvarchar(500)  

NULL -- IS_HAS_CHILD - bit  

)  

--  

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)  

'SEND',  

@p_TLNAME, -- CHECKER_ID - varchar(15)  

GETDATE(), -- APPROVE_DT - datetime  

N'Người tạo tờ trình gửi phê duyệt thành công' , -- PROCESS_DESC - nvarchar(1000)  

N'Nhân viên gửi phê duyệt ' -- NOTES - nvarchar(1000)  

)  

END  

ELSE -- NGUOC LAI KHONG CO CAP PHE DUYET TRUNG GIAN  

BEGIN  

INSERT INTO dbo.PL_REQUEST_PROCESS  

(  

REQ_ID,  

PROCESS_ID,  

STATUS,  

ROLE_USER,  

BRANCH_ID,  

DEP_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)  

'APPNEW', -- PROCESS_ID - varchar(10)  

'C', -- STATUS - varchar(5)  

'GDDV', -- ROLE_USER - varchar(50)  

@BRANCH_ID,  

@DEP_ID, -- BRANCH_ID - varchar(15)  

'', -- CHECKER_ID - varchar(15)  

NULL, -- APPROVE_DT - datetime  

'', -- PARENT_PROCESS_ID - varchar(10)  

'N', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

'', -- DVDM_ID - varchar(15)  

-- N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500)  

N'Chờ trưởng đơn vị phê duyệt' ,  

NULL -- IS_HAS_CHILD - bit  

)  

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)  

'SEND',  

@p_TLNAME, -- CHECKER_ID - varchar(15)  

GETDATE(), -- APPROVE_DT - datetime  

N'Người tạo tờ trình gửi phê duyệt thành công' , -- PROCESS_DESC - nvarchar(1000)  

N'Nhân viên gửi phê duyệt ' -- NOTES - nvarchar(1000)  

)  

END  

IF @@Error <> 0 GOTO ABORT  

COMMIT TRANSACTION  

IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='SIGN'))  

BEGIN  

SELECT '4' as Result,'' REQ_CODE,''REQ_ID, N'Tờ trình số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)+N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc  

RETURN '4'  

END  

ELSE  

BEGIN  

SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc  

RETURN '0'  

END  

ABORT:  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc  

RETURN '-1'  

End