|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_ById]
|
|
3
|
@REQ_ID varchar(15)
|
|
4
|
AS
|
|
5
|
|
|
6
|
--SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID= @REQ_ID
|
|
7
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, ISNULL(A.REQ_TYPE,1) AS REQ_TYPE,A.SIGN_USER,TL_USER.TLFullName as SIGN_USER_NAME,
|
|
8
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
9
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST, A.IS_KT,U.TLFullName AS USER_REQUEST_NAME,
|
|
10
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
11
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
12
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
13
|
RP.CHECKER_ID AS PREV_PROCESS_USER, --USER ĐÃ XỬ LÝ TRƯỚC ĐÓ
|
|
14
|
RP.ROLE_USER,
|
|
15
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
16
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
17
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
18
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
19
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
20
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
21
|
PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
22
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,ISNULL(PLRP.ID,0) AS REF_ID,
|
|
23
|
RPN.STATUS AS STATUS_NEXT,
|
|
24
|
PLRP.STATUS AS STATUS_CURR,
|
|
25
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE, 1 AS IS_ShowBTNDVCM,
|
|
26
|
-----------------------BAODNQ 8/3/2022: Lấy biên bản xét giá-------------------
|
|
27
|
--XG1.RECORD_ID AS RECORD_ID_1,XG2.RECORD_ID AS RECORD_ID_2,XG3.RECORD_ID AS RECORD_ID_3,
|
|
28
|
CASE
|
|
29
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.RECORD_ID
|
|
30
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.RECORD_ID
|
|
31
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.RECORD_ID
|
|
32
|
END
|
|
33
|
AS RECORD_ID,
|
|
34
|
CASE
|
|
35
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.REQ_CODE
|
|
36
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.REQ_CODE
|
|
37
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.REQ_CODE
|
|
38
|
END
|
|
39
|
AS BBXG_CODE,
|
|
40
|
CASE
|
|
41
|
WHEN XG1.RECORD_ID IS NOT NULL THEN '10_100M'
|
|
42
|
WHEN XG2.RECORD_ID IS NOT NULL THEN '100_500M'
|
|
43
|
WHEN XG3.RECORD_ID IS NOT NULL THEN '500M'
|
|
44
|
END
|
|
45
|
AS REPORT_PRICE_TYPE,
|
|
46
|
CASE
|
|
47
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.TOTAL_SUPPLIERS
|
|
48
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.TOTAL_SUPPLIERS
|
|
49
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.TOTAL_SUPPLIERS
|
|
50
|
END
|
|
51
|
AS TOTAL_SUPPLIERS,
|
|
52
|
-----------------------END BAODNQ-------------------
|
|
53
|
----------BAODNQ 10/6/2022 : LẤY TỜ TRÌNH CĐT-------
|
|
54
|
PAC.REQ_ID AS PL_APPOINT_CONTRACTOR_ID,
|
|
55
|
PAC.REQ_CODE AS PL_APPOINT_CONTRACTOR_CODE,
|
|
56
|
----------BAODNQ 20/7/2022 : Lấy DVDM_ID của DMMS-----
|
|
57
|
(
|
|
58
|
SELECT CDV.DVDM_ID
|
|
59
|
FROM CM_DMMS CD
|
|
60
|
LEFT JOIN PL_COSTCENTER_DT PCD ON CD.BRANCH_ID = PCD.BRANCH_ID AND CD.DEP_ID = PCD.DEP_ID
|
|
61
|
LEFT JOIN PL_COSTCENTER PC ON PCD.COST_ID = PC.COST_ID
|
|
62
|
LEFT JOIN CM_DVDM CDV ON PC.DVDM_ID = CDV.DVDM_ID
|
|
63
|
WHERE CD.DMMS_ID = (SELECT DVDM_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID = 'DMMS' AND REQ_ID = @REQ_ID)
|
|
64
|
AND CDV.IS_DVCM = 1
|
|
65
|
) AS DVDM_DMMS_ID,
|
|
66
|
|
|
67
|
STUFF(
|
|
68
|
(SELECT ',' + TRN_TYPE
|
|
69
|
FROM CM_TRAN_TYPE
|
|
70
|
--WHERE TRN_TYPE IN ('TRN0000000003', 'TRN0000000006', 'TRN0000000007')
|
|
71
|
FOR XML PATH('')), 1, 1, '')
|
|
72
|
AS BBXG_TRN_TYPE,
|
|
73
|
STUFF(
|
|
74
|
(SELECT ',' + TRN_TYPE
|
|
75
|
FROM CM_TRAN_TYPE
|
|
76
|
--WHERE TRN_TYPE = 'TRN0000000003'
|
|
77
|
FOR XML PATH('')), 1, 1, '')
|
|
78
|
AS CDT_TRN_TYPE
|
|
79
|
|
|
80
|
|
|
81
|
FROM TR_REQUEST_DOC A
|
|
82
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS ='C'
|
|
83
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
84
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
85
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
86
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
87
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
88
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
89
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
90
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
91
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
92
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
93
|
LEFT JOIN
|
|
94
|
(
|
|
95
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
96
|
dbo.PL_REQUEST_PROCESS
|
|
97
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
98
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
99
|
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)
|
|
100
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
101
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
102
|
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 A.PROCESS_ID <>'PDHT'
|
|
103
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
104
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
105
|
LEFT JOIN
|
|
106
|
(
|
|
107
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
108
|
dbo.CM_DMMS
|
|
109
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
110
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
111
|
UNION ALL
|
|
112
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
113
|
FROM dbo.CM_DVDM
|
|
114
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
115
|
LEFT JOIN (
|
|
116
|
select TLNANME,TLFullName from TL_USER
|
|
117
|
)TL_USER ON TL_USER.TLNANME=A.SIGN_USER
|
|
118
|
--Cuong LX
|
|
119
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_DUOI_100M XG1 ON XG1.REQ_DOC_ID=A.REQ_ID
|
|
120
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_100M_500M XG2 ON XG2.REQ_DOC_ID=A.REQ_ID
|
|
121
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_TREN_500M XG3 ON XG3.REQ_DOC_ID=A.REQ_ID
|
|
122
|
--------------BAODNQ 10/6/2022 : LEFT JOIN TỜ TRÌNH CHỈ ĐỊNH THẦU--------
|
|
123
|
LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON A.REQ_ID = PAC.TR_REQUEST_DOC_ID
|
|
124
|
----------------------END BAODNQ-----------------------
|
|
125
|
WHERE 1 = 1
|
|
126
|
AND A.REQ_ID = @REQ_ID
|
|
127
|
|
|
128
|
|
|
129
|
|
|
130
|
|
|
131
|
|
|
132
|
|
|
133
|
|