|
1
|
USE [gAMSPro_VietcapitalBank_v2_TEST]
|
|
2
|
GO
|
|
3
|
/****** Object: StoredProcedure [dbo].[PL_PROCESS_CURRENT_SEARCH] Script Date: 27-Aug-20 08:28:28 ******/
|
|
4
|
SET ANSI_NULLS ON
|
|
5
|
GO
|
|
6
|
SET QUOTED_IDENTIFIER ON
|
|
7
|
GO
|
|
8
|
|
|
9
|
/*
|
|
10
|
SELECT * FROM PL_TRADEDETAIL WHERE PLAN_ID = 'PLM000000000143'
|
|
11
|
select * from TR_CONTRACT_DT WHERE CONTRACT_ID = 'TRC000000000039'
|
|
12
|
[TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000143','TRC000000000039','','',''
|
|
13
|
exec [TR_PO_GOODS_Search] 1,'','','','','',NULL,'','PLM000000000003','','','',''
|
|
14
|
exec [TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000003','','','',''
|
|
15
|
SELECT * FROM TR_CONTRACT WHERE CONTRACT_TYPE = '1'
|
|
16
|
SELECT * FROM PL_MASTER
|
|
17
|
*/
|
|
18
|
|
|
19
|
ALTER PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]
|
|
20
|
@p_REQ_ID VARCHAR(20)=NULL,
|
|
21
|
@p_USER_LOGIN VARCHAR(20)=NULL,
|
|
22
|
@p_TYPE VARCHAR(20)=NULL
|
|
23
|
AS
|
|
24
|
BEGIN
|
|
25
|
|
|
26
|
DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT
|
|
27
|
|
|
28
|
SELECT TOP 1 @PROCESS_CURR = PROCESS_ID,@IS_HAS_CHILD=IS_HAS_CHILD FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'
|
|
29
|
|
|
30
|
IF(@p_TYPE LIKE 'TTCT%')
|
|
31
|
BEGIN
|
|
32
|
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
33
|
|
|
34
|
BEGIN
|
|
35
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
|
|
36
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
37
|
LEFT JOIN
|
|
38
|
(
|
|
39
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
40
|
LEFT JOIN(
|
|
41
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
42
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
43
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
44
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
45
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
46
|
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
|
|
47
|
END
|
|
48
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
49
|
BEGIN
|
|
50
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
|
|
51
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
52
|
LEFT JOIN
|
|
53
|
(
|
|
54
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
55
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
56
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
57
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
58
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
59
|
LEFT JOIN
|
|
60
|
(
|
|
61
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
62
|
LEFT JOIN(
|
|
63
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
64
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
65
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
66
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
67
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
68
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
69
|
END
|
|
70
|
|
|
71
|
|
|
72
|
END
|
|
73
|
ELSE IF(@p_TYPE LIKE 'PYC%')
|
|
74
|
BEGIN
|
|
75
|
IF(@PROCESS_CURR='DMMS')
|
|
76
|
BEGIN
|
|
77
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
|
|
78
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
79
|
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
|
|
80
|
LEFT JOIN
|
|
81
|
(
|
|
82
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
83
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
84
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
85
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
86
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
87
|
LEFT JOIN
|
|
88
|
(
|
|
89
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID FROM dbo.TL_USER TU
|
|
90
|
LEFT JOIN(
|
|
91
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
92
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
93
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
|
|
94
|
|
|
95
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
96
|
END
|
|
97
|
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
98
|
|
|
99
|
BEGIN
|
|
100
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
|
|
101
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
102
|
LEFT JOIN
|
|
103
|
(
|
|
104
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
105
|
LEFT JOIN(
|
|
106
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
107
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
108
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
109
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
110
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
111
|
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
|
|
112
|
|
|
113
|
END
|
|
114
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
115
|
BEGIN
|
|
116
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
|
|
117
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
118
|
LEFT JOIN
|
|
119
|
(
|
|
120
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
121
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
122
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
123
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
124
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
125
|
LEFT JOIN
|
|
126
|
(
|
|
127
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
128
|
LEFT JOIN(
|
|
129
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
130
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
131
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
132
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
133
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
134
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
135
|
END
|
|
136
|
END
|
|
137
|
|
|
138
|
END
|
|
139
|
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s
|
|
140
|
|
|
141
|
|
|
142
|
|
|
143
|
|
|
144
|
|