EOFFICE - GD4

WITH RankedTrades AS (
SELECT
B.YEAR,
B.DEPT_ID,
B.BRANCH_ID,
DP.DEP_CODE,
DP.DEP_NAME,
G.GD_CODE,
A.AMT_APP,
A.AMT_TF,
A.AMT_RECEIVE_TF,
A.TRADE_ID,
B.PLAN_ID,
ROW_NUMBER() OVER (
PARTITION BY A.GOODS_ID, B.BRANCH_ID, B.DEPT_ID
ORDER BY A.TRADE_ID DESC
) AS RN
FROM dbo.PL_TRADEDETAIL A
INNER JOIN dbo.PL_MASTER B ON B.PLAN_ID = A.PLAN_ID
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = B.DEPT_ID
LEFT JOIN CM_GOODS G ON G.GD_ID = A.GOODS_ID
WHERE B.YEAR = '2025'
AND EXISTS (
SELECT 1
FROM dbo.PL_TRADEDETAIL C
INNER JOIN dbo.PL_MASTER D ON D.PLAN_ID = C.PLAN_ID
WHERE C.GOODS_ID = A.GOODS_ID
AND D.BRANCH_ID = B.BRANCH_ID
AND D.DEPT_ID = B.DEPT_ID
AND D.YEAR = '2025'
GROUP BY C.GOODS_ID, D.BRANCH_ID, D.DEPT_ID
HAVING COUNT >= 2
)
)
DELETE A
FROM dbo.PL_TRADEDETAIL A
INNER JOIN RankedTrades RT ON A.TRADE_ID = RT.TRADE_ID
WHERE RT.RN <> 1;

DECLARE @REQ_ID VARCHAR
SELECT TOP 1 @REQ_ID = IMP_LOG_CODE FROM dbo.PL_IMPORT_LOG ORDER BY IMP_LOG_ID DESC
SELECT * FROM dbo.PL_IMPORT_LOG WHERE IMP_YEAR = '2025' AND IMP_LOG_CODE = @REQ_ID ORDER BY IMP_LOG_ID

UPDATE dbo.PL_TRADEDETAIL
SET PLAN_ID = 'X' + LEFT - 14)
WHERE LEN > 14 AND PLAN_ID IS NOT NULL;

WITH RankedTrades AS (
SELECT
B.YEAR,
B.DEPT_ID,
B.BRANCH_ID,
DP.DEP_CODE,
DP.DEP_NAME,
G.GD_CODE,
A.AMT_APP,
A.AMT_TF,
A.AMT_RECEIVE_TF,
A.TRADE_ID,
B.PLAN_ID,
ROW_NUMBER() OVER (
PARTITION BY A.GOODS_ID, B.BRANCH_ID, B.DEPT_ID
ORDER BY A.TRADE_ID DESC
) AS RN
FROM dbo.PL_TRADEDETAIL A
INNER JOIN dbo.PL_MASTER B ON B.PLAN_ID = A.PLAN_ID
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = B.DEPT_ID
LEFT JOIN CM_GOODS G ON G.GD_ID = A.GOODS_ID
WHERE B.YEAR = '2025'
AND EXISTS (
SELECT 1
FROM dbo.PL_TRADEDETAIL C
INNER JOIN dbo.PL_MASTER D ON D.PLAN_ID = C.PLAN_ID
WHERE C.GOODS_ID = A.GOODS_ID
AND D.BRANCH_ID = B.BRANCH_ID
AND D.DEPT_ID = B.DEPT_ID
AND D.YEAR = '2025'
GROUP BY C.GOODS_ID, D.BRANCH_ID, D.DEPT_ID
HAVING COUNT >= 2
)
)
SELECT GD_CODE ,DEP_CODE,RN ,A.*
FROM dbo.PL_TRADEDETAIL A
INNER JOIN RankedTrades RT ON A.TRADE_ID = RT.TRADE_ID
ORDER BY DEP_CODE, GD_CODE
--04042025_secretkey

DECLARE @REQ_ID VARCHAR
SELECT @REQ_ID = REQ_ID FROM TR_REQUEST_DOC WHERE REQ_CODE = 'PUR/2024/002916'
INSERT dbo.PL_PROCESS(REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES)
VALUES
'GDK_PYC', -- PROCESS_ID - varchar(10)
'tunt', -- CHECKER_ID - varchar(15)
'2025-12-12 09:40:35.877', -- APPROVE_DT - datetime
N'Phê duyệt', -- PROCESS_DESC - nvarchar(max)
N'Phó Tổng Giám đốc khối Hỗ Trợ đã phê duyệt' -- NOTES - nvarchar(max)
)

DECLARE @ASSET_CODES NVARCHAR = 'PYC-XE/2024/001105, PYC-XE/2025/000348, PYC-XE/2025/000160, PYC-XE/2025/000236, PYC-XE/2025/000426, PYC-XE/2024/001282, PYC-XE/2025/000010, PYC-XE/2024/001087, PYC-XE/2024/000911, PYC-XE/2025/000060, PYC-XE/2024/000948, PYC-XE/2025/000088, PYC-XE/2025/000154, PYC-XE/2025/000281, PYC-XE/2025/000285, PYC-XE/2025/000299, PYC-XE/2025/000239, PYC-XE/2025/000266, PYC-XE/2025/000341, PYC-XE/2025/000349, PYC-XE/2025/000062, PYC-XE/2025/000063, PYC-XE/2025/000064, PYC-XE/2025/000065, PYC-XE/2025/000066'
UPDATE dbo.TR_REQUEST_CAR SET BRANCH_CREATE = 'DV0001' WHERE REQ_ID IN (SELECT TRIM AS ASSET_CODE
FROM dbo.wsiSplit(@ASSET_CODES, ','))
UPDATE dbo.PL_REQUEST_PROCESS SET BRANCH_ID = 'DV0001' WHERE REQ_ID IN (SELECT TRIM AS ASSET_CODE
FROM dbo.wsiSplit(@ASSET_CODES, ',')) AND PROCESS_ID = 'CV_APP'
--03062025_SECRETKEY

DECLARE @ASSET_CODES NVARCHAR = 'PYC-XE/2024/001105, PYC-XE/2025/000348, PYC-XE/2025/000160, PYC-XE/2025/000236, PYC-XE/2025/000426, PYC-XE/2024/001282, PYC-XE/2025/000010, PYC-XE/2024/001087, PYC-XE/2024/000911, PYC-XE/2025/000060, PYC-XE/2024/000948, PYC-XE/2025/000088, PYC-XE/2025/000154, PYC-XE/2025/000281, PYC-XE/2025/000285, PYC-XE/2025/000299, PYC-XE/2025/000239, PYC-XE/2025/000266, PYC-XE/2025/000341, PYC-XE/2025/000349, PYC-XE/2025/000062, PYC-XE/2025/000063, PYC-XE/2025/000064, PYC-XE/2025/000065, PYC-XE/2025/000066'
SELECT B.REQ_ID AS ASSET_CODE
FROM dbo.wsiSplit(@ASSET_CODES, ',') A LEFT JOIN TR_REQUEST_CAR B ON trim(A.value) = B.REQ_CODE

UPDATE dbo.PL_REQUEST_PROCESS SET BRANCH_ID = 'DV0001' WHERE REQ_ID IN (SELECT B.REQ_ID AS ASSET_CODE
FROM dbo.wsiSplit(@ASSET_CODES, ',') A LEFT JOIN TR_REQUEST_CAR B ON trim(A.value) = B.REQ_CODE) AND PROCESS_ID = 'CV_APP'
--07062025_SECRETKEY
select * from tr_request_car where req_code = 'PYC-XE/2024/001105'
SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = 'TRCS00000006323'