|
1
|
CREATE FUNCTION [dbo].[FN_GET_PAYMENT_OF_ADVANCE_PAYMENT](@p_REQ_PAY_ID VARCHAR(20))
|
|
2
|
RETURNS NVARCHAR(500)
|
|
3
|
AS
|
|
4
|
BEGIN
|
|
5
|
DECLARE @l_Result NVARCHAR(500)
|
|
6
|
|
|
7
|
SET @l_Result = STUFF (
|
|
8
|
( SELECT ',' + B.REQ_PAY_CODE
|
|
9
|
FROM TR_REQ_PAYMENT_DT A
|
|
10
|
LEFT JOIN TR_REQ_PAYMENT B ON A.PAY_ID = B.REQ_PAY_ID
|
|
11
|
WHERE 1=1
|
|
12
|
AND A.PAY_ADV_ID = @p_REQ_PAY_ID
|
|
13
|
AND A.AUTH_STATUS_KT = 'A'
|
|
14
|
FOR xml path('')
|
|
15
|
), 1, 1, ''
|
|
16
|
)
|
|
17
|
|
|
18
|
RETURN @l_Result
|
|
19
|
END
|
|
20
|
|
|
21
|
GO
|
|
22
|
|
|
23
|
SELECT A.REQ_PAY_ID, A.REQ_PAY_CODE,H.REQ_PAY_ID ID_TT , H.REQ_PAY_CODE SO_PHIEU_HOAN_UNG, B.BRANCH_CODE MA_DON_VI, B.BRANCH_NAME TEN_DON_VI, A.CREATE_DT NGAY_TAO, A.APPROVE_DT_KT NGAY_DUYET_KT, A.MAKER_ID + ' - ' + F.TLFullName NGUOI_TAM_UNG,
|
|
24
|
E.CUSTOMER_NAME TEN_KHACH_HANG, E.CMND, A.REQ_REASON NOI_DUNG, A.REQ_AMT SO_TIEN, A.PAY_AMT SO_TIEN_DA_HOAN_UNG,
|
|
25
|
CASE
|
|
26
|
WHEN ISNULL(A.REQ_AMT, 0) = ISNULL(A.PAY_AMT, 0) THEN N'Đã hoàn ứng'
|
|
27
|
ELSE N'Chưa hoàn ứng xong'
|
|
28
|
END TRANG_THAI_HOAN_UNG,
|
|
29
|
[dbo].[FN_GET_PAYMENT_OF_ADVANCE_PAYMENT](A.REQ_PAY_ID) SO_PHIEU_HOAN_UNG
|
|
30
|
FROM TR_REQ_ADVANCE_PAYMENT A
|
|
31
|
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
|
|
32
|
LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
|
|
33
|
LEFT JOIN TR_REQ_ADVANCE_CUS D ON A.REQ_PAY_ID = D.REQ_PAY_ID
|
|
34
|
LEFT JOIN LS_CUSTOMER E ON D.CUST_ID = E.CUSTOMER_ID
|
|
35
|
LEFT JOIN TL_USER F ON A.MAKER_ID = F.TLNANME
|
|
36
|
LEFT JOIN TR_REQ_PAYMENT_DT G ON A.REQ_PAY_ID = G.PAY_ADV_ID AND G.AUTH_STATUS_KT = 'A'
|
|
37
|
LEFT JOIN TR_REQ_PAYMENT H ON G.PAY_ID = H.REQ_PAY_ID AND H.AUTH_STATUS_KT = 'A'
|
|
38
|
WHERE 1=1
|
|
39
|
AND A.REQ_TYPE = 'D' AND A.AUTH_STATUS_KT = 'A'
|
|
40
|
ORDER BY TRANG_THAI_HOAN_UNG DESC, NGAY_DUYET_KT DESC, REQ_PAY_ID
|
|
41
|
|
|
42
|
GO
|
|
43
|
|
|
44
|
--23062023_secretkey
|