|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_CONTRACT_PAYMENT_ById]
|
|
3
|
@CONTRACT_ID varchar(15)
|
|
4
|
AS
|
|
5
|
IF((SELECT CONT_TYPE FROM TR_CONTRACT WHERE CONTRACT_ID =@CONTRACT_ID) ='DK')
|
|
6
|
BEGIN
|
|
7
|
|
|
8
|
SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, C.APPROVE_DT_KT AS EXPECTED_DT,100.0 AS [PERCENT],
|
|
9
|
B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,B.REASON AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,C.APPROVE_DT_KT AS APPROVE_DT,
|
|
10
|
C.APPROVE_DT_KT AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE,
|
|
11
|
'' AS REQUEST_ID,'' AS CONTRACT_PARENT,
|
|
12
|
B.AMT_PAY AS AMT_ADVANCE, 0.0 AS AMT_PAY_DO,
|
|
13
|
CASE WHEN B.PROCESS ='2' THEN 'TTX' ELSE 'DTU' END AS CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK, 0.0 AS REMAIN_AMT
|
|
14
|
FROM TR_REQ_PAY_PERIOD B
|
|
15
|
INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON B.REQ_PAY_ID = C.REQ_PAY_ID -- 2021928 LUCTV EDIT
|
|
16
|
--LEFT JOIN
|
|
17
|
--(
|
|
18
|
-- SELECT A.AD_PAY_ID, SUM (A.AMT_PAY) AS SO_TIEN_HOAN_UNG FROM TR_REQ_PAY_PERIOD A
|
|
19
|
-- WHERE A.PAY_TYPE ='PAY' AND A.AUTH_STATUS_KT='A' AND ISNULL(AD_PAY_ID,'') <> ''
|
|
20
|
-- GROUP BY A.AD_PAY_ID
|
|
21
|
--) AS P_REVERT ON C.REQ_PAY_ID =P_REVERT.AD_PAY_ID
|
|
22
|
WHERE B.PAY_TYPE ='ADV_PAY' AND B.AUTH_STATUS_KT ='A' AND B.CONTRACT_ID =@CONTRACT_ID AND B.AUTH_STATUS_KT IS NOT NULL
|
|
23
|
AND (B.REQ_PAY_ID IN(SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE RECORD_STATUS <> '0'))
|
|
24
|
UNION
|
|
25
|
SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, C.APPROVE_DT_KT AS EXPECTED_DT,100 AS [PERCENT],
|
|
26
|
B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,B.REASON AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,C.APPROVE_DT_KT AS APPROVE_DT,
|
|
27
|
C.APPROVE_DT_KT AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE,
|
|
28
|
'' AS REQUEST_ID,'' AS CONTRACT_PARENT,
|
|
29
|
0.0 AS AMT_ADVANCE, B.AMT_PAY AS AMT_PAY_DO,
|
|
30
|
'TTX' CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK, 0.0 AS REMAIN_AMT
|
|
31
|
FROM TR_REQ_PAY_PERIOD B
|
|
32
|
INNER JOIN TR_REQ_PAYMENT C ON B.REQ_PAY_ID = C.REQ_PAY_ID
|
|
33
|
WHERE B.PAY_TYPE ='PAY'
|
|
34
|
AND B.PROCESS <> '2' -- chi hien cac phieu thanh toan khac hoan ung
|
|
35
|
AND B.AUTH_STATUS_KT ='A'
|
|
36
|
AND B.CONTRACT_ID =@CONTRACT_ID
|
|
37
|
ORDER BY C.APPROVE_DT_KT DESC
|
|
38
|
END
|
|
39
|
ELSE
|
|
40
|
BEGIN
|
|
41
|
SELECT A.PAY_ID
|
|
42
|
,A.CONTRACT_ID
|
|
43
|
,A.PAY_PHASE
|
|
44
|
,A.EXPECTED_DT
|
|
45
|
,A.[PERCENT]
|
|
46
|
,A.AMOUNT
|
|
47
|
,A.PAY_STATUS
|
|
48
|
,A.NOTES
|
|
49
|
,A.RECORD_STATUS
|
|
50
|
,A.MAKER_ID
|
|
51
|
,A.CREATE_DT
|
|
52
|
,A.AUTH_STATUS
|
|
53
|
,A.CHECKER_ID
|
|
54
|
,A.APPROVE_DT
|
|
55
|
,A.PAY_DT
|
|
56
|
,A.PAY_AMOUNT
|
|
57
|
,A.REQUEST_ID
|
|
58
|
,A.CONTRACT_PARENT
|
|
59
|
,A.BID_ID, ISNULL(B.AMT_ADVANCE,0) AS AMT_ADVANCE, ISNULL(B2.AMT_PAY_DO,0) AS AMT_PAY_DO ,
|
|
60
|
ISNULL(C.CDVAL,(CASE WHEN ISNULL(B.AMT_ADVANCE,0) >0 AND ISNULL(B2.AMT_PAY_DO,0) =0 THEN 'DTU' WHEN ISNULL(B2.AMT_PAY_DO,0) >0 AND ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) >0 THEN 'DTT' WHEN ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) =0 THEN 'TTX' ELSE 'CTT' END)) AS CDVAL,
|
|
61
|
C.CONTENT, ISNULL(B3.AMT_LINK,0) AS AMT_LINK, ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0) - ISNULL(B3.AMT_LINK,0)),0) AS REMAIN_AMT
|
|
62
|
,TRC.CONTRACT_CODE
|
|
63
|
FROM TR_CONTRACT_PAYMENT A
|
|
64
|
LEFT JOIN TR_CONTRACT TRC ON A.CONTRACT_ID = TRC.CONTRACT_ID
|
|
65
|
--- TINH SO TIEN DA TAM UNG
|
|
66
|
LEFT JOIN
|
|
67
|
(
|
|
68
|
SELECT P.PAY_ID , SUM(P.AMT_PAY_REAL) AMT_ADVANCE,
|
|
69
|
MAX(P.AMT_PAY_DO) AMT_PAY_DO, MAX(P.AMT_PAY) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS
|
|
70
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
71
|
WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY'
|
|
72
|
GROUP BY P.PAY_ID
|
|
73
|
)
|
|
74
|
B ON A.PAY_ID = B.PAY_ID
|
|
75
|
LEFT JOIN
|
|
76
|
(
|
|
77
|
SELECT P.PAY_ID , ISNULL(MAX(P.AMT_ADVANCE),0) AMT_ADVANCE,
|
|
78
|
ISNULL(SUM(P.AMT_PAY_REAL),0) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS
|
|
79
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
80
|
WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY'
|
|
81
|
GROUP BY P.PAY_ID
|
|
82
|
)
|
|
83
|
B2 ON A.PAY_ID = B2.PAY_ID
|
|
84
|
-- LEFT JOIN DE LAY SO TIEN LINK TOI PHIEU TAM UNG
|
|
85
|
LEFT JOIN
|
|
86
|
(
|
|
87
|
SELECT P.PAY_ID , ISNULL(MAX(P.AMT_REMAIN),0) AS AMT_LINK,
|
|
88
|
SUM((P.AMT_PAY_REAL)) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS
|
|
89
|
FROM TR_REQ_PAY_SCHEDULE P
|
|
90
|
WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
|
|
91
|
GROUP BY P.PAY_ID
|
|
92
|
)
|
|
93
|
B3 ON A.PAY_ID = B3.PAY_ID
|
|
94
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = B.PAYMENT_STATUS AND C.CDNAME = 'PAYMENT_STATUS'
|
|
95
|
WHERE A.CONTRACT_ID= @CONTRACT_ID
|
|
96
|
END
|