|
1
|
USE [VCCB_AMS]
|
|
2
|
GO
|
|
3
|
/****** Object: StoredProcedure [dbo].[TR_REQ_PAY_GetPOSchedule] Script Date: 1/30/2023 9:45:34 AM ******/
|
|
4
|
SET ANSI_NULLS ON
|
|
5
|
GO
|
|
6
|
SET QUOTED_IDENTIFIER ON
|
|
7
|
GO
|
|
8
|
ALTER PROC [dbo].[TR_REQ_PAY_GetPOSchedule]
|
|
9
|
@p_REF_ID VARCHAR(150) = NULL,
|
|
10
|
@p_TOP INT = 1000
|
|
11
|
AS
|
|
12
|
BEGIN -- PAGING
|
|
13
|
-- NHIEU PO HOAC HOP DONG
|
|
14
|
DECLARE @l_LSTSERI TABLE (
|
|
15
|
[ID] [int] IDENTITY(1,1) NOT NULL,
|
|
16
|
[VALUE] [NVARCHAR](MAX) NULL)
|
|
17
|
DECLARE @l_FILENAME VARCHAR(MAX)
|
|
18
|
INSERT INTO @l_LSTSERI SELECT VALUE FROM WSISPLIT(@p_REF_ID,',')
|
|
19
|
|
|
20
|
IF(@p_TOP IS NULL OR @p_TOP='' OR @p_TOP=0)
|
|
21
|
BEGIN
|
|
22
|
-- PAGING BEGIN
|
|
23
|
SELECT AA.*
|
|
24
|
-- SELECT END
|
|
25
|
FROM
|
|
26
|
(
|
|
27
|
SELECT A.PO_ID AS REF_ID,P.PO_CODE AS REF_CODE, A.PAY_ID,A.AMOUNT, A.EXP_DT,A.[PERCENT], A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
|
|
28
|
ISNULL((A.AMOUNT - (ISNULL(B.TONG_TAM_UNG,0)- ISNULL(B.TONG_TAM_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS, '' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES, 0.0 AS AMT_PAY_REAL,
|
|
29
|
'' AS CURRENCY, 0.0 AS RATE, '' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID, B.REQ_PAY_CODE, '' AS RECORD_STATUS, A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
|
|
30
|
FROM TR_PO_PAYMENT A
|
|
31
|
-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
|
|
32
|
LEFT JOIN
|
|
33
|
(
|
|
34
|
SELECT B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID, B1.PAY_PHASE,SUM(B1.AMT_PAY_REAL) AS TONG_TAM_UNG
|
|
35
|
FROM TR_REQ_PAY_SCHEDULE B1
|
|
36
|
LEFT JOIN TR_REQ_ADVANCE_PAYMENT B2 ON B1.REQ_PAY_ID = B2.REQ_PAY_ID
|
|
37
|
WHERE B1.AUTH_STATUS_KT='A' AND B1.TRN_TYPE='ADV_PAY'
|
|
38
|
GROUP BY B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID,B1.PAY_PHASE
|
|
39
|
)B ON A.PAY_ID = B.PAY_ID
|
|
40
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
41
|
LEFT JOIN
|
|
42
|
(
|
|
43
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
|
|
44
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
45
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A'
|
|
46
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
47
|
) SH ON A.PAY_ID = SH.PAY_ID
|
|
48
|
-- LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
|
|
49
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
50
|
LEFT JOIN
|
|
51
|
(
|
|
52
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
|
|
53
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
54
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
|
|
55
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
56
|
) TU ON A.PAY_ID = TU.PAY_ID
|
|
57
|
--- DIEU KIEN
|
|
58
|
LEFT JOIN TR_PO_MASTER P ON A.PO_ID = P.PO_ID
|
|
59
|
WHERE A.PO_ID IN (SELECT VALUE FROM @l_LSTSERI)
|
|
60
|
UNION
|
|
61
|
SELECT A.CONTRACT_ID AS REF_ID,C.CONTRACT_CODE AS REF_CODE,A.PAY_ID, A.AMOUNT,A.EXPECTED_DT AS EXP_DT, A.[PERCENT],A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
|
|
62
|
ISNULL((A.AMOUNT - (ISNULL(HU.TONG_HOAN_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS,'' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES,0.0 AS AMT_PAY_REAL,
|
|
63
|
'' AS CURRENCY, 0.0 AS RATE,'' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID, B.REQ_PAY_CODE, '' AS RECORD_STATUS,A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
|
|
64
|
FROM TR_CONTRACT_PAYMENT A
|
|
65
|
-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
|
|
66
|
LEFT JOIN
|
|
67
|
(
|
|
68
|
SELECT B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID, B1.PAY_PHASE,SUM(B1.AMT_PAY_REAL) AS TONG_TAM_UNG
|
|
69
|
FROM TR_REQ_PAY_SCHEDULE B1
|
|
70
|
LEFT JOIN TR_REQ_ADVANCE_PAYMENT B2 ON B1.REQ_PAY_ID = B2.REQ_PAY_ID
|
|
71
|
WHERE B1.AUTH_STATUS_KT='A' AND B1.TRN_TYPE='ADV_PAY'
|
|
72
|
GROUP BY B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID,B1.PAY_PHASE
|
|
73
|
)B ON A.PAY_ID = B.PAY_ID
|
|
74
|
-- LAY SO TIEN DA HOAN UNG CUA KI DO
|
|
75
|
LEFT JOIN
|
|
76
|
(
|
|
77
|
SELECT PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_HOAN_UNG
|
|
78
|
FROM TR_REQ_PAY_SCHEDULE
|
|
79
|
WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' AND PROCESS='2'
|
|
80
|
GROUP BY PAY_ID, PAY_PHASE
|
|
81
|
)HU ON A.PAY_ID = HU.PAY_ID
|
|
82
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
83
|
LEFT JOIN
|
|
84
|
(
|
|
85
|
SELECT P.PAY_ID, P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
|
|
86
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
87
|
WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE ='PAY'
|
|
88
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
89
|
) SH ON A.PAY_ID = SH.PAY_ID
|
|
90
|
---LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
|
|
91
|
LEFT JOIN
|
|
92
|
(
|
|
93
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
|
|
94
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
95
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
|
|
96
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
97
|
) TU ON A.PAY_ID = TU.PAY_ID
|
|
98
|
--- DIEU KIEN
|
|
99
|
INNER JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID
|
|
100
|
WHERE A.CONTRACT_ID IN (SELECT VALUE FROM @l_LSTSERI) --- Nghia la REF_ID truyen xuong
|
|
101
|
) AA
|
|
102
|
WHERE 1 = 1
|
|
103
|
-- PAGING END
|
|
104
|
END;
|
|
105
|
ELSE
|
|
106
|
BEGIN
|
|
107
|
-- PAGING BEGIN
|
|
108
|
SELECT AA.*
|
|
109
|
-- SELECT END
|
|
110
|
FROM
|
|
111
|
(
|
|
112
|
SELECT A.PO_ID AS REF_ID,P.PO_CODE AS REF_CODE, A.PAY_ID,A.AMOUNT, A.EXP_DT,A.[PERCENT], A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
|
|
113
|
ISNULL((A.AMOUNT - (ISNULL(B.TONG_TAM_UNG,0)- ISNULL(B.TONG_TAM_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS, '' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES, 0.0 AS AMT_PAY_REAL,
|
|
114
|
'' AS CURRENCY, 0.0 AS RATE, '' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID,'' AS RECORD_STATUS, A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
|
|
115
|
FROM TR_PO_PAYMENT A
|
|
116
|
-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
|
|
117
|
LEFT JOIN
|
|
118
|
(
|
|
119
|
SELECT REQ_PAY_ID, PAY_ID, PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_TAM_UNG
|
|
120
|
FROM TR_REQ_PAY_SCHEDULE
|
|
121
|
WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY'
|
|
122
|
GROUP BY REQ_PAY_ID,PAY_ID,PAY_PHASE
|
|
123
|
)B ON A.PAY_ID = B.PAY_ID
|
|
124
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
125
|
LEFT JOIN
|
|
126
|
(
|
|
127
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
|
|
128
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
129
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A'
|
|
130
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
131
|
) SH ON A.PAY_ID = SH.PAY_ID
|
|
132
|
-- LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
|
|
133
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
134
|
LEFT JOIN
|
|
135
|
(
|
|
136
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
|
|
137
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
138
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
|
|
139
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
140
|
) TU ON A.PAY_ID = TU.PAY_ID
|
|
141
|
--- DIEU KIEN
|
|
142
|
LEFT JOIN TR_PO_MASTER P ON A.PO_ID = P.PO_ID
|
|
143
|
WHERE A.PO_ID IN (SELECT VALUE FROM @l_LSTSERI)
|
|
144
|
UNION
|
|
145
|
SELECT A.CONTRACT_ID AS REF_ID,C.CONTRACT_CODE AS REF_CODE,A.PAY_ID, A.AMOUNT,A.EXPECTED_DT AS EXP_DT, A.[PERCENT],A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
|
|
146
|
ISNULL((A.AMOUNT - (ISNULL(HU.TONG_HOAN_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS,'' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES,0.0 AS AMT_PAY_REAL,
|
|
147
|
'' AS CURRENCY, 0.0 AS RATE,'' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID, '' AS RECORD_STATUS,A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
|
|
148
|
FROM TR_CONTRACT_PAYMENT A
|
|
149
|
-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
|
|
150
|
LEFT JOIN
|
|
151
|
(
|
|
152
|
SELECT REQ_PAY_ID, PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_TAM_UNG
|
|
153
|
FROM TR_REQ_PAY_SCHEDULE
|
|
154
|
WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY'
|
|
155
|
GROUP BY REQ_PAY_ID, PAY_ID, PAY_PHASE
|
|
156
|
)B ON A.PAY_ID = B.PAY_ID
|
|
157
|
-- LAY SO TIEN DA HOAN UNG CUA KI DO
|
|
158
|
LEFT JOIN
|
|
159
|
(
|
|
160
|
SELECT PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_HOAN_UNG
|
|
161
|
FROM TR_REQ_PAY_SCHEDULE
|
|
162
|
WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' AND PROCESS='2'
|
|
163
|
GROUP BY PAY_ID, PAY_PHASE
|
|
164
|
)HU ON A.PAY_ID = HU.PAY_ID
|
|
165
|
---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
|
|
166
|
LEFT JOIN
|
|
167
|
(
|
|
168
|
SELECT P.PAY_ID, P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
|
|
169
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
170
|
WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE ='PAY'
|
|
171
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
172
|
) SH ON A.PAY_ID = SH.PAY_ID
|
|
173
|
---LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
|
|
174
|
LEFT JOIN
|
|
175
|
(
|
|
176
|
SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
|
|
177
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
178
|
WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
|
|
179
|
GROUP BY P.PAY_ID,P.PAY_PHASE
|
|
180
|
) TU ON A.PAY_ID = TU.PAY_ID
|
|
181
|
--- DIEU KIEN
|
|
182
|
INNER JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID
|
|
183
|
WHERE A.CONTRACT_ID IN (SELECT VALUE FROM @l_LSTSERI) --- Nghia la REF_ID truyen xuong
|
|
184
|
) AA
|
|
185
|
WHERE 1 = 1
|
|
186
|
-- PAGING END
|
|
187
|
END;
|
|
188
|
END -- PAGING
|