Project

General

Profile

Download (50.1 KB)

TR_REQ_ADVANCE_PAYMENT_SEARCH.txt

Luc Tran Van, 02/03/2021 10:57 AM

 
1 
??ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Search]
2 
@p_REQ_PAY_ID varchar(15)= NULL,
3 
@p_REQ_PAY_CODE varchar(50) = NULL,
4 
@p_REQ_DT VARCHAR(20)= NULL,
5 
@p_BRANCH_ID varchar(15) = NULL,
6 
@p_DEP_ID varchar(15) = NULL,
7 
@p_REQ_REASON nvarchar(MAX) = NULL,
8 
@p_REQ_TYPE varchar(15) = NULL,
9 
@P_REQ_ENTRIES nvarchar(MAX) = NULL,
10 
@p_REQ_DESCRIPTION nvarchar(MAX) = NULL,
11 
@p_REF_ID varchar(15) = NULL,
12 
@p_RECEIVER_PO nvarchar(250) = NULL,
13 
@p_RECEIVER_DEBIT nvarchar(250) = NULL,
14 
@p_REQ_PAY_TYPE varchar(15) = NULL,
15 
@p_REQ_TYPE_CURRENCY nvarchar(50) = NULL,
16 
@p_REQ_AMT decimal(18, 0) = NULL,
17 
@p_REQ_TEMP_AMT decimal(18, 0) = NULL,
18 
@p_MAKER_ID varchar(15) = NULL,
19 
@p_CREATE_DT varchar(25) = NULL,
20 
@p_EDITOR_ID varchar(15) = NULL,
21 
@p_AUTH_STATUS varchar(1) = NULL,
22 
@p_CHECKER_ID varchar(15) = NULL,
23 
@p_APPROVE_DT varchar(25) = NULL,
24 
@p_CREATE_DT_KT varchar(25) = NULL,
25 
@p_MAKER_ID_KT varchar(15) = NULL,
26 
@p_AUTH_STATUS_KT varchar(1) = NULL,
27 
@p_CHECKER_ID_KT varchar(1) = NULL,
28 
@p_APPROVE_DT_KT varchar(25)= null,
29 
@p_CORE_NOTE nvarchar(500) = NULL,
30 
@p_BRANCH_CREATE varchar(15) = NULL,
31 
@p_NOTES varchar(15) = NULL,
32 
@p_RECORD_STATUS varchar(1) = NULL,
33 
@p_TRANSFER_MAKER nvarchar(50) = NULL,
34 
@p_TRANSFER_DT varchar(25) = NULL,
35 
@p_TRASFER_USER_RECIVE varchar(15) = NULL,
36 
@p_PROCESS varchar(15) = NULL,
37 
@p_PAY_PHASE VARCHAR(15)= NULL,
38 
@p_XMP_TEMP XML = NULL,
39 
@p_TOP INT = 10,
40 
@p_LEVEL varchar(10) = NULL,
41 
@p_FRMDATE VARCHAR(20)= NULL,
42 
@p_TODATE VARCHAR(20) = NULL,
43 
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
44 
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
45 
@p_IS_TRANSFER VARCHAR(15)= NULL,
46 
@p_DVDM_ID VARCHAR(15)= NULL,
47 
@p_USER_LOGIN VARCHAR(15) = NULL,
48 
@p_RATE DECIMAL(18,0) =0,
49 
@p_FUNCTION VARCHAR(15) = NULL,
50 
@p_TYPE_SEARCH VARCHAR(15) = NULL,
51 
@p_TEMP_PAY_STATUS VARCHAR(10) = NULL
52 
--select * from CM_ALLCODE where CDTYPE = 'TEMPPAYSTATUS'
53 
--HT Ho?n t?t
54 
--DCHU ang ch? ho?n ?ng
55 
--DHU ? ho?n ?ng
56 
--'' t?t c?
57 
AS
58 
SET @p_TOP = NULL
59 
DECLARE @TB_TYPE TABLE (BRANCH_TYPE VARCHAR(15))
60 
IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_LOGIN)='HS')
61 
BEGIN
62 
INSERT INTO @TB_TYPE VALUES('HS')
63 
END
64 
ELSE
65 
BEGIN
66 
INSERT INTO @TB_TYPE VALUES('PGD')
67 
INSERT INTO @TB_TYPE VALUES('CN')
68 
END
69 
declare @tmp table(BRANCH_ID varchar(15))
70 
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
71 
declare @tmp_Login table(BRANCH_ID varchar(15))
72 
insert into @tmp_Login SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
73 
DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15)
74 
DECLARE @BRANCH_TYPE VARCHAR(15)
75 
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
76 
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
77 
SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
78 
DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15))
79 
SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG)
80 
SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG)
81 
print @DVDM_ID
82 
INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B WHERE COST_ID =@COST_LG
83 
DECLARE @BRANCH_TYPE_LG VARCHAR(15)
84 
SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
85
86 
--IF(EXISTS(SELECT * FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))
87 
-- SET @ROLE_ID=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID)
88 
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
89 
IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD'))
90 
BEGIN
91 
PRINT @ROLE_ID
92 
END
93 
ELSE
94 
BEGIN
95 
--SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)
96 
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)
97 
IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
98 
BEGIN
99 
SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN))
100 
END
101 
END
102 
-- KHAI BAO THEM ROLE NHAN UY QUYEN
103 
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))
104 
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID
105 
--AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
106 
-- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM
107 
DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))
108 
--INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN
109 
--AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
110 
IF(@p_TOP=NULL OR @p_TOP='' OR @p_TOP=0)BEGIN
111 
SELECT A.*,'' as TEMP_PAY_STATUS ,
112 
PR.PROCESS_DESC AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
113 
TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
114 
BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
115 
--Luanlt--2019/10/15-S?a AL,AL1
116 
BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,
117 
A.REQ_AMT AS TOTAL_AMT_TEMP,
118 
--CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(H.SOTIEN_TT,0) END AS TOTAL_AMT_PAY_HIS,
119 
ISNULL(A.PAY_AMT,0) AS TOTAL_AMT_PAY_HIS,
120 
TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE,
121 
TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
122 
PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME, PM.PAY_PHASE AS KY_TAM_UNG,
123 
DV.DVDM_CODE, DV.DVDM_NAME, PC.ROLE_USER AS ROLE_CONF, PR.PROCESS_DESC AS CONF_STATUS, PC1.NOTES AS NEXT_STEP, PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
124 
PC3.TLNAME AS EXEC_USER, TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE, @p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,
125 
BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE
126 
FROM TR_REQ_ADVANCE_PAYMENT A
127 
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
128 
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
129 
LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
130 
LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
131 
LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
132 
LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
133 
LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
134 
LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
135 
LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
136 
--Luanlt--2019/10/15-S?a AL,AL1
137 
LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'--Lo?i thanh to?n (N?i b?/Thanh to?n/N?)
138 
LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'--H?nh th?c thanh to?n( Ti?n m?t/Chuy?n kho?n)
139 
LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
140 
LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
141 
LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
142 
LEFT JOIN
143 
(
144 
SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
145 
) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
146 
LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
147 
LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
148 
LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
149 
LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
150 
LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
151 
LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
152 
LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
153 
LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
154 
LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
155 
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
156 
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
157 
LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME
158 
LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
159 
LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
160 
WHERE 1=1
161 
AND (A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID ='')
162 
AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
163 
AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
164 
AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
165 
OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
166 
AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
167 
--Luanlt-2019/10/14-Th?m Filter REQ_TYPE,FROMDATE TODATE REF_ID
168 
AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
169 
AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='')
170 
AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='')
171 
--Luanlt-2019/10/17-Th?m Filter REQ_PAY_CODE REQ_PAY_NAME BRANCH_ID DEP_ID KT_AUTH
172 
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
173 
AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
174 
AND(A.BRANCH_CREATE = @p_BRANCH_CREATE or @p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)
175 
AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
176 
AND(A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL)
177 
AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
178 
AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
179 
OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
180 
AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp))
181 
OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
182 
AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
183 
OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
184 
--- KIEM TRA NEU LA MAN HINH TIM KIEM PHIEU TAM UNG DE HOAN TAM UNG THI CHI LAY NHUNG PHIEU TAM UNG CO SO TIEN TAM UNG CON LAI =0
185 
AND(((@p_PROCESS='PM' AND (A.REQ_AMT- ISNULL(H.SOTIEN_TT,0)) >0) AND A.REQ_TYPE='I') OR @p_PROCESS IS NULL OR @p_PROCESS ='')
186 
AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')
187 
AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
188 
AND (
189 
(A.REQ_TYPE IN ('I','P','D')
190 
AND (@p_TYPE_SEARCH='HC' AND EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN AND BRANCH_TYPE ='HS')
191 
AND ( (A.PROCESS IS NOT NULL AND A.PROCESS <> '' AND (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =A.MAKER_ID) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN))
192 
OR(A.MAKER_ID =@P_USER_LOGIN OR A.TRASFER_USER_RECIVE =@p_USER_LOGIN))
193 
OR (@p_TYPE_SEARCH='HC' AND NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN AND BRANCH_TYPE ='HS')
194 
AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
195 
OR (@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL OR A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR (A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>''))
196 
OR (@p_TYPE_SEARCH ='HC' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER ='TGD' AND STATUS ='C'))
197 
-- HOAC NGUOC LAI NEU HC NHUNG PHIEU DUOC TAO O PGD, CN
198 
OR (@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)))
199 
)
200 
OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='')
201 
--AND (
202 
--(A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT'))
203 
--OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )
204 
--OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))
205 
--OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''
206 
--)
207 
ORDER BY A.CREATE_DT DESC;
208 
END;
209 
ELSE BEGIN
210 
SELECT A.*,'' as TEMP_PAY_STATUS ,ISNULL(B.AUTH_STATUS_NAME,N'Ch? duy?t') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
211 
TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
212 
BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
213 
--Luanlt--2019/10/15-S?a AL,AL1
214 
BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,
215 
A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(A.PAY_AMT,0) END AS TOTAL_AMT_PAY_HIS,
216 
TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
217 
TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
218 
PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME,PM.PAY_PHASE AS KY_TAM_UNG,
219 
DV.DVDM_CODE, DV.DVDM_NAME, CASE WHEN PC.ROLE_USER ='GDDV' THEN (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) ELSE PC.ROLE_USER END AS ROLE_CONF, PR.PROCESS_DESC +N' v?o l?c '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_
220 
STATUS, PC1.NOTES AS NEXT_STEP,
221 
PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH,
222 
SR.ROLE_ID AS ROLE_ID_CRE, ISNULL(H.SOTIEN_TT,0) AS STTT, A.REQ_AMT - ISNULL(H.SOTIEN_TT,0) AS SL_CL,BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE
223 
FROM TR_REQ_ADVANCE_PAYMENT A
224 
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
225 
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
226 
LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
227 
LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
228 
LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
229 
LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
230 
LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
231 
LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
232 
LEFT JOIN dbo.CM_DEPARTMENT DP ON A.DEP_ID= DP.DEP_ID
233 
--Luanlt--2019/10/15-S?a AL,AL1
234 
LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'
235 
LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
236 
LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
237 
LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
238 
LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
239 
LEFT JOIN
240 
(
241 
SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
242 
) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
243 
LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
244 
LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
245 
LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
246 
LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
247 
LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
248 
LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
249 
--LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
250 
LEFT JOIN
251 
(
252 
SELECT P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT
253 
FROM PL_PROCESS P
254 
INNER JOIN
255 
(
256 
SELECT MAX (PR.ID) AS ID, MAX(PR.REQ_ID) AS REQ_ID FROM PL_PROCESS PR WHERE PR.REQ_ID =@p_REQ_PAY_ID
257 
GROUP BY PR.REQ_ID
258 
) PR ON PR.REQ_ID = P.REQ_ID AND P.ID = PR.ID
259 
AND P.REQ_ID =@p_REQ_PAY_ID
260 
) AS PR ON A.REQ_PAY_ID = PR.REQ_ID
261 
LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
262 
--LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND PC.STATUS='C'
263 
LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
264 
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
265 
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
266 
LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME
267 
LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
268 
LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
269 
WHERE 1=1
270 
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' OR @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
271 
AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
272 
AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
273 
AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
274 
OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
275 
AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
276 
--Luanlt-2019/10/14-Th?m Filter REQ_TYPE,FROMDATE TODATE REF_ID
277 
AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
278 
AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL)
279 
AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL)
280 
--Luanlt-2019/10/17-Th?m Filter REQ_PAY_CODE REQ_PAY_NAME BRANCH_ID DEP_ID KT_AUTH
281 
AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
282 
--AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
283 
AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
284 
AND((A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL) OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S')))
285 
AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
286 
AND(( @p_IS_TRANSFER='Y' AND (EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) OR A.AUTH_STATUS_KT ='A')) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
287 
OR((@p_IS_TRANSFER='N' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) AND A.AUTH_STATUS_KT <>'A'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
288 
AND(((@p_PROCESS='PM' AND (A.REQ_AMT- ISNULL(A.PAY_AMT,0.00)) >0)
289 
AND (A.REQ_TYPE IN('I','D','P') OR (@DEP_ID_LG ='DEP000000000014' AND A.REQ_TYPE IN ('I','P','D'))) AND
290 
((A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL) OR(@BRANCH_TYPE_LG <>'HS' AND A.BRANCH_ID =@p_BRANCH_LOGIN) OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE)))
291 
OR @p_PROCESS IS NULL OR @p_PROCESS ='')
292 
AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp))
293 
OR((@p_LEVEL='UNIT' AND (A.BRANCH_ID=@p_BRANCH_ID OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE))) OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
294
295 
--AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
296 
-- OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
297 
--AND ((@p_TRASFER_USER_RECIVE IS NOT NULL AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME = @p_TRASFER_USER_RECIVE ))
298 
--OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
299 
AND ((@p_FUNCTION ='KT' AND (EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TLNAME= @p_USER_LOGIN)) OR A.CHECKER_ID_KT ='admin' OR A.AUTH_STATUS_KT ='A')
300 
OR @p_FUNCTION IS NULL OR @p_FUNCTION ='' OR @p_FUNCTION ='TF')
301 
AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='')
302 
--AND(A.MAKER_ID_KT LIKE '%'+@p_TRASFER_USER_RECIVE+'%' OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
303 
--AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
304 
AND(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME =@p_TRASFER_USER_RECIVE AND TYPE_JOB='XL' AND REQ_ID = A.REQ_PAY_ID) OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE='')
305 
AND
306 
(
307 
A.MAKER_ID =@p_USER_LOGIN OR (A.TRASFER_USER_RECIVE = @p_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R'))
308 
OR(
309 
--A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I'
310 
-- AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN
311 
-- OR (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID)
312 
-- OR (A.DVDM_ID IS NULL AND A.DEP_ID =@DEP_ID_LG))
313 
-- ))
314 
A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I'
315 
AND (
316 
(@ROLE_ID IN ('GDDV','TP','PP') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND A.DEP_ID = @DEP_ID_LG)
317 
OR
318 
(@ROLE_ID NOT IN ('GDDV','TP','PP') AND
319 
(A.TRASFER_USER_RECIVE = @p_USER_LOGIN
320 
OR (@ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID)
321 
OR (A.DVDM_ID IS NULL AND A.DEP_ID =@DEP_ID_LG)
322 
OR(A.DEP_ID =@DEP_ID_LG) OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))
323 
)))
324 
AND ( 1=1 AND
325 
( @ROLE_ID IN ('GDK','PTGD') AND (A.DVDM_ID =@DVDM_ID OR (A.DVDM_ID IS NULL OR A.DVDM_ID ='')) AND ((A.PROCESS IS NOT NULL AND A.PROCESS <>'')
326 
OR A.DEP_ID ='DEP000000000052' OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)))
327 
OR (@ROLE_ID NOT IN ('GDK','PTGD'))
328 
)
329 
)
330 
OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE <> 'I' AND
331 
(A.TRASFER_USER_RECIVE = @p_USER_LOGIN OR (@ROLE_ID IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))
332 
AND( A.TRASFER_USER_RECIVE IS NULL OR A.TRASFER_USER_RECIVE ='' OR
333 
(A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'')))
334 
OR (@ROLE_ID NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)))
335 
))
336 
OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID IN ('GDDV','PGD','TPGD') AND
337 
(
338 
(A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'')
339 
OR A.TRASFER_USER_RECIVE ='' OR A.TRASFER_USER_RECIVE IS NULL)
340
341 
OR ((A.BRANCH_CREATE <> @p_BRANCH_LOGIN AND A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'') OR A.BRANCH_CREATE = @p_BRANCH_LOGIN)
342 
AND A.BRANCH_ID IN (SELECT * FROM @tmp)))
343 
OR (@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND A.BRANCH_ID IN (SELECT * FROM @tmp))
344 
OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'PGD' AND (@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') AND A.BRANCH_ID =@p_BRANCH_LOGIN))
345 
OR(@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL AND A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND
346 
@ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID))
347 
OR(A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND @ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)))
348 
OR(@p_TYPE_SEARCH ='HC' AND (@ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER IN ('TGD','HDQT') AND STATUS ='C')))
349 
OR(@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
350 
OR(@p_TYPE_SEARCH='HC' AND @ROLE_ID IN ('KSV','GDV') AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
351 
OR(@p_TYPE_SEARCH='PAY' AND (A.REQ_AMT -ISNULL(A.PAY_AMT,0)) >=0 AND (
352 
(@BRANCH_TYPE_LG ='HS' AND (A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL))
353 
OR (@BRANCH_TYPE_LG <> 'HS' AND A.BRANCH_ID = @p_BRANCH_LOGIN)
354 
OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE)))
355 
)))
356 
AND (
357 
(A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT'))
358 
OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )
359 
OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))
360 
OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''
361 
)
362 
ORDER BY A.CREATE_DT DESC;
363 
END;
364