物控档中，当A 是主料，但同时A以是B 的替代料时（且作为替代料未领料时），倒扣A会扣双倍
物控档中，当A 是主料，但同时A以是B 的替代料时（且作为替代料未领料时），倒扣A会扣双倍。目前是按如下方法处理的： --主产品临时表 insert into #t_control Select oscm_control.part_no, isnull(oscm_control.dIf_flag,'') as dIf_flag,oscm_control.cust_no, oscm_control.bom_type as pk_sta, oscm_control.mpart_no,
oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty-
oscm_control.out_badqty as out_qty,
oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty
- oscm_control.out_badqty as out_qty1,
oscm_control.in_tempqty, oscm_control.in_qty, oscm_control.use_replaceqty,
case when obas_sheet_type.del_scrap_sta='1' then
Cast(@sheet_qty * oscm_control.total_unit_qty / oscm_control.total_bas_qty as numeric(18,8))+
case when obas_sheet_type.scrap_sta = '1' then Cast(@sheet_qty * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty as numeric(18,8))
when obas_part1.stk_scrap_sta = '1' then Cast(@sheet_qty * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty as numeric(18,8))
else 0
end
else
Cast(@sheet_qty * oscm_control.total_unit_qty / oscm_control.total_bas_qty as numeric(18,8))
end
as sheet_qty,
case when obas_sheet_type.del_scrap_sta='1' then
Cast(@sheet_qty1 * oscm_control.total_unit_qty / oscm_control.total_bas_qty as numeric(18,8))+
case when obas_sheet_type.scrap_sta = '1' then Cast(@sheet_qty1 * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty as numeric(18,8))
when obas_part1.stk_scrap_sta = '1' then Cast(@sheet_qty1 * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty as numeric(18,8))
else 0
end
else
Cast(@sheet_qty1 * oscm_control.total_unit_qty / oscm_control.total_bas_qty as numeric(18,8))
end
as sheet_qty1,
oscm_control.unit_no, oscm_control.unit_rate, oscm_control.sheet_pri,
oscm_control.grade, obas_part1.qty_round, oscm_control.total_bas_qty,
oscm_control.total_unit_qty, oscm_control.scrap_rate, oscm_control.group_code,
1.0 , @sheet_qty+@sheet_qty1 as in_taoqty, oscm_control.dum_part
From oscm_control,opur_pur1, opur_pur2,obas_sheet_type,obas_part1
where opur_pur1.sheet_no=opur_pur2.sheet_no
and opur_pur1.sheet_type=obas_sheet_type.sheet_type
and obas_sheet_type.sheet_kind in ('SFC01','SUB03')
and opur_pur2.sheet_lot=oscm_control.sheet_no
and oscm_control.part_no=obas_part1.part_no
and oscm_control.sheet_no=@pur_lot
and opur_pur2.part_no=@part_no
and obas_part1.org_no=@org_no
-- and obas_part1.whout_sw ='2'
and oscm_control.bom_type in ('0','1')
and not (oscm_control.bom_type='1' and oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty-
oscm_control.out_badqty=0 ) --李顶立，解决主替代料一样，倒扣重复问题
--替代品临时表
insert into #t_control
select oscm_control.part_no, isnull(oscm_control.dIf_flag,'') as dIf_flag, oscm_control.cust_no, oscm_control.bom_type as pk_sta, oscm_control.mpart_no,
oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty-
oscm_control.out_badqty as out_qty,
oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty
- oscm_control.out_badqty as out_qty1,
oscm_control.in_tempqty, oscm_control.in_qty, oscm_control.use_replaceqty,
case when obas_sheet_type.del_scrap_sta='1' then
Cast(@sheet_qty * oscm_control.total_unit_qty / oscm_control.total_bas_qty /osfc_p_replace.r_rate as numeric(18,8))+
case when obas_sheet_type.scrap_sta = '1' then Cast(@sheet_qty * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty /osfc_p_replace.r_rate as numeric(18,8))
when obas_part1.stk_scrap_sta = '1' then Cast(@sheet_qty * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty /osfc_p_replace.r_rate as numeric(18,8))
else 0
end
else
Cast(@sheet_qty * oscm_control.total_unit_qty / oscm_control.total_bas_qty /osfc_p_replace.r_rate as numeric(18,8))
end
as sheet_qty,
case when obas_sheet_type.del_scrap_sta='1' then
Cast(@sheet_qty1 * oscm_control.total_unit_qty / oscm_control.total_bas_qty /osfc_p_replace.r_rate as numeric(18,8))+
case when obas_sheet_type.scrap_sta = '1' then Cast(@sheet_qty1 * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty /osfc_p_replace.r_rate as numeric(18,8))
when obas_part1.stk_scrap_sta = '1' then Cast(@sheet_qty1 * oscm_control.unit_qty * oscm_control.scrap_rate / oscm_control.bas_qty /osfc_p_replace.r_rate as numeric(18,8))
else 0
end
else
Cast(@sheet_qty1 * oscm_control.total_unit_qty / oscm_control.total_bas_qty /osfc_p_replace.r_rate as numeric(18,8))
end
as sheet_qty1,
oscm_control.unit_no, oscm_control.unit_rate, oscm_control.sheet_pri,
oscm_control.grade, obas_part1.qty_round, oscm_control.total_bas_qty,
oscm_control.total_unit_qty, oscm_control.scrap_rate, oscm_control.group_code,
osfc_p_replace.r_rate , @sheet_qty+@sheet_qty1 as in_taoqty, oscm_control.dum_part
From oscm_control,opur_pur1,opur_pur2,obas_sheet_type,osfc_p_replace,obas_part1
Where opur_pur1.sheet_no=opur_pur2.sheet_no
and opur_pur1.sheet_type=obas_sheet_type.sheet_type
and obas_sheet_type.sheet_kind in ('SFC01','SUB03')
and opur_pur2.sheet_lot=oscm_control.sheet_no
and oscm_control.part_no=obas_part1.part_no
and oscm_control.sheet_no=@pur_lot
and obas_part1.org_no=@org_no
-- and obas_part1.whout_sw = '2'
and oscm_control.bom_type in ('0','1')
and opur_pur1.org_no=osfc_p_replace.org_no
and opur_pur2.part_no=osfc_p_replace.part_no
and opur_pur2.part_no<>@part_no
and osfc_p_replace.r_part_no=@part_no
and osfc_p_replace.flag='1'
--and osfc_p_replace.r_type='2'
and osfc_p_replace.r_type=case when obas_sheet_type.sheet_kind= 'SFC01' then '2' else '3' end
and not (oscm_control.bom_type='1' and oscm_control.out_llqty+oscm_control.out_preqty+oscm_control.out_transqty+
oscm_control.out_borrowqty-oscm_control.out_goodqty-oscm_control.out_purqty-
oscm_control.out_badqty=0 ) --李顶立，解决主替代料一样，倒扣重复问题