|
1
|
ALTER PROCEDURE dbo.PL_MASTER_Search_New_V2
|
|
2
|
@P_PLAN_CODE nvarchar(15) = NULL,
|
|
3
|
@p_PLAN_NAME NVARCHAR(200) = NULL,
|
|
4
|
@P_YEAR NVARCHAR(4) = NULL,
|
|
5
|
@P_COST_ID nvarchar(15) = NULL,
|
|
6
|
@P_BRANCH_ID nvarchar(15) = NULL,
|
|
7
|
@P_BRANCHLOGIN NVARCHAR(15) = NULL,
|
|
8
|
@P_PLAN_TYPE_ID NVARCHAR(15) = NULL,
|
|
9
|
@p_DEP_ID NVARCHAR(15) = NULL,
|
|
10
|
@p_GOOD_NAME NVARCHAR(200) = NULL,
|
|
11
|
@p_USER_LOGIN VARCHAR(200) = NULL,
|
|
12
|
@P_TOP INT = 10,
|
|
13
|
@P_LEVEL varchar(10) = NULL
|
|
14
|
AS
|
|
15
|
BEGIN -- PAGING
|
|
16
|
SET @P_TOP = NULL
|
|
17
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
18
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@P_BRANCHLOGIN)
|
|
19
|
|
|
20
|
DECLARE @BRANCH_TYPE VARCHAR(15),@DEPLOGIN VARCHAR(20),@ROLE_USER VARCHAR(15),@IS_ALL BIT
|
|
21
|
|
|
22
|
|
|
23
|
SET @IS_ALL=0
|
|
24
|
DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))
|
|
25
|
|
|
26
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@P_BRANCHLOGIN)
|
|
27
|
SET @DEPLOGIN =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)
|
|
28
|
|
|
29
|
|
|
30
|
|
|
31
|
IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@P_BRANCHLOGIN AND DEP_ID=@DEPLOGIN) OR EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='HANHCHINH' AND BRANCH_ID=@P_BRANCHLOGIN AND DEP_ID=@DEPLOGIN))
|
|
32
|
SET @IS_ALL=1
|
|
33
|
SET @ROLE_USER = (SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)
|
|
34
|
|
|
35
|
|
|
36
|
IF(@IS_ALL=1 OR @ROLE_USER='TGD')
|
|
37
|
BEGIN
|
|
38
|
INSERT INTO @DVDM_ID
|
|
39
|
SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
|
|
40
|
END
|
|
41
|
ELSE
|
|
42
|
BEGIN
|
|
43
|
INSERT INTO @DVDM_ID
|
|
44
|
SELECT PC.DVDM_ID FROM
|
|
45
|
dbo.PL_COSTCENTER PC
|
|
46
|
LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
|
|
47
|
WHERE PT.DEP_ID=@DEPLOGIN AND PT.BRANCH_ID=@P_BRANCHLOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
|
|
48
|
END
|
|
49
|
IF (@P_TOP = 0 OR @P_TOP IS NULL)
|
|
50
|
BEGIN
|
|
51
|
-- PAGING BEGIN
|
|
52
|
SELECT L.[YEAR], ALLC.CONTENT AS GOOD_DES, L.BRANCH_ID, L.DEPT_ID, CG.GD_TYPE_ID, D.BRANCH_NAME AS PGD, E.DEP_NAME,D.BRANCH_NAME,D.BRANCH_CODE,
|
|
53
|
--------------BAODNQ 28/2/2022: Lấy thêm cột-----------
|
|
54
|
L.PLAN_NAME, L.PLAN_CODE, L.PLAN_ID,
|
|
55
|
----------------------------------------------
|
|
56
|
SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_USE,
|
|
57
|
SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_ETM,
|
|
58
|
SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,
|
|
59
|
SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,
|
|
60
|
SUM(ISNULL(DT.AMT_EXE,0)) AS AMT_EXE,
|
|
61
|
SUM(ISNULL(DT.AMT_ETM,0) + ISNULL(DT.BUY_TMP,0)) AS AMT_ETM,
|
|
62
|
SUM(ISNULL(DT.AMT_TF,0) + ISNULL(DT.TF_TMP,0)) AS AMT_TRANSFER,
|
|
63
|
SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
|
|
64
|
SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)-ISNULL(DT.TF_TMP,0)) AS AMT_REMAIN,
|
|
65
|
SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)-ISNULL(DT.TF_TMP,0)-ISNULL(DT.BUY_TMP,0)) AS AMT_REMAIN_ETM,
|
|
66
|
SUM(ISNULL(DT.TF_TMP,0)) AS TF_TMP, SUM(ISNULL(DT.BUY_TMP,0)) AS BUY_TMP
|
|
67
|
-- SELECT END
|
|
68
|
FROM
|
|
69
|
(
|
|
70
|
SELECT PLAN_ID,GOODS_ID,
|
|
71
|
QUANTITY,QUANTITY_EXE,QUANTITY_ETM,
|
|
72
|
AMT_APP,AMT_TF,AMT_RECEIVE_TF,AMT_EXE,AMT_ETM,
|
|
73
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
74
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
75
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
76
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
77
|
AND DDT.FR_TRADE_ID = PL.TRADE_ID) AS TF_TMP,
|
|
78
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
79
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
80
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
81
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
82
|
AND DDT.TRADE_ID = PL.TRADE_ID) AS BUY_TMP
|
|
83
|
FROM PL_TRADEDETAIL PL
|
|
84
|
) DT
|
|
85
|
LEFT JOIN PL_MASTER L ON L.PLAN_ID = DT.PLAN_ID
|
|
86
|
LEFT JOIN CM_GOODS CG on CG.GD_ID=DT.GOODS_ID
|
|
87
|
LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = L.BRANCH_ID
|
|
88
|
LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
|
|
89
|
LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
|
|
90
|
LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
|
|
91
|
LEFT JOIN CM_ALLCODE ALLC ON ALLC.CDNAME = 'HMNS_TYPE' AND ALLC.CDTYPE = 'HMNS_TYPE' AND ALLC.CDVAL = CG.GD_TYPE_ID
|
|
92
|
WHERE 1=1
|
|
93
|
AND (CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
|
|
94
|
AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
|
|
95
|
AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
|
|
96
|
AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
|
|
97
|
AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
|
|
98
|
AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
|
|
99
|
AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
|
|
100
|
AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
|
|
101
|
AND (
|
|
102
|
( @BRANCH_TYPE='HS' AND
|
|
103
|
(
|
|
104
|
(L.BRANCH_ID=@P_BRANCHLOGIN AND L.DEPT_ID=@DEPLOGIN )
|
|
105
|
OR( L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
|
|
106
|
OR L.COST_ID IS NULL OR L.COST_ID =''
|
|
107
|
)
|
|
108
|
) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN)
|
|
109
|
)
|
|
110
|
AND L.RECORD_STATUS = '1'
|
|
111
|
GROUP BY L.[YEAR], ALLC.CONTENT, L.BRANCH_ID, L.DEPT_ID, CG.GD_TYPE_ID, D.BRANCH_NAME, E.DEP_NAME,D.BRANCH_NAME,D.BRANCH_CODE,
|
|
112
|
--------------BAODNQ 28/2/2022: Thêm điều kiện group by-----------
|
|
113
|
L.PLAN_NAME, L.PLAN_CODE, L.PLAN_ID
|
|
114
|
ORDER BY L.BRANCH_ID, L.DEPT_ID
|
|
115
|
-- PAGING END
|
|
116
|
|
|
117
|
END
|
|
118
|
ELSE
|
|
119
|
BEGIN
|
|
120
|
|
|
121
|
IF (@P_TOP <> 0 AND @P_TOP IS NOT NULL )
|
|
122
|
BEGIN
|
|
123
|
-- PAGING BEGIN
|
|
124
|
SELECT L.[YEAR], ALLC.CONTENT AS GOOD_DES, L.BRANCH_ID, L.DEPT_ID, CG.GD_TYPE_ID, D.BRANCH_NAME AS PGD, E.DEP_NAME,D.BRANCH_NAME,D.BRANCH_CODE,
|
|
125
|
--------------BAODNQ 28/2/2022: Lấy thêm cột-----------
|
|
126
|
L.PLAN_NAME, L.PLAN_CODE, L.PLAN_ID,
|
|
127
|
SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_USE,
|
|
128
|
SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_ETM,
|
|
129
|
SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,
|
|
130
|
SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,
|
|
131
|
SUM(ISNULL(DT.AMT_EXE,0)) AS AMT_EXE,
|
|
132
|
SUM(ISNULL(DT.AMT_ETM,0) + ISNULL(DT.BUY_TMP,0)) AS AMT_ETM,
|
|
133
|
SUM(ISNULL(DT.AMT_TF,0) + ISNULL(DT.TF_TMP,0)) AS AMT_TRANSFER,
|
|
134
|
SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
|
|
135
|
SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)-ISNULL(DT.TF_TMP,0)) AS AMT_REMAIN,
|
|
136
|
SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)-ISNULL(DT.TF_TMP,0)-ISNULL(DT.BUY_TMP,0)) AS AMT_REMAIN_ETM,
|
|
137
|
SUM(ISNULL(DT.TF_TMP,0)) AS TF_TMP, SUM(ISNULL(DT.BUY_TMP,0)) AS BUY_TMP
|
|
138
|
-- SELECT END
|
|
139
|
FROM
|
|
140
|
(
|
|
141
|
SELECT PLAN_ID,GOODS_ID,
|
|
142
|
QUANTITY,QUANTITY_EXE,QUANTITY_ETM,
|
|
143
|
AMT_APP,AMT_TF,AMT_RECEIVE_TF,AMT_EXE,AMT_ETM,
|
|
144
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
145
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
146
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
147
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
148
|
AND DDT.FR_TRADE_ID = PL.TRADE_ID) AS TF_TMP,
|
|
149
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
150
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
151
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
152
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
153
|
AND DDT.TRADE_ID = PL.TRADE_ID) AS BUY_TMP
|
|
154
|
FROM PL_TRADEDETAIL PL
|
|
155
|
) DT
|
|
156
|
LEFT JOIN PL_MASTER L ON L.PLAN_ID = DT.PLAN_ID
|
|
157
|
LEFT JOIN CM_GOODS CG on CG.GD_ID=DT.GOODS_ID
|
|
158
|
LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = L.BRANCH_ID
|
|
159
|
LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
|
|
160
|
LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
|
|
161
|
LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
|
|
162
|
LEFT JOIN CM_ALLCODE ALLC ON ALLC.CDNAME = 'HMNS_TYPE' AND ALLC.CDTYPE = 'HMNS_TYPE' AND ALLC.CDVAL = CG.GD_TYPE_ID
|
|
163
|
WHERE 1=1
|
|
164
|
AND (CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
|
|
165
|
AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
|
|
166
|
AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
|
|
167
|
AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
|
|
168
|
AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
|
|
169
|
AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
|
|
170
|
AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
|
|
171
|
AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
|
|
172
|
AND ((@BRANCH_TYPE='HS' AND((L.BRANCH_ID=@P_BRANCHLOGIN AND L.DEPT_ID=@DEPLOGIN ) OR( L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR L.COST_ID IS NULL OR L.COST_ID ='')) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN))
|
|
173
|
AND L.RECORD_STATUS = '1'
|
|
174
|
GROUP BY L.[YEAR], ALLC.CONTENT, L.BRANCH_ID, L.DEPT_ID, CG.GD_TYPE_ID, D.BRANCH_NAME, E.DEP_NAME,D.BRANCH_NAME,D.BRANCH_CODE,
|
|
175
|
--------------BAODNQ 28/2/2022: Thêm điều kiện group by-----------
|
|
176
|
L.PLAN_NAME, L.PLAN_CODE,L.PLAN_ID
|
|
177
|
ORDER BY L.BRANCH_ID, L.DEPT_ID
|
|
178
|
-- PAGING END
|
|
179
|
END
|
|
180
|
|
|
181
|
END
|
|
182
|
END -- PAGING
|