|
1
|
ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Byid]
|
|
2
|
@p_REQ_PAY_ID varchar(15)= NULL
|
|
3
|
AS
|
|
4
|
SELECT A.*,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,
|
|
5
|
TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
|
|
6
|
BR.BRANCH_NAME BRANCH_NAME, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
|
|
7
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
8
|
BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,
|
|
9
|
A.REQ_AMT AS TOTAL_AMT_TEMP,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,
|
|
10
|
TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
|
|
11
|
TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
|
|
12
|
PO.PO_CODE, PO.PO_NAME AS ACC_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS ACC_NAME,PM.PAY_PHASE AS KY_TAM_UNG,
|
|
13
|
DV.DVDM_CODE, DV.DVDM_NAME, PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_STATUS, PC1.NOTES AS NEXT_STEP,
|
|
14
|
PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE
|
|
15
|
FROM TR_REQ_ADVANCE_PAYMENT A
|
|
16
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
|
|
17
|
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
|
|
18
|
LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
|
|
19
|
LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
|
|
20
|
LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
|
|
21
|
LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
|
|
22
|
LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
|
|
23
|
LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
|
|
24
|
LEFT JOIN dbo.CM_DEPARTMENT DP ON A.DEP_ID= DP.DEP_ID
|
|
25
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
26
|
LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE' AND CDTYPE = 'REQ_PAY_ADVANCE'
|
|
27
|
LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
|
|
28
|
LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
|
|
29
|
LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
|
|
30
|
LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
|
|
31
|
LEFT JOIN
|
|
32
|
(
|
|
33
|
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
|
|
34
|
) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
|
|
35
|
LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
|
|
36
|
LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
|
|
37
|
LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
|
|
38
|
LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
|
|
39
|
LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
|
|
40
|
LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
|
|
41
|
--LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
|
|
42
|
LEFT JOIN
|
|
43
|
(
|
|
44
|
SELECT MAX(PR.PROCESS_ID) AS PROCESS_ID , MAX(PR.REQ_ID) AS REQ_ID, MAX(PR.PROCESS_DESC) AS PROCESS_DESC, MAX(APPROVE_DT) AS APP_DT FROM PL_PROCESS PR
|
|
45
|
GROUP BY PR.PROCESS_ID, PR.REQ_ID
|
|
46
|
) AS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
|
|
47
|
|
|
48
|
--LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
|
|
49
|
LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
|
|
50
|
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
|
|
51
|
LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
|
|
52
|
LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME
|
|
53
|
LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
|
|
54
|
WHERE 1=1
|
|
55
|
AND(A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL)
|