|
1
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
2
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
3
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
4
|
WHERE TU.TLNANME='phuongtln1'
|
|
5
|
UNION ALL
|
|
6
|
SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
7
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
8
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
9
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
10
|
WHERE TU.TLNANME='phuongtln1' AND TM.ROLE_NEW IS NOT NULL
|
|
11
|
UNION ALL
|
|
12
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
13
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
14
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
15
|
WHERE TU.TLNAME='phuongtln1' AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
16
|
UNION ALL
|
|
17
|
SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
18
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
19
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
20
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
21
|
WHERE TU.TLNAME='phuongtln1' AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|