|
1
|
|
|
2
|
ALTER PROC [dbo].[rpt_TR_REQ_PAY_Inventory]
|
|
3
|
@p_REQ_PAY_CODE varchar(50) = NULL,
|
|
4
|
@p_REQ_TYPE varchar(15) = NULL,
|
|
5
|
@p_REF_ID varchar(15) = NULL,
|
|
6
|
@p_DEP_ID varchar(15) = NULL,
|
|
7
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
8
|
@p_LEVEL varchar(10) = NULL,
|
|
9
|
@p_FromDate VARCHAR(20) = NULL,
|
|
10
|
@p_ToDate VARCHAR(20) = NULL,
|
|
11
|
--@p_PO_CODE varchar(15) = NULL,
|
|
12
|
--@p_REQ_REASON nvarchar(MAX) = NULL,
|
|
13
|
--@p_BRANCH_CREATE varchar(15) = NULL,
|
|
14
|
--@p_REF_ID varchar(15) = NULL,
|
|
15
|
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
16
|
@p_USER_LOGIN VARCHAR(15)= NULL,
|
|
17
|
@p_IS_REQ_AD VARCHAR(5) = NULL,
|
|
18
|
@p_IS_REQ_PAY VARCHAR(5) = NULL
|
|
19
|
AS
|
|
20
|
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
|
|
21
|
INSERT INTO @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
22
|
DECLARE @DEP_CODE_LG VARCHAR(15) ='', @BRANCH_TYPE VARCHAR(15)=''
|
|
23
|
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
24
|
SET @DEP_CODE_LG =( SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
25
|
BEGIN
|
|
26
|
SELECT
|
|
27
|
ROW_NUMBER() OVER (ORDER BY KQ.REQ_PAY_CODE DESC) AS STT,KQ.* FROM
|
|
28
|
(
|
|
29
|
|
|
30
|
SELECT 'TT' AS [TYPE], A.REQ_PAY_CODE, A.REQ_REASON,A.APPROVE_DT_KT,'(VNĐ)' AS CURRENCY,A.REQ_AMT ,B.TLFullName + '/VCCB' AS GDV , C.TLFullName + '/VCCB' AS KSV , D.BRANCH_CODE AS DON_VI
|
|
31
|
FROM TR_REQ_PAYMENT A
|
|
32
|
JOIN TL_USER B ON A.MAKER_ID_KT = B.TLNANME
|
|
33
|
JOIN TL_USER C ON A.CHECKER_ID_KT = C.TLNANME
|
|
34
|
JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
35
|
WHERE 1=1
|
|
36
|
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
|
|
37
|
AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
|
|
38
|
AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
|
|
39
|
AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
|
|
40
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID ='' OR @p_BRANCH_ID IS NULL)
|
|
41
|
AND CONVERT(DATE, A.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103)
|
|
42
|
AND CONVERT(DATE, A.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103)
|
|
43
|
AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp)
|
|
44
|
AND((@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))
|
|
45
|
OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
|
|
46
|
OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN))
|
|
47
|
AND (@p_IS_REQ_PAY='Y')
|
|
48
|
UNION
|
|
49
|
SELECT 'TU' 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
|
|
50
|
FROM TR_REQ_ADVANCE_PAYMENT A
|
|
51
|
JOIN TL_USER B ON A.MAKER_ID_KT = B.TLNANME
|
|
52
|
JOIN TL_USER C ON A.CHECKER_ID_KT = C.TLNANME
|
|
53
|
JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
54
|
WHERE 1=1
|
|
55
|
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
|
|
56
|
AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
|
|
57
|
AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
|
|
58
|
AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
|
|
59
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID ='' OR @p_BRANCH_ID IS NULL)
|
|
60
|
AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) >= CONVERT(DATE, @p_FromDate, 103))
|
|
61
|
AND (CONVERT(DATE, A.APPROVE_DT_KT, 103) <= CONVERT(DATE, @p_ToDate, 103))
|
|
62
|
AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp)
|
|
63
|
AND((@DEP_CODE_LG ='DEP000000000022' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))
|
|
64
|
OR (@BRANCH_TYPE ='HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN AND A.DEP_ID = @DEP_CODE_LG)
|
|
65
|
OR(@BRANCH_TYPE <>'HS' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN))
|
|
66
|
AND (@p_IS_REQ_AD='Y')
|
|
67
|
) AS KQ ORDER BY KQ.TYPE
|
|
68
|
END
|
|
69
|
|