ALTER VIEW dbo.ASS_MASTER_VIEW
AS
SELECT
  dbo.CM_SUPPLIER.SUP_NAME
 ,dbo.CM_SUPPLIER.SUP_CODE
 ,dbo.CM_SUPPLIER.SUP_TYPE_ID
 ,dbo.CM_SUPPLIER.ADDR
 ,dbo.CM_SUPPLIER.EMAIL
 ,dbo.ASS_GROUP.GROUP_CODE
 ,dbo.ASS_GROUP.GROUP_NAME
 ,dbo.CM_EMPLOYEE.EMP_NAME
 ,dbo.CM_EMPLOYEE.EMP_CODE
 ,dbo.CM_DIVISION.DIV_CODE
 ,dbo.CM_DIVISION.DIV_NAME
 ,dbo.CM_DEPARTMENT.DEP_CODE
 ,dbo.CM_DEPARTMENT.DEP_NAME
 ,dbo.CM_BRANCH.BRANCH_CODE
 ,dbo.CM_BRANCH.BRANCH_NAME
 ,dbo.ASS_MASTER.ASSET_ID
 ,dbo.ASS_MASTER.TYPE_ID
 ,dbo.ASS_MASTER.GROUP_ID
 ,dbo.ASS_MASTER.ASSET_CODE
 ,dbo.ASS_MASTER.ASSET_NAME
 ,dbo.ASS_MASTER.ASSET_SERIAL_NO
 ,dbo.ASS_MASTER.ASSET_DESC
 ,dbo.ASS_MASTER.SUP_ID
 ,dbo.ASS_MASTER.BUY_PRICE
 ,dbo.ASS_MASTER.AMORT_AMT
 ,dbo.ASS_MASTER.ASS_TYPE
 ,dbo.ASS_MASTER.BRANCH_ID
 ,dbo.ASS_MASTER.DEPT_ID
 ,dbo.ASS_MASTER.EMP_ID
 ,dbo.ASS_MASTER.DIVISION_ID
 ,dbo.ASS_MASTER.BUY_DATE
 ,dbo.ASS_MASTER.USE_DATE
 ,dbo.ASS_MASTER.SPECIAL_ASS
 ,dbo.ASS_MASTER.AMORT_MONTH
 ,dbo.ASS_MASTER.AMORT_RATE
 ,dbo.ASS_MASTER.AMORT_START_DATE
 ,dbo.ASS_MASTER.AMORT_END_DATE
 ,dbo.ASS_MASTER.FIRST_AMORT_AMT
 ,dbo.ASS_MASTER.MONTHLY_AMORT_AMT
 ,dbo.ASS_MASTER.AMORTIZED_MONTH
 ,dbo.ASS_MASTER.AMORTIZED_AMT
 ,dbo.ASS_MASTER.LIQUIDATION_DT
 ,dbo.ASS_MASTER.PO_ID
 ,dbo.ASS_MASTER.PD_ID
 ,dbo.ASS_MASTER.WAREHOUSE_ID
 ,dbo.ASS_MASTER.LOCATION
 ,dbo.ASS_MASTER.REF_ASSET_ID
 ,dbo.ASS_MASTER.REF_AMORTIZED_AMT
 ,dbo.ASS_MASTER.WARRANTY_MONTHS
 ,dbo.ASS_MASTER.NOTES
 ,dbo.ASS_MASTER.AMORT_STATUS
 ,dbo.ASS_MASTER.ASS_STATUS
 ,dbo.ASS_MASTER.ASS_STATUS_DESC
 ,dbo.ASS_MASTER.RECORD_STATUS
 ,dbo.ASS_MASTER.AUTH_STATUS
 ,dbo.ASS_MASTER.MAKER_ID
 ,dbo.ASS_MASTER.CREATE_DT
 ,dbo.ASS_MASTER.CHECKER_ID
 ,dbo.ASS_MASTER.APPROVE_DT
 ,dbo.ASS_PO.PO_CODE
 ,dbo.TR_PO_MASTER.PO_NAME
 ,ISNULL(dbo.ASS_MASTER.BUY_PRICE - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_VALUE
 ,ISNULL(dbo.ASS_MASTER.AMORT_AMT - dbo.ASS_MASTER.AMORTIZED_AMT, 0) AS REMAIN_AMORT_AMT
 ,dbo.ASS_TYPE.TYPE_CODE
 ,dbo.ASS_TYPE.TYPE_NAME
 ,dbo.ASS_AMORT_STATUS.STATUS_NAME AS AMORT_STATUS_NAME
  --,dbo.ASS_STATUS.STATUS_NAME AS ASS_STATUS_NAME,
 ,CASE
    WHEN ASS_MASTER.LIQ_W_STATUS = '1' AND
      ASS_MASTER.AMORT_STATUS = 'DTL' THEN N'Đã thanh lý'
    ELSE ASS_STATUS.STATUS_NAME
  END AS ASS_STATUS_NAME
 ,RA.ASSET_CODE AS REF_ASSET_CODE
 ,RA.ASSET_NAME AS REF_ASSET_NAME
 ,dbo.ASS_MASTER.USE_DATE_KT
 ,dbo.ASS_MASTER.BUY_DATE_KT
 ,dbo.ASS_MASTER.USE_STATUS
 ,dbo.ASS_MASTER.BRANCH_CREATE
 ,BRCR.BRANCH_NAME AS BRANCH_CREATE_NAME
 ,BRCR.BRANCH_CODE AS BRANCH_CREATE_CODE
 ,dbo.ASS_MASTER.ACCOUNT_GL
 ,dbo.ASS_MASTER.VAT
 ,dbo.ASS_MASTER.PRICE_VAT
 ,OS.CONTENT AS IS_OS
 ,LICENSE.CONTENT AS IS_MO_LICENSE
 ,ASS_MASTER.OS
 ,ASS_MASTER.MO_LICENSE
 ,
  --RG.GROUP_CODE AS REF_GROUP_CODE, RG.GROUP_NAME AS REF_GROUP_NAME,
  --RT.TYPE_CODE AS REF_TYPE_CODE, RT.TYPE_NAME AS REF_TYPE_NAME
  --dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'KV') KHU_VUC,
  --dbo.FN_GET_CHINHANH(dbo.ASS_MASTER.BRANCH_ID,'CN') CHI_NHANH, 
  --D.BRANCH_NAME PGD
  AN.PR_CODE
 , --SỐ PR
  AN.CONTRACT_ID AS ASS_CONTRACT_CODE
 , -- SỐ HỢP ĐỒNG
  AN.PL_CODE
 , --SỐ TỜ TRÌNH
  AP.INVOICE_NO --SỐ HOÁ ĐƠN
FROM dbo.ASS_MASTER
LEFT OUTER JOIN dbo.ASS_GROUP
  ON dbo.ASS_MASTER.GROUP_ID = dbo.ASS_GROUP.GROUP_ID
LEFT OUTER JOIN dbo.CM_SUPPLIER
  ON dbo.ASS_MASTER.SUP_ID = dbo.CM_SUPPLIER.SUP_ID
LEFT OUTER JOIN dbo.CM_EMPLOYEE
  ON dbo.ASS_MASTER.EMP_ID = dbo.CM_EMPLOYEE.EMP_ID
LEFT OUTER JOIN dbo.CM_BRANCH
  ON dbo.ASS_MASTER.BRANCH_ID = dbo.CM_BRANCH.BRANCH_ID
LEFT OUTER JOIN dbo.CM_AUTH_STATUS
  ON dbo.ASS_MASTER.AUTH_STATUS = dbo.CM_AUTH_STATUS.AUTH_STATUS
LEFT OUTER JOIN dbo.CM_DEPARTMENT
  ON dbo.ASS_MASTER.DEPT_ID = dbo.CM_DEPARTMENT.DEP_ID
LEFT OUTER JOIN dbo.CM_DIVISION
  ON dbo.ASS_MASTER.DIVISION_ID = dbo.CM_DIVISION.DIV_ID
LEFT OUTER JOIN dbo.TR_PO_MASTER
  ON dbo.TR_PO_MASTER.PO_ID = dbo.ASS_MASTER.PO_ID
LEFT OUTER JOIN dbo.ASS_TYPE
  ON dbo.ASS_TYPE.TYPE_ID = dbo.ASS_MASTER.TYPE_ID
LEFT OUTER JOIN dbo.ASS_AMORT_STATUS
  ON dbo.ASS_AMORT_STATUS.STATUS_CODE = dbo.ASS_MASTER.AMORT_STATUS
LEFT OUTER JOIN dbo.ASS_STATUS
  ON dbo.ASS_STATUS.STATUS_ID = dbo.ASS_MASTER.ASS_STATUS
LEFT OUTER JOIN dbo.ASS_MASTER RA
  ON RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
LEFT OUTER JOIN dbo.ASS_PO
  ON dbo.ASS_MASTER.PO_ID = dbo.ASS_PO.ASSPO_ID
LEFT OUTER JOIN dbo.CM_BRANCH BRCR
  ON dbo.ASS_MASTER.BRANCH_CREATE = BRCR.BRANCH_ID
LEFT JOIN CM_ALLCODE OS
  ON ASS_MASTER.OS = OS.CDVAL
    AND OS.CDNAME = 'OS_LICENSE'
    AND OS.CDTYPE = 'STATUS'
LEFT JOIN CM_ALLCODE LICENSE
  ON ASS_MASTER.MO_LICENSE = LICENSE.CDVAL
    AND LICENSE.CDNAME = 'OS_LICENSE'
    AND LICENSE.CDTYPE = 'STATUS'
LEFT JOIN ASS_TRANSACTIONS AT
  ON AT.ASSET_ID = dbo.ASS_MASTER.ASSET_ID
    AND AT.TRN_TYPE = 'ADD_NEW'
LEFT JOIN ASS_ADDNEW AN
  ON AT.TRN_ID = AN.ADDNEW_ID
    AND AT.TRN_TYPE = 'ADD_NEW'
LEFT JOIN ASS_PO AP
  ON AN.ADDNEW_ID = AP.ADDNEW_ID

						 --dbo.ASS_GROUP RG ON RG.GROUP_ID = RA.GROUP_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID LEFT JOIN
						 --dbo.ASS_TYPE RT ON RG.TYPE_ID = RA.TYPE_ID AND RA.ASSET_ID = dbo.ASS_MASTER.REF_ASSET_ID
						-- LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = dbo.ASS_MASTER.BRANCH_ID