ALTER PROCEDURE [dbo].[PL_REQUEST_TRANSFER_MOBILE_Search] @p_REQ_ID VARCHAR(15) = NULL,
@p_REQ_CODE NVARCHAR(100) = NULL,
@p_REQ_NAME NVARCHAR(200) = NULL,
@p_REQ_DT VARCHAR(20) = NULL,
@p_REQ_TYPE INT = NULL,
@p_REQ_CONTENT NVARCHAR(1000) = NULL,
@p_REQ_REASON NVARCHAR(500) = NULL,
@p_TOTAL_AMT DECIMAL = NULL,
@p_NOTES NVARCHAR(1000) = NULL,
@p_RECORD_STATUS VARCHAR(1) = NULL,
@p_MAKER_ID VARCHAR(12) = NULL,
@p_CREATE_DT VARCHAR(20) = NULL,
@p_AUTH_STATUS VARCHAR(50) = NULL,
@p_CHECKER_ID VARCHAR(12) = NULL,
@p_APPROVE_DT VARCHAR(20) = NULL,
@p_PROCESS_ID VARCHAR(15) = NULL,
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_DEP_ID VARCHAR(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15),
@p_ROLE_USER VARCHAR(20),
@p_TLNAME_USER VARCHAR(15),
@p_FR_DATE VARCHAR(20) = NULL,
@p_TO_DATE VARCHAR(20) = NULL,
@p_TYPE_TRANFER VARCHAR(15),
@p_TYPE VARCHAR(15),
@p_YEAR INT,
@p_TOP INT = 10,
@p_IS_TRANSFER VARCHAR(10) = NULL,
@p_NGUOIXULY NVARCHAR(15) = NULL,

@p_TO_DEP_ID VARCHAR(15) = NULL,
@p_TO_BRANCH_ID VARCHAR(15) = NULL,
@p_FROM_DEP_ID VARCHAR(15) = NULL,
@p_FROM_BRANCH_ID VARCHAR(15) = NULL,
@p_TO_GD_NAME NVARCHAR(1000) = NULL,
@p_FROM_GD_NAME NVARCHAR(1000) = NULL

AS
BEGIN -- PAGING
  --	DECLARE @TABLE_ROLE TABLE 
  --	( ROLE_ID VARCHAR(20))
  --	INSERT INTO @TABLE_ROLE VALUES(@p_ROLE_USER)
  --
  --	
  --	INSERT INTO @TABLE_ROLE
  --	SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER

  DECLARE @MENU_PERMISSION NVARCHAR(500) = ''--permission chức năng call store, chưa check được mobile nên gán = ''


  DECLARE @DEP_ID VARCHAR(15)
         ,@BRANCH_TYPE VARCHAR(15)
         ,@TO_BRANCH_TYPE VARCHAR(15)
         ,@FROM_BRANCH_TYPE VARCHAR(15)

  DECLARE @COST_ID TABLE (
    COST_ID VARCHAR(15)
  )

  DECLARE @DVDM_ID TABLE (
    DVDM_ID VARCHAR(15)
  )
  SET @DEP_ID = (SELECT
      SECUR_CODE
    FROM dbo.TL_USER
    WHERE TLNANME = @p_TLNAME_USER)
  SET @BRANCH_TYPE = (SELECT
      BRANCH_TYPE
    FROM dbo.CM_BRANCH
    WHERE BRANCH_ID = @p_BRANCH_LOGIN)

  -- ĐƠN VỊ CHUYỂN
  SET @FROM_BRANCH_TYPE = (SELECT
      BRANCH_TYPE
    FROM dbo.CM_BRANCH
    WHERE BRANCH_ID = @p_FROM_BRANCH_ID)
  -- ĐƠN VỊ NHẬN
  SET @TO_BRANCH_TYPE = (SELECT
      BRANCH_TYPE
    FROM dbo.CM_BRANCH
    WHERE BRANCH_ID = @p_TO_BRANCH_ID)


  IF (EXISTS (SELECT
        DEP_CODE
      FROM dbo.CM_DEPARTMENT
      WHERE DEP_ID = @DEP_ID
      AND DEP_CODE LIKE '069%')
    )
  BEGIN
    SET @BRANCH_TYPE = 'HS'
  END



  SELECT
    * INTO #AUTHOR
  FROM [dbo].[FN_GET_ROLE_USER_BY_TLNAME](@p_TLNAME_USER, @MENU_PERMISSION)

  SELECT
    * INTO #AUTHOR_DVDM
  FROM [dbo].[FN_GET_ROLE_DVDM_USER_BY_TLNAME](@p_TLNAME_USER, @MENU_PERMISSION)

  INSERT INTO @COST_ID
    SELECT
      COST_ID
    FROM dbo.PL_COSTCENTER_DT
    WHERE DEP_ID = @DEP_ID
    AND BRANCH_ID = @p_BRANCH_LOGIN
  INSERT INTO @DVDM_ID
    SELECT
      DVDM_ID
    FROM dbo.PL_COSTCENTER
    WHERE COST_ID IN (SELECT
        COST_ID
      FROM @COST_ID)
    GROUP BY DVDM_ID


  -- PAGING BEGIN
  SELECT
    A.REQ_ID
   ,A.REQ_CODE
   ,G.BRANCH_NAME
   ,A.REQ_NAME
   ,A.TOTAL_AMT
   ,N'Chờ duyệt' AS AUTH_STATUS_NAME
   ,'U' AS AUTH_STATUS --A.AUTH_STATUS
  -- SELECT END
  FROM PL_REQUEST_DOC A
  INNER JOIN dbo.PL_REQUEST_PROCESS PLRP
    ON PLRP.REQ_ID = A.REQ_ID
      AND PLRP.STATUS = 'C'
  LEFT JOIN dbo.PL_REQUEST_PROCESS RPN
    ON RPN.ID IN (SELECT TOP 1
          Temp.ID
        FROM dbo.PL_REQUEST_PROCESS Temp
        WHERE Temp.REQ_ID = A.REQ_ID
        AND (Temp.STATUS = 'C'
        OR Temp.STATUS = 'R')
        AND (Temp.IS_HAS_CHILD IS NULL
        OR Temp.IS_HAS_CHILD = 0))

  LEFT JOIN CM_BRANCH G
    ON A.BRANCH_ID = G.BRANCH_ID
  LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPNC
    ON RPNC.PROCESS_ID IN (SELECT TOP 1
          Temp.ID
        FROM dbo.PL_REQUEST_PROCESS Temp
        WHERE Temp.REQ_ID = A.REQ_ID
        AND (Temp.STATUS = 'C'
        OR Temp.STATUS = 'R')
        AND Temp.IS_HAS_CHILD = 1)
      AND (RPNC.STATUS_JOB = 'C'
        OR RPNC.STATUS_JOB = 'R')
  OUTER APPLY (SELECT
      [dbo].[FN_PL_REQUEST_DOC_GET_NEXT_USER](A.REQ_ID, @MENU_PERMISSION) NGUOIXULY
     ,'' NGUOIXULYTLNAME) NXL
  WHERE 1 = 1
  --    AND (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' OR A.AUTH_STATUS = @p_AUTH_STATUS)
  AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'
  OR @p_REQ_CODE IS NULL
  OR @p_REQ_CODE = '')
  AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%'
  OR @p_REQ_NAME IS NULL
  OR @p_REQ_NAME = '')
  AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%'
  OR @p_MAKER_ID IS NULL
  OR @p_MAKER_ID = '')
  AND (@p_FR_DATE IS NULL
  OR @p_REQ_CODE IS NOT NULL
  OR CONVERT(DATETIME, A.REQ_DT, 103) >= CONVERT(DATETIME, @p_FR_DATE, 103))
  AND (@p_TO_DATE IS NULL
  OR @p_REQ_CODE IS NOT NULL
  OR CONVERT(DATETIME, A.REQ_DT, 103) <= CONVERT(DATETIME, @p_TO_DATE, 103))

  AND (A.REQ_TYPE = @p_REQ_TYPE
  OR @p_REQ_TYPE IS NULL
  OR @p_REQ_TYPE = -1)
  AND A.RECORD_STATUS = '1'

  --PHONGNT 12/02/2023 Thêm điều kiện TĐV thấy phiếu
  AND ((RPN.PROCESS_ID = 'APPNEW'
  AND RPN.STATUS = 'C'
  AND EXISTS (SELECT
      1
    FROM #AUTHOR_DVDM
    WHERE ROLE_DISPLAYNAME = RPN.ROLE_USER
    AND BRANCH_ID = RPN.BRANCH_ID
    AND ((BRANCH_ID = 'DV0001'
    AND DEP_ID = RPN.DEP_ID)
    OR (BRANCH_ID <> 'DV0001')))
  AND (PLRP.IS_HAS_CHILD = 0
  OR PLRP.IS_HAS_CHILD IS NULL))
  OR (RPNC.TLNAME = @p_TLNAME_USER
  AND RPNC.STATUS_JOB = 'C'
  AND RPNC.TYPE_JOB = 'TP')
  --        OR (RPN.PROCESS_ID='TC' AND EXISTS(SELECT 1 FROM #AUTHOR_DVDM WHERE ROLE_ID=RPN.ROLE_USER AND BRANCH_ID =RPN.BRANCH_ID AND DEP_ID=RPN.DEP_ID))
  --   
  --        OR (RPN.PROCESS_ID IN ('DVDC','DVCM') AND EXISTS(SELECT 1 FROM #AUTHOR_DVDM WHERE ROLE_ID=RPN.ROLE_USER AND DVDM_ID= RPN.DVDM_ID)AND (PLRP.IS_HAS_CHILD=0 OR PLRP.IS_HAS_CHILD IS NULL))
  --END
  OR
  EXISTS (SELECT
      AUTH.ROLE_DISPLAYNAME
    FROM #AUTHOR_DVDM AUTH
    WHERE AUTH.ROLE_DISPLAYNAME = PLRP.ROLE_USER
    AND (
    (PLRP.PROCESS_ID = 'DVDC'
    AND PLRP.BRANCH_ID = AUTH.BRANCH_ID
    AND (PLRP.DEP_ID = AUTH.DEP_ID
    OR PLRP.DEP_ID IS NULL
    OR PLRP.DEP_ID = ''))
    OR PLRP.DVDM_ID = AUTH.DVDM_ID
    OR ((PLRP.DVDM_ID = ''
    OR PLRP.DVDM_ID IS NULL)
    AND (PLRP.BRANCH_ID IS NULL
    OR PLRP.BRANCH_ID = ''))
    ))
  )
  AND (PLRP.IS_HAS_CHILD = 0
  OR PLRP.IS_HAS_CHILD IS NULL)


  -- GiaNT 20/10/2021
  AND (A.REQ_ID IN (SELECT
      REQ_DOC_ID
    FROM PL_REQUEST_TRANSFER)
  )
  AND (NOT EXISTS (SELECT
      *
    FROM dbo.PL_REQUEST_DOC_DT
    WHERE REQ_ID = A.REQ_ID)
  )
  ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING