AMS - VB

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