Feature #33
IMPORT DATA
0%
History
#1 Updated by Luc Tran Van almost 5 years ago
DELETE FROM PL_REQUEST_PROCESS WHERE REQ_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT)
DELETE FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT)
DELETE FROM PL_PROCESS WHERE REQ_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT)
DELETE FROM TR_REQ_ADVANCE_PAYMENT
DELETE FROM TR_REQ_ADVANCE_DT
DELETE FROM TR_REQ_ADVANCE_CUS
DELETE FROM TR_REQ_PAY_BUDGET
DELETE FROM TR_REQ_PAY_ENTRIES
DELETE FROM TR_REQ_PAY_CAT
DELETE FROM TR_REQ_PAY_INVOICE
DELETE FROM TR_REQ_PAY_METHOD
DELETE FROM TR_REQ_PAY_SCHEDULE
DELETE FROM TR_REQ_PAYMENT
DELETE FROM TR_REQ_PAY_SERVICE
DELETE FROM TR_REQ_PAYMENT_DT
DELETE FROM TR_REQ_PAY_PERIOD
DELETE FROM TR_REQ_PAY_INVOICE_UPD
DELETE FROM TR_REQ_PAY_INVOICE_UPD_DT
DELETE FROM TR_REQ_PAY_INVOICE_LOG
#2 Updated by Truong Nguyen Vu almost 5 years ago
UPDATE PL_TRADEDETAIL SET AMT_EXE =0,AMT_ETM=0,AMT_TF=0,AMT_RECEIVE_TF=0
DELETE FROM TR_REQUEST_DOC
DELETE FROM TR_REQUEST_DOC_DT
DELETE FROM TR_REQUEST_COSTCENTER
DELETE FROM PL_REQUEST_DOC
DELETE FROM PL_REQUEST_DOC_DT
DELETE FROM PL_REQUEST_COSTCENTER
DELETE FROM PL_REQUEST_TRANSFER
#3 Updated by Truong Nguyen Vu almost 5 years ago
UPDATE PL_TRADEDETAIL SET AMT_EXE =0,AMT_ETM=0,AMT_TF=0,AMT_RECEIVE_TF=0
DELETE FROM TR_REQUEST_DOC
DELETE FROM TR_REQUEST_DOC_DT
DELETE FROM TR_REQUEST_COSTCENTER
DELETE FROM PL_REQUEST_DOC
DELETE FROM PL_REQUEST_DOC_DT
DELETE FROM PL_REQUEST_COSTCENTER
DELETE FROM PL_REQUEST_TRANSFER
DELETE FROM TR_PO_MASTER
DELETE FROM TR_PO_DETAIL
DELETE FROM TR_PO_PAYMENT
#4 Updated by Luc Tran Van almost 5 years ago
- File CM_ACCOUNT_PAY.txt CM_ACCOUNT_PAY.txt added
#5 Updated by Luc Tran Van almost 5 years ago
INSERT INTO TL_SYSROLE_LIMIT VALUES ('LM000090','KT',50000000,'','admin','U','',getdate(),null,null,'DCNS',null,null)
#6 Updated by Luc Tran Van almost 5 years ago
#7 Updated by Luc Tran Van almost 5 years ago
ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR
AS
BEGIN TRANSACTION;
DECLARE @l_SUP_ID VARCHAR
DECLARE @p_PO_CODE VARCHAR,
@p_BRANCH_ID VARCHAR,
@p_SUP_ID VARCHAR,
@p_TOTAL_AMT DECIMAL,
@p_MAKER_ID VARCHAR,
@p_TR_REQ_CODE VARCHAR,
@p_SUP_NAME NVARCHAR,
@p_SUP_ADDR NVARCHAR,
@p_BRANCH_RE VARCHAR,
@p_PO_NAME NVARCHAR;
--- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020
DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR)
INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID
WHILE FROM @TABLE_NCC)>0)
BEGIN
SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
--insert master
DECLARE @l_PO_ID VARCHAR;
EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT;
IF @l_PO_ID = '' OR @l_PO_ID IS NULL
GOTO ABORT;
SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID
--SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID
SET @p_SUP_ID =@l_SUP_ID
SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)
--TU PHAT SINH SO PO
DECLARE @ldate INT = (SELECT YEAR(GETDATE()) );
EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT;
IF @p_PO_CODE = ''
OR @p_PO_CODE IS NULL
GOTO ABORT;INSERT INTO TR_PO_MASTER
(
[PO_TYPE],
[PO_ID],
[PO_CODE],
[PO_NAME],
[CONTRACT_ID],
[SUP_ID],
[SUP_NAME],
[SUP_ADDR],
[INPUT_DT],
[PAYMENT_DT],
[TOTAL_AMT],
[REQ_DOC_ID],
[DELIVERY_DT],
[PAYAPP_DT],
[NOTES],
[RECORD_STATUS],
[MAKER_ID],
[CREATE_DT],
[AUTH_STATUS],
[CHECKER_ID],
[APPROVE_DT],
BRANCH_ID
)
VALUES
(1, l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(),
NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL,
NULL, '', '1', @p_MAKER_ID, GETDATE(),
'E', NULL, NULL, @p_BRANCH_ID);
IF @Error <> 0
GOTO ABORT;
PRINT 'INSERT MASTER SUCCESS';
INSERT INTO dbo.TR_PO_MASTER_TEMP
(
PO_ID,
TR_REQ_ID,
TR_REQ_CODE
)
VALUES
( @l_PO_ID, -- PO_ID - varchar(15)
@p_TR_REQ_ID, -- TR_REQ_ID - varchar(20)
@p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20)
);
DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100)
DECLARE lstData CURSOR FOR
SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_IDOPEN lstData
FETCH NEXT FROM lstData INTO HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
WHILE @FETCH_STATUS = 0
BEGIN
SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID
DECLARE @l_PD_ID VARCHAR(15)
EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out
IF @l_PD_ID='' OR @l_PD_ID IS NULL GOTO ABORT INSERT INTO dbo.TR_PO_DETAIL
(
PD_ID,
PO_ID,
PLAN_ID,
TRADE_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
)
VALUES
( @l_PD_ID, -- PD_ID - varchar(15)
@l_PO_ID, -- PO_ID - varchar(15)
'', -- PLAN_ID - varchar(15)
'', -- TRADE_ID - varchar(15)
@HANGHOA_ID, -- GOODS_ID - varchar(15)
N'', -- DESCRIPTION - nvarchar(500)
'', -- UNIT_ID - varchar(15)
@QUANTITY, -- QUANTITY - decimal(18, 0)
ROUND(@PRICE/1.1,0), -- PRICE - decimal(18, 0)
@TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0)
'', -- IS_DELIVERY - varchar(1)
NULL, -- DELIVERY_DT - datetime
'CTT', -- PAYMENT_STATUS - varchar(4)
NULL, -- AMOUNT_PAID - decimal(18, 0)
NULL, -- PAID_DT - datetime
'', -- INVOICENO - varchar(1000)
N'', -- NOTES - nvarchar(1000)
@p_BRANCH_RE, -- RECEIVE_BRANCH - varchar(15)
N'', -- RECEIVE_ADDR - nvarchar(1000)
N'', -- RECEIVE_PERSON - nvarchar(500)
'', -- RECEIVE_TEL - varchar(100)
'', -- RECORD_STATUS - varchar(1)
@p_MAKER_ID, -- MAKER_ID - varchar(15)
GETDATE(), -- CREATE_DT - datetime
'', -- AUTH_STATUS - varchar(50)
'', -- CHECKER_ID - varchar(15)
NULL, -- APPROVE_DT - datetime
NULL, -- EXP_DELIVERY_DT - datetime
@HH_NAME, -- GOODS_NAME - nvarchar(500)
NULL, -- INVOICE_DT - datetime
@HH_TYPE, -- GOODSTYPE_REAL - varchar(15)
10, -- VAT - decimal(18, 2)
--@TOTAL_AMT - (ROUND(@PRICE/1.1,0) *@QUANTITY), -- PRICE_VAT - decimal(18, 0)
@PRICE ROUND(@PRICE/1.1,0),
'' - CONTRACT_DT - varchar(15)
)FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
END
CLOSE lstData;
DEALLOCATE lstData;SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)DECLARE @l_PAY_ID VARCHAR(15)
EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
INSERT INTO dbo.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
)
VALUES
( @l_PAY_ID, -- PAY_ID - varchar(15)
@l_PO_ID, -- PO_ID - varchar(15)
'1', -- PAY_PHASE - varchar(20)
GETDATE(), -- EXP_DT - datetime
50, -- PERCENT - decimal(18, 0)
@TOTAL_AMT / 2 , -- AMOUNT - decimal(18, 0)
N'', -- NOTES - nvarchar(1000)
'1', -- RECORD_STATUS - varchar(1)
@p_MAKER_ID, -- MAKER_ID - varchar(15)
GETDATE(), -- CREATE_DT - datetime
'U', -- AUTH_STATUS - varchar(50)
'', -- CHECKER_ID - varchar(15)
NULL -- APPROVE_DT - datetime
)
EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
INSERT INTO dbo.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
)
VALUES
( @l_PAY_ID, -- PAY_ID - varchar(15)
@l_PO_ID, -- PO_ID - varchar(15)
'2', -- PAY_PHASE - varchar(20)
GETDATE(), -- EXP_DT - datetime
50, -- PERCENT - decimal(18, 0)
@TOTAL_AMT / 2 , -- AMOUNT - decimal(18, 0)
N'', -- NOTES - nvarchar(1000)
'1', -- RECORD_STATUS - varchar(1)
@p_MAKER_ID, -- MAKER_ID - varchar(15)
GETDATE(), -- CREATE_DT - datetime
'E', -- AUTH_STATUS - varchar(50)
'', -- CHECKER_ID - varchar(15)
NULL -- APPROVE_DT - datetime
)IF(@TOTAL_AMT <=5000000)
BEGIN
UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID
END
--XOA DI NHA CUNG CAP
DELETE FROM @TABLE_NCC WHERE SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
--- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS
END
COMMIT TRANSACTION;
RETURN 1;
ABORT:
BEGIN
ROLLBACK TRANSACTION;
RETURN 1;
END;
ABORT1:
BEGIN
CLOSE lstData;
DEALLOCATE lstData;
ROLLBACK TRANSACTION;
RETURN 1;
END;#8 Updated by Luc Tran Van almost 5 years ago
- File cap nhat ham sinh PO.txt cap nhat ham sinh PO.txt added
#9 Updated by Luc Tran Van almost 5 years ago
- File CM ACCOUNT INS.txt CM ACCOUNT INS.txt added
#10 Updated by Luc Tran Van over 4 years ago
- Status changed from New to Closed