DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

CREATE PROCEDURE [dbo].[TR_PO_MASTER_Upd]

@p_PO_ID varchar(15) = NULL,

@P_PO_TYPE INT = NULL,

@p_PO_CODE varchar(15) = NULL,

@p_PO_NAME nvarchar(200) = NULL,

@p_CONTRACT_ID varchar(15) = NULL,

@p_SUP_ID varchar(15) = NULL,

@p_SUP_NAME nvarchar(200) = NULL,

@p_SUP_ADDR nvarchar(200) = NULL,

@p_INPUT_DT VARCHAR(20) = NULL,

@p_PAYMENT_DT VARCHAR(20) = NULL,

@p_TOTAL_AMT decimal(18) = NULL,

@p_REQ_DOC_ID varchar(15) = NULL,

@p_DELIVERY_DT VARCHAR(20) = NULL,

@p_PAYAPP_DT VARCHAR(20) = NULL,

@p_NOTES nvarchar(1000) = NULL,

@p_RECORD_STATUS varchar(1) = NULL,

@p_MAKER_ID varchar(15) = NULL,

@p_CREATE_DT VARCHAR(20) = NULL,

@p_AUTH_STATUS varchar(50) = NULL,

@p_CHECKER_ID varchar(15) = NULL,

@p_APPROVE_DT VARCHAR(20) = NULL,

@p_TR_REQ_ID VARCHAR(20) = NULL,

@p_TR_REQ_CODE VARCHAR(20)= NULL,

@P_LISTASSET XML = NULL,

@P_LISTPAYMENT XML = NULL,

@P_LISTROLE XML = NULL,

@p_BRANCH_ID varchar(15) = null,

@p_IS_CLOSED VARCHAR(1) = NULL,

-----------------------BAODNQ 2/3/2022 : Thêm tham số----------------------

@p_IS_SEND_APPR VARCHAR(1) = NULL,

@p_SEND_APPR_DT VARCHAR(20) = NULL

AS

IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID = @p_PO_ID AND AUTH_STATUS = 'U'))

BEGIN

SELECT '-1' Result, @p_PO_ID AS PO_ID,

N'Không thể chỉnh sửa. PO ' +@p_PO_CODE+ N' đã được gửi yêu cầu phê duyệt' AS ErrorDesc

RETURN '-1'

END

DECLARE @REF_CODE VARCHAR(15) = NULL, @PDN_TT VARCHAR(15) = NULL

SET @REF_CODE =

( SELECT TOP 1 B.REQ_PAY_CODE

FROM TR_REQ_ADVANCE_DT A

INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

WHERE A.REF_ID =@p_PO_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

)

--- TIM KIEM XEM PO NAY CO DUOC PHEP THANH TOAN HAY KHONG

SET @PDN_TT =

( SELECT TOP 1 B.REQ_PAY_CODE

FROM TR_REQ_ADVANCE_DT A

INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)

)

IF(@p_IS_CLOSED ='Y' AND @REF_CODE IS NOT NULL)

BEGIN

--ROLLBACK TRANSACTION

SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện tạm ứng với số phiếu: '+@REF_CODE AS ErrorDesc

RETURN '-1'

END

IF(@p_IS_CLOSED ='Y' AND @PDN_TT IS NOT NULL)

BEGIN

--ROLLBACK TRANSACTION

SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện thanh toán với số phiếu: '+@PDN_TT AS ErrorDesc

RETURN '-1'

END

IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE [PO_CODE] = @p_PO_CODE AND [PO_ID] <> @p_PO_ID)

BEGIN

SELECT ErrorCode Result, '' PO_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'PO-00002'

RETURN '0'

END

--- 21052020 KIEM TRA XEM NEU PYCMS DA TON TAI HAY CHUA

IF((SELECT COUNT(*) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_DOC_ID)=1)

BEGIN

IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID AND AUTH_STATUS IN ('E','U','A') AND PO_ID <>@p_PO_ID))

BEGIN

--ROLLBACK TRANSACTION

SELECT '-1' Result, @p_PO_ID AS PO_ID, N'Phiếu yêu cầu mua sắm số :'+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_DOC_ID)+

N' đã được link tới PO có số: '+(SELECT TOP 1 PO_CODE FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID AND PO_ID <> @p_PO_ID) AS ErrorDesc

RETURN '-1'

END

END

------

Declare @hdoc INT

Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET

DECLARE AssetDetail CURSOR FOR

SELECT *

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

WITH

(

PD_ID VARCHAR(15),

PLAN_ID varchar(15) ,

TRADE_ID varchar(15) ,

REQ_DT_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),

IS_DELIVERY varchar(1) ,

DELIVERY_DT VARCHAR(20),

PAYMENT_STATUS varchar(4),

AMOUNT_PAID decimal(18) ,

PAID_DT VARCHAR(20) ,

INVOICENO varchar(1000),

NOTES nvarchar(1000),

RECEIVE_BRANCH varchar(15),

RECEIVE_ADDR nvarchar(1000),

RECEIVE_PERSON nvarchar(500),

RECEIVE_TEL varchar(100),

EXP_DELIVERY_DT varchar(20),

GOODS_NAME NVARCHAR(500),

INVOICE_DT VARCHAR(20),

--THIEUVQ 100415 THEM LOAI HANG HOA THUC TE

GOODSTYPE_REAL VARCHAR(15),

GOODSTYPE_REAL_NAME VARCHAR(15),

--THIEUVQ 13092016 THEM VAT, CONTRACT_DT

VAT DECIMAL(18,2),

PRICE_VAT DECIMAL(18,0),

CONTRACT_DT VARCHAR(15),

CURRENCY VARCHAR(15),

RATE INT,

TOTAL_AMT_FN DECIMAL(18,2)

)

OPEN AssetDetail

Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT

DECLARE PaymentDetail CURSOR FOR

SELECT *

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

WITH

(

PAY_ID VARCHAR(15),

PAY_PHASE varchar(15),

EXPECTED_DT VARCHAR(20),

[PERCENT] decimal(18,2),

[AMOUNT] decimal(18,2),

NOTES nvarchar(1000),

CURRENCY VARCHAR(15),

RATE INT,

TOTAL_AMT DECIMAL(18,2)

)

OPEN PaymentDetail

PRINT 'PASS KHOI TAO'

--Nhom user gui mail (DAO EDIT)

Exec sp_xml_preparedocument @hdoc Output,@P_LISTROLE

DECLARE LISTROLE CURSOR FOR

SELECT *

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

WITH

(

NOTIFI_ID VARCHAR(15),

TL_NAME varchar(15),

EDITOR_DT VARCHAR(20),

EDITOR_ID VARCHAR(15),

NOTES nvarchar(1000)

)

OPEN LISTROLE

BEGIN TRANSACTION

IF @p_DELIVERY_DT = ''

SET @p_DELIVERY_DT = NULL

IF @p_PAYAPP_DT = ''

SET @p_PAYAPP_DT = NULL

--insert master

UPDATE TR_PO_MASTER SET [PO_TYPE] = @P_PO_TYPE, [PO_CODE] = @p_PO_CODE,[PO_NAME] = @p_PO_NAME,

[CONTRACT_ID] = @p_CONTRACT_ID,[SUP_ID] = @p_SUP_ID,[SUP_NAME] = @p_SUP_NAME,[SUP_ADDR] = @p_SUP_ADDR,

[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[PAYMENT_DT] = CONVERT(DATETIME, @p_PAYMENT_DT, 103),

[TOTAL_AMT] = @p_TOTAL_AMT,[REQ_DOC_ID] = @p_TR_REQ_ID,[DELIVERY_DT] = CONVERT(DATETIME, @p_DELIVERY_DT, 103),

[PAYAPP_DT] = CONVERT(DATETIME, @p_PAYAPP_DT, 103),[NOTES] = @p_NOTES,[RECORD_STATUS] = '1',

[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = 'E',

[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), BRANCH_ID = @p_BRANCH_ID,

-----------------------BAODNQ 2/3/2022 : Thêm cột IS_SEND_APPR, SEND_APPR_DT----------------------

[IS_SEND_APPR] = @p_IS_SEND_APPR,

[SEND_APPR_DT] = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),

IS_CLOSED = @p_IS_CLOSED

WHERE PO_ID= @p_PO_ID

IF @@Error <> 0 GOTO ABORT

PRINT 'INSERT MASTER SUCCESS'

UPDATE dbo.TR_PO_MASTER_TEMP SET TR_REQ_ID=@p_TR_REQ_ID,TR_REQ_CODE=@p_TR_REQ_CODE WHERE PO_ID=@p_PO_ID

--insert asset detail tương đương như NỘI DUNG trong bảng contract detail

Declare

@PAY_ID VARCHAR(15),

@PD_ID VARCHAR(15),

@PLAN_ID varchar(15),

@TRADE_ID varchar(15),

@REQ_DT_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),

@IS_DELIVERY varchar(1),

@DELIVERY_DT VARCHAR(20),

@PAYMENT_STATUS varchar(4),

@AMOUNT_PAID decimal(18),

@PAID_DT VARCHAR(20),

@INVOICENO varchar(20),

@NOTES nvarchar(1000),

@PO_ID varchar(15) = NULL,

@PAY_PHASE VARCHAR(20) = NULL,

@EXPECTED_DT VARCHAR(20) = NULL,

@PERCENT decimal(18,2) = NULL,

@AMOUNT decimal(18,2) = NULL,

@RECEIVE_BRANCH varchar(15)=NULL,

@RECEIVE_ADDR nvarchar(1000)=NULL,

@RECEIVE_PERSON nvarchar(500)=NULL,

@RECEIVE_TEL varchar(100)=NULL,

@EXP_DELIVERY_DT varchar(20) = NULL,

@GOODS_NAME NVARCHAR(500) = NULL,

@INVOICE_DT VARCHAR(20),

@GOODSTYPE_REAL VARCHAR(15) = NULL,

@GOODSTYPE_REAL_NAME VARCHAR(15) = NULL,

@VAT DECIMAL(18,2),

@PRICE_VAT DECIMAL(18,0),

@CONTRACT_DT VARCHAR(15),

--DAO MOI THEM

@NOTIFI_ID VARCHAR(15),

@TL_NAME VARCHAR(15),

@EDITOR_DT VARCHAR(20),

@EDITOR_ID VARCHAR(15),

@NOTES_ROLE nvarchar(1000),

@CURRENCY VARCHAR(15),

@RATE INT,

@TOTAL_AMT_FN DECIMAL(18,2)

DECLARE @l_GOODSTYPE_ID VARCHAR(15)

DELETE FROM TR_PO_DETAIL WHERE PO_ID = @P_PO_ID

FETCH NEXT FROM AssetDetail INTO @PD_ID,@PLAN_ID,@TRADE_ID,@REQ_DT_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,

@PRICE,@TOTAL_AMT,@IS_DELIVERY,@DELIVERY_DT,@PAYMENT_STATUS,@AMOUNT_PAID,@PAID_DT,

@INVOICENO,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,@EXP_DELIVERY_DT,@GOODS_NAME, @INVOICE_DT,

@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT,@CURRENCY,@RATE,@TOTAL_AMT_FN

WHILE @@FETCH_STATUS = 0

BEGIN

--if( NOT EXISTS (SELECT * FROM PL_MASTER A WHERE A.PLAN_ID = @PLAN_ID))

--BEGIN

-- SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'TR-00002'

-- GOTO ABORT

--END

IF(LEN(@PD_ID) = 0)

BEGIN

EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @PD_ID out

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

END

IF(@DELIVERY_DT = '')

SET @DELIVERY_DT = NULL

IF(@PAID_DT = '')

SET @PAID_DT = NULL

IF(@INVOICE_DT = '')

SET @INVOICE_DT = NULL

IF @EXP_DELIVERY_DT = '' SET @EXP_DELIVERY_DT = NULL

/***THIEUVQ THEM KIEM TRA VA LUU THONG TIN LOAI HANG HOA THUC TE***/

--BEGIN

--CHUA CO LOAI HANG HOA THI THEM MOI

IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''

BEGIN

--NEU TON TAI THI LAY RA ID

SET @GOODSTYPE_REAL = (SELECT TOP 1 GD_RETYPE_ID FROM CM_GOODSTYPE_REAL WHERE [TYPE_NAME] = @GOODSTYPE_REAL_NAME)

--NEU CHUA CO THI THEM MOI

IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''

BEGIN

EXEC SYS_CodeMasters_Gen 'CM_GOODSTYPE_REAL', @l_GOODSTYPE_ID OUT

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

INSERT INTO CM_GOODSTYPE_REAL VALUES (@l_GOODSTYPE_ID, @l_GOODSTYPE_ID, @GOODSTYPE_REAL_NAME, '', '1',

@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),'A',@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103))

IF @@ERROR <> 0 GOTO ABORT

SET @GOODSTYPE_REAL = @l_GOODSTYPE_ID

END

END

--END

INSERT INTO TR_PO_DETAIL([PD_ID],[PO_ID],[PLAN_ID],[TRADE_ID],[REQ_DT_ID],[GOODS_ID],[DESCRIPTION],[UNIT_ID],[QUANTITY],[PRICE],[TOTAL_AMT],[IS_DELIVERY],[DELIVERY_DT],

[PAYMENT_STATUS],[AMOUNT_PAID],[PAID_DT],[INVOICENO],[NOTES], RECEIVE_BRANCH, RECEIVE_ADDR, RECEIVE_PERSON, RECEIVE_TEL,[RECORD_STATUS],[MAKER_ID],[CREATE_DT],

[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],EXP_DELIVERY_DT,GOODS_NAME,INVOICE_DT,GOODSTYPE_REAL,VAT,PRICE_VAT,CONTRACT_DT,CURRENCY,RATE,TOTAL_AMT_FN)

VALUES(@PD_ID ,@p_PO_ID ,@PLAN_ID ,@TRADE_ID,@REQ_DT_ID ,@GOODS_ID,@DESCRIPTION ,@UNIT_ID ,@QUANTITY ,@PRICE ,@TOTAL_AMT ,

@IS_DELIVERY ,CONVERT(DATETIME, @DELIVERY_DT, 103) ,@PAYMENT_STATUS ,@AMOUNT_PAID ,

CONVERT(DATETIME, @PAID_DT, 103) ,@INVOICENO ,@NOTES , @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,

@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),CONVERT(DATETIME, @EXP_DELIVERY_DT, 103),

@GOODS_NAME, CONVERT(DATETIME, @INVOICE_DT, 103),@GOODSTYPE_REAL,@VAT,@PRICE_VAT,@CONTRACT_DT,ISNULL(@CURRENCY,'VND'),ISNULL(@RATE,1) ,@TOTAL_AMT*ISNULL(@RATE,1) )

IF @@ERROR <> 0 GOTO ABORT

-- next Group_Id

FETCH NEXT FROM AssetDetail INTO @PD_ID,@PLAN_ID,@TRADE_ID,@REQ_DT_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,

@PRICE,@TOTAL_AMT,@IS_DELIVERY,@DELIVERY_DT,@PAYMENT_STATUS,@AMOUNT_PAID,@PAID_DT,

@INVOICENO,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL, @EXP_DELIVERY_DT,@GOODS_NAME,@INVOICE_DT,

@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT,@CURRENCY,@RATE,@TOTAL_AMT_FN

END

--insert payment detail

DELETE FROM TR_PO_PAYMENT WHERE PO_ID = @p_PO_ID

FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES,@CURRENCY,@RATE,@TOTAL_AMT_FN

WHILE @@FETCH_STATUS = 0

BEGIN

--EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @PAY_ID out

--IF @PAY_ID ='' OR @PAY_ID IS NULL GOTO ABORT

--PRINT @PAY_ID

IF(LEN(@PAY_ID) = 0 OR @PAY_ID IS NULL OR @PAY_ID = '')

BEGIN

EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @PAY_ID out

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

END

IF @EXPECTED_DT = ''

SET @EXPECTED_DT = NULL

--INSERT INTO TR_PO_PAYMENT([PAY_ID],[PO_ID],[PAY_PHASE],[EXP_DT],[PERCENT],[AMOUNT],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],CURRENCY,RATE,TOTAL_AMT)

--VALUES(@PAY_ID ,@p_PO_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@PERCENT ,@AMOUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),ISNULL(@CU

RRENCY,'VND'),ISNULL(@RATE,1) ,@AMOUNT*ISNULL(@RATE,1))

-------------BAODNQ 15/9/2022: Fix lại theo BVB_v2_PROD-----------

-------------Nếu @PAY_ID ko tồn tại trong TR_PO_PAYMENT, INSERT mới---------

IF(NOT EXISTS(SELECT * FROM TR_PO_PAYMENT WHERE PAY_ID = @PAY_ID))

BEGIN

INSERT INTO TR_PO_PAYMENT([PAY_ID],[PO_ID],[PAY_PHASE],[EXP_DT],[PERCENT],[AMOUNT],[NOTES],

[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],

CURRENCY,RATE,TOTAL_AMT)

VALUES(@PAY_ID ,@p_PO_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@PERCENT ,

@AMOUNT,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,

@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),

ISNULL(@CURRENCY,'VND'),ISNULL(@RATE,1) ,@AMOUNT*ISNULL(@RATE,1))

END

ELSE

-------------Nếu @PAY_ID đã tồn tại trong TR_PO_PAYMENT, UPDATE---------

BEGIN

UPDATE TR_PO_PAYMENT SET

PO_ID =@p_PO_ID,

PAY_PHASE =@PAY_PHASE,

EXP_DT =CONVERT(DATETIME, @EXPECTED_DT, 103),

[PERCENT] =@PERCENT,

AMOUNT =@TOTAL_AMT_FN / ISNULL(@RATE,1),

RECORD_STATUS =1,

AUTH_STATUS ='A',

CURRENCY =ISNULL(@CURRENCY,'VND'),

RATE =ISNULL(@RATE,1) ,

TOTAL_AMT =@AMOUNT*ISNULL(@RATE,1),

NOTES =@NOTES,

MAKER_ID =@p_MAKER_ID,

CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103)

WHERE PAY_ID =@PAY_ID --- XU LY TRONG TRUONG HOP TRUNG KEY THI CAP NHAT KY THANH TOAN 202297

END

UPDATE TR_PO_PAYMENT SET TOTAL_AMT = AMOUNT* ISNULL(RATE, 1) WHERE PAY_ID =@PAY_ID

FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES,@CURRENCY,@RATE,@TOTAL_AMT_FN

END

CLOSE AssetDetail

DEALLOCATE AssetDetail

CLOSE PaymentDetail

DEALLOCATE PaymentDetail

---INSERT NHOM QUYEN USER THONG BAO (DAO-EDIT)

DELETE FROM TL_ROLE_NOTIFICATION WHERE PO_ID=@p_PO_ID

FETCH NEXT FROM LISTROLE INTO

@NOTIFI_ID,

@TL_NAME,

@EDITOR_DT,

@EDITOR_ID,

@NOTES_ROLE

WHILE @@FETCH_STATUS = 0

BEGIN

IF(LEN(@NOTIFI_ID)=0)

EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID out

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

INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]

([NOTIFI_ID]

,[NOTIFI_CODE]

,[NOTIFI_NAME]

,[TYPE]

,[PO_ID]

,[TL_NAME]

,[BRANCH_ID]

,[RECORD_STATUS]

,[AUTH_STATUS]

,[EDITOR_ID]

,[EDIT_DT]

,[NOTES])

VALUES

(@NOTIFI_ID

,''

,''

,'PO'

,@p_PO_ID

,@TL_NAME

,@p_BRANCH_ID

,'1'

,'U'

,@EDITOR_ID

,CONVERT(DATETIME, @EDITOR_DT, 103)

,@NOTES_ROLE)

IF @@ERROR <> 0 GOTO ABORT

FETCH NEXT FROM LISTROLE INTO

@NOTIFI_ID,

@TL_NAME,

@EDITOR_DT,

@EDITOR_ID,

@NOTES_ROLE

END

CLOSE LISTROLE

DEALLOCATE LISTROLE

--- BAT DAU CAP NHAT AUTH_STATUS ='U' NEU BAM GUI PHE DUYET

IF(@p_RECORD_STATUS ='U')

BEGIN

UPDATE TR_PO_MASTER SET AUTH_STATUS ='U' WHERE PO_ID =@p_PO_ID

END

------------------------BAODNQ 1/3/2022: Insert lưu lịch sử xử lý----------------

INSERT INTO dbo.PL_PROCESS

(

REQ_ID,

PROCESS_ID,

CHECKER_ID,

APPROVE_DT,

PROCESS_DESC,

NOTES

)

VALUES

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

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

@p_MAKER_ID, -- CHECKER_ID - varchar(15)

GETDATE(), -- APPROVE_DT - datetime

N'Cập nhật phiếu gọi hàng thành công' ,

N'Cập nhật phiếu gọi hàng' -- PROCESS_DESC - nvarchar(1000)

)

COMMIT TRANSACTION

IF(@p_RECORD_STATUS ='U')

BEGIN

IF(NOT EXISTS(SELECT * FROM TR_PO_DETAIL WHERE PO_ID =@p_PO_ID))

BEGIN

SELECT '-1' as Result, @p_PO_ID PO_ID, N'PO số: '+ @p_PO_CODE +N' Vui lòng nhập thông tin hàng hóa gọi hàng' ErrorDesc

RETURN '-1'

END

IF(NOT EXISTS(SELECT * FROM TR_PO_PAYMENT WHERE PO_ID =@p_PO_ID))

BEGIN

SELECT '-1' as Result, @p_PO_ID PO_ID, N'PO số: '+ @p_PO_CODE +N' Vui lòng nhập thông tin lịch thanh toán' ErrorDesc

RETURN '-1'

END

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

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

@p_MAKER_ID, -- CHECKER_ID - varchar(15)

GETDATE(), -- APPROVE_DT - datetime

N'Nhân viên gửi phê duyệt phiếu thành công' ,N'Gửi phê duyệt')

SELECT '2' as Result, @p_PO_ID PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp có thẩm quyền phê duyệt' ErrorDesc

RETURN '2'

END

ELSE

BEGIN

SELECT '0' as Result, @p_PO_ID PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được cập nhật dữ liệu thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc

RETURN '0'

END

ABORT:

BEGIN

CLOSE AssetDetail

DEALLOCATE AssetDetail

CLOSE PaymentDetail

DEALLOCATE PaymentDetail

CLOSE LISTROLE

DEALLOCATE LISTROLE

ROLLBACK TRANSACTION

SELECT '-1' AS RESULT

RETURN '-1'

End

--SELECT * FROM TR_CONTRACT_DT

--SELECT * FROM TR_CONTRACT_PAYMENT

--SELECT * FROM TR_CONTRACT