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

ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Ins]  

@p_REQ_CODE nvarchar(100) = NULL,  

@p_REQ_NAME nvarchar(200) = NULL,  

@p_REQ_DT DATETIME = NULL,  

@p_REQ_TYPE int = NULL,  

@p_REQ_CONTENT NVARCHAR(1000)=NULL,  

@p_REQ_REASON NVARCHAR(500)=NULL,  

@p_TOTAL_AMT decimal = NULL,  

@p_NOTES nvarchar(1000) = NULL,  

@p_RECORD_STATUS varchar(1) = NULL,  

@p_MAKER_ID varchar(20) = NULL,  

@p_CREATE_DT DATETIME = NULL,  

@p_AUTH_STATUS varchar(50) = NULL,  

@p_CHECKER_ID varchar(20) = NULL,  

@p_APPROVE_DT DATETIME = NULL,  

@p_BRANCH_ID VARCHAR(15)=NULL,  

@p_DVDM_ID VARCHAR(20) = NULL,  

@p_REQ_PARENT_ID VARCHAR(20) = NULL,  

@p_BRANCH_FEE NVARCHAR(500) = NULL,  

@p_DEP_ID VARCHAR(20)=NULL,  

@p_DEP_FEE_ID VARCHAR(20)= NULL,  

@p_IS_BACKDAY BIT = NULL,  

@p_REQ_LINE VARCHAR(20),  

@p_SIGN_USER VARCHAR(20) = NULL,  

@p_IS_CHECKALL BIT = NULL,  

@p_BASED_CONTENT NVARCHAR(3000) = NULL,  

@p_PL_BASED_ID VARCHAR(15) = NULL,  

@p_ListGood XML,  

@p_ListCostCenter XML,  

@p_ListTransfer XML  

AS  

BEGIN TRANSACTION  

DECLARE @DEP_ID VARCHAR(15) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME = @p_MAKER_ID)  

exec [dbo].[PL_REQUEST_DOC_CODE_GenKey] @p_BRANCH_ID,@DEP_ID, @p_REQ_CODE out  

IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='')  

BEGIN  

SELECT 'REQ-00001' Result, '' REQ_ID, N'Mã tờ trình chủ trương bắc buộc nhập' ErrorDesc  

ROLLBACK TRANSACTION  

RETURN '-1'  

END  

IF EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE)  

BEGIN  

SELECT '-1' Result, '' REQ_ID, N'Tờ trình số:' + @p_REQ_CODE + N' đã tồn tại trong hệ thống!"' ErrorDesc  

ROLLBACK TRANSACTION  

RETURN '-1'  

END  

-- KIEM TRA NGAY TAO TT PHẢI LỚN HƠN NGÀY TRÌNH

IF(CONVERT(DATE, @p_REQ_DT, 103) > CONVERT(DATE, GETDATE(), 103))

BEGIN  

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

ROLLBACK TRANSACTION  

RETURN '-1'  

END  

DECLARE @sErrorCode VARCHAR(20)  

--insert master  

DECLARE @l_REQ_ID VARCHAR(15)  

EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC', @l_REQ_ID out  

IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT  

DECLARE @BRANCH_CREATE VARCHAR(20),@DEP_CREATE VARCHAR(20)  

SET @BRANCH_CREATE= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)  

IF(EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS'))  

SET @DEP_CREATE=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)  

ELSE  

SET @DEP_CREATE=''  

IF(CAST(@p_REQ_DT AS DATE) < CAST(GETDATE() AS DATE))  

SET @p_IS_BACKDAY=1;  

ELSE  

SET @p_IS_BACKDAY=0  

INSERT INTO dbo.PL_REQUEST_DOC  

(  

REQ_ID,  

REQ_CODE,  

REQ_NAME,  

REQ_DT,  

REQ_TYPE,  

REQ_CONTENT,  

REQ_REASON,  

BRANCH_ID,  

TOTAL_AMT,  

NOTES,  

RECORD_STATUS,  

MAKER_ID,  

CREATE_DT,  

AUTH_STATUS,  

CHECKER_ID,  

APPROVE_DT,  

DVDM_APP_ID,  

REQ_PARENT_ID,  

BRANCH_FEE,  

IS_BACKDAY,  

PROCESS_ID,  

DEP_ID,  

DEP_FEE,  

BRANCH_CREATE,  

DEP_CREATE,  

REQ_LINE,SIGN_USER,  

IS_CHECKALL,  

BASED_CONTENT,PL_BASED_ID  

)  

VALUES  

( @l_REQ_ID, -- REQ_ID - varchar(15)  

@p_REQ_CODE, -- REQ_CODE - varchar(100)  

@p_REQ_NAME, -- REQ_NAME - nvarchar(200)  

CAST(@p_REQ_DT AS DATE), -- REQ_DT - datetime  

@p_REQ_TYPE, -- REQ_TYPE - int  

@p_REQ_CONTENT, -- REQ_CONTENT - nvarchar(1000)  

@p_REQ_REASON, -- REQ_REASON - nvarchar(500)  

@p_BRANCH_ID, -- BRANCH_ID - varchar(15)  

@p_TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0)  

@p_NOTES, -- NOTES - nvarchar(1000)  

@p_RECORD_STATUS, -- RECORD_STATUS - varchar(1)  

@p_MAKER_ID, -- MAKER_ID - varchar(12)  

CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime  

'E', -- AUTH_STATUS - varchar(50)  

@p_CHECKER_ID, -- CHECKER_ID - varchar(12)  

CAST(@P_APPROVE_DT AS DATE), -- APPROVE_DT - datetime  

@p_DVDM_ID,  

@p_REQ_PARENT_ID,  

@p_BRANCH_FEE,  

@p_IS_BACKDAY,  

'',  

@p_DEP_ID,  

@p_DEP_FEE_ID,  

@BRANCH_CREATE,  

@DEP_CREATE,  

@p_REQ_LINE,  

@p_SIGN_USER,  

@p_IS_CHECKALL,  

@p_BASED_CONTENT,@p_PL_BASED_ID  

)  

IF @@Error <> 0 GOTO ABORT  

DECLARE @TABLE TABLE(  

PLAN_ID varchar(15) ,  

TRADE_ID varchar(15) ,  

GOODS_ID varchar(15) ,  

[DESCRIPTION] nvarchar(500),  

UNIT_ID varchar(15) ,  

QUANTITY decimal(18,0) ,  

PRICE decimal(18,2) ,  

TOTAL_AMT decimal(18,2),  

NOTES nvarchar(1000),  

REQDT_TYPE VARCHAR(1),  

NAME NVARCHAR(500),  

DVDM_ID VARCHAR(20),  

HH_ID VARCHAR(20),  

CURRENCY nvarchar(50),  

EXCHANGE_RATE decimal(18,2),  

TAXES decimal(18, 2),  

SUP_ID VARCHAR(20),  

TRADE_TYPE VARCHAR(20),  

KHOI_ID VARCHAR(20),  

UNIT_NAME nvarchar(200)  

)  

--Insert into TABLE PL_REQUEST_DOC_DT  

Declare @hdoc INT  

Exec sp_xml_preparedocument @hdoc Output,@p_ListGood  

INSERT INTO @TABLE  

SELECT PLAN_ID,  

TRADE_ID,  

GOODS_ID,  

DESCRIPTION,  

UNIT_ID,  

QUANTITY,  

PRICE,  

TOTAL_AMT,  

NOTES,  

REQDT_TYPE,  

NAME,  

DVDM_ID,  

HH_ID,  

CURRENCY,  

EXCHANGE_RATE,  

TAXES,  

SUP_ID,  

TRADE_TYPE,  

KHOI_ID,UNIT_NAME  

FROM OPENXML(@hDoc,'/Root/ListGood',2)  

WITH  

(  

PLAN_ID varchar(15) ,  

TRADE_ID varchar(15) ,  

GOODS_ID varchar(15) ,  

[DESCRIPTION] nvarchar(500),  

UNIT_ID varchar(15) ,  

QUANTITY decimal(18,0) ,  

PRICE decimal(18,2) ,  

TOTAL_AMT decimal(18,2),  

NOTES nvarchar(1000),  

REQDT_TYPE VARCHAR(1),  

NAME NVARCHAR(500),  

DVDM_ID VARCHAR(20),  

HH_ID VARCHAR(20),  

CURRENCY nvarchar(50),  

EXCHANGE_RATE decimal(18,2),  

TAXES decimal(18, 2),  

SUP_ID VARCHAR(20),  

TRADE_TYPE VARCHAR(20),  

KHOI_ID VARCHAR(20),  

UNIT_NAME nvarchar(200)  

)  

DECLARE @TABLE_TRANSFER TABLE (  

FR_PLAN_ID varchar(15),  

FR_TRADE_ID varchar(15),  

FR_GOOD_ID varchar(15),  

FR_BRN_ID varchar(15),  

TO_BRN_ID varchar(15),  

TO_PLAN_ID varchar(15),  

TO_TRADE_ID varchar(15),  

TO_GOOD_ID varchar(15),  

QTY DECIMAL(18,0),  

TOTAL_AMT decimal(18),  

NOTES nvarchar(1000),  

FR_DEP_ID VARCHAR(20),  

TO_DEP_ID VARCHAR(20),  

FR_DVDM_ID VARCHAR(20),  

TO_DVDM_ID VARCHAR(20),  

FR_KHOI_ID VARCHAR(20),  

TO_KHOI_ID VARCHAR(20),  

FR_GD_TYPE VARCHAR(20),  

TO_GD_TYPE VARCHAR(20)  

)  

Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer  

INSERT INTO @TABLE_TRANSFER  

SELECT FR_PLAN_ID,  

FR_TRADE_ID,  

FR_GOOD_ID,  

FR_BRN_ID,  

TO_BRN_ID,  

TO_PLAN_ID,  

TO_TRADE_ID,  

TO_GOOD_ID,  

QTY,  

TOTAL_AMT,  

NOTES,  

FR_DEP_ID,  

TO_DEP_ID,  

FR_DVDM_ID,  

TO_DVDM_ID,  

FR_KHOI_ID,  

TO_KHOI_ID,  

FR_GD_TYPE,  

TO_GD_TYPE  

FROM OPENXML(@hdoc,'/Root/ListTransfer',2)  

WITH  

(  

FR_PLAN_ID varchar(15),  

FR_TRADE_ID varchar(15),  

FR_GOOD_ID varchar(15),  

FR_BRN_ID varchar(15),  

TO_BRN_ID varchar(15),  

TO_PLAN_ID varchar(15),  

TO_TRADE_ID varchar(15),  

TO_GOOD_ID varchar(15),  

QTY DECIMAL(18,0),  

TOTAL_AMT decimal(18),  

NOTES nvarchar(1000),  

FR_DEP_ID VARCHAR(20),  

TO_DEP_ID VARCHAR(20),  

FR_DVDM_ID VARCHAR(20),  

TO_DVDM_ID VARCHAR(20),  

FR_KHOI_ID VARCHAR(20),  

TO_KHOI_ID VARCHAR(20),  

FR_GD_TYPE VARCHAR(20),  

TO_GD_TYPE VARCHAR(20)  

)  

WHERE FR_BRN_ID !='' AND FR_BRN_ID IS NOT NULL  

DECLARE ListGoods CURSOR FOR  

SELECT PLAN_ID,  

TRADE_ID,  

GOODS_ID,  

DESCRIPTION,  

UNIT_ID,  

QUANTITY,  

PRICE,  

TOTAL_AMT,  

NOTES,  

REQDT_TYPE,  

NAME,  

DVDM_ID,  

HH_ID,  

CURRENCY,  

EXCHANGE_RATE,  

TAXES,  

SUP_ID,  

TRADE_TYPE,KHOI_ID,UNIT_NAME FROM @TABLE  

OPEN ListGoods  

Declare  

@PLAN_ID varchar(15),  

@TRADE_ID varchar(15),  

@GOODS_ID varchar(15),  

@DESCRIPTION nvarchar(500),  

@UNIT_ID varchar(15),  

@QUANTITY decimal(18),  

@PRICE decimal(18,2),  

@TOTAL_AMT decimal(18,2),  

@NOTES nvarchar(1000),  

@REQDT_TYPE VARCHAR(1),  

@NAME NVARCHAR(500),  

@DVDM_ID VARCHAR(20),  

@HH_ID VARCHAR(20),  

@CURRENCY nvarchar(50),  

@EXCHANGE_RATE decimal(18, 2),  

@TAXES decimal(18, 2),  

@SUP_ID VARCHAR(20),  

@TRADE_TYPE VARCHAR(20),  

@KHOI_ID VARCHAR(20),  

@UNIT_NAME nvarchar(200)  

FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,  

@PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME  

WHILE @@FETCH_STATUS = 0  

BEGIN  

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

--IF(@TRADE_TYPE IS NOT NULL AND @TRADE_TYPE <>'' AND @TRADE_TYPE ='VCCB' AND (@SUP_ID IS NOT NULL AND @SUP_ID <>''))  

--BEGIN  

-- SELECT 'REQ-00001' Result, '' REQ_ID, N'Lưới hàng hóa: Nếu chọn 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  

DECLARE @l_REQDT_ID VARCHAR(15)  

EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC_DT', @l_REQDT_ID out  

IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT  

--SET @TOTAL_AMT=(@PRICE * @QUANTITY * @EXCHANGE_RATE) + (@TAXES * @EXCHANGE_RATE)  

INSERT INTO dbo.PL_REQUEST_DOC_DT  

(  

REQDT_ID,  

REQ_ID,  

PLAN_ID,  

TRADE_ID,  

GOODS_ID,  

NAME,  

DESCRIPTION,  

REQDT_TYPE,  

UNIT_ID,  

QUANTITY,  

PRICE,  

TOTAL_AMT,  

RECORD_STATUS,  

MAKER_ID,  

CREATE_DT,  

AUTH_STATUS,  

CHECKER_ID,  

APPROVE_DT,  

DVDM_ID,  

HANGHOA_ID,  

CURRENCY,  

EXCHANGE_RATE,  

TAXES,SUP_ID,TRADE_TYPE,KHOI_ID,UNIT_NAME  

)  

VALUES  

( @l_REQDT_ID, -- REQDT_ID - varchar(15)  

@l_REQ_ID, -- REQ_ID - varchar(15)  

@PLAN_ID, -- PLAN_ID - varchar(15)  

@TRADE_ID, -- TRADE_ID - varchar(15)  

@GOODS_ID, -- GOODS_ID - varchar(15)  

@NAME, -- NAME - nvarchar(200)  

@DESCRIPTION, -- DESCRIPTION - nvarchar(500)  

@REQDT_TYPE, -- REQDT_TYPE - varchar(1)  

@UNIT_ID, -- UNIT_ID - varchar(15)  

@QUANTITY, -- QUANTITY - decimal(18, 0)  

@PRICE, -- PRICE - decimal(18, 0)  

@TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0)  

@p_RECORD_STATUS, -- RECORD_STATUS - varchar(1)  

@p_MAKER_ID, -- MAKER_ID - varchar(15)  

CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime  

@p_AUTH_STATUS, -- AUTH_STATUS - varchar(50)  

@p_CHECKER_ID, -- CHECKER_ID - varchar(15)  

CAST(@P_APPROVE_DT AS DATE), -- APPROVE_DT - datetime  

@DVDM_ID,  

@HH_ID,  

@CURRENCY,  

@EXCHANGE_RATE,  

@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME  

)  

IF @@ERROR <> 0 GOTO ABORT1  

-- next Group_Id  

FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,  

@PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME  

END  

CLOSE ListGoods  

DEALLOCATE ListGoods  

IF @@Error <> 0 GOTO ABORT  

--Insert into TABLE PL_REQUEST_DOC_DT  

UPDATE dbo.PL_REQUEST_DOC SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID =@l_REQ_ID) WHERE PL_REQUEST_DOC.REQ_ID=@l_REQ_ID  

Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer  

DECLARE ListTransfers CURSOR FOR  

SELECT *  

FROM @TABLE_TRANSFER  

OPEN ListTransfers  

Declare  

@FR_PLAN_ID varchar(15),  

@FR_TRADE_ID varchar(15),  

@FR_GOOD_ID varchar(15),  

@FR_BRN_ID varchar(15),  

@TO_BRN_ID varchar(15),  

@TO_PLAN_ID varchar(15),  

@TO_TRADE_ID varchar(15),  

@TO_GOOD_ID varchar(15),  

@QTY DECIMAL(18,0),  

@FR_DEP_ID VARCHAR(20),  

@TO_DEP_ID VARCHAR(20),  

@FR_DVDM_ID VARCHAR(20),  

@TO_DVDM_ID VARCHAR(20),  

@FR_KHOI_ID VARCHAR(20),  

@TO_KHOI_ID VARCHAR(20),  

@FR_GD_TYPE VARCHAR(20),  

@TO_GD_TYPE VARCHAR(20)  

FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID,  

@TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE  

WHILE @@FETCH_STATUS = 0  

BEGIN  

DECLARE @l_REQ_TRANSFER_ID VARCHAR(15)  

EXEC SYS_CodeMasters_Gen 'PL_REQUEST_TRANSFER', @l_REQ_TRANSFER_ID out  

IF @l_REQ_TRANSFER_ID='' OR @l_REQ_TRANSFER_ID IS NULL GOTO ABORT  

INSERT INTO dbo.PL_REQUEST_TRANSFER  

(  

REQ_TRANSFER_ID,  

REQ_DOC_ID,  

FR_PLAN_ID,  

FR_TRADE_ID,  

FR_GOOD_ID,  

FR_BRN_ID,  

TO_BRN_ID,  

TO_PLAN_ID,  

TO_TRADE_ID,  

TO_GOOD_ID,  

QTY,  

TOTAL_AMT,  

NOTES,  

AUTH_STATUS,  

MAKER_ID,  

CREATE_DT,  

CHECKER_ID,  

APPROVE_DT,  

FR_DEP_ID,  

TO_DEP_ID,  

FR_DVDM_ID,  

TO_DVDM_ID,  

FR_KHOI_ID,  

TO_KHOI_ID  

)  

VALUES  

( @l_REQ_TRANSFER_ID, -- REQ_TRANSFER_ID - varchar(15)  

@l_REQ_ID, -- REQ_DOC_ID - varchar(15)  

@FR_PLAN_ID, -- FR_PLAN_ID - varchar(15)  

@FR_TRADE_ID, -- FR_TRADE_ID - varchar(15)  

@FR_GOOD_ID, -- FR_GOOD_ID - varchar(15)  

@FR_BRN_ID, -- FR_BRN_ID - varchar(15)  

@TO_BRN_ID, -- TO_BRN_ID - varchar(15)  

@TO_PLAN_ID, -- TO_PLAN_ID - varchar(15)  

@TO_TRADE_ID, -- TO_TRADE_ID - varchar(15)  

@TO_GOOD_ID, -- TO_GOOD_ID - varchar(15)  

@QTY, -- QTY - decimal(18, 0)  

@TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0)  

@NOTES, -- NOTES - nvarchar(500)  

@p_AUTH_STATUS, -- AUTH_STATUS - varchar(1)  

@p_MAKER_ID, -- MAKER_ID - varchar(15)  

CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime  

@p_CHECKER_ID, -- CHECKER_ID - varchar(15)  

CAST(@p_APPROVE_DT AS DATE),  

@FR_DEP_ID,  

@TO_DEP_ID,  

@FR_DVDM_ID,  

@TO_DVDM_ID,  

@FR_KHOI_ID,  

@TO_KHOI_ID -- APPROVE_DT - datetime  

)  

IF @@ERROR <> 0 GOTO ABORT1  

-- next Group_Id  

FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID,  

@TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE  

END  

CLOSE ListTransfers  

DEALLOCATE ListTransfers  

IF @@Error <> 0 GOTO ABORT  

--Insert into TABLE PL_REQUEST_DOC_DT  

Exec sp_xml_preparedocument @hdoc Output,@p_ListCostCenter  

DECLARE ListCostCenters CURSOR FOR  

SELECT *  

FROM OPENXML(@hDoc,'/Root/ListCostCenter',2)  

WITH  

(  

COST_ID varchar(15),  

NOTES nvarchar(1000)  

)  

OPEN ListCostCenters  

Declare  

@COST_ID varchar(15)  

FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES  

WHILE @@FETCH_STATUS = 0  

BEGIN  

DECLARE @l_REQ_COST_ID VARCHAR(15)  

EXEC SYS_CodeMasters_Gen 'PL_REQUEST_COSTCENTER', @l_REQ_COST_ID out  

IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT  

INSERT INTO dbo.PL_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)  

@COST_ID, -- COST_ID - varchar(15)  

@l_REQ_ID, -- REQ_ID - varchar(15)  

@NOTES, -- NOTES - nvarchar(500)  

@p_AUTH_STATUS, -- AUTH_STATUS - varchar(1)  

@p_MAKER_ID, -- MAKER_ID - varchar(15)  

CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime  

@p_CHECKER_ID, -- CHECKER_ID - varchar(15)  

CAST(@p_APPROVE_DT AS DATE) -- APPROVE_DT - datetime  

)  

IF @@ERROR <> 0 GOTO ABORT1  

-- next Group_Id  

FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES  

END  

CLOSE ListCostCenters  

DEALLOCATE ListCostCenters  

COMMIT TRANSACTION  

SELECT '0' as Result, @l_REQ_ID REQ_ID, @p_REQ_CODE ErrorDesc  

RETURN '0'  

ABORT:  

BEGIN  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc  

RETURN '-1'  

End  

ABORT1:  

BEGIN  

CLOSE ListGoods  

DEALLOCATE ListGoods  

CLOSE ListCostCenters  

DEALLOCATE ListCostCenters  

CLOSE ListCostCenters  

DEALLOCATE ListCostCenters  

ROLLBACK TRANSACTION  

SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc  

RETURN '-1'  

End