|
1
|
ALTER PROCEDURE dbo.rpt_TSCD_BC017_Excel_BanViet
|
|
2
|
-- Add the parameters for the stored procedure here
|
|
3
|
@p_BRANCH_ID varchar(30) = NULL,
|
|
4
|
@p_FromDate varchar(30) = NULL,
|
|
5
|
@p_ToDate varchar(15) = NULL,
|
|
6
|
@p_BRANCH_LOGIN varchar(15) = NULL,
|
|
7
|
@p_LEVEL varchar(10) = 'ALL',
|
|
8
|
@p_ASSET_TYPE varchar(15) = '', -- loai tai san TSCD/CCLD
|
|
9
|
@p_PriceFrom varchar(18) = null, -- nguyen gia tu
|
|
10
|
@p_PriceTo varchar(18) = null, -- nguyen gia den
|
|
11
|
@p_SupplierId varchar(15) = null, -- nha cung cap
|
|
12
|
@p_ASSET_GROUP_ID varchar(15) = null, -- Nhom tai san
|
|
13
|
@p_Branch_ID_Use varchar(15) = null ,-- don vi su dung
|
|
14
|
@p_User_ID varchar(15) = null -- nguoi su dung
|
|
15
|
AS
|
|
16
|
BEGIN
|
|
17
|
-- Do du lieu ao
|
|
18
|
|
|
19
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
20
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
|
|
21
|
declare @tmp_login table(BRANCH_ID varchar(15))
|
|
22
|
insert into @tmp_login SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
23
|
if @p_PriceFrom = '0' and @p_PriceTo = '0'
|
|
24
|
begin
|
|
25
|
set @p_PriceFrom = null
|
|
26
|
set @p_PriceTo = null
|
|
27
|
end
|
|
28
|
SELECT
|
|
29
|
Row_number() over(order by L.LIQ_ID) AS STT,
|
|
30
|
[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'1') AS NHOMTS,
|
|
31
|
-- [dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'1') AS NHOMTSLV1, --EM GOM NHOM THEO 3 CAI THUOC TINH NAY NHA
|
|
32
|
[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'2') AS NHOM_LV2,
|
|
33
|
[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'3') AS NHOM_LV3,
|
|
34
|
--G.GROUP_NAME NHOMTS, -- NHOM TAI SAN
|
|
35
|
M.ASSET_CODE MATS,
|
|
36
|
M.ASSET_NAME TENTS,
|
|
37
|
M.BUY_DATE NGAYNHAP, -- NGAY NHAP
|
|
38
|
M.BUY_PRICE NGUYENGIA,
|
|
39
|
--L.APPROVE_DT NGAY_TL, -- NGAY THANH LY
|
|
40
|
L.APPROVE_DT_KT NGAY_TL, --
|
|
41
|
--M.AMORTIZED_MONTH AS KHLK_NGAY_TL, -- KHAU HAO LUY KE DEN NGAY THANH LY
|
|
42
|
(M.AMORT_AMT - LD.REMAIN_VALUE) KHLK_NGAY_TL,
|
|
43
|
--M.BUY_PRICE - M.AMORTIZED_MONTH AS GTCL_NGAY_TL, -- GIA TRI CON LAI NGAY THANH LY
|
|
44
|
LD.REMAIN_VALUE GTCL_NGAY_TL,
|
|
45
|
LD.LIQ_PRICE GTTH, -- GIA TRI THU HOI
|
|
46
|
--(M.BUY_PRICE - M.AMORTIZED_MONTH - LD.LIQ_PRICE) AS LAI_LO, -- LAI~ HAY LO~
|
|
47
|
(LD.LIQ_AMT_BE_VAT - LD.REMAIN_VALUE) LAI_LO,
|
|
48
|
LD.LIQ_PRICE AS GT_TL,
|
|
49
|
L.BUY_COMPANY_ID, -- DON VI MUA
|
|
50
|
CASE WHEN M.BRANCH_ID <>'' THEN BR.BRANCH_NAME ELSE BR_CR.BRANCH_NAME END DVSD, -- DON VI SU DUNG
|
|
51
|
CASE WHEN M.BRANCH_ID <>'' THEN BR.BRANCH_CODE ELSE BR_CR.BRANCH_CODE END BRANCH_CODE, -- DON VI SU DUNG
|
|
52
|
DP.DEP_NAME PHONGBAN_SD,DP.DEP_CODE, -- PHONG BAN SU DUNG
|
|
53
|
M.AMORT_START_DATE NGAYBD_KHPB, -- NGAY BAT DAU KHAU HAO PHAN BO
|
|
54
|
M.AMORT_MONTH THOIGIAN_KH,-- THOI GIAN KHAU HAO PHAN BO
|
|
55
|
M.AMORT_MONTH - (M.AMORTIZED_MONTH - ISNULL(AMT.ST_KH_PD_TAM,0)) SOTHANG_KH_CL ,-- SO THANG HAU HAO CON LAI,
|
|
56
|
M.BRANCH_CREATE, M.BRANCH_ID
|
|
57
|
from ASS_LIQUIDATION L
|
|
58
|
LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
|
|
59
|
LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
|
|
60
|
LEFT JOIN ASS_GROUP G ON M.GROUP_ID =G.GROUP_ID
|
|
61
|
LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
|
|
62
|
LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
|
|
63
|
LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
|
|
64
|
-- TINH SO THANG DA KHAU HAO = AMORTED_MONTH - SỐ THÁNG ĐÃ KHẤU HAO : AMORT_DT > TO_DATE
|
|
65
|
LEFT JOIN
|
|
66
|
(
|
|
67
|
--SELECT AM_DT.ASSET_ID, ISNULL(SUM(ISNULL(AM_DT.AMORT_AMT,0)),0) AS ST_DA_PB FROM ASS_AMORT_DT AM_DT
|
|
68
|
SELECT AM_DT.ASSET_ID, ISNULL(COUNT (*),0) AS ST_KH_PD_TAM FROM ASS_AMORT_DT AM_DT
|
|
69
|
WHERE CONVERT(DATE,AM_DT.AMORT_DT) > CONVERT(DATE,@p_ToDate,103)
|
|
70
|
AND AM_DT.CRDR='C'
|
|
71
|
GROUP BY AM_DT.ASSET_ID
|
|
72
|
) AMT ON AMT.ASSET_ID = M.ASSET_ID
|
|
73
|
-- END
|
|
74
|
-- SELECT * FROM ASS_LIQUIDATION
|
|
75
|
WHERE
|
|
76
|
--(M.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
77
|
--LUCTV: 26-12-2018: CHUYEN TU APPROVE HANH CHINH SANG APPROVE KE TOAN
|
|
78
|
--LUCTV : 03-12-2018: CHI LAY NHUNG THANH LY DA DUOC DUYET
|
|
79
|
(L.AUTH_STATUS_KT='A')
|
|
80
|
AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime, @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate = '')
|
|
81
|
AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime, @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate = '')
|
|
82
|
AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')
|
|
83
|
AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom = '')
|
|
84
|
AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo = '')
|
|
85
|
AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId = '')
|
|
86
|
AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID = '')
|
|
87
|
AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID = '')
|
|
88
|
-- PHAN QUYEN
|
|
89
|
--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp) or M.BRANCH_ID='' or M.BRANCH_ID is null )
|
|
90
|
--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
|
|
91
|
--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp_login)or M.BRANCH_ID='' or M.BRANCH_ID is null)
|
|
92
|
--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL
|
|
93
|
--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')
|
|
94
|
--LUCTV: 04-12-2019 : BỔ SUNG THÊM NẾU CHƯA XUẤT SỬ DỤNG THÌ LỌC TÀI SẢN THEO ĐƠN VỊ TẠO
|
|
95
|
--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp)))
|
|
96
|
--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_ID OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID)))
|
|
97
|
--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login)))
|
|
98
|
--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_LOGIN OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE =@p_BRANCH_LOGIN)))
|
|
99
|
AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
|
|
100
|
OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
|
|
101
|
--AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
|
|
102
|
--luctv 01/01/19 bo dieu kien Unit di vi dieu kien nay bi sai.
|
|
103
|
--OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_LOGIN)OR M.BRANCH_ID =@p_BRANCH_LOGIN))
|
|
104
|
--)
|
|
105
|
AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
|
|
106
|
--@p_BRANCH_LOGIN varchar(15) = NULL,
|
|
107
|
--@p_LEVEL varchar(10) = 'ALL',
|
|
108
|
|
|
109
|
--@p_Branch_ID_Use varchar(15) = null ,-- don vi su dung
|
|
110
|
--@p_User_ID varchar(15) = null -- nguoi su dung
|
|
111
|
--SELECT TOP 2 * FROM ASS_MASTER
|
|
112
|
--SELECT * FROM ASS_LIQUIDATION
|
|
113
|
--SELECT * FROM ASS_LIQUIDATION_DT
|
|
114
|
--SELECT * FROM ASS_GROUP
|
|
115
|
IF(
|
|
116
|
SELECT COUNT(M.BRANCH_ID)
|
|
117
|
FROM ASS_LIQUIDATION L
|
|
118
|
LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
|
|
119
|
LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
|
|
120
|
LEFT JOIN ASS_GROUP G ON M.GROUP_ID =G.GROUP_ID
|
|
121
|
LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
|
|
122
|
LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
|
|
123
|
LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
|
|
124
|
WHERE
|
|
125
|
(L.AUTH_STATUS_KT='A')
|
|
126
|
AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime, @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate = '')
|
|
127
|
AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime, @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate = '')
|
|
128
|
AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')
|
|
129
|
AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom = '')
|
|
130
|
AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo = '')
|
|
131
|
AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId = '')
|
|
132
|
AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID = '')
|
|
133
|
AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID = '')
|
|
134
|
AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
|
|
135
|
OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
|
|
136
|
AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
|
|
137
|
GROUP BY M.BRANCH_ID) = 1
|
|
138
|
BEGIN
|
|
139
|
SELECT TOP 1 L.MAKER_ID_KT AS GDV, L.CHECKER_ID_KT AS KSV
|
|
140
|
FROM ASS_LIQUIDATION L
|
|
141
|
LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
|
|
142
|
LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
|
|
143
|
LEFT JOIN ASS_GROUP G ON M.GROUP_ID =G.GROUP_ID
|
|
144
|
LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
|
|
145
|
LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
|
|
146
|
LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
|
|
147
|
WHERE
|
|
148
|
(L.AUTH_STATUS_KT='A')
|
|
149
|
AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime, @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate = '')
|
|
150
|
AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime, @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate = '')
|
|
151
|
AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')
|
|
152
|
AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom = '')
|
|
153
|
AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo = '')
|
|
154
|
AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId = '')
|
|
155
|
AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID = '')
|
|
156
|
AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID = '')
|
|
157
|
AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
|
|
158
|
OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
|
|
159
|
AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
|
|
160
|
END
|
|
161
|
END
|