Description
Properties
| Name | Value |
|---|---|
| Collation | SQL_Latin1_General_CP1_CI_AS |
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Schema Bound | False |
| Created | 7/10/2018 12:41:46 |
| Last Modified | 7/10/2018 12:41:46 |
Columns
| Key | Name | Description |
|---|---|---|
| ID_HEADER | ||
| status_tpb_out_id | ||
| status_tpb_out_name | ||
| mode_source_id | ||
| type_source_id | ||
| mode_source_name | ||
| is_get | ||
| KODE_DOKUMEN_PABEAN | ||
| NOMOR_AJU | ||
| PJT | ||
| TANGGAL_DAFTAR | ||
| NOMOR_DAFTAR | ||
| KODE_STATUS | ||
| URAIAN_STATUS | ||
| KODE_TUJUAN_IN_OUT | ||
| URAINAN_TUJUAN_IN_OUT | ||
| MODULE_ID_PENGIRIM | ||
| MODULE_NAMA_PENGIRIM | ||
| MODULE_KODE_ID_PENGIRIM | ||
| MODULE_ALAMAT_PENGIRIM | ||
| MODULE_KODE_NEGARA_PENGIRIM | ||
| MODULE_NAMA_NEGARA_PENGIRIM | ||
| mapping_supplier_id | ||
| id_type | ||
| INTERNAL_ID_PENGIRIM | ||
| INTERNAL_KODE_PENGIRIM | ||
| INTERNAL_NAMA_PENGIRIM | ||
| INTERNAL_ALAMAT_PENGIRIM | ||
| INTERNAL_KODE_NEGARA_PENGIRIM | ||
| INTERNAL_NAMA_NEGARA_PENGIRIM | ||
| api_type_id | ||
| api_no | ||
| skep_no | ||
| skep_date | ||
| KODE_VALUTA | ||
| NDPBM | ||
| KODE_HARGA | ||
| HARGA_TOTAL | ||
| KODE_ASURANSI | ||
| ASURANSI | ||
| BIAYA_TAMBAHAN | ||
| DISKON | ||
| FREIGHT | ||
| BRUTO | ||
| NETTO | ||
| JUMLAH_BARANG | ||
| JUMLAH_KEMASAN | ||
| JUMLAH_KONTAINER | ||
| KODE_KANTOR_BONGKAR | ||
| KODE_KANTOR_BONGKAR_NAME | ||
| KODE_PEL_BONGKAR | ||
| KODE_PEL_BONGKAR_NAME | ||
| surat_jalan_no | ||
| surat_jalan_date | ||
| WAKTU_BONGKAR | ||
| WAKTU_STUFFING | ||
| status_info | ||
| status_date | ||
| status_by | ||
| create_date | ||
| create_by | ||
| modify_date | ||
| modify_by | ||
| ID_HEADER_ORI | ||
| ms_date_business_id | ||
| ms_date_business_id_from | ||
| ms_date_business | ||
| ms_date_business_from | ||
| is_move_business_date | ||
| cancel_ms_cost_final_avg_id | ||
| approve_ms_cost_final_avg_id |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create view dbo.vw_ms_header_bc_out
---XencX---with encryption
as
SELECT
dt_ms_tpb_out_header.ID_HEADER
, dt_ms_tpb_out_header_h_status.status_tpb_out_id
, enum_ms_status_tpb_out.status_tpb_out_name
, dt_ms_tpb_out_header.mode_source as mode_source_id
, dt_ms_tpb_out_header.type_source_id as type_source_id
, case when dt_ms_tpb_out_header.mode_source=3 then
'MEMO ' + enum_ms_memo_type.ms_memo_type_name
else
enum_ms_mode_source_tpb_out_header.mode_source_name
end as mode_source_name
, isnull(dt_ms_tpb_out_header.is_get ,0) as is_get
, dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN
, dt_ms_tpb_out_header.NOMOR_AJU
, dt_ms_tpb_out_header.PJT
, dt_ms_tpb_out_header.TANGGAL_DAFTAR
, dt_ms_tpb_out_header.NOMOR_DAFTAR
, dt_ms_tpb_out_header.KODE_STATUS
, enum_ms_status_ceisa.URAIAN_STATUS
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='262' then isnull(dt_ms_tpb_out_header.KODE_TUJUAN_PEMASUKAN,'00') else isnull(dt_ms_tpb_out_header.KODE_TUJUAN_PENGIRIMAN ,'00') end as KODE_TUJUAN_IN_OUT
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='262' then isnull(ENUM_TERIMA.URAIAN_TUJUAN,'') else isnull(ENUM_KIRIM.URAIAN_TUJUAN,'') end as URAINAN_TUJUAN_IN_OUT
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.ID_PEMASOK else dt_ms_tpb_out_header.ID_PENGIRIM end as MODULE_ID_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.NAMA_PEMASOK else dt_ms_tpb_out_header.NAMA_PENGIRIM end as MODULE_NAMA_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.KODE_ID_PEMASOK else dt_ms_tpb_out_header.KODE_ID_PENGIRIM end as MODULE_KODE_ID_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.ALAMAT_PEMASOK else dt_ms_tpb_out_header.ALAMAT_PENGIRIM end as MODULE_ALAMAT_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.KODE_NEGARA_PEMASOK else dt_ms_tpb_out_header.KODE_NEGARA_PENGIRIM end as MODULE_KODE_NEGARA_PENGIRIM
, isnull(tblNegara.UrEdi, '') as MODULE_NAMA_NEGARA_PENGIRIM
, isnull(dt_ms_tpb_out_header.mapping_supplier_id ,-1) as mapping_supplier_id
, dt_ms_supplier_customer.id_type
, dt_ms_supplier_customer.id_nomor as INTERNAL_ID_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_code as INTERNAL_KODE_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_name as INTERNAL_NAMA_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_address as INTERNAL_ALAMAT_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_country as INTERNAL_KODE_NEGARA_PENGIRIM
, isnull(tblNegara_int.UrEdi, '') as INTERNAL_NAMA_NEGARA_PENGIRIM
, dt_ms_supplier_customer.api_type_id
, dt_ms_supplier_customer.api_no
, dt_ms_supplier_customer.skep_no
, dt_ms_supplier_customer.skep_date
, dt_ms_tpb_out_header.KODE_VALUTA
, dt_ms_tpb_out_header.NDPBM
, dt_ms_tpb_out_header.KODE_HARGA
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.CIF else dt_ms_tpb_out_header.HARGA_PENYERAHAN end as HARGA_TOTAL
, dt_ms_tpb_out_header.KODE_ASURANSI
, dt_ms_tpb_out_header.ASURANSI
, dt_ms_tpb_out_header.BIAYA_TAMBAHAN
, dt_ms_tpb_out_header.DISKON
, dt_ms_tpb_out_header.FREIGHT
, dt_ms_tpb_out_header.BRUTO
, dt_ms_tpb_out_header.NETTO
, dt_ms_tpb_out_header.JUMLAH_BARANG
, dt_ms_tpb_out_header.JUMLAH_KEMASAN
, dt_ms_tpb_out_header.JUMLAH_KONTAINER
, dt_ms_tpb_out_header.KODE_KANTOR_BONGKAR
, tblKpbc.UrKdKpbc AS KODE_KANTOR_BONGKAR_NAME
, dt_ms_tpb_out_header.KODE_PEL_BONGKAR
, tblPelDN.UREDI AS KODE_PEL_BONGKAR_NAME
, dt_ms_tpb_out_header.surat_jalan_no
, dt_ms_tpb_out_header.surat_jalan_date
, dt_ms_tpb_out_header.WAKTU_BONGKAR
, dt_ms_tpb_out_header.WAKTU_STUFFING
, dt_ms_tpb_out_header_h_status.status_info
, dt_ms_tpb_out_header_h_status.create_date as status_date
, users_status.username as status_by
, dt_ms_tpb_out_header.create_date
, users_modify.username AS create_by
, dt_ms_tpb_out_header.modify_date, users_create.username AS modify_by
, dt_ms_tpb_out_header.ID_HEADER_ORI
, dt_ms_tpb_out_header.ms_date_business_id
, dt_ms_tpb_out_header.ms_date_business_id_from
, isnull(dt_ms_date_business.ms_date_business,dt_ms_tpb_out_header.surat_jalan_date ) as ms_date_business
, isnull(dt_ms_date_business_from.ms_date_business,dt_ms_tpb_out_header.surat_jalan_date ) AS ms_date_business_from
, isnull(dt_ms_tpb_out_header.is_move_business_date,0) as is_move_business_date
, isnull(dt_ms_tpb_out_header.cancel_ms_cost_final_avg_id,-1) as cancel_ms_cost_final_avg_id
, isnull(dt_ms_tpb_out_header.approve_ms_cost_final_avg_id,-1) as approve_ms_cost_final_avg_id
FROM tblNegara RIGHT OUTER JOIN
dt_ms_tpb_out_header INNER JOIN
dt_ms_tpb_out_header_h_status ON dt_ms_tpb_out_header.dt_ms_tpb_out_header_h_status_id = dt_ms_tpb_out_header_h_status.dt_ms_tpb_out_header_h_status_id INNER JOIN
enum_ms_mode_source_tpb_out_header ON dt_ms_tpb_out_header.mode_source = enum_ms_mode_source_tpb_out_header.mode_source LEFT OUTER JOIN
dt_ms_date_business AS dt_ms_date_business_from ON dt_ms_tpb_out_header.ms_date_business_id_from = dt_ms_date_business_from.ms_date_business_id LEFT OUTER JOIN
dt_ms_date_business ON dt_ms_tpb_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id LEFT OUTER JOIN
enum_ms_memo_type ON dt_ms_tpb_out_header.type_source_id = enum_ms_memo_type.ms_memo_type_id LEFT OUTER JOIN
enum_ms_status_tpb_out ON dt_ms_tpb_out_header_h_status.status_tpb_out_id = enum_ms_status_tpb_out.status_tpb_out_id ON tblNegara.KdEdi = dt_ms_tpb_out_header.KODE_NEGARA_PEMASOK LEFT OUTER JOIN
enum_ms_status_ceisa ON dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = enum_ms_status_ceisa.KODE_DOKUMEN AND
dt_ms_tpb_out_header.KODE_STATUS = enum_ms_status_ceisa.KODE_STATUS LEFT OUTER JOIN
tblNegara AS tblNegara_int RIGHT OUTER JOIN
dt_ms_supplier_customer ON tblNegara_int.KdEdi = dt_ms_supplier_customer.supplier_customer_country ON
dt_ms_tpb_out_header.mapping_supplier_id = dt_ms_supplier_customer.ms_supplier_customer_id LEFT OUTER JOIN
tblPelDN ON dt_ms_tpb_out_header.KODE_PEL_BONGKAR = tblPelDN.KDEDI LEFT OUTER JOIN
tblKpbc ON dt_ms_tpb_out_header.KODE_KANTOR_BONGKAR = tblKpbc.KdKpbc LEFT OUTER JOIN
enum_ms_tpb_tujuan AS ENUM_TERIMA ON dt_ms_tpb_out_header.KODE_TUJUAN_PEMASUKAN = ENUM_TERIMA.KODE_TUJUAN AND
dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = ENUM_TERIMA.KODE_DOKUMEN LEFT OUTER JOIN
enum_ms_tpb_tujuan AS ENUM_KIRIM ON dt_ms_tpb_out_header.KODE_TUJUAN_PENGIRIMAN = ENUM_KIRIM.KODE_TUJUAN AND
dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = ENUM_KIRIM.KODE_DOKUMEN LEFT OUTER JOIN
users AS users_create ON dt_ms_tpb_out_header.create_by = users_create.id LEFT OUTER JOIN
users AS users_modify ON dt_ms_tpb_out_header.modify_by = users_modify.id LEFT OUTER JOIN
users AS users_status ON dt_ms_tpb_out_header_h_status.create_by = users_status.id
GO
GO
create view dbo.vw_ms_header_bc_out
---XencX---with encryption
as
SELECT
dt_ms_tpb_out_header.ID_HEADER
, dt_ms_tpb_out_header_h_status.status_tpb_out_id
, enum_ms_status_tpb_out.status_tpb_out_name
, dt_ms_tpb_out_header.mode_source as mode_source_id
, dt_ms_tpb_out_header.type_source_id as type_source_id
, case when dt_ms_tpb_out_header.mode_source=3 then
'MEMO ' + enum_ms_memo_type.ms_memo_type_name
else
enum_ms_mode_source_tpb_out_header.mode_source_name
end as mode_source_name
, isnull(dt_ms_tpb_out_header.is_get ,0) as is_get
, dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN
, dt_ms_tpb_out_header.NOMOR_AJU
, dt_ms_tpb_out_header.PJT
, dt_ms_tpb_out_header.TANGGAL_DAFTAR
, dt_ms_tpb_out_header.NOMOR_DAFTAR
, dt_ms_tpb_out_header.KODE_STATUS
, enum_ms_status_ceisa.URAIAN_STATUS
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='262' then isnull(dt_ms_tpb_out_header.KODE_TUJUAN_PEMASUKAN,'00') else isnull(dt_ms_tpb_out_header.KODE_TUJUAN_PENGIRIMAN ,'00') end as KODE_TUJUAN_IN_OUT
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='262' then isnull(ENUM_TERIMA.URAIAN_TUJUAN,'') else isnull(ENUM_KIRIM.URAIAN_TUJUAN,'') end as URAINAN_TUJUAN_IN_OUT
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.ID_PEMASOK else dt_ms_tpb_out_header.ID_PENGIRIM end as MODULE_ID_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.NAMA_PEMASOK else dt_ms_tpb_out_header.NAMA_PENGIRIM end as MODULE_NAMA_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.KODE_ID_PEMASOK else dt_ms_tpb_out_header.KODE_ID_PENGIRIM end as MODULE_KODE_ID_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.ALAMAT_PEMASOK else dt_ms_tpb_out_header.ALAMAT_PENGIRIM end as MODULE_ALAMAT_PENGIRIM
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.KODE_NEGARA_PEMASOK else dt_ms_tpb_out_header.KODE_NEGARA_PENGIRIM end as MODULE_KODE_NEGARA_PENGIRIM
, isnull(tblNegara.UrEdi, '') as MODULE_NAMA_NEGARA_PENGIRIM
, isnull(dt_ms_tpb_out_header.mapping_supplier_id ,-1) as mapping_supplier_id
, dt_ms_supplier_customer.id_type
, dt_ms_supplier_customer.id_nomor as INTERNAL_ID_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_code as INTERNAL_KODE_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_name as INTERNAL_NAMA_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_address as INTERNAL_ALAMAT_PENGIRIM
, dt_ms_supplier_customer.supplier_customer_country as INTERNAL_KODE_NEGARA_PENGIRIM
, isnull(tblNegara_int.UrEdi, '') as INTERNAL_NAMA_NEGARA_PENGIRIM
, dt_ms_supplier_customer.api_type_id
, dt_ms_supplier_customer.api_no
, dt_ms_supplier_customer.skep_no
, dt_ms_supplier_customer.skep_date
, dt_ms_tpb_out_header.KODE_VALUTA
, dt_ms_tpb_out_header.NDPBM
, dt_ms_tpb_out_header.KODE_HARGA
, case when dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN ='23' then dt_ms_tpb_out_header.CIF else dt_ms_tpb_out_header.HARGA_PENYERAHAN end as HARGA_TOTAL
, dt_ms_tpb_out_header.KODE_ASURANSI
, dt_ms_tpb_out_header.ASURANSI
, dt_ms_tpb_out_header.BIAYA_TAMBAHAN
, dt_ms_tpb_out_header.DISKON
, dt_ms_tpb_out_header.FREIGHT
, dt_ms_tpb_out_header.BRUTO
, dt_ms_tpb_out_header.NETTO
, dt_ms_tpb_out_header.JUMLAH_BARANG
, dt_ms_tpb_out_header.JUMLAH_KEMASAN
, dt_ms_tpb_out_header.JUMLAH_KONTAINER
, dt_ms_tpb_out_header.KODE_KANTOR_BONGKAR
, tblKpbc.UrKdKpbc AS KODE_KANTOR_BONGKAR_NAME
, dt_ms_tpb_out_header.KODE_PEL_BONGKAR
, tblPelDN.UREDI AS KODE_PEL_BONGKAR_NAME
, dt_ms_tpb_out_header.surat_jalan_no
, dt_ms_tpb_out_header.surat_jalan_date
, dt_ms_tpb_out_header.WAKTU_BONGKAR
, dt_ms_tpb_out_header.WAKTU_STUFFING
, dt_ms_tpb_out_header_h_status.status_info
, dt_ms_tpb_out_header_h_status.create_date as status_date
, users_status.username as status_by
, dt_ms_tpb_out_header.create_date
, users_modify.username AS create_by
, dt_ms_tpb_out_header.modify_date, users_create.username AS modify_by
, dt_ms_tpb_out_header.ID_HEADER_ORI
, dt_ms_tpb_out_header.ms_date_business_id
, dt_ms_tpb_out_header.ms_date_business_id_from
, isnull(dt_ms_date_business.ms_date_business,dt_ms_tpb_out_header.surat_jalan_date ) as ms_date_business
, isnull(dt_ms_date_business_from.ms_date_business,dt_ms_tpb_out_header.surat_jalan_date ) AS ms_date_business_from
, isnull(dt_ms_tpb_out_header.is_move_business_date,0) as is_move_business_date
, isnull(dt_ms_tpb_out_header.cancel_ms_cost_final_avg_id,-1) as cancel_ms_cost_final_avg_id
, isnull(dt_ms_tpb_out_header.approve_ms_cost_final_avg_id,-1) as approve_ms_cost_final_avg_id
FROM tblNegara RIGHT OUTER JOIN
dt_ms_tpb_out_header INNER JOIN
dt_ms_tpb_out_header_h_status ON dt_ms_tpb_out_header.dt_ms_tpb_out_header_h_status_id = dt_ms_tpb_out_header_h_status.dt_ms_tpb_out_header_h_status_id INNER JOIN
enum_ms_mode_source_tpb_out_header ON dt_ms_tpb_out_header.mode_source = enum_ms_mode_source_tpb_out_header.mode_source LEFT OUTER JOIN
dt_ms_date_business AS dt_ms_date_business_from ON dt_ms_tpb_out_header.ms_date_business_id_from = dt_ms_date_business_from.ms_date_business_id LEFT OUTER JOIN
dt_ms_date_business ON dt_ms_tpb_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id LEFT OUTER JOIN
enum_ms_memo_type ON dt_ms_tpb_out_header.type_source_id = enum_ms_memo_type.ms_memo_type_id LEFT OUTER JOIN
enum_ms_status_tpb_out ON dt_ms_tpb_out_header_h_status.status_tpb_out_id = enum_ms_status_tpb_out.status_tpb_out_id ON tblNegara.KdEdi = dt_ms_tpb_out_header.KODE_NEGARA_PEMASOK LEFT OUTER JOIN
enum_ms_status_ceisa ON dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = enum_ms_status_ceisa.KODE_DOKUMEN AND
dt_ms_tpb_out_header.KODE_STATUS = enum_ms_status_ceisa.KODE_STATUS LEFT OUTER JOIN
tblNegara AS tblNegara_int RIGHT OUTER JOIN
dt_ms_supplier_customer ON tblNegara_int.KdEdi = dt_ms_supplier_customer.supplier_customer_country ON
dt_ms_tpb_out_header.mapping_supplier_id = dt_ms_supplier_customer.ms_supplier_customer_id LEFT OUTER JOIN
tblPelDN ON dt_ms_tpb_out_header.KODE_PEL_BONGKAR = tblPelDN.KDEDI LEFT OUTER JOIN
tblKpbc ON dt_ms_tpb_out_header.KODE_KANTOR_BONGKAR = tblKpbc.KdKpbc LEFT OUTER JOIN
enum_ms_tpb_tujuan AS ENUM_TERIMA ON dt_ms_tpb_out_header.KODE_TUJUAN_PEMASUKAN = ENUM_TERIMA.KODE_TUJUAN AND
dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = ENUM_TERIMA.KODE_DOKUMEN LEFT OUTER JOIN
enum_ms_tpb_tujuan AS ENUM_KIRIM ON dt_ms_tpb_out_header.KODE_TUJUAN_PENGIRIMAN = ENUM_KIRIM.KODE_TUJUAN AND
dt_ms_tpb_out_header.KODE_DOKUMEN_PABEAN = ENUM_KIRIM.KODE_DOKUMEN LEFT OUTER JOIN
users AS users_create ON dt_ms_tpb_out_header.create_by = users_create.id LEFT OUTER JOIN
users AS users_modify ON dt_ms_tpb_out_header.modify_by = users_modify.id LEFT OUTER JOIN
users AS users_status ON dt_ms_tpb_out_header_h_status.create_by = users_status.id
GO