dbo.vw_ms_memo_header

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create view dbo.vw_ms_memo_header
---XencX---with encryption
as   

SELECT        
dt_ms_memo_header.ms_memo_header_id
, cast( case when vw_ms_header_bc_out.ID_HEADER is null then 0 else 1 end   as bit ) as used 
, enum_ms_memo_header_status.ms_memo_header_status_name
, dt_ms_memo_header.doc_no
, dt_ms_memo_header.doc_date
, dt_ms_memo_header.doc_info 
, dt_ms_memo_header.ms_memo_type_id
, enum_ms_memo_type.ms_memo_type_name
, cast(isnull(vw_ms_memo_header_count_detail.total_item,0) as int) as total_item
, cast(vw_ms_memo_header_count_detail.count_supply as int) as total_source
, cast(0 as int) as total_print 
, isnull(vw_ms_header_bc_out.KODE_DOKUMEN_PABEAN,'') as KODE_DOKUMEN_PABEAN
, isnull( case when vw_ms_header_bc_out.KODE_DOKUMEN_PABEAN ='PEMUSNAHAN' then '' else  'BC '  end  + vw_ms_header_bc_out.KODE_DOKUMEN_PABEAN,'') BC_TYPE
, isnull(case when vw_ms_header_bc_out.KODE_DOKUMEN_PABEAN ='PEMUSNAHAN' then '' else vw_ms_header_bc_out.NOMOR_AJU end,'') as CAR
, isnull(vw_ms_header_bc_out.NOMOR_DAFTAR,'') as BC_NO
, isnull(dbo.date_to_str(vw_ms_header_bc_out.TANGGAL_DAFTAR),'') as BC_DATE
, isnull(vw_ms_header_bc_out.status_tpb_out_name,'') as STATUS_MODULE 
, dt_ms_memo_header_h_status.status_info 
, dt_ms_memo_header_h_status.create_date AS status_date
, users_status.username AS status_by
, dt_ms_memo_header.create_date
, users_create.username AS create_by
, dt_ms_memo_header.create_by as create_by_id
, dt_ms_memo_header.modify_date
, users_modify.username AS modify_by 
, dt_ms_memo_header.modify_by as modify_by_id
, dt_ms_memo_header_h_status.ms_memo_header_status_id
, dt_ms_memo_header.ms_memo_header_h_status_id 
,isnull( dt_ms_supplier_customer.ms_supplier_customer_id,-1) as ms_supplier_customer_id
, isnull(dt_ms_supplier_customer.supplier_customer_code,'') as supplier_customer_code
, isnull(dt_ms_supplier_customer.supplier_customer_name,'') as supplier_customer_name
, isnull(dt_ms_supplier_customer.supplier_customer_address,'') as supplier_customer_address
, isnull(dt_ms_supplier_customer.supplier_customer_country,'') as supplier_customer_country
, isnull(tblNegara.UrEdi,'') as supplier_customer_country_name
, dt_ms_supplier_customer.id_nomor
, dt_ms_supplier_customer.skep_no
, dt_ms_memo_header.valuta_code
,dt_ms_memo_header.ndpbm 
, case when vw_ms_header_bc_out.ID_HEADER is not null then 2 
    when dt_ms_memo_header_h_status.ms_memo_header_status_id =1 then 1
    when dt_ms_memo_header_h_status.ms_memo_header_status_id=-1 then -1
  else 0
  end as r_s
, dt_ms_memo_header.ms_date_business_id
, isnull(dt_ms_date_business.ms_date_business,dt_ms_memo_header.doc_date  ) as ms_date_business
, dt_ms_memo_header.is_move_business_date
, dt_ms_memo_header.ms_date_business_id_from
, isnull(dt_ms_date_business_from.ms_date_business,dt_ms_memo_header.doc_date  )  AS ms_date_business_from
, dt_ms_memo_header.cancel_ms_cost_final_avg_id
, dt_ms_memo_header.approve_ms_cost_final_avg_id 
FROM            users AS users_status RIGHT OUTER JOIN
                         users AS users_create RIGHT OUTER JOIN
                         dt_ms_date_business RIGHT OUTER JOIN
                         dt_ms_memo_header INNER JOIN
                         dt_ms_memo_header_h_status ON dt_ms_memo_header.ms_memo_header_h_status_id = dt_ms_memo_header_h_status.ms_memo_header_h_status_id INNER JOIN
                         enum_ms_memo_header_status ON dt_ms_memo_header_h_status.ms_memo_header_status_id = enum_ms_memo_header_status.ms_memo_header_status_id INNER JOIN
                         enum_ms_memo_type ON dt_ms_memo_header.ms_memo_type_id = enum_ms_memo_type.ms_memo_type_id ON 
                         dt_ms_date_business.ms_date_business_id = dt_ms_memo_header.ms_date_business_id LEFT OUTER JOIN
                         dt_ms_date_business AS dt_ms_date_business_from ON dt_ms_memo_header.ms_date_business_id_from = dt_ms_date_business_from.ms_date_business_id LEFT OUTER JOIN
                         vw_ms_header_bc_out ON dt_ms_memo_header.source_out_id = vw_ms_header_bc_out.ID_HEADER LEFT OUTER JOIN
                         tblNegara RIGHT OUTER JOIN
                         dt_ms_supplier_customer ON tblNegara.KdEdi = dt_ms_supplier_customer.supplier_customer_country ON 
                         dt_ms_memo_header.ms_supplier_customer_id = dt_ms_supplier_customer.ms_supplier_customer_id LEFT OUTER JOIN
                         vw_ms_memo_header_count_detail ON dt_ms_memo_header.ms_memo_header_id = vw_ms_memo_header_count_detail.ms_memo_header_id LEFT OUTER JOIN
                         users AS users_modify ON dt_ms_memo_header.modify_by = users_modify.id ON users_create.id = dt_ms_memo_header.create_by ON users_status.id = dt_ms_memo_header_h_status.create_by
             
GO