|
1
|
ALTER PROC [dbo].[rpt_TR_REQ_PAY_AUTO_Inventory]
|
|
2
|
@p_REQ_PAY_CODE varchar(50) = NULL,
|
|
3
|
@p_REQ_PAY_AUTO_TYPE varchar(15) = NULL,
|
|
4
|
@p_REF_ID varchar(15) = NULL,
|
|
5
|
@p_DEP_ID varchar(15) = NULL,
|
|
6
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
7
|
@p_LEVEL varchar(10) = NULL,
|
|
8
|
@p_FromDate VARCHAR(20) = NULL,
|
|
9
|
@p_ToDate VARCHAR(20) = NULL,
|
|
10
|
--@p_PO_CODE varchar(15) = NULL,
|
|
11
|
@p_REASON nvarchar(MAX) = NULL,
|
|
12
|
--@p_BRANCH_CREATE varchar(15) = NULL,
|
|
13
|
--@p_REF_ID varchar(15) = NULL,
|
|
14
|
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
15
|
@p_USER_LOGIN VARCHAR(15)= NULL,
|
|
16
|
@p_IS_REQ_AD VARCHAR(5) = NULL,
|
|
17
|
@p_IS_REQ_PAY VARCHAR(5) = NULL
|
|
18
|
AS
|
|
19
|
DECLARE @DEP_CODE_LG VARCHAR(15) ='', @BRANCH_TYPE VARCHAR(15)=''
|
|
20
|
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
21
|
SET @DEP_CODE_LG =( SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
22
|
|
|
23
|
-- DOANPTT 280922: CHO PHEP PHONG KE TOAN TIM KIEM THEO DON VI DUOC CHON O GIAO DIEN. DOI VOI DON VI KINH DOANH THI KHONG TIM KIEM THEO PHONG BAN
|
|
24
|
IF(@DEP_CODE_LG = 'DEP000000000022')
|
|
25
|
BEGIN
|
|
26
|
SET @p_BRANCH_LOGIN = @p_BRANCH_ID
|
|
27
|
END
|
|
28
|
ELSE
|
|
29
|
BEGIN
|
|
30
|
SET @p_LEVEL = 'UNIT'
|
|
31
|
IF(@BRANCH_TYPE <> 'HS')
|
|
32
|
BEGIN
|
|
33
|
SET @p_DEP_ID = ''
|
|
34
|
END
|
|
35
|
END
|
|
36
|
|
|
37
|
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
|
|
38
|
INSERT INTO @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
39
|
IF(@p_LEVEL LIKE 'UNIT')
|
|
40
|
BEGIN
|
|
41
|
DELETE FROM @tmp
|
|
42
|
INSERT INTO @tmp(BRANCH_ID) VALUES (@p_BRANCH_LOGIN)
|
|
43
|
END
|
|
44
|
BEGIN
|
|
45
|
SELECT
|
|
46
|
ROW_NUMBER() OVER (ORDER BY KQ.REQ_DT ASC) AS STT,KQ.* FROM
|
|
47
|
(
|
|
48
|
SELECT ALLCODE.CONTENT AS [TYPE], A.REQ_PAY_CODE, A.REQ_REASON,A.REQ_DT,'(VNĐ)' AS CURRENCY,A.REQ_AMT ,B.TLFullName + '/VCCB' AS GDV , C.TLFullName + '/VCCB' AS KSV , D.BRANCH_CODE AS DON_VI, G.BRANCH_CODE AS DON_VI_QUAN_LY_HOP_DONG, H.TLFullName + '/VCCB' AS GDV_AUTO , I.TLFullName + '/VCCB' AS KSV_AUTO,
|
|
49
|
CASE
|
|
50
|
WHEN F.REQ_PAY_AUTO_TYPE = 'A' THEN ALLCODE2.CONTENT
|
|
51
|
ELSE ALLCODE3.CONTENT
|
|
52
|
END LOAI_THANH_TOAN
|
|
53
|
FROM TR_REQ_PAYMENT A
|
|
54
|
JOIN TL_USER B ON A.MAKER_ID_KT = B.TLNANME
|
|
55
|
JOIN TL_USER C ON A.CHECKER_ID_KT = C.TLNANME
|
|
56
|
JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
57
|
LEFT JOIN CM_ALLCODE ALLCODE ON A.TYPE_AUTO = ALLCODE.CDVAL AND CDNAME = 'PAY_TYPE_AUTO' AND CDTYPE = 'REQ_AUTO'
|
|
58
|
LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS E ON A.REQ_PAY_ID = E.REQ_PAY_ID
|
|
59
|
LEFT JOIN TR_REQ_PAYMENT_AUTO F ON E.REQ_PAY_AUTO_ID = F.REQ_PAY_AUTO_ID
|
|
60
|
LEFT JOIN CM_BRANCH G ON F.BRANCH_MANAGE_ID = G.BRANCH_ID
|
|
61
|
LEFT JOIN TL_USER H ON F.MAKER_ID_KT = H.TLNANME
|
|
62
|
LEFT JOIN TL_USER I ON F.CHECKER_ID_KT = I.TLNANME
|
|
63
|
LEFT JOIN CM_ALLCODE ALLCODE2 ON F.REQ_PAY_AUTO_SERVICE_TYPE = ALLCODE2.CDVAL AND ALLCODE2.CDNAME = 'PAY_SER_AUTO_TS' AND ALLCODE2.CDTYPE = 'REQ_AUTO'
|
|
64
|
LEFT JOIN CM_ALLCODE ALLCODE3 ON F.REQ_PAY_AUTO_SERVICE_TYPE = ALLCODE2.CDVAL AND ALLCODE2.CDNAME = 'PAY_SER_AUTO' AND ALLCODE2.CDTYPE = 'REQ_AUTO'
|
|
65
|
WHERE 1=1
|
|
66
|
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
|
|
67
|
AND(A.TYPE_AUTO LIKE N'%'+@p_REQ_PAY_AUTO_TYPE+'%' OR @p_REQ_PAY_AUTO_TYPE = '' OR @p_REQ_PAY_AUTO_TYPE IS NULL)
|
|
68
|
AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103))
|
|
69
|
AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103))
|
|
70
|
AND(A.REQ_REASON LIKE N'%'+@p_REASON+'%' or @p_REASON='' OR @p_REASON IS NULL)
|
|
71
|
AND(A.DEP_ID = @p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
|
|
72
|
AND A.AUTH_STATUS = 'A'
|
|
73
|
AND A.AUTH_STATUS_KT = 'A'
|
|
74
|
AND A.IS_CREATE_AUTO = 'Y'
|
|
75
|
AND A.IS_CREATE_AUTO_DONE = 'Y'
|
|
76
|
|
|
77
|
AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
|
|
78
|
AND ( -- PHONG KE TOAN
|
|
79
|
(@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
|
|
80
|
OR ( @DEP_CODE_LG ='DEP000000000022'
|
|
81
|
AND EXISTS ( SELECT *
|
|
82
|
FROM TR_REQ_ADVANCE_DT
|
|
83
|
WHERE REQ_PAY_ID = A.REQ_PAY_ID
|
|
84
|
AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
|
|
85
|
AND A.AUTH_STATUS_KT ='A'
|
|
86
|
)-- CAC PHIEU DE NGHI CO LINK VOI HOP DONG MÀ DON VI KINH DOANH NAY QUAN LY
|
|
87
|
)
|
|
88
|
-- PHONG BAN THUOC HOI SO
|
|
89
|
OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
|
|
90
|
-- DVKD
|
|
91
|
OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN)
|
|
92
|
OR ( @BRANCH_TYPE <> 'HS'
|
|
93
|
AND EXISTS ( SELECT *
|
|
94
|
FROM TR_REQ_ADVANCE_DT
|
|
95
|
WHERE REQ_PAY_ID = A.REQ_PAY_ID
|
|
96
|
AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
97
|
AND A.AUTH_STATUS_KT ='A'
|
|
98
|
)-- CAC PHIEU DE NGHI CO LINK VOI HOP DONG MÀ DON VI KINH DOANH NAY QUAN LY
|
|
99
|
)
|
|
100
|
)
|
|
101
|
) AS KQ ORDER BY KQ.REQ_DT
|
|
102
|
END
|