|
1
|
|
|
2
|
ALTER PROCEDURE dbo.PL_REQUEST_DOC_ById
|
|
3
|
@p_REQ_ID varchar(15) = NULL,
|
|
4
|
@p_USER_LOGIN VARCHAR(50) = NULL
|
|
5
|
AS
|
|
6
|
BEGIN
|
|
7
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
|
|
8
|
A.TOTAL_AMT AS TOTAL_AMT, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,A.IS_AUTHORITY,
|
|
9
|
CASE WHEN A.BRANCH_FEE IS NULL OR A.BRANCH_FEE = '' OR ISNULL(A.NOTES,'') <> '' THEN A.NOTES
|
|
10
|
WHEN A.BRANCH_FEE = 'DV0001' THEN DF.DEP_NAME + ' - ' + BF.BRANCH_NAME
|
|
11
|
WHEN A.BRANCH_FEE <> 'DV0001' THEN BF.BRANCH_NAME END AS NOTES,
|
|
12
|
UDV.TLFullName AS CHECKER_NAME_DV,
|
|
13
|
A.APPROVE_DT,
|
|
14
|
A.PROCESS_ID,
|
|
15
|
D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
|
|
16
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
17
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
|
|
18
|
G.BRANCH_CODE,
|
|
19
|
CASE WHEN A.BRANCH_CREATE <> 'DV0001' THEN G.BRANCH_NAME ELSE DEP.DEP_NAME + ' - ' + G.BRANCH_NAME END AS BRANCH_NAME,
|
|
20
|
UP.TLFullName AS CHECKER_NAME_PROCESS,
|
|
21
|
RP.APPROVE_DT AS APPROVE_DT_PROCESS,
|
|
22
|
UC.TLFullName AS MAKER_NAME,
|
|
23
|
RPN.PROCESS_ID AS PROCESS_ID_NEXT,
|
|
24
|
RPN.ROLE_USER,
|
|
25
|
RP.NOTES AS PROCESS_STATUS ,
|
|
26
|
RPN.NOTES AS PROCESS_STATUS_NEXT,
|
|
27
|
A.DVDM_APP_ID,
|
|
28
|
CD.DVDM_NAME AS DVDM_APP_NAME,
|
|
29
|
A.REQ_PARENT_ID,
|
|
30
|
PARENT.REQ_NAME AS REQ_PARENT_NAME,
|
|
31
|
PARENT.REQ_CODE AS REQ_PARENT_CODE,
|
|
32
|
PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
|
|
33
|
CC.REQ_NAME AS PL_BASED_NAME,
|
|
34
|
CC.REQ_CODE AS PL_BASED_CODE,
|
|
35
|
CC.TOTAL_AMT AS PL_BASED_AMT,
|
|
36
|
A.BRANCH_FEE,
|
|
37
|
A.DEP_ID,
|
|
38
|
A.DEP_FEE,
|
|
39
|
DEP.DEP_NAME,
|
|
40
|
DEP.DEP_CODE,
|
|
41
|
BF.BRANCH_NAME AS BRANCH_FEE_NAME,
|
|
42
|
BF.BRANCH_CODE AS BRANCH_FEE_CODE,
|
|
43
|
DF.DEP_NAME AS DEP_FEE_NAME,
|
|
44
|
DF.DEP_CODE AS DEP_FEE_CODE,
|
|
45
|
'' AS BRANCH_DEP,
|
|
46
|
'' AS BRANCH_DEP_FEE,
|
|
47
|
|
|
48
|
|
|
49
|
|
|
50
|
RPC.TYPE_JOB AS TYPE_JOB,
|
|
51
|
RPC.TLNAME AS USER_JOB,
|
|
52
|
TU.TLFullName AS USER_JOB_NAME,
|
|
53
|
TFM.TLNANME AS TRANSFER_MAKER,
|
|
54
|
RPC.TRANFER_DT AS TRANFER_DT ,
|
|
55
|
RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
56
|
A.EFFEC_DT,A.IS_BACKDAY,
|
|
57
|
'' AS TYPE_JOB_XL,
|
|
58
|
'' AS USER_JOB_XL,
|
|
59
|
--PLRP.ID AS REF_ID,
|
|
60
|
-- GIANT 11/11/2021
|
|
61
|
ISNULL(RPC.PROCESS_ID,PLRP.ID) AS REF_ID,
|
|
62
|
|
|
63
|
|
|
64
|
RPN.STATUS AS STATUS_NEXT,
|
|
65
|
PLRP.STATUS AS STATUS_CURR,
|
|
66
|
RPC.STATUS_JOB AS STATUS_JOB,
|
|
67
|
A.BRANCH_CREATE,
|
|
68
|
A.DEP_CREATE,
|
|
69
|
A.REQ_LINE,
|
|
70
|
A.TC_NOTES,
|
|
71
|
A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
|
|
72
|
A.KT_NOTES,
|
|
73
|
A.IS_CHECKALL,
|
|
74
|
A.BASED_CONTENT,
|
|
75
|
'' AS IS_TRANSFER,
|
|
76
|
--NGUOI XU LY
|
|
77
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY,
|
|
78
|
'' AS NGUOIXULY
|
|
79
|
,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES,
|
|
80
|
BTC.BRANCH_TYPE AS BRANCH_TYPE_CRE,
|
|
81
|
BF.BRANCH_TYPE AS BRANCH_TYPE_FEE,
|
|
82
|
A.PL_BASED_ID,
|
|
83
|
A.TGD_NOTES, A.DEP_FEE AS DEP_FEE_ID,
|
|
84
|
A.CREATOR_NOTES,
|
|
85
|
ISNULL(PLRP.SUB_PROCESS_ID, '') AS SUB_PROCESS_ID
|
|
86
|
FROM PL_REQUEST_DOC A
|
|
87
|
--LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <> 'U' AND (PLRP.IS_HAS_CHILD=1 OR PLRP.PROCESS_ID IN ('TKTGD','TKHDQT'))
|
|
88
|
------------BAODNQ 24/6/2022-------------------
|
|
89
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND ((PLRP.PROCESS_ID = 'DVCM' AND EXISTS(
|
|
90
|
SELECT pc.COST_ID FROM PL_COSTCENTER pc
|
|
91
|
LEFT JOIN PL_COSTCENTER_DT pcd ON pc.COST_ID = pcd.COST_ID
|
|
92
|
LEFT JOIN CM_DEPARTMENT cd ON pcd.DEP_ID = cd.DEP_ID OR pcd.DEP_ID = cd.FATHER_ID
|
|
93
|
LEFT JOIN TL_USER tu1 ON pcd.BRANCH_ID = tu1.TLSUBBRID AND cd.DEP_ID = tu1.SECUR_CODE
|
|
94
|
LEFT JOIN TL_SYS_ROLE_MAPPING tsrm ON tsrm.DEP_ID = cd.DEP_ID AND pcd.BRANCH_ID = tsrm.BRANCH_ID
|
|
95
|
WHERE (pc.DVDM_ID = PLRP.DVDM_ID OR PLRP.DVDM_ID IS NULL OR PLRP.DVDM_ID = '')
|
|
96
|
AND (tu1.TLNANME = @p_USER_LOGIN OR tsrm.TLNAME = @p_USER_LOGIN)))
|
|
97
|
OR (PLRP.PROCESS_ID = 'DVDC' AND EXISTS(
|
|
98
|
SELECT prt.REQ_TRANSFER_ID FROM PL_REQUEST_TRANSFER prt
|
|
99
|
LEFT JOIN CM_DEPARTMENT cd ON cd.DEP_ID = prt.FR_DEP_ID OR cd.FATHER_ID = prt.FR_DEP_ID
|
|
100
|
LEFT JOIN TL_USER tu1 ON prt.FR_BRN_ID = tu1.TLSUBBRID AND (cd.DEP_ID = tu1.SECUR_CODE OR tu1.BRANCH_TYPE <> 'HS')
|
|
101
|
WHERE prt.REQ_DOC_ID = PLRP.REQ_ID
|
|
102
|
AND tu1.TLNANME = @p_USER_LOGIN))
|
|
103
|
OR PLRP.PROCESS_ID NOT IN ('DVCM','DVDC'))
|
|
104
|
------------END BAODNQ-------------------
|
|
105
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
|
|
106
|
ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
|
|
107
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
108
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
109
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
|
|
110
|
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID
|
|
111
|
|
|
112
|
LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
|
|
113
|
LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
|
|
114
|
LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
|
|
115
|
LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
|
|
116
|
LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
|
|
117
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
|
|
118
|
--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID = PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
119
|
-- GIANT
|
|
120
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND RPC.TLNAME = @p_USER_LOGIN
|
|
121
|
--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
122
|
|
|
123
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
124
|
|
|
125
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
126
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
127
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
128
|
LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
|
|
129
|
LEFT JOIN dbo.PL_REQUEST_DOC CC ON CC.REQ_ID = A.PL_BASED_ID
|
|
130
|
|
|
131
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
132
|
|
|
133
|
-- GiaNT
|
|
134
|
LEFT JOIN CM_BRANCH BTC ON BTC.BRANCH_ID = A.BRANCH_CREATE
|
|
135
|
WHERE 1 = 1
|
|
136
|
AND (A.REQ_ID = @p_REQ_ID )
|
|
137
|
ORDER BY USER_JOB DESC
|
|
138
|
END
|