proc_expand_cost_bom.sql

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

if object_id('dbo.Proc_Expand_Cost_BOM') is not null
drop procedure dbo.Proc_Expand_Cost_BOM
go


create  Procedure Proc_Expand_Cost_BOM    -- Proc_Expand_Cost_BOM '1200','85C530000000','M',1,1,'HKD'
@plant  char(4) = NULL,
@model_number  varchar(50),         --Component Number/Model Number
@bom_Catg char(1),                   --BOM Category
@Requisition_Qty Float,             --requisition  Qty, total requisition Qty = bom used * SO Qty
@Level int=1,                      --1: Expand BOM 2: Requisition Qty calculation
@Currency   CHAR(3) = null
  
as
begin
if(@Currency='')
select @Currency ='HKD'

If Isnull(@Requisition_Qty,0)<=0 
set @Requisition_Qty = 1
If ltrim(rtrim(@bom_Catg)) not in('M','E','S','P')
begin
    if Exists(select bom_num from ca_matl_bom where matl_cd=@model_number and bom_usg='M' and plant_num =@plant)
    set @bom_Catg = 'M'
    else if Exists(select bom_num from ca_matl_bom where matl_cd=@model_number and bom_usg='E' and plant_num =@plant)
    set @bom_Catg = 'E'
    else if Exists(select bom_num from ca_matl_bom where matl_cd=@model_number and bom_usg='E' and plant_num =@plant)
    set @bom_Catg = 'P'
    else if Exists(select bom_num from ca_matl_bom where matl_cd=@model_number and bom_usg='E' and plant_num =@plant)
    set @bom_Catg = 'S'
end
else if Exists(select bom_num from ca_matl_bom 
where matl_cd=@model_number and bom_usg=@bom_Catg and plant_num =@plant)
begin
      select 
      cast(dbo.FillChar(a.bom_node)as varchar(50)) as Level_ID, 
       cast(1 as int) as BOM_Level,
       a.bom_catg,
       a.bom_num,
       a.bom_node,
       a.int_count,
       a.bom_comp,
       a.bom_itm_num,
       a.uom,a.qty,
       a.qty*@Requisition_Qty as Requisition_Qty,
       a.description,
       a.cust_part,
       a.item_typ,
       cast(0 as Float) as Stock_Qty,
       cast('' as varchar(50)) as Warehouse,
       cast(0 as Float) as Issued_Qty
    into #bom_item
 from ca_bom_itm a,ca_matl_bom b 
 where b.bom_usg=@bom_Catg and b.plant_num =@plant
 and b.matl_cd=@model_number 
 and a.bom_num = b.bom_num 
 and a.bom_catg=b.bom_usg
  and Isnull(a.del_ind,'')<>'X'
 order by a.Level_ID
----------------Get bom 's Level Structure---------------------------------------------
while @@rowcount>0
begin
 Insert into #bom_item
 select 
 b.Level_ID+.dbo.FillChar(a.bom_node) as Level_ID,
 len(b.Level_ID+.dbo.FillChar(a.bom_node))/4,
 a.bom_catg,
 a.bom_num,
 a.bom_node,
 a.int_count,
 a.bom_comp,
 a.bom_itm_num,
 a.uom,
 a.qty,
 a.qty*b.Requisition_Qty as Req_qty,
 a.description,
 a.cust_part,
 a.item_typ,0,'',0
 from ca_bom_itm a,
 (select a.bom_num,
 b.Level_ID,
 b.bom_catg,
 b.qty,
 b.Requisition_Qty
 from ca_matl_bom a,
 #bom_item b
  where a.bom_usg=b.bom_catg and a.plant_num =@plant
  and a.matl_cd=b.bom_comp) b
  where a.bom_catg=b.bom_catg 
  and a.bom_num = b.bom_num 
  and isnull(a.del_ind,'')<>'X'
  and not Exists(select bom_num from #bom_item 
  where bom_num=a.bom_num and bom_node=a.bom_node 
  and int_count=a.int_count)
 order by len(Level_ID)
end 

Update #bom_item 
        set description=b.description,
        uom=b.base_uom,
        item_typ=b.item_typ
        from #bom_item a, ca_mm_basic b
        where a.bom_comp=b.item_cd
--计算各级BOM物料的需求
If @Level=2
begin
  update #bom_item set Warehouse='130'
  update #bom_item set Stock_Qty=b.Stock_Qty
   from #bom_item a, Depot_Itemdata b
   where a.bom_comp=b.itemno and a.warehouse=b.Depot

  declare @I int, @maxrep int
  set @I = 4
  select @maxrep = max(len(Level_ID)) from #bom_item  
  while @I <= @maxrep
  begin
    Update #bom_item 
          set Issued_qty = dbo.GetMin(Requisition_Qty,Stock_Qty)
          where len(Level_ID)=@I and Qty>0
    Update #bom_item 
            set Requisition_Qty = a.Requisition_Qty-(a.Requisition_Qty*1.0/b.Requisition_Qty * b.Issued_Qty)
            from #bom_item a, (select Level_ID,Requisition_Qty,Issued_Qty 
            from #bom_item where len(Level_ID)=@I and Qty>0) b
            where len(a.Level_ID)>@I and left(a.Level_ID,@I)=b.Level_ID
    set @I = @I +4
  end
end 
------------------------------------------add fields-----------------------------
select hpn, (dbo.Currency_conv(@Currency,Unitprice)) as Unit_Price,max(last_upd_dt) as Update_dt
   into #Last_Price from ord_item where isnull(HPN,'')<>''  and isnull(last_upd_dt,'')<>''
group by hpn,UnitPrice 


select a.BoM_Level as [BoM Level],
a.BoM_Catg as [Bom Category],
a.bom_comp as [HPN],
a.Description as [Material],
a.cust_part as CPN,
a.item_typ as [MTL Type],
a.UoM,
a.Qty as [Usage],
b.Unit_Price as [Unit Price],
@Currency as [Currency]
  from #bom_item a,#Last_Price b
where a.bom_comp =b.hpn  
 order by a.Bom_level,a.bom_node



drop table #bom_item
drop table #Last_Price
end
else
		print('---------BoM not Found------------')
end