|
1
|
-- khai báo table tạm chưa data
|
|
2
|
DECLARE @tbl_kpi_temp TABLE (KPI_TYPE VARCHAR(50), REQ_TYPE VARCHAR(50), REQ_STATUS VARCHAR(50), REQ_ID VARCHAR(50), REQ_REASON NVARCHAR(MAX), CORE_NOTE NVARCHAR(250), AUTH_STATUS_KT VARCHAR(50), TRANSFER_DT DATETIME, MAKER_ID_KT VARCHAR(50), CREATE_DT_KT DATETIME, CHECKER_ID_KT VARCHAR(50), APPROVE_DT_KT DATETIME)
|
|
3
|
-- khai báo biến cần dùng
|
|
4
|
DECLARE @KPI_TYPE VARCHAR(10), @REQ_TYPE VARCHAR(20), @REQ_STATUS VARCHAR(20), @REQ_REASON NVARCHAR(MAX), @CORE_NOTE NVARCHAR(200), @AUTH_STATUS_KT VARCHAR(5), @TRANSFER_DT DATETIME, @MAKER_ID_KT VARCHAR(20), @CREATE_DT_KT DATETIME, @CHECKER_ID_KT VARCHAR(20), @APPROVE_DT_KT DATETIME,
|
|
5
|
@REQ_ID VARCHAR(20), @PROCESS_ID VARCHAR(20), @CHECKER_ID VARCHAR(20), @APPROVE_DT NVARCHAR(MAX), @PROCESS_DESC NVARCHAR(200), @NOTES NVARCHAR(MAX), @ID INT
|
|
6
|
-- Khai báo cursor
|
|
7
|
DECLARE XmlData CURSOR LOCAL FOR
|
|
8
|
select * from PL_PROCESS
|
|
9
|
where 1=1
|
|
10
|
and (LEFT(REQ_ID, 4) = 'TADY' OR LEFT(REQ_ID, 4) = 'TRPY')-- lấy phiếu thanh toán và tạm ứng
|
|
11
|
and ( ( CONVERT(DATE, APPROVE_DT) < '2022-12-31 23:55:51.610'
|
|
12
|
AND CONVERT(DATE, APPROVE_DT) > '2022-12-01 00:55:51.610'
|
|
13
|
)
|
|
14
|
)-- cac phieu thang 10
|
|
15
|
and CHECKER_ID in ( select TLNANME from TL_USER where DEP_ID = 'DEP000000000022') -- process thuoc phong KT
|
|
16
|
and PROCESS_ID IN ( 'TRANSF', 'SEND', 'REJECT', 'APP', 'SUG')-- trang thai phieu
|
|
17
|
order by CONVERT(DECIMAL(18,0),RIGHT(REQ_ID, 10)), ID
|
|
18
|
|
|
19
|
-- Mở cursor
|
|
20
|
OPEN XmlData;
|
|
21
|
BEGIN TRANSACTION
|
|
22
|
--Insert XmlData
|
|
23
|
FETCH NEXT FROM XmlData INTO @ID, @REQ_ID, @PROCESS_ID, @CHECKER_ID, @APPROVE_DT, @PROCESS_DESC, @NOTES
|
|
24
|
-- fetch_status=0 --> cursor đọc dữ liệu thành công
|
|
25
|
WHILE @@fetch_status=0
|
|
26
|
BEGIN
|
|
27
|
IF(LEFT(@REQ_ID, 4) = 'TADY')
|
|
28
|
BEGIN
|
|
29
|
SET @KPI_TYPE = 'ADV_PAY'
|
|
30
|
SET @REQ_TYPE = 'TR_ADVANCE_PAYMENT'
|
|
31
|
SET @REQ_REASON = (SELECT REQ_REASON FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
|
|
32
|
SET @CORE_NOTE = (SELECT CONFIRM_NOTES FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
|
|
33
|
END
|
|
34
|
ELSE
|
|
35
|
BEGIN
|
|
36
|
SET @KPI_TYPE = 'PAY'
|
|
37
|
SET @REQ_TYPE = 'TR_REQ_PAYMENT'
|
|
38
|
SET @REQ_REASON = (SELECT REQ_REASON FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
|
|
39
|
SET @CORE_NOTE = (SELECT CONFIRM_NOTE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
|
|
40
|
END
|
|
41
|
|
|
42
|
|
|
43
|
IF(@PROCESS_ID = 'TRANSF')
|
|
44
|
BEGIN
|
|
45
|
DELETE FROM @tbl_kpi_temp WHERE REQ_ID = @REQ_ID AND REQ_STATUS = 'TRANSF' AND ISNULL(MAKER_ID_KT, '') = ''
|
|
46
|
INSERT INTO @tbl_kpi_temp( KPI_TYPE, REQ_TYPE, REQ_STATUS, REQ_ID, REQ_REASON, CORE_NOTE, AUTH_STATUS_KT, TRANSFER_DT, MAKER_ID_KT, CREATE_DT_KT, CHECKER_ID_KT, APPROVE_DT_KT)
|
|
47
|
VALUES ( @KPI_TYPE, @REQ_TYPE, 'TRANSF', @REQ_ID, @REQ_REASON, @CORE_NOTE, 'U', @APPROVE_DT, NULL, NULL, @CHECKER_ID, NULL )
|
|
48
|
END
|
|
49
|
--GDV
|
|
50
|
ELSE IF(@PROCESS_ID = 'SEND')
|
|
51
|
BEGIN
|
|
52
|
UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SEND', AUTH_STATUS_KT = 'U' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
|
|
53
|
END
|
|
54
|
ELSE IF(@PROCESS_ID = 'REJECT' AND @NOTES = N'GDV đề xuất từ chối phiếu')
|
|
55
|
BEGIN
|
|
56
|
UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SUG', AUTH_STATUS_KT = 'S' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
|
|
57
|
END
|
|
58
|
ELSE IF(@PROCESS_ID = 'SUG')
|
|
59
|
BEGIN
|
|
60
|
UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SUG', AUTH_STATUS_KT = 'S' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
|
|
61
|
END
|
|
62
|
--KSV
|
|
63
|
ELSE IF(@PROCESS_ID = 'REJECT' AND @NOTES <> N'GDV đề xuất từ chối phiếu')
|
|
64
|
BEGIN
|
|
65
|
UPDATE @tbl_kpi_temp SET CHECKER_ID_KT = @CHECKER_ID, APPROVE_DT_KT = @APPROVE_DT, REQ_STATUS = 'REJECT', AUTH_STATUS_KT = 'R' WHERE REQ_ID = @REQ_ID AND ISNULL(APPROVE_DT_KT, '') = ''
|
|
66
|
END
|
|
67
|
ELSE IF(@PROCESS_ID = 'APP')
|
|
68
|
BEGIN
|
|
69
|
UPDATE @tbl_kpi_temp SET CHECKER_ID_KT = @CHECKER_ID, APPROVE_DT_KT = @APPROVE_DT, REQ_STATUS = 'APP', AUTH_STATUS_KT = 'A' WHERE REQ_ID = @REQ_ID AND ISNULL(APPROVE_DT_KT, '') = ''
|
|
70
|
END
|
|
71
|
|
|
72
|
IF @@error<>0 GOTO ABORT;
|
|
73
|
FETCH NEXT FROM XmlData INTO @ID, @REQ_ID, @PROCESS_ID, @CHECKER_ID, @APPROVE_DT, @PROCESS_DESC, @NOTES
|
|
74
|
END;
|
|
75
|
CLOSE XmlData;
|
|
76
|
DEALLOCATE XmlData;
|
|
77
|
|
|
78
|
COMMIT TRANSACTION
|
|
79
|
/*
|
|
80
|
SELECT * FROM @tbl_kpi_temp
|
|
81
|
ORDER BY REQ_ID, TRANSFER_DT
|
|
82
|
*/
|
|
83
|
|
|
84
|
SELECT H.*
|
|
85
|
FROM
|
|
86
|
(
|
|
87
|
SELECT A.REQ_PAY_CODE AS MA_SO_PHIEU, B.MAKER_ID_KT AS GDV, B.CHECKER_ID_KT AS KSV, A.REQ_REASON AS NOI_DUNG,
|
|
88
|
CONVERT(VARCHAR,MONTH(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,DAY(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,YEAR(B.TRANSFER_DT),2) AS NGAY_NHAN_PHIEU,
|
|
89
|
CONVERT(VARCHAR,MONTH(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.CREATE_DT_KT),2) AS NGAY_GDV_GUI_DUYET,
|
|
90
|
CONVERT(VARCHAR,MONTH(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.APPROVE_DT_KT),2) AS NGAY_KSV_DUYET,
|
|
91
|
CASE WHEN B.AUTH_STATUS_KT = 'U' THEN N'KSV điều phối'
|
|
92
|
WHEN B.AUTH_STATUS_KT = 'P' THEN N'GDV gửi duyệt'
|
|
93
|
WHEN B.AUTH_STATUS_KT = 'S' THEN N'GDV đề xuất từ chối phiếu'
|
|
94
|
WHEN B.AUTH_STATUS_KT = 'R' THEN N'KSV từ chối phiếu'
|
|
95
|
WHEN B.AUTH_STATUS_KT = 'A' THEN N'KSV duyệt phiếu'
|
|
96
|
END AS TRANG_THAI_PHIEU
|
|
97
|
FROM @tbl_kpi_temp B
|
|
98
|
JOIN TR_REQ_PAYMENT A ON B.REQ_ID = A.REQ_PAY_ID
|
|
99
|
WHERE 1=1
|
|
100
|
|
|
101
|
UNION
|
|
102
|
|
|
103
|
SELECT A.REQ_PAY_CODE AS MA_SO_PHIEU, B.MAKER_ID_KT AS GDV, B.CHECKER_ID_KT AS KSV, A.REQ_REASON AS NOI_DUNG,
|
|
104
|
CONVERT(VARCHAR,MONTH(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,DAY(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,YEAR(B.TRANSFER_DT),2) AS NGAY_NHAN_PHIEU,
|
|
105
|
CONVERT(VARCHAR,MONTH(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.CREATE_DT_KT),2) AS NGAY_GDV_GUI_DUYET,
|
|
106
|
CONVERT(VARCHAR,MONTH(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.APPROVE_DT_KT),2) AS NGAY_KSV_DUYET,
|
|
107
|
CASE WHEN B.AUTH_STATUS_KT = 'U' THEN N'KSV điều phối'
|
|
108
|
WHEN B.AUTH_STATUS_KT = 'P' THEN N'GDV gửi duyệt'
|
|
109
|
WHEN B.AUTH_STATUS_KT = 'S' THEN N'GDV đề xuất từ chối phiếu'
|
|
110
|
WHEN B.AUTH_STATUS_KT = 'R' THEN N'KSV từ chối phiếu'
|
|
111
|
WHEN B.AUTH_STATUS_KT = 'A' THEN N'KSV duyệt phiếu'
|
|
112
|
END AS TRANG_THAI_PHIEU
|
|
113
|
FROM @tbl_kpi_temp B
|
|
114
|
JOIN TR_REQ_ADVANCE_PAYMENT A ON B.REQ_ID = A.REQ_PAY_ID
|
|
115
|
WHERE 1=1
|
|
116
|
) H
|
|
117
|
ORDER BY H.MA_SO_PHIEU
|
|
118
|
|
|
119
|
ABORT:
|
|
120
|
BEGIN
|
|
121
|
print 1
|
|
122
|
END
|