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 | 11/6/2020 19:06:49 |
| Last Modified | 11/6/2020 19:06:49 |
Columns
| Key | Name | Description |
|---|---|---|
| ms_part_list_production_no | ||
| ms_part_list_production_date | ||
| status_info | ||
| ms_process_id | ||
| ms_process_name | ||
| ms_car_line_no | ||
| ms_car_line_name | ||
| ms_item_id | ||
| part_no | ||
| part_name | ||
| qty_total |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE view dbo.vw_ms_out_part_list_sum_recycle
AS
SELECT
vw_part_list_production_header.ms_part_list_production_no
, vw_part_list_production_header.ms_part_list_production_date
, vw_part_list_production_header.status_info
, enum_ms_part_list_process.ms_process_id
, enum_ms_part_list_process.ms_process_name
, vw_part_list_production_header.ms_car_line_no
, vw_part_list_production_header.ms_car_line_name
, vw_ms_production_in_detail_recycle.ms_item_id
, vw_ms_production_in_detail_recycle.part_no
, vw_ms_production_in_detail_recycle.part_name
, dt_ms_part_list_out.quantity as qty_total
FROM dt_ms_part_list_out
INNER JOIN vw_part_list_production_detail ON dt_ms_part_list_out.ms_part_list_process_detail_id = vw_part_list_production_detail.ms_part_list_production_detail_id
INNER JOIN vw_part_list_production_header ON vw_part_list_production_detail.ms_part_list_production_id = vw_part_list_production_header.ms_part_list_production_id
INNER JOIN enum_ms_part_list_process ON dt_ms_part_list_out.ms_process_id = enum_ms_part_list_process.ms_process_id
LEFT OUTER JOIN vw_ms_production_in_detail_recycle ON dt_ms_part_list_out.ms_item_out_detail_id = vw_ms_production_in_detail_recycle.ms_production_in_detail_id
WHERE dt_ms_part_list_out.ms_process_id = 1
AND dt_ms_part_list_out.ms_trans_type_id = 2
AND vw_part_list_production_header.ms_part_list_production_status_id = 1
UNION ALL
SELECT
vw_part_list_change_part_header.ms_part_list_change_part_no
, vw_part_list_change_part_header.ms_part_list_change_part_date
, vw_part_list_change_part_header.status_info
, enum_ms_part_list_process.ms_process_id
, enum_ms_part_list_process.ms_process_name
, vw_part_list_change_part_header.ms_car_line_no
, vw_part_list_change_part_header.ms_car_line_name
, vw_ms_production_in_detail_recycle.ms_item_id
, vw_ms_production_in_detail_recycle.part_no
, vw_ms_production_in_detail_recycle.part_name
, dt_ms_part_list_out.quantity as qty_total
FROM dt_ms_part_list_out
INNER JOIN vw_part_list_change_part_detail ON dt_ms_part_list_out.ms_part_list_process_detail_id = vw_part_list_change_part_detail.ms_part_list_change_part_detail_id
INNER JOIN vw_part_list_change_part_header ON vw_part_list_change_part_detail.ms_part_list_change_part_id = vw_part_list_change_part_header.ms_part_list_change_part_id
INNER JOIN enum_ms_part_list_process ON dt_ms_part_list_out.ms_process_id = enum_ms_part_list_process.ms_process_id
LEFT OUTER JOIN vw_ms_production_in_detail_recycle ON dt_ms_part_list_out.ms_item_out_detail_id = vw_ms_production_in_detail_recycle.ms_production_in_detail_id
WHERE dt_ms_part_list_out.ms_process_id = 2
AND dt_ms_part_list_out.ms_trans_type_id = 2
AND vw_part_list_change_part_header.ms_part_list_change_part_status_id = 1
GO
GO
CREATE view dbo.vw_ms_out_part_list_sum_recycle
AS
SELECT
vw_part_list_production_header.ms_part_list_production_no
, vw_part_list_production_header.ms_part_list_production_date
, vw_part_list_production_header.status_info
, enum_ms_part_list_process.ms_process_id
, enum_ms_part_list_process.ms_process_name
, vw_part_list_production_header.ms_car_line_no
, vw_part_list_production_header.ms_car_line_name
, vw_ms_production_in_detail_recycle.ms_item_id
, vw_ms_production_in_detail_recycle.part_no
, vw_ms_production_in_detail_recycle.part_name
, dt_ms_part_list_out.quantity as qty_total
FROM dt_ms_part_list_out
INNER JOIN vw_part_list_production_detail ON dt_ms_part_list_out.ms_part_list_process_detail_id = vw_part_list_production_detail.ms_part_list_production_detail_id
INNER JOIN vw_part_list_production_header ON vw_part_list_production_detail.ms_part_list_production_id = vw_part_list_production_header.ms_part_list_production_id
INNER JOIN enum_ms_part_list_process ON dt_ms_part_list_out.ms_process_id = enum_ms_part_list_process.ms_process_id
LEFT OUTER JOIN vw_ms_production_in_detail_recycle ON dt_ms_part_list_out.ms_item_out_detail_id = vw_ms_production_in_detail_recycle.ms_production_in_detail_id
WHERE dt_ms_part_list_out.ms_process_id = 1
AND dt_ms_part_list_out.ms_trans_type_id = 2
AND vw_part_list_production_header.ms_part_list_production_status_id = 1
UNION ALL
SELECT
vw_part_list_change_part_header.ms_part_list_change_part_no
, vw_part_list_change_part_header.ms_part_list_change_part_date
, vw_part_list_change_part_header.status_info
, enum_ms_part_list_process.ms_process_id
, enum_ms_part_list_process.ms_process_name
, vw_part_list_change_part_header.ms_car_line_no
, vw_part_list_change_part_header.ms_car_line_name
, vw_ms_production_in_detail_recycle.ms_item_id
, vw_ms_production_in_detail_recycle.part_no
, vw_ms_production_in_detail_recycle.part_name
, dt_ms_part_list_out.quantity as qty_total
FROM dt_ms_part_list_out
INNER JOIN vw_part_list_change_part_detail ON dt_ms_part_list_out.ms_part_list_process_detail_id = vw_part_list_change_part_detail.ms_part_list_change_part_detail_id
INNER JOIN vw_part_list_change_part_header ON vw_part_list_change_part_detail.ms_part_list_change_part_id = vw_part_list_change_part_header.ms_part_list_change_part_id
INNER JOIN enum_ms_part_list_process ON dt_ms_part_list_out.ms_process_id = enum_ms_part_list_process.ms_process_id
LEFT OUTER JOIN vw_ms_production_in_detail_recycle ON dt_ms_part_list_out.ms_item_out_detail_id = vw_ms_production_in_detail_recycle.ms_production_in_detail_id
WHERE dt_ms_part_list_out.ms_process_id = 2
AND dt_ms_part_list_out.ms_trans_type_id = 2
AND vw_part_list_change_part_header.ms_part_list_change_part_status_id = 1
GO