|
1
|
ALTER PROCEDURE [dbo].[rpt_PAY_ENTRIES_POST_REFNO_ISNULL]
|
|
2
|
@p_REQ_PAY_CODE VARCHAR(50) = NULL,
|
|
3
|
@p_FROMDATE VARCHAR(50) = NULL,
|
|
4
|
@p_TODATE VARCHAR(50) = NULL,
|
|
5
|
@p_REQ_REASON NVARCHAR(MAX) = NULL,
|
|
6
|
@p_MAKER_ID VARCHAR(100) = NULL,
|
|
7
|
@p_UPDER_ID VARCHAR(100) = NULL,
|
|
8
|
@p_CHECKER_ID VARCHAR(100) = NULL,
|
|
9
|
@p_LEVEL VARCHAR(100) = NULL,
|
|
10
|
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
11
|
@p_USER_LOGIN VARCHAR(100) = NULL
|
|
12
|
AS
|
|
13
|
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
|
|
14
|
INSERT INTO @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
15
|
IF(@p_LEVEL = 'UNIT')
|
|
16
|
BEGIN
|
|
17
|
DELETE FROM @tmp
|
|
18
|
INSERT INTO @tmp(BRANCH_ID) VALUES (@p_BRANCH_LOGIN)
|
|
19
|
END
|
|
20
|
DECLARE @DEP_CODE_LG VARCHAR(15) ='', @BRANCH_TYPE VARCHAR(15)=''
|
|
21
|
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
22
|
SET @DEP_CODE_LG =( SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
23
|
--TABLE 0
|
|
24
|
SELECT CHAR(10)+ CONVERT(CHAR, ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS STT,
|
|
25
|
CHAR(10)+ CC.[TYPE] + CHAR(10) AS 'TYPE',
|
|
26
|
CHAR(10)+ CC.REQ_PAY_CODE + CHAR(10) AS 'REQ_PAY_CODE',
|
|
27
|
CHAR(10)+ CC.REQ_REASON + CHAR(10) AS 'REQ_REASON',
|
|
28
|
CHAR(10) + FORMAT(CC.APPROVE_DT_KT,'dd/MM/yyyy') + CHAR(10) AS 'APPR_DT_KSV',
|
|
29
|
CHAR(10)+ CC.CCY + CHAR(10) AS 'CCY',
|
|
30
|
CHAR(10)+ REPLACE (FORMAT ((CC.REQ_AMT),'#,###'),',',',') + CHAR(10) AS 'REQ_AMT',
|
|
31
|
CHAR(10) + REPLACE (FORMAT ((CC.AMT_SUCCESS),'#,###'),',',',') + CHAR(10) AS 'AMT_SUCCESS',
|
|
32
|
CHAR(10) + REPLACE (FORMAT ((CC.AMT_FAIL),'#,###'),',',',')+ CHAR(10) AS 'AMT_FAIL',
|
|
33
|
CHAR(10)+ CC.GDV + CHAR(10) AS 'GDV',
|
|
34
|
CHAR(10)+ CC.KSV + CHAR(10) AS 'KSV',
|
|
35
|
CHAR(10)+ CC.BRANCH_CODE + CHAR(10) AS 'BRANCH_CREATE'
|
|
36
|
FROM(
|
|
37
|
SELECT 'TT' AS 'TYPE', B.*,A.REQ_REASON,UGDV.TLFullName AS GDV, UKSV.TLFullName AS KSV,A.REQ_PAY_CODE,A.APPROVE_DT_KT,BRA.BRANCH_CODE ,
|
|
38
|
A.REQ_AMT,BRA.BRANCH_ID,A.AUTH_STATUS_KT,A.DEP_ID,A.MAKER_ID,A.MAKER_ID_KT,A.CHECKER_ID_KT
|
|
39
|
FROM TR_REQ_PAYMENT A
|
|
40
|
INNER JOIN (SELECT REQ_PAY_ID,ISNULL( SUM(CASE WHEN REF_NO IS NULL OR REF_NO ='' THEN AMT END),0) AS AMT_FAIL,
|
|
41
|
ISNULL(SUM(CASE WHEN REF_NO IS NOT NULL OR REF_NO <>'' THEN AMT END),0) AS AMT_SUCCESS,'VND' AS CCY
|
|
42
|
FROM PAY_ENTRIES_POST A
|
|
43
|
INNER JOIN TR_REQ_PAYMENT B ON A.TRN_ID = B.REQ_PAY_ID
|
|
44
|
WHERE REF_NO IS NULL
|
|
45
|
GROUP BY REQ_PAY_ID) B ON B.REQ_PAY_ID = A.REQ_PAY_ID
|
|
46
|
LEFT JOIN TL_USER UGDV ON UGDV.TLNANME = A.MAKER_ID_KT
|
|
47
|
LEFT JOIN TL_USER UKSV ON UKSV.TLNANME = A.CHECKER_ID_KT
|
|
48
|
LEFT JOIN CM_BRANCH BRA ON BRA.BRANCH_ID = A.BRANCH_CREATE
|
|
49
|
UNION ALL
|
|
50
|
SELECT 'TU' AS TYPE, B.*,A.REQ_REASON,UGDV.TLFullName AS GDV, UKSV.TLFullName AS KSV,A.REQ_PAY_CODE,A.APPROVE_DT_KT,BRA.BRANCH_CODE ,
|
|
51
|
A.REQ_AMT,BRA.BRANCH_ID,A.AUTH_STATUS_KT,A.DEP_ID,A.MAKER_ID,A.MAKER_ID_KT,A.CHECKER_ID_KT
|
|
52
|
FROM dbo.TR_REQ_ADVANCE_PAYMENT A
|
|
53
|
INNER JOIN (SELECT REQ_PAY_ID,ISNULL( SUM(CASE WHEN REF_NO IS NULL OR REF_NO ='' THEN AMT END),0) AS AMT_FAIL,
|
|
54
|
ISNULL(SUM(CASE WHEN REF_NO IS NOT NULL OR REF_NO <>'' THEN AMT END),0) AS AMT_SUCCESS,'VND' AS CCY
|
|
55
|
FROM PAY_ENTRIES_POST A
|
|
56
|
INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.TRN_ID = B.REQ_PAY_ID
|
|
57
|
WHERE REF_NO IS NULL
|
|
58
|
GROUP BY REQ_PAY_ID) B ON B.REQ_PAY_ID = A.REQ_PAY_ID
|
|
59
|
LEFT JOIN TL_USER UGDV ON UGDV.TLNANME = A.MAKER_ID_KT
|
|
60
|
LEFT JOIN TL_USER UKSV ON UKSV.TLNANME = A.CHECKER_ID_KT
|
|
61
|
LEFT JOIN CM_BRANCH BRA ON BRA.BRANCH_ID = A.BRANCH_CREATE
|
|
62
|
) AS CC
|
|
63
|
WHERE (CC.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
|
|
64
|
AND (CC.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
|
|
65
|
AND (CC.MAKER_ID = @p_MAKER_ID OR @p_MAKER_ID ='' OR @p_MAKER_ID IS NULL)
|
|
66
|
AND (CC.MAKER_ID_KT = @p_UPDER_ID OR @p_UPDER_ID ='' OR @p_UPDER_ID IS NULL)
|
|
67
|
AND (CC.CHECKER_ID_KT = @p_CHECKER_ID OR @p_CHECKER_ID ='' OR @p_CHECKER_ID IS NULL)
|
|
68
|
AND (CC.BRANCH_ID = @p_BRANCH_LOGIN OR @p_BRANCH_LOGIN ='' OR @p_BRANCH_LOGIN IS NULL
|
|
69
|
OR (@BRANCH_TYPE <> 'HS' AND
|
|
70
|
EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT
|
|
71
|
WHERE REQ_PAY_ID = CC.REQ_PAY_ID AND CC.BRANCH_ID = @p_BRANCH_LOGIN AND REF_ID IN
|
|
72
|
(SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
73
|
AND AUTH_STATUS_KT ='A')))
|
|
74
|
AND (CONVERT(DATE, CC.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103) OR ISNULL(@p_FromDate, '') = '')
|
|
75
|
AND (CONVERT(DATE, CC.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103) OR ISNULL(@p_ToDate, '') = '')
|
|
76
|
AND ((CC.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
|
|
77
|
OR ( @BRANCH_TYPE <> 'HS' AND CC.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp) AND
|
|
78
|
EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT
|
|
79
|
WHERE REQ_PAY_ID = CC.REQ_PAY_ID AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) AND AUTH_STATUS_KT ='A')))
|
|
80
|
AND((@DEP_CODE_LG ='DEP000000000022' AND CC.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
|
|
81
|
OR (@BRANCH_TYPE ='HS' AND CC.BRANCH_ID = @p_BRANCH_LOGIN AND CC.DEP_ID = @DEP_CODE_LG)
|
|
82
|
OR(@BRANCH_TYPE <>'HS' AND CC.BRANCH_ID = @p_BRANCH_LOGIN)
|
|
83
|
OR(@BRANCH_TYPE <> 'HS' AND
|
|
84
|
EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT
|
|
85
|
WHERE REQ_PAY_ID = CC.REQ_PAY_ID AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) AND AUTH_STATUS_KT ='A'))
|
|
86
|
)
|
|
87
|
ORDER BY CC.APPROVE_DT_KT DESC;
|
|
88
|
|
|
89
|
SELECT N' TỪ NGÀY '+ FORMAT (CONVERT(DATETIME,@p_FROMDATE,103),'dd/MM/yyyy')+
|
|
90
|
N' ĐẾN NGÀY '+ FORMAT (CONVERT(DATETIME,@p_TODATE,103),'dd/MM/yyyy') AS FROM_DT_TO_DT,
|
|
91
|
N'Đơn vị thực hiện sao kê : '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN) AS DVTH
|