dbo.vw_ms_molts_invoce

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create view dbo.vw_ms_molts_invoce
---XencX---with encryption
as 
 
 SELECT        dt_ms_molts_invoice.ms_molts_invoice_id
, cast( case when vw_ms_header_bc_in.ID_HEADER is null then 0 else 1 end   as bit ) as used 
, dt_ms_molts_invoice.invoice_no
, dt_ms_molts_invoice.invoice_date
, dt_ms_molts_invoice.etd
, dt_ms_molts_invoice.eta 
 ,isnull( vw_ms_molts_invoce_item_count.item_count,0) as item_count
 ,isnull( vw_ms_molts_invoce_item_count.unmap,0) as item_unmap  
, isnull(vw_ms_header_bc_in.KODE_DOKUMEN_PABEAN,'') as KODE_DOKUMEN_PABEAN
, isnull('BC ' + vw_ms_header_bc_in.KODE_DOKUMEN_PABEAN,'') BC_TYPE
, isnull(vw_ms_header_bc_in.NOMOR_AJU,'') as CAR
, isnull(vw_ms_header_bc_in.NOMOR_DAFTAR,'') as BC_DATE
, isnull(dbo.date_to_str(vw_ms_header_bc_in.TANGGAL_DAFTAR),'') as BC_NO
, isnull(vw_ms_header_bc_in.status_tpb_in_name,'') as STATUS_MODULE
, dt_ms_molts_invoice.shipping_mode
, dt_ms_molts_invoice.shipper
, dt_ms_molts_invoice.forwarder
, dt_ms_molts_invoice.type_term
, dt_ms_molts_invoice.valuta_code
, dt_ms_molts_invoice.vessel  
, dt_ms_file.file_name
, dt_ms_molts_invoice.create_date
, users_create.username as create_by
, case when cast( case when vw_ms_header_bc_in.ID_HEADER is null then 0 else 1 end   as bit ) =1 then 1
 when  dt_ms_supplier_customer.ms_supplier_customer_id is null then 2 
 when  isnull( vw_ms_molts_invoce_item_count.unmap,0)>0 then 3
 else
0 end as r_s
, 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(dt_ms_supplier_customer.skep_no,'') as skep_no
, dt_ms_supplier_customer.skep_date
, cast(case when dt_ms_supplier_customer.ms_supplier_customer_id is null then 0 else 1 end as bit) as mapped_supplier
, case when dt_ms_supplier_customer.ms_supplier_customer_id is not null then dt_ms_molts_invoice.mapping_date else null end as mapping_date
, case when dt_ms_supplier_customer.ms_supplier_customer_id is not null then users.username else null end as mapping_by 
,dt_ms_molts_invoice.mapping_supplier_customer_id
,dt_ms_molts_invoice.ms_file_id 
,dt_ms_file.file_name as source_file
 FROM            dt_ms_molts_invoice INNER JOIN
                         users AS users_create ON dt_ms_molts_invoice.create_by = users_create.id INNER JOIN
                         dt_ms_file ON dt_ms_molts_invoice.ms_file_id = dt_ms_file.ms_file_id LEFT OUTER JOIN
                         users ON dt_ms_molts_invoice.mapping_by = users.id LEFT OUTER JOIN
                         dt_ms_supplier_customer ON dt_ms_molts_invoice.mapping_supplier_customer_id = dt_ms_supplier_customer.ms_supplier_customer_id LEFT OUTER JOIN
                         vw_ms_molts_invoce_item_count ON dt_ms_molts_invoice.ms_molts_invoice_id = vw_ms_molts_invoce_item_count.ms_molts_invoice_id LEFT OUTER JOIN
                         vw_ms_header_bc_in ON dt_ms_molts_invoice.source_out_id = vw_ms_header_bc_in.ID_HEADER



GO