DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]
@p_REQ_ID VARCHAR=NULL,
@p_USER_LOGIN VARCHAR=NULL,
@p_TYPE VARCHAR=NULL
AS
BEGIN

DECLARE @PROCESS_CURR VARCHAR,@IS_HAS_CHILD BIT

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'

IF
BEGIN
IF
BEGIN

DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50)
    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 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  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME='')))  
        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
        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.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
        FROM dbo.TL_SYS_ROLE_MAPPING 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  CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
        ) 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
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.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.TL_SYS_ROLE_MAPPING 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 CAST >= CAST AS DATE)
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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
BEGIN
IF

BEGIN

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 
    (
    SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
        dbo.TL_SYS_ROLE_MAPPING TM
        LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
        WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
        ) TU
    ) TempU ON (TempU.RoleName =PL.ROLE_USER  OR PL.ROLE_USER=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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='') 
    WHERE REQ_ID =@p_REQ_ID AND STATUS='C'

END
ELSE IF
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 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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
dbo.TL_SYS_ROLE_MAPPING TM
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
WHERE CAST >= CAST AS DATE)
) 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 IN (SELECT VALUE FROM WSISPLIT) OR EXISTS (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_NEW IN (SELECT VALUE FROM WSISPLIT) AND ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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='')

WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
END
END

ELSE IF
BEGIN
IF
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 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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
dbo.TL_SYS_ROLE_MAPPING TM
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
WHERE CAST >= CAST AS DATE)
) 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  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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
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 CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
dbo.TL_SYS_ROLE_MAPPING TM
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
WHERE CAST >= CAST AS DATE)
) 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 IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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
ELSE IF
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 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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
dbo.TL_SYS_ROLE_MAPPING TM
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
WHERE CAST >= CAST AS DATE)
) 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 IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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
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] ('', '', '')
        ) 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
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 
                OR PRP.ROLE_USER IN ( 
                    SELECT ROLE_NEW 
                    FROM TL_SYS_ROLE_MAPPING TM 
                    WHERE TM.ROLE_OLD = TLUSER.RoleName 
                    AND TM.TLNAME IS NULL OR TM.TLNAME = ''
                )
            )
        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.ROLE_NEW AS RoleName, PCOST.DVDM_ID, 
            TM.DEP_ID AS SECUR_CODE, TM.DEP_ID AS DEP_ID, TM.BRANCH_ID AS TLSUBBRID
            FROM TL_SYS_ROLE_MAPPING 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  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
        ) 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
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 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
(
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,
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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
dbo.TL_SYS_ROLE_MAPPING TM
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
WHERE CAST >= CAST AS DATE)
) 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 
            OR (TempU.RoleName='KSV' 
                AND NOT EXISTS(SELECT TOP 1 PP.CHECKER_ID FROM PL_PROCESS PP WHERE PP.REQ_ID = @p_REQ_ID AND PP.PROCESS_ID = 'DMMS' AND PP.CHECKER_ID = TempU.TLNANME ORDER BY PP.APPROVE_DT DESC)
            )
        )
        OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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 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  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME='')))  
            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
            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 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.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
            FROM dbo.TL_SYS_ROLE_MAPPING 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  CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
            ) 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 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.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
            dbo.TL_SYS_ROLE_MAPPING TM
            LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
            WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
            )  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 IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) 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
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100