|
1
|
DECLARE @p_TLNAME_USER VARCHAR(50) = 'phuongtln1'
|
|
2
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
3
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
4
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
5
|
WHERE TU.TLNANME=@p_TLNAME_USER
|
|
6
|
UNION ALL
|
|
7
|
SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
8
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
9
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
10
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
11
|
WHERE TU.TLNANME=@p_TLNAME_USER AND TM.ROLE_NEW IS NOT NULL
|
|
12
|
UNION ALL
|
|
13
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
14
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
15
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
16
|
WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
17
|
UNION ALL
|
|
18
|
SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
19
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
20
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
21
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
22
|
WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
23
|
---- 16.11.22 LUCTV BO SUNG UNION NHUNG PHONG BAN CON CUA PHONG BAN DUOC KIEM NHIEM
|
|
24
|
UNION ALL
|
|
25
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
26
|
LEFT JOIN CM_DEPARTMENT DP ON DP.FATHER_ID = TU.DEP_ID
|
|
27
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=DP.DEP_ID
|
|
28
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
29
|
WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
30
|
UNION ALL
|
|
31
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
32
|
LEFT JOIN CM_DEPARTMENT DP ON DP.FATHER_ID = TU.SECUR_CODE
|
|
33
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=DP.DEP_ID
|
|
34
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
35
|
WHERE TU.TLNANME=@p_TLNAME_USER
|