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:52:19 |
| Last Modified | 11/6/2020 19:34:59 |
Columns
| Key | Name | Description |
|---|---|---|
| ms_production_in_detail_id | ||
| ms_type_prosses_name | ||
| ms_car_line_no | ||
| ms_car_line_name | ||
| part_no | ||
| part_name | ||
| ms_item_id | ||
| ms_type_id | ||
| uom | ||
| quantity | ||
| quantity_sisa | ||
| qty_out | ||
| qty_plan | ||
| retur_out | ||
| memo_out | ||
| retur_plan | ||
| memo_plan | ||
| doc_no | ||
| doc_date | ||
| doc_info | ||
| ms_item_custom_no | ||
| ms_item_custom_name | ||
| ms_item_base_no | ||
| ms_item_base_name | ||
| consumable | ||
| status_id | ||
| status_name | ||
| ms_type_prosses_id | ||
| ms_car_line_out_header_id | ||
| cancel_ms_cost_final_avg_id | ||
| approve_ms_cost_final_avg_id | ||
| price_usd | ||
| price | ||
| ori_ms_in_item_detail_id | ||
| allow_to_production | ||
| mandatory_car_line | ||
| allow_to_scrap | ||
| allow_to_repair | ||
| allow_to_selling |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE view dbo.vw_ms_production_in_detail
---XencX---with encryption
as
SELECT
dt_ms_item_out_detail.ms_item_out_detail_id as ms_production_in_detail_id
, isnull(dt_ms_type_prosses.ms_type_prosses_name,'') as ms_type_prosses_name
, isnull(dt_ms_car_line_out_header.ms_car_line_no,'') as ms_car_line_no
, isnull(dt_ms_car_line.ms_car_line_name,'') as ms_car_line_name
, vw_ms_item.part_no
, vw_ms_item.part_name
, vw_ms_item.ms_item_id
, vw_ms_item.ms_type_id
, vw_ms_item.uom
, dt_ms_item_out_detail.quantity
,isnull(dt_ms_item_out_detail.quantity,0) -( isnull(vw_ms_production_in_sum_to_memo.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_retur.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_part.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_change_part.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_memo.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_retur.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_part.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_change_part.plan_qty,0)
) as quantity_sisa
,isnull(vw_ms_production_in_sum_to_retur.out_qty,0) + isnull(vw_ms_production_in_sum_to_memo.out_qty,0) as qty_out
,isnull(vw_ms_production_in_sum_to_retur.plan_qty,0) + isnull(vw_ms_production_in_sum_to_memo.plan_qty,0) as qty_plan
,isnull(vw_ms_production_in_sum_to_retur.out_qty,0) as retur_out
,isnull(vw_ms_production_in_sum_to_memo.out_qty,0) as memo_out
,isnull(vw_ms_production_in_sum_to_retur.plan_qty,0) as retur_plan
,isnull(vw_ms_production_in_sum_to_memo.plan_qty,0) as memo_plan
, dt_ms_car_line_out_header.doc_no
, dt_ms_car_line_out_header.doc_date
, dt_ms_car_line_out_header.doc_info
, vw_ms_item.ms_item_custom_no
, vw_ms_item.ms_item_custom_name
, isnull(vw_ms_item.ms_item_base_no,'') as ms_item_base_no
, isnull(vw_ms_item.ms_item_base_name,'') as ms_item_base_name
, vw_ms_item.consumable
, dt_ms_car_line_out_header_h_status.ms_car_line_out_header_status_id as status_id
, enum_ms_car_line_out_header_status.ms_car_line_out_header_status_name as status_name
, dt_ms_car_line_out_header.ms_type_prosses_id
, dt_ms_car_line_out_header.ms_car_line_out_header_id
, isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) as cancel_ms_cost_final_avg_id
, isnull(dt_ms_car_line_out_header.approve_ms_cost_final_avg_id,-1) as approve_ms_cost_final_avg_id
, case when isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) =-1 then
case when isnull(dt_ms_item_daily_in_move_avg.price_avg_move_usd,0)=0 or isnull(dt_ms_item_daily_in_move_avg.total_qty_avg_move,0)=0 then 0
else
dt_ms_item_daily_in_move_avg.price_avg_move_usd/dt_ms_item_daily_in_move_avg.total_qty_avg_move
end
else
case when isnull(dt_ms_item_list_price.end_price_usd,0)=0 or isnull(dt_ms_item_list_price.end_qty,0)=0 then 0
else dt_ms_item_list_price.end_price_usd / dt_ms_item_list_price.end_qty
end
end as price_usd
, case when isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) =-1 then
case when dt_ms_item_daily_in_move_avg.price_avg_move_idr=0 or dt_ms_item_daily_in_move_avg.total_qty_avg_move=0 then 0
else
dt_ms_item_daily_in_move_avg.price_avg_move_idr/dt_ms_item_daily_in_move_avg.total_qty_avg_move
end
else
case when dt_ms_item_list_price.end_price =0 or dt_ms_item_list_price.end_qty =0 then 0 else
dt_ms_item_list_price.end_price / dt_ms_item_list_price.end_qty
end
end as price
, dt_ms_item_in_detail.ori_ms_in_item_detail_id
, vw_ms_item.allow_to_production
, vw_ms_item.mandatory_car_line
, vw_ms_item.allow_to_scrap
, vw_ms_item.allow_to_repair
, vw_ms_item.allow_to_selling
FROM dt_ms_item_out_detail INNER JOIN
dt_ms_car_line_out_detail ON dt_ms_item_out_detail.id_source = dt_ms_car_line_out_detail.ms_car_line_out_detail_id INNER JOIN
dt_ms_car_line_out_header ON dt_ms_car_line_out_detail.ms_car_line_out_header_id = dt_ms_car_line_out_header.ms_car_line_out_header_id INNER JOIN
dt_ms_car_line_out_header_h_status ON dt_ms_car_line_out_header.ms_car_line_out_header_h_status_id = dt_ms_car_line_out_header_h_status.ms_car_line_out_header_h_status_id INNER JOIN
enum_ms_car_line_out_header_status ON dt_ms_car_line_out_header_h_status.ms_car_line_out_header_status_id = enum_ms_car_line_out_header_status.ms_car_line_out_header_status_id INNER JOIN
dt_ms_item_daily_out_move_avg ON dt_ms_car_line_out_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id INNER JOIN
dt_ms_item_daily_in_move_avg ON dt_ms_item_daily_out_move_avg.ms_item_in_move_avg_id = dt_ms_item_daily_in_move_avg.ms_item_in_move_avg_id INNER JOIN
dt_ms_item_in_detail ON dt_ms_item_out_detail.ms_in_item_detail_id = dt_ms_item_in_detail.ms_in_item_detail_id INNER JOIN
vw_ms_item_no_log as vw_ms_item ON dt_ms_car_line_out_detail.ms_item_id = vw_ms_item.ms_item_id LEFT OUTER JOIN
dt_ms_item_list_price ON dt_ms_item_daily_in_move_avg.ms_item_h_price_id = dt_ms_item_list_price.ms_item_h_price_id AND
dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id = dt_ms_item_list_price.ms_cost_final_avg_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_retur ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_retur.ms_production_in_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_memo ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_memo.ms_production_in_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_part ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_part.ms_item_out_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_change_part ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_change_part.ms_item_out_detail_id LEFT OUTER JOIN
dt_ms_car_line ON dt_ms_car_line_out_header.ms_car_line_no = dt_ms_car_line.ms_car_line_no LEFT OUTER JOIN
dt_ms_type_prosses ON dt_ms_car_line_out_header.ms_type_prosses_id = dt_ms_type_prosses.ms_type_prosses_id
WHERE (dt_ms_item_out_detail.mode_source = 0)
GO
GO
CREATE view dbo.vw_ms_production_in_detail
---XencX---with encryption
as
SELECT
dt_ms_item_out_detail.ms_item_out_detail_id as ms_production_in_detail_id
, isnull(dt_ms_type_prosses.ms_type_prosses_name,'') as ms_type_prosses_name
, isnull(dt_ms_car_line_out_header.ms_car_line_no,'') as ms_car_line_no
, isnull(dt_ms_car_line.ms_car_line_name,'') as ms_car_line_name
, vw_ms_item.part_no
, vw_ms_item.part_name
, vw_ms_item.ms_item_id
, vw_ms_item.ms_type_id
, vw_ms_item.uom
, dt_ms_item_out_detail.quantity
,isnull(dt_ms_item_out_detail.quantity,0) -( isnull(vw_ms_production_in_sum_to_memo.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_retur.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_part.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_change_part.out_qty,0)
+ isnull(vw_ms_production_in_sum_to_memo.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_retur.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_part.plan_qty,0)
+ isnull(vw_ms_production_in_sum_to_change_part.plan_qty,0)
) as quantity_sisa
,isnull(vw_ms_production_in_sum_to_retur.out_qty,0) + isnull(vw_ms_production_in_sum_to_memo.out_qty,0) as qty_out
,isnull(vw_ms_production_in_sum_to_retur.plan_qty,0) + isnull(vw_ms_production_in_sum_to_memo.plan_qty,0) as qty_plan
,isnull(vw_ms_production_in_sum_to_retur.out_qty,0) as retur_out
,isnull(vw_ms_production_in_sum_to_memo.out_qty,0) as memo_out
,isnull(vw_ms_production_in_sum_to_retur.plan_qty,0) as retur_plan
,isnull(vw_ms_production_in_sum_to_memo.plan_qty,0) as memo_plan
, dt_ms_car_line_out_header.doc_no
, dt_ms_car_line_out_header.doc_date
, dt_ms_car_line_out_header.doc_info
, vw_ms_item.ms_item_custom_no
, vw_ms_item.ms_item_custom_name
, isnull(vw_ms_item.ms_item_base_no,'') as ms_item_base_no
, isnull(vw_ms_item.ms_item_base_name,'') as ms_item_base_name
, vw_ms_item.consumable
, dt_ms_car_line_out_header_h_status.ms_car_line_out_header_status_id as status_id
, enum_ms_car_line_out_header_status.ms_car_line_out_header_status_name as status_name
, dt_ms_car_line_out_header.ms_type_prosses_id
, dt_ms_car_line_out_header.ms_car_line_out_header_id
, isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) as cancel_ms_cost_final_avg_id
, isnull(dt_ms_car_line_out_header.approve_ms_cost_final_avg_id,-1) as approve_ms_cost_final_avg_id
, case when isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) =-1 then
case when isnull(dt_ms_item_daily_in_move_avg.price_avg_move_usd,0)=0 or isnull(dt_ms_item_daily_in_move_avg.total_qty_avg_move,0)=0 then 0
else
dt_ms_item_daily_in_move_avg.price_avg_move_usd/dt_ms_item_daily_in_move_avg.total_qty_avg_move
end
else
case when isnull(dt_ms_item_list_price.end_price_usd,0)=0 or isnull(dt_ms_item_list_price.end_qty,0)=0 then 0
else dt_ms_item_list_price.end_price_usd / dt_ms_item_list_price.end_qty
end
end as price_usd
, case when isnull(dt_ms_car_line_out_header.cancel_ms_cost_final_avg_id,-1) =-1 then
case when dt_ms_item_daily_in_move_avg.price_avg_move_idr=0 or dt_ms_item_daily_in_move_avg.total_qty_avg_move=0 then 0
else
dt_ms_item_daily_in_move_avg.price_avg_move_idr/dt_ms_item_daily_in_move_avg.total_qty_avg_move
end
else
case when dt_ms_item_list_price.end_price =0 or dt_ms_item_list_price.end_qty =0 then 0 else
dt_ms_item_list_price.end_price / dt_ms_item_list_price.end_qty
end
end as price
, dt_ms_item_in_detail.ori_ms_in_item_detail_id
, vw_ms_item.allow_to_production
, vw_ms_item.mandatory_car_line
, vw_ms_item.allow_to_scrap
, vw_ms_item.allow_to_repair
, vw_ms_item.allow_to_selling
FROM dt_ms_item_out_detail INNER JOIN
dt_ms_car_line_out_detail ON dt_ms_item_out_detail.id_source = dt_ms_car_line_out_detail.ms_car_line_out_detail_id INNER JOIN
dt_ms_car_line_out_header ON dt_ms_car_line_out_detail.ms_car_line_out_header_id = dt_ms_car_line_out_header.ms_car_line_out_header_id INNER JOIN
dt_ms_car_line_out_header_h_status ON dt_ms_car_line_out_header.ms_car_line_out_header_h_status_id = dt_ms_car_line_out_header_h_status.ms_car_line_out_header_h_status_id INNER JOIN
enum_ms_car_line_out_header_status ON dt_ms_car_line_out_header_h_status.ms_car_line_out_header_status_id = enum_ms_car_line_out_header_status.ms_car_line_out_header_status_id INNER JOIN
dt_ms_item_daily_out_move_avg ON dt_ms_car_line_out_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id INNER JOIN
dt_ms_item_daily_in_move_avg ON dt_ms_item_daily_out_move_avg.ms_item_in_move_avg_id = dt_ms_item_daily_in_move_avg.ms_item_in_move_avg_id INNER JOIN
dt_ms_item_in_detail ON dt_ms_item_out_detail.ms_in_item_detail_id = dt_ms_item_in_detail.ms_in_item_detail_id INNER JOIN
vw_ms_item_no_log as vw_ms_item ON dt_ms_car_line_out_detail.ms_item_id = vw_ms_item.ms_item_id LEFT OUTER JOIN
dt_ms_item_list_price ON dt_ms_item_daily_in_move_avg.ms_item_h_price_id = dt_ms_item_list_price.ms_item_h_price_id AND
dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id = dt_ms_item_list_price.ms_cost_final_avg_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_retur ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_retur.ms_production_in_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_memo ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_memo.ms_production_in_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_part ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_part.ms_item_out_detail_id LEFT OUTER JOIN
vw_ms_production_in_sum_to_change_part ON dt_ms_item_out_detail.ms_item_out_detail_id = vw_ms_production_in_sum_to_change_part.ms_item_out_detail_id LEFT OUTER JOIN
dt_ms_car_line ON dt_ms_car_line_out_header.ms_car_line_no = dt_ms_car_line.ms_car_line_no LEFT OUTER JOIN
dt_ms_type_prosses ON dt_ms_car_line_out_header.ms_type_prosses_id = dt_ms_type_prosses.ms_type_prosses_id
WHERE (dt_ms_item_out_detail.mode_source = 0)
GO
Depends On
17- dbo.dt_ms_item_out_detail
- dbo.dt_ms_car_line_out_detail
- dbo.dt_ms_car_line_out_header
- dbo.dt_ms_car_line_out_header_h_status
- dbo.enum_ms_car_line_out_header_status
- dbo.dt_ms_item_daily_out_move_avg
- dbo.dt_ms_item_daily_in_move_avg
- dbo.dt_ms_item_in_detail
- dbo.vw_ms_item_no_log
- dbo.vw_ms_item
- dbo.dt_ms_item_list_price
- dbo.vw_ms_production_in_sum_to_retur
- dbo.vw_ms_production_in_sum_to_memo
- dbo.vw_ms_production_in_sum_to_part
- dbo.vw_ms_production_in_sum_to_change_part
- dbo.dt_ms_car_line
- dbo.dt_ms_type_prosses
Used By
15- dbo.vw_ms_memo_detail_suplly_source_production
- dbo.vw_ms_out_part_list_detail_supply
- dbo.vw_ms_out_part_list_sum_new_part
- dbo.vw_ms_part_list_detail_supply
- dbo.vw_ms_production_retur_detail
- dbo.vw_ms_production_retur_detail_source_available
- dbo.vw_ms_production_retur_header_count_detail
- dbo.vw_part_list_change_part_detail_supply
- dbo.vw_part_list_production_detail_supply
- dbo.vw_part_list_supply_available
- dbo.MG_MS_ITEM_FINAL_AVG_PABRIKASI_CREATE
- dbo.MG_MS_MEMO_DETAIL_SUPPLY_ADD
- dbo.MG_MS_MEMO_DETAIL_SUPPLY_EDIT
- dbo.MG_MS_PRODUCTION_RETUR_DETAIL_ADD
- dbo.MG_MS_PRODUCTION_RETUR_HEADER_EDIT