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