ALTER VIEW dbo.ASS_PENDING_ITEM
AS
SELECT
  A.ADDNEW_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_ADDNEW' TRAN_TYPE
 ,N'Nhập mới TSCD/CCLD' TRAN_TYPE_NAME
 ,A.BRANCH_CREATE
 ,A.TYPE_ID
 ,A.BUY_PRICE AMT
 ,A.ASSET_DESC DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,A.MAKER_ID_KT
 ,A.AUTH_STATUS_KT
 ,A.CREATE_DT_KT
FROM ASS_ADDNEW A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS

UNION ALL

SELECT
  A.USE_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_USE' TRAN_TYPE
 ,N'Xuất sử dụng' TRAN_TYPE_NAME
 ,A.BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,A.NOTES DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,'' MAKER_ID_KT
 ,'' AUTH_STATUS_KT
 ,'' CREATE_DT_KT
--FROM ASS_USE A
FROM ASS_USE A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

UNION ALL


SELECT
  A.TRANSFER_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_TRANSFER' TRAN_TYPE
 ,N'Giao dịch điều chuyển' TRAN_TYPE_NAME
 ,A.BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,A.[DESCRIPTION] DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,'' MAKER_ID_KT
 ,'' AUTH_STATUS_KT
 ,'' CREATE_DT_KT
FROM ASS_TRANSFER A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

--UNION ALL

--SELECT  A.ASSET_ID ID, A.AUTH_STATUS, AA.AUTH_STATUS_NAME, A.MAKER_ID, 'ASS_MASTER' TRAN_TYPE, N'Bảng TSCD/CCLD' TRAN_TYPE_NAME,
--'' BRANCH_CREATE, A.TYPE_ID
--FROM ASS_MASTER A 
--INNER JOIN CM_AUTH_STATUS AA ON AA.AUTH_STATUS = A.AUTH_STATUS

UNION ALL

SELECT
  A.COLLECT_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_COLLECT' TRAN_TYPE
 ,N'Giao dịch thu hồi' TRAN_TYPE_NAME
 ,A.BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,A.COLLECT_NOTE DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,'' MAKER_ID_KT
 ,'' AUTH_STATUS_KT
 ,'' CREATE_DT_KT
FROM ASS_COLLECT A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

SELECT
  A.GROUP_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_GROUP' TRAN_TYPE
 ,N'Nhóm tài sản' TRAN_TYPE_NAME
 ,'' BRANCH_CREATE
 ,A.TYPE_ID
 ,0 AMT
 ,A.NOTES DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,'' MAKER_ID_KT
 ,'' AUTH_STATUS_KT
 ,'' CREATE_DT_KT
FROM ASS_GROUP A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS


UNION ALL

SELECT
  A.AMORT_ID ID
 ,CASE
    WHEN A.PROCESS_STATUS = 'P' THEN 'U'
    ELSE 'A'
  END AUTH_STATUS
 ,CASE
    WHEN A.PROCESS_STATUS = 'P' THEN N'Chưa thực thi'
    ELSE N'Đã thực thi'
  END AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_AMORT' TRAN_TYPE
 ,A.NOTES TRAN_TYPE_NAME
 ,--N'Khấu hao' 
  A.BRANCH_ID BRANCH_CREATE
 ,A.ASSET_TYPE AS [TYPE_ID]
 ,A.TOTAL_AMT AMT
 ,A.NOTES DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,'' MAKER_ID_KT
 ,'' AUTH_STATUS_KT
 ,'' CREATE_DT_KT
FROM ASS_AMORT A


UNION ALL

SELECT
  A.ASSET_ID ID
 ,A.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,A.MAKER_ID
 ,'ASS_UPDATE' TRAN_TYPE
 ,N'Cập nhật thông tin tài sản' TRAN_TYPE_NAME
 ,A.BRANCH_ID BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,A.NOTES DIENGIAI
 ,A.CREATE_DT CREATE_DT
 ,A.MAKER_ID MAKER_ID_KT
 ,A.AUTH_STATUS AUTH_STATUS_KT
 ,A.CREATE_DT CREATE_DT_KT
FROM ASS_UPDATE A
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = A.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

-----thieuvq 5102016 - them dieu kien kiem tra danh sach dieu chuyen nhieu tai san chua duyet
UNION ALL

SELECT
  A.ASSET_ID ID
 ,BB.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,BB.MAKER_ID
 ,'ASS_TRANSFER_MULTI_MASTER' TRAN_TYPE
 ,N'Điều chuyển nhiều tài sản.' TRAN_TYPE_NAME
 ,A.BRANCH_ID BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,BB.NOTES DIENGIAI
 ,BB.CREATE_DT CREATE_DT
 ,BB.MAKER_ID_KT
 ,BB.AUTH_STATUS_KT
 ,BB.CREATE_DT_KT
FROM ASS_TRANSFER_MULTI_DT A
INNER JOIN ASS_TRANSFER_MULTI_MASTER BB
  ON A.TRANS_MULTI_MASTER_ID = BB.TRANS_MULTI_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
SELECT
  A.ASSET_ID ID
 ,BB.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,BB.MAKER_ID
 ,'ASS_USE_MULTI_MASTER' TRAN_TYPE
 ,N'Xuất nhiều tài sản.' TRAN_TYPE_NAME
 ,A.BRANCH_ID BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,BB.NOTES DIENGIAI
 ,BB.CREATE_DT CREATE_DT
 ,BB.MAKER_ID_KT
 ,BB.AUTH_STATUS_KT
 ,BB.CREATE_DT_KT
FROM ASS_USE_MULTI_DT A
INNER JOIN ASS_USE_MULTI_MASTER BB
  ON A.USER_MASTER_ID = BB.USER_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

UNION ALL

-----thieuvq 5102016 - them dieu kien kiem tra danh sach xuat nhieu tai san chua duyet
SELECT
  A.ASSET_ID ID
 ,BB.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,BB.MAKER_ID
 ,'ASS_COLLECT_MULTI_MASTER' TRAN_TYPE
 ,N'Thu hồi nhiều tài sản.' TRAN_TYPE_NAME
 ,A.BRANCH_ID BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,BB.NOTES DIENGIAI
 ,BB.CREATE_DT CREATE_DT
 ,BB.MAKER_ID_KT
 ,BB.AUTH_STATUS_KT
 ,BB.CREATE_DT_KT
FROM ASS_COLLECT_MULTI_DT A
INNER JOIN ASS_COLLECT_MULTI_MASTER BB
  ON A.COL_MULTI_MASTER_ID = BB.COL_MULTI_MASTER_ID
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID


UNION ALL

-----luctv 07012018 - them dieu kien kiem tra danh sach thanh ly nhieu tai san chua duyet
SELECT
  A.ASSET_ID ID
 ,BB.AUTH_STATUS
 ,AA.AUTH_STATUS_NAME
 ,BB.MAKER_ID
 ,'ASS_LIQ_MULTI_MASTER' TRAN_TYPE
 ,N'Thanh lý nhiều tài sản.' TRAN_TYPE_NAME
 ,BB.BRANCH_ID BRANCH_CREATE
 ,CC.TYPE_ID
 ,CC.BUY_PRICE AMT
 ,BB.NOTES DIENGIAI
 ,BB.CREATE_DT CREATE_DT
 ,BB.MAKER_ID_KT
 ,BB.AUTH_STATUS_KT
 ,BB.CREATE_DT_KT
FROM ASS_LIQUIDATION_DT A
INNER JOIN ASS_LIQUIDATION BB
  ON A.LIQ_ID = BB.LIQ_ID
INNER JOIN CM_AUTH_STATUS AA
  ON AA.AUTH_STATUS = BB.AUTH_STATUS
INNER JOIN ASS_MASTER CC
  ON CC.ASSET_ID = A.ASSET_ID

GO


ALTER PROCEDURE dbo.ASS_PENDING_ITEM_Searchnew
	@p_CREATE_DT varchar(15) = NULL,
	@p_BRANCH_ID VARCHAR(15) = NULL,
	@p_BRANCH_LOGIN NVARCHAR(100) = NULL,
	@p_ASSET_TYPE VARCHAR(15) = NULL,
	@p_MAKER_ID VARCHAR(100) = NULL,
	@p_AUTH_STATUS VARCHAR(15) = NULL,
	@p_MODULE NVARCHAR(500) = NULL,
	@p_USER_LOGIN varchar(20) = NULL
AS
DECLARE @l_BRANCH_TYPE VARCHAR(15)
SET @l_BRANCH_TYPE = (SELECT A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_ID = @p_BRANCH_LOGIN)
declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

DECLARE @LISTTYPE TABLE(TRN_TYPE VARCHAR(50), DUONGDAN VARCHAR(500), PHANHE NVARCHAR(500))
INSERT INTO @LISTTYPE values ('ASS_ADDNEW', '/AssetManager/ASSAddNewList',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_USE', '/AssetManager/ASSUseList',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_TRANSFER', '/AssetManager/ASSTransferList',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_MASTER', '/AssetManager/ASSMasterList',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_COLLECT', '/AssetManager/ASSCollectList',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_GROUP', '/AssCommon/AssetGroupList',N'Quản lý danh mục')
INSERT INTO @LISTTYPE values('ASS_AMORT', '/AssetManager/ASSAmort',N'Quản lý TSCĐ/CCLĐ')
INSERT INTO @LISTTYPE values('ASS_UPDATE', '/AssetManager/ASSUpdateList',N'Quản lý TSCĐ/CCLĐ')
BEGIN -- PAGING
select A.*, BB.DUONGDAN DUONGDAN, BB.PHANHE PHANHE, CM.BRANCH_NAME
-- SELECT END
	from ASS_PENDING_ITEM  A
	LEFT JOIN CM_BRANCH CM ON A.BRANCH_CREATE= CM.BRANCH_ID
	LEFT JOIN @LISTTYPE BB ON A.TRAN_TYPE = BB.TRN_TYPE
where 
--A.BRANCH_CREATE LIKE '%'+ @p_BRANCH_CREATE +'%' OR @p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL
	(A.MAKER_ID_KT LIKE '%'+ @p_MAKER_ID +'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
--AND(CM.BRANCH_NAME LIKE '%'+@p_BRANCH_NAME +'%' OR @p_BRANCH_NAME IS NULL OR @p_BRANCH_NAME='')
AND (CONVERT(DATE,A.CREATE_DT_KT)= CONVERT(DATE,@p_CREATE_DT,103) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT='')
AND (A.AUTH_STATUS_KT =@p_AUTH_STATUS OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS ='')
AND (BB.PHANHE = @p_MODULE OR @p_MODULE IS NULL OR @p_MODULE = '' OR @p_MODULE = N'---Tất cả---')
AND ((A.BRANCH_CREATE IN (SELECT * FROM @tmp) OR  @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
OR ((A.BRANCH_CREATE IS NULL OR A.BRANCH_CREATE = '') AND @l_BRANCH_TYPE = 'HS'))
-- PAGING END
END -- PAGING