|
1
|
SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
2
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
3
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
4
|
WHERE TLNANME=@p_TLNAME_USER
|
|
5
|
UNION ALL
|
|
6
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
7
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
8
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
9
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
10
|
WHERE TLNANME=@p_TLNAME_USER
|
|
11
|
UNION ALL
|
|
12
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
13
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
14
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
15
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
16
|
UNION ALL
|
|
17
|
SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
18
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
19
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
20
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
21
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|