alter PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH] @p_REQ_ID VARCHAR(20)=NULL, @p_USER_LOGIN VARCHAR(20)=NULL, @p_TYPE VARCHAR(20)=NULL, @MENU_PERMISSON NVARCHAR(500), @PROCESS_ID VARCHAR(10) = NULL AS BEGIN DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT, @AUTH_STATUS VARCHAR(10),@BRANCH_NAME_HS NVARCHAR(250) SELECT TOP 1 @BRANCH_NAME_HS = BRANCH_NAME FROM dbo.CM_BRANCH WHERE BRANCH_ID = 'DV0001' 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' DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50) IF(@p_TYPE LIKE 'TTCT%') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID = PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN dbo.PL_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME, TempU.TLNANME AS TLNAME, TempU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME, US.TLFullName, TU.RoleDisplayName RoleName, Temp.DVDM_ID, TU.DEP_ID SECUR_CODE, TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID = TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END ELSE IF(@p_TYPE LIKE 'PYC-XE') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN DECLARE @Role VARCHAR(50) DECLARE @BR VARCHAR(50) SELECT TOP 1 @Role = ROLE_USER, @BR = BRANCH_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C' SET @Role= REPLACE(@Role,'-',',') SET @BR= REPLACE(@BR,'-',',') SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID -- LEFT JOIN dbo.FN_GET_USER_BY_ROLE('','','') TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,','))) AND (TempU.BRANCH_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') LEFT JOIN dbo.FN_GET_USER_BY_ROLE('', @BR, '', @MENU_PERMISSON) TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,','))) AND (TempU.BRANCH_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')-- NGUYENTD 10102023_SECRETKEY: FIX LỖI NGƯỜI XỬ LÝ TIẾP THEO KHÔNG LẤY ĐƯỢC USER CÓ ROLE GDDV WHERE REQ_ID =@p_REQ_ID AND STATUS='C' -- UNION -- SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,tmp.CDVAL AS TLNAME,tmp.CONTENT,PL.NOTES -- FROM dbo.PL_REQUEST_PROCESS PL -- LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID -- LEFT JOIN (SELECT ca.CDVAL,ca.CONTENT,tu.TLSUBBRID FROM CM_ALLCODE ca LEFT JOIN TL_USER tu ON ca.CDVAL= tu.TLNANME WHERE ca.CDTYPE ='TR' AND ca.CDNAME='REQCAR') tmp ON (PL.ROLE_USER='CVDDX' AND pl.BRANCH_ID=tmp.TLSUBBRID) -- WHERE REQ_ID =@p_REQ_ID AND STATUS='C' END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TempC.TLNAME AS TLNANME , TU.TLFullName AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='CAR_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END ELSE IF(@p_TYPE LIKE 'PYC%' AND @p_TYPE NOT LIKE 'PYC-KVL' AND @p_TYPE NOT LIKE 'PYCQLX') BEGIN IF(@PROCESS_CURR='DMMS') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME WHEN CD1.DMMS_ID IS NOT NULL AND CD1.DMMS_ID <> '' THEN IIF(BR1.BRANCH_TYPE = 'HS',ISNULL(BR1.BRANCH_NAME,'') + ' - ' + ISNULL(DEP1.DEP_NAME,''),BR.BRANCH_NAME ) ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN dbo.CM_DMMS CD1 ON CD1.DMMS_ID = PL.DVDM_ID LEFT JOIN dbo.CM_BRANCH BR1 ON CD1.BRANCH_ID = BR1.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP1 ON CD1.DEP_ID = DEP1.DEP_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID LEFT JOIN( SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC UNION ALL SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM ( SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE CD.IS_KHOI <>1 GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID ) DVDM ) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID ) TempU ON TempU.RoleName = PL.ROLE_USER AND TempU.DMMS_ID = TR.DMMS_ID AND TempC.ID IS NULL WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS='C' END ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN dbo.TR_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN ( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE REQ_ID =@p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END ELSE IF(@p_TYPE = 'INVENT') BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TU.TLNANME AS TLNAME,TU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.ASS_INVENTORY_MASTER AI ON AI.INVENT_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = AI.SIGN_USER WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND PL.PROCESS_ID = 'SIGN' END ELSE IF(@CURENT_PROCESS = 'APPNEW') BEGIN SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TmpU.TLNANME, TmpU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN ( SELECT * FROM [dbo].[FN_GET_USER_BY_ROLE] ('', '', '', @MENU_PERMISSON) ) TmpU ON 1=1 LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = PL.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C' AND ((TmpU.ROLE_OLD = PL.ROLE_USER OR TmpU.ROLE_NEW = PL.ROLE_USER) OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '') AND ((BR.BRANCH_TYPE = 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID AND TmpU.DEP_ID = PL.DEP_ID) OR (BR.BRANCH_TYPE <> 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID) OR (PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '') ) END ELSE IF(@CURENT_PROCESS <> 'APPNEW') BEGIN SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TU.TLNANME, TU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = PL.ROLE_USER LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = TU.TLSUBBRID WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C' END END ----------------BAODNQ 6/6/2022 TỜ TRÌNH CHỈ ĐỊNH THẦU-------------- ELSE IF (@p_TYPE = 'TT_CDT') BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM PL_APPOINT_CONTRACTOR WHERE REQ_ID = @p_REQ_ID) SET @CURENT_PROCESS = ( SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS = 'C' ) -------------CÓ CẤP DUYỆT TRUNG GIAN----------- IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT DISTINCT PRP.REQ_ID, PRP.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE CB.BRANCH_NAME END AS DVDM_NAME, TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES FROM PL_REQUEST_PROCESS PRP LEFT JOIN CM_DVDM CD ON PRP.DVDM_ID = CD.DVDM_ID LEFT JOIN( SELECT TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE, TU.DEP_ID, TU.TLSUBBRID FROM TL_USER TU LEFT JOIN ( SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID FROM PL_COSTCENTER PC LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID ) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID ) TLUSER ON TLUSER.RoleName = PRP.ROLE_USER AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '') AND (TLUSER.DEP_ID = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '') AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '') LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON PRP.REQ_ID = PAC.REQ_ID LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID WHERE PRP.REQ_ID = @p_REQ_ID AND STATUS = 'C' AND (PRP.PROCESS_ID = 'SIGN' AND TLUSER.TLNANME = PAC.SIGN_USER) END ELSE ----------KO CÓ CẤP DUYỆT TRUNG GIAN------------- BEGIN SELECT DISTINCT PRP.REQ_ID, PRP.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE CB.BRANCH_NAME END AS DVDM_NAME, TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES FROM PL_REQUEST_PROCESS PRP LEFT JOIN CM_DVDM CD ON CD.DVDM_ID = PRP.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE, TU.DEP_ID, TU.TLSUBBRID FROM TL_USER TU LEFT JOIN ( SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID FROM PL_COSTCENTER PC LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID ) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID UNION ALL SELECT TM.TLNAME, TLU.TLFullName, TM.RoleDisplayName AS RoleName, PCOST.DVDM_ID, TM.DEP_ID AS SECUR_CODE, TM.DEP_ID AS DEP_ID, TM.BRANCH_ID AS TLSUBBRID FROM SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN TL_USER TLU ON TM.TLNAME = TLU.TLNANME LEFT JOIN ( SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID FROM PL_COSTCENTER PC LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID ) PCOST ON TM.BRANCH_ID = PCOST.BRANCH_ID AND TM.DEP_ID = PCOST.DEP_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE, TU.DEP_ID,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TLUSER ON (TLUSER.RoleName = PRP.ROLE_USER) AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '') AND ( ((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) = 'HS' AND TLUSER.SECUR_CODE = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '') OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) <> 'HS' ) AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '') LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID WHERE PRP.REQ_ID = @p_REQ_ID AND STATUS = 'C' END END -----------------------BAODNQ 13/7/2022 ĐÁNH GIÁ NCC--------------------------- ELSE IF (@p_TYPE = 'RATE_SUP') BEGIN IF(@PROCESS_CURR='DMMS') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN TR_RATE_SUPPLIER_MASTER TR ON TR.RATE_ID=PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( ------------BAODNQ 11/10/2022 : Ở bước điều phối lần đầu tiên cả GDDV và KSV cùng thấy phiếu-------------- --SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC --LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' --WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' OR (PRC.STATUS_JOB = 'U' AND EXISTS( SELECT PC.ID FROM PL_REQUEST_PROCESS_CHILD PC WHERE PC.REQ_ID = PRC.REQ_ID AND PC.TYPE_JOB = 'KS' AND PC.MAKER_ID IS NULL AND PC.TRANFER_DT IS NULL ) AND NOT EXISTS(SELECT PC_2.ID FROM PL_REQUEST_PROCESS_CHILD PC_2 WHERE PC_2.REQ_ID = PRC.REQ_ID AND PC_2.TYPE_JOB = 'XL') ) ) --------------------END BAODNQ 11/10/2022---------------- )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID FROM ( SELECT TS.TLNANME,TS.TLFullName, --TS.RoleName, AR.DisplayName AS RoleName, TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS JOIN AbpUserRoles AU ON TS.ID = AU.UserId JOIN AbpRoles AR ON AU.RoleId = AR.Id UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID LEFT JOIN( SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC UNION ALL SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM ( SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE CD.IS_KHOI <>1 GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID ) DVDM ) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID ) TempU ON TempU.RoleName=PL.ROLE_USER AND TempU.DMMS_ID = TR.DMMS_ID AND TempC.ID IS NULL WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN dbo.TR_RATE_SUPPLIER_MASTER TRS ON TRS.RATE_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = TRS.SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME, TempU.TLNANME AS TLNAME, TempU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName, --TU.RoleName, AR.DisplayName AS RoleName, Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU JOIN AbpUserRoles AU ON TU.ID = AU.UserId JOIN AbpRoles AR ON AU.RoleId = AR.Id LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID ) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID, TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END -----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ--- ELSE IF(@p_TYPE LIKE 'LAYOUT_BV') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.CON_LAYOUT_BLUEPRINT WHERE CON_LAYOUT_BLUEPRINT_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN dbo.CON_LAYOUT_BLUEPRINT RD ON RD.CON_LAYOUT_BLUEPRINT_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME, TempU.TLNANME AS TLNAME, TempU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END -----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ--- --NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE ELSE IF(@p_TYPE LIKE 'PYCQLX') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_REQUEST_CAR_COST WHERE REQ_COST_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN dbo.TR_REQUEST_CAR_COST RD ON RD.REQ_COST_ID = PL.REQ_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN ( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE REQ_ID =@p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS 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 LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM ( SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM dbo.TL_USER TS UNION ALL SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1' ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END --NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE -- PHIẾU YÊU CẦU KHO VẬT LIỆU -- ELSE IF (@p_TYPE = 'PYC-KVL') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.MW_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') DECLARE @ID_KSV_PROCESS VARCHAR(20) = (SELECT TOP 1 ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND TYPE_JOB='KS') DECLARE @ID_KSV_CHILD VARCHAR(20) = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID=@ID_KSV_PROCESS) -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '') AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME, TempU.TLNANME AS TLNAME, TempU.TLFullName, PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS 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, CASE WHEN TempC.LEVEL_JOB = 1 OR (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P')) THEN 'Approve' ELSE NULL END AS STATUS FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT, PRC.LEVEL_JOB, PRC.TYPE_JOB FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END -- END PHIẾU YÊU CẦU KHO VẬT LIỆU -- -- TỜ TRÌNH THANH LÝ TÀI SẢN -- ELSE IF (@p_TYPE LIKE 'TTTL') BEGIN IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL) BEGIN SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.ASS_PLAN_REQUEST_LIQUID_MASTER WHERE REQ_ID = @p_REQ_ID) SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C') IF(@CURENT_PROCESS IN ('NEW', 'HC_APP'))--ĐANG Ở BƯỚC ĐƠN VỊ TẠO VÀ GDDV DUYỆT BEGIN SET @MENU_PERMISSON = 'Pages.Administration.AssReqDocLiquidation' END ELSE IF(@CURENT_PROCESS IN ('DVCM_DP'))--ĐANG Ở BƯỚC DVCM ĐIỀU PHỐI BEGIN SET @MENU_PERMISSON = 'Pages.Administration.PlanTransfer' END ELSE IF(@CURENT_PROCESS IN ('GDK_APP', 'TKTGD_APP', 'TGD_APP', 'TKHDQT_APP', 'TKHDQT_APP'))--ĐANG ĐẾN BƯỚC PHÊ DUYỆT TỜ TRÌNH BEGIN SET @MENU_PERMISSON = 'Pages.Administration.AssReqDocLiqApprove' END -- Nếu có cấp phê duyệt trung gian IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP') BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES, PL.ROLE_USER FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 ) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '') AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @SIGN_USER) END ELSE BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME, TempU.TLNANME AS TLNAME, TempU.TLFullName, PL.NOTES, PL.ROLE_USER FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.ID = @PROCESS_ID OR @PROCESS_ID = '' OR @PROCESS_ID IS NULL) END END ELSE IF(@IS_HAS_CHILD=1) BEGIN SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS 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, PL.ROLE_USER, CASE WHEN TempC.LEVEL_JOB = 1 THEN 'Approve' WHEN (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P')) THEN 'Confirm' ELSE NULL END AS STATUS FROM dbo.PL_REQUEST_PROCESS PL LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID LEFT JOIN ( SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT, PRC.LEVEL_JOB, PRC.TYPE_JOB FROM dbo.PL_REQUEST_PROCESS_CHILD PRC LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ' WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' ) )TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME LEFT JOIN ( SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 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 UNION ALL SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU LEFT JOIN dbo.TL_USER US ON US.TLNANME = TU.TLNAME LEFT JOIN( SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '') AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '') AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON) AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1' ---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES UNION ALL SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE RL.DisplayName <>'DISABLE' --- END LUCTV 19062023 ) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='') WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C' END END -- END TỜ TRÌNH THANH LÝ TS -- -- BEGIN PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO -- ELSE IF (@P_TYPE = 'SHO') BEGIN SELECT @CURENT_PROCESS=PROCESS_ID, @AUTH_STATUS=AUTH_STATUS FROM SHO_REQ_SERVICE WHERE REQ_ID=@p_REQ_ID -- LƯU NHÁP IF(@CURENT_PROCESS IS NULL AND @AUTH_STATUS='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM SHO_REQ_SERVICE WHERE REQ_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM SHO_REQ_SERVICE A WHERE A.REQ_ID=@p_REQ_ID AND A.AUTH_STATUS='R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@CURENT_PROCESS='SIGN') BEGIN SELECT A.PROCESS_ID, CHECKER_ID AS TLNAME FROM SHO_REQUEST_PROCESS A WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.PROCESS_ID=@CURENT_PROCESS AND A.CHECKER_ID=@p_USER_LOGIN END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@CURENT_PROCESS='CONFIRM') BEGIN SELECT 'CONFIRM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME FROM SHO_REQUEST_PROCESS A WHERE A.REQ_ID=@p_REQ_ID AND A.PROCESS_ID='APPNEW' AND A.CHECKER_ID IS NOT NULL AND A.CHECKER_ID=@p_USER_LOGIN END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@CURENT_PROCESS='APPNEW') BEGIN -- TRƯỞNG ĐƠN VỊ MẶC ĐỊNH SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME FROM SHO_REQUEST_PROCESS A INNER JOIN AbpRoles R ON A.ROLE_USER=R.DisplayName INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId INNER JOIN TL_USER U ON UR.UserId=U.ID AND A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID=@CURENT_PROCESS AND U.TLNANME=@p_USER_LOGIN -- ỦY QUYỀN TRƯỞNG ĐƠN VỊ UNION SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME FROM SHO_REQUEST_PROCESS A INNER JOIN FN_GET_USER_BY_ROLE(NULL,NULL,NULL, @MENU_PERMISSON) U ON A.ROLE_USER=U.ROLE_NEW AND A.DEP_ID=U.DEP_ID AND A.BRANCH_ID=U.BRANCH_ID WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID=@CURENT_PROCESS AND U.TLNANME=@p_USER_LOGIN END ELSE IF(@CURENT_PROCESS='DVCM') BEGIN -- TRƯỞNG ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'TRUONG_DVCM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME, NULL AS NOTES FROM SHO_REQ_SERVICE_DVCM D INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.CHECKER_ID=@p_USER_LOGIN AND A.PROCESS_ID=@CURENT_PROCESS AND D.STEP_APPROVE=1 UNION -- KIỂM SOÁT VIÊN ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'KSV_DVCM' AS PROCESS_ID, ROLE_USER, C.TLNAME AS TLNAME, NULL AS NOTES FROM SHO_REQ_SERVICE_DVCM D INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID WHERE D.REQ_ID=@p_REQ_ID AND A.PROCESS_ID=@CURENT_PROCESS AND C.TLNAME=@p_USER_LOGIN AND A.CHECKER_ID IS NOT NULL AND D.STEP_APPROVE IS NOT NULL AND D.STEP_APPROVE=C.LEVEL_JOB+1 UNION -- NHÂN VIÊN XỬ LÝ ĐƠN VỊ CHUYÊN MÔN SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'NVXL_DVCM' AS PROCESS_ID, C.TYPE_JOB AS ROLE_USER, C.TLNAME AS TLNAME, D.REF_CONTENT AS NOTES FROM SHO_REQ_SERVICE_DVCM D INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID AND C.TYPE_JOB='XL' WHERE D.REQ_ID=@p_REQ_ID AND A.PROCESS_ID=@CURENT_PROCESS AND A.CHECKER_ID IS NOT NULL AND C.TLNAME=@p_USER_LOGIN AND D.REQ_STATUS='E' END END -- END PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO -- -- BEGIN PHIẾU YÊU CẦU Nhập kho tập trung -- DECLARE @BRN VARCHAR(15) = '', @DEP VARCHAR(15) = '' IF (@P_TYPE = 'IN_DOC_QLK') BEGIN SELECT @AUTH_STATUS=AUTH_STATUS FROM REQ_IN_DOCUMENT_GATHER ridg WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID -- LƯU NHÁP IF(@AUTH_STATUS ='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_IN_DOCUMENT_GATHER ridg WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_IN_DOCUMENT_GATHER ridg WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@AUTH_STATUS='P') BEGIN SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME FROM REQ_IN_DOCUMENT_GATHER ridg WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P' END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@AUTH_STATUS IN ('U')) BEGIN SELECT @DEP = DEP_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID -- IF (ISNULL(@BRN, '') = '') -- BEGIN -- SET @BRN = @DEP -- SET @DEP = '' -- END SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('A')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('N')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('S','C','M','T')) BEGIN -- Xử lý điều phối SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME FROM REQ_IN_DOCUMENT_GATHER ridg WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL END END -- END PYC Nhập kho tập trung -- -- BEGIN PHIẾU YÊU CẦU mượn kho tập trung -- ELSE IF (@P_TYPE = 'BOR_DOC_QLK') BEGIN SELECT @AUTH_STATUS=AUTH_STATUS FROM REQ_BOR_DOCUMENT_GATHER ridg WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID -- LƯU NHÁP IF(@AUTH_STATUS ='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_BOR_DOCUMENT_GATHER ridg WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_BOR_DOCUMENT_GATHER ridg WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@AUTH_STATUS='P') BEGIN SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME FROM REQ_BOR_DOCUMENT_GATHER ridg WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P' END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@AUTH_STATUS IN ('U')) BEGIN SELECT @DEP = DEP_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID -- IF (ISNULL(@BRN, '') = '') -- BEGIN -- SET @BRN = @DEP -- SET @DEP = '' -- END SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('A')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('N')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('S','C','M','T')) BEGIN -- Xử lý điều phối SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME FROM REQ_BOR_DOCUMENT_GATHER ridg WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL END END -- END PYC mượn kho tập trung -- -- BEGIN PHIẾU YÊU CẦU xuất kho tập trung -- ELSE IF (@P_TYPE = 'OUT_DOC_QLK') BEGIN SELECT @AUTH_STATUS=AUTH_STATUS FROM REQ_OUT_DOCUMENT_GATHER ridg WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID -- LƯU NHÁP IF(@AUTH_STATUS ='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_OUT_DOCUMENT_GATHER ridg WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_OUT_DOCUMENT_GATHER ridg WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@AUTH_STATUS='P') BEGIN SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME FROM REQ_OUT_DOCUMENT_GATHER ridg WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P' END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@AUTH_STATUS IN ('U')) BEGIN SELECT @DEP = DEP_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID -- IF (ISNULL(@BRN, '') = '') -- BEGIN -- SET @BRN = @DEP -- SET @DEP = '' -- END SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('A')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('N')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('S','C','M','T')) BEGIN -- Xử lý điều phối SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME FROM REQ_OUT_DOCUMENT_GATHER ridg WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL END END -- END PYC xuất kho tập trung -- -- BEGIN PHIẾU YÊU CẦU hoàn kho tập trung -- ELSE IF (@P_TYPE = 'COL_DOC_QLK') BEGIN SELECT @AUTH_STATUS=AUTH_STATUS FROM REQ_COL_DOCUMENT_GATHER ridg WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID -- LƯU NHÁP IF(@AUTH_STATUS ='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_COL_DOCUMENT_GATHER ridg WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_COL_DOCUMENT_GATHER ridg WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@AUTH_STATUS='P') BEGIN SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME FROM REQ_COL_DOCUMENT_GATHER ridg WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P' END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@AUTH_STATUS IN ('U')) BEGIN SELECT @DEP = DEP_ID FROM REQ_COL_DOCUMENT_GATHER WHERE REQ_COL_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM REQ_COL_DOCUMENT_GATHER WHERE REQ_COL_DOC_GATHER_ID = @p_REQ_ID -- IF (ISNULL(@BRN, '') = '') -- BEGIN -- SET @BRN = @DEP -- SET @DEP = '' -- END SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubRefundDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('A')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_COL_DOCUMENT_GATHER WHERE REQ_COL_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubRefundDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('N')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_COL_DOCUMENT_GATHER WHERE REQ_COL_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubRefundDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('S','C','M','T')) BEGIN -- Xử lý điều phối SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME FROM REQ_COL_DOCUMENT_GATHER ridg WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL END END -- END PYC hoàn kho tập trung -- -- BEGIN PHIẾU YÊU CẦU hủy kho tập trung -- ELSE IF (@P_TYPE = 'DES_DOC_QLK') BEGIN SELECT @AUTH_STATUS=AUTH_STATUS FROM REQ_DES_DOCUMENT_GATHER ridg WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID -- LƯU NHÁP IF(@AUTH_STATUS ='E') BEGIN SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_DES_DOCUMENT_GATHER ridg WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID END -- TỪ CHỐI ELSE IF(@AUTH_STATUS='R') BEGIN SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME FROM REQ_DES_DOCUMENT_GATHER ridg WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R' END -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT ELSE IF(@AUTH_STATUS='P') BEGIN SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME FROM REQ_DES_DOCUMENT_GATHER ridg WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P' END -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT ELSE IF(@AUTH_STATUS IN ('U')) BEGIN SELECT @DEP = DEP_ID FROM REQ_DES_DOCUMENT_GATHER WHERE REQ_DES_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM REQ_DES_DOCUMENT_GATHER WHERE REQ_DES_DOC_GATHER_ID = @p_REQ_ID -- IF (ISNULL(@BRN, '') = '') -- BEGIN -- SET @BRN = @DEP -- SET @DEP = '' -- END SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqCancelDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('A')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_DES_DOCUMENT_GATHER WHERE REQ_DES_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqCancelDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('N')) BEGIN SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_DES_DOCUMENT_GATHER WHERE REQ_DES_DOC_GATHER_ID = @p_REQ_ID SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP IF (ISNULL(@BRN, '') = '') BEGIN SET @BRN = @DEP SET @DEP = '' END SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqCancelDocument') WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') AND (DEP_ID = @DEP OR BRANCH_ID = @BRN) END ELSE IF(@AUTH_STATUS IN ('S','C','M','T')) BEGIN -- Xử lý điều phối SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME FROM REQ_DES_DOCUMENT_GATHER ridg WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL END END -- END PYC hủy kho tập trung -- END ----LUCTV: 19062023_SECRETKEY - FIX LỖI SHOW THIẾU TÊN GIÁM ĐỐC KHỐI