Project

General

Profile

Download (31.3 KB)

PL REQ GOOD SEARCH.txt

Luc Tran Van, 01/19/2021 02:07 PM

 
1 
??CREATE PROCEDURE [dbo].[PL_REQ_GOOD_Search]
2 
@P_REQDT_TYPE varchar(1)= NULL,
3 
@p_GD_ID VARCHAR(15) = NULL,
4 
@p_GD_CODE varchar(20) = NULL,
5 
@p_GD_NAME nvarchar(200) = NULL,
6 
@p_BRANCH_CODE varchar(15) = NULL,
7 
@p_BRANCH_ID VARCHAR(20) = NULL,
8 
@p_DEP_ID VARCHAR(20) = NULL,
9 
@p_BRANCH_LOGIN varchar(15) = NULL,
10 
@p_USER_LOGIN VARCHAR(20) = NULL,
11 
@P_PLAN_ID NVARCHAR(15) = NULL,
12 
@P_HH_ID VARCHAR(15),
13 
@p_GD_TYPE_ID VARCHAR(20) = NULL,
14 
@p_GD_TYPE_CODE VARCHAR(20)= NULL,
15 
@p_GD_TYPE_NAME NVARCHAR(500)= NULL,
16 
@p_DVDM_ID VARCHAR(15) = NULL,
17 
@p_PLAN_TYPE_ID VARCHAR(15) = NULL,
18 
@p_TYPE_NS VARCHAR(15) = NULL,
19 
@P_TOP INT = null
20 
AS
21 
BEGIN
22 
DECLARE
23 
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
24 
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,@GD_CODE VARCHAR(50),
25 
@BRANCH_TYPE VARCHAR(20),@BRANCH_LOGIN VARCHAR(20),@DEP_LOGIN VARCHAR(20),@ROLE_USER VARCHAR(20),@IS_ALL BIT,@IS_CHUNG BIT
26 
DECLARE @IS_HANHCHINH BIT
27 
SET @IS_HANHCHINH=0
28
29 
SET @IS_ALL=0
30
31 
DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))
32 
DECLARE @TRADE_REF TABLE( TRADE_ID VARCHAR (20))
33
34 
SELECT @BRANCH_LOGIN=TLSUBBRID,@DEP_LOGIN=SECUR_CODE ,@ROLE_USER=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN
35
36
37 
IF(@p_BRANCH_CODE IS NOT NULL AND @p_BRANCH_CODE <>'')
38 
SELECT TOP 1 @p_DEP_ID=DEP_ID,@p_BRANCH_ID=BRANCH_ID FROM dbo.CM_DEPARTMENT WHERE DEP_CODE=@p_BRANCH_CODE
39
40 
IF(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)
41 
SET @p_BRANCH_ID=@BRANCH_LOGIN
42
43 
IF(EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID AND BRANCH_TYPE = 'HS'))
44 
BEGIN
45 
IF(@p_DEP_ID='' OR @p_DEP_ID IS NULL)
46 
SET @p_DEP_ID=@DEP_LOGIN
47 
END
48
49 
SET @BRANCH_TYPE= (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_ID)
50
51 
INSERT @TRADE_REF
52 
SELECT TRADE_ID FROM dbo.PL_TRADE_REF WHERE REF_CODE IN (SELECT DEP_CODE FROM dbo.CM_DEPARTMENT WHERE BRANCH_ID=@BRANCH_LOGIN)
53
54 
PRINT @BRANCH_TYPE
55 
IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_LOGIN AND DEP_ID=@DEP_LOGIN))
56 
SET @IS_ALL=1
57
58
59
60
61 
SET @IS_CHUNG=0
62
63 
IF(EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG WHERE BRANCH_ID=@p_BRANCH_ID AND DEP_ID=@p_DEP_ID AND ROLE_TYPE='TRADE_USER_ALL'))
64 
BEGIN
65
66
67
68 
IF(EXISTS(
69 
SELECT CC.DVDM_ID FROM dbo.CM_DVDM DM
70 
LEFT JOIN dbo.PL_COSTCENTER CC ON DM.DVDM_ID=CC.DVDM_ID
71 
LEFT JOIN dbo.PL_COSTCENTER_DT CT ON CT.COST_ID=CC.COST_ID
72 
WHERE CT.DEP_ID=@DEP_LOGIN AND CT.BRANCH_ID=@p_BRANCH_ID AND DM.IS_KHOI=1 AND CC.DVDM_ID IN (SELECT CC.DVDM_ID FROM dbo.CM_DVDM DM
73 
LEFT JOIN dbo.PL_COSTCENTER CC ON DM.DVDM_ID=CC.DVDM_ID
74 
LEFT JOIN dbo.PL_COSTCENTER_DT CT ON CT.COST_ID=CC.COST_ID
75 
WHERE CT.DEP_ID=@p_DEP_ID AND CT.BRANCH_ID=@p_BRANCH_ID AND DM.IS_KHOI=1
76 
)))
77 
SET @IS_CHUNG=1
78 
END
79
80
81
82
83
84 
IF(EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG WHERE BRANCH_ID=@BRANCH_LOGIN AND DEP_ID=@DEP_LOGIN AND ROLE_TYPE='HANHCHINH'))
85 
BEGIN
86 
SET @IS_HANHCHINH=1
87 
END
88
89
90 
---PRINT @IS_CHUNG
91
92 
IF(@IS_ALL=1 OR @ROLE_USER='TGD')
93 
BEGIN
94 
INSERT INTO @DVDM_ID
95 
SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
96 
END
97 
ELSE
98 
BEGIN
99 
INSERT INTO @DVDM_ID
100 
SELECT PC.DVDM_ID FROM
101 
dbo.PL_COSTCENTER PC
102 
LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
103 
WHERE PT.DEP_ID=@DEP_LOGIN AND PT.BRANCH_ID=@BRANCH_LOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
104
105 
END
106
107
108
109 
DECLARE @lstGD TABLE (
110 
GD_ID VARCHAR(20)
111 
)
112
113 
DECLARE @lstGD_CODE TABLE (
114 
GD_CODE VARCHAR(20)
115 
)
116 
INSERT INTO @lstGD
117 
SELECT GD_ID FROM dbo.CM_HANGHOA_GOODS WHERE HH_ID=@P_HH_ID
118
119
120 
IF(@P_HH_ID IS NOT NULL AND @P_HH_ID <>'')
121 
BEGIN
122 
IF(NOT EXISTS(SELECT PM.PLAN_ID FROM dbo.PL_MASTER PM
123 
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.PLAN_ID=PM.PLAN_ID
124 
WHERE (PT.GOODS_ID =@p_GD_ID OR EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=PT.GOODS_ID))
125 
--AND PM.YEAR = YEAR(GETDATE())
126 
AND (
127 
(
128 
@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID
129 
AND( @IS_CHUNG=1 OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))))
130 
)
131 
OR
132 
(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) ))
133 
OR
134 
(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID AND (@p_BRANCH_ID=@BRANCH_LOGIN OR PT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND
135 
PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))) )
136 
)
137 
OR
138 
(
139 
EXISTS (SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_VIEW' AND BRANCH_ID=@p_USER_LOGIN AND DEP_ID=PT.GOODS_ID)
140 
)
141 
)
142
143 
)
144 
BEGIN
145 
SET @P_REQDT_TYPE='O'
146 
INSERT INTO @lstGD_CODE
147 
SELECT REPLACE(GD_CODE,'.I.','.O.') FROM dbo.CM_GOODS WHERE GD_ID IN (SELECT GD_ID FROM @lstGD)
148 
END
149 
END
150
151
152
153
154 
PRINT @p_BRANCH_ID
155 
PRINT @p_DEP_ID
156 
PRINT @BRANCH_LOGIN
157
158
159 
IF(@P_REQDT_TYPE='I')
160 
BEGIN
161 
IF(@P_PLAN_ID IS NOT NULL AND @P_PLAN_ID <>'')
162 
BEGIN
163
164 
SELECT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
165 
ISNULL(DT.QUANTITY,0) AS QUANTITY,
166 
ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
167 
ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
168 
ISNULL(DT.QUANTITY,0)- ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
169 
ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM,
170 
ISNULL( DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_APP,
171 
ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
172 
ISNULL( DT.AMT_ETM,0) AS AMT_ETM,
173 
ISNULL( DT.AMT_TF,0) AS AMT_TF,
174 
ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
175 
ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN,
176 
ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
177 
CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
178 
DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
179 
DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,
180 
PM.PLAN_TYPE_ID, CG.GD_TYPE_ID AS TYPE_NS
181
182 
FROM dbo.PL_MASTER PM
183 
LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
184 
LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
185
186 
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
187 
LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
188 
LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
189 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
190 
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
191
192 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID
193 
LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID
194 
LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
195 
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.KHOI_ID
196 
WHERE (1=1)
197 
AND (CG.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
198 
AND (CG.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
199 
--AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
200 
--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
201 
AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
202 
AND (DT.GOODS_ID = @p_GD_ID OR @p_GD_ID IS NULL OR @p_GD_ID ='')
203 
AND (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
204 
--AND PM.YEAR = YEAR(GETDATE())
205
206 
AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
207 
AND(PM.DEPT_ID= @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID='')
208 
AND(DM.DVDM_ID = @p_DVDM_ID OR @p_DVDM_ID IS NULL OR @p_DVDM_ID = '')
209 
AND(PM.PLAN_TYPE_ID = @p_PLAN_TYPE_ID OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = '')
210 
AND(CG.GD_TYPE_ID = @p_TYPE_NS OR @p_TYPE_NS IS NULL OR @p_TYPE_NS = '')
211
212 
AND (GT.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
213 
AND (GT.GD_TYPE_CODE LIKE '%' + @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
214 
AND (GT.GD_TYPE_NAME LIKE N'%' + @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
215 
AND DT.RECORD_STATUS='1'
216 
END
217 
ELSE
218 
BEGIN
219 
SELECT DISTINCT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
220 
ISNULL(DT.QUANTITY,0) AS QUANTITY,
221 
ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
222 
ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
223 
ISNULL(DT.QUANTITY,0)- ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
224 
ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM,
225 
ISNULL( DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_APP,
226 
ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
227 
ISNULL( DT.AMT_ETM,0) AS AMT_ETM,
228 
ISNULL( DT.AMT_TF,0) AS AMT_TF,
229 
ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
230 
ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN,
231 
ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
232 
CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
233 
DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
234 
DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,
235 
PM.PLAN_TYPE_ID, CG.GD_TYPE_ID AS TYPE_NS
236 
FROM dbo.PL_MASTER PM
237 
LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
238 
LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
239
240 
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
241 
LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
242 
LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
243 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
244 
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
245
246 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID
247 
LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID
248 
LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
249 
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.KHOI_ID
250 
WHERE (1=1)
251 
AND (CG.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
252 
AND (CG.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
253 
--AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
254 
--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
255 
--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
256 
AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
257 
AND (DT.GOODS_ID = @p_GD_ID OR @p_GD_ID IS NULL OR @p_GD_ID ='')
258 
AND (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
259 
AND (
260 
(
261 
@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID
262 
AND( @IS_CHUNG=1 OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR ((@p_DEP_ID = @DEP_LOGIN) OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (@p_DEP_ID <> @DEP_LOGIN AND (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))))
263 
)
264 
OR
265 
(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) ))
266 
OR
267 
(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID AND (@p_BRANCH_ID=@BRANCH_LOGIN OR @IS_HANHCHINH=1 OR @IS_ALL=1 OR DT.TRADE_ID IN (SELECT TRADE_ID FROM @TRADE_REF) OR (PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND
268 
PM.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) )) ) )
269 
OR
270 
(
271 
EXISTS (SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_VIEW' AND BRANCH_ID=@p_USER_LOGIN AND DEP_ID=DT.GOODS_ID)
272 
)
273 
)
274
275 
--AND PM.YEAR = YEAR(GETDATE())
276
277 
--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
278 
--AND(PM.DEPT_ID= @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID='')
279 
AND(DM.DVDM_ID = @p_DVDM_ID OR @p_DVDM_ID IS NULL OR @p_DVDM_ID = '')
280 
AND(PM.PLAN_TYPE_ID = @p_PLAN_TYPE_ID OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = '')
281 
AND(CG.GD_TYPE_ID = @p_GD_TYPE_ID OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
282 
AND(CG.GD_TYPE_ID = @p_TYPE_NS OR @p_TYPE_NS IS NULL OR @p_TYPE_NS = '')
283
284 
AND (GT.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
285 
AND (GT.GD_TYPE_CODE LIKE '%' + @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
286 
AND (GT.GD_TYPE_NAME LIKE N'%' + @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
287 
AND DT.RECORD_STATUS='1'
288 
END
289
290 
END
291 
ELSE
292 
BEGIN
293
294 
DECLARE @BRANCH_CODE VARCHAR(15), @BRANCH_NAME NVARCHAR(200), @DEP_CODE VARCHAR(15), @DEP_NAME VARCHAR(200)
295 
SET @BRANCH_CODE = (SELECT BRANCH_CODE FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID)
296 
SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM dbo.CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID)
297 
SET @DEP_CODE = (SELECT DEP_CODE FROM dbo.CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
298 
SET @DEP_NAME = (SELECT DEP_NAME FROM dbo.CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID)
299
300 
SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
301 
@l_QUANTITY AS QUANTITY,
302 
@l_QUANTITY_EXE AS QUANTITY_EXE,
303 
@l_QUANTITY_EXE AS QUANTITY_ETM,
304 
@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
305 
@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
306 
@l_AMT AS AMT_APP,
307 
@l_AMT_EXE AS AMT_EXE,
308 
@l_AMT_EXE AS AMT_ETM,
309 
@l_AMT_EXE AS AMT_TF,
310 
@l_AMT_EXE AS AMT_RECEIVE_TF,
311 
@l_AMT_REMAIN AS AMT_REMAIN,
312 
@l_AMT_REMAIN AS AMT_REMAIN_ETM,
313 
@BRANCH_CODE AS BRANCH_CODE,
314 
@BRANCH_NAME AS BRANCH_NAME,
315 
--N'Ngo?i k? ho?ch' AS BRANCH_NAME,
316 
'O' AS REQDT_TYPE,
317 
'' AS NOTES
318 
,'' AS PLAN_TYPE_CODE,'' AS PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
319 
DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME, @DEP_CODE AS DEP_CODE, @DEP_NAME AS DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
320 
FROM dbo.CM_GOODS CG
321 
LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
322 
LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
323 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
324 
LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
325 
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
326
327
328
329 
WHERE (1=1)
330 
AND (CG.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
331 
AND (CG.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
332 
AND CG.GD_CODE LIKE '%.O.%'
333 
AND (EXISTS(SELECT GD_CODE FROM @lstGD_CODE WHERE [@lstGD_CODE].GD_CODE=CG.GD_CODE ) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
334 
AND (GT.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
335 
AND (GT.GD_TYPE_CODE LIKE '%' + @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
336 
AND (GT.GD_TYPE_NAME LIKE N'%' + @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
337
338 
END
339
340
341 
END
342