|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[rpt_RET_CN_PGD_RENT_Excel]
|
|
3
|
@p_FROM_DT VARCHAR(20) = null,
|
|
4
|
@p_TO_DT VARCHAR(20) = null,
|
|
5
|
@p_BRANCH_ID VARCHAR(15) = NULL,
|
|
6
|
@P_LEVEL VARCHAR(15) = 'ALL'
|
|
7
|
AS
|
|
8
|
BEGIN
|
|
9
|
DECLARE @t_BRANCH_TABLE TABLE(BRANCH_ID VARCHAR(15))
|
|
10
|
|
|
11
|
IF(@P_LEVEL = 'ALL')
|
|
12
|
BEGIN
|
|
13
|
INSERT INTO @t_BRANCH_TABLE SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
|
|
14
|
END
|
|
15
|
IF(@P_LEVEL = 'UNIT')
|
|
16
|
BEGIN
|
|
17
|
INSERT INTO @t_BRANCH_TABLE SELECT @p_BRANCH_ID
|
|
18
|
END
|
|
19
|
|
|
20
|
DECLARE @FROM_DT DATETIME = CONVERT(DATETIME, @p_FROM_DT,103),
|
|
21
|
@TO_DT DATETIME = CONVERT(DATETIME, @p_TO_DT,103)
|
|
22
|
|
|
23
|
DECLARE @t_RET_PRICE_TABLE TABLE (RET_R_H_ID VARCHAR(15), PRICE_END_DT DATETIME)
|
|
24
|
|
|
25
|
INSERT INTO @t_RET_PRICE_TABLE
|
|
26
|
SELECT RP.RET_R_H_ID,RP.PRICE_END_DT
|
|
27
|
FROM RET_PRICE RP
|
|
28
|
WHERE (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
|
|
29
|
AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
|
|
30
|
|
|
31
|
|
|
32
|
SELECT
|
|
33
|
ROW_NUMBER() OVER (ORDER BY A.RET_R_H_ID ASC) AS STT,
|
|
34
|
[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'KV') AS KHU_VUC,
|
|
35
|
[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'CN') AS CHI_NHANH,
|
|
36
|
[dbo].[FN_GET_CHINHANH](A.BRANCH_CREATE,'PGD') AS PGD,
|
|
37
|
A.RET_ADDR AS ADDRESS,
|
|
38
|
B.CONTRACT_CODE,
|
|
39
|
B.CONTRACT_ID,
|
|
40
|
A.START_DT,
|
|
41
|
A.END_DT,
|
|
42
|
-----GIÁ THUÊ(THÁNG) ĐÃ THUẾ KỲ ĐẦU TIÊN--------
|
|
43
|
(
|
|
44
|
SELECT TOP 1 ISNULL(RP.PRICE, 0)
|
|
45
|
FROM RET_PRICE RP
|
|
46
|
WHERE RP.RET_R_H_ID = A.RET_R_H_ID
|
|
47
|
)
|
|
48
|
AS INIT_PRICE,
|
|
49
|
0 AS INIT_VAT,
|
|
50
|
-----GIÁ THUÊ(THÁNG) ĐÃ THUẾ HIỆN TẠI--------
|
|
51
|
(
|
|
52
|
SELECT TOP 1 ISNULL(RP.PRICE, 0)
|
|
53
|
FROM RET_PRICE RP
|
|
54
|
WHERE RP.RET_R_H_ID = A.RET_R_H_ID
|
|
55
|
AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
|
|
56
|
AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
|
|
57
|
ORDER BY RP.PRICE_DT DESC
|
|
58
|
)
|
|
59
|
AS CURRENT_PRICE,
|
|
60
|
0 AS CURRENT_VAT,
|
|
61
|
(
|
|
62
|
SELECT TOP 1 RP.PAY_PHASE
|
|
63
|
FROM RET_PRICE RP
|
|
64
|
WHERE RP.RET_R_H_ID = A.RET_R_H_ID
|
|
65
|
AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
|
|
66
|
AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
|
|
67
|
ORDER BY RP.PRICE_DT DESC
|
|
68
|
)
|
|
69
|
AS PAY_PHASE,
|
|
70
|
(
|
|
71
|
SELECT TOP 1 RP.TOTAL_AMT_PAY_PHASE
|
|
72
|
FROM RET_PRICE RP
|
|
73
|
WHERE RP.RET_R_H_ID = A.RET_R_H_ID
|
|
74
|
AND (RP.PRICE_DT >= @FROM_DT OR @FROM_DT IS NULL OR @FROM_DT = '')
|
|
75
|
AND (RP.PRICE_END_DT <= @TO_DT OR @TO_DT IS NULL OR @TO_DT = '')
|
|
76
|
ORDER BY RP.PRICE_DT DESC
|
|
77
|
)
|
|
78
|
AS PAY_AMOUNT,
|
|
79
|
(
|
|
80
|
SELECT TOP 1 RP.TOTAL_AMT_PAY_PHASE
|
|
81
|
FROM RET_PRICE RP
|
|
82
|
WHERE RP.RET_R_H_ID = A.RET_R_H_ID
|
|
83
|
AND RP.PRICE_DT >= DATEADD(day, 1,
|
|
84
|
(
|
|
85
|
SELECT TOP 1 PRICE_END_DT
|
|
86
|
FROM @t_RET_PRICE_TABLE TP
|
|
87
|
WHERE TP.RET_R_H_ID = A.RET_R_H_ID
|
|
88
|
ORDER BY PRICE_END_DT DESC
|
|
89
|
)
|
|
90
|
)
|
|
91
|
)
|
|
92
|
AS AMT_NEXT_PAYMENT_PHASE
|
|
93
|
|
|
94
|
FROM REAL_ESTATE_R_H A
|
|
95
|
LEFT JOIN TR_CONTRACT B ON A.TR_CONTRACT_ID = B.CONTRACT_ID
|
|
96
|
|
|
97
|
WHERE A.AUTH_STATUS = 'A'
|
|
98
|
AND A.RECORD_STATUS = '1'
|
|
99
|
--AND (CONVERT(DATETIME,A.START_DT, 103) >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
100
|
--AND (CONVERT(DATETIME, A.END_DT, 103) <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
101
|
--AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @t_BRANCH_TABLE)
|
|
102
|
|
|
103
|
END
|
|
104
|
|
|
105
|
|