dbo.vw_ms_production_in_detail_recycle

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO





CREATE view dbo.vw_ms_production_in_detail_recycle
AS


SELECT 
dt_ms_part_list.ms_part_list_id AS ms_production_in_detail_id
, 'STA' AS doc_from
, '' AS receive_no
, dt_ms_part_list.begin_qty_recycle_date AS receive_date
, 'STA' AS doc_no
, dt_ms_part_list.begin_qty_recycle_date AS doc_date
, vw_ms_item.part_no
, vw_ms_item.part_name 
, vw_ms_item.ms_type_id
, vw_ms_item.uom
, dt_ms_part_list.begin_qty_recycle as quantity
, isnull(dt_ms_part_list.begin_qty_recycle,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_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_part.out_qty,0) as part_out
, isnull(vw_ms_production_in_sum_to_change_part.out_qty,0) as change_part_out
, isnull(vw_ms_production_in_sum_to_part.plan_qty,0) as part_plan
, isnull(vw_ms_production_in_sum_to_change_part.plan_qty,0) as change_part_plan

, 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

, vw_ms_item.ms_item_id
, 1 AS is_sumber
from 
dt_ms_part_list
LEFT OUTER JOIN vw_ms_item ON dt_ms_part_list.ms_item_id = vw_ms_item.ms_item_id
LEFT OUTER JOIN vw_ms_production_in_sum_to_part ON dt_ms_part_list.ms_part_list_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_part_list.ms_part_list_id = vw_ms_production_in_sum_to_change_part.ms_item_out_detail_id 
where isnull(dt_ms_part_list.begin_qty_recycle,0) > 0


UNION ALL
select 
ms_part_list_recycle_id AS ms_production_in_detail_id
, '' AS doc_from
, ms_part_list_disamble_no AS receive_no
, ms_part_list_disamble_date AS receive_date
, ms_part_list_disamble_no AS doc_no
, ms_part_list_disamble_date AS doc_date
, vw_ms_item.part_no
, vw_ms_item.part_name 
, vw_ms_item.ms_type_id
, vw_ms_item.uom
, dt_ms_part_list_recycle.quantity as quantity
, isnull(dt_ms_part_list_recycle.quantity,0) -( isnull(vw_ms_production_in_sum_to_part.out_qty,0)
              + isnull(vw_ms_change_part_in_sum_to_part.out_qty,0)
              + isnull(vw_ms_production_in_sum_to_part.plan_qty,0)
              + isnull(vw_ms_change_part_in_sum_to_part.plan_qty,0)
               ) as quantity_sisa
, isnull(vw_ms_production_in_sum_to_part.out_qty,0) as part_out
, isnull(vw_ms_change_part_in_sum_to_part.out_qty,0) as change_part_out
, isnull(vw_ms_production_in_sum_to_part.plan_qty,0) as part_plan
, isnull(vw_ms_change_part_in_sum_to_part.plan_qty,0) as change_part_plan

, 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_part_list_recycle.ms_item_id
, 1 AS is_sumber
from dt_ms_part_list_recycle
LEFT OUTER JOIN vw_ms_item ON dt_ms_part_list_recycle.ms_item_id = vw_ms_item.ms_item_id
LEFT OUTER JOIN dt_ms_part_list_disamble_detail ON dt_ms_part_list_recycle.ms_part_list_disamble_detail_id = dt_ms_part_list_disamble_detail.ms_part_list_disamble_detail_id
LEFT OUTER JOIN dt_ms_part_list_disamble_header ON dt_ms_part_list_disamble_detail.ms_part_list_disamble_id = dt_ms_part_list_disamble_header.ms_part_list_disamble_id
LEFT OUTER JOIN vw_ms_production_in_sum_to_part ON dt_ms_part_list_recycle.ms_part_list_recycle_id = vw_ms_production_in_sum_to_part.ms_item_out_detail_id 
LEFT OUTER JOIN vw_ms_change_part_in_sum_to_part ON dt_ms_part_list_recycle.ms_part_list_recycle_id = vw_ms_change_part_in_sum_to_part.ms_item_out_detail_id 
where isnull(dt_ms_part_list_recycle.quantity,0) > 0






GO