|
1
|
ALTER PROCEDURE [dbo].[rpt_NhapXuatTonKhoVatLieuDuTru_BanViet]
|
|
2
|
@p_Fromdate VARCHAR(20) =NULL, @p_Todate VARCHAR(20) =NULL, @p_Branch_ID VARCHAR(15) =NULL, @p_Level VARCHAR(10) =NULL,@p_WARE_HOUSE VARCHAR(15) = NULL,
|
|
3
|
@p_BRANCH_LOGIN VARCHAR(15) =NULL
|
|
4
|
AS
|
|
5
|
DECLARE @BRANCH_TMP TABLE ( BRANCH_ID VARCHAR(15), BRANCH_NAME NVARCHAR(200))
|
|
6
|
|
|
7
|
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15));
|
|
8
|
INSERT INTO @tmp
|
|
9
|
SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID);
|
|
10
|
|
|
11
|
IF(@p_LEVEL='ALL')
|
|
12
|
INSERT INTO @BRANCH_TMP
|
|
13
|
SELECT A.BRANCH_ID,(SELECT BRANCH_NAME FROM CM_BRANCH CM WHERE CM.BRANCH_ID =A.BRANCH_ID) FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID) A;
|
|
14
|
ELSE
|
|
15
|
INSERT INTO @BRANCH_TMP
|
|
16
|
SELECT BRANCH_ID,BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_Branch_ID
|
|
17
|
|
|
18
|
--DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15));
|
|
19
|
--INSERT INTO @tmp
|
|
20
|
--SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN;
|
|
21
|
|
|
22
|
DECLARE @BRANCH_NAME NVARCHAR(1000)
|
|
23
|
SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_Branch_ID)
|
|
24
|
|
|
25
|
|
|
26
|
DECLARE @TABLE_REPORT TABLE(STT INT,LIQ_ACCTNO NVARCHAR(40),MATERIAL_CODE NVARCHAR(100),GROUP_NAME NVARCHAR(100),MATERIAL_NAME NVARCHAR(100),
|
|
27
|
MATERIAL_ACCTNO NVARCHAR(40),SLTonDau NUMERIC(18,0),
|
|
28
|
THANHTIENTonDau NUMERIC(18,0),SLNhap NUMERIC(18,0),THANHTIENNhap NUMERIC(18,0),SLXuatLe NUMERIC(18,0),ThanhTienXuatLe NUMERIC(18,0),SLDieuChuyen NUMERIC(18,0),ThanhTienDieuChuyen NUMERIC(18,0),
|
|
29
|
SLXuatTapTrung NUMERIC(18,0),ThanhTienXuatTapTrung NUMERIC(18,0),SLTonCuoi NUMERIC(18,0),THANHTIENTonCuoi NUMERIC(18,0),
|
|
30
|
UNIT_NAME NVARCHAR(50),GROUP_ID VARCHAR(50), GROUP_LEVEL1 VARCHAR(15), GROUP_LEVEL2 VARCHAR(15), DonGia DECIMAL(18,0), SLThuHoi NUMERIC(18,0),ThanhTienThuHoi NUMERIC(18,0), NOTES NVARCHAR(1000),DVSD NVARCHAR(1000), ORDER_IDE INT)
|
|
31
|
BEGIN
|
|
32
|
-- LEVEL 1
|
|
33
|
INSERT INTO @TABLE_REPORT SELECT AA.*,'5' FROM
|
|
34
|
(
|
|
35
|
SELECT Row_number() over(order by BRT.BRANCH_NAME) AS STT, GR.LIQ_ACCTNO, F.PRICE_CODE MATERIAL_CODE,GR.GROUP_NAME,MI.MATERIAL_NAME, '' MATERIAL_ACCTNO,
|
|
36
|
ISNULL(A.SOLUONG_TON_DAU,0) SLTonDau,
|
|
37
|
ISNULL(A.SOTIEN_TONDAU,0) THANHTIENTonDau,
|
|
38
|
ISNULL(A5.NHAP_TRONG_KI,0) SLNhap,
|
|
39
|
ISNULL(A5.SOTIEN_NHAP_TRONG_KI,0) THANHTIENNhap,
|
|
40
|
ISNULL(A1.SOLUONG_XUAT_DONLE,0) AS SLXuatLe,
|
|
41
|
ISNULL(A1.SOTIEN_XUAT_DONLE,0) AS ThanhTienXuatLe,
|
|
42
|
ISNULL(TF.SOLUONG_DIEU_CHUYEN,0) AS SLDieuChuyen,
|
|
43
|
ISNULL(TF.SOTIEN_DIEU_CHUYEN,0) AS ThanhTienDieuChuyen,
|
|
44
|
ISNULL(A2.SOLUONG_XUAT_TAP_TRUNG,0) AS SLXuatTapTrung,
|
|
45
|
ISNULL(A2.SOTIEN_XUAT_TAP_TRUNG,0) AS ThanhTienXuatTapTrung,
|
|
46
|
(ISNULL(A4.SOLUONG_TON_CUOI,0) -ISNULL(NOT_TF.SOLUONG_DIEU_CHUYEN,0)) SLTonCuoi,(ISNULL(A4.SOTIEN_TONCUOI,0) -ISNULL(NOT_TF.SOTIEN_DIEU_CHUYEN,0)) THANHTIENTonCuoi,UN.UNIT_NAME,
|
|
47
|
--ISNULL(DBO.FN_GET_MW_GROUP_ID(F.GROUP_ID,1),DBO.FN_GET_MW_GROUP_ID(F.GROUP_ID,1)) GROUP_ID,
|
|
48
|
MI.GROUP_ID GROUP_ID,
|
|
49
|
[dbo].[FN_GET_MW_GROUP_ID](MI.GROUP_ID,1) GROUP_LEVE1,
|
|
50
|
[dbo].[FN_GET_MW_GROUP_ID](MI.GROUP_ID,2) GROUP_LEVEL2, F.PRICE, TH.SOLUONG_THU_HOI, TH.SOTIEN_THU_HOI,MI.NOTES,( BRT.BRANCH_NAME) AS BRANCH_NAME
|
|
51
|
FROM @BRANCH_TMP BRT
|
|
52
|
LEFT JOIN
|
|
53
|
(
|
|
54
|
SELECT PRICE_ID, BRANCH_ID FROM dbo.MW_MAST_BAL
|
|
55
|
GROUP BY PRICE_ID, BRANCH_ID
|
|
56
|
) MB ON MB.BRANCH_ID = BRT.BRANCH_ID
|
|
57
|
LEFT JOIN MW_MAST_PRICE F ON F.PRICE_ID=MB.PRICE_ID
|
|
58
|
LEFT JOIN MW_IN MI ON F.MATERIAL_ID = MI.IN_ID
|
|
59
|
LEFT JOIN MW_IN_MASTER MST ON MI.IN_MASTER_ID = MST.IN_ID
|
|
60
|
LEFT JOIN MW_MATERIAL MT ON MI.MATERIAL_ID = MT.MATERIAL_ID
|
|
61
|
LEFT JOIN
|
|
62
|
(
|
|
63
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE -A.QTY END) AS SOLUONG_TON_DAU,
|
|
64
|
SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE -A.TOTAL_AMT END) AS SOTIEN_TONDAU
|
|
65
|
FROM
|
|
66
|
MW_MAST_BAL_STMT A
|
|
67
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
68
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
69
|
WHERE CONVERT(DATE,A.TRN_DATE, 103) < CONVERT(DATE, @p_Fromdate,103)
|
|
70
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
71
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
72
|
) A ON F.PRICE_ID = A.PRICE_ID AND A.BRANCH_ID=BRT.BRANCH_ID
|
|
73
|
|
|
74
|
LEFT JOIN
|
|
75
|
(
|
|
76
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_XUAT_DONLE,
|
|
77
|
SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_XUAT_DONLE
|
|
78
|
FROM
|
|
79
|
MW_MAST_BAL_STMT A
|
|
80
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
81
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
82
|
WHERE A.TRN_TYPE IN ('O','T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
|
|
83
|
AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
84
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
85
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
86
|
) A1 ON F.PRICE_ID = A1.PRICE_ID AND A1.BRANCH_ID=BRT.BRANCH_ID
|
|
87
|
LEFT JOIN
|
|
88
|
(
|
|
89
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_XUAT_TAP_TRUNG,
|
|
90
|
SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_XUAT_TAP_TRUNG
|
|
91
|
FROM
|
|
92
|
MW_MAST_BAL_STMT A
|
|
93
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
94
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
95
|
WHERE A.TRN_TYPE ='L' AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
|
|
96
|
AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
97
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
98
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
99
|
) A2 ON F.PRICE_ID = A2.PRICE_ID AND A2.BRANCH_ID=BRT.BRANCH_ID
|
|
100
|
LEFT JOIN
|
|
101
|
(
|
|
102
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS SOLUONG_DIEU_CHUYEN,
|
|
103
|
SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_DIEU_CHUYEN
|
|
104
|
FROM
|
|
105
|
MW_MAST_BAL_STMT A
|
|
106
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
107
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
108
|
WHERE A.TRN_TYPE IN ('T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
|
|
109
|
AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
110
|
AND (EXISTS ( SELECT TRANSFER_ID FROM dbo.MW_TRANSFER_CONF WHERE TRANSFER_ID=A.REF_ID AND BRANCH_RECIVE=B.BRANCH_ID))
|
|
111
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
112
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
113
|
) TF ON F.PRICE_ID = TF.PRICE_ID AND TF.BRANCH_ID=BRT.BRANCH_ID
|
|
114
|
LEFT JOIN
|
|
115
|
(
|
|
116
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_THU_HOI,
|
|
117
|
SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_THU_HOI
|
|
118
|
FROM
|
|
119
|
MW_MAST_BAL_STMT A
|
|
120
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
121
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
122
|
WHERE A.TRN_TYPE ='C' AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
|
|
123
|
AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
124
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
125
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
126
|
) TH ON F.PRICE_ID = TH.PRICE_ID AND TH.BRANCH_ID=BRT.BRANCH_ID
|
|
127
|
LEFT JOIN
|
|
128
|
(
|
|
129
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE -A.QTY END) AS SOLUONG_TON_CUOI,
|
|
130
|
SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE -A.TOTAL_AMT END) AS SOTIEN_TONCUOI
|
|
131
|
FROM
|
|
132
|
MW_MAST_BAL_STMT A
|
|
133
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
134
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
135
|
WHERE CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
136
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
137
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
138
|
) A4 ON F.PRICE_ID = A4.PRICE_ID AND A4.BRANCH_ID=BRT.BRANCH_ID
|
|
139
|
LEFT JOIN
|
|
140
|
(
|
|
141
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS NHAP_TRONG_KI,
|
|
142
|
SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_NHAP_TRONG_KI
|
|
143
|
FROM
|
|
144
|
MW_MAST_BAL_STMT A
|
|
145
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
146
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
147
|
WHERE A.TRN_TYPE IN ('I','O') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103) AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
148
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
149
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
150
|
) A5 ON F.PRICE_ID = A5.PRICE_ID AND A5.BRANCH_ID= BRT.BRANCH_ID
|
|
151
|
-- TÍNH SỐ LƯỢNG ĐIỀU CHUYỂN VL NHƯNG CHƯA ĐƯỢC XÁC NHẬN
|
|
152
|
LEFT JOIN
|
|
153
|
(
|
|
154
|
SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS SOLUONG_DIEU_CHUYEN,
|
|
155
|
SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_DIEU_CHUYEN
|
|
156
|
FROM
|
|
157
|
MW_MAST_BAL_STMT A
|
|
158
|
LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
|
|
159
|
LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
|
|
160
|
WHERE A.TRN_TYPE IN ('T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
|
|
161
|
AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
|
|
162
|
AND (NOT EXISTS ( SELECT TRANSFER_ID FROM dbo.MW_TRANSFER_CONF WHERE TRANSFER_ID=A.REF_ID AND BRANCH_RECIVE=B.BRANCH_ID))
|
|
163
|
AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
|
|
164
|
GROUP BY PR.PRICE_ID,B.BRANCH_ID
|
|
165
|
) NOT_TF ON F.PRICE_ID = NOT_TF.PRICE_ID AND NOT_TF.BRANCH_ID=BRT.BRANCH_ID
|
|
166
|
----------
|
|
167
|
LEFT JOIN MW_GROUP GR ON MI.GROUP_ID = GR.GROUP_ID
|
|
168
|
LEFT JOIN CM_UNIT UN ON MT.UNIT_ID = UN.UNIT_ID
|
|
169
|
WHERE MST.WARE_HOUSE =@p_WARE_HOUSE
|
|
170
|
-- LUCTV BO SUNG DIEU KIEN CHI LAY NHUNG LO VAT LIEU DUOC NHAP KHO TRONG KHOANG THOI GIAN TU NGAY DEN NGAY
|
|
171
|
--AND (CONVERT(DATE,MST.APPROVE_DT_KT,103) >= CONVERT (DATE,@p_Fromdate,103) OR @p_Fromdate IS NULL OR @p_Fromdate ='')
|
|
172
|
AND (CONVERT(DATE,MST.APPROVE_DT_KT,103) <= CONVERT (DATE,@p_Todate,103) OR @p_Todate IS NULL OR @p_Todate ='')
|
|
173
|
) AA
|
|
174
|
SELECT A.* FROM @TABLE_REPORT A
|
|
175
|
ORDER BY A.DVSD
|
|
176
|
END
|