|
1
|
|
|
2
|
|
|
3
|
ALTER PROCEDURE dbo.rpt_CCLD_BC8_2
|
|
4
|
@sp_Fromdate VARCHAR(20) = NULL,
|
|
5
|
@sp_Todate VARCHAR(20) = NULL,
|
|
6
|
@sp_DVSD VARCHAR(15) = NULL,
|
|
7
|
@sp_CCLD_Type varchar(15) = NULL,
|
|
8
|
@sp_Supplier varchar(15) = NULL,
|
|
9
|
@sp_BRANCH_ID VARCHAR(15) = NULL,
|
|
10
|
@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
11
|
@sp_LEVEL VARCHAR(10) = 'ALL',
|
|
12
|
@sp_PriceFrom varchar(15) = Null,
|
|
13
|
@sp_PriceTo varchar(15) = null,
|
|
14
|
@sp_Dep_ID VARCHAR(15)= NULL
|
|
15
|
|
|
16
|
AS
|
|
17
|
BEGIN
|
|
18
|
--thieuvq 24/8/2017 - DOC DU LIEU BO QUA COMMIT TRANSACTION
|
|
19
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
20
|
|
|
21
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
22
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
|
|
23
|
declare @tmp_login table(BRANCH_ID varchar(15))
|
|
24
|
insert into @tmp_login SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)
|
|
25
|
|
|
26
|
|
|
27
|
SELECT
|
|
28
|
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
|
|
29
|
--F.MA_CCLD,
|
|
30
|
--F.TEN_CCLD,
|
|
31
|
SUM(F.SL_GIATRI_TONDAUKY) AS SL_GIATRI_TONDAUKY,
|
|
32
|
SUM(F.SL_GIATRI_NHAPTRONGKY) AS SL_GIATRI_NHAPTRONGKY,
|
|
33
|
SUM(F.SL_GIATRI_XUATTRONGKY) AS SL_GIATRI_XUATTRONGKY,
|
|
34
|
SUM(F.SL_GIATRI_TONCUOIKY) AS SL_GIATRI_TONCUOIKY,
|
|
35
|
SUM(F.GIATRI_TONDAUKY) AS GIATRI_TONDAUKY,
|
|
36
|
SUM(F.GIATRI_NHAPTRONGKY) AS GIATRI_NHAPTRONGKY,
|
|
37
|
SUM(F.GIATRI_XUATTRONGKY) AS GIATRI_XUATTRONGKY,
|
|
38
|
SUM(F.GIATRI_TONCUOIKY) AS GIATRI_TONCUOIKY,
|
|
39
|
F.MA_CHI_NHANH_KHO AS MA_CHI_NHANH,
|
|
40
|
--F.TEN_CHINHANH_KHO,
|
|
41
|
'' GHI_CHU,
|
|
42
|
F.MA_NHOM_CCLD_1,
|
|
43
|
F.MA_NHOM_CCLD_2,
|
|
44
|
F.MA_NHOM_CCLD_3,
|
|
45
|
F.NHOM_CCLD_1,
|
|
46
|
F.NHOM_CCLD_2,
|
|
47
|
F.NHOM_CCLD_3,
|
|
48
|
F.NHOM_CCLD_4,
|
|
49
|
F.UNIT_NAME_CCLD_3
|
|
50
|
FROM
|
|
51
|
( --NHAP KHO
|
|
52
|
SELECT
|
|
53
|
STT,
|
|
54
|
MA_CCLD,
|
|
55
|
TEN_CCLD,
|
|
56
|
0 GIATRI_TONDAUKY,
|
|
57
|
GIATRI AS GIATRI_NHAPTRONGKY,
|
|
58
|
0 GIATRI_XUATTRONGKY,
|
|
59
|
GIATRI AS GIATRI_TONCUOIKY,
|
|
60
|
|
|
61
|
0 AS SL_GIATRI_TONDAUKY,
|
|
62
|
1 AS SL_GIATRI_NHAPTRONGKY,
|
|
63
|
0 AS SL_GIATRI_XUATTRONGKY,
|
|
64
|
1 AS SL_GIATRI_TONCUOIKY,
|
|
65
|
|
|
66
|
MA_CHI_NHANH,
|
|
67
|
TEN_CHINHANH,
|
|
68
|
GHI_CHU,
|
|
69
|
NHOM_CCLD_1,
|
|
70
|
NHOM_CCLD_2,
|
|
71
|
NHOM_CCLD_3,
|
|
72
|
NHOM_CCLD_4,
|
|
73
|
MA_NHOM_CCLD_1,
|
|
74
|
MA_NHOM_CCLD_2,
|
|
75
|
MA_NHOM_CCLD_3,
|
|
76
|
MA_CHI_NHANH_KHO,
|
|
77
|
TEN_CHI_NHANH_KHO,
|
|
78
|
UNIT_NAME_CCLD_3
|
|
79
|
FROM [dbo].[fn_NHAP_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,
|
|
80
|
@sp_CCLD_Type,@sp_Supplier,@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
|
|
81
|
UNION ALL
|
|
82
|
--XUAT KHO
|
|
83
|
SELECT
|
|
84
|
STT,
|
|
85
|
MA_CCLD,
|
|
86
|
TEN_CCLD,
|
|
87
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
|
|
88
|
ELSE 0
|
|
89
|
END AS GIATRI_TONDAUKY,
|
|
90
|
0 GIATRI_NHAPTRONGKY,
|
|
91
|
|
|
92
|
GIATRI AS GIATRI_XUATTRONGKY,
|
|
93
|
|
|
94
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0
|
|
95
|
ELSE -GIATRI
|
|
96
|
END AS GIATRI_TONCUOIKY,
|
|
97
|
|
|
98
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONDAUKY,
|
|
99
|
0 SL_GIATRI_NHAPTRONGKY,
|
|
100
|
1 AS SL_GIATRI_XUATTRONGKY,
|
|
101
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0 ELSE -1 END AS SL_GIATRI_TONCUOIKY,
|
|
102
|
|
|
103
|
DVSD AS MA_CHI_NHANH,
|
|
104
|
TEN_CHINHANH,
|
|
105
|
GHI_CHU,
|
|
106
|
NHOM_CCLD_1,
|
|
107
|
NHOM_CCLD_2,
|
|
108
|
NHOM_CCLD_3,
|
|
109
|
NHOM_CCLD_4,
|
|
110
|
MA_NHOM_CCLD_1,
|
|
111
|
MA_NHOM_CCLD_2,
|
|
112
|
MA_NHOM_CCLD_3,
|
|
113
|
MA_CHI_NHANH_KHO,
|
|
114
|
TEN_CHI_NHANH_KHO,
|
|
115
|
UNIT_NAME_CCLD_3
|
|
116
|
FROM [dbo].[fn_XUAT_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,'',@sp_CCLD_Type,@sp_Supplier,
|
|
117
|
@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
|
|
118
|
UNION ALL
|
|
119
|
--TON KHO
|
|
120
|
SELECT
|
|
121
|
STT,
|
|
122
|
MA_CCLD,
|
|
123
|
TEN_CCLD,
|
|
124
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
|
|
125
|
ELSE 0
|
|
126
|
END AS GIATRI_TONDAUKY,
|
|
127
|
|
|
128
|
0 GIATRI_NHAPTRONGKY,
|
|
129
|
|
|
130
|
0 AS GIATRI_XUATTRONGKY,
|
|
131
|
|
|
132
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
|
|
133
|
ELSE 0
|
|
134
|
END AS GIATRI_TONCUOIKY,
|
|
135
|
|
|
136
|
|
|
137
|
|
|
138
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONDAUKY,
|
|
139
|
0 AS SL_GIATRI_NHAPTRONGKY,
|
|
140
|
0 AS SL_GIATRI_XUATTRONGKY,
|
|
141
|
CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONCUOIKY,
|
|
142
|
|
|
143
|
MA_CHINHANH,
|
|
144
|
TEN_CHINHANH,
|
|
145
|
GHI_CHU,
|
|
146
|
NHOM_CCLD_1,
|
|
147
|
NHOM_CCLD_2,
|
|
148
|
NHOM_CCLD_3,
|
|
149
|
NHOM_CCLD_4,
|
|
150
|
MA_NHOM_CCLD_1,
|
|
151
|
MA_NHOM_CCLD_2,
|
|
152
|
MA_NHOM_CCLD_3,
|
|
153
|
MA_CHI_NHANH_KHO,
|
|
154
|
TEN_CHI_NHANH_KHO,
|
|
155
|
UNIT_NAME_CCLD_3
|
|
156
|
FROM [dbo].[fn_TON_KHO](@sp_Todate,@sp_DVSD,@sp_CCLD_Type,@sp_Supplier,
|
|
157
|
@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
|
|
158
|
)F
|
|
159
|
--ORDER BY F.MA_CCLD
|
|
160
|
GROUP BY --F.MA_CCLD,F.TEN_CCLD,
|
|
161
|
F.MA_NHOM_CCLD_1,F.MA_NHOM_CCLD_2,F.MA_NHOM_CCLD_3,F.MA_CHI_NHANH_KHO,F.NHOM_CCLD_1,F.NHOM_CCLD_2,NHOM_CCLD_3,NHOM_CCLD_4,F.UNIT_NAME_CCLD_3
|
|
162
|
END
|
|
163
|
|
|
164
|
|
|
165
|
|