proc_mrp_purreq_cacu.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

IF object_id('dbo.Proc_MRP_PurReq_Cacu') is not null
drop procedure dbo.Proc_MRP_PurReq_Cacu
go


/*
select top 100 *  
From stockmanage..ord_item where hpn like '%CS007'
 and created_dt >getdate()-5

EXEC Proc_MRP_PurReq_Cacu 1,1,0,0,0,'CS007','400024640002-CS007',null,null,null,0

*/
create procedure Proc_MRP_PurReq_Cacu   -- PR for PO & PO Issued     BES96261   更改：将字全库存作为一项需求计算
  @Chk1 int, 
  @Chk2 int, 
  @Chk3 int,
  @Chk4 int,
  @Chk5 int,
  @Cust_Nm varchar(255),
  @ItemNo varchar(18),
  @CPN varchar(50), 
  @Typ_Nm varchar(255),
  @Controller varchar(15),
  @Group int    --0: PR for PO  1:Po Issued
AS
declare @I_Count int,@Col varchar(50),@Day varchar(50),@ItemNo2 varchar(18)
set @Day=convert(varchar(50),getdate(),111)
select @Chk1=Isnull(@Chk1,0),@Chk2=Isnull(@Chk2,0),@Chk3=Isnull(@Chk3,0),@Chk4=Isnull(@Chk4,0),@Chk5=Isnull(@Chk5,0),
       @Cust_Nm=merge_mrp.dbo.Bes_SubStr(@Cust_Nm),@Typ_Nm=merge_mrp.dbo.Bes_SubStr(@Typ_Nm),@Group=Isnull(@Group,0)
set @ItemNo2=Case when @Chk3=0 then '' else (select top 1 ItemNo from merge_mrp..V_Bas_Item_Bes where CPN=@CPN) end

Create Table #Demand(Cust_No varchar(50),Mst_Item varchar(50),ItemNo varchar(50),Ord_id int,
       CPN varchar(50),MPN varchar(50),Description varchar(255),Uom varchar(50),Consign varchar(50),
       Stock_Qty numeric(18,4)default(0),Req_Qty numeric(18,4)default(0),Deli_Day datetime,
       Over_Qty numeric(18,4)default(0),Req_Date datetime,Safe_Qty numeric(18,4)default(0),
       Total_Over numeric(18,4)default(0),IsCom int default(0),Vdr_Nm varchar(255),Total_PoDelay numeric(18,4)default(0))

--计算需求 
Insert Into #Demand(Cust_No,Mst_Item,ItemNo,Ord_id,Req_Qty,Deli_Day,Req_Date,Uom,Consign,Safe_Qty,IsCom)
select a.Cust_No,b.ItemNo,b.ItemNo,1,sum(merge_mrp.dbo.Bes_CntInt(a.Os_Qty*b.Usage_Qty)) as Req_Qty,
       a.Deli_Day,dateadd(DD,-16,a.Deli_Day) as Req_Date,b.Uom,max(b.Consign),0,0
 from merge_mrp..V_Sell_Orderc_Bes a, merge_mrp..V_Bas_Bom_MainItem b
 where  a.ItemNo=b.ParentNo and a.Cust_No=b.Cust_No
  and (@Chk1=0 or b.Cust_No=@Cust_Nm)
  and (@Chk2=0 or b.ItemNo=@ItemNo)
  and (@Chk3=0 or b.ItemNo=@ItemNo2)
  and (@Chk4=0 or Exists(select ItemNo from merge_mrp..V_Bas_Item_Bes where Typ_No=@Typ_nm and ItemNo=b.ItemNo))
  and (@Chk5=0 or Exists(select ItemNo from merge_mrp..V_Bas_Item_Bes where  Controller=@Controller and ItemNo=b.ItemNo))
 group by a.Cust_No,a.Deli_Day,b.ItemNo,b.Uom

--将需求加上损耗率
update #Demand set Req_Qty=merge_mrp.dbo.Bes_CntInt(a.Req_Qty*b.Scrap_Rate)
 from #Demand a, merge_mrp..V_Prod_Scrap_Bes b
 where a.ItemNo=b.ItemNo 

--加入所需安全库存的料品 2004-11-26
Insert into #Demand(Cust_No,Mst_Item,ItemNo,Ord_id,Req_Qty,Deli_Day,Req_Date,Uom,Consign,Safe_Qty,IsCom)
select b.Cust_No,b.ItemNo,b.ItemNo,1,0 as Req_Qty,
       cast(@Day as datetime) as Deli_Day,(cast(@Day as datetime)-16) as Req_Date,b.Unit as Uom,'' as Consign,0,0
 from merge_mrp..V_Prod_ItemSafe_Bes b
 where not Exists(select ItemNo from #Demand where ItemNo=b.ItemNo)
  and (@Chk1=0 or b.Cust_No=@Cust_Nm)
  and (@Chk2=0 or b.ItemNo=@ItemNo)
  and (@Chk3=0 or b.ItemNo=@ItemNo2)
  and (@Chk4=0 or Exists(select ItemNo from merge_mrp..V_Bas_Item_Bes where Typ_No=@Typ_nm and ItemNo=b.ItemNo))
  and (@Chk5=0 or Exists(select ItemNo from merge_mrp..V_Bas_Item_Bes where  Controller=@Controller and ItemNo=b.ItemNo))

--计算供应
select distinct a.ItemNo,a.PoNo,a.Ord_Qty-a.Rec_Qty as Os_Qty,a.Deli_Day,a.Uom
Into #tem_PoData
  from merge_mrp..V_Pur_Orderc_Bes_2 a,(select distinct ItemNo from #Demand) c
  where a.ItemNo=c.ItemNo and a.Ord_Qty>a.Rec_Qty
--添加代用料品的供应明细  一个代替品，只能对应一个主品
Insert into #tem_PoData(ItemNo,PoNo,Os_Qty,Deli_Day,Uom)
select d.Mst_Item,a.PoNo,a.Ord_Qty-a.Rec_Qty as Os_Qty,a.Deli_Day,a.Uom
from merge_mrp..V_Pur_Orderc_Bes_2 a,
    (select Min(a.Mst_Item) as Mst_Item,a.ItemNo  --将代用品料号换成主品料号
     from merge_mrp..V_Bas_Bom_Suintem a, (select distinct ItemNo from #Demand) c 
     where a.Mst_Item=c.ItemNo Group by a.ItemNo) d
where a.ItemNo=d.ItemNo
    and d.ItemNo not in(select ItemNo from #Demand) and a.Ord_Qty-a.Rec_Qty>0

--将PO交期整理成符合最早一次需求的日期
Update #tem_PoData set Deli_Day=b.Req_Date
from #tem_PoData a, 
 (select b.ItemNo,a.Deli_Day,min(b.Req_Date) as Req_Date
  from
  (select ItemNo,Deli_Day from #tem_PoData a where not exists(select * from #Demand where ItemNo=a.ItemNo and Req_Date=a.Deli_Day)) a,
  #Demand b where a.ItemNo=b.ItemNo and a.Deli_Day<b.Req_Date
 group by b.ItemNo, a.Deli_Day) b
where a.ItemNo=b.ItemNo and a.Deli_Day=b.Deli_Day
--各交期的总Po数
select identity(int,1,1) as Add_ID,ItemNo,PoNo,Sum(Isnull(Os_Qty,0)) as Os_Qty, Deli_Day 
into #PoData
from #tem_PoData
group by ItemNo,PoNo,Deli_Day
--将交期迟过最后需求日期的数据进行统计
update #Demand set Total_PoDelay = b.Os_Qty
from #Demand a,(select a.ItemNo,sum(a.Os_Qty) as Os_Qty from #PoData a
                where not Exists(select Req_Date from #Demand where ItemNo=a.ItemNo and Req_Date=a.Deli_Day)
                group by a.ItemNo) b 
where a.ItemNo=b.ItemNo

--将各PO O/S顺序添加至#Demand表中  
select @I_Count=max(a.C) from (select ItemNo,count(*) as C from #PoData group by ItemNo,Deli_Day) a
while @I_Count>0
begin
  set @Col=cast(@I_Count as varchar(5))
  Exec(' Alter table #Demand add APo_' +@Col+' varchar(255) Null')
  Exec(' Alter table #Demand add BPo_' +@Col+' Float Null')
  Exec(' Update #Demand set Over_Qty=Isnull(a.Over_Qty,0)+b.Os_Qty,APo_'+@Col+'= b.PoNo,BPo_'+@Col+'=b.Os_Qty '+
       ' from #Demand a,#PoData b,(select Min(Add_ID) as Add_ID from #PoData group by ItemNo,Deli_Day) c'+
       ' where b.Add_ID=c.Add_ID and a.ItemNo=b.ItemNo and a.Req_Date=b.Deli_Day') 
  Delete #PoData from (select Min(Add_ID) as Add_ID from #PoData group by ItemNo,Deli_Day) a, #PoData b
    where a.Add_ID=b.Add_ID
  Set @I_Count=@I_Count-1
end
drop table #tem_PoData
drop table #PoData

--更新可用数及库存数
update #Demand set Over_Qty=Isnull(a.Over_Qty,0)+c.Stock_Qty, Stock_Qty=c.Stock_Qty
from #Demand a, (select ItemNo, Min(Req_Date) as Req_Date from #Demand group by ItemNo) b, 
     merge_mrp..V_Dpt_Stock_Bes c
where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date and a.ItemNo=c.ItemNo

--将代替品库存分配给主料品
update #Demand set Over_Qty=Isnull(a.Over_Qty,0)+v.Stock_Qty, Stock_Qty=a.Stock_Qty+v.Stock_Qty
from #Demand a, (select ItemNo, Min(Req_Date) as Req_Date from #Demand group by ItemNo) b, 
 (select d.Mst_Item,sum(c.Stock_Qty) as Stock_Qty
  from merge_mrp..V_Dpt_Stock_Bes c, 
      (select Min(a.Mst_Item) as Mst_Item,a.ItemNo  --将代用品料号换成主品料号
       from merge_mrp..V_Bas_Bom_Suintem a, (select distinct ItemNo from #Demand) c 
       where a.Mst_Item=c.ItemNo and a.ItemNo not in(select ItemNo from #Demand)
       Group by a.ItemNo) d
  where c.ItemNo=d.ItemNo
  group by d.Mst_Item ) v
where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date and a.ItemNo=v.Mst_Item

update #Demand set Over_Qty=Over_Qty-Req_Qty,Total_Over=Over_Qty-Req_Qty,Safe_Qty=0
--添加安全库存
Update #Demand set Total_Over=a.Total_Over-c.Safe_Qty,Safe_Qty=c.Safe_Qty  --,Over_Qty=a.Over_Qty-c.Safe_Qty
  from #Demand a,(select ItemNo,Min(Req_Date) as Req_Date from #Demand where IsCom=0 group by ItemNo) b, merge_mrp..V_Prod_ItemSafe_Bes c
  where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date and b.Itemno=c.ItemNo

--最早需求记录
select a.ItemNo,a.Req_Date,a.Total_Over as Over_Qty,a.Safe_Qty
 Into #Tem1
from #Demand a,(select ItemNo,Min(Req_Date) as Req_Date from #Demand where IsCom=0 group by ItemNo) b
where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date

--次早需求记录(需进行推算的记录)
select a.ItemNo,a.Req_Date,b.Over_Qty,b.Safe_Qty
 Into #Tem2  
from (select a.ItemNo,Min(a.Req_Date) as Req_Date
         from #Demand a, #Tem1 b  where a.IsCom=0 and a.ItemNo=b.ItemNo and a.Req_Date<>b.Req_Date
      group by a.ItemNo) a, #Demand b
where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date

select @I_Count=max(a.C) from (select count(*) as C from #Demand group by ItemNo) a
While @I_Count>0
begin
  Update #Demand set Over_Qty=a.Over_Qty+merge_mrp.dbo.Bes_NegCntZero(b.Over_Qty),Total_Over=a.Over_Qty+merge_mrp.dbo.Bes_NegCntZero(b.Over_Qty)
    from #Demand a, #Tem1 b, #Tem2 c
    where a.ItemNo=b.ItemNo and a.ItemNo=c.ItemNo and a.Req_Date=c.Req_Date

  --将结果值更新为下一次推算的起始值
  update #Tem2 set Over_Qty = b.Total_Over
    from #Tem2 a,#Demand b where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date
  Update #Demand set IsCom=1 from #Demand a, #Tem1 b
    where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date

  Delete #Tem1
  insert into #Tem1 select * from #Tem2
  Delete #Tem2
  --产生下次推算的记录数据
  Insert Into #Tem2 select a.ItemNo,a.Req_Date,b.Over_Qty ,0 as Safe_Qty
  from (select a.ItemNo,Min(a.Req_Date) as Req_Date
    from #Demand a, #Tem1 b
    where a.IsCom=0 and a.ItemNo=b.ItemNo and a.Req_Date<>b.Req_Date group by a.ItemNo) a, #Demand b
  where a.ItemNo=b.ItemNo and a.Req_Date=b.Req_Date

  Set @I_Count=@I_Count-1
end

Drop table #Tem2
Drop table #Tem1

--添加代用品料品信息
Insert Into #Demand(Cust_No,Mst_Item,ItemNo,Ord_id,Req_Qty,Deli_Day,Req_Date,Uom,Consign,Safe_Qty,IsCom)
select distinct a.Cust_No,a.Mst_Item,a.ItemNo,2,0,null,null,a.Uom,a.Consign,0,1
from merge_mrp..V_Bas_Bom_Suintem a
where Exists(select ItemNo from #Demand where Cust_No=a.Cust_No and ItemNo=a.Mst_Item) 

--添加建议供应商资料
select a.ItemNo,a.Vdr_Nm,a.Ord_id, a.LeadTime
Into #Tem6
  from merge_mrp..V_Pur_SuggVdr_Bes a, (select distinct ItemNo from #Demand) b
  where a.ItemNo=b.ItemNo --and PatIndex('%EPSON%',Vdr_Nm)=0
  order by a.ItemNo, a.Ord_id
select @I_Count=max(a.C) from (select a.ItemNo,Count(*)as C from #Tem6 a group by a.ItemNo) a
while @I_Count>0
begin
  set @Col=cast(@I_Count as varchar(5))
  Exec(' Alter table #Demand add SuggestV_' +@Col+' varchar(255) Null')
  Exec(' Alter table #Demand add SugLT_' +@Col+' varchar(255) Null')
  Exec(' Alter table #Demand add SugShort_' +@Col+'  Int Null')
  Exec(' Update #Demand set SuggestV_'+@Col+'= b.Vdr_Nm, SugLT_'+@Col+'= b.LeadTime,SugShort_'+@Col+
       '=Case when Cast(a.Req_Date-Cast(Replace(b.LeadTime,'+''''+'DAYS'+''''+','+''''+''+''''+') as int)-'+''''+@Day+''''+
          ' as Int)>0 then 0 else Cast(a.Req_Date-Cast(Replace(b.LeadTime,'+''''+'DAYS'+''''+','+''''+''+''''+') as int)-'+''''+@Day+''''+' as Int) end '+
       ' from #Demand a, #Tem6 b, (select ItemNo, Max(Ord_ID) as Ord_iD from #Tem6 group by ItemNo) c '+
       ' where b.ItemNo=c.ItemNo and b.Ord_id=c.Ord_id and a.ItemNo=b.ItemNo') 
  Delete #Tem6 from #Tem6 a, (select ItemNo, Max(Ord_ID) as Ord_iD from #Tem6 group by ItemNo) b
       where a.ItemNo=b.ItemNo and a.Ord_id=b.Ord_id
  Set @I_Count=@I_Count-1
end
Drop table #Tem6

Exec(' Alter table #Demand add Total_Short  Float Null')
--最后一次采购Vendor
update #Demand set Vdr_Nm=b.Vdr_Nm
  from #Demand a, merge_mrp..V_Pur_LastVendor_Bes b where a.ItemNo=b.ItemNo

--更新料品信息
Update #Demand set CPN=case when Ord_id=2 then '*' else '' end+b.CPN,Description=b.Description,Mpn=b.Mpn
  from #Demand a, merge_mrp..V_Bas_Item_Bes b where a.ItemNo=b.ItemNo

update #Demand set Req_Date=Null where Isnull(Total_Over,0) >= 0
Exec(' Alter table #Demand add ParentNo varchar(50) null')
Exec(' update #Demand set ParentNo= b.ParentNo from #Demand a, '+
         ' (select a.ItemNo, min(a.ParentNo) as ParentNo from merge_mrp..V_Bas_Bom_MainItem a, merge_mrp..V_Sell_Orderc b where a.ParentNo=b.ItemNo group by a.ItemNo) b '+
         ' where a.ItemNo=b.ItemNo ')
Exec(' update #Demand set Total_Short=b.qty from #Demand a,(select ItemNo, sum(Isnull(Total_Over,0)) as qty from #Demand where Total_Over<0 and Ord_id=1 group by ItemNo) b '+
         ' where a.ItemNo=b.ItemNo and  a.Ord_id=1')

if @Group = 0    --PR for PO
    select * from #Demand order by Mst_Item,Ord_id,Deli_Day,ItemNo
else  --PO Issued
begin
    Exec(' alter table #Demand add Po_Uom varchar(50), Po_Short_Qty numeric(18,4)')
    Exec(' update #Demand set Po_Uom=b.Po_Uom, Po_Short_Qty=a.Total_Over*case when a.Uom<>b.Po_Uom then merge_mrp.dbo.Bes_CntUnit(a.ItemNo,a.Uom,b.Po_Uom) else 1 end  '+
             ' from #Demand a, merge_mrp..V_Pur_LastUnitPrc_Bes b where Exists(select 1 from merge_mrp..V_Bas_CntUnit where Mst_Unit=a.Uom and ItemNo=a.ItemNo and CntUnit=b.Po_Uom) and a.ItemNo=b.ItemNo ')
    select * from #Demand where  Total_Over<0 and Ord_id=1 and Uom<>'UN' order by Vdr_Nm, Mst_Item,Ord_id,Deli_Day,ItemNo
end

Drop table #Demand
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO