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 | 9/11/2017 10:57:24 |
| Last Modified | 9/11/2017 10:57:24 |
Columns
| Key | Name | Description |
|---|---|---|
| ms_memo_detail_id | ||
| ms_in_item_detail_id | ||
| KODE_JENIS_DOK_ASAL | ||
| JENIS_DOK | ||
| NOMOR_DAFTAR_DOK_ASAL | ||
| TANGGAL_DAFTAR_DOK_ASAL | ||
| KODE_KANTOR | ||
| NOMOR_AJU_DOK_ASAL | ||
| SERI_BARANG_DOK_ASAL | ||
| KODE_VALUTA | ||
| NDPBM | ||
| JUMLAH | ||
| SATUAN_HARGA_RUPIAH | ||
| SATUAN_HARGA_USD | ||
| TOTAL_HARGA_RUPIAH | ||
| TOTAL_HARGA_USD | ||
| ms_type_id | ||
| ms_item_custom_no | ||
| ms_item_base_no | ||
| part_no | ||
| part_name | ||
| uom | ||
| ms_item_id | ||
| ms_item_base_name | ||
| ms_item_custom_name | ||
| nomor_hs | ||
| tarif_bm | ||
| tarif_cukai | ||
| tarif_pph | ||
| tarif_ppn | ||
| tarif_ppnbm | ||
| netto_per_unit |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create view dbo.vw_ms_memo_detail_suplly_ori_document
---XencX---with encryption
as
SELECT
ms_memo_detail_id
, ms_in_item_detail_id
, KODE_JENIS_DOK_ASAL
, JENIS_DOK
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_VALUTA
, NDPBM
, sum(JUMLAH) as JUMLAH
, SATUAN_HARGA_RUPIAH
, SATUAN_HARGA_USD
, sum(TOTAL_HARGA_RUPIAH) as TOTAL_HARGA_RUPIAH
, sum(TOTAL_HARGA_USD) as TOTAL_HARGA_USD
, ms_type_id
, ms_item_custom_no
, ms_item_base_no
, part_no
, part_name
, uom
, ms_item_id
, ms_item_base_name
, ms_item_custom_name
, nomor_hs
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
,sum(netto_per_unit) as netto_per_unit
FROM(
SELECT
dt_ms_memo_detail_supply.ms_memo_detail_id
, dt_ms_memo_detail_supply.supply_id_source as ms_in_item_detail_id
, vw_ms_item_in_detail_for_bahan_baku.KODE_JENIS_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.JENIS_DOK
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.TANGGAL_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_KANTOR
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_AJU_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.SERI_BARANG_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_VALUTA
, vw_ms_item_in_detail_for_bahan_baku.NDPBM
, dt_ms_memo_detail_supply.quantity as JUMLAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_RUPIAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_USD
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_RUPIAH
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_USD
, vw_ms_item_in_detail_for_bahan_baku.ms_type_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_no
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_no
, vw_ms_item_in_detail_for_bahan_baku.part_no
, vw_ms_item_in_detail_for_bahan_baku.part_name
, vw_ms_item_in_detail_for_bahan_baku.uom
, vw_ms_item_in_detail_for_bahan_baku.ms_item_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_name
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_name
, vw_ms_item_in_detail_for_bahan_baku.nomor_hs
, vw_ms_item_in_detail_for_bahan_baku.tarif_bm
, vw_ms_item_in_detail_for_bahan_baku.tarif_cukai
, vw_ms_item_in_detail_for_bahan_baku.tarif_pph
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppn
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppnbm
, vw_ms_item_in_detail_for_bahan_baku.netto_per_unit
FROM dt_ms_memo_detail_supply INNER JOIN
vw_ms_item_in_detail_for_bahan_baku ON dt_ms_memo_detail_supply.supply_id_source = vw_ms_item_in_detail_for_bahan_baku.ms_in_item_detail_id
WHERE (dt_ms_memo_detail_supply.supply_mode_source = 0)
union ALL
SELECT
dt_ms_memo_detail_supply.ms_memo_detail_id
, dt_ms_memo_detail_supply.supply_id_source as ms_in_item_detail_id
, vw_ms_item_in_detail_for_bahan_baku.KODE_JENIS_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.JENIS_DOK
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.TANGGAL_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_KANTOR
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_AJU_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.SERI_BARANG_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_VALUTA
, vw_ms_item_in_detail_for_bahan_baku.NDPBM
, dt_ms_memo_detail_supply.quantity as JUMLAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_RUPIAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_USD
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_RUPIAH
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_USD
, vw_ms_item_in_detail_for_bahan_baku.ms_type_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_no
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_no
, vw_ms_item_in_detail_for_bahan_baku.part_no
, vw_ms_item_in_detail_for_bahan_baku.part_name
, vw_ms_item_in_detail_for_bahan_baku.uom
, vw_ms_item_in_detail_for_bahan_baku.ms_item_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_name
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_name
, vw_ms_item_in_detail_for_bahan_baku.nomor_hs
, vw_ms_item_in_detail_for_bahan_baku.tarif_bm
, vw_ms_item_in_detail_for_bahan_baku.tarif_cukai
, vw_ms_item_in_detail_for_bahan_baku.tarif_pph
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppn
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppnbm
,vw_ms_item_in_detail_for_bahan_baku.netto_per_unit
FROM dt_ms_item_out_detail INNER JOIN
dt_ms_memo_detail_supply ON dt_ms_item_out_detail.ms_item_out_detail_id = dt_ms_memo_detail_supply.supply_id_source INNER JOIN
vw_ms_item_in_detail_for_bahan_baku ON dt_ms_item_out_detail.ms_in_item_detail_id = vw_ms_item_in_detail_for_bahan_baku.ms_in_item_detail_id
WHERE (dt_ms_memo_detail_supply.supply_mode_source = 1) AND (dt_ms_item_out_detail.mode_source = 0)
)xdata
group by
ms_memo_detail_id
, ms_in_item_detail_id
, KODE_JENIS_DOK_ASAL
, JENIS_DOK
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_VALUTA
, NDPBM
, SATUAN_HARGA_RUPIAH
, SATUAN_HARGA_USD
, ms_type_id
, ms_item_custom_no
, ms_item_base_no
, part_no
, part_name
, uom
, ms_item_id
, ms_item_base_name
, ms_item_custom_name
, nomor_hs
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
GO
GO
create view dbo.vw_ms_memo_detail_suplly_ori_document
---XencX---with encryption
as
SELECT
ms_memo_detail_id
, ms_in_item_detail_id
, KODE_JENIS_DOK_ASAL
, JENIS_DOK
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_VALUTA
, NDPBM
, sum(JUMLAH) as JUMLAH
, SATUAN_HARGA_RUPIAH
, SATUAN_HARGA_USD
, sum(TOTAL_HARGA_RUPIAH) as TOTAL_HARGA_RUPIAH
, sum(TOTAL_HARGA_USD) as TOTAL_HARGA_USD
, ms_type_id
, ms_item_custom_no
, ms_item_base_no
, part_no
, part_name
, uom
, ms_item_id
, ms_item_base_name
, ms_item_custom_name
, nomor_hs
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
,sum(netto_per_unit) as netto_per_unit
FROM(
SELECT
dt_ms_memo_detail_supply.ms_memo_detail_id
, dt_ms_memo_detail_supply.supply_id_source as ms_in_item_detail_id
, vw_ms_item_in_detail_for_bahan_baku.KODE_JENIS_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.JENIS_DOK
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.TANGGAL_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_KANTOR
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_AJU_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.SERI_BARANG_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_VALUTA
, vw_ms_item_in_detail_for_bahan_baku.NDPBM
, dt_ms_memo_detail_supply.quantity as JUMLAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_RUPIAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_USD
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_RUPIAH
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_USD
, vw_ms_item_in_detail_for_bahan_baku.ms_type_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_no
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_no
, vw_ms_item_in_detail_for_bahan_baku.part_no
, vw_ms_item_in_detail_for_bahan_baku.part_name
, vw_ms_item_in_detail_for_bahan_baku.uom
, vw_ms_item_in_detail_for_bahan_baku.ms_item_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_name
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_name
, vw_ms_item_in_detail_for_bahan_baku.nomor_hs
, vw_ms_item_in_detail_for_bahan_baku.tarif_bm
, vw_ms_item_in_detail_for_bahan_baku.tarif_cukai
, vw_ms_item_in_detail_for_bahan_baku.tarif_pph
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppn
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppnbm
, vw_ms_item_in_detail_for_bahan_baku.netto_per_unit
FROM dt_ms_memo_detail_supply INNER JOIN
vw_ms_item_in_detail_for_bahan_baku ON dt_ms_memo_detail_supply.supply_id_source = vw_ms_item_in_detail_for_bahan_baku.ms_in_item_detail_id
WHERE (dt_ms_memo_detail_supply.supply_mode_source = 0)
union ALL
SELECT
dt_ms_memo_detail_supply.ms_memo_detail_id
, dt_ms_memo_detail_supply.supply_id_source as ms_in_item_detail_id
, vw_ms_item_in_detail_for_bahan_baku.KODE_JENIS_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.JENIS_DOK
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.TANGGAL_DAFTAR_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_KANTOR
, vw_ms_item_in_detail_for_bahan_baku.NOMOR_AJU_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.SERI_BARANG_DOK_ASAL
, vw_ms_item_in_detail_for_bahan_baku.KODE_VALUTA
, vw_ms_item_in_detail_for_bahan_baku.NDPBM
, dt_ms_memo_detail_supply.quantity as JUMLAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_RUPIAH
, cast(vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH as decimal(38,4)) as SATUAN_HARGA_USD
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_RUPIAH/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_RUPIAH
, cast((vw_ms_item_in_detail_for_bahan_baku.TOTAL_HARGA_USD/vw_ms_item_in_detail_for_bahan_baku.JUMLAH) * dt_ms_memo_detail_supply.quantity as decimal(38,4)) as TOTAL_HARGA_USD
, vw_ms_item_in_detail_for_bahan_baku.ms_type_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_no
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_no
, vw_ms_item_in_detail_for_bahan_baku.part_no
, vw_ms_item_in_detail_for_bahan_baku.part_name
, vw_ms_item_in_detail_for_bahan_baku.uom
, vw_ms_item_in_detail_for_bahan_baku.ms_item_id
, vw_ms_item_in_detail_for_bahan_baku.ms_item_base_name
, vw_ms_item_in_detail_for_bahan_baku.ms_item_custom_name
, vw_ms_item_in_detail_for_bahan_baku.nomor_hs
, vw_ms_item_in_detail_for_bahan_baku.tarif_bm
, vw_ms_item_in_detail_for_bahan_baku.tarif_cukai
, vw_ms_item_in_detail_for_bahan_baku.tarif_pph
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppn
, vw_ms_item_in_detail_for_bahan_baku.tarif_ppnbm
,vw_ms_item_in_detail_for_bahan_baku.netto_per_unit
FROM dt_ms_item_out_detail INNER JOIN
dt_ms_memo_detail_supply ON dt_ms_item_out_detail.ms_item_out_detail_id = dt_ms_memo_detail_supply.supply_id_source INNER JOIN
vw_ms_item_in_detail_for_bahan_baku ON dt_ms_item_out_detail.ms_in_item_detail_id = vw_ms_item_in_detail_for_bahan_baku.ms_in_item_detail_id
WHERE (dt_ms_memo_detail_supply.supply_mode_source = 1) AND (dt_ms_item_out_detail.mode_source = 0)
)xdata
group by
ms_memo_detail_id
, ms_in_item_detail_id
, KODE_JENIS_DOK_ASAL
, JENIS_DOK
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_VALUTA
, NDPBM
, SATUAN_HARGA_RUPIAH
, SATUAN_HARGA_USD
, ms_type_id
, ms_item_custom_no
, ms_item_base_no
, part_no
, part_name
, uom
, ms_item_id
, ms_item_base_name
, ms_item_custom_name
, nomor_hs
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
GO